[Home] [Help]
3157:
3158: BEGIN
3159: SELECT 1
3160: INTO lv_rec_count_cal
3161: FROM dual
3162: WHERE EXISTS(SELECT 1
3163: FROM msc_st_calendars
3164: WHERE sr_instance_id = v_instance_id
3165: AND process_flag = G_VALID);
3171:
3172: BEGIN
3173: SELECT 1
3174: INTO lv_rec_count_mtp
3175: FROM dual
3176: WHERE EXISTS(SELECT 1
3177: FROM msc_st_trading_partners
3178: WHERE sr_instance_id = v_instance_id
3179: AND process_flag = G_VALID);
3966:
3967: v_sql_stmt := 1;
3968: SELECT msc_st_calendars_s.NEXTVAL
3969: INTO lv_transaction_id
3970: FROM dual;
3971:
3972: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,'Refresh ID: '||v_refresh_id);
3973:
3974: --Update the transaction_id and the who columns
4316:
4317: v_sql_stmt := 8;
4318: SELECT msc_st_workday_patterns_s.NEXTVAL
4319: INTO lv_transaction_id
4320: FROM dual;
4321:
4322: v_sql_stmt := 9;
4323: UPDATE msc_st_workday_patterns
4324: SET st_transaction_id = lv_transaction_id,
4453: lv_shift_num := rec2.shift_name;
4454: ELSE
4455: SELECT msc_st_shift_num_s.NEXTVAL
4456: INTO lv_shift_num
4457: FROM dual;
4458: END IF;
4459:
4460: lv_return := derive_shift_details
4461: (p_transaction_id => lv_transaction_id,
4490:
4491: v_sql_stmt := 13;
4492: SELECT msc_st_shift_times_s.NEXTVAL
4493: INTO lv_transaction_id
4494: FROM dual;
4495:
4496: v_sql_stmt := 14;
4497: UPDATE msc_st_shift_times
4498: SET st_transaction_id = lv_transaction_id,
4567: BEGIN
4568: v_sql_stmt := 17; /*Bug 2172537-Modified the satement*/
4569: lv_sql_stmt :=
4570: 'SELECT 1'
4571: ||' FROM DUAL'
4572: ||' WHERE NOT EXISTS(SELECT 1'
4573: ||' FROM msc_st_shift_times'
4574: ||' WHERE sr_instance_code = :sr_instance_code'
4575: ||' AND process_flag = '||G_IN_PROCESS
4732:
4733: v_sql_stmt := 18;
4734: SELECT msc_st_calendar_exceptions_s.NEXTVAL
4735: INTO lv_transaction_id
4736: FROM dual;
4737:
4738: v_sql_stmt := 19;
4739: UPDATE msc_st_calendar_exceptions
4740: SET st_transaction_id = lv_transaction_id,
4974:
4975: v_sql_stmt := 22;
4976: SELECT msc_st_shift_exceptions_s.NEXTVAL
4977: INTO lv_transaction_id
4978: FROM dual;
4979:
4980: v_sql_stmt := 23;
4981: UPDATE msc_st_shift_exceptions
4982: SET st_transaction_id = lv_transaction_id,
5817: LOOP
5818: v_sql_stmt := 03;
5819: SELECT msc_st_batch_id_s.NEXTVAL
5820: INTO lv_batch_id
5821: FROM DUAL;
5822:
5823: v_sql_stmt := 04;
5824: lv_sql_stmt :=
5825: 'UPDATE msc_st_demand_classes '
6624: LOOP
6625: v_sql_stmt := 05;
6626: SELECT msc_st_batch_id_s.NEXTVAL
6627: INTO lv_batch_id
6628: FROM DUAL;
6629:
6630: v_sql_stmt := 06;
6631: lv_sql_stmt :=
6632: 'UPDATE msc_st_trading_partners '
7765: LOOP
7766: v_sql_stmt := 21;
7767: SELECT msc_st_batch_id_s.NEXTVAL
7768: INTO lv_batch_id
7769: FROM DUAL;
7770:
7771: v_sql_stmt := 22;
7772: lv_sql_stmt :=
7773: 'UPDATE msc_st_trading_partner_sites '
8512: LOOP
8513: v_sql_stmt := 29;
8514: SELECT msc_st_batch_id_s.NEXTVAL
8515: INTO lv_batch_id
8516: FROM DUAL;
8517:
8518: v_sql_stmt := 30;
8519: lv_sql_stmt :=
8520: 'UPDATE msc_st_sub_inventories '
8856: LOOP
8857: v_sql_stmt := 36;
8858: SELECT msc_st_batch_id_s.NEXTVAL
8859: INTO lv_batch_id
8860: FROM DUAL;
8861:
8862: v_sql_stmt := 37;
8863: lv_sql_stmt :=
8864: 'UPDATE msc_st_location_associations '
9459: LOOP
9460: v_sql_stmt := 47;
9461: SELECT msc_st_batch_id_s.NEXTVAL
9462: INTO lv_batch_id
9463: FROM DUAL;
9464:
9465: v_sql_stmt := 48;
9466: lv_sql_stmt :=
9467: 'UPDATE msc_st_partner_contacts '
9869: LOOP
9870: v_sql_stmt := 54;
9871: SELECT msc_st_batch_id_s.NEXTVAL
9872: INTO lv_batch_id
9873: FROM dual;
9874:
9875: v_sql_stmt := 55;
9876: lv_sql_stmt :=
9877: ' UPDATE MSC_ST_GROUPS '
10156: IF lv_return <> 0 THEN
10157: RAISE ex_logging_err;
10158: END IF;
10159:
10160: select fnd_global.user_id into lv_user_id from dual;
10161:
10162: IF NOT MSC_ST_util.IS_OPERATOR(lv_user_id) THEN
10163: lv_return := MSC_ST_util.GET_ERROR_MESSAGE
10164: (p_app_short_name => 'MSC',
10535: LOOP
10536: v_sql_stmt := 70;
10537: SELECT msc_st_batch_id_s.NEXTVAL
10538: INTO lv_batch_id
10539: FROM DUAL;
10540:
10541: v_sql_stmt := 71;
10542: lv_sql_stmt :=
10543: 'UPDATE msc_st_group_companies '
10736:
10737:
10738: -- Only group owner or operator can update
10739:
10740: select fnd_global.user_id into lv_user_id from dual;
10741:
10742: lv_return := MSC_ST_util.GET_ERROR_MESSAGE
10743: (p_app_short_name => 'MSC',
10744: p_error_code => 'MSC_PP_INVALID_VALUE',
11244: LOOP
11245: v_sql_stmt := 03;
11246: SELECT msc_st_batch_id_s.NEXTVAL
11247: INTO lv_batch_id
11248: FROM dual;
11249:
11250: v_sql_stmt := 04;
11251: lv_sql_stmt :=
11252: 'UPDATE msc_st_calendar_assignments '
12074: LOOP
12075: v_sql_stmt := 03;
12076: SELECT msc_st_batch_id_s.NEXTVAL
12077: INTO lv_batch_id
12078: FROM dual;
12079:
12080: v_sql_stmt := 04;
12081: lv_sql_stmt :=
12082: 'UPDATE msc_st_planners '
12730: LOOP
12731: v_sql_stmt := 03;
12732: SELECT msc_st_batch_id_s.NEXTVAL
12733: INTO lv_batch_id
12734: FROM DUAL;
12735:
12736: v_sql_stmt := 04;
12737: lv_sql_stmt :=
12738: 'UPDATE msc_st_interorg_ship_methods '
13709: LOOP
13710: v_sql_stmt := 03;
13711: lv_cursor_stmt :=
13712: ' SELECT msc_st_batch_id_s.NEXTVAL '
13713: ||' FROM dual';
13714:
13715: OPEN c1 FOR lv_cursor_stmt;
13716: FETCH c1 INTO lv_batch_id;
13717: CLOSE c1;
14320: LOOP
14321:
14322: SELECT msc_st_batch_id_s.NEXTVAL
14323: INTO lv_batch_id
14324: FROM DUAL;
14325:
14326: lv_sql_stmt :=
14327: 'UPDATE msc_st_zone_regions '
14328: ||' SET batch_id = :lv_batch_id'
14902: LOOP
14903: v_sql_stmt := 03;
14904: lv_cursor_stmt :=
14905: ' SELECT msc_st_batch_id_s.NEXTVAL '
14906: ||' FROM dual';
14907:
14908: OPEN c1 FOR lv_cursor_stmt;
14909: FETCH c1 INTO lv_batch_id;
14910: CLOSE c1;
15406: LOOP
15407:
15408: lv_cursor_stmt :=
15409: ' SELECT msc_st_batch_id_s.NEXTVAL '
15410: ||' FROM dual';
15411:
15412: OPEN c1 FOR lv_cursor_stmt;
15413: FETCH c1 INTO lv_batch_id;
15414: CLOSE c1;
15836: LOOP
15837: v_sql_stmt := 03;
15838: SELECT msc_st_batch_id_s.NEXTVAL
15839: INTO lv_batch_id
15840: FROM dual;
15841:
15842: v_sql_stmt := 04;
15843: lv_sql_stmt :=
15844: ' UPDATE msc_st_category_sets '
16286: LOOP
16287: v_sql_stmt := 03;
16288: SELECT msc_st_batch_id_s.NEXTVAL
16289: INTO lv_batch_id
16290: FROM dual;
16291:
16292: v_sql_stmt := 04;
16293: lv_sql_stmt :=
16294: 'UPDATE msc_st_units_of_measure '
16796: LOOP
16797: v_sql_stmt := 12;
16798: SELECT msc_st_batch_id_s.NEXTVAL
16799: INTO lv_batch_id
16800: FROM dual;
16801:
16802: v_sql_stmt := 13;
16803: lv_sql_stmt :=
16804: 'UPDATE msc_st_uom_class_conversions '
17366: LOOP
17367: v_sql_stmt := 25;
17368: SELECT msc_st_batch_id_s.NEXTVAL
17369: INTO lv_batch_id
17370: FROM dual;
17371:
17372: v_sql_stmt := 26;
17373: lv_sql_stmt :=
17374: 'UPDATE msc_st_uom_conversions '
17955: v_sql_stmt := 03;
17956:
17957: SELECT msc_st_batch_id_s.NEXTVAL
17958: INTO lv_batch_id
17959: FROM dual;
17960:
17961: v_sql_stmt := 04;
17962: lv_sql_stmt :=
17963: 'UPDATE msc_st_designators '
20913: LOOP
20914: v_sql_stmt := 03;
20915: SELECT msc_st_batch_id_s.NEXTVAL
20916: INTO lv_batch_id
20917: FROM dual;
20918:
20919: v_sql_stmt := 04;
20920: lv_sql_stmt :=
20921: ' UPDATE msc_st_item_substitutes '
21614: LOOP
21615: v_sql_stmt := 03;
21616: SELECT msc_st_batch_id_s.NEXTVAL
21617: INTO lv_batch_id
21618: FROM dual;
21619:
21620: v_sql_stmt := 04;
21621: lv_sql_stmt :=
21622: 'UPDATE msc_st_item_suppliers '
22249: LOOP
22250: v_sql_stmt := 11;
22251: SELECT msc_st_batch_id_s.NEXTVAL
22252: INTO lv_batch_id
22253: FROM dual;
22254:
22255: v_sql_stmt := 12;
22256: lv_sql_stmt :=
22257: 'UPDATE msc_st_supplier_capacities '
22699: LOOP
22700: v_sql_stmt := 19;
22701: SELECT msc_st_batch_id_s.NEXTVAL
22702: INTO lv_batch_id
22703: FROM dual;
22704:
22705: v_sql_stmt := 20;
22706: lv_sql_stmt :=
22707: 'UPDATE msc_st_supplier_flex_fences '
23206: LOOP
23207: v_sql_stmt := 03;
23208: SELECT msc_st_batch_id_s.NEXTVAL
23209: INTO lv_batch_id
23210: FROM dual;
23211:
23212: v_sql_stmt := 04;
23213: lv_sql_stmt :=
23214: ' UPDATE msc_st_safety_stocks '
23712: LOOP
23713: v_sql_stmt := 03;
23714: SELECT msc_st_batch_id_s.NEXTVAL
23715: INTO lv_batch_id
23716: FROM dual;
23717:
23718: v_sql_stmt := 04;
23719: lv_sql_stmt :=
23720: 'UPDATE msc_st_item_categories '
24424: LOOP
24425: v_sql_stmt := 03;
24426: lv_cursor_stmt :=
24427: ' SELECT msc_st_batch_id_s.NEXTVAL '
24428: ||' FROM dual';
24429:
24430: OPEN c1 FOR lv_cursor_stmt;
24431: FETCH c1 INTO lv_batch_id;
24432: CLOSE c1;
26280: LOOP
26281: v_sql_stmt := 03;
26282: lv_cursor_stmt :=
26283: ' SELECT msc_st_batch_id_s.NEXTVAL '
26284: ||' FROM dual';
26285:
26286: OPEN c1 FOR lv_cursor_stmt;
26287: FETCH c1 INTO lv_batch_id;
26288: CLOSE c1;
27126: LOOP
27127: v_sql_stmt := 03;
27128: SELECT msc_st_batch_id_s.NEXTVAL
27129: INTO lv_batch_id
27130: FROM dual;
27131:
27132: v_sql_stmt := 04;
27133: lv_sql_stmt :=
27134: ' UPDATE msc_st_resource_groups '
27533: LOOP
27534: v_sql_stmt := 03;
27535: lv_cursor_stmt :=
27536: ' SELECT msc_st_batch_id_s.NEXTVAL '
27537: ||' FROM dual';
27538:
27539: OPEN c1 FOR lv_cursor_stmt;
27540: FETCH c1 INTO lv_batch_id;
27541: CLOSE c1;
28557: LOOP
28558: v_sql_stmt := 21;
28559: lv_cursor_stmt :=
28560: ' SELECT msc_st_batch_id_s.NEXTVAL '
28561: ||' FROM dual';
28562:
28563: OPEN c1 FOR lv_cursor_stmt;
28564: FETCH c1 INTO lv_batch_id;
28565: CLOSE c1;
29084: LOOP
29085: v_sql_stmt := 33;
29086: lv_cursor_stmt :=
29087: ' SELECT msc_st_batch_id_s.NEXTVAL '
29088: ||' FROM dual';
29089:
29090: OPEN c1 FOR lv_cursor_stmt;
29091: FETCH c1 INTO lv_batch_id;
29092: CLOSE c1;
32978: LOOP
32979: v_sql_stmt := 03;
32980: SELECT msc_st_batch_id_s.NEXTVAL
32981: INTO lv_batch_id
32982: FROM DUAL;
32983:
32984: v_sql_stmt := 04;
32985: lv_sql_stmt :=
32986: 'UPDATE msc_st_reservations '
35047: v_sql_stmt := 01;
35048:
35049: SELECT msc_st_batch_id_s.NEXTVAL
35050: INTO lv_batch_id
35051: FROM DUAL;
35052:
35053: v_sql_stmt := 02;
35054: lv_sql_stmt :=
35055: ' UPDATE MSC_ST_COMPANY_USERS '
35813: LOOP
35814: v_sql_stmt := 03;
35815: lv_cursor_stmt :=
35816: ' SELECT msc_st_batch_id_s.NEXTVAL '
35817: ||' FROM dual';
35818:
35819: OPEN c1 FOR lv_cursor_stmt;
35820: FETCH c1 INTO lv_batch_id;
35821: CLOSE c1;
36422: LOOP
36423: v_sql_stmt := 13;
36424: lv_cursor_stmt :=
36425: ' SELECT msc_st_batch_id_s.NEXTVAL '
36426: ||' FROM dual';
36427:
36428: OPEN c1 FOR lv_cursor_stmt;
36429: FETCH c1 INTO lv_batch_id;
36430: CLOSE c1;
37561: LOOP
37562: v_sql_stmt := 28;
37563: lv_cursor_stmt :=
37564: ' SELECT msc_st_batch_id_s.NEXTVAL '
37565: ||' FROM dual';
37566:
37567: OPEN c1 FOR lv_cursor_stmt;
37568: FETCH c1 INTO lv_batch_id;
37569: CLOSE c1;
38153: LOOP
38154: v_sql_stmt := 38;
38155: lv_cursor_stmt :=
38156: ' SELECT msc_st_batch_id_s.NEXTVAL '
38157: ||' FROM dual';
38158: OPEN c1 FOR lv_cursor_stmt;
38159: FETCH c1 INTO lv_batch_id;
38160: CLOSE c1;
38161:
38893: LOOP
38894: v_sql_stmt := 03;
38895: lv_cursor_stmt :=
38896: ' SELECT msc_st_batch_id_s.NEXTVAL '
38897: ||' FROM dual';
38898:
38899: OPEN c1 FOR lv_cursor_stmt;
38900: FETCH c1 INTO lv_batch_id;
38901: CLOSE c1;
39759:
39760: v_sql_stmt := 20;
39761: lv_cursor_stmt :=
39762: ' SELECT msc_st_batch_id_s.NEXTVAL '
39763: ||' FROM dual';
39764:
39765: OPEN c1 FOR lv_cursor_stmt;
39766: FETCH c1 INTO lv_batch_id;
39767: CLOSE c1;
39768:
39769:
39770: SELECT msc_st_batch_id_s.NEXTVAL
39771: INTO lv_batch_id
39772: FROM dual;
39773:
39774: v_sql_stmt := 21;
39775: lv_sql_stmt :=
39776: ' UPDATE msc_st_routing_operations '
40623:
40624: LOOP
40625: lv_cursor_stmt :=
40626: ' SELECT msc_st_batch_id_s.NEXTVAL '
40627: ||' FROM dual';
40628:
40629: OPEN c1 FOR lv_cursor_stmt;
40630: FETCH c1 INTO lv_batch_id;
40631: CLOSE c1;
41526: v_sql_stmt := 53;
41527:
41528: lv_cursor_stmt :=
41529: ' SELECT msc_st_batch_id_s.NEXTVAL '
41530: ||' FROM dual';
41531:
41532: OPEN c1 FOR lv_cursor_stmt;
41533: FETCH c1 INTO lv_batch_id;
41534: CLOSE c1;
42584: LOOP
42585: v_sql_stmt := 03;
42586: SELECT msc_st_batch_id_s.NEXTVAL
42587: INTO lv_batch_id
42588: FROM dual;
42589:
42590: v_sql_stmt := 04;
42591: lv_sql_stmt :=
42592: ' UPDATE msc_st_supplies '
43667: LOOP
43668: v_sql_stmt := 03;
43669: lv_cursor_stmt :=
43670: ' SELECT msc_st_batch_id_s.NEXTVAL '
43671: ||' FROM dual';
43672:
43673: OPEN c1 FOR lv_cursor_stmt;
43674: FETCH c1 INTO lv_batch_id;
43675: CLOSE c1;
44923: LOOP
44924: v_sql_stmt := 03;
44925: SELECT msc_st_batch_id_s.NEXTVAL
44926: INTO lv_batch_id
44927: FROM DUAL;
44928:
44929: v_sql_stmt := 04;
44930: lv_sql_stmt :=
44931: ' UPDATE msc_st_demands '
45694: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
45695:
45696: -- SELECT msc_st_operation_sequence_id_s.NEXTVAL
45697: -- INTO lv_local_id
45698: -- FROM DUAL;
45699:
45700: UPDATE msc_st_demands
45701: SET operation_seq_num =
45702: to_number(decode(length(rtrim(operation_seq_code,'0123456789')),
46017: v_sql_stmt := 3;
46018:
46019: lv_cursor_stmt :=
46020: ' SELECT msc_st_batch_id_s.NEXTVAL '
46021: ||' FROM dual';
46022:
46023: OPEN c1 FOR lv_cursor_stmt;
46024: FETCH c1 INTO lv_batch_id;
46025: CLOSE c1;
46940: LOOP
46941:
46942: lv_cursor_stmt :=
46943: ' SELECT msc_st_batch_id_s.NEXTVAL '
46944: ||' FROM dual';
46945:
46946: OPEN c1 FOR lv_cursor_stmt;
46947: FETCH c1 INTO lv_batch_id;
46948: CLOSE c1;
47595: LOOP
47596: v_sql_stmt := 03;
47597: SELECT msc_st_batch_id_s.NEXTVAL
47598: INTO lv_batch_id
47599: FROM DUAL;
47600:
47601: v_sql_stmt := 04;
47602: lv_sql_stmt :=
47603: ' UPDATE msc_st_job_requirement_ops '
48421: LOOP
48422: v_sql_stmt := 03;
48423: lv_cursor_stmt :=
48424: ' SELECT msc_st_batch_id_s.NEXTVAL '
48425: ||' FROM dual';
48426:
48427: OPEN c1 FOR lv_cursor_stmt;
48428: FETCH c1 INTO lv_batch_id;
48429: CLOSE c1;
50145: v_sql_stmt := 05;
50146:
50147: SELECT msc_st_batch_id_s.NEXTVAL
50148: INTO lv_batch_id
50149: FROM dual;
50150:
50151: v_sql_stmt := 06;
50152: lv_sql_stmt :=
50153: 'UPDATE msc_st_item_customers '
50760: BEGIN
50761: LOOP
50762: SELECT msc_st_batch_id_s.NEXTVAL
50763: INTO lv_batch_id
50764: FROM DUAL;
50765:
50766: v_sql_stmt := 01;
50767: lv_sql_stmt :=
50768: 'UPDATE '||p_table_name
52449:
52450:
52451: SELECT msc_st_apps_instances_s.NEXTVAL
52452: INTO lv_transaction_id
52453: FROM dual;
52454:
52455:
52456: MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'Refresh ID: '||v_refresh_id);
52457:
52651: WHERE INSTANCE_ID= p_instance_id;
52652:
52653: SELECT MSC_COLLECTION_S.CURRVAL
52654: INTO v_last_collection_id
52655: FROM DUAL;
52656:
52657: RETURN TRUE;
52658:
52659: END IF;
52818: EXIT WHEN is_request_status_running <> SYS_YES;
52819:
52820: -- ============= Check the execution time ==============
52821:
52822: select (SYSDATE- START_TIME) into lv_process_time from dual;
52823: IF lv_process_time > p_timeout/1440.0 THEN Raise EX_PROCESS_TIME_OUT;
52824: END IF;
52825:
52826: -- ============= Get the Task from Task Que ==============
53627: FETCH c2 BULK COLLECT INTO lb_rowid ;
53628:
53629: IF c2%ROWCOUNT > 0 THEN
53630:
53631: SELECT msc_st_batch_id_s.NEXTVAL INTO lv_batch_id FROM dual;
53632: lv_batch_start := lv_batch_id;
53633: lb_batch.extend(lb_rowid.count);
53634:
53635: FOR j IN lb_rowid.FIRST..lb_rowid.LAST LOOP
53635: FOR j IN lb_rowid.FIRST..lb_rowid.LAST LOOP
53636:
53637: lb_batch(j):= lv_batch_id;
53638: IF MOD(j,v_batch_size) = 0 THEN
53639: SELECT msc_st_batch_id_s.NEXTVAL INTO lv_batch_id FROM dual;
53640: END IF;
53641:
53642: END LOOP;
53643:
53867: FETCH c2 BULK COLLECT INTO lb_rowid ;
53868:
53869: IF c2%ROWCOUNT > 0 THEN
53870:
53871: SELECT msc_st_batch_id_s.NEXTVAL INTO lv_batch_id FROM dual;
53872: lv_batch_start := lv_batch_id;
53873: lb_batch.extend(lb_rowid.count);
53874:
53875: FOR j IN lb_rowid.FIRST..lb_rowid.LAST LOOP
53875: FOR j IN lb_rowid.FIRST..lb_rowid.LAST LOOP
53876:
53877: lb_batch(j):= lv_batch_id;
53878: IF MOD(j,v_batch_size) = 0 THEN
53879: SELECT msc_st_batch_id_s.NEXTVAL INTO lv_batch_id FROM dual;
53880: END IF;
53881:
53882: END LOOP;
53883:
53942: FETCH c2 BULK COLLECT INTO lb_rowid ;
53943:
53944: IF c2%ROWCOUNT > 0 THEN
53945:
53946: SELECT msc_st_batch_id_s.NEXTVAL INTO lv_batch_id FROM dual;
53947: lv_batch_start := lv_batch_id;
53948: lb_batch.extend(lb_rowid.count);
53949:
53950: FOR j IN lb_rowid.FIRST..lb_rowid.LAST LOOP
53950: FOR j IN lb_rowid.FIRST..lb_rowid.LAST LOOP
53951:
53952: lb_batch(j):= lv_batch_id;
53953: IF MOD(j,v_batch_size) = 0 THEN
53954: SELECT msc_st_batch_id_s.NEXTVAL INTO lv_batch_id FROM dual;
53955: END IF;
53956:
53957: END LOOP;
53958:
54024: FETCH c2 BULK COLLECT INTO lb_rowid ;
54025:
54026: IF c2%ROWCOUNT > 0 THEN
54027:
54028: SELECT msc_st_batch_id_s.NEXTVAL INTO lv_batch_id FROM dual;
54029: lv_batch_start := lv_batch_id;
54030: lb_batch.extend(lb_rowid.count);
54031:
54032: FOR j IN lb_rowid.FIRST..lb_rowid.LAST LOOP
54032: FOR j IN lb_rowid.FIRST..lb_rowid.LAST LOOP
54033:
54034: lb_batch(j):= lv_batch_id;
54035: IF MOD(j,v_batch_size) = 0 THEN
54036: SELECT msc_st_batch_id_s.NEXTVAL INTO lv_batch_id FROM dual;
54037: END IF;
54038:
54039: END LOOP;
54040:
54192: FETCH c2 BULK COLLECT INTO lb_rowid ;
54193:
54194: IF c2%ROWCOUNT > 0 THEN
54195:
54196: SELECT msc_st_batch_id_s.NEXTVAL INTO lv_batch_id FROM dual;
54197: lv_batch_start := lv_batch_id;
54198: lb_batch.extend(lb_rowid.count);
54199:
54200: FOR j IN lb_rowid.FIRST..lb_rowid.LAST LOOP
54200: FOR j IN lb_rowid.FIRST..lb_rowid.LAST LOOP
54201:
54202: lb_batch(j):= lv_batch_id;
54203: IF MOD(j,v_batch_size) = 0 THEN
54204: SELECT msc_st_batch_id_s.NEXTVAL INTO lv_batch_id FROM dual;
54205: END IF;
54206:
54207: END LOOP;
54208:
54407: END IF;
54408:
54409: -- ============= Check the execution time ==============
54410:
54411: select (SYSDATE- START_TIME) into lv_process_time from dual;
54412:
54413: IF lv_process_time > p_timeout/1440.0 THEN Raise EX_PROCESS_TIME_OUT; END IF;
54414:
54415: END LOOP;
54485: RAISE EX_PIPE_RCV; -- If the error is not time-out error
54486: END IF;
54487:
54488: -- WHY ?
54489: -- SELECT (SYSDATE- lv_start_time) INTO lv_process_time FROM dual;
54490: -- EXIT WHEN lv_process_time > 10.0/1440.0; -- wait for 10 minutes
54491:
54492: END LOOP;
54493: