[Home] [Help]
PACKAGE BODY: APPS.AHL_LTP_MATRL_AVAL_PVT
Source
1 PACKAGE BODY AHL_LTP_MATRL_AVAL_PVT AS
2 /* $Header: AHLVMTAB.pls 120.14.12020000.2 2012/12/12 00:44:54 jaramana ship $ */
3 --
4 G_PKG_NAME VARCHAR2(30) := 'AHL_LTP_MATRL_AVAL_PVT';
5 G_DEBUG VARCHAR2(1) := AHL_DEBUG_PUB.is_log_enabled;
6 ------------------------------------
7 -- Common constants and variables --
8 ------------------------------------
9 l_log_current_level NUMBER := fnd_log.g_current_runtime_level;
10 l_log_statement NUMBER := fnd_log.level_statement;
11 l_log_procedure NUMBER := fnd_log.level_procedure;
12 l_log_error NUMBER := fnd_log.level_error;
13 l_log_unexpected NUMBER := fnd_log.level_unexpected;
14 -----------------------------------------------------------------------
15 --
16 -- PACKAGE
17 -- AHL_LTP_MATRL_AVAL_PVT
18 --
19 -- PURPOSE
20 -- This package is used to derive requested materials for an item which is associated
21 -- to visit task. It calls ATP to check material availabilty
22 --
23 -- NOTES
24 --
25 --
26 -- HISTORY
27 -- 23-Apr-2002 ssurapan Created.
28 --
29 -- Procedure name : Check_Availability
30 -- Type : Private
31 -- Function : This procedure calls ATP to check inventory item is available
32 -- for Routine jobs derived requested quantity and task start date
33 -- Pre-reqs :
34 -- Parameters :
35 --
36 -- Standard OUT Parameters :
37 -- x_return_status OUT VARCHAR2 Required
38 --
39 -- Check_Material_Aval Parameters :
40 -- p_calling_module IN NUMBER ,
41 -- p_inventory_item_id IN NUMBER , Required
42 -- p_quantity_required IN NUMBER, Required
43 -- p_organization_id IN NUMBER, Required
44 -- p_uom IN VARCHAR2, Required
45 -- p_requested_date IN DATE, Required
46 --
47
48 PROCEDURE Check_Availability (
49 p_calling_module IN NUMBER ,
50 p_inventory_item_id IN NUMBER ,
51 p_item_description IN VARCHAR2,
52 p_quantity_required IN NUMBER,
53 p_organization_id IN NUMBER,
54 p_uom IN VARCHAR2,
55 p_requested_date IN DATE, --Modified by rnahata for Issue 105
56 p_schedule_material_id IN NUMBER,
57 x_available_qty OUT NOCOPY NUMBER,
58 x_available_date OUT NOCOPY DATE,
59 x_error_code OUT NOCOPY NUMBER,
60 x_error_message OUT NOCOPY VARCHAR2,
61 x_return_status OUT NOCOPY VARCHAR2
62 )
63 IS
64 CURSOR Error_Message_Cur(c_error_code IN NUMBER) IS
65 SELECT meaning
66 FROM mfg_lookups
67 WHERE lookup_type = 'MTL_DEMAND_INTERFACE_ERRORS'
68 AND lookup_code = c_error_code;
69
70 CURSOR Instance_Id_Cur IS
71 SELECT instance_id
72 FROM MRP_AP_APPS_INSTANCES;
73
74 L_API_NAME CONSTANT VARCHAR2(30) := 'CHECK_AVAILABILITY';
75 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
76 L_API_VERSION CONSTANT NUMBER := 1.0;
77
78 l_atp_table Mrp_Atp_Pub.ATP_Rec_Typ;
79 l_instance_id INTEGER;
80 l_session_id NUMBER;
81 x_atp_table Mrp_Atp_Pub.ATP_Rec_Typ;
82 x_atp_supply_demand Mrp_Atp_Pub.ATP_Supply_Demand_Typ;
83 x_atp_period Mrp_Atp_Pub.ATP_Period_Typ;
84 x_atp_details Mrp_Atp_Pub.ATP_Details_Typ;
85 l_uom_code VARCHAR2(10);
86 l_calling_module NUMBER;
87 l_need_by_date DATE;
88 l_return_status VARCHAR2(1);
89 l_msg_data VARCHAR2(200);
90 l_msg_count NUMBER;
91 l_msg_index_out NUMBER;
92 l_identifier NUMBER := p_schedule_material_id;
93 x_req_date_quantity NUMBER;
94 l_error_message VARCHAR2(80);
95 l_error_code VARCHAR2(10);
96 i pls_integer;
97 BEGIN
98
99 IF (l_log_procedure >= l_log_current_level) THEN
100 fnd_log.string(l_log_procedure,
101 L_DEBUG_KEY ||'.begin',
102 'At the start of PL SQL procedure. Inventory item ID : ' || p_inventory_item_id);
103 END IF;
104
105 --------------------Initialize ----------------------------------
106 -- Standard Start of API savepoint
107 SAVEPOINT check_availability;
108 -- Initialize API return status to success
109 x_return_status := FND_API.G_RET_STS_SUCCESS;
110 -- Initialize message list if p_init_msg_list is set to TRUE.
111 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
112 l_api_version,
113 l_api_name,G_PKG_NAME)
114 THEN
115 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
116 END IF;
117 --
118
119 -- Get Session ID
120 SELECT MRP_ATP_SCHEDULE_TEMP_S.NEXTVAL
121 INTO l_session_id FROM DUAL;
122
123 IF (l_log_statement >= l_log_current_level) THEN
124 fnd_log.string(l_log_statement,
125 L_DEBUG_KEY,
126 'Session Id : '||l_session_id);
127 END IF;
128
129 --Get instance Id
130 --Check for Instance Exists
131 OPEN Instance_Id_Cur;
132 FETCH Instance_Id_Cur INTO l_instance_id;
133 IF Instance_Id_Cur%NOTFOUND THEN
134 FND_MESSAGE.Set_Name( 'AHL','AHL_LTP_ATP_INS_ENABLE' );
135 FND_MSG_PUB.add;
136 CLOSE Instance_Id_Cur;
137 RAISE FND_API.G_EXC_ERROR;
138 END IF;
139 CLOSE Instance_Id_Cur;
140 --
141 IF (l_log_statement >= l_log_current_level) THEN
142 fnd_log.string(l_log_statement,
143 L_DEBUG_KEY,
144 'Instance Id : '||l_instance_id ||
145 ', Identifier : '||l_identifier);
146 END IF;
147 -- Extend array size
148
149 MSC_ATP_GLOBAL.Extend_ATP(l_atp_table, x_return_status);
150
151 --Assign values to input record
152 l_atp_table.Inventory_Item_Id := Mrp_Atp_Pub.number_arr(p_inventory_item_id);
153 l_atp_table.Source_Organization_Id := Mrp_Atp_Pub.number_arr(p_organization_id);
154 -- SKPATHAK :: Bug 12957299 :: 09-SEP-2011
155 -- As per input from GOP team, passed the org id in Old_Source_Organization_Id also as we pass in schedule materials flow
156 l_atp_table.Old_Source_Organization_Id := Mrp_Atp_Pub.number_arr(p_organization_id);
157 -- TCHIMIRA :: Bug 12957299 :: 03-NOV-2011
158 l_atp_table.demand_source_type := Mrp_Atp_Pub.number_arr(100);
159 l_atp_table.Identifier := Mrp_Atp_Pub.number_arr(l_identifier);
160 l_atp_table.Instance_Id := MRP_ATP_PUB.number_arr(l_instance_id) ; --223);
161 l_atp_table.Calling_Module := Mrp_Atp_Pub.number_arr(p_calling_module);
162 l_atp_table.Customer_Id := Mrp_Atp_Pub.number_arr(NULL);
163 l_atp_table.Customer_Site_Id := Mrp_Atp_Pub.number_arr(NULL);
164 l_atp_table.Destination_Time_Zone := Mrp_Atp_Pub.char30_arr(NULL);
165 l_atp_table.Quantity_Ordered := Mrp_Atp_Pub.number_arr(p_quantity_required);
166 l_atp_table.Quantity_UOM := Mrp_Atp_Pub.char3_arr(p_uom);
167 -- Changed by jaramana on 12-OCT-2009 for bug 8910249
168 l_atp_table.Requested_Ship_Date := Mrp_Atp_Pub.date_arr(p_requested_date);
169 l_atp_table.Requested_Arrival_Date := Mrp_Atp_Pub.date_arr(null);
170 l_atp_table.Latest_Acceptable_Date := MRP_ATP_PUB.date_arr(null);
171 l_atp_table.Delivery_Lead_Time := Mrp_Atp_Pub.number_arr(NULL);
172 l_atp_table.Freight_Carrier := Mrp_Atp_Pub.char30_arr(NULL);
173 l_atp_table.Ship_Method := Mrp_Atp_Pub.char30_arr(NULL);
174 l_atp_table.Demand_Class := Mrp_Atp_Pub.char30_arr(NULL);
175 l_atp_table.Ship_Set_Name := Mrp_Atp_Pub.char30_arr(NULL);
176 l_atp_table.Arrival_Set_Name := Mrp_Atp_Pub.char30_arr(NULL);
177 l_atp_table.Override_Flag := Mrp_Atp_Pub.char1_arr(NULL);
178 l_atp_table.Action := Mrp_Atp_Pub.number_arr(100);
179 --SKPATHAK :: Bug 8392521 :: 02-APR-2009 :: Changed date_arr(sysdate) to date_arr(NULL)
180 l_atp_table.Ship_Date := Mrp_Atp_Pub.date_arr(NULL);
181 l_atp_table.Available_Quantity := Mrp_Atp_Pub.number_arr(NULL);
182 l_atp_table.Requested_Date_Quantity := Mrp_Atp_Pub.number_arr(NULL);
183 l_atp_table.Group_Ship_Date := Mrp_Atp_Pub.date_arr(NULL);
184 l_atp_table.Vendor_Id := Mrp_Atp_Pub.number_arr(NULL);
185 l_atp_table.Vendor_Site_Id := Mrp_Atp_Pub.number_arr(NULL);
186 l_atp_table.Insert_Flag := Mrp_Atp_Pub.number_arr(NULL);
187 l_atp_table.Error_Code := Mrp_Atp_Pub.number_arr(NULL);
188 l_atp_table.Message := Mrp_Atp_Pub.char2000_arr(NULL);
189
190 IF (l_log_statement >= l_log_current_level) THEN
191 fnd_log.string(l_log_statement,
192 L_DEBUG_KEY,
193 'Inventory Item Id : '||l_atp_table.Inventory_Item_Id(1));
194 fnd_log.string(l_log_statement,
195 L_DEBUG_KEY,
196 'Requested Date : '||l_atp_table.Requested_ship_Date(1));
197 fnd_log.string(l_log_statement,
198 L_DEBUG_KEY,
199 'Calling Module : '||l_atp_table.Calling_Module(1));
200 fnd_log.string(l_log_statement,
201 L_DEBUG_KEY,
202 'Organization Id : '||l_atp_table.Source_Organization_id(1));
203 fnd_log.string(l_log_statement,
204 L_DEBUG_KEY,
205 'Old Organization Id : '||l_atp_table.Old_Source_Organization_Id(1));
206 fnd_log.string(l_log_statement,
207 L_DEBUG_KEY,
208 'l_atp_table.demand_source_type : '||l_atp_table.demand_source_type(1));
209 fnd_log.string(l_log_statement,
210 L_DEBUG_KEY,
211 'Quantity Ordered : '||l_atp_table.Quantity_Ordered(1));
212 fnd_log.string(l_log_statement,
213 L_DEBUG_KEY,
214 'Action : '||l_atp_table.Action(1));
215 END IF;
216
217 IF (l_log_statement >= l_log_current_level) THEN
218 fnd_log.string(l_log_statement,
219 L_DEBUG_KEY,
220 'Before calling Mrp Atp Pub.Call_ATP');
221 END IF;
222
223 -- call atp module
224 Mrp_Atp_Pub.Call_ATP
225 (l_session_id,
226 l_atp_table,
227 x_atp_table,
228 x_atp_supply_demand,
229 x_atp_period,
230 x_atp_details,
231 l_return_status,
232 l_msg_data,
233 l_msg_count);
234
235 IF (l_log_statement >= l_log_current_level) THEN
236 fnd_log.string(l_log_statement,
237 L_DEBUG_KEY,
238 'After calling Mrp Atp Pub.Call_ATP. Return Status : '|| l_return_status);
239 END IF;
240
241 -- Check Error Message stack.
242 IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
243 l_msg_count := FND_MSG_PUB.count_msg;
244 IF l_msg_count > 0 THEN
245 RAISE FND_API.G_EXC_ERROR;
246 END IF;
247 END IF;
248
249 -- SKPATHAK :: Bug 12833742 :: 11-AUG-2011 :: Commented the below
250 --MSC_ATP_GLOBAL.Extend_ATP(x_atp_table, x_return_status);
251
252 IF (l_log_statement >= l_log_current_level) THEN
253 fnd_log.string(l_log_statement,
254 L_DEBUG_KEY,
255 'Ship Date => '||x_atp_table.ship_date(1));
256 fnd_log.string(l_log_statement,
257 L_DEBUG_KEY,
258 'Inventory Item Id : '||x_atp_table.Inventory_Item_Id(1));
259 fnd_log.string(l_log_statement,
260 L_DEBUG_KEY,
261 'Organization Id : '||x_atp_table.Source_Organization_Id(1));
262 fnd_log.string(l_log_statement,
263 L_DEBUG_KEY,
264 'Organization Code : '||x_atp_table.Source_Organization_code(1));
265 fnd_log.string(l_log_statement,
266 L_DEBUG_KEY,
267 'Quantity Ordered : '||x_atp_table.Quantity_Ordered(1));
268 fnd_log.string(l_log_statement,
269 L_DEBUG_KEY,
270 'Requested Ship Date : '||x_atp_table.Requested_Ship_Date(1));
271 fnd_log.string(l_log_statement,
272 L_DEBUG_KEY,
273 'Requested Arrival Date : '||x_atp_table.Requested_arrival_Date(1));
274 fnd_log.string(l_log_statement,
275 L_DEBUG_KEY,
276 'Arrival Date : '||x_atp_table.Arrival_Date(1));
277 fnd_log.string(l_log_statement,
278 L_DEBUG_KEY,
279 'Available Quantity : '||x_atp_table.Available_Quantity(1));
280 fnd_log.string(l_log_statement,
281 L_DEBUG_KEY,
282 'Requested Date Quantity : '||x_atp_table.Requested_Date_Quantity(1));
283 fnd_log.string(l_log_statement,
284 L_DEBUG_KEY,
285 'Error Code : '||x_atp_table.Error_Code(1));
286 fnd_log.string(l_log_statement,
287 L_DEBUG_KEY,
288 'Pub Message : '||x_atp_table.Message(1));
289 END IF;
290
291 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
292
293 MSC_ATP_GLOBAL.Extend_ATP(x_atp_table, l_return_status);
294
295 x_available_date := to_char(x_atp_table.Ship_Date(1));
296 --
297 MSC_ATP_GLOBAL.Extend_ATP(x_atp_table, l_return_status);
298
299 IF x_atp_table.Error_code(1) IN (0,52,53) THEN
300
301 MSC_ATP_GLOBAL.Extend_ATP(l_atp_table, x_return_status);
302
303 x_available_qty := trunc(x_atp_table.Available_Quantity(1));
304 x_error_code := x_atp_table.Error_code(1);
305
306 IF (l_log_statement >= l_log_current_level) THEN
307 fnd_log.string(l_log_statement,
308 L_DEBUG_KEY,
309 'After Calling Mrp Atp Pub. Error Code = ' || x_error_code ||
310 ', Available Quantity : '||x_available_qty);
311 END IF;
312
313 --Get from mfg lookups
314 OPEN Error_Message_Cur(x_error_code);
315 FETCH Error_Message_Cur INTO x_error_message;
316 CLOSE Error_Message_Cur;
317
318 IF (l_log_statement >= l_log_current_level) THEN
319 fnd_log.string(l_log_statement,
320 L_DEBUG_KEY,
321 'After Calling Mrp Atp Pub Error Message : '||x_error_message);
322 END IF;
323 ELSE
324 MSC_ATP_GLOBAL.Extend_ATP(x_atp_table, l_return_status);
325
326 IF (l_log_statement >= l_log_current_level) THEN
327 fnd_log.string(l_log_statement,
328 L_DEBUG_KEY,
329 'After calling MSC EXTEND');
330 END IF;
331
332 MSC_ATP_GLOBAL.Extend_ATP(l_atp_table, x_return_status);
333 x_error_code := x_atp_table.Error_code(1);
334
335 x_available_qty := trunc(x_atp_table.Available_Quantity(1));
336
337 IF (l_log_statement >= l_log_current_level) THEN
338 fnd_log.string(l_log_statement,
339 L_DEBUG_KEY,
340 'After ERROR CODE : '||x_error_code);
341 END IF;
342 --Get from mfg lookups
343 OPEN Error_Message_Cur(x_error_code);
344 FETCH Error_Message_Cur INTO x_error_message;
345 CLOSE Error_Message_Cur;
346
347 IF (l_log_statement >= l_log_current_level) THEN
348 fnd_log.string(l_log_statement,
349 L_DEBUG_KEY,
350 'Interface Error Message from mfg lookups : '||x_error_message);
351
352 END IF;
353 --
354 END IF;--Error code
355 END IF;
356
357 IF (l_log_statement >= l_log_current_level) THEN
358 fnd_log.string(l_log_statement,
359 L_DEBUG_KEY,
360 'Derived from Mrp Atp Pub Available Quantity : '||x_available_qty);
361 END IF;
362
363 -- Check Error Message stack.
364 IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
365 l_msg_count := FND_MSG_PUB.count_msg;
366 IF l_msg_count > 0 THEN
367 RAISE FND_API.G_EXC_ERROR;
368 END IF;
369 END IF;
370
371 IF (l_log_procedure >= l_log_current_level) THEN
372 fnd_log.string(l_log_procedure,
373 L_DEBUG_KEY ||'.end',
374 'At the end of PL SQL procedure. Return Status = ' || x_return_status);
375 END IF;
376
377 EXCEPTION
378 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
379 ROLLBACK TO check_availability;
380 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
381 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
382 p_count => l_msg_count,
383 p_data => l_msg_data);
384
385 WHEN FND_API.G_EXC_ERROR THEN
386 ROLLBACK TO check_availability;
387 X_return_status := FND_API.G_RET_STS_ERROR;
388 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
389 p_count => l_msg_count,
390 p_data => l_msg_data);
391
392 WHEN OTHERS THEN
393 ROLLBACK TO check_availability;
394 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
395 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
396 THEN
397 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_MATRL_AVAL_PVT',
398 p_procedure_name => 'CHECK_AVAILABILITY',
399 p_error_text => SUBSTR(SQLERRM,1,240));
400 END IF;
401 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
402 p_count => l_msg_count,
403 p_data => l_msg_data);
404
405 END Check_Availability;
406
407 -- Procedure name : Check_Material_Aval
408 -- Type : Private
409 -- Function : This procedure calls ATP to check inventory item is available
410 -- for Routine jobs derived requested quantity and task start date
411 -- Pre-reqs :
412 -- Parameters :
413 --
414 -- Standard IN Parameters :
415 -- p_api_version IN NUMBER Required
416 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
417 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
418 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
419 -- Based on this flag, the API will set the default attributes.
420 -- p_module_type In VARCHAR2 Default NULL
421 -- This will be null.
422 -- Standard OUT Parameters :
423 -- x_return_status OUT VARCHAR2 Required
424 -- x_msg_count OUT NUMBER Required
425 -- x_msg_data OUT VARCHAR2 Required
426 --
427 -- Check_Material_Aval Parameters :
428 -- p_x_material_avl_tbl IN OUT NOCOPY Material_Availability_Tbl,Required
429 -- List of item attributes associated to visit task
430 --
431 PROCEDURE Check_Material_Aval (
432 p_api_version IN NUMBER,
433 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
434 p_commit IN VARCHAR2 := FND_API.g_false,
435 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
436 p_module_type IN VARCHAR2 := 'JSP',
437 p_x_material_avl_tbl IN OUT NOCOPY ahl_ltp_matrl_aval_pub.Material_Availability_Tbl,
438 x_return_status OUT NOCOPY VARCHAR2,
439 x_msg_count OUT NOCOPY NUMBER,
440 x_msg_data OUT NOCOPY VARCHAR2
441 )
442 IS
443 -- Check for visit is scheduled
444 CURSOR Check_Sch_Visit_cur (c_visit_id IN NUMBER) IS
445 SELECT 1 FROM ahl_visits_b
446 WHERE visit_id = c_visit_id
447 AND (organization_id IS NULL
448 OR department_id IS NULL
449 OR start_date_time IS NULL);
450
451 CURSOR Schedule_Matrl_cur (C_SCH_MAT_ID IN NUMBER) IS
452 --Added by sowsubra - status needs be fetched
453 SELECT scheduled_material_id,uom,status,
454 organization_id,visit_task_id, requested_quantity --SKPATHAK :: Bug 12833742 :: 11-AUG-2011
455 FROM ahl_schedule_materials
456 WHERE scheduled_material_id = C_SCH_MAT_ID;
457
458 CURSOR Item_Des_cur(c_item_id IN NUMBER, c_org_id IN NUMBER) IS
459 SELECT CONCATENATED_SEGMENTS
460 FROM mtl_system_items_kfv
461 WHERE inventory_item_id = c_item_id
462 AND organization_id = c_org_id;
463
464 l_api_name CONSTANT VARCHAR2(30) := 'CHECK_MATERIAL_AVAL';
465 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
466 l_api_version CONSTANT NUMBER := 1.0;
467 l_return_status VARCHAR2(1);
468 l_msg_data VARCHAR2(2000);
469 l_msg_count NUMBER;
470 l_dummy NUMBER;
471 l_available_quantity NUMBER;
472 l_available_date DATE;
473 l_Schedule_Matrl_Rec Schedule_Matrl_cur%ROWTYPE;
474 l_error_code NUMBER;
475 l_error_message VARCHAR2(2000);
476
477 BEGIN
478 IF (l_log_procedure >= l_log_current_level) THEN
479 fnd_log.string(l_log_procedure,
480 L_DEBUG_KEY ||'.begin',
481 'At the start of PL SQL procedure.');
482 END IF;
483 -- dbms_output.put_line( 'start private API:');
484
485 --------------------Initialize ----------------------------------
486 -- Standard Start of API savepoint
487 SAVEPOINT check_material_aval;
488 -- Standard call to check for call compatibility.
489 IF FND_API.to_boolean(p_init_msg_list)
490 THEN
491 FND_MSG_PUB.initialize;
492 END IF;
493 -- Initialize API return status to success
494 x_return_status := FND_API.G_RET_STS_SUCCESS;
495 -- Initialize message list if p_init_msg_list is set to TRUE.
496 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
497 p_api_version,
498 l_api_name,G_PKG_NAME)
499 THEN
500 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
501 END IF;
502
503 IF (l_log_statement >= l_log_current_level)THEN
504 fnd_log.string(l_log_statement,
505 L_DEBUG_KEY,
506 'Request for Check Material Availability for Viist item ID : ' ||
507 p_x_material_avl_tbl(1).visit_id);
508 END IF;
509
510 --Validation for schedule visit
511 OPEN Check_Sch_Visit_cur(p_x_material_avl_tbl(1).visit_id);
512 FETCH Check_Sch_Visit_cur INTO l_dummy;
513 IF Check_Sch_Visit_cur%FOUND THEN
514 Fnd_Message.SET_NAME('AHL','AHL_VISIT_UNSCHEDULED');
515 Fnd_Msg_Pub.ADD;
516 CLOSE Check_Sch_Visit_cur;
517 RAISE Fnd_Api.G_EXC_ERROR;
518 END IF;
519 CLOSE Check_Sch_Visit_cur;
520 --
521 IF (l_log_statement >= l_log_current_level)THEN
522 fnd_log.string(l_log_statement,
523 L_DEBUG_KEY,
524 'Request for Check Material Availability for Material Records : ' ||
525 p_x_material_avl_tbl.COUNT);
526 END IF;
527 --
528 IF p_x_material_avl_tbl.COUNT > 0 THEN
529 FOR i IN p_x_material_avl_tbl.FIRST..p_x_material_avl_tbl.LAST
530 LOOP
531 IF (l_log_statement >= l_log_current_level) THEN
532 fnd_log.string(l_log_statement,
533 L_DEBUG_KEY,
534 'Check Material Availability for Inventory Item Id : ' ||
535 p_x_material_avl_tbl(i).inventory_item_id ||
536 ', Schedule Material Id : ' ||
537 p_x_material_avl_tbl(i).schedule_material_id);
538 END IF;
539 --Check for schedule mat rec
540 OPEN Schedule_Matrl_cur(p_x_material_avl_tbl(i).schedule_material_id);
541 FETCH Schedule_Matrl_cur INTO l_Schedule_Matrl_Rec;
542 IF Schedule_Matrl_cur%NOTFOUND THEN
543 Fnd_Message.SET_NAME('AHL','AHL_LTP_ORG_ID_NOT_EXISTS');
544 Fnd_Msg_Pub.ADD;
545 CLOSE Schedule_Matrl_cur;
546 RAISE Fnd_Api.G_EXC_ERROR;
547 END IF;
548 CLOSE Schedule_Matrl_cur;
549
550 --Added by sowsubra - starts
551 IF l_Schedule_Matrl_Rec.status = 'IN-SERVICE' THEN
552 Fnd_Message.SET_NAME('AHL','AHL_MAT_STS_INSERVICE');
553 Fnd_Msg_Pub.ADD;
554 RAISE Fnd_Api.G_EXC_ERROR;
555 END IF;
556 --Added by sowsubra - ends
557
558 --Get Item description
559 OPEN Item_Des_Cur(p_x_material_avl_tbl(i).inventory_item_id,
560 l_Schedule_Matrl_Rec.organization_id);
561 FETCH Item_Des_Cur INTO p_x_material_avl_tbl(i).item;
562 CLOSE Item_Des_Cur;
563
564 IF (l_log_statement >= l_log_current_level) THEN
565 fnd_log.string(l_log_statement,
566 L_DEBUG_KEY,
567 'Before calling Check Availability');
568 END IF;
569
570 Check_Availability (
571 p_calling_module => 867, --fnd_global.prog_appl_id,
572 p_inventory_item_id => p_x_material_avl_tbl(i).inventory_item_id ,
573 p_item_description => p_x_material_avl_tbl(i).item,
574 p_quantity_required => l_Schedule_Matrl_Rec.requested_quantity,--SKPATHAK :: Bug 12833742 :: 11-AUG-2011 -pass fethed qty
575 p_organization_id => l_Schedule_Matrl_Rec.organization_id,
576 p_uom => l_Schedule_Matrl_Rec.uom,
577 p_requested_date => p_x_material_avl_tbl(i).req_arrival_date,
578 p_schedule_material_id => p_x_material_avl_tbl(i).schedule_material_id,
579 x_available_qty => l_available_quantity,
580 x_available_date => l_available_date,
581 x_error_code => l_error_code,
582 x_error_message => l_error_message,
583 x_return_status => l_return_status);
584 --
585 IF (l_log_statement >= l_log_current_level) THEN
586 fnd_log.string(l_log_statement,
587 L_DEBUG_KEY,
588 'After calling Check Availability, Return Status : '|| l_return_status);
589 END IF;
590
591 -- Check Error Message stack.
592 IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
593 l_msg_count := FND_MSG_PUB.count_msg;
594 IF l_msg_count > 0 THEN
595 RAISE FND_API.G_EXC_ERROR;
596 END IF;
597 END IF;
598 --Assign derived values
599 p_x_material_avl_tbl(i).quantity_available:= l_available_quantity;
600 --SKPATHAK :: Bug 8392521 :: 02-APR-2009
601 --Pass the ship_date returned by ATP (as l_available_date) unconditionally to the out param of PVT API
602 p_x_material_avl_tbl(i).scheduled_date:= l_available_date;
603
604 -- anraj : commented these lines of code
605 /*p_x_material_avl_tbl(i).inventory_item_id := p_x_material_avl_tbl(i).inventory_item_id;
606 p_x_material_avl_tbl(i).quantity := p_x_material_avl_tbl(i).quantity;
607 p_x_material_avl_tbl(i).visit_task_id := p_x_material_avl_tbl(i).visit_task_id;
608 p_x_material_avl_tbl(i).task_name := p_x_material_avl_tbl(i).task_name;
609 p_x_material_avl_tbl(i).req_arrival_date := p_x_material_avl_tbl(i).req_arrival_date;
610 p_x_material_avl_tbl(i).uom := p_x_material_avl_tbl(i).uom;
611 */
612 p_x_material_avl_tbl(i).error_code := l_error_code;
613 p_x_material_avl_tbl(i).error_message := l_error_message;
614 --
615 IF (l_log_statement >= l_log_current_level) THEN
616 fnd_log.string(l_log_statement,
617 L_DEBUG_KEY,
618 'Inventory Item Id : ' || p_x_material_avl_tbl(i).inventory_item_id);
619 fnd_log.string(l_log_statement,
620 L_DEBUG_KEY,
621 'Quantity Available : ' || p_x_material_avl_tbl(i).quantity_available);
622 fnd_log.string(l_log_statement,
623 L_DEBUG_KEY,
624 'Quantity Required : ' || p_x_material_avl_tbl(i).quantity);
625 fnd_log.string(l_log_statement,
626 L_DEBUG_KEY,
627 'Visit Task Id: ' || p_x_material_avl_tbl(i).visit_task_id);
628 fnd_log.string(l_log_statement,
629 L_DEBUG_KEY,
630 'Error Code: ' || l_error_code);
631 fnd_log.string(l_log_statement,
632 L_DEBUG_KEY,
633 'Error Message: ' || l_error_message);
634 fnd_log.string(l_log_statement,
635 L_DEBUG_KEY,
636 ' at last Scheduled Date: ' || p_x_material_avl_tbl(i).scheduled_date);
637 END IF;
638 END LOOP;
639 END IF;
640
641 -- Check Error Message stack.
642 IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
643 l_msg_count := FND_MSG_PUB.count_msg;
644 IF l_msg_count > 0 THEN
645 RAISE FND_API.G_EXC_ERROR;
646 END IF;
647 END IF;
648
649 IF (l_log_procedure >= l_log_current_level) THEN
650 fnd_log.string(l_log_procedure,
651 L_DEBUG_KEY ||'.end',
652 'At the end of PL SQL procedure. Return Status = ' || x_return_status);
653 END IF;
654
655 EXCEPTION
656 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
657 ROLLBACK TO check_material_aval;
658 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
659 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
660 p_count => l_msg_count,
661 p_data => l_msg_data);
662
663 WHEN FND_API.G_EXC_ERROR THEN
664 ROLLBACK TO check_material_aval;
665 X_return_status := FND_API.G_RET_STS_ERROR;
666 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
667 p_count => l_msg_count,
668 p_data => l_msg_data);
669
670 WHEN OTHERS THEN
671 ROLLBACK TO check_material_aval;
672 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
673 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
674 THEN
675 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_MATRL_AVAL_PVT',
676 p_procedure_name => 'CHECK_MATERIAL_AVAL',
677 p_error_text => SUBSTR(SQLERRM,1,240));
678 END IF;
679 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
680 p_count => l_msg_count,
681 p_data => l_msg_data);
682
683 END Check_Material_Aval;
684
685 --
686 -- Procedure name : Get_Visit_Task_Materials
687 -- Type : Private
688 -- Function : This procedure derives material information associated to scheduled
689 -- visit, which are defined at Route Operation level
690 -- Pre-reqs :
691 -- Parameters :
692 --
693 -- Standard IN Parameters :
694 -- p_api_version IN NUMBER Required
695 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
696 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
697 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
698 -- Based on this flag, the API will set the default attributes.
699 -- p_module_type In VARCHAR2 Default NULL
700 -- This will be null.
701 -- Standard OUT Parameters :
702 -- x_return_status OUT VARCHAR2 Required
703 -- x_msg_count OUT NUMBER Required
704 -- x_msg_data OUT VARCHAR2 Required
705 --
706 -- Get_Visit_Task_Materials :
707 -- p_visit_id IN NUMBER,Required
708 -- x_task_req_matrl_tbl OUT NOCOPY Task_Req_Matrl_Tbl,
709 --
710 PROCEDURE Get_Visit_Task_Materials (
711 p_api_version IN NUMBER,
712 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
713 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
714 p_visit_id IN NUMBER,
715 x_task_req_matrl_tbl OUT NOCOPY ahl_ltp_matrl_aval_pub.task_req_matrl_tbl,
716 x_return_status OUT NOCOPY VARCHAR2,
717 x_msg_count OUT NOCOPY NUMBER,
718 x_msg_data OUT NOCOPY VARCHAR2)
719 IS
720 --
721 -- changed the select statement to add one more column
722 CURSOR Visit_Task_Matrl_Cur(C_VISIT_ID IN NUMBER) IS
723 SELECT schedule_material_id,
724 object_version_number,
725 visit_id,
726 visit_task_id,
727 visit_task_name,
728 inventory_item_id,
729 item_number, --Modified by rnahata for ER 6391157, ahl_visit_task_matrl_v definition changed
730 requested_quantity,
731 requested_date,
732 scheduled_date,
733 scheduled_quantity,
734 uom,
735 sales_order_line_id,
736 task_status_code,
737 meaning
738 FROM ahl_visit_task_matrl_v, FND_LOOKUP_VALUES_VL
739 WHERE visit_id = C_VISIT_ID
740 --SKPATHAK :: Bug 8429732 :: 17-APR-2009
741 --Commented out the condition (requested_quantity <> 0)
742 /* AND (requested_quantity <> 0) */
743 AND NVL(mat_status,'X') <> 'IN-SERVICE' --Added by sowsubra
744 AND LOOKUP_TYPE(+) = 'AHL_VWP_TASK_STATUS'
745 AND LOOKUP_code = task_status_code;
746 c_Visit_Task_Matrl_Rec Visit_Task_Matrl_Cur%ROWTYPE;
747
748 --Standard local variables
749 l_api_name CONSTANT VARCHAR2(30) := 'Get_Visit_Task_Materials';
750 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
751 l_api_version CONSTANT NUMBER := 1.0;
752 l_return_status VARCHAR2(1);
753 l_msg_data VARCHAR2(2000);
754 l_msg_count NUMBER;
755 --
756 i NUMBER;
757 BEGIN
758
759 IF (l_log_procedure >= l_log_current_level) THEN
760 fnd_log.string(l_log_procedure,
761 L_DEBUG_KEY ||'.begin',
762 'At the start of PL SQL procedure. Visit Id = ' || p_visit_id);
763 END IF;
764 -- Standard Start of API savepoint
765 SAVEPOINT Get_Visit_Task_Materials;
766 -- Initialize message list if p_init_msg_list is set to TRUE.
767 IF FND_API.to_boolean(p_init_msg_list) THEN
768 FND_MSG_PUB.initialize;
769 END IF;
770 -- Initialize API return status to success
771 x_return_status := FND_API.G_RET_STS_SUCCESS;
772 -- Standard call to check for call compatibility.
773 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
774 l_api_version,
775 l_api_name,G_PKG_NAME)
776 THEN
777 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
778 END IF;
779
780 --
781 IF p_visit_id IS NOT NULL THEN
782 --
783 OPEN Visit_Task_Matrl_Cur(p_visit_id);
784 i := 0;
785 LOOP
786 FETCH Visit_Task_Matrl_Cur INTO c_Visit_Task_Matrl_Rec;
787 EXIT WHEN Visit_Task_Matrl_Cur%NOTFOUND;
788 --
789 x_task_req_matrl_tbl(i).schedule_material_id := c_Visit_Task_Matrl_Rec.schedule_material_id;
790 x_task_req_matrl_tbl(i).object_version_number := c_Visit_Task_Matrl_Rec.object_version_number;
791 x_task_req_matrl_tbl(i).visit_task_id := c_Visit_Task_Matrl_Rec.visit_task_id;
792 x_task_req_matrl_tbl(i).task_name := c_Visit_Task_Matrl_Rec.visit_task_name;
793 -- anraj : added columns TASK_STATUS_CODE and TASK_STATUS_MEANING , for Material Availabilty UI
794 x_task_req_matrl_tbl(i).task_status_code := c_Visit_Task_Matrl_Rec.task_status_code;
795 x_task_req_matrl_tbl(i).task_status_meaning := c_Visit_Task_Matrl_Rec.meaning;
796 x_task_req_matrl_tbl(i).inventory_item_id := c_Visit_Task_Matrl_Rec.inventory_item_id;
797 x_task_req_matrl_tbl(i).item := c_Visit_Task_Matrl_Rec.item_number;
798 x_task_req_matrl_tbl(i).req_arrival_date := c_Visit_Task_Matrl_Rec.requested_date;
799 x_task_req_matrl_tbl(i).uom_code := c_Visit_Task_Matrl_Rec.uom;
800 x_task_req_matrl_tbl(i).planned_order := c_Visit_Task_Matrl_Rec.sales_order_line_id;
801 x_task_req_matrl_tbl(i).quantity := c_Visit_Task_Matrl_Rec.requested_quantity;
802 x_task_req_matrl_tbl(i).scheduled_date := c_Visit_Task_Matrl_Rec.scheduled_date;
803 i := i + 1;
804 END LOOP;
805 CLOSE Visit_Task_Matrl_Cur;
806 END IF;
807
808 -- Check Error Message stack.
809 IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
810 l_msg_count := FND_MSG_PUB.count_msg;
811 IF l_msg_count > 0 THEN
812 RAISE FND_API.G_EXC_ERROR;
813 END IF;
814 END IF;
815
816 IF (l_log_procedure >= l_log_current_level) THEN
817 fnd_log.string(l_log_procedure,
818 L_DEBUG_KEY ||'.end',
819 'At the end of PL SQL procedure. Return Status = ' || x_return_status);
820 END IF;
821
822 EXCEPTION
823 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
824 ROLLBACK TO Get_Visit_Task_Materials;
825 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
826 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
827 p_count => l_msg_count,
828 p_data => l_msg_data);
829 WHEN FND_API.G_EXC_ERROR THEN
830 ROLLBACK TO Get_Visit_Task_Materials;
831 X_return_status := FND_API.G_RET_STS_ERROR;
832 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
833 p_count => l_msg_count,
834 p_data => l_msg_data);
835 WHEN OTHERS THEN
836 ROLLBACK TO Get_Visit_Task_Materials;
837 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
838 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
839 THEN
840 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_MATRL_AVAL_PVT',
841 p_procedure_name => 'GET_VISIT_TASK_MATERIALS',
842 p_error_text => SUBSTR(SQLERRM,1,240));
843 END IF;
844 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
845 p_count => l_msg_count,
846 p_data => l_msg_data);
847 END Get_Visit_Task_Materials;
848 --
849 PROCEDURE Extend_ATP
850 (p_atp_table IN OUT NOCOPY MRP_ATP_PUB.ATP_Rec_Typ,
851 x_return_status OUT NOCOPY VARCHAR2)
852 IS
853
854 L_API_NAME CONSTANT VARCHAR2(30) := 'Extend_ATP';
855 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
856
857 BEGIN
858 IF (l_log_procedure >= l_log_current_level) THEN
859 fnd_log.string(l_log_procedure,
860 L_DEBUG_KEY ||'.begin',
861 'At the start of PL SQL procedure.');
862 END IF;
863
864 x_return_status := FND_API.G_RET_STS_SUCCESS;
865 --
866 p_atp_table.Row_Id.Extend;
867 P_ATP_TABLE.INSTANCE_ID.EXTEND;
868 P_ATP_TABLE.INVENTORY_ITEM_ID.EXTEND;
869 P_ATP_TABLE.INVENTORY_ITEM_NAME.EXTEND;
870 P_ATP_TABLE.SOURCE_ORGANIZATION_ID.EXTEND;
871 p_atp_table.Source_Organization_Code.Extend;
872 p_atp_table.Organization_Id.Extend;
873 P_ATP_TABLE.IDENTIFIER.EXTEND;
874 p_atp_table.Scenario_Id.Extend;
875 P_ATP_TABLE.DEMAND_SOURCE_TYPE.EXTEND;
876 P_ATP_TABLE.CALLING_MODULE.EXTEND;
877 p_atp_table.Customer_Id.Extend;
878 p_atp_table.Customer_Site_Id.Extend;
879 p_atp_table.Destination_Time_Zone.Extend;
880 P_ATP_TABLE.QUANTITY_ORDERED.EXTEND;
881 P_ATP_TABLE.QUANTITY_UOM.EXTEND;
882 P_ATP_TABLE.REQUESTED_SHIP_DATE.EXTEND;
883 p_atp_table.Requested_Arrival_Date.Extend;
884 p_atp_table.Earliest_Acceptable_Date.Extend;
885 p_atp_table.Latest_Acceptable_Date.Extend;
886 p_atp_table.Delivery_Lead_Time.Extend;
887 p_atp_table.Freight_Carrier.Extend;
888 p_atp_table.Ship_Method.Extend;
889 p_atp_table.Demand_Class.Extend;
890 p_atp_table.Ship_Set_Name.Extend;
891 p_atp_table.Arrival_Set_Name.Extend;
892 p_atp_table.Override_Flag.Extend;
893 P_ATP_TABLE.ACTION.EXTEND;
894 p_atp_table.Ship_Date.Extend;
895 p_atp_table.Available_Quantity.Extend;
896 P_ATP_TABLE.ORDER_NUMBER.EXTEND;
897 p_atp_table.Requested_Date_Quantity.Extend;
898 p_atp_table.Group_Ship_Date.Extend;
899 p_atp_table.Group_Arrival_Date.Extend;
900 p_atp_table.Vendor_Id.Extend;
901 p_atp_table.Vendor_Name.Extend;
902 p_atp_table.Vendor_Site_Id.Extend;
903 p_atp_table.Vendor_Site_Name.Extend;
904 p_atp_table.Insert_Flag.Extend;
905 p_atp_table.OE_Flag.Extend;
906 p_atp_table.Error_Code.Extend;
907 p_atp_table.Message.Extend;
908 p_atp_table.req_item_req_date_qty.extend;
909 p_atp_table.req_item_available_date.extend;
910 p_atp_table.req_item_available_date_qty.extend;
911
912 IF (l_log_procedure >= l_log_current_level) THEN
913 fnd_log.string(l_log_procedure,
914 L_DEBUG_KEY ||'.end',
915 'At the end of PL SQL procedure. Return Status = ' || x_return_status);
916 END IF;
917
918 END Extend_ATP;
919 --
920 -- Start of Comments --
921 -- Procedure name : Call_ATP
922 -- Type : Public
923 -- Function : This procedure calls ATP to schedule planned materials
924 -- for Routine jobs derived requested quantity and task start date
925 -- Pre-reqs :
926 -- Parameters :
927 --
928 -- Standard IN Parameters :
929 -- p_api_version IN NUMBER Required
930 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
931 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
932 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
933 -- Based on this flag, the API will set the default attributes.
934 -- p_module_type In VARCHAR2 Default NULL
935 -- This will be null.
936 -- Standard OUT Parameters :
937 -- x_return_status OUT VARCHAR2 Required
938 -- x_msg_count OUT NUMBER Required
939 -- x_msg_data OUT VARCHAR2 Required
940 --
941 -- Schedule_Planned_Matrls Parameters :
942 -- p_x_planned_matrls_tbl IN OUT NOCOPY Planned_Matrls_Tbl,Required
943 -- List of item attributes associated to visit task
944 --
945 PROCEDURE Call_ATP (
946 p_api_version IN NUMBER,
947 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
948 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
949 p_x_planned_matrl_tbl IN OUT NOCOPY AHL_LTP_MATRL_AVAL_PUB.Planned_Matrl_Tbl,
950 x_return_status OUT NOCOPY VARCHAR2,
951 x_msg_count OUT NOCOPY NUMBER,
952 x_msg_data OUT NOCOPY VARCHAR2)
953 IS
954
955 CURSOR Error_Message_Cur(c_error_code IN NUMBER)
956 IS
957 SELECT meaning
958 FROM mfg_lookups
959 WHERE lookup_type = 'MTL_DEMAND_INTERFACE_ERRORS'
960 AND lookup_code = C_Error_Code;
961
962 CURSOR Planned_Order_Cur(c_sch_mat_id IN NUMBER) IS
963 -- yazhou 12-May-2006 starts
964 -- Bug fix#5223772
965 /*
966 -- Changed for fixing perf bug:4919540
967 select DECODE( SIGN( trunc(scheduled_date) - trunc(requested_date)),1,scheduled_date,null) scheduled_date,
968 scheduled_quantity
969 from ahl_schedule_materials asmt,
970 AHL_VISIT_TASKS_B tsk
971 where TSK.VISIT_ID = ASMT.VISIT_ID
972 AND TSK.VISIT_TASK_ID = ASMT.VISIT_TASK_ID
973 AND NVL(ASMT.STATUS,' ') <> 'DELETED'
974 AND NVL(TSK.STATUS_CODE,'X') <> 'DELETED'
975 AND scheduled_material_id = c_sch_mat_id;
976 */
977 SELECT scheduled_date ,
978 status, --Added by sowsubra
979 scheduled_quantity
980 FROM ahl_schedule_materials asmt,
981 AHL_VISIT_TASKS_B tsk
982 WHERE TSK.VISIT_ID = ASMT.VISIT_ID
983 AND TSK.VISIT_TASK_ID = ASMT.VISIT_TASK_ID
984 AND NVL(ASMT.STATUS,' ') <> 'DELETED'
985 AND NVL(TSK.STATUS_CODE,'X') <> 'DELETED'
986 AND scheduled_material_id = c_sch_mat_id
987 AND scheduled_date is not null;
988 -- Commented out by surrkuma on 13-JUL-2010 for Bug 9901811 in order to
989 -- include past dated requirements also to consider for rescheduling.
990 -- AND scheduled_date >= requested_date;
991 --yazhou 12-May-2006 ends
992
993 CURSOR Order_Number_Cur(c_visit_task_id IN NUMBER) IS
994 SELECT visit_number||visit_task_number Order_Number
995 FROM ahl_visit_tasks_v
996 WHERE visit_task_id = c_visit_task_id;
997
998 CURSOR Instance_Id_Cur IS
999 SELECT instance_id
1000 FROM MRP_AP_APPS_INSTANCES;
1001
1002 --Standard local variables
1003 l_api_name CONSTANT VARCHAR2(30) := 'CALL_ATP';
1004 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
1005 l_api_version CONSTANT NUMBER := 1.0;
1006 l_return_status VARCHAR2(1);
1007 l_msg_data VARCHAR2(2000);
1008 l_msg_count NUMBER;
1009 --Varibales to call mrp atp pub
1010 l_session_id NUMBER;
1011 l_instance_id NUMBER;
1012 l_atp_table Mrp_Atp_Pub.ATP_Rec_Typ;
1013 x_atp_table Mrp_Atp_Pub.ATP_Rec_Typ;
1014 x_atp_supply_demand Mrp_Atp_Pub.ATP_Supply_Demand_Typ;
1015 x_atp_period Mrp_Atp_Pub.ATP_Period_Typ;
1016 x_atp_details Mrp_Atp_Pub.ATP_Details_Typ;
1017 l_temp_atp_table AHL_LTP_MATRL_AVAL_PUB.Planned_Matrl_Tbl;
1018 l_error_msg VARCHAR2(2000);
1019 l_error_message VARCHAR2(80);
1020 l_planned_matrl_tbl AHL_LTP_MATRL_AVAL_PUB.Planned_Matrl_Tbl := p_x_planned_matrl_tbl;
1021 l_scheduled_date DATE;
1022 l_scheduled_quantity NUMBER;
1023 l_Planned_Order_Rec Planned_Order_Cur%ROWTYPE;
1024 --Required to capture available quantity and scheduled quanity
1025 l_temp_planned_table AHL_LTP_MATRL_AVAL_PUB.Planned_Matrl_Tbl;
1026 l_order_number NUMBER;
1027
1028 BEGIN
1029
1030 IF (l_log_procedure >= l_log_current_level) THEN
1031 fnd_log.string(l_log_procedure,
1032 L_DEBUG_KEY ||'.begin',
1033 'At the start of PL SQL procedure. Number of Records : ' || l_planned_matrl_tbl.COUNT);
1034 END IF;
1035 -- Standard Start of API savepoint
1036 SAVEPOINT Call_ATP;
1037 -- Initialize message list if p_init_msg_list is set to TRUE.
1038 IF FND_API.to_boolean(p_init_msg_list) THEN
1039 FND_MSG_PUB.initialize;
1040 END IF;
1041 -- Initialize API return status to success
1042 x_return_status := FND_API.G_RET_STS_SUCCESS;
1043 -- Standard call to check for call compatibility.
1044 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
1045 l_api_version,
1046 l_api_name,G_PKG_NAME)
1047 THEN
1048 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1049 END IF;
1050
1051 --Get session id
1052 SELECT MRP_ATP_SCHEDULE_TEMP_S.NEXTVAL
1053 INTO l_session_id FROM DUAL;
1054
1055 IF (l_log_statement >= l_log_current_level) THEN
1056 fnd_log.string(l_log_statement,
1057 L_DEBUG_KEY,
1058 'Session Id : '||l_session_id);
1059 END IF;
1060
1061 --Get instance Id
1062 --Check for Instance Id
1063 OPEN Instance_Id_Cur;
1064 FETCH Instance_Id_Cur INTO l_instance_id;
1065 IF Instance_Id_Cur%NOTFOUND THEN
1066 FND_MESSAGE.Set_Name( 'AHL','AHL_LTP_ATP_INS_ENABLE' );
1067 FND_MSG_PUB.add;
1068 CLOSE Instance_Id_Cur;
1069 RAISE FND_API.G_EXC_ERROR;
1070 END IF;
1071 --
1072 CLOSE Instance_Id_Cur;
1073
1074 IF (l_log_statement >= l_log_current_level) THEN
1075 fnd_log.string(l_log_statement,
1076 L_DEBUG_KEY,
1077 'Instance Id : '||l_instance_id);
1078 END IF;
1079
1080 -- Loop through all the records
1081 FOR i IN l_planned_matrl_tbl.FIRST .. l_planned_matrl_tbl.LAST
1082 LOOP
1083 --
1084 IF l_planned_matrl_tbl.EXISTS(i) THEN
1085 --Call extend Atp
1086 MSC_ATP_GLOBAL.Extend_ATP(l_atp_table, x_return_status);
1087 --
1088 l_atp_table.inventory_item_id(i) := l_planned_matrl_tbl(i).inventory_item_id;
1089 l_atp_table.inventory_item_name(i) := l_planned_matrl_tbl(i).item_description;
1090 l_atp_table.instance_id(i) := l_instance_id;
1091 l_atp_table.source_organization_id(i) := l_planned_matrl_tbl(i).organization_id;
1092 l_atp_table.identifier(i) := l_planned_matrl_tbl(i).schedule_material_id;
1093 l_atp_table.demand_source_type(i) := 100;
1094 l_atp_table.quantity_ordered(i) := l_planned_matrl_tbl(i).required_quantity;
1095 l_atp_table.quantity_UOM(i) := l_planned_matrl_tbl(i).primary_uom_code;
1096 l_atp_table.requested_ship_date(i) := l_planned_matrl_tbl(i).requested_date;
1097
1098 -- Begin Changes by surrkuma on 14-JUL-2010 for bug 9901811 after discussion with Anurodh Saxena
1099 -- Check whether SCHEDULING or RESCHEDULING
1100 /**
1101 OPEN Planned_Order_Cur(l_planned_matrl_tbl(i).schedule_material_id);
1102 FETCH Planned_Order_Cur into l_Planned_Order_Rec;
1103 IF Planned_Order_Cur%NOTFOUND THEN
1104 l_atp_table.action(i) := 110;--Scheduling
1105 ELSE
1106 l_atp_table.action(i) := 120;--Rescheduling
1107 l_atp_table.Old_Source_Organization_Id(i) := l_planned_matrl_tbl(i).organization_id;--Rescheduling
1108 END IF;
1109 CLOSE Planned_Order_Cur;
1110 **/
1111 -- Always Call Reschedule in order to avoid duplicate after an ASCP call
1112 l_atp_table.action(i) := 120;--Rescheduling
1113 l_atp_table.Old_Source_Organization_Id(i) := l_planned_matrl_tbl(i).organization_id;--Rescheduling
1114 -- End Changes by surrkuma on 14-JUL-2010 for bug 9901811
1115
1116 --Added by sowsubra - start
1117 IF l_Planned_Order_Rec.status = 'IN-SERVICE' THEN
1118 Fnd_Message.SET_NAME('AHL','AHL_MAT_STS_INSERVICE');
1119 Fnd_Msg_Pub.ADD;
1120 RAISE Fnd_Api.G_EXC_ERROR;
1121 END IF;
1122 --Added by sowsubra - end
1123
1124 --Get Concatenated visit number, task number
1125 OPEN Order_Number_Cur(l_planned_matrl_tbl(i).visit_task_id);
1126 FETCH Order_Number_Cur INTO l_order_number;
1127 CLOSE Order_Number_Cur;
1128 --Assign to atp record
1129 l_atp_table.order_number(i) := l_order_number;
1130 l_atp_table.calling_module(i) := 867; --fnd_global.prog_appl_id;
1131 --
1132 IF (l_log_statement >= l_log_current_level)THEN
1133 fnd_log.string(l_log_statement,
1134 L_DEBUG_KEY,
1135 'Inventory Item Id : '||l_atp_table.inventory_item_id(i) ||'-'||i);
1136 fnd_log.string(l_log_statement,
1137 L_DEBUG_KEY,
1138 'Inventory Item Name : '||l_atp_table.inventory_item_name(i));
1139 fnd_log.string(l_log_statement,
1140 L_DEBUG_KEY,
1141 'Instance Id : '||l_atp_table.instance_id(i));
1142 fnd_log.string(l_log_statement,
1143 L_DEBUG_KEY,
1144 'Organization Id : '||l_atp_table.source_organization_id(i));
1145 fnd_log.string(l_log_statement,
1146 L_DEBUG_KEY,
1147 'Old_Source_Organization_Id : '||l_atp_table.Old_Source_Organization_Id(i));
1148 fnd_log.string(l_log_statement,
1149 L_DEBUG_KEY,
1150 'Identifier : '||l_atp_table.identifier(i));
1151 fnd_log.string(l_log_statement,
1152 L_DEBUG_KEY,
1153 'Demand Source Type : '||l_atp_table.demand_source_type(i));
1154 fnd_log.string(l_log_statement,
1155 L_DEBUG_KEY,
1156 'Quantity Ordered : '||l_atp_table.quantity_ordered(i));
1157 fnd_log.string(l_log_statement,
1158 L_DEBUG_KEY,
1159 'Quantity UOM : '||l_atp_table.quantity_uom(i));
1160 fnd_log.string(l_log_statement,
1161 L_DEBUG_KEY,
1162 'Requested Ship Date : '||l_atp_table.requested_ship_date(i));
1163 fnd_log.string(l_log_statement,
1164 L_DEBUG_KEY,
1165 'Action : '||l_atp_table.action(i));
1166 fnd_log.string(l_log_statement,
1167 L_DEBUG_KEY,
1168 'Order Number : '||l_atp_table.order_number(i));
1169 fnd_log.string(l_log_statement,
1170 L_DEBUG_KEY,
1171 'Requested Date Quantity : '||l_atp_table.requested_date_quantity(i));
1172 fnd_log.string(l_log_statement,
1173 L_DEBUG_KEY,
1174 'Calling Module : '||l_atp_table.Calling_module(i));
1175 END IF;
1176 END IF;
1177 END LOOP;
1178
1179 IF (l_log_statement >= l_log_current_level) THEN
1180 fnd_log.string(l_log_statement,
1181 L_DEBUG_KEY,
1182 'Before calling Mrp Atp Pub.Call_ATP. Calling Module count: '||
1183 l_atp_table.Calling_module.count);
1184 END IF;
1185
1186 -- Call ATP to Schedule
1187 MRP_ATP_PUB.CALL_ATP(l_session_id,
1188 l_atp_table,
1189 x_atp_table,
1190 x_atp_supply_demand,
1191 x_atp_period,
1192 x_atp_details,
1193 x_return_status,
1194 x_msg_data,
1195 x_msg_count);
1196
1197 IF (l_log_statement >= l_log_current_level) THEN
1198 fnd_log.string(l_log_statement,
1199 L_DEBUG_KEY,
1200 'After calling Mrp Atp Pub.Call_ATP. Return Status : '|| x_return_status);
1201 END IF;
1202
1203 -- Check Error Message stack.
1204 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1205 l_msg_count := FND_MSG_PUB.count_msg;
1206 IF l_msg_count > 0 THEN
1207 RAISE FND_API.G_EXC_ERROR;
1208 END IF;
1209 END IF;
1210
1211 --Check for error code
1212 IF x_atp_table.Error_Code.COUNT > 0 THEN
1213 FOR i IN x_atp_table.Error_Code.FIRST .. x_atp_table.Error_Code.LAST
1214 LOOP
1215 MSC_ATP_GLOBAL.Extend_ATP(x_atp_table, x_return_status);
1216 IF (x_atp_table.Error_Code.EXISTS(i) AND x_atp_table.error_code(i) <> 0) THEN
1217 IF (l_log_statement >= l_log_current_level)THEN
1218 fnd_log.string(l_log_statement,
1219 L_DEBUG_KEY,
1220 'x_atp_table.error_code(i) : '||x_atp_table.error_code(i));
1221 END IF;
1222 MSC_ATP_GLOBAL.Extend_ATP(x_atp_table, x_return_status);
1223
1224 --SKPATHAK :: Bug 8392521 :: 02-APR-2009
1225 --Update ahl_schedule_materials table with the ship_date even if the error is not zero
1226 IF (x_atp_table.ship_date(i) IS NOT NULL)THEN
1227 UPDATE ahl_schedule_materials
1228 SET scheduled_date = x_atp_table.ship_date(i),
1229 object_version_number = object_version_number + 1
1230 WHERE scheduled_material_id = x_atp_table.identifier(i);
1231 END IF;
1232
1233 l_temp_atp_table(i).schedule_material_id := x_atp_table.identifier(i);
1234 l_temp_atp_table(i).item_description := x_atp_table.inventory_item_name(i);
1235 l_temp_atp_table(i).error_code := x_atp_table.error_code(i);
1236 l_temp_atp_table(i).quantity_available := trunc(x_atp_table.available_quantity(i));
1237 l_temp_atp_table(i).item_description := x_atp_table.inventory_item_name(i);
1238 --Get error message
1239 OPEN Error_Message_Cur(l_temp_atp_table(i).error_code);
1240 FETCH Error_Message_Cur INTO l_temp_atp_table(i).error_message;
1241 CLOSE Error_Message_Cur;
1242 ELSE
1243 --Error code is zero update the record
1244 MSC_ATP_GLOBAL.Extend_ATP(x_atp_table, x_return_status);
1245
1246 IF (l_log_statement >= l_log_current_level)THEN
1247 fnd_log.string(l_log_statement,
1248 L_DEBUG_KEY,
1249 'Ship Date => '||x_atp_table.ship_date(i));
1250 fnd_log.string(l_log_statement,
1251 L_DEBUG_KEY,
1252 'x_atp_table.identifier(i) : '|| x_atp_table.identifier(i));
1253 fnd_log.string(l_log_statement,
1254 L_DEBUG_KEY,
1255 'x_atp_table.available_quantity(i) : '|| x_atp_table.available_quantity(i));
1256 fnd_log.string(l_log_statement,
1257 L_DEBUG_KEY,
1258 'x_atp_table.requested_date_quantity(i) : '|| trunc(x_atp_table.requested_date_quantity(i)));
1259 fnd_log.string(l_log_statement,
1260 L_DEBUG_KEY,
1261 'x_atp_table.ship_date(i) : '|| x_atp_table.ship_date(i));
1262 END IF;
1263 --Assign out parameter
1264 l_temp_atp_table(i).schedule_material_id := x_atp_table.identifier(i);
1265 l_temp_atp_table(i).quantity_available := trunc(x_atp_table.available_quantity(i));
1266 -- l_temp_atp_table(i).scheduled_quantity := trunc(x_atp_table.requested_date_quantity(i));
1267 l_temp_atp_table(i).error_code := x_atp_table.error_code(i);
1268 l_temp_atp_table(i).error_message := 'Successfully Scheduled';
1269 l_temp_atp_table(i).item_description := x_atp_table.inventory_item_name(i);
1270
1271 --yazhou 12-May-2006 starts
1272 --Bug fix #5223772
1273 UPDATE ahl_schedule_materials
1274 SET scheduled_date = x_atp_table.ship_date(i),
1275 scheduled_quantity = l_planned_matrl_tbl(i).required_quantity,
1276 object_version_number = object_version_number + 1
1277 WHERE scheduled_material_id = x_atp_table.identifier(i);
1278 --yazhou 12-May-2006 ends
1279 END IF;
1280 END LOOP;
1281 END IF;
1282 -- Assign to out parameter
1283 IF l_temp_atp_table.COUNT > 0 THEN
1284 FOR i IN l_temp_atp_table.FIRST..l_temp_atp_table.LAST
1285 LOOP
1286 IF (l_log_statement >= l_log_current_level)THEN
1287 fnd_log.string(l_log_statement,
1288 L_DEBUG_KEY,
1289 'Before assign out parameter, Sch Mat ID: '||
1290 l_temp_atp_table(i).schedule_material_id ||
1291 ', Quantity Available: '||
1292 l_temp_atp_table(i).quantity_available ||
1293 ', Scheduled Date : '||
1294 l_temp_atp_table(i).scheduled_date ||
1295 ', Error Code : '||
1296 l_temp_atp_table(i).error_code ||
1297 ', Error Message : '||
1298 l_temp_atp_table(i).error_message);
1299 END IF;
1300 --
1301
1302 --SKPATHAK :: Bug 8392521 :: 02-APR-2009 :: Included the scheduled_date in the out param
1303 p_x_planned_matrl_tbl(i).scheduled_date := x_atp_table.ship_date(i);
1304 p_x_planned_matrl_tbl(i).schedule_material_id := l_temp_atp_table(i).schedule_material_id;
1305 p_x_planned_matrl_tbl(i).quantity_available := l_temp_atp_table(i).quantity_available;
1306 -- p_x_planned_matrl_tbl(i).scheduled_quantity := l_temp_atp_table(i).scheduled_quantity;
1307 p_x_planned_matrl_tbl(i).error_code := l_temp_atp_table(i).error_code;
1308 p_x_planned_matrl_tbl(i).error_message := 'For Item '||l_temp_atp_table(i).item_description||', '||l_temp_atp_table(i).error_message;
1309 p_x_planned_matrl_tbl(i).item_description := l_temp_atp_table(i).item_description;
1310 END LOOP;
1311 END IF;
1312
1313 -- Check Error Message stack.
1314 IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
1315 l_msg_count := FND_MSG_PUB.count_msg;
1316 IF l_msg_count > 0 THEN
1317 RAISE FND_API.G_EXC_ERROR;
1318 END IF;
1319 END IF;
1320 --Need to fix error messages
1321
1322 IF (l_log_procedure >= l_log_current_level) THEN
1323 fnd_log.string(l_log_procedure,
1324 L_DEBUG_KEY ||'.end',
1325 'At the end of PL SQL procedure. Return Status = ' || x_return_status);
1326 END IF;
1327 EXCEPTION
1328 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1329 ROLLBACK TO Call_ATP;
1330 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1331 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1332 p_count => l_msg_count,
1333 p_data => l_msg_data);
1334
1335 WHEN FND_API.G_EXC_ERROR THEN
1336 ROLLBACK TO Call_ATP;
1337 X_return_status := FND_API.G_RET_STS_ERROR;
1338 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1339 p_count => l_msg_count,
1340 p_data => l_msg_data);
1341
1342 WHEN OTHERS THEN
1343 ROLLBACK TO Call_ATP;
1344 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1345 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1346 THEN
1347 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_MATRL_AVAL_PVT',
1348 p_procedure_name => 'CALL_ATP',
1349 p_error_text => SUBSTR(SQLERRM,1,240));
1350 END IF;
1351 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1352 p_count => l_msg_count,
1353 p_data => l_msg_data);
1354
1355 END Call_ATP;
1356
1357 -- Start of Comments --
1358 -- Procedure name : Schedule_Planned_Mtrls
1359 -- Type : Public
1360 -- Function : This procedure calls ATP to schedule planned materials
1361 -- for Routine jobs derived requested quantity and task start date
1362 -- Pre-reqs :
1363 -- Parameters :
1364 --
1365 -- Standard IN Parameters :
1366 -- p_api_version IN NUMBER Required
1367 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
1368 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
1369 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
1370 -- Based on this flag, the API will set the default attributes.
1371 -- p_module_type In VARCHAR2 Default NULL
1372 -- This will be null.
1373 -- Standard OUT Parameters :
1374 -- x_return_status OUT VARCHAR2 Required
1375 -- x_msg_count OUT NUMBER Required
1376 -- x_msg_data OUT VARCHAR2 Required
1377 --
1378 -- Schedule_Planned_Matrls Parameters :
1379 -- p_x_planned_matrls_tbl IN OUT NOCOPY Planned_Matrls_Tbl,Required
1380 -- List of item attributes associated to visit task
1381 --
1382 PROCEDURE Schedule_Planned_Matrls (
1383 p_api_version IN NUMBER,
1384 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1385 p_commit IN VARCHAR2 := FND_API.g_false,
1386 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1387 p_x_planned_matrl_tbl IN OUT NOCOPY AHL_LTP_MATRL_AVAL_PUB.Planned_Matrl_Tbl,
1388 x_return_status OUT NOCOPY VARCHAR2,
1389 x_msg_count OUT NOCOPY NUMBER,
1390 x_msg_data OUT NOCOPY VARCHAR2)
1391 IS
1392 /* CURSOR Sch_Material_Cur (c_sch_mat_id IN NUMBER) IS
1393 SELECT schm.inventory_item_id,
1394 schm.organization_id,
1395 schm.uom,
1396 schm.requested_date,
1397 schm.status mat_status, --Added by sowsubra
1398 avtm.item_number --Modified by rnahata for ER 6391157, ahl_visit_task_matrl_v definition changed
1399 FROM ahl_schedule_materials schm,
1400 ahl_visit_task_matrl_v avtm
1401 WHERE schm.scheduled_material_id = avtm.schedule_material_id
1402 AND avtm.schedule_material_id = c_sch_mat_id;
1403 */
1404 -- arunjk modified the above cursor to retrieve the item number from base table since ahl_visit_task_matrl_v has only Planned Material Requirements
1405 CURSOR Sch_Material_Cur (c_sch_mat_id IN NUMBER) IS
1406 SELECT schm.inventory_item_id,
1407 AVTM.organization_id,
1408 schm.uom,
1409 schm.requested_date,
1410 schm.status mat_status,
1411 avtm.CONCATENATED_SEGMENTS
1412 FROM ahl_schedule_materials schm,
1413 mtl_system_items_kfv avtm
1414 WHERE SCHM.INVENTORY_ITEM_ID = AVTM.INVENTORY_ITEM_ID
1415 AND AVTM.ORGANIZATION_ID = SCHM.ORGANIZATION_ID
1416 AND SCHM.SCHEDULED_MATERIAL_ID = c_sch_mat_id;
1417
1418 -- anraj modified by adding two more columns task status code and meaning
1419 /* CURSOR Planned_Material_Cur (c_sch_mat_id IN NUMBER) IS
1420 SELECT visit_id,
1421 visit_task_id,
1422 visit_task_name,
1423 requested_quantity,
1424 scheduled_date,
1425 scheduled_quantity,
1426 item_number, --Modified by rnahata for ER 6391157, ahl_visit_task_matrl_v definition changed
1427 object_version_number,
1428 inventory_item_id,
1429 uom,
1430 requested_date,
1431 task_status_code,
1432 meaning
1433 FROM ahl_visit_task_matrl_v,FND_LOOKUP_VALUES_VL
1434 WHERE schedule_material_id = c_sch_mat_id
1435 AND LOOKUP_TYPE(+) = 'AHL_VWP_TASK_STATUS'
1436 AND LOOKUP_code = task_status_code;
1437 */
1438
1439 -- arunjk modified the above cursor to retrieve the details from base tables since ahl_visit_task_matrl_v has only Planned Material Requirements
1440 CURSOR Planned_Material_Cur (c_sch_mat_id IN NUMBER) IS
1441 SELECT ASMT.VISIT_ID,
1442 ASMT.VISIT_TASK_ID,
1443 AVTL.VISIT_TASK_NAME,
1444 ASMT.REQUESTED_QUANTITY,
1445 (SELECT scheduled_date
1446 FROM ahl_schedule_materials asml
1447 WHERE asml.scheduled_material_id = asmt.scheduled_material_id
1448 AND TRUNC(SCHEDULED_DATE) > TRUNC(REQUESTED_DATE)
1449 ) SCHEDULED_DATE,
1450 ASMT.SCHEDULED_QUANTITY,
1451 KFV.CONCATENATED_SEGMENTS ITEM_NUMBER,
1452 ASMT.OBJECT_VERSION_NUMBER,
1453 ASMT.INVENTORY_ITEM_ID,
1454 KFV.PRIMARY_UNIT_OF_MEASURE UOM,
1455 ASMT.REQUESTED_DATE,
1456 AVTL.STATUS_CODE TASK_STATUS_CODE,
1457 FLV.MEANING
1458 FROM AHL_SCHEDULE_MATERIALS ASMT,
1459 AHL_VISIT_TASKS_VL AVTL,
1460 MTL_SYSTEM_ITEMS_KFV KFV,
1461 FND_LOOKUP_VALUES_VL FLV
1462 WHERE SCHEDULED_MATERIAL_ID = c_sch_mat_id
1463 AND AVTL.VISIT_TASK_ID = ASMT.VISIT_TASK_ID
1464 AND ASMT.INVENTORY_ITEM_ID = KFV.INVENTORY_ITEM_ID(+)
1465 AND ASMT.ORGANIZATION_ID = KFV.ORGANIZATION_ID(+)
1466 AND AVTL.STATUS_CODE = FLV.LOOKUP_CODE
1467 AND FLV.LOOKUP_TYPE(+) = 'AHL_VWP_TASK_STATUS';
1468
1469 --Standard local variables
1470 l_api_name CONSTANT VARCHAR2(30) := 'Schedule_Planned_Matrls';
1471 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
1472 l_api_version CONSTANT NUMBER := 1.0;
1473 l_return_status VARCHAR2(1);
1474 l_msg_data VARCHAR2(2000);
1475 l_msg_count NUMBER;
1476
1477 l_planned_matrl_tbl AHL_LTP_MATRL_AVAL_PUB.Planned_Matrl_Tbl := p_x_planned_matrl_tbl;
1478 l_Sch_Material_Rec Sch_Material_Cur%ROWTYPE;
1479 l_Planned_Material_Rec Planned_Material_Cur%ROWTYPE;
1480 l_temp_planned_matrl_tbl AHL_LTP_MATRL_AVAL_PUB.Planned_Matrl_Tbl;
1481 j NUMBER := 1;
1482 BEGIN
1483
1484 IF (l_log_procedure >= l_log_current_level) THEN
1485 fnd_log.string(l_log_procedure,
1486 L_DEBUG_KEY ||'.begin',
1487 'At the start of PL SQL procedure. Number of Records : ' || l_planned_matrl_tbl.COUNT);
1488 END IF;
1489 -- Standard Start of API savepoint
1490 SAVEPOINT schedule_planned_matrls;
1491 -- Initialize message list if p_init_msg_list is set to TRUE.
1492 IF FND_API.to_boolean(p_init_msg_list) THEN
1493 FND_MSG_PUB.initialize;
1494 END IF;
1495 -- Initialize API return status to success
1496 x_return_status := FND_API.G_RET_STS_SUCCESS;
1497 -- Standard call to check for call compatibility.
1498 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
1499 l_api_version,
1500 l_api_name,G_PKG_NAME)
1501 THEN
1502 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1503 END IF;
1504
1505 --
1506 IF l_planned_matrl_tbl.COUNT > 0 THEN
1507 FOR i IN l_planned_matrl_tbl.FIRST..l_planned_matrl_tbl.LAST
1508 LOOP
1509 --Get schedule materil details
1510 IF l_planned_matrl_tbl(i).schedule_material_id IS NOT NULL THEN
1511 OPEN Sch_Material_Cur(l_planned_matrl_tbl(i).schedule_material_id);
1512 FETCH Sch_Material_Cur INTO l_planned_matrl_tbl(i).inventory_item_id,
1513 l_planned_matrl_tbl(i).organization_id,
1514 l_planned_matrl_tbl(i).primary_uom_code,
1515 l_planned_matrl_tbl(i).requested_date,
1516 l_planned_matrl_tbl(i).mat_status, --Added by sowsubra
1517 l_planned_matrl_tbl(i).item_description;
1518 CLOSE Sch_Material_Cur;
1519 END IF;
1520
1521 --Added by sowsubra - starts
1522 IF l_planned_matrl_tbl(i).mat_status = 'IN-SERVICE' THEN
1523 Fnd_Message.SET_NAME('AHL','AHL_MAT_STS_INSERVICE');
1524 Fnd_Msg_Pub.ADD;
1525 RAISE Fnd_Api.G_EXC_ERROR;
1526 END IF;
1527 --Added by sowsubra - ends
1528 END LOOP;
1529 END IF;
1530
1531 --Assign values start from index value 1
1532 IF l_planned_matrl_tbl.COUNT > 0 THEN
1533 FOR i IN l_planned_matrl_tbl.FIRST..l_planned_matrl_tbl.LAST
1534 LOOP
1535 l_temp_planned_matrl_tbl(j).inventory_item_id := l_planned_matrl_tbl(i).inventory_item_id;
1536 l_temp_planned_matrl_tbl(j).visit_id := l_planned_matrl_tbl(i).visit_id;
1537 l_temp_planned_matrl_tbl(j).visit_task_id := l_planned_matrl_tbl(i).visit_task_id;
1538 l_temp_planned_matrl_tbl(j).schedule_material_id := l_planned_matrl_tbl(i).schedule_material_id;
1539 l_temp_planned_matrl_tbl(j).item_description := l_planned_matrl_tbl(i).item_description;
1540 l_temp_planned_matrl_tbl(j).organization_id := l_planned_matrl_tbl(i).organization_id;
1541 l_temp_planned_matrl_tbl(j).primary_uom_code := l_planned_matrl_tbl(i).primary_uom_code;
1542 l_temp_planned_matrl_tbl(j).requested_date := l_planned_matrl_tbl(i).requested_date;
1543 l_temp_planned_matrl_tbl(j).required_quantity := l_planned_matrl_tbl(i).required_quantity;
1544 j := j + 1;
1545 END LOOP;
1546 END IF;
1547
1548 --
1549 IF l_temp_planned_matrl_tbl.COUNT > 0 THEN
1550 IF (l_log_statement >= l_log_current_level) THEN
1551 fnd_log.string(l_log_statement,
1552 L_DEBUG_KEY,
1553 'Before calling Call ATP');
1554 END IF;
1555
1556 -- Call local procedure which calls atp Api
1557 Call_ATP
1558 (p_api_version => p_api_version,
1559 p_init_msg_list => p_init_msg_list,
1560 p_validation_level => p_validation_level,
1561 p_x_planned_matrl_tbl => l_temp_planned_matrl_tbl,
1562 x_return_status => l_return_status,
1563 x_msg_count => l_msg_count,
1564 x_msg_data => l_msg_data);
1565 END IF;
1566
1567 IF (l_log_statement >= l_log_current_level) THEN
1568 fnd_log.string(l_log_statement,
1569 L_DEBUG_KEY,
1570 'After calling Call ATP. Return Status : '|| l_return_status ||
1571 ', Returned Final Records : '||l_temp_planned_matrl_tbl.COUNT);
1572 END IF;
1573
1574 -- Check Error Message stack.
1575 IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
1576 l_msg_count := FND_MSG_PUB.count_msg;
1577 IF l_msg_count > 0 THEN
1578 RAISE FND_API.G_EXC_ERROR;
1579 END IF;
1580 END IF;
1581
1582 --Assign out parameter
1583 IF l_temp_planned_matrl_tbl.COUNT > 0 THEN
1584 FOR i IN l_temp_planned_matrl_tbl.FIRST..l_temp_planned_matrl_tbl.LAST
1585 LOOP
1586 --
1587 --Get schedule materil details
1588 IF l_temp_planned_matrl_tbl(i).schedule_material_id IS NOT NULL THEN
1589 OPEN Planned_Material_Cur(l_planned_matrl_tbl(i).schedule_material_id);
1590 FETCH Planned_Material_Cur INTO l_Planned_Material_Rec;
1591 CLOSE Planned_Material_Cur;
1592 --
1593 p_x_planned_matrl_tbl(i).schedule_material_id := l_temp_planned_matrl_tbl(i).schedule_material_id;
1594 p_x_planned_matrl_tbl(i).object_version_number := l_Planned_Material_Rec.object_version_number;
1595 p_x_planned_matrl_tbl(i).inventory_item_id := l_Planned_Material_Rec.inventory_item_id;
1596
1597 --Modified by rnahata for ER 6391157, ahl_visit_task_matrl_v definition changed
1598 p_x_planned_matrl_tbl(i).item_description := l_Planned_Material_Rec.item_number;
1599
1600 p_x_planned_matrl_tbl(i).visit_id := l_Planned_Material_Rec.visit_id;
1601 p_x_planned_matrl_tbl(i).visit_task_id := l_Planned_Material_Rec.visit_task_id;
1602 p_x_planned_matrl_tbl(i).task_name := l_Planned_Material_Rec.visit_task_name;
1603
1604 -- anraj added fot the Material Availability UI
1605 p_x_planned_matrl_tbl(i).task_status_code := l_Planned_Material_Rec.task_status_code;
1606 p_x_planned_matrl_tbl(i).task_status_meaning := l_Planned_Material_Rec.meaning;
1607
1608 --SKPATHAK :: Bug 8392521 :: 02-APR-2009 :: Included the scheduled_date in the out param
1609 p_x_planned_matrl_tbl(i).scheduled_date := l_temp_planned_matrl_tbl(i).scheduled_date;
1610 p_x_planned_matrl_tbl(i).requested_date := l_Planned_Material_Rec.requested_date;
1611 p_x_planned_matrl_tbl(i).required_quantity := l_Planned_Material_Rec.requested_quantity;
1612 p_x_planned_matrl_tbl(i).quantity_available := l_temp_planned_matrl_tbl(i).quantity_available;
1613 -- p_x_planned_matrl_tbl(i).scheduled_quantity := l_Planned_Material_Rec.scheduled_quantity;
1614 p_x_planned_matrl_tbl(i).primary_uom := l_Planned_Material_Rec.uom;
1615 p_x_planned_matrl_tbl(i).error_code := l_temp_planned_matrl_tbl(i).error_code;
1616 p_x_planned_matrl_tbl(i).error_message := l_temp_planned_matrl_tbl(i).error_message;
1617
1618 IF (l_log_statement >= l_log_current_level)THEN
1619 fnd_log.string(l_log_statement,
1620 L_DEBUG_KEY,
1621 'After Assign, Sch Mat Id : ' || p_x_planned_matrl_tbl(i).schedule_material_id ||
1622 ', Quantity Available : ' || p_x_planned_matrl_tbl(i).quantity_available ||
1623 ', Scheduled Quantity : ' || p_x_planned_matrl_tbl(i).scheduled_date ||
1624 ', Error Code : ' || p_x_planned_matrl_tbl(i).error_code ||
1625 ', Error Message : ' || p_x_planned_matrl_tbl(i).error_message);
1626 END IF;
1627 END IF;
1628 END LOOP;
1629 END IF;
1630
1631 -- Standard check of p_commit
1632 IF FND_API.TO_BOOLEAN(p_commit) THEN
1633 COMMIT WORK;
1634 END IF;
1635
1636 IF (l_log_procedure >= l_log_current_level) THEN
1637 fnd_log.string(l_log_procedure,
1638 L_DEBUG_KEY ||'.end',
1639 'At the end of PL SQL procedure. Return Status = ' || x_return_status);
1640 END IF;
1641
1642 EXCEPTION
1643 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1644 ROLLBACK TO Schedule_Planned_Matrls;
1645 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1646 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1647 p_count => l_msg_count,
1648 p_data => l_msg_data);
1649
1650 WHEN FND_API.G_EXC_ERROR THEN
1651 ROLLBACK TO Schedule_Planned_Matrls;
1652 X_return_status := FND_API.G_RET_STS_ERROR;
1653 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1654 p_count => l_msg_count,
1655 p_data => l_msg_data);
1656
1657 WHEN OTHERS THEN
1658 ROLLBACK TO Schedule_Planned_Matrls;
1659 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1660 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1661 THEN
1662 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_MATRL_AVAL_PVT',
1663 p_procedure_name => 'SCHEDULE_PLANNED_MATRLS',
1664 p_error_text => SUBSTR(SQLERRM,1,240));
1665 END IF;
1666 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1667 p_count => l_msg_count,
1668 p_data => l_msg_data);
1669
1670 END Schedule_Planned_Matrls;
1671 --
1672 -- Start of Comments --
1673 -- Procedure name : Schedule_All_Materials
1674 -- Type : Public
1675 -- Function : This procedure calls ATP to schedule planned materials for a visit
1676 -- for Routine jobs derived requested quantity and task start date
1677 -- Pre-reqs :
1678 -- Parameters :
1679 --
1680 -- Standard IN Parameters :
1681 -- p_api_version IN NUMBER Required
1682 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
1683 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
1684 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
1685 -- Based on this flag, the API will set the default attributes.
1686 -- p_module_type In VARCHAR2 Default NULL
1687 -- This will be null.
1688 -- Standard OUT Parameters :
1689 -- x_return_status OUT VARCHAR2 Required
1690 -- x_msg_count OUT NUMBER Required
1691 -- x_msg_data OUT VARCHAR2 Required
1692 --
1693 -- Schedule_All_Materials Parameters :
1694 -- p_visit_id IN Number,Required
1695 -- List of item attributes associated to visit task
1696 --
1697 PROCEDURE Schedule_All_Materials (
1698 p_api_version IN NUMBER,
1699 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1700 p_commit IN VARCHAR2 := FND_API.g_false,
1701 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1702 p_visit_id IN NUMBER,
1703 x_planned_matrl_tbl OUT NOCOPY AHL_LTP_MATRL_AVAL_PUB.Planned_Matrl_Tbl,
1704 x_return_status OUT NOCOPY VARCHAR2,
1705 x_msg_count OUT NOCOPY NUMBER,
1706 x_msg_data OUT NOCOPY VARCHAR2)
1707 IS
1708
1709 -- yazhou 03-JUL-2006 starts
1710 -- bug fix#5303378
1711
1712 CURSOR Get_Visit_Task_Matrl_Cur (C_VISIT_ID IN NUMBER) IS
1713 SELECT schm.scheduled_material_id,
1714 schm.organization_id,
1715 schm.visit_id,
1716 schm.visit_task_id,
1717 schm.material_request_type,
1718 schm.uom,
1719 schm.inventory_item_id,
1720 schm.requested_date,
1721 schm.requested_quantity,
1722 mtl.concatenated_segments
1723 FROM ahl_schedule_materials schm,
1724 mtl_system_items_vl mtl
1725 WHERE schm.inventory_item_id = mtl.inventory_item_id
1726 AND schm.organization_id = mtl.organization_id
1727 --SKPATHAK :: Bug 8429732 :: 17-APR-2009
1728 --Commented out the condition (requested_quantity <> 0)
1729 /*AND schm.requested_quantity <> 0*/
1730 AND NVL(schm.status, 'X') <> 'IN-SERVICE' --Added by sowsubra for Issue 105
1731 AND schm.visit_id = C_VISIT_ID;
1732 -- yazhou 03-JUL-2006 ends
1733
1734 --Standard local variables
1735 l_api_name CONSTANT VARCHAR2(30) := 'Schedule_All_Materials';
1736 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
1737 l_api_version CONSTANT NUMBER := 1.0;
1738 l_return_status VARCHAR2(1);
1739 l_msg_data VARCHAR2(2000);
1740 l_msg_count NUMBER;
1741 --
1742 l_planned_matrl_tbl AHL_LTP_MATRL_AVAL_PUB.Planned_Matrl_Tbl;
1743 l_Visit_Task_Matrl_Rec Get_Visit_Task_Matrl_Cur%ROWTYPE;
1744 i NUMBER;
1745 BEGIN
1746
1747 IF (l_log_procedure >= l_log_current_level) THEN
1748 fnd_log.string(l_log_procedure,
1749 L_DEBUG_KEY ||'.begin',
1750 'At the start of PL SQL procedure. Visit Id = ' || p_visit_id);
1751 END IF;
1752 -- Standard Start of API savepoint
1753 SAVEPOINT Schedule_All_Materials;
1754 -- Initialize message list if p_init_msg_list is set to TRUE.
1755 IF FND_API.to_boolean(p_init_msg_list) THEN
1756 FND_MSG_PUB.initialize;
1757 END IF;
1758 -- Initialize API return status to success
1759 x_return_status := FND_API.G_RET_STS_SUCCESS;
1760 -- Standard call to check for call compatibility.
1761 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
1762 l_api_version,
1763 l_api_name,G_PKG_NAME)
1764 THEN
1765 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1766 END IF;
1767
1768 IF p_visit_id IS NOT NULL THEN
1769 OPEN Get_Visit_Task_Matrl_Cur(p_visit_id);
1770 i := 1;
1771 LOOP
1772 FETCH Get_Visit_Task_Matrl_Cur INTO l_Visit_Task_Matrl_Rec;
1773 EXIT WHEN Get_Visit_Task_Matrl_Cur%NOTFOUND;
1774 --Assign to table
1775 l_planned_matrl_tbl(i).visit_id := l_Visit_Task_Matrl_Rec.visit_id;
1776 l_planned_matrl_tbl(i).visit_task_id := l_Visit_Task_Matrl_Rec.visit_task_id;
1777 l_planned_matrl_tbl(i).schedule_material_id := l_Visit_Task_Matrl_Rec.scheduled_material_id;
1778 l_planned_matrl_tbl(i).inventory_item_id := l_Visit_Task_Matrl_Rec.inventory_item_id;
1779 l_planned_matrl_tbl(i).item_description := l_Visit_Task_Matrl_Rec.concatenated_segments;
1780 l_planned_matrl_tbl(i).organization_id := l_Visit_Task_Matrl_Rec.organization_id;
1781 l_planned_matrl_tbl(i).primary_uom_code := l_Visit_Task_Matrl_Rec.uom;
1782 l_planned_matrl_tbl(i).requested_date := l_Visit_Task_Matrl_Rec.requested_date;
1783 l_planned_matrl_tbl(i).required_quantity := l_Visit_Task_Matrl_Rec.requested_quantity;
1784 i := i + 1;
1785 END LOOP;
1786 CLOSE Get_Visit_Task_Matrl_Cur;
1787 END IF; --Visit not null
1788
1789 IF (l_log_statement >= l_log_current_level) THEN
1790 fnd_log.string(l_log_statement,
1791 L_DEBUG_KEY,
1792 'Before calling Call ATP No of Records'||l_planned_matrl_tbl.COUNT);
1793 END IF;
1794
1795 IF l_planned_matrl_tbl.COUNT > 0 THEN
1796 -- Call local procedure which calls atp Api
1797 schedule_planned_matrls
1798 (p_api_version => p_api_version,
1799 p_init_msg_list => p_init_msg_list,
1800 p_validation_level => p_validation_level,
1801 p_x_planned_matrl_tbl => l_Planned_Matrl_Tbl,
1802 x_return_status => l_return_status,
1803 x_msg_count => l_msg_count,
1804 x_msg_data => l_msg_data);
1805 END IF;
1806
1807 IF (l_log_statement >= l_log_current_level) THEN
1808 fnd_log.string(l_log_statement,
1809 L_DEBUG_KEY,
1810 'After calling Call ATP, Return Status : '|| l_return_status);
1811 END IF;
1812
1813 -- Check Error Message stack.
1814 IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
1815 l_msg_count := FND_MSG_PUB.count_msg;
1816 IF l_msg_count > 0 THEN
1817 RAISE FND_API.G_EXC_ERROR;
1818 END IF;
1819 END IF;
1820 --Assign to out variable
1821 IF l_Planned_Matrl_Tbl.COUNT > 0 THEN
1822 FOR i IN l_Planned_Matrl_Tbl.FIRST..l_Planned_Matrl_Tbl.LAST
1823 LOOP
1824 x_planned_matrl_tbl(i).schedule_material_id := l_Planned_Matrl_Tbl(i).schedule_material_id;
1825 x_Planned_Matrl_Tbl(i).object_version_number := l_Planned_Matrl_Tbl(i).object_version_number;
1826 x_Planned_Matrl_Tbl(i).inventory_item_id := l_Planned_Matrl_Tbl(i).inventory_item_id;
1827 x_Planned_Matrl_Tbl(i).item_description := l_Planned_Matrl_Tbl(i).item_description;
1828 x_Planned_Matrl_Tbl(i).visit_id := l_Planned_Matrl_Tbl(i).visit_id;
1829 x_Planned_Matrl_Tbl(i).visit_task_id := l_Planned_Matrl_Tbl(i).visit_task_id;
1830 x_Planned_Matrl_Tbl(i).task_name := l_Planned_Matrl_Tbl(i).task_name;
1831 -- anraj added
1832 x_Planned_Matrl_Tbl(i).task_status_code := l_Planned_Matrl_Tbl(i).task_status_code;
1833 x_Planned_Matrl_Tbl(i).task_status_meaning := l_Planned_Matrl_Tbl(i).task_status_meaning;
1834
1835 --SKPATHAK :: Bug 8392521 :: 02-APR-2009 :: Included the scheduled_date in the out param
1836 x_Planned_Matrl_Tbl(i).scheduled_date := l_Planned_Matrl_Tbl(i).scheduled_date;
1837 x_Planned_Matrl_Tbl(i).requested_date := l_Planned_Matrl_Tbl(i).requested_date;
1838 x_Planned_Matrl_Tbl(i).required_quantity := l_Planned_Matrl_Tbl(i).required_quantity;
1839 x_Planned_Matrl_Tbl(i).quantity_available := l_Planned_Matrl_Tbl(i).quantity_available;
1840 -- x_Planned_Matrl_Tbl(i).scheduled_quantity := l_Planned_Matrl_Tbl(i).scheduled_quantity;
1841 x_Planned_Matrl_Tbl(i).primary_uom := l_Planned_Matrl_Tbl(i).primary_uom;
1842 x_Planned_Matrl_Tbl(i).error_code := l_Planned_Matrl_Tbl(i).error_code;
1843 x_Planned_Matrl_Tbl(i).error_message := l_Planned_Matrl_Tbl(i).error_message;
1844
1845 IF (l_log_statement >= l_log_current_level) THEN
1846 fnd_log.string(l_log_statement,
1847 L_DEBUG_KEY,
1848 'After Assign, Sch Mat Id : ' || x_planned_matrl_tbl(i).schedule_material_id ||
1849 ', Quantity Available : ' || x_planned_matrl_tbl(i).quantity_available ||
1850 ', Scheduled Quantity : ' || x_planned_matrl_tbl(i).scheduled_date ||
1851 ', Error Code : ' || x_planned_matrl_tbl(i).error_code ||
1852 ', Error Message : ' || x_planned_matrl_tbl(i).error_message);
1853 END IF;
1854 END LOOP;
1855 END IF;
1856 -- Standard check of p_commit
1857 IF FND_API.TO_BOOLEAN(p_commit) THEN
1858 COMMIT WORK;
1859 END IF;
1860
1861 IF (l_log_procedure >= l_log_current_level) THEN
1862 fnd_log.string(l_log_procedure,
1863 L_DEBUG_KEY ||'.end',
1864 'At the end of PL SQL procedure. Return Status = ' || x_return_status);
1865 END IF;
1866
1867 EXCEPTION
1868 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1869 ROLLBACK TO Schedule_All_Materials;
1870 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1871 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1872 p_count => l_msg_count,
1873 p_data => l_msg_data);
1874
1875 WHEN FND_API.G_EXC_ERROR THEN
1876 ROLLBACK TO Schedule_All_Materials;
1877 X_return_status := FND_API.G_RET_STS_ERROR;
1878 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1879 p_count => l_msg_count,
1880 p_data => l_msg_data);
1881
1882 WHEN OTHERS THEN
1883 ROLLBACK TO Schedule_All_Materials;
1884 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1885 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1886 THEN
1887 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_MATRL_AVAL_PVT',
1888 p_procedure_name => 'SCHEDULE_ALL_MATERIALS',
1889 p_error_text => SUBSTR(SQLERRM,1,240));
1890 END IF;
1891 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1892 p_count => l_msg_count,
1893 p_data => l_msg_data);
1894
1895 END Schedule_All_Materials;
1896
1897 --------------------------------------------------------------------------------------
1898 -- Added by surrkuma on 14-JUL-2010 for bug 9901811
1899 -- Start of Comments --
1900 -- Procedure name: Unschedule_deleted_materials
1901 -- Type : Public
1902 -- Function : This procedure calls ATP to unschedule planned materials that
1903 -- have since been soft-deleted.
1904 -- Pre-reqs :
1905 -- Parameters :
1906 --
1907 -- Standard IN Parameters :
1908 -- p_api_version IN NUMBER Required
1909 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
1910 -- Standard OUT Parameters :
1911 -- x_return_status OUT VARCHAR2 Required
1912 -- x_msg_count OUT NUMBER Required
1913 -- x_msg_data OUT VARCHAR2 Required
1914 --
1915 -- Unschedule_deleted_materials Parameters :
1916 -- p_deleted_matrl_tbl IN Planned_Matrls_Tbl Required
1917 --
1918 PROCEDURE Unschedule_deleted_materials (
1919 p_api_version IN NUMBER,
1920 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1921 p_deleted_matrl_tbl IN AHL_LTP_MATRL_AVAL_PUB.Planned_Matrl_Tbl,
1922 x_return_status OUT NOCOPY VARCHAR2,
1923 x_msg_count OUT NOCOPY NUMBER,
1924 x_msg_data OUT NOCOPY VARCHAR2)
1925 IS
1926
1927 CURSOR Error_Message_Cur(c_error_code IN NUMBER) IS
1928 SELECT meaning
1929 FROM mfg_lookups
1930 WHERE lookup_type = 'MTL_DEMAND_INTERFACE_ERRORS'
1931 AND lookup_code = C_Error_Code;
1932
1933 CURSOR Order_Number_Cur(c_visit_task_id IN NUMBER) IS
1934 SELECT visit_number||visit_task_number Order_Number
1935 FROM ahl_visit_tasks_v
1936 WHERE visit_task_id = c_visit_task_id;
1937
1938 CURSOR Instance_Id_Cur IS
1939 SELECT instance_id
1940 FROM MRP_AP_APPS_INSTANCES;
1941
1942 -- Cursor added by surrkuma on 14-JUL-2010 for the bug 9901811
1943 CURSOR Get_Item_ATP_Flag(c_item_id IN NUMBER, c_org_id IN NUMBER) IS
1944 SELECT NVL(ATP_FLAG, 'N') from mtl_system_items
1945 WHERE INVENTORY_ITEM_ID = c_item_id
1946 AND ORGANIZATION_ID = c_org_id;
1947
1948 --Standard local variables
1949 l_api_name CONSTANT VARCHAR2(30) := 'Unschedule_deleted_materials';
1950 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
1951 l_api_version CONSTANT NUMBER := 1.0;
1952 l_return_status VARCHAR2(1);
1953 l_msg_data VARCHAR2(2000);
1954 l_msg_count NUMBER;
1955
1956 --Varibales to call mrp atp pub
1957 l_session_id NUMBER;
1958 l_instance_id NUMBER;
1959 l_atp_table Mrp_Atp_Pub.ATP_Rec_Typ;
1960 x_atp_table Mrp_Atp_Pub.ATP_Rec_Typ;
1961 x_atp_supply_demand Mrp_Atp_Pub.ATP_Supply_Demand_Typ;
1962 x_atp_period Mrp_Atp_Pub.ATP_Period_Typ;
1963 x_atp_details Mrp_Atp_Pub.ATP_Details_Typ;
1964 l_error_msg VARCHAR2(2000);
1965 l_order_number NUMBER;
1966
1967 -- Variables added by surrkuma on 14-JUL-2010 for the bug 9901811
1968 l_atp_instance_flag BOOLEAN := true;
1969 l_ATP_Flag VARCHAR2(1);
1970
1971 BEGIN
1972
1973 IF (l_log_procedure >= l_log_current_level) THEN
1974 fnd_log.string(l_log_procedure, L_DEBUG_KEY ||'.begin',
1975 'At the start of PL/SQL procedure. Number of Records: ' || p_deleted_matrl_tbl.COUNT);
1976 END IF;
1977
1978 -- Standard Start of API savepoint
1979 SAVEPOINT Unschedule_deleted_materials;
1980
1981 -- Initialize message list if p_init_msg_list is set to TRUE.
1982 IF FND_API.to_boolean(p_init_msg_list) THEN
1983 FND_MSG_PUB.initialize;
1984 END IF;
1985
1986 -- Initialize API return status to success
1987 x_return_status := FND_API.G_RET_STS_SUCCESS;
1988
1989 -- Standard call to check for call compatibility.
1990 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, l_api_version, l_api_name, G_PKG_NAME) THEN
1991 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1992 END IF;
1993
1994 --Get session id
1995 SELECT MRP_ATP_SCHEDULE_TEMP_S.NEXTVAL
1996 INTO l_session_id FROM DUAL;
1997 IF (l_log_statement >= l_log_current_level) THEN
1998 fnd_log.string(l_log_statement, L_DEBUG_KEY, 'Session Id: ' || l_session_id);
1999 END IF;
2000
2001 --Get instance Id
2002 -- Changes by surrkuma on 14-JUL-2010 for the bug 9901811
2003 -- Don't throw this error here. Delay it till an ATP'able item is found
2004 OPEN Instance_Id_Cur;
2005 FETCH Instance_Id_Cur INTO l_instance_id;
2006 IF Instance_Id_Cur%NOTFOUND THEN
2007 l_atp_instance_flag := FALSE;
2008 -- FND_MESSAGE.Set_Name( 'AHL','AHL_LTP_ATP_INS_ENABLE' );
2009 -- FND_MSG_PUB.add;
2010 -- CLOSE Instance_Id_Cur;
2011 -- RAISE FND_API.G_EXC_ERROR;
2012 END IF;
2013 CLOSE Instance_Id_Cur;
2014 IF (l_log_statement >= l_log_current_level) THEN
2015 fnd_log.string(l_log_statement, L_DEBUG_KEY, 'Instance Id: ' || l_instance_id);
2016 END IF;
2017
2018 -- Loop through all the records
2019 FOR i IN p_deleted_matrl_tbl.FIRST .. p_deleted_matrl_tbl.LAST
2020 LOOP
2021 --
2022 IF p_deleted_matrl_tbl.EXISTS(i) THEN
2023 -- Begin changes by surrkuma on 14-JUL-2010 for the bug 9901811
2024 -- If there is no ATP Instance, but an ATP'able item exists, raise an exception
2025 IF (l_atp_instance_flag = FALSE) THEN
2026 OPEN Get_Item_ATP_Flag(p_deleted_matrl_tbl(i).inventory_item_id, p_deleted_matrl_tbl(i).organization_id);
2027 FETCH Get_Item_ATP_Flag INTO l_ATP_flag;
2028 CLOSE Get_Item_ATP_Flag;
2029 IF (l_ATP_flag <> 'N') THEN
2030 FND_MESSAGE.Set_Name('AHL','AHL_LTP_ATP_INS_ENABLE');
2031 FND_MSG_PUB.add;
2032 RAISE FND_API.G_EXC_ERROR;
2033 END IF;
2034 END IF;
2035 -- End changes by surrkuma on 11-JUN-2009 for the bug 9901811
2036
2037 --Call extend Atp
2038 MSC_ATP_GLOBAL.Extend_ATP(l_atp_table, x_return_status);
2039 --
2040 l_atp_table.inventory_item_id(i) := p_deleted_matrl_tbl(i).inventory_item_id;
2041 l_atp_table.inventory_item_name(i) := p_deleted_matrl_tbl(i).item_description;
2042 l_atp_table.instance_id(i) := l_instance_id;
2043 l_atp_table.source_organization_id(i) := p_deleted_matrl_tbl(i).organization_id;
2044 l_atp_table.identifier(i) := p_deleted_matrl_tbl(i).schedule_material_id;
2045 l_atp_table.demand_source_type(i) := 100;
2046 l_atp_table.quantity_ordered(i) := 0; -- Hardcode quantity to zero to unschedule
2047 l_atp_table.quantity_UOM(i) := p_deleted_matrl_tbl(i).primary_uom_code;
2048 l_atp_table.requested_ship_date(i) := p_deleted_matrl_tbl(i).requested_date;
2049 l_atp_table.action(i) := 120; --Rescheduling
2050 l_atp_table.Old_Source_Organization_Id(i) := p_deleted_matrl_tbl(i).organization_id;--Rescheduling
2051 --Get Concatenated visit number, task number
2052 OPEN Order_Number_Cur(p_deleted_matrl_tbl(i).visit_task_id);
2053 FETCH Order_Number_Cur INTO l_order_number;
2054 CLOSE Order_Number_Cur;
2055 --Assign to atp record
2056 l_atp_table.order_number(i) := l_order_number;
2057 l_atp_table.calling_module(i) := 867; --fnd_global.prog_appl_id;
2058 END IF;
2059 END LOOP;
2060
2061 IF (l_log_statement >= l_log_current_level) THEN
2062 fnd_log.string(l_log_statement, L_DEBUG_KEY,
2063 'Before calling MRP_ATP_PUB.CALL_ATP. Calling Module count: ' || l_atp_table.Calling_module.count);
2064 END IF;
2065
2066 -- Call ATP to Reschedule
2067 MRP_ATP_PUB.CALL_ATP(l_session_id,
2068 l_atp_table,
2069 x_atp_table,
2070 x_atp_supply_demand,
2071 x_atp_period,
2072 x_atp_details,
2073 x_return_status,
2074 x_msg_data,
2075 x_msg_count);
2076
2077 IF (l_log_statement >= l_log_current_level) THEN
2078 fnd_log.string(l_log_statement, L_DEBUG_KEY,
2079 'After calling MRP_ATP_PUB.CALL_ATP. Return Status : ' || x_return_status);
2080 END IF;
2081
2082 -- Check Error Message stack.
2083 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2084 l_msg_count := FND_MSG_PUB.count_msg;
2085 IF l_msg_count > 0 THEN
2086 RAISE FND_API.G_EXC_ERROR;
2087 END IF;
2088 END IF;
2089
2090 --Check for error code
2091 IF x_atp_table.Error_Code.COUNT > 0 THEN
2092 FOR i IN x_atp_table.Error_Code.FIRST .. x_atp_table.Error_Code.LAST
2093 LOOP
2094 IF (x_atp_table.Error_Code.EXISTS(i) AND x_atp_table.error_code(i) <> 0) THEN
2095 --Get error message
2096 OPEN Error_Message_Cur(x_atp_table.error_code(i));
2097 FETCH Error_Message_Cur INTO l_error_msg;
2098 CLOSE Error_Message_Cur;
2099 IF (l_log_statement >= l_log_current_level) THEN
2100 fnd_log.string(l_log_statement, L_DEBUG_KEY,
2101 'x_atp_table.identifier(i) = ' || x_atp_table.identifier(i) ||
2102 ', x_atp_table.error_code(i) = ' || x_atp_table.error_code(i) ||
2103 ', Error Message = ' || l_error_msg);
2104 END IF;
2105 ELSE
2106 --Error code is zero: Success
2107 IF (l_log_statement >= l_log_current_level) THEN
2108 fnd_log.string(l_log_statement, L_DEBUG_KEY,
2109 'x_atp_table.identifier(i) = ' || x_atp_table.identifier(i) ||
2110 ', Successfully unscheduled.');
2111 END IF;
2112 END IF;
2113 END LOOP;
2114 END IF;
2115
2116 -- Check Error Message stack.
2117 IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
2118 l_msg_count := FND_MSG_PUB.count_msg;
2119 IF l_msg_count > 0 THEN
2120 RAISE FND_API.G_EXC_ERROR;
2121 END IF;
2122 END IF;
2123
2124 IF (l_log_procedure >= l_log_current_level) THEN
2125 fnd_log.string(l_log_procedure,
2126 L_DEBUG_KEY ||'.end',
2127 'At the end of PL SQL procedure. Return Status = ' || x_return_status);
2128 END IF;
2129 EXCEPTION
2130 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2131 ROLLBACK TO Unschedule_deleted_materials;
2132 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2133 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2134 p_count => l_msg_count,
2135 p_data => l_msg_data);
2136
2137 WHEN FND_API.G_EXC_ERROR THEN
2138 ROLLBACK TO Unschedule_deleted_materials;
2139 X_return_status := FND_API.G_RET_STS_ERROR;
2140 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2141 p_count => l_msg_count,
2142 p_data => l_msg_data);
2143
2144 WHEN OTHERS THEN
2145 ROLLBACK TO Unschedule_deleted_materials;
2146 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2147 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2148 THEN
2149 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_MATRL_AVAL_PVT',
2150 p_procedure_name => 'Unschedule_deleted_materials',
2151 p_error_text => SUBSTR(SQLERRM,1,240));
2152 END IF;
2153 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2154 p_count => l_msg_count,
2155 p_data => l_msg_data);
2156
2157 END Unschedule_deleted_materials;
2158
2159 END AHL_LTP_MATRL_AVAL_PVT;