[Home] [Help]
61: )
62: IS
63:
64: TYPE Prg_Batch_t IS
65: TABLE OF pji_prg_batch%ROWTYPE
66: INDEX BY BINARY_INTEGER;
67:
68:
69: l_Prg_Batch_t Prg_Batch_t;
71: CURSOR prg_group IS
72: SELECT
73: *
74: FROM
75: pji_prg_batch
76: WHERE nvl(curr_request_status,'PENDING') <> 'COMPLETED' /* added for bug#10031149 */
77: ORDER BY
78: DECODE(nvl(curr_request_status,'PENDING') ,
79: 'PENDING' , 1,
184: -- during this run , so that these need not be picked up for
185: -- re-submission
186:
187: UPDATE
188: pji_prg_batch
189: SET
190: curr_request_status = decode(nvl(curr_request_status,'PENDING'),
191: 'ERRORED',
192: decode(l_Prg_Batch_t(i).curr_request_status,
203: --Sridhar July - 10th change
204:
205: BEGIN
206:
207: UPDATE pji_prg_batch
208: SET curr_request_status = 'ERRORED'
209: WHERE nvl(curr_request_id,-1) > 0
210: and curr_request_status = 'COMPLETED'
211: and not exists
244: p_project_status in varchar2
245: )
246: is
247: TYPE Prg_Batch_t IS
248: TABLE OF pji_prg_batch%ROWTYPE
249: INDEX BY BINARY_INTEGER;
250:
251: l_Prg_Batch_t Prg_Batch_t;
252:
267: l_num_of_batches number := 0;
268:
269: CURSOR prg_group IS
270: SELECT *
271: FROM pji_prg_batch
272: ORDER BY DECODE(nvl(curr_request_status,'PENDING'),
273: 'ERRORED',curr_request_status),
274: to_number(rtrim(substrb(replace(batch_name,'-ERR',''),19,10))); /* Modified for bug 9109118 */ /* Modified for bug 14760728 */
275: /* Modified substrb value from 11 to 19 for bug 12570872 */
370:
371: select 'Y' into l_reg_flag
372: from dual
373: where exists
374: ( select 'x' from pji_prg_batch where substr(batch_name,10,3) = '-R-' );
375: select to_number(substr(batch_name,13,5)) into l_reg_num
376: from pji_prg_batch
377: where substr(batch_name,10,3) = '-R-'
378: and rownum = 1;
372: from dual
373: where exists
374: ( select 'x' from pji_prg_batch where substr(batch_name,10,3) = '-R-' );
375: select to_number(substr(batch_name,13,5)) into l_reg_num
376: from pji_prg_batch
377: where substr(batch_name,10,3) = '-R-'
378: and rownum = 1;
379:
380: if ( l_reg_num >= 99999 ) then
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) ;
403:
404: --bug 7121511 start
405: update pji_prg_group c set c.batch_name = c.batch_name||'-ERR'
420: and b.prg_group is null
421: );
422: --bug 7121511 end
423:
424: Insert into pji_prg_batch
425: ( batch_name,
426: wbs_total,
427: prg_total,
428: delta_total,
484: and d2.name like '%FROM_PROJECT'
485: and b2.prg_group is null
486: and d2.value like 'UPP-BATCH%' );
487:
488: Insert into pji_prg_batch
489: ( batch_name,
490: wbs_total,
491: prg_total,
492: delta_total,
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: (
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: (
502: select b.value
503: from pji_system_parameters a , pji_system_parameters b
515: and b.name like 'PJI_PJP%PJI_PJP%'
516: and c.batch_name = a.value
517: and b.value is not null ) ;
518:
519: delete from pji_prg_batch a
520: where not exists
521: (
522: select 'x'
523: from pji_prg_group b
557: );
558:
559: curr_count_batches := l_count_batches;
560:
561: --- Check if any rows exists in the pji_prg_batches that
562: --- are still pending or errored or the table is empty
563: ---- if YES then call the procedure that creates the batches.
564:
565: BEGIN
564:
565: BEGIN
566:
567: SELECT count(*) into l_test
568: FROM pji_prg_batch
569: WHERE nvl(curr_request_status,'PENDING') <> 'COMPLETED'
570: and( exists -- added sridhar_refresh
571: ( select 'x' from PJI_LAUNCH_INCR
572: where incr_type = 'REFRESH' and g_launch_type = 'RPPD' ) -- sridhar_phase_1 p_temp_table_size = 666666 )
697:
698: IF ( l_request_id > 0 ) THEN
699:
700: UPDATE
701: pji_prg_batch
702: SET
703: curr_request_id = l_request_id ,
704: curr_request_status = 'RUNNING'
705: WHERE
704: curr_request_status = 'RUNNING'
705: WHERE
706: batch_name = l_Prg_Batch_t(i).batch_name ;
707:
708: INSERT INTO pji_prg_batch_log
709: ( run_date_key, run_date , request_id, batch_name , wbs_total, prg_total,
710: delta_total, total_count, project_count, custom1, custom2, custom3
711: )
712: values
723:
724: ELSE
725:
726: UPDATE
727: pji_prg_batch
728: SET
729: curr_request_id = l_request_id ,
730: message = 'Error calling FND_REQUEST.SUBMIT_REQUEST',
731: curr_request_status = 'SUBMIT-ERRORED'
772:
773: IF ( curr_count_batches < l_num_of_batches ) THEN
774:
775: SELECT count(*) into l_test
776: FROM pji_prg_batch
777: WHERE nvl(curr_request_status,'PENDING') not in ( 'COMPLETED' ,'SUBMIT-ERRORED' ,'R-ERRORED') ;
778:
779: IF ( l_test = 0 ) THEN
780:
802: -- now check if there are any process that are running
803: -- if yes then we have continue in the loop and
804:
805: SELECT count(*) into l_test
806: FROM pji_prg_batch
807: WHERE nvl(curr_request_status,'PENDING') in ( 'RUNNING') ;
808:
809: IF ( l_test = 0 ) THEN
810: l_no_running_request := 'Y';
839: /* Code added for bug 8416116 starts */
840: begin
841: select count(*) into
842: l_count_rerrored /* Modified for bug 9387564 */
843: from pji_prg_batch
844: where curr_request_status = 'R-ERRORED';
845:
846: exception
847: when others then
1018:
1019: -- sridhar_refresh end change
1020:
1021: delete from pji_prg_group;
1022: delete from pji_prg_batch;
1023:
1024: commit;
1025:
1026: if ( nvl(p_wbs_temp_table_size,0) <= 0 ) then
1144: end if;
1145:
1146: If ( ( l_cnt >= l_batch_size ) or ( l_batch_count >= l_wbs_prg_size ) ) then
1147:
1148: Insert into pji_prg_batch
1149: ( batch_name,
1150: wbs_total,
1151: prg_total,
1152: delta_total,
1179: End loop;
1180:
1181: if ( l_cnt > 0 ) then
1182:
1183: Insert into pji_prg_batch
1184: ( batch_name,
1185: wbs_total,
1186: prg_total,
1187: delta_total,
1266: l_budget_lines_cnt := l_budget_lines_cnt + l_curr_lines_cnt;
1267: l_cnt := l_cnt + 1;
1268:
1269: else
1270: Insert into pji_prg_batch
1271: ( batch_name,
1272: wbs_total,
1273: prg_total,
1274: delta_total,
1362: l_budget_lines_cnt := l_budget_lines_cnt + l_curr_lines_cnt;
1363: l_cnt := l_cnt + 1;
1364:
1365: else
1366: Insert into pji_prg_batch
1367: ( batch_name,
1368: wbs_total,
1369: prg_total,
1370: delta_total,
1423: l_cnt := l_cnt + 1;
1424:
1425: if l_cnt >= l_batch_size then
1426:
1427: Insert into pji_prg_batch
1428: ( batch_name,
1429: wbs_total,
1430: prg_total,
1431: delta_total,
1496: l_cnt := l_cnt + 1;
1497:
1498: if l_cnt >= l_batch_size then
1499:
1500: Insert into pji_prg_batch
1501: ( batch_name,
1502: wbs_total,
1503: prg_total,
1504: delta_total,
1520: end if; /* Added for bug 8416116 */
1521:
1522: if l_cnt >= 0 then
1523:
1524: Insert into pji_prg_batch
1525: ( batch_name,
1526: wbs_total,
1527: prg_total,
1528: delta_total,