PA Supplier Costs Interface Audit

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