PA Supplier Cost Audit

Description
Categories: BI Publisher
Application: Projects
Source: AUD: Supplier Cost Audit Report (XML)
Short Name: PAXSIADJ_XML
DB package: PA_PAXSIADJ_XMLP_PKG
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