[Home] [Help]
PACKAGE BODY: APPS.MSC_PUBLISH_SAFETY_STOCK_PKG
Source
1 PACKAGE BODY msc_publish_safety_stock_pkg AS
2 /* $Header: MSCXPSSB.pls 120.4 2006/05/23 10:47:23 pragarwa noship $ */
3
4 PROCEDURE publish_safety_stocks (
5 p_errbuf out nocopy varchar2,
6 p_retcode out nocopy number, -- Bug 4560452
7 p_plan_id in number,
8 p_org_code in varchar2,
9 p_planner_code in varchar2,
10 p_abc_class in varchar2,
11 p_item_id in number,
12 p_planning_gp in varchar2,
13 p_project_id in number,
14 p_task_id in number,
15 p_supplier_id in number,
16 p_supplier_site_id in number,
17 p_horizon_start in varchar2,
18 p_horizon_end in varchar2,
19 p_overwrite in number
20 ) IS
21
22 p_org_id Number;
23 p_inst_code Varchar2(3);
24 p_sr_instance_id Number;
25 p_category_set_id Number;
26 p_category_name Varchar2(240);
27 p_designator Varchar2(10);
28 l_version Number;
29 l_user_id NUMBER;
30 l_user_name VARCHAR2(100);
31 l_resp_name VARCHAR2(30);
32 l_application_name VARCHAR2(50);
33 l_item_name VARCHAR2(255);
34 l_log_message VARCHAR2(1000);
35 l_supp_name VARCHAR2(100);
36 l_supp_site VARCHAR2(30);
37 l_records_exist NUMBER;
38 l_cursor1 NUMBER;
39 l_cursor2 NUMBER;
40 l_language VARCHAR2(30);
41 l_language_code VARCHAR2(4);
42
43 l_horizon_start date; --canonical date
44 l_horizon_end date; --canonical date
45
46 t_pub companyNameList;
47 t_pub_id numberList;
48 t_pub_site companySiteList;
49 t_pub_site_id numberList;
50 t_customer companyNameList;
51 t_customer_id numberList;
52 t_customer_site companySiteList;
53 t_customer_site_id numberList;
54 t_org_id numberList;
55 t_sr_instance_id numberList;
56 t_item_id numberList;
57 t_base_item_id numberList;
58 t_qty numberList;
59 t_pub_ot numberList;
60 t_bkt_type numberList;
61 t_posting_party_id numberList;
62 t_item_name itemNameList;
63 t_item_desc itemDescList;
64 t_base_item_name itemNameList;
65 t_pub_ot_desc fndMeaningList;
66 t_proj_number numberList;
67 t_task_number numberList;
68 t_planning_gp planningGroupList;
69 t_bkt_type_desc fndMeaningList;
70 t_posting_party_name companyNameList;
71 t_uom_code itemUomList;
72 t_planner_code plannerCodeList;
73 t_key_date dateList;
74 t_ship_date dateList;
75 t_receipt_date dateList;
76 t_supp companyNameList;
77 t_supp_id numberList;
78 t_supp_site companySiteList;
79 t_supp_site_id numberList;
80 t_master_item_name itemNameList;
81 t_master_item_desc itemDescList;
82 t_supp_item_name itemNameList;
83 t_supp_item_desc itemDescList;
84 t_type numberList;
85
86 t_days_in_bkt numberList;
87 t_bucket_type numberList;
88 t_period_start_date dateList;
89 t_bucket_index numberList;
90 t_bucket_start dateList;
91 t_bucket_end dateList;
92
93
94 b_bkt_index numberList;
95 b_bkt_start_date dateList;
96 b_bkt_end_date dateList;
97 b_bkt_type numberList;
98
99 --================================================================
100 --pab variable
101 --================================================================
102 a_pub companyNameList;
103 a_pub_id numberList;
104 a_pub_site companySiteList;
105 a_pub_site_id numberList;
106 a_customer companyNameList;
107 a_customer_id numberList;
108 a_customer_site companySiteList;
109 a_customer_site_id numberList;
110 a_org_id numberList;
111 a_sr_instance_id numberList;
112 a_pab_type numberList;
113 a_item_id numberList;
114 a_base_item_id numberList;
115 a_qty numberList;
116 a_pub_ot numberList;
117 a_bkt_type numberList;
118 a_posting_party_id numberList;
119 a_item_name itemNameList;
120 a_item_desc itemDescList;
121 a_base_item_name itemNameList;
122 a_pub_ot_desc fndMeaningList;
123 a_proj_number numberList;
124 a_task_number numberList;
125 a_planning_gp planningGroupList;
126 a_bkt_type_desc fndMeaningList;
127 a_posting_party_name companyNameList;
128 a_uom_code itemUomList;
129 a_planner_code plannerCodeList;
130 a_period_start_date dateList;
131 a_key_date dateList;
132 a_ship_date dateList;
133 a_receipt_date dateList;
134 a_supp companyNameList;
135 a_supp_id numberList;
136 a_supp_site companySiteList;
137 a_supp_site_id numberList;
138 a_master_item_name itemNameList;
139 a_master_item_desc itemDescList;
140 a_supp_item_name itemNameList;
141 a_supp_item_desc itemDescList;
142 a_type numberList;
143 a_total_qty numberList;
144 a_temp_qty numberList;
145 a_days_in_bkt numberList;
146 a_bucket_type numberList;
147 a_bucket_index numberList;
148 a_bucket_start dateList;
149 a_bucket_end dateList;
150
151
152
153 i_bkt_index numberList;
154 i_bkt_start_date dateList;
155 i_bkt_end_date dateList;
156 i_bkt_type numberList;
157
158
159 i number;
160 l_bucket_end_date msc_plan_buckets.bkt_end_date%type;
161 l_bucket_type msc_plan_buckets.bucket_type%type;
162 l_supply number;
163 l_demand number;
164 l_scrap_demand number;
165 l_exp_lot number;
166 l_onhand number;
167 l_expired_qty number;
168 l_pab_total number;
169
170
171 CURSOR safety_stock_c (
172 p_plan_id in number,
173 p_org_id in number,
174 p_sr_instance_id in number,
175 p_horizon_start in date,
176 p_horizon_end in date,
177 p_planner_code in varchar2,
178 p_abc_class in varchar2,
179 p_item_id in number,
180 p_planning_gp in varchar2,
181 p_project_id in number,
182 p_task_id in number,
183 p_supplier_id in number,
184 p_supplier_site_id in number,
185 p_overwrite in number
186 ) IS
187
188
189 SELECT distinct mst.sr_instance_id,
190 mst.organization_id,
191 item.base_item_id,
192 item.inventory_item_id,
193 mst.period_start_date,
194 mpb.bkt_start_date,
195 mpb.bkt_end_date,
196 mpb.days_in_bkt,
197 mpb.bucket_type,
198 mpb.bucket_index,
199 c.company_id,
200 c.company_name,
201 s.company_site_id,
202 s.company_site_name,
203 item.item_name,
204 item.description,
205 null, --base_item_name
206 item.uom_code,
207 item.planner_code, --Bug 4424426
208 NULL, --mst.planning_group,
209 NULL, --mst.project_id,
210 NULL, --mst.task_id,
211 sum(mst.safety_stock_quantity)
212 FROM msc_safety_stocks mst,
213 msc_plan_buckets mpb,
214 msc_plan_organizations_v ov,
215 msc_system_items item,
216 msc_companies c,
217 msc_company_sites s,
218 msc_trading_partners t,
219 msc_trading_partner_maps m,
220 msc_plans p
221 WHERE p.plan_id = mst.plan_id
222 and mst.plan_id = p_plan_id
223 and mst.organization_id = nvl(p_org_id, mst.organization_id)
224 and mst.sr_instance_id = nvl(p_sr_instance_id, mst.sr_instance_id)
225 and item.inventory_item_id = nvl(p_item_id, item.inventory_item_id)
226 and mst.plan_id = item.plan_id
227 and mst.sr_instance_id = item.sr_instance_id
228 and mst.organization_id = item.organization_id
229 and mst.inventory_item_id = item.inventory_item_id
230 and t.sr_tp_id = mst.organization_id
231 and t.sr_instance_id = mst.sr_instance_id
232 and t.partner_type = 3
233 and m.tp_key = t.partner_id
234 and m.map_type = 2
235 and s.company_site_id = m.company_key
236 and c.company_id = s.company_id
237 --and mst.safety_stock_quantity > 0
238 and NVL(item.planner_code,'-99') = NVL(p_planner_code, NVL(item.planner_code,'-99'))
239 and NVL(item.abc_class_name,'-99') = NVL(p_abc_class, NVL(item.abc_class_name,'-99'))
240 and mst.plan_id = ov.plan_id -- Bug# 3913477
241 and mst.organization_id =ov.planned_organization
242 and mst.sr_instance_id = ov.sr_instance_id
243 and mst.plan_id = mpb.plan_id
244 --and mst.organization_id = mpb.organization_id
245 and mst.sr_instance_id = mpb.sr_instance_id
246 and ov.plan_id = mpb.plan_id
247 and mpb.curr_flag = 1
248 and trunc(mst.period_start_date) between trunc(mpb.bkt_start_date) and trunc(mpb.bkt_end_date)
249 and nvl(mst.planning_group, '-99') = nvl(p_planning_gp, nvl(mst.planning_group, '-99'))
250 and nvl(mst.project_id,-99) = nvl(p_project_id, nvl(mst.project_id,-99))
251 and nvl(mst.task_id, -99) = nvl(p_task_id, nvl(mst.task_id, -99))
252 and p.plan_completion_date is not null
253 and trunc(mst.period_start_date) between nvl(trunc(p.plan_start_date),trunc(mst.period_start_date)) and
254 nvl(trunc(p_horizon_end),trunc(mst.period_start_date))
255 GROUP BY
256 mst.sr_instance_id,
257 mst.organization_id,
258 item.base_item_id,
259 item.inventory_item_id,
260 mst.period_start_date,
261 mpb.bkt_start_date,
262 mpb.bkt_end_date,
263 mpb.days_in_bkt,
264 mpb.bucket_type,
265 mpb.bucket_index,
266 c.company_id,
267 c.company_name,
268 s.company_site_id,
269 s.company_site_name,
270 item.item_name,
271 item.description,
272 null,
273 item.uom_code,
274 item.planner_code,--Bug 4424426
275 NULL, --mst.planning_group,
276 NULL, --mst.project_id,
277 NULL --mst.task_id
278 ORDER BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14;
279
280
281 CURSOR get_bucket_date (p_plan_id in number,
282 p_sr_instance_id in number,
283 p_org_id in number,
284 p_horizon_start_date in date,
285 p_horizon_end_date in date) IS
286 SELECT mpb.bucket_index, mpb.bkt_start_date, mpb.bkt_end_date, mpb.bucket_type
287 FROM msc_plan_buckets mpb,
288 msc_plan_organizations_v ov -- Bug# 3913477
289 WHERE ov.plan_id = p_plan_id
290 AND ov.sr_instance_id = p_sr_instance_id
291 AND ov.planned_organization = p_org_id
292 and mpb.plan_id = ov.plan_id
293 and mpb.curr_flag = 1
294 AND bkt_start_date between nvl(p_horizon_start_date,mpb.bkt_start_date)
295 and nvl(p_horizon_end_date, mpb.bkt_end_date)
296 ORDER BY bucket_index;
297
298
299 ------------------------------------------------------------------------------------
300 -- for the projected available balance
301 -- note: pab will start with plan completion date. If the choose the future horizon date
302 -- you also need to start with the plan completion date for calculation
303 --------------------------------------------------------------------------------------------
304 CURSOR projected_availabe_balance_c (
305 p_plan_id in number,
306 p_org_id in number,
307 p_sr_instance_id in number,
308 p_horizon_start in date,
309 p_horizon_end in date,
310 p_planner_code in varchar2,
311 p_abc_class in varchar2,
312 p_item_id in number,
313 p_planning_gp in varchar2,
314 p_project_id in number,
315 p_task_id in number,
316 p_supplier_id in number,
317 p_supplier_site_id in number,
318 p_overwrite in number
319 ) IS
320
321 SELECT rec.sr_instance_id,
322 rec.organization_id,
323 msi.base_item_id,
324 msi.inventory_item_id,
325 trunc(rec.new_schedule_date),
326 mpb.bkt_start_date,
327 mpb.bkt_end_date,
328 mpb.days_in_bkt,
329 mpb.bucket_type,
330 mpb.bucket_index,
331 DECODE(rec.order_type,
332 PURCHASE_ORDER, PAB_SUPPLY,
333 PURCH_REQ, PAB_SUPPLY,
334 WORK_ORDER, PAB_SUPPLY,
335 FLOW_SCHED, PAB_SUPPLY,
336 REPETITIVE_SCHEDULE, PAB_SUPPLY,
337 PLANNED_ORDER, PAB_SUPPLY,
338 NONSTD_JOB, PAB_SUPPLY,
339 RECEIPT_PURCH_ORDER, PAB_SUPPLY,
340 SHIPMENT, PAB_SUPPLY,
341 RECEIPT_SHIPMENT, PAB_SUPPLY,
342 DIS_JOB_BY, PAB_DEMAND,
343 NON_ST_JOB_BY, PAB_DEMAND,
344 REP_SCHED_BY, PAB_DEMAND,
345 PLANNED_BY, PAB_DEMAND,
346 FLOW_SCHED_BY, PAB_DEMAND,
347 PAYBACK_SUPPLY, PAB_SUPPLY,
348 ON_HAND_QTY, PAB_ONHAND,
349 PAB_SUPPLY),
350 c.company_id,
351 c.company_name,
352 s.company_site_id,
353 s.company_site_name,
354 msi.item_name,
355 msi.description,
356 null, ---base item name
357 msi.uom_code,
358 msi.planner_code,--Bug 4424426
359 NULL, --rec.planning_group,
360 NULL, --rec.project_id,
361 NULL, --rec.task_id,
362 SUM(DECODE(msi.base_item_id,NULL,
363 DECODE(rec.disposition_status_type,
364 2, 0,
365 DECODE(rec.last_unit_completion_date,
366 NULL, rec.new_order_quantity, rec.daily_rate) *
367 DECODE(rec.order_type, DIS_JOB_BY, -1,
368 NON_ST_JOB_BY, -1,
369 REP_SCHED_BY, -1,
370 PLANNED_BY, -1,
371 FLOW_SCHED_BY, -1,
372 1)),
373 DECODE(rec.last_unit_completion_date,
374 NULL, rec.new_order_quantity, rec.daily_rate) *
375 DECODE(rec.order_type, DIS_JOB_BY, -1, NON_ST_JOB_BY, -1,
376 REP_SCHED_BY, -1, PLANNED_BY, -1,
377 FLOW_SCHED_BY, -1, 1))) new_quantity
378 FROM msc_plans p,
379 msc_trading_partners param,
380 msc_system_items msi,
381 msc_supplies rec,
382 msc_plan_buckets mpb,
383 msc_plan_organizations_v ov,
384 msc_companies c,
385 msc_company_sites s,
386 msc_trading_partners t,
387 msc_trading_partner_maps m
388 WHERE p.plan_id = p_plan_id
389 AND p.plan_id = msi.plan_id
390 AND msi.inventory_item_id = nvl(p_item_id, msi.inventory_item_id)
391 AND msi.organization_id = nvl(p_org_id, msi.organization_id)
392 AND msi.sr_instance_id = nvl(p_sr_instance_id, msi.sr_instance_id)
393 AND NVL(msi.planner_code,'-99') = NVL(p_planner_code, NVL(msi.planner_code,'-99'))
394 AND NVL(msi.abc_class_name,'-99') = NVL(p_abc_class, NVL(msi.abc_class_name,'-99'))
395 AND param.sr_tp_id = rec.organization_id
396 AND param.sr_instance_id = rec.sr_instance_id
397 AND param.partner_type = 3
398 AND rec.plan_id = msi.plan_id
399 AND rec.inventory_item_id = msi.inventory_item_id
400 AND rec.organization_id = msi.organization_id
401 AND rec.sr_instance_id = msi.sr_instance_id
402 AND t.sr_tp_id = rec.organization_id
403 AND t.sr_instance_id = rec.sr_instance_id
404 AND t.partner_type = 3
405 AND m.tp_key = t.partner_id
406 AND m.map_type = 2
407 AND s.company_site_id = m.company_key
408 AND c.company_id = s.company_id
409 AND nvl(rec.planning_group, '-99') = nvl(p_planning_gp, nvl(rec.planning_group, '-99'))
410 AND nvl(rec.project_id,-99) = nvl(p_project_id, nvl(rec.project_id,-99))
411 AND nvl(rec.task_id, -99) = nvl(p_task_id, nvl(rec.task_id, -99))
412 AND rec.plan_id = ov.plan_id --- bug# 4106955
413 AND rec.organization_id =ov.planned_organization
414 AND rec.sr_instance_id = ov.sr_instance_id
415 AND rec.plan_id = mpb.plan_id
416 AND rec.plan_id = p.plan_id
417 --AND rec.organization_id = mpb.organization_id --- bug# 4106955
418 AND rec.sr_instance_id = mpb.sr_instance_id
419 AND ov.plan_id = mpb.plan_id
420 AND mpb.curr_flag = 1
421 AND trunc(rec.new_schedule_date) between trunc(mpb.bkt_start_date) and trunc(mpb.bkt_end_date)
422 AND p.plan_completion_date is not null
423 AND trunc(rec.new_schedule_date) BETWEEN nvl(trunc(p.plan_start_date), trunc(rec.new_schedule_date)) and
424 nvl(trunc(p_horizon_end), trunc(rec.new_schedule_date))
425 GROUP BY
426 rec.sr_instance_id,
427 rec.organization_id ,
428 msi.base_item_id,
429 msi.inventory_item_id,
430 rec.new_schedule_date,
431 mpb.bkt_start_date,
432 mpb.bkt_end_date,
433 mpb.days_in_bkt,
434 mpb.bucket_type,
435 mpb.bucket_index,
436 DECODE(rec.order_type,
437 PURCHASE_ORDER, PAB_SUPPLY,
438 PURCH_REQ, PAB_SUPPLY,
439 WORK_ORDER, PAB_SUPPLY,
440 FLOW_SCHED, PAB_SUPPLY,
441 REPETITIVE_SCHEDULE, PAB_SUPPLY,
442 PLANNED_ORDER, PAB_SUPPLY,
443 NONSTD_JOB, PAB_SUPPLY,
444 RECEIPT_PURCH_ORDER, PAB_SUPPLY,
445 SHIPMENT, PAB_SUPPLY,
446 RECEIPT_SHIPMENT, PAB_SUPPLY,
447 DIS_JOB_BY, PAB_DEMAND,
448 NON_ST_JOB_BY, PAB_DEMAND,
449 REP_SCHED_BY, PAB_DEMAND,
450 PLANNED_BY, PAB_DEMAND,
451 FLOW_SCHED_BY, PAB_DEMAND,
452 PAYBACK_SUPPLY, PAB_SUPPLY,
453 ON_HAND_QTY, PAB_ONHAND,
454 PAB_SUPPLY),
455 c.company_id,
456 c.company_name,
457 s.company_site_id,
458 s.company_site_name,
459 msi.item_name,
460 msi.description,
461 null, --base item name
462 msi.uom_code,
463 msi.planner_code, --Bug 4424426
464 NULL, --rec.planning_group,
465 NULL, --rec.project_id,
466 NULL --rec.task_id
467
468 UNION ALL
469 SELECT
470 mgr.sr_instance_id,
471 mgr.organization_id,
472 msi.base_item_id,
473 msi.inventory_item_id,
474 trunc(mgr.using_assembly_demand_date),
475 mpb.bkt_start_date,
476 mpb.bkt_end_date,
477 mpb.days_in_bkt,
478 mpb.bucket_type,
479 mpb.bucket_index,
480 decode (mgr.origination_type, 1, PAB_DEMAND,2,PAB_DEMAND,3,PAB_DEMAND,
481 4,PAB_DEMAND,5,PAB_EXP_LOT,6,PAB_DEMAND,7,PAB_DEMAND,8,PAB_DEMAND,
482 9,PAB_DEMAND,10,PAB_DEMAND,11,PAB_DEMAND,12,PAB_DEMAND,
483 15,PAB_DEMAND,16,PAB_SCRAP_DEMAND,17,PAB_SCRAP_DEMAND,18,PAB_SCRAP_DEMAND,
484 19,PAB_SCRAP_DEMAND,20,PAB_SCRAP_DEMAND,21,PAB_SCRAP_DEMAND,22,PAB_DEMAND,
485 23,PAB_SCRAP_DEMAND,24,PAB_DEMAND,25,PAB_DEMAND,26,PAB_SCRAP_DEMAND,
486 29,PAB_DEMAND,30,PAB_DEMAND,DEMAND_PAYBACK,PAB_DEMAND,
487 PAB_DEMAND),
488 c.company_id,
489 c.company_name,
490 s.company_site_id,
491 s.company_site_name,
492 msi.item_name,
493 msi.description,
494 null, ---base item name
495 msi.uom_code,
496 msi.planner_code,--Bug 4424426
497 NULL, --mgr.planning_group,
498 NULL, --mgr.project_id,
499 NULL, --mgr.task_id,
500 SUM(DECODE(mgr.assembly_demand_comp_date,
501 NULL, DECODE(mgr.origination_type,
502 29,(nvl(mgr.probability,1)*using_requirement_quantity),
503 31, 0,
504 using_requirement_quantity),
505 DECODE(mgr.origination_type,
506 29,(nvl(mgr.probability,1)*daily_demand_rate),
507 31, 0,
508 daily_demand_rate)))/
509 DECODE(nvl(LEAST(SUM(DECODE(mgr.origination_type,
510 29,nvl(mgr.probability,0),
511 null)) ,1) ,1),
512 0,1,
513 nvl(LEAST(SUM(DECODE(mgr.origination_type,
514 29,nvl(mgr.probability,0),
515 null)) ,1) ,1)) new_quantity
516 FROM msc_plans p,
517 msc_trading_partners param,
518 msc_system_items msi,
519 msc_demands mgr,
520 msc_plan_buckets mpb,
521 msc_plan_organizations_v ov,
522 msc_companies c,
523 msc_company_sites s,
524 msc_trading_partners t,
525 msc_trading_partner_maps m
526 WHERE p.plan_id = p_plan_id
527 AND p.plan_id = mgr.plan_id
528 AND msi.inventory_item_id = nvl(p_item_id, msi.inventory_item_id )
529 AND mgr.organization_id = nvl(p_org_id, mgr.organization_id)
530 AND mgr.sr_instance_id = nvl(p_sr_instance_id, mgr.sr_instance_id)
531 AND NVL(msi.planner_code,'-99') = NVL(p_planner_code, NVL(msi.planner_code,'-99'))
532 AND NVL(msi.abc_class_name,'-99') = NVL(p_abc_class, NVL(msi.abc_class_name,'-99'))
533 AND param.sr_tp_id = mgr.organization_id
534 AND param.sr_instance_id = mgr.sr_instance_id
535 AND param.partner_type = 3
536 AND mgr.plan_id = msi.plan_id
537 AND mgr.inventory_item_id = msi.inventory_item_id
538 AND mgr.organization_id = msi.organization_id
539 AND mgr.sr_instance_id = msi.sr_instance_id
540 AND t.sr_tp_id = mgr.organization_id
541 AND t.sr_instance_id = mgr.sr_instance_id
542 AND t.partner_type = 3
543 AND m.tp_key = t.partner_id
544 AND m.map_type = 2
545 AND s.company_site_id = m.company_key
546 AND c.company_id = s.company_id
547 AND nvl(mgr.planning_group, '-99') = nvl(p_planning_gp, nvl(mgr.planning_group, '-99'))
548 AND nvl(mgr.project_id,-99) = nvl(p_project_id, nvl(mgr.project_id,-99))
549 AND nvl(mgr.task_id, -99) = nvl(p_task_id, nvl(mgr.task_id, -99))
550 AND mgr.plan_id = ov.plan_id -- Bug# 3913477
551 AND mgr.organization_id =ov.planned_organization
552 AND mgr.sr_instance_id = ov.sr_instance_id
553 AND mgr.plan_id = mpb.plan_id
554 AND mgr.plan_id = p.plan_id
555 --AND mgr.organization_id = mpb.organization_id
556 AND mgr.sr_instance_id = mpb.sr_instance_id
557 AND ov.plan_id = mpb.plan_id
558 AND mpb.curr_flag = 1
559 AND trunc(mgr.using_assembly_demand_date) between trunc(mpb.bkt_start_date) and trunc(mpb.bkt_end_date)
560 AND p.plan_completion_date is not null
561 AND trunc(mgr.using_assembly_demand_date) BETWEEN nvl(trunc(p.plan_start_date), trunc(mgr.using_assembly_demand_date))
562 AND nvl(trunc(p_horizon_end), trunc(mgr.using_assembly_demand_date))
563 AND not exists (
564 select 'cancelled IR'
565 from msc_supplies mr
566 where mgr.origination_type in (30,6)
567 and mgr.disposition_id = mr.transaction_id
568 and mgr.plan_id = mr.plan_id
569 and mgr.sr_instance_id = mr.sr_instance_id
570 and mr.disposition_status_type = 2)
571 GROUP BY
572 mgr.sr_instance_id,
573 mgr.organization_id,
574 msi.base_item_id,
575 msi.inventory_item_id,
576 mgr.using_assembly_demand_date,
577 mpb.bkt_start_date,
578 mpb.bkt_end_date,
579 mpb.days_in_bkt,
580 mpb.bucket_type,
581 mpb.bucket_index,
582 decode (mgr.origination_type, 1, PAB_DEMAND,2,PAB_DEMAND,3,PAB_DEMAND,
583 4,PAB_DEMAND,5,PAB_EXP_LOT,6,PAB_DEMAND,7,PAB_DEMAND,8,PAB_DEMAND,
584 9,PAB_DEMAND,10,PAB_DEMAND,11,PAB_DEMAND,12,PAB_DEMAND,
585 15,PAB_DEMAND,16,PAB_SCRAP_DEMAND,17,PAB_SCRAP_DEMAND,18,PAB_SCRAP_DEMAND,
586 19,PAB_SCRAP_DEMAND,20,PAB_SCRAP_DEMAND,21,PAB_SCRAP_DEMAND,22,PAB_DEMAND,
587 23,PAB_SCRAP_DEMAND,24,PAB_DEMAND, 25,PAB_DEMAND,26,PAB_SCRAP_DEMAND,
588 29,PAB_DEMAND,30,PAB_DEMAND,DEMAND_PAYBACK,PAB_DEMAND,
589 PAB_DEMAND),
590 c.company_id,
591 c.company_name,
592 s.company_site_id,
593 s.company_site_name,
594 msi.item_name,
595 msi.description,
596 null,
597 msi.uom_code,
598 msi.planner_code,--Bug 4424426
599 NULL, --mgr.planning_group,
600 NULL, --mgr.project_id,
601 NULL --mgr.task_id
602 UNION ALL
603 /*----------------------------------------------------------------------
604 Bug# 3893860
605 The following 2 select statement are added for the fix. The
606 fix will include the past due pab calculation
607 That means will include all the data before the plan_start_date.
608 There will be no join to msc_plan_buckets.
609 ----------------------------------------------------------------------*/
610 SELECT rec.sr_instance_id,
611 rec.organization_id,
612 msi.base_item_id,
613 msi.inventory_item_id,
614 trunc(rec.new_schedule_date),
615 null,--mpb.bkt_start_date,
616 null,--mpb.bkt_end_date,
617 null,--mpb.days_in_bkt,
618 null,--mpb.bucket_type,
619 null,--mpb.bucket_index,
620 DECODE(rec.order_type,
621 PURCHASE_ORDER, PAB_SUPPLY,
622 PURCH_REQ, PAB_SUPPLY,
623 WORK_ORDER, PAB_SUPPLY,
624 FLOW_SCHED, PAB_SUPPLY,
625 REPETITIVE_SCHEDULE, PAB_SUPPLY,
626 PLANNED_ORDER, PAB_SUPPLY,
627 NONSTD_JOB, PAB_SUPPLY,
628 RECEIPT_PURCH_ORDER, PAB_SUPPLY,
629 SHIPMENT, PAB_SUPPLY,
630 RECEIPT_SHIPMENT, PAB_SUPPLY,
631 DIS_JOB_BY, PAB_DEMAND,
632 NON_ST_JOB_BY, PAB_DEMAND,
633 REP_SCHED_BY, PAB_DEMAND,
634 PLANNED_BY, PAB_DEMAND,
635 FLOW_SCHED_BY, PAB_DEMAND,
636 PAYBACK_SUPPLY, PAB_SUPPLY,
637 ON_HAND_QTY, PAB_ONHAND,
638 PAB_SUPPLY),
639 c.company_id,
640 c.company_name,
641 s.company_site_id,
642 s.company_site_name,
643 msi.item_name,
644 msi.description,
645 null, ---base item name
646 msi.uom_code,
647 msi.planner_code,--Bug 4424426
648 NULL, --rec.planning_group,
649 NULL, --rec.project_id,
650 NULL, --rec.task_id,
651 SUM(DECODE(msi.base_item_id,NULL,
652 DECODE(rec.disposition_status_type,
653 2, 0,
654 DECODE(rec.last_unit_completion_date,
655 NULL, rec.new_order_quantity, rec.daily_rate) *
656 DECODE(rec.order_type, DIS_JOB_BY, -1,
657 NON_ST_JOB_BY, -1,
658 REP_SCHED_BY, -1,
659 PLANNED_BY, -1,
660 FLOW_SCHED_BY, -1,
661 1)),
662 DECODE(rec.last_unit_completion_date,
663 NULL, rec.new_order_quantity, rec.daily_rate) *
664 DECODE(rec.order_type, DIS_JOB_BY, -1, NON_ST_JOB_BY, -1,
665 REP_SCHED_BY, -1, PLANNED_BY, -1,
666 FLOW_SCHED_BY, -1, 1))) new_quantity
667 FROM msc_plans p,
668 msc_trading_partners param,
669 msc_system_items msi,
670 msc_supplies rec,
671 msc_companies c,
672 msc_company_sites s,
673 msc_trading_partners t,
674 msc_trading_partner_maps m
675 WHERE p.plan_id = p_plan_id
676 AND p.plan_id = msi.plan_id
677 AND msi.inventory_item_id = nvl(p_item_id, msi.inventory_item_id)
678 AND msi.organization_id = nvl(p_org_id, msi.organization_id)
679 AND msi.sr_instance_id = nvl(p_sr_instance_id, msi.sr_instance_id)
680 AND NVL(msi.planner_code,'-99') = NVL(p_planner_code, NVL(msi.planner_code,'-99'))
681 AND NVL(msi.abc_class_name,'-99') = NVL(p_abc_class, NVL(msi.abc_class_name,'-99'))
682 AND param.sr_tp_id = rec.organization_id
683 AND param.sr_instance_id = rec.sr_instance_id
684 AND param.partner_type = 3
685 AND rec.plan_id = msi.plan_id
686 AND rec.inventory_item_id = msi.inventory_item_id
687 AND rec.organization_id = msi.organization_id
688 AND rec.sr_instance_id = msi.sr_instance_id
689 AND t.sr_tp_id = rec.organization_id
690 AND t.sr_instance_id = rec.sr_instance_id
691 AND t.partner_type = 3
692 AND m.tp_key = t.partner_id
693 AND m.map_type = 2
694 AND s.company_site_id = m.company_key
695 AND c.company_id = s.company_id
696 AND nvl(rec.planning_group, '-99') = nvl(p_planning_gp, nvl(rec.planning_group, '-99'))
697 AND nvl(rec.project_id,-99) = nvl(p_project_id, nvl(rec.project_id,-99))
698 AND nvl(rec.task_id, -99) = nvl(p_task_id, nvl(rec.task_id, -99))
699 AND rec.plan_id = p.plan_id
700 AND p.plan_completion_date is not null
701 AND trunc(rec.new_schedule_date) <= nvl(trunc(p_horizon_end), rec.new_schedule_date)
702 and trunc(rec.new_schedule_date ) < trunc( p.plan_start_date)
703 GROUP BY
704 rec.sr_instance_id,
705 rec.organization_id ,
706 msi.base_item_id,
707 msi.inventory_item_id,
708 rec.new_schedule_date,
709 null,-- mpb.bkt_start_date,
710 null,-- mpb.bkt_end_date,
711 null,-- mpb.days_in_bkt,
712 null,-- mpb.bucket_type,
713 null,-- mpb.bucket_index,
714 DECODE(rec.order_type,
715 PURCHASE_ORDER, PAB_SUPPLY,
716 PURCH_REQ, PAB_SUPPLY,
717 WORK_ORDER, PAB_SUPPLY,
718 FLOW_SCHED, PAB_SUPPLY,
719 REPETITIVE_SCHEDULE, PAB_SUPPLY,
720 PLANNED_ORDER, PAB_SUPPLY,
721 NONSTD_JOB, PAB_SUPPLY,
722 RECEIPT_PURCH_ORDER, PAB_SUPPLY,
723 SHIPMENT, PAB_SUPPLY,
724 RECEIPT_SHIPMENT, PAB_SUPPLY,
725 DIS_JOB_BY, PAB_DEMAND,
726 NON_ST_JOB_BY, PAB_DEMAND,
727 REP_SCHED_BY, PAB_DEMAND,
728 PLANNED_BY, PAB_DEMAND,
729 FLOW_SCHED_BY, PAB_DEMAND,
730 PAYBACK_SUPPLY, PAB_SUPPLY,
731 ON_HAND_QTY, PAB_ONHAND,
732 PAB_SUPPLY),
733 c.company_id,
734 c.company_name,
735 s.company_site_id,
736 s.company_site_name,
737 msi.item_name,
738 msi.description,
739 null, --base item name
740 msi.uom_code,
741 msi.planner_code,--Bug 4424426
742 NULL, --rec.planning_group,
743 NULL, --rec.project_id,
744 NULL --rec.task_id
745
746 UNION ALL
747 SELECT
748 mgr.sr_instance_id,
749 mgr.organization_id,
750 msi.base_item_id,
751 msi.inventory_item_id,
752 trunc(mgr.using_assembly_demand_date),
753 NULL, --mpb.bkt_start_date,
754 NULL, --mpb.bkt_end_date,
755 NULL, --mpb.days_in_bkt,
756 NULL, --mpb.bucket_type,
757 NULL, --mpb.bucket_index,
758 decode (mgr.origination_type, 1, PAB_DEMAND,2,PAB_DEMAND,3,PAB_DEMAND,
759 4,PAB_DEMAND,5,PAB_EXP_LOT,6,PAB_DEMAND,7,PAB_DEMAND,8,PAB_DEMAND,
760 9,PAB_DEMAND,10,PAB_DEMAND,11,PAB_DEMAND,12,PAB_DEMAND,
761 15,PAB_DEMAND,16,PAB_SCRAP_DEMAND,17,PAB_SCRAP_DEMAND,18,PAB_SCRAP_DEMAND,
762 19,PAB_SCRAP_DEMAND,20,PAB_SCRAP_DEMAND,21,PAB_SCRAP_DEMAND,22,PAB_DEMAND,
763 23,PAB_SCRAP_DEMAND,24,PAB_DEMAND,25,PAB_DEMAND,26,PAB_SCRAP_DEMAND,
764 29,PAB_DEMAND,30,PAB_DEMAND,DEMAND_PAYBACK,PAB_DEMAND,
765 PAB_DEMAND),
766 c.company_id,
767 c.company_name,
768 s.company_site_id,
769 s.company_site_name,
770 msi.item_name,
771 msi.description,
772 null, ---base item name
773 msi.uom_code,
774 msi.planner_code,--Bug 4424426
775 NULL, --mgr.planning_group,
776 NULL, --mgr.project_id,
777 NULL, --mgr.task_id,
778 SUM(DECODE(mgr.assembly_demand_comp_date,
779 NULL, DECODE(mgr.origination_type,
780 29,(nvl(mgr.probability,1)*using_requirement_quantity),
781 31, 0,
782 using_requirement_quantity),
783 DECODE(mgr.origination_type,
784 29,(nvl(mgr.probability,1)*daily_demand_rate),
785 31, 0,
786 daily_demand_rate)))/
787 DECODE(nvl(LEAST(SUM(DECODE(mgr.origination_type,
788 29,nvl(mgr.probability,0),
789 null)) ,1) ,1),
790 0,1,
791 nvl(LEAST(SUM(DECODE(mgr.origination_type,
792 29,nvl(mgr.probability,0),
793 null)) ,1) ,1)) new_quantity
794 FROM msc_plans p,
795 msc_trading_partners param,
796 msc_system_items msi,
797 msc_demands mgr,
798 msc_companies c,
799 msc_company_sites s,
800 msc_trading_partners t,
801 msc_trading_partner_maps m
802 WHERE p.plan_id = p_plan_id
803 AND p.plan_id = mgr.plan_id
804 AND msi.inventory_item_id = nvl(p_item_id, msi.inventory_item_id )
805 AND mgr.organization_id = nvl(p_org_id, mgr.organization_id)
806 AND mgr.sr_instance_id = nvl(p_sr_instance_id, mgr.sr_instance_id)
807 AND NVL(msi.planner_code,'-99') = NVL(p_planner_code, NVL(msi.planner_code,'-99'))
808 AND NVL(msi.abc_class_name,'-99') = NVL(p_abc_class, NVL(msi.abc_class_name,'-99'))
809 AND param.sr_tp_id = mgr.organization_id
810 AND param.sr_instance_id = mgr.sr_instance_id
811 AND param.partner_type = 3
812 AND mgr.plan_id = msi.plan_id
813 AND mgr.inventory_item_id = msi.inventory_item_id
814 AND mgr.organization_id = msi.organization_id
815 AND mgr.sr_instance_id = msi.sr_instance_id
816 AND t.sr_tp_id = mgr.organization_id
817 AND t.sr_instance_id = mgr.sr_instance_id
818 AND t.partner_type = 3
819 AND m.tp_key = t.partner_id
820 AND m.map_type = 2
821 AND s.company_site_id = m.company_key
822 AND c.company_id = s.company_id
823 AND nvl(mgr.planning_group, '-99') = nvl(p_planning_gp, nvl(mgr.planning_group, '-99'))
824 AND nvl(mgr.project_id,-99) = nvl(p_project_id, nvl(mgr.project_id,-99))
825 AND nvl(mgr.task_id, -99) = nvl(p_task_id, nvl(mgr.task_id, -99))
826 AND mgr.plan_id = p.plan_id
827 AND p.plan_completion_date is not null
828 AND trunc(mgr.using_assembly_demand_date) <= nvl(trunc(p_horizon_end), trunc(mgr.using_assembly_demand_date))
829 AND trunc(mgr.using_assembly_demand_date) < trunc(p.plan_start_date)
830 AND not exists (
831 select 'cancelled IR'
832 from msc_supplies mr
833 where mgr.origination_type in (30,6)
834 and mgr.disposition_id = mr.transaction_id
835 and mgr.plan_id = mr.plan_id
836 and mgr.sr_instance_id = mr.sr_instance_id
837 and mr.disposition_status_type = 2)
838 GROUP BY
839 mgr.sr_instance_id,
840 mgr.organization_id,
841 msi.base_item_id,
842 msi.inventory_item_id,
843 mgr.using_assembly_demand_date,
844 NULL, --mpb.bkt_start_date,
845 NULL, --mpb.bkt_end_date,
846 NULL, --mpb.days_in_bkt,
847 NULL, --mpb.bucket_type,
848 NULL, --mpb.bucket_index,
849 decode (mgr.origination_type, 1, PAB_DEMAND,2,PAB_DEMAND,3,PAB_DEMAND,
850 4,PAB_DEMAND,5,PAB_EXP_LOT,6,PAB_DEMAND,7,PAB_DEMAND,8,PAB_DEMAND,
851 9,PAB_DEMAND,10,PAB_DEMAND,11,PAB_DEMAND,12,PAB_DEMAND,
852 15,PAB_DEMAND,16,PAB_SCRAP_DEMAND,17,PAB_SCRAP_DEMAND,18,PAB_SCRAP_DEMAND,
853 19,PAB_SCRAP_DEMAND,20,PAB_SCRAP_DEMAND,21,PAB_SCRAP_DEMAND,22,PAB_DEMAND,
854 23,PAB_SCRAP_DEMAND,24,PAB_DEMAND, 25,PAB_DEMAND,26,PAB_SCRAP_DEMAND,
855 29,PAB_DEMAND,30,PAB_DEMAND,DEMAND_PAYBACK,PAB_DEMAND,
856 PAB_DEMAND),
857 c.company_id,
858 c.company_name,
859 s.company_site_id,
860 s.company_site_name,
861 msi.item_name,
862 msi.description,
863 null,
864 msi.uom_code,
865 msi.planner_code,--Bug 4424426
866 NULL, --mgr.planning_group,
867 NULL, --mgr.project_id,
868 NULL --mgr.task_id
869
870 ORDER BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14;
871
872 /*********
873 t_pub companyNameList := companyNameList();
874 t_pub_id numberList := numberList();
875 t_pub_site companySiteList := companySiteList();
876 t_pub_site_id numberList := numberList();
877 t_customer companyNameList := companyNameList();
878 t_customer_id numberList := numberList();
879 t_customer_site companySiteList := companySiteList();
880 t_customer_site_id numberList := numberList();
881 t_item_name itemNameList := itemNameList();
882 t_item_desc itemDescList := itemDescList();
883 t_base_item_name itemNameList := itemNamelist();
884 t_supp companyNameList := companyNameList();
885 t_supp_id numberList := numberList();
886 t_supp_site companySiteList := companySiteList();
887 t_supp_site_id numberList := numberList();
888 t_master_item_name itemNameList := itemNameList();
889 t_master_item_desc itemDescList := itemDescList();
890 t_supp_item_name itemNameList := itemNameList();
891 t_supp_item_desc itemDescList := itemDescList();
892 t_type numberList := numberList();
893
894 a_pub companyNameList := companyNameList();
895 a_pub_id numberList := numberList();
896 a_pub_site companySiteList := companySiteList();
897 a_pub_site_id numberList := numberList();
898 a_customer companyNameList := companyNameList();
899 a_customer_id numberList := numberList();
900 a_customer_site companySiteList := companySiteList();
901 a_customer_site_id numberList := numberList();
902 a_item_name itemNameList := itemNameList();
903 a_item_desc itemDescList := itemDescList();
904 a_base_item_name itemNameList := itemNameList();
905 a_supp companyNameList := companyNameList();
906 a_supp_id numberList := numberList();
907 a_supp_site companySiteList := companySiteList();
908 a_supp_site_id numberList := numberList();
909 a_master_item_name itemNameList := itemNameList();
910 a_master_item_desc itemDescList := itemDescList();
911 a_supp_item_name itemNameList := itemNameList();
912 a_supp_item_desc itemDescList := itemDescList();
913 a_type numberList := numberList();
914 a_total_qty numberList := numberList();
915 a_temp_qty numberList := numberList();
916 a_days_in_bkt numberList := numberList();
917 a_bucket_type numberList := numberList();
918 ***********/
919
920
921
922 ----------------------------------------------------------------
923 -- begin
924 -----------------------------------------------------------------
925
926 BEGIN
927
928 t_pub := companyNameList();
929 t_pub_id := numberList();
930 t_pub_site := companySiteList();
931 t_pub_site_id := numberList();
932 t_customer := companyNameList();
933 t_customer_id := numberList();
934 t_customer_site := companySiteList();
935 t_customer_site_id := numberList();
936 t_item_name := itemNameList();
937 t_item_desc := itemDescList();
938 t_base_item_name := itemNamelist();
939 t_supp := companyNameList();
940 t_supp_id := numberList();
941 t_supp_site := companySiteList();
942 t_supp_site_id := numberList();
943 t_master_item_name := itemNameList();
944 t_master_item_desc := itemDescList();
945 t_supp_item_name := itemNameList();
946 t_supp_item_desc := itemDescList();
947 t_type := numberList();
948 b_bkt_index := numberList();
949 b_bkt_type := numberList();
950
951 a_pub := companyNameList();
952 a_pub_id := numberList();
953 a_pub_site := companySiteList();
954 a_pub_site_id := numberList();
955 a_customer := companyNameList();
956 a_customer_id := numberList();
957 a_customer_site := companySiteList();
958 a_customer_site_id := numberList();
959 a_item_name := itemNameList();
960 a_item_desc := itemDescList();
961 a_base_item_name := itemNameList();
962 a_supp := companyNameList();
963 a_supp_id := numberList();
964 a_supp_site := companySiteList();
965 a_supp_site_id := numberList();
966 a_master_item_name := itemNameList();
967 a_master_item_desc := itemDescList();
968 a_supp_item_name := itemNameList();
969 a_supp_item_desc := itemDescList();
970 a_type := numberList();
971 a_total_qty := numberList();
972 a_temp_qty := numberList();
973 a_days_in_bkt := numberList();
974 a_bucket_type := numberList();
975 i_bkt_index := numberList();
976 i_bkt_type := numberList();
977
978
979 if fnd_global.conc_request_id > 0 then
980
981 p_retcode := 0 ; -- Bug 4560452
982 p_errbuf := null ;
983
984 select
985 fnd_global.user_id,
986 fnd_global.user_name --,
987 --fnd_global.resp_name,
988 --fnd_global.application_name
989 into l_user_id,
990 l_user_name --,
991 --l_resp_name,
992 --l_application_name
993 from dual;
994 end if;
995
996 if l_user_id is null then
997 l_language_code := 'US';
998 else
999 l_language := fnd_preference.get(UPPER(l_user_name),'WF','LANGUAGE');
1000 IF l_language IS NOT NULL THEN
1001 SELECT language_code
1002 INTO l_language_code
1003 FROM fnd_languages
1004 WHERE nls_language = l_language;
1005 ELSE
1006 l_language_code := 'US';
1007 END IF;
1008 end if;
1009
1010
1011 --dbms_output.put_line('At 1');
1012 select compile_designator
1013 into p_designator
1014 from msc_plans
1015 where plan_id = p_plan_id;
1016
1017 --dbms_output.put_line('Designator : ' || p_designator);
1018
1019 if p_org_code is not null then
1020 p_inst_code := substr(p_org_code,1,instr(p_org_code,':')-1);
1021 --dbms_output.put_line('p_inst_code := ' || p_inst_code);
1022 begin
1023 select instance_id
1024 into p_sr_instance_id
1025 from msc_apps_instances
1026 where instance_code = p_inst_code;
1027 --dbms_output.put_line('p_sr_instance_id := ' || p_sr_instance_id);
1028
1029 select sr_tp_id
1030 into p_org_id
1031 from msc_trading_partners
1032 where organization_code = p_org_code and
1033 sr_instance_id = p_sr_instance_id and
1034 partner_type = 3 and
1035 company_id is null;
1036 --dbms_output.put_line('p_org_id := ' || p_org_id);
1037 exception
1038 when others then
1039 p_sr_instance_id := null;
1040 p_org_id := null;
1041 end;
1042 else
1043 p_org_id := null;
1044 p_sr_instance_id := null;
1045 end if;
1046
1047 --------------------------------------------------------------------------
1048 -- set the standard date as canonical date
1049 --------------------------------------------------------------------------
1050 -- Bug 4549069
1051 if (p_horizon_start is null) then
1052 select sysdate
1053 into l_horizon_start
1054 from dual;
1055 else
1056 l_horizon_start := fnd_date.canonical_to_date(p_horizon_start);
1057 end if;
1058
1059 if (p_horizon_end is null) then
1060 select sysdate +365
1061 into l_horizon_end
1062 from dual;
1063 else
1064 l_horizon_end := fnd_date.canonical_to_date(p_horizon_end);
1065 end if;
1066
1067
1068 log_message('l_horizon_start: '||l_horizon_start);
1069 log_message('l_horizon_end: '||l_horizon_end);
1070
1071
1072
1073 l_log_message := get_message('MSC','MSC_PUB_SS',l_language_code) || ' ' || fnd_global.local_chr(10) ||
1074 get_message('MSC','MSC_X_PUB_PLAN',l_language_code) || ': ' || p_designator || fnd_global.local_chr(10);
1075
1076
1077 if (l_horizon_start > l_horizon_end) THEN
1078 l_log_message := l_log_message || get_message('MSC','MSC_X_PUB_START_DATE',l_language_code) || ' ' || l_horizon_start || fnd_global.local_chr(10);
1079 l_log_message := l_log_message || get_message('MSC','MSC_X_PUB_END_DATE',l_language_code) || ' ' || l_horizon_end || fnd_global.local_chr(10);
1080 l_log_message := l_log_message || get_message('MSC','MSC_X_PUB_DATE_MISMATCH',l_language_code) || ' ' || fnd_global.local_chr(10);
1081 log_message(l_log_message);
1082 RETURN;
1083
1084 END IF;
1085
1086 IF p_org_code IS NOT NULL THEN
1087 l_log_message := l_log_message || get_message('MSC','MSC_X_PUB_ORG',l_language_code) || ': ' || p_org_code || fnd_global.local_chr(10);
1088 END IF;
1089
1090
1091 IF p_item_id IS NOT NULL THEN
1092 SELECT item_name
1093 INTO l_item_name
1094 FROM msc_items
1095 WHERE inventory_item_id = p_item_id;
1096 l_log_message := l_log_message || get_message('MSC','MSC_X_PUB_ITEM',l_language_code) || ': ' || l_item_name || fnd_global.local_chr(10);
1097 END IF;
1098
1099 IF p_supplier_id IS NOT NULL THEN
1100 SELECT partner_name
1101 INTO l_supp_name
1102 FROM msc_trading_partners
1103 WHERE partner_id = p_supplier_id;
1104 l_log_message := l_log_message || get_message('MSC','MSC_X_PUB_SUPPLIER',l_language_code) || ': ' || l_supp_name || fnd_global.local_chr(10);
1105 END IF;
1106
1107 IF p_supplier_site_id IS NOT NULL THEN
1108 SELECT tp_site_code
1109 INTO l_supp_site
1110 FROM msc_trading_partner_sites
1111 WHERE partner_id = p_supplier_id
1112 AND partner_site_id = p_supplier_site_id;
1113 l_log_message := l_log_message || get_message('MSC','MSC_X_PUB_SUPP_SITE',l_language_code) || ': ' || l_supp_site || fnd_global.local_chr(10);
1114
1115 END IF;
1116
1117 IF p_planner_code IS NOT NULL THEN
1118 l_log_message := l_log_message || get_message('MSC','MSC_X_PUB_PLANNER',l_language_code) || ': ' || p_planner_code || fnd_global.local_chr(10);
1119 END IF;
1120
1121 IF p_planning_gp IS NOT NULL THEN
1122 l_log_message := l_log_message || get_message('MSC','MSC_X_PUB_PLAN_GP',l_language_code) || ': ' || p_planning_gp || fnd_global.local_chr(10);
1123 END IF;
1124
1125 IF p_project_id IS NOT NULL THEN
1126 l_log_message := l_log_message || get_message('MSC','MSC_X_PUB_PROJ_NUM',l_language_code) || ': ' || p_project_id || fnd_global.local_chr(10);
1127 END IF;
1128
1129 IF p_task_id IS NOT NULL THEN
1130 l_log_message := l_log_message || get_message('MSC','MSC_X_PUB_TASK_NUM',l_language_code) || ': ' || p_task_id || fnd_global.local_chr(10);
1131 END IF;
1132
1133 IF p_abc_class IS NOT NULL THEN
1134 l_log_message := l_log_message || get_message('MSC','MSC_X_PUB_ABC_CLASS',l_language_code) || ': ' || p_abc_class || fnd_global.local_chr(10);
1135 END IF;
1136 log_message(l_log_message);
1137
1138
1139 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Deleting old safety stock matching the filter criteria');
1140
1141 --dbms_output.put_line('At 2');
1142 --dbms_output.put_line('Horizon date ' || p_horizon_start || ' ' || p_horizon_end);
1143 delete_old_safety_stock(
1144 p_plan_id,
1145 p_org_id,
1146 p_sr_instance_id,
1147 p_planner_code,
1148 p_abc_class,
1149 p_item_id,
1150 p_planning_gp,
1151 p_project_id,
1152 p_task_id,
1153 l_horizon_start,
1154 l_horizon_end,
1155 p_overwrite
1156 );
1157
1158
1159 --dbms_output.put_line('At 3');
1160 --FND_FILE.PUT_LINE(FND_FILE.LOG, 'Start');
1161
1162 Open safety_stock_c (
1163 p_plan_id
1164 ,p_org_id
1165 ,p_sr_instance_id
1166 ,l_horizon_start
1167 ,l_horizon_end
1168 ,p_planner_code
1169 ,p_abc_class
1170 ,p_item_id
1171 ,p_planning_gp
1172 ,p_project_id
1173 ,p_task_id
1174 ,p_supplier_id
1175 ,p_supplier_site_id
1176 ,p_overwrite);
1177
1178 FETCH safety_stock_c BULK COLLECT INTO
1179 t_sr_instance_id,
1180 t_org_id,
1181 t_base_item_id,
1182 t_item_id,
1183 t_period_start_date,
1184 t_bucket_start,
1185 t_bucket_end,
1186 t_days_in_bkt,
1187 t_bucket_type,
1188 t_bucket_index,
1189 t_pub_id,
1190 t_pub,
1191 t_pub_site_id,
1192 t_pub_site,
1193 t_item_name,
1194 t_item_desc,
1195 t_base_item_name,
1196 t_uom_code,
1197 t_planner_code,
1198 t_planning_gp,
1199 t_proj_number,
1200 t_task_number,
1201 t_qty;
1202 CLOSE safety_stock_c;
1203
1204 --dbms_output.put_line('At 4');
1205
1206 -------------------------------------------------------------------------------------
1207
1208 --If it is the first record, then insert safety stock into MSC_SUP_DEM_ENTRIES OR
1209 --If the current record = the prev record (same plan_id, sr_instance_id, org_id and item_id)
1210 -- and no need to fill the gap between, then insert safety stock into MSC_SUP_DEM_ENTRIES OR
1211 --If it it a new record (the plan_id is different from the prev record plan_id or
1212 -- Sr_instance_id of the current is different from the previous sr_instance_id or
1213 -- Org_id of the current record is different from the previous org_id or
1214 -- Item_id of the current record is different from the previous item_id)
1215 -- then insert safety stocks into MSC_SUP_DEM_ENTRIES
1216 --------------------------------------------------------------------------------------
1217
1218 IF t_org_id IS NOT NULL AND t_org_id.COUNT > 0 THEN
1219
1220
1221 --dbms_output.put_line ('Records fetched by cursor ss := ' || t_org_id.COUNT);
1222 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Records fetched by safety stock cursor : ' || t_org_id.COUNT);
1223 --dbms_output.put_line('At 5');
1224 get_optional_info(
1225 p_errbuf
1226 ,p_retcode
1227 ,p_plan_id
1228 ,p_supplier_id
1229 ,p_supplier_site_id
1230 ,t_base_item_id
1231 ,t_item_id
1232 ,t_org_id
1233 ,t_sr_instance_id
1234 ,t_uom_code
1235 ,t_qty
1236 ,t_master_item_name
1237 ,t_master_item_desc
1238 ,t_pub_id
1239 ,t_pub
1240 ,t_pub_site_id
1241 ,t_pub_site
1242 ,t_supp_id
1243 ,t_supp
1244 ,t_supp_site_id
1245 ,t_supp_site
1246 ,t_item_name
1247 ,t_item_desc
1248 ,t_base_item_name
1249 ,t_bucket_index
1250 );
1251
1252 --dbms_output.put_line('At 6');
1253
1254
1255 insert_into_sup_dem(
1256 p_errbuf
1257 ,p_retcode
1258 ,p_plan_id
1259 ,l_horizon_start
1260 ,l_horizon_end
1261 ,SAFETY_STOCK
1262 ,t_sr_instance_id
1263 ,t_org_id
1264 ,t_pub
1265 ,t_pub_id
1266 ,t_pub_site
1267 ,t_pub_site_id
1268 ,t_base_item_id
1269 ,t_item_id
1270 ,t_bucket_type
1271 ,t_bucket_start
1272 ,t_bucket_end
1273 ,t_bucket_index
1274 ,t_qty
1275 ,t_qty
1276 ,t_qty
1277 ,t_item_name
1278 ,t_item_desc
1279 ,t_base_item_name
1280 ,t_proj_number
1281 ,t_task_number
1282 ,t_planning_gp
1283 ,t_uom_code
1284 ,t_planner_code
1285 ,t_period_start_date
1286 ,t_master_item_name
1287 ,t_master_item_desc
1288 ,l_version
1289 ,p_designator
1290 ,l_user_id
1291 ,l_language_code
1292 );
1293
1294 --l_log_message := 'Number of records published: ' || 0 || '.';
1295 --log_message(l_log_message);
1296 l_cursor1 := 0;
1297 END IF;
1298
1299 ------------------------------------------------------------------------------
1300 --projected available balance
1301 ------------------------------------------------------------------------------
1302 OPEN projected_availabe_balance_c (
1303 p_plan_id
1304 ,p_org_id
1305 ,p_sr_instance_id
1306 ,l_horizon_start
1307 ,l_horizon_end
1308 ,p_planner_code
1309 ,p_abc_class
1310 ,p_item_id
1311 ,p_planning_gp
1312 ,p_project_id
1313 ,p_task_id
1314 ,p_supplier_id
1315 ,p_supplier_site_id
1316 ,p_overwrite);
1317 FETCH projected_availabe_balance_c BULK COLLECT INTO
1318 a_sr_instance_id,
1319 a_org_id,
1320 a_base_item_id,
1321 a_item_id,
1322 a_period_start_date,
1323 a_bucket_start,
1324 a_bucket_end,
1325 a_days_in_bkt,
1326 a_bucket_type,
1327 a_bucket_index,
1328 a_pab_type,
1329 a_pub_id,
1330 a_pub,
1331 a_pub_site_id,
1332 a_pub_site,
1333 a_item_name,
1334 a_item_desc,
1335 a_base_item_name,
1336 a_uom_code,
1337 a_planner_code,
1338 a_planning_gp,
1339 a_proj_number,
1340 a_task_number,
1341 a_qty;
1342 CLOSE projected_availabe_balance_c;
1343
1344 IF a_org_id IS NOT NULL AND a_org_id.COUNT > 0 THEN
1345 --dbms_output.put_line ('Records fetched by cursor pab := ' || a_org_id.COUNT);
1346 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Records fetched by pab cursor: ' || a_org_id.COUNT);
1347
1348 get_total_qty (
1349 p_errbuf
1350 ,p_retcode
1351 ,a_item_id
1352 ,a_org_id
1353 ,a_sr_instance_id
1354 ,a_pab_type
1355 ,a_period_start_date
1356 ,a_qty
1357 ,a_total_qty
1358 ,a_temp_qty
1359 );
1360
1361 get_optional_info(
1362 p_errbuf
1363 ,p_retcode
1364 ,p_plan_id
1365 ,p_supplier_id
1366 ,p_supplier_site_id
1367 ,a_base_item_id
1368 ,a_item_id
1369 ,a_org_id
1370 ,a_sr_instance_id
1371 ,a_uom_code
1372 ,a_qty
1373 ,a_master_item_name
1374 ,a_master_item_desc
1375 ,a_pub_id
1376 ,a_pub
1377 ,a_pub_site_id
1378 ,a_pub_site
1379 ,a_supp_id
1380 ,a_supp
1381 ,a_supp_site_id
1382 ,a_supp_site
1383 ,a_item_name
1384 ,a_item_desc
1385 ,a_base_item_name
1386 ,a_bucket_index
1387 );
1388 --dbms_output.put_line('At 6');
1389 insert_into_sup_dem(
1390 p_errbuf
1391 ,p_retcode
1392 ,p_plan_id
1393 ,l_horizon_start
1394 ,l_horizon_end
1395 ,PROJECTED_AVAILABLE_BALANCE
1396 ,a_sr_instance_id
1397 ,a_org_id
1398 ,a_pub
1399 ,a_pub_id
1400 ,a_pub_site
1401 ,a_pub_site_id
1402 ,a_base_item_id
1403 ,a_item_id
1404 ,a_bucket_type
1405 ,a_bucket_start
1406 ,a_bucket_end
1407 ,a_bucket_index
1408 ,a_qty
1409 ,a_total_qty
1410 ,a_temp_qty
1411 ,a_item_name
1412 ,a_item_desc
1413 ,a_base_item_name
1414 ,a_proj_number
1415 ,a_task_number
1416 ,a_planning_gp
1417 ,a_uom_code
1418 ,a_planner_code
1419 ,a_period_start_date
1420 ,a_master_item_name
1421 ,a_master_item_desc
1422 ,l_version
1423 ,p_designator
1424 ,l_user_id
1425 ,l_language_code
1426 );
1427 --END IF;
1428
1429 END IF;
1430 commit;
1431
1432 IF l_cursor1 = 0 THEN
1433 --l_log_message := get_message('MSC','MSC_X_PUB_NUM_RECORDS',l_language_code) || ': ' || 0 || '.' || fnd_global.local_chr(10);
1434 --log_message(l_log_message);
1435 null;
1436 END IF;
1437
1438 IF l_version IS NOT NULL THEN
1439 BEGIN
1440 SELECT 1 INTO l_records_exist
1441 FROM dual
1442 WHERE exists ( SELECT 1
1443 FROM msc_sup_dem_entries
1444 WHERE plan_id = -1
1445 AND publisher_order_type = 2
1446 AND designator = p_designator
1447 AND version = l_version);
1448 EXCEPTION
1449 WHEN OTHERS then
1450 l_records_exist := 0;
1451 END;
1452 IF l_records_exist = 1 then
1453 l_log_message := get_message('MSC','MSC_X_PUB_NEW_VERSION',l_language_code) || ' ' || l_version || '.' || fnd_global.local_chr(10);
1454 log_message(l_log_message);
1455 END IF;
1456 END IF;
1457
1458 EXCEPTION
1459 when others then
1460 --dbms_output.put_line('Error in publish safety stock proc: ' ||sqlerrm);
1461 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error in publish safety stock procedure: ' ||sqlerrm);
1462 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Cannot Publish Safety Stock data ');
1463 p_retcode := 1 ; -- Bug 4560452
1464 p_errbuf := sqlerrm ;
1465
1466 END publish_safety_stocks;
1467
1468 PROCEDURE get_total_qty (
1469 p_err OUT nocopy varchar2,
1470 p_ret OUT nocopy number,
1471 t_item_id IN numberList,
1472 t_org_id IN numberList,
1473 t_sr_instance_id IN numberList,
1474 t_pab_type IN numberList,
1475 t_key_date IN dateList,
1476 t_qty IN numberList,
1477 t_total_qty IN OUT NOCOPY numberList,
1478 t_temp_qty IN OUT NOCOPY numberList
1479 ) IS
1480
1481
1482 BEGIN
1483
1484 --------------------------------------------------------------------------------------
1485 --expiration lot qty := exp_lot - total_demand
1486 --
1487 --IF (l_exp_lot > l_demand and l_exp_lot > 0 ) THEN
1488 -- l_expired_qty := l_exp_lot - l_demand;
1489 --END IF;
1490 --l_pab_total:= l_pab_total + l_onhand + l_supply - (l_demand + l_scrap_demand + l_expired_qty);
1491 -----------------------------------------------------------------------------------------
1492
1493 ----FND_FILE.PUT_LINE(FND_FILE.LOG, 'in get total qty ' || t_item_id.COUNT);
1494 IF t_item_id is not null and t_item_id.COUNT > 0 then
1495 --dbms_output.put_line('get total: ' || t_item_id.COUNT);
1496
1497 FOR j in 1..t_item_id.COUNT loop
1498 t_total_qty.EXTEND;
1499 t_temp_qty.EXTEND;
1500 IF (j > 1 and t_sr_instance_id(j) = t_sr_instance_id(j-1) and
1501 t_org_id(j) = t_org_id(j-1) and
1502 t_item_id(j) = t_item_id(j-1)) THEN
1503 IF (t_pab_type(j) in (PAB_ONHAND, PAB_SUPPLY)) THEN
1504 t_total_qty(j) := nvl(t_total_qty(j),0) + t_total_qty(j-1) + nvl(t_qty(j),0);
1505 t_temp_qty(j) := 0;
1506 ELSIF (t_pab_type(j) = PAB_SCRAP_DEMAND ) THEN
1507 t_total_qty(j) := nvl(t_total_qty(j),0) + t_total_qty(j-1) - nvl(t_qty(j),0);
1508 t_temp_qty(j) := 0;
1509 ELSIF (t_pab_type(j) = PAB_DEMAND) THEN
1510 t_total_qty(j) := nvl(t_total_qty(j),0) + t_total_qty(j-1) - nvl(t_qty(j),0);
1511 t_temp_qty(j) := nvl(t_temp_qty(j),0) - nvl(t_qty(j),0);
1512 ELSIF (t_pab_type(j) = PAB_EXP_LOT) THEN
1513 t_total_qty(j) := nvl(t_total_qty(j),0) + t_total_qty(j-1);
1514 t_temp_qty(j) := nvl(t_temp_qty(j),0) + nvl(t_qty(j),0);
1515 END IF;
1516 ----FND_FILE.PUT_LINE(FND_FILE.LOG, 'ELSE TOTAL QTY: ' || t_total_qty(j));
1517 ----FND_FILE.PUT_LINE(FND_FILE.LOG, 'ELSE TEMP QTY: ' || t_temp_qty(j));
1518 --dbms_output.put_line('ELSE Total qty ' || t_total_qty(j) || ' date ' || t_key_date(j));
1519 --dbms_output.put_line('ELSE Temp qty ' || t_temp_qty(j) || ' date ' || t_key_date(j));
1520
1521 ELSIF (j = 1 or t_sr_instance_id(j) <> t_sr_instance_id(j-1) or
1522 t_org_id(j) <> t_org_id(j-1) or
1523 t_item_id(j) <> t_item_id(j-1) or
1524 t_key_date(j) <> t_key_date(j-1)) THEN
1525
1526 IF (t_pab_type(j) = PAB_DEMAND) THEN
1527 t_total_qty(j) := nvl(- t_qty(j), 0);
1528 t_temp_qty(j) := nvl(- t_qty(j),0) ;
1529 ELSIF (t_pab_type(j) = PAB_SCRAP_DEMAND) THEN
1530 t_total_qty(j) := nvl(- t_qty(j), 0);
1531 t_temp_qty(j) := 0;
1532 ELSIF (t_pab_type(j) = PAB_EXP_LOT) THEN
1533 t_total_qty(j) := 0;
1534 t_temp_qty(j) := nvl(t_qty(j),0);
1535 ELSE
1536 t_total_qty(j) := nvl(t_qty(j), 0);
1537 t_temp_qty(j) := 0;
1538 END IF;
1539 ----FND_FILE.PUT_LINE(FND_FILE.LOG, 'IF TOTAL QTY: ' || t_total_qty(j));
1540 ----FND_FILE.PUT_LINE(FND_FILE.LOG, 'IF TEMP QTY: ' || t_temp_qty(j));
1541 --dbms_output.put_line('IF Total qty ' || t_total_qty(j) || ' date ' || t_key_date(j));
1542 --dbms_output.put_line('IF Temp qty ' || t_temp_qty(j) || ' date ' || t_key_date(j));
1543
1544 END IF;
1545 END LOOP;
1546 END IF;
1547 p_ret := 0;
1548 p_err := null ;
1549
1550 EXCEPTION
1551 WHEN OTHERS THEN
1552 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error in get total quantity proc: ' ||sqlerrm);
1553 --dbms_output.put_line('Error in get total quantity proc: ' || sqlerrm);
1554 p_ret := 1;
1555 p_err := sqlerrm ;
1556 END get_total_qty;
1557
1558 PROCEDURE get_optional_info(
1559 p_err OUT nocopy varchar2,
1560 p_ret OUT nocopy number,
1561 p_plan_id IN number,
1562 p_supp_id IN number,
1563 p_supp_site_id IN number,
1564 t_base_item_id IN numberList,
1565 t_item_id IN numberList,
1566 t_org_id IN numberList,
1567 t_sr_instance_id IN numberList,
1568 t_uom_code IN itemUomList,
1569 t_qty IN numberList,
1570 t_master_item_name IN OUT NOCOPY itemNameList,
1571 t_master_item_desc IN OUT NOCOPY itemDescList,
1572 t_pub_id IN OUT NOCOPY numberList,
1573 t_pub IN OUT NOCOPY companyNameList,
1574 t_pub_site_id IN OUT NOCOPY numberList,
1575 t_pub_site IN OUT NOCOPY companySiteList,
1576 t_supp_id IN OUT NOCOPY numberList,
1577 t_supp IN OUT NOCOPY companyNameList,
1578 t_supp_site_id IN OUT NOCOPY numberList,
1579 t_supp_site IN OUT NOCOPY companySiteList,
1580 t_item_name IN OUT NOCOPY itemNameList,
1581 t_item_desc IN OUT NOCOPY itemDescList,
1582 t_base_item_name IN OUT NOCOPY itemNameList,
1583 t_bucket_index IN OUT NOCOPY numberList
1584 ) IS
1585
1586
1587 l_conversion_found boolean;
1588 l_conversion_rate number;
1589 l_lead_time number;
1590 l_using_org_id number;
1591 BEGIN
1592
1593 if t_item_id is not null and t_item_id.COUNT > 0 then
1594 --dbms_output.put_line('In get_optional_info : ' || t_item_id.COUNT);
1595 for j in 1..t_item_id.COUNT loop
1596 /***
1597 t_pub_id.EXTEND;
1598 t_pub.EXTEND;
1599 t_pub_site.EXTEND;
1600 t_pub_site_id.EXTEND;
1601
1602 if (j = 1) or (t_org_id(j-1) <> t_org_id(j)) or (t_sr_instance_id(j-1) <> t_sr_instance_id(j)) then
1603 BEGIN
1604 select c.company_id,
1605 c.company_name,
1606 s.company_site_id,
1607 s.company_site_name
1608 into t_pub_id(j),
1609 t_pub(j),
1610 t_pub_site_id(j),
1611 t_pub_site(j)
1612 from msc_companies c,
1613 msc_company_sites s,
1614 msc_trading_partner_maps m,
1615 msc_trading_partners t
1616 where t.sr_tp_id = t_org_id(j) and
1617 t.sr_instance_id = t_sr_instance_id(j) and
1618 t.partner_type = 3 and
1619 m.tp_key = t.partner_id and
1620 m.map_type = 2 and
1621 s.company_site_id = m.company_key and
1622 c.company_id = s.company_id;
1623 EXCEPTION
1624 WHEN NO_DATA_FOUND THEN
1625 t_pub_id(j) := null;
1626 t_pub(j) := null;
1627 t_pub_site_id(j) := null;
1628 t_pub_site(j) := null;
1629 END;
1630
1631 else
1632 t_pub_id(j) := t_pub_id(j-1);
1633 t_pub(j) := t_pub(j-1);
1634 t_pub_site_id(j) := t_pub_site_id(j-1);
1635 t_pub_site(j) := t_pub_site(j-1);
1636 end if;
1637 ***/
1638
1639
1640 /*-------------------------------------------------------------------------------
1641 get the item_description -- this works for both standard item
1642 ---------------------------------------------------------------------------------*/
1643 begin
1644 select item_name, description
1645 into t_item_name(j), t_item_desc(j)
1646 from msc_system_items
1647 where sr_instance_id = t_sr_instance_id(j)
1648 and organization_id = t_org_id(j)
1649 and inventory_item_id = t_item_id(j)
1650 and plan_id = -1;
1651
1652 exception
1653 when others then
1654 t_item_name(j) := null;
1655 t_item_desc(j) := null;
1656 end;
1657
1658 /*-------------------------------------------------------------------------------
1659 get the base item name
1660 ---------------------------------------------------------------------------------*/
1661 begin
1662 select item_name
1663 into t_base_item_name(j)
1664 from msc_system_items
1665 where sr_instance_id = t_sr_instance_id(j)
1666 and organization_id = t_org_id(j)
1667 and inventory_item_id = t_base_item_id(j)
1668 and plan_id = -1;
1669
1670 exception
1671 when others then
1672 t_base_item_name(j) := null;
1673 end;
1674
1675 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'pub id ' || t_pub_id(j) || ' site ' || t_pub_site_id(j));
1676 --dbms_output.put_line(' Get pub id ' || t_pub(j) || ': ' || t_pub_site(j));
1677
1678 ----------------------------------------------------------------------
1679 -- getting the supplier info
1680 ----------------------------------------------------------------------
1681 t_supp_id.EXTEND;
1682 t_supp.EXTEND;
1683 t_supp_site.EXTEND;
1684 t_supp_site_id.EXTEND;
1685
1686 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'sup id ' || p_supp_id || ' site ' || p_supp_site_id);
1687 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'item' || t_item_id(j) || ' org' || t_org_id(j) || 'sr ' || t_sr_instance_id(j));
1688 if (p_supp_id is not null ) THEN
1689 BEGIN
1690 --dbms_output.put_line('BEGIN');
1691 select distinct c.company_id,
1692 c.company_name
1693 into t_supp_id(j),
1694 t_supp(j)
1695 from msc_companies c,
1696 msc_trading_partner_maps m,
1697 msc_company_relationships r,
1698 msc_item_suppliers mis
1699 where m.tp_key = mis.supplier_id and
1700 m.map_type = 1 and
1701 r.relationship_id = m.company_key and
1702 r.subject_id = t_pub_id(j) and
1703 r.relationship_type = 2 and
1704 c.company_id = r.object_id and
1705 mis.plan_id = -1 and
1706 mis.organization_id = t_org_id(j) and
1707 mis.sr_instance_id = t_sr_instance_id(j) and
1708 mis.inventory_item_id = t_item_id(j) and
1709 mis.supplier_id = p_supp_id;
1710
1711
1712 EXCEPTION
1713 WHEN OTHERS THEN
1714 t_supp_id(j) := null;
1715 t_supp(j) := null;
1716 ---------------------------------------------------------------------------
1717 -- if the supplier or supplier site is not a valid one
1718 -- should not publish the record by setting the t_pub_id(j) to null
1719 ----------------------------------------------------------------------------
1720 t_pub_id(j) := null; -- the data should not be populated.
1721 t_bucket_index(j) := null;
1722 p_ret := 1;
1723 p_err := sqlerrm ;
1724 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error in getting Supplier info. Please check the ASL settings. Error: '||sqlerrm);
1725 END;
1726
1727
1728 IF (p_supp_site_id is not null) then
1729 BEGIN
1730 select s.company_site_id,
1731 s.company_site_name
1732 into t_supp_site_id(j),
1733 t_supp_site(j)
1734 from msc_company_sites s,
1735 msc_trading_partner_maps m,
1736 msc_item_suppliers mis
1737 where m.tp_key = mis.supplier_site_id and
1738 m.map_type = 3 and
1739 s.company_site_id = m.company_key and
1740 s.company_id = t_supp_id(j) and
1741 m.tp_key = mis.supplier_site_id and
1742 mis.plan_id = -1 and
1743 mis.organization_id = t_org_id(j) and
1744 mis.sr_instance_id = t_sr_instance_id(j) and
1745 mis.inventory_item_id = t_item_id(j) and
1746 mis.supplier_id = p_supp_id and
1747 mis.supplier_site_id = p_supp_site_id
1748 ;
1749 EXCEPTION
1750 WHEN OTHERS THEN
1751
1752 t_supp_site_id(j) := null;
1753 t_supp_site(j) := null;
1754 ---------------------------------------------------------------------------
1755 -- if the supplier or supplier site is not a valid one
1756 -- should not publish the record by setting the t_pub_id(j) to null
1757 ----------------------------------------------------------------------------
1758 t_pub_id(j) := null; -- the data should not be populated.
1759 t_bucket_index(j) := null;
1760 p_ret := 1;
1761 p_err := sqlerrm ;
1762 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error in getting Supplier Site. Please check the ASL settings. Error: '||sqlerrm);
1763 END;
1764 END IF;
1765
1766 end if;
1767
1768 -- Bug 4560452
1769 -- Do not need to find supp_site_id as it is not used anywhere in the code.
1770 /* ELSE
1771 ------------------------------------------------
1772 -- IF p_supp_site is not provided
1773 -----------------------------------------------
1774
1775 BEGIN
1776 select s.company_site_id,
1777 s.company_site_name
1778 into t_supp_site_id(j),
1779 t_supp_site(j)
1780 from msc_company_sites s,
1781 msc_trading_partner_maps m,
1782 msc_item_suppliers mis
1783 where m.map_type = 3 and
1784 s.company_site_id = m.company_key and
1785 s.company_id = t_supp_id(j) and
1786 m.tp_key = mis.supplier_site_id and
1787 mis.plan_id = -1 and
1788 mis.organization_id = t_org_id(j) and
1789 mis.sr_instance_id = t_sr_instance_id(j) and
1790 mis.inventory_item_id = t_item_id(j) and
1791 mis.supplier_id = p_supp_id
1792 ;
1793 EXCEPTION
1794 WHEN OTHERS THEN
1795
1796 t_supp_site_id(j) := null;
1797 t_supp_site(j) := null;
1798 ---------------------------------------------------------------------------
1799 -- if the supplier or supplier site is not a valid one
1800 -- should not publish the record by setting the t_pub_id(j) to null
1801 ----------------------------------------------------------------------------
1802 t_pub_id(j) := null; -- the data should not be populated.
1803 t_bucket_index(j) := null;
1804
1805 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error in sup_site : '||sqlerrm);
1806 END; */
1807
1808 --FND_FILE.PUT_LINE(FND_FILE.LOG, 'Item ' || t_item_name(j) || ' Supplier ' || t_supp(j) || ' Supplier site ' || t_supp_site(j));
1809 ------------------------------------------------------------------
1810 -- getting the master item name
1811 ------------------------------------------------------------------
1812
1813 t_master_item_name.EXTEND;
1814 t_master_item_desc.EXTEND;
1815
1816 select item_name,
1817 description
1818 into t_master_item_name(j),
1819 t_master_item_desc(j)
1820 from msc_items
1821 where inventory_item_id = t_item_id(j);
1822
1823
1824 end loop;
1825 end if;
1826 p_ret := 0;
1827 p_err := null ;
1828 EXCEPTION
1829 WHEN others then
1830 null;
1831 --dbms_output.put_line('Error in get option info proc: ' || sqlerrm);
1832 FND_FILE.PUT_LINE(FND_FILE.LOG, ' Error in get option info proc: ' || sqlerrm);
1833 p_ret := 1;
1834 p_err := sqlerrm ;
1835
1836 END get_optional_info;
1837
1838
1839 PROCEDURE insert_into_sup_dem (
1840 p_err OUT nocopy varchar2,
1841 p_ret OUT nocopy number,
1842 p_plan_id IN number,
1843 p_horizon_start IN date,
1844 p_horizon_end IN date,
1845 p_type IN number,
1846 t_sr_instance_id IN numberList,
1847 t_org_id IN numberList,
1848 t_pub IN companyNameList,
1849 t_pub_id IN numberList,
1850 t_pub_site IN companySiteList,
1851 t_pub_site_id IN numberList,
1852 t_base_item_id IN numberList,
1853 t_item_id IN numberList,
1854 t_bucket_type IN numberList,
1855 t_bucket_start IN dateList,
1856 t_bucket_end IN dateList,
1857 t_bucket_index IN numberList,
1858 t_qty IN numberList,
1859 t_total_qty IN numberList,
1860 t_temp_qty IN numberList,
1861 t_item_name IN itemNameList,
1862 t_item_desc IN itemDescList,
1863 t_base_item_name IN itemNameList,
1864 t_proj_number IN numberList,
1865 t_task_number IN numberList,
1866 t_planning_gp IN planningGroupList,
1867 t_uom_code IN itemUomList,
1868 t_planner_code IN plannerCodeList,
1869 t_key_date IN dateList,
1870 t_master_item_name IN itemNameList,
1871 t_master_item_desc IN itemDescList,
1872 p_version IN varchar2,
1873 p_designator IN varchar2,
1874 p_user_id IN number,
1875 p_language_code IN varchar2
1876 ) IS
1877
1878
1879 CURSOR get_bucket_date (p_plan_id in number,
1880 p_sr_instance_id in number,
1881 p_org_id in number,
1882 p_start_date in date,
1883 p_end_date in date) IS
1884 SELECT mpb.bucket_index, trunc(mpb.bkt_start_date), trunc(mpb.bkt_end_date), mpb.bucket_type
1885 FROM msc_plan_buckets mpb ,
1886 msc_plan_organizations_v ov -- Bug# 3913477
1887 WHERE ov.plan_id = p_plan_id
1888 AND ov.sr_instance_id = p_sr_instance_id
1889 AND ov.planned_organization = p_org_id
1890 AND mpb.plan_id = ov.plan_id
1891 AND mpb.curr_flag = 1
1892 AND bkt_start_date between nvl(p_start_date, mpb.bkt_start_date)
1893 and nvl(p_end_date, mpb.bkt_end_date)
1894 ORDER BY bucket_index;
1895
1896
1897 t_ins_pub companyNameList;
1898 t_ins_pub_id numberList;
1899 t_ins_pub_site companySiteList;
1900 t_ins_pub_site_id numberList;
1901 t_ins_item_id numberList;
1902 t_ins_order_type numberList;
1903 t_ins_qty numberList;
1904 t_ins_item_name itemNameList;
1905 t_ins_item_desc itemDescList;
1906 t_ins_proj_number numberList;
1907 t_ins_task_number numberList;
1908 t_ins_planning_gp planningGroupList;
1909 t_ins_uom_code itemUomList ;
1910 t_ins_planner_code plannerCodeList;
1911 t_ins_key_date dateList;
1912 t_ins_ship_date dateList;
1913 t_ins_receipt_date dateList;
1914 t_ins_master_item_name itemNameList;
1915 t_ins_master_item_desc itemDescList;
1916
1917 t_days_in_bkt numberList;
1918 t_period_start_date dateList;
1919 t_pab_type numberList;
1920
1921 b_bkt_index numberList;
1922 b_bkt_start_date dateList;
1923 b_bkt_end_date dateList;
1924 b_bkt_type numberList;
1925
1926 l_order_type_desc mfg_lookups.meaning%type;
1927 l_log_message VARCHAR2(1000);
1928 l_rowcount number;
1929 l_qty number;
1930 l_exp_qty number;
1931 l_record_inserted number;
1932 l_next_work_date msc_calendar_dates.calendar_date%type;
1933 l_prev_work_date date;
1934 l_bucket_type_desc mfg_lookups.meaning%type;
1935 l_date date;
1936 l_total number;
1937 l_plan_start_date date;
1938
1939 BEGIN
1940
1941 l_rowcount := 0;
1942 l_record_inserted := 0;
1943 l_total := 0;
1944 t_ins_pub := companyNameList();
1945 t_ins_pub_id := numberList();
1946 t_ins_pub_site := companySiteList();
1947 t_ins_pub_site_id := numberList();
1948 t_ins_item_id := numberList();
1949 t_ins_order_type := numberList();
1950 t_ins_qty := numberList();
1951 t_ins_item_name := itemNameList();
1952 t_ins_item_desc := itemDescList();
1953 t_ins_proj_number := numberList();
1954 t_ins_task_number := numberList();
1955 t_ins_planning_gp := planningGroupList();
1956 t_ins_uom_code := itemUomList();
1957 t_ins_planner_code := plannerCodeList();
1958 t_ins_key_date := dateList();
1959 t_ins_ship_date := dateList();
1960 t_ins_receipt_date := dateList();
1961 t_ins_master_item_name := itemNameList();
1962 t_ins_master_item_desc := itemDescList();
1963
1964 t_days_in_bkt := numberList();
1965 t_pab_type := numberList();
1966 b_bkt_index := numberList();
1967 b_bkt_type := numberList();
1968
1969
1970 --dbms_output.put_line('Type ' || p_type || ' and count ' || t_pub_id.COUNT);
1971 IF (t_pub_id is not null and t_pub_id.COUNT >0 and p_type = SAFETY_STOCK ) THEN
1972 --l_log_message := get_message('MSC','MSC_X_PUB_NUM_RECORDS',p_language_code) ||
1973 --': ' || t_pub_id.COUNT || '.' || fnd_global.local_chr(10);
1974 --log_message(l_log_message);
1975 -----------------------------------------------------
1976
1977 FOR j in 1..t_pub_id.COUNT LOOP
1978
1979 IF (t_pub_id(j) is not null AND
1980 (j=1 OR (
1981 t_sr_instance_id(j) <> t_sr_instance_id(j-1) OR
1982 t_pub_id(j) <> nvl(t_pub_id(j-1), -99999) OR
1983 t_pub_site_id(J) <> t_pub_site_id(j-1) OR
1984 t_item_id(j) <> t_item_id(j-1) OR
1985 t_bucket_index(j) - nvl(t_bucket_index(j-1), t_bucket_index(j) -1) = 1 )))THEN
1986
1987 l_order_type_desc := get_order_type (SAFETY_STOCK);
1988 l_bucket_type_desc := msc_x_util.get_lookup_meaning('MSC_X_BUCKET_TYPE',t_bucket_type(j));
1989 --dbms_output.Put_line('HERE insert ' || t_qty(j) || ' DATE ' || t_key_date(j));
1990 --FND_FILE.PUT_LINE(FND_FILE.LOG, 'safety stock: ' || t_qty(j) || ' date ' || t_key_date(j));
1991 --dbms_output.put_line('Org ' || t_org_id(j) || 'SR ' || t_sr_instance_id(j) || 'Bucket ' ||t_bucket_type(j) || 'Key date ' || t_key_date(j));
1992
1993 -- IF (l_next_work_date = t_key_date(j)) THEN
1994 IF (t_key_date(j) >= p_horizon_start and t_key_date(j) <= p_horizon_end ) THEN
1995
1996 insert into msc_sup_dem_entries (
1997 transaction_id,
1998 plan_id,
1999 sr_instance_id,
2000 publisher_name,
2001 publisher_id,
2002 publisher_site_name,
2003 publisher_site_id,
2004 publisher_order_type,
2005 publisher_order_type_desc,
2006 bucket_type_desc,
2007 bucket_type,
2008 inventory_item_id,
2009 item_name,
2010 owner_item_name,
2011 item_description,
2012 owner_item_description,
2013 base_item_id,
2014 base_item_name,
2015 primary_uom,
2016 uom_code,
2017 tp_uom_code,
2018 key_date,
2019 new_schedule_date,
2020 quantity,
2021 primary_quantity,
2022 tp_quantity,
2023 last_refresh_number,
2024 posting_party_name,
2025 posting_party_id,
2026 created_by,
2027 creation_date,
2028 last_updated_by,
2029 last_update_date,
2030 project_number,
2031 task_number,
2032 planning_group,
2033 planner_code,
2034 version,
2035 designator
2036 ) values (
2037 msc_sup_dem_entries_s.nextval,
2038 -1,
2039 -1,
2040 t_pub(j),
2041 t_pub_id(j),
2042 t_pub_site(j),
2043 t_pub_site_id(j),
2044 SAFETY_STOCK,
2045 l_order_type_desc,
2046 l_bucket_type_desc,
2047 t_bucket_type(j),
2048 t_item_id(j),
2049 t_master_item_name(j),
2050 t_item_name(j),
2051 nvl(t_master_item_desc(j), t_item_desc(j)),
2052 t_item_desc(j),
2053 t_base_item_id(j),
2054 t_base_item_name(j),
2055 t_uom_code(j),
2056 t_uom_code(j),
2057 null,
2058 t_key_date(j),
2059 t_key_date(j),
2060 t_qty(j),
2061 t_qty(j),
2062 null,
2063 msc_cl_refresh_s.nextval,
2064 t_pub(j),
2065 t_pub_id(j),
2066 nvl(p_user_id,-1),
2067 sysdate,
2068 nvl(p_user_id,-1),
2069 sysdate,
2070 t_proj_number(j),
2071 t_task_number(j),
2072 t_planning_gp(j),
2073 t_planner_code(j),
2074 p_version,
2075 p_designator);
2076 l_record_inserted := l_record_inserted + 1;
2077 END IF;
2078
2079 ELSIF (j > 1 AND t_pub_id(j) is not null AND
2080 t_pub_id(j-1) is not null AND
2081 t_pub_id(j) = t_pub_id(j-1) AND
2082 t_pub_site_id(j) = t_pub_site_id(j-1) AND t_item_id(j) = t_item_id(j-1) AND
2083 t_bucket_index(j) - nvl(t_bucket_index(j-1), t_bucket_index(j) -1) > 1 ) THEN
2084 --FND_FILE.PUT_LINE(FND_FILE.LOG, 'safety stock: ' || t_qty(j) || 'date ' || t_key_date(j));
2085
2086 ----------------------------------------------------------------------
2087 --if the current record = the previous record (same plan_id, org_id,
2088 --sr_instance_id, item_id) and current bucket index prev record
2089 --bucket index <> 1, need to fill up the gap (before the safety stock
2090 --is changed)
2091 -----------------------------------------------------------------------
2092 ----FND_FILE.PUT_LINE(FND_FILE.LOG, 'bucket j-1 ' || t_bucket_start(j-1));
2093 ----FND_FILE.PUT_LINE(FND_FILE.LOG, 'bucket j ' || t_bucket_start(j));
2094
2095 Open get_bucket_date (p_plan_id,
2096 t_sr_instance_id(j),
2097 t_org_id(j),
2098 t_bucket_start(j-1),
2099 t_bucket_start(j));
2100 FETCH get_bucket_date BULK COLLECT INTO
2101 b_bkt_index,
2102 b_bkt_start_date,
2103 b_bkt_end_date,
2104 b_bkt_type;
2105 CLOSE get_bucket_date;
2106
2107 l_qty := t_qty(j);
2108
2109 IF b_bkt_index IS NOT NULL AND b_bkt_index.COUNT > 0 THEN
2110
2111 FOR k in 1..b_bkt_index.COUNT LOOP
2112
2113 IF (k > 1) THEN
2114
2115 IF (k = b_bkt_index.COUNT) THEN
2116 ----FND_FILE.PUT_LINE(FND_FILE.LOG, 'k = b_bkt_index.COUNT ' || b_bkt_index.COUNT);
2117 b_bkt_end_date(k) := t_key_date(j);
2118 l_qty := t_qty(j);
2119 ----FND_FILE.PUT_LINE(FND_FILE.LOG, 'end_date ' || b_bkt_end_date(k));
2120 ELSE
2121 --FND_FILE.PUT_LINE(FND_FILE.LOG, 'At 7d , j: ' || j);
2122 l_qty := t_qty(j-1);
2123
2124 END IF;
2125
2126 l_order_type_desc := msc_x_util.get_lookup_meaning ('MSC_X_ORDER_TYPE', SAFETY_STOCK);
2127 l_bucket_type_desc := msc_x_util.get_lookup_meaning('MSC_X_BUCKET_TYPE',b_bkt_type(k));
2128 --dbms_output.Put_line('insert for the gap ' || t_qty(j));
2129
2130
2131 /**l_prev_work_date := msc_calendar.prev_work_day(t_org_id(j),
2132 t_sr_instance_id(j),
2133 1,
2134 b_bkt_end_date(k));
2135 **/
2136
2137 --dbms_output.put_line('Start ' || b_bkt_start_date(k) || ' End ' || b_bkt_end_date(k) );
2138 --dbms_output.put_line(' prev ' || l_prev_work_date );
2139 IF (b_bkt_end_date(k) >= p_horizon_start and b_bkt_end_date(k) <= p_horizon_end ) THEN
2140
2141 insert into msc_sup_dem_entries (
2142 transaction_id,
2143 plan_id,
2144 sr_instance_id,
2145 publisher_name,
2146 publisher_id,
2147 publisher_site_name,
2148 publisher_site_id,
2149 publisher_order_type,
2150 publisher_order_type_desc,
2151 bucket_type_desc,
2152 bucket_type,
2153 inventory_item_id,
2154 item_name,
2155 owner_item_name,
2156 item_description,
2157 owner_item_description,
2158 base_item_id,
2159 base_item_name,
2160 primary_uom,
2161 uom_code,
2162 tp_uom_code,
2163 key_date,
2164 new_schedule_date,
2165 quantity,
2166 primary_quantity,
2167 tp_quantity,
2168 last_refresh_number,
2169 posting_party_name,
2170 posting_party_id,
2171 created_by,
2172 creation_date,
2173 last_updated_by,
2174 last_update_date,
2175 project_number,
2176 task_number,
2177 planning_group,
2178 planner_code,
2179 version,
2180 designator
2181 ) values (
2182 msc_sup_dem_entries_s.nextval,
2183 -1,
2184 -1,
2185 t_pub(j),
2186 t_pub_id(j),
2187 t_pub_site(j),
2188 t_pub_site_id(j),
2189 SAFETY_STOCK,
2190 l_order_type_desc,
2191 l_bucket_type_desc,
2192 b_bkt_type(k),
2193 t_item_id(j),
2194 t_master_item_name(j),
2195 t_item_name(j),
2196 nvl(t_master_item_desc(j), t_item_desc(j)),
2197 t_item_desc(j),
2198 t_base_item_id(j),
2199 t_base_item_name(j),
2200 t_uom_code(j),
2201 t_uom_code(j),
2202 null,
2203 b_bkt_end_date(k), --l_prev_work_date,
2204 b_bkt_end_date(k), --l_prev_work_date,
2205 l_qty,
2206 l_qty,
2207 null,
2208 msc_cl_refresh_s.nextval,
2209 t_pub(j),
2210 t_pub_id(j),
2211 nvl(p_user_id,-1),
2212 sysdate,
2213 nvl(p_user_id,-1),
2214 sysdate,
2215 t_proj_number(j),
2216 t_task_number(j),
2217 t_planning_gp(j),
2218 t_planner_code(j),
2219 p_version,
2220 p_designator);
2221
2222 l_record_inserted := l_record_inserted + 1;
2223 END IF; --- end horizon date range
2224 END IF;
2225
2226 END LOOP; -- FOR K LOOP
2227 END IF;
2228 END IF; --the if loop directly inside the for loop for j.
2229
2230 /*-------------------------------------------------------------------------------
2231 -- Bug# 3913477 : for levelling safety stock after last safety stock data
2232 available in msc_safety_stocks table till plan_end_date or horizon_end_date
2233 whichever ends first. The loop inserts data for MULTIORG case or MULTI_ITEM
2234 case whenever pub_id or pub_site_id( ORG_ID) or item_id changes.
2235 -------------------------------------------------------------------------------*/
2236 IF (j > 1 AND
2237 (t_pub_id(j) <> t_pub_id(j-1) OR
2238 t_pub_site_id(j) <> t_pub_site_id(j-1) OR
2239 t_item_id(j) <> t_item_id(j-1) )) THEN
2240
2241 Open get_bucket_date (p_plan_id,
2242 t_sr_instance_id(j-1),
2243 t_org_id(j-1),
2244 t_bucket_start(j-1),
2245 p_horizon_end);
2246 FETCH get_bucket_date BULK COLLECT INTO
2247 b_bkt_index,
2248 b_bkt_start_date,
2249 b_bkt_end_date,
2250 b_bkt_type;
2251 CLOSE get_bucket_date;
2252
2253 IF b_bkt_index IS NOT NULL AND b_bkt_index.COUNT > 0 THEN
2254
2255 FOR k in 1..b_bkt_index.COUNT LOOP
2256
2257
2258 IF (k > 1) THEN
2259 l_qty := t_qty(j-1);
2260
2261 l_order_type_desc := msc_x_util.get_lookup_meaning ('MSC_X_ORDER_TYPE', SAFETY_STOCK);
2262 l_bucket_type_desc := msc_x_util.get_lookup_meaning('MSC_X_BUCKET_TYPE',b_bkt_type(k));
2263
2264 IF (b_bkt_end_date(k) >= p_horizon_start and b_bkt_end_date(k) <= p_horizon_end ) THEN
2265 insert into msc_sup_dem_entries (
2266 transaction_id,
2267 plan_id,
2268 sr_instance_id,
2269 publisher_name,
2270 publisher_id,
2271 publisher_site_name,
2272 publisher_site_id,
2273 publisher_order_type,
2274 publisher_order_type_desc,
2275 bucket_type_desc,
2276 bucket_type,
2277 inventory_item_id,
2278 item_name,
2279 owner_item_name,
2280 item_description,
2281 owner_item_description,
2282 base_item_id,
2283 base_item_name,
2284 primary_uom,
2285 uom_code,
2286 tp_uom_code,
2287 key_date,
2288 new_schedule_date,
2289 quantity,
2290 primary_quantity,
2291 tp_quantity,
2292 last_refresh_number,
2293 posting_party_name,
2294 posting_party_id,
2295 created_by,
2296 creation_date,
2297 last_updated_by,
2298 last_update_date,
2299 project_number,
2300 task_number,
2301 planning_group,
2302 planner_code,
2303 version,
2304 designator
2305 ) values (
2306 msc_sup_dem_entries_s.nextval,
2307 -1,
2308 -1,
2309 t_pub(j-1),
2310 t_pub_id(j-1),
2311 t_pub_site(j-1),
2312 t_pub_site_id(j-1),
2313 SAFETY_STOCK,
2314 l_order_type_desc,
2315 l_bucket_type_desc,
2316 b_bkt_type(k),
2317 t_item_id(j-1),
2318 t_master_item_name(j-1),
2319 t_item_name(j-1),
2320 nvl(t_master_item_desc(j-1), t_item_desc(j-1)),
2321 t_item_desc(j-1),
2322 t_base_item_id(j-1),
2323 t_base_item_name(j-1),
2324 t_uom_code(j-1),
2325 t_uom_code(j-1),
2326 null,
2327 b_bkt_end_date(k), --l_prev_work_date,
2328 b_bkt_end_date(k), --l_prev_work_date,
2329 l_qty,
2330 l_qty,
2331 null,
2332 msc_cl_refresh_s.nextval,
2333 t_pub(j-1),
2334 t_pub_id(j-1),
2335 nvl(p_user_id,-1),
2336 sysdate,
2337 nvl(p_user_id,-1),
2338 sysdate,
2339 t_proj_number(j-1),
2340 t_task_number(j-1),
2341 t_planning_gp(j-1),
2342 t_planner_code(j-1),
2343 p_version,
2344 p_designator);
2345
2346 l_record_inserted := l_record_inserted + 1;
2347 END IF; --- end horizon date range
2348 END IF;
2349
2350 END LOOP; -- FOR K LOOP
2351 END IF;
2352 END IF ;
2353
2354 /*---------------------------------------------------------------------
2355 for inserting trailors for last data i.e. for SINGLE ORG case or
2356 in case of multiorg for the last pub_site_id(ORG_ID) or last item_id
2357 data available in table msc_safety_stock.
2358 ------------------------------------------------------------------------*/
2359
2360 IF (j = t_pub_id.COUNT) then
2361 Open get_bucket_date (p_plan_id,
2362 t_sr_instance_id(j),
2363 t_org_id(j),
2364 t_bucket_start(j),
2365 p_horizon_end);
2366 FETCH get_bucket_date BULK COLLECT INTO
2367 b_bkt_index,
2368 b_bkt_start_date,
2369 b_bkt_end_date,
2370 b_bkt_type;
2371 CLOSE get_bucket_date;
2372
2373 IF b_bkt_index IS NOT NULL AND b_bkt_index.COUNT > 0 THEN
2374
2375 FOR k in 1..b_bkt_index.COUNT LOOP
2376
2377 IF (k > 1) THEN
2378 l_qty := t_qty(j);
2379
2380 l_order_type_desc := msc_x_util.get_lookup_meaning ('MSC_X_ORDER_TYPE', SAFETY_STOCK);
2381 l_bucket_type_desc := msc_x_util.get_lookup_meaning('MSC_X_BUCKET_TYPE',b_bkt_type(k));
2382
2383 IF (b_bkt_end_date(k) >= p_horizon_start and b_bkt_end_date(k) <= p_horizon_end ) THEN
2384 insert into msc_sup_dem_entries (
2385 transaction_id,
2386 plan_id,
2387 sr_instance_id,
2388 publisher_name,
2389 publisher_id,
2390 publisher_site_name,
2391 publisher_site_id,
2392 publisher_order_type,
2393 publisher_order_type_desc,
2394 bucket_type_desc,
2395 bucket_type,
2396 inventory_item_id,
2397 item_name,
2398 owner_item_name,
2399 item_description,
2400 owner_item_description,
2401 base_item_id,
2402 base_item_name,
2403 primary_uom,
2404 uom_code,
2405 tp_uom_code,
2406 key_date,
2407 new_schedule_date,
2408 quantity,
2409 primary_quantity,
2410 tp_quantity,
2411 last_refresh_number,
2412 posting_party_name,
2413 posting_party_id,
2414 created_by,
2415 creation_date,
2416 last_updated_by,
2417 last_update_date,
2418 project_number,
2419 task_number,
2420 planning_group,
2421 planner_code,
2422 version,
2423 designator
2424 ) values (
2425 msc_sup_dem_entries_s.nextval,
2426 -1,
2427 -1,
2428 t_pub(j),
2429 t_pub_id(j),
2430 t_pub_site(j),
2431 t_pub_site_id(j),
2432 SAFETY_STOCK,
2433 l_order_type_desc,
2434 l_bucket_type_desc,
2435 b_bkt_type(k),
2436 t_item_id(j),
2437 t_master_item_name(j),
2438 t_item_name(j),
2439 nvl(t_master_item_desc(j), t_item_desc(j)),
2440 t_item_desc(j),
2441 t_base_item_id(j),
2442 t_base_item_name(j),
2443 t_uom_code(j),
2444 t_uom_code(j),
2445 null,
2446 b_bkt_end_date(k), --l_prev_work_date,
2447 b_bkt_end_date(k), --l_prev_work_date,
2448 l_qty,
2449 l_qty,
2450 null,
2451 msc_cl_refresh_s.nextval,
2452 t_pub(j),
2453 t_pub_id(j),
2454 nvl(p_user_id,-1),
2455 sysdate,
2456 nvl(p_user_id,-1),
2457 sysdate,
2458 t_proj_number(j),
2459 t_task_number(j),
2460 t_planning_gp(j),
2461 t_planner_code(j),
2462 p_version,
2463 p_designator);
2464
2465 l_record_inserted := l_record_inserted + 1;
2466 END IF; --- end horizon date range
2467 END IF;
2468
2469 END LOOP; -- FOR K LOOP
2470 END IF;
2471 END IF ;
2472
2473 END LOOP;
2474 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Records to be inserted for safety stock : ' || l_record_inserted);
2475 --dbms_output.put_line( 'Records to be inserted for safety stock : ' || l_record_inserted);
2476 END IF; -- for safety stock
2477
2478
2479
2480 --------------------------------------------------------
2481 -- projected avaiable balance
2482 --------------------------------------------------------
2483 --dbms_output.put_line('Start projected available balance');
2484 l_record_inserted := 0;
2485 l_total := 0;
2486 l_order_type_desc := get_order_type (PROJECTED_AVAILABLE_BALANCE);
2487 IF (t_pub_id is not null and t_pub_id.COUNT >0 and p_type = PROJECTED_AVAILABLE_BALANCE) THEN
2488
2489 SELECT plan_start_date
2490 INTO l_plan_start_date
2491 FROM msc_plans
2492 WHERE plan_id = p_plan_id;
2493
2494 FOR j in 1..t_pub_id.COUNT LOOP
2495
2496 /*----------------------------------------------------------------
2497 bug# 3893860 - Taking care of the past due PAB
2498 ----------------------------------------------------------------*/
2499 IF (trunc(t_key_date(j)) < trunc(l_plan_start_date) and (
2500 j = 1 or t_pub_id(j) is not null and t_pub_id(j-1) is not null AND
2501 t_pub_id(j) = t_pub_id(j-1) AND
2502 t_pub_site_id(j) = t_pub_site_id(j-1) AND
2503 t_item_id(j) = t_item_id(j-1))) THEN
2504
2505 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Compute PAB past due with plan start date ' || l_plan_start_date);
2506 l_exp_qty := nvl(t_temp_qty(j),0);
2507 IF (l_exp_qty < 0) THEN
2508 l_exp_qty:= 0;
2509 END IF;
2510 l_total:= t_total_qty(j) - l_exp_qty;
2511 FND_FILE.PUT_LINE(FND_FILE.LOG, 'total ' || l_total || ' item ' || t_item_id(j) || ' Key date ' || t_key_date(j));
2512 l_bucket_type_desc := msc_x_util.get_lookup_meaning('MSC_X_BUCKET_TYPE',1);
2513
2514 IF (trunc(t_key_date(j)) >= trunc(p_horizon_start)) THEN
2515 update msc_sup_dem_entries
2516 set quantity = l_total,
2517 primary_quantity = l_total
2518 where publisher_id = t_pub_id(j)
2519 and publisher_site_id = t_pub_site_id(j)
2520 and inventory_item_id = t_item_id(j)
2521 and publisher_order_type = PROJECTED_AVAILABLE_BALANCE
2522 and trunc(key_date) = trunc(l_plan_start_date) -1 ;
2523
2524
2525 l_rowcount := sql%ROWCOUNT;
2526
2527 l_bucket_type_desc := msc_x_util.get_lookup_meaning('MSC_X_BUCKET_TYPE',t_bucket_type(j));
2528 IF l_rowcount = 0 THEN
2529 FND_FILE.PUT_LINE(FND_FILE.LOG, 'row count = 0' || 'Pub ' || t_pub(j) || 'Pub id ' || t_pub_id(j));
2530 --dbms_output.put_line('row count = 0'|| 'Pub ' || t_pub(j) || 'Pub id ' || t_pub_id(j));
2531 insert into msc_sup_dem_entries (
2532 transaction_id,
2533 plan_id,
2534 sr_instance_id,
2535 publisher_name,
2536 publisher_id,
2537 publisher_site_name,
2538 publisher_site_id,
2539 publisher_order_type,
2540 publisher_order_type_desc,
2541 bucket_type_desc,
2542 bucket_type,
2543 inventory_item_id,
2544 item_name,
2545 owner_item_name,
2546 item_description,
2547 owner_item_description,
2548 base_item_id,
2549 base_item_name,
2550 primary_uom,
2551 uom_code,
2552 tp_uom_code,
2553 key_date,
2554 new_schedule_date,
2555 quantity,
2556 primary_quantity,
2557 tp_quantity,
2558 last_refresh_number,
2559 posting_party_name,
2560 posting_party_id,
2561 created_by,
2562 creation_date,
2563 last_updated_by,
2564 last_update_date,
2565 project_number,
2566 task_number,
2567 planning_group,
2568 planner_code,
2569 version,
2570 designator
2571 ) values (
2572 msc_sup_dem_entries_s.nextval,
2573 -1,
2574 -1,
2575 t_pub(j),
2576 t_pub_id(j),
2577 t_pub_site(j),
2578 t_pub_site_id(j),
2579 PROJECTED_AVAILABLE_BALANCE,
2580 l_order_type_desc,
2581 l_bucket_type_desc,
2582 t_bucket_type(j),
2583 t_item_id(j),
2584 t_master_item_name(j),
2585 t_item_name(j),
2586 nvl(t_master_item_desc(j), t_item_desc(j)),
2587 t_item_desc(j),
2588 t_base_item_id(j),
2589 t_base_item_name(j),
2590 t_uom_code(j),
2591 t_uom_code(j),
2592 null,
2593 l_plan_start_date - 1, --t_key_date(j),
2594 l_plan_start_date - 1, --t_key_date(j),
2595 l_total,
2596 l_total,
2597 null,
2598 msc_cl_refresh_s.nextval,
2599 t_pub(j),
2600 t_pub_id(j),
2601 nvl(p_user_id,-1),
2602 sysdate,
2603 nvl(p_user_id,-1),
2604 sysdate,
2605 t_proj_number(j),
2606 t_task_number(j),
2607 t_planning_gp(j),
2608 t_planner_code(j),
2609 p_version,
2610 p_designator);
2611 END IF; --rowcount
2612 l_record_inserted := l_record_inserted + 1;
2613 END IF;
2614
2615 ELSIF (j > 1 and t_pub_id(j) is not null and t_pub_id(j-1) is not null AND
2616 t_pub_id(j) = t_pub_id(j-1) AND
2617 t_pub_site_id(j) = t_pub_site_id(j-1) AND
2618 t_item_id(j) = t_item_id(j-1) AND
2619 t_key_date(j) <> t_key_date(j-1) AND
2620 t_bucket_index(j) - nvl(t_bucket_index(j-1), t_bucket_index(j) -1) > 1 ) THEN
2621
2622 --FND_FILE.PUT_LINE(FND_FILE.LOG, '2');
2623 l_exp_qty := nvl(t_temp_qty(j),0);
2624 IF (l_exp_qty < 0) THEN
2625 l_exp_qty:= 0;
2626 END IF;
2627
2628 /*--dbms_output.put_line('HELLO' || 'PUB 1 ' || t_pub_id(j) || ' PUB2 ' || t_pub_id(j-1) ||
2629 ' PUB SITE1 ' || t_pub_site_id(j) || ' PUB SITE2 ' || t_pub_site_id(j-1) ||
2630 ' ITM 1 ' || t_item_id(j) || ' ITM 2' || t_item_id(j-1) ||
2631 ' KEY 1 ' || t_key_date(j) || ' KEY2 ' || t_key_date(j-1) ||
2632 ' INDEX 1 ' || t_bucket_index(j) || ' INDEX2 ' || t_bucket_index(j-1));
2633 */
2634 ----------------------------------------------------------------------
2635 --if the current record has the same plan_id, org_id, sr_instance_id, item_id
2636 -- as the previous record different key date
2637 --and current bucket index prev record
2638 --bucket index <> 1, need to fill up the gap (before the pab
2639 --is changed)
2640 -----------------------------------------------------------------------
2641 ----FND_FILE.PUT_LINE(FND_FILE.LOG, 'bucket j-1 ' || t_bucket_start(j-1));
2642 ----FND_FILE.PUT_LINE(FND_FILE.LOG, 'bucket j ' || t_bucket_start(j));
2643
2644 Open get_bucket_date (p_plan_id,
2645 t_sr_instance_id(j),
2646 t_org_id(j),
2647 t_bucket_start(j-1),
2648 t_bucket_start(j));
2649 FETCH get_bucket_date BULK COLLECT INTO
2650 b_bkt_index,
2651 b_bkt_start_date,
2652 b_bkt_end_date,
2653 b_bkt_type;
2654 CLOSE get_bucket_date;
2655
2656 --l_total:= l_total + t_total_qty(j-1) - l_exp_qty;
2657
2658 IF b_bkt_index IS NOT NULL AND b_bkt_index.COUNT > 0 THEN
2659 FOR k in 1..b_bkt_index.COUNT LOOP
2660
2661 IF (k > 1) THEN
2662
2663 IF (k = b_bkt_index.COUNT) THEN
2664 ----FND_FILE.PUT_LINE(FND_FILE.LOG, 'k = b_bkt_index.COUNT ' || b_bkt_index.COUNT);
2665 b_bkt_end_date(k) := t_key_date(j);
2666 l_total := t_total_qty(j) - l_exp_qty;
2667 --FND_FILE.PUT_LINE(FND_FILE.LOG, 'end_date ' || b_bkt_end_date(k));
2668
2669 ELSE
2670 l_exp_qty := nvl(t_temp_qty(j-1),0);
2671 IF (l_exp_qty < 0) THEN
2672 l_exp_qty:= 0;
2673 END IF;
2674 l_total:= t_total_qty(j-1) - l_exp_qty;
2675 END IF;
2676
2677 l_order_type_desc := msc_x_util.get_lookup_meaning ('MSC_X_ORDER_TYPE', PROJECTED_AVAILABLE_BALANCE);
2678 l_bucket_type_desc := msc_x_util.get_lookup_meaning('MSC_X_BUCKET_TYPE',b_bkt_type(k));
2679 --dbms_output.Put_line('fill gap ' || t_qty(j));
2680 --FND_FILE.PUT_LINE(FND_FILE.LOG, 'pab: ' || t_qty(j) || 'date ' || t_key_date(j));
2681
2682 /*
2683 l_prev_work_date := msc_calendar.prev_work_day(t_org_id(j),
2684 t_sr_instance_id(j),
2685 1,
2686 b_bkt_end_date(k));
2687 */
2688
2689 --dbms_output.put_line(' End ' || b_bkt_end_date(k) || ' prev ' || l_prev_work_date);
2690
2691 IF (b_bkt_end_date(k) >= p_horizon_start ) THEN
2692
2693 insert into msc_sup_dem_entries (
2694 transaction_id,
2695 plan_id,
2696 sr_instance_id,
2697 publisher_name,
2698 publisher_id,
2699 publisher_site_name,
2700 publisher_site_id,
2701 publisher_order_type,
2702 publisher_order_type_desc,
2703 bucket_type_desc,
2704 bucket_type,
2705 inventory_item_id,
2706 item_name,
2707 owner_item_name,
2708 item_description,
2709 owner_item_description,
2710 base_item_id,
2711 base_item_name,
2712 primary_uom,
2713 uom_code,
2714 tp_uom_code,
2715 key_date,
2716 new_schedule_date,
2717 quantity,
2718 primary_quantity,
2719 tp_quantity,
2720 last_refresh_number,
2721 posting_party_name,
2722 posting_party_id,
2723 created_by,
2724 creation_date,
2725 last_updated_by,
2726 last_update_date,
2727 project_number,
2728 task_number,
2729 planning_group,
2730 planner_code,
2731 version,
2732 designator
2733 ) values (
2734 msc_sup_dem_entries_s.nextval,
2735 -1,
2736 -1,
2737 t_pub(j),
2738 t_pub_id(j),
2739 t_pub_site(j),
2740 t_pub_site_id(j),
2741 PROJECTED_AVAILABLE_BALANCE,
2742 l_order_type_desc,
2743 l_bucket_type_desc,
2744 b_bkt_type(k),
2745 t_item_id(j),
2746 t_master_item_name(j),
2747 t_item_name(j),
2748 nvl(t_master_item_desc(j), t_item_desc(j)),
2749 t_item_desc(j),
2750 t_base_item_id(j),
2751 t_base_item_name(j),
2752 t_uom_code(j),
2753 t_uom_code(j),
2754 null,
2755 b_bkt_end_date(k), --l_prev_work_date,
2756 b_bkt_end_date(k), --l_prev_work_date,
2757 l_total,
2758 l_total,
2759 null,
2760 msc_cl_refresh_s.nextval,
2761 t_pub(j),
2762 t_pub_id(j),
2763 nvl(p_user_id,-1),
2764 sysdate,
2765 nvl(p_user_id,-1),
2766 sysdate,
2767 t_proj_number(j),
2768 t_task_number(j),
2769 t_planning_gp(j),
2770 t_planner_code(j),
2771 p_version,
2772 p_designator);
2773
2774 l_record_inserted := l_record_inserted + 1;
2775 END IF;
2776 END IF;
2777
2778 END LOOP; -- FOR K LOOP
2779
2780 END IF;
2781
2782 ELSIF (t_pub_id(j) is not null and
2783 ( j = 1 or t_pub_id(j-1) is not null and (
2784 t_sr_instance_id(j) <> t_sr_instance_id(j-1) or
2785 t_pub_id(j) <> t_pub_id(j-1) or
2786 t_pub_site_id(j) <> t_pub_site_id(j-1) or
2787 t_item_id(j) <> t_item_id(j-1) or
2788 t_key_date(j) <> t_key_date(j-1) OR
2789 t_bucket_index(j) - nvl(t_bucket_index(j-1), t_bucket_index(j) -1) = 1))) THEN
2790
2791 --FND_FILE.PUT_LINE(FND_FILE.LOG, '3');
2792 l_exp_qty := nvl(t_temp_qty(j),0);
2793 IF (l_exp_qty < 0) THEN
2794 l_exp_qty:= 0;
2795 END IF;
2796
2797 l_total := t_total_qty(j);
2798
2799 l_bucket_type_desc := msc_x_util.get_lookup_meaning('MSC_X_BUCKET_TYPE',t_bucket_type(j));
2800
2801 IF ( t_key_date(j) >= p_horizon_start) THEN
2802 insert into msc_sup_dem_entries (
2803 transaction_id,
2804 plan_id,
2805 sr_instance_id,
2806 publisher_name,
2807 publisher_id,
2808 publisher_site_name,
2809 publisher_site_id,
2810 publisher_order_type,
2811 publisher_order_type_desc,
2812 bucket_type_desc,
2813 bucket_type,
2814 inventory_item_id,
2815 item_name,
2816 owner_item_name,
2817 item_description,
2818 owner_item_description,
2819 base_item_id,
2820 base_item_name,
2821 primary_uom,
2822 uom_code,
2823 tp_uom_code,
2824 key_date,
2825 new_schedule_date,
2826 quantity,
2827 primary_quantity,
2828 tp_quantity,
2829 last_refresh_number,
2830 posting_party_name,
2831 posting_party_id,
2832 created_by,
2833 creation_date,
2834 last_updated_by,
2835 last_update_date,
2836 project_number,
2837 task_number,
2838 planning_group,
2839 planner_code,
2840 version,
2841 designator
2842 ) values (
2843 msc_sup_dem_entries_s.nextval,
2844 -1,
2845 -1,
2846 t_pub(j),
2847 t_pub_id(j),
2848 t_pub_site(j),
2849 t_pub_site_id(j),
2850 PROJECTED_AVAILABLE_BALANCE,
2851 l_order_type_desc,
2852 l_bucket_type_desc,
2853 t_bucket_type(j),
2854 t_item_id(j),
2855 t_master_item_name(j),
2856 t_item_name(j),
2857 nvl(t_master_item_desc(j), t_item_desc(j)),
2858 t_item_desc(j),
2859 t_base_item_id(j),
2860 t_base_item_name(j),
2861 t_uom_code(j),
2862 t_uom_code(j),
2863 null,
2864 t_key_date(j),
2865 t_key_date(j),
2866 l_total - l_exp_qty,
2867 l_total - l_exp_qty,
2868 null,
2869 msc_cl_refresh_s.nextval,
2870 t_pub(j),
2871 t_pub_id(j),
2872 nvl(p_user_id,-1),
2873 sysdate,
2874 nvl(p_user_id,-1),
2875 sysdate,
2876 t_proj_number(j),
2877 t_task_number(j),
2878 t_planning_gp(j),
2879 t_planner_code(j),
2880 p_version,
2881 p_designator);
2882
2883 l_record_inserted := l_record_inserted + 1;
2884 END IF;
2885
2886 ELSIF (t_pub_id(j) is not null) THEN
2887
2888
2889 --FND_FILE.PUT_LINE(FND_FILE.LOG, 'Here 4 : j: ' || j ||' t_pub_id.COUNT '||t_pub_id.COUNT );
2890
2891 --dbms_output.put_line('update date ' || t_key_date(j) || ' qty ' || l_total || ' t tol ' || t_total_qty(j) || ' ex ' || l_exp_qty);
2892
2893 l_rowcount := 0;
2894 l_exp_qty := nvl(t_temp_qty(j),0);
2895 IF (l_exp_qty < 0) THEN
2896 l_exp_qty:= 0;
2897 END IF;
2898
2899 l_total := t_total_qty(j) - l_exp_qty;
2900
2901
2902 --dbms_output.put_line('update date ' || t_key_date(j) || ' qty ' || l_total || ' t tol ' || t_total_qty(j) || ' ex ' || l_exp_qty);
2903 --dbms_output.put_line('update');
2904 IF (t_key_date(j) >= p_horizon_start) THEN
2905 update msc_sup_dem_entries
2906 set quantity = l_total,
2907 primary_quantity = l_total
2908 where publisher_id = t_pub_id(j)
2909 and publisher_site_id = t_pub_site_id(j)
2910 and inventory_item_id = t_item_id(j)
2911 and publisher_order_type = PROJECTED_AVAILABLE_BALANCE
2912 and trunc(key_date) = trunc(t_key_date(j));
2913
2914
2915 l_rowcount := sql%ROWCOUNT;
2916
2917 l_bucket_type_desc := msc_x_util.get_lookup_meaning('MSC_X_BUCKET_TYPE',t_bucket_type(j));
2918 IF l_rowcount = 0 THEN
2919 FND_FILE.PUT_LINE(FND_FILE.LOG, 'row count = 0' || 'Pub ' || t_pub(j) || 'Pub id ' || t_pub_id(j));
2920 --dbms_output.put_line('row count = 0'|| 'Pub ' || t_pub(j) || 'Pub id ' || t_pub_id(j));
2921 insert into msc_sup_dem_entries (
2922 transaction_id,
2923 plan_id,
2924 sr_instance_id,
2925 publisher_name,
2926 publisher_id,
2927 publisher_site_name,
2928 publisher_site_id,
2929 publisher_order_type,
2930 publisher_order_type_desc,
2931 bucket_type_desc,
2932 bucket_type,
2933 inventory_item_id,
2934 item_name,
2935 owner_item_name,
2936 item_description,
2937 owner_item_description,
2938 base_item_id,
2939 base_item_name,
2940 primary_uom,
2941 uom_code,
2942 tp_uom_code,
2943 key_date,
2944 new_schedule_date,
2945 quantity,
2946 primary_quantity,
2947 tp_quantity,
2948 last_refresh_number,
2949 posting_party_name,
2950 posting_party_id,
2951 created_by,
2952 creation_date,
2953 last_updated_by,
2954 last_update_date,
2955 project_number,
2956 task_number,
2957 planning_group,
2958 planner_code,
2959 version,
2960 designator
2961 ) values (
2962 msc_sup_dem_entries_s.nextval,
2963 -1,
2964 -1,
2965 t_pub(j),
2966 t_pub_id(j),
2967 t_pub_site(j),
2968 t_pub_site_id(j),
2969 PROJECTED_AVAILABLE_BALANCE,
2970 l_order_type_desc,
2971 l_bucket_type_desc,
2972 t_bucket_type(j),
2973 t_item_id(j),
2974 t_master_item_name(j),
2975 t_item_name(j),
2976 nvl(t_master_item_desc(j), t_item_desc(j)),
2977 t_item_desc(j),
2978 t_base_item_id(j),
2979 t_base_item_name(j),
2980 t_uom_code(j),
2981 t_uom_code(j),
2982 null,
2983 t_key_date(j),
2984 t_key_date(j),
2985 l_total,
2986 l_total,
2987 null,
2988 msc_cl_refresh_s.nextval,
2989 t_pub(j),
2990 t_pub_id(j),
2991 nvl(p_user_id,-1),
2992 sysdate,
2993 nvl(p_user_id,-1),
2994 sysdate,
2995 t_proj_number(j),
2996 t_task_number(j),
2997 t_planning_gp(j),
2998 t_planner_code(j),
2999 p_version,
3000 p_designator);
3001 END IF; --rowcount
3002 l_record_inserted := l_record_inserted + 1;
3003 END IF; -- key_date >= p_horizon_start
3004
3005 END IF ; -----to end block
3006 /*-------------------------------------------------------------------------
3007 -- Bug# 3913477 : for levelling PAB after last PAB data available
3008 in msc_safety_stocks table till plan_end_date or horizon_end_date
3009 whichever ends first. The loop inserts data for MULTIORG , MULTI_ITEM case
3010 whenever pub_id or pub_site_id( ORG_ID) or item_id changes.
3011 -------------------------------------------------------------------------*/
3012
3013 IF (j > 1 AND
3014 (t_pub_id(j) <> t_pub_id(j-1) OR
3015 t_pub_site_id(j) <> t_pub_site_id(j-1) OR
3016 t_item_id(j) <> t_item_id(j-1) )) THEN
3017
3018 Open get_bucket_date (p_plan_id,
3019 t_sr_instance_id(j-1),
3020 t_org_id(j-1),
3021 t_bucket_start(j-1),
3022 p_horizon_end);
3023 FETCH get_bucket_date BULK COLLECT INTO
3024 b_bkt_index,
3025 b_bkt_start_date,
3026 b_bkt_end_date,
3027 b_bkt_type;
3028 CLOSE get_bucket_date;
3029
3030 IF b_bkt_index IS NOT NULL AND b_bkt_index.COUNT > 0 THEN
3031
3032 FOR k in 1..b_bkt_index.COUNT LOOP
3033
3034
3035 IF (k > 1) THEN
3036 l_exp_qty := nvl(t_temp_qty(j-1),0);
3037 IF (l_exp_qty < 0) THEN
3038 l_exp_qty:= 0;
3039 END IF;
3040 l_total := t_total_qty(j-1) - l_exp_qty;
3041
3042 l_order_type_desc := msc_x_util.get_lookup_meaning ('MSC_X_ORDER_TYPE', PROJECTED_AVAILABLE_BALANCE);
3043 l_bucket_type_desc := msc_x_util.get_lookup_meaning('MSC_X_BUCKET_TYPE',b_bkt_type(k));
3044
3045 IF (b_bkt_end_date(k) >= p_horizon_start and b_bkt_end_date(k) <= p_horizon_end ) THEN
3046
3047 insert into msc_sup_dem_entries (
3048 transaction_id,
3049 plan_id,
3050 sr_instance_id,
3051 publisher_name,
3052 publisher_id,
3053 publisher_site_name,
3054 publisher_site_id,
3055 publisher_order_type,
3056 publisher_order_type_desc,
3057 bucket_type_desc,
3058 bucket_type,
3059 inventory_item_id,
3060 item_name,
3061 owner_item_name,
3062 item_description,
3063 owner_item_description,
3064 base_item_id,
3065 base_item_name,
3066 primary_uom,
3067 uom_code,
3068 tp_uom_code,
3069 key_date,
3070 new_schedule_date,
3071 quantity,
3072 primary_quantity,
3073 tp_quantity,
3074 last_refresh_number,
3075 posting_party_name,
3076 posting_party_id,
3077 created_by,
3078 creation_date,
3079 last_updated_by,
3080 last_update_date,
3081 project_number,
3082 task_number,
3083 planning_group,
3084 planner_code,
3085 version,
3086 designator
3087 ) values (
3088 msc_sup_dem_entries_s.nextval,
3089 -1,
3090 -1,
3091 t_pub(j-1),
3092 t_pub_id(j-1),
3093 t_pub_site(j-1),
3094 t_pub_site_id(j-1),
3095 PROJECTED_AVAILABLE_BALANCE,
3096 l_order_type_desc,
3097 l_bucket_type_desc,
3098 b_bkt_type(k),
3099 t_item_id(j-1),
3100 t_master_item_name(j-1),
3101 t_item_name(j-1),
3102 nvl(t_master_item_desc(j-1), t_item_desc(j-1)),
3103 t_item_desc(j-1),
3104 t_base_item_id(j-1),
3105 t_base_item_name(j-1),
3106 t_uom_code(j-1),
3107 t_uom_code(j-1),
3108 null,
3109 b_bkt_end_date(k), --l_prev_work_date,
3110 b_bkt_end_date(k), --l_prev_work_date,
3111 l_total,
3112 l_total,
3113 null,
3114 msc_cl_refresh_s.nextval,
3115 t_pub(j-1),
3116 t_pub_id(j-1),
3117 nvl(p_user_id,-1),
3118 sysdate,
3119 nvl(p_user_id,-1),
3120 sysdate,
3121 t_proj_number(j-1),
3122 t_task_number(j-1),
3123 t_planning_gp(j-1),
3124 t_planner_code(j-1),
3125 p_version,
3126 p_designator);
3127
3128 l_record_inserted := l_record_inserted + 1;
3129 END IF; --- end horizon date range
3130
3131 END IF;
3132
3133 END LOOP; -- FOR K LOOP
3134 END IF;
3135
3136 END IF ;
3137
3138 /*--------------------------------------------------------------------
3139 for inserting trailors for last data i.e. for SINGLE ORG case or
3140 in case of multiorg for the last pub_site_id(ORG_ID) or last item_id
3141 data available in tables.
3142 -----------------------------------------------------------------------*/
3143
3144
3145 IF (j = t_pub_id.COUNT ) THEN
3146
3147 Open get_bucket_date (p_plan_id,
3148 t_sr_instance_id(j),
3149 t_org_id(j),
3150 t_bucket_start(j),
3151 p_horizon_end);
3152 FETCH get_bucket_date BULK COLLECT INTO
3153 b_bkt_index,
3154 b_bkt_start_date,
3155 b_bkt_end_date,
3156 b_bkt_type;
3157 CLOSE get_bucket_date;
3158
3159 IF b_bkt_index IS NOT NULL AND b_bkt_index.COUNT > 0 THEN
3160
3161 FOR k in 1..b_bkt_index.COUNT LOOP
3162
3163 IF (k > 1) THEN
3164 l_exp_qty := nvl(t_temp_qty(j),0);
3165 IF (l_exp_qty < 0) THEN
3166 l_exp_qty:= 0;
3167 END IF;
3168 l_total:= t_total_qty(j) - l_exp_qty;
3169
3170 l_order_type_desc := msc_x_util.get_lookup_meaning ('MSC_X_ORDER_TYPE', PROJECTED_AVAILABLE_BALANCE);
3171 l_bucket_type_desc := msc_x_util.get_lookup_meaning('MSC_X_BUCKET_TYPE',b_bkt_type(k));
3172
3173 IF (b_bkt_end_date(k) >= p_horizon_start and b_bkt_end_date(k) <= p_horizon_end ) THEN
3174
3175 insert into msc_sup_dem_entries (
3176 transaction_id,
3177 plan_id,
3178 sr_instance_id,
3179 publisher_name,
3180 publisher_id,
3181 publisher_site_name,
3182 publisher_site_id,
3183 publisher_order_type,
3184 publisher_order_type_desc,
3185 bucket_type_desc,
3186 bucket_type,
3187 inventory_item_id,
3188 item_name,
3189 owner_item_name,
3190 item_description,
3191 owner_item_description,
3192 base_item_id,
3193 base_item_name,
3194 primary_uom,
3195 uom_code,
3196 tp_uom_code,
3197 key_date,
3198 new_schedule_date,
3199 quantity,
3200 primary_quantity,
3201 tp_quantity,
3202 last_refresh_number,
3203 posting_party_name,
3204 posting_party_id,
3205 created_by,
3206 creation_date,
3207 last_updated_by,
3208 last_update_date,
3209 project_number,
3210 task_number,
3211 planning_group,
3212 planner_code,
3213 version,
3214 designator
3215 ) values (
3216 msc_sup_dem_entries_s.nextval,
3217 -1,
3218 -1,
3219 t_pub(j),
3220 t_pub_id(j),
3221 t_pub_site(j),
3222 t_pub_site_id(j),
3223 PROJECTED_AVAILABLE_BALANCE,
3224 l_order_type_desc,
3225 l_bucket_type_desc,
3226 b_bkt_type(k),
3227 t_item_id(j),
3228 t_master_item_name(j),
3229 t_item_name(j),
3230 nvl(t_master_item_desc(j), t_item_desc(j)),
3231 t_item_desc(j),
3232 t_base_item_id(j),
3233 t_base_item_name(j),
3234 t_uom_code(j),
3235 t_uom_code(j),
3236 null,
3237 b_bkt_end_date(k), --l_prev_work_date,
3238 b_bkt_end_date(k), --l_prev_work_date,
3239 l_total,
3240 l_total,
3241 null,
3242 msc_cl_refresh_s.nextval,
3243 t_pub(j),
3244 t_pub_id(j),
3245 nvl(p_user_id,-1),
3246 sysdate,
3247 nvl(p_user_id,-1),
3248 sysdate,
3249 t_proj_number(j),
3250 t_task_number(j),
3251 t_planning_gp(j),
3252 t_planner_code(j),
3253 p_version,
3254 p_designator);
3255
3256 l_record_inserted := l_record_inserted + 1;
3257
3258 END IF;
3259
3260 END IF;
3261
3262 END LOOP; -- FOR K LOOP
3263 END IF;
3264 END IF ;
3265
3266 END LOOP; ---for outermost j block
3267
3268 --dbms_output.put_line('Records to be inserted/updated for pab based on horizon date: ' || l_record_inserted);
3269 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Records to be inserted/updated for pab based on horizon date: ' || l_record_inserted);
3270
3271 END IF; --------to end PAB calculations
3272 p_ret := 0;
3273 p_err := null ;
3274 EXCEPTION
3275 when others then
3276 --dbms_output.put_line('Error in insert_sup_dem_entries: ' || sqlerrm);
3277 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error in insert_sup_dem_entries. PSS program will not publish data. Error: ' || sqlerrm);
3278 p_ret := 1;
3279 p_err := sqlerrm ;
3280 END insert_into_sup_dem;
3281
3282
3283 PROCEDURE delete_old_safety_stock(
3284 p_plan_id in number,
3285 p_org_id in number,
3286 p_sr_instance_id in number,
3287 p_planner_code in varchar2,
3288 p_abc_class in varchar2,
3289 p_item_id in number,
3290 p_planning_gp in varchar2,
3291 p_project_id in number,
3292 p_task_id in number,
3293 p_horizon_start in date,
3294 p_horizon_end in date,
3295 p_overwrite in number
3296 ) IS
3297
3298 l_row number;
3299
3300 BEGIN
3301 --dbms_output.put_line('In delete_old_safety stock');
3302
3303
3304 IF ( p_overwrite = 1) THEN --delete all
3305 delete from msc_sup_dem_entries sd
3306 where sd.publisher_order_type in (SAFETY_STOCK, PROJECTED_AVAILABLE_BALANCE) and
3307 sd.plan_id = -1 and
3308 sd.publisher_id = 1 and
3309 exists (select cs.company_site_id
3310 from msc_plan_organizations o,
3311 msc_company_sites cs,
3312 msc_trading_partner_maps m,
3313 msc_trading_partners p
3314 where o.plan_id = p_plan_id and
3315 p.sr_tp_id = nvl(p_org_id, o.organization_id) and
3316 p.sr_instance_id = nvl(p_sr_instance_id,
3317 o.sr_instance_id) and
3318 p.partner_type = 3 and
3319 m.tp_key = p.partner_id and
3320 m.map_type = 2 and
3321 cs.company_site_id = m.company_key and
3322 cs.company_id = 1
3323 and cs.company_site_id=sd.publisher_site_id and rownum=1) and
3324 sd.inventory_item_id = nvl(p_item_id, sd.inventory_item_id);
3325 --and
3326 --NVL(sd.planner_code,'-99') = nvl(p_planner_code, NVL(sd.planner_code, '-99')) and
3327 --NVL(sd.planning_group,'-99') = nvl(p_planning_gp, NVL(sd.planning_group, '-99')) and
3328 --NVL(sd.project_number,'-99') = nvl(p_project_id, NVL(sd.project_number, '-99')) and
3329 --NVL(sd.task_number, '-99') = nvl(p_task_id, NVL(sd.task_number, '-99'));
3330
3331 l_row := SQL%ROWCOUNT;
3332 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Deleted number records: ' || l_row);
3333 --dbms_output.put_line('Deleted number records: ' || l_row);
3334 ELSIF ( p_overwrite = 2) THEN --delete by overwritten
3335 delete from msc_sup_dem_entries sd
3336 where sd.publisher_order_type in (SAFETY_STOCK, PROJECTED_AVAILABLE_BALANCE) and
3337 sd.plan_id = -1 and
3338 sd.publisher_id = 1 and
3339 exists (select cs.company_site_id
3340 from msc_plan_organizations o,
3341 msc_company_sites cs,
3342 msc_trading_partner_maps m,
3343 msc_trading_partners p
3344 where o.plan_id = p_plan_id and
3345 p.sr_tp_id = nvl(p_org_id, o.organization_id) and
3346 p.sr_instance_id = nvl(p_sr_instance_id,
3347 o.sr_instance_id) and
3348 p.partner_type = 3 and
3349 m.tp_key = p.partner_id and
3350 m.map_type = 2 and
3351 cs.company_site_id = m.company_key and
3352 cs.company_id = 1
3353 and sd.publisher_site_id =cs.company_site_id and rownum=1) and
3354 sd.inventory_item_id= nvl(p_item_id, sd.inventory_item_id) and
3355 --NVL(sd.planner_code,'-99') = nvl(p_planner_code, NVL(sd.planner_code, '-99')) and
3356 --NVL(sd.planning_group,'-99') = nvl(p_planning_gp, NVL(sd.planning_group, '-99')) and
3357 --NVL(sd.project_number,'-99') = nvl(p_project_id, NVL(sd.project_number, '-99')) and
3358 --NVL(sd.task_number, '-99') = nvl(p_task_id, NVL(sd.task_number, '-99')) and
3359 key_date between nvl(p_horizon_start, sysdate - 36500) and
3360 nvl(p_horizon_end, sysdate + 36500);
3361 l_row := SQL%ROWCOUNT;
3362 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Deleted number records: ' || l_row);
3363 --dbms_output.put_line('Deleted number records: ' || l_row);
3364 END IF;
3365 commit;
3366
3367 END delete_old_safety_stock;
3368
3369 PROCEDURE LOG_MESSAGE(
3370 p_string IN VARCHAR2
3371 ) IS
3372 BEGIN
3373 IF fnd_global.conc_request_id > 0 THEN
3374 FND_FILE.PUT_LINE(FND_FILE.LOG, p_string);
3375 NULL;
3376 ELSE
3377 --dbms_OUTPUT.PUT_LINE( p_string);
3378 null;
3379 END IF;
3380 END LOG_MESSAGE;
3381
3382 FUNCTION get_message (
3383 p_app IN VARCHAR2,
3384 p_name IN VARCHAR2,
3385 p_lang IN VARCHAR2
3386 ) RETURN VARCHAR2 IS
3387 msg VARCHAR2(2000);
3388 CURSOR c1(app_name VARCHAR2, msg_name VARCHAR2, lang VARCHAR2) IS
3389 SELECT m.message_text
3390 FROM fnd_new_messages m,
3391 fnd_application a
3392 WHERE m.message_name = msg_name AND
3393 m.language_code = lang AND
3394 a.application_short_name = app_name AND
3395 m.application_id = a.application_id;
3396 BEGIN
3397
3398 msg := null;
3399
3400 OPEN c1(p_app, p_name, p_lang);
3401 FETCH c1 INTO msg;
3402 IF (c1%NOTFOUND) then
3403 msg := p_name;
3404 END IF;
3405 CLOSE c1;
3406 RETURN msg;
3407 END get_message;
3408
3409
3410
3411 --------------------------------------------------------------------------
3412 -- Function GET_ORDER_TYPE
3413 ----------------------------------------------------------------------
3414 FUNCTION GET_ORDER_TYPE(p_order_type_code in Number) RETURN Varchar2 IS
3415 l_order_type_desc Varchar2(240);
3416 BEGIN
3417 --Get the order type desc. Takes care of order type renaming.
3418 BEGIN
3419 select meaning
3420 into l_order_type_desc
3421 from mfg_lookups
3422 where lookup_type = 'MSC_X_ORDER_TYPE'
3423 and lookup_code = p_order_type_code;
3424
3425 return l_order_type_desc;
3426 EXCEPTION
3427 WHEN OTHERS THEN
3428 l_order_type_desc := null;
3429 return l_order_type_desc;
3430 END;
3431
3432 END get_order_type;
3433
3434
3435
3436 END msc_publish_safety_stock_pkg;