[Home] [Help]
PACKAGE BODY: APPS.MSC_CL_ITEM_PULL
Source
1 PACKAGE BODY MSC_CL_ITEM_PULL AS -- body
2 /* $Header:*/
3
4
5
6 v_union_sql varchar2(32767);
7 v_temp_tp_sql VARCHAR2(100);
8 v_sql_stmt VARCHAR2(32767);
9 v_temp_sql VARCHAR2(15000);
10 v_temp_sql1 VARCHAR2(1000);
11 v_temp_sql2 VARCHAR2(1000);
12 v_temp_sql3 VARCHAR2(1000);
13 v_temp_sql4 VARCHAR2(1000);
14
15 -- NULL_DBLINK CONSTANT VARCHAR2(1):= ' ';
16 -- NULL_DBLINK CONSTANT VARCHAR2(1) :=MSC_UTIL.NULL_DBLINK;
17
18 v_gmp_routine_name VARCHAR2(50);
19 GMP_ERROR EXCEPTION;
20
21
22 --============================================================
23
24 PROCEDURE LOAD_CATEGORY IS
25 BEGIN
26
27 IF MSC_CL_PULL.ITEM_ENABLED= MSC_UTIL.SYS_YES THEN
28
29 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
30 MSC_CL_PULL.v_table_name:= 'MSC_ST_ITEM_CATEGORIES';
31 MSC_CL_PULL.v_view_name := 'MRP_AD_ITEM_CATEGORIES_V';
32 v_sql_stmt:=
33 ' insert into MSC_ST_ITEM_CATEGORIES'
34 ||'( INVENTORY_ITEM_ID,'
35 ||' ORGANIZATION_ID,'
36 ||' SR_CATEGORY_SET_ID,'
37 ||' SR_CATEGORY_ID,'
38 ||' DELETED_FLAG,'
39 ||' REFRESH_ID,'
40 ||' SR_INSTANCE_ID)'
41 ||' select '
42 ||' x.INVENTORY_ITEM_ID,'
43 ||' x.ORGANIZATION_ID,'
44 ||' x.CATEGORY_SET_ID,'
45 ||' x.CATEGORY_ID,'
46 ||' 1,'
47 ||' :v_refresh_id,'
48 ||' :v_instance_id'
49 ||' from MRP_AD_ITEM_CATEGORIES_V'||MSC_CL_PULL.v_dblink||' x'
50 ||' WHERE x.RN> :v_lrn ';
51
52 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn;
53
54 COMMIT;
55 END IF; --- Incremental refresh
56
57
58 MSC_CL_PULL.v_table_name:= 'MSC_ST_ITEM_CATEGORIES';
59 MSC_CL_PULL.v_view_name := 'MRP_AP_ITEM_CATEGORIES_V';
60
61 /* Bug 4365337 - don't need the diff. RNs since Rn1, RN3 and RN4 are anyway 0.
62 We need to check only for RN2 (both in 11i and rel 11.0)
63 Hence commenting the entire v_union_sql. The v_sql_stmt itself can handle this
64 */
65
66 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
67 v_union_sql :=
68 ' AND ( x.RN1>'||MSC_CL_PULL.v_lrn||')'
69 ||' UNION '
70 ||' select'
71 ||' x.INVENTORY_ITEM_ID,'
72 ||' x.ORGANIZATION_ID,'
73 ||' x.CATEGORY_SET_ID,'
74 ||' x.CATEGORY_ID,'
75 ||' x.CATEGORY_NAME,'
76 ||' x.DESCRIPTION,'
77 ||' x.DISABLE_DATE- :v_dgmt,'
78 ||' x.SUMMARY_FLAG,'
79 ||' x.ENABLED_FLAG,'
80 ||' x.START_DATE_ACTIVE- :v_dgmt,'
81 ||' x.END_DATE_ACTIVE- :v_dgmt,'
82 ||' 2,'
83 ||' :v_refresh_id,'
84 ||' :v_instance_id'
85 ||' from MRP_AP_ITEM_CATEGORIES_V'||MSC_CL_PULL.v_dblink||' x'
86 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
87 --||' AND NVL(x.LANGUAGE, :v_lang)= :v_lang'
88 ||' AND ( x.RN2>'||MSC_CL_PULL.v_lrn||')';
89
90 /*
91 ||' UNION '
92 ||' select'
93 ||' x.INVENTORY_ITEM_ID,'
94 ||' x.ORGANIZATION_ID,'
95 ||' x.CATEGORY_SET_ID,'
96 ||' x.CATEGORY_ID,'
97 ||' x.CATEGORY_NAME,'
98 ||' x.DESCRIPTION,'
99 ||' x.DISABLE_DATE- :v_dgmt,'
100 ||' x.SUMMARY_FLAG,'
101 ||' x.ENABLED_FLAG,'
102 ||' x.START_DATE_ACTIVE- :v_dgmt,'
103 ||' x.END_DATE_ACTIVE- :v_dgmt,'
104 ||' 2,'
105 ||' :v_refresh_id,'
106 ||' :v_instance_id'
107 ||' from MRP_AP_ITEM_CATEGORIES_V'||MSC_CL_PULL.v_dblink||' x'
108 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
109 ||' AND NVL(x.LANGUAGE, :v_lang)= :v_lang'
110 ||' AND ( x.RN3>'||MSC_CL_PULL.v_lrn||')'
111 ||' UNION '
112 ||' select'
113 ||' x.INVENTORY_ITEM_ID,'
114 ||' x.ORGANIZATION_ID,'
115 ||' x.CATEGORY_SET_ID,'
116 ||' x.CATEGORY_ID,'
117 ||' x.CATEGORY_NAME,'
118 ||' x.DESCRIPTION,'
119 ||' x.DISABLE_DATE- :v_dgmt,'
120 ||' x.SUMMARY_FLAG,'
121 ||' x.ENABLED_FLAG,'
122 ||' x.START_DATE_ACTIVE- :v_dgmt,'
123 ||' x.END_DATE_ACTIVE- :v_dgmt,'
124 ||' 2,'
125 ||' :v_refresh_id,'
126 ||' :v_instance_id'
127 ||' from MRP_AP_ITEM_CATEGORIES_V'||MSC_CL_PULL.v_dblink||' x'
128 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
129 ||' AND NVL(x.LANGUAGE, :v_lang)= :v_lang'
130 ||' AND ( x.RN4>'||MSC_CL_PULL.v_lrn||')' ;
131 */
132 ELSE
133 /*
134 v_union_sql :=
135 ' AND ( x.RN1>'||MSC_CL_PULL.v_lrn
136 ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
137 ||' OR x.RN3>'||MSC_CL_PULL.v_lrn
138 ||' OR x.RN4>'||MSC_CL_PULL.v_lrn||')';
139 */
140 v_union_sql := ' ';
141 END IF;
142
143
144 v_sql_stmt:=
145 'insert into MSC_ST_ITEM_CATEGORIES'
146 ||' ( INVENTORY_ITEM_ID,'
147 ||' ORGANIZATION_ID,'
148 ||' SR_CATEGORY_SET_ID,'
149 ||' SR_CATEGORY_ID,'
150 ||' CATEGORY_NAME,'
151 ||' DESCRIPTION,'
152 ||' DISABLE_DATE,'
153 ||' SUMMARY_FLAG,'
154 ||' ENABLED_FLAG,'
155 ||' START_DATE_ACTIVE,'
156 ||' END_DATE_ACTIVE,'
157 ||' DELETED_FLAG,'
158 ||' REFRESH_ID,'
159 ||' SR_INSTANCE_ID)'
160 ||' select'
161 ||' x.INVENTORY_ITEM_ID,'
162 ||' x.ORGANIZATION_ID,'
163 ||' x.CATEGORY_SET_ID,'
164 ||' x.CATEGORY_ID,'
165 ||' x.CATEGORY_NAME,'
166 ||' x.DESCRIPTION,'
167 ||' x.DISABLE_DATE- :v_dgmt,'
168 ||' x.SUMMARY_FLAG,'
169 ||' x.ENABLED_FLAG,'
170 ||' x.START_DATE_ACTIVE- :v_dgmt,'
171 ||' x.END_DATE_ACTIVE- :v_dgmt,'
172 ||' 2,'
173 ||' :v_refresh_id,'
174 ||' :v_instance_id'
175 ||' from MRP_AP_ITEM_CATEGORIES_V'||MSC_CL_PULL.v_dblink||' x'
176 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
177 -- bug 4365337 remove lang cond ||' AND NVL(x.LANGUAGE, :v_lang)= :v_lang'
178 --bug 4365337 remove v_union_sql and instead add cond. on RN2
179 || v_union_sql ;
180 --||' AND x.RN2 >'||MSC_CL_PULL.v_lrn;
181
182 --bug 4365337 remove MSC_CL_PULL.v_lang bind parameters since the stmt. does not have it.
183 /* bug 4365337 - no need to check incremental or not since it is the same stmt.
184 Hence commenting out the foll. stmt*/
185 /* Uncommenting check for incremental refresh */
186
187 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
188
189 EXECUTE IMMEDIATE v_sql_stmt
190 USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
191 MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
192 -- MSC_CL_PULL.v_lang, MSC_CL_PULL.v_lang,
193 MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
194 MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
195 /* MSC_CL_PULL.v_lang, MSC_CL_PULL.v_lang,
196 MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
197 MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
198 MSC_CL_PULL.v_lang, MSC_CL_PULL.v_lang,
199 MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
200 MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
201 */
202 ELSE
203
204
205 EXECUTE IMMEDIATE v_sql_stmt
206 USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
207 MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
208
209 -- bug 4365337
210 END IF;
211 COMMIT;
212
213 MSC_CL_PULL.v_table_name:= 'MSC_ST_CATEGORY_SETS';
214 MSC_CL_PULL.v_view_name := 'MRP_AP_CATEGORY_SETS_V';
215
216 v_sql_stmt:=
217 ' insert into MSC_ST_CATEGORY_SETS'
218 ||' ( SR_CATEGORY_SET_ID,'
219 ||' CATEGORY_SET_NAME,'
220 ||' DESCRIPTION,'
221 ||' CONTROL_LEVEL,'
222 ||' DEFAULT_FLAG,'
223 ||' DELETED_FLAG,'
224 ||' REFRESH_ID,'
225 ||' SR_INSTANCE_ID)'
226 ||' select'
227 ||' x.CATEGORY_SET_ID,'
228 ||' x.CATEGORY_SET_NAME,'
229 ||' x.DESCRIPTION,'
230 ||' x.CONTROL_LEVEL,'
231 ||' x.DEFAULT_FLAG,'
232 ||' 2,'
233 ||' :v_refresh_id,'
234 ||' :v_instance_id'
235 ||' from MRP_AP_CATEGORY_SETS_V'||MSC_CL_PULL.v_dblink||' x'
236 ||' where NVL(x.LANGUAGE, :v_lang)= :v_lang'
237 ||' AND x.RN1>'||MSC_CL_PULL.v_lrn;
238
239 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lang, MSC_CL_PULL.v_lang;
240
241 COMMIT;
242
243 END IF;
244
245 END LOAD_CATEGORY;
246
247
248 --==================================================================
249
250 PROCEDURE LOAD_ITEM ( p_worker_num IN NUMBER) IS
251
252 lv_in_org_str varchar2(10240):= NULL;
253 lv_uom_code varchar2(3);
254 lv_base_lang_diff number;
255 lv_item_name varchar2(2000) := NULL;
256 lv_view_name_stmt varchar2(1000):= NULL;
257 lv_icode varchar2(3);
258 v_dblink_a2m VARCHAR2(128);
259
260 cursor org IS
261 select /*+ INDEX(MSC_INSTANCE_ORGS MSC_INSTANCE_ORGS_U1) */ organization_id org_id,
262 DECODE( MOD(rownum,MSC_CL_PULL.TOTAL_IWN),
263 p_worker_num, MSC_UTIL.SYS_YES,
264 MSC_UTIL.SYS_NO) yes_flag
265 from msc_instance_orgs
266 where sr_instance_id= MSC_CL_PULL.v_instance_id
267 and enabled_flag= 1
268 and ((MSC_CL_PULL.v_org_group = MSC_UTIL.G_ALL_ORGANIZATIONS ) or (org_group = MSC_CL_PULL.v_org_group))
269 order by
270 organization_id;
271
272 lv_org_count NUMBER:=0;
273
274 BEGIN
275
276 IF MSC_CL_PULL.ITEM_ENABLED= MSC_UTIL.SYS_YES THEN
277
278 FOR lc_ins_org IN org LOOP
279
280 IF lc_ins_org.yes_flag = MSC_UTIL.SYS_YES THEN
281 lv_org_count := lv_org_count + 1;
282
283 IF lv_org_count = 1 THEN
284 lv_in_org_str:=' IN ('|| lc_ins_org.org_id;
285 ELSE
286 lv_in_org_str := lv_in_org_str||','||lc_ins_org.org_id;
287 END IF;
288 END IF;
289
290 END LOOP;
291
292 IF lv_org_count > 0 THEN
293 lv_in_org_str:= lv_in_org_str || ')';
294 ELSE
295 RETURN;
296 END IF;
297
298
299 MSC_CL_PULL.v_table_name:= 'MSC_ST_SYSTEM_ITEMS';
300
301 if (MSC_CL_PULL.G_COLLECT_ITEM_COSTS = 'N') and (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115) then
302 MSC_CL_PULL.v_view_name := 'MRP_AP_NOCOST_SYSTEM_ITEMS_V';
303 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Debug : View name = ' || MSC_CL_PULL.v_view_name);
304 else
305 MSC_CL_PULL.v_view_name := 'MRP_AP_SYSTEM_ITEMS_V';
306 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Debug : View name = ' || MSC_CL_PULL.v_view_name);
307 end if;
308
309 BEGIN
310
311 IF MSC_CL_PULL.v_apps_ver = MSC_UTIL.G_APPS107 THEN
312
313 v_temp_sql := 'NULL, NULL, NULL, NULL, NULL, x.DESCRIPTION, x.LIST_PRICE ,x.ITEM_NAME, '
314 ||'NULL, NULL, NULL, NULL, NULL, NULL, NULL,NULL, NULL, NULL, NULL, NULL,'
315 ||' NULL, NULL, NULL, NULL, NULL,NULL, NULL, NULL, NULL, NULL,NULL, NULL, NULL,NULL,NULL,NULL, NULL,NULL,'; /* ds change added null*/
316
317 ELSIF MSC_CL_PULL.v_apps_ver = MSC_UTIL.G_APPS110 THEN
318
319 v_temp_sql := 'x.REPLENISH_TO_ORDER_FLAG,x.PICK_COMPONENTS_FLAG ,NULL, NULL,NULL, '
320 ||' x.DESCRIPTION, x.LIST_PRICE , x.ITEM_NAME, NULL, NULL,NULL, NULL, NULL, NULL, NULL,NULL, NULL, NULL, NULL, NULL, '
321 ||' NULL, NULL, NULL, NULL, NULL,NULL, NULL, NULL, NULL, NULL,NULL, NULL, NULL,NULL,NULL, NULL, NULL,NULL,'; /* ds change added null for eam_item_type*/
322
323 ELSIF (MSC_CL_PULL.v_apps_ver = MSC_UTIL.G_APPS115) THEN
324
325 v_temp_sql := ' x.REPLENISH_TO_ORDER_FLAG, x.PICK_COMPONENTS_FLAG ,';
326
327 BEGIN
328 -- check if the UOM class is defined for the class in profile option-FM_YIELD_TYPE in source instance
329 v_sql_stmt := ' select uom_code '
330 ||' from mtl_units_of_measure'||MSC_CL_PULL.v_dblink
331 ||' where uom_class = FND_PROFILE.VALUE'||MSC_CL_PULL.v_dblink||'(''FM_YIELD_TYPE'')'
332 ||' and base_uom_flag = ''Y'' ';
333
334 execute immediate v_sql_stmt into lv_uom_code;
335 v_temp_sql := v_temp_sql ||' x.CONV_FACTOR, ';
336
337 EXCEPTION
338 WHEN OTHERS THEN
339 lv_uom_code := NULL;
340 v_temp_sql := v_temp_sql || ' -99999, ';
341 END ;
342 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Debug Message - Base uom code : '||lv_uom_code);
343
344
345 v_temp_sql := v_temp_sql || ' x.CREATE_SUPPLY_FLAG,x.SUBSTITUTION_WINDOW, ';
346
347 BEGIN
348 -- check if the base language is different than the installed lang in source instance
349 -- If the base installed_flag is same as userenv lang- then dont go to TL table
350 v_sql_stmt := ' select 1 '
351 ||' from fnd_languages'||MSC_CL_PULL.v_dblink
352 ||' where language_code = mrp_cl_function.get_userenv_lang'||MSC_CL_PULL.v_dblink
353 ||' and installed_flag = ''B'' ';
354
355 execute immediate v_sql_stmt into lv_base_lang_diff;
356
357 EXCEPTION
358 WHEN NO_DATA_FOUND THEN
359 -- This means that the base installed lang is different than the userenv(LANG)
360 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Source Environment Language is different then Base Installed Language');
361 lv_base_lang_diff := 2;
362 WHEN OTHERS THEN
363 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Unhandled Exception when trying to identify Base Installed Language');
364 lv_base_lang_diff := 1;
365 END ;
366
367 IF (lv_base_lang_diff = 1) THEN
368 v_temp_sql := v_temp_sql || ' x.DESCRIPTION, ';
369 ELSE
370 v_temp_sql := v_temp_sql ||' x.DESCRIPTION_TL , ';
371 END IF;
372
373 -- If Profile does not contain a Price List then for discrete list_price is null here but in ODS Load
374 -- the nvl(list_price,item_cost) is performed, for process get the List Price.
375 IF MSC_CL_PULL.v_mrp_bis_price_list is null THEN
376 v_temp_sql := v_temp_sql ||' x.LIST_PRICE, ';
377 ELSE
378 v_temp_sql := v_temp_sql ||' x.MRP_BIS_LIST_PRICE, ';
379 END IF;
380
381 BEGIN
382 -- check if the base language is different than the installed lang in source instance
383 -- If the base installed_flag is same as userenv lang- then dont go to TL table
384
385 select instance_code, DECODE(A2M_DBLINK, NULL, MSC_UTIL.NULL_DBLINK, A2M_DBLINK)
386 into lv_icode, v_dblink_a2m
390 ||' from MRP_AP_APPS_INSTANCES_ALL'||MSC_CL_PULL.v_dblink
387 from msc_apps_instances where instance_id = MSC_CL_PULL.v_instance_id;
388
389 v_sql_stmt := ' select item_name_from_kfv '
391 ||' WHERE INSTANCE_ID = '||MSC_CL_PULL.v_instance_id
392 ||' AND INSTANCE_CODE= '''||lv_icode||''''
393 ||' AND nvl(A2M_DBLINK,'||''''||MSC_UTIL.NULL_DBLINK ||''''||') = '''||v_dblink_a2m||'''';
394
395 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'item kfv v_sql_stmt - ' || v_sql_stmt);
396
397 execute immediate v_sql_stmt into lv_item_name;
398
399 EXCEPTION
400 WHEN OTHERS THEN
401 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in deriving item_name kfv...setting to segment1 - ' || sqlerrm);
402 lv_item_name := 'x.SEGMENT1';
403 END ;
404
405 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Debug: Item Name String: '|| lv_item_name);
406 v_temp_sql := v_temp_sql ||' '|| lv_item_name ||', ';
407
408 -- for Bugfix 3057925
409 v_temp_sql := v_temp_sql ||' x.REDUCE_MPS,x.CRITICAL_COMPONENT_FLAG,x.VMI_MINIMUM_UNITS,x.VMI_MINIMUM_DAYS,x.VMI_MAXIMUM_UNITS, '
410 ||' x.VMI_MAXIMUM_DAYS,x.VMI_FIXED_ORDER_QUANTITY,x.SO_AUTHORIZATION_FLAG,x.CONSIGNED_FLAG,x.ASN_AUTOEXPIRE_FLAG, '
411 ||' x.VMI_FORECAST_TYPE,x.FORECAST_HORIZON,x.EXCLUDE_FROM_BUDGET_FLAG,x.DAYS_TGT_INV_SUPPLY,x.DAYS_TGT_INV_WINDOW, '
412 ||' x.DAYS_MAX_INV_SUPPLY,x.DAYS_MAX_INV_WINDOW,x.DRP_PLANNED_FLAG,x.CONTINOUS_TRANSFER,x.CONVERGENCE,x.DIVERGENCE,'
413 ||' x.EAM_ITEM_TYPE, x.MSIB_CREATION_DATE, x.SHORTAGE_TYPE , x.EXCESS_TYPE ,x.PLANNING_TIME_FENCE_CODE,NULL,NULL,NULL,NULL,'; /* ds change eam_item_type added */
414
415
416
417 /* # For Bug 5606037 SRP Changes NULL Colums to collect Item Attribute Data */
418
419 ELSIF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS120 THEN --# For Bug 5606037 SRP Changes
420
421 v_temp_sql := ' x.REPLENISH_TO_ORDER_FLAG, x.PICK_COMPONENTS_FLAG ,';
422
423 BEGIN
424 -- check if the UOM class is defined for the class in profile option-FM_YIELD_TYPE in source instance
425 v_sql_stmt := ' select uom_code '
426 ||' from mtl_units_of_measure'||MSC_CL_PULL.v_dblink
427 ||' where uom_class = FND_PROFILE.VALUE'||MSC_CL_PULL.v_dblink||'(''FM_YIELD_TYPE'')'
428 ||' and base_uom_flag = ''Y'' ';
429
430 execute immediate v_sql_stmt into lv_uom_code;
431 v_temp_sql := v_temp_sql ||' x.CONV_FACTOR, ';
432
433 EXCEPTION
434 WHEN OTHERS THEN
435 lv_uom_code := NULL;
436 v_temp_sql := v_temp_sql || ' -99999, ';
437 END ;
438 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Debug Message - Base uom code : '||lv_uom_code);
439 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'item kfv v_sql_stmt of MSC_UTIL.G_APPS120 - ' || v_sql_stmt);
440
441 v_temp_sql := v_temp_sql || ' x.CREATE_SUPPLY_FLAG,x.SUBSTITUTION_WINDOW, ';
442
443 BEGIN
444 -- check if the base language is different than the installed lang in source instance
445 -- If the base installed_flag is same as userenv lang- then dont go to TL table
446 v_sql_stmt := ' select 1 '
447 ||' from fnd_languages'||MSC_CL_PULL.v_dblink
448 ||' where language_code = mrp_cl_function.get_userenv_lang'||MSC_CL_PULL.v_dblink
449 ||' and installed_flag = ''B'' ';
450
451 execute immediate v_sql_stmt into lv_base_lang_diff;
452
453 EXCEPTION
454 WHEN NO_DATA_FOUND THEN
455 -- This means that the base installed lang is different than the userenv(LANG)
456 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Source Environment Language is different then Base Installed Language');
457 lv_base_lang_diff := 2;
458 WHEN OTHERS THEN
459 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Unhandled Exception when trying to identify Base Installed Language');
460 lv_base_lang_diff := 1;
461 END ;
462
463 IF (lv_base_lang_diff = 1) THEN
464 v_temp_sql := v_temp_sql || ' x.DESCRIPTION, ';
465 ELSE
466 v_temp_sql := v_temp_sql ||' x.DESCRIPTION_TL , ';
467 END IF;
468
469 -- If Profile does not contain a Price List then for discrete list_price is null here but in ODS Load
470 -- the nvl(list_price,item_cost) is performed, for process get the List Price.
471 IF MSC_CL_PULL.v_mrp_bis_price_list is null THEN
472 v_temp_sql := v_temp_sql ||' x.LIST_PRICE, ';
473 ELSE
474 v_temp_sql := v_temp_sql ||' x.MRP_BIS_LIST_PRICE, ';
475 END IF;
476
477 BEGIN
478 -- check if the base language is different than the installed lang in source instance
479 -- If the base installed_flag is same as userenv lang- then dont go to TL table
480
481 select instance_code, DECODE(A2M_DBLINK, NULL, MSC_UTIL.NULL_DBLINK, A2M_DBLINK)
482 into lv_icode, v_dblink_a2m
483 from msc_apps_instances where instance_id = MSC_CL_PULL.v_instance_id;
484
485 v_sql_stmt := ' select item_name_from_kfv '
486 ||' from MRP_AP_APPS_INSTANCES_ALL'||MSC_CL_PULL.v_dblink
487 ||' WHERE INSTANCE_ID = '||MSC_CL_PULL.v_instance_id
488 ||' AND INSTANCE_CODE= '''||lv_icode||''''
492
489 ||' AND nvl(A2M_DBLINK,'||''''||MSC_UTIL.NULL_DBLINK ||''''||') = '''||v_dblink_a2m||'''';
490
491 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'item kfv v_sql_stmt - ' || v_sql_stmt);
493 execute immediate v_sql_stmt into lv_item_name;
494
495 EXCEPTION
496 WHEN OTHERS THEN
497 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in deriving item_name kfv...setting to segment1 - ' || sqlerrm);
498 lv_item_name := 'x.SEGMENT1';
499 END ;
500
501 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Debug: Item Name String: '|| lv_item_name);
502 v_temp_sql := v_temp_sql ||' '|| lv_item_name ||', ';
503
504 -- for Bugfix 3057925
505 v_temp_sql := v_temp_sql ||' x.REDUCE_MPS,x.CRITICAL_COMPONENT_FLAG,x.VMI_MINIMUM_UNITS,x.VMI_MINIMUM_DAYS,x.VMI_MAXIMUM_UNITS, '
506 ||' x.VMI_MAXIMUM_DAYS,x.VMI_FIXED_ORDER_QUANTITY,x.SO_AUTHORIZATION_FLAG,x.CONSIGNED_FLAG,x.ASN_AUTOEXPIRE_FLAG, '
507 ||' x.VMI_FORECAST_TYPE,x.FORECAST_HORIZON,x.EXCLUDE_FROM_BUDGET_FLAG,x.DAYS_TGT_INV_SUPPLY,x.DAYS_TGT_INV_WINDOW, '
508 ||' x.DAYS_MAX_INV_SUPPLY,x.DAYS_MAX_INV_WINDOW,x.DRP_PLANNED_FLAG,x.CONTINOUS_TRANSFER,x.CONVERGENCE,x.DIVERGENCE,'
509 ||' x.EAM_ITEM_TYPE, x.MSIB_CREATION_DATE, x.SHORTAGE_TYPE , x.EXCESS_TYPE ,x.PLANNING_TIME_FENCE_CODE,x.REPAIR_LEADTIME,x.preposition_point,x.REPAIR_YIELD,x.repair_program,'; /* ds change eam_item_type added */
510 --# For Bug 5606037 SRP Changes
511
512 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Debug: v_temp_sql String: '|| v_temp_sql);
513 END IF; --# For Bug 5606037 SRP Changes
514
515 End;
516
517
518 if (MSC_CL_PULL.G_COLLECT_ITEM_COSTS = 'N') and (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115) then
519 lv_view_name_stmt := ' from MRP_AP_NOCOST_SYSTEM_ITEMS_V'||MSC_CL_PULL.v_dblink||' x';
520 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Debug : lv_view_name_stmt = ' || lv_view_name_stmt);
521 else
522 lv_view_name_stmt := ' from MRP_AP_SYSTEM_ITEMS_V'||MSC_CL_PULL.v_dblink||' x';
523 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Debug : lv_view_name_stmt = ' || lv_view_name_stmt);
524 end if;
525
526
527
528 v_sql_stmt:=
529 'insert into MSC_ST_SYSTEM_ITEMS'
530 ||' ( ORGANIZATION_ID,'
531 ||' SR_INVENTORY_ITEM_ID,'
532 ||' LOTS_EXPIRATION,'
533 ||' LOT_CONTROL_CODE,'
534 ||' SHRINKAGE_RATE,'
535 ||' FIXED_DAYS_SUPPLY,'
536 ||' FIXED_ORDER_QUANTITY,'
537 ||' FIXED_LOT_MULTIPLIER,'
538 ||' MINIMUM_ORDER_QUANTITY,'
539 ||' MAXIMUM_ORDER_QUANTITY,'
540 ||' ROUNDING_CONTROL_TYPE,'
541 ||' PLANNING_TIME_FENCE_DAYS,'
542 ||' DEMAND_TIME_FENCE_DAYS,'
543 ||' ABC_CLASS_ID,'
544 ||' ABC_CLASS_NAME,'
545 ||' SR_CATEGORY_ID,'
546 ||' CATEGORY_NAME,'
547 ||' MRP_PLANNING_CODE,'
548 ||' FIXED_LEAD_TIME,'
549 ||' VARIABLE_LEAD_TIME,'
550 ||' PREPROCESSING_LEAD_TIME,'
551 ||' POSTPROCESSING_LEAD_TIME,'
552 ||' FULL_LEAD_TIME,'
553 ||' CUMULATIVE_TOTAL_LEAD_TIME,'
554 ||' CUM_MANUFACTURING_LEAD_TIME,'
555 ||' UOM_CODE,'
556 ||' BUILT_IN_WIP_FLAG,'
557 ||' PURCHASING_ENABLED_FLAG,'
558 ||' PLANNING_MAKE_BUY_CODE,'
559 ||' STANDARD_COST,'
560 ||' CARRYING_COST,'
561 ||' MRP_CALCULATE_ATP_FLAG,'
562 ||' END_ASSEMBLY_PEGGING_FLAG,'
563 ||' ENGINEERING_ITEM_FLAG,'
564 ||' INVENTORY_ITEM_FLAG,'
565 ||' WIP_SUPPLY_TYPE,'
566 ||' MRP_SAFETY_STOCK_CODE,'
567 ||' MRP_SAFETY_STOCK_PERCENT,'
568 ||' SAFETY_STOCK_BUCKET_DAYS,'
569 ||' ACCEPTABLE_EARLY_DELIVERY,'
570 ||' BUYER_NAME,'
571 ||' PLANNER_CODE,'
572 ||' PLANNING_EXCEPTION_SET,'
573 ||' EXCESS_QUANTITY,'
574 ||' EXCEPTION_SHORTAGE_DAYS,'
575 ||' EXCEPTION_EXCESS_DAYS,'
576 ||' EXCEPTION_OVERPROMISED_DAYS,'
577 ||' REPETITIVE_VARIANCE_DAYS,'
578 ||' BASE_ITEM_ID,'
579 ||' BOM_ITEM_TYPE,'
580 ||' ATO_FORECAST_CONTROL,'
581 ||' EFFECTIVITY_CONTROL,'
582 ||' INVENTORY_PLANNING_CODE,'
583 ||' UNIT_WEIGHT,'
584 ||' UNIT_VOLUME,'
585 ||' WEIGHT_UOM,'
586 ||' VOLUME_UOM,'
587 ||' PRODUCT_FAMILY_ID,'
588 ||' RELEASE_TIME_FENCE_CODE,'
589 ||' RELEASE_TIME_FENCE_DAYS,'
590 ||' ATP_RULE_ID,'
591 ||' ORDER_COST,'
592 ||' ATP_COMPONENTS_FLAG,'
593 ||' REPETITIVE_TYPE,'
594 ||' ORGANIZATION_CODE,'
595 ||' INVENTORY_TYPE,'
596 ||' IN_SOURCE_PLAN,'
597 ||' ATP_FLAG,'
598 ||' REVISION_QTY_CONTROL_CODE,'
599 ||' EXPENSE_ACCOUNT,'
600 ||' INVENTORY_ASSET_FLAG,'
601 ||' ACCEPTABLE_RATE_DECREASE,'
602 ||' ACCEPTABLE_RATE_INCREASE,'
603 ||' BUYER_ID,'
604 ||' SOURCE_ORG_ID,'
605 ||' DMD_LATENESS_COST,'
606 ||' SUPPLIER_CAP_OVERUTIL_COST,'
607 ||' MATERIAL_COST,'
608 ||' RESOURCE_COST,'
609 ||' AVERAGE_DISCOUNT,'
610 ||' DELETED_FLAG,'
611 ||' PIP_FLAG,'
612 ||' REPLENISH_TO_ORDER_FLAG,' /* temp start */
613 ||' PICK_COMPONENTS_FLAG,'
614 ||' YIELD_CONV_FACTOR,'
615 ||' CREATE_SUPPLY_FLAG,'
616 ||' SUBSTITUTION_WINDOW,'
617 ||' DESCRIPTION,'
618 ||' LIST_PRICE,'
619 ||' ITEM_NAME,'
620 ||' REDUCE_MPS,'
621 ||' CRITICAL_COMPONENT_FLAG,'
625 ||' VMI_MAXIMUM_DAYS,'
622 ||' VMI_MINIMUM_UNITS,'
623 ||' VMI_MINIMUM_DAYS,'
624 ||' VMI_MAXIMUM_UNITS,'
626 ||' VMI_FIXED_ORDER_QUANTITY,'
627 ||' SO_AUTHORIZATION_FLAG,'
628 ||' CONSIGNED_FLAG,'
629 ||' ASN_AUTOEXPIRE_FLAG,'
630 ||' VMI_FORECAST_TYPE,'
631 ||' FORECAST_HORIZON,'
632 ||' BUDGET_CONSTRAINED,'
633 ||' DAYS_TGT_INV_SUPPLY,'
634 ||' DAYS_TGT_INV_WINDOW,'
635 ||' DAYS_MAX_INV_SUPPLY,'
636 ||' DAYS_MAX_INV_WINDOW,'
637 ||' DRP_PLANNED,'
638 ||' CONTINOUS_TRANSFER,'
639 ||' CONVERGENCE,'
640 ||' DIVERGENCE,'
641 ||' EAM_ITEM_TYPE,' /* ds change */
642 ||' ITEM_CREATION_DATE,'
643 ||' SHORTAGE_TYPE,'
644 ||' EXCESS_TYPE,'
645 ||' PLANNING_TIME_FENCE_CODE,'
646 ||' REPAIR_LEAD_TIME,'
647 ||' PREPOSITION_POINT,'
648 ||' REPAIR_YIELD,'
649 ||' REPAIR_PROGRAM,'
650 ||' SOURCE_TYPE,'
651 ||' REFRESH_ID,'
652 ||' SR_INSTANCE_ID)'
653 ||' select'
654 ||' x.ORGANIZATION_ID,'
655 ||' x.INVENTORY_ITEM_ID,'
656 ||' x.LOTS_EXPIRATION,'
657 ||' x.LOT_CONTROL_CODE,'
658 ||' x.SHRINKAGE_RATE,'
659 ||' x.FIXED_DAYS_SUPPLY,'
660 ||' x.FIXED_ORDER_QUANTITY,'
661 ||' x.FIXED_LOT_MULTIPLIER,'
662 ||' x.MINIMUM_ORDER_QUANTITY,'
663 ||' x.MAXIMUM_ORDER_QUANTITY,'
664 ||' x.ROUNDING_CONTROL_TYPE,'
665 ||' x.PLANNING_TIME_FENCE_DAYS,'
666 ||' x.DEMAND_TIME_FENCE_DAYS,'
667 ||' x.ABC_CLASS_ID,'
668 ||' x.ABC_CLASS_NAME,'
669 ||' x.CATEGORY_ID,'
670 ||' x.CATEGORY_NAME,'
671 ||' x.MRP_PLANNING_CODE,'
672 ||' x.FIXED_LEAD_TIME,'
673 ||' x.VARIABLE_LEAD_TIME,'
674 ||' x.PREPROCESSING_LEAD_TIME,'
675 ||' x.POSTPROCESSING_LEAD_TIME,'
676 ||' x.FULL_LEAD_TIME,'
677 ||' x.CUMULATIVE_TOTAL_LEAD_TIME,'
678 ||' x.CUM_MANUFACTURING_LEAD_TIME,'
679 ||' x.UOM_CODE,'
680 ||' x.BUILT_IN_WIP_FLAG,'
681 ||' x.PURCHASING_ENABLED_FLAG,'
682 ||' x.PLANNING_MAKE_BUY_CODE,'
683 ||' x.ITEM_COST,'
684 ||' x.CARRYING_COST,'
685 ||' x.MRP_CALCULATE_ATP_FLAG,'
686 ||' x.END_ASSEMBLY_PEGGING_FLAG,'
687 ||' x.ENG_ITEM_FLAG,'
688 ||' x.INVENTORY_ITEM_FLAG,'
689 ||' x.WIP_SUPPLY_TYPE,'
690 ||' x.MRP_SAFETY_STOCK_CODE,'
691 ||' x.MRP_SAFETY_STOCK_PERCENT,'
692 ||' x.SAFETY_STOCK_BUCKET_DAYS,'
693 ||' x.ACCEPTABLE_EARLY_DELIVERY,'
694 ||' x.BUYER_NAME,'
695 ||' x.PLANNER_CODE,'
696 ||' x.PLANNING_EXCEPTION_SET,'
697 ||' x.EXCESS_QUANTITY,'
698 ||' x.EXCEPTION_SHORTAGE_DAYS,'
699 ||' x.EXCEPTION_EXCESS_DAYS,'
700 ||' x.EXCEPTION_OVERPROMISED_DAYS,'
701 ||' x.REPETITIVE_VARIANCE_DAYS,'
702 ||' x.BASE_ITEM_ID,'
703 ||' x.BOM_ITEM_TYPE,'
704 ||' x.ATO_FORECAST_CONTROL,'
705 ||' x.EFFECTIVITY_CONTROL,'
706 ||' x.INVENTORY_PLANNING_CODE,'
707 ||' x.UNIT_WEIGHT,'
708 ||' x.UNIT_VOLUME,'
709 ||' x.WEIGHT_UOM,'
710 ||' x.VOLUME_UOM,'
711 ||' x.PRODUCT_FAMILY_ID,'
712 ||' x.RELEASE_TIME_FENCE_CODE,'
713 ||' x.RELEASE_TIME_FENCE_DAYS,'
714 ||' x.ATP_RULE_ID,'
715 ||' x.ORDER_COST,'
716 ||' x.ATP_COMPONENTS_FLAG,'
717 ||' x.REPETITIVE_TYPE,'
718 ||' :V_ICODE||x.ORGANIZATION_CODE,'
719 ||' x.INVENTORY_TYPE,'
720 ||' 2,'
721 ||' x.ATP_FLAG,'
722 ||' x.REVISION_QTY_CONTROL_CODE,'
723 ||' x.EXPENSE_ACCOUNT,'
724 ||' x.INVENTORY_ASSET_FLAG,'
725 ||' x.ACCEPTABLE_RATE_DECREASE,'
726 ||' x.ACCEPTABLE_RATE_INCREASE,'
727 ||' x.BUYER_ID,'
728 ||' x.SOURCE_ORG_ID,'
729 ||' TO_NUMBER(DECODE( :v_mso_item_dmd_penalty,'
730 ||' 1, x.Attribute1,'
731 ||' 2, x.Attribute2,'
732 ||' 3, x.Attribute3,'
733 ||' 4, x.Attribute4,'
734 ||' 5, x.Attribute5,'
735 ||' 6, x.Attribute6,'
736 ||' 7, x.Attribute7,'
737 ||' 8, x.Attribute8,'
738 ||' 9, x.Attribute9,'
739 ||' 10, x.Attribute10,'
740 ||' 11, x.Attribute11,'
741 ||' 12, x.Attribute12,'
742 ||' 13, x.Attribute13,'
743 ||' 14, x.Attribute14,'
744 ||' 15, x.Attribute15)),'
745 ||' TO_NUMBER(DECODE( :v_mso_item_cap_penalty,'
746 ||' 1, x.Attribute1,'
747 ||' 2, x.Attribute2,'
748 ||' 3, x.Attribute3,'
749 ||' 4, x.Attribute4,'
750 ||' 5, x.Attribute5,'
751 ||' 6, x.Attribute6,'
752 ||' 7, x.Attribute7,'
753 ||' 8, x.Attribute8,'
754 ||' 9, x.Attribute9,'
755 ||' 10, x.Attribute10,'
756 ||' 11, x.Attribute11,'
757 ||' 12, x.Attribute12,'
758 ||' 13, x.Attribute13,'
759 ||' 14, x.Attribute14,'
760 ||' 15, x.Attribute15)),'
761 ||' x.MATERIAL_COST,'
762 ||' x.RESOURCE_COST,'
763 ||' :v_mrp_bis_av_discount,'
764 ||' 2,'
765 ||' DECODE(x.PLANNED_INVENTORY_POINT,1,1,2) ,'
766 || v_temp_sql
767 ||' x.SOURCE_TYPE,'
768 ||' :v_refresh_id,'
769 ||' :v_instance_id'
770 || lv_view_name_stmt
771 ||' WHERE x.ORGANIZATION_ID'||lv_in_org_str;
772
773
774
778 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'debug: v_temp_sql ='||v_temp_sql);
775 IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
776
777 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Debug : view sql stmt = ' || v_sql_stmt);
779 EXECUTE IMMEDIATE v_sql_stmt
780 USING MSC_CL_PULL.V_ICODE,
781 MSC_CL_PULL.v_mso_item_dmd_penalty,
782 MSC_CL_PULL.v_mso_item_cap_penalty,
783 MSC_CL_PULL.v_mrp_bis_av_discount,
784 MSC_CL_PULL.v_refresh_id,
785 MSC_CL_PULL.v_instance_id;
786
787 ELSE -- net change
788
789 v_sql_stmt := v_sql_stmt ||' AND x.RN1> :v_lrn ';
790
791 EXECUTE IMMEDIATE v_sql_stmt
792 USING MSC_CL_PULL.V_ICODE,
793 MSC_CL_PULL.v_mso_item_dmd_penalty,
794 MSC_CL_PULL.v_mso_item_cap_penalty,
795 MSC_CL_PULL.v_mrp_bis_av_discount,
796 MSC_CL_PULL.v_refresh_id,
797 MSC_CL_PULL.v_instance_id,
798 MSC_CL_PULL.v_lrn;
799
800 END IF;
801
802 COMMIT;
803
804 END IF; -- MSC_CL_PULL.ITEM_ENABLED
805
806 END LOAD_ITEM;
807
808
809 --==================================================================
810
811 PROCEDURE LOAD_SUPPLIER_CAPACITY IS
812 lv_last_asl_collection_date DATE ;
813 BEGIN
814
815
816 IF MSC_CL_PULL.SUPPLIER_CAP_ENABLED= MSC_UTIL.ASL_YES or MSC_CL_PULL.SUPPLIER_CAP_ENABLED=MSC_UTIL.ASL_YES_RETAIN_CP THEN
817
818 MSC_CL_PULL.v_table_name:= 'MSC_ST_ITEM_SUPPLIERS';
819
820 /*ASL */
821 IF MSC_CL_PULL.v_lrnn = -1 THEN
822 MSC_CL_PULL.v_view_name := 'MRP_AP_PO_SUPPLIERS_V';
823 ELSIF (MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115 and MSC_CL_PULL.v_lrnn <>-1) THEN -- incremental
824 MSC_CL_PULL.v_view_name := 'MRP_AN_PO_SUPPLIERS_V';
825 v_sql_stmt := 'Select min (nvl(LAST_SUCC_ASL_REF_TIME,SYSDATE-365000))'
826 ||' From msc_instance_orgs '
827 ||' Where sr_instance_id = ' || MSC_CL_PULL.v_instance_id
828 ||' And organization_id '|| MSC_UTIL.v_in_org_str;
829
830 -- MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'the sql statement is ' || v_sql_stmt);
831
832 EXECUTE IMMEDIATE v_sql_stmt into lv_last_asl_collection_date;
833
834 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'the sql statement is ' || v_sql_stmt);
835 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'last successful ASL Collection refresh time is '||lv_last_asl_collection_date);
836 END IF ;
837 /*ASL*/
838
839 /* Added this to collect Item Price information for a supplier
840 from 11i/110 source */
841 Begin
842 Select decode(MSC_CL_PULL.v_apps_ver,MSC_UTIL.G_APPS107,' NULL,', ' x.ITEM_PRICE,')
843 into v_temp_sql
844 from dual;
845 End;
846
847 /* Added this code for VMI changes */
848 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
849 v_temp_sql1 := ' x.ENABLE_VMI_FLAG, x.VMI_MIN_QTY, x.VMI_MAX_QTY, x.ENABLE_VMI_AUTO_REPLENISH_FLAG, x.VMI_REPLENISHMENT_APPROVAL,'
850 || ' x.REPLENISHMENT_METHOD,x.MIN_MINMAX_DAYS,x.MAX_MINMAX_DAYS,x.FORECAST_HORIZON,x.FIXED_ORDER_QUANTITY, ';
851
852 ELSE
853 v_temp_sql1 := 'NULL, NULL, NULL, NULL, NULL,NULL, NULL, NULL, NULL, NULL,NULL,';
854 END IF;
855
856
857 IF MSC_CL_PULL.v_lrnn =-1 THEN -- complete refresh
858
859 v_sql_stmt:=
860 ' insert into MSC_ST_ITEM_SUPPLIERS'
861 ||' ( INVENTORY_ITEM_ID,'
862 ||' ORGANIZATION_ID,'
863 ||' USING_ORGANIZATION_ID,'
864 ||' ASL_ID,'
865 ||' PROCESSING_LEAD_TIME,'
866 ||' MINIMUM_ORDER_QUANTITY,'
867 ||' FIXED_LOT_MULTIPLE,'
868 ||' DELIVERY_CALENDAR_CODE,'
869 ||' SUPPLIER_CAP_OVER_UTIL_COST,'
870 ||' PURCHASING_UNIT_OF_MEASURE,'
871 ||' SUPPLIER_ID,'
872 ||' SUPPLIER_SITE_ID,'
873 ||' ITEM_PRICE,'
874 ||' VMI_FLAG, '
875 ||' MIN_MINMAX_QUANTITY, '
876 ||' MAX_MINMAX_QUANTITY, '
877 ||' ENABLE_VMI_AUTO_REPLENISH_FLAG, '
878 ||' VMI_REPLENISHMENT_APPROVAL,'
879 ||' REPLENISHMENT_METHOD,'
880 ||' MIN_MINMAX_DAYS,'
881 ||' MAX_MINMAX_DAYS,'
882 ||' FORECAST_HORIZON,'
883 ||' FIXED_ORDER_QUANTITY,'
884 ||' SR_INSTANCE_ID2,'
885 ||' DELETED_FLAG,'
886 ||' REFRESH_ID,'
887 /* SCE Change start */
888 /* Get partner_item_name */
889 ||' SUPPLIER_ITEM_NAME,'
890 /* SCE Change end */
891 ||' SR_INSTANCE_ID)'
892 ||' select'
893 ||' x.INVENTORY_ITEM_ID,'
894 ||' x.ORGANIZATION_ID,'
895 ||' x.USING_ORGANIZATION_ID,'
896 ||' x.ASL_ID,'
897 ||' x.PROCESSING_LEAD_TIME,'
898 ||' x.MINIMUM_ORDER_QUANTITY,'
899 ||' x.FIXED_LOT_MULTIPLE,'
900 ||' DECODE( x.DELIVERY_CALENDAR_CODE,'
901 ||' NULL,NULL, :V_ICODE||x.DELIVERY_CALENDAR_CODE),'
902 ||' TO_NUMBER(DECODE( :v_mso_sup_cap_penalty,'
903 ||' 1, x.Attribute1,'
904 ||' 2, x.Attribute2,'
905 ||' 3, x.Attribute3,'
909 ||' 7, x.Attribute7,'
906 ||' 4, x.Attribute4,'
907 ||' 5, x.Attribute5,'
908 ||' 6, x.Attribute6,'
910 ||' 8, x.Attribute8,'
911 ||' 9, x.Attribute9,'
912 ||' 10, x.Attribute10,'
913 ||' 11, x.Attribute11,'
914 ||' 12, x.Attribute12,'
915 ||' 13, x.Attribute13,'
916 ||' 14, x.Attribute14,'
917 ||' 15, x.Attribute15)),'
918 ||' x.PURCHASING_UNIT_OF_MEASURE,'
919 ||' x.VENDOR_ID,'
920 ||' x.VENDOR_SITE_ID,'
921 || v_temp_sql
922 || v_temp_sql1
923 ||' :v_instance_id,'
924 ||' 2,'
925 ||' :v_refresh_id,'
926 /* SCE Change start */
927 /* Get partner_item_name */
928 ||' x.PRIMARY_VENDOR_ITEM,'
929 /* SCE Change end */
930 ||' :v_instance_id'
931 ||' from MRP_AP_PO_SUPPLIERS_V'||MSC_CL_PULL.v_dblink||' x'
932 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
933
934 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE,
935 MSC_CL_PULL.v_mso_sup_cap_penalty,
936 MSC_CL_PULL.v_instance_id,
937 MSC_CL_PULL.v_refresh_id,
938 MSC_CL_PULL.v_instance_id;
939
940 ELSIF (MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115 and MSC_CL_PULL.v_lrnn <>-1) THEN -- incremental (ASL net change )
941
942 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'GLOBAL ASL net change ');
943 v_sql_stmt:=
944 ' insert into MSC_ST_ITEM_SUPPLIERS'
945 ||' ( INVENTORY_ITEM_ID,'
946 ||' USING_ORGANIZATION_ID,'
947 ||' ASL_ID,'
948 ||' PROCESSING_LEAD_TIME,'
949 ||' MINIMUM_ORDER_QUANTITY,'
950 ||' FIXED_LOT_MULTIPLE,'
951 ||' DELIVERY_CALENDAR_CODE,'
952 ||' SUPPLIER_CAP_OVER_UTIL_COST,'
953 ||' PURCHASING_UNIT_OF_MEASURE,'
954 ||' SUPPLIER_ID,'
955 ||' SUPPLIER_SITE_ID,'
956 ||' ITEM_PRICE,'
957 ||' VMI_FLAG, '
958 ||' MIN_MINMAX_QUANTITY, '
959 ||' MAX_MINMAX_QUANTITY, '
960 ||' ENABLE_VMI_AUTO_REPLENISH_FLAG, '
961 ||' VMI_REPLENISHMENT_APPROVAL,'
962 ||' REPLENISHMENT_METHOD,'
963 ||' MIN_MINMAX_DAYS,'
964 ||' MAX_MINMAX_DAYS,'
965 ||' FORECAST_HORIZON,'
966 ||' FIXED_ORDER_QUANTITY,'
967 ||' SR_INSTANCE_ID2,'
968 ||' DELETED_FLAG,'
969 ||' ASL_ATTRIBUTE_CREATION_DATE,'
970 ||' REFRESH_ID,'
971 /* SCE Change start */
972 /* Get partner_item_name */
973 ||' SUPPLIER_ITEM_NAME,'
974 /* SCE Change end */
975 ||' SR_INSTANCE_ID)'
976 ||' select'
977 ||' x.ITEM_ID,'
978 ||' x.USING_ORGANIZATION_ID,'
979 ||' x.ASL_ID,'
980 ||' x.PROCESSING_LEAD_TIME,'
981 ||' x.MINIMUM_ORDER_QUANTITY,'
982 ||' x.FIXED_LOT_MULTIPLE,'
983 ||' DECODE( x.DELIVERY_CALENDAR_CODE,'
984 ||' NULL,NULL, :V_ICODE||x.DELIVERY_CALENDAR_CODE),'
985 ||' TO_NUMBER(DECODE( :v_mso_sup_cap_penalty,'
986 ||' 1, x.Attribute1,'
987 ||' 2, x.Attribute2,'
988 ||' 3, x.Attribute3,'
989 ||' 4, x.Attribute4,'
990 ||' 5, x.Attribute5,'
991 ||' 6, x.Attribute6,'
992 ||' 7, x.Attribute7,'
993 ||' 8, x.Attribute8,'
994 ||' 9, x.Attribute9,'
995 ||' 10, x.Attribute10,'
996 ||' 11, x.Attribute11,'
997 ||' 12, x.Attribute12,'
998 ||' 13, x.Attribute13,'
999 ||' 14, x.Attribute14,'
1000 ||' 15, x.Attribute15)),'
1001 ||' x.PURCHASING_UNIT_OF_MEASURE,'
1002 ||' x.VENDOR_ID,'
1003 ||' x.VENDOR_SITE_ID,'
1004 || v_temp_sql
1005 || v_temp_sql1
1006 ||' :v_instance_id,'
1007 ||' Decode (x.disable_flag,''N'', 2,''Y'', 1,2),'
1008 ||' x.date3,'
1009 ||' :v_refresh_id,'
1010 /* SCE Change start */
1011 /* Get partner_item_name */
1012 ||' x.PRIMARY_VENDOR_ITEM,'
1013 /* SCE Change end */
1014 ||' :v_instance_id'
1015 ||' from MRP_AN_PO_GLOBAL_ASL_V '||MSC_CL_PULL.v_dblink||' x'
1016 ||' WHERE (x.USING_ORGANIZATION_ID = -1 or x.USING_ORGANIZATION_ID ' || MSC_UTIL.v_in_org_str ||')'
1017 ||' AND (x.DATE1 > :lv_last_asl_collection_date or x.DATE2 > :lv_last_asl_collection_date )';
1018
1019 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'the sql statement is ' || v_sql_stmt);
1020
1021
1022 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE,
1023 MSC_CL_PULL.v_mso_sup_cap_penalty,
1024 MSC_CL_PULL.v_instance_id,
1025 MSC_CL_PULL.v_refresh_id,
1026 MSC_CL_PULL.v_instance_id,
1027 lv_last_asl_collection_date,
1028 lv_last_asl_collection_date;
1029
1030 -- MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'GLOBAL ASL ROW COUNT IS ' || SQL%ROWCOUNT);
1031
1032
1033 END IF ; --end global ASL
1034
1035
1039 only global ASLs are in this view and the local ASLs are in
1036 COMMIT;
1037
1038 /* 3019053 - Separated the view defn. of MRP_AP_PO_SUPPLIERS_V into 2 so that
1040 MRP_AP_PO_LOCAL_ASL_V. Hence adding another insert stmt. to insert local ASLs.
1041 */
1042
1043 IF (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 ) THEN
1044
1045 IF MSC_CL_PULL.v_lrnn =-1 THEN -- COMPLETE REFRESH
1046 v_sql_stmt:=
1047 ' insert into MSC_ST_ITEM_SUPPLIERS'
1048 ||' ( INVENTORY_ITEM_ID,'
1049 ||' ORGANIZATION_ID,'
1050 ||' USING_ORGANIZATION_ID,'
1051 ||' ASL_ID,'
1052 ||' PROCESSING_LEAD_TIME,'
1053 ||' MINIMUM_ORDER_QUANTITY,'
1054 ||' FIXED_LOT_MULTIPLE,'
1055 ||' DELIVERY_CALENDAR_CODE,'
1056 ||' SUPPLIER_CAP_OVER_UTIL_COST,'
1057 ||' PURCHASING_UNIT_OF_MEASURE,'
1058 ||' SUPPLIER_ID,'
1059 ||' SUPPLIER_SITE_ID,'
1060 ||' ITEM_PRICE,'
1061 ||' VMI_FLAG, '
1062 ||' MIN_MINMAX_QUANTITY, '
1063 ||' MAX_MINMAX_QUANTITY, '
1064 ||' ENABLE_VMI_AUTO_REPLENISH_FLAG, '
1065 ||' VMI_REPLENISHMENT_APPROVAL,'
1066 ||' REPLENISHMENT_METHOD,'
1067 ||' MIN_MINMAX_DAYS,'
1068 ||' MAX_MINMAX_DAYS,'
1069 ||' FORECAST_HORIZON,'
1070 ||' FIXED_ORDER_QUANTITY,'
1071 ||' SR_INSTANCE_ID2,'
1072 ||' DELETED_FLAG,'
1073 ||' REFRESH_ID,'
1074 /* SCE Change start */
1075 /* Get partner_item_name */
1076 ||' SUPPLIER_ITEM_NAME,'
1077 /* SCE Change end */
1078 ||' SR_INSTANCE_ID)'
1079 ||' select'
1080 ||' x.INVENTORY_ITEM_ID,'
1081 ||' x.ORGANIZATION_ID,'
1082 ||' x.USING_ORGANIZATION_ID,'
1083 ||' x.ASL_ID,'
1084 ||' x.PROCESSING_LEAD_TIME,'
1085 ||' x.MINIMUM_ORDER_QUANTITY,'
1086 ||' x.FIXED_LOT_MULTIPLE,'
1087 ||' DECODE( x.DELIVERY_CALENDAR_CODE,'
1088 ||' NULL,NULL, :V_ICODE||x.DELIVERY_CALENDAR_CODE),'
1089 ||' TO_NUMBER(DECODE( :v_mso_sup_cap_penalty,'
1090 ||' 1, x.Attribute1,'
1091 ||' 2, x.Attribute2,'
1092 ||' 3, x.Attribute3,'
1093 ||' 4, x.Attribute4,'
1094 ||' 5, x.Attribute5,'
1095 ||' 6, x.Attribute6,'
1096 ||' 7, x.Attribute7,'
1097 ||' 8, x.Attribute8,'
1098 ||' 9, x.Attribute9,'
1099 ||' 10, x.Attribute10,'
1100 ||' 11, x.Attribute11,'
1101 ||' 12, x.Attribute12,'
1102 ||' 13, x.Attribute13,'
1103 ||' 14, x.Attribute14,'
1104 ||' 15, x.Attribute15)),'
1105 ||' x.PURCHASING_UNIT_OF_MEASURE,'
1106 ||' x.VENDOR_ID,'
1107 ||' x.VENDOR_SITE_ID,'
1108 || v_temp_sql
1109 || v_temp_sql1
1110 ||' :v_instance_id,'
1111 ||' 2,'
1112 ||' :v_refresh_id,'
1113 /* SCE Change start */
1114 /* Get partner_item_name */
1115 ||' x.PRIMARY_VENDOR_ITEM,'
1116 /* SCE Change end */
1117 ||' :v_instance_id'
1118 ||' from MRP_AP_PO_LOCAL_ASL_V'||MSC_CL_PULL.v_dblink||' x'
1119 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
1120
1121 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Collecting from MRP_AP_PO_LOCAL_ASL_V');
1122
1123 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE,
1124 MSC_CL_PULL.v_mso_sup_cap_penalty,
1125 MSC_CL_PULL.v_instance_id,
1126 MSC_CL_PULL.v_refresh_id,
1127 MSC_CL_PULL.v_instance_id;
1128 COMMIT;
1129 ELSE -- LOCAL ASL NET CHANGE
1130 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'LOCAL ASL net change ');
1131 v_sql_stmt:=
1132 ' insert into MSC_ST_ITEM_SUPPLIERS'
1133 ||' ( INVENTORY_ITEM_ID,'
1134 ||' USING_ORGANIZATION_ID,'
1135 ||' ASL_ID,'
1136 ||' PROCESSING_LEAD_TIME,'
1137 ||' MINIMUM_ORDER_QUANTITY,'
1138 ||' FIXED_LOT_MULTIPLE,'
1139 ||' DELIVERY_CALENDAR_CODE,'
1140 ||' SUPPLIER_CAP_OVER_UTIL_COST,'
1141 ||' PURCHASING_UNIT_OF_MEASURE,'
1142 ||' SUPPLIER_ID,'
1143 ||' SUPPLIER_SITE_ID,'
1144 ||' ITEM_PRICE,'
1145 ||' VMI_FLAG, '
1146 ||' MIN_MINMAX_QUANTITY, '
1147 ||' MAX_MINMAX_QUANTITY, '
1148 ||' ENABLE_VMI_AUTO_REPLENISH_FLAG, '
1149 ||' VMI_REPLENISHMENT_APPROVAL,'
1150 ||' REPLENISHMENT_METHOD,'
1151 ||' MIN_MINMAX_DAYS,'
1152 ||' MAX_MINMAX_DAYS,'
1153 ||' FORECAST_HORIZON,'
1154 ||' FIXED_ORDER_QUANTITY,'
1155 ||' SR_INSTANCE_ID2,'
1156 ||' DELETED_FLAG,'
1157 ||' ASL_ATTRIBUTE_CREATION_DATE,'
1158 ||' REFRESH_ID,'
1159 /* SCE Change start */
1160 /* Get partner_item_name */
1161 ||' SUPPLIER_ITEM_NAME,'
1162 /* SCE Change end */
1163 ||' SR_INSTANCE_ID)'
1164 ||' select'
1165 ||' x.ITEM_ID,'
1166 ||' x.USING_ORGANIZATION_ID,'
1167 ||' x.ASL_ID,'
1168 ||' x.PROCESSING_LEAD_TIME,'
1169 ||' x.MINIMUM_ORDER_QUANTITY,'
1170 ||' x.FIXED_LOT_MULTIPLE,'
1171 ||' DECODE( x.DELIVERY_CALENDAR_CODE,'
1175 ||' 2, x.Attribute2,'
1172 ||' NULL,NULL, :V_ICODE||x.DELIVERY_CALENDAR_CODE),'
1173 ||' TO_NUMBER(DECODE( :v_mso_sup_cap_penalty,'
1174 ||' 1, x.Attribute1,'
1176 ||' 3, x.Attribute3,'
1177 ||' 4, x.Attribute4,'
1178 ||' 5, x.Attribute5,'
1179 ||' 6, x.Attribute6,'
1180 ||' 7, x.Attribute7,'
1181 ||' 8, x.Attribute8,'
1182 ||' 9, x.Attribute9,'
1183 ||' 10, x.Attribute10,'
1184 ||' 11, x.Attribute11,'
1185 ||' 12, x.Attribute12,'
1186 ||' 13, x.Attribute13,'
1187 ||' 14, x.Attribute14,'
1188 ||' 15, x.Attribute15)),'
1189 ||' x.PURCHASING_UNIT_OF_MEASURE,'
1190 ||' x.VENDOR_ID,'
1191 ||' x.VENDOR_SITE_ID,'
1192 || v_temp_sql
1193 || v_temp_sql1
1194 ||' :v_instance_id,'
1195 ||' Decode (x.disable_flag,''N'', 2,''Y'', 1,2),'
1196 ||' x.date3,'
1197 ||' :v_refresh_id,'
1198 /* SCE Change start */
1199 /* Get partner_item_name */
1200 ||' x.PRIMARY_VENDOR_ITEM,'
1201 /* SCE Change end */
1202 ||' :v_instance_id'
1203 ||' from MRP_AN_PO_LOCAL_ASL_V '||MSC_CL_PULL.v_dblink||' x'
1204 ||' WHERE x.USING_ORGANIZATION_ID ' || MSC_UTIL.v_in_org_str
1205 ||' AND (x.DATE1 > :lv_last_asl_collection_date or x.DATE2 > :lv_last_asl_collection_date )';
1206
1207 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'the sql statement lcal ASL ' || v_sql_stmt);
1208
1209 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE,
1210 MSC_CL_PULL.v_mso_sup_cap_penalty,
1211 MSC_CL_PULL.v_instance_id,
1212 MSC_CL_PULL.v_refresh_id,
1213 MSC_CL_PULL.v_instance_id,
1214 lv_last_asl_collection_date,
1215 lv_last_asl_collection_date;
1216
1217 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'LOCAL ASL ROW COUNT IS ' || SQL%ROWCOUNT);
1218 COMMIT ;
1219 END IF ;
1220 END IF; ---- MSC_CL_PULL.v_apps_ver === 115
1221
1222 END IF;
1223
1224
1225 IF (MSC_CL_PULL.SUPPLIER_CAP_ENABLED= MSC_UTIL.ASL_YES or MSC_CL_PULL.SUPPLIER_CAP_ENABLED= MSC_UTIL.ASL_YES_RETAIN_CP) AND
1226 MSC_CL_PULL.v_apps_ver<> MSC_UTIL.G_APPS107 AND
1227 MSC_CL_PULL.v_apps_ver<> MSC_UTIL.G_APPS110 THEN
1228
1229 --- LOAD NET_CHAGE for DELETE --------
1230
1231 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1232
1233 MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIER_CAPACITIES';
1234 MSC_CL_PULL.v_view_name := 'MRP_AD_SUPPLIER_CAPACITIES_V';
1235
1236 v_sql_stmt:=
1237 'Insert into MSC_ST_SUPPLIER_CAPACITIES'
1238 ||' ( INVENTORY_ITEM_ID,'
1239 ||' ORGANIZATION_ID,'
1240 ||' USING_ORGANIZATION_ID,'
1241 ||' SUPPLIER_ID,'
1242 ||' SUPPLIER_SITE_ID,'
1243 ||' FROM_DATE,'
1244 ||' DELETED_FLAG,'
1245 ||' REFRESH_ID,'
1246 ||' SR_INSTANCE_ID)'
1247 ||' select '
1248 ||' x.INVENTORY_ITEM_ID,'
1249 ||' x.ORGANIZATION_ID,'
1250 ||' x.USING_ORGANIZATION_ID,'
1251 ||' x.VENDOR_ID,'
1252 ||' x.VENDOR_SITE_ID,'
1253 ||' x.FROM_DATE- :v_dgmt,'
1254 ||' 1,'
1255 ||' :v_refresh_id,'
1256 ||' :v_instance_id'
1257 ||' from MRP_AD_SUPPLIER_CAPACITIES_V'||MSC_CL_PULL.v_dblink||' x'
1258 ||' WHERE x.RN> :v_lrn '
1259 ||' AND x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
1260
1261 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn;
1262
1263 COMMIT;
1264
1265 END IF;
1266
1267 MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIER_CAPACITIES';
1268 MSC_CL_PULL.v_view_name := 'MRP_AP_SUPPLIER_CAPACITIES_V';
1269
1270 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1271
1272 v_union_sql :=
1273 ' AND ( x.RN1 > :v_lrn )'
1274 ||' UNION '
1275 ||' select '
1276 ||' x.INVENTORY_ITEM_ID,'
1277 ||' x.ORGANIZATION_ID,'
1278 ||' x.USING_ORGANIZATION_ID,'
1279 ||' x.VENDOR_ID,'
1280 ||' x.VENDOR_SITE_ID,'
1281 ||' x.FROM_DATE- :v_dgmt,'
1282 ||' x.TO_DATE- :v_dgmt,'
1283 ||' x.CAPACITY,'
1284 ||' 2,'
1285 ||' :v_refresh_id,'
1286 ||' :v_instance_id'
1287 ||' from MRP_AP_SUPPLIER_CAPACITIES_V'||MSC_CL_PULL.v_dblink||' x'
1288 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1289 ||' AND (x.RN2 > :v_lrn)';
1290
1291 ELSE
1292
1293 v_union_sql := ' ';
1294
1295 END IF;
1296
1297
1298 v_sql_stmt:=
1299 'Insert into MSC_ST_SUPPLIER_CAPACITIES'
1300 ||' ( INVENTORY_ITEM_ID,'
1301 ||' ORGANIZATION_ID,'
1302 ||' USING_ORGANIZATION_ID,'
1303 ||' SUPPLIER_ID,'
1304 ||' SUPPLIER_SITE_ID,'
1305 ||' FROM_DATE,'
1306 ||' TO_DATE,'
1307 ||' CAPACITY,'
1308 ||' DELETED_FLAG,'
1309 ||' REFRESH_ID,'
1310 ||' SR_INSTANCE_ID)'
1311 ||' select '
1312 ||' x.INVENTORY_ITEM_ID,'
1313 ||' x.ORGANIZATION_ID,'
1314 ||' x.USING_ORGANIZATION_ID,'
1315 ||' x.VENDOR_ID,'
1316 ||' x.VENDOR_SITE_ID,'
1317 ||' x.FROM_DATE- :v_dgmt,'
1318 ||' x.TO_DATE- :v_dgmt,'
1319 ||' x.CAPACITY,'
1320 ||' 2,'
1324 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1321 ||' :v_refresh_id,'
1322 ||' :v_instance_id'
1323 ||' from MRP_AP_SUPPLIER_CAPACITIES_V'||MSC_CL_PULL.v_dblink||' x'
1325 || v_union_sql;
1326
1327
1328 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1329
1330 EXECUTE IMMEDIATE v_sql_stmt
1331 USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
1332 MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn;
1333
1334 ELSE
1335 EXECUTE IMMEDIATE v_sql_stmt
1336 USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1337 END IF;
1338
1339
1340 COMMIT;
1341
1342 IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
1343
1344 MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIER_FLEX_FENCES';
1345 MSC_CL_PULL.v_view_name := 'MRP_AP_SUPPLIER_FLEX_FENCES_V';
1346
1347 v_sql_stmt:=
1348 ' insert into MSC_ST_SUPPLIER_FLEX_FENCES'
1349 ||' ( INVENTORY_ITEM_ID,'
1350 ||' ORGANIZATION_ID,'
1351 ||' USING_ORGANIZATION_ID,'
1352 ||' SUPPLIER_ID,'
1353 ||' SUPPLIER_SITE_ID,'
1354 ||' FENCE_DAYS,'
1355 ||' TOLERANCE_PERCENTAGE,'
1356 ||' DELETED_FLAG,'
1357 ||' REFRESH_ID,'
1358 ||' SR_INSTANCE_ID)'
1359 ||' select '
1360 ||' x.INVENTORY_ITEM_ID,'
1361 ||' x.ORGANIZATION_ID,'
1362 ||' x.USING_ORGANIZATION_ID,'
1363 ||' x.VENDOR_ID,'
1364 ||' x.VENDOR_SITE_ID,'
1365 ||' x.FENCE_DAYS,'
1366 ||' x.TOLERANCE_PERCENTAGE,'
1367 ||' 2,'
1368 ||' :v_refresh_id,'
1369 ||' :v_instance_id'
1370 ||' from MRP_AP_SUPPLIER_FLEX_FENCES_V'||MSC_CL_PULL.v_dblink||' x'
1371 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str ;
1372
1373 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1374
1375 COMMIT;
1376
1377 END IF; -- complete refresh
1378
1379 END IF; -- MSC_CL_PULL.v_apps_ver
1380
1381 END LOAD_SUPPLIER_CAPACITY;
1382
1383
1384 -- ================= LOAD ITEM SUBSTITUTES ================
1385 PROCEDURE LOAD_ITEM_SUBSTITUTES IS
1386 v_condition varchar2(1000);
1387 BEGIN
1388
1389 IF MSC_CL_PULL.ITEM_SUBST_ENABLED = MSC_UTIL.SYS_YES THEN
1390
1391 -- IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
1392
1393 IF (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 AND MSC_UTIL.G_COLLECT_SRP_DATA = 'Y') THEN --For Bug 5632379 SRP Changes
1394 MSC_CL_PULL.v_table_name:= 'MSC_ST_ITEM_SUBSTITUTES';
1395 MSC_CL_PULL.v_view_name := 'MRP_AP_ITEM_SUPERSESSION_REL_V';
1396 v_sql_stmt:=
1397 ' INSERT INTO MSC_ST_ITEM_SUBSTITUTES'
1398 ||'( HIGHER_ITEM_ID,'
1399 ||' LOWER_ITEM_ID,'
1400 ||' RECIPROCAL_FLAG,'
1401 ||' RELATIONSHIP_TYPE,'
1402 ||' SUBSTITUTION_SET,'
1403 ||' PARTIAL_FULFILLMENT_FLAG,'
1404 ||' EFFECTIVE_DATE,'
1405 ||' DISABLE_DATE,'
1406 ||' SR_INSTANCE_ID,'
1407 ||' DELETED_FLAG,'
1408 ||' ORGANIZATION_ID)'
1409 ||' SELECT'
1410 ||' x.RELATED_ITEM_ID,'
1411 ||' x.INVENTORY_ITEM_ID,'
1412 ||' x.RECIPROCAL_FLAG,'
1413 ||' x.RELATIONSHIP_TYPE_ID,'
1414 ||' x.SUBSTITUTION_SET,'
1415 ||' x.PARTIAL_FULFILLMENT_FLAG,'
1416 ||' x.EFFECTIVE_DATE,'
1417 ||' x.DISABLE_DATE,'
1418 ||' :v_instance_id,'
1419 ||' 2,'
1420 ||' x.ORGANIZATION_ID'
1421 ||' FROM MRP_AP_ITEM_SUPERSESSION_REL_V'||MSC_CL_PULL.v_dblink||' x'
1422 ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn || ' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1423
1424 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id;
1425
1426 COMMIT;
1427
1428 END IF; --MSC_SRP_ENABLED THEN For Bug 5632379 SRP Changes
1429
1430
1431 MSC_CL_PULL.v_table_name:= 'MSC_ST_ITEM_SUBSTITUTES';
1432 MSC_CL_PULL.v_view_name := 'MRP_AP_ITEM_SUBSTITUTES_V';
1433
1434 v_sql_stmt:=
1435 ' INSERT INTO MSC_ST_ITEM_SUBSTITUTES'
1436 ||'( HIGHER_ITEM_ID,'
1437 ||' LOWER_ITEM_ID,'
1438 ||' RECIPROCAL_FLAG,'
1439 ||' SUBSTITUTION_SET,'
1440 ||' RELATIONSHIP_TYPE,'
1441 ||' PARTIAL_FULFILLMENT_FLAG,'
1442 ||' CUSTOMER_ID,'
1443 ||' CUSTOMER_SITE_ID ,'
1444 ||' EFFECTIVE_DATE,'
1445 ||' DISABLE_DATE,'
1446 ||' SR_INSTANCE_ID,'
1447 ||' DELETED_FLAG,'
1448 ||' ORGANIZATION_ID)'
1449 ||' SELECT'
1450 ||' x.RELATED_ITEM_ID,'
1451 ||' x.INVENTORY_ITEM_ID,'
1452 ||' x.RECIPROCAL_FLAG,'
1453 ||' x.SUBSTITUTION_SET,'
1454 ||' x.RELATIONSHIP_TYPE_ID,'
1455 ||' x.PARTIAL_FULFILLMENT_FLAG,'
1456 ||' x.CUSTOMER_ID,'
1457 ||' x.ADDRESS_ID,'
1458 ||' x.EFFECTIVE_DATE,'
1459 ||' x.DISABLE_DATE,'
1460 ||' :v_instance_id,'
1461 ||' 2,'
1462 ||' x.ORGANIZATION_ID'
1466
1463 ||' FROM MRP_AP_ITEM_SUBSTITUTES_V'||MSC_CL_PULL.v_dblink||' x'
1464 ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn
1465 ||' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1467
1468 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id;
1469
1470 COMMIT;
1471
1472 --END IF; --COMPLETE REFRESH For Bug 5702475 SRP Changes
1473
1474 IF (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS120 AND MSC_CL_PULL.v_lrnn<> -1) THEN -- incremental refresh
1475
1476 MSC_CL_PULL.v_table_name:= 'MSC_ST_ITEM_SUBSTITUTES';
1477 MSC_CL_PULL.v_view_name := 'MRP_AD_ITEM_RELATIONSHIPS_V';
1478 v_condition:=null;
1479
1480 --For Bug 5702475 SRP Changes
1481 IF (MSC_UTIL.G_COLLECT_SRP_DATA = 'Y' AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS120) THEN
1482 v_condition:= ' and x.relationship_type_id in (2,5,8,18 ) ';
1483 else
1484 v_condition:= ' and x.relationship_type_id in (2) ';
1485 end if;
1486
1487
1488 v_sql_stmt:=
1489 ' INSERT INTO MSC_ST_ITEM_SUBSTITUTES'
1490 ||'( LOWER_ITEM_ID,'
1491 ||' HIGHER_ITEM_ID,'
1492 ||' ORGANIZATION_ID,'
1493 ||' RELATIONSHIP_TYPE,'
1494 ||' REFRESH_ID,'
1495 ||' DELETED_FLAG,'
1496 ||' SR_INSTANCE_ID)'
1497 ||' SELECT'
1498 ||' x.INVENTORY_ITEM_ID,'
1499 ||' x.RELATED_ITEM_ID,'
1500 ||' x.ORGANIZATION_ID,'
1501 ||' x.RELATIONSHIP_TYPE_ID,'
1502 ||' :v_refresh_id,'
1503 ||' 1,'
1504 ||' :v_instance_id'
1505 ||' FROM MRP_AD_ITEM_RELATIONSHIPS_V'||MSC_CL_PULL.v_dblink||' x'
1506 ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn
1507 || v_condition
1508 ||' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1509
1510
1511 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1512
1513 COMMIT;
1514
1515 END IF ; --INCREMENTAL REFRESH
1516
1517 END IF; -- MSC_CL_PULL.ITEM_SUBST_ENABLED
1518
1519 END LOAD_ITEM_SUBSTITUTES;
1520
1521
1522 PROCEDURE INSERT_DUMMY_ITEMS is
1523 lv_item_name VARCHAR2(255);
1524 BEGIN
1525
1526 DELETE from MSC_ST_SYSTEM_ITEMS st_item
1527 where st_item.SR_INVENTORY_ITEM_ID in ( -1000,-1001) and
1528 st_item.sr_instance_id = MSC_CL_PULL.v_instance_id and
1529 st_item.organization_id in
1530 ( select x.organization_id
1531 FROM msc_instance_orgs x
1532 WHERE x.sr_instance_id= MSC_CL_PULL.v_instance_id
1533 and x.enabled_flag= 1
1534 and ((MSC_CL_PULL.v_org_group = MSC_UTIL.G_ALL_ORGANIZATIONS ) or (x.org_group = MSC_CL_PULL.v_org_group))
1535 );
1536 commit;
1537
1538 lv_item_name := NVL(FND_PROFILE.VALUE('MSC_EAM_NO_ACTIVITY_ITEM'),'No Activity Item');
1539 insert into MSC_ST_SYSTEM_ITEMS (
1540 ORGANIZATION_ID,
1541 SR_INVENTORY_ITEM_ID,
1542 SR_INSTANCE_ID,
1543 LOT_CONTROL_CODE,
1544 ROUNDING_CONTROL_TYPE,
1545 IN_SOURCE_PLAN,
1546 MRP_PLANNING_CODE,
1547 UOM_CODE,
1548 ATP_COMPONENTS_FLAG,
1549 BUILT_IN_WIP_FLAG,
1550 PURCHASING_ENABLED_FLAG,
1551 PLANNING_MAKE_BUY_CODE,
1552 REPETITIVE_TYPE,
1553 ENGINEERING_ITEM_FLAG,
1554 MRP_SAFETY_STOCK_CODE,
1555 EFFECTIVITY_CONTROL,
1556 INVENTORY_PLANNING_CODE,
1557 MRP_CALCULATE_ATP_FLAG,
1558 ATP_FLAG,
1559 eam_item_type,
1560 ITEM_NAME,
1561 ORGANIZATION_CODE)
1562 SELECT /*+ INDEX(MSC_INSTANCE_ORGS MSC_INSTANCE_ORGS_U1) */
1563 x.ORGANIZATION_ID ,
1564 -1000,
1565 MSC_CL_PULL.v_instance_id,
1566 1, --LOT_CONTROL_CODE 1 no control, 2 full control
1567 1, --ROUNDING_CONTROL_TYPE 1 round order qty, 2 no
1568 2, --IN_SOURCE_PLAN
1569 3, --MRP_PLANNING_CODE 3 mrp planning, 6 not planned,
1570 'Ea', --UOM_CODE
1571 'N', --ATP_COMPONENTS_FLAG N no, Y material only, R Resource, C material and resource
1572 1, --BUILD_IN_WIP_FLAG
1573 2, --PURCHASING_ENABLED_FLAG
1574 1, --PLANNING_MAKE_BUY_CODE 1 Make, 2 Buy
1575 1, --REPETITIVE_TYPE
1576 2, --ENGINEERING_ITEM_FLAG
1577 1, --MRP_SAFETY_STOCK_CODE
1578 2, --EFFECTIVITY_CONTROL
1579 1, --INVENTORY_PLANNING_CODE 6 not planned, 2 min max, 1 reorder point
1580 2, --CALCULATE_ATP
1581 2, --ATP_FLAG
1582 1, --eam_item_type
1583 lv_item_name ,
1584 org.ORGANIZATION_CODE
1585 FROM msc_instance_orgs x,
1586 MTL_PARAMETERS org
1587 WHERE sr_instance_id= MSC_CL_PULL.v_instance_id
1588 and enabled_flag= 1
1589 and org.ORGANIZATION_ID = x.ORGANIZATION_ID
1590 and ((MSC_CL_PULL.v_org_group = MSC_UTIL.G_ALL_ORGANIZATIONS ) or (org_group = MSC_CL_PULL.v_org_group));
1591
1592 commit;
1593 lv_item_name := NVL(FND_PROFILE.VALUE('MSC_WIP_NONSTD_JOB_ITEM'),'Non Standard Job Item');
1594
1595 insert into MSC_ST_SYSTEM_ITEMS (
1596 ORGANIZATION_ID,
1597 SR_INVENTORY_ITEM_ID,
1598 SR_INSTANCE_ID,
1599 LOT_CONTROL_CODE,
1600 ROUNDING_CONTROL_TYPE,
1601 IN_SOURCE_PLAN,
1602 MRP_PLANNING_CODE,
1603 UOM_CODE,
1604 ATP_COMPONENTS_FLAG,
1605 BUILT_IN_WIP_FLAG,
1606 PURCHASING_ENABLED_FLAG,
1607 PLANNING_MAKE_BUY_CODE,
1608 REPETITIVE_TYPE,
1609 ENGINEERING_ITEM_FLAG,
1610 MRP_SAFETY_STOCK_CODE,
1611 EFFECTIVITY_CONTROL,
1612 INVENTORY_PLANNING_CODE,
1613 MRP_CALCULATE_ATP_FLAG,
1614 ATP_FLAG,
1615 ITEM_NAME,
1616 ORGANIZATION_CODE)
1617 SELECT /*+ INDEX(MSC_INSTANCE_ORGS MSC_INSTANCE_ORGS_U1) */
1618 x.ORGANIZATION_ID ,
1619 -1001,
1620 MSC_CL_PULL.v_instance_id,
1621 1, --LOT_CONTROL_CODE
1622 1, --ROUNDING_CONTROL_TYPE
1623 2, --IN_SOURCE_PLAN
1624 3, --MRP_PLANNING_CODE
1625 'Ea', --UOM_CODE
1626 'N', --ATP_COMPONENTS_FLAG
1627 1, --BUILD_IN_WIP_FLAG
1628 2, --PURCHASING_ENABLED_FLAG
1629 1, --PLANNING_MAKE_BUY_CODE
1630 1, --REPETITIVE_TYPE
1631 1, --ENGINEERING_ITEM_FLAG
1632 1, --MRP_SAFETY_STOCK_CODE
1633 2, --EFFECTIVITY_CONTROL
1634 1, --INVENTORY_PLANNING_CODE
1635 2, --CALCULATE_ATP
1636 2, --ATP_FLAG
1637 lv_item_name ,
1638 org.ORGANIZATION_CODE
1639 FROM msc_instance_orgs x,
1640 MTL_PARAMETERS org
1641 WHERE x.sr_instance_id= MSC_CL_PULL.v_instance_id
1642 and x.enabled_flag= 1
1643 and org.ORGANIZATION_ID = x.ORGANIZATION_ID
1644 and ((MSC_CL_PULL.v_org_group = MSC_UTIL.G_ALL_ORGANIZATIONS ) or (x.org_group = MSC_CL_PULL.v_org_group));
1645
1646 commit;
1647
1648 END INSERT_DUMMY_ITEMS;
1649
1650 PROCEDURE INSERT_DUMMY_CATEGORIES is
1651 lv_table_name VARCHAR2(255);
1652 lv_sql_stmt VARCHAR2(5000);
1653 lv_category_set_id PLS_INTEGER;
1654 lv_category_set_name VARCHAR2(255):= 'Unspecified Items';
1655 lv_category_set_description VARCHAR2(255):= 'Category set for no activity item ';
1656 lv_category_name VARCHAR2(255) :='NEW.MISC';
1657 lv_category_id PLS_INTEGER ;
1658 lv_category_description VARCHAR2(255):='No Activity Item Category';
1659 lv_disable_date DATE;
1660 lv_summary_flag VARCHAR2(1):='N';
1661 lv_enabled_flag VARCHAR2(1):='Y';
1662 lv_start_date_active DATE ;
1663 lv_end_date_active DATE;
1664 lv_deleted_flag PLS_INTEGER := 2;
1665 BEGIN
1666
1667 delete from MSC_ST_CATEGORY_SETS st_item_category_set
1668 where st_item_category_set.sr_instance_id = MSC_CL_PULL.v_instance_id
1669 and st_item_category_set.SR_CATEGORY_SET_ID = -5000;
1670 commit;
1671 lv_category_set_name := NVL(FND_PROFILE.VALUE('MSC_NO_ACTIVITY_ITEM_CATEGORY_SET'),'Unspecified Items');
1672 BEGIN
1673 lv_table_name:= 'MRP_AP_CATEGORY_SETS_V'||MSC_CL_PULL.v_dblink;
1674 lv_sql_stmt:= 'SELECT category_set_id FROM '||lv_table_name
1675 ||' WHERE category_set_name = '''||lv_category_set_name
1676 ||''' AND nvl(language,''' || MSC_CL_PULL.v_lang || ''')=''' || MSC_CL_PULL.v_lang || '''';
1677
1678 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'debug1 - ' || lv_sql_stmt);
1679
1680 EXECUTE IMMEDIATE lv_sql_stmt INTO lv_category_set_id ;
1681 EXCEPTION
1682 WHEN NO_DATA_FOUND THEN
1683 lv_category_set_id := -5000 ;
1684
1685 END ;
1686
1687 IF lv_category_set_id = -5000 then
1688 insert into MSC_ST_CATEGORY_SETS (
1689 SR_CATEGORY_SET_ID,
1690 CATEGORY_SET_NAME,
1691 DESCRIPTION,
1692 CONTROL_LEVEL,
1693 DEFAULT_FLAG,
1694 DELETED_FLAG,
1695 SR_INSTANCE_ID)
1696 values(
1697 lv_category_set_id,
1698 lv_category_set_name,
1699 lv_category_set_description ,
1700 2,
1701 2,
1702 2,
1703 MSC_CL_PULL.v_instance_id ) ;
1704 commit ;
1705 END IF ;
1706
1707 lv_table_name:= 'MTL_PARAMETERS'||MSC_CL_PULL.v_dblink;
1708
1709
1710 DELETE from MSC_ST_ITEM_CATEGORIES st_item_category
1711 where st_item_category.INVENTORY_ITEM_ID in ( -1000,-1001) and
1712 st_item_category.SR_CATEGORY_SET_ID in (-5000)and
1713 st_item_category.SR_CATEGORY_ID in (-5001) and
1714 st_item_category.sr_instance_id = MSC_CL_PULL.v_instance_id and
1715 st_item_category.organization_id in
1716 ( select x.organization_id
1717 FROM msc_instance_orgs x
1718 WHERE x.sr_instance_id= MSC_CL_PULL.v_instance_id
1719 and x.enabled_flag= 1
1720 and ((MSC_CL_PULL.v_org_group = MSC_UTIL.G_ALL_ORGANIZATIONS ) or (x.org_group = MSC_CL_PULL.v_org_group))
1721 );
1722 commit;
1723
1724 lv_category_name := NVL(FND_PROFILE.VALUE('MSC_NO_ACTIVITY_ITEM_CATEGORY'),'NEW.MISC');
1725 BEGIN
1726 lv_table_name:= 'MRP_AP_ITEM_CATEGORIES_V'||MSC_CL_PULL.v_dblink;
1727 lv_sql_stmt:= 'SELECT category_id,description FROM ' ||lv_table_name
1728 || ' WHERE category_name = '''||lv_category_name
1729 || ''' AND nvl(language,''' || MSC_CL_PULL.v_lang || ''')=''' || MSC_CL_PULL.v_lang || '''and rownum=1';
1730
1731 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'debug2 - ' || lv_sql_stmt);
1732
1733 EXECUTE IMMEDIATE lv_sql_stmt INTO lv_category_id,lv_category_description ;
1734 EXCEPTION
1735 WHEN NO_DATA_FOUND THEN
1736 lv_category_id := -5001;
1737 END ;
1738
1739 lv_table_name :='MTL_PARAMETERS'||MSC_CL_PULL.v_dblink;
1740 lv_sql_stmt := 'insert into MSC_ST_ITEM_CATEGORIES ('
1741 ||'INVENTORY_ITEM_ID,'
1742 ||'ORGANIZATION_ID,'
1743 ||'SR_CATEGORY_SET_ID,'
1744 ||'SR_CATEGORY_ID, '
1745 ||'CATEGORY_NAME, '
1746 -- ||'DESCRIPTION, '
1747 ||'DISABLE_DATE, '
1748 ||'SUMMARY_FLAG, '
1749 ||'ENABLED_FLAG, '
1750 ||'START_DATE_ACTIVE, '
1751 ||'END_DATE_ACTIVE, '
1752 ||'DELETED_FLAG, '
1753 ||'SR_INSTANCE_ID)'
1754 ||' SELECT /*+ INDEX(MSC_INSTANCE_ORGS MSC_INSTANCE_ORGS_U1)*/ '
1755 ||' :lv_item_id ,'
1756 || 'x.ORGANIZATION_ID ,'
1757 || lv_category_set_id ||','
1758 || lv_category_id ||','
1759 ||''''|| lv_category_name||'''' ||','
1760 -- ||''''|| lv_category_description||'''' || ','
1761 || 'null ,'
1762 ||''''|| lv_summary_flag||'''' ||','
1763 ||''''|| lv_enabled_flag||'''' ||','
1764 || 'null,'
1765 || 'null,'
1766 || lv_deleted_flag ||','
1767 || MSC_CL_PULL.v_instance_id
1768 || ' FROM msc_instance_orgs x,'
1769 || lv_table_name ||' org '
1770 ||' WHERE x.sr_instance_id='|| MSC_CL_PULL.v_instance_id
1771 ||' and x.enabled_flag= 1'
1772 ||' and org.ORGANIZATION_ID = x.ORGANIZATION_ID'
1773 ||' and (('''||MSC_CL_PULL.v_org_group ||'''='''|| MSC_UTIL.G_ALL_ORGANIZATIONS||''' ) or (org_group ='''|| MSC_CL_PULL.v_org_group||'''))';
1774
1775
1776
1777 for lv_item_id in -1001..-1000 loop
1778 execute immediate lv_sql_stmt using lv_item_id ;
1779
1780 end loop;
1781
1782 update MSC_ST_ITEM_CATEGORIES set DESCRIPTION = lv_category_description
1783 where INVENTORY_ITEM_ID in (-1001,-1000)
1784 and SR_CATEGORY_SET_ID = lv_category_set_id
1785 and SR_CATEGORY_ID = lv_category_id
1786 and organization_id in
1787 ( select x.organization_id
1788 FROM msc_instance_orgs x
1789 WHERE x.sr_instance_id= MSC_CL_PULL.v_instance_id
1790 and x.enabled_flag= 1
1791 and ((MSC_CL_PULL.v_org_group = MSC_UTIL.G_ALL_ORGANIZATIONS ) or (x.org_group = MSC_CL_PULL.v_org_group))
1792 );
1793
1794 commit;
1795
1796 END INSERT_DUMMY_CATEGORIES;
1797
1798 --====================TO initialize the part condition attribute values ===========
1799
1800 /*added for bug:4765403*/
1801 PROCEDURE LOAD_ABC_CLASSES IS
1802 BEGIN
1803 MSC_CL_PULL.v_table_name:= 'MSC_ST_ABC_CLASSES';
1804 MSC_CL_PULL.v_view_name:= 'MRP_AP_ABC_CLASSES';
1805
1806 v_sql_stmt:=
1807 'insert into MSC_ST_ABC_CLASSES'
1808 ||' (ABC_CLASS_ID,'
1809 ||' ABC_CLASS_NAME,'
1810 ||' ORGANIZATION_ID,'
1811 ||' SR_ASSIGNMENT_GROUP_ID,'
1812 ||' SR_INSTANCE_ID)'
1813 ||' select '
1814 ||' x.ABC_CLASS_ID,'
1815 ||' x.ABC_CLASS_NAME,'
1816 ||' x.ORGANIZATION_ID,'
1817 ||' x.ASSIGNMENT_GROUP_ID,'
1818 ||' :v_instance_id'
1819 ||' from MRP_AP_ABC_CLASSES_V'||MSC_CL_PULL.v_dblink||' x'
1820 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
1821
1822
1823 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id;
1824
1825 COMMIT;
1826
1827 END LOAD_ABC_CLASSES;
1828
1829
1830 END MSC_CL_ITEM_PULL;