1 PACKAGE BODY MSC_AATP_FUNC AS
2 /* $Header: MSCFAATB.pls 120.1.12010000.2 2008/08/25 10:40:06 sbnaik ship $ */
3 G_PKG_NAME CONSTANT VARCHAR2(30) := 'MSC_AATP_FUNC';
4
5 -- INFINITE_NUMBER CONSTANT NUMBER := 1.0e+10;
6
7 -- demand type
8 DEMAND_SALES_ORDER_MDS CONSTANT INTEGER := 6;
9 DEMAND_FORECAST CONSTANT INTEGER := 7;
10 DEMAND_MANUAL CONSTANT INTEGER := 8;
11 DEMAND_OTHER CONSTANT INTEGER := 9;
12 DEMAND_HARD_RESERVE CONSTANT INTEGER := 10;
13 DEMND_MDS_IND CONSTANT INTEGER := 11;
14 DEMND_MPS_COMPILE CONSTANT INTEGER := 12;
15 FORECAST CONSTANT INTEGER := 29;
16 DEMAND_SALES_ORDER CONSTANT INTEGER := 30;
17
18 G_HIERARCHY_PROFILE NUMBER := NVL(FND_PROFILE.VALUE('MSC_CLASS_HIERARCHY'), 2);
19
20
21 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('MSC_ATP_DEBUG'), 'N');
22
23 FUNCTION Get_Item_Demand_Alloc_Percent(
24 p_plan_id IN NUMBER,
25 p_demand_id IN NUMBER,
26 p_demand_date IN DATE,
27 p_assembly_item_id IN NUMBER,
28 p_source_org_id IN NUMBER,
29 p_inventory_item_id IN NUMBER,
30 p_org_id IN NUMBER,
31 p_instance_id IN NUMBER,
32 p_origination_type IN NUMBER,
33 p_record_class IN VARCHAR2,
34 p_demand_class IN VARCHAR2,
35 p_level_id IN NUMBER)
36 RETURN NUMBER
37 IS
38 l_alloc_percent NUMBER;
39 l_alloc_rule NUMBER;
40 l_default_atp_rule_id NUMBER;
41 l_calendar_code VARCHAR2(14);
42 l_calendar_exception_set_id NUMBER;
43 l_default_demand_class VARCHAR2(34);
44 l_org_code VARCHAR2(7);
45 BEGIN
46
47 IF PG_DEBUG in ('Y', 'C') THEN
48 msc_sch_wb.atp_debug('***** Get_Item_Demand_Alloc_Percent *****');
49 msc_sch_wb.atp_debug('p_plan_id =' || p_plan_id);
50 msc_sch_wb.atp_debug('p_demand_id =' || p_demand_id);
51 msc_sch_wb.atp_debug('p_demand_date =' || p_demand_date );
52 msc_sch_wb.atp_debug('p_assembly_item_id =' || p_assembly_item_id);
53 msc_sch_wb.atp_debug('p_source_org_id =' || p_source_org_id);
54 msc_sch_wb.atp_debug('p_inventory_item_id =' || p_inventory_item_id);
55 msc_sch_wb.atp_debug('p_org_id =' || p_org_id);
56 msc_sch_wb.atp_debug('p_instance_id =' || p_instance_id);
57 msc_sch_wb.atp_debug('p_origination_type =' || p_origination_type );
58 msc_sch_wb.atp_debug('p_demand_class =' || p_demand_class);
59 msc_sch_wb.atp_debug('p_record_class =' || p_record_class);
60 END IF;
61
62 l_alloc_rule := MSC_AATP_FUNC.Get_DC_Alloc_Percent(p_instance_id, p_inventory_item_id, p_org_id,
63 null, null, p_demand_class, p_demand_date);
64 IF PG_DEBUG in ('Y', 'C') THEN
65 msc_sch_wb.atp_debug('l_alloc_rule : '||l_alloc_rule);
66 END IF;
67
68 -- dsting #1625776
69 -- If no rule exists for the demand on the demand date, and the demand date is > sysdate
70 -- ie the demand is past due and the demand is a sales order,
71 -- then honor the rule for that item on sysdate
72 IF l_alloc_rule IS NULL AND
73 p_demand_date < trunc(sysdate) AND
74 p_origination_type in (DEMAND_SALES_ORDER, DEMAND_SALES_ORDER_MDS)
75 THEN
76 l_alloc_rule := MSC_AATP_FUNC.Get_DC_Alloc_Percent(p_instance_id, p_inventory_item_id, p_org_id,
77 null, null, p_demand_class,sysdate);
78 END IF;
79
80 IF l_alloc_rule IS NULL THEN
81 l_alloc_percent := 1;
82 ELSIF p_record_class <> p_demand_class THEN
83 l_alloc_percent := 0;
84 --rajjain changed the condition as an internal sales order is also a dependent demand
85 ELSIF ( p_origination_type = DEMAND_HARD_RESERVE or (p_origination_type in (DEMAND_SALES_ORDER_MDS, DEMAND_SALES_ORDER) and
86 (p_source_org_id in (-23453, p_org_id)
87 -- bug 2783787 (ssurendr) - independent demands were getting considered as dependent because of wrong IN expresession
88 or p_source_org_id IS NULL))) THEN
89
90 -- mps and mds we may need to do something different, let's deal with
91 -- them later.
92
93 -- if the demand is the independent demand, we join back to see the demand
94 -- class. if the demand is the dependent demand, we see how we allocate for
95 -- the parent supply, we do the same thing to it's dependent demand.
96
97 IF PG_DEBUG in ('Y', 'C') THEN
98 msc_sch_wb.atp_debug('this is indep demand, origination_type = '||p_origination_type);
99 END IF;
100 /*
101 bug 2783787 (ssurendr) - null demand_class in request should be regarded as "OTHER"
102 MSC_ATP_PROC.get_org_default_info(p_instance_id, p_org_id, l_default_atp_rule_id,
103 l_calendar_code, l_calendar_exception_set_id, l_default_demand_class,
104 l_org_code);
105
106 IF PG_DEBUG in ('Y', 'C') THEN
107 msc_sch_wb.atp_debug('l_default_atp_rule_id='||l_default_atp_rule_id);
108 msc_sch_wb.atp_debug('l_calendar_code='||l_calendar_code);
109 msc_sch_wb.atp_debug('l_calendar_exception_set_id'||l_calendar_exception_set_id);
110 msc_sch_wb.atp_debug('l_default_demand_class'||l_default_demand_class);
111 msc_sch_wb.atp_debug('l_org_code'||l_org_code);
112 END IF;
113 */
114
115 BEGIN
116
117 -- Changed as per discussion with Christine.
118 IF (p_origination_type = DEMAND_SALES_ORDER) OR
119 (p_origination_type = DEMAND_SALES_ORDER_MDS) THEN
120
121 IF G_HIERARCHY_PROFILE = 1 THEN
122 -- demand class
123 select 1
124 into l_alloc_percent
125 from msc_demands d
126 --5027568
127 where decode(d.origination_type, -100, 30, d.origination_type) in (6, 30)--DEMAND_SALES_ORDER
128 and d.plan_id = p_plan_id
129 and d.demand_id = p_demand_id
130 and NVL(d.demand_class,'-1') = p_demand_class;
131
132 /* bug 2783787 - demand_class is always available in msc_demands
133 no need to look in msc_sales_orders
134 select 1
135 into l_alloc_percent
136 from msc_sales_orders so,
137 msc_demands d
138 where d.origination_type in (6, 30) --DEMAND_SALES_ORDER
139 and d.plan_id = p_plan_id
140 and d.demand_id = p_demand_id
141 and d.reservation_id = so.demand_id
142 and so.sr_instance_id= d.sr_instance_id
143 and so.organization_id= d.organization_id
144 and so.inventory_item_id= d.inventory_item_id
145 --bug 2424357: treat others as -1
146 --and NVL(so.demand_class, NVL(l_default_demand_class,'@@@')) = p_demand_class
147 and NVL(so.demand_class, NVL(l_default_demand_class,'-1')) = p_demand_class
148 and so.parent_demand_id is null;*/
149
150 ELSIF G_HIERARCHY_PROFILE = 2 THEN
151 select 1
152 into l_alloc_percent
153 from msc_demands d
154 where d.plan_id = p_plan_id
155 and d.demand_id = p_demand_id
156 and MSC_AATP_FUNC.get_hierarchy_demand_class(d.customer_id, d.ship_to_site_id,d.inventory_item_id, d.organization_id, d.sr_instance_id, p_demand_date, p_level_id, null) = p_demand_class;
157
158
159 END IF; -- IF G_HIERARCHY_PROFILE = 1 THEN
160
161 ELSIF p_origination_type = DEMAND_HARD_RESERVE THEN
162
163 -- It is assumed that we shall be deducting any hard reservations before
164 -- doing allocations. For example if total supply is 100 and there is a
165 -- hard reservation of 20 (irrespective of the demand class), we shall be
166 -- allocating only the balance (100 - 20 = 80) based on allocation rule of
167 -- the requested demand class.
168
169 l_alloc_percent := MSC_AATP_FUNC.Get_DC_Alloc_Percent(
170 p_instance_id, p_assembly_item_id, p_org_id,
171 null, null, p_demand_class, p_demand_date);
172 END IF;
173
174 -- work around for bug 1403859
175 -- As per Raghvan we need to also add condition for parent_demand_id
176 -- as null on msc_sales_orders to avoid multiple rows - Bug # 1405568
177
178 /*
179 SELECT 1
180 INTO l_alloc_percent
181 FROM msc_sales_orders so,
182 msc_demands d
183 WHERE d.demand_id = p_demand_id
184 AND d.plan_id = p_plan_id
185 AND so.demand_id = d.reservation_id
186 AND so.sr_instance_id = d.sr_instance_id
187 AND so.inventory_item_id = d.inventory_item_id
188 AND so.organization_id = d.organization_id
189 AND so.parent_demand_id is null
190 AND NVL(so.demand_class, NVL(l_default_demand_class,'@@@')) = p_demand_class;
191 */
192
193
194 EXCEPTION
195 when no_data_found then
196 l_alloc_percent := 0.0;
197 when others then
198 l_alloc_percent := NULL;
199 IF PG_DEBUG in ('Y', 'C') THEN
200 msc_sch_wb.atp_debug('Exception in Get_Item_Demand_Alloc_Percent');
201 END IF;
202 END;
203
204 IF PG_DEBUG in ('Y', 'C') THEN
205 msc_sch_wb.atp_debug('before leave the if, l_alloc_percent = '|| l_alloc_percent);
206 END IF;
207
208 ELSE
209
210 -- this is dependent demand, we see how we alocate the parent supply,
211 -- and do the same thing for it's demand
212
213 IF PG_DEBUG in ('Y', 'C') THEN
214 msc_sch_wb.atp_debug('this is dependent demand');
215 msc_sch_wb.atp_debug('Get_Item_Demand_Alloc_Percent: ' || 'G_TIME_PHASED_PF_ENABLED := ' || MSC_ATP_PVT.G_TIME_PHASED_PF_ENABLED);
216 END IF;
217 -- Check if there is an allocation rule defined at component level.
218 -- If no rule at component level, consider every demand and supply, that means
219 -- consider allocation percent to be 1 (100%), else check if there is a rule at
220 -- parent level and use that rule.
221 /*
222 l_alloc_percent := MSC_AATP_FUNC.Get_DC_Alloc_Percent(
223 p_instance_id, p_inventory_item_id, p_org_id,
224 null, null, p_demand_class, p_demand_date);
225
226 IF PG_DEBUG in ('Y', 'C') THEN
227 msc_sch_wb.atp_debug('before entering the parent if, l_alloc_percent = '||l_alloc_percent);
228 END IF;
229
230 IF l_alloc_percent IS NOT NULL THEN
231 */
232
233 /* To support new logic for dependent demands allocation in time phased PF rule based AATP scenarios
234 As per the new logic for dependent demand allocation use allocation rule for component only if
235 component is time phased PF ATP enabled*/
236 IF MSC_ATP_PVT.G_TIME_PHASED_PF_ENABLED = 'Y' THEN
237 l_alloc_percent := l_alloc_rule;
238 ELSE
239 /* To support new logic for dependent demands allocation in time phased PF rule based AATP scenarios
240 As per the new logic for we don't time phased ATP allocation logic at assembly. So we always pass
241 p_assembly_item_id irrespective of whether assembly item is time phased PF enabled or not*/
242 l_alloc_percent := NVL(MSC_AATP_FUNC.Get_DC_Alloc_Percent(
243 p_instance_id, p_assembly_item_id,
244 NVL(p_source_org_id, p_org_id),
245 null, null,p_demand_class, p_demand_date), l_alloc_rule);
246 END IF;
247
248 IF PG_DEBUG in ('Y', 'C') THEN
249 msc_sch_wb.atp_debug('before leaving the parent if, l_alloc_percent = '||l_alloc_percent);
250 END IF;
251 /*
252 ELSE
253 l_alloc_percent := 1;
254 END IF;
255 */
256
257 IF PG_DEBUG in ('Y', 'C') THEN
258 msc_sch_wb.atp_debug('before leave the if, l_alloc_percent = '||l_alloc_percent);
259 END IF;
260
261 END IF;
262
263 IF PG_DEBUG in ('Y', 'C') THEN
264 msc_sch_wb.atp_debug('before returning, l_alloc_percent = '||l_alloc_percent);
265 END IF;
266 return(l_alloc_percent);
267
268 END Get_Item_Demand_Alloc_Percent;
269
270 FUNCTION Get_DC_Alloc_Percent(
271 p_instance_id IN NUMBER,
272 p_inv_item_id IN NUMBER,
273 p_org_id IN NUMBER,
274 p_dept_id IN NUMBER,
275 p_res_id IN NUMBER,
276 p_demand_class IN VARCHAR2,
277 p_request_date IN DATE)
278 RETURN NUMBER
279 IS
280 l_alloc_percent NUMBER := 0.0;
281 l_rule_name VARCHAR2(30);
282 l_time_phase NUMBER;
283 BEGIN
284 IF PG_DEBUG in ('Y', 'C') THEN
285 msc_sch_wb.atp_debug('***** Get_DC_Alloc_Percent *****');
286 msc_sch_wb.atp_debug('p_instance_id =' || p_instance_id);
287 msc_sch_wb.atp_debug('p_inv_item_id =' || p_inv_item_id);
288 msc_sch_wb.atp_debug('p_org_id =' || p_org_id);
289 msc_sch_wb.atp_debug('p_dept_id =' || p_dept_id);
290 msc_sch_wb.atp_debug('p_res_id =' || p_res_id);
291 msc_sch_wb.atp_debug('p_demand_class =' || p_demand_class);
292 msc_sch_wb.atp_debug('p_request_date =' || p_request_date );
293 END IF;
294
295 --IF p_inv_item_id is not null THEN placed down
296 -- Get the allocation percent for the item/demand class. If no rule found,
297 -- check if a rule on the specified date exists for any demand class
298 -- for the specific item, take allocation percentage as NULL.
299 -- Though we will treat NULL as 1, but we need to differentiate them
300 -- so as to group demands/ supplies by demand classes. - ngoel 8/31/2000.
301
302 /*
303 SELECT allocation_percent/100
304 INTO l_alloc_percent
305 FROM msc_item_hierarchy_mv
306 WHERE demand_class = p_demand_class
307 AND inventory_item_id = p_inv_item_id
308 AND organization_id = p_org_id
309 AND sr_instance_id = p_instance_id
310 AND p_request_date between effective_date and disable_date;
311 */
312 -- Modified by NGOEL on 2/23/2001 as there may be more than 1 rule assigned
313 -- to an item/org/instance combinantion at a given level based on time phase.
314 --SELECT distinct allocation_rule_name
315 /* Assumption is that in time phased ATP scenarios Aggregate time fence
316 will be more than the plan run frequency */
317 --code commented to get allocation percent
318 --bug3099066
319 --BEGIN --bug3333114
320 IF p_inv_item_id is not null THEN
321 IF G_HIERARCHY_PROFILE = 1 THEN ---bug3099066
322 BEGIN ---Bug 3099066
323 IF PG_DEBUG in ('Y', 'C') THEN
324 msc_sch_wb.atp_debug('Get the Allocation on Request Date');
325 END IF;
326
327 SELECT allocation_percent/100
328 INTO l_alloc_percent
329 FROM msc_item_hierarchy_mv
330 WHERE inventory_item_id = p_inv_item_id
331 AND organization_id = p_org_id
332 AND sr_instance_id = p_instance_id
333 AND demand_class = p_demand_class
334 AND level_id = -1
335 AND p_request_date between effective_date and disable_date;
336 EXCEPTION
337 WHEN NO_DATA_FOUND THEN
338 IF PG_DEBUG in ('Y', 'C') THEN
339 msc_sch_wb.atp_debug('Inside Exception Get the Allocation on Sysdate');
340 END IF;
341 IF (p_request_date < trunc(sysdate)) THEN
342 IF PG_DEBUG in ('Y', 'C') THEN
343 msc_sch_wb.atp_debug('Inside IF of Exception');
344 END IF;
345
346 BEGIN
347
348 SELECT allocation_percent/100
349 INTO l_alloc_percent
350 FROM msc_item_hierarchy_mv
351 WHERE inventory_item_id = p_inv_item_id
352 AND organization_id = p_org_id
353 AND sr_instance_id = p_instance_id
354 AND demand_class = p_demand_class
355 AND level_id = -1
356 AND trunc(sysdate) between effective_date and disable_date;
357
358 EXCEPTION
359
360 WHEN NO_DATA_FOUND THEN
361 SELECT DECODE(count(allocation_percent), 0, NULL, 0)
362 INTO l_alloc_percent
363 FROM msc_item_hierarchy_mv
364 WHERE inventory_item_id = p_inv_item_id
365 AND organization_id = p_org_id
366 AND sr_instance_id = p_instance_id
367 AND (p_request_date between effective_date and disable_date
368 OR trunc(sysdate) between effective_date and disable_date)
369 AND level_id = -1;
370 END;
371 ELSE
372 IF PG_DEBUG in ('Y', 'C') THEN
373 msc_sch_wb.atp_debug('Inside Else of Exception');
374 END IF;
375
376 SELECT DECODE(count(allocation_percent), 0, NULL, 0)
377 INTO l_alloc_percent
378 FROM msc_item_hierarchy_mv
379 WHERE inventory_item_id = p_inv_item_id
380 AND organization_id = p_org_id
381 AND sr_instance_id = p_instance_id
382 AND p_request_date between effective_date and disable_date
383 AND level_id = -1;
384 END IF;
385 END;
386 ELSE
387 BEGIN ---Bug 3099066
388 IF PG_DEBUG in ('Y', 'C') THEN
389 msc_sch_wb.atp_debug('Get the Allocation on Request Date:2');
390 END IF;
391
392 SELECT allocation_percent/100
393 INTO l_alloc_percent
394 FROM msc_item_hierarchy_mv
395 WHERE inventory_item_id = p_inv_item_id
396 AND organization_id = p_org_id
397 AND sr_instance_id = p_instance_id
398 AND demand_class = p_demand_class
399 AND level_id <> -1
400 AND p_request_date between effective_date and disable_date;
401 EXCEPTION
402
403 WHEN NO_DATA_FOUND THEN
404 IF PG_DEBUG in ('Y', 'C') THEN
405 msc_sch_wb.atp_debug('Get the Allocation on Sysdate:2');
406 END IF;
407
408 IF (p_request_date < trunc(sysdate)) THEN
409 BEGIN
410
411 SELECT allocation_percent/100
412 INTO l_alloc_percent
413 FROM msc_item_hierarchy_mv
414 WHERE inventory_item_id = p_inv_item_id
415 AND organization_id = p_org_id
416 AND sr_instance_id = p_instance_id
417 AND demand_class = p_demand_class
418 AND level_id <> -1
419 AND trunc(sysdate) between effective_date and disable_date;
420 EXCEPTION
421
422 WHEN NO_DATA_FOUND THEN
423
424 SELECT DECODE(count(allocation_percent), 0, NULL, 0)
425 INTO l_alloc_percent
426 FROM msc_item_hierarchy_mv
427 WHERE inventory_item_id = p_inv_item_id
428 AND organization_id = p_org_id
429 AND sr_instance_id = p_instance_id
430 AND (p_request_date between effective_date and disable_date
431 OR trunc(sysdate) between effective_date and disable_date)
432 AND level_id <> -1;
433 END;
434 ELSE
435 SELECT DECODE(count(allocation_percent), 0, NULL, 0)
436 INTO l_alloc_percent
437 FROM msc_item_hierarchy_mv
438 WHERE inventory_item_id = p_inv_item_id
439 AND organization_id = p_org_id
440 AND sr_instance_id = p_instance_id
441 AND p_request_date between effective_date and disable_date
442 AND level_id <> -1;
443 END IF;
444 END;
445 END IF;
446 --END; --bug3333114
447 --old code code commented to get allocation percent
448 --for the dept/res/demand class bug3333114
449 ELSE
450 IF G_HIERARCHY_PROFILE = 1 THEN ---bug3333114 start
451 BEGIN
452 IF PG_DEBUG in ('Y', 'C') THEN
453 msc_sch_wb.atp_debug('Get the Allocation on Request Date:1');
454 END IF;
455
456 SELECT allocation_percent/100
457 INTO l_alloc_percent
458 FROM msc_resource_hierarchy_mv
459 WHERE demand_class = p_demand_class
460 AND department_id = p_dept_id
461 AND resource_id = p_res_id
462 AND organization_id = p_org_id
463 AND sr_instance_id = p_instance_id
464 AND level_id = -1
465 AND p_request_date between effective_date and disable_date;
466
467 EXCEPTION
468 WHEN NO_DATA_FOUND THEN
469 IF PG_DEBUG in ('Y', 'C') THEN
470 msc_sch_wb.atp_debug('Get the Allocation on Sysdate:1');
471 END IF;
472 IF (p_request_date < trunc(sysdate)) THEN
473 IF PG_DEBUG in ('Y', 'C') THEN
474 msc_sch_wb.atp_debug('Inside IF');
475 END IF;
476 BEGIN
477 SELECT allocation_percent/100
478 INTO l_alloc_percent
479 FROM msc_resource_hierarchy_mv
480 WHERE demand_class = p_demand_class
481 AND department_id = p_dept_id
482 AND resource_id = p_res_id
483 AND organization_id = p_org_id
484 AND sr_instance_id = p_instance_id
485 AND level_id = -1
486 AND trunc(sysdate) between effective_date and disable_date;
487 EXCEPTION
488 WHEN NO_DATA_FOUND THEN
489 SELECT DECODE(count(allocation_percent), 0, NULL, 0)
490 INTO l_alloc_percent
491 FROM msc_resource_hierarchy_mv
492 WHERE department_id = p_dept_id
493 AND resource_id = p_res_id
494 AND organization_id = p_org_id
495 AND sr_instance_id = p_instance_id
496 AND (p_request_date between effective_date and disable_date
497 OR trunc(sysdate) between effective_date and disable_date)
498 AND level_id = -1;
499 END;
500 ELSE
501 IF PG_DEBUG in ('Y', 'C') THEN
502 msc_sch_wb.atp_debug('Inside Else');
503 END IF;
504
505 SELECT DECODE(count(allocation_percent), 0, NULL, 0)
506 INTO l_alloc_percent
507 FROM msc_resource_hierarchy_mv
508 WHERE department_id = p_dept_id
509 AND resource_id = p_res_id
510 AND organization_id = p_org_id
511 AND sr_instance_id = p_instance_id
512 AND p_request_date between effective_date and disable_date
513 AND level_id = -1;
514 END IF;
515 END;
516 ELSE
517 BEGIN
518 IF PG_DEBUG in ('Y', 'C') THEN
519 msc_sch_wb.atp_debug('Get the Allocation on Request Date:2');
520 END IF;
521 SELECT allocation_percent/100
522 INTO l_alloc_percent
523 FROM msc_resource_hierarchy_mv
524 WHERE demand_class = p_demand_class
525 AND department_id = p_dept_id
526 AND resource_id = p_res_id
527 AND organization_id = p_org_id
528 AND sr_instance_id = p_instance_id
529 AND level_id <> -1
530 AND p_request_date between effective_date and disable_date;
531 EXCEPTION
532 WHEN NO_DATA_FOUND THEN
533 IF PG_DEBUG in ('Y', 'C') THEN
534 msc_sch_wb.atp_debug('Get the Allocation on Sysdate:2');
535 END IF;
536
537 IF (p_request_date < trunc(sysdate)) THEN
538
539 BEGIN
540 SELECT allocation_percent/100
541 INTO l_alloc_percent
542 FROM msc_resource_hierarchy_mv
543 WHERE demand_class = p_demand_class
544 AND department_id = p_dept_id
545 AND resource_id = p_res_id
546 AND organization_id = p_org_id
547 AND sr_instance_id = p_instance_id
548 AND level_id <> -1
549 AND trunc(sysdate) between effective_date and disable_date;
550 EXCEPTION
551 WHEN NO_DATA_FOUND THEN
552 SELECT DECODE(count(allocation_percent), 0, NULL, 0)
553 INTO l_alloc_percent
554 FROM msc_resource_hierarchy_mv
555 WHERE department_id = p_dept_id
556 AND resource_id = p_res_id
557 AND organization_id = p_org_id
558 AND sr_instance_id = p_instance_id
559 AND (p_request_date between effective_date and disable_date
560 OR trunc(sysdate) between effective_date and disable_date)
561 AND level_id <> -1;
562 END;
563 ELSE
564 SELECT DECODE(count(allocation_percent), 0, NULL, 0)
565 INTO l_alloc_percent
566 FROM msc_resource_hierarchy_mv
567 WHERE department_id = p_dept_id
568 AND resource_id = p_res_id
569 AND organization_id = p_org_id
570 AND sr_instance_id = p_instance_id
571 AND p_request_date between effective_date and disable_date
572 AND level_id <> -1;
573 END IF;
574 END;
575 END IF;
576 END IF; --bug3333114 end
577 IF PG_DEBUG in ('Y', 'C') THEN
578 msc_sch_wb.atp_debug('Call to alloc:' || to_char(l_alloc_percent));
579 END IF;
580 return (l_alloc_percent);
581
582 EXCEPTION
583 WHEN OTHERS THEN
584 IF PG_DEBUG in ('Y', 'C') THEN
585 msc_sch_wb.atp_debug('Error code:' || to_char(sqlcode));
586 END IF;
587 return(0.0);
588 END Get_DC_Alloc_Percent;
589
590 FUNCTION Get_Res_Demand_Alloc_Percent(
591 p_demand_date IN DATE,
592 p_assembly_item_id IN NUMBER,
593 p_org_id IN NUMBER,
594 p_instance_id IN NUMBER,
595 p_dept_id IN NUMBER,
596 p_res_id IN NUMBER,
597 p_record_class IN VARCHAR2,
598 p_demand_class IN VARCHAR2)
599 RETURN NUMBER
600 IS
601 l_alloc_rule NUMBER;
602 l_alloc_percent NUMBER;
603 BEGIN
604
605 IF PG_DEBUG in ('Y', 'C') THEN
606 msc_sch_wb.atp_debug('******** Get_Res_demand_Alloc_Percent ********');
607 msc_sch_wb.atp_debug('Get_Res_Demand_Alloc_Percent: ' || 'p_demand_date =' || p_demand_date );
608 msc_sch_wb.atp_debug('Get_Res_Demand_Alloc_Percent: ' || 'p_assembly_item_id =' || p_assembly_item_id);
609 msc_sch_wb.atp_debug('Get_Res_Demand_Alloc_Percent: ' || 'p_org_id =' || p_org_id);
610 msc_sch_wb.atp_debug('Get_Res_Demand_Alloc_Percent: ' || 'p_instance_id =' || p_instance_id);
611 msc_sch_wb.atp_debug('Get_Res_Demand_Alloc_Percent: ' || 'p_dept_id =' || p_dept_id);
612 msc_sch_wb.atp_debug('Get_Res_Demand_Alloc_Percent: ' || 'p_res_id =' || p_res_id);
613 msc_sch_wb.atp_debug('Get_Res_Demand_Alloc_Percent: ' || 'p_record_class =' || p_record_class);
614 msc_sch_wb.atp_debug('Get_Res_Demand_Alloc_Percent: ' || 'p_demand_class =' || p_demand_class);
615 END IF;
616
617 --Check if there is any allocation rule for the resource
618
619 l_alloc_rule := MSC_AATP_FUNC.Get_DC_Alloc_Percent(p_instance_id, null, p_org_id, p_dept_id,
620 p_res_id, p_demand_class, p_demand_date);
621
622 IF PG_DEBUG in ('Y', 'C') THEN
623 msc_sch_wb.atp_debug('Get_Res_Demand_Alloc_Percent: ' || 'After resource DC Alloc rule :' ||to_char(l_alloc_rule));
624 END IF;
625 IF l_alloc_rule IS NULL THEN
626 --For Bug # 1409203, if no rule at resource level
627 --every supply demand must be counted.
628 l_alloc_percent := 1;
629 --l_alloc_percent := NULL;
630 ELSIF p_record_class <> p_demand_class THEN
631 l_alloc_percent := 0;
632 ELSE
633 -- this is dependent demand, we see how we alocate the parent supply,
634 -- and do the same thing for its demand
635 l_alloc_percent := NVL(MSC_AATP_FUNC.Get_DC_Alloc_Percent(p_instance_id, p_assembly_item_id, p_org_id,
636 null, null, p_demand_class, p_demand_date), l_alloc_rule);
637 IF PG_DEBUG in ('Y', 'C') THEN
638 msc_sch_wb.atp_debug('Get_Res_Demand_Alloc_Percent: ' || 'After parent item DC Alloc Percent :' ||to_char(l_alloc_percent));
639 END IF;
640 END IF;
641 /*
642 -- If no allocation percentage specified for any demand classes for
643 -- specified time period, get the allocation percentage for department
644 -- resource.
645 IF l_alloc_percent IS NULL THEN
646 l_alloc_percent := MSC_AATP_FUNC.Get_DC_Alloc_Percent(p_instance_id, null, p_org_id,
647 p_dept_id, p_res_id, p_demand_class, p_demand_date);
648 END IF;
649 */
650 IF PG_DEBUG in ('Y', 'C') THEN
651 msc_sch_wb.atp_debug('Get_Res_Demand_Alloc_Percent: ' || 'Before Leaving Res Demand Alloc Percent :' ||to_char(l_alloc_percent));
652 END IF;
653 return (l_alloc_percent);
654
655 END GET_RES_DEMAND_ALLOC_PERCENT;
656
657
658 FUNCTION Get_Hierarchy_Demand_Class(
659 p_partner_id IN NUMBER,
660 p_partner_site_id IN NUMBER,
661 p_inventory_item_id IN NUMBER,
662 p_organization_id IN NUMBER,
663 p_instance_id IN NUMBER,
664 p_request_date IN DATE,
665 p_level_id IN NUMBER,
666 p_demand_class IN VARCHAR2)
667 RETURN VARCHAR2
668 IS
669 l_hierarchy_demand_class VARCHAR2(200);
670 l_partner_id NUMBER;
671 l_partner_site_id NUMBER;
672 l_class_code VARCHAR2(30);
673 l_inv_item_id NUMBER;
674 l_time_phase_id NUMBER;
675 l_temp NUMBER := 0;
676 BEGIN
677 IF PG_DEBUG in ('Y', 'C') THEN
678 msc_sch_wb.atp_debug('BEGIN Get_Hierarchy_Demand_Class');
679 msc_sch_wb.atp_debug('Get_Hierarchy_Demand_Class: ' || 'p_inventory_item_id : ' ||p_inventory_item_id);
680 msc_sch_wb.atp_debug('Get_Hierarchy_Demand_Class: ' || 'p_partner_id : ' ||p_partner_id);
681 msc_sch_wb.atp_debug('Get_Hierarchy_Demand_Class: ' || 'p_partner_site_id : ' ||p_partner_site_id);
682 msc_sch_wb.atp_debug('Get_Hierarchy_Demand_Class: ' || 'p_request_date : ' ||p_request_date);
683 msc_sch_wb.atp_debug('Get_Hierarchy_Demand_Class: ' || 'p_organization_id : ' ||p_organization_id);
684 msc_sch_wb.atp_debug('Get_Hierarchy_Demand_Class: ' || 'p_instance_id : ' ||p_instance_id);
685 msc_sch_wb.atp_debug('Get_Hierarchy_Demand_Class: ' || 'p_level_id : ' ||p_level_id);
686 msc_sch_wb.atp_debug('Get_Hierarchy_Demand_Class: ' || 'p_demand_class : ' ||p_demand_class);
687 END IF;
688
689
690 -- now this function supports 2 functionalities.
691 -- CASE 1: to find the demand class for an incoming request
692 -- G_Hierarchy_Profile is 1 .
693 --
694 -- CASE 2: to find the hierarchy demand class at a particular level
695 -- p_level_id is not null
696 G_HIERARCHY_PROFILE := NVL(FND_PROFILE.VALUE('MSC_CLASS_HIERARCHY'), 2);
697
698 IF (G_HIERARCHY_PROFILE = 1) THEN
699 -- we are doing the demand class
700 -- this would happen only in case 1
701 l_hierarchy_demand_class := p_demand_class;
702
703 IF (MSC_ATP_PVT.G_ALLOCATED_ATP = 'Y') THEN
704
705 BEGIN
706 --diag_atp
707 SELECT distinct time_phase_id, allocation_rule_name
708 INTO l_time_phase_id, MSC_ATP_PVT.G_ALLOCATION_RULE_NAME
709 FROM msc_item_hierarchy_mv
710 WHERE inventory_item_id = p_inventory_item_id
711 AND organization_id = p_organization_id
712 AND sr_instance_id = p_instance_id
713 AND greatest(p_request_date,trunc(sysdate)) between effective_date and disable_date; --bug3099066
714 EXCEPTION
715 WHEN NO_DATA_FOUND THEN
716 l_time_phase_id := NULL;
717 MSC_ATP_PVT.G_ALLOCATION_RULE_NAME := null;
718 END;
719
720 IF PG_DEBUG in ('Y', 'C') THEN
721 msc_sch_wb.atp_debug('Get_Hierarchy_Demand_Class: ' || 'l_time_phase_id '||l_time_phase_id);
722 END IF;
723
724 IF l_time_phase_id IS NOT NULL THEN
725
726 BEGIN
727 SELECT distinct demand_class
728 INTO l_hierarchy_demand_class
729 FROM msc_allocations
730 WHERE demand_class = p_demand_class
731 AND time_phase_id = l_time_phase_id
732 --AND level_id = 1 -- demand_class hence no level_id
733 --AND class = '-1' -- demand_class hence no customer_class
734 ;
735 EXCEPTION
736 WHEN NO_DATA_FOUND THEN
737 l_hierarchy_demand_class := '-1'; -- Return Others
738 END;
739
740 ELSE
741
742 l_hierarchy_demand_class := '-1'; -- Return Others
743
744 END IF; -- IF l_time_phase_id IS NULL
745
746 END IF;
747
748 IF PG_DEBUG in ('Y', 'C') THEN
749 msc_sch_wb.atp_debug('Get_Hierarchy_Demand_Class: ' || 'l_hierarchy_demand_class = '||
750 l_hierarchy_demand_class);
751 END IF;
752
753 ELSIF (G_HIERARCHY_PROFILE = 2) THEN
754 -- we are doing the customer class
755 -- get the local id of the customer and site
756 IF p_partner_id IS NOT NULL THEN
757 BEGIN
758
759 SELECT CUSTOMER_CLASS_CODE
760 INTO l_class_code
761 FROM msc_trading_partners
762 WHERE PARTNER_ID = p_partner_id;
763 EXCEPTION
764 WHEN NO_DATA_FOUND THEN
765 l_class_code := NULL;
766 END;
767 END IF; -- IF p_customer_id IS NOT NULL
768
769 IF PG_DEBUG in ('Y', 'C') THEN
770 msc_sch_wb.atp_debug('Get_Hierarchy_Demand_Class: ' || 'l_class_code '||l_class_code);
771 END IF;
772
773 BEGIN
774 --diag_atp
775 SELECT distinct time_phase_id, allocation_rule_name
776 INTO l_time_phase_id, MSC_ATP_PVT.G_ALLOCATION_RULE_NAME
777 FROM msc_item_hierarchy_mv
778 WHERE inventory_item_id = p_inventory_item_id
779 AND organization_id = p_organization_id
780 AND sr_instance_id = p_instance_id
781 AND greatest(p_request_date,trunc(sysdate)) between effective_date and disable_date; --bug3099066
782 EXCEPTION
783 WHEN NO_DATA_FOUND THEN
784 l_time_phase_id := NULL;
785 --diag_atp
786 MSC_ATP_PVT.G_ALLOCATION_RULE_NAME := null;
787 --bug 2424357: trat others as -1
788 --return '@@@'; -- since there is no time phase id
789 return '-1'; -- since there is no time phase id
790 -- we won't find the dummy demand class
791 END;
792
793 IF PG_DEBUG in ('Y', 'C') THEN
794 msc_sch_wb.atp_debug('Get_Hierarchy_Demand_Class: ' || 'l_time_phase_id '||l_time_phase_id);
795 END IF;
796
797 IF (p_partner_id IS NOT NULL AND p_level_id is null) OR
798 (p_level_id in (2, 3)) THEN -- level 2 + level 3
799
800 IF (p_partner_site_id IS NOT NULL AND p_level_id is null) OR
801 (p_level_id = 3) THEN -- level 3
802 -- level 3
803 -- try to find the match demand class for
804 -- that class_code+partner_id+partner_site_id
805 IF PG_DEBUG in ('Y', 'C') THEN
806 msc_sch_wb.atp_debug('Get_Hierarchy_Demand_Class: ' || 'level 3');
807 END IF;
808
809 BEGIN
810 SELECT distinct demand_class
811 INTO l_hierarchy_demand_class
812 FROM msc_allocations
813 WHERE level_id = 3
814 AND time_phase_id = l_time_phase_id
815 AND class = l_class_code
816 AND partner_id = p_partner_id
817 AND partner_site_id = p_partner_site_id;
818 EXCEPTION
819 WHEN NO_DATA_FOUND THEN
820 -- no match for
821 -- class_code + partner_id+_partner_site_id
822 l_hierarchy_demand_class := null;
823 END;
824
825 IF PG_DEBUG in ('Y', 'C') THEN
826 msc_sch_wb.atp_debug('Get_Hierarchy_Demand_Class: ' || 'level 3, after the first select');
827 msc_sch_wb.atp_debug('Get_Hierarchy_Demand_Class: ' || 'l_hierarchy_demand_class = '||l_hierarchy_demand_class);
828 END IF;
829
830
831 IF l_hierarchy_demand_class IS NULL THEN
832 -- could not find the match demand class
833 -- for class_code+partner_id+partner_site_id
834 -- try to find the match demand class for that
835 -- class_code+partner_id+others
836 IF PG_DEBUG in ('Y', 'C') THEN
837 msc_sch_wb.atp_debug('Get_Hierarchy_Demand_Class: ' || 'level 3, first select fails');
838 END IF;
839
840 BEGIN
841 SELECT distinct demand_class
842 INTO l_hierarchy_demand_class
843 FROM msc_allocations
844 WHERE level_id = 3
845 AND time_phase_id = l_time_phase_id
846 AND class = l_class_code
847 AND partner_id = p_partner_id
848 AND partner_site_id = -1;
849 Exception
850 WHEN NO_DATA_FOUND THEN
851 l_hierarchy_demand_class := null;
852 END;
853
854 IF PG_DEBUG in ('Y', 'C') THEN
855 msc_sch_wb.atp_debug('Get_Hierarchy_Demand_Class: ' || 'level 3, after the second select');
856 msc_sch_wb.atp_debug('Get_Hierarchy_Demand_Class: ' || 'l_hierarchy_demand_class = '||l_hierarchy_demand_class);
857 END IF;
858
859 END IF; -- IF l_hierarchy_demand_class IS NULL
860
861 IF l_hierarchy_demand_class IS NULL THEN
862 -- could not find the match demand class for
863 -- (class_code+partner_id+partner_site_id) and
864 -- (class_code+partner_id+others)
865
866 -- try to find the match demand class for that
867 -- class_code+others+others
868 IF PG_DEBUG in ('Y', 'C') THEN
869 msc_sch_wb.atp_debug('Get_Hierarchy_Demand_Class: ' || 'level 3, second select fails');
870 END IF;
871
872 -- bug 1680773
873 -- before we try class+others+others,
874 -- try to see if we have a the exact class+partner defined.
875 -- if yes, the hierarchy for that class+partner exists
876 -- but it does not cover the site.
877 -- the name should be '@@@' which eventually will
878 -- match to 0 percent.
879 -- if no, then find class+others+others
880 ---bug 2424357: Since we have will always have 'OTHERS' we wouldn't need
881 --- to check the following condition
882 /*SELECT count(*)
883 INTO l_temp
884 FROM msc_allocations
885 WHERE level_id =3
886 AND time_phase_id = l_time_phase_id
887 AND class = l_class_code
888 AND partner_id = p_partner_id;
889
890 IF l_temp = 0 THEN */
891
892 BEGIN
893
894 IF PG_DEBUG in ('Y', 'C') THEN
895 msc_sch_wb.atp_debug('Get_Hierarchy_Demand_Class: ' || 'l_time_phase_id = '||l_time_phase_id);
896 msc_sch_wb.atp_debug('Get_Hierarchy_Demand_Class: ' || 'l_class_code = '||l_class_code);
897 END IF;
898 SELECT distinct demand_class
899 INTO l_hierarchy_demand_class
900 FROM msc_allocations
901 WHERE level_id = 3
902 AND time_phase_id = l_time_phase_id
903 AND class = l_class_code
904 AND partner_id = -1
905 AND partner_site_id = -1;
906 EXCEPTION
907 WHEN NO_DATA_FOUND THEN
908 IF PG_DEBUG in ('Y', 'C') THEN
909 msc_sch_wb.atp_debug('Get_Hierarchy_Demand_Class: ' || 'NO DATA FOUND');
910 END IF;
911 -- no match for
912 -- class_code + other+other
913 l_hierarchy_demand_class := null;
914 END;
915
916 /*ELSE
917 --bug 2424357: trat others as -1
918 --l_hierarchy_demand_class := '@@@';
919 l_hierarchy_demand_class := '-1';
920 END IF; */
921
922
923 IF PG_DEBUG in ('Y', 'C') THEN
924 msc_sch_wb.atp_debug('Get_Hierarchy_Demand_Class: ' || 'level 3, after the third select');
925 msc_sch_wb.atp_debug('Get_Hierarchy_Demand_Class: ' || 'l_hierarchy_demand_class = '||l_hierarchy_demand_class);
926 END IF;
927
928
929 END IF; -- l_hierarchy_demand_class IS NULL THEN
930
931
932 IF l_hierarchy_demand_class IS NULL THEN
933 -- could not find the match demand class for
934 -- (class_code+partner_id+partner_site_id) and
935 -- (class_code+partner_id+others)
936 -- (class_code+others+others)
937
938 -- try to find the match demand class for that
939 -- others+others+others
940
941 -- bug 1680773
942 -- try to see if we have a the exact class defined.
943 -- if yes, the hierarchy for that class does exist
944 -- but does not cover the customer. so
945 -- the name should be '@@@' which eventually will
946 -- match to 0 percent.
947 -- if no, then find others+others+others
948
949 IF PG_DEBUG in ('Y', 'C') THEN
950 msc_sch_wb.atp_debug('Get_Hierarchy_Demand_Class: ' || 'level 3, third select fails');
951 END IF;
952
953 /*SELECT count(*)
954 INTO l_temp
955 FROM msc_allocations
956 WHERE level_id =3
957 AND time_phase_id = l_time_phase_id
958 AND class = l_class_code;
959
960 IF l_temp = 0 THEN
961 */
962
963 BEGIN
964 SELECT distinct demand_class
965 INTO l_hierarchy_demand_class
966 FROM msc_allocations
967 WHERE level_id = 3
968 AND time_phase_id = l_time_phase_id
969 AND class = '-1'
970 AND partner_id = -1
971 AND partner_site_id = -1;
972 Exception
973 WHEN NO_DATA_FOUND THEN
974 --bug 2424357
975 --l_hierarchy_demand_class := null;
976 l_hierarchy_demand_class := '-1';
977 END;
978 /*
979 ELSE
980 --bug 2424357: trat others as -1
981 --l_hierarchy_demand_class := '@@@';
982 l_hierarchy_demand_class := '-1';
983 END IF;
984 */
985 IF PG_DEBUG in ('Y', 'C') THEN
986 msc_sch_wb.atp_debug('Get_Hierarchy_Demand_Class: ' || 'level 3, after the 4th select');
987 msc_sch_wb.atp_debug('Get_Hierarchy_Demand_Class: ' || 'l_hierarchy_demand_class = '||l_hierarchy_demand_class);
988 END IF;
989
990
991 END IF; -- IF l_hierarchy_demand_class IS NULL
992 ELSIF (p_partner_site_id IS NULL AND p_level_id is null) OR
993 (p_level_id = 2) THEN
994
995 IF PG_DEBUG in ('Y', 'C') THEN
996 msc_sch_wb.atp_debug('Get_Hierarchy_Demand_Class: ' || 'level 2');
997 END IF;
998
999 -- level 2
1000 -- try to find the match demand class for
1001 -- that class_code+partner_id
1002 BEGIN
1003 SELECT distinct demand_class
1004 INTO l_hierarchy_demand_class
1005 FROM msc_allocations
1006 WHERE level_id = 2
1007 AND time_phase_id = l_time_phase_id
1008 AND class = l_class_code
1009 AND partner_id = p_partner_id;
1010 EXCEPTION
1011 WHEN NO_DATA_FOUND THEN
1012 -- no match for
1013 -- class_code + partner_id
1014 l_hierarchy_demand_class := null;
1015 END;
1016
1017 IF PG_DEBUG in ('Y', 'C') THEN
1018 msc_sch_wb.atp_debug('Get_Hierarchy_Demand_Class: ' || 'level 2, after the first select');
1019 msc_sch_wb.atp_debug('Get_Hierarchy_Demand_Class: ' || 'l_hierarchy_demand_class = '||l_hierarchy_demand_class);
1020 END IF;
1021
1022 IF l_hierarchy_demand_class IS NULL THEN
1023 -- could not find the match demand class
1024 -- for class_code+partner_id
1025 -- try to find the match demand class for that
1026 -- class_code+others
1027
1028 IF PG_DEBUG in ('Y', 'C') THEN
1029 msc_sch_wb.atp_debug('Get_Hierarchy_Demand_Class: ' || 'level 2, first select fails');
1030 END IF;
1031
1032 BEGIN
1033 SELECT distinct demand_class
1034 INTO l_hierarchy_demand_class
1035 FROM msc_allocations
1036 WHERE level_id = 2
1037 AND time_phase_id = l_time_phase_id
1038 AND class = l_class_code
1039 AND partner_id = -1;
1040 Exception
1041 WHEN NO_DATA_FOUND THEN
1042 l_hierarchy_demand_class := null;
1043 END;
1044 IF PG_DEBUG in ('Y', 'C') THEN
1045 msc_sch_wb.atp_debug('Get_Hierarchy_Demand_Class: ' || 'level 2, after the second select');
1046 msc_sch_wb.atp_debug('Get_Hierarchy_Demand_Class: ' || 'l_hierarchy_demand_class = '||l_hierarchy_demand_class);
1047 END IF;
1048
1049 END IF; -- IF l_hierarchy_demand_class IS NULL
1050 -- bug 1680773
1051 IF l_hierarchy_demand_class IS NULL THEN
1052 -- could not find the match demand class
1053 -- for (class_code+partner_id) and
1054 -- (class_code+others )
1055 -- try to see if we have the exact class defined.
1056 -- if yes, the hierarchy for that class does exist
1057 -- but does not cover the customer. so
1058 -- the name should be '@@@' which eventually will
1059 -- match to 0 percent.
1060 -- if no, then find others+others
1061
1062 IF PG_DEBUG in ('Y', 'C') THEN
1063 msc_sch_wb.atp_debug('Get_Hierarchy_Demand_Class: ' || 'level 2, second select fails');
1064 END IF;
1065 /*
1066 SELECT count(*)
1067 INTO l_temp
1068 FROM msc_allocations
1069 WHERE level_id =2
1070 AND time_phase_id = l_time_phase_id
1071 AND class = l_class_code;
1072
1073 IF l_temp = 0 THEN
1074 */
1075 BEGIN
1076 SELECT distinct demand_class
1077 INTO l_hierarchy_demand_class
1078 FROM msc_allocations
1079 WHERE level_id =2
1080 AND time_phase_id = l_time_phase_id
1081 AND class = '-1'
1082 AND partner_id = -1;
1083 Exception
1084 WHEN NO_DATA_FOUND THEN
1085 -- 2424357: Since this is the last search
1086 -- and we have't found anything. That means
1087 -- this customer belongs to others
1088 --l_hierarchy_demand_class := null;
1089 l_hierarchy_demand_class := '-1';
1090
1091 END;
1092 /*
1093 ELSE
1094 --bug 2424357: trat others as -1
1095 --l_hierarchy_demand_class := '@@@';
1096 l_hierarchy_demand_class := '-1';
1097 END IF;
1098 */
1099 IF PG_DEBUG in ('Y', 'C') THEN
1100 msc_sch_wb.atp_debug('Get_Hierarchy_Demand_Class: ' || 'level 2, after the third select');
1101 msc_sch_wb.atp_debug('Get_Hierarchy_Demand_Class: ' || 'l_hierarchy_demand_class = '||l_hierarchy_demand_class);
1102 END IF;
1103 END IF; -- IF l_hierarchy_demand_class IS NULL
1104
1105 END IF; -- IF (p_partner_site_id IS NOT NULL AND p_level_id is null)
1106 -- OR (p_level_id in (2,3))
1107
1108 ELSIF(p_partner_id IS NULL AND p_level_id is null) OR
1109 (p_level_id = 1) THEN
1110
1111 -- level 1
1112 -- since we do not have the class info which comes from
1113 -- partner_id, assume it is others
1114 -- find the match demand class for others
1115
1116 IF PG_DEBUG in ('Y', 'C') THEN
1117 msc_sch_wb.atp_debug('Get_Hierarchy_Demand_Class: ' || 'level 1');
1118 END IF;
1119 IF p_level_id = 1 THEN
1120 BEGIN
1121 SELECT distinct demand_class
1122 INTO l_hierarchy_demand_class
1123 FROM msc_allocations
1124 WHERE level_id = 1
1125 AND time_phase_id = l_time_phase_id
1126 AND class = l_class_code;
1127 EXCEPTION
1128 WHEN NO_DATA_FOUND THEN
1129 l_hierarchy_demand_class := null;
1130 END;
1131
1132 IF PG_DEBUG in ('Y', 'C') THEN
1133 msc_sch_wb.atp_debug('Get_Hierarchy_Demand_Class: ' || 'level 1, after the first select');
1134 msc_sch_wb.atp_debug('Get_Hierarchy_Demand_Class: ' || 'l_hierarchy_demand_class = '||l_hierarchy_demand_class);
1135 END IF;
1136
1137 END IF;
1138
1139 IF l_hierarchy_demand_class IS NULL THEN
1140
1141 BEGIN
1142 SELECT distinct demand_class
1143 INTO l_hierarchy_demand_class
1144 FROM msc_allocations
1145 WHERE level_id = 1
1146 AND time_phase_id = l_time_phase_id
1147 AND class = '-1';
1148 EXCEPTION
1149 WHEN NO_DATA_FOUND THEN
1150 -- this should not happen
1151 --2424357
1152 l_hierarchy_demand_class := '-1';
1153 END;
1154
1155 END IF; -- IF l_hierarchy_demand_class IS NULL
1156
1157 IF PG_DEBUG in ('Y', 'C') THEN
1158 msc_sch_wb.atp_debug('Get_Hierarchy_Demand_Class: ' || 'level 1, after the second select');
1159 msc_sch_wb.atp_debug('Get_Hierarchy_Demand_Class: ' || 'l_hierarchy_demand_class = '||l_hierarchy_demand_class);
1160 END IF;
1161
1162 END IF; -- IF p_partner_id IS NOT NULL THEN
1163 END IF; -- IF (G_HIERARCHY_PROFILE = 1)
1164
1165 IF PG_DEBUG in ('Y', 'C') THEN
1166 msc_sch_wb.atp_debug('END Get_Hierarchy_Demand_Class');
1167 END IF;
1168 --bug 2424357: trat others as -1
1169 --RETURN NVL(l_hierarchy_demand_class, '@@@'); -- 1680773
1170 RETURN NVL(l_hierarchy_demand_class, '-1'); -- 1680773
1171
1172 END Get_Hierarchy_Demand_Class;
1173
1174
1175 FUNCTION Get_Allowed_Stolen_Percent(
1176 p_instance_id IN NUMBER,
1177 p_inv_item_id IN NUMBER,
1178 p_org_id IN NUMBER,
1179 p_dept_id IN NUMBER,
1180 p_res_id IN NUMBER,
1181 p_demand_class IN VARCHAR2,
1182 p_request_date IN DATE)
1183 RETURN NUMBER
1184 IS
1185 l_alloc_percent NUMBER := 0.0;
1186 l_rule_name VARCHAR2(30);
1187 BEGIN
1188 IF PG_DEBUG in ('Y', 'C') THEN
1189 msc_sch_wb.atp_debug('******** Get_Allowed_Stolen_Percent ********');
1190 msc_sch_wb.atp_debug('Get_Allowed_Stolen_Percent: ' || 'p_instance_id =' || p_instance_id);
1191 msc_sch_wb.atp_debug('Get_Allowed_Stolen_Percent: ' || 'p_inv_item_id =' || p_inv_item_id);
1192 msc_sch_wb.atp_debug('Get_Allowed_Stolen_Percent: ' || 'p_org_id =' || p_org_id);
1193 msc_sch_wb.atp_debug('Get_Allowed_Stolen_Percent: ' || 'p_dept_id =' || p_dept_id);
1194 msc_sch_wb.atp_debug('Get_Allowed_Stolen_Percent: ' || 'p_res_id =' || p_res_id);
1195 msc_sch_wb.atp_debug('Get_Allowed_Stolen_Percent: ' || 'p_demand_class =' || p_demand_class);
1196 msc_sch_wb.atp_debug('Get_Allowed_Stolen_Percent: ' || 'p_request_date =' || p_request_date );
1197 END IF;
1198
1199 IF p_inv_item_id is not null THEN
1200 -- Get the allocation percent for the item/demand class. If no rule found,
1201 -- check if a rule on the specified date exists for any demand class
1202 -- for the specific item, take allocation percentage as NULL.
1203 -- Though we will treat NULL as 1, but we need to differentiate them
1204 -- so as to group demands/ supplies by demand classes. - ngoel 8/31/2000.
1205 BEGIN
1206 -- Changes For bug 2384551 start
1207 IF G_HIERARCHY_PROFILE = 1 THEN
1208
1209 SELECT (allocation_percent- NVL(min_allocation_percent,0))/100
1210 INTO l_alloc_percent
1211 FROM msc_item_hierarchy_mv
1212 WHERE demand_class = p_demand_class
1213 AND inventory_item_id = p_inv_item_id
1214 AND organization_id = p_org_id
1215 AND sr_instance_id = p_instance_id
1216 AND p_request_date between effective_date and disable_date
1217 AND level_id = -1;
1218
1219 ELSE
1220
1221 SELECT (allocation_percent- NVL(min_allocation_percent,0))/100
1222 INTO l_alloc_percent
1223 FROM msc_item_hierarchy_mv
1224 WHERE demand_class = p_demand_class
1225 AND inventory_item_id = p_inv_item_id
1226 AND organization_id = p_org_id
1227 AND sr_instance_id = p_instance_id
1228 AND p_request_date between effective_date and disable_date
1229 AND level_id <> -1;
1230
1231 END IF;
1232 -- Changes For bug 2384551 start
1233
1234 EXCEPTION
1235 WHEN NO_DATA_FOUND THEN
1236 -- Changes For bug 2384551 start
1237 IF G_HIERARCHY_PROFILE = 1 THEN
1238
1239 SELECT DECODE(count(allocation_percent), 0, NULL, 0)
1240 INTO l_alloc_percent
1241 FROM msc_item_hierarchy_mv
1242 WHERE inventory_item_id = p_inv_item_id
1243 AND organization_id = p_org_id
1244 AND sr_instance_id = p_instance_id
1245 AND p_request_date between effective_date and disable_date
1246 AND level_id = -1;
1247
1248 ELSE
1249
1250 SELECT DECODE(count(allocation_percent), 0, NULL, 0)
1251 INTO l_alloc_percent
1252 FROM msc_item_hierarchy_mv
1253 WHERE inventory_item_id = p_inv_item_id
1254 AND organization_id = p_org_id
1255 AND sr_instance_id = p_instance_id
1256 AND p_request_date between effective_date and disable_date
1257 AND level_id <> -1;
1258
1259 END IF;
1260 -- Changes For bug 2384551 end
1261 WHEN OTHERS THEN
1262 l_alloc_percent := NULL;
1263 IF PG_DEBUG in ('Y', 'C') THEN
1264 msc_sch_wb.atp_debug('Exception in Get_Allowed_Stolen_Percent');
1265 END IF;
1266 END;
1267
1268 ELSE
1269 -- Get the allocation percent for the dept/res/demand class
1270 BEGIN
1271 -- Changes For bug 2384551 start
1272 IF G_HIERARCHY_PROFILE = 1 THEN
1273
1274 SELECT (allocation_percent- NVL(min_allocation_percent, 0))/100
1275 INTO l_alloc_percent
1276 FROM msc_resource_hierarchy_mv
1277 WHERE demand_class = p_demand_class
1278 AND department_id = p_dept_id
1279 AND resource_id = p_res_id
1280 AND organization_id = p_org_id
1281 AND sr_instance_id = p_instance_id
1282 AND p_request_date between effective_date and disable_date
1283 AND level_id = -1;
1284
1285 ELSE
1286
1287 SELECT (allocation_percent- NVL(min_allocation_percent, 0))/100
1288 INTO l_alloc_percent
1289 FROM msc_resource_hierarchy_mv
1290 WHERE demand_class = p_demand_class
1291 AND department_id = p_dept_id
1292 AND resource_id = p_res_id
1293 AND organization_id = p_org_id
1294 AND sr_instance_id = p_instance_id
1295 AND p_request_date between effective_date and disable_date
1296 AND level_id <> -1;
1297
1298 END IF;
1299 -- Changes For bug 2384551 end
1300
1301 EXCEPTION
1302 WHEN NO_DATA_FOUND THEN
1303 -- Changes For bug 2384551 start
1304 IF G_HIERARCHY_PROFILE = 1 THEN
1305
1306 SELECT DECODE(count(allocation_percent), 0, NULL, 0)
1307 INTO l_alloc_percent
1308 FROM msc_resource_hierarchy_mv
1309 WHERE department_id = p_dept_id
1310 AND resource_id = p_res_id
1311 AND organization_id = p_org_id
1312 AND sr_instance_id = p_instance_id
1313 AND p_request_date between effective_date and disable_date
1314 AND level_id = -1;
1315
1316 ELSE
1317
1318 SELECT DECODE(count(allocation_percent), 0, NULL, 0)
1319 INTO l_alloc_percent
1320 FROM msc_resource_hierarchy_mv
1321 WHERE department_id = p_dept_id
1322 AND resource_id = p_res_id
1323 AND organization_id = p_org_id
1324 AND sr_instance_id = p_instance_id
1325 AND p_request_date between effective_date and disable_date
1326 AND level_id <> -1;
1327
1328 END IF;
1329 -- Changes For bug 2384551 end
1330 WHEN OTHERS THEN
1331 l_alloc_percent := NULL;
1332 IF PG_DEBUG in ('Y', 'C') THEN
1333 msc_sch_wb.atp_debug('Exception in Get_Allowed_Stolen_Percent');
1334 END IF;
1335 END;
1336
1337 END IF;
1338 IF PG_DEBUG in ('Y', 'C') THEN
1339 msc_sch_wb.atp_debug('Get_Allowed_Stolen_Percent: ' || 'Call to alloc:' || to_char(l_alloc_percent));
1340 END IF;
1341 return (l_alloc_percent);
1342 EXCEPTION
1343 WHEN OTHERS THEN
1344 IF PG_DEBUG in ('Y', 'C') THEN
1345 msc_sch_wb.atp_debug('Get_Allowed_Stolen_Percent: ' || 'Error code:' || to_char(sqlcode));
1346 END IF;
1347 return(0.0);
1348 END Get_Allowed_Stolen_Percent;
1349
1350
1351 FUNCTION Get_Res_Hierarchy_Demand_Class(
1352 p_partner_id IN NUMBER,
1353 p_partner_site_id IN NUMBER,
1354 p_department_id IN NUMBER,
1355 p_resource_id IN NUMBER,
1356 p_organization_id IN NUMBER,
1357 p_instance_id IN NUMBER,
1358 p_request_date IN DATE,
1359 p_level_id IN NUMBER,
1360 p_demand_class IN VARCHAR2)
1361 RETURN VARCHAR2
1362 IS
1363
1364 l_hierarchy_demand_class VARCHAR2(200);
1365 l_partner_id NUMBER;
1366 l_partner_site_id NUMBER;
1367 l_class_code VARCHAR2(30);
1368 l_inv_item_id NUMBER;
1369 l_time_phase_id NUMBER;
1370 l_temp NUMBER := 0;
1371 BEGIN
1372 IF PG_DEBUG in ('Y', 'C') THEN
1373 msc_sch_wb.atp_debug('Get_Res_Hierarchy_Demand_Class: ' || 'BEGIN Get_Hierarchy_Demand_Class');
1374 msc_sch_wb.atp_debug('Get_Res_Hierarchy_Demand_Class: ' || 'p_department_id : ' ||p_department_id);
1375 msc_sch_wb.atp_debug('Get_Res_Hierarchy_Demand_Class: ' || 'p_resource_id : ' ||p_resource_id);
1376 msc_sch_wb.atp_debug('Get_Res_Hierarchy_Demand_Class: ' || 'p_partner_id : ' ||p_partner_id);
1377 msc_sch_wb.atp_debug('Get_Res_Hierarchy_Demand_Class: ' || 'p_partner_site_id : ' ||p_partner_site_id);
1378 msc_sch_wb.atp_debug('Get_Res_Hierarchy_Demand_Class: ' || 'p_request_date : ' ||p_request_date);
1379 msc_sch_wb.atp_debug('Get_Res_Hierarchy_Demand_Class: ' || 'p_organization_id : ' ||p_organization_id);
1380 msc_sch_wb.atp_debug('Get_Res_Hierarchy_Demand_Class: ' || 'p_instance_id : ' ||p_instance_id);
1381 msc_sch_wb.atp_debug('Get_Res_Hierarchy_Demand_Class: ' || 'p_level_id : ' ||p_level_id);
1382 msc_sch_wb.atp_debug('Get_Res_Hierarchy_Demand_Class: ' || 'p_demand_class : ' ||p_demand_class);
1383 END IF;
1384
1385
1386 -- now this function supports 2 functionalities.
1387 -- CASE 1: to find the demand class for an incoming request
1388 -- G_Hierarchy_Profile is 1 .
1389 --
1390 -- CASE 2: to find the hierarchy demand class at a particular level
1391 -- p_level_id is not null
1392 G_HIERARCHY_PROFILE := NVL(FND_PROFILE.VALUE('MSC_CLASS_HIERARCHY'), 2);
1393
1394 IF (G_HIERARCHY_PROFILE = 1) THEN
1395 -- we are doing the demand class
1396 -- this would happen only in case 1
1397 l_hierarchy_demand_class := p_demand_class;
1398
1399 IF (MSC_ATP_PVT.G_ALLOCATED_ATP = 'Y') THEN
1400
1401 BEGIN
1402 --diag_atp
1403 SELECT distinct time_phase_id, allocation_rule_name
1404 INTO l_time_phase_id, MSC_ATP_PVT.G_ALLOCATION_RULE_NAME
1405 FROM msc_resource_hierarchy_mv
1406 WHERE resource_id = p_resource_id
1407 AND department_id = p_department_id
1408 AND organization_id = p_organization_id
1409 AND sr_instance_id = p_instance_id
1410 AND GREATEST(p_request_date,trunc(sysdate)) between effective_date and disable_date; --bug3333114
1411 EXCEPTION
1412 WHEN NO_DATA_FOUND THEN
1413
1414 l_time_phase_id := NULL;
1415 --diag_atp
1416 MSC_ATP_PVT.G_ALLOCATION_RULE_NAME := null;
1417 END;
1418
1419 IF PG_DEBUG in ('Y', 'C') THEN
1420 msc_sch_wb.atp_debug('Get_Res_Hierarchy_Demand_Class: ' || 'l_time_phase_id '||l_time_phase_id);
1421 END IF;
1422
1423 IF l_time_phase_id IS NOT NULL THEN
1424
1425 BEGIN
1426 SELECT distinct demand_class
1427 INTO l_hierarchy_demand_class
1428 FROM msc_allocations
1429 WHERE demand_class = p_demand_class
1430 AND time_phase_id = l_time_phase_id
1431 --AND level_id = 1 -- demand_class hence no level_id
1432 --AND class = '-1' -- demand_class hence no customer_class
1433 ;
1434 EXCEPTION
1435 WHEN NO_DATA_FOUND THEN
1436 l_hierarchy_demand_class := '-1'; -- Return Others
1437 END;
1438
1439 ELSE
1440
1441 l_hierarchy_demand_class := '-1'; -- Return Others
1442
1443 END IF; -- IF l_time_phase_id IS NULL
1444
1445 END IF;
1446
1447 IF PG_DEBUG in ('Y', 'C') THEN
1448 msc_sch_wb.atp_debug('Get_Res_Hierarchy_Demand_Class: ' || 'l_hierarchy_demand_class = '||
1449 l_hierarchy_demand_class);
1450 END IF;
1451
1452 ELSIF (G_HIERARCHY_PROFILE = 2) THEN
1453 -- we are doing the customer class
1454 -- get the local id of the customer and site
1455 IF p_partner_id IS NOT NULL THEN
1456 BEGIN
1457
1458 SELECT CUSTOMER_CLASS_CODE
1459 INTO l_class_code
1460 FROM msc_trading_partners
1461 WHERE PARTNER_ID = p_partner_id;
1462 EXCEPTION
1463 WHEN NO_DATA_FOUND THEN
1464 l_class_code := NULL;
1465 END;
1466 END IF; -- IF p_customer_id IS NOT NULL
1467
1468 IF PG_DEBUG in ('Y', 'C') THEN
1469 msc_sch_wb.atp_debug('Get_Res_Hierarchy_Demand_Class: ' || 'l_class_code '||l_class_code);
1470 END IF;
1471
1472 BEGIN
1473
1474 SELECT distinct time_phase_id, allocation_rule_name
1475 INTO l_time_phase_id, MSC_ATP_PVT.G_ALLOCATION_RULE_NAME
1476 FROM msc_resource_hierarchy_mv
1477 WHERE resource_id = p_resource_id
1478 AND department_id = p_department_id
1479 AND organization_id = p_organization_id
1480 AND sr_instance_id = p_instance_id
1481 AND greatest(p_request_date,trunc(sysdate)) between effective_date and disable_date; --bug3333114
1482 EXCEPTION
1483 WHEN NO_DATA_FOUND THEN
1484 l_time_phase_id := NULL;
1485 --diag_atp
1486 MSC_ATP_PVT.G_ALLOCATION_RULE_NAME := null;
1487 --bug 2424357: trat others as -1
1488 --return '@@@'; -- since there is no time phase id
1489 return '-1'; -- since there is no time phase id
1490 -- we won't find the dummy demand class
1491 END;
1492
1493 IF PG_DEBUG in ('Y', 'C') THEN
1494 msc_sch_wb.atp_debug('Get_Res_Hierarchy_Demand_Class: ' || 'l_time_phase_id '||l_time_phase_id);
1495 END IF;
1496
1497 IF (p_partner_id IS NOT NULL AND p_level_id is null) OR
1498 (p_level_id in (2, 3)) THEN -- level 2 + level 3
1499
1500 IF (p_partner_site_id IS NOT NULL AND p_level_id is null) OR
1501 (p_level_id = 3) THEN -- level 3
1502 -- level 3
1503 -- try to find the match demand class for
1504 -- that class_code+partner_id+partner_site_id
1505 IF PG_DEBUG in ('Y', 'C') THEN
1506 msc_sch_wb.atp_debug('Get_Res_Hierarchy_Demand_Class: ' || 'level 3');
1507 END IF;
1508
1509 BEGIN
1510 SELECT distinct demand_class
1511 INTO l_hierarchy_demand_class
1512 FROM msc_allocations
1513 WHERE level_id = 3
1514 AND time_phase_id = l_time_phase_id
1515 AND class = l_class_code
1516 AND partner_id = p_partner_id
1517 AND partner_site_id = p_partner_site_id;
1518 EXCEPTION
1519 WHEN NO_DATA_FOUND THEN
1520 -- no match for
1521 -- class_code + partner_id+_partner_site_id
1522 l_hierarchy_demand_class := null;
1523 END;
1524
1525 IF PG_DEBUG in ('Y', 'C') THEN
1526 msc_sch_wb.atp_debug('Get_Res_Hierarchy_Demand_Class: ' || 'level 3, after the first select');
1527 msc_sch_wb.atp_debug('Get_Res_Hierarchy_Demand_Class: ' || 'l_hierarchy_demand_class = '||l_hierarchy_demand_class);
1528 END IF;
1529
1530
1531 IF l_hierarchy_demand_class IS NULL THEN
1532 -- could not find the match demand class
1533 -- for class_code+partner_id+partner_site_id
1534 -- try to find the match demand class for that
1535 -- class_code+partner_id+others
1536 IF PG_DEBUG in ('Y', 'C') THEN
1537 msc_sch_wb.atp_debug('Get_Res_Hierarchy_Demand_Class: ' || 'level 3, first select fails');
1538 END IF;
1539
1540 BEGIN
1541 SELECT distinct demand_class
1542 INTO l_hierarchy_demand_class
1543 FROM msc_allocations
1544 WHERE level_id = 3
1545 AND time_phase_id = l_time_phase_id
1546 AND class = l_class_code
1547 AND partner_id = p_partner_id
1548 AND partner_site_id = -1;
1549 Exception
1550 WHEN NO_DATA_FOUND THEN
1551 l_hierarchy_demand_class := null;
1552 END;
1553
1554 IF PG_DEBUG in ('Y', 'C') THEN
1555 msc_sch_wb.atp_debug('Get_Res_Hierarchy_Demand_Class: ' || 'level 3, after the second select');
1556 msc_sch_wb.atp_debug('Get_Res_Hierarchy_Demand_Class: ' || 'l_hierarchy_demand_class = '||l_hierarchy_demand_class);
1557 END IF;
1558
1559 END IF; -- IF l_hierarchy_demand_class IS NULL
1560
1561 IF l_hierarchy_demand_class IS NULL THEN
1562 -- could not find the match demand class for
1563 -- (class_code+partner_id+partner_site_id) and
1564 -- (class_code+partner_id+others)
1565
1566 -- try to find the match demand class for that
1567 -- class_code+others+others
1568 IF PG_DEBUG in ('Y', 'C') THEN
1569 msc_sch_wb.atp_debug('Get_Res_Hierarchy_Demand_Class: ' || 'level 3, second select fails');
1570 END IF;
1571
1572 BEGIN
1573
1574 IF PG_DEBUG in ('Y', 'C') THEN
1575 msc_sch_wb.atp_debug('Get_Res_Hierarchy_Demand_Class: ' || 'l_time_phase_id = '||l_time_phase_id);
1576 msc_sch_wb.atp_debug('Get_Res_Hierarchy_Demand_Class: ' || 'l_class_code = '||l_class_code);
1577 END IF;
1578 SELECT distinct demand_class
1579 INTO l_hierarchy_demand_class
1580 FROM msc_allocations
1581 WHERE level_id = 3
1582 AND time_phase_id = l_time_phase_id
1583 AND class = l_class_code
1584 AND partner_id = -1
1585 AND partner_site_id = -1;
1586 EXCEPTION
1587 WHEN NO_DATA_FOUND THEN
1588 IF PG_DEBUG in ('Y', 'C') THEN
1589 msc_sch_wb.atp_debug('Get_Res_Hierarchy_Demand_Class: ' || 'NO DATA FOUND');
1590 END IF;
1591 -- no match for
1592 -- class_code + other+other
1593 l_hierarchy_demand_class := null;
1594 END;
1595
1596
1597
1598 IF PG_DEBUG in ('Y', 'C') THEN
1599 msc_sch_wb.atp_debug('Get_Res_Hierarchy_Demand_Class: ' || 'level 3, after the third select');
1600 msc_sch_wb.atp_debug('Get_Res_Hierarchy_Demand_Class: ' || 'l_hierarchy_demand_class = '||l_hierarchy_demand_class);
1601 END IF;
1602
1603
1604 END IF; -- l_hierarchy_demand_class IS NULL THEN
1605
1606
1607 IF l_hierarchy_demand_class IS NULL THEN
1608 -- could not find the match demand class for
1609 -- (class_code+partner_id+partner_site_id) and
1610 -- (class_code+partner_id+others)
1611 -- (class_code+others+others)
1612
1613 -- try to find the match demand class for that
1614 -- others+others+others
1615
1616 IF PG_DEBUG in ('Y', 'C') THEN
1617 msc_sch_wb.atp_debug('Get_Res_Hierarchy_Demand_Class: ' || 'level 3, third select fails');
1618 END IF;
1619
1620 BEGIN
1621 SELECT distinct demand_class
1622 INTO l_hierarchy_demand_class
1623 FROM msc_allocations
1624 WHERE level_id = 3
1625 AND time_phase_id = l_time_phase_id
1626 AND class = '-1'
1627 AND partner_id = -1
1628 AND partner_site_id = -1;
1629 Exception
1630 WHEN NO_DATA_FOUND THEN
1631 l_hierarchy_demand_class := '-1';
1632 END;
1633
1634 IF PG_DEBUG in ('Y', 'C') THEN
1635 msc_sch_wb.atp_debug('Get_Res_Hierarchy_Demand_Class: ' || 'level 3, after the 4th select');
1636 msc_sch_wb.atp_debug('Get_Res_Hierarchy_Demand_Class: ' || 'l_hierarchy_demand_class = '||l_hierarchy_demand_class);
1637 END IF;
1638
1639
1640 END IF; -- IF l_hierarchy_demand_class IS NULL
1641 ELSIF (p_partner_site_id IS NULL AND p_level_id is null) OR
1642 (p_level_id = 2) THEN
1643
1644 IF PG_DEBUG in ('Y', 'C') THEN
1645 msc_sch_wb.atp_debug('Get_Res_Hierarchy_Demand_Class: ' || 'level 2');
1646 END IF;
1647
1648 -- level 2
1649 -- try to find the match demand class for
1650 -- that class_code+partner_id
1651 BEGIN
1652 SELECT distinct demand_class
1653 INTO l_hierarchy_demand_class
1654 FROM msc_allocations
1655 WHERE level_id = 2
1656 AND time_phase_id = l_time_phase_id
1657 AND class = l_class_code
1658 AND partner_id = p_partner_id;
1659 EXCEPTION
1660 WHEN NO_DATA_FOUND THEN
1661 -- no match for
1662 -- class_code + partner_id
1663 l_hierarchy_demand_class := null;
1664 END;
1665
1666 IF PG_DEBUG in ('Y', 'C') THEN
1667 msc_sch_wb.atp_debug('Get_Res_Hierarchy_Demand_Class: ' || 'level 2, after the first select');
1668 msc_sch_wb.atp_debug('Get_Res_Hierarchy_Demand_Class: ' || 'l_hierarchy_demand_class = '||l_hierarchy_demand_class);
1669 END IF;
1670
1671 IF l_hierarchy_demand_class IS NULL THEN
1672 -- could not find the match demand class
1673 -- for class_code+partner_id
1674 -- try to find the match demand class for that
1675 -- class_code+others
1676
1677 IF PG_DEBUG in ('Y', 'C') THEN
1678 msc_sch_wb.atp_debug('Get_Res_Hierarchy_Demand_Class: ' || 'level 2, first select fails');
1679 END IF;
1680
1681 BEGIN
1682 SELECT distinct demand_class
1683 INTO l_hierarchy_demand_class
1684 FROM msc_allocations
1685 WHERE level_id = 2
1686 AND time_phase_id = l_time_phase_id
1687 AND class = l_class_code
1688 AND partner_id = -1;
1689 Exception
1690 WHEN NO_DATA_FOUND THEN
1691 l_hierarchy_demand_class := null;
1692 END;
1693 IF PG_DEBUG in ('Y', 'C') THEN
1694 msc_sch_wb.atp_debug('Get_Res_Hierarchy_Demand_Class: ' || 'level 2, after the second select');
1695 msc_sch_wb.atp_debug('Get_Res_Hierarchy_Demand_Class: ' || 'l_hierarchy_demand_class = '||l_hierarchy_demand_class);
1696 END IF;
1697
1698 END IF; -- IF l_hierarchy_demand_class IS NULL
1699 -- bug 1680773
1700 IF l_hierarchy_demand_class IS NULL THEN
1701 -- could not find the match demand class
1702 -- for (class_code+partner_id) and
1703 -- (class_code+others )
1704
1705 IF PG_DEBUG in ('Y', 'C') THEN
1706 msc_sch_wb.atp_debug('Get_Res_Hierarchy_Demand_Class: ' || 'level 2, second select fails');
1707 END IF;
1708
1709 BEGIN
1710 SELECT distinct demand_class
1711 INTO l_hierarchy_demand_class
1712 FROM msc_allocations
1713 WHERE level_id =2
1714 AND time_phase_id = l_time_phase_id
1715 AND class = '-1'
1716 AND partner_id = -1;
1717 Exception
1718 WHEN NO_DATA_FOUND THEN
1719 l_hierarchy_demand_class := '-1';
1720
1721 END;
1722 IF PG_DEBUG in ('Y', 'C') THEN
1723 msc_sch_wb.atp_debug('Get_Res_Hierarchy_Demand_Class: ' || 'level 2, after the third select');
1724 msc_sch_wb.atp_debug('Get_Res_Hierarchy_Demand_Class: ' || 'l_hierarchy_demand_class = '||l_hierarchy_demand_class);
1725 END IF;
1726 END IF; -- IF l_hierarchy_demand_class IS NULL
1727
1728 END IF; -- IF (p_partner_site_id IS NOT NULL AND p_level_id is null)
1729 -- OR (p_level_id in (2,3))
1730
1731 ELSIF(p_partner_id IS NULL AND p_level_id is null) OR
1732 (p_level_id = 1) THEN
1733
1734 -- level 1
1735 -- since we do not have the class info which comes from
1736 -- partner_id, assume it is others
1737 -- find the match demand class for others
1738
1739 IF PG_DEBUG in ('Y', 'C') THEN
1740 msc_sch_wb.atp_debug('Get_Res_Hierarchy_Demand_Class: ' || 'level 1');
1741 END IF;
1742 IF p_level_id = 1 THEN
1743 BEGIN
1744 SELECT distinct demand_class
1745 INTO l_hierarchy_demand_class
1746 FROM msc_allocations
1747 WHERE level_id = 1
1748 AND time_phase_id = l_time_phase_id
1749 AND class = l_class_code;
1750 EXCEPTION
1751 WHEN NO_DATA_FOUND THEN
1752 l_hierarchy_demand_class := null;
1753 END;
1754
1755 IF PG_DEBUG in ('Y', 'C') THEN
1756 msc_sch_wb.atp_debug('Get_Res_Hierarchy_Demand_Class: ' || 'level 1, after the first select');
1757 msc_sch_wb.atp_debug('Get_Res_Hierarchy_Demand_Class: ' || 'l_hierarchy_demand_class = '||l_hierarchy_demand_class);
1758 END IF;
1759
1760 END IF;
1761
1762 IF l_hierarchy_demand_class IS NULL THEN
1763
1764 BEGIN
1765 SELECT distinct demand_class
1766 INTO l_hierarchy_demand_class
1767 FROM msc_allocations
1768 WHERE level_id = 1
1769 AND time_phase_id = l_time_phase_id
1770 AND class = '-1';
1771 EXCEPTION
1772 WHEN NO_DATA_FOUND THEN
1773 -- this should not happen
1774 l_hierarchy_demand_class := '-1';
1775 END;
1776
1777 END IF; -- IF l_hierarchy_demand_class IS NULL
1778
1779 IF PG_DEBUG in ('Y', 'C') THEN
1780 msc_sch_wb.atp_debug('Get_Res_Hierarchy_Demand_Class: ' || 'level 1, after the second select');
1781 msc_sch_wb.atp_debug('Get_Res_Hierarchy_Demand_Class: ' || 'l_hierarchy_demand_class = '||l_hierarchy_demand_class);
1782 END IF;
1783
1784 END IF; -- IF p_partner_id IS NOT NULL THEN
1785 END IF; -- IF (G_HIERARCHY_PROFILE = 1)
1786
1787 IF PG_DEBUG in ('Y', 'C') THEN
1788 msc_sch_wb.atp_debug('END Get_RES_Hierarchy_Demand_Class');
1789 END IF;
1790 --bug 2424357: trat others as -1
1791 --RETURN NVL(l_hierarchy_demand_class, '@@@'); -- 1680773
1792 RETURN NVL(l_hierarchy_demand_class, '-1'); -- 1680773
1793
1794 END Get_Res_Hierarchy_Demand_Class;
1795
1796 END MSC_AATP_FUNC;
[Home] [Help]