[Home] [Help]
1: PACKAGE BODY MSC_CL_SETUP_ODS_LOAD AS -- specification
2: /* $Header: MSCLSTPB.pls 120.9.12010000.3 2009/01/15 06:32:56 lsindhur ship $ */
3: -- SYS_YES Number:= MSC_UTIL.SYS_YES ;
4: -- SYS_NO Number:= MSC_CL_COLLECTION.SYS_NO ;
5: -- G_SUCCESS NUMBER := MSC_CL_COLLECTION.G_SUCCESS;
6: -- G_WARNING NUMBER := MSC_CL_COLLECTION.G_WARNING;
7: -- G_ERROR NUMBER := MSC_CL_COLLECTION.G_ERROR;
7: -- G_ERROR NUMBER := MSC_CL_COLLECTION.G_ERROR;
8: -- G_COLLECTION_PROGRAM NUMBER := MSC_CL_COLLECTION.G_COLLECTION_PROGRAM;
9: -- SYS_TGT NUMBER:=MSC_CL_COLLECTION.SYS_TGT;
10: -- G_INS_OTHER NUMBER:= MSC_CL_COLLECTION.G_INS_OTHER;
11: -- NULL_VALUE NUMBER:=MSC_UTIL.NULL_VALUE;
12: -- NULL_DATE DATE:=MSC_CL_COLLECTION.NULL_DATE;
13: -- NULL_DBLINK VARCHAR2(1):=MSC_UTIL.NULL_DBLINK;
14: -- G_MY_COMPANY_ID NUMBER := MSC_CL_COLLECTION.G_MY_COMPANY_ID;
15: -- G_APPS110 NUMBER := MSC_CL_COLLECTION.G_APPS110;
9: -- SYS_TGT NUMBER:=MSC_CL_COLLECTION.SYS_TGT;
10: -- G_INS_OTHER NUMBER:= MSC_CL_COLLECTION.G_INS_OTHER;
11: -- NULL_VALUE NUMBER:=MSC_UTIL.NULL_VALUE;
12: -- NULL_DATE DATE:=MSC_CL_COLLECTION.NULL_DATE;
13: -- NULL_DBLINK VARCHAR2(1):=MSC_UTIL.NULL_DBLINK;
14: -- G_MY_COMPANY_ID NUMBER := MSC_CL_COLLECTION.G_MY_COMPANY_ID;
15: -- G_APPS110 NUMBER := MSC_CL_COLLECTION.G_APPS110;
16: -- G_APPS115 NUMBER :=MSC_CL_COLLECTION.G_APPS115;
17: -- G_APPS107 NUMBER :=MSC_CL_COLLECTION.G_APPS107;
26: begin
27:
28: lv_task_start_time := SYSDATE;
29:
30: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
31: lv_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
32:
33: ELSE
34: lv_tbl:= 'MSC_SUPPLIES';
54:
55: FND_MESSAGE.SET_NAME('MSC', 'MSC_ELAPSED_TIME');
56: FND_MESSAGE.SET_TOKEN('ELAPSED_TIME',
57: TO_CHAR(CEIL((SYSDATE- lv_task_start_time)*14400.0)/10));
58: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' '||FND_MESSAGE.GET);
59:
60: RETURN TRUE;
61:
62: EXCEPTION
60: RETURN TRUE;
61:
62: EXCEPTION
63: WHEN OTHERS THEN
64: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error executing MSC_CL_DEMAND_ODS_LOAD.LINK_PARENT_SALES_ORDERS_MDS......');
65: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
66:
67: RETURN FALSE;
68:
61:
62: EXCEPTION
63: WHEN OTHERS THEN
64: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error executing MSC_CL_DEMAND_ODS_LOAD.LINK_PARENT_SALES_ORDERS_MDS......');
65: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
66:
67: RETURN FALSE;
68:
69: END LINK_SUPPLY_TOP_LINK_ID;
79: CLEANSED_FLAG NUMBER;
80:
81: BEGIN
82:
83: SELECT NVL( CLEANSED_FLAG, MSC_UTIL.SYS_NO)
84: INTO CLEANSED_FLAG
85: FROM MSC_APPS_INSTANCES mai
86: WHERE mai.INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
87:
84: INTO CLEANSED_FLAG
85: FROM MSC_APPS_INSTANCES mai
86: WHERE mai.INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
87:
88: IF CLEANSED_FLAG= MSC_UTIL.SYS_NO THEN
89:
90: MSC_CL_CLEANSE.CLEANSE( ERRBUF,
91: RETCODE,
92: MSC_CL_COLLECTION.v_instance_id);
90: MSC_CL_CLEANSE.CLEANSE( ERRBUF,
91: RETCODE,
92: MSC_CL_COLLECTION.v_instance_id);
93:
94: IF RETCODE= MSC_UTIL.G_SUCCESS OR RETCODE= MSC_UTIL.G_WARNING THEN
95:
96: UPDATE MSC_APPS_INSTANCES mai
97: SET mai.CLEANSED_FLAG= MSC_UTIL.SYS_YES,
98: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
93:
94: IF RETCODE= MSC_UTIL.G_SUCCESS OR RETCODE= MSC_UTIL.G_WARNING THEN
95:
96: UPDATE MSC_APPS_INSTANCES mai
97: SET mai.CLEANSED_FLAG= MSC_UTIL.SYS_YES,
98: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
99: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
100: REQUEST_ID= FND_GLOBAL.CONC_REQUEST_ID
101: WHERE mai.Instance_ID= MSC_CL_COLLECTION.v_instance_id;
108:
109: MSC_CL_COLLECTION.log_message( MSC_CL_COLLECTION.G_COLLECTION_PROGRAM,
110: MSC_CL_COLLECTION.v_last_collection_id,
111: MSC_CL_COLLECTION.v_current_date,
112: MSC_UTIL.G_ERROR,
113: 'MSC_CL_COLLECTION.CLEANSE_DATA',
114: 'UNKNOWN',
115: TO_CHAR( MSC_CL_COLLECTION.v_instance_id),
116: ERRBUF);
120: END IF;
121:
122: END IF;
123:
124: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'CLEANSE DATA... OK!');
125:
126: RETURN TRUE;
127:
128: EXCEPTION
128: EXCEPTION
129:
130: WHEN OTHERS THEN
131:
132: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
133:
134: RAISE;
135:
136: -- RETURN FALSE;
153: /* for bug: 2605884, added this piece of code to set the so_tbl_status to NO */
154:
155: IF (MSC_CL_COLLECTION.v_inv_ctp_val = 4) THEN -- PDS ATP , set SO_TBL_STATUS=2 , no matter type of collections
156: UPDATE MSC_APPS_INSTANCES
157: SET so_tbl_status= MSC_UTIL.SYS_NO
158: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
159: commit;
160: ELSIF (MSC_CL_COLLECTION.v_inv_ctp_val = 5) THEN -- ODS ATP
161: IF MSC_CL_COLLECTION.v_is_complete_refresh THEN
160: ELSIF (MSC_CL_COLLECTION.v_inv_ctp_val = 5) THEN -- ODS ATP
161: IF MSC_CL_COLLECTION.v_is_complete_refresh THEN
162: IF (NOT MSC_CL_COLLECTION.v_is_so_complete_refresh) THEN -- In complete refresh, if the SO flag is no
163: UPDATE MSC_APPS_INSTANCES
164: SET so_tbl_status= MSC_UTIL.SYS_NO
165: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
166: commit;
167: END IF;
168: ELSIF MSC_CL_COLLECTION.v_is_partial_refresh THEN
165: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
166: commit;
167: END IF;
168: ELSIF MSC_CL_COLLECTION.v_is_partial_refresh THEN
169: IF (MSC_CL_COLLECTION.v_coll_prec.sales_order_flag <> MSC_UTIL.SYS_YES) THEN -- in partial refresh, if SO is not collected
170: UPDATE MSC_APPS_INSTANCES
171: SET so_tbl_status= MSC_UTIL.SYS_NO
172: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
173: commit;
167: END IF;
168: ELSIF MSC_CL_COLLECTION.v_is_partial_refresh THEN
169: IF (MSC_CL_COLLECTION.v_coll_prec.sales_order_flag <> MSC_UTIL.SYS_YES) THEN -- in partial refresh, if SO is not collected
170: UPDATE MSC_APPS_INSTANCES
171: SET so_tbl_status= MSC_UTIL.SYS_NO
172: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
173: commit;
174: END IF;
175: ELSIF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
173: commit;
174: END IF;
175: ELSIF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
176: UPDATE MSC_APPS_INSTANCES
177: SET so_tbl_status= MSC_UTIL.SYS_NO
178: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
179: commit;
180: ELSIF MSC_CL_COLLECTION.v_is_cont_refresh THEN
181: IF ( MSC_CL_COLLECTION.v_coll_prec.sales_order_flag <> MSC_UTIL.SYS_YES ) THEN
177: SET so_tbl_status= MSC_UTIL.SYS_NO
178: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
179: commit;
180: ELSIF MSC_CL_COLLECTION.v_is_cont_refresh THEN
181: IF ( MSC_CL_COLLECTION.v_coll_prec.sales_order_flag <> MSC_UTIL.SYS_YES ) THEN
182: UPDATE MSC_APPS_INSTANCES
183: SET so_tbl_status= MSC_UTIL.SYS_NO
184: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
185: commit;
179: commit;
180: ELSIF MSC_CL_COLLECTION.v_is_cont_refresh THEN
181: IF ( MSC_CL_COLLECTION.v_coll_prec.sales_order_flag <> MSC_UTIL.SYS_YES ) THEN
182: UPDATE MSC_APPS_INSTANCES
183: SET so_tbl_status= MSC_UTIL.SYS_NO
184: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
185: commit;
186: ELSE
187: IF ( MSC_CL_COLLECTION.v_coll_prec.so_sn_flag <> MSC_UTIL.SYS_TGT ) THEN
183: SET so_tbl_status= MSC_UTIL.SYS_NO
184: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
185: commit;
186: ELSE
187: IF ( MSC_CL_COLLECTION.v_coll_prec.so_sn_flag <> MSC_UTIL.SYS_TGT ) THEN
188: UPDATE MSC_APPS_INSTANCES
189: SET so_tbl_status= MSC_UTIL.SYS_NO
190: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
191: commit;
185: commit;
186: ELSE
187: IF ( MSC_CL_COLLECTION.v_coll_prec.so_sn_flag <> MSC_UTIL.SYS_TGT ) THEN
188: UPDATE MSC_APPS_INSTANCES
189: SET so_tbl_status= MSC_UTIL.SYS_NO
190: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
191: commit;
192: END IF;
193: END IF;
207:
208: IF lvs_request_id(1) = 0 THEN
209: FND_MESSAGE.SET_NAME('MSC', 'MSC_PROGRAM_LAUNCH_FAIL');
210: FND_MESSAGE.SET_TOKEN('PROGRAM_NAME', 'MSC_CL_COLLECTION.GENERATE_ITEM_KEYS');
211: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
212: RETURN FALSE;
213: ELSE
214: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'Submitted request for Items and Category Sets Key Generation');
215: END IF;
210: FND_MESSAGE.SET_TOKEN('PROGRAM_NAME', 'MSC_CL_COLLECTION.GENERATE_ITEM_KEYS');
211: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
212: RETURN FALSE;
213: ELSE
214: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'Submitted request for Items and Category Sets Key Generation');
215: END IF;
216:
217: --Submit request for Trading Parteners Key Generation
218: lvs_request_id(2) := FND_REQUEST.SUBMIT_REQUEST(
227:
228: IF lvs_request_id(2) = 0 THEN
229: FND_MESSAGE.SET_NAME('MSC', 'MSC_PROGRAM_LAUNCH_FAIL');
230: FND_MESSAGE.SET_TOKEN('PROGRAM_NAME', 'MSC_CL_COLLECTION.GENERATE_TRADING_PARTNER_KEYS');
231: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
232: RETURN FALSE;
233: ELSE
234: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'Submitted request for Trading Parteners Key Generation');
235: END IF;
230: FND_MESSAGE.SET_TOKEN('PROGRAM_NAME', 'MSC_CL_COLLECTION.GENERATE_TRADING_PARTNER_KEYS');
231: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
232: RETURN FALSE;
233: ELSE
234: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'Submitted request for Trading Parteners Key Generation');
235: END IF;
236:
237: FOR j IN 1..lvs_request_id.COUNT LOOP
238: mrp_cl_refresh_snapshot.wait_for_request(lvs_request_id(j), 30, lv_out);
239:
240: IF lv_out = 2 THEN
241: FND_MESSAGE.SET_NAME('MSC', 'MSC_PROGRAM_RUN_FAIL');
242: FND_MESSAGE.SET_TOKEN('REQUEST_ID', lvs_request_id(j));
243: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
244: RETURN FALSE;
245: ELSE
246: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'Request ' || lvs_request_id(j) || ' successful');
247: END IF;
242: FND_MESSAGE.SET_TOKEN('REQUEST_ID', lvs_request_id(j));
243: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
244: RETURN FALSE;
245: ELSE
246: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'Request ' || lvs_request_id(j) || ' successful');
247: END IF;
248:
249: END LOOP;
250:
252: /* for bug: 2605884, added this piece of code to set the so_tbl_status to YES after the Key transformations */
253:
254: IF (MSC_CL_COLLECTION.v_inv_ctp_val = 4) THEN -- PDS ATP , set SO_TBL_STATUS=1
255: UPDATE MSC_APPS_INSTANCES
256: SET so_tbl_status= MSC_UTIL.SYS_YES
257: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
258: commit;
259: ELSIF (MSC_CL_COLLECTION.v_inv_ctp_val = 5) THEN -- ODS ATP
260: IF MSC_CL_COLLECTION.v_is_complete_refresh THEN
259: ELSIF (MSC_CL_COLLECTION.v_inv_ctp_val = 5) THEN -- ODS ATP
260: IF MSC_CL_COLLECTION.v_is_complete_refresh THEN
261: IF (NOT MSC_CL_COLLECTION.v_is_so_complete_refresh) THEN -- In complete refresh, if the SO flag is no
262: UPDATE MSC_APPS_INSTANCES
263: SET so_tbl_status= MSC_UTIL.SYS_YES
264: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
265: commit;
266: END IF;
267: ELSIF MSC_CL_COLLECTION.v_is_partial_refresh THEN
264: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
265: commit;
266: END IF;
267: ELSIF MSC_CL_COLLECTION.v_is_partial_refresh THEN
268: IF (MSC_CL_COLLECTION.v_coll_prec.sales_order_flag <> MSC_UTIL.SYS_YES) THEN -- in partial refresh, if SO is not collected
269: UPDATE MSC_APPS_INSTANCES
270: SET so_tbl_status= MSC_UTIL.SYS_YES
271: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
272: commit;
266: END IF;
267: ELSIF MSC_CL_COLLECTION.v_is_partial_refresh THEN
268: IF (MSC_CL_COLLECTION.v_coll_prec.sales_order_flag <> MSC_UTIL.SYS_YES) THEN -- in partial refresh, if SO is not collected
269: UPDATE MSC_APPS_INSTANCES
270: SET so_tbl_status= MSC_UTIL.SYS_YES
271: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
272: commit;
273: END IF;
274: ELSIF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
272: commit;
273: END IF;
274: ELSIF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
275: UPDATE MSC_APPS_INSTANCES
276: SET so_tbl_status= MSC_UTIL.SYS_YES
277: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
278: commit;
279: ELSIF MSC_CL_COLLECTION.v_is_cont_refresh THEN
280: IF ( MSC_CL_COLLECTION.v_coll_prec.sales_order_flag <> MSC_UTIL.SYS_YES ) THEN
276: SET so_tbl_status= MSC_UTIL.SYS_YES
277: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
278: commit;
279: ELSIF MSC_CL_COLLECTION.v_is_cont_refresh THEN
280: IF ( MSC_CL_COLLECTION.v_coll_prec.sales_order_flag <> MSC_UTIL.SYS_YES ) THEN
281: UPDATE MSC_APPS_INSTANCES
282: SET so_tbl_status= MSC_UTIL.SYS_YES
283: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
284: commit;
278: commit;
279: ELSIF MSC_CL_COLLECTION.v_is_cont_refresh THEN
280: IF ( MSC_CL_COLLECTION.v_coll_prec.sales_order_flag <> MSC_UTIL.SYS_YES ) THEN
281: UPDATE MSC_APPS_INSTANCES
282: SET so_tbl_status= MSC_UTIL.SYS_YES
283: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
284: commit;
285: ELSE
286: IF ( MSC_CL_COLLECTION.v_coll_prec.so_sn_flag <> MSC_UTIL.SYS_TGT ) THEN
282: SET so_tbl_status= MSC_UTIL.SYS_YES
283: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
284: commit;
285: ELSE
286: IF ( MSC_CL_COLLECTION.v_coll_prec.so_sn_flag <> MSC_UTIL.SYS_TGT ) THEN
287: UPDATE MSC_APPS_INSTANCES
288: SET so_tbl_status= MSC_UTIL.SYS_YES
289: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
290: commit;
284: commit;
285: ELSE
286: IF ( MSC_CL_COLLECTION.v_coll_prec.so_sn_flag <> MSC_UTIL.SYS_TGT ) THEN
287: UPDATE MSC_APPS_INSTANCES
288: SET so_tbl_status= MSC_UTIL.SYS_YES
289: WHERE instance_id= MSC_CL_COLLECTION.v_instance_id;
290: commit;
291: END IF;
292: END IF;
299: EXCEPTION
300:
301: WHEN OTHERS THEN
302:
303: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
304:
305: RETURN FALSE;
306:
307: END TRANSFORM_KEYS;
467: -- Calendar Dates --
468:
469: if (MSC_CL_COLLECTION.v_is_partial_refresh or MSC_CL_COLLECTION.v_is_complete_refresh ) THEN
470: UPDATE MSC_CALENDAR_DATES
471: SET DELETED_FLAG= MSC_UTIL.SYS_YES,
472: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
473: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
474: WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
475:
573: CALENDAR_START_DATE= lb_CALENDAR_START_DATE(j),
574: CALENDAR_END_DATE= lb_CALENDAR_END_DATE(j),
575: DESCRIPTION= lb_DESCRIPTION(j),
576: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
577: Deleted_Flag= MSC_UTIL.SYS_NO,
578: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
579: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
580: WHERE CALENDAR_DATE= lb_CALENDAR_DATE(j)
581: AND CALENDAR_CODE= lb_CALENDAR_CODE(j)
599: lb_DESCRIPTION(j),
600: lb_EXCEPTION_SET_ID(j),
601: lb_SR_INSTANCE_ID(j),
602: MSC_CL_COLLECTION.v_last_collection_id,
603: MSC_UTIL.SYS_NO,
604: MSC_CL_COLLECTION.v_current_date,
605: MSC_CL_COLLECTION.v_current_user,
606: MSC_CL_COLLECTION.v_current_date,
607: MSC_CL_COLLECTION.v_current_user ;
614:
615:
616: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
617:
618: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
619: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
620: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
621: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CALENDAR_DATES');
622: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
618: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
619: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
620: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
621: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CALENDAR_DATES');
622: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
623:
624: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
625: RAISE;
626:
620: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
621: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CALENDAR_DATES');
622: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
623:
624: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
625: RAISE;
626:
627: ELSE
628: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
624: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
625: RAISE;
626:
627: ELSE
628: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
629:
630: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
631: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
632: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
626:
627: ELSE
628: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
629:
630: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
631: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
632: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
633: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CALENDAR_DATES');
634: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
630: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
631: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
632: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
633: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CALENDAR_DATES');
634: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
635:
636: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
637: FND_MESSAGE.SET_TOKEN('COLUMN', 'EXCEPTION_SET_ID');
638: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( lb_EXCEPTION_SET_ID(j)));
635:
636: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
637: FND_MESSAGE.SET_TOKEN('COLUMN', 'EXCEPTION_SET_ID');
638: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( lb_EXCEPTION_SET_ID(j)));
639: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
640:
641: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
642: FND_MESSAGE.SET_TOKEN('COLUMN', 'CALENDAR_DATE');
643: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(lb_CALENDAR_DATE(j)));
640:
641: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
642: FND_MESSAGE.SET_TOKEN('COLUMN', 'CALENDAR_DATE');
643: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(lb_CALENDAR_DATE(j)));
644: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
645:
646: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
647: FND_MESSAGE.SET_TOKEN('COLUMN', 'CALENDAR_CODE');
648: FND_MESSAGE.SET_TOKEN('VALUE', lb_CALENDAR_CODE(j));
645:
646: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
647: FND_MESSAGE.SET_TOKEN('COLUMN', 'CALENDAR_CODE');
648: FND_MESSAGE.SET_TOKEN('VALUE', lb_CALENDAR_CODE(j));
649: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
650:
651: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
652: END IF;
653:
647: FND_MESSAGE.SET_TOKEN('COLUMN', 'CALENDAR_CODE');
648: FND_MESSAGE.SET_TOKEN('VALUE', lb_CALENDAR_CODE(j));
649: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
650:
651: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
652: END IF;
653:
654: END;
655:
660: COMMIT;
661:
662:
663: DELETE MSC_CALENDAR_DATES
664: WHERE DELETED_FLAG= MSC_UTIL.SYS_YES
665: AND SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
666:
667: COMMIT;
668:
680:
681: FOR c_rec IN c5 LOOP
682:
683: BEGIN
684: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'cal code='|| c_rec.CALENDAR_CODE);
685: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh OR MSC_CL_COLLECTION.v_is_cont_refresh) THEN
686:
687: IF MSC_CL_COLLECTION.v_instance_type <> MSC_UTIL.G_INS_OTHER THEN
688:
683: BEGIN
684: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'cal code='|| c_rec.CALENDAR_CODE);
685: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh OR MSC_CL_COLLECTION.v_is_cont_refresh) THEN
686:
687: IF MSC_CL_COLLECTION.v_instance_type <> MSC_UTIL.G_INS_OTHER THEN
688:
689: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'cal code='|| c_rec.CALENDAR_CODE);
690:
691: INSERT INTO MSC_CALENDARS
685: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh OR MSC_CL_COLLECTION.v_is_cont_refresh) THEN
686:
687: IF MSC_CL_COLLECTION.v_instance_type <> MSC_UTIL.G_INS_OTHER THEN
688:
689: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'cal code='|| c_rec.CALENDAR_CODE);
690:
691: INSERT INTO MSC_CALENDARS
692: ( CALENDAR_CODE,
693: DESCRIPTION,
718: WHEN OTHERS THEN
719:
720: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
721:
722: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
723: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
724: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
725: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CALENDARS');
726: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
722: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
723: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
724: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
725: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CALENDARS');
726: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
727: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
728: RAISE;
729:
730: ELSE
723: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
724: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
725: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CALENDARS');
726: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
727: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
728: RAISE;
729:
730: ELSE
731: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
727: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
728: RAISE;
729:
730: ELSE
731: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
732:
733: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
734: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
735: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
729:
730: ELSE
731: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
732:
733: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
734: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
735: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
736: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CALENDARS');
737: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
733: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
734: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
735: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
736: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CALENDARS');
737: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
738:
739: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
740: FND_MESSAGE.SET_TOKEN('COLUMN', 'CALENDAR_CODE');
741: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CALENDAR_CODE);
738:
739: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
740: FND_MESSAGE.SET_TOKEN('COLUMN', 'CALENDAR_CODE');
741: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CALENDAR_CODE);
742: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
743: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
744: END IF;
745: END;
746: END LOOP;
739: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
740: FND_MESSAGE.SET_TOKEN('COLUMN', 'CALENDAR_CODE');
741: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CALENDAR_CODE);
742: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
743: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
744: END IF;
745: END;
746: END LOOP;
747:
761: WHEN OTHERS THEN
762:
763: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
764:
765: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
766: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
767: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
768: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CALENDARS');
769: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
765: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
766: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
767: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
768: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CALENDARS');
769: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
770: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
771: RAISE;
772:
773: ELSE
766: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
767: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
768: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CALENDARS');
769: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
770: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
771: RAISE;
772:
773: ELSE
774: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
770: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
771: RAISE;
772:
773: ELSE
774: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
775:
776: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
777: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
778: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
772:
773: ELSE
774: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
775:
776: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
777: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
778: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
779: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CALENDARS');
780: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
776: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
777: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
778: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
779: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CALENDARS');
780: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
781: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
782: FND_MESSAGE.SET_TOKEN('COLUMN', 'CALENDAR_CODE');
783: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CALENDAR_CODE);
784: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
780: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
781: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
782: FND_MESSAGE.SET_TOKEN('COLUMN', 'CALENDAR_CODE');
783: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CALENDAR_CODE);
784: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
785: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
786: END IF;
787: END;
788: END LOOP;
781: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
782: FND_MESSAGE.SET_TOKEN('COLUMN', 'CALENDAR_CODE');
783: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CALENDAR_CODE);
784: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
785: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
786: END IF;
787: END;
788: END LOOP;
789:
842: WHEN OTHERS THEN
843:
844: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
845:
846: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
847: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
848: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
849: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CALENDAR_ASSIGNMENTS');
850: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
846: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
847: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
848: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
849: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CALENDAR_ASSIGNMENTS');
850: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
851: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
852: RAISE;
853:
854: ELSE
847: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
848: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
849: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CALENDAR_ASSIGNMENTS');
850: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
851: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
852: RAISE;
853:
854: ELSE
855: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
851: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
852: RAISE;
853:
854: ELSE
855: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
856:
857: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
858: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
859: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
853:
854: ELSE
855: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
856:
857: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
858: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
859: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
860: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CALENDAR_ASSIGNMENTS');
861: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
857: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
858: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
859: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
860: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CALENDAR_ASSIGNMENTS');
861: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
862: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
863: FND_MESSAGE.SET_TOKEN('COLUMN', 'CALENDAR_CODE');
864: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CALENDAR_CODE);
865: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
861: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
862: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
863: FND_MESSAGE.SET_TOKEN('COLUMN', 'CALENDAR_CODE');
864: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CALENDAR_CODE);
865: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
866: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
867: END IF;
868: END;
869: END LOOP;
862: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
863: FND_MESSAGE.SET_TOKEN('COLUMN', 'CALENDAR_CODE');
864: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CALENDAR_CODE);
865: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
866: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
867: END IF;
868: END;
869: END LOOP;
870:
941: WHEN OTHERS THEN
942:
943: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
944:
945: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
946: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
947: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
948: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_PERIOD_START_DATES');
949: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
945: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
946: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
947: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
948: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_PERIOD_START_DATES');
949: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
950:
951: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
952: RAISE;
953:
947: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
948: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_PERIOD_START_DATES');
949: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
950:
951: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
952: RAISE;
953:
954: ELSE
955: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
951: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
952: RAISE;
953:
954: ELSE
955: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
956:
957: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
958: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
959: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
953:
954: ELSE
955: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
956:
957: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
958: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
959: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
960: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_PERIOD_START_DATES');
961: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
957: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
958: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
959: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
960: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_PERIOD_START_DATES');
961: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
962:
963: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
964: FND_MESSAGE.SET_TOKEN('COLUMN', 'EXCEPTION_SET_ID');
965: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.EXCEPTION_SET_ID));
962:
963: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
964: FND_MESSAGE.SET_TOKEN('COLUMN', 'EXCEPTION_SET_ID');
965: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.EXCEPTION_SET_ID));
966: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
967:
968: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
969: FND_MESSAGE.SET_TOKEN('COLUMN', 'PERIOD_START_DATE');
970: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.PERIOD_START_DATE));
967:
968: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
969: FND_MESSAGE.SET_TOKEN('COLUMN', 'PERIOD_START_DATE');
970: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.PERIOD_START_DATE));
971: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
972:
973: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
974: FND_MESSAGE.SET_TOKEN('COLUMN', 'CALENDAR_CODE');
975: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CALENDAR_CODE);
972:
973: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
974: FND_MESSAGE.SET_TOKEN('COLUMN', 'CALENDAR_CODE');
975: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CALENDAR_CODE);
976: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
977:
978: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
979:
980: END IF;
974: FND_MESSAGE.SET_TOKEN('COLUMN', 'CALENDAR_CODE');
975: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CALENDAR_CODE);
976: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
977:
978: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
979:
980: END IF;
981: END;
982:
1040: EXCEPTION
1041: WHEN OTHERS THEN
1042:
1043: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
1044: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1045: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1046: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
1047: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CAL_YEAR_START_DATES');
1048: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1044: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1045: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1046: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
1047: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CAL_YEAR_START_DATES');
1048: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1049:
1050: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1051: RAISE;
1052:
1046: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
1047: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CAL_YEAR_START_DATES');
1048: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1049:
1050: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1051: RAISE;
1052:
1053: ELSE
1054: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1050: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1051: RAISE;
1052:
1053: ELSE
1054: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1055:
1056: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1057: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1058: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
1052:
1053: ELSE
1054: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1055:
1056: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1057: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1058: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
1059: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CAL_YEAR_START_DATES');
1060: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1056: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1057: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1058: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
1059: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CAL_YEAR_START_DATES');
1060: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1061:
1062: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1063: FND_MESSAGE.SET_TOKEN('COLUMN', 'EXCEPTION_SET_ID');
1064: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.EXCEPTION_SET_ID));
1061:
1062: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1063: FND_MESSAGE.SET_TOKEN('COLUMN', 'EXCEPTION_SET_ID');
1064: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.EXCEPTION_SET_ID));
1065: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1066:
1067: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1068: FND_MESSAGE.SET_TOKEN('COLUMN', 'YEAR_START_DATE');
1069: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.YEAR_START_DATE));
1066:
1067: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1068: FND_MESSAGE.SET_TOKEN('COLUMN', 'YEAR_START_DATE');
1069: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.YEAR_START_DATE));
1070: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1071:
1072: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1073: FND_MESSAGE.SET_TOKEN('COLUMN', 'CALENDAR_CODE');
1074: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CALENDAR_CODE);
1071:
1072: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1073: FND_MESSAGE.SET_TOKEN('COLUMN', 'CALENDAR_CODE');
1074: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CALENDAR_CODE);
1075: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1076:
1077: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1078: END IF;
1079:
1073: FND_MESSAGE.SET_TOKEN('COLUMN', 'CALENDAR_CODE');
1074: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CALENDAR_CODE);
1075: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1076:
1077: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1078: END IF;
1079:
1080: END;
1081:
1147: EXCEPTION
1148: WHEN OTHERS THEN
1149:
1150: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
1151: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1152: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1153: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
1154: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CAL_WEEK_START_DATES');
1155: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1151: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1152: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1153: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
1154: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CAL_WEEK_START_DATES');
1155: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1156:
1157: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1158: RAISE;
1159:
1153: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
1154: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CAL_WEEK_START_DATES');
1155: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1156:
1157: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1158: RAISE;
1159:
1160: ELSE
1161: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1157: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1158: RAISE;
1159:
1160: ELSE
1161: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1162:
1163: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1164: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1165: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
1159:
1160: ELSE
1161: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1162:
1163: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1164: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1165: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
1166: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CAL_WEEK_START_DATES');
1167: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1163: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1164: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1165: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
1166: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CAL_WEEK_START_DATES');
1167: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1168:
1169: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1170: FND_MESSAGE.SET_TOKEN('COLUMN', 'EXCEPTION_SET_ID');
1171: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.EXCEPTION_SET_ID));
1168:
1169: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1170: FND_MESSAGE.SET_TOKEN('COLUMN', 'EXCEPTION_SET_ID');
1171: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.EXCEPTION_SET_ID));
1172: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1173:
1174: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1175: FND_MESSAGE.SET_TOKEN('COLUMN', 'WEEK_START_DATE');
1176: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.WEEK_START_DATE));
1173:
1174: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1175: FND_MESSAGE.SET_TOKEN('COLUMN', 'WEEK_START_DATE');
1176: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.WEEK_START_DATE));
1177: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1178:
1179: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1180: FND_MESSAGE.SET_TOKEN('COLUMN', 'CALENDAR_CODE');
1181: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CALENDAR_CODE);
1178:
1179: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1180: FND_MESSAGE.SET_TOKEN('COLUMN', 'CALENDAR_CODE');
1181: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CALENDAR_CODE);
1182: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1183:
1184: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1185: END IF;
1186: END;
1180: FND_MESSAGE.SET_TOKEN('COLUMN', 'CALENDAR_CODE');
1181: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CALENDAR_CODE);
1182: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1183:
1184: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1185: END IF;
1186: END;
1187:
1188: END LOOP;
1286: ex_calc_res_avail EXCEPTION;
1287:
1288: BEGIN
1289:
1290: if ((MSC_CL_COLLECTION.v_is_partial_refresh AND MSC_CL_COLLECTION.v_coll_prec.calendar_flag = MSC_UTIL.SYS_YES) OR
1291: MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_incremental_refresh) then
1292:
1293: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
1294:
1363: EXCEPTION
1364: WHEN OTHERS THEN
1365:
1366: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
1367: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1368: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1369: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1370: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_SHIFTS');
1371: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1367: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1368: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1369: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1370: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_SHIFTS');
1371: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1372:
1373: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1374: RAISE;
1375:
1369: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1370: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_SHIFTS');
1371: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1372:
1373: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1374: RAISE;
1375:
1376: ELSE
1377: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1373: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1374: RAISE;
1375:
1376: ELSE
1377: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1378:
1379: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1380: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1381: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1375:
1376: ELSE
1377: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1378:
1379: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1380: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1381: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1382: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_SHIFTS');
1383: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1379: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1380: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1381: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1382: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_SHIFTS');
1383: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1384:
1385: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1386: FND_MESSAGE.SET_TOKEN('COLUMN', 'DEPARTMENT_ID');
1387: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.DEPARTMENT_ID));
1384:
1385: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1386: FND_MESSAGE.SET_TOKEN('COLUMN', 'DEPARTMENT_ID');
1387: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.DEPARTMENT_ID));
1388: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1389:
1390: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1391: FND_MESSAGE.SET_TOKEN('COLUMN', 'RESOURCE_ID');
1392: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.RESOURCE_ID));
1389:
1390: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1391: FND_MESSAGE.SET_TOKEN('COLUMN', 'RESOURCE_ID');
1392: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.RESOURCE_ID));
1393: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1394:
1395: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1396: FND_MESSAGE.SET_TOKEN('COLUMN', 'SHIFT_NUM');
1397: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SHIFT_NUM));
1394:
1395: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1396: FND_MESSAGE.SET_TOKEN('COLUMN', 'SHIFT_NUM');
1397: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SHIFT_NUM));
1398: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1399:
1400: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1401: END IF;
1402:
1396: FND_MESSAGE.SET_TOKEN('COLUMN', 'SHIFT_NUM');
1397: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SHIFT_NUM));
1398: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1399:
1400: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1401: END IF;
1402:
1403: END;
1404:
1468:
1469: WHEN OTHERS THEN
1470:
1471: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
1472: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1473: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1474: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1475: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CALENDAR_SHIFTS');
1476: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1472: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1473: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1474: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1475: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CALENDAR_SHIFTS');
1476: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1477:
1478: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1479: RAISE;
1480:
1474: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1475: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CALENDAR_SHIFTS');
1476: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1477:
1478: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1479: RAISE;
1480:
1481: ELSE
1482: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1478: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1479: RAISE;
1480:
1481: ELSE
1482: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1483:
1484: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1485: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1486: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1480:
1481: ELSE
1482: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1483:
1484: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1485: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1486: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1487: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CALENDAR_SHIFTS');
1488: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1484: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1485: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1486: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1487: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CALENDAR_SHIFTS');
1488: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1489:
1490: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1491: FND_MESSAGE.SET_TOKEN('COLUMN', 'CALENDAR_CODE');
1492: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CALENDAR_CODE);
1489:
1490: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1491: FND_MESSAGE.SET_TOKEN('COLUMN', 'CALENDAR_CODE');
1492: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CALENDAR_CODE);
1493: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1494:
1495: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1496: FND_MESSAGE.SET_TOKEN('COLUMN', 'SHIFT_NUM');
1497: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SHIFT_NUM));
1494:
1495: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1496: FND_MESSAGE.SET_TOKEN('COLUMN', 'SHIFT_NUM');
1497: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SHIFT_NUM));
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: END IF;
1502:
1496: FND_MESSAGE.SET_TOKEN('COLUMN', 'SHIFT_NUM');
1497: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SHIFT_NUM));
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: END IF;
1502:
1503: END;
1504:
1579: EXCEPTION
1580: WHEN OTHERS THEN
1581:
1582: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
1583: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1584: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1585: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1586: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SHIFT_DATES');
1587: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1583: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1584: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1585: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1586: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SHIFT_DATES');
1587: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1588:
1589: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1590: RAISE;
1591:
1585: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1586: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SHIFT_DATES');
1587: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1588:
1589: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1590: RAISE;
1591:
1592: ELSE
1593: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1589: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1590: RAISE;
1591:
1592: ELSE
1593: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1594:
1595: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1596: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1597: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1591:
1592: ELSE
1593: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1594:
1595: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1596: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1597: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1598: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SHIFT_DATES');
1599: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1595: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1596: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1597: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1598: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SHIFT_DATES');
1599: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1600:
1601: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1602: FND_MESSAGE.SET_TOKEN('COLUMN', 'CALENDAR_CODE');
1603: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CALENDAR_CODE);
1600:
1601: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1602: FND_MESSAGE.SET_TOKEN('COLUMN', 'CALENDAR_CODE');
1603: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CALENDAR_CODE);
1604: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1605:
1606: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1607: FND_MESSAGE.SET_TOKEN('COLUMN', 'EXCEPTION_SET_ID');
1608: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.EXCEPTION_SET_ID));
1605:
1606: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1607: FND_MESSAGE.SET_TOKEN('COLUMN', 'EXCEPTION_SET_ID');
1608: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.EXCEPTION_SET_ID));
1609: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1610:
1611: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1612: FND_MESSAGE.SET_TOKEN('COLUMN', 'SHIFT_DATE');
1613: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SHIFT_DATE));
1610:
1611: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1612: FND_MESSAGE.SET_TOKEN('COLUMN', 'SHIFT_DATE');
1613: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SHIFT_DATE));
1614: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1615:
1616: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1617: FND_MESSAGE.SET_TOKEN('COLUMN', 'SHIFT_NUM');
1618: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SHIFT_NUM));
1615:
1616: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1617: FND_MESSAGE.SET_TOKEN('COLUMN', 'SHIFT_NUM');
1618: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SHIFT_NUM));
1619: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1620:
1621: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1622: END IF;
1623:
1617: FND_MESSAGE.SET_TOKEN('COLUMN', 'SHIFT_NUM');
1618: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SHIFT_NUM));
1619: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1620:
1621: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1622: END IF;
1623:
1624: END;
1625:
1632: FOR c_rec IN c8 LOOP
1633:
1634: BEGIN
1635:
1636: IF MSC_CL_COLLECTION.v_is_incremental_refresh AND c_rec.DELETED_FLAG= MSC_UTIL.SYS_YES THEN
1637:
1638: -- set SR_INSTANCE_ID to negative to indicate a SOFT delete
1639:
1640: DELETE MSC_RESOURCE_CHANGES
1641: WHERE DEPARTMENT_ID= c_rec.DEPARTMENT_ID
1642: AND RESOURCE_ID= c_rec.RESOURCE_ID
1643: AND SHIFT_NUM= c_rec.SHIFT_NUM
1644: AND FROM_DATE= c_rec.FROM_DATE
1645: AND NVL(TO_DATE,MSC_UTIL.NULL_DATE)= NVL(c_rec.TO_DATE,MSC_UTIL.NULL_DATE)
1646: AND NVL(FROM_TIME,MSC_UTIL.NULL_VALUE)= NVL(c_rec.FROM_TIME,MSC_UTIL.NULL_VALUE)
1647: AND NVL(TO_TIME,MSC_UTIL.NULL_VALUE)= NVL(c_rec.TO_TIME,MSC_UTIL.NULL_VALUE)
1648: AND SIMULATION_SET= c_rec.SIMULATION_SET
1649: AND ACTION_TYPE= c_rec.ACTION_TYPE
1642: AND RESOURCE_ID= c_rec.RESOURCE_ID
1643: AND SHIFT_NUM= c_rec.SHIFT_NUM
1644: AND FROM_DATE= c_rec.FROM_DATE
1645: AND NVL(TO_DATE,MSC_UTIL.NULL_DATE)= NVL(c_rec.TO_DATE,MSC_UTIL.NULL_DATE)
1646: AND NVL(FROM_TIME,MSC_UTIL.NULL_VALUE)= NVL(c_rec.FROM_TIME,MSC_UTIL.NULL_VALUE)
1647: AND NVL(TO_TIME,MSC_UTIL.NULL_VALUE)= NVL(c_rec.TO_TIME,MSC_UTIL.NULL_VALUE)
1648: AND SIMULATION_SET= c_rec.SIMULATION_SET
1649: AND ACTION_TYPE= c_rec.ACTION_TYPE
1650: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
1643: AND SHIFT_NUM= c_rec.SHIFT_NUM
1644: AND FROM_DATE= c_rec.FROM_DATE
1645: AND NVL(TO_DATE,MSC_UTIL.NULL_DATE)= NVL(c_rec.TO_DATE,MSC_UTIL.NULL_DATE)
1646: AND NVL(FROM_TIME,MSC_UTIL.NULL_VALUE)= NVL(c_rec.FROM_TIME,MSC_UTIL.NULL_VALUE)
1647: AND NVL(TO_TIME,MSC_UTIL.NULL_VALUE)= NVL(c_rec.TO_TIME,MSC_UTIL.NULL_VALUE)
1648: AND SIMULATION_SET= c_rec.SIMULATION_SET
1649: AND ACTION_TYPE= c_rec.ACTION_TYPE
1650: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
1651:
1699:
1700: WHEN OTHERS THEN
1701:
1702: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
1703: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1704: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1705: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1706: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_CHANGES');
1707: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1703: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1704: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1705: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1706: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_CHANGES');
1707: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1708:
1709: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1710: RAISE;
1711:
1705: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1706: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_CHANGES');
1707: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1708:
1709: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1710: RAISE;
1711:
1712: ELSE
1713: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1709: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1710: RAISE;
1711:
1712: ELSE
1713: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1714:
1715: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1716: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1717: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1711:
1712: ELSE
1713: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1714:
1715: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1716: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1717: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1718: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_CHANGES');
1719: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1715: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1716: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1717: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1718: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_CHANGES');
1719: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1720:
1721: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1722: FND_MESSAGE.SET_TOKEN('COLUMN', 'DEPARTMENT_ID');
1723: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.DEPARTMENT_ID));
1720:
1721: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1722: FND_MESSAGE.SET_TOKEN('COLUMN', 'DEPARTMENT_ID');
1723: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.DEPARTMENT_ID));
1724: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1725:
1726: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1727: FND_MESSAGE.SET_TOKEN('COLUMN', 'RESOURCE_ID');
1728: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.RESOURCE_ID));
1725:
1726: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1727: FND_MESSAGE.SET_TOKEN('COLUMN', 'RESOURCE_ID');
1728: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.RESOURCE_ID));
1729: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1730:
1731: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1732: FND_MESSAGE.SET_TOKEN('COLUMN', 'SHIFT_NUM');
1733: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SHIFT_NUM));
1730:
1731: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1732: FND_MESSAGE.SET_TOKEN('COLUMN', 'SHIFT_NUM');
1733: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SHIFT_NUM));
1734: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1735:
1736: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1737: FND_MESSAGE.SET_TOKEN('COLUMN', 'ACTION_TYPE');
1738: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.ACTION_TYPE));
1735:
1736: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1737: FND_MESSAGE.SET_TOKEN('COLUMN', 'ACTION_TYPE');
1738: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.ACTION_TYPE));
1739: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1740:
1741: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1742: FND_MESSAGE.SET_TOKEN('COLUMN', 'SIMULATION_SET');
1743: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SIMULATION_SET);
1740:
1741: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1742: FND_MESSAGE.SET_TOKEN('COLUMN', 'SIMULATION_SET');
1743: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SIMULATION_SET);
1744: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1745:
1746: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1747: END IF;
1748:
1742: FND_MESSAGE.SET_TOKEN('COLUMN', 'SIMULATION_SET');
1743: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SIMULATION_SET);
1744: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1745:
1746: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1747: END IF;
1748:
1749: END;
1750:
1814: EXCEPTION
1815: WHEN OTHERS THEN
1816:
1817: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
1818: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1819: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1820: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1821: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SHIFT_TIMES');
1822: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1818: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1819: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1820: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1821: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SHIFT_TIMES');
1822: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1823:
1824: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1825: RAISE;
1826:
1820: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1821: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SHIFT_TIMES');
1822: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1823:
1824: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1825: RAISE;
1826:
1827: ELSE
1828: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1824: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1825: RAISE;
1826:
1827: ELSE
1828: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1829:
1830: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1831: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1832: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1826:
1827: ELSE
1828: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1829:
1830: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1831: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1832: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1833: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SHIFT_TIMES');
1834: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1830: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1831: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1832: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1833: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SHIFT_TIMES');
1834: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1835:
1836: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1837: FND_MESSAGE.SET_TOKEN('COLUMN', 'CALENDAR_CODE');
1838: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CALENDAR_CODE);
1835:
1836: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1837: FND_MESSAGE.SET_TOKEN('COLUMN', 'CALENDAR_CODE');
1838: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CALENDAR_CODE);
1839: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1840:
1841: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1842: FND_MESSAGE.SET_TOKEN('COLUMN', 'SHIFT_NUM');
1843: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SHIFT_NUM));
1840:
1841: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1842: FND_MESSAGE.SET_TOKEN('COLUMN', 'SHIFT_NUM');
1843: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SHIFT_NUM));
1844: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1845:
1846: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1847: FND_MESSAGE.SET_TOKEN('COLUMN', 'FROM_TIME');
1848: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.FROM_TIME));
1845:
1846: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1847: FND_MESSAGE.SET_TOKEN('COLUMN', 'FROM_TIME');
1848: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.FROM_TIME));
1849: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1850:
1851: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1852: FND_MESSAGE.SET_TOKEN('COLUMN', 'TO_TIME');
1853: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.TO_TIME));
1850:
1851: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1852: FND_MESSAGE.SET_TOKEN('COLUMN', 'TO_TIME');
1853: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.TO_TIME));
1854: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1855:
1856: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1857: END IF;
1858:
1852: FND_MESSAGE.SET_TOKEN('COLUMN', 'TO_TIME');
1853: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.TO_TIME));
1854: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1855:
1856: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1857: END IF;
1858:
1859: END;
1860:
1923: EXCEPTION
1924: WHEN OTHERS THEN
1925:
1926: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
1927: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1928: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1929: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1930: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SHIFT_EXCEPTIONS');
1931: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1927: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1928: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1929: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1930: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SHIFT_EXCEPTIONS');
1931: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1932:
1933: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1934: RAISE;
1935:
1929: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1930: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SHIFT_EXCEPTIONS');
1931: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1932:
1933: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1934: RAISE;
1935:
1936: ELSE
1937: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1933: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1934: RAISE;
1935:
1936: ELSE
1937: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1938:
1939: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1940: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1941: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1935:
1936: ELSE
1937: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1938:
1939: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1940: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1941: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1942: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SHIFT_EXCEPTIONS');
1943: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1939: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1940: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1941: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1942: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SHIFT_EXCEPTIONS');
1943: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1944:
1945: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1946: FND_MESSAGE.SET_TOKEN('COLUMN', 'CALENDAR_CODE');
1947: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CALENDAR_CODE);
1944:
1945: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1946: FND_MESSAGE.SET_TOKEN('COLUMN', 'CALENDAR_CODE');
1947: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CALENDAR_CODE);
1948: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1949:
1950: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1951: FND_MESSAGE.SET_TOKEN('COLUMN', 'EXCEPTION_DATE');
1952: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.EXCEPTION_DATE));
1949:
1950: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1951: FND_MESSAGE.SET_TOKEN('COLUMN', 'EXCEPTION_DATE');
1952: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.EXCEPTION_DATE));
1953: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1954:
1955: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1956: FND_MESSAGE.SET_TOKEN('COLUMN', 'EXCEPTION_SET_ID');
1957: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.EXCEPTION_SET_ID));
1954:
1955: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1956: FND_MESSAGE.SET_TOKEN('COLUMN', 'EXCEPTION_SET_ID');
1957: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.EXCEPTION_SET_ID));
1958: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1959:
1960: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1961: FND_MESSAGE.SET_TOKEN('COLUMN', 'SHIFT_NUM');
1962: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SHIFT_NUM));
1959:
1960: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1961: FND_MESSAGE.SET_TOKEN('COLUMN', 'SHIFT_NUM');
1962: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SHIFT_NUM));
1963: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1964:
1965: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1966: END IF;
1967: END;
1961: FND_MESSAGE.SET_TOKEN('COLUMN', 'SHIFT_NUM');
1962: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SHIFT_NUM));
1963: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1964:
1965: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1966: END IF;
1967: END;
1968:
1969: END LOOP;
1970:
1971: COMMIT;
1972: end if;
1973:
1974: if ((MSC_CL_COLLECTION.v_is_partial_refresh AND MSC_CL_COLLECTION.v_coll_prec.bom_flag = MSC_UTIL.SYS_YES) OR
1975: MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_incremental_refresh) then
1976:
1977: FND_MESSAGE.SET_NAME('MSC', 'MSC_DP_TASK_START');
1978: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'MSC_CL_BOM_ODS_LOAD.LOAD_RESOURCE');
1975: MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_incremental_refresh) then
1976:
1977: FND_MESSAGE.SET_NAME('MSC', 'MSC_DP_TASK_START');
1978: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'MSC_CL_BOM_ODS_LOAD.LOAD_RESOURCE');
1979: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1980: if (MSC_CL_COLLECTION.v_bom_refresh_type <> 3) then
1981: MSC_CL_BOM_ODS_LOAD.LOAD_RESOURCE;
1982: end if ;
1983: end if;
1980: if (MSC_CL_COLLECTION.v_bom_refresh_type <> 3) then
1981: MSC_CL_BOM_ODS_LOAD.LOAD_RESOURCE;
1982: end if ;
1983: end if;
1984: IF MSC_CL_COLLECTION.v_recalc_nra= MSC_UTIL.SYS_YES THEN
1985: IF MSC_CL_COLLECTION.v_discrete_flag= MSC_UTIL.SYS_YES THEN
1986: BEGIN
1987: IF MSC_CL_COLLECTION.v_instance_type = MSC_UTIL.G_INS_OTHER THEN
1988: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_NET_RESOURCE_AVAIL',
1981: MSC_CL_BOM_ODS_LOAD.LOAD_RESOURCE;
1982: end if ;
1983: end if;
1984: IF MSC_CL_COLLECTION.v_recalc_nra= MSC_UTIL.SYS_YES THEN
1985: IF MSC_CL_COLLECTION.v_discrete_flag= MSC_UTIL.SYS_YES THEN
1986: BEGIN
1987: IF MSC_CL_COLLECTION.v_instance_type = MSC_UTIL.G_INS_OTHER THEN
1988: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_NET_RESOURCE_AVAIL',
1989: MSC_CL_COLLECTION.v_instance_id, -1);
1983: end if;
1984: IF MSC_CL_COLLECTION.v_recalc_nra= MSC_UTIL.SYS_YES THEN
1985: IF MSC_CL_COLLECTION.v_discrete_flag= MSC_UTIL.SYS_YES THEN
1986: BEGIN
1987: IF MSC_CL_COLLECTION.v_instance_type = MSC_UTIL.G_INS_OTHER THEN
1988: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_NET_RESOURCE_AVAIL',
1989: MSC_CL_COLLECTION.v_instance_id, -1);
1990: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_NET_RES_INST_AVAIL',
1991: MSC_CL_COLLECTION.v_instance_id, -1);
1994: SELECT DECODE(M2A_DBLINK,
1995: NULL,'',
1996: '@'||M2A_DBLINK),
1997: DECODE( A2M_DBLINK,
1998: NULL,MSC_UTIL.NULL_DBLINK,
1999: A2M_DBLINK),
2000: INSTANCE_CODE
2001: INTO lv_dblink,
2002: lv_dest_a2m,
2004: FROM MSC_APPS_INSTANCES
2005: WHERE INSTANCE_ID=MSC_CL_COLLECTION.v_instance_id;
2006:
2007: lv_res_avail_before_sysdate := nvl(TO_NUMBER(FND_PROFILE.VAlUE('MSC_RES_AVAIL_BEFORE_SYSDAT')),1);
2008: IF MSC_CL_COLLECTION.v_instance_type <> MSC_UTIL.G_INS_OTHER THEN
2009: lv_sql_stmt:= 'SELECT nvl(mar.LRD,sysdate)- '||lv_res_avail_before_sysdate
2010: ||' FROM MRP_AP_APPS_INSTANCES_ALL'||lv_dblink||' mar'
2011: ||' WHERE INSTANCE_ID = '||MSC_CL_COLLECTION.v_instance_id
2012: ||' AND INSTANCE_CODE = '''||lv_instance_code||''''
2009: lv_sql_stmt:= 'SELECT nvl(mar.LRD,sysdate)- '||lv_res_avail_before_sysdate
2010: ||' FROM MRP_AP_APPS_INSTANCES_ALL'||lv_dblink||' mar'
2011: ||' WHERE INSTANCE_ID = '||MSC_CL_COLLECTION.v_instance_id
2012: ||' AND INSTANCE_CODE = '''||lv_instance_code||''''
2013: ||' AND nvl(A2M_DBLINK,'''||MSC_UTIL.NULL_DBLINK||''') = '''||lv_dest_a2m||'''' ;
2014: EXECUTE IMMEDIATE lv_sql_stmt INTO lv_resource_start_time;
2015: END IF;
2016: IF MSC_CL_COLLECTION.v_instance_type = MSC_UTIL.G_INS_OTHER THEN
2017: lv_resource_start_time := lv_resource_start_time - nvl(lv_res_avail_before_sysdate,1);
2012: ||' AND INSTANCE_CODE = '''||lv_instance_code||''''
2013: ||' AND nvl(A2M_DBLINK,'''||MSC_UTIL.NULL_DBLINK||''') = '''||lv_dest_a2m||'''' ;
2014: EXECUTE IMMEDIATE lv_sql_stmt INTO lv_resource_start_time;
2015: END IF;
2016: IF MSC_CL_COLLECTION.v_instance_type = MSC_UTIL.G_INS_OTHER THEN
2017: lv_resource_start_time := lv_resource_start_time - nvl(lv_res_avail_before_sysdate,1);
2018: END IF;
2019:
2020: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'@@before net res avai : debug 1');
2016: IF MSC_CL_COLLECTION.v_instance_type = MSC_UTIL.G_INS_OTHER THEN
2017: lv_resource_start_time := lv_resource_start_time - nvl(lv_res_avail_before_sysdate,1);
2018: END IF;
2019:
2020: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'@@before net res avai : debug 1');
2021: lv_ret_res_ava:=MSC_RESOURCE_AVAILABILITY.CALC_RESOURCE_AVAILABILITY(lv_resource_start_time,MSC_CL_COLLECTION.v_coll_prec.org_group_flag,FALSE);
2022:
2023: IF lv_ret_res_ava = 2 THEN
2024: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_CALC_RES_AVAIL_FAIL');
2021: lv_ret_res_ava:=MSC_RESOURCE_AVAILABILITY.CALC_RESOURCE_AVAILABILITY(lv_resource_start_time,MSC_CL_COLLECTION.v_coll_prec.org_group_flag,FALSE);
2022:
2023: IF lv_ret_res_ava = 2 THEN
2024: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_CALC_RES_AVAIL_FAIL');
2025: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2026: MSC_CL_COLLECTION.v_warning_flag:=MSC_UTIL.SYS_YES;
2027: ELSIF lv_ret_res_ava <> 0 THEN
2028: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_CALC_RES_AVAIL_FAIL');
2029: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2022:
2023: IF lv_ret_res_ava = 2 THEN
2024: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_CALC_RES_AVAIL_FAIL');
2025: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2026: MSC_CL_COLLECTION.v_warning_flag:=MSC_UTIL.SYS_YES;
2027: ELSIF lv_ret_res_ava <> 0 THEN
2028: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_CALC_RES_AVAIL_FAIL');
2029: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2030: RAISE ex_calc_res_avail;
2025: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2026: MSC_CL_COLLECTION.v_warning_flag:=MSC_UTIL.SYS_YES;
2027: ELSIF lv_ret_res_ava <> 0 THEN
2028: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_CALC_RES_AVAIL_FAIL');
2029: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2030: RAISE ex_calc_res_avail;
2031:
2032: -- ERRBUF:= FND_MESSAGE.GET;
2033: -- RETCODE:= G_ERROR;
2547: WHEN OTHERS THEN
2548:
2549: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
2550:
2551: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2552: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2553: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2554: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TRADING_PARTNERS');
2555: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2551: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2552: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2553: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2554: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TRADING_PARTNERS');
2555: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2556:
2557: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2558: RAISE;
2559:
2553: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2554: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TRADING_PARTNERS');
2555: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2556:
2557: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2558: RAISE;
2559:
2560: ELSE
2561: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2557: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2558: RAISE;
2559:
2560: ELSE
2561: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2562:
2563: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2564: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2565: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2559:
2560: ELSE
2561: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2562:
2563: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2564: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2565: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2566: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TRADING_PARTNERS');
2567: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2563: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2564: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2565: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2566: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TRADING_PARTNERS');
2567: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2568:
2569: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2570: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TP_ID');
2571: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SR_TP_ID));
2568:
2569: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2570: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TP_ID');
2571: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SR_TP_ID));
2572: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2573:
2574: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2575: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
2576: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.ORGANIZATION_CODE);
2573:
2574: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2575: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
2576: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.ORGANIZATION_CODE);
2577: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2578:
2579: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2580: END IF;
2581:
2575: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
2576: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.ORGANIZATION_CODE);
2577: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2578:
2579: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2580: END IF;
2581:
2582: END;
2583:
2606: TP_SITE_CODE= c_rec.TP_SITE_CODE,
2607: LOCATION= c_rec.LOCATION,
2608: LONGITUDE= c_rec.LONGITUDE,
2609: LATITUDE= c_rec.LATITUDE,
2610: DELETED_FLAG= MSC_UTIL.SYS_NO,
2611: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2612: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2613: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2614: WHERE PARTNER_TYPE= 3
2649: c_rec.SR_TP_SITE_ID,
2650: c_rec.SR_INSTANCE_ID,
2651: c_rec.TP_SITE_CODE,
2652: c_rec.LOCATION,
2653: MSC_UTIL.SYS_NO,
2654: MSC_CL_COLLECTION.v_last_collection_id,
2655: MSC_CL_COLLECTION.v_current_date,
2656: MSC_CL_COLLECTION.v_current_user,
2657: MSC_CL_COLLECTION.v_current_date,
2663: WHEN OTHERS THEN
2664:
2665: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
2666:
2667: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2668: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2669: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2670: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TRADING_PARTNERS_SITES');
2671: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2667: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2668: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2669: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2670: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TRADING_PARTNERS_SITES');
2671: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2672:
2673: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2674: RAISE;
2675:
2669: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2670: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TRADING_PARTNERS_SITES');
2671: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2672:
2673: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2674: RAISE;
2675:
2676: ELSE
2677: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2673: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2674: RAISE;
2675:
2676: ELSE
2677: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2678:
2679: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2680: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2681: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2675:
2676: ELSE
2677: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2678:
2679: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2680: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2681: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2682: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TRADING_PARTNERS_SITES');
2683: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2679: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2680: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2681: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2682: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TRADING_PARTNERS_SITES');
2683: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2684:
2685: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2686: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TP_SITE_ID');
2687: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SR_TP_SITE_ID));
2684:
2685: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2686: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TP_SITE_ID');
2687: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SR_TP_SITE_ID));
2688: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2689:
2690: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2691: FND_MESSAGE.SET_TOKEN('COLUMN', 'LOCATION');
2692: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.LOCATION);
2689:
2690: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2691: FND_MESSAGE.SET_TOKEN('COLUMN', 'LOCATION');
2692: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.LOCATION);
2693: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2694:
2695: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2696: END IF;
2697:
2691: FND_MESSAGE.SET_TOKEN('COLUMN', 'LOCATION');
2692: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.LOCATION);
2693: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2694:
2695: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2696: END IF;
2697:
2698: END;
2699:
2745: WHEN OTHERS THEN
2746:
2747: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
2748:
2749: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2750: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2751: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2752: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_LOCATION_ASSOCIATIONS');
2753: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2749: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2750: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2751: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2752: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_LOCATION_ASSOCIATIONS');
2753: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2754:
2755: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2756: RAISE;
2757:
2751: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2752: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_LOCATION_ASSOCIATIONS');
2753: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2754:
2755: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2756: RAISE;
2757:
2758: ELSE
2759:
2756: RAISE;
2757:
2758: ELSE
2759:
2760: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2761:
2762: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2763: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2764: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2758: ELSE
2759:
2760: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2761:
2762: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2763: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2764: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2765: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_LOCATION_ASSOCIATIONS');
2766: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2762: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2763: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2764: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2765: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_LOCATION_ASSOCIATIONS');
2766: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2767:
2768: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2769: FND_MESSAGE.SET_TOKEN('COLUMN', 'LOCATION_ID');
2770: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.LOCATION_ID));
2767:
2768: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2769: FND_MESSAGE.SET_TOKEN('COLUMN', 'LOCATION_ID');
2770: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.LOCATION_ID));
2771: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2772:
2773: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2774: FND_MESSAGE.SET_TOKEN('COLUMN', 'LOCATION_CODE');
2775: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.LOCATION_CODE);
2772:
2773: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2774: FND_MESSAGE.SET_TOKEN('COLUMN', 'LOCATION_CODE');
2775: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.LOCATION_CODE);
2776: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2777:
2778: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2779: END IF;
2780:
2774: FND_MESSAGE.SET_TOKEN('COLUMN', 'LOCATION_CODE');
2775: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.LOCATION_CODE);
2776: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2777:
2778: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2779: END IF;
2780:
2781: END;
2782:
2820: EXCEPTION
2821: WHEN OTHERS THEN
2822: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
2823:
2824: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2825: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2826: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2827: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_LOCATION_ASSOCIATIONS');
2828: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2824: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2825: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2826: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2827: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_LOCATION_ASSOCIATIONS');
2828: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2829:
2830: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2831: RAISE;
2832:
2826: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2827: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_LOCATION_ASSOCIATIONS');
2828: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2829:
2830: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2831: RAISE;
2832:
2833: ELSE
2834: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2830: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2831: RAISE;
2832:
2833: ELSE
2834: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2835:
2836: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2837: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2838: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2832:
2833: ELSE
2834: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2835:
2836: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2837: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2838: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2839: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_LOCATION_ASSOCIATIONS');
2840: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2836: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2837: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2838: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2839: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_LOCATION_ASSOCIATIONS');
2840: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2841:
2842: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2843: FND_MESSAGE.SET_TOKEN('COLUMN', 'LOCATION_ID');
2844: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.LOCATION_ID));
2841:
2842: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2843: FND_MESSAGE.SET_TOKEN('COLUMN', 'LOCATION_ID');
2844: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.LOCATION_ID));
2845: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2846:
2847: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2848: FND_MESSAGE.SET_TOKEN('COLUMN', 'LOCATION_CODE');
2849: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.LOCATION_CODE);
2846:
2847: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2848: FND_MESSAGE.SET_TOKEN('COLUMN', 'LOCATION_CODE');
2849: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.LOCATION_CODE);
2850: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2851:
2852: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2853: END IF;
2854:
2848: FND_MESSAGE.SET_TOKEN('COLUMN', 'LOCATION_CODE');
2849: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.LOCATION_CODE);
2850: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2851:
2852: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2853: END IF;
2854:
2855: END;
2856:
2869: FOR c_rec IN c4 LOOP
2870:
2871: if(MSC_CL_COLLECTION.v_is_legacy_refresh) then
2872:
2873: if (c_rec.DELETED_FLAG =MSC_UTIL.SYS_YES) then
2874:
2875: delete from MSC_PARTNER_CONTACTS where
2876: partner_id=c_rec.PARTNER_ID
2877: and nvl(partner_site_id,-99999)=nvl(c_rec.PARTNER_SITE_ID,-99999)
2876: partner_id=c_rec.PARTNER_ID
2877: and nvl(partner_site_id,-99999)=nvl(c_rec.PARTNER_SITE_ID,-99999)
2878: and partner_type=c_rec.PARTNER_TYPE
2879: and SR_INSTANCE_ID =MSC_CL_COLLECTION.v_instance_id
2880: and c_rec.DELETED_FLAG =MSC_UTIL.SYS_YES;
2881: else
2882: UPDATE MSC_PARTNER_CONTACTS
2883: set Name=c_rec.NAME,
2884: DISPLAY_NAME =c_rec.DISPLAY_NAME,
2899:
2900: END IF ;
2901:
2902:
2903: IF MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh OR MSC_CL_COLLECTION.v_is_cont_refresh OR (SQL%NOTFOUND and c_rec.DELETED_FLAG =MSC_UTIL.SYS_NO)
2904: THEN
2905: IF lv_old_partner_id <> NVL(c_rec.partner_id,0) OR
2906: lv_old_partner_site_id <> NVL(c_rec.partner_site_id,0) OR
2907: lv_old_partner_type <> c_rec.partner_type THEN
2942: EXCEPTION
2943: WHEN OTHERS THEN
2944: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
2945:
2946: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2947: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2948: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2949: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_PARTNER_CONTACTS');
2950: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2946: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2947: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2948: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2949: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_PARTNER_CONTACTS');
2950: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2951:
2952: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2953: RAISE;
2954:
2948: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2949: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_PARTNER_CONTACTS');
2950: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2951:
2952: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2953: RAISE;
2954:
2955: ELSE
2956: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2952: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2953: RAISE;
2954:
2955: ELSE
2956: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2957:
2958: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2959: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2960: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2954:
2955: ELSE
2956: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2957:
2958: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2959: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2960: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2961: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_PARTNER_CONTACTS');
2962: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2958: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2959: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2960: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2961: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_PARTNER_CONTACTS');
2962: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2963:
2964: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2965: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_ID');
2966: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.PARTNER_ID));
2963:
2964: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2965: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_ID');
2966: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.PARTNER_ID));
2967: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2968:
2969: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2970: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_SITE_ID');
2971: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.PARTNER_SITE_ID));
2968:
2969: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2970: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_SITE_ID');
2971: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.PARTNER_SITE_ID));
2972: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2973:
2974: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2975: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_TYPE');
2976: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.PARTNER_TYPE));
2973:
2974: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2975: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_TYPE');
2976: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.PARTNER_TYPE));
2977: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2978:
2979: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2980: END IF;
2981:
2975: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_TYPE');
2976: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.PARTNER_TYPE));
2977: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2978:
2979: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2980: END IF;
2981:
2982: END;
2983:
3069: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3070: WHERE SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
3071: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID;
3072: /* Bug: 1993151 remove the collected flag from the update statement */
3073: -- AND COLLECTED_FLAG= MSC_UTIL.SYS_YES;
3074:
3075: IF SQL%NOTFOUND THEN
3076:
3077: INSERT INTO MSC_PARAMETERS
3127: c_rec.REPETITIVE_BUCKET_SIZE2,
3128: c_rec.REPETITIVE_BUCKET_SIZE3,
3129: c_rec.REPETITIVE_ANCHOR_DATE,
3130: 1,
3131: MSC_UTIL.SYS_YES,
3132: c_rec.SR_INSTANCE_ID,
3133: MSC_CL_COLLECTION.v_current_date,
3134: MSC_CL_COLLECTION.v_current_user,
3135: MSC_CL_COLLECTION.v_current_date,
3142: WHEN OTHERS THEN
3143:
3144: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
3145:
3146: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3147: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3148: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_PARAMETER');
3149: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_PARAMETERS');
3150: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3146: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3147: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3148: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_PARAMETER');
3149: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_PARAMETERS');
3150: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3151:
3152: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3153: RAISE;
3154:
3148: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_PARAMETER');
3149: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_PARAMETERS');
3150: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3151:
3152: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3153: RAISE;
3154:
3155: ELSE
3156:
3153: RAISE;
3154:
3155: ELSE
3156:
3157: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3158:
3159: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3160: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3161: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_PARAMETER');
3155: ELSE
3156:
3157: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3158:
3159: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3160: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3161: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_PARAMETER');
3162: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_PARAMETERS');
3163: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3159: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3160: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3161: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_PARAMETER');
3162: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_PARAMETERS');
3163: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3164:
3165: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3166: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
3167: FND_MESSAGE.SET_TOKEN('VALUE',
3166: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
3167: FND_MESSAGE.SET_TOKEN('VALUE',
3168: MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
3169: MSC_CL_COLLECTION.v_instance_id));
3170: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3171:
3172: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3173: END IF;
3174:
3168: MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
3169: MSC_CL_COLLECTION.v_instance_id));
3170: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3171:
3172: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3173: END IF;
3174:
3175: END;
3176:
3315: WHEN OTHERS THEN
3316:
3317: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
3318:
3319: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3320: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3321: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_UOM');
3322: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_UNITS_OF_MEASURE');
3323: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3319: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3320: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3321: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_UOM');
3322: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_UNITS_OF_MEASURE');
3323: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3324:
3325: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3326: RAISE;
3327:
3321: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_UOM');
3322: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_UNITS_OF_MEASURE');
3323: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3324:
3325: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3326: RAISE;
3327:
3328: ELSE
3329:
3326: RAISE;
3327:
3328: ELSE
3329:
3330: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3331:
3332: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3333: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3334: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_UOM');
3328: ELSE
3329:
3330: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3331:
3332: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3333: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3334: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_UOM');
3335: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_UNITS_OF_MEASURE');
3336: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3332: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3333: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3334: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_UOM');
3335: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_UNITS_OF_MEASURE');
3336: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3337:
3338: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3339: FND_MESSAGE.SET_TOKEN('COLUMN', 'UOM_CODE');
3340: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.UOM_CODE);
3337:
3338: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3339: FND_MESSAGE.SET_TOKEN('COLUMN', 'UOM_CODE');
3340: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.UOM_CODE);
3341: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3342:
3343: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3344: FND_MESSAGE.SET_TOKEN('COLUMN', 'UNIT_OF_MEASURE');
3345: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.UNIT_OF_MEASURE);
3342:
3343: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3344: FND_MESSAGE.SET_TOKEN('COLUMN', 'UNIT_OF_MEASURE');
3345: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.UNIT_OF_MEASURE);
3346: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3347:
3348: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3349: END IF;
3350:
3344: FND_MESSAGE.SET_TOKEN('COLUMN', 'UNIT_OF_MEASURE');
3345: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.UNIT_OF_MEASURE);
3346: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3347:
3348: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3349: END IF;
3350:
3351: END;
3352:
3434: WHEN OTHERS THEN
3435:
3436: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
3437:
3438: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3439: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3440: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_UOM');
3441: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_UOM_CLASS_CONVERSIONS');
3442: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3438: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3439: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3440: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_UOM');
3441: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_UOM_CLASS_CONVERSIONS');
3442: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3443:
3444: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3445: RAISE;
3446:
3440: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_UOM');
3441: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_UOM_CLASS_CONVERSIONS');
3442: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3443:
3444: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3445: RAISE;
3446:
3447: ELSE
3448:
3445: RAISE;
3446:
3447: ELSE
3448:
3449: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3450:
3451: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3452: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3453: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_UOM');
3447: ELSE
3448:
3449: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3450:
3451: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3452: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3453: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_UOM');
3454: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_UOM_CLASS_CONVERSIONS');
3455: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3451: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3452: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3453: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_UOM');
3454: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_UOM_CLASS_CONVERSIONS');
3455: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3456:
3457: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3458: FND_MESSAGE.SET_TOKEN('COLUMN', 'MSC_CL_ITEM_ODS_LOAD.item_name');
3459: FND_MESSAGE.SET_TOKEN('VALUE', MSC_CL_ITEM_ODS_LOAD.item_name( c_rec.INVENTORY_ITEM_ID));
3456:
3457: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3458: FND_MESSAGE.SET_TOKEN('COLUMN', 'MSC_CL_ITEM_ODS_LOAD.item_name');
3459: FND_MESSAGE.SET_TOKEN('VALUE', MSC_CL_ITEM_ODS_LOAD.item_name( c_rec.INVENTORY_ITEM_ID));
3460: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3461:
3462: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3463: FND_MESSAGE.SET_TOKEN('COLUMN', 'FROM_UNIT_OF_MEASURE');
3464: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.FROM_UNIT_OF_MEASURE);
3461:
3462: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3463: FND_MESSAGE.SET_TOKEN('COLUMN', 'FROM_UNIT_OF_MEASURE');
3464: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.FROM_UNIT_OF_MEASURE);
3465: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3466:
3467: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3468: FND_MESSAGE.SET_TOKEN('COLUMN', 'TO_UNIT_OF_MEASURE');
3469: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.TO_UNIT_OF_MEASURE);
3466:
3467: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3468: FND_MESSAGE.SET_TOKEN('COLUMN', 'TO_UNIT_OF_MEASURE');
3469: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.TO_UNIT_OF_MEASURE);
3470: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3471:
3472: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3473: END IF;
3474:
3468: FND_MESSAGE.SET_TOKEN('COLUMN', 'TO_UNIT_OF_MEASURE');
3469: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.TO_UNIT_OF_MEASURE);
3470: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3471:
3472: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3473: END IF;
3474:
3475: END;
3476:
3557: WHEN OTHERS THEN
3558:
3559: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
3560:
3561: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3562: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3563: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_UOM');
3564: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_UOM_CONVERSIONS');
3565: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3561: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3562: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3563: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_UOM');
3564: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_UOM_CONVERSIONS');
3565: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3566:
3567: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3568: RAISE;
3569:
3563: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_UOM');
3564: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_UOM_CONVERSIONS');
3565: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3566:
3567: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3568: RAISE;
3569:
3570: ELSE
3571:
3568: RAISE;
3569:
3570: ELSE
3571:
3572: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3573:
3574: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3575: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3576: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_UOM');
3570: ELSE
3571:
3572: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3573:
3574: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3575: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3576: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_UOM');
3577: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_UOM_CONVERSIONS');
3578: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3574: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3575: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3576: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_UOM');
3577: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_UOM_CONVERSIONS');
3578: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3579:
3580: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3581: FND_MESSAGE.SET_TOKEN('COLUMN', 'MSC_CL_ITEM_ODS_LOAD.item_name');
3582: FND_MESSAGE.SET_TOKEN('VALUE', MSC_CL_ITEM_ODS_LOAD.item_name( c_rec.INVENTORY_ITEM_ID));
3579:
3580: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3581: FND_MESSAGE.SET_TOKEN('COLUMN', 'MSC_CL_ITEM_ODS_LOAD.item_name');
3582: FND_MESSAGE.SET_TOKEN('VALUE', MSC_CL_ITEM_ODS_LOAD.item_name( c_rec.INVENTORY_ITEM_ID));
3583: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3584:
3585: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3586: FND_MESSAGE.SET_TOKEN('COLUMN', 'UNIT_OF_MEASURE');
3587: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.UNIT_OF_MEASURE);
3584:
3585: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3586: FND_MESSAGE.SET_TOKEN('COLUMN', 'UNIT_OF_MEASURE');
3587: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.UNIT_OF_MEASURE);
3588: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3589:
3590: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3591: FND_MESSAGE.SET_TOKEN('COLUMN', 'UOM_CODE');
3592: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.UOM_CODE);
3589:
3590: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3591: FND_MESSAGE.SET_TOKEN('COLUMN', 'UOM_CODE');
3592: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.UOM_CODE);
3593: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3594:
3595: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3596: END IF;
3597:
3591: FND_MESSAGE.SET_TOKEN('COLUMN', 'UOM_CODE');
3592: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.UOM_CODE);
3593: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3594:
3595: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3596: END IF;
3597:
3598: END;
3599:
3963: lv_partner_count NUMBER;
3964:
3965: lv_tp_id_count NUMBER := 0;
3966: lv_tp_site_id_count NUMBER := 0;
3967: lv_tp_stat_stale NUMBER := MSC_UTIL.SYS_NO;
3968: lv_tp_site_stat_stale NUMBER := MSC_UTIL.SYS_NO;
3969: lv_ins_records NUMBER := 0;
3970:
3971: BEGIN
3964:
3965: lv_tp_id_count NUMBER := 0;
3966: lv_tp_site_id_count NUMBER := 0;
3967: lv_tp_stat_stale NUMBER := MSC_UTIL.SYS_NO;
3968: lv_tp_site_stat_stale NUMBER := MSC_UTIL.SYS_NO;
3969: lv_ins_records NUMBER := 0;
3970:
3971: BEGIN
3972:
3986: WHEN OTHERS THEN
3987: lv_msc_tp_coll_window := 0;
3988: END;
3989:
3990: IF (MSC_CL_COLLECTION.v_apps_ver = MSC_UTIL.G_APPS107) OR (MSC_CL_COLLECTION.v_apps_ver = MSC_UTIL.G_APPS110) OR lv_msc_tp_coll_window IS NULL THEN
3991: lv_msc_tp_coll_window := 0;
3992: END IF;
3993:
3994: begin
3991: lv_msc_tp_coll_window := 0;
3992: END IF;
3993:
3994: begin
3995: select num_rows,decode (stale_stats,'NO', MSC_UTIL.SYS_NO, MSC_UTIL.SYS_YES )
3996: into lv_tp_id_count, lv_tp_stat_stale
3997: from dba_TAB_STATISTICS
3998: where table_name = 'MSC_TP_ID_LID';
3999: exception when no_data_found then
3996: into lv_tp_id_count, lv_tp_stat_stale
3997: from dba_TAB_STATISTICS
3998: where table_name = 'MSC_TP_ID_LID';
3999: exception when no_data_found then
4000: lv_tp_stat_stale := MSC_UTIL.SYS_YES ;
4001: end;
4002:
4003: begin
4004: select num_rows,decode (stale_stats,'NO', MSC_UTIL.SYS_NO, MSC_UTIL.SYS_YES )
4000: lv_tp_stat_stale := MSC_UTIL.SYS_YES ;
4001: end;
4002:
4003: begin
4004: select num_rows,decode (stale_stats,'NO', MSC_UTIL.SYS_NO, MSC_UTIL.SYS_YES )
4005: into lv_tp_site_id_count, lv_tp_site_stat_stale
4006: from dba_TAB_STATISTICS
4007: where table_name ='MSC_TP_SITE_ID_LID';
4008: exception when no_data_found then
4005: into lv_tp_site_id_count, lv_tp_site_stat_stale
4006: from dba_TAB_STATISTICS
4007: where table_name ='MSC_TP_SITE_ID_LID';
4008: exception when no_data_found then
4009: lv_tp_site_stat_stale := MSC_UTIL.SYS_YES ;
4010: end;
4011:
4012: /* if complete refresh, regen the key mapping data */
4013: IF MSC_CL_COLLECTION.v_is_complete_refresh THEN
4026: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_TP_SITE_ID_LID');
4027: END IF;
4028:
4029: END IF;
4030: lv_tp_stat_stale := MSC_UTIL.SYS_YES ;
4031: lv_tp_site_stat_stale := MSC_UTIL.SYS_YES ;
4032: END IF;
4033:
4034: /*************** PREPLACE CHANGE START *****************/
4027: END IF;
4028:
4029: END IF;
4030: lv_tp_stat_stale := MSC_UTIL.SYS_YES ;
4031: lv_tp_site_stat_stale := MSC_UTIL.SYS_YES ;
4032: END IF;
4033:
4034: /*************** PREPLACE CHANGE START *****************/
4035:
4038: --when sourcing SRS launch parameter is Yes --and delete supplier and customer records
4039: --from MSC_TP_ID_LID when either Supplier or Customer SRS launch parameter is Yes.
4040:
4041: IF (MSC_CL_COLLECTION.v_is_partial_refresh OR MSC_CL_COLLECTION.V_IS_CONT_REFRESH) THEN
4042: IF (MSC_CL_COLLECTION.v_coll_prec.sourcing_rule_flag=MSC_UTIL.SYS_YES) THEN
4043: DELETE MSC_TP_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id and partner_type=4;
4044: DELETE MSC_TP_ID_LID WHERE SR_INSTANCE_ID= -1 and partner_type=4;
4045: lv_tp_stat_stale := MSC_UTIL.SYS_YES ;
4046: END IF;
4041: IF (MSC_CL_COLLECTION.v_is_partial_refresh OR MSC_CL_COLLECTION.V_IS_CONT_REFRESH) THEN
4042: IF (MSC_CL_COLLECTION.v_coll_prec.sourcing_rule_flag=MSC_UTIL.SYS_YES) THEN
4043: DELETE MSC_TP_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id and partner_type=4;
4044: DELETE MSC_TP_ID_LID WHERE SR_INSTANCE_ID= -1 and partner_type=4;
4045: lv_tp_stat_stale := MSC_UTIL.SYS_YES ;
4046: END IF;
4047: END IF;
4048:
4049: IF MSC_CL_COLLECTION.v_is_partial_refresh THEN
4047: END IF;
4048:
4049: IF MSC_CL_COLLECTION.v_is_partial_refresh THEN
4050:
4051: IF ((MSC_CL_COLLECTION.v_coll_prec.tp_vendor_flag = MSC_UTIL.SYS_YES) or
4052: (MSC_CL_COLLECTION.v_coll_prec.tp_customer_flag = MSC_UTIL.SYS_YES)) THEN
4053:
4054: -- Note now vendor or customer cannot be refreshed
4055: -- separately. If that functionality needs to be provided
4048:
4049: IF MSC_CL_COLLECTION.v_is_partial_refresh THEN
4050:
4051: IF ((MSC_CL_COLLECTION.v_coll_prec.tp_vendor_flag = MSC_UTIL.SYS_YES) or
4052: (MSC_CL_COLLECTION.v_coll_prec.tp_customer_flag = MSC_UTIL.SYS_YES)) THEN
4053:
4054: -- Note now vendor or customer cannot be refreshed
4055: -- separately. If that functionality needs to be provided
4056: -- in future then the the ID_LID tables will have to
4070: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_TP_ID_LID');
4071: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_TP_SITE_ID_LID');
4072: END IF;
4073: END IF;
4074: lv_tp_stat_stale := MSC_UTIL.SYS_YES ;
4075: lv_tp_site_stat_stale := MSC_UTIL.SYS_YES ;
4076: END IF;
4077:
4078: END IF;
4071: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_TP_SITE_ID_LID');
4072: END IF;
4073: END IF;
4074: lv_tp_stat_stale := MSC_UTIL.SYS_YES ;
4075: lv_tp_site_stat_stale := MSC_UTIL.SYS_YES ;
4076: END IF;
4077:
4078: END IF;
4079:
4081:
4082: --agmcont
4083: IF MSC_CL_COLLECTION.V_IS_CONT_REFRESH THEN
4084:
4085: IF ((MSC_CL_COLLECTION.v_coll_prec.tp_vendor_flag = MSC_UTIL.SYS_YES) or
4086: (MSC_CL_COLLECTION.v_coll_prec.tp_customer_flag = MSC_UTIL.SYS_YES)) THEN
4087:
4088: -- Note now vendor or customer cannot be refreshed
4089: -- separately. If that functionality needs to be provided
4082: --agmcont
4083: IF MSC_CL_COLLECTION.V_IS_CONT_REFRESH THEN
4084:
4085: IF ((MSC_CL_COLLECTION.v_coll_prec.tp_vendor_flag = MSC_UTIL.SYS_YES) or
4086: (MSC_CL_COLLECTION.v_coll_prec.tp_customer_flag = MSC_UTIL.SYS_YES)) THEN
4087:
4088: -- Note now vendor or customer cannot be refreshed
4089: -- separately. If that functionality needs to be provided
4090: -- in future then the the ID_LID tables will have to
4102: ELSE
4103: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_TP_ID_LID');
4104: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_TP_SITE_ID_LID');
4105: END IF;
4106: lv_tp_stat_stale := MSC_UTIL.SYS_YES ;
4107: lv_tp_site_stat_stale := MSC_UTIL.SYS_YES ;
4108: END IF;
4109: END IF;
4110:
4103: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_TP_ID_LID');
4104: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_TP_SITE_ID_LID');
4105: END IF;
4106: lv_tp_stat_stale := MSC_UTIL.SYS_YES ;
4107: lv_tp_site_stat_stale := MSC_UTIL.SYS_YES ;
4108: END IF;
4109: END IF;
4110:
4111: END IF;
4131: AND nvl(company_id, -1) = c_rec.company_id1;
4132:
4133: IF (lv_partner_count > 0) THEN
4134:
4135: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'Partner is being merged..');
4136: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'Partner name is :'|| c_rec.partner_name);
4137:
4138: ELSE
4139: BEGIN
4132:
4133: IF (lv_partner_count > 0) THEN
4134:
4135: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'Partner is being merged..');
4136: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'Partner name is :'|| c_rec.partner_name);
4137:
4138: ELSE
4139: BEGIN
4140:
4146: AND nvl(mtp.company_id,-1) = c_rec.company_id1;
4147:
4148: EXCEPTION
4149: WHEN DUP_VAL_ON_INDEX THEN
4150: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,SQLERRM);
4151: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'In DUP_VAL_ON_INDEX exception clause of c5_tpname');
4152: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'The value of partner name is: '|| c_rec.PARTNER_NAME);
4153: -- Fetch the Old Partner Id
4154: SELECT partner_id
4147:
4148: EXCEPTION
4149: WHEN DUP_VAL_ON_INDEX THEN
4150: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,SQLERRM);
4151: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'In DUP_VAL_ON_INDEX exception clause of c5_tpname');
4152: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'The value of partner name is: '|| c_rec.PARTNER_NAME);
4153: -- Fetch the Old Partner Id
4154: SELECT partner_id
4155: INTO lv_old_partner_id
4148: EXCEPTION
4149: WHEN DUP_VAL_ON_INDEX THEN
4150: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,SQLERRM);
4151: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'In DUP_VAL_ON_INDEX exception clause of c5_tpname');
4152: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'The value of partner name is: '|| c_rec.PARTNER_NAME);
4153: -- Fetch the Old Partner Id
4154: SELECT partner_id
4155: INTO lv_old_partner_id
4156: FROM msc_trading_partners
4159: AND SR_INSTANCE_ID = c_rec.SR_INSTANCE_ID
4160: AND PARTNER_TYPE = c_rec.PARTNER_TYPE
4161: AND nvl(company_id,-1) = c_rec.company_id1;
4162:
4163: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'The old Partner_Id IS: ' || to_number(lv_old_partner_id));
4164: -- Fetch the New Partner Id
4165: SELECT partner_id
4166: INTO lv_new_partner_id
4167: FROM msc_trading_partners
4170: AND SR_INSTANCE_ID = c_rec.SR_INSTANCE_ID
4171: AND PARTNER_TYPE = c_rec.PARTNER_TYPE
4172: AND nvl(company_id,-1) = c_rec.company_id1;
4173:
4174: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'The new Partner_Id IS: ' || to_number(lv_new_partner_id));
4175:
4176: /*DELETE FROM MSC_TRADING_PARTNERS
4177: WHERE SR_TP_ID = c_rec.SR_TP_ID
4178: AND SR_INSTANCE_ID = c_rec.SR_INSTANCE_ID
4194: */
4195:
4196: WHEN OTHERS THEN
4197:
4198: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
4199:
4200: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4201: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4202: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'MSC_CL_SETUP_ODS_LOAD.TRANSFORM_KEYS');
4196: WHEN OTHERS THEN
4197:
4198: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
4199:
4200: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4201: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4202: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'MSC_CL_SETUP_ODS_LOAD.TRANSFORM_KEYS');
4203: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TRADING_PARTNERS');
4204: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4200: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4201: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4202: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'MSC_CL_SETUP_ODS_LOAD.TRANSFORM_KEYS');
4203: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TRADING_PARTNERS');
4204: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4205:
4206: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4207: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_TYPE');
4208: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_TYPE);
4205:
4206: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4207: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_TYPE');
4208: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_TYPE);
4209: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4210:
4211: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4212: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TP_ID');
4213: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SR_TP_ID);
4210:
4211: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4212: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TP_ID');
4213: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SR_TP_ID);
4214: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4215:
4216: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4217: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_NAME');
4218: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_NAME);
4215:
4216: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4217: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_NAME');
4218: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_NAME);
4219: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4220:
4221: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4222:
4223: END;
4217: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_NAME');
4218: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_NAME);
4219: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4220:
4221: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4222:
4223: END;
4224: END IF;-- IF (lv_partner_count > 0) THEN
4225: END LOOP;
4273: NULL;
4274:
4275: WHEN OTHERS THEN
4276:
4277: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4278: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4279: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
4280: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TRADING_PARTNERS');
4281: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4277: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4278: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4279: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
4280: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TRADING_PARTNERS');
4281: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4282:
4283: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4284: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_NAME');
4285: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_NAME);
4282:
4283: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4284: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_NAME');
4285: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_NAME);
4286: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4287:
4288: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4289:
4290: ERRBUF := sqlerrm;
4284: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_NAME');
4285: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_NAME);
4286: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4287:
4288: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4289:
4290: ERRBUF := sqlerrm;
4291: RETCODE := MSC_UTIL.G_ERROR;
4292: RAISE;
4287:
4288: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4289:
4290: ERRBUF := sqlerrm;
4291: RETCODE := MSC_UTIL.G_ERROR;
4292: RAISE;
4293:
4294: END;
4295:
4298: COMMIT;
4299:
4300: lv_ins_records := 0;
4301: -- ==== populate msc_tp_id_lid with newly created Suppliers / Customers ====
4302: IF MSC_CL_COLLECTION.v_apps_ver < MSC_UTIL.G_APPS115 AND MSC_CL_COLLECTION.v_apps_ver <> -1 THEN
4303:
4304: /* For 107 and 110 the vendor_id and vendor
4305: site id can be duplicate, therefore we use
4306: the cursors to handle such exceptions, but for 11i we can use a straight
4330: WHEN OTHERS THEN
4331:
4332: IF SQLCODE IN (-1653,-1654) THEN
4333:
4334: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4335: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4336: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
4337: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TP_ID_LID');
4338: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4334: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4335: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4336: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
4337: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TP_ID_LID');
4338: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4339:
4340: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4341: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TP_ID');
4342: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SR_TP_ID);
4339:
4340: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4341: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TP_ID');
4342: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SR_TP_ID);
4343: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4344:
4345: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4346: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_TYPE');
4347: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_TYPE);
4344:
4345: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4346: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_TYPE');
4347: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_TYPE);
4348: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4349:
4350: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4351: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_ID');
4352: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_ID);
4349:
4350: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4351: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_ID');
4352: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_ID);
4353: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4354:
4355: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4356:
4357: ERRBUF := sqlerrm;
4351: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_ID');
4352: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_ID);
4353: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4354:
4355: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4356:
4357: ERRBUF := sqlerrm;
4358: RETCODE := MSC_UTIL.G_ERROR;
4359: RAISE;
4354:
4355: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4356:
4357: ERRBUF := sqlerrm;
4358: RETCODE := MSC_UTIL.G_ERROR;
4359: RAISE;
4360:
4361: ELSE
4362:
4359: RAISE;
4360:
4361: ELSE
4362:
4363: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
4364:
4365: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4366: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4367: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
4361: ELSE
4362:
4363: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
4364:
4365: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4366: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4367: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
4368: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TP_ID_LID');
4369: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4365: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4366: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4367: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
4368: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TP_ID_LID');
4369: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4370:
4371: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4372: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TP_ID');
4373: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SR_TP_ID);
4370:
4371: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4372: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TP_ID');
4373: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SR_TP_ID);
4374: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4375:
4376: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4377: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_TYPE');
4378: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_TYPE);
4375:
4376: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4377: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_TYPE');
4378: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_TYPE);
4379: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4380:
4381: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4382: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_ID');
4383: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_ID);
4380:
4381: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4382: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_ID');
4383: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_ID);
4384: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4385:
4386: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4387: ERRBUF := sqlerrm;
4388: RETCODE := MSC_UTIL.G_WARNING;
4382: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_ID');
4383: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_ID);
4384: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4385:
4386: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4387: ERRBUF := sqlerrm;
4388: RETCODE := MSC_UTIL.G_WARNING;
4389:
4390: END IF;
4384: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4385:
4386: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4387: ERRBUF := sqlerrm;
4388: RETCODE := MSC_UTIL.G_WARNING;
4389:
4390: END IF;
4391:
4392: END;
4432: END IF;
4433:
4434: COMMIT;
4435: /* Bug7679044 */
4436: IF lv_tp_stat_stale = MSC_UTIL.SYS_YES OR lv_ins_records > lv_tp_id_count * 0.2 THEN
4437: msc_analyse_tables_pk.analyse_table( 'MSC_TP_ID_LID');
4438: lv_tp_stat_stale := MSC_UTIL.SYS_NO;
4439: lv_tp_id_count := lv_tp_id_count + lv_ins_records;
4440: END IF;
4434: COMMIT;
4435: /* Bug7679044 */
4436: IF lv_tp_stat_stale = MSC_UTIL.SYS_YES OR lv_ins_records > lv_tp_id_count * 0.2 THEN
4437: msc_analyse_tables_pk.analyse_table( 'MSC_TP_ID_LID');
4438: lv_tp_stat_stale := MSC_UTIL.SYS_NO;
4439: lv_tp_id_count := lv_tp_id_count + lv_ins_records;
4440: END IF;
4441:
4442: -- ==== Update msc_tp_id_lid with resource_type ==== SRP Changes
4495: NULL;
4496:
4497: WHEN OTHERS THEN
4498:
4499: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4500: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4501: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
4502: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TRADING_PARTNER_SITES');
4503: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4499: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4500: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4501: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
4502: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TRADING_PARTNER_SITES');
4503: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4504:
4505: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4506: FND_MESSAGE.SET_TOKEN('COLUMN', 'TP_ID');
4507: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.TP_ID);
4504:
4505: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4506: FND_MESSAGE.SET_TOKEN('COLUMN', 'TP_ID');
4507: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.TP_ID);
4508: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4509:
4510: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4511: FND_MESSAGE.SET_TOKEN('COLUMN', 'TP_SITE_CODE');
4512: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.TP_SITE_CODE);
4509:
4510: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4511: FND_MESSAGE.SET_TOKEN('COLUMN', 'TP_SITE_CODE');
4512: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.TP_SITE_CODE);
4513: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4514:
4515: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4516:
4517: ERRBUF := sqlerrm;
4511: FND_MESSAGE.SET_TOKEN('COLUMN', 'TP_SITE_CODE');
4512: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.TP_SITE_CODE);
4513: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4514:
4515: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4516:
4517: ERRBUF := sqlerrm;
4518: RETCODE := MSC_UTIL.G_ERROR;
4519: RAISE;
4514:
4515: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4516:
4517: ERRBUF := sqlerrm;
4518: RETCODE := MSC_UTIL.G_ERROR;
4519: RAISE;
4520:
4521: END;
4522:
4524:
4525: COMMIT;
4526: lv_ins_records := 0;
4527: -- ==== Populate msc_tp_site_id_lid with new Supplier Sites ====
4528: IF MSC_CL_COLLECTION.v_apps_ver < MSC_UTIL.G_APPS115 THEN
4529:
4530: /* For 107 and 110 the vendor_id and vendor
4531: site id can be duplicate, therefore we use
4532: the cursors to handle such exceptions, but for 11i we can use a straight
4554: EXCEPTION
4555:
4556: WHEN DUP_VAL_ON_INDEX THEN
4557:
4558: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
4559:
4560: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4561: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4562: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
4556: WHEN DUP_VAL_ON_INDEX THEN
4557:
4558: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
4559:
4560: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4561: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4562: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
4563: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TP_SITE_ID_LID');
4564: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4560: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4561: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4562: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
4563: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TP_SITE_ID_LID');
4564: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4565:
4566: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4567: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_TYPE');
4568: FND_MESSAGE.SET_TOKEN('VALUE', 1);
4565:
4566: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4567: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_TYPE');
4568: FND_MESSAGE.SET_TOKEN('VALUE', 1);
4569: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4570:
4571: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4572: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TP_SITE_ID');
4573: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SR_TP_SITE_ID);
4570:
4571: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4572: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TP_SITE_ID');
4573: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SR_TP_SITE_ID);
4574: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4575:
4576: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4577: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_SITE_ID');
4578: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_SITE_ID);
4575:
4576: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4577: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_SITE_ID');
4578: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_SITE_ID);
4579: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4580:
4581: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4582: ERRBUF := sqlerrm;
4583: RETCODE := MSC_UTIL.G_WARNING;
4577: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_SITE_ID');
4578: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_SITE_ID);
4579: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4580:
4581: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4582: ERRBUF := sqlerrm;
4583: RETCODE := MSC_UTIL.G_WARNING;
4584:
4585: WHEN OTHERS THEN
4579: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4580:
4581: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4582: ERRBUF := sqlerrm;
4583: RETCODE := MSC_UTIL.G_WARNING;
4584:
4585: WHEN OTHERS THEN
4586:
4587: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4583: RETCODE := MSC_UTIL.G_WARNING;
4584:
4585: WHEN OTHERS THEN
4586:
4587: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4588: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4589: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
4590: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TP_SITE_ID_LID');
4591: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4587: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4588: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4589: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
4590: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TP_SITE_ID_LID');
4591: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4592:
4593: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4594: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_TYPE');
4595: FND_MESSAGE.SET_TOKEN('VALUE', 1);
4592:
4593: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4594: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_TYPE');
4595: FND_MESSAGE.SET_TOKEN('VALUE', 1);
4596: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4597:
4598: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4599: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TP_SITE_ID');
4600: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SR_TP_SITE_ID);
4597:
4598: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4599: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TP_SITE_ID');
4600: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SR_TP_SITE_ID);
4601: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4602:
4603: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4604: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_SITE_ID');
4605: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_SITE_ID);
4602:
4603: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4604: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_SITE_ID');
4605: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_SITE_ID);
4606: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4607:
4608: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4609:
4610: ERRBUF := sqlerrm;
4604: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_SITE_ID');
4605: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_SITE_ID);
4606: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4607:
4608: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4609:
4610: ERRBUF := sqlerrm;
4611: RETCODE := MSC_UTIL.G_ERROR;
4612: RAISE;
4607:
4608: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4609:
4610: ERRBUF := sqlerrm;
4611: RETCODE := MSC_UTIL.G_ERROR;
4612: RAISE;
4613:
4614: END;
4615:
4655: END IF;
4656:
4657: COMMIT;
4658: /* Bug7679044 */
4659: IF lv_tp_site_stat_stale = MSC_UTIL.SYS_YES OR lv_ins_records > lv_tp_site_id_count * 0.2 THEN
4660: msc_analyse_tables_pk.analyse_table( 'MSC_TP_SITE_ID_LID');
4661: lv_tp_site_stat_stale := MSC_UTIL.SYS_NO;
4662: lv_tp_site_id_count := lv_tp_site_id_count + lv_ins_records;
4663: END IF;
4657: COMMIT;
4658: /* Bug7679044 */
4659: IF lv_tp_site_stat_stale = MSC_UTIL.SYS_YES OR lv_ins_records > lv_tp_site_id_count * 0.2 THEN
4660: msc_analyse_tables_pk.analyse_table( 'MSC_TP_SITE_ID_LID');
4661: lv_tp_site_stat_stale := MSC_UTIL.SYS_NO;
4662: lv_tp_site_id_count := lv_tp_site_id_count + lv_ins_records;
4663: END IF;
4664:
4665: --========== CUSTOMER SITE ==========
4704: NULL;
4705:
4706: WHEN OTHERS THEN
4707:
4708: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4709: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4710: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
4711: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TRADING_PARTNER_SITES');
4712: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4708: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4709: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4710: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
4711: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TRADING_PARTNER_SITES');
4712: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4713:
4714: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4715: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_TYPE');
4716: FND_MESSAGE.SET_TOKEN('VALUE', 1);
4713:
4714: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4715: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_TYPE');
4716: FND_MESSAGE.SET_TOKEN('VALUE', 1);
4717: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4718:
4719: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4720: FND_MESSAGE.SET_TOKEN('COLUMN', 'TP_ID');
4721: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.TP_ID);
4718:
4719: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4720: FND_MESSAGE.SET_TOKEN('COLUMN', 'TP_ID');
4721: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.TP_ID);
4722: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4723:
4724: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4725: FND_MESSAGE.SET_TOKEN('COLUMN', 'TP_SITE_CODE');
4726: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.TP_SITE_CODE);
4723:
4724: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4725: FND_MESSAGE.SET_TOKEN('COLUMN', 'TP_SITE_CODE');
4726: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.TP_SITE_CODE);
4727: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4728:
4729: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4730: FND_MESSAGE.SET_TOKEN('COLUMN', 'LOCATION');
4731: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.LOCATION);
4728:
4729: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4730: FND_MESSAGE.SET_TOKEN('COLUMN', 'LOCATION');
4731: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.LOCATION);
4732: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4733:
4734: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4735:
4736: ERRBUF := sqlerrm;
4730: FND_MESSAGE.SET_TOKEN('COLUMN', 'LOCATION');
4731: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.LOCATION);
4732: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4733:
4734: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4735:
4736: ERRBUF := sqlerrm;
4737: RETCODE := MSC_UTIL.G_ERROR;
4738: RAISE;
4733:
4734: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4735:
4736: ERRBUF := sqlerrm;
4737: RETCODE := MSC_UTIL.G_ERROR;
4738: RAISE;
4739:
4740: END;
4741:
4744:
4745: COMMIT;
4746:
4747: lv_ins_records := 0;
4748: IF MSC_CL_COLLECTION.v_apps_ver < MSC_UTIL.G_APPS115 THEN
4749:
4750: /* For 107 and 110 the vendor_id and vendor
4751: site id can be duplicate, therefore we use
4752: the cursors to handle such exceptions, but for 11i we can use a straight
4775: EXCEPTION
4776:
4777: WHEN DUP_VAL_ON_INDEX THEN
4778:
4779: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
4780:
4781: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4782: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4783: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
4777: WHEN DUP_VAL_ON_INDEX THEN
4778:
4779: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
4780:
4781: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4782: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4783: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
4784: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TP_SITE_ID_LID');
4785: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4781: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4782: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4783: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
4784: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TP_SITE_ID_LID');
4785: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4786:
4787: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4788: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_TYPE');
4789: FND_MESSAGE.SET_TOKEN('VALUE', 2);
4786:
4787: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4788: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_TYPE');
4789: FND_MESSAGE.SET_TOKEN('VALUE', 2);
4790: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4791:
4792: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4793: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TP_SITE_ID');
4794: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SR_TP_SITE_ID);
4791:
4792: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4793: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TP_SITE_ID');
4794: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SR_TP_SITE_ID);
4795: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4796:
4797: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4798: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_SITE_ID');
4799: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_SITE_ID);
4796:
4797: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4798: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_SITE_ID');
4799: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_SITE_ID);
4800: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4801:
4802: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4803: ERRBUF := sqlerrm;
4804: RETCODE := MSC_UTIL.G_WARNING;
4798: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_SITE_ID');
4799: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_SITE_ID);
4800: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4801:
4802: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4803: ERRBUF := sqlerrm;
4804: RETCODE := MSC_UTIL.G_WARNING;
4805:
4806: WHEN OTHERS THEN
4800: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4801:
4802: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4803: ERRBUF := sqlerrm;
4804: RETCODE := MSC_UTIL.G_WARNING;
4805:
4806: WHEN OTHERS THEN
4807:
4808: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4804: RETCODE := MSC_UTIL.G_WARNING;
4805:
4806: WHEN OTHERS THEN
4807:
4808: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4809: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4810: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
4811: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TP_SITE_ID_LID');
4812: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4808: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4809: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4810: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
4811: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TP_SITE_ID_LID');
4812: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4813:
4814: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4815: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_TYPE');
4816: FND_MESSAGE.SET_TOKEN('VALUE', 2);
4813:
4814: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4815: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_TYPE');
4816: FND_MESSAGE.SET_TOKEN('VALUE', 2);
4817: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4818:
4819: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4820: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TP_SITE_ID');
4821: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SR_TP_SITE_ID);
4818:
4819: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4820: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TP_SITE_ID');
4821: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SR_TP_SITE_ID);
4822: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4823:
4824: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4825: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_SITE_ID');
4826: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_SITE_ID);
4823:
4824: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4825: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_SITE_ID');
4826: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_SITE_ID);
4827: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4828:
4829: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4830:
4831: ERRBUF := sqlerrm;
4825: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_SITE_ID');
4826: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_SITE_ID);
4827: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4828:
4829: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4830:
4831: ERRBUF := sqlerrm;
4832: RETCODE := MSC_UTIL.G_ERROR;
4833: RAISE;
4828:
4829: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4830:
4831: ERRBUF := sqlerrm;
4832: RETCODE := MSC_UTIL.G_ERROR;
4833: RAISE;
4834:
4835: END;
4836:
4879: END IF;
4880:
4881: COMMIT;
4882: /* Bug7679044 */
4883: IF lv_tp_site_stat_stale = MSC_UTIL.SYS_YES OR lv_ins_records > lv_tp_site_id_count * 0.2 THEN
4884: msc_analyse_tables_pk.analyse_table( 'MSC_TP_SITE_ID_LID');
4885: lv_tp_site_stat_stale := MSC_UTIL.SYS_NO;
4886: lv_tp_site_id_count := lv_tp_site_id_count + lv_ins_records;
4887: END IF;
4881: COMMIT;
4882: /* Bug7679044 */
4883: IF lv_tp_site_stat_stale = MSC_UTIL.SYS_YES OR lv_ins_records > lv_tp_site_id_count * 0.2 THEN
4884: msc_analyse_tables_pk.analyse_table( 'MSC_TP_SITE_ID_LID');
4885: lv_tp_site_stat_stale := MSC_UTIL.SYS_NO;
4886: lv_tp_site_id_count := lv_tp_site_id_count + lv_ins_records;
4887: END IF;
4888:
4889: --================ Collect Vendor/Customer
4926: WHEN OTHERS THEN
4927:
4928: IF SQLCODE IN (-01683,-1653,-1654) THEN
4929:
4930: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4931: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4932: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
4933: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TRADING_PARTNERS');
4934: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4930: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4931: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4932: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
4933: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TRADING_PARTNERS');
4934: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4935:
4936: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4937: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_TYPE');
4938: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_TYPE);
4935:
4936: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4937: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_TYPE');
4938: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_TYPE);
4939: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4940:
4941: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4942: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TP_ID');
4943: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SR_TP_ID);
4940:
4941: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4942: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TP_ID');
4943: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SR_TP_ID);
4944: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4945:
4946: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4947:
4948: ERRBUF := sqlerrm;
4942: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TP_ID');
4943: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SR_TP_ID);
4944: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4945:
4946: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4947:
4948: ERRBUF := sqlerrm;
4949: RETCODE := MSC_UTIL.G_ERROR;
4950: RAISE;
4945:
4946: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4947:
4948: ERRBUF := sqlerrm;
4949: RETCODE := MSC_UTIL.G_ERROR;
4950: RAISE;
4951:
4952: ELSE
4953:
4950: RAISE;
4951:
4952: ELSE
4953:
4954: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
4955:
4956: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4957: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4958: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
4952: ELSE
4953:
4954: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
4955:
4956: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4957: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4958: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
4959: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TRADING_PARTNERS');
4960: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4956: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4957: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4958: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
4959: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TRADING_PARTNERS');
4960: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4961:
4962: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4963: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_TYPE');
4964: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_TYPE);
4961:
4962: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4963: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_TYPE');
4964: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_TYPE);
4965: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4966:
4967: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4968: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TP_ID');
4969: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SR_TP_ID);
4966:
4967: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4968: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TP_ID');
4969: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SR_TP_ID);
4970: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4971:
4972: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4973: ERRBUF := sqlerrm;
4974: RETCODE := MSC_UTIL.G_WARNING;
4968: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TP_ID');
4969: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SR_TP_ID);
4970: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4971:
4972: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4973: ERRBUF := sqlerrm;
4974: RETCODE := MSC_UTIL.G_WARNING;
4975:
4976: END IF;
4970: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4971:
4972: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4973: ERRBUF := sqlerrm;
4974: RETCODE := MSC_UTIL.G_WARNING;
4975:
4976: END IF;
4977:
4978: END;
4996: mtps.CITY = c_rec.CITY,
4997: mtps.STATE = c_rec.STATE,
4998: mtps.COUNTRY = c_rec.COUNTRY,
4999: mtps.SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID,
5000: mtps.DELETED_FLAG= MSC_UTIL.SYS_NO,
5001: mtps.REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
5002: mtps.SHIPPING_CONTROL=c_rec.SHIPPING_CONTROL,
5003: mtps.LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
5004: mtps.LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
5011: WHEN OTHERS THEN
5012:
5013: IF SQLCODE IN (-1653,-1654) THEN
5014:
5015: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
5016: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
5017: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
5018: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TRADING_PARTNER_SITES');
5019: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
5015: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
5016: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
5017: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
5018: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TRADING_PARTNER_SITES');
5019: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
5020:
5021: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
5022: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_TYPE');
5023: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_TYPE);
5020:
5021: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
5022: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_TYPE');
5023: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_TYPE);
5024: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
5025:
5026: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
5027: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TP_SITE_ID');
5028: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SR_TP_SITE_ID);
5025:
5026: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
5027: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TP_SITE_ID');
5028: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SR_TP_SITE_ID);
5029: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
5030:
5031: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
5032:
5033: ERRBUF := sqlerrm;
5027: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TP_SITE_ID');
5028: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SR_TP_SITE_ID);
5029: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
5030:
5031: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
5032:
5033: ERRBUF := sqlerrm;
5034: RETCODE := MSC_UTIL.G_ERROR;
5035: RAISE;
5030:
5031: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
5032:
5033: ERRBUF := sqlerrm;
5034: RETCODE := MSC_UTIL.G_ERROR;
5035: RAISE;
5036:
5037: ELSE
5038:
5035: RAISE;
5036:
5037: ELSE
5038:
5039: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
5040:
5041: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
5042: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
5043: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
5037: ELSE
5038:
5039: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
5040:
5041: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
5042: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
5043: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
5044: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TRADING_PARTNER_SITES');
5045: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
5041: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
5042: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
5043: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
5044: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TRADING_PARTNER_SITES');
5045: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
5046:
5047: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
5048: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_TYPE');
5049: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_TYPE);
5046:
5047: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
5048: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_TYPE');
5049: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_TYPE);
5050: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
5051:
5052: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
5053: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TP_SITE_ID');
5054: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SR_TP_SITE_ID);
5051:
5052: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
5053: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TP_SITE_ID');
5054: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SR_TP_SITE_ID);
5055: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
5056:
5057: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
5058: ERRBUF := sqlerrm;
5059: RETCODE := MSC_UTIL.G_WARNING;
5053: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TP_SITE_ID');
5054: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SR_TP_SITE_ID);
5055: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
5056:
5057: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
5058: ERRBUF := sqlerrm;
5059: RETCODE := MSC_UTIL.G_WARNING;
5060:
5061: END IF;
5055: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
5056:
5057: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
5058: ERRBUF := sqlerrm;
5059: RETCODE := MSC_UTIL.G_WARNING;
5060:
5061: END IF;
5062:
5063: END;
5066:
5067: COMMIT;
5068:
5069: /* analyse the key mapping tables */
5070: IF lv_tp_stat_stale = MSC_UTIL.SYS_YES THEN
5071: msc_analyse_tables_pk.analyse_table( 'MSC_TP_ID_LID');
5072: END IF;
5073: IF lv_tp_site_stat_stale = MSC_UTIL.SYS_YES THEN
5074: msc_analyse_tables_pk.analyse_table( 'MSC_TP_SITE_ID_LID');
5069: /* analyse the key mapping tables */
5070: IF lv_tp_stat_stale = MSC_UTIL.SYS_YES THEN
5071: msc_analyse_tables_pk.analyse_table( 'MSC_TP_ID_LID');
5072: END IF;
5073: IF lv_tp_site_stat_stale = MSC_UTIL.SYS_YES THEN
5074: msc_analyse_tables_pk.analyse_table( 'MSC_TP_SITE_ID_LID');
5075: END IF;
5076:
5077: EXCEPTION
5077: EXCEPTION
5078:
5079: WHEN OTHERS THEN
5080:
5081: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
5082:
5083: ERRBUF := sqlerrm;
5084: RETCODE := MSC_UTIL.G_ERROR;
5085: RAISE;
5080:
5081: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
5082:
5083: ERRBUF := sqlerrm;
5084: RETCODE := MSC_UTIL.G_ERROR;
5085: RAISE;
5086:
5087: END GENERATE_TRADING_PARTNER_KEYS;
5088: