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