[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.10 2008/02/29 13:05:51 vpalla noship $ */
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 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
3808 BEGIN
3809 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
3810
3811 MSC_CL_COLLECTION.DELETE_MSC_TABLE ('MSC_SALES_CHANNEL', MSC_CL_COLLECTION.v_instance_id, NULL);
3812
3813 END IF;
3814
3815 INSERT INTO MSC_SR_LOOKUPS (
3816 LOOKUP_TYPE,
3817 LOOKUP_CODE,
3818 MEANING,
3819 DESCRIPTION,
3820 FROM_DATE,
3821 TO_DATE,
3822 ENABLED_FLAG,
3823 SR_INSTANCE_ID,
3824 LAST_UPDATE_DATE,
3825 LAST_UPDATED_BY,
3826 CREATION_DATE,
3827 CREATED_BY)
3828 SELECT
3829 'SALES_CHANNEL',
3830 SALES_CHANNEL,
3831 MEANING,
3832 DESCRIPTION,
3833 FROM_DATE,
3834 TO_DATE,
3835 ENABLED_FLAG,
3836 MSC_CL_COLLECTION.v_instance_id,
3837 MSC_CL_COLLECTION.v_current_date,
3838 MSC_CL_COLLECTION.v_current_user,
3839 MSC_CL_COLLECTION.v_current_date,
3840 MSC_CL_COLLECTION.v_current_user
3841 FROM MSC_ST_SALES_CHANNEL
3842 WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
3843
3844 COMMIT;
3845
3846 EXCEPTION
3847 WHEN OTHERS THEN
3848
3849 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'An error has occurred.');
3850 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3851 RAISE;
3852
3853 END LOAD_SALES_CHANNEL;
3854
3855
3856 PROCEDURE LOAD_FISCAL_CALENDAR IS
3857
3858 BEGIN
3859
3860 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
3861
3862 MSC_CL_COLLECTION.DELETE_MSC_TABLE ('MSC_CALENDARS', MSC_CL_COLLECTION.v_instance_id, Null, 'AND CALENDAR_TYPE=''FISCAL''');
3863
3864 END IF;
3865
3866 INSERT INTO MSC_CALENDARS
3867 (
3868 CALENDAR_CODE,
3869 CALENDAR_TYPE,
3870 REFRESH_ID,
3871 SR_INSTANCE_ID,
3872 LAST_UPDATE_DATE,
3873 LAST_UPDATED_BY,
3874 CREATION_DATE,
3875 CREATED_BY
3876 )
3877 SELECT
3878 DISTINCT
3879 CALENDAR_CODE,
3880 CALENDAR_TYPE,
3881 MSC_CL_COLLECTION.v_last_collection_id,
3882 MSC_CL_COLLECTION.v_instance_id,
3883 MSC_CL_COLLECTION.v_current_date,
3884 MSC_CL_COLLECTION.v_current_user,
3885 MSC_CL_COLLECTION.v_current_date,
3886 MSC_CL_COLLECTION.v_current_user
3887 FROM MSC_ST_CALENDAR_MONTHS
3888 WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3889 AND CALENDAR_TYPE='FISCAL';
3890
3891 COMMIT;
3892
3893
3894 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
3895
3896 MSC_CL_COLLECTION.DELETE_MSC_TABLE ('MSC_CALENDAR_MONTHS', MSC_CL_COLLECTION.v_instance_id, NULL);
3897
3898 END IF;
3899
3900 INSERT INTO MSC_CALENDAR_MONTHS (
3901 CALENDAR_CODE,
3902 CALENDAR_TYPE,
3903 YEAR,
3904 YEAR_DESCRIPTION,
3905 YEAR_START_DATE,
3906 YEAR_END_DATE,
3907 QUARTER,
3908 QUARTER_DESCRIPTION,
3909 QUARTER_START_DATE,
3910 QUARTER_END_DATE,
3911 MONTH,
3912 MONTH_DESCRIPTION,
3913 MONTH_START_DATE,
3914 MONTH_END_DATE,
3915 SR_INSTANCE_ID,
3916 LAST_UPDATE_DATE,
3917 LAST_UPDATED_BY,
3918 CREATION_DATE,
3919 CREATED_BY,
3920 LAST_UPDATE_LOGIN)
3921 SELECT
3922 CALENDAR_CODE,
3923 CALENDAR_TYPE,
3924 YEAR,
3925 YEAR_DESCRIPTION,
3926 YEAR_START_DATE,
3927 YEAR_END_DATE,
3928 QUARTER,
3929 QUARTER_DESCRIPTION,
3930 QUARTER_START_DATE,
3931 QUARTER_END_DATE,
3932 MONTH,
3933 MONTH_DESCRIPTION,
3934 MONTH_START_DATE,
3935 MONTH_END_DATE,
3936 MSC_CL_COLLECTION.v_instance_id,
3937 MSC_CL_COLLECTION.v_current_date,
3938 MSC_CL_COLLECTION.v_current_user,
3939 MSC_CL_COLLECTION.v_current_date,
3940 MSC_CL_COLLECTION.v_current_user,
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
3948 EXCEPTION
3949 WHEN OTHERS THEN
3950
3951 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'An error has occurred.');
3952 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3953 RAISE;
3954
3955 END LOAD_FISCAL_CALENDAR;
3956
3957 /* LOAD_TRIP added for Collecting Trip and Trip Stops for Deployment Planning */
3958 PROCEDURE LOAD_TRIP IS
3959
3960 CURSOR c1 IS
3961 SELECT
3962 mst.TRIP_ID,
3963 mst.NAME,
3964 mst.SHIP_METHOD_CODE,
3965 mst.PLANNED_FLAG,
3966 mst.STATUS_CODE,
3967 mst.WEIGHT_CAPACITY,
3968 mst.WEIGHT_UOM,
3969 mst.VOLUME_CAPACITY,
3970 mst.VOLUME_UOM,
3971 mst.SR_INSTANCE_ID
3972 FROM MSC_ST_TRIPS mst
3973 WHERE mst.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3974 AND mst.DELETED_FLAG= MSC_UTIL.SYS_NO;
3975
3976 CURSOR c1_d IS
3977 SELECT
3978 mst.TRIP_ID,
3979 mst.SR_INSTANCE_ID
3980 FROM MSC_ST_TRIPS mst
3981 WHERE mst.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3982 AND mst.DELETED_FLAG= MSC_UTIL.SYS_YES;
3983
3984 CURSOR c2 IS
3985 SELECT
3986 STOP_ID,
3987 STOP_LOCATION_ID,
3988 STATUS_CODE,
3989 STOP_SEQUENCE_NUMBER,
3990 PLANNED_ARRIVAL_DATE,
3991 PLANNED_DEPARTURE_DATE,
3992 TRIP_ID,
3993 mst.SR_INSTANCE_ID
3994 FROM MSC_ST_TRIP_STOPS mst
3995 WHERE mst.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3996 AND mst.DELETED_FLAG= MSC_UTIL.SYS_NO;
3997
3998 CURSOR c2_d IS
3999 SELECT
4000 mst.STOP_ID,
4001 mst.SR_INSTANCE_ID
4002 FROM MSC_ST_TRIP_STOPS mst
4003 WHERE mst.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
4004 AND mst.DELETED_FLAG= MSC_UTIL.SYS_YES;
4005
4006
4007 c_count NUMBER:= 0;
4008 lv_tbl VARCHAR2(30);
4009 lv_sql_stmt VARCHAR2(5000);
4010
4011 BEGIN
4012
4013 IF MSC_CL_COLLECTION.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
4014
4015 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
4016
4017 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_TRIPS', MSC_CL_COLLECTION.v_instance_id, -1);
4018
4019 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_TRIP_STOPS', MSC_CL_COLLECTION.v_instance_id, -1);
4020
4021 END IF; -- MSC_CL_COLLECTION.v_is_complete_refresh
4022
4023 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
4024
4025 FOR c_rec IN c1_d LOOP
4026
4027 DELETE MSC_TRIPS
4028 WHERE PLAN_ID= -1
4029 AND TRIP_ID= c_rec.TRIP_ID
4030 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
4031
4032 END LOOP;
4033
4034 FOR c_rec IN c2_d LOOP
4035
4036 DELETE MSC_TRIP_STOPS
4037 WHERE PLAN_ID= -1
4038 AND STOP_ID= c_rec.STOP_ID
4039 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
4040
4041 END LOOP;
4042
4043 END IF;
4044
4045 c_count:= 0;
4046
4047 FOR c_rec IN c1 LOOP
4048
4049 BEGIN
4050
4051 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
4052
4053 UPDATE MSC_TRIPS
4054 SET
4055 NAME = c_rec.NAME,
4056 SHIP_METHOD_CODE = c_rec.SHIP_METHOD_CODE,
4057 PLANNED_FLAG = c_rec.PLANNED_FLAG,
4058 STATUS_CODE = c_rec.STATUS_CODE,
4059 WEIGHT_CAPACITY = c_rec.WEIGHT_CAPACITY,
4060 WEIGHT_UOM = c_rec.WEIGHT_UOM,
4061 VOLUME_CAPACITY = c_rec.VOLUME_CAPACITY,
4062 VOLUME_UOM = c_rec.VOLUME_UOM,
4063 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
4064 LAST_UPDATED_BY = MSC_CL_COLLECTION.v_current_user,
4065 LAST_UPDATE_DATE = MSC_CL_COLLECTION.v_current_date,
4066 LAST_UPDATE_LOGIN = MSC_CL_COLLECTION.v_current_user
4067 WHERE PLAN_ID= -1
4068 AND TRIP_ID= c_rec.TRIP_ID
4069 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
4070
4071 END IF;
4072
4073 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
4074
4075 INSERT INTO MSC_TRIPS
4076 ( PLAN_ID,
4077 TRIP_ID,
4078 NAME,
4079 SHIP_METHOD_CODE,
4080 PLANNED_FLAG,
4081 STATUS_CODE,
4082 WEIGHT_CAPACITY,
4083 WEIGHT_UOM,
4084 VOLUME_CAPACITY,
4085 VOLUME_UOM,
4086 SR_INSTANCE_ID,
4087 REFRESH_NUMBER,
4088 LAST_UPDATE_DATE,
4089 LAST_UPDATED_BY,
4090 CREATION_DATE,
4091 CREATED_BY)
4092 VALUES
4093 ( -1,
4094 c_rec.TRIP_ID,
4095 c_rec.NAME,
4096 c_rec.SHIP_METHOD_CODE,
4097 c_rec.PLANNED_FLAG,
4098 c_rec.STATUS_CODE,
4099 c_rec.WEIGHT_CAPACITY,
4100 c_rec.WEIGHT_UOM,
4101 c_rec.VOLUME_CAPACITY,
4102 c_rec.VOLUME_UOM,
4103 c_rec.SR_INSTANCE_ID,
4104 MSC_CL_COLLECTION.v_last_collection_id,
4105 MSC_CL_COLLECTION.v_current_date,
4106 MSC_CL_COLLECTION.v_current_user,
4107 MSC_CL_COLLECTION.v_current_date,
4108 MSC_CL_COLLECTION.v_current_user );
4109
4110 END IF; --SQL%NOTFOUND
4111
4112 c_count:= c_count+1;
4113
4114 IF c_count> MSC_CL_COLLECTION.PBS THEN
4115 COMMIT;
4116 c_count:= 0;
4117 END IF;
4118
4119 EXCEPTION WHEN OTHERS THEN
4120
4121 IF SQLCODE IN (-01653,-01650,-01562,-01683) THEN
4122
4123 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4124 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4125 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRIP');
4126 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TRIPS');
4127 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4128
4129 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4130 RAISE;
4131
4132 ELSE
4133 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
4134
4135 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4136 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4137 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRIP');
4138 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TRIPS');
4139 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4140
4141
4142 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4143 FND_MESSAGE.SET_TOKEN('COLUMN', 'NAME');
4144 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.NAME);
4145 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4146 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4147
4148 END IF;
4149
4150 END;
4151
4152 END LOOP;
4153
4154
4155 c_count:= 0;
4156
4157 FOR c_rec IN c2 LOOP
4158
4159 BEGIN
4160
4161 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
4162
4163 UPDATE MSC_TRIP_STOPS
4164 SET
4165 STOP_LOCATION_ID = c_rec.STOP_LOCATION_ID,
4166 STATUS_CODE = c_rec.STATUS_CODE,
4167 STOP_SEQUENCE_NUMBER = c_rec.STOP_SEQUENCE_NUMBER,
4168 PLANNED_ARRIVAL_DATE = c_rec.PLANNED_ARRIVAL_DATE,
4169 PLANNED_DEPARTURE_DATE = c_rec.PLANNED_DEPARTURE_DATE,
4170 TRIP_ID = c_rec.TRIP_ID,
4171 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
4172 LAST_UPDATED_BY = MSC_CL_COLLECTION.v_current_user,
4173 LAST_UPDATE_DATE = MSC_CL_COLLECTION.v_current_date,
4174 LAST_UPDATE_LOGIN = MSC_CL_COLLECTION.v_current_user
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 IF;
4180
4181 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
4182
4183 INSERT INTO MSC_TRIP_STOPS
4184 ( PLAN_ID,
4185 STOP_ID,
4186 STOP_LOCATION_ID,
4187 STATUS_CODE,
4188 STOP_SEQUENCE_NUMBER,
4189 PLANNED_ARRIVAL_DATE,
4190 PLANNED_DEPARTURE_DATE,
4191 TRIP_ID,
4192 SR_INSTANCE_ID,
4193 REFRESH_NUMBER,
4194 LAST_UPDATE_DATE,
4195 LAST_UPDATED_BY,
4196 CREATION_DATE,
4197 CREATED_BY)
4198 VALUES
4199 ( -1,
4200 c_rec.STOP_ID,
4201 c_rec.STOP_LOCATION_ID,
4202 c_rec.STATUS_CODE,
4203 c_rec.STOP_SEQUENCE_NUMBER,
4204 c_rec.PLANNED_ARRIVAL_DATE,
4205 c_rec.PLANNED_DEPARTURE_DATE,
4206 c_rec.TRIP_ID,
4207 c_rec.SR_INSTANCE_ID,
4208 MSC_CL_COLLECTION.v_last_collection_id,
4209 MSC_CL_COLLECTION.v_current_date,
4210 MSC_CL_COLLECTION.v_current_user,
4211 MSC_CL_COLLECTION.v_current_date,
4212 MSC_CL_COLLECTION.v_current_user );
4213
4214 END IF; --SQL%NOTFOUND
4215
4216 c_count:= c_count+1;
4217
4218 IF c_count> MSC_CL_COLLECTION.PBS THEN
4219 COMMIT;
4220 c_count:= 0;
4221 END IF;
4222
4223 EXCEPTION WHEN OTHERS THEN
4224
4225 IF SQLCODE IN (-01653,-01650,-01562,-01683) THEN
4226
4227 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4228 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4229 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRIP');
4230 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TRIP_STOPS');
4231 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4232
4233 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4234 RAISE;
4235
4236 ELSE
4237 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
4238
4239 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4240 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4241 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_TRIP');
4242 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_TRIP_STOPS');
4243 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4244
4245
4246 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4247 FND_MESSAGE.SET_TOKEN('COLUMN', 'STOP_ID');
4248 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.STOP_ID));
4249 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4250
4251 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4252
4253 END IF;
4254
4255 END;
4256
4257 END LOOP;
4258
4259 COMMIT;
4260
4261 END IF; -- v_apps_ver >= G_APPS115
4262 END LOAD_TRIP;
4263
4264 --- for bug # 6469722
4265 PROCEDURE LOAD_CURRENCY_CONVERSION IS
4266
4267 cnt number := 0;
4268 reqid number;
4269 v_sql_stmt VARCHAR2(2000);
4270
4271 BEGIN
4272
4273 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
4274
4275 Begin
4276 select 1 into cnt from MSC_CURRENCY_CONVERSIONS
4277 where (to_currency = MSC_CL_OTHER_PULL.G_MSC_HUB_CURR_CODE and
4278 conv_type = MSC_CL_OTHER_PULL.G_MSC_CURR_CONV_TYPE)
4279 and rownum < 2;
4280
4281 exception
4282 when no_data_found then
4283 cnt :=0;
4284
4285 end;
4286
4287
4288 If (cnt = 0) then
4289 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE('MSC_CURRENCY_CONVERSIONS');
4290 End if;
4291
4292
4293 MERGE INTO MSC_CURRENCY_CONVERSIONS mcc
4294 USING (Select * from MSC_ST_CURRENCY_CONVERSIONS where sr_instance_id = MSC_CL_COLLECTION.v_instance_id) mst
4295 ON (mcc.from_currency = mst.from_currency
4296 AND mcc.to_currency = mst.to_currency
4297 AND mcc.conv_date = mst.conv_date
4298 AND mcc.conv_type = mst.conv_type
4299 AND mcc.conv_type = MSC_CL_OTHER_PULL.G_MSC_CURR_CONV_TYPE)
4300 WHEN MATCHED THEN
4301 UPDATE SET mcc.conv_rate = mst.conv_rate,
4302 mcc.last_coll_instance_id = mst.sr_instance_id
4303 WHEN NOT MATCHED THEN
4304 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)
4305 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);
4306 COMMIT;
4307 END IF;
4308
4309 Begin
4310 /* Submit the CP for Purging old rows */
4311
4312 reqid := FND_REQUEST.SUBMIT_REQUEST('MSC',
4313 'MSCCLMISC',
4314 Null,
4315 Null,
4316 False,
4317 'MSC_CL_OTHER_ODS_LOAD',
4318 'PURGE_STALE_CURRENCY_CONV',
4319 Null);
4320 commit ;
4321 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Submitted CP for purge stale currency data. '|| reqid);
4322 EXCEPTION
4323 WHEN OTHERS THEN
4324 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4325 RAISE;
4326 End;
4327
4328 Begin
4329 /* submit CP for refreshing MV */
4330 reqid := FND_REQUEST.SUBMIT_REQUEST('MSC',
4331 'MSCCLMISC',
4332 Null,
4333 Null,
4334 False,
4335 'MSC_PHUB_PKG',
4336 'REFRESH_MVS',
4337 1);
4338 commit ;
4339 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Submitted CP to refresh MV. '|| reqid);
4340 EXCEPTION
4341 WHEN OTHERS THEN
4342 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4343 RAISE;
4344 End;
4345
4346 EXCEPTION
4347 WHEN OTHERS THEN
4348
4349 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'An error has occurred.');
4350 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4351 RAISE;
4352
4353 END LOAD_CURRENCY_CONVERSION;
4354
4355 PROCEDURE LOAD_DELIVERY_DETAILS IS -- for bug 6730983
4356 lv_sql_stmt VARCHAR2(10000);
4357 i NUMBER := -1;
4358 reqid number;
4359 BEGIN
4360 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
4361 /* Updating Who cols of Staging Tables */
4362 UPDATE MSC_ST_DELIVERY_DETAILS
4363 SET
4364 REFRESH_NUMBER = MSC_CL_COLLECTION.v_last_collection_id,
4365 LAST_UPDATE_DATE = MSC_CL_COLLECTION.v_current_date,
4366 LAST_UPDATED_BY = MSC_CL_COLLECTION.v_current_user,
4367 CREATION_DATE = MSC_CL_COLLECTION.v_current_date,
4368 CREATED_BY = MSC_CL_COLLECTION.v_current_user,
4369 LAST_UPDATE_LOGIN =MSC_CL_COLLECTION.v_current_user
4370 WHERE SR_INSTANCE_ID = MSC_CL_COLLECTION.v_instance_id;
4371
4372 COMMIT;
4373
4374 /* Initialize the list */
4375 IF NOT MSC_CL_EXCHANGE_PARTTBL.Initialize_SWAP_Tbl_List(MSC_CL_COLLECTION.v_instance_id,MSC_CL_COLLECTION.v_instance_code) THEN
4376 RAISE MSC_CL_COLLECTION.EXCHANGE_PARTN_ERROR;
4377 END IF;
4378 /* Get the swap table index number in the list*/
4379 i := MSC_CL_EXCHANGE_PARTTBL.get_SWAP_table_index('MSC_DELIVERY_DETAILS'); --ods table name
4380 IF i = -1 THEN
4381 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Table not in the list of SWAP partition');
4382 RAISE MSC_CL_COLLECTION.EXCHANGE_PARTN_ERROR;
4383 END IF;
4384 /* Do phase 1 exchange*/
4385
4386 IF NOT MSC_CL_EXCHANGE_PARTTBL.EXCHANGE_SINGLE_TAB_PARTN (
4387 MSC_CL_EXCHANGE_PARTTBL.v_swapTblList(i).stg_table_name,
4388 MSC_CL_EXCHANGE_PARTTBL.v_swapTblList(i).stg_table_partn_name,
4389 MSC_CL_EXCHANGE_PARTTBL.v_swapTblList(i).temp_table_name,
4390 MSC_UTIL.SYS_NO ) THEN
4391 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Exchange partition failed');
4392 RAISE MSC_CL_COLLECTION.EXCHANGE_PARTN_ERROR;
4393 END IF;
4394
4395 EXECUTE IMMEDIATE ' Update msc_coll_parameters set '
4396 || MSC_CL_EXCHANGE_PARTTBL.v_swapTblList(i).column_name || ' = '
4397 || MSC_CL_COLLECTION.G_STG_ODS_SWP_PHASE_1
4398 || ' where instance_id = ' || MSC_CL_COLLECTION.v_instance_id;
4399
4400 commit;
4401 /* Add code to copy required data from ods table to this temp table*/
4402
4403 lv_sql_stmt := ' INSERT INTO '||MSC_CL_EXCHANGE_PARTTBL.v_swapTblList(i).temp_table_name
4404 ||' ('
4405 ||' SR_INSTANCE_ID,'
4406 ||' DELIVERY_DETAIL_ID,'
4407 ||' SOURCE_CODE,'
4408 ||' SOURCE_HEADER_ID,'
4409 ||' SOURCE_LINE_ID,'
4410 ||' SOURCE_HEADER_NUMBER,'
4411 ||' SHIP_SET_ID,'
4412 ||' ARRIVAL_SET_ID,'
4413 ||' SHIP_FROM_LOCATION_ID,'
4414 ||' ORGANIZATION_ID,'
4415 ||' SHIP_TO_LOCATION_ID,'
4416 ||' SHIP_TO_SITE_USE_ID,'
4417 ||' DELIVER_TO_LOCATION_ID,'
4418 ||' DELIVER_TO_SITE_USE_ID,'
4419 ||' CANCELLED_QUANTITY,'
4420 ||' REQUESTED_QUANTITY,'
4421 ||' REQUESTED_QUANTITY_UOM,'
4422 ||' SHIPPED_QUANTITY,'
4423 ||' DELIVERED_QUANTITY,'
4424 ||' DATE_REQUESTED,'
4425 ||' DATE_SCHEDULED,'
4426 ||' OPERATING_UNIT,'
4427 ||' INV_INTERFACED_FLAG,'
4428 ||' EARLIEST_PICKUP_DATE,'
4429 ||' LATEST_PICKUP_DATE,'
4430 ||' EARLIEST_DROPOFF_DATE,'
4431 ||' LATEST_DROPOFF_DATE,'
4432 ||' REFRESH_NUMBER,'
4433 ||' LAST_UPDATE_DATE,'
4434 ||' LAST_UPDATED_BY,'
4435 ||' CREATION_DATE,'
4436 ||' CREATED_BY,'
4437 ||' LAST_UPDATE_LOGIN'
4438 ||' ) '
4439 ||' SELECT'
4440 ||' SR_INSTANCE_ID,'
4441 ||' DELIVERY_DETAIL_ID,'
4442 ||' SOURCE_CODE,'
4443 ||' SOURCE_HEADER_ID,'
4444 ||' SOURCE_LINE_ID,'
4445 ||' SOURCE_HEADER_NUMBER,'
4446 ||' SHIP_SET_ID,'
4447 ||' ARRIVAL_SET_ID,'
4448 ||' SHIP_FROM_LOCATION_ID,'
4449 ||' ORGANIZATION_ID,'
4450 ||' SHIP_TO_LOCATION_ID,'
4451 ||' SHIP_TO_SITE_USE_ID,'
4452 ||' DELIVER_TO_LOCATION_ID,'
4453 ||' DELIVER_TO_SITE_USE_ID,'
4454 ||' CANCELLED_QUANTITY,'
4455 ||' REQUESTED_QUANTITY,'
4456 ||' REQUESTED_QUANTITY_UOM,'
4457 ||' SHIPPED_QUANTITY,'
4458 ||' DELIVERED_QUANTITY,'
4459 ||' DATE_REQUESTED,'
4460 ||' DATE_SCHEDULED,'
4461 ||' OPERATING_UNIT,'
4462 ||' INV_INTERFACED_FLAG,'
4463 ||' EARLIEST_PICKUP_DATE,'
4464 ||' LATEST_PICKUP_DATE,'
4465 ||' EARLIEST_DROPOFF_DATE,'
4466 ||' LATEST_DROPOFF_DATE,'
4467 ||' REFRESH_NUMBER,'
4468 ||' LAST_UPDATE_DATE,'
4469 ||' LAST_UPDATED_BY,'
4470 ||' CREATION_DATE,'
4471 ||' CREATED_BY,'
4472 ||' LAST_UPDATE_LOGIN'
4473 ||' FROM MSC_DELIVERY_DETAILS '
4474 ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
4475 ||' and organization_id not '|| msc_Util.v_in_org_str;
4476
4477
4478 EXECUTE IMMEDIATE lv_sql_stmt;
4479 /* Add code to create indexes on this temp table*/
4480
4481
4482 COMMIT;
4483 Begin
4484 /* Submit the CP for Purging MSC_TRANSPORTATION_UPDATES */
4485
4486 reqid := FND_REQUEST.SUBMIT_REQUEST('MSC',
4487 'MSCCLMISC',
4488 Null,
4489 Null,
4490 False,
4491 'MSC_WS_OTM_BPEL',
4492 'PURGETRANSPORTATIONUPDATES',
4493 Null);
4494 commit ;
4495 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Submitted CP for purge MSC_TRANSPORTATION_UPDATES. '|| reqid);
4496 EXCEPTION
4497 WHEN OTHERS THEN
4498 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4499 RAISE;
4500 End;
4501 END IF; -- MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh
4502 END LOAD_DELIVERY_DETAILS;
4503
4504 PROCEDURE PURGE_STALE_CURRENCY_CONV IS
4505 v_pbs number;
4506
4507 BEGIN
4508
4509 v_pbs := TO_NUMBER(FND_PROFILE.VALUE('MRP_PURGE_BATCH_SIZE'));
4510
4511 Loop
4512 Delete from MSC_CURRENCY_CONVERSIONS
4513 where conv_date > (sysdate + MSC_CL_OTHER_PULL.G_MSC_FUTURE_DAYS) or conv_date < (sysdate - MSC_CL_OTHER_PULL.G_MSC_PAST_DAYS)
4514 And rownum < v_pbs;
4515 Exit when sql%rowcount = 0;
4516
4517 End loop;
4518 commit;
4519
4520 END PURGE_STALE_CURRENCY_CONV;
4521
4522
4523 END MSC_CL_OTHER_ODS_LOAD;