[Home] [Help]
PACKAGE BODY: APPS.MSD_SCE_PUBLISH_FORECAST_PKG
Source
1 PACKAGE BODY MSD_SCE_PUBLISH_FORECAST_PKG AS
2 /* $Header: msdxpcfb.pls 115.10 2004/07/15 22:26:59 esubrama ship $ */
3
4 PROCEDURE publish_customer_forecast (
5 p_errbuf out NOCOPY varchar2,
6 p_retcode out NOCOPY varchar2,
7 p_designator in varchar2,
8 p_order_type in number,
9 p_demand_plan_id in number,
10 p_scenario_id in number,
11 p_forecast_date in varchar2,
12 p_org_code in varchar2,
13 p_planner_code in varchar2,
14 -- p_abc_class in varchar2,
15 p_item_id in number,
16 p_customer_id in number,
17 p_customer_site_id in number,
18 p_horizon_start in varchar2,
19 p_horizon_days in number,
20 p_auto_version in number,
21 p_version in number
22 ) IS
23
24 p_org_id Number;
25 p_sr_instance_id Number;
26 p_horizon_end date;
27 l_horizon_start date; --canonical date
28 l_version Number;
29 l_order_type Varchar2(80);
30
31 t_pub companyNameList;
32 t_pub_id numberList;
33 t_pub_site companySiteList;
34 t_pub_site_id numberList;
35 t_item_id numberList;
36 t_qty numberList;
37 t_pub_ot numberList;
38 t_cust companyNameList;
39 t_cust_id numberList;
40 t_cust_site companySiteList;
41 t_cust_site_id numberList;
42 t_ship_from companyNameList;
43 t_ship_from_id numberList;
44 t_ship_from_site companySiteList;
45 t_ship_from_site_id numberList;
46 t_ship_to companyNameList;
47 t_ship_to_id numberList;
48 t_ship_to_site companySiteList;
49 t_ship_to_site_id numberList;
50 t_bkt_type numberList;
51 t_posting_party_id numberList;
52 t_item_name itemNameList;
53 t_item_desc itemDescList;
54 t_pub_ot_desc fndMeaningList;
55 t_bkt_type_desc fndMeaningList;
56 t_posting_party_name companyNameList;
57 t_uom_code itemUomList;
58 t_planner_code plannerCodeList;
59 t_end_date dateList;
60 t_ship_date dateList;
61 t_receipt_date dateList;
62 -- t_src_cust_id numberList;
63 t_tp_cust_id numberList;
64 t_src_cust_site_id numberList;
65 t_src_org_id numberList;
66 t_src_instance_id numberList;
67 t_shipping_control shippingControlList;
68 t_lead_time numberList;
69
70 t_tp_uom itemUomList := itemUomList();
71 t_tp_qty numberList := numberList();
72 t_tp_ship_date dateList := dateList();
73 t_tp_receipt_date dateList := dateList();
74 t_master_item_name itemNameList := itemNameList();
75 t_master_item_desc itemDescList := itemDescList();
76 t_cust_item_name itemNameList := itemNameList();
77 t_cust_item_desc itemDescList := itemDescList();
78
79
80 CURSOR publish_cust_fcst_c1 (
81 p_scenario_id in number,
82 p_org_id in number,
83 p_sr_instance_id in number,
84 l_horizon_start in date,
85 p_horizon_end in date,
86 p_planner_code in varchar2,
87 -- p_abc_class in varchar2,
88 p_item_id in number,
89 p_customer_id in number,
90 p_customer_site_id in number
91 ) IS
92 select c.company_name, --publisher
93 c.company_id, --publisher id
94 cs.company_site_name, --publisher site
95 cs.company_site_id, --publisher site id
96 item.inventory_item_id, --inventory item id
97 round(fcst.quantity, 6), --quantity
98 p_order_type, --publisher order type
99 c1.company_name, --customer name
100 c1.company_id, --customer id
101 cs1.company_site_name, --customer site
102 cs1.company_site_id, --customer site id
103 c.company_name, --ship from
104 c.company_id, --ship from id
105 cs.company_site_name, --ship from site
106 cs.company_site_id, --ship from site id
107 c1.company_name, --ship to
108 c1.company_id, --ship to id
109 cs1.company_site_name, --ship to site
110 cs1.company_site_id, --ship to site id
111 fcst.bucket_type, --bucket type
112 c.company_id, --posting party id
113 item.item_name, --publisher item name
114 item.description, --publisher item desc
115 l_order_type, --publisher order type desc
116 fcst.bucket_type, --bucket type desc
117 c.company_name, --posting supplier name [Owner]
118 fcst.uom_code, --primary uom
119 item.planner_code, --planner code
120 fcst.end_date, --end date
121 fcst.start_date, --ship date
122 fcst.start_date, --receipt date
123 -- ti1.sr_tp_id, --Source Partner Id
124 ts1.partner_id, --Partner_id
125 tsi1.sr_tp_site_id, --Source Partner Site Id
126 fcst.sr_organization_id, --Source Partner Org Id
127 fcst.sr_instance_id, --Source Partner Instance Id,
128 ts1.shipping_control, --Shipping control method
129 MSD_SCE_RECEIVE_FORECAST_PKG.get_intrasit_lead_time(t.sr_instance_id, t.sr_tp_id, tsi1.location_id)
130 from msd_dp_sce_scn_entries_v fcst,
131 msc_system_items item,
132 msc_company_sites cs,
133 msc_company_sites cs1,
134 msc_companies c,
135 msc_companies c1,
136 msc_trading_partner_maps m,
137 msc_trading_partner_maps m2,
138 msc_trading_partners t,
139 msc_tp_site_id_lid tsi1,
140 msc_trading_partner_sites ts1
141 -- msc_tp_id_lid ti1
142 where fcst.sr_instance_id = item.sr_instance_id and
143 fcst.sr_organization_id = item.organization_id and
144 fcst.sr_inventory_item_id = item.sr_inventory_item_id and
145 item.plan_id = -1 and
146 /* Mapping Organization */
147 t.sr_tp_id = fcst.sr_organization_id and
148 t.sr_instance_id = fcst.sr_instance_id and
149 t.partner_type = 3 and
150 m.tp_key = t.partner_id and
151 m.map_type = 2 and
152 m.company_key = cs.company_site_id and
153 c.company_id = cs.company_id and
154 /* Mapping Customer Site */
155 tsi1.sr_tp_site_id = fcst.sr_geography_id and
156 tsi1.sr_instance_id = fcst.sr_instance_id and
157 tsi1.partner_type = 2 and
158 nvl(tsi1.sr_company_id, -1) = -1 and
159 m2.tp_key = tsi1.tp_site_id and
160 m2.map_type = 3 and
161 cs1.company_site_id = m2.company_key and
162 cs1.company_id = c1.company_id and
163 /* Mapping Customer site - for source Customer Site Id */
164 ts1.partner_site_id = tsi1.tp_site_id and
165 /* Mapping Customer - for source Customer Id */
166 -- ti1.tp_id = ts1.partner_id and
167 -- ti1.sr_instance_id = fcst.sr_instance_id and
168 -- ti1.partner_type = 2 and
169 -- nvl(ti1.sr_company_id, -1) = -1 and
170 /* Filter conditions */
171 ts1.partner_id = NVL(p_customer_id, ts1.partner_id) and
172 ts1.partner_site_id = NVL(p_customer_site_id, ts1.partner_site_id) and
173 -- NVL(item.abc_class_name,'-99') = NVL(p_abc_class, NVL(item.abc_class_name,'-99')) and
174 NVL(item.planner_code,'-99') = NVL(p_planner_code, NVL(item.planner_code,'-99')) and
175 item.inventory_item_id = nvl(p_item_id, item.inventory_item_id ) and
176 item.organization_id = NVL(p_org_id, item.organization_id) and
177 item.sr_instance_id = NVL(p_sr_instance_id, item.sr_instance_id) and
178 fcst.scenario_id = p_scenario_id and
179 fcst.start_date between nvl(l_horizon_start, sysdate) and nvl(p_horizon_end, sysdate+365);
180
181
182 BEGIN
183 l_horizon_start := fnd_date.canonical_to_date(p_horizon_start);
184 p_horizon_end := nvl(l_horizon_start, sysdate)+nvl(p_horizon_days, 365);
185
186 select meaning
187 into l_order_type
188 from fnd_lookup_values_vl
189 where lookup_type = 'MSC_X_ORDER_TYPE'
190 and lookup_code = p_order_type;
191
192 if p_org_code is not null then
193
194 select sr_tp_id, sr_instance_id
195 into p_org_id, p_sr_instance_id
196 from msc_trading_partners
197 where organization_code = p_org_code and
198 partner_type = 3 and
199 company_id is null;
200 -- dbms_output.put_line('p_org_id := ' || p_org_id);
201 -- dbms_output.put_line('p_sr_instance_id := ' || p_sr_instance_id);
202 else
203 p_org_id := null;
204 p_sr_instance_id := null;
205 end if;
206
207 if nvl(p_auto_version, 1) = 1 then
208 l_version := p_version + 1;
209 else
210 l_version := null;
211 end if;
212
213 delete_old_forecast(
214 p_org_id,
215 p_sr_instance_id,
216 p_planner_code,
217 -- p_abc_class,
218 p_item_id,
219 p_customer_id,
220 p_customer_site_id,
221 l_horizon_start,
222 p_horizon_end
223 );
224
225
226 OPEN publish_cust_fcst_c1 (
227 p_scenario_id
228 ,p_org_id
229 ,p_sr_instance_id
230 ,l_horizon_start
231 ,p_horizon_end
232 ,p_planner_code
233 -- ,p_abc_class
234 ,p_item_id
235 ,p_customer_id
236 ,p_customer_site_id
237 );
238
239 FETCH publish_cust_fcst_c1 BULK COLLECT INTO
240 t_pub
241 ,t_pub_id
242 ,t_pub_site
243 ,t_pub_site_id
244 ,t_item_id
245 ,t_qty
246 ,t_pub_ot
247 ,t_cust
248 ,t_cust_id
249 ,t_cust_site
250 ,t_cust_site_id
251 ,t_ship_from
252 ,t_ship_from_id
253 ,t_ship_from_site
254 ,t_ship_from_site_id
255 ,t_ship_to
256 ,t_ship_to_id
257 ,t_ship_to_site
258 ,t_ship_to_site_id
259 ,t_bkt_type
260 ,t_posting_party_id
261 ,t_item_name
262 ,t_item_desc
263 ,t_pub_ot_desc
264 ,t_bkt_type_desc
265 ,t_posting_party_name
266 ,t_uom_code
267 ,t_planner_code
268 ,t_end_date
269 ,t_ship_date
270 ,t_receipt_date
271 ,t_tp_cust_id
272 ,t_src_cust_site_id
273 ,t_src_org_id
274 ,t_src_instance_id
275 ,t_shipping_control
276 ,t_lead_time;
277 CLOSE publish_cust_fcst_c1;
278
279
280 IF t_pub IS NOT NULL AND t_pub.COUNT > 0 THEN
281 -- dbms_output.put_line ('Records fetched by cursor := ' || t_pub.COUNT);
282
283 get_optional_info(
284 t_item_id,
285 t_pub_id,
286 t_cust_id,
287 t_cust_site_id,
288 t_tp_cust_id,
289 t_src_cust_site_id,
290 t_src_org_id,
291 t_src_instance_id,
292 t_item_name,
293 t_uom_code,
294 t_qty,
295 t_ship_date,
296 t_receipt_date,
297 t_tp_ship_date,
298 t_tp_receipt_date,
299 t_master_item_name,
300 t_master_item_desc,
301 t_cust_item_name,
302 t_cust_item_desc,
303 t_tp_uom,
304 t_tp_qty,
305 t_lead_time,
306 p_forecast_date
307 );
308
309 explode_dates (
310 t_pub,
311 t_pub_id,
312 t_pub_site,
313 t_pub_site_id,
314 t_item_id,
315 t_qty,
316 t_pub_ot,
317 t_cust,
318 t_cust_id,
319 t_cust_site,
320 t_cust_site_id,
321 t_ship_from,
322 t_ship_from_id,
323 t_ship_from_site,
324 t_ship_from_site_id,
325 t_ship_to,
326 t_ship_to_id,
327 t_ship_to_site,
328 t_ship_to_site_id,
329 t_bkt_type,
330 t_posting_party_id,
331 t_item_name,
332 t_item_desc,
333 t_pub_ot_desc,
334 t_bkt_type_desc,
335 t_posting_party_name,
336 t_uom_code,
337 t_planner_code,
338 t_end_date,
339 t_ship_date,
340 t_tp_ship_date,
341 t_receipt_date,
342 t_tp_receipt_date,
343 t_master_item_name,
344 t_master_item_desc,
345 t_cust_item_name,
346 t_cust_item_desc,
347 t_tp_uom,
348 t_tp_qty
349 );
350
351
352 insert_into_sup_dem(
353 t_pub
354 ,t_pub_id
355 ,t_pub_site
356 ,t_pub_site_id
357 ,t_item_id
358 ,t_qty
359 ,t_pub_ot
360 ,t_cust
361 ,t_cust_id
362 ,t_cust_site
363 ,t_cust_site_id
364 ,t_ship_from
365 ,t_ship_from_id
366 ,t_ship_from_site
367 ,t_ship_from_site_id
368 ,t_ship_to
369 ,t_ship_to_id
370 ,t_ship_to_site
371 ,t_ship_to_site_id
372 ,t_bkt_type
373 ,t_posting_party_id
374 ,t_item_name
375 ,t_item_desc
376 ,t_master_item_name
377 ,t_master_item_desc
378 ,t_cust_item_name
379 ,t_cust_item_desc
380 ,t_pub_ot_desc
381 ,t_bkt_type_desc
382 ,t_posting_party_name
383 ,t_uom_code
384 ,t_planner_code
385 ,t_tp_ship_date
386 ,t_tp_receipt_date
387 ,t_tp_uom
388 ,t_tp_qty
389 ,l_version
390 ,p_designator
391 ,t_shipping_control
392 );
393
394 commit;
395 p_errbuf := 'Total records processed := ' || t_pub.COUNT;
396
397 else
398 p_errbuf := 'There were no rows fetched.';
399 p_retcode := 1;
400
401 end if;
402
403 exception
404
405 when others then
406
407 p_errbuf := substr(SQLERRM,1,150);
408 p_retcode := -1;
409 rollback;
410
411 END publish_customer_forecast;
412
413 PROCEDURE explode_dates (
414 t_pub IN OUT NOCOPY companyNameList,
415 t_pub_id IN OUT NOCOPY numberList,
416 t_pub_site IN OUT NOCOPY companySiteList,
417 t_pub_site_id IN OUT NOCOPY numberList,
418 t_item_id IN OUT NOCOPY numberList,
419 t_qty IN OUT NOCOPY numberList,
420 t_pub_ot IN OUT NOCOPY numberList,
421 t_cust IN OUT NOCOPY companyNameList,
422 t_cust_id IN OUT NOCOPY numberList,
423 t_cust_site IN OUT NOCOPY companySiteList,
424 t_cust_site_id IN OUT NOCOPY numberList,
425 t_ship_from IN OUT NOCOPY companyNameList,
426 t_ship_from_id IN OUT NOCOPY numberList,
427 t_ship_from_site IN OUT NOCOPY companySiteList,
428 t_ship_from_site_id IN OUT NOCOPY numberList,
429 t_ship_to IN OUT NOCOPY companyNameList,
430 t_ship_to_id IN OUT NOCOPY numberList,
431 t_ship_to_site IN OUT NOCOPY companySiteList,
432 t_ship_to_site_id IN OUT NOCOPY numberList,
433 t_bkt_type IN OUT NOCOPY numberList,
434 t_posting_party_id IN OUT NOCOPY numberList,
435 t_item_name IN OUT NOCOPY itemNameList,
436 t_item_desc IN OUT NOCOPY itemDescList,
437 t_pub_ot_desc IN OUT NOCOPY fndMeaningList,
438 t_bkt_type_desc IN OUT NOCOPY fndMeaningList,
439 t_posting_party_name IN OUT NOCOPY companyNameList,
440 t_uom_code IN OUT NOCOPY itemUomList,
441 t_planner_code IN OUT NOCOPY plannerCodeList,
442 t_end_date IN OUT NOCOPY dateList,
443 t_ship_date IN OUT NOCOPY dateList,
444 t_tp_ship_date IN OUT NOCOPY dateList,
445 t_receipt_date IN OUT NOCOPY dateList,
446 t_tp_receipt_date IN OUT NOCOPY dateList,
447 t_master_item_name IN OUT NOCOPY itemNameList,
448 t_master_item_desc IN OUT NOCOPY itemDescList,
449 t_cust_item_name IN OUT NOCOPY itemNameList,
450 t_cust_item_desc IN OUT NOCOPY itemDescList,
451 t_tp_uom IN OUT NOCOPY itemUomList,
452 t_tp_qty IN OUT NOCOPY numberList
453 ) IS
454
455 numFirst NUMBER := t_item_id.FIRST;
456 numLast NUMBER := t_item_id.LAST;
457 p_qty_per_day NUMBER;
458 p_curr_date DATE;
459 p_curr_month_start_date DATE;
460 p_curr_month_end_date DATE;
461 new_qty NUMBER(15,6);
462 numInsertIndex NUMBER;
463 p_first_insert boolean;
464 p_done boolean := FALSE;
465 l_bkt_desc varchar2(80);
466
467 begin
468
469 for j in numFirst..numLast loop
470
471 select meaning
472 into l_bkt_desc
473 from fnd_lookup_values_vl
474 where lookup_type = 'MSC_X_BUCKET_TYPE' and
475 lookup_code = decode(t_bkt_type(j), 9, 1, 1, 2, 3);
476
477 t_bkt_type_desc(j) := l_bkt_desc;
478
479 p_first_insert := TRUE;
480 /* Day */
481 if (t_bkt_type(j) = 9) then
482 t_bkt_type(j) := 1;
483 /* Fiscal Month, Manufacturing Period */
484 elsif (t_bkt_type(j) in (2,3)) then
485 t_bkt_type(j) := 3;
486 t_ship_date(j) := TRUNC(t_ship_date(j), 'MONTH');
487
488 /* Manufacturing Week */
489 elsif (t_bkt_type(j) = 1) then
490 t_bkt_type(j) := 2;
491 t_ship_date(j) := next_day(t_ship_date(j),
492 to_char(to_date('11/03/1997', 'DD/MM/RRRR'), 'DY')) - 7;
493
494 else
495 /* Rest move to Month */
496 t_bkt_type(j) := 3;
497
498 /* Quantity per day equals total quantity divided by number of days */
499 p_qty_per_day := t_qty(j) / (t_end_date(j) - t_ship_date(j));
500 p_curr_date := t_ship_date(j);
501
502 LOOP
503 p_curr_month_start_date := TRUNC(p_curr_date, 'MONTH');
504 p_curr_month_end_date := LAST_DAY(p_curr_date);
505
506 if (p_curr_month_start_date = p_curr_date
507 AND
508 p_curr_month_end_date < t_end_date(j)) then
509 new_qty := p_qty_per_day * (p_curr_month_end_date - p_curr_month_start_date);
510 p_curr_date := p_curr_month_end_date + 1;
511 elsif (p_curr_month_start_date <> p_curr_date
512 AND
513 p_curr_month_end_date < t_end_date(j)) then
514 new_qty := p_qty_per_day * (p_curr_month_end_date - p_curr_date);
515 p_curr_date := p_curr_month_end_date + 1;
516 else
517 new_qty := p_qty_per_day * (t_end_date(j) - p_curr_month_start_date);
518 p_done := TRUE;
519 end if;
520
521 if (p_first_insert) then
522 numInsertIndex := j;
523 p_first_insert := FALSE;
524 else
525 numInsertIndex := t_pub.LAST + 1;
526 end if;
527 if (numInsertIndex > t_pub.LAST) then
528 t_pub.EXTEND;
529 t_pub_id.EXTEND;
530 t_pub_site.EXTEND;
531 t_pub_site_id.EXTEND;
532 t_item_id.EXTEND;
533 t_qty.EXTEND;
534 t_pub_ot.EXTEND;
535 t_cust.EXTEND;
536 t_cust_id.EXTEND;
537 t_cust_site.EXTEND;
538 t_cust_site_id.EXTEND;
539 t_ship_from.EXTEND;
540 t_ship_from_id.EXTEND;
541 t_ship_from_site.EXTEND;
542 t_ship_from_site_id.EXTEND;
543 t_ship_to.EXTEND;
544 t_ship_to_id.EXTEND;
545 t_ship_to_site.EXTEND;
546 t_ship_to_site_id.EXTEND;
547 t_bkt_type.EXTEND;
548 t_posting_party_id.EXTEND;
549 t_item_name.EXTEND;
550 t_item_desc.EXTEND;
551 t_pub_ot_desc.EXTEND;
552 t_bkt_type_desc.EXTEND;
553 t_posting_party_name.EXTEND;
554 t_uom_code.EXTEND;
555 t_planner_code.EXTEND;
556 t_ship_date.EXTEND;
557 t_tp_ship_date.EXTEND;
558 t_receipt_date.EXTEND;
559 t_tp_receipt_date.EXTEND;
560 t_tp_uom.EXTEND;
561 t_tp_qty.EXTEND;
562 t_master_item_name.EXTEND;
563 t_master_item_desc.EXTEND;
564 t_cust_item_name.EXTEND;
565 t_cust_item_desc.EXTEND;
566
567 end if;
568
569 t_pub(numInsertIndex) := t_pub(j);
570 t_pub_id(numInsertIndex) := t_pub_id(j);
571 t_pub_site(numInsertIndex) := t_pub_site(j);
572 t_pub_site_id(numInsertIndex) := t_pub_site_id(j);
573 t_item_id(numInsertIndex) := t_item_id(j);
574 t_qty(numInsertIndex) := new_qty;
575 t_pub_ot(numInsertIndex) := t_pub_ot(j);
576 t_cust(numInsertIndex) := t_cust(j);
577 t_cust_id(numInsertIndex) := t_cust_id(j);
578 t_cust_site(numInsertIndex) := t_cust_site(j);
579 t_cust_site_id(numInsertIndex) := t_cust_site_id(j);
580 t_ship_from(numInsertIndex) := t_ship_from(j);
581 t_ship_from_id(numInsertIndex) := t_ship_from_id(j);
582 t_ship_from_site(numInsertIndex) := t_ship_from_site(j);
583 t_ship_from_site_id(numInsertIndex) := t_ship_from_site_id(j);
584 t_ship_to(numInsertIndex) := t_ship_to(j);
585 t_ship_to_id(numInsertIndex) := t_ship_to_id(j);
586 t_ship_to_site(numInsertIndex) := t_ship_to_site(j);
587 t_ship_to_site_id(numInsertIndex) := t_ship_to_site_id(j);
588 t_bkt_type(numInsertIndex) := 3;
589 t_posting_party_id(numInsertIndex) := t_posting_party_id(j);
590 t_item_name(numInsertIndex) := t_item_name(j);
591 t_item_desc(numInsertIndex) := t_item_desc(j);
592 t_pub_ot_desc(numInsertIndex) := t_pub_ot_desc(j);
593 t_bkt_type_desc(numInsertIndex) := l_bkt_desc;
594 t_posting_party_name(numInsertIndex) := t_posting_party_name(j);
595 t_uom_code(numInsertIndex) := t_uom_code(j);
596 t_planner_code(numInsertIndex) := t_planner_code(j);
597 t_ship_date(numInsertIndex) := p_curr_month_start_date;
598 t_tp_ship_date(numInsertIndex) := p_curr_month_start_date;
599 t_receipt_date(numInsertIndex) := t_receipt_date(j);
600 t_tp_receipt_date(numInsertIndex) := t_tp_receipt_date(j);
601 t_tp_uom(numInsertIndex) := t_tp_uom(j);
602 t_tp_qty(numInsertIndex) := new_qty;
603 t_master_item_name(numInsertIndex) := t_master_item_name(j);
604 t_master_item_desc(numInsertIndex) := t_master_item_desc(j);
605 t_cust_item_name(numInsertIndex) := t_cust_item_name(j);
606 t_cust_item_desc(numInsertIndex) := t_cust_item_desc(j);
607
608
609 if (p_done) then
610 exit;
611 end if;
612 END LOOP;
613 end if;
614 end loop;
615 end explode_dates;
616
617
618 PROCEDURE get_optional_info(
619 t_item_id IN numberList,
620 t_pub_id IN numberList,
621 t_cust_id IN numberList,
622 t_cust_site_id IN numberList,
623 t_tp_cust_id IN numberList,
624 t_src_cust_site_id IN numberList,
625 t_src_org_id IN numberList,
626 t_src_instance_id IN numberList,
627 t_item_name IN itemNameList,
628 t_uom_code IN itemUomList,
629 t_qty IN numberList,
630 t_ship_date IN dateList,
631 t_receipt_date IN dateList,
632 t_tp_ship_date IN OUT NOCOPY dateList,
633 t_tp_receipt_date IN OUT NOCOPY dateList,
634 t_master_item_name IN OUT NOCOPY itemNameList,
635 t_master_item_desc IN OUT NOCOPY itemDescList,
636 t_cust_item_name IN OUT NOCOPY itemNameList,
637 t_cust_item_desc IN OUT NOCOPY itemDescList,
638 t_tp_uom IN OUT NOCOPY itemUomList,
639 t_tp_qty IN OUT NOCOPY numberList,
640 t_lead_time IN numberList,
641 p_forecast_date IN varchar2
642 ) IS
643
644 l_conversion_found boolean;
645 l_conversion_rate number;
646 -- l_to_location_id number;
647 -- l_org_location_id number;
648 -- l_lead_time number;
649 -- l_session_id number;
650 -- l_src_cust_id number;
651 -- l_regions_return_status varchar(1);
652
653 cursor get_src_cust_id_c1(t_tp_cust_id IN number,
654 t_src_instance_id IN number) IS
655 SELECT sr_tp_id
656 FROM msc_tp_id_lid
657 WHERE tp_id = t_tp_cust_id
658 AND sr_instance_id = t_src_instance_id
659 AND nvl(sr_company_id, -1) = -1
660 AND partner_type = 2;
661
662
663 BEGIN
664
665 for j in t_item_id.FIRST..t_item_id.LAST loop
666 t_tp_ship_date.EXTEND;
667 t_tp_receipt_date.EXTEND;
668 t_tp_uom.EXTEND;
669 t_tp_qty.EXTEND;
670 t_master_item_name.EXTEND;
671 t_master_item_desc.EXTEND;
672 t_cust_item_name.EXTEND;
673 t_cust_item_desc.EXTEND;
674
675 begin
676 select item_name,
677 description
678 into t_master_item_name(j),
679 t_master_item_desc(j)
680 from msc_items
681 where inventory_item_id = t_item_id(j);
682 exception
683 when others then
684 t_master_item_name(j) := t_item_name(j);
685 t_master_item_desc(j) := null;
686 end;
687
688 begin
689 select mcf.customer_item_name,
690 mcf.description,
691 mcf.uom_code
692 into t_cust_item_name(j),
693 t_cust_item_desc(j),
694 t_tp_uom(j)
695 from msc_item_customers mcf,
696 msc_trading_partner_maps m,
697 msc_trading_partner_maps m2,
698 msc_company_relationships r
699 where mcf.inventory_item_id = t_item_id(j) and
700 mcf.plan_id = -1 and
701 r.relationship_type = 1 and
702 r.subject_id = t_pub_id(j) and
703 r.object_id = t_cust_id(j) and
704 m.map_type = 1 and
705 m.company_key = r.relationship_id and
706 mcf.customer_id = m.tp_key and
707 m2.map_type = 3 and
708 m2.company_key = t_cust_site_id(j) and
709 mcf.customer_site_id = m2.tp_key;
710
711 exception
712 when NO_DATA_FOUND then
713 begin
714 select mcf.customer_item_name,
715 mcf.description,
716 mcf.uom_code
717 into t_cust_item_name(j),
718 t_cust_item_desc(j),
719 t_tp_uom(j)
720 from msc_item_customers mcf,
721 msc_trading_partner_maps m,
722 msc_trading_partner_maps m2,
723 msc_company_relationships r
724 where mcf.inventory_item_id = t_item_id(j) and
725 r.relationship_type = 1 and
726 r.subject_id = t_pub_id(j) and
727 r.object_id = t_cust_id(j) and
728 m.map_type = 1 and
729 m.company_key = r.relationship_id and
730 mcf.customer_id = m.tp_key and
731 m2.map_type = 3 and
732 m2.company_key = t_cust_site_id(j) and
733 mcf.customer_site_id is null;
734
735 exception
736 when NO_DATA_FOUND then
737
738 t_cust_item_name(j) := null;
739 t_tp_uom(j) := t_uom_code(j);
740 end;
741 end;
742
743 msc_x_util.get_uom_conversion_rates( t_uom_code(j),
744 t_tp_uom(j),
745 t_item_id(j),
746 l_conversion_found,
747 l_conversion_rate);
748 if l_conversion_found then
749 t_tp_qty(j) := nvl(t_qty(j),0)* l_conversion_rate;
750 else
751 t_tp_qty(j) := t_qty(j);
752 end if;
753
754 /*
755 -- Get source customer Id
756
757 l_src_cust_id := null;
758
759 open get_src_cust_id_c1(t_tp_cust_id(j),t_src_instance_id(j));
760 fetch get_src_cust_id_c1 into l_src_cust_id;
761 close get_src_cust_id_c1;
762
763 if l_src_cust_id is null or t_src_cust_site_id(j) is null then
764 l_lead_time := 0;
765 else
766 l_org_location_id := null;
767 l_to_location_id := null;
768 l_lead_time := null;
769
770 -- Call the ATP API's for regions setup
771
772 select mrp_atp_schedule_temp_s.nextval
773 into l_session_id
774 from dual;
775
776 MSC_SATP_FUNC.GET_REGIONS(t_src_cust_site_id(j),
777 724, -- Calling Module is 'MSC'
778 t_src_instance_id(j),
779 l_session_id,
780 null,
781 l_regions_return_status);
782
783
784
785 -- Get the default ship to/deliver from location for the org
786
787 l_org_location_id := msc_atp_func.get_location_id(
788 t_src_instance_id(j),
789 t_src_org_id(j),
790 null,
791 null,
792 null,
793 null);
794
795 -- dbms_output.put_line('Org Location Id ' || l_org_location_id);
796
797 -- Get the default ship to/deliver from location for the customer
798
799 l_to_location_id := msc_atp_func.get_location_id(
800 t_src_instance_id(j),
801 null,
802 l_src_cust_id,
803 t_src_cust_site_id(j),
804 null,
805 null);
806
807 -- dbms_output.put_line('Location Id ' || l_to_location_id);
808
809 l_lead_time := MSC_SCATP_PUB.get_default_intransit_time (
810 l_org_location_id,
811 t_src_instance_id(j),
812 l_to_location_id,
813 t_src_instance_id(j),
814 l_session_id,
815 t_src_cust_site_id(j));
816
817 -- dbms_output.put_line('Lead time ' || l_lead_time);
818
819 if l_lead_time is null then
820 l_lead_time := 0;
821 end if;
822
823 end if;
824
825 t_tp_receipt_date(j) := t_receipt_date(j) + l_lead_time;
826
827 -- dbms_output.put_line('receipt date ' || t_tp_receipt_date(j));
828 */
829
830 if p_forecast_date = 'SHIP' then
831
832 t_tp_ship_date(j) := t_ship_date(j);
833 t_tp_receipt_date(j) := t_ship_date(j) + t_lead_time(j);
834
835 elsif p_forecast_date = 'RECEIPT' then
836
837 t_tp_ship_date(j) := t_ship_date(j) - t_lead_time(j);
838 t_tp_receipt_date(j) := t_receipt_date(j);
839
840 end if;
841
842
843 end loop;
844
845 END get_optional_info;
846
847
848 PROCEDURE insert_into_sup_dem (
849 t_pub IN companyNameList,
850 t_pub_id IN numberList,
851 t_pub_site IN companySiteList,
852 t_pub_site_id IN numberList,
853 t_item_id IN numberList,
854 t_qty IN numberList,
855 t_pub_ot IN numberList,
856 t_cust IN companyNameList,
857 t_cust_id IN numberList,
858 t_cust_site IN companySiteList,
859 t_cust_site_id IN numberList,
860 t_ship_from IN companyNameList,
861 t_ship_from_id IN numberList,
862 t_ship_from_site IN companySiteList,
863 t_ship_from_site_id IN numberList,
864 t_ship_to IN companyNameList,
865 t_ship_to_id IN numberList,
866 t_ship_to_site IN companySiteList,
867 t_ship_to_site_id IN numberList,
868 t_bkt_type IN numberList,
869 t_posting_party_id IN numberList,
870 t_item_name IN itemNameList,
871 t_item_desc IN itemDescList,
872 t_master_item_name IN itemNameList,
873 t_master_item_desc IN itemDescList,
874 t_cust_item_name IN itemNameList,
875 t_cust_item_desc IN itemDescList,
876 t_pub_ot_desc IN fndMeaningList,
877 t_bkt_type_desc IN fndMeaningList,
878 t_posting_party_name IN companyNameList,
879 t_uom_code IN itemUomList,
880 t_planner_code IN plannerCodeList,
881 t_tp_ship_date IN dateList,
882 t_tp_receipt_date IN dateList,
883 t_tp_uom IN itemUomList,
884 t_tp_qty IN numberList,
885 p_version IN varchar2,
886 p_designator IN varchar2,
887 t_shipping_control IN shippingControlList
888 ) IS
889
890 BEGIN
891
892 FORALL j in t_pub.FIRST..t_pub.LAST
893
894 insert into msc_sup_dem_entries (
895 transaction_id,
896 plan_id,
897 sr_instance_id,
898 publisher_name,
899 publisher_id,
900 publisher_site_name,
901 publisher_site_id,
902 customer_name,
903 customer_id,
904 customer_site_name,
905 customer_site_id,
906 supplier_name,
907 supplier_id,
908 supplier_site_name,
909 supplier_site_id,
910 ship_from_party_name,
911 ship_from_party_id,
912 ship_from_party_site_name,
913 ship_from_party_site_id,
914 ship_to_party_name,
915 ship_to_party_id,
916 ship_to_party_site_name,
917 ship_to_party_site_id,
918 publisher_order_type,
919 publisher_order_type_desc,
920 bucket_type_desc,
921 bucket_type,
922 item_name,
923 item_description,
924 owner_item_name,
925 owner_item_description,
926 supplier_item_name,
927 supplier_item_description,
928 customer_item_name,
929 customer_item_description,
930 inventory_item_id,
931 primary_uom,
932 uom_code,
933 tp_uom_code,
934 key_date,
935 ship_date,
936 receipt_date,
937 quantity,
938 primary_quantity,
939 tp_quantity,
940 last_refresh_number,
941 posting_party_name,
942 posting_party_id,
943 planner_code,
944 version,
945 designator,
946 created_by,
947 creation_date,
948 last_updated_by,
949 last_update_date,
950 last_update_login
951 ) values (
952 msc_sup_dem_entries_s.nextval,
953 -1,
954 -1,
955 t_pub(j),
956 t_pub_id(j),
957 t_pub_site(j),
958 t_pub_site_id(j),
959 t_cust(j),
960 t_cust_id(j),
961 t_cust_site(j),
962 t_cust_site_id(j),
963 t_pub(j),
964 t_pub_id(j),
965 t_pub_site(j),
966 t_pub_site_id(j),
967 t_ship_from(j),
968 t_ship_from_id(j),
969 t_ship_from_site(j),
970 t_ship_from_site_id(j),
971 t_ship_to(j),
972 t_ship_to_id(j),
973 t_ship_to_site(j),
974 t_ship_to_site_id(j),
975 t_pub_ot(j),
976 t_pub_ot_desc(j),
977 t_bkt_type_desc(j),
978 t_bkt_type(j),
979 t_master_item_name(j),
980 t_master_item_desc(j),
981 t_item_name(j),
982 t_item_desc(j),
983 t_item_name(j),
984 t_item_desc(j),
985 t_cust_item_name(j),
986 t_cust_item_desc(j),
987 t_item_id(j),
988 t_uom_code(j),
989 t_uom_code(j),
990 t_tp_uom(j),
991 decode(t_pub_ot(j), 4, t_tp_receipt_date(j), decode(nvl(t_shipping_control(j), 'BUYER'), 'BUYER', t_tp_ship_date(j), t_tp_receipt_date(j))),
992 t_tp_ship_date(j),
993 t_tp_receipt_date(j),
994 t_qty(j),
995 t_qty(j),
996 t_tp_qty(j),
997 msc_cl_refresh_s.nextval,
998 t_posting_party_name(j),
999 t_posting_party_id(j),
1000 t_planner_code(j),
1001 p_version,
1002 p_designator,
1003 fnd_global.user_id,
1004 sysdate,
1005 fnd_global.user_id,
1006 sysdate,
1007 fnd_global.login_id
1008 );
1009 END insert_into_sup_dem;
1010
1011
1012 PROCEDURE delete_old_forecast(
1013 p_org_id in number,
1014 p_sr_instance_id in number,
1015 p_planner_code in varchar2,
1016 -- p_abc_class in varchar2,
1017 p_item_id in number,
1018 p_customer_id in number,
1019 p_customer_site_id in number,
1020 l_horizon_start in date,
1021 p_horizon_end in date
1022 ) IS
1023
1024 l_customer_id number;
1025 l_customer_site_id number;
1026 l_supplier_site_id number;
1027
1028 BEGIN
1029
1030 if p_customer_id is not null then
1031 BEGIN
1032 select c.company_id
1033 into l_customer_id
1034 from msc_trading_partner_maps m,
1035 msc_company_relationships r,
1036 msc_companies c
1037 where m.tp_key = p_customer_id and
1038 m.map_type = 1 and
1039 m.company_key = r.relationship_id and
1040 r.relationship_type = 1 and
1041 r.subject_id = 1 and /* Owner Company Id */
1042 c.company_id = r.object_id;
1043 EXCEPTION
1044 WHEN OTHERS THEN
1045 l_customer_id := NULL;
1046 END;
1047 else
1048 l_customer_id := null;
1049 end if;
1050
1051 -- dbms_output.put_line('l_customer_id := ' || l_customer_id);
1052
1053 if p_customer_site_id is not null then
1054 BEGIN
1055 select cs.company_site_id
1056 into l_customer_site_id
1057 from msc_trading_partner_maps m,
1058 msc_company_sites cs
1059 where m.tp_key = p_customer_site_id and
1060 m.map_type = 3 and
1061 cs.company_site_id = m.company_key;
1062 EXCEPTION
1063 WHEN OTHERS THEN
1064 l_customer_site_id := null;
1065 END;
1066 else
1067 l_customer_site_id := null;
1068 end if;
1069
1070 -- dbms_output.put_line('l_customer_site_id := ' || l_customer_site_id);
1071
1072 if p_org_id is not null and p_sr_instance_id is not null then
1073 BEGIN
1074 select distinct cs.company_site_id
1075 into l_supplier_site_id
1076 from msc_company_sites cs,
1077 msc_trading_partner_maps m,
1078 msc_trading_partners t
1079 where t.sr_tp_id = p_org_id and
1080 t.sr_instance_id = p_sr_instance_id and
1081 t.partner_type = 3 and
1082 m.tp_key = t.partner_id and
1083 m.map_type = 2 and
1084 cs.company_site_id = m.company_key and
1085 cs.company_id = 1;
1086 EXCEPTION
1087 WHEN OTHERS THEN
1088 l_supplier_site_id := NULL;
1089 END;
1090 else
1091 l_supplier_site_id := null;
1092 end if;
1093
1094 -- dbms_output.put_line('l_supplier_site_id := ' || l_supplier_site_id);
1095
1096
1097 delete from msc_sup_dem_entries sd
1098 where sd.publisher_order_type = 1 and
1099 sd.plan_id = -1 and
1100 sd.publisher_id = 1 and
1101 sd.publisher_site_id = nvl(l_supplier_site_id, sd.publisher_site_id) and
1102 sd.customer_id = nvl(l_customer_id, sd.customer_id) and
1103 sd.customer_site_id = nvl(l_customer_site_id, sd.customer_site_id) and
1104 sd.inventory_item_id = nvl(p_item_id, sd.inventory_item_id) and
1105 NVL(sd.planner_code,'-99') = nvl(p_planner_code, NVL(sd.planner_code, '-99')) and
1106 sd.ship_date between nvl(l_horizon_start, sysdate) and nvl(p_horizon_end, sysdate+365);
1107
1108 END delete_old_forecast;
1109
1110 END MSD_SCE_PUBLISH_FORECAST_PKG;