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