[Home] [Help]
PACKAGE BODY: APPS.MSC_X_PLANNING
Source
1 PACKAGE BODY MSC_X_PLANNING AS
2 /* $Header: MSCXSVPB.pls 115.14 2004/07/21 22:30:07 jguo noship $ */
3
4 g_msc_cp_debug VARCHAR2(10) := NVL(FND_PROFILE.VALUE('MSC_CP_DEBUG'), '0');
5
6 SUPPLIER_IS_OEM number := 1;
7 CUSTOMER_IS_OEM number := 2;
8
9 -- calculate average daily demand
10 PROCEDURE calculate_average_demand
11 IS
12 l_total_supply_schedule NUMBER;
13 l_average_daily_demand NUMBER;
14 l_sce_customer_id NUMBER;
15 l_sce_organization_id NUMBER;
16 l_sce_supplier_id NUMBER;
17 l_sce_supplier_site_id NUMBER;
18
19 l_horizon_end_date DATE;
20
21 CURSOR c_forecast_items IS
22 SELECT DISTINCT
23 mis.plan_id
24 , mis.inventory_item_id
25 , mis.organization_id
26 , mis.sr_instance_id
27 , mis.supplier_id
28 , mis.supplier_site_id
29 , mis.using_organization_id
30 FROM msc_item_suppliers mis
31 WHERE mis.plan_id = -1
32 AND mis.vmi_flag = 1
33 ;
34
35 BEGIN
36
37 print_debug_info('Start of average daily demand engine');
38
39 FOR forecast_item IN c_forecast_items LOOP
40
41 print_debug_info( ' plan/item/org/instance/supplier/supplier site/using org = '
42 || forecast_item.plan_id
43 || '/' || forecast_item.inventory_item_id
44 || '/' || forecast_item.organization_id
45 || '/' || forecast_item.sr_instance_id
46 || '/' || forecast_item.supplier_id
47 || '/' || forecast_item.supplier_site_id
48 || '/' || forecast_item.using_organization_id
49 );
50 calculate_average_demand_api
51 ( forecast_item.plan_id
52 , forecast_item.inventory_item_id
53 , forecast_item.organization_id
54 , forecast_item.sr_instance_id
55 , forecast_item.supplier_id
56 , forecast_item.supplier_site_id
57 , forecast_item.using_organization_id
58 , 1
59 , SYSDATE
60 , l_average_daily_demand
61 );
62
63 END LOOP; -- c_forecast_items
64 print_debug_info( 'End of average daily demand calculation engine');
65 EXCEPTION
66 WHEN OTHERS THEN
67 print_debug_info('Error in average daily demand calculation engine = ' || sqlerrm);
68 RAISE;
69 END calculate_average_demand;
70
71 -- calculate average daily demand
72 PROCEDURE calculate_average_demand_api
73 ( p_plan_id IN NUMBER
74 , p_inventory_item_id IN NUMBER
75 , p_organization_id IN NUMBER
76 , p_sr_instance_id IN NUMBER
77 , p_supplier_id IN NUMBER
78 , p_supplier_site_id IN NUMBER
79 , p_using_organization_id IN NUMBER
80 , p_update_flag IN NUMBER DEFAULT 1
81 , p_horizon_start_date IN DATE DEFAULT SYSDATE
82 , p_average_daily_demand OUT NOCOPY NUMBER
83 )
84 IS
85 l_total_supply_schedule NUMBER;
86 l_sce_customer_id NUMBER;
87 l_sce_organization_id NUMBER;
88 l_sce_supplier_id NUMBER;
89 l_sce_supplier_site_id NUMBER;
90
91 l_horizon_end_date DATE;
92 l_vmi_refresh_flag NUMBER;
93 l_forecast_horizon NUMBER;
94 l_old_average_daily_demand NUMBER;
95
96 lv_calendar_code varchar2(14);
97 lv_instance_id number;
98
99 CURSOR c_total_supply_schedule
100 ( p_plan_id NUMBER
101 , p_inventory_item_id NUMBER
102 , p_organization_id NUMBER
103 -- , p_sr_instance_id NUMBER
104 , p_supplier_id NUMBER
105 , p_supplier_site_id NUMBER
106 , p_horizon_end_date DATE
107 ) IS
108 SELECT SUM(sd.primary_quantity) total_demand
109 FROM msc_sup_dem_entries sd
110 WHERE sd.plan_id = p_plan_id
111 AND sd.inventory_item_id = p_inventory_item_id
112 AND sd.customer_site_id = p_organization_id
113 -- AND sd.sr_instance_id = p_sr_instance_id
114 AND sd.supplier_id = p_supplier_id
115 AND sd.supplier_site_id = p_supplier_site_id
116 AND TRUNC(receipt_date) BETWEEN TRUNC(p_horizon_start_date)
117 AND TRUNC(p_horizon_end_date + 1)
118 AND publisher_order_type = 2 -- order forecast
119 ;
120
121 CURSOR c_asl_attributes
122 ( p_inventory_item_id IN NUMBER
123 , p_plan_id IN NUMBER
124 , p_sr_instance_id IN NUMBER
125 , p_organization_id IN NUMBER
126 , p_supplier_id IN NUMBER
127 , p_supplier_site_id IN NUMBER
128 , p_using_organization_id IN NUMBER
129
130 ) IS
131 SELECT mis.forecast_horizon, mvt.average_daily_demand
132 FROM msc_item_suppliers mis
133 , msc_vmi_temp mvt
134 WHERE mis.inventory_item_id = p_inventory_item_id
135 AND mis.plan_id = p_plan_id
136 AND mis.sr_instance_id = p_sr_instance_id
137 AND mis.organization_id = p_organization_id
138 AND mis. supplier_id = p_supplier_id
139 AND mis. supplier_site_id = p_supplier_site_id
140 AND mis.using_organization_id = p_using_organization_id
141 and mvt.plan_id (+) = mis.plan_id
142 and mvt.inventory_item_id (+) = mis.inventory_item_id
143 and mvt.organization_id (+) = mis.organization_id
144 and mvt.sr_instance_id (+) = mis.sr_instance_id
145 and mvt.supplier_site_id (+) = mis.supplier_site_id
146 and mvt.supplier_id (+) = mis.supplier_id
147 and NVL (mvt.using_organization_id(+), 1) = NVL(mis.using_organization_id, -1)
148 and mvt.vmi_type (+) = 1 -- supplier facing vmi
149 ;
150
151 BEGIN
152
153 print_debug_info( ' plan/item/org/instance/supplier/supplier site/using org = '
154 || p_plan_id
155 || '/' || p_inventory_item_id
156 || '/' || p_organization_id
157 || '/' || p_sr_instance_id
158 || '/' || p_supplier_id
159 || '/' || p_supplier_site_id
160 || '/' || p_using_organization_id
161 );
162 l_sce_organization_id := msc_x_replenish.aps_to_sce(p_organization_id, MSC_X_REPLENISH.ORGANIZATION_MAPPING, p_sr_instance_id);
163 l_sce_supplier_id := msc_x_replenish.aps_to_sce(p_supplier_id, MSC_X_REPLENISH.COMPANY_MAPPING);
164 l_sce_supplier_site_id := msc_x_replenish.aps_to_sce(p_supplier_site_id, MSC_X_REPLENISH.SITE_MAPPING);
165
166 print_debug_info( ' cp org/cp supplier/cp supplier site = '
167 || l_sce_organization_id
168 || '/' || l_sce_supplier_id
169 || '/' || l_sce_supplier_site_id
170 );
171
172 OPEN c_asl_attributes
173 ( p_inventory_item_id
174 , p_plan_id
175 , p_sr_instance_id
176 , p_organization_id
177 , p_supplier_id
178 , p_supplier_site_id
179 , p_using_organization_id
180 );
181 FETCH c_asl_attributes INTO l_forecast_horizon, l_old_average_daily_demand;
182 CLOSE c_asl_attributes;
183
184 print_debug_info( ' forecast horizon/old average daily demand = '
185 || l_forecast_horizon
186 || '/' || l_old_average_daily_demand
187 );
188
189 if (nvl(l_forecast_horizon,0) > 0) then
190 /* Call the API to get the correct Calendar */
191
192 BEGIN
193 msc_x_util.get_calendar_code(
194 p_supplier_id,
195 p_supplier_site_id,
196 1, --- OEM
197 p_organization_id, -- oem Org
198 lv_calendar_code,
199 lv_instance_id,
200 2, -- TP ids are in terms of APS
201 p_sr_instance_id,
202 CUSTOMER_IS_OEM);
203 EXCEPTION
204 WHEN OTHERS THEN
205 IF (lv_calendar_code = '-1') THEN
206 print_user_info( ' Default calendar code is not correct, please check profile option MSC: Collaborative Planning Default Calendar.');
207 ELSE
208 RAISE;
209 END IF;
210 END;
211 print_debug_info(' Calendar/sr_instance_id : ' || lv_calendar_code||'/'||lv_instance_id);
212
213 l_horizon_end_date := MSC_CALENDAR.DATE_OFFSET(
214 lv_calendar_code -- arg_calendar_code IN varchar2,
215 , lv_instance_id -- arg_instance_id IN NUMBER,
216 , p_horizon_start_date -- arg_date IN DATE,
217 , l_forecast_horizon -- arg_offset IN NUMBER
218 , 99999 --arg_offset_type
219 );
220 end if;
221
222 print_debug_info( ' forecast horizon end date = '
223 || l_horizon_end_date
224 );
225
226 OPEN c_total_supply_schedule
227 ( p_plan_id
228 , p_inventory_item_id
229 , l_sce_organization_id
230 -- , p_sr_instance_id
231 , l_sce_supplier_id
232 , l_sce_supplier_site_id
233 , l_horizon_end_date
234 );
235 FETCH c_total_supply_schedule INTO l_total_supply_schedule;
236 CLOSE c_total_supply_schedule;
237
238 IF (l_forecast_horizon <> 0) AND (l_forecast_horizon IS NOT NULL) THEN
239 p_average_daily_demand := NVL(l_total_supply_schedule, 0)
240 / l_forecast_horizon;
241 ELSE
242 print_user_info( ' Forecast horizon is zero or NULL, please set up forecast horizon correctly');
243 p_average_daily_demand := 0;
244 END IF;
245
246 print_debug_info( ' total order forecast/average daily demand = '
247 || l_total_supply_schedule
248 || '/' || p_average_daily_demand
249 );
250
251 IF (p_update_flag = 1) THEN
252 IF (p_average_daily_demand <> l_old_average_daily_demand) THEN
253 l_vmi_refresh_flag := 1;
254 ELSE
255 l_vmi_refresh_flag := NULL;
256 END IF;
257
258 UPDATE msc_item_suppliers
259 SET -- average_daily_demand = p_average_daily_demand
260 vmi_refresh_flag = NVL(l_vmi_refresh_flag, vmi_refresh_flag)
261 WHERE plan_id = p_plan_id
262 AND inventory_item_id = p_inventory_item_id
263 AND organization_id = p_organization_id
264 AND sr_instance_id = p_sr_instance_id
265 AND supplier_id = p_supplier_id
266 AND supplier_site_id = p_supplier_site_id
267 AND using_organization_id = p_using_organization_id
268 ;
269 print_debug_info( ' vmi refresh flag updated, number of rows updated = '
270 || SQL%ROWCOUNT
271 );
272
273 UPDATE msc_vmi_temp
274 SET average_daily_demand = p_average_daily_demand
275 WHERE plan_id = p_plan_id
276 AND inventory_item_id = p_inventory_item_id
277 AND organization_id = p_organization_id
278 AND sr_instance_id = p_sr_instance_id
279 AND supplier_id = p_supplier_id
280 AND supplier_site_id = p_supplier_site_id
281 AND using_organization_id = p_using_organization_id
282 AND vmi_type = 1 -- supplier facing vmi
283 ;
284
285 print_debug_info( ' average daily demand updated, number of rows updated = '
286 || SQL%ROWCOUNT
287 );
288
289 IF (SQL%ROWCOUNT = 0 ) THEN
290 INSERT INTO msc_vmi_temp
291 ( PLAN_ID,
292 INVENTORY_ITEM_ID,
293 ORGANIZATION_ID ,
294 SR_INSTANCE_ID ,
295 SUPPLIER_ID ,
296 SUPPLIER_SITE_ID ,
297 USING_ORGANIZATION_ID ,
298 VMI_TYPE ,
299 AVERAGE_DAILY_DEMAND
300 ) VALUES
301 ( p_PLAN_ID,
302 p_INVENTORY_ITEM_ID,
303 p_ORGANIZATION_ID ,
304 p_SR_INSTANCE_ID ,
305 p_SUPPLIER_ID ,
306 p_SUPPLIER_SITE_ID ,
307 p_USING_ORGANIZATION_ID ,
308 1 ,
309 p_AVERAGE_DAILY_DEMAND
310 );
311 print_debug_info( ' average daily demand inserted, number of rows inserted = '
312 || SQL%ROWCOUNT
313 );
314
315 END IF;
316
317 END IF;
318
319 EXCEPTION
320 WHEN OTHERS THEN
321 print_debug_info('Error in average daily demand calculation ' || sqlerrm);
322 RAISE;
323 END calculate_average_demand_api;
324
325 -- This procesure prints out debug information
326 PROCEDURE print_debug_info(
327 p_debug_info IN VARCHAR2
328 )IS
329 BEGIN
330 IF ( g_msc_cp_debug= '1' OR g_msc_cp_debug = '2') THEN
331 FND_FILE.PUT_LINE(FND_FILE.LOG, p_debug_info);
332 END IF;
333 -- dbms_output.put_line(p_debug_info); --ut
334 EXCEPTION
335 WHEN OTHERS THEN
336 RAISE;
337 END print_debug_info;
338
339 -- This procesure prints out message to user
340 PROCEDURE print_user_info(
341 p_user_info IN VARCHAR2
342 ) IS
343 BEGIN
344 FND_FILE.PUT_LINE(FND_FILE.LOG, p_user_info);
345 -- dbms_output.put_line(p_user_info); --ut
346 EXCEPTION
347 WHEN OTHERS THEN
348 RAISE;
349 END print_user_info;
350
351 END MSC_X_PLANNING;