ORACLE EBS PL/SQL: AP Invoice

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

Creation Date : 04 October, 2016

Purpose : AP Prepayment Report

Module : Payables

Parameters : Supplier and Organization

Created By : Muhammad Waqas Khan

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

SELECT VENDOR_NAME,

ORG_ID,

VOUCHER,

C_INVOICE_NUM,

CURR,

INVOICE_DATE,

GL_DATE,

DESCRIP,

(INVOICE_AMOUNT * EXCHANGE_RATE) INVOICE_AMOUNT,

(ABS(PREPAY_AMOUNT) * EXCHANGE_RATE) PREPAY_AMOUNT,

(REMAINING_AMOUNT * EXCHANGE_RATE) REMAINING_AMOUNT

FROM (SELECT UPPER(C_VENDOR_NAME) VENDOR_NAME,

ORG_ID,

DESCRIP,

DOC_SEQUENCE_VALUE,

C_INVOICE_NUM,

INVOICE_DATE,

INVOICE_AMOUNT,

PREPAY_AMOUNT,

INVOICE_AMOUNT + PREPAY_AMOUNT REMAINING_AMOUNT,

EXCHANGE_RATE,

GL_DATE,

CURR,

(SELECT ACA.DOC_SEQUENCE_VALUE

FROM AP_INVOICE_PAYMENTS_ALL AIPA, AP_CHECKS_ALL ACA

WHERE AIPA.INVOICE_ID = X.INVOICE_ID

AND ACA.STATUS_LOOKUP_CODE = 'RECONCILED'

AND ACA.CHECK_ID = AIPA.CHECK_ID) VOUCHER

FROM (SELECT DISTINCT PV.VENDOR_NAME AS C_VENDOR_NAME,

INV.DOC_SEQUENCE_VALUE,

INV.DESCRIPTION DESCRIP,

INV.ORG_ID,

INV.INVOICE_ID,

INV.INVOICE_NUM AS C_INVOICE_NUM,

INV.INVOICE_DATE,

NVL(INV.EXCHANGE_RATE, 1) EXCHANGE_RATE,

INV.INVOICE_CURRENCY_CODE CURR,

INV.INVOICE_CURRENCY_CODE AS C_CURRENCY_CODE,

INV.GL_DATE,

NVL(INV.INVOICE_AMOUNT, 0) AS INVOICE_AMOUNT,

(SELECT SUM(AMOUNT)

FROM AP_INVOICE_DISTRIBUTIONS_ALL IDA

WHERE LINE_TYPE_LOOKUP_CODE = 'PREPAY'

AND AID.INVOICE_DISTRIBUTION_ID =

IDA.PREPAY_DISTRIBUTION_ID) AS PREPAY_AMOUNT

FROM PO_VENDORS PV,

AP_INVOICES_ALL INV,

AP_INVOICE_DISTRIBUTIONS_ALL AID,

AP_INVOICE_PREPAYS_ALL AIPP

WHERE PV.VENDOR_ID = INV.VENDOR_ID

AND INV.INVOICE_ID = AID.INVOICE_ID

AND AIPP.INVOICE_ID(+) = INV.INVOICE_ID

AND PV.VENDOR_ID = NVL(:P_VENDOR_ID, PV.VENDOR_ID)

AND AID.LINE_TYPE_LOOKUP_CODE <> 'PREPAY'

AND INV.ORG_ID = :P_ORG_ID --234

--AND INV.INVOICE_NUM = 'PTC2014/380'

) X

WHERE INVOICE_AMOUNT - PREPAY_AMOUNT IS NOT NULL)

WHERE REMAINING_AMOUNT > 0

ORDER BY 1, 6;

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

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

AP Prepayment Report Query

Supplier Ledger (Invoices, Payments and Prepayments)

Query to Find Receipt Class and its GL Combinition Query