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
Imported from BI Publisher
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.
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.
SQL 854 lines
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_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 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_RCV_REV_ADJ_WHERE &P_RCV_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('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 ,to_char(apchk.check_number) RCV_PAY_Number ,apchk.check_date RCV_PAY_Date ,PA_Adjustments.Get_Displayed_Field('PAYMENT TYPE',apchk.payment_type_flag) RCV_PAY_Transaction_Type ,appay.reversal_flag 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 ,'PAYMENTS' 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_all apdist ,ap_invoice_payments appay ,ap_checks apchk 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 ei.document_distribution_id=apdist.invoice_distribution_id AND ei.document_line_number=apline.line_number AND ei.document_header_id=ap.invoice_id AND ap.invoice_id=apline.invoice_id AND ei.document_payment_id=appay.invoice_payment_id AND apchk.check_id=appay.check_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.document_payment_id IS NOT NULL 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 DISCOUNTS' ,'AP VARIANCE' ,'INTERPROJECT_AP_INVOICES' ,'AP ERV') AND :P_TRANSACTION_TYPE IN ('ALL','PAY') 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_PAY_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 DIST NUM',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 DIST NUM',apdist.po_distribution_id),NULL) AP_PO_Dist_Type ,to_char(apchk.check_number) RCV_PAY_Number ,apchk.check_date RCV_PAY_Date ,PA_Adjustments.Get_Displayed_Field('PAYMENT TYPE',apchk.payment_type_flag) RCV_PAY_Transaction_Type ,appay.reversal_flag 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 ,'PAYMENTS' 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 apline ,ap_invoice_distributions_all apdist ,ap_invoice_payments_all appay ,ap_checks apchk WHERE ei.expenditure_item_id=cdl.expenditure_item_id AND cdl.line_type='R' AND ei.vendor_id=vend.vendor_id AND ei.document_distribution_id=apdist.invoice_distribution_id AND ei.document_line_number=apline.line_number AND ei.document_header_id=ap.invoice_id AND ap.invoice_id=apline.invoice_id AND ei.document_payment_id=appay.invoice_payment_id AND apchk.check_id=appay.check_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.document_payment_id IS NOT NULL 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 DISCOUNTS' ,'AP VARIANCE' ,'INTERPROJECT_AP_INVOICES' ,'AP ERV') AND :P_TRANSACTION_TYPE IN ('ALL','PAY') 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_PAY_REV_ADJ_WHERE &P_PAY_PA_ADJ_WHERE ORDER BY Supplier_Name &P_ORDER_BY ,Transaction_ID ,CDL_Line_Number |
Parameters 15
| Parameter | Type | List of values SQL |
|---|---|---|
| Operating Unit | LOV | |
| P_FROM_PROJECT_NUMBER | Char | |
| P_TO_PROJECT_NUMBER | Char | |
| Task Number | LOV Oracle | |
| Expenditure Organization | LOV Oracle | |
| Supplier Name | LOV Oracle | |
| Invoice Number | LOV Oracle | |
| Purchase Order | LOV Oracle | |
| Transaction Type | LOV Oracle | |
| From GL Period | LOV Oracle | |
| To GL Period | LOV Oracle | |
| From GL Account | Char | |
| To GL Account | Char | |
| Adjustment Type | LOV Oracle | |
| Sort Order | LOV Oracle |