SELECT ei.ROWID row_id, 'PROJECT' search_criteria, ei.expenditure_item_id,
          SUBSTR (gcc.segment2, 1, 6) ACCOUNT,
          SUBSTR (gcc.segment5, 1, 10) local_account,
          SUBSTR (gcc.segment1, 1, 3) le, SUBSTR (gcc.segment3, 1, 5) pu,
          SUBSTR (gcc.segment4, 1, 3) dept, ei.expenditure_id, p.project_id,
          p.segment1 project_number, p.NAME project_name, pt.project_type,
          pt.project_type_class_code, ei.task_id, t.task_number,
          NVL (SUBSTR (t.task_number, 1, INSTR (t.task_number, '*', 1) - 1),
               t.task_number
              ) work_stage,
          DECODE (INSTR (t.task_number, '*', 1),
                  0, '',
                  SUBSTR (t.task_number, INSTR (t.task_number, '*', 1) + 1)
                 ) activity_code,
          t.task_name, ei.expenditure_item_date, ei.expenditure_type,
          et.expenditure_category, et.revenue_category_code,
          x.incurred_by_person_id, emp.full_name employee_name,
                            /*DECODE (p.org_id,
                            '103', DECODE (emp.attribute7,
                                           NULL, NULL,
          --                                 'DEN#' || emp.attribute7
                                             emp.attribute7
                                        ),
                            '107', DECODE (emp.attribute7,
                                           NULL, NULL,
          --                                 'DEN#' || emp.attribute7
                                             emp.attribute7
                                          ),
                            '2201', DECODE (emp.attribute7,
                                            NULL, NULL,
          --                                 'DEN#' || emp.attribute7
                                             emp.attribute7
                                           ),
                            emp.employee_number
                           ) employee_number,*/
          DECODE (p.org_id,
                  '103', NVL (DECODE (emp.attribute7,
                                      NULL, NULL,
--                                 'DEN#' || emp.attribute7
                                      emp.attribute7
                                     ),
                              emp.employee_number
                             ),
                  '107', NVL (DECODE (emp.attribute7,
                                      NULL, NULL,
--                                 'DEN#' || emp.attribute7
                                      emp.attribute7
                                     ),
                              emp.employee_number
                             ),
                  '2201', NVL (DECODE (emp.attribute7,
                                       NULL, NULL,
--                                 'DEN#' || emp.attribute7
                                       emp.attribute7
                                      ),
                               emp.employee_number
                              ),
                  emp.employee_number
                 ) employee_number,                  ---- Ghansham 24-Nov-2009
          emp.attribute7 den_number, ei.job_id, j.NAME job_name,
          x.incurred_by_organization_id, ei.override_to_organization_id,
          NVL (ei.override_to_organization_id,
               x.incurred_by_organization_id
              ) expenditure_organization_id,
          o1.NAME expenditure_organization_name, ei.non_labor_resource,
          ei.organization_id nlr_organization_id,
          o2.NAME nlr_organization_name, ei.system_linkage_function,
          c.expenditure_comment,
          ei.transaction_source || '' transaction_source,
          ei.orig_transaction_reference, x.expenditure_group,
          x.expenditure_status_code, x.expenditure_ending_date, cdl.quantity,
          et.unit_of_measure,
          (SELECT l2.meaning
             FROM pa_lookups l2
            WHERE 1 = 1
              AND l2.lookup_type = 'UNIT'
              AND l2.lookup_code = et.unit_of_measure
              AND ROWNUM = 1) unit_of_measure_m,
          DECODE (ei.system_linkage_function,
                  'ST', ei.raw_cost_rate,
                  'OT', ei.raw_cost_rate,
                  ei.raw_cost_rate
                 ) raw_cost_rate,
          ei.cost_distributed_flag, ei.cost_dist_rejection_code,
          ei.labor_cost_multiplier_name,
          DECODE (ei.system_linkage_function,
                  'VI', cdl.burdened_cost,
                  cdl.amount
                 ) burdened_cost,
          DECODE (ei.system_linkage_function,
                  'ST', ei.burden_cost_rate,
                  'OT', ei.burden_cost_rate,
                  ei.burden_cost_rate
                 ) burdened_cost_rate,
          ei.cost_burden_distributed_flag, ei.ind_cost_dist_rejection_code,
          ei.cost_ind_compiled_set_id,
          DECODE (pt.project_type_class_code,
                  'CAPITAL', ei.billable_flag,
                  CAST (NULL AS VARCHAR2 (10))
                 ) capitalizable_flag,
          ei.accrued_revenue, ei.accrual_rate, ei.raw_revenue,
          ei.forecast_revenue,
          SIGN (cdl.amount) * ABS (ei.bill_amount) bill_amount, ei.bill_rate,
          ei.bill_rate_multiplier, ei.adjusted_revenue, ei.adjusted_rate,
          DECODE (pt.project_type_class_code,
                  'CAPITAL', CAST (NULL AS VARCHAR2 (10)),
                  cdl.billable_flag
                 ) billable_flag,
          DECODE (pt.project_type_class_code,
                  'CAPITAL', ei.revenue_distributed_flag,
                  CAST (NULL AS VARCHAR2 (10))
                 ) grouped_cip_flag,
          DECODE (pt.project_type_class_code,
                  'CAPITAL', CAST (NULL AS VARCHAR2 (10)),
                  ei.revenue_distributed_flag
                 ) revenue_distributed_flag,
          ei.bill_hold_flag, ei.rev_dist_rejection_code, ei.bill_job_id,
          ei.bill_job_billing_title, ei.bill_employee_billing_title,
          ei.rev_ind_compiled_set_id, ei.inv_ind_compiled_set_id,
          ei.event_num, ei.event_task_id, ei.adjusted_expenditure_item_id,
          NVL (ei.net_zero_adjustment_flag, 'N') net_zero_adjustment_flag,
          ei.transferred_from_exp_item_id, ei.converted_flag,
          ei.source_expenditure_item_id, ei.last_update_login,
          ei.last_update_date, ei.last_updated_by, ei.creation_date,
          ei.created_by, ei.attribute_category, ei.attribute1, ei.attribute2,
          ei.attribute3, ei.attribute4, ei.attribute5, ei.attribute6,
          ei.attribute7, ei.attribute8, ei.attribute9, ei.attribute10,
          DECODE (ei.org_id,
                  '1131', SUBSTR (ei.attribute8,
                                  (INSTR (ei.attribute8, '[', 1, 5)
                                  ) + 1,
                                  ((  INSTR (ei.attribute8, ']', 1, 5)
                                    - 1
                                    - (INSTR (ei.attribute8, '[', 1, 5))
                                   )
                                  )
                                 ),
                  TO_CHAR (CAST (NULL AS VARCHAR2 (10)))
                 ),
          ei.org_id, ei.org_id expenditure_org_id,
          tr.user_transaction_source || '' user_transaction_source,
          tr.allow_adjustments_flag, cast('' as varchar2(1)) transferred_item_flag,
          cdl.line_num cdl_line_num,
          cdl.transfer_status_code cdl_xfr_status_code,
          DECODE (ei.system_linkage_function,
                  'VI', cdl.amount,
                  (SIGN (cdl.amount) * ABS (ei.raw_cost))
                 ) raw_cost,
          cdl.function_transaction_code cdl_func_txn_code,
          cdl.pa_date + 0 cdl_pa_date, cdl.dr_code_combination_id cdl_dr_ccid,
          cdl.gl_date + 0 cdl_gl_date, cdl.accumulated_flag cdl_accum_flag,
          cdl.reversed_flag cdl_reversed_flag,
          cdl.line_num_reversed cdl_line_num_rev,
          NVL (cdl.system_reference1, 0) cdl_sys_ref1,
          cdl.system_reference2 cdl_sys_ref2,
          cdl.system_reference3 cdl_sys_ref3,
          cdl.cr_code_combination_id cdl_cr_ccid, cdl.line_type cdl_line_type,
          cdl.code_combination_id cdl_ccid, et.attribute8 aorl_flag,
          (SELECT hp.party_name
             FROM pa_project_customers ppc,
                  hz_cust_accounts hca,
                  hz_parties hp
            WHERE ppc.project_id = p.project_id
              AND ppc.customer_bill_split = 100
              AND hca.cust_account_id = ppc.customer_id
              AND hp.party_id = hca.party_id
              AND ROWNUM = 1) client,
          (SELECT hca.account_number
             FROM pa_project_customers ppc,
                  hz_cust_accounts hca
            WHERE ppc.project_id = p.project_id
              AND ppc.customer_bill_split = 100
              AND hca.cust_account_id = ppc.customer_id
              AND ROWNUM = 1) client_number,
          ei.project_id event_project_id,
          SUBSTR (ei.attribute2, 7, 4) office_site,
          SUBSTR (ei.attribute2, 11, 3) hovensa_mrn,
          SUBSTR (ei.attribute2, 14, 4) hovensa_crew,
          SUBSTR (ei.attribute2, 18, 2) hovensa_craft,
          SUBSTR (ei.attribute2, 20, 2) hovensa_class,
          SUBSTR (ei.attribute2, 1, 1) hovensa_shift,
          SUBSTR (emp.attribute6, 3, 8) hovensa_badge_number,
          SUBSTR (emp.attribute6, 1, 2) hovensa_frequency,
          pa_expenditures_utils.getorgtlname (ei.org_id) prvdr_org_name,
          pa_expenditures_utils.getorgtlname (ei.recvr_org_id) recvr_org_name,
          ei.receipt_currency_code, ei.receipt_exchange_rate,
          ei.receipt_currency_amount, ei.denom_currency_code,
          DECODE (ei.system_linkage_function,
                  'VI', cdl.denom_raw_cost,
                  (SIGN (cdl.amount) * ABS (ei.denom_raw_cost)
                  )
                 ) denom_raw_cost,
          DECODE (ei.system_linkage_function,
                  'VI', cdl.burdened_cost,
                  (SIGN (cdl.amount) * ABS (ei.denom_burdened_cost)
                  )
                 ) denom_burdened_cost,
          ei.acct_exchange_rounding_limit, ei.acct_currency_code,
          ei.acct_rate_type, ei.acct_rate_date, ei.acct_exchange_rate,
          DECODE (ei.system_linkage_function,
                  'VI', cdl.acct_raw_cost,
                  (SIGN (cdl.amount) * ABS (ei.acct_raw_cost)
                  )
                 ) acct_raw_cost,
          DECODE (ei.system_linkage_function,
                  'VI', cdl.burdened_cost,
                  (SIGN (cdl.amount) * ABS (ei.acct_burdened_cost)
                  )
                 ) acct_burdened_cost,
          ei.project_currency_code, ei.project_rate_type,
          ei.project_rate_date, ei.project_exchange_rate,
          DECODE (ei.system_linkage_function,
                  'VI', cdl.project_raw_cost,
                  (SIGN (cdl.amount) * ABS (ei.project_raw_cost)
                  )
                 ) project_raw_cost,
          DECODE (ei.system_linkage_function,
                  'VI', cdl.burdened_cost,
                  (SIGN (cdl.amount) * ABS (ei.project_burdened_cost)
                  )
                 ) project_burdened_cost,
          ei.cc_prvdr_organization_id, ei.cc_recvr_organization_id,
          pa_expenditures_utils.getorgtlname
                      (ei.cc_prvdr_organization_id)
                                                   cc_prvdr_organization_name,
          pa_expenditures_utils.getorgtlname
                      (ei.cc_recvr_organization_id)
                                                   cc_recvr_organization_name,
          cc_cross_charge_code, cc_cross_charge_type, cc_rejection_code,
          jeg_get_lookup ('CC_CROSS_CHARGE_CODE',
                          cc_cross_charge_code
                         ) cc_cross_charge_code_m,
          jeg_get_lookup ('CC_CROSS_CHARGE_TYPE',
                          cc_cross_charge_type
                         ) cc_cross_charge_type_m,
          jeg_get_lookup ('CC_REJECTION_CODE',
                          cc_rejection_code
                         ) cc_rejection_code_m,
          fnd_profile.VALUE ('GL_SET_OF_BKS_ID') set_of_books_id,
          (SELECT ass_attribute3
             FROM per_all_assignments_f paa
            WHERE paa.person_id = emp.person_id
              AND ei.expenditure_item_date BETWEEN effective_start_date
                                               AND effective_end_date
              AND ROWNUM = 1) jeg_assigned_pu,
          (SELECT    SUBSTR (SUBSTR (ei.attribute8,
                                     1,
                                     INSTR (ei.attribute8, '~', 1) - 1
                                    ),
                             c1.l_from1,
                             (c1.l_to1 - c1.l_from1) + 1
                            )
                  || ','
                  || SUBSTR (SUBSTR (ei.attribute8,
                                     1,
                                     INSTR (ei.attribute8, '~', 1) - 1
                                    ),
                             c1.l_from2,
                             (c1.l_to2 - c1.l_from2) + 1
                            )
                  || ','
                  || SUBSTR (SUBSTR (ei.attribute8,
                                     1,
                                     INSTR (ei.attribute8, '~', 1) - 1
                                    ),
                             c1.l_from3,
                             (c1.l_to3 - c1.l_from3) + 1
                            )
                  || ','
                  || SUBSTR (SUBSTR (ei.attribute8,
                                     1,
                                     INSTR (ei.attribute8, '~', 1) - 1
                                    ),
                             c1.l_from4,
                             (c1.l_to4 - c1.l_from4) + 1
                            )
             FROM jeg_proj_mask_map c1
            WHERE p.org_id = 3225                                     -- CA OU
              AND c1.project_id = p.project_id
              AND ei.expenditure_item_date BETWEEN c1.start_date
                                               AND NVL (c1.end_date, SYSDATE)
              AND ROWNUM = 1) jeg_exp_mask,
          DECODE (UPPER (SUBSTR (ei.expenditure_type, 1, 11)),
                  'JOB SHOPPER', UPPER (SUBSTR (emp.last_name, 1, 4)),
                  DECODE (UPPER (SUBSTR (ei.expenditure_type, 1, 21)),
                          'SHIFT DIFFERENTIAL JS', UPPER
                                                       (SUBSTR (emp.last_name,
                                                                1,
                                                                4
                                                               )
                                                       ),
                             INITCAP (SUBSTR (emp.last_name, 1, 2))
                          || SUBSTR (emp.first_name, 1, 1)
                         )
                 ) mailstop,
          jeg_get_ar_inv (p.project_id, ei.expenditure_item_id) ar_inv_num,
          ---- Ghansham 21-JUL-2008 added foe GCC
          jeg_check_service_centre_cc (ei.expenditure_item_id) service_center,
          ---- Ghansham 21-JUL-2008 added foe GCC
          ei.acct_transfer_price       ---- Ghansham 21-JUL-2008 added foe GCC
     FROM pa_projects_all p,
          pa_cost_distribution_lines_all cdl,
          pa_expenditure_items_all ei,
          pa_tasks t,
          pa_project_types_all pt,
          pa_expenditures_all x,
          hr_all_organization_units o1,
          hr_all_organization_units o2,
          per_jobs j,
          pa_expenditure_types et,
          gl_code_combinations gcc,
          pa_expenditure_comments c,
          pa_transaction_sources tr,
          per_all_people_f emp
    WHERE 1 = 1
      AND cdl.project_id = p.project_id
      AND ei.expenditure_item_id = cdl.expenditure_item_id
