ORACLE EBS PL/SQL: Supplier Ledger (Invoices, Payments and Prepayments)

/*********************************************

Creation Date : 01 October, 2016

Purpose : AP Supplier Ledger

Module : Payables

Parameters : Supplier

Created By : Muhammad Waqas Khan

*********************************************/

select *

from (SELECT 2 sno,

LE.NAME LE_NAME,

HAOU.NAME HAOU_NAME,

AIA.INVOICE_TYPE_LOOKUP_CODE INV_TYPE,

DECODE(AIA.INVOICE_TYPE_LOOKUP_CODE,

'STANDARD',

'Standard Invoice',

'DEBIT',

'Debit Memo',

'CREDIT',

'Credit Memo') INV_TYPE_DESC,

AIA.INVOICE_NUM,

0 cHECK_NUMBER,

AIA.INVOICE_ID,

ASA.VENDOR_ID SUPP_ID,

ASA.SEGMENT1 || '-' || ASA.VENDOR_NAME SUPP_NAME,

ASSA.VENDOR_SITE_CODE SUPP_SITE,

AIA.DOC_SEQUENCE_VALUE VOUCHER_NO,

AIA.INVOICE_DATE INV_DATE,

AIDA.DESCRIPTION INV_DESC,

(case

when AIDA.AMOUNT < 0 then

abs(AIDA.AMOUNT) * NVL(ROUND(AIA.EXCHANGE_RATE, 3), 1)

end) DR,

(case

when AIDA.AMOUNT > 0 then

(AIDA.AMOUNT) * NVL(ROUND(AIA.EXCHANGE_RATE, 3), 1)

end) CR,

0 check_id,

AIA.Doc_Sequence_Value inv_doc_seq_val,

0 chk_doc_seq_val

FROM AP_INVOICES_ALL AIA,

AP_INVOICE_DISTRIBUTIONS_ALL AIDA,

AP_SUPPLIERS ASA,

AP_SUPPLIER_SITES_ALL ASSA,

XLE_ENTITY_PROFILES LE,

HR_ALL_ORGANIZATION_UNITS HAOU,

GL_LEDGER_LE_V GL

WHERE 1 = 1

and AIA.INVOICE_ID = AIDA.INVOICE_ID

AND AIA.LEGAL_ENTITY_ID = LE.LEGAL_ENTITY_ID

AND AIA.ORG_ID = HAOU.ORGANIZATION_ID

AND LE.LEGAL_ENTITY_ID = GL.LEGAL_ENTITY_ID

AND ASA.VENDOR_ID = AIA.VENDOR_ID

AND ASSA.VENDOR_ID = ASA.VENDOR_ID

AND ASSA.VENDOR_SITE_ID = AIA.VENDOR_SITE_ID

AND AIA.INVOICE_TYPE_LOOKUP_CODE in

('STANDARD', 'DEBIT', 'CREDIT')

AND AP_INVOICES_PKG.GET_APPROVAL_STATUS(AIA.INVOICE_ID,

AIA.INVOICE_AMOUNT,

AIA.PAYMENT_STATUS_FLAG,

AIA.INVOICE_TYPE_LOOKUP_CODE) = 'APPROVED'

AND AIA.GL_DATE BETWEEN &P_DATE_FROM AND &P_DATE_TO

AND AIA.ORG_ID = &P_ORG_ID

AND AIA.VENDOR_ID = &P_VENODR_TO

and aia.vendor_site_id = nvl(&P_SIT_LOW, aia.vendor_site_id)

AND AIA.Cancelled_Date Is Null

and aida.line_type_lookup_code != 'PREPAY'

and nvl(AIA.DOC_SEQUENCE_VALUE, -1) =

nvl(&voucher_number, nvl(AIA.DOC_SEQUENCE_VALUE, -1))

--&ADD_WHERE

UNION ALL

SELECT 21 sno,

LE.NAME LE_NAME,

HAOU.NAME HAOU_NAME,

AIA.INVOICE_TYPE_LOOKUP_CODE INV_TYPE,

DECODE(AIA.INVOICE_TYPE_LOOKUP_CODE,

'STANDARD',

'Standard Invoice',

'DEBIT',

'Debit Memo',

'CREDIT',

'Credit Memo') INV_TYPE_DESC,

AIA.INVOICE_NUM,

0 cHECK_NUMBER,

AIA.INVOICE_ID,

ASA.VENDOR_ID SUPP_ID,

ASA.SEGMENT1 || '-' || ASA.VENDOR_NAME SUPP_NAME,

ASSA.VENDOR_SITE_CODE SUPP_SITE,

AIA.DOC_SEQUENCE_VALUE VOUCHER_NO,

AIA.INVOICE_DATE INV_DATE,

AIDA.DESCRIPTION INV_DESC,

(case

when AIDA.AMOUNT < 0 then

abs(AIDA.AMOUNT) * NVL(ROUND(AIA.EXCHANGE_RATE, 3), 1)

end) DR,

(case

when AIDA.AMOUNT > 0 then

(AIDA.AMOUNT) * NVL(ROUND(AIA.EXCHANGE_RATE, 3), 1)

end) CR,

0 check_id,

AIA.Doc_Sequence_Value inv_doc_seq_val,

0 chk_doc_seq_val

FROM AP_INVOICES_ALL AIA,

AP_INVOICE_DISTRIBUTIONS_ALL AIDA,

AP_SUPPLIERS ASA,

AP_SUPPLIER_SITES_ALL ASSA,

XLE_ENTITY_PROFILES LE,

HR_ALL_ORGANIZATION_UNITS HAOU,

GL_LEDGER_LE_V GL

WHERE 1 = 1

and AIA.INVOICE_ID = AIDA.INVOICE_ID

AND AIA.LEGAL_ENTITY_ID = LE.LEGAL_ENTITY_ID

AND AIA.ORG_ID = HAOU.ORGANIZATION_ID

AND LE.LEGAL_ENTITY_ID = GL.LEGAL_ENTITY_ID

AND ASA.VENDOR_ID = AIA.VENDOR_ID

AND ASSA.VENDOR_ID = ASA.VENDOR_ID

AND ASSA.VENDOR_SITE_ID = AIA.VENDOR_SITE_ID

AND AIA.INVOICE_TYPE_LOOKUP_CODE in

('STANDARD', 'DEBIT', 'CREDIT')

AND AIA.GL_DATE BETWEEN &P_DATE_FROM AND &P_DATE_TO

AND AIA.ORG_ID = &P_ORG_ID

AND AIA.VENDOR_ID = &P_VENODR_TO

and aia.vendor_site_id = nvl(&P_SIT_LOW, aia.vendor_site_id)

AND AIA.Cancelled_Date Is Null

and aida.line_type_lookup_code = 'PREPAY'

AND AIDA.REVERSAL_FLAG = 'N'

and nvl(AIA.DOC_SEQUENCE_VALUE, -1) =

nvl(&voucher_number, nvl(AIA.DOC_SEQUENCE_VALUE, -1))

UNION ALL

--For Payment--

SELECT 3 sno,

LE.NAME LE_NAME,

HAOU.NAME,

AIA.INVOICE_TYPE_LOOKUP_CODE INV_TYPE,

'Payment' INV_TYPE_DESC,

AIA.INVOICE_NUM,

ACA.CHECK_NUMBER,

AIA.INVOICE_ID,

ASA.VENDOR_ID,

ASA.SEGMENT1 || '-' || ASA.VENDOR_NAME SUPP_NAME,

ASSA.VENDOR_SITE_CODE SUPP_SITE,

ACA.doc_sequence_value VOUCHER_NO,

ACA.CHECK_DATE PAY_DATE,

ACA.DESCRIPTION INV_DESC,

(CASE

WHEN AIPA.AMOUNT >= 0 THEN

SUM(AIPA.AMOUNT * NVL(ROUND(AIA.EXCHANGE_RATE, 7), 1))

ELSE

END) DR,

(CASE

WHEN AIPA.AMOUNT < 0 THEN

SUM(AIPA.AMOUNT * NVL(ROUND(AIA.EXCHANGE_RATE, 7), 1))

ELSE

END) CR,

ACA.check_id,

AIA.Doc_Sequence_Value inv_doc_seq_val,

ACA.Doc_Sequence_Value chk_doc_seq_val

FROM AP_INVOICES_ALL AIA,

AP_INVOICE_PAYMENTS_ALL AIPA,

AP_CHECKS_ALL ACA,

AP_SUPPLIERS ASA,

AP_SUPPLIER_SITES_ALL ASSA,

XLE_ENTITY_PROFILES LE,

HR_ALL_ORGANIZATION_UNITS HAOU,

GL_LEDGER_LE_V GL

WHERE 1 = 1

AND AIPA.INVOICE_ID = AIA.INVOICE_ID

AND AIPA.CHECK_ID = ACA.CHECK_ID

AND AIA.LEGAL_ENTITY_ID = LE.LEGAL_ENTITY_ID

AND AIA.ORG_ID = HAOU.ORGANIZATION_ID

AND LE.LEGAL_ENTITY_ID = GL.LEGAL_ENTITY_ID

AND ASA.VENDOR_ID = AIA.VENDOR_ID

AND ASSA.VENDOR_ID = ASA.VENDOR_ID

AND ASSA.VENDOR_SITE_ID = AIA.VENDOR_SITE_ID

AND AP_INVOICES_PKG.GET_APPROVAL_STATUS(AIA.INVOICE_ID,

AIA.INVOICE_AMOUNT,

AIA.PAYMENT_STATUS_FLAG,

AIA.INVOICE_TYPE_LOOKUP_CODE) = 'APPROVED'

AND ACA.CHECK_DATE BETWEEN &P_DATE_FROM AND &P_DATE_TO

AND AIA.ORG_ID = &P_ORG_ID

AND AIA.VENDOR_ID = &P_VENODR_TO

and aia.vendor_site_id = nvl(&P_SIT_LOW, aia.vendor_site_id)

AND ACA.STATUS_LOOKUP_CODE <> 'VOIDED'

and nvl(AIA.DOC_SEQUENCE_VALUE, -1) =

nvl(&voucher_number, nvl(AIA.DOC_SEQUENCE_VALUE, -1))

GROUP BY LE.NAME,

HAOU.NAME,

AIA.INVOICE_TYPE_LOOKUP_CODE,

'Payment',

AIA.INVOICE_ID,

CHECK_NUMBER,

ASA.VENDOR_ID,

ASA.SEGMENT1 || '-' || ASA.VENDOR_NAME,

ASSA.VENDOR_SITE_CODE,

ACA.doc_sequence_value,

ACA.CHECK_DATE,

ACA.DESCRIPTION,

AIA.EXCHANGE_RATE,

AIPA.AMOUNT,

ACA.STATUS_LOOKUP_CODE,

ACA.check_id,

ACA.CHECK_NUMBER,

AIA.INVOICE_NUM,

AIA.Doc_Sequence_Value,

ACA.Doc_Sequence_Value

UNION ALL

--For Prepayment Invoice--

SELECT 1 sno,

LE.NAME LE_NAME,

HAOU.NAME,

AIA.INVOICE_TYPE_LOOKUP_CODE INV_TYPE,

'Prepayment Invoice' INV_TYPE_DESC,

AIA.INVOICE_NUM,

0 CHECK_NUMBER,

AIA.INVOICE_ID,

ASA.VENDOR_ID,

ASA.SEGMENT1 || '-' || ASA.VENDOR_NAME SUPP_NAME,

ASSA.VENDOR_SITE_CODE SUPP_SITE,

AIA.DOC_SEQUENCE_VALUE VOUCHER_NO,

AIA.INVOICE_DATE INV_DATE,

AIA.DESCRIPTION INV_DESC,

0 CR,

(AIA.INVOICE_AMOUNT * NVL(ROUND(AIA.EXCHANGE_RATE, 7), 1)) DR,

0 check_id,

AIA.Doc_Sequence_Value inv_doc_seq_val,

0 chk_doc_seq_val

FROM AP_INVOICES_ALL AIA,

AP_SUPPLIERS ASA,

AP_SUPPLIER_SITES_ALL ASSA,

XLE_ENTITY_PROFILES LE,

HR_ALL_ORGANIZATION_UNITS HAOU,

GL_LEDGER_LE_V GL

WHERE 1 = 1

AND AIA.LEGAL_ENTITY_ID = LE.LEGAL_ENTITY_ID

AND AIA.ORG_ID = HAOU.ORGANIZATION_ID

AND LE.LEGAL_ENTITY_ID = GL.LEGAL_ENTITY_ID

AND ASA.VENDOR_ID = AIA.VENDOR_ID

AND ASSA.VENDOR_ID = ASA.VENDOR_ID

AND ASSA.VENDOR_SITE_ID = AIA.VENDOR_SITE_ID

AND AIA.INVOICE_TYPE_LOOKUP_CODE = 'PREPAYMENT'

AND AP_INVOICES_PKG.GET_APPROVAL_STATUS(AIA.INVOICE_ID,

AIA.INVOICE_AMOUNT,

AIA.PAYMENT_STATUS_FLAG,

AIA.INVOICE_TYPE_LOOKUP_CODE) in ('AVAILABLE', 'FULL')

AND AIA.GL_DATE BETWEEN &P_DATE_FROM AND &P_DATE_TO

AND AIA.ORG_ID = &P_ORG_ID

And Aia.Cancelled_Date Is Null

AND AIA.VENDOR_ID = &P_VENODR_TO

and aia.vendor_site_id = nvl(&P_SIT_LOW, aia.vendor_site_id)

and nvl(AIA.DOC_SEQUENCE_VALUE, -1) = nvl(&voucher_number, nvl(AIA.DOC_SEQUENCE_VALUE, -1)))

ORDER BY INV_DATE, inv_doc_seq_val, DR desc

ORACLE EBS PL/SQL

Wednesday, 5 October 2016

Supplier Ledger (Invoices, Payments and Prepayments)

No comments:

Post a Comment

Query to Find Receipt Class and its GL Combinition Query