[Home] [Help]
PACKAGE BODY: APPS.MSD_SCE_RECEIVE_FORECAST_PKG
Source
1 PACKAGE BODY MSD_SCE_RECEIVE_FORECAST_PKG AS
2 /* $Header: msdxrcfb.pls 120.2.12010000.1 2008/05/15 08:48:14 vrepaka ship $ */
3
4 /** Bug 2488293 **/
5 Procedure delete_from_headers(
6 p_cs_definition_id in number,
7 p_designator in varchar2);
8
9 Procedure Insert_Data_Into_Headers(
10 p_cs_definition_id in number,
11 p_designator in varchar2,
12 p_refresh_num in number);
13
14 /** End Bug 2488293 **/
15
16 PROCEDURE receive_customer_forecast(
17 p_errbuf out NOCOPY varchar2,
18 p_retcode out NOCOPY varchar2,
19 p_designator in varchar2,
20 p_order_type in number,
21 p_org_code in varchar2,
22 p_planner_code in varchar2,
23 p_item_id in number,
24 p_customer_id in number,
25 p_customer_site_id in number default null, -- Bug # 4710963
26 p_horizon_start in varchar2,
27 p_horizon_days in number
28 ) IS
29
30
31 l_horizon_start Date; --canonical date
32 l_horizon_end Date;
33 p_cs_definition_id Number;
34 p_org_id Number;
35 p_sr_instance_id Number;
36 p_name Varchar2(30);
37
38
39 l_new_refresh_num NUMBER;
40
41
42 cursor get_cs_defn_id_c1(p_name IN Varchar2) IS
43 select cs_definition_id
44 from msd_cs_definitions
45 where name = p_name;
46
47 BEGIN
48
49 p_name := null;
50 p_cs_definition_id := null;
51
52 if p_horizon_start is null then
53
54 select decode(p_order_type, 4, sysdate-365, sysdate)
55 into l_horizon_start
56 from dual;
57
58 else
59
60 l_horizon_start := fnd_date.canonical_to_date(p_horizon_start);
61
62 end if;
63
64 l_horizon_end := l_horizon_start + nvl(p_horizon_days, 365);
65
66 -- dbms_output.put_line('l_horizon_start := ' || l_horizon_start);
67 -- dbms_output.put_line('l_horizon_end := ' || l_horizon_end);
68
69 /* Receive the net chanage sequence number */
70 SELECT msd.msd_last_refresh_number_s.nextval into l_new_refresh_Num from dual;
71
72 if p_org_code is not null then
73
74 select sr_tp_id, sr_instance_id
75 into p_org_id, p_sr_instance_id
76 from msc_trading_partners
77 where organization_code = p_org_code and
78 partner_type = 3 and
79 company_id is null;
80 --dbms_output.put_line('p_org_id := ' || p_org_id);
81 --dbms_output.put_line('p_sr_instance_id := ' || p_sr_instance_id);
82 else
83 p_org_id := null;
84 p_sr_instance_id := null;
85 end if;
86
87 If p_order_type = 1 then
88 p_name := 'MSD_CUSTOMER_SALES_FORECAST';
89 elsif p_order_type = 2 then
90 p_name := 'MSD_CUSTOMER_ORDER_FORECAST';
91 elsif p_order_type = 4 then
92 p_name := 'MSD_CUSTOMER_HISTORICAL_SALES';
93 end if;
94
95 open get_cs_defn_id_c1(p_name);
96 fetch get_cs_defn_id_c1 into p_cs_definition_id;
97 close get_cs_defn_id_c1;
98
99 if p_cs_definition_id is not null then
100
101 delete_old_forecast(
102 p_sr_instance_id
103 ,p_cs_definition_id
104 ,p_designator
105 ,p_org_id
106 ,p_item_id -- Bug 4710963
107 ,p_customer_id -- Bug 4710963
108 ,p_customer_site_id -- Bug 4710963
109 ,l_horizon_start
110 ,l_horizon_end,
111 l_new_refresh_Num
112 );
113
114 insert into msd_cs_data (
115 cs_data_id,
116 cs_definition_id,
117 cs_name,
118 attribute_1,
119 attribute_2,
120 attribute_3,
121 attribute_4,
122 attribute_5,
123 attribute_6,
124 attribute_7,
125 attribute_8,
126 attribute_9,
127 attribute_10,
128 attribute_11,
129 attribute_12,
130 attribute_13,
131 attribute_34,
132 attribute_41,
133 attribute_43,
134 attribute_45,
135 attribute_50, -- Bug 4710963
136 attribute_51, -- Bug 4710963
137 attribute_52, -- Bug 4710963
138 attribute_53, -- Bug 4710963
139 created_by,
140 creation_date,
141 last_updated_by,
142 last_update_date,
143 last_update_login,
144 action_code,
145 last_refresh_num,
146 created_by_refresh_num
147 )
148 ( SELECT
149 msd_cs_data_s.nextval,
150 p_cs_definition_id,
151 p_designator,
152 t1.sr_instance_id, -- Bug 5729146
153 1,
154 lv4.sr_level_pk,
155 lv4.level_value,
156 lv4.level_pk,
157 11,
158 lv2.sr_level_pk,
159 lv2.level_value,
160 lv2.level_pk,
161 7,
162 lv3.sr_level_pk,
163 lv3.level_value,
164 lv3.level_pk,
165 9, -- bucket type 'Day'
166 MSD_COMMON_UTILITIES.msd_uom_convert(ilp.sr_item_pk, null, sd.tp_uom_code, ilp.base_uom) * sd.tp_quantity,
167 to_char(decode(p_order_type, 4, sd.new_schedule_date, nvl(sd.ship_date, decode(nvl(ps.shipping_control, 'BUYER'), 'BUYER', sd.key_date, sd.key_date - get_intrasit_lead_time(t1.sr_instance_id, t1.sr_tp_id, s.location_id)))), 'YYYY/MM/DD'),
168 to_char(decode(p_order_type, 4, to_date(null), nvl(sd.receipt_date, decode(nvl(ps.shipping_control, 'BUYER'), 'SUPPLIER', sd.key_date, sd.key_date + get_intrasit_lead_time(t1.sr_instance_id, t1.sr_tp_id, s.location_id)))), 'YYYY/MM/DD'),
169 34, -- Bug 4710963
170 lv5.sr_level_pk, -- Bug 4710963
171 lv5.level_value, -- Bug 4710963
172 lv5.level_pk, -- Bug 4710963
173 fnd_global.user_id,
174 sysdate,
175 fnd_global.user_id,
176 sysdate,
177 fnd_global.login_id,
178 'I',
179 l_new_refresh_Num,
180 l_new_refresh_Num
181 FROM msc_sup_dem_entries sd,
182 msd_level_values lv2,
183 msd_level_values lv3,
184 msd_level_values lv4,
185 msd_level_values lv5, -- Bug 4710963
186 msc_trading_partner_maps m2,
187 msc_trading_partners t1,
188 msc_item_id_lid item,
189 msc_tp_site_id_lid s,
190 msc_trading_partner_sites ps,
191 msd_item_list_price ilp
192 WHERE ilp.sr_item_pk(+) = lv4.sr_level_pk and
193 ilp.instance(+) = lv4.instance and
194 sd.inventory_item_id = item.inventory_item_id and
195 item.sr_instance_id = t1.sr_instance_id and
196 lv4.instance = t1.sr_instance_id and
197 lv4.sr_level_pk = to_char(item.sr_inventory_item_id) and
198 lv4.level_id = 1 and
199 -- Mapping for Customer Site
200 lv2.instance = t1.sr_instance_id and
201 lv2.sr_level_pk = to_char(s.sr_tp_site_id) and
202 lv2.level_id = 11 and
203 s.sr_tp_site_id = get_sr_tp_site_id(sd.customer_site_id, t1.sr_instance_id) and
204 s.sr_instance_id = t1.sr_instance_id and
205 s.partner_type = 2 and
206 nvl(s.sr_company_id, -1) = -1 and
207 --s.tp_site_id = sd.customer_site_id and
208 ps.partner_site_id = s.tp_site_id and
209 -- Mapping for Demand Class -- Bug 4710963
210 lv5.instance = t1.sr_instance_id and
211 lv5.sr_level_pk = nvl(sd.demand_class,'-777') and -- Bug 4710963
212 lv5.level_id = 34 and -- Bug 4710963
213 -- Mapping for Supplier Org
214 lv3.instance = t1.sr_instance_id and
215 lv3.sr_level_pk = to_char(t1.sr_tp_id) and
216 lv3.level_id = 7 and
217 m2.company_key = sd.supplier_site_id and
218 m2.map_type = 2 and
219 t1.partner_id = m2.tp_key and
220 t1.partner_type = 3 and
221 ps.partner_id = NVL(p_customer_id, ps.partner_id) and
222 s.tp_site_id = NVL(p_customer_site_id, s.tp_site_id) and
223 t1.sr_tp_id = NVL(p_org_id, t1.sr_tp_id) and
224 item.sr_instance_id = NVL(p_sr_instance_id, item.sr_instance_id) and
225 item.inventory_item_id = nvl(p_item_id, item.inventory_item_id) and
226 NVL(sd.planner_code,'-99') = NVL(p_planner_code, NVL(sd.planner_code,'-99')) and
227 sd.publisher_order_type = p_order_type and
228 sd.plan_id = -1 and
229 sd.supplier_id = 1 and
230 decode(p_order_type, 4, sd.new_schedule_date, sd.key_date) between l_horizon_start and l_horizon_end);
231
232 if (sql%rowcount = 0) then
233
234 p_errbuf := 'There were no rows fetched.';
235 p_retcode := 1;
236
237 else
238
239 /* Bug 2488293. Insert data into headers table. */
240 insert_data_into_Headers(p_cs_definition_id,p_designator,l_new_refresh_num);
241 /* End Bug 2488293 */
242
243 end if;
244
245 commit;
246
247 else
248 p_retcode :=-1;
249 p_errbuf := 'Error while getting p_cs_definition_id';
250
251 end if;
252
253 exception
254
255 when others then
256
257 p_errbuf := substr(SQLERRM,1,150);
258 p_retcode := -1;
259 rollback;
260
261 END receive_customer_forecast;
262
263
264 PROCEDURE delete_old_forecast(
265 p_sr_instance_id in number,
266 p_cs_definition_id in number,
267 p_designator in varchar2,
268 p_org_id in number,
269 p_item_id in number, -- Bug 4710963
270 p_customer_id in number, -- Bug 4710963
271 p_customer_site_id in number, -- Bug 4710963
272 l_horizon_start in date,
273 l_horizon_end in date,
274 p_new_fresh_num in number
275 ) IS
276
277
278 errbuf VARCHAR2(150);
279 retcode VARCHAR2(150);
280
281 p_sr_item_pk number; -- Bug 4710963
282 p_sr_ship_to_loc_pk number; -- Bug 4710963
283
284 -- Bug 4710963
285 cursor c_sr_item_pk is
286 select sr_inventory_item_id
287 from msc_system_items
288 where plan_id = -1
289 and sr_instance_id = nvl(p_sr_instance_id,sr_instance_id)
290 and inventory_item_id = p_item_id
291 and organization_id = nvl(p_org_id,organization_id)
292 and rownum < 2;
293
294
295 cursor c_sr_ship_to_loc_pk is
296 select sr_tp_site_id
297 from msc_trading_partners tp, msc_trading_partner_sites tps
298 where tp.partner_id = p_customer_id
299 and tps.partner_site_id = p_customer_site_id
300 and tps.partner_id = tp.partner_id
301 and tps.partner_type = 2;
302
303
304 BEGIN
305 /*
306 delete from msd_cs_data
307 where cs_name = p_designator
308 and cs_definition_id = p_cs_definition_id
309 and attribute_11 = nvl(to_char(p_org_id), attribute_11)
310 and attribute_1 = nvl(to_char(p_sr_instance_id), attribute_1)
311 and attribute_43 between to_char(l_horizon_start, 'YYYY/MM/DD') and to_char(l_horizon_end, 'YYYY/MM/DD');
312 */
313
314 /* Enable Net-Change. Instead of physically deleteing the forecast,
315 update it with action_code = D */
316
317 if p_item_id is not null then
318 open c_sr_item_pk;
319 fetch c_sr_item_pk INTO p_sr_item_pk;
320 close c_sr_item_pk;
321 else
322 p_sr_item_pk := to_number(NULL);
323 end if;
324
325 if p_customer_site_id is not null then
326 open c_sr_ship_to_loc_pk;
327 fetch c_sr_ship_to_loc_pk INTO p_sr_ship_to_loc_pk;
328 close c_sr_ship_to_loc_pk;
329 else
330 p_sr_ship_to_loc_pk :=to_number(NULL);
331 end if;
332
333
334 update msd_cs_data
335 set action_code = 'D'
336 where cs_name = p_designator
337 and cs_definition_id = p_cs_definition_id
338 and attribute_11 = nvl(to_char(p_org_id), attribute_11)
339 and attribute_7 = nvl(to_char(p_sr_ship_to_loc_pk), attribute_7)
340 and attribute_3 = nvl(to_char(p_sr_item_pk), attribute_3)
341 and attribute_1 = nvl(to_char(p_sr_instance_id), attribute_1)
342 and attribute_43 between to_char(l_horizon_start, 'YYYY/MM/DD')
343 and to_char(l_horizon_end, 'YYYY/MM/DD');
344
345 /* Delete rows that are not used by any demand plans */
346 MSD_TRANSLATE_FACT_DATA.clean_fact_data( errbuf,
347 retcode,
348 'MSD_CS_DATA');
349
350
351 /* Bug 2488293. Delets data from headers table. */
352 delete_from_headers(p_cs_definition_id,p_designator);
353 /* End Bug 2488293 */
354
355
356
357 END delete_old_forecast;
358
359 FUNCTION get_intrasit_lead_time(
360 p_from_instance_id in number,
361 p_from_organization_id in number,
362 p_to_location_id in number
363 ) return number is
364
365 cursor c2 is
366 select intransit_time
367 from msc_orgcustomer_ship_methods_v
368 where from_sr_instance_id(+) = p_from_instance_id and
369 from_organization_id(+) = p_from_organization_id and
370 to_location_id (+) = p_to_location_id and
371 default_flag(+) = 1;
372
373 l_ret number := null;
374
375 Begin
376
377 open c2;
378 fetch c2 into l_ret;
379 close c2;
380 return nvl(l_ret, 0);
381
382 End get_intrasit_lead_time;
383
384
385
386 FUNCTION get_sr_tp_site_id(
387 p_customer_site_id in number,
388 p_sr_instance_id in number
389 ) return number is
390
391 cursor c1 is
392 select slid.sr_tp_site_id
393 from
394 msc_tp_site_id_lid slid,
395 msc_trading_partner_maps map,
396 msc_trading_partner_sites site,
397 msd_level_values lvl
398 where
399 map.company_key = p_customer_site_id and
400 map.map_type = 3 and
401 slid.tp_site_id = map.tp_key and
402 slid.sr_instance_id = p_sr_instance_id and
403 slid.partner_type = 2 and
404 nvl(slid.sr_company_id, -1) = -1 and
405 site.partner_site_id = slid.tp_site_id and
406 site.tp_site_code = 'SHIP_TO' and
407 lvl.instance = p_sr_instance_id and
408 lvl.sr_level_pk = to_char(slid.sr_tp_site_id) and
409 lvl.level_id = 11;
410
411 l_ret number := null;
412 Begin
413
414 open c1;
415 fetch c1 into l_ret;
416 close c1;
417 return l_ret;
418
419 End get_sr_tp_site_id;
420
421
422 /* This procedure will delete the data from the msd_cs_data_headers
423 * table.
424 *
425 * Bug 2488293.
426 */
427
428 Procedure delete_from_headers
429 (p_cs_definition_id in number,
430 p_designator in varchar2) IS
431
432 BEGIN
433
434 DELETE from msd_cs_data_headers mcdh
435 where
436 cs_definition_id = p_cs_definition_id
437 and cs_name = p_designator
438 and not exists
439 (select 1
440 from msd_cs_data mcd
441 where mcd.cs_definition_id = mcdh.cs_definition_id
442 and mcd.cs_name = mcdh.cs_name
443 and mcd.attribute_1 = mcdh.instance
444 and mcd.action_code = 'I'
445 and rownum = 1);
446
447 Exception
448 When others then
449 fnd_file.put_line(fnd_file.log, 'Error in deleting from MSD_CS_DATA_HEADERS');
450 fnd_file.put_line(fnd_file.log, sqlerrm);
451 raise;
452
453 End delete_from_headers;
454
455
456 /* This procedure will insert cs_definition_id, cs_name, and instance into
457 * msd_cs_data_headers table.
458 * Bug 2488293.
459 */
460 Procedure Insert_Data_Into_Headers
461 (p_cs_definition_id in number,
462 p_designator in varchar2,
463 p_refresh_num in number) IS
464
465 BEGIN
466
467 insert into msd_cs_data_headers
468 (
469 cs_data_header_id,
470 instance,
471 cs_definition_id,
472 cs_name,
473 last_update_date,
474 last_updated_by,
475 creation_date,
476 created_by,
477 last_update_login,
478 last_refresh_num
479 )
480 select msd_cs_data_headers_s.nextval,
481 mcd.instance,
482 mcd.cs_definition_id,
483 mcd.cs_name,
484 sysdate,
485 fnd_global.user_id,
486 sysdate,
487 fnd_global.user_id,
488 fnd_global.login_id,
489 p_refresh_num
490 from
491 (
492 select distinct attribute_1 instance, cs_definition_id, cs_name
493 from msd_cs_data
494 where cs_definition_id = p_cs_definition_id
495 and cs_name = p_designator
496 minus
497 select instance, cs_definition_id, cs_name
498 from msd_cs_data_headers
499 ) mcd;
500
501 if (sql%rowcount = 0) then
502
503 update msd_cs_data_headers
504 set last_refresh_num = p_refresh_num,
505 last_update_date = sysdate,
506 last_updated_by = fnd_global.user_id,
507 last_update_login = fnd_global.login_id
508 where cs_definition_id = p_cs_definition_id
509 and cs_name = p_designator;
510
511 end if;
512
513 Exception
514 When others then
515 fnd_file.put_line(fnd_file.log, 'Error in inserting into MSD_CS_DATA_HEADERS');
516 fnd_file.put_line(fnd_file.log, sqlerrm);
517 raise;
518
519 END Insert_Data_Into_Headers;
520
521
522
523
524 END MSD_SCE_RECEIVE_FORECAST_PKG;