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