PA Supplier Costs Interface Audit

Description
Categories: BI Publisher
Columns: Cdl System Reference1, Cdl System Reference2, Invoice Num, Line Num, Expenditure Item Date, Expenditure Type, Gl Date, Project Number, Acct Raw Cost, Task Number ...
Application: Projects
Source: AUD: Supplier Costs Interface Audit (XML)
Short Name: PAAPIMPR_XML
DB package: PA_PAAPIMPR_XMLP_PKG
SELECT dist.vendor_id CDL_SYSTEM_REFERENCE1, dist.CDL_SYSTEM_REFERENCE2, inv.invoice_num,
apdist.distribution_line_number line_num, 
dist.EXPENDITURE_item_date, dist.EXPENDITURE_TYPE, dist.GL_DATE, pa_utils3.getcachedprojnum(dist.PROJECT_ID) project_number, dist.acct_raw_cost,  
pa_utils3.getcachedtasknum(dist.TASK_ID) task_number, 
SUBSTR(vend.vendor_name,1,80) 
 , dist.transaction_rejection_code, l.meaning,
inv.invoice_amount, inv.invoice_date, inv.invoice_currency_code, inv.base_amount,
DECODE(dist.transaction_status_code, 'PR', 'A', 'R', 'B', 'C') sort_c, l1.meaning trx_type,l1.lookup_code
 ,to_char(pa_utils4.get_invoice_payment_num(dist.transaction_source,dist.cdl_system_reference4)) po_number,
apdist.invoice_line_number  po_line_num
FROM pa_transaction_interface_all dist,
ap_invoices_all inv
, ap_invoice_distributions_all apdist 
, po_vendors vend 
, pa_lookups l
, pa_lookups  l1 
WHERE NVL(:Transaction_Source, dist.transaction_source) = dist.transaction_source
AND NVL(:Batch_Name, dist.batch_name) = dist.batch_name
AND dist.transaction_status_code IN ('R' , 'PR', 'PO')
AND dist.org_id            = pa_moac_utils.get_current_org_id
AND apdist.invoice_id = inv.invoice_id 
AND dist.cdl_system_reference5 = apdist.invoice_distribution_id 
AND dist.vendor_id = vend.vendor_id
AND to_number(dist.cdl_system_reference2) = inv.invoice_id
AND NVL(:System_Linkage, dist.system_linkage) = dist.system_linkage
AND NVL(:Original_Transaction_Reference, dist.orig_transaction_reference) = dist.orig_transaction_reference
AND l.lookup_code  = dist.transaction_rejection_code
AND  DECODE(dist.transaction_source,'AP INVOICE','A_INV','AP NRTAX','B_TAX','AP DISCOUNTS','C_DISC','AP VARIANCE','D_VAR','AP ERV','D_VAR','AP EXPENSE','E_EXP','INTERPROJECT_AP_INVOICES') = l1.lookup_code
AND l1.lookup_type = 'PA_CMT_TRX_TYPE'
AND l.lookup_type IN 
('TRANSACTION REJECTION REASON','FC_RESULT_CODE', 'COST DIST REJECTION CODE','INVOICE_CURRENCY', 'TRANSACTION USER REJ REASON')
UNION ALL  
SELECT dist.vendor_id CDL_SYSTEM_REFERENCE1, dist.CDL_SYSTEM_REFERENCE2, rcv_shp.receipt_num invoice_num, po_dist.distribution_num line_num, 
dist.EXPENDITURE_item_date, dist.EXPENDITURE_TYPE, dist.GL_DATE,pa_utils3.getcachedprojnum( dist.PROJECT_ID) project_number, dist.acct_raw_cost,  
pa_utils3.getcachedtasknum(dist.TASK_ID) task_number, 
SUBSTR(vend.vendor_name,1,80) 
 , dist.transaction_rejection_code, l.meaning,
