[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.7 2008/02/25 11:31:38 rnahata 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 l_atp_table.Identifier := Mrp_Atp_Pub.number_arr(l_identifier);
155 l_atp_table.Instance_Id := MRP_ATP_PUB.number_arr(l_instance_id) ; --223);
156 l_atp_table.Calling_Module := Mrp_Atp_Pub.number_arr(p_calling_module);
157 l_atp_table.Customer_Id := Mrp_Atp_Pub.number_arr(NULL);
158 l_atp_table.Customer_Site_Id := Mrp_Atp_Pub.number_arr(NULL);
159 l_atp_table.Destination_Time_Zone := Mrp_Atp_Pub.char30_arr(NULL);
160 l_atp_table.Quantity_Ordered := Mrp_Atp_Pub.number_arr(p_quantity_required);
161 l_atp_table.Quantity_UOM := Mrp_Atp_Pub.char3_arr(p_uom);
162 l_atp_table.Requested_Ship_Date := Mrp_Atp_Pub.date_arr(to_date(p_requested_date,'DD-MM-YYYY'));
163 l_atp_table.Requested_Arrival_Date := Mrp_Atp_Pub.date_arr(null);
164 l_atp_table.Latest_Acceptable_Date := MRP_ATP_PUB.date_arr(null);
165 l_atp_table.Delivery_Lead_Time := Mrp_Atp_Pub.number_arr(NULL);
166 l_atp_table.Freight_Carrier := Mrp_Atp_Pub.char30_arr(NULL);
167 l_atp_table.Ship_Method := Mrp_Atp_Pub.char30_arr(NULL);
168 l_atp_table.Demand_Class := Mrp_Atp_Pub.char30_arr(NULL);
169 l_atp_table.Ship_Set_Name := Mrp_Atp_Pub.char30_arr(NULL);
170 l_atp_table.Arrival_Set_Name := Mrp_Atp_Pub.char30_arr(NULL);
171 l_atp_table.Override_Flag := Mrp_Atp_Pub.char1_arr(NULL);
172 l_atp_table.Action := Mrp_Atp_Pub.number_arr(100);
173 l_atp_table.Ship_Date := Mrp_Atp_Pub.date_arr(sysdate);
174 l_atp_table.Available_Quantity := Mrp_Atp_Pub.number_arr(NULL);
175 l_atp_table.Requested_Date_Quantity := Mrp_Atp_Pub.number_arr(NULL);
176 l_atp_table.Group_Ship_Date := Mrp_Atp_Pub.date_arr(NULL);
177 l_atp_table.Vendor_Id := Mrp_Atp_Pub.number_arr(NULL);
178 l_atp_table.Vendor_Site_Id := Mrp_Atp_Pub.number_arr(NULL);
179 l_atp_table.Insert_Flag := Mrp_Atp_Pub.number_arr(NULL);
180 l_atp_table.Error_Code := Mrp_Atp_Pub.number_arr(NULL);
181 l_atp_table.Message := Mrp_Atp_Pub.char2000_arr(NULL);
182
183 IF (l_log_statement >= l_log_current_level) THEN
184 fnd_log.string(l_log_statement,
185 L_DEBUG_KEY,
186 'Inventory Item Id : '||l_atp_table.Inventory_Item_Id(1));
187 fnd_log.string(l_log_statement,
188 L_DEBUG_KEY,
189 'Requested Date : '||l_atp_table.Requested_ship_Date(1));
190 fnd_log.string(l_log_statement,
191 L_DEBUG_KEY,
192 'Calling Module : '||l_atp_table.Calling_Module(1));
193 fnd_log.string(l_log_statement,
194 L_DEBUG_KEY,
195 'Organization Id : '||l_atp_table.Source_Organization_id(1));
196 fnd_log.string(l_log_statement,
197 L_DEBUG_KEY,
198 'Quantity Ordered : '||l_atp_table.Quantity_Ordered(1));
199 fnd_log.string(l_log_statement,
200 L_DEBUG_KEY,
201 'Action : '||l_atp_table.Action(1));
202 END IF;
203
204 IF (l_log_statement >= l_log_current_level) THEN
205 fnd_log.string(l_log_statement,
206 L_DEBUG_KEY,
207 'Before calling Mrp Atp Pub.Call_ATP');
208 END IF;
209
210 -- call atp module
211 Mrp_Atp_Pub.Call_ATP
212 (l_session_id,
213 l_atp_table,
214 x_atp_table,
215 x_atp_supply_demand,
216 x_atp_period,
217 x_atp_details,
218 l_return_status,
219 l_msg_data,
220 l_msg_count);
221
222 IF (l_log_statement >= l_log_current_level) THEN
223 fnd_log.string(l_log_statement,
224 L_DEBUG_KEY,
225 'After calling Mrp Atp Pub.Call_ATP. Return Status : '|| l_return_status);
226 END IF;
227
228 -- Check Error Message stack.
229 IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
230 l_msg_count := FND_MSG_PUB.count_msg;
231 IF l_msg_count > 0 THEN
232 RAISE FND_API.G_EXC_ERROR;
233 END IF;
234 END IF;
235
236 MSC_ATP_GLOBAL.Extend_ATP(x_atp_table, x_return_status);
237
238 IF (l_log_statement >= l_log_current_level) THEN
239 fnd_log.string(l_log_statement,
240 L_DEBUG_KEY,
241 'Inventory Item Id : '||x_atp_table.Inventory_Item_Id(1));
242 fnd_log.string(l_log_statement,
243 L_DEBUG_KEY,
244 'Organization Id : '||x_atp_table.Source_Organization_Id(1));
245 fnd_log.string(l_log_statement,
246 L_DEBUG_KEY,
247 'Organization Code : '||x_atp_table.Source_Organization_code(1));
248 fnd_log.string(l_log_statement,
249 L_DEBUG_KEY,
250 'Quantity Ordered : '||x_atp_table.Quantity_Ordered(1));
251 fnd_log.string(l_log_statement,
252 L_DEBUG_KEY,
253 'Requested Ship Date : '||x_atp_table.Requested_Ship_Date(1));
254 fnd_log.string(l_log_statement,
255 L_DEBUG_KEY,
256 'Requested Arrival Date : '||x_atp_table.Requested_arrival_Date(1));
257 fnd_log.string(l_log_statement,
258 L_DEBUG_KEY,
259 'Arrival Date : '||x_atp_table.Arrival_Date(1));
260 fnd_log.string(l_log_statement,
261 L_DEBUG_KEY,
262 'Available Quantity : '||x_atp_table.Available_Quantity(1));
263 fnd_log.string(l_log_statement,
264 L_DEBUG_KEY,
265 'Requested Date Quantity : '||x_atp_table.Requested_Date_Quantity(1));
266 fnd_log.string(l_log_statement,
267 L_DEBUG_KEY,
268 'Error Code : '||x_atp_table.Error_Code(1));
269 fnd_log.string(l_log_statement,
270 L_DEBUG_KEY,
271 'Pub Message : '||x_atp_table.Message(1));
272 END IF;
273
274 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
275
276 MSC_ATP_GLOBAL.Extend_ATP(x_atp_table, l_return_status);
277
278 x_available_date := to_char(x_atp_table.Ship_Date(1));
279 --
280 MSC_ATP_GLOBAL.Extend_ATP(x_atp_table, l_return_status);
281
282 IF x_atp_table.Error_code(1) IN (0,52,53) THEN
283
284 MSC_ATP_GLOBAL.Extend_ATP(l_atp_table, x_return_status);
285
286 x_available_qty := trunc(x_atp_table.Available_Quantity(1));
287 x_error_code := x_atp_table.Error_code(1);
288
289 IF (l_log_statement >= l_log_current_level) THEN
290 fnd_log.string(l_log_statement,
291 L_DEBUG_KEY,
292 'After Calling Mrp Atp Pub. Error Code = ' || x_error_code ||
293 ', Available Quantity : '||x_available_qty);
294 END IF;
295
296 --Get from mfg lookups
297 OPEN Error_Message_Cur(x_error_code);
298 FETCH Error_Message_Cur INTO x_error_message;
299 CLOSE Error_Message_Cur;
300
301 IF (l_log_statement >= l_log_current_level) THEN
302 fnd_log.string(l_log_statement,
303 L_DEBUG_KEY,
304 'After Calling Mrp Atp Pub Error Message : '||x_error_message);
305 END IF;
306 ELSE
307 MSC_ATP_GLOBAL.Extend_ATP(x_atp_table, l_return_status);
308
309 IF (l_log_statement >= l_log_current_level) THEN
310 fnd_log.string(l_log_statement,
311 L_DEBUG_KEY,
312 'After calling MSC EXTEND');
313 END IF;
314
315 MSC_ATP_GLOBAL.Extend_ATP(l_atp_table, x_return_status);
316 x_error_code := x_atp_table.Error_code(1);
317
318 x_available_qty := trunc(x_atp_table.Available_Quantity(1));
319
320 IF (l_log_statement >= l_log_current_level) THEN
321 fnd_log.string(l_log_statement,
322 L_DEBUG_KEY,
323 'After ERROR CODE : '||x_error_code);
324 END IF;
325 --Get from mfg lookups
326 OPEN Error_Message_Cur(x_error_code);
327 FETCH Error_Message_Cur INTO x_error_message;
328 CLOSE Error_Message_Cur;
329
330 IF (l_log_statement >= l_log_current_level) THEN
331 fnd_log.string(l_log_statement,
332 L_DEBUG_KEY,
333 'Interface Error Message from mfg lookups : '||x_error_message);
334
335 END IF;
336 --
337 END IF;--Error code
338 END IF;
339
340 IF (l_log_statement >= l_log_current_level) THEN
341 fnd_log.string(l_log_statement,
342 L_DEBUG_KEY,
343 'Derived from Mrp Atp Pub Available Quantity : '||x_available_qty);
344 END IF;
345
346 -- Check Error Message stack.
347 IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
348 l_msg_count := FND_MSG_PUB.count_msg;
349 IF l_msg_count > 0 THEN
350 RAISE FND_API.G_EXC_ERROR;
351 END IF;
352 END IF;
353
354 IF (l_log_procedure >= l_log_current_level) THEN
355 fnd_log.string(l_log_procedure,
356 L_DEBUG_KEY ||'.end',
357 'At the end of PL SQL procedure. Return Status = ' || x_return_status);
358 END IF;
359
360 EXCEPTION
361 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
362 ROLLBACK TO check_availability;
363 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
364 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
365 p_count => l_msg_count,
366 p_data => l_msg_data);
367
368 WHEN FND_API.G_EXC_ERROR THEN
369 ROLLBACK TO check_availability;
370 X_return_status := FND_API.G_RET_STS_ERROR;
371 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
372 p_count => l_msg_count,
373 p_data => l_msg_data);
374
375 WHEN OTHERS THEN
376 ROLLBACK TO check_availability;
377 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
378 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
379 THEN
380 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_MATRL_AVAL_PVT',
381 p_procedure_name => 'CHECK_AVAILABILITY',
382 p_error_text => SUBSTR(SQLERRM,1,240));
383 END IF;
384 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
385 p_count => l_msg_count,
386 p_data => l_msg_data);
387
388 END Check_Availability;
389
390 -- Procedure name : Check_Material_Aval
391 -- Type : Private
392 -- Function : This procedure calls ATP to check inventory item is available
393 -- for Routine jobs derived requested quantity and task start date
394 -- Pre-reqs :
395 -- Parameters :
396 --
397 -- Standard IN Parameters :
398 -- p_api_version IN NUMBER Required
399 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
400 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
401 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
402 -- Based on this flag, the API will set the default attributes.
403 -- p_module_type In VARCHAR2 Default NULL
404 -- This will be null.
405 -- Standard OUT Parameters :
406 -- x_return_status OUT VARCHAR2 Required
407 -- x_msg_count OUT NUMBER Required
408 -- x_msg_data OUT VARCHAR2 Required
409 --
410 -- Check_Material_Aval Parameters :
411 -- p_x_material_avl_tbl IN OUT NOCOPY Material_Availability_Tbl,Required
412 -- List of item attributes associated to visit task
413 --
414 PROCEDURE Check_Material_Aval (
415 p_api_version IN NUMBER,
416 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
417 p_commit IN VARCHAR2 := FND_API.g_false,
418 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
419 p_module_type IN VARCHAR2 := 'JSP',
420 p_x_material_avl_tbl IN OUT NOCOPY ahl_ltp_matrl_aval_pub.Material_Availability_Tbl,
421 x_return_status OUT NOCOPY VARCHAR2,
422 x_msg_count OUT NOCOPY NUMBER,
423 x_msg_data OUT NOCOPY VARCHAR2
424 )
425 IS
426 -- Check for visit is scheduled
427 CURSOR Check_Sch_Visit_cur (c_visit_id IN NUMBER) IS
428 SELECT 1 FROM ahl_visits_b
429 WHERE visit_id = c_visit_id
430 AND (organization_id IS NULL
431 OR department_id IS NULL
432 OR start_date_time IS NULL);
433
434 CURSOR Schedule_Matrl_cur (C_SCH_MAT_ID IN NUMBER) IS
435 --Added by sowsubra - status needs be fetched
436 SELECT scheduled_material_id,uom,status,
437 organization_id,visit_task_id
438 FROM ahl_schedule_materials
439 WHERE scheduled_material_id = C_SCH_MAT_ID;
440
441 CURSOR Item_Des_cur(c_item_id IN NUMBER, c_org_id IN NUMBER) IS
442 SELECT CONCATENATED_SEGMENTS
443 FROM mtl_system_items_kfv
444 WHERE inventory_item_id = c_item_id
445 AND organization_id = c_org_id;
446
447 l_api_name CONSTANT VARCHAR2(30) := 'CHECK_MATERIAL_AVAL';
448 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
449 l_api_version CONSTANT NUMBER := 1.0;
450 l_return_status VARCHAR2(1);
451 l_msg_data VARCHAR2(2000);
452 l_msg_count NUMBER;
453 l_dummy NUMBER;
454 l_available_quantity NUMBER;
455 l_available_date DATE;
456 l_Schedule_Matrl_Rec Schedule_Matrl_cur%ROWTYPE;
457 l_error_code NUMBER;
458 l_error_message VARCHAR2(2000);
459
460 BEGIN
461 IF (l_log_procedure >= l_log_current_level) THEN
462 fnd_log.string(l_log_procedure,
463 L_DEBUG_KEY ||'.begin',
464 'At the start of PL SQL procedure.');
465 END IF;
466 -- dbms_output.put_line( 'start private API:');
467
468 --------------------Initialize ----------------------------------
469 -- Standard Start of API savepoint
470 SAVEPOINT check_material_aval;
471 -- Standard call to check for call compatibility.
472 IF FND_API.to_boolean(p_init_msg_list)
473 THEN
474 FND_MSG_PUB.initialize;
475 END IF;
476 -- Initialize API return status to success
477 x_return_status := FND_API.G_RET_STS_SUCCESS;
478 -- Initialize message list if p_init_msg_list is set to TRUE.
479 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
480 p_api_version,
481 l_api_name,G_PKG_NAME)
482 THEN
483 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
484 END IF;
485
486 IF (l_log_statement >= l_log_current_level)THEN
487 fnd_log.string(l_log_statement,
488 L_DEBUG_KEY,
489 'Request for Check Material Availability for Viist item ID : ' ||
490 p_x_material_avl_tbl(1).visit_id);
491 END IF;
492
493 --Validation for schedule visit
494 OPEN Check_Sch_Visit_cur(p_x_material_avl_tbl(1).visit_id);
495 FETCH Check_Sch_Visit_cur INTO l_dummy;
496 IF Check_Sch_Visit_cur%FOUND THEN
497 Fnd_Message.SET_NAME('AHL','AHL_VISIT_UNSCHEDULED');
498 Fnd_Msg_Pub.ADD;
499 CLOSE Check_Sch_Visit_cur;
500 RAISE Fnd_Api.G_EXC_ERROR;
501 END IF;
502 CLOSE Check_Sch_Visit_cur;
503 --
504 IF (l_log_statement >= l_log_current_level)THEN
505 fnd_log.string(l_log_statement,
506 L_DEBUG_KEY,
507 'Request for Check Material Availability for Material Records : ' ||
508 p_x_material_avl_tbl.COUNT);
509 END IF;
510 --
511 IF p_x_material_avl_tbl.COUNT > 0 THEN
512 FOR i IN p_x_material_avl_tbl.FIRST..p_x_material_avl_tbl.LAST
513 LOOP
514 IF (l_log_statement >= l_log_current_level) THEN
515 fnd_log.string(l_log_statement,
516 L_DEBUG_KEY,
517 'Check Material Availability for Inventory Item Id : ' ||
518 p_x_material_avl_tbl(i).inventory_item_id ||
519 ', Schedule Material Id : ' ||
520 p_x_material_avl_tbl(i).schedule_material_id);
521 END IF;
522 --Check for schedule mat rec
523 OPEN Schedule_Matrl_cur(p_x_material_avl_tbl(i).schedule_material_id);
524 FETCH Schedule_Matrl_cur INTO l_Schedule_Matrl_Rec;
525 IF Schedule_Matrl_cur%NOTFOUND THEN
526 Fnd_Message.SET_NAME('AHL','AHL_LTP_ORG_ID_NOT_EXISTS');
527 Fnd_Msg_Pub.ADD;
528 CLOSE Schedule_Matrl_cur;
529 RAISE Fnd_Api.G_EXC_ERROR;
530 END IF;
531 CLOSE Schedule_Matrl_cur;
532
533 --Added by sowsubra - starts
534 IF l_Schedule_Matrl_Rec.status = 'IN-SERVICE' THEN
535 Fnd_Message.SET_NAME('AHL','AHL_MAT_STS_INSERVICE');
536 Fnd_Msg_Pub.ADD;
537 RAISE Fnd_Api.G_EXC_ERROR;
538 END IF;
539 --Added by sowsubra - ends
540
541 --Get Item description
542 OPEN Item_Des_Cur(p_x_material_avl_tbl(i).inventory_item_id,
543 l_Schedule_Matrl_Rec.organization_id);
544 FETCH Item_Des_Cur INTO p_x_material_avl_tbl(i).item;
545 CLOSE Item_Des_Cur;
546
547 IF (l_log_statement >= l_log_current_level) THEN
548 fnd_log.string(l_log_statement,
549 L_DEBUG_KEY,
550 'Before calling Check Availability');
551 END IF;
552
553 Check_Availability (
554 p_calling_module => 867, --fnd_global.prog_appl_id,
555 p_inventory_item_id => p_x_material_avl_tbl(i).inventory_item_id ,
556 p_item_description => p_x_material_avl_tbl(i).item,
557 p_quantity_required => p_x_material_avl_tbl(i).quantity,
558 p_organization_id => l_Schedule_Matrl_Rec.organization_id,
559 p_uom => l_Schedule_Matrl_Rec.uom,
560 p_requested_date => p_x_material_avl_tbl(i).req_arrival_date,
561 p_schedule_material_id => p_x_material_avl_tbl(i).schedule_material_id,
562 x_available_qty => l_available_quantity,
563 x_available_date => l_available_date,
564 x_error_code => l_error_code,
565 x_error_message => l_error_message,
566 x_return_status => l_return_status);
567 --
568 IF (l_log_statement >= l_log_current_level) THEN
569 fnd_log.string(l_log_statement,
570 L_DEBUG_KEY,
571 'After calling Check Availability, Return Status : '|| l_return_status);
572 END IF;
573
574 -- Check Error Message stack.
575 IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
576 l_msg_count := FND_MSG_PUB.count_msg;
577 IF l_msg_count > 0 THEN
578 RAISE FND_API.G_EXC_ERROR;
579 END IF;
580 END IF;
581 --Assign derived values
582 p_x_material_avl_tbl(i).quantity_available:= l_available_quantity;
583 -- anraj : commented these lines of code
584 /*p_x_material_avl_tbl(i).inventory_item_id := p_x_material_avl_tbl(i).inventory_item_id;
585 p_x_material_avl_tbl(i).quantity := p_x_material_avl_tbl(i).quantity;
586 p_x_material_avl_tbl(i).visit_task_id := p_x_material_avl_tbl(i).visit_task_id;
587 p_x_material_avl_tbl(i).task_name := p_x_material_avl_tbl(i).task_name;
588 p_x_material_avl_tbl(i).req_arrival_date := p_x_material_avl_tbl(i).req_arrival_date;
589 p_x_material_avl_tbl(i).uom := p_x_material_avl_tbl(i).uom;
590 */
591 p_x_material_avl_tbl(i).error_code := l_error_code;
592 p_x_material_avl_tbl(i).error_message := l_error_message;
593 --
594 IF (l_log_statement >= l_log_current_level) THEN
595 fnd_log.string(l_log_statement,
596 L_DEBUG_KEY,
597 'Inventory Item Id : ' || p_x_material_avl_tbl(i).inventory_item_id);
598 fnd_log.string(l_log_statement,
599 L_DEBUG_KEY,
600 'Quantity Available : ' || p_x_material_avl_tbl(i).quantity_available);
601 fnd_log.string(l_log_statement,
602 L_DEBUG_KEY,
603 'Quantity Required : ' || p_x_material_avl_tbl(i).quantity);
604 fnd_log.string(l_log_statement,
605 L_DEBUG_KEY,
606 'Visit Task Id: ' || p_x_material_avl_tbl(i).visit_task_id);
607 fnd_log.string(l_log_statement,
608 L_DEBUG_KEY,
609 'Error Code: ' || l_error_code);
610 fnd_log.string(l_log_statement,
611 L_DEBUG_KEY,
612 'Error Message: ' || l_error_message);
613 END IF;
614 END LOOP;
615 END IF;
616
617 -- Check Error Message stack.
618 IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
619 l_msg_count := FND_MSG_PUB.count_msg;
620 IF l_msg_count > 0 THEN
621 RAISE FND_API.G_EXC_ERROR;
622 END IF;
623 END IF;
624
625 IF (l_log_procedure >= l_log_current_level) THEN
626 fnd_log.string(l_log_procedure,
627 L_DEBUG_KEY ||'.end',
628 'At the end of PL SQL procedure. Return Status = ' || x_return_status);
629 END IF;
630
631 EXCEPTION
632 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
633 ROLLBACK TO check_material_aval;
634 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
635 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
636 p_count => l_msg_count,
637 p_data => l_msg_data);
638
639 WHEN FND_API.G_EXC_ERROR THEN
640 ROLLBACK TO check_material_aval;
641 X_return_status := FND_API.G_RET_STS_ERROR;
642 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
643 p_count => l_msg_count,
644 p_data => l_msg_data);
645
646 WHEN OTHERS THEN
647 ROLLBACK TO check_material_aval;
648 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
649 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
650 THEN
651 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_MATRL_AVAL_PVT',
652 p_procedure_name => 'CHECK_MATERIAL_AVAL',
653 p_error_text => SUBSTR(SQLERRM,1,240));
654 END IF;
655 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
656 p_count => l_msg_count,
657 p_data => l_msg_data);
658
659 END Check_Material_Aval;
660
661 --
662 -- Procedure name : Get_Visit_Task_Materials
663 -- Type : Private
664 -- Function : This procedure derives material information associated to scheduled
665 -- visit, which are defined at Route Operation level
666 -- Pre-reqs :
667 -- Parameters :
668 --
669 -- Standard IN Parameters :
670 -- p_api_version IN NUMBER Required
671 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
672 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
673 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
674 -- Based on this flag, the API will set the default attributes.
675 -- p_module_type In VARCHAR2 Default NULL
676 -- This will be null.
677 -- Standard OUT Parameters :
678 -- x_return_status OUT VARCHAR2 Required
679 -- x_msg_count OUT NUMBER Required
680 -- x_msg_data OUT VARCHAR2 Required
681 --
682 -- Get_Visit_Task_Materials :
683 -- p_visit_id IN NUMBER,Required
684 -- x_task_req_matrl_tbl OUT NOCOPY Task_Req_Matrl_Tbl,
685 --
686 PROCEDURE Get_Visit_Task_Materials (
687 p_api_version IN NUMBER,
688 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
689 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
690 p_visit_id IN NUMBER,
691 x_task_req_matrl_tbl OUT NOCOPY ahl_ltp_matrl_aval_pub.task_req_matrl_tbl,
692 x_return_status OUT NOCOPY VARCHAR2,
693 x_msg_count OUT NOCOPY NUMBER,
694 x_msg_data OUT NOCOPY VARCHAR2)
695 IS
696 --
697 -- changed the select statement to add one more column
698 CURSOR Visit_Task_Matrl_Cur(C_VISIT_ID IN NUMBER) IS
699 SELECT schedule_material_id,
700 object_version_number,
701 visit_id,
702 visit_task_id,
703 visit_task_name,
704 inventory_item_id,
705 item_number, --Modified by rnahata for ER 6391157, ahl_visit_task_matrl_v definition changed
706 requested_quantity,
707 requested_date,
708 scheduled_date,
709 scheduled_quantity,
710 uom,
711 sales_order_line_id,
712 task_status_code,
713 meaning
714 FROM ahl_visit_task_matrl_v, FND_LOOKUP_VALUES_VL
715 WHERE visit_id = C_VISIT_ID
716 AND (requested_quantity <> 0)
717 AND NVL(mat_status,'X') <> 'IN-SERVICE' --Added by sowsubra
718 AND LOOKUP_TYPE(+) = 'AHL_VWP_TASK_STATUS'
719 AND LOOKUP_code = task_status_code;
720 c_Visit_Task_Matrl_Rec Visit_Task_Matrl_Cur%ROWTYPE;
721
722 --Standard local variables
723 l_api_name CONSTANT VARCHAR2(30) := 'Get_Visit_Task_Materials';
724 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
725 l_api_version CONSTANT NUMBER := 1.0;
726 l_return_status VARCHAR2(1);
727 l_msg_data VARCHAR2(2000);
728 l_msg_count NUMBER;
729 --
730 i NUMBER;
731 BEGIN
732
733 IF (l_log_procedure >= l_log_current_level) THEN
734 fnd_log.string(l_log_procedure,
735 L_DEBUG_KEY ||'.begin',
736 'At the start of PL SQL procedure. Visit Id = ' || p_visit_id);
737 END IF;
738 -- Standard Start of API savepoint
739 SAVEPOINT Get_Visit_Task_Materials;
740 -- Initialize message list if p_init_msg_list is set to TRUE.
741 IF FND_API.to_boolean(p_init_msg_list) THEN
742 FND_MSG_PUB.initialize;
743 END IF;
744 -- Initialize API return status to success
745 x_return_status := FND_API.G_RET_STS_SUCCESS;
746 -- Standard call to check for call compatibility.
747 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
748 l_api_version,
749 l_api_name,G_PKG_NAME)
750 THEN
751 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
752 END IF;
753
754 --
755 IF p_visit_id IS NOT NULL THEN
756 --
757 OPEN Visit_Task_Matrl_Cur(p_visit_id);
758 i := 0;
759 LOOP
760 FETCH Visit_Task_Matrl_Cur INTO c_Visit_Task_Matrl_Rec;
761 EXIT WHEN Visit_Task_Matrl_Cur%NOTFOUND;
762 --
763 x_task_req_matrl_tbl(i).schedule_material_id := c_Visit_Task_Matrl_Rec.schedule_material_id;
764 x_task_req_matrl_tbl(i).object_version_number := c_Visit_Task_Matrl_Rec.object_version_number;
765 x_task_req_matrl_tbl(i).visit_task_id := c_Visit_Task_Matrl_Rec.visit_task_id;
766 x_task_req_matrl_tbl(i).task_name := c_Visit_Task_Matrl_Rec.visit_task_name;
767 -- anraj : added columns TASK_STATUS_CODE and TASK_STATUS_MEANING , for Material Availabilty UI
768 x_task_req_matrl_tbl(i).task_status_code := c_Visit_Task_Matrl_Rec.task_status_code;
769 x_task_req_matrl_tbl(i).task_status_meaning := c_Visit_Task_Matrl_Rec.meaning;
770 x_task_req_matrl_tbl(i).inventory_item_id := c_Visit_Task_Matrl_Rec.inventory_item_id;
771 x_task_req_matrl_tbl(i).item := c_Visit_Task_Matrl_Rec.item_number;
772 x_task_req_matrl_tbl(i).req_arrival_date := c_Visit_Task_Matrl_Rec.requested_date;
773 x_task_req_matrl_tbl(i).uom_code := c_Visit_Task_Matrl_Rec.uom;
774 x_task_req_matrl_tbl(i).planned_order := c_Visit_Task_Matrl_Rec.sales_order_line_id;
775 x_task_req_matrl_tbl(i).quantity := c_Visit_Task_Matrl_Rec.requested_quantity;
776 x_task_req_matrl_tbl(i).scheduled_date := c_Visit_Task_Matrl_Rec.scheduled_date;
777 i := i + 1;
778 END LOOP;
779 CLOSE Visit_Task_Matrl_Cur;
780 END IF;
781
782 -- Check Error Message stack.
783 IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
784 l_msg_count := FND_MSG_PUB.count_msg;
785 IF l_msg_count > 0 THEN
786 RAISE FND_API.G_EXC_ERROR;
787 END IF;
788 END IF;
789
790 IF (l_log_procedure >= l_log_current_level) THEN
791 fnd_log.string(l_log_procedure,
792 L_DEBUG_KEY ||'.end',
793 'At the end of PL SQL procedure. Return Status = ' || x_return_status);
794 END IF;
795
796 EXCEPTION
797 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
798 ROLLBACK TO Get_Visit_Task_Materials;
799 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
800 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
801 p_count => l_msg_count,
802 p_data => l_msg_data);
803 WHEN FND_API.G_EXC_ERROR THEN
804 ROLLBACK TO Get_Visit_Task_Materials;
805 X_return_status := FND_API.G_RET_STS_ERROR;
806 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
807 p_count => l_msg_count,
808 p_data => l_msg_data);
809 WHEN OTHERS THEN
810 ROLLBACK TO Get_Visit_Task_Materials;
811 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
812 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
813 THEN
814 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_MATRL_AVAL_PVT',
815 p_procedure_name => 'GET_VISIT_TASK_MATERIALS',
816 p_error_text => SUBSTR(SQLERRM,1,240));
817 END IF;
818 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
819 p_count => l_msg_count,
820 p_data => l_msg_data);
821 END Get_Visit_Task_Materials;
822 --
823 PROCEDURE Extend_ATP
824 (p_atp_table IN OUT NOCOPY MRP_ATP_PUB.ATP_Rec_Typ,
825 x_return_status OUT NOCOPY VARCHAR2)
826 IS
827
828 L_API_NAME CONSTANT VARCHAR2(30) := 'Extend_ATP';
829 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
830
831 BEGIN
832 IF (l_log_procedure >= l_log_current_level) THEN
833 fnd_log.string(l_log_procedure,
834 L_DEBUG_KEY ||'.begin',
835 'At the start of PL SQL procedure.');
836 END IF;
837
838 x_return_status := FND_API.G_RET_STS_SUCCESS;
839 --
840 p_atp_table.Row_Id.Extend;
841 P_ATP_TABLE.INSTANCE_ID.EXTEND;
842 P_ATP_TABLE.INVENTORY_ITEM_ID.EXTEND;
843 P_ATP_TABLE.INVENTORY_ITEM_NAME.EXTEND;
844 P_ATP_TABLE.SOURCE_ORGANIZATION_ID.EXTEND;
845 p_atp_table.Source_Organization_Code.Extend;
846 p_atp_table.Organization_Id.Extend;
847 P_ATP_TABLE.IDENTIFIER.EXTEND;
848 p_atp_table.Scenario_Id.Extend;
849 P_ATP_TABLE.DEMAND_SOURCE_TYPE.EXTEND;
850 P_ATP_TABLE.CALLING_MODULE.EXTEND;
851 p_atp_table.Customer_Id.Extend;
852 p_atp_table.Customer_Site_Id.Extend;
853 p_atp_table.Destination_Time_Zone.Extend;
854 P_ATP_TABLE.QUANTITY_ORDERED.EXTEND;
855 P_ATP_TABLE.QUANTITY_UOM.EXTEND;
856 P_ATP_TABLE.REQUESTED_SHIP_DATE.EXTEND;
857 p_atp_table.Requested_Arrival_Date.Extend;
858 p_atp_table.Earliest_Acceptable_Date.Extend;
859 p_atp_table.Latest_Acceptable_Date.Extend;
860 p_atp_table.Delivery_Lead_Time.Extend;
861 p_atp_table.Freight_Carrier.Extend;
862 p_atp_table.Ship_Method.Extend;
863 p_atp_table.Demand_Class.Extend;
864 p_atp_table.Ship_Set_Name.Extend;
865 p_atp_table.Arrival_Set_Name.Extend;
866 p_atp_table.Override_Flag.Extend;
867 P_ATP_TABLE.ACTION.EXTEND;
868 p_atp_table.Ship_Date.Extend;
869 p_atp_table.Available_Quantity.Extend;
870 P_ATP_TABLE.ORDER_NUMBER.EXTEND;
871 p_atp_table.Requested_Date_Quantity.Extend;
872 p_atp_table.Group_Ship_Date.Extend;
873 p_atp_table.Group_Arrival_Date.Extend;
874 p_atp_table.Vendor_Id.Extend;
875 p_atp_table.Vendor_Name.Extend;
876 p_atp_table.Vendor_Site_Id.Extend;
877 p_atp_table.Vendor_Site_Name.Extend;
878 p_atp_table.Insert_Flag.Extend;
879 p_atp_table.OE_Flag.Extend;
880 p_atp_table.Error_Code.Extend;
881 p_atp_table.Message.Extend;
882 p_atp_table.req_item_req_date_qty.extend;
883 p_atp_table.req_item_available_date.extend;
884 p_atp_table.req_item_available_date_qty.extend;
885
886 IF (l_log_procedure >= l_log_current_level) THEN
887 fnd_log.string(l_log_procedure,
888 L_DEBUG_KEY ||'.end',
889 'At the end of PL SQL procedure. Return Status = ' || x_return_status);
890 END IF;
891
892 END Extend_ATP;
893 --
894 -- Start of Comments --
895 -- Procedure name : Call_ATP
896 -- Type : Public
897 -- Function : This procedure calls ATP to schedule planned materials
898 -- for Routine jobs derived requested quantity and task start date
899 -- Pre-reqs :
900 -- Parameters :
901 --
902 -- Standard IN Parameters :
903 -- p_api_version IN NUMBER Required
904 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
905 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
906 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
907 -- Based on this flag, the API will set the default attributes.
908 -- p_module_type In VARCHAR2 Default NULL
909 -- This will be null.
910 -- Standard OUT Parameters :
911 -- x_return_status OUT VARCHAR2 Required
912 -- x_msg_count OUT NUMBER Required
913 -- x_msg_data OUT VARCHAR2 Required
914 --
915 -- Schedule_Planned_Matrls Parameters :
916 -- p_x_planned_matrls_tbl IN OUT NOCOPY Planned_Matrls_Tbl,Required
917 -- List of item attributes associated to visit task
918 --
919 PROCEDURE Call_ATP (
920 p_api_version IN NUMBER,
921 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
922 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
923 p_x_planned_matrl_tbl IN OUT NOCOPY AHL_LTP_MATRL_AVAL_PUB.Planned_Matrl_Tbl,
924 x_return_status OUT NOCOPY VARCHAR2,
925 x_msg_count OUT NOCOPY NUMBER,
926 x_msg_data OUT NOCOPY VARCHAR2)
927 IS
928
929 CURSOR Error_Message_Cur(c_error_code IN NUMBER)
930 IS
931 SELECT meaning
932 FROM mfg_lookups
933 WHERE lookup_type = 'MTL_DEMAND_INTERFACE_ERRORS'
934 AND lookup_code = C_Error_Code;
935
936 CURSOR Planned_Order_Cur(c_sch_mat_id IN NUMBER) IS
937 -- yazhou 12-May-2006 starts
938 -- Bug fix#5223772
939 /*
940 -- Changed for fixing perf bug:4919540
941 select DECODE( SIGN( trunc(scheduled_date) - trunc(requested_date)),1,scheduled_date,null) scheduled_date,
942 scheduled_quantity
943 from ahl_schedule_materials asmt,
944 AHL_VISIT_TASKS_B tsk
945 where TSK.VISIT_ID = ASMT.VISIT_ID
946 AND TSK.VISIT_TASK_ID = ASMT.VISIT_TASK_ID
947 AND NVL(ASMT.STATUS,' ') <> 'DELETED'
948 AND NVL(TSK.STATUS_CODE,'X') <> 'DELETED'
949 AND scheduled_material_id = c_sch_mat_id;
950 */
951 SELECT scheduled_date ,
952 status, --Added by sowsubra
953 scheduled_quantity
954 FROM ahl_schedule_materials asmt,
955 AHL_VISIT_TASKS_B tsk
956 WHERE TSK.VISIT_ID = ASMT.VISIT_ID
957 AND TSK.VISIT_TASK_ID = ASMT.VISIT_TASK_ID
958 AND NVL(ASMT.STATUS,' ') <> 'DELETED'
959 AND NVL(TSK.STATUS_CODE,'X') <> 'DELETED'
960 AND scheduled_material_id = c_sch_mat_id
961 AND scheduled_date is not null
962 AND scheduled_date >= requested_date;
963 --yazhou 12-May-2006 ends
964
965 CURSOR Order_Number_Cur(c_visit_task_id IN NUMBER) IS
966 SELECT visit_number||visit_task_number Order_Number
967 FROM ahl_visit_tasks_v
968 WHERE visit_task_id = c_visit_task_id;
969
970 CURSOR Instance_Id_Cur IS
971 SELECT instance_id
972 FROM MRP_AP_APPS_INSTANCES;
973
974 --Standard local variables
975 l_api_name CONSTANT VARCHAR2(30) := 'CALL_ATP';
976 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
977 l_api_version CONSTANT NUMBER := 1.0;
978 l_return_status VARCHAR2(1);
979 l_msg_data VARCHAR2(2000);
980 l_msg_count NUMBER;
981 --Varibales to call mrp atp pub
982 l_session_id NUMBER;
983 l_instance_id NUMBER;
984 l_atp_table Mrp_Atp_Pub.ATP_Rec_Typ;
985 x_atp_table Mrp_Atp_Pub.ATP_Rec_Typ;
986 x_atp_supply_demand Mrp_Atp_Pub.ATP_Supply_Demand_Typ;
987 x_atp_period Mrp_Atp_Pub.ATP_Period_Typ;
988 x_atp_details Mrp_Atp_Pub.ATP_Details_Typ;
989 l_temp_atp_table AHL_LTP_MATRL_AVAL_PUB.Planned_Matrl_Tbl;
990 l_error_msg VARCHAR2(2000);
991 l_error_message VARCHAR2(80);
992 l_planned_matrl_tbl AHL_LTP_MATRL_AVAL_PUB.Planned_Matrl_Tbl := p_x_planned_matrl_tbl;
993 l_scheduled_date DATE;
994 l_scheduled_quantity NUMBER;
995 l_Planned_Order_Rec Planned_Order_Cur%ROWTYPE;
996 --Required to capture available quantity and scheduled quanity
997 l_temp_planned_table AHL_LTP_MATRL_AVAL_PUB.Planned_Matrl_Tbl;
998 l_order_number NUMBER;
999
1000 BEGIN
1001
1002 IF (l_log_procedure >= l_log_current_level) THEN
1003 fnd_log.string(l_log_procedure,
1004 L_DEBUG_KEY ||'.begin',
1005 'At the start of PL SQL procedure. Number of Records : ' || l_planned_matrl_tbl.COUNT);
1006 END IF;
1007 -- Standard Start of API savepoint
1008 SAVEPOINT Call_ATP;
1009 -- Initialize message list if p_init_msg_list is set to TRUE.
1010 IF FND_API.to_boolean(p_init_msg_list) THEN
1011 FND_MSG_PUB.initialize;
1012 END IF;
1013 -- Initialize API return status to success
1014 x_return_status := FND_API.G_RET_STS_SUCCESS;
1015 -- Standard call to check for call compatibility.
1016 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
1017 l_api_version,
1018 l_api_name,G_PKG_NAME)
1019 THEN
1020 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1021 END IF;
1022
1023 --Get session id
1024 SELECT MRP_ATP_SCHEDULE_TEMP_S.NEXTVAL
1025 INTO l_session_id FROM DUAL;
1026
1027 IF (l_log_statement >= l_log_current_level) THEN
1028 fnd_log.string(l_log_statement,
1029 L_DEBUG_KEY,
1030 'Session Id : '||l_session_id);
1031 END IF;
1032
1033 --Get instance Id
1034 --Check for Instance Id
1035 OPEN Instance_Id_Cur;
1036 FETCH Instance_Id_Cur INTO l_instance_id;
1037 IF Instance_Id_Cur%NOTFOUND THEN
1038 FND_MESSAGE.Set_Name( 'AHL','AHL_LTP_ATP_INS_ENABLE' );
1039 FND_MSG_PUB.add;
1040 CLOSE Instance_Id_Cur;
1041 RAISE FND_API.G_EXC_ERROR;
1042 END IF;
1043 --
1044 CLOSE Instance_Id_Cur;
1045
1046 IF (l_log_statement >= l_log_current_level) THEN
1047 fnd_log.string(l_log_statement,
1048 L_DEBUG_KEY,
1049 'Instance Id : '||l_instance_id);
1050 END IF;
1051
1052 -- Loop through all the records
1053 FOR i IN l_planned_matrl_tbl.FIRST .. l_planned_matrl_tbl.LAST
1054 LOOP
1055 --
1056 IF l_planned_matrl_tbl.EXISTS(i) THEN
1057 --Call extend Atp
1058 MSC_ATP_GLOBAL.Extend_ATP(l_atp_table, x_return_status);
1059 --
1060 l_atp_table.inventory_item_id(i) := l_planned_matrl_tbl(i).inventory_item_id;
1061 l_atp_table.inventory_item_name(i) := l_planned_matrl_tbl(i).item_description;
1062 l_atp_table.instance_id(i) := l_instance_id;
1063 l_atp_table.source_organization_id(i) := l_planned_matrl_tbl(i).organization_id;
1064 l_atp_table.identifier(i) := l_planned_matrl_tbl(i).schedule_material_id;
1065 l_atp_table.demand_source_type(i) := 100;
1066 l_atp_table.quantity_ordered(i) := l_planned_matrl_tbl(i).required_quantity;
1067 l_atp_table.quantity_UOM(i) := l_planned_matrl_tbl(i).primary_uom_code;
1068 l_atp_table.requested_ship_date(i) := l_planned_matrl_tbl(i).requested_date;
1069 --VERFY WEATHER SCHEDULING OR RESCHEDULING
1070 OPEN Planned_Order_Cur(l_planned_matrl_tbl(i).schedule_material_id);
1071 FETCH Planned_Order_Cur into l_Planned_Order_Rec;
1072 IF Planned_Order_Cur%NOTFOUND THEN
1073 l_atp_table.action(i) := 110;--Scheduling
1074 ELSE
1075 l_atp_table.action(i) := 120;--Rescheduling
1076 l_atp_table.Old_Source_Organization_Id(i) := l_planned_matrl_tbl(i).organization_id;--Rescheduling
1077 END IF;
1078 CLOSE Planned_Order_Cur;
1079
1080 --Added by sowsubra - start
1081 IF l_Planned_Order_Rec.status = 'IN-SERVICE' THEN
1082 Fnd_Message.SET_NAME('AHL','AHL_MAT_STS_INSERVICE');
1083 Fnd_Msg_Pub.ADD;
1084 RAISE Fnd_Api.G_EXC_ERROR;
1085 END IF;
1086 --Added by sowsubra - end
1087
1088 --Get Concatenated visit number, task number
1089 OPEN Order_Number_Cur(l_planned_matrl_tbl(i).visit_task_id);
1090 FETCH Order_Number_Cur INTO l_order_number;
1091 CLOSE Order_Number_Cur;
1092 --Assign to atp record
1093 l_atp_table.order_number(i) := l_order_number;
1094 l_atp_table.calling_module(i) := 867; --fnd_global.prog_appl_id;
1095 --
1096 IF (l_log_statement >= l_log_current_level)THEN
1097 fnd_log.string(l_log_statement,
1098 L_DEBUG_KEY,
1099 'Inventory Item Id : '||l_atp_table.inventory_item_id(i) ||'-'||i);
1100 fnd_log.string(l_log_statement,
1101 L_DEBUG_KEY,
1102 'Inventory Item Name : '||l_atp_table.inventory_item_name(i));
1103 fnd_log.string(l_log_statement,
1104 L_DEBUG_KEY,
1105 'Instance Id : '||l_atp_table.instance_id(i));
1106 fnd_log.string(l_log_statement,
1107 L_DEBUG_KEY,
1108 'Organization Id : '||l_atp_table.source_organization_id(i));
1109 fnd_log.string(l_log_statement,
1110 L_DEBUG_KEY,
1111 'Identifier : '||l_atp_table.identifier(i));
1112 fnd_log.string(l_log_statement,
1113 L_DEBUG_KEY,
1114 'Demand Source Type : '||l_atp_table.demand_source_type(i));
1115 fnd_log.string(l_log_statement,
1116 L_DEBUG_KEY,
1117 'Quantity Ordered : '||l_atp_table.quantity_ordered(i));
1118 fnd_log.string(l_log_statement,
1119 L_DEBUG_KEY,
1120 'Quantity UOM : '||l_atp_table.quantity_uom(i));
1121 fnd_log.string(l_log_statement,
1122 L_DEBUG_KEY,
1123 'Requested Ship Date : '||l_atp_table.requested_ship_date(i));
1124 fnd_log.string(l_log_statement,
1125 L_DEBUG_KEY,
1126 'Action : '||l_atp_table.action(i));
1127 fnd_log.string(l_log_statement,
1128 L_DEBUG_KEY,
1129 'Order Number : '||l_atp_table.order_number(i));
1130 fnd_log.string(l_log_statement,
1131 L_DEBUG_KEY,
1132 'Requested Date Quantity : '||l_atp_table.requested_date_quantity(i));
1133 fnd_log.string(l_log_statement,
1134 L_DEBUG_KEY,
1135 'Calling Module : '||l_atp_table.Calling_module(i));
1136 END IF;
1137 END IF;
1138 END LOOP;
1139
1140 IF (l_log_statement >= l_log_current_level) THEN
1141 fnd_log.string(l_log_statement,
1142 L_DEBUG_KEY,
1143 'Before calling Mrp Atp Pub.Call_ATP. Calling Module count: '||
1144 l_atp_table.Calling_module.count);
1145 END IF;
1146
1147 -- Call ATP to Schedule
1148 MRP_ATP_PUB.CALL_ATP(l_session_id,
1149 l_atp_table,
1150 x_atp_table,
1151 x_atp_supply_demand,
1152 x_atp_period,
1153 x_atp_details,
1154 x_return_status,
1155 x_msg_data,
1156 x_msg_count);
1157
1158 IF (l_log_statement >= l_log_current_level) THEN
1159 fnd_log.string(l_log_statement,
1160 L_DEBUG_KEY,
1161 'After calling Mrp Atp Pub.Call_ATP. Return Status : '|| x_return_status);
1162 END IF;
1163
1164 -- Check Error Message stack.
1165 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1166 l_msg_count := FND_MSG_PUB.count_msg;
1167 IF l_msg_count > 0 THEN
1168 RAISE FND_API.G_EXC_ERROR;
1169 END IF;
1170 END IF;
1171
1172 --Check for error code
1173 IF x_atp_table.Error_Code.COUNT > 0 THEN
1174 FOR i IN x_atp_table.Error_Code.FIRST .. x_atp_table.Error_Code.LAST
1175 LOOP
1176 MSC_ATP_GLOBAL.Extend_ATP(x_atp_table, x_return_status);
1177 IF (x_atp_table.Error_Code.EXISTS(i) AND x_atp_table.error_code(i) <> 0) THEN
1178 IF (l_log_statement >= l_log_current_level)THEN
1179 fnd_log.string(l_log_statement,
1180 L_DEBUG_KEY,
1181 'x_atp_table.error_code(i) : '||x_atp_table.error_code(i));
1182 END IF;
1183 MSC_ATP_GLOBAL.Extend_ATP(x_atp_table, x_return_status);
1184
1185 l_temp_atp_table(i).schedule_material_id := x_atp_table.identifier(i);
1186 l_temp_atp_table(i).item_description := x_atp_table.inventory_item_name(i);
1187 l_temp_atp_table(i).error_code := x_atp_table.error_code(i);
1188 l_temp_atp_table(i).quantity_available := trunc(x_atp_table.available_quantity(i));
1189 l_temp_atp_table(i).item_description := x_atp_table.inventory_item_name(i);
1190 --Get error message
1191 OPEN Error_Message_Cur(l_temp_atp_table(i).error_code);
1192 FETCH Error_Message_Cur INTO l_temp_atp_table(i).error_message;
1193 CLOSE Error_Message_Cur;
1194 ELSE
1195 --Error code is zero update the record
1196 MSC_ATP_GLOBAL.Extend_ATP(x_atp_table, x_return_status);
1197
1198 IF (l_log_statement >= l_log_current_level)THEN
1199 fnd_log.string(l_log_statement,
1200 L_DEBUG_KEY,
1201 'x_atp_table.identifier(i) : '|| x_atp_table.identifier(i));
1202 fnd_log.string(l_log_statement,
1203 L_DEBUG_KEY,
1204 'x_atp_table.available_quantity(i) : '|| x_atp_table.available_quantity(i));
1205 fnd_log.string(l_log_statement,
1206 L_DEBUG_KEY,
1207 'x_atp_table.requested_date_quantity(i) : '|| trunc(x_atp_table.requested_date_quantity(i)));
1208 fnd_log.string(l_log_statement,
1209 L_DEBUG_KEY,
1210 'x_atp_table.ship_date(i) : '|| x_atp_table.ship_date(i));
1211 END IF;
1212 --Assign out parameter
1213 l_temp_atp_table(i).schedule_material_id := x_atp_table.identifier(i);
1214 l_temp_atp_table(i).quantity_available := trunc(x_atp_table.available_quantity(i));
1215 -- l_temp_atp_table(i).scheduled_quantity := trunc(x_atp_table.requested_date_quantity(i));
1216 l_temp_atp_table(i).error_code := x_atp_table.error_code(i);
1217 l_temp_atp_table(i).error_message := 'Successfully Scheduled';
1218 l_temp_atp_table(i).item_description := x_atp_table.inventory_item_name(i);
1219
1220 --yazhou 12-May-2006 starts
1221 --Bug fix #5223772
1222 UPDATE ahl_schedule_materials
1223 SET scheduled_date = x_atp_table.ship_date(i),
1224 scheduled_quantity = l_planned_matrl_tbl(i).required_quantity,
1225 object_version_number = object_version_number + 1
1226 WHERE scheduled_material_id = x_atp_table.identifier(i);
1227 --yazhou 12-May-2006 ends
1228 END IF;
1229 END LOOP;
1230 END IF;
1231 -- Assign to out parameter
1232 IF l_temp_atp_table.COUNT > 0 THEN
1233 FOR i IN l_temp_atp_table.FIRST..l_temp_atp_table.LAST
1234 LOOP
1235 IF (l_log_statement >= l_log_current_level)THEN
1236 fnd_log.string(l_log_statement,
1237 L_DEBUG_KEY,
1238 'Before assign out parameter, Sch Mat ID: '||
1239 l_temp_atp_table(i).schedule_material_id ||
1240 ', Quantity Available: '||
1241 l_temp_atp_table(i).quantity_available ||
1242 ', Scheduled Quantity : '||
1243 l_temp_atp_table(i).scheduled_date ||
1244 ', Error Code : '||
1245 l_temp_atp_table(i).error_code ||
1246 ', Error Message : '||
1247 l_temp_atp_table(i).error_message);
1248 END IF;
1249 --
1250 p_x_planned_matrl_tbl(i).schedule_material_id := l_temp_atp_table(i).schedule_material_id;
1251 p_x_planned_matrl_tbl(i).quantity_available := l_temp_atp_table(i).quantity_available;
1252 -- p_x_planned_matrl_tbl(i).scheduled_quantity := l_temp_atp_table(i).scheduled_quantity;
1253 p_x_planned_matrl_tbl(i).error_code := l_temp_atp_table(i).error_code;
1254 p_x_planned_matrl_tbl(i).error_message := 'For Item '||l_temp_atp_table(i).item_description||', '||l_temp_atp_table(i).error_message;
1255 p_x_planned_matrl_tbl(i).item_description := l_temp_atp_table(i).item_description;
1256 END LOOP;
1257 END IF;
1258
1259 -- Check Error Message stack.
1260 IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
1261 l_msg_count := FND_MSG_PUB.count_msg;
1262 IF l_msg_count > 0 THEN
1263 RAISE FND_API.G_EXC_ERROR;
1264 END IF;
1265 END IF;
1266 --Need to fix error messages
1267
1268 IF (l_log_procedure >= l_log_current_level) THEN
1269 fnd_log.string(l_log_procedure,
1270 L_DEBUG_KEY ||'.end',
1271 'At the end of PL SQL procedure. Return Status = ' || x_return_status);
1272 END IF;
1273 EXCEPTION
1274 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1275 ROLLBACK TO Call_ATP;
1276 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1277 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1278 p_count => l_msg_count,
1279 p_data => l_msg_data);
1280
1281 WHEN FND_API.G_EXC_ERROR THEN
1282 ROLLBACK TO Call_ATP;
1283 X_return_status := FND_API.G_RET_STS_ERROR;
1284 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1285 p_count => l_msg_count,
1286 p_data => l_msg_data);
1287
1288 WHEN OTHERS THEN
1289 ROLLBACK TO Call_ATP;
1290 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1291 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1292 THEN
1293 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_MATRL_AVAL_PVT',
1294 p_procedure_name => 'CALL_ATP',
1295 p_error_text => SUBSTR(SQLERRM,1,240));
1296 END IF;
1297 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1298 p_count => l_msg_count,
1299 p_data => l_msg_data);
1300
1301 END Call_ATP;
1302
1303 -- Start of Comments --
1304 -- Procedure name : Schedule_Planned_Mtrls
1305 -- Type : Public
1306 -- Function : This procedure calls ATP to schedule planned materials
1307 -- for Routine jobs derived requested quantity and task start date
1308 -- Pre-reqs :
1309 -- Parameters :
1310 --
1311 -- Standard IN Parameters :
1312 -- p_api_version IN NUMBER Required
1313 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
1314 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
1315 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
1316 -- Based on this flag, the API will set the default attributes.
1317 -- p_module_type In VARCHAR2 Default NULL
1318 -- This will be null.
1319 -- Standard OUT Parameters :
1320 -- x_return_status OUT VARCHAR2 Required
1321 -- x_msg_count OUT NUMBER Required
1322 -- x_msg_data OUT VARCHAR2 Required
1323 --
1324 -- Schedule_Planned_Matrls Parameters :
1325 -- p_x_planned_matrls_tbl IN OUT NOCOPY Planned_Matrls_Tbl,Required
1326 -- List of item attributes associated to visit task
1327 --
1328 PROCEDURE Schedule_Planned_Matrls (
1329 p_api_version IN NUMBER,
1330 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1331 p_commit IN VARCHAR2 := FND_API.g_false,
1332 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1333 p_x_planned_matrl_tbl IN OUT NOCOPY AHL_LTP_MATRL_AVAL_PUB.Planned_Matrl_Tbl,
1334 x_return_status OUT NOCOPY VARCHAR2,
1335 x_msg_count OUT NOCOPY NUMBER,
1336 x_msg_data OUT NOCOPY VARCHAR2)
1337 IS
1338 CURSOR Sch_Material_Cur (c_sch_mat_id IN NUMBER) IS
1339 SELECT schm.inventory_item_id,
1340 schm.organization_id,
1341 schm.uom,
1342 schm.requested_date,
1343 schm.status mat_status, --Added by sowsubra
1344 avtm.item_number --Modified by rnahata for ER 6391157, ahl_visit_task_matrl_v definition changed
1345 FROM ahl_schedule_materials schm,
1346 ahl_visit_task_matrl_v avtm
1347 WHERE schm.scheduled_material_id = avtm.schedule_material_id
1348 AND avtm.schedule_material_id = c_sch_mat_id;
1349
1350 -- anraj modified by adding two more columns task status code and meaning
1351 CURSOR Planned_Material_Cur (c_sch_mat_id IN NUMBER) IS
1352 SELECT visit_id,
1353 visit_task_id,
1354 visit_task_name,
1355 requested_quantity,
1356 scheduled_date,
1357 scheduled_quantity,
1358 item_number, --Modified by rnahata for ER 6391157, ahl_visit_task_matrl_v definition changed
1359 object_version_number,
1360 inventory_item_id,
1361 uom,
1362 requested_date,
1363 task_status_code,
1364 meaning
1365 FROM ahl_visit_task_matrl_v,FND_LOOKUP_VALUES_VL
1366 WHERE schedule_material_id = c_sch_mat_id
1367 AND LOOKUP_TYPE(+) = 'AHL_VWP_TASK_STATUS'
1368 AND LOOKUP_code = task_status_code;
1369
1370 --Standard local variables
1371 l_api_name CONSTANT VARCHAR2(30) := 'Schedule_Planned_Matrls';
1372 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
1373 l_api_version CONSTANT NUMBER := 1.0;
1374 l_return_status VARCHAR2(1);
1375 l_msg_data VARCHAR2(2000);
1376 l_msg_count NUMBER;
1377
1378 l_planned_matrl_tbl AHL_LTP_MATRL_AVAL_PUB.Planned_Matrl_Tbl := p_x_planned_matrl_tbl;
1379 l_Sch_Material_Rec Sch_Material_Cur%ROWTYPE;
1380 l_Planned_Material_Rec Planned_Material_Cur%ROWTYPE;
1381 l_temp_planned_matrl_tbl AHL_LTP_MATRL_AVAL_PUB.Planned_Matrl_Tbl;
1382 j NUMBER := 1;
1383 BEGIN
1384
1385 IF (l_log_procedure >= l_log_current_level) THEN
1386 fnd_log.string(l_log_procedure,
1387 L_DEBUG_KEY ||'.begin',
1388 'At the start of PL SQL procedure. Number of Records : ' || l_planned_matrl_tbl.COUNT);
1389 END IF;
1390 -- Standard Start of API savepoint
1391 SAVEPOINT schedule_planned_matrls;
1392 -- Initialize message list if p_init_msg_list is set to TRUE.
1393 IF FND_API.to_boolean(p_init_msg_list) THEN
1394 FND_MSG_PUB.initialize;
1395 END IF;
1396 -- Initialize API return status to success
1397 x_return_status := FND_API.G_RET_STS_SUCCESS;
1398 -- Standard call to check for call compatibility.
1399 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
1400 l_api_version,
1401 l_api_name,G_PKG_NAME)
1402 THEN
1403 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1404 END IF;
1405
1406 --
1407 IF l_planned_matrl_tbl.COUNT > 0 THEN
1408 FOR i IN l_planned_matrl_tbl.FIRST..l_planned_matrl_tbl.LAST
1409 LOOP
1410 --Get schedule materil details
1411 IF l_planned_matrl_tbl(i).schedule_material_id IS NOT NULL THEN
1412 OPEN Sch_Material_Cur(l_planned_matrl_tbl(i).schedule_material_id);
1413 FETCH Sch_Material_Cur INTO l_planned_matrl_tbl(i).inventory_item_id,
1414 l_planned_matrl_tbl(i).organization_id,
1415 l_planned_matrl_tbl(i).primary_uom_code,
1416 l_planned_matrl_tbl(i).requested_date,
1417 l_planned_matrl_tbl(i).mat_status, --Added by sowsubra
1418 l_planned_matrl_tbl(i).item_description;
1419 CLOSE Sch_Material_Cur;
1420 END IF;
1421
1422 --Added by sowsubra - starts
1423 IF l_planned_matrl_tbl(i).mat_status = 'IN-SERVICE' THEN
1424 Fnd_Message.SET_NAME('AHL','AHL_MAT_STS_INSERVICE');
1425 Fnd_Msg_Pub.ADD;
1426 RAISE Fnd_Api.G_EXC_ERROR;
1427 END IF;
1428 --Added by sowsubra - ends
1429 END LOOP;
1430 END IF;
1431
1432 --Assign values start from index value 1
1433 IF l_planned_matrl_tbl.COUNT > 0 THEN
1434 FOR i IN l_planned_matrl_tbl.FIRST..l_planned_matrl_tbl.LAST
1435 LOOP
1436 l_temp_planned_matrl_tbl(j).inventory_item_id := l_planned_matrl_tbl(i).inventory_item_id;
1437 l_temp_planned_matrl_tbl(j).visit_id := l_planned_matrl_tbl(i).visit_id;
1438 l_temp_planned_matrl_tbl(j).visit_task_id := l_planned_matrl_tbl(i).visit_task_id;
1439 l_temp_planned_matrl_tbl(j).schedule_material_id := l_planned_matrl_tbl(i).schedule_material_id;
1440 l_temp_planned_matrl_tbl(j).item_description := l_planned_matrl_tbl(i).item_description;
1441 l_temp_planned_matrl_tbl(j).organization_id := l_planned_matrl_tbl(i).organization_id;
1442 l_temp_planned_matrl_tbl(j).primary_uom_code := l_planned_matrl_tbl(i).primary_uom_code;
1443 l_temp_planned_matrl_tbl(j).requested_date := l_planned_matrl_tbl(i).requested_date;
1444 l_temp_planned_matrl_tbl(j).required_quantity := l_planned_matrl_tbl(i).required_quantity;
1445 j := j + 1;
1446 END LOOP;
1447 END IF;
1448
1449 --
1450 IF l_temp_planned_matrl_tbl.COUNT > 0 THEN
1451 IF (l_log_statement >= l_log_current_level) THEN
1452 fnd_log.string(l_log_statement,
1453 L_DEBUG_KEY,
1454 'Before calling Call ATP');
1455 END IF;
1456
1457 -- Call local procedure which calls atp Api
1458 Call_ATP
1459 (p_api_version => p_api_version,
1460 p_init_msg_list => p_init_msg_list,
1461 p_validation_level => p_validation_level,
1462 p_x_planned_matrl_tbl => l_temp_planned_matrl_tbl,
1463 x_return_status => l_return_status,
1464 x_msg_count => l_msg_count,
1465 x_msg_data => l_msg_data);
1466 END IF;
1467
1468 IF (l_log_statement >= l_log_current_level) THEN
1469 fnd_log.string(l_log_statement,
1470 L_DEBUG_KEY,
1471 'After calling Call ATP. Return Status : '|| l_return_status ||
1472 ', Returned Final Records : '||l_temp_planned_matrl_tbl.COUNT);
1473 END IF;
1474
1475 -- Check Error Message stack.
1476 IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
1477 l_msg_count := FND_MSG_PUB.count_msg;
1478 IF l_msg_count > 0 THEN
1479 RAISE FND_API.G_EXC_ERROR;
1480 END IF;
1481 END IF;
1482
1483 --Assign out parameter
1484 IF l_temp_planned_matrl_tbl.COUNT > 0 THEN
1485 FOR i IN l_temp_planned_matrl_tbl.FIRST..l_temp_planned_matrl_tbl.LAST
1486 LOOP
1487 --
1488 --Get schedule materil details
1489 IF l_temp_planned_matrl_tbl(i).schedule_material_id IS NOT NULL THEN
1490 OPEN Planned_Material_Cur(l_planned_matrl_tbl(i).schedule_material_id);
1491 FETCH Planned_Material_Cur INTO l_Planned_Material_Rec;
1492 CLOSE Planned_Material_Cur;
1493 --
1494 p_x_planned_matrl_tbl(i).schedule_material_id := l_temp_planned_matrl_tbl(i).schedule_material_id;
1495 p_x_planned_matrl_tbl(i).object_version_number := l_Planned_Material_Rec.object_version_number;
1496 p_x_planned_matrl_tbl(i).inventory_item_id := l_Planned_Material_Rec.inventory_item_id;
1497
1498 --Modified by rnahata for ER 6391157, ahl_visit_task_matrl_v definition changed
1499 p_x_planned_matrl_tbl(i).item_description := l_Planned_Material_Rec.item_number;
1500
1501 p_x_planned_matrl_tbl(i).visit_id := l_Planned_Material_Rec.visit_id;
1502 p_x_planned_matrl_tbl(i).visit_task_id := l_Planned_Material_Rec.visit_task_id;
1503 p_x_planned_matrl_tbl(i).task_name := l_Planned_Material_Rec.visit_task_name;
1504
1505 -- anraj added fot the Material Availability UI
1506 p_x_planned_matrl_tbl(i).task_status_code := l_Planned_Material_Rec.task_status_code;
1507 p_x_planned_matrl_tbl(i).task_status_meaning := l_Planned_Material_Rec.meaning;
1508
1509 p_x_planned_matrl_tbl(i).requested_date := l_Planned_Material_Rec.requested_date;
1510 p_x_planned_matrl_tbl(i).required_quantity := l_Planned_Material_Rec.requested_quantity;
1511 p_x_planned_matrl_tbl(i).quantity_available := l_temp_planned_matrl_tbl(i).quantity_available;
1512 -- p_x_planned_matrl_tbl(i).scheduled_quantity := l_Planned_Material_Rec.scheduled_quantity;
1513 p_x_planned_matrl_tbl(i).primary_uom := l_Planned_Material_Rec.uom;
1514 p_x_planned_matrl_tbl(i).error_code := l_temp_planned_matrl_tbl(i).error_code;
1515 p_x_planned_matrl_tbl(i).error_message := l_temp_planned_matrl_tbl(i).error_message;
1516
1517 IF (l_log_statement >= l_log_current_level)THEN
1518 fnd_log.string(l_log_statement,
1519 L_DEBUG_KEY,
1520 'After Assign, Sch Mat Id : ' || p_x_planned_matrl_tbl(i).schedule_material_id ||
1521 ', Quantity Available : ' || p_x_planned_matrl_tbl(i).quantity_available ||
1522 ', Scheduled Quantity : ' || p_x_planned_matrl_tbl(i).scheduled_date ||
1523 ', Error Code : ' || p_x_planned_matrl_tbl(i).error_code ||
1524 ', Error Message : ' || p_x_planned_matrl_tbl(i).error_message);
1525 END IF;
1526 END IF;
1527 END LOOP;
1528 END IF;
1529
1530 -- Standard check of p_commit
1531 IF FND_API.TO_BOOLEAN(p_commit) THEN
1532 COMMIT WORK;
1533 END IF;
1534
1535 IF (l_log_procedure >= l_log_current_level) THEN
1536 fnd_log.string(l_log_procedure,
1537 L_DEBUG_KEY ||'.end',
1538 'At the end of PL SQL procedure. Return Status = ' || x_return_status);
1539 END IF;
1540
1541 EXCEPTION
1542 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1543 ROLLBACK TO Schedule_Planned_Matrls;
1544 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1545 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1546 p_count => l_msg_count,
1547 p_data => l_msg_data);
1548
1549 WHEN FND_API.G_EXC_ERROR THEN
1550 ROLLBACK TO Schedule_Planned_Matrls;
1551 X_return_status := FND_API.G_RET_STS_ERROR;
1552 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1553 p_count => l_msg_count,
1554 p_data => l_msg_data);
1555
1556 WHEN OTHERS THEN
1557 ROLLBACK TO Schedule_Planned_Matrls;
1558 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1559 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1560 THEN
1561 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_MATRL_AVAL_PVT',
1562 p_procedure_name => 'SCHEDULE_PLANNED_MATRLS',
1563 p_error_text => SUBSTR(SQLERRM,1,240));
1564 END IF;
1565 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1566 p_count => l_msg_count,
1567 p_data => l_msg_data);
1568
1569 END Schedule_Planned_Matrls;
1570 --
1571 -- Start of Comments --
1572 -- Procedure name : Schedule_All_Materials
1573 -- Type : Public
1574 -- Function : This procedure calls ATP to schedule planned materials for a visit
1575 -- for Routine jobs derived requested quantity and task start date
1576 -- Pre-reqs :
1577 -- Parameters :
1578 --
1579 -- Standard IN Parameters :
1580 -- p_api_version IN NUMBER Required
1581 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
1582 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
1583 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
1584 -- Based on this flag, the API will set the default attributes.
1585 -- p_module_type In VARCHAR2 Default NULL
1586 -- This will be null.
1587 -- Standard OUT Parameters :
1588 -- x_return_status OUT VARCHAR2 Required
1589 -- x_msg_count OUT NUMBER Required
1590 -- x_msg_data OUT VARCHAR2 Required
1591 --
1592 -- Schedule_All_Materials Parameters :
1593 -- p_visit_id IN Number,Required
1594 -- List of item attributes associated to visit task
1595 --
1596 PROCEDURE Schedule_All_Materials (
1597 p_api_version IN NUMBER,
1598 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1599 p_commit IN VARCHAR2 := FND_API.g_false,
1600 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1601 p_visit_id IN NUMBER,
1602 x_planned_matrl_tbl OUT NOCOPY AHL_LTP_MATRL_AVAL_PUB.Planned_Matrl_Tbl,
1603 x_return_status OUT NOCOPY VARCHAR2,
1604 x_msg_count OUT NOCOPY NUMBER,
1605 x_msg_data OUT NOCOPY VARCHAR2)
1606 IS
1607
1608 -- yazhou 03-JUL-2006 starts
1609 -- bug fix#5303378
1610
1611 CURSOR Get_Visit_Task_Matrl_Cur (C_VISIT_ID IN NUMBER) IS
1612 SELECT schm.scheduled_material_id,
1613 schm.organization_id,
1614 schm.visit_id,
1615 schm.visit_task_id,
1616 schm.material_request_type,
1617 schm.uom,
1618 schm.inventory_item_id,
1619 schm.requested_date,
1620 schm.requested_quantity,
1621 mtl.concatenated_segments
1622 FROM ahl_schedule_materials schm,
1623 mtl_system_items_vl mtl
1624 WHERE schm.inventory_item_id = mtl.inventory_item_id
1625 AND schm.organization_id = mtl.organization_id
1626 AND schm.requested_quantity <> 0
1627 AND NVL(schm.status, 'X') <> 'IN-SERVICE' --Added by sowsubra for Issue 105
1628 AND schm.visit_id = C_VISIT_ID;
1629 -- yazhou 03-JUL-2006 ends
1630
1631 --Standard local variables
1632 l_api_name CONSTANT VARCHAR2(30) := 'Schedule_All_Materials';
1633 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
1634 l_api_version CONSTANT NUMBER := 1.0;
1635 l_return_status VARCHAR2(1);
1636 l_msg_data VARCHAR2(2000);
1637 l_msg_count NUMBER;
1638 --
1639 l_planned_matrl_tbl AHL_LTP_MATRL_AVAL_PUB.Planned_Matrl_Tbl;
1640 l_Visit_Task_Matrl_Rec Get_Visit_Task_Matrl_Cur%ROWTYPE;
1641 i NUMBER;
1642 BEGIN
1643
1644 IF (l_log_procedure >= l_log_current_level) THEN
1645 fnd_log.string(l_log_procedure,
1646 L_DEBUG_KEY ||'.begin',
1647 'At the start of PL SQL procedure. Visit Id = ' || p_visit_id);
1648 END IF;
1649 -- Standard Start of API savepoint
1650 SAVEPOINT Schedule_All_Materials;
1651 -- Initialize message list if p_init_msg_list is set to TRUE.
1652 IF FND_API.to_boolean(p_init_msg_list) THEN
1653 FND_MSG_PUB.initialize;
1654 END IF;
1655 -- Initialize API return status to success
1656 x_return_status := FND_API.G_RET_STS_SUCCESS;
1657 -- Standard call to check for call compatibility.
1658 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
1659 l_api_version,
1660 l_api_name,G_PKG_NAME)
1661 THEN
1662 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1663 END IF;
1664
1665 IF p_visit_id IS NOT NULL THEN
1666 OPEN Get_Visit_Task_Matrl_Cur(p_visit_id);
1667 i := 1;
1668 LOOP
1669 FETCH Get_Visit_Task_Matrl_Cur INTO l_Visit_Task_Matrl_Rec;
1670 EXIT WHEN Get_Visit_Task_Matrl_Cur%NOTFOUND;
1671 --Assign to table
1672 l_planned_matrl_tbl(i).visit_id := l_Visit_Task_Matrl_Rec.visit_id;
1673 l_planned_matrl_tbl(i).visit_task_id := l_Visit_Task_Matrl_Rec.visit_task_id;
1674 l_planned_matrl_tbl(i).schedule_material_id := l_Visit_Task_Matrl_Rec.scheduled_material_id;
1675 l_planned_matrl_tbl(i).inventory_item_id := l_Visit_Task_Matrl_Rec.inventory_item_id;
1676 l_planned_matrl_tbl(i).item_description := l_Visit_Task_Matrl_Rec.concatenated_segments;
1677 l_planned_matrl_tbl(i).organization_id := l_Visit_Task_Matrl_Rec.organization_id;
1678 l_planned_matrl_tbl(i).primary_uom_code := l_Visit_Task_Matrl_Rec.uom;
1679 l_planned_matrl_tbl(i).requested_date := l_Visit_Task_Matrl_Rec.requested_date;
1680 l_planned_matrl_tbl(i).required_quantity := l_Visit_Task_Matrl_Rec.requested_quantity;
1681 i := i + 1;
1682 END LOOP;
1683 CLOSE Get_Visit_Task_Matrl_Cur;
1684 END IF; --Visit not null
1685
1686 IF (l_log_statement >= l_log_current_level) THEN
1687 fnd_log.string(l_log_statement,
1688 L_DEBUG_KEY,
1689 'Before calling Call ATP No of Records'||l_planned_matrl_tbl.COUNT);
1690 END IF;
1691
1692 IF l_planned_matrl_tbl.COUNT > 0 THEN
1693 -- Call local procedure which calls atp Api
1694 schedule_planned_matrls
1695 (p_api_version => p_api_version,
1696 p_init_msg_list => p_init_msg_list,
1697 p_validation_level => p_validation_level,
1698 p_x_planned_matrl_tbl => l_Planned_Matrl_Tbl,
1699 x_return_status => l_return_status,
1700 x_msg_count => l_msg_count,
1701 x_msg_data => l_msg_data);
1702 END IF;
1703
1704 IF (l_log_statement >= l_log_current_level) THEN
1705 fnd_log.string(l_log_statement,
1706 L_DEBUG_KEY,
1707 'After calling Call ATP, Return Status : '|| l_return_status);
1708 END IF;
1709
1710 -- Check Error Message stack.
1711 IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
1712 l_msg_count := FND_MSG_PUB.count_msg;
1713 IF l_msg_count > 0 THEN
1714 RAISE FND_API.G_EXC_ERROR;
1715 END IF;
1716 END IF;
1717 --Assign to out variable
1718 IF l_Planned_Matrl_Tbl.COUNT > 0 THEN
1719 FOR i IN l_Planned_Matrl_Tbl.FIRST..l_Planned_Matrl_Tbl.LAST
1720 LOOP
1721 x_planned_matrl_tbl(i).schedule_material_id := l_Planned_Matrl_Tbl(i).schedule_material_id;
1722 x_Planned_Matrl_Tbl(i).object_version_number := l_Planned_Matrl_Tbl(i).object_version_number;
1723 x_Planned_Matrl_Tbl(i).inventory_item_id := l_Planned_Matrl_Tbl(i).inventory_item_id;
1724 x_Planned_Matrl_Tbl(i).item_description := l_Planned_Matrl_Tbl(i).item_description;
1725 x_Planned_Matrl_Tbl(i).visit_id := l_Planned_Matrl_Tbl(i).visit_id;
1726 x_Planned_Matrl_Tbl(i).visit_task_id := l_Planned_Matrl_Tbl(i).visit_task_id;
1727 x_Planned_Matrl_Tbl(i).task_name := l_Planned_Matrl_Tbl(i).task_name;
1728 -- anraj added
1729 x_Planned_Matrl_Tbl(i).task_status_code := l_Planned_Matrl_Tbl(i).task_status_code;
1730 x_Planned_Matrl_Tbl(i).task_status_meaning := l_Planned_Matrl_Tbl(i).task_status_meaning;
1731
1732 x_Planned_Matrl_Tbl(i).requested_date := l_Planned_Matrl_Tbl(i).requested_date;
1733 x_Planned_Matrl_Tbl(i).required_quantity := l_Planned_Matrl_Tbl(i).required_quantity;
1734 x_Planned_Matrl_Tbl(i).quantity_available := l_Planned_Matrl_Tbl(i).quantity_available;
1735 -- x_Planned_Matrl_Tbl(i).scheduled_quantity := l_Planned_Matrl_Tbl(i).scheduled_quantity;
1736 x_Planned_Matrl_Tbl(i).primary_uom := l_Planned_Matrl_Tbl(i).primary_uom;
1737 x_Planned_Matrl_Tbl(i).error_code := l_Planned_Matrl_Tbl(i).error_code;
1738 x_Planned_Matrl_Tbl(i).error_message := l_Planned_Matrl_Tbl(i).error_message;
1739
1740 IF (l_log_statement >= l_log_current_level) THEN
1741 fnd_log.string(l_log_statement,
1742 L_DEBUG_KEY,
1743 'After Assign, Sch Mat Id : ' || x_planned_matrl_tbl(i).schedule_material_id ||
1744 ', Quantity Available : ' || x_planned_matrl_tbl(i).quantity_available ||
1745 ', Scheduled Quantity : ' || x_planned_matrl_tbl(i).scheduled_date ||
1746 ', Error Code : ' || x_planned_matrl_tbl(i).error_code ||
1747 ', Error Message : ' || x_planned_matrl_tbl(i).error_message);
1748 END IF;
1749 END LOOP;
1750 END IF;
1751 -- Standard check of p_commit
1752 IF FND_API.TO_BOOLEAN(p_commit) THEN
1753 COMMIT WORK;
1754 END IF;
1755
1756 IF (l_log_procedure >= l_log_current_level) THEN
1757 fnd_log.string(l_log_procedure,
1758 L_DEBUG_KEY ||'.end',
1759 'At the end of PL SQL procedure. Return Status = ' || x_return_status);
1760 END IF;
1761
1762 EXCEPTION
1763 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1764 ROLLBACK TO Schedule_All_Materials;
1765 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1766 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1767 p_count => l_msg_count,
1768 p_data => l_msg_data);
1769
1770 WHEN FND_API.G_EXC_ERROR THEN
1771 ROLLBACK TO Schedule_All_Materials;
1772 X_return_status := FND_API.G_RET_STS_ERROR;
1773 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1774 p_count => l_msg_count,
1775 p_data => l_msg_data);
1776
1777 WHEN OTHERS THEN
1778 ROLLBACK TO Schedule_All_Materials;
1779 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1780 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1781 THEN
1782 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_MATRL_AVAL_PVT',
1783 p_procedure_name => 'SCHEDULE_ALL_MATERIALS',
1784 p_error_text => SUBSTR(SQLERRM,1,240));
1785 END IF;
1786 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1787 p_count => l_msg_count,
1788 p_data => l_msg_data);
1789
1790 END Schedule_All_Materials;
1791
1792 END AHL_LTP_MATRL_AVAL_PVT;