[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
insert into FND_LOG_MESSAGES
(MODULE, LOG_LEVEL, MESSAGE_TEXT
, SESSION_ID, USER_ID, TIMESTAMP
, LOG_SEQUENCE, ENCODED, NODE
, NODE_IP_ADDRESS, PROCESS_ID, JVM_ID
, THREAD_ID, AUDSID, DB_INSTANCE
, TRANSACTION_CONTEXT_ID)
values
(p_module, 6, p_msg, -1, 0, sysdate
, FND_LOG_MESSAGES_S.NEXTVAL, 'Y', null
, null, NULL, NULL, NULL, NULL, 1, NULL);
PROCEDURE UPDATE_BATCH_CONC_STATUS
(
p_first_time_flag in varchar2,
x_count_batches out nocopy number,
x_count_running out nocopy number,
x_count_errored out nocopy number,
x_count_completed out nocopy number,
x_count_pending out nocopy number
)
IS
TYPE Prg_Batch_t IS
TABLE OF pji_prg_batch%ROWTYPE
INDEX BY BINARY_INTEGER;
SELECT
*
FROM
pji_prg_batch
WHERE nvl(curr_request_status,'PENDING') <> 'COMPLETED' /* added for bug#10031149 */
ORDER BY
DECODE(nvl(curr_request_status,'PENDING') ,
'PENDING' , 1,
'ERRORED' , 2, 3 ) ;
l_Prg_Batch_t.delete;
UPDATE
pji_prg_batch
SET
curr_request_status = decode(nvl(curr_request_status,'PENDING'),
'ERRORED',
decode(l_Prg_Batch_t(i).curr_request_status,
'R-ERRORED',curr_request_status,
l_Prg_Batch_t(i).curr_request_status ),
l_Prg_Batch_t(i).curr_request_status )
WHERE
batch_name = l_Prg_Batch_t(i).batch_name;
UPDATE pji_prg_batch
SET curr_request_status = 'ERRORED'
WHERE nvl(curr_request_id,-1) > 0
and curr_request_status = 'COMPLETED'
and not exists
( select 'x' from fnd_concurrent_requests x where x.request_id = curr_request_id );
end UPDATE_BATCH_CONC_STATUS;
SELECT *
FROM pji_prg_batch
ORDER BY DECODE(nvl(curr_request_status,'PENDING'),
'ERRORED',curr_request_status),
to_number(rtrim(substrb(replace(batch_name,'-ERR',''),19,10))); /* Modified for bug 9109118 */ /* Modified for bug 14760728 */
delete from pji_system_parameters
where name = 'PJI_STAGE3_REQ_LUPPD';
Select count(*)
Into l_req_count
From Fnd_Concurrent_Requests a, Fnd_Concurrent_Programs P, Fnd_Application c
Where a.Program_Application_ID = P.Application_ID
And a.Concurrent_Program_ID = P.Concurrent_Program_ID
AND P.Concurrent_Program_Name in ('PJI_PJP_SUMMARIZE_RBS', 'PJI_PJP_SUMMARIZE_PRTL',
'PJI_PJP_SUMMARIZE_INCR', 'PJI_PJP_SUMMARIZE_FULL','PJI_PJP_SUM_CLEANALL')
And P.Application_ID = c.Application_ID
And c.Application_Short_Name = 'PJI'
AND a.phase_code in ('R','P','I')
AND rownum = 1;
delete from pji_system_parameters
where name = 'PJI_STAGE3_REQ_LUPPD';
select 'Y' into l_reg_flag
from dual
where exists
( select 'x' from pji_prg_batch where substr(batch_name,10,3) = '-R-' );
select to_number(substr(batch_name,13,5)) into l_reg_num
from pji_prg_batch
where substr(batch_name,10,3) = '-R-'
and rownum = 1;
update pji_prg_group
set batch_name = 'UPP-BATCH'||'-R-'||l_reg_num||substr(batch_name,10,5) ;
update pji_prg_batch
set batch_name = 'UPP-BATCH'||'-R-'||l_reg_num||substr(batch_name,10,5) ;
update pji_prg_group c set c.batch_name = c.batch_name||'-ERR'
where c.prg_group in
( select distinct b.prg_group
from pji_pjp_proj_batch_map a,
pji_prg_group b
where a.project_id = b.project_id
and b.prg_group is not null
);
update pji_prg_group c set c.batch_name = c.batch_name||'-ERR'
where c.project_id in
( select distinct b.project_id
from pji_pjp_proj_batch_map a,
pji_prg_group b
where a.project_id = b.project_id
and b.prg_group is null
);
Insert into pji_prg_batch
( batch_name,
wbs_total,
prg_total,
delta_total,
total_count,
project_count
)
select distinct batch_name ,0,0,0,0,0
from pji_prg_group where batch_name like '%-ERR';
update pji_prg_group c set c.batch_name = ( select d1.value
from pji_system_parameters d1 , pji_pjp_proj_batch_map a1,
pji_prg_group b1
where a1.project_id = b1.project_id
and c.batch_name like '%-ERR'
and b1.prg_group = c.prg_group
and to_number(substr(d1.name,8,instr(d1.name,'$',1) - 8)) = a1.worker_id -- Sridhar changed added substr june-11th V1_CHANGE
and d1.name like '%FROM_PROJECT'
and d1.value like 'UPP-BATCH%'
and b1.prg_group is not null -- Sridhar changed added not null condition june-11th V1_CHANGE
and rownum=1)
where
c.batch_name like '%-ERR'
and exists
( select 'x'
from pji_system_parameters d2 , pji_pjp_proj_batch_map a2,
pji_prg_group b2
where a2.project_id = b2.project_id
and b2.prg_group = c.prg_group
and to_number(substr(d2.name,8,instr(d2.name,'$',1) - 8)) = a2.worker_id -- Sridhar changed added substr june-11th V1_CHANGE
and d2.name like '%FROM_PROJECT'
and b2.prg_group is not null -- Sridhar changed added not null condition june-11th V1_CHANGE
and d2.value like 'UPP-BATCH%' );
update pji_prg_group c set c.batch_name = ( select d1.value
from pji_system_parameters d1 , pji_pjp_proj_batch_map a1,
pji_prg_group b1
where a1.project_id = b1.project_id
and c.batch_name like '%-ERR'
and b1.project_id = c.project_id
and to_number(substr(d1.name,8,instr(d1.name,'$',1) - 8)) = a1.worker_id
and d1.name like '%FROM_PROJECT'
and d1.value like 'UPP-BATCH%'
and b1.prg_group is null
and rownum=1)
where
c.batch_name like '%-ERR'
and exists
( select 'x'
from pji_system_parameters d2 , pji_pjp_proj_batch_map a2,
pji_prg_group b2
where a2.project_id = b2.project_id
and b2.project_id = c.project_id
and to_number(substr(d2.name,8,instr(d2.name,'$',1) - 8)) = a2.worker_id
and d2.name like '%FROM_PROJECT'
and b2.prg_group is null
and d2.value like 'UPP-BATCH%' );
Insert into pji_prg_batch
( batch_name,
wbs_total,
prg_total,
delta_total,
total_count,
project_count
)
select distinct batch_name ,0,0,0,0,0
from pji_prg_group a2 where a2.batch_name not in ( select c1.batch_name from pji_prg_batch c1 );
UPDATE pji_prg_batch c
SET c.curr_request_id =
(
select b.value
from pji_system_parameters a , pji_system_parameters b
where a.name like 'PJI_PJP%FROM_PROJECT'
and to_number(substr(a.name,8,instr(a.name,'$',1) - 8)) = to_number(substr(b.name,8,instr(b.name,'$',1) - 8))
and b.name like 'PJI_PJP%PJI_PJP%'
and c.batch_name = a.value
and b.value is not null
)
WHERE exists
( select b.value
from pji_system_parameters a , pji_system_parameters b
where a.name like 'PJI_PJP%FROM_PROJECT'
and to_number(substr(a.name,8,instr(a.name,'$',1) - 8)) = to_number(substr(b.name,8,instr(b.name,'$',1) - 8))
and b.name like 'PJI_PJP%PJI_PJP%'
and c.batch_name = a.value
and b.value is not null ) ;
delete from pji_prg_batch a
where not exists
(
select 'x'
from pji_prg_group b
where a.batch_name = b.batch_name );
UPDATE_BATCH_CONC_STATUS
(
'Y' ,
l_count_batches ,
l_count_running ,
l_count_errored ,
l_count_completed ,
l_count_pending
);
SELECT count(*) into l_test
FROM pji_prg_batch
WHERE nvl(curr_request_status,'PENDING') <> 'COMPLETED'
and( exists -- added sridhar_refresh
( select 'x' from PJI_LAUNCH_INCR
where incr_type = 'REFRESH' and g_launch_type = 'RPPD' ) -- sridhar_phase_1 p_temp_table_size = 666666 )
or
exists -- added sridhar_refresh
( select 'x' from PJI_LAUNCH_INCR
where incr_type <> 'REFRESH' and g_launch_type = 'UPPD' ) -- sridhar_phase_1 and p_temp_table_size <> 666666 )
);
UPDATE_BATCH_CONC_STATUS -- after creating
(
'Y' , -- value has to 'Y'
l_count_batches ,
l_count_running ,
l_count_errored ,
l_count_completed ,
l_count_pending
);
l_Prg_Batch_t.delete;
UPDATE
pji_prg_batch
SET
curr_request_id = l_request_id ,
curr_request_status = 'RUNNING'
WHERE
batch_name = l_Prg_Batch_t(i).batch_name ;
INSERT INTO pji_prg_batch_log
( run_date_key, run_date , request_id, batch_name , wbs_total, prg_total,
delta_total, total_count, project_count, custom1, custom2, custom3
)
values
( to_char(sysdate,'DD-Mon-YYYY HH24:MI:SS'), sysdate , l_request_id,
l_Prg_Batch_t(i).batch_name , l_Prg_Batch_t(i).wbs_total,
l_Prg_Batch_t(i).prg_total, l_Prg_Batch_t(i).delta_total,
l_Prg_Batch_t(i).total_count, l_Prg_Batch_t(i).project_count,
l_Prg_Batch_t(i).custom1, l_Prg_Batch_t(i).custom2,
l_Prg_Batch_t(i).custom3
);
UPDATE
pji_prg_batch
SET
curr_request_id = l_request_id ,
message = 'Error calling FND_REQUEST.SUBMIT_REQUEST',
curr_request_status = 'SUBMIT-ERRORED'
WHERE
batch_name = l_Prg_Batch_t(i).batch_name ;
commit; -- commit the status after each update. This will allow
SELECT count(*) into l_test
FROM pji_prg_batch
WHERE nvl(curr_request_status,'PENDING') not in ( 'COMPLETED' ,'SUBMIT-ERRORED' ,'R-ERRORED') ;
SELECT count(*) into l_test
FROM pji_prg_batch
WHERE nvl(curr_request_status,'PENDING') in ( 'RUNNING') ;
UPDATE_BATCH_CONC_STATUS
(
'N' ,
l_count_batches ,
l_count_running ,
l_count_errored ,
l_count_completed ,
l_count_pending
);
select count(*) into
l_count_rerrored /* Modified for bug 9387564 */
from pji_prg_batch
where curr_request_status = 'R-ERRORED';
UPDATE_BATCH_CONC_STATUS
(
'Y' ,
l_count_batches ,
l_count_running ,
l_count_errored ,
l_count_completed ,
l_count_pending
);
delete from pji_system_parameters
where name = 'PJI_STAGE3_REQ_LUPPD';
select prg_group, count(distinct project_id) cnt, 'ALL' prg_type
from pa_proj_element_versions ver
where g_launch_type = 'UPPD' and -- sridhar_phase_1 p_wbs_temp_table_size <> 666666 and -- sridhar refresh
nvl(p_incremental_mode,'N') = 'N'
and object_type = 'PA_STRUCTURES'
and prg_group is not null
and exists ( select 'x' from
pa_projects_all p1
where p1.project_id = ver.project_id
and nvl(p1.org_id,-1) = nvl(P_OPERATING_UNIT,nvl(p1.org_id,-1)))
group by prg_group
union all
select /*+ ordered index(ver PA_PROJ_ELEMENT_VERSIONS_N5) */
ver.PRG_GROUP , count( distinct ver.project_id ) cnt, 'PRG_CHANGE' prg_type
from
PJI_LAUNCH_INCR grp ,
PA_PROJ_ELEMENT_VERSIONS ver
where ( nvl(p_incremental_mode,'N') = 'Y' or g_launch_type = 'RPPD' ) and -- sridhar_phase_1 p_wbs_temp_table_size = 666666 ) and -- sridhar refresh and
ver.object_type = 'PA_STRUCTURES' and
grp.incr_type in ( 'PRG_BASE', 'REFRESH') and
ver.prg_group = grp.prg_group
group by ver.prg_group
union all
select grp.prg_group ,count( distinct grp.project_id ) cnt, 'PRG_PARENT' prg_type
from
PJI_LAUNCH_INCR grp
where ( nvl(p_incremental_mode,'N') = 'Y' or g_launch_type = 'RPPD' ) and -- sridhar_phase_1 p_wbs_temp_table_size = 666666 ) and -- sridhar refresh and
grp.incr_type not in ( 'PRG_BASE' , 'REFRESH') and
grp.prg_group > 0
group by grp.prg_group;
select prg_group, prg_level, project_id
from pa_proj_element_versions
where x_prg_type <> 'PRG_PARENT'
and object_type = 'PA_STRUCTURES'
and prg_group IS NOT NULL
and prg_group = x_prg_group
UNION
select /*+ index(pji_xbs_denorm pji_xbs_denorm_n5) */
prg_group, sup_level, sup_project_id
from pji_xbs_denorm
where x_prg_type <> 'PRG_PARENT'
and struct_type = 'PRG_BASE'
and prg_group is not null
and struct_type is null
and sub_level = sup_level
and prg_group = x_prg_group
UNION
select prg_group , prg_level , project_id
from PJI_LAUNCH_INCR grp
where grp.prg_group = x_prg_group
and x_prg_type = 'PRG_PARENT'
and grp.incr_type not in ( 'PRG_BASE' , 'REFRESH')
and grp.prg_group > 0 ;
select count(1) into r_count
from dual
where exists
( select 'x' from PJI_LAUNCH_INCR where incr_type = 'REFRESH' ) ;
select count(1) into u_count
from dual
where exists
( select 'x' from PJI_LAUNCH_INCR where incr_type <> 'REFRESH' ) ;
delete from pji_prg_group;
delete from pji_prg_batch;
select 'Y'
into l_prg_event_exists
from dual
where exists
( select 'x'
from PA_PJI_PROJ_EVENTS_LOG log
where
log.EVENT_TYPE = 'PRG_CHANGE' and
log.EVENT_OBJECT <> -1 and
i.PRG_GROUP in (log.EVENT_OBJECT, log.ATTRIBUTE1)
);
select count(*)
into l_wbs_count
from pa_proj_element_versions A
where l_prg_event_exists = 'Y' and
a.parent_structure_version_id in
( select B.element_version_id
from pa_proj_element_versions B
where B.prg_group = i.prg_group
and B.object_type = 'PA_STRUCTURES'
)
and A.object_type = 'PA_TASKS';
select lw_lf.lw_lf_count + pa_struct.pa_struct_count
into l_prg_count
from
( select count(*) lw_lf_count from
(
select
distinct
prg_node.prg_group,
PRG_NODE.element_version_id sub_id,
pvt_parent1.parent_structure_version_id sup_id ,
pvt_parent1.project_id ,
pvt_parent1.proj_element_id ,
prt_parent.object_id_from1,
prt_parent.relationship_type,
ver.prg_level
from PA_OBJECT_RELATIONSHIPS prt_parent,
PA_PROJ_ELEMENT_VERSIONS ver ,
PA_PROJ_ELEMENT_VERSIONS pvt_parent1 ,
pa_proj_element_versions PRG_NODE
where 1=1
and PRG_NODE.prg_group = i.prg_group
and prt_parent.object_id_to1 = PRG_NODE.element_version_id
and PRG_NODE.object_type = 'PA_STRUCTURES'
and prt_parent.object_type_from = 'PA_TASKS'
and prt_parent.object_type_to = 'PA_STRUCTURES'
and (
prt_parent.relationship_type = 'LF'
or
prt_parent.relationship_type = 'LW'
)
and ver.element_version_id = prt_parent.object_id_from1
and pvt_parent1.element_version_id = prt_parent.object_id_from1
)
where l_prg_event_exists = 'Y' ) lw_lf,
(
select count(*) pa_struct_count
from pa_proj_element_versions a
where l_prg_event_exists = 'Y'
and a.prg_group = i.prg_group
and a.object_type = 'PA_STRUCTURES'
) pa_struct ;
Insert into pji_prg_group
( batch_name,
prg_group,
prg_level,
project_id,
parent_program_id
) values (
l_batch_name||l_batch_var,
i.prg_group,
j.prg_level,
j.project_id,
null);
Insert into pji_prg_batch
( batch_name,
wbs_total,
prg_total,
delta_total,
total_count,
project_count,
custom1
) values (
l_batch_name||l_batch_var,
l_wbs_total,
l_prg_total,
l_delta_total,
l_batch_count,
l_cnt,
l_prg_event_total);
Insert into pji_prg_batch
( batch_name,
wbs_total,
prg_total,
delta_total,
total_count,
project_count
) values (
l_batch_name||l_batch_var,
l_wbs_total,
l_prg_total,
l_delta_total,
l_batch_count,
l_cnt);
select count(*)
into l_curr_lines_cnt
from pa_budget_versions pbv,
pa_budget_lines pbl
where pbv.project_id = l_prj_list(a) and
pbv.budget_version_id = pbl.budget_version_id;
Insert into pji_prg_group
( batch_name,
prg_group,
prg_level,
project_id,
parent_program_id
) values (
l_batch_name||l_batch_var,
Null,
Null,
l_prj_list(a),
null );
Insert into pji_prg_batch
( batch_name,
wbs_total,
prg_total,
delta_total,
total_count,
project_count
) values (
l_batch_name||l_batch_var,
l_wbs_total,
l_prg_total,
l_delta_total,
l_batch_count,
l_cnt);
Insert into pji_prg_group
( batch_name,
prg_group,
prg_level,
project_id,
parent_program_id
) values (
l_batch_name||l_batch_var,
Null,
Null,
l_prj_list(a),
null );
For k in (select a.project_id
from pa_projects_all a
where nvl(p_incremental_mode,'N') = 'N'
and template_flag = 'N' -- Bug 9059688
and nvl(a.org_id,-1) = nvl(p_operating_unit,nvl(a.org_id,-1))
--added for 12.1.3 feature for new parameter Project Status
--commenting out the below clause as not needed in this scenario
/* and nvl(a.project_status_code,'PS') =
nvl(p_project_status,nvl(a.project_status_code,'PS'))*/
and not exists
(select 'x' from pji_prg_group b
where a.project_id = b.project_id)
union all
select project_id
from pji_launch_incr a
where nvl(p_incremental_mode,'N') = 'Y'
and incr_type like 'PROJ%'
and prg_group = -1
and not exists
(select 'x' from pji_prg_group b
where a.project_id = b.project_id )) loop
l_curr_lines_cnt := 0;
select count(*)
into l_curr_lines_cnt
from pa_budget_versions pbv,
pa_budget_lines pbl
where pbv.project_id = k.project_id
and pbv.budget_version_id = pbl.budget_version_id;
Insert into pji_prg_group
( batch_name,
prg_group,
prg_level,
project_id,
parent_program_id
) values (
l_batch_name||l_batch_var,
Null,
Null,
k.project_id,
null );
Insert into pji_prg_batch
( batch_name,
wbs_total,
prg_total,
delta_total,
total_count,
project_count
) values (
l_batch_name||l_batch_var,
l_wbs_total,
l_prg_total,
l_delta_total,
l_batch_count,
l_cnt);
Insert into pji_prg_group
( batch_name,
prg_group,
prg_level,
project_id,
parent_program_id
) values (
l_batch_name||l_batch_var,
Null,
Null,
k.project_id,
null );
Insert into pji_prg_group
( batch_name,
prg_group,
prg_level,
project_id,
parent_program_id
) values (
l_batch_name||l_batch_var,
Null,
Null,
l_prj_list(a),
null );
Insert into pji_prg_batch
( batch_name,
wbs_total,
prg_total,
delta_total,
total_count,
project_count
) values (
l_batch_name||l_batch_var,
l_wbs_total,
l_prg_total,
l_delta_total,
l_batch_count,
l_cnt);
For k in ( select a.project_id
from pa_projects_all a
where ( nvl(p_incremental_mode,'N') = 'N' and g_launch_type <> 'RPPD' ) -- sridhar_phase_1 p_wbs_temp_table_size <> 666666
and template_flag = 'N' -- Bug 9059688
and nvl(a.org_id,-1) = nvl(p_operating_unit,nvl(a.org_id,-1))
and
nvl(a.project_status_code,'PS')=nvl(p_project_status,nvl(a.project_status_code,'PS'))
and not exists
( select 'x'
from pji_prg_group b where a.project_id = b.project_id )
union all
select a.project_id
from pji_launch_incr a, pa_projects_all b /* added for bug 9712797 */
where ( nvl(p_incremental_mode,'N') = 'Y' or g_launch_type = 'RPPD' ) -- sridhar_phase_1 p_wbs_temp_table_size = 666666
and a. project_id = b.project_id /* added for bug 9712797 */
-- Moved following condition to the next line for UPPD case. Not applicable for RPPD case
-- and nvl(b.project_status_code,'PS') = nvl(p_project_status,nvl(b.project_status_code,'PS')) /* added for bug 9712797 */
and (
( incr_type like 'PROJ%' and g_launch_type = 'UPPD'
and nvl(b.project_status_code,'PS') = nvl(p_project_status,nvl(b.project_status_code,'PS')) /* added for bug 9712797 */
) -- sridhar_phase_1 p_wbs_temp_table_size <> 666666
or
( incr_type like 'REF%' and g_launch_type = 'RPPD' ) -- sridhar_phase_1 p_wbs_temp_table_size = 666666
)
and prg_group = -1
and not exists
( select 'x'
from pji_prg_group b where a.project_id = b.project_id )
)
loop
Insert into pji_prg_group
( batch_name,
prg_group,
prg_level,
project_id,
parent_program_id
) values (
l_batch_name||l_batch_var,
Null,
Null,
k.project_id,
null );
Insert into pji_prg_batch
( batch_name,
wbs_total,
prg_total,
delta_total,
total_count,
project_count
) values (
l_batch_name||l_batch_var,
l_wbs_total,
l_prg_total,
l_delta_total,
l_batch_count,
l_cnt);
Insert into pji_prg_batch
( batch_name,
wbs_total,
prg_total,
delta_total,
total_count,
project_count
) values (
l_batch_name||l_batch_var,
l_wbs_total,
l_prg_total,
l_delta_total,
l_batch_count,
l_cnt);
select rpad(p1.segment1,30)||','||
decode(ATC_COST,0,'--','ITD Actual Cost')||
decode(BACKLOG,0,',--',', ITD Backlog')||
decode(BUD_COST,0,',--',', At Completion Cost Budget')||
decode(BUD_REV,0,',--',', At Completion Revenue Budget') ||
decode(WP_COST,0,',--',', Workplan Actuals') || /* Added for bug 12328939 */
decode(FC_COST,0,',--',', Cost Forecast Actuals') project_line /* Added for bug 12328939 */
from
(select
project_id ,
sum(decode(incr_type, 'VAL_ACT_COST',1,0)) ATC_COST ,
sum(decode(incr_type, 'VAL_BACKLOG',1,0)) BACKLOG ,
sum(decode(incr_type, 'VAL_BUD_COST',1,0)) BUD_COST ,
sum(decode(incr_type, 'VAL_BUD_REV',1,0)) BUD_REV,
sum(decode(incr_type, 'VAL_ACT_WP_COST',1,0)) WP_COST, /* Added for bug 12328939 */
sum(decode(incr_type, 'VAL_ACT_FC_COST',1,0)) FC_COST /* Added for bug 12328939 */
from
( select distinct incr_type , project_id
FROM PJI_LAUNCH_INCR where incr_type like 'VAL%'
) GROUP BY project_id ) t1 , pa_projects_all p1
where p1.project_id = t1.project_id
order by p1.segment1 ;
select
sum(decode(incr_type, 'VAL_ACT_COST',1,0)) ATC_COST ,
sum(decode(incr_type, 'VAL_BACKLOG',1,0)) BACKLOG ,
sum(decode(incr_type, 'VAL_BUD_COST',1,0)) BUD_COST ,
sum(decode(incr_type, 'VAL_BUD_REV',1,0)) BUD_REV ,
sum(decode(incr_type, 'VAL_ACT_WP_COST',1,0)) WP_COST , /* Added for bug 12328939 */
sum(decode(incr_type, 'VAL_ACT_FC_COST',1,0)) FC_COST , /* Added for bug 12328939 */
count( distinct project_id ) proj_count
into
l_ATC_COST,
l_BACKLOG ,
l_BUD_COST ,
l_BUD_REV ,
l_WP_COST , /* Added for bug 12328939 */
l_FC_COST , /* Added for bug 12328939 */
l_proj_count
from
( select distinct incr_type , project_id
FROM PJI_LAUNCH_INCR where incr_type like 'VAL%'
);
DELETE FROM PJI_LAUNCH_INCR where incr_type like 'VAL%';
SELECT DISTINCT pa.project_id project_id
FROM (SELECT a.project_id ,
SUM (a.total_accrued_amount) total_rev
FROM pa_summary_project_fundings a,
pa_projects_all ppa
where PA_PROJECT_UTILS.CHECK_PRJ_STUS_ACTION_ALLOWED(ppa.project_status_code,'STATUS_REPORTING') = 'Y'
and a.project_id = ppa.project_id
GROUP BY a.project_id
)
base_rev,
(SELECT pf.project_id
,sum(pf.projfunc_allocated_amount) Net_fund
FROM pa_project_fundings pf
,pa_agreements_all agr
,pa_project_customers cust,
pa_projects_all ppa
WHERE 1=1
and pf.project_id = ppa.project_id
and PA_PROJECT_UTILS.CHECK_PRJ_STUS_ACTION_ALLOWED(ppa.project_status_code,'STATUS_REPORTING') = 'Y'
AND pf.agreement_id = agr.agreement_id
AND pf.project_id = cust.project_id
AND pf.BUDGET_TYPE_CODE = 'BASELINE'
AND agr.customer_id = cust.customer_id
AND NVL(cust.bill_another_project_flag,'N') <> 'Y'
AND pf.date_allocated is not null
GROUP BY pf.project_id
) base_fund,
(SELECT f.project_id,
sum(initial_funding_amount
+ additional_funding_amount
+ cancelled_funding_amount
+ funding_adjustment_amount) total_fund
FROM pji_ac_xbs_accum_f f,
pa_projects_all ppa
WHERE f.project_id = ppa.project_id
and PA_PROJECT_UTILS.CHECK_PRJ_STUS_ACTION_ALLOWED(ppa.project_status_code,'STATUS_REPORTING') = 'Y'
AND f.currency_code = ppa.projfunc_currency_code
AND time_id = -1
AND wbs_rollup_flag = 'N'
AND initial_funding_amount IS NOT NULL
GROUP BY f.project_id
)
report ,
pa_projects_all pa,
pa_project_statuses pps
WHERE pa.project_id = base_fund.project_id
and pa.project_id = base_rev.project_id
AND base_rev.project_id = report.project_id
AND base_fund.project_id = report.project_id
AND pa.project_status_code = pps.project_status_code
AND pa.org_id = nvl(p_operating_unit, pa.org_id )
AND pa.template_flag = 'N'
and nvl((base_fund.net_fund-base_rev.total_rev), -99) <>
nvl((report.total_fund - base_rev.total_rev),-99) ;
select distinct costbud.project_id project_id
from (SELECT budgets.project_id, reporting.project_status_code,
budgets.cost BUDGET_COST,
reporting.cost REPORTING_COST,
budgets.cost - reporting.cost cost_diff_budgets
FROM (SELECT ver.project_id,
sum(ver.burdened_cost) cost
FROM pa_budget_versions ver,
pa_projects_all ppa
WHERE ver.budget_status_code = 'B'
and ver.project_id = ppa.project_id
and PA_PROJECT_UTILS.CHECK_PRJ_STUS_ACTION_ALLOWED(ppa.project_status_code,'STATUS_REPORTING') = 'Y'
AND ver.current_flag = 'Y'
AND ver.fin_plan_type_id IN (SELECT fin_plan_type_id
FROM pa_fin_plan_types_b
WHERE approved_cost_plan_type_flag ='Y')
group by ver.project_id) budgets
LEFT OUTER JOIN
(SELECT f.project_id, p.project_status_code,
Sum(brdn_cost) cost
FROM pji_fp_xbs_accum_f f,
pa_projects_all p
WHERE plan_version_id = -3
and PA_PROJECT_UTILS.CHECK_PRJ_STUS_ACTION_ALLOWED(p.project_status_code,'STATUS_REPORTING') = 'Y'
AND rbs_aggr_level = 'T'
AND wbs_rollup_flag = 'Y'
AND calendar_type = 'A'
AND time_id = -1
AND project_element_id = apps.pa_project_structure_utils.Get_fin_structure_id(f.project_id)
AND plan_type_id IN (SELECT fin_plan_type_id
FROM pa_fin_plan_types_b
WHERE approved_cost_plan_type_flag = 'Y')
AND f.currency_code = p.projfunc_currency_code
AND f.project_id = p.project_id
GROUP BY f.project_id, p.project_status_code) reporting
ON budgets.project_id = reporting.project_id ) costbud, pa_projects_all pa
where nvl(costbud.BUDGET_COST, 0) <> nvl(costbud.REPORTING_COST, 0)
AND pa.project_id = costbud.project_id
AND pa.template_flag = 'N'
AND pa.org_id = nvl(p_operating_unit, pa.org_id )
AND ( not exists ( select 'x' from PJI_LAUNCH_INCR l1 where l1.project_id = pa.project_id )
or l_run_type <> 'REFRESH');
select distinct revbud.project_id project_id from (
SELECT budgets.project_id, reporting.project_status_code,
budgets.revenue BUDGET_REVENUE,
reporting.revenue REPORTING_REVENUE,
budgets.revenue - reporting.revenue rev_diff_budgets
FROM (SELECT ver.project_id,
sum(ver.revenue) revenue
FROM pa_budget_versions ver,
pa_projects_all ppa
WHERE ver.budget_status_code = 'B'
and ver.project_id = ppa.project_id
and PA_PROJECT_UTILS.CHECK_PRJ_STUS_ACTION_ALLOWED(ppa.project_status_code,'STATUS_REPORTING') = 'Y'
AND ver.current_flag = 'Y'
AND ver.fin_plan_type_id IN (SELECT fin_plan_type_id
FROM pa_fin_plan_types_b
WHERE approved_rev_plan_type_flag ='Y')
group by ver.project_id) budgets
LEFT OUTER JOIN
(SELECT f.project_id, p.project_status_code,
Sum(revenue) revenue
FROM pji_fp_xbs_accum_f f,
pa_projects_all p
WHERE plan_version_id = -3
and PA_PROJECT_UTILS.CHECK_PRJ_STUS_ACTION_ALLOWED(p.project_status_code,'STATUS_REPORTING') = 'Y'
AND rbs_aggr_level = 'T'
AND wbs_rollup_flag = 'Y'
AND calendar_type = 'A'
AND time_id = -1
AND project_element_id = apps.pa_project_structure_utils.Get_fin_structure_id(f.project_id)
AND plan_type_id IN (SELECT fin_plan_type_id
FROM pa_fin_plan_types_b
WHERE approved_rev_plan_type_flag = 'Y')
AND f.currency_code = p.projfunc_currency_code
AND f.project_id = p.project_id
group by f.project_id, p.project_status_code) reporting
ON budgets.project_id = reporting.project_id ) revbud, pa_projects_all pa
where nvl(revbud.BUDGET_REVENUE, 0) <> nvl(revbud.REPORTING_REVENUE, 0)
AND pa.project_id = revbud.project_id
AND pa.template_flag = 'N'
AND pa.org_id = nvl(p_operating_unit, pa.org_id )
AND ( not exists ( select 'x' from PJI_LAUNCH_INCR l1 where l1.project_id = pa.project_id )
or l_run_type <> 'REFRESH');
SELECT distinct p1.project_id
FROM pa_projects_all p1 ,
(SELECT
/*+ ordered */
SUM(cdl.burdened_cost) amt,
pa.project_id
FROM pa_projects_all pa,
pa_cost_distribution_lines_all cdl
WHERE pa.template_flag = 'N'
and PA_PROJECT_UTILS.CHECK_PRJ_STUS_ACTION_ALLOWED(pa.project_status_code,'STATUS_REPORTING') = 'Y'
AND pa.project_id = cdl.project_id
AND cdl.line_type = 'R'
AND cdl.pji_summarized_flag IS NULL
AND pa.org_id = nvl(p_operating_unit, pa.org_id )
GROUP BY pa.project_id
) cdl1 ,
(SELECT
/*+ ordered */
brdn_cost amt,
f.project_id
FROM pa_projects_all p ,
pji_fp_xbs_accum_f f
WHERE p.project_id = f.project_id
and PA_PROJECT_UTILS.CHECK_PRJ_STUS_ACTION_ALLOWED(p.project_status_code,'STATUS_REPORTING') = 'Y'
AND f.currency_code = p.projfunc_currency_code
AND f.plan_type_id = -1
AND f.rbs_version_id = -1
AND f.calendar_type = 'A'
AND f.rbs_aggr_level = 'T'
AND f.wbs_rollup_flag = 'Y'
AND f.prg_rollup_flag = 'N'
AND period_type_id = 2048
AND time_id = -1
AND project_element_id = pa_project_structure_utils.Get_fin_structure_id(f.project_id)
AND p.template_flag = 'N'
AND p.org_id = nvl(p_operating_unit, p.org_id )
) fact1
WHERE p1.project_id = cdl1.project_id (+)
AND p1.project_id = fact1.project_id (+)
AND NVL(cdl1.amt,0) <> NVL(fact1.amt,0)
AND (abs(NVL(cdl1.amt,0) - NVL(fact1.amt,0)) > l_tolerance_amt)
AND p1.org_id = nvl(p_operating_unit, p1.org_id )
AND ( not exists ( select 'x' from PJI_LAUNCH_INCR l1 where l1.project_id = p1.project_id )
or l_run_type <> 'REFRESH') ;
SELECT distinct p1.project_id
FROM pa_projects_all p1 ,
(SELECT project_id,
Sum(Nvl(act_labor_hrs,0)) act_ppl_effort,
Sum(Nvl(act_equip_hrs,0)) act_eq_effort,
Sum(Nvl(act_raw_cost,0)) act_raw_cost,
Sum(Nvl(act_brdn_cost,0)) act_brdn_cost
FROM pji_fp_xbs_accum_f f
WHERE plan_version_id IN (SELECT budget_version_id
FROM pa_budget_versions bv
WHERE bv.project_id = f.project_id
AND project_structure_version_id =
PA_PROJECT_STRUCTURE_UTILS.GET_LATEST_WP_VERSION(f.project_id))
AND rbs_version_id = -1 AND time_id = -1
AND rbs_aggr_level = 'T' AND wbs_rollup_flag = 'Y' AND prg_rollup_flag = 'N'
AND bitand(curr_record_type_id,4) = 4
AND project_element_id = (SELECT proj_element_id
from pa_proj_element_versions e
where e.project_id = f.project_id
and element_version_id =
PA_PROJECT_STRUCTURE_UTILS.GET_LATEST_WP_VERSION(f.project_id))
GROUP BY project_id) rep,
(SELECT res.project_id project_id,
Sum(Decode(res.resource_class_code,'PEOPLE',Nvl(init_quantity,0),0)) act_ppl_effort,
Sum(Decode(res.resource_class_code,'EQUIPMENT',Nvl(init_quantity,0),0)) act_eq_effort,
Sum(Nvl(init_raw_cost,0)) act_raw_cost,
Sum(Nvl(init_burdened_cost,0)) act_brdn_cost
FROM pa_budget_lines bl, pa_resource_assignments res
WHERE bl.budget_version_id IN (SELECT budget_version_id
FROM pa_budget_versions bv
WHERE bv.project_id = res.project_id
AND project_structure_version_id =
PA_PROJECT_STRUCTURE_UTILS.GET_LATEST_WP_VERSION(bv.project_id))
AND bl.budget_version_id = res.budget_version_id
AND bl.resource_assignment_id = res.resource_assignment_id
GROUP BY res.project_id) base
WHERE base.project_id = rep.project_id(+)
AND p1.project_id = base.project_id
AND PA_PROJECT_UTILS.CHECK_PRJ_STUS_ACTION_ALLOWED (p1.project_status_code,
'STATUS_REPORTING') = 'Y'
AND p1.template_flag = 'N'
AND (base.act_ppl_effort <> nvl(rep.act_ppl_effort,0)
OR base.act_eq_effort <> nvl(rep.act_eq_effort,0)
OR base.act_raw_cost <> nvl(rep.act_raw_cost,0)
OR base.act_brdn_cost <> nvl(rep.act_brdn_cost,0))
AND p1.org_id = nvl(p_operating_unit, p1.org_id )
AND ( not exists ( select 'x' from PJI_LAUNCH_INCR l1 where l1.project_id = p1.project_id )
or l_run_type <> 'REFRESH') ;
SELECT distinct p1.project_id
FROM pa_projects_all p1 ,
(SELECT project_id,
Sum(Nvl(act_labor_hrs,0)) act_ppl_effort,
Sum(Nvl(act_equip_hrs,0)) act_eq_effort,
Sum(Nvl(act_raw_cost,0)) act_raw_cost,
Sum(Nvl(act_brdn_cost,0)) act_brdn_cost
FROM pji_fp_xbs_accum_f f
WHERE plan_type_id IN (SELECT fin_plan_type_id
FROM pa_fin_plan_types_b
WHERE primary_cost_forecast_flag = 'Y')
AND plan_version_id = -3
AND rbs_version_id = -1 AND time_id = -1
AND rbs_aggr_level = 'T' AND wbs_rollup_flag = 'Y' AND prg_rollup_flag = 'N'
AND bitand(curr_record_type_id,4) = 4
AND project_element_id = pa_project_structure_utils.Get_fin_structure_id(f.project_id)
GROUP BY project_id) rep,
(SELECT res.project_id project_id,
Sum(Decode(res.resource_class_code,'PEOPLE',Nvl(init_quantity,0),0)) act_ppl_effort,
Sum(Decode(res.resource_class_code,'EQUIPMENT',Nvl(init_quantity,0),0)) act_eq_effort,
Sum(Nvl(init_raw_cost,0)) act_raw_cost,
Sum(Nvl(init_burdened_cost,0)) act_brdn_cost
FROM pa_budget_lines bl, pa_resource_assignments res
WHERE bl.budget_version_id IN (SELECT budget_version_id
FROM pa_budget_versions bv
WHERE bv.project_id = res.project_id
AND budget_status_code = 'B'
AND current_flag = 'Y'
AND fin_plan_type_id IN (SELECT fin_plan_type_id
FROM pa_fin_plan_types_b
WHERE primary_cost_forecast_flag = 'Y'))
AND bl.budget_version_id = res.budget_version_id
AND bl.resource_assignment_id = res.resource_assignment_id
GROUP BY res.project_id) base
WHERE base.project_id = rep.project_id(+)
AND p1.project_id = base.project_id
AND PA_PROJECT_UTILS.CHECK_PRJ_STUS_ACTION_ALLOWED (p1.project_status_code, 'STATUS_REPORTING') = 'Y'
AND p1.template_flag = 'N'
AND (base.act_ppl_effort <> nvl(rep.act_ppl_effort,0)
OR base.act_eq_effort <> nvl(rep.act_eq_effort,0)
OR base.act_raw_cost <> nvl(rep.act_raw_cost,0)
OR base.act_brdn_cost <> nvl(rep.act_brdn_cost,0))
AND p1.org_id = nvl(p_operating_unit, p1.org_id )
AND ( not exists ( select 'x' from PJI_LAUNCH_INCR l1 where l1.project_id = p1.project_id )
or l_run_type <> 'REFRESH') ;
DELETE FROM PJI_LAUNCH_INCR;
DELETE FROM PJI_LAUNCH_INCR; /* Added for bug 12328939 */
INSERT INTO PJI_LAUNCH_INCR
(incr_type, project_id, prg_group ,prg_level )
values
(l_data_type,
v_project_id(i),
-1,
-1);
v_project_id.delete;
INSERT INTO PJI_LAUNCH_INCR
(incr_type, project_id,prg_group , prg_level )
values
(l_data_type,
v_project_id(i),
-1,
-1);
v_project_id.delete;
INSERT INTO PJI_LAUNCH_INCR
(incr_type, project_id, prg_group ,prg_level )
values
(l_data_type,
v_project_id(i),
-1,
-1);
v_project_id.delete;
INSERT INTO PJI_LAUNCH_INCR
(incr_type, project_id, prg_group ,prg_level )
values
(l_data_type,
v_project_id(i),
-1,
-1);
v_project_id.delete;
INSERT INTO PJI_LAUNCH_INCR
(incr_type, project_id, prg_group ,prg_level )
values
(l_data_type,
v_project_id(i),
-1,
-1);
v_project_id.delete;
INSERT INTO PJI_LAUNCH_INCR
(incr_type, project_id, prg_group ,prg_level )
values
(l_data_type,
v_project_id(i),
-1,
-1);
v_project_id.delete;
UPDATE PJI_LAUNCH_INCR a
SET a.INCR_TYPE = 'PROJ_WRK'
WHERE
exists
( select 'x'
from pa_proj_element_versions c
where a.project_id = c.project_id
and c.object_type = 'PA_STRUCTURES'
AND c.prg_group IS NOT NULL ) ;
SELECT /*+ ordered index(a PA_PROJ_ELEMENT_VERSIONS_N6) index(b PA_OBJECT_RELATIONSHIPS_N4) */
DISTINCT
a0.project_id child_proj_id , a.prg_level child_prg_level,
c.project_id parent_proj_id , c.prg_group , c.prg_level parent_prg_level
FROM pji_launch_incr a0,
pa_proj_element_versions a,
pa_object_relationships b,
pa_proj_element_versions c
WHERE a0.incr_type in ( 'PROJ_WRK', 'PROJ_WRK_NEW' )
AND a.project_id = a0.project_id --51956 --5062 --5269 --51954
AND a.prg_group IS NOT NULL
AND a.object_type = 'PA_STRUCTURES'
AND a.object_type = b.object_type_to
AND a.element_version_id = b.object_id_to1
AND c.element_version_id = b.object_id_from1
AND b.relationship_type IN ('LW', 'LF')
) LOOP
l_prg_parent := 1;
UPDATE PJI_LAUNCH_INCR
SET prg_group = PRG_PARENT_NODE.prg_group,
incr_type = decode(incr_type,'PROJ_WRK','PROJ_B_PARENT','PROJ_PARENT'),
prg_level = PRG_PARENT_NODE.child_prg_level
WHERE project_id = PRG_PARENT_NODE.child_proj_id
AND incr_type in ( 'PROJ_WRK', 'PROJ_WRK_NEW' );
INSERT INTO PJI_LAUNCH_INCR a
(a.incr_type, a.prg_group , a.project_id , a.prg_level)
select
'PROJ_WRK_NEW',
PRG_PARENT_NODE.prg_group,
PRG_PARENT_NODE.parent_proj_id ,
PRG_PARENT_NODE.parent_prg_level
from dual
where not exists
(
select 'x'
from PJI_LAUNCH_INCR b
where PRG_PARENT_NODE.parent_proj_id = b.project_id
and PRG_PARENT_NODE.prg_group = b.prg_group
and PRG_PARENT_NODE.parent_prg_level = b.prg_level
and b.incr_type in ('PROJ_PARENT','PROJ_B_PARENT','PROJ_WRK_NEW')
);
UPDATE PJI_LAUNCH_INCR
SET incr_type = decode(incr_type,'PROJ_WRK',l_data_type,l_data_type_parent)
WHERE incr_type in ( 'PROJ_WRK', 'PROJ_WRK_NEW' );
DELETE FROM pji_data_validation;
insert into pji_data_validation select * from pji_launch_incr where incr_type like 'VAL%';
DELETE FROM PJI_LAUNCH_INCR where incr_type like 'VAL%';
select project_id
from pji_prg_group
where batch_name = p_batchname;
delete PJI_FP_XBS_ACCUM_F where project_id = v_project_id(i);
delete PJI_AC_XBS_ACCUM_F where project_id = v_project_id(i);
DELETE FROM PJI_LAUNCH_INCR;
INSERT INTO PJI_LAUNCH_INCR
(incr_type, prg_group , project_id, prg_level )
SELECT /*+ ordered use_nl(log ver ) index(ver PA_PROJ_ELEMENT_VERSIONS_N5) */
DISTINCT
'PRG_BASE' incr_type ,
ver.PRG_GROUP ,
- 1 project_id ,
-1 prg_level
FROM
PA_PJI_PROJ_EVENTS_LOG LOG ,
PA_PROJ_ELEMENT_VERSIONS ver
WHERE
ver.object_type = 'PA_STRUCTURES' AND
log.EVENT_TYPE = 'PRG_CHANGE' AND
log.EVENT_OBJECT <> - 1 AND
ver.PRG_GROUP IN (log.EVENT_OBJECT, log.ATTRIBUTE1)
and exists ( select 'x' from
pa_projects_all p1
where p1.project_id = ver.project_id
and nvl(p1.org_id,-1) = nvl(p_operating_unit,nvl(p1.org_id,-1)));
INSERT INTO PJI_LAUNCH_INCR
(incr_type, prg_group , project_id , prg_level)
SELECT
DISTINCT
'PROJ_BASE',
- 1,
to_number(log.attribute1),
- 1
FROM
PA_PJI_PROJ_EVENTS_LOG LOG
WHERE
log.EVENT_TYPE = 'PRG_CHANGE' AND
log.EVENT_OBJECT = - 1
AND NOT EXISTS
(SELECT 'x' FROM
PJI_LAUNCH_INCR grp2
WHERE grp2.incr_type = 'PROJ_BASE'
AND grp2.prg_group = - 1
AND grp2.project_id = to_number(log.attribute1) )
and exists ( select 'x' from
pa_projects_all p1
where p1.project_id = to_number(log.attribute1)
and nvl(p1.org_id,-1) = nvl(p_operating_unit,nvl(p1.org_id,-1)));
INSERT INTO PJI_LAUNCH_INCR
(incr_type, prg_group , project_id, prg_level )
SELECT
DISTINCT
'PROJ_BASE',
- 1,
to_number(log.attribute1),
-1
FROM
PA_PJI_PROJ_EVENTS_LOG LOG
WHERE
log.EVENT_TYPE IN ('RBS_ASSOC', 'RBS_PRG' )
AND NOT EXISTS
(SELECT 'x' FROM
PJI_LAUNCH_INCR grp2
WHERE grp2.incr_type = 'PROJ_BASE'
AND grp2.prg_group = - 1
AND grp2.project_id = to_number(log.attribute1) )
and exists ( select 'x' from
pa_projects_all p1
where p1.project_id = to_number(log.attribute1)
and nvl(p1.org_id,-1) = nvl(p_operating_unit,nvl(p1.org_id,-1)));
INSERT INTO PJI_LAUNCH_INCR
(incr_type, prg_group , project_id , prg_level)
SELECT
DISTINCT
'PROJ_BASE',
- 1,
asg.project_id,
-1
FROM
PA_PJI_PROJ_EVENTS_LOG LOG,
PA_RBS_PRJ_ASSIGNMENTS asg
WHERE
log.EVENT_TYPE = 'RBS_PUSH' AND
asg.RBS_VERSION_ID IN (log.EVENT_OBJECT, log.ATTRIBUTE2)
AND NOT EXISTS
(SELECT 'x' FROM
PJI_LAUNCH_INCR grp2
WHERE grp2.incr_type = 'PROJ_BASE'
AND grp2.prg_group = - 1
AND grp2.project_id = asg.project_id )
and exists ( select 'x' from
pa_projects_all p1
where p1.project_id = asg.project_id
and nvl(p1.org_id,-1) = nvl(p_operating_unit,nvl(p1.org_id,-1)));
INSERT INTO PJI_LAUNCH_INCR
(incr_type, prg_group , project_id , prg_level)
SELECT
DISTINCT
'PROJ_BASE',
- 1,
asg.project_id,
- 1
FROM
PA_PJI_PROJ_EVENTS_LOG LOG,
PA_RBS_PRJ_ASSIGNMENTS asg
WHERE
log.EVENT_TYPE = 'RBS_DELETE' AND
asg.RBS_VERSION_ID = log.EVENT_OBJECT
AND NOT EXISTS
(SELECT 'x' FROM
PJI_LAUNCH_INCR grp2
WHERE grp2.incr_type = 'PROJ_BASE'
AND grp2.prg_group = - 1
AND grp2.project_id = asg.project_id )
and exists ( select 'x' from
pa_projects_all p1
where p1.project_id = asg.project_id
and nvl(p1.org_id,-1) = nvl(p_operating_unit,nvl(p1.org_id,-1))) ;
insert into PJI_LAUNCH_INCR
( incr_type, prg_group , project_id )
select
distinct
'PROJ_BASE',
-1 ,
project_id
from
pji_fm_extr_plan_lines a1
where not exists
( select 'x' from
PJI_LAUNCH_INCR grp2
where grp2.incr_type = 'PROJ_BASE'
and grp2.prg_group = -1
and grp2.project_id = a1.project_id );
INSERT INTO PJI_LAUNCH_INCR
(incr_type, prg_group , project_id , prg_level)
SELECT
DISTINCT
'PROJ_BASE',
- 1,
project_id,
- 1
FROM PJI_FM_AGGR_FIN7 a1
WHERE NOT EXISTS
(SELECT 'x' FROM
PJI_LAUNCH_INCR grp2
WHERE grp2.incr_type = 'PROJ_BASE'
AND grp2.prg_group = - 1
AND grp2.project_id = a1.project_id )
and exists ( select 'x' from
pa_projects_all p1
where p1.project_id = a1.project_id
and nvl(p1.org_id,-1) = nvl(p_operating_unit,nvl(p1.org_id,-1)));
INSERT INTO PJI_LAUNCH_INCR
(incr_type, prg_group , project_id , prg_level )
SELECT
DISTINCT
'PROJ_BASE',
- 1,
project_id ,
- 1
FROM PJI_FM_AGGR_ACT4 a1
WHERE NOT EXISTS
(SELECT 'x' FROM
PJI_LAUNCH_INCR grp2
WHERE grp2.incr_type = 'PROJ_BASE'
AND grp2.prg_group = - 1
AND grp2.project_id = a1.project_id )
and exists ( select 'x' from
pa_projects_all p1
where p1.project_id = a1.project_id
and nvl(p1.org_id,-1) = nvl(p_operating_unit,nvl(p1.org_id,-1)));
INSERT INTO PJI_LAUNCH_INCR
(incr_type, prg_group , project_id , prg_level )
SELECT
DISTINCT
'PROJ_BASE',
- 1,
project_id,
- 1
FROM PA_BUDGET_VERSIONS a1
WHERE budget_status_code = 'B' AND
pji_summarized_flag = 'P' AND
NOT EXISTS
(SELECT 'x' FROM
PJI_LAUNCH_INCR grp2
WHERE grp2.incr_type = 'PROJ_BASE'
AND grp2.prg_group = - 1
AND grp2.project_id = a1.project_id )
and exists ( select 'x' from
pa_projects_all p1
where p1.project_id = a1.project_id
and nvl(p1.org_id,-1) = nvl(p_operating_unit,nvl(p1.org_id,-1)));
insert into PJI_LAUNCH_INCR
( incr_type, prg_group , project_id )
select
distinct
'PROJ_BASE',
-1 ,
project_id
from pji_fm_aggr_fin8 a1
where
not exists
( select 'x' from
PJI_LAUNCH_INCR grp2
where grp2.incr_type = 'PROJ_BASE'
and grp2.prg_group = -1
and grp2.project_id = a1.project_id );
UPDATE PJI_LAUNCH_INCR a
SET a.INCR_TYPE = 'PROJ_PRG'
WHERE a.INCR_TYPE = 'PROJ_BASE'
AND EXISTS
(SELECT /*+ ordered index(c PA_PROJ_ELEMENT_VERSIONS_N6) */ 'x'
FROM
pa_proj_element_versions c,
PJI_LAUNCH_INCR b
WHERE a.project_id = c.project_id
and c.object_type = 'PA_STRUCTURES'
AND c.prg_group IS NOT NULL
AND c.prg_group = b.prg_group
AND b.incr_type = 'PRG_BASE'
);
UPDATE PJI_LAUNCH_INCR a
SET a.INCR_TYPE = 'PROJ_WRK'
WHERE a.INCR_TYPE = 'PROJ_BASE' and
exists
( select 'x'
from pa_proj_element_versions c
where a.project_id = c.project_id
and c.object_type = 'PA_STRUCTURES'
AND c.prg_group IS NOT NULL ) ;
SELECT /*+ ordered index(a PA_PROJ_ELEMENT_VERSIONS_N6) index(b PA_OBJECT_RELATIONSHIPS_N4) */
DISTINCT
a0.project_id child_proj_id , a.prg_level child_prg_level,
c.project_id parent_proj_id , c.prg_group , c.prg_level parent_prg_level
FROM pji_launch_incr a0,
pa_proj_element_versions a,
pa_object_relationships b,
pa_proj_element_versions c
WHERE a0.incr_type in ( 'PROJ_WRK', 'PROJ_WRK_NEW' )
AND a.project_id = a0.project_id --51956 --5062 --5269 --51954
AND a.prg_group IS NOT NULL
AND a.object_type = 'PA_STRUCTURES'
AND a.object_type = b.object_type_to
AND a.element_version_id = b.object_id_to1
AND c.element_version_id = b.object_id_from1
AND b.relationship_type IN ('LW', 'LF')
) LOOP
l_prg_parent := 1;
UPDATE PJI_LAUNCH_INCR
SET prg_group = PRG_PARENT_NODE.prg_group,
incr_type = decode(incr_type,'PROJ_WRK','PROJ_B_PARENT','PROJ_PARENT'),
prg_level = PRG_PARENT_NODE.child_prg_level
WHERE project_id = PRG_PARENT_NODE.child_proj_id
AND incr_type in ( 'PROJ_WRK', 'PROJ_WRK_NEW' );
INSERT INTO PJI_LAUNCH_INCR a
(a.incr_type, a.prg_group , a.project_id , a.prg_level)
select
'PROJ_WRK_NEW',
PRG_PARENT_NODE.prg_group,
PRG_PARENT_NODE.parent_proj_id ,
PRG_PARENT_NODE.parent_prg_level
from dual
where not exists
(
select 'x'
from PJI_LAUNCH_INCR b
where PRG_PARENT_NODE.parent_proj_id = b.project_id
and PRG_PARENT_NODE.prg_group = b.prg_group
and PRG_PARENT_NODE.parent_prg_level = b.prg_level
and b.incr_type in ('PROJ_PARENT','PROJ_B_PARENT','PROJ_WRK_NEW')
);
UPDATE PJI_LAUNCH_INCR
SET incr_type = decode(incr_type,'PROJ_WRK','PROJ_BASE','PROJ_PARENT')
WHERE incr_type in ( 'PROJ_WRK', 'PROJ_WRK_NEW' );
INSERT INTO PJI_LAUNCH_INCR
(incr_type, prg_group , project_id ,prg_level)
SELECT /*+ ordered index(ver PA_PROJ_ELEMENT_VERSIONS_N6) */
DISTINCT
'PROJ_BASE_MAP' incr_type ,
nvl(ver.PRG_GROUP,-1) ,
map1.project_id project_id ,
nvl(ver.prg_level,-1)
FROM
PJI_PJP_PROJ_BATCH_MAP map1 ,
PA_PROJ_ELEMENT_VERSIONS ver
WHERE
ver.object_type = 'PA_STRUCTURES' AND
ver.project_id = map1.project_id ;
UPDATE PJI_LAUNCH_INCR a
set a.prg_group = -2
where incr_type = 'PROJ_BASE_MAP'
and exists
(
SELECT 'x' FROM PJI_LAUNCH_INCR grp2
WHERE grp2.prg_group = nvl(a.prg_group,-1) AND grp2.incr_type = 'PRG_BASE' AND grp2.project_id = - 1) ;