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