PA Supplier Invoice Audit

Description
Categories: BI Publisher
Application: Projects
Source: AUD: Supplier Invoice Audit Report (XML)
Short Name: PAXAUVIT_XML
DB package: PA_PAXAUVIT_XMLP_PKG
           (SELECT
                p.segment1      project_number
                ,t.task_number      task_number
                ,pai.expenditure_type   expenditure_type
                ,api.invoice_date       invoice_date
                ,cdl.transferred_date       transfer_date
                ,cdl.gl_date                                     gl_date
                ,&c_select_clause                          vendor_employee_name
                ,api.invoice_num                       invoice_number
                ,apid.distribution_line_number     distribution_line_number
                ,cdl.amount amount1
                ,cdl.dr_code_combination_id,
                PA_PAXAUVIT_XMLP_PKG.account_idformula(cdl.dr_code_combination_id) ACCOUNT_ID
            FROM    pa_projects_all         p
                             ,pa_tasks          t
                             ,pa_expenditures       pae
                             ,pa_expenditure_items  pai
                                          ,ap_invoices_all      api
                             ,gl_code_combinations      cc
                             ,pa_cost_distribution_lines    cdl
                             ,ap_invoice_distributions  apid
			     , po_distributions    	 	 pod 
                             ,&c_from_clause
            WHERE
	cdl.dr_code_combination_id=decode(pod.accrue_on_receipt_flag,'Y',pod.code_combination_id,apid.dist_code_combination_id)
              -- cdl.dr_code_combination_id=apid.dist_code_combination_id 
AND	apid.po_distribution_id=pod.po_distribution_id(+)
            AND        cdl.expenditure_item_id=pai.expenditure_item_id
            AND        api.invoice_id=apid.invoice_id
           /* AND        to_number(cdl.system_reference2)=apid.invoice_id 
	    AND        to_number(cdl.system_reference3)=apid.distribution_line_number  */
	    AND        cdl.line_type='R'
            AND        &c_where_clause
            AND        &c_where_cc
            AND        pai.task_id=t.task_id
            AND        pai.system_linkage_function=:P_report_type
            AND        pai.expenditure_id=pae.expenditure_id
            AND        t.project_id=p.project_id
            AND        cdl.transfer_status_code='A'
            AND        cdl.dr_code_combination_id=cc.code_combination_id
            AND       trunc(cdl.transferred_date) between nvl(:P_from_transfer_date,trunc(cdl.transferred_date))             AND       nvl(:P_to_transfer_date,trunc(cdl.transferred_date))
            AND       cdl.gl_date between nvl(:P_from_gl_date,cdl.gl_date)
            AND       nvl(:P_to_gl_date,cdl.gl_date)
	    AND       pai.document_header_id = apid.invoice_id
AND       pai.document_distribution_id = apid.invoice_distribution_id
			UNION
			SELECT
                p.segment1      project_number
                ,t.task_number      task_number
                ,pai.expenditure_type   expenditure_type
                ,api.invoice_date       invoice_date
                ,cdl.transferred_date       transfer_date
                ,cdl.gl_date                                     gl_date
                ,&c_select_clause                          vendor_employee_name
                ,api.invoice_num                       invoice_number
                ,apid.distribution_line_number     distribution_line_number
                ,cdl.amount amount1
                ,cdl.dr_code_combination_id,
                PA_PAXAUVIT_XMLP_PKG.account_idformula(cdl.dr_code_combination_id) ACCOUNT_ID
            FROM    pa_projects_all         p
                             ,pa_tasks          t
                             ,pa_expenditures       pae
                             ,pa_expenditure_items  pai
                                          ,ap_invoices_all      api
                             ,gl_code_combinations      cc
                             ,pa_cost_distribution_lines    cdl
                             ,ap_self_assessed_tax_dist  apid
			     , po_distributions    	 	 pod 
                             ,&c_from_clause
            WHERE
	cdl.dr_code_combination_id=decode(pod.accrue_on_receipt_flag,'Y',pod.code_combination_id,apid.dist_code_combination_id)
              -- cdl.dr_code_combination_id=apid.dist_code_combination_id 
AND	apid.po_distribution_id=pod.po_distribution_id(+)
            AND        cdl.expenditure_item_id=pai.expenditure_item_id
            AND        api.invoice_id=apid.invoice_id
/*            AND        to_number(cdl.system_reference2)=apid.invoice_id 
AND        to_number(cdl.system_reference3)=apid.distribution_line_number  */
AND        cdl.line_type='R'
            AND        &c_where_clause
            AND        &c_where_cc
            AND        pai.task_id=t.task_id
            AND        pai.system_linkage_function=:P_report_type
            AND        pai.expenditure_id=pae.expenditure_id
            AND        t.project_id=p.project_id
            AND        cdl.transfer_status_code='A'
            AND        cdl.dr_code_combination_id=cc.code_combination_id
            AND       trunc(cdl.transferred_date) between nvl(:P_from_transfer_date,trunc(cdl.transferred_date)) AND       nvl(:P_to_transfer_date,trunc(cdl.transferred_date))
            AND       cdl.gl_date between nvl(:P_from_gl_date,cdl.gl_date)
            AND       nvl(:P_to_gl_date,cdl.gl_date)
	    AND       pai.document_header_id = apid.invoice_id
AND       pai.document_distribution_id = apid.invoice_distribution_id)
ORDER BY dr_code_combination_id,invoice_number,distribution_line_number
Parameter Name SQL text Validation
From GL Date
 
Date
To GL Date
 
Date
From Transfer Date
 
Date
To Transfer Date
 
Date
From GL Account
 
To GL Account
 
Set of Books Currency
 
LOV Oracle
SOB_ID
 
Number
Chart of accounts id
 
LOV Oracle
Report Type