Supplier Balance Break up Query

This Query will give you the break of the invoice which need to be paid..
and this report will give the break up of the "Accounts Payable trial Balance".
"Accounts Payable trial Balance"  is a Standard report in the Oracle Apps

And this Query will also match with the supplier ledger ..

The Query for the Supplier Balance Break up Report

SELECT   INVOICE_TYPE_LOOKUP_CODE,
           hou.name,
           aps.segment1 Supplier_code,
           aps.segment1 Supplier_code1,
           aps.vendor_name,
           assa.vendor_site_code,
           aia.invoice_num,
           TO_CHAR (aia.invoice_date, 'DD-MON-YYYY') invoice_date,
           --        aia.invoice_date invoice_date,
           aia.invoice_amount,
           ----  sum(apd.amount) AMOUNT_APPLICABLE_TO_DISCOUNT,
           --- aia.amount_paid,
           --(APPS.invoice_paid_prepay_amount(AIa.INVOICE_ID, :gl_date)) dd,
           nvl((apps.iNVOICE_PAID_AMOUNT (AIa.INVOICE_ID, :gl_date)),0)
                         paid,
           ((NVL (aia.invoice_amount, 0)-nvl((APPS.invoice_paid_prepay_amount(AIa.INVOICE_ID, :gl_date)),0))
            - (nvl((apps.iNVOICE_PAID_AMOUNT (AIa.INVOICE_ID, :gl_date)),0)))
              remaining,
           --      nvl((aia.invoice_amount-
           --         aia.amount_paid),aia.invoice_amount) REMAINING_AMOUNT,
           --apl.AMOUNT_REMAINING,
           NVL ( (SELECT   a.segment1
                    FROM   apps.po_headers_all a
                   WHERE   a.po_header_id = ail.po_header_id), NULL)
              po_num,
           AIa.INVOICE_ID,
           aia.description,
           aia.org_id,
           aia.DOC_SEQUENCE_VALUE voucher_number,
           AIa.PAYMENT_STATUS_FLAG
    FROM   apps.ap_suppliers aps,
           apps.ap_supplier_sites_all assa,
           apps.hr_operating_units hou,
           apps.ap_invoices_all aia,
           apps.ap_invoice_lines_all ail,
           apps.ap_invoice_distributions_all apd,
           apps.AP_PAYMENT_SCHEDULES_all apl
   WHERE       aia.invoice_id = ail.invoice_id
           AND aia.invoice_id = apd.invoice_id
           AND aia.VENDOR_ID = aps.VENDOR_ID
           AND APS.VENDOR_ID = assa.VENDOR_ID
           ---     and apps.AP_INVOICES_UTILITY_PKG.get_approval_status (AIa.INVOICE_ID,
           ----                                          AIa.INVOICE_AMOUNT,
           ----                                      AIa.PAYMENT_STATUS_FLAG,
           ----                                  AIa.INVOICE_TYPE_LOOKUP_CODE) not like 'NEEDS REAPPROVAL'
           AND apps.AP_INVOICES_UTILITY_PKG.GET_POSTING_STATUS (AIa.INVOICE_ID) =
                 'Y'
           AND assa.VENDOR_SITE_ID = aia.VENDOR_SITE_ID
           AND aia.invoice_id = apl.invoice_id
           AND AIL.LINE_NUMBER = 1
           AND apd.ACCOUNTING_DATE <= :gl_date
           AND aia.org_id = :org_ID
           AND to_number(aps.segment1) BETWEEN :from_SUPPLIER_CODE AND :to_supplier_code
           AND hou.SET_OF_BOOKS_ID = aia.SET_OF_BOOKS_ID
           AND ((NVL (aia.invoice_amount, 0)-nvl((APPS.invoice_paid_prepay_amount(AIa.INVOICE_ID, :gl_date)),0))
            - (nvl((apps.iNVOICE_PAID_AMOUNT (AIa.INVOICE_ID, :gl_date)),0))) != 0
--         and aia.invoice_id in (3206842)
-----        AND (NVL(aia.invoice_amount,0)-NVL(aia.amount_paid,0)) !=0
--       AND (aia.invoice_amount)-(aia.amount_paid) !=0
GROUP BY   INVOICE_TYPE_LOOKUP_CODE,
           hou.name,
           aps.segment1,
           aps.vendor_name,
           assa.vendor_site_code,
           aia.invoice_num,
           aia.invoice_date,
           aia.invoice_amount,
           aia.amount_paid,
           aia.LAST_UPDATE_DATE,
           aia.AMOUNT_APPLICABLE_TO_DISCOUNT,
           --         ail.amount,
           --        apl.AMOUNT_REMAINING,
           ail.po_header_id,
           AIa.INVOICE_ID,
           aia.description,
           aia.org_id,
           aia.DOC_SEQUENCE_VALUE,
           AIa.PAYMENT_STATUS_FLAG
         
         
         
This Function is to get the paid amount against the invoice based on the parameter date
because we will not be able to get the exact amount on the given parameter date directly in the above query
For example
------------
There is a Invoice with the invoice amount 2000...
The invoice amount is paid partically on 10th december for 1000..
And the Remaining amount is paid on the 20th december and you run the report on 21th december
and you pass the parameter date as 19th december..
the paid amount must be 1000 against that invoice
To get the correct amount the below function will be use full
To use this function you need to pass two parameters they are
Invoice id and date on which you want the paid amount against the invocie


Function to get the paid amount of a invoice
----------------------------------------------

CREATE OR REPLACE function APPS.invoice_paid_amount(p_invoice_id in number,
                                      p_date in date )
           return number is
           r_amount number;
begin  
                                   
select (nvl(sum(Amount),0)+nvl(sum(DISCOUNT_TAKEN),0)) into r_amount
from AP_INVOICE_PAYMENTS_all
where invoice_id = p_invoice_id
and ACCOUNTING_DATE <= trunc(p_date) ;
return (r_amount);
exception
when others then
return (0);
end;      



The below Function is for concidering the prepayment made against the particular invoices


CREATE OR REPLACE function APPS.invoice_paid_prepay_amount(p_invoice_id in number,
                                      p_date in date )
           return number is
           r_amount number;
begin  
                                   
select abs(sum(AMOUNT)) into r_amount from ap_invoice_distributions_all
where invoice_id =p_invoice_id
and ACCOUNTING_DATE <= trunc(p_date)
and LINE_TYPE_LOOKUP_CODE ='PREPAY';
return (r_amount);
exception
when others then
return (0);
end;