--AND    ei.expenditure_item_date BETWEEN TO_DATE('01-DEC-2007') AND TO_DATE('14-DEC-2007')
      AND cdl.line_type || '' =
                           DECODE (ei.system_linkage_function,
                                   'VI', 'R',
                                   'D'
                                  )
      AND (   ei.cc_cross_charge_type || '' = 'IC'
           OR ei.org_id = fnd_profile.VALUE ('ORG_ID')
          )
      AND t.task_id = ei.task_id
      AND pt.project_type = p.project_type
      AND pt.org_id = p.org_id
      AND x.expenditure_id = ei.expenditure_id
      AND o1.organization_id =
             NVL (ei.override_to_organization_id,
                  x.incurred_by_organization_id
                 )
      AND ei.organization_id = o2.organization_id(+)
      AND ei.job_id = j.job_id(+)
      AND ei.expenditure_type = et.expenditure_type
      AND et.unit_of_measure IN
                          ('DOLLARS', 'HOURS', 'MILEAGE', 'Plots', 'Samples')
      AND gcc.code_combination_id = cdl.dr_code_combination_id
      AND ei.expenditure_item_id = c.expenditure_item_id(+)
      AND ei.transaction_source = tr.transaction_source(+)
      AND x.incurred_by_person_id = emp.person_id(+)
      AND TRUNC (SYSDATE) BETWEEN emp.effective_start_date(+) AND emp.effective_end_date(+)
      AND emp.employee_number(+) IS NOT NULL;
                  Execute immediate('ALTER SESSION SET SQL_TRACE = FALSE'),