rcv_sub.entered_DR invoice_amount, rcv_txn.TRANSACTION_DATE invoice_date, rcv_txn.CURRENCY_CODE invoice_currency_code, 
rcv_sub.accounted_DR base_amount,DECODE(dist.transaction_status_code, 'PR', 'A', 'R', 'B', 'C') sort_c , l1.meaning trx_type,
l1.lookup_code,po_head.segment1 po_number,po_line.line_num po_line_num
FROM pa_transaction_interface_all dist
, rcv_transactions rcv_txn
, rcv_shipment_headers rcv_shp
, po_headers_all po_head
, po_distributions_all po_dist
, po_lines_all po_line
,(SELECT     SUM(NVL(sub.accounted_dr,0)) accounted_dr
                   ,SUM(NVL(sub.entered_dr,0)) entered_dr
                  ,dist1.cdl_system_reference2 po_header_id
                 ,rcv_shp1.receipt_num receipt_num
                 ,dist1.transaction_source transaction_source 
  FROM      rcv_receiving_sub_ledger sub
                 ,rcv_transactions rcv_txn1
                 ,rcv_shipment_headers rcv_shp1
                ,pa_transaction_interface_all dist1  
   WHERE    NVL(:Transaction_Source, dist1.transaction_source) = dist1.transaction_source
   AND      NVL(:Batch_Name,dist1.batch_name) = dist1.batch_name
   AND      NVL(:System_Linkage, dist1.system_linkage) = dist1.system_linkage
   AND       dist1.org_id = pa_moac_utils.get_current_org_id
   AND      NVL(:Original_Transaction_Reference,dist1.orig_transaction_reference) = dist1.orig_transaction_reference
   AND     to_number(NVL2(LTRIM(dist1.cdl_system_reference4, '0123456789'), NULL, dist1.cdl_system_reference4)) = sub.rcv_transaction_id 
   AND      rcv_txn1.shipment_header_id = rcv_shp1.shipment_header_id
   AND      sub.rcv_transaction_id =rcv_txn1.transaction_id
  AND      ((dist1.transaction_source = 'PO RECEIPT'
                              AND EXISTS (SELECT NULL
	                                   FROM   pa_transaction_interface_all dist2
		                WHERE  dist2.cdl_system_reference4 = dist1.cdl_system_reference4
		                AND    dist2.transaction_status_code = 'R'
		                AND    dist2.transaction_source = 'PO RECEIPT'))
                  OR (dist1.transaction_source = 'PO RECEIPT NRTAX'
                              AND NOT EXISTS (SELECT NULL
			      FROM   pa_transaction_interface_all dist2
			      WHERE  dist2.cdl_system_reference4 = dist1.cdl_system_reference4
			      AND    dist2.transaction_status_code = 'R'
			     AND    dist2.transaction_source = 'PO RECEIPT'))
		  OR (dist1.transaction_source = 'PO RECEIPT PRICE ADJ'
                              AND EXISTS (SELECT NULL
			      FROM   pa_transaction_interface_all dist2
			      WHERE  dist2.cdl_system_reference4 = dist1.cdl_system_reference4
			      AND    dist2.transaction_status_code = 'R'
			     AND    dist2.transaction_source = 'PO RECEIPT PRICE ADJ'))			     
		  OR (dist1.transaction_source = 'PO RECEIPT NRTAX PRICE ADJ'
                              AND NOT EXISTS (SELECT NULL
			      FROM   pa_transaction_interface_all dist2
			      WHERE  dist2.cdl_system_reference4 = dist1.cdl_system_reference4
			      AND    dist2.transaction_status_code = 'R'
			     AND    dist2.transaction_source = 'PO RECEIPT PRICE ADJ'))			     
			     )
   AND      sub.actual_flag = 'A'
   AND      sub.code_combination_id = dist1.dr_code_combination_id
   AND      dist1.transaction_status_code = 'R'
   GROUP BY dist1.cdl_system_reference2,rcv_shp1.receipt_num,dist1.transaction_source) rcv_sub 
, po_vendors vend 
, pa_lookups l
, pa_lookups l1 
WHERE NVL(:Transaction_Source, dist.transaction_source) = dist.transaction_source
AND NVL(:Batch_Name, dist.batch_name) = dist.batch_name
AND rcv_txn.shipment_header_id = rcv_shp.shipment_header_id
AND po_head.po_header_id=po_line.po_header_id
AND po_head.po_header_id=po_dist.po_header_id
AND po_line.po_line_id=po_dist.po_line_id
AND po_dist.po_distribution_id = to_number(dist.cdl_system_reference3)
AND po_head.po_header_id = rcv_sub.po_header_id
AND po_dist.project_ID IS NOT NULL
AND rcv_sub.accounted_dr IS NOT NULL
AND po_dist.accrue_on_receipt_flag= 'Y'
AND dist.transaction_status_code IN ('R' , 'PR', 'PO')
and dist.org_id = pa_moac_utils.get_current_org_id
AND dist.vendor_id=vend.vendor_id
AND to_number(NVL2(LTRIM(dist.cdl_system_reference4, '0123456789'), NULL, dist.cdl_system_reference4)) = rcv_txn.transaction_id 
AND rcv_shp.receipt_num = rcv_sub.receipt_num
AND NVL(:System_Linkage, dist.system_linkage) = dist.system_linkage
AND NVL(:Original_Transaction_Reference, dist.orig_transaction_reference) = dist.orig_transaction_reference
AND  DECODE(dist.transaction_source,'PO RECEIPT','F_REC','PO RECEIPT NRTAX','G_RTAX',
	'PO RECEIPT PRICE ADJ','H_RECADJ','PO RECEIPT NRTAX PRICE ADJ','I_RTAXADJ') =  l1.lookup_code 
AND l1.lookup_type = 'PA_CMT_TRX_TYPE'
AND l.lookup_code  = dist.transaction_rejection_code
AND l.lookup_type IN 
('TRANSACTION REJECTION REASON','FC_RESULT_CODE', 'COST DIST REJECTION CODE','INVOICE_CURRENCY', 'TRANSACTION USER REJ REASON')
AND dist.transaction_source IN ('PO RECEIPT','PO RECEIPT NRTAX', 'PO RECEIPT PRICE ADJ','PO RECEIPT NRTAX PRICE ADJ') 
ORDER BY  20,18,11,3,4
Parameter Name SQL text Validation
Transaction Source
 
LOV Oracle
System Linkage
 
LOV Oracle
Batch Name
 
LOV Oracle
Original Transaction Reference
 
LOV Oracle