ECC Projects, Projects Costing: Transactions, SQL1

Description
Categories: Enterprise Command Center
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

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Contact us to schedule a demo or if you need help with the installation

select
x.*
from
(
select * from (select /*+ push_pred(ei) */
record_type,
ecc_spec_id,
tmp.expenditure_item_id,'N' IS_WORKTYPE_BILLABLE_ENABLED
,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,
tmp.proj_non_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.project_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,
pa_utils.get_lookup_values('COST_TYPE_DESC', cost_identifier, tmp.language) cost_identifier_desc,
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 >= '12-DEC-21'
				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 >='12-DEC-21'
				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 >= '12-DEC-21'
                           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 >= '12-DEC-21'
                                   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 >= '12-DEC-21'
                           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 >= '12-DEC-21'
                                   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,
tmp.rate_source_id,  /* V6 */
tmp.discount_percentage,
tmp.rate_disc_reason_code,
pa_utils.get_lookup_values('RATE AND DISCOUNT REASON', tmp.rate_disc_reason_code, tmp.language) rate_discount_reason,
tmp.bill_markup_percentage,
tmp.bill_group,
tmp.projfunc_bill_amount,
tmp.bill_amount,
tmp.bill_rate,
tmp.revenue_hold_flag,
pa_utils.get_lookup_values('YES_NO', tmp.revenue_hold_flag, tmp.language) revenue_hold,
tmp.accrual_rate,
tmp.inventory_item_id,
tmp.inventory_item,
-9999 event_id,
case when tmp.project_type_class_code = 'CONTRACT' and (tmp.billed_flag = 'Y' or tmp.revenue_distributed = 'Y')
     then 'EI-'||tmp.expenditure_item_id else '-9999' end ei_ev_reference,
tmp.funding_task_id,
tmp.funding_task_name,
tmp.funding_task_number,
decode(tmp.billing_project_id, null, 'N', 'Y') billproj_display_flag,
bill_chart_period_name,
display_planrev_chart,
display_totexp_chart,
billed_flag,
case when tmp.project_type_class_code = 'CONTRACT' THEN
  case when tmp.billable_flag = 'N' then 'NOBILL'
      when tmp.cost_distributed_flag = 'N' THEN 'UCST'
     when tmp.cost_distributed_flag = 'Y' THEN
     case
       when tmp.rev_dist_rejection_code is NULL and tmp.revenue_distributed in ('N', 'P')
	        THEN 'GENREV'
	   when tmp.rev_dist_rejection_code is NOT NULL and tmp.revenue_distributed in ('N', 'P')
	        THEN 'REVREJ'
       when ((tmp.inv_gen_rejection_code is NULL or (tmp.inv_gen_rejection_code = 'N' and tmp.billed_flag = 'N'))
            and (tmp.revenue_distributed = 'Y' AND tmp.billable_flag = 'Y'))
			THEN 'GENINV'
	   when (tmp.inv_gen_rejection_code is NOT NULL and tmp.inv_gen_rejection_code <> 'N' and tmp.revenue_distributed = 'Y')
	        THEN 'INVREJ'
       when tmp.billable_flag = 'Y' AND tmp.INVOICE_METHOD = 'WORK' AND tmp.billed_flag = 'Y'
	        THEN 'BILLED'
	   else NULL
     end
  else NULL
  end
else NULL
end wip_status_code,
pa_utils.get_lookup_values('PA_BILL_ECC_WIP_STATUS',
		case when tmp.project_type_class_code = 'CONTRACT' THEN
		  case when tmp.billable_flag = 'N' then 'NOBILL'
		     when tmp.cost_distributed_flag = 'N' THEN 'UCST'
			 when tmp.cost_distributed_flag = 'Y' THEN
			 case
			   when tmp.rev_dist_rejection_code is NULL and tmp.revenue_distributed in ('N', 'P')
			        THEN 'GENREV'
			   when tmp.rev_dist_rejection_code is NOT NULL and tmp.revenue_distributed in ('N', 'P')
			        THEN 'REVREJ'
			   when ((tmp.inv_gen_rejection_code is NULL or (tmp.inv_gen_rejection_code = 'N' and tmp.billed_flag = 'N'))
			        and (tmp.revenue_distributed = 'Y' AND tmp.billable_flag = 'Y'))
					THEN 'GENINV'
			   when (tmp.inv_gen_rejection_code is NOT NULL and tmp.inv_gen_rejection_code <> 'N' and tmp.revenue_distributed = 'Y')
			        THEN 'INVREJ'
			   when tmp.billable_flag = 'Y' AND tmp.INVOICE_METHOD = 'WORK' AND tmp.billed_flag = 'Y'
			        THEN 'BILLED'
			   else NULL
			 end
		  else NULL
		  end
		else NULL
		end
 , tmp.language) wip_status,
case when tmp.project_type_class_code = 'CONTRACT' and tmp.billed_flag = 'Y' and tmp.billable_flag = 'Y' THEN
     tmp.billdb_pfc_bill_amount else 0 end billable_billed_pfc_amt,
case when tmp.project_type_class_code = 'CONTRACT' and tmp.billed_flag = 'N' and tmp.billable_flag = 'Y' THEN
     tmp.billdb_pfc_bill_amount else 0 end billable_nonbilled_pfc_amt,
case when tmp.project_type_class_code = 'CONTRACT' and tmp.cost_distributed_flag = 'Y' and tmp.billable_flag = 'Y' THEN
      tmp.billdb_pfc_bill_amount else 0 end billable_pfc_amt,
case when tmp.project_type_class_code = 'CONTRACT' and tmp.projfunc_bill_amount is not null then 'N' else 'Y' end derived_bill_amt_flag,
tmp.billdb_pfc_bill_amount,
case when tmp.project_type_class_code = 'CONTRACT' and tmp.revenue_hold_flag <> 'N'
     then tmp.billdb_pfc_bill_amount else 0 end ei_revenue_hold_amt,
case when tmp.project_type_class_code = 'CONTRACT' and tmp.rev_dist_rejection_code is not null
     and tmp.revenue_distributed in ('N', 'P') and tmp.revenue_hold_flag = 'N'
     then tmp.billdb_pfc_bill_amount else 0 end ei_rev_dist_rej_amt,
case when tmp.project_type_class_code = 'CONTRACT' and tmp.inv_gen_rejection_code is not null
     and tmp.inv_gen_rejection_code <> 'N' and tmp.revenue_distributed = 'Y' and tmp.bill_hold_flag = 'N'
     then tmp.billdb_pfc_bill_amount else 0 end ei_inv_gen_rej_amt,
case when tmp.project_type_class_code = 'CONTRACT' and tmp.rev_dist_rejection_code is NULL
     and tmp.revenue_distributed in ('N', 'P') and tmp.revenue_hold_flag = 'N'
     then tmp.billdb_pfc_bill_amount else 0 end ei_pend_gen_rev_amt,
case when tmp.project_type_class_code = 'CONTRACT' and tmp.inv_gen_rejection_code is NULL and tmp.revenue_distributed = 'Y'
     and tmp.billable_flag = 'Y' and tmp.bill_hold_flag = 'N' then tmp.billdb_pfc_bill_amount else 0 end ei_pend_gen_inv_amt,
case when tmp.project_type_class_code = 'CONTRACT' and tmp.billable_flag = 'Y' and nvl(tmp.bill_hold_flag, 'N') = 'Y' then tmp.billdb_pfc_bill_amount else 0 end ei_bill_hold_amt,
    0 evt_revenue_hold_amt,
	0 evt_rev_dist_rej_amt,
	0 evt_inv_gen_rej_amt,
	0 evt_pend_gen_rev_amt,
	0 evt_pend_gen_inv_amt,
	0 evt_bill_hold_amt,
	0 evt_pfc_rev_derived_amt,
     tmp.project_id||':'||tmp.funding_task_id||':'||
               case when tmp.project_type_class_code = 'CONTRACT' and (tmp.billed_flag = 'Y' or tmp.revenue_distributed = 'Y')
                then 'EI-'||tmp.expenditure_item_id else '-9999' end DS_ASSOC_PRJ_TSK_EE_ATTR,
     tmp.project_id||':'||tmp.funding_task_id||':'||
               case when tmp.project_type_class_code = 'CONTRACT' and (tmp.billed_flag = 'Y' or tmp.revenue_distributed = 'Y')
                then 'EI-'||tmp.expenditure_item_id else '-9999' end ||':'||bill_gl_period_name DS_ASSOC_PRJ_TSK_EE_PER_ATTR,
     tmp.project_id||':'||tmp.funding_task_id||':'||bill_gl_period_name DS_ASSOC_PRJ_TSK_PER_ATTR,
     case when tmp.project_type_class_code = 'CONTRACT' and tmp.billable_flag = 'Y' and tmp.revenue_distributed <> 'N'
	 and nvl(tmp.bill_hold_flag, 'N') = 'N' and tmp.billed_flag <> 'Y' then tmp.billdb_pfc_bill_amount else 0 end ei_ready_to_bill_amt,
	 case when tmp.project_type_class_code = 'CONTRACT' and tmp.billable_flag = 'Y' and tmp.revenue_distributed = 'N'
	 and nvl(tmp.bill_hold_flag, 'N') = 'N' then tmp.billdb_pfc_bill_amount else 0 end ei_unaccrued_amt,
	 pa_ecc_bill_load_pkg.get_three_quarter_flag(tmp.set_of_books_id, tmp.bill_gl_period_name) period_disp_flag,
	 gl_period_start_date,
	 case when tmp.project_type_class_code = 'CONTRACT' and tmp.cost_distributed_flag = 'Y' and tmp.billable_flag = 'Y'
	 and tmp.revenue_distributed <> 'N' and nvl(tmp.bill_hold_flag, 'N') = 'N' and tmp.billed_flag <> 'Y'
	 then 'Y' else 'N' end ready_to_bill_flag,
	 case when tmp.project_type_class_code = 'CONTRACT' and tmp.cost_distributed_flag = 'Y' and tmp.billable_flag = 'Y'
	 and tmp.revenue_distributed = 'N' and nvl(tmp.bill_hold_flag, 'N') = 'N' then 'Y' else 'N' end ei_unaccrued_flag
 
 , 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,
ei.project_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 >= '12-DEC-21'
    ),0), 0)  proj_capitalizable,
     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 = 'N'
            AND paie.creation_date >= '12-DEC-21'
    ),0), 0)  proj_non_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 >= '12-DEC-21'
                  )),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',NU
Download
 
Blitz Report™

Blitz Report™ provides multiple benefits: