[Home] [Help]
PACKAGE BODY: APPS.MSC_CL_OTHER_ODS_LOAD
Source
1 PACKAGE BODY MSC_CL_OTHER_ODS_LOAD AS -- body
2 /* $Header: MSCLOTHB.pls 120.25.12020000.3 2012/09/04 11:59:07 neelredd ship $ */
3
4
5 PROCEDURE LOAD_SAFETY_STOCK IS
6
7 CURSOR c1 IS
8 SELECT
9 msss.ORGANIZATION_ID,
10 t1.INVENTORY_ITEM_ID, -- msss.INVENTORY_ITEM_ID,
11 msss.PERIOD_START_DATE,
12 msss.SAFETY_STOCK_QUANTITY,
13 msss.UPDATED,
14 msss.STATUS,
15 msss.PROJECT_ID,
16 msss.TASK_ID,
17 msss.PLANNING_GROUP,
18 msss.SR_INSTANCE_ID
19 FROM MSC_ITEM_ID_LID t1,
20 MSC_ST_SAFETY_STOCKS msss
21 WHERE t1.SR_INVENTORY_ITEM_ID= msss.inventory_item_id
22 AND t1.sr_instance_id= msss.sr_instance_id
23 AND msss.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
24
25 c_count NUMBER:= 0;
26 lv_ITEM_TYPE_VALUE NUMBER;
27 lv_ITEM_TYPE_ID NUMBER;
28 BEGIN
29
30 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
31
32 --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SAFETY_STOCKS', MSC_CL_COLLECTION.v_instance_id, -1);
33
34 IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
35 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SAFETY_STOCKS', MSC_CL_COLLECTION.v_instance_id, -1);
36 ELSE
37 MSC_CL_COLLECTION.v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
38 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SAFETY_STOCKS', MSC_CL_COLLECTION.v_instance_id, -1,MSC_CL_COLLECTION.v_sub_str);
39 END IF;
40
41 END IF;
42 -- SRP enhancement
43 IF MSC_UTIL.G_COLLECT_SRP_DATA = 'Y' THEN
44 lv_ITEM_TYPE_ID := MSC_UTIL.G_PARTCONDN_ITEMTYPEID;
45 lv_ITEM_TYPE_VALUE := MSC_UTIL.G_PARTCONDN_GOOD;
46 ELSE
47 lv_ITEM_TYPE_ID := NULL;
48 lv_ITEM_TYPE_VALUE := NULL;
49 END IF;
50 c_count:= 0;
51
52 FOR c_rec IN c1 LOOP
53
54 BEGIN
55
56 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
57
58 UPDATE MSC_SAFETY_STOCKS
59 SET
60 SAFETY_STOCK_QUANTITY= c_rec.SAFETY_STOCK_QUANTITY,
61 UPDATED= c_rec.UPDATED,
62 STATUS= c_rec.STATUS,
63 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
64 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
65 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
66 ITEM_TYPE_ID = lv_ITEM_TYPE_ID ,
67 ITEM_TYPE_VALUE = lv_ITEM_TYPE_VALUE
68 WHERE PLAN_ID= -1
69 AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
70 AND INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
71 AND PERIOD_START_DATE= c_rec.PERIOD_START_DATE
72 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
73
74 END IF;
75
76 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
77
78 INSERT INTO MSC_SAFETY_STOCKS
79 ( PLAN_ID,
80 ORGANIZATION_ID,
81 INVENTORY_ITEM_ID,
82 PERIOD_START_DATE,
83 SAFETY_STOCK_QUANTITY,
84 UPDATED,
85 STATUS,
86 PROJECT_ID,
87 TASK_ID,
88 PLANNING_GROUP,
89 SR_INSTANCE_ID,
90 REFRESH_NUMBER,
91 LAST_UPDATE_DATE,
92 LAST_UPDATED_BY,
93 CREATION_DATE,
94 CREATED_BY,
95 ITEM_TYPE_ID,
96 ITEM_TYPE_VALUE)
97 VALUES
98 ( -1,
99 c_rec.ORGANIZATION_ID,
100 c_rec.INVENTORY_ITEM_ID,
101 c_rec.PERIOD_START_DATE,
102 c_rec.SAFETY_STOCK_QUANTITY,
103 c_rec.UPDATED,
104 c_rec.STATUS,
105 c_rec.PROJECT_ID,
106 c_rec.TASK_ID,
107 c_rec.PLANNING_GROUP,
108 c_rec.SR_INSTANCE_ID,
109 MSC_CL_COLLECTION.v_last_collection_id,
110 MSC_CL_COLLECTION.v_current_date,
111 MSC_CL_COLLECTION.v_current_user,
112 MSC_CL_COLLECTION.v_current_date,
113 MSC_CL_COLLECTION.v_current_user,
114 lv_ITEM_TYPE_ID,
115 lv_ITEM_TYPE_VALUE
116 );
117
118 END IF;
119
120 c_count:= c_count+1;
121
122 IF c_count> MSC_CL_COLLECTION.PBS THEN
123 COMMIT;
124 c_count:= 0;
125 END IF;
126
127 EXCEPTION
128 WHEN OTHERS THEN
129
130 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
131
132 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
133 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
134 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SAFETY_STOCK');
135 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SAFETY_STOCKS');
136 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
137
138 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
139 RAISE;
140
141 ELSE
142 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
143
144 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
145 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
146 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SAFETY_STOCK');
147 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SAFETY_STOCKS');
148 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
149
150 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
151 FND_MESSAGE.SET_TOKEN('COLUMN', 'MSC_CL_ITEM_ODS_LOAD.ITEM_NAME');
152 FND_MESSAGE.SET_TOKEN('VALUE', MSC_CL_ITEM_ODS_LOAD.ITEM_NAME( c_rec.INVENTORY_ITEM_ID));
153 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
154
155 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
156 FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
157 FND_MESSAGE.SET_TOKEN('VALUE',
158 MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
159 MSC_CL_COLLECTION.v_instance_id));
160 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
161
162 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
163 FND_MESSAGE.SET_TOKEN('COLUMN', 'PERIOD_START_DATE');
164 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.PERIOD_START_DATE));
165 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
166
167 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
168 FND_MESSAGE.SET_TOKEN('COLUMN', 'PROJECT_ID');
169 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.PROJECT_ID));
170 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
171
172 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
173 FND_MESSAGE.SET_TOKEN('COLUMN', 'TASK_ID');
174 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.TASK_ID));
175 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
176
177 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
178 END IF;
179
180 END;
181
182 END LOOP;
183
184 COMMIT;
185
186 END LOAD_SAFETY_STOCK;
187
188
189
190 --==================================================================
191
192 PROCEDURE LOAD_SOURCING IS
193
194 CURSOR c1 IS
195 SELECT
196 msas.SR_ASSIGNMENT_SET_ID,
197 msas.DESCRIPTION,
198 msas.ASSIGNMENT_SET_NAME,
199 msas.SR_INSTANCE_ID
200 FROM MSC_ST_ASSIGNMENT_SETS msas
201 WHERE msas.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
202
203 CURSOR c2 IS
204 SELECT
205 mssr.ORGANIZATION_ID,
206 mssr.SR_SOURCING_RULE_ID,
207 mssr.SOURCING_RULE_NAME,
208 substrb(mssr.DESCRIPTION,1,80) DESCRIPTION,--added for the NLS bug3463401
209 mssr.STATUS,
210 mssr.SOURCING_RULE_TYPE,
211 mssr.PLANNING_ACTIVE,
212 mssr.SR_INSTANCE_ID
213 FROM MSC_ST_SOURCING_RULES mssr
214 WHERE mssr.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
215
216 CURSOR c3 IS
217 SELECT
218 mssa.SR_ASSIGNMENT_ID,
219 mas.ASSIGNMENT_SET_ID,
220 mssa.ASSIGNMENT_TYPE,
221 msr.SOURCING_RULE_ID,
222 mssa.SOURCING_RULE_TYPE,
223 miil.INVENTORY_ITEM_ID,
224 mtil.TP_ID,
225 mtsil.TP_SITE_ID,
226 mcsil.Category_Set_ID,
227 mssa.ORGANIZATION_ID,
228 mssa.SR_INSTANCE_ID,
229 mssa.CATEGORY_NAME,
230 mssa.SR_ASSIGNMENT_INSTANCE_ID
231 FROM MSC_ITEM_ID_LID miil,
232 MSC_TP_ID_LID mtil,
233 MSC_TP_SITE_ID_LID mtsil,
234 MSC_CATEGORY_SET_ID_LID mcsil,
235 MSC_Assignment_SETS mas,
236 MSC_Sourcing_Rules msr,
237 MSC_ST_SR_ASSIGNMENTS mssa
238 WHERE mas.SR_ASSIGNMENT_SET_ID= mssa.ASSIGNMENT_SET_ID -- Assignment Set
239 AND mas.SR_INSTANCE_ID= mssa.SR_ASSIGNMENT_INSTANCE_ID
240 AND msr.SR_SOURCING_RULE_ID= mssa.SOURCING_RULE_ID -- Sourcing Rule
241 AND msr.SR_INSTANCE_ID= mssa.SR_ASSIGNMENT_INSTANCE_ID
242 AND mtsil.SR_TP_SITE_ID(+)= mssa.SHIP_TO_SITE_ID -- Ship to Site
243 AND mtsil.SR_Instance_ID(+)= mssa.SR_ASSIGNMENT_Instance_ID
244 AND mtsil.Partner_Type(+)=2
245 AND mcsil.SR_Category_Set_ID(+)= mssa.Category_Set_Identifier -- Category Set
246 AND mcsil.SR_Instance_ID(+)= mssa.SR_ASSIGNMENT_Instance_ID
247 AND miil.SR_INVENTORY_ITEM_ID(+)= mssa.INVENTORY_ITEM_ID -- ITEM
248 AND miil.SR_INSTANCE_ID(+)= mssa.SR_ASSIGNMENT_INSTANCE_ID
249 AND mtil.SR_TP_ID(+)= mssa.PARTNER_ID -- TP
250 AND mtil.SR_INSTANCE_ID(+)= mssa.SR_ASSIGNMENT_INSTANCE_ID
251 AND mtil.Partner_Type(+)= 2
252 AND mssa.SR_ASSIGNMENT_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
253 AND
254 (
255 EXISTS
256 (
257 select 1 from msc_item_id_lid miil1
258 where miil1.SR_INVENTORY_ITEM_ID=mssa.INVENTORY_ITEM_ID AND
259 miil1.SR_INSTANCE_ID=mssa.SR_ASSIGNMENT_INSTANCE_ID AND
260 mssa.assignment_type in (3,6)
261 )
262 OR
263 EXISTS
264 (
265 select 1 from MSC_CATEGORY_SET_ID_LID mcsil1
266 where mcsil1.SR_Category_Set_ID= mssa.Category_Set_Identifier AND
267 mcsil1.SR_Instance_ID= mssa.SR_ASSIGNMENT_Instance_ID AND
268 mssa.assignment_type in (2,5)
269 )
270 OR
271 mssa.assignment_type not in (2,3,5,6)
272 );
273
274 CURSOR c4 IS
275 SELECT
276 mssro.SR_RECEIPT_ID,
277 mssro.SR_SR_RECEIPT_ORG,
278 mssro.RECEIPT_ORG_INSTANCE_ID,
279 msr.SOURCING_RULE_ID,
280 mssro.RECEIPT_PARTNER_ID,
281 mssro.RECEIPT_PARTNER_SITE_ID,
282 mssro.EFFECTIVE_DATE,
283 mssro.DISABLE_DATE,
284 mssro.SR_INSTANCE_ID
285 FROM MSC_Sourcing_Rules msr,
286 MSC_ST_SR_RECEIPT_ORG mssro
287 WHERE msr.SR_SOURCING_RULE_ID= mssro.SOURCING_RULE_ID
288 AND msr.SR_INSTANCE_ID= mssro.SR_INSTANCE_ID
289 AND mssro.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
290
291
292 CURSOR c5 IS
293 SELECT
294 mssso.SR_SR_SOURCE_ID,
295 msro.SR_RECEIPT_ID, -- mssso.SR_RECEIPT_ID,
296 mssso.Source_Organization_ID,
297 mssso.SOURCE_ORG_INSTANCE_ID,
298 mtil.TP_ID, -- mssso.SOURCE_PARTNER_ID,
299 mtsil.TP_SITE_ID, -- mssso.SOURCE_PARTNER_SITE_ID,
300 mssso.ALLOCATION_PERCENT,
301 mssso.RANK,
302 mssso.SR_INSTANCE_ID,
303 mssso.SHIP_METHOD,
304 mssso.SOURCE_TYPE
305 FROM MSC_TP_ID_LID mtil,
306 MSC_TP_SITE_ID_LID mtsil,
307 MSC_SR_Receipt_Org msro,
308 MSC_ST_SR_SOURCE_ORG mssso
309 WHERE msro.SR_SR_RECEIPT_ID= mssso.SR_RECEIPT_ID
310 AND msro.SR_Instance_ID= mssso.SR_Instance_ID
311 AND mtil.SR_TP_ID(+)= mssso.SOURCE_PARTNER_ID
312 AND mtil.SR_INSTANCE_ID(+)= mssso.SR_INSTANCE_ID
313 AND mtil.Partner_Type(+)= 1
314 AND mtsil.SR_TP_SITE_ID(+)= mssso.SOURCE_PARTNER_SITE_ID
315 AND mtsil.SR_INSTANCE_ID(+)= mssso.SR_Instance_ID
316 AND mtsil.Partner_Type(+)= 1
317 AND mssso.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
318
319 CURSOR c6 IS
320 SELECT
321 NVL(msism.FROM_ORGANIZATION_ID,-1) FROM_ORGANIZATION_ID,
322 NVL(msism.TO_ORGANIZATION_ID,-1) TO_ORGANIZATION_ID,
323 msism.SHIP_METHOD,
324 msism.SHIP_METHOD_TEXT,
325 msism.TIME_UOM_CODE,
326 NVL(msism.DEFAULT_FLAG,2) DEFAULT_FLAG,
327 NVL(msism.FROM_LOCATION_ID,-1) FROM_LOCATION_ID,
328 NVL(msism.TO_LOCATION_ID,-1) TO_LOCATION_ID,
329 msism.WEIGHT_CAPACITY,
330 msism.WEIGHT_UOM,
331 msism.VOLUME_CAPACITY,
332 msism.VOLUME_UOM,
333 msism.COST_PER_WEIGHT_UNIT,
334 msism.COST_PER_VOLUME_UNIT,
335 msism.INTRANSIT_TIME,
336 msism.TO_REGION_ID,
337 msism.FROM_REGION_ID,
338 msism.CURRENCY,
339 msism.TRANSPORT_CAP_OVER_UTIL_COST,
340 msism.SR_INSTANCE_ID,
341 msism.SR_INSTANCE_ID2, -- to_org
342 msism.SHIPMENT_WEIGHT,
343 msism.SHIPMENT_VOLUME,
344 msism.SHIPMENT_WEIGHT_UOM,
345 msism.SHIPMENT_VOLUME_UOM,
346 msism.LEADTIME_VARIABILITY
347 FROM MSC_ST_INTERORG_SHIP_METHODS msism
348 WHERE msism.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
349 /* Changed Refresh_id to Refresh_Number */
350 CURSOR c7 IS
351 SELECT msr.REGION_ID,
352 msr.REGION_TYPE,
353 msr.PARENT_REGION_ID,
354 msr.COUNTRY_CODE,
355 msr.COUNTRY_REGION_CODE,
356 msr.STATE_CODE,
357 msr.CITY_CODE,
358 msr.PORT_FLAG,
359 msr.AIRPORT_FLAG,
360 msr.ROAD_TERMINAL_FLAG,
361 msr.RAIL_TERMINAL_FLAG,
362 msr.LONGITUDE,
363 msr.LATITUDE,
364 msr.TIMEZONE,
365 msr.CREATED_BY,
366 msr.CREATION_DATE,
367 msr.LAST_UPDATED_BY,
368 msr.LAST_UPDATE_DATE,
369 msr.LAST_UPDATE_LOGIN,
370 msr.CONTINENT,
371 msr.COUNTRY,
372 msr.COUNTRY_REGION,
373 msr.STATE,
374 msr.CITY,
375 msr.ZONE,
376 msr.ZONE_LEVEL,
377 msr.POSTAL_CODE_FROM,
378 msr.POSTAL_CODE_TO,
379 msr.ALTERNATE_NAME,
380 msr.COUNTY,
381 msr.SR_INSTANCE_ID,
382 msr.REFRESH_NUMBER,
383 msr.ZONE_USAGE
384 FROM MSC_ST_REGIONS msr
385 WHERE msr.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
386
387 /* Changed Refresh_id to Refresh_number */
388 CURSOR c8 IS
389 SELECT mszr.ZONE_REGION_ID,
390 mszr.REGION_ID,
391 mszr.PARENT_REGION_ID,
392 mszr.PARTY_ID,
393 mszr.CREATED_BY,
394 mszr.CREATION_DATE,
395 mszr.LAST_UPDATED_BY,
396 mszr.LAST_UPDATE_DATE,
397 mszr.LAST_UPDATE_LOGIN,
398 mszr.SR_INSTANCE_ID,
399 mszr.REFRESH_NUMBER
400 FROM MSC_ST_ZONE_REGIONS mszr
401 WHERE mszr.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
402 /* Changed Refresh_id to Refresh_number */
403 CURSOR c9 IS
404 SELECT
405 msrl.REGION_ID,
406 msrl.LOCATION_ID,
407 msrl.REGION_TYPE,
408 msrl.PARENT_REGION_FLAG,
409 msrl.LOCATION_SOURCE,
410 msrl.EXCEPTION_TYPE,
411 msrl.CREATED_BY,
412 msrl.CREATION_DATE,
413 msrl.LAST_UPDATED_BY,
414 msrl.LAST_UPDATE_DATE,
415 msrl.LAST_UPDATE_LOGIN,
416 msrl.SR_INSTANCE_ID,
417 msrl.REFRESH_NUMBER
418 FROM MSC_ST_REGION_LOCATIONS msrl
419 WHERE msrl.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
420
421 CURSOR c10 IS
422 SELECT DISTINCT
423 msrs.REGION_ID,
424 mtsil.TP_SITE_ID,
425 msrs.REGION_TYPE,
426 msrs.ZONE_LEVEL,
427 msrs.SR_INSTANCE_ID,
428 msrs.REFRESH_ID
429 FROM MSC_ST_REGION_SITES msrs,
430 MSC_TP_SITE_ID_LID mtsil
431 WHERE msrs.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
432 AND mtsil.SR_TP_SITE_ID = msrs.VENDOR_SITE_ID
433 AND mtsil.SR_Instance_ID = msrs.SR_INSTANCE_ID
434 AND mtsil.Partner_Type = 1;
435
436 CURSOR c11 IS
437 SELECT
438 mscs.SHIP_METHOD_CODE,
439 mtil.TP_ID,
440 mscs.SERVICE_LEVEL,
441 mscs.MODE_OF_TRANSPORT,
442 mscs.SR_INSTANCE_ID,
443 mscs.REFRESH_ID
444 FROM MSC_ST_CARRIER_SERVICES mscs,
445 MSC_TP_ID_LID mtil
446 WHERE mscs.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
447 AND mtil.SR_TP_ID = mscs.CARRIER_ID
448 AND mtil.SR_Instance_ID = mscs.SR_INSTANCE_ID
449 AND mtil.Partner_Type = 4;
450
451 c_count NUMBER:= 0;
452
453 TYPE CharTblTyp IS TABLE OF VARCHAR2(250);
454 TYPE NumTblTyp IS TABLE OF NUMBER;
455 TYPE dateTblTyp IS TABLE OF DATE;
456 lb_SR_ASSIGNMENT_ID NumTblTyp;
457 lb_ASSIGNMENT_SET_ID NumTblTyp;
458 lb_ASSIGNMENT_TYPE NumTblTyp;
459 lb_SOURCING_RULE_ID NumTblTyp;
460 lb_SOURCING_RULE_TYPE NumTblTyp;
461 lb_INVENTORY_ITEM_ID NumTblTyp;
462 lb_TP_ID NumTblTyp;
463 lb_TP_SITE_ID NumTblTyp;
464 lb_Category_Set_ID NumTblTyp;
465 lb_ORGANIZATION_ID NumTblTyp;
466 lb_SR_INSTANCE_ID NumTblTyp;
467 lb_category_name CharTblTyp;
468 lb_SR_ASSIGNMENT_INSTANCE_ID NumTblTyp;
469 lb_FetchComplete Boolean;
470 ln_rows_to_fetch Number := nvl(TO_NUMBER( FND_PROFILE.VALUE('MRP_PURGE_BATCH_SIZE')),75000);
471
472
473 lv_control_flag NUMBER;
474 lv_msc_tp_coll_window NUMBER;
475 lv_sql_stmt VARCHAR2(4000);
476
477 i NUMBER := -1; -- added for 6643314
478 lv_crt_ind_status NUMBER;
479 BEGIN
480
481 --/* it's removed due to bug 1219661
482 IF ((MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh)
483 AND MSC_CL_COLLECTION.v_sourcing_flag=MSC_UTIL.SYS_YES ) THEN
484
485 UPDATE MSC_ASSIGNMENT_SETS
486 SET DELETED_FLAG= MSC_UTIL.SYS_YES,
487 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
488 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
489 WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
490 AND COLLECTED_FLAG= MSC_UTIL.SYS_YES;
491
492 END IF;
493
494 COMMIT;
495 --*/
496
497 c_count:= 0;
498
499 FOR c_rec IN c1 LOOP
500
501 BEGIN
502
503 UPDATE MSC_ASSIGNMENT_SETS mas
504 SET mas.ASSIGNMENT_SET_NAME=c_rec.ASSIGNMENT_SET_NAME,
505 mas.Description= c_rec.Description,
506 mas.Deleted_Flag= MSC_UTIL.SYS_NO,
507 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
508 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
509 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
510 WHERE mas.SR_Assignment_Set_Id= c_rec.SR_Assignment_Set_Id
511 AND mas.SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
512 AND COLLECTED_FLAG= MSC_UTIL.SYS_YES;
513
514 IF SQL%NOTFOUND THEN
515
516 INSERT INTO MSC_ASSIGNMENT_SETS
517 ( ASSIGNMENT_SET_ID,
518 SR_ASSIGNMENT_SET_ID,
519 DESCRIPTION,
520 ASSIGNMENT_SET_NAME,
521 COLLECTED_FLAG,
522 SR_INSTANCE_ID,
523 DELETED_FLAG,
524 REFRESH_NUMBER,
525 LAST_UPDATE_DATE,
526 LAST_UPDATED_BY,
527 CREATION_DATE,
528 CREATED_BY)
529 VALUES
530 ( MSC_ASSIGNMENT_SETS_S.NEXTVAL,
531 c_rec.SR_ASSIGNMENT_SET_ID,
532 c_rec.DESCRIPTION,
533 c_rec.ASSIGNMENT_SET_NAME,
534 MSC_UTIL.SYS_YES,
535 c_rec.SR_INSTANCE_ID,
536 MSC_UTIL.SYS_NO,
537 MSC_CL_COLLECTION.v_last_collection_id,
538 MSC_CL_COLLECTION.v_current_date,
539 MSC_CL_COLLECTION.v_current_user,
540 MSC_CL_COLLECTION.v_current_date,
541 MSC_CL_COLLECTION.v_current_user);
542
543 END IF;
544
545 c_count:= c_count+1;
546
547 IF c_count> MSC_CL_COLLECTION.PBS THEN
548 COMMIT;
549 c_count:= 0;
550 END IF;
551
552 EXCEPTION
553 WHEN OTHERS THEN
554
555 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
556
557 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
558 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
559 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SOURCING');
560 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ASSIGNMENT_SETS');
561 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
562
563 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
564 RAISE;
565
566 ELSE
567
568 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
569
570 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
571 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
572 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SOURCING');
573 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ASSIGNMENT_SETS');
574 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
575
576 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
577 FND_MESSAGE.SET_TOKEN('COLUMN', 'ASSIGNMENT_SET_NAME');
578 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.ASSIGNMENT_SET_NAME);
579 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
580
581 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
582 FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_ASSIGNMENT_SET_ID');
583 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SR_ASSIGNMENT_SET_ID));
584 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
585
586 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
587 END IF;
588
589 END;
590
591 END LOOP;
592
593 COMMIT;
594
595 --/* it's removed due to bug 1219661
596 DELETE MSC_ASSIGNMENT_SETS
597 WHERE DELETED_FLAG= MSC_UTIL.SYS_YES
598 AND SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
599 AND COLLECTED_FLAG= MSC_UTIL.SYS_YES;
600
601 COMMIT;
602 --*/
603
604 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
605
606 msc_analyse_tables_pk.analyse_table( 'MSC_ASSIGNMENT_SETS');
607
608 END IF;
609
610 --/* it's removed due to bug 1219661
611 IF ((MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh)
612 AND MSC_CL_COLLECTION.v_sourcing_flag =MSC_UTIL.SYS_YES) THEN
613
614 UPDATE MSC_SOURCING_RULES
615 SET DELETED_FLAG= MSC_UTIL.SYS_YES,
616 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
617 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
618 WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
619 AND COLLECTED_FLAG= MSC_UTIL.SYS_YES;
620
621 END IF;
622
623 COMMIT;
624 --*/
625
626 c_count:= 0;
627
628 FOR c_rec IN c2 LOOP
629
630 BEGIN
631
632 UPDATE MSC_SOURCING_RULES msr
633 SET msr.Description= c_rec.Description,
634 msr.Status= c_rec.Status,
635 msr.Sourcing_Rule_Type= c_rec.Sourcing_Rule_Type,
636 msr.sourcing_rule_name= c_rec.sourcing_rule_name,
637 msr.Planning_Active= c_rec.Planning_Active,
638 msr.Deleted_Flag= MSC_UTIL.SYS_NO,
639 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
640 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
641 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
642 CREATION_DATE= MSC_CL_COLLECTION.v_current_date,
643 CREATED_BY= MSC_CL_COLLECTION.v_current_user
644 WHERE msr.SR_Sourcing_Rule_ID= c_rec.SR_Sourcing_Rule_ID
645 AND msr.SR_Instance_ID= c_rec.SR_Instance_ID
646 AND msr.COLLECTED_FLAG= MSC_UTIL.SYS_YES;
647
648 IF SQL%NOTFOUND THEN
649
650 INSERT INTO MSC_SOURCING_RULES
651 ( ORGANIZATION_ID,
652 SOURCING_RULE_ID,
653 SR_SOURCING_RULE_ID,
654 SOURCING_RULE_NAME,
655 DESCRIPTION,
656 STATUS,
657 SOURCING_RULE_TYPE,
658 PLANNING_ACTIVE,
659 COLLECTED_FLAG,
660 SR_INSTANCE_ID,
661 DELETED_FLAG,
662 REFRESH_NUMBER,
663 LAST_UPDATE_DATE,
664 LAST_UPDATED_BY,
665 CREATION_DATE,
666 CREATED_BY)
667 VALUES
668 ( c_rec.ORGANIZATION_ID,
669 MSC_SOURCING_RULES_S.NEXTVAL,
670 c_rec.SR_SOURCING_RULE_ID,
671 c_rec.SOURCING_RULE_NAME,
672 c_rec.DESCRIPTION,
673 c_rec.STATUS,
674 c_rec.SOURCING_RULE_TYPE,
675 c_rec.PLANNING_ACTIVE,
676 MSC_UTIL.SYS_YES,
677 c_rec.SR_INSTANCE_ID,
678 MSC_UTIL.SYS_NO,
679 MSC_CL_COLLECTION.v_last_collection_id,
680 MSC_CL_COLLECTION.v_current_date,
681 MSC_CL_COLLECTION.v_current_user,
682 MSC_CL_COLLECTION.v_current_date,
683 MSC_CL_COLLECTION.v_current_user);
684
685 END IF;
686
687 c_count:= c_count+1;
688
689 IF c_count> MSC_CL_COLLECTION.PBS THEN
690 COMMIT;
691 c_count:= 0;
692 END IF;
693
694 EXCEPTION
695 WHEN OTHERS THEN
696 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
697
698 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
699 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
700 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SOURCING');
701 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SOURCING_RULES');
702 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
703
704 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
705 RAISE;
706
707 ELSE
708 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
709
710 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
711 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
712 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SOURCING');
713 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SOURCING_RULES');
714 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
715
716 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
717 FND_MESSAGE.SET_TOKEN('COLUMN', 'SOURCING_RULE_NAME');
718 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SOURCING_RULE_NAME);
719 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
720
721 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
722 END IF;
723
724 END;
725
726 END LOOP;
727
728 COMMIT;
729
730 --/* it's removed due to bug 1219661
731 DELETE MSC_SOURCING_RULES
732 WHERE DELETED_FLAG= MSC_UTIL.SYS_YES
733 AND SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
734 AND COLLECTED_FLAG= MSC_UTIL.SYS_YES;
735
736 COMMIT;
737 --*/
738
739 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
740
741 msc_analyse_tables_pk.analyse_table( 'MSC_SOURCING_RULES');
742
743 END IF;
744
745 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
746
747 UPDATE MSC_SR_ASSIGNMENTS
748 SET DELETED_FLAG= MSC_UTIL.SYS_YES,
749 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
750 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
751 WHERE SR_ASSIGNMENT_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
752 AND COLLECTED_FLAG= MSC_UTIL.SYS_YES;
753
754 END IF;
755
756 COMMIT;
757
758
759 OPEN c3;
760
761 IF (c3%ISOPEN) THEN
762 LOOP
763
764 --
765 -- Retrieve the next set of rows if we are currently not in the
766 -- middle of processing a fetched set or rows.
767 --
768 IF (lb_FetchComplete) THEN
769 EXIT;
770 END IF;
771
772 -- Fetch the next set of rows
773 FETCH c3 BULK COLLECT INTO lb_SR_ASSIGNMENT_ID,
774 lb_ASSIGNMENT_SET_ID,
775 lb_ASSIGNMENT_TYPE,
776 lb_SOURCING_RULE_ID,
777 lb_SOURCING_RULE_TYPE,
778 lb_INVENTORY_ITEM_ID,
779 lb_TP_ID,
780 lb_TP_SITE_ID,
781 lb_Category_Set_ID,
782 lb_ORGANIZATION_ID,
783 lb_SR_INSTANCE_ID,
784 lb_category_name,
785 lb_SR_ASSIGNMENT_INSTANCE_ID
786 LIMIT ln_rows_to_fetch;
787
788 -- Since we are only fetching records if either (1) this is the first
789 -- fetch or (2) the previous fetch did not retrieve all of the
790 -- records, then at least one row should always be fetched. But
791 -- checking just to make sure.
792 EXIT WHEN lb_SR_ASSIGNMENT_ID.count = 0;
793
794 -- Check if all of the rows have been fetched. If so, indicate that
795 -- the fetch is complete so that another fetch is not made.
796 -- Additional check is introduced for the following reasons
797 -- In 9i, the table of records gets modified but in 8.1.6 the table of records is
798 -- unchanged after the fetch(bug#2995144)
799
800 IF (c3%NOTFOUND) THEN
801 lb_FetchComplete := TRUE;
802 END IF;
803
804 BEGIN
805
806 FORALL j IN lb_SR_ASSIGNMENT_ID.FIRST..lb_SR_ASSIGNMENT_ID.LAST
807
808 UPDATE MSC_SR_ASSIGNMENTS msa
809 SET msa.ASSIGNMENT_TYPE = lb_ASSIGNMENT_TYPE(j),
810 msa.SOURCING_RULE_ID = lb_SOURCING_RULE_ID(j),
811 msa.SOURCING_RULE_TYPE = lb_SOURCING_RULE_TYPE(j),
812 msa.INVENTORY_ITEM_ID = lb_INVENTORY_ITEM_ID(j),
813 msa.PARTNER_ID = lb_TP_ID(j),
814 msa.SHIP_TO_SITE_ID = lb_TP_SITE_ID(j),
815 msa.CATEGORY_SET_ID = lb_Category_Set_ID(j),
816 msa.ORGANIZATION_ID = lb_ORGANIZATION_ID(j),
817 msa.SR_INSTANCE_ID = lb_SR_INSTANCE_ID(j),
818 msa.CATEGORY_NAME = lb_category_name(j),
819 msa.Deleted_Flag= MSC_UTIL.SYS_NO,
820 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
821 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
822 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
823 CREATION_DATE= MSC_CL_COLLECTION.v_current_date,
824 CREATED_BY= MSC_CL_COLLECTION.v_current_user
825 WHERE msa.SR_Assignment_ID= lb_SR_ASSIGNMENT_ID(j)
826 AND msa.SR_Assignment_Instance_ID= lb_SR_ASSIGNMENT_INSTANCE_ID(j)
827 AND msa.COLLECTED_FLAG= MSC_UTIL.SYS_YES;
828
829 EXCEPTION
830 WHEN OTHERS THEN
831
832 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
833
834 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
835 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
836 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SOURCING');
837 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SR_ASSIGNMENTS');
838 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
839
840 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
841 RAISE;
842
843 ELSE
844
845 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
846
847 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
848 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
849 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SOURCING');
850 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SR_ASSIGNMENTS');
851 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
852
853 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
854 END IF;
855
856 END;
857 commit;
858 END LOOP;
859 END IF;
860 CLOSE c3;
861
862 BEGIN
863
864
865 INSERT /*+ APPEND */
866 INTO MSC_SR_ASSIGNMENTS
867 ( ASSIGNMENT_ID,
868 SR_ASSIGNMENT_ID,
869 ASSIGNMENT_SET_ID,
870 ASSIGNMENT_TYPE,
871 SOURCING_RULE_ID,
872 SOURCING_RULE_TYPE,
873 INVENTORY_ITEM_ID,
874 PARTNER_ID,
875 SHIP_TO_SITE_ID,
876 CATEGORY_SET_ID,
877 ORGANIZATION_ID,
878 SR_INSTANCE_ID,
879 CATEGORY_NAME,
880 COLLECTED_FLAG,
881 SR_ASSIGNMENT_INSTANCE_ID,
882 DELETED_FLAG,
883 REFRESH_NUMBER,
884 LAST_UPDATE_DATE,
885 LAST_UPDATED_BY,
886 CREATION_DATE,
887 CREATED_BY)
888 SELECT
889 MSC_SR_ASSIGNMENTS_S.NEXTVAL,
890 mssa.SR_ASSIGNMENT_ID,
891 mas.ASSIGNMENT_SET_ID,
892 mssa.ASSIGNMENT_TYPE,
893 msr.SOURCING_RULE_ID,
894 mssa.SOURCING_RULE_TYPE,
895 miil.INVENTORY_ITEM_ID,
896 mtil.TP_ID,
897 mtsil.TP_SITE_ID,
898 mcsil.Category_Set_ID,
899 mssa.ORGANIZATION_ID,
900 mssa.SR_INSTANCE_ID,
901 mssa.CATEGORY_NAME,
902 MSC_UTIL.SYS_YES,
903 mssa.SR_ASSIGNMENT_INSTANCE_ID,
904 MSC_UTIL.SYS_NO,
905 MSC_CL_COLLECTION.v_last_collection_id,
906 MSC_CL_COLLECTION.v_current_date,
907 MSC_CL_COLLECTION.v_current_user,
908 MSC_CL_COLLECTION.v_current_date,
909 MSC_CL_COLLECTION.v_current_user
910 FROM MSC_ITEM_ID_LID miil,
911 MSC_TP_ID_LID mtil,
912 MSC_TP_SITE_ID_LID mtsil,
913 MSC_CATEGORY_SET_ID_LID mcsil,
914 MSC_Assignment_SETS mas,
915 MSC_Sourcing_Rules msr,
916 MSC_ST_SR_ASSIGNMENTS mssa
917 WHERE mas.SR_ASSIGNMENT_SET_ID= mssa.ASSIGNMENT_SET_ID -- Assignment Set
918 AND mas.SR_INSTANCE_ID= mssa.SR_ASSIGNMENT_INSTANCE_ID
919 AND msr.SR_SOURCING_RULE_ID= mssa.SOURCING_RULE_ID -- Sourcing Rule
920 AND msr.SR_INSTANCE_ID= mssa.SR_ASSIGNMENT_INSTANCE_ID
921 AND mtsil.SR_TP_SITE_ID(+)= mssa.SHIP_TO_SITE_ID -- Ship to Site
922 AND mtsil.SR_Instance_ID(+)= mssa.SR_ASSIGNMENT_Instance_ID
923 AND mtsil.Partner_Type(+)=2
924 AND mcsil.SR_Category_Set_ID(+)= mssa.Category_Set_Identifier -- Category Set
925 AND mcsil.SR_Instance_ID(+)= mssa.SR_ASSIGNMENT_Instance_ID
926 AND miil.SR_INVENTORY_ITEM_ID(+)= mssa.INVENTORY_ITEM_ID -- ITEM
927 AND miil.SR_INSTANCE_ID(+)= mssa.SR_ASSIGNMENT_INSTANCE_ID
928 AND mtil.SR_TP_ID(+)= mssa.PARTNER_ID -- TP
929 AND mtil.SR_INSTANCE_ID(+)= mssa.SR_ASSIGNMENT_INSTANCE_ID
930 AND mtil.Partner_Type(+)= 2
931 AND mssa.SR_ASSIGNMENT_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
932 AND
933 (
934 EXISTS
935 (
936 select 1 from msc_item_id_lid miil1
937 where miil1.SR_INVENTORY_ITEM_ID=mssa.INVENTORY_ITEM_ID AND
938 miil1.SR_INSTANCE_ID=mssa.SR_ASSIGNMENT_INSTANCE_ID AND
939 mssa.assignment_type in (3,6)
940 )
941 OR
942 EXISTS
943 (
944 select 1 from MSC_CATEGORY_SET_ID_LID mcsil1
945 where mcsil1.SR_Category_Set_ID= mssa.Category_Set_Identifier AND
946 mcsil1.SR_Instance_ID= mssa.SR_ASSIGNMENT_Instance_ID AND
947 mssa.assignment_type in (2,5)
948 )
949 OR
950 mssa.assignment_type not in (2,3,5,6)
951 )
952 AND not exists (select 1
953 from MSC_SR_ASSIGNMENTS msa2
954 where msa2.SR_Assignment_ID = mssa.SR_Assignment_ID
955 AND msa2.SR_Assignment_Instance_ID = mssa.SR_Assignment_Instance_ID
956 AND msa2.collected_flag = MSC_UTIL.SYS_YES);
957
958 EXCEPTION
959 WHEN OTHERS THEN
960
961 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
962
963 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
964 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
965 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SOURCING');
966 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SR_ASSIGNMENTS');
967 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
968
969 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
970 RAISE;
971
972 ELSE
973
974 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
975
976 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
977 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
978 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SOURCING');
979 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SR_ASSIGNMENTS');
980 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
981
982 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
983 END IF;
984
985 END;
986
987
988 COMMIT;
989
990 DELETE MSC_SR_ASSIGNMENTS
991 WHERE DELETED_FLAG= MSC_UTIL.SYS_YES
992 AND SR_ASSIGNMENT_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
993 AND COLLECTED_FLAG= MSC_UTIL.SYS_YES;
994
995 COMMIT;
996
997 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
998
999 msc_analyse_tables_pk.analyse_table( 'MSC_SR_ASSIGNMENTS');
1000
1001 END IF;
1002
1003 --/* it's removed due to bug 1219661
1004 IF ((MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh)
1005 AND MSC_CL_COLLECTION.v_sourcing_flag=MSC_UTIL.SYS_YES ) THEN
1006
1007 UPDATE MSC_SR_RECEIPT_ORG
1008 SET DELETED_FLAG= MSC_UTIL.SYS_YES,
1009 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1010 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1011 WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1012 AND COLLECTED_FLAG= MSC_UTIL.SYS_YES;
1013
1014 END IF;
1015
1016 COMMIT;
1017 --*/
1018
1019 c_count:= 0;
1020
1021 FOR c_rec IN c4 LOOP
1022
1023 BEGIN
1024
1025 UPDATE MSC_SR_RECEIPT_ORG msro
1026 SET msro.SR_RECEIPT_ORG= c_rec.SR_SR_Receipt_Org,
1027 msro.RECEIPT_ORG_INSTANCE_ID= c_rec.RECEIPT_ORG_INSTANCE_ID,
1028 msro.SOURCING_RULE_ID= c_rec.Sourcing_Rule_ID,
1029 msro.RECEIPT_PARTNER_ID= c_rec.Receipt_Partner_ID,
1030 msro.RECEIPT_PARTNER_SITE_ID= c_rec.Receipt_Partner_Site_ID,
1031 msro.EFFECTIVE_DATE= c_rec.Effective_Date,
1032 msro.DISABLE_DATE= c_rec.Disable_Date,
1033 msro.Deleted_Flag= MSC_UTIL.SYS_NO,
1034 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1035 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1036 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
1037 CREATION_DATE= MSC_CL_COLLECTION.v_current_date,
1038 CREATED_BY= MSC_CL_COLLECTION.v_current_user
1039 WHERE msro.SR_SR_Receipt_ID= c_rec.SR_Receipt_ID
1040 AND msro.SR_Instance_ID= c_rec.SR_Instance_ID
1041 AND msro.COLLECTED_FLAG= MSC_UTIL.SYS_YES;
1042
1043 IF SQL%NOTFOUND THEN
1044
1045 INSERT INTO MSC_SR_RECEIPT_ORG
1046 ( SR_RECEIPT_ID,
1047 SR_SR_RECEIPT_ID,
1048 SR_RECEIPT_ORG,
1049 RECEIPT_ORG_INSTANCE_ID,
1050 SOURCING_RULE_ID,
1051 RECEIPT_PARTNER_ID,
1052 RECEIPT_PARTNER_SITE_ID,
1053 EFFECTIVE_DATE,
1054 DISABLE_DATE,
1055 COLLECTED_FLAG,
1056 SR_INSTANCE_ID,
1057 DELETED_FLAG,
1058 REFRESH_NUMBER,
1059 LAST_UPDATE_DATE,
1060 LAST_UPDATED_BY,
1061 CREATION_DATE,
1062 CREATED_BY)
1063 VALUES
1064 ( MSC_SR_RECEIPT_ORG_S.NEXTVAL,
1065 c_rec.SR_RECEIPT_ID,
1066 c_rec.SR_SR_RECEIPT_ORG,
1067 c_rec.RECEIPT_ORG_INSTANCE_ID,
1068 c_rec.SOURCING_RULE_ID,
1069 c_rec.RECEIPT_PARTNER_ID,
1070 c_rec.RECEIPT_PARTNER_SITE_ID,
1071 c_rec.EFFECTIVE_DATE,
1072 c_rec.DISABLE_DATE,
1073 MSC_UTIL.SYS_YES,
1074 c_rec.SR_INSTANCE_ID,
1075 MSC_UTIL.SYS_NO,
1076 MSC_CL_COLLECTION.v_last_collection_id,
1077 MSC_CL_COLLECTION.v_current_date,
1078 MSC_CL_COLLECTION.v_current_user,
1079 MSC_CL_COLLECTION.v_current_date,
1080 MSC_CL_COLLECTION.v_current_user);
1081
1082 END IF;
1083
1084 c_count:= c_count+1;
1085
1086 IF c_count> MSC_CL_COLLECTION.PBS THEN
1087 COMMIT;
1088 c_count:= 0;
1089 END IF;
1090
1091 EXCEPTION
1092
1093 WHEN OTHERS THEN
1094
1095 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
1096
1097 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1098 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1099 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SOURCING');
1100 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SR_RECEIPT_ORG');
1101 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1102
1103 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1104 RAISE;
1105
1106 ELSE
1107 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1108
1109 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1110 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1111 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SOURCING');
1112 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SR_RECEIPT_ORG');
1113 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1114
1115 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1116 FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_RECEIPT_ID');
1117 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SR_RECEIPT_ID));
1118 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1119
1120 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1121 END IF;
1122
1123 END;
1124
1125 END LOOP; -- c4
1126
1127 COMMIT;
1128
1129 --/* it's removed due to bug 1219661
1130 DELETE MSC_SR_RECEIPT_ORG
1131 WHERE DELETED_FLAG= MSC_UTIL.SYS_YES
1132 AND SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1133 AND COLLECTED_FLAG= MSC_UTIL.SYS_YES;
1134
1135 COMMIT;
1136 --*/
1137
1138 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
1139
1140 msc_analyse_tables_pk.analyse_table( 'MSC_SR_RECEIPT_ORG');
1141
1142 END IF;
1143
1144 --/* it's removed due to bug 1219661
1145 IF ((MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh)
1146 AND MSC_CL_COLLECTION.v_sourcing_flag=MSC_UTIL.SYS_YES ) THEN
1147
1148 UPDATE MSC_SR_SOURCE_ORG
1149 SET DELETED_FLAG= MSC_UTIL.SYS_YES,
1150 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1151 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1152 WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1153 AND COLLECTED_FLAG= MSC_UTIL.SYS_YES;
1154
1155 END IF;
1156
1157 COMMIT;
1158 --*/
1159
1160 c_count:= 0;
1161
1162 FOR c_rec IN c5 LOOP
1163
1164 BEGIN
1165
1166 UPDATE MSC_SR_SOURCE_ORG msso
1167 SET msso.SR_RECEIPT_ID= c_rec.SR_Receipt_ID,
1168 msso.SOURCE_ORGANIZATION_ID= c_rec.Source_Organization_ID,
1169 msso.SOURCE_ORG_INSTANCE_ID= c_rec.SOURCE_ORG_INSTANCE_ID,
1170 msso.SOURCE_PARTNER_ID= c_rec.TP_ID,
1171 msso.SOURCE_PARTNER_SITE_ID= c_rec.TP_Site_ID,
1172 msso.ALLOCATION_PERCENT= c_rec.Allocation_percent,
1173 msso.RANK= c_rec.Rank,
1174 msso.SHIP_METHOD= c_rec.Ship_Method,
1175 msso.SOURCE_TYPE= c_rec.Source_Type,
1176 msso.Deleted_Flag= MSC_UTIL.SYS_NO,
1177 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1178 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1179 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1180 WHERE msso.SR_SR_SOURCE_ID= c_rec.SR_SR_SOURCE_ID
1181 AND msso.SR_Instance_ID= c_rec.SR_Instance_ID
1182 AND msso.COLLECTED_FLAG= MSC_UTIL.SYS_YES;
1183
1184 IF SQL%NOTFOUND THEN
1185
1186 INSERT INTO MSC_SR_SOURCE_ORG
1187 ( SR_SOURCE_ID,
1188 SR_SR_SOURCE_ID,
1189 SR_RECEIPT_ID,
1190 SOURCE_ORGANIZATION_ID,
1191 SOURCE_ORG_INSTANCE_ID,
1192 SOURCE_PARTNER_ID,
1193 SOURCE_PARTNER_SITE_ID,
1194 ALLOCATION_PERCENT,
1195 RANK,
1196 COLLECTED_FLAG,
1197 SR_INSTANCE_ID,
1198 DELETED_FLAG,
1199 SHIP_METHOD,
1200 SOURCE_TYPE,
1201 REFRESH_NUMBER,
1202 LAST_UPDATE_DATE,
1203 LAST_UPDATED_BY,
1204 CREATION_DATE,
1205 CREATED_BY)
1206 VALUES
1207 ( MSC_SR_SOURCE_ORG_S.NEXTVAL,
1208 c_rec.SR_SR_SOURCE_ID,
1209 c_rec.SR_RECEIPT_ID,
1210 c_rec.Source_Organization_ID,
1211 c_rec.SOURCE_ORG_INSTANCE_ID,
1212 c_rec.TP_ID,
1213 c_rec.TP_SITE_ID,
1214 c_rec.ALLOCATION_PERCENT,
1215 c_rec.RANK,
1216 MSC_UTIL.SYS_YES,
1217 c_rec.SR_INSTANCE_ID,
1218 MSC_UTIL.SYS_NO,
1219 c_rec.SHIP_METHOD,
1220 c_rec.SOURCE_TYPE,
1221 MSC_CL_COLLECTION.v_last_collection_id,
1222 MSC_CL_COLLECTION.v_current_date,
1223 MSC_CL_COLLECTION.v_current_user,
1224 MSC_CL_COLLECTION.v_current_date,
1225 MSC_CL_COLLECTION.v_current_user);
1226
1227 END IF;
1228
1229 c_count:= c_count+1;
1230
1231 IF c_count> MSC_CL_COLLECTION.PBS THEN
1232 COMMIT;
1233 c_count:= 0;
1234 END IF;
1235
1236 EXCEPTION
1237
1238 WHEN OTHERS THEN
1239
1240 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
1241
1242 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1243 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1244 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SOURCING');
1245 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SR_SOURCE_ORG');
1246 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1247
1248 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1249 RAISE;
1250
1251 ELSE
1252 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1253
1254 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1255 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1256 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SOURCING');
1257 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SR_SOURCE_ORG');
1258 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1259
1260 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1261 FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_SR_SOURCE_ID');
1262 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SR_SR_SOURCE_ID));
1263 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1264
1265 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1266 END IF;
1267
1268 END;
1269
1270 END LOOP; -- c5
1271
1272 COMMIT;
1273
1274 --/* it's removed due to bug 1219661
1275 DELETE MSC_SR_SOURCE_ORG
1276 WHERE DELETED_FLAG= MSC_UTIL.SYS_YES
1277 AND SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1278 AND COLLECTED_FLAG= MSC_UTIL.SYS_YES;
1279
1280 COMMIT;
1281 --*/
1282
1283 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
1284
1285 msc_analyse_tables_pk.analyse_table( 'MSC_SR_SOURCE_ORG');
1286
1287 END IF;
1288
1289
1290 UPDATE msc_st_interorg_ship_methods msism
1291 SET (shipment_weight, shipment_volume, shipment_weight_uom, shipment_volume_uom, leadtime_variability) =
1292 (SELECT shipment_weight, shipment_volume, shipment_weight_uom, shipment_volume_uom, leadtime_variability FROM
1293 msc_interorg_ship_methods mism
1294 WHERE mism.from_organization_id = msism.from_organization_id
1295 AND mism.sr_instance_id = msism.sr_instance_id
1296 AND mism.to_organization_id = msism.to_organization_id
1297 AND mism.sr_instance_id2 = msism.sr_instance_id2
1298 AND mism.plan_id = -1
1299 AND mism.from_location_id = msism.from_location_id
1300 AND mism.to_location_id = msism.to_location_id
1301 AND mism.ship_method = msism.ship_method);
1302
1303 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
1304
1305 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_INTERORG_SHIP_METHODS', MSC_CL_COLLECTION.v_instance_id,-1, 'AND nvl(COLLECTED_FLAG,1) <> 2');
1306
1307 END IF;
1308
1309 c_count:= 0;
1310
1311 FOR c_rec IN c6 LOOP
1312
1313 BEGIN
1314
1315 INSERT INTO MSC_INTERORG_SHIP_METHODS
1316 ( PLAN_ID,
1317 TRANSACTION_ID,
1318 FROM_ORGANIZATION_ID,
1319 TO_ORGANIZATION_ID,
1320 SHIP_METHOD,
1321 SHIP_METHOD_TEXT,
1322 TIME_UOM_CODE,
1323 DEFAULT_FLAG,
1324 FROM_LOCATION_ID,
1325 TO_LOCATION_ID,
1326 WEIGHT_CAPACITY,
1327 WEIGHT_UOM,
1328 VOLUME_CAPACITY,
1329 VOLUME_UOM,
1330 COST_PER_WEIGHT_UNIT,
1331 COST_PER_VOLUME_UNIT,
1332 INTRANSIT_TIME,
1333 TO_REGION_ID,
1334 FROM_REGION_ID,
1335 CURRENCY,
1336 TRANSPORT_CAP_OVER_UTIL_COST,
1337 SR_INSTANCE_ID,
1338 SR_INSTANCE_ID2,
1339 REFRESH_NUMBER,
1340 LAST_UPDATE_DATE,
1341 LAST_UPDATED_BY,
1342 CREATION_DATE,
1343 CREATED_BY,
1344 SHIPMENT_WEIGHT,
1345 SHIPMENT_VOLUME,
1346 SHIPMENT_WEIGHT_UOM,
1347 SHIPMENT_VOLUME_UOM,
1348 LEADTIME_VARIABILITY)
1349 VALUES
1350 ( -1,
1351 MSC_INTERORG_SHIP_METHODS_S.NEXTVAL,
1352 c_rec.FROM_ORGANIZATION_ID,
1353 c_rec.TO_ORGANIZATION_ID,
1354 c_rec.SHIP_METHOD,
1355 c_rec.SHIP_METHOD_TEXT,
1356 c_rec.TIME_UOM_CODE,
1357 c_rec.DEFAULT_FLAG,
1358 c_rec.FROM_LOCATION_ID,
1359 c_rec.TO_LOCATION_ID,
1360 c_rec.WEIGHT_CAPACITY,
1361 c_rec.WEIGHT_UOM,
1362 c_rec.VOLUME_CAPACITY,
1363 c_rec.VOLUME_UOM,
1364 c_rec.COST_PER_WEIGHT_UNIT,
1365 c_rec.COST_PER_VOLUME_UNIT,
1366 c_rec.INTRANSIT_TIME,
1367 c_rec.TO_REGION_ID,
1368 c_rec.FROM_REGION_ID,
1369 c_rec.CURRENCY,
1370 c_rec.TRANSPORT_CAP_OVER_UTIL_COST,
1371 c_rec.SR_INSTANCE_ID,
1372 c_rec.SR_INSTANCE_ID2,
1373 MSC_CL_COLLECTION.v_last_collection_id,
1374 MSC_CL_COLLECTION.v_current_date,
1375 MSC_CL_COLLECTION.v_current_user,
1376 MSC_CL_COLLECTION.v_current_date,
1377 MSC_CL_COLLECTION.v_current_user,
1378 c_rec.SHIPMENT_WEIGHT,
1379 c_rec.SHIPMENT_VOLUME,
1380 c_rec.SHIPMENT_WEIGHT_UOM,
1381 c_rec.SHIPMENT_VOLUME_UOM,
1382 c_rec.LEADTIME_VARIABILITY);
1383 /*
1384 c_count:= c_count+1;
1385
1386 IF c_count> MSC_CL_COLLECTION.PBS THEN
1387 COMMIT;
1388 c_count:= 0;
1389 END IF;
1390 */
1391 EXCEPTION
1392
1393 WHEN OTHERS THEN
1394
1395 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
1396
1397 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1398 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1399 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SOURCING');
1400 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_INTERORG_SHIP_METHODS');
1401 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1402
1403 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1404 RAISE;
1405
1406 ELSE
1407 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1408
1409 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1410 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1411 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SOURCING');
1412 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_INTERORG_SHIP_METHODS');
1413 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1414
1415 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1416 FND_MESSAGE.SET_TOKEN('COLUMN', 'FROM_ORGANIZATION_CODE');
1417 FND_MESSAGE.SET_TOKEN('VALUE',
1418 MSC_GET_NAME.ORG_CODE( c_rec.FROM_ORGANIZATION_ID,
1419 MSC_CL_COLLECTION.v_instance_id));
1420 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1421
1422 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1423 FND_MESSAGE.SET_TOKEN('COLUMN', 'TO_ORGANIZATION_CODE');
1424 FND_MESSAGE.SET_TOKEN('VALUE',
1425 MSC_GET_NAME.ORG_CODE( c_rec.TO_ORGANIZATION_ID,
1426 MSC_CL_COLLECTION.v_instance_id));
1427 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1428
1429 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1430 FND_MESSAGE.SET_TOKEN('COLUMN', 'FROM_LOCATION_ID');
1431 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.FROM_LOCATION_ID));
1432 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1433
1434 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1435 FND_MESSAGE.SET_TOKEN('COLUMN', 'TO_LOCATION_ID');
1436 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.TO_LOCATION_ID));
1437 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1438
1439 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1440 FND_MESSAGE.SET_TOKEN('COLUMN', 'SHIP_METHOD');
1441 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SHIP_METHOD);
1442 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1443
1444 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1445 END IF;
1446
1447 END;
1448
1449 END LOOP;
1450
1451 COMMIT;
1452
1453 /* Code added for Region Level Sourcing for ATP - Only for 11i Source */
1454 IF MSC_CL_COLLECTION.v_apps_ver >= 3 THEN
1455
1456
1457 IF (MSC_CL_COLLECTION.v_apps_ver = MSC_UTIL.G_APPS107) OR (MSC_CL_COLLECTION.v_apps_ver = MSC_UTIL.G_APPS110) THEN
1458 lv_msc_tp_coll_window := 0;
1459 ELSE
1460 BEGIN
1461 lv_msc_tp_coll_window:= NVL(TO_NUMBER(FND_PROFILE.VALUE('MSC_COLLECTION_WINDOW_FOR_TP_CHANGES')),0);
1462 EXCEPTION
1463 WHEN OTHERS THEN
1464 lv_msc_tp_coll_window := 0;
1465 END ;
1466 END IF;
1467 -- bug 4590579
1468 -- During complete/targeted refresh, we will delete or truncate the
1469 -- following tables based upon the profile option: MSC_PURGE_ST_CONTROL:
1470 -- 1. MSC_REGIONS
1471 -- 2. MSC_ZONE_REGIONS
1472 -- 3. MSC_REGION_LOCATIONS
1473 -- 4. MSC_REGION_SITES
1474 -- 5. MSC_CARRIER_SERVICES
1475
1476 SELECT
1477 decode(nvl(fnd_profile.value('MSC_PURGE_ST_CONTROL'),'N'),'Y',1,2)
1478 INTO lv_control_flag
1479 FROM dual;
1480
1481 /*
1482 Bug 5126455
1483 Tables:
1484 -- 1. MSC_REGIONS
1485 -- 2. MSC_ZONE_REGIONS
1486 -- 3. MSC_REGION_LOCATIONS
1487 -- 4. MSC_REGION_SITES
1488
1489 Changed row by row processing to do bulk update/insert
1490
1491 Also, the data pulled into the msc_st_region_locations will depend on
1492 the value of the profile, MSC_COLLECTION_WINDOW_FOR_TP_CHANGES.
1493 We will delete/truncate this ods tables only if the profile is null or 0
1494
1495 */
1496
1497
1498
1499 /* ------------- MSC_REGIONS ------------- */
1500 IF ((MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh)) THEN
1501
1502 BEGIN -- load for MSC_REGIONS
1503 /* Updating Who cols of Staging Tables */
1504 UPDATE MSC_ST_REGIONS
1505 SET
1506 REFRESH_NUMBER = MSC_CL_COLLECTION.v_last_collection_id,
1507 LAST_UPDATE_DATE = MSC_CL_COLLECTION.v_current_date,
1508 LAST_UPDATED_BY = MSC_CL_COLLECTION.v_current_user,
1509 CREATION_DATE = MSC_CL_COLLECTION.v_current_date,
1510 CREATED_BY = MSC_CL_COLLECTION.v_current_user,
1511 LAST_UPDATE_LOGIN = MSC_CL_COLLECTION.v_current_user
1512 WHERE SR_INSTANCE_ID = MSC_CL_COLLECTION.v_instance_id;
1513
1514 COMMIT;
1515
1516 /* Initialize the list */
1517 IF NOT MSC_CL_EXCHANGE_PARTTBL.Initialize_SWAP_Tbl_List(MSC_CL_COLLECTION.v_instance_id,MSC_CL_COLLECTION.v_instance_code) THEN
1518 RAISE MSC_CL_COLLECTION.EXCHANGE_PARTN_ERROR;
1519 END IF;
1520 /* Get the swap table index number in the list*/
1521 i := MSC_CL_EXCHANGE_PARTTBL.get_SWAP_table_index('MSC_REGIONS'); --ods table name
1522 IF i = -1 THEN
1523 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Table not in the list of SWAP partition');
1524 RAISE MSC_CL_COLLECTION.EXCHANGE_PARTN_ERROR;
1525 END IF;
1526 /* Do phase 1 exchange*/
1527
1528 IF NOT MSC_CL_EXCHANGE_PARTTBL.EXCHANGE_SINGLE_TAB_PARTN (
1529 MSC_CL_EXCHANGE_PARTTBL.v_swapTblList(i).stg_table_name,
1530 MSC_CL_EXCHANGE_PARTTBL.v_swapTblList(i).stg_table_partn_name,
1531 MSC_CL_EXCHANGE_PARTTBL.v_swapTblList(i).temp_table_name,
1532 MSC_UTIL.SYS_NO ) THEN
1533 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Exchange partition failed');
1534 RAISE MSC_CL_COLLECTION.EXCHANGE_PARTN_ERROR;
1535 END IF;
1536
1537 EXECUTE IMMEDIATE ' Update msc_coll_parameters set '
1538 || MSC_CL_EXCHANGE_PARTTBL.v_swapTblList(i).column_name || ' = '
1539 || MSC_CL_COLLECTION.G_STG_ODS_SWP_PHASE_1
1540 || ' where instance_id = ' || MSC_CL_COLLECTION.v_instance_id;
1541
1542 commit;
1543 /* Add code to copy required data from ods table to this temp table*/
1544
1545 /* Add code to create indexes on this temp table*/
1546 lv_crt_ind_status := MSC_CL_EXCHANGE_PARTTBL.create_temp_table_index
1547 ( 'NONUNIQUE',
1548 MSC_CL_EXCHANGE_PARTTBL.v_swapTblList(i).ods_table_name,
1549 MSC_CL_EXCHANGE_PARTTBL.v_swapTblList(i).temp_table_name,
1550 MSC_CL_COLLECTION.v_instance_code,
1551 MSC_CL_COLLECTION.v_instance_id,
1552 MSC_UTIL.SYS_NO,
1553 MSC_CL_COLLECTION.G_WARNING
1554 );
1555
1556 IF lv_crt_ind_status = MSC_CL_COLLECTION.G_WARNING THEN
1557 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Warning during nonunique index creation on table, ' || MSC_CL_EXCHANGE_PARTTBL.v_swapTblList(i).temp_table_name);
1558 ELSIF lv_crt_ind_status = MSC_CL_COLLECTION.G_ERROR THEN
1559 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error during nonunique index creation on table, ' || MSC_CL_EXCHANGE_PARTTBL.v_swapTblList(i).temp_table_name);
1560 --RETURN ;
1561 ELSE
1562 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'NonUnique index creation successful on table, ' || MSC_CL_EXCHANGE_PARTTBL.v_swapTblList(i).temp_table_name);
1563 END IF;
1564
1565 COMMIT;
1566
1567
1568
1569 EXCEPTION
1570 WHEN MSC_CL_COLLECTION.EXCHANGE_PARTN_ERROR THEN
1571 RAISE;
1572 WHEN OTHERS THEN
1573
1574 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
1575
1576 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1577 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1578 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SOURCING');
1579 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_REGIONS');
1580 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1581
1582 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1583 RAISE;
1584
1585 ELSE
1586 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1587
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_SOURCING');
1591 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_REGIONS');
1592 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1593 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1594 END IF;
1595 END; -- load for MSC_REGIONS
1596
1597
1598
1599 /* ------------- MSC_ZONE_REGIONS ------------- */
1600
1601 BEGIN -- load for MSC_ZONE_REGIONS
1602 /* Updating Who cols of Staging Tables */
1603 UPDATE MSC_ST_ZONE_REGIONS
1604 SET
1605 REFRESH_NUMBER = MSC_CL_COLLECTION.v_last_collection_id,
1606 LAST_UPDATE_DATE = MSC_CL_COLLECTION.v_current_date,
1607 LAST_UPDATED_BY = MSC_CL_COLLECTION.v_current_user,
1608 CREATION_DATE = MSC_CL_COLLECTION.v_current_date,
1609 CREATED_BY = MSC_CL_COLLECTION.v_current_user,
1610 LAST_UPDATE_LOGIN = MSC_CL_COLLECTION.v_current_user
1611 WHERE SR_INSTANCE_ID = MSC_CL_COLLECTION.v_instance_id;
1612
1613 COMMIT;
1614
1615 /* Initialize the list */
1616 IF NOT MSC_CL_EXCHANGE_PARTTBL.Initialize_SWAP_Tbl_List(MSC_CL_COLLECTION.v_instance_id,MSC_CL_COLLECTION.v_instance_code) THEN
1617 RAISE MSC_CL_COLLECTION.EXCHANGE_PARTN_ERROR;
1618 END IF;
1619 /* Get the swap table index number in the list*/
1620 i := MSC_CL_EXCHANGE_PARTTBL.get_SWAP_table_index('MSC_ZONE_REGIONS'); --ods table name
1621 IF i = -1 THEN
1622 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Table not in the list of SWAP partition');
1623 RAISE MSC_CL_COLLECTION.EXCHANGE_PARTN_ERROR;
1624 END IF;
1625 /* Do phase 1 exchange*/
1626
1627 IF NOT MSC_CL_EXCHANGE_PARTTBL.EXCHANGE_SINGLE_TAB_PARTN (
1628 MSC_CL_EXCHANGE_PARTTBL.v_swapTblList(i).stg_table_name,
1629 MSC_CL_EXCHANGE_PARTTBL.v_swapTblList(i).stg_table_partn_name,
1630 MSC_CL_EXCHANGE_PARTTBL.v_swapTblList(i).temp_table_name,
1631 MSC_UTIL.SYS_NO ) THEN
1632 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Exchange partition failed');
1633 RAISE MSC_CL_COLLECTION.EXCHANGE_PARTN_ERROR;
1634 END IF;
1635
1636 EXECUTE IMMEDIATE ' Update msc_coll_parameters set '
1637 || MSC_CL_EXCHANGE_PARTTBL.v_swapTblList(i).column_name || ' = '
1638 || MSC_CL_COLLECTION.G_STG_ODS_SWP_PHASE_1
1639 || ' where instance_id = ' || MSC_CL_COLLECTION.v_instance_id;
1640
1641 commit;
1642 /* Add code to copy required data from ods table to this temp table*/
1643
1644 /* Add code to create indexes on this temp table*/
1645 lv_crt_ind_status := MSC_CL_EXCHANGE_PARTTBL.create_temp_table_index
1646 ( 'NONUNIQUE',
1647 MSC_CL_EXCHANGE_PARTTBL.v_swapTblList(i).ods_table_name,
1648 MSC_CL_EXCHANGE_PARTTBL.v_swapTblList(i).temp_table_name,
1649 MSC_CL_COLLECTION.v_instance_code,
1650 MSC_CL_COLLECTION.v_instance_id,
1651 MSC_UTIL.SYS_NO,
1652 MSC_CL_COLLECTION.G_WARNING
1653 );
1654
1655 IF lv_crt_ind_status = MSC_CL_COLLECTION.G_WARNING THEN
1656 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Warning during nonunique index creation on table, ' || MSC_CL_EXCHANGE_PARTTBL.v_swapTblList(i).temp_table_name);
1657 ELSIF lv_crt_ind_status = MSC_CL_COLLECTION.G_ERROR THEN
1658 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error during nonunique index creation on table, ' || MSC_CL_EXCHANGE_PARTTBL.v_swapTblList(i).temp_table_name);
1659 -- RETURN ;
1660 ELSE
1661 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'NonUnique index creation successful on table, ' || MSC_CL_EXCHANGE_PARTTBL.v_swapTblList(i).temp_table_name);
1662 END IF;
1663
1664 EXCEPTION
1665 WHEN MSC_CL_COLLECTION.EXCHANGE_PARTN_ERROR THEN
1666 RAISE;
1667 WHEN OTHERS THEN
1668
1669 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
1670
1671 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1672 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1673 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SOURCING');
1674 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ZONE_REGIONS');
1675 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1676
1677 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1678 RAISE;
1679
1680 ELSE
1681 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1682
1683 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1684 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1685 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SOURCING');
1686 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ZONE_REGIONS');
1687 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1688 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1689 END IF;
1690 END; -- load for MSC_ZONE_REGIONS
1691
1692
1693 /* ------------- MSC_REGION_LOCATIONS ------------- */
1694 BEGIN -- load for MSC_REGION_LOCATIONS
1695 /* Updating Who cols of Staging Tables */
1696 UPDATE MSC_ST_REGION_LOCATIONS
1697 SET
1698 REFRESH_NUMBER = MSC_CL_COLLECTION.v_last_collection_id,
1699 LAST_UPDATE_DATE = MSC_CL_COLLECTION.v_current_date,
1700 LAST_UPDATED_BY = MSC_CL_COLLECTION.v_current_user,
1701 CREATION_DATE = MSC_CL_COLLECTION.v_current_date,
1702 CREATED_BY = MSC_CL_COLLECTION.v_current_user,
1703 LAST_UPDATE_LOGIN = MSC_CL_COLLECTION.v_current_user
1704 WHERE SR_INSTANCE_ID = MSC_CL_COLLECTION.v_instance_id;
1705
1706 COMMIT;
1707
1708 /* Initialize the list */
1709 IF NOT MSC_CL_EXCHANGE_PARTTBL.Initialize_SWAP_Tbl_List(MSC_CL_COLLECTION.v_instance_id,MSC_CL_COLLECTION.v_instance_code) THEN
1710 RAISE MSC_CL_COLLECTION.EXCHANGE_PARTN_ERROR;
1711 END IF;
1712 /* Get the swap table index number in the list*/
1713 i := MSC_CL_EXCHANGE_PARTTBL.get_SWAP_table_index('MSC_REGION_LOCATIONS'); --ods table name
1714 IF i = -1 THEN
1715 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Table not in the list of SWAP partition');
1716 RAISE MSC_CL_COLLECTION.EXCHANGE_PARTN_ERROR;
1717 END IF;
1718 /* Do phase 1 exchange*/
1719
1720 IF NOT MSC_CL_EXCHANGE_PARTTBL.EXCHANGE_SINGLE_TAB_PARTN (
1721 MSC_CL_EXCHANGE_PARTTBL.v_swapTblList(i).stg_table_name,
1722 MSC_CL_EXCHANGE_PARTTBL.v_swapTblList(i).stg_table_partn_name,
1723 MSC_CL_EXCHANGE_PARTTBL.v_swapTblList(i).temp_table_name,
1724 MSC_UTIL.SYS_NO ) THEN
1725 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Exchange partition failed');
1726 RAISE MSC_CL_COLLECTION.EXCHANGE_PARTN_ERROR;
1727 END IF;
1728
1729 EXECUTE IMMEDIATE ' Update msc_coll_parameters set '
1730 || MSC_CL_EXCHANGE_PARTTBL.v_swapTblList(i).column_name || ' = '
1731 || MSC_CL_COLLECTION.G_STG_ODS_SWP_PHASE_1
1732 || ' where instance_id = ' || MSC_CL_COLLECTION.v_instance_id;
1733
1734 commit;
1735 /* Add code to copy required data from ods table to this temp table*/
1736
1737 /* Add code to create indexes on this temp table*/
1738 lv_crt_ind_status := MSC_CL_EXCHANGE_PARTTBL.create_temp_table_index
1739 ( 'NONUNIQUE',
1740 MSC_CL_EXCHANGE_PARTTBL.v_swapTblList(i).ods_table_name,
1741 MSC_CL_EXCHANGE_PARTTBL.v_swapTblList(i).temp_table_name,
1742 MSC_CL_COLLECTION.v_instance_code,
1743 MSC_CL_COLLECTION.v_instance_id,
1744 MSC_UTIL.SYS_NO,
1745 MSC_CL_COLLECTION.G_WARNING
1746 );
1747
1748 IF lv_crt_ind_status = MSC_CL_COLLECTION.G_WARNING THEN
1749 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Warning during nonunique index creation on table, ' || MSC_CL_EXCHANGE_PARTTBL.v_swapTblList(i).temp_table_name);
1750 ELSIF lv_crt_ind_status = MSC_CL_COLLECTION.G_ERROR THEN
1751 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error during nonunique index creation on table, ' || MSC_CL_EXCHANGE_PARTTBL.v_swapTblList(i).temp_table_name);
1752 -- RETURN ;
1753 ELSE
1754 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'NonUnique index creation successful on table, ' || MSC_CL_EXCHANGE_PARTTBL.v_swapTblList(i).temp_table_name);
1755 END IF;
1756
1757 EXCEPTION
1758 WHEN MSC_CL_COLLECTION.EXCHANGE_PARTN_ERROR THEN
1759 RAISE;
1760 WHEN OTHERS THEN
1761
1762 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
1763
1764 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1765 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1766 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SOURCING');
1767 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_REGION_LOCATIONS');
1768 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1769
1770 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1771 RAISE;
1772
1773 ELSE
1774 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1775
1776 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1777 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1778 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SOURCING');
1779 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_REGION_LOCATIONS');
1780 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1781 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1782 END IF;
1783 END; -- load for MSC_REGION_LOCATIONS
1784 END IF; --IF ((MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh)) THEN
1785
1786
1787 /* ------------- MSC_REGION_SITES ------------- */
1788 BEGIN
1789
1790 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
1791 IF lv_control_flag = 2 THEN
1792 MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_REGION_SITES', MSC_CL_COLLECTION.v_instance_id,NULL);
1793 ELSE
1794 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE('MSC_REGION_SITES');
1795 END IF;
1796 END IF;
1797
1798 lv_sql_stmt:=
1799 ' INSERT INTO MSC_REGION_SITES '
1800 ||' (REGION_ID, '
1801 ||' VENDOR_SITE_ID, '
1802 ||' REGION_TYPE, '
1803 ||' ZONE_LEVEL, '
1804 ||' SR_INSTANCE_ID, '
1805 ||' REFRESH_NUMBER, '
1806 ||' CREATED_BY, '
1807 ||' CREATION_DATE, '
1808 ||' LAST_UPDATED_BY, '
1809 ||' LAST_UPDATE_DATE, '
1810 ||' LAST_UPDATE_LOGIN) '
1811 ||' (SELECT DISTINCT '
1812 ||' msrs.REGION_ID, '
1813 ||' mtsil.TP_SITE_ID, '
1814 ||' msrs.REGION_TYPE, '
1815 ||' msrs.ZONE_LEVEL, '
1816 ||' msrs.SR_INSTANCE_ID, '
1817 ||' :v_last_collection_id, '
1818 ||' :v_current_user, '
1819 ||' :v_current_date, '
1820 ||' :v_current_user, '
1821 ||' :v_current_date, '
1822 ||' :v_current_user '
1823 ||' FROM MSC_ST_REGION_SITES msrs, '
1824 ||' MSC_TP_SITE_ID_LID mtsil '
1825 ||' WHERE msrs.SR_INSTANCE_ID= :v_instance_id '
1826 ||' AND mtsil.SR_TP_SITE_ID = msrs.VENDOR_SITE_ID '
1827 ||' AND mtsil.SR_Instance_ID = msrs.SR_INSTANCE_ID '
1828 ||' AND mtsil.Partner_Type = 1 '
1829 ||' ) ';
1830
1831 EXECUTE IMMEDIATE lv_sql_stmt
1832 USING MSC_CL_COLLECTION.v_last_collection_id,
1833 MSC_CL_COLLECTION.v_current_user,
1834 MSC_CL_COLLECTION.v_current_date,
1835 MSC_CL_COLLECTION.v_current_user,
1836 MSC_CL_COLLECTION.v_current_date,
1837 MSC_CL_COLLECTION.v_current_user,
1838 MSC_CL_COLLECTION.v_instance_id;
1839
1840 COMMIT;
1841 EXCEPTION
1842 WHEN OTHERS THEN
1843 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
1844
1845 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1846 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1847 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SOURCING');
1848 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_REGION_SITES');
1849 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1850
1851 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1852 RAISE;
1853
1854 ELSE
1855 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1856
1857 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1858 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1859 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SOURCING');
1860 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_REGION_SITES');
1861 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1862
1863 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1864 END IF;
1865
1866 END;
1867
1868
1869
1870 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
1871
1872 IF lv_control_flag = 2 THEN
1873 MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_CARRIER_SERVICES', MSC_CL_COLLECTION.v_instance_id,-1);
1874 ELSE
1875 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE('MSC_CARRIER_SERVICES');
1876 END IF;
1877
1878 END IF;
1879
1880 FOR c_rec IN c11 LOOP
1881
1882 BEGIN
1883
1884 INSERT INTO MSC_CARRIER_SERVICES
1885 (SHIP_METHOD_CODE,
1886 CARRIER_ID,
1887 SERVICE_LEVEL,
1888 MODE_OF_TRANSPORT,
1889 SR_INSTANCE_ID,
1890 REFRESH_NUMBER,
1891 PLAN_ID,
1892 CREATED_BY,
1893 CREATION_DATE,
1894 LAST_UPDATED_BY,
1895 LAST_UPDATE_DATE,
1896 LAST_UPDATE_LOGIN)
1897 VALUES
1898 (c_rec.SHIP_METHOD_CODE,
1899 c_rec.TP_ID,
1900 c_rec.SERVICE_LEVEL,
1901 c_rec.MODE_OF_TRANSPORT,
1902 c_rec.SR_INSTANCE_ID,
1903 MSC_CL_COLLECTION.v_last_collection_id,
1904 -1,
1905 MSC_CL_COLLECTION.v_current_user,
1906 MSC_CL_COLLECTION.v_current_date,
1907 MSC_CL_COLLECTION.v_current_user,
1908 MSC_CL_COLLECTION.v_current_date,
1909 MSC_CL_COLLECTION.v_current_user);
1910
1911 EXCEPTION
1912 WHEN OTHERS THEN
1913 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
1914
1915 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1916 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1917 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SOURCING');
1918 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CARRIER_SERVICES');
1919 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1920
1921 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1922 RAISE;
1923
1924 ELSE
1925 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1926
1927 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1928 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1929 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SOURCING');
1930 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_REGION_SITES');
1931 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1932
1933 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1934 END IF;
1935
1936 END;
1937
1938 END LOOP;
1939
1940 COMMIT;
1941
1942 END IF;
1943
1944 END LOAD_SOURCING;
1945
1946
1947 --==================================================================
1948
1949 PROCEDURE LOAD_SUB_INVENTORY IS
1950
1951 CURSOR c1 IS
1952 SELECT
1953 mssi.ORGANIZATION_ID,
1954 mssi.SUB_INVENTORY_CODE,
1955 mssi.NETTING_TYPE,
1956 mssi.INVENTORY_ATP_CODE,
1957 substrb(mssi.DESCRIPTION,1,50) DESCRIPTION, --added for the NLS bug3463401
1958 mssi.SR_INSTANCE_ID,
1959 mssi.condition_type, -- For Bug # 5660122 SRP Changes
1960 mssi.SR_RESOURCE_NAME,
1961 mssi.SR_CUSTOMER_ACCT_ID
1962 FROM MSC_ST_SUB_INVENTORIES mssi
1963 WHERE mssi.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1964
1965 c_count NUMBER:= 0;
1966
1967 BEGIN
1968
1969 IF (MSC_CL_COLLECTION.v_is_complete_refresh or MSC_CL_COLLECTION.v_is_partial_refresh) THEN
1970
1971 --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUB_INVENTORIES', MSC_CL_COLLECTION.v_instance_id, -1);
1972
1973 IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1974 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUB_INVENTORIES', MSC_CL_COLLECTION.v_instance_id, -1);
1975 ELSE
1976 MSC_CL_COLLECTION.v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1977 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUB_INVENTORIES', MSC_CL_COLLECTION.v_instance_id, -1,MSC_CL_COLLECTION.v_sub_str);
1978 END IF;
1979
1980 END IF;
1981
1982 c_count:= 0;
1983
1984 FOR c_rec IN c1 LOOP
1985
1986 BEGIN
1987
1988 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
1989
1990 UPDATE MSC_SUB_INVENTORIES
1991 SET
1992 NETTING_TYPE= c_rec.NETTING_TYPE,
1993 INVENTORY_ATP_CODE= c_rec.INVENTORY_ATP_CODE,
1994 DESCRIPTION= c_rec.DESCRIPTION,
1995 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1996 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1997 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
1998 condition_type=c_rec.condition_type, -- For Bug # 5660122 SRP Changes
1999 SR_RESOURCE_NAME=c_rec.SR_RESOURCE_NAME,
2000 SR_CUSTOMER_ACCT_ID=c_rec.SR_CUSTOMER_ACCT_ID
2001 WHERE PLAN_ID= -1
2002 AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
2003 AND SUB_INVENTORY_CODE= c_rec.SUB_INVENTORY_CODE
2004 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
2005
2006 END IF;
2007
2008 IF (MSC_CL_COLLECTION.v_is_complete_refresh or MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
2009
2010 INSERT INTO MSC_SUB_INVENTORIES
2011 ( PLAN_ID,
2012 ORGANIZATION_ID,
2013 SUB_INVENTORY_CODE,
2014 NETTING_TYPE,
2015 INVENTORY_ATP_CODE,
2016 DESCRIPTION,
2017 CONDITION_TYPE,
2018 SR_RESOURCE_NAME,
2019 SR_CUSTOMER_ACCT_ID, -- For Bug # 5660122 SRP Changes
2020 SR_INSTANCE_ID,
2021 REFRESH_NUMBER,
2022 LAST_UPDATE_DATE,
2023 LAST_UPDATED_BY,
2024 CREATION_DATE,
2025 CREATED_BY)
2026 VALUES
2027 ( -1,
2028 c_rec.ORGANIZATION_ID,
2029 c_rec.SUB_INVENTORY_CODE,
2030 c_rec.NETTING_TYPE,
2031 c_rec.INVENTORY_ATP_CODE,
2032 c_rec.DESCRIPTION,
2033 c_rec.CONDITION_TYPE,
2034 c_rec.SR_RESOURCE_NAME,
2035 c_rec.SR_CUSTOMER_ACCT_ID, -- For Bug # 5660122 SRP Changes
2036 c_rec.SR_INSTANCE_ID,
2037 MSC_CL_COLLECTION.v_last_collection_id,
2038 MSC_CL_COLLECTION.v_current_date,
2039 MSC_CL_COLLECTION.v_current_user,
2040 MSC_CL_COLLECTION.v_current_date,
2041 MSC_CL_COLLECTION.v_current_user );
2042
2043
2044 END IF;
2045
2046 c_count:= c_count+1;
2047
2048 IF c_count> MSC_CL_COLLECTION.PBS THEN
2049 COMMIT;
2050 c_count:= 0;
2051 END IF;
2052
2053 EXCEPTION
2054
2055 WHEN OTHERS THEN
2056
2057 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
2058
2059 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2060 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2061 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUB_INVENTORY');
2062 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SUB_INVENTORIES');
2063 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2064
2065 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2066 RAISE;
2067
2068 ELSE
2069 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2070
2071 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2072 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2073 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUB_INVENTORY');
2074 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SUB_INVENTORIES');
2075 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2076
2077 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2078 FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
2079 FND_MESSAGE.SET_TOKEN('VALUE',
2080 MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
2081 MSC_CL_COLLECTION.v_instance_id));
2082 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2083
2084 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2085 FND_MESSAGE.SET_TOKEN('COLUMN', 'SUB_INVENTORY_CODE');
2086 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SUB_INVENTORY_CODE);
2087 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2088
2089 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2090 END IF;
2091 END;
2092
2093 END LOOP;
2094
2095 COMMIT;
2096
2097 END LOAD_SUB_INVENTORY;
2098
2099 --==================================================================
2100
2101 PROCEDURE LOAD_UNIT_NUMBER IS
2102
2103 CURSOR c1 IS
2104 SELECT
2105 msun.UNIT_NUMBER,
2106 t1.INVENTORY_ITEM_ID END_ITEM_ID,
2107 msun.MASTER_ORGANIZATION_ID,
2108 msun.COMMENTS,
2109 msun.SR_INSTANCE_ID
2110 FROM MSC_ITEM_ID_LID t1,
2111 MSC_ST_Unit_Numbers msun
2112 WHERE t1.SR_INVENTORY_ITEM_ID= msun.end_item_id
2113 AND t1.sr_instance_id= msun.sr_instance_id
2114 AND msun.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
2115
2116 c_count NUMBER:= 0;
2117
2118 BEGIN
2119
2120 IF (MSC_CL_COLLECTION.v_is_complete_refresh or MSC_CL_COLLECTION.v_is_partial_refresh) THEN
2121
2122 --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_UNIT_NUMBERS', MSC_CL_COLLECTION.v_instance_id, NULL);
2123
2124 IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2125 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_UNIT_NUMBERS', MSC_CL_COLLECTION.v_instance_id,NULL);
2126 ELSE
2127 MSC_CL_COLLECTION.v_sub_str :='AND MASTER_ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
2128 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_UNIT_NUMBERS', MSC_CL_COLLECTION.v_instance_id,NULL,MSC_CL_COLLECTION.v_sub_str);
2129 END IF;
2130
2131 END IF;
2132
2133 c_count:= 0;
2134
2135 FOR c_rec IN c1 LOOP
2136
2137 BEGIN
2138
2139 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
2140
2141 UPDATE MSC_UNIT_NUMBERS
2142 SET
2143 END_ITEM_ID= c_rec.END_ITEM_ID,
2144 MASTER_ORGANIZATION_ID= c_rec.MASTER_ORGANIZATION_ID,
2145 COMMENTS= c_rec.COMMENTS,
2146 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2147 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2148 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2149 WHERE UNIT_NUMBER= c_rec.UNIT_NUMBER
2150 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
2151
2152 END IF;
2153
2154 IF (MSC_CL_COLLECTION.v_is_complete_refresh or MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
2155
2156 INSERT INTO MSC_UNIT_NUMBERS
2157 ( UNIT_NUMBER,
2158 END_ITEM_ID,
2159 MASTER_ORGANIZATION_ID,
2160 COMMENTS,
2161 SR_INSTANCE_ID,
2162 REFRESH_NUMBER,
2163 LAST_UPDATE_DATE,
2164 LAST_UPDATED_BY,
2165 CREATION_DATE,
2166 CREATED_BY)
2167 VALUES
2168 ( c_rec.UNIT_NUMBER,
2169 c_rec.END_ITEM_ID,
2170 c_rec.MASTER_ORGANIZATION_ID,
2171 c_rec.COMMENTS,
2172 c_rec.SR_INSTANCE_ID,
2173 MSC_CL_COLLECTION.v_last_collection_id,
2174 MSC_CL_COLLECTION.v_current_date,
2175 MSC_CL_COLLECTION.v_current_user,
2176 MSC_CL_COLLECTION.v_current_date,
2177 MSC_CL_COLLECTION.v_current_user );
2178
2179 END IF;
2180
2181 c_count:= c_count+1;
2182
2183 IF c_count> MSC_CL_COLLECTION.PBS THEN
2184 COMMIT;
2185 c_count:= 0;
2186 END IF;
2187
2188 EXCEPTION
2189 WHEN OTHERS THEN
2190
2191 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
2192
2193 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2194 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2195 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_UNIT_NUMBER');
2196 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_UNIT_NUMBERS');
2197 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2198
2199 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2200 RAISE;
2201
2202 ELSE
2203 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2204
2205 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2206 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2207 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_UNIT_NUMBER');
2208 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_UNIT_NUMBERS');
2209 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2210
2211 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2212 FND_MESSAGE.SET_TOKEN('COLUMN', 'UNIT_NUMBER');
2213 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.UNIT_NUMBER);
2214 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2215
2216 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2217 FND_MESSAGE.SET_TOKEN('COLUMN', 'END_ITEM_ID');
2218 FND_MESSAGE.SET_TOKEN('VALUE', MSC_CL_ITEM_ODS_LOAD.ITEM_NAME(c_rec.END_ITEM_ID));
2219 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2220
2221 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2222 FND_MESSAGE.SET_TOKEN('COLUMN', 'MASTER_ORGANIZATION_CODE');
2223 FND_MESSAGE.SET_TOKEN('VALUE',
2224 MSC_GET_NAME.ORG_CODE(
2225 c_rec.MASTER_ORGANIZATION_ID,
2226 MSC_CL_COLLECTION.v_instance_id));
2227 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2228
2229 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2230 END IF;
2231
2232 END;
2233
2234 END LOOP;
2235
2236 COMMIT;
2237
2238 END LOAD_UNIT_NUMBER;
2239 --==================================================================
2240
2241 PROCEDURE LOAD_PROJECT IS
2242
2243 CURSOR c1 IS
2244 SELECT
2245 msp.PROJECT_ID,
2246 msp.ORGANIZATION_ID,
2247 msp.PLANNING_GROUP,
2248 msp.COSTING_GROUP_ID,
2249 msp.MATERIAL_ACCOUNT,
2250 msp.WIP_ACCT_CLASS_CODE,
2251 msp.SEIBAN_NUMBER_FLAG,
2252 msp.PROJECT_NAME,
2253 msp.PROJECT_NUMBER,
2254 msp.PROJECT_NUMBER_SORT_ORDER,
2255 msp.PROJECT_DESCRIPTION,
2256 msp.START_DATE,
2257 msp.COMPLETION_DATE,
2258 msp.OPERATING_UNIT,
2259 msp.MANAGER_CONTACT,
2260 msp.SR_INSTANCE_ID
2261 FROM MSC_ST_PROJECTS msp
2262 WHERE msp.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
2263
2264 CURSOR c2 IS
2265 SELECT
2266 mspt.PROJECT_ID,
2267 mspt.TASK_ID,
2268 mspt.ORGANIZATION_ID,
2269 mspt.TASK_NUMBER,
2270 mspt.TASK_NAME,
2271 mspt.DESCRIPTION,
2272 mspt.MANAGER,
2273 mspt.START_DATE,
2274 mspt.END_DATE,
2275 mspt.MANAGER_CONTACT,
2276 mspt.SR_INSTANCE_ID
2277 FROM MSC_ST_PROJECT_TASKS mspt
2278 WHERE mspt.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
2279
2280 c_count NUMBER:= 0;
2281
2282 BEGIN
2283
2284 IF (MSC_CL_COLLECTION.v_is_complete_refresh or MSC_CL_COLLECTION.v_is_partial_refresh) THEN
2285
2286 --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_PROJECTS', MSC_CL_COLLECTION.v_instance_id, -1);
2287
2288 IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2289 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_PROJECTS', MSC_CL_COLLECTION.v_instance_id, -1);
2290 ELSE
2291 MSC_CL_COLLECTION.v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2292 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_PROJECTS', MSC_CL_COLLECTION.v_instance_id, -1,MSC_CL_COLLECTION.v_sub_str);
2293 END IF;
2294
2295 END IF;
2296
2297 c_count:= 0;
2298
2299 FOR c_rec IN c1 LOOP
2300
2301 BEGIN
2302
2303 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
2304
2305 UPDATE MSC_PROJECTS
2306 SET
2307 PLANNING_GROUP= c_rec.PLANNING_GROUP,
2308 COSTING_GROUP_ID= c_rec.COSTING_GROUP_ID,
2309 MATERIAL_ACCOUNT= c_rec.MATERIAL_ACCOUNT,
2310 WIP_ACCT_CLASS_CODE= c_rec.WIP_ACCT_CLASS_CODE,
2311 SEIBAN_NUMBER_FLAG= c_rec.SEIBAN_NUMBER_FLAG,
2312 PROJECT_NAME= c_rec.PROJECT_NAME,
2313 PROJECT_NUMBER= c_rec.PROJECT_NUMBER,
2314 PROJECT_NUMBER_SORT_ORDER= c_rec.PROJECT_NUMBER_SORT_ORDER,
2315 PROJECT_DESCRIPTION= c_rec.PROJECT_DESCRIPTION,
2316 START_DATE= c_rec.START_DATE,
2317 COMPLETION_DATE= c_rec.COMPLETION_DATE,
2318 OPERATING_UNIT= c_rec.OPERATING_UNIT,
2319 MANAGER_CONTACT= c_rec.MANAGER_CONTACT,
2320 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2321 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2322 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2323 WHERE PLAN_ID= -1
2324 AND PROJECT_ID= c_rec.PROJECT_ID
2325 AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
2326 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
2327
2328 END IF;
2329
2330 IF (MSC_CL_COLLECTION.v_is_complete_refresh or MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
2331
2332 INSERT INTO MSC_PROJECTS
2333 ( PLAN_ID,
2334 PROJECT_ID,
2335 ORGANIZATION_ID,
2336 PLANNING_GROUP,
2337 COSTING_GROUP_ID,
2338 MATERIAL_ACCOUNT,
2339 WIP_ACCT_CLASS_CODE,
2340 SEIBAN_NUMBER_FLAG,
2341 PROJECT_NAME,
2342 PROJECT_NUMBER,
2343 PROJECT_NUMBER_SORT_ORDER,
2344 PROJECT_DESCRIPTION,
2345 START_DATE,
2346 COMPLETION_DATE,
2347 OPERATING_UNIT,
2348 MANAGER_CONTACT,
2349 SR_INSTANCE_ID,
2350 REFRESH_NUMBER,
2351 LAST_UPDATE_DATE,
2352 LAST_UPDATED_BY,
2353 CREATION_DATE,
2354 CREATED_BY)
2355 VALUES
2356 ( -1,
2357 c_rec.PROJECT_ID,
2358 c_rec.ORGANIZATION_ID,
2359 c_rec.PLANNING_GROUP,
2360 c_rec.COSTING_GROUP_ID,
2361 c_rec.MATERIAL_ACCOUNT,
2362 c_rec.WIP_ACCT_CLASS_CODE,
2363 c_rec.SEIBAN_NUMBER_FLAG,
2364 c_rec.PROJECT_NAME,
2365 c_rec.PROJECT_NUMBER,
2366 c_rec.PROJECT_NUMBER_SORT_ORDER,
2367 c_rec.PROJECT_DESCRIPTION,
2368 c_rec.START_DATE,
2369 c_rec.COMPLETION_DATE,
2370 c_rec.OPERATING_UNIT,
2371 c_rec.MANAGER_CONTACT,
2372 c_rec.SR_INSTANCE_ID,
2373 MSC_CL_COLLECTION.v_last_collection_id,
2374 MSC_CL_COLLECTION.v_current_date,
2375 MSC_CL_COLLECTION.v_current_user,
2376 MSC_CL_COLLECTION.v_current_date,
2377 MSC_CL_COLLECTION.v_current_user );
2378
2379 END IF;
2380
2381 c_count:= c_count+1;
2382
2383 IF c_count> MSC_CL_COLLECTION.PBS THEN
2384 COMMIT;
2385 c_count:= 0;
2386 END IF;
2387
2388 EXCEPTION
2389 WHEN OTHERS THEN
2390
2391 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
2392
2393 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2394 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2395 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_PROJECT');
2396 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_PROJECTS');
2397 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2398
2399 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2400 RAISE;
2401
2402 ELSE
2403
2404 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2405
2406 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2407 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2408 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_PROJECT');
2409 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_PROJECTS');
2410 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2411
2412 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2413 FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
2414 FND_MESSAGE.SET_TOKEN('VALUE',
2415 MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
2416 MSC_CL_COLLECTION.v_instance_id));
2417 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2418
2419 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2420 FND_MESSAGE.SET_TOKEN('COLUMN', 'PROJECT_NAME');
2421 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PROJECT_NAME);
2422 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2423
2424 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2425 END IF;
2426
2427 END;
2428
2429 END LOOP;
2430
2431 COMMIT;
2432
2433 IF (MSC_CL_COLLECTION.v_is_complete_refresh or MSC_CL_COLLECTION.v_is_partial_refresh) THEN
2434
2435 --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_PROJECT_TASKS', MSC_CL_COLLECTION.v_instance_id, -1);
2436
2437 IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2438 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_PROJECT_TASKS', MSC_CL_COLLECTION.v_instance_id, -1);
2439 ELSE
2440 MSC_CL_COLLECTION.v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2441 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_PROJECT_TASKS', MSC_CL_COLLECTION.v_instance_id, -1,MSC_CL_COLLECTION.v_sub_str);
2442 END IF;
2443
2444 END IF;
2445
2446 c_count:= 0;
2447
2448 FOR c_rec IN c2 LOOP
2449
2450 BEGIN
2451
2452 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
2453
2454 UPDATE MSC_PROJECT_TASKS
2455 SET
2456 TASK_NUMBER= c_rec.TASK_NUMBER,
2457 TASK_NAME= c_rec.TASK_NAME,
2458 DESCRIPTION= c_rec.DESCRIPTION,
2459 MANAGER= c_rec.MANAGER,
2460 START_DATE= c_rec.START_DATE,
2461 END_DATE= c_rec.END_DATE,
2462 MANAGER_CONTACT= c_rec.MANAGER_CONTACT,
2463 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2464 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2465 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2466 WHERE PLAN_ID= -1
2467 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
2468 AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
2469 AND PROJECT_ID= c_rec.PROJECT_ID
2470 AND TASK_ID= c_rec.TASK_ID;
2471
2472 END IF;
2473
2474 IF (MSC_CL_COLLECTION.v_is_complete_refresh or MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
2475
2476 INSERT INTO MSC_PROJECT_TASKS
2477 ( PLAN_ID,
2478 PROJECT_ID,
2479 TASK_ID,
2480 ORGANIZATION_ID,
2481 TASK_NUMBER,
2482 TASK_NAME,
2483 DESCRIPTION,
2484 MANAGER,
2485 START_DATE,
2486 END_DATE,
2487 MANAGER_CONTACT,
2488 SR_INSTANCE_ID,
2489 REFRESH_NUMBER,
2490 LAST_UPDATE_DATE,
2491 LAST_UPDATED_BY,
2492 CREATION_DATE,
2493 CREATED_BY)
2494 VALUES
2495 ( -1,
2496 c_rec.PROJECT_ID,
2497 c_rec.TASK_ID,
2498 c_rec.ORGANIZATION_ID,
2499 c_rec.TASK_NUMBER,
2500 c_rec.TASK_NAME,
2501 c_rec.DESCRIPTION,
2502 c_rec.MANAGER,
2503 c_rec.START_DATE,
2504 c_rec.END_DATE,
2505 c_rec.MANAGER_CONTACT,
2506 c_rec.SR_INSTANCE_ID,
2507 MSC_CL_COLLECTION.v_last_collection_id,
2508 MSC_CL_COLLECTION.v_current_date,
2509 MSC_CL_COLLECTION.v_current_user,
2510 MSC_CL_COLLECTION.v_current_date,
2511 MSC_CL_COLLECTION.v_current_user );
2512
2513 END IF;
2514
2515 c_count:= c_count+1;
2516
2517 IF c_count> MSC_CL_COLLECTION.PBS THEN
2518 COMMIT;
2519 c_count:= 0;
2520 END IF;
2521
2522 EXCEPTION
2523
2524 WHEN OTHERS THEN
2525
2526 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
2527
2528 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2529 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2530 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_PROJECT');
2531 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_PROJECT_TASKS');
2532 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2533
2534 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2535 RAISE;
2536
2537 ELSE
2538
2539 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2540
2541 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2542 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2543 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_PROJECT');
2544 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_PROJECT_TASKS');
2545 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2546
2547 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2548 FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
2549 FND_MESSAGE.SET_TOKEN('VALUE',
2550 MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
2551 MSC_CL_COLLECTION.v_instance_id));
2552 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2553
2554 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2555 FND_MESSAGE.SET_TOKEN('COLUMN', 'PROJECT_ID');
2556 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PROJECT_ID);
2557 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2558
2559 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2560 FND_MESSAGE.SET_TOKEN('COLUMN', 'TASK_NAME');
2561 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.TASK_NAME);
2562 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2563
2564 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2565 END IF;
2566
2567 END;
2568
2569 END LOOP;
2570
2571 COMMIT;
2572
2573 END LOAD_PROJECT;
2574
2575
2576 -- ====================== LOAD BIS ======================
2577
2578 PROCEDURE LOAD_BIS_PFMC_MEASURES IS
2579
2580 CURSOR c1 IS
2581 SELECT
2582 MEASURE_ID,
2583 MEASURE_SHORT_NAME,
2584 MEASURE_NAME,
2585 DESCRIPTION,
2586 ORG_DIMENSION_ID,
2587 TIME_DIMENSION_ID,
2588 DIMENSION1_ID,
2589 DIMENSION2_ID,
2590 DIMENSION3_ID,
2591 DIMENSION4_ID,
2592 DIMENSION5_ID,
2593 UNIT_OF_MEASURE_CLASS
2594 FROM MSC_ST_BIS_PFMC_MEASURES
2595 WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
2596
2597 c_count NUMBER:= 0;
2598
2599 BEGIN
2600
2601 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
2602
2603 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BIS_PERFORMANCE_MEASURES', MSC_CL_COLLECTION.v_instance_id, NULL);
2604
2605 END IF;
2606
2607 c_count:= 0;
2608
2609 FOR c_rec IN c1 LOOP
2610
2611 BEGIN
2612
2613 INSERT INTO MSC_BIS_PERFORMANCE_MEASURES
2614 ( MEASURE_ID,
2615 MEASURE_SHORT_NAME,
2616 MEASURE_NAME,
2617 DESCRIPTION,
2618 ORG_DIMENSION_ID,
2619 TIME_DIMENSION_ID,
2620 DIMENSION1_ID,
2621 DIMENSION2_ID,
2622 DIMENSION3_ID,
2623 DIMENSION4_ID,
2624 DIMENSION5_ID,
2625 UNIT_OF_MEASURE_CLASS,
2626 SR_INSTANCE_ID,
2627 LAST_UPDATE_DATE,
2628 LAST_UPDATED_BY,
2629 CREATION_DATE,
2630 CREATED_BY)
2631 VALUES
2632 ( c_rec.MEASURE_ID,
2633 c_rec.MEASURE_SHORT_NAME,
2634 c_rec.MEASURE_NAME,
2635 c_rec.DESCRIPTION,
2636 c_rec.ORG_DIMENSION_ID,
2637 c_rec.TIME_DIMENSION_ID,
2638 c_rec.DIMENSION1_ID,
2639 c_rec.DIMENSION2_ID,
2640 c_rec.DIMENSION3_ID,
2641 c_rec.DIMENSION4_ID,
2642 c_rec.DIMENSION5_ID,
2643 c_rec.UNIT_OF_MEASURE_CLASS,
2644 MSC_CL_COLLECTION.v_instance_id,
2645 MSC_CL_COLLECTION.v_current_date,
2646 MSC_CL_COLLECTION.v_current_user,
2647 MSC_CL_COLLECTION.v_current_date,
2648 MSC_CL_COLLECTION.v_current_user );
2649
2650 c_count:= c_count+1;
2651
2652 IF c_count> MSC_CL_COLLECTION.PBS THEN
2653 COMMIT;
2654 c_count:= 0;
2655 END IF;
2656
2657 EXCEPTION
2658 WHEN OTHERS THEN
2659
2660 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
2661
2662 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2663 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2664 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BIS');
2665 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BIS_PERFORMANCE_MEASURES');
2666 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2667
2668 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2669 RAISE;
2670
2671 ELSE
2672 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2673
2674 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2675 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2676 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BIS');
2677 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BIS_PERFORMANCE_MEASURES');
2678 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2679
2680 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2681 FND_MESSAGE.SET_TOKEN('COLUMN', 'MEASURE_ID');
2682 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.MEASURE_ID));
2683 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2684
2685 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2686 FND_MESSAGE.SET_TOKEN('COLUMN', 'ORG_DIMENSION_ID');
2687 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.ORG_DIMENSION_ID);
2688 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2689
2690 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2691 FND_MESSAGE.SET_TOKEN('COLUMN', 'TIME_DIMENSION_ID');
2692 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.TIME_DIMENSION_ID);
2693 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2694
2695 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2696 END IF;
2697
2698 END;
2699
2700 END LOOP;
2701
2702 COMMIT;
2703
2704 END LOAD_BIS_PFMC_MEASURES;
2705
2706
2707 PROCEDURE LOAD_BIS_TARGET_LEVELS IS
2708
2709 CURSOR c2 IS
2710 SELECT
2711 TARGET_LEVEL_ID,
2712 TARGET_LEVEL_SHORT_NAME,
2713 TARGET_LEVEL_NAME,
2714 DESCRIPTION,
2715 MEASURE_ID,
2716 ORG_LEVEL_ID,
2717 TIME_LEVEL_ID,
2718 DIMENSION1_LEVEL_ID,
2719 DIMENSION2_LEVEL_ID,
2720 DIMENSION3_LEVEL_ID,
2721 DIMENSION4_LEVEL_ID,
2722 DIMENSION5_LEVEL_ID,
2723 WORKFLOW_ITEM_TYPE,
2724 WORKFLOW_PROCESS_SHORT_NAME,
2725 DEFAULT_NOTIFY_RESP_ID,
2726 DEFAULT_NOTIFY_RESP_SHORT_NAME,
2727 COMPUTING_FUNCTION_ID,
2728 REPORT_FUNCTION_ID,
2729 UNIT_OF_MEASURE,
2730 SYSTEM_FLAG
2731 FROM MSC_ST_BIS_TARGET_LEVELS
2732 WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
2733
2734 c_count NUMBER:= 0;
2735
2736 BEGIN
2737
2738 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
2739
2740 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BIS_TARGET_LEVELS', MSC_CL_COLLECTION.v_instance_id, NULL);
2741
2742 END IF;
2743
2744 c_count:= 0;
2745
2746 FOR c_rec IN c2 LOOP
2747
2748 BEGIN
2749
2750 INSERT INTO MSC_BIS_TARGET_LEVELS
2751 ( TARGET_LEVEL_ID,
2752 TARGET_LEVEL_SHORT_NAME,
2753 TARGET_LEVEL_NAME,
2754 DESCRIPTION,
2755 MEASURE_ID,
2756 ORG_LEVEL_ID,
2757 TIME_LEVEL_ID,
2758 DIMENSION1_LEVEL_ID,
2759 DIMENSION2_LEVEL_ID,
2760 DIMENSION3_LEVEL_ID,
2761 DIMENSION4_LEVEL_ID,
2762 DIMENSION5_LEVEL_ID,
2763 WORKFLOW_ITEM_TYPE,
2764 WORKFLOW_PROCESS_SHORT_NAME,
2765 DEFAULT_NOTIFY_RESP_ID,
2766 DEFAULT_NOTIFY_RESP_SHORT_NAME,
2767 COMPUTING_FUNCTION_ID,
2768 REPORT_FUNCTION_ID,
2769 UNIT_OF_MEASURE,
2770 SYSTEM_FLAG,
2771 SR_INSTANCE_ID,
2772 LAST_UPDATE_DATE,
2773 LAST_UPDATED_BY,
2774 CREATION_DATE,
2775 CREATED_BY)
2776 VALUES
2777 ( c_rec.TARGET_LEVEL_ID,
2778 c_rec.TARGET_LEVEL_SHORT_NAME,
2779 c_rec.TARGET_LEVEL_NAME,
2780 c_rec.DESCRIPTION,
2781 c_rec.MEASURE_ID,
2782 c_rec.ORG_LEVEL_ID,
2783 c_rec.TIME_LEVEL_ID,
2784 c_rec.DIMENSION1_LEVEL_ID,
2785 c_rec.DIMENSION2_LEVEL_ID,
2786 c_rec.DIMENSION3_LEVEL_ID,
2787 c_rec.DIMENSION4_LEVEL_ID,
2788 c_rec.DIMENSION5_LEVEL_ID,
2789 c_rec.WORKFLOW_ITEM_TYPE,
2790 c_rec.WORKFLOW_PROCESS_SHORT_NAME,
2791 c_rec.DEFAULT_NOTIFY_RESP_ID,
2792 c_rec.DEFAULT_NOTIFY_RESP_SHORT_NAME,
2793 c_rec.COMPUTING_FUNCTION_ID,
2794 c_rec.REPORT_FUNCTION_ID,
2795 c_rec.UNIT_OF_MEASURE,
2796 c_rec.SYSTEM_FLAG,
2797 MSC_CL_COLLECTION.v_instance_id,
2798 MSC_CL_COLLECTION.v_current_date,
2799 MSC_CL_COLLECTION.v_current_user,
2800 MSC_CL_COLLECTION.v_current_date,
2801 MSC_CL_COLLECTION.v_current_user );
2802
2803 c_count:= c_count+1;
2804
2805 IF c_count> MSC_CL_COLLECTION.PBS THEN
2806 COMMIT;
2807 c_count:= 0;
2808 END IF;
2809
2810 EXCEPTION
2811 WHEN OTHERS THEN
2812
2813 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
2814
2815 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2816 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2817 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BIS');
2818 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BIS_TARGET_LEVELS');
2819 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2820
2821 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2822 RAISE;
2823
2824 ELSE
2825
2826 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2827
2828 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2829 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2830 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BIS');
2831 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BIS_TARGET_LEVELS');
2832 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2833
2834 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2835 FND_MESSAGE.SET_TOKEN('COLUMN', 'TARGET_LEVEL_ID');
2836 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.TARGET_LEVEL_ID);
2837 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2838
2839 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2840 FND_MESSAGE.SET_TOKEN('COLUMN', 'MEASURE_ID');
2841 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.MEASURE_ID));
2842 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2843
2844 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2845 FND_MESSAGE.SET_TOKEN('COLUMN', 'ORG_LEVEL_ID');
2846 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.ORG_LEVEL_ID);
2847 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2848
2849 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2850 FND_MESSAGE.SET_TOKEN('COLUMN', 'TIME_LEVEL_ID');
2851 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.TIME_LEVEL_ID);
2852 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2853
2854 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2855 END IF;
2856
2857 END;
2858
2859 END LOOP;
2860
2861 COMMIT;
2862
2863 END LOAD_BIS_TARGET_LEVELS;
2864
2865
2866 PROCEDURE LOAD_BIS_TARGETS IS
2867
2868 CURSOR c3 IS
2869 SELECT
2870 TARGET_ID,
2871 TARGET_LEVEL_ID,
2872 BUSINESS_PLAN_ID,
2873 ORG_LEVEL_VALUE_ID,
2874 TIME_LEVEL_VALUE_ID,
2875 DIM1_LEVEL_VALUE_ID,
2876 DIM2_LEVEL_VALUE_ID,
2877 DIM3_LEVEL_VALUE_ID,
2878 DIM4_LEVEL_VALUE_ID,
2879 DIM5_LEVEL_VALUE_ID,
2880 TARGET,
2881 RANGE1_LOW,
2882 RANGE1_HIGH,
2883 RANGE2_LOW,
2884 RANGE2_HIGH,
2885 RANGE3_LOW,
2886 RANGE3_HIGH,
2887 NOTIFY_RESP1_ID,
2888 NOTIFY_RESP1_SHORT_NAME,
2889 NOTIFY_RESP2_ID,
2890 NOTIFY_RESP2_SHORT_NAME,
2891 NOTIFY_RESP3_ID,
2892 NOTIFY_RESP3_SHORT_NAME
2893 FROM MSC_ST_BIS_TARGETS
2894 WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
2895
2896 c_count NUMBER:= 0;
2897
2898 BEGIN
2899
2900 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
2901
2902 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BIS_TARGETS', MSC_CL_COLLECTION.v_instance_id, NULL);
2903
2904 c_count:= 0;
2905
2906 FOR c_rec IN c3 LOOP
2907
2908 BEGIN
2909
2910 INSERT INTO MSC_BIS_TARGETS
2911 ( TARGET_ID,
2912 TARGET_LEVEL_ID,
2913 BUSINESS_PLAN_ID,
2914 ORG_LEVEL_VALUE_ID,
2915 TIME_LEVEL_VALUE_ID,
2916 DIM1_LEVEL_VALUE_ID,
2917 DIM2_LEVEL_VALUE_ID,
2918 DIM3_LEVEL_VALUE_ID,
2919 DIM4_LEVEL_VALUE_ID,
2920 DIM5_LEVEL_VALUE_ID,
2921 TARGET,
2922 RANGE1_LOW,
2923 RANGE1_HIGH,
2924 RANGE2_LOW,
2925 RANGE2_HIGH,
2926 RANGE3_LOW,
2927 RANGE3_HIGH,
2928 NOTIFY_RESP1_ID,
2929 NOTIFY_RESP1_SHORT_NAME,
2930 NOTIFY_RESP2_ID,
2931 NOTIFY_RESP2_SHORT_NAME,
2932 NOTIFY_RESP3_ID,
2933 NOTIFY_RESP3_SHORT_NAME,
2934 SR_INSTANCE_ID,
2935 LAST_UPDATE_DATE,
2936 LAST_UPDATED_BY,
2937 CREATION_DATE,
2938 CREATED_BY)
2939 VALUES
2940 ( c_rec.TARGET_ID,
2941 c_rec.TARGET_LEVEL_ID,
2942 c_rec.BUSINESS_PLAN_ID,
2943 c_rec.ORG_LEVEL_VALUE_ID,
2944 c_rec.TIME_LEVEL_VALUE_ID,
2945 c_rec.DIM1_LEVEL_VALUE_ID,
2946 c_rec.DIM2_LEVEL_VALUE_ID,
2947 c_rec.DIM3_LEVEL_VALUE_ID,
2948 c_rec.DIM4_LEVEL_VALUE_ID,
2949 c_rec.DIM5_LEVEL_VALUE_ID,
2950 c_rec.TARGET,
2951 c_rec.RANGE1_LOW,
2952 c_rec.RANGE1_HIGH,
2953 c_rec.RANGE2_LOW,
2954 c_rec.RANGE2_HIGH,
2955 c_rec.RANGE3_LOW,
2956 c_rec.RANGE3_HIGH,
2957 c_rec.NOTIFY_RESP1_ID,
2958 c_rec.NOTIFY_RESP1_SHORT_NAME,
2959 c_rec.NOTIFY_RESP2_ID,
2960 c_rec.NOTIFY_RESP2_SHORT_NAME,
2961 c_rec.NOTIFY_RESP3_ID,
2962 c_rec.NOTIFY_RESP3_SHORT_NAME,
2963 MSC_CL_COLLECTION.v_instance_id,
2964 MSC_CL_COLLECTION.v_current_date,
2965 MSC_CL_COLLECTION.v_current_user,
2966 MSC_CL_COLLECTION.v_current_date,
2967 MSC_CL_COLLECTION.v_current_user );
2968
2969 c_count:= c_count+1;
2970
2971 IF c_count> MSC_CL_COLLECTION.PBS THEN
2972 COMMIT;
2973 c_count:= 0;
2974 END IF;
2975
2976 EXCEPTION
2977 WHEN OTHERS THEN
2978
2979 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
2980
2981 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2982 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2983 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BIS');
2984 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BIS_TARGETS');
2985 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2986
2987 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2988 RAISE;
2989
2990 ELSE
2991 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2992
2993 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2994 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2995 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BIS');
2996 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BIS_TARGETS');
2997 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2998
2999 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3000 FND_MESSAGE.SET_TOKEN('COLUMN', 'TARGET_ID');
3001 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.TARGET_ID));
3002 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3003
3004 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3005 FND_MESSAGE.SET_TOKEN('COLUMN', 'TARGET_LEVEL_ID');
3006 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.TARGET_LEVEL_ID));
3007 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3008
3009 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3010 FND_MESSAGE.SET_TOKEN('COLUMN', 'BUSINESS_PLAN_ID');
3011 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.BUSINESS_PLAN_ID));
3012 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3013
3014 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3015 FND_MESSAGE.SET_TOKEN('COLUMN', 'ORG_LEVEL_ID');
3016 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.ORG_LEVEL_VALUE_ID);
3017 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3018
3019 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3020 FND_MESSAGE.SET_TOKEN('COLUMN', 'TIME_LEVEL_ID');
3021 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.TIME_LEVEL_VALUE_ID);
3022 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3023
3024 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3025 END IF;
3026
3027 END;
3028
3029 END LOOP;
3030
3031 END IF;
3032
3033 COMMIT;
3034
3035 END LOAD_BIS_TARGETS;
3036
3037
3038 PROCEDURE LOAD_BIS_BUSINESS_PLANS IS
3039
3040 CURSOR c4 IS
3041 SELECT
3042 BUSINESS_PLAN_ID,
3043 SHORT_NAME,
3044 NAME,
3045 DESCRIPTION,
3046 VERSION_NO,
3047 CURRENT_PLAN_FLAG
3048 FROM MSC_ST_BIS_BUSINESS_PLANS
3049 WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
3050
3051 c_count NUMBER:= 0;
3052
3053 BEGIN
3054
3055 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
3056
3057 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BIS_BUSINESS_PLANS', MSC_CL_COLLECTION.v_instance_id, NULL);
3058
3059 END IF;
3060
3061 c_count:= 0;
3062
3063 FOR c_rec IN c4 LOOP
3064
3065 BEGIN
3066 INSERT INTO MSC_BIS_BUSINESS_PLANS
3067 ( BUSINESS_PLAN_ID,
3068 SHORT_NAME,
3069 NAME,
3070 DESCRIPTION,
3071 VERSION_NO,
3072 CURRENT_PLAN_FLAG,
3073 SR_INSTANCE_ID,
3074 LAST_UPDATE_DATE,
3075 LAST_UPDATED_BY,
3076 CREATION_DATE,
3077 CREATED_BY)
3078 VALUES
3079 ( c_rec.BUSINESS_PLAN_ID,
3080 c_rec.SHORT_NAME,
3081 c_rec.NAME,
3082 c_rec.DESCRIPTION,
3083 c_rec.VERSION_NO,
3084 c_rec.CURRENT_PLAN_FLAG,
3085 MSC_CL_COLLECTION.v_instance_id,
3086 MSC_CL_COLLECTION.v_current_date,
3087 MSC_CL_COLLECTION.v_current_user,
3088 MSC_CL_COLLECTION.v_current_date,
3089 MSC_CL_COLLECTION.v_current_user );
3090
3091 c_count:= c_count+1;
3092
3093 IF c_count> MSC_CL_COLLECTION.PBS THEN
3094 COMMIT;
3095 c_count:= 0;
3096 END IF;
3097
3098 EXCEPTION
3099 WHEN OTHERS THEN
3100
3101 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
3102
3103 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3104 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3105 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BIS');
3106 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BIS_BUSINESS_PLANS');
3107 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3108
3109 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3110 RAISE;
3111
3112 ELSE
3113
3114 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3115
3116 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3117 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3118 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BIS');
3119 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BIS_BUSINESS_PLANS');
3120 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3121
3122 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3123 FND_MESSAGE.SET_TOKEN('COLUMN', 'BUSINESS_PLAN_ID');
3124 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.BUSINESS_PLAN_ID));
3125 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3126
3127 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3128 FND_MESSAGE.SET_TOKEN('COLUMN', 'VERSION_NO');
3129 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.VERSION_NO));
3130 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3131
3132 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3133 END IF;
3134
3135 END;
3136
3137 END LOOP;
3138
3139 COMMIT;
3140
3141 END LOAD_BIS_BUSINESS_PLANS;
3142
3143
3144 PROCEDURE LOAD_BIS_PERIODS IS
3145
3146 CURSOR c5 IS
3147 SELECT
3148 ORGANIZATION_ID,
3149 PERIOD_SET_NAME,
3150 PERIOD_NAME,
3151 START_DATE,
3152 END_DATE,
3153 PERIOD_TYPE,
3154 PERIOD_YEAR,
3155 PERIOD_NUM,
3156 QUARTER_NUM,
3157 ENTERED_PERIOD_NAME,
3158 ADJUSTMENT_PERIOD_FLAG,
3159 DESCRIPTION,
3160 CONTEXT,
3161 YEAR_START_DATE,
3162 QUARTER_START_DATE
3163 FROM MSC_ST_BIS_PERIODS
3164 WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
3165
3166 c_count NUMBER:= 0;
3167
3168 /************** LEGACY_CHANGE_START*************************/
3169 lv_rec_count NUMBER:= 0;
3170
3171 /*****************LEGACY_CHANGE_ENDS************************/
3172 BEGIN
3173
3174 /************** LEGACY_CHANGE_START*************************/
3175 BEGIN
3176
3177
3178 SELECT 1
3179 INTO lv_rec_count
3180 FROM dual
3181 WHERE EXISTS(SELECT 1
3182 FROM msc_st_bis_periods
3183 WHERE sr_instance_id = MSC_CL_COLLECTION.v_instance_id);
3184 EXCEPTION
3185 WHEN NO_DATA_FOUND THEN
3186 lv_rec_count := 0;
3187 END;
3188
3189 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) or
3190 (MSC_CL_COLLECTION.v_instance_type = MSC_UTIL.G_INS_OTHER AND lv_rec_count > 0) THEN
3191 --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BIS_PERIODS', MSC_CL_COLLECTION.v_instance_id, NULL);
3192
3193 IF (MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS) OR (MSC_CL_COLLECTION.v_coll_prec.org_group_flag IS NULL) THEN
3194 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BIS_PERIODS', MSC_CL_COLLECTION.v_instance_id,NULL);
3195 ELSE
3196 MSC_CL_COLLECTION.v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
3197 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BIS_PERIODS', MSC_CL_COLLECTION.v_instance_id,NULL,MSC_CL_COLLECTION.v_sub_str);
3198 END IF;
3199
3200 END IF;
3201
3202 /*****************LEGACY_CHANGE_ENDS************************/
3203 c_count:= 0;
3204
3205 FOR c_rec IN c5 LOOP
3206
3207 BEGIN
3208
3209 INSERT INTO MSC_BIS_PERIODS
3210 ( ORGANIZATION_ID,
3211 PERIOD_SET_NAME,
3212 PERIOD_NAME,
3213 START_DATE,
3214 END_DATE,
3215 PERIOD_TYPE,
3216 PERIOD_YEAR,
3217 PERIOD_NUM,
3218 QUARTER_NUM,
3219 ENTERED_PERIOD_NAME,
3220 ADJUSTMENT_PERIOD_FLAG,
3221 DESCRIPTION,
3222 CONTEXT,
3223 YEAR_START_DATE,
3224 QUARTER_START_DATE,
3225 SR_INSTANCE_ID,
3226 LAST_UPDATE_DATE,
3227 LAST_UPDATED_BY,
3228 CREATION_DATE,
3229 CREATED_BY)
3230 VALUES
3231 ( c_rec.ORGANIZATION_ID,
3232 c_rec.PERIOD_SET_NAME,
3233 c_rec.PERIOD_NAME,
3234 c_rec.START_DATE,
3235 c_rec.END_DATE,
3236 c_rec.PERIOD_TYPE,
3237 c_rec.PERIOD_YEAR,
3238 c_rec.PERIOD_NUM,
3239 c_rec.QUARTER_NUM,
3240 c_rec.ENTERED_PERIOD_NAME,
3241 c_rec.ADJUSTMENT_PERIOD_FLAG,
3242 c_rec.DESCRIPTION,
3243 c_rec.CONTEXT,
3244 c_rec.YEAR_START_DATE,
3245 c_rec.QUARTER_START_DATE,
3246 MSC_CL_COLLECTION.v_instance_id,
3247 MSC_CL_COLLECTION.v_current_date,
3248 MSC_CL_COLLECTION.v_current_user,
3249 MSC_CL_COLLECTION.v_current_date,
3250 MSC_CL_COLLECTION.v_current_user );
3251
3252 c_count:= c_count+1;
3253
3254 IF c_count> MSC_CL_COLLECTION.PBS THEN
3255 COMMIT;
3256 c_count:= 0;
3257 END IF;
3258
3259 EXCEPTION
3260
3261 WHEN OTHERS THEN
3262
3263 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
3264
3265 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3266 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3267 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BIS');
3268 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BIS_PERIODS');
3269 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3270
3271 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3272 RAISE;
3273
3274 ELSE
3275 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3276
3277 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3278 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3279 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BIS');
3280 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BIS_PERIODS');
3281 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3282
3283 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3284 FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
3285 FND_MESSAGE.SET_TOKEN('VALUE',
3286 MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
3287 MSC_CL_COLLECTION.v_instance_id));
3288 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3289
3290 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3291 FND_MESSAGE.SET_TOKEN('COLUMN', 'PERIOD_SET_NAME');
3292 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PERIOD_SET_NAME);
3293 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3294
3295 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3296 FND_MESSAGE.SET_TOKEN('COLUMN', 'PERIOD_NAME');
3297 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PERIOD_NAME);
3298 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3299
3300 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3301 END IF;
3302
3303 END;
3304
3305 END LOOP;
3306
3307 COMMIT;
3308
3309 END LOAD_BIS_PERIODS;
3310
3311
3312 -- ============ LOAD_ATP_RULES =================
3313
3314 PROCEDURE LOAD_ATP_RULES IS
3315
3316 CURSOR c1 IS
3317 SELECT
3318 RULE_ID,
3319 RULE_NAME,
3320 DESCRIPTION,
3321 ACCUMULATE_AVAILABLE_FLAG,
3322 BACKWARD_CONSUMPTION_FLAG,
3323 FORWARD_CONSUMPTION_FLAG,
3324 PAST_DUE_DEMAND_CUTOFF_FENCE,
3325 PAST_DUE_SUPPLY_CUTOFF_FENCE,
3326 INFINITE_SUPPLY_FENCE_CODE,
3327 INFINITE_SUPPLY_TIME_FENCE,
3328 ACCEPTABLE_EARLY_FENCE,
3329 ACCEPTABLE_LATE_FENCE,
3330 DEFAULT_ATP_SOURCES,
3331 DEMAND_CLASS_ATP_FLAG,
3332 INCLUDE_SALES_ORDERS,
3333 INCLUDE_DISCRETE_WIP_DEMAND,
3334 INCLUDE_REP_WIP_DEMAND,
3335 INCLUDE_NONSTD_WIP_DEMAND,
3336 INCLUDE_DISCRETE_MPS,
3337 INCLUDE_USER_DEFINED_DEMAND,
3338 INCLUDE_PURCHASE_ORDERS,
3339 INCLUDE_DISCRETE_WIP_RECEIPTS,
3340 INCLUDE_REP_WIP_RECEIPTS,
3341 INCLUDE_NONSTD_WIP_RECEIPTS,
3342 INCLUDE_INTERORG_TRANSFERS,
3343 INCLUDE_ONHAND_AVAILABLE,
3344 INCLUDE_USER_DEFINED_SUPPLY,
3345 ACCUMULATION_WINDOW,
3346 INCLUDE_REP_MPS,
3347 INCLUDE_INTERNAL_REQS,
3348 INCLUDE_SUPPLIER_REQS,
3349 INCLUDE_INTERNAL_ORDERS,
3350 INCLUDE_FLOW_SCHEDULE_DEMAND,
3351 INCLUDE_FLOW_SCHEDULE_RECEIPTS,
3352 USER_ATP_SUPPLY_TABLE_NAME,
3353 USER_ATP_DEMAND_TABLE_NAME,
3354 MPS_DESIGNATOR,
3355 AGGREGATE_TIME_FENCE_CODE,
3356 AGGREGATE_TIME_FENCE
3357 FROM MSC_ST_ATP_RULES
3358 WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
3359
3360 c_count NUMBER:= 0;
3361
3362 BEGIN
3363
3364 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
3365
3366 DELETE MSC_ATP_RULES
3367 WHERE sr_instance_id= MSC_CL_COLLECTION.v_instance_id;
3368
3369 -- MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ATP_RULES', MSC_CL_COLLECTION.v_instance_id, NULL);
3370
3371 END IF;
3372
3373 c_count:= 0;
3374
3375 FOR c_rec IN c1 LOOP
3376
3377 BEGIN
3378
3379 INSERT INTO MSC_ATP_RULES
3380 ( RULE_ID,
3381 RULE_NAME,
3382 DESCRIPTION,
3383 ACCUMULATE_AVAILABLE_FLAG,
3384 BACKWARD_CONSUMPTION_FLAG,
3385 FORWARD_CONSUMPTION_FLAG,
3386 PAST_DUE_DEMAND_CUTOFF_FENCE,
3387 PAST_DUE_SUPPLY_CUTOFF_FENCE,
3388 INFINITE_SUPPLY_FENCE_CODE,
3389 INFINITE_SUPPLY_TIME_FENCE,
3390 ACCEPTABLE_EARLY_FENCE,
3391 ACCEPTABLE_LATE_FENCE,
3392 DEFAULT_ATP_SOURCES,
3393 DEMAND_CLASS_ATP_FLAG,
3394 INCLUDE_SALES_ORDERS,
3395 INCLUDE_DISCRETE_WIP_DEMAND,
3396 INCLUDE_REP_WIP_DEMAND,
3397 INCLUDE_NONSTD_WIP_DEMAND,
3398 INCLUDE_DISCRETE_MPS,
3399 INCLUDE_USER_DEFINED_DEMAND,
3400 INCLUDE_PURCHASE_ORDERS,
3401 INCLUDE_DISCRETE_WIP_RECEIPTS,
3402 INCLUDE_REP_WIP_RECEIPTS,
3403 INCLUDE_NONSTD_WIP_RECEIPTS,
3404 INCLUDE_INTERORG_TRANSFERS,
3405 INCLUDE_ONHAND_AVAILABLE,
3406 INCLUDE_USER_DEFINED_SUPPLY,
3407 ACCUMULATION_WINDOW,
3408 INCLUDE_REP_MPS,
3409 INCLUDE_INTERNAL_REQS,
3410 INCLUDE_SUPPLIER_REQS,
3411 INCLUDE_INTERNAL_ORDERS,
3412 INCLUDE_FLOW_SCHEDULE_DEMAND,
3413 INCLUDE_FLOW_SCHEDULE_RECEIPTS,
3414 USER_ATP_SUPPLY_TABLE_NAME,
3415 USER_ATP_DEMAND_TABLE_NAME,
3416 MPS_DESIGNATOR,
3417 AGGREGATE_TIME_FENCE_CODE,
3418 AGGREGATE_TIME_FENCE,
3419 SR_INSTANCE_ID,
3420 LAST_UPDATE_DATE,
3421 LAST_UPDATED_BY,
3422 CREATION_DATE,
3423 CREATED_BY)
3424 VALUES
3425 ( c_rec.RULE_ID,
3426 c_rec.RULE_NAME,
3427 c_rec.DESCRIPTION,
3428 c_rec.ACCUMULATE_AVAILABLE_FLAG,
3429 c_rec.BACKWARD_CONSUMPTION_FLAG,
3430 c_rec.FORWARD_CONSUMPTION_FLAG,
3431 c_rec.PAST_DUE_DEMAND_CUTOFF_FENCE,
3432 c_rec.PAST_DUE_SUPPLY_CUTOFF_FENCE,
3433 c_rec.INFINITE_SUPPLY_FENCE_CODE,
3434 c_rec.INFINITE_SUPPLY_TIME_FENCE,
3435 c_rec.ACCEPTABLE_EARLY_FENCE,
3436 c_rec.ACCEPTABLE_LATE_FENCE,
3437 c_rec.DEFAULT_ATP_SOURCES,
3438 c_rec.DEMAND_CLASS_ATP_FLAG,
3439 c_rec.INCLUDE_SALES_ORDERS,
3440 c_rec.INCLUDE_DISCRETE_WIP_DEMAND,
3441 c_rec.INCLUDE_REP_WIP_DEMAND,
3442 c_rec.INCLUDE_NONSTD_WIP_DEMAND,
3443 c_rec.INCLUDE_DISCRETE_MPS,
3444 c_rec.INCLUDE_USER_DEFINED_DEMAND,
3445 c_rec.INCLUDE_PURCHASE_ORDERS,
3446 c_rec.INCLUDE_DISCRETE_WIP_RECEIPTS,
3447 c_rec.INCLUDE_REP_WIP_RECEIPTS,
3448 c_rec.INCLUDE_NONSTD_WIP_RECEIPTS,
3449 c_rec.INCLUDE_INTERORG_TRANSFERS,
3450 c_rec.INCLUDE_ONHAND_AVAILABLE,
3451 c_rec.INCLUDE_USER_DEFINED_SUPPLY,
3452 c_rec.ACCUMULATION_WINDOW,
3453 c_rec.INCLUDE_REP_MPS,
3454 c_rec.INCLUDE_INTERNAL_REQS,
3455 c_rec.INCLUDE_SUPPLIER_REQS,
3456 c_rec.INCLUDE_INTERNAL_ORDERS,
3457 c_rec.INCLUDE_FLOW_SCHEDULE_DEMAND,
3458 c_rec.INCLUDE_FLOW_SCHEDULE_RECEIPTS,
3459 c_rec.USER_ATP_SUPPLY_TABLE_NAME,
3460 c_rec.USER_ATP_DEMAND_TABLE_NAME,
3461 c_rec.MPS_DESIGNATOR,
3462 c_rec.AGGREGATE_TIME_FENCE_CODE,
3463 c_rec.AGGREGATE_TIME_FENCE,
3464 MSC_CL_COLLECTION.v_instance_id,
3465 MSC_CL_COLLECTION.v_current_date,
3466 MSC_CL_COLLECTION.v_current_user,
3467 MSC_CL_COLLECTION.v_current_date,
3468 MSC_CL_COLLECTION.v_current_user );
3469 /*
3470 c_count:= c_count+1;
3471
3472 IF c_count> MSC_CL_COLLECTION.PBS THEN
3473 COMMIT;
3474 c_count:= 0;
3475 END IF;
3476 */
3477 EXCEPTION
3478
3479 WHEN OTHERS THEN
3480
3481 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
3482
3483 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3484 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3485 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ATP_RULES');
3486 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ATP_RULES');
3487 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3488
3489 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3490 RAISE;
3491
3492 ELSE
3493 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3494
3495 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3496 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3497 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ATP_RULES');
3498 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ATP_RULES');
3499 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3500
3501 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3502 FND_MESSAGE.SET_TOKEN('COLUMN', 'RULE_ID');
3503 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.RULE_ID));
3504 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3505
3506 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3507 FND_MESSAGE.SET_TOKEN('COLUMN', 'RULE_NAME');
3508 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.RULE_NAME);
3509 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3510
3511 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3512 END IF;
3513
3514 END;
3515
3516 END LOOP;
3517
3518 COMMIT;
3519
3520 END LOAD_ATP_RULES;
3521
3522
3523 -- ============= PLANNERS ===================
3524 PROCEDURE LOAD_PLANNERS IS
3525
3526 CURSOR c1 IS
3527 SELECT
3528 x.PLANNER_CODE,
3529 x.ORGANIZATION_ID,
3530 x.DESCRIPTION,
3531 x.DISABLE_DATE,
3532 x.ELECTRONIC_MAIL_ADDRESS,
3533 x.EMPLOYEE_ID,
3534 x.CURRENT_EMPLOYEE_FLAG,
3535 x.USER_NAME
3536 FROM MSC_ST_PLANNERS x
3537 WHERE x.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
3538
3539 /* added this cursor for bug: 1121172 */
3540 CURSOR c2 IS
3541 SELECT x.USER_NAME,
3542 x.ELECTRONIC_MAIL_ADDRESS,
3543 x.EMPLOYEE_ID,
3544 x.ORGANIZATION_ID
3545 FROM MSC_ST_PLANNERS x,
3546 FND_USER y
3547 WHERE UPPER(x.USER_NAME) = y.USER_NAME
3548 AND x.CURRENT_EMPLOYEE_FLAG = 1
3549 AND x.EMPLOYEE_ID IS NOT NULL
3550 AND x.ELECTRONIC_MAIL_ADDRESS IS NOT NULL
3551 AND x.SR_INSTANCE_ID = MSC_CL_COLLECTION.v_instance_id;
3552
3553 c_count NUMBER:= 0;
3554
3555 BEGIN
3556
3557 IF (MSC_CL_COLLECTION.v_is_complete_refresh or MSC_CL_COLLECTION.v_is_partial_refresh) THEN
3558
3559 --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_PLANNERS', MSC_CL_COLLECTION.v_instance_id, NULL);
3560 IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
3561 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_PLANNERS', MSC_CL_COLLECTION.v_instance_id,NULL);
3562 ELSE
3563 MSC_CL_COLLECTION.v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
3564 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_PLANNERS', MSC_CL_COLLECTION.v_instance_id,NULL,MSC_CL_COLLECTION.v_sub_str);
3565 END IF;
3566
3567 c_count:= 0;
3568
3569 FOR c_rec IN c1 LOOP
3570
3571 BEGIN
3572
3573 INSERT INTO MSC_PLANNERS
3574 ( PLANNER_CODE,
3575 ORGANIZATION_ID,
3576 DESCRIPTION,
3577 DISABLE_DATE,
3578 ELECTRONIC_MAIL_ADDRESS,
3579 EMPLOYEE_ID,
3580 CURRENT_EMPLOYEE_FLAG,
3581 USER_NAME,
3582 SR_INSTANCE_ID,
3583 LAST_UPDATE_DATE,
3584 LAST_UPDATED_BY,
3585 CREATION_DATE,
3586 CREATED_BY)
3587 VALUES
3588 ( c_rec.PLANNER_CODE,
3589 c_rec.ORGANIZATION_ID,
3590 c_rec.DESCRIPTION,
3591 c_rec.DISABLE_DATE,
3592 c_rec.ELECTRONIC_MAIL_ADDRESS,
3593 c_rec.EMPLOYEE_ID,
3594 c_rec.CURRENT_EMPLOYEE_FLAG,
3595 c_rec.USER_NAME,
3596 MSC_CL_COLLECTION.v_instance_id,
3597 MSC_CL_COLLECTION.v_current_date,
3598 MSC_CL_COLLECTION.v_current_user,
3599 MSC_CL_COLLECTION.v_current_date,
3600 MSC_CL_COLLECTION.v_current_user );
3601
3602 c_count:= c_count+1;
3603
3604 IF c_count> MSC_CL_COLLECTION.PBS THEN
3605 COMMIT;
3606 c_count:= 0;
3607 END IF;
3608
3609 EXCEPTION
3610
3611 WHEN OTHERS THEN
3612
3613 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
3614
3615 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3616 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3617 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_PLANNERS');
3618 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_PLANNERS');
3619 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3620
3621 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3622 RAISE;
3623
3624 ELSE
3625 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3626
3627 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3628 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3629 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_PLANNERS');
3630 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_PLANNERS');
3631 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3632
3633 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3634 FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
3635 FND_MESSAGE.SET_TOKEN('VALUE',
3636 MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
3637 MSC_CL_COLLECTION.v_instance_id));
3638 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3639
3640 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3641 FND_MESSAGE.SET_TOKEN('COLUMN', 'PLANNER_CODE');
3642 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.PLANNER_CODE);
3643 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3644
3645 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3646 END IF;
3647
3648 END;
3649
3650 END LOOP;
3651
3652 COMMIT;
3653
3654 /* For Bug: 1121172, update the E-mail address of the planner*/
3655 FOR c_rec IN c2 LOOP
3656
3657 BEGIN
3658 UPDATE FND_USER
3659 SET EMAIL_ADDRESS = c_rec.ELECTRONIC_MAIL_ADDRESS
3660 WHERE USER_NAME = UPPER(c_rec.USER_NAME);
3661
3662 EXCEPTION
3663
3664 WHEN OTHERS THEN
3665
3666 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3667
3668 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3669 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3670 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_PLANNERS');
3671 FND_MESSAGE.SET_TOKEN('TABLE', 'FND_USER');
3672 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3673
3674 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3675 FND_MESSAGE.SET_TOKEN('COLUMN', 'USER_NAME');
3676 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.USER_NAME);
3677 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3678
3679 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3680
3681 END;
3682
3683 END LOOP;
3684
3685 COMMIT;
3686
3687 END IF;
3688
3689 END LOAD_PLANNERS;
3690
3691 -- ============= DEMAND_CLASS ===================
3692 PROCEDURE LOAD_DEMAND_CLASS IS
3693
3694 CURSOR c1 IS
3695 SELECT
3696 msrg.DEMAND_CLASS,
3697 msrg.MEANING,
3698 msrg.DESCRIPTION,
3699 msrg.FROM_DATE,
3700 msrg.TO_DATE,
3701 msrg.ENABLED_FLAG
3702 FROM MSC_ST_DEMAND_CLASSES msrg
3703 WHERE msrg.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
3704
3705 c_count NUMBER:= 0;
3706
3707 BEGIN
3708
3709 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
3710
3711 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_DEMAND_CLASSES', MSC_CL_COLLECTION.v_instance_id, NULL);
3712
3713 END IF;
3714
3715 -- IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
3716
3717 FOR c_rec IN c1 LOOP
3718
3719 BEGIN
3720
3721 IF MSC_CL_COLLECTION.v_is_legacy_refresh THEN
3722
3723 UPDATE MSC_DEMAND_CLASSES
3724 SET
3725 MEANING = c_rec.MEANING,
3726 DESCRIPTION = c_rec.DESCRIPTION,
3727 FROM_DATE = c_rec.FROM_DATE,
3728 TO_DATE = c_rec.TO_DATE,
3729 ENABLED_FLAG = c_rec.ENABLED_FLAG,
3730 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3731 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3732 WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3733 AND DEMAND_CLASS = c_rec.DEMAND_CLASS;
3734
3735 END IF;
3736
3737 IF (MSC_CL_COLLECTION.v_is_complete_refresh or MSC_CL_COLLECTION.v_is_partial_refresh) OR (MSC_CL_COLLECTION.v_is_legacy_refresh AND SQL%NOTFOUND) THEN
3738
3739 INSERT INTO MSC_DEMAND_CLASSES
3740 ( DEMAND_CLASS,
3741 MEANING,
3742 DESCRIPTION,
3743 FROM_DATE,
3744 TO_DATE,
3745 ENABLED_FLAG,
3746 SR_INSTANCE_ID,
3747 LAST_UPDATE_DATE,
3748 LAST_UPDATED_BY,
3749 CREATION_DATE,
3750 CREATED_BY)
3751 VALUES
3752 ( c_rec.DEMAND_CLASS,
3753 c_rec.MEANING,
3754 c_rec.DESCRIPTION,
3755 c_rec.FROM_DATE,
3756 c_rec.TO_DATE,
3757 c_rec.ENABLED_FLAG,
3758 MSC_CL_COLLECTION.v_instance_id,
3759 MSC_CL_COLLECTION.v_current_date,
3760 MSC_CL_COLLECTION.v_current_user,
3761 MSC_CL_COLLECTION.v_current_date,
3762 MSC_CL_COLLECTION.v_current_user );
3763
3764 END IF;
3765
3766 EXCEPTION
3767 WHEN OTHERS THEN
3768
3769 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
3770
3771 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3772 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3773 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_DEMAND_CLASS');
3774 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DEMAND_CLASSES');
3775 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3776
3777 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3778 RAISE;
3779
3780 ELSE
3781
3782 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3783
3784 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3785 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3786 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_DEMAND_CLASS');
3787 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DEMAND_CLASSES');
3788 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3789
3790 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3791 FND_MESSAGE.SET_TOKEN('COLUMN', 'DEMAND_CLASS');
3792 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.DEMAND_CLASS);
3793 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3794
3795 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3796 END IF;
3797
3798 END;
3799
3800 END LOOP;
3801
3802 COMMIT;
3803
3804 END LOAD_DEMAND_CLASS;
3805
3806 PROCEDURE LOAD_SALES_CHANNEL IS
3807 CURSOR c1 IS
3808 SELECT
3809 mssc.SALES_CHANNEL,
3810 mssc.MEANING,
3811 mssc.DESCRIPTION,
3812 mssc.FROM_DATE,
3813 mssc.TO_DATE,
3814 mssc.ENABLED_FLAG
3815 FROM MSC_ST_SALES_CHANNEL mssc
3816 WHERE mssc.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
3817
3818 c_count NUMBER:= 0;
3819
3820 BEGIN
3821 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
3822
3823 MSC_CL_COLLECTION.DELETE_MSC_TABLE ('MSC_SALES_CHANNEL', MSC_CL_COLLECTION.v_instance_id, NULL);
3824 END IF;
3825
3826 FOR c_rec IN c1 LOOP
3827
3828 BEGIN
3829
3830 IF MSC_CL_COLLECTION.v_is_legacy_refresh THEN
3831 UPDATE MSC_SR_LOOKUPS
3832 SET
3833 MEANING = c_rec.MEANING,
3834 DESCRIPTION = c_rec.DESCRIPTION,
3835 FROM_DATE = c_rec.FROM_DATE,
3836 TO_DATE = c_rec.TO_DATE,
3837 ENABLED_FLAG = c_rec.ENABLED_FLAG,
3838 REFRESH_NUMBER = MSC_CL_COLLECTION.v_last_collection_id,
3839 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3840 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3841 WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3842 AND LOOKUP_CODE = c_rec.SALES_CHANNEL
3843 AND LOOKUP_TYPE = 'SALES_CHANNEL';
3844
3845 END IF;
3846
3847 IF (MSC_CL_COLLECTION.v_is_complete_refresh or MSC_CL_COLLECTION.v_is_partial_refresh) OR (MSC_CL_COLLECTION.v_is_legacy_refresh AND SQL%NOTFOUND) THEN
3848 INSERT INTO MSC_SR_LOOKUPS (
3849 LOOKUP_TYPE,
3850 LOOKUP_CODE,
3851 MEANING,
3852 DESCRIPTION,
3853 FROM_DATE,
3854 TO_DATE,
3855 ENABLED_FLAG,
3856 REFRESH_NUMBER,
3857 SR_INSTANCE_ID,
3858 LAST_UPDATE_DATE,
3859 LAST_UPDATED_BY,
3860 CREATION_DATE,
3861 CREATED_BY)
3862 VALUES
3863 ('SALES_CHANNEL',
3864 c_rec.SALES_CHANNEL,
3865 c_rec.MEANING,
3866 c_rec.DESCRIPTION,
3867 c_rec.FROM_DATE,
3868 c_rec.TO_DATE,
3869 c_rec.ENABLED_FLAG,
3870 MSC_CL_COLLECTION.v_last_collection_id,
3871 MSC_CL_COLLECTION.v_instance_id,
3872 MSC_CL_COLLECTION.v_current_date,
3873 MSC_CL_COLLECTION.v_current_user,
3874 MSC_CL_COLLECTION.v_current_date,
3875 MSC_CL_COLLECTION.v_current_user);
3876
3877 END IF;
3878
3879 EXCEPTION
3880 WHEN OTHERS THEN
3881
3882 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'An error has occurred.');
3883 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3884 RAISE;
3885 END;
3886
3887 END LOOP;
3888
3889 COMMIT;
3890
3891 END LOAD_SALES_CHANNEL;
3892
3893
3894 PROCEDURE LOAD_FISCAL_CALENDAR IS
3895 CURSOR c1 IS
3896 SELECT
3897 mscm.CALENDAR_CODE,
3898 mscm.YEAR,
3899 mscm.YEAR_DESCRIPTION,
3900 mscm.YEAR_START_DATE,
3901 mscm.YEAR_END_DATE,
3902 mscm.QUARTER,
3903 mscm.QUARTER_DESCRIPTION,
3904 mscm.QUARTER_START_DATE,
3905 mscm.QUARTER_END_DATE,
3906 mscm.MONTH,
3907 mscm.MONTH_DESCRIPTION,
3908 mscm.MONTH_START_DATE,
3909 mscm.MONTH_END_DATE
3910 FROM MSC_ST_CALENDAR_MONTHS mscm
3911 WHERE mscm.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
3912
3913 c_count NUMBER:= 0;
3914 BEGIN
3915
3916 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
3917
3918 MSC_CL_COLLECTION.DELETE_MSC_TABLE ('MSC_CALENDARS', MSC_CL_COLLECTION.v_instance_id, Null, 'AND CALENDAR_TYPE=''FISCAL''');
3919 MSC_CL_COLLECTION.DELETE_MSC_TABLE ('MSC_CALENDAR_MONTHS', MSC_CL_COLLECTION.v_instance_id, NULL);
3920
3921 INSERT INTO MSC_CALENDARS
3922 (
3923 CALENDAR_CODE,
3924 CALENDAR_TYPE,
3925 REFRESH_ID,
3926 SR_INSTANCE_ID,
3927 LAST_UPDATE_DATE,
3928 LAST_UPDATED_BY,
3929 CREATION_DATE,
3930 CREATED_BY
3931 )
3932 SELECT
3933 DISTINCT
3934 CALENDAR_CODE,
3935 CALENDAR_TYPE,
3936 MSC_CL_COLLECTION.v_last_collection_id,
3937 MSC_CL_COLLECTION.v_instance_id,
3938 MSC_CL_COLLECTION.v_current_date,
3939 MSC_CL_COLLECTION.v_current_user,
3940 MSC_CL_COLLECTION.v_current_date,
3941 MSC_CL_COLLECTION.v_current_user
3942 FROM MSC_ST_CALENDAR_MONTHS
3943 WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3944 AND CALENDAR_TYPE = 'FISCAL';
3945
3946 COMMIT;
3947 END IF;
3948
3949 FOR c_rec IN c1 LOOP
3950 BEGIN
3951
3952 IF MSC_CL_COLLECTION.v_is_legacy_refresh THEN
3953
3954 UPDATE MSC_CALENDARS
3955 SET
3956 REFRESH_NUMBER = MSC_CL_COLLECTION.v_last_collection_id,
3957 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3958 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3959 WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3960 AND CALENDAR_CODE = c_rec.calendar_code
3961 AND CALENDAR_TYPE = 'FISCAL';
3962
3963 END IF;
3964
3965 IF (MSC_CL_COLLECTION.v_is_legacy_refresh AND SQL%NOTFOUND) THEN
3966 INSERT INTO MSC_CALENDARS
3967 ( CALENDAR_CODE,
3968 CALENDAR_TYPE,
3969 REFRESH_NUMBER,
3970 SR_INSTANCE_ID,
3971 LAST_UPDATE_DATE,
3972 LAST_UPDATED_BY,
3973 CREATION_DATE,
3974 CREATED_BY
3975 )
3976 VALUES
3977 ( c_rec.CALENDAR_CODE,
3978 'FISCAL',
3979 MSC_CL_COLLECTION.v_last_collection_id,
3980 MSC_CL_COLLECTION.v_instance_id,
3981 MSC_CL_COLLECTION.v_current_date,
3982 MSC_CL_COLLECTION.v_current_user,
3983 MSC_CL_COLLECTION.v_current_date,
3984 MSC_CL_COLLECTION.v_current_user
3985 );
3986
3987 END IF;
3988 EXCEPTION
3989 WHEN OTHERS THEN
3990
3991 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'An error has occurred.');
3992 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3993 RAISE;
3994 END;
3995
3996 BEGIN
3997
3998 IF MSC_CL_COLLECTION.v_is_legacy_refresh THEN
3999
4000 UPDATE MSC_CALENDAR_MONTHS
4001 SET
4002 YEAR = c_rec.YEAR,
4003 YEAR_DESCRIPTION = c_rec.YEAR_DESCRIPTION,
4004 YEAR_START_DATE = c_rec.YEAR_START_DATE,
4005 YEAR_END_DATE = c_rec.YEAR_END_DATE,
4006 QUARTER = c_rec.QUARTER,
4007 QUARTER_DESCRIPTION = c_rec.QUARTER_DESCRIPTION,
4008 QUARTER_START_DATE = c_rec.QUARTER_START_DATE,
4009 QUARTER_END_DATE = c_rec.QUARTER_END_DATE,
4010 MONTH = c_rec.MONTH,
4011 MONTH_DESCRIPTION = c_rec.MONTH_DESCRIPTION,
4012 MONTH_START_DATE = c_rec.MONTH_START_DATE,
4013 MONTH_END_DATE = c_rec.MONTH_END_DATE,
4014 REFRESH_NUMBER = MSC_CL_COLLECTION.v_last_collection_id,
4015 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
4016 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
4017 WHERE
4018 SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
4019 AND CALENDAR_CODE = c_rec.calendar_code
4020 AND CALENDAR_TYPE= 'FISCAL'
4021 AND YEAR = c_rec.YEAR
4022 AND MONTH = c_rec.MONTH;
4023
4024 END IF;
4025
4026
4027 IF (MSC_CL_COLLECTION.v_is_complete_refresh or MSC_CL_COLLECTION.v_is_partial_refresh) OR (MSC_CL_COLLECTION.v_is_legacy_refresh AND SQL%NOTFOUND) THEN
4028
4029 INSERT INTO MSC_CALENDAR_MONTHS (
4030 CALENDAR_CODE,
4031 CALENDAR_TYPE,
4032 YEAR,
4033 YEAR_DESCRIPTION,
4034 YEAR_START_DATE,
4035 YEAR_END_DATE,
4036 QUARTER,
4037 QUARTER_DESCRIPTION,
4038 QUARTER_START_DATE,
4039 QUARTER_END_DATE,
4040 MONTH,
4041 MONTH_DESCRIPTION,
4042 MONTH_START_DATE,
4043 MONTH_END_DATE,
4044 REFRESH_NUMBER,
4045 SR_INSTANCE_ID,
4046 LAST_UPDATE_DATE,
4047 LAST_UPDATED_BY,
4048 CREATION_DATE,
4049 CREATED_BY,
4050 LAST_UPDATE_LOGIN)
4051 Values (
4052 c_rec.CALENDAR_CODE,
4053 'FISCAL',
4054 c_rec.YEAR,
4055 c_rec.YEAR_DESCRIPTION,
4056 c_rec.YEAR_START_DATE,
4057 c_rec.YEAR_END_DATE,
4058 c_rec.QUARTER,
4059 c_rec.QUARTER_DESCRIPTION,
4060 c_rec.QUARTER_START_DATE,
4061 c_rec.QUARTER_END_DATE,
4062 c_rec.MONTH,
4063 c_rec.MONTH_DESCRIPTION,
4064 c_rec.MONTH_START_DATE,
4065 c_rec.MONTH_END_DATE,
4066 MSC_CL_COLLECTION.v_last_collection_id,
4067 MSC_CL_COLLECTION.v_instance_id,
4068 MSC_CL_COLLECTION.v_current_date,
4069 MSC_CL_COLLECTION.v_current_user,
4070 MSC_CL_COLLECTION.v_current_date,
4071 MSC_CL_COLLECTION.v_current_user,
4072 MSC_CL_COLLECTION.v_current_user
4073 );
4074
4075
4076 END IF;
4077
4078 COMMIT;
4079
4080 EXCEPTION
4081 WHEN OTHERS THEN
4082
4083 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'An error has occurred.');
4084 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4085 RAISE;
4086 END;
4087
4088 END LOOP;
4089
4090 COMMIT;
4091
4092
4093 END LOAD_FISCAL_CALENDAR;
4094
4095 /* LOAD_TRIP added for Collecting Trip and Trip Stops for Deployment Planning */
4096 PROCEDURE LOAD_TRIP IS
4097
4098 CURSOR c1 IS
4099 SELECT
4100 mst.TRIP_ID,
4101 mst.NAME,
4102 mst.SHIP_METHOD_CODE,
4103 mst.PLANNED_FLAG,
4104 mst.STATUS_CODE,
4105 mst.WEIGHT_CAPACITY,
4106 mst.WEIGHT_UOM,
4107 mst.VOLUME_CAPACITY,
4108 mst.VOLUME_UOM,
4109 mst.SR_INSTANCE_ID
4110 FROM MSC_ST_TRIPS mst
4111 WHERE mst.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
4112 AND mst.DELETED_FLAG= MSC_UTIL.SYS_NO;
4113
4114 CURSOR c1_d IS
4115 SELECT
4116 mst.TRIP_ID,
4117 mst.SR_INSTANCE_ID
4118 FROM MSC_ST_TRIPS mst
4119 WHERE mst.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
4120 AND mst.DELETED_FLAG= MSC_UTIL.SYS_YES;
4121
4122 CURSOR c2 IS
4123 SELECT
4124 STOP_ID,
4125 STOP_LOCATION_ID,
4126 STATUS_CODE,
4127 STOP_SEQUENCE_NUMBER,
4128 PLANNED_ARRIVAL_DATE,
4129 PLANNED_DEPARTURE_DATE,
4130 TRIP_ID,
4131 mst.SR_INSTANCE_ID
4132 FROM MSC_ST_TRIP_STOPS mst
4133 WHERE mst.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
4134 AND mst.DELETED_FLAG= MSC_UTIL.SYS_NO;
4135
4136 CURSOR c2_d IS
4137 SELECT
4138 mst.STOP_ID,
4139 mst.SR_INSTANCE_ID
4140 FROM MSC_ST_TRIP_STOPS mst
4141 WHERE mst.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
4142 AND mst.DELETED_FLAG= MSC_UTIL.SYS_YES;
4143
4144
4145 c_count NUMBER:= 0;
4146 lv_tbl VARCHAR2(30);
4147 lv_sql_stmt VARCHAR2(5000);
4148
4149 BEGIN
4150
4151 IF MSC_CL_COLLECTION.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
4152
4153 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
4154
4155 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_TRIPS', MSC_CL_COLLECTION.v_instance_id, -1);
4156
4157 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_TRIP_STOPS', MSC_CL_COLLECTION.v_instance_id, -1);
4158
4159 END IF; -- MSC_CL_COLLECTION.v_is_complete_refresh
4160
4161 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
4162
4163 FOR c_rec IN c1_d LOOP
4164
4165 DELETE MSC_TRIPS
4166 WHERE PLAN_ID= -1
4167 AND TRIP_ID= c_rec.TRIP_ID
4168 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
4169
4170 END LOOP;
4171
4172 FOR c_rec IN c2_d LOOP
4173
4174 DELETE MSC_TRIP_STOPS
4175 WHERE PLAN_ID= -1
4176 AND STOP_ID= c_rec.STOP_ID
4177 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
4178
4179 END LOOP;
4180
4181 END IF;
4182
4183 c_count:= 0;
4184
4185 FOR c_rec IN c1 LOOP
4186
4187 BEGIN
4188
4189 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
4190
4191 UPDATE MSC_TRIPS
4192 SET
4193 NAME = c_rec.NAME,
4194 SHIP_METHOD_CODE = c_rec.SHIP_METHOD_CODE,
4195 PLANNED_FLAG = c_rec.PLANNED_FLAG,
4196 STATUS_CODE = c_rec.STATUS_CODE,
4197 WEIGHT_CAPACITY = c_rec.WEIGHT_CAPACITY,
4198 WEIGHT_UOM = c_rec.WEIGHT_UOM,
4199 VOLUME_CAPACITY = c_rec.VOLUME_CAPACITY,
4200 VOLUME_UOM = c_rec.VOLUME_UOM,
4201 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
4202 LAST_UPDATED_BY = MSC_CL_COLLECTION.v_current_user,
4203 LAST_UPDATE_DATE = MSC_CL_COLLECTION.v_current_date,
4204 LAST_UPDATE_LOGIN = MSC_CL_COLLECTION.v_current_user
4205 WHERE PLAN_ID= -1
4206 AND TRIP_ID= c_rec.TRIP_ID
4207 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
4208
4209 END IF;
4210
4211 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
4212
4213 INSERT INTO MSC_TRIPS
4214 ( PLAN_ID,
4215 TRIP_ID,
4216 NAME,
4217 SHIP_METHOD_CODE,
4218 PLANNED_FLAG,
4219 STATUS_CODE,
4220 WEIGHT_CAPACITY,
4221 WEIGHT_UOM,
4222 VOLUME_CAPACITY,
4223 VOLUME_UOM,
4224 SR_INSTANCE_ID,
4225 REFRESH_NUMBER,
4226 LAST_UPDATE_DATE,
4227 LAST_UPDATED_BY,
4228 CREATION_DATE,
4229 CREATED_BY)
4230 VALUES
4231 ( -1,
4232 c_rec.TRIP_ID,
4233 c_rec.NAME,
4234 c_rec.SHIP_METHOD_CODE,
4235 c_rec.PLANNED_FLAG,
4236 c_rec.STATUS_CODE,
4237 c_rec.WEIGHT_CAPACITY,
4238 c_rec.WEIGHT_UOM,
4239 c_rec.VOLUME_CAPACITY,
4240 c_rec.VOLUME_UOM,
4241 c_rec.SR_INSTANCE_ID,
4242 MSC_CL_COLLECTION.v_last_collection_id,
4243 MSC_CL_COLLECTION.v_current_date,
4244 MSC_CL_COLLECTION.v_current_user,
4245 MSC_CL_COLLECTION.v_current_date,
4246 MSC_CL_COLLECTION.v_current_user );
4247
4248 END IF; --SQL%NOTFOUND
4249
4250 c_count:= c_count+1;
4251
4252 IF c_count> MSC_CL_COLLECTION.PBS THEN
4253 COMMIT;
4254 c_count:= 0;
4255 END IF;
4256
4257 EXCEPTION WHEN OTHERS THEN
4258
4259 IF SQLCODE IN (-01653,-01650,-01562,-01683) THEN
4260
4261 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4262 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4263 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRIP');
4264 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TRIPS');
4265 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4266
4267 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4268 RAISE;
4269
4270 ELSE
4271 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
4272
4273 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4274 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4275 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRIP');
4276 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TRIPS');
4277 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4278
4279
4280 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4281 FND_MESSAGE.SET_TOKEN('COLUMN', 'NAME');
4282 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.NAME);
4283 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4284 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4285
4286 END IF;
4287
4288 END;
4289
4290 END LOOP;
4291
4292
4293 c_count:= 0;
4294
4295 FOR c_rec IN c2 LOOP
4296
4297 BEGIN
4298
4299 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
4300
4301 UPDATE MSC_TRIP_STOPS
4302 SET
4303 STOP_LOCATION_ID = c_rec.STOP_LOCATION_ID,
4304 STATUS_CODE = c_rec.STATUS_CODE,
4305 STOP_SEQUENCE_NUMBER = c_rec.STOP_SEQUENCE_NUMBER,
4306 PLANNED_ARRIVAL_DATE = c_rec.PLANNED_ARRIVAL_DATE,
4307 PLANNED_DEPARTURE_DATE = c_rec.PLANNED_DEPARTURE_DATE,
4308 TRIP_ID = c_rec.TRIP_ID,
4309 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
4310 LAST_UPDATED_BY = MSC_CL_COLLECTION.v_current_user,
4311 LAST_UPDATE_DATE = MSC_CL_COLLECTION.v_current_date,
4312 LAST_UPDATE_LOGIN = MSC_CL_COLLECTION.v_current_user
4313 WHERE PLAN_ID= -1
4314 AND STOP_ID= c_rec.STOP_ID
4315 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
4316
4317 END IF;
4318
4319 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
4320
4321 INSERT INTO MSC_TRIP_STOPS
4322 ( PLAN_ID,
4323 STOP_ID,
4324 STOP_LOCATION_ID,
4325 STATUS_CODE,
4326 STOP_SEQUENCE_NUMBER,
4327 PLANNED_ARRIVAL_DATE,
4328 PLANNED_DEPARTURE_DATE,
4329 TRIP_ID,
4330 SR_INSTANCE_ID,
4331 REFRESH_NUMBER,
4332 LAST_UPDATE_DATE,
4333 LAST_UPDATED_BY,
4334 CREATION_DATE,
4335 CREATED_BY)
4336 VALUES
4337 ( -1,
4338 c_rec.STOP_ID,
4339 c_rec.STOP_LOCATION_ID,
4340 c_rec.STATUS_CODE,
4341 c_rec.STOP_SEQUENCE_NUMBER,
4342 c_rec.PLANNED_ARRIVAL_DATE,
4343 c_rec.PLANNED_DEPARTURE_DATE,
4344 c_rec.TRIP_ID,
4345 c_rec.SR_INSTANCE_ID,
4346 MSC_CL_COLLECTION.v_last_collection_id,
4347 MSC_CL_COLLECTION.v_current_date,
4348 MSC_CL_COLLECTION.v_current_user,
4349 MSC_CL_COLLECTION.v_current_date,
4350 MSC_CL_COLLECTION.v_current_user );
4351
4352 END IF; --SQL%NOTFOUND
4353
4354 c_count:= c_count+1;
4355
4356 IF c_count> MSC_CL_COLLECTION.PBS THEN
4357 COMMIT;
4358 c_count:= 0;
4359 END IF;
4360
4361 EXCEPTION WHEN OTHERS THEN
4362
4363 IF SQLCODE IN (-01653,-01650,-01562,-01683) THEN
4364
4365 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4366 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4367 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRIP');
4368 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TRIP_STOPS');
4369 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4370
4371 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4372 RAISE;
4373
4374 ELSE
4375 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
4376
4377 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4378 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4379 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRIP');
4380 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TRIP_STOPS');
4381 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4382
4383
4384 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4385 FND_MESSAGE.SET_TOKEN('COLUMN', 'STOP_ID');
4386 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.STOP_ID));
4387 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4388
4389 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4390
4391 END IF;
4392
4393 END;
4394
4395 END LOOP;
4396
4397 COMMIT;
4398
4399 END IF; -- v_apps_ver >= G_APPS115
4400 END LOAD_TRIP;
4401
4402 --- for bug # 6469722
4403 PROCEDURE LOAD_CURRENCY_CONVERSION IS
4404
4405 cnt number := 0;
4406 reqid number;
4407 v_sql_stmt VARCHAR2(2000);
4408
4409 BEGIN
4410
4411 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
4412
4413 Begin
4414 select 1 into cnt from MSC_CURRENCY_CONVERSIONS
4415 where (to_currency = MSC_CL_OTHER_PULL.G_MSC_HUB_CURR_CODE and
4416 conv_type = MSC_CL_OTHER_PULL.G_MSC_CURR_CONV_TYPE)
4417 and rownum < 2;
4418
4419 exception
4420 when no_data_found then
4421 cnt :=0;
4422
4423 end;
4424
4425
4426 If (cnt = 0) then
4427 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE('MSC_CURRENCY_CONVERSIONS');
4428 End if;
4429
4430
4431 MERGE INTO MSC_CURRENCY_CONVERSIONS mcc
4432 USING (Select * from MSC_ST_CURRENCY_CONVERSIONS where sr_instance_id = MSC_CL_COLLECTION.v_instance_id) mst
4433 ON (mcc.from_currency = mst.from_currency
4434 AND mcc.to_currency = mst.to_currency
4435 AND mcc.conv_date = mst.conv_date
4436 AND mcc.conv_type = mst.conv_type
4437 AND mcc.conv_type = MSC_CL_OTHER_PULL.G_MSC_CURR_CONV_TYPE)
4438 WHEN MATCHED THEN
4439 UPDATE SET mcc.conv_rate = mst.conv_rate,
4440 mcc.last_coll_instance_id = mst.sr_instance_id
4441 WHEN NOT MATCHED THEN
4442 INSERT (mcc.last_coll_instance_id,mcc.from_currency,mcc.to_currency,mcc.conv_date,mcc.conv_type,mcc.conv_rate,mcc.creation_date,mcc.created_by,mcc.last_update_date,mcc.last_updated_by,mcc.last_update_login,mcc.rn)
4443 VALUES (mst.sr_instance_id,mst.from_currency,mst.to_currency,mst.conv_date,mst.conv_type,mst.conv_rate,mst.creation_date,mst.created_by,mst.last_update_date,mst.last_updated_by,mst.last_update_login,mst.rn);
4444 COMMIT;
4445 END IF;
4446
4447 Begin
4448 /* Submit the CP for Purging old rows */
4449
4450 reqid := FND_REQUEST.SUBMIT_REQUEST('MSC',
4451 'MSCCLMISC',
4452 Null,
4453 Null,
4454 False,
4455 'MSC_CL_OTHER_ODS_LOAD',
4456 'PURGE_STALE_CURRENCY_CONV',
4457 Null);
4458 commit ;
4459 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Submitted CP for purge stale currency data. '|| reqid);
4460 EXCEPTION
4461 WHEN OTHERS THEN
4462 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4463 RAISE;
4464 End;
4465
4466 Begin
4467 /* submit CP for refreshing MV */
4468 -- Forward port of bug 12940569:
4469 -- submit MSCHUBRMV instead of MSCCLMISC for MSC_PHUB_PKG.REFRESH_MVS
4470 reqid := FND_REQUEST.SUBMIT_REQUEST('MSC',
4471 'MSCHUBRMV',
4472 Null,
4473 Null,
4474 False,
4475 1);
4476 commit ;
4477 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Submitted CP MSCHUBRMV to refresh MV. '|| reqid);
4478 EXCEPTION
4479 WHEN OTHERS THEN
4480 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4481 RAISE;
4482 End;
4483
4484 EXCEPTION
4485 WHEN OTHERS THEN
4486
4487 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'An error has occurred.');
4488 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4489 RAISE;
4490
4491 END LOAD_CURRENCY_CONVERSION;
4492
4493 PROCEDURE LOAD_DELIVERY_DETAILS IS -- for bug 6730983
4494 lv_sql_stmt VARCHAR2(32767);
4495 i NUMBER := -1;
4496 reqid number;
4497 BEGIN
4498 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
4499 /* Updating Who cols of Staging Tables */
4500 UPDATE MSC_ST_DELIVERY_DETAILS
4501 SET
4502 REFRESH_NUMBER = MSC_CL_COLLECTION.v_last_collection_id,
4503 LAST_UPDATE_DATE = MSC_CL_COLLECTION.v_current_date,
4504 LAST_UPDATED_BY = MSC_CL_COLLECTION.v_current_user,
4505 CREATION_DATE = MSC_CL_COLLECTION.v_current_date,
4506 CREATED_BY = MSC_CL_COLLECTION.v_current_user,
4507 LAST_UPDATE_LOGIN =MSC_CL_COLLECTION.v_current_user
4508 WHERE SR_INSTANCE_ID = MSC_CL_COLLECTION.v_instance_id;
4509
4510 COMMIT;
4511
4512 /* Initialize the list */
4513 IF NOT MSC_CL_EXCHANGE_PARTTBL.Initialize_SWAP_Tbl_List(MSC_CL_COLLECTION.v_instance_id,MSC_CL_COLLECTION.v_instance_code) THEN
4514 RAISE MSC_CL_COLLECTION.EXCHANGE_PARTN_ERROR;
4515 END IF;
4516 /* Get the swap table index number in the list*/
4517 i := MSC_CL_EXCHANGE_PARTTBL.get_SWAP_table_index('MSC_DELIVERY_DETAILS'); --ods table name
4518 IF i = -1 THEN
4519 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Table not in the list of SWAP partition');
4520 RAISE MSC_CL_COLLECTION.EXCHANGE_PARTN_ERROR;
4521 END IF;
4522 /* Do phase 1 exchange*/
4523
4524 IF NOT MSC_CL_EXCHANGE_PARTTBL.EXCHANGE_SINGLE_TAB_PARTN (
4525 MSC_CL_EXCHANGE_PARTTBL.v_swapTblList(i).stg_table_name,
4526 MSC_CL_EXCHANGE_PARTTBL.v_swapTblList(i).stg_table_partn_name,
4527 MSC_CL_EXCHANGE_PARTTBL.v_swapTblList(i).temp_table_name,
4528 MSC_UTIL.SYS_NO ) THEN
4529 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Exchange partition failed');
4530 RAISE MSC_CL_COLLECTION.EXCHANGE_PARTN_ERROR;
4531 END IF;
4532
4533 EXECUTE IMMEDIATE ' Update msc_coll_parameters set '
4534 || MSC_CL_EXCHANGE_PARTTBL.v_swapTblList(i).column_name || ' = '
4535 || MSC_CL_COLLECTION.G_STG_ODS_SWP_PHASE_1
4536 || ' where instance_id = ' || MSC_CL_COLLECTION.v_instance_id;
4537
4538 commit;
4539 /* Add code to copy required data from ods table to this temp table*/
4540
4541 lv_sql_stmt := ' INSERT INTO '||MSC_CL_EXCHANGE_PARTTBL.v_swapTblList(i).temp_table_name
4542 ||' ('
4543 ||' SR_INSTANCE_ID,'
4544 ||' DELIVERY_DETAIL_ID,'
4545 ||' SOURCE_CODE,'
4546 ||' SOURCE_HEADER_ID,'
4547 ||' SOURCE_LINE_ID,'
4548 ||' SOURCE_HEADER_NUMBER,'
4549 ||' SHIP_SET_ID,'
4550 ||' ARRIVAL_SET_ID,'
4551 ||' SHIP_FROM_LOCATION_ID,'
4552 ||' ORGANIZATION_ID,'
4553 ||' SHIP_TO_LOCATION_ID,'
4554 ||' SHIP_TO_SITE_USE_ID,'
4555 ||' DELIVER_TO_LOCATION_ID,'
4556 ||' DELIVER_TO_SITE_USE_ID,'
4557 ||' CANCELLED_QUANTITY,'
4558 ||' REQUESTED_QUANTITY,'
4559 ||' REQUESTED_QUANTITY_UOM,'
4560 ||' SHIPPED_QUANTITY,'
4561 ||' DELIVERED_QUANTITY,'
4562 ||' DATE_REQUESTED,'
4563 ||' DATE_SCHEDULED,'
4564 ||' OPERATING_UNIT,'
4565 ||' INV_INTERFACED_FLAG,'
4566 ||' EARLIEST_PICKUP_DATE,'
4567 ||' LATEST_PICKUP_DATE,'
4568 ||' EARLIEST_DROPOFF_DATE,'
4569 ||' LATEST_DROPOFF_DATE,'
4570 ||' REFRESH_NUMBER,'
4571 ||' LAST_UPDATE_DATE,'
4572 ||' LAST_UPDATED_BY,'
4573 ||' CREATION_DATE,'
4574 ||' CREATED_BY,'
4575 ||' LAST_UPDATE_LOGIN'
4576 ||' ) '
4577 ||' SELECT'
4578 ||' SR_INSTANCE_ID,'
4579 ||' DELIVERY_DETAIL_ID,'
4580 ||' SOURCE_CODE,'
4581 ||' SOURCE_HEADER_ID,'
4582 ||' SOURCE_LINE_ID,'
4583 ||' SOURCE_HEADER_NUMBER,'
4584 ||' SHIP_SET_ID,'
4585 ||' ARRIVAL_SET_ID,'
4586 ||' SHIP_FROM_LOCATION_ID,'
4587 ||' ORGANIZATION_ID,'
4588 ||' SHIP_TO_LOCATION_ID,'
4589 ||' SHIP_TO_SITE_USE_ID,'
4590 ||' DELIVER_TO_LOCATION_ID,'
4591 ||' DELIVER_TO_SITE_USE_ID,'
4592 ||' CANCELLED_QUANTITY,'
4593 ||' REQUESTED_QUANTITY,'
4594 ||' REQUESTED_QUANTITY_UOM,'
4595 ||' SHIPPED_QUANTITY,'
4596 ||' DELIVERED_QUANTITY,'
4597 ||' DATE_REQUESTED,'
4598 ||' DATE_SCHEDULED,'
4599 ||' OPERATING_UNIT,'
4600 ||' INV_INTERFACED_FLAG,'
4601 ||' EARLIEST_PICKUP_DATE,'
4602 ||' LATEST_PICKUP_DATE,'
4603 ||' EARLIEST_DROPOFF_DATE,'
4604 ||' LATEST_DROPOFF_DATE,'
4605 ||' REFRESH_NUMBER,'
4606 ||' LAST_UPDATE_DATE,'
4607 ||' LAST_UPDATED_BY,'
4608 ||' CREATION_DATE,'
4609 ||' CREATED_BY,'
4610 ||' LAST_UPDATE_LOGIN'
4611 ||' FROM MSC_DELIVERY_DETAILS '
4612 ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
4613 ||' and organization_id not '|| msc_Util.v_in_org_str;
4614
4615
4616 EXECUTE IMMEDIATE lv_sql_stmt;
4617 /* Add code to create indexes on this temp table*/
4618
4619
4620 COMMIT;
4621 Begin
4622 /* Submit the CP for Purging MSC_TRANSPORTATION_UPDATES */
4623
4624 reqid := FND_REQUEST.SUBMIT_REQUEST('MSC',
4625 'MSCCLMISC',
4626 Null,
4627 Null,
4628 False,
4629 'MSC_WS_OTM_BPEL',
4630 'PURGETRANSPORTATIONUPDATES',
4631 Null);
4632 commit ;
4633 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Submitted CP for purge MSC_TRANSPORTATION_UPDATES. '|| reqid);
4634 EXCEPTION
4635 WHEN OTHERS THEN
4636 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4637 RAISE;
4638 End;
4639 END IF; -- MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh
4640 END LOAD_DELIVERY_DETAILS;
4641
4642
4643 PROCEDURE LOAD_VISITS IS
4644 CURSOR c1 IS
4645 SELECT
4646 msv.VISIT_ID,
4647 msv.VISIT_NAME,
4648 msv.VISIT_DESC,
4649 msv.VISIT_START_DATE,
4650 msv.VISIT_END_DATE,
4651 msv.ORGANIZATION_ID,
4652 msv.SR_INSTANCE_ID
4653 FROM MSC_ST_VISITS msv
4654 WHERE msv.deleted_flag = MSC_UTIL.SYS_NO
4655 AND msv.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
4656
4657 CURSOR c_del IS
4658 SELECT
4659 msv.VISIT_ID,
4660 msv.ORGANIZATION_ID,
4661 msv.SR_INSTANCE_ID
4662 FROM MSC_ST_VISITS msv
4663 WHERE msv.deleted_flag = MSC_UTIL.SYS_YES
4664 and msv.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
4665
4666 BEGIN
4667
4668 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'IN LOAD VISITS');
4669
4670 FOR c_rec IN c_del LOOP
4671 BEGIN
4672
4673 DELETE MSC_WO_MILESTONES
4674 WHERE SR_INSTANCE_ID= c_rec.sr_instance_id
4675 AND VISIT_ID = c_rec.visit_id
4676 AND ORGANIZATION_ID = c_rec.organization_id;
4677
4678 DELETE MSC_VISITS
4679 WHERE SR_INSTANCE_ID= c_rec.sr_instance_id
4680 AND VISIT_ID = c_rec.visit_id
4681 AND ORGANIZATION_ID = c_rec.organization_id;
4682
4683 EXCEPTION
4684 WHEN OTHERS THEN
4685
4686 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'An error has occurred during deletion of Visits.');
4687 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4688 RAISE;
4689 END;
4690 END LOOP;
4691
4692 COMMIT;
4693 FOR c_rec IN c1 LOOP
4694
4695 BEGIN
4696
4697 IF MSC_CL_COLLECTION.v_is_legacy_refresh THEN
4698 UPDATE MSC_VISITS
4699 SET
4700 VISIT_DESC = c_rec.VISIT_DESC,
4701 VISIT_START_DATE = c_rec.VISIT_START_DATE,
4702 VISIT_END_DATE = c_rec.VISIT_END_DATE,
4703 REFRESH_ID = MSC_CL_COLLECTION.v_last_collection_id,
4704 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
4705 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
4706 WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
4707 AND VISIT_ID = c_rec.VISIT_ID
4708 AND ORGANIZATION_ID = c_rec.ORGANIZATION_ID;
4709
4710 END IF;
4711
4712 IF (MSC_CL_COLLECTION.v_is_complete_refresh or MSC_CL_COLLECTION.v_is_partial_refresh) OR (MSC_CL_COLLECTION.v_is_legacy_refresh AND SQL%NOTFOUND) THEN
4713 INSERT INTO MSC_VISITS (
4714 VISIT_ID,
4715 VISIT_NAME,
4716 VISIT_DESC,
4717 VISIT_START_DATE,
4718 VISIT_END_DATE,
4719 ORGANIZATION_ID,
4720 REFRESH_ID,
4721 SR_INSTANCE_ID,
4722 LAST_UPDATE_DATE,
4723 LAST_UPDATED_BY,
4724 CREATION_DATE,
4725 CREATED_BY)
4726 VALUES
4727 (c_rec.VISIT_ID,
4728 c_rec.VISIT_NAME,
4729 c_rec.VISIT_DESC,
4730 c_rec.VISIT_START_DATE,
4731 c_rec.VISIT_END_DATE,
4732 c_rec.ORGANIZATION_ID,
4733 MSC_CL_COLLECTION.v_last_collection_id,
4734 MSC_CL_COLLECTION.v_instance_id,
4735 MSC_CL_COLLECTION.v_current_date,
4736 MSC_CL_COLLECTION.v_current_user,
4737 MSC_CL_COLLECTION.v_current_date,
4738 MSC_CL_COLLECTION.v_current_user);
4739
4740 END IF;
4741
4742 EXCEPTION
4743 WHEN OTHERS THEN
4744
4745 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'An error has occurred.');
4746 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4747 RAISE;
4748 END;
4749
4750 END LOOP;
4751 COMMIT;
4752
4753 END LOAD_VISITS;
4754
4755 PROCEDURE LOAD_MILESTONES IS
4756 CURSOR c1 IS
4757 SELECT
4758 mswm.MILESTONE,
4759 mswm.MILESTONE_DESC,
4760 mswm.VISIT_ID,
4761 mswm.ORGANIZATION_ID,
4762 mswm.SR_INSTANCE_ID
4763 FROM MSC_ST_WO_MILESTONES mswm
4764 WHERE mswm.deleted_flag = MSC_UTIL.SYS_NO
4765 AND mswm.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
4766
4767 CURSOR c_del IS
4768 SELECT
4769 mswm.MILESTONE,
4770 mswm.VISIT_ID,
4771 mswm.ORGANIZATION_ID,
4772 mswm.SR_INSTANCE_ID
4773 FROM MSC_ST_WO_MILESTONES mswm
4774 WHERE mswm.deleted_flag = MSC_UTIL.SYS_YES
4775 and mswm.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
4776
4777 BEGIN
4778 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'IN LOAD MILESTONES- Inst :'||MSC_CL_COLLECTION.v_instance_id );
4779 FOR c_rec IN c_del LOOP
4780 BEGIN
4781
4782 DELETE MSC_WO_MILESTONES
4783 WHERE SR_INSTANCE_ID= c_rec.sr_instance_id
4784 AND MILESTONE = c_rec.milestone
4785 AND VISIT_ID = c_rec.visit_id
4786 AND ORGANIZATION_ID = c_rec.organization_id;
4787
4788 EXCEPTION
4789 WHEN OTHERS THEN
4790
4791 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'An error has occurred during deletion of Milestones.');
4792 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4793 RAISE;
4794 END;
4795
4796 END LOOP;
4797
4798 COMMIT;
4799
4800 FOR c_rec IN c1 LOOP
4801
4802 BEGIN
4803
4804 IF MSC_CL_COLLECTION.v_is_legacy_refresh THEN
4805 UPDATE MSC_WO_MILESTONES
4806 SET
4807 MILESTONE_DESC = c_rec.MILESTONE_DESC,
4808 REFRESH_ID = MSC_CL_COLLECTION.v_last_collection_id,
4809 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
4810 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
4811 WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
4812 AND VISIT_ID = c_rec.VISIT_ID
4813 AND MILESTONE = c_rec.MILESTONE
4814 AND ORGANIZATION_ID = c_rec.ORGANIZATION_ID;
4815
4816 END IF;
4817 IF (MSC_CL_COLLECTION.v_is_complete_refresh or MSC_CL_COLLECTION.v_is_partial_refresh) OR (MSC_CL_COLLECTION.v_is_legacy_refresh AND SQL%NOTFOUND) THEN
4818 INSERT INTO MSC_WO_MILESTONES (
4819 MILESTONE,
4820 MILESTONE_DESC,
4821 VISIT_ID,
4822 ORGANIZATION_ID,
4823 REFRESH_ID,
4824 SR_INSTANCE_ID,
4825 LAST_UPDATE_DATE,
4826 LAST_UPDATED_BY,
4827 CREATION_DATE,
4828 CREATED_BY)
4829 VALUES
4830 (c_rec.MILESTONE,
4831 c_rec.MILESTONE_DESC,
4832 c_rec.VISIT_ID,
4833 c_rec.ORGANIZATION_ID,
4834 MSC_CL_COLLECTION.v_last_collection_id,
4835 MSC_CL_COLLECTION.v_instance_id,
4836 MSC_CL_COLLECTION.v_current_date,
4837 MSC_CL_COLLECTION.v_current_user,
4838 MSC_CL_COLLECTION.v_current_date,
4839 MSC_CL_COLLECTION.v_current_user);
4840
4841 END IF;
4842 EXCEPTION
4843 WHEN OTHERS THEN
4844
4845 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'An error has occurred.');
4846 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4847 RAISE;
4848 END;
4849
4850 END LOOP;
4851
4852 COMMIT;
4853 END LOAD_MILESTONES;
4854
4855 PROCEDURE LOAD_WBS IS
4856 CURSOR c1 IS
4857 SELECT
4858 mswbs.PARAMETER_NAME,
4859 mswbs.DISPLAY_NAME,
4860 mswbs.ORGANIZATION_ID,
4861 mswbs.SR_INSTANCE_ID
4862 FROM MSC_ST_WORK_BREAKDOWN_STRUCT mswbs
4863 WHERE mswbs.deleted_flag = MSC_UTIL.SYS_NO
4864 AND mswbs.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
4865
4866 CURSOR c_del IS
4867 SELECT
4868 mswbs.PARAMETER_NAME,
4869 mswbs.DISPLAY_NAME,
4870 mswbs.ORGANIZATION_ID,
4871 mswbs.SR_INSTANCE_ID
4872 FROM MSC_ST_WORK_BREAKDOWN_STRUCT mswbs
4873 WHERE mswbs.deleted_flag = MSC_UTIL.SYS_YES
4874 and mswbs.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
4875
4876 BEGIN
4877 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'IN LOAD WBS- Inst :'||MSC_CL_COLLECTION.v_instance_id );
4878 FOR c_rec IN c_del LOOP
4879 BEGIN
4880
4881 DELETE MSC_WORK_BREAKDOWN_STRUCT
4882 WHERE SR_INSTANCE_ID= c_rec.sr_instance_id
4883 AND PARAMETER_NAME = c_rec.parameter_name
4884 AND ORGANIZATION_ID = c_rec.organization_id;
4885
4886 EXCEPTION
4887 WHEN OTHERS THEN
4888
4889 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'An error has occurred during deletion of WBS.');
4890 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4891 RAISE;
4892 END;
4893
4894 END LOOP;
4895
4896 COMMIT;
4897
4898 FOR c_rec IN c1 LOOP
4899
4900 BEGIN
4901
4902 IF MSC_CL_COLLECTION.v_is_legacy_refresh THEN
4903
4904 UPDATE MSC_WORK_BREAKDOWN_STRUCT
4905 SET DISPLAY_NAME = c_rec.DISPLAY_NAME,
4906 REFRESH_ID = MSC_CL_COLLECTION.v_last_collection_id,
4907 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
4908 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
4909 WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
4910 AND ORGANIZATION_ID = c_rec.ORGANIZATION_ID
4911 AND PARAMETER_NAME = c_rec.PARAMETER_NAME;
4912
4913
4914 END IF;
4915 IF (MSC_CL_COLLECTION.v_is_complete_refresh or MSC_CL_COLLECTION.v_is_partial_refresh) OR (MSC_CL_COLLECTION.v_is_legacy_refresh AND SQL%NOTFOUND) THEN
4916 INSERT INTO MSC_WORK_BREAKDOWN_STRUCT (
4917 PARAMETER_NAME,
4918 DISPLAY_NAME,
4919 ORGANIZATION_ID,
4920 REFRESH_ID,
4921 SR_INSTANCE_ID,
4922 LAST_UPDATE_DATE,
4923 LAST_UPDATED_BY,
4924 CREATION_DATE,
4925 CREATED_BY)
4926 VALUES
4927 (c_rec.PARAMETER_NAME,
4928 c_rec.DISPLAY_NAME,
4929 c_rec.ORGANIZATION_ID,
4930 MSC_CL_COLLECTION.v_last_collection_id,
4931 MSC_CL_COLLECTION.v_instance_id,
4932 MSC_CL_COLLECTION.v_current_date,
4933 MSC_CL_COLLECTION.v_current_user,
4934 MSC_CL_COLLECTION.v_current_date,
4935 MSC_CL_COLLECTION.v_current_user);
4936
4937 END IF;
4938 EXCEPTION
4939 WHEN OTHERS THEN
4940
4941 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'An error has occurred.');
4942 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4943 RAISE;
4944 END;
4945
4946 END LOOP;
4947
4948 COMMIT;
4949 END LOAD_WBS;
4950
4951 PROCEDURE LOAD_WOATTRIBUTES IS
4952 CURSOR c1 IS
4953 SELECT
4954 mswa.SUPPLY_ID,
4955 mswa.PRODUCES_TO_STOCK,
4956 mswa.SERIAL_NUM,
4957 mswa.VISIT_ID,
4958 mswa.VISIT_NAME,
4959 mswa.PARAMETER1,
4960 mswa.PARAMETER2,
4961 mswa.PARAMETER3,
4962 mswa.PARAMETER4,
4963 mswa.PARAMETER5,
4964 mswa.PARAMETER6,
4965 mswa.PARAMETER7,
4966 mswa.PARAMETER8,
4967 mswa.PARAMETER9,
4968 mswa.MASTER_WO,
4969 mswa.PREV_MILESTONE,
4970 mswa.NEXT_MILESTONE,
4971 mswa.ORGANIZATION_ID,
4972 mswa.SR_INSTANCE_ID
4973 FROM MSC_ST_WO_ATTRIBUTES mswa
4974 WHERE mswa.deleted_flag = MSC_UTIL.SYS_NO
4975 AND mswa.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
4976
4977 CURSOR c_del IS
4978 SELECT
4979 mswa.SUPPLY_ID,
4980 mswa.VISIT_ID,
4981 mswa.ORGANIZATION_ID,
4982 mswa.SR_INSTANCE_ID
4983 FROM MSC_ST_WO_ATTRIBUTES mswa
4984 WHERE mswa.deleted_flag = MSC_UTIL.SYS_YES
4985 and mswa.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
4986
4987 BEGIN
4988
4989 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'IN LOAD WO ATTRIBUTES- Inst :'||MSC_CL_COLLECTION.v_instance_id );
4990 FOR c_rec IN c_del LOOP
4991 BEGIN
4992
4993 DELETE MSC_WO_ATTRIBUTES
4994 WHERE SR_INSTANCE_ID= c_rec.sr_instance_id
4995 AND VISIT_ID = c_rec.visit_id
4996 AND SUPPLY_ID = c_rec.supply_id
4997 AND ORGANIZATION_ID = c_rec.organization_id;
4998
4999 EXCEPTION
5000 WHEN OTHERS THEN
5001
5002 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'An error has occurred during deletion of WO attributes.');
5003 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
5004 RAISE;
5005 END;
5006
5007 END LOOP;
5008
5009 COMMIT;
5010 FOR c_rec IN c1 LOOP
5011
5012 BEGIN
5013
5014 IF MSC_CL_COLLECTION.v_is_legacy_refresh THEN
5015 UPDATE MSC_WO_ATTRIBUTES
5016 SET
5017 SERIAL_NUM = c_rec.SERIAL_NUM,
5018 PRODUCES_TO_STOCK = c_rec.PRODUCES_TO_STOCK,
5019 VISIT_ID = c_rec.VISIT_ID,
5020 PARAMETER1 = c_rec.PARAMETER1,
5021 PARAMETER2 = c_rec.PARAMETER2,
5022 PARAMETER3 = c_rec.PARAMETER3,
5023 PARAMETER4 = c_rec.PARAMETER4,
5024 PARAMETER5 = c_rec.PARAMETER5,
5025 PARAMETER6 = c_rec.PARAMETER6,
5026 PARAMETER7 = c_rec.PARAMETER7,
5027 PARAMETER8 = c_rec.PARAMETER8,
5028 PARAMETER9 = c_rec.PARAMETER9,
5029 MASTER_WO = c_rec.MASTER_WO,
5030 PREV_MILESTONE = c_rec.PREV_MILESTONE,
5031 NEXT_MILESTONE = c_rec.NEXT_MILESTONE,
5032 REFRESH_ID = MSC_CL_COLLECTION.v_last_collection_id,
5033 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
5034 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
5035 WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
5036 AND SUPPLY_ID = c_rec.SUPPLY_ID
5037 AND ORGANIZATION_ID = c_rec.ORGANIZATION_ID;
5038
5039 END IF;
5040
5041 IF (MSC_CL_COLLECTION.v_is_complete_refresh or MSC_CL_COLLECTION.v_is_partial_refresh) OR (MSC_CL_COLLECTION.v_is_legacy_refresh AND SQL%NOTFOUND) THEN
5042
5043 INSERT INTO MSC_WO_ATTRIBUTES (
5044 SUPPLY_ID,
5045 PRODUCES_TO_STOCK,
5046 SERIAL_NUM,
5047 VISIT_ID,
5048 VISIT_NAME,
5049 PARAMETER1,
5050 PARAMETER2,
5051 PARAMETER3,
5052 PARAMETER4,
5053 PARAMETER5,
5054 PARAMETER6,
5055 PARAMETER7,
5056 PARAMETER8,
5057 PARAMETER9,
5058 MASTER_WO,
5059 PREV_MILESTONE,
5060 NEXT_MILESTONE,
5061 ORGANIZATION_ID,
5062 REFRESH_ID,
5063 SR_INSTANCE_ID,
5064 LAST_UPDATE_DATE,
5065 LAST_UPDATED_BY,
5066 CREATION_DATE,
5067 CREATED_BY)
5068 VALUES
5069 (c_rec.SUPPLY_ID,
5070 c_rec.PRODUCES_TO_STOCK,
5071 c_rec.SERIAL_NUM,
5072 c_rec.VISIT_ID,
5073 c_rec.VISIT_NAME,
5074 c_rec.PARAMETER1,
5075 c_rec.PARAMETER2,
5076 c_rec.PARAMETER3,
5077 c_rec.PARAMETER4,
5078 c_rec.PARAMETER5,
5079 c_rec.PARAMETER6,
5080 c_rec.PARAMETER7,
5081 c_rec.PARAMETER8,
5082 c_rec.PARAMETER9,
5083 c_rec.MASTER_WO,
5084 c_rec.PREV_MILESTONE,
5085 c_rec.NEXT_MILESTONE,
5086 c_rec.ORGANIZATION_ID,
5087 MSC_CL_COLLECTION.v_last_collection_id,
5088 MSC_CL_COLLECTION.v_instance_id,
5089 MSC_CL_COLLECTION.v_current_date,
5090 MSC_CL_COLLECTION.v_current_user,
5091 MSC_CL_COLLECTION.v_current_date,
5092 MSC_CL_COLLECTION.v_current_user);
5093
5094 END IF;
5095 EXCEPTION
5096 WHEN OTHERS THEN
5097
5098 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'An error has occurred.');
5099 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
5100 RAISE;
5101 END;
5102
5103 END LOOP;
5104
5105 COMMIT;
5106
5107 END LOAD_WOATTRIBUTES;
5108
5109 PROCEDURE LOAD_WO_TASK_HIERARCHY IS
5110 CURSOR c1 IS
5111 SELECT
5112 mswth.CURR_SUPPLY_ID,
5113 mswth.NEXT_SUPPLY_ID,
5114 mswth.PRECEDENCE_CONSTRAINT,
5115 mswth.MIN_SEPARATION,
5116 mswth.MIN_SEP_TIME_UNIT,
5117 mswth.MAX_SEPARATION,
5118 mswth.MAX_SEP_TIME_UNIT,
5119 mswth.ORGANIZATION_ID,
5120 mswth.SR_INSTANCE_ID
5121 FROM MSC_ST_WO_TASK_HIERARCHY mswth
5122 WHERE mswth.deleted_flag = MSC_UTIL.SYS_NO
5123 AND mswth.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
5124
5125 CURSOR c_del IS
5126 SELECT
5127 mswth.CURR_SUPPLY_ID,
5128 mswth.NEXT_SUPPLY_ID,
5129 mswth.ORGANIZATION_ID,
5130 mswth.SR_INSTANCE_ID
5131 FROM MSC_ST_WO_TASK_HIERARCHY mswth
5132 WHERE mswth.deleted_flag = MSC_UTIL.SYS_YES
5133 AND mswth.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
5134
5135 BEGIN
5136
5137 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'IN LOAD WO_TASK_HIERARCHY- Inst :'||MSC_CL_COLLECTION.v_instance_id );
5138
5139 FOR c_rec IN c_del LOOP
5140 BEGIN
5141
5142 DELETE MSC_WO_TASK_HIERARCHY
5143 WHERE SR_INSTANCE_ID= c_rec.sr_instance_id
5144 AND CURR_SUPPLY_ID = c_rec.curr_supply_id
5145 AND NEXT_SUPPLY_ID = c_rec.next_supply_id
5146 AND ORGANIZATION_ID = c_rec.organization_id;
5147
5148 EXCEPTION
5149 WHEN OTHERS THEN
5150
5151 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'An error has occurred during deletion of WO task hierarchy.');
5152 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
5153 RAISE;
5154 END;
5155
5156 END LOOP;
5157
5158 COMMIT;
5159 FOR c_rec IN c1 LOOP
5160
5161 BEGIN
5162
5163 IF MSC_CL_COLLECTION.v_is_legacy_refresh THEN
5164 UPDATE MSC_WO_TASK_HIERARCHY
5165 SET
5166 PRECEDENCE_CONSTRAINT = c_rec.PRECEDENCE_CONSTRAINT,
5167 MIN_SEPARATION = c_rec.MIN_SEPARATION,
5168 MIN_SEP_TIME_UNIT = c_rec.MIN_SEP_TIME_UNIT,
5169 MAX_SEPARATION = c_rec.MAX_SEPARATION,
5170 MAX_SEP_TIME_UNIT = c_rec.MAX_SEP_TIME_UNIT,
5171 REFRESH_ID = MSC_CL_COLLECTION.v_last_collection_id,
5172 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
5173 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
5174 WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
5175 AND ORGANIZATION_ID = c_rec.ORGANIZATION_ID
5176 AND CURR_SUPPLY_ID = c_rec.CURR_SUPPLY_ID
5177 AND NEXT_SUPPLY_ID = c_rec.NEXT_SUPPLY_ID;
5178
5179 END IF;
5180
5181 IF (MSC_CL_COLLECTION.v_is_complete_refresh or MSC_CL_COLLECTION.v_is_partial_refresh) OR (MSC_CL_COLLECTION.v_is_legacy_refresh AND SQL%NOTFOUND) THEN
5182
5183 INSERT INTO MSC_WO_TASK_HIERARCHY (
5184 CURR_SUPPLY_ID,
5185 NEXT_SUPPLY_ID,
5186 PRECEDENCE_CONSTRAINT,
5187 MIN_SEPARATION,
5188 MIN_SEP_TIME_UNIT,
5189 MAX_SEPARATION,
5190 MAX_SEP_TIME_UNIT,
5191 ORGANIZATION_ID,
5192 REFRESH_ID,
5193 SR_INSTANCE_ID,
5194 LAST_UPDATE_DATE,
5195 LAST_UPDATED_BY,
5196 CREATION_DATE,
5197 CREATED_BY)
5198 VALUES
5199 (c_rec.CURR_SUPPLY_ID,
5200 c_rec.NEXT_SUPPLY_ID,
5201 c_rec.PRECEDENCE_CONSTRAINT,
5202 c_rec.MIN_SEPARATION,
5203 c_rec.MIN_SEP_TIME_UNIT,
5204 c_rec.MAX_SEPARATION,
5205 c_rec.MAX_SEP_TIME_UNIT,
5206 c_rec.ORGANIZATION_ID,
5207 MSC_CL_COLLECTION.v_last_collection_id,
5208 MSC_CL_COLLECTION.v_instance_id,
5209 MSC_CL_COLLECTION.v_current_date,
5210 MSC_CL_COLLECTION.v_current_user,
5211 MSC_CL_COLLECTION.v_current_date,
5212 MSC_CL_COLLECTION.v_current_user);
5213
5214 END IF;
5215 EXCEPTION
5216 WHEN OTHERS THEN
5217
5218 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'An error has occurred.');
5219 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
5220 RAISE;
5221 END;
5222
5223 END LOOP;
5224
5225 COMMIT;
5226
5227 END LOAD_WO_TASK_HIERARCHY;
5228
5229 PROCEDURE LOAD_WO_OPERATION_REL IS
5230 CURSOR c1 IS
5231 SELECT
5232 mswor.SUPPLY_ID,
5233 mswor.PRECEDENCE_CONSTRAINT,
5234 mswor.MIN_SEPARATION,
5235 mswor.MIN_SEP_TIME_UNIT,
5236 mswor.MAX_SEPARATION,
5237 mswor.MAX_SEP_TIME_UNIT,
5238 mswor.FROM_OP_SEQ_NUM,
5239 mswor.FROM_OP_RES_SEQ_NUM,
5240 mswor.FROM_OP_DESC,
5241 mswor.TO_OP_SEQ_NUM,
5242 mswor.TO_OP_RES_SEQ_NUM,
5243 mswor.TO_OP_DESC,
5244 mswor.ORGANIZATION_ID,
5245 mswor.SR_INSTANCE_ID
5246 FROM MSC_ST_WO_OPERATION_REL mswor
5247 WHERE mswor.deleted_flag = MSC_UTIL.SYS_NO
5248 AND mswor.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
5249
5250 CURSOR c_del IS
5251 SELECT
5252 mswor.SUPPLY_ID,
5253 mswor.FROM_OP_SEQ_NUM,
5254 mswor.FROM_OP_RES_SEQ_NUM,
5255 mswor.TO_OP_SEQ_NUM,
5256 mswor.TO_OP_RES_SEQ_NUM,
5257 mswor.ORGANIZATION_ID,
5258 mswor.SR_INSTANCE_ID
5259 FROM MSC_ST_WO_OPERATION_REL mswor
5260 WHERE mswor.deleted_flag = MSC_UTIL.SYS_YES
5261 and mswor.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
5262
5263 BEGIN
5264
5265 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'IN LOAD WO_OPERATION REL- Inst :'||MSC_CL_COLLECTION.v_instance_id );
5266
5267 FOR c_rec IN c_del LOOP
5268 BEGIN
5269
5270 DELETE MSC_WO_OPERATION_REL
5271 WHERE SR_INSTANCE_ID= c_rec.sr_instance_id
5272 AND SUPPLY_ID = c_rec.supply_id
5273 AND FROM_OP_SEQ_NUM = c_rec.FROM_OP_SEQ_NUM
5274 AND nvl(FROM_OP_RES_SEQ_NUM,-1) = nvl(c_rec.FROM_OP_RES_SEQ_NUM,-1)
5275 AND TO_OP_SEQ_NUM = c_rec.TO_OP_SEQ_NUM
5276 AND nvl(TO_OP_RES_SEQ_NUM,-1) = nvl(c_rec.TO_OP_RES_SEQ_NUM,-1)
5277 AND ORGANIZATION_ID = c_rec.organization_id;
5278
5279 EXCEPTION
5280 WHEN OTHERS THEN
5281
5282 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'An error has occurred during deletion of WO operation Relation.');
5283 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
5284 RAISE;
5285 END;
5286
5287 END LOOP;
5288
5289 COMMIT;
5290 FOR c_rec IN c1 LOOP
5291
5292 BEGIN
5293 IF MSC_CL_COLLECTION.v_is_legacy_refresh THEN
5294 UPDATE MSC_WO_OPERATION_REL
5295 SET
5296 PRECEDENCE_CONSTRAINT = c_rec.PRECEDENCE_CONSTRAINT,
5297 FROM_OP_DESC = c_rec.FROM_OP_DESC,
5298 TO_OP_DESC = c_rec.TO_OP_DESC,
5299 MIN_SEPARATION = c_rec.MIN_SEPARATION,
5300 MIN_SEP_TIME_UNIT = c_rec.MIN_SEP_TIME_UNIT,
5301 MAX_SEPARATION = c_rec.MAX_SEPARATION,
5302 MAX_SEP_TIME_UNIT = c_rec.MAX_SEP_TIME_UNIT,
5303 REFRESH_ID = MSC_CL_COLLECTION.v_last_collection_id,
5304 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
5305 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
5306 WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
5307 AND ORGANIZATION_ID = c_rec.ORGANIZATION_ID
5308 AND SUPPLY_ID = c_rec.SUPPLY_ID
5309 AND FROM_OP_SEQ_NUM = c_rec.FROM_OP_SEQ_NUM
5310 AND TO_OP_SEQ_NUM = c_rec.TO_OP_SEQ_NUM
5311 AND nvl(FROM_OP_RES_SEQ_NUM,-1) = nvl(c_rec.FROM_OP_RES_SEQ_NUM,-1)
5312 AND nvl(TO_OP_RES_SEQ_NUM,-1) = nvl(c_rec.TO_OP_RES_SEQ_NUM,-1);
5313
5314 END IF;
5315 IF (MSC_CL_COLLECTION.v_is_complete_refresh or MSC_CL_COLLECTION.v_is_partial_refresh) OR (MSC_CL_COLLECTION.v_is_legacy_refresh AND SQL%NOTFOUND) THEN
5316
5317 INSERT INTO MSC_WO_OPERATION_REL (
5318 SUPPLY_ID,
5319 PRECEDENCE_CONSTRAINT,
5320 MIN_SEPARATION,
5321 MIN_SEP_TIME_UNIT,
5322 MAX_SEPARATION,
5323 MAX_SEP_TIME_UNIT,
5324 FROM_OP_SEQ_NUM,
5325 FROM_OP_RES_SEQ_NUM,
5326 FROM_OP_DESC,
5327 TO_OP_SEQ_NUM,
5328 TO_OP_RES_SEQ_NUM,
5329 TO_OP_DESC,
5330 ORGANIZATION_ID,
5331 REFRESH_ID,
5332 SR_INSTANCE_ID,
5333 LAST_UPDATE_DATE,
5334 LAST_UPDATED_BY,
5335 CREATION_DATE,
5336 CREATED_BY)
5337 VALUES
5338 (c_rec.SUPPLY_ID,
5339 c_rec.PRECEDENCE_CONSTRAINT,
5340 c_rec.MIN_SEPARATION,
5341 c_rec.MIN_SEP_TIME_UNIT,
5342 c_rec.MAX_SEPARATION,
5343 c_rec.MAX_SEP_TIME_UNIT,
5344 c_rec.FROM_OP_SEQ_NUM,
5345 c_rec.FROM_OP_RES_SEQ_NUM,
5346 c_rec.FROM_OP_DESC,
5347 c_rec.TO_OP_SEQ_NUM,
5348 c_rec.TO_OP_RES_SEQ_NUM,
5349 c_rec.TO_OP_DESC,
5350 c_rec.ORGANIZATION_ID,
5351 MSC_CL_COLLECTION.v_last_collection_id,
5352 MSC_CL_COLLECTION.v_instance_id,
5353 MSC_CL_COLLECTION.v_current_date,
5354 MSC_CL_COLLECTION.v_current_user,
5355 MSC_CL_COLLECTION.v_current_date,
5356 MSC_CL_COLLECTION.v_current_user);
5357
5358 END IF;
5359 EXCEPTION
5360 WHEN OTHERS THEN
5361
5362 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'An error has occurred.');
5363 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
5364 RAISE;
5365 END;
5366
5367 END LOOP;
5368
5369 COMMIT;
5370 END LOAD_WO_OPERATION_REL;
5371
5372 PROCEDURE LOAD_IB_CONTRACTS IS
5373 c_count NUMBER:=0;
5374 lv_tbl VARCHAR2(30);
5375 lv_sql_stmt VARCHAR2(5000);
5376 lv_cursor_stmt VARCHAR2(5000);
5377
5378 lv_errbuf VARCHAR2(240);
5379 lv_retcode NUMBER;
5380 lv_last_ibuc_coll_date DATE;
5381
5382
5383 BEGIN
5384
5385
5386 IF MSC_CL_COLLECTION.v_exchange_mode = MSC_UTIL.SYS_YES THEN
5387 lv_tbl:= 'ORG_AGGR_IBUC_'||MSC_CL_COLLECTION.v_instance_code;
5388 ELSE
5389 lv_tbl:= 'MSC_ORG_AGGR_IBUC';
5390
5391 SELECT LAST_IBUC_COLL_DATE INTO lv_last_ibuc_coll_date
5392 FROM MSC_APPS_INSTANCES
5393 WHERE INSTANCE_ID = MSC_CL_COLLECTION.v_instance_id;
5394
5395 DELETE FROM MSC_ORG_AGGR_IBUC
5396 WHERE SAMPLE_DATE = trunc(lv_last_ibuc_coll_date);
5397
5398 END IF;
5399
5400 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'Inserting data into table ' || lv_tbl);
5401 /* bulk insert statement, used in case of target/complete collection */
5402
5403 lv_cursor_stmt:=
5404 'INSERT INTO '||lv_tbl
5405 ||'( INVENTORY_ITEM_ID,'
5406 ||' SAMPLE_DATE,'
5407 ||' QUANTITY,'
5408 ||' ZONE,'
5409 ||' REGION_ID,'
5410 ||' REFRESH_NUMBER,'
5411 ||' SR_INSTANCE_ID,'
5412 ||' LAST_UPDATE_DATE,'
5413 ||' LAST_UPDATED_BY,'
5414 ||' LAST_UPDATE_LOGIN,'
5415 ||' CREATION_DATE,'
5416 ||' CREATED_BY)'
5417 ||' SELECT'
5418 ||' t1.INVENTORY_ITEM_ID,'
5419 ||' moab.SAMPLE_DATE,'
5420 ||' moab.QUANTITY,'
5421 ||' moab.ZONE,'
5422 ||' moab.REGION_ID,'
5423 ||' :v_last_collection_id,'
5424 ||' :v_instance_id,'
5425 ||' :v_current_date,'
5426 ||' :v_current_user,'
5427 ||' :v_current_user,'
5428 ||' :v_current_date,'
5429 ||' :v_current_user'
5430 ||' FROM '
5431 ||' MSC_ITEM_ID_LID t1,'
5432 ||' MSC_ST_ZN_AGGR_IBUC moab '
5433 ||' WHERE t1.SR_INVENTORY_ITEM_ID= moab.sr_INVENTORY_ITEM_ID'
5434 ||' AND t1.SR_INSTANCE_ID= moab.SR_INSTANCE_ID'
5435 ||' AND moab.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id ;
5436
5437
5438 IF (MSC_CL_COLLECTION.v_is_complete_refresh
5439 OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
5440 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ORG_AGGR_IBUC',
5441 MSC_CL_COLLECTION.v_instance_id, null);
5442
5443 END IF;
5444
5445 BEGIN
5446
5447 EXECUTE IMMEDIATE lv_cursor_stmt
5448 USING MSC_CL_COLLECTION.v_last_collection_id,
5449 MSC_CL_COLLECTION.v_instance_id,
5450 MSC_CL_COLLECTION.v_current_date,
5451 MSC_CL_COLLECTION.v_current_user,
5452 MSC_CL_COLLECTION.v_current_user,
5453 MSC_CL_COLLECTION.v_current_date,
5454 MSC_CL_COLLECTION.v_current_user;
5455
5456 EXCEPTION
5457 WHEN OTHERS THEN
5458 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
5459 -- IF NOT Handle_Exception(SQLCODE) THEN RAISE; END IF;
5460 END;
5461
5462 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
5463 MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
5464 lv_retcode,
5465 'MSC_ORG_AGGR_IBUC',
5466 MSC_CL_COLLECTION.v_INSTANCE_CODE,
5467 MSC_UTIL.G_ERROR);
5468
5469 IF lv_retcode = MSC_UTIL.G_ERROR THEN
5470 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
5471 RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
5472 ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
5473 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
5474 END IF;
5475
5476 END IF;
5477
5478
5479 COMMIT;
5480 EXCEPTION
5481 WHEN OTHERS THEN
5482 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
5483 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '==============================');
5484 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
5485 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_IB_CONTRACTS');
5486 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ORG_AGGR_IBUC');
5487 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
5488 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
5489 RAISE;
5490 ELSE
5491 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
5492 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '==============================');
5493 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
5494 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_IB_CONTRACTS');
5495 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ORG_AGGR_IBUC');
5496 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
5497
5498 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
5499 FND_MESSAGE.SET_TOKEN('COLUMN', 'INVENTORY_ITEM_ID');
5500 --FND_MESSAGE.SET_TOKEN('VALUE',TO_CHAR(c_rec.ITEM_INSTANCE_ID) );
5501 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
5502 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
5503
5504 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
5505 FND_MESSAGE.SET_TOKEN('COLUMN', 'SAMPLE_DATE');
5506 --FND_MESSAGE.SET_TOKEN('VALUE',TO_CHAR(c_rec.ITEM_INSTANCE_ID) );
5507 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
5508 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
5509
5510 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
5511 FND_MESSAGE.SET_TOKEN('COLUMN', 'ZONE');
5512 --FND_MESSAGE.SET_TOKEN('VALUE',TO_CHAR(c_rec.ITEM_INSTANCE_ID) );
5513 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
5514 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
5515
5516 END IF;
5517
5518 COMMIT;
5519
5520 END LOAD_IB_CONTRACTS;
5521
5522 PROCEDURE POPULATE_ORG (ERRBUF OUT NOCOPY VARCHAR2,
5523 RETCODE OUT NOCOPY NUMBER,
5524 INSTANCE_ID IN NUMBER,
5525 ASSIGNMENT_SET_ID IN NUMBER, --ASSIGNMENT_SET_NAME IN VARCHAR2,
5526 COMPLETE_REFRESH IN NUMBER)
5527
5528 IS
5529 lv_instance_id number ;
5530 --lv_assign_set_name varchar2(34);
5531 lv_assign_set_id NUMBER;
5532 lv_refresh_type NUMBER;
5533 n1 NUMBER;
5534 n2 NUMBER;
5535 lv_sql_stmt VARCHAR2(5000);
5536 BEGIN
5537
5538 lv_instance_id := instance_id;
5539 lv_assign_set_id := assignment_set_id;
5540 lv_refresh_type := complete_refresh;
5541
5542 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Instance ID---' || lv_instance_id ||
5543 ' Assignment Set Id ----' || lv_assign_set_id || ' Refresh Type ----' ||lv_refresh_type) ;
5544
5545 /*SELECT ASSIGNMENT_SET_ID INTO lv_assign_set_id
5546 FROM MSC_ASSIGNMENT_SETS
5547 WHERE ASSIGNMENT_SET_NAME = lv_assign_set_name
5548 AND SR_INSTANCE_ID = lv_instance_id;*/
5549
5550 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Aset id ---' || lv_assign_set_id) ;
5551
5552 SELECT IBUCCP_LCID, lcid INTO n1, n2
5553 FROM MSC_APPS_INSTANCES WHERE instance_id = lv_instance_id ;
5554
5555 lv_sql_stmt :=
5556 ' UPDATE MSC_ORG_AGGR_IBUC MOAI '
5557 ||' set MOAI.SUPPLYING_ORG_ID =('
5558 ||' select MSSO.SOURCE_ORGANIZATION_ID'
5559 ||' from MSC_SR_ASSIGNMENTS MSA,'
5560 ||' MSC_SOURCING_RULES MSR,'
5561 ||' MSC_SR_RECEIPT_ORG MSRO,'
5562 ||' MSC_SR_SOURCE_ORG MSSO'
5563 ||' WHERE MSA.ASSIGNMENT_SET_ID = :lv_assign_set_id'
5564 ||' AND ( (msa.assignment_type=9 AND MSA.REGION_ID = MOAI.REGION_ID AND'
5565 ||' MSA.SR_INSTANCE_ID = MOAI.SR_INSTANCE_ID AND MOAI.INVENTORY_ITEM_ID = msa.INVENTORY_ITEM_ID)'
5566 ||' OR'
5567 ||' (MSA.ASSIGNMENT_TYPE =7 AND MSA.REGION_ID = MOAI.REGION_ID AND MSA.SR_INSTANCE_ID = MOAI.SR_INSTANCE_ID'
5568 ||' AND NOT EXISTS (SELECT 1 FROM MSC_SR_ASSIGNMENTS MSA2'
5569 ||' WHERE MSA2.ASSIGNMENT_SET_ID = :lv_assign_set_id and MSA2.ASSIGNMENT_TYPE =9'
5570 ||' AND MSA2.REGION_ID = MOAI.REGION_ID AND MSA2.SR_INSTANCE_ID=MOAI.sr_instance_id'
5571 ||' AND MOAI.INVENTORY_item_id = msa2.inventory_item_id)))'
5572 ||' AND MSR.SR_INSTANCE_ID = MSA.SR_INSTANCE_ID'
5573 ||' AND MSR.SOURCING_RULE_ID = MSA.SOURCING_RULE_ID'
5574 ||' AND MSRO.SR_INSTANCE_ID = MSR.SR_INSTANCE_ID'
5575 ||' AND MSRO.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID'
5576 ||' AND MSSO.SR_INSTANCE_ID = MSRO.SR_INSTANCE_ID'
5577 ||' AND MSSO.SR_RECEIPT_ID = MSRO.SR_RECEIPT_ID'
5578 ||' AND ROWNUM =1'
5579 ||' )'
5580 ||' WHERE MOAI.SR_INSTANCE_ID = :lv_instance_id' ;
5581
5582
5583 IF lv_refresh_type = 2 THEN
5584
5585 lv_sql_stmt := lv_sql_stmt || ' AND MOAI.REFRESH_NUMBER > :n1';
5586
5587 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'lv_sql_stmt-' ||lv_sql_stmt);
5588 EXECUTE IMMEDIATE lv_sql_stmt using lv_assign_set_id , lv_assign_set_id ,lv_instance_id,n1;
5589
5590 ELSE
5591 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'lv_sql_stmt-' ||lv_sql_stmt);
5592
5593 EXECUTE IMMEDIATE lv_sql_stmt using lv_assign_set_id , lv_assign_set_id ,lv_instance_id;
5594
5595 END IF ;
5596
5597 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Popualted Org info. for Install base Aggregations');
5598 COMMIT;
5599
5600 UPDATE MSC_APPS_INSTANCES
5601 SET IBUCCP_LCID = n2 WHERE INSTANCE_ID= lv_instance_id;
5602 COMMIT;
5603
5604 EXCEPTION
5605 WHEN OTHERS THEN
5606 ERRBUF := SQLERRM;
5607 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'ERROR ' || ERRBUF) ;
5608
5609 END POPULATE_ORG;
5610
5611 PROCEDURE LOAD_SHORT_TEXT IS
5612 c_count NUMBER:=0;
5613 lv_tbl VARCHAR2(30);
5614 lv_sql_stmt VARCHAR2(5000);
5615 lv_cursor_stmt VARCHAR2(5000);
5616 lv_errbuf VARCHAR2(240);
5617 lv_retcode NUMBER;
5618
5619 BEGIN
5620
5621 BEGIN
5622 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
5623 lv_tbl:= ' DOC_ATTACHMENTS_'||MSC_CL_COLLECTION.v_instance_code;
5624 ELSE
5625 lv_tbl:= ' MSC_DOC_ATTACHMENTS ';
5626 END IF;
5627
5628 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'Inserting data into table ' || lv_tbl);
5629 /* bulk insert statement, used in case of target/complete collection
5630 This stmt needs to be repeated for each entity_name covered */
5631
5632 lv_cursor_stmt:=
5633 'INSERT INTO '||lv_tbl
5634 ||' ( DOC_ID,'
5635 ||' SEQ_NUM,'
5636 ||' ENTITY_NAME,'
5637 ||' PK_VALUE1,'
5638 ||' PK_VALUE2,'
5639 ||' PK_VALUE3,'
5640 ||' PK_VALUE4,'
5641 ||' PK_VALUE5,'
5642 ||' DESCRIPTION,'
5643 ||' TITLE,'
5644 ||' DOC_TYPE,'
5645 ||' START_DATE_ACTIVE,'
5646 ||' END_DATE_ACTIVE,'
5647 ||' URL,'
5648 ||' MEDIA_ID,'
5649 ||' SR_INSTANCE_ID,'
5650 ||' REFRESH_NUMBER,'
5651 ||' LAST_UPDATE_DATE,'
5652 ||' LAST_UPDATED_BY,'
5653 ||' CREATION_DATE,'
5654 ||' CREATED_BY,'
5655 ||' LAST_UPDATE_LOGIN)'
5656 ||' SELECT'
5657 ||' msda.DOC_ID,'
5658 ||' msda.SEQ_NUM,'
5659 ||' msda.ENTITY_NAME,'
5660 ||' t1.INVENTORY_ITEM_ID,'
5661 ||' t2.INVENTORY_ITEM_ID,'
5662 ||' msda.PK_VALUE3,'
5663 ||' msda.PK_VALUE4,'
5664 ||' msda.PK_VALUE5,'
5665 ||' msda.DESCRIPTION,'
5666 ||' msda.TITLE,'
5667 ||' msda.DOC_TYPE,'
5668 ||' msda.START_DATE_ACTIVE,'
5669 ||' msda.END_DATE_ACTIVE,'
5670 ||' msda.URL,'
5671 ||' msda.MEDIA_ID,'
5672 ||' :v_instance_id,'
5673 ||' :v_last_collection_id,'
5674 ||' :v_current_date,'
5675 ||' :v_current_user,'
5676 ||' :v_current_date,'
5677 ||' :v_current_user,'
5678 ||' :v_current_user'
5679 ||' FROM '
5680 ||' MSC_ITEM_ID_LID t1,'
5681 ||' MSC_ITEM_ID_LID t2,'
5682 ||' MSC_ST_DOC_ATTACHMENTS msda '
5683 ||' WHERE t1.SR_INVENTORY_ITEM_ID= msda.PK_VALUE1'
5684 ||' AND t2.SR_INVENTORY_ITEM_ID= msda.PK_VALUE2'
5685 ||' AND t1.SR_INSTANCE_ID= msda.SR_INSTANCE_ID'
5686 ||' AND t2.SR_INSTANCE_ID= msda.SR_INSTANCE_ID'
5687 ||' AND msda.ENTITY_NAME = ''MSC_USER_NOTES'''
5688 ||' AND msda.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id ;
5689
5690 /* IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
5691 MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_DOC_ATTACHMENTS',MSC_CL_COLLECTION.v_instance_id, null);
5692 END IF;*/
5693
5694
5695 BEGIN
5696
5697 EXECUTE IMMEDIATE lv_cursor_stmt
5698 USING MSC_CL_COLLECTION.v_instance_id,MSC_CL_COLLECTION.v_last_collection_id,
5699 MSC_CL_COLLECTION.v_current_date,
5700 MSC_CL_COLLECTION.v_current_user,
5701 MSC_CL_COLLECTION.v_current_date,
5702 MSC_CL_COLLECTION.v_current_user,
5703 MSC_CL_COLLECTION.v_current_user;
5704
5705 COMMIT;
5706 EXCEPTION
5707 WHEN OTHERS THEN
5708 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
5709 -- IF NOT Handle_Exception(SQLCODE) THEN RAISE; END IF;
5710 END;
5711
5712
5713 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
5714 MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,lv_retcode,'MSC_DOC_ATTACHMENTS',
5715 MSC_CL_COLLECTION.v_INSTANCE_CODE, MSC_UTIL.G_ERROR);
5716
5717 IF lv_retcode = MSC_UTIL.G_ERROR THEN
5718 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
5719 RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
5720 ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
5721 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
5722 END IF;
5723
5724 END IF;
5725
5726 COMMIT;
5727
5728 EXCEPTION
5729 WHEN OTHERS THEN
5730
5731 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
5732
5733 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
5734 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
5735 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SHORT_TEXT');
5736 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DOC_ATTACHMENTS');
5737 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
5738
5739 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
5740 RAISE;
5741
5742 ELSE
5743 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
5744
5745 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
5746 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
5747 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SHORT_TEXT');
5748 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DOC_ATTACHMENTS');
5749 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
5750 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
5751 END IF;
5752 END; -- load for MSC_DOC_ATTACHMENTS
5753
5754 BEGIN -- load for MSC_SHORT_TEXT
5755
5756 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
5757 lv_tbl:= ' SHORT_TEXT_'||MSC_CL_COLLECTION.v_instance_code;
5758 ELSE
5759 lv_tbl:= ' MSC_SHORT_TEXT';
5760 END IF ;
5761
5762 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'Inserting data into table ' || lv_tbl);
5763 /* bulk insert statement, used in case of target/complete collection */
5764 lv_cursor_stmt:=
5765 ' INSERT INTO '||lv_tbl
5766 ||'( MEDIA_ID,'
5767 ||' SHORT_TEXT,'
5768 ||' SR_INSTANCE_ID,'
5769 ||' REFRESH_NUMBER,'
5770 ||' LAST_UPDATE_DATE,'
5771 ||' LAST_UPDATED_BY,'
5772 ||' CREATION_DATE,'
5773 ||' CREATED_BY,'
5774 ||' LAST_UPDATE_LOGIN)'
5775 ||' SELECT'
5776 ||' msst.MEDIA_ID,'
5777 ||' msst.SHORT_TEXT,'
5778 ||' :v_instance_id,'
5779 ||' :v_last_collection_id,'
5780 ||' :v_current_date,'
5781 ||' :v_current_user,'
5782 ||' :v_current_date,'
5783 ||' :v_current_user,'
5784 ||' :v_current_user'
5785 ||' FROM MSC_ST_SHORT_TEXT msst'
5786 ||' WHERE msSt.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id ;
5787
5788 /* IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
5789 MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_SHORT_TEXT',MSC_CL_COLLECTION.v_instance_id, null);
5790 END IF;*/
5791
5792 BEGIN
5793 EXECUTE IMMEDIATE lv_cursor_stmt
5794 USING MSC_CL_COLLECTION.v_instance_id,MSC_CL_COLLECTION.v_last_collection_id,
5795 MSC_CL_COLLECTION.v_current_date,
5796 MSC_CL_COLLECTION.v_current_user,
5797 MSC_CL_COLLECTION.v_current_date,
5798 MSC_CL_COLLECTION.v_current_user,
5799 MSC_CL_COLLECTION.v_current_user;
5800
5801 COMMIT;
5802 EXCEPTION
5803 WHEN OTHERS THEN
5804 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
5805 -- IF NOT Handle_Exception(SQLCODE) THEN RAISE; END IF;
5806 END;
5807
5808 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
5809 MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,lv_retcode,'MSC_SHORT_TEXT',
5810 MSC_CL_COLLECTION.v_INSTANCE_CODE, MSC_UTIL.G_ERROR);
5811
5812 IF lv_retcode = MSC_UTIL.G_ERROR THEN
5813 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
5814 RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
5815 ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
5816 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
5817 END IF;
5818
5819 END IF;
5820
5821 COMMIT;
5822
5823 EXCEPTION
5824 WHEN OTHERS THEN
5825
5826 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
5827
5828 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
5829 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
5830 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SHORT_TEXT');
5831 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SHORT_TEXT');
5832 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
5833
5834 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
5835 RAISE;
5836
5837 ELSE
5838 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
5839
5840 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
5841 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
5842 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SHORT_TEXT');
5843 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SHORT_TEXT');
5844 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
5845 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
5846 END IF;
5847 END; -- load for MSC_SHORT_TEXT
5848
5849
5850 END LOAD_SHORT_TEXT;
5851
5852
5853 PROCEDURE LOAD_LONG_TEXT IS
5854 c_count NUMBER:=0;
5855 lv_tbl VARCHAR2(30);
5856 lv_sql_stmt VARCHAR2(5000);
5857 lv_cursor_stmt VARCHAR2(5000);
5858 lv_errbuf VARCHAR2(240);
5859 lv_retcode NUMBER;
5860
5861 BEGIN
5862
5863 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
5864 lv_tbl:= ' LONG_TEXT_'||MSC_CL_COLLECTION.v_instance_code;
5865 ELSE
5866 lv_tbl:= ' MSC_LONG_TEXT ';
5867 END IF;
5868
5869 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'Inserting data into table ' || lv_tbl);
5870 /* bulk insert statement, used in case of target/complete collection */
5871 lv_cursor_stmt:=
5872
5873 ' INSERT INTO '||lv_tbl
5874 ||'( MEDIA_ID,'
5875 ||' LONG_TEXT,'
5876 ||' SR_INSTANCE_ID,'
5877 ||' REFRESH_NUMBER,'
5878 ||' LAST_UPDATE_DATE,'
5879 ||' LAST_UPDATED_BY,'
5880 ||' CREATION_DATE,'
5881 ||' CREATED_BY,'
5882 ||' LAST_UPDATE_LOGIN)'
5883 ||' SELECT'
5884 ||' mslt.MEDIA_ID,'
5885 ||' mslt.LONG_TEXT,'
5886 ||' :v_instance_id,'
5887 ||' :v_last_collection_id,'
5888 ||' :v_current_date,'
5889 ||' :v_current_user,'
5890 ||' :v_current_date,'
5891 ||' :v_current_user,'
5892 ||' :v_current_user'
5893 ||' FROM MSC_ST_LONG_TEXT mslt'
5894 ||' WHERE mslt.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id ;
5895
5896 /* IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
5897 MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_LONG_TEXT',MSC_CL_COLLECTION.v_instance_id, null);
5898 END IF;*/
5899
5900 BEGIN
5901 EXECUTE IMMEDIATE lv_cursor_stmt
5902 USING MSC_CL_COLLECTION.v_instance_id,MSC_CL_COLLECTION.v_last_collection_id,
5903 MSC_CL_COLLECTION.v_current_date,
5904 MSC_CL_COLLECTION.v_current_user,
5905 MSC_CL_COLLECTION.v_current_date,
5906 MSC_CL_COLLECTION.v_current_user,
5907 MSC_CL_COLLECTION.v_current_user;
5908
5909 COMMIT;
5910
5911 EXCEPTION
5912 WHEN OTHERS THEN
5913 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
5914 -- IF NOT Handle_Exception(SQLCODE) THEN RAISE; END IF;
5915 END;
5916
5917 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
5918 MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,lv_retcode,'MSC_LONG_TEXT',
5919 MSC_CL_COLLECTION.v_INSTANCE_CODE, MSC_UTIL.G_ERROR);
5920
5921 IF lv_retcode = MSC_UTIL.G_ERROR THEN
5922 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
5923 RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
5924 ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
5925 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
5926 END IF;
5927
5928 END IF;
5929
5930
5931 COMMIT;
5932
5933 EXCEPTION
5934 WHEN OTHERS THEN
5935
5936 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
5937
5938 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
5939 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
5940 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_LONG_TEXT');
5941 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_LONG_TEXT');
5942 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
5943
5944 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
5945 RAISE;
5946
5947 ELSE
5948 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
5949
5950 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
5951 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
5952 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_LONG_TEXT');
5953 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_LONG_TEXT');
5954 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
5955 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
5956 END IF;
5957
5958
5959 END LOAD_LONG_TEXT;
5960
5961
5962 PROCEDURE PURGE_STALE_CURRENCY_CONV IS
5963 v_pbs number;
5964
5965 BEGIN
5966
5967 v_pbs := TO_NUMBER(FND_PROFILE.VALUE('MRP_PURGE_BATCH_SIZE'));
5968
5969 Loop
5970 Delete from MSC_CURRENCY_CONVERSIONS
5971 where conv_date > (sysdate + MSC_CL_OTHER_PULL.G_MSC_FUTURE_DAYS) or conv_date < (sysdate - MSC_CL_OTHER_PULL.G_MSC_PAST_DAYS)
5972 And rownum < v_pbs;
5973 Exit when sql%rowcount = 0;
5974
5975 End loop;
5976 commit;
5977
5978 END PURGE_STALE_CURRENCY_CONV;
5979
5980
5981 END MSC_CL_OTHER_ODS_LOAD;