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 |
