[Home] [Help]
PACKAGE BODY: APPS.MSC_ATP_DRP
Source
1 PACKAGE BODY MSC_ATP_DRP AS
2 /* $Header: MSCATDRB.pls 120.4 2006/04/19 06:11:35 anbansal noship $ */
3 G_PKG_NAME CONSTANT VARCHAR2(30) := 'MSC_ATP_DRP';
4
5 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('MSC_ATP_DEBUG'), 'N');
6
7
8 -- Procedure that nets supplies and demands for DRP plan.
9 PROCEDURE Get_Mat_Avail_Drp (
10 p_item_id IN NUMBER,
11 p_org_id IN NUMBER,
12 p_instance_id IN NUMBER,
13 p_plan_id IN NUMBER,
14 p_itf IN DATE,
15 x_atp_dates OUT NoCopy MRP_ATP_PUB.date_arr,
16 x_atp_qtys OUT NoCopy MRP_ATP_PUB.number_arr
17 ) IS
18 BEGIN
19 IF PG_DEBUG in ('Y', 'C') THEN
20 msc_sch_wb.atp_debug('********** Begin Get_Mat_Avail_Drp **********');
21 msc_sch_wb.atp_debug('Get_Mat_Avail_Drp: p_item_id ' || p_item_id);
22 msc_sch_wb.atp_debug('Get_Mat_Avail_Drp: p_org_id ' || p_org_id);
23 msc_sch_wb.atp_debug('Get_Mat_Avail_Drp: p_instance_id ' || p_instance_id);
24 msc_sch_wb.atp_debug('Get_Mat_Avail_Drp: p_plan_id ' || p_plan_id);
25 msc_sch_wb.atp_debug('Get_Mat_Avail_Drp: p_itf ' || p_itf);
26 END IF;
27
28 SELECT SD_DATE, SUM(SD_QTY)
29 BULK COLLECT INTO x_atp_dates, x_atp_qtys
30 FROM (
31 SELECT
32 TRUNC(DECODE(D.RECORD_SOURCE,
33 2, NVL(D.SCHEDULE_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
34 DECODE(MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF,
35 2, NVL(D.IMPLEMENT_SHIP_DATE,NVL(D.FIRM_DATE,NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE))),
36 NVL(D.SCHEDULE_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE)))) SD_DATE,
37 --plan by requestdate,promisedate,scheduledate
38 -1*D.USING_REQUIREMENT_QUANTITY SD_QTY
39 FROM MSC_DEMANDS D
40 WHERE D.PLAN_ID = p_plan_id
41 AND D.SR_INSTANCE_ID = p_instance_id
42 AND D.INVENTORY_ITEM_ID = MSC_ATP_PVT.G_ITEM_INFO_REC.dest_inv_item_id
43 AND D.ORGANIZATION_ID = p_org_id
44 AND D.ORIGINATION_TYPE NOT IN(1,4,5,7,8,9,11,15,22,28,29,31,48,49,53)
45 AND D.USING_REQUIREMENT_QUANTITY <> 0 --4501434
46 -- Ignore Plan Order Demand (Requested Outbound)
47 -- and Unconstrained Kit Demand for DRP Plans
48 -- Origination Type 1, 48
49 AND TRUNC(DECODE(D.RECORD_SOURCE,
50 2, NVL(D.SCHEDULE_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
51 DECODE(MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF,
52 2, NVL(D.IMPLEMENT_SHIP_DATE,NVL(D.FIRM_DATE,NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE))),
53 NVL(D.SCHEDULE_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE))))
54 < TRUNC(NVL(p_itf, DECODE(D.RECORD_SOURCE,
55 2, NVL(D.SCHEDULE_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
56 DECODE(MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF,
57 2, NVL(D.IMPLEMENT_SHIP_DATE,NVL(D.FIRM_DATE,NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE))),
58 NVL(D.SCHEDULE_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE))) + 1))
59 --plan by request date,promise date ,ship date
60 UNION ALL
61 SELECT
62 TRUNC(NVL(S.FIRM_DATE,S.NEW_SCHEDULE_DATE)) SD_DATE,
63 NVL(S.FIRM_QUANTITY,S.NEW_ORDER_QUANTITY) SD_QTY
64 FROM MSC_SUPPLIES S
65 WHERE S.PLAN_ID = p_plan_id
66 AND S.SR_INSTANCE_ID = p_instance_id
67 AND S.INVENTORY_ITEM_ID = MSC_ATP_PVT.G_ITEM_INFO_REC.dest_inv_item_id
68 AND S.ORGANIZATION_ID = p_org_id
69 -- Exclude Cancelled Supplies 2460645
70 AND NVL(S.DISPOSITION_STATUS_TYPE, 1) <> 2 -- Bug 2460645
71 AND NVL(S.FIRM_QUANTITY,S.NEW_ORDER_QUANTITY) <> 0 -- 1243985
72 AND TRUNC(NVL(S.FIRM_DATE,S.NEW_SCHEDULE_DATE))
73 < NVL(p_itf, TRUNC(NVL(S.FIRM_DATE,S.NEW_SCHEDULE_DATE)) + 1) -- 2859130
74 ---bug 1735580
75 UNION ALL
76 SELECT -- Net Planned arrival as outbound demand in source org.
77 TRUNC(NVL(S.NEW_SHIP_DATE,S.NEW_SCHEDULE_DATE)) SD_DATE,
78 -- Bug 4042808 Outbound Shipments are demands. Firm Supply Date
79 -- does not apply. (Previous Comment -- Firm Date is common across orgs).
80 -1 * NVL(S.FIRM_QUANTITY,S.NEW_ORDER_QUANTITY) SD_QTY
81 FROM MSC_SUPPLIES S
82 WHERE S.PLAN_ID = p_plan_id
83 AND S.SOURCE_SR_INSTANCE_ID = p_instance_id
84 AND S.SOURCE_ORGANIZATION_ID = p_org_id
85 AND S.INVENTORY_ITEM_ID = MSC_ATP_PVT.G_ITEM_INFO_REC.dest_inv_item_id
86 AND NVL(S.DISPOSITION_STATUS_TYPE, 1) <> 2 -- Bug 2460645
87 AND S.ORDER_TYPE = 51 -- Planned Arrival is a Demand in Source Org
88 AND NVL(S.FIRM_QUANTITY,S.NEW_ORDER_QUANTITY) <> 0 -- 1243985
89 AND TRUNC(NVL(S.FIRM_DATE,S.NEW_SHIP_DATE))
90 < NVL(p_itf, TRUNC(NVL(S.FIRM_DATE,S.NEW_SHIP_DATE)) + 1)
91 )
92 GROUP BY SD_DATE;
93
94 IF PG_DEBUG in ('Y', 'C') THEN
95 msc_sch_wb.atp_debug('Get_Mat_Avail_Drp : Total Row Count ' || x_atp_qtys.COUNT );
96 msc_sch_wb.atp_debug('******** Get_Mat_Avail_Drp End ********');
97 END IF;
98
99
100 END Get_Mat_Avail_Drp;
101
102 -- Procedure that nets supplies and demand details for DRP plan.
103 PROCEDURE Get_Mat_Avail_Drp_Dtls (
104 p_item_id IN NUMBER,
105 p_request_item_id IN NUMBER,
106 p_org_id IN NUMBER,
107 p_instance_id IN NUMBER,
108 p_plan_id IN NUMBER,
109 p_itf IN DATE,
110 p_level IN NUMBER,
111 p_scenario_id IN NUMBER,
112 p_identifier IN NUMBER
113 ) IS
114
115 l_null_num NUMBER;
116 l_null_char VARCHAR2(1);
117 l_null_date DATE; --bug3814584
118 l_sysdate DATE := sysdate;
119 l_user_id NUMBER := FND_GLOBAL.USER_ID; -- Declare and Define value.
120 BEGIN
121 IF PG_DEBUG in ('Y', 'C') THEN
122 msc_sch_wb.atp_debug('********** Begin Get_Mat_Avail_Drp_Dtls **********');
123 msc_sch_wb.atp_debug('Get_Mat_Avail_Drp_Dtls: p_item_id ' || p_item_id);
124 msc_sch_wb.atp_debug('Get_Mat_Avail_Drp_Dtls: p_request_item_id ' || p_request_item_id);
125 msc_sch_wb.atp_debug('Get_Mat_Avail_Drp_Dtls: p_org_id ' || p_org_id);
126 msc_sch_wb.atp_debug('Get_Mat_Avail_Drp_Dtls: p_instance_id ' || p_instance_id);
127 msc_sch_wb.atp_debug('Get_Mat_Avail_Drp_Dtls: p_plan_id ' || p_plan_id);
128 msc_sch_wb.atp_debug('Get_Mat_Avail_Drp_Dtls: p_itf ' || p_itf);
129 END IF;
130
131 INSERT INTO msc_atp_sd_details_temp (
132 ATP_Level,
133 Order_line_id,
134 Scenario_Id,
135 Inventory_Item_Id,
136 Request_Item_Id,
137 Organization_Id,
138 Department_Id,
139 Resource_Id,
140 Supplier_Id,
141 Supplier_Site_Id,
142 From_Organization_Id,
143 From_Location_Id,
144 To_Organization_Id,
145 To_Location_Id,
146 Ship_Method,
147 UOM_code,
148 Supply_Demand_Type,
149 Supply_Demand_Source_Type,
150 Supply_Demand_Source_Type_Name,
151 Identifier1,
152 Identifier2,
153 Identifier3,
154 Identifier4,
155 Supply_Demand_Quantity,
156 Supply_Demand_Date,
157 Disposition_Type,
158 Disposition_Name,
159 Pegging_Id,
160 End_Pegging_Id,
161 creation_date,
162 created_by,
163 last_update_date,
164 last_updated_by,
165 last_update_login,
166 ORIG_CUSTOMER_SITE_NAME,--bug3263368
167 ORIG_CUSTOMER_NAME, --bug3263368
168 ORIG_DEMAND_CLASS, --bug3263368
169 ORIG_REQUEST_DATE --bug3263368
170 )
171 (
172 SELECT p_level col1,
173 p_identifier col2,
174 p_scenario_id col3,
175 p_item_id col4 ,
176 p_request_item_id col5,
177 p_org_id col6,
178 l_null_num col7,
179 l_null_num col8,
180 l_null_num col9,
181 l_null_num col10,
182 l_null_num col11,
183 l_null_num col12,
184 l_null_num col13,
185 l_null_num col14,
186 l_null_char col15,
187 MSC_ATP_PVT.G_ITEM_INFO_REC.UOM_CODE col16, -- ATP4drp Re-Use Global data.
188 1 col17, -- demand
189 DECODE(D.ORIGINATION_TYPE, -200, 53, --4686870
190 30,DECODE(NVL(D.DEMAND_SOURCE_TYPE, 2), 8, 54,D.ORIGINATION_TYPE),
191 D.ORIGINATION_TYPE) col18, --4568493
192 --D.ORIGINATION_TYPE col18,
193 l_null_char col19,
194 D.SR_INSTANCE_ID col20,
195 l_null_num col21,
196 D.DEMAND_ID col22,
197 l_null_num col23,
198 -1* D.USING_REQUIREMENT_QUANTITY col24,
199 TRUNC(DECODE(D.RECORD_SOURCE,
200 2, NVL(D.SCHEDULE_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
201 DECODE(MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF,
202 2, NVL(D.IMPLEMENT_SHIP_DATE,NVL(D.FIRM_DATE,NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE))),
203 NVL(D.SCHEDULE_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE)))) col25,
204 --plan by request date,promise date, schedule date
205 l_null_num col26,
206 D.ORDER_NUMBER col27,
207 l_null_num col28,
208 l_null_num col29,
209 l_sysdate,
210 l_user_id,
211 l_sysdate,
212 l_user_id,
213 l_user_id,
214 MTPS.LOCATION, --bug3263368
215 MTP.PARTNER_NAME, --bug3263368
216 D.DEMAND_CLASS, --bug3263368
217 DECODE(D.ORDER_DATE_TYPE_CODE,2,D.REQUEST_DATE,
218 D.REQUEST_SHIP_DATE) --bug3263368
219 FROM
220 MSC_DEMANDS D,
221 MSC_TRADING_PARTNERS MTP,--bug3263368
222 MSC_TRADING_PARTNER_SITES MTPS --bug3263368
223 WHERE D.PLAN_ID = p_plan_id
224 AND D.SR_INSTANCE_ID = p_instance_id
225 AND D.ORGANIZATION_ID = p_org_id
226 AND D.INVENTORY_ITEM_ID = MSC_ATP_PVT.G_ITEM_INFO_REC.dest_inv_item_id
227 AND D.SHIP_TO_SITE_ID = MTPS.PARTNER_SITE_ID(+) --bug3263368
228 AND D.CUSTOMER_ID = MTP.PARTNER_ID(+) --bug3263368
229 AND D.USING_REQUIREMENT_QUANTITY <> 0 --4501434
230 AND D.ORIGINATION_TYPE NOT IN(1,4,5,7,8,9,11,15,22,28,29,31,48,49,52,53) -- ignore copy SO for summary enhancement
231 -- Ignore Plan Order Demand (Requested Outbound)
232 -- and Unconstrained Kit Demand for DRP Plans
233 -- Origination Type 1, 48
234 AND TRUNC(DECODE(D.RECORD_SOURCE,
235 2, NVL(D.SCHEDULE_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
236 DECODE(MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF,
237 2, NVL(D.IMPLEMENT_SHIP_DATE,NVL(D.FIRM_DATE,NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE))),
238 NVL(D.SCHEDULE_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE))))
239 < TRUNC(NVL(p_itf,
240 DECODE(D.RECORD_SOURCE,
241 2, NVL(D.SCHEDULE_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
242 DECODE(MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF,
243 2, NVL(D.IMPLEMENT_SHIP_DATE,NVL(D.FIRM_DATE,NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE))),
244 NVL(D.SCHEDULE_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE))) + 1))
245 --plan by request date, promise date, schedule date
246 UNION ALL
247 SELECT p_level col1,
248 p_identifier col2,
249 p_scenario_id col3,
250 p_item_id col4 ,
251 p_request_item_id col5,
252 p_org_id col6,
253 l_null_num col7,
254 l_null_num col8,
255 l_null_num col9,
256 l_null_num col10,
257 l_null_num col11,
258 l_null_num col12,
259 l_null_num col13,
260 l_null_num col14,
261 l_null_char col15,
262 MSC_ATP_PVT.G_ITEM_INFO_REC.UOM_CODE col16, -- ATP4drp Re-Use Global data.
263 2 col17, -- supply
264 DECODE(S.ORDER_TYPE , 2,
265 DECODE(NVL(S.SOURCE_ORGANIZATION_ID, S.ORGANIZATION_ID),
266 S.ORGANIZATION_ID, 2, 53),S.ORDER_TYPE) col18,
267 --S.ORDER_TYPE col18, --4568493
268 l_null_char col19,
269 S.SR_INSTANCE_ID col20,
270 l_null_num col21,
271 S.TRANSACTION_ID col22,
272 l_null_num col23,
273 NVL(S.FIRM_QUANTITY,S.NEW_ORDER_QUANTITY) col24,
274 TRUNC(NVL(S.FIRM_DATE,S.NEW_SCHEDULE_DATE)) col25,
275 l_null_num col26,
279 --bug4368456 show order number for inbound/outbound shipments and plan orders
276 --bug 4273652: show order number for planned inbound/outbound shipments
277 --DECODE(S.ORDER_TYPE, 5, to_char(S.TRANSACTION_ID), S.ORDER_NUMBER) col27,
278 --DECODE(S.ORDER_TYPE, 51, to_char(S.TRANSACTION_ID),S.ORDER_NUMBER) col27,
280 DECODE(S.ORDER_TYPE, 51, to_char(S.TRANSACTION_ID),
281 5, to_char(S.TRANSACTION_ID),
282 S.ORDER_NUMBER) col27,
283 l_null_num col28,
284 l_null_num col29,
285 l_sysdate,
286 l_user_id,
287 l_sysdate,
288 l_user_id,
289 l_user_id,
290 l_null_char, --bug3814584
291 l_null_char, --bug3814584
292 l_null_char, --bug3814584
293 l_null_date --bug3814584
294 FROM
295 MSC_SUPPLIES S
296 WHERE S.PLAN_ID = p_plan_id
297 AND S.SR_INSTANCE_ID = p_instance_id
298 AND S.INVENTORY_ITEM_ID = MSC_ATP_PVT.G_ITEM_INFO_REC.dest_inv_item_id
299 AND S.ORGANIZATION_ID = p_org_id
300 AND NVL(S.DISPOSITION_STATUS_TYPE, 1) <> 2 -- Bug 2460645
301 AND NVL(S.FIRM_QUANTITY,S.NEW_ORDER_QUANTITY) <> 0 -- 1243985
302 AND TRUNC(NVL(S.FIRM_DATE,S.NEW_SCHEDULE_DATE))
303 < NVL(p_itf, TRUNC(NVL(S.FIRM_DATE,S.NEW_SCHEDULE_DATE)) + 1)
304 UNION ALL -- Net Planned arrival as outbound demand in source org.
305 SELECT p_level col1,
306 p_identifier col2,
307 p_scenario_id col3,
308 p_item_id col4 ,
309 p_request_item_id col5,
310 p_org_id col6,
311 l_null_num col7,
312 l_null_num col8,
313 l_null_num col9,
314 l_null_num col10,
315 l_null_num col11,
316 l_null_num col12,
317 l_null_num col13,
318 l_null_num col14,
319 l_null_char col15,
320 MSC_ATP_PVT.G_ITEM_INFO_REC.UOM_CODE col16, -- ATP4drp Re-Use Global data.
321 1 col17, -- demand in source org.
322 DECODE(S.ORDER_TYPE, 51, 53) col18,
323 -- Bug 4052808 For Display of Inbound as Planned Outbound Shipment.
324 l_null_char col19,
325 S.SR_INSTANCE_ID col20,
326 l_null_num col21,
327 S.TRANSACTION_ID col22,
328 l_null_num col23,
329 -1 * NVL(S.FIRM_QUANTITY,S.NEW_ORDER_QUANTITY) col24,
330 -- Net Planned arrival as outbound demand in source org.
331 TRUNC(NVL(S.NEW_SHIP_DATE,S.NEW_SCHEDULE_DATE)) col25,
332 -- Bug 4042808 Outbound Shipments are demands. Firm Supply Date
333 -- does not apply. (Previous Comment -- Firm Date is common across orgs).
334 l_null_num col26,
335 --bug 4273652: show order number for planned inbound/outbound shipments
336 --DECODE(S.ORDER_TYPE, 5, to_char(S.TRANSACTION_ID), S.ORDER_NUMBER) col27,
337 DECODE(S.ORDER_TYPE, 51, to_char(S.TRANSACTION_ID),S.ORDER_NUMBER) col27,
338 l_null_num col28,
339 l_null_num col29,
340 l_sysdate,
341 l_user_id,
342 l_sysdate,
343 l_user_id,
344 l_user_id,
345 l_null_char, --bug3814584
346 l_null_char, --bug3814584
347 l_null_char, --bug3814584
348 l_null_date --bug3814584
349 FROM
350 MSC_SUPPLIES S
351 WHERE S.PLAN_ID = p_plan_id
352 AND S.SOURCE_SR_INSTANCE_ID = p_instance_id
353 AND S.SOURCE_ORGANIZATION_ID = p_org_id
354 AND S.INVENTORY_ITEM_ID = MSC_ATP_PVT.G_ITEM_INFO_REC.dest_inv_item_id
355 AND NVL(S.DISPOSITION_STATUS_TYPE, 1) <> 2 -- Bug 2460645
356 AND S.ORDER_TYPE = 51 -- Planned Arrival is a Demand in Source Org
357 AND NVL(S.FIRM_QUANTITY,S.NEW_ORDER_QUANTITY) <> 0 -- 1243985
358 AND TRUNC(NVL(S.FIRM_DATE,S.NEW_SHIP_DATE))
359 < NVL(p_itf, TRUNC(NVL(S.FIRM_DATE,S.NEW_SHIP_DATE)) + 1)
360 )
361 ;
362 IF PG_DEBUG in ('Y', 'C') THEN
363 msc_sch_wb.atp_debug('Get_Mat_Avail_Drp_Dtls: ' || 'Total Records inserted : ' || SQL%ROWCOUNT);
364 msc_sch_wb.atp_debug('******** Get_Mat_Avail_Drp_Dtls End ********');
365 END IF;
366
367 END Get_Mat_Avail_Drp_Dtls;
368
369 -- procedure for full summation of
370 -- supply/demand for DRP plans.
371
372 PROCEDURE LOAD_SD_FULL_DRP(p_plan_id IN NUMBER,
373 p_sys_date IN DATE)
374 IS
375 l_user_id NUMBER := FND_GLOBAL.USER_ID; -- Declare and Define value.
376 BEGIN
377
378 msc_util.msc_log('******** LOAD_SD_FULL_DRP Begin ********');
379 msc_util.msc_log('LOAD_SD_FULL_DRP p_plan_id ' || p_plan_id);
380 msc_util.msc_log('LOAD_SD_FULL_DRP p_sys_date ' || p_sys_date);
381
382 INSERT INTO MSC_ATP_SUMMARY_SD (
383 plan_id,
384 sr_instance_id,
385 organization_id,
386 inventory_item_id,
387 demand_class,
388 sd_date,
389 sd_qty,
390 last_update_date,
391 last_updated_by,
392 creation_date,
393 created_by)
394 (SELECT p_plan_id,
395 sr_instance_id,
396 organization_id,
397 inventory_item_id,
398 demand_class,
399 SD_DATE,
400 sum(sd_qty),
401 p_sys_date,
402 l_user_id,
403 p_sys_date,
404 l_user_id
405 from (SELECT /*+ ORDERED */
406 I.sr_instance_id,
407 I.organization_id,
411 2, NVL(d.PLANNED_SHIP_DATE,d.USING_ASSEMBLY_DEMAND_DATE),
408 I.inventory_item_id,
409 '@@@' demand_class,
410 TRUNC(DECODE(MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF,
412 NVL(D.SCHEDULE_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE))) SD_DATE,
413 --plan by request date, promise date or schedule date -- 2859130
414 -1* D.USING_REQUIREMENT_QUANTITY SD_QTY
415 FROM MSC_SYSTEM_ITEMS I,
416 MSC_PLAN_ORGANIZATIONS PO,
417 MSC_DEMANDS D
418 WHERE PO.plan_id = p_plan_id
419 AND I.PLAN_ID = PO.PLAN_ID
420 AND I.SR_INSTANCE_ID = PO.SR_INSTANCE_ID
421 AND I.ORGANIZATION_ID = PO.ORGANIZATION_ID
422 AND I.ATP_FLAG = 'Y'
423 AND D.PLAN_ID = I.PLAN_ID
424 AND D.SR_INSTANCE_ID = I.SR_INSTANCE_ID
425 AND D.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
426 AND D.ORGANIZATION_ID = I.ORGANIZATION_ID
427 AND D.ORIGINATION_TYPE NOT IN (1,4,5,7,8,9,11,15,22,28,29,31,48,49,53)
428 -- Ignore Plan Order Demand (Requested Outbound)
429 -- and Unconstrained Kit Demand for DRP Plans
430 -- Origination Type 1, 48
431 AND D.REFRESH_NUMBER IS NULL -- consider only planning records in full summation - summary enhancement
432
433 UNION ALL
434
435 SELECT /*+ ORDERED */
436 I.sr_instance_id,
437 I.organization_id,
438 I.inventory_item_id,
439 '@@@' demand_class,
440 TRUNC(NVL(S.FIRM_DATE,S.NEW_SCHEDULE_DATE)) SD_DATE,
441 NVL(S.FIRM_QUANTITY,S.NEW_ORDER_QUANTITY) SD_QTY
442 FROM MSC_SYSTEM_ITEMS I,
443 MSC_PLAN_ORGANIZATIONS PO,
444 MSC_SUPPLIES S
445 WHERE PO.plan_id = p_plan_id
446 AND I.PLAN_ID = PO.PLAN_ID
447 AND I.SR_INSTANCE_ID = PO.SR_INSTANCE_ID
448 AND I.ORGANIZATION_ID = PO.ORGANIZATION_ID
449 AND I.ATP_FLAG = 'Y'
450 AND S.PLAN_ID = I.PLAN_ID
451 AND S.SR_INSTANCE_ID = I.SR_INSTANCE_ID
452 AND S.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
453 AND S.ORGANIZATION_ID = I.ORGANIZATION_ID
454 AND NVL(S.DISPOSITION_STATUS_TYPE, 1) <> 2
455 AND NVL(S.FIRM_QUANTITY,S.NEW_ORDER_QUANTITY) <> 0
456 AND S.REFRESH_NUMBER IS NULL -- consider only planning records in full summation - summary enhancement
457
458 UNION ALL -- Net Planned arrival as outbound demand in source org.
459
460 SELECT /*+ ORDERED */
461 I.sr_instance_id,
462 I.organization_id,
463 I.inventory_item_id,
464 '@@@' demand_class,
465 TRUNC(NVL(S.NEW_SHIP_DATE,S.NEW_SCHEDULE_DATE)) SD_DATE,
466 -- Bug 4042808 Outbound Shipments are demands. Firm Supply Date
467 -- does not apply. (Previous Comment -- Firm Date is common across orgs).
468 -1 * NVL(S.FIRM_QUANTITY,S.NEW_ORDER_QUANTITY) SD_QTY
469 FROM MSC_SYSTEM_ITEMS I,
470 MSC_PLAN_ORGANIZATIONS PO,
471 MSC_SUPPLIES S
472 WHERE PO.plan_id = p_plan_id
473 AND I.PLAN_ID = PO.PLAN_ID
474 AND I.SR_INSTANCE_ID = PO.SR_INSTANCE_ID
475 AND I.ORGANIZATION_ID = PO.ORGANIZATION_ID
476 AND I.ATP_FLAG = 'Y'
477 AND S.PLAN_ID = I.PLAN_ID
478 AND S.SOURCE_SR_INSTANCE_ID = I.SR_INSTANCE_ID
479 AND S.SOURCE_ORGANIZATION_ID = I.ORGANIZATION_ID
480 AND S.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
481 AND NVL(S.DISPOSITION_STATUS_TYPE, 1) <> 2
482 AND S.ORDER_TYPE = 51 -- Planned Arrival is a Demand in Source Org
483 AND NVL(S.FIRM_QUANTITY,S.NEW_ORDER_QUANTITY) <> 0
484 AND S.REFRESH_NUMBER IS NULL -- consider only planning records in full summation - summary enhancement
485 )
486 GROUP BY inventory_item_id,organization_id, sr_instance_id, demand_class, sd_date
487 );
488
489 msc_util.msc_log('LOAD_SD_FULL_DRP: ' || 'Total Records inserted : ' || SQL%ROWCOUNT);
490 msc_util.msc_log('******** LOAD_SD_FULL_DRP End ********');
491
492
493 END LOAD_SD_FULL_DRP;
494
495 -- summary enhancement : procedure for net summation of supply/demand
496 -- for DRP cases.
497
498 PROCEDURE LOAD_SD_NET_DRP (p_plan_id IN NUMBER,
499 p_last_refresh_number IN NUMBER,
500 p_new_refresh_number IN NUMBER,
501 p_sys_date IN DATE)
502 IS
503 j pls_integer;
504 l_sr_instance_id_tab MRP_ATP_PUB.number_arr;
505 l_organization_id_tab MRP_ATP_PUB.number_arr;
506 l_inventory_item_id_tab MRP_ATP_PUB.number_arr;
507 l_sd_date_tab MRP_ATP_PUB.date_arr;
508 l_sd_quantity_tab MRP_ATP_PUB.number_arr;
509 l_ins_sr_instance_id_tab MRP_ATP_PUB.number_arr := MRP_ATP_PUB.number_arr();
510 l_ins_organization_id_tab MRP_ATP_PUB.number_arr := MRP_ATP_PUB.number_arr();
511 l_ins_inventory_item_id_tab MRP_ATP_PUB.number_arr := MRP_ATP_PUB.number_arr();
512 l_ins_sd_date_tab MRP_ATP_PUB.date_arr := MRP_ATP_PUB.date_arr();
516 BEGIN
513 l_ins_sd_quantity_tab MRP_ATP_PUB.number_arr := MRP_ATP_PUB.number_arr();
514
515 l_user_id NUMBER := FND_GLOBAL.USER_ID; -- Declare and Define value.
517
518 msc_util.msc_log('******** LOAD_SD_NET_DRP Begin ********');
519 msc_util.msc_log('LOAD_SD_NET_DRP: ' || 'p_last_refresh_number - ' || p_last_refresh_number);
520 msc_util.msc_log('LOAD_SD_NET_DRP: ' || 'p_new_refresh_number - ' || p_new_refresh_number);
521 msc_util.msc_log('LOAD_SD_NET_DRP: ' || 'p_plan_id - ' || p_plan_id);
522 msc_util.msc_log('LOAD_SD_NET_DRP: ' || 'p_sys_date - ' || p_sys_date);
523
524
525 SELECT sr_instance_id,
526 organization_id,
527 inventory_item_id,
528 SD_DATE,
529 sum(sd_qty)
530 BULK COLLECT INTO l_sr_instance_id_tab,
531 l_organization_id_tab,
532 l_inventory_item_id_tab,
533 l_sd_date_tab,
534 l_sd_quantity_tab
535 from (SELECT I.sr_instance_id,
536 I.organization_id,
537 I.inventory_item_id,
538 TRUNC(DECODE(D.RECORD_SOURCE,
539 2, NVL(D.SCHEDULE_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
540 DECODE(MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF,
541 2, NVL(D.IMPLEMENT_SHIP_DATE,NVL(D.FIRM_DATE,NVL(d.PLANNED_SHIP_DATE,d.USING_ASSEMBLY_DEMAND_DATE))),
542 NVL(D.SCHEDULE_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE)))) SD_DATE,
543 --plan by request date, promise date or schedule date
544 decode(D.USING_REQUIREMENT_QUANTITY, -- Consider unscheduled orders as dummy supplies
545 0, D.OLD_DEMAND_QUANTITY, -- For summary enhancement
546 -1 * D.USING_REQUIREMENT_QUANTITY) SD_QTY
547 FROM MSC_SYSTEM_ITEMS I,
548 MSC_PLAN_ORGANIZATIONS PO,
549 MSC_DEMANDS D
550 WHERE PO.plan_id = p_plan_id
551 AND I.PLAN_ID = PO.PLAN_ID
552 AND I.SR_INSTANCE_ID = PO.SR_INSTANCE_ID
553 AND I.ORGANIZATION_ID = PO.ORGANIZATION_ID
554 AND I.ATP_FLAG = 'Y'
555 AND D.PLAN_ID = I.PLAN_ID
556 AND D.SR_INSTANCE_ID = I.SR_INSTANCE_ID
557 AND D.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
558 AND D.ORGANIZATION_ID = I.ORGANIZATION_ID
559 AND D.ORIGINATION_TYPE NOT IN (1,4,5,7,8,9,11,15,22,28,29,31,48,49,53)
560 -- Ignore Plan Order Demand (Requested Outbound)
561 -- and Unconstrained Kit Demand for DRP Plans
562 -- Origination Type 1, 48
563 AND D.REFRESH_NUMBER BETWEEN (p_last_refresh_number + 1) and p_new_refresh_number
564
565 UNION ALL
566
567 SELECT I.sr_instance_id,
568 I.organization_id,
569 I.inventory_item_id,
570 TRUNC(NVL(S.FIRM_DATE,S.NEW_SCHEDULE_DATE)) SD_DATE,
571 NVL(S.FIRM_QUANTITY,S.NEW_ORDER_QUANTITY) SD_QTY
572 FROM MSC_SYSTEM_ITEMS I,
573 MSC_PLAN_ORGANIZATIONS PO,
574 MSC_SUPPLIES S
575 WHERE PO.plan_id = p_plan_id
576 AND I.PLAN_ID = PO.PLAN_ID
577 AND I.SR_INSTANCE_ID = PO.SR_INSTANCE_ID
578 AND I.ORGANIZATION_ID = PO.ORGANIZATION_ID
579 AND I.ATP_FLAG = 'Y'
580 AND S.PLAN_ID = I.PLAN_ID
581 AND S.SR_INSTANCE_ID = I.SR_INSTANCE_ID
582 AND S.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
583 AND S.ORGANIZATION_ID = I.ORGANIZATION_ID
584 AND NVL(S.DISPOSITION_STATUS_TYPE, 1) <> 2
585 AND NVL(S.FIRM_QUANTITY,S.NEW_ORDER_QUANTITY) <> 0
586 AND S.REFRESH_NUMBER BETWEEN (p_last_refresh_number + 1) and p_new_refresh_number
587 UNION ALL -- Net Planned arrival as outbound demand in source org.
588
589 SELECT I.sr_instance_id,
590 I.organization_id,
591 I.inventory_item_id,
592 TRUNC(NVL(S.NEW_SHIP_DATE,S.NEW_SCHEDULE_DATE)) SD_DATE,
593 -- Bug 4042808 Outbound Shipments are demands. Firm Supply Date
594 -- does not apply. (Previous Comment -- Firm Date is common across orgs).
595 -1 * NVL(S.FIRM_QUANTITY,S.NEW_ORDER_QUANTITY) SD_QTY
596 FROM MSC_SYSTEM_ITEMS I,
597 MSC_PLAN_ORGANIZATIONS PO,
598 MSC_SUPPLIES S
599 WHERE PO.plan_id = p_plan_id
600 AND I.PLAN_ID = PO.PLAN_ID
601 AND I.SR_INSTANCE_ID = PO.SR_INSTANCE_ID
602 AND I.ORGANIZATION_ID = PO.ORGANIZATION_ID
603 AND I.ATP_FLAG = 'Y'
604 AND S.PLAN_ID = I.PLAN_ID
605 AND S.SOURCE_SR_INSTANCE_ID = I.SR_INSTANCE_ID
606 AND S.SOURCE_ORGANIZATION_ID = I.ORGANIZATION_ID
607 AND S.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
608 AND NVL(S.DISPOSITION_STATUS_TYPE, 1) <> 2
609 AND S.ORDER_TYPE = 51 -- Planned Arrival is a Demand in Source Org
610 AND NVL(S.FIRM_QUANTITY,S.NEW_ORDER_QUANTITY) <> 0
611 AND S.REFRESH_NUMBER BETWEEN (p_last_refresh_number + 1) and p_new_refresh_number
612 )
613 GROUP BY inventory_item_id, organization_id, sr_instance_id, sd_date;
614
615 msc_util.msc_log('LOAD_SD_NET_DRP: ' || 'Total Row COUNT := ' || l_inventory_item_id_tab.COUNT);
616
617 IF l_inventory_item_id_tab.COUNT > 0 THEN
621 SET sd_qty = sd_qty + l_sd_quantity_tab(j),
618
619 forall j IN l_inventory_item_id_tab.first.. l_inventory_item_id_tab.last
620 UPDATE MSC_ATP_SUMMARY_SD
622 last_update_date = p_sys_date,
623 last_updated_by = l_user_id
624 WHERE plan_id = p_plan_id
625 AND sr_instance_id = l_sr_instance_id_tab(j)
626 AND inventory_item_id = l_inventory_item_id_tab(j)
627 AND organization_id = l_organization_id_tab(j)
628 AND sd_date = l_sd_date_tab(j);
629
630 msc_util.msc_log('LOAD_SD_NET_DRP: ' || 'After FORALL UPDATE');
631
632 FOR j IN l_inventory_item_id_tab.first.. l_inventory_item_id_tab.last LOOP
633 -- Count how many rows were updated for each item.
634 msc_util.msc_log('LOAD_SD_NET_DRP: For Item id '|| l_inventory_item_id_tab(j)||': updated '||
635 SQL%BULK_ROWCOUNT(j)||' records');
636 IF SQL%BULK_ROWCOUNT(j) = 0 THEN
637 l_ins_sr_instance_id_tab.EXTEND;
638 l_ins_organization_id_tab.EXTEND;
639 l_ins_inventory_item_id_tab.EXTEND;
640 l_ins_sd_date_tab.EXTEND;
641 l_ins_sd_quantity_tab.EXTEND;
642
643 l_ins_sr_instance_id_tab(l_ins_sr_instance_id_tab.LAST) := l_sr_instance_id_tab(j);
644 l_ins_organization_id_tab(l_ins_organization_id_tab.LAST) := l_organization_id_tab(j);
645 l_ins_inventory_item_id_tab(l_ins_inventory_item_id_tab.LAST) := l_inventory_item_id_tab(j);
646 l_ins_sd_date_tab(l_ins_sd_date_tab.LAST) := l_sd_date_tab(j);
647 l_ins_sd_quantity_tab(l_ins_sd_quantity_tab.LAST) := l_sd_quantity_tab(j);
648 END IF;
649 END LOOP;
650
651 msc_util.msc_log('LOAD_SD_NET_DRP: ' || 'l_ins_inventory_item_id_tab.COUNT := ' || l_ins_inventory_item_id_tab.COUNT);
652
653 IF l_ins_inventory_item_id_tab.COUNT > 0 THEN
654
655
656 forall j IN l_ins_inventory_item_id_tab.first.. l_ins_inventory_item_id_tab.last
657 INSERT INTO MSC_ATP_SUMMARY_SD (
658 plan_id,
659 sr_instance_id,
660 organization_id,
661 inventory_item_id,
662 demand_class,
663 sd_date,
664 sd_qty,
665 last_update_date,
666 last_updated_by,
667 creation_date,
668 created_by)
669 VALUES (p_plan_id,
670 l_ins_sr_instance_id_tab(j),
671 l_ins_organization_id_tab(j),
672 l_ins_inventory_item_id_tab(j),
673 '@@@',
674 l_ins_sd_date_tab(j),
675 l_ins_sd_quantity_tab(j),
676 p_sys_date,
677 l_user_id,
678 p_sys_date,
679 l_user_id);
680
681 msc_util.msc_log('LOAD_SD_NET_DRP: ' || 'After FORALL INSERT');
682 msc_util.msc_log('LOAD_SD_NET_DRP: ' || 'Total Records inserted : ' || SQL%ROWCOUNT);
683
684 ELSE
685 msc_util.msc_log('LOAD_SD_NET_DRP: ' || 'No records to be inserted');
686 END IF;
687 ELSE
688 msc_util.msc_log('LOAD_SD_NET_DRP: ' || 'No records fetched in the net cursor');
689 END IF;
690
691 msc_util.msc_log('******** LOAD_SD_NET_DRP End ********');
692
693 -- Exception included here since Array processing happens in this procedure
694 EXCEPTION
695 WHEN OTHERS THEN
696 msc_util.msc_log ('LOAD_SD_NET_DRP: ' || 'ERROR , sqlcode= '|| sqlcode);
697 msc_util.msc_log ('LOAD_SD_NET_DRP: IN Exception Block in others');
698 msc_util.msc_log ('ERROR := ' || SQLERRM);
699
700 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
701
702 END LOAD_SD_NET_DRP;
703
704 -- Procedure that nets supplies and demands for DRP plan when summary is enabled.
705 PROCEDURE get_mat_avail_drp_summ (
706 p_item_id IN NUMBER,
707 p_org_id IN NUMBER,
708 p_instance_id IN NUMBER,
709 p_plan_id IN NUMBER,
710 p_itf IN DATE,
711 p_refresh_number IN NUMBER, -- For summary enhancement
712 x_atp_dates OUT NoCopy MRP_ATP_PUB.date_arr,
713 x_atp_qtys OUT NoCopy MRP_ATP_PUB.number_arr
714 ) IS
715 BEGIN
716 IF PG_DEBUG in ('Y', 'C') THEN
717 msc_sch_wb.atp_debug('********** Begin Get_Mat_Avail_Drp_Summ **********');
718 msc_sch_wb.atp_debug('Get_Mat_Avail_Drp_Summ: p_item_id ' || p_item_id);
719 msc_sch_wb.atp_debug('Get_Mat_Avail_Drp_Summ: p_org_id ' || p_org_id);
720 msc_sch_wb.atp_debug('Get_Mat_Avail_Drp_Summ: p_instance_id ' || p_instance_id);
721 msc_sch_wb.atp_debug('Get_Mat_Avail_Drp_Summ: p_plan_id ' || p_plan_id);
722 msc_sch_wb.atp_debug('Get_Mat_Avail_Drp_Summ: p_itf ' || p_itf);
723 msc_sch_wb.atp_debug('Get_Mat_Avail_Drp_Summ: p_refresh_number ' || p_refresh_number);
724 END IF;
725
726 -- SQL changed for summary enhancement
727 SELECT SD_DATE, SUM(SD_QTY)
728 BULK COLLECT INTO x_atp_dates, x_atp_qtys
729 FROM (
730 SELECT /*+ INDEX(S MSC_ATP_SUMMARY_SD_U1) */
731 SD_DATE, SD_QTY
732 FROM MSC_ATP_SUMMARY_SD S
733 WHERE S.PLAN_ID = p_plan_id
734 AND S.SR_INSTANCE_ID = p_instance_id
735 AND S.INVENTORY_ITEM_ID = MSC_ATP_PVT.G_ITEM_INFO_REC.dest_inv_item_id
736 AND S.ORGANIZATION_ID = p_org_id
737 AND S.SD_DATE < NVL(p_itf, S.SD_DATE + 1)
738
739 UNION ALL
740
741 SELECT TRUNC(NVL(D.SCHEDULE_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE)) SD_DATE,--plan by request,promise,schedule date
742 decode(D.USING_REQUIREMENT_QUANTITY, -- Consider unscheduled orders as dummy supplies
743 0, nvl(D.OLD_DEMAND_QUANTITY,0), --4658238 -- For summary enhancement
744 -1 * D.USING_REQUIREMENT_QUANTITY) SD_QTY
745 FROM MSC_DEMANDS D,
746 MSC_PLANS P -- For summary enhancement
747 WHERE D.PLAN_ID = p_plan_id
748 AND D.SR_INSTANCE_ID = p_instance_id
749 AND D.INVENTORY_ITEM_ID = MSC_ATP_PVT.G_ITEM_INFO_REC.dest_inv_item_id
750 AND D.ORGANIZATION_ID = p_org_id
751 AND D.USING_REQUIREMENT_QUANTITY <> 0 --4501434
752 AND D.ORIGINATION_TYPE NOT IN (1,4,5,7,8,9,11,15,22,28,29,31,48,49,53)
753 -- Ignore Plan Order Demand (Requested Outbound)
754 -- and Unconstrained Kit Demand for DRP Plans
755 -- Origination Type 1, 48
756 AND trunc(NVL(D.SCHEDULE_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE)) <
757 trunc(NVL(p_itf, NVL(D.SCHEDULE_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE) + 1))
758 --plan by requestdate,promisedate,scheduledate
759 AND P.PLAN_ID = D.PLAN_ID
760 AND (D.REFRESH_NUMBER > P.LATEST_REFRESH_NUMBER
761 OR D.REFRESH_NUMBER = p_refresh_number)
762
763 UNION ALL
764
765 SELECT TRUNC(NVL(S.FIRM_DATE,S.NEW_SCHEDULE_DATE)) SD_DATE,
766 NVL(S.FIRM_QUANTITY,S.NEW_ORDER_QUANTITY) SD_QTY
767 FROM MSC_SUPPLIES S,
768 MSC_PLANS P -- For summary enhancement
769 WHERE S.PLAN_ID = p_plan_id
770 AND S.SR_INSTANCE_ID = p_instance_id
771 AND S.INVENTORY_ITEM_ID = MSC_ATP_PVT.G_ITEM_INFO_REC.dest_inv_item_id
772 AND S.ORGANIZATION_ID = p_org_id
773 AND NVL(S.DISPOSITION_STATUS_TYPE, 1) <> 2
774 AND NVL(S.FIRM_QUANTITY,S.NEW_ORDER_QUANTITY) <> 0
775 AND TRUNC(NVL(S.FIRM_DATE,S.NEW_SCHEDULE_DATE)) < NVL(p_itf, TRUNC(NVL(S.FIRM_DATE,S.NEW_SCHEDULE_DATE)) + 1)
776 AND P.PLAN_ID = S.PLAN_ID
777 AND (S.REFRESH_NUMBER > P.LATEST_REFRESH_NUMBER
778 OR S.REFRESH_NUMBER = p_refresh_number)
779
780 UNION ALL -- Net Planned arrival as outbound demand in source org.
781
782 SELECT TRUNC(NVL(S.NEW_SHIP_DATE,S.NEW_SCHEDULE_DATE)) SD_DATE,
783 -- Bug 4042808 Outbound Shipments are demands. Firm Supply Date
784 -- does not apply. (Previous Comment -- Firm Date is common across orgs).
785 -1 * NVL(S.FIRM_QUANTITY,S.NEW_ORDER_QUANTITY) SD_QTY
786 FROM MSC_SUPPLIES S,
787 MSC_PLANS P -- For summary enhancement
788 WHERE S.PLAN_ID = p_plan_id
789 AND S.SOURCE_SR_INSTANCE_ID = p_instance_id
790 AND S.SOURCE_ORGANIZATION_ID = p_org_id
791 AND S.INVENTORY_ITEM_ID = MSC_ATP_PVT.G_ITEM_INFO_REC.dest_inv_item_id
792 AND NVL(S.DISPOSITION_STATUS_TYPE, 1) <> 2
793 AND S.ORDER_TYPE = 51 -- Planned Arrival is a Demand in Source Org
794 AND NVL(S.FIRM_QUANTITY,S.NEW_ORDER_QUANTITY) <> 0
795 AND TRUNC(NVL(S.FIRM_DATE,S.NEW_SHIP_DATE)) < NVL(p_itf, TRUNC(NVL(S.FIRM_DATE,S.NEW_SHIP_DATE)) + 1)
796 AND P.PLAN_ID = S.PLAN_ID
797 AND (S.REFRESH_NUMBER > P.LATEST_REFRESH_NUMBER
798 OR S.REFRESH_NUMBER = p_refresh_number)
799 )
800 GROUP BY SD_DATE
801 ORDER BY SD_DATE;
802
803 IF PG_DEBUG in ('Y', 'C') THEN
804 msc_sch_wb.atp_debug('Get_Mat_Avail_Drp_Summ : Total Row Count ' || x_atp_qtys.COUNT );
805 msc_sch_wb.atp_debug('******** Get_Mat_Avail_Drp_Summ End ********');
806 END IF;
807
808 END get_mat_avail_drp_summ;
809
810 END MSC_ATP_DRP;