1 PACKAGE BODY MSC_ATP_FUNC AS
2 /* $Header: MSCFATPB.pls 120.5 2011/12/06 09:51:01 vjuluri ship $ */
3 G_PKG_NAME CONSTANT VARCHAR2(30) := 'MSC_ATP_FUNC';
4
5
6 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('MSC_ATP_DEBUG'), 'N');
7
8 FUNCTION get_atp_flag (p_instance_id IN NUMBER,
9 p_plan_id IN NUMBER,
10 p_inventory_item_id IN NUMBER,
11 p_organization_id IN NUMBER)
12 RETURN VARCHAR2
13 IS
14 l_atp_flag VARCHAR2(1);
15 l_bom_item_type NUMBER;
16 l_atp_check NUMBER;
17
18 BEGIN
19
20 IF PG_DEBUG in ('Y', 'C') THEN
21 msc_sch_wb.atp_debug('inside get_atp_flag');
22 msc_sch_wb.atp_debug('get_atp_flag: ' || 'sr_inventory_item_id = '||p_inventory_item_id);
23 msc_sch_wb.atp_debug('get_atp_flag: ' || 'organization_id = '||p_organization_id);
24 msc_sch_wb.atp_debug('get_atp_flag: ' || 'plan_id = '||p_plan_id);
25 msc_sch_wb.atp_debug('get_atp_flag: ' || 'sr_instance_id = '||p_instance_id);
26 END IF;
27
28 -- Changed on 1/23/2001 by ngoel. In case of multi-level/multi-org CTO
29 -- we need to set this flag to No in case the atp check flag is set to
30 -- No for the bom.
31 /*
32 SELECT atp_flag
33 INTO l_atp_flag
34 FROM msc_system_items
35 WHERE sr_inventory_item_id = p_inventory_item_id
36 AND organization_id = p_organization_id
37 AND plan_id = p_plan_id
38 AND sr_instance_id = p_instance_id;
39 */
40
41 SELECT i.atp_flag, i.bom_item_type, b.atp_check
42 INTO l_atp_flag, l_bom_item_type, l_atp_check
43 FROM msc_system_items i, msc_bom_temp b
44 WHERE i.sr_inventory_item_id = p_inventory_item_id
45 AND i.organization_id = p_organization_id
46 AND i.plan_id = p_plan_id
47 AND i.sr_instance_id = p_instance_id
48 AND b.component_item_id (+) = i.sr_inventory_item_id
49 AND b.component_identifier (+) = MSC_ATP_PVT.G_COMP_LINE_ID
50 AND b.session_id (+) = MSC_ATP_PVT.G_SESSION_ID;
51
52 IF l_bom_item_type = 4 AND NVL(l_atp_check, -1) = 2 THEN
53 l_atp_flag := 'N';
54 END IF;
55
56 MSC_ATP_PVT.G_SR_INVENTORY_ITEM_ID := NULL;
57 return l_atp_flag;
58 EXCEPTION
59 WHEN NO_DATA_FOUND THEN
60 MSC_ATP_PVT.G_SR_INVENTORY_ITEM_ID := p_inventory_item_id;
61 return 'N';
62 END get_atp_flag;
63
64
65 FUNCTION get_atp_comp_flag (p_instance_id IN NUMBER,
66 p_plan_id IN NUMBER,
67 p_inventory_item_id IN NUMBER,
68 p_organization_id IN NUMBER)
69 RETURN VARCHAR2
70 IS
71 l_atp_comp_flag VARCHAR2(1);
72 l_bom_item_type NUMBER;
73 l_pick_comp_flag VARCHAR2(1);
74 l_replenish_flag VARCHAR2(1);
75 l_cto_bom NUMBER := 0;
76 l_atp_check NUMBER;
77 BEGIN
78
79 IF PG_DEBUG in ('Y', 'C') THEN
80 msc_sch_wb.atp_debug('inside get_atp_comp_flag');
81 msc_sch_wb.atp_debug('get_atp_comp_flag: ' || 'sr_inventory_item_id = '||p_inventory_item_id);
82 msc_sch_wb.atp_debug('get_atp_comp_flag: ' || 'organization_id = '||p_organization_id);
83 msc_sch_wb.atp_debug('get_atp_comp_flag: ' || 'plan_id = '||p_plan_id);
84 msc_sch_wb.atp_debug('get_atp_comp_flag: ' || 'sr_instance_id = '||p_instance_id);
85 msc_sch_wb.atp_debug('get_atp_comp_flag: ' || 'G_ORDER_LINE_ID = '||MSC_ATP_PVT.G_ORDER_LINE_ID);
86 msc_sch_wb.atp_debug('get_atp_comp_flag: ' || 'G_ASSEMBLY_LINE_ID = '||MSC_ATP_PVT.G_ASSEMBLY_LINE_ID);
87 msc_sch_wb.atp_debug('get_atp_comp_flag: ' || 'G_COMP_LINE_ID = '||MSC_ATP_PVT.G_COMP_LINE_ID);
88 END IF;
89
90 -- Fix for Bug 1413039 9/22/00 - NGOEL
91 -- Since we don't support multi-level ATP for ODS, set
92 -- return ATP component flag as N in case of ODS.
93
94 IF PG_DEBUG in ('Y', 'C') THEN
95 msc_sch_wb.atp_debug('get_atp_comp_flag: ' || 'G_INV_CTP = '||MSC_ATP_PVT.G_INV_CTP);
96 END IF;
97 IF MSC_ATP_PVT.G_INV_CTP = 5 THEN
98 l_atp_comp_flag := 'N';
99
100 -- IF p_plan_id = -1 THEN
101
102 ELSE
103 SELECT atp_components_flag , bom_item_type,
104 pick_components_flag, replenish_to_order_flag
105 INTO l_atp_comp_flag, l_bom_item_type,
106 l_pick_comp_flag, l_replenish_flag
107 FROM msc_system_items
108 WHERE sr_inventory_item_id = p_inventory_item_id
109 AND organization_id = p_organization_id
110 AND plan_id = -1
111 AND sr_instance_id = p_instance_id;
112
113 IF PG_DEBUG in ('Y', 'C') THEN
114 msc_sch_wb.atp_debug('get_atp_comp_flag: ' || 'l_bom_item_type = '||l_bom_item_type);
115 msc_sch_wb.atp_debug('get_atp_comp_flag: ' || 'l_pick_comp_flag = '||l_pick_comp_flag);
116 msc_sch_wb.atp_debug('get_atp_comp_flag: ' || 'l_replenish_flag = '||l_replenish_flag);
117 END IF;
118
119 IF l_bom_item_type in (1, 2) THEN
120 -- since bom team does the explosion for the model and option class
121 -- already, we don't want to do components one more time. so we need to
122 -- change the components flag based on the followings
123 -- value from database what get return
124 -- Y N
125 -- N N
126 -- C R
127 -- R R
128
129 -- Bug 1562754, use G_ASSEMBLY_LINE_ID instead of G_COMP_LINE_ID, to make sure that
130 -- in case of CTO, we try to get the BOM correctly from msc_bom_temp_table.-NGOEL 02/01/2001
131
132 -- ngoel 9/24/2001, added to identify if current line is a MATO line, no need to run this select again.
133
134 IF (NVL(MSC_ATP_PVT.G_CTO_LINE, 'N') = 'N') THEN
135 SELECT count(assembly_identifier)
136 INTO l_cto_bom
137 FROM msc_bom_temp mbt
138 WHERE mbt.session_id = MSC_ATP_PVT.G_SESSION_ID
139 AND mbt.assembly_identifier = MSC_ATP_PVT.G_COMP_LINE_ID
140 AND mbt.assembly_item_id = p_inventory_item_id;
141
142 IF l_cto_bom > 0 THEN
143 MSC_ATP_PVT.G_CTO_LINE := 'Y';
144 END IF;
145 END IF;
146
147 -- FOR CTO Models, pick_components_flag shall be 'N' and replenish_to_order
148 -- flag shall be 'Y'. Modify flag only for PTO model or an option class.
149 -- When a BOM exists in MSC_BOM_TEMP table, modify flags to always check for
150 -- material.
151
152 IF PG_DEBUG in ('Y', 'C') THEN
153 msc_sch_wb.atp_debug('get_atp_comp_flag: ' || 'l_cto_bom = '||l_cto_bom);
154 msc_sch_wb.atp_debug('get_atp_comp_flag: ' || 'G_CTO_LINE = '||MSC_ATP_PVT.G_CTO_LINE);
155 END IF;
156
157 IF ((NVL(l_pick_comp_flag, 'N') = 'N') AND (NVL(l_replenish_flag, 'N') = 'Y')) AND
158 (NVL(MSC_ATP_PVT.G_CTO_LINE, 'N') = 'Y') AND (l_bom_item_type = 1) THEN
159 IF l_atp_comp_flag = 'N' THEN
160 l_atp_comp_flag := 'Y';
161 ELSIF l_atp_comp_flag = 'R' THEN
162 l_atp_comp_flag := 'C';
163 END IF;
164 ELSE
165 IF l_atp_comp_flag = 'Y' THEN
166 l_atp_comp_flag := 'N';
167 ELSIF l_atp_comp_flag = 'C' THEN
168 l_atp_comp_flag := 'R';
169 END IF;
170 END IF;
171 ELSIF l_bom_item_type = 4 THEN
172 BEGIN
173 SELECT atp_check
174 INTO l_atp_check
175 FROM msc_bom_temp
176 WHERE component_item_id = p_inventory_item_id
177 AND component_identifier = MSC_ATP_PVT.G_COMP_LINE_ID
178 AND session_id = MSC_ATP_PVT.G_SESSION_ID;
179
180 IF PG_DEBUG in ('Y', 'C') THEN
181 msc_sch_wb.atp_debug('get_atp_comp_flag: ' || 'l_atp_check : '||l_atp_check);
182 END IF;
183
184 IF l_bom_item_type = 4 AND NVL(l_atp_check, -1) = 2 THEN
185 l_atp_comp_flag := 'N';
186 END IF;
187 EXCEPTION
188 WHEN NO_DATA_FOUND THEN
189 NULL;
190 END;
191
192 END IF;
193
194 END IF;
195
196 return l_atp_comp_flag;
197 EXCEPTION
198 WHEN NO_DATA_FOUND THEN
199 IF PG_DEBUG in ('Y', 'C') THEN
200 msc_sch_wb.atp_debug('get_atp_comp_flag: ' || 'sqlcode : '||sqlcode);
201 msc_sch_wb.atp_debug('get_atp_comp_flag: ' || 'sqlerr : '||sqlerrm);
202 END IF;
203 return 'N';
204 END get_atp_comp_flag;
205
206
207 FUNCTION get_location_id (p_instance_id IN NUMBER,
208 p_organization_id IN NUMBER,
209 p_customer_id IN NUMBER,
210 p_customer_site_id IN NUMBER,
211 p_supplier_id IN NUMBER,
212 p_supplier_site_id IN NUMBER)
213 RETURN NUMBER
214 IS
215 l_location_id NUMBER;
216
217 BEGIN
218
219 IF (p_organization_id IS NOT NULL) THEN
220
221 SELECT sr_tp_site_id
222 INTO l_location_id
223 FROM msc_trading_partner_sites
224 WHERE sr_tp_id = p_organization_id
225 AND sr_instance_id = p_instance_id
226 AND partner_type = 3;
227
228
229 ELSIF ((p_customer_id IS NOT NULL) AND
230 (p_customer_site_id IS NOT NULL)) THEN
231 -- krajan:
232 -- Location ID from msc_location_associations
233 SELECT loc.location_id
234 INTO l_location_id
235 FROM msc_tp_site_id_lid tpsid,
236 msc_location_associations loc
237 -- Modified for Sony Bug 2793404
238 -- Remove customer_id filter and corresponding join to msc_tp_id Bug 2816887
239 WHERE tpsid.sr_tp_site_id = p_customer_site_id
240 AND tpsid.sr_instance_id = p_instance_id
241 AND tpsid.partner_type = 2
242 AND loc.partner_site_id = tpsid.tp_site_id
243 AND loc.sr_instance_id = tpsid.sr_instance_id
244
245 --bug 6833430 to ensure loc record is that of customer
246 AND NOT EXISTS (select NULL
247 from msc_trading_partners mtp
248 where mtp.sr_instance_id = loc.sr_instance_id AND
249 mtp.partner_id = loc.partner_id AND
250 mtp.partner_type = 3);
251
252 -- AND loc.organization_id is NULL; --bug 6833430
253 -- Add organization_id is null filter.
254 -- End Bug 2793404
255
256 ELSIF ((p_supplier_id IS NOT NULL) AND
257 (p_supplier_site_id IS NOT NULL)) THEN
258
259 -- cchen: 1124206
260 SELECT l.location_id
261 INTO l_location_id
262 FROM msc_location_associations l
263 WHERE l.sr_instance_id = p_instance_id
264 AND l.partner_id = p_supplier_id
265 AND l.partner_site_id = p_supplier_site_id;
266
267 END IF;
268
269 return l_location_id;
270 EXCEPTION WHEN NO_DATA_FOUND THEN
271 return null;
272
273 END get_location_id;
274
275
276 FUNCTION get_infinite_time_fence_date (p_instance_id IN NUMBER,
277 p_inventory_item_id IN NUMBER,
278 p_organization_id IN NUMBER,
279 p_plan_id IN NUMBER)
280 RETURN DATE
281 IS
282
283 l_infinite_time_fence_date DATE;
284 l_item_type NUMBER;
285
286 BEGIN
287 /* for 1478110. please refer to the BUG
288 IF p_plan_id <> -1 THEN
289 IF PG_DEBUG in ('Y', 'C') THEN
290 msc_sch_wb.atp_debug('get_infinite_time_fence_date: ' || 'selecting infinite_time_fence_date, MSC_ATP_PVT.G_INV_CTP = 4');
291 END IF;
292
293 SELECT curr_cutoff_date
294 INTO l_infinite_time_fence_date
295 FROM msc_plans
296 WHERE plan_id = p_plan_id;
297 IF PG_DEBUG in ('Y', 'C') THEN
298 msc_sch_wb.atp_debug('get_infinite_time_fence_date: ' || 'l_infinite_time_fence_date'||l_infinite_time_fence_date);
299 END IF;
300 ELSE
301
302 IF PG_DEBUG in ('Y', 'C') THEN
303 msc_sch_wb.atp_debug('get_infinite_time_fence_date: ' || 'selecting infinite_time_fence_date, MSC_ATP_PVT.G_INV_CTP = 5');
304 END IF;
305 */
306
307 -- Bug 1566260, in case of modle or option class for PDS ATP, return null.
308 -- This way, we always will work with multi-level results rather than single level
309 -- This was done so that pegging tree is always available for Mulit-org CTO and
310 -- demand entries could be stored for planning purposes.
311
312 IF p_plan_id <> -1 THEN
313 IF PG_DEBUG in ('Y', 'C') THEN
314 msc_sch_wb.atp_debug('get_infinite_time_fence_date: ' || 'selecting item type for PDS');
315 END IF;
316 SELECT i.bom_item_type
317 INTO l_item_type
318 FROM msc_system_items i
319 WHERE i.plan_id = p_plan_id
320 AND i.sr_instance_id = p_instance_id
321 AND i.organization_id = p_organization_id
322 AND i.sr_inventory_item_id = p_inventory_item_id;
323 END IF;
324
325 IF nvl(l_item_type, -1) IN (1,2) THEN
326 IF PG_DEBUG in ('Y', 'C') THEN
327 msc_sch_wb.atp_debug('get_infinite_time_fence_date: ' || 'PDS item type is model(1) or option class(2) : '||l_item_type);
328 END IF;
329 l_infinite_time_fence_date := null;
330 ELSE
331
332 -- Bug 2877340, 2746213
333 -- Read the Profile option to pad the user defined days
334 -- to infinite Supply fence.
335 IF PG_DEBUG in ('Y', 'C') THEN
336 msc_sch_wb.atp_debug('get_infinite_time_fence_date: Profile value for Infinite Supply Pad');
337 msc_sch_wb.atp_debug('get_infinite_time_fence_date: MSC_ATP_PVT.G_INF_SUP_TF_PAD ' || MSC_ATP_PVT.G_INF_SUP_TF_PAD);
338 END IF;
339 -- End Bug 2877340, 2746213
340 SELECT c2.calendar_date
341 INTO l_infinite_time_fence_date
342 FROM msc_calendar_dates c2,
343 msc_calendar_dates c1,
344 msc_atp_rules r,
345 msc_trading_partners tp,
346 msc_system_items i
347 WHERE i.sr_inventory_item_id = p_inventory_item_id
348 AND i.organization_id = p_organization_id
349 --AND i.plan_id = p_plan_id
350 AND i.plan_id = -1 -- for 1478110
351 AND i.sr_instance_id = p_instance_id
352 AND tp.sr_tp_id = i.organization_id
353 AND tp.sr_instance_id = i.sr_instance_id
354 AND tp.partner_type = 3
355 AND r.sr_instance_id = tp.sr_instance_id
356 AND r.rule_id = NVL(i.atp_rule_id, NVL(tp.default_atp_rule_id,0))
357 AND c1.sr_instance_id = r.sr_instance_id
358 AND c1.calendar_date = TRUNC(sysdate)
359 AND c1.calendar_code = tp.calendar_code
360 AND c1.exception_set_id = -1
361 AND c2.sr_instance_id = c1.sr_instance_id
362
363 -- Bug 2877340, 2746213
364 -- Add Infinite Supply Time Fence PAD
365 --bug3609031 adding ceil
366 AND c2.seq_num = c1.next_seq_num +
367 DECODE(r.infinite_supply_fence_code,
368 1, ceil(i.cumulative_total_lead_time) + MSC_ATP_PVT.G_INF_SUP_TF_PAD,
369 2, ceil(i.cum_manufacturing_lead_time) + MSC_ATP_PVT.G_INF_SUP_TF_PAD,
370 3, DECODE(NVL(ceil(i.preprocessing_lead_time),-1)+
371 NVL(ceil(i.full_lead_time),-1)+
372 NVL(ceil(i.postprocessing_lead_time),-1),-3,
373 NULL, -- All are NULL so return NULL.
374 NVL(ceil(i.preprocessing_lead_time),0)+ -- Otherwise
375 NVL(ceil(i.full_lead_time),0) + -- evaluate to
376 NVL(ceil(i.postprocessing_lead_time),0) -- NON NULL
377 + MSC_ATP_PVT.G_INF_SUP_TF_PAD),
378 -- Bugs 1986353, 2004479.
379 4, r.infinite_supply_time_fence)
380 -- End Bug 2877340, 2746213
381 AND c2.calendar_code = c1.calendar_code
382 AND c2.exception_set_id = -1;
383 END IF;
384
385 IF PG_DEBUG in ('Y', 'C') THEN
386 msc_sch_wb.atp_debug('get_infinite_time_fence_date: ' || 'l_infinite_time_fence_date '||l_infinite_time_fence_date);
387 END IF;
388 return l_infinite_time_fence_date;
389
390 EXCEPTION
391 WHEN NO_DATA_FOUND THEN
392 -- for 1478110. please refer to the BUG
393 --IF p_plan_id = -1 THEN
394
395 -- ngoel 2/15/2002, modified to avoid no_data_found in case call is made from
396 -- View_Allocation
397
398 IF p_plan_id IN (-1, -200) THEN
399 return null;
400 ELSE
401
402 -- since this is pds, use planning's cutoff date as the infinite
403 -- time fence date if no rule at item/org level, and no rule
404 -- at org default.
405
406 SELECT trunc(curr_cutoff_date)
407 INTO l_infinite_time_fence_date
408 FROM msc_plans
409 WHERE plan_id = p_plan_id;
410
411 IF PG_DEBUG in ('Y', 'C') THEN
412 msc_sch_wb.atp_debug('get_infinite_time_fence_date: ' || 'l_infinite_time_fence_date'||l_infinite_time_fence_date);
413 END IF;
414 return l_infinite_time_fence_date;
415 END IF;
416
417 END get_infinite_time_fence_date;
418
419
420 FUNCTION get_org_code (p_instance_id IN NUMBER,
421 p_organization_id IN NUMBER)
422 RETURN VARCHAR2
423 IS
424 l_org_code VARCHAR2(7);
425
426 BEGIN
427
428 SELECT organization_code
429 INTO l_org_code
430 FROM msc_trading_partners
431 WHERE sr_tp_id = p_organization_id
432 AND sr_instance_id = p_instance_id
433 AND partner_type = 3;
434
435 return l_org_code;
436 EXCEPTION WHEN NO_DATA_FOUND THEN
437 return null;
438 END get_org_code;
439
440
441 FUNCTION get_inv_item_name (p_instance_id IN NUMBER,
442 p_inventory_item_id IN NUMBER,
443 p_organization_id IN NUMBER)
444 RETURN VARCHAR2
445 IS
446 l_inv_item_name VARCHAR2(40);
447
448 BEGIN
449
450 SELECT substr(ITEM_NAME, 1, 40)
451 INTO l_inv_item_name
452 FROM msc_system_items
453 WHERE organization_id = p_organization_id
454 AND sr_inventory_item_id = p_inventory_item_id
455 AND plan_id = -1
456 AND sr_instance_id = p_instance_id;
457
458 return l_inv_item_name;
459 EXCEPTION WHEN NO_DATA_FOUND THEN
460 return null;
461 END get_inv_item_name;
462
463
464 FUNCTION get_inv_item_id (p_instance_id IN NUMBER,
465 p_inventory_item_id IN NUMBER,
466 p_match_item_id IN NUMBER,
467 p_organization_id IN NUMBER)
468 RETURN number
469 IS
470 l_inv_item_id NUMBER;
471 l_sr_inv_item_id MRP_ATP_PUB.number_arr := MRP_ATP_PUB.number_arr();
472 l_inv_item_ids MRP_ATP_PUB.number_arr := MRP_ATP_PUB.number_arr();
473 l_item_count NUMBER;
474 l_count NUMBER; -- 4091487
475
476 BEGIN
477 /*
478 SELECT inventory_item_id
479 INTO l_inv_item_id
480 FROM msc_system_items
481 WHERE organization_id = p_organization_id
482 AND sr_inventory_item_id = p_inventory_item_id
483 AND plan_id = -1
484 AND sr_instance_id = p_instance_id;
485 */
486 IF PG_DEBUG in ('Y', 'C') THEN
487 msc_sch_wb.atp_debug('Inside get_inv_item_id');
488 msc_sch_wb.atp_debug('p_instance_id := ' || p_instance_id);
489 msc_sch_wb.atp_debug('p_inventory_item_id := ' || p_inventory_item_id);
490 msc_sch_wb.atp_debug('p_match_item_id := ' || p_match_item_id);
491 END IF;
492 IF p_match_item_id is not null THEN
493
494 IF PG_DEBUG in ('Y', 'C') THEN
495 msc_sch_wb.atp_debug('Match item exists');
496 END IF;
497 --bug 4091487 Added the if condition to drive query from msc_system_items in case organization id is passed
498 -- else go through msc_item_id_lid
499 IF(p_organization_id is not null) THEN
500
501 SELECT inventory_item_id, inventory_item_id
502 bulk collect into
503 l_sr_inv_item_id, l_inv_item_ids
504 FROM msc_system_items
505 WHERE organization_id = p_organization_id
506 AND sr_inventory_item_id in (p_inventory_item_id, p_match_item_id)
507 AND plan_id = -1
508 AND sr_instance_id = p_instance_id;
509
510 ELSE
511
512 select sr_inventory_item_id, inventory_item_id
513 bulk collect into
514 l_sr_inv_item_id, l_inv_item_ids
515 from msc_item_id_lid
516 where sr_inventory_item_id in (p_inventory_item_id, p_match_item_id)
517 AND sr_instance_id = p_instance_id;
518
519 END IF;
520
521 IF l_inv_item_ids.count = 0 THEN
522
523 RAISE NO_DATA_FOUND;
524 IF PG_DEBUG in ('Y', 'C') THEN
525 msc_sch_wb.atp_debug('Exception no data found');
526 END IF;
527 END IF;
528
529 IF PG_DEBUG in ('Y', 'C') THEN
530 msc_sch_wb.atp_debug('Number of item found := ' || l_sr_inv_item_id.count);
531
532 END IF;
533
534 FOR l_item_count in 1..l_sr_inv_item_id.count LOOP
535 IF PG_DEBUG in ('Y', 'C') THEN
536 msc_sch_wb.atp_debug('Item # := ' || l_item_count);
537 msc_sch_wb.atp_debug('Sr inv id := ' || l_sr_inv_item_id(l_item_count));
538 msc_sch_wb.atp_debug('Inv Id := ' || l_inv_item_ids(l_item_count));
539 END IF;
540 l_inv_item_id := l_inv_item_ids(l_item_count);
541 EXIT WHEN l_sr_inv_item_id(l_item_count) = p_match_item_id;
542 END LOOP;
543 ELSE
544 --bug 4091487 Added the if condition to drive query from msc_system_items in case organization id is passed
545 -- else go through msc_item_id_lid
546 IF(p_organization_id is not null) THEN
547
548 SELECT inventory_item_id
549 INTO l_inv_item_id
550 FROM msc_system_items
551 WHERE organization_id = p_organization_id
552 AND sr_inventory_item_id = p_inventory_item_id
553 AND plan_id = -1
554 AND sr_instance_id = p_instance_id;
555
556 ELSE
557
558 SELECT inventory_item_id
559 INTO l_inv_item_id
560 FROM msc_item_id_lid
561 WHERE sr_inventory_item_id = p_inventory_item_id
562 AND sr_instance_id = p_instance_id;
563
564 END IF;
565 END IF;
566
567 IF PG_DEBUG in ('Y', 'C') THEN
568 msc_sch_wb.atp_debug('l_inv_item_id := ' || l_inv_item_id);
569 msc_sch_wb.atp_debug('End get_inv_item_id');
570 END IF;
571
572 return l_inv_item_id;
573 EXCEPTION
574 WHEN NO_DATA_FOUND THEN
575 /*
576 4091487 Added the if condition to see if no items are present in msc_item_id_lid
577 raise TRY ATP Later other wise raise collections error.
578 */
579 IF(p_organization_id is null) THEN
580 Select count(*) into l_count from msc_item_id_lid;
581 IF l_count > 0 THEN
582 return null;
583 ELSE
584 return -1;
585 END IF;
586 ELSE
587 return null;
588 END IF;
589
590 WHEN OTHERS THEN
591 IF PG_DEBUG in ('Y', 'C') THEN
592 msc_sch_wb.atp_debug('error in get_inv_item_id');
593 msc_sch_wb.atp_debug('get_inv_item_id: ' || 'sqlerrm : '||sqlerrm);
594 END IF;
595 return null;
596 END get_inv_item_id;
597
598
599 FUNCTION get_supplier_name (p_instance_id IN NUMBER,
600 p_supplier_id IN NUMBER)
601 RETURN VARCHAR2
602 IS
603 l_supplier_name VARCHAR2(80);
604
605 BEGIN
606
607 SELECT partner_name
608 INTO l_supplier_name
609 FROM msc_trading_partners s
610 WHERE s.partner_id = p_supplier_id;
611
612 return l_supplier_name;
613
614 EXCEPTION WHEN NO_DATA_FOUND THEN
615 return null;
616 END get_supplier_name;
617
618
619 FUNCTION get_supplier_site_name (p_instance_id IN NUMBER,
620 p_supplier_site_id IN NUMBER)
621 RETURN VARCHAR2
622 IS
623 l_supplier_site_name VARCHAR2(80);
624
625 BEGIN
626
627 SELECT TP_SITE_CODE
628 INTO l_supplier_site_name
629 FROM msc_trading_partner_sites
630 WHERE PARTNER_SITE_ID = p_supplier_site_id;
631
632 return l_supplier_site_name;
633 EXCEPTION WHEN NO_DATA_FOUND THEN
634 return null;
635 END get_supplier_site_name;
636
637
638 FUNCTION get_location_code (p_instance_id IN NUMBER,
639 p_location_id IN NUMBER)
640 RETURN VARCHAR2
641 IS
642 l_location_code VARCHAR2(20);
643
644 BEGIN
645
646 return null;
647
648 EXCEPTION WHEN NO_DATA_FOUND THEN
649 return null;
650 END get_location_code;
651
652
653 FUNCTION get_sd_source_name (p_instance_id IN NUMBER,
654 p_sd_type IN NUMBER,
655 p_sd_source_type IN NUMBER)
656 RETURN VARCHAR2
657 IS
658 l_sd_source_name VARCHAR2(80);
659
660 BEGIN
661 SELECT MEANING
662 into l_sd_source_name
663 FROM MFG_LOOKUPS
664 WHERE LOOKUP_TYPE = DECODE(p_sd_type, 2, 'MRP_ORDER_TYPE',
665 DECODE(p_sd_source_type,
666 1, 'MRP_PLANNED_ORDER_DEMAND',
667 3, 'MRP_PLANNED_ORDER_DEMAND',
668 25, 'MRP_PLANNED_ORDER_DEMAND',
669 'MRP_DEMAND_ORIGINATION'))
670 AND LOOKUP_CODE = p_sd_source_type ;
671
672 return l_sd_source_name;
673
674 EXCEPTION WHEN NO_DATA_FOUND THEN
675 return null;
676 END get_sd_source_name;
677
678
679 FUNCTION prev_work_day(p_organization_id IN NUMBER,
680 p_instance_id IN NUMBER,
681 p_date IN DATE)
682 RETURN DATE
683 IS
684 l_date DATE;
685
686 BEGIN
687 SELECT cal.prior_date
688 INTO l_date
689 FROM msc_calendar_dates cal,
690 msc_trading_partners tp
691 WHERE cal.exception_set_id = tp.calendar_exception_set_id
692 AND cal.calendar_code = tp.calendar_code
693 AND cal.calendar_date = TRUNC(p_date)
694 AND cal.sr_instance_id = tp.sr_instance_id
695 AND tp.sr_instance_id = p_instance_id
696 AND tp.partner_type = 3
697 AND tp.sr_tp_id = p_organization_id;
698
699 return l_date;
700 EXCEPTION WHEN NO_DATA_FOUND THEN
701 return null;
702 END prev_work_day;
703
704
705 FUNCTION MPS_ATP(p_desig_id IN NUMBER)
706
707 RETURN NUMBER
708 IS
709
710 l_valid PLS_INTEGER := 0;
711 BEGIN
712
713 BEGIN
714 SELECT '1'
715 INTO l_valid
716 FROM MSC_DESIGNATORS
717 WHERE INVENTORY_ATP_FLAG = 1
718 AND DESIGNATOR_TYPE = 2
719 AND DESIGNATOR_ID = p_desig_id;
720 EXCEPTION
721 WHEN NO_DATA_FOUND THEN
722 l_valid := 0;
723 END;
724
725 RETURN l_valid;
726 END MPS_ATP;
727
728
729 FUNCTION Get_Designator(p_desig_id IN NUMBER)
730
731 RETURN VARCHAR2
732 IS
733
734 l_designator VARCHAR2(10);
735 BEGIN
736
737 BEGIN
738 SELECT designator
739 INTO l_designator
740 FROM MSC_DESIGNATORS
741 WHERE DESIGNATOR_ID = p_desig_id;
742 EXCEPTION
743 WHEN NO_DATA_FOUND THEN
744 l_designator := NULL;
745 END;
746
747 return l_designator;
748 END Get_Designator;
749
750
751 FUNCTION Get_MPS_Demand_Class(p_desig_id IN NUMBER)
752 RETURN VARCHAR2
753
754 IS
755 l_demand_class VARCHAR2(34);
756
757 BEGIN
758 BEGIN
759 SELECT demand_class
760 INTO l_demand_class
761 FROM MSC_DESIGNATORS
762 WHERE DESIGNATOR_ID = p_desig_id;
763 EXCEPTION
764 WHEN NO_DATA_FOUND THEN
765 l_demand_class := NULL;
766 END;
767
768 return l_demand_class;
769 END Get_MPS_Demand_Class;
770
771
772 FUNCTION NEXT_WORK_DAY_SEQNUM(p_organization_id IN NUMBER,
773 p_instance_id IN NUMBER,
774 p_date IN DATE)
775 RETURN number
776 IS
777 l_seq_num number;
778
779 BEGIN
780 SELECT C.NEXT_SEQ_NUM
781 INTO l_seq_num
782 FROM MSC_CALENDAR_DATES C,
783 MSC_TRADING_PARTNERS TP
784 WHERE TP.SR_TP_ID = p_organization_id
785 AND TP.SR_INSTANCE_ID = p_instance_id
786 AND TP.PARTNER_TYPE = 3
787 AND C.CALENDAR_CODE = TP.CALENDAR_CODE
788 AND C.EXCEPTION_SET_ID = TP.CALENDAR_EXCEPTION_SET_ID
789 AND C.SR_INSTANCE_ID = TP.SR_INSTANCE_ID
790 AND C.CALENDAR_DATE = TRUNC(p_date);
791
792 return l_seq_num;
793 END NEXT_WORK_DAY_SEQNUM;
794
795
796 FUNCTION get_tolerance_percentage(
797 p_instance_id IN NUMBER,
798 p_plan_id IN NUMBER,
799 p_inventory_item_id IN NUMBER,
800 p_organization_id IN NUMBER,
801 p_supplier_id IN NUMBER,
802 p_supplier_site_id IN NUMBER,
803 p_seq_num_difference IN NUMBER -- For ship_rec_cal
804 )
805 RETURN NUMBER
806 IS
807
808 v_tolerance_percent NUMBER;
809 v_fence_days NUMBER;
810
811 BEGIN
812
813 -- Rewriting the SQL as part of ship_rec_cal project.
814 SELECT tolerance_percentage
815 INTO v_tolerance_percent
816 FROM (SELECT tolerance_percentage
817 FROM msc_supplier_flex_fences
818 WHERE fence_days <= p_seq_num_difference
819 AND sr_instance_id = p_instance_id
820 AND plan_id = p_plan_id
821 AND organization_id = p_organization_id
822 AND inventory_item_id = p_inventory_item_id
823 AND supplier_id = p_supplier_id
824 AND NVL(supplier_site_id, -1) = NVL(p_supplier_site_id, -1)
825 ORDER BY fence_days desc
826 )
827 WHERE ROWNUM = 1;
828
829 return v_tolerance_percent/100;
830
831 EXCEPTION WHEN NO_DATA_FOUND THEN
832 return NULL;
833 END get_tolerance_percentage ;
834
835
836 FUNCTION Get_Order_Number(p_supply_id IN NUMBER,
837 p_plan_id IN NUMBER)
838
839 RETURN VARCHAR2
840
841 IS
842
843 l_order_number VARCHAR2(62);
844 BEGIN
845
846 BEGIN
847 SELECT order_number
848 INTO l_order_number
849 FROM MSC_SUPPLIES
850 WHERE plan_id = p_plan_id
851 AND transaction_id = p_supply_id;
852 EXCEPTION
853 WHEN NO_DATA_FOUND THEN
854 l_order_number := NULL;
855 END;
856
857 return l_order_number;
858 END Get_Order_Number;
859
860
861 FUNCTION Get_Order_Type(p_supply_id IN NUMBER,
862 p_plan_id IN NUMBER)
863
864 RETURN NUMBER
865
866 IS
867
868 l_order_type number;
869 BEGIN
870
871 BEGIN
872 SELECT order_type
873 INTO l_order_type
874 FROM MSC_SUPPLIES
875 WHERE plan_id = p_plan_id
876 AND transaction_id = p_supply_id;
877 EXCEPTION
878 WHEN NO_DATA_FOUND THEN
879 l_order_type := NULL;
880 END;
881
882 return l_order_type;
883 END Get_Order_Type;
884
885 -- savirine added parameters p_session_id and p_partner_site_id on Sep 24, 2001.
886
887 FUNCTION get_interloc_transit_time (p_from_location_id IN NUMBER,
888 p_from_instance_id IN NUMBER,
889 p_to_location_id IN NUMBER,
890 p_to_instance_id IN NUMBER,
891 p_ship_method IN VARCHAR2,
892 p_session_id IN NUMBER,
893 p_partner_site_id IN NUMBER)
894 return NUMBER IS
895
896 l_intransit_time NUMBER;
897 l_level NUMBER;
898
899 -- ngoel 9/25/2001, need to select most specific lead time based on regions
900 CURSOR c_lead_time
901 IS
902 SELECT intransit_time,
903 ((10 * (10 - mrt.region_type)) + DECODE(mrt.zone_flag, 'Y', 1, 0)) region_level
904 FROM msc_interorg_ship_methods mism,
905 msc_regions_temp mrt
906 WHERE mism.plan_id = -1
907 AND mism.from_location_id = p_from_location_id
908 AND mism.sr_instance_id = p_from_instance_id
909 AND mism.sr_instance_id2 = p_to_instance_id
910 AND mism.ship_method = p_ship_method
911 AND mism.to_region_id = mrt.region_id
912 AND mrt.session_id = p_session_id
913 AND mrt.partner_site_id = p_partner_site_id
914 ORDER BY 2;
915 BEGIN
916 BEGIN
917 -- bug 2958287
918 SELECT intransit_time
919 INTO l_intransit_time
920 FROM msc_interorg_ship_methods
921 WHERE plan_id = -1
922 AND from_location_id = p_from_location_id
923 AND sr_instance_id = p_from_instance_id
924 AND to_location_id = p_to_location_id
925 AND sr_instance_id2 = p_to_instance_id
926 AND ship_method = p_ship_method
927 AND to_region_id is null
928 AND rownum = 1;
929 EXCEPTION
930 WHEN NO_DATA_FOUND THEN
931 -- savirine added the following select statement on Sep 24, 2001
932 OPEN c_lead_time;
933 FETCH c_lead_time INTO l_intransit_time, l_level;
934 CLOSE c_lead_time;
935 END;
936 return l_intransit_time;
937 EXCEPTION
938 WHEN NO_DATA_FOUND THEN
939 return null;
940 END get_interloc_transit_time;
941
942
943 FUNCTION Calc_Arrival_date(
944 org_id IN NUMBER,
945 instance_id IN NUMBER,
946 customer_id IN NUMBER,
947 bucket_type IN NUMBER,
948 sch_ship_date IN DATE,
949 req_arrival_date IN DATE,
950 delivery_lead_time IN NUMBER
951
952 ) RETURN DATE IS
953
954 l_offset_date date;
955
956 -- This function is used to calculate the arrival date based
957 -- on given scheduled_ship_date and del_lead_time- Used in
958 ---USED in view MRP_ATP_SCHEDULE_TEMP_V
959
960 BEGIN
961 IF (customer_id IS NOT NULL) THEN
962 RETURN SCH_SHIP_DATE + NVL(delivery_lead_time,0);
963 ELSE
964 l_offset_date := MSC_CALENDAR.DATE_OFFSET (org_id,
965 instance_id,
966 bucket_type,
967 sch_ship_date,
968 NVL(delivery_lead_time, 0));
969
970 --- If arrival date is provided and offsetted arrival date is
971 --- less than the requested_arrival_date then we return arrival date else
972 --- we return the offseted date.
973 IF ((req_arrival_date IS NOT NULL) AND (l_offset_date < req_arrival_date)) THEN
974
975 RETURN req_arrival_date;
976 ELSE
977 RETURN l_offset_date;
978 --RETURN '21-APR-2001';
979 END IF;
980 END IF;
981 EXCEPTION
982 WHEN OTHERS THEN
983 RETURN sch_ship_date + delivery_lead_time;
984 END Calc_Arrival_date;
985
986
987 -- ngoel 9/28/2001, added this function for use in View MSC_SCATP_SOURCES_V to support
988 -- Region Level Sourcing.
989
990 FUNCTION Get_Session_id
991 RETURN NUMBER
992 IS
993 BEGIN
994 RETURN order_sch_wb.debug_session_id;
995 END Get_Session_id;
996
997 -- rajjain 02/19/2003 Bug 2788302 Begin
998 --pumehta added this function to get process_sequence_id to be populated when
999 --adding a planned order for Make Case.
1000 FUNCTION get_process_seq_id(
1001 p_plan_id IN NUMBER,
1002 p_item_id IN NUMBER,
1003 p_organization_id IN NUMBER,
1004 p_sr_instance_id IN NUMBER,
1005 p_new_schedule_date IN DATE
1006 ) RETURN NUMBER
1007 IS
1008 l_process_seq_id NUMBER;
1009 BEGIN
1010 msc_sch_wb.atp_debug('Selecting Process Sequence ID');
1011 Select process_sequence_id
1012 into l_process_seq_id
1013 from msc_process_effectivity prc
1014 where prc.plan_id = p_plan_id
1015 and prc.item_id = p_item_id
1016 and prc.organization_id = p_organization_id
1017 and prc.sr_instance_id = p_sr_instance_id
1018 and trunc(prc.effectivity_date) <= trunc(p_new_schedule_date)
1019 and trunc(nvl(prc.disable_date,p_new_schedule_date))
1020 >= trunc(p_new_schedule_date)
1021 and prc.preference = 1;
1022 RETURN l_process_seq_id;
1023 EXCEPTION
1024 WHEN OTHERS THEN
1025 msc_sch_wb.atp_debug('Get_Process_Seq_Id: ' || 'could not find process seq id,returning null');
1026 return NULL;
1027 END get_process_seq_id;
1028 -- rajjain 02/19/2003 Bug 2788302 End
1029
1030 -- ALLOC ATP CHANGES, 12973673
1031 FUNCTION check_alloc_exists (p_instance_id IN NUMBER,
1032 p_plan_id IN NUMBER,
1033 p_inventory_item_id IN NUMBER,
1034 p_organization_id IN NUMBER,
1035 p_requested_date IN DATE)
1036 RETURN VARCHAR2
1037 IS
1038 l_count PLS_INTEGER := 0;
1039
1040 BEGIN
1041
1042 IF PG_DEBUG in ('Y', 'C') THEN
1043 msc_sch_wb.atp_debug('inside check_alloc_exists');
1044 msc_sch_wb.atp_debug('check_alloc_exists: ' || 'sr_inventory_item_id = '||p_inventory_item_id);
1045 msc_sch_wb.atp_debug('check_alloc_exists: ' || 'organization_id = '||p_organization_id);
1046 msc_sch_wb.atp_debug('check_alloc_exists: ' || 'plan_id = '||p_plan_id);
1047 msc_sch_wb.atp_debug('check_alloc_exists: ' || 'sr_instance_id = '||p_instance_id);
1048 msc_sch_wb.atp_debug('check_alloc_exists: ' || 'p_requested_date = '||p_requested_date);
1049 msc_sch_wb.atp_debug('check_alloc_exists: ' || 'MSC_ATP_PVT.G_ALLOCATED_ATP = '||MSC_ATP_PVT.G_ALLOCATED_ATP);
1050 END IF;
1051
1052 SELECT Count(1)
1053 INTO l_count
1054 FROM msc_item_hierarchy_mv mv,
1055 msc_system_items item
1056 WHERE item.sr_instance_id = p_instance_id
1057 and item.sr_inventory_item_id = p_inventory_item_id
1058 and item.organization_id = p_organization_id
1059 and item.plan_id = p_plan_id
1060 and mv.inventory_item_id = item.inventory_item_id
1061 and mv.organization_id = item.organization_id
1062 and mv.sr_instance_id = item.sr_instance_id
1063 and greatest(p_requested_date,trunc(sysdate)) between mv.effective_date and mv.disable_date
1064 and rownum=1;
1065
1066 IF PG_DEBUG in ('Y', 'C') THEN
1067 msc_sch_wb.atp_debug('check_alloc_exists: ' || 'l_count = '||l_count);
1068 END IF;
1069
1070 IF l_count > 0 THEN
1071 return 'Y';
1072 ELSE
1073 return 'N';
1074 END IF;
1075
1076 EXCEPTION
1077 WHEN OTHERS THEN
1078 IF PG_DEBUG in ('Y', 'C') THEN
1079 msc_sch_wb.atp_debug('check_alloc_exists: ' || 'sqlcode : '||sqlcode);
1080 msc_sch_wb.atp_debug('check_alloc_exists: ' || 'sqlerr : '||sqlerrm);
1081 END IF;
1082 return 'N';
1083 END check_alloc_exists;
1084
1085 END MSC_ATP_FUNC;
[Home] [Help]