Thursday, 4 January 2018

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,
      nvl((select nvl(USER_TRANSACTION_SOURCE,'Pre-Approved Batches') 
    from pa_transaction_sources where TRANSACTION_SOURCE=ei.transaction_source),'Pre-Approved Batches') transaction_source,
      (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,
          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 =
          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 =
          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,
           NULL, pa_utils4.get_unit_of_measure (ei.expenditure_type),
          ) unit_of_measure,                         --ei.unit_of_measure uom,
       pa_utils4.get_unit_of_measure_m (ei.unit_of_measure,
                                       ) unit_of_measure_m,
       (SELECT expenditure_comment
          FROM pa_expenditure_comments
         WHERE expenditure_item_id =
       x.expenditure_group batch_name,
       x.expenditure_status_code expend_status_code, ei.cost_distributed_flag,
          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,
          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 =
                                        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,
  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) =
   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,
                                    AND NVL (:to_item_date,
   AND ei.project_id = :p_project_id


  1. Nice blog about punchout XML, it's being great to read this.
    CXML Punchout

  2. Thanks for sharing article about CXML Punchout
    CXML Punchout