[Home] [Help]
32: mrs.setup_Description,
33: mrs.operation_desc,
34: mrs.version
35: FROM MSC_ST_RESOURCE_SETUPS mrs
36: WHERE mrs.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
37: ORDER BY mrs.DELETED_FLAG;
38: lv_cnt NUMBER;
39: lv_pbs NUMBER;
40:
41: c_count NUMBER := 0;
42: total_count NUMBER := 0;
43: BEGIN
44:
45: IF ((MSC_CL_COLLECTION.v_is_complete_refresh) OR (MSC_CL_COLLECTION.v_is_partial_refresh) OR (MSC_CL_COLLECTION.v_is_legacy_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_RESOURCE_SETUPS', MSC_CL_COLLECTION.v_instance_id, -1);
48: ELSE
49: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
42: total_count NUMBER := 0;
43: BEGIN
44:
45: IF ((MSC_CL_COLLECTION.v_is_complete_refresh) OR (MSC_CL_COLLECTION.v_is_partial_refresh) OR (MSC_CL_COLLECTION.v_is_legacy_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_RESOURCE_SETUPS', MSC_CL_COLLECTION.v_instance_id, -1);
48: ELSE
49: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
50: MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_RESOURCE_SETUPS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
43: BEGIN
44:
45: IF ((MSC_CL_COLLECTION.v_is_complete_refresh) OR (MSC_CL_COLLECTION.v_is_partial_refresh) OR (MSC_CL_COLLECTION.v_is_legacy_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_RESOURCE_SETUPS', MSC_CL_COLLECTION.v_instance_id, -1);
48: ELSE
49: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
50: MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_RESOURCE_SETUPS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
51: END IF;
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_RESOURCE_SETUPS', MSC_CL_COLLECTION.v_instance_id, -1);
48: ELSE
49: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
50: MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_RESOURCE_SETUPS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
51: END IF;
52:
53:
54: c_count := 0;
81: c_rec.SETUP_CODE,
82: c_rec.SETUP_DESCRIPTION,
83: c_rec.OPERATION_DESC,
84: c_rec.VERSION,
85: MSC_CL_COLLECTION.v_last_collection_id,
86: MSC_CL_COLLECTION.v_current_date,
87: MSC_CL_COLLECTION.v_current_user,
88: MSC_CL_COLLECTION.v_current_date,
89: MSC_CL_COLLECTION.v_current_user );
82: c_rec.SETUP_DESCRIPTION,
83: c_rec.OPERATION_DESC,
84: c_rec.VERSION,
85: MSC_CL_COLLECTION.v_last_collection_id,
86: MSC_CL_COLLECTION.v_current_date,
87: MSC_CL_COLLECTION.v_current_user,
88: MSC_CL_COLLECTION.v_current_date,
89: MSC_CL_COLLECTION.v_current_user );
90:
83: c_rec.OPERATION_DESC,
84: c_rec.VERSION,
85: MSC_CL_COLLECTION.v_last_collection_id,
86: MSC_CL_COLLECTION.v_current_date,
87: MSC_CL_COLLECTION.v_current_user,
88: MSC_CL_COLLECTION.v_current_date,
89: MSC_CL_COLLECTION.v_current_user );
90:
91: c_count:= c_count+1;
84: c_rec.VERSION,
85: MSC_CL_COLLECTION.v_last_collection_id,
86: MSC_CL_COLLECTION.v_current_date,
87: MSC_CL_COLLECTION.v_current_user,
88: MSC_CL_COLLECTION.v_current_date,
89: MSC_CL_COLLECTION.v_current_user );
90:
91: c_count:= c_count+1;
92: total_count:= total_count+1;
85: MSC_CL_COLLECTION.v_last_collection_id,
86: MSC_CL_COLLECTION.v_current_date,
87: MSC_CL_COLLECTION.v_current_user,
88: MSC_CL_COLLECTION.v_current_date,
89: MSC_CL_COLLECTION.v_current_user );
90:
91: c_count:= c_count+1;
92: total_count:= total_count+1;
93:
90:
91: c_count:= c_count+1;
92: total_count:= total_count+1;
93:
94: IF c_count> MSC_CL_COLLECTION.PBS THEN
95: COMMIT;
96: c_count:= 0;
97: END IF;
98:
111: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
112: RAISE;
113:
114: ELSE
115: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
116:
117: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
118: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
119: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE_SETUP');
123: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
124: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
125: FND_MESSAGE.SET_TOKEN('VALUE',
126: MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
127: MSC_CL_COLLECTION.v_instance_id));
128: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
129:
130: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
131: FND_MESSAGE.SET_TOKEN('COLUMN', 'RESOURCE_ID');
145: END LOOP;
146: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Total resource setups = '|| to_char(total_count));
147:
148: COMMIT;
149: END IF; /* MSC_CL_COLLECTION.v_is_complete_refresh OR v_is_partial_refresh */
150: END LOAD_RESOURCE_SETUP;
151:
152: PROCEDURE LOAD_SETUP_TRANSITION
153: IS
162: mrs.transition_time,
163: mrs.transition_uom,
164: mrs.transition_penalty
165: FROM MSC_ST_SETUP_TRANSITIONS mrs
166: WHERE mrs.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
167:
168: lv_cnt NUMBER;
169: lv_pbs NUMBER;
170: c_count NUMBER := 0;
170: c_count NUMBER := 0;
171: total_count NUMBER := 0;
172: BEGIN
173:
174: IF ((MSC_CL_COLLECTION.v_is_complete_refresh) OR (MSC_CL_COLLECTION.v_is_partial_refresh) OR (MSC_CL_COLLECTION.v_is_legacy_refresh)) THEN
175: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
176: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SETUP_TRANSITIONS', MSC_CL_COLLECTION.v_instance_id, -1);
177: ELSE
178: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
171: total_count NUMBER := 0;
172: BEGIN
173:
174: IF ((MSC_CL_COLLECTION.v_is_complete_refresh) OR (MSC_CL_COLLECTION.v_is_partial_refresh) OR (MSC_CL_COLLECTION.v_is_legacy_refresh)) THEN
175: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
176: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SETUP_TRANSITIONS', MSC_CL_COLLECTION.v_instance_id, -1);
177: ELSE
178: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
179: MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_SETUP_TRANSITIONS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
172: BEGIN
173:
174: IF ((MSC_CL_COLLECTION.v_is_complete_refresh) OR (MSC_CL_COLLECTION.v_is_partial_refresh) OR (MSC_CL_COLLECTION.v_is_legacy_refresh)) THEN
175: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
176: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SETUP_TRANSITIONS', MSC_CL_COLLECTION.v_instance_id, -1);
177: ELSE
178: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
179: MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_SETUP_TRANSITIONS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
180: END IF;
175: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
176: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SETUP_TRANSITIONS', MSC_CL_COLLECTION.v_instance_id, -1);
177: ELSE
178: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
179: MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_SETUP_TRANSITIONS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
180: END IF;
181:
182:
183: c_count := 0;
210: c_rec.STANDARD_OPERATION_ID,
211: c_rec.TRANSITION_TIME,
212: c_rec.TRANSITION_UOM,
213: c_rec.TRANSITION_PENALTY,
214: MSC_CL_COLLECTION.v_last_collection_id,
215: MSC_CL_COLLECTION.v_current_date,
216: MSC_CL_COLLECTION.v_current_user,
217: MSC_CL_COLLECTION.v_current_date,
218: MSC_CL_COLLECTION.v_current_user );
211: c_rec.TRANSITION_TIME,
212: c_rec.TRANSITION_UOM,
213: c_rec.TRANSITION_PENALTY,
214: MSC_CL_COLLECTION.v_last_collection_id,
215: MSC_CL_COLLECTION.v_current_date,
216: MSC_CL_COLLECTION.v_current_user,
217: MSC_CL_COLLECTION.v_current_date,
218: MSC_CL_COLLECTION.v_current_user );
219:
212: c_rec.TRANSITION_UOM,
213: c_rec.TRANSITION_PENALTY,
214: MSC_CL_COLLECTION.v_last_collection_id,
215: MSC_CL_COLLECTION.v_current_date,
216: MSC_CL_COLLECTION.v_current_user,
217: MSC_CL_COLLECTION.v_current_date,
218: MSC_CL_COLLECTION.v_current_user );
219:
220: c_count:= c_count+1;
213: c_rec.TRANSITION_PENALTY,
214: MSC_CL_COLLECTION.v_last_collection_id,
215: MSC_CL_COLLECTION.v_current_date,
216: MSC_CL_COLLECTION.v_current_user,
217: MSC_CL_COLLECTION.v_current_date,
218: MSC_CL_COLLECTION.v_current_user );
219:
220: c_count:= c_count+1;
221: total_count:= total_count+1;
214: MSC_CL_COLLECTION.v_last_collection_id,
215: MSC_CL_COLLECTION.v_current_date,
216: MSC_CL_COLLECTION.v_current_user,
217: MSC_CL_COLLECTION.v_current_date,
218: MSC_CL_COLLECTION.v_current_user );
219:
220: c_count:= c_count+1;
221: total_count:= total_count+1;
222:
219:
220: c_count:= c_count+1;
221: total_count:= total_count+1;
222:
223: IF c_count> MSC_CL_COLLECTION.PBS THEN
224: COMMIT;
225: c_count:= 0;
226: END IF;
227:
239:
240: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
241: RAISE;
242: ELSE
243: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
244:
245: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
246: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
247: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SETUP_TRANSITION');
251: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
252: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
253: FND_MESSAGE.SET_TOKEN('VALUE',
254: MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
255: MSC_CL_COLLECTION.v_instance_id));
256: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
257:
258: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
259: FND_MESSAGE.SET_TOKEN('COLUMN', 'RESOURCE_ID');
279: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Total resource transition = '|| to_char(total_count));
280:
281: COMMIT;
282:
283: END IF; /* MSC_CL_COLLECTION.v_is_complete_refresh) OR (v_is_partial_refresh */
284:
285: END LOAD_SETUP_TRANSITION;
286:
287: PROCEDURE LOAD_RESOURCE_CHARGES
305: lv_CHARGE_END_DATETIME DATE;
306:
307:
308: BEGIN
309: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
310: lv_res_req_tbl := 'RESOURCE_REQUIREMENTS_'||MSC_CL_COLLECTION.v_instance_code;
311: ELSE
312: lv_res_req_tbl := 'MSC_RESOURCE_REQUIREMENTS';
313: END IF;
306:
307:
308: BEGIN
309: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
310: lv_res_req_tbl := 'RESOURCE_REQUIREMENTS_'||MSC_CL_COLLECTION.v_instance_code;
311: ELSE
312: lv_res_req_tbl := 'MSC_RESOURCE_REQUIREMENTS';
313: END IF;
314:
324: ||' mrc.CHARGE_END_DATETIME,'
325: ||' mrc.DEPARTMENT_ID'
326: ||' FROM '||lv_res_req_tbl||' mrr,'
327: ||' MSC_ST_RESOURCE_CHARGES mrc'
328: ||' WHERE mrr.SR_INSTANCE_ID = '||MSC_CL_COLLECTION.v_instance_id
329: ||' AND mrr.PLAN_ID = -1'
330: ||' AND mrr.SR_INSTANCE_ID = mrc.SR_INSTANCE_ID'
331: ||' AND mrr.WIP_ENTITY_ID = mrc.WIP_ENTITY_ID'
332: ||' AND mrr.ORGANIZATION_ID = mrc.ORGANIZATION_ID'
376:
377: EXIT WHEN c4%NOTFOUND;
378:
379: BEGIN
380: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) Then
381: EXECUTE IMMEDIATE lv_sql_stmt
382: USING
383: lv_TRANSACTION_ID,
384: lv_SR_INSTANCE_ID,
385: lv_CHARGE_NUMBER,
386: lv_CHARGE_QUANTITY,
387: lv_CHARGE_START_DATETIME,
388: lv_CHARGE_END_DATETIME,
389: MSC_CL_COLLECTION.v_current_date,
390: MSC_CL_COLLECTION.v_current_user,
391: MSC_CL_COLLECTION.v_current_date,
392: MSC_CL_COLLECTION.v_current_user;
393: END IF;
386: lv_CHARGE_QUANTITY,
387: lv_CHARGE_START_DATETIME,
388: lv_CHARGE_END_DATETIME,
389: MSC_CL_COLLECTION.v_current_date,
390: MSC_CL_COLLECTION.v_current_user,
391: MSC_CL_COLLECTION.v_current_date,
392: MSC_CL_COLLECTION.v_current_user;
393: END IF;
394:
387: lv_CHARGE_START_DATETIME,
388: lv_CHARGE_END_DATETIME,
389: MSC_CL_COLLECTION.v_current_date,
390: MSC_CL_COLLECTION.v_current_user,
391: MSC_CL_COLLECTION.v_current_date,
392: MSC_CL_COLLECTION.v_current_user;
393: END IF;
394:
395: c_count:= c_count+1;
388: lv_CHARGE_END_DATETIME,
389: MSC_CL_COLLECTION.v_current_date,
390: MSC_CL_COLLECTION.v_current_user,
391: MSC_CL_COLLECTION.v_current_date,
392: MSC_CL_COLLECTION.v_current_user;
393: END IF;
394:
395: c_count:= c_count+1;
396: total_count:= total_count+1;
393: END IF;
394:
395: c_count:= c_count+1;
396: total_count:= total_count+1;
397: IF c_count> MSC_CL_COLLECTION.PBS THEN
398: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
399: COMMIT;
400: END IF;
401: c_count:= 0;
394:
395: c_count:= c_count+1;
396: total_count:= total_count+1;
397: IF c_count> MSC_CL_COLLECTION.PBS THEN
398: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
399: COMMIT;
400: END IF;
401: c_count:= 0;
402: END IF;
415: RAISE;
416:
417: ELSE
418:
419: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
420:
421: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
422: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
423: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE_CHARGES');
439:
440: END;
441: END LOOP;
442: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Total resource charges = '|| total_count);
443: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
444: COMMIT;
445: END IF;
446: EXCEPTION
447: WHEN OTHERS THEN
477: msric.ACTION_TYPE,
478: msric.DELETED_FLAG,
479: msric.SR_INSTANCE_ID
480: FROM MSC_ST_RES_INSTANCE_CHANGES msric
481: WHERE msric.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
482: ORDER BY
483: msric.DELETED_FLAG;
484:
485: c_count NUMBER:= 0;
490: lv_instance_code varchar2(10);
491:
492: Begin
493:
494: IF ((MSC_CL_COLLECTION.v_is_partial_refresh AND MSC_CL_COLLECTION.v_coll_prec.calendar_flag = MSC_UTIL.SYS_YES) OR
495: MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_incremental_refresh) then
496:
497: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
498:
491:
492: Begin
493:
494: IF ((MSC_CL_COLLECTION.v_is_partial_refresh AND MSC_CL_COLLECTION.v_coll_prec.calendar_flag = MSC_UTIL.SYS_YES) OR
495: MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_incremental_refresh) then
496:
497: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
498:
499: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RES_INSTANCE_CHANGES', MSC_CL_COLLECTION.v_instance_id, NULL);
493:
494: IF ((MSC_CL_COLLECTION.v_is_partial_refresh AND MSC_CL_COLLECTION.v_coll_prec.calendar_flag = MSC_UTIL.SYS_YES) OR
495: MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_incremental_refresh) then
496:
497: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
498:
499: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RES_INSTANCE_CHANGES', MSC_CL_COLLECTION.v_instance_id, NULL);
500:
501:
495: MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_incremental_refresh) then
496:
497: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
498:
499: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RES_INSTANCE_CHANGES', MSC_CL_COLLECTION.v_instance_id, NULL);
500:
501:
502: END IF;
503:
507: FOR c_rec IN res_inst_chngs LOOP
508:
509: BEGIN
510:
511: IF MSC_CL_COLLECTION.v_is_incremental_refresh AND c_rec.DELETED_FLAG= MSC_UTIL.SYS_YES THEN
512:
513: DELETE MSC_RES_INSTANCE_CHANGES
514: WHERE DEPARTMENT_ID = c_rec.DEPARTMENT_ID
515: AND RESOURCE_ID = c_rec.RESOURCE_ID
560: c_rec.CAPACITY_CHANGE,
561: c_rec.SIMULATION_SET,
562: c_rec.ACTION_TYPE,
563: c_rec.SR_INSTANCE_ID,
564: MSC_CL_COLLECTION.v_last_collection_id,
565: MSC_CL_COLLECTION.v_current_date,
566: MSC_CL_COLLECTION.v_current_user,
567: MSC_CL_COLLECTION.v_current_date,
568: MSC_CL_COLLECTION.v_current_user );
561: c_rec.SIMULATION_SET,
562: c_rec.ACTION_TYPE,
563: c_rec.SR_INSTANCE_ID,
564: MSC_CL_COLLECTION.v_last_collection_id,
565: MSC_CL_COLLECTION.v_current_date,
566: MSC_CL_COLLECTION.v_current_user,
567: MSC_CL_COLLECTION.v_current_date,
568: MSC_CL_COLLECTION.v_current_user );
569: END IF;
562: c_rec.ACTION_TYPE,
563: c_rec.SR_INSTANCE_ID,
564: MSC_CL_COLLECTION.v_last_collection_id,
565: MSC_CL_COLLECTION.v_current_date,
566: MSC_CL_COLLECTION.v_current_user,
567: MSC_CL_COLLECTION.v_current_date,
568: MSC_CL_COLLECTION.v_current_user );
569: END IF;
570:
563: c_rec.SR_INSTANCE_ID,
564: MSC_CL_COLLECTION.v_last_collection_id,
565: MSC_CL_COLLECTION.v_current_date,
566: MSC_CL_COLLECTION.v_current_user,
567: MSC_CL_COLLECTION.v_current_date,
568: MSC_CL_COLLECTION.v_current_user );
569: END IF;
570:
571: c_count:= c_count+1;
564: MSC_CL_COLLECTION.v_last_collection_id,
565: MSC_CL_COLLECTION.v_current_date,
566: MSC_CL_COLLECTION.v_current_user,
567: MSC_CL_COLLECTION.v_current_date,
568: MSC_CL_COLLECTION.v_current_user );
569: END IF;
570:
571: c_count:= c_count+1;
572: total_count:= total_count+1;
570:
571: c_count:= c_count+1;
572: total_count:= total_count+1;
573:
574: IF c_count> MSC_CL_COLLECTION.PBS THEN
575: COMMIT;
576: c_count:= 0;
577: END IF;
578:
590: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
591: RAISE;
592:
593: ELSE
594: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
595:
596: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
597: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
598: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RES_INST_CHANGE');
668: FROM MSC_ITEM_ID_LID t1,
669: MSC_ST_COMPONENT_SUBSTITUTES mscs
670: WHERE t1.SR_INVENTORY_ITEM_ID= mscs.substitute_item_id
671: AND t1.sr_instance_id= mscs.sr_instance_id
672: AND mscs.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
673: AND mscs.DELETED_FLAG= MSC_UTIL.SYS_NO;
674:
675: CURSOR c1_d IS
676: SELECT
682: MSC_ST_COMPONENT_SUBSTITUTES mscs
683: WHERE ((t1.SR_INVENTORY_ITEM_ID= mscs.substitute_item_id) AND (mscs.substitute_item_id
684: is NOT NULL))
685: AND t1.sr_instance_id= mscs.sr_instance_id
686: AND mscs.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
687: AND mscs.DELETED_FLAG= MSC_UTIL.SYS_YES
688: UNION ALL
689: SELECT
690: mscs.BILL_SEQUENCE_ID,
692: TO_NUMBER(NULL) SUBSTITUTE_ITEM_ID,
693: mscs.SR_INSTANCE_ID
694: FROM MSC_ST_COMPONENT_SUBSTITUTES mscs
695: WHERE mscs.substitute_item_id IS NULL
696: AND mscs.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
697: AND mscs.DELETED_FLAG= MSC_UTIL.SYS_YES;
698:
699:
700: c_count NUMBER:= 0;
702: lv_sql_stmt VARCHAR2(5000);
703:
704: BEGIN
705:
706: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
707: -- We want to delete all BOM related data and get new stuff.
708:
709: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_COMPONENT_SUBSTITUTES', MSC_CL_COLLECTION.v_instance_id, -1);
710:
705:
706: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
707: -- We want to delete all BOM related data and get new stuff.
708:
709: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_COMPONENT_SUBSTITUTES', MSC_CL_COLLECTION.v_instance_id, -1);
710:
711: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
712: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_COMPONENT_SUBSTITUTES', MSC_CL_COLLECTION.v_instance_id, -1);
713: ELSE
707: -- We want to delete all BOM related data and get new stuff.
708:
709: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_COMPONENT_SUBSTITUTES', MSC_CL_COLLECTION.v_instance_id, -1);
710:
711: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
712: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_COMPONENT_SUBSTITUTES', MSC_CL_COLLECTION.v_instance_id, -1);
713: ELSE
714: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
715: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_COMPONENT_SUBSTITUTES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
708:
709: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_COMPONENT_SUBSTITUTES', MSC_CL_COLLECTION.v_instance_id, -1);
710:
711: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
712: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_COMPONENT_SUBSTITUTES', MSC_CL_COLLECTION.v_instance_id, -1);
713: ELSE
714: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
715: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_COMPONENT_SUBSTITUTES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
716: END IF;
711: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
712: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_COMPONENT_SUBSTITUTES', MSC_CL_COLLECTION.v_instance_id, -1);
713: ELSE
714: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
715: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_COMPONENT_SUBSTITUTES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
716: END IF;
717:
718: END IF; -- MSC_CL_COLLECTION.v_is_complete_refresh
719:
714: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
715: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_COMPONENT_SUBSTITUTES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
716: END IF;
717:
718: END IF; -- MSC_CL_COLLECTION.v_is_complete_refresh
719:
720: c_count:= 0;
721:
722: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
718: END IF; -- MSC_CL_COLLECTION.v_is_complete_refresh
719:
720: c_count:= 0;
721:
722: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
723:
724: FOR c_rec IN c1_d LOOP
725:
726: IF c_rec.BILL_SEQUENCE_ID IS NOT NULL AND c_rec.COMPONENT_SEQUENCE_ID IS NULL AND c_rec.SUBSTITUTE_ITEM_ID IS NULL THEN
726: IF c_rec.BILL_SEQUENCE_ID IS NOT NULL AND c_rec.COMPONENT_SEQUENCE_ID IS NULL AND c_rec.SUBSTITUTE_ITEM_ID IS NULL THEN
727:
728: UPDATE MSC_COMPONENT_SUBSTITUTES
729: SET USAGE_QUANTITY= 0,
730: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
731: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
732: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
733: WHERE PLAN_ID= -1
734: AND BILL_SEQUENCE_ID= c_rec.BILL_SEQUENCE_ID
727:
728: UPDATE MSC_COMPONENT_SUBSTITUTES
729: SET USAGE_QUANTITY= 0,
730: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
731: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
732: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
733: WHERE PLAN_ID= -1
734: AND BILL_SEQUENCE_ID= c_rec.BILL_SEQUENCE_ID
735: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
728: UPDATE MSC_COMPONENT_SUBSTITUTES
729: SET USAGE_QUANTITY= 0,
730: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
731: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
732: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
733: WHERE PLAN_ID= -1
734: AND BILL_SEQUENCE_ID= c_rec.BILL_SEQUENCE_ID
735: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
736:
737: ELSIF c_rec.COMPONENT_SEQUENCE_ID IS NOT NULL AND c_rec.BILL_SEQUENCE_ID IS NULL AND c_rec.SUBSTITUTE_ITEM_ID IS NULL THEN
738:
739: UPDATE MSC_COMPONENT_SUBSTITUTES
740: SET USAGE_QUANTITY= 0,
741: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
742: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
743: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
744: WHERE PLAN_ID= -1
745: AND COMPONENT_SEQUENCE_ID= c_rec.COMPONENT_SEQUENCE_ID
738:
739: UPDATE MSC_COMPONENT_SUBSTITUTES
740: SET USAGE_QUANTITY= 0,
741: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
742: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
743: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
744: WHERE PLAN_ID= -1
745: AND COMPONENT_SEQUENCE_ID= c_rec.COMPONENT_SEQUENCE_ID
746: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
739: UPDATE MSC_COMPONENT_SUBSTITUTES
740: SET USAGE_QUANTITY= 0,
741: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
742: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
743: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
744: WHERE PLAN_ID= -1
745: AND COMPONENT_SEQUENCE_ID= c_rec.COMPONENT_SEQUENCE_ID
746: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
747:
749: AND c_rec.BILL_SEQUENCE_ID IS NULL THEN
750:
751: UPDATE MSC_COMPONENT_SUBSTITUTES
752: SET USAGE_QUANTITY= 0,
753: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
754: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
755: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
756: WHERE PLAN_ID= -1
757: AND COMPONENT_SEQUENCE_ID= c_rec.COMPONENT_SEQUENCE_ID
750:
751: UPDATE MSC_COMPONENT_SUBSTITUTES
752: SET USAGE_QUANTITY= 0,
753: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
754: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
755: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
756: WHERE PLAN_ID= -1
757: AND COMPONENT_SEQUENCE_ID= c_rec.COMPONENT_SEQUENCE_ID
758: AND SUBSTITUTE_ITEM_ID= c_rec.SUBSTITUTE_ITEM_ID
751: UPDATE MSC_COMPONENT_SUBSTITUTES
752: SET USAGE_QUANTITY= 0,
753: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
754: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
755: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
756: WHERE PLAN_ID= -1
757: AND COMPONENT_SEQUENCE_ID= c_rec.COMPONENT_SEQUENCE_ID
758: AND SUBSTITUTE_ITEM_ID= c_rec.SUBSTITUTE_ITEM_ID
759: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
761: END IF; /* c_rec combinations */
762:
763: END LOOP; /* c1_d */
764:
765: END IF; /* MSC_CL_COLLECTION.v_is_incremental_refresh */
766:
767: COMMIT;
768:
769: c_count:= 0;
771: FOR c_rec IN c3 LOOP
772:
773: BEGIN
774:
775: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
776:
777: UPDATE MSC_COMPONENT_SUBSTITUTES
778: SET
779: USAGE_QUANTITY= c_rec.USAGE_QUANTITY,
778: SET
779: USAGE_QUANTITY= c_rec.USAGE_QUANTITY,
780: PRIORITY= c_rec.PRIORITY,
781: ROUNDING_DIRECTION= c_rec.ROUNDING_DIRECTION,
782: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
783: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
784: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
785: WHERE PLAN_ID= -1
786: AND BILL_SEQUENCE_ID= c_rec.Bill_Sequence_ID
779: USAGE_QUANTITY= c_rec.USAGE_QUANTITY,
780: PRIORITY= c_rec.PRIORITY,
781: ROUNDING_DIRECTION= c_rec.ROUNDING_DIRECTION,
782: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
783: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
784: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
785: WHERE PLAN_ID= -1
786: AND BILL_SEQUENCE_ID= c_rec.Bill_Sequence_ID
787: AND COMPONENT_SEQUENCE_ID= c_rec.COMPONENT_SEQUENCE_ID
780: PRIORITY= c_rec.PRIORITY,
781: ROUNDING_DIRECTION= c_rec.ROUNDING_DIRECTION,
782: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
783: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
784: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
785: WHERE PLAN_ID= -1
786: AND BILL_SEQUENCE_ID= c_rec.Bill_Sequence_ID
787: AND COMPONENT_SEQUENCE_ID= c_rec.COMPONENT_SEQUENCE_ID
788: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
791:
792: END IF;
793:
794:
795: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
796:
797: INSERT INTO MSC_COMPONENT_SUBSTITUTES
798: ( PLAN_ID,
799: BILL_SEQUENCE_ID,
818: c_rec.ORGANIZATION_ID,
819: c_rec.SUBSTITUTE_ITEM_ID,
820: c_rec.COMPONENT_SEQUENCE_ID,
821: c_rec.SR_INSTANCE_ID,
822: MSC_CL_COLLECTION.v_last_collection_id,
823: MSC_CL_COLLECTION.v_current_date,
824: MSC_CL_COLLECTION.v_current_user,
825: MSC_CL_COLLECTION.v_current_date,
826: MSC_CL_COLLECTION.v_current_user );
819: c_rec.SUBSTITUTE_ITEM_ID,
820: c_rec.COMPONENT_SEQUENCE_ID,
821: c_rec.SR_INSTANCE_ID,
822: MSC_CL_COLLECTION.v_last_collection_id,
823: MSC_CL_COLLECTION.v_current_date,
824: MSC_CL_COLLECTION.v_current_user,
825: MSC_CL_COLLECTION.v_current_date,
826: MSC_CL_COLLECTION.v_current_user );
827:
820: c_rec.COMPONENT_SEQUENCE_ID,
821: c_rec.SR_INSTANCE_ID,
822: MSC_CL_COLLECTION.v_last_collection_id,
823: MSC_CL_COLLECTION.v_current_date,
824: MSC_CL_COLLECTION.v_current_user,
825: MSC_CL_COLLECTION.v_current_date,
826: MSC_CL_COLLECTION.v_current_user );
827:
828: END IF;
821: c_rec.SR_INSTANCE_ID,
822: MSC_CL_COLLECTION.v_last_collection_id,
823: MSC_CL_COLLECTION.v_current_date,
824: MSC_CL_COLLECTION.v_current_user,
825: MSC_CL_COLLECTION.v_current_date,
826: MSC_CL_COLLECTION.v_current_user );
827:
828: END IF;
829:
822: MSC_CL_COLLECTION.v_last_collection_id,
823: MSC_CL_COLLECTION.v_current_date,
824: MSC_CL_COLLECTION.v_current_user,
825: MSC_CL_COLLECTION.v_current_date,
826: MSC_CL_COLLECTION.v_current_user );
827:
828: END IF;
829:
830: c_count:= c_count+1;
828: END IF;
829:
830: c_count:= c_count+1;
831:
832: IF c_count> MSC_CL_COLLECTION.PBS THEN
833: COMMIT;
834: c_count:= 0;
835: END IF;
836:
837: EXCEPTION
838:
839: WHEN OTHERS THEN
840:
841: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
842:
843: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
844: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
845: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_COMPONENT_SUBSTITUTE');
864: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
865: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
866: FND_MESSAGE.SET_TOKEN('VALUE',
867: MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
868: MSC_CL_COLLECTION.v_instance_id));
869: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
870:
871: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
872:
889: msb.ROLLUP_START_DATE,
890: msb.ROLLUP_COMPLETION_DATE,
891: msb.SR_INSTANCE_ID
892: FROM MSC_ST_BILL_OF_RESOURCES msb
893: WHERE msb.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
894:
895: CURSOR c2 IS
896: SELECT
897: msbr.BILL_OF_RESOURCES,
917: WHERE t1.SR_INVENTORY_ITEM_ID= msbr.assembly_item_id
918: AND t1.sr_instance_id= msbr.sr_instance_id
919: AND t2.SR_INVENTORY_ITEM_ID= msbr.source_item_id
920: AND t2.sr_instance_id= msbr.sr_instance_id
921: AND msbr.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
922:
923: c_count NUMBER:= 0;
924:
925: BEGIN
923: c_count NUMBER:= 0;
924:
925: BEGIN
926:
927: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
928:
929: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BILL_OF_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1);
930: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOR_REQUIREMENTS', MSC_CL_COLLECTION.v_instance_id, -1);
931:
925: BEGIN
926:
927: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
928:
929: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BILL_OF_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1);
930: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOR_REQUIREMENTS', MSC_CL_COLLECTION.v_instance_id, -1);
931:
932: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
933: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BILL_OF_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1);
926:
927: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
928:
929: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BILL_OF_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1);
930: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOR_REQUIREMENTS', MSC_CL_COLLECTION.v_instance_id, -1);
931:
932: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
933: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BILL_OF_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1);
934: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOR_REQUIREMENTS', MSC_CL_COLLECTION.v_instance_id, -1);
928:
929: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BILL_OF_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1);
930: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOR_REQUIREMENTS', MSC_CL_COLLECTION.v_instance_id, -1);
931:
932: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
933: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BILL_OF_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1);
934: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOR_REQUIREMENTS', MSC_CL_COLLECTION.v_instance_id, -1);
935: ELSE
936: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
929: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BILL_OF_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1);
930: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOR_REQUIREMENTS', MSC_CL_COLLECTION.v_instance_id, -1);
931:
932: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
933: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BILL_OF_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1);
934: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOR_REQUIREMENTS', MSC_CL_COLLECTION.v_instance_id, -1);
935: ELSE
936: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
937: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BILL_OF_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
930: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOR_REQUIREMENTS', MSC_CL_COLLECTION.v_instance_id, -1);
931:
932: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
933: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BILL_OF_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1);
934: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOR_REQUIREMENTS', MSC_CL_COLLECTION.v_instance_id, -1);
935: ELSE
936: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
937: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BILL_OF_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
938: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOR_REQUIREMENTS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
933: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BILL_OF_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1);
934: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOR_REQUIREMENTS', MSC_CL_COLLECTION.v_instance_id, -1);
935: ELSE
936: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
937: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BILL_OF_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
938: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOR_REQUIREMENTS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
939: END IF;
940:
941: END IF; -- MSC_CL_COLLECTION.v_is_complete_refresh
934: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOR_REQUIREMENTS', MSC_CL_COLLECTION.v_instance_id, -1);
935: ELSE
936: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
937: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BILL_OF_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
938: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOR_REQUIREMENTS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
939: END IF;
940:
941: END IF; -- MSC_CL_COLLECTION.v_is_complete_refresh
942:
937: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BILL_OF_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
938: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOR_REQUIREMENTS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
939: END IF;
940:
941: END IF; -- MSC_CL_COLLECTION.v_is_complete_refresh
942:
943: c_count:= 0;
944:
945: FOR c_rec IN c1 LOOP
945: FOR c_rec IN c1 LOOP
946:
947: BEGIN
948:
949: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
950:
951: UPDATE MSC_BILL_OF_RESOURCES
952: SET
953: DESCRIPTION= c_rec.DESCRIPTION,
953: DESCRIPTION= c_rec.DESCRIPTION,
954: DISABLE_DATE= c_rec.DISABLE_DATE,
955: ROLLUP_START_DATE= c_rec.ROLLUP_START_DATE,
956: ROLLUP_COMPLETION_DATE= c_rec.ROLLUP_COMPLETION_DATE,
957: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
958: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
959: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
960: WHERE PLAN_ID= -1
961: AND BILL_OF_RESOURCES= c_rec.BILL_OF_RESOURCES
954: DISABLE_DATE= c_rec.DISABLE_DATE,
955: ROLLUP_START_DATE= c_rec.ROLLUP_START_DATE,
956: ROLLUP_COMPLETION_DATE= c_rec.ROLLUP_COMPLETION_DATE,
957: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
958: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
959: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
960: WHERE PLAN_ID= -1
961: AND BILL_OF_RESOURCES= c_rec.BILL_OF_RESOURCES
962: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
955: ROLLUP_START_DATE= c_rec.ROLLUP_START_DATE,
956: ROLLUP_COMPLETION_DATE= c_rec.ROLLUP_COMPLETION_DATE,
957: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
958: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
959: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
960: WHERE PLAN_ID= -1
961: AND BILL_OF_RESOURCES= c_rec.BILL_OF_RESOURCES
962: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
963: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
964:
965: END IF;
966:
967:
968: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
969:
970: INSERT INTO MSC_BILL_OF_RESOURCES
971: ( PLAN_ID,
972: BILL_OF_RESOURCES,
989: c_rec.DISABLE_DATE,
990: c_rec.ROLLUP_START_DATE,
991: c_rec.ROLLUP_COMPLETION_DATE,
992: c_rec.SR_INSTANCE_ID,
993: MSC_CL_COLLECTION.v_last_collection_id,
994: MSC_CL_COLLECTION.v_current_date,
995: MSC_CL_COLLECTION.v_current_user,
996: MSC_CL_COLLECTION.v_current_date,
997: MSC_CL_COLLECTION.v_current_user );
990: c_rec.ROLLUP_START_DATE,
991: c_rec.ROLLUP_COMPLETION_DATE,
992: c_rec.SR_INSTANCE_ID,
993: MSC_CL_COLLECTION.v_last_collection_id,
994: MSC_CL_COLLECTION.v_current_date,
995: MSC_CL_COLLECTION.v_current_user,
996: MSC_CL_COLLECTION.v_current_date,
997: MSC_CL_COLLECTION.v_current_user );
998:
991: c_rec.ROLLUP_COMPLETION_DATE,
992: c_rec.SR_INSTANCE_ID,
993: MSC_CL_COLLECTION.v_last_collection_id,
994: MSC_CL_COLLECTION.v_current_date,
995: MSC_CL_COLLECTION.v_current_user,
996: MSC_CL_COLLECTION.v_current_date,
997: MSC_CL_COLLECTION.v_current_user );
998:
999: END IF;
992: c_rec.SR_INSTANCE_ID,
993: MSC_CL_COLLECTION.v_last_collection_id,
994: MSC_CL_COLLECTION.v_current_date,
995: MSC_CL_COLLECTION.v_current_user,
996: MSC_CL_COLLECTION.v_current_date,
997: MSC_CL_COLLECTION.v_current_user );
998:
999: END IF;
1000:
993: MSC_CL_COLLECTION.v_last_collection_id,
994: MSC_CL_COLLECTION.v_current_date,
995: MSC_CL_COLLECTION.v_current_user,
996: MSC_CL_COLLECTION.v_current_date,
997: MSC_CL_COLLECTION.v_current_user );
998:
999: END IF;
1000:
1001: c_count:= c_count+1;
999: END IF;
1000:
1001: c_count:= c_count+1;
1002:
1003: IF c_count> MSC_CL_COLLECTION.PBS THEN
1004: COMMIT;
1005: c_count:= 0;
1006: END IF;
1007:
1019: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1020: RAISE;
1021:
1022: ELSE
1023: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1024:
1025: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1026: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1027: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOR');
1031: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1032: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
1033: FND_MESSAGE.SET_TOKEN('VALUE',
1034: MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
1035: MSC_CL_COLLECTION.v_instance_id));
1036: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1037:
1038: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1039: FND_MESSAGE.SET_TOKEN('COLUMN', 'BILL_OF_RESOURCES');
1054: FOR c_rec IN c2 LOOP
1055:
1056: BEGIN
1057:
1058: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
1059:
1060: UPDATE MSC_BOR_REQUIREMENTS
1061: SET
1062: BILL_OF_RESOURCES= c_rec.BILL_OF_RESOURCES,
1073: DEPARTMENT_ID= c_rec.DEPARTMENT_ID,
1074: ORGANIZATION_ID= c_rec.ORGANIZATION_ID,
1075: ASSEMBLY_ITEM_ID= c_rec.ASSEMBLY_ITEM_ID,
1076: SOURCE_ITEM_ID= c_rec.SOURCE_ITEM_ID,
1077: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1078: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1079: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1080: WHERE PLAN_ID= -1
1081: AND SR_TRANSACTION_ID= c_rec.SR_TRANSACTION_ID
1074: ORGANIZATION_ID= c_rec.ORGANIZATION_ID,
1075: ASSEMBLY_ITEM_ID= c_rec.ASSEMBLY_ITEM_ID,
1076: SOURCE_ITEM_ID= c_rec.SOURCE_ITEM_ID,
1077: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1078: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1079: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1080: WHERE PLAN_ID= -1
1081: AND SR_TRANSACTION_ID= c_rec.SR_TRANSACTION_ID
1082: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
1075: ASSEMBLY_ITEM_ID= c_rec.ASSEMBLY_ITEM_ID,
1076: SOURCE_ITEM_ID= c_rec.SOURCE_ITEM_ID,
1077: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1078: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1079: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1080: WHERE PLAN_ID= -1
1081: AND SR_TRANSACTION_ID= c_rec.SR_TRANSACTION_ID
1082: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
1083:
1083:
1084: END IF;
1085:
1086:
1087: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
1088:
1089: INSERT INTO MSC_BOR_REQUIREMENTS
1090: ( PLAN_ID,
1091: TRANSACTION_ID,
1130: c_rec.ORGANIZATION_ID,
1131: c_rec.ASSEMBLY_ITEM_ID,
1132: c_rec.SOURCE_ITEM_ID,
1133: c_rec.SR_INSTANCE_ID,
1134: MSC_CL_COLLECTION.v_last_collection_id,
1135: MSC_CL_COLLECTION.v_current_date,
1136: MSC_CL_COLLECTION.v_current_user,
1137: MSC_CL_COLLECTION.v_current_date,
1138: MSC_CL_COLLECTION.v_current_user );
1131: c_rec.ASSEMBLY_ITEM_ID,
1132: c_rec.SOURCE_ITEM_ID,
1133: c_rec.SR_INSTANCE_ID,
1134: MSC_CL_COLLECTION.v_last_collection_id,
1135: MSC_CL_COLLECTION.v_current_date,
1136: MSC_CL_COLLECTION.v_current_user,
1137: MSC_CL_COLLECTION.v_current_date,
1138: MSC_CL_COLLECTION.v_current_user );
1139:
1132: c_rec.SOURCE_ITEM_ID,
1133: c_rec.SR_INSTANCE_ID,
1134: MSC_CL_COLLECTION.v_last_collection_id,
1135: MSC_CL_COLLECTION.v_current_date,
1136: MSC_CL_COLLECTION.v_current_user,
1137: MSC_CL_COLLECTION.v_current_date,
1138: MSC_CL_COLLECTION.v_current_user );
1139:
1140: END IF;
1133: c_rec.SR_INSTANCE_ID,
1134: MSC_CL_COLLECTION.v_last_collection_id,
1135: MSC_CL_COLLECTION.v_current_date,
1136: MSC_CL_COLLECTION.v_current_user,
1137: MSC_CL_COLLECTION.v_current_date,
1138: MSC_CL_COLLECTION.v_current_user );
1139:
1140: END IF;
1141:
1134: MSC_CL_COLLECTION.v_last_collection_id,
1135: MSC_CL_COLLECTION.v_current_date,
1136: MSC_CL_COLLECTION.v_current_user,
1137: MSC_CL_COLLECTION.v_current_date,
1138: MSC_CL_COLLECTION.v_current_user );
1139:
1140: END IF;
1141:
1142: c_count:= c_count+1;
1140: END IF;
1141:
1142: c_count:= c_count+1;
1143:
1144: IF c_count> MSC_CL_COLLECTION.PBS THEN
1145: COMMIT;
1146: c_count:= 0;
1147: END IF;
1148:
1161: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1162: RAISE;
1163:
1164: ELSE
1165: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1166:
1167: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1168: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1169: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOR');
1219: msor.RECIPE
1220: FROM MSC_ITEM_ID_LID miil,
1221: MSC_ST_PROCESS_EFFECTIVITY msor
1222: WHERE miil.SR_INVENTORY_ITEM_ID= msor.ITEM_ID
1223: AND miil.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1224: AND msor.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1225: AND msor.DELETED_FLAG= MSC_UTIL.SYS_NO;
1226:
1227: CURSOR c8_d IS
1220: FROM MSC_ITEM_ID_LID miil,
1221: MSC_ST_PROCESS_EFFECTIVITY msor
1222: WHERE miil.SR_INVENTORY_ITEM_ID= msor.ITEM_ID
1223: AND miil.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1224: AND msor.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1225: AND msor.DELETED_FLAG= MSC_UTIL.SYS_NO;
1226:
1227: CURSOR c8_d IS
1228: SELECT
1234: msor.SR_INSTANCE_ID
1235: FROM MSC_ITEM_ID_LID miil,
1236: MSC_ST_PROCESS_EFFECTIVITY msor
1237: WHERE miil.SR_INVENTORY_ITEM_ID(+)= msor.ITEM_ID
1238: AND miil.SR_INSTANCE_ID(+)= MSC_CL_COLLECTION.v_instance_id
1239: AND msor.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1240: AND msor.DELETED_FLAG= MSC_UTIL.SYS_YES
1241: UNION ALL
1242: SELECT
1235: FROM MSC_ITEM_ID_LID miil,
1236: MSC_ST_PROCESS_EFFECTIVITY msor
1237: WHERE miil.SR_INVENTORY_ITEM_ID(+)= msor.ITEM_ID
1238: AND miil.SR_INSTANCE_ID(+)= MSC_CL_COLLECTION.v_instance_id
1239: AND msor.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1240: AND msor.DELETED_FLAG= MSC_UTIL.SYS_YES
1241: UNION ALL
1242: SELECT
1243: TO_NUMBER(NULL),
1249: FROM MSC_PROCESS_EFFECTIVITY mpe,
1250: MSC_ST_PROCESS_EFFECTIVITY mspe
1251: WHERE mspe.Bill_Sequence_ID= mpe.Bill_Sequence_ID
1252: AND mspe.Routing_Sequence_ID <> mpe.Routing_Sequence_ID
1253: AND mspe.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1254: AND mpe.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1255: AND mspe.DELETED_FLAG= MSC_UTIL.SYS_NO
1256: AND mpe.PLAN_ID= -1;
1257:
1250: MSC_ST_PROCESS_EFFECTIVITY mspe
1251: WHERE mspe.Bill_Sequence_ID= mpe.Bill_Sequence_ID
1252: AND mspe.Routing_Sequence_ID <> mpe.Routing_Sequence_ID
1253: AND mspe.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1254: AND mpe.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1255: AND mspe.DELETED_FLAG= MSC_UTIL.SYS_NO
1256: AND mpe.PLAN_ID= -1;
1257:
1258: CURSOR c_del_leg IS
1262: t1.INVENTORY_ITEM_ID item_id,
1263: msr.organization_id
1264: FROM MSC_ST_ROUTINGS msr,
1265: msc_item_id_lid t1
1266: WHERE msr.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1267: AND msr.assembly_item_id = t1.SR_INVENTORY_ITEM_ID(+)
1268: AND msr.sr_instance_id = t1.sr_instance_id(+)
1269: UNION ALL
1270: SELECT
1273: t1.INVENTORY_ITEM_ID item_id,
1274: msb.organization_id
1275: FROM MSC_ST_BOMS msb,
1276: msc_item_id_lid t1
1277: WHERE msb.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1278: AND msb.assembly_item_id = t1.SR_INVENTORY_ITEM_ID(+)
1279: AND msb.sr_instance_id = t1.sr_instance_id(+) ;
1280:
1281: c_count NUMBER:= 0;
1286: lv_val_2 NUMBER;
1287: lv_val_3 NUMBER;
1288:
1289: BEGIN
1290: IF MSC_CL_COLLECTION.v_instance_type = MSC_UTIL.G_INS_OTHER THEN
1291:
1292: -- deleting if the record already existed.
1293: FOR c_rec IN c_del_leg LOOP
1294:
1293: FOR c_rec IN c_del_leg LOOP
1294:
1295: DELETE MSC_PROCESS_EFFECTIVITY
1296: WHERE PLAN_ID= -1
1297: AND SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1298: AND ( ROUTING_SEQUENCE_ID = c_rec.ROUTING_SEQUENCE_ID
1299: OR BILL_SEQUENCE_ID= c_rec.BILL_SEQUENCE_ID )
1300: AND ITEM_ID = c_rec.ITEM_ID
1301: AND ORGANIZATION_ID = c_rec.ORGANIZATION_ID;
1304:
1305: ELSE
1306:
1307:
1308: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
1309:
1310: FOR c_rec IN c8_d LOOP
1311: ---5470477
1312: lv_sql_stmt1 := ' DELETE MSC_PROCESS_EFFECTIVITY '
1383: END LOOP;
1384:
1385: END IF;
1386:
1387: END IF; -- v_instance_type = MSC_CL_COLLECTION.G_INS_OTHER
1388:
1389: c_count:= 0;
1390:
1391: FOR c_rec IN c8 LOOP
1405: LOAD_DISTRIBUTION_PRIORITY= c_rec.LOAD_DISTRIBUTION_PRIORITY,
1406: ITEM_PROCESS_COST= c_rec.ITEM_PROCESS_COST,
1407: EFFECTIVITY_DATE= c_rec.EFFECTIVITY_DATE,
1408: DISABLE_DATE= c_rec.DISABLE_DATE,
1409: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1410: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1411: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
1412: RECIPE = c_rec.RECIPE
1413: WHERE PLAN_ID= -1
1406: ITEM_PROCESS_COST= c_rec.ITEM_PROCESS_COST,
1407: EFFECTIVITY_DATE= c_rec.EFFECTIVITY_DATE,
1408: DISABLE_DATE= c_rec.DISABLE_DATE,
1409: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1410: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1411: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
1412: RECIPE = c_rec.RECIPE
1413: WHERE PLAN_ID= -1
1414: AND ITEM_ID= c_rec.INVENTORY_ITEM_ID
1407: EFFECTIVITY_DATE= c_rec.EFFECTIVITY_DATE,
1408: DISABLE_DATE= c_rec.DISABLE_DATE,
1409: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1410: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1411: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
1412: RECIPE = c_rec.RECIPE
1413: WHERE PLAN_ID= -1
1414: AND ITEM_ID= c_rec.INVENTORY_ITEM_ID
1415: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
1470: c_rec.LOAD_DISTRIBUTION_PRIORITY,
1471: c_rec.ITEM_PROCESS_COST,
1472: c_rec.RECIPE,
1473: c_rec.SR_INSTANCE_ID,
1474: MSC_CL_COLLECTION.v_last_collection_id,
1475: MSC_CL_COLLECTION.v_current_date,
1476: MSC_CL_COLLECTION.v_current_user,
1477: MSC_CL_COLLECTION.v_current_date,
1478: MSC_CL_COLLECTION.v_current_user );
1471: c_rec.ITEM_PROCESS_COST,
1472: c_rec.RECIPE,
1473: c_rec.SR_INSTANCE_ID,
1474: MSC_CL_COLLECTION.v_last_collection_id,
1475: MSC_CL_COLLECTION.v_current_date,
1476: MSC_CL_COLLECTION.v_current_user,
1477: MSC_CL_COLLECTION.v_current_date,
1478: MSC_CL_COLLECTION.v_current_user );
1479:
1472: c_rec.RECIPE,
1473: c_rec.SR_INSTANCE_ID,
1474: MSC_CL_COLLECTION.v_last_collection_id,
1475: MSC_CL_COLLECTION.v_current_date,
1476: MSC_CL_COLLECTION.v_current_user,
1477: MSC_CL_COLLECTION.v_current_date,
1478: MSC_CL_COLLECTION.v_current_user );
1479:
1480: END IF; -- SQL%NOTFOUND
1473: c_rec.SR_INSTANCE_ID,
1474: MSC_CL_COLLECTION.v_last_collection_id,
1475: MSC_CL_COLLECTION.v_current_date,
1476: MSC_CL_COLLECTION.v_current_user,
1477: MSC_CL_COLLECTION.v_current_date,
1478: MSC_CL_COLLECTION.v_current_user );
1479:
1480: END IF; -- SQL%NOTFOUND
1481:
1474: MSC_CL_COLLECTION.v_last_collection_id,
1475: MSC_CL_COLLECTION.v_current_date,
1476: MSC_CL_COLLECTION.v_current_user,
1477: MSC_CL_COLLECTION.v_current_date,
1478: MSC_CL_COLLECTION.v_current_user );
1479:
1480: END IF; -- SQL%NOTFOUND
1481:
1482: c_count:= c_count+1;
1480: END IF; -- SQL%NOTFOUND
1481:
1482: c_count:= c_count+1;
1483:
1484: IF c_count> MSC_CL_COLLECTION.PBS THEN
1485: COMMIT;
1486: c_count:= 0;
1487: END IF;
1488:
1500: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1501: RAISE;
1502:
1503: ELSE
1504: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1505:
1506: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1507: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1508: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_PROCESS_EFFECTIVITY');
1517: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1518: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
1519: FND_MESSAGE.SET_TOKEN('VALUE',
1520: MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
1521: MSC_CL_COLLECTION.v_instance_id));
1522: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1523:
1524: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1525: FND_MESSAGE.SET_TOKEN('COLUMN', 'BILL_SEQUENCE_ID');
1538: END LOOP;
1539:
1540: /* bug 1244578 fix */
1541: /* if it's complete refresh, delete the old records after the insert/update */
1542: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
1543: -- We want to delete all BOM related data and get new stuff.
1544:
1545: /* DELETE MSC_PROCESS_EFFECTIVITY
1546: WHERE PLAN_ID= -1
1543: -- We want to delete all BOM related data and get new stuff.
1544:
1545: /* DELETE MSC_PROCESS_EFFECTIVITY
1546: WHERE PLAN_ID= -1
1547: AND SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1548: AND REFRESH_NUMBER <> MSC_CL_COLLECTION.v_last_collection_id; */
1549:
1550: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1551: v_sub_str :='AND REFRESH_NUMBER <>'||MSC_CL_COLLECTION.v_last_collection_id;
1544:
1545: /* DELETE MSC_PROCESS_EFFECTIVITY
1546: WHERE PLAN_ID= -1
1547: AND SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1548: AND REFRESH_NUMBER <> MSC_CL_COLLECTION.v_last_collection_id; */
1549:
1550: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1551: v_sub_str :='AND REFRESH_NUMBER <>'||MSC_CL_COLLECTION.v_last_collection_id;
1552: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_PROCESS_EFFECTIVITY', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
1546: WHERE PLAN_ID= -1
1547: AND SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1548: AND REFRESH_NUMBER <> MSC_CL_COLLECTION.v_last_collection_id; */
1549:
1550: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1551: v_sub_str :='AND REFRESH_NUMBER <>'||MSC_CL_COLLECTION.v_last_collection_id;
1552: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_PROCESS_EFFECTIVITY', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
1553: ELSE
1554: v_sub_str :=' AND ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1547: AND SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1548: AND REFRESH_NUMBER <> MSC_CL_COLLECTION.v_last_collection_id; */
1549:
1550: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1551: v_sub_str :='AND REFRESH_NUMBER <>'||MSC_CL_COLLECTION.v_last_collection_id;
1552: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_PROCESS_EFFECTIVITY', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
1553: ELSE
1554: v_sub_str :=' AND ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1555: ||' AND REFRESH_NUMBER <>'||MSC_CL_COLLECTION.v_last_collection_id;
1548: AND REFRESH_NUMBER <> MSC_CL_COLLECTION.v_last_collection_id; */
1549:
1550: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1551: v_sub_str :='AND REFRESH_NUMBER <>'||MSC_CL_COLLECTION.v_last_collection_id;
1552: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_PROCESS_EFFECTIVITY', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
1553: ELSE
1554: v_sub_str :=' AND ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1555: ||' AND REFRESH_NUMBER <>'||MSC_CL_COLLECTION.v_last_collection_id;
1556: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_PROCESS_EFFECTIVITY', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
1551: v_sub_str :='AND REFRESH_NUMBER <>'||MSC_CL_COLLECTION.v_last_collection_id;
1552: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_PROCESS_EFFECTIVITY', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
1553: ELSE
1554: v_sub_str :=' AND ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1555: ||' AND REFRESH_NUMBER <>'||MSC_CL_COLLECTION.v_last_collection_id;
1556: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_PROCESS_EFFECTIVITY', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
1557: END IF;
1558:
1559:
1552: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_PROCESS_EFFECTIVITY', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
1553: ELSE
1554: v_sub_str :=' AND ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1555: ||' AND REFRESH_NUMBER <>'||MSC_CL_COLLECTION.v_last_collection_id;
1556: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_PROCESS_EFFECTIVITY', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
1557: END IF;
1558:
1559:
1560:
1584: FROM MSC_ITEM_ID_LID t1,
1585: MSC_ST_BOMS msb
1586: WHERE t1.SR_INVENTORY_ITEM_ID= msb.assembly_item_id
1587: AND t1.sr_instance_id= msb.sr_instance_id
1588: AND msb.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1589: AND msb.DELETED_FLAG= MSC_UTIL.SYS_NO;
1590:
1591: CURSOR c2_d IS
1592: SELECT
1592: SELECT
1593: msb.BILL_SEQUENCE_ID,
1594: msb.SR_INSTANCE_ID
1595: FROM MSC_ST_BOMS msb
1596: WHERE msb.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1597: AND msb.DELETED_FLAG= MSC_UTIL.SYS_YES;
1598:
1599:
1600: lv_tbl VARCHAR2(30);
1668: ||' :v_current_user,'
1669: ||' :v_current_date,'
1670: ||' :v_current_user )';
1671:
1672: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
1673:
1674: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1675: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOMS', MSC_CL_COLLECTION.v_instance_id, -1);
1676: ELSE
1670: ||' :v_current_user )';
1671:
1672: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
1673:
1674: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1675: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOMS', MSC_CL_COLLECTION.v_instance_id, -1);
1676: ELSE
1677: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1678: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOMS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
1671:
1672: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
1673:
1674: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1675: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOMS', MSC_CL_COLLECTION.v_instance_id, -1);
1676: ELSE
1677: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1678: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOMS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
1679: END IF;
1674: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1675: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOMS', MSC_CL_COLLECTION.v_instance_id, -1);
1676: ELSE
1677: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1678: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOMS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
1679: END IF;
1680:
1681: BEGIN
1682: lv_sql_ins :=
1721: ||' FROM MSC_ITEM_ID_LID t1,'
1722: ||' MSC_ST_BOMS msb '
1723: ||' WHERE t1.SR_INVENTORY_ITEM_ID= msb.assembly_item_id'
1724: ||' AND t1.sr_instance_id= msb.sr_instance_id'
1725: ||' AND msb.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id
1726: ||' AND msb.DELETED_FLAG= '||MSC_UTIL.SYS_NO;
1727:
1728: EXECUTE IMMEDIATE lv_sql_ins
1729: 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;
1725: ||' AND msb.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id
1726: ||' AND msb.DELETED_FLAG= '||MSC_UTIL.SYS_NO;
1727:
1728: EXECUTE IMMEDIATE lv_sql_ins
1729: 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;
1730:
1731: commit;
1732: EXCEPTION
1733: WHEN OTHERS THEN
1757: lb_refresh_failed := TRUE;
1758: END IF;
1759: END;
1760:
1761: END IF; -- MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh
1762:
1763:
1764:
1765: --Incremental Refresh or the above complete refresh or partial refresh has
1763:
1764:
1765: --Incremental Refresh or the above complete refresh or partial refresh has
1766: --failed.
1767: IF MSC_CL_COLLECTION.v_is_incremental_refresh OR lb_refresh_failed THEN
1768:
1769: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
1770: FOR c_rec IN c2_d LOOP
1771: DELETE MSC_BOMS
1765: --Incremental Refresh or the above complete refresh or partial refresh has
1766: --failed.
1767: IF MSC_CL_COLLECTION.v_is_incremental_refresh OR lb_refresh_failed THEN
1768:
1769: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
1770: FOR c_rec IN c2_d LOOP
1771: DELETE MSC_BOMS
1772: WHERE PLAN_ID= -1
1773: AND BILL_SEQUENCE_ID= c_rec.BILL_SEQUENCE_ID
1821:
1822: FOR j IN 1..lb_ASSEMBLY_TYPE.COUNT LOOP
1823:
1824: BEGIN
1825: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
1826:
1827: UPDATE MSC_BOMS
1828: SET
1829: ASSEMBLY_TYPE= lb_ASSEMBLY_TYPE(j),
1834: ASSEMBLY_QUANTITY= lb_ASSEMBLY_QUANTITY(j),
1835: UOM= lb_UOM(j),
1836: ORGANIZATION_ID= lb_ORGANIZATION_ID(j),
1837: ASSEMBLY_ITEM_ID= lb_ASSEMBLY_ITEM_ID(j),
1838: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1839: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1840: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1841: WHERE PLAN_ID= -1
1842: AND BILL_SEQUENCE_ID= lb_BILL_SEQUENCE_ID(j)
1835: UOM= lb_UOM(j),
1836: ORGANIZATION_ID= lb_ORGANIZATION_ID(j),
1837: ASSEMBLY_ITEM_ID= lb_ASSEMBLY_ITEM_ID(j),
1838: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1839: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1840: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1841: WHERE PLAN_ID= -1
1842: AND BILL_SEQUENCE_ID= lb_BILL_SEQUENCE_ID(j)
1843: AND SR_INSTANCE_ID= lb_SR_INSTANCE_ID(j);
1836: ORGANIZATION_ID= lb_ORGANIZATION_ID(j),
1837: ASSEMBLY_ITEM_ID= lb_ASSEMBLY_ITEM_ID(j),
1838: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1839: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1840: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1841: WHERE PLAN_ID= -1
1842: AND BILL_SEQUENCE_ID= lb_BILL_SEQUENCE_ID(j)
1843: AND SR_INSTANCE_ID= lb_SR_INSTANCE_ID(j);
1844:
1843: AND SR_INSTANCE_ID= lb_SR_INSTANCE_ID(j);
1844:
1845: END IF;
1846:
1847: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
1848:
1849: EXECUTE IMMEDIATE lv_sql_stmt
1850: USING
1851: lb_ASSEMBLY_TYPE(j),
1859: lb_ASSEMBLY_ITEM_ID(j),
1860: lb_BILL_SEQUENCE_ID(j),
1861: lb_SR_INSTANCE_ID(j),
1862: lb_operation_seq_num(j),
1863: MSC_CL_COLLECTION.v_last_collection_id,
1864: MSC_CL_COLLECTION.v_current_date,
1865: MSC_CL_COLLECTION.v_current_user,
1866: MSC_CL_COLLECTION.v_current_date,
1867: MSC_CL_COLLECTION.v_current_user;
1860: lb_BILL_SEQUENCE_ID(j),
1861: lb_SR_INSTANCE_ID(j),
1862: lb_operation_seq_num(j),
1863: MSC_CL_COLLECTION.v_last_collection_id,
1864: MSC_CL_COLLECTION.v_current_date,
1865: MSC_CL_COLLECTION.v_current_user,
1866: MSC_CL_COLLECTION.v_current_date,
1867: MSC_CL_COLLECTION.v_current_user;
1868: END IF; -- SQL%NOTFOUND
1861: lb_SR_INSTANCE_ID(j),
1862: lb_operation_seq_num(j),
1863: MSC_CL_COLLECTION.v_last_collection_id,
1864: MSC_CL_COLLECTION.v_current_date,
1865: MSC_CL_COLLECTION.v_current_user,
1866: MSC_CL_COLLECTION.v_current_date,
1867: MSC_CL_COLLECTION.v_current_user;
1868: END IF; -- SQL%NOTFOUND
1869:
1862: lb_operation_seq_num(j),
1863: MSC_CL_COLLECTION.v_last_collection_id,
1864: MSC_CL_COLLECTION.v_current_date,
1865: MSC_CL_COLLECTION.v_current_user,
1866: MSC_CL_COLLECTION.v_current_date,
1867: MSC_CL_COLLECTION.v_current_user;
1868: END IF; -- SQL%NOTFOUND
1869:
1870: c_count:= c_count+1;
1863: MSC_CL_COLLECTION.v_last_collection_id,
1864: MSC_CL_COLLECTION.v_current_date,
1865: MSC_CL_COLLECTION.v_current_user,
1866: MSC_CL_COLLECTION.v_current_date,
1867: MSC_CL_COLLECTION.v_current_user;
1868: END IF; -- SQL%NOTFOUND
1869:
1870: c_count:= c_count+1;
1871:
1868: END IF; -- SQL%NOTFOUND
1869:
1870: c_count:= c_count+1;
1871:
1872: IF c_count> MSC_CL_COLLECTION.PBS THEN
1873: COMMIT;
1874: c_count:= 0;
1875: END IF;
1876:
1888: RAISE;
1889:
1890: ELSE
1891:
1892: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1893:
1894: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1895: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1896: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOM');
1967: AND t2.SR_INVENTORY_ITEM_ID= msbc.using_assembly_id
1968: AND t2.sr_instance_id= msbc.sr_instance_id
1969: AND t3.SR_INVENTORY_ITEM_ID(+)= msbc.driving_item_id
1970: AND t3.sr_instance_id(+)= msbc.sr_instance_id
1971: AND msbc.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1972: AND msbc.DELETED_FLAG= MSC_UTIL.SYS_NO;
1973:
1974: CURSOR c1_d IS
1975: SELECT
1976: msbc.COMPONENT_SEQUENCE_ID,
1977: msbc.BILL_SEQUENCE_ID,
1978: msbc.SR_INSTANCE_ID
1979: FROM MSC_ST_BOM_COMPONENTS msbc
1980: WHERE msbc.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1981: AND msbc.DELETED_FLAG= MSC_UTIL.SYS_YES;
1982:
1983:
1984: lv_tbl VARCHAR2(30);
2034:
2035: BEGIN
2036:
2037:
2038: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
2039: lv_tbl:= 'BOM_COMPONENTS_'||MSC_CL_COLLECTION.v_instance_code;
2040: ELSE
2041: lv_tbl:= 'MSC_BOM_COMPONENTS';
2042: END IF;
2035: BEGIN
2036:
2037:
2038: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
2039: lv_tbl:= 'BOM_COMPONENTS_'||MSC_CL_COLLECTION.v_instance_code;
2040: ELSE
2041: lv_tbl:= 'MSC_BOM_COMPONENTS';
2042: END IF;
2043:
2123: ||' :v_current_date,'
2124: ||' :v_current_user)';
2125:
2126: --fix for the bug#3283959
2127: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
2128:
2129: if (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO) then
2130: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOM_COMPONENTS', MSC_CL_COLLECTION.v_instance_id, -1);
2131: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2125:
2126: --fix for the bug#3283959
2127: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
2128:
2129: if (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO) then
2130: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOM_COMPONENTS', MSC_CL_COLLECTION.v_instance_id, -1);
2131: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2132: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOM_COMPONENTS', MSC_CL_COLLECTION.v_instance_id, -1);
2133: ELSE
2126: --fix for the bug#3283959
2127: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
2128:
2129: if (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO) then
2130: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOM_COMPONENTS', MSC_CL_COLLECTION.v_instance_id, -1);
2131: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2132: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOM_COMPONENTS', MSC_CL_COLLECTION.v_instance_id, -1);
2133: ELSE
2134: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2127: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
2128:
2129: if (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO) then
2130: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOM_COMPONENTS', MSC_CL_COLLECTION.v_instance_id, -1);
2131: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2132: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOM_COMPONENTS', MSC_CL_COLLECTION.v_instance_id, -1);
2133: ELSE
2134: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2135: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOM_COMPONENTS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
2128:
2129: if (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO) then
2130: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOM_COMPONENTS', MSC_CL_COLLECTION.v_instance_id, -1);
2131: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2132: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOM_COMPONENTS', MSC_CL_COLLECTION.v_instance_id, -1);
2133: ELSE
2134: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2135: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOM_COMPONENTS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
2136: END IF;
2131: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2132: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOM_COMPONENTS', MSC_CL_COLLECTION.v_instance_id, -1);
2133: ELSE
2134: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2135: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOM_COMPONENTS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
2136: END IF;
2137: end if;
2138:
2139: BEGIN
2228: ||' AND t2.SR_INVENTORY_ITEM_ID = msbc.using_assembly_id'
2229: ||' AND t2.sr_instance_id = msbc.sr_instance_id'
2230: ||' AND t3.SR_INVENTORY_ITEM_ID(+) = msbc.driving_item_id'
2231: ||' AND t3.sr_instance_id(+) = msbc.sr_instance_id'
2232: ||' AND msbc.SR_INSTANCE_ID = '||MSC_CL_COLLECTION.v_instance_id
2233: ||' AND msbc.DELETED_FLAG = '||MSC_UTIL.SYS_NO;
2234:
2235: EXECUTE IMMEDIATE lv_sql_ins
2236: 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;
2232: ||' AND msbc.SR_INSTANCE_ID = '||MSC_CL_COLLECTION.v_instance_id
2233: ||' AND msbc.DELETED_FLAG = '||MSC_UTIL.SYS_NO;
2234:
2235: EXECUTE IMMEDIATE lv_sql_ins
2236: 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;
2237:
2238: commit;
2239: EXCEPTION
2240: WHEN OTHERS THEN
2264: lb_refresh_failed := TRUE;
2265: END IF;
2266: END;
2267:
2268: END IF; --MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh
2269:
2270: --Incremental Refresh or the above complete refresh or partial refresh has
2271: --failed.
2272: IF MSC_CL_COLLECTION.v_is_incremental_refresh OR lb_refresh_failed THEN
2268: END IF; --MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh
2269:
2270: --Incremental Refresh or the above complete refresh or partial refresh has
2271: --failed.
2272: IF MSC_CL_COLLECTION.v_is_incremental_refresh OR lb_refresh_failed THEN
2273:
2274: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
2275:
2276: -- set SR_INSTANCE_ID to negative to indicate a SOFT delete
2270: --Incremental Refresh or the above complete refresh or partial refresh has
2271: --failed.
2272: IF MSC_CL_COLLECTION.v_is_incremental_refresh OR lb_refresh_failed THEN
2273:
2274: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
2275:
2276: -- set SR_INSTANCE_ID to negative to indicate a SOFT delete
2277:
2278: FOR c_rec IN c1_d LOOP
2294: END IF;
2295: BEGIN
2296: IF (c_rec.BILL_SEQUENCE_ID IS NOT NULL) THEN
2297: IF (c_rec.COMPONENT_SEQUENCE_ID IS NOT NULL) THEN
2298: 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, c_rec.BILL_SEQUENCE_ID , c_rec.COMPONENT_SEQUENCE_ID ;
2299: ELSE
2300: 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, c_rec.BILL_SEQUENCE_ID;
2301: END IF;
2302: ELSE
2296: IF (c_rec.BILL_SEQUENCE_ID IS NOT NULL) THEN
2297: IF (c_rec.COMPONENT_SEQUENCE_ID IS NOT NULL) THEN
2298: 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, c_rec.BILL_SEQUENCE_ID , c_rec.COMPONENT_SEQUENCE_ID ;
2299: ELSE
2300: 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, c_rec.BILL_SEQUENCE_ID;
2301: END IF;
2302: ELSE
2303: IF (c_rec.COMPONENT_SEQUENCE_ID IS NOT NULL) THEN
2304: 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, c_rec.COMPONENT_SEQUENCE_ID ;
2300: 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, c_rec.BILL_SEQUENCE_ID;
2301: END IF;
2302: ELSE
2303: IF (c_rec.COMPONENT_SEQUENCE_ID IS NOT NULL) THEN
2304: 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, c_rec.COMPONENT_SEQUENCE_ID ;
2305: /*ELSE --condition should not arise. even if it does, we should not delete
2306: 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; */
2307: END IF;
2308:
2302: ELSE
2303: IF (c_rec.COMPONENT_SEQUENCE_ID IS NOT NULL) THEN
2304: 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, c_rec.COMPONENT_SEQUENCE_ID ;
2305: /*ELSE --condition should not arise. even if it does, we should not delete
2306: 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; */
2307: END IF;
2308:
2309: END IF;
2310:
2319:
2320: /*
2321: UPDATE MSC_BOM_COMPONENTS
2322: SET USAGE_QUANTITY= 0,
2323: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2324: LAST_UPDATE_DATE= v_current_date,
2325: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2326: WHERE PLAN_ID= -1
2327: AND BILL_SEQUENCE_ID= NVL(c_rec.BILL_SEQUENCE_ID, BILL_SEQUENCE_ID)
2321: UPDATE MSC_BOM_COMPONENTS
2322: SET USAGE_QUANTITY= 0,
2323: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2324: LAST_UPDATE_DATE= v_current_date,
2325: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2326: WHERE PLAN_ID= -1
2327: AND BILL_SEQUENCE_ID= NVL(c_rec.BILL_SEQUENCE_ID, BILL_SEQUENCE_ID)
2328: AND COMPONENT_SEQUENCE_ID= NVL(c_rec.COMPONENT_SEQUENCE_ID, COMPONENT_SEQUENCE_ID)
2329: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
2399: FOR j IN 1..lb_COMPONENT_SEQUENCE_ID.COUNT LOOP
2400:
2401: BEGIN
2402:
2403: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
2404:
2405: UPDATE MSC_BOM_COMPONENTS
2406: SET
2407: ORGANIZATION_ID= lb_ORGANIZATION_ID(j),
2433: ROUNDING_DIRECTION = lb_ROUNDING_DIRECTION(j),
2434: PRIMARY_FLAG = lb_PRIMARY_FLAG(j),
2435: CONTRIBUTE_TO_STEP_QTY = lb_CONTRIBUTE_TO_STEP_QTY(j),
2436: OLD_COMPONENT_SEQUENCE_ID = lb_OLD_COMPONENT_SEQUENCE_ID(j),
2437: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2438: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2439: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2440: WHERE PLAN_ID= -1
2441: AND BILL_SEQUENCE_ID= lb_BILL_SEQUENCE_ID(j)
2434: PRIMARY_FLAG = lb_PRIMARY_FLAG(j),
2435: CONTRIBUTE_TO_STEP_QTY = lb_CONTRIBUTE_TO_STEP_QTY(j),
2436: OLD_COMPONENT_SEQUENCE_ID = lb_OLD_COMPONENT_SEQUENCE_ID(j),
2437: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2438: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2439: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2440: WHERE PLAN_ID= -1
2441: AND BILL_SEQUENCE_ID= lb_BILL_SEQUENCE_ID(j)
2442: AND COMPONENT_SEQUENCE_ID= lb_COMPONENT_SEQUENCE_ID(j)
2435: CONTRIBUTE_TO_STEP_QTY = lb_CONTRIBUTE_TO_STEP_QTY(j),
2436: OLD_COMPONENT_SEQUENCE_ID = lb_OLD_COMPONENT_SEQUENCE_ID(j),
2437: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2438: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2439: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2440: WHERE PLAN_ID= -1
2441: AND BILL_SEQUENCE_ID= lb_BILL_SEQUENCE_ID(j)
2442: AND COMPONENT_SEQUENCE_ID= lb_COMPONENT_SEQUENCE_ID(j)
2443: AND SR_INSTANCE_ID= lb_SR_INSTANCE_ID(j);
2443: AND SR_INSTANCE_ID= lb_SR_INSTANCE_ID(j);
2444:
2445: END IF;
2446:
2447: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
2448:
2449: EXECUTE IMMEDIATE lv_sql_stmt
2450: USING
2451: lb_COMPONENT_SEQUENCE_ID(j),
2480: lb_ROUNDING_DIRECTION(j),
2481: lb_PRIMARY_FLAG(j),
2482: lb_CONTRIBUTE_TO_STEP_QTY(j),
2483: lb_OLD_COMPONENT_SEQUENCE_ID(j),
2484: MSC_CL_COLLECTION.v_last_collection_id,
2485: MSC_CL_COLLECTION.v_current_date,
2486: MSC_CL_COLLECTION.v_current_user,
2487: MSC_CL_COLLECTION.v_current_date,
2488: MSC_CL_COLLECTION.v_current_user;
2481: lb_PRIMARY_FLAG(j),
2482: lb_CONTRIBUTE_TO_STEP_QTY(j),
2483: lb_OLD_COMPONENT_SEQUENCE_ID(j),
2484: MSC_CL_COLLECTION.v_last_collection_id,
2485: MSC_CL_COLLECTION.v_current_date,
2486: MSC_CL_COLLECTION.v_current_user,
2487: MSC_CL_COLLECTION.v_current_date,
2488: MSC_CL_COLLECTION.v_current_user;
2489: END IF; -- SQL%NOTFOUND
2482: lb_CONTRIBUTE_TO_STEP_QTY(j),
2483: lb_OLD_COMPONENT_SEQUENCE_ID(j),
2484: MSC_CL_COLLECTION.v_last_collection_id,
2485: MSC_CL_COLLECTION.v_current_date,
2486: MSC_CL_COLLECTION.v_current_user,
2487: MSC_CL_COLLECTION.v_current_date,
2488: MSC_CL_COLLECTION.v_current_user;
2489: END IF; -- SQL%NOTFOUND
2490:
2483: lb_OLD_COMPONENT_SEQUENCE_ID(j),
2484: MSC_CL_COLLECTION.v_last_collection_id,
2485: MSC_CL_COLLECTION.v_current_date,
2486: MSC_CL_COLLECTION.v_current_user,
2487: MSC_CL_COLLECTION.v_current_date,
2488: MSC_CL_COLLECTION.v_current_user;
2489: END IF; -- SQL%NOTFOUND
2490:
2491:
2484: MSC_CL_COLLECTION.v_last_collection_id,
2485: MSC_CL_COLLECTION.v_current_date,
2486: MSC_CL_COLLECTION.v_current_user,
2487: MSC_CL_COLLECTION.v_current_date,
2488: MSC_CL_COLLECTION.v_current_user;
2489: END IF; -- SQL%NOTFOUND
2490:
2491:
2492:
2504: RAISE;
2505:
2506: ELSE
2507:
2508: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2509:
2510: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2511: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2512: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOM_COMPONENTS');
2537: END IF;
2538:
2539: BEGIN
2540:
2541: 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
2542:
2543: lv_tbl:= 'BOM_COMPONENTS_'||MSC_CL_COLLECTION.v_instance_code;
2544:
2545: lv_sql_stmt:=
2539: BEGIN
2540:
2541: 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
2542:
2543: lv_tbl:= 'BOM_COMPONENTS_'||MSC_CL_COLLECTION.v_instance_code;
2544:
2545: lv_sql_stmt:=
2546: 'INSERT INTO '||MSC_UTIL.G_MSC_SCHEMA||'.'||lv_tbl
2547: ||' SELECT * from '||MSC_UTIL.G_MSC_SCHEMA||'.MSC_BOM_COMPONENTS'
2544:
2545: lv_sql_stmt:=
2546: 'INSERT INTO '||MSC_UTIL.G_MSC_SCHEMA||'.'||lv_tbl
2547: ||' SELECT * from '||MSC_UTIL.G_MSC_SCHEMA||'.MSC_BOM_COMPONENTS'
2548: ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
2549: ||' AND plan_id = -1 '
2550: ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
2551:
2552: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The sql statement is '||lv_sql_stmt);
2555: COMMIT;
2556:
2557: END IF;
2558:
2559: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
2560: MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
2561: lv_retcode,
2562: 'MSC_BOM_COMPONENTS',
2563: MSC_CL_COLLECTION.v_instance_code,
2556:
2557: END IF;
2558:
2559: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
2560: MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
2561: lv_retcode,
2562: 'MSC_BOM_COMPONENTS',
2563: MSC_CL_COLLECTION.v_instance_code,
2564: MSC_UTIL.G_WARNING
2559: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
2560: MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
2561: lv_retcode,
2562: 'MSC_BOM_COMPONENTS',
2563: MSC_CL_COLLECTION.v_instance_code,
2564: MSC_UTIL.G_WARNING
2565: );
2566:
2567: IF lv_retcode = MSC_UTIL.G_ERROR THEN
2565: );
2566:
2567: IF lv_retcode = MSC_UTIL.G_ERROR THEN
2568: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
2569: RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
2570: ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
2571: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2572: END IF;
2573:
2567: IF lv_retcode = MSC_UTIL.G_ERROR THEN
2568: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
2569: RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
2570: ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
2571: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2572: END IF;
2573:
2574: END IF;
2575:
2643: msdr.SETUP_TIME_TYPE,
2644: msdr.UTILIZATION_CHANGE_TYPE ,
2645: msdr.SDS_SCHEDULING_WINDOW /* ds change change end */
2646: FROM MSC_ST_DEPARTMENT_RESOURCES msdr
2647: WHERE msdr.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
2648:
2649: CURSOR c3 IS
2650: SELECT
2651: msss.ORGANIZATION_ID,
2653: msss.DESCRIPTION,
2654: msss.USE_IN_WIP_FLAG,
2655: msss.SR_INSTANCE_ID
2656: FROM MSC_ST_SIMULATION_SETS msss
2657: WHERE msss.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
2658:
2659: CURSOR c4 IS
2660: SELECT
2661: msrg.GROUP_CODE,
2664: msrg.FROM_DATE,
2665: msrg.TO_DATE,
2666: msrg.ENABLED_FLAG
2667: FROM MSC_ST_RESOURCE_GROUPS msrg
2668: WHERE msrg.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
2669:
2670: /* ds:plan: change start */
2671: CURSOR dept_res_inst IS
2672: SELECT
2682: msdri.LAST_KNOWN_SETUP,
2683: msdri.DELETED_FLAG
2684: FROM MSC_ST_DEPT_RES_INSTANCES msdri,
2685: MSC_ITEM_ID_LID t1
2686: WHERE msdri.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
2687: AND t1.sr_instance_id (+) = msdri.sr_instance_id
2688: AND t1.sr_inventory_item_id (+) = msdri.equipment_item_id;
2689: /* ds:plan: change end */
2690:
2702: lv_res_partial_refresh BOOLEAN;
2703:
2704: BEGIN
2705:
2706: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
2707: if MSC_CL_COLLECTION.v_bom_refresh_type =1 then --2 be changed
2708: lv_res_incr_refresh :=TRUE;
2709: lv_res_partial_refresh := FALSE;
2710: elsif MSC_CL_COLLECTION.v_bom_refresh_type =2 then --2 be changed
2703:
2704: BEGIN
2705:
2706: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
2707: if MSC_CL_COLLECTION.v_bom_refresh_type =1 then --2 be changed
2708: lv_res_incr_refresh :=TRUE;
2709: lv_res_partial_refresh := FALSE;
2710: elsif MSC_CL_COLLECTION.v_bom_refresh_type =2 then --2 be changed
2711: lv_res_incr_refresh := FALSE;
2706: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
2707: if MSC_CL_COLLECTION.v_bom_refresh_type =1 then --2 be changed
2708: lv_res_incr_refresh :=TRUE;
2709: lv_res_partial_refresh := FALSE;
2710: elsif MSC_CL_COLLECTION.v_bom_refresh_type =2 then --2 be changed
2711: lv_res_incr_refresh := FALSE;
2712: lv_res_partial_refresh := TRUE;
2713: else
2714: null;
2713: else
2714: null;
2715: end if;
2716: else
2717: if(MSC_CL_COLLECTION.v_is_partial_refresh) then
2718: lv_res_partial_refresh:=TRUE;
2719: lv_res_incr_refresh := FALSE;
2720: elsif(MSC_CL_COLLECTION.v_is_incremental_refresh) then
2721: lv_res_incr_refresh:= TRUE;
2716: else
2717: if(MSC_CL_COLLECTION.v_is_partial_refresh) then
2718: lv_res_partial_refresh:=TRUE;
2719: lv_res_incr_refresh := FALSE;
2720: elsif(MSC_CL_COLLECTION.v_is_incremental_refresh) then
2721: lv_res_incr_refresh:= TRUE;
2722: lv_res_partial_refresh := FALSE;
2723: end if;
2724: end if;
2729: /* ds change change start */
2730: select bom, wip
2731: into lv_bom, lv_wip
2732: from msc_coll_parameters
2733: where instance_id = MSC_CL_COLLECTION.v_instance_id;
2734:
2735: IF (((lv_bom = MSC_UTIL.SYS_YES) OR (lv_wip = MSC_UTIL.SYS_YES)) AND NOT MSC_CL_COLLECTION.v_is_legacy_refresh) THEN
2736: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2737: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_DEPT_RES_INSTANCES', MSC_CL_COLLECTION.v_instance_id, -1);
2731: into lv_bom, lv_wip
2732: from msc_coll_parameters
2733: where instance_id = MSC_CL_COLLECTION.v_instance_id;
2734:
2735: IF (((lv_bom = MSC_UTIL.SYS_YES) OR (lv_wip = MSC_UTIL.SYS_YES)) AND NOT MSC_CL_COLLECTION.v_is_legacy_refresh) THEN
2736: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2737: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_DEPT_RES_INSTANCES', MSC_CL_COLLECTION.v_instance_id, -1);
2738: ELSE
2739: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2732: from msc_coll_parameters
2733: where instance_id = MSC_CL_COLLECTION.v_instance_id;
2734:
2735: IF (((lv_bom = MSC_UTIL.SYS_YES) OR (lv_wip = MSC_UTIL.SYS_YES)) AND NOT MSC_CL_COLLECTION.v_is_legacy_refresh) THEN
2736: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2737: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_DEPT_RES_INSTANCES', MSC_CL_COLLECTION.v_instance_id, -1);
2738: ELSE
2739: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2740: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_DEPT_RES_INSTANCES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str); /* ds change change */
2733: where instance_id = MSC_CL_COLLECTION.v_instance_id;
2734:
2735: IF (((lv_bom = MSC_UTIL.SYS_YES) OR (lv_wip = MSC_UTIL.SYS_YES)) AND NOT MSC_CL_COLLECTION.v_is_legacy_refresh) THEN
2736: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2737: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_DEPT_RES_INSTANCES', MSC_CL_COLLECTION.v_instance_id, -1);
2738: ELSE
2739: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2740: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_DEPT_RES_INSTANCES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str); /* ds change change */
2741: END IF;
2736: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2737: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_DEPT_RES_INSTANCES', MSC_CL_COLLECTION.v_instance_id, -1);
2738: ELSE
2739: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2740: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_DEPT_RES_INSTANCES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str); /* ds change change */
2741: END IF;
2742: END IF;
2743: /* ds change change end */
2744:
2742: END IF;
2743: /* ds change change end */
2744:
2745:
2746: IF ((MSC_CL_COLLECTION.v_is_complete_refresh) OR (lv_res_partial_refresh)) THEN
2747:
2748:
2749: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESOURCE_GROUPS', MSC_CL_COLLECTION.v_instance_id, NULL); /*No Organization_id Column */
2750:
2745:
2746: IF ((MSC_CL_COLLECTION.v_is_complete_refresh) OR (lv_res_partial_refresh)) THEN
2747:
2748:
2749: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESOURCE_GROUPS', MSC_CL_COLLECTION.v_instance_id, NULL); /*No Organization_id Column */
2750:
2751: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2752: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_DEPARTMENT_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1);
2753: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SIMULATION_SETS', MSC_CL_COLLECTION.v_instance_id,NULL);
2747:
2748:
2749: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESOURCE_GROUPS', MSC_CL_COLLECTION.v_instance_id, NULL); /*No Organization_id Column */
2750:
2751: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2752: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_DEPARTMENT_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1);
2753: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SIMULATION_SETS', MSC_CL_COLLECTION.v_instance_id,NULL);
2754: ELSE
2755: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2748:
2749: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESOURCE_GROUPS', MSC_CL_COLLECTION.v_instance_id, NULL); /*No Organization_id Column */
2750:
2751: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2752: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_DEPARTMENT_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1);
2753: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SIMULATION_SETS', MSC_CL_COLLECTION.v_instance_id,NULL);
2754: ELSE
2755: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2756: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_DEPARTMENT_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
2749: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESOURCE_GROUPS', MSC_CL_COLLECTION.v_instance_id, NULL); /*No Organization_id Column */
2750:
2751: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2752: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_DEPARTMENT_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1);
2753: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SIMULATION_SETS', MSC_CL_COLLECTION.v_instance_id,NULL);
2754: ELSE
2755: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2756: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_DEPARTMENT_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
2757: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SIMULATION_SETS', MSC_CL_COLLECTION.v_instance_id,NULL,v_sub_str);
2752: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_DEPARTMENT_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1);
2753: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SIMULATION_SETS', MSC_CL_COLLECTION.v_instance_id,NULL);
2754: ELSE
2755: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2756: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_DEPARTMENT_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
2757: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SIMULATION_SETS', MSC_CL_COLLECTION.v_instance_id,NULL,v_sub_str);
2758: END IF;
2759:
2760: ELSE
2753: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SIMULATION_SETS', MSC_CL_COLLECTION.v_instance_id,NULL);
2754: ELSE
2755: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2756: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_DEPARTMENT_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
2757: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SIMULATION_SETS', MSC_CL_COLLECTION.v_instance_id,NULL,v_sub_str);
2758: END IF;
2759:
2760: ELSE
2761:
2758: END IF;
2759:
2760: ELSE
2761:
2762: IF (((lv_bom = MSC_UTIL.SYS_YES) OR (lv_wip = MSC_UTIL.SYS_YES)) AND NOT MSC_CL_COLLECTION.v_is_legacy_refresh) THEN
2763:
2764: /* Bug 3041176 - Delete Only Department resources in NetChange */
2765:
2766: p_instance_id := MSC_CL_COLLECTION.v_instance_id;
2762: IF (((lv_bom = MSC_UTIL.SYS_YES) OR (lv_wip = MSC_UTIL.SYS_YES)) AND NOT MSC_CL_COLLECTION.v_is_legacy_refresh) THEN
2763:
2764: /* Bug 3041176 - Delete Only Department resources in NetChange */
2765:
2766: p_instance_id := MSC_CL_COLLECTION.v_instance_id;
2767: p_table_name := 'MSC_DEPARTMENT_RESOURCES';
2768:
2769: lv_pbs:= TO_NUMBER( FND_PROFILE.VALUE('MRP_PURGE_BATCH_SIZE'));
2770:
2865: UPDATE MSC_DEPARTMENT_RESOURCES
2866: SET CAPACITY_UNITS= 0,
2867: MAX_RATE= 0,
2868: MIN_RATE= 0,
2869: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2870: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2871: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2872: WHERE PLAN_ID= -1
2873: AND DEPARTMENT_ID= c_rec.DEPARTMENT_ID
2866: SET CAPACITY_UNITS= 0,
2867: MAX_RATE= 0,
2868: MIN_RATE= 0,
2869: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2870: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2871: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2872: WHERE PLAN_ID= -1
2873: AND DEPARTMENT_ID= c_rec.DEPARTMENT_ID
2874: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
2867: MAX_RATE= 0,
2868: MIN_RATE= 0,
2869: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2870: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2871: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2872: WHERE PLAN_ID= -1
2873: AND DEPARTMENT_ID= c_rec.DEPARTMENT_ID
2874: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
2875: AND RESOURCE_ID= c_rec.RESOURCE_ID
2919: RESOURCE_COST= c_rec.RESOURCE_COST,
2920: RESOURCE_OVER_UTIL_COST= c_rec.RESOURCE_OVER_UTIL_COST,
2921: DEPT_OVERHEAD_COST= c_rec.DEPT_OVERHEAD_COST,
2922: ATP_RULE_ID=c_rec.ATP_RULE_ID,
2923: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2924: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2925: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
2926: CAPACITY_TOLERANCE= c_rec.CAPACITY_TOLERANCE, /* ds change change start*/
2927: CHARGEABLE_FLAG= c_rec.CHARGEABLE_FLAG,
2920: RESOURCE_OVER_UTIL_COST= c_rec.RESOURCE_OVER_UTIL_COST,
2921: DEPT_OVERHEAD_COST= c_rec.DEPT_OVERHEAD_COST,
2922: ATP_RULE_ID=c_rec.ATP_RULE_ID,
2923: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2924: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2925: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
2926: CAPACITY_TOLERANCE= c_rec.CAPACITY_TOLERANCE, /* ds change change start*/
2927: CHARGEABLE_FLAG= c_rec.CHARGEABLE_FLAG,
2928: IDLE_TIME_TOLERANCE= c_rec.IDLE_TIME_TOLERANCE,
2921: DEPT_OVERHEAD_COST= c_rec.DEPT_OVERHEAD_COST,
2922: ATP_RULE_ID=c_rec.ATP_RULE_ID,
2923: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2924: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2925: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
2926: CAPACITY_TOLERANCE= c_rec.CAPACITY_TOLERANCE, /* ds change change start*/
2927: CHARGEABLE_FLAG= c_rec.CHARGEABLE_FLAG,
2928: IDLE_TIME_TOLERANCE= c_rec.IDLE_TIME_TOLERANCE,
2929: BATCHING_PENALTY= c_rec.BATCHING_PENALTY,
2943: END IF; -- DELETED_FLAG
2944:
2945: END IF; -- refresh mode
2946:
2947: IF ((MSC_CL_COLLECTION.v_is_complete_refresh) OR (lv_res_partial_refresh)) OR
2948: (SQL%NOTFOUND) THEN
2949: INSERT INTO MSC_DEPARTMENT_RESOURCES
2950: ( PLAN_ID,
2951: RESOURCE_CODE,
3069: c_rec.SETUP_TIME_TYPE,
3070: c_rec.UTILIZATION_CHANGE_TYPE,
3071: c_rec.SDS_SCHEDULING_WINDOW,
3072: /* ds change change end */
3073: MSC_CL_COLLECTION.v_last_collection_id,
3074: MSC_CL_COLLECTION.v_current_date,
3075: MSC_CL_COLLECTION.v_current_user,
3076: MSC_CL_COLLECTION.v_current_date,
3077: MSC_CL_COLLECTION.v_current_user );
3070: c_rec.UTILIZATION_CHANGE_TYPE,
3071: c_rec.SDS_SCHEDULING_WINDOW,
3072: /* ds change change end */
3073: MSC_CL_COLLECTION.v_last_collection_id,
3074: MSC_CL_COLLECTION.v_current_date,
3075: MSC_CL_COLLECTION.v_current_user,
3076: MSC_CL_COLLECTION.v_current_date,
3077: MSC_CL_COLLECTION.v_current_user );
3078:
3071: c_rec.SDS_SCHEDULING_WINDOW,
3072: /* ds change change end */
3073: MSC_CL_COLLECTION.v_last_collection_id,
3074: MSC_CL_COLLECTION.v_current_date,
3075: MSC_CL_COLLECTION.v_current_user,
3076: MSC_CL_COLLECTION.v_current_date,
3077: MSC_CL_COLLECTION.v_current_user );
3078:
3079: END IF;
3072: /* ds change change end */
3073: MSC_CL_COLLECTION.v_last_collection_id,
3074: MSC_CL_COLLECTION.v_current_date,
3075: MSC_CL_COLLECTION.v_current_user,
3076: MSC_CL_COLLECTION.v_current_date,
3077: MSC_CL_COLLECTION.v_current_user );
3078:
3079: END IF;
3080:
3073: MSC_CL_COLLECTION.v_last_collection_id,
3074: MSC_CL_COLLECTION.v_current_date,
3075: MSC_CL_COLLECTION.v_current_user,
3076: MSC_CL_COLLECTION.v_current_date,
3077: MSC_CL_COLLECTION.v_current_user );
3078:
3079: END IF;
3080:
3081: c_count:= c_count+1;
3079: END IF;
3080:
3081: c_count:= c_count+1;
3082:
3083: IF c_count> MSC_CL_COLLECTION.PBS THEN
3084: COMMIT;
3085: c_count:= 0;
3086: END IF;
3087:
3100: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3101: RAISE;
3102:
3103: ELSE
3104: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3105:
3106: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3107: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3108: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE');
3112: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3113: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
3114: FND_MESSAGE.SET_TOKEN('VALUE',
3115: MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
3116: MSC_CL_COLLECTION.v_instance_id));
3117: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3118:
3119: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3120: FND_MESSAGE.SET_TOKEN('COLUMN', 'DEPARTMENT_CODE');
3141: FOR c_rec IN dept_res_inst LOOP
3142: /* for dept resource instance we don't have snapshot
3143: so in increment its alwasy full refresh */
3144: BEGIN
3145: -- IF ((MSC_CL_COLLECTION.v_is_complete_refresh) OR (lv_res_partial_refresh)) THEN
3146:
3147: INSERT INTO MSC_DEPT_RES_INSTANCES
3148: ( PLAN_ID,
3149: DEPT_RESOURCE_INST_ID ,
3174: c_rec.SR_INSTANCE_ID,
3175: c_rec.LAST_KNOWN_SETUP,
3176: c_rec.EFFECTIVE_START_DATE,
3177: c_rec.EFFECTIVE_END_DATE,
3178: MSC_CL_COLLECTION.v_last_collection_id,
3179: MSC_CL_COLLECTION.v_current_date,
3180: MSC_CL_COLLECTION.v_current_user,
3181: MSC_CL_COLLECTION.v_current_date,
3182: MSC_CL_COLLECTION.v_current_user );
3175: c_rec.LAST_KNOWN_SETUP,
3176: c_rec.EFFECTIVE_START_DATE,
3177: c_rec.EFFECTIVE_END_DATE,
3178: MSC_CL_COLLECTION.v_last_collection_id,
3179: MSC_CL_COLLECTION.v_current_date,
3180: MSC_CL_COLLECTION.v_current_user,
3181: MSC_CL_COLLECTION.v_current_date,
3182: MSC_CL_COLLECTION.v_current_user );
3183: --END IF;
3176: c_rec.EFFECTIVE_START_DATE,
3177: c_rec.EFFECTIVE_END_DATE,
3178: MSC_CL_COLLECTION.v_last_collection_id,
3179: MSC_CL_COLLECTION.v_current_date,
3180: MSC_CL_COLLECTION.v_current_user,
3181: MSC_CL_COLLECTION.v_current_date,
3182: MSC_CL_COLLECTION.v_current_user );
3183: --END IF;
3184:
3177: c_rec.EFFECTIVE_END_DATE,
3178: MSC_CL_COLLECTION.v_last_collection_id,
3179: MSC_CL_COLLECTION.v_current_date,
3180: MSC_CL_COLLECTION.v_current_user,
3181: MSC_CL_COLLECTION.v_current_date,
3182: MSC_CL_COLLECTION.v_current_user );
3183: --END IF;
3184:
3185: c_count:= c_count+1;
3178: MSC_CL_COLLECTION.v_last_collection_id,
3179: MSC_CL_COLLECTION.v_current_date,
3180: MSC_CL_COLLECTION.v_current_user,
3181: MSC_CL_COLLECTION.v_current_date,
3182: MSC_CL_COLLECTION.v_current_user );
3183: --END IF;
3184:
3185: c_count:= c_count+1;
3186:
3183: --END IF;
3184:
3185: c_count:= c_count+1;
3186:
3187: IF c_count> MSC_CL_COLLECTION.PBS THEN
3188: COMMIT;
3189: c_count:= 0;
3190: END IF;
3191:
3203: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3204: RAISE;
3205:
3206: ELSE
3207: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3208:
3209: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3210: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3211: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE');
3254: UPDATE MSC_SIMULATION_SETS
3255: SET
3256: DESCRIPTION= c_rec.DESCRIPTION,
3257: USE_IN_WIP_FLAG= c_rec.USE_IN_WIP_FLAG,
3258: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
3259: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3260: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3261: WHERE ORGANIZATION_ID= c_rec.ORGANIZATION_ID
3262: AND SIMULATION_SET= c_rec.SIMULATION_SET
3255: SET
3256: DESCRIPTION= c_rec.DESCRIPTION,
3257: USE_IN_WIP_FLAG= c_rec.USE_IN_WIP_FLAG,
3258: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
3259: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3260: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3261: WHERE ORGANIZATION_ID= c_rec.ORGANIZATION_ID
3262: AND SIMULATION_SET= c_rec.SIMULATION_SET
3263: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
3256: DESCRIPTION= c_rec.DESCRIPTION,
3257: USE_IN_WIP_FLAG= c_rec.USE_IN_WIP_FLAG,
3258: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
3259: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3260: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3261: WHERE ORGANIZATION_ID= c_rec.ORGANIZATION_ID
3262: AND SIMULATION_SET= c_rec.SIMULATION_SET
3263: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
3264:
3263: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
3264:
3265: END IF;
3266:
3267: IF ((MSC_CL_COLLECTION.v_is_complete_refresh) OR (lv_res_partial_refresh)) OR SQL%NOTFOUND THEN
3268:
3269: INSERT INTO MSC_SIMULATION_SETS
3270: ( ORGANIZATION_ID,
3271: SIMULATION_SET,
3282: c_rec.SIMULATION_SET,
3283: c_rec.DESCRIPTION,
3284: c_rec.USE_IN_WIP_FLAG,
3285: c_rec.SR_INSTANCE_ID,
3286: MSC_CL_COLLECTION.v_last_collection_id,
3287: MSC_CL_COLLECTION.v_current_date,
3288: MSC_CL_COLLECTION.v_current_user,
3289: MSC_CL_COLLECTION.v_current_date,
3290: MSC_CL_COLLECTION.v_current_user );
3283: c_rec.DESCRIPTION,
3284: c_rec.USE_IN_WIP_FLAG,
3285: c_rec.SR_INSTANCE_ID,
3286: MSC_CL_COLLECTION.v_last_collection_id,
3287: MSC_CL_COLLECTION.v_current_date,
3288: MSC_CL_COLLECTION.v_current_user,
3289: MSC_CL_COLLECTION.v_current_date,
3290: MSC_CL_COLLECTION.v_current_user );
3291:
3284: c_rec.USE_IN_WIP_FLAG,
3285: c_rec.SR_INSTANCE_ID,
3286: MSC_CL_COLLECTION.v_last_collection_id,
3287: MSC_CL_COLLECTION.v_current_date,
3288: MSC_CL_COLLECTION.v_current_user,
3289: MSC_CL_COLLECTION.v_current_date,
3290: MSC_CL_COLLECTION.v_current_user );
3291:
3292: END IF;
3285: c_rec.SR_INSTANCE_ID,
3286: MSC_CL_COLLECTION.v_last_collection_id,
3287: MSC_CL_COLLECTION.v_current_date,
3288: MSC_CL_COLLECTION.v_current_user,
3289: MSC_CL_COLLECTION.v_current_date,
3290: MSC_CL_COLLECTION.v_current_user );
3291:
3292: END IF;
3293:
3286: MSC_CL_COLLECTION.v_last_collection_id,
3287: MSC_CL_COLLECTION.v_current_date,
3288: MSC_CL_COLLECTION.v_current_user,
3289: MSC_CL_COLLECTION.v_current_date,
3290: MSC_CL_COLLECTION.v_current_user );
3291:
3292: END IF;
3293:
3294: c_count:= c_count+1;
3292: END IF;
3293:
3294: c_count:= c_count+1;
3295:
3296: IF c_count> MSC_CL_COLLECTION.PBS THEN
3297: COMMIT;
3298: c_count:= 0;
3299: END IF;
3300:
3312: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3313: RAISE;
3314:
3315: ELSE
3316: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3317:
3318: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3319: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3320: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE');
3324: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3325: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
3326: FND_MESSAGE.SET_TOKEN('VALUE',
3327: MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
3328: MSC_CL_COLLECTION.v_instance_id));
3329: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3330:
3331: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3332: FND_MESSAGE.SET_TOKEN('COLUMN', 'SIMULATION_SET');
3341: END LOOP;
3342:
3343: COMMIT;
3344:
3345: IF ((MSC_CL_COLLECTION.v_is_complete_refresh) OR (lv_res_partial_refresh)) THEN
3346:
3347: c_count:= 0;
3348:
3349: FOR c_rec IN c4 LOOP
3368: c_rec.DESCRIPTION,
3369: c_rec.FROM_DATE,
3370: c_rec.TO_DATE,
3371: c_rec.ENABLED_FLAG,
3372: MSC_CL_COLLECTION.v_instance_id,
3373: MSC_CL_COLLECTION.v_current_date,
3374: MSC_CL_COLLECTION.v_current_user,
3375: MSC_CL_COLLECTION.v_current_date,
3376: MSC_CL_COLLECTION.v_current_user );
3369: c_rec.FROM_DATE,
3370: c_rec.TO_DATE,
3371: c_rec.ENABLED_FLAG,
3372: MSC_CL_COLLECTION.v_instance_id,
3373: MSC_CL_COLLECTION.v_current_date,
3374: MSC_CL_COLLECTION.v_current_user,
3375: MSC_CL_COLLECTION.v_current_date,
3376: MSC_CL_COLLECTION.v_current_user );
3377:
3370: c_rec.TO_DATE,
3371: c_rec.ENABLED_FLAG,
3372: MSC_CL_COLLECTION.v_instance_id,
3373: MSC_CL_COLLECTION.v_current_date,
3374: MSC_CL_COLLECTION.v_current_user,
3375: MSC_CL_COLLECTION.v_current_date,
3376: MSC_CL_COLLECTION.v_current_user );
3377:
3378: c_count:= c_count+1;
3371: c_rec.ENABLED_FLAG,
3372: MSC_CL_COLLECTION.v_instance_id,
3373: MSC_CL_COLLECTION.v_current_date,
3374: MSC_CL_COLLECTION.v_current_user,
3375: MSC_CL_COLLECTION.v_current_date,
3376: MSC_CL_COLLECTION.v_current_user );
3377:
3378: c_count:= c_count+1;
3379:
3372: MSC_CL_COLLECTION.v_instance_id,
3373: MSC_CL_COLLECTION.v_current_date,
3374: MSC_CL_COLLECTION.v_current_user,
3375: MSC_CL_COLLECTION.v_current_date,
3376: MSC_CL_COLLECTION.v_current_user );
3377:
3378: c_count:= c_count+1;
3379:
3380: IF c_count> MSC_CL_COLLECTION.PBS THEN
3376: MSC_CL_COLLECTION.v_current_user );
3377:
3378: c_count:= c_count+1;
3379:
3380: IF c_count> MSC_CL_COLLECTION.PBS THEN
3381: COMMIT;
3382: c_count:= 0;
3383: END IF;
3384:
3397: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3398: RAISE;
3399:
3400: ELSE
3401: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3402:
3403: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3404: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3405: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE');