PA Expense Report Audit

            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
                               ,cdl.dr_code_combination_id
                            ,apid.invoice_line_number         invoice_line_num,
                PA_PAXAUVIT_XMLP_PKG.account_flex_idformula(cdl.dr_code_combination_id) ACCOUNT_FLEX_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
                           ,&c_from_clause
            WHERE  cdl.dr_code_combination_id=apid.dist_code_combination_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='V'
                 AND cdl.dr_code_combination_id=cc.code_combination_id
                 AND cdl.gl_date BETWEEN  nvl(:P_from_gl_date,cdl.gl_date) AND                             nvl(:P_to_gl_date,cdl.gl_date)
                 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))
               ORDER BY 11 ASC , dr_code_combination_id , invoice_number , distribution_line_number