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