[Home] [Help]
3:
4:
5: v_sub_str VARCHAR2(32767):=NULL;
6: c_count NUMBER:= 0;
7: -- v_warning_flag NUMBER:= MSC_UTIL.SYS_NO; --2 be changed
8:
9:
10:
11: -- PROCEDURE LOAD_RESOURCE_SETUP;
42: total_count NUMBER := 0;
43: BEGIN
44:
45: IF ((MSC_CL_COLLECTION.v_is_complete_refresh) OR (MSC_CL_COLLECTION.v_is_partial_refresh) OR (MSC_CL_COLLECTION.v_is_legacy_refresh)) THEN
46: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
47: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESOURCE_SETUPS', MSC_CL_COLLECTION.v_instance_id, -1);
48: ELSE
49: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
50: MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_RESOURCE_SETUPS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
45: IF ((MSC_CL_COLLECTION.v_is_complete_refresh) OR (MSC_CL_COLLECTION.v_is_partial_refresh) OR (MSC_CL_COLLECTION.v_is_legacy_refresh)) THEN
46: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
47: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESOURCE_SETUPS', MSC_CL_COLLECTION.v_instance_id, -1);
48: ELSE
49: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
50: MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_RESOURCE_SETUPS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
51: END IF;
52:
53:
101: WHEN OTHERS THEN
102:
103: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
104:
105: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
106: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
107: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE_SETUP');
108: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_SETUPS');
109: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
105: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
106: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
107: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE_SETUP');
108: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_SETUPS');
109: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
110:
111: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
112: RAISE;
113:
107: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE_SETUP');
108: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_SETUPS');
109: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
110:
111: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
112: RAISE;
113:
114: ELSE
115: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
111: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
112: RAISE;
113:
114: ELSE
115: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
116:
117: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
118: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
119: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE_SETUP');
113:
114: ELSE
115: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
116:
117: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
118: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
119: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE_SETUP');
120: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_SETUPS');
121: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
117: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
118: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
119: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE_SETUP');
120: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_SETUPS');
121: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
122:
123: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
124: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
125: FND_MESSAGE.SET_TOKEN('VALUE',
124: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
125: FND_MESSAGE.SET_TOKEN('VALUE',
126: MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
127: MSC_CL_COLLECTION.v_instance_id));
128: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
129:
130: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
131: FND_MESSAGE.SET_TOKEN('COLUMN', 'RESOURCE_ID');
132: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.RESOURCE_ID);
129:
130: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
131: FND_MESSAGE.SET_TOKEN('COLUMN', 'RESOURCE_ID');
132: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.RESOURCE_ID);
133: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
134:
135: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
136: FND_MESSAGE.SET_TOKEN('COLUMN', 'SETUP_CODE');
137: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SETUP_CODE);
134:
135: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
136: FND_MESSAGE.SET_TOKEN('COLUMN', 'SETUP_CODE');
137: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SETUP_CODE);
138: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
139:
140: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
141: END IF;
142:
136: FND_MESSAGE.SET_TOKEN('COLUMN', 'SETUP_CODE');
137: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SETUP_CODE);
138: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
139:
140: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
141: END IF;
142:
143: END;
144:
142:
143: END;
144:
145: END LOOP;
146: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Total resource setups = '|| to_char(total_count));
147:
148: COMMIT;
149: END IF; /* MSC_CL_COLLECTION.v_is_complete_refresh OR v_is_partial_refresh */
150: END LOAD_RESOURCE_SETUP;
171: total_count NUMBER := 0;
172: BEGIN
173:
174: IF ((MSC_CL_COLLECTION.v_is_complete_refresh) OR (MSC_CL_COLLECTION.v_is_partial_refresh) OR (MSC_CL_COLLECTION.v_is_legacy_refresh)) THEN
175: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
176: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SETUP_TRANSITIONS', MSC_CL_COLLECTION.v_instance_id, -1);
177: ELSE
178: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
179: MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_SETUP_TRANSITIONS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
174: IF ((MSC_CL_COLLECTION.v_is_complete_refresh) OR (MSC_CL_COLLECTION.v_is_partial_refresh) OR (MSC_CL_COLLECTION.v_is_legacy_refresh)) THEN
175: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
176: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SETUP_TRANSITIONS', MSC_CL_COLLECTION.v_instance_id, -1);
177: ELSE
178: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
179: MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_SETUP_TRANSITIONS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
180: END IF;
181:
182:
230: WHEN OTHERS THEN
231:
232: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
233:
234: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
235: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
236: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SETUP_TRANSITION');
237: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SETUP_TRANSITIONS');
238: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
234: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
235: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
236: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SETUP_TRANSITION');
237: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SETUP_TRANSITIONS');
238: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
239:
240: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
241: RAISE;
242: ELSE
236: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SETUP_TRANSITION');
237: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SETUP_TRANSITIONS');
238: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
239:
240: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
241: RAISE;
242: ELSE
243: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
244:
239:
240: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
241: RAISE;
242: ELSE
243: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
244:
245: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
246: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
247: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SETUP_TRANSITION');
241: RAISE;
242: ELSE
243: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
244:
245: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
246: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
247: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SETUP_TRANSITION');
248: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SETUP_TRANSITIONS');
249: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
245: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
246: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
247: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SETUP_TRANSITION');
248: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SETUP_TRANSITIONS');
249: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
250:
251: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
252: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
253: FND_MESSAGE.SET_TOKEN('VALUE',
252: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
253: FND_MESSAGE.SET_TOKEN('VALUE',
254: MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
255: MSC_CL_COLLECTION.v_instance_id));
256: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
257:
258: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
259: FND_MESSAGE.SET_TOKEN('COLUMN', 'RESOURCE_ID');
260: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.RESOURCE_ID);
257:
258: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
259: FND_MESSAGE.SET_TOKEN('COLUMN', 'RESOURCE_ID');
260: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.RESOURCE_ID);
261: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
262:
263: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
264: FND_MESSAGE.SET_TOKEN('COLUMN', 'FROM_SETUP_ID');
265: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.FROM_SETUP_ID);
262:
263: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
264: FND_MESSAGE.SET_TOKEN('COLUMN', 'FROM_SETUP_ID');
265: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.FROM_SETUP_ID);
266: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
267:
268:
269: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
270: FND_MESSAGE.SET_TOKEN('COLUMN', 'TO_SETUP_ID');
268:
269: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
270: FND_MESSAGE.SET_TOKEN('COLUMN', 'TO_SETUP_ID');
271: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.TO_SETUP_ID);
272: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
273: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
274: END IF;
275:
276: END;
269: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
270: FND_MESSAGE.SET_TOKEN('COLUMN', 'TO_SETUP_ID');
271: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.TO_SETUP_ID);
272: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
273: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
274: END IF;
275:
276: END;
277:
275:
276: END;
277:
278: END LOOP;
279: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Total resource transition = '|| to_char(total_count));
280:
281: COMMIT;
282:
283: END IF; /* MSC_CL_COLLECTION.v_is_complete_refresh) OR (v_is_partial_refresh */
305: lv_CHARGE_END_DATETIME DATE;
306:
307:
308: BEGIN
309: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
310: lv_res_req_tbl := 'RESOURCE_REQUIREMENTS_'||MSC_CL_COLLECTION.v_instance_code;
311: ELSE
312: lv_res_req_tbl := 'MSC_RESOURCE_REQUIREMENTS';
313: END IF;
404: EXCEPTION
405: WHEN OTHERS THEN
406: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
407:
408: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
409: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
410: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE_CHARGES');
411: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_CHARGES');
412: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
408: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
409: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
410: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE_CHARGES');
411: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_CHARGES');
412: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
413:
414: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
415: RAISE;
416:
410: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE_CHARGES');
411: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_CHARGES');
412: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
413:
414: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
415: RAISE;
416:
417: ELSE
418:
415: RAISE;
416:
417: ELSE
418:
419: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
420:
421: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
422: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
423: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE_CHARGES');
417: ELSE
418:
419: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
420:
421: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
422: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
423: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE_CHARGES');
424: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_CHARGES');
425: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
421: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
422: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
423: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE_CHARGES');
424: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_CHARGES');
425: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
426:
427: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
428: FND_MESSAGE.SET_TOKEN('COLUMN', 'RES_TRANSACTION_ID');
429: FND_MESSAGE.SET_TOKEN('VALUE', lv_TRANSACTION_ID);
426:
427: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
428: FND_MESSAGE.SET_TOKEN('COLUMN', 'RES_TRANSACTION_ID');
429: FND_MESSAGE.SET_TOKEN('VALUE', lv_TRANSACTION_ID);
430: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
431:
432: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
433: FND_MESSAGE.SET_TOKEN('COLUMN', 'CHARGE_NUMBER');
434: FND_MESSAGE.SET_TOKEN('VALUE', lv_CHARGE_NUMBER);
431:
432: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
433: FND_MESSAGE.SET_TOKEN('COLUMN', 'CHARGE_NUMBER');
434: FND_MESSAGE.SET_TOKEN('VALUE', lv_CHARGE_NUMBER);
435: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
436:
437: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
438: END IF;
439:
433: FND_MESSAGE.SET_TOKEN('COLUMN', 'CHARGE_NUMBER');
434: FND_MESSAGE.SET_TOKEN('VALUE', lv_CHARGE_NUMBER);
435: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
436:
437: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
438: END IF;
439:
440: END;
441: END LOOP;
438: END IF;
439:
440: END;
441: END LOOP;
442: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Total resource charges = '|| total_count);
443: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
444: COMMIT;
445: END IF;
446: EXCEPTION
446: EXCEPTION
447: WHEN OTHERS THEN
448: IF c4%ISOPEN THEN CLOSE c4; END IF;
449:
450: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
452: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
453: END IF;
454: IF lv_sql_stmt IS NOT NULL THEN
448: IF c4%ISOPEN THEN CLOSE c4; END IF;
449:
450: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
451: IF lv_cursor_stmt IS NOT NULL THEN
452: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
454: IF lv_sql_stmt IS NOT NULL THEN
455: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
456: END IF;
451: IF lv_cursor_stmt IS NOT NULL THEN
452: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
453: END IF;
454: IF lv_sql_stmt IS NOT NULL THEN
455: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
457: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
458: RAISE;
459: END LOAD_RESOURCE_CHARGES;
453: END IF;
454: IF lv_sql_stmt IS NOT NULL THEN
455: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
456: END IF;
457: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
458: RAISE;
459: END LOAD_RESOURCE_CHARGES;
460:
461: PROCEDURE LOAD_RES_INST_CHANGE IS
490: lv_instance_code varchar2(10);
491:
492: Begin
493:
494: IF ((MSC_CL_COLLECTION.v_is_partial_refresh AND MSC_CL_COLLECTION.v_coll_prec.calendar_flag = MSC_UTIL.SYS_YES) OR
495: MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_incremental_refresh) then
496:
497: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
498:
507: FOR c_rec IN res_inst_chngs LOOP
508:
509: BEGIN
510:
511: IF MSC_CL_COLLECTION.v_is_incremental_refresh AND c_rec.DELETED_FLAG= MSC_UTIL.SYS_YES THEN
512:
513: DELETE MSC_RES_INSTANCE_CHANGES
514: WHERE DEPARTMENT_ID = c_rec.DEPARTMENT_ID
515: AND RESOURCE_ID = c_rec.RESOURCE_ID
517: AND SERIAL_NUMBER = c_rec.SERIAL_NUMBER
518: --AND EQUIPMENT_ITEM_ID = c_rec.EQUIPMENT_ITEM_ID
519: AND SHIFT_NUM= c_rec.SHIFT_NUM
520: AND FROM_DATE= c_rec.FROM_DATE
521: AND NVL(TO_DATE,MSC_UTIL.NULL_DATE)= NVL(c_rec.TO_DATE,MSC_UTIL.NULL_DATE)
522: AND NVL(FROM_TIME,MSC_UTIL.NULL_VALUE)= NVL(c_rec.FROM_TIME,MSC_UTIL.NULL_VALUE)
523: AND NVL(TO_TIME,MSC_UTIL.NULL_VALUE)= NVL(c_rec.TO_TIME,MSC_UTIL.NULL_VALUE)
524: AND SIMULATION_SET= c_rec.SIMULATION_SET
525: AND ACTION_TYPE= c_rec.ACTION_TYPE
518: --AND EQUIPMENT_ITEM_ID = c_rec.EQUIPMENT_ITEM_ID
519: AND SHIFT_NUM= c_rec.SHIFT_NUM
520: AND FROM_DATE= c_rec.FROM_DATE
521: AND NVL(TO_DATE,MSC_UTIL.NULL_DATE)= NVL(c_rec.TO_DATE,MSC_UTIL.NULL_DATE)
522: AND NVL(FROM_TIME,MSC_UTIL.NULL_VALUE)= NVL(c_rec.FROM_TIME,MSC_UTIL.NULL_VALUE)
523: AND NVL(TO_TIME,MSC_UTIL.NULL_VALUE)= NVL(c_rec.TO_TIME,MSC_UTIL.NULL_VALUE)
524: AND SIMULATION_SET= c_rec.SIMULATION_SET
525: AND ACTION_TYPE= c_rec.ACTION_TYPE
526: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
519: AND SHIFT_NUM= c_rec.SHIFT_NUM
520: AND FROM_DATE= c_rec.FROM_DATE
521: AND NVL(TO_DATE,MSC_UTIL.NULL_DATE)= NVL(c_rec.TO_DATE,MSC_UTIL.NULL_DATE)
522: AND NVL(FROM_TIME,MSC_UTIL.NULL_VALUE)= NVL(c_rec.FROM_TIME,MSC_UTIL.NULL_VALUE)
523: AND NVL(TO_TIME,MSC_UTIL.NULL_VALUE)= NVL(c_rec.TO_TIME,MSC_UTIL.NULL_VALUE)
524: AND SIMULATION_SET= c_rec.SIMULATION_SET
525: AND ACTION_TYPE= c_rec.ACTION_TYPE
526: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
527:
580:
581: WHEN OTHERS THEN
582:
583: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
584: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
585: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
586: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RES_INST_CHANGE');
587: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RES_INSTANCE_CHANGES');
588: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
584: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
585: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
586: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RES_INST_CHANGE');
587: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RES_INSTANCE_CHANGES');
588: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
589:
590: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
591: RAISE;
592:
586: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RES_INST_CHANGE');
587: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RES_INSTANCE_CHANGES');
588: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
589:
590: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
591: RAISE;
592:
593: ELSE
594: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
590: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
591: RAISE;
592:
593: ELSE
594: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
595:
596: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
597: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
598: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RES_INST_CHANGE');
592:
593: ELSE
594: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
595:
596: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
597: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
598: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RES_INST_CHANGE');
599: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RES_INSTANCE_CHANGES');
600: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
596: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
597: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
598: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RES_INST_CHANGE');
599: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RES_INSTANCE_CHANGES');
600: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
601:
602: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
603: FND_MESSAGE.SET_TOKEN('COLUMN', 'DEPARTMENT_ID');
604: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.DEPARTMENT_ID));
601:
602: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
603: FND_MESSAGE.SET_TOKEN('COLUMN', 'DEPARTMENT_ID');
604: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.DEPARTMENT_ID));
605: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
606:
607: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
608: FND_MESSAGE.SET_TOKEN('COLUMN', 'RESOURCE_ID');
609: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.RESOURCE_ID));
606:
607: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
608: FND_MESSAGE.SET_TOKEN('COLUMN', 'RESOURCE_ID');
609: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.RESOURCE_ID));
610: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
611:
612:
613: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
614: FND_MESSAGE.SET_TOKEN('COLUMN', 'RES_INSTANCE_ID');
612:
613: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
614: FND_MESSAGE.SET_TOKEN('COLUMN', 'RES_INSTANCE_ID');
615: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.RES_INSTANCE_ID));
616: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
617:
618: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
619: FND_MESSAGE.SET_TOKEN('COLUMN', 'SERIAL_NUMBER');
620: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SERIAL_NUMBER);
617:
618: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
619: FND_MESSAGE.SET_TOKEN('COLUMN', 'SERIAL_NUMBER');
620: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SERIAL_NUMBER);
621: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
622:
623: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
624: FND_MESSAGE.SET_TOKEN('COLUMN', 'SHIFT_NUM');
625: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SHIFT_NUM));
622:
623: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
624: FND_MESSAGE.SET_TOKEN('COLUMN', 'SHIFT_NUM');
625: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SHIFT_NUM));
626: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
627:
628: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
629: FND_MESSAGE.SET_TOKEN('COLUMN', 'ACTION_TYPE');
630: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.ACTION_TYPE));
627:
628: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
629: FND_MESSAGE.SET_TOKEN('COLUMN', 'ACTION_TYPE');
630: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.ACTION_TYPE));
631: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
632:
633: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
634: FND_MESSAGE.SET_TOKEN('COLUMN', 'SIMULATION_SET');
635: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SIMULATION_SET);
632:
633: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
634: FND_MESSAGE.SET_TOKEN('COLUMN', 'SIMULATION_SET');
635: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SIMULATION_SET);
636: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
637:
638: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
639: END IF;
640:
634: FND_MESSAGE.SET_TOKEN('COLUMN', 'SIMULATION_SET');
635: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SIMULATION_SET);
636: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
637:
638: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
639: END IF;
640:
641: END;
642:
640:
641: END;
642:
643: END LOOP;
644: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Total res instance changes = '|| total_count);
645:
646: COMMIT;
647:
648: END IF;
669: MSC_ST_COMPONENT_SUBSTITUTES mscs
670: WHERE t1.SR_INVENTORY_ITEM_ID= mscs.substitute_item_id
671: AND t1.sr_instance_id= mscs.sr_instance_id
672: AND mscs.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
673: AND mscs.DELETED_FLAG= MSC_UTIL.SYS_NO;
674:
675: CURSOR c1_d IS
676: SELECT
677: mscs.BILL_SEQUENCE_ID,
683: WHERE ((t1.SR_INVENTORY_ITEM_ID= mscs.substitute_item_id) AND (mscs.substitute_item_id
684: is NOT NULL))
685: AND t1.sr_instance_id= mscs.sr_instance_id
686: AND mscs.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
687: AND mscs.DELETED_FLAG= MSC_UTIL.SYS_YES
688: UNION ALL
689: SELECT
690: mscs.BILL_SEQUENCE_ID,
691: mscs.COMPONENT_SEQUENCE_ID,
693: mscs.SR_INSTANCE_ID
694: FROM MSC_ST_COMPONENT_SUBSTITUTES mscs
695: WHERE mscs.substitute_item_id IS NULL
696: AND mscs.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
697: AND mscs.DELETED_FLAG= MSC_UTIL.SYS_YES;
698:
699:
700: c_count NUMBER:= 0;
701: lv_tbl VARCHAR2(30);
707: -- We want to delete all BOM related data and get new stuff.
708:
709: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_COMPONENT_SUBSTITUTES', MSC_CL_COLLECTION.v_instance_id, -1);
710:
711: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
712: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_COMPONENT_SUBSTITUTES', MSC_CL_COLLECTION.v_instance_id, -1);
713: ELSE
714: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
715: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_COMPONENT_SUBSTITUTES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
710:
711: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
712: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_COMPONENT_SUBSTITUTES', MSC_CL_COLLECTION.v_instance_id, -1);
713: ELSE
714: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
715: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_COMPONENT_SUBSTITUTES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
716: END IF;
717:
718: END IF; -- MSC_CL_COLLECTION.v_is_complete_refresh
837: EXCEPTION
838:
839: WHEN OTHERS THEN
840:
841: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
842:
843: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
844: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
845: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_COMPONENT_SUBSTITUTE');
839: WHEN OTHERS THEN
840:
841: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
842:
843: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
844: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
845: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_COMPONENT_SUBSTITUTE');
846: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_COMPONENT_SUBSTITUTES');
847: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
843: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
844: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
845: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_COMPONENT_SUBSTITUTE');
846: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_COMPONENT_SUBSTITUTES');
847: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
848:
849: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
850: FND_MESSAGE.SET_TOKEN('COLUMN', 'BILL_SEQUENCE_ID');
851: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.BILL_SEQUENCE_ID));
848:
849: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
850: FND_MESSAGE.SET_TOKEN('COLUMN', 'BILL_SEQUENCE_ID');
851: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.BILL_SEQUENCE_ID));
852: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
853:
854: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
855: FND_MESSAGE.SET_TOKEN('COLUMN', 'COMPONENT_SEQUENCE_ID');
856: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.COMPONENT_SEQUENCE_ID));
853:
854: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
855: FND_MESSAGE.SET_TOKEN('COLUMN', 'COMPONENT_SEQUENCE_ID');
856: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.COMPONENT_SEQUENCE_ID));
857: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
858:
859: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
860: FND_MESSAGE.SET_TOKEN('COLUMN', 'SUBSTITUTE_ITEM_NAME');
861: FND_MESSAGE.SET_TOKEN('VALUE', MSC_CL_ITEM_ODS_LOAD.ITEM_NAME( c_rec.SUBSTITUTE_ITEM_ID));
858:
859: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
860: FND_MESSAGE.SET_TOKEN('COLUMN', 'SUBSTITUTE_ITEM_NAME');
861: FND_MESSAGE.SET_TOKEN('VALUE', MSC_CL_ITEM_ODS_LOAD.ITEM_NAME( c_rec.SUBSTITUTE_ITEM_ID));
862: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
863:
864: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
865: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
866: FND_MESSAGE.SET_TOKEN('VALUE',
865: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
866: FND_MESSAGE.SET_TOKEN('VALUE',
867: MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
868: MSC_CL_COLLECTION.v_instance_id));
869: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
870:
871: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
872:
873: END;
867: MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
868: MSC_CL_COLLECTION.v_instance_id));
869: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
870:
871: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
872:
873: END;
874:
875: END LOOP;
928:
929: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BILL_OF_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1);
930: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOR_REQUIREMENTS', MSC_CL_COLLECTION.v_instance_id, -1);
931:
932: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
933: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BILL_OF_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1);
934: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOR_REQUIREMENTS', MSC_CL_COLLECTION.v_instance_id, -1);
935: ELSE
936: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
932: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
933: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BILL_OF_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1);
934: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOR_REQUIREMENTS', MSC_CL_COLLECTION.v_instance_id, -1);
935: ELSE
936: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
937: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BILL_OF_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
938: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOR_REQUIREMENTS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
939: END IF;
940:
1009: WHEN OTHERS THEN
1010:
1011: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
1012:
1013: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1014: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1015: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOR');
1016: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BILL_OF_RESOURCES');
1017: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1013: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1014: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1015: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOR');
1016: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BILL_OF_RESOURCES');
1017: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1018:
1019: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1020: RAISE;
1021:
1015: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOR');
1016: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BILL_OF_RESOURCES');
1017: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1018:
1019: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1020: RAISE;
1021:
1022: ELSE
1023: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1019: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1020: RAISE;
1021:
1022: ELSE
1023: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1024:
1025: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1026: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1027: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOR');
1021:
1022: ELSE
1023: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1024:
1025: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1026: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1027: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOR');
1028: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BILL_OF_RESOURCES');
1029: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1025: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1026: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1027: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOR');
1028: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BILL_OF_RESOURCES');
1029: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1030:
1031: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1032: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
1033: FND_MESSAGE.SET_TOKEN('VALUE',
1032: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
1033: FND_MESSAGE.SET_TOKEN('VALUE',
1034: MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
1035: MSC_CL_COLLECTION.v_instance_id));
1036: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1037:
1038: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1039: FND_MESSAGE.SET_TOKEN('COLUMN', 'BILL_OF_RESOURCES');
1040: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.BILL_OF_RESOURCES);
1037:
1038: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1039: FND_MESSAGE.SET_TOKEN('COLUMN', 'BILL_OF_RESOURCES');
1040: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.BILL_OF_RESOURCES);
1041: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1042:
1043: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1044:
1045: END IF;
1039: FND_MESSAGE.SET_TOKEN('COLUMN', 'BILL_OF_RESOURCES');
1040: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.BILL_OF_RESOURCES);
1041: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1042:
1043: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1044:
1045: END IF;
1046: END;
1047:
1151: WHEN OTHERS THEN
1152:
1153: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
1154:
1155: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1156: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1157: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOR');
1158: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BOR_REQUIREMENTS');
1159: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1155: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1156: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1157: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOR');
1158: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BOR_REQUIREMENTS');
1159: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1160:
1161: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1162: RAISE;
1163:
1157: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOR');
1158: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BOR_REQUIREMENTS');
1159: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1160:
1161: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1162: RAISE;
1163:
1164: ELSE
1165: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1161: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1162: RAISE;
1163:
1164: ELSE
1165: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1166:
1167: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1168: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1169: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOR');
1163:
1164: ELSE
1165: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1166:
1167: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1168: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1169: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOR');
1170: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BOR_REQUIREMENTS');
1171: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1167: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1168: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1169: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOR');
1170: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BOR_REQUIREMENTS');
1171: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1172:
1173: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1174: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TRANSACTION_ID');
1175: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.SR_TRANSACTION_ID));
1172:
1173: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1174: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TRANSACTION_ID');
1175: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.SR_TRANSACTION_ID));
1176: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1177:
1178: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1179: FND_MESSAGE.SET_TOKEN('COLUMN', 'DEPARTMENT_ID');
1180: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.DEPARTMENT_ID);
1177:
1178: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1179: FND_MESSAGE.SET_TOKEN('COLUMN', 'DEPARTMENT_ID');
1180: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.DEPARTMENT_ID);
1181: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1182:
1183: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1184: FND_MESSAGE.SET_TOKEN('COLUMN', 'RESOURCE_ID');
1185: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.RESOURCE_ID);
1182:
1183: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1184: FND_MESSAGE.SET_TOKEN('COLUMN', 'RESOURCE_ID');
1185: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.RESOURCE_ID);
1186: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1187:
1188: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1189:
1190: END IF;
1184: FND_MESSAGE.SET_TOKEN('COLUMN', 'RESOURCE_ID');
1185: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.RESOURCE_ID);
1186: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1187:
1188: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1189:
1190: END IF;
1191: END;
1192:
1221: MSC_ST_PROCESS_EFFECTIVITY msor
1222: WHERE miil.SR_INVENTORY_ITEM_ID= msor.ITEM_ID
1223: AND miil.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1224: AND msor.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1225: AND msor.DELETED_FLAG= MSC_UTIL.SYS_NO;
1226:
1227: CURSOR c8_d IS
1228: SELECT
1229: miil.INVENTORY_ITEM_ID, -- msor.ITEM_ID,
1236: MSC_ST_PROCESS_EFFECTIVITY msor
1237: WHERE miil.SR_INVENTORY_ITEM_ID(+)= msor.ITEM_ID
1238: AND miil.SR_INSTANCE_ID(+)= MSC_CL_COLLECTION.v_instance_id
1239: AND msor.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1240: AND msor.DELETED_FLAG= MSC_UTIL.SYS_YES
1241: UNION ALL
1242: SELECT
1243: TO_NUMBER(NULL),
1244: TO_NUMBER(NULL),
1251: WHERE mspe.Bill_Sequence_ID= mpe.Bill_Sequence_ID
1252: AND mspe.Routing_Sequence_ID <> mpe.Routing_Sequence_ID
1253: AND mspe.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1254: AND mpe.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1255: AND mspe.DELETED_FLAG= MSC_UTIL.SYS_NO
1256: AND mpe.PLAN_ID= -1;
1257:
1258: CURSOR c_del_leg IS
1259: SELECT
1286: lv_val_2 NUMBER;
1287: lv_val_3 NUMBER;
1288:
1289: BEGIN
1290: IF MSC_CL_COLLECTION.v_instance_type = MSC_UTIL.G_INS_OTHER THEN
1291:
1292: -- deleting if the record already existed.
1293: FOR c_rec IN c_del_leg LOOP
1294:
1360:
1361:
1362: EXCEPTION
1363: WHEN OTHERS THEN
1364: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '-----------------------------');
1365: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'SQL Executed: ' || lv_sql_stmt1 );
1366: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'INVENTORY_ITEM_ID: ' || c_rec.INVENTORY_ITEM_ID);
1367: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'ORGANIZATION_ID: ' || c_rec.ORGANIZATION_ID);
1368: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'BILL_SEQUENCE_ID: ' || c_rec.BILL_SEQUENCE_ID );
1361:
1362: EXCEPTION
1363: WHEN OTHERS THEN
1364: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '-----------------------------');
1365: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'SQL Executed: ' || lv_sql_stmt1 );
1366: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'INVENTORY_ITEM_ID: ' || c_rec.INVENTORY_ITEM_ID);
1367: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'ORGANIZATION_ID: ' || c_rec.ORGANIZATION_ID);
1368: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'BILL_SEQUENCE_ID: ' || c_rec.BILL_SEQUENCE_ID );
1369: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'ROUTING_SEQUENCE_ID: ' || c_rec.ROUTING_SEQUENCE_ID);
1362: EXCEPTION
1363: WHEN OTHERS THEN
1364: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '-----------------------------');
1365: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'SQL Executed: ' || lv_sql_stmt1 );
1366: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'INVENTORY_ITEM_ID: ' || c_rec.INVENTORY_ITEM_ID);
1367: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'ORGANIZATION_ID: ' || c_rec.ORGANIZATION_ID);
1368: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'BILL_SEQUENCE_ID: ' || c_rec.BILL_SEQUENCE_ID );
1369: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'ROUTING_SEQUENCE_ID: ' || c_rec.ROUTING_SEQUENCE_ID);
1370: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'LINE_ID: ' || c_rec.LINE_ID);
1363: WHEN OTHERS THEN
1364: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '-----------------------------');
1365: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'SQL Executed: ' || lv_sql_stmt1 );
1366: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'INVENTORY_ITEM_ID: ' || c_rec.INVENTORY_ITEM_ID);
1367: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'ORGANIZATION_ID: ' || c_rec.ORGANIZATION_ID);
1368: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'BILL_SEQUENCE_ID: ' || c_rec.BILL_SEQUENCE_ID );
1369: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'ROUTING_SEQUENCE_ID: ' || c_rec.ROUTING_SEQUENCE_ID);
1370: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'LINE_ID: ' || c_rec.LINE_ID);
1371: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '-----------------------------');
1364: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '-----------------------------');
1365: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'SQL Executed: ' || lv_sql_stmt1 );
1366: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'INVENTORY_ITEM_ID: ' || c_rec.INVENTORY_ITEM_ID);
1367: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'ORGANIZATION_ID: ' || c_rec.ORGANIZATION_ID);
1368: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'BILL_SEQUENCE_ID: ' || c_rec.BILL_SEQUENCE_ID );
1369: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'ROUTING_SEQUENCE_ID: ' || c_rec.ROUTING_SEQUENCE_ID);
1370: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'LINE_ID: ' || c_rec.LINE_ID);
1371: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '-----------------------------');
1372: END;
1365: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'SQL Executed: ' || lv_sql_stmt1 );
1366: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'INVENTORY_ITEM_ID: ' || c_rec.INVENTORY_ITEM_ID);
1367: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'ORGANIZATION_ID: ' || c_rec.ORGANIZATION_ID);
1368: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'BILL_SEQUENCE_ID: ' || c_rec.BILL_SEQUENCE_ID );
1369: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'ROUTING_SEQUENCE_ID: ' || c_rec.ROUTING_SEQUENCE_ID);
1370: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'LINE_ID: ' || c_rec.LINE_ID);
1371: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '-----------------------------');
1372: END;
1373: /*
1366: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'INVENTORY_ITEM_ID: ' || c_rec.INVENTORY_ITEM_ID);
1367: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'ORGANIZATION_ID: ' || c_rec.ORGANIZATION_ID);
1368: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'BILL_SEQUENCE_ID: ' || c_rec.BILL_SEQUENCE_ID );
1369: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'ROUTING_SEQUENCE_ID: ' || c_rec.ROUTING_SEQUENCE_ID);
1370: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'LINE_ID: ' || c_rec.LINE_ID);
1371: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '-----------------------------');
1372: END;
1373: /*
1374: DELETE MSC_PROCESS_EFFECTIVITY
1367: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'ORGANIZATION_ID: ' || c_rec.ORGANIZATION_ID);
1368: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'BILL_SEQUENCE_ID: ' || c_rec.BILL_SEQUENCE_ID );
1369: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'ROUTING_SEQUENCE_ID: ' || c_rec.ROUTING_SEQUENCE_ID);
1370: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'LINE_ID: ' || c_rec.LINE_ID);
1371: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '-----------------------------');
1372: END;
1373: /*
1374: DELETE MSC_PROCESS_EFFECTIVITY
1375: WHERE PLAN_ID= -1
1490: WHEN OTHERS THEN
1491:
1492: IF SQLCODE IN (-01653,-01650,-01562,-01683) THEN
1493:
1494: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1495: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1496: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_PROCESS_EFFECTIVITY');
1497: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_PROCESS_EFFECTIVITY');
1498: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1494: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1495: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1496: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_PROCESS_EFFECTIVITY');
1497: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_PROCESS_EFFECTIVITY');
1498: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1499:
1500: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1501: RAISE;
1502:
1496: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_PROCESS_EFFECTIVITY');
1497: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_PROCESS_EFFECTIVITY');
1498: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1499:
1500: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1501: RAISE;
1502:
1503: ELSE
1504: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1500: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1501: RAISE;
1502:
1503: ELSE
1504: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1505:
1506: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1507: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1508: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_PROCESS_EFFECTIVITY');
1502:
1503: ELSE
1504: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1505:
1506: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1507: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1508: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_PROCESS_EFFECTIVITY');
1509: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_PROCESS_EFFECTIVITY');
1510: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1506: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1507: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1508: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_PROCESS_EFFECTIVITY');
1509: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_PROCESS_EFFECTIVITY');
1510: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1511:
1512: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1513: FND_MESSAGE.SET_TOKEN('COLUMN', 'ITEM_NAME');
1514: FND_MESSAGE.SET_TOKEN('VALUE', MSC_CL_ITEM_ODS_LOAD.ITEM_NAME( c_rec.INVENTORY_ITEM_ID));
1511:
1512: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1513: FND_MESSAGE.SET_TOKEN('COLUMN', 'ITEM_NAME');
1514: FND_MESSAGE.SET_TOKEN('VALUE', MSC_CL_ITEM_ODS_LOAD.ITEM_NAME( c_rec.INVENTORY_ITEM_ID));
1515: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1516:
1517: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1518: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
1519: FND_MESSAGE.SET_TOKEN('VALUE',
1518: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
1519: FND_MESSAGE.SET_TOKEN('VALUE',
1520: MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
1521: MSC_CL_COLLECTION.v_instance_id));
1522: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1523:
1524: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1525: FND_MESSAGE.SET_TOKEN('COLUMN', 'BILL_SEQUENCE_ID');
1526: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.BILL_SEQUENCE_ID));
1523:
1524: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1525: FND_MESSAGE.SET_TOKEN('COLUMN', 'BILL_SEQUENCE_ID');
1526: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.BILL_SEQUENCE_ID));
1527: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1528:
1529: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1530: FND_MESSAGE.SET_TOKEN('COLUMN', 'ROUTING_SEQUENCE_ID');
1531: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.ROUTING_SEQUENCE_ID));
1528:
1529: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1530: FND_MESSAGE.SET_TOKEN('COLUMN', 'ROUTING_SEQUENCE_ID');
1531: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.ROUTING_SEQUENCE_ID));
1532: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1533:
1534: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1535: END IF;
1536: END;
1530: FND_MESSAGE.SET_TOKEN('COLUMN', 'ROUTING_SEQUENCE_ID');
1531: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.ROUTING_SEQUENCE_ID));
1532: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1533:
1534: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1535: END IF;
1536: END;
1537:
1538: END LOOP;
1546: WHERE PLAN_ID= -1
1547: AND SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1548: AND REFRESH_NUMBER <> MSC_CL_COLLECTION.v_last_collection_id; */
1549:
1550: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1551: v_sub_str :='AND REFRESH_NUMBER <>'||MSC_CL_COLLECTION.v_last_collection_id;
1552: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_PROCESS_EFFECTIVITY', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
1553: ELSE
1554: v_sub_str :=' AND ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1550: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1551: v_sub_str :='AND REFRESH_NUMBER <>'||MSC_CL_COLLECTION.v_last_collection_id;
1552: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_PROCESS_EFFECTIVITY', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
1553: ELSE
1554: v_sub_str :=' AND ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1555: ||' AND REFRESH_NUMBER <>'||MSC_CL_COLLECTION.v_last_collection_id;
1556: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_PROCESS_EFFECTIVITY', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
1557: END IF;
1558:
1585: MSC_ST_BOMS msb
1586: WHERE t1.SR_INVENTORY_ITEM_ID= msb.assembly_item_id
1587: AND t1.sr_instance_id= msb.sr_instance_id
1588: AND msb.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1589: AND msb.DELETED_FLAG= MSC_UTIL.SYS_NO;
1590:
1591: CURSOR c2_d IS
1592: SELECT
1593: msb.BILL_SEQUENCE_ID,
1593: msb.BILL_SEQUENCE_ID,
1594: msb.SR_INSTANCE_ID
1595: FROM MSC_ST_BOMS msb
1596: WHERE msb.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1597: AND msb.DELETED_FLAG= MSC_UTIL.SYS_YES;
1598:
1599:
1600: lv_tbl VARCHAR2(30);
1601: lv_sql_stmt VARCHAR2(5000);
1670: ||' :v_current_user )';
1671:
1672: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
1673:
1674: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1675: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOMS', MSC_CL_COLLECTION.v_instance_id, -1);
1676: ELSE
1677: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1678: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOMS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
1673:
1674: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1675: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOMS', MSC_CL_COLLECTION.v_instance_id, -1);
1676: ELSE
1677: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1678: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOMS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
1679: END IF;
1680:
1681: BEGIN
1722: ||' MSC_ST_BOMS msb '
1723: ||' WHERE t1.SR_INVENTORY_ITEM_ID= msb.assembly_item_id'
1724: ||' AND t1.sr_instance_id= msb.sr_instance_id'
1725: ||' AND msb.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id
1726: ||' AND msb.DELETED_FLAG= '||MSC_UTIL.SYS_NO;
1727:
1728: EXECUTE IMMEDIATE lv_sql_ins
1729: USING MSC_CL_COLLECTION.v_last_collection_id,MSC_CL_COLLECTION.v_current_date,MSC_CL_COLLECTION.v_current_user,MSC_CL_COLLECTION.v_current_date,MSC_CL_COLLECTION.v_current_user;
1730:
1732: EXCEPTION
1733: WHEN OTHERS THEN
1734: IF SQLCODE IN (-01653,-01650,-01562,-01683) THEN
1735:
1736: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1737: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1738: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOM');
1739: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BOMS');
1740: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1736: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1737: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1738: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOM');
1739: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BOMS');
1740: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1741:
1742: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1743: RAISE;
1744:
1738: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOM');
1739: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BOMS');
1740: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1741:
1742: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1743: RAISE;
1744:
1745: ELSE
1746: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1742: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1743: RAISE;
1744:
1745: ELSE
1746: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1747: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1748: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOM');
1749: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BOMS');
1750: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1746: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1747: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1748: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOM');
1749: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BOMS');
1750: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1751:
1752: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1753:
1754: --If Direct path load results in warning then the processing has to be
1748: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOM');
1749: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BOMS');
1750: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1751:
1752: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1753:
1754: --If Direct path load results in warning then the processing has to be
1755: --switched back to row by row processing. This will help to identify the
1756: --erroneous record and will also help in processing the rest of the records.
1877: EXCEPTION
1878: WHEN OTHERS THEN
1879: IF SQLCODE IN (-01653,-01650,-01562,-01683) THEN
1880:
1881: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1882: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1883: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOM');
1884: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BOMS');
1885: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1881: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1882: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1883: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOM');
1884: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BOMS');
1885: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1886:
1887: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1888: RAISE;
1889:
1883: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOM');
1884: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BOMS');
1885: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1886:
1887: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1888: RAISE;
1889:
1890: ELSE
1891:
1888: RAISE;
1889:
1890: ELSE
1891:
1892: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1893:
1894: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1895: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1896: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOM');
1890: ELSE
1891:
1892: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1893:
1894: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1895: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1896: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOM');
1897: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BOMS');
1898: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1894: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1895: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1896: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOM');
1897: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BOMS');
1898: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1899:
1900: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1901: FND_MESSAGE.SET_TOKEN('COLUMN', 'ASSEMBLY_ITEM_NAME');
1902: FND_MESSAGE.SET_TOKEN('VALUE', MSC_CL_ITEM_ODS_LOAD.ITEM_NAME(lb_ASSEMBLY_ITEM_ID(j)));
1899:
1900: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1901: FND_MESSAGE.SET_TOKEN('COLUMN', 'ASSEMBLY_ITEM_NAME');
1902: FND_MESSAGE.SET_TOKEN('VALUE', MSC_CL_ITEM_ODS_LOAD.ITEM_NAME(lb_ASSEMBLY_ITEM_ID(j)));
1903: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1904:
1905: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1906: FND_MESSAGE.SET_TOKEN('COLUMN', 'BILL_SEQUENCE_ID');
1907: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( lb_BILL_SEQUENCE_ID(j)));
1904:
1905: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1906: FND_MESSAGE.SET_TOKEN('COLUMN', 'BILL_SEQUENCE_ID');
1907: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( lb_BILL_SEQUENCE_ID(j)));
1908: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1909:
1910: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1911:
1912: END IF;
1906: FND_MESSAGE.SET_TOKEN('COLUMN', 'BILL_SEQUENCE_ID');
1907: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( lb_BILL_SEQUENCE_ID(j)));
1908: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1909:
1910: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1911:
1912: END IF;
1913: END;
1914: END LOOP;
1968: AND t2.sr_instance_id= msbc.sr_instance_id
1969: AND t3.SR_INVENTORY_ITEM_ID(+)= msbc.driving_item_id
1970: AND t3.sr_instance_id(+)= msbc.sr_instance_id
1971: AND msbc.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1972: AND msbc.DELETED_FLAG= MSC_UTIL.SYS_NO;
1973:
1974: CURSOR c1_d IS
1975: SELECT
1976: msbc.COMPONENT_SEQUENCE_ID,
1977: msbc.BILL_SEQUENCE_ID,
1978: msbc.SR_INSTANCE_ID
1979: FROM MSC_ST_BOM_COMPONENTS msbc
1980: WHERE msbc.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1981: AND msbc.DELETED_FLAG= MSC_UTIL.SYS_YES;
1982:
1983:
1984: lv_tbl VARCHAR2(30);
1985: lv_sql_stmt VARCHAR2(5000);
2034:
2035: BEGIN
2036:
2037:
2038: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
2039: lv_tbl:= 'BOM_COMPONENTS_'||MSC_CL_COLLECTION.v_instance_code;
2040: ELSE
2041: lv_tbl:= 'MSC_BOM_COMPONENTS';
2042: END IF;
2125:
2126: --fix for the bug#3283959
2127: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
2128:
2129: if (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO) then
2130: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOM_COMPONENTS', MSC_CL_COLLECTION.v_instance_id, -1);
2131: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2132: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOM_COMPONENTS', MSC_CL_COLLECTION.v_instance_id, -1);
2133: ELSE
2127: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
2128:
2129: if (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO) then
2130: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOM_COMPONENTS', MSC_CL_COLLECTION.v_instance_id, -1);
2131: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2132: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOM_COMPONENTS', MSC_CL_COLLECTION.v_instance_id, -1);
2133: ELSE
2134: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2135: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOM_COMPONENTS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
2130: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOM_COMPONENTS', MSC_CL_COLLECTION.v_instance_id, -1);
2131: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2132: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOM_COMPONENTS', MSC_CL_COLLECTION.v_instance_id, -1);
2133: ELSE
2134: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2135: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOM_COMPONENTS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
2136: END IF;
2137: end if;
2138:
2229: ||' AND t2.sr_instance_id = msbc.sr_instance_id'
2230: ||' AND t3.SR_INVENTORY_ITEM_ID(+) = msbc.driving_item_id'
2231: ||' AND t3.sr_instance_id(+) = msbc.sr_instance_id'
2232: ||' AND msbc.SR_INSTANCE_ID = '||MSC_CL_COLLECTION.v_instance_id
2233: ||' AND msbc.DELETED_FLAG = '||MSC_UTIL.SYS_NO;
2234:
2235: EXECUTE IMMEDIATE lv_sql_ins
2236: USING MSC_CL_COLLECTION.v_last_collection_id,MSC_CL_COLLECTION.v_current_date,MSC_CL_COLLECTION.v_current_user,MSC_CL_COLLECTION.v_current_date,MSC_CL_COLLECTION.v_current_user;
2237:
2239: EXCEPTION
2240: WHEN OTHERS THEN
2241: IF SQLCODE IN (-01653,-01650,-01562,-01683) THEN
2242:
2243: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2244: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2245: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOM_COMPONENTS');
2246: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BOM_COMPONENTS');
2247: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2243: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2244: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2245: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOM_COMPONENTS');
2246: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BOM_COMPONENTS');
2247: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2248:
2249: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2250: RAISE;
2251:
2245: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOM_COMPONENTS');
2246: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BOM_COMPONENTS');
2247: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2248:
2249: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2250: RAISE;
2251:
2252: ELSE
2253: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2249: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2250: RAISE;
2251:
2252: ELSE
2253: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2254: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2255: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOM_COMPONENTS');
2256: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BOM_COMPONENTS');
2257: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2253: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2254: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2255: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOM_COMPONENTS');
2256: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BOM_COMPONENTS');
2257: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2258:
2259: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2260:
2261: --If Direct path load results in warning then the processing has to be
2255: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOM_COMPONENTS');
2256: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BOM_COMPONENTS');
2257: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2258:
2259: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2260:
2261: --If Direct path load results in warning then the processing has to be
2262: --switched back to row by row processing. This will help to identify the
2263: --erroneous record and will also help in processing the rest of the records.
2309: END IF;
2310:
2311: EXCEPTION
2312: WHEN OTHERS THEN
2313: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '------------------------------------------------');
2314: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'SQL Executed: ' || lv_sql_stmt1 );
2315: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'BILL SEQUENCE ID: ' || c_rec.BILL_SEQUENCE_ID );
2316: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'COMPONENT SEQUENCE ID: ' || c_rec.COMPONENT_SEQUENCE_ID);
2317: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '------------------------------------------------');
2310:
2311: EXCEPTION
2312: WHEN OTHERS THEN
2313: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '------------------------------------------------');
2314: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'SQL Executed: ' || lv_sql_stmt1 );
2315: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'BILL SEQUENCE ID: ' || c_rec.BILL_SEQUENCE_ID );
2316: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'COMPONENT SEQUENCE ID: ' || c_rec.COMPONENT_SEQUENCE_ID);
2317: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '------------------------------------------------');
2318: END;
2311: EXCEPTION
2312: WHEN OTHERS THEN
2313: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '------------------------------------------------');
2314: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'SQL Executed: ' || lv_sql_stmt1 );
2315: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'BILL SEQUENCE ID: ' || c_rec.BILL_SEQUENCE_ID );
2316: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'COMPONENT SEQUENCE ID: ' || c_rec.COMPONENT_SEQUENCE_ID);
2317: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '------------------------------------------------');
2318: END;
2319:
2312: WHEN OTHERS THEN
2313: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '------------------------------------------------');
2314: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'SQL Executed: ' || lv_sql_stmt1 );
2315: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'BILL SEQUENCE ID: ' || c_rec.BILL_SEQUENCE_ID );
2316: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'COMPONENT SEQUENCE ID: ' || c_rec.COMPONENT_SEQUENCE_ID);
2317: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '------------------------------------------------');
2318: END;
2319:
2320: /*
2313: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '------------------------------------------------');
2314: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'SQL Executed: ' || lv_sql_stmt1 );
2315: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'BILL SEQUENCE ID: ' || c_rec.BILL_SEQUENCE_ID );
2316: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'COMPONENT SEQUENCE ID: ' || c_rec.COMPONENT_SEQUENCE_ID);
2317: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '------------------------------------------------');
2318: END;
2319:
2320: /*
2321: UPDATE MSC_BOM_COMPONENTS
2493: EXCEPTION
2494: WHEN OTHERS THEN
2495: IF SQLCODE IN (-01653,-01650,-01562,-01683) THEN
2496:
2497: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2498: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2499: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOM_COMPONENTS');
2500: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BOM_COMPONENTS');
2501: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2497: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2498: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2499: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOM_COMPONENTS');
2500: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BOM_COMPONENTS');
2501: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2502:
2503: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2504: RAISE;
2505:
2499: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOM_COMPONENTS');
2500: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BOM_COMPONENTS');
2501: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2502:
2503: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2504: RAISE;
2505:
2506: ELSE
2507:
2504: RAISE;
2505:
2506: ELSE
2507:
2508: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2509:
2510: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2511: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2512: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOM_COMPONENTS');
2506: ELSE
2507:
2508: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2509:
2510: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2511: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2512: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOM_COMPONENTS');
2513: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BOM_COMPONENTS');
2514: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2510: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2511: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2512: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOM_COMPONENTS');
2513: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BOM_COMPONENTS');
2514: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2515:
2516: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2517: FND_MESSAGE.SET_TOKEN('COLUMN', 'BILL_SEQUENCE_ID');
2518: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( lb_BILL_SEQUENCE_ID(j)));
2515:
2516: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2517: FND_MESSAGE.SET_TOKEN('COLUMN', 'BILL_SEQUENCE_ID');
2518: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( lb_BILL_SEQUENCE_ID(j)));
2519: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2520:
2521: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2522: FND_MESSAGE.SET_TOKEN('COLUMN', 'COMPONENT_SEQUENCE_ID');
2523: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( lb_COMPONENT_SEQUENCE_ID(j)));
2520:
2521: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2522: FND_MESSAGE.SET_TOKEN('COLUMN', 'COMPONENT_SEQUENCE_ID');
2523: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( lb_COMPONENT_SEQUENCE_ID(j)));
2524: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2525:
2526: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2527:
2528: END IF;
2522: FND_MESSAGE.SET_TOKEN('COLUMN', 'COMPONENT_SEQUENCE_ID');
2523: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( lb_COMPONENT_SEQUENCE_ID(j)));
2524: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2525:
2526: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2527:
2528: END IF;
2529: END;
2530:
2537: END IF;
2538:
2539: BEGIN
2540:
2541: IF ((MSC_CL_COLLECTION.v_coll_prec.org_group_flag <> MSC_UTIL.G_ALL_ORGANIZATIONS ) AND (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES)) THEN
2542:
2543: lv_tbl:= 'BOM_COMPONENTS_'||MSC_CL_COLLECTION.v_instance_code;
2544:
2545: lv_sql_stmt:=
2542:
2543: lv_tbl:= 'BOM_COMPONENTS_'||MSC_CL_COLLECTION.v_instance_code;
2544:
2545: lv_sql_stmt:=
2546: 'INSERT INTO '||MSC_UTIL.G_MSC_SCHEMA||'.'||lv_tbl
2547: ||' SELECT * from '||MSC_UTIL.G_MSC_SCHEMA||'.MSC_BOM_COMPONENTS'
2548: ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
2549: ||' AND plan_id = -1 '
2550: ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
2543: lv_tbl:= 'BOM_COMPONENTS_'||MSC_CL_COLLECTION.v_instance_code;
2544:
2545: lv_sql_stmt:=
2546: 'INSERT INTO '||MSC_UTIL.G_MSC_SCHEMA||'.'||lv_tbl
2547: ||' SELECT * from '||MSC_UTIL.G_MSC_SCHEMA||'.MSC_BOM_COMPONENTS'
2548: ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
2549: ||' AND plan_id = -1 '
2550: ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
2551:
2546: 'INSERT INTO '||MSC_UTIL.G_MSC_SCHEMA||'.'||lv_tbl
2547: ||' SELECT * from '||MSC_UTIL.G_MSC_SCHEMA||'.MSC_BOM_COMPONENTS'
2548: ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
2549: ||' AND plan_id = -1 '
2550: ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
2551:
2552: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The sql statement is '||lv_sql_stmt);
2553: EXECUTE IMMEDIATE lv_sql_stmt;
2554:
2548: ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
2549: ||' AND plan_id = -1 '
2550: ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
2551:
2552: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The sql statement is '||lv_sql_stmt);
2553: EXECUTE IMMEDIATE lv_sql_stmt;
2554:
2555: COMMIT;
2556:
2555: COMMIT;
2556:
2557: END IF;
2558:
2559: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
2560: MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
2561: lv_retcode,
2562: 'MSC_BOM_COMPONENTS',
2563: MSC_CL_COLLECTION.v_instance_code,
2560: MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
2561: lv_retcode,
2562: 'MSC_BOM_COMPONENTS',
2563: MSC_CL_COLLECTION.v_instance_code,
2564: MSC_UTIL.G_WARNING
2565: );
2566:
2567: IF lv_retcode = MSC_UTIL.G_ERROR THEN
2568: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
2563: MSC_CL_COLLECTION.v_instance_code,
2564: MSC_UTIL.G_WARNING
2565: );
2566:
2567: IF lv_retcode = MSC_UTIL.G_ERROR THEN
2568: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
2569: RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
2570: ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
2571: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2564: MSC_UTIL.G_WARNING
2565: );
2566:
2567: IF lv_retcode = MSC_UTIL.G_ERROR THEN
2568: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
2569: RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
2570: ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
2571: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2572: END IF;
2566:
2567: IF lv_retcode = MSC_UTIL.G_ERROR THEN
2568: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
2569: RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
2570: ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
2571: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2572: END IF;
2573:
2574: END IF;
2567: IF lv_retcode = MSC_UTIL.G_ERROR THEN
2568: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
2569: RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
2570: ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
2571: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2572: END IF;
2573:
2574: END IF;
2575:
2575:
2576: EXCEPTION
2577: WHEN OTHERS THEN
2578:
2579: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2580: RAISE;
2581: END;
2582:
2583: END LOAD_BOM_COMPONENTS;
2731: into lv_bom, lv_wip
2732: from msc_coll_parameters
2733: where instance_id = MSC_CL_COLLECTION.v_instance_id;
2734:
2735: IF (((lv_bom = MSC_UTIL.SYS_YES) OR (lv_wip = MSC_UTIL.SYS_YES)) AND NOT MSC_CL_COLLECTION.v_is_legacy_refresh) THEN
2736: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2737: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_DEPT_RES_INSTANCES', MSC_CL_COLLECTION.v_instance_id, -1);
2738: ELSE
2739: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2732: from msc_coll_parameters
2733: where instance_id = MSC_CL_COLLECTION.v_instance_id;
2734:
2735: IF (((lv_bom = MSC_UTIL.SYS_YES) OR (lv_wip = MSC_UTIL.SYS_YES)) AND NOT MSC_CL_COLLECTION.v_is_legacy_refresh) THEN
2736: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2737: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_DEPT_RES_INSTANCES', MSC_CL_COLLECTION.v_instance_id, -1);
2738: ELSE
2739: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2740: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_DEPT_RES_INSTANCES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str); /* ds change change */
2735: IF (((lv_bom = MSC_UTIL.SYS_YES) OR (lv_wip = MSC_UTIL.SYS_YES)) AND NOT MSC_CL_COLLECTION.v_is_legacy_refresh) THEN
2736: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2737: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_DEPT_RES_INSTANCES', MSC_CL_COLLECTION.v_instance_id, -1);
2738: ELSE
2739: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2740: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_DEPT_RES_INSTANCES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str); /* ds change change */
2741: END IF;
2742: END IF;
2743: /* ds change change end */
2747:
2748:
2749: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESOURCE_GROUPS', MSC_CL_COLLECTION.v_instance_id, NULL); /*No Organization_id Column */
2750:
2751: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2752: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_DEPARTMENT_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1);
2753: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SIMULATION_SETS', MSC_CL_COLLECTION.v_instance_id,NULL);
2754: ELSE
2755: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2751: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2752: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_DEPARTMENT_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1);
2753: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SIMULATION_SETS', MSC_CL_COLLECTION.v_instance_id,NULL);
2754: ELSE
2755: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2756: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_DEPARTMENT_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
2757: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SIMULATION_SETS', MSC_CL_COLLECTION.v_instance_id,NULL,v_sub_str);
2758: END IF;
2759:
2758: END IF;
2759:
2760: ELSE
2761:
2762: IF (((lv_bom = MSC_UTIL.SYS_YES) OR (lv_wip = MSC_UTIL.SYS_YES)) AND NOT MSC_CL_COLLECTION.v_is_legacy_refresh) THEN
2763:
2764: /* Bug 3041176 - Delete Only Department resources in NetChange */
2765:
2766: p_instance_id := MSC_CL_COLLECTION.v_instance_id;
2772: ||' FROM '||p_table_name||' mdr, msc_trading_partners mtp'
2773: ||' WHERE mdr.SR_INSTANCE_ID= :p_instance_id'
2774: ||' AND mdr.PLAN_ID= -1 '
2775: ||' AND mdr.LINE_FLAG = 2'
2776: ||' AND mdr.organization_id '|| MSC_UTIL.v_in_org_str
2777: ||' AND mtp.sr_instance_id = :p_instance_id'
2778: ||' AND mtp.sr_tp_id = mdr.organization_id'
2779: ||' AND mtp.partner_type = 3'
2780: ||' AND mtp.organization_type = 1';
2789: lv_sql_stmt:= 'DELETE '||p_table_name||' mdr'
2790: ||' WHERE mdr.SR_INSTANCE_ID= :lv_instance_id'
2791: ||' AND mdr.PLAN_ID= -1 '
2792: ||' AND mdr.LINE_FLAG = 2'
2793: ||' AND mdr.organization_id '|| MSC_UTIL.v_in_org_str
2794: ||' AND mdr.organization_id = '
2795: ||' (SELECT mtp.sr_tp_id'
2796: ||' FROM msc_trading_partners mtp'
2797: ||' WHERE mtp.sr_instance_id = :lv_instance_id'
2809: lv_sql_stmt:= 'DELETE '||p_table_name||' mdr'
2810: ||' WHERE mdr.SR_INSTANCE_ID= :p_instance_id '
2811: ||' AND mdr.PLAN_ID= -1 '
2812: ||' AND mdr.LINE_FLAG = 2'
2813: ||' AND mdr.organization_id '|| MSC_UTIL.v_in_org_str
2814: ||' AND ROWNUM < :lv_pbs'
2815: ||' AND mdr.organization_id = '
2816: ||' (SELECT mtp.sr_tp_id'
2817: ||' FROM msc_trading_partners mtp'
2859:
2860: BEGIN
2861:
2862: IF (lv_res_incr_refresh) THEN
2863: IF c_rec.DELETED_FLAG= MSC_UTIL.SYS_YES THEN
2864:
2865: UPDATE MSC_DEPARTMENT_RESOURCES
2866: SET CAPACITY_UNITS= 0,
2867: MAX_RATE= 0,
3090: WHEN OTHERS THEN
3091:
3092: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
3093:
3094: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3095: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3096: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE');
3097: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DEPARTMENT_RESOURCES');
3098: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3094: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3095: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3096: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE');
3097: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DEPARTMENT_RESOURCES');
3098: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3099:
3100: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3101: RAISE;
3102:
3096: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE');
3097: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DEPARTMENT_RESOURCES');
3098: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3099:
3100: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3101: RAISE;
3102:
3103: ELSE
3104: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3100: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3101: RAISE;
3102:
3103: ELSE
3104: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3105:
3106: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3107: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3108: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE');
3102:
3103: ELSE
3104: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3105:
3106: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3107: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3108: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE');
3109: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DEPARTMENT_RESOURCES');
3110: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3106: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3107: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3108: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE');
3109: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DEPARTMENT_RESOURCES');
3110: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3111:
3112: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3113: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
3114: FND_MESSAGE.SET_TOKEN('VALUE',
3113: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
3114: FND_MESSAGE.SET_TOKEN('VALUE',
3115: MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
3116: MSC_CL_COLLECTION.v_instance_id));
3117: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3118:
3119: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3120: FND_MESSAGE.SET_TOKEN('COLUMN', 'DEPARTMENT_CODE');
3121: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.DEPARTMENT_CODE);
3118:
3119: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3120: FND_MESSAGE.SET_TOKEN('COLUMN', 'DEPARTMENT_CODE');
3121: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.DEPARTMENT_CODE);
3122: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3123:
3124: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3125: FND_MESSAGE.SET_TOKEN('COLUMN', 'RESOURCE_CODE');
3126: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.RESOURCE_CODE);
3123:
3124: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3125: FND_MESSAGE.SET_TOKEN('COLUMN', 'RESOURCE_CODE');
3126: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.RESOURCE_CODE);
3127: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3128:
3129: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3130: END IF;
3131:
3125: FND_MESSAGE.SET_TOKEN('COLUMN', 'RESOURCE_CODE');
3126: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.RESOURCE_CODE);
3127: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3128:
3129: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3130: END IF;
3131:
3132: END;
3133:
3193: WHEN OTHERS THEN
3194:
3195: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
3196:
3197: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3198: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3199: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE');
3200: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DEPT_RES_INSTANCES');
3201: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3197: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3198: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3199: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE');
3200: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DEPT_RES_INSTANCES');
3201: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3202:
3203: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3204: RAISE;
3205:
3199: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE');
3200: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DEPT_RES_INSTANCES');
3201: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3202:
3203: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3204: RAISE;
3205:
3206: ELSE
3207: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3203: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3204: RAISE;
3205:
3206: ELSE
3207: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3208:
3209: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3210: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3211: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE');
3205:
3206: ELSE
3207: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3208:
3209: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3210: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3211: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE');
3212: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DEPT_RES_INSTANCES');
3213: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3209: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3210: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3211: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE');
3212: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DEPT_RES_INSTANCES');
3213: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3214:
3215: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3216: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_ID');
3217: FND_MESSAGE.SET_TOKEN('VALUE',c_rec.ORGANIZATION_ID);
3214:
3215: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3216: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_ID');
3217: FND_MESSAGE.SET_TOKEN('VALUE',c_rec.ORGANIZATION_ID);
3218: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3219:
3220: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3221: FND_MESSAGE.SET_TOKEN('COLUMN', 'DEPARTMENT_ID');
3222: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.DEPARTMENT_ID);
3219:
3220: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3221: FND_MESSAGE.SET_TOKEN('COLUMN', 'DEPARTMENT_ID');
3222: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.DEPARTMENT_ID);
3223: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3224:
3225: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3226: FND_MESSAGE.SET_TOKEN('COLUMN', 'RESOURCE_ID');
3227: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.RESOURCE_ID);
3224:
3225: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3226: FND_MESSAGE.SET_TOKEN('COLUMN', 'RESOURCE_ID');
3227: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.RESOURCE_ID);
3228: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3229:
3230: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3231: FND_MESSAGE.SET_TOKEN('COLUMN', 'RES_INSTANCE_ID');
3232: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.RES_INSTANCE_ID);
3229:
3230: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3231: FND_MESSAGE.SET_TOKEN('COLUMN', 'RES_INSTANCE_ID');
3232: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.RES_INSTANCE_ID);
3233: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3234: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3235: END IF;
3236:
3237: END;
3230: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3231: FND_MESSAGE.SET_TOKEN('COLUMN', 'RES_INSTANCE_ID');
3232: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.RES_INSTANCE_ID);
3233: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3234: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3235: END IF;
3236:
3237: END;
3238:
3302: WHEN OTHERS THEN
3303:
3304: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
3305:
3306: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3307: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3308: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE');
3309: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SIMULATION_SETS');
3310: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3306: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3307: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3308: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE');
3309: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SIMULATION_SETS');
3310: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3311:
3312: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3313: RAISE;
3314:
3308: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE');
3309: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SIMULATION_SETS');
3310: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3311:
3312: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3313: RAISE;
3314:
3315: ELSE
3316: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3312: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3313: RAISE;
3314:
3315: ELSE
3316: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3317:
3318: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3319: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3320: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE');
3314:
3315: ELSE
3316: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3317:
3318: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3319: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3320: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE');
3321: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SIMULATION_SETS');
3322: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3318: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3319: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3320: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE');
3321: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SIMULATION_SETS');
3322: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3323:
3324: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3325: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
3326: FND_MESSAGE.SET_TOKEN('VALUE',
3325: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
3326: FND_MESSAGE.SET_TOKEN('VALUE',
3327: MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
3328: MSC_CL_COLLECTION.v_instance_id));
3329: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3330:
3331: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3332: FND_MESSAGE.SET_TOKEN('COLUMN', 'SIMULATION_SET');
3333: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SIMULATION_SET);
3330:
3331: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3332: FND_MESSAGE.SET_TOKEN('COLUMN', 'SIMULATION_SET');
3333: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SIMULATION_SET);
3334: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3335:
3336: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3337: END IF;
3338:
3332: FND_MESSAGE.SET_TOKEN('COLUMN', 'SIMULATION_SET');
3333: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SIMULATION_SET);
3334: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3335:
3336: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3337: END IF;
3338:
3339: END;
3340:
3387: WHEN OTHERS THEN
3388:
3389: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
3390:
3391: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3392: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3393: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE');
3394: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_GROUPS');
3395: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3391: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3392: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3393: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE');
3394: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_GROUPS');
3395: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3396:
3397: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3398: RAISE;
3399:
3393: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE');
3394: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_GROUPS');
3395: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3396:
3397: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3398: RAISE;
3399:
3400: ELSE
3401: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3397: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3398: RAISE;
3399:
3400: ELSE
3401: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3402:
3403: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3404: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3405: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE');
3399:
3400: ELSE
3401: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3402:
3403: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3404: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3405: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE');
3406: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_GROUPS');
3407: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3403: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3404: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3405: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE');
3406: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_GROUPS');
3407: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3408:
3409: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3410: FND_MESSAGE.SET_TOKEN('COLUMN', 'GROUP_CODE');
3411: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.GROUP_CODE);
3408:
3409: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3410: FND_MESSAGE.SET_TOKEN('COLUMN', 'GROUP_CODE');
3411: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.GROUP_CODE);
3412: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3413:
3414: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3415: END IF;
3416:
3410: FND_MESSAGE.SET_TOKEN('COLUMN', 'GROUP_CODE');
3411: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.GROUP_CODE);
3412: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3413:
3414: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3415: END IF;
3416:
3417: END;
3418: