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