[Home] [Help]
PACKAGE BODY: APPS.OE_ATP
Source
1 PACKAGE BODY OE_ATP AS
2 /* $Header: OEXVATPB.pls 120.1.12020000.2 2013/01/18 22:49:06 gabhatia ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):='OE_ATP';
5
6
7 /*-----------------------------------------------------------------------------
8 Procedure Name : ATP_Check
9 Description :
10 ----------------------------------------------------------------------------- */
11
12 Procedure ATP_Check( p_line_atp_rec IN line_atp_rec_type
13 ,p_old_line_atp_rec IN line_atp_rec_type
14 ,x_atp_rec OUT NOCOPY /* file.sql.39 change */ atp_rec_type
15 ,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
16 )
17 IS
18 l_line_rec OE_ORDER_PUB.line_rec_type;
19 l_old_line_rec OE_ORDER_PUB.line_rec_type;
20 l_atp_tbl OE_ATP.atp_tbl_type;
21 --- 2697690 --
22 l_scheduling_level_code VARCHAR2(30) := NULL;
23 --
24 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
25 --
26 l_validity_flag NUMBER := 0; -- for bug 2549166
27
28 BEGIN
29
30 IF l_debug_level > 0 THEN
31 oe_debug_pub.add( 'ENTERING ATP_CHECK' , 1 ) ;
32 oe_debug_pub.add( 'BEFORE FIRST ASSIGN ATPREC' , 3 ) ;
33 oe_debug_pub.add( 'NEW LINE TYPE : '||TO_CHAR ( P_LINE_ATP_REC.LINE_TYPE_ID ) , 3 ) ;
34 oe_debug_pub.add( 'OLD LINE TYPE : '||TO_CHAR ( P_OLD_LINE_ATP_REC.LINE_TYPE_ID ) , 3 ) ;
35 END IF;
36
37 -- Check Min Attributes Required to do ATP
38
39 IF l_debug_level > 0 THEN
40 oe_debug_pub.add( 'R1' , 3 ) ;
41 END IF;
42 IF (p_line_atp_rec.inventory_item_id IS null) OR
43 (p_line_atp_rec.inventory_item_id = FND_API.G_MISS_NUM)
44 THEN
45 null;
46 goto end_atp_check;
47 END IF;
48
49 IF l_debug_level > 0 THEN
50 oe_debug_pub.add( 'R2' , 3 ) ;
51 END IF;
52 IF (p_line_atp_rec.ordered_quantity IS null) OR
53 (p_line_atp_rec.ordered_quantity = FND_API.G_MISS_NUM) OR
54 (p_line_atp_rec.ordered_quantity <= 0) OR
55 (p_line_atp_rec.line_category_code = 'RETURN')
56 THEN
57 IF l_debug_level > 0 THEN
58 oe_debug_pub.add( 'ORDERED_QUANTITY IS NULL' , 3 ) ;
59 END IF;
60 goto end_atp_check;
61 END IF;
62
63 IF l_debug_level > 0 THEN
64 oe_debug_pub.add( 'R3' , 3 ) ;
65 END IF;
66 IF (p_line_atp_rec.order_quantity_uom IS null) OR
67 (p_line_atp_rec.order_quantity_uom = FND_API.G_MISS_CHAR)
68 THEN
69 null;
70 goto end_atp_check;
71 END IF;
72
73 IF l_debug_level > 0 THEN
74 oe_debug_pub.add( 'R4' , 3 ) ;
75 END IF;
76 IF (p_line_atp_rec.request_date IS null) OR
77 (p_line_atp_rec.request_date = FND_API.G_MISS_DATE)
78 THEN
79 null;
80 goto end_atp_check;
81 END IF;
82
83 IF l_debug_level > 0 THEN
84 oe_debug_pub.add( 'R5 ', 3 ) ;
85 END IF;
86
87 -- 3670442 : added check for External source and close order
88 IF p_line_atp_rec.source_type_code = OE_GLOBALS.G_SOURCE_EXTERNAL
89 THEN
90 IF l_debug_level > 0 THEN
91 oe_debug_pub.add( 'INVALID SOURCE TYPE '||p_line_atp_rec.source_type_code , 3 ) ;
92 END IF;
93 goto end_atp_check;
94 END IF;
95
96 IF (p_line_atp_rec.shipped_quantity is not null) AND
97 (p_line_atp_rec.shipped_quantity <> FND_API.G_MISS_NUM) THEN
98 IF l_debug_level > 0 THEN
99 oe_debug_pub.add( 'LINE IS SHIPPED' , 3 ) ;
100 END IF;
101 goto end_atp_check;
102 END IF;
103
104 IF NVL(p_line_atp_rec.open_flag,'Y') = 'N'
105 THEN
106 IF l_debug_level > 0 THEN
107 oe_debug_pub.add( 'IT IS A CLOSED LINE' , 3 ) ;
108 END IF;
109 goto end_atp_check;
110 END IF;
111
112 IF (p_line_atp_rec.item_type_code <> OE_GLOBALS.G_ITEM_STANDARD)
113 THEN
114 IF l_debug_level > 0 THEN
115 oe_debug_pub.add( 'NOT A STANDARD LINE' , 3 ) ;
116 END IF;
117 goto end_atp_check;
118 END IF;
119
120 IF l_debug_level > 0 THEN
121 oe_debug_pub.add( 'R6' , 3 ) ;
122 END IF;
123
124 --Added for Bug 2549166
125
126 IF (p_line_atp_rec.ship_from_org_id IS null) OR
127 (p_line_atp_rec.ship_from_org_id = FND_API.G_MISS_NUM)
128 THEN
129 null;
130 goto end_atp_check;
131 ELSE
132 BEGIN
133 SELECT 1
134 INTO l_validity_flag
135 FROM mtl_system_items_b msi,
136 org_organization_definitions org
137 WHERE msi.inventory_item_id= p_line_atp_rec.inventory_item_id
138 AND org.organization_id=msi.organization_id
139 AND sysdate<=nvl(org.disable_date,sysdate)
140 AND org.organization_id=p_line_atp_rec.ship_from_org_id
141 AND rownum=1;
142 EXCEPTION
143 WHEN NO_DATA_FOUND THEN
144 IF l_debug_level > 0 THEN
145 oe_debug_pub.add('INVALID ITEM WAREHOUSE COMBINATION',3);
146 END IF;
147 goto end_atp_check;
148 END;
149 END IF;
150 IF l_debug_level > 0 THEN
151 oe_debug_pub.add('R7',3);
152 END IF;
153
154 --End of Modification for Bug 2549166
155
156 --- Start 2697690 --
157 l_scheduling_level_code :=
158 NVL(Oe_Schedule_Util.Get_Scheduling_Level(p_line_atp_rec.header_id,
159 p_line_atp_rec.line_type_id),OE_SCHEDULE_UTIL.SCH_LEVEL_THREE);
160 --3763015
161 IF l_scheduling_level_code = OE_SCHEDULE_UTIL.SCH_LEVEL_FOUR OR
162 l_scheduling_level_code = OE_SCHEDULE_UTIL.SCH_LEVEL_FIVE OR
163 NVL(fnd_profile.value('ONT_BYPASS_ATP'),'N') = 'Y' THEN
164
165 IF l_debug_level > 0 THEN
166 oe_debug_pub.add( 'FOUR AND FIVE CANNOT HAVE ATP PERFORMED' , 3 ) ;
167 END IF;
168
169 -- start 3431595
170 IF p_line_atp_rec.p_pa_call then
171 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
172 FND_MESSAGE.SET_NAME('ONT','OE_SCH_ACTION_NOT_ALLOWED');
173 FND_MESSAGE.SET_TOKEN('ACTION',OE_SCHEDULE_UTIL.OESCH_ACT_ATP_CHECK);
174
175 IF p_line_atp_rec.line_type_id is not null THEN
176 FND_MESSAGE.SET_TOKEN('ORDER_TYPE',OE_SCHEDULE_UTIL.sch_cached_line_type);
177 ELSE
178 FND_MESSAGE.SET_TOKEN('ORDER_TYPE',OE_SCHEDULE_UTIL.sch_cached_order_type);
179 END IF;
180
181 OE_MSG_PUB.Add;
182 END IF;
183
184 --end 3431595
185
186 goto end_atp_check;
187
188 END IF;
189 --- End 2697690 --
190
191
192 Assign_Atprec(p_line_atp_rec => p_old_line_atp_rec
193 ,x_line_rec => l_old_line_rec);
194
195 IF l_debug_level > 0 THEN
196 oe_debug_pub.add( 'AFTER FIRST ASSIGN ATPREC' , 3 ) ;
197 END IF;
198 IF l_debug_level > 0 THEN
199 oe_debug_pub.add( 'LINE TYPE ON LINE : '||TO_CHAR ( L_OLD_LINE_REC.LINE_TYPE_ID ) , 3 ) ;
200 END IF;
201
202 Assign_Atprec(p_line_atp_rec => p_line_atp_rec
203 ,x_line_rec => l_line_rec);
204
205 IF l_debug_level > 0 THEN
206 oe_debug_pub.add( 'LINE TYPE ON LINE NEW : '||TO_CHAR ( L_LINE_REC.LINE_TYPE_ID ) , 3 ) ;
207 END IF;
208
209 l_line_rec.item_type_code := OE_GLOBALS.G_ITEM_STANDARD;
210 l_line_rec.schedule_action_code := OE_ORDER_SCH_UTIL.OESCH_ACT_ATP_CHECK;
211
212 -- Bug4504362
213
214 IF l_debug_level > 0 THEN
215 oe_debug_pub.add( 'SCH:CALLING OE_SCHEDULE_UTIL.ATP_CHECK' , 2 ) ;
216 END IF;
217
218 OE_SCHEDULE_UTIL.atp_check(
219 p_old_line_rec => l_old_line_rec,
220 p_validate => FND_API.G_FALSE,
221 p_x_line_rec => l_line_rec,
222 x_atp_tbl => l_atp_tbl,
223 x_return_status => x_return_status);
224
225
226 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
227 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
228 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
229 RAISE FND_API.G_EXC_ERROR;
230 END IF;
231
232 IF l_debug_level > 0 THEN
233 oe_debug_pub.add( 'PRINTING THE ATP RECORD' , 3 ) ;
234 END IF;
235
236 x_atp_rec := l_atp_tbl(1);
237
238 <<end_atp_check>>
239 null;
240 IF l_debug_level > 0 THEN
241 oe_debug_pub.add( 'EXITING ATP_CHECK' || X_RETURN_STATUS , 1 ) ;
242 END IF;
243
244 EXCEPTION
245 WHEN FND_API.G_EXC_ERROR THEN
246
247 x_return_status := FND_API.G_RET_STS_ERROR;
248
249 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
250
251 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
252
253 WHEN OTHERS THEN
254
255 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
256
257 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
258 THEN
259 FND_MSG_PUB.Add_Exc_Msg
260 ( G_PKG_NAME
261 , 'ATP_Check'
262 );
263 END IF;
264
265 END ATP_Check;
266
267 Procedure Assign_Atprec(p_line_atp_rec IN line_atp_rec_type
268 ,x_line_rec IN OUT NOCOPY OE_ORDER_PUB.line_rec_type)
269 IS
270 --
271 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
272 --
273 BEGIN
274 x_line_rec.arrival_set_id := p_line_atp_rec.arrival_set_id;
275 x_line_rec.ato_line_id := p_line_atp_rec.ato_line_id;
276 x_line_rec.demand_class_code := p_line_atp_rec.demand_class_code;
277 x_line_rec.delivery_lead_time := p_line_atp_rec.delivery_lead_time;
278 x_line_rec.freight_carrier_code := p_line_atp_rec.freight_carrier_code;
279 x_line_rec.header_id := p_line_atp_rec.header_id;
280 x_line_rec.ordered_item := p_line_atp_rec.item_input;
281 x_line_rec.inventory_item_id := p_line_atp_rec.inventory_item_id;
282 x_line_rec.item_type_code := p_line_atp_rec.item_type_code;
283 x_line_rec.line_id := p_line_atp_rec.line_id;
284 x_line_rec.ordered_quantity := p_line_atp_rec.ordered_quantity;
285 x_line_rec.order_quantity_uom := p_line_atp_rec.order_quantity_uom;
286 x_line_rec.request_date := p_line_atp_rec.request_date;
287 x_line_rec.schedule_ship_date := p_line_atp_rec.schedule_ship_date;
288 x_line_rec.schedule_arrival_date := p_line_atp_rec.schedule_arrival_date;
289 x_line_rec.latest_acceptable_date := p_line_atp_rec.latest_acceptable_date;
290 x_line_rec.ship_from_org_id := p_line_atp_rec.ship_from_org_id;
291 x_line_rec.ship_model_complete_flag := p_line_atp_rec.ship_model_complete_flag;
292 x_line_rec.ship_set_id := p_line_atp_rec.ship_set_id;
293 x_line_rec.ship_to_org_id := p_line_atp_rec.ship_to_org_id;
294 x_line_rec.source_type_code := p_line_atp_rec.source_type_code;
295 x_line_rec.shipping_method_code := p_line_atp_rec.shipping_method_code;
296 x_line_rec.sold_to_org_id := p_line_atp_rec.sold_to_org_id;
297 x_line_rec.top_model_line_id := p_line_atp_rec.top_model_line_id;
298 x_line_rec.line_type_id := p_line_atp_rec.line_type_id;
299 x_line_rec.fulfillment_base := p_line_atp_rec.fulfillment_base; --ER#14763609
300
301 END Assign_Atprec;
302
303 /*-------------------------------------------------------------------
304 Procedure Name : ATP_Inquiry
305 Description : This will be called when Manual ATP Inquiry on a
306 line(which has been saved).
307
308 ------------------------------------------------------------------- */
309
310 Procedure ATP_Inquiry( p_entity_type IN VARCHAR2
311 , p_entity_id IN NUMBER
312 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
313 , x_atp_tbl OUT NOCOPY /* file.sql.39 change */ ATP_TBL_TYPE)
314 IS
315 l_line_atp_rec OE_ORDER_PUB.line_rec_type;
316 l_out_atp_rec OE_ATP.atp_rec_type;
317 l_out_atp_tbl OE_ATP.atp_tbl_type;
318
319 l_return_status VARCHAR2(1);
320 l_entity_type VARCHAR2(30);
321 l_entity_id NUMBER;
322 l_group_req_rec OE_GRP_SCH_UTIL.Sch_Group_Rec_Type;
323 --
324 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
325 --
326 BEGIN
327
328 IF l_debug_level > 0 THEN
329 oe_debug_pub.add( 'ENTERING ATP_INQUIRY' , 1 ) ;
330 END IF;
331 l_entity_type := p_entity_type;
332 l_entity_id := p_entity_id;
333
334
335 IF (p_entity_type <> OE_ORDER_SCH_UTIL.OESCH_ENTITY_O_LINE) THEN
336 OE_GRP_SCH_UTIL.Group_Schedule
337 (p_group_req_rec => l_group_req_rec,
338 x_atp_tbl => l_out_atp_tbl,
339 x_return_status => l_return_status);
340
341 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
342 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
343 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
344 RAISE FND_API.G_EXC_ERROR;
345 END IF;
346 END IF;
347
348 -- Perform ATP Inquiry for Standard Lines
349
350 -- l_line_atp_rec := OE_LINE_UTIL.Query_Row(l_entity_id);
351 OE_Line_Util.Query_Row(p_line_id => l_entity_id,
352 x_line_rec => l_line_atp_rec);
353
354 IF l_debug_level > 0 THEN
355 oe_debug_pub.add( 'CALLING ATP_CHECK' , 2 ) ;
356 END IF;
357
358 /* ATP_Check(p_line_atp_rec => l_line_atp_rec
359 ,p_old_line_atp_rec => l_line_atp_rec
360 ,x_atp_rec => l_out_atp_rec
361 ,x_return_status => l_return_status
362 );
363 */
364
365 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
366 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
367 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
368 RAISE FND_API.G_EXC_ERROR;
369 END IF;
370
371 x_atp_tbl(1) := l_out_atp_rec;
372
373 IF l_debug_level > 0 THEN
374 oe_debug_pub.add( 'EXITING ATP_INQUIRY' , 1 ) ;
375 END IF;
376
377 EXCEPTION
378 WHEN FND_API.G_EXC_ERROR THEN
379
380 x_return_status := FND_API.G_RET_STS_ERROR;
381
382 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
383
384 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
385
386 WHEN OTHERS THEN
387
388 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
389
390 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
391 THEN
392 FND_MSG_PUB.Add_Exc_Msg
393 ( G_PKG_NAME
394 , 'Schedule_line'
395 );
396 END IF;
397
398 END ATP_Inquiry;
399
400 END OE_ATP;