[Home] [Help]
PACKAGE BODY: APPS.MSC_AATP_REQ
Source
1 PACKAGE BODY MSC_AATP_REQ AS
2 /* $Header: MSCRAATB.pls 120.5.12010000.2 2008/08/25 10:37:19 sbnaik ship $ */
3 G_PKG_NAME CONSTANT VARCHAR2(30) := 'MSC_AATP_REQ';
4
5 -- INFINITE_NUMBER CONSTANT NUMBER := 1.0e+10;
6
7 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('MSC_ATP_DEBUG'), 'N');
8
9 PROCEDURE Item_Pre_Allocated_Atp(
10 p_plan_id IN NUMBER,
11 p_level IN NUMBER,
12 p_identifier IN NUMBER,
13 p_scenario_id IN NUMBER,
14 p_inventory_item_id IN NUMBER,
15 p_organization_id IN NUMBER,
16 p_instance_id IN NUMBER,
17 p_demand_class IN VARCHAR2,
18 p_request_date IN DATE,
19 p_insert_flag IN NUMBER,
20 x_atp_info OUT NoCopy MRP_ATP_PVT.ATP_Info,
21 x_atp_period OUT NoCopy MRP_ATP_PUB.ATP_Period_Typ,
22 x_atp_supply_demand OUT NoCopy MRP_ATP_PUB.ATP_Supply_Demand_Typ,
23 p_get_mat_in_rec IN MSC_ATP_REQ.get_mat_in_rec,
24 p_refresh_number IN NUMBER, -- For summary enhancement
25 p_request_item_id IN NUMBER, -- For time_phased_atp
26 p_atf_date IN DATE) -- For time_phased_atp
27 IS
28 l_infinite_time_fence_date DATE;
29 l_default_atp_rule_id NUMBER;
30 l_calendar_exception_set_id NUMBER;
31 l_inv_item_id NUMBER;
32 l_null_num NUMBER := null;
33 l_uom_code VARCHAR2(3);
34 l_null_char VARCHAR2(3) := null;
35 l_return_status VARCHAR2(1);
36 i PLS_INTEGER;
37 mm PLS_INTEGER;
38 ii PLS_INTEGER;
39 jj PLS_INTEGER;
40 j PLS_INTEGER;
41 k PLS_INTEGER;
42 l_current_atp MRP_ATP_PVT.ATP_Info;
43
44 -- time_phased_atp
45 l_time_phased_atp VARCHAR2(1) := 'N';
46 l_pf_item_id NUMBER;
47
48 BEGIN
49
50 IF PG_DEBUG in ('Y', 'C') THEN
51 msc_sch_wb.atp_debug('******* Item_Pre_Allocated_Atp *******');
52 msc_sch_wb.atp_debug('Item_Pre_Allocated_Atp: ' || 'p_plan_id =' || p_plan_id );
53 msc_sch_wb.atp_debug('Item_Pre_Allocated_Atp: ' || 'p_level =' || p_level );
54 msc_sch_wb.atp_debug('Item_Pre_Allocated_Atp: ' || 'p_identifier =' || p_identifier);
55 msc_sch_wb.atp_debug('Item_Pre_Allocated_Atp: ' || 'p_scenario_id =' || p_scenario_id);
56 msc_sch_wb.atp_debug('Item_Pre_Allocated_Atp: ' || 'p_inventory_item_id =' || p_inventory_item_id);
57 msc_sch_wb.atp_debug('Item_Pre_Allocated_Atp: ' || 'p_organization_id =' || p_organization_id);
58 msc_sch_wb.atp_debug('Item_Pre_Allocated_Atp: ' || 'p_instance_id =' || p_instance_id);
59 msc_sch_wb.atp_debug('Item_Pre_Allocated_Atp: ' || 'p_demand_class =' || p_demand_class);
60 msc_sch_wb.atp_debug('Item_Pre_Allocated_Atp: ' || 'p_request_date =' || p_request_date );
61 msc_sch_wb.atp_debug('Item_Pre_Allocated_Atp: ' || 'p_insert_flag =' || p_insert_flag );
62 msc_sch_wb.atp_debug('Item_Pre_Allocated_Atp: ' || 'p_request_item_id =' || p_request_item_id );
63 msc_sch_wb.atp_debug('Item_Pre_Allocated_Atp: ' || 'p_atf_date =' || p_atf_date );
64 END IF;
65
66 -- This procedure will only deal with the requested demand class.
67
68 -- Logic
69 -- Step 1:
70 -- FOR demand class DCi (p_demand_class), we need to
71 -- 1. get the net daily availability. Two scenarios are handled:
72 -- summary of availability or supply-demand details .
73 -- Step 2:
74 -- do accumulation for the requested demand class
75 -- Forward and Backward consumption and Accumulation done together.
76
77 -- The result is that ATP_Info, ATP_Period_Typ and ATP_Supply_Demand_Typ
78 -- related data is populated.
79
80 /* time_phased_atp changes begin*/
81 IF (p_inventory_item_id <> p_request_item_id and p_atf_date is not null) THEN
82 l_time_phased_atp := 'Y';
83 l_pf_item_id := MSC_ATP_PVT.G_ITEM_INFO_REC.product_family_id;
84 END IF;
85
86 IF PG_DEBUG in ('Y', 'C') THEN
87 msc_sch_wb.atp_debug('Item_Pre_Allocated_Atp: ' || 'Time Phased ATP = ' || l_time_phased_atp );
88 END IF;
89
90 /*
91 BEGIN
92 SELECT inventory_item_id, uom_code
93 INTO l_inv_item_id, l_uom_code
94 FROM msc_system_items
95 WHERE plan_id = p_plan_id
96 AND sr_instance_id = p_instance_id
97 AND organization_id = p_organization_id
98 AND sr_inventory_item_id = p_inventory_item_id;
99 EXCEPTION
100 WHEN OTHERS THEN
101 IF PG_DEBUG in ('Y', 'C') THEN
102 msc_sch_wb.atp_debug('Item_Pre_Allocated_Atp: ' || 'Error selecting uom_code for the item');
103 END IF;
104 END;*/
105 l_inv_item_id := MSC_ATP_PVT.G_ITEM_INFO_REC.inventory_item_id;
106 l_uom_code := MSC_ATP_PVT.G_ITEM_INFO_REC.uom_code;
107 -- time_phased_atp changes end
108
109 -- get the infinite time fence date if it exists
110 --diag_atp
111 IF p_scenario_id = -1 THEN
112 l_infinite_time_fence_date := MSC_ATP_FUNC.get_infinite_time_fence_date(
113 p_instance_id, p_inventory_item_id,
114 p_organization_id, p_plan_id);
115 ELSE
116 l_infinite_time_fence_date := p_get_mat_in_rec.infinite_time_fence_date;
117 END IF;
118 IF PG_DEBUG in ('Y', 'C') THEN
119 msc_sch_wb.atp_debug('Item_Pre_Allocated_Atp: ' || 'infinite fence : ' || l_infinite_time_fence_date);
120 END IF;
121
122 -- get the daily net availability for DCi
123 IF (NVL(p_insert_flag, 0) = 0 ) THEN
124 -- we don't want details
125 IF PG_DEBUG in ('Y', 'C') THEN
126 msc_sch_wb.atp_debug('Item_Pre_Allocated_Atp: ' || 'p_insert_flag : 0');
127 END IF;
128
129 IF MSC_ATP_PVT.G_SUMMARY_SQL = 'Y' THEN -- For summary enhancement
130 IF PG_DEBUG in ('Y', 'C') THEN
131 msc_sch_wb.atp_debug('Item_Pre_Allocated_Atp: ' || 'MSC_ATP_PVT.G_SUMMARY_SQL := ' || MSC_ATP_PVT.G_SUMMARY_SQL);
132 END IF;
133
134 -- time_phased_atp
135 IF l_time_phased_atp = 'N' THEN
136 IF PG_DEBUG in ('Y', 'C') THEN
137 msc_sch_wb.atp_debug('Item_Pre_Allocated_Atp: ' || 'l_time_phased_atp := ' || l_time_phased_atp);
138 END IF;
139
140 -- SQL changed for summary enhancement
141 SELECT SD_DATE,
142 SUM(SD_QTY)
143 BULK COLLECT INTO
144 l_current_atp.atp_period,
145 l_current_atp.atp_qty
146 FROM
147 (
148 SELECT /*+ INDEX(S MSC_ATP_SUMMARY_SD_U1) */
149 SD_DATE, SD_QTY
150 FROM MSC_ATP_SUMMARY_SD S
151 WHERE S.PLAN_ID = p_plan_id
152 AND S.SR_INSTANCE_ID = p_instance_id
153 AND S.INVENTORY_ITEM_ID = l_inv_item_id
154 AND S.ORGANIZATION_ID = p_organization_id
155 AND S.DEMAND_CLASS = NVL(p_demand_class, S.DEMAND_CLASS)
156 AND S.SD_DATE < l_infinite_time_fence_date
157
158 UNION ALL
159
160 SELECT TRUNC(AD.DEMAND_DATE) SD_DATE,
161 decode(AD.ALLOCATED_QUANTITY, -- Consider unscheduled orders as dummy supplies
162 0,NVL(AD.OLD_ALLOCATED_QUANTITY,0), -- For summary enhancement --5283809
163 -1 * AD.ALLOCATED_QUANTITY) SD_QTY
164 FROM MSC_ALLOC_DEMANDS AD,
165 MSC_PLANS P -- For summary enhancement
166 WHERE AD.PLAN_ID = p_plan_id
167 AND AD.SR_INSTANCE_ID = p_instance_id
168 AND AD.INVENTORY_ITEM_ID = l_inv_item_id
169 AND AD.ORGANIZATION_ID = p_organization_id
170 AND AD.DEMAND_CLASS = NVL(p_demand_class, AD.DEMAND_CLASS)
171 AND TRUNC(AD.DEMAND_DATE) < l_infinite_time_fence_date
172 AND P.PLAN_ID = AD.PLAN_ID
173 AND (AD.REFRESH_NUMBER > P.LATEST_REFRESH_NUMBER
174 OR AD.REFRESH_NUMBER = p_refresh_number)
175 -- since repetitive schedule demand is not supported in this case
176 -- join to msc_calendar_dates is not needed.
177
178 UNION ALL
179
180 SELECT TRUNC(SA.SUPPLY_DATE) SD_DATE,
181 decode(SA.ALLOCATED_QUANTITY, -- Consider deleted stealing records as dummy demands
182 0, -1 * (NVL(OLD_ALLOCATED_QUANTITY,0)), -- For summary enhancement --5283809
183 SA.ALLOCATED_QUANTITY) SD_QTY
184 FROM MSC_ALLOC_SUPPLIES SA,
185 MSC_PLANS P -- For summary enhancement
186 WHERE SA.PLAN_ID = p_plan_id
187 AND SA.SR_INSTANCE_ID = p_instance_id
188 AND SA.INVENTORY_ITEM_ID = l_inv_item_id
189 AND SA.ORGANIZATION_ID = p_organization_id
190 AND SA.DEMAND_CLASS = NVL(p_demand_class, SA.DEMAND_CLASS)
191 AND TRUNC(SA.SUPPLY_DATE) < l_infinite_time_fence_date
192 AND P.PLAN_ID = SA.PLAN_ID
193 AND (SA.REFRESH_NUMBER > P.LATEST_REFRESH_NUMBER
194 OR SA.REFRESH_NUMBER = p_refresh_number)
195 )
196 GROUP BY SD_DATE
197 ORDER BY SD_DATE;--4698199
198
199 ELSE -- IF Not_PF_Case THEN
200
201 IF PG_DEBUG in ('Y', 'C') THEN
202 msc_sch_wb.atp_debug('Item_Pre_Allocated_Atp: ' || 'l_time_phased_atp := ' || l_time_phased_atp);
203 END IF;
204
205 MSC_ATP_PF.Item_Prealloc_Avail_Pf_Summ(
206 l_inv_item_id,
207 l_pf_item_id,
208 p_organization_id,
209 p_instance_id,
210 p_plan_id,
211 p_demand_class,
212 l_infinite_time_fence_date,
213 p_refresh_number,
214 l_current_atp.atp_period,
215 l_current_atp.atp_qty,
216 l_return_status
217 );
218 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
219 IF PG_DEBUG in ('Y', 'C') THEN
220 msc_sch_wb.atp_debug('Item_Pre_Allocated_Atp: ' || 'Error occured in procedure Item_Prealloc_Avail_Pf_Summ');
221 END IF;
222 RAISE FND_API.G_EXC_ERROR;
223 END IF;
224
225 END IF; -- IF Not_PF_Case THEN
226
227
228 ELSE -- IF MSC_ATP_PVT.G_SUMMARY_FLAG = 'Y' THEN -- For summary enhancement
229
230 IF PG_DEBUG in ('Y', 'C') THEN
231 msc_sch_wb.atp_debug('Item_Pre_Allocated_Atp: ' || 'MSC_ATP_PVT.G_SUMMARY_SQL := ' || MSC_ATP_PVT.G_SUMMARY_SQL);
232 END IF;
233
234 -- time_phased_atp
235 IF l_time_phased_atp = 'N' THEN
236 SELECT SD_DATE,
237 SUM(SD_QTY)
238 BULK COLLECT INTO
239 l_current_atp.atp_period,
240 l_current_atp.atp_qty
241 FROM (
242 SELECT TRUNC(AD.DEMAND_DATE) SD_DATE,
243 -1 * AD.ALLOCATED_QUANTITY SD_QTY
244 FROM MSC_ALLOC_DEMANDS AD
245 WHERE AD.PLAN_ID = p_plan_id
246 AND AD.SR_INSTANCE_ID = p_instance_id
247 AND AD.INVENTORY_ITEM_ID = l_inv_item_id
248 AND AD.ORGANIZATION_ID = p_organization_id
249 AND AD.DEMAND_CLASS = NVL(p_demand_class, AD.DEMAND_CLASS)
250 AND TRUNC(AD.DEMAND_DATE) < l_infinite_time_fence_date
251 -- since repetitive schedule demand is not supported in this case
252 -- join to msc_calendar_dates is not needed.
253 UNION ALL
254 SELECT TRUNC(SA.SUPPLY_DATE) SD_DATE,
255 SA.ALLOCATED_QUANTITY SD_QTY
256 FROM MSC_ALLOC_SUPPLIES SA
257 WHERE SA.PLAN_ID = p_plan_id
258 AND SA.SR_INSTANCE_ID = p_instance_id
259 AND SA.INVENTORY_ITEM_ID = l_inv_item_id
260 AND SA.ORGANIZATION_ID = p_organization_id
261 AND SA.ALLOCATED_QUANTITY <> 0
262 AND SA.DEMAND_CLASS = NVL(p_demand_class, SA.DEMAND_CLASS)
263 -- fixed as part of time_phased_atp chagnes
264 AND TRUNC(SA.SUPPLY_DATE) >= DECODE(SA.ORIGINAL_ORDER_TYPE,
265 27, TRUNC(SYSDATE),
266 28, TRUNC(SYSDATE),
267 TRUNC(SA.SUPPLY_DATE))
268 AND TRUNC(SA.SUPPLY_DATE) < l_infinite_time_fence_date
269 )
270 GROUP BY SD_DATE
271 ORDER BY SD_DATE;--4698199
272 ELSE -- IF Not_PF_Case THEN
273 MSC_ATP_PF.Item_Prealloc_Avail_Pf(
274 l_inv_item_id,
275 l_pf_item_id,
276 p_organization_id,
277 p_instance_id,
278 p_plan_id,
279 p_demand_class,
280 l_infinite_time_fence_date,
281 l_current_atp.atp_period,
282 l_current_atp.atp_qty,
283 l_return_status
284 );
285 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
286 IF PG_DEBUG in ('Y', 'C') THEN
287 msc_sch_wb.atp_debug('Item_Pre_Allocated_Atp: ' || 'Error occured in procedure Item_Prealloc_Avail_Pf');
288 END IF;
289 RAISE FND_API.G_EXC_ERROR;
290 END IF;
291 END IF; -- IF Not_PF_Case THEN
292
293 END IF; -- IF MSC_ATP_PVT.G_SUMMARY_FLAG = 'Y' THEN -- For summary enhancement
294
295 ELSE -- IF (NVL(p_insert_flag, 0) = 0 ) THEN
296 -- IF (NVL(p_insert_flag, 0) <> 0 )
297 -- OR p_scenario_id = -1
298 -- get the details
299 MSC_ATP_DB_UTILS.Clear_SD_Details_Temp();
300
301 -- time_phased_atp
302 IF l_time_phased_atp = 'Y' THEN
303 MSC_ATP_PF.Item_Prealloc_Avail_Pf_Dtls(
304 l_inv_item_id,
305 l_pf_item_id,
306 p_request_item_id,
307 p_inventory_item_id,
308 p_organization_id,
309 p_instance_id,
310 p_plan_id,
311 p_demand_class,
312 l_infinite_time_fence_date,
313 p_atf_date,
314 p_level,
315 p_identifier,
316 p_scenario_id,
317 l_uom_code,
318 l_return_status
319 );
320 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
321 IF PG_DEBUG in ('Y', 'C') THEN
322 msc_sch_wb.atp_debug('Item_Pre_Allocated_Atp: ' || 'Error occured in procedure Item_Prealloc_Avail_Pf_Dtls');
323 END IF;
324 RAISE FND_API.G_EXC_ERROR;
325 END IF;
326 ELSE -- IF Not_PF_Case THEN
327 INSERT INTO msc_atp_sd_details_temp (
328 ATP_Level,
329 Order_line_id,
330 Scenario_Id,
331 Inventory_Item_Id,
332 Request_Item_Id,
333 Organization_Id,
334 Department_Id,
335 Resource_Id,
336 Supplier_Id,
337 Supplier_Site_Id,
338 From_Organization_Id,
339 From_Location_Id,
340 To_Organization_Id,
341 To_Location_Id,
342 Ship_Method,
343 UOM_code,
344 Supply_Demand_Type,
345 Supply_Demand_Source_Type,
346 Supply_Demand_Source_Type_Name,
347 Identifier1,
348 Identifier2,
349 Identifier3,
350 Identifier4,
351 Allocated_Quantity, -- fixed as part of time_phased_atp
352 Supply_Demand_Quantity,
353 Supply_Demand_Date,
354 Disposition_Type,
355 Disposition_Name,
356 Pegging_Id,
357 End_Pegging_Id,
358 Pf_Display_Flag,
359 Original_Demand_Quantity,
360 Original_Demand_Date,
361 Original_Item_Id,
362 Original_Supply_Demand_Type,
363 creation_date,
364 created_by,
365 last_update_date,
366 last_updated_by,
367 last_update_login,
368 ORIG_CUSTOMER_SITE_NAME,--bug3263368
369 ORIG_CUSTOMER_NAME, --bug3263368
370 ORIG_DEMAND_CLASS, --bug3263368
371 ORIG_REQUEST_DATE --bug3263368
372 )
373 (
374 SELECT p_level col1,
375 p_identifier col2,
376 p_scenario_id col3,
377 p_inventory_item_id col4 ,
378 p_request_item_id col5,
379 p_organization_id col6,
380 l_null_num col7,
381 l_null_num col8,
382 l_null_num col9,
383 l_null_num col10,
384 l_null_num col11,
385 l_null_num col12,
386 l_null_num col13,
387 l_null_num col14,
388 l_null_char col15,
389 l_uom_code col16,
390 1 col17, -- demand
391 --AD.ORIGINATION_TYPE col18,
392 DECODE(AD.ORIGINATION_TYPE, -100, 30,AD.ORIGINATION_TYPE) col18, --5027568
393 l_null_char col19,
394 AD.SR_INSTANCE_ID col20,
395 l_null_num col21,
396 AD.PARENT_DEMAND_ID col22,
397 l_null_num col23,
398 -1 * AD.ALLOCATED_QUANTITY col24,
399 -1* NVL(AD.Demand_Quantity, AD.ALLOCATED_QUANTITY), -- fixed as part of time_phased_atp
400 TRUNC(AD.DEMAND_DATE) col25,
401 l_null_num col26,
402 AD.ORDER_NUMBER col27,
403 l_null_num col28,
404 l_null_num col29,
405 AD.Pf_Display_Flag,
406 -1* NVL(AD.Demand_Quantity, AD.ALLOCATED_QUANTITY),
407 AD.Original_Demand_Date,
408 AD.Original_Item_Id,
409 AD.Original_Origination_Type
410 , sysdate
411 , FND_GLOBAL.USER_ID
412 , sysdate
413 , FND_GLOBAL.USER_ID
414 , FND_GLOBAL.USER_ID,
415 MTPS.LOCATION, --bug3263368
416 MTP.PARTNER_NAME, --bug3263368
417 AD.DEMAND_CLASS, --bug3263368
418 AD.REQUEST_DATE --bug3263368
419
420 FROM MSC_ALLOC_DEMANDS AD,
421 MSC_TRADING_PARTNERS MTP,--bug3263368
422 MSC_TRADING_PARTNER_SITES MTPS --bug3263368
423
424 WHERE AD.PLAN_ID = p_plan_id
425 AND AD.SR_INSTANCE_ID = p_instance_id
426 AND AD.INVENTORY_ITEM_ID = l_inv_item_id
427 AND AD.ORGANIZATION_ID = p_organization_id
428 AND AD.DEMAND_CLASS = NVL(p_demand_class, AD.DEMAND_CLASS )
429 AND AD.ORIGINATION_TYPE <> 52
430 AND TRUNC(AD.DEMAND_DATE) < l_infinite_time_fence_date
431 AND AD.SHIP_TO_SITE_ID = MTPS.PARTNER_SITE_ID(+) --bug3263368
432 AND AD.CUSTOMER_ID = MTP.PARTNER_ID(+)--bug3263368
433
434 -- since repetitive schedule demand is not supported in this case
435 -- join to msc_calendar_dates is not needed.
436 UNION ALL
437 SELECT p_level col1,
438 p_identifier col2,
439 p_scenario_id col3,
440 p_inventory_item_id col4 ,
441 p_request_item_id col5,
442 p_organization_id col6,
443 l_null_num col7,
444 l_null_num col8,
445 l_null_num col9,
446 l_null_num col10,
447 l_null_num col11,
448 l_null_num col12,
449 l_null_num col13,
450 l_null_num col14,
451 l_null_char col15,
452 l_uom_code col16,
453 2 col17, -- supply
454 SA.ORDER_TYPE col18,
455 l_null_char col19,
456 SA.SR_INSTANCE_ID col20,
457 l_null_num col21,
458 SA.PARENT_TRANSACTION_ID col22,
459 l_null_num col23,
460 SA.ALLOCATED_QUANTITY col24,
461 NVL(SA.Supply_Quantity, SA.ALLOCATED_QUANTITY), -- fixed as part of time_phased_atp
462 TRUNC(SA.SUPPLY_DATE) col25,
463 l_null_num col26,
464 DECODE(SA.ORDER_TYPE, 5, to_char(SA.PARENT_TRANSACTION_ID), SA.ORDER_NUMBER) col27,
465 -- Bug 2771075. For Planned Orders, we will populate transaction_id
466 -- in the disposition_name column to be consistent with Planning.
467 l_null_num col28,
468 l_null_num col29,
469 l_null_num,
470 l_null_num,
471 to_date(null),
472 SA.Original_Item_Id,
473 SA.Original_Order_Type
474 , sysdate
475 , FND_GLOBAL.USER_ID
476 , sysdate
477 , FND_GLOBAL.USER_ID
478 , FND_GLOBAL.USER_ID,
479 MTPS.LOCATION, --bug3684383
480 MTP.PARTNER_NAME, --bug3684383
481 SA.DEMAND_CLASS, --bug3684383
482 null --bug3684383
483
484 FROM MSC_ALLOC_SUPPLIES SA,
485 MSC_TRADING_PARTNERS MTP,--bug3684383
486 MSC_TRADING_PARTNER_SITES MTPS --bug3684383
487 WHERE SA.PLAN_ID = p_plan_id
488 AND SA.SR_INSTANCE_ID = p_instance_id
489 AND SA.INVENTORY_ITEM_ID = l_inv_item_id
490 AND SA.ORGANIZATION_ID = p_organization_id
491 AND SA.ALLOCATED_QUANTITY <> 0
492 AND SA.DEMAND_CLASS = NVL(p_demand_class, SA.DEMAND_CLASS )
493 -- fixed as part of time_phased_atp chagnes
494 AND TRUNC(SA.SUPPLY_DATE) >= DECODE(SA.ORIGINAL_ORDER_TYPE,
495 27, TRUNC(SYSDATE),
496 28, TRUNC(SYSDATE),
497 TRUNC(SA.SUPPLY_DATE))
498 AND TRUNC(SA.SUPPLY_DATE) < l_infinite_time_fence_date
499 AND SA.SHIP_TO_SITE_ID = MTPS.PARTNER_SITE_ID(+) --bug3684383
500 AND SA.CUSTOMER_ID = MTP.PARTNER_ID(+)--bug3684383
501 );
502 END IF; -- IF Not_PF_Case THEN
503
504 -- for period ATP
505 IF PG_DEBUG in ('Y', 'C') THEN
506 msc_sch_wb.atp_debug('Item_Pre_Allocated_Atp: ' || 'after selecting sd data into msc_atp_sd_details_temp');
507 msc_sch_wb.atp_debug('Item_Pre_Allocated_Atp: ' || 'count : ' || SQL%ROWCOUNT);
508 END IF;
509
510 -- time_phased_atp
511 IF l_time_phased_atp='Y' THEN
512 MSC_ATP_PF.Get_Period_Data_From_Sd_Temp(x_atp_period, l_return_status);
513 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
514 IF PG_DEBUG in ('Y', 'C') THEN
515 msc_sch_wb.atp_debug('Item_Pre_Allocated_Atp: ' || 'Error occured in procedure Get_Period_Data_From_Sd_Temp');
516 END IF;
517 RAISE FND_API.G_EXC_ERROR;
518 END IF;
519 ELSE
520 --MSC_ATP_PROC.get_period_data_from_SD_temp(x_atp_period);
521 /* time_phased_atp
522 call new procedure to fix the issue of not displaying correct quantities in ATP SD Window when
523 user opens ATP SD window from ATP pegging in allocated scenarios*/
524 MSC_ATP_PROC.Get_Alloc_Data_From_Sd_Temp(x_atp_period, l_return_status);
525 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
526 IF PG_DEBUG in ('Y', 'C') THEN
527 msc_sch_wb.atp_debug('Item_Pre_Allocated_Atp: ' || 'Error occured in procedure Get_Alloc_Data_From_Sd_Temp');
528 END IF;
529 RAISE FND_API.G_EXC_ERROR;
530 END IF;
531 END IF;
532
533 l_current_atp.atp_period := x_atp_period.Period_Start_Date;
534 l_current_atp.atp_qty := x_atp_period.Period_Quantity;
535
536 END IF; -- NVL(p_insert_flag, 0) = 0
537
538 IF PG_DEBUG in ('Y', 'C') THEN
539 msc_sch_wb.atp_debug('Item_Pre_Allocated_Atp: ' || 'right after the big query');
540 mm := l_current_atp.atp_qty.FIRST;
541
542 WHILE mm is not null LOOP
543 msc_sch_wb.atp_debug('Item_Pre_Allocated_Atp: ' || 'l_current_atp.atp_period:atp_qty = '||
544 l_current_atp.atp_period(mm) ||' : '|| l_current_atp.atp_qty(mm));
545 mm := l_current_atp.atp_qty.Next(mm);
546 END LOOP;
547 END IF;
548
549 -- Do backward consumption, forward consumption and accumulation
550 -- as a single step process for DCi
551 -- time_phased_atp
552 IF l_time_phased_atp = 'Y' THEN
553 MSC_ATP_PF.pf_atp_consume(
554 l_current_atp.atp_qty,
555 l_return_status,
556 l_current_atp.atp_period,
557 MSC_ATP_PF.Bw_Fw_Cum, --b/w, f/w consumption and accumulation
558 p_atf_date);
559 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
560 IF PG_DEBUG in ('Y', 'C') THEN
561 msc_sch_wb.atp_debug('Item_Pre_Allocated_Atp: ' || 'Error occured in procedure Pf_Atp_Consume');
562 END IF;
563 RAISE FND_API.G_EXC_ERROR;
564 END IF;
565 ELSE
566 MSC_ATP_PROC.Atp_Consume(l_current_atp.atp_qty, l_current_atp.atp_qty.COUNT);
567 END IF;
568
569 /* Cum drop issue changes begin*/
570 MSC_AATP_PROC.Atp_Remove_Negatives(l_current_atp.atp_qty, l_return_status);
571 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
572 IF PG_DEBUG in ('Y', 'C') THEN
573 msc_sch_wb.atp_debug('Item_Pre_Allocated_Atp: ' || 'Error occured in procedure Atp_Remove_Negatives');
574 END IF;
575 RAISE FND_API.G_EXC_ERROR;
576 END IF;
577 /* Cum drop issue changes end*/
578
579 IF PG_DEBUG in ('Y', 'C') THEN
580 msc_sch_wb.atp_debug('Item_Pre_Allocated_Atp: ' || 'right after the ATP consume');
581 mm := l_current_atp.atp_qty.FIRST;
582
583 WHILE mm is not null LOOP
584 msc_sch_wb.atp_debug('Item_Pre_Allocated_Atp: ' || 'l_current_atp.atp_period and atp_qty = '||
585 l_current_atp.atp_period(mm) ||' : '|| l_current_atp.atp_qty(mm));
586 mm := l_current_atp.atp_qty.Next(mm);
587 END LOOP;
588 END IF;
589
590 x_atp_info := l_current_atp;
591
592 IF l_infinite_time_fence_date IS NOT NULL THEN
593 -- add one more entry to indicate infinite time fence date and quantity.
594 x_atp_info.atp_qty.EXTEND;
595 --x_atp_info.limit_qty.EXTEND;
596 x_atp_info.atp_period.EXTEND;
597
598 i := x_atp_info.atp_qty.COUNT;
599 x_atp_info.atp_period(i) := l_infinite_time_fence_date;
600 x_atp_info.atp_qty(i) := MSC_ATP_PVT.INFINITE_NUMBER;
601 --x_atp_info.limit_qty(i) := MSC_ATP_PVT.INFINITE_NUMBER;
602
603 IF NVL(p_insert_flag, 0) <> 0 THEN
604 -- add one more entry to indicate infinite time fence date and quantity.
605 x_atp_period.Cumulative_Quantity := x_atp_info.atp_qty;
606
607 MSC_ATP_PROC.add_inf_time_fence_to_period(
608 p_level,
609 p_identifier,
610 p_scenario_id,
611 p_inventory_item_id,
612 p_inventory_item_id,
613 p_organization_id,
614 null, -- p_supplier_id
615 null, -- p_supplier_site_id
616 l_infinite_time_fence_date,
617 x_atp_period
618 );
619 END IF;
620 END IF;
621
622 END Item_Pre_Allocated_Atp;
623
624
625 PROCEDURE Atp_Alloc_Consume(
626 p_atp_qty IN OUT NoCopy MRP_ATP_PUB.number_arr,
627 p_atp_dc_tab IN MRP_ATP_PUB.char80_arr,
628 x_dc_list_tab OUT NoCopy MRP_ATP_PUB.char80_arr,
629 x_dc_start_index OUT NoCopy MRP_ATP_PUB.number_arr,
630 x_dc_end_index OUT NoCopy MRP_ATP_PUB.number_arr)
631 IS
632 i NUMBER;
633 j NUMBER;
634
635 BEGIN
636
637 IF PG_DEBUG in ('Y', 'C') THEN
638 msc_sch_wb.atp_debug('**********Begin Atp_Alloc_Consume Procedure************');
639 END IF;
640
641 x_dc_list_tab := MRP_ATP_PUB.Char80_Arr();
642 x_dc_start_index := MRP_ATP_PUB.Number_Arr();
643 x_dc_end_index := MRP_ATP_PUB.Number_Arr();
644
645 x_dc_list_tab.EXTEND;
646 x_dc_start_index.EXTEND;
647 x_dc_end_index.EXTEND;
648
649 IF PG_DEBUG in ('Y', 'C') THEN
650 msc_sch_wb.atp_debug('Atp_Alloc_Consume: ' || 'after extend : ' || p_atp_dc_tab(p_atp_dc_tab.FIRST));
651 END IF;
652
653 x_dc_list_tab(1) := p_atp_dc_tab(p_atp_dc_tab.FIRST);
654 x_dc_start_index(1) := 1;
655 IF PG_DEBUG in ('Y', 'C') THEN
656 msc_sch_wb.atp_debug('Atp_Alloc_Consume: ' || 'after assign : ' || x_dc_list_tab(1));
657 msc_sch_wb.atp_debug('Atp_Alloc_Consume: ' || 'start index : ' || x_dc_start_index(1));
658 END IF;
659
660 -- 2970405 rework break up demand classes
661 -- this for loop will do backward consumption
662 FOR i in 1..(p_atp_dc_tab.COUNT-1) LOOP -- 1490473: i starts from 1, not 2
663
664 IF PG_DEBUG in ('Y', 'C') THEN
665 msc_sch_wb.atp_debug('Atp_Alloc_Consume: ' || 'index : ' || i);
666 msc_sch_wb.atp_debug('Atp_Alloc_Consume: ' || 'x_dc_list_tab : ' || x_dc_list_tab(x_dc_list_tab.COUNT));
667 msc_sch_wb.atp_debug('Atp_Alloc_Consume: ' || 'p_atp_dc_tab : ' || p_atp_dc_tab(i));
668 END IF;
669
670 -- If demand class changes, re-initialize these variables.
671 IF p_atp_dc_tab(i+1) <> x_dc_list_tab(x_dc_list_tab.COUNT) THEN
672
673 x_dc_end_index(x_dc_end_index.COUNT) := i;
674
675 IF PG_DEBUG in ('Y', 'C') THEN
676 msc_sch_wb.atp_debug('Atp_Alloc_Consume: ' || 'inside IF');
677 msc_sch_wb.atp_debug('Atp_Alloc_Consume: ' || 'end index : ' || x_dc_end_index(x_dc_end_index.COUNT));
678 END IF;
679
680 x_dc_list_tab.EXTEND;
681 x_dc_start_index.EXTEND;
682 x_dc_end_index.EXTEND;
683 x_dc_list_tab(x_dc_list_tab.COUNT) := p_atp_dc_tab(i+1);
684 x_dc_start_index(x_dc_start_index.COUNT) := i+1;
685 -- x_dc_end_index(x_dc_end_index.COUNT) := i;
686 --ELSE
687 --IF PG_DEBUG in ('Y', 'C') THEN
688 -- msc_sch_wb.atp_debug('Atp_Alloc_Consume: ' || 'inside else');
689 --END IF;
690 --x_dc_end_index(x_dc_end_index.COUNT) := i;
691 END IF;
692
693 IF PG_DEBUG in ('Y', 'C') THEN
694 msc_sch_wb.atp_debug('Atp_Alloc_Consume: ' || 'start index : ' || x_dc_start_index(x_dc_start_index.COUNT));
695 msc_sch_wb.atp_debug('Atp_Alloc_Consume: ' || 'end index : ' || x_dc_end_index(x_dc_end_index.COUNT));
696 END IF;
697
698 /* 2970405 just calculate the start and end of the dc's
699 -- backward consumption when neg atp quantity occurs
700 IF (p_atp_qty(i) < 0 ) THEN
701 j := i - 1;
702 WHILE ((j >= x_dc_start_index(x_dc_start_index.COUNT)) and (p_atp_qty(j) >= 0)) LOOP
703 IF (p_atp_qty(j) = 0) THEN
704 -- backward one more period
705 j := j-1 ;
706 ELSE
707 IF (p_atp_qty(j) + p_atp_qty(i) < 0) THEN
708 -- not enough to cover the shortage
709 p_atp_qty(i) := p_atp_qty(i) + p_atp_qty(j);
710 p_atp_qty(j) := 0;
711 j := j-1;
712 ELSE
713 -- enough to cover the shortage
714 p_atp_qty(j) := p_atp_qty(j) + p_atp_qty(i);
715 p_atp_qty(i) := 0;
716 j := -1;
717 END IF;
718 END IF;
719 END LOOP;
720 END IF;
721
722 IF PG_DEBUG in ('Y', 'C') THEN
723 msc_sch_wb.atp_debug('Atp_Alloc_Consume: ' || 'before forward consumption');
724 END IF;
725
726 -- 1490473: forward consumption
727 -- this for loop will do forward consumption
728
729 -- forward consumption when neg atp quantity occurs
730 IF (p_atp_qty(i) < 0 ) THEN
731
732 j := i + 1;
733
734 IF PG_DEBUG in ('Y', 'C') THEN
735 msc_sch_wb.atp_debug('Atp_Alloc_Consume: ' || 'in forward consumption : ' || i || ':' || j);
736 msc_sch_wb.atp_debug('Atp_Alloc_Consume: ' || 'in forward : ' || p_atp_dc_tab.COUNT);
737 END IF;
738
739 IF j < p_atp_dc_tab.COUNT THEN
740
741 IF PG_DEBUG in ('Y', 'C') THEN
742 msc_sch_wb.atp_debug('Atp_Alloc_Consume: ' || 'in j : ' || p_atp_dc_tab.COUNT);
743 END IF;
744
745 WHILE (p_atp_dc_tab(j) = x_dc_list_tab(x_dc_list_tab.COUNT)) LOOP
746 IF (p_atp_qty(j) <= 0) THEN
747 -- forward one more period
748 j := j+1 ;
749 ELSE
750 IF (p_atp_qty(j) + p_atp_qty(i) < 0) THEN
751 -- not enough to cover the shortage
752 p_atp_qty(i) := p_atp_qty(i) + p_atp_qty(j);
753 p_atp_qty(j) := 0;
754 j := j + 1;
755 ELSE
756 -- enough to cover the shortage
757 p_atp_qty(j) := p_atp_qty(j) + p_atp_qty(i);
758 p_atp_qty(i) := 0;
759 EXIT;
760 END IF;
761 END IF;
762
763 IF j > p_atp_dc_tab.COUNT THEN
764 EXIT;
765 END IF;
766
767 END LOOP;
768 END IF;
769 END IF;
770 end 2970405 */
771 END LOOP;
772
773 -- 2970405
774 x_dc_end_index(x_dc_end_index.count) := p_atp_dc_tab.count;
775
776 IF PG_DEBUG in ('Y', 'C') THEN
777 msc_sch_wb.atp_debug('Atp_Alloc_Consume: ' || 'x_dc_list_tab : ' || x_dc_list_tab.COUNT);
778 END IF;
779
780 -- this for loop will do atp consume on each dc
781 FOR j in 1..x_dc_list_tab.COUNT LOOP
782
783 IF PG_DEBUG in ('Y', 'C') THEN
784 msc_sch_wb.atp_debug('Atp_Alloc_Consume: ' || 'inside atp consume : ' || j);
785 msc_sch_wb.atp_debug('Atp_Alloc_Consume: ' || 'x_dc_start_index : ' || x_dc_start_index(j));
786 msc_sch_wb.atp_debug('Atp_Alloc_Consume: ' || 'x_dc_end_index : ' || x_dc_end_index(j));
787 END IF;
788
789 MSC_ATP_PROC.atp_consume_range(p_atp_qty, x_dc_start_index(j), x_dc_end_index(j));
790 /* 2970405
791 FOR i in (x_dc_start_index(j) + 1)..x_dc_end_index(j) LOOP
792
793 IF PG_DEBUG in ('Y', 'C') THEN
794 msc_sch_wb.atp_debug('Atp_Alloc_Consume: ' || 'inside accumulation : ' || i);
795 END IF;
796 -- 1956037: do accumulation for neg quantity as well
797 p_atp_qty(i) := p_atp_qty(i) + p_atp_qty(i-1);
798 END LOOP;
799 */
800 END LOOP; --FOR i in 1..x_dc_list_tab.COUNT LOOP
801
802 IF PG_DEBUG in ('Y', 'C') THEN
803 msc_sch_wb.atp_debug('**********End Atp_Alloc_Consume Procedure************');
804 END IF;
805
806 EXCEPTION
807 WHEN others THEN
808 IF PG_DEBUG in ('Y', 'C') THEN
809 msc_sch_wb.atp_debug('Atp_Alloc_Consume: ' || 'in Exception : ' || i || ':' || j);
810 msc_sch_wb.atp_debug('Exception in Atp_Alloc_Consume : ' || sqlcode);
811 END IF;
812 END Atp_Alloc_Consume;
813
814
815 -- 3/6/2002, added this procedure by copying from MSC_ATP_REQ.Get_Material_Atp_Info
816 -- This will be called from MSC_ATP_PVT.ATP_Check for forward scheduling instead of Get_Material_Atp_Info
817 -- only for Allocated ATP in case forward stealing needs to be done.
818
819 PROCEDURE Get_Forward_Material_Atp(
820 p_instance_id IN NUMBER,
821 p_plan_id IN NUMBER,
822 p_level IN NUMBER,
823 p_identifier IN NUMBER,
824 p_demand_source_type IN NUMBER,--cmro
825 p_scenario_id IN NUMBER,
826 p_inventory_item_id IN NUMBER,
827 p_request_item_id IN NUMBER, -- For time_phased_atp
828 p_organization_id IN NUMBER,
829 p_item_name IN VARCHAR2,
830 p_family_item_name IN VARCHAR2, -- For time_phased_atp
831 p_requested_date IN DATE,
832 p_quantity_ordered IN NUMBER,
833 p_demand_class IN VARCHAR2,
834 x_requested_date_quantity OUT NoCopy NUMBER,
835 x_atf_date_quantity OUT NoCopy NUMBER, -- For time_phased_atp
836 x_atp_date_this_level OUT NoCopy DATE,
837 x_atp_date_quantity_this_level OUT NoCopy NUMBER,
838 x_atp_pegging_tab OUT NOCOPY MRP_ATP_PUB.Number_Arr,
839 x_return_status OUT NoCopy VARCHAR2,
840 x_used_available_quantity OUT NoCopy NUMBER, --bug3409973
841 p_substitution_window IN number,
842 p_get_mat_in_rec IN MSC_ATP_REQ.get_mat_in_rec,
843 x_get_mat_out_rec OUT NOCOPY MSC_ATP_REQ.get_mat_out_rec,
844 p_atf_date IN DATE, -- For time_phased_atp
845 p_order_number IN NUMBER := NULL,
846 p_refresh_number IN NUMBER := NULL,
847 p_parent_pegging_id IN NUMBER := NULL
848 )
849 IS
850 l_atp_requested_date DATE;
851 l_infinite_time_fence_date DATE;
852 l_requested_date DATE;
853 l_sys_next_date DATE;
854 NO_MATCHING_CAL_DATE EXCEPTION;
855 l_atp_quantity NUMBER;
856 l_calendar_exception_set_id NUMBER;
857 l_default_atp_rule_id NUMBER;
858 l_inv_item_id NUMBER;
859 l_level_id NUMBER;
860 l_partner_id NUMBER;
861 l_priority NUMBER;
862 l_round_flag NUMBER;
863 l_stealing_quantity NUMBER;
864 l_total_atp_qty NUMBER;
865 l_transaction_id NUMBER;
866 l_calendar_code VARCHAR2(14);
867 l_class VARCHAR2(30);
868 l_default_demand_class VARCHAR2(34);
869 l_org_code VARCHAR2(7);
870 l_stealing_flag VARCHAR2(1);
871 i PLS_INTEGER := 1;
872 j PLS_INTEGER := 1;
873 k PLS_INTEGER := 1;
874 m PLS_INTEGER := 1;
875 n PLS_INTEGER := 1;
876 l_atp_dc_tab MRP_ATP_PUB.char80_arr := MRP_ATP_PUB.char80_arr();
877 l_atp_qty_tab MRP_ATP_PUB.number_arr:=MRP_ATP_PUB.number_arr();
878 l_atp_period_tab MRP_ATP_PUB.date_arr:=MRP_ATP_PUB.date_arr();
879 l_dc_end_index MRP_ATP_PUB.number_arr := MRP_ATP_PUB.number_arr();
880 l_dc_list_tab MRP_ATP_PUB.char80_arr := MRP_ATP_PUB.char80_arr();
881 l_dc_start_index MRP_ATP_PUB.number_arr := MRP_ATP_PUB.number_arr();
882 l_demand_class_priority_tab MRP_ATP_PUB.number_arr := MRP_ATP_PUB.number_arr();
883 l_demand_class_tab MRP_ATP_PUB.char80_arr := MRP_ATP_PUB.char80_arr();
884 l_pegging_rec mrp_atp_details_temp%ROWTYPE;
885 l_period_tab MRP_ATP_PUB.date_arr:=MRP_ATP_PUB.date_arr();
886 l_used_dc_tab MRP_ATP_PUB.char80_arr := MRP_ATP_PUB.char80_arr();
887 l_used_dc_qty MRP_ATP_PUB.number_arr := MRP_ATP_PUB.number_arr();
888 l_substitution_end_date DATE;
889 --- Enhance CTO Phase 1 Req #17 new variable
890 l_demand_pegging_id NUMBER;
891 l_demand_id NUMBER;
892 l_atp_rec MRP_ATP_PVT.AtpRec;
893
894 -- For summary enhancement
895 l_summary_flag NUMBER;
896
897 -- time_phased_atp
898 l_time_phased_atp VARCHAR2(1) := 'N';
899 l_return_status VARCHAR2(1);
900 l_used_dc_mem_qty MRP_ATP_PUB.number_arr := MRP_ATP_PUB.number_arr();
901 l_atf_quantity NUMBER; -- l_atf_quantity is qty total qty used within ATF, may not be equal to ATF date qty
902 l_mem_stealing_qty NUMBER;
903 l_pf_stealing_qty NUMBER;
904 k_atf PLS_INTEGER;
905 l_pf_item_id NUMBER;
906 l_item_to_use NUMBER;
907 m_atf PLS_INTEGER;
908
909 BEGIN
910 IF PG_DEBUG in ('Y', 'C') THEN
911 msc_sch_wb.atp_debug('**********Begin Get_Forward_Material_Atp Procedure************');
912 END IF;
913 -- initialize API return status to success
914 x_return_status := FND_API.G_RET_STS_SUCCESS;
915 x_atp_pegging_tab := MRP_ATP_PUB.Number_Arr();
916
917 IF PG_DEBUG in ('Y', 'C') THEN
918 msc_sch_wb.atp_debug('********** INPUT DATA:Get_Forward_Material_Atp **********');
919 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'p_inventory_item_id: '|| to_char(p_inventory_item_id));
920 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'p_organization_id: '|| to_char(p_organization_id));
921 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'p_requested_date: '|| to_char(p_requested_date));
922 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'p_instance_id: '|| to_char(p_instance_id));
923 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'p_plan_id: '|| to_char(p_plan_id));
924 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'p_quantity_ordered: '|| to_char(p_quantity_ordered));
925 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'p_demand_class: '|| p_demand_class);
926 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'p_substitution_window:= ' || p_substitution_window);
927 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'p_parent_pegging_id:= ' || p_parent_pegging_id);
928 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'p_order_number:= ' || p_order_number);
929 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'p_get_mat_in_rec.shipping_cal_code:= ' || p_get_mat_in_rec.shipping_cal_code);
930 END IF;
931
932 -- time_phased_atp changes begin
933 IF (p_inventory_item_id <> p_request_item_id) and p_atf_date is not null THEN
934 l_time_phased_atp := 'Y';
935 IF PG_DEBUG in ('Y', 'C') THEN
936 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'Time Phased ATP = ' || l_time_phased_atp);
937 END IF;
938 END IF;
939 -- time_phased_atp changes end
940
941 -- get the infinite time fence date if it exists
942 --diag_atp
943 /*
944 l_infinite_time_fence_date := MSC_ATP_FUNC.get_infinite_time_fence_date(p_instance_id,
945 p_inventory_item_id,p_organization_id, p_plan_id);
946 */
947 MSC_ATP_PROC.get_infinite_time_fence_date(p_instance_id,
948 p_inventory_item_id,
949 p_organization_id,
950 p_plan_id,
951 l_infinite_time_fence_date,
952 x_get_mat_out_rec.atp_rule_name);
953
954 x_get_mat_out_rec.infinite_time_fence_date := l_infinite_time_fence_date;
955
956 IF PG_DEBUG in ('Y', 'C') THEN
957 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'l_infinite_time_fence_date: '|| to_char(l_infinite_time_fence_date));
958 END IF;
959 --diag_atp
960 /*
961 BEGIN
962 SELECT NVL(rounding_control_type,2), inventory_item_id
963 INTO l_round_flag, l_inv_item_id
964 FROM msc_system_items I
965 WHERE I.sr_inventory_item_id = p_inventory_item_id
966 AND I.sr_instance_id = p_instance_id
967 AND I.plan_id = p_plan_id
968 AND I.organization_id = p_organization_id;
969 EXCEPTION
970 WHEN OTHERS THEN
971 IF PG_DEBUG in ('Y', 'C') THEN
972 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'Excpetion in Round Flag : ' || sqlcode || ':' || sqlerrm);
973 END IF;
974 l_round_flag := 2; -- do not round
975 END;
976 */
977 l_round_flag := p_get_mat_in_rec.rounding_control_flag;
978 l_inv_item_id := p_get_mat_in_rec.dest_inv_item_id;
979 -- time_phased_atp
980 l_pf_item_id := MSC_ATP_PVT.G_ITEM_INFO_REC.product_family_id;
981
982 IF PG_DEBUG in ('Y', 'C') THEN
983 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'l_round_flag = '|| l_round_flag);
984 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'l_inv_item_id = ' || l_inv_item_id);
985 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'l_pf_item_id = ' || l_pf_item_id);
986 END IF;
987
988 MSC_ATP_PROC.get_org_default_info(p_instance_id, p_organization_id,
989 l_default_atp_rule_id, l_calendar_code, l_calendar_exception_set_id,
990 l_default_demand_class, l_org_code);
991 -- Bug 3371817 - l_sys_next_date should be actually calculated using the calendar passed from ATP_Check
992 l_sys_next_date := MSC_CALENDAR.NEXT_WORK_DAY(
993 p_get_mat_in_rec.shipping_cal_code,
994 p_instance_id,
995 TRUNC(sysdate));
996 /*
997 BEGIN
998 SELECT cal.next_date
999 INTO l_sys_next_date
1000 FROM msc_calendar_dates cal
1001 WHERE cal.exception_set_id = l_calendar_exception_set_id
1002 AND cal.calendar_code = l_calendar_code
1003 AND cal.calendar_date = TRUNC(sysdate)
1004 AND cal.sr_instance_id = p_instance_id ;
1005 EXCEPTION
1006 WHEN OTHERS THEN
1007 RAISE NO_MATCHING_CAL_DATE;
1008 END;
1009 -- Bug 3371817 - Changes end.
1010 */
1011 IF PG_DEBUG in ('Y', 'C') THEN
1012 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'l_sys_next_date = ' || l_sys_next_date);
1013 END IF;
1014
1015 ---forward steal:subst
1016 IF NVL(p_substitution_window, 0) = 0 THEN
1017 l_substitution_end_date := l_infinite_time_fence_date;
1018 ELSE
1019 l_substitution_end_date := MSC_CALENDAR.DATE_OFFSET(
1020 p_organization_id,
1021 p_instance_id,
1022 1,
1023 --bug 3589115: Move to sysdate for past due dates
1024 greatest(p_requested_date, l_sys_next_date),
1025 p_substitution_window);
1026
1027 END IF;
1028
1029 IF PG_DEBUG in ('Y', 'C') THEN
1030 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'l_substitution_end_date := ' || l_substitution_end_date);
1031 END IF;
1032
1033 -- in case we want to support flex date
1034 l_requested_date := p_requested_date;
1035
1036 /* New allocation logic for time_phased_atp changes begin */
1037 IF l_time_phased_atp = 'Y' THEN
1038 IF p_requested_date <= p_atf_date THEN
1039 IF MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF = 'Y' THEN
1040 l_item_to_use := l_inv_item_id;
1041 ELSE
1042 l_item_to_use := l_pf_item_id;
1043 END IF;
1044 ELSE
1045 IF MSC_ATP_PVT.G_PF_RULE_OUTSIDE_ATF = 'Y' THEN
1046 l_item_to_use := l_pf_item_id;
1047 ELSE
1048 l_item_to_use := l_inv_item_id;
1049 END IF;
1050 END IF;
1051 ELSE
1052 l_item_to_use := l_inv_item_id;
1053 END IF;
1054
1055 IF PG_DEBUG in ('Y', 'C') THEN
1056 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'Item to be used = ' || l_item_to_use);
1057 END IF;
1058 /* New allocation logic for time_phased_atp changes end */
1059
1060 BEGIN
1061 -- Changes For bug 2384551 start
1062 IF MSC_ATP_PVT.G_HIERARCHY_PROFILE = 1 THEN
1063
1064 SELECT mv.priority, mv.level_id, mv.class, mv.partner_id
1065 INTO l_priority, l_level_id, l_class, l_partner_id
1066 FROM msc_item_hierarchy_mv mv
1067 WHERE mv.inventory_item_id = l_item_to_use /* New allocation logic for time_phased_atp changes*/
1068 AND mv.organization_id = p_organization_id
1069 AND mv.sr_instance_id = p_instance_id
1070 --bug 3589115: if allocation rule is not valid on request date then pick one applicable on sysdate
1071 AND GREATEST(p_requested_date, l_sys_next_date) BETWEEN effective_date AND disable_date
1072 AND mv.demand_class = p_demand_class
1073 AND mv.level_id = -1;
1074
1075 ELSE
1076
1077 SELECT mv.priority, mv.level_id, mv.class, mv.partner_id
1078 INTO l_priority, l_level_id, l_class, l_partner_id
1079 FROM msc_item_hierarchy_mv mv
1080 WHERE mv.inventory_item_id = l_item_to_use /* New allocation logic for time_phased_atp changes*/
1081 AND mv.organization_id = p_organization_id
1082 AND mv.sr_instance_id = p_instance_id
1083 --bug 3589115: if allocation rule is not valid on request date then pick one applicable on sysdate
1084 AND GREATEST(p_requested_date, l_sys_next_date) BETWEEN effective_date AND disable_date
1085 AND mv.demand_class = p_demand_class
1086 AND mv.level_id <> -1;
1087
1088 END IF;
1089 -- Changes For bug 2384551 end
1090 EXCEPTION
1091 WHEN NO_DATA_FOUND THEN
1092 l_priority := NULL;
1093 l_level_id := NULL;
1094 l_partner_id := NULL;
1095 l_class := NULL;
1096 END ;
1097
1098 IF PG_DEBUG in ('Y', 'C') THEN
1099 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'before lower priority dc for : ' || l_priority || ':'|| l_class);
1100 END IF;
1101
1102 -- Order by clause reversed as we'll add request demand class at the end and acces the list bottom-up.
1103
1104 SELECT mv.demand_class, mv.priority
1105 BULK COLLECT INTO l_demand_class_tab, l_demand_class_priority_tab
1106 FROM msc_item_hierarchy_mv mv
1107 WHERE mv.inventory_item_id = l_item_to_use /* New allocation logic for time_phased_atp changes*/
1108 AND mv.organization_id = p_organization_id
1109 AND mv.sr_instance_id = p_instance_id
1110 --bug 3589115: if allocation rule is not valid on request date then pick one applicable on sysdate
1111 AND GREATEST(p_requested_date, l_sys_next_date) BETWEEN effective_date AND disable_date
1112 AND mv.priority > l_priority
1113 AND mv.level_id = l_level_id
1114 ORDER BY mv.priority desc , mv.allocation_percent asc, mv.demand_class desc;
1115
1116 -- Add request demand class to the end of the arrays.
1117
1118 l_demand_class_tab.Extend;
1119 l_demand_class_priority_tab.Extend;
1120
1121 l_demand_class_tab(l_demand_class_tab.Count) := p_demand_class;
1122 l_demand_class_priority_tab(l_demand_class_priority_tab.Count) := l_priority;
1123
1124 IF PG_DEBUG in ('Y', 'C') THEN
1125 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'No. of DC : ' || l_demand_class_tab.Count);
1126 END IF;
1127
1128 -- Insert these demand classes in Global Temp Table to use in SELECT clause.
1129
1130 FORALL i IN l_demand_class_tab.FIRST..l_demand_class_tab.COUNT
1131 INSERT INTO msc_alloc_temp(demand_class)
1132 VALUES (l_demand_class_tab(i));
1133
1134 -- for performance, we dont support the s/d details for forward Scheduling in case of allocated ATP if
1135 -- forward stealing needs to be supported.
1136 -- since we don't need details, do a group by and select the sum in the sql statement.
1137
1138 -- Summary enhancement changes begin
1139 SELECT summary_flag
1140 INTO l_summary_flag
1141 FROM msc_plans plans
1142 WHERE plans.plan_id = p_plan_id;
1143
1144 IF PG_DEBUG in ('Y', 'C') THEN
1145 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'right before the huge select statement');
1146 END IF;
1147
1148 -- Check if full summary has been run - for summary enhancement
1149 IF MSC_ATP_PVT.G_SUMMARY_FLAG = 'Y' AND
1150 l_summary_flag NOT IN (MSC_POST_PRO.G_SF_SUMMARY_NOT_RUN, MSC_POST_PRO.G_SF_PREALLOC_COMPLETED,
1151 MSC_POST_PRO.G_SF_FULL_SUMMARY_RUNNING) THEN
1152
1153 -- time_phased_atp
1154 IF l_time_phased_atp = 'N' THEN
1155 IF PG_DEBUG in ('Y', 'C') THEN
1156 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'l_time_phased_atp := ' || l_time_phased_atp);
1157 END IF;
1158
1159 -- Summary SQL can be used
1160 SELECT SD_DATE,
1161 SUM(SD_QTY),
1162 DEMAND_CLASS
1163 BULK COLLECT INTO
1164 l_atp_period_tab,
1165 l_atp_qty_tab,
1166 l_atp_dc_tab
1167 FROM
1168 (
1169 SELECT /*+ INDEX(S MSC_ATP_SUMMARY_SD_U1) */
1170 SD_DATE, SD_QTY, DEMAND_CLASS
1171 FROM MSC_ATP_SUMMARY_SD S
1172 WHERE S.PLAN_ID = p_plan_id
1173 AND S.SR_INSTANCE_ID = p_instance_id
1174 AND S.INVENTORY_ITEM_ID = l_inv_item_id
1175 AND S.ORGANIZATION_ID = p_organization_id
1176 AND S.DEMAND_CLASS IN (
1177 SELECT demand_class
1178 FROM msc_alloc_temp
1179 WHERE demand_class IS NOT NULL)
1180 AND S.SD_DATE < l_infinite_time_fence_date
1181
1182 UNION ALL
1183
1184 SELECT TRUNC(AD.DEMAND_DATE) SD_DATE,
1185 decode(AD.ALLOCATED_QUANTITY, -- Consider unscheduled orders as dummy supplies
1186 0, nvl(OLD_ALLOCATED_QUANTITY,0), --4658238 -- For summary enhancement
1187 -1 * AD.ALLOCATED_QUANTITY) SD_QTY,
1188 AD.DEMAND_CLASS
1189 FROM MSC_ALLOC_DEMANDS AD,
1190 MSC_PLANS P -- For summary enhancement
1191 WHERE AD.PLAN_ID = p_plan_id
1192 AND AD.SR_INSTANCE_ID = p_instance_id
1193 AND AD.INVENTORY_ITEM_ID = l_inv_item_id
1194 AND AD.ORGANIZATION_ID = p_organization_id
1195 AND AD.DEMAND_CLASS IN (
1196 SELECT demand_class
1197 FROM msc_alloc_temp
1198 WHERE demand_class IS NOT NULL)
1199 --bug3693892 added trunc
1200 AND trunc(AD.DEMAND_DATE) < l_infinite_time_fence_date
1201 AND P.PLAN_ID = AD.PLAN_ID
1202 AND (AD.REFRESH_NUMBER > P.LATEST_REFRESH_NUMBER
1203 OR AD.REFRESH_NUMBER = p_refresh_number)
1204
1205 UNION ALL
1206
1207 SELECT TRUNC(SA.SUPPLY_DATE) SD_DATE,
1208 decode(SA.ALLOCATED_QUANTITY, -- Consider deleted stealing records as dummy demands
1209 0, -1 * (NVL(OLD_ALLOCATED_QUANTITY,0)), -- For summary enhancement --5283809
1210 SA.ALLOCATED_QUANTITY) SD_QTY ,
1211 SA.DEMAND_CLASS
1212 FROM MSC_ALLOC_SUPPLIES SA,
1213 MSC_PLANS P -- For summary enhancement
1214 WHERE SA.PLAN_ID = p_plan_id
1215 AND SA.SR_INSTANCE_ID = p_instance_id
1216 AND SA.INVENTORY_ITEM_ID = l_inv_item_id
1217 AND SA.ORGANIZATION_ID = p_organization_id
1218 AND SA.DEMAND_CLASS IN (
1219 SELECT demand_class
1220 FROM msc_alloc_temp
1221 WHERE demand_class IS NOT NULL)
1222 --bug3693892 added trunc
1223 AND trunc(SA.SUPPLY_DATE) < l_infinite_time_fence_date
1224 AND P.PLAN_ID = SA.PLAN_ID
1225 AND (SA.REFRESH_NUMBER > P.LATEST_REFRESH_NUMBER
1226 OR SA.REFRESH_NUMBER = p_refresh_number)
1227 )
1228 GROUP BY DEMAND_CLASS, SD_DATE
1229 ORDER BY DEMAND_CLASS, SD_DATE;--4698199 --5353882
1230
1231 ELSE -- IF Not_PF_Case THEN
1232
1233 IF PG_DEBUG in ('Y', 'C') THEN
1234 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'l_time_phased_atp := ' || l_time_phased_atp);
1235 END IF;
1236
1237 MSC_ATP_PF.Get_Forward_Mat_Pf_Summ(
1238 l_inv_item_id,
1239 l_pf_item_id,
1240 p_organization_id,
1241 p_instance_id,
1242 p_plan_id,
1243 l_infinite_time_fence_date,
1244 p_refresh_number,
1245 l_atp_period_tab,
1246 l_atp_qty_tab,
1247 l_atp_dc_tab,
1248 l_return_status
1249 );
1250 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1251 IF PG_DEBUG in ('Y', 'C') THEN
1252 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'Error occured in procedure Get_Forward_Mat_Pf_Summ');
1253 END IF;
1254 RAISE FND_API.G_EXC_ERROR;
1255 END IF;
1256
1257 END IF; -- IF Not_PF_Case THEN
1258
1259 ELSE
1260 -- time_phased_atp
1261 IF l_time_phased_atp = 'N' THEN
1262 IF PG_DEBUG in ('Y', 'C') THEN
1263 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'l_time_phased_atp := ' || l_time_phased_atp);
1264 END IF;
1265
1266 -- Use the SQL for non summary case
1267 SELECT SD_DATE,
1268 SUM(SD_QTY),
1269 DEMAND_CLASS
1270 BULK COLLECT INTO
1271 l_atp_period_tab,
1272 l_atp_qty_tab,
1273 l_atp_dc_tab
1274 FROM
1275 (
1276 SELECT TRUNC(AD.DEMAND_DATE) SD_DATE,
1277 -1 * AD.ALLOCATED_QUANTITY SD_QTY,
1278 AD.DEMAND_CLASS
1279 FROM MSC_ALLOC_DEMANDS AD
1280 WHERE AD.PLAN_ID = p_plan_id
1281 AND AD.SR_INSTANCE_ID = p_instance_id
1282 AND AD.INVENTORY_ITEM_ID = l_inv_item_id
1283 AND AD.ORGANIZATION_ID = p_organization_id
1284 AND AD.ORIGINATION_TYPE <> 52 -- Ignore copy SO and copy stealing records for summary enhancement
1285 AND AD.DEMAND_CLASS IN (
1286 SELECT demand_class
1287 FROM msc_alloc_temp
1288 WHERE demand_class IS NOT NULL)
1289 --bug3693892 added trunc
1290 AND trunc(AD.DEMAND_DATE) < l_infinite_time_fence_date
1291
1292 UNION ALL
1293
1294 SELECT TRUNC(SA.SUPPLY_DATE) SD_DATE,
1295 SA.ALLOCATED_QUANTITY SD_QTY,
1296 SA.DEMAND_CLASS
1297 FROM MSC_ALLOC_SUPPLIES SA
1298 WHERE SA.PLAN_ID = p_plan_id
1299 AND SA.SR_INSTANCE_ID = p_instance_id
1300 AND SA.INVENTORY_ITEM_ID = l_inv_item_id
1301 AND SA.ORGANIZATION_ID = p_organization_id
1302 AND SA.ALLOCATED_QUANTITY <> 0
1303 -- fixed as part of time_phased_atp chagnes
1304 AND TRUNC(SA.SUPPLY_DATE) >= DECODE(SA.ORIGINAL_ORDER_TYPE,
1305 27, TRUNC(SYSDATE),
1306 28, TRUNC(SYSDATE),
1307 TRUNC(SA.SUPPLY_DATE))
1308 AND SA.DEMAND_CLASS IN (
1309 SELECT demand_class
1310 FROM msc_alloc_temp
1311 WHERE demand_class IS NOT NULL)
1312 --bug3693892 added trunc
1313 AND trunc(SA.SUPPLY_DATE) < l_infinite_time_fence_date
1314 )
1315 GROUP BY DEMAND_CLASS, SD_DATE
1316 ORDER BY DEMAND_CLASS, SD_DATE;--4698199 --5353882
1317
1318 ELSE -- IF Not_PF_Case THEN
1319
1320 IF PG_DEBUG in ('Y', 'C') THEN
1321 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'l_time_phased_atp := ' || l_time_phased_atp);
1322 END IF;
1323
1324 MSC_ATP_PF.Get_Forward_Mat_Pf(
1325 l_inv_item_id,
1326 l_pf_item_id,
1327 p_organization_id,
1328 p_instance_id,
1329 p_plan_id,
1330 l_infinite_time_fence_date,
1331 l_atp_period_tab,
1332 l_atp_qty_tab,
1333 l_atp_dc_tab,
1334 l_return_status
1335 );
1336 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1337 IF PG_DEBUG in ('Y', 'C') THEN
1338 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'Error occured in procedure Get_Forward_Mat_Pf');
1339 END IF;
1340 RAISE FND_API.G_EXC_ERROR;
1341 END IF;
1342
1343 END IF; -- IF Not_PF_Case THEN
1344
1345 END IF;
1346
1347
1348 IF PG_DEBUG in ('Y', 'C') THEN
1349 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'after huge select, l_atp_period_tab.COUNT : ' || l_atp_period_tab.COUNT);
1350 END IF;
1351
1352 IF l_atp_period_tab.COUNT = 0 THEN
1353 -- need to add error message
1354 --RAISE NO_DATA_FOUND;
1355 null;
1356 END IF;
1357
1358 -- Bug 3344138 initialize x_requested_date_quantity
1359 x_requested_date_quantity := 0;
1360
1361 IF PG_DEBUG in ('Y', 'C') THEN
1362 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'before the atp_alloc_consume');
1363 END IF;
1364
1365 -- do the backward consumption and accumulation
1366
1367 IF l_atp_period_tab.COUNT > 0 THEN
1368
1369 FOR i IN 1..l_atp_period_tab.COUNT LOOP
1370 IF PG_DEBUG in ('Y', 'C') THEN
1371 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || l_atp_period_tab(i) ||':'||l_atp_dc_tab(i) ||':' || l_atp_qty_tab(i));
1372 END IF;
1373 END LOOP;
1374
1375 -- time_phased_atp
1376 IF l_time_phased_atp = 'Y' THEN
1377 MSC_ATP_PF.Pf_Atp_Alloc_Consume(
1378 l_atp_qty_tab,
1379 l_atp_period_tab,
1380 l_atp_dc_tab,
1381 p_atf_date,
1382 l_dc_list_tab,
1383 l_dc_start_index,
1384 l_dc_end_index,
1385 l_return_status
1386 );
1387 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1388 IF PG_DEBUG in ('Y', 'C') THEN
1389 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'Error occured in procedure Pf_Atp_Alloc_Consume');
1390 END IF;
1391 RAISE FND_API.G_EXC_ERROR;
1392 END IF;
1393 ELSE
1394 atp_alloc_consume(l_atp_qty_tab, l_atp_dc_tab, l_dc_list_tab, l_dc_start_index, l_dc_end_index);
1395 END IF;
1396
1397 IF PG_DEBUG in ('Y', 'C') THEN
1398 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'after the atp_alloc_consume');
1399 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'l_dc_list_tab.count = '||l_dc_list_tab.COUNT);
1400
1401 FOR i IN 1..l_atp_period_tab.COUNT LOOP
1402 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || l_atp_period_tab(i) ||':'
1403 ||l_atp_dc_tab(i) ||':' || l_atp_qty_tab(i));
1404 END LOOP;
1405 END IF;
1406
1407 /* Cum drop issue changes begin*/
1408 MSC_AATP_PROC.Atp_Remove_Negatives(l_atp_qty_tab, l_return_status);
1409 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1410 IF PG_DEBUG in ('Y', 'C') THEN
1411 msc_sch_wb.atp_debug('Item_Pre_Allocated_Atp: ' || 'Error occured in procedure Atp_Remove_Negatives');
1412 END IF;
1413 RAISE FND_API.G_EXC_ERROR;
1414 END IF;
1415 /* Cum drop issue changes end*/
1416
1417
1418 -- Insert all the records in l_atp_period_tab in a temp table. Then we'll select
1419 -- all these dates in another pl/sql table. This is done to make sure we move thru
1420 -- data from various demand classes in correct order of dates as these may be misaligned.
1421
1422 FORALL i IN l_atp_period_tab.FIRST..l_atp_period_tab.COUNT
1423 INSERT INTO msc_alloc_temp(supply_demand_date)
1424 VALUES (l_atp_period_tab(i));
1425
1426 SELECT supply_demand_date --sd_date
1427 BULK COLLECT INTO
1428 l_period_tab
1429 FROM
1430 (
1431 SELECT DISTINCT supply_demand_date --sd_date
1432 FROM msc_alloc_temp
1433 WHERE supply_demand_date IS NOT NULL
1434 --- for substitution we want to consider supplies only within substitution widow
1435 --- filter out dates after substitution window
1436 and supply_demand_date <= l_substitution_end_date
1437 ORDER BY supply_demand_date --sd_date
1438 );
1439
1440 IF PG_DEBUG in ('Y', 'C') THEN
1441 FOR i IN 1..l_period_tab.COUNT LOOP
1442 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || l_period_tab(i));
1443 END LOOP;
1444 END IF;
1445
1446 -- if requested date is eariler than sysdate, we have an issue here.
1447 -- this is possible since we have the offset from requested arrival
1448 -- date. if requested date is eariler than sysdate, we should set
1449 -- the x_requested_date_quantity = 0, and find the atp date and
1450 -- quantity from sysdate.
1451
1452 -- we use this l_atp_requested_date to do the search
1453 l_atp_requested_date := GREATEST(l_requested_date, trunc(l_sys_next_date));
1454
1455 IF PG_DEBUG in ('Y', 'C') THEN
1456 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'G_PTF_DATE:= ' || MSC_ATP_PVT.G_PTF_DATE);
1457 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'l_atp_requested_date = ' || l_atp_requested_date);
1458 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'p_level = ' || p_level);
1459 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'l_period_tab = ' || l_period_tab.COUNT);
1460 END IF;
1461
1462 l_atp_quantity := 0;
1463 --x_requested_date_quantity := 0; -- Bug 3344138
1464 x_atf_date_quantity := 0; --bug3409973
1465 x_used_available_quantity := 0; --bug3409973
1466
1467 FOR k IN 1..l_period_tab.COUNT LOOP
1468
1469 l_atp_quantity := 0;
1470 l_stealing_flag := 'N';
1471 l_atf_quantity := 0; -- time_phased_atp
1472
1473 -- time_phased_atp changes begin
1474 IF l_time_phased_atp = 'Y' THEN
1475 IF (l_period_tab(k) <= p_atf_date) and (k <> l_period_tab.COUNT) and (l_period_tab(k+1) > p_atf_date) THEN
1476 /* We have found counter k for ATF date*/
1477 k_atf := k;
1478 ELSIF (k = 1) and (l_period_tab(k) > p_atf_date) THEN
1479 k_atf := 0;
1480 END IF;
1481 IF PG_DEBUG in ('Y', 'C') THEN
1482 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ============================');
1483 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'k : ' || k);
1484 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'l_period_tab(k) : ' || l_period_tab(k));
1485 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'p_atf_date : ' || p_atf_date);
1486 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'k_atf : ' || k_atf);
1487 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ============================');
1488 END IF;
1489 END IF;
1490 -- time_phased_atp changes end
1491
1492 -- Reset following variables before start of following Loop
1493
1494 --l_used_dc_tab.TRIM(l_used_dc_tab(l_used_dc_tab.COUNT));
1495 --l_used_dc_qty.TRIM(l_used_dc_qty(l_used_dc_qty.COUNT));
1496
1497 IF PG_DEBUG in ('Y', 'C') THEN
1498 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'before trim = ' || l_used_dc_tab.COUNT);
1499 END IF;
1500 l_used_dc_tab.TRIM(l_used_dc_tab.COUNT);
1501 l_used_dc_qty.TRIM(l_used_dc_qty.COUNT);
1502 -- time_phased_atp
1503 IF l_time_phased_atp = 'Y' THEN
1504 l_used_dc_mem_qty.TRIM(l_used_dc_mem_qty.COUNT);
1505 END IF;
1506 IF PG_DEBUG in ('Y', 'C') THEN
1507 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'after trim = ' || l_used_dc_tab.COUNT);
1508 END IF;
1509
1510 FOR i in REVERSE 1..l_demand_class_tab.COUNT LOOP
1511 IF PG_DEBUG in ('Y', 'C') THEN
1512 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'before j = ' || l_demand_class_tab(i));
1513 END IF;
1514 n := 0;
1515 FOR j IN 1..l_dc_list_tab.COUNT LOOP
1516 IF PG_DEBUG in ('Y', 'C') THEN
1517 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'inside j = ' || l_dc_list_tab(j));
1518 END IF;
1519
1520 IF l_dc_list_tab(j) = l_demand_class_tab(i) THEN
1521 IF PG_DEBUG in ('Y', 'C') THEN
1522 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'inside IF = ' || j);
1523 END IF;
1524 n := j;
1525 EXIT;
1526 END IF;
1527 END LOOP; --FOR j IN 1..l_dc_list_tab.COUNT LOOP
1528
1529 IF PG_DEBUG in ('Y', 'C') THEN
1530 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'after loop = ' || j);
1531 END IF;
1532 IF NVL(n, 0) > 0 THEN
1533
1534 j := n;
1535 IF PG_DEBUG in ('Y', 'C') THEN
1536 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'before m = ' || l_dc_list_tab(j));
1537 END IF;
1538
1539 -- Reset m_atf
1540 m_atf := NULL;
1541
1542 FOR m IN l_dc_start_index(j)..l_dc_end_index(j) LOOP
1543
1544 /* time_phased_atp
1545 We were not handling cases where there are holes (i.e. all demand classes do
1546 not have records on all distinct dates) correctly while forward stealing.
1547 Fixed this issue as part of time phased atp changes*/
1548 --IF (l_atp_period_tab(m) = l_period_tab(k)) AND (l_atp_qty_tab(m) > 0) THEN
1549
1550 --bug 3443276: We are returning a date previuos to request date
1551 --if that date has an availability. We should be returning atleast request date
1552 IF ( (l_atp_period_tab(m) =
1553 GREATEST(l_period_tab(k), MSC_ATP_PVT.G_PTF_DATE, l_atp_requested_date))
1554 OR ( ( l_atp_period_tab(m) <
1555 GREATEST(MSC_ATP_PVT.G_PTF_DATE, l_period_tab(k), l_atp_requested_date) )
1556 AND ( ( ( m < l_dc_end_index(j) )
1557 AND ( l_atp_period_tab(m+1) >
1558 GREATEST(MSC_ATP_PVT.G_PTF_DATE,l_period_tab(k), l_atp_requested_date) )
1559 )
1560 OR ( m = l_dc_end_index(j) )
1561 )
1562 )
1563 )
1564 AND (l_atp_qty_tab(m) > 0)
1565 THEN
1566 l_used_dc_tab.EXTEND;
1567 l_used_dc_qty.EXTEND;
1568 l_used_dc_tab(l_used_dc_tab.COUNT) := l_dc_list_tab(j);
1569 l_used_dc_qty(l_used_dc_qty.COUNT) := l_atp_qty_tab(m);
1570
1571 -- time_phased_atp changes begin
1572 IF l_time_phased_atp = 'Y' THEN
1573 l_used_dc_mem_qty.EXTEND;
1574 IF (k_atf is null) OR (m_atf is null) THEN
1575 /* we are using qty within ATF completely*/
1576 l_used_dc_mem_qty(l_used_dc_mem_qty.COUNT) := l_atp_qty_tab(m);
1577 ELSIF k_atf = 0 THEN
1578 /* we are using qty outside ATF completely*/
1579 l_used_dc_mem_qty(l_used_dc_mem_qty.COUNT) := 0;
1580 ELSE
1581 /* we are using qty within and outside ATF*/
1582 l_used_dc_mem_qty(l_used_dc_mem_qty.COUNT) := l_atp_qty_tab(m_atf);
1583 END IF;
1584 IF PG_DEBUG in ('Y', 'C') THEN
1585 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: <<<<<<<<<<<<< >>>>>>>>>>>>>');
1586 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'k_atf = ' || k_atf);
1587 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'l_used_dc_mem_qty('
1588 || l_used_dc_mem_qty.COUNT || ') = ' || l_used_dc_mem_qty(l_used_dc_mem_qty.COUNT));
1589 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: <<<<<<<<<<<<< >>>>>>>>>>>>>');
1590 END IF;
1591 END IF;
1592 -- time_phased_atp changes end
1593
1594 IF l_used_dc_tab(l_used_dc_tab.COUNT) <> p_demand_class THEN
1595 l_stealing_flag := 'Y';
1596 END IF; --IF l_used_dc_tab(l_used_dc_tab.COUNT) <> p_demand_class THEN
1597
1598 IF PG_DEBUG in ('Y', 'C') THEN
1599 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'before l_atp_quantity = ' || l_atp_quantity);
1600 msc_sch_wb.atp_debug('Used DC qty := ' || l_used_dc_qty(l_used_dc_qty.COUNT));
1601 END IF;
1602
1603 IF l_atp_quantity + l_used_dc_qty(l_used_dc_qty.COUNT) >= p_quantity_ordered THEN
1604
1605 -- time_phased_atp changes begin
1606 IF l_time_phased_atp = 'Y' THEN
1607 /*IF l_atp_quantity + l_used_dc_mem_qty(l_used_dc_mem_qty.COUNT) >= p_quantity_ordered THEN
1608 l_atf_quantity := l_atf_quantity + p_quantity_ordered - l_atp_quantity;
1609 l_used_dc_mem_qty(l_used_dc_mem_qty.COUNT) := p_quantity_ordered - l_atp_quantity;
1610 ELSE*/
1611 l_atf_quantity := l_atf_quantity + l_used_dc_mem_qty(l_used_dc_mem_qty.COUNT);
1612 /*END IF;*/
1613 END IF;
1614 -- time_phased_atp changes end
1615
1616 l_atp_quantity := l_atp_quantity + l_atp_qty_tab(m);
1617
1618 IF (l_round_flag = 1) THEN
1619 x_atp_date_quantity_this_level := FLOOR(l_atp_quantity);
1620 ELSE
1621 x_atp_date_quantity_this_level := l_atp_quantity;
1622 END IF;
1623
1624 --this condition is not needed any more as we check
1625 -- this condition in 'IF' logic to come here
1626
1627 /* IF l_period_tab(k) >= MSC_ATP_PVT.G_PTF_DATE OR
1628 (k < l_period_tab.count and l_period_tab(k+1) > MSC_ATP_PVT.G_PTF_DATE)
1629 THEN
1630 */
1631 --3443276: we need to return date > req date
1632 x_atp_date_this_level := GREATEST(l_period_tab(k),MSC_ATP_PVT.G_PTF_DATE, l_atp_requested_date);
1633 IF PG_DEBUG in ('Y', 'C') THEN
1634 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || x_atp_date_quantity_this_level);
1635 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || x_atp_date_this_level);
1636 END IF;
1637
1638 EXIT;
1639 --END IF;
1640
1641 ELSE
1642 l_atp_quantity := l_atp_quantity + l_atp_qty_tab(m);
1643 -- time_phased_atp changes begin
1644 IF l_time_phased_atp = 'Y' THEN
1645 l_atf_quantity := l_atf_quantity + l_used_dc_mem_qty(l_used_dc_mem_qty.COUNT);
1646 END IF;
1647 -- time_phased_atp changes end
1648 END IF;
1649
1650 IF PG_DEBUG in ('Y', 'C') THEN
1651 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'after l_atp_quantity = ' || l_atp_quantity);
1652 END IF;
1653
1654 ELSIF (m_atf is null) AND (k_atf is not null) AND (k_atf <> 0)
1655 AND ((l_atp_period_tab(m) = l_period_tab(k_atf))
1656 OR ( ( l_atp_period_tab(m) < l_period_tab(k_atf) )
1657 AND ( ( ( m < l_dc_end_index(j) )
1658 AND ( l_atp_period_tab(m+1) > l_period_tab(k_atf) )
1659 )
1660 OR ( m = l_dc_end_index(j) )
1661 )
1662 )
1663 )
1664 THEN
1665 /* Set m_atf*/
1666 m_atf := m;
1667 END IF; --IF l_atp_period_tab(j)) = l_period_tab(k) THEN
1668
1669 END LOOP; --FOR m IN 1..l_dc_start_index(j)..l_dc_end_index(j) LOOP
1670
1671 IF x_atp_date_this_level IS NOT NULL THEN
1672 EXIT;
1673 END IF;
1674
1675 END IF; --IF NVL(n, 0) > 0 THEN
1676 END LOOP; --FOR i in l_demand_class_tab.COUNT..1 LOOP
1677
1678 -- time_phased_atp
1679 -- x_atf_date_quantity := l_atf_quantity;
1680 -- let say the next period is on Day5 but our request in on Day2.
1681
1682 IF PG_DEBUG in ('Y', 'C') THEN
1683 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'l_period_tab : ' || l_period_tab(k));
1684 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'l_period_tab.count : ' || l_period_tab.COUNT);
1685 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'k : ' || k);
1686 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'l_atp_requested_date : ' || l_atp_requested_date);
1687 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'l_atp_quantity : ' || l_atp_quantity);
1688 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'l_atf_quantity : ' || l_atf_quantity);
1689 END IF;
1690
1691 IF k < l_period_tab.COUNT THEN
1692 IF PG_DEBUG in ('Y', 'C') THEN
1693 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'l_period_tab (k+1) : ' || l_period_tab(k+1));
1694 END IF;
1695 IF (l_atp_requested_date >= l_period_tab(k)) AND
1696 (l_atp_requested_date < l_period_tab(k + 1)) THEN
1697 x_requested_date_quantity := l_atp_quantity;
1698 END IF;
1699 ELSE
1700 IF (l_atp_requested_date >= l_period_tab(k)) AND x_requested_date_quantity = 0 THEN
1701 x_requested_date_quantity := l_atp_quantity;
1702 END IF;
1703 END IF; --IF k < l_period_tab.COUNT THEN
1704
1705 -- dsting 2807614
1706 IF l_atp_requested_date > l_requested_date THEN
1707 IF PG_DEBUG in ('Y', 'C') THEN
1708 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: l_requested_date < l_atp_requested_date');
1709 END IF;
1710 x_requested_date_quantity := 0;
1711 END IF;
1712
1713 IF PG_DEBUG in ('Y', 'C') THEN
1714 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'x_requested_date_quantity : ' || x_requested_date_quantity);
1715 END IF;
1716
1717 IF x_atp_date_this_level IS NOT NULL THEN
1718 EXIT;
1719 END IF;
1720
1721 END LOOP; --FOR k IN 1..l_period_tab.COUNT LOOP
1722 END IF;
1723 --bug3409973 start
1724 IF l_time_phased_atp = 'Y' THEN
1725 FOR i IN 1..l_used_dc_qty.COUNT LOOP
1726
1727 l_stealing_quantity := LEAST(l_used_dc_qty(i), (p_quantity_ordered - NVL(l_total_atp_qty, 0)));
1728 l_total_atp_qty := NVL(l_total_atp_qty, 0) + l_stealing_quantity;
1729
1730 IF PG_DEBUG in ('Y', 'C') THEN
1731 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'l_used_dc_qty(i) : ' || l_used_dc_qty(i));
1732 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'l_stealing_quantity : ' || l_stealing_quantity);
1733 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'l_total_atp_qty : ' || l_total_atp_qty);
1734 END IF;
1735
1736 IF p_demand_class <> l_used_dc_tab(i) THEN
1737
1738 l_pf_stealing_qty := LEAST(l_used_dc_qty(i)-NVL(l_used_dc_mem_qty(i), 0), l_stealing_quantity);
1739 l_mem_stealing_qty := l_stealing_quantity - l_pf_stealing_qty;
1740 x_atf_date_quantity := x_atf_date_quantity + l_mem_stealing_qty ;
1741 x_used_available_quantity := x_used_available_quantity + l_stealing_quantity;
1742
1743 IF PG_DEBUG in ('Y', 'C') THEN
1744 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'l_pf_stealing_qty : ' || l_pf_stealing_qty);
1745 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'l_mem_stealing_qty : ' || l_mem_stealing_qty);
1746 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'x_atf_date_quantity : ' || x_atf_date_quantity);
1747 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'x_used_available_quantity : ' || x_used_available_quantity);
1748 END IF;
1749
1750 ELSE
1751 x_atf_date_quantity := x_atf_date_quantity + NVL(l_used_dc_mem_qty(i), 0);
1752 x_used_available_quantity := x_used_available_quantity + NVL(l_used_dc_qty(i),0);
1753
1754 IF PG_DEBUG in ('Y', 'C') THEN
1755 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'x_atf_date_quantity :Else ' || x_atf_date_quantity);
1756 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'x_used_available_quantity :Else ' || x_used_available_quantity );
1757 END IF;
1758 END IF;
1759 END LOOP; --FOR i IN 1..l_used_dc_qty.COUNT LOOP
1760 END IF;
1761
1762 --bug3409973 end
1763 -- Delete data from table, in case we re-visit this table within same transaction
1764 -- as this is a transaction specific global temporary table.
1765
1766 DELETE msc_alloc_temp;
1767
1768 IF PG_DEBUG in ('Y', 'C') THEN
1769 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'before setting pegging for : ' || p_item_name);
1770 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'x_atf_date_quantity : ' || x_atf_date_quantity);
1771 END IF;
1772 -- Insert pegging record for Supply/ Stealing.
1773
1774 l_pegging_rec.session_id:= MSC_ATP_PVT.G_SESSION_ID;
1775 l_pegging_rec.order_line_id:= MSC_ATP_PVT.G_ORDER_LINE_ID;
1776 l_pegging_rec.organization_id:= p_organization_id;
1777 l_pegging_rec.organization_code := l_org_code;
1778 l_pegging_rec.identifier1:= p_instance_id;
1779 l_pegging_rec.identifier2 := p_plan_id;
1780
1781 -- time_phased_atp changes begin
1782 IF l_time_phased_atp = 'Y' THEN
1783 l_pegging_rec.inventory_item_id:= p_request_item_id;
1784 l_pegging_rec.inventory_item_name := p_item_name;
1785 ELSE
1786 l_pegging_rec.inventory_item_id:= p_inventory_item_id;
1787 l_pegging_rec.inventory_item_name := p_family_item_name;
1788 END IF;
1789 -- time_phased_atp changes end
1790
1791 l_pegging_rec.resource_id := NULL;
1792 l_pegging_rec.resource_code := NULL;
1793 l_pegging_rec.department_id := NULL;
1794 l_pegging_rec.department_code := NULL;
1795 l_pegging_rec.supplier_id := NULL;
1796 l_pegging_rec.supplier_name := NULL;
1797 l_pegging_rec.supplier_site_id := NULL;
1798 l_pegging_rec.supplier_site_name := NULL;
1799 l_pegging_rec.scenario_id:= p_scenario_id;
1800 l_pegging_rec.component_identifier := MSC_ATP_PVT.G_COMP_LINE_ID;
1801
1802 l_pegging_rec.constraint_flag := 'N';
1803
1804 --- Enhance CTO Phase 1 Req #17
1805 -- Support Forward Stealing for components of ATO model in
1806 -- Model's sourcing organization.
1807 IF NVL(p_parent_pegging_id, 0) <> 0 THEN
1808
1809 -- First add the demand record for the requirement in future.
1810
1811 l_atp_rec.quantity_ordered := p_quantity_ordered;
1812 l_atp_rec.requested_ship_date := p_requested_date;
1813
1814 IF PG_DEBUG in ('Y', 'C') THEN
1815 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'l_atp_rec.quantity_ordered ='|| p_quantity_ordered);
1816 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'l_atp_rec.requested_ship_date ='|| p_requested_date);
1817 END IF;
1818 --S-Cto_rearch: all demands are now dependent demand
1819 --l_atp_rec.origination_type := 30;
1820 l_atp_rec.origination_type := 1;
1821
1822 -- Bug 3148248 fixed as part of time_phased_atp changes
1823 -- Use destination inventory_item_id
1824 --l_atp_rec.inventory_item_id := p_inventory_item_id;
1825 --l_atp_rec.request_item_id := p_inventory_item_id;
1826 l_atp_rec.inventory_item_id := l_pf_item_id;
1827 l_atp_rec.request_item_id := l_inv_item_id;
1828 l_atp_rec.atf_date := p_atf_date;
1829 l_atp_rec.requested_date_quantity := x_requested_date_quantity;
1830 l_atp_rec.atf_date_quantity := x_atf_date_quantity;
1831 -- time_phased_atp changes end
1832
1833 l_atp_rec.organization_id := p_organization_id;
1834 l_atp_rec.demand_source_line := MSC_ATP_PVT.G_COMP_LINE_ID ;
1835 l_atp_rec.instance_id := p_instance_id;
1836 l_atp_rec.demand_class := p_demand_class;
1837 l_atp_rec.refresh_number := p_refresh_number;
1838 l_atp_rec.order_number := p_order_number;
1839 l_atp_rec.identifier := p_identifier;
1840 --l_atp_rec.demand_source_type := 2;
1841 l_atp_rec.demand_source_type := p_demand_source_type;--cmro
1842 l_atp_rec.demand_source_header_id := -1;
1843
1844 MSC_ATP_DB_UTILS.Add_Mat_Demand(l_atp_rec,
1845 p_plan_id,
1846 1, -- Demand Class True for forward stealing.
1847 l_demand_id);
1848
1849 IF PG_DEBUG in ('Y', 'C') THEN
1850 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'after calling Add_Mat_Demand, l_demand_id ='||
1851 l_demand_id);
1852 END IF;
1853
1854 --bug 3432341: pass demand id back to calling module
1855 x_get_mat_out_rec.demand_id := l_demand_id;
1856
1857 -- For component forward stealing need to add demand pegging.
1858 IF PG_DEBUG in ('Y', 'C') THEN
1859 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'Before Adding Demand Pegging for '|| p_item_name);
1860 END IF;
1861
1862 l_pegging_rec.parent_pegging_id:= p_parent_pegging_id;
1863 l_pegging_rec.atp_level:= p_level + 1;
1864 l_pegging_rec.identifier3 := l_demand_id;
1865 l_pegging_rec.supply_demand_source_type:= 6;
1866 l_pegging_rec.supply_demand_type := 1;
1867 l_pegging_rec.supply_demand_quantity:= p_quantity_ordered ;
1868 l_pegging_rec.supply_demand_date:= p_requested_date;
1869
1870 --- bug 2152184: For PF based ATP inventory_item_id field contains id for PF item
1871 --- cto looks at pegging tree to place their demands. Since CTO expects to find
1872 -- id for the requested item, we add the following column.
1873 -- CTO will now read from this column. For CTO components PF item same as request one.
1874 --l_pegging_rec.request_item_id := p_inventory_item_id;
1875 l_pegging_rec.request_item_id := p_request_item_id; -- time_phased_atp
1876
1877 IF PG_DEBUG in ('Y', 'C') THEN
1878 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'l_pegging_rec.supply_demand_quantity:= : ' || p_quantity_ordered);
1879 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'l_pegging_rec.supply_demand_date:= : ' || p_requested_date);
1880 END IF;
1881
1882 --diag_atp
1883 l_pegging_rec.pegging_type := MSC_ATP_PVT.ORG_DEMAND;
1884 l_pegging_rec.dest_inv_item_id := l_inv_item_id;
1885 l_pegging_rec.summary_flag := MSC_ATP_PVT.G_SUMMARY_FLAG; -- for summary enhancement
1886 l_pegging_rec.required_date := p_requested_date;
1887 --bug 3328421:
1888 l_pegging_rec.actual_supply_demand_date := p_requested_date;
1889 l_pegging_rec.demand_class := p_demand_class;
1890
1891 IF (p_get_mat_in_rec.parent_bom_item_type in (1, 4) and p_get_mat_in_rec.parent_repl_order_flag = 'Y')
1892 --parent is model entity
1893 OR (p_get_mat_in_rec.bom_item_type in (1, 4) and p_get_mat_in_rec.replenish_to_ord_flag = 'Y') THEN
1894 l_pegging_rec.model_sd_flag := 1;
1895 END IF;
1896 MSC_ATP_DB_UTILS.Add_Pegging(l_pegging_rec, l_demand_pegging_id);
1897
1898 --s_cto_rearch: pass demand pegg back just in case PO date is better than Sch. rec. date
1899 x_get_mat_out_rec.demand_pegging_id := l_demand_pegging_id;
1900
1901 MSC_ATP_PVT.G_CTO_FORWARD_DMD_PEG := l_demand_pegging_id;
1902 IF PG_DEBUG in ('Y', 'C') THEN
1903 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'after Add_Pegging : ' || l_demand_pegging_id);
1904 END IF;
1905
1906 END IF;
1907 --- End Enhance CTO Phase 1 Req #17
1908
1909
1910 IF PG_DEBUG in ('Y', 'C') THEN
1911 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'before setting pegging for : ' || p_item_name);
1912 END IF;
1913 -- Insert pegging record for Supply/ Stealing.
1914
1915 --- Enhance CTO Phase 1 Req #17
1916 -- Support Forward Stealing for components of ATO model in
1917 -- Model's sourcing organization.
1918 IF NVL(p_parent_pegging_id, 0) = 0 THEN
1919 l_pegging_rec.parent_pegging_id:= MSC_ATP_PVT.G_DEMAND_PEGGING_ID;
1920 l_pegging_rec.atp_level:= p_level + 1;
1921 ELSE
1922 -- Stealing may happen at lower levels also
1923 -- not just for the requested item in MATO cases.
1924 -- Ensure that the pegging record is linked to the correct parent.
1925 l_pegging_rec.parent_pegging_id:= l_demand_pegging_id;
1926 l_pegging_rec.atp_level:= p_level + 2;
1927 END IF;
1928 --- End Enhance CTO Phase 1 Req #17
1929 l_pegging_rec.identifier3 := NULL;
1930 l_pegging_rec.supply_demand_type:= 2;
1931 l_pegging_rec.supply_demand_source_type:= MSC_ATP_PVT.ATP;
1932 l_pegging_rec.source_type := 0;
1933
1934 IF x_atp_date_this_level IS NULL THEN
1935
1936 IF PG_DEBUG in ('Y', 'C') THEN
1937 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'x_atp_date_this_level null for : ' || p_item_name);
1938 END IF;
1939
1940 l_stealing_flag := 'N';
1941 --x_requested_date_quantity := 0;
1942 x_atp_date_this_level := TRUNC(l_infinite_time_fence_date);
1943 x_atp_date_quantity_this_level := MSC_ATP_PVT.INFINITE_NUMBER;
1944
1945 IF PG_DEBUG in ('Y', 'C') THEN
1946 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'before IF');
1947 END IF;
1948
1949 IF l_used_dc_tab.COUNT > 0 THEN
1950 l_used_dc_tab.TRIM(l_used_dc_tab.COUNT);
1951 l_used_dc_qty.TRIM(l_used_dc_qty.COUNT);
1952 END IF;
1953
1954 IF PG_DEBUG in ('Y', 'C') THEN
1955 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'after IF : ' || l_used_dc_tab.COUNT);
1956 END IF;
1957
1958 l_used_dc_tab.EXTEND;
1959 l_used_dc_qty.EXTEND;
1960 IF PG_DEBUG in ('Y', 'C') THEN
1961 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'after extend : ' || l_used_dc_tab.COUNT);
1962 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'before x_atp_pegging_tab : ' || x_atp_pegging_tab.COUNT);
1963 END IF;
1964 x_atp_pegging_tab.EXTEND;
1965
1966 IF PG_DEBUG in ('Y', 'C') THEN
1967 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'after x_atp_pegging_tab : ' || x_atp_pegging_tab.COUNT);
1968 END IF;
1969
1970 l_used_dc_tab(1) := p_demand_class;
1971
1972 IF PG_DEBUG in ('Y', 'C') THEN
1973 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'after dc : ' || l_used_dc_tab.COUNT);
1974 END IF;
1975
1976 l_used_dc_qty(1) := x_atp_date_quantity_this_level;
1977 l_pegging_rec.supply_demand_quantity:= x_atp_date_quantity_this_level;
1978 l_pegging_rec.supply_demand_date:= x_atp_date_this_level;
1979
1980 -- time_phased_atp changes begin
1981 IF l_time_phased_atp = 'Y' and x_atp_date_this_level <= p_atf_date THEN
1982 l_pegging_rec.inventory_item_id:= p_request_item_id;
1983 l_pegging_rec.inventory_item_name := p_item_name;
1984 ELSE
1985 l_pegging_rec.inventory_item_id:= p_inventory_item_id;
1986 l_pegging_rec.inventory_item_name := p_family_item_name;
1987 END IF;
1988 -- time_phased_atp changes end
1989
1990 l_pegging_rec.summary_flag := MSC_ATP_PVT.G_SUMMARY_FLAG; -- for summary enhancement
1991 l_pegging_rec.required_date := p_requested_date;
1992 --bug3328421
1993 l_pegging_rec.actual_supply_demand_date := x_atp_date_this_level;
1994 l_pegging_rec.constraint_type := 1;
1995 l_pegging_rec.model_sd_flag := 2;
1996 -- Bug 3826234 start
1997 IF PG_DEBUG in ('Y', 'C') THEN
1998 msc_sch_wb.atp_debug('ATP_Check: ' || '----------- Calendars passed to Pegging -----------------');
1999 msc_sch_wb.atp_debug('ATP_Check: ' || 'shipping_cal_code = ' ||p_get_mat_in_rec.shipping_cal_code);
2000 msc_sch_wb.atp_debug('ATP_Check: ' || 'receiving_cal_code = ' ||p_get_mat_in_rec.receiving_cal_code);
2001 msc_sch_wb.atp_debug('ATP_Check: ' || 'intransit_cal_code = ' ||p_get_mat_in_rec.intransit_cal_code);
2002 msc_sch_wb.atp_debug('ATP_Check: ' || 'manufacturing_cal_code = ' ||p_get_mat_in_rec.manufacturing_cal_code);
2003 msc_sch_wb.atp_debug('ATP_Check: ' || 'to_organization_id = ' ||p_get_mat_in_rec.to_organization_id);
2004 END IF;
2005 IF p_parent_pegging_id is null then
2006 l_pegging_rec.shipping_cal_code := p_get_mat_in_rec.shipping_cal_code;
2007 l_pegging_rec.receiving_cal_code := p_get_mat_in_rec.receiving_cal_code;
2008 l_pegging_rec.intransit_cal_code := p_get_mat_in_rec.intransit_cal_code;
2009 l_pegging_rec.manufacturing_cal_code := p_get_mat_in_rec.manufacturing_cal_code;
2010 IF PG_DEBUG in ('Y', 'C') THEN
2011 msc_sch_wb.atp_debug('ATP_Check: ' || 'Inside IF');
2012 END IF;
2013 ELSIF NVL(p_get_mat_in_rec.to_organization_id,p_get_mat_in_rec.organization_id)
2014 <> p_get_mat_in_rec.organization_id THEN
2015 l_pegging_rec.shipping_cal_code := p_get_mat_in_rec.shipping_cal_code;
2016 l_pegging_rec.receiving_cal_code := p_get_mat_in_rec.receiving_cal_code;
2017 l_pegging_rec.intransit_cal_code := p_get_mat_in_rec.intransit_cal_code;
2018 l_pegging_rec.manufacturing_cal_code := NULL;
2019 IF PG_DEBUG in ('Y', 'C') THEN
2020 msc_sch_wb.atp_debug('ATP_Check: ' || 'Inside ELSIF');
2021 END IF;
2022 ELSE
2023 l_pegging_rec.manufacturing_cal_code := p_get_mat_in_rec.manufacturing_cal_code;
2024 l_pegging_rec.shipping_cal_code := NULL;
2025 l_pegging_rec.receiving_cal_code := NULL;
2026 l_pegging_rec.intransit_cal_code := NULL;
2027 IF PG_DEBUG in ('Y', 'C') THEN
2028 msc_sch_wb.atp_debug('ATP_Check: ' || 'Inside ELSE');
2029 END IF;
2030 END IF;
2031 -- Bug 3826234 end
2032 MSC_ATP_DB_UTILS.Add_Pegging(l_pegging_rec, x_atp_pegging_tab(1));
2033
2034 IF PG_DEBUG in ('Y', 'C') THEN
2035 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'after Add_Pegging : ' || x_atp_pegging_tab(1));
2036 END IF;
2037 ELSE
2038 l_stealing_quantity := 0;
2039 l_total_atp_qty := 0;
2040 l_pf_stealing_qty := 0;
2041 l_mem_stealing_qty := 0;
2042 FOR i IN 1..l_used_dc_qty.COUNT LOOP
2043
2044 l_stealing_quantity := LEAST(l_used_dc_qty(i), (p_quantity_ordered - NVL(l_total_atp_qty, 0)));
2045 l_total_atp_qty := NVL(l_total_atp_qty, 0) + l_stealing_quantity;
2046
2047 l_pegging_rec.supply_demand_quantity:= GREATEST(l_used_dc_qty(i), 0);
2048 l_pegging_rec.supply_demand_date:= x_atp_date_this_level;
2049
2050 IF PG_DEBUG in ('Y', 'C') THEN
2051 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'before inserting Stealing Info' || l_used_dc_tab(i));
2052 END IF;
2053
2054 IF p_demand_class <> l_used_dc_tab(i) THEN
2055 -- Add the Stealing Data.
2056
2057 IF l_time_phased_atp = 'N' THEN
2058 MSC_ATP_DB_UTILS.Add_Stealing_Supply_Details (
2059 p_plan_id,
2060 p_identifier,
2061 l_inv_item_id,
2062 p_organization_id,
2063 p_instance_id,
2064 l_stealing_quantity,
2065 p_demand_class,
2066 l_used_dc_tab(i),
2067 x_atp_date_this_level,
2068 l_transaction_id,
2069 p_refresh_number,
2070 p_get_mat_in_rec.ato_model_line_id, -- For summary enhancement
2071 p_demand_source_type,--cmro
2072 --bug3684383
2073 p_order_number);
2074 IF PG_DEBUG in ('Y', 'C') THEN
2075 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'after insert into msc_alloc_supplies-Stealing Info' || l_transaction_id);
2076 END IF;
2077 ELSE
2078 l_pf_stealing_qty := LEAST(l_used_dc_qty(i)-NVL(l_used_dc_mem_qty(i), 0), l_stealing_quantity);
2079 l_mem_stealing_qty := l_stealing_quantity - l_pf_stealing_qty;
2080
2081 MSC_ATP_PF.Add_PF_Stealing_Supply_Details (
2082 p_plan_id,
2083 p_identifier,
2084 l_inv_item_id,
2085 l_pf_item_id,
2086 p_organization_id,
2087 p_instance_id,
2088 l_mem_stealing_qty,
2089 l_pf_stealing_qty,
2090 p_demand_class,
2091 l_used_dc_tab(i),
2092 x_atp_date_this_level,
2093 p_atf_date,
2094 p_refresh_number, -- for summary enhancement
2095 l_transaction_id,
2096 p_get_mat_in_rec.ato_model_line_id,
2097 p_demand_source_type,--cmro
2098 --bug3684383
2099 p_order_number,
2100 l_return_status);
2101 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2102 IF PG_DEBUG in ('Y', 'C') THEN
2103 msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'Error occured in procedure Add_PF_Stealing_Supply_Details');
2104 END IF;
2105 RAISE FND_API.G_EXC_ERROR;
2106 END IF;
2107 END IF;
2108 -- time_phased_atp changes end
2109
2110 l_pegging_rec.identifier3 := l_transaction_id;
2111 l_pegging_rec.char1 := l_used_dc_tab(i);
2112
2113 END IF; --IF p_demand_class <> l_used_dc_tab(i) THEN
2114
2115 -- time_phased_atp changes begin
2116 IF l_time_phased_atp = 'Y' and x_atp_date_this_level <= p_atf_date THEN
2117 l_pegging_rec.inventory_item_id:= p_request_item_id;
2118 l_pegging_rec.inventory_item_name := p_item_name;
2119 ELSE
2120 l_pegging_rec.inventory_item_id:= p_inventory_item_id;
2121 l_pegging_rec.inventory_item_name := p_family_item_name;
2122 END IF;
2123 -- time_phased_atp changes end
2124
2125 x_atp_pegging_tab.EXTEND;
2126 --diag_atp
2127 l_pegging_rec.plan_name := p_get_mat_in_rec.plan_name;
2128 l_pegging_rec.required_quantity:= p_quantity_ordered;
2129 l_pegging_rec.required_date := p_requested_date;
2130 --bug 3328421
2131 l_pegging_rec.actual_supply_demand_date := x_atp_date_this_level;
2132
2133 --bug 3443276: Add constarint only if ATP date is later than request date
2134 IF x_atp_date_this_level > p_requested_date THEN
2135 l_pegging_rec.constraint_type := 1;
2136 END IF;
2137 l_pegging_rec.infinite_time_fence := l_infinite_time_fence_date;
2138 l_pegging_rec.atp_rule_name := x_get_mat_out_rec.atp_rule_name;
2139 l_pegging_rec.rounding_control := l_round_flag;
2140 l_pegging_rec.atp_flag := MSC_ATP_PVT.G_ITEM_INFO_REC.atp_flag;
2141 l_pegging_rec.atp_component_flag := MSC_ATP_PVT.G_ITEM_INFO_REC.atp_comp_flag;
2142 l_pegging_rec.pegging_type := MSC_ATP_PVT.ATP_SUPPLY; ---atp supply node
2143 l_pegging_rec.postprocessing_lead_time := MSC_ATP_PVT.G_ITEM_INFO_REC.pre_pro_lt;
2144 l_pegging_rec.preprocessing_lead_time := MSC_ATP_PVT.G_ITEM_INFO_REC.post_pro_lt;
2145 l_pegging_rec.fixed_lead_time := MSC_ATP_PVT.G_ITEM_INFO_REC.fixed_lt;
2146 l_pegging_rec.variable_lead_time := MSC_ATP_PVT.G_ITEM_INFO_REC.variable_lt;
2147 l_pegging_rec.weight_capacity := MSC_ATP_PVT.G_ITEM_INFO_REC.unit_weight;
2148 l_pegging_rec.volume_capacity := MSC_ATP_PVT.G_ITEM_INFO_REC.unit_volume;
2149 l_pegging_rec.weight_uom := MSC_ATP_PVT.G_ITEM_INFO_REC.weight_uom;
2150 l_pegging_rec.volume_uom := MSC_ATP_PVT.G_ITEM_INFO_REC.volume_uom;
2151 l_pegging_rec.allocation_rule := MSC_ATP_PVT.G_ALLOCATION_RULE_NAME;
2152 l_pegging_rec.substitution_window := MSC_ATP_PVT.G_ITEM_INFO_REC.substitution_window;
2153
2154
2155 l_pegging_rec.summary_flag := MSC_ATP_PVT.G_SUMMARY_FLAG; -- for summary enhancement
2156 l_pegging_rec.required_date := p_requested_date;
2157 l_pegging_rec.model_sd_flag := 2;
2158 MSC_ATP_DB_UTILS.Add_Pegging(l_pegging_rec, x_atp_pegging_tab(i));
2159
2160 END LOOP; --FOR i IN 1..l_used_dc_qty.COUNT LOOP
2161 END IF; --IF NVL(l_stealing_flag, 'N') = 'Y' THEN
2162
2163 IF PG_DEBUG in ('Y', 'C') THEN
2164 msc_sch_wb.atp_debug('**********End Get_Forward_Material_Atp Procedure************');
2165 END IF;
2166 EXCEPTION
2167 WHEN NO_DATA_FOUND THEN
2168 x_requested_date_quantity := 0.0;
2169 x_atp_date_this_level := TRUNC(l_infinite_time_fence_date);
2170 x_atp_date_quantity_this_level := MSC_ATP_PVT.INFINITE_NUMBER;
2171
2172 IF PG_DEBUG in ('Y', 'C') THEN
2173 msc_sch_wb.atp_debug('Get_Forward_Material_Atp, no data found');
2174 END IF;
2175 WHEN MSC_ATP_PVT.NO_MATCHING_DATE_IN_CAL THEN --bug3583705
2176 x_requested_date_quantity := 0.0;
2177 x_atp_date_this_level := TRUNC(l_infinite_time_fence_date);
2178 x_atp_date_quantity_this_level := MSC_ATP_PVT.INFINITE_NUMBER;
2179 IF PG_DEBUG in ('Y', 'C') THEN
2180 msc_sch_wb.atp_debug('MAtching cal date not found, in atp_check');
2181 END IF;
2182 RAISE MSC_ATP_PVT.NO_MATCHING_DATE_IN_CAL;
2183 WHEN OTHERS THEN
2184 IF PG_DEBUG in ('Y', 'C') THEN
2185 msc_sch_wb.atp_debug('Get_Forward_Material_Atp, sqlcode= '||sqlcode);
2186 msc_sch_wb.atp_debug('Get_Forward_Material_Atp, sqlerrm= '||sqlerrm);
2187 END IF;
2188 x_requested_date_quantity := 0.0;
2189 x_atp_date_this_level := TRUNC(l_infinite_time_fence_date);
2190 x_atp_date_quantity_this_level := MSC_ATP_PVT.INFINITE_NUMBER;
2191 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2192
2193 /*IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2194 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , 'Get_Forward_Material_Atp');
2195 END IF;*/ --bug3583705
2196
2197 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2198 END Get_Forward_Material_Atp;
2199
2200
2201 END MSC_AATP_REQ;