PA Supplier Cost Audit

Description
Categories: BI Publisher
Imported from BI Publisher
Description: The program reports on supplier cost transactions. You can use the report as an aid to reconcile Oracle Projects with Oracle Payables, Oracle Purchasing, and Oracle General Ledger.
Application: Projects
Source: AUD: Supplier Cost Audit Report (XML)
Short Name: PAXSIADJ_XML
DB package: PA_PAXSIADJ_XMLP_PKG
Run PA Supplier Cost Audit and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT
vend.segment1 Supplier_Number
,vend.vendor_name Supplier_Name
,to_number(DECODE(:P_SORT_ORDER,'PO',PA_Adjustments.Get_PO_Info('PO HEADER ID',apdist.po_distribution_id),NULL)) PO_Header_ID
,DECODE(:P_SORT_ORDER,'PO',PA_Adjustments.Get_PO_Info('PO NUM',apdist.po_distribution_id),NULL) PO_Number
,to_date(DECODE(:P_SORT_ORDER,'PO',PA_Adjustments.Get_PO_Info('PO DATE',apdist.po_distribution_id),NULL),'DD-MON-YYYY')
PO_Date
,PA_Adjustments.Get_Displayed_Field('INVOICE TYPE',ap.invoice_type_lookup_code) Invoice_Type
,ap.invoice_num Invoice_Number
,ap.invoice_date Invoice_Date
,to_number(DECODE(:P_SORT_ORDER,'AP',PA_Adjustments.Get_PO_Info('PO HEADER ID',apdist.po_distribution_id),NULL)) AP_PO_Header_ID
,DECODE(:P_SORT_ORDER,'AP',PA_Adjustments.Get_PO_Info('PO NUM',apdist.po_distribution_id),NULL) AP_PO_Number
,to_Date(DECODE(:P_SORT_ORDER,'AP',PA_Adjustments.Get_PO_Info('PO DATE',apdist.po_distribution_id),NULL),'DD-MON-YYYY')
AP_PO_Date
,DECODE(:P_SORT_ORDER,'PO',apdist.po_distribution_id,NULL) PO_Distribution_ID
,to_number(DECODE(:P_SORT_ORDER,'PO',PA_Adjustments.Get_PO_Info('PO LINE NUM',apdist.po_distribution_id),NULL)) PO_Line_Num
,to_number(DECODE(:P_SORT_ORDER,'PO',PA_Adjustments.Get_PO_Info('PO DIST NUM',apdist.po_distribution_id),NULL)) PO_Dist_Num
,DECODE(:P_SORT_ORDER,'PO',PA_Adjustments.Get_PO_Info('PO TYPE',apdist.po_distribution_id),NULL) PO_Dist_Type
,apdist.invoice_distribution_id Invoice_Distribution_ID
,apline.line_number Invoice_Line_Num
,PA_Adjustments.Get_Displayed_Field('INVOICE LINE TYPE',apline.line_type_lookup_code) Invoice_Line_Type
,apdist.distribution_line_number Invoice_Dist_Line_Num
,PA_Adjustments.Get_Displayed_Field('INVOICE DISTRIBUTION TYPE',apdist.line_type_lookup_code) Invoice_Dist_Type
,apdist.reversal_flag AP_Reversal_Flag
,DECODE(:P_SORT_ORDER,'AP',apdist.po_distribution_id,NULL) AP_PO_Distribution_ID
,to_number(DECODE(:P_SORT_ORDER,'AP',PA_Adjustments.Get_PO_Info('PO LINE NUM',apdist.po_distribution_id),NULL)) AP_PO_Line_Num
,to_number(DECODE(:P_SORT_ORDER,'AP',PA_Adjustments.Get_PO_Info('PO DIST NUM',apdist.po_distribution_id),NULL)) AP_PO_Dist_Num
,DECODE(:P_SORT_ORDER,'AP',PA_Adjustments.Get_PO_Info('PO TYPE',apdist.po_distribution_id),NULL) AP_PO_Dist_Type
,PA_Adjustments.Get_Rcv_Info('RECEIPT NUMBER',apdist.rcv_transaction_id) RCV_PAY_Number
,to_Date(PA_Adjustments.Get_Rcv_Info('RECEIPT DATE',apdist.rcv_transaction_id),'DD-MON-YYYY') RCV_PAY_Date
,PA_Adjustments.Get_Rcv_Info('RCV TRANSACTION TYPE',apdist.rcv_transaction_id) RCV_PAY_Transaction_Type
,NULL RCV_PAY_Reversal_Flag
,pa_utils3.GetCachedOUName(ei.override_to_organization_id) Expenditure_Organization
,ei.expenditure_type Expenditure_Type
,ei.expenditure_item_date Expenditure_Item_Date
,ei.expenditure_item_id Transaction_ID
,ei.net_zero_adjustment_flag EI_Adjusted_Flag
,ei.adjusted_expenditure_item_id EI_Adjust_ID
,ei.transferred_from_exp_item_id EI_Transfer_ID
,NULL CDL_Line_Number
,NULL CDL_Reversed_Flag
,NULL CDL_Reversal_Line_Number
,pa_utils3.getcachedprojnum(ei.project_id) Project_Number
,pa_utils3.getcachedtasknum(ei.task_id) Task_Number
,ei.billable_flag Billable
,NULL Charge_Account
,NULL PA_Date
,NULL GL_Date
,ei.denom_currency_code Transaction_Currency
,ei.denom_raw_cost Transaction_Raw_Cost
,DECODE(apdist.po_distribution_id,NULL,to_number(NULL),ei.denom_raw_cost) PO_Transaction_Raw_Cost
,ei.denom_raw_cost AP_Transaction_Raw_Cost
,ei.acct_currency_code Functional_Currency
,ei.acct_raw_cost Functional_Raw_cost
,DECODE(apdist.po_distribution_id,NULL,to_number(NULL),ei.acct_raw_cost) PO_Functional_Raw_cost
,ei.acct_raw_cost AP_Functional_Raw_cost
,ei.transaction_source Transaction_Source
,'INVOICES' Transaction_Type,
PA_PAXSIADJ_XMLP_PKG.cf_award_numberformula(ei.expenditure_item_id) CF_AWARD_NUMBER
FROM
pa_expenditure_items ei
,po_vendors vend
,ap_invoices ap
,ap_invoice_lines apline
,ap_invoice_distributions apdist
WHERE
NOT EXISTS (SELECT NULL
FROM pa_cost_distribution_lines cdl
WHERE cdl.expenditure_item_id=ei.expenditure_item_id)
AND ei.vendor_id=vend.vendor_id
AND ap.invoice_id=apline.invoice_id
AND apline.invoice_id=apdist.invoice_id
AND apline.line_number=apdist.invoice_line_number
AND ei.document_header_id=ap.invoice_id
AND ei.document_distribution_id=apdist.invoice_distribution_id
AND ei.document_distribution_type<>'SELF_ASSESSED_TAX'
AND ei.document_payment_id IS NULL
&P_GMS_WHERE
AND pa_utils3.GetCachedProjNum(ei.project_id) BETWEEN NVL(:P_FROM_PROJECT_NUMBER,pa_utils3.GetCachedProjNum(ei.project_id))
AND NVL(:P_TO_PROJECT_NUMBER,pa_utils3.GetCachedProjNum(ei.project_id))
AND ei.task_id=NVL(:P_TASK_ID,ei.task_id)
AND ei.override_to_organization_id=NVL(:P_EXPENDITURE_ORG_ID,ei.override_to_organization_id)
AND vend.vendor_id=NVL(:P_VENDOR_ID,vend.vendor_id)
AND ap.invoice_id=NVL(:P_INVOICE_ID,ap.invoice_id)
AND (:P_PO_HEADER_ID IS NULL
OR (:P_PO_HEADER_ID IS NOT NULL
AND apdist.po_distribution_id IS NOT NULL
AND PA_Adjustments.Get_PO_Info('PO HEADER ID',apdist.po_distribution_id)=:P_PO_HEADER_ID))
AND ei.transaction_source IN ('AP EXPENSE'
,'AP INVOICE'
,'AP NRTAX'
,'AP VARIANCE'
,'INTERPROJECT_AP_INVOICES'
,'AP ERV')
AND (:P_TRANSACTION_TYPE='ALL'
OR (:P_TRANSACTION_TYPE='VI'
AND (ap.invoice_type_lookup_code<>'EXPENSE REPORT'
OR (ap.source='CREDIT_CARD'
AND ap.invoice_type_lookup_code NOT IN ('STANDARD','MIXED','CREDIT'))))
OR (:P_TRANSACTION_TYPE='ER'
AND (ap.invoice_type_lookup_code='EXPENSE REPORT'
OR (ap.source='CREDIT CARD'
AND ap.invoice_type_lookup_code IN ('STANDARD','MIXED','CREDIT')))))
AND ei.system_linkage_function IN ('VI','ER')
AND :P_FROM_GL_DATE IS NULL
AND :P_TO_GL_DATE IS NULL
AND :P_FROM_GL_ACCOUNT IS NULL
AND :P_TO_GL_ACCOUNT IS NULL
&P_INV_REV_ADJ_WHERE
UNION ALL
SELECT
vend.segment1 Supplier_Number
,vend.vendor_name Supplier_Name
,to_number(DECODE(:P_SORT_ORDER,'PO',PA_Adjustments.Get_PO_Info('PO HEADER ID',apdist.po_distribution_id),NULL)) PO_Header_ID
,DECODE(:P_SORT_ORDER,'PO',PA_Adjustments.Get_PO_Info('PO NUM',apdist.po_distribution_id),NULL) PO_Number
,to_date(DECODE(:P_SORT_ORDER,'PO',PA_Adjustments.Get_PO_Info('PO DATE',apdist.po_distribution_id),NULL),'DD-MON-YYYY')
PO_Date
,PA_Adjustments.Get_Displayed_Field('INVOICE TYPE',ap.invoice_type_lookup_code) Invoice_Type
,ap.invoice_num Invoice_Number
,ap.invoice_date Invoice_Date
,to_number(DECODE(:P_SORT_ORDER,'AP',PA_Adjustments.Get_PO_Info('PO HEADER ID',apdist.po_distribution_id),NULL)) AP_PO_Header_ID
,DECODE(:P_SORT_ORDER,'AP',PA_Adjustments.Get_PO_Info('PO NUM',apdist.po_distribution_id),NULL) AP_PO_Number
,to_Date(DECODE(:P_SORT_ORDER,'AP',PA_Adjustments.Get_PO_Info('PO DATE',apdist.po_distribution_id),NULL),'DD-MON-YYYY')
AP_PO_Date
,DECODE(:P_SORT_ORDER,'PO',apdist.po_distribution_id,NULL) PO_Distribution_ID
,to_number(DECODE(:P_SORT_ORDER,'PO',PA_Adjustments.Get_PO_Info('PO LINE NUM',apdist.po_distribution_id),NULL)) PO_Line_Num
,to_number(DECODE(:P_SORT_ORDER,'PO',PA_Adjustments.Get_PO_Info('PO DIST NUM',apdist.po_distribution_id),NULL)) PO_Dist_Num
,DECODE(:P_SORT_ORDER,'PO',PA_Adjustments.Get_PO_Info('PO TYPE',apdist.po_distribution_id),NULL) PO_Dist_Type
,apdist.invoice_distribution_id Invoice_Distribution_ID
,apline.line_number Invoice_Line_Num
,PA_Adjustments.Get_Displayed_Field('INVOICE LINE TYPE',apline.line_type_lookup_code) Invoice_Line_Type
,apdist.distribution_line_number Invoice_Dist_Line_Num
,PA_Adjustments.Get_Displayed_Field('TRANSACTION_TYPE',ei.document_distribution_type) Invoice_Dist_Type
,apdist.reversal_flag AP_Reversal_Flag
,DECODE(:P_SORT_ORDER,'AP',apdist.po_distribution_id,NULL) AP_PO_Distribution_ID
,to_number(DECODE(:P_SORT_ORDER,'AP',PA_Adjustments.Get_PO_Info('PO LINE NUM',apdist.po_distribution_id),NULL)) AP_PO_Line_Num
,to_number(DECODE(:P_SORT_ORDER,'AP',PA_Adjustments.Get_PO_Info('PO DIST NUM',apdist.po_distribution_id),NULL)) AP_PO_Dist_Num
,DECODE(:P_SORT_ORDER,'AP',PA_Adjustments.Get_PO_Info('PO TYPE',apdist.po_distribution_id),NULL) AP_PO_Dist_Type
,PA_Adjustments.Get_Rcv_Info('RECEIPT NUMBER',apdist.rcv_transaction_id) RCV_PAY_Number
,to_Date(PA_Adjustments.Get_Rcv_Info('RECEIPT DATE',apdist.rcv_transaction_id),'DD-MON-YYYY') RCV_PAY_Date
,PA_Adjustments.Get_Rcv_Info('RCV TRANSACTION TYPE',apdist.rcv_transaction_id) RCV_PAY_Transaction_Type
,NULL RCV_PAY_Reversal_Flag
,pa_utils3.GetCachedOUName(ei.override_to_organization_id) Expenditure_Organization
,ei.expenditure_type Expenditure_Type
,ei.expenditure_item_date Expenditure_Item_Date
,ei.expenditure_item_id Transaction_ID
,ei.net_zero_adjustment_flag EI_Adjusted_Flag
,ei.adjusted_expenditure_item_id EI_Adjust_ID
,ei.transferred_from_exp_item_id EI_Transfer_ID
,NULL CDL_Line_Number
,NULL CDL_Reversed_Flag
,NULL CDL_Reversal_Line_Number
,pa_utils3.getcachedprojnum(ei.project_id) Project_Number
,pa_utils3.getcachedtasknum(ei.task_id) Task_Number
,ei.billable_flag Billable
,NULL Charge_Account
,NULL PA_Date
,NULL GL_Date
,ei.denom_currency_code Transaction_Currency
,ei.denom_raw_cost Transaction_Raw_Cost
,DECODE(apdist.po_distribution_id,NULL,to_number(NULL),ei.denom_raw_cost) PO_Transaction_Raw_Cost
,ei.denom_raw_cost AP_Transaction_Raw_Cost
,ei.acct_currency_code Functional_Currency
,ei.acct_raw_cost Functional_Raw_cost
,DECODE(apdist.po_distribution_id,NULL,to_number(NULL),ei.acct_raw_cost) PO_Functional_Raw_cost
,ei.acct_raw_cost AP_Functional_Raw_cost
,ei.transaction_source Transaction_Source
,'INVOICES' Transaction_Type,
PA_PAXSIADJ_XMLP_PKG.cf_award_numberformula(ei.expenditure_item_id) CF_AWARD_NUMBER
FROM
pa_expenditure_items ei
,po_vendors vend
,ap_invoices ap
,ap_invoice_lines apline
,ap_self_assessed_tax_dist_all apdist
WHERE
NOT EXISTS (SELECT NULL
FROM pa_cost_distribution_lines cdl
WHERE cdl.expenditure_item_id=ei.expenditure_item_id)
AND ei.vendor_id=vend.vendor_id
AND ap.invoice_id=apline.invoice_id
AND apline.invoice_id=apdist.invoice_id
AND apline.line_number=apdist.invoice_line_number
AND ei.document_header_id=ap.invoice_id
AND ei.document_distribution_id=apdist.invoice_distribution_id
AND ei.document_distribution_type='SELF_ASSESSED_TAX'
AND ei.document_payment_id IS NULL
&P_GMS_WHERE
AND pa_utils3.GetCachedProjNum(ei.project_id) BETWEEN NVL(:P_FROM_PROJECT_NUMBER,pa_utils3.GetCachedProjNum(ei.project_id))
AND NVL(:P_TO_PROJECT_NUMBER,pa_utils3.GetCachedProjNum(ei.project_id))
AND ei.task_id=NVL(:P_TASK_ID,ei.task_id)
AND ei.override_to_organization_id=NVL(:P_EXPENDITURE_ORG_ID,ei.override_to_organization_id)
AND vend.vendor_id=NVL(:P_VENDOR_ID,vend.vendor_id)
AND ap.invoice_id=NVL(:P_INVOICE_ID,ap.invoice_id)
AND (:P_PO_HEADER_ID IS NULL
OR (:P_PO_HEADER_ID IS NOT NULL
AND apdist.po_distribution_id IS NOT NULL
AND PA_Adjustments.Get_PO_Info('PO HEADER ID',apdist.po_distribution_id)=:P_PO_HEADER_ID))
AND ei.transaction_source IN ('AP NRTAX')
AND (:P_TRANSACTION_TYPE='ALL'
OR (:P_TRANSACTION_TYPE='VI'
AND (ap.invoice_type_lookup_code<>'EXPENSE REPORT'
OR (ap.source='CREDIT_CARD'
AND ap.invoice_type_lookup_code NOT IN ('STANDARD','MIXED','CREDIT'))))
OR (:P_TRANSACTION_TYPE='ER'
AND (ap.invoice_type_lookup_code='EXPENSE REPORT'
OR (ap.source='CREDIT CARD'
AND ap.invoice_type_lookup_code IN ('STANDARD','MIXED','CREDIT')))))
AND ei.system_linkage_function IN ('VI','ER')
AND :P_FROM_GL_DATE IS NULL
AND :P_TO_GL_DATE IS NULL
AND :P_FROM_GL_ACCOUNT IS NULL
AND :P_TO_GL_ACCOUNT IS NULL
&P_INV_REV_ADJ_WHERE
UNION ALL
SELECT
vend.segment1 Supplier_Number
,vend.vendor_name Supplier_Name
,to_number(DECODE(:P_SORT_ORDER,'PO',PA_Adjustments.Get_PO_Info('PO HEADER ID',apdist.po_distribution_id),NULL)) PO_Header_ID
,DECODE(:P_SORT_ORDER,'PO',PA_Adjustments.Get_PO_Info('PO NUM',apdist.po_distribution_id),NULL) PO_Number
,to_date(DECODE(:P_SORT_ORDER,'PO',PA_Adjustments.Get_PO_Info('PO DATE',apdist.po_distribution_id),NULL),'DD-MON-YYYY')
PO_Date
,PA_Adjustments.Get_Displayed_Field('INVOICE TYPE',ap.invoice_type_lookup_code) Invoice_Type
,ap.invoice_num Invoice_Number
,ap.invoice_date Invoice_Date
,to_number(DECODE(:P_SORT_ORDER,'AP',PA_Adjustments.Get_PO_Info('PO HEADER ID',apdist.po_distribution_id),NULL)) AP_PO_Header_ID
,DECODE(:P_SORT_ORDER,'AP',PA_Adjustments.Get_PO_Info('PO NUM',apdist.po_distribution_id),NULL) AP_PO_Number
,to_Date(DECODE(:P_SORT_ORDER,'AP',PA_Adjustments.Get_PO_Info('PO DATE',apdist.po_distribution_id),NULL),'DD-MON-YYYY')
AP_PO_Date
,DECODE(:P_SORT_ORDER,'PO',apdist.po_distribution_id,NULL) PO_Distribution_ID
,to_number(DECODE(:P_SORT_ORDER,'PO',PA_Adjustments.Get_PO_Info('PO LINE NUM',apdist.po_distribution_id),NULL)) PO_Line_Num
,to_number(DECODE(:P_SORT_ORDER,'PO',PA_Adjustments.Get_PO_Info('PO DIST NUM',apdist.po_distribution_id),NULL)) PO_Dist_Num
,DECODE(:P_SORT_ORDER,'PO',PA_Adjustments.Get_PO_Info('PO TYPE',apdist.po_distribution_id),NULL) PO_Dist_Type
,apdist.invoice_distribution_id Invoice_Distribution_ID
,apline.line_number Invoice_Line_Num
,PA_Adjustments.Get_Displayed_Field('INVOICE LINE TYPE',apline.line_type_lookup_code) Invoice_Line_Type
,apdist.distribution_line_number Invoice_Dist_Line_Num
,PA_Adjustments.Get_Displayed_Field('INVOICE DISTRIBUTION TYPE',apdist.line_type_lookup_code) Invoice_Dist_Type
,apdist.reversal_flag AP_Reversal_Flag
,DECODE(:P_SORT_ORDER,'AP',apdist.po_distribution_id,NULL) AP_PO_Distribution_ID
,to_number(DECODE(:P_SORT_ORDER,'AP',PA_Adjustments.Get_PO_Info('PO LINE NUM',apdist.po_distribution_id),NULL)) AP_PO_Line_Num
,to_number(DECODE(:P_SORT_ORDER,'AP',PA_Adjustments.Get_PO_Info('PO DIST NUM',apdist.po_distribution_id),NULL)) AP_PO_Dist_Num
,DECODE(:P_SORT_ORDER,'AP',PA_Adjustments.Get_PO_Info('PO TYPE',apdist.po_distribution_id),NULL) AP_PO_Dist_Type
,PA_Adjustments.Get_Rcv_Info('RECEIPT NUMBER',apdist.rcv_transaction_id) RCV_PAY_Number
,to_Date(PA_Adjustments.Get_Rcv_Info('RECEIPT DATE',apdist.rcv_transaction_id),'DD-MON-YYYY') RCV_PAY_Date
,PA_Adjustments.Get_Rcv_Info('RCV TRANSACTION TYPE',apdist.rcv_transaction_id) RCV_PAY_Transaction_Type
,NULL RCV_PAY_Reversal_Flag
,pa_utils3.GetCachedOUName(ei.override_to_organization_id) Expenditure_Organization
,ei.expenditure_type Expenditure_Type
,ei.expenditure_item_date Expenditure_Item_Date
,ei.expenditure_item_id Transaction_ID
,ei.net_zero_adjustment_flag EI_Adjusted_Flag
,ei.adjusted_expenditure_item_id EI_Adjust_ID
,ei.transferred_from_exp_item_id EI_Transfer_ID
,cdl.line_num CDL_Line_Number
,cdl.reversed_flag CDL_Reversed_Flag
,cdl.line_num_reversed CDL_Reversal_Line_Number
,pa_utils3.getcachedprojnum(ei.project_id) Project_Number
,pa_utils3.getcachedtasknum(ei.task_id) Task_Number
,cdl.billable_flag Billable
,fnd_flex_ext.get_segs('SQLGL','GL#',:P_COA_ID,cdl.dr_code_combination_id) Charge_Account
,cdl.pa_date PA_Date
,cdl.gl_date GL_Date
,ei.denom_currency_code Transaction_Currency
,cdl.denom_raw_cost Transaction_Raw_Cost
,DECODE(apdist.po_distribution_id,NULL,to_number(NULL),cdl.denom_raw_cost) PO_Transaction_Raw_Cost
,cdl.denom_raw_cost AP_Transaction_Raw_Cost
,ei.acct_currency_code Functional_Currency
,cdl.acct_raw_cost Functional_Raw_cost
,DECODE(apdist.po_distribution_id,NULL,to_number(NULL),cdl.acct_raw_cost) PO_Functional_Raw_cost
,cdl.acct_raw_cost AP_Functional_Raw_cost
,ei.transaction_source Transaction_Source
,'INVOICES' Transaction_Type,
PA_PAXSIADJ_XMLP_PKG.cf_award_numberformula(ei.expenditure_item_id) CF_AWARD_NUMBER
FROM
pa_expenditure_items ei
,pa_cost_distribution_lines_all cdl
,po_vendors vend
,ap_invoices_all ap
,ap_invoice_lines_all apline
,ap_invoice_distributions_all apdist
WHERE
ei.expenditure_item_id=cdl.expenditure_item_id
AND cdl.line_type='R'
AND ei.document_payment_id IS NULL
AND ei.vendor_id=vend.vendor_id
AND ap.invoice_id=apline.invoice_id
AND apline.invoice_id=apdist.invoice_id
AND apline.line_number=apdist.invoice_line_number
AND ei.document_header_id=ap.invoice_id
AND ei.document_distribution_id=apdist.invoice_distribution_id
AND ei.document_distribution_type<>'SELF_ASSESSED_TAX'
&P_GMS_WHERE
AND pa_utils3.GetCachedProjNum(ei.project_id) BETWEEN NVL(:P_FROM_PROJECT_NUMBER,pa_utils3.GetCachedProjNum(ei.project_id))
AND NVL(:P_TO_PROJECT_NUMBER,pa_utils3.GetCachedProjNum(ei.project_id))
AND ei.task_id=NVL(:P_TASK_ID,ei.task_id)
AND ei.override_to_organization_id=NVL(:P_EXPENDITURE_ORG_ID,ei.override_to_organization_id)
AND vend.vendor_id=NVL(:P_VENDOR_ID,vend.vendor_id)
AND ap.invoice_id=NVL(:P_INVOICE_ID,ap.invoice_id)
AND (:P_PO_HEADER_ID IS NULL
OR (:P_PO_HEADER_ID IS NOT NULL
AND apdist.po_distribution_id IS NOT NULL
AND PA_Adjustments.Get_PO_Info('PO HEADER ID',apdist.po_distribution_id)=:P_PO_HEADER_ID))
AND ei.transaction_source IN ('AP EXPENSE'
,'AP INVOICE'
,'AP NRTAX'
,'AP VARIANCE'
,'INTERPROJECT_AP_INVOICES'
,'AP ERV')
AND (:P_TRANSACTION_TYPE='ALL'
OR (:P_TRANSACTION_TYPE='VI'
AND (ap.invoice_type_lookup_code<>'EXPENSE REPORT'
OR (ap.source='CREDIT_CARD'
AND ap.invoice_type_lookup_code NOT IN ('STANDARD','MIXED','CREDIT'))))
OR (:P_TRANSACTION_TYPE='ER'
AND (ap.invoice_type_lookup_code='EXPENSE REPORT'
OR (ap.source='CREDIT CARD'
AND ap.invoice_type_lookup_code IN ('STANDARD','MIXED','CREDIT')))))
AND ei.system_linkage_function IN ('VI','ER')
AND cdl.gl_date BETWEEN NVL(:P_FROM_GL_DATE,cdl.gl_date) AND NVL(:P_TO_GL_DATE,cdl.gl_date)
AND EXISTS (SELECT NULL
FROM gl_code_combinations cc
WHERE cdl.dr_code_combination_id=cc.code_combination_id
and &P_ACC_WHERE)
&P_INV_REV_ADJ_WHERE
&P_INV_PA_ADJ_WHERE
UNION ALL
SELECT
vend.segment1 Supplier_Number
,vend.vendor_name Supplier_Name
,to_number(DECODE(:P_SORT_ORDER,'PO',PA_Adjustments.Get_PO_Info('PO HEADER ID',apdist.po_distribution_id),NULL)) PO_Header_ID
,DECODE(:P_SORT_ORDER,'PO',PA_Adjustments.Get_PO_Info('PO NUM',apdist.po_distribution_id),NULL) PO_Number
,to_date(DECODE(:P_SORT_ORDER,'PO',PA_Adjustments.Get_PO_Info('PO DATE',apdist.po_distribution_id),NULL),'DD-MON-YYYY')
PO_Date
,PA_Adjustments.Get_Displayed_Field('INVOICE TYPE',ap.invoice_type_lookup_code) Invoice_Type
,ap.invoice_num Invoice_Number
,ap.invoice_date Invoice_Date
,to_number(DECODE(:P_SORT_ORDER,'AP',PA_Adjustments.Get_PO_Info('PO HEADER ID',apdist.po_distribution_id),NULL)) AP_PO_Header_ID
,DECODE(:P_SORT_ORDER,'AP',PA_Adjustments.Get_PO_Info('PO NUM',apdist.po_distribution_id),NULL) AP_PO_Number
,to_Date(DECODE(:P_SORT_ORDER,'AP',PA_Adjustments.Get_PO_Info('PO DATE',apdist.po_distribution_id),NULL),'DD-MON-YYYY')
AP_PO_Date
,DECODE(:P_SORT_ORDER,'PO',apdist.po_distribution_id,NULL) PO_Distribution_ID
,to_number(DECODE(:P_SORT_ORDER,'PO',PA_Adjustments.Get_PO_Info('PO LINE NUM',apdist.po_distribution_id),NULL)) PO_Line_Num
,to_number(DECODE(:P_SORT_ORDER,'PO',PA_Adjustments.Get_PO_Info('PO DIST NUM',apdist.po_distribution_id),NULL)) PO_Dist_Num
,DECODE(:P_SORT_ORDER,'PO',PA_Adjustments.Get_PO_Info('PO TYPE',apdist.po_distribution_id),NULL) PO_Dist_Type
,apdist.invoice_distribution_id Invoice_Distribution_ID
,apline.line_number Invoice_Line_Num
,PA_Adjustments.Get_Displayed_Field('INVOICE LINE TYPE',apline.line_type_lookup_code) Invoice_Line_Type
,apdist.distribution_line_number Invoice_Dist_Line_Num
,PA_Adjustments.Get_Displayed_Field('TRANSACTION_TYPE',ei.document_distribution_type) Invoice_Dist_Type
,apdist.reversal_flag AP_Reversal_Flag
,DECODE(:P_SORT_ORDER,'AP',apdist.po_distribution_id,NULL) AP_PO_Distribution_ID
,to_number(DECODE(:P_SORT_ORDER,'AP',PA_Adjustments.Get_PO_Info('PO LINE NUM',apdist.po_distribution_id),NULL)) AP_PO_Line_Num
,to_number(DECODE(:P_SORT_ORDER,'AP',PA_Adjustments.Get_PO_Info('PO DIST NUM',apdist.po_distribution_id),NULL)) AP_PO_Dist_Num
,DECODE(:P_SORT_ORDER,'AP',PA_Adjustments.Get_PO_Info('PO TYPE',apdist.po_distribution_id),NULL) AP_PO_Dist_Type
,PA_Adjustments.Get_Rcv_Info('RECEIPT NUMBER',apdist.rcv_transaction_id) RCV_PAY_Number
,to_Date(PA_Adjustments.Get_Rcv_Info('RECEIPT DATE',apdist.rcv_transaction_id),'DD-MON-YYYY') RCV_PAY_Date
,PA_Adjustments.Get_Rcv_Info('RCV TRANSACTION TYPE',apdist.rcv_transaction_id) RCV_PAY_Transaction_Type
,NULL RCV_PAY_Reversal_Flag
,pa_utils3.GetCachedOUName(ei.override_to_organization_id) Expenditure_Organization
,ei.expenditure_type Expenditure_Type
,ei.expenditure_item_date Expenditure_Item_Date
,ei.expenditure_item_id Transaction_ID
,ei.net_zero_adjustment_flag EI_Adjusted_Flag
,ei.adjusted_expenditure_item_id EI_Adjust_ID
,ei.transferred_from_exp_item_id EI_Transfer_ID
,cdl.line_num CDL_Line_Number
,cdl.reversed_flag CDL_Reversed_Flag
,cdl.line_num_reversed CDL_Reversal_Line_Number
,pa_utils3.getcachedprojnum(ei.project_id) Project_Number
,pa_utils3.getcachedtasknum(ei.task_id) Task_Number
,cdl.billable_flag Billable
,fnd_flex_ext.get_segs('SQLGL','GL#',:P_COA_ID,cdl.dr_code_combination_id) Charge_Account
,cdl.pa_date PA_Date
,cdl.gl_date GL_Date
,ei.denom_currency_code Transaction_Currency
,cdl.denom_raw_cost Transaction_Raw_Cost
,DECODE(apdist.po_distribution_id,NULL,to_number(NULL),cdl.denom_raw_cost) PO_Transaction_Raw_Cost
,cdl.denom_raw_cost AP_Transaction_Raw_Cost
,ei.acct_currency_code Functional_Currency
,cdl.acct_raw_cost Functional_Raw_cost
,DECODE(apdist.po_distribution_id,NULL,to_number(NULL),cdl.acct_raw_cost) PO_Functional_Raw_cost
,cdl.acct_raw_cost AP_Functional_Raw_cost
,ei.transaction_source Transaction_Source
,'INVOICES' Transaction_Type,
PA_PAXSIADJ_XMLP_PKG.cf_award_numberformula(ei.expenditure_item_id) CF_AWARD_NUMBER
FROM
pa_expenditure_items ei
,pa_cost_distribution_lines_all cdl
,po_vendors vend
,ap_invoices_all ap
,ap_invoice_lines_all apline
,ap_self_assessed_tax_dist_all apdist
WHERE
ei.expenditure_item_id=cdl.expenditure_item_id
AND cdl.line_type='R'
AND ei.document_payment_id IS NULL
AND ei.vendor_id=vend.vendor_id
AND ap.invoice_id=apline.invoice_id
AND apline.invoice_id=apdist.invoice_id
AND apline.line_number=apdist.invoice_line_number
AND ei.document_header_id=ap.invoice_id
AND ei.document_distribution_id=apdist.invoice_distribution_id
AND ei.document_distribution_type='SELF_ASSESSED_TAX'
&P_GMS_WHERE
AND pa_utils3.GetCachedProjNum(ei.project_id) BETWEEN NVL(:P_FROM_PROJECT_NUMBER,pa_utils3.GetCachedProjNum(ei.project_id))
AND NVL(:P_TO_PROJECT_NUMBER,pa_utils3.GetCachedProjNum(ei.project_id))
AND ei.task_id=NVL(:P_TASK_ID,ei.task_id)
AND ei.override_to_organization_id=NVL(:P_EXPENDITURE_ORG_ID,ei.override_to_organization_id)
AND vend.vendor_id=NVL(:P_VENDOR_ID,vend.vendor_id)
AND ap.invoice_id=NVL(:P_INVOICE_ID,ap.invoice_id)
AND (:P_PO_HEADER_ID IS NULL
OR (:P_PO_HEADER_ID IS NOT NULL
AND apdist.po_distribution_id IS NOT NULL
AND PA_Adjustments.Get_PO_Info('PO HEADER ID',apdist.po_distribution_id)=:P_PO_HEADER_ID))
AND ei.transaction_source IN ('AP NRTAX')
AND (:P_TRANSACTION_TYPE='ALL'
OR (:P_TRANSACTION_TYPE='VI'
AND (ap.invoice_type_lookup_code<>'EXPENSE REPORT'
OR (ap.source='CREDIT_CARD'
AND ap.invoice_type_lookup_code NOT IN ('STANDARD','MIXED','CREDIT'))))
OR (:P_TRANSACTION_TYPE='ER'
AND (ap.invoice_type_lookup_code='EXPENSE REPORT'
OR (ap.source='CREDIT CARD'
AND ap.invoice_type_lookup_code IN ('STANDARD','MIXED','CREDIT')))))
AND ei.system_linkage_function IN ('VI','ER')
AND cdl.gl_date BETWEEN NVL(:P_FROM_GL_DATE,cdl.gl_date) AND NVL(:P_TO_GL_DATE,cdl.gl_date)
AND EXISTS (SELECT NULL
FROM gl_code_combinations cc
WHERE cdl.dr_code_combination_id=cc.code_combination_id
and &P_ACC_WHERE)
&P_INV_REV_ADJ_WHERE
&P_INV_PA_ADJ_WHERE
UNION ALL
SELECT
vend.segment1 Supplier_Number
,vend.vendor_name Supplier_Name
,to_number(DECODE(:P_SORT_ORDER,'PO',PA_Adjustments.Get_PO_Info('PO HEADER ID',rcvtxn.po_distribution_id),NULL)) PO_Header_ID
,DECODE(:P_SORT_ORDER,'PO',PA_Adjustments.Get_PO_Info('PO NUM',rcvtxn.po_distribution_id),NULL) PO_Number
,to_date(DECODE(:P_SORT_ORDER,'PO',PA_Adjustments.Get_PO_Info('PO DATE',rcvtxn.po_distribution_id),NULL),'DD-MON-YYYY')
PO_Date
,PA_Adjustments.Get_Inv_Info('INVOICE TYPE',:P_INVOICE_ID) Invoice_Type
,PA_Adjustments.Get_Inv_Info('INVOICE NUMBER',:P_INVOICE_ID) Invoice_Number
,to_date(PA_Adjustments.Get_Inv_Info('INVOICE DATE',:P_INVOICE_ID),'DD-MON-YYYY') Invoice_Date
,to_number(DECODE(:P_SORT_ORDER,'AP',PA_Adjustments.Get_PO_Info('PO HEADER ID',rcvtxn.po_distribution_id),NULL)) AP_PO_Header_ID
,DECODE(:P_SORT_ORDER,'AP',PA_Adjustments.Get_PO_Info('PO NUM',rcvtxn.po_distribution_id),NULL) AP_PO_Number
,to_date(DECODE(:P_SORT_ORDER,'AP',PA_Adjustments.Get_PO_Info('PO DATE',rcvtxn.po_distribution_id),NULL),'DD-MON-YYYY')
AP_PO_Date
,DECODE(:P_SORT_ORDER,'PO',rcvtxn.po_distribution_id,NULL) PO_Distribution_ID
,to_number(DECODE(:P_SORT_ORDER,'PO',PA_Adjustments.Get_PO_Info('PO LINE NUM',rcvtxn.po_distribution_id),NULL)) PO_Line_Num
,to_number(DECODE(:P_SORT_ORDER,'PO',PA_Adjustments.Get_PO_Info('PO DIST NUM',rcvtxn.po_distribution_id),NULL)) PO_Dist_Num
,DECODE(:P_SORT_ORDER,'PO',PA_Adjustments.Get_PO_Info('PO TYPE',rcvtxn.po_distribution_id),NULL) PO_Dist_Type
,NULL Invoice_Distribution_ID
,NULL Invoice_Line_Num
,NULL Invoice_Line_Type
,NULL Invoice_Dist_Line_Num
,NULL Invoice_Dist_Type
,NULL AP_Reversal_Flag
,DECODE(:P_SORT_ORDER,'AP',rcvtxn.po_distribution_id,NULL) AP_PO_Distribution_ID
,to_number(DECODE(:P_SORT_ORDER,'AP',PA_Adjustments.Get_PO_Info('PO LINE NUM',rcvtxn.po_distribution_id),NULL)) AP_PO_Line_Num
,to_number(DECODE(:P_SORT_ORDER,'AP',PA_Adjustments.Get_PO_Info('PO DIST NUM',rcvtxn.po_distribution_id),NULL)) AP_PO_Dist_Num
,DECODE(:P_SORT_ORDER,'AP',PA_Adjustments.Get_PO_Info('PO TYPE',rcvtxn.po_distribution_id),NULL) AP_PO_Dist_Type
,rcv.receipt_num RCV_PAY_Number
,rcvtxn.transaction_date RCV_PAY_Date
,PA_Adjustments.Get_Displayed_Field('RCV TRANSACTION TYPE',rcvtxn.transaction_type) RCV_PAY_Transaction_Type
,NVL((SELECT 'Y'
FROM dual
WHERE 0=
(SELECT sum(rcvtxnrev.quantity)
FROM rcv_transactions rcvtxnrev
WHERE rcvtxnrev.parent_transaction_id=rcvtxn.transaction_id
OR rcvtxnrev.transaction_id=rcvtxn.transaction_id)),'N') RCV_PAY_Reversal_Flag
,pa_utils3.GetCachedOUName(ei.override_to_organization_id) Expenditure_Organization
,ei.expenditure_type Expenditure_Type
,ei.expenditure_item_date Expenditure_Item_Date
,ei.expenditure_item_id Transaction_ID
,ei.net_zero_adjustment_flag EI_Adjusted_Flag
,ei.adjusted_expenditure_item_id EI_Adjust_ID
,ei.transferred_from_exp_item_id EI_Transfer_ID
,NULL CDL_Line_Number
,NULL CDL_Reversed_Flag
,NULL CDL_Reversal_Line_Number
,pa_utils3.getcachedprojnum(ei.project_id) Project_Number
,pa_utils3.getcachedtasknum(ei.task_id) Task_Number
,ei.billable_flag Billable
,NULL Charge_Account
,NULL PA_Date
,NULL GL_Date
,ei.denom_currency_code Transaction_Currency
,ei.denom_raw_cost Functional_Raw_cost
,ei.denom_raw_cost PO_Transaction_Raw_Cost
,NULL AP_Transaction_Raw_Cost
,ei.acct_currency_code Functional_Currency
,ei.acct_raw_cost Functional_Raw_cost
,ei.acct_raw_cost PO_Functional_Raw_cost
,NULL AP_Functional_Raw_cost
,ei.transaction_source Transaction_Source
,'RECEIPTS' Transaction_Type,
PA_PAXSIADJ_XMLP_PKG.cf_award_numberformula(ei.expenditure_item_id) CF_AWARD_NUMBER
FROM
pa_expenditure_items ei
,po_vendors vend
,rcv_shipment_headers rcv
,rcv_transactions rcvtxn
WHERE
NOT EXISTS (SELECT NULL
FROM pa_cost_distribution_lines cdl
WHERE cdl.expenditure_item_id=ei.expenditure_item_id)
AND ei.vendor_id=vend.vendor_id
AND rcvtxn.transaction_id=ei.document_distribution_id
AND rcv.shipment_header_id=rcvtxn.shipment_header_id
&P_GMS_WHERE
AND pa_utils3.GetCachedProjNum(ei.project_id) BETWEEN NVL(:P_FROM_PROJECT_NUMBER,pa_utils3.GetCachedProjNum(ei.project_id))
AND NVL(:P_TO_PROJECT_NUMBER,pa_utils3.GetCachedProjNum(ei.project_id))
AND ei.task_id=NVL(:P_TASK_ID,ei.task_id)
AND ei.override_to_organization_id=NVL(:P_EXPENDITURE_ORG_ID,ei.override_to_organization_id)
AND vend.vendor_id=NVL(:P_VENDOR_ID,vend.vendor_id)
AND ei.transaction_source IN ('PO RECEIPT'
,'PO RECEIPT NRTAX'
,'PO RECEIPT NRTAX PRICE ADJ'
,'PO RECEIPT PRICE ADJ')
AND :P_TRANSACTION_TYPE IN ('ALL','RCV')
AND (:P_INVOICE_ID IS NULL
OR rcvtxn.transaction_id IN (SELECT ap_dist.rcv_transaction_id
FROM ap_invoice_distributions_all ap_dist
WHERE ap_dist.invoice_id=:P_INVOICE_ID))
AND rcvtxn.po_header_id=NVL(:P_PO_HEADER_ID,rcvtxn.po_header_id)
AND ei.system_linkage_function='VI'
AND :P_FROM_GL_DATE IS NULL
AND :P_TO_GL_DATE IS NULL
AND :P_FROM_GL_ACCOUNT IS NULL
AND :P_TO_GL_ACCOUNT IS NULL
&P_RCV_REV_ADJ_WHERE
UNION ALL
SELECT
vend.segment1 Supplier_Number
,vend.vendor_name Supplier_Name
,to_number(DECODE(:P_SORT_ORDER,'PO',PA_Adjustments.Get_PO_Info('PO HEADER ID',rcvtxn.po_distribution_id),NULL)) PO_Header_ID
,DECODE(:P_SORT_ORDER,'PO',PA_Adjustments.Get_PO_Info('PO NUM',rcvtxn.po_distribution_id),NULL) PO_Number
,to_date(DECODE(:P_SORT_ORDER,'PO',PA_Adjustments.Get_PO_Info('PO DATE',rcvtxn.po_distribution_id),NULL),'DD-MON-YYYY')
PO_Date
,PA_Adjustments.Get_Inv_Info('INVOICE TYPE',:P_INVOICE_ID) Invoice_Type
,PA_Adjustments.Get_Inv_Info('INVOICE NUMBER',:P_INVOICE_ID) Invoice_Number
,to_date(PA_Adjustments.Get_Inv_Info('INVOICE DATE',:P_INVOICE_ID),'DD-MON-YYYY') Invoice_Date
,to_number(DECODE(:P_SORT_ORDER,'AP',PA_Adjustments.Get_PO_Info('PO HEADER ID',rcvtxn.po_distribution_id),NULL)) AP_PO_Header_ID
,DECODE(:P_SORT_ORDER,'AP',PA_Adjustments.Get_PO_Info('PO NUM',rcvtxn.po_distribution_id),NULL) AP_PO_Number
,to_date(DECODE(:P_SORT_ORDER,'AP',PA_Adjustments.Get_PO_Info('PO DATE',rcvtxn.po_distribution_id),NULL),'DD-MON-YYYY')
AP_PO_Date
,DECODE(:P_SORT_ORDER,'PO',rcvtxn.po_distribution_id,NULL) PO_Distribution_ID
,to_number(DECODE(:P_SORT_ORDER,'PO',PA_Adjustments.Get_PO_Info('PO LINE NUM',rcvtxn.po_distribution_id),NULL)) PO_Line_Num
,to_number(DECODE(:P_SORT_ORDER,'PO',PA_Adjustments.Get_PO_Info('PO DIST NUM',rcvtxn.po_distribution_id),NULL)) PO_Dist_Num
,DECODE(:P_SORT_ORDER,'PO',PA_Adjustments.Get_PO_Info('PO TYPE',rcvtxn.po_distribution_id),NULL) PO_Dist_Type
,NULL Invoice_Distribution_ID
,NULL Invoice_Line_Num
,NULL Invoice_Line_Type
,NULL Invoice_Dist_Line_Num
,NULL Invoice_Dist_Type
,NULL AP_Reversal_Flag
,DECODE(:P_SORT_ORDER,'AP',rcvtxn.po_distribution_id,NULL) AP_PO_Distribution_ID
,to_number(DECODE(:P_SORT_ORDER,'AP',PA_Adjustments.Get_PO_Info('PO LINE NUM',rcvtxn.po_distribution_id),NULL)) AP_PO_Line_Num
,to_number(DECODE(:P_SORT_ORDER,'AP',PA_Adjustments.Get_PO_Info('PO DIST NUM',rcvtxn.po_distribution_id),NULL)) AP_PO_Dist_Num
,DECODE(:P_SORT_ORDER,'AP',PA_Adjustments.Get_PO_Info('PO TYPE',rcvtxn.po_distribution_id),NULL) AP_PO_Dist_Type
,rcv.receipt_num RCV_PAY_Number
,rcvtxn.transaction_date RCV_PAY_Date
,PA_Adjustments.Get_Displayed_Field('RCV TRANSACTION TYPE',rcvtxn.transaction_type) RCV_PAY_Transaction_Type
,NVL((SELECT 'Y'
FROM dual
WHERE 0=
(SELECT sum(rcvtxnrev.quantity)
FROM rcv_transactions rcvtxnrev
WHERE rcvtxnrev.parent_transaction_id=rcvtxn.transaction_id
OR rcvtxnrev.transaction_id=rcvtxn.transaction_id)),'N') RCV_PAY_Reversal_Flag
,pa_utils3.GetCachedOUName(ei.override_to_organization_id) Expenditure_Organization
,ei.expenditure_type Expenditure_Type
,ei.expenditure_item_date Expenditure_Item_Date
,ei.expenditure_item_id Transaction_ID
,ei.net_zero_adjustment_flag EI_Adjusted_Flag
,ei.adjusted_expenditure_item_id EI_Adjust_ID
,ei.transferred_from_exp_item_id EI_Transfer_ID
,cdl.line_num CDL_Line_Number
,cdl.reversed_flag CDL_Reversed_Flag
,cdl.line_num_reversed CDL_Reversal_Line_Number
,pa_utils3.getcachedprojnum(ei.project_id) Project_Number
,pa_utils3.getcachedtasknum(ei.task_id) Task_Number
,cdl.billable_flag Billable
,fnd_flex_ext.get_segs('SQLGL','GL#',:P_COA_ID,cdl.dr_code_combination_id) Charge_Account
,cdl.pa_date PA_Date
,cdl.gl_date GL_Date
,ei.denom_currency_code Transaction_Currency
,cdl.denom_raw_cost Functional_Raw_cost
,cdl.denom_raw_cost PO_Transaction_Raw_Cost
,NULL AP_Transaction_Raw_Cost
,ei.acct_currency_code Functional_Currency
,cdl.acct_raw_cost Functional_Raw_cost
,cdl.acct_raw_cost PO_Functional_Raw_cost
,NULL AP_Functional_Raw_cost
,ei.transaction_source Transaction_Source
,'RECEIPTS' Transaction_Type,
PA_PAXSIADJ_XMLP_PKG.cf_award_numberformula(ei.expenditure_item_id) CF_AWARD_NUMBER
FROM
pa_expenditure_items ei
,pa_cost_distribution_lines_all cdl
,po_vendors vend
,rcv_shipment_headers rcv
,rcv_transactions rcvtxn
WHERE
ei.expenditure_item_id=cdl.expenditure_item_id
AND cdl.line_type='R'
AND ei.vendor_id=vend.vendor_id
AND rcvtxn.transaction_id=ei.document_distribution_id
AND rcv.shipment_header_id=rcvtxn.shipment_header_id
&P_GMS_WHERE
AND pa_utils3.GetCachedProjNum(ei.project_id) BETWEEN NVL(:P_FROM_PROJECT_NUMBER,pa_utils3.GetCachedProjNum(ei.project_id))
AND NVL(:P_TO_PROJECT_NUMBER,pa_utils3.GetCachedProjNum(ei.project_id))
AND ei.task_id=NVL(:P_TASK_ID,ei.task_id)
AND ei.override_to_organization_id=NVL(:P_EXPENDITURE_ORG_ID,ei.override_to_organization_id)
AND vend.vendor_id=NVL(:P_VENDOR_ID,vend.vendor_id)
AND ei.transaction_source IN ('PO RECEIPT'
,'PO RECEIPT NRTAX'
,'PO RECEIPT NRTAX PRICE ADJ'
,'PO RECEIPT PRICE ADJ')
AND :P_TRANSACTION_TYPE IN ('ALL','RCV')
AND (:P_INVOICE_ID IS NULL
OR rcvtxn.transaction_id IN (SELECT ap_dist.rcv_transac