[Home] [Help]
1: PACKAGE BODY MSC_CL_SETUP_ODS_LOAD AS -- specification
2: /* $Header: MSCLSTPB.pls 120.25.12020000.3 2012/09/04 12:08:36 neelredd 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
475: SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
575: CALENDAR_START_DATE= lb_CALENDAR_START_DATE(j),
576: CALENDAR_END_DATE= lb_CALENDAR_END_DATE(j),
577: DESCRIPTION= lb_DESCRIPTION(j),
578: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
579: Deleted_Flag= MSC_UTIL.SYS_NO,
580: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
581: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
582: WHERE CALENDAR_DATE= lb_CALENDAR_DATE(j)
583: AND CALENDAR_CODE= lb_CALENDAR_CODE(j)
601: lb_DESCRIPTION(j),
602: lb_EXCEPTION_SET_ID(j),
603: lb_SR_INSTANCE_ID(j),
604: MSC_CL_COLLECTION.v_last_collection_id,
605: MSC_UTIL.SYS_NO,
606: MSC_CL_COLLECTION.v_current_date,
607: MSC_CL_COLLECTION.v_current_user,
608: MSC_CL_COLLECTION.v_current_date,
609: MSC_CL_COLLECTION.v_current_user ;
616:
617:
618: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
619:
620: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
621: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
622: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
623: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CALENDAR_DATES');
624: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
620: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
621: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
622: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
623: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CALENDAR_DATES');
624: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
625:
626: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
627: RAISE;
628:
622: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
623: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CALENDAR_DATES');
624: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
625:
626: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
627: RAISE;
628:
629: ELSE
630: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
626: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
627: RAISE;
628:
629: ELSE
630: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
631:
632: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
633: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
634: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
628:
629: ELSE
630: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
631:
632: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
633: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
634: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
635: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CALENDAR_DATES');
636: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
632: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
633: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
634: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
635: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CALENDAR_DATES');
636: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
637:
638: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
639: FND_MESSAGE.SET_TOKEN('COLUMN', 'EXCEPTION_SET_ID');
640: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( lb_EXCEPTION_SET_ID(j)));
637:
638: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
639: FND_MESSAGE.SET_TOKEN('COLUMN', 'EXCEPTION_SET_ID');
640: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( lb_EXCEPTION_SET_ID(j)));
641: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
642:
643: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
644: FND_MESSAGE.SET_TOKEN('COLUMN', 'CALENDAR_DATE');
645: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(lb_CALENDAR_DATE(j)));
642:
643: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
644: FND_MESSAGE.SET_TOKEN('COLUMN', 'CALENDAR_DATE');
645: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(lb_CALENDAR_DATE(j)));
646: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
647:
648: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
649: FND_MESSAGE.SET_TOKEN('COLUMN', 'CALENDAR_CODE');
650: FND_MESSAGE.SET_TOKEN('VALUE', lb_CALENDAR_CODE(j));
647:
648: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
649: FND_MESSAGE.SET_TOKEN('COLUMN', 'CALENDAR_CODE');
650: FND_MESSAGE.SET_TOKEN('VALUE', lb_CALENDAR_CODE(j));
651: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
652:
653: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
654: END IF;
655:
649: FND_MESSAGE.SET_TOKEN('COLUMN', 'CALENDAR_CODE');
650: FND_MESSAGE.SET_TOKEN('VALUE', lb_CALENDAR_CODE(j));
651: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
652:
653: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
654: END IF;
655:
656: END;
657:
662: COMMIT;
663:
664:
665: DELETE MSC_CALENDAR_DATES
666: WHERE DELETED_FLAG= MSC_UTIL.SYS_YES
667: AND SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
668: AND exception_set_id = -1;
669:
670: COMMIT;
683:
684: FOR c_rec IN c5 LOOP
685:
686: BEGIN
687: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'cal code='|| c_rec.CALENDAR_CODE);
688: 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
689:
690: IF MSC_CL_COLLECTION.v_instance_type <> MSC_UTIL.G_INS_OTHER THEN
691:
686: BEGIN
687: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'cal code='|| c_rec.CALENDAR_CODE);
688: 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
689:
690: IF MSC_CL_COLLECTION.v_instance_type <> MSC_UTIL.G_INS_OTHER THEN
691:
692: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'cal code='|| c_rec.CALENDAR_CODE);
693:
694: INSERT INTO MSC_CALENDARS
688: 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
689:
690: IF MSC_CL_COLLECTION.v_instance_type <> MSC_UTIL.G_INS_OTHER THEN
691:
692: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'cal code='|| c_rec.CALENDAR_CODE);
693:
694: INSERT INTO MSC_CALENDARS
695: ( CALENDAR_CODE,
696: DESCRIPTION,
721: WHEN OTHERS THEN
722:
723: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
724:
725: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
726: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
727: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
728: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CALENDARS');
729: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
725: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
726: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
727: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
728: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CALENDARS');
729: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
730: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
731: RAISE;
732:
733: ELSE
726: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
727: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
728: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CALENDARS');
729: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
730: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
731: RAISE;
732:
733: ELSE
734: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
730: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
731: RAISE;
732:
733: ELSE
734: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
735:
736: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
737: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
738: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
732:
733: ELSE
734: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
735:
736: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
737: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
738: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
739: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CALENDARS');
740: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
736: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
737: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
738: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
739: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CALENDARS');
740: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
741:
742: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
743: FND_MESSAGE.SET_TOKEN('COLUMN', 'CALENDAR_CODE');
744: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CALENDAR_CODE);
741:
742: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
743: FND_MESSAGE.SET_TOKEN('COLUMN', 'CALENDAR_CODE');
744: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CALENDAR_CODE);
745: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
746: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
747: END IF;
748: END;
749: END LOOP;
742: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
743: FND_MESSAGE.SET_TOKEN('COLUMN', 'CALENDAR_CODE');
744: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CALENDAR_CODE);
745: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
746: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
747: END IF;
748: END;
749: END LOOP;
750:
764: WHEN OTHERS THEN
765:
766: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
767:
768: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
769: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
770: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
771: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CALENDARS');
772: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
768: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
769: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
770: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
771: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CALENDARS');
772: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
773: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
774: RAISE;
775:
776: ELSE
769: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
770: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
771: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CALENDARS');
772: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
773: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
774: RAISE;
775:
776: ELSE
777: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
773: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
774: RAISE;
775:
776: ELSE
777: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
778:
779: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
780: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
781: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
775:
776: ELSE
777: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
778:
779: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
780: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
781: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
782: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CALENDARS');
783: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
779: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
780: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
781: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
782: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CALENDARS');
783: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
784: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
785: FND_MESSAGE.SET_TOKEN('COLUMN', 'CALENDAR_CODE');
786: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CALENDAR_CODE);
787: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
783: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
784: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
785: FND_MESSAGE.SET_TOKEN('COLUMN', 'CALENDAR_CODE');
786: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CALENDAR_CODE);
787: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
788: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
789: END IF;
790: END;
791: END LOOP;
784: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
785: FND_MESSAGE.SET_TOKEN('COLUMN', 'CALENDAR_CODE');
786: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CALENDAR_CODE);
787: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
788: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
789: END IF;
790: END;
791: END LOOP;
792:
845: WHEN OTHERS THEN
846:
847: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
848:
849: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
850: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
851: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
852: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CALENDAR_ASSIGNMENTS');
853: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
849: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
850: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
851: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
852: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CALENDAR_ASSIGNMENTS');
853: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
854: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
855: RAISE;
856:
857: ELSE
850: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
851: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
852: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CALENDAR_ASSIGNMENTS');
853: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
854: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
855: RAISE;
856:
857: ELSE
858: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
854: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
855: RAISE;
856:
857: ELSE
858: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
859:
860: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
861: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
862: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
856:
857: ELSE
858: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
859:
860: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
861: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
862: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
863: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CALENDAR_ASSIGNMENTS');
864: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
860: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
861: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
862: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
863: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CALENDAR_ASSIGNMENTS');
864: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
865: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
866: FND_MESSAGE.SET_TOKEN('COLUMN', 'CALENDAR_CODE');
867: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CALENDAR_CODE);
868: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
864: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
865: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
866: FND_MESSAGE.SET_TOKEN('COLUMN', 'CALENDAR_CODE');
867: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CALENDAR_CODE);
868: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
869: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
870: END IF;
871: END;
872: END LOOP;
865: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
866: FND_MESSAGE.SET_TOKEN('COLUMN', 'CALENDAR_CODE');
867: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CALENDAR_CODE);
868: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
869: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
870: END IF;
871: END;
872: END LOOP;
873:
944: WHEN OTHERS THEN
945:
946: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
947:
948: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
949: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
950: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
951: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_PERIOD_START_DATES');
952: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
948: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
949: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
950: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
951: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_PERIOD_START_DATES');
952: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
953:
954: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
955: RAISE;
956:
950: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
951: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_PERIOD_START_DATES');
952: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
953:
954: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
955: RAISE;
956:
957: ELSE
958: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
954: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
955: RAISE;
956:
957: ELSE
958: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
959:
960: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
961: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
962: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
956:
957: ELSE
958: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
959:
960: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
961: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
962: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
963: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_PERIOD_START_DATES');
964: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
960: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
961: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
962: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
963: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_PERIOD_START_DATES');
964: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
965:
966: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
967: FND_MESSAGE.SET_TOKEN('COLUMN', 'EXCEPTION_SET_ID');
968: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.EXCEPTION_SET_ID));
965:
966: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
967: FND_MESSAGE.SET_TOKEN('COLUMN', 'EXCEPTION_SET_ID');
968: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.EXCEPTION_SET_ID));
969: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
970:
971: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
972: FND_MESSAGE.SET_TOKEN('COLUMN', 'PERIOD_START_DATE');
973: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.PERIOD_START_DATE));
970:
971: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
972: FND_MESSAGE.SET_TOKEN('COLUMN', 'PERIOD_START_DATE');
973: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.PERIOD_START_DATE));
974: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
975:
976: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
977: FND_MESSAGE.SET_TOKEN('COLUMN', 'CALENDAR_CODE');
978: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CALENDAR_CODE);
975:
976: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
977: FND_MESSAGE.SET_TOKEN('COLUMN', 'CALENDAR_CODE');
978: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CALENDAR_CODE);
979: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
980:
981: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
982:
983: END IF;
977: FND_MESSAGE.SET_TOKEN('COLUMN', 'CALENDAR_CODE');
978: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CALENDAR_CODE);
979: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
980:
981: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
982:
983: END IF;
984: END;
985:
1043: EXCEPTION
1044: WHEN OTHERS THEN
1045:
1046: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
1047: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1048: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1049: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
1050: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CAL_YEAR_START_DATES');
1051: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1047: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1048: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1049: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
1050: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CAL_YEAR_START_DATES');
1051: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1052:
1053: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1054: RAISE;
1055:
1049: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
1050: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CAL_YEAR_START_DATES');
1051: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1052:
1053: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1054: RAISE;
1055:
1056: ELSE
1057: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1053: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1054: RAISE;
1055:
1056: ELSE
1057: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1058:
1059: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1060: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1061: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
1055:
1056: ELSE
1057: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1058:
1059: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1060: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1061: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
1062: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CAL_YEAR_START_DATES');
1063: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1059: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1060: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1061: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
1062: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CAL_YEAR_START_DATES');
1063: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1064:
1065: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1066: FND_MESSAGE.SET_TOKEN('COLUMN', 'EXCEPTION_SET_ID');
1067: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.EXCEPTION_SET_ID));
1064:
1065: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1066: FND_MESSAGE.SET_TOKEN('COLUMN', 'EXCEPTION_SET_ID');
1067: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.EXCEPTION_SET_ID));
1068: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1069:
1070: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1071: FND_MESSAGE.SET_TOKEN('COLUMN', 'YEAR_START_DATE');
1072: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.YEAR_START_DATE));
1069:
1070: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1071: FND_MESSAGE.SET_TOKEN('COLUMN', 'YEAR_START_DATE');
1072: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.YEAR_START_DATE));
1073: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1074:
1075: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1076: FND_MESSAGE.SET_TOKEN('COLUMN', 'CALENDAR_CODE');
1077: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CALENDAR_CODE);
1074:
1075: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1076: FND_MESSAGE.SET_TOKEN('COLUMN', 'CALENDAR_CODE');
1077: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CALENDAR_CODE);
1078: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1079:
1080: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1081: END IF;
1082:
1076: FND_MESSAGE.SET_TOKEN('COLUMN', 'CALENDAR_CODE');
1077: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CALENDAR_CODE);
1078: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1079:
1080: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1081: END IF;
1082:
1083: END;
1084:
1150: EXCEPTION
1151: WHEN OTHERS THEN
1152:
1153: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
1154: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1155: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1156: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
1157: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CAL_WEEK_START_DATES');
1158: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1154: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1155: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1156: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
1157: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CAL_WEEK_START_DATES');
1158: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1159:
1160: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1161: RAISE;
1162:
1156: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
1157: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CAL_WEEK_START_DATES');
1158: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1159:
1160: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1161: RAISE;
1162:
1163: ELSE
1164: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1160: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1161: RAISE;
1162:
1163: ELSE
1164: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1165:
1166: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1167: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1168: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
1162:
1163: ELSE
1164: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1165:
1166: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1167: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1168: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
1169: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CAL_WEEK_START_DATES');
1170: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1166: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1167: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1168: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
1169: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CAL_WEEK_START_DATES');
1170: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1171:
1172: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1173: FND_MESSAGE.SET_TOKEN('COLUMN', 'EXCEPTION_SET_ID');
1174: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.EXCEPTION_SET_ID));
1171:
1172: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1173: FND_MESSAGE.SET_TOKEN('COLUMN', 'EXCEPTION_SET_ID');
1174: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.EXCEPTION_SET_ID));
1175: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1176:
1177: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1178: FND_MESSAGE.SET_TOKEN('COLUMN', 'WEEK_START_DATE');
1179: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.WEEK_START_DATE));
1176:
1177: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1178: FND_MESSAGE.SET_TOKEN('COLUMN', 'WEEK_START_DATE');
1179: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.WEEK_START_DATE));
1180: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1181:
1182: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1183: FND_MESSAGE.SET_TOKEN('COLUMN', 'CALENDAR_CODE');
1184: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CALENDAR_CODE);
1181:
1182: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1183: FND_MESSAGE.SET_TOKEN('COLUMN', 'CALENDAR_CODE');
1184: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CALENDAR_CODE);
1185: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1186:
1187: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1188: END IF;
1189: END;
1183: FND_MESSAGE.SET_TOKEN('COLUMN', 'CALENDAR_CODE');
1184: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CALENDAR_CODE);
1185: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1186:
1187: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1188: END IF;
1189: END;
1190:
1191: END LOOP;
1219: mscs.DESCRIPTION,
1220: mscs.SR_INSTANCE_ID
1221: FROM MSC_ST_CALENDAR_SHIFTS mscs
1222: WHERE mscs.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1223: AND nvl(mscs.process_flag, -99) <> MSC_UTIL.G_ERROR;
1224:
1225: CURSOR c7 IS
1226: SELECT
1227: mssd.CALENDAR_CODE,
1290: ex_calc_res_avail EXCEPTION;
1291:
1292: BEGIN
1293:
1294: if ((MSC_CL_COLLECTION.v_is_partial_refresh AND MSC_CL_COLLECTION.v_coll_prec.calendar_flag = MSC_UTIL.SYS_YES) OR
1295: MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_incremental_refresh) then
1296:
1297: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
1298:
1368: EXCEPTION
1369: WHEN OTHERS THEN
1370:
1371: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
1372: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1373: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1374: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1375: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_SHIFTS');
1376: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1372: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1373: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1374: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1375: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_SHIFTS');
1376: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1377:
1378: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1379: RAISE;
1380:
1374: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1375: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_SHIFTS');
1376: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1377:
1378: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1379: RAISE;
1380:
1381: ELSE
1382: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1378: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1379: RAISE;
1380:
1381: ELSE
1382: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1383:
1384: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1385: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1386: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1380:
1381: ELSE
1382: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1383:
1384: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1385: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1386: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1387: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_SHIFTS');
1388: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1384: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1385: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1386: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1387: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_SHIFTS');
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', 'DEPARTMENT_ID');
1392: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.DEPARTMENT_ID));
1389:
1390: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1391: FND_MESSAGE.SET_TOKEN('COLUMN', 'DEPARTMENT_ID');
1392: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.DEPARTMENT_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', 'RESOURCE_ID');
1397: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.RESOURCE_ID));
1394:
1395: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1396: FND_MESSAGE.SET_TOKEN('COLUMN', 'RESOURCE_ID');
1397: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.RESOURCE_ID));
1398: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1399:
1400: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1401: FND_MESSAGE.SET_TOKEN('COLUMN', 'SHIFT_NUM');
1402: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SHIFT_NUM));
1399:
1400: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1401: FND_MESSAGE.SET_TOKEN('COLUMN', 'SHIFT_NUM');
1402: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SHIFT_NUM));
1403: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1404:
1405: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1406: END IF;
1407:
1401: FND_MESSAGE.SET_TOKEN('COLUMN', 'SHIFT_NUM');
1402: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SHIFT_NUM));
1403: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1404:
1405: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1406: END IF;
1407:
1408: END;
1409:
1473:
1474: WHEN OTHERS THEN
1475:
1476: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
1477: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1478: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1479: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1480: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CALENDAR_SHIFTS');
1481: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1477: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1478: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1479: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1480: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CALENDAR_SHIFTS');
1481: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1482:
1483: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1484: RAISE;
1485:
1479: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1480: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CALENDAR_SHIFTS');
1481: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1482:
1483: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1484: RAISE;
1485:
1486: ELSE
1487: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1483: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1484: RAISE;
1485:
1486: ELSE
1487: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1488:
1489: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1490: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1491: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1485:
1486: ELSE
1487: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1488:
1489: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1490: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1491: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1492: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CALENDAR_SHIFTS');
1493: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1489: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1490: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1491: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1492: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CALENDAR_SHIFTS');
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', 'CALENDAR_CODE');
1497: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CALENDAR_CODE);
1494:
1495: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1496: FND_MESSAGE.SET_TOKEN('COLUMN', 'CALENDAR_CODE');
1497: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CALENDAR_CODE);
1498: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1499:
1500: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1501: FND_MESSAGE.SET_TOKEN('COLUMN', 'SHIFT_NUM');
1502: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SHIFT_NUM));
1499:
1500: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1501: FND_MESSAGE.SET_TOKEN('COLUMN', 'SHIFT_NUM');
1502: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SHIFT_NUM));
1503: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1504:
1505: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1506: END IF;
1507:
1501: FND_MESSAGE.SET_TOKEN('COLUMN', 'SHIFT_NUM');
1502: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SHIFT_NUM));
1503: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1504:
1505: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1506: END IF;
1507:
1508: END;
1509:
1584: EXCEPTION
1585: WHEN OTHERS THEN
1586:
1587: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
1588: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1589: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1590: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1591: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SHIFT_DATES');
1592: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1588: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1589: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1590: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1591: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SHIFT_DATES');
1592: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1593:
1594: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1595: RAISE;
1596:
1590: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1591: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SHIFT_DATES');
1592: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1593:
1594: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1595: RAISE;
1596:
1597: ELSE
1598: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1594: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1595: RAISE;
1596:
1597: ELSE
1598: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1599:
1600: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1601: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1602: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1596:
1597: ELSE
1598: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1599:
1600: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1601: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1602: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1603: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SHIFT_DATES');
1604: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1600: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1601: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1602: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1603: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SHIFT_DATES');
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', 'CALENDAR_CODE');
1608: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CALENDAR_CODE);
1605:
1606: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1607: FND_MESSAGE.SET_TOKEN('COLUMN', 'CALENDAR_CODE');
1608: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CALENDAR_CODE);
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', 'EXCEPTION_SET_ID');
1613: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.EXCEPTION_SET_ID));
1610:
1611: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1612: FND_MESSAGE.SET_TOKEN('COLUMN', 'EXCEPTION_SET_ID');
1613: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.EXCEPTION_SET_ID));
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_DATE');
1618: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SHIFT_DATE));
1615:
1616: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1617: FND_MESSAGE.SET_TOKEN('COLUMN', 'SHIFT_DATE');
1618: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SHIFT_DATE));
1619: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1620:
1621: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1622: FND_MESSAGE.SET_TOKEN('COLUMN', 'SHIFT_NUM');
1623: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SHIFT_NUM));
1620:
1621: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1622: FND_MESSAGE.SET_TOKEN('COLUMN', 'SHIFT_NUM');
1623: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SHIFT_NUM));
1624: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1625:
1626: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1627: END IF;
1628:
1622: FND_MESSAGE.SET_TOKEN('COLUMN', 'SHIFT_NUM');
1623: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SHIFT_NUM));
1624: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1625:
1626: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1627: END IF;
1628:
1629: END;
1630:
1637: FOR c_rec IN c8 LOOP
1638:
1639: BEGIN
1640:
1641: IF MSC_CL_COLLECTION.v_is_incremental_refresh AND c_rec.DELETED_FLAG= MSC_UTIL.SYS_YES THEN
1642:
1643: -- set SR_INSTANCE_ID to negative to indicate a SOFT delete
1644:
1645: DELETE MSC_RESOURCE_CHANGES
1646: WHERE DEPARTMENT_ID= c_rec.DEPARTMENT_ID
1647: AND RESOURCE_ID= c_rec.RESOURCE_ID
1648: AND SHIFT_NUM= c_rec.SHIFT_NUM
1649: AND FROM_DATE= c_rec.FROM_DATE
1650: AND NVL(TO_DATE,MSC_UTIL.NULL_DATE)= NVL(c_rec.TO_DATE,MSC_UTIL.NULL_DATE)
1651: AND NVL(FROM_TIME,MSC_UTIL.NULL_VALUE)= NVL(c_rec.FROM_TIME,MSC_UTIL.NULL_VALUE)
1652: AND NVL(TO_TIME,MSC_UTIL.NULL_VALUE)= NVL(c_rec.TO_TIME,MSC_UTIL.NULL_VALUE)
1653: AND SIMULATION_SET= c_rec.SIMULATION_SET
1654: AND ACTION_TYPE= c_rec.ACTION_TYPE
1647: AND RESOURCE_ID= c_rec.RESOURCE_ID
1648: AND SHIFT_NUM= c_rec.SHIFT_NUM
1649: AND FROM_DATE= c_rec.FROM_DATE
1650: AND NVL(TO_DATE,MSC_UTIL.NULL_DATE)= NVL(c_rec.TO_DATE,MSC_UTIL.NULL_DATE)
1651: AND NVL(FROM_TIME,MSC_UTIL.NULL_VALUE)= NVL(c_rec.FROM_TIME,MSC_UTIL.NULL_VALUE)
1652: AND NVL(TO_TIME,MSC_UTIL.NULL_VALUE)= NVL(c_rec.TO_TIME,MSC_UTIL.NULL_VALUE)
1653: AND SIMULATION_SET= c_rec.SIMULATION_SET
1654: AND ACTION_TYPE= c_rec.ACTION_TYPE
1655: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
1648: AND SHIFT_NUM= c_rec.SHIFT_NUM
1649: AND FROM_DATE= c_rec.FROM_DATE
1650: AND NVL(TO_DATE,MSC_UTIL.NULL_DATE)= NVL(c_rec.TO_DATE,MSC_UTIL.NULL_DATE)
1651: AND NVL(FROM_TIME,MSC_UTIL.NULL_VALUE)= NVL(c_rec.FROM_TIME,MSC_UTIL.NULL_VALUE)
1652: AND NVL(TO_TIME,MSC_UTIL.NULL_VALUE)= NVL(c_rec.TO_TIME,MSC_UTIL.NULL_VALUE)
1653: AND SIMULATION_SET= c_rec.SIMULATION_SET
1654: AND ACTION_TYPE= c_rec.ACTION_TYPE
1655: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
1656:
1704:
1705: WHEN OTHERS THEN
1706:
1707: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
1708: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1709: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1710: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1711: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_CHANGES');
1712: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1708: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1709: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1710: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1711: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_CHANGES');
1712: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1713:
1714: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1715: RAISE;
1716:
1710: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1711: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_CHANGES');
1712: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1713:
1714: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1715: RAISE;
1716:
1717: ELSE
1718: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1714: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1715: RAISE;
1716:
1717: ELSE
1718: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1719:
1720: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1721: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1722: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1716:
1717: ELSE
1718: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1719:
1720: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1721: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1722: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1723: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_CHANGES');
1724: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1720: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1721: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1722: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1723: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_CHANGES');
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', 'DEPARTMENT_ID');
1728: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.DEPARTMENT_ID));
1725:
1726: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1727: FND_MESSAGE.SET_TOKEN('COLUMN', 'DEPARTMENT_ID');
1728: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.DEPARTMENT_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', 'RESOURCE_ID');
1733: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.RESOURCE_ID));
1730:
1731: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1732: FND_MESSAGE.SET_TOKEN('COLUMN', 'RESOURCE_ID');
1733: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.RESOURCE_ID));
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', 'SHIFT_NUM');
1738: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SHIFT_NUM));
1735:
1736: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1737: FND_MESSAGE.SET_TOKEN('COLUMN', 'SHIFT_NUM');
1738: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SHIFT_NUM));
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', 'ACTION_TYPE');
1743: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.ACTION_TYPE));
1740:
1741: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1742: FND_MESSAGE.SET_TOKEN('COLUMN', 'ACTION_TYPE');
1743: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.ACTION_TYPE));
1744: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1745:
1746: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1747: FND_MESSAGE.SET_TOKEN('COLUMN', 'SIMULATION_SET');
1748: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SIMULATION_SET);
1745:
1746: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1747: FND_MESSAGE.SET_TOKEN('COLUMN', 'SIMULATION_SET');
1748: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SIMULATION_SET);
1749: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1750:
1751: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1752: END IF;
1753:
1747: FND_MESSAGE.SET_TOKEN('COLUMN', 'SIMULATION_SET');
1748: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SIMULATION_SET);
1749: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1750:
1751: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1752: END IF;
1753:
1754: END;
1755:
1819: EXCEPTION
1820: WHEN OTHERS THEN
1821:
1822: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
1823: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1824: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1825: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1826: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SHIFT_TIMES');
1827: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1823: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1824: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1825: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1826: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SHIFT_TIMES');
1827: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1828:
1829: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1830: RAISE;
1831:
1825: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1826: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SHIFT_TIMES');
1827: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1828:
1829: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1830: RAISE;
1831:
1832: ELSE
1833: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1829: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1830: RAISE;
1831:
1832: ELSE
1833: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1834:
1835: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1836: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1837: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1831:
1832: ELSE
1833: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1834:
1835: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1836: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1837: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1838: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SHIFT_TIMES');
1839: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1835: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1836: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1837: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1838: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SHIFT_TIMES');
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', 'CALENDAR_CODE');
1843: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CALENDAR_CODE);
1840:
1841: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1842: FND_MESSAGE.SET_TOKEN('COLUMN', 'CALENDAR_CODE');
1843: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CALENDAR_CODE);
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', 'SHIFT_NUM');
1848: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SHIFT_NUM));
1845:
1846: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1847: FND_MESSAGE.SET_TOKEN('COLUMN', 'SHIFT_NUM');
1848: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SHIFT_NUM));
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', 'FROM_TIME');
1853: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.FROM_TIME));
1850:
1851: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1852: FND_MESSAGE.SET_TOKEN('COLUMN', 'FROM_TIME');
1853: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.FROM_TIME));
1854: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1855:
1856: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1857: FND_MESSAGE.SET_TOKEN('COLUMN', 'TO_TIME');
1858: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.TO_TIME));
1855:
1856: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1857: FND_MESSAGE.SET_TOKEN('COLUMN', 'TO_TIME');
1858: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.TO_TIME));
1859: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1860:
1861: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1862: END IF;
1863:
1857: FND_MESSAGE.SET_TOKEN('COLUMN', 'TO_TIME');
1858: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.TO_TIME));
1859: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1860:
1861: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1862: END IF;
1863:
1864: END;
1865:
1928: EXCEPTION
1929: WHEN OTHERS THEN
1930:
1931: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
1932: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1933: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1934: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1935: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SHIFT_EXCEPTIONS');
1936: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1932: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1933: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1934: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1935: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SHIFT_EXCEPTIONS');
1936: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1937:
1938: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1939: RAISE;
1940:
1934: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1935: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SHIFT_EXCEPTIONS');
1936: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1937:
1938: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1939: RAISE;
1940:
1941: ELSE
1942: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1938: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1939: RAISE;
1940:
1941: ELSE
1942: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1943:
1944: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1945: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1946: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1940:
1941: ELSE
1942: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1943:
1944: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1945: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1946: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1947: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SHIFT_EXCEPTIONS');
1948: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1944: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1945: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1946: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1947: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SHIFT_EXCEPTIONS');
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', 'CALENDAR_CODE');
1952: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CALENDAR_CODE);
1949:
1950: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1951: FND_MESSAGE.SET_TOKEN('COLUMN', 'CALENDAR_CODE');
1952: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CALENDAR_CODE);
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_DATE');
1957: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.EXCEPTION_DATE));
1954:
1955: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1956: FND_MESSAGE.SET_TOKEN('COLUMN', 'EXCEPTION_DATE');
1957: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.EXCEPTION_DATE));
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', 'EXCEPTION_SET_ID');
1962: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.EXCEPTION_SET_ID));
1959:
1960: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1961: FND_MESSAGE.SET_TOKEN('COLUMN', 'EXCEPTION_SET_ID');
1962: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.EXCEPTION_SET_ID));
1963: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1964:
1965: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1966: FND_MESSAGE.SET_TOKEN('COLUMN', 'SHIFT_NUM');
1967: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SHIFT_NUM));
1964:
1965: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1966: FND_MESSAGE.SET_TOKEN('COLUMN', 'SHIFT_NUM');
1967: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SHIFT_NUM));
1968: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1969:
1970: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1971: END IF;
1972: END;
1966: FND_MESSAGE.SET_TOKEN('COLUMN', 'SHIFT_NUM');
1967: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SHIFT_NUM));
1968: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1969:
1970: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1971: END IF;
1972: END;
1973:
1974: END LOOP;
1975:
1976: COMMIT;
1977: end if;
1978:
1979: if ((MSC_CL_COLLECTION.v_is_partial_refresh AND MSC_CL_COLLECTION.v_coll_prec.bom_flag = MSC_UTIL.SYS_YES) OR
1980: MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_incremental_refresh) then
1981:
1982: FND_MESSAGE.SET_NAME('MSC', 'MSC_DP_TASK_START');
1983: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'MSC_CL_BOM_ODS_LOAD.LOAD_RESOURCE');
1980: MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_incremental_refresh) then
1981:
1982: FND_MESSAGE.SET_NAME('MSC', 'MSC_DP_TASK_START');
1983: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'MSC_CL_BOM_ODS_LOAD.LOAD_RESOURCE');
1984: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1985: if (MSC_CL_COLLECTION.v_bom_refresh_type <> 3) then
1986: MSC_CL_BOM_ODS_LOAD.LOAD_RESOURCE;
1987: end if ;
1988: end if;
1985: if (MSC_CL_COLLECTION.v_bom_refresh_type <> 3) then
1986: MSC_CL_BOM_ODS_LOAD.LOAD_RESOURCE;
1987: end if ;
1988: end if;
1989: IF MSC_CL_COLLECTION.v_recalc_nra= MSC_UTIL.SYS_YES THEN
1990: IF MSC_CL_COLLECTION.v_discrete_flag= MSC_UTIL.SYS_YES THEN
1991: BEGIN
1992: IF MSC_CL_COLLECTION.v_instance_type = MSC_UTIL.G_INS_OTHER THEN
1993: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_NET_RESOURCE_AVAIL',
1986: MSC_CL_BOM_ODS_LOAD.LOAD_RESOURCE;
1987: end if ;
1988: end if;
1989: IF MSC_CL_COLLECTION.v_recalc_nra= MSC_UTIL.SYS_YES THEN
1990: IF MSC_CL_COLLECTION.v_discrete_flag= MSC_UTIL.SYS_YES THEN
1991: BEGIN
1992: IF MSC_CL_COLLECTION.v_instance_type = MSC_UTIL.G_INS_OTHER THEN
1993: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_NET_RESOURCE_AVAIL',
1994: MSC_CL_COLLECTION.v_instance_id, -1);
1988: end if;
1989: IF MSC_CL_COLLECTION.v_recalc_nra= MSC_UTIL.SYS_YES THEN
1990: IF MSC_CL_COLLECTION.v_discrete_flag= MSC_UTIL.SYS_YES THEN
1991: BEGIN
1992: IF MSC_CL_COLLECTION.v_instance_type = MSC_UTIL.G_INS_OTHER THEN
1993: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_NET_RESOURCE_AVAIL',
1994: MSC_CL_COLLECTION.v_instance_id, -1);
1995: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_NET_RES_INST_AVAIL',
1996: MSC_CL_COLLECTION.v_instance_id, -1);
1999: SELECT DECODE(M2A_DBLINK,
2000: NULL,'',
2001: '@'||M2A_DBLINK),
2002: DECODE( A2M_DBLINK,
2003: NULL,MSC_UTIL.NULL_DBLINK,
2004: A2M_DBLINK),
2005: INSTANCE_CODE
2006: INTO lv_dblink,
2007: lv_dest_a2m,
2009: FROM MSC_APPS_INSTANCES
2010: WHERE INSTANCE_ID=MSC_CL_COLLECTION.v_instance_id;
2011:
2012: lv_res_avail_before_sysdate := nvl(TO_NUMBER(FND_PROFILE.VAlUE('MSC_RES_AVAIL_BEFORE_SYSDAT')),1);
2013: IF MSC_CL_COLLECTION.v_instance_type <> MSC_UTIL.G_INS_OTHER THEN
2014: lv_sql_stmt:= 'SELECT nvl(mar.LRD,sysdate)- '||lv_res_avail_before_sysdate
2015: ||' FROM MRP_AP_APPS_INSTANCES_ALL'||lv_dblink||' mar'
2016: ||' WHERE INSTANCE_ID = '||MSC_CL_COLLECTION.v_instance_id
2017: ||' AND INSTANCE_CODE = '''||lv_instance_code||''''
2014: lv_sql_stmt:= 'SELECT nvl(mar.LRD,sysdate)- '||lv_res_avail_before_sysdate
2015: ||' FROM MRP_AP_APPS_INSTANCES_ALL'||lv_dblink||' mar'
2016: ||' WHERE INSTANCE_ID = '||MSC_CL_COLLECTION.v_instance_id
2017: ||' AND INSTANCE_CODE = '''||lv_instance_code||''''
2018: ||' AND nvl(A2M_DBLINK,'''||MSC_UTIL.NULL_DBLINK||''') = '''||lv_dest_a2m||'''' ;
2019: EXECUTE IMMEDIATE lv_sql_stmt INTO lv_resource_start_time;
2020: END IF;
2021: IF MSC_CL_COLLECTION.v_instance_type = MSC_UTIL.G_INS_OTHER THEN
2022: lv_resource_start_time := lv_resource_start_time - nvl(lv_res_avail_before_sysdate,1);
2017: ||' AND INSTANCE_CODE = '''||lv_instance_code||''''
2018: ||' AND nvl(A2M_DBLINK,'''||MSC_UTIL.NULL_DBLINK||''') = '''||lv_dest_a2m||'''' ;
2019: EXECUTE IMMEDIATE lv_sql_stmt INTO lv_resource_start_time;
2020: END IF;
2021: IF MSC_CL_COLLECTION.v_instance_type = MSC_UTIL.G_INS_OTHER THEN
2022: lv_resource_start_time := lv_resource_start_time - nvl(lv_res_avail_before_sysdate,1);
2023: END IF;
2024:
2025: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'@@before net res avai : debug 1');
2021: IF MSC_CL_COLLECTION.v_instance_type = MSC_UTIL.G_INS_OTHER THEN
2022: lv_resource_start_time := lv_resource_start_time - nvl(lv_res_avail_before_sysdate,1);
2023: END IF;
2024:
2025: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'@@before net res avai : debug 1');
2026: lv_ret_res_ava:=MSC_RESOURCE_AVAILABILITY.CALC_RESOURCE_AVAILABILITY(lv_resource_start_time,MSC_CL_COLLECTION.v_coll_prec.org_group_flag,FALSE);
2027:
2028: IF lv_ret_res_ava = 2 THEN
2029: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_CALC_RES_AVAIL_FAIL');
2026: lv_ret_res_ava:=MSC_RESOURCE_AVAILABILITY.CALC_RESOURCE_AVAILABILITY(lv_resource_start_time,MSC_CL_COLLECTION.v_coll_prec.org_group_flag,FALSE);
2027:
2028: IF lv_ret_res_ava = 2 THEN
2029: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_CALC_RES_AVAIL_FAIL');
2030: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2031: MSC_CL_COLLECTION.v_warning_flag:=MSC_UTIL.SYS_YES;
2032: ELSIF lv_ret_res_ava <> 0 THEN
2033: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_CALC_RES_AVAIL_FAIL');
2034: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2027:
2028: IF lv_ret_res_ava = 2 THEN
2029: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_CALC_RES_AVAIL_FAIL');
2030: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2031: MSC_CL_COLLECTION.v_warning_flag:=MSC_UTIL.SYS_YES;
2032: ELSIF lv_ret_res_ava <> 0 THEN
2033: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_CALC_RES_AVAIL_FAIL');
2034: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2035: RAISE ex_calc_res_avail;
2030: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2031: MSC_CL_COLLECTION.v_warning_flag:=MSC_UTIL.SYS_YES;
2032: ELSIF lv_ret_res_ava <> 0 THEN
2033: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_CALC_RES_AVAIL_FAIL');
2034: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2035: RAISE ex_calc_res_avail;
2036:
2037: -- ERRBUF:= FND_MESSAGE.GET;
2038: -- RETCODE:= G_ERROR;
2557: WHEN OTHERS THEN
2558:
2559: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
2560:
2561: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2562: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2563: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2564: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TRADING_PARTNERS');
2565: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2561: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2562: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2563: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2564: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TRADING_PARTNERS');
2565: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2566:
2567: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2568: RAISE;
2569:
2563: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2564: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TRADING_PARTNERS');
2565: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2566:
2567: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2568: RAISE;
2569:
2570: ELSE
2571: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2567: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2568: RAISE;
2569:
2570: ELSE
2571: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2572:
2573: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2574: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2575: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2569:
2570: ELSE
2571: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2572:
2573: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2574: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2575: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2576: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TRADING_PARTNERS');
2577: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2573: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2574: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2575: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2576: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TRADING_PARTNERS');
2577: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2578:
2579: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2580: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TP_ID');
2581: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SR_TP_ID));
2578:
2579: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2580: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TP_ID');
2581: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SR_TP_ID));
2582: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2583:
2584: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2585: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
2586: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.ORGANIZATION_CODE);
2583:
2584: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2585: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
2586: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.ORGANIZATION_CODE);
2587: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2588:
2589: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2590: END IF;
2591:
2585: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
2586: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.ORGANIZATION_CODE);
2587: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2588:
2589: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2590: END IF;
2591:
2592: END;
2593:
2616: TP_SITE_CODE= c_rec.TP_SITE_CODE,
2617: LOCATION= c_rec.LOCATION,
2618: LONGITUDE= c_rec.LONGITUDE,
2619: LATITUDE= c_rec.LATITUDE,
2620: DELETED_FLAG= MSC_UTIL.SYS_NO,
2621: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2622: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2623: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2624: WHERE PARTNER_TYPE= 3
2659: c_rec.SR_TP_SITE_ID,
2660: c_rec.SR_INSTANCE_ID,
2661: c_rec.TP_SITE_CODE,
2662: c_rec.LOCATION,
2663: MSC_UTIL.SYS_NO,
2664: MSC_CL_COLLECTION.v_last_collection_id,
2665: MSC_CL_COLLECTION.v_current_date,
2666: MSC_CL_COLLECTION.v_current_user,
2667: MSC_CL_COLLECTION.v_current_date,
2673: WHEN OTHERS THEN
2674:
2675: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
2676:
2677: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2678: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2679: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2680: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TRADING_PARTNERS_SITES');
2681: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2677: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2678: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2679: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2680: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TRADING_PARTNERS_SITES');
2681: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2682:
2683: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2684: RAISE;
2685:
2679: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2680: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TRADING_PARTNERS_SITES');
2681: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2682:
2683: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2684: RAISE;
2685:
2686: ELSE
2687: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2683: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2684: RAISE;
2685:
2686: ELSE
2687: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2688:
2689: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2690: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2691: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2685:
2686: ELSE
2687: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2688:
2689: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2690: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2691: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2692: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TRADING_PARTNERS_SITES');
2693: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2689: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2690: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2691: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2692: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TRADING_PARTNERS_SITES');
2693: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2694:
2695: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2696: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TP_SITE_ID');
2697: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SR_TP_SITE_ID));
2694:
2695: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2696: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TP_SITE_ID');
2697: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SR_TP_SITE_ID));
2698: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2699:
2700: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2701: FND_MESSAGE.SET_TOKEN('COLUMN', 'LOCATION');
2702: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.LOCATION);
2699:
2700: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2701: FND_MESSAGE.SET_TOKEN('COLUMN', 'LOCATION');
2702: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.LOCATION);
2703: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2704:
2705: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2706: END IF;
2707:
2701: FND_MESSAGE.SET_TOKEN('COLUMN', 'LOCATION');
2702: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.LOCATION);
2703: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2704:
2705: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2706: END IF;
2707:
2708: END;
2709:
2755: WHEN OTHERS THEN
2756:
2757: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
2758:
2759: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2760: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2761: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2762: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_LOCATION_ASSOCIATIONS');
2763: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2759: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2760: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2761: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2762: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_LOCATION_ASSOCIATIONS');
2763: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2764:
2765: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2766: RAISE;
2767:
2761: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2762: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_LOCATION_ASSOCIATIONS');
2763: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2764:
2765: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2766: RAISE;
2767:
2768: ELSE
2769:
2766: RAISE;
2767:
2768: ELSE
2769:
2770: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2771:
2772: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2773: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2774: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2768: ELSE
2769:
2770: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2771:
2772: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2773: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2774: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2775: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_LOCATION_ASSOCIATIONS');
2776: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2772: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2773: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2774: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2775: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_LOCATION_ASSOCIATIONS');
2776: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2777:
2778: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2779: FND_MESSAGE.SET_TOKEN('COLUMN', 'LOCATION_ID');
2780: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.LOCATION_ID));
2777:
2778: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2779: FND_MESSAGE.SET_TOKEN('COLUMN', 'LOCATION_ID');
2780: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.LOCATION_ID));
2781: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2782:
2783: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2784: FND_MESSAGE.SET_TOKEN('COLUMN', 'LOCATION_CODE');
2785: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.LOCATION_CODE);
2782:
2783: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2784: FND_MESSAGE.SET_TOKEN('COLUMN', 'LOCATION_CODE');
2785: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.LOCATION_CODE);
2786: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2787:
2788: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2789: END IF;
2790:
2784: FND_MESSAGE.SET_TOKEN('COLUMN', 'LOCATION_CODE');
2785: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.LOCATION_CODE);
2786: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2787:
2788: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2789: END IF;
2790:
2791: END;
2792:
2830: EXCEPTION
2831: WHEN OTHERS THEN
2832: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
2833:
2834: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2835: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2836: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2837: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_LOCATION_ASSOCIATIONS');
2838: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2834: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2835: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2836: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2837: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_LOCATION_ASSOCIATIONS');
2838: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2839:
2840: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2841: RAISE;
2842:
2836: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2837: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_LOCATION_ASSOCIATIONS');
2838: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2839:
2840: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2841: RAISE;
2842:
2843: ELSE
2844: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2840: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2841: RAISE;
2842:
2843: ELSE
2844: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2845:
2846: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2847: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2848: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2842:
2843: ELSE
2844: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2845:
2846: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2847: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2848: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2849: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_LOCATION_ASSOCIATIONS');
2850: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2846: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2847: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2848: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2849: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_LOCATION_ASSOCIATIONS');
2850: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2851:
2852: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2853: FND_MESSAGE.SET_TOKEN('COLUMN', 'LOCATION_ID');
2854: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.LOCATION_ID));
2851:
2852: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2853: FND_MESSAGE.SET_TOKEN('COLUMN', 'LOCATION_ID');
2854: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.LOCATION_ID));
2855: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2856:
2857: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2858: FND_MESSAGE.SET_TOKEN('COLUMN', 'LOCATION_CODE');
2859: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.LOCATION_CODE);
2856:
2857: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2858: FND_MESSAGE.SET_TOKEN('COLUMN', 'LOCATION_CODE');
2859: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.LOCATION_CODE);
2860: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2861:
2862: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2863: END IF;
2864:
2858: FND_MESSAGE.SET_TOKEN('COLUMN', 'LOCATION_CODE');
2859: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.LOCATION_CODE);
2860: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2861:
2862: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2863: END IF;
2864:
2865: END;
2866:
2879: FOR c_rec IN c4 LOOP
2880:
2881: if(MSC_CL_COLLECTION.v_is_legacy_refresh) then
2882:
2883: if (c_rec.DELETED_FLAG =MSC_UTIL.SYS_YES) then
2884:
2885: delete from MSC_PARTNER_CONTACTS where
2886: partner_id=c_rec.PARTNER_ID
2887: and nvl(partner_site_id,-99999)=nvl(c_rec.PARTNER_SITE_ID,-99999)
2886: partner_id=c_rec.PARTNER_ID
2887: and nvl(partner_site_id,-99999)=nvl(c_rec.PARTNER_SITE_ID,-99999)
2888: and partner_type=c_rec.PARTNER_TYPE
2889: and SR_INSTANCE_ID =MSC_CL_COLLECTION.v_instance_id
2890: and c_rec.DELETED_FLAG =MSC_UTIL.SYS_YES;
2891: else
2892: UPDATE MSC_PARTNER_CONTACTS
2893: set Name=c_rec.NAME,
2894: DISPLAY_NAME =c_rec.DISPLAY_NAME,
2909:
2910: END IF ;
2911:
2912:
2913: 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)
2914: THEN
2915: IF lv_old_partner_id <> NVL(c_rec.partner_id,0) OR
2916: lv_old_partner_site_id <> NVL(c_rec.partner_site_id,0) OR
2917: lv_old_partner_type <> c_rec.partner_type THEN
2952: EXCEPTION
2953: WHEN OTHERS THEN
2954: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
2955:
2956: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2957: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2958: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2959: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_PARTNER_CONTACTS');
2960: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2956: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2957: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2958: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2959: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_PARTNER_CONTACTS');
2960: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2961:
2962: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2963: RAISE;
2964:
2958: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2959: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_PARTNER_CONTACTS');
2960: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2961:
2962: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2963: RAISE;
2964:
2965: ELSE
2966: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2962: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2963: RAISE;
2964:
2965: ELSE
2966: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2967:
2968: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2969: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2970: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2964:
2965: ELSE
2966: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2967:
2968: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2969: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2970: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2971: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_PARTNER_CONTACTS');
2972: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2968: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2969: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2970: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2971: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_PARTNER_CONTACTS');
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_ID');
2976: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.PARTNER_ID));
2973:
2974: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2975: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_ID');
2976: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.PARTNER_ID));
2977: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2978:
2979: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2980: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_SITE_ID');
2981: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.PARTNER_SITE_ID));
2978:
2979: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2980: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_SITE_ID');
2981: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.PARTNER_SITE_ID));
2982: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2983:
2984: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2985: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_TYPE');
2986: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.PARTNER_TYPE));
2983:
2984: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2985: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_TYPE');
2986: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.PARTNER_TYPE));
2987: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2988:
2989: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2990: END IF;
2991:
2985: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_TYPE');
2986: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.PARTNER_TYPE));
2987: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2988:
2989: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2990: END IF;
2991:
2992: END;
2993:
3079: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3080: WHERE SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
3081: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID;
3082: /* Bug: 1993151 remove the collected flag from the update statement */
3083: -- AND COLLECTED_FLAG= MSC_UTIL.SYS_YES;
3084:
3085: IF SQL%NOTFOUND THEN
3086:
3087: INSERT INTO MSC_PARAMETERS
3137: c_rec.REPETITIVE_BUCKET_SIZE2,
3138: c_rec.REPETITIVE_BUCKET_SIZE3,
3139: c_rec.REPETITIVE_ANCHOR_DATE,
3140: 1,
3141: MSC_UTIL.SYS_YES,
3142: c_rec.SR_INSTANCE_ID,
3143: MSC_CL_COLLECTION.v_current_date,
3144: MSC_CL_COLLECTION.v_current_user,
3145: MSC_CL_COLLECTION.v_current_date,
3152: WHEN OTHERS THEN
3153:
3154: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
3155:
3156: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3157: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3158: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_PARAMETER');
3159: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_PARAMETERS');
3160: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3156: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3157: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3158: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_PARAMETER');
3159: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_PARAMETERS');
3160: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3161:
3162: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3163: RAISE;
3164:
3158: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_PARAMETER');
3159: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_PARAMETERS');
3160: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3161:
3162: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3163: RAISE;
3164:
3165: ELSE
3166:
3163: RAISE;
3164:
3165: ELSE
3166:
3167: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3168:
3169: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3170: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3171: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_PARAMETER');
3165: ELSE
3166:
3167: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3168:
3169: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3170: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3171: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_PARAMETER');
3172: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_PARAMETERS');
3173: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3169: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3170: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3171: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_PARAMETER');
3172: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_PARAMETERS');
3173: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3174:
3175: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3176: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
3177: FND_MESSAGE.SET_TOKEN('VALUE',
3176: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
3177: FND_MESSAGE.SET_TOKEN('VALUE',
3178: MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
3179: MSC_CL_COLLECTION.v_instance_id));
3180: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3181:
3182: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3183: END IF;
3184:
3178: MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
3179: MSC_CL_COLLECTION.v_instance_id));
3180: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3181:
3182: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3183: END IF;
3184:
3185: END;
3186:
3325: WHEN OTHERS THEN
3326:
3327: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
3328:
3329: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3330: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3331: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_UOM');
3332: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_UNITS_OF_MEASURE');
3333: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3329: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3330: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3331: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_UOM');
3332: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_UNITS_OF_MEASURE');
3333: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3334:
3335: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3336: RAISE;
3337:
3331: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_UOM');
3332: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_UNITS_OF_MEASURE');
3333: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3334:
3335: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3336: RAISE;
3337:
3338: ELSE
3339:
3336: RAISE;
3337:
3338: ELSE
3339:
3340: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3341:
3342: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3343: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3344: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_UOM');
3338: ELSE
3339:
3340: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3341:
3342: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3343: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3344: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_UOM');
3345: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_UNITS_OF_MEASURE');
3346: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3342: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3343: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3344: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_UOM');
3345: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_UNITS_OF_MEASURE');
3346: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3347:
3348: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3349: FND_MESSAGE.SET_TOKEN('COLUMN', 'UOM_CODE');
3350: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.UOM_CODE);
3347:
3348: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3349: FND_MESSAGE.SET_TOKEN('COLUMN', 'UOM_CODE');
3350: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.UOM_CODE);
3351: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3352:
3353: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3354: FND_MESSAGE.SET_TOKEN('COLUMN', 'UNIT_OF_MEASURE');
3355: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.UNIT_OF_MEASURE);
3352:
3353: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3354: FND_MESSAGE.SET_TOKEN('COLUMN', 'UNIT_OF_MEASURE');
3355: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.UNIT_OF_MEASURE);
3356: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3357:
3358: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3359: END IF;
3360:
3354: FND_MESSAGE.SET_TOKEN('COLUMN', 'UNIT_OF_MEASURE');
3355: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.UNIT_OF_MEASURE);
3356: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3357:
3358: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3359: END IF;
3360:
3361: END;
3362:
3444: WHEN OTHERS THEN
3445:
3446: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
3447:
3448: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3449: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3450: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_UOM');
3451: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_UOM_CLASS_CONVERSIONS');
3452: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3448: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3449: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3450: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_UOM');
3451: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_UOM_CLASS_CONVERSIONS');
3452: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3453:
3454: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3455: RAISE;
3456:
3450: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_UOM');
3451: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_UOM_CLASS_CONVERSIONS');
3452: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3453:
3454: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3455: RAISE;
3456:
3457: ELSE
3458:
3455: RAISE;
3456:
3457: ELSE
3458:
3459: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3460:
3461: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3462: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3463: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_UOM');
3457: ELSE
3458:
3459: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3460:
3461: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3462: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3463: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_UOM');
3464: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_UOM_CLASS_CONVERSIONS');
3465: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3461: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3462: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3463: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_UOM');
3464: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_UOM_CLASS_CONVERSIONS');
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', 'MSC_CL_ITEM_ODS_LOAD.item_name');
3469: FND_MESSAGE.SET_TOKEN('VALUE', MSC_CL_ITEM_ODS_LOAD.item_name( c_rec.INVENTORY_ITEM_ID));
3466:
3467: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3468: FND_MESSAGE.SET_TOKEN('COLUMN', 'MSC_CL_ITEM_ODS_LOAD.item_name');
3469: FND_MESSAGE.SET_TOKEN('VALUE', MSC_CL_ITEM_ODS_LOAD.item_name( c_rec.INVENTORY_ITEM_ID));
3470: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3471:
3472: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3473: FND_MESSAGE.SET_TOKEN('COLUMN', 'FROM_UNIT_OF_MEASURE');
3474: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.FROM_UNIT_OF_MEASURE);
3471:
3472: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3473: FND_MESSAGE.SET_TOKEN('COLUMN', 'FROM_UNIT_OF_MEASURE');
3474: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.FROM_UNIT_OF_MEASURE);
3475: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3476:
3477: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3478: FND_MESSAGE.SET_TOKEN('COLUMN', 'TO_UNIT_OF_MEASURE');
3479: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.TO_UNIT_OF_MEASURE);
3476:
3477: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3478: FND_MESSAGE.SET_TOKEN('COLUMN', 'TO_UNIT_OF_MEASURE');
3479: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.TO_UNIT_OF_MEASURE);
3480: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3481:
3482: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3483: END IF;
3484:
3478: FND_MESSAGE.SET_TOKEN('COLUMN', 'TO_UNIT_OF_MEASURE');
3479: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.TO_UNIT_OF_MEASURE);
3480: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3481:
3482: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3483: END IF;
3484:
3485: END;
3486:
3567: WHEN OTHERS THEN
3568:
3569: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
3570:
3571: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3572: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3573: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_UOM');
3574: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_UOM_CONVERSIONS');
3575: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3571: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3572: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3573: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_UOM');
3574: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_UOM_CONVERSIONS');
3575: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3576:
3577: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3578: RAISE;
3579:
3573: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_UOM');
3574: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_UOM_CONVERSIONS');
3575: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3576:
3577: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3578: RAISE;
3579:
3580: ELSE
3581:
3578: RAISE;
3579:
3580: ELSE
3581:
3582: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3583:
3584: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3585: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3586: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_UOM');
3580: ELSE
3581:
3582: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3583:
3584: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3585: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3586: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_UOM');
3587: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_UOM_CONVERSIONS');
3588: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3584: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3585: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3586: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_UOM');
3587: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_UOM_CONVERSIONS');
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', 'MSC_CL_ITEM_ODS_LOAD.item_name');
3592: FND_MESSAGE.SET_TOKEN('VALUE', MSC_CL_ITEM_ODS_LOAD.item_name( c_rec.INVENTORY_ITEM_ID));
3589:
3590: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3591: FND_MESSAGE.SET_TOKEN('COLUMN', 'MSC_CL_ITEM_ODS_LOAD.item_name');
3592: FND_MESSAGE.SET_TOKEN('VALUE', MSC_CL_ITEM_ODS_LOAD.item_name( c_rec.INVENTORY_ITEM_ID));
3593: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3594:
3595: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3596: FND_MESSAGE.SET_TOKEN('COLUMN', 'UNIT_OF_MEASURE');
3597: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.UNIT_OF_MEASURE);
3594:
3595: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3596: FND_MESSAGE.SET_TOKEN('COLUMN', 'UNIT_OF_MEASURE');
3597: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.UNIT_OF_MEASURE);
3598: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3599:
3600: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3601: FND_MESSAGE.SET_TOKEN('COLUMN', 'UOM_CODE');
3602: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.UOM_CODE);
3599:
3600: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3601: FND_MESSAGE.SET_TOKEN('COLUMN', 'UOM_CODE');
3602: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.UOM_CODE);
3603: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3604:
3605: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3606: END IF;
3607:
3601: FND_MESSAGE.SET_TOKEN('COLUMN', 'UOM_CODE');
3602: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.UOM_CODE);
3603: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3604:
3605: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3606: END IF;
3607:
3608: END;
3609:
3973: lv_partner_count NUMBER;
3974:
3975: lv_tp_id_count NUMBER := 0;
3976: lv_tp_site_id_count NUMBER := 0;
3977: lv_tp_stat_stale NUMBER := MSC_UTIL.SYS_NO;
3978: lv_tp_site_stat_stale NUMBER := MSC_UTIL.SYS_NO;
3979: lv_ins_records NUMBER := 0;
3980:
3981: BEGIN
3974:
3975: lv_tp_id_count NUMBER := 0;
3976: lv_tp_site_id_count NUMBER := 0;
3977: lv_tp_stat_stale NUMBER := MSC_UTIL.SYS_NO;
3978: lv_tp_site_stat_stale NUMBER := MSC_UTIL.SYS_NO;
3979: lv_ins_records NUMBER := 0;
3980:
3981: BEGIN
3982:
3996: WHEN OTHERS THEN
3997: lv_msc_tp_coll_window := 0;
3998: END;
3999:
4000: 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
4001: lv_msc_tp_coll_window := 0;
4002: END IF;
4003:
4004: begin
4001: lv_msc_tp_coll_window := 0;
4002: END IF;
4003:
4004: begin
4005: select num_rows,decode (stale_stats,'NO', MSC_UTIL.SYS_NO, MSC_UTIL.SYS_YES )
4006: into lv_tp_id_count, lv_tp_stat_stale
4007: from dba_TAB_STATISTICS
4008: where table_name = 'MSC_TP_ID_LID';
4009: exception when no_data_found then
4006: into lv_tp_id_count, lv_tp_stat_stale
4007: from dba_TAB_STATISTICS
4008: where table_name = 'MSC_TP_ID_LID';
4009: exception when no_data_found then
4010: lv_tp_stat_stale := MSC_UTIL.SYS_YES ;
4011: end;
4012:
4013: begin
4014: select num_rows,decode (stale_stats,'NO', MSC_UTIL.SYS_NO, MSC_UTIL.SYS_YES )
4010: lv_tp_stat_stale := MSC_UTIL.SYS_YES ;
4011: end;
4012:
4013: begin
4014: select num_rows,decode (stale_stats,'NO', MSC_UTIL.SYS_NO, MSC_UTIL.SYS_YES )
4015: into lv_tp_site_id_count, lv_tp_site_stat_stale
4016: from dba_TAB_STATISTICS
4017: where table_name ='MSC_TP_SITE_ID_LID';
4018: exception when no_data_found then
4015: into lv_tp_site_id_count, lv_tp_site_stat_stale
4016: from dba_TAB_STATISTICS
4017: where table_name ='MSC_TP_SITE_ID_LID';
4018: exception when no_data_found then
4019: lv_tp_site_stat_stale := MSC_UTIL.SYS_YES ;
4020: end;
4021:
4022: /* if complete refresh, regen the key mapping data */
4023: IF MSC_CL_COLLECTION.v_is_complete_refresh THEN
4036: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_TP_SITE_ID_LID');
4037: END IF;
4038:
4039: END IF;
4040: lv_tp_stat_stale := MSC_UTIL.SYS_YES ;
4041: lv_tp_site_stat_stale := MSC_UTIL.SYS_YES ;
4042: END IF;
4043:
4044: /*************** PREPLACE CHANGE START *****************/
4037: END IF;
4038:
4039: END IF;
4040: lv_tp_stat_stale := MSC_UTIL.SYS_YES ;
4041: lv_tp_site_stat_stale := MSC_UTIL.SYS_YES ;
4042: END IF;
4043:
4044: /*************** PREPLACE CHANGE START *****************/
4045:
4048: --when sourcing SRS launch parameter is Yes --and delete supplier and customer records
4049: --from MSC_TP_ID_LID when either Supplier or Customer SRS launch parameter is Yes.
4050:
4051: IF (MSC_CL_COLLECTION.v_is_partial_refresh OR MSC_CL_COLLECTION.V_IS_CONT_REFRESH) THEN
4052: IF (MSC_CL_COLLECTION.v_coll_prec.sourcing_rule_flag=MSC_UTIL.SYS_YES) THEN
4053: DELETE MSC_TP_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id and partner_type=4;
4054: DELETE MSC_TP_ID_LID WHERE SR_INSTANCE_ID= -1 and partner_type=4;
4055: lv_tp_stat_stale := MSC_UTIL.SYS_YES ;
4056: END IF;
4051: IF (MSC_CL_COLLECTION.v_is_partial_refresh OR MSC_CL_COLLECTION.V_IS_CONT_REFRESH) THEN
4052: IF (MSC_CL_COLLECTION.v_coll_prec.sourcing_rule_flag=MSC_UTIL.SYS_YES) THEN
4053: DELETE MSC_TP_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id and partner_type=4;
4054: DELETE MSC_TP_ID_LID WHERE SR_INSTANCE_ID= -1 and partner_type=4;
4055: lv_tp_stat_stale := MSC_UTIL.SYS_YES ;
4056: END IF;
4057: END IF;
4058:
4059: IF MSC_CL_COLLECTION.v_is_partial_refresh THEN
4057: END IF;
4058:
4059: IF MSC_CL_COLLECTION.v_is_partial_refresh THEN
4060:
4061: IF ((MSC_CL_COLLECTION.v_coll_prec.tp_vendor_flag = MSC_UTIL.SYS_YES) or
4062: (MSC_CL_COLLECTION.v_coll_prec.tp_customer_flag = MSC_UTIL.SYS_YES)) THEN
4063:
4064: -- Note now vendor or customer cannot be refreshed
4065: -- separately. If that functionality needs to be provided
4058:
4059: IF MSC_CL_COLLECTION.v_is_partial_refresh THEN
4060:
4061: IF ((MSC_CL_COLLECTION.v_coll_prec.tp_vendor_flag = MSC_UTIL.SYS_YES) or
4062: (MSC_CL_COLLECTION.v_coll_prec.tp_customer_flag = MSC_UTIL.SYS_YES)) THEN
4063:
4064: -- Note now vendor or customer cannot be refreshed
4065: -- separately. If that functionality needs to be provided
4066: -- in future then the the ID_LID tables will have to
4080: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_TP_ID_LID');
4081: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_TP_SITE_ID_LID');
4082: END IF;
4083: END IF;
4084: lv_tp_stat_stale := MSC_UTIL.SYS_YES ;
4085: lv_tp_site_stat_stale := MSC_UTIL.SYS_YES ;
4086: END IF;
4087:
4088: END IF;
4081: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_TP_SITE_ID_LID');
4082: END IF;
4083: END IF;
4084: lv_tp_stat_stale := MSC_UTIL.SYS_YES ;
4085: lv_tp_site_stat_stale := MSC_UTIL.SYS_YES ;
4086: END IF;
4087:
4088: END IF;
4089:
4091:
4092: --agmcont
4093: IF MSC_CL_COLLECTION.V_IS_CONT_REFRESH THEN
4094:
4095: IF ((MSC_CL_COLLECTION.v_coll_prec.tp_vendor_flag = MSC_UTIL.SYS_YES) or
4096: (MSC_CL_COLLECTION.v_coll_prec.tp_customer_flag = MSC_UTIL.SYS_YES)) THEN
4097:
4098: -- Note now vendor or customer cannot be refreshed
4099: -- separately. If that functionality needs to be provided
4092: --agmcont
4093: IF MSC_CL_COLLECTION.V_IS_CONT_REFRESH THEN
4094:
4095: IF ((MSC_CL_COLLECTION.v_coll_prec.tp_vendor_flag = MSC_UTIL.SYS_YES) or
4096: (MSC_CL_COLLECTION.v_coll_prec.tp_customer_flag = MSC_UTIL.SYS_YES)) THEN
4097:
4098: -- Note now vendor or customer cannot be refreshed
4099: -- separately. If that functionality needs to be provided
4100: -- in future then the the ID_LID tables will have to
4112: ELSE
4113: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_TP_ID_LID');
4114: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_TP_SITE_ID_LID');
4115: END IF;
4116: lv_tp_stat_stale := MSC_UTIL.SYS_YES ;
4117: lv_tp_site_stat_stale := MSC_UTIL.SYS_YES ;
4118: END IF;
4119: END IF;
4120:
4113: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_TP_ID_LID');
4114: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_TP_SITE_ID_LID');
4115: END IF;
4116: lv_tp_stat_stale := MSC_UTIL.SYS_YES ;
4117: lv_tp_site_stat_stale := MSC_UTIL.SYS_YES ;
4118: END IF;
4119: END IF;
4120:
4121: END IF;
4141: AND nvl(company_id, -1) = c_rec.company_id1;
4142:
4143: IF (lv_partner_count > 0) THEN
4144:
4145: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'Partner is being merged..');
4146: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'Partner name is :'|| c_rec.partner_name);
4147:
4148: ELSE
4149: BEGIN
4142:
4143: IF (lv_partner_count > 0) THEN
4144:
4145: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'Partner is being merged..');
4146: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'Partner name is :'|| c_rec.partner_name);
4147:
4148: ELSE
4149: BEGIN
4150:
4156: AND nvl(mtp.company_id,-1) = c_rec.company_id1;
4157:
4158: EXCEPTION
4159: WHEN DUP_VAL_ON_INDEX THEN
4160: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,SQLERRM);
4161: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'In DUP_VAL_ON_INDEX exception clause of c5_tpname');
4162: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'The value of partner name is: '|| c_rec.PARTNER_NAME);
4163: -- Fetch the Old Partner Id
4164: SELECT partner_id
4157:
4158: EXCEPTION
4159: WHEN DUP_VAL_ON_INDEX THEN
4160: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,SQLERRM);
4161: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'In DUP_VAL_ON_INDEX exception clause of c5_tpname');
4162: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'The value of partner name is: '|| c_rec.PARTNER_NAME);
4163: -- Fetch the Old Partner Id
4164: SELECT partner_id
4165: INTO lv_old_partner_id
4158: EXCEPTION
4159: WHEN DUP_VAL_ON_INDEX THEN
4160: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,SQLERRM);
4161: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'In DUP_VAL_ON_INDEX exception clause of c5_tpname');
4162: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'The value of partner name is: '|| c_rec.PARTNER_NAME);
4163: -- Fetch the Old Partner Id
4164: SELECT partner_id
4165: INTO lv_old_partner_id
4166: FROM msc_trading_partners
4169: AND SR_INSTANCE_ID = c_rec.SR_INSTANCE_ID
4170: AND PARTNER_TYPE = c_rec.PARTNER_TYPE
4171: AND nvl(company_id,-1) = c_rec.company_id1;
4172:
4173: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'The old Partner_Id IS: ' || to_number(lv_old_partner_id));
4174: -- Fetch the New Partner Id
4175: SELECT partner_id
4176: INTO lv_new_partner_id
4177: FROM msc_trading_partners
4180: AND SR_INSTANCE_ID = c_rec.SR_INSTANCE_ID
4181: AND PARTNER_TYPE = c_rec.PARTNER_TYPE
4182: AND nvl(company_id,-1) = c_rec.company_id1;
4183:
4184: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'The new Partner_Id IS: ' || to_number(lv_new_partner_id));
4185:
4186: /*DELETE FROM MSC_TRADING_PARTNERS
4187: WHERE SR_TP_ID = c_rec.SR_TP_ID
4188: AND SR_INSTANCE_ID = c_rec.SR_INSTANCE_ID
4204: */
4205:
4206: WHEN OTHERS THEN
4207:
4208: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
4209:
4210: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4211: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4212: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'MSC_CL_SETUP_ODS_LOAD.TRANSFORM_KEYS');
4206: WHEN OTHERS THEN
4207:
4208: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
4209:
4210: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4211: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4212: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'MSC_CL_SETUP_ODS_LOAD.TRANSFORM_KEYS');
4213: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TRADING_PARTNERS');
4214: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4210: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4211: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4212: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'MSC_CL_SETUP_ODS_LOAD.TRANSFORM_KEYS');
4213: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TRADING_PARTNERS');
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_TYPE');
4218: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_TYPE);
4215:
4216: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4217: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_TYPE');
4218: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_TYPE);
4219: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4220:
4221: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4222: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TP_ID');
4223: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SR_TP_ID);
4220:
4221: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4222: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TP_ID');
4223: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SR_TP_ID);
4224: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4225:
4226: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4227: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_NAME');
4228: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_NAME);
4225:
4226: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4227: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_NAME');
4228: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_NAME);
4229: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4230:
4231: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4232:
4233: END;
4227: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_NAME');
4228: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_NAME);
4229: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4230:
4231: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4232:
4233: END;
4234: END IF;-- IF (lv_partner_count > 0) THEN
4235: END LOOP;
4283: NULL;
4284:
4285: WHEN OTHERS THEN
4286:
4287: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4288: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4289: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
4290: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TRADING_PARTNERS');
4291: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4287: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4288: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4289: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
4290: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TRADING_PARTNERS');
4291: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4292:
4293: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4294: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_NAME');
4295: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_NAME);
4292:
4293: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4294: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_NAME');
4295: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_NAME);
4296: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4297:
4298: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4299:
4300: ERRBUF := sqlerrm;
4294: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_NAME');
4295: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_NAME);
4296: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4297:
4298: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4299:
4300: ERRBUF := sqlerrm;
4301: RETCODE := MSC_UTIL.G_ERROR;
4302: RAISE;
4297:
4298: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4299:
4300: ERRBUF := sqlerrm;
4301: RETCODE := MSC_UTIL.G_ERROR;
4302: RAISE;
4303:
4304: END;
4305:
4308: COMMIT;
4309:
4310: lv_ins_records := 0;
4311: -- ==== populate msc_tp_id_lid with newly created Suppliers / Customers ====
4312: IF MSC_CL_COLLECTION.v_apps_ver < MSC_UTIL.G_APPS115 AND MSC_CL_COLLECTION.v_apps_ver <> -1 THEN
4313:
4314: /* For 107 and 110 the vendor_id and vendor
4315: site id can be duplicate, therefore we use
4316: the cursors to handle such exceptions, but for 11i we can use a straight
4340: WHEN OTHERS THEN
4341:
4342: IF SQLCODE IN (-1653,-1654) THEN
4343:
4344: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4345: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4346: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
4347: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TP_ID_LID');
4348: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4344: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4345: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4346: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
4347: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TP_ID_LID');
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', 'SR_TP_ID');
4352: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SR_TP_ID);
4349:
4350: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4351: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TP_ID');
4352: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SR_TP_ID);
4353: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4354:
4355: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4356: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_TYPE');
4357: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_TYPE);
4354:
4355: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4356: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_TYPE');
4357: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_TYPE);
4358: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4359:
4360: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4361: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_ID');
4362: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_ID);
4359:
4360: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4361: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_ID');
4362: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_ID);
4363: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4364:
4365: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4366:
4367: ERRBUF := sqlerrm;
4361: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_ID');
4362: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_ID);
4363: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4364:
4365: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4366:
4367: ERRBUF := sqlerrm;
4368: RETCODE := MSC_UTIL.G_ERROR;
4369: RAISE;
4364:
4365: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4366:
4367: ERRBUF := sqlerrm;
4368: RETCODE := MSC_UTIL.G_ERROR;
4369: RAISE;
4370:
4371: ELSE
4372:
4369: RAISE;
4370:
4371: ELSE
4372:
4373: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
4374:
4375: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4376: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4377: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
4371: ELSE
4372:
4373: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
4374:
4375: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4376: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4377: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
4378: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TP_ID_LID');
4379: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4375: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4376: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4377: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
4378: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TP_ID_LID');
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', 'SR_TP_ID');
4383: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SR_TP_ID);
4380:
4381: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4382: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TP_ID');
4383: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SR_TP_ID);
4384: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4385:
4386: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4387: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_TYPE');
4388: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_TYPE);
4385:
4386: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4387: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_TYPE');
4388: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_TYPE);
4389: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4390:
4391: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4392: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_ID');
4393: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_ID);
4390:
4391: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4392: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_ID');
4393: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_ID);
4394: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4395:
4396: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4397: ERRBUF := sqlerrm;
4398: RETCODE := MSC_UTIL.G_WARNING;
4392: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_ID');
4393: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_ID);
4394: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4395:
4396: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4397: ERRBUF := sqlerrm;
4398: RETCODE := MSC_UTIL.G_WARNING;
4399:
4400: END IF;
4394: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4395:
4396: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4397: ERRBUF := sqlerrm;
4398: RETCODE := MSC_UTIL.G_WARNING;
4399:
4400: END IF;
4401:
4402: END;
4442: END IF;
4443:
4444: COMMIT;
4445: /* Bug7679044 */
4446: IF lv_tp_stat_stale = MSC_UTIL.SYS_YES OR lv_ins_records > lv_tp_id_count * 0.2 THEN
4447: msc_analyse_tables_pk.analyse_table( 'MSC_TP_ID_LID');
4448: lv_tp_stat_stale := MSC_UTIL.SYS_NO;
4449: lv_tp_id_count := lv_tp_id_count + lv_ins_records;
4450: END IF;
4444: COMMIT;
4445: /* Bug7679044 */
4446: IF lv_tp_stat_stale = MSC_UTIL.SYS_YES OR lv_ins_records > lv_tp_id_count * 0.2 THEN
4447: msc_analyse_tables_pk.analyse_table( 'MSC_TP_ID_LID');
4448: lv_tp_stat_stale := MSC_UTIL.SYS_NO;
4449: lv_tp_id_count := lv_tp_id_count + lv_ins_records;
4450: END IF;
4451:
4452: -- ==== Update msc_tp_id_lid with resource_type ==== SRP Changes
4505: NULL;
4506:
4507: WHEN OTHERS THEN
4508:
4509: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4510: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4511: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
4512: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TRADING_PARTNER_SITES');
4513: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4509: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4510: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4511: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
4512: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TRADING_PARTNER_SITES');
4513: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4514:
4515: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4516: FND_MESSAGE.SET_TOKEN('COLUMN', 'TP_ID');
4517: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.TP_ID);
4514:
4515: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4516: FND_MESSAGE.SET_TOKEN('COLUMN', 'TP_ID');
4517: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.TP_ID);
4518: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4519:
4520: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4521: FND_MESSAGE.SET_TOKEN('COLUMN', 'TP_SITE_CODE');
4522: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.TP_SITE_CODE);
4519:
4520: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4521: FND_MESSAGE.SET_TOKEN('COLUMN', 'TP_SITE_CODE');
4522: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.TP_SITE_CODE);
4523: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4524:
4525: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4526:
4527: ERRBUF := sqlerrm;
4521: FND_MESSAGE.SET_TOKEN('COLUMN', 'TP_SITE_CODE');
4522: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.TP_SITE_CODE);
4523: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4524:
4525: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4526:
4527: ERRBUF := sqlerrm;
4528: RETCODE := MSC_UTIL.G_ERROR;
4529: RAISE;
4524:
4525: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4526:
4527: ERRBUF := sqlerrm;
4528: RETCODE := MSC_UTIL.G_ERROR;
4529: RAISE;
4530:
4531: END;
4532:
4534:
4535: COMMIT;
4536: lv_ins_records := 0;
4537: -- ==== Populate msc_tp_site_id_lid with new Supplier Sites ====
4538: IF MSC_CL_COLLECTION.v_apps_ver < MSC_UTIL.G_APPS115 THEN
4539:
4540: /* For 107 and 110 the vendor_id and vendor
4541: site id can be duplicate, therefore we use
4542: the cursors to handle such exceptions, but for 11i we can use a straight
4564: EXCEPTION
4565:
4566: WHEN DUP_VAL_ON_INDEX THEN
4567:
4568: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
4569:
4570: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4571: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4572: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
4566: WHEN DUP_VAL_ON_INDEX THEN
4567:
4568: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
4569:
4570: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4571: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4572: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
4573: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TP_SITE_ID_LID');
4574: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4570: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4571: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4572: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
4573: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TP_SITE_ID_LID');
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_TYPE');
4578: FND_MESSAGE.SET_TOKEN('VALUE', 1);
4575:
4576: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4577: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_TYPE');
4578: FND_MESSAGE.SET_TOKEN('VALUE', 1);
4579: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4580:
4581: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4582: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TP_SITE_ID');
4583: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SR_TP_SITE_ID);
4580:
4581: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4582: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TP_SITE_ID');
4583: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SR_TP_SITE_ID);
4584: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4585:
4586: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4587: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_SITE_ID');
4588: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_SITE_ID);
4585:
4586: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4587: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_SITE_ID');
4588: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_SITE_ID);
4589: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4590:
4591: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4592: ERRBUF := sqlerrm;
4593: RETCODE := MSC_UTIL.G_WARNING;
4587: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_SITE_ID');
4588: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_SITE_ID);
4589: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4590:
4591: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4592: ERRBUF := sqlerrm;
4593: RETCODE := MSC_UTIL.G_WARNING;
4594:
4595: WHEN OTHERS THEN
4589: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4590:
4591: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4592: ERRBUF := sqlerrm;
4593: RETCODE := MSC_UTIL.G_WARNING;
4594:
4595: WHEN OTHERS THEN
4596:
4597: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4593: RETCODE := MSC_UTIL.G_WARNING;
4594:
4595: WHEN OTHERS THEN
4596:
4597: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4598: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4599: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
4600: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TP_SITE_ID_LID');
4601: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4597: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4598: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4599: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
4600: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TP_SITE_ID_LID');
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_TYPE');
4605: FND_MESSAGE.SET_TOKEN('VALUE', 1);
4602:
4603: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4604: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_TYPE');
4605: FND_MESSAGE.SET_TOKEN('VALUE', 1);
4606: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4607:
4608: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4609: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TP_SITE_ID');
4610: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SR_TP_SITE_ID);
4607:
4608: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4609: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TP_SITE_ID');
4610: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SR_TP_SITE_ID);
4611: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4612:
4613: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4614: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_SITE_ID');
4615: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_SITE_ID);
4612:
4613: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4614: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_SITE_ID');
4615: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_SITE_ID);
4616: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4617:
4618: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4619:
4620: ERRBUF := sqlerrm;
4614: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_SITE_ID');
4615: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_SITE_ID);
4616: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4617:
4618: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4619:
4620: ERRBUF := sqlerrm;
4621: RETCODE := MSC_UTIL.G_ERROR;
4622: RAISE;
4617:
4618: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4619:
4620: ERRBUF := sqlerrm;
4621: RETCODE := MSC_UTIL.G_ERROR;
4622: RAISE;
4623:
4624: END;
4625:
4665: END IF;
4666:
4667: COMMIT;
4668: /* Bug7679044 */
4669: IF lv_tp_site_stat_stale = MSC_UTIL.SYS_YES OR lv_ins_records > lv_tp_site_id_count * 0.2 THEN
4670: msc_analyse_tables_pk.analyse_table( 'MSC_TP_SITE_ID_LID');
4671: lv_tp_site_stat_stale := MSC_UTIL.SYS_NO;
4672: lv_tp_site_id_count := lv_tp_site_id_count + lv_ins_records;
4673: END IF;
4667: COMMIT;
4668: /* Bug7679044 */
4669: IF lv_tp_site_stat_stale = MSC_UTIL.SYS_YES OR lv_ins_records > lv_tp_site_id_count * 0.2 THEN
4670: msc_analyse_tables_pk.analyse_table( 'MSC_TP_SITE_ID_LID');
4671: lv_tp_site_stat_stale := MSC_UTIL.SYS_NO;
4672: lv_tp_site_id_count := lv_tp_site_id_count + lv_ins_records;
4673: END IF;
4674:
4675: --========== CUSTOMER SITE ==========
4714: NULL;
4715:
4716: WHEN OTHERS THEN
4717:
4718: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4719: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4720: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
4721: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TRADING_PARTNER_SITES');
4722: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4718: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4719: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4720: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
4721: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TRADING_PARTNER_SITES');
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', 'PARTNER_TYPE');
4726: FND_MESSAGE.SET_TOKEN('VALUE', 1);
4723:
4724: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4725: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_TYPE');
4726: FND_MESSAGE.SET_TOKEN('VALUE', 1);
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', 'TP_ID');
4731: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.TP_ID);
4728:
4729: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4730: FND_MESSAGE.SET_TOKEN('COLUMN', 'TP_ID');
4731: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.TP_ID);
4732: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4733:
4734: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4735: FND_MESSAGE.SET_TOKEN('COLUMN', 'TP_SITE_CODE');
4736: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.TP_SITE_CODE);
4733:
4734: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4735: FND_MESSAGE.SET_TOKEN('COLUMN', 'TP_SITE_CODE');
4736: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.TP_SITE_CODE);
4737: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4738:
4739: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4740: FND_MESSAGE.SET_TOKEN('COLUMN', 'LOCATION');
4741: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.LOCATION);
4738:
4739: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4740: FND_MESSAGE.SET_TOKEN('COLUMN', 'LOCATION');
4741: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.LOCATION);
4742: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4743:
4744: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4745:
4746: ERRBUF := sqlerrm;
4740: FND_MESSAGE.SET_TOKEN('COLUMN', 'LOCATION');
4741: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.LOCATION);
4742: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4743:
4744: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4745:
4746: ERRBUF := sqlerrm;
4747: RETCODE := MSC_UTIL.G_ERROR;
4748: RAISE;
4743:
4744: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4745:
4746: ERRBUF := sqlerrm;
4747: RETCODE := MSC_UTIL.G_ERROR;
4748: RAISE;
4749:
4750: END;
4751:
4754:
4755: COMMIT;
4756:
4757: lv_ins_records := 0;
4758: IF MSC_CL_COLLECTION.v_apps_ver < MSC_UTIL.G_APPS115 AND MSC_CL_COLLECTION.v_apps_ver <> -1 THEN
4759:
4760: /* For 107 and 110 the vendor_id and vendor
4761: site id can be duplicate, therefore we use
4762: the cursors to handle such exceptions, but for 11i we can use a straight
4785: EXCEPTION
4786:
4787: WHEN DUP_VAL_ON_INDEX THEN
4788:
4789: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
4790:
4791: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4792: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4793: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
4787: WHEN DUP_VAL_ON_INDEX THEN
4788:
4789: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
4790:
4791: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4792: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4793: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
4794: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TP_SITE_ID_LID');
4795: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4791: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4792: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4793: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
4794: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TP_SITE_ID_LID');
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_TYPE');
4799: FND_MESSAGE.SET_TOKEN('VALUE', 2);
4796:
4797: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4798: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_TYPE');
4799: FND_MESSAGE.SET_TOKEN('VALUE', 2);
4800: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4801:
4802: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4803: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TP_SITE_ID');
4804: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SR_TP_SITE_ID);
4801:
4802: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4803: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TP_SITE_ID');
4804: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SR_TP_SITE_ID);
4805: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4806:
4807: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4808: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_SITE_ID');
4809: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_SITE_ID);
4806:
4807: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4808: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_SITE_ID');
4809: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_SITE_ID);
4810: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4811:
4812: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4813: ERRBUF := sqlerrm;
4814: RETCODE := MSC_UTIL.G_WARNING;
4808: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_SITE_ID');
4809: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_SITE_ID);
4810: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4811:
4812: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4813: ERRBUF := sqlerrm;
4814: RETCODE := MSC_UTIL.G_WARNING;
4815:
4816: WHEN OTHERS THEN
4810: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4811:
4812: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4813: ERRBUF := sqlerrm;
4814: RETCODE := MSC_UTIL.G_WARNING;
4815:
4816: WHEN OTHERS THEN
4817:
4818: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4814: RETCODE := MSC_UTIL.G_WARNING;
4815:
4816: WHEN OTHERS THEN
4817:
4818: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4819: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4820: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
4821: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TP_SITE_ID_LID');
4822: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4818: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4819: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4820: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
4821: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TP_SITE_ID_LID');
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_TYPE');
4826: FND_MESSAGE.SET_TOKEN('VALUE', 2);
4823:
4824: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4825: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_TYPE');
4826: FND_MESSAGE.SET_TOKEN('VALUE', 2);
4827: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4828:
4829: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4830: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TP_SITE_ID');
4831: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SR_TP_SITE_ID);
4828:
4829: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4830: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TP_SITE_ID');
4831: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SR_TP_SITE_ID);
4832: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4833:
4834: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4835: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_SITE_ID');
4836: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_SITE_ID);
4833:
4834: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4835: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_SITE_ID');
4836: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_SITE_ID);
4837: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4838:
4839: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4840:
4841: ERRBUF := sqlerrm;
4835: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_SITE_ID');
4836: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_SITE_ID);
4837: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4838:
4839: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4840:
4841: ERRBUF := sqlerrm;
4842: RETCODE := MSC_UTIL.G_ERROR;
4843: RAISE;
4838:
4839: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4840:
4841: ERRBUF := sqlerrm;
4842: RETCODE := MSC_UTIL.G_ERROR;
4843: RAISE;
4844:
4845: END;
4846:
4889: END IF;
4890:
4891: COMMIT;
4892: /* Bug7679044 */
4893: IF lv_tp_site_stat_stale = MSC_UTIL.SYS_YES OR lv_ins_records > lv_tp_site_id_count * 0.2 THEN
4894: msc_analyse_tables_pk.analyse_table( 'MSC_TP_SITE_ID_LID');
4895: lv_tp_site_stat_stale := MSC_UTIL.SYS_NO;
4896: lv_tp_site_id_count := lv_tp_site_id_count + lv_ins_records;
4897: END IF;
4891: COMMIT;
4892: /* Bug7679044 */
4893: IF lv_tp_site_stat_stale = MSC_UTIL.SYS_YES OR lv_ins_records > lv_tp_site_id_count * 0.2 THEN
4894: msc_analyse_tables_pk.analyse_table( 'MSC_TP_SITE_ID_LID');
4895: lv_tp_site_stat_stale := MSC_UTIL.SYS_NO;
4896: lv_tp_site_id_count := lv_tp_site_id_count + lv_ins_records;
4897: END IF;
4898:
4899: --================ Collect Vendor/Customer
4936: WHEN OTHERS THEN
4937:
4938: IF SQLCODE IN (-01683,-1653,-1654) THEN
4939:
4940: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4941: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4942: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
4943: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TRADING_PARTNERS');
4944: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4940: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4941: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4942: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
4943: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TRADING_PARTNERS');
4944: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4945:
4946: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4947: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_TYPE');
4948: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_TYPE);
4945:
4946: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4947: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_TYPE');
4948: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_TYPE);
4949: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4950:
4951: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4952: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TP_ID');
4953: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SR_TP_ID);
4950:
4951: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4952: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TP_ID');
4953: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SR_TP_ID);
4954: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4955:
4956: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4957:
4958: ERRBUF := sqlerrm;
4952: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TP_ID');
4953: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SR_TP_ID);
4954: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4955:
4956: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4957:
4958: ERRBUF := sqlerrm;
4959: RETCODE := MSC_UTIL.G_ERROR;
4960: RAISE;
4955:
4956: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4957:
4958: ERRBUF := sqlerrm;
4959: RETCODE := MSC_UTIL.G_ERROR;
4960: RAISE;
4961:
4962: ELSE
4963:
4960: RAISE;
4961:
4962: ELSE
4963:
4964: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
4965:
4966: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4967: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4968: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
4962: ELSE
4963:
4964: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
4965:
4966: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4967: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4968: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
4969: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TRADING_PARTNERS');
4970: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4966: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4967: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4968: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
4969: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TRADING_PARTNERS');
4970: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4971:
4972: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4973: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_TYPE');
4974: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_TYPE);
4971:
4972: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4973: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_TYPE');
4974: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_TYPE);
4975: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4976:
4977: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4978: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TP_ID');
4979: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SR_TP_ID);
4976:
4977: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4978: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TP_ID');
4979: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SR_TP_ID);
4980: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4981:
4982: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4983: ERRBUF := sqlerrm;
4984: RETCODE := MSC_UTIL.G_WARNING;
4978: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TP_ID');
4979: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SR_TP_ID);
4980: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4981:
4982: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4983: ERRBUF := sqlerrm;
4984: RETCODE := MSC_UTIL.G_WARNING;
4985:
4986: END IF;
4980: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4981:
4982: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4983: ERRBUF := sqlerrm;
4984: RETCODE := MSC_UTIL.G_WARNING;
4985:
4986: END IF;
4987:
4988: END;
5006: mtps.CITY = c_rec.CITY,
5007: mtps.STATE = c_rec.STATE,
5008: mtps.COUNTRY = c_rec.COUNTRY,
5009: mtps.SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID,
5010: mtps.DELETED_FLAG= MSC_UTIL.SYS_NO,
5011: mtps.REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
5012: mtps.SHIPPING_CONTROL=c_rec.SHIPPING_CONTROL,
5013: mtps.LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
5014: mtps.LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
5021: WHEN OTHERS THEN
5022:
5023: IF SQLCODE IN (-1653,-1654) THEN
5024:
5025: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
5026: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
5027: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
5028: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TRADING_PARTNER_SITES');
5029: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
5025: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
5026: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
5027: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
5028: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TRADING_PARTNER_SITES');
5029: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
5030:
5031: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
5032: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_TYPE');
5033: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_TYPE);
5030:
5031: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
5032: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_TYPE');
5033: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_TYPE);
5034: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
5035:
5036: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
5037: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TP_SITE_ID');
5038: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SR_TP_SITE_ID);
5035:
5036: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
5037: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TP_SITE_ID');
5038: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SR_TP_SITE_ID);
5039: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
5040:
5041: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
5042:
5043: ERRBUF := sqlerrm;
5037: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TP_SITE_ID');
5038: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SR_TP_SITE_ID);
5039: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
5040:
5041: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
5042:
5043: ERRBUF := sqlerrm;
5044: RETCODE := MSC_UTIL.G_ERROR;
5045: RAISE;
5040:
5041: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
5042:
5043: ERRBUF := sqlerrm;
5044: RETCODE := MSC_UTIL.G_ERROR;
5045: RAISE;
5046:
5047: ELSE
5048:
5045: RAISE;
5046:
5047: ELSE
5048:
5049: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
5050:
5051: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
5052: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
5053: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
5047: ELSE
5048:
5049: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
5050:
5051: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
5052: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
5053: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
5054: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TRADING_PARTNER_SITES');
5055: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
5051: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
5052: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
5053: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
5054: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TRADING_PARTNER_SITES');
5055: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
5056:
5057: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
5058: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_TYPE');
5059: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_TYPE);
5056:
5057: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
5058: FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_TYPE');
5059: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_TYPE);
5060: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
5061:
5062: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
5063: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TP_SITE_ID');
5064: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SR_TP_SITE_ID);
5061:
5062: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
5063: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TP_SITE_ID');
5064: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SR_TP_SITE_ID);
5065: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
5066:
5067: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
5068: ERRBUF := sqlerrm;
5069: RETCODE := MSC_UTIL.G_WARNING;
5063: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TP_SITE_ID');
5064: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SR_TP_SITE_ID);
5065: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
5066:
5067: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
5068: ERRBUF := sqlerrm;
5069: RETCODE := MSC_UTIL.G_WARNING;
5070:
5071: END IF;
5065: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
5066:
5067: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
5068: ERRBUF := sqlerrm;
5069: RETCODE := MSC_UTIL.G_WARNING;
5070:
5071: END IF;
5072:
5073: END;
5076:
5077: COMMIT;
5078:
5079: /* analyse the key mapping tables */
5080: IF lv_tp_stat_stale = MSC_UTIL.SYS_YES THEN
5081: msc_analyse_tables_pk.analyse_table( 'MSC_TP_ID_LID');
5082: END IF;
5083: IF lv_tp_site_stat_stale = MSC_UTIL.SYS_YES THEN
5084: msc_analyse_tables_pk.analyse_table( 'MSC_TP_SITE_ID_LID');
5079: /* analyse the key mapping tables */
5080: IF lv_tp_stat_stale = MSC_UTIL.SYS_YES THEN
5081: msc_analyse_tables_pk.analyse_table( 'MSC_TP_ID_LID');
5082: END IF;
5083: IF lv_tp_site_stat_stale = MSC_UTIL.SYS_YES THEN
5084: msc_analyse_tables_pk.analyse_table( 'MSC_TP_SITE_ID_LID');
5085: END IF;
5086:
5087: EXCEPTION
5087: EXCEPTION
5088:
5089: WHEN OTHERS THEN
5090:
5091: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
5092:
5093: ERRBUF := sqlerrm;
5094: RETCODE := MSC_UTIL.G_ERROR;
5095: RAISE;
5090:
5091: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
5092:
5093: ERRBUF := sqlerrm;
5094: RETCODE := MSC_UTIL.G_ERROR;
5095: RAISE;
5096:
5097: END GENERATE_TRADING_PARTNER_KEYS;
5098:
5107:
5108: IF (MSC_CL_COLLECTION.v_prec_defined = FALSE) THEN
5109: SELECT delete_ods_data,org_group, supplier_capacity, atp_rules,
5110: bom, bor, calendar_check, demand_class,ITEM_SUBSTITUTES, forecast, item,
5111: decode(MSC_CL_COLLECTION.v_apps_ver, MSC_UTIL.G_APPS122, MSC_UTIL.SYS_NO, kpi_targets_bis), mds, mps, oh, parameter, planners,
5112: projects, po, reservations, nra, safety_stock,
5113: sales_order, sourcing_history, sourcing, sub_inventories,
5114: customer, supplier, unit_numbers, uom, user_supply_demand, wip, user_comp_association,po_receipts,
5115: bom_sn_flag, bor_sn_flag, item_sn_flag, oh_sn_flag,
5177: /* Initialize the global prec record variable */
5178:
5179: SELECT delete_ods_data,org_group, supplier_capacity, atp_rules,
5180: bom, bor, calendar_check, demand_class,ITEM_SUBSTITUTES, forecast, item,
5181: decode(MSC_CL_COLLECTION.v_apps_ver, MSC_UTIL.G_APPS122, MSC_UTIL.SYS_NO, kpi_targets_bis), mds, mps, oh, parameter, planners,
5182: projects, po, reservations, nra, safety_stock,
5183: sales_order, sourcing_history, sourcing, sub_inventories,
5184: customer, supplier, unit_numbers, uom, user_supply_demand, wip, user_comp_association,
5185: po_receipts,