[Home] [Help]
3:
4:
5: v_sub_str VARCHAR2(4000):=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;
40: total_count NUMBER := 0;
41: BEGIN
42:
43: IF ((MSC_CL_COLLECTION.v_is_complete_refresh) OR (MSC_CL_COLLECTION.v_is_partial_refresh)) THEN
44: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
45: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESOURCE_SETUPS', MSC_CL_COLLECTION.v_instance_id, -1);
46: ELSE
47: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
48: MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_RESOURCE_SETUPS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
43: IF ((MSC_CL_COLLECTION.v_is_complete_refresh) OR (MSC_CL_COLLECTION.v_is_partial_refresh)) THEN
44: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
45: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESOURCE_SETUPS', MSC_CL_COLLECTION.v_instance_id, -1);
46: ELSE
47: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
48: MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_RESOURCE_SETUPS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
49: END IF;
50:
51:
95: WHEN OTHERS THEN
96:
97: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
98:
99: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
100: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
101: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE_SETUP');
102: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_SETUPS');
103: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
99: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
100: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
101: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE_SETUP');
102: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_SETUPS');
103: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
104:
105: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
106: RAISE;
107:
101: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE_SETUP');
102: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_SETUPS');
103: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
104:
105: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
106: RAISE;
107:
108: ELSE
109: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
105: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
106: RAISE;
107:
108: ELSE
109: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
110:
111: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
112: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
113: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE_SETUP');
107:
108: ELSE
109: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
110:
111: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
112: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
113: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE_SETUP');
114: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_SETUPS');
115: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
111: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
112: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
113: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE_SETUP');
114: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_SETUPS');
115: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
116:
117: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
118: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
119: FND_MESSAGE.SET_TOKEN('VALUE',
118: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
119: FND_MESSAGE.SET_TOKEN('VALUE',
120: MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
121: MSC_CL_COLLECTION.v_instance_id));
122: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
123:
124: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
125: FND_MESSAGE.SET_TOKEN('COLUMN', 'RESOURCE_ID');
126: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.RESOURCE_ID);
123:
124: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
125: FND_MESSAGE.SET_TOKEN('COLUMN', 'RESOURCE_ID');
126: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.RESOURCE_ID);
127: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
128:
129: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
130: FND_MESSAGE.SET_TOKEN('COLUMN', 'SETUP_CODE');
131: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SETUP_CODE);
128:
129: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
130: FND_MESSAGE.SET_TOKEN('COLUMN', 'SETUP_CODE');
131: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SETUP_CODE);
132: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
133:
134: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
135: END IF;
136:
130: FND_MESSAGE.SET_TOKEN('COLUMN', 'SETUP_CODE');
131: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SETUP_CODE);
132: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
133:
134: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
135: END IF;
136:
137: END;
138:
136:
137: END;
138:
139: END LOOP;
140: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Total resource setups = '|| to_char(total_count));
141:
142: COMMIT;
143: END IF; /* MSC_CL_COLLECTION.v_is_complete_refresh OR v_is_partial_refresh */
144: END LOAD_RESOURCE_SETUP;
165: total_count NUMBER := 0;
166: BEGIN
167:
168: IF ((MSC_CL_COLLECTION.v_is_complete_refresh) OR (MSC_CL_COLLECTION.v_is_partial_refresh)) THEN
169: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
170: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SETUP_TRANSITIONS', MSC_CL_COLLECTION.v_instance_id, -1);
171: ELSE
172: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
173: MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_SETUP_TRANSITIONS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
168: IF ((MSC_CL_COLLECTION.v_is_complete_refresh) OR (MSC_CL_COLLECTION.v_is_partial_refresh)) THEN
169: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
170: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SETUP_TRANSITIONS', MSC_CL_COLLECTION.v_instance_id, -1);
171: ELSE
172: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
173: MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_SETUP_TRANSITIONS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
174: END IF;
175:
176:
224: WHEN OTHERS THEN
225:
226: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
227:
228: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
229: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
230: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SETUP_TRANSITION');
231: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SETUP_TRANSITIONS');
232: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
228: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
229: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
230: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SETUP_TRANSITION');
231: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SETUP_TRANSITIONS');
232: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
233:
234: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
235: RAISE;
236: ELSE
230: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SETUP_TRANSITION');
231: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SETUP_TRANSITIONS');
232: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
233:
234: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
235: RAISE;
236: ELSE
237: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
238:
233:
234: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
235: RAISE;
236: ELSE
237: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
238:
239: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
240: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
241: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SETUP_TRANSITION');
235: RAISE;
236: ELSE
237: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
238:
239: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
240: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
241: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SETUP_TRANSITION');
242: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SETUP_TRANSITIONS');
243: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
239: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
240: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
241: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SETUP_TRANSITION');
242: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SETUP_TRANSITIONS');
243: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
244:
245: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
246: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
247: FND_MESSAGE.SET_TOKEN('VALUE',
246: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
247: FND_MESSAGE.SET_TOKEN('VALUE',
248: MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
249: MSC_CL_COLLECTION.v_instance_id));
250: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
251:
252: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
253: FND_MESSAGE.SET_TOKEN('COLUMN', 'RESOURCE_ID');
254: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.RESOURCE_ID);
251:
252: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
253: FND_MESSAGE.SET_TOKEN('COLUMN', 'RESOURCE_ID');
254: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.RESOURCE_ID);
255: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
256:
257: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
258: FND_MESSAGE.SET_TOKEN('COLUMN', 'FROM_SETUP_ID');
259: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.FROM_SETUP_ID);
256:
257: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
258: FND_MESSAGE.SET_TOKEN('COLUMN', 'FROM_SETUP_ID');
259: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.FROM_SETUP_ID);
260: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
261:
262:
263: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
264: FND_MESSAGE.SET_TOKEN('COLUMN', 'TO_SETUP_ID');
262:
263: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
264: FND_MESSAGE.SET_TOKEN('COLUMN', 'TO_SETUP_ID');
265: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.TO_SETUP_ID);
266: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
267: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
268: END IF;
269:
270: END;
263: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
264: FND_MESSAGE.SET_TOKEN('COLUMN', 'TO_SETUP_ID');
265: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.TO_SETUP_ID);
266: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
267: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
268: END IF;
269:
270: END;
271:
269:
270: END;
271:
272: END LOOP;
273: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Total resource transition = '|| to_char(total_count));
274:
275: COMMIT;
276:
277: END IF; /* MSC_CL_COLLECTION.v_is_complete_refresh) OR (v_is_partial_refresh */
299: lv_CHARGE_END_DATETIME DATE;
300:
301:
302: BEGIN
303: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
304: lv_res_req_tbl := 'RESOURCE_REQUIREMENTS_'||MSC_CL_COLLECTION.v_instance_code;
305: ELSE
306: lv_res_req_tbl := 'MSC_RESOURCE_REQUIREMENTS';
307: END IF;
398: EXCEPTION
399: WHEN OTHERS THEN
400: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
401:
402: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
403: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
404: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE_CHARGES');
405: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_CHARGES');
406: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
402: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
403: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
404: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE_CHARGES');
405: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_CHARGES');
406: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
407:
408: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
409: RAISE;
410:
404: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE_CHARGES');
405: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_CHARGES');
406: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
407:
408: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
409: RAISE;
410:
411: ELSE
412:
409: RAISE;
410:
411: ELSE
412:
413: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
414:
415: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
416: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
417: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE_CHARGES');
411: ELSE
412:
413: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
414:
415: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
416: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
417: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE_CHARGES');
418: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_CHARGES');
419: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
415: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
416: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
417: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE_CHARGES');
418: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_CHARGES');
419: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
420:
421: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
422: FND_MESSAGE.SET_TOKEN('COLUMN', 'RES_TRANSACTION_ID');
423: FND_MESSAGE.SET_TOKEN('VALUE', lv_TRANSACTION_ID);
420:
421: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
422: FND_MESSAGE.SET_TOKEN('COLUMN', 'RES_TRANSACTION_ID');
423: FND_MESSAGE.SET_TOKEN('VALUE', lv_TRANSACTION_ID);
424: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
425:
426: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
427: FND_MESSAGE.SET_TOKEN('COLUMN', 'CHARGE_NUMBER');
428: FND_MESSAGE.SET_TOKEN('VALUE', lv_CHARGE_NUMBER);
425:
426: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
427: FND_MESSAGE.SET_TOKEN('COLUMN', 'CHARGE_NUMBER');
428: FND_MESSAGE.SET_TOKEN('VALUE', lv_CHARGE_NUMBER);
429: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
430:
431: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
432: END IF;
433:
427: FND_MESSAGE.SET_TOKEN('COLUMN', 'CHARGE_NUMBER');
428: FND_MESSAGE.SET_TOKEN('VALUE', lv_CHARGE_NUMBER);
429: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
430:
431: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
432: END IF;
433:
434: END;
435: END LOOP;
432: END IF;
433:
434: END;
435: END LOOP;
436: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Total resource charges = '|| total_count);
437: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
438: COMMIT;
439: END IF;
440: EXCEPTION
440: EXCEPTION
441: WHEN OTHERS THEN
442: IF c4%ISOPEN THEN CLOSE c4; END IF;
443:
444: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
446: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
447: END IF;
448: IF lv_sql_stmt IS NOT NULL THEN
442: IF c4%ISOPEN THEN CLOSE c4; END IF;
443:
444: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
445: IF lv_cursor_stmt IS NOT NULL THEN
446: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
448: IF lv_sql_stmt IS NOT NULL THEN
449: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
450: END IF;
445: IF lv_cursor_stmt IS NOT NULL THEN
446: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
447: END IF;
448: IF lv_sql_stmt IS NOT NULL THEN
449: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
451: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
452: RAISE;
453: END LOAD_RESOURCE_CHARGES;
447: END IF;
448: IF lv_sql_stmt IS NOT NULL THEN
449: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<
450: END IF;
451: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
452: RAISE;
453: END LOAD_RESOURCE_CHARGES;
454:
455: PROCEDURE LOAD_RES_INST_CHANGE IS
484: lv_instance_code varchar2(10);
485:
486: Begin
487:
488: IF ((MSC_CL_COLLECTION.v_is_partial_refresh AND MSC_CL_COLLECTION.v_coll_prec.calendar_flag = MSC_UTIL.SYS_YES) OR
489: MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_incremental_refresh) then
490:
491: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
492:
501: FOR c_rec IN res_inst_chngs LOOP
502:
503: BEGIN
504:
505: IF MSC_CL_COLLECTION.v_is_incremental_refresh AND c_rec.DELETED_FLAG= MSC_UTIL.SYS_YES THEN
506:
507: DELETE MSC_RES_INSTANCE_CHANGES
508: WHERE DEPARTMENT_ID = c_rec.DEPARTMENT_ID
509: AND RESOURCE_ID = c_rec.RESOURCE_ID
511: AND SERIAL_NUMBER = c_rec.SERIAL_NUMBER
512: --AND EQUIPMENT_ITEM_ID = c_rec.EQUIPMENT_ITEM_ID
513: AND SHIFT_NUM= c_rec.SHIFT_NUM
514: AND FROM_DATE= c_rec.FROM_DATE
515: AND NVL(TO_DATE,MSC_UTIL.NULL_DATE)= NVL(c_rec.TO_DATE,MSC_UTIL.NULL_DATE)
516: AND NVL(FROM_TIME,MSC_UTIL.NULL_VALUE)= NVL(c_rec.FROM_TIME,MSC_UTIL.NULL_VALUE)
517: AND NVL(TO_TIME,MSC_UTIL.NULL_VALUE)= NVL(c_rec.TO_TIME,MSC_UTIL.NULL_VALUE)
518: AND SIMULATION_SET= c_rec.SIMULATION_SET
519: AND ACTION_TYPE= c_rec.ACTION_TYPE
512: --AND EQUIPMENT_ITEM_ID = c_rec.EQUIPMENT_ITEM_ID
513: AND SHIFT_NUM= c_rec.SHIFT_NUM
514: AND FROM_DATE= c_rec.FROM_DATE
515: AND NVL(TO_DATE,MSC_UTIL.NULL_DATE)= NVL(c_rec.TO_DATE,MSC_UTIL.NULL_DATE)
516: AND NVL(FROM_TIME,MSC_UTIL.NULL_VALUE)= NVL(c_rec.FROM_TIME,MSC_UTIL.NULL_VALUE)
517: AND NVL(TO_TIME,MSC_UTIL.NULL_VALUE)= NVL(c_rec.TO_TIME,MSC_UTIL.NULL_VALUE)
518: AND SIMULATION_SET= c_rec.SIMULATION_SET
519: AND ACTION_TYPE= c_rec.ACTION_TYPE
520: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
513: AND SHIFT_NUM= c_rec.SHIFT_NUM
514: AND FROM_DATE= c_rec.FROM_DATE
515: AND NVL(TO_DATE,MSC_UTIL.NULL_DATE)= NVL(c_rec.TO_DATE,MSC_UTIL.NULL_DATE)
516: AND NVL(FROM_TIME,MSC_UTIL.NULL_VALUE)= NVL(c_rec.FROM_TIME,MSC_UTIL.NULL_VALUE)
517: AND NVL(TO_TIME,MSC_UTIL.NULL_VALUE)= NVL(c_rec.TO_TIME,MSC_UTIL.NULL_VALUE)
518: AND SIMULATION_SET= c_rec.SIMULATION_SET
519: AND ACTION_TYPE= c_rec.ACTION_TYPE
520: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
521:
574:
575: WHEN OTHERS THEN
576:
577: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
578: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
579: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
580: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RES_INST_CHANGE');
581: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RES_INSTANCE_CHANGES');
582: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
578: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
579: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
580: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RES_INST_CHANGE');
581: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RES_INSTANCE_CHANGES');
582: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
583:
584: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
585: RAISE;
586:
580: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RES_INST_CHANGE');
581: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RES_INSTANCE_CHANGES');
582: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
583:
584: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
585: RAISE;
586:
587: ELSE
588: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
584: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
585: RAISE;
586:
587: ELSE
588: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
589:
590: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
591: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
592: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RES_INST_CHANGE');
586:
587: ELSE
588: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
589:
590: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
591: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
592: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RES_INST_CHANGE');
593: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RES_INSTANCE_CHANGES');
594: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
590: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
591: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
592: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RES_INST_CHANGE');
593: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RES_INSTANCE_CHANGES');
594: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
595:
596: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
597: FND_MESSAGE.SET_TOKEN('COLUMN', 'DEPARTMENT_ID');
598: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.DEPARTMENT_ID));
595:
596: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
597: FND_MESSAGE.SET_TOKEN('COLUMN', 'DEPARTMENT_ID');
598: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.DEPARTMENT_ID));
599: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
600:
601: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
602: FND_MESSAGE.SET_TOKEN('COLUMN', 'RESOURCE_ID');
603: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.RESOURCE_ID));
600:
601: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
602: FND_MESSAGE.SET_TOKEN('COLUMN', 'RESOURCE_ID');
603: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.RESOURCE_ID));
604: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
605:
606:
607: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
608: FND_MESSAGE.SET_TOKEN('COLUMN', 'RES_INSTANCE_ID');
606:
607: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
608: FND_MESSAGE.SET_TOKEN('COLUMN', 'RES_INSTANCE_ID');
609: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.RES_INSTANCE_ID));
610: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
611:
612: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
613: FND_MESSAGE.SET_TOKEN('COLUMN', 'SERIAL_NUMBER');
614: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SERIAL_NUMBER);
611:
612: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
613: FND_MESSAGE.SET_TOKEN('COLUMN', 'SERIAL_NUMBER');
614: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SERIAL_NUMBER);
615: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
616:
617: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
618: FND_MESSAGE.SET_TOKEN('COLUMN', 'SHIFT_NUM');
619: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SHIFT_NUM));
616:
617: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
618: FND_MESSAGE.SET_TOKEN('COLUMN', 'SHIFT_NUM');
619: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SHIFT_NUM));
620: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
621:
622: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
623: FND_MESSAGE.SET_TOKEN('COLUMN', 'ACTION_TYPE');
624: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.ACTION_TYPE));
621:
622: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
623: FND_MESSAGE.SET_TOKEN('COLUMN', 'ACTION_TYPE');
624: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.ACTION_TYPE));
625: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
626:
627: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
628: FND_MESSAGE.SET_TOKEN('COLUMN', 'SIMULATION_SET');
629: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SIMULATION_SET);
626:
627: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
628: FND_MESSAGE.SET_TOKEN('COLUMN', 'SIMULATION_SET');
629: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SIMULATION_SET);
630: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
631:
632: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
633: END IF;
634:
628: FND_MESSAGE.SET_TOKEN('COLUMN', 'SIMULATION_SET');
629: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SIMULATION_SET);
630: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
631:
632: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
633: END IF;
634:
635: END;
636:
634:
635: END;
636:
637: END LOOP;
638: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Total res instance changes = '|| total_count);
639:
640: COMMIT;
641:
642: END IF;
663: MSC_ST_COMPONENT_SUBSTITUTES mscs
664: WHERE t1.SR_INVENTORY_ITEM_ID= mscs.substitute_item_id
665: AND t1.sr_instance_id= mscs.sr_instance_id
666: AND mscs.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
667: AND mscs.DELETED_FLAG= MSC_UTIL.SYS_NO;
668:
669: CURSOR c1_d IS
670: SELECT
671: mscs.BILL_SEQUENCE_ID,
677: WHERE ((t1.SR_INVENTORY_ITEM_ID= mscs.substitute_item_id) AND (mscs.substitute_item_id
678: is NOT NULL))
679: AND t1.sr_instance_id= mscs.sr_instance_id
680: AND mscs.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
681: AND mscs.DELETED_FLAG= MSC_UTIL.SYS_YES
682: UNION ALL
683: SELECT
684: mscs.BILL_SEQUENCE_ID,
685: mscs.COMPONENT_SEQUENCE_ID,
687: mscs.SR_INSTANCE_ID
688: FROM MSC_ST_COMPONENT_SUBSTITUTES mscs
689: WHERE mscs.substitute_item_id IS NULL
690: AND mscs.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
691: AND mscs.DELETED_FLAG= MSC_UTIL.SYS_YES;
692:
693:
694: c_count NUMBER:= 0;
695: lv_tbl VARCHAR2(30);
701: -- We want to delete all BOM related data and get new stuff.
702:
703: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_COMPONENT_SUBSTITUTES', MSC_CL_COLLECTION.v_instance_id, -1);
704:
705: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
706: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_COMPONENT_SUBSTITUTES', MSC_CL_COLLECTION.v_instance_id, -1);
707: ELSE
708: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
709: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_COMPONENT_SUBSTITUTES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
704:
705: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
706: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_COMPONENT_SUBSTITUTES', MSC_CL_COLLECTION.v_instance_id, -1);
707: ELSE
708: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
709: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_COMPONENT_SUBSTITUTES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
710: END IF;
711:
712: END IF; -- MSC_CL_COLLECTION.v_is_complete_refresh
831: EXCEPTION
832:
833: WHEN OTHERS THEN
834:
835: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
836:
837: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
838: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
839: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_COMPONENT_SUBSTITUTE');
833: WHEN OTHERS THEN
834:
835: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
836:
837: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
838: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
839: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_COMPONENT_SUBSTITUTE');
840: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_COMPONENT_SUBSTITUTES');
841: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
837: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
838: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
839: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_COMPONENT_SUBSTITUTE');
840: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_COMPONENT_SUBSTITUTES');
841: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
842:
843: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
844: FND_MESSAGE.SET_TOKEN('COLUMN', 'BILL_SEQUENCE_ID');
845: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.BILL_SEQUENCE_ID));
842:
843: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
844: FND_MESSAGE.SET_TOKEN('COLUMN', 'BILL_SEQUENCE_ID');
845: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.BILL_SEQUENCE_ID));
846: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
847:
848: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
849: FND_MESSAGE.SET_TOKEN('COLUMN', 'COMPONENT_SEQUENCE_ID');
850: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.COMPONENT_SEQUENCE_ID));
847:
848: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
849: FND_MESSAGE.SET_TOKEN('COLUMN', 'COMPONENT_SEQUENCE_ID');
850: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.COMPONENT_SEQUENCE_ID));
851: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
852:
853: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
854: FND_MESSAGE.SET_TOKEN('COLUMN', 'SUBSTITUTE_ITEM_NAME');
855: FND_MESSAGE.SET_TOKEN('VALUE', MSC_CL_ITEM_ODS_LOAD.ITEM_NAME( c_rec.SUBSTITUTE_ITEM_ID));
852:
853: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
854: FND_MESSAGE.SET_TOKEN('COLUMN', 'SUBSTITUTE_ITEM_NAME');
855: FND_MESSAGE.SET_TOKEN('VALUE', MSC_CL_ITEM_ODS_LOAD.ITEM_NAME( c_rec.SUBSTITUTE_ITEM_ID));
856: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
857:
858: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
859: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
860: FND_MESSAGE.SET_TOKEN('VALUE',
859: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
860: FND_MESSAGE.SET_TOKEN('VALUE',
861: MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
862: MSC_CL_COLLECTION.v_instance_id));
863: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
864:
865: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
866:
867: END;
861: MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
862: MSC_CL_COLLECTION.v_instance_id));
863: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
864:
865: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
866:
867: END;
868:
869: END LOOP;
922:
923: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BILL_OF_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1);
924: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOR_REQUIREMENTS', MSC_CL_COLLECTION.v_instance_id, -1);
925:
926: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
927: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BILL_OF_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1);
928: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOR_REQUIREMENTS', MSC_CL_COLLECTION.v_instance_id, -1);
929: ELSE
930: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
926: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
927: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BILL_OF_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1);
928: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOR_REQUIREMENTS', MSC_CL_COLLECTION.v_instance_id, -1);
929: ELSE
930: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
931: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BILL_OF_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
932: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOR_REQUIREMENTS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
933: END IF;
934:
1003: WHEN OTHERS THEN
1004:
1005: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
1006:
1007: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1008: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1009: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOR');
1010: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BILL_OF_RESOURCES');
1011: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1007: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1008: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1009: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOR');
1010: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BILL_OF_RESOURCES');
1011: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1012:
1013: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1014: RAISE;
1015:
1009: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOR');
1010: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BILL_OF_RESOURCES');
1011: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1012:
1013: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1014: RAISE;
1015:
1016: ELSE
1017: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1013: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1014: RAISE;
1015:
1016: ELSE
1017: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1018:
1019: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1020: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1021: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOR');
1015:
1016: ELSE
1017: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1018:
1019: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1020: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1021: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOR');
1022: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BILL_OF_RESOURCES');
1023: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1019: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1020: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1021: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOR');
1022: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BILL_OF_RESOURCES');
1023: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1024:
1025: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1026: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
1027: FND_MESSAGE.SET_TOKEN('VALUE',
1026: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
1027: FND_MESSAGE.SET_TOKEN('VALUE',
1028: MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
1029: MSC_CL_COLLECTION.v_instance_id));
1030: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1031:
1032: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1033: FND_MESSAGE.SET_TOKEN('COLUMN', 'BILL_OF_RESOURCES');
1034: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.BILL_OF_RESOURCES);
1031:
1032: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1033: FND_MESSAGE.SET_TOKEN('COLUMN', 'BILL_OF_RESOURCES');
1034: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.BILL_OF_RESOURCES);
1035: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1036:
1037: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1038:
1039: END IF;
1033: FND_MESSAGE.SET_TOKEN('COLUMN', 'BILL_OF_RESOURCES');
1034: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.BILL_OF_RESOURCES);
1035: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1036:
1037: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1038:
1039: END IF;
1040: END;
1041:
1145: WHEN OTHERS THEN
1146:
1147: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
1148:
1149: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1150: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1151: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOR');
1152: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BOR_REQUIREMENTS');
1153: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1149: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1150: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1151: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOR');
1152: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BOR_REQUIREMENTS');
1153: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1154:
1155: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1156: RAISE;
1157:
1151: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOR');
1152: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BOR_REQUIREMENTS');
1153: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1154:
1155: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1156: RAISE;
1157:
1158: ELSE
1159: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1155: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1156: RAISE;
1157:
1158: ELSE
1159: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1160:
1161: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1162: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1163: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOR');
1157:
1158: ELSE
1159: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1160:
1161: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1162: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1163: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOR');
1164: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BOR_REQUIREMENTS');
1165: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1161: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1162: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1163: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOR');
1164: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BOR_REQUIREMENTS');
1165: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1166:
1167: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1168: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TRANSACTION_ID');
1169: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.SR_TRANSACTION_ID));
1166:
1167: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1168: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TRANSACTION_ID');
1169: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.SR_TRANSACTION_ID));
1170: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1171:
1172: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1173: FND_MESSAGE.SET_TOKEN('COLUMN', 'DEPARTMENT_ID');
1174: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.DEPARTMENT_ID);
1171:
1172: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1173: FND_MESSAGE.SET_TOKEN('COLUMN', 'DEPARTMENT_ID');
1174: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.DEPARTMENT_ID);
1175: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1176:
1177: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1178: FND_MESSAGE.SET_TOKEN('COLUMN', 'RESOURCE_ID');
1179: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.RESOURCE_ID);
1176:
1177: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1178: FND_MESSAGE.SET_TOKEN('COLUMN', 'RESOURCE_ID');
1179: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.RESOURCE_ID);
1180: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1181:
1182: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1183:
1184: END IF;
1178: FND_MESSAGE.SET_TOKEN('COLUMN', 'RESOURCE_ID');
1179: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.RESOURCE_ID);
1180: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1181:
1182: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1183:
1184: END IF;
1185: END;
1186:
1215: MSC_ST_PROCESS_EFFECTIVITY msor
1216: WHERE miil.SR_INVENTORY_ITEM_ID= msor.ITEM_ID
1217: AND miil.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1218: AND msor.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1219: AND msor.DELETED_FLAG= MSC_UTIL.SYS_NO;
1220:
1221: CURSOR c8_d IS
1222: SELECT
1223: miil.INVENTORY_ITEM_ID, -- msor.ITEM_ID,
1230: MSC_ST_PROCESS_EFFECTIVITY msor
1231: WHERE miil.SR_INVENTORY_ITEM_ID(+)= msor.ITEM_ID
1232: AND miil.SR_INSTANCE_ID(+)= MSC_CL_COLLECTION.v_instance_id
1233: AND msor.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1234: AND msor.DELETED_FLAG= MSC_UTIL.SYS_YES
1235: UNION ALL
1236: SELECT
1237: TO_NUMBER(NULL),
1238: TO_NUMBER(NULL),
1245: WHERE mspe.Bill_Sequence_ID= mpe.Bill_Sequence_ID
1246: AND mspe.Routing_Sequence_ID <> mpe.Routing_Sequence_ID
1247: AND mspe.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1248: AND mpe.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1249: AND mspe.DELETED_FLAG= MSC_UTIL.SYS_NO
1250: AND mpe.PLAN_ID= -1;
1251:
1252: CURSOR c_del_leg IS
1253: SELECT
1269: lv_val_2 NUMBER;
1270: lv_val_3 NUMBER;
1271:
1272: BEGIN
1273: IF MSC_CL_COLLECTION.v_instance_type = MSC_UTIL.G_INS_OTHER THEN
1274:
1275: -- deleting if the record already existed.
1276: FOR c_rec IN c_del_leg LOOP
1277:
1341:
1342:
1343: EXCEPTION
1344: WHEN OTHERS THEN
1345: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '-----------------------------');
1346: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'SQL Executed: ' || lv_sql_stmt1 );
1347: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'INVENTORY_ITEM_ID: ' || c_rec.INVENTORY_ITEM_ID);
1348: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'ORGANIZATION_ID: ' || c_rec.ORGANIZATION_ID);
1349: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'BILL_SEQUENCE_ID: ' || c_rec.BILL_SEQUENCE_ID );
1342:
1343: EXCEPTION
1344: WHEN OTHERS THEN
1345: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '-----------------------------');
1346: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'SQL Executed: ' || lv_sql_stmt1 );
1347: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'INVENTORY_ITEM_ID: ' || c_rec.INVENTORY_ITEM_ID);
1348: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'ORGANIZATION_ID: ' || c_rec.ORGANIZATION_ID);
1349: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'BILL_SEQUENCE_ID: ' || c_rec.BILL_SEQUENCE_ID );
1350: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'ROUTING_SEQUENCE_ID: ' || c_rec.ROUTING_SEQUENCE_ID);
1343: EXCEPTION
1344: WHEN OTHERS THEN
1345: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '-----------------------------');
1346: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'SQL Executed: ' || lv_sql_stmt1 );
1347: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'INVENTORY_ITEM_ID: ' || c_rec.INVENTORY_ITEM_ID);
1348: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'ORGANIZATION_ID: ' || c_rec.ORGANIZATION_ID);
1349: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'BILL_SEQUENCE_ID: ' || c_rec.BILL_SEQUENCE_ID );
1350: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'ROUTING_SEQUENCE_ID: ' || c_rec.ROUTING_SEQUENCE_ID);
1351: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'LINE_ID: ' || c_rec.LINE_ID);
1344: WHEN OTHERS THEN
1345: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '-----------------------------');
1346: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'SQL Executed: ' || lv_sql_stmt1 );
1347: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'INVENTORY_ITEM_ID: ' || c_rec.INVENTORY_ITEM_ID);
1348: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'ORGANIZATION_ID: ' || c_rec.ORGANIZATION_ID);
1349: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'BILL_SEQUENCE_ID: ' || c_rec.BILL_SEQUENCE_ID );
1350: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'ROUTING_SEQUENCE_ID: ' || c_rec.ROUTING_SEQUENCE_ID);
1351: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'LINE_ID: ' || c_rec.LINE_ID);
1352: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '-----------------------------');
1345: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '-----------------------------');
1346: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'SQL Executed: ' || lv_sql_stmt1 );
1347: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'INVENTORY_ITEM_ID: ' || c_rec.INVENTORY_ITEM_ID);
1348: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'ORGANIZATION_ID: ' || c_rec.ORGANIZATION_ID);
1349: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'BILL_SEQUENCE_ID: ' || c_rec.BILL_SEQUENCE_ID );
1350: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'ROUTING_SEQUENCE_ID: ' || c_rec.ROUTING_SEQUENCE_ID);
1351: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'LINE_ID: ' || c_rec.LINE_ID);
1352: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '-----------------------------');
1353: END;
1346: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'SQL Executed: ' || lv_sql_stmt1 );
1347: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'INVENTORY_ITEM_ID: ' || c_rec.INVENTORY_ITEM_ID);
1348: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'ORGANIZATION_ID: ' || c_rec.ORGANIZATION_ID);
1349: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'BILL_SEQUENCE_ID: ' || c_rec.BILL_SEQUENCE_ID );
1350: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'ROUTING_SEQUENCE_ID: ' || c_rec.ROUTING_SEQUENCE_ID);
1351: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'LINE_ID: ' || c_rec.LINE_ID);
1352: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '-----------------------------');
1353: END;
1354: /*
1347: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'INVENTORY_ITEM_ID: ' || c_rec.INVENTORY_ITEM_ID);
1348: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'ORGANIZATION_ID: ' || c_rec.ORGANIZATION_ID);
1349: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'BILL_SEQUENCE_ID: ' || c_rec.BILL_SEQUENCE_ID );
1350: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'ROUTING_SEQUENCE_ID: ' || c_rec.ROUTING_SEQUENCE_ID);
1351: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'LINE_ID: ' || c_rec.LINE_ID);
1352: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '-----------------------------');
1353: END;
1354: /*
1355: DELETE MSC_PROCESS_EFFECTIVITY
1348: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'ORGANIZATION_ID: ' || c_rec.ORGANIZATION_ID);
1349: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'BILL_SEQUENCE_ID: ' || c_rec.BILL_SEQUENCE_ID );
1350: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'ROUTING_SEQUENCE_ID: ' || c_rec.ROUTING_SEQUENCE_ID);
1351: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'LINE_ID: ' || c_rec.LINE_ID);
1352: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '-----------------------------');
1353: END;
1354: /*
1355: DELETE MSC_PROCESS_EFFECTIVITY
1356: WHERE PLAN_ID= -1
1471: WHEN OTHERS THEN
1472:
1473: IF SQLCODE IN (-01653,-01650,-01562,-01683) THEN
1474:
1475: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1476: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1477: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_PROCESS_EFFECTIVITY');
1478: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_PROCESS_EFFECTIVITY');
1479: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1475: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1476: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1477: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_PROCESS_EFFECTIVITY');
1478: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_PROCESS_EFFECTIVITY');
1479: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1480:
1481: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1482: RAISE;
1483:
1477: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_PROCESS_EFFECTIVITY');
1478: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_PROCESS_EFFECTIVITY');
1479: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1480:
1481: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1482: RAISE;
1483:
1484: ELSE
1485: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1481: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1482: RAISE;
1483:
1484: ELSE
1485: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1486:
1487: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1488: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1489: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_PROCESS_EFFECTIVITY');
1483:
1484: ELSE
1485: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1486:
1487: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1488: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1489: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_PROCESS_EFFECTIVITY');
1490: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_PROCESS_EFFECTIVITY');
1491: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1487: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1488: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1489: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_PROCESS_EFFECTIVITY');
1490: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_PROCESS_EFFECTIVITY');
1491: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1492:
1493: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1494: FND_MESSAGE.SET_TOKEN('COLUMN', 'ITEM_NAME');
1495: FND_MESSAGE.SET_TOKEN('VALUE', MSC_CL_ITEM_ODS_LOAD.ITEM_NAME( c_rec.INVENTORY_ITEM_ID));
1492:
1493: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1494: FND_MESSAGE.SET_TOKEN('COLUMN', 'ITEM_NAME');
1495: FND_MESSAGE.SET_TOKEN('VALUE', MSC_CL_ITEM_ODS_LOAD.ITEM_NAME( c_rec.INVENTORY_ITEM_ID));
1496: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1497:
1498: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1499: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
1500: FND_MESSAGE.SET_TOKEN('VALUE',
1499: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
1500: FND_MESSAGE.SET_TOKEN('VALUE',
1501: MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
1502: MSC_CL_COLLECTION.v_instance_id));
1503: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1504:
1505: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1506: FND_MESSAGE.SET_TOKEN('COLUMN', 'BILL_SEQUENCE_ID');
1507: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.BILL_SEQUENCE_ID));
1504:
1505: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1506: FND_MESSAGE.SET_TOKEN('COLUMN', 'BILL_SEQUENCE_ID');
1507: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.BILL_SEQUENCE_ID));
1508: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1509:
1510: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1511: FND_MESSAGE.SET_TOKEN('COLUMN', 'ROUTING_SEQUENCE_ID');
1512: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.ROUTING_SEQUENCE_ID));
1509:
1510: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1511: FND_MESSAGE.SET_TOKEN('COLUMN', 'ROUTING_SEQUENCE_ID');
1512: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.ROUTING_SEQUENCE_ID));
1513: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1514:
1515: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1516: END IF;
1517: END;
1511: FND_MESSAGE.SET_TOKEN('COLUMN', 'ROUTING_SEQUENCE_ID');
1512: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.ROUTING_SEQUENCE_ID));
1513: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1514:
1515: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1516: END IF;
1517: END;
1518:
1519: END LOOP;
1527: WHERE PLAN_ID= -1
1528: AND SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1529: AND REFRESH_NUMBER <> MSC_CL_COLLECTION.v_last_collection_id; */
1530:
1531: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1532: v_sub_str :='AND REFRESH_NUMBER <>'||MSC_CL_COLLECTION.v_last_collection_id;
1533: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_PROCESS_EFFECTIVITY', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
1534: ELSE
1535: v_sub_str :=' AND ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1531: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1532: v_sub_str :='AND REFRESH_NUMBER <>'||MSC_CL_COLLECTION.v_last_collection_id;
1533: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_PROCESS_EFFECTIVITY', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
1534: ELSE
1535: v_sub_str :=' AND ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1536: ||' AND REFRESH_NUMBER <>'||MSC_CL_COLLECTION.v_last_collection_id;
1537: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_PROCESS_EFFECTIVITY', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
1538: END IF;
1539:
1566: MSC_ST_BOMS msb
1567: WHERE t1.SR_INVENTORY_ITEM_ID= msb.assembly_item_id
1568: AND t1.sr_instance_id= msb.sr_instance_id
1569: AND msb.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1570: AND msb.DELETED_FLAG= MSC_UTIL.SYS_NO;
1571:
1572: CURSOR c2_d IS
1573: SELECT
1574: msb.BILL_SEQUENCE_ID,
1574: msb.BILL_SEQUENCE_ID,
1575: msb.SR_INSTANCE_ID
1576: FROM MSC_ST_BOMS msb
1577: WHERE msb.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1578: AND msb.DELETED_FLAG= MSC_UTIL.SYS_YES;
1579:
1580:
1581: lv_tbl VARCHAR2(30);
1582: lv_sql_stmt VARCHAR2(5000);
1651: ||' :v_current_user )';
1652:
1653: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
1654:
1655: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1656: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOMS', MSC_CL_COLLECTION.v_instance_id, -1);
1657: ELSE
1658: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1659: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOMS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
1654:
1655: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1656: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOMS', MSC_CL_COLLECTION.v_instance_id, -1);
1657: ELSE
1658: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1659: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOMS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
1660: END IF;
1661:
1662: BEGIN
1703: ||' MSC_ST_BOMS msb '
1704: ||' WHERE t1.SR_INVENTORY_ITEM_ID= msb.assembly_item_id'
1705: ||' AND t1.sr_instance_id= msb.sr_instance_id'
1706: ||' AND msb.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id
1707: ||' AND msb.DELETED_FLAG= '||MSC_UTIL.SYS_NO;
1708:
1709: EXECUTE IMMEDIATE lv_sql_ins
1710: USING MSC_CL_COLLECTION.v_last_collection_id,MSC_CL_COLLECTION.v_current_date,MSC_CL_COLLECTION.v_current_user,MSC_CL_COLLECTION.v_current_date,MSC_CL_COLLECTION.v_current_user;
1711:
1713: EXCEPTION
1714: WHEN OTHERS THEN
1715: IF SQLCODE IN (-01653,-01650,-01562,-01683) THEN
1716:
1717: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1718: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1719: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOM');
1720: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BOMS');
1721: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1717: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1718: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1719: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOM');
1720: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BOMS');
1721: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1722:
1723: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1724: RAISE;
1725:
1719: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOM');
1720: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BOMS');
1721: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1722:
1723: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1724: RAISE;
1725:
1726: ELSE
1727: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1723: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1724: RAISE;
1725:
1726: ELSE
1727: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1728: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1729: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOM');
1730: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BOMS');
1731: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1727: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1728: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1729: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOM');
1730: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BOMS');
1731: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1732:
1733: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1734:
1735: --If Direct path load results in warning then the processing has to be
1729: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOM');
1730: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BOMS');
1731: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1732:
1733: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1734:
1735: --If Direct path load results in warning then the processing has to be
1736: --switched back to row by row processing. This will help to identify the
1737: --erroneous record and will also help in processing the rest of the records.
1858: EXCEPTION
1859: WHEN OTHERS THEN
1860: IF SQLCODE IN (-01653,-01650,-01562,-01683) THEN
1861:
1862: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1863: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1864: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOM');
1865: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BOMS');
1866: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1862: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1863: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1864: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOM');
1865: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BOMS');
1866: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1867:
1868: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1869: RAISE;
1870:
1864: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOM');
1865: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BOMS');
1866: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1867:
1868: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1869: RAISE;
1870:
1871: ELSE
1872:
1869: RAISE;
1870:
1871: ELSE
1872:
1873: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1874:
1875: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1876: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1877: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOM');
1871: ELSE
1872:
1873: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1874:
1875: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1876: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1877: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOM');
1878: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BOMS');
1879: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1875: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1876: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1877: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOM');
1878: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BOMS');
1879: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1880:
1881: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1882: FND_MESSAGE.SET_TOKEN('COLUMN', 'ASSEMBLY_ITEM_NAME');
1883: FND_MESSAGE.SET_TOKEN('VALUE', MSC_CL_ITEM_ODS_LOAD.ITEM_NAME(lb_ASSEMBLY_ITEM_ID(j)));
1880:
1881: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1882: FND_MESSAGE.SET_TOKEN('COLUMN', 'ASSEMBLY_ITEM_NAME');
1883: FND_MESSAGE.SET_TOKEN('VALUE', MSC_CL_ITEM_ODS_LOAD.ITEM_NAME(lb_ASSEMBLY_ITEM_ID(j)));
1884: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1885:
1886: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1887: FND_MESSAGE.SET_TOKEN('COLUMN', 'BILL_SEQUENCE_ID');
1888: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( lb_BILL_SEQUENCE_ID(j)));
1885:
1886: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1887: FND_MESSAGE.SET_TOKEN('COLUMN', 'BILL_SEQUENCE_ID');
1888: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( lb_BILL_SEQUENCE_ID(j)));
1889: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1890:
1891: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1892:
1893: END IF;
1887: FND_MESSAGE.SET_TOKEN('COLUMN', 'BILL_SEQUENCE_ID');
1888: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( lb_BILL_SEQUENCE_ID(j)));
1889: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1890:
1891: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1892:
1893: END IF;
1894: END;
1895: END LOOP;
1949: AND t2.sr_instance_id= msbc.sr_instance_id
1950: AND t3.SR_INVENTORY_ITEM_ID(+)= msbc.driving_item_id
1951: AND t3.sr_instance_id(+)= msbc.sr_instance_id
1952: AND msbc.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1953: AND msbc.DELETED_FLAG= MSC_UTIL.SYS_NO;
1954:
1955: CURSOR c1_d IS
1956: SELECT
1957: msbc.COMPONENT_SEQUENCE_ID,
1958: msbc.BILL_SEQUENCE_ID,
1959: msbc.SR_INSTANCE_ID
1960: FROM MSC_ST_BOM_COMPONENTS msbc
1961: WHERE msbc.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1962: AND msbc.DELETED_FLAG= MSC_UTIL.SYS_YES;
1963:
1964:
1965: lv_tbl VARCHAR2(30);
1966: lv_sql_stmt VARCHAR2(5000);
2015:
2016: BEGIN
2017:
2018:
2019: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
2020: lv_tbl:= 'BOM_COMPONENTS_'||MSC_CL_COLLECTION.v_instance_code;
2021: ELSE
2022: lv_tbl:= 'MSC_BOM_COMPONENTS';
2023: END IF;
2106:
2107: --fix for the bug#3283959
2108: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
2109:
2110: if (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO) then
2111: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOM_COMPONENTS', MSC_CL_COLLECTION.v_instance_id, -1);
2112: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2113: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOM_COMPONENTS', MSC_CL_COLLECTION.v_instance_id, -1);
2114: ELSE
2108: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
2109:
2110: if (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO) then
2111: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOM_COMPONENTS', MSC_CL_COLLECTION.v_instance_id, -1);
2112: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2113: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOM_COMPONENTS', MSC_CL_COLLECTION.v_instance_id, -1);
2114: ELSE
2115: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2116: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOM_COMPONENTS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
2111: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOM_COMPONENTS', MSC_CL_COLLECTION.v_instance_id, -1);
2112: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2113: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOM_COMPONENTS', MSC_CL_COLLECTION.v_instance_id, -1);
2114: ELSE
2115: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2116: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOM_COMPONENTS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
2117: END IF;
2118: end if;
2119:
2210: ||' AND t2.sr_instance_id = msbc.sr_instance_id'
2211: ||' AND t3.SR_INVENTORY_ITEM_ID(+) = msbc.driving_item_id'
2212: ||' AND t3.sr_instance_id(+) = msbc.sr_instance_id'
2213: ||' AND msbc.SR_INSTANCE_ID = '||MSC_CL_COLLECTION.v_instance_id
2214: ||' AND msbc.DELETED_FLAG = '||MSC_UTIL.SYS_NO;
2215:
2216: EXECUTE IMMEDIATE lv_sql_ins
2217: USING MSC_CL_COLLECTION.v_last_collection_id,MSC_CL_COLLECTION.v_current_date,MSC_CL_COLLECTION.v_current_user,MSC_CL_COLLECTION.v_current_date,MSC_CL_COLLECTION.v_current_user;
2218:
2220: EXCEPTION
2221: WHEN OTHERS THEN
2222: IF SQLCODE IN (-01653,-01650,-01562,-01683) THEN
2223:
2224: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2225: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2226: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOM_COMPONENTS');
2227: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BOM_COMPONENTS');
2228: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2224: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2225: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2226: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOM_COMPONENTS');
2227: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BOM_COMPONENTS');
2228: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2229:
2230: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2231: RAISE;
2232:
2226: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOM_COMPONENTS');
2227: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BOM_COMPONENTS');
2228: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2229:
2230: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2231: RAISE;
2232:
2233: ELSE
2234: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2230: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2231: RAISE;
2232:
2233: ELSE
2234: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2235: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2236: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOM_COMPONENTS');
2237: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BOM_COMPONENTS');
2238: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2234: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2235: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2236: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOM_COMPONENTS');
2237: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BOM_COMPONENTS');
2238: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2239:
2240: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2241:
2242: --If Direct path load results in warning then the processing has to be
2236: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOM_COMPONENTS');
2237: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BOM_COMPONENTS');
2238: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2239:
2240: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2241:
2242: --If Direct path load results in warning then the processing has to be
2243: --switched back to row by row processing. This will help to identify the
2244: --erroneous record and will also help in processing the rest of the records.
2290: END IF;
2291:
2292: EXCEPTION
2293: WHEN OTHERS THEN
2294: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '------------------------------------------------');
2295: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'SQL Executed: ' || lv_sql_stmt1 );
2296: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'BILL SEQUENCE ID: ' || c_rec.BILL_SEQUENCE_ID );
2297: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'COMPONENT SEQUENCE ID: ' || c_rec.COMPONENT_SEQUENCE_ID);
2298: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '------------------------------------------------');
2291:
2292: EXCEPTION
2293: WHEN OTHERS THEN
2294: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '------------------------------------------------');
2295: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'SQL Executed: ' || lv_sql_stmt1 );
2296: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'BILL SEQUENCE ID: ' || c_rec.BILL_SEQUENCE_ID );
2297: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'COMPONENT SEQUENCE ID: ' || c_rec.COMPONENT_SEQUENCE_ID);
2298: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '------------------------------------------------');
2299: END;
2292: EXCEPTION
2293: WHEN OTHERS THEN
2294: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '------------------------------------------------');
2295: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'SQL Executed: ' || lv_sql_stmt1 );
2296: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'BILL SEQUENCE ID: ' || c_rec.BILL_SEQUENCE_ID );
2297: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'COMPONENT SEQUENCE ID: ' || c_rec.COMPONENT_SEQUENCE_ID);
2298: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '------------------------------------------------');
2299: END;
2300:
2293: WHEN OTHERS THEN
2294: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '------------------------------------------------');
2295: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'SQL Executed: ' || lv_sql_stmt1 );
2296: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'BILL SEQUENCE ID: ' || c_rec.BILL_SEQUENCE_ID );
2297: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'COMPONENT SEQUENCE ID: ' || c_rec.COMPONENT_SEQUENCE_ID);
2298: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '------------------------------------------------');
2299: END;
2300:
2301: /*
2294: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '------------------------------------------------');
2295: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'SQL Executed: ' || lv_sql_stmt1 );
2296: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'BILL SEQUENCE ID: ' || c_rec.BILL_SEQUENCE_ID );
2297: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'COMPONENT SEQUENCE ID: ' || c_rec.COMPONENT_SEQUENCE_ID);
2298: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '------------------------------------------------');
2299: END;
2300:
2301: /*
2302: UPDATE MSC_BOM_COMPONENTS
2474: EXCEPTION
2475: WHEN OTHERS THEN
2476: IF SQLCODE IN (-01653,-01650,-01562,-01683) THEN
2477:
2478: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2479: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2480: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOM_COMPONENTS');
2481: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BOM_COMPONENTS');
2482: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2478: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2479: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2480: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOM_COMPONENTS');
2481: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BOM_COMPONENTS');
2482: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2483:
2484: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2485: RAISE;
2486:
2480: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOM_COMPONENTS');
2481: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BOM_COMPONENTS');
2482: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2483:
2484: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2485: RAISE;
2486:
2487: ELSE
2488:
2485: RAISE;
2486:
2487: ELSE
2488:
2489: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2490:
2491: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2492: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2493: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOM_COMPONENTS');
2487: ELSE
2488:
2489: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2490:
2491: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2492: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2493: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOM_COMPONENTS');
2494: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BOM_COMPONENTS');
2495: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2491: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2492: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2493: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOM_COMPONENTS');
2494: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BOM_COMPONENTS');
2495: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2496:
2497: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2498: FND_MESSAGE.SET_TOKEN('COLUMN', 'BILL_SEQUENCE_ID');
2499: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( lb_BILL_SEQUENCE_ID(j)));
2496:
2497: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2498: FND_MESSAGE.SET_TOKEN('COLUMN', 'BILL_SEQUENCE_ID');
2499: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( lb_BILL_SEQUENCE_ID(j)));
2500: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2501:
2502: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2503: FND_MESSAGE.SET_TOKEN('COLUMN', 'COMPONENT_SEQUENCE_ID');
2504: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( lb_COMPONENT_SEQUENCE_ID(j)));
2501:
2502: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2503: FND_MESSAGE.SET_TOKEN('COLUMN', 'COMPONENT_SEQUENCE_ID');
2504: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( lb_COMPONENT_SEQUENCE_ID(j)));
2505: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2506:
2507: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2508:
2509: END IF;
2503: FND_MESSAGE.SET_TOKEN('COLUMN', 'COMPONENT_SEQUENCE_ID');
2504: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( lb_COMPONENT_SEQUENCE_ID(j)));
2505: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2506:
2507: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2508:
2509: END IF;
2510: END;
2511:
2518: END IF;
2519:
2520: BEGIN
2521:
2522: IF ((MSC_CL_COLLECTION.v_coll_prec.org_group_flag <> MSC_UTIL.G_ALL_ORGANIZATIONS ) AND (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES)) THEN
2523:
2524: lv_tbl:= 'BOM_COMPONENTS_'||MSC_CL_COLLECTION.v_instance_code;
2525:
2526: lv_sql_stmt:=
2527: 'INSERT INTO '||lv_tbl
2528: ||' SELECT * from MSC_BOM_COMPONENTS'
2529: ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
2530: ||' AND plan_id = -1 '
2531: ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
2532:
2533: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The sql statement is '||lv_sql_stmt);
2534: EXECUTE IMMEDIATE lv_sql_stmt;
2535:
2529: ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
2530: ||' AND plan_id = -1 '
2531: ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
2532:
2533: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The sql statement is '||lv_sql_stmt);
2534: EXECUTE IMMEDIATE lv_sql_stmt;
2535:
2536: COMMIT;
2537:
2536: COMMIT;
2537:
2538: END IF;
2539:
2540: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
2541: MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
2542: lv_retcode,
2543: 'MSC_BOM_COMPONENTS',
2544: MSC_CL_COLLECTION.v_instance_code,
2541: MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
2542: lv_retcode,
2543: 'MSC_BOM_COMPONENTS',
2544: MSC_CL_COLLECTION.v_instance_code,
2545: MSC_UTIL.G_WARNING
2546: );
2547:
2548: IF lv_retcode = MSC_UTIL.G_ERROR THEN
2549: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
2544: MSC_CL_COLLECTION.v_instance_code,
2545: MSC_UTIL.G_WARNING
2546: );
2547:
2548: IF lv_retcode = MSC_UTIL.G_ERROR THEN
2549: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
2550: RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
2551: ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
2552: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2545: MSC_UTIL.G_WARNING
2546: );
2547:
2548: IF lv_retcode = MSC_UTIL.G_ERROR THEN
2549: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
2550: RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
2551: ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
2552: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2553: END IF;
2547:
2548: IF lv_retcode = MSC_UTIL.G_ERROR THEN
2549: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
2550: RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
2551: ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
2552: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2553: END IF;
2554:
2555: END IF;
2548: IF lv_retcode = MSC_UTIL.G_ERROR THEN
2549: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
2550: RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
2551: ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
2552: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2553: END IF;
2554:
2555: END IF;
2556:
2556:
2557: EXCEPTION
2558: WHEN OTHERS THEN
2559:
2560: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2561: RAISE;
2562: END;
2563:
2564: END LOAD_BOM_COMPONENTS;
2712: into lv_bom, lv_wip
2713: from msc_coll_parameters
2714: where instance_id = MSC_CL_COLLECTION.v_instance_id;
2715:
2716: IF (((lv_bom = MSC_UTIL.SYS_YES) OR (lv_wip = MSC_UTIL.SYS_YES)) AND NOT MSC_CL_COLLECTION.v_is_legacy_refresh) THEN
2717: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2718: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_DEPT_RES_INSTANCES', MSC_CL_COLLECTION.v_instance_id, -1);
2719: ELSE
2720: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2713: from msc_coll_parameters
2714: where instance_id = MSC_CL_COLLECTION.v_instance_id;
2715:
2716: IF (((lv_bom = MSC_UTIL.SYS_YES) OR (lv_wip = MSC_UTIL.SYS_YES)) AND NOT MSC_CL_COLLECTION.v_is_legacy_refresh) THEN
2717: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2718: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_DEPT_RES_INSTANCES', MSC_CL_COLLECTION.v_instance_id, -1);
2719: ELSE
2720: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2721: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_DEPT_RES_INSTANCES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str); /* ds change change */
2716: IF (((lv_bom = MSC_UTIL.SYS_YES) OR (lv_wip = MSC_UTIL.SYS_YES)) AND NOT MSC_CL_COLLECTION.v_is_legacy_refresh) THEN
2717: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2718: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_DEPT_RES_INSTANCES', MSC_CL_COLLECTION.v_instance_id, -1);
2719: ELSE
2720: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2721: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_DEPT_RES_INSTANCES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str); /* ds change change */
2722: END IF;
2723: END IF;
2724: /* ds change change end */
2728:
2729:
2730: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESOURCE_GROUPS', MSC_CL_COLLECTION.v_instance_id, NULL); /*No Organization_id Column */
2731:
2732: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2733: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_DEPARTMENT_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1);
2734: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SIMULATION_SETS', MSC_CL_COLLECTION.v_instance_id,NULL);
2735: ELSE
2736: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2732: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2733: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_DEPARTMENT_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1);
2734: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SIMULATION_SETS', MSC_CL_COLLECTION.v_instance_id,NULL);
2735: ELSE
2736: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2737: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_DEPARTMENT_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
2738: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SIMULATION_SETS', MSC_CL_COLLECTION.v_instance_id,NULL,v_sub_str);
2739: END IF;
2740:
2739: END IF;
2740:
2741: ELSE
2742:
2743: IF (((lv_bom = MSC_UTIL.SYS_YES) OR (lv_wip = MSC_UTIL.SYS_YES)) AND NOT MSC_CL_COLLECTION.v_is_legacy_refresh) THEN
2744:
2745: /* Bug 3041176 - Delete Only Department resources in NetChange */
2746:
2747: p_instance_id := MSC_CL_COLLECTION.v_instance_id;
2753: ||' FROM '||p_table_name||' mdr, msc_trading_partners mtp'
2754: ||' WHERE mdr.SR_INSTANCE_ID= :p_instance_id'
2755: ||' AND mdr.PLAN_ID= -1 '
2756: ||' AND mdr.LINE_FLAG = 2'
2757: ||' AND mdr.organization_id '|| MSC_UTIL.v_in_org_str
2758: ||' AND mtp.sr_instance_id = :p_instance_id'
2759: ||' AND mtp.sr_tp_id = mdr.organization_id'
2760: ||' AND mtp.partner_type = 3'
2761: ||' AND mtp.organization_type = 1';
2770: lv_sql_stmt:= 'DELETE '||p_table_name||' mdr'
2771: ||' WHERE mdr.SR_INSTANCE_ID= :lv_instance_id'
2772: ||' AND mdr.PLAN_ID= -1 '
2773: ||' AND mdr.LINE_FLAG = 2'
2774: ||' AND mdr.organization_id '|| MSC_UTIL.v_in_org_str
2775: ||' AND mdr.organization_id = '
2776: ||' (SELECT mtp.sr_tp_id'
2777: ||' FROM msc_trading_partners mtp'
2778: ||' WHERE mtp.sr_instance_id = :lv_instance_id'
2790: lv_sql_stmt:= 'DELETE '||p_table_name||' mdr'
2791: ||' WHERE mdr.SR_INSTANCE_ID= :p_instance_id '
2792: ||' AND mdr.PLAN_ID= -1 '
2793: ||' AND mdr.LINE_FLAG = 2'
2794: ||' AND mdr.organization_id '|| MSC_UTIL.v_in_org_str
2795: ||' AND ROWNUM < :lv_pbs'
2796: ||' AND mdr.organization_id = '
2797: ||' (SELECT mtp.sr_tp_id'
2798: ||' FROM msc_trading_partners mtp'
2840:
2841: BEGIN
2842:
2843: IF (lv_res_incr_refresh) THEN
2844: IF c_rec.DELETED_FLAG= MSC_UTIL.SYS_YES THEN
2845:
2846: UPDATE MSC_DEPARTMENT_RESOURCES
2847: SET CAPACITY_UNITS= 0,
2848: MAX_RATE= 0,
3071: WHEN OTHERS THEN
3072:
3073: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
3074:
3075: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3076: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3077: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE');
3078: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DEPARTMENT_RESOURCES');
3079: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3075: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3076: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3077: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE');
3078: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DEPARTMENT_RESOURCES');
3079: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3080:
3081: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3082: RAISE;
3083:
3077: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE');
3078: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DEPARTMENT_RESOURCES');
3079: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3080:
3081: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3082: RAISE;
3083:
3084: ELSE
3085: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3081: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3082: RAISE;
3083:
3084: ELSE
3085: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3086:
3087: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3088: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3089: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE');
3083:
3084: ELSE
3085: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3086:
3087: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3088: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3089: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE');
3090: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DEPARTMENT_RESOURCES');
3091: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3087: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3088: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3089: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE');
3090: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DEPARTMENT_RESOURCES');
3091: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3092:
3093: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3094: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
3095: FND_MESSAGE.SET_TOKEN('VALUE',
3094: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
3095: FND_MESSAGE.SET_TOKEN('VALUE',
3096: MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
3097: MSC_CL_COLLECTION.v_instance_id));
3098: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3099:
3100: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3101: FND_MESSAGE.SET_TOKEN('COLUMN', 'DEPARTMENT_CODE');
3102: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.DEPARTMENT_CODE);
3099:
3100: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3101: FND_MESSAGE.SET_TOKEN('COLUMN', 'DEPARTMENT_CODE');
3102: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.DEPARTMENT_CODE);
3103: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3104:
3105: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3106: FND_MESSAGE.SET_TOKEN('COLUMN', 'RESOURCE_CODE');
3107: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.RESOURCE_CODE);
3104:
3105: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3106: FND_MESSAGE.SET_TOKEN('COLUMN', 'RESOURCE_CODE');
3107: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.RESOURCE_CODE);
3108: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3109:
3110: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3111: END IF;
3112:
3106: FND_MESSAGE.SET_TOKEN('COLUMN', 'RESOURCE_CODE');
3107: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.RESOURCE_CODE);
3108: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3109:
3110: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3111: END IF;
3112:
3113: END;
3114:
3174: WHEN OTHERS THEN
3175:
3176: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
3177:
3178: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3179: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3180: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE');
3181: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DEPT_RES_INSTANCES');
3182: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3178: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3179: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3180: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE');
3181: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DEPT_RES_INSTANCES');
3182: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3183:
3184: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3185: RAISE;
3186:
3180: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE');
3181: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DEPT_RES_INSTANCES');
3182: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3183:
3184: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3185: RAISE;
3186:
3187: ELSE
3188: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3184: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3185: RAISE;
3186:
3187: ELSE
3188: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3189:
3190: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3191: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3192: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE');
3186:
3187: ELSE
3188: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3189:
3190: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3191: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3192: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE');
3193: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DEPT_RES_INSTANCES');
3194: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3190: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3191: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3192: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE');
3193: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DEPT_RES_INSTANCES');
3194: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3195:
3196: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3197: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_ID');
3198: FND_MESSAGE.SET_TOKEN('VALUE',c_rec.ORGANIZATION_ID);
3195:
3196: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3197: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_ID');
3198: FND_MESSAGE.SET_TOKEN('VALUE',c_rec.ORGANIZATION_ID);
3199: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3200:
3201: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3202: FND_MESSAGE.SET_TOKEN('COLUMN', 'DEPARTMENT_ID');
3203: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.DEPARTMENT_ID);
3200:
3201: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3202: FND_MESSAGE.SET_TOKEN('COLUMN', 'DEPARTMENT_ID');
3203: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.DEPARTMENT_ID);
3204: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3205:
3206: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3207: FND_MESSAGE.SET_TOKEN('COLUMN', 'RESOURCE_ID');
3208: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.RESOURCE_ID);
3205:
3206: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3207: FND_MESSAGE.SET_TOKEN('COLUMN', 'RESOURCE_ID');
3208: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.RESOURCE_ID);
3209: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3210:
3211: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3212: FND_MESSAGE.SET_TOKEN('COLUMN', 'RES_INSTANCE_ID');
3213: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.RES_INSTANCE_ID);
3210:
3211: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3212: FND_MESSAGE.SET_TOKEN('COLUMN', 'RES_INSTANCE_ID');
3213: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.RES_INSTANCE_ID);
3214: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3215: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3216: END IF;
3217:
3218: END;
3211: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3212: FND_MESSAGE.SET_TOKEN('COLUMN', 'RES_INSTANCE_ID');
3213: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.RES_INSTANCE_ID);
3214: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3215: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3216: END IF;
3217:
3218: END;
3219:
3283: WHEN OTHERS THEN
3284:
3285: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
3286:
3287: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3288: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3289: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE');
3290: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SIMULATION_SETS');
3291: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3287: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3288: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3289: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE');
3290: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SIMULATION_SETS');
3291: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3292:
3293: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3294: RAISE;
3295:
3289: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE');
3290: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SIMULATION_SETS');
3291: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3292:
3293: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3294: RAISE;
3295:
3296: ELSE
3297: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3293: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3294: RAISE;
3295:
3296: ELSE
3297: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3298:
3299: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3300: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3301: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE');
3295:
3296: ELSE
3297: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3298:
3299: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3300: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3301: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE');
3302: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SIMULATION_SETS');
3303: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3299: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3300: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3301: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE');
3302: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SIMULATION_SETS');
3303: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3304:
3305: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3306: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
3307: FND_MESSAGE.SET_TOKEN('VALUE',
3306: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
3307: FND_MESSAGE.SET_TOKEN('VALUE',
3308: MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
3309: MSC_CL_COLLECTION.v_instance_id));
3310: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3311:
3312: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3313: FND_MESSAGE.SET_TOKEN('COLUMN', 'SIMULATION_SET');
3314: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SIMULATION_SET);
3311:
3312: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3313: FND_MESSAGE.SET_TOKEN('COLUMN', 'SIMULATION_SET');
3314: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SIMULATION_SET);
3315: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3316:
3317: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3318: END IF;
3319:
3313: FND_MESSAGE.SET_TOKEN('COLUMN', 'SIMULATION_SET');
3314: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SIMULATION_SET);
3315: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3316:
3317: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3318: END IF;
3319:
3320: END;
3321:
3368: WHEN OTHERS THEN
3369:
3370: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
3371:
3372: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3373: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3374: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE');
3375: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_GROUPS');
3376: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3372: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3373: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3374: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE');
3375: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_GROUPS');
3376: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3377:
3378: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3379: RAISE;
3380:
3374: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE');
3375: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_GROUPS');
3376: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3377:
3378: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3379: RAISE;
3380:
3381: ELSE
3382: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3378: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3379: RAISE;
3380:
3381: ELSE
3382: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3383:
3384: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3385: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3386: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE');
3380:
3381: ELSE
3382: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3383:
3384: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3385: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3386: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE');
3387: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_GROUPS');
3388: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3384: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3385: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3386: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE');
3387: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_GROUPS');
3388: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3389:
3390: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3391: FND_MESSAGE.SET_TOKEN('COLUMN', 'GROUP_CODE');
3392: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.GROUP_CODE);
3389:
3390: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3391: FND_MESSAGE.SET_TOKEN('COLUMN', 'GROUP_CODE');
3392: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.GROUP_CODE);
3393: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3394:
3395: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3396: END IF;
3397:
3391: FND_MESSAGE.SET_TOKEN('COLUMN', 'GROUP_CODE');
3392: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.GROUP_CODE);
3393: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3394:
3395: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3396: END IF;
3397:
3398: END;
3399: