[Home] [Help]
32: std_op_res.SUBSTITUTE_GROUP_NUM,
33: std_op_res.UOM_CODE,
34: std_op_res.SCHEDULE_FLAG
35: FROM MSC_ST_STD_OP_RESOURCES std_op_res
36: WHERE std_op_res.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
37:
38: lv_cnt NUMBER;
39: lv_pbs NUMBER;
40: c_count NUMBER := 0;
41: total_count NUMBER := 0;
42:
43: BEGIN
44:
45: IF ((MSC_CL_COLLECTION.v_is_complete_refresh) OR (MSC_CL_COLLECTION.v_is_partial_refresh)) THEN
46: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
47: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_STD_OP_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1);
48: ELSE
49: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_STD_OP_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str); /* ds change change */
42:
43: BEGIN
44:
45: IF ((MSC_CL_COLLECTION.v_is_complete_refresh) OR (MSC_CL_COLLECTION.v_is_partial_refresh)) THEN
46: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
47: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_STD_OP_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1);
48: ELSE
49: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_STD_OP_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str); /* ds change change */
50: END IF;
43: BEGIN
44:
45: IF ((MSC_CL_COLLECTION.v_is_complete_refresh) OR (MSC_CL_COLLECTION.v_is_partial_refresh)) THEN
46: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
47: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_STD_OP_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1);
48: ELSE
49: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_STD_OP_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str); /* ds change change */
50: END IF;
51:
45: IF ((MSC_CL_COLLECTION.v_is_complete_refresh) OR (MSC_CL_COLLECTION.v_is_partial_refresh)) THEN
46: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
47: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_STD_OP_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1);
48: ELSE
49: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_STD_OP_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str); /* ds change change */
50: END IF;
51:
52: c_count := 0;
53: total_count := 0;
88: c_rec.RESOURCE_UNITS,
89: c_rec.SUBSTITUTE_GROUP_NUM,
90: c_rec.UOM_CODE,
91: c_rec.SCHEDULE_FLAG,
92: MSC_CL_COLLECTION.v_last_collection_id,
93: MSC_CL_COLLECTION.v_current_date,
94: MSC_CL_COLLECTION.v_current_user,
95: MSC_CL_COLLECTION.v_current_date,
96: MSC_CL_COLLECTION.v_current_user );
89: c_rec.SUBSTITUTE_GROUP_NUM,
90: c_rec.UOM_CODE,
91: c_rec.SCHEDULE_FLAG,
92: MSC_CL_COLLECTION.v_last_collection_id,
93: MSC_CL_COLLECTION.v_current_date,
94: MSC_CL_COLLECTION.v_current_user,
95: MSC_CL_COLLECTION.v_current_date,
96: MSC_CL_COLLECTION.v_current_user );
97:
90: c_rec.UOM_CODE,
91: c_rec.SCHEDULE_FLAG,
92: MSC_CL_COLLECTION.v_last_collection_id,
93: MSC_CL_COLLECTION.v_current_date,
94: MSC_CL_COLLECTION.v_current_user,
95: MSC_CL_COLLECTION.v_current_date,
96: MSC_CL_COLLECTION.v_current_user );
97:
98: c_count:= c_count+1;
91: c_rec.SCHEDULE_FLAG,
92: MSC_CL_COLLECTION.v_last_collection_id,
93: MSC_CL_COLLECTION.v_current_date,
94: MSC_CL_COLLECTION.v_current_user,
95: MSC_CL_COLLECTION.v_current_date,
96: MSC_CL_COLLECTION.v_current_user );
97:
98: c_count:= c_count+1;
99: total_count := total_count+1;
92: MSC_CL_COLLECTION.v_last_collection_id,
93: MSC_CL_COLLECTION.v_current_date,
94: MSC_CL_COLLECTION.v_current_user,
95: MSC_CL_COLLECTION.v_current_date,
96: MSC_CL_COLLECTION.v_current_user );
97:
98: c_count:= c_count+1;
99: total_count := total_count+1;
100:
97:
98: c_count:= c_count+1;
99: total_count := total_count+1;
100:
101: IF c_count> MSC_CL_COLLECTION.PBS THEN
102: COMMIT;
103: c_count:= 0;
104: END IF;
105:
117:
118: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
119: RAISE;
120: ELSE
121: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
122:
123: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
124: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
125: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_STD_OP_RESOURCES');
144:
145: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
146: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_ID');
147: FND_MESSAGE.SET_TOKEN('VALUE', MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
148: MSC_CL_COLLECTION.v_instance_id));
149: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
150: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
151: END IF;
152:
155: END LOOP;
156: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Total std op resources = '|| to_char(total_count));
157: COMMIT;
158:
159: END IF; /* MSC_CL_COLLECTION.v_is_complete_refresh) OR (MSC_CL_COLLECTION.v_is_partial_refresh */
160:
161: END LOAD_STD_OP_RESOURCES;
162:
163:
213: mon.REFRESH_ID,
214: mon.SR_INSTANCE_ID
215: from MSC_ST_OPERATION_NETWORKS mon,
216: MSC_ITEM_ID_LID t1 /* ds change change */
217: WHERE mon.SR_INSTANCE_ID = MSC_CL_COLLECTION.v_instance_id
218: AND mon.DELETED_FLAG = MSC_UTIL.SYS_NO
219: AND mon.FROM_ITEM_ID = t1.SR_INVENTORY_ITEM_ID(+) /* ds change change */
220: AND mon.sr_instance_id = t1.sr_instance_id(+) ; /* ds change change */
221:
227: mon.DELETED_FLAG,
228: mon.REFRESH_ID,
229: mon.SR_INSTANCE_ID
230: FROM MSC_ST_OPERATION_NETWORKS mon
231: WHERE mon.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
232: and mon.DELETED_FLAG= MSC_UTIL.SYS_YES;
233:
234: c_count NUMBER:= 0;
235: lv_tbl VARCHAR2(30);
236: lv_sql_stmt VARCHAR2(5000);
237:
238: BEGIN
239:
240: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
241:
242: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
243: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_NETWORKS', MSC_CL_COLLECTION.v_instance_id, -1);
244: ELSE
238: BEGIN
239:
240: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
241:
242: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
243: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_NETWORKS', MSC_CL_COLLECTION.v_instance_id, -1);
244: ELSE
245: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
246: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_NETWORKS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
239:
240: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
241:
242: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
243: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_NETWORKS', MSC_CL_COLLECTION.v_instance_id, -1);
244: ELSE
245: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
246: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_NETWORKS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
247: END IF;
242: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
243: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_NETWORKS', MSC_CL_COLLECTION.v_instance_id, -1);
244: ELSE
245: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
246: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_NETWORKS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
247: END IF;
248:
249: END IF; -- MSC_CL_COLLECTION.v_is_complete_refresh
250:
245: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
246: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_NETWORKS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
247: END IF;
248:
249: END IF; -- MSC_CL_COLLECTION.v_is_complete_refresh
250:
251: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
252:
253: -- set SR_INSTANCE_ID to negative to indicate a SOFT delete
247: END IF;
248:
249: END IF; -- MSC_CL_COLLECTION.v_is_complete_refresh
250:
251: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
252:
253: -- set SR_INSTANCE_ID to negative to indicate a SOFT delete
254:
255: FOR c_rec IN c10_d LOOP
270: FOR c_rec IN c10 LOOP
271:
272: BEGIN
273:
274: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
275:
276: UPDATE MSC_OPERATION_NETWORKS
277: SET
278: FROM_OP_SEQ_ID = c_rec.from_op_seq_id,
283: CUMMULATIVE_PCT = c_rec. cummulative_pct,
284: EFFECTIVITY_DATE = c_Rec.efectivity_date,
285: DISABLE_DATE = c_Rec.disable_date,
286: PLAN_ID = -1,
287: LAST_UPDATED_BY = MSC_CL_COLLECTION.v_current_user,
288: LAST_UPDATE_DATE = MSC_CL_COLLECTION.v_current_date,
289: ATTRIBUTE_CATEGORY = c_rec.attribute_category,
290: ATTRIBUTE1 = c_rec.attribute1,
291: ATTRIBUTE2 = c_rec.attribute2,
284: EFFECTIVITY_DATE = c_Rec.efectivity_date,
285: DISABLE_DATE = c_Rec.disable_date,
286: PLAN_ID = -1,
287: LAST_UPDATED_BY = MSC_CL_COLLECTION.v_current_user,
288: LAST_UPDATE_DATE = MSC_CL_COLLECTION.v_current_date,
289: ATTRIBUTE_CATEGORY = c_rec.attribute_category,
290: ATTRIBUTE1 = c_rec.attribute1,
291: ATTRIBUTE2 = c_rec.attribute2,
292: ATTRIBUTE3 = c_Rec.attribute3,
304: ATTRIBUTE15 = c_rec.attribute15,
305: FROM_OP_SEQ_NUM= c_rec.FROM_OP_SEQ_NUM,
306: TO_OP_SEQ_NUM = c_rec.TO_OP_SEQ_NUM,
307: DEPENDENCY_TYPE = c_rec.DEPENDENCY_TYPE, /* ds change other new attr not added as they are for opm */
308: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id
309: WHERE PLAN_ID= -1
310: AND FROM_OP_SEQ_ID= c_rec.FROM_OP_SEQ_ID
311: AND TO_OP_SEQ_ID= c_rec.TO_OP_SEQ_ID
312: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
312: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
313:
314: END IF;
315:
316: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
317:
318: insert into MSC_OPERATION_NETWORKS
319: ( FROM_OP_SEQ_ID,
320: TO_OP_SEQ_ID,
379: c_rec.APPLY_TO_CHARGES,
380: c_rec.TRANSFER_PCT,
381: c_rec.TRANSFER_QTY,
382: c_rec.TRANSFER_UOM, /*ds change change end */
383: MSC_CL_COLLECTION.v_current_user,
384: MSC_CL_COLLECTION.v_current_date,
385: MSC_CL_COLLECTION.v_current_user,
386: MSC_CL_COLLECTION.v_current_date,
387: c_rec.ATTRIBUTE_CATEGORY,
380: c_rec.TRANSFER_PCT,
381: c_rec.TRANSFER_QTY,
382: c_rec.TRANSFER_UOM, /*ds change change end */
383: MSC_CL_COLLECTION.v_current_user,
384: MSC_CL_COLLECTION.v_current_date,
385: MSC_CL_COLLECTION.v_current_user,
386: MSC_CL_COLLECTION.v_current_date,
387: c_rec.ATTRIBUTE_CATEGORY,
388: c_rec.ATTRIBUTE1,
381: c_rec.TRANSFER_QTY,
382: c_rec.TRANSFER_UOM, /*ds change change end */
383: MSC_CL_COLLECTION.v_current_user,
384: MSC_CL_COLLECTION.v_current_date,
385: MSC_CL_COLLECTION.v_current_user,
386: MSC_CL_COLLECTION.v_current_date,
387: c_rec.ATTRIBUTE_CATEGORY,
388: c_rec.ATTRIBUTE1,
389: c_rec.ATTRIBUTE2,
382: c_rec.TRANSFER_UOM, /*ds change change end */
383: MSC_CL_COLLECTION.v_current_user,
384: MSC_CL_COLLECTION.v_current_date,
385: MSC_CL_COLLECTION.v_current_user,
386: MSC_CL_COLLECTION.v_current_date,
387: c_rec.ATTRIBUTE_CATEGORY,
388: c_rec.ATTRIBUTE1,
389: c_rec.ATTRIBUTE2,
390: c_Rec.ATTRIBUTE3,
401: c_rec.ATTRIBUTE14,
402: c_rec.ATTRIBUTE15,
403: c_rec.FROM_OP_SEQ_NUM,
404: c_rec.TO_OP_SEQ_NUM,
405: MSC_CL_COLLECTION.v_last_collection_id,
406: MSC_CL_COLLECTION.v_instance_id );
407:
408:
409: END IF; -- SQL%NOTFOUND
402: c_rec.ATTRIBUTE15,
403: c_rec.FROM_OP_SEQ_NUM,
404: c_rec.TO_OP_SEQ_NUM,
405: MSC_CL_COLLECTION.v_last_collection_id,
406: MSC_CL_COLLECTION.v_instance_id );
407:
408:
409: END IF; -- SQL%NOTFOUND
410:
409: END IF; -- SQL%NOTFOUND
410:
411: c_count:= c_count+1;
412:
413: IF c_count> MSC_CL_COLLECTION.PBS THEN
414: COMMIT;
415: c_count:= 0;
416: END IF;
417:
429: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
430: RAISE;
431:
432: ELSE
433: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
434:
435: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
436: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
437: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_OPERATION_NETWORKS');
495: FROM MSC_ITEM_ID_LID t1,
496: MSC_ST_ROUTINGS msr
497: WHERE t1.SR_INVENTORY_ITEM_ID= msr.assembly_item_id
498: AND t1.sr_instance_id= msr.sr_instance_id
499: AND msr.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
500: AND msr.DELETED_FLAG= MSC_UTIL.SYS_NO;
501:
502: CURSOR c4_d IS
503: SELECT
503: SELECT
504: msr.ROUTING_SEQUENCE_ID,
505: msr.SR_INSTANCE_ID
506: FROM MSC_ST_ROUTINGS msr
507: WHERE msr.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
508: AND msr.DELETED_FLAG= MSC_UTIL.SYS_YES;
509:
510:
511: c_count NUMBER:= 0;
515: lb_refresh_failed BOOLEAN:= FALSE;
516:
517: BEGIN
518:
519: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
520: -- We want to delete all ROUTING related data and get new stuff.
521:
522: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ROUTINGS', MSC_CL_COLLECTION.v_instance_id, -1);
523:
518:
519: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
520: -- We want to delete all ROUTING related data and get new stuff.
521:
522: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ROUTINGS', MSC_CL_COLLECTION.v_instance_id, -1);
523:
524: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
525: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ROUTINGS', MSC_CL_COLLECTION.v_instance_id, -1);
526: ELSE
520: -- We want to delete all ROUTING related data and get new stuff.
521:
522: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ROUTINGS', MSC_CL_COLLECTION.v_instance_id, -1);
523:
524: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
525: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ROUTINGS', MSC_CL_COLLECTION.v_instance_id, -1);
526: ELSE
527: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
528: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ROUTINGS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
521:
522: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ROUTINGS', MSC_CL_COLLECTION.v_instance_id, -1);
523:
524: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
525: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ROUTINGS', MSC_CL_COLLECTION.v_instance_id, -1);
526: ELSE
527: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
528: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ROUTINGS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
529: END IF;
524: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
525: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ROUTINGS', MSC_CL_COLLECTION.v_instance_id, -1);
526: ELSE
527: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
528: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ROUTINGS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
529: END IF;
530:
531: BEGIN
532: lv_sql_ins:=
583: ||' FROM MSC_ITEM_ID_LID t1,'
584: ||' MSC_ST_ROUTINGS msr'
585: ||' WHERE t1.SR_INVENTORY_ITEM_ID= msr.assembly_item_id'
586: ||' AND t1.sr_instance_id= msr.sr_instance_id'
587: ||' AND msr.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id
588: ||' AND msr.DELETED_FLAG= '||MSC_UTIL.SYS_NO;
589:
590: EXECUTE IMMEDIATE lv_sql_ins
591: USING v_chr10, v_chr13, MSC_CL_COLLECTION.v_last_collection_id,MSC_CL_COLLECTION.v_current_date,MSC_CL_COLLECTION.v_current_user,MSC_CL_COLLECTION.v_current_date,MSC_CL_COLLECTION.v_current_user;
587: ||' AND msr.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id
588: ||' AND msr.DELETED_FLAG= '||MSC_UTIL.SYS_NO;
589:
590: EXECUTE IMMEDIATE lv_sql_ins
591: USING v_chr10, v_chr13, MSC_CL_COLLECTION.v_last_collection_id,MSC_CL_COLLECTION.v_current_date,MSC_CL_COLLECTION.v_current_user,MSC_CL_COLLECTION.v_current_date,MSC_CL_COLLECTION.v_current_user;
592:
593: COMMIT;
594: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'routings loaded');
595:
622: lb_refresh_failed := TRUE;
623: END IF;
624: END;
625:
626: END IF; -- MSC_CL_COLLECTION.v_is_complete_refresh
627:
628: IF MSC_CL_COLLECTION.v_is_incremental_refresh OR lb_refresh_failed THEN
629:
630: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
624: END;
625:
626: END IF; -- MSC_CL_COLLECTION.v_is_complete_refresh
627:
628: IF MSC_CL_COLLECTION.v_is_incremental_refresh OR lb_refresh_failed THEN
629:
630: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
631:
632: FOR c_rec IN c4_d LOOP
626: END IF; -- MSC_CL_COLLECTION.v_is_complete_refresh
627:
628: IF MSC_CL_COLLECTION.v_is_incremental_refresh OR lb_refresh_failed THEN
629:
630: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
631:
632: FOR c_rec IN c4_d LOOP
633:
634: -- set SR_INSTANCE_ID to negative to indicate a SOFT delete
648: FOR c_rec IN c4 LOOP
649:
650: BEGIN
651:
652: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
653:
654: UPDATE MSC_ROUTINGS
655: SET
656: ROUTING_TYPE= c_rec.ROUTING_TYPE,
667: CTP_FLAG= c_rec.CTP_FLAG,
668: ASSEMBLY_ITEM_ID= c_rec.ASSEMBLY_ITEM_ID,
669: ORGANIZATION_ID= c_rec.ORGANIZATION_ID,
670: ROUTING_QUANTITY= c_rec.ROUTING_QUANTITY,
671: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
672: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
673: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
674: WHERE PLAN_ID= -1
675: AND ROUTING_SEQUENCE_ID= c_rec.ROUTING_SEQUENCE_ID
668: ASSEMBLY_ITEM_ID= c_rec.ASSEMBLY_ITEM_ID,
669: ORGANIZATION_ID= c_rec.ORGANIZATION_ID,
670: ROUTING_QUANTITY= c_rec.ROUTING_QUANTITY,
671: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
672: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
673: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
674: WHERE PLAN_ID= -1
675: AND ROUTING_SEQUENCE_ID= c_rec.ROUTING_SEQUENCE_ID
676: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
669: ORGANIZATION_ID= c_rec.ORGANIZATION_ID,
670: ROUTING_QUANTITY= c_rec.ROUTING_QUANTITY,
671: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
672: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
673: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
674: WHERE PLAN_ID= -1
675: AND ROUTING_SEQUENCE_ID= c_rec.ROUTING_SEQUENCE_ID
676: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
677:
676: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
677:
678: END IF;
679:
680: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
681:
682: INSERT INTO MSC_ROUTINGS
683: ( PLAN_ID,
684: ROUTING_SEQUENCE_ID,
719: c_rec.ASSEMBLY_ITEM_ID,
720: c_rec.ORGANIZATION_ID,
721: c_rec.ROUTING_QUANTITY,
722: c_rec.SR_INSTANCE_ID,
723: MSC_CL_COLLECTION.v_last_collection_id,
724: MSC_CL_COLLECTION.v_current_date,
725: MSC_CL_COLLECTION.v_current_user,
726: MSC_CL_COLLECTION.v_current_date,
727: MSC_CL_COLLECTION.v_current_user,
720: c_rec.ORGANIZATION_ID,
721: c_rec.ROUTING_QUANTITY,
722: c_rec.SR_INSTANCE_ID,
723: MSC_CL_COLLECTION.v_last_collection_id,
724: MSC_CL_COLLECTION.v_current_date,
725: MSC_CL_COLLECTION.v_current_user,
726: MSC_CL_COLLECTION.v_current_date,
727: MSC_CL_COLLECTION.v_current_user,
728: c_rec.first_op_seq_num,
721: c_rec.ROUTING_QUANTITY,
722: c_rec.SR_INSTANCE_ID,
723: MSC_CL_COLLECTION.v_last_collection_id,
724: MSC_CL_COLLECTION.v_current_date,
725: MSC_CL_COLLECTION.v_current_user,
726: MSC_CL_COLLECTION.v_current_date,
727: MSC_CL_COLLECTION.v_current_user,
728: c_rec.first_op_seq_num,
729: c_rec.last_op_seq_num,
722: c_rec.SR_INSTANCE_ID,
723: MSC_CL_COLLECTION.v_last_collection_id,
724: MSC_CL_COLLECTION.v_current_date,
725: MSC_CL_COLLECTION.v_current_user,
726: MSC_CL_COLLECTION.v_current_date,
727: MSC_CL_COLLECTION.v_current_user,
728: c_rec.first_op_seq_num,
729: c_rec.last_op_seq_num,
730: c_rec.common_routing_sequence_id,
723: MSC_CL_COLLECTION.v_last_collection_id,
724: MSC_CL_COLLECTION.v_current_date,
725: MSC_CL_COLLECTION.v_current_user,
726: MSC_CL_COLLECTION.v_current_date,
727: MSC_CL_COLLECTION.v_current_user,
728: c_rec.first_op_seq_num,
729: c_rec.last_op_seq_num,
730: c_rec.common_routing_sequence_id,
731: c_rec.auto_step_qty_flag);
733: END IF; -- SQL%NOTFOUND
734:
735: c_count:= c_count+1;
736:
737: IF c_count> MSC_CL_COLLECTION.PBS THEN
738: COMMIT;
739: c_count:= 0;
740: END IF;
741:
754: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
755: RAISE;
756:
757: ELSE
758: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
759:
760: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
761: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
762: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ROUTING');
777: END IF;
778: END;
779:
780: END LOOP;
781: END IF; -- MSC_CL_COLLECTION.v_is_incremental_refresh
782:
783: COMMIT;
784:
785: END LOAD_ROUTING;
796: BILL_SEQUENCE_ID,
797: ROUTING_SEQUENCE_ID,
798: SR_INSTANCE_ID
799: FROM MSC_ST_OPERATION_COMPONENTS msoc
800: WHERE msoc.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
801: AND msoc.DELETED_FLAG= MSC_UTIL.SYS_NO;
802:
803: CURSOR c9_d IS
804: SELECT
808: ROUTING_SEQUENCE_ID,
809: SR_INSTANCE_ID,
810: ORGANIZATION_ID
811: FROM MSC_ST_OPERATION_COMPONENTS msoc
812: WHERE msoc.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
813: AND msoc.DELETED_FLAG= MSC_UTIL.SYS_YES
814: UNION ALL
815: SELECT DISTINCT
816: TO_NUMBER(NULL),
823: MSC_ST_OPERATION_COMPONENTS msoc
824: WHERE msoc.Bill_Sequence_ID= moc.Bill_Sequence_ID
825: AND msoc.Routing_Sequence_ID <> moc.Routing_Sequence_ID
826: AND msoc.Organization_ID= moc.Organization_ID
827: AND msoc.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
828: AND moc.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
829: AND msoc.DELETED_FLAG= MSC_UTIL.SYS_NO
830: AND moc.PLAN_ID= -1;
831:
824: WHERE msoc.Bill_Sequence_ID= moc.Bill_Sequence_ID
825: AND msoc.Routing_Sequence_ID <> moc.Routing_Sequence_ID
826: AND msoc.Organization_ID= moc.Organization_ID
827: AND msoc.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
828: AND moc.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
829: AND msoc.DELETED_FLAG= MSC_UTIL.SYS_NO
830: AND moc.PLAN_ID= -1;
831:
832:
838: lv_delete_flag BOOLEAN:= FALSE;
839:
840: BEGIN
841:
842: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
843: -- We want to delete all BOM related data and get new stuff.
844:
845: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_COMPONENTS', MSC_CL_COLLECTION.v_instance_id, -1);
846:
841:
842: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
843: -- We want to delete all BOM related data and get new stuff.
844:
845: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_COMPONENTS', MSC_CL_COLLECTION.v_instance_id, -1);
846:
847: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
848: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_COMPONENTS', MSC_CL_COLLECTION.v_instance_id, -1);
849: ELSE
843: -- We want to delete all BOM related data and get new stuff.
844:
845: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_COMPONENTS', MSC_CL_COLLECTION.v_instance_id, -1);
846:
847: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
848: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_COMPONENTS', MSC_CL_COLLECTION.v_instance_id, -1);
849: ELSE
850: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
851: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_COMPONENTS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
844:
845: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_COMPONENTS', MSC_CL_COLLECTION.v_instance_id, -1);
846:
847: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
848: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_COMPONENTS', MSC_CL_COLLECTION.v_instance_id, -1);
849: ELSE
850: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
851: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_COMPONENTS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
852: END IF;
847: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
848: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_COMPONENTS', MSC_CL_COLLECTION.v_instance_id, -1);
849: ELSE
850: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
851: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_COMPONENTS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
852: END IF;
853: BEGIN
854: INSERT /*+ append */
855: INTO MSC_OPERATION_COMPONENTS
872: COMPONENT_SEQUENCE_ID,
873: SR_INSTANCE_ID,
874: BILL_SEQUENCE_ID,
875: ROUTING_SEQUENCE_ID,
876: MSC_CL_COLLECTION.v_last_collection_id,
877: MSC_CL_COLLECTION.v_current_date,
878: MSC_CL_COLLECTION.v_current_user,
879: MSC_CL_COLLECTION.v_current_date,
880: MSC_CL_COLLECTION.v_current_user
873: SR_INSTANCE_ID,
874: BILL_SEQUENCE_ID,
875: ROUTING_SEQUENCE_ID,
876: MSC_CL_COLLECTION.v_last_collection_id,
877: MSC_CL_COLLECTION.v_current_date,
878: MSC_CL_COLLECTION.v_current_user,
879: MSC_CL_COLLECTION.v_current_date,
880: MSC_CL_COLLECTION.v_current_user
881: FROM MSC_ST_OPERATION_COMPONENTS msoc
874: BILL_SEQUENCE_ID,
875: ROUTING_SEQUENCE_ID,
876: MSC_CL_COLLECTION.v_last_collection_id,
877: MSC_CL_COLLECTION.v_current_date,
878: MSC_CL_COLLECTION.v_current_user,
879: MSC_CL_COLLECTION.v_current_date,
880: MSC_CL_COLLECTION.v_current_user
881: FROM MSC_ST_OPERATION_COMPONENTS msoc
882: WHERE msoc.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
875: ROUTING_SEQUENCE_ID,
876: MSC_CL_COLLECTION.v_last_collection_id,
877: MSC_CL_COLLECTION.v_current_date,
878: MSC_CL_COLLECTION.v_current_user,
879: MSC_CL_COLLECTION.v_current_date,
880: MSC_CL_COLLECTION.v_current_user
881: FROM MSC_ST_OPERATION_COMPONENTS msoc
882: WHERE msoc.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
883: AND msoc.DELETED_FLAG= MSC_UTIL.SYS_NO;
876: MSC_CL_COLLECTION.v_last_collection_id,
877: MSC_CL_COLLECTION.v_current_date,
878: MSC_CL_COLLECTION.v_current_user,
879: MSC_CL_COLLECTION.v_current_date,
880: MSC_CL_COLLECTION.v_current_user
881: FROM MSC_ST_OPERATION_COMPONENTS msoc
882: WHERE msoc.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
883: AND msoc.DELETED_FLAG= MSC_UTIL.SYS_NO;
884:
878: MSC_CL_COLLECTION.v_current_user,
879: MSC_CL_COLLECTION.v_current_date,
880: MSC_CL_COLLECTION.v_current_user
881: FROM MSC_ST_OPERATION_COMPONENTS msoc
882: WHERE msoc.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
883: AND msoc.DELETED_FLAG= MSC_UTIL.SYS_NO;
884:
885: COMMIT;
886: EXCEPTION
896:
897: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
898: RAISE;
899: ELSE
900: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
901: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
902: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
903: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_OPERATION_COMPONENTS');
904: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_OPERATION_COMPONENTS');
907: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
908: lb_refresh_failed := TRUE;
909: END IF;
910: END;
911: END IF; -- MSC_CL_COLLECTION.v_is_complete_refresh
912:
913: IF MSC_CL_COLLECTION.v_is_incremental_refresh OR lb_refresh_failed THEN
914:
915: -- set SR_INSTANCE_ID to negative to indicate a SOFT delete
909: END IF;
910: END;
911: END IF; -- MSC_CL_COLLECTION.v_is_complete_refresh
912:
913: IF MSC_CL_COLLECTION.v_is_incremental_refresh OR lb_refresh_failed THEN
914:
915: -- set SR_INSTANCE_ID to negative to indicate a SOFT delete
916: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
917: FOR c_rec IN c9_d LOOP
912:
913: IF MSC_CL_COLLECTION.v_is_incremental_refresh OR lb_refresh_failed THEN
914:
915: -- set SR_INSTANCE_ID to negative to indicate a SOFT delete
916: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
917: FOR c_rec IN c9_d LOOP
918:
919: BEGIN
920:
1004: FOR c_rec IN c9 LOOP
1005:
1006: BEGIN
1007:
1008: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
1009:
1010: UPDATE MSC_OPERATION_COMPONENTS
1011: SET PLAN_ID= -1,
1012: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1008: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
1009:
1010: UPDATE MSC_OPERATION_COMPONENTS
1011: SET PLAN_ID= -1,
1012: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1013: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1014: WHERE PLAN_ID= -1
1015: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
1016: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
1009:
1010: UPDATE MSC_OPERATION_COMPONENTS
1011: SET PLAN_ID= -1,
1012: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1013: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1014: WHERE PLAN_ID= -1
1015: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
1016: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
1017: AND BILL_SEQUENCE_ID = c_rec.BILL_SEQUENCE_ID
1020: AND OPERATION_SEQUENCE_ID= c_rec.OPERATION_SEQUENCE_ID;
1021:
1022: END IF;
1023:
1024: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
1025:
1026: INSERT INTO MSC_OPERATION_COMPONENTS
1027: ( PLAN_ID,
1028: ORGANIZATION_ID,
1043: c_rec.COMPONENT_SEQUENCE_ID,
1044: c_rec.SR_INSTANCE_ID,
1045: c_rec.BILL_SEQUENCE_ID,
1046: c_rec.ROUTING_SEQUENCE_ID,
1047: MSC_CL_COLLECTION.v_last_collection_id,
1048: MSC_CL_COLLECTION.v_current_date,
1049: MSC_CL_COLLECTION.v_current_user,
1050: MSC_CL_COLLECTION.v_current_date,
1051: MSC_CL_COLLECTION.v_current_user);
1044: c_rec.SR_INSTANCE_ID,
1045: c_rec.BILL_SEQUENCE_ID,
1046: c_rec.ROUTING_SEQUENCE_ID,
1047: MSC_CL_COLLECTION.v_last_collection_id,
1048: MSC_CL_COLLECTION.v_current_date,
1049: MSC_CL_COLLECTION.v_current_user,
1050: MSC_CL_COLLECTION.v_current_date,
1051: MSC_CL_COLLECTION.v_current_user);
1052:
1045: c_rec.BILL_SEQUENCE_ID,
1046: c_rec.ROUTING_SEQUENCE_ID,
1047: MSC_CL_COLLECTION.v_last_collection_id,
1048: MSC_CL_COLLECTION.v_current_date,
1049: MSC_CL_COLLECTION.v_current_user,
1050: MSC_CL_COLLECTION.v_current_date,
1051: MSC_CL_COLLECTION.v_current_user);
1052:
1053: END IF;
1046: c_rec.ROUTING_SEQUENCE_ID,
1047: MSC_CL_COLLECTION.v_last_collection_id,
1048: MSC_CL_COLLECTION.v_current_date,
1049: MSC_CL_COLLECTION.v_current_user,
1050: MSC_CL_COLLECTION.v_current_date,
1051: MSC_CL_COLLECTION.v_current_user);
1052:
1053: END IF;
1054:
1047: MSC_CL_COLLECTION.v_last_collection_id,
1048: MSC_CL_COLLECTION.v_current_date,
1049: MSC_CL_COLLECTION.v_current_user,
1050: MSC_CL_COLLECTION.v_current_date,
1051: MSC_CL_COLLECTION.v_current_user);
1052:
1053: END IF;
1054:
1055: c_count:= c_count+1;
1053: END IF;
1054:
1055: c_count:= c_count+1;
1056:
1057: IF c_count> MSC_CL_COLLECTION.PBS THEN
1058: COMMIT;
1059: c_count:= 0;
1060: END IF;
1061:
1073: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1074: RAISE;
1075:
1076: ELSE
1077: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1078:
1079: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1080: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1081: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_OPERATION_COMPONENTS');
1083: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1084:
1085: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1086: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
1087: FND_MESSAGE.SET_TOKEN('VALUE', MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID, MSC_CL_COLLECTION.v_instance_id));
1088: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1089:
1090: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1091: FND_MESSAGE.SET_TOKEN('COLUMN', 'OPERATION_SEQUENCE_ID');
1124: msors.ACTIVITY_GROUP_ID,
1125: msors.SR_INSTANCE_ID,
1126: msors.ORGANIZATION_ID
1127: FROM MSC_ST_OPERATION_RESOURCE_SEQS msors
1128: WHERE msors.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1129: AND msors.DELETED_FLAG= MSC_UTIL.SYS_NO;
1130:
1131: CURSOR c7_d IS
1132: SELECT
1134: msors.OPERATION_SEQUENCE_ID,
1135: msors.RESOURCE_SEQ_NUM,
1136: msors.SR_INSTANCE_ID
1137: FROM MSC_ST_OPERATION_RESOURCE_SEQS msors
1138: WHERE msors.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1139: AND msors.DELETED_FLAG= MSC_UTIL.SYS_YES;
1140:
1141:
1142: c_count NUMBER:= 0;
1149: lv_retcode NUMBER;
1150:
1151: BEGIN
1152:
1153: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
1154: lv_tbl:= 'OPERATION_RESOURCE_SEQS_'||MSC_CL_COLLECTION.v_instance_code;
1155: ELSE
1156: lv_tbl:= 'MSC_OPERATION_RESOURCE_SEQS';
1157: END IF;
1150:
1151: BEGIN
1152:
1153: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
1154: lv_tbl:= 'OPERATION_RESOURCE_SEQS_'||MSC_CL_COLLECTION.v_instance_code;
1155: ELSE
1156: lv_tbl:= 'MSC_OPERATION_RESOURCE_SEQS';
1157: END IF;
1158:
1155: ELSE
1156: lv_tbl:= 'MSC_OPERATION_RESOURCE_SEQS';
1157: END IF;
1158:
1159: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
1160: -- We want to delete all BOM related data and get new stuff.
1161: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_RESOURCE_SEQS', MSC_CL_COLLECTION.v_instance_id, -1);
1162:
1163: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO THEN
1157: END IF;
1158:
1159: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
1160: -- We want to delete all BOM related data and get new stuff.
1161: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_RESOURCE_SEQS', MSC_CL_COLLECTION.v_instance_id, -1);
1162:
1163: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO THEN
1164: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1165: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_RESOURCE_SEQS', MSC_CL_COLLECTION.v_instance_id, -1);
1159: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
1160: -- We want to delete all BOM related data and get new stuff.
1161: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_RESOURCE_SEQS', MSC_CL_COLLECTION.v_instance_id, -1);
1162:
1163: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO THEN
1164: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1165: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_RESOURCE_SEQS', MSC_CL_COLLECTION.v_instance_id, -1);
1166: ELSE
1167: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1160: -- We want to delete all BOM related data and get new stuff.
1161: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_RESOURCE_SEQS', MSC_CL_COLLECTION.v_instance_id, -1);
1162:
1163: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO THEN
1164: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1165: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_RESOURCE_SEQS', MSC_CL_COLLECTION.v_instance_id, -1);
1166: ELSE
1167: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1168: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_RESOURCE_SEQS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
1161: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_RESOURCE_SEQS', MSC_CL_COLLECTION.v_instance_id, -1);
1162:
1163: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO THEN
1164: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1165: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_RESOURCE_SEQS', MSC_CL_COLLECTION.v_instance_id, -1);
1166: ELSE
1167: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1168: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_RESOURCE_SEQS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
1169: END IF;
1164: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1165: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_RESOURCE_SEQS', MSC_CL_COLLECTION.v_instance_id, -1);
1166: ELSE
1167: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1168: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_RESOURCE_SEQS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
1169: END IF;
1170: END IF;
1171:
1172: BEGIN
1204: ||' :v_current_user,'
1205: ||' :v_current_date,'
1206: ||' :v_current_user '
1207: ||' FROM MSC_ST_OPERATION_RESOURCE_SEQS msors'
1208: ||' WHERE msors.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id
1209: ||' AND msors.DELETED_FLAG= '||MSC_UTIL.SYS_NO;
1210:
1211: EXECUTE IMMEDIATE lv_sql_ins
1212: USING MSC_CL_COLLECTION.v_last_collection_id,MSC_CL_COLLECTION.v_current_date,MSC_CL_COLLECTION.v_current_user,MSC_CL_COLLECTION.v_current_date,MSC_CL_COLLECTION.v_current_user;
1208: ||' WHERE msors.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id
1209: ||' AND msors.DELETED_FLAG= '||MSC_UTIL.SYS_NO;
1210:
1211: EXECUTE IMMEDIATE lv_sql_ins
1212: USING MSC_CL_COLLECTION.v_last_collection_id,MSC_CL_COLLECTION.v_current_date,MSC_CL_COLLECTION.v_current_user,MSC_CL_COLLECTION.v_current_date,MSC_CL_COLLECTION.v_current_user;
1213:
1214: COMMIT;
1215: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'operation resource seqs loaded');
1216:
1243: lb_refresh_failed := TRUE;
1244: END IF;
1245: END;
1246:
1247: END IF; -- MSC_CL_COLLECTION.v_is_complete_refresh
1248:
1249: lv_sql_stmt :=
1250: ' INSERT INTO '||lv_tbl
1251: ||' ( PLAN_ID, '
1279: ||' :v_current_user, '
1280: ||' :v_current_date, '
1281: ||' :v_current_user ) ';
1282:
1283: IF MSC_CL_COLLECTION.v_is_incremental_refresh OR lb_refresh_failed THEN
1284:
1285: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
1286:
1287: FOR c_rec IN c7_d LOOP
1281: ||' :v_current_user ) ';
1282:
1283: IF MSC_CL_COLLECTION.v_is_incremental_refresh OR lb_refresh_failed THEN
1284:
1285: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
1286:
1287: FOR c_rec IN c7_d LOOP
1288:
1289: IF c_rec.OPERATION_SEQUENCE_ID IS NOT NULL THEN
1310: FOR c_rec IN c7 LOOP
1311:
1312: BEGIN
1313:
1314: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
1315:
1316: UPDATE MSC_OPERATION_RESOURCE_SEQS
1317: SET
1318: SCHEDULE_FLAG= c_rec.SCHEDULE_FLAG,
1319: RESOURCE_OFFSET_PERCENT= c_rec.RESOURCE_OFFSET_PERCENT,
1320: DEPARTMENT_ID= c_rec.DEPARTMENT_ID,
1321: ACTIVITY_GROUP_ID= c_rec.ACTIVITY_GROUP_ID,
1322: ORGANIZATION_ID=c_rec.ORGANIZATION_ID,
1323: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1324: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1325: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1326: WHERE PLAN_ID= -1
1327: AND ROUTING_SEQUENCE_ID= c_rec.ROUTING_SEQUENCE_ID
1320: DEPARTMENT_ID= c_rec.DEPARTMENT_ID,
1321: ACTIVITY_GROUP_ID= c_rec.ACTIVITY_GROUP_ID,
1322: ORGANIZATION_ID=c_rec.ORGANIZATION_ID,
1323: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1324: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1325: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1326: WHERE PLAN_ID= -1
1327: AND ROUTING_SEQUENCE_ID= c_rec.ROUTING_SEQUENCE_ID
1328: AND OPERATION_SEQUENCE_ID= c_rec.OPERATION_SEQUENCE_ID
1321: ACTIVITY_GROUP_ID= c_rec.ACTIVITY_GROUP_ID,
1322: ORGANIZATION_ID=c_rec.ORGANIZATION_ID,
1323: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1324: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1325: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1326: WHERE PLAN_ID= -1
1327: AND ROUTING_SEQUENCE_ID= c_rec.ROUTING_SEQUENCE_ID
1328: AND OPERATION_SEQUENCE_ID= c_rec.OPERATION_SEQUENCE_ID
1329: AND RESOURCE_SEQ_NUM= c_rec.RESOURCE_SEQ_NUM
1330: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
1331:
1332: END IF;
1333:
1334: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
1335:
1336: EXECUTE IMMEDIATE lv_sql_stmt USING
1337: c_rec.ROUTING_SEQUENCE_ID,
1338: c_rec.OPERATION_SEQUENCE_ID,
1342: c_rec.DEPARTMENT_ID,
1343: c_rec.ACTIVITY_GROUP_ID,
1344: c_rec.SR_INSTANCE_ID,
1345: c_rec.ORGANIZATION_ID,
1346: MSC_CL_COLLECTION.v_last_collection_id,
1347: MSC_CL_COLLECTION.v_current_date,
1348: MSC_CL_COLLECTION.v_current_user,
1349: MSC_CL_COLLECTION.v_current_date,
1350: MSC_CL_COLLECTION.v_current_user ;
1343: c_rec.ACTIVITY_GROUP_ID,
1344: c_rec.SR_INSTANCE_ID,
1345: c_rec.ORGANIZATION_ID,
1346: MSC_CL_COLLECTION.v_last_collection_id,
1347: MSC_CL_COLLECTION.v_current_date,
1348: MSC_CL_COLLECTION.v_current_user,
1349: MSC_CL_COLLECTION.v_current_date,
1350: MSC_CL_COLLECTION.v_current_user ;
1351:
1344: c_rec.SR_INSTANCE_ID,
1345: c_rec.ORGANIZATION_ID,
1346: MSC_CL_COLLECTION.v_last_collection_id,
1347: MSC_CL_COLLECTION.v_current_date,
1348: MSC_CL_COLLECTION.v_current_user,
1349: MSC_CL_COLLECTION.v_current_date,
1350: MSC_CL_COLLECTION.v_current_user ;
1351:
1352: END IF; -- SQL%NOTFOUND
1345: c_rec.ORGANIZATION_ID,
1346: MSC_CL_COLLECTION.v_last_collection_id,
1347: MSC_CL_COLLECTION.v_current_date,
1348: MSC_CL_COLLECTION.v_current_user,
1349: MSC_CL_COLLECTION.v_current_date,
1350: MSC_CL_COLLECTION.v_current_user ;
1351:
1352: END IF; -- SQL%NOTFOUND
1353:
1346: MSC_CL_COLLECTION.v_last_collection_id,
1347: MSC_CL_COLLECTION.v_current_date,
1348: MSC_CL_COLLECTION.v_current_user,
1349: MSC_CL_COLLECTION.v_current_date,
1350: MSC_CL_COLLECTION.v_current_user ;
1351:
1352: END IF; -- SQL%NOTFOUND
1353:
1354: c_count:= c_count+1;
1352: END IF; -- SQL%NOTFOUND
1353:
1354: c_count:= c_count+1;
1355:
1356: IF c_count> MSC_CL_COLLECTION.PBS THEN
1357: COMMIT;
1358: c_count:= 0;
1359: END IF;
1360:
1377: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1378: RAISE;
1379:
1380: ELSE
1381: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1382:
1383: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1384: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1385: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_OP_RESOURCE_SEQ');
1406: END IF;
1407: END;
1408:
1409: END LOOP;
1410: END IF; -- MSC_CL_COLLECTION.v_is_incremental_refresh
1411:
1412: COMMIT;
1413:
1414: BEGIN
1412: COMMIT;
1413:
1414: BEGIN
1415:
1416: IF ((MSC_CL_COLLECTION.v_coll_prec.org_group_flag <> MSC_UTIL.G_ALL_ORGANIZATIONS ) AND (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES)) THEN
1417:
1418: lv_tbl:= 'OPERATION_RESOURCE_SEQS_'||MSC_CL_COLLECTION.v_instance_code;
1419:
1420: lv_sql_stmt:=
1414: BEGIN
1415:
1416: IF ((MSC_CL_COLLECTION.v_coll_prec.org_group_flag <> MSC_UTIL.G_ALL_ORGANIZATIONS ) AND (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES)) THEN
1417:
1418: lv_tbl:= 'OPERATION_RESOURCE_SEQS_'||MSC_CL_COLLECTION.v_instance_code;
1419:
1420: lv_sql_stmt:=
1421: 'INSERT INTO '||lv_tbl
1422: ||' SELECT * from MSC_OPERATION_RESOURCE_SEQS'
1419:
1420: lv_sql_stmt:=
1421: 'INSERT INTO '||lv_tbl
1422: ||' SELECT * from MSC_OPERATION_RESOURCE_SEQS'
1423: ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
1424: ||' AND plan_id = -1 '
1425: ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
1426:
1427: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The sql statement is '||lv_sql_stmt);
1430: COMMIT;
1431:
1432: END IF;
1433:
1434: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
1435: MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
1436: lv_retcode,
1437: 'MSC_OPERATION_RESOURCE_SEQS',
1438: MSC_CL_COLLECTION.v_instance_code,
1431:
1432: END IF;
1433:
1434: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
1435: MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
1436: lv_retcode,
1437: 'MSC_OPERATION_RESOURCE_SEQS',
1438: MSC_CL_COLLECTION.v_instance_code,
1439: MSC_UTIL.G_WARNING
1434: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
1435: MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
1436: lv_retcode,
1437: 'MSC_OPERATION_RESOURCE_SEQS',
1438: MSC_CL_COLLECTION.v_instance_code,
1439: MSC_UTIL.G_WARNING
1440: );
1441:
1442: IF lv_retcode = MSC_UTIL.G_ERROR THEN
1440: );
1441:
1442: IF lv_retcode = MSC_UTIL.G_ERROR THEN
1443: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
1444: RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
1445: ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
1446: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1447: END IF;
1448:
1442: IF lv_retcode = MSC_UTIL.G_ERROR THEN
1443: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
1444: RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
1445: ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
1446: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1447: END IF;
1448:
1449: END IF;
1450:
1489: msro.DELETED_FLAG,
1490: msro.SR_INSTANCE_ID,
1491: msro.ORGANIZATION_ID
1492: FROM MSC_ST_ROUTING_OPERATIONS msro
1493: WHERE msro.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1494: AND msro.DELETED_FLAG= MSC_UTIL.SYS_NO;
1495:
1496: CURSOR c5_d IS
1497: SELECT
1498: msro.ROUTING_SEQUENCE_ID,
1499: msro.OPERATION_SEQUENCE_ID,
1500: msro.SR_INSTANCE_ID
1501: FROM MSC_ST_ROUTING_OPERATIONS msro
1502: WHERE msro.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1503: AND msro.DELETED_FLAG= MSC_UTIL.SYS_YES;
1504:
1505:
1506: c_count NUMBER:= 0;
1513: lv_retcode NUMBER;
1514:
1515: BEGIN
1516:
1517: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
1518: lv_tbl:= 'ROUTING_OPERATIONS_'||MSC_CL_COLLECTION.v_instance_code;
1519: ELSE
1520: lv_tbl:= 'MSC_ROUTING_OPERATIONS';
1521: END IF;
1514:
1515: BEGIN
1516:
1517: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
1518: lv_tbl:= 'ROUTING_OPERATIONS_'||MSC_CL_COLLECTION.v_instance_code;
1519: ELSE
1520: lv_tbl:= 'MSC_ROUTING_OPERATIONS';
1521: END IF;
1522:
1519: ELSE
1520: lv_tbl:= 'MSC_ROUTING_OPERATIONS';
1521: END IF;
1522:
1523: IF MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh THEN
1524: -- We want to delete all BOM related data and get new stuff
1525: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ROUTING_OPERATIONS', MSC_CL_COLLECTION.v_instance_id, -1);
1526:
1527: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO THEN
1521: END IF;
1522:
1523: IF MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh THEN
1524: -- We want to delete all BOM related data and get new stuff
1525: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ROUTING_OPERATIONS', MSC_CL_COLLECTION.v_instance_id, -1);
1526:
1527: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO THEN
1528: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1529: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ROUTING_OPERATIONS', MSC_CL_COLLECTION.v_instance_id, -1);
1523: IF MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh THEN
1524: -- We want to delete all BOM related data and get new stuff
1525: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ROUTING_OPERATIONS', MSC_CL_COLLECTION.v_instance_id, -1);
1526:
1527: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO THEN
1528: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1529: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ROUTING_OPERATIONS', MSC_CL_COLLECTION.v_instance_id, -1);
1530: ELSE
1531: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1524: -- We want to delete all BOM related data and get new stuff
1525: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ROUTING_OPERATIONS', MSC_CL_COLLECTION.v_instance_id, -1);
1526:
1527: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO THEN
1528: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1529: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ROUTING_OPERATIONS', MSC_CL_COLLECTION.v_instance_id, -1);
1530: ELSE
1531: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1532: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ROUTING_OPERATIONS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
1525: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ROUTING_OPERATIONS', MSC_CL_COLLECTION.v_instance_id, -1);
1526:
1527: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO THEN
1528: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1529: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ROUTING_OPERATIONS', MSC_CL_COLLECTION.v_instance_id, -1);
1530: ELSE
1531: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1532: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ROUTING_OPERATIONS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
1533: END IF;
1528: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1529: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ROUTING_OPERATIONS', MSC_CL_COLLECTION.v_instance_id, -1);
1530: ELSE
1531: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1532: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ROUTING_OPERATIONS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
1533: END IF;
1534: END IF;
1535:
1536: BEGIN
1601: ||' :v_current_user, '
1602: ||' :v_current_date, '
1603: ||' :v_current_user '
1604: ||' FROM MSC_ST_ROUTING_OPERATIONS msro '
1605: ||' WHERE msro.SR_INSTANCE_ID= ' || MSC_CL_COLLECTION.v_instance_id
1606: ||' AND msro.DELETED_FLAG= ' || MSC_UTIL.SYS_NO;
1607:
1608: EXECUTE IMMEDIATE lv_sql_ins
1609: USING v_chr10, v_chr13, MSC_CL_COLLECTION.v_last_collection_id,MSC_CL_COLLECTION.v_current_date,MSC_CL_COLLECTION.v_current_user,MSC_CL_COLLECTION.v_current_date,MSC_CL_COLLECTION.v_current_user;
1605: ||' WHERE msro.SR_INSTANCE_ID= ' || MSC_CL_COLLECTION.v_instance_id
1606: ||' AND msro.DELETED_FLAG= ' || MSC_UTIL.SYS_NO;
1607:
1608: EXECUTE IMMEDIATE lv_sql_ins
1609: USING v_chr10, v_chr13, MSC_CL_COLLECTION.v_last_collection_id,MSC_CL_COLLECTION.v_current_date,MSC_CL_COLLECTION.v_current_user,MSC_CL_COLLECTION.v_current_date,MSC_CL_COLLECTION.v_current_user;
1610:
1611: COMMIT;
1612: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'routing operations loaded');
1613:
1640: lb_refresh_failed := TRUE;
1641: END IF;
1642: END;
1643:
1644: END IF; -- MSC_CL_COLLECTION.v_is_complete_refresh
1645:
1646:
1647: lv_sql_stmt:=
1648: ' INSERT INTO '||lv_tbl
1711: ||' :v_current_user, '
1712: ||' :v_current_date, '
1713: ||' :v_current_user ) ';
1714:
1715: IF MSC_CL_COLLECTION.v_is_incremental_refresh OR lb_refresh_failed THEN
1716:
1717: -- set SR_INSTANCE_ID to negative to indicate a SOFT delete
1718:
1719: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
1715: IF MSC_CL_COLLECTION.v_is_incremental_refresh OR lb_refresh_failed THEN
1716:
1717: -- set SR_INSTANCE_ID to negative to indicate a SOFT delete
1718:
1719: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
1720: FOR c_rec IN c5_d LOOP
1721:
1722: DELETE MSC_ROUTING_OPERATIONS
1723: WHERE PLAN_ID= -1
1733: FOR c_rec IN c5 LOOP
1734:
1735: BEGIN
1736:
1737: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
1738: UPDATE MSC_ROUTING_OPERATIONS
1739: SET
1740: OPERATION_SEQ_NUM= c_rec.OPERATION_SEQ_NUM,
1741: OPERATION_DESCRIPTION= c_rec.OPERATION_DESCRIPTION,
1759: STANDARD_OPERATION_CODE= c_rec.STANDARD_OPERATION_CODE,
1760: STEP_QUANTITY= c_rec.STEP_QUANTITY,
1761: STEP_QUANTITY_UOM= c_rec.STEP_QUANTITY_UOM,
1762: ORGANIZATION_ID = c_rec.ORGANIZATION_ID,
1763: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1764: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1765: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1766: WHERE PLAN_ID= -1
1767: AND ROUTING_SEQUENCE_ID= c_rec.ROUTING_SEQUENCE_ID
1760: STEP_QUANTITY= c_rec.STEP_QUANTITY,
1761: STEP_QUANTITY_UOM= c_rec.STEP_QUANTITY_UOM,
1762: ORGANIZATION_ID = c_rec.ORGANIZATION_ID,
1763: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1764: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1765: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1766: WHERE PLAN_ID= -1
1767: AND ROUTING_SEQUENCE_ID= c_rec.ROUTING_SEQUENCE_ID
1768: AND OPERATION_SEQUENCE_ID= c_rec.OPERATION_SEQUENCE_ID
1761: STEP_QUANTITY_UOM= c_rec.STEP_QUANTITY_UOM,
1762: ORGANIZATION_ID = c_rec.ORGANIZATION_ID,
1763: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1764: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1765: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1766: WHERE PLAN_ID= -1
1767: AND ROUTING_SEQUENCE_ID= c_rec.ROUTING_SEQUENCE_ID
1768: AND OPERATION_SEQUENCE_ID= c_rec.OPERATION_SEQUENCE_ID
1769: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
1768: AND OPERATION_SEQUENCE_ID= c_rec.OPERATION_SEQUENCE_ID
1769: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
1770: END IF;
1771:
1772: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
1773: EXECUTE IMMEDIATE lv_sql_stmt USING
1774: c_rec.ROUTING_SEQUENCE_ID,
1775: c_rec.OPERATION_SEQ_NUM,
1776: c_rec.OPERATION_SEQUENCE_ID,
1796: c_rec.STEP_QUANTITY,
1797: c_rec.STEP_QUANTITY_UOM,
1798: c_rec.SR_INSTANCE_ID,
1799: c_rec.ORGANIZATION_ID,
1800: MSC_CL_COLLECTION.v_last_collection_id,
1801: MSC_CL_COLLECTION.v_current_date,
1802: MSC_CL_COLLECTION.v_current_user,
1803: MSC_CL_COLLECTION.v_current_date,
1804: MSC_CL_COLLECTION.v_current_user ;
1797: c_rec.STEP_QUANTITY_UOM,
1798: c_rec.SR_INSTANCE_ID,
1799: c_rec.ORGANIZATION_ID,
1800: MSC_CL_COLLECTION.v_last_collection_id,
1801: MSC_CL_COLLECTION.v_current_date,
1802: MSC_CL_COLLECTION.v_current_user,
1803: MSC_CL_COLLECTION.v_current_date,
1804: MSC_CL_COLLECTION.v_current_user ;
1805:
1798: c_rec.SR_INSTANCE_ID,
1799: c_rec.ORGANIZATION_ID,
1800: MSC_CL_COLLECTION.v_last_collection_id,
1801: MSC_CL_COLLECTION.v_current_date,
1802: MSC_CL_COLLECTION.v_current_user,
1803: MSC_CL_COLLECTION.v_current_date,
1804: MSC_CL_COLLECTION.v_current_user ;
1805:
1806: END IF; -- SQL%NOTFOUND
1799: c_rec.ORGANIZATION_ID,
1800: MSC_CL_COLLECTION.v_last_collection_id,
1801: MSC_CL_COLLECTION.v_current_date,
1802: MSC_CL_COLLECTION.v_current_user,
1803: MSC_CL_COLLECTION.v_current_date,
1804: MSC_CL_COLLECTION.v_current_user ;
1805:
1806: END IF; -- SQL%NOTFOUND
1807:
1800: MSC_CL_COLLECTION.v_last_collection_id,
1801: MSC_CL_COLLECTION.v_current_date,
1802: MSC_CL_COLLECTION.v_current_user,
1803: MSC_CL_COLLECTION.v_current_date,
1804: MSC_CL_COLLECTION.v_current_user ;
1805:
1806: END IF; -- SQL%NOTFOUND
1807:
1808: c_count:= c_count+1;
1806: END IF; -- SQL%NOTFOUND
1807:
1808: c_count:= c_count+1;
1809:
1810: IF c_count> MSC_CL_COLLECTION.PBS THEN
1811: COMMIT;
1812: c_count:= 0;
1813: END IF;
1814:
1827: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1828: RAISE;
1829:
1830: ELSE
1831: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1832:
1833: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1834: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1835: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ROUTING_OPERATIONS');
1855: END IF;
1856: END;
1857:
1858: END LOOP;
1859: END IF; -- MSC_CL_COLLECTION.v_is_incremental_refresh
1860:
1861: COMMIT;
1862:
1863: BEGIN
1861: COMMIT;
1862:
1863: BEGIN
1864:
1865: IF ((MSC_CL_COLLECTION.v_coll_prec.org_group_flag <> MSC_UTIL.G_ALL_ORGANIZATIONS ) AND (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES)) THEN
1866:
1867: lv_tbl:= 'ROUTING_OPERATIONS_'||MSC_CL_COLLECTION.v_instance_code;
1868:
1869: lv_sql_stmt:=
1863: BEGIN
1864:
1865: IF ((MSC_CL_COLLECTION.v_coll_prec.org_group_flag <> MSC_UTIL.G_ALL_ORGANIZATIONS ) AND (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES)) THEN
1866:
1867: lv_tbl:= 'ROUTING_OPERATIONS_'||MSC_CL_COLLECTION.v_instance_code;
1868:
1869: lv_sql_stmt:=
1870: 'INSERT INTO '||lv_tbl
1871: ||' SELECT * from MSC_ROUTING_OPERATIONS'
1868:
1869: lv_sql_stmt:=
1870: 'INSERT INTO '||lv_tbl
1871: ||' SELECT * from MSC_ROUTING_OPERATIONS'
1872: ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
1873: ||' AND plan_id = -1 '
1874: ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
1875:
1876: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The sql statement is '||lv_sql_stmt);
1879: COMMIT;
1880:
1881: END IF;
1882:
1883: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
1884: MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
1885: lv_retcode,
1886: 'MSC_ROUTING_OPERATIONS',
1887: MSC_CL_COLLECTION.v_instance_code,
1880:
1881: END IF;
1882:
1883: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
1884: MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
1885: lv_retcode,
1886: 'MSC_ROUTING_OPERATIONS',
1887: MSC_CL_COLLECTION.v_instance_code,
1888: MSC_UTIL.G_WARNING
1883: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
1884: MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
1885: lv_retcode,
1886: 'MSC_ROUTING_OPERATIONS',
1887: MSC_CL_COLLECTION.v_instance_code,
1888: MSC_UTIL.G_WARNING
1889: );
1890:
1891: IF lv_retcode = MSC_UTIL.G_ERROR THEN
1889: );
1890:
1891: IF lv_retcode = MSC_UTIL.G_ERROR THEN
1892: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
1893: RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
1894: ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
1895: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1896: END IF;
1897:
1891: IF lv_retcode = MSC_UTIL.G_ERROR THEN
1892: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
1893: RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
1894: ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
1895: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1896: END IF;
1897:
1898: END IF;
1899:
1932: msor.MAXIMUM_CAPACITY ,
1933: msor.orig_resource_seq_num,
1934: msor.BREAKABLE_ACTIVITY_FLAG /*ds change change end */
1935: FROM MSC_ST_OPERATION_RESOURCES msor
1936: WHERE msor.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1937: AND msor.DELETED_FLAG= MSC_UTIL.SYS_NO;
1938:
1939: CURSOR c6_d IS
1940: SELECT
1944: msor.RESOURCE_ID,
1945: msor.ALTERNATE_NUMBER,
1946: msor.SR_INSTANCE_ID
1947: FROM MSC_ST_OPERATION_RESOURCES msor
1948: WHERE msor.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1949: AND msor.DELETED_FLAG= MSC_UTIL.SYS_YES;
1950:
1951: CURSOR c6_corrupt_data IS
1952: SELECT
1953: msor.ROUTING_SEQUENCE_ID,
1954: msor.RESOURCE_ID,
1955: msor.RESOURCE_SEQ_NUM
1956: FROM MSC_ST_OPERATION_RESOURCES msor
1957: WHERE msor.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1958: AND msor.DELETED_FLAG= MSC_UTIL.SYS_NO
1959: AND nvl(msor.RESOURCE_SEQ_NUM,0)=0
1960: AND msor.ALTERNATE_NUMBER > 0;
1961:
1971: lv_delete_flag BOOLEAN:= FALSE;
1972:
1973: BEGIN
1974:
1975: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
1976: lv_tbl:= 'OPERATION_RESOURCES_'||MSC_CL_COLLECTION.v_instance_code;
1977: ELSE
1978: lv_tbl:= 'MSC_OPERATION_RESOURCES';
1979: END IF;
1972:
1973: BEGIN
1974:
1975: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
1976: lv_tbl:= 'OPERATION_RESOURCES_'||MSC_CL_COLLECTION.v_instance_code;
1977: ELSE
1978: lv_tbl:= 'MSC_OPERATION_RESOURCES';
1979: END IF;
1980:
1977: ELSE
1978: lv_tbl:= 'MSC_OPERATION_RESOURCES';
1979: END IF;
1980:
1981: IF (MSC_CL_COLLECTION.v_is_complete_refresh or MSC_CL_COLLECTION.v_is_partial_refresh) THEN
1982:
1983: -- We want to delete all BOM related data and get new stuff.
1984: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1);
1985:
1980:
1981: IF (MSC_CL_COLLECTION.v_is_complete_refresh or MSC_CL_COLLECTION.v_is_partial_refresh) THEN
1982:
1983: -- We want to delete all BOM related data and get new stuff.
1984: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1);
1985:
1986: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO THEN
1987: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1988: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1);
1982:
1983: -- We want to delete all BOM related data and get new stuff.
1984: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1);
1985:
1986: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO THEN
1987: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1988: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1);
1989: ELSE
1990: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1983: -- We want to delete all BOM related data and get new stuff.
1984: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1);
1985:
1986: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO THEN
1987: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1988: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1);
1989: ELSE
1990: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1991: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
1984: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1);
1985:
1986: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO THEN
1987: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1988: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1);
1989: ELSE
1990: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1991: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
1992: END IF;
1987: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1988: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1);
1989: ELSE
1990: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1991: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
1992: END IF;
1993: END IF;
1994:
1995: BEGIN
2046: ||' :v_current_user, '
2047: ||' :v_current_date, '
2048: ||' :v_current_user '
2049: ||' FROM MSC_ST_OPERATION_RESOURCES msor'
2050: ||' WHERE msor.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id
2051: ||' AND msor.DELETED_FLAG= '||MSC_UTIL.SYS_NO
2052: ||' AND NOT(nvl(msor.RESOURCE_SEQ_NUM,0)=0 AND msor.ALTERNATE_NUMBER > 0)';
2053:
2054: EXECUTE IMMEDIATE lv_sql_ins
2051: ||' AND msor.DELETED_FLAG= '||MSC_UTIL.SYS_NO
2052: ||' AND NOT(nvl(msor.RESOURCE_SEQ_NUM,0)=0 AND msor.ALTERNATE_NUMBER > 0)';
2053:
2054: EXECUTE IMMEDIATE lv_sql_ins
2055: USING MSC_CL_COLLECTION.v_last_collection_id,MSC_CL_COLLECTION.v_current_date,MSC_CL_COLLECTION.v_current_user,MSC_CL_COLLECTION.v_current_date,MSC_CL_COLLECTION.v_current_user;
2056:
2057: COMMIT;
2058: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'operation resources loaded');
2059:
2058: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'operation resources loaded');
2059:
2060: FOR c_rec IN c6_corrupt_data LOOP
2061:
2062: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2063:
2064: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2065:
2066: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2110: END IF;
2111:
2112: END;
2113:
2114: END IF; -- MSC_CL_COLLECTION.v_is_complete_refresh
2115:
2116: IF MSC_CL_COLLECTION.v_is_incremental_refresh OR lb_refresh_failed THEN
2117:
2118: lv_sql_stmt :=
2112: END;
2113:
2114: END IF; -- MSC_CL_COLLECTION.v_is_complete_refresh
2115:
2116: IF MSC_CL_COLLECTION.v_is_incremental_refresh OR lb_refresh_failed THEN
2117:
2118: lv_sql_stmt :=
2119: ' INSERT INTO '||lv_tbl
2120: ||' ( PLAN_ID, '
2170: ||' :v_current_user ) ';
2171:
2172:
2173: -- set RESOURCE_USAGE to 0 to indicate a SOFT delete
2174: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
2175: FOR c_rec IN c6_d LOOP
2176: ---5470477
2177: lv_delete_flag := FALSE;
2178: lv_sql_stmt1 := ' UPDATE MSC_OPERATION_RESOURCES '
2209: END IF;
2210:
2211: BEGIN
2212: If (lv_delete_flag) then
2213: EXECUTE IMMEDIATE lv_sql_stmt1 USING MSC_CL_COLLECTION.v_last_collection_id, MSC_CL_COLLECTION.v_current_date, MSC_CL_COLLECTION.v_current_user, c_rec.SR_INSTANCE_ID ;
2214: end if;
2215: EXCEPTION
2216: WHEN OTHERS THEN
2217: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '------------------------------');
2227: /*
2228: UPDATE MSC_OPERATION_RESOURCES
2229: SET RESOURCE_USAGE= 0,
2230: REFRESH_NUMBER= v_last_collection_id,
2231: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2232: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2233: WHERE PLAN_ID= -1
2234: AND ROUTING_SEQUENCE_ID= NVL(c_rec.ROUTING_SEQUENCE_ID,ROUTING_SEQUENCE_ID)
2235: AND OPERATION_SEQUENCE_ID= NVL(c_rec.OPERATION_SEQUENCE_ID,OPERATION_SEQUENCE_ID)
2228: UPDATE MSC_OPERATION_RESOURCES
2229: SET RESOURCE_USAGE= 0,
2230: REFRESH_NUMBER= v_last_collection_id,
2231: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2232: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2233: WHERE PLAN_ID= -1
2234: AND ROUTING_SEQUENCE_ID= NVL(c_rec.ROUTING_SEQUENCE_ID,ROUTING_SEQUENCE_ID)
2235: AND OPERATION_SEQUENCE_ID= NVL(c_rec.OPERATION_SEQUENCE_ID,OPERATION_SEQUENCE_ID)
2236: AND RESOURCE_SEQ_NUM= NVL(c_rec.RESOURCE_SEQ_NUM,RESOURCE_SEQ_NUM)
2248: FOR c_rec IN c6 LOOP
2249:
2250: BEGIN
2251:
2252: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
2253:
2254: UPDATE MSC_OPERATION_RESOURCES
2255: SET
2256: PRINCIPAL_FLAG= c_rec.PRINCIPAL_FLAG,
2259: MAX_RESOURCE_UNITS= c_rec.MAX_RESOURCE_UNITS,
2260: RESOURCE_UNITS= c_rec.RESOURCE_UNITS,
2261: UOM_CODE= c_rec.UOM_CODE,
2262: RESOURCE_TYPE= c_rec.RESOURCE_TYPE,
2263: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2264: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2265: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
2266: ALTERNATE_NUMBER= c_rec.ALTERNATE_NUMBER,
2267: ORGANIZATION_ID= c_rec.ORGANIZATION_ID ,
2260: RESOURCE_UNITS= c_rec.RESOURCE_UNITS,
2261: UOM_CODE= c_rec.UOM_CODE,
2262: RESOURCE_TYPE= c_rec.RESOURCE_TYPE,
2263: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2264: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2265: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
2266: ALTERNATE_NUMBER= c_rec.ALTERNATE_NUMBER,
2267: ORGANIZATION_ID= c_rec.ORGANIZATION_ID ,
2268: SETUP_ID= c_rec.SETUP_ID , /* ds change change start*/
2261: UOM_CODE= c_rec.UOM_CODE,
2262: RESOURCE_TYPE= c_rec.RESOURCE_TYPE,
2263: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2264: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2265: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
2266: ALTERNATE_NUMBER= c_rec.ALTERNATE_NUMBER,
2267: ORGANIZATION_ID= c_rec.ORGANIZATION_ID ,
2268: SETUP_ID= c_rec.SETUP_ID , /* ds change change start*/
2269: MINIMUM_CAPACITY = c_rec.MINIMUM_CAPACITY ,
2280: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
2281:
2282: END IF;
2283:
2284: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
2285: /* Start changes for 3586386*/
2286: IF nvl(c_rec.RESOURCE_SEQ_NUM,0)=0 AND c_rec.ALTERNATE_NUMBER > 0 THEN
2287:
2288: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2284: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
2285: /* Start changes for 3586386*/
2286: IF nvl(c_rec.RESOURCE_SEQ_NUM,0)=0 AND c_rec.ALTERNATE_NUMBER > 0 THEN
2287:
2288: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2289:
2290: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2291:
2292: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2325: c_rec.MINIMUM_CAPACITY,
2326: c_rec.MAXIMUM_CAPACITY,
2327: c_rec.orig_resource_seq_num,
2328: c_rec.BREAKABLE_ACTIVITY_FLAG, /* ds change change end */
2329: MSC_CL_COLLECTION.v_last_collection_id,
2330: MSC_CL_COLLECTION.v_current_date,
2331: MSC_CL_COLLECTION.v_current_user,
2332: MSC_CL_COLLECTION.v_current_date,
2333: MSC_CL_COLLECTION.v_current_user ;
2326: c_rec.MAXIMUM_CAPACITY,
2327: c_rec.orig_resource_seq_num,
2328: c_rec.BREAKABLE_ACTIVITY_FLAG, /* ds change change end */
2329: MSC_CL_COLLECTION.v_last_collection_id,
2330: MSC_CL_COLLECTION.v_current_date,
2331: MSC_CL_COLLECTION.v_current_user,
2332: MSC_CL_COLLECTION.v_current_date,
2333: MSC_CL_COLLECTION.v_current_user ;
2334: END IF;
2327: c_rec.orig_resource_seq_num,
2328: c_rec.BREAKABLE_ACTIVITY_FLAG, /* ds change change end */
2329: MSC_CL_COLLECTION.v_last_collection_id,
2330: MSC_CL_COLLECTION.v_current_date,
2331: MSC_CL_COLLECTION.v_current_user,
2332: MSC_CL_COLLECTION.v_current_date,
2333: MSC_CL_COLLECTION.v_current_user ;
2334: END IF;
2335:
2328: c_rec.BREAKABLE_ACTIVITY_FLAG, /* ds change change end */
2329: MSC_CL_COLLECTION.v_last_collection_id,
2330: MSC_CL_COLLECTION.v_current_date,
2331: MSC_CL_COLLECTION.v_current_user,
2332: MSC_CL_COLLECTION.v_current_date,
2333: MSC_CL_COLLECTION.v_current_user ;
2334: END IF;
2335:
2336: END IF; -- SQL%NOTFOUND
2329: MSC_CL_COLLECTION.v_last_collection_id,
2330: MSC_CL_COLLECTION.v_current_date,
2331: MSC_CL_COLLECTION.v_current_user,
2332: MSC_CL_COLLECTION.v_current_date,
2333: MSC_CL_COLLECTION.v_current_user ;
2334: END IF;
2335:
2336: END IF; -- SQL%NOTFOUND
2337:
2336: END IF; -- SQL%NOTFOUND
2337:
2338: c_count:= c_count+1;
2339:
2340: IF c_count> MSC_CL_COLLECTION.PBS THEN
2341: COMMIT;
2342: c_count:= 0;
2343: END IF;
2344:
2356: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2357: RAISE;
2358:
2359: ELSE
2360: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2361:
2362: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2363: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2364: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_OPERATION_RESOURCES');
2395: COMMIT;
2396:
2397: BEGIN
2398:
2399: IF ((MSC_CL_COLLECTION.v_coll_prec.org_group_flag <> MSC_UTIL.G_ALL_ORGANIZATIONS ) AND (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES)) THEN
2400:
2401: lv_tbl:= 'OPERATION_RESOURCES_'||MSC_CL_COLLECTION.v_instance_code;
2402:
2403: lv_sql_stmt:=
2397: BEGIN
2398:
2399: IF ((MSC_CL_COLLECTION.v_coll_prec.org_group_flag <> MSC_UTIL.G_ALL_ORGANIZATIONS ) AND (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES)) THEN
2400:
2401: lv_tbl:= 'OPERATION_RESOURCES_'||MSC_CL_COLLECTION.v_instance_code;
2402:
2403: lv_sql_stmt:=
2404: 'INSERT INTO '||lv_tbl
2405: ||' SELECT * from MSC_OPERATION_RESOURCES'
2402:
2403: lv_sql_stmt:=
2404: 'INSERT INTO '||lv_tbl
2405: ||' SELECT * from MSC_OPERATION_RESOURCES'
2406: ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
2407: ||' AND plan_id = -1 '
2408: ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
2409:
2410: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The sql statement is '||lv_sql_stmt);
2413: COMMIT;
2414:
2415: END IF;
2416:
2417: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
2418: MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
2419: lv_retcode,
2420: 'MSC_OPERATION_RESOURCES',
2421: MSC_CL_COLLECTION.v_instance_code,
2414:
2415: END IF;
2416:
2417: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
2418: MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
2419: lv_retcode,
2420: 'MSC_OPERATION_RESOURCES',
2421: MSC_CL_COLLECTION.v_instance_code,
2422: MSC_UTIL.G_WARNING
2417: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
2418: MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
2419: lv_retcode,
2420: 'MSC_OPERATION_RESOURCES',
2421: MSC_CL_COLLECTION.v_instance_code,
2422: MSC_UTIL.G_WARNING
2423: );
2424:
2425: IF lv_retcode = MSC_UTIL.G_ERROR THEN
2423: );
2424:
2425: IF lv_retcode = MSC_UTIL.G_ERROR THEN
2426: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
2427: RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
2428: ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
2429: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2430: END IF;
2431:
2425: IF lv_retcode = MSC_UTIL.G_ERROR THEN
2426: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
2427: RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
2428: ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
2429: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2430: END IF;
2431:
2432: END IF;
2433: