1 PACKAGE BODY MSC_AATP_PROC AS
2 /* $Header: MSCPAATB.pls 120.1 2007/12/12 10:32:59 sbnaik ship $ */
3
4 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('MSC_ATP_DEBUG'), 'N');
5
6 PROCEDURE Add_to_current_atp (
7 p_steal_atp IN OUT NOCOPY MRP_ATP_PVT.ATP_Info,
8 p_current_atp IN OUT NOCOPY MRP_ATP_PVT.ATP_Info,
9 x_return_status OUT NOCOPY VARCHAR2
10 ) IS
11 i PLS_INTEGER; -- index for p_current_atp
12 j PLS_INTEGER; -- index for p_steal_atp
13 k PLS_INTEGER; -- index for l_current_atp
14 n PLS_INTEGER; -- starting point of p_steal_atp
15 l_current_atp MRP_ATP_PVT.ATP_Info; -- this will be the output
16 l_processed BOOLEAN;
17
18 BEGIN
19
20 IF PG_DEBUG in ('Y', 'C') THEN
21 msc_sch_wb.atp_debug('**********Begin Add_to_current_atp Procedure************');
22 END IF;
23
24 -- initialize API return status to success
25 x_return_status := FND_API.G_RET_STS_SUCCESS;
26
27 j := p_current_atp.atp_period.FIRST;
28 k := 0;
29 FOR i IN 1..p_steal_atp.atp_period.COUNT LOOP
30
31 IF PG_DEBUG in ('Y', 'C') THEN
32 msc_sch_wb.atp_debug ('Add_to_current_atp: ' || 'we are in loop i = '||i);
33 END IF;
34 IF p_steal_atp.atp_qty(i) < 0 THEN
35 l_processed := FALSE;
36 WHILE (j IS NOT NULL) LOOP
37 IF PG_DEBUG in ('Y', 'C') THEN
38 msc_sch_wb.atp_debug ('Add_to_current_atp: ' || 'we are in loop j = '||j);
39 END IF;
40 k := k+1;
41 l_current_atp.atp_period.Extend;
42 l_current_atp.atp_qty.Extend;
43
44 IF p_current_atp.atp_period(j) < p_steal_atp.atp_period(i) THEN
45
46 -- we add this to l_current_atp
47 l_current_atp.atp_period(k) := p_current_atp.atp_period(j);
48 l_current_atp.atp_qty(k) := p_current_atp.atp_qty(j);
49
50 ELSIF p_current_atp.atp_period(j)=p_steal_atp.atp_period(i) THEN
51
52 -- both record (p_current_atp and p_steal_atp) are on the same
53 -- date. we need to sum them up
54 l_processed := TRUE;
55 l_current_atp.atp_period(k) := p_current_atp.atp_period(j);
56 l_current_atp.atp_qty(k) := p_current_atp.atp_qty(j) +
57 p_steal_atp.atp_qty(i);
58 -- j := j+1;
59 j := p_current_atp.atp_period.NEXT(j);
60 EXIT; -- exit the loop since we had done group by before. so
61 -- we don't need to go to next record any more
62 ELSE -- this is the greater part
63 l_processed := TRUE;
64 l_current_atp.atp_period(k) := p_steal_atp.atp_period(i);
65 l_current_atp.atp_qty(k) := p_steal_atp.atp_qty(i);
66 EXIT; -- exit the loop since we had done group by before.
67
68 END IF;
69 j := p_current_atp.atp_period.NEXT(j) ;
70 END LOOP;
71
72 IF (j is null) AND (l_processed = FALSE) THEN
73 -- this means p_current_atp is over,
74 -- so we don't need to worry about p_next_steak_atp,
75 -- we just keep add p_steal_atp to l_current_atp
76 -- if they are not added before
77 k := k+1;
78 l_current_atp.atp_period.Extend;
79 l_current_atp.atp_qty.Extend;
80
81 l_current_atp.atp_period(k) := p_steal_atp.atp_period(i);
82 l_current_atp.atp_qty(k) := p_steal_atp.atp_qty(i);
83 END IF;
84 p_steal_atp.atp_qty(i) := 0;
85
86 END IF; -- p_steal_atp.atp_qty < 0
87 END LOOP;
88
89 -- now we have taken care of all p_steal_atp and part of
90 -- p_current_atp. now we need to take care the rest of p_current_atp
91
92 -- FOR j IN n..p_current_atp.atp_period.COUNT LOOP
93 WHILE j is not null LOOP
94 -- we add this to l_current_atp
95 k := k+1;
96 l_current_atp.atp_period.Extend;
97 l_current_atp.atp_qty.Extend;
98 l_current_atp.atp_period(k) := p_current_atp.atp_period(j);
99 l_current_atp.atp_qty(k) := p_current_atp.atp_qty(j);
100 j := p_current_atp.atp_period.NEXT(j);
101 END LOOP;
102
103 p_current_atp := l_current_atp;
104 IF PG_DEBUG in ('Y', 'C') THEN
105 msc_sch_wb.atp_debug('**********End Add_to_current_atp Procedure************');
106 END IF;
107
108 EXCEPTION
109 WHEN OTHERS THEN
110 IF PG_DEBUG in ('Y', 'C') THEN
111 msc_sch_wb.atp_debug('Add_to_current_atp: ' || 'Error code:' || to_char(sqlcode));
112 END IF;
113 x_return_status := FND_API.G_RET_STS_ERROR;
114
115 END Add_to_current_atp;
116
117 PROCEDURE Atp_Forward_Consume (
118 p_atp_period IN MRP_ATP_PUB.date_arr,
119 p_atf_date IN DATE,
120 p_atp_qty IN OUT NOCOPY MRP_ATP_PUB.number_arr,
121 x_return_status OUT NOCOPY VARCHAR2
122 ) IS
123 i PLS_INTEGER;
124 j PLS_INTEGER;
125 l_counter PLS_INTEGER;
126 -- time_phased_atp
127 l_fw_nullifying_bucket_index NUMBER := 1;
128 BEGIN
129
130 IF PG_DEBUG in ('Y', 'C') THEN
131 msc_sch_wb.atp_debug('**********Begin Atp_Forward_Consume Procedure************');
132 END IF;
133
134 -- initialize API return status to success
135 x_return_status := FND_API.G_RET_STS_SUCCESS;
136
137 l_counter := p_atp_qty.COUNT;
138 FOR i in 1..l_counter LOOP
139
140 -- this loop will do forward consumption
141 -- forward consumption when neg atp quantity occurs
142 IF (p_atp_qty(i) < 0 ) THEN
143 j := i + 1;
144 WHILE (j <= l_counter) LOOP
145 IF ((p_atp_period(i)<=p_atf_date) and (p_atp_period(j)>p_atf_date)) THEN
146 -- exit loop when crossing time fence
147 j := l_counter + 1;
148 ELSIF (p_atp_qty(j) <= 0 OR j < l_fw_nullifying_bucket_index) THEN
149 -- forward one more period
150 j := j+1 ;
151 ELSE
152 -- You can get something from here. So set the nullifying bucket index
153 l_fw_nullifying_bucket_index := j;
154 IF (p_atp_qty(j) + p_atp_qty(i) < 0) THEN
155 -- not enough to cover the shortage
156 p_atp_qty(i) := p_atp_qty(i) + p_atp_qty(j);
157 p_atp_qty(j) := 0;
158 j := j+1;
159 ELSE
160 -- enough to cover the shortage
161 p_atp_qty(j) := p_atp_qty(j) + p_atp_qty(i);
162 p_atp_qty(i) := 0;
163 j := l_counter + 1;
164 END IF;
165 END IF;
166 END LOOP;
167 END IF;
168
169 END LOOP;
170
171 IF PG_DEBUG in ('Y', 'C') THEN
172 msc_sch_wb.atp_debug('**********End Atp_Forward_Consume Procedure************');
173 END IF;
174
175 EXCEPTION
176 WHEN OTHERS THEN
177 IF PG_DEBUG in ('Y', 'C') THEN
178 msc_sch_wb.atp_debug('Atp_Forward_Consume: ' || 'Error code:' || to_char(sqlcode));
179 END IF;
180 x_return_status := FND_API.G_RET_STS_ERROR;
181
182 END Atp_Forward_Consume;
183
184 PROCEDURE Atp_Adjusted_Cum (
185 p_current_atp IN OUT NOCOPY MRP_ATP_PVT.ATP_Info,
186 p_unallocated_atp IN OUT NOCOPY MRP_ATP_PVT.ATP_Info,
187 x_return_status OUT NOCOPY VARCHAR2
188 ) IS
189 i PLS_INTEGER;
190 j PLS_INTEGER;
191 l_counter PLS_INTEGER;
192 BEGIN
193
194 IF PG_DEBUG in ('Y', 'C') THEN
195 msc_sch_wb.atp_debug('**********Begin Atp_Adjusted_Cum Procedure************');
196 END IF;
197
198 -- initialize API return status to success
199 x_return_status := FND_API.G_RET_STS_SUCCESS;
200
201 i := p_current_atp.atp_period.LAST;
202 While i is not null LOOP
203
204 IF PG_DEBUG in ('Y', 'C') THEN
205 msc_sch_wb.atp_debug ('Atp_Adjusted_Cum: ' || 'i = '||i);
206 END IF;
207 p_current_atp.atp_qty(i) := GREATEST(LEAST(p_current_atp.atp_qty(i),
208 p_unallocated_atp.atp_qty(i)), 0);
209
210 p_unallocated_atp.atp_qty(i) := p_unallocated_atp.atp_qty(i) - p_current_atp.atp_qty(i);
211
212 --rajjain Bug 2793336 03/10/2003 Begin
213 IF i <> p_current_atp.atp_period.LAST
214 AND p_unallocated_atp.atp_qty(i) > p_unallocated_atp.atp_qty(i+1)
215 THEN
216 IF PG_DEBUG in ('Y', 'C') THEN
217 msc_sch_wb.atp_debug ('Atp_Adjusted_Cum: ' || 'Unallocated Cum Date:Qty - '||
218 p_current_atp.atp_period(i) ||' : '|| p_unallocated_atp.atp_qty(i) );
219 END IF;
220 p_unallocated_atp.atp_qty(i) := p_unallocated_atp.atp_qty(i+1);
221 IF PG_DEBUG in ('Y', 'C') THEN
222 msc_sch_wb.atp_debug ('Atp_Adjusted_Cum: ' || 'Updated Unallocated Cum Date:Qty - '||
223 p_current_atp.atp_period(i) ||' : '|| p_unallocated_atp.atp_qty(i) );
224 END IF;
225 END IF;
226 --rajjain Bug 2793336 03/10/2003 End
227 i := p_current_atp.atp_period.Prior(i);
228 END LOOP;
229
230 IF PG_DEBUG in ('Y', 'C') THEN
231 msc_sch_wb.atp_debug('**********End Atp_Adjusted_Cum Procedure************');
232 END IF;
233
234 EXCEPTION
235 WHEN OTHERS THEN
236 IF PG_DEBUG in ('Y', 'C') THEN
237 msc_sch_wb.atp_debug('Atp_Adjusted_Cum: ' || 'Error code:' || to_char(sqlcode));
238 END IF;
239 x_return_status := FND_API.G_RET_STS_ERROR;
240
241 END Atp_Adjusted_Cum;
242
243 PROCEDURE Atp_Remove_Negatives (
244 p_atp_qty IN OUT NOCOPY MRP_ATP_PUB.number_arr,
245 x_return_status OUT NOCOPY VARCHAR2
246 ) IS
247 i PLS_INTEGER;
248 l_counter PLS_INTEGER;
249 BEGIN
250
251 IF PG_DEBUG in ('Y', 'C') THEN
252 msc_sch_wb.atp_debug('**********Begin Atp_Remove_Negatives Procedure************');
253 END IF;
254
255 -- initialize API return status to success
256 x_return_status := FND_API.G_RET_STS_SUCCESS;
257
258 l_counter := p_atp_qty.COUNT;
259 FOR i in 1..l_counter LOOP
260
261 -- this loop will remove negatives
262 IF (p_atp_qty(i) < 0 ) THEN
263 p_atp_qty(i) := 0;
264
265 IF PG_DEBUG in ('Y', 'C') THEN
266 msc_sch_wb.atp_debug('Atp_Remove_Negatives: ' || 'we are in loop for removing negatives, i='||i);
267 END IF;
268 END IF;
269
270 END LOOP;
271
272 IF PG_DEBUG in ('Y', 'C') THEN
273 msc_sch_wb.atp_debug('**********End Atp_Remove_Negatives Procedure************');
274 END IF;
275
276 EXCEPTION
277 WHEN OTHERS THEN
278 IF PG_DEBUG in ('Y', 'C') THEN
279 msc_sch_wb.atp_debug('Atp_Remove_Negatives: ' || 'Error code:' || to_char(sqlcode));
280 END IF;
281 x_return_status := FND_API.G_RET_STS_ERROR;
282
283 END Atp_Remove_Negatives;
284
285 PROCEDURE get_unalloc_data_from_SD_temp(
286 x_atp_period OUT NOCOPY MRP_ATP_PUB.ATP_Period_Typ,
287 p_unallocated_atp IN OUT NOCOPY MRP_ATP_PVT.ATP_Info,
288 x_return_status OUT NOCOPY VARCHAR2
289 ) IS
290 i NUMBER;
291 j NUMBER;
292 BEGIN
293
294 IF PG_DEBUG in ('Y', 'C') THEN
295 msc_sch_wb.atp_debug('PROCEDURE get_unalloc_data_from_SD_temp');
296 END IF;
297
298 -- initialize API return status to success
299 x_return_status := FND_API.G_RET_STS_SUCCESS;
300
301 -- do netting for unallocated qty also
302 SELECT
303 ATP_level
304 ,scenario_id
305 ,inventory_item_id
306 ,request_item_id
307 ,organization_id
308 ,supplier_id
309 ,supplier_site_id
310 ,department_id
311 ,resource_id
312 ,supply_demand_date
313 ,identifier1
314 ,identifier2
315 ,SUM(DECODE(supply_demand_type, 1, allocated_quantity, 0))
316 total_demand_quantity
317 ,SUM(DECODE(supply_demand_type, 2, allocated_quantity, 0))
318 total_supply_quantity
319 ,SUM(allocated_quantity)
320 ,SUM(unallocated_quantity)
321 BULK COLLECT INTO
322 x_atp_period.Level,
323 x_atp_period.Scenario_Id,
324 x_atp_period.Inventory_Item_Id,
325 x_atp_period.Request_Item_Id,
326 x_atp_period.Organization_id,
327 x_atp_period.Supplier_ID,
328 x_atp_period.Supplier_Site_ID,
329 x_atp_period.Department_id,
330 x_atp_period.Resource_id,
331 x_atp_period.Period_Start_Date,
332 x_atp_period.Identifier1,
333 x_atp_period.Identifier2,
334 x_atp_period.Total_Demand_Quantity,
335 x_atp_period.Total_Supply_Quantity,
336 x_atp_period.Period_Quantity,
337 p_unallocated_atp.atp_qty
338 FROM msc_atp_sd_details_temp
339 GROUP BY
340 supply_demand_date
341 ,ATP_level
342 ,scenario_id
343 ,inventory_item_id
344 ,request_item_id
345 ,organization_id
346 ,supplier_id
347 ,supplier_site_id
348 ,department_id
349 ,resource_id
350 ,identifier1
351 ,identifier2
352 ORDER BY supply_demand_date;
353
354
355 -- set the period end dates and
356 -- extend the remaining fields to ensure same behaviour as before
357 i := x_atp_period.Period_Start_Date.COUNT;
358
359 x_atp_period.Identifier.EXTEND(i);
360 x_atp_period.Pegging_Id.EXTEND(i);
361 x_atp_period.End_Pegging_Id.EXTEND(i);
362 x_atp_period.Period_End_Date.EXTEND(i);
363 x_atp_period.From_Location_Id.EXTEND(i);
364 x_atp_period.From_Organization_Id.EXTEND(i);
365 x_atp_period.Ship_Method.EXTEND(i);
366 x_atp_period.To_Location_Id.EXTEND(i);
367 x_atp_period.To_Organization_Id.EXTEND(i);
368 x_atp_period.Uom.EXTEND(i);
369 x_atp_period.total_bucketed_demand_quantity.extend(i); -- time_phased_atp
370
371 FOR j IN 1..(i-1) LOOP
372 x_atp_period.Period_End_Date(j) :=
373 x_atp_period.Period_Start_Date(j+1) - 1;
374 END LOOP;
375
376 EXCEPTION
377 WHEN OTHERS THEN
378 IF PG_DEBUG in ('Y', 'C') THEN
379 msc_sch_wb.atp_debug('get_unalloc_data_from_SD_temp: ' || 'Error code:' || to_char(sqlcode));
380 END IF;
381 x_return_status := FND_API.G_RET_STS_ERROR;
382
383 END get_unalloc_data_from_SD_temp;
384
385 END MSC_AATP_PROC;
[Home] [Help]