AP Reconciliation
select 'Invoice Accounting' Accounting
,aia.ORG_ID Operating_Unit
,pv.SEGMENT1 Supplier_Code
,aia.INVOICE_TYPE_LOOKUP_CODE Invoice_Type
,pv.VENDOR_TYPE_LOOKUP_CODE VENDOR_TYPE_LOOKUP_CODE
,pv.VENDOR_NAME Supplier_Name
,pvsa.VENDOR_SITE_CODE Supplier_Site
,aia.INVOICE_ID INVOICE_ID_or_CHECK_ID
,to_char( aia.DOC_SEQUENCE_VALUE ) voucher_no
,pvsa.ATTRIBUTE1 leg_code
,aia.INVOICE_TYPE_LOOKUP_CODE PAY_COD_or_INV_TYP_LOKUP_COD
, aia.INVOICE_NUM INVOICE_NUMBER
,TO_CHAR('N/A') CHECK_NUMBER
,DECODE(AP_INVOICES_PKG.GET_POSTING_STATUS( AIa.INVOICE_ID),'P','Partial','Y','Accounted','No') ACCOUNTED
,decode((select count(aida.ATTRIBUTE2) from ap_invoice_distributions_all aida where aida.INVOICE_ID = aia.INVOICE_ID and aida.LINE_TYPE_LOOKUP_CODE = 'ITEM' and aida.ATTRIBUTE_CATEGORY = 'Purchasing Information'),
1,(select (aida.ATTRIBUTE2) from ap_invoice_distributions_all aida where aida.INVOICE_ID = aia.INVOICE_ID and aida.LINE_TYPE_LOOKUP_CODE = 'ITEM' and aida.ATTRIBUTE_CATEGORY = 'Purchasing Information')
,'Invoice Num = '||aia.INVOICE_NUM) RR
,xel.ACCOUNTED_DR ACCOUNTED_DR
,xel.ACCOUNTED_CR ACCOUNTED_CR
,xel.CODE_COMBINATION_ID
,gcc.segment1
|| '.'
|| gcc.segment2
|| '.'
|| gcc.segment3
|| '.'
|| gcc.segment4
|| '.'
|| gcc.segment5
|| '.'
|| gcc.segment6
|| '.'
|| gcc.segment7
|| '.'
|| gcc.segment8 code
,xel.code_combination_id account
,XEL.CODE_COMBINATION_ID account_desc
,TO_CHAR(nvl(aia.EXCHANGE_RATE,1)) exchange_rate
-- ,NVL(aia.ATTRIBUTE4,aia.APPROVAL_DESCRIPTION) Legacy_Voucher
,aia.DESCRIPTION
,aia.GL_DATE
,aia.INVOICE_DATE INVOICE_DATE_or_CHECK_DATE
,aia.CREATION_DATE
, AP_INVOICES_PKG.GET_APPROVAL_STATUS( AIA.INVOICE_ID,
AIA.INVOICE_AMOUNT, AIA.PAYMENT_STATUS_FLAG,
AIA.INVOICE_TYPE_LOOKUP_CODE) APPROVAL_STATUS_LOOKUP_CODE
,xel.ACCOUNTING_DATE
,xel.GL_TRANSFER_MODE_CODE
,xel.LEDGER_ID
,xel.CURRENCY_CODE
,xel.ACCOUNTING_CLASS_CODE
,xeh.GL_TRANSFER_STATUS_CODE
,APPS.FND_USER_AP_PKG.GET_USER_NAME(AIA.CREATED_BY) user_name
from xla_ae_lines xel, xla_ae_headers xeh, ap_invoices_all aia,
xla.xla_transaction_entities xte
,po_vendors pv
,po_vendor_sites_all pvsa
,gl_code_combinations gcc
where xte.application_id = 200
and xel.application_id = xeh.application_id
and xte.application_id = xeh.application_id
and xel.ae_header_id = xeh.ae_header_id
and xte.entity_code = 'AP_INVOICES'
and xte.source_id_int_1 = aia.invoice_id
and xte.entity_id = xeh.entity_id
and aia.VENDOR_ID = pv.VENDOR_ID
and aia.VENDOR_SITE_ID = pvsa.VENDOR_SITE_ID
and xel.CODE_COMBINATION_ID = gcc.CODE_COMBINATION_ID
and xel.LEDGER_ID = :p_sob_id
and xel.ACCOUNTING_CLASS_CODE in ('LIABILITY','PREPAID_EXPENSE')
-- AND XEL.CODE_COMBINATION_ID IN (SELECT GC.CODE_COMBINATION_ID FROM GL_CODE_COMBINATIONS GC
--GC.SEGMENT4 IN ('2201010001','2201010002','2201010003','2201010004','2201010005','3207021001','3207021002','3207021003','3207021004','3207021005')
--)
-- and (TO_CHAR(AIA.INVOICE_DATE, 'YYYY/MM/DD')||' 00:00:00' BETWEEN :p_DATE_FROM AND :P_DATE_TO --11380
-- OR TO_CHAR(xel.accounting_DATE, 'YYYY/MM/DD')||' 00:00:00' BETWEEN :p_DATE_FROM AND :P_DATE_TO --11380 )
-- select 30797 + 7910 from dual
union all
select 'Check Accounting' Accounting
,aca.ORG_ID Operating_Unit
,pv.SEGMENT1 Supplier_Code
,aia.INVOICE_TYPE_LOOKUP_CODE Invoice_Type
,pv.VENDOR_TYPE_LOOKUP_CODE VENDOR_TYPE_LOOKUP_CODE
,pv.VENDOR_NAME Supplier_Name
,pvsa.VENDOR_SITE_CODE Supplier_Site
,aca.CHECK_ID INVOICE_ID_or_CHECK_ID
,'N/A' voucher_no
,pvsa.ATTRIBUTE1 leg_code
,'Invoice' PAY_COD_or_INV_TYP_LOKUP_COD
,to_char( aia.INVOICE_NUM ) INVOICE_NUM
,TO_CHAR(ACA.CHECK_NUMBER) CHECK_NUMBER
,DECODE(AP_CHECKS_PKG.GET_POSTING_STATUS(ACa.CHECK_ID),'Y','Processed','N','Unprocessed','P','Partial','S','Processing','Check it Out') ACCOUNTED
,'N/A' RR
,xel.ACCOUNTED_DR ACCOUNTED_DR
,xel.ACCOUNTED_CR ACCOUNTED_CR
,xel.CODE_COMBINATION_ID
,gcc.segment1
|| '.'
|| gcc.segment2
|| '.'
|| gcc.segment3
|| '.'
|| gcc.segment4
|| '.'
|| gcc.segment5
|| '.'
|| gcc.segment6
|| '.'
|| gcc.segment7
|| '.'
|| gcc.segment8 code
,xel.code_combination_id account
,XEL.CODE_COMBINATION_ID account_desc
-- ,decode(:p_sob_id,2022,apps.get_acc_code_all(xel.code_combination_id),2026,apps.get_acc_code_all_ns(xel.code_combination_id)) account
-- ,decode(:p_sob_id,2022,apps.get_acc_desc(XEL.CODE_COMBINATION_ID),2026,apps.get_acc_desc_ns(XEL.CODE_COMBINATION_ID)) account_desc
,'N/A' exchange_rate
-- ,'N/A' Legacy_Voucher
,aca.DESCRIPTION
,aca.CHECK_DATE GL_DATE
,aca.CHECK_DATE INVOICE_DATE_or_CHECK_DATE
,aca.CREATION_DATE
,'N/A' APPROVAL_STATUS_LOOKUP_CODE
,xel.ACCOUNTING_DATE
,xel.GL_TRANSFER_MODE_CODE
,xel.LEDGER_ID
,xel.CURRENCY_CODE
,xel.ACCOUNTING_CLASS_CODE
,xeh.GL_TRANSFER_STATUS_CODE
,APPS.FND_USER_AP_PKG.GET_USER_NAME(ACA.CREATED_BY) user_name
from xla_ae_lines xel,
xla_ae_headers xeh,
ap_checks_all aca,
xla.xla_transaction_entities xte
,po_vendors pv
,po_vendor_sites_all pvsa
,gl_code_combinations gcc
,ap_invoices_all aia
where xte.application_id = 200
and xel.application_id = xeh.application_id
and xte.application_id = xeh.application_id
and xel.ae_header_id = xeh.ae_header_id
and xte.entity_code = 'AP_PAYMENTS'
and xte.source_id_int_1 = aca.CHECK_ID
and xte.entity_id = xeh.entity_id
and aia.invoice_id = aca.CHECK_ID--xte.source_id_int_1
and aca.VENDOR_ID = pv.VENDOR_ID
and aca.VENDOR_SITE_ID = pvsa.VENDOR_SITE_ID
and xel.CODE_COMBINATION_ID = gcc.CODE_COMBINATION_ID
and xel.ACCOUNTING_CLASS_CODE in ('LIABILITY','PREPAID_EXPENSE')
and xel.LEDGER_ID = :p_sob_id
-- AND XEL.CODE_COMBINATION_ID IN (SELECT GC.CODE_COMBINATION_ID FROM GL_CODE_COMBINATIONS GC
--WHERE GC.SEGMENT4 IN ('2201010001','2201010002','2201010003','2201010004','2201010005','3207021001','3207021002','3207021003','3207021004','3207021005'))
-- AND (TO_CHAR(XEL.ACCOUNTING_DATE, 'YYYY/MM/DD')||' 00:00:00' BETWEEN :p_DATE_FROM AND :P_DATE_TO --11380
-- OR TO_CHAR(ACA.CHECK_DATE, 'YYYY/MM/DD')||' 00:00:00' BETWEEN :p_DATE_FROM AND :P_DATE_TO --11380 );
0 comments:
Post a Comment