SQL Query to find the Open Prepayment Invoices in Oracle Cloud: Prepayment Status Report Query in Oracle cloud
We know that Prepayment is the Advance given to the suppliers in the form of Prepayment Invoices. When we got the final Invoice from the supplier then we do apply the Prepayment Invoice to that supplier standard invoice so this is very important to find those prepayment invoices which are still open to apply. Those prepayment invoices which are totally applied to the standard invoices are called closed prepayment. But we need to find those prepayment whose amount is still open to apply against the standard Invoices. This query will also show the amount left in the prepayment invoices for apply.
SQL Query to find the Open Prepayment Invoices in Oracle Cloud
SELECT PS.VENDOR_NAME,PS.SEGMENT1 SUPPLIER_NUMBER,
pvs.vendor_site_code,
A2.INVOICE_AMOUNT,A2.INVOICE_DATE,a2.invoice_num PREPAY_NUMBER,A2.ORG_ID,A2.VENDOR_ID,A2.INVOICE_CURRENCY_CODE,
sum(AP_PREPAY_UTILS_PKG.GET_LINE_PREPAY_AMT_REMAINING(A1.INVOICE_ID,A1.LINE_NUMBER)) prepay_amount_rema_value,
A2.VENDOR_SITE_ID,AMOUNT_PAID,A2.INVOICE_TYPE_LOOKUP_CODE,
A2.DESCRIPTION,decode(A2.PAYMENT_STATUS_FLAG,'P','Partial Paid','N','Not Paid','Y','FULLY PAID','Not Paid') PAYMENT_STATUS_FLAG
FROM AP_invoice_lines_all A1,AP_INVOICES_ALL A2,POZ_SUPPLIERS_V PS,poz_supplier_sites_all_m pvs
WHERE A1.ORG_ID=NVL(:P_ORG_ID,A1.ORG_ID)
AND A1.INVOICE_ID=A2.INVOICE_ID
and a2.INVOICE_TYPE_LOOKUP_CODE='PREPAYMENT'
and a2.vendor_id=pvs.vendor_id
and A2.INVOICE_DATE between NVL(:P_FROM_DATE,A2.INVOICE_DATE) AND NVL(:P_TO_DATE,A2.INVOICE_DATE)
and A2.VENDOR_ID=NVL(:P_VENDOR_ID,A2.VENDOR_ID)
and a2.vendor_site_id=pvs.vendor_site_id
and a2.vendor_id=ps.vendor_id
group by
PS.VENDOR_NAME,PS.SEGMENT1,pvs.vendor_site_code
,A2.INVOICE_AMOUNT,A2.INVOICE_DATE,a2.invoice_num,A2.ORG_ID,A2.VENDOR_ID,A2.INVOICE_CURRENCY_CODE,A2.VENDOR_SITE_ID,AMOUNT_PAID,A2.INVOICE_TYPE_LOOKUP_CODE,
A2.DESCRIPTION,A2.PAYMENT_STATUS_FLAG
0 comments:
Post a Comment