[Home] [Help]
PACKAGE BODY: APPS.MSC_X_CVMI_PLANNING
Source
1 PACKAGE BODY MSC_X_CVMI_PLANNING AS
2 /* $Header: MSCXCVPB.pls 115.14 2004/07/14 01:04:38 jguo noship $ */
3
4 COMPANY_MAPPING CONSTANT NUMBER := 1;
5 ORGANIZATION_MAPPING CONSTANT NUMBER := 2;
6 SITE_MAPPING CONSTANT NUMBER := 3;
7 OEM_COMPANY_ID CONSTANT NUMBER := 1;
8
9 g_msc_cp_debug VARCHAR2(10) := NVL(FND_PROFILE.VALUE('MSC_CP_DEBUG'), '0');
10 l_old_average_daily_demand NUMBER;
11 l_forecast_horizon NUMBER;
12 l_vmi_refresh_flag NUMBER;
13
14 -- calculate average daily demand
15 PROCEDURE calculate_average_demand
16 IS
17 l_total_forecast NUMBER;
18 l_old_average_daily_demand NUMBER;
19 l_sce_customer_id NUMBER;
20 l_sce_organization_id NUMBER;
21 l_sce_supplier_id NUMBER;
22 l_sce_supplier_site_id NUMBER;
23
24 l_horizon_end_date DATE;
25 l_forecast_order_type NUMBER;
26 l_vmi_forecast_type NUMBER;
27
28 CURSOR c_forecast_items IS
29 SELECT
30 msi.plan_id
31 , msi.inventory_item_id
32 , msi.organization_id
33 , msi.sr_instance_id
34 , mtp.modeled_customer_id
35 , mtp.modeled_customer_site_id
36 , NVL(msi.forecast_horizon, 0) forecast_horizon
37 , msi.vmi_forecast_type
38 , msi.uom_code
39 , mvt.average_daily_demand
40 FROM msc_system_items msi
41 , msc_trading_partners mtp
42 , msc_vmi_temp mvt
43 WHERE msi.inventory_planning_code = 7
44 AND msi.organization_id = mtp.sr_tp_id
45 AND msi.sr_instance_id = mtp.sr_instance_id
46 AND mtp.partner_type = 3 -- org
47 AND mtp.modeled_customer_id IS NOT NULL
48 AND mtp.modeled_customer_site_id IS NOT NULL
49 AND msi.plan_id = -1
50 and mvt.plan_id (+) = msi.plan_id
51 and mvt.inventory_item_id (+) = msi.inventory_item_id
52 and mvt.organization_id (+) = msi.organization_id
53 and mvt.sr_instance_id (+) = msi.sr_instance_id
54 and mvt.vmi_type (+) = 2 -- customer facing vmi
55 ;
56
57 BEGIN
58
59 print_debug_info('Start of average daily demand engine');
60
61 FOR forecast_item IN c_forecast_items LOOP
62
63 print_debug_info( ' plan/item/org/instance/customer/customer site = '
64 || forecast_item.plan_id
65 || '-' || forecast_item.inventory_item_id
66 || '-' || forecast_item.organization_id
67 || '-' || forecast_item.sr_instance_id
68 || '-' || forecast_item.modeled_customer_id
69 || '-' || forecast_item.modeled_customer_site_id
70 );
71 print_debug_info( ' forecast horizon/forecast type/uom/old average daily demand = '
72 || forecast_item.forecast_horizon
73 || '-' || forecast_item.vmi_forecast_type
74 || '-' || forecast_item.uom_code
75 || '-' || forecast_item.average_daily_demand
76 );
77
78
79 calculate_average_demand
80 ( forecast_item.plan_id
81 , forecast_item.inventory_item_id
82 , forecast_item.organization_id
83 , forecast_item.sr_instance_id
84 , forecast_item.modeled_customer_id
85 , forecast_item.modeled_customer_site_id
86 , forecast_item.forecast_horizon
87 , forecast_item.vmi_forecast_type
88 , forecast_item.uom_code
89 , forecast_item.average_daily_demand
90 );
91
92 END LOOP; -- c_forecast_items
93 print_debug_info( 'End of average daily demand calculation engine');
94 EXCEPTION
95 WHEN OTHERS THEN
96 print_debug_info('Error in average daily demand calculation engine = ' || sqlerrm);
97 RAISE;
98 END calculate_average_demand;
99
100 -- calculate average daily demand
101 PROCEDURE calculate_average_demand
102 ( p_plan_id IN NUMBER
103 , p_inventory_item_id IN NUMBER
104 , p_organization_id IN NUMBER
105 , p_sr_instance_id IN NUMBER
106 , p_customer_id IN NUMBER
107 , p_customer_site_id IN NUMBER
108 , p_forecast_horizon IN NUMBER
109 , p_vmi_forecast_type IN NUMBER
110 , p_item_uom_code IN varchar2
111 , p_old_average_daily_demand IN NUMBER
112 )
113 IS
114 l_total_forecast NUMBER := 0;
115 l_average_daily_demand NUMBER;
116 l_sce_supplier_id NUMBER;
117 l_sce_organization_id NUMBER;
118 l_sce_customer_id NUMBER;
119 l_sce_customer_site_id NUMBER;
120 l_horizon_end_date DATE;
121 l_forecast_order_type NUMBER;
122 l_horizon_start_date DATE;
123
124 lv_calendar_code varchar2(14);
125 lv_instance_id number;
126
127 l_conv_found BOOLEAN := FALSE;
128 l_conv_rate NUMBER := 1;
129 lv_forecast_type NUMBER;
130
131 CURSOR c_total_forecast
132 ( p_plan_id NUMBER
133 , p_inventory_item_id NUMBER
134 , p_organization_id NUMBER
135 , p_customer_id NUMBER
136 , p_customer_site_id NUMBER
137 , p_horizon_end_date DATE
138 , l_forecast_type NUMBER
139 , p_horizon_start_date DATE
140 ) IS
141 SELECT distinct SUM(sd.primary_quantity) total_demand,
142 sd.primary_uom
143 FROM msc_sup_dem_entries sd
144 WHERE sd.plan_id = p_plan_id
145 AND sd.inventory_item_id = p_inventory_item_id
146 AND sd.customer_id = p_customer_id
147 AND sd.customer_site_id = p_customer_site_id
148 AND TRUNC(key_date) BETWEEN TRUNC(p_horizon_start_date)
149 AND TRUNC(p_horizon_end_date)
150 AND publisher_order_type = l_forecast_type
151 AND sd.supplier_id = 1
152 AND p_forecast_horizon > 0
153 GROUP BY
154 sd.primary_uom
155 ;
156
157 BEGIN
158
159 print_debug_info( ' plan/item/org/instance/customer/customer site = '
160 || p_plan_id
161 || '-' || p_inventory_item_id
162 || '-' || p_organization_id
163 || '-' || p_sr_instance_id
164 || '-' || p_customer_id
165 || '-' || p_customer_site_id
166 );
167 print_debug_info( ' forecast horizon/forecast type/old average daily demand = '
168 || p_forecast_horizon
169 || '-' || p_vmi_forecast_type
170 || '-' || p_old_average_daily_demand
171 );
172 l_sce_organization_id := aps_to_sce(p_organization_id, ORGANIZATION_MAPPING, p_sr_instance_id);
173 l_sce_customer_id := aps_to_sce(p_customer_id, COMPANY_MAPPING);
174 l_sce_customer_site_id := aps_to_sce(p_customer_site_id, SITE_MAPPING);
175
176 /* Call the API to get the correct Calendar */
177 msc_x_util.get_calendar_code(
178 1,
179 l_sce_organization_id,
180 l_sce_customer_id,
181 l_sce_customer_site_id,
182 lv_calendar_code,
183 lv_instance_id);
184 print_debug_info(' Calendar/sr_instance_id : ' || lv_calendar_code||'/'||lv_instance_id);
185
186 print_debug_info( ' cp org/cp customer/cp customer site = '
187 || l_sce_organization_id
188 || '/' || l_sce_customer_id
189 || '/' || l_sce_customer_site_id
190 );
191
192 IF (p_vmi_forecast_type = 1 OR p_vmi_forecast_type = 2) THEN
193 l_horizon_start_date := SYSDATE;
194 l_horizon_end_date := MSC_CALENDAR.DATE_OFFSET( lv_calendar_code -- arg_calendar_code IN varchar2,
195 , lv_instance_id -- arg_instance_id IN NUMBER,
196 , SYSDATE -- l_horizon_start_date -- arg_date IN DATE,
197 , p_forecast_horizon -- arg_offset IN NUMBER
198 , 99999 --arg_offset_type
199 );
200 ELSIF (p_vmi_forecast_type = 3) THEN
201 l_horizon_start_date := MSC_CALENDAR.DATE_OFFSET( lv_calendar_code -- arg_calendar_code IN varchar2,
202 , lv_instance_id -- arg_instance_id IN NUMBER,
203 , SYSDATE -- l_horizon_start_date -- arg_date IN DATE,
204 , 0 - p_forecast_horizon -- arg_offset IN NUMBER
205 , 99999 --arg_offset_type
206 );
207 l_horizon_end_date := SYSDATE;
208 END IF;
209
210 print_user_info( ' forecast horizon end date = ' || l_horizon_end_date);
211
212 IF (p_vmi_forecast_type = 1) THEN
213 /* ORDER - FORECAST */
214 lv_forecast_type := 2;
215 print_debug_info('Order Forecast');
216
217 ELSIF (p_vmi_forecast_type = 2) THEN
218 /* SALES - FORECAST */
219 lv_forecast_type := 1;
220 print_debug_info('Sales Forecast');
221
222 ELSIF (p_vmi_forecast_type = 3) THEN
223 /* HISTORICAL - SALES */
224 lv_forecast_type := 4;
225 print_debug_info('Historical Sales');
226
227 END IF;
228
229 FOR c_rec IN c_total_forecast(
230 p_plan_id
231 , p_inventory_item_id
232 , l_sce_organization_id
233 , l_sce_customer_id
234 , l_sce_customer_site_id
235 , l_horizon_end_date
236 , lv_forecast_type
237 , l_horizon_start_date
238 )
239 LOOP
240 IF (c_rec.primary_uom <> p_item_uom_code) THEN
241 MSC_X_UTIL.GET_UOM_CONVERSION_RATES( c_rec.primary_uom
242 , p_item_uom_code
243 , p_inventory_item_id
244 , l_conv_found
245 , l_conv_rate
246 );
247 print_debug_info('t_primary_uom/item_uom_code/l_conv_rate:'
248 || c_rec.primary_uom||'/'||p_item_uom_code
249 ||'/'||l_conv_rate);
250 l_total_forecast := l_total_forecast + c_rec.total_demand*l_conv_rate;
251 ELSE
252 l_total_forecast := l_total_forecast + c_rec.total_demand;
253 END IF;
254 END LOOP;
255
256
257 IF (p_forecast_horizon <> 0) AND (p_forecast_horizon IS NOT NULL) THEN
258 l_average_daily_demand := round((NVL(l_total_forecast, 0)
259 / p_forecast_horizon),6);
260 ELSE
261 print_user_info( ' Forecast horizon is zero or NULL, please set up forecast horizon correctly');
262
263 END IF;
264
265 print_debug_info( ' total order forecast/average daily demand = '
266 || l_total_forecast
267 || '/' || l_average_daily_demand
268 );
269
270 IF (l_average_daily_demand <> p_old_average_daily_demand) THEN
271 l_vmi_refresh_flag := 1;
272 ELSE
273 l_vmi_refresh_flag := NULL;
274 END IF;
275
276 UPDATE msc_system_items
277 SET -- average_daily_demand = l_average_daily_demand
278 vmi_refresh_flag = NVL(l_vmi_refresh_flag, vmi_refresh_flag)
279 WHERE plan_id = p_plan_id
280 AND inventory_item_id = p_inventory_item_id
281 AND organization_id = p_organization_id
282 AND sr_instance_id = p_sr_instance_id
283 ;
284 print_debug_info( ' vmi refresh flag updated, number of rows updated = '
285 || SQL%ROWCOUNT
286 );
287
288 UPDATE msc_vmi_temp
289 SET average_daily_demand = l_average_daily_demand
290 WHERE plan_id = p_plan_id
291 AND inventory_item_id = p_inventory_item_id
292 AND organization_id = p_organization_id
293 AND sr_instance_id = p_sr_instance_id
294 AND vmi_type = 2 -- customer facing vmi
295 ;
296 print_debug_info( ' average daily demand updated, number of rows updated = '
297 || SQL%ROWCOUNT
298 );
299
300 IF (SQL%ROWCOUNT = 0 ) THEN
301 INSERT INTO msc_vmi_temp
302 ( PLAN_ID,
303 INVENTORY_ITEM_ID,
304 ORGANIZATION_ID ,
305 SR_INSTANCE_ID ,
306 SUPPLIER_ID ,
307 SUPPLIER_SITE_ID ,
308 USING_ORGANIZATION_ID ,
309 VMI_TYPE ,
310 AVERAGE_DAILY_DEMAND
311 ) VALUES
312 ( p_PLAN_ID,
313 p_INVENTORY_ITEM_ID,
314 p_ORGANIZATION_ID ,
315 p_SR_INSTANCE_ID ,
316 NULL, -- p_SUPPLIER_ID ,
317 NULL, -- p_SUPPLIER_SITE_ID ,
318 NULL, -- p_USING_ORGANIZATION_ID ,
319 2 ,
320 l_AVERAGE_DAILY_DEMAND
321 );
322
323 print_debug_info( ' average daily demand inserted, number of rows inserted = '
324 || SQL%ROWCOUNT
325 );
326
327 END IF;
328
329 commit;
330
331 EXCEPTION
332 WHEN OTHERS THEN
333 print_debug_info('Error in average daily demand calculation ' || sqlerrm);
334 RAISE;
335 END calculate_average_demand;
336
337 -- This function is used to convert APS tp key to SCE company key
338 FUNCTION aps_to_sce(
339 p_tp_key IN NUMBER
340 , p_map_type IN NUMBER
341 , p_sr_instance_id IN NUMBER DEFAULT NULL
342 ) RETURN NUMBER IS
343
344 l_company_key NUMBER;
345
346 CURSOR c_company_key_1 IS
347 SELECT cr.object_id
348 FROM msc_trading_partner_maps map
349 , msc_company_relationships cr
350 WHERE map.map_type = p_map_type
351 AND map.tp_key = p_tp_key
352 AND map.company_key = cr.relationship_id
353 AND cr.relationship_type = 1 -- customer of, 2 -- supplier of
354 ;
355
356 CURSOR c_company_key_2 IS
357 SELECT map.company_key
358 FROM msc_trading_partner_maps map
359 , msc_trading_partners tp
360 WHERE map.map_type = p_map_type
361 AND tp.partner_id = map.tp_key
362 AND tp.sr_tp_id = p_tp_key
363 AND tp.sr_instance_id = p_sr_instance_id
364 ;
365
366 CURSOR c_company_key_3 IS
367 SELECT map.company_key
368 FROM msc_trading_partner_maps map
369 WHERE map.map_type = p_map_type
370 AND map.tp_key = p_tp_key
371 ;
372 BEGIN
373 IF (p_map_type = COMPANY_MAPPING) THEN -- company
374 OPEN c_company_key_1;
375 FETCH c_company_key_1 INTO l_company_key;
376 CLOSE c_company_key_1;
377 END IF;
378
379 IF (p_map_type = ORGANIZATION_MAPPING) THEN -- org
380 OPEN c_company_key_2;
381 FETCH c_company_key_2 INTO l_company_key;
382 CLOSE c_company_key_2;
383 END IF;
384
385 IF (p_map_type = SITE_MAPPING) THEN -- site
386 OPEN c_company_key_3;
387 FETCH c_company_key_3 INTO l_company_key;
388 CLOSE c_company_key_3;
389 END IF;
390
391 print_debug_info(' p_map_type = ' || p_map_type
392 || ' p_tp_key = ' || p_tp_key
393 || ' l_company_key = ' || l_company_key
394 );
395 RETURN l_company_key;
396 EXCEPTION
397 WHEN OTHERS THEN
398 raise;
399 END aps_to_sce;
400
401 -- This function is used to convert APS tp key to SCE company key
402 FUNCTION sce_to_aps(
403 p_company_key IN NUMBER
404 , p_map_type IN NUMBER
405 ) RETURN NUMBER IS
406
407 l_tp_key NUMBER;
408
409 CURSOR c_tp_key_1 IS
410 SELECT map.tp_key
411 FROM msc_trading_partner_maps map
412 , msc_company_relationships cr
413 WHERE map.map_type = p_map_type
414 AND cr.object_id = p_company_key
415 AND map.company_key = cr.relationship_id
416 AND cr.relationship_type = 1 -- customer of, 2 -- supplier of
417 AND cr.subject_id = OEM_COMPANY_ID
418 ;
419
420 CURSOR c_tp_key_2 IS
421 SELECT tp.sr_tp_id
422 FROM msc_trading_partner_maps map
423 , msc_trading_partners tp
424 WHERE map.map_type = p_map_type
425 AND tp.partner_id = map.tp_key
426 AND map.company_key= p_company_key
427 ;
428 /*AND tp.partner.partner_type = 3*/
429
430 CURSOR c_tp_key_3 IS
431 SELECT map.tp_key
432 FROM msc_trading_partner_maps map
433 WHERE map.map_type = p_map_type
434 AND map.company_key = p_company_key
435
436 ;
437 BEGIN
438 IF (p_map_type = COMPANY_MAPPING) THEN -- company
439 OPEN c_tp_key_1;
440 FETCH c_tp_key_1 INTO l_tp_key;
441 CLOSE c_tp_key_1;
442 END IF;
443
444 IF (p_map_type = ORGANIZATION_MAPPING) THEN -- org
445 OPEN c_tp_key_2;
446 FETCH c_tp_key_2 INTO l_tp_key;
447 CLOSE c_tp_key_2;
448 END IF;
449
450 IF (p_map_type = SITE_MAPPING) THEN -- site
451 OPEN c_tp_key_3;
452 FETCH c_tp_key_3 INTO l_tp_key;
453 CLOSE c_tp_key_3;
454 END IF;
455
456 print_debug_info('sce_to_aps:000 p_map_type = ' || p_map_type
457 || ' p_company_key = ' || p_company_key
458 || ' l_tp_key = ' || l_tp_key
459 );
460 RETURN l_tp_key;
461
462 EXCEPTION
463 WHEN OTHERS THEN
464 raise;
465 END sce_to_aps;
466
467 -- This procesure prints out debug information
468 PROCEDURE print_debug_info(
469 p_debug_info IN VARCHAR2
470 )IS
471 BEGIN
472 IF ( g_msc_cp_debug= '1' OR g_msc_cp_debug = '2') THEN
473 FND_FILE.PUT_LINE(FND_FILE.LOG, p_debug_info);
474 END IF;
475 -- dbms_output.put_line(p_debug_info); --ut
476 EXCEPTION
477 WHEN OTHERS THEN
478 RAISE;
479 END print_debug_info;
480
481 -- This procesure prints out message to user
482 PROCEDURE print_user_info(
483 p_user_info IN VARCHAR2
484 ) IS
485 BEGIN
486 FND_FILE.PUT_LINE(FND_FILE.LOG, p_user_info);
487 -- dbms_output.put_line(p_user_info); --ut
488 EXCEPTION
489 WHEN OTHERS THEN
490 RAISE;
491 END print_user_info;
492
493 END MSC_X_CVMI_PLANNING;