[Home] [Help]
PACKAGE BODY: APPS.MSC_IMPORT_UTIL
Source
1 PACKAGE BODY MSC_IMPORT_UTIL AS
2 /* $Header: MSCIMPUB.pls 120.7 2011/07/04 12:53:30 tboliset noship $ */
3
4 /*=============================================================================
5 Four global Variables
6 g_attr_name - List of attribute names
7 g_attr_val - List of attribute values for a particular record in msc_st_item_attributes
8 g_attr_type - lov type-1:Number 2: Char 6:Date
9 g_sql_stmt - If lov type is 2 then use this sql stmt to get the hidden value
10 =============================================================================*/
11
12 TYPE attr_name_list is table of varchar2(30) index by binary_integer;
13
14 TYPE attr_type_list is table of number index by binary_integer;
15
16 TYPE sql_stmt_list is table of varchar2(2000) index by binary_integer;
17
18 TYPE attr_val_list is table of varchar2(240) index by binary_integer;
19
20 TYPE imm_stg_rec is record(
21 ROWID VARCHAR2(240) ,
22 SIMULATION_SET_NAME VARCHAR2(240) ,
23 ITEM_NAME VARCHAR2(250) ,
24 ORGANIZATION_CODE VARCHAR2(7) ,
25 SR_INSTANCE_CODE VARCHAR2(3) ,
26 ZONE VARCHAR2(60) ,
27 CUSTOMER_NAME VARCHAR2(255) ,
28 CUSTOMER_SITE_NAME VARCHAR2(30) ,
29 CRITICAL_COMPONENT_FLAG VARCHAR2(80) ,
30 FULL_LEAD_TIME NUMBER ,
31 PREPROCESSING_LEAD_TIME NUMBER ,
32 POSTPROCESSING_LEAD_TIME NUMBER ,
33 FIXED_LEAD_TIME NUMBER ,
34 VARIABLE_LEAD_TIME NUMBER ,
35 FIXED_ORDER_QUANTITY NUMBER ,
36 FIXED_DAYS_SUPPLY NUMBER ,
37 SHRINKAGE_RATE NUMBER ,
38 FIXED_LOT_MULTIPLIER NUMBER ,
39 MINIMUM_ORDER_QUANTITY NUMBER ,
40 MAXIMUM_ORDER_QUANTITY NUMBER ,
41 SERVICE_LEVEL VARCHAR2(40) ,
42 CARRYING_COST NUMBER ,
43 DEMAND_TIME_FENCE_DAYS NUMBER ,
44 ATO_FORECAST_CONTROL VARCHAR2(80) ,
45 PLANNING_TIME_FENCE_DAYS NUMBER ,
46 STANDARD_COST NUMBER ,
47 PIP_FLAG VARCHAR2(80) ,
48 LIST_PRICE NUMBER ,
49 SUBSTITUTION_WINDOW NUMBER ,
50 SAFETY_STOCK_BUCKET_DAYS NUMBER ,
51 UNIT_WEIGHT NUMBER ,
52 UNIT_VOLUME NUMBER ,
53 SAFETY_STOCK_CODE VARCHAR2(80) ,
54 SAFETY_STOCK_PERCENT NUMBER ,
55 ABC_CLASS_NAME VARCHAR2(40) ,
56 MRP_PLANNING_CODE VARCHAR2(80) ,
57 DRP_PLANNED VARCHAR2(80) ,
58 DAYS_MAX_INV_SUPPLY NUMBER ,
59 DAYS_MAX_INV_WINDOW NUMBER ,
60 DAYS_TGT_INV_SUPPLY NUMBER ,
61 DAYS_TGT_INV_WINDOW NUMBER ,
62 CONTINOUS_TRANSFER VARCHAR2(80) ,
63 CONVERGENCE VARCHAR2(80) ,
64 CREATE_SUPPLY_FLAG VARCHAR2(80) ,
65 DIVERGENCE VARCHAR2(80) ,
66 PLANNING_EXCEPTION_SET VARCHAR2(10) ,
67 INVENTORY_USE_UP_DATE DATE ,
68 PLANNING_MAKE_BUY_CODE VARCHAR2(80) ,
69 WEIGHT_UOM VARCHAR2(3) ,
70 VOLUME_UOM VARCHAR2(3) ,
71 ROUNDING_CONTROL_TYPE VARCHAR2(80) ,
72 ATP_FLAG VARCHAR2(80) ,
73 ATP_COMPONENTS_FLAG VARCHAR2(80) ,
74 DEMAND_FULFILLMENT_LT VARCHAR2(40) ,
75 LOTS_EXPIRATION NUMBER ,
76 CONSIGNED_FLAG VARCHAR2(80) ,
77 LEADTIME_VARIABILITY VARCHAR2(40) ,
78 PLANNER_CODE VARCHAR2(240) ,
79 EO_FLAG VARCHAR2(80) ,
80 EXCESS_HORIZON VARCHAR2(40) ,
81 OBSOLESCENCE_DATE VARCHAR2(18) ,
82 REPAIR_LEAD_TIME VARCHAR2(40) ,
83 REPAIR_YIELD VARCHAR2(40) ,
84 REPAIR_COST VARCHAR2(40) ,
85 RELIABILITY VARCHAR2(240) ,
86 FAILURE_IMPACT VARCHAR2(240) ,
87 STANDARD_DEVIATION VARCHAR2(40) ,
88 COEFFICIENT_OF_VARIATION VARCHAR2(40) ,
89 BASIS_AVG_DAILY_DEMAND VARCHAR2(40) ,
90 FORECAST_RULE_FOR_DEMANDS VARCHAR2(30) ,
91 FORECAST_RULE_FOR_RETURNS VARCHAR2(30) ,
92 LIFE_TIME_BUY_DATE DATE ,
93 END_OF_LIFE_DATE DATE ,
94 AVG_DEMAND_BEYOND_PH NUMBER ,
95 AVG_RETURNS_BEYOND_PH NUMBER ,
96 RETURN_FORECAST_TIME_FENCE NUMBER ,
97 AVERAGE_DAILY_DEMAND NUMBER ,
98 DEFECTIVE_ITEM_COST NUMBER ,
99 STD_DEVIATION_FOR_DEMAND NUMBER ,
100 MEAN_INTER_ARRIVAL NUMBER ,
101 STD_DEVIATION_INTER_ARRIVAL NUMBER ,
102 INTERARRIVAL_DIST_METHOD VARCHAR2(240) ,
103 INTERMITTENT_DEMAND VARCHAR2(80) ,
104 MAX_USAGE_FACTOR NUMBER ,
105 MIN_REM_SHELF_LIFE_DAYS NUMBER ,
106 UNSATISFIED_DEMAND_FACTOR NUMBER ,
107 DMD_SATISFIED_PERCENT NUMBER ,
108 MIN_SUP_DEM_PERCENT NUMBER ,
109 ROP_SAFETY_STOCK NUMBER ,
110 COMPUTE_SS VARCHAR2(10) ,
111 COMPUTE_EOQ VARCHAR2(10) ,
112 ORDER_COST NUMBER
113 );
114
115
116 g_attr_name attr_name_list;
117 g_attr_val attr_val_list;
118 g_attr_type attr_type_list;
119 g_sql_stmt sql_stmt_list;
120
121
122 procedure init is
123
124 cursor c_lov_type(l_field_name varchar2) is
125 select lov_type,sql_statement from
126 msc_criteria where
127 folder_object in ('MSC_IMM_UPDATE_ATTRIBUTES',
128 'MSC_REGION_UPDATE_ATTRIBUTES',
129 'MSC_CUST_UPDATE_ATTRIBUTES',
130 'MSC_IMM_DESTINATION_ATTRIBUTES'
131 )
132 and field_name = l_field_name;
133
134 cursor c_lov_type_count(l_field_name varchar2) is
135 select count(*) from
136 msc_criteria where
137 folder_object in ('MSC_IMM_UPDATE_ATTRIBUTES',
138 'MSC_REGION_UPDATE_ATTRIBUTES',
139 'MSC_CUST_UPDATE_ATTRIBUTES',
140 'MSC_IMM_DESTINATION_ATTRIBUTES')
141 and field_name = l_field_name;
142 i number;
143 l_count number :=0;
144 begin
145
146 g_attr_name(1) := 'SIMULATION_SET_NAME';
147 g_attr_type(1) := null;
148
149 g_attr_name(2) :='ITEM_NAME';
150 g_attr_type(2) := null;
151
152 g_attr_name(3) :='ORGANIZATION_CODE';
153 g_attr_type(3) := null;
154
155 g_attr_name(4) :='SR_INSTANCE_CODE';
156 g_attr_type(4) := null;
157
158 g_attr_name(5) :='ZONE';
159 g_attr_type(5) := null;
160
161 g_attr_name(6) :='CUSTOMER_NAME';
162 g_attr_type(6) := null;
163
164 g_attr_name(7) :='CUSTOMER_SITE_NAME';
165 g_attr_type(7) := null;
166
167 g_attr_name(8) :='CRITICAL_COMPONENT_FLAG';
168 g_attr_name(9) :='FULL_LEAD_TIME';
169 g_attr_name(10) :='PREPROCESSING_LEAD_TIME';
170 g_attr_name(11) :='POSTPROCESSING_LEAD_TIME';
171 g_attr_name(12) :='FIXED_LEAD_TIME';
172 g_attr_name(13) :='VARIABLE_LEAD_TIME';
173 g_attr_name(14) :='FIXED_ORDER_QUANTITY';
174 g_attr_name(15) :='FIXED_DAYS_SUPPLY';
175 g_attr_name(16) :='SHRINKAGE_RATE';
176 g_attr_name(17) :='FIXED_LOT_MULTIPLIER';
177 g_attr_name(18) :='MINIMUM_ORDER_QUANTITY';
178 g_attr_name(19) :='MAXIMUM_ORDER_QUANTITY';
179 g_attr_name(20) :='SERVICE_LEVEL';
180 g_attr_name(21) :='CARRYING_COST';
181 g_attr_name(22) :='DEMAND_TIME_FENCE_DAYS';
182 g_attr_name(23) :='ATO_FORECAST_CONTROL';
183 g_attr_name(24) :='PLANNING_TIME_FENCE_DAYS';
184 g_attr_name(25) :='STANDARD_COST';
185 g_attr_name(26) :='PIP_FLAG';
186 g_attr_name(27) :='LIST_PRICE';
187 g_attr_name(28) :='SUBSTITUTION_WINDOW';
188 g_attr_name(29) :='SAFETY_STOCK_BUCKET_DAYS';
189 g_attr_name(30) :='UNIT_WEIGHT';
190 g_attr_name(31) :='UNIT_VOLUME';
191 g_attr_name(32) :='SAFETY_STOCK_CODE';
192 g_attr_name(33) :='SAFETY_STOCK_PERCENT';
193 g_attr_name(34) :='ABC_CLASS_NAME';
194 g_attr_name(35) :='MRP_PLANNING_CODE';
195 g_attr_name(36) :='DRP_PLANNED';
196 g_attr_name(37) :='DAYS_MAX_INV_SUPPLY';
197 g_attr_name(38) :='DAYS_MAX_INV_WINDOW';
198 g_attr_name(39) :='DAYS_TGT_INV_SUPPLY';
199 g_attr_name(40) :='DAYS_TGT_INV_WINDOW';
200 g_attr_name(41) :='CONTINOUS_TRANSFER';
201 g_attr_name(42) :='CONVERGENCE';
202 g_attr_name(43) :='CREATE_SUPPLY_FLAG';
203 g_attr_name(44) :='DIVERGENCE';
204 g_attr_name(45) :='PLANNING_EXCEPTION_SET';
205 g_attr_name(46) :='INVENTORY_USE_UP_DATE';
206 g_attr_name(47) :='PLANNING_MAKE_BUY_CODE';
207 g_attr_name(48) :='WEIGHT_UOM';
208 g_attr_name(49) :='VOLUME_UOM';
209 g_attr_name(50) :='ROUNDING_CONTROL_TYPE';
210 g_attr_name(51) :='ATP_FLAG';
211 g_attr_name(52) :='ATP_COMPONENTS_FLAG';
212 g_attr_name(53) :='DEMAND_FULFILLMENT_LT';
213 g_attr_name(54) :='LOTS_EXPIRATION';
214 g_attr_name(55) :='CONSIGNED_FLAG';
215 g_attr_name(56) :='LEADTIME_VARIABILITY';
216 g_attr_name(57) :='PLANNER_CODE';
217 g_attr_name(58) :='EO_FLAG';
218 g_attr_name(59) :='EXCESS_HORIZON';
219 g_attr_name(60) :='OBSOLESCENCE_DATE';
220 g_attr_name(61) :='REPAIR_LEAD_TIME';
221 g_attr_name(62) :='REPAIR_YIELD';
222 g_attr_name(63) :='REPAIR_COST';
223 g_attr_name(64) :='RELIABILITY';
224 g_attr_name(65) :='FAILURE_IMPACT';
225 g_attr_name(66) :='STANDARD_DEVIATION';
226 g_attr_name(67) :='COEFFICIENT_OF_VARIATION';
227 g_attr_name(68) :='BASIS_AVG_DAILY_DEMAND';
228 g_attr_name(69) :='FORECAST_RULE_FOR_DEMANDS';
229 g_attr_name(70) :='FORECAST_RULE_FOR_RETURNS';
230 g_attr_name(71) :='LIFE_TIME_BUY_DATE';
231 g_attr_name(72) :='END_OF_LIFE_DATE';
232 g_attr_name(73) :='AVG_DEMAND_BEYOND_PH';
233 g_attr_name(74) :='AVG_RETURNS_BEYOND_PH';
234 g_attr_name(75) :='RETURN_FORECAST_TIME_FENCE';
235 g_attr_name(76) :='AVERAGE_DAILY_DEMAND';
236 g_attr_name(77) :='DEFECTIVE_ITEM_COST';
237 g_attr_name(78) :='STD_DEVIATION_FOR_DEMAND';
238 g_attr_name(79) :='MEAN_INTER_ARRIVAL';
239 g_attr_name(80) :='STD_DEVIATION_INTER_ARRIVAL';
240 g_attr_name(81) :='INTERARRIVAL_DIST_METHOD';
241 g_attr_name(82) :='INTERMITTENT_DEMAND';
242 g_attr_name(83) :='MAX_USAGE_FACTOR';
243 g_attr_name(84) :='MIN_REM_SHELF_LIFE_DAYS';
244 g_attr_name(85) :='UNSATISFIED_DEMAND_FACTOR';
245 g_attr_name(86) :='DMD_SATISFIED_PERCENT';
246 g_attr_name(87) :='MIN_SUP_DEM_PERCENT';
247 g_attr_name(88) :='ROP_SAFETY_STOCK';
248 g_attr_name(89) := 'COMPUTE_SS';
249 g_attr_name(90) := 'COMPUTE_EOQ';
250 g_attr_name(91) := 'ORDER_COST';
251
252 i := 8;
253 loop
254 l_count:=0;
255 -- msc_util.msc_debug('in init i='||i);
256 open c_lov_type_count(g_attr_name(i));
257 fetch c_lov_type_count into l_count;
258 close c_lov_type_count;
259 -- msc_util.msc_debug('l_count:'||l_count);
260 if l_count=0 then
261 g_attr_type(i) := 2;
262 g_sql_stmt(i) := null;
263 else
264 open c_lov_type(g_attr_name(i));
265 fetch c_lov_type into g_attr_type(i),g_sql_stmt(i);
266 close c_lov_type;
267 end if;
268 if g_attr_name.count = i then
269 exit;
270 end if;
271 i:=i+1;
272 end loop;
273
274 end init;
275
276 procedure set_attr_val(l_imm_stg_rec imm_stg_Rec) is
277 i number :=1;
278 begin
279 g_attr_val(1) := l_imm_stg_rec.SIMULATION_SET_NAME;
280 g_attr_val(2) := l_imm_stg_rec.ITEM_NAME;
281 g_attr_val(3) := l_imm_stg_rec.ORGANIZATION_CODE;
282 g_attr_val(4) := l_imm_stg_rec.SR_INSTANCE_CODE;
283 g_attr_val(5) := l_imm_stg_rec.ZONE;
284 g_attr_val(6) := l_imm_stg_rec.CUSTOMER_NAME;
285 g_attr_val(7) := l_imm_stg_rec.CUSTOMER_SITE_NAME;
286 g_attr_val(8) := l_imm_stg_rec.CRITICAL_COMPONENT_FLAG;
287 g_attr_val(9) := l_imm_stg_rec.FULL_LEAD_TIME;
288 g_attr_val(10) := l_imm_stg_rec.PREPROCESSING_LEAD_TIME;
289 g_attr_val(11) := l_imm_stg_rec.POSTPROCESSING_LEAD_TIME;
290 g_attr_val(12) := l_imm_stg_rec.FIXED_LEAD_TIME;
291 g_attr_val(13) := l_imm_stg_rec.VARIABLE_LEAD_TIME;
292 g_attr_val(14) := l_imm_stg_rec.FIXED_ORDER_QUANTITY;
293 g_attr_val(15) := l_imm_stg_rec.FIXED_DAYS_SUPPLY;
294 g_attr_val(16) := l_imm_stg_rec.SHRINKAGE_RATE;
295 g_attr_val(17) := l_imm_stg_rec.FIXED_LOT_MULTIPLIER;
296 g_attr_val(18) := l_imm_stg_rec.MINIMUM_ORDER_QUANTITY;
297 g_attr_val(19) := l_imm_stg_rec.MAXIMUM_ORDER_QUANTITY;
298 g_attr_val(20) := l_imm_stg_rec.SERVICE_LEVEL;
299 g_attr_val(21) := l_imm_stg_rec.CARRYING_COST;
300 g_attr_val(22) := l_imm_stg_rec.DEMAND_TIME_FENCE_DAYS;
301 g_attr_val(23) := l_imm_stg_rec.ATO_FORECAST_CONTROL;
302 g_attr_val(24) := l_imm_stg_rec.PLANNING_TIME_FENCE_DAYS;
303 g_attr_val(25) := l_imm_stg_rec.STANDARD_COST;
304 g_attr_val(26) := l_imm_stg_rec.PIP_FLAG;
305 g_attr_val(27) := l_imm_stg_rec.LIST_PRICE;
306 g_attr_val(28) := l_imm_stg_rec.SUBSTITUTION_WINDOW;
307 g_attr_val(29) := l_imm_stg_rec.SAFETY_STOCK_BUCKET_DAYS;
308 g_attr_val(30) := l_imm_stg_rec.UNIT_WEIGHT;
309 g_attr_val(31) := l_imm_stg_rec.UNIT_VOLUME;
310 g_attr_val(32) := l_imm_stg_rec.SAFETY_STOCK_CODE;
311 g_attr_val(33) := l_imm_stg_rec.SAFETY_STOCK_PERCENT;
312 g_attr_val(34) := l_imm_stg_rec.ABC_CLASS_NAME;
313 g_attr_val(35) := l_imm_stg_rec.MRP_PLANNING_CODE;
314 g_attr_val(36) := l_imm_stg_rec.DRP_PLANNED;
315 g_attr_val(37) := l_imm_stg_rec.DAYS_MAX_INV_SUPPLY;
316 g_attr_val(38) := l_imm_stg_rec.DAYS_MAX_INV_WINDOW;
317 g_attr_val(39) := l_imm_stg_rec.DAYS_TGT_INV_SUPPLY;
318 g_attr_val(40) := l_imm_stg_rec.DAYS_TGT_INV_WINDOW;
319 g_attr_val(41) := l_imm_stg_rec.CONTINOUS_TRANSFER;
320 g_attr_val(42) := l_imm_stg_rec.CONVERGENCE;
321 g_attr_val(43) := l_imm_stg_rec.CREATE_SUPPLY_FLAG;
322 g_attr_val(44) := l_imm_stg_rec.DIVERGENCE;
323 g_attr_val(45) := l_imm_stg_rec.PLANNING_EXCEPTION_SET;
324 g_attr_val(46) := l_imm_stg_rec.INVENTORY_USE_UP_DATE;
325 g_attr_val(47) := l_imm_stg_rec.PLANNING_MAKE_BUY_CODE;
326 g_attr_val(48) := l_imm_stg_rec.WEIGHT_UOM;
327 g_attr_val(49) := l_imm_stg_rec.VOLUME_UOM;
328 g_attr_val(50) := l_imm_stg_rec.ROUNDING_CONTROL_TYPE;
329 g_attr_val(51) := l_imm_stg_rec.ATP_FLAG;
330 g_attr_val(52) := l_imm_stg_rec.ATP_COMPONENTS_FLAG;
331 g_attr_val(53) := l_imm_stg_rec.DEMAND_FULFILLMENT_LT;
332 g_attr_val(54) := l_imm_stg_rec.LOTS_EXPIRATION;
333 g_attr_val(55) := l_imm_stg_rec.CONSIGNED_FLAG;
334 g_attr_val(56) := l_imm_stg_rec.LEADTIME_VARIABILITY;
335 g_attr_val(57) := l_imm_stg_rec.PLANNER_CODE;
336 g_attr_val(58) := l_imm_stg_rec.EO_FLAG;
337 g_attr_val(59) := l_imm_stg_rec.EXCESS_HORIZON;
338 g_attr_val(60) := l_imm_stg_rec.OBSOLESCENCE_DATE;
339 g_attr_val(61) := l_imm_stg_rec.REPAIR_LEAD_TIME;
340 g_attr_val(62) := l_imm_stg_rec.REPAIR_YIELD;
341 g_attr_val(63) := l_imm_stg_rec.REPAIR_COST;
342 g_attr_val(64) := l_imm_stg_rec.RELIABILITY;
343 g_attr_val(65) := l_imm_stg_rec.FAILURE_IMPACT;
344 g_attr_val(66) := l_imm_stg_rec.STANDARD_DEVIATION;
345 g_attr_val(67) := l_imm_stg_rec.COEFFICIENT_OF_VARIATION;
346 g_attr_val(68) := l_imm_stg_rec.BASIS_AVG_DAILY_DEMAND;
347 g_attr_val(69) := l_imm_stg_rec.FORECAST_RULE_FOR_DEMANDS;
348 g_attr_val(70) := l_imm_stg_rec.FORECAST_RULE_FOR_RETURNS;
349 g_attr_val(71) := l_imm_stg_rec.LIFE_TIME_BUY_DATE;
350 g_attr_val(72) := l_imm_stg_rec.END_OF_LIFE_DATE;
351 g_attr_val(73) := l_imm_stg_rec.AVG_DEMAND_BEYOND_PH;
352 g_attr_val(74) := l_imm_stg_rec.AVG_RETURNS_BEYOND_PH;
353 g_attr_val(75) := l_imm_stg_rec.RETURN_FORECAST_TIME_FENCE;
354 g_attr_val(76) := l_imm_stg_rec.AVERAGE_DAILY_DEMAND;
355 g_attr_val(77) := l_imm_stg_rec.DEFECTIVE_ITEM_COST;
356 g_attr_val(78) := l_imm_stg_rec.STD_DEVIATION_FOR_DEMAND;
357 g_attr_val(79) := l_imm_stg_rec.MEAN_INTER_ARRIVAL;
358 g_attr_val(80) := l_imm_stg_rec.STD_DEVIATION_INTER_ARRIVAL;
359 g_attr_val(81) := l_imm_stg_rec.INTERARRIVAL_DIST_METHOD;
360 g_attr_val(82) := l_imm_stg_rec.INTERMITTENT_DEMAND;
361 g_attr_val(83) := l_imm_stg_rec.MAX_USAGE_FACTOR;
362 g_attr_val(84) := l_imm_stg_rec.MIN_REM_SHELF_LIFE_DAYS;
363 g_attr_val(85) := l_imm_stg_rec.UNSATISFIED_DEMAND_FACTOR;
364 g_attr_val(86) := l_imm_stg_rec.DMD_SATISFIED_PERCENT;
365 g_attr_val(87) := l_imm_stg_rec.MIN_SUP_DEM_PERCENT;
366 g_attr_val(88) := l_imm_stg_rec.ROP_SAFETY_STOCK;
367 g_attr_val(89) := l_imm_stg_rec.COMPUTE_SS;
368 g_attr_val(90) := l_imm_stg_rec.COMPUTE_EOQ;
369 g_attr_val(91) := l_imm_stg_rec.ORDER_COST;
370 end set_attr_val;
371
372 /*
373 This procedure posts a record into msc_item_attributes table
374
375 */
376 procedure update_record_to_db(stg_rec_rowid VARCHAR2) is
377
378 cursor c_get_simset_id(l_simset_name varchar2) is
379 select simulation_set_id
380 from msc_item_simulation_sets
381 where simulation_set_name = l_simset_name;
382
383 cursor c_get_item_id(l_item_name varchar2,l_org_id number,l_inst_id number) is
384 select distinct inventory_item_id
385 from msc_system_items
386 where item_name = l_item_name and
387 organization_id = l_org_id and
388 sr_instance_id = l_inst_id and
389 plan_id = -1;
390
391 cursor c_get_org_id(l_org_code varchar2) is
392 select sr_tp_id
393 from msc_trading_partners
394 where organization_code = l_org_code;
395
396 cursor c_get_inst_id(l_inst_code varchar2) is
397 select instance_id
398 from msc_apps_instances
399 where instance_code=l_inst_code;
400
401 cursor c_get_zone_id(l_zone varchar2,l_sr_instance_id number) is
402 select region_id
403 from msc_regions
404 where zone=l_zone and
405 sr_instance_id=l_sr_instance_id;
406
407 cursor c_get_customer_id(l_cust_name varchar2,l_sr_instance_id number) is
408 select partner_id
409 from msc_trading_partners
410 where partner_type=2 and
411 partner_name = l_cust_name and
412 sr_instance_id = l_sr_instance_id;
413
414 cursor c_get_cust_site_id (l_cust_id number,l_sr_instance_id number,l_tp_site_code varchar2) is
415 select partner_site_id --sr_tp_site_id
416 from msc_trading_partner_sites
417 where partner_id=l_cust_id and
418 location= l_tp_site_code and
419 tp_site_code='SHIP_TO' and
420 sr_instance_id = l_sr_instance_id;
421
422 cursor c_chk_attr(p_folder_object in varchar2,
423 p_attr_name in varchar2) is
424 select 1
425 from msc_criteria
426 where folder_object = p_folder_object
427 and field_name = p_attr_name;
428
429 cursor c_get_dest_rowid_cust(l_simset_id number,l_item_id number,l_org_id number,
430 l_inst_id number,l_cust_id number,l_site_id number) is
431 select rowid
432 from msc_item_attributes
433 where simulation_set_id=l_simset_id and
434 inventory_item_id=l_item_id and
435 organization_id=l_org_id and
436 sr_instance_id= l_inst_id and
437 customer_id=l_cust_id and
438 customer_site_id=l_site_id;
439
440
441 cursor c_get_dest_rowid_zone(l_simset_id number,l_item_id number,l_org_id number,
442 l_inst_id number,l_zone_id number) is
443 select rowid
444 from msc_item_attributes
445 where simulation_set_id=l_simset_id and
446 inventory_item_id=l_item_id and
447 organization_id=l_org_id and
448 sr_instance_id= l_inst_id and
449 region_id=l_zone_id;
450
451 cursor c_get_dest_rowid(l_simset_id number,l_item_id number,l_org_id number,l_inst_id number,l_zone_id number,l_cust_id number,l_site_id number) is
452 select rowid
453 from msc_item_attributes
454 where simulation_set_id=l_simset_id and
455 inventory_item_id=l_item_id and
456 organization_id=l_org_id and
457 sr_instance_id= l_inst_id and
458 ( region_id=l_zone_id
459 or (region_id is null and l_zone_id is null)) and
460 ( customer_id=l_cust_id
461 or (customer_id is null and l_cust_id is null)) and
462 ( customer_site_id=l_site_id
463 or (customer_site_id is null and l_site_id is null));
464
465
466
467 l_sim_set_id number;
468 l_org_id number;
469 l_inst_id number;
470 l_item_id number;
471 l_zone_id number;
472 l_cust_id number;
473 l_site_id number;
474 l_dest_rowid varchar2(80);
475
476 l_update_stmt varchar2(32000);
477 l_insert_stmt varchar2(32000);
478 l_insert_cols varchar2(32000);
479 l_insert_vals varchar2(32000);
480
481 l_zone_val varchar2(10);
482 l_cust_val varchar2(10);
483 l_site_val varchar2(10);
484 l_sql varchar2(4000);
485
486 Type attr_sql_type is REF CURSOR;
487 c_attr_sql attr_sql_type;
488 l_hidden varchar2(100);
489 l_meaning varchar2(240);
490 l_old_val varchar2(240);
491 l_first number := 1;
492 l_temp number;
493
494 i number;
495 l_upd_attr_count number :=0;
496 begin
497 msc_util.msc_debug('In update proc.. '||g_attr_val(1)||'-'||g_attr_val(2));
498 open c_get_simset_id(g_attr_val(1));
499 fetch c_get_simset_id into l_sim_set_id;
500 close c_get_simset_id;
501 msc_util.msc_debug('sim set id'||l_sim_set_id);
502 if l_sim_set_id is null then
503 msc_util.msc_debug('Simulation Set Name:'||g_attr_val(1)||' doesnt exist');
504 return;
505 end if;
506
507 open c_get_org_id(g_attr_val(3));
508 fetch c_get_org_id into l_org_id;
509 close c_get_org_id;
510 msc_util.msc_debug('org id'||l_org_id);
511 if l_org_id is null then
512 msc_util.msc_debug('Organization Code:'||g_attr_val(3)||' doesnt exist');
513 return;
514 end if;
515
516 open c_get_inst_id(g_attr_val(4));
517 fetch c_get_inst_id into l_inst_id;
518 close c_get_inst_id;
519
520 if l_inst_id is null then
521 msc_util.msc_debug('Sr Instance Code:'||g_attr_val(4)||' doesnt exist');
522 return;
523 end if;
524
525 open c_get_item_id(g_attr_val(2),l_org_id,l_inst_id);
526 fetch c_get_item_id into l_item_id;
527 close c_get_item_id;
528 msc_util.msc_debug('item id'||l_item_id);
529 if l_item_id is null then
530 msc_util.msc_debug('Item name:'||g_attr_val(2)||' doesnt exist');
531 return;
532 end if;
533
534 open c_get_zone_id(g_attr_val(5),l_inst_id);
535 fetch c_get_zone_id into l_zone_id;
536 close c_get_zone_id;
537 msc_util.msc_debug('zone id:'||l_zone_id);
538
539 open c_get_customer_id(g_attr_val(6),l_inst_id);
540 fetch c_get_customer_id into l_cust_id;
541 close c_get_customer_id;
542 msc_util.msc_debug('cust id:'||l_cust_id);
543
544 open c_get_cust_site_id(l_cust_id,l_inst_id,g_attr_val(7));
545 fetch c_get_cust_site_id into l_site_id;
546 close c_get_cust_site_id;
547 msc_util.msc_debug('site id:'||l_site_id);
548
549 if(l_zone_id is not null and l_cust_id is not null) then
550 msc_util.msc_debug('Both zone and customer value were provided for record with simulation set name,item name:'||g_attr_val(1)||','||g_attr_val(2));
551 return;
552 end if;
553
554 if(l_zone_id is not null) then
555 for i in 8..g_attr_name.last loop
556 if g_attr_val(i) is not null then
557 Open c_chk_attr('MSC_REGION_UPDATE_ATTRIBUTES',g_attr_name(i));
558 fetch c_chk_attr into l_temp;
559 if c_chk_attr%notfound then
560 msc_util.msc_debug('Attribute '||g_attr_name(i)||' is not updateable at zone level.');
561 close c_chk_attr;
562 return;
563 end if;
564 close c_chk_attr;
565 end if;
566 end loop;
567 open c_get_dest_rowid_zone(l_sim_set_id,l_item_id,l_org_id,l_inst_id,l_zone_id);
568 fetch c_get_dest_rowid_zone into l_dest_rowid;
569 close c_get_dest_rowid_zone;
570 elsif l_cust_id is not null then
571 for i in 8..g_attr_name.last loop
572 if g_attr_val(i) is not null then
573 Open c_chk_attr('MSC_CUST_UPDATE_ATTRIBUTES',g_attr_name(i));
574 fetch c_chk_attr into l_temp;
575 if c_chk_attr%notfound then
576 msc_util.msc_debug('Attribute '||g_attr_name(i)||' is not updateable at Customer level.');
577 close c_chk_attr;
578 return;
579 end if;
580 close c_chk_attr;
581 end if;
582 end loop;
583
584 open c_get_dest_rowid_cust(l_sim_set_id,l_item_id,l_org_id,l_inst_id,l_cust_id,l_site_id);
585 fetch c_get_dest_rowid_cust into l_dest_rowid;
586 close c_get_dest_rowid_cust;
587 else
588 open c_get_dest_rowid(l_sim_set_id,l_item_id,l_org_id,l_inst_id,l_zone_id,l_cust_id,l_site_id);
589 fetch c_get_dest_rowid into l_dest_rowid;
590 close c_get_dest_rowid;
591 end if;
592
593 msc_util.msc_debug('l_dest_row_id:'||l_dest_rowid);
594 l_first := 1;
595 if l_dest_rowid is not null then -- row exists in msc_item_attributes
596
597 l_update_stmt := 'update msc_item_attributes set ';
598 msc_util.msc_debug('l_update_stmt:'||l_update_stmt);
599 i := 8;
600 loop
601 l_hidden := null;
602
603 -- -- msc_util.msc_debug('i,count'||i||':'||g_attr_name.count);
604 -- -- msc_util.msc_debug('type,val'||g_attr_type(i)||','||g_attr_val(i));
605 if(g_attr_val(i) is not null) then
606 if g_attr_type(i) in ( 1,6) then -- number,date
607 l_hidden := to_char(g_attr_val(i));
608 if g_attr_type(i) = 6 THEN
609 l_hidden := ''''||l_hidden||'''';
610 end if;
611
612 elsif g_attr_type(i) = 2 then --char
613 if g_sql_stmt(i) is not null then
614 l_sql := 'select hidden,displayed from ('||g_sql_stmt(i)||') where displayed='||''''||g_attr_val(i)||'''';
615 l_sql := replace(l_sql,' display ',' displayed ');
616 msc_util.msc_debug('sql stmt:'||l_sql);
617 open c_attr_sql for l_sql;
618 fetch c_attr_sql into l_hidden,l_meaning;
619 close c_attr_sql;
620 else
621 l_hidden := g_attr_val(i);
622 end if;
623 -- following attributes are char columns in msc_item_attributes so add quotes to the value.
624 if g_attr_name(i) in ('PLANNING_EXCEPTION_SET','WEIGHT_UOM','VOLUME_UOM','ATP_FLAG','ATP_COMPONENTS_FLAG','ABC_CLASS_NAME','PLANNER_CODE','RELIABILITY','FAILURE_IMPACT','INTERARRIVAL_DIST_METHOD') then
625 l_hidden := ''''||l_hidden||'''';
626 end if;
627 --msc_util.msc_debug('l_hidden,l_meaning:'||l_hidden||','||l_meaning);
628
629 else
630 null;
631 end if;
632 l_sql := 'select '||g_attr_name(i)||' from msc_item_attributes where rowid='||''''||l_dest_rowid||'''';
633
634 msc_util.msc_debug('l_sql:'||l_sql);
635 open c_attr_sql for l_sql;
636 fetch c_attr_sql into l_old_val;
637 close c_attr_sql;
638
639 if(l_first = 1) then
640 l_update_stmt := l_update_stmt||g_attr_name(i)||'='||l_hidden;
641 l_first := 2;
642 else
643 l_update_stmt := l_update_stmt||','||g_attr_name(i)||'='||l_hidden;
644 end if;
645
646 if l_old_val is null then
647 l_upd_attr_count := l_upd_attr_count+1;
648 end if;
649 end if;
650 -- msc_util.msc_debug('l_update_stmt:'||l_update_stmt);
651 if i=g_attr_name.count then
652 exit;
653 end if;
654 i:=i+1;
655 end loop;
656 l_update_stmt := l_update_stmt||',updated_columns_count=updated_columns_count+'||l_upd_attr_count;
657 l_update_stmt := l_update_stmt||' where rowid='||''''||l_dest_rowid||'''';
658 msc_util.msc_debug('update stmt:'||l_update_stmt);
659 msc_Get_name.execute_dsql(l_update_stmt);
660
661 else -- dest row id is null so insert row into msc_item_attributes
662 l_zone_val := nvl(to_char(l_zone_id),'null');
663 l_cust_val := nvl(to_char(l_cust_id),'null');
664 l_site_val := nvl(to_char(l_site_id),'null');
665 l_insert_stmt := 'insert into msc_item_attributes ';
666 l_insert_cols := 'plan_id,simulation_set_id,inventory_item_id,organization_id,sr_instance_id,created_by,creation_date,last_update_date,last_updated_by,last_update_login,
667 region,region_id,region_instance_id,customer_id,customer_site_id,customer_instance_id';
668 l_insert_vals := '-1,'||l_sim_set_id||','||l_item_id||','||l_org_id||','||l_inst_id||','||1||',sysdate,sysdate,'||1||','||1||','''||g_attr_val(5)||''','||l_zone_val||','||l_inst_id||','||l_cust_val||','||l_site_val||','||l_inst_id;
669 l_upd_attr_count :=0;
670 i := 8;
671
672 loop
673 l_hidden := null;
674 -- msc_util.msc_debug('i,count'||i||':'||g_attr_name.count);
675 -- msc_util.msc_debug('type,val'||g_attr_type(i)||','||g_attr_val(i));
676 if g_attr_val(i) is not null then
677 if g_attr_type(i) in ( 1,6) then -- number,date
678 l_hidden := g_attr_val(i);
679 if g_attr_type(i) = 6 THEN
680 l_hidden := ''''||l_hidden||'''';
681 end if;
682
683 elsif g_attr_type(i) = 2 then --char
684 --build a dynamic sql attaching meaning and get hidden value
685 if g_sql_stmt(i) is not null then
686 l_sql := 'select hidden,displayed from ('||g_sql_stmt(i)||') where displayed='||''''||g_attr_val(i)||'''';
687 l_sql := replace(l_sql,' display ',' displayed ');
688 msc_util.msc_debug('sql stmt:'||l_sql);
689 open c_attr_sql for l_sql;
690 fetch c_attr_sql into l_hidden,l_meaning;
691 close c_attr_sql;
692 else
693 l_hidden := g_attr_val(i);
694 end if;
695 -- following attributes are char columns in msc_item_attributes so add quotes to the value.
696 if g_attr_name(i) in ('PLANNING_EXCEPTION_SET','WEIGHT_UOM','VOLUME_UOM','ATP_FLAG','ATP_COMPONENTS_FLAG','ABC_CLASS_NAME',
697 'PLANNER_CODE','RELIABILITY','FAILURE_IMPACT','INTERARRIVAL_DIST_METHOD') then
698 l_hidden := ''''||l_hidden||'''';
699 end if;
700 --msc_util.msc_debug('l_hidden,l_meaning:'||l_hidden||','||l_meaning);
701
702 else
703 null;
704 end if;
705 l_insert_cols := l_insert_cols||','||g_attr_name(i);
706 l_insert_vals := l_insert_vals||','||l_hidden;
707 l_upd_attr_count := l_upd_attr_count+1;
708 end if;
709 -- msc_util.msc_debug('l_insert_stmt:'||l_insert_stmt);
710 if i=g_attr_name.count then
711 exit;
712 end if;
713 i:=i+1;
714 end loop;
715 l_insert_stmt := l_insert_stmt||'('||l_insert_cols||',updated_columns_count) values ('||l_insert_vals||','||l_upd_attr_count||')';
716 msc_util.msc_debug('insert stmt:'||l_insert_stmt);
717 msc_Get_name.execute_dsql(l_insert_stmt);
718 end if;
719
720 delete from msc_st_item_attributes where rowid=stg_rec_rowid;
721 commit;
722 end update_record_to_db;
723
724 function sim_set(p_plan_id number) return varchar2 is
725
726 cursor c_sim_set(l_plan_id number) is
727 select simulation_set_name from
728 msc_item_simulation_sets ms,
729 msc_plans mp
730 where mp.plan_id=l_plan_id and ms.simulation_set_id = mp.item_simulation_set_id;
731
732 l_sim_set varchar2(80);
733 begin
734 open c_sim_set(p_plan_id);
735 fetch c_sim_set into l_sim_set;
736 close c_sim_set;
737
738 return l_sim_set;
739 end sim_set;
740
741 procedure attach_simset_toplan(p_plan_id number, p_simset varchar2) is
742 cursor c_nextval is
743 SELECT msc_item_simulation_sets_s.nextval
744 FROM dual;
745
746 cursor c_simset_id is
747 select simulation_set_id from msc_item_simulation_sets where simulation_set_name = p_simset;
748
749 l_count number;
750 l_simset_id number;
751 begin
752
753 open c_simset_id;
754 fetch c_simset_id into l_simset_id;
755 close c_simset_id;
756
757 if l_simset_id is null then
758 open c_nextval;
759 fetch c_nextval into l_simset_id;
760 close c_nextval;
761
762 insert into msc_item_simulation_sets(simulation_set_name,simulation_set_id,last_update_date,last_updated_by,last_update_login,created_by,creation_date) values (p_simset,l_simset_id,sysdate,1,1,1,sysdate);
763 commit;
764 end if;
765
766 if sim_set(p_plan_id) is null or sim_set(p_plan_id)<>p_simset then
767 update msc_plans set item_simulation_set_id = l_simset_id where plan_id=p_plan_id;
768 end if;
769
770 commit;
771
772 end attach_simset_toplan;
773
774 procedure get_sql(p_attr_name varchar2,p_sql out nocopy varchar2,p_lov_type out nocopy number) is
775 cursor c_sql is
776 select sql_statement,lov_type from
777 msc_criteria where
778 folder_object in ('MSC_IMM_UPDATE_ATTRIBUTES',
779 'MSC_REGION_UPDATE_ATTRIBUTES',
780 'MSC_CUST_UPDATE_ATTRIBUTES',
781 'MSC_IMM_DESTINATION_ATTRIBUTES'
782 )
783 and field_name = p_attr_name;
784 begin
785
786 open c_sql;
787 fetch c_sql into p_sql,p_lov_type;
788 close c_sql;
789
790 end get_sql;
791
792 procedure modify_imm_item_attr(p_plan_id number,
793 p_attr_name varchar2,
794 p_attr_val varchar2,
795 p_item_id number,
796 p_org_id number,
797 p_inst_id number) is
798
799 cursor c_simset_id is
800 select item_simulation_set_id
801 from msc_plans
802 where plan_id = p_plan_id;
803
804 cursor c_dest_rowid(p_simset_id number) is
805 select rowid
806 from msc_item_attributes
807 where simulation_set_id=p_simset_id and
808 inventory_item_id = p_item_id and
809 organization_id = p_org_id and
810 sr_instance_id = p_inst_id and
811 region_id is null and
812 customer_id is null;
813
814 l_simset_id number;
815 l_lov_type number;
816 l_sql varchar2(32000);
817 l_old_val varchar2(240);
818 l_rowid varchar2(100);
819
820 l_attr_val varchar2(100);
821 l_upd_count number := 0;
822
823 type dyn_cur is REF CURSOR;
824 c_attr_sql dyn_cur;
825 begin
826 null;
827
828 open c_simset_id;
829 fetch c_simset_id into l_simset_id;
830 close c_simset_id;
831
832 open c_dest_rowid(l_simset_id);
833 fetch c_dest_rowid into l_rowid;
834 close c_dest_rowid;
835
836 get_sql(p_attr_name,l_sql,l_lov_type);
837 if l_lov_type in (2,6) then
838 l_attr_val := ''''||p_attr_val||'''';
839 if l_lov_type = 6 then
840 l_attr_val := ' to_date('||l_attr_val||')';
841 end if;
842 else
843 l_attr_val := p_attr_val;
844 end if;
845
846
847 if l_rowid is null then -- insert record into msc_item_attributes
848
849 l_sql := 'insert into msc_item_attributes(plan_id,simulation_set_id,'||
850 'inventory_item_id,'||
851 'organization_id,'||
852 'sr_instance_id,'||
853 p_attr_name||','||
854 'updated_columns_count,'||
855 'last_updated_by,'||
856 'last_update_date,'||
857 'last_update_login,'||
858 'creation_date,'||
859 'created_by) values (-1,'||
860 l_simset_id||','||
861 p_item_id||','||
862 p_org_id||','||
863 p_inst_id||','||
864 l_attr_val||','||
865 1||','||
866 1||','||
867 'sysdate'||','||
868 1||','||
869 'sysdate'||','||
870 1||')';
871
872 msc_get_name.execute_dsql(l_sql);
873 commit;
874 else -- update existing row
875
876 l_sql := 'select '||p_attr_name||',updated_columns_count from msc_item_attributes where rowid='||''''||l_rowid||'''';
877
878
879 open c_attr_sql for l_sql;
880 fetch c_attr_sql into l_old_val,l_upd_count;
881 close c_attr_sql;
882
883 if l_old_val is null then
884 l_upd_count := l_upd_count+1;
885 end if;
886
887 l_sql := 'update msc_item_attributes set '||p_attr_name||'='||l_attr_val||',updated_columns_count='||l_upd_count||
888 ' where rowid='||''''||l_rowid||'''';
889
890 msc_get_name.execute_dsql(l_sql);
891 commit;
892
893 end if;
894
895 end modify_imm_item_attr;
896
897 procedure modify_plan_item_attr(p_plan_id number,
898 p_attr_name varchar2,
899 p_attr_val varchar2,
900 p_item_id number,
901 p_org_id number,
902 p_sr_instance_id number) is
903 l_sql varchar2(32000);
904 l_lov_type number;
905
906 l_attr_val varchar2(240);
907
908 begin
909
910 get_sql(p_attr_name,l_sql,l_lov_type);
911
912 if l_lov_type in (2,6) then
913 l_attr_val := ''''||p_attr_val||'''';
914 if l_lov_type = 6 then
915 l_attr_val := ' to_date('||l_attr_val||')';
916 end if;
917 else
918 l_attr_val := p_attr_val;
919 end if;
920
921 l_sql := 'update msc_system_items set '||p_attr_name||'='||l_attr_val||
922 ' where plan_id='||p_plan_id||' and inventory_item_id ='||p_item_id||' and organization_id='||p_org_id||' and sr_instance_id='||p_sr_instance_id;
923
924 msc_get_name.execute_dsql(l_sql);
925
926 l_sql := 'update msc_supplies set applied=2,status=0'||
927 ' where plan_id='||p_plan_id||' and inventory_item_id ='||p_item_id||' and organization_id='||p_org_id||' and sr_instance_id='||p_sr_instance_id;
928
929 msc_get_name.execute_dsql(l_sql);
930
931 l_sql := 'update msc_demands set applied=2,status=0'||
932 ' where plan_id='||p_plan_id||' and inventory_item_id ='||p_item_id||' and organization_id='||p_org_id||' and sr_instance_id='||p_sr_instance_id;
933
934 msc_get_name.execute_dsql(l_sql);
935
936 l_sql := 'update msc_supplier_capacities set applied=2,status=0'||
937 ' where plan_id='||p_plan_id||' and inventory_item_id ='||p_item_id||' and organization_id='||p_org_id||' and sr_instance_id='||p_sr_instance_id;
938
939 msc_get_name.execute_dsql(l_sql);
940 commit;
941
942
943 end modify_plan_item_attr;
944
945 function get_real_attrname(p_attr_name varchar2) return varchar2 is
946 l_real_name varchar2(100);
947 begin
948 l_real_name := p_attr_name;
949 if p_attr_name = 'ATO_FORECAST_CONTROL_TEXT' then
950 l_real_name := 'ATO_FORECAST_CONTROL';
951 elsif p_attr_name = 'CONTINOUS_TRANSFER_TEXT' then
952 l_real_name := 'CONTINOUS_TRANSFER';
953 elsif p_attr_name = 'FCST_RULE_FOR_DEMANDS_TEXT' then
954 l_real_name := 'FORECAST_RULE_FOR_DEMANDS';
955 elsif p_attr_name = 'FCST_RULE_FOR_RETURNS_TEXT' then
956 l_real_name := 'FORECAST_RULE_FOR_RETURNS';
957 elsif p_attr_name = 'INTERARRIVAL_TIME' then
958 l_real_name := 'MEAN_INTER_ARRIVAL';
959 elsif p_attr_name = 'SAFETY_STOCK_DAYS' then
960 l_real_name := 'SAFETY_STOCK_BUCKET_DAYS';
961 end if;
962
963 return l_real_name;
964 end get_real_attrname;
965
966
967 PROCEDURE load_frm_stg_tbl (
968 errbuf OUT NOCOPY VARCHAR2,
969 retcode OUT NOCOPY NUMBER,
970 req_id in number,
971 stg_tbl in varchar2 Default 'MSC_ST_ITEM_ATTRIBUTES'
972 ) is
973
974 Type imm_stg_tbl is REF CURSOR;
975 c_imm_stg_tbl imm_stg_tbl;
976 l_imm_stg_rec imm_stg_rec;
977
978
979 cursor c_imm_stg_rec_count(l_req_id number) is
980 select count(*) from msc_st_item_attributes where request_id=l_req_id;
981
982 cursor c_max_req_id is
983 select max(request_id) from msc_st_item_attributes;
984
985 cursor c_imm_stg(l_req_id number) is
986 select
987 ROWID,
988 SIMULATION_SET_NAME,
989 ITEM_NAME,
990 ORGANIZATION_CODE,
991 SR_INSTANCE_CODE,
992 ZONE,
993 CUSTOMER_NAME,
994 CUSTOMER_SITE_NAME,
995 CRITICAL_COMPONENT_FLAG,
996 FULL_LEAD_TIME,
997 PREPROCESSING_LEAD_TIME,
998 POSTPROCESSING_LEAD_TIME,
999 FIXED_LEAD_TIME,
1000 VARIABLE_LEAD_TIME,
1001 FIXED_ORDER_QUANTITY,
1002 FIXED_DAYS_SUPPLY,
1003 SHRINKAGE_RATE,
1004 FIXED_LOT_MULTIPLIER,
1005 MINIMUM_ORDER_QUANTITY,
1006 MAXIMUM_ORDER_QUANTITY,
1007 SERVICE_LEVEL,
1008 CARRYING_COST,
1009 DEMAND_TIME_FENCE_DAYS,
1010 ATO_FORECAST_CONTROL,
1011 PLANNING_TIME_FENCE_DAYS,
1012 STANDARD_COST,
1013 PIP_FLAG,
1014 LIST_PRICE,
1015 SUBSTITUTION_WINDOW,
1016 SAFETY_STOCK_BUCKET_DAYS,
1017 UNIT_WEIGHT,
1018 UNIT_VOLUME,
1019 SAFETY_STOCK_CODE,
1020 SAFETY_STOCK_PERCENT,
1021 ABC_CLASS_NAME,
1022 MRP_PLANNING_CODE,
1023 DRP_PLANNED,
1024 DAYS_MAX_INV_SUPPLY,
1025 DAYS_MAX_INV_WINDOW,
1026 DAYS_TGT_INV_SUPPLY,
1027 DAYS_TGT_INV_WINDOW,
1028 CONTINOUS_TRANSFER,
1029 CONVERGENCE,
1030 CREATE_SUPPLY_FLAG,
1031 DIVERGENCE,
1032 PLANNING_EXCEPTION_SET,
1033 INVENTORY_USE_UP_DATE,
1034 PLANNING_MAKE_BUY_CODE,
1035 WEIGHT_UOM,
1036 VOLUME_UOM,
1037 ROUNDING_CONTROL_TYPE,
1038 ATP_FLAG,
1039 ATP_COMPONENTS_FLAG,
1040 DEMAND_FULFILLMENT_LT,
1041 LOTS_EXPIRATION,
1042 CONSIGNED_FLAG,
1043 LEADTIME_VARIABILITY,
1044 PLANNER_CODE,
1045 EO_FLAG,
1046 EXCESS_HORIZON,
1047 OBSOLESCENCE_DATE,
1048 REPAIR_LEAD_TIME,
1049 REPAIR_YIELD,
1050 REPAIR_COST,
1051 RELIABILITY,
1052 FAILURE_IMPACT,
1053 STANDARD_DEVIATION,
1054 COEFFICIENT_OF_VARIATION,
1055 BASIS_AVG_DAILY_DEMAND,
1056 FORECAST_RULE_FOR_DEMANDS,
1057 FORECAST_RULE_FOR_RETURNS,
1058 LIFE_TIME_BUY_DATE,
1059 END_OF_LIFE_DATE,
1060 AVG_DEMAND_BEYOND_PH,
1061 AVG_RETURNS_BEYOND_PH,
1062 RETURN_FORECAST_TIME_FENCE,
1063 AVERAGE_DAILY_DEMAND,
1064 DEFECTIVE_ITEM_COST,
1065 STD_DEVIATION_FOR_DEMAND,
1066 MEAN_INTER_ARRIVAL,
1067 STD_DEVIATION_INTER_ARRIVAL,
1068 INTERARRIVAL_DIST_METHOD,
1069 INTERMITTENT_DEMAND,
1070 MAX_USAGE_FACTOR,
1071 MIN_REM_SHELF_LIFE_DAYS,
1072 UNSATISFIED_DEMAND_FACTOR,
1073 DMD_SATISFIED_PERCENT,
1074 MIN_SUP_DEM_PERCENT,
1075 ROP_SAFETY_STOCK,
1076 COMPUTE_SS,
1077 COMPUTE_EOQ,
1078 ORDER_COST
1079 from MSC_ST_ITEM_ATTRIBUTES where request_id=l_req_id;
1080
1081
1082 l_stg_rec_count number;
1083
1084 l_sql_stmt varchar2(32000);
1085 l_select_clause varchar2(32000);
1086 i number;
1087 l_req_id number;
1088
1089 exc_load_fail EXCEPTION;
1090
1091 BEGIN
1092 msc_util.msc_debug('Staging table name:'||stg_tbl);
1093 msc_util.msc_debug('Req Id of File Loader is:'||req_id);
1094
1095 if stg_tbl='MSC_ST_ITEM_ATTRIBUTES' then
1096 open c_max_req_id;
1097 fetch c_max_req_id into l_req_id;
1098 close c_max_req_id;
1099
1100 if l_req_id is null then
1101 msc_util.msc_debug('No records in staging table MSC_ST_ITEM_ATTRIBUTES');
1102 return;
1103 end if;
1104
1105 open c_imm_stg_rec_count(l_req_id);
1106 fetch c_imm_stg_rec_count into l_stg_rec_count;
1107 close c_imm_stg_rec_count;
1108
1109 msc_util.msc_debug('No. of records in staging table:'||l_stg_rec_count);
1110
1111 if l_stg_rec_count = 0 then
1112 msc_util.msc_debug('No rows available in MSC_ST_ITEM_ATTRIBUTES');
1113 raise exc_load_fail;
1114 end if;
1115 init; -- This will prepare attribute_names, lov_type, sql_stmt list
1116
1117 /* l_sql_stmt := 'select rowid,';
1118 i := 1;
1119 loop
1120 l_sql_stmt := l_sql_stmt||g_attr_name(i);
1121 if i=g_attr_name.count then
1122 exit;
1123 end if;
1124 l_sql_stmt := l_sql_stmt||', ';
1125 i:=i+1;
1126 end loop;
1127
1128 l_sql_stmt := l_sql_stmt||' from msc_st_item_attributes' ;
1129 --msc_util.msc_debug('l_sql_stmt='||l_sql_stmt);
1130 */
1131
1132
1133 msc_util.msc_debug('Max Request Id :'||l_req_id);
1134
1135 open c_imm_stg(l_req_id);
1136 loop
1137 fetch c_imm_stg into l_imm_stg_rec;
1138 exit when c_imm_stg%NOTFOUND;
1139 msc_util.msc_debug(l_imm_stg_rec.simulation_set_name);
1140 set_attr_val(l_imm_stg_rec); -- This copies the attribute values of a record into g_attr_val list
1141 msc_util.msc_debug('after set_attr_val');
1142 update_record_to_db(l_imm_stg_rec.rowid);
1143 msc_util.msc_debug('after update_record_to_db');
1144 commit;
1145 --msc_util.msc_debug('Fetching values---------------');
1146 --msc_util.msc_debug(l_imm_stg_rec.simulation_set_name);
1147 end loop;
1148 close c_imm_stg;
1149
1150 msc_util.msc_debug('=============================================');
1151 msc_util.msc_debug(l_stg_rec_count||' records inserted into staging table');
1152
1153 open c_imm_stg_rec_count(l_req_id);
1154 fetch c_imm_stg_rec_count into l_stg_rec_count;
1155 close c_imm_stg_rec_count;
1156
1157 msc_util.msc_debug(l_stg_rec_count||' records not processed properly and not inserted/updated to IMM');
1158 -- msc_util.msc_debug('Use the following sql to find out which rows are not processed and inserted/updated to IMM');
1159
1160 /* open c_imm_stg_tbl for l_sql_stmt;
1161 loop
1162 fetch c_imm_stg_tbl into l_imm_stg_rec;
1163 exit when c_imm_stg%NOTFOUND;
1164 msc_util.msc_debug(l_imm_stg_rec.simulation_set_name);
1165 set_attr_val(l_imm_stg_rec); -- This copies the attribute values of a record into g_attr_val list
1166 msc_util.msc_debug('after set_attr_val');
1167 update_record_to_db(l_imm_stg_rec.rowid);
1168 msc_util.msc_debug('after update_record_to_db');
1169 commit;
1170 msc_util.msc_debug('Fetching values---------------');
1171 msc_util.msc_debug(l_imm_stg_rec.simulation_set_name);
1172 end loop;
1173 close c_imm_stg_tbl;
1174 */
1175 end if;
1176
1177 EXCEPTION
1178 when exc_load_fail then
1179 retcode := 2;
1180
1181 when OTHERS THEN
1182 retcode := 2;
1183 errbuf := sqlerrm;
1184
1185 END load_frm_stg_tbl;
1186
1187
1188
1189 END MSC_IMPORT_UTIL; -- package