Query to get the Suppier Open balances upto particular date.

SELECT  ORG_ID,VENDOR_SITE_CODE SUPPLIER_SITE,SEGMENT1 Supplier_number,case when sign(sum(balance))=-1 then -1*sum(balance) else 0 end CR
                                ,case when sign(sum(balance))=1 then sum(balance) else 0 end DR from
                               (
SELECT VENDOR_NAME,vendor_id,SEGMENT1,
              vendor_site_code,
               invoice_currency_code,
               org_id,
               accts ,
               jai_ap_rpt_apcr_pkg.compute_credit_balance(TO_DATE(:p_as_of_date)+1,vendor_id,2021, vendor_site_code,org_id,invoice_currency_code,accts) CREDIT,
             jai_ap_rpt_apcr_pkg.compute_debit_balance(TO_DATE(:p_as_of_date)+1,vendor_id,2021, vendor_site_code,org_id,invoice_currency_code,accts) debit,
              jai_ap_rpt_apcr_pkg.compute_credit_balance(TO_DATE(:p_as_of_date)+1,vendor_id,2021, vendor_site_code,org_id,invoice_currency_code,accts) - jai_ap_rpt_apcr_pkg.compute_debit_balance(TO_DATE(:p_as_of_date)+1,vendor_id,2021, vendor_site_code,org_id,invoice_currency_code,accts) balance
               FROM (                
             
 SELECT      VENDOR_NAME,api.vendor_id,POV.SEGMENT1,
               povs.vendor_site_code,
               api.invoice_currency_code,
               api.org_id,
               api.accts_pay_code_combination_id accts ,
               api.gl_date
FROM   ap_invoices_all api,
              po_vendors pov,
              ap_supplier_sites_all  povs
WHERE        api.vendor_id = pov.vendor_id
AND       pov.vendor_id = povs.vendor_id
AND       api.vendor_site_id = povs.vendor_site_id
--AND jai_ap_rpt_apcr_pkg.compute_credit_balance(TO_DATE(:p_as_of_date)+1,vendor_id,2022, vendor_site_code,org_id,invoice_currency_code,accts) - jai_ap_rpt_apcr_pkg.compute_debit_balance(TO_DATE(:p_as_of_date)+1,vendor_id,2022, vendor_site_code,83,invoice_currency_code,accts) <> 0
AND        api.vendor_id = NVL(:p_vendor_id, api.vendor_id)
AND      pov.segment1  = NVL(:P_vendor_no,pov.segment1)
AND      (
                (:p_vendor_type_lookup_code is null)
                OR
                ( (:p_vendor_type_lookup_code is not null) and (pov.vendor_type_lookup_code = :p_vendor_type_lookup_code) )
               )
AND (api.gl_date)          <= nvl(:p_as_of_date,api.gl_date)
AND     (api.org_id = :p_org_id )
AND api.accts_pay_code_combination_id = nvl(:p_accts_id,api.accts_pay_code_combination_id)
GROUP BY api.vendor_id,
POV.SEGMENT1 ,
VENDOR_NAME,
               povs.vendor_site_code,
               api.invoice_currency_code,
               api.org_id,
               api.accts_pay_code_combination_id)      
 )
               WHERE  1= 1
               --and  BALANCE<>0
               GROUP BY ORG_ID,VENDOR_SITE_CODE,SEGMENT1
               order by SEGMENT1;