[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
ei_update_count NUMBER ; /*2933915*/
cdl_update_count NUMBER ; /*2933915*/
Cursor projects_with_eb IS /* S.N. Bug 3618193 Updated Cursor Name */
select p.project_id
, p.segment1
, p.org_id
, upper(nvl(pt.burden_account_flag,'N')) burden_account_flag
, pt.burden_sum_dest_project_id dest_project_id
, pt.burden_sum_dest_task_id dest_task_id
, upper(pt.burden_amt_display_method) burden_amt_display_method
from pa_projects_all p, -- pa_projects_all changed to pa_projects for Bug# 5743708 /*pa_projects is changed to pa_projects_all for the bug 6610145*/
pa_project_types_all pt -- pa_project_types_all changed to pa_project_types for Bug# 5743708 /*pa_project_types is changed to pa_project_types_all for the bug 6610145*/
where pt.project_type = p.project_type
and p.segment1 between p_start_project_number and p_end_project_number /*2255068*/
and ( pt.burden_amt_display_method in ('D','d') or
pt.burden_amt_display_method in ('S','s') and
pt.burden_account_flag in ('Y','y'))
and pt.org_id = p.org_id /*5368274*/
/* Bug#3033030 Added the following to check if the project status allows creation of
burden trasanction */
and pa_project_utils.Check_prj_stus_action_allowed(p.project_status_code, 'GENERATE_BURDEN') = 'Y'
and (exists (select 1
/* Removed ei ,pa_tasks table and changed cdl_all table to cdl view for bug# 1668634 */
from pa_cost_distribution_lines cdl,
/*2255068*/ pa_expenditure_items ei
where cdl.line_type = 'R'
and nvl(cdl.amount,0) <> 0
and cdl.burden_sum_source_run_id = init_cdl_run_id
and cdl.project_id = p.project_id
and ei.expenditure_item_id = cdl.expenditure_item_id
and ei.expenditure_item_date <= nvl(l_end_date,ei.expenditure_item_date)
)
or exists (select 1
from pa_cost_distribution_lines cdl,
pa_expenditure_items ei
where cdl.line_type = 'R'
and nvl(cdl.amount,0) <> 0
and cdl.burden_sum_source_run_id >0
and nvl(cdl.reversed_flag,'N') = 'N'
and cdl.line_num_reversed IS NULL
and ei.adjustment_type ='BURDEN_RESUMMARIZE'
and cdl.project_id = p.project_id
and ei.project_id = p.project_id /* Bug# 5743708 */
and ei.expenditure_item_id = cdl.expenditure_item_id
and ei.expenditure_item_date <= nvl(l_end_date,ei.expenditure_item_date))
);
select p.project_id
, p.segment1
, p.org_id
, upper(nvl(pt.burden_account_flag,'N')) burden_account_flag
, pt.burden_sum_dest_project_id dest_project_id
, pt.burden_sum_dest_task_id dest_task_id
, upper(pt.burden_amt_display_method) burden_amt_display_method
from pa_projects_all p, -- Bug # 5743708 changing pa_projects_all to pa_projects /*changed pa_projects to pa_projects_all for the bug 6610145 */
pa_project_types_all pt -- Bug # 5743708 changing pa_project_typees_all to pa_project_types /*changed pa_project_types to pa_project_types_all for the bug 6610145 */
where pt.project_type = p.project_type
and p.segment1 between p_start_project_number and p_end_project_number /*2255068*/
and ( pt.burden_amt_display_method in ('D','d') or
pt.burden_amt_display_method in ('S','s') and
pt.burden_account_flag in ('Y','y'))
and pt.org_id = p.org_id /*5368274*/
/* Bug#3033030 Added the following to check if the project status allows creation of
burden trasanction */
and pa_project_utils.Check_prj_stus_action_allowed(p.project_status_code, 'GENERATE_BURDEN') = 'Y'
and (( x_end_date is not null and exists (select 1
/* Removed ei ,pa_tasks table and changed cdl_all table to cdl view for bug# 1668634 */
/* Removed rownum=1 condition for the bug 4527643 (Basebug#4391999) */
from pa_cost_distribution_lines cdl,
/*2255068*/ pa_expenditure_items ei
where cdl.line_type = 'R'
and nvl(cdl.amount,0) <> 0
and cdl.burden_sum_source_run_id = init_cdl_run_id
and cdl.project_id = p.project_id
and ei.expenditure_item_id = cdl.expenditure_item_id
/*2255068*/ and ei.expenditure_item_date <= l_end_date
)
) OR x_end_date is null and exists (select 1
from pa_cost_distribution_lines cdl
where cdl.line_type = 'R'
and nvl(cdl.amount,0) <> 0
and cdl.burden_sum_source_run_id = init_cdl_run_id
and cdl.project_id = p.project_id
)
);
* Inclued attribute2 - attibute10 and attribute_category in select clause of the bcc_cur
*/
/*Bug# 2368916: Added the hint ordered in the cursor below to ensure that the
tables (mainly:PA_COST_BASE_EXP_TYPES) are accessed in the order
in which it appears in the base view -pa_cdl_burden_detail_v*/
/*========================================================================================+
| 03-Feb-2004 - M - The grouping used for budgetory control projects and non-budgetory |
| control projects are different. This is, to enabled funds-check for change in burden |
| cost following burden schedule recompilation for projects with budgetory control |
| enabled. The additional grouping is by the adjustment type (=BURDEN_RESUMMARIZE). |
+========================================================================================*/
cursor bcc_cur (p_proj_bc_enabled IN VARCHAR2) is
select source_project_id
,source_task_id
,source_org_id
,source_pa_date
,source_attribute1
,source_attribute2
,source_attribute3
,source_attribute4
,source_attribute5
,source_attribute6
,source_attribute7
,source_attribute8
,source_attribute9
,source_attribute10
,source_attribute_category
,source_person_type
,source_po_line_id
,source_adjustment_type
,source_ind_cost_code
,source_expenditure_type
,source_ind_expenditure_type
,source_cost_base
,source_compiled_multiplier
-- ,source_ind_rate_sch_id
-- ,source_ind_rate_sch_rev_id
,source_exp_item_id
,source_line_num
,source_exp_item_date
,source_burden_cost
,source_denom_burdened_cost
,source_acct_burdened_cost
,source_project_burdened_cost
,source_projfunc_currency_code
,source_denom_currency_code
,source_acct_currency_code
,source_project_currency_code
,source_id
,source_burden_reject_code
/*,dest_project_id
,dest_task_id :Commented for :3069632*/
,dest_org_id
,dest_pa_date
,dest_attribute1
,dest_ind_expenditure_type
,billable_flag /* Added for bug 2091559*/
,dest_summary_group_resum dest_summary_group_Y /* Added for bug 5743708*/
,dest_summary_group dest_summary_group_N /* Added for bug 5743708*/
,source_request_id /*Bug# 2161261*/
,source_system_linkage_function /* 4057874 */
,source_job_id /* 4057874 */
,source_nl_resource /* 4057874 */
,source_nl_resource_orgn_id /* 4057874 */
,source_wip_resource_id /* 4057874 */
,source_incurred_by_person_id /* 4057874 */
,source_inventory_item_id /* 4057874 */
,source_vendor_id /* 4057874 */
,src_acct_rate_date
,src_acct_rate_type
,src_acct_exchange_rate
,src_project_rate_date
,src_project_rate_type
,src_project_exchange_rate
,src_projfunc_cost_rate_date
,src_projfunc_cost_rate_type
,src_projfunc_cost_xchng_rate
from pa_cdl_burden_summary_v
order by DECODE(p_proj_bc_enabled, 'Y', dest_summary_group_resum, dest_summary_group), source_exp_item_date;
/******2933915:Cursor to select attributes for deriving new compiled set ids for the 'Special eis/cdls':
By SPECIAL eis we mean the one having corresponding summarized cdls and which are marked with adjsutment type
:BURDEN_RESUMMARIZE by the burden compilation process when the profile option PA_ENHANCED_BURDENING is 'Y'****/
/*Bug# 3040724 :We need to derive new compile set id even when burden_sum_source_run_id =-9999 and
adjustment_type =:BURDEN_RESUMMARIZE*/
CURSOR get_compile_cursor(l_project_id NUMBER )
IS
select ei.expenditure_item_id , ei.task_id,nvl(ei.override_to_organization_id,e.incurred_by_organization_id) organization_id ,
ei.expenditure_item_date, ei.expenditure_type ,
e.person_type person_type,
e.incurred_by_person_id
from pa_cost_distribution_lines cdl,
pa_expenditure_items ei,
pa_expenditures e /*3040724*/
where cdl.line_type = 'R'
and nvl(cdl.amount,0) <> 0
and ((cdl.burden_sum_source_run_id >0
and cdl.prev_ind_compiled_set_id is NOT NULL) /*2933915*/
OR cdl.burden_sum_source_run_id = init_cdl_run_id) /*3040724*/
and cdl.request_id = x_request_id
and ei.request_id = x_request_id /*2933915*/
and nvl(cdl.reversed_flag,'N') = 'N'
and cdl.line_num_reversed IS NULL
and ei.adjustment_type ='BURDEN_RESUMMARIZE'
and ei.project_id = l_project_id /* Bug# 5406802 */
and cdl.burden_sum_rejection_code ='IN_PROCESS' /*2933915*/
and cdl.project_id = l_project_id
and ei.expenditure_item_id = cdl.expenditure_item_id
and ei.expenditure_id = e.expenditure_id
and ei.expenditure_item_date <= nvl(l_end_date,ei.expenditure_item_date);/*5743708*/
pa_cc_utils.log_message('create_burden_expenditure_item: ' || '100:Select Org_id from Implementations');
select org_id
into x_org_id
from pa_implementations;
pa_cc_utils.log_message('Create_Burden_Expenditure_Item: ' || '100:Select Current Org_id.');
* Select the profile set size for CDLs per batch.
*/
FND_PROFILE.GET('PA_NUM_CDL_PER_SET', l_profile_set_size );
* select SUBSTRB(meaning,1,6), pa_burden_sum_run_s.nextval
* into exp_group, current_run_id
* from pa_lookups
* where lookup_type = 'BURDEN_ACCOUNTING'
* and lookup_code = 'BS'
* and sysdate between start_date_active and nvl(end_date_active,sysdate);
l_tbl_project_id.delete;
l_tbl_segment1.delete;
l_tbl_org_id.delete;
l_tbl_burden_account_flag.delete;
l_tbl_dest_project_id.delete;
l_tbl_dest_task_id.delete;
l_tbl_burden_amt_disp_method.delete;
stage := 120; -- in CDL update for project/task null
pa_cc_utils.log_message('create_burden_expenditure_item: ' || '450:before update CDL for error:project_task_null');
update pa_cost_distribution_lines cdl
set burden_sum_rejection_code = 'PROJECT_TASK_NULL'
where cdl.line_type = 'R'
and nvl(cdl.amount,0) <> 0
and (cdl.burden_sum_source_run_id = init_cdl_run_id
OR
(cdl.burden_sum_source_run_id >0 /*2933915*/
and nvl(cdl.reversed_flag,'N') = 'N' /*2933915*/
and cdl.line_num_reversed IS NULL )) /*2933915*/
and cdl.project_id = current_project_id
and nvl(cdl.burden_sum_rejection_code, 'ABC') <> 'PROJECT_TASK_NULL' /*2255068*/
and exists ( select NULL
from pa_expenditure_items_all ei
where ei.expenditure_item_id = cdl.expenditure_item_id
and ei.expenditure_item_date <= l_end_date
)
and rownum <= l_profile_set_size ;
update pa_cost_distribution_lines cdl
set cdl.burden_sum_rejection_code = 'PROJECT_TASK_NULL'
where cdl.line_type = 'R'
and nvl(cdl.amount,0) <> 0
and (cdl.burden_sum_source_run_id = init_cdl_run_id
OR
(cdl.burden_sum_source_run_id >0 /*2933915*/
and nvl(cdl.reversed_flag,'N') = 'N' /*2933915*/
and cdl.line_num_reversed IS NULL )) /*2933915*/
and cdl.project_id = current_project_id
and NVL(cdl.burden_sum_rejection_code, 'ABC') <> 'PROJECT_TASK_NULL'
and rownum <= l_profile_set_size ;
pa_cc_utils.log_message('create_burden_expenditure_item: ' || '500:after update CDL for error:project_task_null');
pa_cc_utils.log_message('create_burden_expenditure_item: ' || '550:CDL update:Others');
select attribute1
into l_attribute1
from pa_projects_all
where project_id = proj_rec.project_id
for update of attribute1 nowait;
/*This is to insert audit records for affected cdls before starting with summarisation.
Since the update immediately after this loop is updating burden_sum_source_run_id so we have to insert audit record before
at this point to identify original burden sum source run id to insert in audit table */
/************ MOVED THE CODE HERE WHICH WAS AFTER THE IF-ENDIF LOGIC (5406802)***********/
/*Bug#2255068: Run ids are generated once for each batch*/
select SUBSTRB(meaning,1,6), pa_burden_sum_run_s.nextval
into exp_group, current_run_id
from pa_lookups
where lookup_type = 'BURDEN_ACCOUNTING'
and lookup_code = 'BS'
and sysdate between start_date_active and nvl(end_date_active,sysdate);
pa_cc_utils.log_message('create_burden_expenditure_item: ' || '603:Update CDL with prev_ind_compiled_set_id');
l_tbl_eiid.delete;
l_tbl_cdlln.delete;
UPDATE pa_cost_distribution_lines cdl
SET cdl.prev_ind_compiled_set_id = decode(cdl.burden_sum_source_run_id,init_cdl_run_id,NULL
,cdl.ind_compiled_set_id), /*3071338*/
request_id = x_request_id,
burden_sum_rejection_code = 'IN_PROCESS' /*2933915:Stamping it for intermediate processing*/
where cdl.line_num_reversed is null
and nvl(cdl.reversed_flag,'N') = 'N'
and cdl.line_type = 'R'
and nvl(cdl.amount,0) <>0
/* and cdl.burden_sum_source_run_id > 0 :3040724*/
/* and cdl.burden_sum_rejection_code IS NULL 3040274 -Commented to process rejected cdls of previous runs*/
and cdl.project_id = current_project_id
and cdl.request_id <>x_request_id /*2933915*/
and exists (select null
from pa_expenditure_items ei
where ei.adjustment_type = 'BURDEN_RESUMMARIZE'
and ei.project_id = current_project_id /*5406802*/
and ei.expenditure_item_id = cdl.expenditure_item_id
and ei.expenditure_item_date <= nvl(l_end_date,ei.expenditure_item_date))
and rownum <= l_profile_set_size /*2933915*/
returning expenditure_item_id, line_num bulk collect into l_tbl_eiid, l_tbl_cdlln; /* Bug# 5406802 */
/* Changed this update for Bug# 5406802 */
FORALL I in 1..l_tbl_eiid.count
UPDATE pa_expenditure_items ei
set ei.request_id = x_request_id
where ei.adjustment_type = 'BURDEN_RESUMMARIZE'
and ei.project_id = current_project_id
and ei.request_id <> x_request_id /*2933915*/
and ei.expenditure_Item_id = l_tbl_eiid(i);
/*To get compiled set for special cdls and update on ei and cdl*/
IF P_DEBUG_MODE THEN
pa_cc_utils.log_message('create_burden_expenditure_item: ' || '605:Deriving compiled set id for special eis');
l_eiid_tbl.Delete;
l_task_id_tbl.Delete;
l_org_id_tbl.Delete;
l_exp_item_date_tbl.Delete;
l_exp_type_tbl.Delete;
l_stage_tbl.Delete;
l_status_tbl.Delete;
l_compiled_multiplier_tbl.Delete;
l_compiled_set_id_tbl.Delete;
l_cp_structure_tbl.Delete;/*5743708*/
l_cost_base_tbl.Delete;/*5743708*/
pa_cc_utils.log_message('create_burden_expenditure_item: ' || '607 STATUS :Update eis/cdls ');/*5980459*/
update pa_cost_distribution_lines cdl
set cdl.burden_sum_rejection_code = reason(i),
cdl.prev_ind_compiled_set_id =NULL
where cdl.request_id = x_request_id
AND cdl.project_id = current_project_id
AND cdl.burden_sum_rejection_code ='IN_PROCESS'
AND cdl.expenditure_item_id = l_eiid_tbl(i)
AND (l_status_tbl(i) <> 0) AND (l_stage_tbl(i) <> 400 or l_status_tbl(i) <> 100);
/*2933915 : Update affected ei with the newly derived compiled set_id */
FORALL i in 1..l_eiid_tbl.count
UPDATE pa_expenditure_items ei
SET cost_ind_compiled_set_id = l_compiled_set_id_tbl(i)
where ei.task_id =l_task_id_tbl(i)
AND ei.expenditure_type =l_exp_type_tbl(i)
AND trunc(ei.expenditure_item_date) =trunc(l_exp_item_date_tbl(i))
AND ei.request_id = x_request_id /*2933915*/
AND ei.expenditure_item_id = l_eiid_tbl(i)
AND ((l_status_tbl(i) = 0) OR (l_status_tbl(i) =100 AND l_stage_tbl(i) =400)) ;
/*2933915 : Update affected cdl with the newly derived compiled set_id and burden sum source run id as -9999*/
FORALL i in 1..l_eiid_tbl.count
UPDATE pa_cost_distribution_lines cdl
set ind_compiled_set_id = l_compiled_set_id_tbl(i) ,
burden_sum_source_run_id = -9999 ,
burden_sum_rejection_code = NULL
where cdl.request_id = x_request_id
AND cdl.project_id = current_project_id
/* AND cdl.prev_ind_compiled_set_id is NOT NULL Commented for 3040724*/
AND cdl.burden_sum_rejection_code ='IN_PROCESS' /*2993915*/
AND cdl.expenditure_item_id = l_eiid_tbl(i)
AND ((l_status_tbl(i) = 0) OR (l_status_tbl(i) =100 AND l_stage_tbl(i) =400));
/* Bug 5896943: Inserting prvdr_accrual_date in place of expenditure_item_date
for period accrual transactions so that the reversal BTC's will be in the future period.
*/
/* Insert into global temp table with the Ei's selected by get_compile_cursor Bug# 5406802 */
/* Modified expenditure item date for 5907315*/
FORALL i in 1..l_eiid_tbl.count
insert into PA_EI_CDL_CM_GTEMP(
PROJECT_ID ,TASK_ID ,ORGANIZATION_ID
,PA_DATE ,PA_PERIOD_NAME ,ATTRIBUTE1
,ATTRIBUTE2 ,ATTRIBUTE3 ,ATTRIBUTE4
,ATTRIBUTE5 ,ATTRIBUTE6 ,ATTRIBUTE7
,ATTRIBUTE8 ,ATTRIBUTE9 ,ATTRIBUTE10
,ATTRIBUTE_CATEGORY ,PERSON_TYPE ,PO_LINE_ID
,SYSTEM_LINKAGE_FUNCTION ,EI_EXPENDITURE_TYPE ,IND_COMPILED_SET_ID
,PREV_IND_COMPILED_SET_ID ,EXPENDITURE_ITEM_ID ,LINE_NUM
,EXPENDITURE_ITEM_DATE ,CDL_AMOUNT ,CDL_PROJFUNC_CURRENCY_CODE
,CDL_DENOM_RAW_COST ,CDL_DENOM_CURRENCY_CODE ,CDL_ACCT_RAW_COST
,CDL_ACCT_CURRENCY_CODE ,CDL_PROJECT_RAW_COST ,CDL_PROJECT_CURRENCY_CODE
,BURDEN_SUM_SOURCE_RUN_ID ,BURDEN_SUM_REJECTION_CODE ,SYSTEM_REFERENCE1
,DENOM_CURRENCY_CODE ,ACCT_CURRENCY_CODE ,PROJECT_CURRENCY_CODE
,PROJFUNC_CURRENCY_CODE ,BILLABLE_FLAG ,REQUEST_ID
,ADJUSTMENT_TYPE ,JOB_ID ,NON_LABOR_RESOURCE
,NON_LABOR_RESOURCE_ORGN_ID ,WIP_RESOURCE_ID ,INCURRED_BY_PERSON_ID
,INVENTORY_ITEM_ID ,COST_PLUS_STRUCTURE ,COST_BASE
,ORG_ID ,ACCT_RATE_DATE ,ACCT_RATE_TYPE
,ACCT_EXCHANGE_RATE ,PROJECT_RATE_DATE ,PROJECT_RATE_TYPE
,PROJECT_EXCHANGE_RATE ,PROJFUNC_COST_RATE_DATE ,PROJFUNC_COST_RATE_TYPE
,PROJFUNC_COST_EXCHANGE_RATE)
select
cdl.project_id ,cdl.task_id ,l_org_id_tbl(i)
,cdl.pa_date ,decode(cdl.prev_ind_compiled_set_id, null, cdl.pa_period_name
,nvl(pa_utils2.get_pa_period_name(ei.expenditure_item_date, ei.org_id), cdl.pa_period_name))
, ei.attribute1
, ei.attribute2 , ei.attribute3 , ei.attribute4
, ei.attribute5 , ei.attribute6 , ei.attribute7
, ei.attribute8 , ei.attribute9 , ei.attribute10
, ei.attribute_category , l_person_type_tbl(i) , ei.po_line_id
, ei.system_linkage_function , ei.expenditure_type , cdl.ind_compiled_set_id
, cdl.prev_ind_compiled_set_id , ei.expenditure_item_id , cdl.line_num
, decode(NVL(fnd_profile.value_specific('PA_REVENUE_ORIGINAL_RATE_FORRECALC'),'N'),'N',nvl(ei.prvdr_accrual_date,ei.expenditure_item_date),ei.expenditure_item_date),
cdl.amount , cdl.projfunc_currency_code
, cdl.denom_raw_cost , cdl.denom_currency_code , cdl.acct_raw_cost
, cdl.acct_currency_code , cdl.project_raw_cost , cdl.project_currency_code
, current_run_id , cdl.burden_sum_rejection_code , cdl.system_reference1
, ei.denom_currency_code , ei.acct_currency_code , ei.project_currency_code
, ei.projfunc_currency_code , cdl.billable_flag , cdl.request_id
, DECODE(ei.system_linkage_function, 'VI', ei.adjustment_type
, DECODE(ei.po_line_id, NULL, NULL, ei.adjustment_type) ) adjustment_type
, ei.job_id , ei.non_labor_resource
, ei.organization_id NON_LABOR_RESOURCE_ORGN_ID , ei.wip_resource_id , l_incur_per_id_tbl(i)
, ei.inventory_item_id ,l_cp_structure_tbl(i) ,l_cost_base_tbl(i)
, ei.org_id ,CDL.ACCT_RATE_DATE ,CDL.ACCT_RATE_TYPE
,CDL.ACCT_EXCHANGE_RATE ,CDL.PROJECT_RATE_DATE ,CDL.PROJECT_RATE_TYPE
,CDL.PROJECT_EXCHANGE_RATE ,CDL.PROJFUNC_COST_RATE_DATE ,CDL.PROJFUNC_COST_RATE_TYPE
,CDL.PROJFUNC_COST_EXCHANGE_RATE
FROM
PA_COST_DISTRIBUTION_LINES_ALL CDL,
PA_EXPENDITURE_ITEMS EI
WHERE ei.expenditure_item_id = l_eiid_tbl(i)
AND cdl.expenditure_item_id = ei.expenditure_item_id
AND cdl.request_id = x_request_id
AND cdl.project_id = current_project_id
AND cdl.burden_sum_rejection_code is NULL
AND cdl.line_type = 'R'
AND ( ei.transaction_source IS NULL
or pa_utils2.get_ts_allow_burden_flag(ei.transaction_source)<>'Y' );
pa_cc_utils.log_message('create_burden_expenditure_item: ' || '606:Update CDL with rejection reason ');
update pa_cost_distribution_lines cdl
set cdl.burden_sum_rejection_code = reason,
cdl.prev_ind_compiled_set_id =NULL
where cdl.request_id = x_request_id
AND cdl.project_id = current_project_id
/* AND cdl.prev_ind_compiled_set_id is NOT NULL :3040724
AND cdl.burden_sum_rejection_code ='IN_PROCESS'
AND cdl.expenditure_item_id in (select ei.expenditure_item_id
from pa_expenditure_items_all ei,
pa_expenditures_all e
where e.expenditure_id =ei.expenditure_id
AND ei.task_id =rec.task_id
AND ei.expenditure_type =rec.expenditure_type
AND ei.expenditure_item_date =rec.expenditure_item_date
AND rec.organization_id = nvl(ei.override_to_organization_id,
e.incurred_by_organization_id)
AND ei.adjustment_type = 'BURDEN_RESUMMARIZE'
);
/*2933915 : Update affected ei with the newly derived compiled set_id
IF P_DEBUG_MODE THEN
pa_cc_utils.log_message('create_burden_expenditure_item: ' || '607:Update eis/cdls with newly derived compiled set id ');
UPDATE pa_expenditure_items ei
SET cost_ind_compiled_set_id = l_compiled_set_id
where ei.adjustment_type = 'BURDEN_RESUMMARIZE'
AND ei.task_id =rec.task_id
AND ei.expenditure_type =rec.expenditure_type
AND ei.project_id =current_project_id
AND trunc(ei.expenditure_item_date) =trunc(rec.expenditure_item_date)
AND ei.request_id = x_request_id /*2933915
AND exists (select 1
from pa_expenditures e,
pa_cost_distribution_lines cdl
where e.expenditure_id =ei.expenditure_id
AND cdl.expenditure_item_id =ei.expenditure_item_id
AND cdl.burden_sum_rejection_code ='IN_PROCESS'
AND nvl(ei.override_to_organization_id,e.incurred_by_organization_id)=rec.organization_id);
/*2933915 : Update affected cdl with the newly derived compiled set_id and burden sum source run id as -9999
UPDATE pa_cost_distribution_lines cdl
set ind_compiled_set_id = l_compiled_set_id ,
burden_sum_source_run_id = -9999 ,
burden_sum_rejection_code = NULL
where cdl.request_id = x_request_id
AND cdl.project_id = current_project_id
/* AND cdl.prev_ind_compiled_set_id is NOT NULL Commented for 3040724
AND cdl.burden_sum_rejection_code ='IN_PROCESS' /*2993915
AND cdl.expenditure_item_id in (select ei.expenditure_item_id
from pa_expenditure_items_all ei,
pa_expenditures_all e
where e.expenditure_id =ei.expenditure_id
AND ei.task_id =rec.task_id
AND ei.expenditure_type =rec.expenditure_type
AND ei.expenditure_item_date =rec.expenditure_item_date
AND rec.organization_id = nvl(ei.override_to_organization_id,e.incurred_by_organization_id)
AND ei.adjustment_type = 'BURDEN_RESUMMARIZE'
);
* The following update is modified to update a set of CDLs to the current_run_id.
* The cdl_burden_detail_v picks up CDLs of this run_id.
* Also this update is modified to update cdls on the basis of ei date(and not on
* pa_date.
*/
IF ( X_end_date IS NOT NULL )
THEN
/* Removed ei table and pa_tasks table and changed cdl_all table to cdl view for
bug# 1668634
*/
update pa_cost_distribution_lines cdl
set burden_sum_rejection_code = NULL,
request_id = x_request_id, /*2161261*/
burden_sum_source_run_id = current_run_id
where cdl.line_type = 'R'
and nvl(cdl.amount,0) <> 0
and cdl.burden_sum_source_run_id = init_cdl_run_id
and (burden_sum_rejection_code = 'IN_PROCESS' or /*Added for the bug#5949107*/
request_id <> x_request_id or
burden_sum_rejection_code is null)
and cdl.project_id = current_project_id
and exists
(select null
from pa_expenditure_items_all ei
where ei.expenditure_item_id = cdl.expenditure_item_id
and ei.expenditure_item_date <= l_end_date
)
and rownum <= l_profile_set_size;
update pa_cost_distribution_lines cdl
set burden_sum_rejection_code = NULL,
request_id = x_request_id, /*2161261*/
burden_sum_source_run_id = current_run_id
where cdl.line_type = 'R'
and nvl(cdl.amount,0) <> 0
and (burden_sum_rejection_code = 'IN_PROCESS' or /*Added for the bug#5949107*/
request_id <> x_request_id or
burden_sum_rejection_code is null)
and cdl.burden_sum_source_run_id = init_cdl_run_id
and cdl.project_id = current_project_id
and rownum <= l_profile_set_size;
* The following update ensures that all CDLs of an EI gets processed
* in the same batch.
*/
update pa_cost_distribution_lines cdl
set burden_sum_rejection_code = NULL
,request_id = x_request_id
,burden_sum_source_run_id = current_run_id
where cdl.line_type = 'R'
and nvl(cdl.amount,0) <> 0
and cdl.burden_sum_source_run_id = init_cdl_run_id
and cdl.project_id = current_project_id
and (burden_sum_rejection_code = 'IN_PROCESS' or /*Added for the bug#5949107*/
request_id <> x_request_id or
burden_sum_rejection_code is null)
and exists
(select NULL
from pa_cost_distribution_lines cdl1
where cdl1.burden_sum_source_run_id+0 = current_run_id
and cdl1.burden_sum_rejection_code is NULL /*3071338*/
and cdl1.expenditure_item_id= cdl.expenditure_item_id) ;
* due to the above update.
*/
exception
when resource_busy then
IF P_DEBUG_MODE THEN
pa_cc_utils.log_message('create_burden_expenditure_item: ' || '700:Lock the CDLs:Resource_busy');
update_gtemp(x_request_id); /*Added for the bug#5949107*/
l_bcc_rec.delete;
pa_cc_utils.log_message('create_burden_expenditure_item: ' || '950:call to insertexpgroup');
pa_transactions.InsertExpGroup(exp_group,'APPROVED',sysdate,'BTC',0,NULL,NULL,G_MOAC_ORG_ID);
pa_cc_utils.log_message('create_burden_expenditure_item: ' || '1000:back from insertexpgroup');
select expenditure_id,incurred_by_organization_id
into exp_id,exp_org_id
from pa_expenditures_all
where expenditure_group = exp_group
and incurred_by_organization_id = bcc_rec.source_org_id
and NVL(incurred_by_person_id,-99) = nvl(bcc_rec.source_incurred_by_person_id,-99) -- changes done for 4324340 . Bug 4115096 and added NVL By 4282553
and expenditure_ending_date = l_next_weekend_date; -- Bug 3551106
select pa_expenditures_s.nextval
into exp_id from dual;
pa_cc_utils.log_message('create_burden_expenditure_item: ' || '1200:Call InsertExp');
pa_transactions.InsertExp(
x_expenditure_id =>exp_id,
x_expend_status =>'APPROVED',
x_expend_ending => pa_utils.NewGetWeekEnding((bcc_rec.source_exp_item_date)), --Bug 2236707,3551106
-- x_expend_ending =>pa_utils.NewGetWeekEnding(pa_utils2.get_pa_period_end_date_OU(bcc_rec.source_pa_date)-6), -- Bug 2933915,3551106
x_expend_class => 'BT',
x_inc_by_person => bcc_rec.source_incurred_by_person_id, -- 4057874
x_inc_by_org => bcc_rec.source_org_id,
x_expend_group => exp_group,
x_entered_by_id =>exp_org_id,
x_created_by_id =>0,
x_attribute_category => null,
x_attribute1 => null,
x_attribute2 => null,
x_attribute3 => null,
x_attribute4 => null,
x_attribute5 => null,
x_attribute6 => null,
x_attribute7 => null,
x_attribute8 => null,
x_attribute9 => null,
x_attribute10=> null,
x_description=> null,
x_control_total=> null,
x_denom_currency_code =>bcc_rec.source_denom_currency_code,
x_acct_currency_code => bcc_rec.source_acct_currency_code,
x_acct_rate_type => null,
x_acct_rate_date => null,
x_acct_exchange_rate=> null
,X_person_type => bcc_rec.source_person_type
,X_vendor_id => bcc_rec.source_vendor_id -- 4057874
,P_Org_Id => G_MOAC_ORG_ID
);
pa_cc_utils.log_message('create_burden_expenditure_item: ' || '1250:Back from InsertExp');
select pa_expenditure_items_s.nextval
into exp_item_id from dual;
select pa_expenditure_items_s.nextval
into exp_item_id from dual;
* Commented this update as this update is no more required.
*
*
* ** Update those CDLs which are successfully processed with run id
*
* pa_cc_utils.log_message('2400:Update successful CDLs');
* update pa_cost_distribution_lines cdl
* set burden_sum_source_run_id = current_run_id
* where cdl.line_type = 'R'
* and cdl.burden_sum_source_run_id = init_cdl_run_id
* and cdl.project_id = current_project_id
* cdl.PA_DATE <= nvl(to_date(x_end_date,'DD-MM-RR'),cdl.PA_DATE)
* and request_id = x_request_id ;
UPDATE pa_expenditure_items_all ei
set adjustment_type =NULL, /*Start of bug 4754024*/
cc_bl_distributed_code =decode(tp_ind_compiled_set_id,NULL,
decode(cc_bl_distributed_code,'Y','N',cc_bl_distributed_code),cc_bl_distributed_code), /*4754024*/
cc_ic_processed_code =decode(tp_ind_compiled_set_id,NULL,decode(cc_ic_processed_code,'Y','N',cc_ic_processed_code)
,cc_ic_processed_code),
Denom_Tp_Currency_Code =decode(tp_ind_compiled_set_id,NULL,decode(cc_bl_distributed_code,'Y',NULL,Denom_Tp_Currency_Code),
Denom_Tp_Currency_Code),
Denom_Transfer_Price =decode(tp_ind_compiled_set_id,NULL,decode(cc_bl_distributed_code,'Y',NULL,Denom_Transfer_Price),
Denom_Transfer_Price),
Acct_Tp_Rate_Type =decode(tp_ind_compiled_set_id,NULL,decode(cc_bl_distributed_code,'Y',NULL,Acct_Tp_Rate_Type),
Acct_Tp_Rate_Type),
Acct_Tp_Rate_Date =decode(tp_ind_compiled_set_id,NULL,decode(cc_bl_distributed_code,'Y',NULL,Acct_Tp_Rate_Date),
Acct_Tp_Rate_Date),
Acct_Tp_Exchange_Rate =decode(tp_ind_compiled_set_id,NULL,decode(cc_bl_distributed_code,'Y',NULL,Acct_Tp_Exchange_Rate),
Acct_Tp_Exchange_Rate),
Acct_Transfer_Price =decode(tp_ind_compiled_set_id,NULL,decode(cc_bl_distributed_code,'Y',NULL,Acct_Transfer_Price),
Acct_Transfer_Price),
Projacct_Transfer_Price=decode(tp_ind_compiled_set_id,NULL,decode(cc_bl_distributed_code,'Y',NULL,Projacct_Transfer_Price),
Projacct_Transfer_Price),
Cc_Markup_Base_Code =decode(tp_ind_compiled_set_id,NULL,decode(cc_bl_distributed_code,'Y',NULL,Cc_Markup_Base_Code),
Cc_Markup_Base_Code),
Tp_Base_Amount =decode(tp_ind_compiled_set_id,NULL,decode(cc_bl_distributed_code,'Y',NULL,Tp_Base_Amount),
Tp_Base_Amount),
Tp_Bill_Rate =decode(tp_ind_compiled_set_id,NULL,decode(cc_bl_distributed_code,'Y',NULL,Tp_Bill_Rate),TP_Bill_Rate),
Tp_Bill_Markup_Percentage=decode(tp_ind_compiled_set_id,NULL,decode(cc_bl_distributed_code,'Y',NULL,
Tp_Bill_Markup_Percentage),Tp_Bill_Markup_Percentage),
Tp_Schedule_line_Percentage =decode(tp_ind_compiled_set_id,NULL,decode(cc_bl_distributed_code,'Y',NULL,
Tp_Schedule_line_Percentage),Tp_Schedule_line_Percentage),
Tp_Rule_percentage = decode(tp_ind_compiled_set_id,NULL,decode(cc_bl_distributed_code,'Y',NULL,Tp_Rule_percentage),
Tp_Rule_percentage) /*End of bug 4754024*/
where adjustment_type ='BURDEN_RESUMMARIZE'
and project_id = current_project_id
and exists (select 1 from pa_cost_distribution_lines_all cdl
where cdl.expenditure_item_id = ei.expenditure_item_id
and cdl.request_id = x_request_id
/* and cdl.prev_ind_compiled_set_id is NOT NULL :Commented for bug# 3040724*/
and cdl.burden_sum_source_run_id =current_run_id )
and rownum <=l_profile_set_size;
ei_update_count :=SQL%ROWCOUNT ;
UPDATE pa_cost_distribution_lines_all
set prev_ind_compiled_set_id = NULL
where prev_ind_compiled_set_id IS NOT NULL
and project_id = current_project_id
and request_id = x_request_id
and burden_sum_source_run_id =current_run_id
and expenditure_item_id = l_tbl_eiid(i);
cdl_update_count :=SQL%ROWCOUNT ;
IF (ei_update_count
select /*+ use_hash(pt p) */
p.project_id , p.segment1
, upper(nvl(pt.burden_account_flag,'N')) burden_account_flag
, upper(pt.burden_amt_display_method) burden_amt_display_method
from pa_projects_all p,
pa_project_types_all pt
where pt.project_type = p.project_type
and pt.org_id = p.org_id /*5368274*/
and pt.burden_amt_display_method in ('D','d')
and p.project_id = nvl(x_project_id,p.project_id) /* bug#2791563 */
and exists ( select 1
from pa_commitment_txns cmt
where nvl(x_project_id,p.project_id) = cmt.project_id /* Bug 3613712 : Perf Issue SQL rep ID : 7938694 FTS on pa_commitment_txns */
--Bug#960813
-- and cmt.line_type = 'R'
and cmt.burden_sum_source_run_id = init_cmt_run_id );
select source_project_id,
source_task_id,
source_org_id ,
source_pa_period ,
source_gl_period ,
source_txn_source,
source_line_type ,
source_ind_cost_code ,
source_txn_ref1,
source_expenditure_type,
source_ind_expenditure_type,
source_exp_category ,
source_revenue_category,
source_cost_base ,
source_compiled_multiplier,
source_ind_rate_sch_id ,
source_ind_rate_sch_rev_id,
source_burden_cost ,
source_run_id,
source_burden_sum_rej_code,
resource_class,
source_system_linkage_function, -- 4057874
dest_project_id,
dest_task_id ,
dest_org_id,
dest_pa_period ,
dest_txn_source,
dest_gl_period ,
dest_exp_category ,
dest_revenue_category ,
dest_ind_exp_type ,
dest_line_type ,
dest_txn_ref1,
dest_ind_cost_code,
acct_raw_cost , /* 2324127 */
acct_burdened_cost , /* 2324127 */
denom_currency_code , /* 2324127 */
denom_raw_cost , /* 2324127 */
denom_burdened_cost , /* 2324127 */
acct_currency_code , /* 2324127 */
acct_rate_date , /* 2324127 */
acct_rate_type , /* 2324127 */
acct_exchange_rate , /* 2324127 */
-- receipt_currency_code , /* 2324127 */
-- receipt_currency_amount, /* 2324127 */
-- receipt_exchange_rate , /* 2324127 */
project_currency_code , /* 2324127 */
project_rate_date , /* 2324127 */
project_rate_type , /* 2324127 */
project_exchange_rate , /* 2324127 */
vendor_id , -- 4057874
inventory_item_id , -- 4057874
bom_labor_resource_id , -- 4057874
bom_equipment_resource_id , -- 4057874
dest_summary_group
from pa_cmt_burden_summary_v
order by dest_summary_group;
select pa_burden_sum_run_s.nextval
into current_run_id
from dual;
* select set_of_books_id
* into sob_id
* from pa_implementations;
select attribute1
into l_attribute1
from pa_projects_all
where project_id = proj_rec.project_id
for update of attribute1 nowait;
update pa_commitment_txns
set burden_sum_rejection_code = NULL
where project_id = current_project_id
and burden_sum_source_run_id = init_cmt_run_id;
update pa_commitment_txns
set burden_sum_rejection_code = 'BCC_EXP_TYPE_NULL',
burden_sum_source_run_id = current_run_id
where project_id = current_project_id
and (cmt_line_id) in
(select cmt.cmt_line_id
from pa_commitment_txns cmt
-- Bug#960813
-- where cmt.line_type = 'R'
where cmt.burden_sum_source_run_id = current_run_id
and cmt.project_id = current_project_id
and exists (select 1
from pa_compiled_multipliers cm,
pa_ind_cost_codes icc
where cm.ind_compiled_set_id=cmt.cmt_ind_compiled_set_id
and icc.ind_cost_code = cm.ind_cost_code
and icc.expenditure_type is null));
select pa_commitment_txns_s.nextval
into l_cmt_line_id
from dual;
insert into pa_commitment_txns (
cmt_line_id,
project_id,
task_id ,
transaction_source ,
line_type ,
expenditure_item_date,
pa_period ,
gl_period,
expenditure_type,
expenditure_category ,
revenue_category,
system_linkage_function,
tot_cmt_burdened_cost ,
original_txn_reference1,
last_updated_by ,
last_update_date ,
creation_date ,
created_by ,
last_update_login,
acct_raw_cost , /* 2324127 */
acct_burdened_cost , /* 2324127 */
denom_currency_code , /* 2324127 */
denom_raw_cost , /* 2324127 */
denom_burdened_cost , /* 2324127 */
acct_currency_code , /* 2324127 */
acct_rate_date , /* 2324127 */
acct_rate_type , /* 2324127 */
acct_exchange_rate , /* 2324127 */
-- receipt_currency_code , /* 2324127 */
-- receipt_currency_amount , /* 2324127 */
-- receipt_exchange_rate , /* 2324127 */
project_currency_code , /* 2324127 */
project_rate_date , /* 2324127 */
project_rate_type , /* 2324127 */
project_exchange_rate , /* 2324127 */
burden_sum_dest_run_id,
organization_id ,
resource_class ,
vendor_id , /* 4057874 */
inventory_item_id , /* 4057874 */
bom_labor_resource_id , /* 4057874 */
bom_equipment_resource_id , /* 4057874 */
src_system_linkage_function ) /* 4057874 */
values (
l_cmt_line_id,
prev_bcc_rec.dest_project_id,
prev_bcc_rec.dest_task_id,
prev_bcc_rec.dest_txn_source,
prev_bcc_rec.dest_line_type,
nvl(l_pa_end_date, sysdate) ,
prev_bcc_rec.dest_pa_period,
prev_bcc_rec.dest_gl_period,
prev_bcc_rec.dest_ind_exp_type,
prev_bcc_rec.dest_exp_category,
prev_bcc_rec.dest_revenue_category,
'BTC',
l_burden_cost,
l_txn_ref1,
1,
sysdate,
sysdate,
0,
0,
0, /* acct_raw_cost 2324127 */
l_acct_burdened_cost , /* 2324127 */
prev_bcc_rec.denom_currency_code , /* 2324127 */
0 , /* denom_raw_cost 2324127 */
l_denom_burdened_cost , /* 2324127 */
prev_bcc_rec.acct_currency_code , /* 2324127 */
prev_bcc_rec.acct_rate_date , /* 2324127 */
prev_bcc_rec.acct_rate_type , /* 2324127 */
prev_bcc_rec.acct_exchange_rate , /* 2324127 */
-- receipt_currency_code , /* 2324127 */
-- receipt_currency_amount , /* 2324127 */
-- receipt_exchange_rate , /* 2324127 */
prev_bcc_rec.project_currency_code , /* 2324127 */
prev_bcc_rec.project_rate_date , /* 2324127 */
prev_bcc_rec.project_rate_type , /* 2324127 */
prev_bcc_rec.project_exchange_rate , /* 2324127 */
current_run_id,
prev_bcc_rec.dest_org_id,
prev_bcc_rec.resource_class,
prev_bcc_rec.vendor_id, /* 4057874 */
prev_bcc_rec.inventory_item_id, /* 4057874 */
prev_bcc_rec.bom_labor_resource_id, /* 4057874 */
prev_bcc_rec.bom_equipment_resource_id, /* 4057874 */
prev_bcc_rec.source_system_linkage_function ); /* 4057874 */
select pa_commitment_txns_s.nextval
into l_cmt_line_id
from dual;
insert into pa_commitment_txns (
cmt_line_id,
project_id,
task_id ,
transaction_source ,
line_type ,
expenditure_item_date,
pa_period ,
gl_period,
expenditure_type,
expenditure_category ,
revenue_category,
system_linkage_function,
tot_cmt_burdened_cost ,
original_txn_reference1,
last_updated_by ,
last_update_date ,
creation_date ,
created_by ,
last_update_login,
acct_raw_cost , /* 2324127 */
acct_burdened_cost , /* 2324127 */
denom_currency_code , /* 2324127 */
denom_raw_cost , /* 2324127 */
denom_burdened_cost , /* 2324127 */
acct_currency_code , /* 2324127 */
acct_rate_date , /* 2324127 */
acct_rate_type , /* 2324127 */
acct_exchange_rate , /* 2324127 */
-- receipt_currency_code , /* 2324127 */
-- receipt_currency_amount , /* 2324127 */
-- receipt_exchange_rate , /* 2324127 */
project_currency_code , /* 2324127 */
project_rate_date , /* 2324127 */
project_rate_type , /* 2324127 */
project_exchange_rate , /* 2324127 */
burden_sum_dest_run_id,
organization_id
,resource_class,
vendor_id , /* 4057874 */
inventory_item_id , /* 4057874 */
bom_labor_resource_id , /* 4057874 */
bom_equipment_resource_id , /* 4057874 */
src_system_linkage_function ) /* 4057874 */
values (
l_cmt_line_id,
prev_bcc_rec.dest_project_id,
prev_bcc_rec.dest_task_id,
prev_bcc_rec.dest_txn_source,
prev_bcc_rec.dest_line_type,
nvl(l_pa_end_date, sysdate) ,
prev_bcc_rec.dest_pa_period,
prev_bcc_rec.dest_gl_period,
prev_bcc_rec.dest_ind_exp_type,
prev_bcc_rec.dest_exp_category,
prev_bcc_rec.dest_revenue_category,
'BTC',
l_burden_cost,
l_txn_ref1,
1,
sysdate,
sysdate,
0,
0,
0, /* acct_raw_cost 2324127 */
l_acct_burdened_cost , /* 2324127 */
prev_bcc_rec.denom_currency_code , /* 2324127 */
0 , /* denom_raw_cost 2324127 */
l_denom_burdened_cost , /* 2324127 */
prev_bcc_rec.acct_currency_code , /* 2324127 */
prev_bcc_rec.acct_rate_date , /* 2324127 */
prev_bcc_rec.acct_rate_type , /* 2324127 */
prev_bcc_rec.acct_exchange_rate , /* 2324127 */
-- receipt_currency_code , /* 2324127 */
-- receipt_currency_amount , /* 2324127 */
-- receipt_exchange_rate , /* 2324127 */
prev_bcc_rec.project_currency_code , /* 2324127 */
prev_bcc_rec.project_rate_date , /* 2324127 */
prev_bcc_rec.project_rate_type , /* 2324127 */
prev_bcc_rec.project_exchange_rate , /* 2324127 */
current_run_id,
prev_bcc_rec.dest_org_id
,prev_bcc_rec.resource_class,
prev_bcc_rec.vendor_id, /* 4057874 */
prev_bcc_rec.inventory_item_id, /* 4057874 */
prev_bcc_rec.bom_labor_resource_id, /* 4057874 */
prev_bcc_rec.bom_equipment_resource_id, /* 4057874 */
prev_bcc_rec.source_system_linkage_function ); /* 4057874 */
update pa_commitment_txns
set burden_sum_source_run_id = current_run_id
where (cmt_line_id) in
( select cmt_line_id
from pa_commitment_txns cmt
where cmt.burden_sum_rejection_code is NULL
-- Bug#960813
-- and cmt.line_type = 'R'
and cmt.burden_sum_source_run_id = init_cmt_run_id
and cmt.project_id = current_project_id);
INSERT INTO pa_aud_cost_dist_lines (
expenditure_item_id
, line_num
, ind_compiled_set_id
, burden_sum_source_run_id
, creation_date
, created_by
, program_id
, program_application_id
, request_id
)
SELECT
expenditure_Item_id,
line_num,
prev_ind_compiled_set_id,
burden_sum_source_run_id,
sysdate,
p_user_id,
l_program_id,
l_program_application_id,
p_request_id
FROM
pa_cost_distribution_lines_all
WHERE expenditure_item_id = l_tbl_eiid(i)
AND line_num= l_tbl_cdlln(i)
AND prev_ind_compiled_set_id IS NOT NULL
AND request_id = p_request_id
AND project_id = p_project_id;
/* Bug 5896943: Inserting prvdr_accrual_date in place of expenditure_item_date
for period accrual transactions so that the reversal BTC's will be in the future period.
*/
/* Modified expenditure item date for bug 5907315*/
insert into PA_EI_CDL_CM_GTEMP(
PROJECT_ID ,TASK_ID ,ORGANIZATION_ID
,PA_DATE ,PA_PERIOD_NAME ,ATTRIBUTE1
,ATTRIBUTE2 ,ATTRIBUTE3 ,ATTRIBUTE4
,ATTRIBUTE5 ,ATTRIBUTE6 ,ATTRIBUTE7
,ATTRIBUTE8 ,ATTRIBUTE9 ,ATTRIBUTE10
,ATTRIBUTE_CATEGORY ,PERSON_TYPE ,PO_LINE_ID
,SYSTEM_LINKAGE_FUNCTION ,EI_EXPENDITURE_TYPE ,IND_COMPILED_SET_ID
,PREV_IND_COMPILED_SET_ID ,EXPENDITURE_ITEM_ID ,LINE_NUM
,EXPENDITURE_ITEM_DATE ,CDL_AMOUNT ,CDL_PROJFUNC_CURRENCY_CODE
,CDL_DENOM_RAW_COST ,CDL_DENOM_CURRENCY_CODE ,CDL_ACCT_RAW_COST
,CDL_ACCT_CURRENCY_CODE ,CDL_PROJECT_RAW_COST ,CDL_PROJECT_CURRENCY_CODE
,BURDEN_SUM_SOURCE_RUN_ID ,BURDEN_SUM_REJECTION_CODE ,SYSTEM_REFERENCE1
,DENOM_CURRENCY_CODE ,ACCT_CURRENCY_CODE ,PROJECT_CURRENCY_CODE
,PROJFUNC_CURRENCY_CODE ,BILLABLE_FLAG ,REQUEST_ID
,ADJUSTMENT_TYPE ,JOB_ID ,NON_LABOR_RESOURCE
,NON_LABOR_RESOURCE_ORGN_ID ,WIP_RESOURCE_ID ,INCURRED_BY_PERSON_ID
,INVENTORY_ITEM_ID
,ORG_ID ,ACCT_RATE_DATE ,ACCT_RATE_TYPE
,ACCT_EXCHANGE_RATE ,PROJECT_RATE_DATE ,PROJECT_RATE_TYPE
,PROJECT_EXCHANGE_RATE ,PROJFUNC_COST_RATE_DATE ,PROJFUNC_COST_RATE_TYPE
,PROJFUNC_COST_EXCHANGE_RATE)
(
select
cdl.project_id
,cdl.task_id
,nvl(ei.override_to_organization_id,e.incurred_by_organization_id)
,cdl.pa_date
,decode(cdl.prev_ind_compiled_set_id, null, cdl.pa_period_name
,nvl(pa_utils2.get_pa_period_name(ei.expenditure_item_date, ei.org_id), cdl.pa_period_name))
, ei.attribute1 , ei.attribute2 , ei.attribute3 , ei.attribute4
, ei.attribute5 , ei.attribute6 , ei.attribute7
, ei.attribute8 , ei.attribute9 , ei.attribute10
, ei.attribute_category , e.person_type , ei.po_line_id
, ei.system_linkage_function , ei.expenditure_type , cdl.ind_compiled_set_id
, cdl.prev_ind_compiled_set_id , ei.expenditure_item_id , cdl.line_num
, decode(NVL(fnd_profile.value_specific('PA_REVENUE_ORIGINAL_RATE_FORRECALC'),'N'),'N',nvl(ei.prvdr_accrual_date,ei.expenditure_item_date),ei.expenditure_item_date)
, cdl.amount , cdl.projfunc_currency_code
, cdl.denom_raw_cost , cdl.denom_currency_code , cdl.acct_raw_cost
, cdl.acct_currency_code , cdl.project_raw_cost , cdl.project_currency_code
, cdl.burden_sum_source_run_id , cdl.burden_sum_rejection_code , cdl.system_reference1
, ei.denom_currency_code , ei.acct_currency_code , ei.project_currency_code
, ei.projfunc_currency_code , cdl.billable_flag , cdl.request_id
, DECODE(ei.adjustment_type, 'BURDEN_RESUMMARIZE'
, DECODE(ei.system_linkage_function, 'VI', ei.adjustment_type
, DECODE(ei.po_line_id, NULL, NULL, ei.adjustment_type)), NULL) adjustment_type
, ei.job_id , ei.non_labor_resource
, ei.organization_id NON_LABOR_RESOURCE_ORGN_ID , ei.wip_resource_id , e.incurred_by_person_id
, ei.inventory_item_id
, ei.org_id ,CDL.ACCT_RATE_DATE ,CDL.ACCT_RATE_TYPE
,CDL.ACCT_EXCHANGE_RATE ,CDL.PROJECT_RATE_DATE ,CDL.PROJECT_RATE_TYPE
,CDL.PROJECT_EXCHANGE_RATE ,CDL.PROJFUNC_COST_RATE_DATE ,CDL.PROJFUNC_COST_RATE_TYPE
,CDL.PROJFUNC_COST_EXCHANGE_RATE
FROM
PA_COST_DISTRIBUTION_LINES_ALL CDL,
PA_EXPENDITURE_ITEMS EI,
PA_EXPENDITURES_ALL E
WHERE cdl.burden_sum_source_run_id = p_current_run_id
AND cdl.project_id = p_project_id
AND cdl.expenditure_item_id = ei.expenditure_item_id
AND cdl.line_type = 'R'
AND e.expenditure_id = ei.expenditure_id
AND nvl(ei.adjustment_type,'-999') <> 'BURDEN_RESUMMARIZE' /*Bug# 6449677*/
AND cdl.burden_sum_rejection_code is NULL
AND cdl.prev_ind_compiled_set_id IS NULL
AND ei.expenditure_item_date <= nvl(l_end_date,ei.expenditure_item_date)
AND ( ei.transaction_source IS NULL or pa_utils2.get_ts_allow_burden_flag(ei.transaction_source)<>'Y' ));
UPDATE PA_EI_CDL_CM_GTEMP ei
set (COST_PLUS_STRUCTURE ,COST_BASE) =
(select /*+ ORDERED */ distinct
cbcc.cost_plus_structure, cbcc.cost_base
from
PA_COST_BASE_EXP_TYPES CBET,
PA_COMPILED_MULTIPLIERS CM,
PA_COST_BASE_COST_CODES CBCC
WHERE cbet.expenditure_type = ei.ei_expenditure_type
AND cbet.cost_base_type = 'INDIRECT COST'
AND cm.ind_Compiled_set_id = ei.ind_compiled_set_id
AND cm.cost_base = cbet.cost_base
AND cbcc.cost_base_cost_code_id = cm.cost_base_cost_code_id
AND cbcc.ind_cost_code = cm.ind_cost_code
AND cbcc.cost_base = cm.cost_base
AND cbcc.cost_base_type = 'INDIRECT COST'
AND cbcc.cost_plus_structure = cbet.cost_plus_structure)
where ei.cost_base is null
and ei.prev_ind_compiled_set_id is null
AND ei.burden_sum_source_run_id = p_current_run_id
AND ei.project_id = p_project_id;
PROCEDURE update_gtemp(l_request_id number) IS
l_eiid typ_tbl_eiid;
l_eiid.delete;
l_linenum.delete; /*added for the bug#5949107*/
UPDATE PA_EI_CDL_CM_GTEMP gtemp
set BURDEN_SUM_REJECTION_CODE = 'BCC_EXP_TYPE_NULL'
WHERE BURDEN_SUM_REJECTION_CODE IS NULL
and exists ( select 1 from pa_ind_cost_codes icc, pa_cost_base_cost_codes cbcc
where cbcc.cost_plus_structure = gtemp.cost_plus_structure
and cbcc.cost_base = gtemp.cost_base
and cbcc.cost_base_type = 'INDIRECT COST'
and cbcc.ind_cost_code = icc.ind_cost_code
and icc.expenditure_type is NULL )
returning expenditure_Item_id,line_num bulk collect into l_eiid,l_linenum; /*l_linenum is added for the bug#5949107*/
UPDATE PA_COST_DISTRIBUTION_LINES
set BURDEN_SUM_REJECTION_CODE = 'BCC_EXP_TYPE_NULL'
where expenditure_item_id = l_eiid(i)
and line_num = l_linenum(i); /*added for the bug#5949107*/
END update_gtemp;