[Home] [Help]
394: nvl(p_regenerate_batches,'N'), -- sridhar_refresh
395: P_OPERATING_UNIT,
396: p_project_status) ; -- Call here the batch creation procedure
397:
398: update pji_prg_group
399: set batch_name = 'UPP-BATCH'||'-R-'||l_reg_num||substr(batch_name,10,5) ;
400:
401: update pji_prg_batch
402: set batch_name = 'UPP-BATCH'||'-R-'||l_reg_num||substr(batch_name,10,5) ;
401: update pji_prg_batch
402: set batch_name = 'UPP-BATCH'||'-R-'||l_reg_num||substr(batch_name,10,5) ;
403:
404: --bug 7121511 start
405: update pji_prg_group c set c.batch_name = c.batch_name||'-ERR'
406: where c.prg_group in
407: ( select distinct b.prg_group
408: from pji_pjp_proj_batch_map a,
409: pji_prg_group b
405: update pji_prg_group c set c.batch_name = c.batch_name||'-ERR'
406: where c.prg_group in
407: ( select distinct b.prg_group
408: from pji_pjp_proj_batch_map a,
409: pji_prg_group b
410: where a.project_id = b.project_id
411: and b.prg_group is not null
412: );
413:
410: where a.project_id = b.project_id
411: and b.prg_group is not null
412: );
413:
414: update pji_prg_group c set c.batch_name = c.batch_name||'-ERR'
415: where c.project_id in
416: ( select distinct b.project_id
417: from pji_pjp_proj_batch_map a,
418: pji_prg_group b
414: update pji_prg_group c set c.batch_name = c.batch_name||'-ERR'
415: where c.project_id in
416: ( select distinct b.project_id
417: from pji_pjp_proj_batch_map a,
418: pji_prg_group b
419: where a.project_id = b.project_id
420: and b.prg_group is null
421: );
422: --bug 7121511 end
429: total_count,
430: project_count
431: )
432: select distinct batch_name ,0,0,0,0,0
433: from pji_prg_group where batch_name like '%-ERR';
434:
435: -- bug 6276970 & 6505683
436: -- Sridhar Added below insert and update statement
437:
434:
435: -- bug 6276970 & 6505683
436: -- Sridhar Added below insert and update statement
437:
438: update pji_prg_group c set c.batch_name = ( select d1.value
439: from pji_system_parameters d1 , pji_pjp_proj_batch_map a1,
440: pji_prg_group b1
441: where a1.project_id = b1.project_id
442: and c.batch_name like '%-ERR'
436: -- Sridhar Added below insert and update statement
437:
438: update pji_prg_group c set c.batch_name = ( select d1.value
439: from pji_system_parameters d1 , pji_pjp_proj_batch_map a1,
440: pji_prg_group b1
441: where a1.project_id = b1.project_id
442: and c.batch_name like '%-ERR'
443: and b1.prg_group = c.prg_group
444: and to_number(substr(d1.name,8,instr(d1.name,'$',1) - 8)) = a1.worker_id -- Sridhar changed added substr june-11th V1_CHANGE
450: c.batch_name like '%-ERR'
451: and exists
452: ( select 'x'
453: from pji_system_parameters d2 , pji_pjp_proj_batch_map a2,
454: pji_prg_group b2
455: where a2.project_id = b2.project_id
456: and b2.prg_group = c.prg_group
457: and to_number(substr(d2.name,8,instr(d2.name,'$',1) - 8)) = a2.worker_id -- Sridhar changed added substr june-11th V1_CHANGE
458: and d2.name like '%FROM_PROJECT'
460: and d2.value like 'UPP-BATCH%' );
461:
462: -- Sridhar changed june-11th Added new Update Statement START V1_CHANGE
463:
464: update pji_prg_group c set c.batch_name = ( select d1.value
465: from pji_system_parameters d1 , pji_pjp_proj_batch_map a1,
466: pji_prg_group b1
467: where a1.project_id = b1.project_id
468: and c.batch_name like '%-ERR'
462: -- Sridhar changed june-11th Added new Update Statement START V1_CHANGE
463:
464: update pji_prg_group c set c.batch_name = ( select d1.value
465: from pji_system_parameters d1 , pji_pjp_proj_batch_map a1,
466: pji_prg_group b1
467: where a1.project_id = b1.project_id
468: and c.batch_name like '%-ERR'
469: and b1.project_id = c.project_id
470: and to_number(substr(d1.name,8,instr(d1.name,'$',1) - 8)) = a1.worker_id
476: c.batch_name like '%-ERR'
477: and exists
478: ( select 'x'
479: from pji_system_parameters d2 , pji_pjp_proj_batch_map a2,
480: pji_prg_group b2
481: where a2.project_id = b2.project_id
482: and b2.project_id = c.project_id
483: and to_number(substr(d2.name,8,instr(d2.name,'$',1) - 8)) = a2.worker_id
484: and d2.name like '%FROM_PROJECT'
493: total_count,
494: project_count
495: )
496: select distinct batch_name ,0,0,0,0,0
497: from pji_prg_group a2 where a2.batch_name not in ( select c1.batch_name from pji_prg_batch c1 );
498:
499: UPDATE pji_prg_batch c
500: SET c.curr_request_id =
501: (
519: delete from pji_prg_batch a
520: where not exists
521: (
522: select 'x'
523: from pji_prg_group b
524: where a.batch_name = b.batch_name );
525:
526: commit;
527: end if; /* if statement for re-generate batches */
1017: end if ;
1018:
1019: -- sridhar_refresh end change
1020:
1021: delete from pji_prg_group;
1022: delete from pji_prg_batch;
1023:
1024: commit;
1025:
1113: and a.object_type = 'PA_STRUCTURES'
1114: ) pa_struct ;
1115:
1116: For j in c_program(i.prg_group,i.prg_type) loop
1117: Insert into pji_prg_group
1118: ( batch_name,
1119: prg_group,
1120: prg_level,
1121: project_id,
1249:
1250: if (( l_budget_lines_cnt + l_curr_lines_cnt
1251: <= l_budget_lines_count) or l_cnt = 0) THEN
1252:
1253: Insert into pji_prg_group
1254: ( batch_name,
1255: prg_group,
1256: prg_level,
1257: project_id,
1285: l_cnt := 0;
1286: l_batch_var := l_batch_var + 1;
1287: l_budget_lines_cnt := 0;
1288:
1289: Insert into pji_prg_group
1290: ( batch_name,
1291: prg_group,
1292: prg_level,
1293: project_id,
1321: --commenting out the below clause as not needed in this scenario
1322: /* and nvl(a.project_status_code,'PS') =
1323: nvl(p_project_status,nvl(a.project_status_code,'PS'))*/
1324: and not exists
1325: (select 'x' from pji_prg_group b
1326: where a.project_id = b.project_id)
1327: union all
1328: select project_id
1329: from pji_launch_incr a
1330: where nvl(p_incremental_mode,'N') = 'Y'
1331: and incr_type like 'PROJ%'
1332: and prg_group = -1
1333: and not exists
1334: (select 'x' from pji_prg_group b
1335: where a.project_id = b.project_id )) loop
1336:
1337: l_curr_lines_cnt := 0;
1338:
1345:
1346: if (( l_budget_lines_cnt + l_curr_lines_cnt
1347: <= l_budget_lines_count) or l_cnt = 0) THEN
1348:
1349: Insert into pji_prg_group
1350: ( batch_name,
1351: prg_group,
1352: prg_level,
1353: project_id,
1381: l_cnt := 0;
1382: l_batch_var := l_batch_var + 1;
1383: l_budget_lines_cnt := 0;
1384:
1385: Insert into pji_prg_group
1386: ( batch_name,
1387: prg_group,
1388: prg_level,
1389: project_id,
1406: elsif (l_prj_list.count > 0 and (l_context = 'INCREMENTAL' or l_context = 'UPGRADE')) then
1407:
1408: FOR a IN l_prj_list.first..l_prj_list.last LOOP
1409:
1410: Insert into pji_prg_group
1411: ( batch_name,
1412: prg_group,
1413: prg_level,
1414: project_id,
1457: and
1458: nvl(a.project_status_code,'PS')=nvl(p_project_status,nvl(a.project_status_code,'PS'))
1459: and not exists
1460: ( select 'x'
1461: from pji_prg_group b where a.project_id = b.project_id )
1462: union all
1463: select a.project_id
1464: from pji_launch_incr a, pa_projects_all b /* added for bug 9712797 */
1465: where ( nvl(p_incremental_mode,'N') = 'Y' or g_launch_type = 'RPPD' ) -- sridhar_phase_1 p_wbs_temp_table_size = 666666
1476: )
1477: and prg_group = -1
1478: and not exists
1479: ( select 'x'
1480: from pji_prg_group b where a.project_id = b.project_id )
1481: )
1482: loop
1483: Insert into pji_prg_group
1484: ( batch_name,
1479: ( select 'x'
1480: from pji_prg_group b where a.project_id = b.project_id )
1481: )
1482: loop
1483: Insert into pji_prg_group
1484: ( batch_name,
1485: prg_group,
1486: prg_level,
1487: project_id,
2249:
2250:
2251: cursor get_projects_for_fact_deletion(p_batchname varchar2) is
2252: select project_id
2253: from pji_prg_group
2254: where batch_name = p_batchname;
2255:
2256: TYPE vr1_tbl_type IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
2257: v_batchname vr1_tbl_type;