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
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
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) |