[Home] [Help]
30: mrs.setup_id,
31: mrs.setup_code,
32: mrs.setup_Description
33: FROM MSC_ST_RESOURCE_SETUPS mrs
34: WHERE mrs.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
35: ORDER BY mrs.DELETED_FLAG;
36: lv_cnt NUMBER;
37: lv_pbs NUMBER;
38:
39: c_count NUMBER := 0;
40: total_count NUMBER := 0;
41: BEGIN
42:
43: IF ((MSC_CL_COLLECTION.v_is_complete_refresh) OR (MSC_CL_COLLECTION.v_is_partial_refresh)) THEN
44: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
45: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESOURCE_SETUPS', MSC_CL_COLLECTION.v_instance_id, -1);
46: ELSE
47: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
40: total_count NUMBER := 0;
41: BEGIN
42:
43: IF ((MSC_CL_COLLECTION.v_is_complete_refresh) OR (MSC_CL_COLLECTION.v_is_partial_refresh)) THEN
44: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
45: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESOURCE_SETUPS', MSC_CL_COLLECTION.v_instance_id, -1);
46: ELSE
47: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
48: MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_RESOURCE_SETUPS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
41: BEGIN
42:
43: IF ((MSC_CL_COLLECTION.v_is_complete_refresh) OR (MSC_CL_COLLECTION.v_is_partial_refresh)) THEN
44: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
45: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESOURCE_SETUPS', MSC_CL_COLLECTION.v_instance_id, -1);
46: ELSE
47: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
48: MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_RESOURCE_SETUPS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
49: END IF;
44: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
45: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESOURCE_SETUPS', MSC_CL_COLLECTION.v_instance_id, -1);
46: ELSE
47: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
48: MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_RESOURCE_SETUPS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
49: END IF;
50:
51:
52: c_count := 0;
75: c_rec.SR_INSTANCE_ID ,
76: c_rec. SETUP_ID,
77: c_rec.SETUP_CODE,
78: c_rec.SETUP_DESCRIPTION,
79: MSC_CL_COLLECTION.v_last_collection_id,
80: MSC_CL_COLLECTION.v_current_date,
81: MSC_CL_COLLECTION.v_current_user,
82: MSC_CL_COLLECTION.v_current_date,
83: MSC_CL_COLLECTION.v_current_user );
76: c_rec. SETUP_ID,
77: c_rec.SETUP_CODE,
78: c_rec.SETUP_DESCRIPTION,
79: MSC_CL_COLLECTION.v_last_collection_id,
80: MSC_CL_COLLECTION.v_current_date,
81: MSC_CL_COLLECTION.v_current_user,
82: MSC_CL_COLLECTION.v_current_date,
83: MSC_CL_COLLECTION.v_current_user );
84:
77: c_rec.SETUP_CODE,
78: c_rec.SETUP_DESCRIPTION,
79: MSC_CL_COLLECTION.v_last_collection_id,
80: MSC_CL_COLLECTION.v_current_date,
81: MSC_CL_COLLECTION.v_current_user,
82: MSC_CL_COLLECTION.v_current_date,
83: MSC_CL_COLLECTION.v_current_user );
84:
85: c_count:= c_count+1;
78: c_rec.SETUP_DESCRIPTION,
79: MSC_CL_COLLECTION.v_last_collection_id,
80: MSC_CL_COLLECTION.v_current_date,
81: MSC_CL_COLLECTION.v_current_user,
82: MSC_CL_COLLECTION.v_current_date,
83: MSC_CL_COLLECTION.v_current_user );
84:
85: c_count:= c_count+1;
86: total_count:= total_count+1;
79: MSC_CL_COLLECTION.v_last_collection_id,
80: MSC_CL_COLLECTION.v_current_date,
81: MSC_CL_COLLECTION.v_current_user,
82: MSC_CL_COLLECTION.v_current_date,
83: MSC_CL_COLLECTION.v_current_user );
84:
85: c_count:= c_count+1;
86: total_count:= total_count+1;
87:
84:
85: c_count:= c_count+1;
86: total_count:= total_count+1;
87:
88: IF c_count> MSC_CL_COLLECTION.PBS THEN
89: COMMIT;
90: c_count:= 0;
91: END IF;
92:
105: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
106: RAISE;
107:
108: ELSE
109: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
110:
111: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
112: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
113: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE_SETUP');
117: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
118: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
119: FND_MESSAGE.SET_TOKEN('VALUE',
120: MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
121: MSC_CL_COLLECTION.v_instance_id));
122: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
123:
124: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
125: FND_MESSAGE.SET_TOKEN('COLUMN', 'RESOURCE_ID');
139: END LOOP;
140: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Total resource setups = '|| to_char(total_count));
141:
142: COMMIT;
143: END IF; /* MSC_CL_COLLECTION.v_is_complete_refresh OR v_is_partial_refresh */
144: END LOAD_RESOURCE_SETUP;
145:
146: PROCEDURE LOAD_SETUP_TRANSITION
147: IS
156: mrs.transition_time,
157: mrs.transition_uom,
158: mrs.transition_penalty
159: FROM MSC_ST_SETUP_TRANSITIONS mrs
160: WHERE mrs.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
161:
162: lv_cnt NUMBER;
163: lv_pbs NUMBER;
164: c_count NUMBER := 0;
164: c_count NUMBER := 0;
165: total_count NUMBER := 0;
166: BEGIN
167:
168: IF ((MSC_CL_COLLECTION.v_is_complete_refresh) OR (MSC_CL_COLLECTION.v_is_partial_refresh)) THEN
169: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
170: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SETUP_TRANSITIONS', MSC_CL_COLLECTION.v_instance_id, -1);
171: ELSE
172: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
165: total_count NUMBER := 0;
166: BEGIN
167:
168: IF ((MSC_CL_COLLECTION.v_is_complete_refresh) OR (MSC_CL_COLLECTION.v_is_partial_refresh)) THEN
169: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
170: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SETUP_TRANSITIONS', MSC_CL_COLLECTION.v_instance_id, -1);
171: ELSE
172: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
173: MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_SETUP_TRANSITIONS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
166: BEGIN
167:
168: IF ((MSC_CL_COLLECTION.v_is_complete_refresh) OR (MSC_CL_COLLECTION.v_is_partial_refresh)) THEN
169: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
170: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SETUP_TRANSITIONS', MSC_CL_COLLECTION.v_instance_id, -1);
171: ELSE
172: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
173: MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_SETUP_TRANSITIONS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
174: END IF;
169: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
170: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SETUP_TRANSITIONS', MSC_CL_COLLECTION.v_instance_id, -1);
171: ELSE
172: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
173: MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_SETUP_TRANSITIONS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
174: END IF;
175:
176:
177: c_count := 0;
204: c_rec.STANDARD_OPERATION_ID,
205: c_rec.TRANSITION_TIME,
206: c_rec.TRANSITION_UOM,
207: c_rec.TRANSITION_PENALTY,
208: MSC_CL_COLLECTION.v_last_collection_id,
209: MSC_CL_COLLECTION.v_current_date,
210: MSC_CL_COLLECTION.v_current_user,
211: MSC_CL_COLLECTION.v_current_date,
212: MSC_CL_COLLECTION.v_current_user );
205: c_rec.TRANSITION_TIME,
206: c_rec.TRANSITION_UOM,
207: c_rec.TRANSITION_PENALTY,
208: MSC_CL_COLLECTION.v_last_collection_id,
209: MSC_CL_COLLECTION.v_current_date,
210: MSC_CL_COLLECTION.v_current_user,
211: MSC_CL_COLLECTION.v_current_date,
212: MSC_CL_COLLECTION.v_current_user );
213:
206: c_rec.TRANSITION_UOM,
207: c_rec.TRANSITION_PENALTY,
208: MSC_CL_COLLECTION.v_last_collection_id,
209: MSC_CL_COLLECTION.v_current_date,
210: MSC_CL_COLLECTION.v_current_user,
211: MSC_CL_COLLECTION.v_current_date,
212: MSC_CL_COLLECTION.v_current_user );
213:
214: c_count:= c_count+1;
207: c_rec.TRANSITION_PENALTY,
208: MSC_CL_COLLECTION.v_last_collection_id,
209: MSC_CL_COLLECTION.v_current_date,
210: MSC_CL_COLLECTION.v_current_user,
211: MSC_CL_COLLECTION.v_current_date,
212: MSC_CL_COLLECTION.v_current_user );
213:
214: c_count:= c_count+1;
215: total_count:= total_count+1;
208: MSC_CL_COLLECTION.v_last_collection_id,
209: MSC_CL_COLLECTION.v_current_date,
210: MSC_CL_COLLECTION.v_current_user,
211: MSC_CL_COLLECTION.v_current_date,
212: MSC_CL_COLLECTION.v_current_user );
213:
214: c_count:= c_count+1;
215: total_count:= total_count+1;
216:
213:
214: c_count:= c_count+1;
215: total_count:= total_count+1;
216:
217: IF c_count> MSC_CL_COLLECTION.PBS THEN
218: COMMIT;
219: c_count:= 0;
220: END IF;
221:
233:
234: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
235: RAISE;
236: ELSE
237: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
238:
239: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
240: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
241: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SETUP_TRANSITION');
245: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
246: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
247: FND_MESSAGE.SET_TOKEN('VALUE',
248: MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
249: MSC_CL_COLLECTION.v_instance_id));
250: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
251:
252: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
253: FND_MESSAGE.SET_TOKEN('COLUMN', 'RESOURCE_ID');
273: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Total resource transition = '|| to_char(total_count));
274:
275: COMMIT;
276:
277: END IF; /* MSC_CL_COLLECTION.v_is_complete_refresh) OR (v_is_partial_refresh */
278:
279: END LOAD_SETUP_TRANSITION;
280:
281: PROCEDURE LOAD_RESOURCE_CHARGES
299: lv_CHARGE_END_DATETIME DATE;
300:
301:
302: BEGIN
303: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
304: lv_res_req_tbl := 'RESOURCE_REQUIREMENTS_'||MSC_CL_COLLECTION.v_instance_code;
305: ELSE
306: lv_res_req_tbl := 'MSC_RESOURCE_REQUIREMENTS';
307: END IF;
300:
301:
302: BEGIN
303: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
304: lv_res_req_tbl := 'RESOURCE_REQUIREMENTS_'||MSC_CL_COLLECTION.v_instance_code;
305: ELSE
306: lv_res_req_tbl := 'MSC_RESOURCE_REQUIREMENTS';
307: END IF;
308:
318: ||' mrc.CHARGE_END_DATETIME,'
319: ||' mrc.DEPARTMENT_ID'
320: ||' FROM '||lv_res_req_tbl||' mrr,'
321: ||' MSC_ST_RESOURCE_CHARGES mrc'
322: ||' WHERE mrr.SR_INSTANCE_ID = '||MSC_CL_COLLECTION.v_instance_id
323: ||' AND mrr.PLAN_ID = -1'
324: ||' AND mrr.SR_INSTANCE_ID = mrc.SR_INSTANCE_ID'
325: ||' AND mrr.WIP_ENTITY_ID = mrc.WIP_ENTITY_ID'
326: ||' AND mrr.ORGANIZATION_ID = mrc.ORGANIZATION_ID'
370:
371: EXIT WHEN c4%NOTFOUND;
372:
373: BEGIN
374: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) Then
375: EXECUTE IMMEDIATE lv_sql_stmt
376: USING
377: lv_TRANSACTION_ID,
378: lv_SR_INSTANCE_ID,
379: lv_CHARGE_NUMBER,
380: lv_CHARGE_QUANTITY,
381: lv_CHARGE_START_DATETIME,
382: lv_CHARGE_END_DATETIME,
383: MSC_CL_COLLECTION.v_current_date,
384: MSC_CL_COLLECTION.v_current_user,
385: MSC_CL_COLLECTION.v_current_date,
386: MSC_CL_COLLECTION.v_current_user;
387: END IF;
380: lv_CHARGE_QUANTITY,
381: lv_CHARGE_START_DATETIME,
382: lv_CHARGE_END_DATETIME,
383: MSC_CL_COLLECTION.v_current_date,
384: MSC_CL_COLLECTION.v_current_user,
385: MSC_CL_COLLECTION.v_current_date,
386: MSC_CL_COLLECTION.v_current_user;
387: END IF;
388:
381: lv_CHARGE_START_DATETIME,
382: lv_CHARGE_END_DATETIME,
383: MSC_CL_COLLECTION.v_current_date,
384: MSC_CL_COLLECTION.v_current_user,
385: MSC_CL_COLLECTION.v_current_date,
386: MSC_CL_COLLECTION.v_current_user;
387: END IF;
388:
389: c_count:= c_count+1;
382: lv_CHARGE_END_DATETIME,
383: MSC_CL_COLLECTION.v_current_date,
384: MSC_CL_COLLECTION.v_current_user,
385: MSC_CL_COLLECTION.v_current_date,
386: MSC_CL_COLLECTION.v_current_user;
387: END IF;
388:
389: c_count:= c_count+1;
390: total_count:= total_count+1;
387: END IF;
388:
389: c_count:= c_count+1;
390: total_count:= total_count+1;
391: IF c_count> MSC_CL_COLLECTION.PBS THEN
392: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
393: COMMIT;
394: END IF;
395: c_count:= 0;
388:
389: c_count:= c_count+1;
390: total_count:= total_count+1;
391: IF c_count> MSC_CL_COLLECTION.PBS THEN
392: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
393: COMMIT;
394: END IF;
395: c_count:= 0;
396: END IF;
409: RAISE;
410:
411: ELSE
412:
413: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
414:
415: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
416: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
417: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE_CHARGES');
433:
434: END;
435: END LOOP;
436: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Total resource charges = '|| total_count);
437: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
438: COMMIT;
439: END IF;
440: EXCEPTION
441: WHEN OTHERS THEN
471: msric.ACTION_TYPE,
472: msric.DELETED_FLAG,
473: msric.SR_INSTANCE_ID
474: FROM MSC_ST_RES_INSTANCE_CHANGES msric
475: WHERE msric.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
476: ORDER BY
477: msric.DELETED_FLAG;
478:
479: c_count NUMBER:= 0;
484: lv_instance_code varchar2(10);
485:
486: Begin
487:
488: IF ((MSC_CL_COLLECTION.v_is_partial_refresh AND MSC_CL_COLLECTION.v_coll_prec.calendar_flag = MSC_UTIL.SYS_YES) OR
489: MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_incremental_refresh) then
490:
491: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
492:
485:
486: Begin
487:
488: IF ((MSC_CL_COLLECTION.v_is_partial_refresh AND MSC_CL_COLLECTION.v_coll_prec.calendar_flag = MSC_UTIL.SYS_YES) OR
489: MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_incremental_refresh) then
490:
491: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
492:
493: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RES_INSTANCE_CHANGES', MSC_CL_COLLECTION.v_instance_id, NULL);
487:
488: IF ((MSC_CL_COLLECTION.v_is_partial_refresh AND MSC_CL_COLLECTION.v_coll_prec.calendar_flag = MSC_UTIL.SYS_YES) OR
489: MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_incremental_refresh) then
490:
491: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
492:
493: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RES_INSTANCE_CHANGES', MSC_CL_COLLECTION.v_instance_id, NULL);
494:
495:
489: MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_incremental_refresh) then
490:
491: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
492:
493: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RES_INSTANCE_CHANGES', MSC_CL_COLLECTION.v_instance_id, NULL);
494:
495:
496: END IF;
497:
501: FOR c_rec IN res_inst_chngs LOOP
502:
503: BEGIN
504:
505: IF MSC_CL_COLLECTION.v_is_incremental_refresh AND c_rec.DELETED_FLAG= MSC_UTIL.SYS_YES THEN
506:
507: DELETE MSC_RES_INSTANCE_CHANGES
508: WHERE DEPARTMENT_ID = c_rec.DEPARTMENT_ID
509: AND RESOURCE_ID = c_rec.RESOURCE_ID
554: c_rec.CAPACITY_CHANGE,
555: c_rec.SIMULATION_SET,
556: c_rec.ACTION_TYPE,
557: c_rec.SR_INSTANCE_ID,
558: MSC_CL_COLLECTION.v_last_collection_id,
559: MSC_CL_COLLECTION.v_current_date,
560: MSC_CL_COLLECTION.v_current_user,
561: MSC_CL_COLLECTION.v_current_date,
562: MSC_CL_COLLECTION.v_current_user );
555: c_rec.SIMULATION_SET,
556: c_rec.ACTION_TYPE,
557: c_rec.SR_INSTANCE_ID,
558: MSC_CL_COLLECTION.v_last_collection_id,
559: MSC_CL_COLLECTION.v_current_date,
560: MSC_CL_COLLECTION.v_current_user,
561: MSC_CL_COLLECTION.v_current_date,
562: MSC_CL_COLLECTION.v_current_user );
563: END IF;
556: c_rec.ACTION_TYPE,
557: c_rec.SR_INSTANCE_ID,
558: MSC_CL_COLLECTION.v_last_collection_id,
559: MSC_CL_COLLECTION.v_current_date,
560: MSC_CL_COLLECTION.v_current_user,
561: MSC_CL_COLLECTION.v_current_date,
562: MSC_CL_COLLECTION.v_current_user );
563: END IF;
564:
557: c_rec.SR_INSTANCE_ID,
558: MSC_CL_COLLECTION.v_last_collection_id,
559: MSC_CL_COLLECTION.v_current_date,
560: MSC_CL_COLLECTION.v_current_user,
561: MSC_CL_COLLECTION.v_current_date,
562: MSC_CL_COLLECTION.v_current_user );
563: END IF;
564:
565: c_count:= c_count+1;
558: MSC_CL_COLLECTION.v_last_collection_id,
559: MSC_CL_COLLECTION.v_current_date,
560: MSC_CL_COLLECTION.v_current_user,
561: MSC_CL_COLLECTION.v_current_date,
562: MSC_CL_COLLECTION.v_current_user );
563: END IF;
564:
565: c_count:= c_count+1;
566: total_count:= total_count+1;
564:
565: c_count:= c_count+1;
566: total_count:= total_count+1;
567:
568: IF c_count> MSC_CL_COLLECTION.PBS THEN
569: COMMIT;
570: c_count:= 0;
571: END IF;
572:
584: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
585: RAISE;
586:
587: ELSE
588: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
589:
590: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
591: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
592: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RES_INST_CHANGE');
662: FROM MSC_ITEM_ID_LID t1,
663: MSC_ST_COMPONENT_SUBSTITUTES mscs
664: WHERE t1.SR_INVENTORY_ITEM_ID= mscs.substitute_item_id
665: AND t1.sr_instance_id= mscs.sr_instance_id
666: AND mscs.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
667: AND mscs.DELETED_FLAG= MSC_UTIL.SYS_NO;
668:
669: CURSOR c1_d IS
670: SELECT
676: MSC_ST_COMPONENT_SUBSTITUTES mscs
677: WHERE ((t1.SR_INVENTORY_ITEM_ID= mscs.substitute_item_id) AND (mscs.substitute_item_id
678: is NOT NULL))
679: AND t1.sr_instance_id= mscs.sr_instance_id
680: AND mscs.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
681: AND mscs.DELETED_FLAG= MSC_UTIL.SYS_YES
682: UNION ALL
683: SELECT
684: mscs.BILL_SEQUENCE_ID,
686: TO_NUMBER(NULL) SUBSTITUTE_ITEM_ID,
687: mscs.SR_INSTANCE_ID
688: FROM MSC_ST_COMPONENT_SUBSTITUTES mscs
689: WHERE mscs.substitute_item_id IS NULL
690: AND mscs.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
691: AND mscs.DELETED_FLAG= MSC_UTIL.SYS_YES;
692:
693:
694: c_count NUMBER:= 0;
696: lv_sql_stmt VARCHAR2(5000);
697:
698: BEGIN
699:
700: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
701: -- We want to delete all BOM related data and get new stuff.
702:
703: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_COMPONENT_SUBSTITUTES', MSC_CL_COLLECTION.v_instance_id, -1);
704:
699:
700: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
701: -- We want to delete all BOM related data and get new stuff.
702:
703: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_COMPONENT_SUBSTITUTES', MSC_CL_COLLECTION.v_instance_id, -1);
704:
705: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
706: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_COMPONENT_SUBSTITUTES', MSC_CL_COLLECTION.v_instance_id, -1);
707: ELSE
701: -- We want to delete all BOM related data and get new stuff.
702:
703: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_COMPONENT_SUBSTITUTES', MSC_CL_COLLECTION.v_instance_id, -1);
704:
705: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
706: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_COMPONENT_SUBSTITUTES', MSC_CL_COLLECTION.v_instance_id, -1);
707: ELSE
708: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
709: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_COMPONENT_SUBSTITUTES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
702:
703: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_COMPONENT_SUBSTITUTES', MSC_CL_COLLECTION.v_instance_id, -1);
704:
705: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
706: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_COMPONENT_SUBSTITUTES', MSC_CL_COLLECTION.v_instance_id, -1);
707: ELSE
708: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
709: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_COMPONENT_SUBSTITUTES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
710: END IF;
705: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
706: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_COMPONENT_SUBSTITUTES', MSC_CL_COLLECTION.v_instance_id, -1);
707: ELSE
708: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
709: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_COMPONENT_SUBSTITUTES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
710: END IF;
711:
712: END IF; -- MSC_CL_COLLECTION.v_is_complete_refresh
713:
708: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
709: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_COMPONENT_SUBSTITUTES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
710: END IF;
711:
712: END IF; -- MSC_CL_COLLECTION.v_is_complete_refresh
713:
714: c_count:= 0;
715:
716: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
712: END IF; -- MSC_CL_COLLECTION.v_is_complete_refresh
713:
714: c_count:= 0;
715:
716: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
717:
718: FOR c_rec IN c1_d LOOP
719:
720: 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
720: 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
721:
722: UPDATE MSC_COMPONENT_SUBSTITUTES
723: SET USAGE_QUANTITY= 0,
724: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
725: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
726: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
727: WHERE PLAN_ID= -1
728: AND BILL_SEQUENCE_ID= c_rec.BILL_SEQUENCE_ID
721:
722: UPDATE MSC_COMPONENT_SUBSTITUTES
723: SET USAGE_QUANTITY= 0,
724: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
725: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
726: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
727: WHERE PLAN_ID= -1
728: AND BILL_SEQUENCE_ID= c_rec.BILL_SEQUENCE_ID
729: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
722: UPDATE MSC_COMPONENT_SUBSTITUTES
723: SET USAGE_QUANTITY= 0,
724: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
725: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
726: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
727: WHERE PLAN_ID= -1
728: AND BILL_SEQUENCE_ID= c_rec.BILL_SEQUENCE_ID
729: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
730:
731: 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
732:
733: UPDATE MSC_COMPONENT_SUBSTITUTES
734: SET USAGE_QUANTITY= 0,
735: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
736: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
737: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
738: WHERE PLAN_ID= -1
739: AND COMPONENT_SEQUENCE_ID= c_rec.COMPONENT_SEQUENCE_ID
732:
733: UPDATE MSC_COMPONENT_SUBSTITUTES
734: SET USAGE_QUANTITY= 0,
735: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
736: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
737: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
738: WHERE PLAN_ID= -1
739: AND COMPONENT_SEQUENCE_ID= c_rec.COMPONENT_SEQUENCE_ID
740: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
733: UPDATE MSC_COMPONENT_SUBSTITUTES
734: SET USAGE_QUANTITY= 0,
735: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
736: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
737: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
738: WHERE PLAN_ID= -1
739: AND COMPONENT_SEQUENCE_ID= c_rec.COMPONENT_SEQUENCE_ID
740: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
741:
743: AND c_rec.BILL_SEQUENCE_ID IS NULL THEN
744:
745: UPDATE MSC_COMPONENT_SUBSTITUTES
746: SET USAGE_QUANTITY= 0,
747: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
748: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
749: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
750: WHERE PLAN_ID= -1
751: AND COMPONENT_SEQUENCE_ID= c_rec.COMPONENT_SEQUENCE_ID
744:
745: UPDATE MSC_COMPONENT_SUBSTITUTES
746: SET USAGE_QUANTITY= 0,
747: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
748: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
749: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
750: WHERE PLAN_ID= -1
751: AND COMPONENT_SEQUENCE_ID= c_rec.COMPONENT_SEQUENCE_ID
752: AND SUBSTITUTE_ITEM_ID= c_rec.SUBSTITUTE_ITEM_ID
745: UPDATE MSC_COMPONENT_SUBSTITUTES
746: SET USAGE_QUANTITY= 0,
747: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
748: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
749: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
750: WHERE PLAN_ID= -1
751: AND COMPONENT_SEQUENCE_ID= c_rec.COMPONENT_SEQUENCE_ID
752: AND SUBSTITUTE_ITEM_ID= c_rec.SUBSTITUTE_ITEM_ID
753: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
755: END IF; /* c_rec combinations */
756:
757: END LOOP; /* c1_d */
758:
759: END IF; /* MSC_CL_COLLECTION.v_is_incremental_refresh */
760:
761: COMMIT;
762:
763: c_count:= 0;
765: FOR c_rec IN c3 LOOP
766:
767: BEGIN
768:
769: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
770:
771: UPDATE MSC_COMPONENT_SUBSTITUTES
772: SET
773: USAGE_QUANTITY= c_rec.USAGE_QUANTITY,
772: SET
773: USAGE_QUANTITY= c_rec.USAGE_QUANTITY,
774: PRIORITY= c_rec.PRIORITY,
775: ROUNDING_DIRECTION= c_rec.ROUNDING_DIRECTION,
776: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
777: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
778: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
779: WHERE PLAN_ID= -1
780: AND BILL_SEQUENCE_ID= c_rec.Bill_Sequence_ID
773: USAGE_QUANTITY= c_rec.USAGE_QUANTITY,
774: PRIORITY= c_rec.PRIORITY,
775: ROUNDING_DIRECTION= c_rec.ROUNDING_DIRECTION,
776: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
777: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
778: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
779: WHERE PLAN_ID= -1
780: AND BILL_SEQUENCE_ID= c_rec.Bill_Sequence_ID
781: AND COMPONENT_SEQUENCE_ID= c_rec.COMPONENT_SEQUENCE_ID
774: PRIORITY= c_rec.PRIORITY,
775: ROUNDING_DIRECTION= c_rec.ROUNDING_DIRECTION,
776: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
777: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
778: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
779: WHERE PLAN_ID= -1
780: AND BILL_SEQUENCE_ID= c_rec.Bill_Sequence_ID
781: AND COMPONENT_SEQUENCE_ID= c_rec.COMPONENT_SEQUENCE_ID
782: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
785:
786: END IF;
787:
788:
789: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
790:
791: INSERT INTO MSC_COMPONENT_SUBSTITUTES
792: ( PLAN_ID,
793: BILL_SEQUENCE_ID,
812: c_rec.ORGANIZATION_ID,
813: c_rec.SUBSTITUTE_ITEM_ID,
814: c_rec.COMPONENT_SEQUENCE_ID,
815: c_rec.SR_INSTANCE_ID,
816: MSC_CL_COLLECTION.v_last_collection_id,
817: MSC_CL_COLLECTION.v_current_date,
818: MSC_CL_COLLECTION.v_current_user,
819: MSC_CL_COLLECTION.v_current_date,
820: MSC_CL_COLLECTION.v_current_user );
813: c_rec.SUBSTITUTE_ITEM_ID,
814: c_rec.COMPONENT_SEQUENCE_ID,
815: c_rec.SR_INSTANCE_ID,
816: MSC_CL_COLLECTION.v_last_collection_id,
817: MSC_CL_COLLECTION.v_current_date,
818: MSC_CL_COLLECTION.v_current_user,
819: MSC_CL_COLLECTION.v_current_date,
820: MSC_CL_COLLECTION.v_current_user );
821:
814: c_rec.COMPONENT_SEQUENCE_ID,
815: c_rec.SR_INSTANCE_ID,
816: MSC_CL_COLLECTION.v_last_collection_id,
817: MSC_CL_COLLECTION.v_current_date,
818: MSC_CL_COLLECTION.v_current_user,
819: MSC_CL_COLLECTION.v_current_date,
820: MSC_CL_COLLECTION.v_current_user );
821:
822: END IF;
815: c_rec.SR_INSTANCE_ID,
816: MSC_CL_COLLECTION.v_last_collection_id,
817: MSC_CL_COLLECTION.v_current_date,
818: MSC_CL_COLLECTION.v_current_user,
819: MSC_CL_COLLECTION.v_current_date,
820: MSC_CL_COLLECTION.v_current_user );
821:
822: END IF;
823:
816: MSC_CL_COLLECTION.v_last_collection_id,
817: MSC_CL_COLLECTION.v_current_date,
818: MSC_CL_COLLECTION.v_current_user,
819: MSC_CL_COLLECTION.v_current_date,
820: MSC_CL_COLLECTION.v_current_user );
821:
822: END IF;
823:
824: c_count:= c_count+1;
822: END IF;
823:
824: c_count:= c_count+1;
825:
826: IF c_count> MSC_CL_COLLECTION.PBS THEN
827: COMMIT;
828: c_count:= 0;
829: END IF;
830:
831: EXCEPTION
832:
833: WHEN OTHERS THEN
834:
835: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
836:
837: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
838: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
839: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_COMPONENT_SUBSTITUTE');
858: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
859: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
860: FND_MESSAGE.SET_TOKEN('VALUE',
861: MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
862: MSC_CL_COLLECTION.v_instance_id));
863: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
864:
865: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
866:
883: msb.ROLLUP_START_DATE,
884: msb.ROLLUP_COMPLETION_DATE,
885: msb.SR_INSTANCE_ID
886: FROM MSC_ST_BILL_OF_RESOURCES msb
887: WHERE msb.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
888:
889: CURSOR c2 IS
890: SELECT
891: msbr.BILL_OF_RESOURCES,
911: WHERE t1.SR_INVENTORY_ITEM_ID= msbr.assembly_item_id
912: AND t1.sr_instance_id= msbr.sr_instance_id
913: AND t2.SR_INVENTORY_ITEM_ID= msbr.source_item_id
914: AND t2.sr_instance_id= msbr.sr_instance_id
915: AND msbr.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
916:
917: c_count NUMBER:= 0;
918:
919: BEGIN
917: c_count NUMBER:= 0;
918:
919: BEGIN
920:
921: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
922:
923: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BILL_OF_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1);
924: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOR_REQUIREMENTS', MSC_CL_COLLECTION.v_instance_id, -1);
925:
919: BEGIN
920:
921: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
922:
923: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BILL_OF_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1);
924: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOR_REQUIREMENTS', MSC_CL_COLLECTION.v_instance_id, -1);
925:
926: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
927: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BILL_OF_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1);
920:
921: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
922:
923: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BILL_OF_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1);
924: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOR_REQUIREMENTS', MSC_CL_COLLECTION.v_instance_id, -1);
925:
926: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
927: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BILL_OF_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1);
928: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOR_REQUIREMENTS', MSC_CL_COLLECTION.v_instance_id, -1);
922:
923: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BILL_OF_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1);
924: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOR_REQUIREMENTS', MSC_CL_COLLECTION.v_instance_id, -1);
925:
926: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
927: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BILL_OF_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1);
928: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOR_REQUIREMENTS', MSC_CL_COLLECTION.v_instance_id, -1);
929: ELSE
930: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
923: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BILL_OF_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1);
924: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOR_REQUIREMENTS', MSC_CL_COLLECTION.v_instance_id, -1);
925:
926: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
927: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BILL_OF_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1);
928: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOR_REQUIREMENTS', MSC_CL_COLLECTION.v_instance_id, -1);
929: ELSE
930: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
931: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BILL_OF_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
924: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOR_REQUIREMENTS', MSC_CL_COLLECTION.v_instance_id, -1);
925:
926: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
927: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BILL_OF_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1);
928: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOR_REQUIREMENTS', MSC_CL_COLLECTION.v_instance_id, -1);
929: ELSE
930: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
931: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BILL_OF_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
932: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOR_REQUIREMENTS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
927: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BILL_OF_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1);
928: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOR_REQUIREMENTS', MSC_CL_COLLECTION.v_instance_id, -1);
929: ELSE
930: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
931: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BILL_OF_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
932: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOR_REQUIREMENTS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
933: END IF;
934:
935: END IF; -- MSC_CL_COLLECTION.v_is_complete_refresh
928: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOR_REQUIREMENTS', MSC_CL_COLLECTION.v_instance_id, -1);
929: ELSE
930: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
931: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BILL_OF_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
932: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOR_REQUIREMENTS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
933: END IF;
934:
935: END IF; -- MSC_CL_COLLECTION.v_is_complete_refresh
936:
931: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BILL_OF_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
932: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOR_REQUIREMENTS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
933: END IF;
934:
935: END IF; -- MSC_CL_COLLECTION.v_is_complete_refresh
936:
937: c_count:= 0;
938:
939: FOR c_rec IN c1 LOOP
939: FOR c_rec IN c1 LOOP
940:
941: BEGIN
942:
943: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
944:
945: UPDATE MSC_BILL_OF_RESOURCES
946: SET
947: DESCRIPTION= c_rec.DESCRIPTION,
947: DESCRIPTION= c_rec.DESCRIPTION,
948: DISABLE_DATE= c_rec.DISABLE_DATE,
949: ROLLUP_START_DATE= c_rec.ROLLUP_START_DATE,
950: ROLLUP_COMPLETION_DATE= c_rec.ROLLUP_COMPLETION_DATE,
951: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
952: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
953: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
954: WHERE PLAN_ID= -1
955: AND BILL_OF_RESOURCES= c_rec.BILL_OF_RESOURCES
948: DISABLE_DATE= c_rec.DISABLE_DATE,
949: ROLLUP_START_DATE= c_rec.ROLLUP_START_DATE,
950: ROLLUP_COMPLETION_DATE= c_rec.ROLLUP_COMPLETION_DATE,
951: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
952: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
953: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
954: WHERE PLAN_ID= -1
955: AND BILL_OF_RESOURCES= c_rec.BILL_OF_RESOURCES
956: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
949: ROLLUP_START_DATE= c_rec.ROLLUP_START_DATE,
950: ROLLUP_COMPLETION_DATE= c_rec.ROLLUP_COMPLETION_DATE,
951: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
952: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
953: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
954: WHERE PLAN_ID= -1
955: AND BILL_OF_RESOURCES= c_rec.BILL_OF_RESOURCES
956: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
957: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
958:
959: END IF;
960:
961:
962: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
963:
964: INSERT INTO MSC_BILL_OF_RESOURCES
965: ( PLAN_ID,
966: BILL_OF_RESOURCES,
983: c_rec.DISABLE_DATE,
984: c_rec.ROLLUP_START_DATE,
985: c_rec.ROLLUP_COMPLETION_DATE,
986: c_rec.SR_INSTANCE_ID,
987: MSC_CL_COLLECTION.v_last_collection_id,
988: MSC_CL_COLLECTION.v_current_date,
989: MSC_CL_COLLECTION.v_current_user,
990: MSC_CL_COLLECTION.v_current_date,
991: MSC_CL_COLLECTION.v_current_user );
984: c_rec.ROLLUP_START_DATE,
985: c_rec.ROLLUP_COMPLETION_DATE,
986: c_rec.SR_INSTANCE_ID,
987: MSC_CL_COLLECTION.v_last_collection_id,
988: MSC_CL_COLLECTION.v_current_date,
989: MSC_CL_COLLECTION.v_current_user,
990: MSC_CL_COLLECTION.v_current_date,
991: MSC_CL_COLLECTION.v_current_user );
992:
985: c_rec.ROLLUP_COMPLETION_DATE,
986: c_rec.SR_INSTANCE_ID,
987: MSC_CL_COLLECTION.v_last_collection_id,
988: MSC_CL_COLLECTION.v_current_date,
989: MSC_CL_COLLECTION.v_current_user,
990: MSC_CL_COLLECTION.v_current_date,
991: MSC_CL_COLLECTION.v_current_user );
992:
993: END IF;
986: c_rec.SR_INSTANCE_ID,
987: MSC_CL_COLLECTION.v_last_collection_id,
988: MSC_CL_COLLECTION.v_current_date,
989: MSC_CL_COLLECTION.v_current_user,
990: MSC_CL_COLLECTION.v_current_date,
991: MSC_CL_COLLECTION.v_current_user );
992:
993: END IF;
994:
987: MSC_CL_COLLECTION.v_last_collection_id,
988: MSC_CL_COLLECTION.v_current_date,
989: MSC_CL_COLLECTION.v_current_user,
990: MSC_CL_COLLECTION.v_current_date,
991: MSC_CL_COLLECTION.v_current_user );
992:
993: END IF;
994:
995: c_count:= c_count+1;
993: END IF;
994:
995: c_count:= c_count+1;
996:
997: IF c_count> MSC_CL_COLLECTION.PBS THEN
998: COMMIT;
999: c_count:= 0;
1000: END IF;
1001:
1013: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1014: RAISE;
1015:
1016: ELSE
1017: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1018:
1019: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1020: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1021: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOR');
1025: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1026: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
1027: FND_MESSAGE.SET_TOKEN('VALUE',
1028: MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
1029: MSC_CL_COLLECTION.v_instance_id));
1030: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1031:
1032: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1033: FND_MESSAGE.SET_TOKEN('COLUMN', 'BILL_OF_RESOURCES');
1048: FOR c_rec IN c2 LOOP
1049:
1050: BEGIN
1051:
1052: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
1053:
1054: UPDATE MSC_BOR_REQUIREMENTS
1055: SET
1056: BILL_OF_RESOURCES= c_rec.BILL_OF_RESOURCES,
1067: DEPARTMENT_ID= c_rec.DEPARTMENT_ID,
1068: ORGANIZATION_ID= c_rec.ORGANIZATION_ID,
1069: ASSEMBLY_ITEM_ID= c_rec.ASSEMBLY_ITEM_ID,
1070: SOURCE_ITEM_ID= c_rec.SOURCE_ITEM_ID,
1071: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1072: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1073: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1074: WHERE PLAN_ID= -1
1075: AND SR_TRANSACTION_ID= c_rec.SR_TRANSACTION_ID
1068: ORGANIZATION_ID= c_rec.ORGANIZATION_ID,
1069: ASSEMBLY_ITEM_ID= c_rec.ASSEMBLY_ITEM_ID,
1070: SOURCE_ITEM_ID= c_rec.SOURCE_ITEM_ID,
1071: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1072: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1073: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1074: WHERE PLAN_ID= -1
1075: AND SR_TRANSACTION_ID= c_rec.SR_TRANSACTION_ID
1076: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
1069: ASSEMBLY_ITEM_ID= c_rec.ASSEMBLY_ITEM_ID,
1070: SOURCE_ITEM_ID= c_rec.SOURCE_ITEM_ID,
1071: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1072: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1073: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1074: WHERE PLAN_ID= -1
1075: AND SR_TRANSACTION_ID= c_rec.SR_TRANSACTION_ID
1076: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
1077:
1077:
1078: END IF;
1079:
1080:
1081: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
1082:
1083: INSERT INTO MSC_BOR_REQUIREMENTS
1084: ( PLAN_ID,
1085: TRANSACTION_ID,
1124: c_rec.ORGANIZATION_ID,
1125: c_rec.ASSEMBLY_ITEM_ID,
1126: c_rec.SOURCE_ITEM_ID,
1127: c_rec.SR_INSTANCE_ID,
1128: MSC_CL_COLLECTION.v_last_collection_id,
1129: MSC_CL_COLLECTION.v_current_date,
1130: MSC_CL_COLLECTION.v_current_user,
1131: MSC_CL_COLLECTION.v_current_date,
1132: MSC_CL_COLLECTION.v_current_user );
1125: c_rec.ASSEMBLY_ITEM_ID,
1126: c_rec.SOURCE_ITEM_ID,
1127: c_rec.SR_INSTANCE_ID,
1128: MSC_CL_COLLECTION.v_last_collection_id,
1129: MSC_CL_COLLECTION.v_current_date,
1130: MSC_CL_COLLECTION.v_current_user,
1131: MSC_CL_COLLECTION.v_current_date,
1132: MSC_CL_COLLECTION.v_current_user );
1133:
1126: c_rec.SOURCE_ITEM_ID,
1127: c_rec.SR_INSTANCE_ID,
1128: MSC_CL_COLLECTION.v_last_collection_id,
1129: MSC_CL_COLLECTION.v_current_date,
1130: MSC_CL_COLLECTION.v_current_user,
1131: MSC_CL_COLLECTION.v_current_date,
1132: MSC_CL_COLLECTION.v_current_user );
1133:
1134: END IF;
1127: c_rec.SR_INSTANCE_ID,
1128: MSC_CL_COLLECTION.v_last_collection_id,
1129: MSC_CL_COLLECTION.v_current_date,
1130: MSC_CL_COLLECTION.v_current_user,
1131: MSC_CL_COLLECTION.v_current_date,
1132: MSC_CL_COLLECTION.v_current_user );
1133:
1134: END IF;
1135:
1128: MSC_CL_COLLECTION.v_last_collection_id,
1129: MSC_CL_COLLECTION.v_current_date,
1130: MSC_CL_COLLECTION.v_current_user,
1131: MSC_CL_COLLECTION.v_current_date,
1132: MSC_CL_COLLECTION.v_current_user );
1133:
1134: END IF;
1135:
1136: c_count:= c_count+1;
1134: END IF;
1135:
1136: c_count:= c_count+1;
1137:
1138: IF c_count> MSC_CL_COLLECTION.PBS THEN
1139: COMMIT;
1140: c_count:= 0;
1141: END IF;
1142:
1155: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1156: RAISE;
1157:
1158: ELSE
1159: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1160:
1161: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1162: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1163: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOR');
1213: msor.RECIPE
1214: FROM MSC_ITEM_ID_LID miil,
1215: MSC_ST_PROCESS_EFFECTIVITY msor
1216: WHERE miil.SR_INVENTORY_ITEM_ID= msor.ITEM_ID
1217: AND miil.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1218: AND msor.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1219: AND msor.DELETED_FLAG= MSC_UTIL.SYS_NO;
1220:
1221: CURSOR c8_d IS
1214: FROM MSC_ITEM_ID_LID miil,
1215: MSC_ST_PROCESS_EFFECTIVITY msor
1216: WHERE miil.SR_INVENTORY_ITEM_ID= msor.ITEM_ID
1217: AND miil.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1218: AND msor.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1219: AND msor.DELETED_FLAG= MSC_UTIL.SYS_NO;
1220:
1221: CURSOR c8_d IS
1222: SELECT
1228: msor.SR_INSTANCE_ID
1229: FROM MSC_ITEM_ID_LID miil,
1230: MSC_ST_PROCESS_EFFECTIVITY msor
1231: WHERE miil.SR_INVENTORY_ITEM_ID(+)= msor.ITEM_ID
1232: AND miil.SR_INSTANCE_ID(+)= MSC_CL_COLLECTION.v_instance_id
1233: AND msor.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1234: AND msor.DELETED_FLAG= MSC_UTIL.SYS_YES
1235: UNION ALL
1236: SELECT
1229: FROM MSC_ITEM_ID_LID miil,
1230: MSC_ST_PROCESS_EFFECTIVITY msor
1231: WHERE miil.SR_INVENTORY_ITEM_ID(+)= msor.ITEM_ID
1232: AND miil.SR_INSTANCE_ID(+)= MSC_CL_COLLECTION.v_instance_id
1233: AND msor.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1234: AND msor.DELETED_FLAG= MSC_UTIL.SYS_YES
1235: UNION ALL
1236: SELECT
1237: TO_NUMBER(NULL),
1243: FROM MSC_PROCESS_EFFECTIVITY mpe,
1244: MSC_ST_PROCESS_EFFECTIVITY mspe
1245: WHERE mspe.Bill_Sequence_ID= mpe.Bill_Sequence_ID
1246: AND mspe.Routing_Sequence_ID <> mpe.Routing_Sequence_ID
1247: AND mspe.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1248: AND mpe.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1249: AND mspe.DELETED_FLAG= MSC_UTIL.SYS_NO
1250: AND mpe.PLAN_ID= -1;
1251:
1244: MSC_ST_PROCESS_EFFECTIVITY mspe
1245: WHERE mspe.Bill_Sequence_ID= mpe.Bill_Sequence_ID
1246: AND mspe.Routing_Sequence_ID <> mpe.Routing_Sequence_ID
1247: AND mspe.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1248: AND mpe.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1249: AND mspe.DELETED_FLAG= MSC_UTIL.SYS_NO
1250: AND mpe.PLAN_ID= -1;
1251:
1252: CURSOR c_del_leg IS
1253: SELECT
1254: msr.ROUTING_SEQUENCE_ID,
1255: msr.BILL_SEQUENCE_ID
1256: FROM MSC_ST_ROUTINGS msr
1257: WHERE msr.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1258: UNION ALL
1259: SELECT
1260: TO_NUMBER(NULL),
1261: msb.BILL_SEQUENCE_ID
1259: SELECT
1260: TO_NUMBER(NULL),
1261: msb.BILL_SEQUENCE_ID
1262: FROM MSC_ST_BOMS msb
1263: WHERE msb.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1264: c_count NUMBER:= 0;
1265: lv_tbl VARCHAR2(30);
1266: lv_sql_stmt VARCHAR2(5000);
1267: lv_sql_stmt1 VARCHAR2(5000);
1269: lv_val_2 NUMBER;
1270: lv_val_3 NUMBER;
1271:
1272: BEGIN
1273: IF MSC_CL_COLLECTION.v_instance_type = MSC_UTIL.G_INS_OTHER THEN
1274:
1275: -- deleting if the record already existed.
1276: FOR c_rec IN c_del_leg LOOP
1277:
1276: FOR c_rec IN c_del_leg LOOP
1277:
1278: DELETE MSC_PROCESS_EFFECTIVITY
1279: WHERE PLAN_ID= -1
1280: AND SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1281: AND ( ROUTING_SEQUENCE_ID = c_rec.ROUTING_SEQUENCE_ID
1282: OR BILL_SEQUENCE_ID= c_rec.BILL_SEQUENCE_ID );
1283:
1284: END LOOP;
1285:
1286: ELSE
1287:
1288:
1289: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
1290:
1291: FOR c_rec IN c8_d LOOP
1292: ---5470477
1293: lv_sql_stmt1 := ' DELETE MSC_PROCESS_EFFECTIVITY '
1364: END LOOP;
1365:
1366: END IF;
1367:
1368: END IF; -- v_instance_type = MSC_CL_COLLECTION.G_INS_OTHER
1369:
1370: c_count:= 0;
1371:
1372: FOR c_rec IN c8 LOOP
1386: LOAD_DISTRIBUTION_PRIORITY= c_rec.LOAD_DISTRIBUTION_PRIORITY,
1387: ITEM_PROCESS_COST= c_rec.ITEM_PROCESS_COST,
1388: EFFECTIVITY_DATE= c_rec.EFFECTIVITY_DATE,
1389: DISABLE_DATE= c_rec.DISABLE_DATE,
1390: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1391: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1392: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
1393: RECIPE = c_rec.RECIPE
1394: WHERE PLAN_ID= -1
1387: ITEM_PROCESS_COST= c_rec.ITEM_PROCESS_COST,
1388: EFFECTIVITY_DATE= c_rec.EFFECTIVITY_DATE,
1389: DISABLE_DATE= c_rec.DISABLE_DATE,
1390: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1391: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1392: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
1393: RECIPE = c_rec.RECIPE
1394: WHERE PLAN_ID= -1
1395: AND ITEM_ID= c_rec.INVENTORY_ITEM_ID
1388: EFFECTIVITY_DATE= c_rec.EFFECTIVITY_DATE,
1389: DISABLE_DATE= c_rec.DISABLE_DATE,
1390: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1391: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1392: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
1393: RECIPE = c_rec.RECIPE
1394: WHERE PLAN_ID= -1
1395: AND ITEM_ID= c_rec.INVENTORY_ITEM_ID
1396: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
1451: c_rec.LOAD_DISTRIBUTION_PRIORITY,
1452: c_rec.ITEM_PROCESS_COST,
1453: c_rec.RECIPE,
1454: c_rec.SR_INSTANCE_ID,
1455: MSC_CL_COLLECTION.v_last_collection_id,
1456: MSC_CL_COLLECTION.v_current_date,
1457: MSC_CL_COLLECTION.v_current_user,
1458: MSC_CL_COLLECTION.v_current_date,
1459: MSC_CL_COLLECTION.v_current_user );
1452: c_rec.ITEM_PROCESS_COST,
1453: c_rec.RECIPE,
1454: c_rec.SR_INSTANCE_ID,
1455: MSC_CL_COLLECTION.v_last_collection_id,
1456: MSC_CL_COLLECTION.v_current_date,
1457: MSC_CL_COLLECTION.v_current_user,
1458: MSC_CL_COLLECTION.v_current_date,
1459: MSC_CL_COLLECTION.v_current_user );
1460:
1453: c_rec.RECIPE,
1454: c_rec.SR_INSTANCE_ID,
1455: MSC_CL_COLLECTION.v_last_collection_id,
1456: MSC_CL_COLLECTION.v_current_date,
1457: MSC_CL_COLLECTION.v_current_user,
1458: MSC_CL_COLLECTION.v_current_date,
1459: MSC_CL_COLLECTION.v_current_user );
1460:
1461: END IF; -- SQL%NOTFOUND
1454: c_rec.SR_INSTANCE_ID,
1455: MSC_CL_COLLECTION.v_last_collection_id,
1456: MSC_CL_COLLECTION.v_current_date,
1457: MSC_CL_COLLECTION.v_current_user,
1458: MSC_CL_COLLECTION.v_current_date,
1459: MSC_CL_COLLECTION.v_current_user );
1460:
1461: END IF; -- SQL%NOTFOUND
1462:
1455: MSC_CL_COLLECTION.v_last_collection_id,
1456: MSC_CL_COLLECTION.v_current_date,
1457: MSC_CL_COLLECTION.v_current_user,
1458: MSC_CL_COLLECTION.v_current_date,
1459: MSC_CL_COLLECTION.v_current_user );
1460:
1461: END IF; -- SQL%NOTFOUND
1462:
1463: c_count:= c_count+1;
1461: END IF; -- SQL%NOTFOUND
1462:
1463: c_count:= c_count+1;
1464:
1465: IF c_count> MSC_CL_COLLECTION.PBS THEN
1466: COMMIT;
1467: c_count:= 0;
1468: END IF;
1469:
1481: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1482: RAISE;
1483:
1484: ELSE
1485: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1486:
1487: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1488: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1489: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_PROCESS_EFFECTIVITY');
1498: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1499: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
1500: FND_MESSAGE.SET_TOKEN('VALUE',
1501: MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
1502: MSC_CL_COLLECTION.v_instance_id));
1503: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1504:
1505: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1506: FND_MESSAGE.SET_TOKEN('COLUMN', 'BILL_SEQUENCE_ID');
1519: END LOOP;
1520:
1521: /* bug 1244578 fix */
1522: /* if it's complete refresh, delete the old records after the insert/update */
1523: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
1524: -- We want to delete all BOM related data and get new stuff.
1525:
1526: /* DELETE MSC_PROCESS_EFFECTIVITY
1527: WHERE PLAN_ID= -1
1524: -- We want to delete all BOM related data and get new stuff.
1525:
1526: /* DELETE MSC_PROCESS_EFFECTIVITY
1527: WHERE PLAN_ID= -1
1528: AND SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1529: AND REFRESH_NUMBER <> MSC_CL_COLLECTION.v_last_collection_id; */
1530:
1531: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1532: v_sub_str :='AND REFRESH_NUMBER <>'||MSC_CL_COLLECTION.v_last_collection_id;
1525:
1526: /* DELETE MSC_PROCESS_EFFECTIVITY
1527: WHERE PLAN_ID= -1
1528: AND SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1529: AND REFRESH_NUMBER <> MSC_CL_COLLECTION.v_last_collection_id; */
1530:
1531: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1532: v_sub_str :='AND REFRESH_NUMBER <>'||MSC_CL_COLLECTION.v_last_collection_id;
1533: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_PROCESS_EFFECTIVITY', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
1527: WHERE PLAN_ID= -1
1528: AND SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1529: AND REFRESH_NUMBER <> MSC_CL_COLLECTION.v_last_collection_id; */
1530:
1531: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1532: v_sub_str :='AND REFRESH_NUMBER <>'||MSC_CL_COLLECTION.v_last_collection_id;
1533: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_PROCESS_EFFECTIVITY', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
1534: ELSE
1535: v_sub_str :=' AND ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1528: AND SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1529: AND REFRESH_NUMBER <> MSC_CL_COLLECTION.v_last_collection_id; */
1530:
1531: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1532: v_sub_str :='AND REFRESH_NUMBER <>'||MSC_CL_COLLECTION.v_last_collection_id;
1533: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_PROCESS_EFFECTIVITY', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
1534: ELSE
1535: v_sub_str :=' AND ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1536: ||' AND REFRESH_NUMBER <>'||MSC_CL_COLLECTION.v_last_collection_id;
1529: AND REFRESH_NUMBER <> MSC_CL_COLLECTION.v_last_collection_id; */
1530:
1531: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1532: v_sub_str :='AND REFRESH_NUMBER <>'||MSC_CL_COLLECTION.v_last_collection_id;
1533: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_PROCESS_EFFECTIVITY', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
1534: ELSE
1535: v_sub_str :=' AND ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1536: ||' AND REFRESH_NUMBER <>'||MSC_CL_COLLECTION.v_last_collection_id;
1537: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_PROCESS_EFFECTIVITY', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
1532: v_sub_str :='AND REFRESH_NUMBER <>'||MSC_CL_COLLECTION.v_last_collection_id;
1533: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_PROCESS_EFFECTIVITY', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
1534: ELSE
1535: v_sub_str :=' AND ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1536: ||' AND REFRESH_NUMBER <>'||MSC_CL_COLLECTION.v_last_collection_id;
1537: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_PROCESS_EFFECTIVITY', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
1538: END IF;
1539:
1540:
1533: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_PROCESS_EFFECTIVITY', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
1534: ELSE
1535: v_sub_str :=' AND ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1536: ||' AND REFRESH_NUMBER <>'||MSC_CL_COLLECTION.v_last_collection_id;
1537: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_PROCESS_EFFECTIVITY', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
1538: END IF;
1539:
1540:
1541:
1565: FROM MSC_ITEM_ID_LID t1,
1566: MSC_ST_BOMS msb
1567: WHERE t1.SR_INVENTORY_ITEM_ID= msb.assembly_item_id
1568: AND t1.sr_instance_id= msb.sr_instance_id
1569: AND msb.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1570: AND msb.DELETED_FLAG= MSC_UTIL.SYS_NO;
1571:
1572: CURSOR c2_d IS
1573: SELECT
1573: SELECT
1574: msb.BILL_SEQUENCE_ID,
1575: msb.SR_INSTANCE_ID
1576: FROM MSC_ST_BOMS msb
1577: WHERE msb.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1578: AND msb.DELETED_FLAG= MSC_UTIL.SYS_YES;
1579:
1580:
1581: lv_tbl VARCHAR2(30);
1649: ||' :v_current_user,'
1650: ||' :v_current_date,'
1651: ||' :v_current_user )';
1652:
1653: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
1654:
1655: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1656: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOMS', MSC_CL_COLLECTION.v_instance_id, -1);
1657: ELSE
1651: ||' :v_current_user )';
1652:
1653: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
1654:
1655: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1656: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOMS', MSC_CL_COLLECTION.v_instance_id, -1);
1657: ELSE
1658: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1659: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOMS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
1652:
1653: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
1654:
1655: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1656: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOMS', MSC_CL_COLLECTION.v_instance_id, -1);
1657: ELSE
1658: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1659: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOMS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
1660: END IF;
1655: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1656: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOMS', MSC_CL_COLLECTION.v_instance_id, -1);
1657: ELSE
1658: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1659: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOMS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
1660: END IF;
1661:
1662: BEGIN
1663: lv_sql_ins :=
1702: ||' FROM MSC_ITEM_ID_LID t1,'
1703: ||' MSC_ST_BOMS msb '
1704: ||' WHERE t1.SR_INVENTORY_ITEM_ID= msb.assembly_item_id'
1705: ||' AND t1.sr_instance_id= msb.sr_instance_id'
1706: ||' AND msb.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id
1707: ||' AND msb.DELETED_FLAG= '||MSC_UTIL.SYS_NO;
1708:
1709: EXECUTE IMMEDIATE lv_sql_ins
1710: 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;
1706: ||' AND msb.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id
1707: ||' AND msb.DELETED_FLAG= '||MSC_UTIL.SYS_NO;
1708:
1709: EXECUTE IMMEDIATE lv_sql_ins
1710: 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;
1711:
1712: commit;
1713: EXCEPTION
1714: WHEN OTHERS THEN
1738: lb_refresh_failed := TRUE;
1739: END IF;
1740: END;
1741:
1742: END IF; -- MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh
1743:
1744:
1745:
1746: --Incremental Refresh or the above complete refresh or partial refresh has
1744:
1745:
1746: --Incremental Refresh or the above complete refresh or partial refresh has
1747: --failed.
1748: IF MSC_CL_COLLECTION.v_is_incremental_refresh OR lb_refresh_failed THEN
1749:
1750: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
1751: FOR c_rec IN c2_d LOOP
1752: DELETE MSC_BOMS
1746: --Incremental Refresh or the above complete refresh or partial refresh has
1747: --failed.
1748: IF MSC_CL_COLLECTION.v_is_incremental_refresh OR lb_refresh_failed THEN
1749:
1750: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
1751: FOR c_rec IN c2_d LOOP
1752: DELETE MSC_BOMS
1753: WHERE PLAN_ID= -1
1754: AND BILL_SEQUENCE_ID= c_rec.BILL_SEQUENCE_ID
1802:
1803: FOR j IN 1..lb_ASSEMBLY_TYPE.COUNT LOOP
1804:
1805: BEGIN
1806: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
1807:
1808: UPDATE MSC_BOMS
1809: SET
1810: ASSEMBLY_TYPE= lb_ASSEMBLY_TYPE(j),
1815: ASSEMBLY_QUANTITY= lb_ASSEMBLY_QUANTITY(j),
1816: UOM= lb_UOM(j),
1817: ORGANIZATION_ID= lb_ORGANIZATION_ID(j),
1818: ASSEMBLY_ITEM_ID= lb_ASSEMBLY_ITEM_ID(j),
1819: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1820: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1821: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1822: WHERE PLAN_ID= -1
1823: AND BILL_SEQUENCE_ID= lb_BILL_SEQUENCE_ID(j)
1816: UOM= lb_UOM(j),
1817: ORGANIZATION_ID= lb_ORGANIZATION_ID(j),
1818: ASSEMBLY_ITEM_ID= lb_ASSEMBLY_ITEM_ID(j),
1819: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1820: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1821: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1822: WHERE PLAN_ID= -1
1823: AND BILL_SEQUENCE_ID= lb_BILL_SEQUENCE_ID(j)
1824: AND SR_INSTANCE_ID= lb_SR_INSTANCE_ID(j);
1817: ORGANIZATION_ID= lb_ORGANIZATION_ID(j),
1818: ASSEMBLY_ITEM_ID= lb_ASSEMBLY_ITEM_ID(j),
1819: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1820: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1821: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1822: WHERE PLAN_ID= -1
1823: AND BILL_SEQUENCE_ID= lb_BILL_SEQUENCE_ID(j)
1824: AND SR_INSTANCE_ID= lb_SR_INSTANCE_ID(j);
1825:
1824: AND SR_INSTANCE_ID= lb_SR_INSTANCE_ID(j);
1825:
1826: END IF;
1827:
1828: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
1829:
1830: EXECUTE IMMEDIATE lv_sql_stmt
1831: USING
1832: lb_ASSEMBLY_TYPE(j),
1840: lb_ASSEMBLY_ITEM_ID(j),
1841: lb_BILL_SEQUENCE_ID(j),
1842: lb_SR_INSTANCE_ID(j),
1843: lb_operation_seq_num(j),
1844: MSC_CL_COLLECTION.v_last_collection_id,
1845: MSC_CL_COLLECTION.v_current_date,
1846: MSC_CL_COLLECTION.v_current_user,
1847: MSC_CL_COLLECTION.v_current_date,
1848: MSC_CL_COLLECTION.v_current_user;
1841: lb_BILL_SEQUENCE_ID(j),
1842: lb_SR_INSTANCE_ID(j),
1843: lb_operation_seq_num(j),
1844: MSC_CL_COLLECTION.v_last_collection_id,
1845: MSC_CL_COLLECTION.v_current_date,
1846: MSC_CL_COLLECTION.v_current_user,
1847: MSC_CL_COLLECTION.v_current_date,
1848: MSC_CL_COLLECTION.v_current_user;
1849: END IF; -- SQL%NOTFOUND
1842: lb_SR_INSTANCE_ID(j),
1843: lb_operation_seq_num(j),
1844: MSC_CL_COLLECTION.v_last_collection_id,
1845: MSC_CL_COLLECTION.v_current_date,
1846: MSC_CL_COLLECTION.v_current_user,
1847: MSC_CL_COLLECTION.v_current_date,
1848: MSC_CL_COLLECTION.v_current_user;
1849: END IF; -- SQL%NOTFOUND
1850:
1843: lb_operation_seq_num(j),
1844: MSC_CL_COLLECTION.v_last_collection_id,
1845: MSC_CL_COLLECTION.v_current_date,
1846: MSC_CL_COLLECTION.v_current_user,
1847: MSC_CL_COLLECTION.v_current_date,
1848: MSC_CL_COLLECTION.v_current_user;
1849: END IF; -- SQL%NOTFOUND
1850:
1851: c_count:= c_count+1;
1844: MSC_CL_COLLECTION.v_last_collection_id,
1845: MSC_CL_COLLECTION.v_current_date,
1846: MSC_CL_COLLECTION.v_current_user,
1847: MSC_CL_COLLECTION.v_current_date,
1848: MSC_CL_COLLECTION.v_current_user;
1849: END IF; -- SQL%NOTFOUND
1850:
1851: c_count:= c_count+1;
1852:
1849: END IF; -- SQL%NOTFOUND
1850:
1851: c_count:= c_count+1;
1852:
1853: IF c_count> MSC_CL_COLLECTION.PBS THEN
1854: COMMIT;
1855: c_count:= 0;
1856: END IF;
1857:
1869: RAISE;
1870:
1871: ELSE
1872:
1873: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1874:
1875: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1876: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1877: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOM');
1948: AND t2.SR_INVENTORY_ITEM_ID= msbc.using_assembly_id
1949: AND t2.sr_instance_id= msbc.sr_instance_id
1950: AND t3.SR_INVENTORY_ITEM_ID(+)= msbc.driving_item_id
1951: AND t3.sr_instance_id(+)= msbc.sr_instance_id
1952: AND msbc.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1953: AND msbc.DELETED_FLAG= MSC_UTIL.SYS_NO;
1954:
1955: CURSOR c1_d IS
1956: SELECT
1957: msbc.COMPONENT_SEQUENCE_ID,
1958: msbc.BILL_SEQUENCE_ID,
1959: msbc.SR_INSTANCE_ID
1960: FROM MSC_ST_BOM_COMPONENTS msbc
1961: WHERE msbc.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1962: AND msbc.DELETED_FLAG= MSC_UTIL.SYS_YES;
1963:
1964:
1965: lv_tbl VARCHAR2(30);
2015:
2016: BEGIN
2017:
2018:
2019: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
2020: lv_tbl:= 'BOM_COMPONENTS_'||MSC_CL_COLLECTION.v_instance_code;
2021: ELSE
2022: lv_tbl:= 'MSC_BOM_COMPONENTS';
2023: END IF;
2016: BEGIN
2017:
2018:
2019: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
2020: lv_tbl:= 'BOM_COMPONENTS_'||MSC_CL_COLLECTION.v_instance_code;
2021: ELSE
2022: lv_tbl:= 'MSC_BOM_COMPONENTS';
2023: END IF;
2024:
2104: ||' :v_current_date,'
2105: ||' :v_current_user)';
2106:
2107: --fix for the bug#3283959
2108: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
2109:
2110: if (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO) then
2111: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOM_COMPONENTS', MSC_CL_COLLECTION.v_instance_id, -1);
2112: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2106:
2107: --fix for the bug#3283959
2108: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
2109:
2110: if (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO) then
2111: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOM_COMPONENTS', MSC_CL_COLLECTION.v_instance_id, -1);
2112: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2113: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOM_COMPONENTS', MSC_CL_COLLECTION.v_instance_id, -1);
2114: ELSE
2107: --fix for the bug#3283959
2108: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
2109:
2110: if (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO) then
2111: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOM_COMPONENTS', MSC_CL_COLLECTION.v_instance_id, -1);
2112: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2113: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOM_COMPONENTS', MSC_CL_COLLECTION.v_instance_id, -1);
2114: ELSE
2115: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2108: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
2109:
2110: if (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO) then
2111: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOM_COMPONENTS', MSC_CL_COLLECTION.v_instance_id, -1);
2112: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2113: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOM_COMPONENTS', MSC_CL_COLLECTION.v_instance_id, -1);
2114: ELSE
2115: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2116: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOM_COMPONENTS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
2109:
2110: if (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO) then
2111: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOM_COMPONENTS', MSC_CL_COLLECTION.v_instance_id, -1);
2112: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2113: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOM_COMPONENTS', MSC_CL_COLLECTION.v_instance_id, -1);
2114: ELSE
2115: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2116: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOM_COMPONENTS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
2117: END IF;
2112: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2113: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOM_COMPONENTS', MSC_CL_COLLECTION.v_instance_id, -1);
2114: ELSE
2115: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2116: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOM_COMPONENTS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
2117: END IF;
2118: end if;
2119:
2120: BEGIN
2209: ||' AND t2.SR_INVENTORY_ITEM_ID = msbc.using_assembly_id'
2210: ||' AND t2.sr_instance_id = msbc.sr_instance_id'
2211: ||' AND t3.SR_INVENTORY_ITEM_ID(+) = msbc.driving_item_id'
2212: ||' AND t3.sr_instance_id(+) = msbc.sr_instance_id'
2213: ||' AND msbc.SR_INSTANCE_ID = '||MSC_CL_COLLECTION.v_instance_id
2214: ||' AND msbc.DELETED_FLAG = '||MSC_UTIL.SYS_NO;
2215:
2216: EXECUTE IMMEDIATE lv_sql_ins
2217: 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;
2213: ||' AND msbc.SR_INSTANCE_ID = '||MSC_CL_COLLECTION.v_instance_id
2214: ||' AND msbc.DELETED_FLAG = '||MSC_UTIL.SYS_NO;
2215:
2216: EXECUTE IMMEDIATE lv_sql_ins
2217: 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;
2218:
2219: commit;
2220: EXCEPTION
2221: WHEN OTHERS THEN
2245: lb_refresh_failed := TRUE;
2246: END IF;
2247: END;
2248:
2249: END IF; --MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh
2250:
2251: --Incremental Refresh or the above complete refresh or partial refresh has
2252: --failed.
2253: IF MSC_CL_COLLECTION.v_is_incremental_refresh OR lb_refresh_failed THEN
2249: END IF; --MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh
2250:
2251: --Incremental Refresh or the above complete refresh or partial refresh has
2252: --failed.
2253: IF MSC_CL_COLLECTION.v_is_incremental_refresh OR lb_refresh_failed THEN
2254:
2255: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
2256:
2257: -- set SR_INSTANCE_ID to negative to indicate a SOFT delete
2251: --Incremental Refresh or the above complete refresh or partial refresh has
2252: --failed.
2253: IF MSC_CL_COLLECTION.v_is_incremental_refresh OR lb_refresh_failed THEN
2254:
2255: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
2256:
2257: -- set SR_INSTANCE_ID to negative to indicate a SOFT delete
2258:
2259: FOR c_rec IN c1_d LOOP
2275: END IF;
2276: BEGIN
2277: IF (c_rec.BILL_SEQUENCE_ID IS NOT NULL) THEN
2278: IF (c_rec.COMPONENT_SEQUENCE_ID IS NOT NULL) THEN
2279: 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 ;
2280: ELSE
2281: 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;
2282: END IF;
2283: ELSE
2277: IF (c_rec.BILL_SEQUENCE_ID IS NOT NULL) THEN
2278: IF (c_rec.COMPONENT_SEQUENCE_ID IS NOT NULL) THEN
2279: 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 ;
2280: ELSE
2281: 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;
2282: END IF;
2283: ELSE
2284: IF (c_rec.COMPONENT_SEQUENCE_ID IS NOT NULL) THEN
2285: 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 ;
2281: 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;
2282: END IF;
2283: ELSE
2284: IF (c_rec.COMPONENT_SEQUENCE_ID IS NOT NULL) THEN
2285: 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 ;
2286: /*ELSE --condition should not arise. even if it does, we should not delete
2287: 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; */
2288: END IF;
2289:
2283: ELSE
2284: IF (c_rec.COMPONENT_SEQUENCE_ID IS NOT NULL) THEN
2285: 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 ;
2286: /*ELSE --condition should not arise. even if it does, we should not delete
2287: 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; */
2288: END IF;
2289:
2290: END IF;
2291:
2300:
2301: /*
2302: UPDATE MSC_BOM_COMPONENTS
2303: SET USAGE_QUANTITY= 0,
2304: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2305: LAST_UPDATE_DATE= v_current_date,
2306: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2307: WHERE PLAN_ID= -1
2308: AND BILL_SEQUENCE_ID= NVL(c_rec.BILL_SEQUENCE_ID, BILL_SEQUENCE_ID)
2302: UPDATE MSC_BOM_COMPONENTS
2303: SET USAGE_QUANTITY= 0,
2304: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2305: LAST_UPDATE_DATE= v_current_date,
2306: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2307: WHERE PLAN_ID= -1
2308: AND BILL_SEQUENCE_ID= NVL(c_rec.BILL_SEQUENCE_ID, BILL_SEQUENCE_ID)
2309: AND COMPONENT_SEQUENCE_ID= NVL(c_rec.COMPONENT_SEQUENCE_ID, COMPONENT_SEQUENCE_ID)
2310: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
2380: FOR j IN 1..lb_COMPONENT_SEQUENCE_ID.COUNT LOOP
2381:
2382: BEGIN
2383:
2384: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
2385:
2386: UPDATE MSC_BOM_COMPONENTS
2387: SET
2388: ORGANIZATION_ID= lb_ORGANIZATION_ID(j),
2414: ROUNDING_DIRECTION = lb_ROUNDING_DIRECTION(j),
2415: PRIMARY_FLAG = lb_PRIMARY_FLAG(j),
2416: CONTRIBUTE_TO_STEP_QTY = lb_CONTRIBUTE_TO_STEP_QTY(j),
2417: OLD_COMPONENT_SEQUENCE_ID = lb_OLD_COMPONENT_SEQUENCE_ID(j),
2418: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2419: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2420: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2421: WHERE PLAN_ID= -1
2422: AND BILL_SEQUENCE_ID= lb_BILL_SEQUENCE_ID(j)
2415: PRIMARY_FLAG = lb_PRIMARY_FLAG(j),
2416: CONTRIBUTE_TO_STEP_QTY = lb_CONTRIBUTE_TO_STEP_QTY(j),
2417: OLD_COMPONENT_SEQUENCE_ID = lb_OLD_COMPONENT_SEQUENCE_ID(j),
2418: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2419: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2420: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2421: WHERE PLAN_ID= -1
2422: AND BILL_SEQUENCE_ID= lb_BILL_SEQUENCE_ID(j)
2423: AND COMPONENT_SEQUENCE_ID= lb_COMPONENT_SEQUENCE_ID(j)
2416: CONTRIBUTE_TO_STEP_QTY = lb_CONTRIBUTE_TO_STEP_QTY(j),
2417: OLD_COMPONENT_SEQUENCE_ID = lb_OLD_COMPONENT_SEQUENCE_ID(j),
2418: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2419: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2420: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2421: WHERE PLAN_ID= -1
2422: AND BILL_SEQUENCE_ID= lb_BILL_SEQUENCE_ID(j)
2423: AND COMPONENT_SEQUENCE_ID= lb_COMPONENT_SEQUENCE_ID(j)
2424: AND SR_INSTANCE_ID= lb_SR_INSTANCE_ID(j);
2424: AND SR_INSTANCE_ID= lb_SR_INSTANCE_ID(j);
2425:
2426: END IF;
2427:
2428: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
2429:
2430: EXECUTE IMMEDIATE lv_sql_stmt
2431: USING
2432: lb_COMPONENT_SEQUENCE_ID(j),
2461: lb_ROUNDING_DIRECTION(j),
2462: lb_PRIMARY_FLAG(j),
2463: lb_CONTRIBUTE_TO_STEP_QTY(j),
2464: lb_OLD_COMPONENT_SEQUENCE_ID(j),
2465: MSC_CL_COLLECTION.v_last_collection_id,
2466: MSC_CL_COLLECTION.v_current_date,
2467: MSC_CL_COLLECTION.v_current_user,
2468: MSC_CL_COLLECTION.v_current_date,
2469: MSC_CL_COLLECTION.v_current_user;
2462: lb_PRIMARY_FLAG(j),
2463: lb_CONTRIBUTE_TO_STEP_QTY(j),
2464: lb_OLD_COMPONENT_SEQUENCE_ID(j),
2465: MSC_CL_COLLECTION.v_last_collection_id,
2466: MSC_CL_COLLECTION.v_current_date,
2467: MSC_CL_COLLECTION.v_current_user,
2468: MSC_CL_COLLECTION.v_current_date,
2469: MSC_CL_COLLECTION.v_current_user;
2470: END IF; -- SQL%NOTFOUND
2463: lb_CONTRIBUTE_TO_STEP_QTY(j),
2464: lb_OLD_COMPONENT_SEQUENCE_ID(j),
2465: MSC_CL_COLLECTION.v_last_collection_id,
2466: MSC_CL_COLLECTION.v_current_date,
2467: MSC_CL_COLLECTION.v_current_user,
2468: MSC_CL_COLLECTION.v_current_date,
2469: MSC_CL_COLLECTION.v_current_user;
2470: END IF; -- SQL%NOTFOUND
2471:
2464: lb_OLD_COMPONENT_SEQUENCE_ID(j),
2465: MSC_CL_COLLECTION.v_last_collection_id,
2466: MSC_CL_COLLECTION.v_current_date,
2467: MSC_CL_COLLECTION.v_current_user,
2468: MSC_CL_COLLECTION.v_current_date,
2469: MSC_CL_COLLECTION.v_current_user;
2470: END IF; -- SQL%NOTFOUND
2471:
2472:
2465: MSC_CL_COLLECTION.v_last_collection_id,
2466: MSC_CL_COLLECTION.v_current_date,
2467: MSC_CL_COLLECTION.v_current_user,
2468: MSC_CL_COLLECTION.v_current_date,
2469: MSC_CL_COLLECTION.v_current_user;
2470: END IF; -- SQL%NOTFOUND
2471:
2472:
2473:
2485: RAISE;
2486:
2487: ELSE
2488:
2489: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2490:
2491: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2492: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2493: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOM_COMPONENTS');
2518: END IF;
2519:
2520: BEGIN
2521:
2522: 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
2523:
2524: lv_tbl:= 'BOM_COMPONENTS_'||MSC_CL_COLLECTION.v_instance_code;
2525:
2526: lv_sql_stmt:=
2520: BEGIN
2521:
2522: 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
2523:
2524: lv_tbl:= 'BOM_COMPONENTS_'||MSC_CL_COLLECTION.v_instance_code;
2525:
2526: lv_sql_stmt:=
2527: 'INSERT INTO '||lv_tbl
2528: ||' SELECT * from MSC_BOM_COMPONENTS'
2525:
2526: lv_sql_stmt:=
2527: 'INSERT INTO '||lv_tbl
2528: ||' SELECT * from MSC_BOM_COMPONENTS'
2529: ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
2530: ||' AND plan_id = -1 '
2531: ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
2532:
2533: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The sql statement is '||lv_sql_stmt);
2536: COMMIT;
2537:
2538: END IF;
2539:
2540: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
2541: MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
2542: lv_retcode,
2543: 'MSC_BOM_COMPONENTS',
2544: MSC_CL_COLLECTION.v_instance_code,
2537:
2538: END IF;
2539:
2540: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
2541: MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
2542: lv_retcode,
2543: 'MSC_BOM_COMPONENTS',
2544: MSC_CL_COLLECTION.v_instance_code,
2545: MSC_UTIL.G_WARNING
2540: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
2541: MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
2542: lv_retcode,
2543: 'MSC_BOM_COMPONENTS',
2544: MSC_CL_COLLECTION.v_instance_code,
2545: MSC_UTIL.G_WARNING
2546: );
2547:
2548: IF lv_retcode = MSC_UTIL.G_ERROR THEN
2546: );
2547:
2548: IF lv_retcode = MSC_UTIL.G_ERROR THEN
2549: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
2550: RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
2551: ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
2552: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2553: END IF;
2554:
2548: IF lv_retcode = MSC_UTIL.G_ERROR THEN
2549: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
2550: RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
2551: ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
2552: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2553: END IF;
2554:
2555: END IF;
2556:
2624: msdr.SETUP_TIME_TYPE,
2625: msdr.UTILIZATION_CHANGE_TYPE ,
2626: msdr.SDS_SCHEDULING_WINDOW /* ds change change end */
2627: FROM MSC_ST_DEPARTMENT_RESOURCES msdr
2628: WHERE msdr.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
2629:
2630: CURSOR c3 IS
2631: SELECT
2632: msss.ORGANIZATION_ID,
2634: msss.DESCRIPTION,
2635: msss.USE_IN_WIP_FLAG,
2636: msss.SR_INSTANCE_ID
2637: FROM MSC_ST_SIMULATION_SETS msss
2638: WHERE msss.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
2639:
2640: CURSOR c4 IS
2641: SELECT
2642: msrg.GROUP_CODE,
2645: msrg.FROM_DATE,
2646: msrg.TO_DATE,
2647: msrg.ENABLED_FLAG
2648: FROM MSC_ST_RESOURCE_GROUPS msrg
2649: WHERE msrg.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
2650:
2651: /* ds:plan: change start */
2652: CURSOR dept_res_inst IS
2653: SELECT
2663: msdri.LAST_KNOWN_SETUP,
2664: msdri.DELETED_FLAG
2665: FROM MSC_ST_DEPT_RES_INSTANCES msdri,
2666: MSC_ITEM_ID_LID t1
2667: WHERE msdri.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
2668: AND t1.sr_instance_id (+) = msdri.sr_instance_id
2669: AND t1.sr_inventory_item_id (+) = msdri.equipment_item_id;
2670: /* ds:plan: change end */
2671:
2683: lv_res_partial_refresh BOOLEAN;
2684:
2685: BEGIN
2686:
2687: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
2688: if MSC_CL_COLLECTION.v_bom_refresh_type =1 then --2 be changed
2689: lv_res_incr_refresh :=TRUE;
2690: lv_res_partial_refresh := FALSE;
2691: elsif MSC_CL_COLLECTION.v_bom_refresh_type =2 then --2 be changed
2684:
2685: BEGIN
2686:
2687: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
2688: if MSC_CL_COLLECTION.v_bom_refresh_type =1 then --2 be changed
2689: lv_res_incr_refresh :=TRUE;
2690: lv_res_partial_refresh := FALSE;
2691: elsif MSC_CL_COLLECTION.v_bom_refresh_type =2 then --2 be changed
2692: lv_res_incr_refresh := FALSE;
2687: if (MSC_CL_COLLECTION.v_is_cont_refresh) then
2688: if MSC_CL_COLLECTION.v_bom_refresh_type =1 then --2 be changed
2689: lv_res_incr_refresh :=TRUE;
2690: lv_res_partial_refresh := FALSE;
2691: elsif MSC_CL_COLLECTION.v_bom_refresh_type =2 then --2 be changed
2692: lv_res_incr_refresh := FALSE;
2693: lv_res_partial_refresh := TRUE;
2694: else
2695: null;
2694: else
2695: null;
2696: end if;
2697: else
2698: if(MSC_CL_COLLECTION.v_is_partial_refresh) then
2699: lv_res_partial_refresh:=TRUE;
2700: lv_res_incr_refresh := FALSE;
2701: elsif(MSC_CL_COLLECTION.v_is_incremental_refresh) then
2702: lv_res_incr_refresh:= TRUE;
2697: else
2698: if(MSC_CL_COLLECTION.v_is_partial_refresh) then
2699: lv_res_partial_refresh:=TRUE;
2700: lv_res_incr_refresh := FALSE;
2701: elsif(MSC_CL_COLLECTION.v_is_incremental_refresh) then
2702: lv_res_incr_refresh:= TRUE;
2703: lv_res_partial_refresh := FALSE;
2704: end if;
2705: end if;
2710: /* ds change change start */
2711: select bom, wip
2712: into lv_bom, lv_wip
2713: from msc_coll_parameters
2714: where instance_id = MSC_CL_COLLECTION.v_instance_id;
2715:
2716: IF (((lv_bom = MSC_UTIL.SYS_YES) OR (lv_wip = MSC_UTIL.SYS_YES)) AND NOT MSC_CL_COLLECTION.v_is_legacy_refresh) THEN
2717: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2718: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_DEPT_RES_INSTANCES', MSC_CL_COLLECTION.v_instance_id, -1);
2712: into lv_bom, lv_wip
2713: from msc_coll_parameters
2714: where instance_id = MSC_CL_COLLECTION.v_instance_id;
2715:
2716: IF (((lv_bom = MSC_UTIL.SYS_YES) OR (lv_wip = MSC_UTIL.SYS_YES)) AND NOT MSC_CL_COLLECTION.v_is_legacy_refresh) THEN
2717: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2718: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_DEPT_RES_INSTANCES', MSC_CL_COLLECTION.v_instance_id, -1);
2719: ELSE
2720: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2713: from msc_coll_parameters
2714: where instance_id = MSC_CL_COLLECTION.v_instance_id;
2715:
2716: IF (((lv_bom = MSC_UTIL.SYS_YES) OR (lv_wip = MSC_UTIL.SYS_YES)) AND NOT MSC_CL_COLLECTION.v_is_legacy_refresh) THEN
2717: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2718: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_DEPT_RES_INSTANCES', MSC_CL_COLLECTION.v_instance_id, -1);
2719: ELSE
2720: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2721: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_DEPT_RES_INSTANCES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str); /* ds change change */
2714: where instance_id = MSC_CL_COLLECTION.v_instance_id;
2715:
2716: IF (((lv_bom = MSC_UTIL.SYS_YES) OR (lv_wip = MSC_UTIL.SYS_YES)) AND NOT MSC_CL_COLLECTION.v_is_legacy_refresh) THEN
2717: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2718: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_DEPT_RES_INSTANCES', MSC_CL_COLLECTION.v_instance_id, -1);
2719: ELSE
2720: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2721: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_DEPT_RES_INSTANCES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str); /* ds change change */
2722: END IF;
2717: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2718: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_DEPT_RES_INSTANCES', MSC_CL_COLLECTION.v_instance_id, -1);
2719: ELSE
2720: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2721: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_DEPT_RES_INSTANCES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str); /* ds change change */
2722: END IF;
2723: END IF;
2724: /* ds change change end */
2725:
2723: END IF;
2724: /* ds change change end */
2725:
2726:
2727: IF ((MSC_CL_COLLECTION.v_is_complete_refresh) OR (lv_res_partial_refresh)) THEN
2728:
2729:
2730: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESOURCE_GROUPS', MSC_CL_COLLECTION.v_instance_id, NULL); /*No Organization_id Column */
2731:
2726:
2727: IF ((MSC_CL_COLLECTION.v_is_complete_refresh) OR (lv_res_partial_refresh)) THEN
2728:
2729:
2730: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESOURCE_GROUPS', MSC_CL_COLLECTION.v_instance_id, NULL); /*No Organization_id Column */
2731:
2732: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2733: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_DEPARTMENT_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1);
2734: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SIMULATION_SETS', MSC_CL_COLLECTION.v_instance_id,NULL);
2728:
2729:
2730: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESOURCE_GROUPS', MSC_CL_COLLECTION.v_instance_id, NULL); /*No Organization_id Column */
2731:
2732: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2733: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_DEPARTMENT_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1);
2734: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SIMULATION_SETS', MSC_CL_COLLECTION.v_instance_id,NULL);
2735: ELSE
2736: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2729:
2730: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESOURCE_GROUPS', MSC_CL_COLLECTION.v_instance_id, NULL); /*No Organization_id Column */
2731:
2732: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2733: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_DEPARTMENT_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1);
2734: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SIMULATION_SETS', MSC_CL_COLLECTION.v_instance_id,NULL);
2735: ELSE
2736: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2737: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_DEPARTMENT_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
2730: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESOURCE_GROUPS', MSC_CL_COLLECTION.v_instance_id, NULL); /*No Organization_id Column */
2731:
2732: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2733: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_DEPARTMENT_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1);
2734: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SIMULATION_SETS', MSC_CL_COLLECTION.v_instance_id,NULL);
2735: ELSE
2736: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2737: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_DEPARTMENT_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
2738: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SIMULATION_SETS', MSC_CL_COLLECTION.v_instance_id,NULL,v_sub_str);
2733: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_DEPARTMENT_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1);
2734: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SIMULATION_SETS', MSC_CL_COLLECTION.v_instance_id,NULL);
2735: ELSE
2736: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2737: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_DEPARTMENT_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
2738: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SIMULATION_SETS', MSC_CL_COLLECTION.v_instance_id,NULL,v_sub_str);
2739: END IF;
2740:
2741: ELSE
2734: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SIMULATION_SETS', MSC_CL_COLLECTION.v_instance_id,NULL);
2735: ELSE
2736: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2737: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_DEPARTMENT_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
2738: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SIMULATION_SETS', MSC_CL_COLLECTION.v_instance_id,NULL,v_sub_str);
2739: END IF;
2740:
2741: ELSE
2742:
2739: END IF;
2740:
2741: ELSE
2742:
2743: IF (((lv_bom = MSC_UTIL.SYS_YES) OR (lv_wip = MSC_UTIL.SYS_YES)) AND NOT MSC_CL_COLLECTION.v_is_legacy_refresh) THEN
2744:
2745: /* Bug 3041176 - Delete Only Department resources in NetChange */
2746:
2747: p_instance_id := MSC_CL_COLLECTION.v_instance_id;
2743: IF (((lv_bom = MSC_UTIL.SYS_YES) OR (lv_wip = MSC_UTIL.SYS_YES)) AND NOT MSC_CL_COLLECTION.v_is_legacy_refresh) THEN
2744:
2745: /* Bug 3041176 - Delete Only Department resources in NetChange */
2746:
2747: p_instance_id := MSC_CL_COLLECTION.v_instance_id;
2748: p_table_name := 'MSC_DEPARTMENT_RESOURCES';
2749:
2750: lv_pbs:= TO_NUMBER( FND_PROFILE.VALUE('MRP_PURGE_BATCH_SIZE'));
2751:
2846: UPDATE MSC_DEPARTMENT_RESOURCES
2847: SET CAPACITY_UNITS= 0,
2848: MAX_RATE= 0,
2849: MIN_RATE= 0,
2850: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2851: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2852: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2853: WHERE PLAN_ID= -1
2854: AND DEPARTMENT_ID= c_rec.DEPARTMENT_ID
2847: SET CAPACITY_UNITS= 0,
2848: MAX_RATE= 0,
2849: MIN_RATE= 0,
2850: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2851: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2852: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2853: WHERE PLAN_ID= -1
2854: AND DEPARTMENT_ID= c_rec.DEPARTMENT_ID
2855: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
2848: MAX_RATE= 0,
2849: MIN_RATE= 0,
2850: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2851: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2852: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2853: WHERE PLAN_ID= -1
2854: AND DEPARTMENT_ID= c_rec.DEPARTMENT_ID
2855: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
2856: AND RESOURCE_ID= c_rec.RESOURCE_ID
2900: RESOURCE_COST= c_rec.RESOURCE_COST,
2901: RESOURCE_OVER_UTIL_COST= c_rec.RESOURCE_OVER_UTIL_COST,
2902: DEPT_OVERHEAD_COST= c_rec.DEPT_OVERHEAD_COST,
2903: ATP_RULE_ID=c_rec.ATP_RULE_ID,
2904: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2905: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2906: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
2907: CAPACITY_TOLERANCE= c_rec.CAPACITY_TOLERANCE, /* ds change change start*/
2908: CHARGEABLE_FLAG= c_rec.CHARGEABLE_FLAG,
2901: RESOURCE_OVER_UTIL_COST= c_rec.RESOURCE_OVER_UTIL_COST,
2902: DEPT_OVERHEAD_COST= c_rec.DEPT_OVERHEAD_COST,
2903: ATP_RULE_ID=c_rec.ATP_RULE_ID,
2904: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2905: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2906: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
2907: CAPACITY_TOLERANCE= c_rec.CAPACITY_TOLERANCE, /* ds change change start*/
2908: CHARGEABLE_FLAG= c_rec.CHARGEABLE_FLAG,
2909: IDLE_TIME_TOLERANCE= c_rec.IDLE_TIME_TOLERANCE,
2902: DEPT_OVERHEAD_COST= c_rec.DEPT_OVERHEAD_COST,
2903: ATP_RULE_ID=c_rec.ATP_RULE_ID,
2904: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2905: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2906: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
2907: CAPACITY_TOLERANCE= c_rec.CAPACITY_TOLERANCE, /* ds change change start*/
2908: CHARGEABLE_FLAG= c_rec.CHARGEABLE_FLAG,
2909: IDLE_TIME_TOLERANCE= c_rec.IDLE_TIME_TOLERANCE,
2910: BATCHING_PENALTY= c_rec.BATCHING_PENALTY,
2924: END IF; -- DELETED_FLAG
2925:
2926: END IF; -- refresh mode
2927:
2928: IF ((MSC_CL_COLLECTION.v_is_complete_refresh) OR (lv_res_partial_refresh)) OR
2929: (SQL%NOTFOUND) THEN
2930: INSERT INTO MSC_DEPARTMENT_RESOURCES
2931: ( PLAN_ID,
2932: RESOURCE_CODE,
3050: c_rec.SETUP_TIME_TYPE,
3051: c_rec.UTILIZATION_CHANGE_TYPE,
3052: c_rec.SDS_SCHEDULING_WINDOW,
3053: /* ds change change end */
3054: MSC_CL_COLLECTION.v_last_collection_id,
3055: MSC_CL_COLLECTION.v_current_date,
3056: MSC_CL_COLLECTION.v_current_user,
3057: MSC_CL_COLLECTION.v_current_date,
3058: MSC_CL_COLLECTION.v_current_user );
3051: c_rec.UTILIZATION_CHANGE_TYPE,
3052: c_rec.SDS_SCHEDULING_WINDOW,
3053: /* ds change change end */
3054: MSC_CL_COLLECTION.v_last_collection_id,
3055: MSC_CL_COLLECTION.v_current_date,
3056: MSC_CL_COLLECTION.v_current_user,
3057: MSC_CL_COLLECTION.v_current_date,
3058: MSC_CL_COLLECTION.v_current_user );
3059:
3052: c_rec.SDS_SCHEDULING_WINDOW,
3053: /* ds change change end */
3054: MSC_CL_COLLECTION.v_last_collection_id,
3055: MSC_CL_COLLECTION.v_current_date,
3056: MSC_CL_COLLECTION.v_current_user,
3057: MSC_CL_COLLECTION.v_current_date,
3058: MSC_CL_COLLECTION.v_current_user );
3059:
3060: END IF;
3053: /* ds change change end */
3054: MSC_CL_COLLECTION.v_last_collection_id,
3055: MSC_CL_COLLECTION.v_current_date,
3056: MSC_CL_COLLECTION.v_current_user,
3057: MSC_CL_COLLECTION.v_current_date,
3058: MSC_CL_COLLECTION.v_current_user );
3059:
3060: END IF;
3061:
3054: MSC_CL_COLLECTION.v_last_collection_id,
3055: MSC_CL_COLLECTION.v_current_date,
3056: MSC_CL_COLLECTION.v_current_user,
3057: MSC_CL_COLLECTION.v_current_date,
3058: MSC_CL_COLLECTION.v_current_user );
3059:
3060: END IF;
3061:
3062: c_count:= c_count+1;
3060: END IF;
3061:
3062: c_count:= c_count+1;
3063:
3064: IF c_count> MSC_CL_COLLECTION.PBS THEN
3065: COMMIT;
3066: c_count:= 0;
3067: END IF;
3068:
3081: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3082: RAISE;
3083:
3084: ELSE
3085: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3086:
3087: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3088: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3089: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE');
3093: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3094: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
3095: FND_MESSAGE.SET_TOKEN('VALUE',
3096: MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
3097: MSC_CL_COLLECTION.v_instance_id));
3098: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3099:
3100: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3101: FND_MESSAGE.SET_TOKEN('COLUMN', 'DEPARTMENT_CODE');
3122: FOR c_rec IN dept_res_inst LOOP
3123: /* for dept resource instance we don't have snapshot
3124: so in increment its alwasy full refresh */
3125: BEGIN
3126: -- IF ((MSC_CL_COLLECTION.v_is_complete_refresh) OR (lv_res_partial_refresh)) THEN
3127:
3128: INSERT INTO MSC_DEPT_RES_INSTANCES
3129: ( PLAN_ID,
3130: DEPT_RESOURCE_INST_ID ,
3155: c_rec.SR_INSTANCE_ID,
3156: c_rec.LAST_KNOWN_SETUP,
3157: c_rec.EFFECTIVE_START_DATE,
3158: c_rec.EFFECTIVE_END_DATE,
3159: MSC_CL_COLLECTION.v_last_collection_id,
3160: MSC_CL_COLLECTION.v_current_date,
3161: MSC_CL_COLLECTION.v_current_user,
3162: MSC_CL_COLLECTION.v_current_date,
3163: MSC_CL_COLLECTION.v_current_user );
3156: c_rec.LAST_KNOWN_SETUP,
3157: c_rec.EFFECTIVE_START_DATE,
3158: c_rec.EFFECTIVE_END_DATE,
3159: MSC_CL_COLLECTION.v_last_collection_id,
3160: MSC_CL_COLLECTION.v_current_date,
3161: MSC_CL_COLLECTION.v_current_user,
3162: MSC_CL_COLLECTION.v_current_date,
3163: MSC_CL_COLLECTION.v_current_user );
3164: --END IF;
3157: c_rec.EFFECTIVE_START_DATE,
3158: c_rec.EFFECTIVE_END_DATE,
3159: MSC_CL_COLLECTION.v_last_collection_id,
3160: MSC_CL_COLLECTION.v_current_date,
3161: MSC_CL_COLLECTION.v_current_user,
3162: MSC_CL_COLLECTION.v_current_date,
3163: MSC_CL_COLLECTION.v_current_user );
3164: --END IF;
3165:
3158: c_rec.EFFECTIVE_END_DATE,
3159: MSC_CL_COLLECTION.v_last_collection_id,
3160: MSC_CL_COLLECTION.v_current_date,
3161: MSC_CL_COLLECTION.v_current_user,
3162: MSC_CL_COLLECTION.v_current_date,
3163: MSC_CL_COLLECTION.v_current_user );
3164: --END IF;
3165:
3166: c_count:= c_count+1;
3159: MSC_CL_COLLECTION.v_last_collection_id,
3160: MSC_CL_COLLECTION.v_current_date,
3161: MSC_CL_COLLECTION.v_current_user,
3162: MSC_CL_COLLECTION.v_current_date,
3163: MSC_CL_COLLECTION.v_current_user );
3164: --END IF;
3165:
3166: c_count:= c_count+1;
3167:
3164: --END IF;
3165:
3166: c_count:= c_count+1;
3167:
3168: IF c_count> MSC_CL_COLLECTION.PBS THEN
3169: COMMIT;
3170: c_count:= 0;
3171: END IF;
3172:
3184: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3185: RAISE;
3186:
3187: ELSE
3188: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3189:
3190: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3191: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3192: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE');
3235: UPDATE MSC_SIMULATION_SETS
3236: SET
3237: DESCRIPTION= c_rec.DESCRIPTION,
3238: USE_IN_WIP_FLAG= c_rec.USE_IN_WIP_FLAG,
3239: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
3240: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3241: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3242: WHERE ORGANIZATION_ID= c_rec.ORGANIZATION_ID
3243: AND SIMULATION_SET= c_rec.SIMULATION_SET
3236: SET
3237: DESCRIPTION= c_rec.DESCRIPTION,
3238: USE_IN_WIP_FLAG= c_rec.USE_IN_WIP_FLAG,
3239: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
3240: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3241: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3242: WHERE ORGANIZATION_ID= c_rec.ORGANIZATION_ID
3243: AND SIMULATION_SET= c_rec.SIMULATION_SET
3244: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
3237: DESCRIPTION= c_rec.DESCRIPTION,
3238: USE_IN_WIP_FLAG= c_rec.USE_IN_WIP_FLAG,
3239: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
3240: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3241: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3242: WHERE ORGANIZATION_ID= c_rec.ORGANIZATION_ID
3243: AND SIMULATION_SET= c_rec.SIMULATION_SET
3244: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
3245:
3244: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
3245:
3246: END IF;
3247:
3248: IF ((MSC_CL_COLLECTION.v_is_complete_refresh) OR (lv_res_partial_refresh)) OR SQL%NOTFOUND THEN
3249:
3250: INSERT INTO MSC_SIMULATION_SETS
3251: ( ORGANIZATION_ID,
3252: SIMULATION_SET,
3263: c_rec.SIMULATION_SET,
3264: c_rec.DESCRIPTION,
3265: c_rec.USE_IN_WIP_FLAG,
3266: c_rec.SR_INSTANCE_ID,
3267: MSC_CL_COLLECTION.v_last_collection_id,
3268: MSC_CL_COLLECTION.v_current_date,
3269: MSC_CL_COLLECTION.v_current_user,
3270: MSC_CL_COLLECTION.v_current_date,
3271: MSC_CL_COLLECTION.v_current_user );
3264: c_rec.DESCRIPTION,
3265: c_rec.USE_IN_WIP_FLAG,
3266: c_rec.SR_INSTANCE_ID,
3267: MSC_CL_COLLECTION.v_last_collection_id,
3268: MSC_CL_COLLECTION.v_current_date,
3269: MSC_CL_COLLECTION.v_current_user,
3270: MSC_CL_COLLECTION.v_current_date,
3271: MSC_CL_COLLECTION.v_current_user );
3272:
3265: c_rec.USE_IN_WIP_FLAG,
3266: c_rec.SR_INSTANCE_ID,
3267: MSC_CL_COLLECTION.v_last_collection_id,
3268: MSC_CL_COLLECTION.v_current_date,
3269: MSC_CL_COLLECTION.v_current_user,
3270: MSC_CL_COLLECTION.v_current_date,
3271: MSC_CL_COLLECTION.v_current_user );
3272:
3273: END IF;
3266: c_rec.SR_INSTANCE_ID,
3267: MSC_CL_COLLECTION.v_last_collection_id,
3268: MSC_CL_COLLECTION.v_current_date,
3269: MSC_CL_COLLECTION.v_current_user,
3270: MSC_CL_COLLECTION.v_current_date,
3271: MSC_CL_COLLECTION.v_current_user );
3272:
3273: END IF;
3274:
3267: MSC_CL_COLLECTION.v_last_collection_id,
3268: MSC_CL_COLLECTION.v_current_date,
3269: MSC_CL_COLLECTION.v_current_user,
3270: MSC_CL_COLLECTION.v_current_date,
3271: MSC_CL_COLLECTION.v_current_user );
3272:
3273: END IF;
3274:
3275: c_count:= c_count+1;
3273: END IF;
3274:
3275: c_count:= c_count+1;
3276:
3277: IF c_count> MSC_CL_COLLECTION.PBS THEN
3278: COMMIT;
3279: c_count:= 0;
3280: END IF;
3281:
3293: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3294: RAISE;
3295:
3296: ELSE
3297: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3298:
3299: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3300: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3301: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE');
3305: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3306: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
3307: FND_MESSAGE.SET_TOKEN('VALUE',
3308: MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
3309: MSC_CL_COLLECTION.v_instance_id));
3310: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3311:
3312: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3313: FND_MESSAGE.SET_TOKEN('COLUMN', 'SIMULATION_SET');
3322: END LOOP;
3323:
3324: COMMIT;
3325:
3326: IF ((MSC_CL_COLLECTION.v_is_complete_refresh) OR (lv_res_partial_refresh)) THEN
3327:
3328: c_count:= 0;
3329:
3330: FOR c_rec IN c4 LOOP
3349: c_rec.DESCRIPTION,
3350: c_rec.FROM_DATE,
3351: c_rec.TO_DATE,
3352: c_rec.ENABLED_FLAG,
3353: MSC_CL_COLLECTION.v_instance_id,
3354: MSC_CL_COLLECTION.v_current_date,
3355: MSC_CL_COLLECTION.v_current_user,
3356: MSC_CL_COLLECTION.v_current_date,
3357: MSC_CL_COLLECTION.v_current_user );
3350: c_rec.FROM_DATE,
3351: c_rec.TO_DATE,
3352: c_rec.ENABLED_FLAG,
3353: MSC_CL_COLLECTION.v_instance_id,
3354: MSC_CL_COLLECTION.v_current_date,
3355: MSC_CL_COLLECTION.v_current_user,
3356: MSC_CL_COLLECTION.v_current_date,
3357: MSC_CL_COLLECTION.v_current_user );
3358:
3351: c_rec.TO_DATE,
3352: c_rec.ENABLED_FLAG,
3353: MSC_CL_COLLECTION.v_instance_id,
3354: MSC_CL_COLLECTION.v_current_date,
3355: MSC_CL_COLLECTION.v_current_user,
3356: MSC_CL_COLLECTION.v_current_date,
3357: MSC_CL_COLLECTION.v_current_user );
3358:
3359: c_count:= c_count+1;
3352: c_rec.ENABLED_FLAG,
3353: MSC_CL_COLLECTION.v_instance_id,
3354: MSC_CL_COLLECTION.v_current_date,
3355: MSC_CL_COLLECTION.v_current_user,
3356: MSC_CL_COLLECTION.v_current_date,
3357: MSC_CL_COLLECTION.v_current_user );
3358:
3359: c_count:= c_count+1;
3360:
3353: MSC_CL_COLLECTION.v_instance_id,
3354: MSC_CL_COLLECTION.v_current_date,
3355: MSC_CL_COLLECTION.v_current_user,
3356: MSC_CL_COLLECTION.v_current_date,
3357: MSC_CL_COLLECTION.v_current_user );
3358:
3359: c_count:= c_count+1;
3360:
3361: IF c_count> MSC_CL_COLLECTION.PBS THEN
3357: MSC_CL_COLLECTION.v_current_user );
3358:
3359: c_count:= c_count+1;
3360:
3361: IF c_count> MSC_CL_COLLECTION.PBS THEN
3362: COMMIT;
3363: c_count:= 0;
3364: END IF;
3365:
3378: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3379: RAISE;
3380:
3381: ELSE
3382: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3383:
3384: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3385: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3386: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE');