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