[Home] [Help]
PACKAGE BODY: APPS.MSC_CL_ITEM_ODS_LOAD
Source
1 PACKAGE BODY MSC_CL_ITEM_ODS_LOAD AS -- specification
2 /* $Header: MSCLITEB.pls 120.8.12010000.5 2009/01/05 07:23:59 lsindhur ship $ */
3
4 v_sql_stmt VARCHAR2(4000);
5 lv_sql_stmt1 VARCHAR2(4000);
6 v_sub_str VARCHAR2(4000);
7 -- v_warning_flag NUMBER:= MSC_UTIL.SYS_NO; --2 be changed
8
9 -- G_COLLECT_SRP_DATA VARCHAR2(1) := NVL(FND_PROFILE.VALUE('MSC_SRP_ENABLED'),'N');
10 -- To collect SRP Data when this profile is set to Yes neds to be deleted
11 -- v_is_cont_refresh BOOLEAN; -- 2 be changed
12 v_chr9 VARCHAR2(1) := FND_GLOBAL.LOCAL_CHR(9);
13 -- v_chr10 VARCHAR2(1) := FND_GLOBAL.LOCAL_CHR(10);
14 -- v_chr13 VARCHAR2(1) := FND_GLOBAL.LOCAL_CHR(13);
15
16
17 -- PROCEDURE ADD_NEW_IMPL_ITEM_ASL;
18 -- PROCEDURE UPDATE_LEADTIME;
19 -- PROCEDURE LOAD_ABC_CLASSES;
20 -- PROCEDURE LOAD_ITEM_SUBSTITUTES; --for Product substitution
21 -- PROCEDURE ADD_NEW_IMPL_ITEM_ASL;
22 -- PROCEDURE LOAD_CATEGORY;
23 /*PROCEDURE GENERATE_ITEM_KEYS (
24 ERRBUF OUT NOCOPY VARCHAR2,
25 RETCODE OUT NOCOPY NUMBER,
26 pINSTANCE_ID IN NUMBER
27 );*/
28 -- PROCEDURE LOAD_SUPPLIER_CAPACITY;
29 -- PROCEDURE LOAD_ITEM;
30
31
32 FUNCTION Handle_Exception ( pErrorCode IN NUMBER)
33 RETURN BOOLEAN
34 IS
35 BEGIN
36 IF pErrorCode IN (-0001,-01400) THEN
37 -- These error codes can be skipped with out affecting the collections run
38 RETURN TRUE;
39 END IF;
40 RETURN FALSE;
41 END;
42
43 PROCEDURE ADD_NEW_IMPL_ITEM_ASL IS
44 TYPE CurTyp IS REF CURSOR; -- define weak REF CURSOR type -Cursor variable
45 c1 CurTyp;
46 lv_sql_stmt varchar2(5000);
47 lv_sql_stmt1 varchar2(5000);
48 lv_table_name varchar2(100);
49 lv_inventory_item_id Number;
50 lv_organization_id Number ;
51 lv_min_last_item_coll_date DATE;
52 BEGIN
53 IF (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.item_flag = MSC_UTIL.SYS_YES ) THEN
54 lv_table_name:= 'SYSTEM_ITEMS_' || MSC_CL_COLLECTION.v_INSTANCE_CODE;
55 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, ' table name in ADD_NEW_IMPL_ITEM_ASL is ' || lv_table_name);
56 else
57 lv_table_name:= 'MSC_SYSTEM_ITEMS';
58 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, ' table name in ADD_NEW_IMPL_ITEM_ASL is ' || lv_table_name);
59 End if;
60
61 v_sql_stmt := 'Select min (nvl(LAST_SUCC_ITEM_REF_TIME,SYSDATE-365000))'
62 ||' From msc_instance_orgs '
63 ||' Where sr_instance_id = ' || MSC_CL_COLLECTION.v_instance_id
64 ||' And organization_id '|| MSC_UTIL.v_in_org_str;
65
66 -- MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'the sql statement is ' || v_sql_stmt);
67
68 EXECUTE IMMEDIATE v_sql_stmt into lv_min_last_item_coll_date;
69
70 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'the sql statement is ' || v_sql_stmt);
71 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'minimum of last successful ITEM Collection refresh time is '||lv_min_last_item_coll_date);
72
73 lv_sql_stmt := 'select x.inventory_item_id , x.organization_id '
74 ||' FROM ' || lv_table_name ||' x , MSC_INSTANCE_ORGS mio'
75 ||' WHERE '
76 ||' x.organization_id = mio.organization_id '
77 ||' AND x.sr_instance_id = mio.sr_instance_id '
78 ||' AND x.organization_id '|| MSC_UTIL.v_in_org_str
79 ||' AND x.item_creation_date > nvl(mio.LAST_SUCC_ITEM_REF_TIME, SYSDATE-365000)'
80 ||' AND x.item_creation_date>:lv_min_last_item_coll_date'
81 ||' AND x.sr_instance_id = ' || MSC_CL_COLLECTION.v_instance_id
82 ||' AND x.plan_id =-1 ';
83
84 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'the sql statement is ' || lv_sql_stmt);
85
86 Open c1 for lv_sql_stmt using lv_min_last_item_coll_date ;
87 IF (c1%ISOPEN ) THEN
88 LOOP
89 FETCH c1 INTO lv_inventory_item_id,lv_organization_id;
90 EXIT WHEN c1%NOTFOUND ;
91 lv_sql_stmt1:= ' INSERT INTO MSC_ITEM_SUPPLIERS '
92 || ' ( USING_ORGANIZATION_ID,'
93 || ' SUPPLIER_ID,'
94 || ' SUPPLIER_SITE_ID,'
95 || ' INVENTORY_ITEM_ID,'
96 || ' PROCESSING_LEAD_TIME,'
97 || ' MINIMUM_ORDER_QUANTITY,'
98 || ' FIXED_LOT_MULTIPLIER,'
99 || ' DELIVERY_CALENDAR_CODE,'
100 || ' SUPPLIER_CAP_OVER_UTIL_COST,'
101 || ' PURCHASING_UNIT_OF_MEASURE,'
102 || ' SR_INSTANCE_ID2,'
103 || ' ITEM_PRICE, ' -- Item Price by Supplier Fix
104 || ' SR_INSTANCE_ID,'
105 || ' SUPPLIER_ITEM_NAME,'
106 || ' PLANNER_CODE,'
107 || ' VMI_FLAG ,'
108 || ' MIN_MINMAX_QUANTITY,'
109 || ' MAX_MINMAX_QUANTITY,'
110 || ' MAXIMUM_ORDER_QUANTITY,'
111 || ' UOM_CODE,'
112 || ' VMI_REPLENISHMENT_APPROVAL,'
113 || ' ENABLE_VMI_AUTO_REPLENISH_FLAG,'
114 || ' REPLENISHMENT_METHOD,'
115 || ' MIN_MINMAX_DAYS,'
116 || ' MAX_MINMAX_DAYS,'
117 || ' FORECAST_HORIZON,'
118 || ' FIXED_ORDER_QUANTITY,'
119 || ' VMI_REFRESH_FLAG,'
120 || ' PLAN_ID,'
121 || ' ORGANIZATION_ID,'
122 || ' REFRESH_NUMBER,'
123 || ' LAST_UPDATE_DATE,'
124 || ' LAST_UPDATED_BY,'
125 || ' CREATION_DATE,'
126 || ' CREATED_BY)'
127 || ' SELECT a.USING_ORGANIZATION_ID ,'
128 || ' a.SUPPLIER_ID,'
129 || ' a.SUPPLIER_SITE_ID,'
130 || ' a.INVENTORY_ITEM_ID,'
131 || ' a.PROCESSING_LEAD_TIME,'
132 || ' a.MINIMUM_ORDER_QUANTITY,'
133 || ' a.FIXED_LOT_MULTIPLIER,'
134 || ' a.DELIVERY_CALENDAR_CODE,'
135 || ' a.SUPPLIER_CAP_OVER_UTIL_COST,'
136 || ' a.PURCHASING_UNIT_OF_MEASURE,'
137 || ' a.SR_INSTANCE_ID2,'
138 || ' a.ITEM_PRICE, ' -- Item Price by Supplier Fix
139 || ' a.SR_INSTANCE_ID,'
140 || ' a.SUPPLIER_ITEM_NAME,'
141 || ' a.PLANNER_CODE,'
142 || ' a.VMI_FLAG ,'
143 || ' a.MIN_MINMAX_QUANTITY,'
144 || ' a.MAX_MINMAX_QUANTITY,'
145 || ' a.MAXIMUM_ORDER_QUANTITY,'
146 || ' a.UOM_CODE,'
147 || ' a.VMI_REPLENISHMENT_APPROVAL,'
148 || ' a.ENABLE_VMI_AUTO_REPLENISH_FLAG,'
149 || ' a.REPLENISHMENT_METHOD,'
150 || ' a.MIN_MINMAX_DAYS,'
151 || ' a.MAX_MINMAX_DAYS,'
152 || ' a.FORECAST_HORIZON,'
153 || ' a.FIXED_ORDER_QUANTITY,'
154 || ' 1, -1, :lv_organization_id , :v_last_collection_id,:v_current_date,'
155 || ' :v_current_user,:v_current_date,:v_current_user'
156 ||' FROM ('
157 ||' SELECT DISTINCT USING_ORGANIZATION_ID, '
158 || ' SUPPLIER_ID,'
159 || ' SUPPLIER_SITE_ID,'
160 || ' INVENTORY_ITEM_ID,'
161 || ' PROCESSING_LEAD_TIME,'
162 || ' MINIMUM_ORDER_QUANTITY,'
163 || ' FIXED_LOT_MULTIPLIER,'
164 || ' DELIVERY_CALENDAR_CODE,'
165 || ' SUPPLIER_CAP_OVER_UTIL_COST,'
166 || ' PURCHASING_UNIT_OF_MEASURE,'
167 || ' SR_INSTANCE_ID2,'
168 || ' ITEM_PRICE, ' -- Item Price by Supplier Fix
169 || ' SR_INSTANCE_ID,'
170 || ' SUPPLIER_ITEM_NAME,'
171 || ' PLANNER_CODE,'
172 || ' VMI_FLAG ,'
173 || ' MIN_MINMAX_QUANTITY,'
174 || ' MAX_MINMAX_QUANTITY,'
175 || ' MAXIMUM_ORDER_QUANTITY,'
176 || ' UOM_CODE,'
177 || ' VMI_REPLENISHMENT_APPROVAL,'
178 || ' ENABLE_VMI_AUTO_REPLENISH_FLAG,'
179 || ' REPLENISHMENT_METHOD,'
180 || ' MIN_MINMAX_DAYS,'
181 || ' MAX_MINMAX_DAYS,'
182 || ' FORECAST_HORIZON,'
183 || ' FIXED_ORDER_QUANTITY'
184 || ' FROM MSC_ITEM_SUPPLIERS '
185 || ' WHERE SR_INSTANCE_ID =' || MSC_CL_COLLECTION.v_instance_id
186 || ' AND PLAN_ID =-1 '
187 || ' AND USING_ORGANIZATION_ID =-1 '
188 || ' AND INVENTORY_ITEM_ID =' ||lv_inventory_item_id
189 || ' AND ORGANIZATION_ID '|| MSC_UTIL.v_in_org_str || ' ) a ';
190
191 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'sql in inser_Asl ' || lv_sql_stmt1);
192
193 EXECUTE IMMEDIATE lv_sql_stmt1
194 USING lv_organization_id ,MSC_CL_COLLECTION.v_last_collection_id,MSC_CL_COLLECTION.v_current_date,MSC_CL_COLLECTION.v_current_user,MSC_CL_COLLECTION.v_current_date,MSC_CL_COLLECTION.v_current_user;
195
196 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows inserted in ADD_NEW_IMPL_ITEM_ASL ' || SQL%ROWCOUNT);
197 commit ;
198 END LOOP;
199
200 END IF ;
201
202 CLOSE c1 ;
203 EXCEPTION
204
205 WHEN OTHERS THEN
206 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
207
208 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
209 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
210 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'ADD_NEW_IMPL_ITEM_ASL');
211 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ITEM_SUPPLIERS');
212 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
213
214 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
215 FND_MESSAGE.SET_TOKEN('COLUMN', 'ITEM_NAME');
216 FND_MESSAGE.SET_TOKEN('VALUE', ITEM_NAME( lv_inventory_item_id));
217 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
218
219 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
220 FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
221 FND_MESSAGE.SET_TOKEN('VALUE',
222 MSC_GET_NAME.ORG_CODE( lv_organization_id,
223 MSC_CL_COLLECTION.v_instance_id));
224 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
225
226 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
227
228 END ADD_NEW_IMPL_ITEM_ASL;
229
230 FUNCTION ITEM_NAME ( p_item_id IN NUMBER)
231 RETURN VARCHAR2
232 IS
233 CURSOR c_item( cp_item_id IN NUMBER) IS
234 SELECT ITEM_NAME
235 FROM MSC_ITEMS
236 WHERE INVENTORY_ITEM_ID= cp_item_id;
237
238 lv_item_name VARCHAR2(250):= NULL;
239 BEGIN
240 OPEN c_item( p_item_id);
241 FETCH c_item INTO lv_item_name;
242 CLOSE c_item;
243
244 RETURN lv_item_name;
245 EXCEPTION
246 WHEN OTHERS THEN
247 IF c_item%ISOPEN THEN CLOSE c_item; END IF;
248 RETURN lv_item_name;
249 END ITEM_NAME;
250
251 PROCEDURE UPDATE_LEADTIME
252 IS
253 BEGIN
254
255 update MSC_SYSTEM_ITEMS
256 set VARIABLE_LEAD_TIME=0,
257 FIXED_LEAD_TIME=0
258 where PLAN_ID=-1
259 and SR_INSTANCE_ID=MSC_CL_COLLECTION.v_instance_id
260 and (INVENTORY_ITEM_ID,organization_id) in (
261 select current_item.inventory_item_id,current_item.organization_id
262 from
263 msc_routings model_routing,
264 msc_routings option_class_routing,
265 msc_system_items model_item,
266 msc_system_items current_item
267 where
268 current_item.bom_item_type = 2
269 and model_routing.PLAN_ID=-1
270 and option_class_routing.PLAN_ID=-1
271 and model_item.PLAN_ID=-1
272 and current_item.PLAN_ID=-1
273 and current_item.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
274 and model_routing.SR_INSTANCE_ID=current_item.SR_INSTANCE_ID
275 and option_class_routing.SR_INSTANCE_ID=current_item.SR_INSTANCE_ID
276 and model_item.SR_INSTANCE_ID=current_item.SR_INSTANCE_ID
277 and model_routing.ORGANIZATION_ID = current_item.ORGANIZATION_ID
278 and option_class_routing.ORGANIZATION_ID = current_item.ORGANIZATION_ID
279 and model_item.ORGANIZATION_ID = current_item.ORGANIZATION_ID
280 and model_item.bom_item_type = 1
281 and model_item.inventory_item_id = model_routing.ASSEMBLY_ITEM_ID
282 and option_class_routing.ASSEMBLY_ITEM_ID = current_item.inventory_item_id
283 and option_class_routing.common_routing_sequence_id = model_routing.routing_sequence_id);
284 COMMIT;
285
286 EXCEPTION
287 WHEN OTHERS THEN
288 ROLLBACK;
289 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'An error has occurred.');
290 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
291
292 END UPDATE_LEADTIME;
293
294 PROCEDURE LOAD_ABC_CLASSES IS
295 lv_temp_sql_stmt VARCHAR2(2000);
296 lv_sql_stmt VARCHAR2(7500);
297 BEGIN
298
299 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh
300 OR (MSC_CL_COLLECTION.v_is_incremental_refresh AND MSC_CL_COLLECTION.v_apps_ver >= MSC_UTIL.G_APPS120)) THEN
301
302 IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
303 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ABC_CLASSES', MSC_CL_COLLECTION.v_instance_id,NULL );
304 ELSE
305 v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
306 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ABC_CLASSES', MSC_CL_COLLECTION.v_instance_id,NULL,v_sub_str);
307 END IF;
308 /*changed for bug:4765403*/
309 IF MSC_CL_COLLECTION.v_apps_ver >= MSC_UTIL.G_APPS120 THEN
310
311 lv_temp_sql_stmt :=
312 ' SELECT'
313 ||' msa.abc_class_id,'
314 ||' msa.abc_class_name,'
315 ||' msa.organization_id,'
316 ||' msa.sr_instance_id,'
317 ||' :v_current_date,'
318 ||' :v_current_user,'
319 ||' :v_current_date,'
320 ||' :v_current_user,'
321 ||' msa.sr_assignment_group_id'
322 ||' FROM MSC_ST_ABC_CLASSES msa'
323 ||' WHERE msa.sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id;
324
325 ELSE
326
327 lv_temp_sql_stmt :=
328 ' SELECT distinct'
329 ||' msi.abc_class_id,'
330 ||' msi.abc_class_name,'
331 ||' msi.organization_id,'
332 ||' msi.sr_instance_id,'
333 ||' :v_current_date,'
334 ||' :v_current_user,'
335 ||' :v_current_date,'
336 ||' :v_current_user,'
337 ||' NULL'
338 ||' FROM MSC_ST_SYSTEM_ITEMS msi'
339 ||' WHERE msi.sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
340 ||' AND msi.abc_class_id is not null'
341 ||' AND msi.abc_class_name is not null';
342
343 END IF;
344
345 BEGIN
346
347 lv_sql_stmt :=
348 ' INSERT INTO MSC_ABC_CLASSES'
349 ||' ( ABC_CLASS_ID,'
350 ||' ABC_CLASS_NAME,'
351 ||' ORGANIZATION_ID,'
352 ||' SR_INSTANCE_ID,'
353 ||' LAST_UPDATE_DATE,'
354 ||' LAST_UPDATED_BY,'
355 ||' CREATION_DATE,'
356 ||' CREATED_BY,'
357 ||' SR_ASSIGNMENT_GROUP_ID)'
358 ||lv_temp_sql_stmt;
359
360 EXECUTE IMMEDIATE lv_sql_stmt USING MSC_CL_COLLECTION.v_current_date,MSC_CL_COLLECTION.v_current_user,MSC_CL_COLLECTION.v_current_date,MSC_CL_COLLECTION.v_current_user;
361
362
363 COMMIT;
364
365 EXCEPTION
366 WHEN OTHERS THEN
367 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
368
369 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
370 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
371 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ABC_CLASSES');
372 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ABC_CLASSES');
373 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
374
375 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
376 RAISE;
377
378 ELSE
379 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
380 null;
381
382 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
383 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
384 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ABC_CLASSES');
385 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ABC_CLASSES');
386 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
387
388 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
389 END IF;
390
391 END;
392 END IF;
393
394 END LOAD_ABC_CLASSES;
395 --==================================================================
396
397 PROCEDURE LOAD_ITEM_SUBSTITUTES IS
398
399 c_count NUMBER:=0;
400 lv_tbl VARCHAR2(30);
401 lv_sql_stmt VARCHAR2(5000);
402 lv_sql_stmt1 VARCHAR2(5000);
403 lv_cursor_stmt VARCHAR2(5000);
404 lv_cursor_stmt1 VARCHAR2(5000);
405
406 lv_errbuf VARCHAR2(240);
407 lv_retcode NUMBER;
408
409 CURSOR c1 IS
410 SELECT
411 t1.INVENTORY_ITEM_ID HIGHER_ITEM_ID,
412 t2.INVENTORY_ITEM_ID LOWER_ITEM_ID,
413 msis.RECIPROCAL_FLAG,
414 msis.SUBSTITUTION_SET,
415 msis.ORGANIZATION_ID,
416 nvl(msis.EFFECTIVE_DATE,sysdate) EFFECTIVE_DATE,
417 msis.DISABLE_DATE,
418 msis.RELATIONSHIP_TYPE,
419 msis.PARTIAL_FULFILLMENT_FLAG,
420 tp.TP_ID CUSTOMER_ID,
421 tps.TP_SITE_ID CUSTOMER_SITE_ID
422 FROM MSC_TP_SITE_ID_LID tps,
423 MSC_TP_ID_LID tp,
424 MSC_ITEM_ID_LID t1,
425 MSC_ITEM_ID_LID t2,
426 MSC_ST_ITEM_SUBSTITUTES msis
427 WHERE t1.SR_INVENTORY_ITEM_ID= msis.HIGHER_ITEM_ID
428 AND t1.SR_INSTANCE_ID= msis.SR_INSTANCE_ID
429 AND t2.SR_INVENTORY_ITEM_ID = msis.LOWER_ITEM_ID
430 AND t2.SR_INSTANCE_ID = msis.SR_INSTANCE_ID
431 AND tp.SR_TP_ID(+)= msis.CUSTOMER_ID
432 AND tp.SR_INSTANCE_ID(+)= msis.SR_INSTANCE_ID
433 AND tp.PARTNER_TYPE(+) = 2
434 AND tps.SR_TP_SITE_ID(+)= msis.CUSTOMER_SITE_ID
435 AND tps.SR_INSTANCE_ID(+)= msis.SR_INSTANCE_ID
436 AND tps.PARTNER_TYPE(+)= 2
437 AND msis.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
438 AND msis.relationship_type=2
439 AND msis.deleted_flag=MSC_UTIL.SYS_NO;
440
441 CURSOR c2 IS
442 SELECT
443 t1.INVENTORY_ITEM_ID HIGHER_ITEM_ID,
444 t2.INVENTORY_ITEM_ID LOWER_ITEM_ID,
445 msis.RECIPROCAL_FLAG,
446 msis.SUBSTITUTION_SET,
447 msis.ORGANIZATION_ID,
448 msis.RELATIONSHIP_TYPE,
449 msis.PARTIAL_FULFILLMENT_FLAG,
450 nvl(msis.EFFECTIVE_DATE,sysdate) EFFECTIVE_DATE,
451 msis.DISABLE_DATE
452 FROM --MSC_TP_SITE_ID_LID tps,
453 --MSC_TP_ID_LID tp,
454 MSC_ITEM_ID_LID t1,
455 MSC_ITEM_ID_LID t2,
456 MSC_ST_ITEM_SUBSTITUTES msis
457 WHERE t1.SR_INVENTORY_ITEM_ID= msis.HIGHER_ITEM_ID
458 AND t1.SR_INSTANCE_ID= msis.SR_INSTANCE_ID
459 AND t2.SR_INVENTORY_ITEM_ID = msis.LOWER_ITEM_ID
460 AND t2.SR_INSTANCE_ID = msis.SR_INSTANCE_ID
461 AND msis.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
462 AND msis.relationship_type in (5,8,18)
463 AND msis.deleted_flag=MSC_UTIL.SYS_NO;
464
465 TYPE CharTblTyp IS TABLE OF VARCHAR2(70);
466 TYPE NumTblTyp IS TABLE OF NUMBER;
467 TYPE dateTblTyp IS TABLE OF DATE;
468
469 lb_FetchComplete Boolean;
470 ln_rows_to_fetch Number := nvl(TO_NUMBER( FND_PROFILE.VALUE('MRP_PURGE_BATCH_SIZE')),75000);
471
472 lb_HIGHER_ITEM_ID NumTblTyp;
473 lb_LOWER_ITEM_ID NumTblTyp;
474 lb_RECIPROCAL_FLAG NumTblTyp;
475 lb_SUBSTITUTION_SET CharTblTyp;
476 lb_ORGANIZATION_ID NumTblTyp;
477 lb_EFFECTIVE_DATE dateTblTyp;
478 lb_DISABLE_DATE dateTblTyp;
479 lb_RELATIONSHIP_TYPE NumTblTyp;
480 lb_PARTIAL_FULFILLMENT_FLAG NumTblTyp;
481 lb_CUSTOMER_ID NumTblTyp;
482 lb_CUSTOMER_SITE_ID NumTblTyp;
483
484 BEGIN
485
486
487 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
488 lv_tbl:= 'ITEM_SUBSTITUTES_'||MSC_CL_COLLECTION.v_INSTANCE_CODE;
489 ELSE
490 lv_tbl:= ' MSC_ITEM_SUBSTITUTES ';
491 END IF;
492
493 /* below statement will be used to insert new recs in case of net change */
494 lv_sql_stmt :=
495 'INSERT INTO '||lv_tbl
496 ||'( PLAN_ID,'
497 ||' HIGHER_ITEM_ID,'
498 ||' LOWER_ITEM_ID,'
499 ||' RECIPROCAL_FLAG,'
500 ||' SUBSTITUTION_SET,'
501 ||' CUSTOMER_ID,'
502 ||' CUSTOMER_SITE_ID,'
503 ||' EFFECTIVE_DATE,'
504 ||' DISABLE_DATE,'
505 ||' RELATIONSHIP_TYPE,'
506 ||' PARTIAL_FULFILLMENT_FLAG,'
507 ||' USAGE_RATIO,'
508 ||' REFRESH_ID,'
509 ||' SR_INSTANCE_ID,'
510 ||' ORGANIZATION_ID, '
511 ||' LAST_UPDATE_DATE,'
512 ||' LAST_UPDATED_BY,'
513 ||' LAST_UPDATE_LOGIN,'
514 ||' CREATION_DATE,'
515 ||' CREATED_BY)'
516 ||'VALUES'
517 ||'( -1, '
518 ||' :HIGHER_ITEM_ID, '
519 ||' :LOWER_ITEM_ID, '
520 ||' :RECIPROCAL_FLAG, '
521 ||' :SUBSTITUTION_SET, '
522 ||' :CUSTOMER_ID, '
523 ||' :CUSTOMER_SITE_ID, '
524 ||' :EFFECTIVE_DATE, '
525 ||' :DISABLE_DATE, '
526 ||' :RELATIONSHIP_TYPE, '
527 ||' :PARTIAL_FULFILLMENT_FLAG, '
528 ||' 1, '
529 ||' :v_last_collection_id,'
530 ||' :v_instance_id, '
531 ||' :ORGANIZATION_ID, '
532 ||' :v_current_date, '
533 ||' :v_current_user, '
534 ||' :v_current_user, '
535 ||' :v_current_date, '
536 ||' :v_current_user)';
537
538 lv_sql_stmt1 :=
539 'INSERT INTO '||lv_tbl
540 ||'( PLAN_ID,'
541 ||' HIGHER_ITEM_ID,'
542 ||' LOWER_ITEM_ID,'
543 ||' RECIPROCAL_FLAG,'
544 ||' SUBSTITUTION_SET,'
545 ||' EFFECTIVE_DATE,'
546 ||' DISABLE_DATE,'
547 ||' RELATIONSHIP_TYPE,'
548 ||' PARTIAL_FULFILLMENT_FLAG,'
549 ||' USAGE_RATIO,'
550 ||' REFRESH_ID,'
551 ||' SR_INSTANCE_ID,'
552 ||' ORGANIZATION_ID, '
553 ||' LAST_UPDATE_DATE,'
554 ||' LAST_UPDATED_BY,'
555 ||' LAST_UPDATE_LOGIN,'
556 ||' CREATION_DATE,'
557 ||' CREATED_BY)'
558 ||'VALUES'
559 ||'( -1, '
560 ||' :HIGHER_ITEM_ID, '
561 ||' :LOWER_ITEM_ID, '
562 ||' :RECIPROCAL_FLAG, '
563 ||' :SUBSTITUTION_SET,'
564 ||' :EFFECTIVE_DATE, '
565 ||' :DISABLE_DATE, '
566 ||' :RELATIONSHIP_TYPE, '
567 ||' :PARTIAL_FULFILLMENT_FLAG,'
568 ||' 1, '
569 ||' :v_last_collection_id,'
570 ||' :v_instance_id, '
571 ||' :ORGANIZATION_ID, '
572 ||' :v_current_date, '
573 ||' :v_current_user, '
574 ||' :v_current_user, '
575 ||' :v_current_date, '
576 ||' :v_current_user)';
577
578
579
580 /* bulk insert statement, used in case of target/complete collection */
581 lv_cursor_stmt:=
582 'INSERT INTO '||lv_tbl
583 ||'( PLAN_ID,'
584 ||' HIGHER_ITEM_ID,'
585 ||' LOWER_ITEM_ID,'
586 ||' RECIPROCAL_FLAG,'
587 ||' SUBSTITUTION_SET,'
588 ||' CUSTOMER_ID,'
589 ||' CUSTOMER_SITE_ID,'
590 ||' EFFECTIVE_DATE,'
591 ||' DISABLE_DATE,'
592 ||' RELATIONSHIP_TYPE,'
593 ||' PARTIAL_FULFILLMENT_FLAG,'
594 ||' USAGE_RATIO,'
595 ||' REFRESH_ID,'
596 ||' SR_INSTANCE_ID,'
597 ||' ORGANIZATION_ID,'
598 ||' LAST_UPDATE_DATE,'
599 ||' LAST_UPDATED_BY,'
600 ||' LAST_UPDATE_LOGIN,'
601 ||' CREATION_DATE,'
602 ||' CREATED_BY)'
603 ||' SELECT'
604 ||' -1, '
605 ||' t1.INVENTORY_ITEM_ID HIGHER_ITEM_ID,'
606 ||' t2.INVENTORY_ITEM_ID LOWER_ITEM_ID,'
607 ||' msis.RECIPROCAL_FLAG,'
608 ||' msis.SUBSTITUTION_SET,'
609 ||' tp.TP_ID,'
610 ||' tps.tp_SITE_ID,'
611 ||' nvl(msis.EFFECTIVE_DATE, sysdate),'
612 ||' msis.DISABLE_DATE,'
613 ||' msis.RELATIONSHIP_TYPE,'
614 ||' msis.PARTIAL_FULFILLMENT_FLAG,'
615 ||' 1,'
616 ||' :v_last_collection_id,'
617 ||' msis.SR_INSTANCE_ID,'
618 ||' msis.ORGANIZATION_ID,'
619 ||' :v_current_date,'
620 ||' :v_current_user,'
621 ||' :v_current_user,'
622 ||' :v_current_date,'
623 ||' :v_current_user'
624 ||' FROM MSC_TP_SITE_ID_LID tps,'
625 ||' MSC_TP_ID_LID tp,'
626 ||' MSC_ITEM_ID_LID t1,'
627 ||' MSC_ITEM_ID_LID t2,'
628 ||' MSC_ST_ITEM_SUBSTITUTES msis'
629 ||' WHERE t1.SR_INVENTORY_ITEM_ID= msis.HIGHER_ITEM_ID'
630 ||' AND t1.SR_INSTANCE_ID= msis.SR_INSTANCE_ID'
631 ||' AND t2.SR_INVENTORY_ITEM_ID = msis.LOWER_ITEM_ID'
632 ||' AND t2.SR_INSTANCE_ID = msis.SR_INSTANCE_ID'
633 ||' AND tp.SR_TP_ID(+)= msis.CUSTOMER_ID'
634 ||' AND tp.SR_INSTANCE_ID(+)= msis.SR_INSTANCE_ID'
635 ||' AND tp.PARTNER_TYPE(+) = 2'
636 ||' AND tps.SR_TP_SITE_ID(+)= msis.CUSTOMER_SITE_ID'
637 ||' AND tps.SR_INSTANCE_ID(+)= msis.SR_INSTANCE_ID'
638 ||' AND tps.PARTNER_TYPE(+)= 2'
639 ||' AND msis.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id
640 ||' AND msis. RELATIONSHIP_TYPE =2'
641 ||' AND nvl(msis.deleted_flag,2)='||MSC_UTIL.SYS_NO;
642
643
644 lv_cursor_stmt1:=
645 'INSERT INTO '||lv_tbl
646 ||'( PLAN_ID,'
647 ||' HIGHER_ITEM_ID,'
648 ||' LOWER_ITEM_ID,'
649 ||' RECIPROCAL_FLAG,'
650 ||' EFFECTIVE_DATE,'
651 ||' DISABLE_DATE,'
652 ||' RELATIONSHIP_TYPE,'
653 ||' SUBSTITUTION_SET,'
654 ||' PARTIAL_FULFILLMENT_FLAG,'
655 ||' USAGE_RATIO,'
656 ||' REFRESH_ID,'
657 ||' SR_INSTANCE_ID,'
658 ||' ORGANIZATION_ID,'
659 ||' LAST_UPDATE_DATE,'
660 ||' LAST_UPDATED_BY,'
661 ||' LAST_UPDATE_LOGIN,'
662 ||' CREATION_DATE,'
663 ||' CREATED_BY)'
664 ||' SELECT'
665 ||' -1, '
666 ||' t1.INVENTORY_ITEM_ID HIGHER_ITEM_ID,'
667 ||' t2.INVENTORY_ITEM_ID LOWER_ITEM_ID,'
668 ||' msis.RECIPROCAL_FLAG,'
669 ||' nvl(msis.EFFECTIVE_DATE, sysdate),'
670 ||' msis.DISABLE_DATE,'
671 ||' msis.RELATIONSHIP_TYPE,'
672 ||' msis.SUBSTITUTION_SET,'
673 ||' msis.PARTIAL_FULFILLMENT_FLAG,'
674 ||' 1,'
675 ||' :v_last_collection_id,'
676 ||' msis.SR_INSTANCE_ID,'
677 ||' msis.ORGANIZATION_ID,'
678 ||' :v_current_date,'
679 ||' :v_current_user,'
680 ||' :v_current_user,'
681 ||' :v_current_date,'
682 ||' :v_current_user'
683 ||' FROM '
684 ||' MSC_ITEM_ID_LID t1,'
685 ||' MSC_ITEM_ID_LID t2,'
686 ||' MSC_ST_ITEM_SUBSTITUTES msis'
687 ||' WHERE t1.SR_INVENTORY_ITEM_ID= msis.HIGHER_ITEM_ID'
688 ||' AND t1.SR_INSTANCE_ID= msis.SR_INSTANCE_ID'
689 ||' AND t2.SR_INVENTORY_ITEM_ID = msis.LOWER_ITEM_ID'
690 ||' AND t2.SR_INSTANCE_ID = msis.SR_INSTANCE_ID'
691 ||' AND msis.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id
692 ||' AND msis. RELATIONSHIP_TYPE in (5,8,18)'
693 ||' AND nvl(msis.deleted_flag,2)='||MSC_UTIL.SYS_NO;
694
695 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) AND
696 MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO THEN
697 IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
698 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_SUBSTITUTES', MSC_CL_COLLECTION.v_instance_id, -1);
699 ELSE
700 v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
701 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_SUBSTITUTES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
702 END IF;
703 END IF;
704
705 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
706
707 begin
708
709 EXECUTE IMMEDIATE lv_cursor_stmt
710 USING MSC_CL_COLLECTION.v_last_collection_id,
711 MSC_CL_COLLECTION.v_current_date,
712 MSC_CL_COLLECTION.v_current_user,
713 MSC_CL_COLLECTION.v_current_user,
714 MSC_CL_COLLECTION.v_current_date,
715 MSC_CL_COLLECTION.v_current_user;
716
717 EXCEPTION
718 WHEN OTHERS THEN
719 --LOG_MESSAGE(SQLERRM);
720 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
721 IF NOT Handle_Exception(SQLCODE) THEN RAISE; END IF;
722
723 end;
724 IF (MSC_UTIL.G_COLLECT_SRP_DATA = 'Y' AND MSC_CL_COLLECTION.v_apps_ver >= MSC_UTIL.G_APPS115) THEN
725
726 begin
727 EXECUTE IMMEDIATE lv_cursor_stmt1
728 USING MSC_CL_COLLECTION.v_last_collection_id,
729 MSC_CL_COLLECTION.v_current_date,
730 MSC_CL_COLLECTION.v_current_user,
731 MSC_CL_COLLECTION.v_current_user,
732 MSC_CL_COLLECTION.v_current_date,
733 MSC_CL_COLLECTION.v_current_user;
734
735 EXCEPTION
736 WHEN OTHERS THEN
737 -- LOG_MESSAGE(SQLERRM);
738 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
739 IF NOT Handle_Exception(SQLCODE) THEN RAISE; END IF;
740 end;
741
742 END IF ;
743
744 END IF;
745
746 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
747 --Net Change
748 LOOP
749 DELETE FROM MSC_ITEM_SUBSTITUTES
750 WHERE ( HIGHER_ITEM_ID, LOWER_ITEM_ID, ORGANIZATION_ID, RELATIONSHIP_TYPE) in
751 ( select t1.INVENTORY_ITEM_ID, t2.INVENTORY_ITEM_ID, msis.ORGANIZATION_ID, msis.RELATIONSHIP_TYPE
752 from MSC_ST_ITEM_SUBSTITUTES msis,
753 MSC_ITEM_ID_LID t1,
754 MSC_ITEM_ID_LID t2
755 where
756 t1.SR_INVENTORY_ITEM_ID= msis.HIGHER_ITEM_ID
757 AND t1.SR_INSTANCE_ID= msis.SR_INSTANCE_ID
758 AND t2.SR_INVENTORY_ITEM_ID = msis.LOWER_ITEM_ID
759 --AND plan_id = -1
760 AND t2.SR_INSTANCE_ID = msis.SR_INSTANCE_ID
761 AND msis.SR_INSTANCE_ID = MSC_CL_COLLECTION.v_instance_id
762 AND DELETED_FLAG =1 )
763 AND PLAN_ID =-1
764 AND SR_INSTANCE_ID =MSC_CL_COLLECTION.v_instance_id
765 AND ROWNUM <1000;
766
767 COMMIT;
768 EXIT WHEN SQL%ROWCOUNT =0 ;
769 END LOOP;
770 COMMIT;
771
772
773 open c1;
774
775 IF (c1%ISOPEN) THEN
776 LOOP
777
778 IF (lb_FetchComplete) THEN
779 EXIT;
780 END IF;
781
782 FETCH c1 BULK COLLECT INTO
783 lb_HIGHER_ITEM_ID,
784 lb_LOWER_ITEM_ID ,
785 lb_RECIPROCAL_FLAG ,
786 lb_SUBSTITUTION_SET,
787 lb_ORGANIZATION_ID,
788 lb_EFFECTIVE_DATE,
789 lb_DISABLE_DATE ,
790 lb_RELATIONSHIP_TYPE ,
791 lb_PARTIAL_FULFILLMENT_FLAG,
792 lb_CUSTOMER_ID ,
793 lb_CUSTOMER_SITE_ID
794 LIMIT ln_rows_to_fetch;
795
796 EXIT WHEN lb_HIGHER_ITEM_ID.count = 0;
797
798 IF (c1%NOTFOUND) THEN
799 lb_FetchComplete := TRUE;
800 END IF;
801
802 FOR j IN 1..lb_HIGHER_ITEM_ID.COUNT LOOP
803
804
805
806 UPDATE MSC_ITEM_SUBSTITUTES
807 SET
808 RECIPROCAL_FLAG = lb_RECIPROCAL_FLAG(j),
809 SUBSTITUTION_SET = lb_SUBSTITUTION_SET(j),
810 CUSTOMER_ID = lb_CUSTOMER_ID(j),
811 CUSTOMER_SITE_ID = lb_CUSTOMER_SITE_ID(j),
812 EFFECTIVE_DATE = lb_EFFECTIVE_DATE(j),
813 DISABLE_DATE = lb_DISABLE_DATE(j) ,
814 PARTIAL_FULFILLMENT_FLAG = lb_PARTIAL_FULFILLMENT_FLAG(j),
815 REFRESH_ID = MSC_CL_COLLECTION.v_last_collection_id,
816 LAST_UPDATE_DATE = MSC_CL_COLLECTION.v_current_date,
817 LAST_UPDATED_BY = MSC_CL_COLLECTION.v_current_user,
818 LAST_UPDATE_LOGIN = MSC_CL_COLLECTION.v_current_user,
819 CREATION_DATE = MSC_CL_COLLECTION.v_current_date
820 WHERE
821 PLAN_ID = -1
822 AND SR_INSTANCE_ID = MSC_CL_COLLECTION.v_instance_id
823 AND HIGHER_ITEM_ID = lb_HIGHER_ITEM_ID(j)
824 AND LOWER_ITEM_ID = lb_LOWER_ITEM_ID(j)
825 AND ORGANIZATION_ID = lb_ORGANIZATION_ID(j)
826 AND RELATIONSHIP_TYPE = lb_RELATIONSHIP_TYPE(j);
827
828 IF SQL%NOTFOUND THEN
829 begin
830 EXECUTE IMMEDIATE lv_sql_stmt
831 USING lb_HIGHER_ITEM_ID(j),
832 lb_LOWER_ITEM_ID(j),
833 lb_RECIPROCAL_FLAG(j),
834 lb_SUBSTITUTION_SET(j),
835 lb_CUSTOMER_ID(j),
836 lb_CUSTOMER_SITE_ID(j),
837 lb_EFFECTIVE_DATE(j),
838 lb_DISABLE_DATE(j),
839 lb_RELATIONSHIP_TYPE(j),
840 lb_PARTIAL_FULFILLMENT_FLAG(j),
841 MSC_CL_COLLECTION.v_last_collection_id,
842 MSC_CL_COLLECTION.v_instance_id,
843 lb_ORGANIZATION_ID(j),
844 MSC_CL_COLLECTION.v_current_date,
845 MSC_CL_COLLECTION.v_current_user,
846 MSC_CL_COLLECTION.v_current_user,
847 MSC_CL_COLLECTION.v_current_date,
848 MSC_CL_COLLECTION.v_current_user;
849 EXCEPTION
850 WHEN OTHERS THEN
851 -- LOG_MESSAGE(SQLERRM);
852 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
853 IF NOT Handle_Exception(SQLCODE) THEN RAISE; END IF;
854 end;
855 END IF;
856
857 c_count:= c_count+1;
858 IF c_count > MSC_CL_COLLECTION.PBS THEN
859 COMMIT;
860 -- LOG_MESSAGE('The total record count inserted is '||TO_CHAR(c_count));
861 c_count:= 0;
862 END IF;
863
864 END loop; -- j loop
865 END loop; --c1 loop
866 END IF;
867 CLOSE c1;
868
869 lb_FetchComplete := FALSE;
870
871
872 IF (MSC_UTIL.G_COLLECT_SRP_DATA = 'Y' AND (MSC_CL_COLLECTION.v_apps_ver >= MSC_UTIL.G_APPS115 OR MSC_CL_COLLECTION.v_is_legacy_refresh)) THEN
873 Open c2;
874
875 IF (c2%ISOPEN) THEN
876 LOOP
877
878 IF (lb_FetchComplete) THEN
879 EXIT;
880 END IF;
881
882 FETCH c2 BULK COLLECT INTO
883 lb_HIGHER_ITEM_ID,
884 lb_LOWER_ITEM_ID ,
885 lb_RECIPROCAL_FLAG ,
886 lb_SUBSTITUTION_SET,
887 lb_ORGANIZATION_ID,
888 lb_RELATIONSHIP_TYPE,
889 lb_PARTIAL_FULFILLMENT_FLAG,
890 lb_EFFECTIVE_DATE,
891 lb_DISABLE_DATE
892 LIMIT ln_rows_to_fetch;
893
894
895 EXIT WHEN lb_HIGHER_ITEM_ID.count = 0;
896
897 IF (c2%NOTFOUND) THEN
898 lb_FetchComplete := TRUE;
899 END IF;
900
901 FOR j IN 1..lb_HIGHER_ITEM_ID.COUNT LOOP
902
903 UPDATE MSC_ITEM_SUBSTITUTES
904 SET
905 RECIPROCAL_FLAG = lb_RECIPROCAL_FLAG(j),
906 EFFECTIVE_DATE = lb_EFFECTIVE_DATE(j),
907 DISABLE_DATE = lb_DISABLE_DATE(j),
908 REFRESH_ID = MSC_CL_COLLECTION.v_last_collection_id,
909 LAST_UPDATE_DATE = MSC_CL_COLLECTION.v_current_date,
910 LAST_UPDATED_BY = MSC_CL_COLLECTION.v_current_user,
911 LAST_UPDATE_LOGIN = MSC_CL_COLLECTION.v_current_user,
912 CREATION_DATE = MSC_CL_COLLECTION.v_current_date
913 WHERE
914 PLAN_ID = -1
915 AND SR_INSTANCE_ID = MSC_CL_COLLECTION.v_instance_id
916 AND HIGHER_ITEM_ID = lb_HIGHER_ITEM_ID(j)
917 AND LOWER_ITEM_ID = lb_LOWER_ITEM_ID(j)
918 AND ORGANIZATION_ID = lb_ORGANIZATION_ID(j)
919 AND RELATIONSHIP_TYPE = lb_RELATIONSHIP_TYPE(j);
920
921 IF SQL%NOTFOUND THEN
922 begin
923
924 EXECUTE IMMEDIATE lv_sql_stmt1
925 USING lb_HIGHER_ITEM_ID(j),
926 lb_LOWER_ITEM_ID(j),
927 lb_RECIPROCAL_FLAG(j),
928 lb_SUBSTITUTION_SET(j),
929 lb_EFFECTIVE_DATE(j),
930 lb_DISABLE_DATE(j),
931 lb_RELATIONSHIP_TYPE(j),
932 lb_PARTIAL_FULFILLMENT_FLAG(j),
933 MSC_CL_COLLECTION.v_last_collection_id,
934 MSC_CL_COLLECTION.v_instance_id,
935 lb_ORGANIZATION_ID(j),
936 MSC_CL_COLLECTION.v_current_date,
937 MSC_CL_COLLECTION.v_current_user,
938 MSC_CL_COLLECTION.v_current_user,
939 MSC_CL_COLLECTION.v_current_date,
940 MSC_CL_COLLECTION.v_current_user;
941
942 EXCEPTION
943 WHEN OTHERS THEN
944 -- LOG_MESSAGE(SQLERRM);
945 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
946 IF NOT Handle_Exception(SQLCODE) THEN RAISE; END IF;
947 end;
948 END IF;
949
950 c_count:= c_count+1;
951 IF c_count > MSC_CL_COLLECTION.PBS THEN
952 COMMIT;
953 -- LOG_MESSAGE('The total record count inserted is '||TO_CHAR(c_count));
954 c_count:= 0;
955 END IF;
956
957
958 END LOOP; -- j loop
959 END loop; -- c2 loop
960 END IF;
961 END IF; -- SRP Collections
962 CLOSE c2;
963 END IF; -- End of Net Change
964
965
966 IF ((MSC_CL_COLLECTION.v_coll_prec.org_group_flag <> MSC_UTIL.G_ALL_ORGANIZATIONS) AND (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES)) THEN
967
968 lv_tbl:= 'ITEM_SUBSTITUTES_'||MSC_CL_COLLECTION.v_INSTANCE_CODE;
969
970 lv_sql_stmt:=
971 'INSERT INTO '||lv_tbl
972 ||' SELECT * from MSC_ITEM_SUBSTITUTES'
973 ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
974 ||' AND plan_id = -1 '
975 ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
976
977 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The sql statement is '||lv_sql_stmt);
978 EXECUTE IMMEDIATE lv_sql_stmt;
979
980 COMMIT;
981
982 END IF;
983
984 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
985 MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
986 lv_retcode,
987 'MSC_ITEM_SUBSTITUTES',
988 MSC_CL_COLLECTION.v_INSTANCE_CODE,
989 MSC_UTIL.G_WARNING
990 );
991
992
993 IF lv_retcode = MSC_UTIL.G_WARNING THEN
994 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
995 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
996 END IF;
997
998 END IF;
999
1000 COMMIT;
1001 EXCEPTION
1002 WHEN OTHERS THEN
1003 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
1004
1005 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1006 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1007 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ITEM_SUBSTITUTES');
1008 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ITEM_SUBSTITUTES');
1009 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1010
1011 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1012 RAISE;
1013
1014 ELSE
1015
1016 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1017
1018 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1019 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1020 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ITEM_SUBSTITUTES');
1021 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ITEM_SUBSTITUTES');
1022 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1023
1024 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1025 FND_MESSAGE.SET_TOKEN('COLUMN', 'HIGHER_ITEM_ID');
1026 -- FND_MESSAGE.SET_TOKEN('VALUE',TO_CHAR(c_rec.HIGHER_ITEM_ID) );
1027 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1028
1029 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1030 FND_MESSAGE.SET_TOKEN('COLUMN', 'LOWER_ITEM_ID');
1031 -- FND_MESSAGE.SET_TOKEN('VALUE',TO_CHAR(c_rec.LOWER_ITEM_ID) );
1032 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1033
1034 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1035 END IF;
1036
1037
1038 COMMIT;
1039
1040 END LOAD_ITEM_SUBSTITUTES;
1041
1042
1043 PROCEDURE LOAD_CATEGORY IS
1044
1045 CURSOR c1 IS
1046 SELECT /*+ LEADING (msic) */
1047 msic.ORGANIZATION_ID,
1048 t1.INVENTORY_ITEM_ID,
1049 mcsil.Category_Set_ID,
1050 msic.CATEGORY_NAME,
1051 msic.DESCRIPTION,
1052 msic.DISABLE_DATE,
1053 msic.SUMMARY_FLAG,
1054 msic.ENABLED_FLAG,
1055 msic.START_DATE_ACTIVE,
1056 msic.END_DATE_ACTIVE,
1057 msic.SR_INSTANCE_ID,
1058 msic.SR_CATEGORY_ID
1059 FROM MSC_CATEGORY_SET_ID_LID mcsil,
1060 MSC_ITEM_ID_LID t1,
1061 MSC_ST_ITEM_CATEGORIES msic
1062 WHERE t1.SR_INVENTORY_ITEM_ID= msic.inventory_item_id
1063 AND t1.sr_instance_id= msic.sr_instance_id
1064 AND mcsil.SR_Category_Set_ID= msic.SR_Category_Set_ID
1065 AND mcsil.SR_Instance_ID= msic.SR_Instance_ID
1066 AND msic.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1067 AND msic.DELETED_FLAG= MSC_UTIL.SYS_NO;
1068
1069 CURSOR c1_d IS
1070 SELECT
1071 msic.ORGANIZATION_ID,
1072 t1.INVENTORY_ITEM_ID,
1073 mcsil.Category_Set_ID,
1074 msic.SR_INSTANCE_ID,
1075 msic.SR_CATEGORY_ID
1076 FROM MSC_CATEGORY_SET_ID_LID mcsil,
1077 MSC_ITEM_ID_LID t1,
1078 MSC_ST_ITEM_CATEGORIES msic
1079 WHERE t1.SR_INVENTORY_ITEM_ID= msic.inventory_item_id
1080 AND t1.sr_instance_id= msic.sr_instance_id
1081 AND mcsil.SR_Category_Set_ID= msic.SR_Category_Set_ID
1082 AND mcsil.SR_Instance_ID= msic.SR_Instance_ID
1083 AND msic.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1084 AND msic.DELETED_FLAG= MSC_UTIL.SYS_YES;
1085
1086 c_count NUMBER:= 0;
1087 lv_tbl VARCHAR2(30);
1088 lv_sql_stmt VARCHAR2(5000);
1089 lv_sql_ins VARCHAR2(5000);
1090 lb_FetchComplete Boolean;
1091 ln_rows_to_fetch Number := nvl(TO_NUMBER( FND_PROFILE.VALUE('MRP_PURGE_BATCH_SIZE')),75000);
1092
1093
1094 TYPE CharTblTyp IS TABLE OF VARCHAR2(250);
1095 TYPE NumTblTyp IS TABLE OF NUMBER;
1096 TYPE dateTblTyp IS TABLE OF DATE;
1097 lb_organization_id NumTblTyp;
1098 lb_inventory_item_id NumTblTyp;
1099 lb_category_set_id NumTblTyp;
1100 lb_category_name CharTblTyp;
1101 lb_description CharTblTyp;
1102 lb_disable_date dateTblTyp;
1103 lb_summary_flag CharTblTyp;
1104 lb_enabled_flag CharTblTyp;
1105 lb_start_date_active dateTblTyp;
1106 lb_end_date_active dateTblTyp;
1107 lb_sr_instance_id NumTblTyp;
1108 lb_sr_category_id NumTblTyp;
1109
1110 lv_errbuf VARCHAR2(240);
1111 lv_retcode NUMBER;
1112
1113 BEGIN
1114
1115 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
1116 -- We want to delete all CATEGORY related data and get new stuff.
1117
1118 if (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO) then
1119 --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_CATEGORIES', MSC_CL_COLLECTION.v_instance_id, NULL);
1120
1121 IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1122 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_CATEGORIES', MSC_CL_COLLECTION.v_instance_id, NULL);
1123 ELSE
1124 v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1125 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_CATEGORIES', MSC_CL_COLLECTION.v_instance_id, NULL,v_sub_str);
1126 END IF;
1127 end if;
1128
1129 END IF;
1130
1131 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
1132 lv_tbl:= 'ITEM_CATEGORIES_'||MSC_CL_COLLECTION.v_INSTANCE_CODE;
1133 ELSE
1134 lv_tbl:= 'MSC_ITEM_CATEGORIES';
1135 END IF;
1136
1137 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
1138
1139 BEGIN
1140
1141 lv_sql_ins :=
1142 ' INSERT /*+ APPEND */ '
1143 || ' INTO '||lv_tbl
1144 ||' ( ORGANIZATION_ID, '
1145 ||' INVENTORY_ITEM_ID, '
1146 ||' CATEGORY_SET_ID, '
1147 ||' CATEGORY_NAME, '
1148 ||' DESCRIPTION, '
1149 ||' DISABLE_DATE, '
1150 ||' SUMMARY_FLAG, '
1151 ||' ENABLED_FLAG, '
1152 ||' START_DATE_ACTIVE, '
1153 ||' END_DATE_ACTIVE, '
1154 ||' SR_INSTANCE_ID, '
1155 ||' SR_CATEGORY_ID, '
1156 ||' REFRESH_NUMBER, '
1157 ||' LAST_UPDATE_DATE, '
1158 ||' LAST_UPDATED_BY, '
1159 ||' CREATION_DATE, '
1160 ||' CREATED_BY) '
1161 ||' SELECT /*+ LEADING (msic) */ '
1162 ||' msic.ORGANIZATION_ID,'
1163 ||' t1.INVENTORY_ITEM_ID,'
1164 ||' mcsil.Category_Set_ID,'
1165 ||' msic.CATEGORY_NAME,'
1166 ||' msic.DESCRIPTION,'
1167 ||' msic.DISABLE_DATE,'
1168 ||' msic.SUMMARY_FLAG,'
1169 ||' msic.ENABLED_FLAG,'
1170 ||' msic.START_DATE_ACTIVE,'
1171 ||' msic.END_DATE_ACTIVE,'
1172 ||' msic.SR_INSTANCE_ID,'
1173 ||' msic.SR_CATEGORY_ID,'
1174 ||' :v_last_collection_id, '
1175 ||' :v_current_date , '
1176 ||' :v_current_user , '
1177 ||' :v_current_date , '
1178 ||' :v_current_user '
1179 ||' FROM MSC_CATEGORY_SET_ID_LID mcsil,'
1180 ||' MSC_ITEM_ID_LID t1,'
1181 ||' MSC_ST_ITEM_CATEGORIES msic'
1182 ||' WHERE t1.SR_INVENTORY_ITEM_ID = msic.inventory_item_id '
1183 ||' AND t1.sr_instance_id = msic.sr_instance_id '
1184 ||' AND mcsil.SR_Category_Set_ID = msic.SR_Category_Set_ID '
1185 ||' AND mcsil.SR_Instance_ID = msic.SR_Instance_ID '
1186 ||' AND msic.SR_INSTANCE_ID = '||MSC_CL_COLLECTION.v_instance_id;
1187
1188 EXECUTE IMMEDIATE lv_sql_ins
1189 USING MSC_CL_COLLECTION.v_last_collection_id,MSC_CL_COLLECTION.v_current_date,MSC_CL_COLLECTION.v_current_user,MSC_CL_COLLECTION.v_current_date,MSC_CL_COLLECTION.v_current_user;
1190
1191 commit;
1192 EXCEPTION
1193 WHEN OTHERS THEN
1194
1195 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1196 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1197 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CATEGORY');
1198 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ITEM_CATEGORIES');
1199 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1200
1201 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1202 RAISE;
1203
1204 END;
1205 ELSE
1206 FOR c_rec IN c1_d LOOP
1207
1208 DELETE FROM MSC_ITEM_CATEGORIES
1209 WHERE ORGANIZATION_ID= c_rec.ORGANIZATION_ID
1210 AND INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
1211 AND CATEGORY_SET_ID= c_rec.CATEGORY_SET_ID
1212 AND SR_CATEGORY_ID= c_rec.SR_CATEGORY_ID
1213 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
1214
1215 END LOOP;
1216 lv_sql_stmt :=
1217 ' INSERT INTO '||lv_tbl
1218 ||' ( ORGANIZATION_ID, '
1219 ||' INVENTORY_ITEM_ID, '
1220 ||' CATEGORY_SET_ID, '
1221 ||' CATEGORY_NAME, '
1222 ||' DESCRIPTION, '
1223 ||' DISABLE_DATE, '
1224 ||' SUMMARY_FLAG, '
1225 ||' ENABLED_FLAG, '
1226 ||' START_DATE_ACTIVE, '
1227 ||' END_DATE_ACTIVE, '
1228 ||' SR_INSTANCE_ID, '
1229 ||' SR_CATEGORY_ID, '
1230 ||' REFRESH_NUMBER, '
1231 ||' LAST_UPDATE_DATE, '
1232 ||' LAST_UPDATED_BY, '
1233 ||' CREATION_DATE, '
1234 ||' CREATED_BY) '
1235 ||' VALUES '
1236 ||' ( :ORGANIZATION_ID, '
1237 ||' :INVENTORY_ITEM_ID, '
1238 ||' :CATEGORY_SET_ID, '
1239 ||' :CATEGORY_NAME, '
1240 ||' :DESCRIPTION, '
1241 ||' :DISABLE_DATE, '
1242 ||' :SUMMARY_FLAG, '
1243 ||' :ENABLED_FLAG, '
1244 ||' :START_DATE_ACTIVE, '
1245 ||' :END_DATE_ACTIVE, '
1246 ||' :SR_INSTANCE_ID, '
1247 ||' :SR_CATEGORY_ID, '
1248 ||' :v_last_collection_id, '
1249 ||' :v_current_date, '
1250 ||' :v_current_user, '
1251 ||' :v_current_date, '
1252 ||' :v_current_user ) ';
1253
1254
1255 c_count:= 0;
1256
1257 OPEN c1;
1258 IF (c1%ISOPEN) THEN
1259 LOOP
1260
1261 --
1262 -- Retrieve the next set of rows if we are currently not in the
1263 -- middle of processing a fetched set or rows.
1264 --
1265 IF (lb_FetchComplete) THEN
1266 EXIT;
1267 END IF;
1268
1269 -- Fetch the next set of rows
1270 FETCH c1 BULK COLLECT INTO lb_organization_id,
1271 lb_inventory_item_id,
1272 lb_category_set_id,
1273 lb_category_name,
1274 lb_description,
1275 lb_disable_date,
1276 lb_summary_flag,
1277 lb_enabled_flag,
1278 lb_start_date_active,
1279 lb_end_date_active,
1280 lb_sr_instance_id,
1281 lb_sr_category_id
1282 LIMIT ln_rows_to_fetch;
1283
1284 -- Since we are only fetching records if either (1) this is the first
1285 -- fetch or (2) the previous fetch did not retrieve all of the
1286 -- records, then at least one row should always be fetched. But
1287 -- checking just to make sure.
1288 EXIT WHEN lb_inventory_item_id.count = 0;
1289
1290 -- Check if all of the rows have been fetched. If so, indicate that
1291 -- the fetch is complete so that another fetch is not made.
1292 -- Additional check is introduced for the following reasons
1293 -- In 9i, the table of records gets modified but in 8.1.6 the table of records is
1294 -- unchanged after the fetch(bug#2995144)
1295 IF (c1%NOTFOUND) THEN
1296 lb_FetchComplete := TRUE;
1297 END IF;
1298
1299 FOR j IN 1..lb_inventory_item_id.COUNT LOOP
1300
1301 BEGIN
1302
1303 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
1304
1305 UPDATE MSC_ITEM_CATEGORIES
1306 SET
1307 CATEGORY_NAME = lb_category_name(j),
1308 DESCRIPTION = lb_description(j),
1309 DISABLE_DATE = lb_disable_date(j),
1310 SUMMARY_FLAG = lb_summary_flag(j),
1311 ENABLED_FLAG = lb_enabled_flag(j),
1312 START_DATE_ACTIVE = lb_start_date_active(j),
1313 END_DATE_ACTIVE = lb_end_date_active(J),
1314 /* SR_CATEGORY_ID= lb_sr_category_id(j), --If the item is assigned to more than 1 category in the same category-set then,
1315 only one row of that category set will be repeatedly updated and the other
1316 item categories will not be inserted. Moving this into the WHERE clause.
1317 */
1318 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1319 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1320 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1321 WHERE ORGANIZATION_ID = lb_organization_id(j)
1322 AND INVENTORY_ITEM_ID = lb_inventory_item_id(J)
1323 AND CATEGORY_SET_ID = lb_category_set_id(j)
1324 AND SR_INSTANCE_ID = lb_sr_instance_id(j)
1325 AND SR_CATEGORY_ID = lb_sr_category_id(j);
1326
1327 END IF;
1328
1329 IF SQL%NOTFOUND THEN
1330 EXECUTE IMMEDIATE lv_sql_stmt
1331 USING
1332 lb_organization_id(j),
1333 lb_inventory_item_id(J),
1334 lb_category_set_id(j),
1335 lb_category_name(j),
1336 lb_description(j),
1337 lb_disable_date(j),
1338 lb_summary_flag(j),
1339 lb_enabled_flag(j),
1340 lb_start_date_active(j),
1341 lb_end_date_active(J),
1342 lb_sr_instance_id(j),
1343 lb_sr_category_id(j),
1344 MSC_CL_COLLECTION.v_last_collection_id,
1345 MSC_CL_COLLECTION.v_current_date,
1346 MSC_CL_COLLECTION.v_current_user,
1347 MSC_CL_COLLECTION.v_current_date,
1348 MSC_CL_COLLECTION.v_current_user ;
1349
1350 END IF;
1351
1352 c_count:= c_count+1;
1353
1354 IF c_count>MSC_CL_COLLECTION.PBS THEN
1355
1356 COMMIT;
1357
1358 c_count:= 0;
1359
1360 END IF;
1361
1362
1363 EXCEPTION
1364 WHEN OTHERS THEN
1365
1366 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
1367
1368 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1369 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1370 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CATEGORY');
1371 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ITEM_CATEGORIES');
1372 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1373
1374 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1375 RAISE;
1376
1377 ELSE
1378
1379 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1380
1381 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1382 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1383 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CATEGORY');
1384 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ITEM_CATEGORIES');
1385 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1386
1387 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1388 FND_MESSAGE.SET_TOKEN('COLUMN', 'ITEM_NAME');
1389 FND_MESSAGE.SET_TOKEN('VALUE', ITEM_NAME( lb_inventory_item_id(J)));
1390 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1391
1392 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1393 FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
1394 FND_MESSAGE.SET_TOKEN('VALUE',
1395 MSC_GET_NAME.ORG_CODE( lb_organization_id(j),
1396 MSC_CL_COLLECTION.v_instance_id));
1397 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1398
1399 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1400 FND_MESSAGE.SET_TOKEN('COLUMN', 'CATEGORY_NAME');
1401 FND_MESSAGE.SET_TOKEN('VALUE', lb_category_name(j));
1402 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1403
1404 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1405 END IF;
1406
1407 END;
1408
1409 END LOOP;
1410 END LOOP;
1411 END IF;
1412 CLOSE c1;
1413 END IF;
1414 COMMIT;
1415
1416 BEGIN
1417
1418 IF ((MSC_CL_COLLECTION.v_coll_prec.org_group_flag <> MSC_UTIL.G_ALL_ORGANIZATIONS) AND (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES)) THEN
1419
1420 lv_tbl:= 'ITEM_CATEGORIES_'||MSC_CL_COLLECTION.v_INSTANCE_CODE;
1421
1422 lv_sql_stmt:=
1423 'INSERT INTO '||lv_tbl
1424 ||' SELECT * from MSC_ITEM_CATEGORIES'
1425 ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
1426 ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
1427
1428 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The sql statement is '||lv_sql_stmt);
1429 EXECUTE IMMEDIATE lv_sql_stmt;
1430
1431 COMMIT;
1432
1433 END IF;
1434
1435 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
1436 MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
1437 lv_retcode,
1438 'MSC_ITEM_CATEGORIES',
1439 MSC_CL_COLLECTION.v_INSTANCE_CODE,
1440 MSC_UTIL.G_ERROR
1441 );
1442
1443 IF lv_retcode = MSC_UTIL.G_ERROR THEN
1444 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
1445 RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
1446 ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
1447 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1448 END IF;
1449
1450 END IF;
1451
1452 EXCEPTION
1453 WHEN OTHERS THEN
1454
1455 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1456 RAISE;
1457 END;
1458
1459 END LOAD_CATEGORY;
1460
1461 -- ============== KEY TRANSFORMATION FOR ITEMS, CATEGORY SETS =================
1462
1463 PROCEDURE GENERATE_ITEM_KEYS (
1464 ERRBUF OUT NOCOPY VARCHAR2,
1465 RETCODE OUT NOCOPY NUMBER,
1466 pINSTANCE_ID IN NUMBER) IS
1467
1468 Cursor c3 IS
1469 SELECT mcsil.Category_Set_ID,
1470 mscs.Category_set_Name,
1471 mscs.DESCRIPTION,
1472 mscs.CONTROL_LEVEL,
1473 mscs.DEFAULT_FLAG,
1474 mscs.SR_INSTANCE_ID
1475 FROM MSC_CATEGORY_SET_ID_LID mcsil,
1476 MSC_ST_CATEGORY_SETS mscs
1477 WHERE mcsil.SR_Category_Set_ID= mscs.SR_Category_Set_ID
1478 AND mcsil.SR_Instance_ID= mscs.SR_Instance_ID
1479 AND mscs.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1480 ORDER BY
1481 mcsil.Category_Set_ID;
1482
1483 Cursor c4 IS
1484 SELECT distinct msi.Item_Name
1485 FROM MSC_ST_SYSTEM_ITEMS msi
1486 WHERE NOT EXISTS ( select 1
1487 from MSC_ITEMS mi
1488 where mi.Item_Name= msi.Item_Name)
1489 AND msi.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1490 ORDER BY msi.Item_Name; -- using ORDER BY to avoid dead lock
1491
1492 Cursor c8 IS
1493 SELECT DISTINCT
1494 mscs.Category_Set_Name
1495 FROM MSC_ST_CATEGORY_SETS mscs
1496 WHERE NOT EXISTS ( select 1
1497 from MSC_Category_Sets mcs
1498 where mscs.Category_Set_Name= mcs.Category_Set_Name)
1499 AND mscs.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1500 ORDER BY mscs.Category_Set_Name; -- using ORDER BY to avoid dead lock
1501
1502 CURSOR c11 IS
1503 SELECT distinct
1504 msi.SR_INVENTORY_ITEM_ID, msi.SR_INSTANCE_ID, mi.INVENTORY_ITEM_ID
1505 FROM MSC_ST_SYSTEM_ITEMS msi,
1506 MSC_ITEMS mi
1507 WHERE NOT EXISTS( select 1
1508 from MSC_ITEM_ID_LID miil
1509 where msi.SR_INVENTORY_ITEM_ID=
1510 miil.SR_INVENTORY_ITEM_ID
1511 and msi.SR_INSTANCE_ID= miil.SR_INSTANCE_ID)
1512 AND msi.ITEM_NAME= mi.ITEM_NAME
1513 AND msi.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1514
1515 lv_default_category_set_id NUMBER;
1516 lv_control_flag NUMBER;
1517
1518 lv_item_id_count NUMBER := 0;
1519 lv_cat_id_count NUMBER := 0;
1520 lv_items_stat_stale NUMBER := MSC_UTIL.SYS_NO;
1521 lv_cat_stat_stale NUMBER := MSC_UTIL.SYS_NO;
1522 lv_ins_records NUMBER := 0;
1523
1524 BEGIN
1525
1526 -- MSC_CL_SETUP_COLLECTION.GET_COLL_PARAM (pINSTANCE_ID); -- 2 be changed
1527
1528 MSC_CL_COLLECTION.INITIALIZE( pINSTANCE_ID);
1529
1530 SELECT decode(nvl(fnd_profile.value('MSC_PURGE_ST_CONTROL'),'N'),'Y',1,2)
1531 INTO lv_control_flag
1532 FROM dual;
1533
1534 begin
1535 select num_rows,decode (stale_stats,'NO', MSC_UTIL.SYS_NO, MSC_UTIL.SYS_YES )
1536 into lv_item_id_count, lv_items_stat_stale
1537 from dba_TAB_STATISTICS
1538 where table_name = 'MSC_ITEM_ID_LID';
1539 exception when no_data_found then
1540 lv_items_stat_stale := MSC_UTIL.SYS_YES ;
1541 end;
1542
1543 begin
1544 select num_rows,decode (stale_stats,'NO', MSC_UTIL.SYS_NO, MSC_UTIL.SYS_YES )
1545 into lv_cat_id_count, lv_cat_stat_stale
1546 from dba_TAB_STATISTICS
1547 where table_name ='MSC_CATEGORY_SET_ID_LID';
1548 exception when no_data_found then
1549 lv_cat_stat_stale := MSC_UTIL.SYS_YES ;
1550 end;
1551
1552
1553 /* if complete refresh, regen the key mapping data */
1554 IF MSC_CL_COLLECTION.v_is_complete_refresh THEN
1555
1556 IF lv_control_flag = 2 THEN
1557
1558 IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1559 DELETE MSC_ITEM_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1560 DELETE MSC_CATEGORY_SET_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1561 DELETE MSC_ITEM_ID_LID WHERE SR_INSTANCE_ID= -1;
1562 DELETE MSC_CATEGORY_SET_ID_LID WHERE SR_INSTANCE_ID= -1;
1563 lv_items_stat_stale := MSC_UTIL.SYS_YES;
1564 lv_cat_stat_stale := MSC_UTIL.SYS_YES;
1565 END IF;
1566
1567 ELSE
1568
1569 IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1570 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ITEM_ID_LID');
1571 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_CATEGORY_SET_ID_LID');
1572 lv_items_stat_stale := MSC_UTIL.SYS_YES;
1573 lv_cat_stat_stale := MSC_UTIL.SYS_YES;
1574 END IF;
1575
1576 END IF;
1577
1578 END IF;
1579
1580 /*************** PREPLACE CHANGE START *****************/
1581
1582 IF MSC_CL_COLLECTION.v_is_partial_refresh THEN
1583
1584 IF (MSC_CL_COLLECTION.v_coll_prec.item_flag = MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.org_group_flag =MSC_UTIL.G_ALL_ORGANIZATIONS ) THEN
1585
1586 IF lv_control_flag = 2 THEN
1587 DELETE MSC_ITEM_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1588 DELETE MSC_CATEGORY_SET_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1589 DELETE MSC_ITEM_ID_LID WHERE SR_INSTANCE_ID= -1;
1590 DELETE MSC_CATEGORY_SET_ID_LID WHERE SR_INSTANCE_ID= -1;
1591 lv_items_stat_stale := MSC_UTIL.SYS_YES;
1592 lv_cat_stat_stale := MSC_UTIL.SYS_YES;
1593 ELSE
1594 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ITEM_ID_LID');
1595 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_CATEGORY_SET_ID_LID');
1596 lv_items_stat_stale := MSC_UTIL.SYS_YES;
1597 lv_cat_stat_stale := MSC_UTIL.SYS_YES;
1598 END IF;
1599
1600 END IF;
1601
1602 END IF;
1603
1604 /*************** PREPLACE CHANGE END *****************/
1605
1606 --agmcont
1607 IF MSC_CL_COLLECTION.v_is_cont_refresh THEN
1608
1609 IF (MSC_CL_COLLECTION.v_coll_prec.item_flag = MSC_UTIL.SYS_YES) and
1610 (MSC_CL_COLLECTION.v_coll_prec.item_sn_flag = MSC_UTIL.SYS_TGT) and (MSC_CL_COLLECTION.v_coll_prec.org_group_flag=MSC_UTIL.G_ALL_ORGANIZATIONS) THEN
1611
1612 IF lv_control_flag = 2 THEN
1613 DELETE MSC_ITEM_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1614 DELETE MSC_CATEGORY_SET_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1615 DELETE MSC_ITEM_ID_LID WHERE SR_INSTANCE_ID= -1;
1616 DELETE MSC_CATEGORY_SET_ID_LID WHERE SR_INSTANCE_ID= -1;
1617 lv_items_stat_stale := MSC_UTIL.SYS_YES;
1618 lv_cat_stat_stale := MSC_UTIL.SYS_YES;
1619 ELSE
1620 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ITEM_ID_LID');
1621 MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_CATEGORY_SET_ID_LID');
1622 lv_items_stat_stale := MSC_UTIL.SYS_YES;
1623 lv_cat_stat_stale := MSC_UTIL.SYS_YES;
1624 END IF;
1625
1626 END IF;
1627
1628 END IF;
1629
1630 COMMIT;
1631
1632 --========== ITEM ==========
1633 FOR c_rec IN c4 LOOP
1634
1635 BEGIN
1636
1637 INSERT INTO MSC_ITEMS
1638 ( ITEM_NAME,
1639 INVENTORY_ITEM_ID,
1640 LAST_UPDATE_DATE,
1641 LAST_UPDATED_BY,
1642 CREATION_DATE,
1643 CREATED_BY)
1644 VALUES
1645 ( c_rec.Item_Name,
1646 MSC_Items_S.NEXTVAL,
1647 MSC_CL_COLLECTION.v_current_date,
1648 MSC_CL_COLLECTION.v_current_user,
1649 MSC_CL_COLLECTION.v_current_date,
1650 MSC_CL_COLLECTION.v_current_user);
1651
1652 EXCEPTION
1653
1654 WHEN DUP_VAL_ON_INDEX THEN
1655
1656 NULL;
1657
1658 WHEN OTHERS THEN
1659
1660 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1661 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1662 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_ITEM_KEYS');
1663 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ITEMS');
1664 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1665
1666 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1667 FND_MESSAGE.SET_TOKEN('COLUMN', 'ITEM_NAME');
1668 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.ITEM_NAME);
1669 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1670
1671 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1672
1673 ERRBUF := sqlerrm;
1674 RETCODE := MSC_UTIL.G_ERROR;
1675 RAISE;
1676
1677 END;
1678
1679 END LOOP;
1680
1681 COMMIT;
1682
1683 lv_ins_records := 0;
1684
1685 FOR c_rec IN c11 LOOP
1686
1687 BEGIN
1688
1689 INSERT INTO MSC_ITEM_ID_LID
1690 ( SR_INVENTORY_ITEM_ID,
1691 SR_INSTANCE_ID,
1692 INVENTORY_ITEM_ID)
1693 VALUES
1694 ( c_rec.SR_INVENTORY_ITEM_ID,
1695 c_rec.SR_INSTANCE_ID,
1696 c_rec.INVENTORY_ITEM_ID);
1697
1698 lv_ins_records := lv_ins_records + 1;
1699
1700 EXCEPTION
1701
1702 WHEN OTHERS THEN
1703
1704 IF SQLCODE IN (-1653,-1654) THEN
1705
1706 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1707 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1708 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_ITEM_KEYS');
1709 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ITEM_ID_LID');
1710 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1711
1712 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1713 FND_MESSAGE.SET_TOKEN('COLUMN', 'ITEM_NAME');
1714 FND_MESSAGE.SET_TOKEN('VALUE', ITEM_NAME( c_rec.INVENTORY_ITEM_ID));
1715 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1716
1717 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1718 FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_INVENTORY_ITEM_ID');
1719 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SR_INVENTORY_ITEM_ID));
1720 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1721
1722 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1723
1724 ERRBUF := sqlerrm;
1725 RETCODE := MSC_UTIL.G_ERROR;
1726 RAISE;
1727
1728 ELSE
1729
1730 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1731
1732 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1733 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1734 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_ITEM_KEYS');
1735 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ITEM_ID_LID');
1736 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1737
1738 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1739 FND_MESSAGE.SET_TOKEN('COLUMN', 'ITEM_NAME');
1740 FND_MESSAGE.SET_TOKEN('VALUE', ITEM_NAME( c_rec.INVENTORY_ITEM_ID));
1741 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1742
1743 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1744 FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_INVENTORY_ITEM_ID');
1745 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SR_INVENTORY_ITEM_ID));
1746 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1747
1748 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1749
1750 ERRBUF := sqlerrm;
1751 RETCODE := MSC_UTIL.G_WARNING;
1752
1753 END IF;
1754
1755 END;
1756
1757 END LOOP;
1758
1759 COMMIT;
1760
1761 /* Bug 7653761 - If inserted records are more than 20% */
1762 IF lv_items_stat_stale = MSC_UTIL.SYS_NO AND lv_ins_records > lv_item_id_count * 0.2 THEN
1763 lv_items_stat_stale := MSC_UTIL.SYS_YES;
1764 END IF;
1765
1766 --========== CATEGORY SET ==========
1767
1768 FOR c_rec IN c8 LOOP
1769
1770 BEGIN
1771
1772 INSERT INTO MSC_CATEGORY_SETS
1773 ( CATEGORY_SET_ID,
1774 CATEGORY_SET_NAME,
1775 CONTROL_LEVEL,
1776 SR_CATEGORY_SET_ID, -- using ORDER BY to avoid dead lock
1777 SR_INSTANCE_ID,
1778 REFRESH_NUMBER,
1779 LAST_UPDATE_DATE,
1780 LAST_UPDATED_BY,
1781 CREATION_DATE,
1782 CREATED_BY)
1783 VALUES
1784 ( MSC_Category_Sets_S.NEXTVAL,
1785 c_rec.Category_Set_Name,
1786 -1,
1787 MSC_Category_Sets_S.NEXTVAL, -- dummy value to satisfy the unique constraint
1788 -1,
1789 MSC_CL_COLLECTION.v_last_collection_id,
1790 MSC_CL_COLLECTION.v_current_date,
1791 MSC_CL_COLLECTION.v_current_user,
1792 MSC_CL_COLLECTION.v_current_date,
1793 MSC_CL_COLLECTION.v_current_user);
1794
1795 EXCEPTION
1796
1797 WHEN DUP_VAL_ON_INDEX THEN
1798
1799 NULL;
1800
1801 WHEN OTHERS THEN
1802
1803 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1804 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1805 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_ITEM_KEYS');
1806 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CATEGORY_SETS');
1807 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1808
1809 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1810 FND_MESSAGE.SET_TOKEN('COLUMN', 'CATEGORY_SET_NAME');
1811 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CATEGORY_SET_NAME);
1812 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1813
1814 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1815
1816 ERRBUF := sqlerrm;
1817 RETCODE := MSC_UTIL.G_ERROR;
1818 RAISE;
1819
1820 END;
1821
1822 END LOOP;
1823
1824 COMMIT;
1825
1826 INSERT INTO MSC_CATEGORY_SET_ID_LID
1827 ( SR_Category_Set_ID,
1828 SR_INSTANCE_ID,
1829 Category_Set_ID)
1830 SELECT distinct
1831 mscs.SR_Category_Set_ID,
1832 mscs.SR_INSTANCE_ID,
1833 mcs.Category_Set_ID
1834 FROM MSC_ST_CATEGORY_SETS mscs,
1835 MSC_CATEGORY_SETS mcs
1836 WHERE NOT EXISTS( select 1
1837 from MSC_CATEGORY_SET_ID_LID mcsil
1838 where mscs.SR_Category_Set_ID= mcsil.SR_Category_Set_ID
1839 and mscs.SR_INSTANCE_ID= mcsil.SR_INSTANCE_ID)
1840 AND mscs.Category_Set_NAME= mcs.Category_Set_NAME
1841 AND mscs.SR_INSTANCE_ID = MSC_CL_COLLECTION.v_instance_id;
1842
1843 lv_ins_records := SQL%ROWCOUNT;
1844
1845 COMMIT;
1846
1847 /* Bug 7653761 - If inserted records are more than 20%*/
1848 IF lv_cat_stat_stale = MSC_UTIL.SYS_NO AND lv_ins_records > lv_cat_id_count * 0.2 THEN
1849 lv_cat_stat_stale := MSC_UTIL.SYS_YES;
1850 END IF;
1851
1852 /* in order to set default flag, access the lock on the table first*/
1853 LOCK TABLE MSC_CATEGORY_SETS IN EXCLUSIVE MODE;
1854
1855 lv_default_category_set_id:= NULL;
1856
1857 FOR c_rec IN c3 LOOP
1858
1859 BEGIN
1860
1861 UPDATE MSC_Category_Sets mcs
1862 SET mcs.DESCRIPTION= c_rec.Description,
1863 mcs.CONTROL_LEVEL= c_rec.Control_Level,
1864 mcs.DEFAULT_FLAG= c_rec.DEFAULT_FLAG,
1865 mcs.SR_INSTANCE_ID= c_rec.SR_Instance_ID,
1866 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1867 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1868 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
1869 CREATION_DATE= MSC_CL_COLLECTION.v_current_date,
1870 CREATED_BY= MSC_CL_COLLECTION.v_current_user
1871 WHERE mcs.Category_Set_ID= c_rec.Category_Set_ID;
1872
1873 IF c_rec.DEFAULT_FLAG= 1 THEN
1874 lv_default_category_set_id:= c_rec.Category_Set_ID;
1875 END IF;
1876
1877 EXCEPTION
1878
1879 WHEN OTHERS THEN
1880
1881 IF SQLCODE IN (-1653,-1654) THEN
1882
1883 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1884 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1885 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_ITEM_KEYS');
1886 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CATEGORY_SETS');
1887 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1888
1889 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1890 FND_MESSAGE.SET_TOKEN('COLUMN', 'CATEGORY_SET_NAME');
1891 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CATEGORY_SET_NAME);
1892 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1893
1894 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1895
1896 ERRBUF := sqlerrm;
1897 RETCODE := MSC_UTIL.G_ERROR;
1898 RAISE;
1899
1900 ELSE
1901
1902 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1903
1904 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1905 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1906 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_ITEM_KEYS');
1907 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CATEGORY_SETS');
1908 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1909
1910 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1911 FND_MESSAGE.SET_TOKEN('COLUMN', 'CATEGORY_SET_NAME');
1912 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CATEGORY_SET_NAME);
1913 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1914
1915 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1916
1917 ERRBUF := sqlerrm;
1918 RETCODE := MSC_UTIL.G_WARNING;
1919
1920 END IF;
1921
1922 END;
1923
1924 END LOOP;
1925
1926 IF lv_default_category_set_id IS NOT NULL THEN
1927
1928 UPDATE MSC_CATEGORY_SETS
1929 SET DEFAULT_FLAG= 2
1930 WHERE DEFAULT_FLAG= 1
1931 AND CATEGORY_SET_ID <> lv_default_category_set_id;
1932
1933 END IF;
1934
1935 COMMIT;
1936
1937 /* analyse the key mapping tables */
1938 IF MSC_CL_COLLECTION.v_coll_prec.item_flag = MSC_UTIL.SYS_YES THEN
1939 IF lv_items_stat_stale = MSC_UTIL.SYS_YES THEN
1940 msc_analyse_tables_pk.analyse_table( 'MSC_ITEM_ID_LID');
1941 END IF;
1942 IF lv_cat_stat_stale = MSC_UTIL.SYS_YES THEN
1943 msc_analyse_tables_pk.analyse_table( 'MSC_CATEGORY_SET_ID_LID');
1944 END IF;
1945 END IF;
1946
1947 EXCEPTION
1948
1949 WHEN OTHERS THEN
1950
1951 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1952
1953 ERRBUF := sqlerrm;
1954 RETCODE := MSC_UTIL.G_ERROR;
1955 RAISE;
1956
1957 END GENERATE_ITEM_KEYS;
1958
1959 --==================================================================
1960
1961 PROCEDURE LOAD_SUPPLIER_CAPACITY IS
1962
1963 CURSOR c1 IS
1964 SELECT
1965 msis.ORGANIZATION_ID,
1966 msis.USING_ORGANIZATION_ID,
1967 mtil.TP_ID SUPPLIER_ID,
1968 mtsil.TP_SITE_ID SUPPLIER_SITE_ID,
1969 t1.INVENTORY_ITEM_ID,
1970 msis.PROCESSING_LEAD_TIME,
1971 msis.MINIMUM_ORDER_QUANTITY,
1972 msis.FIXED_LOT_MULTIPLE,
1973 msis.DELIVERY_CALENDAR_CODE,
1974 msis.SUPPLIER_CAP_OVER_UTIL_COST,
1975 msis.PURCHASING_UNIT_OF_MEASURE,
1976 msis.SR_INSTANCE_ID2,
1977 msis.ITEM_PRICE, --Item Price by Supplier Fix
1978 /* SCE Change starts */
1979 -- Pull Supplier Item Name for cross reference functionality.
1980 msis.supplier_item_name,
1981 msis.planner_code,
1982 msis.vmi_flag,
1983 msis.min_minmax_quantity,
1984 msis.max_minmax_quantity,
1985 msis.maximum_order_quantity,
1986 --msis.VMI_UNIT_OF_MEASURE,
1987 msis.VMI_REPLENISHMENT_APPROVAL,
1988 msis.ENABLE_VMI_AUTO_REPLENISH_FLAG,
1989 muom.uom_code,
1990 --muom1.uom_code VMI_UOM_CODE,
1991 msis.REPLENISHMENT_METHOD,
1992 msis.MIN_MINMAX_DAYS,
1993 msis.MAX_MINMAX_DAYS,
1994 msis.FORECAST_HORIZON,
1995 msis.FIXED_ORDER_QUANTITY,
1996 /* SCE Change ends */
1997 msis.SR_INSTANCE_ID
1998 FROM MSC_TP_ID_LID mtil,
1999 MSC_TP_SITE_ID_LID mtsil,
2000 MSC_ITEM_ID_LID t1,
2001 MSC_ST_ITEM_SUPPLIERS msis,
2002 MSC_UNITS_OF_MEASURE muom
2003 -- MSC_UNITS_OF_MEASURE muom1
2004 WHERE t1.SR_INVENTORY_ITEM_ID= msis.inventory_item_id
2005 AND t1.sr_instance_id= msis.sr_instance_id
2006 AND mtil.SR_TP_ID = msis.SUPPLIER_ID
2007 AND mtil.SR_INSTANCE_ID = msis.SR_INSTANCE_ID
2008 AND mtil.PARTNER_TYPE = 1
2009 AND mtsil.SR_TP_SITE_ID(+)= msis.Supplier_Site_ID
2010 AND mtsil.SR_INSTANCE_ID(+)= msis.SR_Instance_ID
2011 AND mtsil.Partner_Type(+)= 1
2012 /* SCE Change starts */
2013 -- Pull only valid records
2014 AND nvl(msis.process_flag, -99) <> MSC_UTIL.G_ERROR
2015 -- Make a join with company_id
2016 -- If company_id is null then it means the record is owned by the Application
2017 -- owner company.
2018 AND nvl(msis.company_id, -1) = nvl(mtil.sr_company_id, -1)
2019 AND nvl(msis.company_id, -1) = nvl(mtsil.sr_company_id, -1)
2020 -- Join to get uom_code
2021 AND nvl(msis.purchasing_unit_of_measure, '-99') = muom.unit_of_measure (+)
2022 -- Join to get vmi_uom_code
2023 -- AND nvl(msis.vmi_unit_of_measure, '-99') = muom1.unit_of_measure (+)
2024 /* SCE change ends */
2025 AND msis.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
2026
2027 CURSOR c2 IS
2028 SELECT
2029 mssc.ORGANIZATION_ID,
2030 mssc.USING_ORGANIZATION_ID,
2031 mtil.TP_ID SUPPLIER_ID,
2032 mtsil.TP_SITE_ID SUPPLIER_SITE_ID,
2033 t1.INVENTORY_ITEM_ID,
2034 mssc.FROM_DATE,
2035 mssc.TO_DATE,
2036 mssc.CAPACITY,
2037 mssc.SR_INSTANCE_ID
2038 FROM MSC_TP_ID_LID mtil,
2039 MSC_TP_SITE_ID_LID mtsil,
2040 MSC_ITEM_ID_LID t1,
2041 MSC_ST_SUPPLIER_CAPACITIES mssc
2042 WHERE t1.SR_INVENTORY_ITEM_ID= mssc.inventory_item_id
2043 AND t1.sr_instance_id= mssc.sr_instance_id
2044 AND mtil.SR_TP_ID = mssc.SUPPLIER_ID
2045 AND mtil.SR_INSTANCE_ID = mssc.SR_INSTANCE_ID
2046 AND mtil.PARTNER_TYPE = 1
2047 AND mtsil.SR_TP_SITE_ID(+)= mssc.Supplier_Site_ID
2048 AND mtsil.SR_INSTANCE_ID(+)= mssc.SR_Instance_ID
2049 AND mtsil.Partner_Type(+)= 1
2050 AND mssc.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
2051 AND mssc.DELETED_FLAG= MSC_UTIL.SYS_NO
2052 AND NVL(decode( MSC_CL_COLLECTION.v_coll_prec.app_supp_cap_flag, MSC_UTIL.ASL_YES, 1,
2053 MSC_UTIL.ASL_YES_RETAIN_CP, (select distinct 0
2054 from msc_supplier_capacities msc1
2055 where msc1.SR_INSTANCE_ID = mssc.SR_INSTANCE_ID
2056 and msc1.ORGANIZATION_ID = mssc.ORGANIZATION_ID
2057 and msc1.INVENTORY_ITEM_ID = t1.INVENTORY_ITEM_ID
2058 and msc1.SUPPLIER_ID = mtil.TP_ID
2059 and msc1.SUPPLIER_SITE_ID = mtsil.TP_SITE_ID
2060 and msc1.collected_flag=3 )
2061 ,0 ) , 1 ) = 1;
2062
2063 CURSOR c2d IS
2064 SELECT
2065 mssc.ORGANIZATION_ID,
2066 mssc.USING_ORGANIZATION_ID,
2067 mtil.TP_ID SUPPLIER_ID,
2068 mtsil.TP_SITE_ID SUPPLIER_SITE_ID,
2069 t1.INVENTORY_ITEM_ID,
2070 mssc.FROM_DATE,
2071 mssc.SR_INSTANCE_ID
2072 FROM MSC_TP_ID_LID mtil,
2073 MSC_TP_SITE_ID_LID mtsil,
2074 MSC_ITEM_ID_LID t1,
2075 MSC_ST_SUPPLIER_CAPACITIES mssc
2076 WHERE t1.SR_INVENTORY_ITEM_ID= mssc.inventory_item_id
2077 AND t1.sr_instance_id= mssc.sr_instance_id
2078 AND mtil.SR_TP_ID = mssc.SUPPLIER_ID
2079 AND mtil.SR_INSTANCE_ID = mssc.SR_INSTANCE_ID
2080 AND mtil.PARTNER_TYPE = 1
2081 AND mtsil.SR_TP_SITE_ID(+)= mssc.Supplier_Site_ID
2082 AND mtsil.SR_INSTANCE_ID(+)= mssc.SR_Instance_ID
2083 AND mtsil.Partner_Type(+)= 1
2084 AND mssc.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
2085 AND mssc.DELETED_FLAG= MSC_UTIL.SYS_YES;
2086
2087 CURSOR c5d IS
2088 SELECT mssc.ORGANIZATION_ID,
2089 mssc.USING_ORGANIZATION_ID,
2090 mtil.TP_ID SUPPLIER_ID,
2091 mtsil.TP_SITE_ID SUPPLIER_SITE_ID,
2092 t1.INVENTORY_ITEM_ID,
2093 mssc.FROM_DATE,
2094 mssc.SR_INSTANCE_ID
2095 FROM MSC_TP_ID_LID mtil,
2096 MSC_TP_SITE_ID_LID mtsil,
2097 MSC_ITEM_ID_LID t1,
2098 MSC_ST_SUPPLIER_CAPACITIES mssc,
2099 MSC_SUPPLIER_CAPACITIES msc1
2100 WHERE t1.SR_INVENTORY_ITEM_ID= mssc.inventory_item_id
2101 AND t1.sr_instance_id= mssc.sr_instance_id
2102 AND mtil.SR_TP_ID = mssc.SUPPLIER_ID
2103 AND mtil.SR_INSTANCE_ID = mssc.SR_INSTANCE_ID
2104 AND mtil.PARTNER_TYPE = 1
2105 AND mtsil.SR_TP_SITE_ID(+)= mssc.Supplier_Site_ID
2106 AND mtsil.SR_INSTANCE_ID(+)= mssc.SR_Instance_ID
2107 AND mtsil.Partner_Type(+)= 1
2108 AND msc1.supplier_id = mtil.tp_id
2109 AND msc1.supplier_site_id(+) = mtsil.tp_site_id
2110 AND msc1.inventory_item_id = t1.inventory_item_id
2111 AND msc1.organization_id = mssc.organization_id
2112 AND msc1.sr_instance_id = mssc.sr_instance_id
2113 AND mssc.SR_INSTANCE_ID = MSC_CL_COLLECTION.v_instance_id
2114 AND msc1.collected_flag = 3
2115 AND decode(MSC_CL_COLLECTION.v_coll_prec.app_supp_cap_flag , MSC_UTIL.ASL_YES,1,0)= 1;
2116
2117 CURSOR c3 IS
2118 SELECT
2119 mtil.TP_ID SUPPLIER_ID,
2120 mtsil.TP_SITE_ID SUPPLIER_SITE_ID,
2121 mssfe.ORGANIZATION_ID,
2122 mssfe.USING_ORGANIZATION_ID,
2123 t1.INVENTORY_ITEM_ID, -- mssfe.INVENTORY_ITEM_ID,
2124 mssfe.FENCE_DAYS,
2125 mssfe.TOLERANCE_PERCENTAGE,
2126 mssfe.SR_INSTANCE_ID
2127 FROM MSC_TP_ID_LID mtil,
2128 MSC_TP_SITE_ID_LID mtsil,
2129 MSC_ITEM_ID_LID t1,
2130 MSC_ST_SUPPLIER_FLEX_FENCES mssfe
2131 WHERE t1.SR_INVENTORY_ITEM_ID= mssfe.inventory_item_id
2132 AND t1.sr_instance_id= mssfe.sr_instance_id
2133 AND mtil.SR_TP_ID = mssfe.SUPPLIER_ID
2134 AND mtil.SR_INSTANCE_ID = mssfe.SR_INSTANCE_ID
2135 AND mtil.PARTNER_TYPE = 1
2136 AND mtsil.SR_TP_SITE_ID(+)= mssfe.Supplier_Site_ID
2137 AND mtsil.SR_INSTANCE_ID(+)= mssfe.SR_Instance_ID
2138 AND mtsil.Partner_Type(+)= 1
2139 AND mssfe.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
2140
2141 /*ASL */
2142 Cursor del_asl is
2143 SELECT T1.INVENTORY_ITEM_ID, msis.USING_ORGANIZATION_ID,
2144 mtil.TP_ID SUPPLIER_ID, mtsil.TP_SITE_ID SUPPLIER_SITE_ID
2145 FROM MSC_TP_ID_LID mtil,
2146 MSC_TP_SITE_ID_LID mtsil,
2147 MSC_ITEM_ID_LID t1,
2148 MSC_ST_ITEM_SUPPLIERS msis
2149 WHERE t1.SR_INVENTORY_ITEM_ID= msis.inventory_item_id
2150 AND t1.sr_instance_id= msis.sr_instance_id
2151 AND mtil.SR_TP_ID = msis.SUPPLIER_ID
2152 AND mtil.SR_INSTANCE_ID = msis.SR_INSTANCE_ID
2153 AND mtil.PARTNER_TYPE = 1
2154 AND mtsil.SR_TP_SITE_ID (+)= msis.Supplier_Site_ID
2155 AND mtsil.SR_INSTANCE_ID (+)= msis.SR_Instance_ID
2156 AND mtsil.Partner_Type (+)= 1
2157 AND nvl(msis.process_flag, -99) <> MSC_UTIL.G_ERROR
2158 AND nvl(msis.company_id, -1) = nvl (mtil.sr_company_id, -1)
2159 AND nvl(msis.company_id, -1) = nvl (mtsil.sr_company_id, -1)
2160 AND msis.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
2161 And msis.deleted_flag=MSC_UTIL.SYS_YES
2162 ;
2163
2164
2165 CURSOR c4 IS
2166 SELECT
2167 Msis.ORGANIZATION_ID,
2168 Msis.USING_ORGANIZATION_ID,
2169 Mtil.TP_ID SUPPLIER_ID,
2170 Mtsil.TP_SITE_ID SUPPLIER_SITE_ID,
2171 T1.INVENTORY_ITEM_ID,
2172 Msis.PROCESSING_LEAD_TIME,
2173 Msis.MINIMUM_ORDER_QUANTITY,
2174 Msis.FIXED_LOT_MULTIPLE,
2175 Msis.DELIVERY_CALENDAR_CODE,
2176 Msis.SUPPLIER_CAP_OVER_UTIL_COST,
2177 Msis.PURCHASING_UNIT_OF_MEASURE,
2178 Msis.SR_INSTANCE_ID2,
2179 Msis.ITEM_PRICE, --Item Price by Supplier Fix
2180 /* SCE Change starts */
2181 -- Pull Supplier Item Name for cross-reference functionality.
2182 Msis.supplier_item_name,
2183 Msis.planner_code,
2184 Msis.vmi_flag,
2185 Msis.min_minmax_quantity,
2186 Msis.max_minmax_quantity,
2187 Msis.maximum_order_quantity,
2188 --msis. VMI_UNIT_OF_MEASURE,
2189 Msis.VMI_REPLENISHMENT_APPROVAL,
2190 Msis.ENABLE_VMI_AUTO_REPLENISH_FLAG,
2191 Muom.uom_code,
2192 --Muom1.uom_code VMI_UOM_CODE,
2193 Msis.REPLENISHMENT_METHOD,
2194 Msis.MIN_MINMAX_DAYS,
2195 Msis.MAX_MINMAX_DAYS,
2196 Msis.FORECAST_HORIZON,
2197 Msis.FIXED_ORDER_QUANTITY,
2198 /* SCE Change ends */
2199 Msis.SR_INSTANCE_ID
2200 FROM MSC_TP_ID_LID mtil,
2201 MSC_TP_SITE_ID_LID mtsil,
2202 MSC_ITEM_ID_LID t1,
2203 MSC_ST_ITEM_SUPPLIERS msis,
2204 MSC_UNITS_OF_MEASURE muom
2205 WHERE t1.SR_INVENTORY_ITEM_ID= msis.inventory_item_id
2206 AND t1.sr_instance_id= msis.sr_instance_id
2207 AND mtil.SR_TP_ID = msis.SUPPLIER_ID
2208 AND mtil.SR_INSTANCE_ID = msis.SR_INSTANCE_ID
2209 AND mtil.PARTNER_TYPE = 1
2210 AND mtsil.SR_TP_SITE_ID (+)= msis.Supplier_Site_ID
2211 AND mtsil.SR_INSTANCE_ID (+)= msis.SR_Instance_ID
2212 AND mtsil.Partner_Type (+)= 1
2213 AND nvl (msis.process_flag, -99) <> MSC_UTIL.G_ERROR
2214 AND nvl (msis.company_id, -1) = nvl (mtil.sr_company_id, -1)
2215 AND nvl (msis.company_id, -1) = nvl (mtsil.sr_company_id, -1)
2216 AND nvl (msis.purchasing_unit_of_measure, '-99') = muom.unit_of_measure (+)
2217 AND msis.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
2218 AND msis.deleted_flag=MSC_UTIL.SYS_NO ;
2219
2220 lv_table_name VARCHAR2(100);
2221 lv_sql_stmt VARCHAR2(5000);
2222 lv_last_asl_collection_date DATE;
2223
2224 /*ASL */
2225
2226 c_count NUMBER:= 0;
2227
2228 BEGIN
2229
2230 /*ASL */
2231 IF (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.item_flag = MSC_UTIL.SYS_YES ) THEN
2232 lv_table_name:= 'SYSTEM_ITEMS_' || MSC_CL_COLLECTION.v_INSTANCE_CODE;
2233 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, ' table name in LOAD_SUPPLIER_CAPACITY is ' || lv_table_name);
2234 else
2235 lv_table_name:= 'MSC_SYSTEM_ITEMS';
2236 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, ' table name in LOAD_SUPPLIER_CAPACITY is ' || lv_table_name);
2237 End if;
2238
2239 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, ' table name is ' || lv_table_name);
2240
2241 /*ASL */
2242
2243 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
2244
2245
2246 --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_SUPPLIERS', MSC_CL_COLLECTION.v_instance_id, -1, 'AND nvl(COLLECTED_FLAG,1) <> 2');
2247
2248 IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2249 v_sub_str := 'AND nvl(COLLECTED_FLAG,1) <> 2';
2250 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_SUPPLIERS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
2251 ELSE
2252 v_sub_str := ' AND nvl(COLLECTED_FLAG,1) <> 2'
2253 ||' AND ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
2254 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_SUPPLIERS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
2255 END IF;
2256
2257 END IF;
2258
2259 c_count:= 0;
2260
2261 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh or MSC_CL_COLLECTION.v_is_legacy_refresh) THEN
2262 FOR c_rec IN c1 LOOP
2263
2264 -- Always setting VMI_REFRESH_FLAG to 1 incase of update/inserts
2265 -- to identify records for whom the ASL has changed since the last
2266 -- time VMI ran in CP and redo the VMI calculations.
2267
2268 BEGIN
2269
2270 IF MSC_CL_COLLECTION.v_is_legacy_refresh THEN
2271
2272 UPDATE MSC_ITEM_SUPPLIERS
2273 SET
2274 PROCESSING_LEAD_TIME= c_rec.PROCESSING_LEAD_TIME,
2275 MINIMUM_ORDER_QUANTITY= c_rec.MINIMUM_ORDER_QUANTITY,
2276 FIXED_LOT_MULTIPLIER= c_rec.FIXED_LOT_MULTIPLE,
2277 DELIVERY_CALENDAR_CODE= c_rec.DELIVERY_CALENDAR_CODE,
2278 SUPPLIER_CAP_OVER_UTIL_COST= c_rec.SUPPLIER_CAP_OVER_UTIL_COST,
2279 PURCHASING_UNIT_OF_MEASURE= c_rec.PURCHASING_UNIT_OF_MEASURE,
2280 SR_INSTANCE_ID2= c_rec.SR_INSTANCE_ID2,
2281 ITEM_PRICE= c_rec.ITEM_PRICE, -- Item Price by Supplier Fix
2282 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2283 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2284 /* SCE Change Starts */
2285 /* Pull Supplier Item Name, company_id and company_site_id */
2286 SUPPLIER_ITEM_NAME = c_rec.SUPPLIER_ITEM_NAME,
2287 PLANNER_CODE = c_rec.planner_code,
2288 VMI_FLAG = c_rec.vmi_flag,
2289 MIN_MINMAX_QUANTITY = c_rec.min_minmax_quantity,
2290 MAX_MINMAX_QUANTITY = c_rec.max_minmax_quantity,
2291 MAXIMUM_ORDER_QUANTITY = c_rec.maximum_order_quantity,
2292 UOM_CODE = c_rec.UOM_CODE,
2293 -- VMI_UOM_CODE = c_rec.VMI_UOM_CODE,
2294 /* SCE change ends */
2295 --VMI_UNIT_OF_MEASURE = c_rec.VMI_UNIT_OF_MEASURE,
2296 VMI_REPLENISHMENT_APPROVAL = c_rec.VMI_REPLENISHMENT_APPROVAL,
2297 ENABLE_VMI_AUTO_REPLENISH_FLAG =c_rec.ENABLE_VMI_AUTO_REPLENISH_FLAG,
2298 REPLENISHMENT_METHOD = c_rec.REPLENISHMENT_METHOD,
2299 MIN_MINMAX_DAYS = c_rec.MIN_MINMAX_DAYS,
2300 MAX_MINMAX_DAYS = c_rec.MAX_MINMAX_DAYS,
2301 FORECAST_HORIZON = c_rec.FORECAST_HORIZON,
2302 FIXED_ORDER_QUANTITY = c_rec.FIXED_ORDER_QUANTITY,
2303 VMI_REFRESH_FLAG=1,
2304 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2305 WHERE PLAN_ID= -1
2306 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
2307 AND INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
2308 AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
2309 AND USING_ORGANIZATION_ID= c_rec.USING_ORGANIZATION_ID
2310 AND SUPPLIER_ID= c_rec.SUPPLIER_ID
2311 AND NVL(SUPPLIER_SITE_ID,MSC_UTIL.NULL_VALUE)=
2312 NVL(c_rec.SUPPLIER_SITE_ID,MSC_UTIL.NULL_VALUE);
2313
2314 END IF;
2315
2316 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
2317
2318 INSERT INTO MSC_ITEM_SUPPLIERS
2319 ( PLAN_ID,
2320 ORGANIZATION_ID,
2321 USING_ORGANIZATION_ID,
2322 SUPPLIER_ID,
2323 SUPPLIER_SITE_ID,
2324 INVENTORY_ITEM_ID,
2325 PROCESSING_LEAD_TIME,
2326 MINIMUM_ORDER_QUANTITY,
2327 FIXED_LOT_MULTIPLIER,
2328 DELIVERY_CALENDAR_CODE,
2329 SUPPLIER_CAP_OVER_UTIL_COST,
2330 PURCHASING_UNIT_OF_MEASURE,
2331 SR_INSTANCE_ID2,
2332 ITEM_PRICE, -- Item Price by Supplier Fix
2333 SR_INSTANCE_ID,
2334 REFRESH_NUMBER,
2335 LAST_UPDATE_DATE,
2336 LAST_UPDATED_BY,
2337 CREATION_DATE,
2338 /* SCE Change Starts */
2339 SUPPLIER_ITEM_NAME,
2340 PLANNER_CODE,
2341 VMI_FLAG ,
2342 MIN_MINMAX_QUANTITY,
2343 MAX_MINMAX_QUANTITY,
2344 MAXIMUM_ORDER_QUANTITY,
2345 UOM_CODE,
2346 -- VMI_UOM_CODE,
2347 /* SCE Change Ends */
2348 --VMI_UNIT_OF_MEASURE,
2349 VMI_REPLENISHMENT_APPROVAL,
2350 ENABLE_VMI_AUTO_REPLENISH_FLAG,
2351 REPLENISHMENT_METHOD,
2352 MIN_MINMAX_DAYS,
2353 MAX_MINMAX_DAYS,
2354 FORECAST_HORIZON,
2355 FIXED_ORDER_QUANTITY,
2356 VMI_REFRESH_FLAG,
2357 CREATED_BY)
2358 VALUES
2359 ( -1,
2360 c_rec.ORGANIZATION_ID,
2361 c_rec.USING_ORGANIZATION_ID,
2362 c_rec.SUPPLIER_ID,
2363 c_rec.SUPPLIER_SITE_ID,
2364 c_rec.INVENTORY_ITEM_ID,
2365 c_rec.PROCESSING_LEAD_TIME,
2366 c_rec.MINIMUM_ORDER_QUANTITY,
2367 c_rec.FIXED_LOT_MULTIPLE,
2368 c_rec.DELIVERY_CALENDAR_CODE,
2369 c_rec.SUPPLIER_CAP_OVER_UTIL_COST,
2370 c_rec.PURCHASING_UNIT_OF_MEASURE,
2371 c_rec.SR_INSTANCE_ID2,
2372 c_rec.ITEM_PRICE, -- Item Price by Supplier Fix
2373 c_rec.SR_INSTANCE_ID,
2374 MSC_CL_COLLECTION.v_last_collection_id,
2375 MSC_CL_COLLECTION.v_current_date,
2376 MSC_CL_COLLECTION.v_current_user,
2377 MSC_CL_COLLECTION.v_current_date,
2378 /* SCE Change Starts */
2379 c_rec.SUPPLIER_ITEM_NAME,
2380 c_rec.planner_code,
2381 c_rec.vmi_flag,
2382 c_rec.min_minmax_quantity,
2383 c_rec.max_minmax_quantity,
2384 c_rec.maximum_order_quantity,
2385 c_rec.UOM_CODE,
2386 -- c_rec.VMI_UOM_CODE,
2387 /* SCE Change Ends */
2388 -- c_rec.VMI_UNIT_OF_MEASURE,
2389 c_rec.VMI_REPLENISHMENT_APPROVAL,
2390 c_rec.ENABLE_VMI_AUTO_REPLENISH_FLAG,
2391 c_rec.REPLENISHMENT_METHOD,
2392 c_rec.MIN_MINMAX_DAYS,
2393 c_rec.MAX_MINMAX_DAYS,
2394 c_rec.FORECAST_HORIZON,
2395 c_rec.FIXED_ORDER_QUANTITY,
2396 1,
2397 MSC_CL_COLLECTION.v_current_user );
2398
2399 END IF;
2400
2401 c_count:= c_count+1;
2402
2403 IF c_count> MSC_CL_COLLECTION.PBS THEN
2404 COMMIT;
2405 c_count:= 0;
2406 END IF;
2407
2408 EXCEPTION
2409
2410 WHEN DUP_VAL_ON_INDEX THEN
2411
2412 NULL;
2413
2414 WHEN OTHERS THEN
2415
2416 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
2417
2418 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2419 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2420 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLIER_CAPACITY');
2421 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ITEM_SUPPLIERS');
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 RAISE;
2426
2427 ELSE
2428 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2429
2430 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2431 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2432 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLIER_CAPACITY');
2433 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ITEM_SUPPLIERS');
2434 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2435
2436 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2437 FND_MESSAGE.SET_TOKEN('COLUMN', 'ITEM_NAME');
2438 FND_MESSAGE.SET_TOKEN('VALUE', ITEM_NAME( c_rec.INVENTORY_ITEM_ID));
2439 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2440
2441 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2442 FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
2443 FND_MESSAGE.SET_TOKEN('VALUE',
2444 MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
2445 MSC_CL_COLLECTION.v_instance_id));
2446 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2447
2448 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2449 FND_MESSAGE.SET_TOKEN('COLUMN', 'SUPPLIER_ID');
2450 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SUPPLIER_ID));
2451 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2452
2453 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2454 FND_MESSAGE.SET_TOKEN('COLUMN', 'SUPPLIER_SITE_ID');
2455 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SUPPLIER_SITE_ID));
2456 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2457
2458 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2459 END IF;
2460
2461 END;
2462
2463 END LOOP;
2464
2465 COMMIT;
2466 END IF ;
2467
2468 /* ASL */
2469 IF (MSC_CL_COLLECTION.v_is_incremental_refresh and not MSC_CL_COLLECTION.v_is_legacy_refresh) THEN
2470
2471 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'INCREMENTAL ASL CODE START ');
2472 /*NOT REQUIRED
2473 IF (MSC_CL_COLLECTION.v_coll_prec.app_supp_cap_flag=MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.item_flag=MSC_UTIL.SYS_YES AND NOT MSC_CL_COLLECTION.v_is_legacy_refresh) THEN
2474 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'ADD_NEW_IMPL_ITEM_ASL PROCEDURE BEING CALLED FROM LOAD_SUPPLIER_CAPACITY PROCEDURE ');
2475 ADD_NEW_IMPL_ITEM_ASL;
2476 END IF ;
2477 */
2478
2479 lv_sql_stmt := 'Select min (nvl(LAST_SUCC_ASL_REF_TIME,SYSDATE-365000))'
2480 ||' From msc_instance_orgs '
2481 ||' Where sr_instance_id = ' || MSC_CL_COLLECTION.v_instance_id
2482 ||' And organization_id '|| MSC_UTIL.v_in_org_str;
2483
2484 EXECUTE IMMEDIATE lv_sql_stmt into lv_last_asl_collection_date;
2485 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'last successful ASL Collection refresh time is '||lv_last_asl_collection_date);
2486 c_count:= 0;
2487 FOR del_rec in del_asl LOOP
2488 BEGIN
2489 lv_sql_stmt:= 'Delete MSC_ITEM_SUPPLIERS mis'
2490 ||' WHERE mis.inventory_item_id =' || del_rec.inventory_item_id
2491 ||' AND mis.USING_ORGANIZATION_ID=' || del_rec.USING_ORGANIZATION_ID
2492 ||' AND mis.SUPPLIER_ID = ' || del_rec.SUPPLIER_ID
2493 ||' AND nvl(mis.SUPPLIER_SITE_ID, -1) = nvl( :SUPPLIER_SITE_ID , -1)'
2494 ||' AND mis.sr_instance_id = ' || MSC_CL_COLLECTION.v_instance_id
2495 ||' AND mis.plan_id = -1 '
2496 ||' AND mis.ORGANIZATION_ID ' || MSC_UTIL.v_in_org_str;
2497
2498 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'delete query is ' || lv_sql_stmt);
2499
2500 EXECUTE IMMEDIATE lv_sql_stmt using del_rec.SUPPLIER_SITE_ID;
2501
2502 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'the number of rows deleted '|| SQL%ROWCOUNT);
2503 EXCEPTION
2504 WHEN OTHERS THEN
2505
2506 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
2507
2508 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2509 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2510 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLIER_CAPACITY');
2511 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ITEM_SUPPLIERS');
2512 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2513
2514 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2515 RAISE;
2516
2517 ELSE
2518 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2519
2520 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2521 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2522 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLIER_CAPACITY');
2523 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ITEM_SUPPLIERS');
2524 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2525
2526 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2527 FND_MESSAGE.SET_TOKEN('COLUMN', 'ITEM_NAME');
2528 FND_MESSAGE.SET_TOKEN('VALUE', ITEM_NAME( del_rec.INVENTORY_ITEM_ID));
2529 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2530
2531 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2532 FND_MESSAGE.SET_TOKEN('COLUMN', 'SUPPLIER_ID');
2533 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(del_rec.SUPPLIER_ID));
2534 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2535
2536 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2537 FND_MESSAGE.SET_TOKEN('COLUMN', 'SUPPLIER_SITE_ID');
2538 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(del_rec.SUPPLIER_SITE_ID));
2539 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2540
2541 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2542 END IF ;
2543 END ;
2544
2545 END LOOP;
2546 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'number of row deleted ' || c_count);
2547 COMMIT;
2548
2549 /*Insert Logic */
2550 FOR c_rec in c4 LOOP
2551 BEGIN
2552 UPDATE MSC_ITEM_SUPPLIERS
2553 SET
2554 PROCESSING_LEAD_TIME= c_rec.PROCESSING_LEAD_TIME,
2555 MINIMUM_ORDER_QUANTITY= c_rec.MINIMUM_ORDER_QUANTITY,
2556 FIXED_LOT_MULTIPLIER= c_rec.FIXED_LOT_MULTIPLE,
2557 DELIVERY_CALENDAR_CODE= c_rec.DELIVERY_CALENDAR_CODE,
2558 SUPPLIER_CAP_OVER_UTIL_COST= c_rec.SUPPLIER_CAP_OVER_UTIL_COST,
2559 PURCHASING_UNIT_OF_MEASURE= c_rec.PURCHASING_UNIT_OF_MEASURE,
2560 SR_INSTANCE_ID2= c_rec.SR_INSTANCE_ID2,
2561 ITEM_PRICE= c_rec.ITEM_PRICE, -- Item Price by Supplier Fix
2562 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2563 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2564 /* SCE Change Starts */
2565 /* Pull Supplier Item Name, company_id and company_site_id */
2566 SUPPLIER_ITEM_NAME = c_rec.SUPPLIER_ITEM_NAME,
2567 PLANNER_CODE = c_rec.planner_code,
2568 VMI_FLAG = c_rec.vmi_flag,
2569 MIN_MINMAX_QUANTITY = c_rec.min_minmax_quantity,
2570 MAX_MINMAX_QUANTITY = c_rec.max_minmax_quantity,
2571 MAXIMUM_ORDER_QUANTITY = c_rec.maximum_order_quantity,
2572 UOM_CODE = c_rec.UOM_CODE,
2573 -- VMI_UOM_CODE = c_rec.VMI_UOM_CODE,
2574 /* SCE change ends */
2575 --VMI_UNIT_OF_MEASURE = c_rec.VMI_UNIT_OF_MEASURE,
2576 VMI_REPLENISHMENT_APPROVAL = c_rec.VMI_REPLENISHMENT_APPROVAL,
2577 ENABLE_VMI_AUTO_REPLENISH_FLAG =c_rec.ENABLE_VMI_AUTO_REPLENISH_FLAG,
2578 REPLENISHMENT_METHOD = c_rec.REPLENISHMENT_METHOD,
2579 MIN_MINMAX_DAYS = c_rec.MIN_MINMAX_DAYS,
2580 MAX_MINMAX_DAYS = c_rec.MAX_MINMAX_DAYS,
2581 FORECAST_HORIZON = c_rec.FORECAST_HORIZON,
2582 FIXED_ORDER_QUANTITY = c_rec.FIXED_ORDER_QUANTITY,
2583 VMI_REFRESH_FLAG=1,
2584 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2585 WHERE PLAN_ID= -1
2586 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
2587 AND INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
2588 AND USING_ORGANIZATION_ID= c_rec.USING_ORGANIZATION_ID
2589 AND SUPPLIER_ID= c_rec.SUPPLIER_ID
2590 AND NVL(SUPPLIER_SITE_ID,MSC_UTIL.NULL_VALUE)=
2591 NVL(c_rec.SUPPLIER_SITE_ID,MSC_UTIL.NULL_VALUE);
2592
2593 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'ROW UPDATED ' || SQL%ROWCOUNT);
2594
2595 IF SQL%NOTFOUND THEN
2596 IF c_rec.using_organization_id =-1 THEN
2597 lv_sql_stmt:= 'INSERT INTO MSC_ITEM_SUPPLIERS'
2598 || ' ( PLAN_ID, '
2599 ||' ORGANIZATION_ID, '
2600 ||' USING_ORGANIZATION_ID,'
2601 ||' SUPPLIER_ID, '
2602 ||' SUPPLIER_SITE_ID,'
2603 ||' INVENTORY_ITEM_ID,'
2604 ||' PROCESSING_LEAD_TIME,'
2605 ||' MINIMUM_ORDER_QUANTITY,'
2606 ||' FIXED_LOT_MULTIPLIER,'
2607 ||' DELIVERY_CALENDAR_CODE,'
2608 ||' SUPPLIER_CAP_OVER_UTIL_COST,'
2609 ||' PURCHASING_UNIT_OF_MEASURE,'
2610 ||' SR_INSTANCE_ID2,'
2611 ||' ITEM_PRICE,' -- Item Price by Supplier Fix
2612 ||' SR_INSTANCE_ID,'
2613 ||' REFRESH_NUMBER,'
2614 ||' LAST_UPDATE_DATE,'
2615 ||' LAST_UPDATED_BY,'
2616 ||' CREATION_DATE,'
2617 /* SCE Change Starts */
2618 ||' SUPPLIER_ITEM_NAME,'
2619 ||' PLANNER_CODE,'
2620 ||' VMI_FLAG ,'
2621 ||' MIN_MINMAX_QUANTITY,'
2622 ||' MAX_MINMAX_QUANTITY,'
2623 ||' MAXIMUM_ORDER_QUANTITY,'
2624 ||' UOM_CODE,'
2625 -- VMI_UOM_CODE,
2626 /* SCE Change Ends */
2627 --VMI_UNIT_OF_MEASURE,'
2628 ||' VMI_REPLENISHMENT_APPROVAL,'
2629 ||' ENABLE_VMI_AUTO_REPLENISH_FLAG,'
2630 ||' REPLENISHMENT_METHOD,'
2631 ||' MIN_MINMAX_DAYS,'
2632 ||' MAX_MINMAX_DAYS,'
2633 ||' FORECAST_HORIZON,'
2634 ||' FIXED_ORDER_QUANTITY,'
2635 ||' VMI_REFRESH_FLAG,'
2636 ||' CREATED_BY)'
2637 ||'select'
2638 ||' -1,'
2639 ||' msi.ORGANIZATION_ID,'
2640 ||' :USING_ORGANIZATION_ID,'
2641 ||' :SUPPLIER_ID,'
2642 ||' :SUPPLIER_SITE_ID,'
2643 ||' :INVENTORY_ITEM_ID,'
2644 ||' :PROCESSING_LEAD_TIME,'
2645 ||' :MINIMUM_ORDER_QUANTITY,'
2646 ||' :FIXED_LOT_MULTIPLE,'
2647 ||' :DELIVERY_CALENDAR_CODE,'
2648 ||' :SUPPLIER_CAP_OVER_UTIL_COST,'
2649 ||' :PURCHASING_UNIT_OF_MEASURE,'
2650 ||' :SR_INSTANCE_ID2,'
2651 ||' :ITEM_PRICE,'
2652 ||' :SR_INSTANCE_ID,'
2653 ||' :v_last_collection_id,'
2654 ||' :v_current_date,'
2655 ||' :v_current_user,'
2656 ||' :v_current_date,'
2657 ||' :SUPPLIER_ITEM_NAME,'
2658 ||' :planner_code,'
2659 ||' :vmi_flag,'
2660 ||' :min_minmax_quantity,'
2661 ||' :max_minmax_quantity,'
2662 ||' :maximum_order_quantity,'
2663 ||' :UOM_CODE,'
2664 ||' :VMI_REPLENISHMENT_APPROVAL,'
2665 ||' :ENABLE_VMI_AUTO_REPLENISH_FLAG,'
2666 ||' :REPLENISHMENT_METHOD,'
2667 ||' :MIN_MINMAX_DAYS,'
2668 ||' :MAX_MINMAX_DAYS,'
2669 ||' :FORECAST_HORIZON,'
2670 ||' :FIXED_ORDER_QUANTITY,'
2671 ||' 1,'
2672 ||' :v_current_user '
2673 ||' FROM '
2674 || lv_table_name ||' msi '
2675 ||' WHERE msi.inventory_item_id = '|| c_rec.INVENTORY_ITEM_ID
2676 ||' and msi.organization_id ' || MSC_UTIL.v_in_org_str
2677 ||' and msi.sr_instance_id = '|| MSC_CL_COLLECTION.v_instance_id
2678 ||' and msi.plan_id =-1 ' ;
2679
2680 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1,'the insert statement is ' || lv_sql_stmt );
2681
2682 execute immediate lv_sql_stmt using c_rec.USING_ORGANIZATION_ID,
2683 c_rec.SUPPLIER_ID,
2684 c_rec.SUPPLIER_SITE_ID,
2685 c_rec.INVENTORY_ITEM_ID,
2686 c_rec.PROCESSING_LEAD_TIME,
2687 c_rec.MINIMUM_ORDER_QUANTITY,
2688 c_rec.FIXED_LOT_MULTIPLE,
2689 c_rec.DELIVERY_CALENDAR_CODE,
2690 c_rec.SUPPLIER_CAP_OVER_UTIL_COST,
2691 c_rec.PURCHASING_UNIT_OF_MEASURE,
2692 c_rec.SR_INSTANCE_ID2,
2693 c_rec.ITEM_PRICE,
2694 c_rec.SR_INSTANCE_ID,
2695 MSC_CL_COLLECTION.v_last_collection_id,
2696 MSC_CL_COLLECTION.v_current_date,
2697 MSC_CL_COLLECTION.v_current_user,
2698 MSC_CL_COLLECTION.v_current_date,
2699 c_rec.SUPPLIER_ITEM_NAME,
2700 c_rec.planner_code,
2701 c_rec.vmi_flag,
2702 c_rec.min_minmax_quantity,
2703 c_rec.max_minmax_quantity,
2704 c_rec.maximum_order_quantity,
2705 c_rec.UOM_CODE,
2706 c_rec.VMI_REPLENISHMENT_APPROVAL,
2707 c_rec.ENABLE_VMI_AUTO_REPLENISH_FLAG,
2708 c_rec.REPLENISHMENT_METHOD,
2709 c_rec.MIN_MINMAX_DAYS,
2710 c_rec.MAX_MINMAX_DAYS,
2711 c_rec.FORECAST_HORIZON,
2712 c_rec.FIXED_ORDER_QUANTITY,
2713 MSC_CL_COLLECTION.v_current_user ;
2714
2715 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'ROW INSERTED WITH USING ORG -1 ' || SQL%ROWCOUNT);
2716
2717 ELSE
2718 INSERT INTO MSC_ITEM_SUPPLIERS
2719 ( PLAN_ID,
2720 ORGANIZATION_ID,
2721 USING_ORGANIZATION_ID,
2722 SUPPLIER_ID,
2723 SUPPLIER_SITE_ID,
2724 INVENTORY_ITEM_ID,
2725 PROCESSING_LEAD_TIME,
2726 MINIMUM_ORDER_QUANTITY,
2727 FIXED_LOT_MULTIPLIER,
2728 DELIVERY_CALENDAR_CODE,
2729 SUPPLIER_CAP_OVER_UTIL_COST,
2730 PURCHASING_UNIT_OF_MEASURE,
2731 SR_INSTANCE_ID2,
2732 ITEM_PRICE, -- Item Price by Supplier Fix
2733 SR_INSTANCE_ID,
2734 REFRESH_NUMBER,
2735 LAST_UPDATE_DATE,
2736 LAST_UPDATED_BY,
2737 CREATION_DATE,
2738 /* SCE Change Starts */
2739 SUPPLIER_ITEM_NAME,
2740 PLANNER_CODE,
2741 VMI_FLAG ,
2742 MIN_MINMAX_QUANTITY,
2743 MAX_MINMAX_QUANTITY,
2744 MAXIMUM_ORDER_QUANTITY,
2745 UOM_CODE,
2746 -- VMI_UOM_CODE,
2747 /* SCE Change Ends */
2748 --VMI_UNIT_OF_MEASURE,
2749 VMI_REPLENISHMENT_APPROVAL,
2750 ENABLE_VMI_AUTO_REPLENISH_FLAG,
2751 REPLENISHMENT_METHOD,
2752 MIN_MINMAX_DAYS,
2753 MAX_MINMAX_DAYS,
2754 FORECAST_HORIZON,
2755 FIXED_ORDER_QUANTITY,
2756 VMI_REFRESH_FLAG,
2757 CREATED_BY)
2758 VALUES
2759 ( -1,
2760 c_rec.USING_ORGANIZATION_ID,
2761 c_rec.USING_ORGANIZATION_ID,
2762 c_rec.SUPPLIER_ID,
2763 c_rec.SUPPLIER_SITE_ID,
2764 c_rec.INVENTORY_ITEM_ID,
2765 c_rec.PROCESSING_LEAD_TIME,
2766 c_rec.MINIMUM_ORDER_QUANTITY,
2767 c_rec.FIXED_LOT_MULTIPLE,
2768 c_rec.DELIVERY_CALENDAR_CODE,
2769 c_rec.SUPPLIER_CAP_OVER_UTIL_COST,
2770 c_rec.PURCHASING_UNIT_OF_MEASURE,
2771 c_rec.SR_INSTANCE_ID2,
2772 c_rec.ITEM_PRICE, -- Item Price by Supplier Fix
2773 c_rec.SR_INSTANCE_ID,
2774 MSC_CL_COLLECTION.v_last_collection_id,
2775 MSC_CL_COLLECTION.v_current_date,
2776 MSC_CL_COLLECTION.v_current_user,
2777 MSC_CL_COLLECTION.v_current_date,
2778 /* SCE Change Starts */
2779 c_rec.SUPPLIER_ITEM_NAME,
2780 c_rec.planner_code,
2781 c_rec.vmi_flag,
2782 c_rec.min_minmax_quantity,
2783 c_rec.max_minmax_quantity,
2784 c_rec.maximum_order_quantity,
2785 c_rec.UOM_CODE,
2786 -- c_rec.VMI_UOM_CODE,
2787 /* SCE Change Ends */
2788 -- c_rec.VMI_UNIT_OF_MEASURE,
2789 c_rec.VMI_REPLENISHMENT_APPROVAL,
2790 c_rec.ENABLE_VMI_AUTO_REPLENISH_FLAG,
2791 c_rec.REPLENISHMENT_METHOD,
2792 c_rec.MIN_MINMAX_DAYS,
2793 c_rec.MAX_MINMAX_DAYS,
2794 c_rec.FORECAST_HORIZON,
2795 c_rec.FIXED_ORDER_QUANTITY,
2796 1,
2797 MSC_CL_COLLECTION.v_current_user );
2798
2799 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'ROW INSERTED WITH USING org id not -1 ' || SQL%ROWCOUNT);
2800
2801 END IF ;
2802 END IF ;
2803
2804 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, ' the item id ' || c_rec.INVENTORY_ITEM_ID);
2805 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, ' the using organization_id is ' || c_rec.USING_ORGANIZATION_ID);
2806 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, ' the supplier id is ' || c_rec.SUPPLIER_ID);
2807 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, ' the supplier site id is ' || c_rec.SUPPLIER_SITE_ID);
2808
2809 EXCEPTION
2810 WHEN OTHERS THEN
2811
2812 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
2813
2814 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2815 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2816 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLIER_CAPACITY');
2817 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ITEM_SUPPLIERS');
2818 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2819
2820 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2821 RAISE;
2822
2823 ELSE
2824
2825 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2826
2827 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2828 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2829 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLIER_CAPACITY');
2830 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ITEM_SUPPLIERS');
2831 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2832
2833 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2834 END IF;-- exception end
2835 END ; -- begin end
2836 END LOOP; -- loop insert or update
2837 COMMIT ;
2838
2839 END IF ; -- v_is_incremental
2840
2841 /*ASL */
2842
2843 IF MSC_CL_COLLECTION.v_apps_ver <> MSC_UTIL.G_APPS107 AND
2844 MSC_CL_COLLECTION.v_apps_ver <> MSC_UTIL.G_APPS110 THEN
2845
2846 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
2847
2848 --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_CAPACITIES', MSC_CL_COLLECTION.v_instance_id, -1);
2849
2850 IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2851 if MSC_CL_COLLECTION.v_coll_prec.app_supp_cap_flag= MSC_UTIL.ASL_YES_RETAIN_CP THEN
2852 v_sub_str :=' AND COLLECTED_FLAG <> 3';
2853 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_CAPACITIES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
2854 else
2855 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_CAPACITIES', MSC_CL_COLLECTION.v_instance_id, -1);
2856 end if ;
2857 ELSE
2858 if MSC_CL_COLLECTION.v_coll_prec.app_supp_cap_flag= MSC_UTIL.ASL_YES_RETAIN_CP THEN
2859 v_sub_str :=' AND COLLECTED_FLAG <> 3 AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2860 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_CAPACITIES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
2861 else
2862 v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2863 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_CAPACITIES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
2864 end if ;
2865 END IF;
2866
2867 END IF;
2868
2869 --============ Incremental Refresh for DELETE =========
2870
2871 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
2872
2873 FOR c_rec IN c2d LOOP
2874
2875 UPDATE MSC_SUPPLIER_CAPACITIES
2876 SET
2877 USING_ORGANIZATION_ID= c_rec.USING_ORGANIZATION_ID,
2878 CAPACITY= NULL,
2879 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2880 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2881 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2882 WHERE PLAN_ID= -1
2883 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
2884 AND INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
2885 AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
2886 AND SUPPLIER_ID= c_rec.SUPPLIER_ID
2887 AND NVL(SUPPLIER_SITE_ID,MSC_UTIL.NULL_VALUE)=
2888 NVL(c_rec.SUPPLIER_SITE_ID,MSC_UTIL.NULL_VALUE)
2889 AND FROM_DATE= c_rec.FROM_DATE
2890 AND COLLECTED_FLAG=1;
2891
2892 END LOOP;
2893
2894 FOR c_rec IN c5d LOOP
2895
2896 DELETE FROM MSC_SUPPLIER_CAPACITIES
2897 WHERE PLAN_ID= -1
2898 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
2899 AND INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
2900 AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
2901 AND SUPPLIER_ID= c_rec.SUPPLIER_ID
2902 AND NVL(SUPPLIER_SITE_ID,MSC_UTIL.NULL_VALUE)=
2903 NVL(c_rec.SUPPLIER_SITE_ID,MSC_UTIL.NULL_VALUE)
2904 AND COLLECTED_FLAG=3;
2905
2906 END LOOP;
2907
2908
2909 END IF;
2910
2911 COMMIT;
2912
2913 c_count:= 0;
2914
2915 FOR c_rec IN c2 LOOP
2916
2917 BEGIN
2918
2919 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
2920
2921 UPDATE MSC_SUPPLIER_CAPACITIES
2922 SET
2923 USING_ORGANIZATION_ID= c_rec.USING_ORGANIZATION_ID,
2924 TO_DATE= c_rec.TO_DATE,
2925 CAPACITY= c_rec.CAPACITY,
2926 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2927 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2928 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2929 WHERE PLAN_ID= -1
2930 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
2931 AND INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
2932 AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
2933 AND SUPPLIER_ID= c_rec.SUPPLIER_ID
2934 AND NVL(SUPPLIER_SITE_ID,MSC_UTIL.NULL_VALUE)=
2935 NVL(c_rec.SUPPLIER_SITE_ID,MSC_UTIL.NULL_VALUE)
2936 AND FROM_DATE= c_rec.FROM_DATE
2937 AND COLLECTED_FLAG=1;
2938
2939 END IF;
2940
2941 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
2942
2943 INSERT INTO MSC_SUPPLIER_CAPACITIES
2944 ( TRANSACTION_ID,
2945 PLAN_ID,
2946 ORGANIZATION_ID,
2947 USING_ORGANIZATION_ID,
2948 SUPPLIER_ID,
2949 SUPPLIER_SITE_ID,
2950 INVENTORY_ITEM_ID,
2951 FROM_DATE,
2952 TO_DATE,
2953 CAPACITY,
2954 SR_INSTANCE_ID,
2955 COLLECTED_FLAG,
2956 REFRESH_NUMBER,
2957 LAST_UPDATE_DATE,
2958 LAST_UPDATED_BY,
2959 CREATION_DATE,
2960 CREATED_BY)
2961 VALUES
2962 ( MSC_SUPPLIER_CAPACITIES_S.NEXTVAL,
2963 -1,
2964 c_rec.ORGANIZATION_ID,
2965 c_rec.USING_ORGANIZATION_ID,
2966 c_rec.SUPPLIER_ID,
2967 c_rec.SUPPLIER_SITE_ID,
2968 c_rec.INVENTORY_ITEM_ID,
2969 c_rec.FROM_DATE,
2970 c_rec.TO_DATE,
2971 c_rec.CAPACITY,
2972 c_rec.SR_INSTANCE_ID,
2973 1,
2974 MSC_CL_COLLECTION.v_last_collection_id,
2975 MSC_CL_COLLECTION.v_current_date,
2976 MSC_CL_COLLECTION.v_current_user,
2977 MSC_CL_COLLECTION.v_current_date,
2978 MSC_CL_COLLECTION.v_current_user );
2979
2980 END IF;
2981
2982 c_count:= c_count+1;
2983
2984 IF c_count> MSC_CL_COLLECTION.PBS THEN
2985 COMMIT;
2986 c_count:= 0;
2987 END IF;
2988
2989 EXCEPTION
2990
2991 WHEN OTHERS THEN
2992
2993 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
2994
2995 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2996 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2997 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLIER_CAPACITY');
2998 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SUPPLIER_CAPACITIES');
2999 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3000
3001 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3002 RAISE;
3003
3004 ELSE
3005 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3006
3007 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3008 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3009 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLIER_CAPACITY');
3010 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SUPPLIER_CAPACITIES');
3011 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3012
3013 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3014 FND_MESSAGE.SET_TOKEN('COLUMN', 'ITEM_NAME');
3015 FND_MESSAGE.SET_TOKEN('VALUE', ITEM_NAME( c_rec.INVENTORY_ITEM_ID));
3016 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3017
3018 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3019 FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
3020 FND_MESSAGE.SET_TOKEN('VALUE',
3021 MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
3022 MSC_CL_COLLECTION.v_instance_id));
3023 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3024
3025 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3026 FND_MESSAGE.SET_TOKEN('COLUMN', 'SUPPLIER_ID');
3027 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SUPPLIER_ID));
3028 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3029
3030 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3031 FND_MESSAGE.SET_TOKEN('COLUMN', 'SUPPLIER_SITE_ID');
3032 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SUPPLIER_SITE_ID));
3033 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3034
3035 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3036 FND_MESSAGE.SET_TOKEN('COLUMN', 'FROM_DATE');
3037 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.FROM_DATE));
3038 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3039
3040 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3041 END IF;
3042
3043 END;
3044
3045 END LOOP;
3046
3047 COMMIT;
3048
3049 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
3050
3051 --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_FLEX_FENCES', MSC_CL_COLLECTION.v_instance_id, -1);
3052
3053 IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
3054 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_FLEX_FENCES', MSC_CL_COLLECTION.v_instance_id, -1);
3055 ELSE
3056 v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
3057 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_FLEX_FENCES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
3058 END IF;
3059
3060 END IF;
3061
3062 c_count:= 0;
3063
3064 FOR c_rec IN c3 LOOP
3065
3066 BEGIN
3067
3068 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
3069
3070 UPDATE MSC_SUPPLIER_FLEX_FENCES
3071 SET
3072 USING_ORGANIZATION_ID= c_rec.USING_ORGANIZATION_ID,
3073 TOLERANCE_PERCENTAGE= c_rec.TOLERANCE_PERCENTAGE,
3074 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
3075 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3076 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3077 WHERE PLAN_ID= -1
3078 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
3079 AND INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
3080 AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
3081 AND SUPPLIER_ID= c_rec.SUPPLIER_ID
3082 AND NVL(SUPPLIER_SITE_ID,MSC_UTIL.NULL_VALUE)=
3083 NVL(c_rec.SUPPLIER_SITE_ID,MSC_UTIL.NULL_VALUE)
3084 AND FENCE_DAYS= c_rec.FENCE_DAYS;
3085
3086 END IF;
3087
3088 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
3089
3090 INSERT INTO MSC_SUPPLIER_FLEX_FENCES
3091 ( TRANSACTION_ID,
3092 PLAN_ID,
3093 SUPPLIER_ID,
3094 SUPPLIER_SITE_ID,
3095 ORGANIZATION_ID,
3096 USING_ORGANIZATION_ID,
3097 INVENTORY_ITEM_ID,
3098 FENCE_DAYS,
3099 TOLERANCE_PERCENTAGE,
3100 SR_INSTANCE_ID,
3101 REFRESH_NUMBER,
3102 LAST_UPDATE_DATE,
3103 LAST_UPDATED_BY,
3104 CREATION_DATE,
3105 CREATED_BY)
3106 VALUES
3107 ( MSC_SUPPLIER_FLEX_FENCES_S.NEXTVAL,
3108 -1,
3109 c_rec.SUPPLIER_ID,
3110 c_rec.SUPPLIER_SITE_ID,
3111 c_rec.ORGANIZATION_ID,
3112 c_rec.USING_ORGANIZATION_ID,
3113 c_rec.INVENTORY_ITEM_ID,
3114 c_rec.FENCE_DAYS,
3115 c_rec.TOLERANCE_PERCENTAGE,
3116 c_rec.SR_INSTANCE_ID,
3117 MSC_CL_COLLECTION.v_last_collection_id,
3118 MSC_CL_COLLECTION.v_current_date,
3119 MSC_CL_COLLECTION.v_current_user,
3120 MSC_CL_COLLECTION.v_current_date,
3121 MSC_CL_COLLECTION.v_current_user );
3122
3123 END IF;
3124
3125 c_count:= c_count+1;
3126
3127 IF c_count> MSC_CL_COLLECTION.PBS THEN
3128 COMMIT;
3129 c_count:= 0;
3130 END IF;
3131
3132 EXCEPTION
3133
3134 WHEN OTHERS THEN
3135
3136 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
3137
3138 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3139 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3140 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLIER_CAPACITY');
3141 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SUPPLIER_FLEX_FENCES');
3142 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3143
3144 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3145 RAISE;
3146
3147 ELSE
3148 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3149
3150 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3151 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3152 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLIER_CAPACITY');
3153 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SUPPLIER_FLEX_FENCES');
3154 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3155
3156 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3157 FND_MESSAGE.SET_TOKEN('COLUMN', 'ITEM_NAME');
3158 FND_MESSAGE.SET_TOKEN('VALUE', ITEM_NAME( c_rec.INVENTORY_ITEM_ID));
3159 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3160
3161 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3162 FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
3163 FND_MESSAGE.SET_TOKEN('VALUE',
3164 MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
3165 MSC_CL_COLLECTION.v_instance_id));
3166 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3167
3168 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3169 FND_MESSAGE.SET_TOKEN('COLUMN', 'SUPPLIER_ID');
3170 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SUPPLIER_ID));
3171 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3172
3173 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3174 FND_MESSAGE.SET_TOKEN('COLUMN', 'SUPPLIER_SITE_ID');
3175 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SUPPLIER_SITE_ID));
3176 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3177
3178 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3179 FND_MESSAGE.SET_TOKEN('COLUMN', 'FENCE_DAYS');
3180 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.FENCE_DAYS));
3181 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3182
3183 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3184 END IF;
3185
3186 END;
3187
3188 END LOOP;
3189
3190 COMMIT;
3191
3192 END IF; -- MSC_CL_COLLECTION.v_apps_ver
3193
3194 END LOAD_SUPPLIER_CAPACITY;
3195
3196 --==================================================================
3197 --==================================================================
3198
3199 PROCEDURE LOAD_ITEM IS
3200
3201 CURSOR c1 IS
3202 SELECT
3203 msi.ORGANIZATION_ID,
3204 t1.INVENTORY_ITEM_ID, -- msi.INVENTORY_ITEM_ID,
3205 msi.ITEM_NAME,
3206 msi.LOTS_EXPIRATION,
3207 msi.LOT_CONTROL_CODE,
3208 msi.SHRINKAGE_RATE,
3209 msi.FIXED_DAYS_SUPPLY,
3210 msi.FIXED_ORDER_QUANTITY,
3211 msi.FIXED_LOT_MULTIPLIER,
3212 msi.MINIMUM_ORDER_QUANTITY,
3213 msi.MAXIMUM_ORDER_QUANTITY,
3214 msi.ROUNDING_CONTROL_TYPE,
3215 msi.PLANNING_TIME_FENCE_CODE,
3216 msi.PLANNING_TIME_FENCE_DAYS,
3217 msi.DEMAND_TIME_FENCE_DAYS,
3218 replace(substrb(msi.DESCRIPTION,1,240),v_chr9,' ') DESCRIPTION,
3219 msi.RELEASE_TIME_FENCE_CODE,
3220 msi.RELEASE_TIME_FENCE_DAYS,
3221 msi.IN_SOURCE_PLAN,
3222 msi.REVISION,
3223 msi.SR_CATEGORY_ID,
3224 msi.CATEGORY_NAME,
3225 msi.ABC_CLASS_ID,
3226 msi.ABC_CLASS_NAME,
3227 msi.MRP_PLANNING_CODE,
3228 msi.FIXED_LEAD_TIME,
3229 msi.VARIABLE_LEAD_TIME,
3230 msi.PREPROCESSING_LEAD_TIME,
3231 msi.POSTPROCESSING_LEAD_TIME,
3232 msi.FULL_LEAD_TIME,
3233 msi.CUMULATIVE_TOTAL_LEAD_TIME,
3234 msi.CUM_MANUFACTURING_LEAD_TIME,
3235 msi.UOM_CODE,
3236 msi.UNIT_WEIGHT,
3237 msi.UNIT_VOLUME,
3238 msi.WEIGHT_UOM,
3239 msi.VOLUME_UOM,
3240 t3.Inventory_Item_ID PRODUCT_FAMILY_ID,
3241 msi.ATP_RULE_ID,
3242 msi.ATP_COMPONENTS_FLAG,
3243 msi.BUILT_IN_WIP_FLAG,
3244 msi.PURCHASING_ENABLED_FLAG,
3245 msi.PLANNING_MAKE_BUY_CODE,
3246 msi.REPETITIVE_TYPE,
3247 msi.REPETITIVE_VARIANCE_DAYS,
3248 msi.STANDARD_COST,
3249 msi.CARRYING_COST,
3250 msi.ORDER_COST,
3251 nvl(msi.DMD_LATENESS_COST, mtp.DEMAND_LATENESS_COST),
3252 msi.SS_PENALTY_COST,
3253 msi.SUPPLIER_CAP_OVERUTIL_COST,
3254 nvl(msi.LIST_PRICE,msi.STANDARD_COST) LIST_PRICE,
3255 msi.AVERAGE_DISCOUNT,
3256 msi.ENGINEERING_ITEM_FLAG,
3257 msi.INVENTORY_ITEM_FLAG,
3258 msi.WIP_SUPPLY_TYPE,
3259 msi.MRP_SAFETY_STOCK_CODE,
3260 msi.MRP_SAFETY_STOCK_PERCENT,
3261 msi.SAFETY_STOCK_BUCKET_DAYS,
3262 -- msi.INVENTORY_USE_UP_DATE,
3263 msi.BUYER_NAME,
3264 msi.PLANNER_CODE,
3265 msi.PLANNING_EXCEPTION_SET,
3266 msi.EXCESS_QUANTITY,
3267 msi.SHORTAGE_TYPE,
3268 msi.EXCEPTION_SHORTAGE_DAYS,
3269 msi.EXCESS_TYPE,
3270 msi.EXCEPTION_EXCESS_DAYS,
3271 msi.EXCEPTION_OVERPROMISED_DAYS,
3272 -- msi.EXCEPTION_CODE,
3273 msi.BOM_ITEM_TYPE,
3274 msi.ATO_FORECAST_CONTROL,
3275 msi.EFFECTIVITY_CONTROL,
3276 msi.ORGANIZATION_CODE,
3277 msi.ACCEPTABLE_RATE_INCREASE,
3278 msi.ACCEPTABLE_RATE_DECREASE,
3279 msi.INVENTORY_PLANNING_CODE,
3280 msi.ACCEPTABLE_EARLY_DELIVERY,
3281 msi.MRP_CALCULATE_ATP_FLAG,
3282 msi.END_ASSEMBLY_PEGGING_FLAG,
3283 t2.INVENTORY_ITEM_ID BASE_ITEM_ID, -- msi.BASE_ITEM_ID,
3284 msi.PRIMARY_SUPPLIER_ID,
3285 /* ATP SUMMARY CHANGES
3286 If the ATP_FLAG is 'C' - this means that this record has been updated and the ATP_FLAG
3287 has been changed from 'N' to 'Y' OR This is a new record and the ATP_FLAG for this item is 'Y'.
3288 We will flag this kind of change by putting a 'Y' in
3289 the column new_atp_flag. This (complimentary with the refresh number)
3290 will be being used for ATP Team so that the ATP Code can identify
3291 such records after a net change collections and calculate the ATP summary for these items.
3292 */
3293 decode(msi.ATP_FLAG,'C', 'Y', msi.ATP_FLAG) ATP_FLAG ,
3294 decode(msi.ATP_FLAG,'C', 'Y', 'N') NEW_ATP_FLAG ,
3295 msi.REVISION_QTY_CONTROL_CODE,
3296 msi.EXPENSE_ACCOUNT,
3297 msi.INVENTORY_ASSET_FLAG,
3298 msi.BUYER_ID,
3299 msi.SOURCE_ORG_ID,
3300 msi.MATERIAL_COST,
3301 msi.RESOURCE_COST,
3302 msi.SR_INVENTORY_ITEM_ID,
3303 msi.DELETED_FLAG,
3304 msi.SR_INSTANCE_ID,
3305 msi.replenish_to_order_flag,
3306 msi.pick_components_flag,
3307 msi.pip_flag,
3308 msi.REDUCE_MPS,
3309 msi.CRITICAL_COMPONENT_FLAG,
3310 msi.VMI_MINIMUM_UNITS,
3311 msi.VMI_MINIMUM_DAYS,
3312 msi.VMI_MAXIMUM_UNITS,
3313 msi.VMI_MAXIMUM_DAYS,
3314 msi.VMI_FIXED_ORDER_QUANTITY,
3315 msi.SO_AUTHORIZATION_FLAG,
3316 msi.CONSIGNED_FLAG,
3317 msi.ASN_AUTOEXPIRE_FLAG,
3318 msi.VMI_FORECAST_TYPE,
3319 msi.FORECAST_HORIZON,
3320 msi.BUDGET_CONSTRAINED,
3321 msi.DAYS_TGT_INV_SUPPLY,
3322 msi.DAYS_TGT_INV_WINDOW,
3323 msi.DAYS_MAX_INV_SUPPLY,
3324 msi.DAYS_MAX_INV_WINDOW,
3325 msi.DRP_PLANNED,
3326 msi.CONTINOUS_TRANSFER,
3327 msi.CONVERGENCE,
3328 msi.DIVERGENCE,
3329 msi.SOURCE_TYPE,
3330 msi.SUBSTITUTION_WINDOW,
3331 msi.CREATE_SUPPLY_FLAG,
3332 msi.yield_conv_factor,
3333 msi.serial_number_control_code ,
3334 msi.Item_Creation_Date,
3335 msi.EAM_ITEM_TYPE, /* ds change change */
3336 msi.pegging_demand_window_days,
3337 msi.pegging_supply_window_days,
3338 msi.REPAIR_LEAD_TIME , --# For Bug 5606037 SRP Changes
3339 msi.PREPOSITION_POINT,
3340 msi.REPAIR_YIELD ,
3341 msi.REPAIR_PROGRAM
3342 FROM MSC_ITEM_ID_LID t3,
3343 MSC_ITEM_ID_LID t2,
3344 MSC_ITEM_ID_LID t1,
3345 MSC_TRADING_PARTNERS mtp,
3346 MSC_ST_SYSTEM_ITEMS msi
3347 WHERE t1.SR_INVENTORY_ITEM_ID= msi.sr_inventory_item_id
3348 AND t1.sr_instance_id= msi.sr_instance_id
3349 AND t2.SR_INVENTORY_ITEM_ID(+)= msi.base_item_id
3350 AND t2.sr_instance_id(+)= msi.sr_instance_id
3351 AND t3.SR_INVENTORY_ITEM_ID(+)= msi.product_family_id
3352 AND t3.sr_instance_id(+)= msi.sr_instance_id
3353 AND mtp.sr_tp_id(+)= msi.organization_id
3354 AND mtp.partner_type(+) = 3
3355 AND mtp.sr_instance_id(+)= msi.sr_instance_id
3356 AND msi.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
3357
3358 /*
3359 CURSOR c2 IS
3360 SELECT distinct
3361 msi.abc_class_id,
3362 msi.abc_class_name,
3363 msi.sr_instance_id,
3364 msi.organization_id
3365 FROM MSC_ST_SYSTEM_ITEMS msi
3366 WHERE msi.sr_instance_id = MSC_CL_COLLECTION.v_instance_id
3367 AND msi.abc_class_id is not null
3368 AND msi.abc_class_name is not null;*/
3369
3370 c_count NUMBER:=0;
3371 lv_tbl VARCHAR2(30);
3372 lv_sql_stmt VARCHAR2(7500);
3373 lv_sql_ins vARCHAR2(8000);
3374 lb_FetchComplete Boolean;
3375 ln_rows_to_fetch Number := nvl(TO_NUMBER( FND_PROFILE.VALUE('MRP_PURGE_BATCH_SIZE')),75000);
3376 lv_MSC_CONFIGURATION VARCHAR2(10) := nvl(fnd_profile.value('MSC_X_CONFIGURATION'), MSC_UTIL.G_CONF_APS);
3377
3378 TYPE CharTblTyp IS TABLE OF VARCHAR2(270);
3379 TYPE NumTblTyp IS TABLE OF NUMBER;
3380 TYPE dateTblTyp IS TABLE OF DATE;
3381
3382 lb_ORGANIZATION_ID NumTblTyp;
3383 lb_INVENTORY_ITEM_ID NumTblTyp;
3384 lb_ITEM_NAME CharTblTyp;
3385 lb_LOTS_EXPIRATION NumTblTyp;
3386 lb_LOT_CONTROL_CODE NumTblTyp;
3387 lb_SHRINKAGE_RATE NumTblTyp;
3388 lb_FIXED_DAYS_SUPPLY NumTblTyp;
3389 lb_FIXED_ORDER_QUANTITY NumTblTyp;
3390 lb_FIXED_LOT_MULTIPLIER NumTblTyp;
3391 lb_MINIMUM_ORDER_QUANTITY NumTblTyp;
3392 lb_MAXIMUM_ORDER_QUANTITY NumTblTyp;
3393 lb_ROUNDING_CONTROL_TYPE NumTblTyp;
3394 lb_PLANNING_TIME_FENCE_CODE NumTblTyp;
3395 lb_PLANNING_TIME_FENCE_DAYS NumTblTyp;
3396 lb_DEMAND_TIME_FENCE_DAYS NumTblTyp;
3397 lb_DESCRIPTION CharTblTyp;
3398 lb_RELEASE_TIME_FENCE_CODE NumTblTyp;
3399 lb_RELEASE_TIME_FENCE_DAYS NumTblTyp;
3400 lb_IN_SOURCE_PLAN NumTblTyp;
3401 lb_REVISION CharTblTyp;
3402 lb_SR_CATEGORY_ID NumTblTyp;
3403 lb_CATEGORY_NAME CharTblTyp;
3404 lb_ABC_CLASS_ID NumTblTyp;
3405 lb_ABC_CLASS_NAME CharTblTyp;
3406 lb_MRP_PLANNING_CODE NumTblTyp;
3407 lb_FIXED_LEAD_TIME NumTblTyp;
3408 lb_VARIABLE_LEAD_TIME NumTblTyp;
3409 lb_PREPROCESSING_LEAD_TIME NumTblTyp;
3410 lb_POSTPROCESSING_LEAD_TIME NumTblTyp;
3411 lb_FULL_LEAD_TIME NumTblTyp;
3412 lb_CUMULATIVE_TOTAL_LEAD_TIME NumTblTyp;
3413 lb_CUM_MANUFACTURING_LEAD_TIME NumTblTyp;
3414 lb_UOM_CODE CharTblTyp;
3415 lb_UNIT_WEIGHT NumTblTyp;
3416 lb_UNIT_VOLUME NumTblTyp;
3417 lb_WEIGHT_UOM CharTblTyp;
3418 lb_VOLUME_UOM CharTblTyp;
3419 lb_PRODUCT_FAMILY_ID NumTblTyp;
3420 lb_ATP_RULE_ID NumTblTyp;
3421 lb_ATP_COMPONENTS_FLAG CharTblTyp;
3422 lb_BUILT_IN_WIP_FLAG NumTblTyp;
3423 lb_PURCHASING_ENABLED_FLAG NumTblTyp;
3424 lb_PLANNING_MAKE_BUY_CODE NumTblTyp;
3425 lb_REPETITIVE_TYPE NumTblTyp;
3426 lb_REPETITIVE_VARIANCE_DAYS NumTblTyp;
3427 lb_STANDARD_COST NumTblTyp;
3428 lb_CARRYING_COST NumTblTyp;
3429 lb_ORDER_COST NumTblTyp;
3430 lb_DMD_LATENESS_COST NumTblTyp;
3431 lb_SS_PENALTY_COST NumTblTyp;
3432 lb_SUPPLIER_CAP_OVERUTIL_COST NumTblTyp;
3433 lb_LIST_PRICE NumTblTyp;
3434 lb_AVERAGE_DISCOUNT NumTblTyp;
3435 lb_ENGINEERING_ITEM_FLAG NumTblTyp;
3436 lb_INVENTORY_ITEM_FLAG NumTblTyp;
3437 lb_WIP_SUPPLY_TYPE NumTblTyp;
3438 lb_MRP_SAFETY_STOCK_CODE NumTblTyp;
3439 lb_MRP_SAFETY_STOCK_PERCENT NumTblTyp;
3440 lb_SAFETY_STOCK_BUCKET_DAYS NumTblTyp;
3441 lb_BUYER_NAME CharTblTyp;
3442 lb_PLANNER_CODE CharTblTyp;
3443 lb_PLANNING_EXCEPTION_SET CharTblTyp;
3444 lb_EXCESS_QUANTITY NumTblTyp;
3445 lb_SHORTAGE_TYPE NumTblTyp;
3446 lb_EXCEPTION_SHORTAGE_DAYS NumTblTyp;
3447 lb_EXCESS_TYPE NumTblTyp;
3448 lb_EXCEPTION_EXCESS_DAYS NumTblTyp;
3449 lb_EXCEPTION_OVERPROMISED_DAYS NumTblTyp;
3450 lb_BOM_ITEM_TYPE NumTblTyp;
3451 lb_ATO_FORECAST_CONTROL NumTblTyp;
3452 lb_EFFECTIVITY_CONTROL NumTblTyp;
3453 lb_ORGANIZATION_CODE CharTblTyp;
3454 lb_ACCEPTABLE_RATE_INCREASE NumTblTyp;
3455 lb_ACCEPTABLE_RATE_DECREASE NumTblTyp;
3456 lb_INVENTORY_PLANNING_CODE NumTblTyp;
3457 lb_ACCEPTABLE_EARLY_DELIVERY NumTblTyp;
3458 lb_MRP_CALCULATE_ATP_FLAG NumTblTyp;
3459 lb_END_ASSEMBLY_PEGGING_FLAG CharTblTyp;
3460 lb_BASE_ITEM_ID NumTblTyp;
3461 lb_PRIMARY_SUPPLIER_ID NumTblTyp;
3462 lb_ATP_FLAG CharTblTyp;
3463 lb_NEW_ATP_FLAG CharTblTyp;
3464 lb_REVISION_QTY_CONTROL_CODE NumTblTyp;
3465 lb_EXPENSE_ACCOUNT NumTblTyp;
3466 lb_INVENTORY_ASSET_FLAG CharTblTyp;
3467 lb_BUYER_ID NumTblTyp;
3468 lb_SOURCE_ORG_ID NumTblTyp;
3469 lb_MATERIAL_COST NumTblTyp;
3470 lb_RESOURCE_COST NumTblTyp;
3471 lb_SR_INVENTORY_ITEM_ID NumTblTyp;
3472 lb_DELETED_FLAG NumTblTyp;
3473 lb_SR_INSTANCE_ID NumTblTyp;
3474 lb_EAM_ITEM_TYPE NumTblTyp; /* ds change change */
3475 lb_REPLENISH_TO_ORDER_FLAG CharTblTyp;
3476 lb_PICK_COMPONENTS_FLAG CharTblTyp;
3477 lb_PIP_FLAG NumTblTyp;
3478 lb_SOURCE_TYPE NumTblTyp;
3479 lb_SUBSTITUTION_WINDOW NumTblTyp;
3480 lb_CREATE_SUPPLY_FLAG NumTblTyp;
3481 lb_YIELD_CONV_FACTOR NumTblTyp;
3482 lb_SERIAL_NUMBER_CONTROL_CODE NumTblTyp;
3483
3484 lb_REDUCE_MPS NumTblTyp;
3485 lb_CRITICAL_COMPONENT_FLAG CharTblTyp;
3486 lb_VMI_MINIMUM_UNITS NumTblTyp;
3487 lb_VMI_MINIMUM_DAYS NumTblTyp;
3488 lb_VMI_MAXIMUM_UNITS NumTblTyp;
3489 lb_VMI_MAXIMUM_DAYS NumTblTyp;
3490 lb_VMI_FIXED_ORDER_QUANTITY NumTblTyp;
3491 lb_SO_AUTHORIZATION_FLAG CharTblTyp;
3492 lb_CONSIGNED_FLAG CharTblTyp;
3493 lb_ASN_AUTOEXPIRE_FLAG CharTblTyp;
3494 lb_VMI_FORECAST_TYPE CharTblTyp;
3495 lb_FORECAST_HORIZON NumTblTyp;
3496 lb_BUDGET_CONSTRAINED NumTblTyp;
3497 lb_DAYS_TGT_INV_SUPPLY NumTblTyp;
3498 lb_DAYS_TGT_INV_WINDOW NumTblTyp;
3499 lb_DAYS_MAX_INV_SUPPLY NumTblTyp;
3500 lb_DAYS_MAX_INV_WINDOW NumTblTyp;
3501 lb_DRP_PLANNED NumTblTyp;
3502 lb_CONTINOUS_TRANSFER NumTblTyp;
3503 lb_CONVERGENCE NumTblTyp;
3504 lb_DIVERGENCE NumTblTyp;
3505 lb_ITEM_CREATION_DATE dateTblTyp;
3506 lb_PEGGING_DEMAND_WINDOW_DAYS NumTblTyp;
3507 lb_PEGGING_SUPPLY_WINDOW_DAYS NumTblTyp;
3508 lb_REPAIR_LEAD_TIME NumTblTyp; --# For Bug 5606037 SRP Changes
3509 lb_REPAIR_YIELD NumTblTyp;
3510 lb_PRE_POSITIONING_POINT CharTblTyp;
3511 lb_REPAIR_PROGRAM NumTblTyp;
3512
3513 lv_errbuf VARCHAR2(240);
3514 lv_retcode NUMBER;
3515
3516 BEGIN
3517
3518 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
3519 lv_tbl:= 'SYSTEM_ITEMS_'||MSC_CL_COLLECTION.v_INSTANCE_CODE;
3520 ELSE
3521 lv_tbl:= 'MSC_SYSTEM_ITEMS';
3522 END IF;
3523
3524
3525 lv_sql_stmt:=
3526 'INSERT INTO '||lv_tbl
3527 ||'( PLAN_ID,'
3528 ||' ORGANIZATION_ID,'
3529 ||' INVENTORY_ITEM_ID,'
3530 ||' ITEM_NAME,'
3531 ||' LOTS_EXPIRATION,'
3532 ||' LOT_CONTROL_CODE,'
3533 ||' SHRINKAGE_RATE,'
3534 ||' FIXED_DAYS_SUPPLY,'
3535 ||' FIXED_ORDER_QUANTITY,'
3536 ||' FIXED_LOT_MULTIPLIER,'
3537 ||' MINIMUM_ORDER_QUANTITY,'
3538 ||' MAXIMUM_ORDER_QUANTITY,'
3539 ||' ROUNDING_CONTROL_TYPE,'
3540 ||' PLANNING_TIME_FENCE_CODE,'
3541 ||' PLANNING_TIME_FENCE_DAYS,'
3542 ||' DEMAND_TIME_FENCE_DAYS,'
3543 ||' DESCRIPTION,'
3544 ||' RELEASE_TIME_FENCE_CODE,'
3545 ||' RELEASE_TIME_FENCE_DAYS,'
3546 ||' IN_SOURCE_PLAN,'
3547 ||' REVISION,'
3548 ||' SR_CATEGORY_ID,'
3549 ||' CATEGORY_NAME,'
3550 ||' ABC_CLASS,'
3551 ||' ABC_CLASS_NAME,'
3552 ||' MRP_PLANNING_CODE,'
3553 ||' FIXED_LEAD_TIME,'
3554 ||' VARIABLE_LEAD_TIME,'
3555 ||' PREPROCESSING_LEAD_TIME,'
3556 ||' POSTPROCESSING_LEAD_TIME,'
3557 ||' FULL_LEAD_TIME,'
3558 ||' CUMULATIVE_TOTAL_LEAD_TIME,'
3559 ||' CUM_MANUFACTURING_LEAD_TIME,'
3560 ||' UOM_CODE,'
3561 ||' UNIT_WEIGHT,'
3562 ||' UNIT_VOLUME,'
3563 ||' WEIGHT_UOM,'
3564 ||' VOLUME_UOM,'
3565 ||' PRODUCT_FAMILY_ID,'
3566 ||' ATP_RULE_ID,'
3567 ||' ATP_COMPONENTS_FLAG,'
3568 ||' BUILD_IN_WIP_FLAG,'
3569 ||' PURCHASING_ENABLED_FLAG,'
3570 ||' PLANNING_MAKE_BUY_CODE,'
3571 ||' REPETITIVE_TYPE,'
3572 ||' REPETITIVE_VARIANCE,'
3573 ||' STANDARD_COST,'
3574 ||' CARRYING_COST,'
3575 ||' ORDER_COST,'
3576 ||' DMD_LATENESS_COST,'
3577 ||' SS_PENALTY_COST,'
3578 ||' SUPPLIER_CAP_OVERUTIL_COST,'
3579 ||' LIST_PRICE,'
3580 ||' AVERAGE_DISCOUNT,'
3581 ||' ENGINEERING_ITEM_FLAG,'
3582 ||' INVENTORY_ITEM_FLAG,'
3583 ||' WIP_SUPPLY_TYPE,'
3584 ||' SAFETY_STOCK_CODE,'
3585 ||' SAFETY_STOCK_PERCENT,'
3586 ||' SAFETY_STOCK_BUCKET_DAYS,'
3587 ||' BUYER_NAME,'
3588 ||' PLANNER_CODE,'
3589 ||' PLANNING_EXCEPTION_SET,'
3590 ||' EXCESS_QUANTITY,'
3591 ||' SHORTAGE_TYPE,'
3592 ||' EXCEPTION_SHORTAGE_DAYS,'
3593 ||' EXCESS_TYPE,'
3594 ||' EXCEPTION_EXCESS_DAYS,'
3595 ||' EXCEPTION_OVERPROMISED_DAYS,'
3596 ||' BOM_ITEM_TYPE,'
3597 ||' ATO_FORECAST_CONTROL,'
3598 ||' EFFECTIVITY_CONTROL,'
3599 ||' ORGANIZATION_CODE,'
3600 ||' ACCEPTABLE_RATE_INCREASE,'
3601 ||' ACCEPTABLE_RATE_DECREASE,'
3602 ||' INVENTORY_PLANNING_CODE,'
3603 ||' ACCEPTABLE_EARLY_DELIVERY,'
3604 ||' CALCULATE_ATP,'
3605 ||' END_ASSEMBLY_PEGGING_FLAG,'
3606 ||' BASE_ITEM_ID,'
3607 ||' PRIMARY_SUPPLIER_ID,'
3608 ||' ATP_FLAG,'
3609 ||' NEW_ATP_FLAG,'
3610 ||' REVISION_QTY_CONTROL_CODE,'
3611 ||' EXPENSE_ACCOUNT,'
3612 ||' INVENTORY_ASSET_FLAG,'
3613 ||' BUYER_ID,'
3614 ||' SOURCE_ORG_ID,'
3615 ||' MATERIAL_COST,'
3616 ||' RESOURCE_COST,'
3617 ||' REPLENISH_TO_ORDER_FLAG,'
3618 ||' PICK_COMPONENTS_FLAG,'
3619 ||' YIELD_CONV_FACTOR,'
3620 ||' PIP_FLAG,'
3621 ||' REDUCE_MPS,'
3622 ||' CRITICAL_COMPONENT_FLAG,'
3623 ||' VMI_MINIMUM_UNITS,'
3624 ||' VMI_MINIMUM_DAYS,'
3625 ||' VMI_MAXIMUM_UNITS,'
3626 ||' VMI_MAXIMUM_DAYS,'
3627 ||' VMI_FIXED_ORDER_QUANTITY,'
3628 ||' SO_AUTHORIZATION_FLAG,'
3629 ||' CONSIGNED_FLAG,'
3630 ||' ASN_AUTOEXPIRE_FLAG,'
3631 ||' VMI_FORECAST_TYPE,'
3632 ||' FORECAST_HORIZON,'
3633 ||' BUDGET_CONSTRAINED,'
3634 ||' DAYS_TGT_INV_SUPPLY,'
3635 ||' DAYS_TGT_INV_WINDOW,'
3636 ||' DAYS_MAX_INV_SUPPLY,'
3637 ||' DAYS_MAX_INV_WINDOW,'
3638 ||' DRP_PLANNED,'
3639 ||' CONTINOUS_TRANSFER,'
3640 ||' CONVERGENCE,'
3641 ||' DIVERGENCE,'
3642 ||' VMI_REFRESH_FLAG,'
3643 ||' SOURCE_TYPE,'
3644 ||' SUBSTITUTION_WINDOW,'
3645 ||' CREATE_SUPPLY_FLAG,'
3646 ||' SERIAL_NUMBER_CONTROL_CODE,'
3647 ||' SR_INVENTORY_ITEM_ID,'
3648 ||' ITEM_CREATION_DATE,'
3649 ||' SR_INSTANCE_ID,'
3650 ||' EAM_ITEM_TYPE,' /* ds change change */
3651 ||' REPAIR_LEAD_TIME,' /* SRP Changes */
3652 ||' PREPOSITION_POINT ,'
3653 ||' REPAIR_YIELD,'
3654 ||' REPAIR_PROGRAM,'
3655
3656 ||' REFRESH_NUMBER,'
3657 ||' LAST_UPDATE_DATE,'
3658 ||' LAST_UPDATED_BY,'
3659 ||' CREATION_DATE,'
3660 ||' CREATED_BY,'
3661 ||' PEGGING_DEMAND_WINDOW_DAYS,'
3662 ||' PEGGING_SUPPLY_WINDOW_DAYS )'
3663 ||'VALUES'
3664 ||'( -1,'
3665 ||' :ORGANIZATION_ID,'
3666 ||' :INVENTORY_ITEM_ID,'
3667 ||' :ITEM_NAME,'
3668 ||' :LOTS_EXPIRATION,'
3669 ||' :LOT_CONTROL_CODE,'
3670 ||' :SHRINKAGE_RATE,'
3671 ||' :FIXED_DAYS_SUPPLY,'
3672 ||' :FIXED_ORDER_QUANTITY,'
3673 ||' :FIXED_LOT_MULTIPLIER,'
3674 ||' :MINIMUM_ORDER_QUANTITY,'
3675 ||' :MAXIMUM_ORDER_QUANTITY,'
3676 ||' :ROUNDING_CONTROL_TYPE,'
3677 ||' :PLANNING_TIME_FENCE_CODE,'
3678 ||' :PLANNING_TIME_FENCE_DAYS,'
3679 ||' :DEMAND_TIME_FENCE_DAYS,'
3680 ||' :DESCRIPTION,'
3681 ||' :RELEASE_TIME_FENCE_CODE,'
3682 ||' :RELEASE_TIME_FENCE_DAYS,'
3683 ||' :IN_SOURCE_PLAN,'
3684 ||' :REVISION,'
3685 ||' :SR_CATEGORY_ID,'
3686 ||' :CATEGORY_NAME,'
3687 ||' :ABC_CLASS_ID,'
3688 ||' :ABC_CLASS_NAME,'
3689 ||' :MRP_PLANNING_CODE,'
3690 ||' :FIXED_LEAD_TIME,'
3691 ||' :VARIABLE_LEAD_TIME,'
3692 ||' :PREPROCESSING_LEAD_TIME,'
3693 ||' :POSTPROCESSING_LEAD_TIME,'
3694 ||' :FULL_LEAD_TIME,'
3695 ||' :CUMULATIVE_TOTAL_LEAD_TIME,'
3696 ||' :CUM_MANUFACTURING_LEAD_TIME,'
3697 ||' :UOM_CODE,'
3698 ||' :UNIT_WEIGHT,'
3699 ||' :UNIT_VOLUME,'
3700 ||' :WEIGHT_UOM,'
3701 ||' :VOLUME_UOM,'
3702 ||' :PRODUCT_FAMILY_ID,'
3703 ||' :ATP_RULE_ID,'
3704 ||' :ATP_COMPONENTS_FLAG,'
3705 ||' :BUILT_IN_WIP_FLAG,'
3706 ||' :PURCHASING_ENABLED_FLAG,'
3707 ||' :PLANNING_MAKE_BUY_CODE,'
3708 ||' :REPETITIVE_TYPE,'
3709 ||' :REPETITIVE_VARIANCE_DAYS,'
3710 ||' :STANDARD_COST,'
3711 ||' :CARRYING_COST,'
3712 ||' :ORDER_COST,'
3713 ||' :DMD_LATENESS_COST,'
3714 ||' :SS_PENALTY_COST,'
3715 ||' :SUPPLIER_CAP_OVERUTIL_COST,'
3716 ||' :LIST_PRICE,'
3717 ||' :AVERAGE_DISCOUNT,'
3718 ||' :ENGINEERING_ITEM_FLAG,'
3719 ||' :INVENTORY_ITEM_FLAG,'
3720 ||' :WIP_SUPPLY_TYPE,'
3721 ||' :MRP_SAFETY_STOCK_CODE,'
3722 ||' :MRP_SAFETY_STOCK_PERCENT,'
3723 ||' :SAFETY_STOCK_BUCKET_DAYS,'
3724 ||' :BUYER_NAME,'
3725 ||' :PLANNER_CODE,'
3726 ||' :PLANNING_EXCEPTION_SET,'
3727 ||' :EXCESS_QUANTITY,'
3728 ||' :SHORTAGE_TYPE,'
3729 ||' :EXCEPTION_SHORTAGE_DAYS,'
3730 ||' :EXCESS_TYPE,'
3731 ||' :EXCEPTION_EXCESS_DAYS,'
3732 ||' :EXCEPTION_OVERPROMISED_DAYS,'
3733 ||' :BOM_ITEM_TYPE,'
3734 ||' :ATO_FORECAST_CONTROL,'
3735 ||' :EFFECTIVITY_CONTROL,'
3736 ||' :ORGANIZATION_CODE,'
3737 ||' :ACCEPTABLE_RATE_INCREASE,'
3738 ||' :ACCEPTABLE_RATE_DECREASE,'
3739 ||' :INVENTORY_PLANNING_CODE,'
3740 ||' :ACCEPTABLE_EARLY_DELIVERY,'
3741 ||' :MRP_CALCULATE_ATP_FLAG,'
3742 ||' :END_ASSEMBLY_PEGGING_FLAG,'
3743 ||' :BASE_ITEM_ID,'
3744 ||' :PRIMARY_SUPPLIER_ID,'
3745 ||' :ATP_FLAG,'
3746 ||' :NEW_ATP_FLAG,'
3747 ||' :REVISION_QTY_CONTROL_CODE,'
3748 ||' :EXPENSE_ACCOUNT,'
3749 ||' :INVENTORY_ASSET_FLAG,'
3750 ||' :BUYER_ID,'
3751 ||' :SOURCE_ORG_ID,'
3752 ||' :MATERIAL_COST,'
3753 ||' :RESOURCE_COST,'
3754 ||' :REPLENISH_TO_ORDER_FLAG,'
3755 ||' :PICK_COMPONENTS_FLAG,'
3756 ||' :YIELD_CONV_FACTOR,'
3757 ||' :PIP_FLAG,'
3758 ||' :REDUCE_MPS,'
3759 ||' :CRITICAL_COMPONENT_FLAG,'
3760 ||' :VMI_MINIMUM_UNITS,'
3761 ||' :VMI_MINIMUM_DAYS,'
3762 ||' :VMI_MAXIMUM_UNITS,'
3763 ||' :VMI_MAXIMUM_DAYS,'
3764 ||' :VMI_FIXED_ORDER_QUANTITY,'
3765 ||' :SO_AUTHORIZATION_FLAG,'
3766 ||' :CONSIGNED_FLAG,'
3767 ||' :ASN_AUTOEXPIRE_FLAG,'
3768 ||' :VMI_FORECAST_TYPE,'
3769 ||' :FORECAST_HORIZON,'
3770 ||' :BUDGET_CONSTRAINED,'
3771 ||' :DAYS_TGT_INV_SUPPLY,'
3772 ||' :DAYS_TGT_INV_WINDOW,'
3773 ||' :DAYS_MAX_INV_SUPPLY,'
3774 ||' :DAYS_MAX_INV_WINDOW,'
3775 ||' :DRP_PLANNED,'
3776 ||' :CONTINOUS_TRANSFER,'
3777 ||' :CONVERGENCE,'
3778 ||' :DIVERGENCE,'
3779 ||' 1,'
3780 ||' :SOURCE_TYPE,'
3781 ||' :SUBSTITUTION_WINDOW,'
3782 ||' :CREATE_SUPPLY_FLAG,'
3783 ||' :SERIAL_NUMBER_CONTROL_CODE,'
3784 ||' :SR_INVENTORY_ITEM_ID,'
3785 ||' :ITEM_CREATION_DATE,'
3786 ||' :SR_INSTANCE_ID,'
3787 ||' :EAM_ITEM_TYPE,' /* ds change change */
3788 ||' :REPAIR_LEAD_TIME,' --# For Bug 5606037 SRP Changes
3789 ||' :PREPOSITION_POINT,'
3790 ||' :REPAIR_YIELD,'
3791 ||' :REPAIR_PROGRAM,'
3792 ||' :v_last_collection_id,'
3793 ||' :v_current_date,'
3794 ||' :v_current_user,'
3795 ||' :v_current_date,'
3796 ||' :v_current_user,'
3797 ||' :PEGGING_DEMAND_WINDOW_DAYS,'
3798 ||' :PEGGING_SUPPLY_WINDOW_DAYS )';
3799
3800 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) AND
3801 MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO THEN
3802
3803 IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
3804 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SYSTEM_ITEMS', MSC_CL_COLLECTION.v_instance_id, -1);
3805 ELSE
3806 v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
3807 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SYSTEM_ITEMS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
3808 END IF;
3809 END IF;
3810
3811 -- delete is not supported
3812 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
3813 BEGIN
3814 lv_sql_ins :=
3815 ' INSERT /*+ append */ '
3816 || ' INTO '||lv_tbl
3817 ||'( PLAN_ID,'
3818 ||' ORGANIZATION_ID,'
3819 ||' INVENTORY_ITEM_ID,'
3820 ||' ITEM_NAME,'
3821 ||' LOTS_EXPIRATION,'
3822 ||' LOT_CONTROL_CODE,'
3823 ||' SHRINKAGE_RATE,'
3824 ||' FIXED_DAYS_SUPPLY,'
3825 ||' FIXED_ORDER_QUANTITY,'
3826 ||' FIXED_LOT_MULTIPLIER,'
3827 ||' MINIMUM_ORDER_QUANTITY,'
3828 ||' MAXIMUM_ORDER_QUANTITY,'
3829 ||' ROUNDING_CONTROL_TYPE,'
3830 ||' PLANNING_TIME_FENCE_CODE,'
3831 ||' PLANNING_TIME_FENCE_DAYS,'
3832 ||' DEMAND_TIME_FENCE_DAYS,'
3833 ||' DESCRIPTION,'
3834 ||' RELEASE_TIME_FENCE_CODE,'
3835 ||' RELEASE_TIME_FENCE_DAYS,'
3836 ||' IN_SOURCE_PLAN,'
3837 ||' REVISION,'
3838 ||' SR_CATEGORY_ID,'
3839 ||' CATEGORY_NAME,'
3840 ||' ABC_CLASS,'
3841 ||' ABC_CLASS_NAME,'
3842 ||' MRP_PLANNING_CODE,'
3843 ||' FIXED_LEAD_TIME,'
3844 ||' VARIABLE_LEAD_TIME,'
3845 ||' PREPROCESSING_LEAD_TIME,'
3846 ||' POSTPROCESSING_LEAD_TIME,'
3847 ||' FULL_LEAD_TIME,'
3848 ||' CUMULATIVE_TOTAL_LEAD_TIME,'
3849 ||' CUM_MANUFACTURING_LEAD_TIME,'
3850 ||' UOM_CODE,'
3851 ||' UNIT_WEIGHT,'
3852 ||' UNIT_VOLUME,'
3853 ||' WEIGHT_UOM,'
3854 ||' VOLUME_UOM,'
3855 ||' PRODUCT_FAMILY_ID,'
3856 ||' ATP_RULE_ID,'
3857 ||' ATP_COMPONENTS_FLAG,'
3858 ||' BUILD_IN_WIP_FLAG,'
3859 ||' PURCHASING_ENABLED_FLAG,'
3860 ||' PLANNING_MAKE_BUY_CODE,'
3861 ||' REPETITIVE_TYPE,'
3862 ||' REPETITIVE_VARIANCE,'
3863 ||' STANDARD_COST,'
3864 ||' CARRYING_COST,'
3865 ||' ORDER_COST,'
3866 ||' DMD_LATENESS_COST,'
3867 ||' SS_PENALTY_COST,'
3868 ||' SUPPLIER_CAP_OVERUTIL_COST,'
3869 ||' LIST_PRICE,'
3870 ||' AVERAGE_DISCOUNT,'
3871 ||' ENGINEERING_ITEM_FLAG,'
3872 ||' INVENTORY_ITEM_FLAG,'
3873 ||' WIP_SUPPLY_TYPE,'
3874 ||' SAFETY_STOCK_CODE,'
3875 ||' SAFETY_STOCK_PERCENT,'
3876 ||' SAFETY_STOCK_BUCKET_DAYS,'
3877 ||' BUYER_NAME,'
3878 ||' PLANNER_CODE,'
3879 ||' PLANNING_EXCEPTION_SET,'
3880 ||' EXCESS_QUANTITY,'
3881 ||' SHORTAGE_TYPE,'
3882 ||' EXCEPTION_SHORTAGE_DAYS,'
3883 ||' EXCESS_TYPE,'
3884 ||' EXCEPTION_EXCESS_DAYS,'
3885 ||' EXCEPTION_OVERPROMISED_DAYS,'
3886 ||' BOM_ITEM_TYPE,'
3887 ||' ATO_FORECAST_CONTROL,'
3888 ||' EFFECTIVITY_CONTROL,'
3889 ||' ORGANIZATION_CODE,'
3890 ||' ACCEPTABLE_RATE_INCREASE,'
3891 ||' ACCEPTABLE_RATE_DECREASE,'
3892 ||' INVENTORY_PLANNING_CODE,'
3893 ||' ACCEPTABLE_EARLY_DELIVERY,'
3894 ||' CALCULATE_ATP,'
3895 ||' END_ASSEMBLY_PEGGING_FLAG,'
3896 ||' BASE_ITEM_ID,'
3897 ||' PRIMARY_SUPPLIER_ID,'
3898 ||' ATP_FLAG,'
3899 ||' NEW_ATP_FLAG,'
3900 ||' REVISION_QTY_CONTROL_CODE,'
3901 ||' EXPENSE_ACCOUNT,'
3902 ||' INVENTORY_ASSET_FLAG,'
3903 ||' BUYER_ID,'
3904 ||' SOURCE_ORG_ID,'
3905 ||' MATERIAL_COST,'
3906 ||' RESOURCE_COST,'
3907 ||' REPLENISH_TO_ORDER_FLAG,'
3908 ||' PICK_COMPONENTS_FLAG,'
3909 ||' YIELD_CONV_FACTOR,'
3910 ||' PIP_FLAG,'
3911 ||' REDUCE_MPS,'
3912 ||' CRITICAL_COMPONENT_FLAG,'
3913 ||' VMI_MINIMUM_UNITS,'
3914 ||' VMI_MINIMUM_DAYS,'
3915 ||' VMI_MAXIMUM_UNITS,'
3916 ||' VMI_MAXIMUM_DAYS,'
3917 ||' VMI_FIXED_ORDER_QUANTITY,'
3918 ||' SO_AUTHORIZATION_FLAG,'
3919 ||' CONSIGNED_FLAG,'
3920 ||' ASN_AUTOEXPIRE_FLAG,'
3921 ||' VMI_FORECAST_TYPE,'
3922 ||' FORECAST_HORIZON,'
3923 ||' BUDGET_CONSTRAINED,'
3924 ||' DAYS_TGT_INV_SUPPLY,'
3925 ||' DAYS_TGT_INV_WINDOW,'
3926 ||' DAYS_MAX_INV_SUPPLY,'
3927 ||' DAYS_MAX_INV_WINDOW,'
3928 ||' DRP_PLANNED,'
3929 ||' CONTINOUS_TRANSFER,'
3930 ||' CONVERGENCE,'
3931 ||' DIVERGENCE,'
3932 ||' VMI_REFRESH_FLAG,'
3933 ||' SOURCE_TYPE,'
3934 ||' SUBSTITUTION_WINDOW,'
3935 ||' CREATE_SUPPLY_FLAG,'
3936 ||' SERIAL_NUMBER_CONTROL_CODE,'
3937 ||' SR_INVENTORY_ITEM_ID,'
3938 ||' ITEM_CREATION_DATE,'
3939 ||' SR_INSTANCE_ID,'
3940 ||' EAM_ITEM_TYPE,' /* ds change change */
3941 ||' REPAIR_LEAD_TIME,' --# For Bug 5606037 SRP Changes
3942 ||' PREPOSITION_POINT,'
3943 ||' REPAIR_YIELD,'
3944 ||' REPAIR_PROGRAM,'
3945 ||' REFRESH_NUMBER,'
3946 ||' LAST_UPDATE_DATE,'
3947 ||' LAST_UPDATED_BY,'
3948 ||' CREATION_DATE,'
3949 ||' CREATED_BY,'
3950 ||' PEGGING_DEMAND_WINDOW_DAYS,'
3951 ||' PEGGING_SUPPLY_WINDOW_DAYS )'
3952 ||' SELECT '
3953 ||' -1,'
3954 ||' msi.ORGANIZATION_ID,'
3955 ||' t1.INVENTORY_ITEM_ID,'
3956 ||' msi.ITEM_NAME,'
3957 ||' msi.LOTS_EXPIRATION,'
3958 ||' msi.LOT_CONTROL_CODE,'
3959 ||' msi.SHRINKAGE_RATE,'
3960 ||' msi.FIXED_DAYS_SUPPLY,'
3961 ||' msi.FIXED_ORDER_QUANTITY,'
3962 ||' msi.FIXED_LOT_MULTIPLIER,'
3963 ||' msi.MINIMUM_ORDER_QUANTITY,'
3964 ||' msi.MAXIMUM_ORDER_QUANTITY,'
3965 ||' msi.ROUNDING_CONTROL_TYPE,'
3966 ||' msi.PLANNING_TIME_FENCE_CODE,'
3967 ||' msi.PLANNING_TIME_FENCE_DAYS,'
3968 ||' msi.DEMAND_TIME_FENCE_DAYS,'
3969 ||' replace(substrb(msi.DESCRIPTION,1,240),:v_chr9,'' '') DESCRIPTION,'
3970 ||' msi.RELEASE_TIME_FENCE_CODE,'
3971 ||' msi.RELEASE_TIME_FENCE_DAYS,'
3972 ||' msi.IN_SOURCE_PLAN,'
3973 ||' msi.REVISION,'
3974 ||' msi.SR_CATEGORY_ID,'
3975 ||' msi.CATEGORY_NAME,'
3976 ||' msi.ABC_CLASS_ID,'
3977 ||' msi.ABC_CLASS_NAME,'
3978 ||' msi.MRP_PLANNING_CODE,'
3979 ||' msi.FIXED_LEAD_TIME,'
3980 ||' msi.VARIABLE_LEAD_TIME,'
3981 ||' msi.PREPROCESSING_LEAD_TIME,'
3982 ||' msi.POSTPROCESSING_LEAD_TIME,'
3983 ||' msi.FULL_LEAD_TIME,'
3984 ||' msi.CUMULATIVE_TOTAL_LEAD_TIME,'
3985 ||' msi.CUM_MANUFACTURING_LEAD_TIME,'
3986 ||' msi.UOM_CODE,'
3987 ||' msi.UNIT_WEIGHT,'
3988 ||' msi.UNIT_VOLUME,'
3989 ||' msi.WEIGHT_UOM,'
3990 ||' msi.VOLUME_UOM,'
3991 ||' t3.Inventory_Item_ID,'
3992 ||' msi.ATP_RULE_ID,'
3993 ||' msi.ATP_COMPONENTS_FLAG,'
3994 ||' msi.BUILT_IN_WIP_FLAG,'
3995 ||' msi.PURCHASING_ENABLED_FLAG,'
3996 ||' msi.PLANNING_MAKE_BUY_CODE,'
3997 ||' msi.REPETITIVE_TYPE,'
3998 ||' msi.REPETITIVE_VARIANCE_DAYS,'
3999 ||' msi.STANDARD_COST,'
4000 ||' msi.CARRYING_COST,'
4001 ||' msi.ORDER_COST,'
4002 ||' nvl(msi.DMD_LATENESS_COST, mtp.DEMAND_LATENESS_COST),'
4003 ||' msi.SS_PENALTY_COST,'
4004 ||' msi.SUPPLIER_CAP_OVERUTIL_COST,'
4005 ||' nvl(msi.LIST_PRICE,msi.STANDARD_COST),'
4006 ||' msi.AVERAGE_DISCOUNT,'
4007 ||' msi.ENGINEERING_ITEM_FLAG,'
4008 ||' msi.INVENTORY_ITEM_FLAG,'
4009 ||' msi.WIP_SUPPLY_TYPE,'
4010 ||' msi.MRP_SAFETY_STOCK_CODE,'
4011 ||' msi.MRP_SAFETY_STOCK_PERCENT,'
4012 ||' msi.SAFETY_STOCK_BUCKET_DAYS,'
4013 ||' msi.BUYER_NAME,'
4014 ||' msi.PLANNER_CODE,'
4015 ||' msi.PLANNING_EXCEPTION_SET,'
4016 ||' msi.EXCESS_QUANTITY,'
4017 ||' msi.SHORTAGE_TYPE,'
4018 ||' msi.EXCEPTION_SHORTAGE_DAYS,'
4019 ||' msi.EXCESS_TYPE,'
4020 ||' msi.EXCEPTION_EXCESS_DAYS,'
4021 ||' msi.EXCEPTION_OVERPROMISED_DAYS,'
4022 ||' msi.BOM_ITEM_TYPE,'
4023 ||' msi.ATO_FORECAST_CONTROL,'
4024 ||' msi.EFFECTIVITY_CONTROL,'
4025 ||' msi.ORGANIZATION_CODE,'
4026 ||' msi.ACCEPTABLE_RATE_INCREASE,'
4027 ||' msi.ACCEPTABLE_RATE_DECREASE,'
4028 ||' msi.INVENTORY_PLANNING_CODE,'
4029 ||' msi.ACCEPTABLE_EARLY_DELIVERY,'
4030 ||' msi.MRP_CALCULATE_ATP_FLAG,'
4031 ||' msi.END_ASSEMBLY_PEGGING_FLAG,'
4032 ||' t2.INVENTORY_ITEM_ID, '
4033 ||' msi.PRIMARY_SUPPLIER_ID,'
4034 ||' decode(msi.ATP_FLAG,''C'', ''Y'', msi.ATP_FLAG) ,'
4035 ||' decode(msi.ATP_FLAG,''C'', ''Y'', ''N'') ,'
4036 ||' msi.REVISION_QTY_CONTROL_CODE,'
4037 ||' msi.EXPENSE_ACCOUNT,'
4038 ||' msi.INVENTORY_ASSET_FLAG,'
4039 ||' msi.BUYER_ID,'
4040 ||' msi.SOURCE_ORG_ID,'
4041 ||' msi.MATERIAL_COST,'
4042 ||' msi.RESOURCE_COST,'
4043 ||' msi.replenish_to_order_flag,'
4044 ||' msi.pick_components_flag,'
4045 ||' msi.yield_conv_factor,'
4046 ||' msi.pip_flag,'
4047 ||' msi.REDUCE_MPS,'
4048 ||' msi.CRITICAL_COMPONENT_FLAG,'
4049 ||' msi.VMI_MINIMUM_UNITS,'
4050 ||' msi.VMI_MINIMUM_DAYS,'
4051 ||' msi.VMI_MAXIMUM_UNITS,'
4052 ||' msi.VMI_MAXIMUM_DAYS,'
4053 ||' msi.VMI_FIXED_ORDER_QUANTITY,'
4054 ||' msi.SO_AUTHORIZATION_FLAG,'
4055 ||' msi.CONSIGNED_FLAG,'
4056 ||' msi.ASN_AUTOEXPIRE_FLAG,'
4057 ||' msi.VMI_FORECAST_TYPE,'
4058 ||' msi.FORECAST_HORIZON,'
4059 ||' msi.BUDGET_CONSTRAINED,'
4060 ||' msi.DAYS_TGT_INV_SUPPLY,'
4061 ||' msi.DAYS_TGT_INV_WINDOW,'
4062 ||' msi.DAYS_MAX_INV_SUPPLY,'
4063 ||' msi.DAYS_MAX_INV_WINDOW,'
4064 ||' msi.DRP_PLANNED,'
4065 ||' msi.CONTINOUS_TRANSFER,'
4066 ||' msi.CONVERGENCE,'
4067 ||' msi.DIVERGENCE,'
4068 ||' 1,'
4069 ||' msi.SOURCE_TYPE,'
4070 ||' msi.SUBSTITUTION_WINDOW,'
4071 ||' msi.CREATE_SUPPLY_FLAG,'
4072 ||' msi.serial_number_control_code,'
4073 ||' msi.SR_INVENTORY_ITEM_ID,'
4074 ||' msi.ITEM_CREATION_DATE,'
4075 ||' msi.SR_INSTANCE_ID,'
4076 ||' msi.EAM_ITEM_TYPE,' /* ds change change */
4077 ||' msi.REPAIR_LEAD_TIME , '--# For Bug 5606037 SRP Changes
4078 ||' msi.PREPOSITION_POINT ,'
4079 ||' msi.REPAIR_YIELD ,'
4080 ||' msi.REPAIR_PROGRAM ,'
4081 ||' :v_last_collection_id, '
4082 ||' :v_current_date , '
4083 ||' :v_current_user , '
4084 ||' :v_current_date , '
4085 ||' :v_current_user , '
4086 ||' msi.PEGGING_DEMAND_WINDOW_DAYS,'
4087 ||' msi.PEGGING_SUPPLY_WINDOW_DAYS '
4088 ||' FROM MSC_ITEM_ID_LID t3, '
4089 ||' MSC_ITEM_ID_LID t2, '
4090 ||' MSC_ITEM_ID_LID t1, '
4091 ||' MSC_TRADING_PARTNERS mtp, '
4092 ||' MSC_ST_SYSTEM_ITEMS msi '
4093 ||' WHERE t1.SR_INVENTORY_ITEM_ID = msi.sr_inventory_item_id '
4094 ||' AND t1.sr_instance_id = msi.sr_instance_id '
4095 ||' AND t2.SR_INVENTORY_ITEM_ID(+) = msi.base_item_id '
4096 ||' AND t2.sr_instance_id(+) = msi.sr_instance_id '
4097 ||' AND t3.SR_INVENTORY_ITEM_ID(+) = msi.product_family_id '
4098 ||' AND t3.sr_instance_id(+) = msi.sr_instance_id '
4099 ||' AND mtp.sr_tp_id(+) = msi.organization_id '
4100 ||' AND mtp.partner_type(+) = 3 '
4101 ||' AND mtp.sr_instance_id(+) = msi.sr_instance_id '
4102 ||' AND msi.SR_INSTANCE_ID = '||MSC_CL_COLLECTION.v_instance_id;
4103
4104 EXECUTE IMMEDIATE lv_sql_ins
4105 USING v_chr9, MSC_CL_COLLECTION.v_last_collection_id,MSC_CL_COLLECTION.v_current_date,MSC_CL_COLLECTION.v_current_user,MSC_CL_COLLECTION.v_current_date,MSC_CL_COLLECTION.v_current_user;
4106
4107 commit;
4108 EXCEPTION
4109 WHEN OTHERS THEN
4110
4111 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4112 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4113 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ITEM');
4114 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SYSTEM_ITEMS');
4115 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4116
4117 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4118 RAISE;
4119
4120 END;
4121 END IF;
4122
4123 IF (MSC_CL_COLLECTION.v_is_incremental_refresh OR
4124 lv_MSC_CONFIGURATION = MSC_UTIL.G_CONF_APS_SCE OR
4125 lv_MSC_CONFIGURATION = MSC_UTIL.G_CONF_SCE) THEN
4126 c_count :=0;
4127
4128 OPEN c1;
4129 IF (c1%ISOPEN) THEN
4130 LOOP
4131 --
4132 -- Retrieve the next set of rows if we are currently not in the
4133 -- middle of processing a fetched set or rows.
4134 --
4135 IF (lb_FetchComplete) THEN
4136 EXIT;
4137 END IF;
4138 -- Fetch the next set of rows
4139 FETCH c1 BULK COLLECT INTO
4140 lb_ORGANIZATION_ID,
4141 lb_INVENTORY_ITEM_ID,
4142 lb_ITEM_NAME,
4143 lb_LOTS_EXPIRATION,
4144 lb_LOT_CONTROL_CODE,
4145 lb_SHRINKAGE_RATE,
4146 lb_FIXED_DAYS_SUPPLY,
4147 lb_FIXED_ORDER_QUANTITY,
4148 lb_FIXED_LOT_MULTIPLIER,
4149 lb_MINIMUM_ORDER_QUANTITY,
4150 lb_MAXIMUM_ORDER_QUANTITY,
4151 lb_ROUNDING_CONTROL_TYPE,
4152 lb_PLANNING_TIME_FENCE_CODE,
4153 lb_PLANNING_TIME_FENCE_DAYS,
4154 lb_DEMAND_TIME_FENCE_DAYS,
4155 lb_DESCRIPTION,
4156 lb_RELEASE_TIME_FENCE_CODE,
4157 lb_RELEASE_TIME_FENCE_DAYS,
4158 lb_IN_SOURCE_PLAN,
4159 lb_REVISION,
4160 lb_SR_CATEGORY_ID,
4161 lb_CATEGORY_NAME,
4162 lb_ABC_CLASS_ID,
4163 lb_ABC_CLASS_NAME,
4164 lb_MRP_PLANNING_CODE,
4165 lb_FIXED_LEAD_TIME,
4166 lb_VARIABLE_LEAD_TIME,
4167 lb_PREPROCESSING_LEAD_TIME,
4168 lb_POSTPROCESSING_LEAD_TIME,
4169 lb_FULL_LEAD_TIME,
4170 lb_CUMULATIVE_TOTAL_LEAD_TIME,
4171 lb_CUM_MANUFACTURING_LEAD_TIME,
4172 lb_UOM_CODE,
4173 lb_UNIT_WEIGHT,
4174 lb_UNIT_VOLUME,
4175 lb_WEIGHT_UOM,
4176 lb_VOLUME_UOM,
4177 lb_PRODUCT_FAMILY_ID,
4178 lb_ATP_RULE_ID,
4179 lb_ATP_COMPONENTS_FLAG,
4180 lb_BUILT_IN_WIP_FLAG,
4181 lb_PURCHASING_ENABLED_FLAG,
4182 lb_PLANNING_MAKE_BUY_CODE,
4183 lb_REPETITIVE_TYPE,
4184 lb_REPETITIVE_VARIANCE_DAYS,
4185 lb_STANDARD_COST,
4186 lb_CARRYING_COST,
4187 lb_ORDER_COST,
4188 lb_DMD_LATENESS_COST,
4189 lb_SS_PENALTY_COST,
4190 lb_SUPPLIER_CAP_OVERUTIL_COST,
4191 lb_LIST_PRICE,
4192 lb_AVERAGE_DISCOUNT,
4193 lb_ENGINEERING_ITEM_FLAG,
4194 lb_INVENTORY_ITEM_FLAG,
4195 lb_WIP_SUPPLY_TYPE,
4196 lb_MRP_SAFETY_STOCK_CODE,
4197 lb_MRP_SAFETY_STOCK_PERCENT,
4198 lb_SAFETY_STOCK_BUCKET_DAYS,
4199 lb_BUYER_NAME,
4200 lb_PLANNER_CODE,
4201 lb_PLANNING_EXCEPTION_SET,
4202 lb_EXCESS_QUANTITY,
4203 lb_SHORTAGE_TYPE,
4204 lb_EXCEPTION_SHORTAGE_DAYS,
4205 lb_EXCESS_TYPE,
4206 lb_EXCEPTION_EXCESS_DAYS,
4207 lb_EXCEPTION_OVERPROMISED_DAYS,
4208 lb_BOM_ITEM_TYPE,
4209 lb_ATO_FORECAST_CONTROL,
4210 lb_EFFECTIVITY_CONTROL,
4211 lb_ORGANIZATION_CODE,
4212 lb_ACCEPTABLE_RATE_INCREASE,
4213 lb_ACCEPTABLE_RATE_DECREASE,
4214 lb_INVENTORY_PLANNING_CODE,
4215 lb_ACCEPTABLE_EARLY_DELIVERY,
4216 lb_MRP_CALCULATE_ATP_FLAG,
4217 lb_END_ASSEMBLY_PEGGING_FLAG,
4218 lb_BASE_ITEM_ID,
4219 lb_PRIMARY_SUPPLIER_ID,
4220 lb_ATP_FLAG ,
4221 lb_NEW_ATP_FLAG ,
4222 lb_REVISION_QTY_CONTROL_CODE,
4223 lb_EXPENSE_ACCOUNT,
4224 lb_INVENTORY_ASSET_FLAG,
4225 lb_BUYER_ID,
4226 lb_SOURCE_ORG_ID,
4227 lb_MATERIAL_COST,
4228 lb_RESOURCE_COST,
4229 lb_SR_INVENTORY_ITEM_ID,
4230 lb_DELETED_FLAG,
4231 lb_SR_INSTANCE_ID,
4232 lb_REPLENISH_TO_ORDER_FLAG,
4233 lb_PICK_COMPONENTS_FLAG,
4234 lb_PIP_FLAG,
4235 lb_REDUCE_MPS,
4236 lb_CRITICAL_COMPONENT_FLAG,
4237 lb_VMI_MINIMUM_UNITS,
4238 lb_VMI_MINIMUM_DAYS,
4239 lb_VMI_MAXIMUM_UNITS,
4240 lb_VMI_MAXIMUM_DAYS,
4241 lb_VMI_FIXED_ORDER_QUANTITY,
4242 lb_SO_AUTHORIZATION_FLAG,
4243 lb_CONSIGNED_FLAG,
4244 lb_ASN_AUTOEXPIRE_FLAG,
4245 lb_VMI_FORECAST_TYPE,
4246 lb_FORECAST_HORIZON,
4247 lb_BUDGET_CONSTRAINED,
4248 lb_DAYS_TGT_INV_SUPPLY,
4249 lb_DAYS_TGT_INV_WINDOW,
4250 lb_DAYS_MAX_INV_SUPPLY,
4251 lb_DAYS_MAX_INV_WINDOW,
4252 lb_DRP_PLANNED,
4253 lb_CONTINOUS_TRANSFER,
4254 lb_CONVERGENCE,
4255 lb_DIVERGENCE,
4256 lb_SOURCE_TYPE,
4257 lb_SUBSTITUTION_WINDOW,
4258 lb_CREATE_SUPPLY_FLAG,
4259 lb_YIELD_CONV_FACTOR,
4260 lb_SERIAL_NUMBER_CONTROL_CODE ,
4261 lb_ITEM_CREATION_DATE,
4262 lb_EAM_ITEM_TYPE, /* ds change change */
4263 lb_PEGGING_DEMAND_WINDOW_DAYS,
4264 lb_PEGGING_SUPPLY_WINDOW_DAYS,
4265 lb_REPAIR_LEAD_TIME, --# For Bug 5606037 SRP Changes
4266 lb_PRE_POSITIONING_POINT,
4267 lb_REPAIR_YIELD,
4268 lb_REPAIR_PROGRAM
4269 LIMIT ln_rows_to_fetch;
4270
4271 -- Since we are only fetching records if either (1) this is the first
4272 -- fetch or (2) the previous fetch did not retrieve all of the
4273 -- records, then at least one row should always be fetched. But
4274 -- checking just to make sure.
4275 EXIT WHEN lb_INVENTORY_ITEM_ID.count = 0;
4276
4277 -- Check if all of the rows have been fetched. If so, indicate that
4278 -- the fetch is complete so that another fetch is not made.
4279 -- Additional check is introduced for the following reasons
4280 -- In 9i, the table of records gets modified but in 8.1.6 the table of records is
4281 -- unchanged after the fetch(bug#2995144)
4282 IF (c1%NOTFOUND) THEN
4283 lb_FetchComplete := TRUE;
4284 END IF;
4285
4286 FOR j IN 1..lb_INVENTORY_ITEM_ID.COUNT LOOP
4287
4288
4289
4290 BEGIN
4291
4292 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
4293
4294 UPDATE MSC_SYSTEM_ITEMS
4295 SET
4296 ITEM_NAME= lb_ITEM_NAME(j),
4297 LOTS_EXPIRATION= lb_LOTS_EXPIRATION(j),
4298 LOT_CONTROL_CODE= lb_LOT_CONTROL_CODE(j),
4299 SHRINKAGE_RATE= lb_SHRINKAGE_RATE(j),
4300 FIXED_DAYS_SUPPLY= lb_FIXED_DAYS_SUPPLY(j),
4301 FIXED_ORDER_QUANTITY= lb_FIXED_ORDER_QUANTITY(j),
4302 FIXED_LOT_MULTIPLIER= lb_FIXED_LOT_MULTIPLIER(j),
4303 MINIMUM_ORDER_QUANTITY= lb_MINIMUM_ORDER_QUANTITY(j),
4304 MAXIMUM_ORDER_QUANTITY= lb_MAXIMUM_ORDER_QUANTITY(j),
4305 ROUNDING_CONTROL_TYPE= lb_ROUNDING_CONTROL_TYPE(j),
4306 PLANNING_TIME_FENCE_CODE= lb_PLANNING_TIME_FENCE_CODE(j),
4307 PLANNING_TIME_FENCE_DAYS= lb_PLANNING_TIME_FENCE_DAYS(j),
4308 DEMAND_TIME_FENCE_DAYS= lb_DEMAND_TIME_FENCE_DAYS(j),
4309 DESCRIPTION= lb_DESCRIPTION(j),
4310 RELEASE_TIME_FENCE_CODE= lb_RELEASE_TIME_FENCE_CODE(j),
4311 RELEASE_TIME_FENCE_DAYS= lb_RELEASE_TIME_FENCE_DAYS(j),
4312 IN_SOURCE_PLAN= lb_IN_SOURCE_PLAN(j),
4313 REVISION= lb_REVISION(j),
4314 SR_CATEGORY_ID= lb_SR_CATEGORY_ID(j),
4315 CATEGORY_NAME= lb_CATEGORY_NAME(j),
4316 ABC_CLASS= lb_ABC_CLASS_ID(j),
4317 ABC_CLASS_NAME= lb_ABC_CLASS_NAME(j),
4318 MRP_PLANNING_CODE= lb_MRP_PLANNING_CODE(j),
4319 FIXED_LEAD_TIME= lb_FIXED_LEAD_TIME(j),
4320 VARIABLE_LEAD_TIME= lb_VARIABLE_LEAD_TIME(j),
4321 PREPROCESSING_LEAD_TIME= lb_PREPROCESSING_LEAD_TIME(j),
4322 POSTPROCESSING_LEAD_TIME= lb_POSTPROCESSING_LEAD_TIME(j),
4323 FULL_LEAD_TIME= lb_FULL_LEAD_TIME(j),
4324 CUMULATIVE_TOTAL_LEAD_TIME= lb_CUMULATIVE_TOTAL_LEAD_TIME(j),
4325 CUM_MANUFACTURING_LEAD_TIME= lb_CUM_MANUFACTURING_LEAD_TIME(j),
4326 UOM_CODE= lb_UOM_CODE(j),
4327 UNIT_WEIGHT= lb_UNIT_WEIGHT(j),
4328 UNIT_VOLUME= lb_UNIT_VOLUME(j),
4329 WEIGHT_UOM= lb_WEIGHT_UOM(j),
4330 VOLUME_UOM= lb_VOLUME_UOM(j),
4331 PRODUCT_FAMILY_ID= lb_PRODUCT_FAMILY_ID(j),
4332 ATP_RULE_ID= lb_ATP_RULE_ID(j),
4333 ATP_COMPONENTS_FLAG= lb_ATP_COMPONENTS_FLAG(j),
4334 BUILD_IN_WIP_FLAG= lb_BUILT_IN_WIP_FLAG(j),
4335 PURCHASING_ENABLED_FLAG= lb_PURCHASING_ENABLED_FLAG(j),
4336 PLANNING_MAKE_BUY_CODE= lb_PLANNING_MAKE_BUY_CODE(j),
4337 REPETITIVE_TYPE= lb_REPETITIVE_TYPE(j),
4338 REPETITIVE_VARIANCE= lb_REPETITIVE_VARIANCE_DAYS(j),
4339 STANDARD_COST= lb_STANDARD_COST(j),
4340 CARRYING_COST= lb_CARRYING_COST(j),
4341 ORDER_COST= lb_ORDER_COST(j),
4342 DMD_LATENESS_COST= lb_DMD_LATENESS_COST(j),
4343 SS_PENALTY_COST= lb_SS_PENALTY_COST(j),
4344 SUPPLIER_CAP_OVERUTIL_COST= lb_SUPPLIER_CAP_OVERUTIL_COST(j),
4345 LIST_PRICE= lb_LIST_PRICE(j),
4346 AVERAGE_DISCOUNT= lb_AVERAGE_DISCOUNT(j),
4347 ENGINEERING_ITEM_FLAG= lb_ENGINEERING_ITEM_FLAG(j),
4348 INVENTORY_ITEM_FLAG= lb_INVENTORY_ITEM_FLAG(j),
4349 WIP_SUPPLY_TYPE= lb_WIP_SUPPLY_TYPE(j),
4350 SAFETY_STOCK_CODE= lb_MRP_SAFETY_STOCK_CODE(j),
4351 SAFETY_STOCK_PERCENT= lb_MRP_SAFETY_STOCK_PERCENT(j),
4352 SAFETY_STOCK_BUCKET_DAYS= lb_SAFETY_STOCK_BUCKET_DAYS(j),
4353 BUYER_NAME= lb_BUYER_NAME(j),
4354 PLANNER_CODE= lb_PLANNER_CODE(j),
4355 PLANNING_EXCEPTION_SET= lb_PLANNING_EXCEPTION_SET(j),
4356 EXCESS_QUANTITY= lb_EXCESS_QUANTITY(j),
4357 SHORTAGE_TYPE= lb_SHORTAGE_TYPE(j),
4358 EXCEPTION_SHORTAGE_DAYS= lb_EXCEPTION_SHORTAGE_DAYS(j),
4359 EXCESS_TYPE= lb_EXCESS_TYPE(j),
4360 EXCEPTION_EXCESS_DAYS= lb_EXCEPTION_EXCESS_DAYS(j),
4361 EXCEPTION_OVERPROMISED_DAYS= lb_EXCEPTION_OVERPROMISED_DAYS(j),
4362 BOM_ITEM_TYPE= lb_BOM_ITEM_TYPE(j),
4363 ATO_FORECAST_CONTROL= lb_ATO_FORECAST_CONTROL(j),
4364 EFFECTIVITY_CONTROL= lb_EFFECTIVITY_CONTROL(j),
4365 ORGANIZATION_CODE= lb_ORGANIZATION_CODE(j),
4366 ACCEPTABLE_RATE_INCREASE= lb_ACCEPTABLE_RATE_INCREASE(j),
4367 ACCEPTABLE_RATE_DECREASE= lb_ACCEPTABLE_RATE_DECREASE(j),
4368 INVENTORY_PLANNING_CODE= lb_INVENTORY_PLANNING_CODE(j),
4369 ACCEPTABLE_EARLY_DELIVERY= lb_ACCEPTABLE_EARLY_DELIVERY(j),
4370 CALCULATE_ATP= lb_MRP_CALCULATE_ATP_FLAG(j),
4371 END_ASSEMBLY_PEGGING_FLAG= lb_END_ASSEMBLY_PEGGING_FLAG(j),
4372 BASE_ITEM_ID= lb_BASE_ITEM_ID(j),
4373 PRIMARY_SUPPLIER_ID= lb_PRIMARY_SUPPLIER_ID(j),
4374 ATP_FLAG= lb_ATP_FLAG(j),
4375 NEW_ATP_FLAG= lb_NEW_ATP_FLAG(j),
4376 REVISION_QTY_CONTROL_CODE= lb_REVISION_QTY_CONTROL_CODE(j),
4377 EXPENSE_ACCOUNT= lb_EXPENSE_ACCOUNT(j),
4378 INVENTORY_ASSET_FLAG= lb_INVENTORY_ASSET_FLAG(j),
4379 BUYER_ID= lb_BUYER_ID(j),
4380 SOURCE_ORG_ID= lb_SOURCE_ORG_ID(j),
4381 MATERIAL_COST= lb_MATERIAL_COST(j),
4382 RESOURCE_COST= lb_RESOURCE_COST(j),
4383 REPLENISH_TO_ORDER_FLAG = lb_REPLENISH_TO_ORDER_FLAG (j),
4384 PICK_COMPONENTS_FLAG = lb_PICK_COMPONENTS_FLAG(j),
4385 YIELD_CONV_FACTOR = lb_YIELD_CONV_FACTOR(j),
4386 PIP_FLAG = lb_PIP_FLAG(j),
4387 REDUCE_MPS = lb_REDUCE_MPS(j),
4388 CRITICAL_COMPONENT_FLAG = lb_CRITICAL_COMPONENT_FLAG(j),
4389 VMI_MINIMUM_UNITS = lb_VMI_MINIMUM_UNITS(j),
4390 VMI_MINIMUM_DAYS = lb_VMI_MINIMUM_DAYS(j),
4391 VMI_MAXIMUM_UNITS = lb_VMI_MAXIMUM_UNITS(j),
4392 VMI_MAXIMUM_DAYS = lb_VMI_MAXIMUM_DAYS(j),
4393 VMI_FIXED_ORDER_QUANTITY = lb_VMI_FIXED_ORDER_QUANTITY(j),
4394 SO_AUTHORIZATION_FLAG = lb_SO_AUTHORIZATION_FLAG(j),
4395 CONSIGNED_FLAG = lb_CONSIGNED_FLAG(j),
4396 ASN_AUTOEXPIRE_FLAG = lb_ASN_AUTOEXPIRE_FLAG(j),
4397 VMI_FORECAST_TYPE = lb_VMI_FORECAST_TYPE(j),
4398 FORECAST_HORIZON = lb_FORECAST_HORIZON(j),
4399 BUDGET_CONSTRAINED = lb_BUDGET_CONSTRAINED(j),
4400 DAYS_TGT_INV_SUPPLY = lb_DAYS_TGT_INV_SUPPLY(j),
4401 DAYS_TGT_INV_WINDOW = lb_DAYS_TGT_INV_WINDOW(j),
4402 DAYS_MAX_INV_SUPPLY = lb_DAYS_MAX_INV_SUPPLY(j),
4403 DAYS_MAX_INV_WINDOW = lb_DAYS_MAX_INV_WINDOW(j),
4404 DRP_PLANNED = lb_DRP_PLANNED(j),
4405 CONTINOUS_TRANSFER = lb_CONTINOUS_TRANSFER(j),
4406 CONVERGENCE = lb_CONVERGENCE(j),
4407 DIVERGENCE = lb_DIVERGENCE(j),
4408 VMI_REFRESH_FLAG = 1,
4409 SOURCE_TYPE = lb_SOURCE_TYPE(j),
4410 SUBSTITUTION_WINDOW = lb_SUBSTITUTION_WINDOW(j),
4411 CREATE_SUPPLY_FLAG = lb_CREATE_SUPPLY_FLAG(j),
4412 SERIAL_NUMBER_CONTROL_CODE = lb_SERIAL_NUMBER_CONTROL_CODE(j),
4413 SR_INVENTORY_ITEM_ID= lb_SR_INVENTORY_ITEM_ID(j),
4414 EAM_ITEM_TYPE = lb_EAM_ITEM_TYPE(j), /* ds change change */
4415 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
4416 /* ATP SUMMARY CHANGES Added the Refresh_number */
4417 REFRESH_NUMBER = MSC_CL_COLLECTION.v_last_collection_id,
4418 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
4419 REPAIR_LEAD_TIME= lb_REPAIR_LEAD_TIME(j), --# For Bug 5606037 SRP Changes
4420 PREPOSITION_POINT = lb_PRE_POSITIONING_POINT(j),
4421 REPAIR_PROGRAM = lb_REPAIR_PROGRAM(j),
4422 REPAIR_YIELD = lb_REPAIR_YIELD(j),
4423 PEGGING_DEMAND_WINDOW_DAYS = lb_PEGGING_DEMAND_WINDOW_DAYS(j),
4424 PEGGING_SUPPLY_WINDOW_DAYS = lb_PEGGING_SUPPLY_WINDOW_DAYS(j)
4425 WHERE PLAN_ID= -1
4426 AND ORGANIZATION_ID= lb_ORGANIZATION_ID(j)
4427 AND INVENTORY_ITEM_ID= lb_INVENTORY_ITEM_ID(j)
4428 AND SR_INSTANCE_ID= lb_SR_INSTANCE_ID(j);
4429
4430 --END IF; -- refresh mode
4431
4432 IF SQL%NOTFOUND THEN
4433
4434 EXECUTE IMMEDIATE lv_sql_stmt
4435 USING lb_ORGANIZATION_ID(j),
4436 lb_INVENTORY_ITEM_ID(j),
4437 lb_ITEM_NAME(j),
4438 lb_LOTS_EXPIRATION(j),
4439 lb_LOT_CONTROL_CODE(j),
4440 lb_SHRINKAGE_RATE(j),
4441 lb_FIXED_DAYS_SUPPLY(j),
4442 lb_FIXED_ORDER_QUANTITY(j),
4443 lb_FIXED_LOT_MULTIPLIER(j),
4444 lb_MINIMUM_ORDER_QUANTITY(j),
4445 lb_MAXIMUM_ORDER_QUANTITY(j),
4446 lb_ROUNDING_CONTROL_TYPE(j),
4447 lb_PLANNING_TIME_FENCE_CODE(j),
4448 lb_PLANNING_TIME_FENCE_DAYS(j),
4449 lb_DEMAND_TIME_FENCE_DAYS(j),
4450 lb_DESCRIPTION(j),
4451 lb_RELEASE_TIME_FENCE_CODE(j),
4452 lb_RELEASE_TIME_FENCE_DAYS(j),
4453 lb_IN_SOURCE_PLAN(j),
4454 lb_REVISION(j),
4455 lb_SR_CATEGORY_ID(j),
4456 lb_CATEGORY_NAME(j),
4457 lb_ABC_CLASS_ID(j),
4458 lb_ABC_CLASS_NAME(j),
4459 lb_MRP_PLANNING_CODE(j),
4460 lb_FIXED_LEAD_TIME(j),
4461 lb_VARIABLE_LEAD_TIME(j),
4462 lb_PREPROCESSING_LEAD_TIME(j),
4463 lb_POSTPROCESSING_LEAD_TIME(j),
4464 lb_FULL_LEAD_TIME(j),
4465 lb_CUMULATIVE_TOTAL_LEAD_TIME(j),
4466 lb_CUM_MANUFACTURING_LEAD_TIME(j),
4467 lb_UOM_CODE(j),
4468 lb_UNIT_WEIGHT(j),
4469 lb_UNIT_VOLUME(j),
4470 lb_WEIGHT_UOM(j),
4471 lb_VOLUME_UOM(j),
4472 lb_PRODUCT_FAMILY_ID(j),
4473 lb_ATP_RULE_ID(j),
4474 lb_ATP_COMPONENTS_FLAG(j),
4475 lb_BUILT_IN_WIP_FLAG(j),
4476 lb_PURCHASING_ENABLED_FLAG(j),
4477 lb_PLANNING_MAKE_BUY_CODE(j),
4478 lb_REPETITIVE_TYPE(j),
4479 lb_REPETITIVE_VARIANCE_DAYS(j),
4480 lb_STANDARD_COST(j),
4481 lb_CARRYING_COST(j),
4482 lb_ORDER_COST(j),
4483 lb_DMD_LATENESS_COST(j),
4484 lb_SS_PENALTY_COST(j),
4485 lb_SUPPLIER_CAP_OVERUTIL_COST(j),
4486 lb_LIST_PRICE(j),
4487 lb_AVERAGE_DISCOUNT(j),
4488 lb_ENGINEERING_ITEM_FLAG(j),
4489 lb_INVENTORY_ITEM_FLAG(j),
4490 lb_WIP_SUPPLY_TYPE(j),
4491 lb_MRP_SAFETY_STOCK_CODE(j),
4492 lb_MRP_SAFETY_STOCK_PERCENT(j),
4493 lb_SAFETY_STOCK_BUCKET_DAYS(j),
4494 lb_BUYER_NAME(j),
4495 lb_PLANNER_CODE(j),
4496 lb_PLANNING_EXCEPTION_SET(j),
4497 lb_EXCESS_QUANTITY(j),
4498 lb_SHORTAGE_TYPE(j),
4499 lb_EXCEPTION_SHORTAGE_DAYS(j),
4500 lb_EXCESS_TYPE(j),
4501 lb_EXCEPTION_EXCESS_DAYS(j),
4502 lb_EXCEPTION_OVERPROMISED_DAYS(j),
4503 lb_BOM_ITEM_TYPE(j),
4504 lb_ATO_FORECAST_CONTROL(j),
4505 lb_EFFECTIVITY_CONTROL(j),
4506 lb_ORGANIZATION_CODE(j),
4507 lb_ACCEPTABLE_RATE_INCREASE(j),
4508 lb_ACCEPTABLE_RATE_DECREASE(j),
4509 lb_INVENTORY_PLANNING_CODE(j),
4510 lb_ACCEPTABLE_EARLY_DELIVERY(j),
4511 lb_MRP_CALCULATE_ATP_FLAG(j),
4512 lb_END_ASSEMBLY_PEGGING_FLAG(j),
4513 lb_BASE_ITEM_ID(j),
4514 lb_PRIMARY_SUPPLIER_ID(j),
4515 lb_ATP_FLAG(j),
4516 lb_NEW_ATP_FLAG(j),
4517 lb_REVISION_QTY_CONTROL_CODE(j),
4518 lb_EXPENSE_ACCOUNT(j),
4519 lb_INVENTORY_ASSET_FLAG(j),
4520 lb_BUYER_ID(j),
4521 lb_SOURCE_ORG_ID(j),
4522 lb_MATERIAL_COST(j),
4523 lb_RESOURCE_COST(j),
4524 lb_REPLENISH_TO_ORDER_FLAG(j),
4525 lb_PICK_COMPONENTS_FLAG(j),
4526 lb_YIELD_CONV_FACTOR(j),
4527 lb_PIP_FLAG(j),
4528 lb_REDUCE_MPS(j),
4529 lb_CRITICAL_COMPONENT_FLAG(j),
4530 lb_VMI_MINIMUM_UNITS(j),
4531 lb_VMI_MINIMUM_DAYS(j),
4532 lb_VMI_MAXIMUM_UNITS(j),
4533 lb_VMI_MAXIMUM_DAYS(j),
4534 lb_VMI_FIXED_ORDER_QUANTITY(j),
4535 lb_SO_AUTHORIZATION_FLAG(j),
4536 lb_CONSIGNED_FLAG(j),
4537 lb_ASN_AUTOEXPIRE_FLAG(j),
4538 lb_VMI_FORECAST_TYPE(j),
4539 lb_FORECAST_HORIZON(j),
4540 lb_BUDGET_CONSTRAINED(j),
4541 lb_DAYS_TGT_INV_SUPPLY(j),
4542 lb_DAYS_TGT_INV_WINDOW(j),
4543 lb_DAYS_MAX_INV_SUPPLY(j),
4544 lb_DAYS_MAX_INV_WINDOW(j),
4545 lb_DRP_PLANNED(j),
4546 lb_CONTINOUS_TRANSFER(j),
4547 lb_CONVERGENCE(j),
4548 lb_DIVERGENCE(j),
4549 lb_SOURCE_TYPE(j),
4550 lb_SUBSTITUTION_WINDOW(j),
4551 lb_CREATE_SUPPLY_FLAG(j),
4552 lb_SERIAL_NUMBER_CONTROL_CODE(j),
4553 lb_SR_INVENTORY_ITEM_ID(j),
4554 lb_ITEM_CREATION_DATE(j),
4555 lb_SR_INSTANCE_ID(j),
4556 lb_EAM_ITEM_TYPE(j),
4557 lb_REPAIR_LEAD_TIME(j), --# For Bug 5606037 SRP Changes
4558 lb_PRE_POSITIONING_POINT(j),
4559 lb_REPAIR_YIELD(j),
4560 lb_REPAIR_PROGRAM(j),
4561 MSC_CL_COLLECTION.v_last_collection_id,
4562 MSC_CL_COLLECTION.v_current_date,
4563 MSC_CL_COLLECTION.v_current_user,
4564 MSC_CL_COLLECTION.v_current_date,
4565 MSC_CL_COLLECTION.v_current_user,
4566 lb_PEGGING_DEMAND_WINDOW_DAYS(j),
4567 lb_PEGGING_SUPPLY_WINDOW_DAYS(j);
4568
4569 END IF;
4570 END IF;
4571 IF ( lv_MSC_CONFIGURATION = MSC_UTIL.G_CONF_APS_SCE OR
4572 lv_MSC_CONFIGURATION = MSC_UTIL.G_CONF_SCE) THEN
4573 UPDATE MSC_ITEMS
4574 SET description= lb_DESCRIPTION(j)
4575 WHERE inventory_item_id = lb_INVENTORY_ITEM_ID(j);
4576 END IF;
4577 c_count:= c_count+1;
4578
4579 IF c_count>MSC_CL_COLLECTION.PBS THEN
4580
4581 COMMIT;
4582
4583 c_count:= 0;
4584
4585 END IF;
4586
4587 EXCEPTION
4588 WHEN OTHERS THEN
4589 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
4590
4591 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4592 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4593 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ITEM');
4594 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SYSTEM_ITEMS');
4595 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4596
4597 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4598 RAISE;
4599
4600 ELSE
4601
4602 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
4603
4604 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4605 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4606 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ITEM');
4607 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SYSTEM_ITEMS');
4608 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4609
4610 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4611 FND_MESSAGE.SET_TOKEN('COLUMN', 'ITEM_NAME');
4612 FND_MESSAGE.SET_TOKEN('VALUE', ITEM_NAME( lb_INVENTORY_ITEM_ID(j)));
4613 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4614
4615 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4616 FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
4617 FND_MESSAGE.SET_TOKEN('VALUE',
4618 MSC_GET_NAME.ORG_CODE( lb_ORGANIZATION_ID(j),
4619 MSC_CL_COLLECTION.v_instance_id));
4620 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4621
4622 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4623 FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_INVENTORY_ITEM_ID');
4624 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(lb_SR_INVENTORY_ITEM_ID(j)));
4625 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4626
4627 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4628 END IF;
4629
4630 END;
4631 END LOOP;
4632 END LOOP;
4633 END IF;
4634 CLOSE c1;
4635 COMMIT;
4636
4637 END IF;
4638
4639 BEGIN
4640
4641 IF ((MSC_CL_COLLECTION.v_coll_prec.org_group_flag <> MSC_UTIL.G_ALL_ORGANIZATIONS) AND (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES)) THEN
4642
4643 lv_tbl:= 'SYSTEM_ITEMS_'||MSC_CL_COLLECTION.v_INSTANCE_CODE;
4644
4645 lv_sql_stmt:=
4646 'INSERT INTO '||lv_tbl
4647 ||' SELECT * from MSC_SYSTEM_ITEMS'
4648 ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
4649 ||' AND plan_id = -1 '
4650 ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
4651
4652 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The sql statement is '||lv_sql_stmt);
4653 EXECUTE IMMEDIATE lv_sql_stmt;
4654
4655 COMMIT;
4656
4657 END IF;
4658
4659 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
4660 MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
4661 lv_retcode,
4662 'MSC_SYSTEM_ITEMS',
4663 MSC_CL_COLLECTION.v_INSTANCE_CODE,
4664 MSC_UTIL.G_ERROR
4665 );
4666
4667 IF lv_retcode = MSC_UTIL.G_ERROR THEN
4668 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
4669 RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
4670 ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
4671 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
4672 END IF;
4673
4674 END IF;
4675
4676 /*call to insert ASL */
4677 IF ( NOT MSC_CL_COLLECTION.v_is_legacy_refresh) THEN
4678 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'ADD_NEW_IMPL_ITEM_ASL PROCEDURE BEING CALLED FROM ITEM PROCEDURE ');
4679 ADD_NEW_IMPL_ITEM_ASL;
4680 END IF ;
4681
4682 EXCEPTION
4683 WHEN OTHERS THEN
4684
4685 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4686 RAISE;
4687 END;
4688 COMMIT;
4689
4690 END LOAD_ITEM;
4691
4692
4693
4694
4695 END MSC_CL_ITEM_ODS_LOAD;