1 PACKAGE BODY MSC_ATP_FUNC AS
2 /* $Header: MSCFATPB.pls 120.1 2007/12/12 10:27:11 sbnaik 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 AND loc.organization_id is NULL;
245 -- Add organization_id is null filter.
246 -- End Bug 2793404
247
248 ELSIF ((p_supplier_id IS NOT NULL) AND
249 (p_supplier_site_id IS NOT NULL)) THEN
250
251 -- cchen: 1124206
252 SELECT l.location_id
253 INTO l_location_id
254 FROM msc_location_associations l
255 WHERE l.sr_instance_id = p_instance_id
256 AND l.partner_id = p_supplier_id
257 AND l.partner_site_id = p_supplier_site_id;
258
259 END IF;
260
261 return l_location_id;
262 EXCEPTION WHEN NO_DATA_FOUND THEN
263 return null;
264
265 END get_location_id;
266
267
268 FUNCTION get_infinite_time_fence_date (p_instance_id IN NUMBER,
269 p_inventory_item_id IN NUMBER,
270 p_organization_id IN NUMBER,
271 p_plan_id IN NUMBER)
272 RETURN DATE
273 IS
274
275 l_infinite_time_fence_date DATE;
276 l_item_type NUMBER;
277
278 BEGIN
279 /* for 1478110. please refer to the BUG
280 IF p_plan_id <> -1 THEN
281 IF PG_DEBUG in ('Y', 'C') THEN
282 msc_sch_wb.atp_debug('get_infinite_time_fence_date: ' || 'selecting infinite_time_fence_date, MSC_ATP_PVT.G_INV_CTP = 4');
283 END IF;
284
285 SELECT curr_cutoff_date
286 INTO l_infinite_time_fence_date
287 FROM msc_plans
288 WHERE plan_id = p_plan_id;
289 IF PG_DEBUG in ('Y', 'C') THEN
290 msc_sch_wb.atp_debug('get_infinite_time_fence_date: ' || 'l_infinite_time_fence_date'||l_infinite_time_fence_date);
291 END IF;
292 ELSE
293
294 IF PG_DEBUG in ('Y', 'C') THEN
295 msc_sch_wb.atp_debug('get_infinite_time_fence_date: ' || 'selecting infinite_time_fence_date, MSC_ATP_PVT.G_INV_CTP = 5');
296 END IF;
297 */
298
299 -- Bug 1566260, in case of modle or option class for PDS ATP, return null.
300 -- This way, we always will work with multi-level results rather than single level
301 -- This was done so that pegging tree is always available for Mulit-org CTO and
302 -- demand entries could be stored for planning purposes.
303
304 IF p_plan_id <> -1 THEN
305 IF PG_DEBUG in ('Y', 'C') THEN
306 msc_sch_wb.atp_debug('get_infinite_time_fence_date: ' || 'selecting item type for PDS');
307 END IF;
308 SELECT i.bom_item_type
309 INTO l_item_type
310 FROM msc_system_items i
311 WHERE i.plan_id = p_plan_id
312 AND i.sr_instance_id = p_instance_id
313 AND i.organization_id = p_organization_id
314 AND i.sr_inventory_item_id = p_inventory_item_id;
315 END IF;
316
317 IF nvl(l_item_type, -1) IN (1,2) THEN
318 IF PG_DEBUG in ('Y', 'C') THEN
319 msc_sch_wb.atp_debug('get_infinite_time_fence_date: ' || 'PDS item type is model(1) or option class(2) : '||l_item_type);
320 END IF;
321 l_infinite_time_fence_date := null;
322 ELSE
323
324 -- Bug 2877340, 2746213
325 -- Read the Profile option to pad the user defined days
326 -- to infinite Supply fence.
327 IF PG_DEBUG in ('Y', 'C') THEN
328 msc_sch_wb.atp_debug('get_infinite_time_fence_date: Profile value for Infinite Supply Pad');
329 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);
330 END IF;
331 -- End Bug 2877340, 2746213
332 SELECT c2.calendar_date
333 INTO l_infinite_time_fence_date
334 FROM msc_calendar_dates c2,
335 msc_calendar_dates c1,
336 msc_atp_rules r,
337 msc_trading_partners tp,
338 msc_system_items i
339 WHERE i.sr_inventory_item_id = p_inventory_item_id
340 AND i.organization_id = p_organization_id
341 --AND i.plan_id = p_plan_id
342 AND i.plan_id = -1 -- for 1478110
343 AND i.sr_instance_id = p_instance_id
344 AND tp.sr_tp_id = i.organization_id
345 AND tp.sr_instance_id = i.sr_instance_id
346 AND tp.partner_type = 3
347 AND r.sr_instance_id = tp.sr_instance_id
348 AND r.rule_id = NVL(i.atp_rule_id, NVL(tp.default_atp_rule_id,0))
349 AND c1.sr_instance_id = r.sr_instance_id
350 AND c1.calendar_date = TRUNC(sysdate)
351 AND c1.calendar_code = tp.calendar_code
352 AND c1.exception_set_id = -1
353 AND c2.sr_instance_id = c1.sr_instance_id
354
355 -- Bug 2877340, 2746213
356 -- Add Infinite Supply Time Fence PAD
357 --bug3609031 adding ceil
358 AND c2.seq_num = c1.next_seq_num +
359 DECODE(r.infinite_supply_fence_code,
360 1, ceil(i.cumulative_total_lead_time) + MSC_ATP_PVT.G_INF_SUP_TF_PAD,
361 2, ceil(i.cum_manufacturing_lead_time) + MSC_ATP_PVT.G_INF_SUP_TF_PAD,
362 3, DECODE(NVL(ceil(i.preprocessing_lead_time),-1)+
363 NVL(ceil(i.full_lead_time),-1)+
364 NVL(ceil(i.postprocessing_lead_time),-1),-3,
365 NULL, -- All are NULL so return NULL.
366 NVL(ceil(i.preprocessing_lead_time),0)+ -- Otherwise
367 NVL(ceil(i.full_lead_time),0) + -- evaluate to
368 NVL(ceil(i.postprocessing_lead_time),0) -- NON NULL
369 + MSC_ATP_PVT.G_INF_SUP_TF_PAD),
370 -- Bugs 1986353, 2004479.
371 4, r.infinite_supply_time_fence)
372 -- End Bug 2877340, 2746213
373 AND c2.calendar_code = c1.calendar_code
374 AND c2.exception_set_id = -1;
375 END IF;
376
377 IF PG_DEBUG in ('Y', 'C') THEN
378 msc_sch_wb.atp_debug('get_infinite_time_fence_date: ' || 'l_infinite_time_fence_date '||l_infinite_time_fence_date);
379 END IF;
380 return l_infinite_time_fence_date;
381
382 EXCEPTION
383 WHEN NO_DATA_FOUND THEN
384 -- for 1478110. please refer to the BUG
385 --IF p_plan_id = -1 THEN
386
387 -- ngoel 2/15/2002, modified to avoid no_data_found in case call is made from
388 -- View_Allocation
389
390 IF p_plan_id IN (-1, -200) THEN
391 return null;
392 ELSE
393
394 -- since this is pds, use planning's cutoff date as the infinite
395 -- time fence date if no rule at item/org level, and no rule
396 -- at org default.
397
398 SELECT trunc(curr_cutoff_date)
399 INTO l_infinite_time_fence_date
400 FROM msc_plans
401 WHERE plan_id = p_plan_id;
402
403 IF PG_DEBUG in ('Y', 'C') THEN
404 msc_sch_wb.atp_debug('get_infinite_time_fence_date: ' || 'l_infinite_time_fence_date'||l_infinite_time_fence_date);
405 END IF;
406 return l_infinite_time_fence_date;
407 END IF;
408
409 END get_infinite_time_fence_date;
410
411
412 FUNCTION get_org_code (p_instance_id IN NUMBER,
413 p_organization_id IN NUMBER)
414 RETURN VARCHAR2
415 IS
416 l_org_code VARCHAR2(7);
417
418 BEGIN
419
420 SELECT organization_code
421 INTO l_org_code
422 FROM msc_trading_partners
423 WHERE sr_tp_id = p_organization_id
424 AND sr_instance_id = p_instance_id
425 AND partner_type = 3;
426
427 return l_org_code;
428 EXCEPTION WHEN NO_DATA_FOUND THEN
429 return null;
430 END get_org_code;
431
432
433 FUNCTION get_inv_item_name (p_instance_id IN NUMBER,
434 p_inventory_item_id IN NUMBER,
435 p_organization_id IN NUMBER)
436 RETURN VARCHAR2
437 IS
438 l_inv_item_name VARCHAR2(40);
439
440 BEGIN
441
442 SELECT substr(ITEM_NAME, 1, 40)
443 INTO l_inv_item_name
444 FROM msc_system_items
445 WHERE organization_id = p_organization_id
446 AND sr_inventory_item_id = p_inventory_item_id
447 AND plan_id = -1
448 AND sr_instance_id = p_instance_id;
449
450 return l_inv_item_name;
451 EXCEPTION WHEN NO_DATA_FOUND THEN
452 return null;
453 END get_inv_item_name;
454
455
456 FUNCTION get_inv_item_id (p_instance_id IN NUMBER,
457 p_inventory_item_id IN NUMBER,
458 p_match_item_id IN NUMBER,
459 p_organization_id IN NUMBER)
460 RETURN number
461 IS
462 l_inv_item_id NUMBER;
463 l_sr_inv_item_id MRP_ATP_PUB.number_arr := MRP_ATP_PUB.number_arr();
464 l_inv_item_ids MRP_ATP_PUB.number_arr := MRP_ATP_PUB.number_arr();
465 l_item_count NUMBER;
466 l_count NUMBER; -- 4091487
467
468 BEGIN
469 /*
470 SELECT inventory_item_id
471 INTO l_inv_item_id
472 FROM msc_system_items
473 WHERE organization_id = p_organization_id
474 AND sr_inventory_item_id = p_inventory_item_id
475 AND plan_id = -1
476 AND sr_instance_id = p_instance_id;
477 */
478 IF PG_DEBUG in ('Y', 'C') THEN
479 msc_sch_wb.atp_debug('Inside get_inv_item_id');
480 msc_sch_wb.atp_debug('p_instance_id := ' || p_instance_id);
481 msc_sch_wb.atp_debug('p_inventory_item_id := ' || p_inventory_item_id);
482 msc_sch_wb.atp_debug('p_match_item_id := ' || p_match_item_id);
483 END IF;
484 IF p_match_item_id is not null THEN
485
486 IF PG_DEBUG in ('Y', 'C') THEN
487 msc_sch_wb.atp_debug('Match item exists');
488 END IF;
489 --bug 4091487 Added the if condition to drive query from msc_system_items in case organization id is passed
490 -- else go through msc_item_id_lid
491 IF(p_organization_id is not null) THEN
492
493 SELECT inventory_item_id, inventory_item_id
494 bulk collect into
495 l_sr_inv_item_id, l_inv_item_ids
496 FROM msc_system_items
497 WHERE organization_id = p_organization_id
498 AND sr_inventory_item_id in (p_inventory_item_id, p_match_item_id)
499 AND plan_id = -1
500 AND sr_instance_id = p_instance_id;
501
502 ELSE
503
504 select sr_inventory_item_id, inventory_item_id
505 bulk collect into
506 l_sr_inv_item_id, l_inv_item_ids
507 from msc_item_id_lid
508 where sr_inventory_item_id in (p_inventory_item_id, p_match_item_id)
509 AND sr_instance_id = p_instance_id;
510
511 END IF;
512
513 IF l_inv_item_ids.count = 0 THEN
514
515 RAISE NO_DATA_FOUND;
516 IF PG_DEBUG in ('Y', 'C') THEN
517 msc_sch_wb.atp_debug('Exception no data found');
518 END IF;
519 END IF;
520
521 IF PG_DEBUG in ('Y', 'C') THEN
522 msc_sch_wb.atp_debug('Number of item found := ' || l_sr_inv_item_id.count);
523
524 END IF;
525
526 FOR l_item_count in 1..l_sr_inv_item_id.count LOOP
527 IF PG_DEBUG in ('Y', 'C') THEN
528 msc_sch_wb.atp_debug('Item # := ' || l_item_count);
529 msc_sch_wb.atp_debug('Sr inv id := ' || l_sr_inv_item_id(l_item_count));
530 msc_sch_wb.atp_debug('Inv Id := ' || l_inv_item_ids(l_item_count));
531 END IF;
532 l_inv_item_id := l_inv_item_ids(l_item_count);
533 EXIT WHEN l_sr_inv_item_id(l_item_count) = p_match_item_id;
534 END LOOP;
535 ELSE
536 --bug 4091487 Added the if condition to drive query from msc_system_items in case organization id is passed
537 -- else go through msc_item_id_lid
538 IF(p_organization_id is not null) THEN
539
540 SELECT inventory_item_id
541 INTO l_inv_item_id
542 FROM msc_system_items
543 WHERE organization_id = p_organization_id
544 AND sr_inventory_item_id = p_inventory_item_id
545 AND plan_id = -1
546 AND sr_instance_id = p_instance_id;
547
548 ELSE
549
550 SELECT inventory_item_id
551 INTO l_inv_item_id
552 FROM msc_item_id_lid
553 WHERE sr_inventory_item_id = p_inventory_item_id
554 AND sr_instance_id = p_instance_id;
555
556 END IF;
557 END IF;
558
559 IF PG_DEBUG in ('Y', 'C') THEN
560 msc_sch_wb.atp_debug('l_inv_item_id := ' || l_inv_item_id);
561 msc_sch_wb.atp_debug('End get_inv_item_id');
562 END IF;
563
564 return l_inv_item_id;
565 EXCEPTION
566 WHEN NO_DATA_FOUND THEN
567 /*
568 4091487 Added the if condition to see if no items are present in msc_item_id_lid
569 raise TRY ATP Later other wise raise collections error.
570 */
571 IF(p_organization_id is null) THEN
572 Select count(*) into l_count from msc_item_id_lid;
573 IF l_count > 0 THEN
574 return null;
575 ELSE
576 return -1;
577 END IF;
578 ELSE
579 return null;
580 END IF;
581
582 WHEN OTHERS THEN
583 IF PG_DEBUG in ('Y', 'C') THEN
584 msc_sch_wb.atp_debug('error in get_inv_item_id');
585 msc_sch_wb.atp_debug('get_inv_item_id: ' || 'sqlerrm : '||sqlerrm);
586 END IF;
587 return null;
588 END get_inv_item_id;
589
590
591 FUNCTION get_supplier_name (p_instance_id IN NUMBER,
592 p_supplier_id IN NUMBER)
593 RETURN VARCHAR2
594 IS
595 l_supplier_name VARCHAR2(80);
596
597 BEGIN
598
599 SELECT partner_name
600 INTO l_supplier_name
601 FROM msc_trading_partners s
602 WHERE s.partner_id = p_supplier_id;
603
604 return l_supplier_name;
605
606 EXCEPTION WHEN NO_DATA_FOUND THEN
607 return null;
608 END get_supplier_name;
609
610
611 FUNCTION get_supplier_site_name (p_instance_id IN NUMBER,
612 p_supplier_site_id IN NUMBER)
613 RETURN VARCHAR2
614 IS
615 l_supplier_site_name VARCHAR2(80);
616
617 BEGIN
618
619 SELECT TP_SITE_CODE
620 INTO l_supplier_site_name
621 FROM msc_trading_partner_sites
622 WHERE PARTNER_SITE_ID = p_supplier_site_id;
623
624 return l_supplier_site_name;
625 EXCEPTION WHEN NO_DATA_FOUND THEN
626 return null;
627 END get_supplier_site_name;
628
629
630 FUNCTION get_location_code (p_instance_id IN NUMBER,
631 p_location_id IN NUMBER)
632 RETURN VARCHAR2
633 IS
634 l_location_code VARCHAR2(20);
635
636 BEGIN
637
638 return null;
639
640 EXCEPTION WHEN NO_DATA_FOUND THEN
641 return null;
642 END get_location_code;
643
644
645 FUNCTION get_sd_source_name (p_instance_id IN NUMBER,
646 p_sd_type IN NUMBER,
647 p_sd_source_type IN NUMBER)
648 RETURN VARCHAR2
649 IS
650 l_sd_source_name VARCHAR2(80);
651
652 BEGIN
653 SELECT MEANING
654 into l_sd_source_name
655 FROM MFG_LOOKUPS
656 WHERE LOOKUP_TYPE = DECODE(p_sd_type, 2, 'MRP_ORDER_TYPE',
657 DECODE(p_sd_source_type,
658 1, 'MRP_PLANNED_ORDER_DEMAND',
659 3, 'MRP_PLANNED_ORDER_DEMAND',
660 25, 'MRP_PLANNED_ORDER_DEMAND',
661 'MRP_DEMAND_ORIGINATION'))
662 AND LOOKUP_CODE = p_sd_source_type ;
663
664 return l_sd_source_name;
665
666 EXCEPTION WHEN NO_DATA_FOUND THEN
667 return null;
668 END get_sd_source_name;
669
670
671 FUNCTION prev_work_day(p_organization_id IN NUMBER,
672 p_instance_id IN NUMBER,
673 p_date IN DATE)
674 RETURN DATE
675 IS
676 l_date DATE;
677
678 BEGIN
679 SELECT cal.prior_date
680 INTO l_date
681 FROM msc_calendar_dates cal,
682 msc_trading_partners tp
683 WHERE cal.exception_set_id = tp.calendar_exception_set_id
684 AND cal.calendar_code = tp.calendar_code
685 AND cal.calendar_date = TRUNC(p_date)
686 AND cal.sr_instance_id = tp.sr_instance_id
687 AND tp.sr_instance_id = p_instance_id
688 AND tp.partner_type = 3
689 AND tp.sr_tp_id = p_organization_id;
690
691 return l_date;
692 EXCEPTION WHEN NO_DATA_FOUND THEN
693 return null;
694 END prev_work_day;
695
696
697 FUNCTION MPS_ATP(p_desig_id IN NUMBER)
698
699 RETURN NUMBER
700 IS
701
702 l_valid PLS_INTEGER := 0;
703 BEGIN
704
705 BEGIN
706 SELECT '1'
707 INTO l_valid
708 FROM MSC_DESIGNATORS
709 WHERE INVENTORY_ATP_FLAG = 1
710 AND DESIGNATOR_TYPE = 2
711 AND DESIGNATOR_ID = p_desig_id;
712 EXCEPTION
713 WHEN NO_DATA_FOUND THEN
714 l_valid := 0;
715 END;
716
717 RETURN l_valid;
718 END MPS_ATP;
719
720
721 FUNCTION Get_Designator(p_desig_id IN NUMBER)
722
723 RETURN VARCHAR2
724 IS
725
726 l_designator VARCHAR2(10);
727 BEGIN
728
729 BEGIN
730 SELECT designator
731 INTO l_designator
732 FROM MSC_DESIGNATORS
733 WHERE DESIGNATOR_ID = p_desig_id;
734 EXCEPTION
735 WHEN NO_DATA_FOUND THEN
736 l_designator := NULL;
737 END;
738
739 return l_designator;
740 END Get_Designator;
741
742
743 FUNCTION Get_MPS_Demand_Class(p_desig_id IN NUMBER)
744 RETURN VARCHAR2
745
746 IS
747 l_demand_class VARCHAR2(34);
748
749 BEGIN
750 BEGIN
751 SELECT demand_class
752 INTO l_demand_class
753 FROM MSC_DESIGNATORS
754 WHERE DESIGNATOR_ID = p_desig_id;
755 EXCEPTION
756 WHEN NO_DATA_FOUND THEN
757 l_demand_class := NULL;
758 END;
759
760 return l_demand_class;
761 END Get_MPS_Demand_Class;
762
763
764 FUNCTION NEXT_WORK_DAY_SEQNUM(p_organization_id IN NUMBER,
765 p_instance_id IN NUMBER,
766 p_date IN DATE)
767 RETURN number
768 IS
769 l_seq_num number;
770
771 BEGIN
772 SELECT C.NEXT_SEQ_NUM
773 INTO l_seq_num
774 FROM MSC_CALENDAR_DATES C,
775 MSC_TRADING_PARTNERS TP
776 WHERE TP.SR_TP_ID = p_organization_id
777 AND TP.SR_INSTANCE_ID = p_instance_id
778 AND TP.PARTNER_TYPE = 3
779 AND C.CALENDAR_CODE = TP.CALENDAR_CODE
780 AND C.EXCEPTION_SET_ID = TP.CALENDAR_EXCEPTION_SET_ID
781 AND C.SR_INSTANCE_ID = TP.SR_INSTANCE_ID
782 AND C.CALENDAR_DATE = TRUNC(p_date);
783
784 return l_seq_num;
785 END NEXT_WORK_DAY_SEQNUM;
786
787
788 FUNCTION get_tolerance_percentage(
789 p_instance_id IN NUMBER,
790 p_plan_id IN NUMBER,
791 p_inventory_item_id IN NUMBER,
792 p_organization_id IN NUMBER,
793 p_supplier_id IN NUMBER,
794 p_supplier_site_id IN NUMBER,
795 p_seq_num_difference IN NUMBER -- For ship_rec_cal
796 )
797 RETURN NUMBER
798 IS
799
800 v_tolerance_percent NUMBER;
801 v_fence_days NUMBER;
802
803 BEGIN
804
805 -- Rewriting the SQL as part of ship_rec_cal project.
806 SELECT tolerance_percentage
807 INTO v_tolerance_percent
808 FROM (SELECT tolerance_percentage
809 FROM msc_supplier_flex_fences
810 WHERE fence_days <= p_seq_num_difference
811 AND sr_instance_id = p_instance_id
812 AND plan_id = p_plan_id
813 AND organization_id = p_organization_id
814 AND inventory_item_id = p_inventory_item_id
815 AND supplier_id = p_supplier_id
816 AND NVL(supplier_site_id, -1) = NVL(p_supplier_site_id, -1)
817 ORDER BY fence_days desc
818 )
819 WHERE ROWNUM = 1;
820
821 return v_tolerance_percent/100;
822
823 EXCEPTION WHEN NO_DATA_FOUND THEN
824 return NULL;
825 END get_tolerance_percentage ;
826
827
828 FUNCTION Get_Order_Number(p_supply_id IN NUMBER,
829 p_plan_id IN NUMBER)
830
831 RETURN VARCHAR2
832
833 IS
834
835 l_order_number VARCHAR2(62);
836 BEGIN
837
838 BEGIN
839 SELECT order_number
840 INTO l_order_number
841 FROM MSC_SUPPLIES
842 WHERE plan_id = p_plan_id
843 AND transaction_id = p_supply_id;
844 EXCEPTION
845 WHEN NO_DATA_FOUND THEN
846 l_order_number := NULL;
847 END;
848
849 return l_order_number;
850 END Get_Order_Number;
851
852
853 FUNCTION Get_Order_Type(p_supply_id IN NUMBER,
854 p_plan_id IN NUMBER)
855
856 RETURN NUMBER
857
858 IS
859
860 l_order_type number;
861 BEGIN
862
863 BEGIN
864 SELECT order_type
865 INTO l_order_type
866 FROM MSC_SUPPLIES
867 WHERE plan_id = p_plan_id
868 AND transaction_id = p_supply_id;
869 EXCEPTION
870 WHEN NO_DATA_FOUND THEN
871 l_order_type := NULL;
872 END;
873
874 return l_order_type;
875 END Get_Order_Type;
876
877 -- savirine added parameters p_session_id and p_partner_site_id on Sep 24, 2001.
878
879 FUNCTION get_interloc_transit_time (p_from_location_id IN NUMBER,
880 p_from_instance_id IN NUMBER,
881 p_to_location_id IN NUMBER,
882 p_to_instance_id IN NUMBER,
883 p_ship_method IN VARCHAR2,
884 p_session_id IN NUMBER,
885 p_partner_site_id IN NUMBER)
886 return NUMBER IS
887
888 l_intransit_time NUMBER;
889 l_level NUMBER;
890
891 -- ngoel 9/25/2001, need to select most specific lead time based on regions
892 CURSOR c_lead_time
893 IS
894 SELECT intransit_time,
895 ((10 * (10 - mrt.region_type)) + DECODE(mrt.zone_flag, 'Y', 1, 0)) region_level
896 FROM msc_interorg_ship_methods mism,
897 msc_regions_temp mrt
898 WHERE mism.plan_id = -1
899 AND mism.from_location_id = p_from_location_id
900 AND mism.sr_instance_id = p_from_instance_id
901 AND mism.sr_instance_id2 = p_to_instance_id
902 AND mism.ship_method = p_ship_method
903 AND mism.to_region_id = mrt.region_id
904 AND mrt.session_id = p_session_id
905 AND mrt.partner_site_id = p_partner_site_id
906 ORDER BY 2;
907 BEGIN
908 BEGIN
909 -- bug 2958287
910 SELECT intransit_time
911 INTO l_intransit_time
912 FROM msc_interorg_ship_methods
913 WHERE plan_id = -1
914 AND from_location_id = p_from_location_id
915 AND sr_instance_id = p_from_instance_id
916 AND to_location_id = p_to_location_id
917 AND sr_instance_id2 = p_to_instance_id
918 AND ship_method = p_ship_method
919 AND to_region_id is null
920 AND rownum = 1;
921 EXCEPTION
922 WHEN NO_DATA_FOUND THEN
923 -- savirine added the following select statement on Sep 24, 2001
924 OPEN c_lead_time;
925 FETCH c_lead_time INTO l_intransit_time, l_level;
926 CLOSE c_lead_time;
927 END;
928 return l_intransit_time;
929 EXCEPTION
930 WHEN NO_DATA_FOUND THEN
931 return null;
932 END get_interloc_transit_time;
933
934
935 FUNCTION Calc_Arrival_date(
936 org_id IN NUMBER,
937 instance_id IN NUMBER,
938 customer_id IN NUMBER,
939 bucket_type IN NUMBER,
940 sch_ship_date IN DATE,
941 req_arrival_date IN DATE,
942 delivery_lead_time IN NUMBER
943
944 ) RETURN DATE IS
945
946 l_offset_date date;
947
948 -- This function is used to calculate the arrival date based
949 -- on given scheduled_ship_date and del_lead_time- Used in
950 ---USED in view MRP_ATP_SCHEDULE_TEMP_V
951
952 BEGIN
953 IF (customer_id IS NOT NULL) THEN
954 RETURN SCH_SHIP_DATE + NVL(delivery_lead_time,0);
955 ELSE
956 l_offset_date := MSC_CALENDAR.DATE_OFFSET (org_id,
957 instance_id,
958 bucket_type,
959 sch_ship_date,
960 NVL(delivery_lead_time, 0));
961
962 --- If arrival date is provided and offsetted arrival date is
963 --- less than the requested_arrival_date then we return arrival date else
964 --- we return the offseted date.
965 IF ((req_arrival_date IS NOT NULL) AND (l_offset_date < req_arrival_date)) THEN
966
967 RETURN req_arrival_date;
968 ELSE
969 RETURN l_offset_date;
970 --RETURN '21-APR-2001';
971 END IF;
972 END IF;
973 EXCEPTION
974 WHEN OTHERS THEN
975 RETURN sch_ship_date + delivery_lead_time;
976 END Calc_Arrival_date;
977
978
979 -- ngoel 9/28/2001, added this function for use in View MSC_SCATP_SOURCES_V to support
980 -- Region Level Sourcing.
981
982 FUNCTION Get_Session_id
983 RETURN NUMBER
984 IS
985 BEGIN
986 RETURN order_sch_wb.debug_session_id;
987 END Get_Session_id;
988
989 -- rajjain 02/19/2003 Bug 2788302 Begin
990 --pumehta added this function to get process_sequence_id to be populated when
991 --adding a planned order for Make Case.
992 FUNCTION get_process_seq_id(
993 p_plan_id IN NUMBER,
994 p_item_id IN NUMBER,
995 p_organization_id IN NUMBER,
996 p_sr_instance_id IN NUMBER,
997 p_new_schedule_date IN DATE
998 ) RETURN NUMBER
999 IS
1000 l_process_seq_id NUMBER;
1001 BEGIN
1002 msc_sch_wb.atp_debug('Selecting Process Sequence ID');
1003 Select process_sequence_id
1004 into l_process_seq_id
1005 from msc_process_effectivity prc
1006 where prc.plan_id = p_plan_id
1007 and prc.item_id = p_item_id
1008 and prc.organization_id = p_organization_id
1009 and prc.sr_instance_id = p_sr_instance_id
1010 and trunc(prc.effectivity_date) <= trunc(p_new_schedule_date)
1011 and trunc(nvl(prc.disable_date,p_new_schedule_date))
1012 >= trunc(p_new_schedule_date)
1013 and prc.preference = 1;
1014 RETURN l_process_seq_id;
1015 EXCEPTION
1016 WHEN OTHERS THEN
1017 msc_sch_wb.atp_debug('Get_Process_Seq_Id: ' || 'could not find process seq id,returning null');
1018 return NULL;
1019 END get_process_seq_id;
1020 -- rajjain 02/19/2003 Bug 2788302 End
1021
1022 END MSC_ATP_FUNC;
[Home] [Help]