Project Expenditure Extraction Query

This query fetches all the expenses incurred on a project,and also captures the debit and credit account transaction wise that will help in reconcilation of data with GL.

SELECT ei.expenditure_id, p.project_id, p.segment1 project_number,p.DESCRIPTION,

       p.NAME project_name, ei.expenditure_item_id trans_id, t.task_number,

       t.task_name, 

      nvl((select nvl(USER_TRANSACTION_SOURCE,'Pre-Approved Batches') 

    from pa_transaction_sources where TRANSACTION_SOURCE=ei.transaction_source),'Pre-Approved Batches') transaction_source,

       ei.expenditure_type,

      (SELECT expenditure_category

       FROM pa_expenditure_types

       WHERE expenditure_type = ei.expenditure_type) expenditure_category,

       ei.expenditure_item_date, vendor_name supp_name,

       ap.segment1 supp_numer,

       CASE

          WHEN ei.transaction_source = 'AP INVOICE'

             THEN (SELECT vendor_site_code

                     FROM ap_supplier_sites_all

                    WHERE vendor_site_id =

                             (SELECT vendor_site_id

                                FROM ap_invoices_all

                               WHERE invoice_id =

                                        pcla.system_reference2))

          WHEN ei.transaction_source = 'PO RECEIPT'

             THEN (SELECT vendor_site_code

                     FROM ap_supplier_sites_all

                    WHERE vendor_site_id =

                             (SELECT vendor_site_id

                                FROM po_headers_all

                               WHERE po_header_id =

                                        pcla.system_reference2))

          ELSE NULL

       END vendor_site_code,

       ei.quantity, ei.raw_cost_rate,

       (SELECT SEGMENT4--concatenated_segments

        FROM gl_code_combinations_kfv

        WHERE code_combination_id = dr_code_combination_id) d_account,

       ei.projfunc_currency_code, ei.project_currency_code,

       ei.denom_currency_code trans_cur, ei.acct_raw_cost fun_raw_cost,

       ei.project_raw_cost, ei.denom_raw_cost tran_raw_cost,

       DECODE

          (ei.unit_of_measure,

           NULL, pa_utils4.get_unit_of_measure (ei.expenditure_type),

           ei.unit_of_measure

          ) unit_of_measure,                         --ei.unit_of_measure uom,

       pa_utils4.get_unit_of_measure_m (ei.unit_of_measure,

                                        ei.expenditure_type

                                       ) unit_of_measure_m,

       (SELECT expenditure_comment

          FROM pa_expenditure_comments

         WHERE expenditure_item_id =

                                   ei.expenditure_item_id)

                                                          expenditure_comment,

       x.expenditure_group batch_name,

       x.expenditure_status_code expend_status_code, ei.cost_distributed_flag,

       CASE

          WHEN ei.transaction_source = 'AP INVOICE'

             THEN (SELECT h.segment1

                     FROM po_headers_all h, po_lines_all l

                    WHERE l.po_header_id = h.po_header_id

                      AND l.po_line_id = ei.po_line_id)

          WHEN ei.transaction_source = 'PO RECEIPT'

             THEN (SELECT segment1

                     FROM po_headers_all

                    WHERE po_header_id = pcla.system_reference2)

          ELSE NULL

       END po_number,

        CASE

          WHEN ei.transaction_source = 'AP INVOICE'

             THEN NULL

          WHEN ei.transaction_source = 'PO RECEIPT'

             THEN (SELECT receipt_num

                     FROM rcv_shipment_headers

                    WHERE shipment_header_id IN (

                             SELECT shipment_header_id

                               FROM rcv_transactions

                              WHERE po_header_id =

                                        pcla.system_reference2

                                        and  TRANSACTION_ID=pcla.system_reference4

                                          ))

          ELSE NULL

       END receipt_number,

       (SELECT invoice_num

          FROM ap_invoices_all

         WHERE invoice_id = pcla.system_reference2) invoice_number,

       o1.NAME expenditure_organization_name,

       ei.expenditure_item_date gl_date,

       (SELECT SEGMENT4--concatenated_segments

          FROM gl_code_combinations_kfv

         WHERE code_combination_id = cr_code_combination_id) offset_account,

       pcla.system_reference2, dr_code_combination_id, cr_code_combination_id,

      (SELECT hp.party_name 

          FROM hz_parties hp, hz_cust_accounts hca

         WHERE hp.party_id = hca.party_id

           AND hca.status = 'A'

            and hca.org_id=p.org_id

           AND hca.cust_account_id = ei.attribute1) attribute1,

       (SELECT  hcsu.LOCATION

          FROM hz_cust_acct_sites_all hcas,

               hz_cust_site_uses_all hcsu

         WHERE hcas.cust_account_id = ei.attribute1

           AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id

           AND hcas.cust_acct_site_id = ei.attribute2

           and hcas.org_id=p.org_id

           and hcsu.site_use_code='BILL_TO') attribute2, ei.po_line_id,

       ei.orig_transaction_reference

  FROM pa_projects_all p,

       pa_tasks t,

       pa_expenditure_items_all ei,

       pa_expenditures_all x,

       pa_project_types_all pt,

       pa_transaction_sources tr,

       hr_all_organization_units_tl o1,

       ap_suppliers ap,

       pa_cost_distribution_lines_all pcla

WHERE  t.project_id = p.project_id

   AND ei.project_id = p.project_id

   AND p.project_type = pt.project_type

   AND p.org_id = pt.org_id

   AND ei.task_id = t.task_id

   AND ei.expenditure_id = x.expenditure_id

   AND ap.vendor_id(+) = ei.vendor_id

   AND t.project_id = pcla.project_id(+)

   AND ei.task_id = pcla.task_id(+)

   AND p.org_id = pcla.org_id(+)

   AND ei.expenditure_item_id = pcla.expenditure_item_id(+)

   AND NVL (ei.override_to_organization_id, x.incurred_by_organization_id) =

                                                            o1.organization_id

   AND ei.transaction_source = tr.transaction_source(+)

   AND ei.project_id IN (

          SELECT prp.project_id

            FROM pa_project_players prp, pa_project_role_types_b pprt

           WHERE p.project_id = prp.project_id

             AND t.project_id = prp.project_id

             AND prp.project_role_type = pprt.project_role_type

             AND prp.project_role_type IN ('PROJECT MANAGER', 1000)

             AND prp.person_id = :p_person_id

             AND nvl(prp.end_date_active,SYSDATE) >=SYSDATE)

   AND ei.expenditure_item_date BETWEEN NVL (:form_item_date,

                                             ei.expenditure_item_date

                                            )

                                    AND NVL (:to_item_date,

                                             ei.expenditure_item_date

                                            )

   AND ei.project_id = :p_project_id