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