[Home] [Help]
PACKAGE BODY: APPS.AHL_LTP_MATRL_AVAL_PUB
Source
1 PACKAGE BODY AHL_LTP_MATRL_AVAL_PUB AS
2 /* $Header: AHLPMTAB.pls 120.1.12020000.2 2012/12/07 00:45:04 sareepar ship $ */
3 --
4 G_PKG_NAME VARCHAR2(30) := 'AHL_LTP_MATRL_AVAL_PUB';
5 G_DEBUG VARCHAR2(1) := AHL_DEBUG_PUB.is_log_enabled;
6 --
7 -- PACKAGE
8 -- AHL_LTP_MATRL_AVAL_PUB
9 --
10 -- PURPOSE
11 -- This Package is a Public API for verifying material availabilty for an item
12 -- Calling ATP
13 --
14 -- NOTES
15 --
16 --
17 -- HISTORY
18 -- 23-Apr-2002 ssurapan Created.
19
20 ------------------------
21 -- Declare Procedures --
22 ------------------------
23
24 -- Start of Comments --
25 -- Procedure name : Check_Material_Aval
26 -- Type : Public
27 -- Function : This procedure calls ATP to check inventory item is available
28 -- for Routine jobs derived requested quantity and task start date
29 -- Pre-reqs :
30 -- Parameters :
31 --
32 -- Standard IN Parameters :
33 -- p_api_version IN NUMBER Required
34 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
35 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
36 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
37 -- Based on this flag, the API will set the default attributes.
38 -- p_module_type In VARCHAR2 Default NULL
39 -- This will be null.
40 -- Standard OUT Parameters :
41 -- x_return_status OUT VARCHAR2 Required
42 -- x_msg_count OUT NUMBER Required
43 -- x_msg_data OUT VARCHAR2 Required
44 --
45 -- Check_Material_Aval Parameters :
46 -- p_x_material_avl_tbl IN OUT NOCOPY Material_Availability_Tbl,Required
47 -- List of item attributes associated to visit task
48 --
49 PROCEDURE Check_Material_Aval (
50 p_api_version IN NUMBER,
51 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
52 p_commit IN VARCHAR2 := FND_API.g_false,
53 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
54 p_module_type IN VARCHAR2 := 'JSP',
55 p_x_material_avl_tbl IN OUT NOCOPY Material_Availability_Tbl,
56 x_return_status OUT NOCOPY VARCHAR2,
57 x_msg_count OUT NOCOPY NUMBER,
58 x_msg_data OUT NOCOPY VARCHAR2
59 )
60 IS
61 l_api_name CONSTANT VARCHAR2(30) := 'CHECK_MATERIAL_AVAL';
62 l_api_version CONSTANT NUMBER := 1.0;
63 l_msg_count NUMBER;
64 l_return_status VARCHAR2(1);
65 l_msg_data VARCHAR2(2000);
66 l_commit VARCHAR2(10) := FND_API.g_false;
67 l_material_avl_tbl Material_Availability_Tbl := p_x_material_avl_tbl;
68 BEGIN
69
70 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
71 fnd_log.string
72 (
73 fnd_log.level_procedure,
74 'ahl.plsql.AHL_LTP_REQST_MATRL_PUB.Check_Material_Aval',
75 'At the start of PLSQL procedure'
76 );
77 END IF;
78 -- dbms_output.put_line( 'start public API:');
79
80 --------------------Initialize ----------------------------------
81 -- Standard Start of API savepoint
82 SAVEPOINT check_material_aval;
83 -- Standard call to check for call compatibility.
84 IF FND_API.to_boolean(p_init_msg_list)
85 THEN
86 FND_MSG_PUB.initialize;
87 END IF;
88 -- Initialize API return status to success
89 x_return_status := FND_API.G_RET_STS_SUCCESS;
90 -- Initialize message list if p_init_msg_list is set to TRUE.
91 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
92 p_api_version,
93 l_api_name,G_PKG_NAME)
94 THEN
95 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
96 END IF;
97 --------------------Start of API Body-----------------------------------
98 IF l_material_avl_tbl.COUNT > 0 THEN
99 --
100 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
101 fnd_log.string
102 (
103 fnd_log.level_statement,
104 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
105 'Request for Check Material Aval Number of Records : ' || l_material_avl_tbl.COUNT
106 );
107 fnd_log.string
108 (
109 fnd_log.level_statement,
110 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
111 'Request for Check Material Aval Schedule material Id : ' || l_material_avl_tbl(1).schedule_material_id
112 );
113
114 END IF;
115
116 AHL_LTP_MATRL_AVAL_PVT.Check_Material_Aval
117 (p_api_version => p_api_version,
118 p_init_msg_list => p_init_msg_list,
119 p_commit => l_commit,
120 p_validation_level => p_validation_level,
121 p_module_type => p_module_type,
122 p_x_material_avl_tbl => l_material_avl_tbl,
123 x_return_status => l_return_status,
124 x_msg_count => l_msg_count,
125 x_msg_data => l_msg_data);
126 END IF;
127
128 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
129 fnd_log.string
130 (
131 fnd_log.level_procedure,
132 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||': End API',
133 'After calling ahl ltp matrl aval pvt.Check Material Aval, Return Status : '|| l_return_status
134 );
135 END IF;
136
137 -- Check Error Message stack.
138 IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
139 l_msg_count := FND_MSG_PUB.count_msg;
140 IF l_msg_count > 0 THEN
141 RAISE FND_API.G_EXC_ERROR;
142 END IF;
143 END IF;
144 --Assign derived values
145 IF l_material_avl_tbl.COUNT > 0 THEN
146 FOR i IN l_material_avl_tbl.FIRST..l_material_avl_tbl.LAST
147 LOOP
148 p_x_material_avl_tbl(i).visit_task_id := l_material_avl_tbl(i).visit_task_id;
149 p_x_material_avl_tbl(i).task_name := l_material_avl_tbl(i).task_name;
150 p_x_material_avl_tbl(i).inventory_item_id := l_material_avl_tbl(i).inventory_item_id;
151 p_x_material_avl_tbl(i).item := l_material_avl_tbl(i).item;
152 p_x_material_avl_tbl(i).req_arrival_date := l_material_avl_tbl(i).req_arrival_date;
153 p_x_material_avl_tbl(i).uom := l_material_avl_tbl(i).uom;
154 p_x_material_avl_tbl(i).quantity := l_material_avl_tbl(i).quantity;
155 p_x_material_avl_tbl(i).quantity_available := l_material_avl_tbl(i).quantity_available;
156 p_x_material_avl_tbl(i).schedule_material_id := l_material_avl_tbl(i).schedule_material_id;
157 p_x_material_avl_tbl(i).error_code := l_material_avl_tbl(i).error_code;
158 p_x_material_avl_tbl(i).error_message := 'For Item'||l_material_avl_tbl(i).item||','||l_material_avl_tbl(i).error_message;
159 --SKPATHAK :: Bug 8392521 :: 02-APR-2009
160 --Make the schedule date returned by the private API, available to the out param of the public API
161 p_x_material_avl_tbl(i).scheduled_date := l_material_avl_tbl(i).scheduled_date;
162
163
164 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
165 fnd_log.string
166 (
167 fnd_log.level_statement,
168 'ahl.plsql.AHL_LTP_MATRL_AVAL_PUB.Check_Material_Aval',
169 ' Derieved Value, Visit Task Id: ' || p_x_material_avl_tbl(i).visit_task_id
170 );
171 fnd_log.string
172 (
173 fnd_log.level_statement,
174 'ahl.plsql.AHL_LTP_MATRL_AVAL_PUB.Check_Material_Aval',
175 ' Derieved Value, Inventory Item Id: ' || p_x_material_avl_tbl(i).inventory_item_id
176 );
177 fnd_log.string
178 (
179 fnd_log.level_statement,
180 'ahl.plsql.AHL_LTP_MATRL_AVAL_PUB.Check_Material_Aval',
181 ' Derieved Value, Quantity: ' || p_x_material_avl_tbl(i).quantity
182 );
183 fnd_log.string
184 (
185 fnd_log.level_statement,
186 'ahl.plsql.AHL_LTP_MATRL_AVAL_PUB.Check_Material_Aval',
187 ' Derieved Value, Quantity Available: ' || p_x_material_avl_tbl(i).quantity_available
188 );
189 fnd_log.string
190 (
191 fnd_log.level_statement,
192 'ahl.plsql.AHL_LTP_MATRL_AVAL_PUB.Check_Material_Aval',
193 ' Derieved Value, Error Message: ' || p_x_material_avl_tbl(i).error_message
194 );
195
196 END IF;
197 END LOOP;
198 END IF;
199 ------------------------End of Body---------------------------------------
200 --Standard check to count messages
201 IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
202 l_msg_count := FND_MSG_PUB.count_msg;
203 IF l_msg_count > 0 THEN
204 RAISE FND_API.G_EXC_ERROR;
205 END IF;
206 END IF;
207
208 --Standard check for commit
209 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
210 COMMIT;
211 END IF;
212
213 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
214 fnd_log.string
215 (
216 fnd_log.level_procedure,
217 'ahl.plsql.AHL_LTP_MATRL_AVAL_PUB.Check Material Aval.end',
218 'At the end of PLSQL procedure'
219 );
220 END IF;
221
222 EXCEPTION
223 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
224 ROLLBACK TO check_material_aval;
225 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
226 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
227 p_count => x_msg_count,
228 p_data => x_msg_data);
229
230 WHEN FND_API.G_EXC_ERROR THEN
231 ROLLBACK TO check_material_aval;
232 X_return_status := FND_API.G_RET_STS_ERROR;
233 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
234 p_count => x_msg_count,
235 p_data => X_msg_data);
236
237 WHEN OTHERS THEN
238 ROLLBACK TO check_material_aval;
239 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
240 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
241 THEN
242 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_MATRL_AVAL_PUB',
243 p_procedure_name => 'CHECK_MATERIAL_AVAL',
244 p_error_text => SUBSTR(SQLERRM,1,240));
245 END IF;
246 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
247 p_count => x_msg_count,
248 p_data => X_msg_data);
249
250 END Check_Material_Aval;
251
252 -- Start of Comments --
253 -- Procedure name : Get_Visit_Task_Materials
254 -- Type : Public
255 -- Function : This procedure derives material information associated to scheduled
256 -- visit, which are defined at Route Operation level
257 -- Pre-reqs :
258 -- Parameters :
259 --
260 -- Standard IN Parameters :
261 -- p_api_version IN NUMBER Required
262 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
263 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
264 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
265 -- Based on this flag, the API will set the default attributes.
266 -- p_module_type In VARCHAR2 Default NULL
267 -- This will be null.
268 -- Standard OUT Parameters :
269 -- x_return_status OUT VARCHAR2 Required
270 -- x_msg_count OUT NUMBER Required
271 -- x_msg_data OUT VARCHAR2 Required
272 --
273 -- Get_Visit_Task_Materials :
274 -- p_visit_id IN NUMBER,Required
275 --
276 PROCEDURE Get_Visit_Task_Materials (
277 p_api_version IN NUMBER,
278 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
279 p_commit IN VARCHAR2 := FND_API.g_false,
280 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
281 p_module_type IN VARCHAR2 := 'JSP',
282 p_visit_id IN NUMBER,
283 x_task_req_matrl_tbl OUT NOCOPY Task_Req_Matrl_Tbl,
284 x_return_status OUT NOCOPY VARCHAR2,
285 x_msg_count OUT NOCOPY NUMBER,
286 x_msg_data OUT NOCOPY VARCHAR2)
287 IS
288 --Standard local variables
289 l_api_name CONSTANT VARCHAR2(30) := 'Get_Visit_Task_Materials';
290 l_api_version CONSTANT NUMBER := 1.0;
291 l_msg_count NUMBER;
292 l_return_status VARCHAR2(1);
293 l_msg_data VARCHAR2(2000);
294 --
295 l_task_req_matrl_tbl Task_Req_Matrl_Tbl;
296 --
297 BEGIN
298
299 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
300 fnd_log.string
301 (
302 fnd_log.level_procedure,
303 'ahl.plsql.AHL_LTP_MATRL_AVAL_PUB.Get_Visit_Task_Materials.begin',
304 'At the start of PLSQL procedure'
305 );
306 END IF;
307 -- Standard Start of API savepoint
308 SAVEPOINT get_visit_task_materials;
309 -- Initialize message list if p_init_msg_list is set to TRUE.
310 IF FND_API.to_boolean(p_init_msg_list)
311 THEN
312 FND_MSG_PUB.initialize;
313 END IF;
314 -- Initialize API return status to success
315 x_return_status := FND_API.G_RET_STS_SUCCESS;
316 -- Standard call to check for call compatibility.
317 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
318 p_api_version,
319 l_api_name,G_PKG_NAME)
320 THEN
321 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
322 END IF;
323
324 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
325 fnd_log.string
326 (
327 fnd_log.level_statement,
328 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
329 'Request for Visit Task Materials for Visit Id : ' || p_visit_id
330 );
331
332 END IF;
333
334 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
335 fnd_log.string
336 (
337 fnd_log.level_procedure,
338 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
339 'Before calling ahl ltp matrl aval pvt.Get Visit Task Materials'
340 );
341
342 END IF;
343
344 IF p_visit_id IS NOT NULL AND p_visit_id <> FND_API.G_MISS_NUM
345 THEN
346
347 AHL_LTP_MATRL_AVAL_PVT.Get_Visit_Task_Materials
348 (p_api_version => p_api_version,
349 p_init_msg_list => p_init_msg_list,
350 p_validation_level => p_validation_level,
351 p_visit_id => p_visit_id,
352 x_task_req_matrl_tbl => l_task_req_matrl_tbl,
353 x_return_status => l_return_status,
354 x_msg_count => l_msg_count,
355 x_msg_data => l_msg_data);
356
357 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
358 fnd_log.string
359 (
360 fnd_log.level_procedure,
361 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||': End API',
362 'After calling ahl ltp matrl aval pvt.Get Visit Task Materials, Return Status : '|| l_return_status
363 );
364 END IF;
365
366 -- Check Error Message stack.
367 IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
368 l_msg_count := FND_MSG_PUB.count_msg;
369 IF l_msg_count > 0 THEN
370 RAISE FND_API.G_EXC_ERROR;
371 END IF;
372 END IF;
373
374 IF l_task_req_matrl_tbl.COUNT > 0 THEN
375 FOR i IN l_task_req_matrl_tbl.FIRST..l_task_req_matrl_tbl.LAST
376 LOOP
377 x_task_req_matrl_tbl(i).schedule_material_id := l_task_req_matrl_tbl(i).schedule_material_id;
378 x_task_req_matrl_tbl(i).object_version_number := l_task_req_matrl_tbl(i).object_version_number;
379 x_task_req_matrl_tbl(i).visit_task_id := l_task_req_matrl_tbl(i).visit_task_id;
380 x_task_req_matrl_tbl(i).task_name := l_task_req_matrl_tbl(i).task_name;
381 -- anraj : added columns TASK_STATUS_CODE and TASK_STATUS_MEANING , for Material Availabilty UI
382 x_task_req_matrl_tbl(i).task_status_code := l_task_req_matrl_tbl(i).task_status_code;
383 x_task_req_matrl_tbl(i).task_status_meaning:= l_task_req_matrl_tbl(i).task_status_meaning;
384 x_task_req_matrl_tbl(i).inventory_item_id := l_task_req_matrl_tbl(i).inventory_item_id;
385 x_task_req_matrl_tbl(i).item := l_task_req_matrl_tbl(i).item;
386 x_task_req_matrl_tbl(i).req_arrival_date := l_task_req_matrl_tbl(i).req_arrival_date;
387 x_task_req_matrl_tbl(i).uom_code := l_task_req_matrl_tbl(i).uom_code;
388 x_task_req_matrl_tbl(i).quantity := l_task_req_matrl_tbl(i).quantity;
389 x_task_req_matrl_tbl(i).scheduled_date := l_task_req_matrl_tbl(i).scheduled_date;
390 x_task_req_matrl_tbl(i).planned_order := l_task_req_matrl_tbl(i).planned_order;
391 --
395 fnd_log.level_statement,
392 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
393 fnd_log.string
394 (
396 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
397 'Schedule Material Id : '||x_task_req_matrl_tbl(i).schedule_material_id
398 );
399 fnd_log.string
400 (
401 fnd_log.level_statement,
402 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
403 'Object Version Number : '||x_task_req_matrl_tbl(i).object_version_number
404 );
405 fnd_log.string
406 (
407 fnd_log.level_statement,
408 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
409 'Visit Task Id : '||x_task_req_matrl_tbl(i).visit_task_id
410 );
411 fnd_log.string
412 (
413 fnd_log.level_statement,
414 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
415 'Visit Task Name : '||x_task_req_matrl_tbl(i).task_name
416 );
417 fnd_log.string
418 (
419 fnd_log.level_statement,
420 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
421 'Inventory Item Id : '||x_task_req_matrl_tbl(i).inventory_item_id
422 );
423 fnd_log.string
424 (
425 fnd_log.level_statement,
426 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
427 'Item Description : '||x_task_req_matrl_tbl(i).item
428 );
429 fnd_log.string
430 (
431 fnd_log.level_statement,
432 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
433 'Requested Date : '||x_task_req_matrl_tbl(i).req_arrival_date
434 );
435 fnd_log.string
436 (
437 fnd_log.level_statement,
438 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
439 'UOM : '||x_task_req_matrl_tbl(i).uom_code
440 );
441 fnd_log.string
442 (
443 fnd_log.level_statement,
444 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
445 'Requested Quantity : '||x_task_req_matrl_tbl(i).quantity
446 );
447 fnd_log.string
448 (
449 fnd_log.level_statement,
450 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
451 'Planned Order Id : '||x_task_req_matrl_tbl(i).planned_order
452 );
453
454 END IF;
455
456 END LOOP;
457 END IF;
458 END IF;
459
460 -- Check Error Message stack.
461 IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
462 l_msg_count := FND_MSG_PUB.count_msg;
463 IF l_msg_count > 0 THEN
464 RAISE FND_API.G_EXC_ERROR;
465 END IF;
466 END IF;
467
468 --Standard check for commit
469 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
470 COMMIT;
471 END IF;
472
473 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
474 fnd_log.string
475 (
476 fnd_log.level_procedure,
477 'ahl.plsql.AHL_LTP_MATRL_AVAL_PUB.Get_Visit_Task_Materials.end',
478 'At the end of PLSQL procedure'
479 );
480 END IF;
481
482 EXCEPTION
483 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
484 ROLLBACK TO get_visit_task_materials;
485 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
486 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
487 p_count => x_msg_count,
488 p_data => x_msg_data);
489
490 WHEN FND_API.G_EXC_ERROR THEN
491 ROLLBACK TO get_visit_task_materials;
492 X_return_status := FND_API.G_RET_STS_ERROR;
493 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
494 p_count => x_msg_count,
495 p_data => X_msg_data);
496
497 WHEN OTHERS THEN
498 ROLLBACK TO get_visit_task_materials;
499 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
500 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
501 THEN
502 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_MATRL_AVAL_PUB',
503 p_procedure_name => 'GET_VISIT_TASK_MATERIALS',
504 p_error_text => SUBSTR(SQLERRM,1,240));
505 END IF;
506 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
507 p_count => x_msg_count,
508 p_data => X_msg_data);
509 END Get_Visit_Task_Materials;
510
511 -- Start of Comments --
512 -- Procedure name : Check_Materials_For_All
513 -- Type : Public
514 -- Function : This procedure calls ATP to check inventory item is available
515 -- for Routine jobs associated to a visit
516 -- Pre-reqs :
517 -- Parameters :
518 --
519 -- Standard IN Parameters :
520 -- p_api_version IN NUMBER Required
521 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
522 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
523 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
524 -- Based on this flag, the API will set the default attributes.
525 -- p_module_type In VARCHAR2 Default NULL
526 -- This will be null.
527 -- Standard OUT Parameters :
528 -- x_return_status OUT VARCHAR2 Required
529 -- x_msg_count OUT NUMBER Required
530 -- x_msg_data OUT VARCHAR2 Required
531 --
532 -- Check_Materials_For_All Parameters :
533 -- p_visit_id IN NUMBER, Required
537 p_api_version IN NUMBER,
534 -- List of item attributes associated to visit task
535 --
536 PROCEDURE Check_Materials_For_All (
538 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
539 p_commit IN VARCHAR2 := FND_API.g_false,
540 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
541 p_module_type IN VARCHAR2 := 'JSP',
542 p_visit_id IN NUMBER,
543 x_task_matrl_aval_tbl OUT NOCOPY Material_Availability_Tbl,
544 x_return_status OUT NOCOPY VARCHAR2,
545 x_msg_count OUT NOCOPY NUMBER,
546 x_msg_data OUT NOCOPY VARCHAR2
547
548 )IS
549
550 -- anraj added for checking whether atleast one task is in status planning
551 CURSOR c_any_task_in_planning (c_visit_id IN NUMBER)
552 IS
553 SELECT 1
554 FROM AHL_VISIT_TASKS_B
555 WHERE visit_id = c_visit_id
556 AND status_code = 'PLANNING';
557
558 l_dummy number;
559
560 l_api_name CONSTANT VARCHAR2(30) := 'CHECK_MATERIALS_FOR_ALL';
561 l_api_version CONSTANT NUMBER := 1.0;
562 l_msg_count NUMBER;
563 l_return_status VARCHAR2(1);
564 l_mat_return_status VARCHAR2(1);
565 l_msg_data VARCHAR2(2000);
566 l_task_req_matrl_tbl Task_Req_Matrl_Tbl;
567 l_material_avl_tbl Material_Availability_Tbl;
568 --
569 J NUMBER;
570 BEGIN
571
572 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
573 fnd_log.string
574 (
575 fnd_log.level_procedure,
576 'ahl.plsql.AHL_LTP_REQST_MATRL_PUB.Check_Materials_For_All',
577 'At the start of PLSQL procedure'
578 );
579 END IF;
580
581 --------------------Initialize ----------------------------------
582 -- Standard Start of API savepoint
583 SAVEPOINT check_materials_for_all;
584 -- Standard call to check for call compatibility.
585 IF FND_API.to_boolean(p_init_msg_list)
586 THEN
587 FND_MSG_PUB.initialize;
588 END IF;
589 -- Initialize API return status to success
590 x_return_status := FND_API.G_RET_STS_SUCCESS;
591 -- Initialize message list if p_init_msg_list is set to TRUE.
592 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
593 p_api_version,
594 l_api_name,G_PKG_NAME)
595 THEN
596 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
597 END IF;
598 --------------------Start of API Body-----------------------------------
599 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
600 fnd_log.string
601 (
602 fnd_log.level_statement,
603 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
604 'Request for Check Material For Visit Id : ' || p_visit_id
605 );
606
607 END IF;
608
609 IF p_visit_id IS NOT NULL AND p_visit_id <> FND_API.G_MISS_NUM
610 THEN
611
612 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
613 fnd_log.string
614 (
615 fnd_log.level_procedure,
616 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
617 'Before calling ahl ltp matrl aval pvt.Get Visit Task Materials'
618 );
619
620 END IF;
621
622 --anraj, validation to check whether atleast one task is in planning
623 OPEN c_any_task_in_planning(p_visit_id);
624 FETCH c_any_task_in_planning INTO l_dummy;
625 IF c_any_task_in_planning%NOTFOUND THEN
626 Fnd_Message.SET_NAME('AHL','AHL_LTP_CHK_AVL_ALL_NONE_PLAN');
627 Fnd_Msg_Pub.ADD;
628 CLOSE c_any_task_in_planning;
629 RAISE Fnd_Api.G_EXC_ERROR;
630 END IF;
631 CLOSE c_any_task_in_planning;
632
633
634 AHL_LTP_MATRL_AVAL_PVT.Get_Visit_Task_Materials
635 ( p_api_version => p_api_version,
636 p_init_msg_list => p_init_msg_list,
637 p_validation_level => p_validation_level,
638 p_visit_id => p_visit_id,
639 x_task_req_matrl_tbl => l_task_req_matrl_tbl,
640 x_return_status => l_return_status,
641 x_msg_count => l_msg_count,
642 x_msg_data => l_msg_data);
643 --
644 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
645 fnd_log.string
646 (
647 fnd_log.level_procedure,
648 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||': End API',
649 'After calling ahl ltp Matrl Aval pvt.Get Visit Task Materials, Return Status : '|| l_return_status
650 );
651 END IF;
652
653 -- Check Error Message stack.
654 IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
655 l_msg_count := FND_MSG_PUB.count_msg;
656 IF l_msg_count > 0 THEN
657 RAISE FND_API.G_EXC_ERROR;
658 END IF;
659 END IF;
660
661 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
662 fnd_log.string
663 (
664 fnd_log.level_statement,
665 'ahl.plsql.AHL_LTP_MATRL_AVAL_PUB.Check_Materials_For_All',
666 ' After Calling Get Visit Task Materials, Number of Records: ' || l_task_req_matrl_tbl.COUNT
667 );
668 END IF;
669
670 IF l_task_req_matrl_tbl.COUNT > 0 THEN
671 j := 1;
672 FOR i IN l_task_req_matrl_tbl.FIRST..l_task_req_matrl_tbl.LAST
673 LOOP
674 l_material_avl_tbl(j).inventory_item_id := l_task_req_matrl_tbl(i).inventory_item_id;
675 l_material_avl_tbl(j).item := l_task_req_matrl_tbl(i).item;
676 l_material_avl_tbl(j).visit_task_id := l_task_req_matrl_tbl(i).visit_task_id;
677 -- anraj : this line of code was missing and coz of this "For Task" was null after "Check Avail For All"
681 l_material_avl_tbl(j).task_status_meaning := l_task_req_matrl_tbl(i).task_status_meaning;
678 l_material_avl_tbl(j).task_name := l_task_req_matrl_tbl(i).task_name;
679 -- anraj : added the following two lines for task_status_code and task_status_meaning
680 l_material_avl_tbl(j).task_status_code := l_task_req_matrl_tbl(i).task_status_code;
682
683 l_material_avl_tbl(j).req_arrival_date := l_task_req_matrl_tbl(i).req_arrival_date;
684 l_material_avl_tbl(j).uom := l_task_req_matrl_tbl(i).uom_code;
685 l_material_avl_tbl(j).quantity := l_task_req_matrl_tbl(i).quantity;
686 l_material_avl_tbl(j).visit_id := p_visit_id;
687 l_material_avl_tbl(j).schedule_material_id := l_task_req_matrl_tbl(i).schedule_material_id;
688 j := j + 1;
689 --
690 END LOOP;
691 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
692 fnd_log.string
693 (
694 fnd_log.level_statement,
695 'ahl.plsql.AHL_LTP_MATRL_AVAL_PUB.Check_Materials_For_All',
696 ' Before Calling Ahl ltp Matrl aval pvt Check Material Aval, Number of Records: ' || l_material_avl_tbl.COUNT
697 );
698 END IF;
699 --Call check material
700 AHL_LTP_MATRL_AVAL_PVT.Check_Material_Aval
701 (
702 p_api_version => p_api_version,
703 p_init_msg_list => p_init_msg_list,
704 p_commit => p_commit,
705 p_validation_level => p_validation_level,
706 p_module_type => p_module_type,
707 p_x_material_avl_tbl => l_material_avl_tbl,
708 x_return_status => l_return_status,
709 x_msg_count => l_msg_count,
710 x_msg_data => l_msg_data );
711 END IF;
712 --
713 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
714 fnd_log.string
715 (
716 fnd_log.level_procedure,
717 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||': End API',
718 'After calling ahl ltp Matrl Aval pvt.Check Material Aval, Return Status : '|| l_return_status
719 );
720 END IF;
721
722 -- Check Error Message stack.
723 IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
724 l_msg_count := FND_MSG_PUB.count_msg;
725 IF l_msg_count > 0 THEN
726 RAISE FND_API.G_EXC_ERROR;
727 END IF;
728 END IF;
729
730 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
731 fnd_log.string
732 (
733 fnd_log.level_statement,
734 'ahl.plsql.AHL_LTP_MATRL_AVAL_PUB.Check_Materials_For_All',
735 ' Before Assigning , Number of Records: ' || l_material_avl_tbl.COUNT
736 );
737 END IF;
738
739 IF l_material_avl_tbl.COUNT > 0 THEN
740 FOR i IN l_material_avl_tbl.FIRST..l_material_avl_tbl.LAST
741 LOOP
742 x_task_matrl_aval_tbl(i).visit_task_id := l_material_avl_tbl(i).visit_task_id;
743 x_task_matrl_aval_tbl(i).task_name := l_material_avl_tbl(i).task_name;
744 -- added these two lines of code for the material availability UI
745 x_task_matrl_aval_tbl(i).task_status_code := l_material_avl_tbl(i).task_status_code;
746 x_task_matrl_aval_tbl(i).task_status_meaning := l_material_avl_tbl(i).task_status_meaning;
747
748 x_task_matrl_aval_tbl(i).inventory_item_id := l_material_avl_tbl(i).inventory_item_id;
749 x_task_matrl_aval_tbl(i).item := l_material_avl_tbl(i).item;
750 x_task_matrl_aval_tbl(i).req_arrival_date := l_material_avl_tbl(i).req_arrival_date;
751 x_task_matrl_aval_tbl(i).uom := l_material_avl_tbl(i).uom;
752 x_task_matrl_aval_tbl(i).quantity := l_material_avl_tbl(i).quantity;
753 x_task_matrl_aval_tbl(i).quantity_available := l_material_avl_tbl(i).quantity_available;
754 x_task_matrl_aval_tbl(i).schedule_material_id:= l_material_avl_tbl(i).schedule_material_id;
755 x_task_matrl_aval_tbl(i).error_code := l_material_avl_tbl(i).error_code;
756 x_task_matrl_aval_tbl(i).error_message := l_material_avl_tbl(i).item||' '||l_material_avl_tbl(i).error_message;
757 --SKPATHAK :: Bug 8392521 :: 02-APR-2009
758 --Make the schedule date returned by the private API, available to the out param of the public API
759 x_task_matrl_aval_tbl(i).scheduled_date := l_material_avl_tbl(i).scheduled_date;
760
761 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
762 fnd_log.string
763 (
764 fnd_log.level_statement,
765 'ahl.plsql.AHL_LTP_MATRL_AVAL_PUB.Check_Materials_For_All',
766 ' Derieved Value, Visit Task Id: ' || x_task_matrl_aval_tbl(i).visit_task_id
767 );
768 fnd_log.string
769 (
770 fnd_log.level_statement,
771 'ahl.plsql.AHL_LTP_MATRL_AVAL_PUB.Check_Materials_For_All',
772 ' Derieved Value, Inventory Item Id: ' || x_task_matrl_aval_tbl(i).inventory_item_id
773 );
774 fnd_log.string
775 (
776 fnd_log.level_statement,
777 'ahl.plsql.AHL_LTP_MATRL_AVAL_PUB.Check_Materials_For_All',
778 ' Derieved Value, Quantity: ' || x_task_matrl_aval_tbl(i).quantity
779 );
780 fnd_log.string
781 (
782 fnd_log.level_statement,
783 'ahl.plsql.AHL_LTP_MATRL_AVAL_PUB.Check_Materials_For_All',
784 ' Derieved Value, Quantity Available: ' || x_task_matrl_aval_tbl(i).quantity_available
785 );
786 fnd_log.string
787 (
788 fnd_log.level_statement,
789 'ahl.plsql.AHL_LTP_MATRL_AVAL_PUB.Check_Materials_For_All',
790 'at last x_task_matrl_aval_tbl(i).scheduled_date ' || x_task_matrl_aval_tbl(i).scheduled_date
791 );
792
793 END IF;
794
795 END LOOP;
796 END IF;
797 END IF;
798
802 l_msg_count := FND_MSG_PUB.count_msg;
799 ------------------------End of Body---------------------------------------
800 --Standard check to count messages
801 IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
803 IF l_msg_count > 0 THEN
804 RAISE FND_API.G_EXC_ERROR;
805 END IF;
806 END IF;
807
808 --Standard check for commit
809 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
810 COMMIT;
811 END IF;
812 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
813 fnd_log.string
814 (
815 fnd_log.level_procedure,
816 'ahl.plsql.AHL_LTP_MATRL_AVAL_PUB.Check Materials For All.end',
817 'At the end of PLSQL procedure'
818 );
819 END IF;
820
821 EXCEPTION
822 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
823 ROLLBACK TO check_materials_for_all;
824 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
825 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
826 p_count => x_msg_count,
827 p_data => x_msg_data);
828
829 WHEN FND_API.G_EXC_ERROR THEN
830 ROLLBACK TO check_materials_for_all;
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 => x_msg_count,
834 p_data => X_msg_data);
835
836 WHEN OTHERS THEN
837 ROLLBACK TO check_materials_for_all;
838 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
839 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
840 THEN
841 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_MATRL_AVAL_PUB',
842 p_procedure_name => 'CHECK_MATERIALS_FOR_ALL',
843 p_error_text => SUBSTR(SQLERRM,1,240));
844 END IF;
845 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
846 p_count => x_msg_count,
847 p_data => X_msg_data);
848
849 END Check_Materials_For_All;
850 --
851 -- Start of Comments --
852 -- Procedure name : Schedule_Planned_Mtrls
853 -- Type : Public
854 -- Function : This procedure calls ATP to schedule planned materials
855 -- for Routine jobs derived requested quantity and task start date
856 -- Pre-reqs :
857 -- Parameters :
858 --
859 -- Standard IN Parameters :
860 -- p_api_version IN NUMBER Required
861 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
862 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
863 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
864 -- Based on this flag, the API will set the default attributes.
865 -- p_module_type In VARCHAR2 Default NULL
866 -- This will be null.
867 -- Standard OUT Parameters :
868 -- x_return_status OUT VARCHAR2 Required
869 -- x_msg_count OUT NUMBER Required
870 -- x_msg_data OUT VARCHAR2 Required
871 --
872 -- Schedule_Planned_Matrls Parameters :
873 -- p_x_planned_matrls_tbl IN OUT NOCOPY Planned_Matrls_Tbl,Required
874 -- List of item attributes associated to visit task
875 --
876 PROCEDURE Schedule_Planned_Matrls (
877 p_api_version IN NUMBER,
878 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
879 p_commit IN VARCHAR2 := FND_API.g_false,
880 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
881 p_module_type IN VARCHAR2 := 'JSP',
882 p_x_planned_matrl_tbl IN OUT NOCOPY Planned_Matrl_Tbl,
883 x_return_status OUT NOCOPY VARCHAR2,
884 x_msg_count OUT NOCOPY NUMBER,
885 x_msg_data OUT NOCOPY VARCHAR2)
886
887 IS
888 --Standard local variables
889 l_api_name CONSTANT VARCHAR2(30) := 'Schedule_Planned_Matrls';
890 l_api_version CONSTANT NUMBER := 1.0;
891 l_msg_count NUMBER;
892 l_return_status VARCHAR2(1);
893 l_mat_return_status VARCHAR2(1);
894 l_msg_data VARCHAR2(2000);
895 l_commit VARCHAR2(10) := FND_API.g_false;
896 --
897 l_planned_matrl_tbl Planned_Matrl_Tbl := p_x_planned_matrl_tbl;
898
899 BEGIN
900
901 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
902 fnd_log.string
903 (
904 fnd_log.level_procedure,
905 'ahl.plsql.AHL_LTP_MATRL_AVAL_PUB.Schedule_Planned_Matrls.begin',
906 'At the start of PLSQL procedure'
907 );
908 END IF;
909
910 -- Standard Start of API savepoint
911 SAVEPOINT Schedule_Planned_Matrls;
912 -- Initialize message list if p_init_msg_list is set to TRUE.
913 IF FND_API.to_boolean(p_init_msg_list)
914 THEN
915 FND_MSG_PUB.initialize;
916 END IF;
917 -- Initialize API return status to success
918 x_return_status := FND_API.G_RET_STS_SUCCESS;
919 -- Standard call to check for call compatibility.
920 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
921 p_api_version,
922 l_api_name,G_PKG_NAME)
923 THEN
924 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
925 END IF;
926
927 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
928 fnd_log.string
929 (
930 fnd_log.level_statement,
931 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
932 'Request for Schedule Planned Materials for Number of Records : ' || P_x_Planned_Matrl_Tbl.COUNT
936
933 );
934
935 END IF;
937 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
938 fnd_log.string
939 (
940 fnd_log.level_procedure,
941 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
942 'Before calling ahl ltp matrl aval pvt.Schedule Planned Matrls'
943 );
944
945 END IF;
946
947 -- Private Api to Process
948 AHL_LTP_MATRL_AVAL_PVT.Schedule_Planned_Matrls
949 (p_api_version => p_api_version,
950 p_init_msg_list => p_init_msg_list,
951 p_commit => l_commit,
952 p_validation_level => p_validation_level,
953 p_x_planned_matrl_tbl => l_Planned_Matrl_Tbl,
954 x_return_status => l_return_status,
955 x_msg_count => l_msg_count,
956 x_msg_data => l_msg_data);
957
958 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
959 fnd_log.string
960 (
961 fnd_log.level_procedure,
962 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||': End API',
963 'After calling ahl ltp matrl aval pvt.Schedule Planned Matrls, Return Status : '|| l_return_status
964 );
965 END IF;
966
967 -- Check Error Message stack.
968 IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
969 l_msg_count := FND_MSG_PUB.count_msg;
970 IF l_msg_count > 0 THEN
971 RAISE FND_API.G_EXC_ERROR;
972 END IF;
973 END IF;
974 IF l_planned_matrl_tbl.COUNT > 0 THEN
975 FOR i IN l_planned_matrl_tbl.FIRST..l_planned_matrl_tbl.LAST
976 LOOP
977 --Assign
978 p_x_planned_matrl_tbl(i).schedule_material_id := l_planned_matrl_tbl(i).schedule_material_id;
979 p_x_planned_matrl_tbl(i).quantity_available := l_planned_matrl_tbl(i).quantity_available;
980 p_x_planned_matrl_tbl(i).object_version_number := l_planned_matrl_tbl(i).object_version_number;
981 p_x_planned_matrl_tbl(i).inventory_item_id := l_planned_matrl_tbl(i).inventory_item_id;
982 p_x_planned_matrl_tbl(i).item_description := l_planned_matrl_tbl(i).item_description;
983 p_x_planned_matrl_tbl(i).visit_id := l_planned_matrl_tbl(i).visit_id;
984 p_x_planned_matrl_tbl(i).visit_task_id := l_planned_matrl_tbl(i).visit_task_id;
985 p_x_planned_matrl_tbl(i).task_name := l_planned_matrl_tbl(i).task_name;
986 --SKPATHAK :: Bug 8392521 :: 02-APR-2009
987 --Make the schedule date returned by the private API, available to the out param of the public API
988 p_x_planned_matrl_tbl(i).scheduled_date := l_planned_matrl_tbl(i).scheduled_date;
989 p_x_planned_matrl_tbl(i).requested_date := l_planned_matrl_tbl(i).requested_date;
990 p_x_planned_matrl_tbl(i).required_quantity := l_planned_matrl_tbl(i).required_quantity;
991 -- p_x_planned_matrl_tbl(i).scheduled_quantity := l_planned_matrl_tbl(i).scheduled_quantity;
992 p_x_planned_matrl_tbl(i).primary_uom := l_planned_matrl_tbl(i).primary_uom;
993 p_x_planned_matrl_tbl(i).error_code := l_planned_matrl_tbl(i).error_code;
994 p_x_planned_matrl_tbl(i).error_message := l_planned_matrl_tbl(i).error_message;
995
996 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
997 fnd_log.string
998 (
999 fnd_log.level_statement,
1000 'ahl.plsql.AHL_LTP_MATRL_AVAL_PUB.Schedule Planned Matrls',
1001 ' Derieved Value, Schedule Material Id: ' || p_x_planned_matrl_tbl(i).schedule_material_id
1002 );
1003 fnd_log.string
1004 (
1005 fnd_log.level_statement,
1006 'ahl.plsql.AHL_LTP_MATRL_AVAL_PUB.Schedule Planned Matrls',
1007 ' Derieved Value, Available Quantity: ' || p_x_planned_matrl_tbl(i).quantity_available
1008 );
1009 fnd_log.string
1010 (
1011 fnd_log.level_statement,
1012 'ahl.plsql.AHL_LTP_MATRL_AVAL_PUB.Schedule Planned Matrls',
1013 'p_x_planned_matrl_tbl(i).scheduled_date: ' || p_x_planned_matrl_tbl(i).scheduled_date
1014 );
1015
1016 END IF;
1017
1018 END LOOP;
1019 END IF;
1020 --Standard check for commit
1021 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
1022 COMMIT;
1023 END IF;
1024
1025 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
1026 fnd_log.string
1027 (
1028 fnd_log.level_procedure,
1029 'ahl.plsql.AHL_LTP_MATRL_AVAL_PUB.Schedule_Planned_Matrls.end',
1030 'At the end of PLSQL procedure'
1031 );
1032 END IF;
1033
1034 EXCEPTION
1035
1036 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1037 ROLLBACK TO Schedule_Planned_Matrls;
1038 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1039 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1040 p_count => x_msg_count,
1041 p_data => x_msg_data);
1042
1043 WHEN FND_API.G_EXC_ERROR THEN
1044 ROLLBACK TO Schedule_Planned_Matrls;
1045 X_return_status := FND_API.G_RET_STS_ERROR;
1046 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1047 p_count => x_msg_count,
1048 p_data => X_msg_data);
1049
1050 WHEN OTHERS THEN
1051 ROLLBACK TO Schedule_Planned_Matrls;
1052 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1053 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1054 THEN
1055 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_MATRL_AVAL_PUB',
1056 p_procedure_name => 'SCHEDULE_PLANNED_MATRLS',
1057 p_error_text => SUBSTR(SQLERRM,1,240));
1058 END IF;
1059 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1060 p_count => x_msg_count,
1061 p_data => X_msg_data);
1062
1063 END Schedule_Planned_Matrls;
1067 -- Type : Public
1064
1065 -- Start of Comments --
1066 -- Procedure name : Schedule_All_Materials
1068 -- Function : This procedure calls ATP to schedule planned materials
1069 -- for Routine jobs derived requested quantity and task start date
1070 -- Pre-reqs :
1071 -- Parameters :
1072 --
1073 -- Standard IN Parameters :
1074 -- p_api_version IN NUMBER Required
1075 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
1076 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
1077 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
1078 -- Based on this flag, the API will set the default attributes.
1079 -- p_module_type In VARCHAR2 Default NULL
1080 -- This will be null.
1081 -- Standard OUT Parameters :
1082 -- x_return_status OUT VARCHAR2 Required
1083 -- x_msg_count OUT NUMBER Required
1084 -- x_msg_data OUT VARCHAR2 Required
1085 --
1086 -- Schedule_All_Materials Parameters :
1087 -- p_visit_id IN NUMBER Required,
1088 -- List of item attributes associated to visit task
1089 --
1090 PROCEDURE Schedule_All_Materials (
1091 p_api_version IN NUMBER,
1092 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1093 p_commit IN VARCHAR2 := FND_API.g_false,
1094 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1095 p_module_type IN VARCHAR2 := 'JSP',
1096 p_visit_id IN NUMBER,
1097 x_planned_matrl_tbl OUT NOCOPY Planned_Matrl_Tbl,
1098 x_return_status OUT NOCOPY VARCHAR2,
1099 x_msg_count OUT NOCOPY NUMBER,
1100 x_msg_data OUT NOCOPY VARCHAR2)
1101
1102 IS
1103
1104 -- anraj added for checking whether atleast one task is in status planning
1105 CURSOR c_any_task_in_planning (c_visit_id IN NUMBER)
1106 IS
1107 SELECT 1
1108 FROM AHL_VISIT_TASKS_B
1109 WHERE visit_id = c_visit_id
1110 AND status_code = 'PLANNING';
1111 l_dummy number;
1112 --Standard local variables
1113 l_api_name CONSTANT VARCHAR2(30) := 'Schedule_All_Materials';
1114 l_api_version CONSTANT NUMBER := 1.0;
1115 l_msg_count NUMBER;
1116 l_return_status VARCHAR2(1);
1117 l_msg_data VARCHAR2(2000);
1118 l_commit VARCHAR2(10) := FND_API.g_false;
1119 l_planned_matrl_tbl Planned_Matrl_Tbl;
1120
1121 BEGIN
1122
1123 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
1124 fnd_log.string
1125 (
1126 fnd_log.level_procedure,
1127 'ahl.plsql.AHL_LTP_MATRL_AVAL_PUB.Schedule_All_Materials.begin',
1128 'At the start of PLSQL procedure'
1129 );
1130 END IF;
1131
1132 -- Standard Start of API savepoint
1133 SAVEPOINT Schedule_All_Materials;
1134 -- Initialize message list if p_init_msg_list is set to TRUE.
1135 IF FND_API.to_boolean(p_init_msg_list)
1136 THEN
1137 FND_MSG_PUB.initialize;
1138 END IF;
1139 -- Initialize API return status to success
1140 x_return_status := FND_API.G_RET_STS_SUCCESS;
1141 -- Standard call to check for call compatibility.
1142 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
1143 p_api_version,
1144 l_api_name,G_PKG_NAME)
1145 THEN
1146 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1147 END IF;
1148
1149 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1150 fnd_log.string
1151 (
1152 fnd_log.level_statement,
1153 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1154 'Request for Schedule All Materials for Visit Id : ' || P_visit_id
1155 );
1156
1157 END IF;
1158
1159 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1160 fnd_log.string
1161 (
1162 fnd_log.level_procedure,
1163 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1164 'Before calling ahl ltp matrl aval pvt.Schedule All Materials'
1165 );
1166
1167 END IF;
1168
1169 --anraj, validation to check whether atleast one task is in planning
1170 OPEN c_any_task_in_planning(p_visit_id);
1171 FETCH c_any_task_in_planning INTO l_dummy;
1172 IF c_any_task_in_planning%NOTFOUND THEN
1173 Fnd_Message.SET_NAME('AHL','AHL_LTP_SCHEDULE_ALL_NONE_PLAN');
1174 Fnd_Msg_Pub.ADD;
1175 CLOSE c_any_task_in_planning;
1176 RAISE Fnd_Api.G_EXC_ERROR;
1177 END IF;
1178 CLOSE c_any_task_in_planning;
1179
1180
1181
1182 -- Private Api to Process
1183 AHL_LTP_MATRL_AVAL_PVT.Schedule_All_Materials
1184 (p_api_version => p_api_version,
1185 p_init_msg_list => p_init_msg_list,
1186 p_commit => l_commit,
1187 p_validation_level => p_validation_level,
1188 p_visit_id => p_visit_id,
1189 x_planned_matrl_tbl => l_Planned_Matrl_Tbl,
1190 x_return_status => l_return_status,
1191 x_msg_count => l_msg_count,
1192 x_msg_data => l_msg_data);
1193
1194 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1195 fnd_log.string
1196 (
1197 fnd_log.level_procedure,
1198 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||': End API',
1199 'After calling ahl ltp matrl aval pvt.Schedule All Materials, Return Status : '|| l_return_status
1200 );
1201 END IF;
1202
1203 -- Check Error Message stack.
1207 RAISE FND_API.G_EXC_ERROR;
1204 IF (l_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
1208 END IF;
1209 END IF;
1210
1211 IF l_Planned_Matrl_Tbl.COUNT > 0 THEN
1212 FOR i IN l_Planned_Matrl_Tbl.FIRST..l_Planned_Matrl_Tbl.LAST
1213 LOOP
1214 --Assign
1215 X_Planned_Matrl_Tbl(i).schedule_material_id := l_Planned_Matrl_Tbl(i).schedule_material_id;
1216 X_Planned_Matrl_Tbl(i).quantity_available := l_Planned_Matrl_Tbl(i).quantity_available;
1217 X_Planned_Matrl_Tbl(i).object_version_number := l_Planned_Matrl_Tbl(i).object_version_number;
1218 X_Planned_Matrl_Tbl(i).inventory_item_id := l_Planned_Matrl_Tbl(i).inventory_item_id;
1219 X_Planned_Matrl_Tbl(i).item_description := l_Planned_Matrl_Tbl(i).item_description;
1220 X_Planned_Matrl_Tbl(i).visit_id := l_Planned_Matrl_Tbl(i).visit_id;
1221 X_Planned_Matrl_Tbl(i).visit_task_id := l_Planned_Matrl_Tbl(i).visit_task_id;
1222 X_Planned_Matrl_Tbl(i).task_name := l_Planned_Matrl_Tbl(i).task_name;
1223 -- anraj added fot the Material Availability UI
1224 X_Planned_Matrl_Tbl(i).task_status_code := l_Planned_Matrl_Tbl(i).task_status_code;
1225 X_Planned_Matrl_Tbl(i).task_status_meaning := l_Planned_Matrl_Tbl(i).task_status_meaning;
1226
1227 --SKPATHAK :: Bug 8392521 :: 02-APR-2009
1228 --Make the schedule date returned by the private API, available to the out param of the public API
1229 X_Planned_Matrl_Tbl(i).scheduled_date := l_Planned_Matrl_Tbl(i).scheduled_date;
1230 X_Planned_Matrl_Tbl(i).requested_date := l_Planned_Matrl_Tbl(i).requested_date;
1231 X_Planned_Matrl_Tbl(i).required_quantity := l_Planned_Matrl_Tbl(i).required_quantity;
1232 -- X_Planned_Matrl_Tbl(i).scheduled_quantity := l_Planned_Matrl_Tbl(i).scheduled_quantity;
1233 X_Planned_Matrl_Tbl(i).primary_uom := l_Planned_Matrl_Tbl(i).primary_uom;
1234 X_Planned_Matrl_Tbl(i).error_code := l_Planned_Matrl_Tbl(i).error_code;
1235 X_Planned_Matrl_Tbl(i).error_message := l_Planned_Matrl_Tbl(i).error_message;
1236
1237 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1238 fnd_log.string
1239 (
1240 fnd_log.level_statement,
1241 'ahl.plsql.AHL_LTP_MATRL_AVAL_PUB.Schedule Planned Matrls',
1242 ' Derieved Value, Schedule Material Id: ' || X_Planned_Matrl_Tbl(i).schedule_material_id
1243 );
1244 fnd_log.string
1245 (
1246 fnd_log.level_statement,
1247 'ahl.plsql.AHL_LTP_MATRL_AVAL_PUB.Schedule Planned Matrls',
1248 ' Derieved Value, Available Quantity: ' || X_Planned_Matrl_Tbl(i).quantity_available
1249 );
1250 fnd_log.string
1251 (
1252 fnd_log.level_statement,
1253 'ahl.plsql.AHL_LTP_MATRL_AVAL_PUB.Schedule Planned Matrls',
1254 'X_Planned_Matrl_Tbl(i).scheduled_date: ' ||X_Planned_Matrl_Tbl(i).scheduled_date
1255 );
1256
1257 END IF;
1258
1259 END LOOP;
1260 END IF;
1261
1262 --Standard check for commit
1263 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
1264 COMMIT;
1265 END IF;
1266
1267 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
1268 fnd_log.string
1269 (
1270 fnd_log.level_procedure,
1271 'ahl.plsql.AHL_LTP_MATRL_AVAL_PUB.Schedule_All_Materials.end',
1272 'At the end of PLSQL procedure'
1273 );
1274 END IF;
1275
1276 EXCEPTION
1277 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1278 ROLLBACK TO Schedule_All_Materials;
1279 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1280 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1281 p_count => x_msg_count,
1282 p_data => x_msg_data);
1283
1284 WHEN FND_API.G_EXC_ERROR THEN
1285 ROLLBACK TO Schedule_All_Materials;
1286 X_return_status := FND_API.G_RET_STS_ERROR;
1287 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1288 p_count => x_msg_count,
1289 p_data => X_msg_data);
1290
1291 WHEN OTHERS THEN
1292 ROLLBACK TO Schedule_All_Materials;
1293 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1294 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1295 THEN
1296 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_MATRL_AVAL_PUB',
1297 p_procedure_name => 'SCHEDULE_ALL_MATERIALS',
1298 p_error_text => SUBSTR(SQLERRM,1,240));
1299 END IF;
1300 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1301 p_count => x_msg_count,
1302 p_data => X_msg_data);
1303
1304 END Schedule_All_Materials;
1305
1306 --
1307 END AHL_LTP_MATRL_AVAL_PUB;