ECC Projects, Projects Costing: Transactions

Description
Categories: Enterprise Command Center
Columns: Record Type, Ecc Spec Id, Expenditure Item Id, Expenditure Id, Project Id, Project Name, Project Number, Project Type Class Code, Project Manager, Project Start Date ...
Imported from Enterprise Command Center
Description: Data set for information about expenditure items
Dataset Key: pa-ds-cost-eicdl
Query Procedure: PA_ECC_COST_PROJ_PKG.LOAD_FULL_EICDL_DATA
Security Procedure: PA_ECC_DATASECURITY_PKG.GetFilterAttributeValues
select
x.*
from
(
select * from (select /*+ push_pred(ei) */
record_type,
ecc_spec_id,
tmp.expenditure_item_id,
tmp.expenditure_id,
tmp.project_id,
project_name,
project_number,
user_project_type,
project_type_class_code,
tmp.project_type,
project_manager,
project_start_date,
project_completion_date,
project_organization,
project_status_name,
project_status,
project_budget,
task_plan_capitalizable,
task_non_plan_capitalize,
proj_plan_capitalize,
proj_non_plan_capitalize,
tmp.proj_capitalizable,
actual_capitalizable,
actual_non_capitalizable,
actual_yet_to_capitalizable,
project_revenue_budget,
proj_grouping_method,
proj_grp_supplier_invoice,
proj_asset_cost_alloc_mhd,
proj_enbl_burden_cost_acc,
current_pa_period,
current_gl_period,
current_reporting_pa_period,
tmp.projfunc_currency_code,
tmp.number_of_assets,
tmp.proj_capitalized_cost,
(proj_capitalizable-proj_capitalized_cost)proj_cip_cost,
tmp.task_id,
tmp.task_number,
tmp.task_name,
tmp.top_task_id,
tmp.top_task_name,
tmp.top_task_number,
tmp.task_start_date,
tmp.task_finish_date,
tmp.task_manager,
tmp.task_organization,
tmp.task_burden_schedule,
tmp.task_billable_flag,
tmp.task_work_type,
tmp.expenditure_item_date,
tmp.expenditure_week_ending_date,
tmp.expenditure_type,
tmp.expenditure_category,
tmp.expenditure_type_class,
tmp.incurred_by_person_id,
tmp.employee_name,
tmp.employee_number,
tmp.location_code,
tmp.job_id,
tmp.job_name,
tmp.incurred_by_organization_id,
tmp.override_to_organization_id,
tmp.expenditure_organization_id,
tmp.expenditure_org_name,
tmp.non_labor_resource,
tmp.system_linkage_function,
tmp.transaction_source_code,
nvl (tmp.transaction_source,'Projects') transaction_source,
tmp.orig_transaction_ref,
tmp.expenditure_group,
tmp.ei_pend_burden_dist,
decode(tmp.ei_pend_burden_dist, 'Y', 1, 0) ei_pend_burden_dist_count,
tmp.ei_group_unreleased,
decode(tmp.ei_group_unreleased, 'Y', 1, 0) ei_group_unreleased_count,
tmp.ei_cost_exception,
decode(tmp.ei_cost_exception, 'Y', 1, 0) ei_cost_exception_count,
tmp.ei_uncosted,
decode(tmp.ei_uncosted, 'Y', 1, 0) ei_uncosted_count,
tmp.ei_costed,
decode(tmp.ei_costed, 'Y', 1, 0) ei_costed_count,
tmp.ei_costed_value,
tmp.quantity,
tmp.burden_cost,
tmp.ei_labor_cost,
tmp.ei_non_labor_cost,
tmp.unit_of_measure,
tmp.unit_of_measure_m,
tmp.raw_cost,
tmp.raw_cost_rate,
tmp.cost_distributed_flag,
tmp.cost_distributed,
tmp.cost_dist_rejection_code,
tmp.revenue_distributed,
tmp.revenue_distributed_flag,
tmp.billed,
tmp.billable_flag,
tmp.billable,
tmp.bill_hold_flag,
tmp.bill_hold,
tmp.billed_cost,
tmp.billable_unbilled_cost,
tmp.billable_onhold_cost,
tmp.bill_billable_cost,
tmp.bill_nonbilable_flag,
tmp.bill_nonbilable_cost,
tmp.bill_billhold_cost,
tmp.bill_billed_flag,
tmp.bill_billed_cost,
tmp.bill_not_billed_flag,
tmp.bill_not_billed_cost,
tmp.ei_work_burden_cost,
tmp.burdened_cost,
tmp.burdened_cost_rate,
tmp.denom_currency_code,
tmp.denom_raw_cost,
tmp.denom_burdened_cost,
tmp.acct_currency_code,
tmp.acct_rate_type,
tmp.acct_rate_date,
tmp.acct_exchange_rate,
tmp.acct_raw_cost,
tmp.acct_burdened_cost,
cip_cost,
tmp.project_currency_code,
tmp.project_raw_cost,
tmp.project_burdened_cost,
tmp.cost_bur_distributed_flag,
tmp.capitalizable_flag,
tmp.capitalizable,
tmp.asset_line_generated,
(decode(project_type_class_code,'CAPITAL',decode(capitalizable_flag,'N',tmp.cdl_amount,0),0))non_capitalizable_cost,
(decode(project_type_class_code,'CAPITAL',decode(capitalizable_flag,'Y',tmp.cdl_amount,0),0))capitalizable_cost,
(decode(project_type_class_code,'CAPITAL',decode(capitalized_flag,'Y',tmp.cdl_amount,0),0))capitalized_cost,
tmp.capitalized_flag,
tmp.capitalized,
tmp.capitalizable_cap_hold_flag,
tmp.capitalizable_cap_hold,
tmp.adjusted_expenditure_item_id,
tmp.net_zero_adjustment_flag,
tmp.net_zero_adjustment,
tmp.transferred_from_exp_item_id,
tmp.transferred_item_flag,
tmp.cc_cross_charge_code,
tmp.cc_cross_charge_type,
tmp.cc_bl_distributed_code,
tmp.org_id,
tmp.vendor_id,
tmp.vendor_name,
tmp.document_header_id,
tmp.document_distribution_id,
tmp.document_line_number,
tmp.document_payment_id,
tmp.document_type,
tmp.document_distribution_type,
tmp.Operating_Unit,
tmp.capital_cost_code_amt,
tmp.line_num,
tmp.line_type_code,
tmp.line_type,
tmp.reversed_flag,
tmp.transfer_status_code,
tmp.pa_date,
tmp.gl_date,
tmp.recvr_pa_date,
tmp.recvr_gl_date,
tmp.pa_period_name,
tmp.gl_period_name,
tmp.recvr_pa_period_name,
tmp.recvr_gl_period_name,
tmp.acct_event_id,
tmp.amount,
tmp.cdl_burdened_cost,
tmp.cdl_amount,
tmp.cdl_acct_status,
tmp.bl_acct_status,
tmp.exp_group_status_code,
tmp.exp_group_status,
tmp.expenditure_status,
tmp.precosting_status_code,
tmp.precosting_status,
asset_name,
tmp.work_type,
po_number,
po_line_number,
receipt_number,
receipt_line_number,
invoice_no,
invoice_line_number,
po_distribution_line_number,
invoice_distribution_line_no,
debit_account,
credit_account,
accounting_source,
transfer_status,
tmp.provider_le_name,
tmp.receiver_le_name,
tmp.receiver_operating_unit,
tmp.transfer_price,
tmp.labot_tp_sch_name,
tmp.non_labor_tp_sch_name,
accounting_status_code,
project_sla_acc_status,
sla_gl_acc_status,
pre_accounting_status,
tmp.project_exchange_rate,
tmp.project_rate_date,
tmp.project_rate_type,
tmp.revenue_accrued,
tmp.burden_schedule,
tmp.transfer_rejection_reason,
tmp.cc_rejection_reason,
alert_type,
alert_text,
cost_identifier,
unaccounted,
(decode(tmp.unaccounted,'Unaccounted','Y',null))ei_unaccounted,
(decode(tmp.unaccounted,'Unaccounted',1,0))ei_unaccounted_count,
(decode(tmp.unaccounted,'Unaccounted',tmp.raw_cost,null))ei_unaccounted_cost,
accounting_exception,
project_accounted,
sla_accounted,
gl_accounted,
(decode(nvl(tmp.project_accounted,'N'),'Y',tmp.amount,0))project_accounted_cost,
(decode(nvl(tmp.sla_accounted,'N'),'Y',tmp.amount,0))sla_accounted_cost,
(decode(nvl(tmp.gl_accounted,'N'),'Y',tmp.amount,0))gl_accounted_cost,
ei_account_exception,
(decode(nvl(tmp.ei_account_exception,'N'),'Y',tmp.amount,0))ei_account_exception_cost,
ei_accounted,
(decode(nvl(tmp.ei_accounted,'N'),'Y',tmp.amount,0))ei_accounted_cost,
sla_acct_code,
language,
NVL(tmp.sla_gl_acc_status,tmp.project_sla_acc_status) exp_accounting_stage,
( SELECT
nvl( pa_utils.get_lookup_values ( 'PA_EI_ACCOUNTING_STATUS',(CASE
WHEN(xea.gl_transfer_status_code = 'Y'
OR nvl(tmp.historical_flag, 'Y') = 'Y') THEN 'GL'
WHEN xea.gl_transfer_status_code <> 'Y'
AND cdl.is_ei_final_accounted = 'Y' THEN 'PGL' /* Bug 30749534 */
END), tmp.language),
pa_utils.get_lookup_values('PA_EI_ACCOUNTING_STATUS',(CASE
WHEN  DECODE(cdl.transfer_status_code, 'X', 'PSLA', 'R', 'PSLA', 'P', 'PSLA', 'A', DECODE(cdl.is_ei_final_accounted, 'N', 'PSLA', 'SLA')) = 'PSLA'
THEN 'PSLA'
WHEN cdl.transfer_status_code NOT IN( 'V', 'G') THEN 'SLA'
END), tmp.language)
)
FROM
/* Commented for Bug#30749534
(
SELECT
        cd.*
    FROM
        (
            SELECT
                c.expenditure_item_id,
                c.transfer_status_code,
                c.acct_event_id,
                c.line_num
            FROM
                pa_cost_dist_lines_v c
            WHERE
                c.line_type IN (
                    'D'
                )
                AND c.creation_date >= '26-OCT-19'
				and (c.expenditure_item_id, c.line_num) in
				(select expenditure_item_id, max(line_num)
				from  pa_cost_dist_lines_v b
				where creation_date >='26-OCT-19'
				and line_type = 'D'
				group by b.expenditure_item_id
				)
        ) cd
) cdl,
*/
/* Added Bug#30749534 */
(
select /*+ index (pecca pa_ecc_cost_cdl_account_n2) */
pecca.expenditure_item_id,
pecca.transfer_status_code,
pecca.acct_event_id,
pecca.line_num,
pecca.is_ei_final_accounted,
pecca.project_id,
pecca.task_id
from pa_ecc_cost_cdl_account pecca
where pecca.line_type = 'D'
) cdl,
xla_ae_headers xea
WHERE
tmp.cost_distributed_flag = 'Y'
AND cdl.expenditure_item_id = tmp.expenditure_item_id
and cdl.acct_event_id is not null
AND xea.balance_type_code (+) = 'A'
AND xea.application_id (+) = 275
AND cdl.acct_event_id = xea.event_id(+)
AND tmp.set_of_books_id = xea.ledger_id(+)
/* Added for Bug#30749534 */
AND cdl.project_id = tmp.project_id
AND cdl.task_id = tmp.task_id
) acct_status_tot_bur,
decode(project_type_class_code, 'CAPITAL',
   (select case when cdl.transfer_status_code IN('P', 'A', 'V', 'T', 'R', 'G', 'B')
    AND(cdl.line_type = tmp.derive_tot_burden_flag
        OR cdl.line_type = DECODE(tmp.derive_tot_burden_flag, 'R', 'I', tmp.derive_tot_burden_flag)
       )
    AND( (cdl.line_type = 'I'
        AND cdl.transfer_status_code = 'G')
       OR cdl.line_type IN('R', 'D')
        AND cdl.transfer_status_code IN('P', 'V', 'A', 'B')
       )
    AND cdl.billable_flag = 'Y'
    and pa_utils.iseifinalaccounted(tmp.expenditure_item_id, cdl.line_num) = 'N'
   then decode(tmp.capital_cost_type_code,'R',nvl(cdl.amount, 0),'B',nvl(cdl.burdened_cost, cdl.amount),nvl(cdl.amount, 0))
   else 0
   end
   from
           (
               SELECT
                   cd.*
               FROM
                   (
                       SELECT
                           c.expenditure_item_id,
                           c.transfer_status_code,
                           c.acct_event_id,
                           c.line_num,
                           c.amount,
                           c.burdened_cost,
                           c.billable_flag,
                           c.line_type
                       FROM
                           pa_cost_dist_lines_v c
                       WHERE c.creation_date >= '26-OCT-19'
                           AND ( c.expenditure_item_id,
                                 c.line_num ) IN (
                               SELECT
                                   expenditure_item_id,
                                   MAX(line_num)
                               FROM
                                   pa_cost_dist_lines_v b
                               WHERE
                                   creation_date >= '26-OCT-19'
                                   AND line_type in ('R','D')
                               GROUP BY
                                   b.expenditure_item_id, b.line_type
                           )
                   ) cd
           ) cdl where cdl.line_type = tmp.DERIVE_TOT_BURDEN_FLAG and cdl.expenditure_item_id = tmp.expenditure_item_id)
           , 0) pending_final_accounting,
decode(project_type_class_code, 'CAPITAL',
   (select case when cdl.transfer_status_code IN('P', 'A', 'V', 'T', 'R', 'G', 'B')
    AND(cdl.line_type = tmp.derive_tot_burden_flag
        OR cdl.line_type = DECODE(tmp.derive_tot_burden_flag, 'R', 'I', tmp.derive_tot_burden_flag)
       )
    AND( (cdl.line_type = 'I'
        AND cdl.transfer_status_code = 'G')
       OR cdl.line_type IN('R', 'D')
        AND cdl.transfer_status_code IN('P', 'V', 'A', 'B')
       )
    AND cdl.billable_flag = 'Y'
	AND tmp.capitalized_flag = 'N'
    and pa_utils.iseifinalaccounted(tmp.expenditure_item_id, cdl.line_num) = 'Y'
   then decode(tmp.capital_cost_type_code,'R',nvl(cdl.amount, 0),'B',nvl(cdl.burdened_cost, cdl.amount),nvl(cdl.amount, 0))
   else 0
   end
   from
           (
               SELECT
                   cd.*
               FROM
                   (
                       SELECT
                           c.expenditure_item_id,
                           c.transfer_status_code,
                           c.acct_event_id,
                           c.line_num,
                           c.amount,
                           c.burdened_cost,
                           c.billable_flag,
                           c.line_type
                       FROM
                           pa_cost_dist_lines_v c
                       WHERE c.creation_date >= '26-OCT-19'
                           AND ( c.expenditure_item_id,
                                 c.line_num ) IN (
                               SELECT
                                   expenditure_item_id,
                                   MAX(line_num)
                               FROM
                                   pa_cost_dist_lines_v b
                               WHERE
                                   creation_date >= '26-OCT-19'
                                   AND line_type in ('R','D')
                               GROUP BY
                                   b.expenditure_item_id, b.line_type
                           )
                   ) cd
           ) cdl where cdl.line_type = tmp.DERIVE_TOT_BURDEN_FLAG and cdl.expenditure_item_id = tmp.expenditure_item_id)
           , 0) proj_cost_pend_al_gen,
tmp.asset_asgn_exists_code,
DECODE(project_type_class_code, 'CAPITAL', pa_utils.get_lookup_values('PA_ASSET_ASG_LEVEL', tmp.asset_asgn_exists_code, tmp.language)) asset_assignment_exists,
tmp.grouped_task_id,
DECODE(project_type_class_code, 'CAPITAL', decode(tmp.grouped_task_id,-9999,NULL,(select task_number from pa_tasks where task_id = tmp.grouped_task_id))) grouped_task_number,
DECODE(project_type_class_code, 'CAPITAL', decode(tmp.grouped_task_id,-9999,NULL,(select task_name from pa_tasks where task_id = tmp.grouped_task_id))) grouped_task_name , dfv2.* 
from
(
SELECT DISTINCT /*+ push_pred(ei) */
'PROJ_DS_EICDL' record_type,
'PROJ-'
|| TO_CHAR(ei.expenditure_item_id) ecc_spec_id,
ei.expenditure_item_id,
ei.expenditure_id,
ei.project_id,
ei.project_name,
ei.project_number,
ei.project_type user_project_type,
ei.project_type_class_code ,
ei.CAPITAL_COST_TYPE_CODE,
pa_utils.get_lookup_values('PROJECT TYPE CLASS', ei.project_type_class_code, ei.language) project_type,
ei.DERIVE_TOT_BURDEN_FLAG,
ei.project_manager,
ei.project_start_date,
ei.project_completion_date,
ei.project_organization,
ei.task_burden_schedule,
pa_utils.get_lookup_values('YES_NO',ei.task_billable_flag,ei.language) task_billable_flag,
ei.task_work_type,
ei.project_status_name,
ei.project_status,
(SELECT
        nvl(SUM(DECODE(ei.capital_cost_type_code, 'R', nvl(pbl.raw_cost, 0), nvl(pbl.burdened_cost, 0
        ))), 0)
    FROM
        pa_budget_versions pbv,
        pa_budget_lines pbl,
        pa_resource_assignments pra,
        pa_budget_types pbt
    WHERE
        pbv.project_id = ei.project_id
        AND pbv.budget_type_code = pbt.budget_type_code
        AND pbt.budget_amount_code = 'C'
        AND pbv.budget_status_code = 'B'
        AND pbv.current_flag = 'Y'
        AND pra.budget_version_id = pbv.budget_version_id
        AND pra.project_id = pbv.project_id
        AND pbl.budget_version_id = pra.budget_version_id
        AND pbl.resource_assignment_id = pra.resource_assignment_id
) project_budget,
(SELECT
        (decode(project_type_class_code,'CAPITAL',nvl(SUM(DECODE(ei.capital_cost_type_code, 'R', nvl(pbl.raw_cost, 0), nvl(pbl.burdened_cost, 0
        ))), 0),0))
    FROM
        pa_budget_versions pbv,
        pa_budget_lines pbl,
        pa_resource_assignments pra,
        pa_budget_types pbt
    WHERE
        pbv.project_id = ei.project_id
		AND pra.task_id = ei.task_id
		AND ei.task_billable_flag = 'Y'
        AND pbv.budget_type_code = pbt.budget_type_code
        AND pbt.budget_amount_code = 'C'
        AND pbv.budget_status_code = 'B'
        AND pbv.current_flag = 'Y'
        AND pra.budget_version_id = pbv.budget_version_id
        AND pra.project_id = pbv.project_id
        AND pbl.budget_version_id = pra.budget_version_id
        AND pbl.resource_assignment_id = pra.resource_assignment_id
) task_plan_capitalizable,
(SELECT
        (decode(project_type_class_code,'CAPITAL',nvl(SUM(DECODE(ei.capital_cost_type_code, 'R', nvl(pbl.raw_cost, 0), nvl(pbl.burdened_cost, 0
        ))), 0),0))
    FROM
        pa_budget_versions pbv,
        pa_budget_lines pbl,
        pa_resource_assignments pra,
        pa_budget_types pbt
    WHERE
        pbv.project_id = ei.project_id
		AND pra.task_id = ei.task_id
		AND ei.task_billable_flag = 'N'
        AND pbv.budget_type_code = pbt.budget_type_code
        AND pbt.budget_amount_code = 'C'
        AND pbv.budget_status_code = 'B'
        AND pbv.current_flag = 'Y'
        AND pra.budget_version_id = pbv.budget_version_id
        AND pra.project_id = pbv.project_id
        AND pbl.budget_version_id = pra.budget_version_id
        AND pbl.resource_assignment_id = pra.resource_assignment_id
) task_non_plan_capitalize,
(
(SELECT
        (decode(project_type_class_code,'CAPITAL',nvl(SUM(DECODE(ei.capital_cost_type_code, 'R', nvl(pbl.raw_cost, 0), nvl(pbl.burdened_cost, 0
        ))), 0),0))
    FROM
        pa_budget_versions pbv,
        pa_budget_lines pbl,
        pa_resource_assignments pra,
        pa_budget_types pbt,
		pa_budget_entry_methods bem
    WHERE
        pbv.project_id = ei.project_id
        AND pbv.budget_type_code = pbt.budget_type_code
        AND pbt.budget_amount_code = 'C'
        AND pbv.budget_status_code = 'B'
        AND pbv.current_flag = 'Y'
        AND pbv.budget_entry_method_code = bem.budget_entry_method_code
        and bem.entry_level_code = 'P'
        AND pra.budget_version_id = pbv.budget_version_id
        AND pra.project_id = pbv.project_id
        AND pbl.budget_version_id = pra.budget_version_id
        AND pbl.resource_assignment_id = pra.resource_assignment_id)
	+
(SELECT
        (decode(project_type_class_code,'CAPITAL',nvl(SUM(DECODE(ei.capital_cost_type_code, 'R', nvl(pbl.raw_cost, 0), nvl(pbl.burdened_cost, 0
        ))), 0),0))
    FROM
        pa_budget_versions pbv,
        pa_budget_lines pbl,
        pa_resource_assignments pra,
        pa_budget_types pbt,
		pa_budget_entry_methods bem,
        pa_tasks pt
    WHERE
        pbv.project_id = ei.project_id
		AND pra.task_id = pt.task_id
		AND pt.billable_flag = 'Y'
        AND pbv.budget_type_code = pbt.budget_type_code
        AND pbt.budget_amount_code = 'C'
        AND pbv.budget_status_code = 'B'
        AND pbv.current_flag = 'Y'
        AND pbv.budget_entry_method_code = bem.budget_entry_method_code
        and bem.entry_level_code in ( 'M', 'L', 'T')
        AND pra.budget_version_id = pbv.budget_version_id
        AND pra.project_id = pbv.project_id
        AND pbl.budget_version_id = pra.budget_version_id
        AND pbl.resource_assignment_id = pra.resource_assignment_id)
) proj_plan_capitalize,
(
SELECT
        (decode(project_type_class_code,'CAPITAL',nvl(SUM(DECODE(ei.capital_cost_type_code, 'R', nvl(pbl.raw_cost, 0), nvl(pbl.burdened_cost, 0
        ))), 0),0))
    FROM
        pa_budget_versions pbv,
        pa_budget_lines pbl,
        pa_resource_assignments pra,
        pa_budget_types pbt,
		pa_budget_entry_methods bem,
        pa_tasks pt
    WHERE
        pbv.project_id = ei.project_id
		AND pra.task_id = pt.task_id
		AND pt.billable_flag = 'N'
        AND pbv.budget_type_code = pbt.budget_type_code
        AND pbt.budget_amount_code = 'C'
        AND pbv.budget_status_code = 'B'
        AND pbv.current_flag = 'Y'
        AND pbv.budget_entry_method_code = bem.budget_entry_method_code
        and bem.entry_level_code in ( 'M', 'L', 'T')
        AND pra.budget_version_id = pbv.budget_version_id
        AND pra.project_id = pbv.project_id
        AND pbl.budget_version_id = pra.budget_version_id
        AND pbl.resource_assignment_id = pra.resource_assignment_id
) proj_non_plan_capitalize,
     DECODE(project_type_class_code, 'CAPITAL', nvl(
        (SELECT
            SUM(DECODE(ei.capital_cost_type_code, 'R', nvl(paie.raw_cost, 0), nvl(paie.burden_cost, 0) ))
        FROM
            pa_expenditure_items_all paie
        WHERE
            paie.project_id = ei.project_id
			AND paie.billable_flag = 'Y'
            AND paie.creation_date >= '26-OCT-19'
    ),0), 0)  proj_capitalizable,
(decode(project_type_class_code,'CAPITAL',decode(ei.capitalizable_flag,'Y',(nvl(DECODE(ei.capital_cost_type_code, 'R', nvl(ei.raw_cost, 0), nvl(ei.burdened_cost, 0
        )), 0)),0),0))actual_capitalizable,
(decode(project_type_class_code,'CAPITAL',decode(ei.capitalizable_flag,'N',(nvl(DECODE(ei.capital_cost_type_code, 'R', nvl(ei.raw_cost, 0), nvl(ei.burdened_cost, 0
        )), 0)),0),0))actual_non_capitalizable,
( DECODE(project_type_class_code, 'CAPITAL', DECODE(ei.capitalizable_flag, 'Y',decode(ei.capitalized_flag,'N',(nvl(DECODE(ei.capital_cost_type_code
, 'R', nvl(ei.raw_cost, 0), nvl(ei.burdened_cost, 0)), 0)),0), 0), 0) ) actual_yet_to_capitalizable,
(SELECT
        SUM(nvl(pbl.revenue,0))
    FROM
        pa_budget_versions pbv,
        pa_budget_lines pbl,
        pa_resource_assignments pra,
        pa_budget_types pbt
    WHERE
        pbv.project_id = ei.project_id
        AND pbv.budget_type_code = pbt.budget_type_code
        AND pbt.budget_amount_code = 'R'
        AND pbv.budget_status_code = 'B'
        AND pbv.current_flag = 'Y'
        AND pra.budget_version_id = pbv.budget_version_id
        AND pra.project_id = pbv.project_id
        AND pbl.budget_version_id = pra.budget_version_id
        AND pbl.resource_assignment_id = pra.resource_assignment_id
)project_revenue_budget,
ei.proj_grouping_method,
ei.proj_grp_supplier_invoice,
ei.proj_asset_cost_alloc_mhd,
pa_utils.get_lookup_values('YES_NO', ei.total_burden_flag, ei.language) proj_enbl_burden_cost_acc,
(SELECT per.period_name FROM pa_periods_all per
 where start_date in
         (select max(start_date) from pa_periods_all per1
          where per1.status in ('O','F')
          and per1.org_id = ei.org_id)
      and per.org_id = ei.org_id) current_pa_period,
(SELECT per.gl_period_name FROM pa_periods_all per
 where start_date in
         (select max(start_date) from pa_periods_all per1
          where per1.status in ('O','F')
          and per1.org_id = ei.org_id)
      and per.org_id = ei.org_id) current_gl_period,
(select per.period_name from pa_periods_all per where ei.org_id = per.org_id
 		AND per.current_pa_period_flag = 'Y') current_reporting_pa_period, /* Bug#31692951 */
ei.projfunc_currency_code,
(decode(project_type_class_code,'CAPITAL',(select count( project_asset_id ) from pa_project_assets_all ppaa where ppaa.project_id = ei.project_id),0)) number_of_assets,
(decode(project_type_class_code,'CAPITAL',nvl((select sum( current_asset_cost ) from pa_project_asset_lines_all ppal where ppal.project_id = ei.project_id and ppal.transfer_status_code = 'T'
                            		AND EXISTS(  SELECT   1
                     FROM pa_expenditure_items_all peia,pa_project_asset_line_Details ppald
                     WHERE peia.expenditure_item_id = ppald.expenditure_item_id
                     and  ppal.project_asset_line_detail_id = ppald.project_asset_line_detail_id
				     and peia.project_id = ei.project_id
                     AND peia.creation_date >= '26-OCT-19'
                  )),0),0)) proj_capitalized_cost,
ei.task_id,
ei.task_number,
ei.task_name,
ei.top_task_id,
ei.top_task_name,
ei.top_task_number,
ei.task_start_date,
ei.task_finish_date,
ei.task_manager,
ei.task_organization,
ei.expenditure_item_date,
ei.expenditure_ending_date expenditure_week_ending_date,
ei.expenditure_type,
ei.expenditure_category,
ei.expenditure_type_class,
ei.incurred_by_person_id,
ei.employee_name,
ei.employee_number,
ei.location_code,
ei.job_id,
ei.job_name,
ei.incurred_by_organization_id,
ei.override_to_organization_id,
ei.expenditure_organization_id,
ei.expenditure_org_name,
ei.non_labor_resource,
ei.system_linkage_function,
ei.transaction_source transaction_source_code,
ei.user_transaction_source transaction_source,
ei.orig_transaction_reference orig_transaction_ref,
ei.expenditure_group,
(CASE
WHEN ei.cost_distributed_flag = 'Y'
     AND ei.cost_burden_distributed_flag = 'N' THEN 'Y'
ELSE null
END)ei_pend_burden_dist,
( CASE
    WHEN ei.expenditure_group_status_code IN (
        'UPDATE_RELEASED',
        'RELEASED'
    ) THEN null
    ELSE 'Y'
END ) ei_group_unreleased,
(case when ei.cost_dist_rejection_code is not null then 'Y' else null end)ei_cost_exception,
(case when ei.expenditure_group_status_code IN (
        'UPDATE_RELEASED',
        'RELEASED'
    ) AND ei.cost_distributed_flag = 'N' then 'Y' else null end) ei_uncosted,
DECODE(ei.cost_distributed_flag, 'Y', 'Y', null) ei_costed,
DECODE(ei.cost_distributed_flag, 'Y', nvl(ei.burden_cost, 0), 0) ei_costed_value,
ei.quantity,
decode(ei.cost_distributed_flag,'Y',ei.burden_cost,0) burden_cost,
( CASE
    WHEN ei.system_linkage_function IN (
        'OT',
        'ST'
    ) THEN nvl(ei.burden_cost, 0)
    ELSE 0
END ) ei_labor_cost,
( CASE
    WHEN ei.system_linkage_function IN (
        'OT',
        'ST'
    ) THEN 0
    ELSE nvl(ei.burden_cost, 0)
END ) ei_non_labor_cost,
ei.unit_of_measure,
ei.unit_of_measure_m,
decode(ei.cost_distributed_flag,'Y',ei.raw_cost,0) raw_cost,
decode(ei.cost_distributed_flag,'Y',ei.raw_cost_rate,0) raw_cost_rate,
ei.cost_distributed_flag cost_distributed_flag,
pa_utils.get_lookup_values('YES_NO', ei.cost_distributed_flag, ei.language) cost_distributed,
pa_funds_control_utils.get_cost_rejection_reason(NVL(ei.cost_dist_rejection_code,ei.IND_COST_DIST_REJECTION_CODE),NULL) cost_dist_rejection_code,
decode(ei.project_type_class_code ,'INDIRECT',NULL,'CAPITAL',NULL,NVL(ei.revenue_distributed_flag,ei.capitalized_flag)) revenue_distributed,
pa_utils.get_lookup_values('YES_NO',decode(ei.project_type_class_code ,'INDIRECT',NULL,'CAPITAL',NULL,NVL(ei.revenue_distributed_flag,ei.capitalized_flag)), ei.language) revenue_distributed_flag,
pa_utils.get_lookup_values('YES_NO',(CASE WHEN ei.project_type_class_code = 'CONTRACT' AND bill_amount IS NOT NULL THEN 'Y'
WHEN ei.project_type_class_code = 'CONTRACT' AND bill_amount IS NULL THEN 'N'
ELSE NULL END), ei.language) billed,
ei.billable_flag  billable_flag,
pa_utils.get_lookup_values('YES_NO',ei.billable_flag,ei.language)  billable,
ei.bill_hold_flag ,
pa_utils.get_lookup_values('YES_NO',ei.bill_hold_flag,ei.language) bill_hold,
( CASE
WHEN ei.billable_flag = 'Y'
AND ei.bill_amount IS NOT NULL
AND nvl(ei.net_zero_adjustment_flag, 'N') = 'N'
AND ei.INVOICE_METHOD = 'WORK'
AND ei.cost_distributed_flag = 'Y' THEN ei.bill_amount
ELSE 0
END ) billed_cost,
( CASE
WHEN ei.billable_flag = 'Y'
AND ei.bill_amount IS NULL
AND nvl(ei.net_zero_adjustment_flag, 'N') = 'N'
AND ei.INVOICE_METHOD = 'WORK'
AND ei.cost_distributed_flag = 'Y' THEN ei.BURDEN_COST
ELSE 0
END ) billable_unbilled_cost,
( CASE
WHEN ei.billable_flag = 'Y'
AND ei.bill_amount IS NULL
AND ei.bill_hold_flag = 'Y'
AND nvl(ei.net_zero_adjustment_flag, 'N') = 'N'
AND ei.INVOICE_METHOD = 'WORK'
AND ei.cost_distributed_flag = 'Y' THEN ei.BURDEN_COST
ELSE 0
END ) billable_onhold_cost,
ei.bill_billable_cost,
pa_utils.get_lookup_values('YES_NO',ei.bill_nonbilable_flag,ei.language) bill_nonbilable_flag,
ei.bill_nonbilable_cost