[Home] [Help]
PACKAGE BODY: APPS.MSC_CL_SETUP_ODS_LOAD
Source
1 PACKAGE BODY MSC_CL_SETUP_ODS_LOAD AS -- specification
2 /* $Header: MSCLSTPB.pls 120.9.12010000.3 2009/01/15 06:32:56 lsindhur ship $ */
3 -- SYS_YES Number:= MSC_UTIL.SYS_YES ;
4 -- SYS_NO Number:= MSC_CL_COLLECTION.SYS_NO ;
5 -- G_SUCCESS NUMBER := MSC_CL_COLLECTION.G_SUCCESS;
6 -- G_WARNING NUMBER := MSC_CL_COLLECTION.G_WARNING;
7 -- G_ERROR NUMBER := MSC_CL_COLLECTION.G_ERROR;
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;
16 -- G_APPS115 NUMBER :=MSC_CL_COLLECTION.G_APPS115;
17 -- G_APPS107 NUMBER :=MSC_CL_COLLECTION.G_APPS107;
18 -- G_APPS120 NUMBER :=MSC_CL_COLLECTION.G_APPS120;
19 -- G_ALL_ORGANIZATIONS VARCHAR2(6):= MSC_CL_COLLECTION.G_ALL_ORGANIZATIONS;
20
21 FUNCTION LINK_SUPPLY_TOP_LINK_ID
22 RETURN BOOLEAN
23 IS
24 lv_task_start_time DATE;
25 lv_tbl VARCHAR2(30);
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';
35 END IF;
36
37 EXECUTE IMMEDIATE
38 ' update ' || lv_tbl || ' s '
39 ||' set s.top_transaction_id = '
40 || ' ( select nwk.top_transaction_id '
41 || ' from msc_job_operation_networks nwk '
42 || ' where nwk.to_transaction_id = s.transaction_id '
43 || ' and nwk.plan_id = s.plan_id '
44 || ' and nwk.sr_instance_id = s.sr_instance_id '
45 || ' and nwk.top_transaction_id is not null '
46 || ' and nwk.plan_id = -1 '
47 || ' and rownum = 1 )'
48 || ' WHERE s.plan_id = -1 '
49 || ' and s.sr_instance_id = '|| MSC_CL_COLLECTION.v_instance_id
50 || ' and s.order_type = 70'; /* eam supply order type */
51
52 COMMIT;
53
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
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;
70
71 -- ============== CLEANSE DATA =================
72
73 FUNCTION CLEANSE_DATA RETURN BOOLEAN
74 IS
75
76 ERRBUF VARCHAR2(2048);
77 RETCODE NUMBER;
78
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
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);
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;
102
103 COMMIT;
104
105 ELSE
106
107 ROLLBACK;
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);
117
118 RETURN FALSE;
119
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
129
130 WHEN OTHERS THEN
131
132 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
133
134 RAISE;
135
136 -- RETURN FALSE;
137
138 END CLEANSE_DATA;
139
140 FUNCTION TRANSFORM_KEYS RETURN BOOLEAN IS
141
142 lv_default_category_set_id NUMBER;
143 lv_control_flag NUMBER;
144 lv_msc_tp_coll_window NUMBER;
145 lvs_request_id NumTblTyp := NumTblTyp(0);
146 lv_out NUMBER;
147 BEGIN
148
149 SELECT decode(nvl(fnd_profile.value('MSC_PURGE_ST_CONTROL'),'N'),'Y',1,2)
150 INTO lv_control_flag
151 FROM dual;
152
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
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
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
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
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
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;
194 END IF;
195 END IF;
196 lvs_request_id.EXTEND(1);
197
198 --Submit request for Items and Category Sets Key Generation
199 lvs_request_id(1) := FND_REQUEST.SUBMIT_REQUEST(
200 'MSC',
201 'MSCITTK',
202 NULL, -- description
203 NULL, -- start date
204 FALSE, -- TRUE,
205 MSC_CL_COLLECTION.v_instance_id);
206 COMMIT;
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;
216
217 --Submit request for Trading Parteners Key Generation
218 lvs_request_id(2) := FND_REQUEST.SUBMIT_REQUEST(
219 'MSC',
220 'MSCTPTK',
221 NULL, -- description
222 NULL, -- start date
223 FALSE, -- TRUE,
224 MSC_CL_COLLECTION.v_instance_id);
225
226 COMMIT;
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;
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;
248
249 END LOOP;
250
251
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
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
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
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
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
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;
293 END IF;
294 END IF;
295
296
297 RETURN TRUE;
298
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;
308
309 /* CP-ACK starts */
310 --==================================================
311 -- PROCEDURE : LOAD_CALENDAR_SET_UP
312 -- This procedure will load all calendar dates
313 -- into MSC_CALENDAR_DATES tables.
314 -- These code lines were part of LOAD_CALENDAR_DATE
315 -- procedure but we are now seperating this since
316 -- PO Acknowledgment collections need Calendar Dates
317 -- data before processing Acknowledgment records.
318 -- =================================================
319 PROCEDURE LOAD_CALENDAR_SET_UP IS
320
321 --for loading in MSC_CALENDAR_ASSIGNMENTS
322 CURSOR c7 IS
323 SELECT distinct
324 msca.ASSOCIATION_TYPE,
325 msca.CALENDAR_CODE,
326 msca.CALENDAR_TYPE,
327 til.TP_ID PARTNER_ID,
328 tsil.TP_SITE_ID PARTNER_SITE_ID,
329 msca.ORGANIZATION_ID,
330 msca.SR_INSTANCE_ID,
331 mtil.TP_ID CARRIER_PARTNER_ID,
332 msca.PARTNER_TYPE,
333 msca.ASSOCIATION_LEVEL,
334 msca.SHIP_METHOD_CODE
335 FROM MSC_TP_ID_LID til,
336 MSC_TP_SITE_ID_LID tsil,
337 MSC_ST_CALENDAR_ASSIGNMENTS msca,
338 MSC_TP_ID_LID mtil
339 WHERE til.SR_INSTANCE_ID(+)= msca.SR_INSTANCE_ID
340 AND til.SR_TP_ID(+)= msca.PARTNER_ID
341 AND til.PARTNER_TYPE(+)= msca.PARTNER_TYPE
342 AND tsil.SR_INSTANCE_ID(+)= msca.SR_INSTANCE_ID
343 AND tsil.SR_TP_SITE_ID(+)= msca.PARTNER_SITE_ID
344 AND tsil.PARTNER_TYPE(+)= msca.PARTNER_TYPE
345 AND msca.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
346 AND mtil.SR_INSTANCE_ID(+)= msca.SR_INSTANCE_ID
347 AND mtil.SR_TP_ID(+)= msca.CARRIER_PARTNER_ID
348 AND mtil.PARTNER_TYPE(+)=4;
349
350
351 --If instance type is not 'others', then insert into msc_calendars those calendars present in msc_calendar_dates
352 CURSOR c5 IS
353 SELECT distinct
354 mscd.CALENDAR_CODE,
355 mscd.CALENDAR_START_DATE,
356 mscd.CALENDAR_END_DATE,
357 mscd.DESCRIPTION,
358 mscd.SR_INSTANCE_ID
359 FROM MSC_ST_CALENDAR_DATES mscd
360 WHERE mscd.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
361
362
363
364 --Calculate the first and last working days for each of the calendars in MSC_CALENDARS
365 CURSOR c6 IS
366 SELECT
367 Min(CALENDAR_DATE) FIRST_WORKING_DATE,
368 Max(CALENDAR_DATE) LAST_WORKING_DATE,
369 CALENDAR_CODE,
370 SR_INSTANCE_ID
371 FROM MSC_CALENDAR_DATES mscd
372 WHERE mscd.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
373 and seq_num is not null
374 GROUP BY CALENDAR_CODE, SR_INSTANCE_ID;
375
376
377
378 CURSOR c1 IS
379 SELECT
380 mscd.CALENDAR_DATE,
381 mscd.CALENDAR_CODE,
382 mscd.SEQ_NUM,
383 mscd.NEXT_SEQ_NUM,
384 mscd.PRIOR_SEQ_NUM,
385 mscd.NEXT_DATE,
386 mscd.PRIOR_DATE,
387 mscd.CALENDAR_START_DATE,
388 mscd.CALENDAR_END_DATE,
389 mscd.DESCRIPTION,
390 mscd.EXCEPTION_SET_ID,
391 mscd.SR_INSTANCE_ID
392 FROM MSC_ST_CALENDAR_DATES mscd
393 WHERE mscd.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
394
395 CURSOR c2 IS
396 SELECT
397 mspsd.CALENDAR_CODE,
398 mspsd.EXCEPTION_SET_ID,
399 mspsd.PERIOD_START_DATE,
400 mspsd.PERIOD_SEQUENCE_NUM,
401 substrb(mspsd.PERIOD_NAME,1,3) PERIOD_NAME, --added for the NLS bug3463401
402 mspsd.NEXT_DATE,
403 mspsd.PRIOR_DATE,
404 mspsd.SR_INSTANCE_ID
405 FROM MSC_ST_PERIOD_START_DATES mspsd
406 WHERE mspsd.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
407
408 CURSOR c3 IS
409 SELECT
410 mscysd.CALENDAR_CODE,
411 mscysd.EXCEPTION_SET_ID,
412 mscysd.YEAR_START_DATE,
413 mscysd.SR_INSTANCE_ID
414 FROM MSC_ST_CAL_YEAR_START_DATES mscysd
415 WHERE mscysd.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
416
417 CURSOR c4 IS
418 SELECT
419 mscwsd.CALENDAR_CODE,
420 mscwsd.EXCEPTION_SET_ID,
421 mscwsd.WEEK_START_DATE,
422 mscwsd.NEXT_DATE,
423 mscwsd.PRIOR_DATE,
424 mscwsd.SEQ_NUM,
425 mscwsd.SR_INSTANCE_ID
426 FROM MSC_ST_CAL_WEEK_START_DATES mscwsd
427 WHERE mscwsd.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
428
429 c_count NUMBER:= 0;
430
431 lv_sql_stmt VARCHAR2(5000);
432 lv_sql_ins VARCHAR2(5000);
433 lb_FetchComplete Boolean;
434 ln_rows_to_fetch Number := nvl(TO_NUMBER( FND_PROFILE.VALUE('MRP_PURGE_BATCH_SIZE')),75000);
435
436
437 TYPE CharTblTyp IS TABLE OF VARCHAR2(250);
438 TYPE NumTblTyp IS TABLE OF NUMBER;
439 TYPE dateTblTyp IS TABLE OF DATE;
440
441 lb_CALENDAR_DATE dateTblTyp;
442 lb_CALENDAR_CODE CharTblTyp;
443 lb_SEQ_NUM NumTblTyp;
444 lb_NEXT_SEQ_NUM NumTblTyp;
445 lb_PRIOR_SEQ_NUM NumTblTyp;
446 lb_NEXT_DATE dateTblTyp;
447 lb_PRIOR_DATE dateTblTyp;
448 lb_CALENDAR_START_DATE dateTblTyp;
449 lb_CALENDAR_END_DATE dateTblTyp;
450 lb_DESCRIPTION CharTblTyp;
451 lb_EXCEPTION_SET_ID NumTblTyp;
452 lb_SR_INSTANCE_ID NumTblTyp;
453
454 BEGIN
455
456
457 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
458
459 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_PERIOD_START_DATES', MSC_CL_COLLECTION.v_instance_id, NULL);
460
461 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_CAL_YEAR_START_DATES', MSC_CL_COLLECTION.v_instance_id, NULL);
462
463 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_CAL_WEEK_START_DATES', MSC_CL_COLLECTION.v_instance_id, NULL);
464
465 END IF;
466
467 -- Calendar Dates --
468
469 if (MSC_CL_COLLECTION.v_is_partial_refresh or MSC_CL_COLLECTION.v_is_complete_refresh ) THEN
470 UPDATE MSC_CALENDAR_DATES
471 SET DELETED_FLAG= MSC_UTIL.SYS_YES,
472 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
473 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
474 WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
475
476
477 COMMIT;
478 lv_sql_stmt :=
479 ' INSERT INTO MSC_CALENDAR_DATES '
480 ||'( CALENDAR_DATE, '
481 ||' CALENDAR_CODE, '
482 ||' SEQ_NUM, '
483 ||' NEXT_SEQ_NUM, '
484 ||' PRIOR_SEQ_NUM, '
485 ||' NEXT_DATE, '
486 ||' PRIOR_DATE, '
487 ||' CALENDAR_START_DATE, '
488 ||' CALENDAR_END_DATE, '
489 ||' DESCRIPTION, '
490 ||' EXCEPTION_SET_ID, '
491 ||' SR_INSTANCE_ID, '
492 ||' REFRESH_NUMBER, '
493 ||' DELETED_FLAG, '
494 ||' LAST_UPDATE_DATE, '
495 ||' LAST_UPDATED_BY, '
496 ||' CREATION_DATE, '
497 ||' CREATED_BY) '
498 ||' VALUES '
499 ||'( :CALENDAR_DATE, '
500 ||' :CALENDAR_CODE, '
501 ||' :SEQ_NUM, '
502 ||' :NEXT_SEQ_NUM, '
503 ||' :PRIOR_SEQ_NUM, '
504 ||' :NEXT_DATE, '
505 ||' :PRIOR_DATE, '
506 ||' :CALENDAR_START_DATE, '
507 ||' :CALENDAR_END_DATE, '
508 ||' :DESCRIPTION, '
509 ||' :EXCEPTION_SET_ID, '
510 ||' :SR_INSTANCE_ID, '
511 ||' :v_last_collection_id, '
512 ||' :SYS_NO, '
513 ||' :v_current_date, '
514 ||' :v_current_user, '
515 ||' :v_current_date, '
516 ||' :v_current_user ) ';
517
518 OPEN c1;
519 IF (c1%ISOPEN) THEN
520 LOOP
521
522 --
523 -- Retrieve the next set of rows if we are currently not in the
524 -- middle of processing a fetched set or rows.
525 --
526 IF (lb_FetchComplete) THEN
527 EXIT;
528 END IF;
529
530 -- Fetch the next set of rows
531 FETCH c1 BULK COLLECT INTO lb_CALENDAR_DATE ,
532 lb_CALENDAR_CODE ,
533 lb_SEQ_NUM ,
534 lb_NEXT_SEQ_NUM ,
535 lb_PRIOR_SEQ_NUM ,
536 lb_NEXT_DATE ,
537 lb_PRIOR_DATE ,
538 lb_CALENDAR_START_DATE ,
539 lb_CALENDAR_END_DATE ,
540 lb_DESCRIPTION ,
541 lb_EXCEPTION_SET_ID ,
542 lb_SR_INSTANCE_ID
543 LIMIT ln_rows_to_fetch;
544
545 -- Since we are only fetching records if either (1) this is the first
546 -- fetch or (2) the previous fetch did not retrieve all of the
547 -- records, then at least one row should always be fetched. But
548 -- checking just to make sure.
549 EXIT WHEN lb_CALENDAR_CODE.count = 0;
550
551 -- Check if all of the rows have been fetched. If so, indicate that
552 -- the fetch is complete so that another fetch is not made.
553 -- Additional check is introduced for the following reasons
554 -- In 9i, the table of records gets modified but in 8.1.6 the table of records is
555 -- unchanged after the fetch(bug#2995144)
556 IF (c1%NOTFOUND) THEN
557 lb_FetchComplete := TRUE;
558 END IF;
559
560 FOR j IN 1..lb_CALENDAR_CODE.COUNT LOOP
561
562 BEGIN
563
564 --IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
565
566 UPDATE MSC_CALENDAR_DATES
567 SET
568 SEQ_NUM= lb_SEQ_NUM(j),
569 NEXT_SEQ_NUM= lb_NEXT_SEQ_NUM(j),
570 PRIOR_SEQ_NUM= lb_PRIOR_SEQ_NUM(j),
571 NEXT_DATE= lb_NEXT_DATE(j),
572 PRIOR_DATE= lb_PRIOR_DATE(j),
573 CALENDAR_START_DATE= lb_CALENDAR_START_DATE(j),
574 CALENDAR_END_DATE= lb_CALENDAR_END_DATE(j),
575 DESCRIPTION= lb_DESCRIPTION(j),
576 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
577 Deleted_Flag= MSC_UTIL.SYS_NO,
578 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
579 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
580 WHERE CALENDAR_DATE= lb_CALENDAR_DATE(j)
581 AND CALENDAR_CODE= lb_CALENDAR_CODE(j)
582 AND EXCEPTION_SET_ID= lb_EXCEPTION_SET_ID(j)
583 AND SR_INSTANCE_ID= lb_SR_INSTANCE_ID(j);
584
585 --END IF;
586
587 IF SQL%NOTFOUND THEN
588 EXECUTE IMMEDIATE lv_sql_stmt
589 USING
590 lb_CALENDAR_DATE(j),
591 lb_CALENDAR_CODE(j),
592 lb_SEQ_NUM(j),
593 lb_NEXT_SEQ_NUM(j),
594 lb_PRIOR_SEQ_NUM(j),
595 lb_NEXT_DATE(j),
596 lb_PRIOR_DATE(j),
597 lb_CALENDAR_START_DATE(j),
598 lb_CALENDAR_END_DATE(j),
599 lb_DESCRIPTION(j),
600 lb_EXCEPTION_SET_ID(j),
601 lb_SR_INSTANCE_ID(j),
602 MSC_CL_COLLECTION.v_last_collection_id,
603 MSC_UTIL.SYS_NO,
604 MSC_CL_COLLECTION.v_current_date,
605 MSC_CL_COLLECTION.v_current_user,
606 MSC_CL_COLLECTION.v_current_date,
607 MSC_CL_COLLECTION.v_current_user ;
608
609 END IF;
610
611
612 EXCEPTION
613 WHEN OTHERS THEN
614
615
616 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
617
618 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
619 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
620 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
621 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CALENDAR_DATES');
622 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
623
624 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
625 RAISE;
626
627 ELSE
628 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
629
630 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
631 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
632 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
633 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CALENDAR_DATES');
634 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
635
636 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
637 FND_MESSAGE.SET_TOKEN('COLUMN', 'EXCEPTION_SET_ID');
638 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( lb_EXCEPTION_SET_ID(j)));
639 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
640
641 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
642 FND_MESSAGE.SET_TOKEN('COLUMN', 'CALENDAR_DATE');
643 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(lb_CALENDAR_DATE(j)));
644 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
645
646 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
647 FND_MESSAGE.SET_TOKEN('COLUMN', 'CALENDAR_CODE');
648 FND_MESSAGE.SET_TOKEN('VALUE', lb_CALENDAR_CODE(j));
649 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
650
651 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
652 END IF;
653
654 END;
655
656 END LOOP;
657 END LOOP;
658 END IF;
659 CLOSE c1;
660 COMMIT;
661
662
663 DELETE MSC_CALENDAR_DATES
664 WHERE DELETED_FLAG= MSC_UTIL.SYS_YES
665 AND SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
666
667 COMMIT;
668
669 msc_analyse_tables_pk.analyse_table( 'MSC_CALENDAR_DATES');
670
671
672 END IF;
673
674 --If it is complete or partial or continuous refresh, delete existing calendars in the current instance from MSC_CALENDARS
675
676
677 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
678 MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_CALENDARS', MSC_CL_COLLECTION.v_instance_id, NULL);
679 END IF;
680
681 FOR c_rec IN c5 LOOP
682
683 BEGIN
684 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'cal code='|| c_rec.CALENDAR_CODE);
685 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh OR MSC_CL_COLLECTION.v_is_cont_refresh) THEN
686
687 IF MSC_CL_COLLECTION.v_instance_type <> MSC_UTIL.G_INS_OTHER THEN
688
689 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'cal code='|| c_rec.CALENDAR_CODE);
690
691 INSERT INTO MSC_CALENDARS
692 ( CALENDAR_CODE,
693 DESCRIPTION,
694 CALENDAR_START_DATE,
695 CALENDAR_END_DATE,
696 SR_INSTANCE_ID,
697 REFRESH_ID,
698 LAST_UPDATE_DATE,
699 LAST_UPDATED_BY,
700 CREATION_DATE,
701 CREATED_BY)
702 VALUES
703 ( c_rec.CALENDAR_CODE,
704 c_rec.DESCRIPTION,
705 c_rec.CALENDAR_START_DATE,
706 c_rec.CALENDAR_END_DATE,
707 c_rec.SR_INSTANCE_ID,
708 MSC_CL_COLLECTION.v_last_collection_id,
709 MSC_CL_COLLECTION.v_current_date,
710 MSC_CL_COLLECTION.v_current_user,
711 MSC_CL_COLLECTION.v_current_date,
712 MSC_CL_COLLECTION.v_current_user );
713
714 END IF;
715
716 END IF;
717 EXCEPTION
718 WHEN OTHERS THEN
719
720 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
721
722 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
723 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
724 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
725 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CALENDARS');
726 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
727 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
728 RAISE;
729
730 ELSE
731 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
732
733 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
734 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
735 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
736 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CALENDARS');
737 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
738
739 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
740 FND_MESSAGE.SET_TOKEN('COLUMN', 'CALENDAR_CODE');
741 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CALENDAR_CODE);
742 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
743 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
744 END IF;
745 END;
746 END LOOP;
747
748
749 FOR c_rec IN c6 LOOP
750
751 BEGIN
752
753 UPDATE MSC_CALENDARS
754 SET
755 FIRST_WORKING_DATE = c_rec.FIRST_WORKING_DATE,
756 LAST_WORKING_DATE = c_rec.LAST_WORKING_DATE
757 WHERE CALENDAR_CODE = c_rec.CALENDAR_CODE
758 AND SR_INSTANCE_ID = c_rec. SR_INSTANCE_ID;
759
760 EXCEPTION
761 WHEN OTHERS THEN
762
763 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
764
765 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
766 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
767 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
768 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CALENDARS');
769 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
770 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
771 RAISE;
772
773 ELSE
774 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
775
776 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
777 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
778 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
779 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CALENDARS');
780 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
781 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
782 FND_MESSAGE.SET_TOKEN('COLUMN', 'CALENDAR_CODE');
783 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CALENDAR_CODE);
784 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
785 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
786 END IF;
787 END;
788 END LOOP;
789
790
791
792 --Collection of Calendar Assignments in case of complete, targeted and continuous refresh modes only..
793
794 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 MSC_CL_COLLECTION.v_is_legacy_refresh) THEN
795
796 MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_CALENDAR_ASSIGNMENTS', MSC_CL_COLLECTION.v_instance_id, NULL);
797
798
799 FOR c_rec IN c7 LOOP
800
801 BEGIN
802
803 INSERT INTO MSC_CALENDAR_ASSIGNMENTS
804 ( ASSOCIATION_TYPE,
805 CALENDAR_CODE,
806 CALENDAR_TYPE,
807 PARTNER_ID,
808 PARTNER_SITE_ID,
809 ORGANIZATION_ID,
810 SR_INSTANCE_ID,
811 CARRIER_PARTNER_ID,
812 PARTNER_TYPE,
813 ASSOCIATION_LEVEL,
814 SHIP_METHOD_CODE,
815 REFRESH_NUMBER,
816 LAST_UPDATE_DATE,
817 LAST_UPDATED_BY,
818 CREATION_DATE,
819 CREATED_BY,
820 LAST_UPDATE_LOGIN)
821 VALUES
822 ( c_rec.ASSOCIATION_TYPE,
823 c_rec.CALENDAR_CODE,
824 c_rec.CALENDAR_TYPE,
825 c_rec.PARTNER_ID,
826 c_rec.PARTNER_SITE_ID,
827 c_rec.ORGANIZATION_ID,
828 c_rec.SR_INSTANCE_ID,
829 c_rec.CARRIER_PARTNER_ID,
830 c_rec.PARTNER_TYPE,
831 c_rec.ASSOCIATION_LEVEL,
832 c_rec.SHIP_METHOD_CODE,
833 MSC_CL_COLLECTION.v_last_collection_id,
834 MSC_CL_COLLECTION.v_current_date,
835 MSC_CL_COLLECTION.v_current_user,
836 MSC_CL_COLLECTION.v_current_date,
837 MSC_CL_COLLECTION.v_current_user,
838 MSC_CL_COLLECTION.v_current_user);
839
840
841 EXCEPTION
842 WHEN OTHERS THEN
843
844 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
845
846 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
847 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
848 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
849 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CALENDAR_ASSIGNMENTS');
850 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
851 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
852 RAISE;
853
854 ELSE
855 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
856
857 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
858 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
859 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
860 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CALENDAR_ASSIGNMENTS');
861 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
862 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
863 FND_MESSAGE.SET_TOKEN('COLUMN', 'CALENDAR_CODE');
864 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CALENDAR_CODE);
865 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
866 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
867 END IF;
868 END;
869 END LOOP;
870
871 COMMIT;
872 END IF;
873
874 c_count:= 0;
875
876 FOR c_rec IN c2 LOOP
877
878 BEGIN
879
880 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
881
882 UPDATE MSC_PERIOD_START_DATES
883 SET
884 PERIOD_SEQUENCE_NUM= c_rec.PERIOD_SEQUENCE_NUM,
885 PERIOD_NAME= c_rec.PERIOD_NAME,
886 NEXT_DATE= c_rec.NEXT_DATE,
887 PRIOR_DATE= c_rec.PRIOR_DATE,
888 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
889 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
890 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
891 WHERE CALENDAR_CODE= c_rec.CALENDAR_CODE
892 AND EXCEPTION_SET_ID= c_rec.EXCEPTION_SET_ID
893 AND PERIOD_START_DATE= c_rec.PERIOD_START_DATE
894 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
895
896 END IF;
897
898
899 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
900
901 INSERT INTO MSC_PERIOD_START_DATES
902 ( CALENDAR_CODE,
903 EXCEPTION_SET_ID,
904 PERIOD_START_DATE,
905 PERIOD_SEQUENCE_NUM,
906 PERIOD_NAME,
907 NEXT_DATE,
908 PRIOR_DATE,
909 SR_INSTANCE_ID,
910 REFRESH_NUMBER,
911 LAST_UPDATE_DATE,
912 LAST_UPDATED_BY,
913 CREATION_DATE,
914 CREATED_BY)
915 VALUES
916 ( c_rec.CALENDAR_CODE,
917 c_rec.EXCEPTION_SET_ID,
918 c_rec.PERIOD_START_DATE,
919 c_rec.PERIOD_SEQUENCE_NUM,
920 c_rec.PERIOD_NAME,
921 c_rec.NEXT_DATE,
922 c_rec.PRIOR_DATE,
923 c_rec.SR_INSTANCE_ID,
924 MSC_CL_COLLECTION.v_last_collection_id,
925 MSC_CL_COLLECTION.v_current_date,
926 MSC_CL_COLLECTION.v_current_user,
927 MSC_CL_COLLECTION.v_current_date,
928 MSC_CL_COLLECTION.v_current_user );
929
930 END IF;
931
932 c_count:= c_count+1;
933
934 IF c_count> MSC_CL_COLLECTION.PBS THEN
935 COMMIT;
936 c_count:= 0;
937 END IF;
938
939 EXCEPTION
940
941 WHEN OTHERS THEN
942
943 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
944
945 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
946 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
947 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
948 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_PERIOD_START_DATES');
949 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
950
951 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
952 RAISE;
953
954 ELSE
955 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
956
957 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
958 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
959 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
960 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_PERIOD_START_DATES');
961 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
962
963 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
964 FND_MESSAGE.SET_TOKEN('COLUMN', 'EXCEPTION_SET_ID');
965 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.EXCEPTION_SET_ID));
966 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
967
968 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
969 FND_MESSAGE.SET_TOKEN('COLUMN', 'PERIOD_START_DATE');
970 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.PERIOD_START_DATE));
971 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
972
973 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
974 FND_MESSAGE.SET_TOKEN('COLUMN', 'CALENDAR_CODE');
975 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CALENDAR_CODE);
976 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
977
978 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
979
980 END IF;
981 END;
982
983 END LOOP;
984
985 COMMIT;
986
987 c_count:= 0;
988
989 FOR c_rec IN c3 LOOP
990
991 BEGIN
992
993 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
994
995 UPDATE MSC_CAL_YEAR_START_DATES
996 SET
997 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
998 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
999 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1000 WHERE CALENDAR_CODE= c_rec.CALENDAR_CODE
1001 AND EXCEPTION_SET_ID= c_rec.EXCEPTION_SET_ID
1002 AND YEAR_START_DATE= c_rec.YEAR_START_DATE
1003 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
1004
1005 END IF;
1006
1007
1008 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
1009
1010 INSERT INTO MSC_CAL_YEAR_START_DATES
1011 ( CALENDAR_CODE,
1012 EXCEPTION_SET_ID,
1013 YEAR_START_DATE,
1014 SR_INSTANCE_ID,
1015 REFRESH_NUMBER,
1016 LAST_UPDATE_DATE,
1017 LAST_UPDATED_BY,
1018 CREATION_DATE,
1019 CREATED_BY)
1020 VALUES
1021 ( c_rec.CALENDAR_CODE,
1022 c_rec.EXCEPTION_SET_ID,
1023 c_rec.YEAR_START_DATE,
1024 c_rec.SR_INSTANCE_ID,
1025 MSC_CL_COLLECTION.v_last_collection_id,
1026 MSC_CL_COLLECTION.v_current_date,
1027 MSC_CL_COLLECTION.v_current_user,
1028 MSC_CL_COLLECTION.v_current_date,
1029 MSC_CL_COLLECTION.v_current_user );
1030
1031 END IF;
1032
1033 c_count:= c_count+1;
1034
1035 IF c_count> MSC_CL_COLLECTION.PBS THEN
1036 COMMIT;
1037 c_count:= 0;
1038 END IF;
1039
1040 EXCEPTION
1041 WHEN OTHERS THEN
1042
1043 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
1044 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1045 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1046 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
1047 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CAL_YEAR_START_DATES');
1048 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1049
1050 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1051 RAISE;
1052
1053 ELSE
1054 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1055
1056 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1057 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1058 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
1059 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CAL_YEAR_START_DATES');
1060 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1061
1062 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1063 FND_MESSAGE.SET_TOKEN('COLUMN', 'EXCEPTION_SET_ID');
1064 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.EXCEPTION_SET_ID));
1065 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1066
1067 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1068 FND_MESSAGE.SET_TOKEN('COLUMN', 'YEAR_START_DATE');
1069 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.YEAR_START_DATE));
1070 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1071
1072 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1073 FND_MESSAGE.SET_TOKEN('COLUMN', 'CALENDAR_CODE');
1074 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CALENDAR_CODE);
1075 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1076
1077 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1078 END IF;
1079
1080 END;
1081
1082 END LOOP;
1083
1084 COMMIT;
1085
1086 c_count:= 0;
1087
1088 FOR c_rec IN c4 LOOP
1089
1090 BEGIN
1091
1092 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
1093
1094 UPDATE MSC_CAL_WEEK_START_DATES
1095 SET
1096 NEXT_DATE= c_rec.NEXT_DATE,
1097 PRIOR_DATE= c_rec.PRIOR_DATE,
1098 SEQ_NUM= c_rec.SEQ_NUM,
1099 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1100 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1101 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1102 WHERE CALENDAR_CODE= c_rec.CALENDAR_CODE
1103 AND EXCEPTION_SET_ID= c_rec.EXCEPTION_SET_ID
1104 AND WEEK_START_DATE= c_rec.WEEK_START_DATE
1105 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
1106
1107 END IF;
1108
1109 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
1110
1111 INSERT INTO MSC_CAL_WEEK_START_DATES
1112 ( CALENDAR_CODE,
1113 EXCEPTION_SET_ID,
1114 WEEK_START_DATE,
1115 NEXT_DATE,
1116 PRIOR_DATE,
1117 SEQ_NUM,
1118 SR_INSTANCE_ID,
1119 REFRESH_NUMBER,
1120 LAST_UPDATE_DATE,
1121 LAST_UPDATED_BY,
1122 CREATION_DATE,
1123 CREATED_BY)
1124 VALUES
1125 ( c_rec.CALENDAR_CODE,
1126 c_rec.EXCEPTION_SET_ID,
1127 c_rec.WEEK_START_DATE,
1128 c_rec.NEXT_DATE,
1129 c_rec.PRIOR_DATE,
1130 c_rec.SEQ_NUM,
1131 c_rec.SR_INSTANCE_ID,
1132 MSC_CL_COLLECTION.v_last_collection_id,
1133 MSC_CL_COLLECTION.v_current_date,
1134 MSC_CL_COLLECTION.v_current_user,
1135 MSC_CL_COLLECTION.v_current_date,
1136 MSC_CL_COLLECTION.v_current_user );
1137
1138 END IF;
1139
1140 c_count:= c_count+1;
1141
1142 IF c_count> MSC_CL_COLLECTION.PBS THEN
1143 COMMIT;
1144 c_count:= 0;
1145 END IF;
1146
1147 EXCEPTION
1148 WHEN OTHERS THEN
1149
1150 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
1151 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1152 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1153 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
1154 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CAL_WEEK_START_DATES');
1155 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1156
1157 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1158 RAISE;
1159
1160 ELSE
1161 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1162
1163 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1164 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1165 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_SET_UP');
1166 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CAL_WEEK_START_DATES');
1167 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1168
1169 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1170 FND_MESSAGE.SET_TOKEN('COLUMN', 'EXCEPTION_SET_ID');
1171 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.EXCEPTION_SET_ID));
1172 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1173
1174 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1175 FND_MESSAGE.SET_TOKEN('COLUMN', 'WEEK_START_DATE');
1176 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.WEEK_START_DATE));
1177 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1178
1179 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1180 FND_MESSAGE.SET_TOKEN('COLUMN', 'CALENDAR_CODE');
1181 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CALENDAR_CODE);
1182 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1183
1184 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1185 END IF;
1186 END;
1187
1188 END LOOP;
1189
1190 COMMIT;
1191
1192 END LOAD_CALENDAR_SET_UP;
1193
1194 /* CP-ACK ends */
1195
1196 --==================================================================
1197
1198 PROCEDURE LOAD_CALENDAR_DATE IS
1199
1200 CURSOR c5 IS
1201 SELECT
1202 msrs.DEPARTMENT_ID,
1203 msrs.RESOURCE_ID,
1204 msrs.SHIFT_NUM,
1205 msrs.CAPACITY_UNITS,
1206 msrs.SR_INSTANCE_ID
1207 FROM MSC_ST_RESOURCE_SHIFTS msrs
1208 WHERE msrs.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1209
1210 CURSOR c6 IS
1211 SELECT
1212 mscs.CALENDAR_CODE,
1213 mscs.SHIFT_NUM,
1214 mscs.DAYS_ON,
1215 mscs.DAYS_OFF,
1216 mscs.DESCRIPTION,
1217 mscs.SR_INSTANCE_ID
1218 FROM MSC_ST_CALENDAR_SHIFTS mscs
1219 WHERE mscs.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1220
1221 CURSOR c7 IS
1222 SELECT
1223 mssd.CALENDAR_CODE,
1224 mssd.EXCEPTION_SET_ID,
1225 mssd.SHIFT_NUM,
1226 mssd.SHIFT_DATE,
1227 mssd.SEQ_NUM,
1228 mssd.NEXT_SEQ_NUM,
1229 mssd.PRIOR_SEQ_NUM,
1230 mssd.NEXT_DATE,
1231 mssd.PRIOR_DATE,
1232 mssd.SR_INSTANCE_ID
1233 FROM MSC_ST_SHIFT_DATES mssd
1234 WHERE mssd.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1235
1236 CURSOR c8 IS
1237 SELECT
1238 msrc.DEPARTMENT_ID,
1239 msrc.RESOURCE_ID,
1240 msrc.SHIFT_NUM,
1241 msrc.FROM_DATE,
1242 msrc.TO_DATE,
1243 msrc.FROM_TIME,
1244 msrc.TO_TIME,
1245 msrc.CAPACITY_CHANGE,
1246 msrc.SIMULATION_SET,
1247 msrc.ACTION_TYPE,
1248 msrc.DELETED_FLAG,
1249 msrc.SR_INSTANCE_ID
1250 FROM MSC_ST_RESOURCE_CHANGES msrc
1251 WHERE msrc.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1252 ORDER BY
1253 msrc.DELETED_FLAG;
1254
1255 CURSOR c9 IS
1256 SELECT
1257 msst.CALENDAR_CODE,
1258 msst.SHIFT_NUM,
1259 msst.FROM_TIME,
1260 msst.TO_TIME,
1261 msst.SR_INSTANCE_ID
1262 FROM MSC_ST_SHIFT_TIMES msst
1263 WHERE msst.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1264
1265
1266 CURSOR c10 IS
1267 SELECT
1268 msse.CALENDAR_CODE,
1269 msse.SHIFT_NUM,
1270 msse.EXCEPTION_SET_ID,
1271 msse.EXCEPTION_DATE,
1272 msse.EXCEPTION_TYPE,
1273 msse.SR_INSTANCE_ID
1274 FROM MSC_ST_SHIFT_EXCEPTIONS msse
1275 WHERE msse.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1276
1277 c_count NUMBER:= 0;
1278 lv_sql_stmt varchar2(500);
1279 lv_dblink varchar2(50);
1280 lv_resource_start_time DATE := SYSDATE;
1281 lv_ret_res_ava NUMBER ;
1282 lv_dest_a2m varchar2(128);
1283 lv_instance_code varchar2(10);
1284 lv_res_avail_before_sysdate NUMBER; -- Days
1285
1286 ex_calc_res_avail EXCEPTION;
1287
1288 BEGIN
1289
1290 if ((MSC_CL_COLLECTION.v_is_partial_refresh AND MSC_CL_COLLECTION.v_coll_prec.calendar_flag = MSC_UTIL.SYS_YES) OR
1291 MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_incremental_refresh) then
1292
1293 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
1294
1295 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESOURCE_SHIFTS', MSC_CL_COLLECTION.v_instance_id, NULL);
1296
1297 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_CALENDAR_SHIFTS', MSC_CL_COLLECTION.v_instance_id, NULL);
1298
1299 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SHIFT_DATES', MSC_CL_COLLECTION.v_instance_id, NULL);
1300
1301 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESOURCE_CHANGES', MSC_CL_COLLECTION.v_instance_id, NULL);
1302
1303 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SHIFT_TIMES', MSC_CL_COLLECTION.v_instance_id, NULL);
1304
1305 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SHIFT_EXCEPTIONS', MSC_CL_COLLECTION.v_instance_id, NULL);
1306
1307 END IF;
1308
1309 c_count:= 0;
1310
1311 FOR c_rec IN c5 LOOP
1312
1313 BEGIN
1314
1315 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
1316
1317 UPDATE MSC_RESOURCE_SHIFTS
1318 SET
1319 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1320 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1321 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1322 WHERE DEPARTMENT_ID= c_rec.DEPARTMENT_ID
1323 AND RESOURCE_ID= c_rec.RESOURCE_ID
1324 AND SHIFT_NUM= c_rec.SHIFT_NUM
1325 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
1326
1327 END IF;
1328
1329 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
1330
1331 INSERT INTO MSC_RESOURCE_SHIFTS
1332 ( DEPARTMENT_ID,
1333 RESOURCE_ID,
1334 SHIFT_NUM,
1335 CAPACITY_UNITS,
1336 SR_INSTANCE_ID,
1337 REFRESH_NUMBER,
1338 LAST_UPDATE_DATE,
1339 LAST_UPDATED_BY,
1340 CREATION_DATE,
1341 CREATED_BY)
1342 VALUES
1343 ( c_rec.DEPARTMENT_ID,
1344 c_rec.RESOURCE_ID,
1345 c_rec.SHIFT_NUM,
1346 c_rec.CAPACITY_UNITS,
1347 c_rec.SR_INSTANCE_ID,
1348 MSC_CL_COLLECTION.v_last_collection_id,
1349 MSC_CL_COLLECTION.v_current_date,
1350 MSC_CL_COLLECTION.v_current_user,
1351 MSC_CL_COLLECTION.v_current_date,
1352 MSC_CL_COLLECTION.v_current_user );
1353
1354 END IF;
1355
1356 c_count:= c_count+1;
1357
1358 IF c_count> MSC_CL_COLLECTION.PBS THEN
1359 COMMIT;
1360 c_count:= 0;
1361 END IF;
1362
1363 EXCEPTION
1364 WHEN OTHERS THEN
1365
1366 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
1367 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1368 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1369 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1370 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_SHIFTS');
1371 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1372
1373 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1374 RAISE;
1375
1376 ELSE
1377 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1378
1379 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1380 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1381 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1382 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_SHIFTS');
1383 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1384
1385 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1386 FND_MESSAGE.SET_TOKEN('COLUMN', 'DEPARTMENT_ID');
1387 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.DEPARTMENT_ID));
1388 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1389
1390 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1391 FND_MESSAGE.SET_TOKEN('COLUMN', 'RESOURCE_ID');
1392 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.RESOURCE_ID));
1393 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1394
1395 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1396 FND_MESSAGE.SET_TOKEN('COLUMN', 'SHIFT_NUM');
1397 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SHIFT_NUM));
1398 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1399
1400 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1401 END IF;
1402
1403 END;
1404
1405 END LOOP;
1406
1407 COMMIT;
1408
1409 c_count:= 0;
1410
1411 FOR c_rec IN c6 LOOP
1412
1413 BEGIN
1414
1415 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
1416
1417 UPDATE MSC_CALENDAR_SHIFTS
1418 SET
1419 DAYS_ON= c_rec.DAYS_ON,
1420 DAYS_OFF= c_rec.DAYS_OFF,
1421 DESCRIPTION= c_rec.DESCRIPTION,
1422 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1423 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1424 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1425 WHERE CALENDAR_CODE= c_rec.CALENDAR_CODE
1426 AND SHIFT_NUM= c_rec.SHIFT_NUM
1427 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
1428
1429 END IF;
1430
1431 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
1432
1433 INSERT INTO MSC_CALENDAR_SHIFTS
1434 ( CALENDAR_CODE,
1435 SHIFT_NUM,
1436 DAYS_ON,
1437 DAYS_OFF,
1438 DESCRIPTION,
1439 SR_INSTANCE_ID,
1440 REFRESH_NUMBER,
1441 LAST_UPDATE_DATE,
1442 LAST_UPDATED_BY,
1443 CREATION_DATE,
1444 CREATED_BY)
1445 VALUES
1446 ( c_rec.CALENDAR_CODE,
1447 c_rec.SHIFT_NUM,
1448 c_rec.DAYS_ON,
1449 c_rec.DAYS_OFF,
1450 c_rec.DESCRIPTION,
1451 c_rec.SR_INSTANCE_ID,
1452 MSC_CL_COLLECTION.v_last_collection_id,
1453 MSC_CL_COLLECTION.v_current_date,
1454 MSC_CL_COLLECTION.v_current_user,
1455 MSC_CL_COLLECTION.v_current_date,
1456 MSC_CL_COLLECTION.v_current_user );
1457
1458 END IF;
1459
1460 c_count:= c_count+1;
1461
1462 IF c_count> MSC_CL_COLLECTION.PBS THEN
1463 COMMIT;
1464 c_count:= 0;
1465 END IF;
1466
1467 EXCEPTION
1468
1469 WHEN OTHERS THEN
1470
1471 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
1472 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1473 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1474 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1475 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CALENDAR_SHIFTS');
1476 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1477
1478 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1479 RAISE;
1480
1481 ELSE
1482 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1483
1484 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1485 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1486 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1487 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CALENDAR_SHIFTS');
1488 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1489
1490 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1491 FND_MESSAGE.SET_TOKEN('COLUMN', 'CALENDAR_CODE');
1492 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CALENDAR_CODE);
1493 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1494
1495 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1496 FND_MESSAGE.SET_TOKEN('COLUMN', 'SHIFT_NUM');
1497 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SHIFT_NUM));
1498 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1499
1500 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1501 END IF;
1502
1503 END;
1504
1505 END LOOP;
1506
1507 COMMIT;
1508
1509 c_count:= 0;
1510
1511 FOR c_rec IN c7 LOOP
1512
1513 BEGIN
1514
1515 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
1516
1517 UPDATE MSC_SHIFT_DATES
1518 SET
1519 SEQ_NUM= c_rec.SEQ_NUM,
1520 NEXT_SEQ_NUM= c_rec.NEXT_SEQ_NUM,
1521 PRIOR_SEQ_NUM= c_rec.PRIOR_SEQ_NUM,
1522 NEXT_DATE= c_rec.NEXT_DATE,
1523 PRIOR_DATE= c_rec.PRIOR_DATE,
1524 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1525 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1526 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1527 WHERE CALENDAR_CODE= c_rec.CALENDAR_CODE
1528 AND EXCEPTION_SET_ID= c_rec.EXCEPTION_SET_ID
1529 AND SHIFT_NUM= c_rec.SHIFT_NUM
1530 AND SHIFT_DATE= c_rec.SHIFT_DATE
1531 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
1532
1533 END IF;
1534
1535 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
1536
1537 INSERT INTO MSC_SHIFT_DATES
1538 ( CALENDAR_CODE,
1539 EXCEPTION_SET_ID,
1540 SHIFT_NUM,
1541 SHIFT_DATE,
1542 SEQ_NUM,
1543 NEXT_SEQ_NUM,
1544 PRIOR_SEQ_NUM,
1545 NEXT_DATE,
1546 PRIOR_DATE,
1547 SR_INSTANCE_ID,
1548 REFRESH_NUMBER,
1549 LAST_UPDATE_DATE,
1550 LAST_UPDATED_BY,
1551 CREATION_DATE,
1552 CREATED_BY)
1553 VALUES
1554 ( c_rec.CALENDAR_CODE,
1555 c_rec.EXCEPTION_SET_ID,
1556 c_rec.SHIFT_NUM,
1557 c_rec.SHIFT_DATE,
1558 c_rec.SEQ_NUM,
1559 c_rec.NEXT_SEQ_NUM,
1560 c_rec.PRIOR_SEQ_NUM,
1561 c_rec.NEXT_DATE,
1562 c_rec.PRIOR_DATE,
1563 c_rec.SR_INSTANCE_ID,
1564 MSC_CL_COLLECTION.v_last_collection_id,
1565 MSC_CL_COLLECTION.v_current_date,
1566 MSC_CL_COLLECTION.v_current_user,
1567 MSC_CL_COLLECTION.v_current_date,
1568 MSC_CL_COLLECTION.v_current_user );
1569
1570 END IF;
1571
1572 c_count:= c_count+1;
1573
1574 IF c_count> MSC_CL_COLLECTION.PBS THEN
1575 COMMIT;
1576 c_count:= 0;
1577 END IF;
1578
1579 EXCEPTION
1580 WHEN OTHERS THEN
1581
1582 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
1583 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1584 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1585 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1586 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SHIFT_DATES');
1587 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1588
1589 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1590 RAISE;
1591
1592 ELSE
1593 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1594
1595 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1596 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1597 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1598 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SHIFT_DATES');
1599 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1600
1601 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1602 FND_MESSAGE.SET_TOKEN('COLUMN', 'CALENDAR_CODE');
1603 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CALENDAR_CODE);
1604 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1605
1606 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1607 FND_MESSAGE.SET_TOKEN('COLUMN', 'EXCEPTION_SET_ID');
1608 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.EXCEPTION_SET_ID));
1609 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1610
1611 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1612 FND_MESSAGE.SET_TOKEN('COLUMN', 'SHIFT_DATE');
1613 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SHIFT_DATE));
1614 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1615
1616 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1617 FND_MESSAGE.SET_TOKEN('COLUMN', 'SHIFT_NUM');
1618 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SHIFT_NUM));
1619 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1620
1621 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1622 END IF;
1623
1624 END;
1625
1626 END LOOP;
1627
1628 COMMIT;
1629
1630 c_count:= 0;
1631
1632 FOR c_rec IN c8 LOOP
1633
1634 BEGIN
1635
1636 IF MSC_CL_COLLECTION.v_is_incremental_refresh AND c_rec.DELETED_FLAG= MSC_UTIL.SYS_YES THEN
1637
1638 -- set SR_INSTANCE_ID to negative to indicate a SOFT delete
1639
1640 DELETE MSC_RESOURCE_CHANGES
1641 WHERE DEPARTMENT_ID= c_rec.DEPARTMENT_ID
1642 AND RESOURCE_ID= c_rec.RESOURCE_ID
1643 AND SHIFT_NUM= c_rec.SHIFT_NUM
1644 AND FROM_DATE= c_rec.FROM_DATE
1645 AND NVL(TO_DATE,MSC_UTIL.NULL_DATE)= NVL(c_rec.TO_DATE,MSC_UTIL.NULL_DATE)
1646 AND NVL(FROM_TIME,MSC_UTIL.NULL_VALUE)= NVL(c_rec.FROM_TIME,MSC_UTIL.NULL_VALUE)
1647 AND NVL(TO_TIME,MSC_UTIL.NULL_VALUE)= NVL(c_rec.TO_TIME,MSC_UTIL.NULL_VALUE)
1648 AND SIMULATION_SET= c_rec.SIMULATION_SET
1649 AND ACTION_TYPE= c_rec.ACTION_TYPE
1650 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
1651
1652 ELSE
1653
1654 INSERT INTO MSC_RESOURCE_CHANGES
1655 ( DEPARTMENT_ID,
1656 RESOURCE_ID,
1657 SHIFT_NUM,
1658 FROM_DATE,
1659 TO_DATE,
1660 FROM_TIME,
1661 TO_TIME,
1662 CAPACITY_CHANGE,
1663 SIMULATION_SET,
1664 ACTION_TYPE,
1665 SR_INSTANCE_ID,
1666 REFRESH_NUMBER,
1667 LAST_UPDATE_DATE,
1668 LAST_UPDATED_BY,
1669 CREATION_DATE,
1670 CREATED_BY)
1671 VALUES
1672 ( c_rec.DEPARTMENT_ID,
1673 c_rec.RESOURCE_ID,
1674 c_rec.SHIFT_NUM,
1675 c_rec.FROM_DATE,
1676 c_rec.TO_DATE,
1677 c_rec.FROM_TIME,
1678 c_rec.TO_TIME,
1679 c_rec.CAPACITY_CHANGE,
1680 c_rec.SIMULATION_SET,
1681 c_rec.ACTION_TYPE,
1682 c_rec.SR_INSTANCE_ID,
1683 MSC_CL_COLLECTION.v_last_collection_id,
1684 MSC_CL_COLLECTION.v_current_date,
1685 MSC_CL_COLLECTION.v_current_user,
1686 MSC_CL_COLLECTION.v_current_date,
1687 MSC_CL_COLLECTION.v_current_user );
1688
1689 END IF;
1690
1691 c_count:= c_count+1;
1692
1693 IF c_count> MSC_CL_COLLECTION.PBS THEN
1694 COMMIT;
1695 c_count:= 0;
1696 END IF;
1697
1698 EXCEPTION
1699
1700 WHEN OTHERS THEN
1701
1702 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
1703 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1704 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1705 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1706 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_CHANGES');
1707 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1708
1709 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1710 RAISE;
1711
1712 ELSE
1713 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1714
1715 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1716 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1717 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1718 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_CHANGES');
1719 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1720
1721 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1722 FND_MESSAGE.SET_TOKEN('COLUMN', 'DEPARTMENT_ID');
1723 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.DEPARTMENT_ID));
1724 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1725
1726 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1727 FND_MESSAGE.SET_TOKEN('COLUMN', 'RESOURCE_ID');
1728 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.RESOURCE_ID));
1729 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1730
1731 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1732 FND_MESSAGE.SET_TOKEN('COLUMN', 'SHIFT_NUM');
1733 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SHIFT_NUM));
1734 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1735
1736 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1737 FND_MESSAGE.SET_TOKEN('COLUMN', 'ACTION_TYPE');
1738 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.ACTION_TYPE));
1739 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1740
1741 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1742 FND_MESSAGE.SET_TOKEN('COLUMN', 'SIMULATION_SET');
1743 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SIMULATION_SET);
1744 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1745
1746 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1747 END IF;
1748
1749 END;
1750
1751 END LOOP;
1752
1753 /*ds change change start */
1754 MSC_CL_BOM_ODS_LOAD.LOAD_RES_INST_CHANGE;
1755 /*ds change change end */
1756
1757 COMMIT;
1758
1759 c_count:= 0;
1760
1761 FOR c_rec IN c9 LOOP
1762
1763 BEGIN
1764
1765 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
1766
1767 UPDATE MSC_SHIFT_TIMES
1768 SET
1769 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1770 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1771 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1772 WHERE CALENDAR_CODE= c_rec.CALENDAR_CODE
1773 AND SHIFT_NUM= c_rec.SHIFT_NUM
1774 AND FROM_TIME= c_rec.FROM_TIME
1775 AND TO_TIME= c_rec.TO_TIME
1776 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
1777
1778 END IF;
1779
1780 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
1781
1782 INSERT INTO MSC_SHIFT_TIMES
1783 ( CALENDAR_CODE,
1784 SHIFT_NUM,
1785 FROM_TIME,
1786 TO_TIME,
1787 SR_INSTANCE_ID,
1788 REFRESH_NUMBER,
1789 LAST_UPDATE_DATE,
1790 LAST_UPDATED_BY,
1791 CREATION_DATE,
1792 CREATED_BY)
1793 VALUES
1794 ( c_rec.CALENDAR_CODE,
1795 c_rec.SHIFT_NUM,
1796 c_rec.FROM_TIME,
1797 c_rec.TO_TIME,
1798 c_rec.SR_INSTANCE_ID,
1799 MSC_CL_COLLECTION.v_last_collection_id,
1800 MSC_CL_COLLECTION.v_current_date,
1801 MSC_CL_COLLECTION.v_current_user,
1802 MSC_CL_COLLECTION.v_current_date,
1803 MSC_CL_COLLECTION.v_current_user );
1804
1805 END IF;
1806
1807 c_count:= c_count+1;
1808
1809 IF c_count> MSC_CL_COLLECTION.PBS THEN
1810 COMMIT;
1811 c_count:= 0;
1812 END IF;
1813
1814 EXCEPTION
1815 WHEN OTHERS THEN
1816
1817 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
1818 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1819 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1820 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1821 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SHIFT_TIMES');
1822 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1823
1824 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1825 RAISE;
1826
1827 ELSE
1828 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1829
1830 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1831 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1832 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1833 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SHIFT_TIMES');
1834 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1835
1836 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1837 FND_MESSAGE.SET_TOKEN('COLUMN', 'CALENDAR_CODE');
1838 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CALENDAR_CODE);
1839 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1840
1841 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1842 FND_MESSAGE.SET_TOKEN('COLUMN', 'SHIFT_NUM');
1843 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SHIFT_NUM));
1844 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1845
1846 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1847 FND_MESSAGE.SET_TOKEN('COLUMN', 'FROM_TIME');
1848 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.FROM_TIME));
1849 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1850
1851 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1852 FND_MESSAGE.SET_TOKEN('COLUMN', 'TO_TIME');
1853 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.TO_TIME));
1854 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1855
1856 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1857 END IF;
1858
1859 END;
1860
1861 END LOOP;
1862
1863 COMMIT;
1864
1865 c_count:= 0;
1866
1867 FOR c_rec IN c10 LOOP
1868
1869 BEGIN
1870
1871 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
1872
1873 UPDATE MSC_SHIFT_EXCEPTIONS
1874 SET
1875 EXCEPTION_TYPE= c_rec.EXCEPTION_TYPE,
1876 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1877 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1878 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1879 WHERE CALENDAR_CODE= c_rec.CALENDAR_CODE
1880 AND SHIFT_NUM= c_rec.SHIFT_NUM
1881 AND EXCEPTION_SET_ID= c_rec.EXCEPTION_SET_ID
1882 AND EXCEPTION_DATE= c_rec.EXCEPTION_DATE
1883 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
1884
1885 END IF;
1886
1887 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
1888
1889 INSERT INTO MSC_SHIFT_EXCEPTIONS
1890 ( CALENDAR_CODE,
1891 SHIFT_NUM,
1892 EXCEPTION_SET_ID,
1893 EXCEPTION_DATE,
1894 EXCEPTION_TYPE,
1895 SR_INSTANCE_ID,
1896 REFRESH_NUMBER,
1897 LAST_UPDATE_DATE,
1898 LAST_UPDATED_BY,
1899 CREATION_DATE,
1900 CREATED_BY)
1901 VALUES
1902 ( c_rec.CALENDAR_CODE,
1903 c_rec.SHIFT_NUM,
1904 c_rec.EXCEPTION_SET_ID,
1905 c_rec.EXCEPTION_DATE,
1906 c_rec.EXCEPTION_TYPE,
1907 c_rec.SR_INSTANCE_ID,
1908 MSC_CL_COLLECTION.v_last_collection_id,
1909 MSC_CL_COLLECTION.v_current_date,
1910 MSC_CL_COLLECTION.v_current_user,
1911 MSC_CL_COLLECTION.v_current_date,
1912 MSC_CL_COLLECTION.v_current_user );
1913
1914 END IF;
1915
1916 c_count:= c_count+1;
1917
1918 IF c_count> MSC_CL_COLLECTION.PBS THEN
1919 COMMIT;
1920 c_count:= 0;
1921 END IF;
1922
1923 EXCEPTION
1924 WHEN OTHERS THEN
1925
1926 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
1927 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1928 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1929 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1930 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SHIFT_EXCEPTIONS');
1931 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1932
1933 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1934 RAISE;
1935
1936 ELSE
1937 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1938
1939 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1940 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1941 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CALENDAR_DATE');
1942 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SHIFT_EXCEPTIONS');
1943 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1944
1945 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1946 FND_MESSAGE.SET_TOKEN('COLUMN', 'CALENDAR_CODE');
1947 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CALENDAR_CODE);
1948 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1949
1950 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1951 FND_MESSAGE.SET_TOKEN('COLUMN', 'EXCEPTION_DATE');
1952 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.EXCEPTION_DATE));
1953 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1954
1955 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1956 FND_MESSAGE.SET_TOKEN('COLUMN', 'EXCEPTION_SET_ID');
1957 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.EXCEPTION_SET_ID));
1958 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1959
1960 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1961 FND_MESSAGE.SET_TOKEN('COLUMN', 'SHIFT_NUM');
1962 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SHIFT_NUM));
1963 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1964
1965 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1966 END IF;
1967 END;
1968
1969 END LOOP;
1970
1971 COMMIT;
1972 end if;
1973
1974 if ((MSC_CL_COLLECTION.v_is_partial_refresh AND MSC_CL_COLLECTION.v_coll_prec.bom_flag = MSC_UTIL.SYS_YES) OR
1975 MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_incremental_refresh) then
1976
1977 FND_MESSAGE.SET_NAME('MSC', 'MSC_DP_TASK_START');
1978 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'MSC_CL_BOM_ODS_LOAD.LOAD_RESOURCE');
1979 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1980 if (MSC_CL_COLLECTION.v_bom_refresh_type <> 3) then
1981 MSC_CL_BOM_ODS_LOAD.LOAD_RESOURCE;
1982 end if ;
1983 end if;
1984 IF MSC_CL_COLLECTION.v_recalc_nra= MSC_UTIL.SYS_YES THEN
1985 IF MSC_CL_COLLECTION.v_discrete_flag= MSC_UTIL.SYS_YES THEN
1986 BEGIN
1987 IF MSC_CL_COLLECTION.v_instance_type = MSC_UTIL.G_INS_OTHER THEN
1988 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_NET_RESOURCE_AVAIL',
1989 MSC_CL_COLLECTION.v_instance_id, -1);
1990 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_NET_RES_INST_AVAIL',
1991 MSC_CL_COLLECTION.v_instance_id, -1);
1992 END IF;
1993
1994 SELECT DECODE(M2A_DBLINK,
1995 NULL,'',
1996 '@'||M2A_DBLINK),
1997 DECODE( A2M_DBLINK,
1998 NULL,MSC_UTIL.NULL_DBLINK,
1999 A2M_DBLINK),
2000 INSTANCE_CODE
2001 INTO lv_dblink,
2002 lv_dest_a2m,
2003 lv_instance_code
2004 FROM MSC_APPS_INSTANCES
2005 WHERE INSTANCE_ID=MSC_CL_COLLECTION.v_instance_id;
2006
2007 lv_res_avail_before_sysdate := nvl(TO_NUMBER(FND_PROFILE.VAlUE('MSC_RES_AVAIL_BEFORE_SYSDAT')),1);
2008 IF MSC_CL_COLLECTION.v_instance_type <> MSC_UTIL.G_INS_OTHER THEN
2009 lv_sql_stmt:= 'SELECT nvl(mar.LRD,sysdate)- '||lv_res_avail_before_sysdate
2010 ||' FROM MRP_AP_APPS_INSTANCES_ALL'||lv_dblink||' mar'
2011 ||' WHERE INSTANCE_ID = '||MSC_CL_COLLECTION.v_instance_id
2012 ||' AND INSTANCE_CODE = '''||lv_instance_code||''''
2013 ||' AND nvl(A2M_DBLINK,'''||MSC_UTIL.NULL_DBLINK||''') = '''||lv_dest_a2m||'''' ;
2014 EXECUTE IMMEDIATE lv_sql_stmt INTO lv_resource_start_time;
2015 END IF;
2016 IF MSC_CL_COLLECTION.v_instance_type = MSC_UTIL.G_INS_OTHER THEN
2017 lv_resource_start_time := lv_resource_start_time - nvl(lv_res_avail_before_sysdate,1);
2018 END IF;
2019
2020 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'@@before net res avai : debug 1');
2021 lv_ret_res_ava:=MSC_RESOURCE_AVAILABILITY.CALC_RESOURCE_AVAILABILITY(lv_resource_start_time,MSC_CL_COLLECTION.v_coll_prec.org_group_flag,FALSE);
2022
2023 IF lv_ret_res_ava = 2 THEN
2024 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_CALC_RES_AVAIL_FAIL');
2025 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2026 MSC_CL_COLLECTION.v_warning_flag:=MSC_UTIL.SYS_YES;
2027 ELSIF lv_ret_res_ava <> 0 THEN
2028 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_CALC_RES_AVAIL_FAIL');
2029 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2030 RAISE ex_calc_res_avail;
2031
2032 -- ERRBUF:= FND_MESSAGE.GET;
2033 -- RETCODE:= G_ERROR;
2034
2035 -- ROLLBACK WORK TO SAVEPOINT WORKERS_COMMITTED;
2036 END IF;
2037
2038 EXCEPTION
2039 WHEN OTHERS THEN
2040 RAISE;
2041 END;
2042 END IF;
2043
2044 END IF;
2045
2046 END LOAD_CALENDAR_DATE;
2047
2048 --============= COLLECT Trading Partners ==================================
2049
2050 PROCEDURE LOAD_TRADING_PARTNER IS
2051
2052 lv_sql_stmt VARCHAR2(5000);
2053
2054 CURSOR c1 IS
2055 SELECT
2056 /* SCE Change starts */
2057 decode(mc.COMPANY_ID, MSC_CL_COLLECTION.G_MY_COMPANY_ID, null, mc.COMPANY_ID) COMPANY_ID ,
2058 /* SCE change ends */
2059 mst.ORGANIZATION_CODE,
2060 mst.ORGANIZATION_TYPE,
2061 mst.SR_TP_ID,
2062 mst.DISABLE_DATE,
2063 mst.STATUS,
2064 mst.MASTER_ORGANIZATION,
2065 mst.SOURCE_ORG_ID,
2066 mst.WEIGHT_UOM,
2067 mst.MAXIMUM_WEIGHT,
2068 mst.VOLUME_UOM,
2069 mst.MAXIMUM_VOLUME,
2070 mst.PARTNER_TYPE,
2071 mst.PARTNER_NAME,
2072 mst.PARTNER_NUMBER,
2073 mst.CALENDAR_CODE,
2074 mst.CURRENCY_CODE,
2075 mst.CALENDAR_EXCEPTION_SET_ID,
2076 mst.OPERATING_UNIT,
2077 mst.SR_INSTANCE_ID,
2078 mst.PROJECT_REFERENCE_ENABLED,
2079 mst.PROJECT_CONTROL_LEVEL,
2080 mst.DEMAND_LATENESS_COST,
2081 mst.SUPPLIER_CAP_OVERUTIL_COST,
2082 mst.RESOURCE_CAP_OVERUTIL_COST,
2083 mst.TRANSPORT_CAP_OVER_UTIL_COST,
2084 mst.DEFAULT_ATP_RULE_ID,
2085 mst.DEFAULT_DEMAND_CLASS,
2086 mst.MATERIAL_ACCOUNT,
2087 mst.EXPENSE_ACCOUNT,
2088 tilc.TP_ID MODELED_CUSTOMER_ID,
2089 tsilc.TP_SITE_ID MODELED_CUSTOMER_SITE_ID,
2090 tils.TP_ID MODELED_SUPPLIER_ID,
2091 tsils.TP_SITE_ID MODELED_SUPPLIER_SITE_ID,
2092 mst.USE_PHANTOM_ROUTINGS,
2093 mst.INHERIT_PHANTOM_OP_SEQ,
2094 mst.INHERIT_OC_OP_SEQ_NUM,
2095 mst.BUSINESS_GROUP_ID,
2096 mst.LEGAL_ENTITY,
2097 mst.SET_OF_BOOKS_ID,
2098 mst.CHART_OF_ACCOUNTS_ID,
2099 mst.BUSINESS_GROUP_NAME,
2100 mst.LEGAL_ENTITY_NAME,
2101 mst.OPERATING_UNIT_NAME
2102 FROM MSC_TP_ID_LID tilc,
2103 MSC_TP_ID_LID tils,
2104 MSC_TP_SITE_ID_LID tsilc,
2105 MSC_TP_SITE_ID_LID tsils,
2106 MSC_ST_TRADING_PARTNERS mst,
2107 MSC_COMPANIES MC
2108 WHERE mst.PARTNER_TYPE= 3
2109 /* SCE Change starts */
2110 AND nvl(mst.company_name, MSC_CL_COLLECTION.v_my_company_name) = MC.company_name
2111 -- AND nvl( mst.company_id, -1) = -1 -- commented for aerox
2112 /* SCE Change Ends */
2113 AND mst.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
2114 AND tilc.SR_INSTANCE_ID(+)= MSC_CL_COLLECTION.v_instance_id
2115 AND tilc.PARTNER_TYPE(+)= 2
2116 AND tilc.SR_TP_ID(+)= mst.MODELED_CUSTOMER_ID
2117 AND tils.SR_INSTANCE_ID(+)= MSC_CL_COLLECTION.v_instance_id
2118 AND tils.PARTNER_TYPE(+)= 1
2119 AND tils.SR_TP_ID(+)= mst.MODELED_SUPPLIER_ID
2120 AND tsilc.SR_INSTANCE_ID(+)= MSC_CL_COLLECTION.v_instance_id
2121 AND tsilc.PARTNER_TYPE(+)= 2
2122 AND tsilc.SR_TP_SITE_ID(+)= mst.MODELED_CUSTOMER_SITE_ID
2123 AND tsils.SR_INSTANCE_ID(+)= MSC_CL_COLLECTION.v_instance_id
2124 AND tsils.PARTNER_TYPE(+)= 1
2125 AND tsils.SR_TP_SITE_ID(+)= mst.MODELED_SUPPLIER_SITE_ID;
2126
2127
2128 CURSOR c2 IS
2129 SELECT
2130 mtp.PARTNER_ID,
2131 substrb(msts.PARTNER_ADDRESS,1,1600) PARTNER_ADDRESS,--added for the NLS bug3463401
2132 msts.SR_TP_ID,
2133 msts.SR_TP_SITE_ID,
2134 msts.SR_INSTANCE_ID,
2135 msts.TP_SITE_CODE,
2136 msts.LOCATION,
2137 msts.LONGITUDE,
2138 msts.LATITUDE
2139 FROM MSC_TRADING_PARTNERS mtp,
2140 MSC_ST_TRADING_PARTNER_SITES msts
2141 WHERE msts.PARTNER_TYPE= 3
2142 AND msts.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
2143 AND mtp.SR_TP_ID= msts.SR_TP_ID
2144 AND mtp.PARTNER_TYPE= 3
2145 AND mtp.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
2146
2147 /* For bug#2198339 modified this cursor to bring data only for Vendors-Customers */
2148 CURSOR c3 IS
2149 SELECT DISTINCT
2150 msta.LOCATION_ID,
2151 msta.LOCATION_CODE,
2152 til.TP_ID PARTNER_ID,
2153 tsil.TP_SITE_ID PARTNER_SITE_ID,
2154 msta.organization_id,
2155 msta.SR_INSTANCE_ID
2156 FROM MSC_TP_ID_LID til,
2157 MSC_TP_SITE_ID_LID tsil,
2158 MSC_ST_LOCATION_ASSOCIATIONS msta
2159 WHERE til.SR_INSTANCE_ID= msta.SR_INSTANCE_ID
2160 AND til.SR_TP_ID= msta.SR_TP_ID
2161 AND til.PARTNER_TYPE= msta.PARTNER_TYPE
2162 AND tsil.SR_INSTANCE_ID= msta.SR_INSTANCE_ID
2163 AND tsil.SR_TP_SITE_ID= msta.SR_TP_SITE_ID
2164 AND tsil.PARTNER_TYPE= msta.PARTNER_TYPE
2165 AND msta.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
2166 AND msta.PARTNER_TYPE IN (1,2);
2167
2168 CURSOR c4 IS
2169 SELECT
2170 pc.PARTNER_TYPE,
2171 DECODE( pc.PARTNER_TYPE,
2172 1, til.TP_ID,
2173 2, til.TP_ID,
2174 4, pc.PARTNER_ID) PARTNER_ID,
2175 DECODE( pc.PARTNER_TYPE,
2176 1, tsil.TP_SITE_ID,
2177 2, tsil.TP_SITE_ID,
2178 NULL) PARTNER_SITE_ID,
2179 pc.NAME,
2180 pc.DISPLAY_NAME,
2181 pc.EMAIL,
2182 pc.FAX,
2183 pc.ENABLED_FLAG,
2184 pc.DELETED_FLAG
2185 FROM MSC_TP_ID_LID til,
2186 MSC_TP_SITE_ID_LID tsil,
2187 MSC_ST_PARTNER_CONTACTS pc
2188 WHERE pc.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
2189 AND pc.DELETED_FLAG in (1, 2)
2190 AND til.sr_tp_id(+)= pc.partner_id
2191 AND til.partner_type(+)= DECODE( pc.PARTNER_TYPE,1,1,2,2,NULL)
2192 AND til.sr_instance_id(+)= MSC_CL_COLLECTION.v_instance_id
2193 AND tsil.sr_tp_site_id(+)= pc.partner_site_id
2194 AND tsil.partner_type(+)= DECODE( pc.PARTNER_TYPE,1,1,2,2,NULL)
2195 AND tsil.sr_instance_id(+)= MSC_CL_COLLECTION.v_instance_id
2196 ORDER BY
2197 1,2,3,4 ASC;
2198
2199 /* For bug#2198339 added this cursor to bring Locations associations data only for Organizations */
2200 CURSOR c5 IS
2201 SELECT
2202 mtps.PARTNER_ID,
2203 msta.LOCATION_ID,
2204 msta.LOCATION_CODE,
2205 msta.SR_TP_ID ORGANIZATION_ID,
2206 msta.LOCATION_ID PARTNER_SITE_ID,
2207 msta.SR_INSTANCE_ID
2208 FROM MSC_TRADING_PARTNERS mtps,
2209 MSC_ST_LOCATION_ASSOCIATIONS msta
2210 WHERE msta.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
2211 AND msta.partner_type = 3
2212 AND msta.SR_INSTANCE_ID= mtps.SR_INSTANCE_ID
2213 AND msta.SR_TP_ID= mtps.SR_TP_ID
2214 AND msta.PARTNER_TYPE= mtps.PARTNER_TYPE;
2215
2216 lv_old_partner_type NUMBER:=0;
2217 lv_old_partner_id NUMBER:=0;
2218 lv_old_partner_site_id NUMBER:=0;
2219
2220 c_count NUMBER:= 0;
2221
2222 TYPE OrgCurType IS REF CURSOR;
2223 c_org_exist OrgCurType;
2224 lv_exist pls_integer;
2225
2226 BEGIN
2227
2228 -- Organization
2229
2230 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
2231
2232 DELETE MSC_TRADING_PARTNERS
2233 WHERE sr_instance_id= MSC_CL_COLLECTION.v_instance_id
2234 AND partner_type=3
2235 AND nvl(ORG_SUPPLIER_MAPPED,'N') <> 'Y';
2236
2237 -- MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_TRADING_PARTNERS', MSC_CL_COLLECTION.v_instance_id, NULL,
2238 -- 'AND PARTNER_TYPE=3');
2239
2240 END IF;
2241
2242 FOR c_rec IN c1 LOOP
2243
2244 BEGIN
2245
2246 lv_sql_stmt :=
2247 'UPDATE MSC_TRADING_PARTNERS '
2248 ||' SET '
2249 ||' COMPANY_ID = :company_id,'
2250 ||' ORGANIZATION_CODE = :ORG_CODE,'
2251 ||' ORGANIZATION_TYPE = :ORG_TYPE,'
2252 ||' DISABLE_DATE = :DISABLE_DATE,'
2253 ||' STATUS = :STATUS,'
2254 ||' MASTER_ORGANIZATION = :MASTER_ORG,'
2255 ||' SOURCE_ORG_ID = :SOURCE_ORG_ID,'
2256 ||' WEIGHT_UOM = :WEIGHT_UOM,'
2257 ||' MAXIMUM_WEIGHT = :MAXIMUM_WEIGHT,'
2258 ||' VOLUME_UOM = :VOLUME_UOM,'
2259 ||' MAXIMUM_VOLUME = :MAXIMUM_VOLUME,'
2260 ||' PARTNER_NAME = :PARTNER_NAME,'
2261 ||' PARTNER_NUMBER = :PARTNER_NUMBER,'
2262 ||' CALENDAR_CODE = :CALENDAR_CODE,'
2263 ||' CURRENCY_CODE = :CURRENCY_CODE,'
2264 ||' CALENDAR_EXCEPTION_SET_ID = :CAL_EXP_SET_ID,'
2265 ||' OPERATING_UNIT = :OPERATING_UNIT,'
2266 ||' PROJECT_REFERENCE_ENABLED = :PROJ_REF_ENABLED,'
2267 ||' PROJECT_CONTROL_LEVEL = :PROJ_CON_LEVEL,'
2268 ||' DEMAND_LATENESS_COST = :DMD_LATE_COST,'
2269 ||' SUPPLIER_CAP_OVERUTIL_COST = :SUPP_CAP_OVR_COST,'
2270 ||' RESOURCE_CAP_OVERUTIL_COST = :RES_CAP_OVER_COST,'
2271 ||' TRANSPORT_CAP_OVER_UTIL_COST= :TR_CAP_OV_UTIL_COST,'
2272 ||' MODELED_CUSTOMER_ID = decode(ORG_SUPPLIER_MAPPED,''Y'',MODELED_CUSTOMER_ID,:MOD_CUS_ID),'
2273 ||' MODELED_CUSTOMER_SITE_ID = decode(ORG_SUPPLIER_MAPPED,''Y'',MODELED_CUSTOMER_SITE_ID,:MOD_CUS_SITE_ID),'
2274 ||' MODELED_SUPPLIER_ID = decode(ORG_SUPPLIER_MAPPED,''Y'',MODELED_SUPPLIER_ID,:MOD_SUPP_ID),'
2275 ||' MODELED_SUPPLIER_SITE_ID = decode(ORG_SUPPLIER_MAPPED,''Y'',MODELED_SUPPLIER_SITE_ID,:MOD_SUPP_SITE_ID),'
2276 ||' USE_PHANTOM_ROUTINGS = :USE_PH_ROUTINGS,'
2277 ||' INHERIT_PHANTOM_OP_SEQ = :INH_PH_OP_SEQ,'
2278 ||' INHERIT_OC_OP_SEQ_NUM = :INH_OC_OP_SEQ_NUM,'
2279 ||' DEFAULT_ATP_RULE_ID = :DEF_ATP_RULE_ID,'
2280 ||' DEFAULT_DEMAND_CLASS = :DEF_DEMAND_CLASS,'
2281 ||' MATERIAL_ACCOUNT = :MATERIAL_ACCOUNT,'
2282 ||' EXPENSE_ACCOUNT = :EXPENSE_ACCOUNT,'
2283 ||' SR_BUSINESS_GROUP_ID = :BUSINESS_GROUP_ID,'
2284 ||' SR_LEGAL_ENTITY = :LEGAL_ENTITY,'
2285 ||' SR_SET_OF_BOOKS_ID = :SET_OF_BOOKS_ID,'
2286 ||' SR_CHART_OF_ACCOUNTS_ID = :CHART_OF_ACCOUNTS_ID,'
2287 ||' BUSINESS_GROUP_NAME = :BUSINESS_GROUP_NAME,'
2288 ||' LEGAL_ENTITY_NAME = :LEGAL_ENTITY_NAME,'
2289 ||' OPERATING_UNIT_NAME = :OPERATING_UNIT_NAME,'
2290 ||' REFRESH_NUMBER = :v_last_collection_id,'
2291 ||' LAST_UPDATE_DATE = :v_current_date,'
2292 ||' LAST_UPDATED_BY = :v_current_user'
2293 ||' WHERE SR_TP_ID = :SR_TP_ID'
2294 ||' AND SR_INSTANCE_ID = :SR_INSTANCE_ID'
2295 ||' AND PARTNER_TYPE = :PARTNER_TYPE';
2296
2297
2298 EXECUTE IMMEDIATE lv_sql_stmt
2299 USING c_rec.company_id,
2300 c_rec.ORGANIZATION_CODE,
2301 c_rec.ORGANIZATION_TYPE,
2302 c_rec.DISABLE_DATE,
2303 c_rec.STATUS,
2304 c_rec.MASTER_ORGANIZATION,
2305 c_rec.SOURCE_ORG_ID,
2306 c_rec.WEIGHT_UOM,
2307 c_rec.MAXIMUM_WEIGHT,
2308 c_rec.VOLUME_UOM,
2309 c_rec.MAXIMUM_VOLUME,
2310 c_rec.PARTNER_NAME,
2311 c_rec.PARTNER_NUMBER,
2312 c_rec.CALENDAR_CODE,
2313 c_rec.CURRENCY_CODE,
2314 c_rec.CALENDAR_EXCEPTION_SET_ID,
2315 c_rec.OPERATING_UNIT,
2316 c_rec.PROJECT_REFERENCE_ENABLED,
2317 c_rec.PROJECT_CONTROL_LEVEL,
2318 c_rec.DEMAND_LATENESS_COST,
2319 c_rec.SUPPLIER_CAP_OVERUTIL_COST,
2320 c_rec.RESOURCE_CAP_OVERUTIL_COST,
2321 c_rec.TRANSPORT_CAP_OVER_UTIL_COST,
2322 c_rec.MODELED_CUSTOMER_ID,
2323 c_rec.MODELED_CUSTOMER_SITE_ID,
2324 c_rec.MODELED_SUPPLIER_ID,
2325 c_rec.MODELED_SUPPLIER_SITE_ID,
2326 c_rec.USE_PHANTOM_ROUTINGS,
2327 c_rec.INHERIT_PHANTOM_OP_SEQ,
2328 c_rec.INHERIT_OC_OP_SEQ_NUM,
2329 c_rec.DEFAULT_ATP_RULE_ID,
2330 c_rec.DEFAULT_DEMAND_CLASS,
2331 c_rec.MATERIAL_ACCOUNT,
2332 c_rec.EXPENSE_ACCOUNT,
2333 c_rec.BUSINESS_GROUP_ID,
2334 c_rec.LEGAL_ENTITY,
2335 c_rec.SET_OF_BOOKS_ID,
2336 c_rec.CHART_OF_ACCOUNTS_ID,
2337 c_rec.BUSINESS_GROUP_NAME,
2338 c_rec.LEGAL_ENTITY_NAME,
2339 c_rec.OPERATING_UNIT_NAME,
2340 MSC_CL_COLLECTION.v_last_collection_id,
2341 MSC_CL_COLLECTION.v_current_date,
2342 MSC_CL_COLLECTION.v_current_user,
2343 c_rec.SR_TP_ID,
2344 c_rec.SR_INSTANCE_ID,
2345 c_rec.PARTNER_TYPE;
2346
2347
2348 IF SQL%NOTFOUND THEN
2349
2350 INSERT INTO MSC_TRADING_PARTNERS
2351 ( PARTNER_ID,
2352 /* SCE change starts */
2353 COMPANY_ID,
2354 /* SCE change ends */
2355 ORGANIZATION_CODE,
2356 ORGANIZATION_TYPE,
2357 SR_TP_ID,
2358 DISABLE_DATE,
2359 STATUS,
2360 MASTER_ORGANIZATION,
2361 SOURCE_ORG_ID,
2362 WEIGHT_UOM,
2363 MAXIMUM_WEIGHT,
2364 VOLUME_UOM,
2365 MAXIMUM_VOLUME,
2366 PARTNER_TYPE,
2367 PARTNER_NAME,
2368 PARTNER_NUMBER,
2369 CALENDAR_CODE,
2370 CURRENCY_CODE,
2371 CALENDAR_EXCEPTION_SET_ID,
2372 OPERATING_UNIT,
2373 SR_INSTANCE_ID,
2374 PROJECT_REFERENCE_ENABLED,
2375 PROJECT_CONTROL_LEVEL,
2376 DEMAND_LATENESS_COST,
2377 SUPPLIER_CAP_OVERUTIL_COST,
2378 RESOURCE_CAP_OVERUTIL_COST,
2379 TRANSPORT_CAP_OVER_UTIL_COST,
2380 MODELED_CUSTOMER_ID,
2381 MODELED_CUSTOMER_SITE_ID,
2382 MODELED_SUPPLIER_ID,
2383 MODELED_SUPPLIER_SITE_ID,
2384 USE_PHANTOM_ROUTINGS,
2385 INHERIT_PHANTOM_OP_SEQ,
2386 DEFAULT_ATP_RULE_ID,
2387 DEFAULT_DEMAND_CLASS,
2388 MATERIAL_ACCOUNT,
2389 EXPENSE_ACCOUNT,
2390 SR_BUSINESS_GROUP_ID,
2391 SR_LEGAL_ENTITY,
2392 SR_SET_OF_BOOKS_ID,
2393 SR_CHART_OF_ACCOUNTS_ID,
2394 BUSINESS_GROUP_NAME,
2395 LEGAL_ENTITY_NAME,
2396 OPERATING_UNIT_NAME,
2397 REFRESH_NUMBER,
2398 INHERIT_OC_OP_SEQ_NUM,
2399 LAST_UPDATE_DATE,
2400 LAST_UPDATED_BY,
2401 CREATION_DATE,
2402 CREATED_BY)
2403 VALUES
2404 ( MSC_Trading_Partners_S.NEXTVAL,
2405 /* SCE change starts */
2406 c_rec.company_id,
2407 /* SCE change ends */
2408 c_rec.ORGANIZATION_CODE,
2409 c_rec.ORGANIZATION_TYPE,
2410 c_rec.SR_TP_ID,
2411 c_rec.DISABLE_DATE,
2412 c_rec.STATUS,
2413 c_rec.MASTER_ORGANIZATION,
2414 c_rec.SOURCE_ORG_ID,
2415 c_rec.WEIGHT_UOM,
2416 c_rec.MAXIMUM_WEIGHT,
2417 c_rec.VOLUME_UOM,
2418 c_rec.MAXIMUM_VOLUME,
2419 c_rec.PARTNER_TYPE,
2420 c_rec.PARTNER_NAME,
2421 c_rec.PARTNER_NUMBER,
2422 c_rec.CALENDAR_CODE,
2423 c_rec.CURRENCY_CODE,
2424 c_rec.CALENDAR_EXCEPTION_SET_ID,
2425 c_rec.OPERATING_UNIT,
2426 c_rec.SR_INSTANCE_ID,
2427 c_rec.PROJECT_REFERENCE_ENABLED,
2428 c_rec.PROJECT_CONTROL_LEVEL,
2429 c_rec.DEMAND_LATENESS_COST,
2430 c_rec.SUPPLIER_CAP_OVERUTIL_COST,
2431 c_rec.RESOURCE_CAP_OVERUTIL_COST,
2432 c_rec.TRANSPORT_CAP_OVER_UTIL_COST,
2433 c_rec.MODELED_CUSTOMER_ID,
2434 c_rec.MODELED_CUSTOMER_SITE_ID,
2435 c_rec.MODELED_SUPPLIER_ID,
2436 c_rec.MODELED_SUPPLIER_SITE_ID,
2437 c_rec.USE_PHANTOM_ROUTINGS,
2438 c_rec.INHERIT_PHANTOM_OP_SEQ,
2439 c_rec.DEFAULT_ATP_RULE_ID,
2440 c_rec.DEFAULT_DEMAND_CLASS,
2441 c_rec.MATERIAL_ACCOUNT,
2442 c_rec.EXPENSE_ACCOUNT,
2443 c_rec.BUSINESS_GROUP_ID,
2444 c_rec.LEGAL_ENTITY,
2445 c_rec.SET_OF_BOOKS_ID,
2446 c_rec.CHART_OF_ACCOUNTS_ID,
2447 c_rec.BUSINESS_GROUP_NAME,
2448 c_rec.LEGAL_ENTITY_NAME,
2449 c_rec.OPERATING_UNIT_NAME,
2450 MSC_CL_COLLECTION.v_last_collection_id,
2451 c_rec.INHERIT_OC_OP_SEQ_NUM,
2452 MSC_CL_COLLECTION.v_current_date,
2453 MSC_CL_COLLECTION.v_current_user,
2454 MSC_CL_COLLECTION.v_current_date,
2455 MSC_CL_COLLECTION.v_current_user );
2456
2457 /************** LEGACY_CHANGE_START*************************/
2458
2459 -- added for Legacy and Exchange
2460
2461 IF MSC_CL_COLLECTION.v_is_legacy_refresh THEN -- change for l-flow
2462
2463 lv_exist := 0;
2464
2465 OPEN c_org_exist FOR
2466 ' select 1 from MSC_INSTANCE_ORGS '
2467 ||' where ORGANIZATION_ID = :sr_tp_id '
2468 ||' and SR_INSTANCE_ID = :instance_id ' USING c_rec.SR_TP_ID, c_rec.SR_INSTANCE_ID;
2469
2470 FETCH c_org_exist into lv_exist ;
2471 CLOSE c_org_exist;
2472
2473 IF lv_exist = 0 THEN
2474 INSERT INTO MSC_INSTANCE_ORGS(
2475 SR_INSTANCE_ID,
2476 ORGANIZATION_ID,
2477 LAST_UPDATE_DATE,
2478 LAST_UPDATED_BY,
2479 CREATION_DATE,
2480 CREATED_BY,
2481 ENABLED_FLAG)
2482 VALUES
2483 (c_rec.SR_INSTANCE_ID,
2484 c_rec.SR_TP_ID,
2485 MSC_CL_COLLECTION.v_current_date,
2486 MSC_CL_COLLECTION.v_current_user,
2487 MSC_CL_COLLECTION.v_current_date,
2488 MSC_CL_COLLECTION.v_current_user,
2489 1);
2490 END IF;
2491
2492 lv_exist:= 0;
2493
2494 OPEN c_org_exist FOR
2495 ' select 1 from MSC_PARAMETERS '
2496 ||' where ORGANIZATION_ID = :sr_tp_id '
2497 ||' and SR_INSTANCE_ID = :instance_id ' USING c_rec.SR_TP_ID, c_rec.SR_INSTANCE_ID;
2498
2499 FETCH c_org_exist into lv_exist ;
2500 CLOSE c_org_exist;
2501
2502 IF lv_exist = 0 THEN
2503 INSERT INTO MSC_PARAMETERS
2504 ( ORGANIZATION_ID,
2505 SR_INSTANCE_ID,
2506 DEMAND_TIME_FENCE_FLAG,
2507 PLANNING_TIME_FENCE_FLAG,
2508 OPERATION_SCHEDULE_TYPE,
2509 CONSIDER_WIP,
2510 CONSIDER_PO,
2511 SNAPSHOT_LOCK,
2512 PLAN_SAFETY_STOCK,
2513 CONSIDER_RESERVATIONS,
2514 PART_INCLUDE_TYPE,
2515 PERIOD_TYPE,
2516 NETWORK_SCHEDULING_METHOD, /* hard coded to 1 (primary)*/
2517 LAST_UPDATE_DATE,
2518 LAST_UPDATED_BY,
2519 CREATION_DATE,
2520 CREATED_BY)
2521 values
2522 (c_rec.SR_TP_ID,
2523 c_rec.SR_INSTANCE_ID,
2524 1,
2525 1,
2526 1,
2527 1,
2528 1,
2529 1,
2530 1,
2531 1,
2532 1,
2533 1,
2534 1,
2535 MSC_CL_COLLECTION.v_current_date,
2536 MSC_CL_COLLECTION.v_current_user,
2537 MSC_CL_COLLECTION.v_current_date,
2538 MSC_CL_COLLECTION.v_current_user );
2539 END IF;
2540
2541 END IF;
2542
2543 /*****************LEGACY_CHANGE_ENDS************************/
2544 END IF;
2545
2546 EXCEPTION
2547 WHEN OTHERS THEN
2548
2549 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
2550
2551 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2552 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2553 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2554 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TRADING_PARTNERS');
2555 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2556
2557 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2558 RAISE;
2559
2560 ELSE
2561 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2562
2563 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2564 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2565 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2566 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TRADING_PARTNERS');
2567 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2568
2569 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2570 FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TP_ID');
2571 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SR_TP_ID));
2572 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2573
2574 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2575 FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
2576 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.ORGANIZATION_CODE);
2577 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2578
2579 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2580 END IF;
2581
2582 END;
2583
2584 END LOOP;
2585
2586 COMMIT;
2587
2588 -- Organization Site
2589
2590 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
2591
2592 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_TRADING_PARTNER_SITES', MSC_CL_COLLECTION.v_instance_id, NULL,
2593 'AND PARTNER_TYPE=3');
2594
2595 END IF;
2596
2597 FOR c_rec IN c2 LOOP
2598
2599 BEGIN
2600
2601 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
2602
2603 UPDATE MSC_TRADING_PARTNER_SITES
2604 SET
2605 PARTNER_ADDRESS= c_rec.PARTNER_ADDRESS,
2606 TP_SITE_CODE= c_rec.TP_SITE_CODE,
2607 LOCATION= c_rec.LOCATION,
2608 LONGITUDE= c_rec.LONGITUDE,
2609 LATITUDE= c_rec.LATITUDE,
2610 DELETED_FLAG= MSC_UTIL.SYS_NO,
2611 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2612 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2613 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2614 WHERE PARTNER_TYPE= 3
2615 AND SR_TP_ID= c_rec.SR_TP_ID
2616 AND SR_TP_SITE_ID= c_rec.SR_TP_SITE_ID
2617 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
2618
2619 END IF;
2620
2621 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
2622
2623 INSERT INTO MSC_Trading_Partner_Sites
2624 ( PARTNER_ID,
2625 PARTNER_SITE_ID,
2626 PARTNER_ADDRESS,
2627 LONGITUDE,
2628 LATITUDE,
2629 PARTNER_TYPE,
2630 SR_TP_ID,
2631 SR_TP_SITE_ID,
2632 SR_INSTANCE_ID,
2633 TP_SITE_CODE,
2634 LOCATION,
2635 DELETED_FLAG,
2636 REFRESH_NUMBER,
2637 LAST_UPDATE_DATE,
2638 LAST_UPDATED_BY,
2639 CREATION_DATE,
2640 CREATED_BY)
2641 VALUES
2642 ( c_rec.PARTNER_ID,
2643 MSC_Trading_Partner_Sites_S.NEXTVAL,
2644 c_rec.PARTNER_ADDRESS,
2645 c_rec.LONGITUDE,
2646 c_rec.LATITUDE,
2647 3,
2648 c_rec.SR_TP_ID,
2649 c_rec.SR_TP_SITE_ID,
2650 c_rec.SR_INSTANCE_ID,
2651 c_rec.TP_SITE_CODE,
2652 c_rec.LOCATION,
2653 MSC_UTIL.SYS_NO,
2654 MSC_CL_COLLECTION.v_last_collection_id,
2655 MSC_CL_COLLECTION.v_current_date,
2656 MSC_CL_COLLECTION.v_current_user,
2657 MSC_CL_COLLECTION.v_current_date,
2658 MSC_CL_COLLECTION.v_current_user );
2659
2660 END IF;
2661
2662 EXCEPTION
2663 WHEN OTHERS THEN
2664
2665 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
2666
2667 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2668 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2669 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2670 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TRADING_PARTNERS_SITES');
2671 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2672
2673 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2674 RAISE;
2675
2676 ELSE
2677 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2678
2679 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2680 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2681 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2682 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TRADING_PARTNERS_SITES');
2683 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2684
2685 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2686 FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TP_SITE_ID');
2687 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SR_TP_SITE_ID));
2688 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2689
2690 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2691 FND_MESSAGE.SET_TOKEN('COLUMN', 'LOCATION');
2692 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.LOCATION);
2693 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2694
2695 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2696 END IF;
2697
2698 END;
2699
2700 END LOOP;
2701
2702 COMMIT;
2703
2704 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
2705
2706 DELETE MSC_LOCATION_ASSOCIATIONS
2707 WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
2708
2709 -- MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_LOCATION_ASSOCIATIONS', MSC_CL_COLLECTION.v_instance_id, NULL);
2710
2711 END IF;
2712
2713 FOR c_rec IN c3 LOOP
2714
2715 BEGIN
2716
2717 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh OR MSC_CL_COLLECTION.v_is_legacy_refresh OR MSC_CL_COLLECTION.v_is_cont_refresh) THEN
2718
2719 INSERT INTO MSC_LOCATION_ASSOCIATIONS
2720 ( LOCATION_ID,
2721 LOCATION_CODE,
2722 PARTNER_ID,
2723 PARTNER_SITE_ID,
2724 organization_id,
2725 SR_INSTANCE_ID,
2726 LAST_UPDATE_DATE,
2727 LAST_UPDATED_BY,
2728 CREATION_DATE,
2729 CREATED_BY)
2730 VALUES
2731 ( c_rec.LOCATION_ID,
2732 c_rec.LOCATION_CODE,
2733 c_rec.PARTNER_ID,
2734 c_rec.PARTNER_SITE_ID,
2735 c_rec.organization_id,
2736 c_rec.SR_INSTANCE_ID,
2737 MSC_CL_COLLECTION.v_current_date,
2738 MSC_CL_COLLECTION.v_current_user,
2739 MSC_CL_COLLECTION.v_current_date,
2740 MSC_CL_COLLECTION.v_current_user );
2741
2742 END IF;
2743
2744 EXCEPTION
2745 WHEN OTHERS THEN
2746
2747 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
2748
2749 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2750 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2751 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2752 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_LOCATION_ASSOCIATIONS');
2753 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2754
2755 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2756 RAISE;
2757
2758 ELSE
2759
2760 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2761
2762 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2763 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2764 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2765 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_LOCATION_ASSOCIATIONS');
2766 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2767
2768 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2769 FND_MESSAGE.SET_TOKEN('COLUMN', 'LOCATION_ID');
2770 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.LOCATION_ID));
2771 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2772
2773 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2774 FND_MESSAGE.SET_TOKEN('COLUMN', 'LOCATION_CODE');
2775 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.LOCATION_CODE);
2776 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2777
2778 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2779 END IF;
2780
2781 END;
2782
2783 END LOOP;
2784
2785 COMMIT;
2786
2787 /*For bug# 2198339 Added this piece of code to collect location associations for Organizations */
2788
2789 FOR c_rec IN c5 LOOP
2790
2791 BEGIN
2792
2793 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh OR MSC_CL_COLLECTION.v_is_legacy_refresh OR MSC_CL_COLLECTION.v_is_cont_refresh) THEN
2794
2795 INSERT INTO MSC_LOCATION_ASSOCIATIONS
2796 ( LOCATION_ID,
2797 LOCATION_CODE,
2798 PARTNER_ID,
2799 PARTNER_SITE_ID,
2800 ORGANIZATION_ID,
2801 SR_INSTANCE_ID,
2802 LAST_UPDATE_DATE,
2803 LAST_UPDATED_BY,
2804 CREATION_DATE,
2805 CREATED_BY)
2806 VALUES
2807 ( c_rec.LOCATION_ID,
2808 c_rec.LOCATION_CODE,
2809 c_rec.PARTNER_ID,
2810 c_rec.PARTNER_SITE_ID,
2811 c_rec.ORGANIZATION_ID,
2812 c_rec.SR_INSTANCE_ID,
2813 MSC_CL_COLLECTION.v_current_date,
2814 MSC_CL_COLLECTION.v_current_user,
2815 MSC_CL_COLLECTION.v_current_date,
2816 MSC_CL_COLLECTION.v_current_user);
2817
2818 END IF;
2819
2820 EXCEPTION
2821 WHEN OTHERS THEN
2822 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
2823
2824 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2825 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2826 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2827 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_LOCATION_ASSOCIATIONS');
2828 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2829
2830 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2831 RAISE;
2832
2833 ELSE
2834 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2835
2836 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2837 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2838 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2839 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_LOCATION_ASSOCIATIONS');
2840 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2841
2842 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2843 FND_MESSAGE.SET_TOKEN('COLUMN', 'LOCATION_ID');
2844 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.LOCATION_ID));
2845 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2846
2847 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2848 FND_MESSAGE.SET_TOKEN('COLUMN', 'LOCATION_CODE');
2849 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.LOCATION_CODE);
2850 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2851
2852 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2853 END IF;
2854
2855 END;
2856
2857 END LOOP;
2858
2859 COMMIT;
2860
2861 -- Partner Contacts
2862
2863 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh OR MSC_CL_COLLECTION.v_is_legacy_refresh OR MSC_CL_COLLECTION.v_is_cont_refresh) THEN
2864
2865 IF NOT MSC_CL_COLLECTION.v_is_legacy_refresh THEN
2866 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_PARTNER_CONTACTS', MSC_CL_COLLECTION.v_instance_id, NULL);
2867 END IF;
2868
2869 FOR c_rec IN c4 LOOP
2870
2871 if(MSC_CL_COLLECTION.v_is_legacy_refresh) then
2872
2873 if (c_rec.DELETED_FLAG =MSC_UTIL.SYS_YES) then
2874
2875 delete from MSC_PARTNER_CONTACTS where
2876 partner_id=c_rec.PARTNER_ID
2877 and nvl(partner_site_id,-99999)=nvl(c_rec.PARTNER_SITE_ID,-99999)
2878 and partner_type=c_rec.PARTNER_TYPE
2879 and SR_INSTANCE_ID =MSC_CL_COLLECTION.v_instance_id
2880 and c_rec.DELETED_FLAG =MSC_UTIL.SYS_YES;
2881 else
2882 UPDATE MSC_PARTNER_CONTACTS
2883 set Name=c_rec.NAME,
2884 DISPLAY_NAME =c_rec.DISPLAY_NAME,
2885 EMAIL = c_rec.EMAIL,
2886 FAX = c_rec.FAX,
2887 ENABLED_FLAG =c_rec.ENABLED_FLAG,
2888 REFRESH_NUMBER =MSC_CL_COLLECTION.v_last_collection_id,
2889 LAST_UPDATE_DATE = MSC_CL_COLLECTION.v_current_date,
2890 LAST_UPDATED_BY = MSC_CL_COLLECTION.v_current_user,
2891 CREATION_DATE =MSC_CL_COLLECTION.v_current_date,
2892 CREATED_BY =MSC_CL_COLLECTION.v_current_user
2893 where
2894 partner_id=c_rec.PARTNER_ID
2895 and nvl(partner_site_id,-99999)=nvl(c_rec.PARTNER_SITE_ID,-99999)
2896 and partner_type=c_rec.PARTNER_TYPE
2897 and SR_INSTANCE_ID =MSC_CL_COLLECTION.v_instance_id;
2898 END IF ;
2899
2900 END IF ;
2901
2902
2903 IF MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh OR MSC_CL_COLLECTION.v_is_cont_refresh OR (SQL%NOTFOUND and c_rec.DELETED_FLAG =MSC_UTIL.SYS_NO)
2904 THEN
2905 IF lv_old_partner_id <> NVL(c_rec.partner_id,0) OR
2906 lv_old_partner_site_id <> NVL(c_rec.partner_site_id,0) OR
2907 lv_old_partner_type <> c_rec.partner_type THEN
2908
2909 BEGIN
2910
2911 INSERT INTO MSC_PARTNER_CONTACTS
2912 ( PARTNER_ID,
2913 PARTNER_SITE_ID,
2914 PARTNER_TYPE,
2915 NAME,
2916 DISPLAY_NAME,
2917 EMAIL,
2918 FAX,
2919 ENABLED_FLAG,
2920 SR_INSTANCE_ID,
2921 REFRESH_NUMBER,
2922 LAST_UPDATE_DATE,
2923 LAST_UPDATED_BY,
2924 CREATION_DATE,
2925 CREATED_BY)
2926 VALUES
2927 ( c_rec.PARTNER_ID,
2928 c_rec.PARTNER_SITE_ID,
2929 c_rec.PARTNER_TYPE,
2930 c_rec.NAME,
2931 c_rec.DISPLAY_NAME,
2932 c_rec.EMAIL,
2933 c_rec.FAX,
2934 c_rec.ENABLED_FLAG,
2935 MSC_CL_COLLECTION.v_instance_id,
2936 MSC_CL_COLLECTION.v_last_collection_id,
2937 MSC_CL_COLLECTION.v_current_date,
2938 MSC_CL_COLLECTION.v_current_user,
2939 MSC_CL_COLLECTION.v_current_date,
2940 MSC_CL_COLLECTION.v_current_user );
2941
2942 EXCEPTION
2943 WHEN OTHERS THEN
2944 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
2945
2946 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2947 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2948 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2949 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_PARTNER_CONTACTS');
2950 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2951
2952 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2953 RAISE;
2954
2955 ELSE
2956 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2957
2958 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2959 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2960 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRADING_PARTNER');
2961 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_PARTNER_CONTACTS');
2962 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2963
2964 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2965 FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_ID');
2966 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.PARTNER_ID));
2967 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2968
2969 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2970 FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_SITE_ID');
2971 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.PARTNER_SITE_ID));
2972 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2973
2974 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2975 FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_TYPE');
2976 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.PARTNER_TYPE));
2977 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2978
2979 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2980 END IF;
2981
2982 END;
2983
2984 lv_old_partner_id := NVL(c_rec.partner_id,0);
2985 lv_old_partner_site_id := NVL(c_rec.partner_site_id,0);
2986 lv_old_partner_type := c_rec.partner_type;
2987
2988 END IF;
2989 END IF ;
2990
2991 END LOOP;
2992
2993 COMMIT;
2994
2995 END IF;
2996
2997 END LOAD_TRADING_PARTNER;
2998
2999 --==================================================================
3000
3001 PROCEDURE LOAD_PARAMETER IS
3002
3003 CURSOR c1 IS
3004 SELECT
3005 msp.ORGANIZATION_ID,
3006 msp.DEMAND_TIME_FENCE_FLAG,
3007 msp.PLANNING_TIME_FENCE_FLAG,
3008 msp.OPERATION_SCHEDULE_TYPE,
3009 msp.CONSIDER_WIP,
3010 msp.CONSIDER_PO,
3011 msp.SNAPSHOT_LOCK,
3012 msp.PLAN_SAFETY_STOCK,
3013 msp.CONSIDER_RESERVATIONS,
3014 msp.PART_INCLUDE_TYPE,
3015 msp.DEFAULT_ABC_ASSIGNMENT_GROUP,
3016 msp.PERIOD_TYPE,
3017 msp.RESCHED_ASSUMPTION,
3018 msp.PLAN_DATE_DEFAULT_TYPE,
3019 msp.INCLUDE_REP_SUPPLY_DAYS,
3020 msp.INCLUDE_MDS_DAYS,
3021 msp.REPETITIVE_HORIZON1,
3022 msp.REPETITIVE_HORIZON2,
3023 msp.REPETITIVE_BUCKET_SIZE1,
3024 msp.REPETITIVE_BUCKET_SIZE2,
3025 msp.REPETITIVE_BUCKET_SIZE3,
3026 msp.REPETITIVE_ANCHOR_DATE,
3027 msp.SR_INSTANCE_ID
3028 FROM MSC_ST_PARAMETERS msp
3029 WHERE msp.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
3030
3031 c_count NUMBER:= 0;
3032
3033 BEGIN
3034
3035 --IF MSC_CL_COLLECTION.v_is_complete_refresh THEN
3036
3037 --DELETE FROM MSC_PARAMETERS
3038 -- WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
3039 --END IF;
3040
3041 FOR c_rec IN c1 LOOP
3042
3043 BEGIN
3044
3045 UPDATE MSC_PARAMETERS
3046 SET
3047 DEMAND_TIME_FENCE_FLAG= c_rec.DEMAND_TIME_FENCE_FLAG,
3048 PLANNING_TIME_FENCE_FLAG= c_rec.PLANNING_TIME_FENCE_FLAG,
3049 OPERATION_SCHEDULE_TYPE= c_rec.OPERATION_SCHEDULE_TYPE,
3050 CONSIDER_WIP= c_rec.CONSIDER_WIP,
3051 CONSIDER_PO= c_rec.CONSIDER_PO,
3052 SNAPSHOT_LOCK= c_rec.SNAPSHOT_LOCK,
3053 PLAN_SAFETY_STOCK= c_rec.PLAN_SAFETY_STOCK,
3054 CONSIDER_RESERVATIONS= c_rec.CONSIDER_RESERVATIONS,
3055 PART_INCLUDE_TYPE= c_rec.PART_INCLUDE_TYPE,
3056 DEFAULT_ABC_ASSIGNMENT_GROUP= c_rec.DEFAULT_ABC_ASSIGNMENT_GROUP,
3057 PERIOD_TYPE= c_rec.PERIOD_TYPE,
3058 RESCHED_ASSUMPTION= c_rec.RESCHED_ASSUMPTION,
3059 PLAN_DATE_DEFAULT_TYPE= c_rec.PLAN_DATE_DEFAULT_TYPE,
3060 INCLUDE_REP_SUPPLY_DAYS= c_rec.INCLUDE_REP_SUPPLY_DAYS,
3061 INCLUDE_MDS_DAYS= c_rec.INCLUDE_MDS_DAYS,
3062 REPETITIVE_HORIZON1= c_rec.REPETITIVE_HORIZON1,
3063 REPETITIVE_HORIZON2= c_rec.REPETITIVE_HORIZON2,
3064 REPETITIVE_BUCKET_SIZE1= c_rec.REPETITIVE_BUCKET_SIZE1,
3065 REPETITIVE_BUCKET_SIZE2= c_rec.REPETITIVE_BUCKET_SIZE2,
3066 REPETITIVE_BUCKET_SIZE3= c_rec.REPETITIVE_BUCKET_SIZE3,
3067 REPETITIVE_ANCHOR_DATE= c_rec.REPETITIVE_ANCHOR_DATE,
3068 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3069 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3070 WHERE SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
3071 AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID;
3072 /* Bug: 1993151 remove the collected flag from the update statement */
3073 -- AND COLLECTED_FLAG= MSC_UTIL.SYS_YES;
3074
3075 IF SQL%NOTFOUND THEN
3076
3077 INSERT INTO MSC_PARAMETERS
3078 ( ORGANIZATION_ID,
3079 DEMAND_TIME_FENCE_FLAG,
3080 PLANNING_TIME_FENCE_FLAG,
3081 OPERATION_SCHEDULE_TYPE,
3082 CONSIDER_WIP,
3083 CONSIDER_PO,
3084 SNAPSHOT_LOCK,
3085 PLAN_SAFETY_STOCK,
3086 CONSIDER_RESERVATIONS,
3087 PART_INCLUDE_TYPE,
3088 DEFAULT_ABC_ASSIGNMENT_GROUP,
3089 PERIOD_TYPE,
3090 RESCHED_ASSUMPTION,
3091 PLAN_DATE_DEFAULT_TYPE,
3092 INCLUDE_REP_SUPPLY_DAYS,
3093 INCLUDE_MDS_DAYS,
3094 REPETITIVE_HORIZON1,
3095 REPETITIVE_HORIZON2,
3096 REPETITIVE_BUCKET_SIZE1,
3097 REPETITIVE_BUCKET_SIZE2,
3098 REPETITIVE_BUCKET_SIZE3,
3099 REPETITIVE_ANCHOR_DATE,
3100 NETWORK_SCHEDULING_METHOD, /* hard coded to 1 (primary)*/
3101 COLLECTED_FLAG,
3102 SR_INSTANCE_ID,
3103 LAST_UPDATE_DATE,
3104 LAST_UPDATED_BY,
3105 CREATION_DATE,
3106 CREATED_BY)
3107 VALUES
3108 ( c_rec.ORGANIZATION_ID,
3109 c_rec.DEMAND_TIME_FENCE_FLAG,
3110 c_rec.PLANNING_TIME_FENCE_FLAG,
3111 c_rec.OPERATION_SCHEDULE_TYPE,
3112 c_rec.CONSIDER_WIP,
3113 c_rec.CONSIDER_PO,
3114 c_rec.SNAPSHOT_LOCK,
3115 c_rec.PLAN_SAFETY_STOCK,
3116 c_rec.CONSIDER_RESERVATIONS,
3117 c_rec.PART_INCLUDE_TYPE,
3118 c_rec.DEFAULT_ABC_ASSIGNMENT_GROUP,
3119 c_rec.PERIOD_TYPE,
3120 c_rec.RESCHED_ASSUMPTION,
3121 c_rec.PLAN_DATE_DEFAULT_TYPE,
3122 c_rec.INCLUDE_REP_SUPPLY_DAYS,
3123 c_rec.INCLUDE_MDS_DAYS,
3124 c_rec.REPETITIVE_HORIZON1,
3125 c_rec.REPETITIVE_HORIZON2,
3126 c_rec.REPETITIVE_BUCKET_SIZE1,
3127 c_rec.REPETITIVE_BUCKET_SIZE2,
3128 c_rec.REPETITIVE_BUCKET_SIZE3,
3129 c_rec.REPETITIVE_ANCHOR_DATE,
3130 1,
3131 MSC_UTIL.SYS_YES,
3132 c_rec.SR_INSTANCE_ID,
3133 MSC_CL_COLLECTION.v_current_date,
3134 MSC_CL_COLLECTION.v_current_user,
3135 MSC_CL_COLLECTION.v_current_date,
3136 MSC_CL_COLLECTION.v_current_user );
3137
3138 END IF;
3139
3140 EXCEPTION
3141
3142 WHEN OTHERS THEN
3143
3144 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
3145
3146 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3147 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3148 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_PARAMETER');
3149 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_PARAMETERS');
3150 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3151
3152 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3153 RAISE;
3154
3155 ELSE
3156
3157 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3158
3159 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3160 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3161 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_PARAMETER');
3162 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_PARAMETERS');
3163 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3164
3165 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3166 FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
3167 FND_MESSAGE.SET_TOKEN('VALUE',
3168 MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
3169 MSC_CL_COLLECTION.v_instance_id));
3170 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3171
3172 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3173 END IF;
3174
3175 END;
3176
3177 END LOOP;
3178
3179 COMMIT;
3180
3181 END LOAD_PARAMETER;
3182
3183
3184 --==================================================================
3185
3186 PROCEDURE LOAD_UOM IS
3187
3188 CURSOR c1 IS
3189 select
3190 msuom.UNIT_OF_MEASURE,
3191 msuom.UOM_CODE,
3192 msuom.UOM_CLASS,
3193 msuom.BASE_UOM_FLAG,
3194 msuom.DISABLE_DATE,
3195 msuom.DESCRIPTION,
3196 msuom.SR_INSTANCE_ID
3197 from MSC_ST_UNITS_OF_MEASURE msuom
3198 where msuom.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3199 order by UNIT_OF_MEASURE; /* use order by to avoid dead locking */
3200
3201 CURSOR c2 IS
3202 select
3203 NVL( t1.INVENTORY_ITEM_ID,0) INVENTORY_ITEM_ID, -- 0 means resource
3204 msucc.INVENTORY_ITEM_ID SR_INVENTORY_ITEM_ID,
3205 msucc.FROM_UNIT_OF_MEASURE,
3206 msucc.FROM_UOM_CODE,
3207 msucc.FROM_UOM_CLASS,
3208 msucc.TO_UNIT_OF_MEASURE,
3209 msucc.TO_UOM_CODE,
3210 msucc.TO_UOM_CLASS,
3211 msucc.CONVERSION_RATE,
3212 msucc.DISABLE_DATE,
3213 msucc.SR_INSTANCE_ID
3214 from MSC_ITEM_ID_LID t1,
3215 MSC_ST_UOM_CLASS_CONVERSIONS msucc
3216 WHERE t1.SR_INVENTORY_ITEM_ID(+)= msucc.Inventory_Item_ID
3217 AND t1.sr_instance_id(+)= msucc.sr_instance_id
3218 AND DECODE( t1.INVENTORY_ITEM_ID, NULL, msucc.Inventory_ITEM_ID,0 )= 0
3219 AND msucc.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3220 ORDER BY
3221 1,
3222 msucc.FROM_UNIT_OF_MEASURE,
3223 msucc.TO_UNIT_OF_MEASURE;
3224
3225 CURSOR c3 IS
3226 SELECT
3227 msuc.UNIT_OF_MEASURE,
3228 msuc.UOM_CODE,
3229 msuc.UOM_CLASS,
3230 NVL( t1.INVENTORY_ITEM_ID,0) Inventory_Item_ID,
3231 msuc.CONVERSION_RATE,
3232 msuc.DEFAULT_CONVERSION_FLAG,
3233 msuc.DISABLE_DATE,
3234 msuc.SR_INSTANCE_ID,
3235 msuc.Inventory_Item_ID SR_Inventory_Item_ID
3236 from MSC_ITEM_ID_LID t1,
3237 MSC_ST_UOM_CONVERSIONS msuc
3238 WHERE t1.SR_INVENTORY_ITEM_ID(+)= msuc.Inventory_Item_ID
3239 AND t1.sr_instance_id(+)= msuc.sr_instance_id
3240 AND DECODE( t1.INVENTORY_ITEM_ID, NULL, msuc.Inventory_ITEM_ID,0 )= 0
3241 AND msuc.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3242 ORDER BY
3243 4,1;
3244
3245 c_count NUMBER:= 0;
3246
3247 BEGIN
3248
3249 /*
3250 IF MSC_CL_COLLECTION.v_is_complete_refresh THEN
3251
3252 DELETE FROM MSC_UNITS_OF_MEASURE
3253 WHERE SR_INSTANCE_ID IN ( MSC_CL_COLLECTION.v_instance_id, -MSC_CL_COLLECTION.v_instance_id);
3254
3255 END IF;
3256 */
3257
3258 c_count:= 0;
3259
3260 FOR c_rec IN c1 LOOP
3261
3262 BEGIN
3263
3264 UPDATE MSC_UNITS_OF_MEASURE muom
3265 SET muom.UOM_CODE= c_rec.UOM_CODE,
3266 muom.UOM_CLASS= c_rec.UOM_CLASS,
3267 muom.BASE_UOM_FLAG= c_rec.BASE_UOM_FLAG,
3268 muom.DISABLE_DATE= c_rec.DISABLE_DATE,
3269 muom.DESCRIPTION= c_rec.DESCRIPTION,
3270 muom.SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID,
3271 muom.REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
3272 muom.LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3273 muom.LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3274 WHERE muom.UNIT_OF_MEASURE= c_rec.UNIT_OF_MEASURE;
3275
3276 IF SQL%NOTFOUND THEN
3277
3278 INSERT INTO MSC_UNITS_OF_MEASURE
3279 ( UNIT_OF_MEASURE,
3280 UOM_CODE,
3281 UOM_CLASS,
3282 BASE_UOM_FLAG,
3283 DISABLE_DATE,
3284 DESCRIPTION,
3285 SR_INSTANCE_ID,
3286 REFRESH_NUMBER,
3287 LAST_UPDATE_DATE,
3288 LAST_UPDATED_BY,
3289 CREATION_DATE,
3290 CREATED_BY)
3291 VALUES
3292 ( c_rec.UNIT_OF_MEASURE,
3293 c_rec.UOM_CODE,
3294 c_rec.UOM_CLASS,
3295 c_rec.BASE_UOM_FLAG,
3296 c_rec.DISABLE_DATE,
3297 c_rec.DESCRIPTION,
3298 c_rec.SR_INSTANCE_ID,
3299 MSC_CL_COLLECTION.v_last_collection_id,
3300 MSC_CL_COLLECTION.v_current_date,
3301 MSC_CL_COLLECTION.v_current_user,
3302 MSC_CL_COLLECTION.v_current_date,
3303 MSC_CL_COLLECTION.v_current_user );
3304
3305 END IF;
3306
3307 c_count:= c_count+1;
3308
3309 IF c_count> MSC_CL_COLLECTION.PBS THEN
3310 COMMIT;
3311 c_count:= 0;
3312 END IF;
3313
3314 EXCEPTION
3315 WHEN OTHERS THEN
3316
3317 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
3318
3319 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3320 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3321 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_UOM');
3322 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_UNITS_OF_MEASURE');
3323 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3324
3325 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3326 RAISE;
3327
3328 ELSE
3329
3330 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3331
3332 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3333 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3334 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_UOM');
3335 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_UNITS_OF_MEASURE');
3336 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3337
3338 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3339 FND_MESSAGE.SET_TOKEN('COLUMN', 'UOM_CODE');
3340 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.UOM_CODE);
3341 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3342
3343 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3344 FND_MESSAGE.SET_TOKEN('COLUMN', 'UNIT_OF_MEASURE');
3345 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.UNIT_OF_MEASURE);
3346 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3347
3348 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3349 END IF;
3350
3351 END;
3352
3353 END LOOP;
3354
3355 COMMIT;
3356
3357 /*
3358 IF MSC_CL_COLLECTION.v_is_complete_refresh THEN
3359
3360 DELETE FROM MSC_UOM_CLASS_CONVERSIONS
3361 WHERE SR_INSTANCE_ID IN ( MSC_CL_COLLECTION.v_instance_id, -MSC_CL_COLLECTION.v_instance_id);
3362
3363 COMMIT;
3364
3365 END IF;
3366 */
3367
3368 c_count:= 0;
3369
3370 FOR c_rec IN c2 LOOP
3371
3372 BEGIN
3373
3374 UPDATE MSC_UOM_CLASS_CONVERSIONS mucc
3375 SET mucc.FROM_UOM_CODE= c_rec.FROM_UOM_CODE,
3376 mucc.FROM_UOM_CLASS= c_rec.FROM_UOM_CLASS,
3377 mucc.TO_UOM_CODE= c_rec.TO_UOM_CODE,
3378 mucc.TO_UOM_CLASS= c_rec.TO_UOM_CLASS,
3379 mucc.CONVERSION_RATE= c_rec.CONVERSION_RATE,
3380 mucc.DISABLE_DATE= c_rec.DISABLE_DATE,
3381 mucc.SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID,
3382 mucc.REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
3383 mucc.LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3384 mucc.LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3385 WHERE mucc.INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
3386 AND mucc.FROM_UNIT_OF_MEASURE= c_rec.FROM_UNIT_OF_MEASURE
3387 AND mucc.TO_UNIT_OF_MEASURE= c_rec.TO_UNIT_OF_MEASURE;
3388
3389 IF SQL%NOTFOUND THEN
3390
3391 insert into MSC_UOM_CLASS_CONVERSIONS
3392 ( INVENTORY_ITEM_ID,
3393 FROM_UNIT_OF_MEASURE,
3394 FROM_UOM_CODE,
3395 FROM_UOM_CLASS,
3396 TO_UNIT_OF_MEASURE,
3397 TO_UOM_CODE,
3398 TO_UOM_CLASS,
3399 CONVERSION_RATE,
3400 DISABLE_DATE,
3401 SR_INSTANCE_ID,
3402 REFRESH_NUMBER,
3403 LAST_UPDATE_DATE,
3404 LAST_UPDATED_BY,
3405 CREATION_DATE,
3406 CREATED_BY)
3407 VALUES
3408 ( c_rec.INVENTORY_ITEM_ID,
3409 c_rec.FROM_UNIT_OF_MEASURE,
3410 c_rec.FROM_UOM_CODE,
3411 c_rec.FROM_UOM_CLASS,
3412 c_rec.TO_UNIT_OF_MEASURE,
3413 c_rec.TO_UOM_CODE,
3414 c_rec.TO_UOM_CLASS,
3415 c_rec.CONVERSION_RATE,
3416 c_rec.DISABLE_DATE,
3417 c_rec.SR_INSTANCE_ID,
3418 MSC_CL_COLLECTION.v_last_collection_id,
3419 MSC_CL_COLLECTION.v_current_date,
3420 MSC_CL_COLLECTION.v_current_user,
3421 MSC_CL_COLLECTION.v_current_date,
3422 MSC_CL_COLLECTION.v_current_user );
3423
3424 END IF;
3425
3426 c_count:= c_count+1;
3427
3428 IF c_count> MSC_CL_COLLECTION.PBS THEN
3429 COMMIT;
3430 c_count:= 0;
3431 END IF;
3432
3433 EXCEPTION
3434 WHEN OTHERS THEN
3435
3436 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
3437
3438 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3439 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3440 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_UOM');
3441 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_UOM_CLASS_CONVERSIONS');
3442 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3443
3444 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3445 RAISE;
3446
3447 ELSE
3448
3449 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3450
3451 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3452 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3453 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_UOM');
3454 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_UOM_CLASS_CONVERSIONS');
3455 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3456
3457 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3458 FND_MESSAGE.SET_TOKEN('COLUMN', 'MSC_CL_ITEM_ODS_LOAD.item_name');
3459 FND_MESSAGE.SET_TOKEN('VALUE', MSC_CL_ITEM_ODS_LOAD.item_name( c_rec.INVENTORY_ITEM_ID));
3460 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3461
3462 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3463 FND_MESSAGE.SET_TOKEN('COLUMN', 'FROM_UNIT_OF_MEASURE');
3464 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.FROM_UNIT_OF_MEASURE);
3465 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3466
3467 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3468 FND_MESSAGE.SET_TOKEN('COLUMN', 'TO_UNIT_OF_MEASURE');
3469 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.TO_UNIT_OF_MEASURE);
3470 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3471
3472 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3473 END IF;
3474
3475 END;
3476
3477 END LOOP;
3478
3479 COMMIT;
3480
3481 /*
3482 IF MSC_CL_COLLECTION.v_is_complete_refresh THEN
3483
3484 DELETE FROM MSC_UOM_CONVERSIONS
3485 WHERE SR_INSTANCE_ID IN ( MSC_CL_COLLECTION.v_instance_id, -MSC_CL_COLLECTION.v_instance_id);
3486
3487 COMMIT;
3488
3489 END IF;
3490 */
3491
3492 c_count:= 0;
3493
3494 FOR c_rec IN c3 LOOP
3495
3496 BEGIN
3497
3498 /* changed the where cond to update the row based on UOM code as a new
3499 index on MSC_UOM_CONVERSIONS(INVENTORY_ITEM_ID,UOM_CODE) is introduced */
3500
3501 UPDATE MSC_UOM_CONVERSIONS muc
3502 SET UNIT_OF_MEASURE= c_rec.UNIT_OF_MEASURE,
3503 UOM_CODE= c_rec.UOM_CODE,
3504 UOM_CLASS= c_rec.UOM_CLASS,
3505 INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID,
3506 CONVERSION_RATE= c_rec.CONVERSION_RATE,
3507 DEFAULT_CONVERSION_FLAGS= c_rec.DEFAULT_CONVERSION_FLAG,
3508 DISABLE_DATE= c_rec.DISABLE_DATE,
3509 SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID,
3510 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
3511 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3512 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3513 WHERE muc.INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
3514 AND muc.UOM_CODE = c_rec.UOM_CODE;
3515
3516 IF SQL%NOTFOUND THEN
3517
3518 insert into MSC_UOM_CONVERSIONS
3519 ( UNIT_OF_MEASURE,
3520 UOM_CODE,
3521 UOM_CLASS,
3522 INVENTORY_ITEM_ID,
3523 CONVERSION_RATE,
3524 DEFAULT_CONVERSION_FLAGS,
3525 DISABLE_DATE,
3526 SR_INSTANCE_ID,
3527 REFRESH_NUMBER,
3528 LAST_UPDATE_DATE,
3529 LAST_UPDATED_BY,
3530 CREATION_DATE,
3531 CREATED_BY)
3532 VALUES
3533 ( c_rec.UNIT_OF_MEASURE,
3534 c_rec.UOM_CODE,
3535 c_rec.UOM_CLASS,
3536 c_rec.INVENTORY_ITEM_ID,
3537 c_rec.CONVERSION_RATE,
3538 c_rec.DEFAULT_CONVERSION_FLAG,
3539 c_rec.DISABLE_DATE,
3540 c_rec.SR_INSTANCE_ID,
3541 MSC_CL_COLLECTION.v_last_collection_id,
3542 MSC_CL_COLLECTION.v_current_date,
3543 MSC_CL_COLLECTION.v_current_user,
3544 MSC_CL_COLLECTION.v_current_date,
3545 MSC_CL_COLLECTION.v_current_user);
3546
3547 END IF;
3548
3549 c_count:= c_count+1;
3550
3551 IF c_count> MSC_CL_COLLECTION.PBS THEN
3552 COMMIT;
3553 c_count:= 0;
3554 END IF;
3555
3556 EXCEPTION
3557 WHEN OTHERS THEN
3558
3559 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
3560
3561 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3562 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3563 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_UOM');
3564 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_UOM_CONVERSIONS');
3565 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3566
3567 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3568 RAISE;
3569
3570 ELSE
3571
3572 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3573
3574 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3575 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3576 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_UOM');
3577 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_UOM_CONVERSIONS');
3578 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3579
3580 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3581 FND_MESSAGE.SET_TOKEN('COLUMN', 'MSC_CL_ITEM_ODS_LOAD.item_name');
3582 FND_MESSAGE.SET_TOKEN('VALUE', MSC_CL_ITEM_ODS_LOAD.item_name( c_rec.INVENTORY_ITEM_ID));
3583 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3584
3585 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3586 FND_MESSAGE.SET_TOKEN('COLUMN', 'UNIT_OF_MEASURE');
3587 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.UNIT_OF_MEASURE);
3588 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3589
3590 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3591 FND_MESSAGE.SET_TOKEN('COLUMN', 'UOM_CODE');
3592 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.UOM_CODE);
3593 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3594
3595 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3596 END IF;
3597
3598 END;
3599
3600 END LOOP;
3601
3602 COMMIT;
3603
3604 END LOAD_UOM;
3605
3606 /* This procedure has been introduced for Liability Project */
3607 /* This procedure removes the ASL's from the MSC_ASL_AUTH_DETAILS if they do not exist in */
3608 /* MSC_ITEM_SUPPLIERS */
3609 PROCEDURE CLEAN_LIAB_AGREEMENT
3610
3611 IS
3612
3613
3614 /* This cursor gives the set of ASL that are there in MSC_ASL_AUTH_DETAILS */
3615 CURSOR c_sup_item_org is
3616 select distinct
3617 SUPPLIER_ID,
3618 SUPPLIER_SITE_ID ,
3619 ORGANIZATION_ID ,
3620 SR_INSTANCE_ID,
3621 INVENTORY_ITEM_ID,
3622 INCLUDE_LIABILITY_AGREEMENT,
3623 ASL_LIABILITY_AGREEMENT_BASIS,USING_ORGANIZATION_ID
3624 from
3625 msc_asl_auth_details
3626 where
3627 plan_id = -1 and
3628 sr_instance_id = MSC_CL_COLLECTION.v_instance_id ;
3629
3630
3631
3632
3633 BEGIN
3634
3635 /* This deletes the agreements form MSC_ASL_AUTH_DETAILS that dont have parent records in msc_item_suppliers*/
3636 delete msc_asl_auth_details a
3637 where not exists (select 1 from msc_item_suppliers p
3638 where p.PLAN_ID = a.PLAN_ID and
3639 p.SUPPLIER_ID = a. SUPPLIER_ID and
3640 p.SUPPLIER_SITE_ID = a.SUPPLIER_SITE_ID and
3641 p.ORGANIZATION_ID = a.ORGANIZATION_ID and
3642 p.SR_INSTANCE_ID = a.SR_INSTANCE_ID and
3643 p.INVENTORY_ITEM_ID = a.INVENTORY_ITEM_ID and
3644 p.using_organization_id =a.using_organization_id and
3645 p.sr_instance_id = MSC_CL_COLLECTION.v_instance_id and
3646 p.plan_id = -1
3647 )
3648 and sr_instance_id = MSC_CL_COLLECTION.v_instance_id
3649 and plan_id = -1 ;
3650 commit ;
3651
3652
3653
3654
3655
3656
3657 /* Updates the msc_item_supplier : include_liability_agreement , asl_liability_agreement_basis */
3658 FOR x_sup_item_org in c_sup_item_org
3659
3660 LOOP
3661
3662
3663 UPDATE msc_item_suppliers
3664 set INCLUDE_LIABILITY_AGREEMENT = x_sup_item_org.INCLUDE_LIABILITY_AGREEMENT ,
3665 ASL_LIABILITY_AGREEMENT_BASIS = x_sup_item_org.ASL_LIABILITY_AGREEMENT_BASIS
3666 where
3667 SUPPLIER_ID = x_sup_item_org.SUPPLIER_ID and
3668 SUPPLIER_SITE_ID = x_sup_item_org.SUPPLIER_SITE_ID and
3669 ORGANIZATION_ID = x_sup_item_org.ORGANIZATION_ID and
3670 SR_INSTANCE_ID = x_sup_item_org.SR_INSTANCE_ID and
3671 INVENTORY_ITEM_ID = x_sup_item_org.INVENTORY_ITEM_ID and
3672 USING_ORGANIZATION_ID = x_sup_item_org.USING_ORGANIZATION_ID and
3673 plan_id = -1
3674 ;
3675
3676
3677
3678 END LOOP ;
3679 commit ;
3680
3681
3682 END CLEAN_LIAB_AGREEMENT ;
3683
3684 PROCEDURE GENERATE_TRADING_PARTNER_KEYS (
3685 ERRBUF OUT NOCOPY VARCHAR2,
3686 RETCODE OUT NOCOPY NUMBER,
3687 pINSTANCE_ID IN NUMBER) IS
3688
3689 CURSOR c1 IS
3690 SELECT mtil.TP_ID,
3691 mtil.PARTNER_TYPE,
3692 mst.ORGANIZATION_CODE,
3693 mst.SR_TP_ID,
3694 mst.DISABLE_DATE,
3695 mst.STATUS,
3696 mst.MASTER_ORGANIZATION,
3697 mst.WEIGHT_UOM,
3698 mst.MAXIMUM_WEIGHT,
3699 mst.VOLUME_UOM,
3700 mst.MAXIMUM_VOLUME,
3701 mst.PARTNER_NUMBER,
3702 mst.CALENDAR_CODE,
3703 mst.CALENDAR_EXCEPTION_SET_ID,
3704 mst.OPERATING_UNIT,
3705 mst.SR_INSTANCE_ID,
3706 mst.PROJECT_REFERENCE_ENABLED,
3707 mst.PROJECT_CONTROL_LEVEL,
3708 mst.CUSTOMER_CLASS_CODE,
3709 mst.CUSTOMER_TYPE
3710 FROM MSC_TP_ID_LID mtil,
3711 MSC_ST_TRADING_PARTNERS mst
3712 WHERE mtil.SR_TP_ID= mst.SR_TP_ID
3713 AND mtil.SR_INSTANCE_ID= mst.SR_INSTANCE_ID
3714 AND mst.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3715 AND mtil.Partner_Type= mst.Partner_Type
3716 AND mst.Partner_Type IN (1,2,4) --Vendor/Customer/Carrier
3717 /* SCE Change Starts */
3718 AND nvl(mst.company_id, -1) = mtil.sr_company_id
3719 AND nvl(mst.company_id, -1) = -1
3720 /* SCE Change Starts */
3721 ORDER BY
3722 mtil.TP_ID;
3723
3724 CURSOR c2 IS
3725 SELECT mtsil.TP_SITE_ID,
3726 mtsil.Partner_Type,
3727 substrb(msts.PARTNER_ADDRESS,1,1600) PARTNER_ADDRESS,--added for the NLS bug3463401
3728 msts.POSTAL_CODE,
3729 substrb(msts.CITY,1,60) CITY,--added for the NLS bug3463401
3730 msts.STATE,
3731 msts.COUNTRY,
3732 msts.LONGITUDE,
3733 msts.LATITUDE,
3734 msts.SR_TP_SITE_ID,
3735 msts.SR_INSTANCE_ID,
3736 msts.SHIPPING_CONTROL
3737 FROM MSC_TP_SITE_ID_LID mtsil,
3738 MSC_ST_TRADING_PARTNER_SITES msts
3739 WHERE mtsil.SR_TP_SITE_ID= msts.SR_TP_SITE_ID
3740 AND mtsil.SR_INSTANCE_ID= msts.SR_INSTANCE_ID
3741 AND msts.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3742 AND mtsil.Partner_Type= msts.Partner_Type
3743 AND msts.Partner_Type IN (1,2) --Vendor/Customer
3744 /* SCE Change Starts */
3745 AND nvl(msts.company_id, -1) = mtsil.sr_company_id
3746 /* SCE Change Starts */
3747 ORDER BY
3748 mtsil.TP_SITE_ID;
3749
3750 /*
3751 Cursor c5 IS
3752 SELECT distinct mst.Partner_Name, mst.Partner_Type
3753 FROM MSC_ST_TRADING_PARTNERS mst
3754 WHERE NOT EXISTS ( select 1
3755 from MSC_TRADING_PARTNERS mtp
3756 where mtp.Partner_Name= mst.Partner_Name
3757 and mtp.Partner_Type= mst.Partner_Type)
3758 AND mst.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3759 AND mst.Partner_type IN (1,2)
3760 ORDER BY mst.Partner_Type,
3761 mst.Partner_Name; -- using ORDER BY to avoid dead lock
3762 */
3763
3764 -- ==== New Customers and Suppliers ====
3765 Cursor c5 IS
3766 SELECT decode(mc.company_id, MSC_CL_COLLECTION.G_MY_COMPANY_ID, null, mc.company_id) company_id1,
3767 mst.partner_name partner_name,
3768 mst.partner_type partner_type
3769 from MSC_ST_TRADING_PARTNERS mst,
3770 MSC_COMPANIES MC
3771 where nvl(mst.company_name, MSC_CL_COLLECTION.v_my_company_name) = mc.company_name
3772 and mst.sr_instance_id = MSC_CL_COLLECTION.v_instance_id
3773 and mst.partner_type IN (1,2,4) --Vendor/Customer/Carrier
3774 /* SCE CHANGE STARTS */
3775 and nvl(mst.company_id , -1) = -1
3776 /* SCE CHANGE ENDS */
3777 MINUS
3778 SELECT decode(mtp.company_id,null, null, mtp.company_id) company_id,
3779 mtp.partner_name partner_name,
3780 mtp.partner_type partner_type
3781 from msc_trading_partners mtp
3782 where mtp.partner_type IN (1,2,4) --Vendor/Customer/Carrier
3783 ORDER BY partner_type,
3784 company_id1,
3785 partner_name ;
3786
3787 Cursor c5_tpname IS
3788 SELECT distinct mst.Partner_Name, mst.sr_tp_id, mst.sr_instance_id, mst.Partner_Type,
3789 decode(mc.company_id, MSC_CL_COLLECTION.G_MY_COMPANY_ID, -1, mc.company_id) company_id1
3790 FROM MSC_ST_TRADING_PARTNERS mst,
3791 MSC_COMPANIES MC
3792 WHERE EXISTS ( select 1
3793 from MSC_TRADING_PARTNERS mtp
3794 where mtp.sr_tp_id= mst.sr_tp_id
3795 and mtp.sr_instance_id= mst.sr_instance_id
3796 and mtp.Partner_Type= mst.Partner_Type
3797 and nvl(mtp.COMPANY_ID,MSC_CL_COLLECTION.G_MY_COMPANY_ID) = MC.COMPANY_ID
3798 and mtp.Partner_Name <> mst.Partner_Name)
3799 AND mst.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3800 AND mst.Partner_type IN (1,2)
3801 AND nvl(mst.company_name, MSC_CL_COLLECTION.v_my_company_name) = mc.company_name
3802 AND nvl(mst.company_id , -1) = -1
3803 ORDER BY mst.Partner_Name; -- using ORDER BY to avoid dead lock
3804
3805
3806 Cursor c6 IS
3807 SELECT distinct mtil.TP_ID, msts.tp_site_code
3808 FROM MSC_ST_TRADING_PARTNER_SITES msts,
3809 MSC_TP_ID_LID mtil
3810 WHERE NOT EXISTS ( select 1
3811 from MSC_TRADING_PARTNER_SITES mtps
3812 where mtps.TP_Site_Code= msts.TP_Site_Code
3813 and mtps.Partner_ID= mtil.tp_id)
3814 AND msts.SR_Instance_ID= mtil.SR_INSTANCE_ID
3815 AND msts.SR_TP_ID= mtil.SR_TP_ID
3816
3817 /* SCE CHANGE */
3818 AND nvl(msts.company_id, -1) = mtil.sr_company_id
3819
3820 AND msts.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3821 AND msts.Partner_Type=1
3822 AND mtil.Partner_type=1
3823 ORDER BY mtil.TP_ID,
3824 msts.TP_Site_Code; -- using ORDER BY to avoid dead lock
3825
3826 Cursor c7 IS
3827 SELECT distinct
3828 mtil.TP_ID,
3829 msts.Operating_Unit_Name,
3830 msts.TP_Site_Code,
3831 msts.Location
3832 FROM MSC_ST_TRADING_PARTNER_SITES msts,
3833 MSC_TP_ID_LID mtil
3834 WHERE NOT EXISTS ( select 1
3835 from MSC_TRADING_PARTNER_SITES mtps
3836 where NVL(mtps.Operating_Unit_Name, ' ')=
3837 NVL(msts.Operating_Unit_Name, ' ')
3838 and mtps.TP_Site_Code= msts.TP_Site_Code
3839 and mtps.Location= msts.Location
3840 and mtps.Partner_ID= mtil.TP_ID)
3841 AND msts.SR_Instance_ID= mtil.SR_INSTANCE_ID
3842 AND msts.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3843 AND msts.SR_TP_ID= mtil.SR_TP_ID
3844 /* SCE CHANGE starts*/
3845 AND nvl(msts.company_id, -1) = mtil.sr_company_id
3846 /* SCE CHANGE ends*/
3847 AND msts.Partner_Type=2
3848 AND mtil.Partner_type=2
3849 ORDER BY mtil.TP_ID,
3850 msts.TP_Site_Code,
3851 msts.Location; -- using ORDER BY to avoid dead lock
3852
3853 Cursor c9 IS
3854 SELECT distinct
3855 nvl(msts.company_id, -1) SR_COMPANY_ID,
3856 msts.SR_TP_SITE_ID,
3857 msts.SR_INSTANCE_ID,
3858 mtp.PARTNER_SITE_ID
3859 FROM MSC_ST_TRADING_PARTNER_SITES msts,
3860 MSC_TP_ID_LID mtil,
3861 MSC_TRADING_PARTNER_SITES mtp
3862 WHERE NOT EXISTS( select 1
3863 from MSC_TP_SITE_ID_LID mtsil
3864 where msts.SR_TP_SITE_ID= mtsil.SR_TP_SITE_ID
3865 and msts.SR_INSTANCE_ID= mtsil.SR_INSTANCE_ID
3866 and mtsil.Partner_Type= 1
3867 and nvl(msts.company_id, -1) = mtsil.sr_company_id)
3868 AND msts.TP_Site_Code= mtp.TP_Site_Code
3869 AND msts.SR_TP_ID= mtil.SR_TP_ID
3870 AND msts.SR_INSTANCE_ID= mtil.SR_INSTANCE_ID
3871 AND msts.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3872 /* SCE Change starts */
3873 AND nvl(msts.company_id, -1) = mtil.SR_COMPANY_ID
3874 /* SCE changes ends */
3875 AND mtil.TP_ID= mtp.Partner_ID
3876 AND mtp.partner_type = mtil.partner_type
3877 AND mtil.Partner_Type= msts.partner_type
3878 AND msts.Partner_Type= 1;
3879
3880 --====== Cursor for populating msc_tp_site_id_lid ====
3881
3882 Cursor c10 IS
3883 SELECT distinct
3884 /* SCE Change starts*/
3885 /* Added sr_company_id for SCE purpose */
3886 nvl(msts.company_id, -1) SR_COMPANY_ID,
3887 /* SCE Change ends*/
3888 msts.SR_TP_SITE_ID,
3889 msts.SR_INSTANCE_ID,
3890 mtp.PARTNER_SITE_ID
3891 FROM MSC_ST_TRADING_PARTNER_SITES msts,
3892 MSC_TP_ID_LID mtil,
3893 MSC_TRADING_PARTNER_SITES mtp
3894 WHERE NOT EXISTS( select 1
3895 from MSC_TP_SITE_ID_LID mtsil
3896 where msts.SR_TP_SITE_ID= mtsil.SR_TP_SITE_ID
3897 and msts.SR_INSTANCE_ID= mtsil.SR_INSTANCE_ID
3898 and mtsil.Partner_Type= 2
3899 and nvl(msts.company_id, -1) = mtsil.sr_company_id)
3900 AND NVL( msts.Operating_Unit_Name, ' ')=
3901 NVL( mtp.Operating_Unit_Name, ' ')
3902 AND msts.TP_Site_Code= mtp.TP_Site_Code
3903 AND nvl(msts.Location, ' ')= nvl(mtp.Location, ' ')
3904 AND msts.SR_TP_ID= mtil.SR_TP_ID
3905 AND msts.SR_INSTANCE_ID= mtil.SR_INSTANCE_ID
3906 AND msts.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3907 /* SCE Change stars*/
3908 /* Added sr_company_id for SCE purpose */
3909 AND nvl(msts.company_id, -1) = mtil.SR_COMPANY_ID
3910 /* SCE Change stars*/
3911 AND mtil.TP_ID= mtp.Partner_ID
3912 AND mtp.partner_type = mtil.partner_type
3913 AND mtil.Partner_Type= msts.partner_type
3914 AND msts.Partner_Type= 2;
3915
3916 -- ============ Cursor for populating msc_tp_id_lid ==================== --
3917 CURSOR c12 IS
3918 SELECT distinct
3919 /* SCE Change starts */
3920 nvl(mst.company_id, -1) SR_COMPANY_ID,
3921 /* SCE change ends */
3922 mst.SR_TP_ID,
3923 mst.SR_INSTANCE_ID,
3924 mst.Partner_Type,
3925 mtp.PARTNER_ID
3926 FROM MSC_ST_TRADING_PARTNERS mst,
3927 MSC_TRADING_PARTNERS mtp,
3928 /* SCE Change starts */
3929 msc_companies mc
3930 /* SCE Change ends */
3931 WHERE NOT EXISTS( select 1
3932 from MSC_TP_ID_LID mtil
3933 where mst.SR_TP_ID= mtil.SR_TP_ID
3934 and mst.SR_INSTANCE_ID= mtil.SR_INSTANCE_ID
3935 and mst.Partner_Type= mtil.Partner_Type
3936 -- SCE Change
3937 -- Join with company_id
3938 and nvl( mst.company_id, -1) = nvl(mtil.sr_company_id, -1)
3939 and nvl( mst.company_id, -1) = -1)
3940 AND mst.Partner_NAME= mtp.Partner_NAME
3941 AND mst.Partner_Type= mtp.Partner_Type
3942 AND mst.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3943 AND mst.Partner_Type IN ( 1, 2)
3944 /* SCE Change starts */
3945 -- Add join with msc_companies
3946 AND nvl(mst.company_name, MSC_CL_COLLECTION.v_my_company_name) = mc.company_name
3947 AND mc.company_id = nvl(mtp.company_id, MSC_CL_COLLECTION.G_MY_COMPANY_ID );
3948 -- AND nvl( mst.company_id, -1) = -1; -- commented for aerox
3949 /* SCE Change ends */
3950
3951
3952 -- ============ Cursor for UPDATE MSC_TP_ID_LID SRP Changes ==================== --
3953 CURSOR c13 IS
3954 Select resource_type, sr_instance_id , partner_type,sr_tp_id
3955 From msc_st_trading_partners
3956 Where sr_instance_id = MSC_CL_COLLECTION.v_instance_id
3957 And partner_type=2;
3958
3959 lv_control_flag NUMBER;
3960 lv_msc_tp_coll_window NUMBER;
3961 lv_new_partner_id NUMBER;
3962 lv_old_partner_id NUMBER;
3963 lv_partner_count NUMBER;
3964
3965 lv_tp_id_count NUMBER := 0;
3966 lv_tp_site_id_count NUMBER := 0;
3967 lv_tp_stat_stale NUMBER := MSC_UTIL.SYS_NO;
3968 lv_tp_site_stat_stale NUMBER := MSC_UTIL.SYS_NO;
3969 lv_ins_records NUMBER := 0;
3970
3971 BEGIN
3972
3973
3974 GET_COLL_PARAM (pINSTANCE_ID);
3975 MSC_CL_COLLECTION.INITIALIZE( pINSTANCE_ID);
3976 MSC_CL_COLLECTION.v_my_company_name := MSC_CL_SCE_COLLECTION.GET_MY_COMPANY;
3977
3978
3979 SELECT decode(nvl(fnd_profile.value('MSC_PURGE_ST_CONTROL'),'N'),'Y',1,2)
3980 INTO lv_control_flag
3981 FROM dual;
3982
3983 BEGIN
3984 lv_msc_tp_coll_window := NVL(TO_NUMBER(FND_PROFILE.VALUE('MSC_COLLECTION_WINDOW_FOR_TP_CHANGES')),0);
3985 EXCEPTION
3986 WHEN OTHERS THEN
3987 lv_msc_tp_coll_window := 0;
3988 END;
3989
3990 IF (MSC_CL_COLLECTION.v_apps_ver = MSC_UTIL.G_APPS107) OR (MSC_CL_COLLECTION.v_apps_ver = MSC_UTIL.G_APPS110) OR lv_msc_tp_coll_window IS NULL THEN
3991 lv_msc_tp_coll_window := 0;
3992 END IF;
3993
3994 begin
3995 select num_rows,decode (stale_stats,'NO', MSC_UTIL.SYS_NO, MSC_UTIL.SYS_YES )
3996 into lv_tp_id_count, lv_tp_stat_stale
3997 from dba_TAB_STATISTICS
3998 where table_name = 'MSC_TP_ID_LID';
3999 exception when no_data_found then
4000 lv_tp_stat_stale := MSC_UTIL.SYS_YES ;
4001 end;
4002
4003 begin
4004 select num_rows,decode (stale_stats,'NO', MSC_UTIL.SYS_NO, MSC_UTIL.SYS_YES )
4005 into lv_tp_site_id_count, lv_tp_site_stat_stale
4006 from dba_TAB_STATISTICS
4007 where table_name ='MSC_TP_SITE_ID_LID';
4008 exception when no_data_found then
4009 lv_tp_site_stat_stale := MSC_UTIL.SYS_YES ;
4010 end;
4011
4012 /* if complete refresh, regen the key mapping data */
4013 IF MSC_CL_COLLECTION.v_is_complete_refresh THEN
4014
4015 IF lv_control_flag = 2 THEN
4016 IF lv_msc_tp_coll_window = 0 THEN
4017 DELETE MSC_TP_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
4018 DELETE MSC_TP_SITE_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
4019 DELETE MSC_TP_ID_LID WHERE SR_INSTANCE_ID= -1;
4020 DELETE MSC_TP_SITE_ID_LID WHERE SR_INSTANCE_ID= -1;
4021 END IF;
4022
4023 ELSE
4024 IF lv_msc_tp_coll_window = 0 THEN
4025 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_TP_ID_LID');
4026 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_TP_SITE_ID_LID');
4027 END IF;
4028
4029 END IF;
4030 lv_tp_stat_stale := MSC_UTIL.SYS_YES ;
4031 lv_tp_site_stat_stale := MSC_UTIL.SYS_YES ;
4032 END IF;
4033
4034 /*************** PREPLACE CHANGE START *****************/
4035
4036
4037 --In case of continuous and targetted collections, delete carrier records from MSC_TP_ID_LID
4038 --when sourcing SRS launch parameter is Yes --and delete supplier and customer records
4039 --from MSC_TP_ID_LID when either Supplier or Customer SRS launch parameter is Yes.
4040
4041 IF (MSC_CL_COLLECTION.v_is_partial_refresh OR MSC_CL_COLLECTION.V_IS_CONT_REFRESH) THEN
4042 IF (MSC_CL_COLLECTION.v_coll_prec.sourcing_rule_flag=MSC_UTIL.SYS_YES) THEN
4043 DELETE MSC_TP_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id and partner_type=4;
4044 DELETE MSC_TP_ID_LID WHERE SR_INSTANCE_ID= -1 and partner_type=4;
4045 lv_tp_stat_stale := MSC_UTIL.SYS_YES ;
4046 END IF;
4047 END IF;
4048
4049 IF MSC_CL_COLLECTION.v_is_partial_refresh THEN
4050
4051 IF ((MSC_CL_COLLECTION.v_coll_prec.tp_vendor_flag = MSC_UTIL.SYS_YES) or
4052 (MSC_CL_COLLECTION.v_coll_prec.tp_customer_flag = MSC_UTIL.SYS_YES)) THEN
4053
4054 -- Note now vendor or customer cannot be refreshed
4055 -- separately. If that functionality needs to be provided
4056 -- in future then the the ID_LID tables will have to
4057 -- be partitioned, so that only either Supplier or
4058 -- Customer information is replaced. The other
4059 -- alternative of course is to conditionally load
4060 -- data using the partner_type as a filter.
4061 -- We do not delete data if the profile "MSC_COLLECTION_WINDOW_FOR_TP_CHANGES" is set to not null.
4062
4063 IF lv_msc_tp_coll_window = 0 THEN
4064 IF lv_control_flag = 2 THEN
4065 DELETE MSC_TP_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id and partner_type in (1,2);
4066 DELETE MSC_TP_SITE_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
4067 DELETE MSC_TP_ID_LID WHERE SR_INSTANCE_ID= -1 and partner_type in (1,2);
4068 DELETE MSC_TP_SITE_ID_LID WHERE SR_INSTANCE_ID= -1;
4069 ELSE
4070 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_TP_ID_LID');
4071 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_TP_SITE_ID_LID');
4072 END IF;
4073 END IF;
4074 lv_tp_stat_stale := MSC_UTIL.SYS_YES ;
4075 lv_tp_site_stat_stale := MSC_UTIL.SYS_YES ;
4076 END IF;
4077
4078 END IF;
4079
4080 /*************** PREPLACE CHANGE END *****************/
4081
4082 --agmcont
4083 IF MSC_CL_COLLECTION.V_IS_CONT_REFRESH THEN
4084
4085 IF ((MSC_CL_COLLECTION.v_coll_prec.tp_vendor_flag = MSC_UTIL.SYS_YES) or
4086 (MSC_CL_COLLECTION.v_coll_prec.tp_customer_flag = MSC_UTIL.SYS_YES)) THEN
4087
4088 -- Note now vendor or customer cannot be refreshed
4089 -- separately. If that functionality needs to be provided
4090 -- in future then the the ID_LID tables will have to
4091 -- be partitioned, so that only either Supplier or
4092 -- Customer information is replaced. The other
4093 -- alternative of course is to conditionally load
4094 -- data using the partner_type as a filter.
4095 -- We do not delete data if the profile "MSC_COLLECTION_WINDOW_FOR_TP_CHANGES" is set to not null.
4096 IF lv_msc_tp_coll_window = 0 THEN
4097 IF lv_control_flag = 2 THEN
4098 DELETE MSC_TP_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id and partner_type in (1,2);
4099 DELETE MSC_TP_SITE_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
4100 DELETE MSC_TP_ID_LID WHERE SR_INSTANCE_ID= -1 and partner_type in (1,2);
4101 DELETE MSC_TP_SITE_ID_LID WHERE SR_INSTANCE_ID= -1;
4102 ELSE
4103 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_TP_ID_LID');
4104 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_TP_SITE_ID_LID');
4105 END IF;
4106 lv_tp_stat_stale := MSC_UTIL.SYS_YES ;
4107 lv_tp_site_stat_stale := MSC_UTIL.SYS_YES ;
4108 END IF;
4109 END IF;
4110
4111 END IF;
4112
4113 COMMIT;
4114
4115 --========== Same VENDOR/CUSTOMER with Changed Name ==========
4116 /*
4117 Commented out this piece of code because it does not work for a case where
4118 the Trading partner name is changed to a name which is already existing in
4119 the msc_trading_partners. This generates a lot of unique index violation
4120 of partner_name, partner_type
4121 This fix will be done with the enhancement: 2700654 */
4122
4123 if (lv_control_flag = 1) then /* Added For Bug 6414426 */
4124 FOR c_rec IN c5_tpname LOOP
4125 SELECT count(*)
4126 INTO lv_partner_count
4127 FROM msc_trading_partners
4128 WHERE
4129 partner_name = c_rec.partner_name
4130 AND partner_type = c_rec.partner_type
4131 AND nvl(company_id, -1) = c_rec.company_id1;
4132
4133 IF (lv_partner_count > 0) THEN
4134
4135 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'Partner is being merged..');
4136 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'Partner name is :'|| c_rec.partner_name);
4137
4138 ELSE
4139 BEGIN
4140
4141 UPDATE MSC_TRADING_PARTNERS mtp
4142 SET mtp.PARTNER_NAME= c_rec.PARTNER_NAME
4143 WHERE mtp.SR_TP_ID= c_rec.SR_TP_ID
4144 AND mtp.SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
4145 AND mtp.PARTNER_TYPE= c_rec.PARTNER_TYPE
4146 AND nvl(mtp.company_id,-1) = c_rec.company_id1;
4147
4148 EXCEPTION
4149 WHEN DUP_VAL_ON_INDEX THEN
4150 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,SQLERRM);
4151 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'In DUP_VAL_ON_INDEX exception clause of c5_tpname');
4152 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'The value of partner name is: '|| c_rec.PARTNER_NAME);
4153 -- Fetch the Old Partner Id
4154 SELECT partner_id
4155 INTO lv_old_partner_id
4156 FROM msc_trading_partners
4157 WHERE
4158 sr_tp_id = c_rec.SR_TP_ID
4159 AND SR_INSTANCE_ID = c_rec.SR_INSTANCE_ID
4160 AND PARTNER_TYPE = c_rec.PARTNER_TYPE
4161 AND nvl(company_id,-1) = c_rec.company_id1;
4162
4163 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'The old Partner_Id IS: ' || to_number(lv_old_partner_id));
4164 -- Fetch the New Partner Id
4165 SELECT partner_id
4166 INTO lv_new_partner_id
4167 FROM msc_trading_partners
4168 WHERE
4169 partner_name= c_rec.partner_name
4170 AND SR_INSTANCE_ID = c_rec.SR_INSTANCE_ID
4171 AND PARTNER_TYPE = c_rec.PARTNER_TYPE
4172 AND nvl(company_id,-1) = c_rec.company_id1;
4173
4174 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'The new Partner_Id IS: ' || to_number(lv_new_partner_id));
4175
4176 /*DELETE FROM MSC_TRADING_PARTNERS
4177 WHERE SR_TP_ID = c_rec.SR_TP_ID
4178 AND SR_INSTANCE_ID = c_rec.SR_INSTANCE_ID
4179 AND PARTNER_TYPE = c_rec.PARTNER_TYPE;
4180
4181 UPDATE MSC_TRADING_PARTNER_SITES
4182 SET PARTNER_ID = lv_new_partner_id,
4183 LAST_UPDATE_DATE = v_current_date,
4184 LAST_UPDATED_BY = v_current_user
4185 WHERE PARTNER_ID = lv_old_partner_id
4186 AND SR_INSTANCE_ID = c_rec.SR_INSTANCE_ID
4187 AND PARTNER_TYPE = c_rec.PARTNER_TYPE;
4188
4189 UPDATE MSC_TP_ID_LID
4190 SET TP_ID = lv_new_partner_id
4191 WHERE SR_TP_ID = c_rec.SR_TP_ID
4192 AND SR_INSTANCE_ID = c_rec.SR_INSTANCE_ID
4193 AND PARTNER_TYPE = c_rec.PARTNER_TYPE;
4194 */
4195
4196 WHEN OTHERS THEN
4197
4198 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
4199
4200 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4201 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4202 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'MSC_CL_SETUP_ODS_LOAD.TRANSFORM_KEYS');
4203 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TRADING_PARTNERS');
4204 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4205
4206 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4207 FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_TYPE');
4208 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_TYPE);
4209 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4210
4211 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4212 FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TP_ID');
4213 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SR_TP_ID);
4214 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4215
4216 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4217 FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_NAME');
4218 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_NAME);
4219 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4220
4221 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4222
4223 END;
4224 END IF;-- IF (lv_partner_count > 0) THEN
4225 END LOOP;
4226 END IF;-- if (lv_control_flag = 1) then
4227
4228 COMMIT;
4229
4230 --========== VENDOR/CUSTOMER ==========
4231
4232 FOR c_rec IN c5 LOOP
4233
4234 BEGIN
4235
4236 INSERT INTO MSC_TRADING_PARTNERS
4237 ( /* SCE Change starts */
4238 COMPANY_ID,
4239 /* SCE Change ends */
4240 PARTNER_NAME,
4241 PARTNER_ID,
4242 SR_TP_ID,
4243 PARTNER_TYPE,
4244 PARTNER_NUMBER,
4245 MASTER_ORGANIZATION,
4246 SR_INSTANCE_ID,
4247 REFRESH_NUMBER,
4248 LAST_UPDATE_DATE,
4249 LAST_UPDATED_BY,
4250 CREATION_DATE,
4251 CREATED_BY)
4252 VALUES
4253 ( /* SCE Change starts */
4254 c_rec.COMPANY_ID1,
4255 /* SCE Change ends */
4256 c_rec.Partner_Name,
4257 MSC_Trading_Partners_S.NEXTVAL,
4258 MSC_Trading_Partners_S.NEXTVAL, -- dummy value to satisfy the unique constraint
4259 c_rec.Partner_Type,
4260 -1,
4261 -1,
4262 -1,
4263 MSC_CL_COLLECTION.v_last_collection_id,
4264 MSC_CL_COLLECTION.v_current_date,
4265 MSC_CL_COLLECTION.v_current_user,
4266 MSC_CL_COLLECTION.v_current_date,
4267 MSC_CL_COLLECTION.v_current_user );
4268
4269 EXCEPTION
4270
4271 WHEN DUP_VAL_ON_INDEX THEN
4272
4273 NULL;
4274
4275 WHEN OTHERS THEN
4276
4277 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4278 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4279 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
4280 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TRADING_PARTNERS');
4281 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4282
4283 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4284 FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_NAME');
4285 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_NAME);
4286 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4287
4288 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4289
4290 ERRBUF := sqlerrm;
4291 RETCODE := MSC_UTIL.G_ERROR;
4292 RAISE;
4293
4294 END;
4295
4296 END LOOP;
4297
4298 COMMIT;
4299
4300 lv_ins_records := 0;
4301 -- ==== populate msc_tp_id_lid with newly created Suppliers / Customers ====
4302 IF MSC_CL_COLLECTION.v_apps_ver < MSC_UTIL.G_APPS115 AND MSC_CL_COLLECTION.v_apps_ver <> -1 THEN
4303
4304 /* For 107 and 110 the vendor_id and vendor
4305 site id can be duplicate, therefore we use
4306 the cursors to handle such exceptions, but for 11i we can use a straight
4307 Insert-as-select to improve performance */
4308
4309 FOR c_rec IN c12 LOOP
4310
4311 BEGIN
4312
4313 INSERT INTO MSC_TP_ID_LID
4314 ( /* SCE Change starts */
4315 SR_COMPANY_ID,
4316 /* SCE change ends */
4317 SR_TP_ID,
4318 SR_INSTANCE_ID,
4319 Partner_Type,
4320 TP_ID)
4321 VALUES
4322 ( c_rec.SR_COMPANY_ID,
4323 c_rec.SR_TP_ID,
4324 c_rec.SR_INSTANCE_ID,
4325 c_rec.PARTNER_TYPE,
4326 c_rec.PARTNER_ID);
4327
4328 EXCEPTION
4329
4330 WHEN OTHERS THEN
4331
4332 IF SQLCODE IN (-1653,-1654) THEN
4333
4334 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4335 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4336 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
4337 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TP_ID_LID');
4338 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4339
4340 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4341 FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TP_ID');
4342 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SR_TP_ID);
4343 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4344
4345 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4346 FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_TYPE');
4347 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_TYPE);
4348 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4349
4350 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4351 FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_ID');
4352 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_ID);
4353 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4354
4355 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4356
4357 ERRBUF := sqlerrm;
4358 RETCODE := MSC_UTIL.G_ERROR;
4359 RAISE;
4360
4361 ELSE
4362
4363 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
4364
4365 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4366 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4367 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
4368 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TP_ID_LID');
4369 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4370
4371 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4372 FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TP_ID');
4373 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SR_TP_ID);
4374 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4375
4376 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4377 FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_TYPE');
4378 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_TYPE);
4379 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4380
4381 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4382 FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_ID');
4383 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_ID);
4384 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4385
4386 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4387 ERRBUF := sqlerrm;
4388 RETCODE := MSC_UTIL.G_WARNING;
4389
4390 END IF;
4391
4392 END;
4393
4394 END LOOP;
4395
4396 ELSE
4397
4398 INSERT /*+ APPEND */ INTO MSC_TP_ID_LID
4399 ( SR_COMPANY_ID,
4400 SR_TP_ID,
4401 SR_INSTANCE_ID,
4402 Partner_Type,
4403 TP_ID,
4404 AGGREGATE_DEMAND_FLAG,
4405 SR_CUST_ACCOUNT_NUMBER)
4406 SELECT distinct
4407 nvl(mst.company_id, -1) SR_COMPANY_ID,
4408 mst.SR_TP_ID,
4409 mst.SR_INSTANCE_ID,
4410 mst.Partner_Type,
4411 mtp.PARTNER_ID,
4412 mst.AGGREGATE_DEMAND_FLAG,
4413 mst.CUST_ACCOUNT_NUMBER
4414 FROM MSC_ST_TRADING_PARTNERS mst,
4415 MSC_TRADING_PARTNERS mtp,
4416 MSC_COMPANIES mc
4417 WHERE NOT EXISTS( select 1
4418 from MSC_TP_ID_LID mtil
4419 where mst.SR_TP_ID= mtil.SR_TP_ID
4420 and mst.SR_INSTANCE_ID= mtil.SR_INSTANCE_ID
4421 and nvl( mst.company_id, -1) = nvl(mtil.sr_company_id, -1)
4422 and nvl( mst.company_id, -1) = -1
4423 and mst.Partner_Type= mtil.Partner_Type)
4424 AND mst.Partner_NAME= mtp.Partner_NAME
4425 AND mst.Partner_Type= mtp.Partner_Type
4426 AND mst.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
4427 AND mst.Partner_Type IN ( 1, 2,4)
4428 AND nvl(mst.company_name, MSC_CL_COLLECTION.v_my_company_name) = mc.company_name
4429 AND mc.company_id = nvl(mtp.company_id, MSC_CL_COLLECTION.G_MY_COMPANY_ID)
4430 and nvl( mst.company_id, -1) = -1;
4431 lv_ins_records := SQL%ROWCOUNT;
4432 END IF;
4433
4434 COMMIT;
4435 /* Bug7679044 */
4436 IF lv_tp_stat_stale = MSC_UTIL.SYS_YES OR lv_ins_records > lv_tp_id_count * 0.2 THEN
4437 msc_analyse_tables_pk.analyse_table( 'MSC_TP_ID_LID');
4438 lv_tp_stat_stale := MSC_UTIL.SYS_NO;
4439 lv_tp_id_count := lv_tp_id_count + lv_ins_records;
4440 END IF;
4441
4442 -- ==== Update msc_tp_id_lid with resource_type ==== SRP Changes
4443 FOR c_rec IN c13 LOOP
4444 BEGIN
4445
4446 UPDATE MSC_TP_ID_LID
4447 set
4448 resource_type = c_rec.resource_type
4449 WHERE sr_tp_id= c_rec.sr_tp_id And
4450 partner_type=2 And
4451 sr_instance_id = MSC_CL_COLLECTION.v_instance_id;
4452
4453 END;
4454
4455 END LOOP; --c13 crec loop
4456
4457 COMMIT;
4458
4459
4460 -- ==== Populate msc_trading_partner_sites with new Supplier Sites ====
4461
4462 FOR c_rec IN c6 LOOP
4463
4464 BEGIN
4465
4466 INSERT INTO MSC_TRADING_PARTNER_SITES
4467 ( TP_SITE_CODE,
4468 PARTNER_ID,
4469 PARTNER_SITE_ID,
4470 SR_TP_SITE_ID,
4471 PARTNER_TYPE,
4472 SR_INSTANCE_ID,
4473 REFRESH_NUMBER,
4474 LAST_UPDATE_DATE,
4475 LAST_UPDATED_BY,
4476 CREATION_DATE,
4477 CREATED_BY)
4478 VALUES
4479 ( c_rec.TP_Site_Code,
4480 c_rec.TP_ID,
4481 MSC_Trading_Partner_Sites_S.NEXTVAL,
4482 MSC_Trading_Partner_Sites_S.NEXTVAL,
4483 1,
4484 MSC_CL_COLLECTION.v_instance_id,
4485 MSC_CL_COLLECTION.v_last_collection_id,
4486 MSC_CL_COLLECTION.v_current_date,
4487 MSC_CL_COLLECTION.v_current_user,
4488 MSC_CL_COLLECTION.v_current_date,
4489 MSC_CL_COLLECTION.v_current_user);
4490
4491 EXCEPTION
4492
4493 WHEN DUP_VAL_ON_INDEX THEN
4494
4495 NULL;
4496
4497 WHEN OTHERS THEN
4498
4499 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4500 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4501 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
4502 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TRADING_PARTNER_SITES');
4503 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4504
4505 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4506 FND_MESSAGE.SET_TOKEN('COLUMN', 'TP_ID');
4507 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.TP_ID);
4508 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4509
4510 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4511 FND_MESSAGE.SET_TOKEN('COLUMN', 'TP_SITE_CODE');
4512 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.TP_SITE_CODE);
4513 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4514
4515 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4516
4517 ERRBUF := sqlerrm;
4518 RETCODE := MSC_UTIL.G_ERROR;
4519 RAISE;
4520
4521 END;
4522
4523 END LOOP;
4524
4525 COMMIT;
4526 lv_ins_records := 0;
4527 -- ==== Populate msc_tp_site_id_lid with new Supplier Sites ====
4528 IF MSC_CL_COLLECTION.v_apps_ver < MSC_UTIL.G_APPS115 THEN
4529
4530 /* For 107 and 110 the vendor_id and vendor
4531 site id can be duplicate, therefore we use
4532 the cursors to handle such exceptions, but for 11i we can use a straight
4533 Insert-as-select to improve performance */
4534
4535 FOR c_rec IN c9 LOOP
4536
4537 BEGIN
4538
4539 INSERT INTO MSC_TP_SITE_ID_LID
4540 ( /* SCE Change starts */
4541 SR_COMPANY_ID,
4542 /* SCE Change ends */
4543 SR_TP_SITE_ID,
4544 SR_INSTANCE_ID,
4545 Partner_Type,
4546 TP_SITE_ID)
4547 VALUES
4548 ( c_rec.SR_COMPANY_ID,
4549 c_rec.SR_TP_SITE_ID,
4550 c_rec.SR_INSTANCE_ID,
4551 1,
4552 c_rec.PARTNER_SITE_ID);
4553
4554 EXCEPTION
4555
4556 WHEN DUP_VAL_ON_INDEX THEN
4557
4558 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
4559
4560 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4561 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4562 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
4563 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TP_SITE_ID_LID');
4564 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4565
4566 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4567 FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_TYPE');
4568 FND_MESSAGE.SET_TOKEN('VALUE', 1);
4569 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4570
4571 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4572 FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TP_SITE_ID');
4573 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SR_TP_SITE_ID);
4574 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4575
4576 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4577 FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_SITE_ID');
4578 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_SITE_ID);
4579 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4580
4581 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4582 ERRBUF := sqlerrm;
4583 RETCODE := MSC_UTIL.G_WARNING;
4584
4585 WHEN OTHERS THEN
4586
4587 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4588 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4589 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
4590 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TP_SITE_ID_LID');
4591 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4592
4593 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4594 FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_TYPE');
4595 FND_MESSAGE.SET_TOKEN('VALUE', 1);
4596 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4597
4598 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4599 FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TP_SITE_ID');
4600 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SR_TP_SITE_ID);
4601 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4602
4603 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4604 FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_SITE_ID');
4605 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_SITE_ID);
4606 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4607
4608 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4609
4610 ERRBUF := sqlerrm;
4611 RETCODE := MSC_UTIL.G_ERROR;
4612 RAISE;
4613
4614 END;
4615
4616 END LOOP;
4617
4618 ELSE
4619
4620 INSERT /*+ APPEND */ INTO MSC_TP_SITE_ID_LID
4621 ( SR_COMPANY_ID,
4622 SR_TP_SITE_ID,
4623 SR_INSTANCE_ID,
4624 Partner_Type,
4625 location_id,
4626 operating_unit,
4627 TP_SITE_ID)
4628 SELECT distinct
4629 nvl(msts.company_id, -1) SR_COMPANY_ID,
4630 msts.SR_TP_SITE_ID,
4631 msts.SR_INSTANCE_ID,
4632 1,
4633 msts.location_id,
4634 msts.operating_unit,
4635 mtp.PARTNER_SITE_ID
4636 FROM MSC_ST_TRADING_PARTNER_SITES msts,
4637 MSC_TP_ID_LID mtil,
4638 MSC_TRADING_PARTNER_SITES mtp
4639 WHERE NOT EXISTS( select 1
4640 from MSC_TP_SITE_ID_LID mtsil
4641 where msts.SR_TP_SITE_ID= mtsil.SR_TP_SITE_ID
4642 and msts.SR_INSTANCE_ID= mtsil.SR_INSTANCE_ID
4643 and mtsil.Partner_Type= 1
4644 and nvl(msts.company_id, -1) = mtsil.sr_company_id)
4645 AND msts.TP_Site_Code= mtp.TP_Site_Code
4646 AND msts.SR_TP_ID= mtil.SR_TP_ID
4647 AND msts.SR_INSTANCE_ID= mtil.SR_INSTANCE_ID
4648 AND msts.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
4649 AND nvl(msts.company_id, -1) = mtil.SR_COMPANY_ID
4650 AND mtil.TP_ID= mtp.Partner_ID
4651 AND mtp.partner_type = mtil.partner_type
4652 AND mtil.Partner_Type= msts.partner_type
4653 AND msts.Partner_Type= 1;
4654 lv_ins_records := SQL%ROWCOUNT;
4655 END IF;
4656
4657 COMMIT;
4658 /* Bug7679044 */
4659 IF lv_tp_site_stat_stale = MSC_UTIL.SYS_YES OR lv_ins_records > lv_tp_site_id_count * 0.2 THEN
4660 msc_analyse_tables_pk.analyse_table( 'MSC_TP_SITE_ID_LID');
4661 lv_tp_site_stat_stale := MSC_UTIL.SYS_NO;
4662 lv_tp_site_id_count := lv_tp_site_id_count + lv_ins_records;
4663 END IF;
4664
4665 --========== CUSTOMER SITE ==========
4666
4667 FOR c_rec IN c7 LOOP
4668
4669 BEGIN
4670
4671 INSERT INTO MSC_TRADING_PARTNER_SITES
4672 ( TP_SITE_CODE,
4673 LOCATION,
4674 OPERATING_UNIT_NAME,
4675 PARTNER_ID,
4676 PARTNER_SITE_ID,
4677 SR_TP_SITE_ID,
4678 PARTNER_TYPE,
4679 SR_INSTANCE_ID,
4680 REFRESH_NUMBER,
4681 LAST_UPDATE_DATE,
4682 LAST_UPDATED_BY,
4683 CREATION_DATE,
4684 CREATED_BY)
4685 VALUES
4686 ( c_rec.TP_Site_Code,
4687 c_rec.Location,
4688 c_rec.OPERATING_UNIT_NAME,
4689 c_rec.TP_ID,
4690 MSC_Trading_Partner_Sites_S.NEXTVAL,
4691 MSC_Trading_Partner_Sites_S.NEXTVAL,
4692 2,
4693 MSC_CL_COLLECTION.v_instance_id,
4694 MSC_CL_COLLECTION.v_last_collection_id,
4695 MSC_CL_COLLECTION.v_current_date,
4696 MSC_CL_COLLECTION.v_current_user,
4697 MSC_CL_COLLECTION.v_current_date,
4698 MSC_CL_COLLECTION.v_current_user );
4699
4700 EXCEPTION
4701
4702 WHEN DUP_VAL_ON_INDEX THEN
4703
4704 NULL;
4705
4706 WHEN OTHERS THEN
4707
4708 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4709 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4710 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
4711 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TRADING_PARTNER_SITES');
4712 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4713
4714 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4715 FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_TYPE');
4716 FND_MESSAGE.SET_TOKEN('VALUE', 1);
4717 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4718
4719 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4720 FND_MESSAGE.SET_TOKEN('COLUMN', 'TP_ID');
4721 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.TP_ID);
4722 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4723
4724 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4725 FND_MESSAGE.SET_TOKEN('COLUMN', 'TP_SITE_CODE');
4726 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.TP_SITE_CODE);
4727 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4728
4729 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4730 FND_MESSAGE.SET_TOKEN('COLUMN', 'LOCATION');
4731 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.LOCATION);
4732 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4733
4734 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4735
4736 ERRBUF := sqlerrm;
4737 RETCODE := MSC_UTIL.G_ERROR;
4738 RAISE;
4739
4740 END;
4741
4742
4743 END LOOP;
4744
4745 COMMIT;
4746
4747 lv_ins_records := 0;
4748 IF MSC_CL_COLLECTION.v_apps_ver < MSC_UTIL.G_APPS115 THEN
4749
4750 /* For 107 and 110 the vendor_id and vendor
4751 site id can be duplicate, therefore we use
4752 the cursors to handle such exceptions, but for 11i we can use a straight
4753 Insert-as-select to improve performance */
4754
4755 FOR c_rec IN c10 LOOP
4756
4757 BEGIN
4758
4759 INSERT INTO MSC_TP_SITE_ID_LID
4760 ( /* SCE Change starts*/
4761 SR_COMPANY_ID,
4762 /* SCE Change ends*/
4763 SR_TP_SITE_ID,
4764 SR_INSTANCE_ID,
4765 Partner_Type,
4766 TP_SITE_ID)
4767 VALUES
4768 ( c_rec.SR_COMPANY_ID,
4769 c_rec.SR_TP_SITE_ID,
4770 c_rec.SR_INSTANCE_ID,
4771 2,
4772 c_rec.PARTNER_SITE_ID);
4773
4774
4775 EXCEPTION
4776
4777 WHEN DUP_VAL_ON_INDEX THEN
4778
4779 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
4780
4781 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4782 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4783 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
4784 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TP_SITE_ID_LID');
4785 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4786
4787 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4788 FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_TYPE');
4789 FND_MESSAGE.SET_TOKEN('VALUE', 2);
4790 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4791
4792 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4793 FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TP_SITE_ID');
4794 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SR_TP_SITE_ID);
4795 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4796
4797 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4798 FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_SITE_ID');
4799 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_SITE_ID);
4800 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4801
4802 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4803 ERRBUF := sqlerrm;
4804 RETCODE := MSC_UTIL.G_WARNING;
4805
4806 WHEN OTHERS THEN
4807
4808 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4809 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4810 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
4811 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TP_SITE_ID_LID');
4812 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4813
4814 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4815 FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_TYPE');
4816 FND_MESSAGE.SET_TOKEN('VALUE', 2);
4817 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4818
4819 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4820 FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TP_SITE_ID');
4821 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SR_TP_SITE_ID);
4822 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4823
4824 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4825 FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_SITE_ID');
4826 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_SITE_ID);
4827 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4828
4829 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4830
4831 ERRBUF := sqlerrm;
4832 RETCODE := MSC_UTIL.G_ERROR;
4833 RAISE;
4834
4835 END;
4836
4837 END LOOP;
4838
4839 ELSE
4840
4841 INSERT /*+ APPEND */ INTO MSC_TP_SITE_ID_LID
4842 ( SR_COMPANY_ID,
4843 SR_TP_SITE_ID,
4844 SR_INSTANCE_ID,
4845 Partner_Type,
4846 location_id,
4847 TP_SITE_ID,
4848 SR_CUST_ACCT_ID)
4849 SELECT distinct
4850 nvl(msts.company_id, -1) SR_COMPANY_ID,
4851 msts.SR_TP_SITE_ID,
4852 msts.SR_INSTANCE_ID,
4853 2,
4854 msts.location_id,
4855 mtp.PARTNER_SITE_ID,
4856 msts.SR_TP_ID
4857 FROM MSC_ST_TRADING_PARTNER_SITES msts,
4858 MSC_TP_ID_LID mtil,
4859 MSC_TRADING_PARTNER_SITES mtp
4860 WHERE NOT EXISTS( select 1
4861 from MSC_TP_SITE_ID_LID mtsil
4862 where msts.SR_TP_SITE_ID= mtsil.SR_TP_SITE_ID
4863 and msts.SR_INSTANCE_ID= mtsil.SR_INSTANCE_ID
4864 and mtsil.Partner_Type= 2
4865 and nvl(msts.company_id, -1) = mtsil.sr_company_id)
4866 AND NVL( msts.Operating_Unit_Name, ' ')=
4867 NVL( mtp.Operating_Unit_Name, ' ')
4868 AND msts.TP_Site_Code= mtp.TP_Site_Code
4869 AND msts.Location= mtp.Location
4870 AND msts.SR_TP_ID= mtil.SR_TP_ID
4871 AND msts.SR_INSTANCE_ID= mtil.SR_INSTANCE_ID
4872 AND msts.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
4873 AND nvl(msts.company_id, -1) = mtil.SR_COMPANY_ID
4874 AND mtil.TP_ID= mtp.Partner_ID
4875 AND mtp.partner_type = mtil.partner_type
4876 AND mtil.Partner_Type= msts.partner_type
4877 AND msts.Partner_Type= 2;
4878 lv_ins_records := SQL%ROWCOUNT;
4879 END IF;
4880
4881 COMMIT;
4882 /* Bug7679044 */
4883 IF lv_tp_site_stat_stale = MSC_UTIL.SYS_YES OR lv_ins_records > lv_tp_site_id_count * 0.2 THEN
4884 msc_analyse_tables_pk.analyse_table( 'MSC_TP_SITE_ID_LID');
4885 lv_tp_site_stat_stale := MSC_UTIL.SYS_NO;
4886 lv_tp_site_id_count := lv_tp_site_id_count + lv_ins_records;
4887 END IF;
4888
4889 --================ Collect Vendor/Customer
4890
4891 FOR c_rec IN c1 LOOP
4892
4893 BEGIN
4894
4895 UPDATE MSC_TRADING_PARTNERS mtp
4896 SET ORGANIZATION_CODE= c_rec.ORGANIZATION_CODE,
4897 SR_TP_ID= c_rec.SR_TP_ID,
4898 DISABLE_DATE= c_rec.Disable_Date,
4899 STATUS= c_rec.Status,
4900 MASTER_ORGANIZATION= c_rec.Master_Organization,
4901 WEIGHT_UOM= c_rec.WEIGHT_UOM,
4902 MAXIMUM_WEIGHT= c_rec.MAXIMUM_WEIGHT,
4903 VOLUME_UOM= c_rec.VOLUME_UOM,
4904 MAXIMUM_VOLUME= c_rec.MAXIMUM_VOLUME,
4905 PARTNER_NUMBER= c_rec.PARTNER_NUMBER,
4906 CALENDAR_CODE= c_rec.CALENDAR_CODE,
4907 CALENDAR_EXCEPTION_SET_ID= c_rec.CALENDAR_EXCEPTION_SET_ID,
4908 OPERATING_UNIT= c_rec.OPERATING_UNIT,
4909 SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID,
4910 PROJECT_REFERENCE_ENABLED= c_rec.PROJECT_REFERENCE_ENABLED,
4911 PROJECT_CONTROL_LEVEL= c_rec.PROJECT_CONTROL_LEVEL,
4912 CUSTOMER_CLASS_CODE = c_rec.CUSTOMER_CLASS_CODE,
4913 CUSTOMER_TYPE = c_rec.CUSTOMER_TYPE,
4914 LAST_UPDATE_DATE = MSC_CL_COLLECTION.v_current_date,
4915 LAST_UPDATED_BY = MSC_CL_COLLECTION.v_current_user,
4916 CREATION_DATE = MSC_CL_COLLECTION.v_current_date,
4917 CREATED_BY = MSC_CL_COLLECTION.v_current_user
4918 WHERE mtp.Partner_ID= c_rec.TP_ID;
4919
4920 EXCEPTION
4921
4922 WHEN DUP_VAL_ON_INDEX THEN
4923
4924 NULL;
4925
4926 WHEN OTHERS THEN
4927
4928 IF SQLCODE IN (-01683,-1653,-1654) THEN
4929
4930 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4931 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4932 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
4933 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TRADING_PARTNERS');
4934 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4935
4936 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4937 FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_TYPE');
4938 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_TYPE);
4939 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4940
4941 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4942 FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TP_ID');
4943 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SR_TP_ID);
4944 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4945
4946 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4947
4948 ERRBUF := sqlerrm;
4949 RETCODE := MSC_UTIL.G_ERROR;
4950 RAISE;
4951
4952 ELSE
4953
4954 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
4955
4956 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4957 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4958 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
4959 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TRADING_PARTNERS');
4960 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4961
4962 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4963 FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_TYPE');
4964 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_TYPE);
4965 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4966
4967 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4968 FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TP_ID');
4969 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SR_TP_ID);
4970 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4971
4972 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4973 ERRBUF := sqlerrm;
4974 RETCODE := MSC_UTIL.G_WARNING;
4975
4976 END IF;
4977
4978 END;
4979
4980 END LOOP;
4981
4982 COMMIT;
4983
4984 --================ Collect Vendor/Customer Sites
4985
4986 FOR c_rec IN c2 LOOP
4987
4988 BEGIN
4989 UPDATE MSC_Trading_Partner_Sites mtps
4990 SET mtps.PARTNER_ADDRESS= c_rec.PARTNER_ADDRESS,
4991 mtps.LONGITUDE= NVL( c_rec.LONGITUDE, mtps.LONGITUDE),
4992 mtps.LATITUDE= NVL( c_rec.LATITUDE, mtps.LATITUDE),
4993 mtps.SR_TP_SITE_ID= c_rec.SR_TP_SITE_ID,
4994 mtps.PARTNER_TYPE= c_rec.Partner_Type,
4995 mtps.POSTAL_CODE = c_rec.POSTAL_CODE,
4996 mtps.CITY = c_rec.CITY,
4997 mtps.STATE = c_rec.STATE,
4998 mtps.COUNTRY = c_rec.COUNTRY,
4999 mtps.SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID,
5000 mtps.DELETED_FLAG= MSC_UTIL.SYS_NO,
5001 mtps.REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
5002 mtps.SHIPPING_CONTROL=c_rec.SHIPPING_CONTROL,
5003 mtps.LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
5004 mtps.LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
5005 mtps.CREATION_DATE= MSC_CL_COLLECTION.v_current_date,
5006 mtps.CREATED_BY= MSC_CL_COLLECTION.v_current_user
5007 WHERE mtps.PARTNER_SITE_ID= c_rec.TP_SITE_ID;
5008
5009 EXCEPTION
5010
5011 WHEN OTHERS THEN
5012
5013 IF SQLCODE IN (-1653,-1654) THEN
5014
5015 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
5016 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
5017 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
5018 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TRADING_PARTNER_SITES');
5019 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
5020
5021 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
5022 FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_TYPE');
5023 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_TYPE);
5024 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
5025
5026 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
5027 FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TP_SITE_ID');
5028 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SR_TP_SITE_ID);
5029 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
5030
5031 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
5032
5033 ERRBUF := sqlerrm;
5034 RETCODE := MSC_UTIL.G_ERROR;
5035 RAISE;
5036
5037 ELSE
5038
5039 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
5040
5041 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
5042 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
5043 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_TRADING_PARTNER_KEYS');
5044 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TRADING_PARTNER_SITES');
5045 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
5046
5047 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
5048 FND_MESSAGE.SET_TOKEN('COLUMN', 'PARTNER_TYPE');
5049 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PARTNER_TYPE);
5050 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
5051
5052 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
5053 FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TP_SITE_ID');
5054 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SR_TP_SITE_ID);
5055 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
5056
5057 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
5058 ERRBUF := sqlerrm;
5059 RETCODE := MSC_UTIL.G_WARNING;
5060
5061 END IF;
5062
5063 END;
5064
5065 END LOOP;
5066
5067 COMMIT;
5068
5069 /* analyse the key mapping tables */
5070 IF lv_tp_stat_stale = MSC_UTIL.SYS_YES THEN
5071 msc_analyse_tables_pk.analyse_table( 'MSC_TP_ID_LID');
5072 END IF;
5073 IF lv_tp_site_stat_stale = MSC_UTIL.SYS_YES THEN
5074 msc_analyse_tables_pk.analyse_table( 'MSC_TP_SITE_ID_LID');
5075 END IF;
5076
5077 EXCEPTION
5078
5079 WHEN OTHERS THEN
5080
5081 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
5082
5083 ERRBUF := sqlerrm;
5084 RETCODE := MSC_UTIL.G_ERROR;
5085 RAISE;
5086
5087 END GENERATE_TRADING_PARTNER_KEYS;
5088
5089 /*************** PREPLACE CHANGE START *****************/
5090
5091 PROCEDURE GET_COLL_PARAM (p_instance_id NUMBER) AS
5092
5093 BEGIN
5094
5095 /* Initialize the global prec record variable */
5096
5097 IF (MSC_CL_COLLECTION.v_prec_defined = FALSE) THEN
5098 SELECT delete_ods_data,org_group, supplier_capacity, atp_rules,
5099 bom, bor, calendar_check, demand_class,ITEM_SUBSTITUTES, forecast, item,
5100 kpi_targets_bis, mds, mps, oh, parameter, planners,
5101 projects, po, reservations, nra, safety_stock,
5102 sales_order, sourcing_history, sourcing, sub_inventories,
5103 customer, supplier, unit_numbers, uom, user_supply_demand, wip, user_comp_association,po_receipts,
5104 bom_sn_flag, bor_sn_flag, item_sn_flag, oh_sn_flag,
5105 usup_sn_flag, udmd_sn_flag, so_sn_flag, fcst_sn_flag,
5106 wip_sn_flag, supcap_sn_flag, po_sn_flag, mds_sn_flag,
5107 mps_sn_flag, nosnap_flag
5108 /* CP-ACK starts */
5109 ,supplier_response
5110 /* CP-ACK ends */
5111 /* CP-AUTO */
5112 ,suprep_sn_flag, trip,trip_sn_flag,ds_mode
5113 , sales_channel, fiscal_calendar,internal_repair,external_repair -- changed for bug 5909379 SRP addition
5114 ,payback_demand_supply
5115 ,currency_conversion,delivery_details
5116 INTO MSC_CL_COLLECTION.v_coll_prec.purge_ods_flag,MSC_CL_COLLECTION.v_coll_prec.org_group_flag, MSC_CL_COLLECTION.v_coll_prec.app_supp_cap_flag,
5117 MSC_CL_COLLECTION.v_coll_prec.atp_rules_flag, MSC_CL_COLLECTION.v_coll_prec.bom_flag,
5118 MSC_CL_COLLECTION.v_coll_prec.bor_flag, MSC_CL_COLLECTION.v_coll_prec.calendar_flag,
5119 MSC_CL_COLLECTION.v_coll_prec.demand_class_flag,MSC_CL_COLLECTION.v_coll_prec.item_subst_flag, MSC_CL_COLLECTION.v_coll_prec.forecast_flag,
5120 MSC_CL_COLLECTION.v_coll_prec.item_flag, MSC_CL_COLLECTION.v_coll_prec.kpi_bis_flag,
5121 MSC_CL_COLLECTION.v_coll_prec.mds_flag, MSC_CL_COLLECTION.v_coll_prec.mps_flag,
5122 MSC_CL_COLLECTION.v_coll_prec.oh_flag, MSC_CL_COLLECTION.v_coll_prec.parameter_flag,
5123 MSC_CL_COLLECTION.v_coll_prec.planner_flag, MSC_CL_COLLECTION.v_coll_prec.project_flag,
5124 MSC_CL_COLLECTION.v_coll_prec.po_flag, MSC_CL_COLLECTION.v_coll_prec.reserves_flag,
5125 MSC_CL_COLLECTION.v_coll_prec.resource_nra_flag, MSC_CL_COLLECTION.v_coll_prec.saf_stock_flag,
5126 MSC_CL_COLLECTION.v_coll_prec.sales_order_flag, MSC_CL_COLLECTION.v_coll_prec.source_hist_flag,
5127 MSC_CL_COLLECTION.v_coll_prec.sourcing_rule_flag, MSC_CL_COLLECTION.v_coll_prec.sub_inventory_flag,
5128 MSC_CL_COLLECTION.v_coll_prec.tp_customer_flag, MSC_CL_COLLECTION.v_coll_prec.tp_vendor_flag,
5129 MSC_CL_COLLECTION.v_coll_prec.unit_number_flag, MSC_CL_COLLECTION.v_coll_prec.uom_flag,
5130 MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag, MSC_CL_COLLECTION.v_coll_prec.wip_flag, MSC_CL_COLLECTION.v_coll_prec.user_company_flag,
5131 MSC_CL_COLLECTION.v_coll_prec.po_receipts_flag,
5132 MSC_CL_COLLECTION.v_coll_prec.bom_sn_flag, MSC_CL_COLLECTION.v_coll_prec.bor_sn_flag,
5133 MSC_CL_COLLECTION.v_coll_prec.item_sn_flag, MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag,
5134 MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag, MSC_CL_COLLECTION.v_coll_prec.udmd_sn_flag,
5135 MSC_CL_COLLECTION.v_coll_prec.so_sn_flag, MSC_CL_COLLECTION.v_coll_prec.fcst_sn_flag,
5136 MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag,
5137 MSC_CL_COLLECTION.v_coll_prec.supcap_sn_flag, MSC_CL_COLLECTION.v_coll_prec.po_sn_flag,
5138 MSC_CL_COLLECTION.v_coll_prec.mds_sn_flag, MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag,
5139 MSC_CL_COLLECTION.v_coll_prec.nosnap_flag
5140 /* CP-ACK starts */
5141 ,MSC_CL_COLLECTION.v_coll_prec.supplier_response_flag
5142 /* CP-ACK ends */
5143 /* CP-AUTO */
5144 ,MSC_CL_COLLECTION.v_coll_prec.suprep_sn_flag, MSC_CL_COLLECTION.v_coll_prec.trip_flag,MSC_CL_COLLECTION.v_coll_prec.trip_sn_flag , MSC_CL_COLLECTION.v_coll_prec.ds_mode
5145 ,MSC_CL_COLLECTION.v_coll_prec.sales_channel_flag,MSC_CL_COLLECTION.v_coll_prec.fiscal_calendar_flag,MSC_CL_COLLECTION.v_coll_prec.internal_repair_flag,MSC_CL_COLLECTION.v_coll_prec.external_repair_flag
5146 ,MSC_CL_COLLECTION.v_coll_prec.payback_demand_supply_flag
5147 ,MSC_CL_COLLECTION.v_coll_prec.currency_conversion_flag
5148 ,MSC_CL_COLLECTION.v_coll_prec.delivery_details_flag
5149 FROM msc_coll_parameters
5150 WHERE instance_id = p_instance_id;
5151 MSC_CL_COLLECTION.v_prec_defined := TRUE;
5152 END IF;
5153
5154 END GET_COLL_PARAM;
5155
5156 PROCEDURE GET_COLL_PARAM
5157 (p_instance_id IN NUMBER,
5158 v_prec OUT NOCOPY MSC_CL_EXCHANGE_PARTTBL.CollParamREC) AS
5159
5160 BEGIN
5161
5162 /* Initialize the global prec record variable */
5163
5164 SELECT delete_ods_data,org_group, supplier_capacity, atp_rules,
5165 bom, bor, calendar_check, demand_class,ITEM_SUBSTITUTES, forecast, item,
5166 kpi_targets_bis, mds, mps, oh, parameter, planners,
5167 projects, po, reservations, nra, safety_stock,
5168 sales_order, sourcing_history, sourcing, sub_inventories,
5169 customer, supplier, unit_numbers, uom, user_supply_demand, wip, user_comp_association,
5170 po_receipts,
5171 bom_sn_flag, bor_sn_flag, item_sn_flag, oh_sn_flag,
5172 usup_sn_flag, udmd_sn_flag, so_sn_flag, fcst_sn_flag,
5173 wip_sn_flag, supcap_sn_flag, po_sn_flag, mds_sn_flag,
5174 mps_sn_flag, nosnap_flag
5175 /* CP-ACK starts */
5176 ,supplier_response
5177 /* CP-ACK ends */
5178 /* CP-AUTO */
5179 ,suprep_sn_flag, trip,trip_sn_flag, ds_mode
5180 ,sales_channel,fiscal_calendar,internal_repair,external_repair
5181 ,payback_demand_supply
5182 ,currency_conversion,delivery_details
5183 INTO v_prec.purge_ods_flag,v_prec.org_group_flag, v_prec.app_supp_cap_flag,
5184 v_prec.atp_rules_flag, v_prec.bom_flag,
5185 v_prec.bor_flag, v_prec.calendar_flag,
5186 v_prec.demand_class_flag, v_prec.item_subst_flag,v_prec.forecast_flag,
5187 v_prec.item_flag, v_prec.kpi_bis_flag,
5188 v_prec.mds_flag, v_prec.mps_flag,
5189 v_prec.oh_flag, v_prec.parameter_flag,
5190 v_prec.planner_flag, v_prec.project_flag,
5191 v_prec.po_flag, v_prec.reserves_flag,
5192 v_prec.resource_nra_flag, v_prec.saf_stock_flag,
5193 v_prec.sales_order_flag, v_prec.source_hist_flag,
5194 v_prec.sourcing_rule_flag, v_prec.sub_inventory_flag,
5195 v_prec.tp_customer_flag, v_prec.tp_vendor_flag,
5196 v_prec.unit_number_flag, v_prec.uom_flag,
5197 v_prec.user_supply_demand_flag, v_prec.wip_flag, v_prec.user_company_flag,
5198 v_prec.po_receipts_flag,
5199 v_prec.bom_sn_flag, v_prec.bor_sn_flag,
5200 v_prec.item_sn_flag, v_prec.oh_sn_flag,
5201 v_prec.usup_sn_flag, v_prec.udmd_sn_flag,
5202 v_prec.so_sn_flag, v_prec.fcst_sn_flag,
5203 v_prec.wip_sn_flag,
5204 v_prec.supcap_sn_flag, v_prec.po_sn_flag,
5205 v_prec.mds_sn_flag, v_prec.mps_sn_flag,
5206 v_prec.nosnap_flag
5207 /* CP-ACK starts */
5208 ,v_prec.supplier_response_flag
5209 /* CP-ACK ends */
5210 /* CP-AUTO */
5211 ,v_prec.suprep_sn_flag,v_prec.trip_flag,v_prec.trip_sn_flag,v_prec.ds_mode
5212 ,v_prec.sales_channel_flag,v_prec.fiscal_calendar_flag,v_prec.internal_repair_flag,v_prec.external_repair_flag
5213 ,v_prec.payback_demand_supply_flag
5214 ,v_prec.currency_conversion_flag
5215 ,v_prec.delivery_details_flag
5216 FROM msc_coll_parameters
5217 WHERE instance_id = p_instance_id;
5218 END GET_COLL_PARAM;
5219
5220 END MSC_CL_SETUP_ODS_LOAD;