[Home] [Help]
PACKAGE BODY: APPS.AHL_LTP_REQST_MATRL_PVT
Source
1 PACKAGE BODY AHL_LTP_REQST_MATRL_PVT AS
2 /* $Header: AHLVRMTB.pls 120.22 2008/03/20 10:30:07 rnahata ship $ */
3 --
4 G_PKG_NAME VARCHAR2(30) := 'AHL_LTP_REQST_MATRL_PVT';
5 G_DEBUG VARCHAR2(1) := AHL_DEBUG_PUB.is_log_enabled;
6
7 ------------------------------------
8 -- Common constants and variables --
9 ------------------------------------
10 l_log_current_level NUMBER := fnd_log.g_current_runtime_level;
11 l_log_statement NUMBER := fnd_log.level_statement;
12 l_log_procedure NUMBER := fnd_log.level_procedure;
13 l_log_error NUMBER := fnd_log.level_error;
14 l_log_unexpected NUMBER := fnd_log.level_unexpected;
15 -----------------------------------------------------------------
16
17 -- Definition of private procedure.
18 --
19 PROCEDURE Modify_Visit_Reservations (
20 p_visit_id IN NUMBER,
21 x_return_status OUT NOCOPY VARCHAR2);
22
23 -- PROCEDURE
24 -- anraj added
25 Procedure Unschedule_Visit_Materials (
26 p_api_version IN NUMBER,
27 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
28 p_commit IN VARCHAR2 := FND_API.g_false,
29 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
30 p_visit_id IN NUMBER,
31 x_return_status OUT NOCOPY VARCHAR2,
32 x_msg_count OUT NOCOPY NUMBER,
33 x_msg_data OUT NOCOPY VARCHAR2
34 )
35 IS
36 CURSOR c_sch_mat_cur (c_visit_id IN NUMBER)
37 IS
38 SELECT scheduled_material_id,
39 object_version_number
40 FROM ahl_schedule_materials
41 WHERE visit_id = c_visit_id;
42
43 CURSOR c_visit_task_matrl_cur(c_sch_mat_id IN NUMBER)
44 IS
45 SELECT scheduled_date,scheduled_quantity
46 FROM ahl_visit_task_matrl_v
47 WHERE schedule_material_id = c_sch_mat_id;
48
49 l_api_name CONSTANT VARCHAR2(30) := 'Unschedule_Visit_Materials';
50 l_api_version CONSTANT NUMBER := 1.0;
51 l_msg_count NUMBER;
52 l_return_status VARCHAR2(1);
53 l_msg_data VARCHAR2(2000);
54 l_dummy NUMBER;
55 /*l_rowid VARCHAR2(30);
56 l_organization_id NUMBER;
57 l_department_id NUMBER;
58 l_visit_id NUMBER;*/
59 l_object_version_number NUMBER;
60 /* l_start_date_time DATE;
61 l_space_assignment_id NUMBER;
62 l_space_version_number NUMBER;
63 l_visit_status_code VARCHAR2(30);
64 l_meaning VARCHAR2(80);*/
65 l_schedule_material_id NUMBER;
66 l_scheduled_date DATE;
67 l_scheduled_quantity NUMBER;
68
69 /*_visit_tbl AHL_VWP_VISITS_PVT.Visit_Tbl_Type;
70 i NUMBER := 0;
71 l_visit_name VARCHAR2(80);
72 */
73 BEGIN
74 --------------------Initialize ----------------------------------
75 -- Standard Start of API savepoint
76 SAVEPOINT unschedule_visit;
77 -- Check if API is called in debug mode. If yes, enable debug.
78 IF G_DEBUG='Y' THEN
79 AHL_DEBUG_PUB.enable_debug;
80 END IF;
81 -- Debug info.
82 IF G_DEBUG='Y' THEN
83 AHL_DEBUG_PUB.debug( 'enter AHL_LTP_REQST_MATRL_PVT.Unschedule_Visit_Materials','+SPANT+');
84 END IF;
85 -- Standard call to check for call compatibility.
86 IF FND_API.to_boolean(p_init_msg_list)
87 THEN
88 FND_MSG_PUB.initialize;
89 END IF;
90 -- Initialize API return status to success
91 x_return_status := FND_API.G_RET_STS_SUCCESS;
92 -- Initialize message list if p_init_msg_list is set to TRUE.
93 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
94 p_api_version,
95 l_api_name,G_PKG_NAME)
96 THEN
97 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
98 END IF;
99 --Check for material scheduling
100 OPEN c_sch_mat_cur(p_visit_id);
101 LOOP
102 FETCH c_sch_mat_cur INTO l_schedule_material_id, l_object_version_number;
103 EXIT WHEN c_sch_mat_cur%NOTFOUND;
104 IF l_schedule_material_id IS NOT NULL THEN
105 --Check for Item scheduled
106 OPEN c_visit_task_matrl_cur(l_schedule_material_id);
107 FETCH c_visit_task_matrl_cur INTO l_scheduled_date,l_scheduled_quantity;
108 IF l_scheduled_date IS NOT NULL THEN
109 Fnd_Message.SET_NAME('AHL','AHL_LTP_MRP_SCHEDUl_ITEM');
110 Fnd_Msg_Pub.ADD;
111 CLOSE c_visit_task_matrl_cur;
112 RAISE Fnd_Api.G_EXC_ERROR;
113 ELSE
114 UPDATE ahl_schedule_materials
115 SET requested_quantity = 0,
116 status = 'DELETED',
117 object_version_number = l_object_version_number + 1,
118 last_update_date = SYSDATE,
119 last_updated_by = Fnd_Global.user_id,
120 last_update_login = Fnd_Global.login_id
121 WHERE scheduled_material_id = l_schedule_material_id;
122 --
123 END IF; --Scheduled date
124 CLOSE c_visit_task_matrl_cur;
125 END IF;-- Scheduled mat id
126 END LOOP;
127 CLOSE c_sch_mat_cur;
128
129 -- Serial Number reservation Enh.
130 -- When a Visit is unscheduled, all the reservations made for the Visit should also be deleted
131 AHL_RSV_RESERVATIONS_PVT.DELETE_VISIT_RESERVATIONS(
132 X_RETURN_STATUS => X_RETURN_STATUS,
133 P_VISIT_ID => p_visit_id);
134 IF (l_log_statement >= l_log_current_level)THEN
135 fnd_log.string
136 (
137 l_log_statement,
138 'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Remove_Visit_Task_Matrls',
139 ' After calling AHL_RSV_RESERVATIONS_PVT.DELETE_VISIT_RESERVATIONS ErrorMessage Status : ' || X_RETURN_STATUS
140 );
141 END IF;
142
143 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
144 RAISE Fnd_Api.g_exc_error;
145 END IF;
146
147 IF FND_API.TO_BOOLEAN(p_commit) THEN
148 COMMIT WORK;
149 END IF;
150
151 EXCEPTION
152 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
153 ROLLBACK TO unschedule_visit;
154 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
155 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
156 p_count => x_msg_count,
157 p_data => x_msg_data);
158 IF G_DEBUG='Y' THEN
159 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'ERROR' );
160 AHL_DEBUG_PUB.debug( 'AHL_LTP_REQST_MATRL_PVT.Unschedule_Visit_Materials','+SPANT+');
161 -- Check if API is called in debug mode. If yes, disable debug.
162 AHL_DEBUG_PUB.disable_debug;
163 END IF;
164 WHEN FND_API.G_EXC_ERROR THEN
165 ROLLBACK TO unschedule_visit;
166 X_return_status := FND_API.G_RET_STS_ERROR;
167 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
168 p_count => x_msg_count,
169 p_data => X_msg_data);
170 IF G_DEBUG='Y' THEN
171 -- Debug info.
172 AHL_DEBUG_PUB.log_app_messages (
173 x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
174 AHL_DEBUG_PUB.debug( 'AHL_LTP_REQST_MATRL_PVT.Unschedule_Visit_Materials','+SPANT+');
175 -- Check if API is called in debug mode. If yes, disable debug.
176 AHL_DEBUG_PUB.disable_debug;
177 END IF;
178 WHEN OTHERS THEN
179 ROLLBACK TO unschedule_visit;
180 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
181 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
182 THEN
183 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_REQST_MATRL_PVT',
184 p_procedure_name => 'Unschedule_Visit_Materials ',
185 p_error_text => SUBSTR(SQLERRM,1,240));
186 END IF;
187 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
188 p_count => x_msg_count,
189 p_data => X_msg_data);
190 IF G_DEBUG='Y' THEN
191 -- Debug info.
192 AHL_DEBUG_PUB.log_app_messages (
193 x_msg_count, x_msg_data, 'SQL ERROR' );
194 AHL_DEBUG_PUB.debug( 'ahl_ltp_space_assign_pvt.Unschedule_Visit_Materials','+SPANT+');
195 -- Check if API is called in debug mode. If yes, disable debug.
196 AHL_DEBUG_PUB.disable_debug;
197 END IF;
198 END Unschedule_Visit_Materials;
199
200
201
202 -- PROCEDURE
203 -- Insert_Planned_Matrls
204 --
205 -- PURPOSE
206 -- Creates record in ahl_schedule_materials
207 --
208 -- PARAMETERS
209 --
210 -- NOTES
211
212 PROCEDURE Insert_Planned_Matrls(
213 p_visit_id IN NUMBER,
214 p_visit_task_id IN NUMBER,
215 p_task_start_date IN DATE,
216 p_inventory_item_id IN NUMBER,
217 p_requested_quantity IN NUMBER,
218 p_uom_code IN VARCHAR2,
219 p_item_group_id IN NUMBER,
220 p_rt_oper_material_id IN NUMBER,
221 p_position_path_id IN NUMBER,
222 p_relationship_id IN NUMBER,
223 p_mr_route_id IN NUMBER default null,
224 p_item_comp_detail_id IN NUMBER default null,
225 p_inv_master_org_id IN NUMBER default null,
226 x_return_status OUT NOCOPY VARCHAR2,
227 x_msg_count OUT NOCOPY NUMBER,
228 x_msg_data OUT NOCOPY VARCHAR2
229 )
230 IS
231 -- Check for record already exists
232 CURSOR check_matrl_cur (c_visit_id IN NUMBER,
233 c_visit_task_id IN NUMBER,
234 c_rt_oper_mat_id IN NUMBER)
235 IS
236 -- yazhou 17-May-2006 starts
237 -- bug fix#5232544
238
239 -- yazhou 03-JUL-2006 starts
240 -- bug fix#5303378
241
242 SELECT scheduled_material_id
243 FROM AHL_SCHEDULE_MATERIALS
244 WHERE visit_id = c_visit_id
245 AND visit_task_id = c_visit_task_id
246 -- AND requested_quantity <> 0
247 AND NVL(status,'') = 'ACTIVE'
248 AND rt_oper_material_id = c_rt_oper_mat_id;
249
250 -- yazhou 03-JUL-2006 ends
251
252 -- yazhou 17-May-2006 ends
253
254 -- Cursor to get organization and schedule designator
255 CURSOR get_org_cur (c_visit_id IN NUMBER)
256 IS
257 SELECT organization_id
258 FROM ahl_visits_b
259 WHERE visit_id = c_visit_id;
260 --Get priority item from item associations
261 CURSOR Get_Prior_Item_Cur(C_ITEM_GROUP_ID IN NUMBER,
262 C_ORG_ID IN NUMBER)
263 IS
264 SELECT it.inventory_item_id,
265 it.priority,
266 it.uom_code,
267 it.quantity
268 FROM ahl_item_associations_vl it,
269 mtl_system_items_vl mt
270 WHERE it.inventory_item_id = mt.inventory_item_id
271 AND item_group_id = C_ITEM_GROUP_ID
272 AND mt.organization_id = C_ORG_ID
273 -- Fix for bug # 4109330
274 AND it.interchange_type_code in ('1-WAY INTERCHANGEABLE', '2-WAY INTERCHANGEABLE')
275 ORDER BY priority;
276 --Bug Fix #4104968
277 CURSOR get_route_cur (c_visit_task_id IN NUMBER)
278 IS
279 SELECT route_id
280 FROM ahl_mr_routes mr,
281 ahl_visit_tasks_b vt
282 WHERE mr.mr_route_id = vt.mr_route_id
283 AND visit_task_id = c_visit_task_id;
284 --Modifed the cursor for Bug #4104968
285 -- Cursor to get operation sequence and operation id
286 CURSOR get_oper_seq_cur (c_rt_oper_mat_id IN NUMBER,
287 c_route_id IN NUMBER)
288 IS
289 SELECT ro.step,
290 ro.operation_id,
291 ro.concatenated_segments
292 FROM ahl_route_operations_v ro,
293 ahl_rt_oper_materials rm
294 WHERE ro.operation_id = rm.object_id
295 AND ro.route_id = c_route_id
296 AND rm.rt_oper_material_id = c_rt_oper_mat_id
297 AND rm.association_type_code = 'OPERATION';
298 -- Inventory item should exists in visit org
299 CURSOR Check_item_org (C_ITEM_ID IN NUMBER,
300 C_ORG_ID IN NUMBER)
301 IS
302 SELECT inventory_item_id,
303 primary_uom_code
304 FROM mtl_system_items_vl
305 WHERE inventory_item_id = C_ITEM_ID
306 AND organization_id = C_ORG_ID;
307 --Get quanity from rt oper materisl if null
308 CURSOR Quantity_cur (c_rt_oper_mat_id IN NUMBER)
309 IS
310 SELECT quantity,
311 in_service, --B5865210 - sowsubra
312 replace_percent,
313 association_type_code
314 FROM ahl_rt_oper_materials
315 WHERE rt_oper_material_id = c_rt_oper_mat_id;
316
317 --Standard local variables
318 l_api_name CONSTANT VARCHAR2(30) := 'Update_Planned_Materials';
319 l_api_version CONSTANT NUMBER := 1.0;
320 l_msg_data VARCHAR2(2000);
321 l_return_status VARCHAR2(1);
322 l_msg_count NUMBER;
323 l_init_msg_list VARCHAR2(10) := FND_API.g_false;
324 --
325 l_schedule_material_id NUMBER;
326 l_dummy NUMBER;
327 l_organization_id NUMBER;
328 l_operation_id NUMBER;
329 l_inventory_item_id NUMBER := p_inventory_item_id;
330 l_requested_quantity NUMBER := p_requested_quantity;
331 l_inventory_org_item_id NUMBER;
332 l_uom_code VARCHAR2(3) := p_uom_code;
333 l_step NUMBER;
334 l_operation_code VARCHAR2(80);
335 l_prim_uom_code VARCHAR2(3) := null;
336 l_prim_quantity NUMBER;
337 l_replace_percent NUMBER;
338 l_assoc_type_code VARCHAR2(30);
339 l_sched_prim_quantity NUMBER; -- yazhou 04Aug2005
340 l_route_id NUMBER;
341 --
342 l_task_type_code VARCHAR2(30);
343 l_material_request_type VARCHAR2(30);
344 l_Prior_Item_Rec Get_Prior_Item_Cur%ROWTYPE;
345 l_isInservice AHL_RT_OPER_MATERIALS.IN_SERVICE%TYPE; --Added by sowsubra for Issue 105
346 l_mat_status AHL_SCHEDULE_MATERIALS.STATUS%TYPE; --Added by sowsubra for Issue 105
347
348 BEGIN
349
350 IF (l_log_procedure >= l_log_current_level)THEN
351 fnd_log.string
352 (
353 l_log_procedure,
354 'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Insert_Planned_Matrls',
355 'At the start of PLSQL procedure'
356 );
357 END IF;
358 -- Standard start of API savepoint
359 SAVEPOINT Insert_Planned_Matrls;
360 -- Initialize message list if p_init_msg_list is set to TRUE
361 IF FND_API.To_Boolean( l_init_msg_list) THEN
362 FND_MSG_PUB.Initialize;
363 END IF;
364 -- Initialize API return status to success
365 x_return_status := FND_API.G_RET_STS_SUCCESS;
366 --
367 IF (l_log_statement >= l_log_current_level)THEN
368 fnd_log.string
369 (
370 l_log_statement,
371 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
372 'Request for Create Planned Material for Visit Id : '|| p_visit_id
373 );
374
375 END IF;
376
377 --Get the sequence number
378 SELECT ahl_schedule_materials_s.nextval INTO l_schedule_material_id
379 FROM DUAL;
380 --Check for record exists
381 OPEN check_matrl_cur(p_visit_id,p_visit_task_id,p_rt_oper_material_id);
382 FETCH check_matrl_cur INTO l_dummy;
383 CLOSE check_matrl_cur;
384 --Get visit Organization
385 OPEN get_org_cur(p_visit_id);
386 FETCH get_org_cur INTO l_organization_id;
387 CLOSE get_org_cur;
388 --Get Route id
389 OPEN get_route_cur(p_visit_task_id);
390 FETCH get_route_cur INTO l_route_id;
391 CLOSE get_route_cur;
392 --During org change in schedule visits UI
393 IF p_inv_master_org_id IS NOT NULL THEN
394 l_organization_id := p_inv_master_org_id;
395 END IF;
396 -- Get operation sequence
397 OPEN get_oper_seq_cur(p_rt_oper_material_id,l_route_id);
398 FETCH get_oper_seq_cur INTO l_step,l_operation_id,l_operation_code;
399 CLOSE get_oper_seq_cur;
400 --
401 IF ((p_item_comp_detail_id IS NOT NULL AND p_item_group_id IS NOT NULL )
402 OR
403 (p_item_comp_detail_id IS NULL AND p_item_group_id IS NOT NULL ))THEN
404 --Get from item associations
405 OPEN Get_Prior_Item_Cur(p_item_group_id,l_organization_id);
406 FETCH Get_Prior_Item_Cur INTO l_Prior_Item_rec;
407 CLOSE Get_Prior_Item_Cur;
408 --Assign returned values
409 l_inventory_item_id := l_prior_Item_rec.inventory_item_id;
410
411 ELSE
412 IF (p_position_path_id IS NOT NULL AND p_item_group_id IS NOT NULL ) THEN
413 --Get from item associations
414 OPEN Get_Prior_Item_Cur(p_item_group_id,l_organization_id);
415 FETCH Get_Prior_Item_Cur INTO l_Prior_Item_rec;
416 CLOSE Get_Prior_Item_Cur;
417 --Assign returned values
418 l_inventory_item_id := l_prior_Item_rec.inventory_item_id;
419 END IF;
420
421 END IF;
422 --Check for item exists in inventory Ord
423 OPEN Check_item_org(l_inventory_item_id,l_organization_id);
424 FETCH Check_item_org INTO l_inventory_org_item_id,l_prim_uom_code;
425 CLOSE Check_item_org;
426
427 --Check for primayr UOM COde
428 IF l_uom_code <> l_prim_uom_code
429 THEN
430
431 IF (l_log_statement >= l_log_current_level)THEN
432 fnd_log.string
433 (
434 l_log_statement,
435 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
436 'Primary Uom Code : '|| l_prim_uom_code
437 );
438 fnd_log.string
439 (
440 l_log_statement,
441 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
442 'Source Uom Code : '|| l_uom_code
443 );
444
445 END IF;
446
447 -- yazhou 04Aug2005 Starts
448 l_prim_quantity := AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM_Qty
449 (l_inventory_item_id, l_uom_code, l_requested_quantity);
450
451 -- Required to check the UOM Conversion exists in mtl_units_of_measure
452 IF l_prim_quantity IS NULL THEN
453 FND_MESSAGE.Set_Name( 'AHL','AHL_LTP_UOM_CONV_NOT_EXIST' );
454 FND_MESSAGE.Set_Token('FUOM', l_uom_code);
455 FND_MESSAGE.Set_Token('TUOM', l_prim_uom_code);
456 FND_MSG_PUB.add;
457 RAISE FND_API.G_EXC_ERROR;
458 END IF;
459
460 l_sched_prim_quantity := l_prim_quantity;
461
462 -- yazhou 04Aug2005 Ends
463
464 --sowsubra
465 --changes done to collect inservice material
466 OPEN Quantity_cur(p_rt_oper_material_id);
467 FETCH Quantity_cur INTO l_requested_quantity,l_isInservice,l_replace_percent,l_assoc_type_code;
468 CLOSE Quantity_cur;
469
470 -- For Bug # 4007058
471 -- IF l_assoc_type_code = 'DISPOSITION' AND NVL(l_replace_percent,0) < 100 THEN
472 IF NVL(l_replace_percent,100) < 100 THEN
473 l_prim_quantity := 0;
474 END IF;
475
476 --Added by sowsubra for Issue 105
477 IF NVL(l_isInservice,'N') = 'N' THEN
478 l_mat_status := 'ACTIVE';
479 ELSE
480 l_mat_status := 'IN-SERVICE';
481 END IF;
482
483 ELSE
484
485 IF (l_log_statement >= l_log_current_level)THEN
486 fnd_log.string
487 (
488 l_log_statement,
489 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
490 'ELSE Primary Uom Code : '|| l_prim_uom_code
491 );
492 fnd_log.string
493 (
494 l_log_statement,
495 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
496 'ELSE Source Uom Code : '|| l_uom_code
497 );
498 fnd_log.string
499 (
500 l_log_statement,
501 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
502 'ELSE Requested quantity : '|| l_requested_quantity || ' -'||l_requested_quantity
503 );
504
505 END IF;
506
507 -- yazhou 04Aug2005 Starts
508
509 l_sched_prim_quantity := l_requested_quantity;
510
511 --sowsubra
512 --changes done to collect inservice material
513 OPEN Quantity_cur(p_rt_oper_material_id);
514 FETCH Quantity_cur INTO l_requested_quantity,l_isInservice,l_replace_percent,l_assoc_type_code;
515 CLOSE Quantity_cur;
516 -- else passsed value
517 -- For Bug # 4007058
518 -- IF l_assoc_type_code = 'DISPOSITION' AND NVL(l_replace_percent,0) < 100 THEN
519 IF NVL(l_replace_percent,100) < 100 THEN
520 l_prim_quantity := 0;
521 ELSE
522 l_prim_quantity := l_sched_prim_quantity;
523 END IF;
524
525 --Added by sowsubra for Issue 105
526 IF NVL(l_isInservice,'N') = 'N' THEN
527 l_mat_status := 'ACTIVE';
528 ELSE
529 l_mat_status := 'IN-SERVICE';
530 END IF;
531
532 -- yazhou 04Aug2005 Ends
533
534 END IF;
535
536 --Check for visit task type
537 SELECT TASK_TYPE_CODE INTO l_task_type_code
538 FROM ahl_visit_tasks_vl
539 WHERE visit_task_id = p_visit_task_id;
540 --From unplanned and Unassociated
541 IF l_task_type_code IN ('UNPLANNED','UNASSOCIATED') THEN
542 l_material_request_type := 'UNPLANNED';
543 ELSE
544 l_material_request_type := 'PLANNED';
545 END IF;
546
547 IF (l_log_statement >= l_log_current_level)THEN
548 fnd_log.string
549 (
550 l_log_statement,
551 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
552 'Before Insert Schedule Materials for Visit Id : '|| p_visit_id
553 );
554 fnd_log.string
555 (
556 l_log_statement,
557 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
558 'Before Insert Schedule Materials for Visit Task Id : '|| p_visit_task_id
559 );
560 fnd_log.string
561 (
562 l_log_statement,
563 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
564 'Before Insert Schedule Materials for Schedule Material Id : '|| l_schedule_material_id
565 );
566
567 END IF;
568
569 -- Insert the record into schedule materials
570 IF (l_dummy IS NULL AND l_inventory_org_item_id IS NOT NULL )THEN
571 INSERT INTO AHL_SCHEDULE_MATERIALS
572 (SCHEDULED_MATERIAL_ID,
573 OBJECT_VERSION_NUMBER,
574 LAST_UPDATE_DATE,
575 LAST_UPDATED_BY,
576 CREATION_DATE,
577 CREATED_BY,
578 LAST_UPDATE_LOGIN,
579 INVENTORY_ITEM_ID,
580 SCHEDULE_DESIGNATOR,
581 VISIT_ID,
582 VISIT_START_DATE,
583 VISIT_TASK_ID,
584 ORGANIZATION_ID,
585 SCHEDULED_DATE,
586 REQUESTED_QUANTITY,
587 REQUEST_ID,
588 REQUESTED_DATE,
589 SCHEDULED_QUANTITY,
590 PROCESS_STATUS,
591 ERROR_MESSAGE,
592 TRANSACTION_ID,
593 UOM,
594 RT_OPER_MATERIAL_ID,
595 OPERATION_CODE,
596 ITEM_GROUP_ID,
597 OPERATION_SEQUENCE,
598 POSITION_PATH_ID,
599 RELATIONSHIP_ID,
600 MR_ROUTE_ID,
601 MATERIAL_REQUEST_TYPE,
602 STATUS,
603 ATTRIBUTE_CATEGORY,
604 ATTRIBUTE1,
605 ATTRIBUTE2,
606 ATTRIBUTE3,
607 ATTRIBUTE4,
608 ATTRIBUTE5,
609 ATTRIBUTE6,
610 ATTRIBUTE7,
611 ATTRIBUTE8,
612 ATTRIBUTE9,
613 ATTRIBUTE10,
614 ATTRIBUTE11,
615 ATTRIBUTE12,
616 ATTRIBUTE13,
617 ATTRIBUTE14,
618 ATTRIBUTE15
619 )
620 VALUES
621 (l_schedule_material_id,
622 1,
623 SYSDATE,
624 fnd_global.user_id,
625 SYSDATE,
626 fnd_global.user_id,
627 fnd_global.login_id,
628 l_inventory_item_id,
629 NULL,
630 p_visit_id,
631 NULL,
632 p_visit_task_id,
633 l_organization_id,
634 NULL,
635 l_prim_quantity,
636 NULL,
637 -- AnRaj: truncating Requested Date for Material Requirement based on discussions with PM
638 trunc(p_task_start_date),
639 l_sched_prim_quantity, -- yazhou 04Aug2005
640 NULL,
641 NULL,
642 NULL,
643 l_uom_code,
644 p_rt_oper_material_id,
645 l_operation_code,
646 p_item_group_id,
647 l_step,
648 p_position_path_id,
649 p_relationship_id,
650 p_mr_route_id,
651 l_material_request_type,
652 l_mat_status, --Added by sowsubra for Issue 105
653 NULL,
654 NULL,
655 NULL,
656 NULL,
657 NULL,
658 NULL,
659 NULL,
660 NULL,
661 NULL,
662 NULL,
663 NULL,
664 NULL,
665 NULL,
666 NULL,
667 NULL,
668 NULL);
669 END IF; --Record doesnt exist
670
671 -- Check Error Message stack.
672 l_msg_count := FND_MSG_PUB.count_msg;
673 IF l_msg_count > 0 THEN
674 RAISE FND_API.G_EXC_ERROR;
675 END IF;
676
677 IF (l_log_procedure >= l_log_current_level)THEN
678 fnd_log.string
679 (
680 l_log_procedure,
681 'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Insert Planned Matrls.end',
682 'At the end of PLSQL procedure'
683 );
684 END IF;
685
686 EXCEPTION
687 WHEN FND_API.G_EXC_ERROR THEN
688 x_return_status := FND_API.G_RET_STS_ERROR;
689 ROLLBACK TO Insert_Planned_Matrls;
690 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
691 p_data => x_msg_data,
692 p_encoded => fnd_api.g_false);
693
694
695 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
696 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
697 ROLLBACK TO Insert_Planned_Matrls;
698 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
699 p_data => x_msg_data,
700 p_encoded => fnd_api.g_false);
701
702
703 WHEN OTHERS THEN
704 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
705 ROLLBACK TO Insert_Planned_Matrls;
706 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
707 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
708 p_procedure_name => 'INSERT_PLANNED_MATRLS',
709 p_error_text => SUBSTR(SQLERRM,1,500));
710 END IF;
711 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
712 p_data => x_msg_data,
713 p_encoded => fnd_api.g_false);
714
715
716 END Insert_Planned_Matrls;
717
718 -- Start of Comments --
719 -- Procedure name : Update_Planned_Materials
720 -- Type : Private
721 -- Function : This procedure Updates Planned materials information associated to scheduled
722 -- visit, which are defined at Route Operation and Disposition level
723 -- Pre-reqs :
724 -- Parameters :
725 --
726 -- Standard IN Parameters :
727 -- p_api_version IN NUMBER Required
728 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
729 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
730 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
731 -- Based on this flag, the API will set the default attributes.
732 -- p_module_type In VARCHAR2 Default NULL
733 -- This will be null.
734 -- Standard out Parameters :
735 -- x_return_status OUT VARCHAR2 Required
736 -- x_msg_count OUT NUMBER Required
737 -- x_msg_data OUT VARCHAR2 Required
738 --
739 -- Update_Planned_Materials Parameters :
740 -- p_planned_materials_tbl IN Planned_Materials_Tbl,Required
741 --
742 --
743 PROCEDURE Update_Planned_Materials (
744 p_api_version IN NUMBER,
745 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
746 p_commit IN VARCHAR2 := FND_API.g_false,
747 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
748 p_planned_materials_tbl IN ahl_ltp_reqst_matrl_pub.Planned_Materials_Tbl,
749 x_return_status OUT NOCOPY VARCHAR2,
750 x_msg_count OUT NOCOPY NUMBER,
751 x_msg_data OUT NOCOPY VARCHAR2)
752
753 IS
754
755 CURSOR Get_Planned_Items_cur (c_sched_mat_id IN NUMBER)
756 IS
757 SELECT scheduled_material_id,
758 object_version_number,
759 inventory_item_id,
760 requested_quantity,
761 visit_task_id,
762 organization_id,
763 completed_quantity,
764 requested_date,
765 visit_id
766 FROM ahl_schedule_materials
767 WHERE scheduled_material_id = c_sched_mat_id;
768
769 CURSOR Get_Inv_Item_cur (c_item_desc IN VARCHAR2,
770 c_org_id IN NUMBER)
771 IS
772 SELECT inventory_item_id
773 FROM mtl_system_items_vl
774 WHERE concatenated_segments = c_item_desc
775 AND organization_id = c_org_id;
776
777 -- Serial Number Resrvation Change Starts
778 CURSOR Get_Visit_Dates_cur (c_visit_id IN NUMBER)
779 IS
780 SELECT start_date_time, close_date_time
781 FROM ahl_visits_b
782 WHERE visit_id = c_visit_id;
783
784 -- Serial Number Resrvation Change ends
785
786 --Standard local variables
787 l_api_name CONSTANT VARCHAR2(30) := 'Update_Planned_Materials';
788 l_api_version CONSTANT NUMBER := 1.0;
789 l_msg_data VARCHAR2(2000);
790 l_return_status VARCHAR2(1);
791 l_msg_count NUMBER;
792 --
793 l_planned_materials_tbl ahl_ltp_reqst_matrl_pub.planned_materials_tbl := p_planned_materials_tbl;
794 l_Planned_Items_rec Get_Planned_Items_cur%ROWTYPE;
795
796 l_rsvd_quantity NUMBER;
797
798 -- Serial Number Resrvation Change Starts
799 l_visit_start_date DATE;
800 l_visit_end_date DATE;
801 -- Serial Number Resrvation Change ends
802
803 BEGIN
804
805 IF (l_log_procedure >= l_log_current_level)THEN
806 fnd_log.string
807 (
808 l_log_procedure,
809 'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Update_Planned_Materials',
810 'At the start of PLSQL procedure'
811 );
812 END IF;
813 -- Standard start of API savepoint
814 SAVEPOINT Update_Planned_Materials;
815 -- Initialize message list if p_init_msg_list is set to TRUE
816 IF FND_API.To_Boolean( p_init_msg_list) THEN
817 FND_MSG_PUB.Initialize;
818 END IF;
819 -- Initialize API return status to success
820 x_return_status := FND_API.G_RET_STS_SUCCESS;
821 --
822 IF (l_log_statement >= l_log_current_level)THEN
823 fnd_log.string
824 (
825 l_log_statement,
826 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
827 'Request for Update Material Number of Records : '|| l_planned_materials_tbl.COUNT
828 );
829
830 END IF;
831
832 IF l_planned_materials_tbl.COUNT > 0 THEN
833 FOR i IN l_planned_materials_tbl.FIRST..l_planned_materials_tbl.LAST
834 LOOP
835 --Check for Schedule material Record exists
836 IF (l_planned_materials_tbl(i).schedule_material_id IS NOT NULL AND
837 l_planned_materials_tbl(i).schedule_material_id <> FND_API.G_MISS_NUM ) THEN
838 --
839 OPEN Get_Planned_Items_cur(l_planned_materials_tbl(i).schedule_material_id);
840 FETCH Get_Planned_Items_cur INTO l_Planned_Items_rec;
841 IF Get_Planned_Items_cur%NOTFOUND THEN
842 FND_MESSAGE.set_name( 'AHL','AHL_LTP_SCHE_ID_INVALID' );
843 FND_MSG_PUB.add;
844 IF (l_log_error >= l_log_current_level)THEN
845 fnd_log.string
846 (
847 l_log_error,
848 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
849 'Schedule Material id not found in ahl_schedule_materials table'
850 );
851 END IF;
852 CLOSE Get_Planned_Items_cur;
853 RAISE FND_API.G_EXC_ERROR;
854 END IF;
855 CLOSE Get_Planned_Items_cur;
856 END IF;
857
858 --Check for Record has been modified by someother user
859 IF (l_planned_materials_tbl(i).object_version_number IS NOT NULL AND
860 l_planned_materials_tbl(i).object_version_number <> FND_API.G_MISS_NUM ) THEN
861 --
862 IF (l_planned_materials_tbl(i).object_version_number <> l_Planned_Items_rec.object_version_number )
863 THEN
864 FND_MESSAGE.set_name( 'AHL','AHL_LTP_RECORD_INVALID' );
865 FND_MSG_PUB.add;
866 IF (l_log_error >= l_log_current_level)THEN
867 fnd_log.string
868 (
869 l_log_error,
870 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
871 'Schedule Material Record has been modified by someother user'
872 );
873 END IF;
874 RAISE FND_API.G_EXC_ERROR;
875 END IF;
876 END IF;
877 --Convert item description to item id
878 IF (l_planned_materials_tbl(i).item_description IS NOT NULL AND
879 l_planned_materials_tbl(i).item_description <> FND_API.G_MISS_CHAR ) THEN
880 --
881 OPEN Get_Inv_Item_cur(l_planned_materials_tbl(i).item_description,
882 l_planned_items_rec.organization_id);
883 FETCH Get_Inv_Item_cur INTO l_planned_materials_tbl(i).inventory_item_id;
884 IF Get_Inv_Item_cur%NOTFOUND THEN
885 FND_MESSAGE.set_name( 'AHL','AHL_LTP_ITEM_INVALID' );
886 FND_MSG_PUB.add;
887 IF (l_log_error >= l_log_current_level)THEN
888 fnd_log.string
889 (
890 l_log_error,
891 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
892 'Inventory Item doesnt exist in Mtl System Items Vl'
893 );
894 END IF;
895 CLOSE Get_Inv_Item_Cur;
896 RAISE FND_API.G_EXC_ERROR;
897 END IF;
898 CLOSE Get_Inv_Item_cur;
899 END IF;
900
901 -- AnRaj: Moved this code down, after the id in l_planned_materials_tbl has been populated
902 -- Serial Number Reservation Enhancement Changes Start.
903 -- AnRaj: Changed the WHERE and FROM clause adding join with ahl_schedule_materials, for Performance improvement
904 IF l_planned_materials_tbl(i).inventory_item_id <> l_Planned_Items_rec.inventory_item_id
905 THEN
906 SELECT SUM(MR.PRIMARY_RESERVATION_QUANTITY)
907 INTO l_rsvd_quantity
908 FROM mtl_reservations MR,
909 ahl_schedule_materials SM
910 WHERE MR.DEMAND_SOURCE_LINE_DETAIL = l_planned_materials_tbl(i).schedule_material_id
911 AND MR.external_source_code = 'AHL'
912 AND MR.demand_source_line_detail = SM.scheduled_material_id
913 AND MR.organization_id = SM.organization_id
914 AND MR.requirement_date = SM.requested_date
915 AND MR.inventory_item_id = SM.inventory_item_id;
916
917 -- This is based on PRIMARY_RESERVATION_QUANTITY is not null in mtl_reservations
918 IF l_rsvd_quantity IS NOT NULL THEN
919 FND_MESSAGE.set_name( 'AHL','AHL_LTP_ITEM_RSV_EXISTS' );
920 -- Cannot change the item required because at least one reservation already exists for this item.
921 FND_MSG_PUB.add;
922 RAISE FND_API.G_EXC_ERROR;
923 END IF;
924 END IF;
925 -- Serial Number Reservation Enhancement Changes Ends.
926
927 -- Validation for requested quantity
928 IF (l_planned_materials_tbl(i).quantity IS NOT NULL AND
929 l_planned_materials_tbl(i).quantity <> FND_API.G_MISS_NUM) THEN
930
931 -- yazhou 03-JUL-2006 starts
932 -- bug fix#5303378
933 -- Will allow quantity to be changed to zero
934
935 IF l_planned_materials_tbl(i).quantity < 0 THEN
936
937 -- yazhou 03-JUL-2006 ends
938 --
939 Fnd_message.SET_NAME('AHL','AHL_LTP_QUANTITY_INVALID');
940 Fnd_Msg_Pub.ADD;
941 RAISE FND_API.G_EXC_ERROR;
942 END IF;
943
944 -- Serial Number Reservation Enhancement Changes Starts.
945 -- AnRaj: Changed the WHERE and FROM clause adding join with ahl_schedule_materials, for Performance improvement
946 IF NVL(l_planned_materials_tbl(i).quantity,-9) <> NVL(l_Planned_Items_rec.requested_quantity,-99)
947 THEN
948 SELECT SUM(MR.PRIMARY_RESERVATION_QUANTITY)
949 INTO l_rsvd_quantity
950 FROM mtl_reservations MR,
951 ahl_schedule_materials SM
952 WHERE MR.DEMAND_SOURCE_LINE_DETAIL = l_planned_materials_tbl(i).schedule_material_id
953 AND MR.external_source_code = 'AHL'
954 AND MR.demand_source_line_detail = SM.scheduled_material_id
955 AND MR.organization_id = SM.organization_id
956 AND MR.requirement_date = SM.requested_date
957 AND MR.inventory_item_id = SM.inventory_item_id;
958
959 IF NVL((NVL(l_rsvd_quantity,0) + nvl(l_Planned_Items_rec.completed_quantity,0)),-9) > NVL(l_planned_materials_tbl(i).quantity,-9)
960 THEN
961 Fnd_message.SET_NAME('AHL','AHL_LTP_QTY_EXCEEDS');
962 --Completed quantity plus reserved quantity exceeded scheduled quantity
963 Fnd_Msg_Pub.ADD;
964 RAISE FND_API.G_EXC_ERROR;
965 END IF;
966 END IF;
967 END IF;
968 -- Serial Number Reservation Enhancement Changes Ends.
969 -- Validation for requested date
970 IF (l_planned_materials_tbl(i).requested_date IS NOT NULL AND
971 l_planned_materials_tbl(i).requested_date <> FND_API.G_MISS_DATE) THEN
972 IF l_planned_materials_tbl(i).requested_date < trunc(sysdate) THEN
973 --
974 Fnd_message.SET_NAME('AHL','AHL_LTP_DATE_INVALID');
975 Fnd_Msg_Pub.ADD;
976 RAISE FND_API.G_EXC_ERROR;
977
978 END IF;
979
980 -- Serial Number Reservation Enhancement Changes. Starts
981 IF l_planned_materials_tbl(i).requested_date IS NOT NULL
982 AND l_Planned_Items_rec.requested_date <> l_planned_materials_tbl(i).requested_date
983 THEN
984
985 -- New Required Date has to fall between Visit start date and Visit End Date
986 OPEN Get_Visit_Dates_cur(l_planned_items_rec.visit_id);
987 FETCH Get_Visit_Dates_cur into l_visit_start_date, l_visit_end_date;
988 CLOSE Get_Visit_Dates_cur;
989
990 IF (TRUNC(l_planned_materials_tbl(i).requested_date) < TRUNC(l_visit_start_date)) OR
991 (l_visit_end_date is not NULL AND
992 (TRUNC(l_planned_materials_tbl(i).requested_date) > TRUNC(l_visit_end_date))) THEN
993
994 Fnd_message.SET_NAME('AHL','AHL_LTP_REQ_DATE_RANGE');
995 Fnd_Msg_Pub.ADD;
996 RAISE FND_API.G_EXC_ERROR;
997
998 END IF;
999
1000 AHL_RSV_RESERVATIONS_PVT.UPDATE_RESERVATION(
1001 P_API_VERSION => 1.0,
1002 /*P_INIT_MSG_LIST
1003 P_COMMIT
1004 P_VALIDATION_LEVEL */
1005 P_MODULE_TYPE => NULL,
1006 X_RETURN_STATUS => l_return_Status,
1007 X_MSG_COUNT => l_msg_count,
1008 X_MSG_DATA => X_MSG_DATA,
1009 P_SCHEDULED_MATERIAL_ID => l_planned_materials_tbl(i).schedule_material_id,
1010 P_REQUESTED_DATE => l_planned_materials_tbl(i).requested_date);
1011 END IF;
1012
1013 IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
1014 l_msg_count := FND_MSG_PUB.count_msg;
1015 RAISE FND_API.G_EXC_ERROR;
1016 END IF;
1017
1018 -- Serial Number Reservation Enhancement Changes. Ends
1019
1020 END IF;
1021 --
1022 END LOOP;
1023 END IF;
1024 -- Check Error Message stack.
1025 IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
1026 l_msg_count := FND_MSG_PUB.count_msg;
1027 IF l_msg_count > 0 THEN
1028 RAISE FND_API.G_EXC_ERROR;
1029 END IF;
1030 END IF;
1031 --
1032 IF l_planned_materials_tbl.COUNT > 0 THEN
1033 FOR i IN l_planned_materials_tbl.FIRST..l_planned_materials_tbl.LAST
1034 LOOP
1035 --
1036 IF l_planned_materials_tbl(i).schedule_material_id IS NOT NULL THEN
1037 --
1038 UPDATE ahl_schedule_materials
1039 SET inventory_item_id = l_planned_materials_tbl(i).inventory_item_id,
1040 requested_quantity = l_planned_materials_tbl(i).quantity,
1041 -- AnRaj: truncating Requested Date for Material Requirement based on discussions with PM
1042 requested_date = trunc(l_planned_materials_tbl(i).requested_date),
1043 object_version_number = l_planned_materials_tbl(i).object_version_number + 1
1044 WHERE scheduled_material_id = l_planned_materials_tbl(i).schedule_material_id;
1045 END IF;
1046 --
1047 END LOOP;
1048 END IF;
1049 -- Check Error Message stack.
1050 x_msg_count := FND_MSG_PUB.count_msg;
1051 IF x_msg_count > 0 THEN
1052 RAISE FND_API.G_EXC_ERROR;
1053 END IF;
1054
1055 -- Standard check of p_commit
1056 IF FND_API.TO_BOOLEAN(p_commit) THEN
1057 COMMIT WORK;
1058 END IF;
1059
1060 IF (l_log_procedure >= l_log_current_level)THEN
1061 fnd_log.string
1062 (
1063 l_log_procedure,
1064 'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Update Planned Materials.end',
1065 'At the end of PLSQL procedure'
1066 );
1067 END IF;
1068 EXCEPTION
1069 WHEN FND_API.G_EXC_ERROR THEN
1070 x_return_status := FND_API.G_RET_STS_ERROR;
1071 ROLLBACK TO Update_Planned_Materials;
1072 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1073 p_data => x_msg_data,
1074 p_encoded => fnd_api.g_false);
1075
1076
1077 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1078 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1079 ROLLBACK TO Update_Planned_Materials;
1080 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1081 p_data => x_msg_data,
1082 p_encoded => fnd_api.g_false);
1083
1084
1085 WHEN OTHERS THEN
1086 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1087 ROLLBACK TO Update_Planned_Materials;
1088 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1089 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
1090 p_procedure_name => 'Update_Planned_Materials',
1091 p_error_text => SUBSTR(SQLERRM,1,500));
1092 END IF;
1093 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1094 p_data => x_msg_data,
1095 p_encoded => fnd_api.g_false);
1096
1097 END Update_Planned_Materials;
1098
1099 --
1100 -- Start of Comments --
1101 -- Procedure name : Create_Task_Materials
1102 -- Type : Private
1103 -- Function : This procedure Created Planned materials information associated to scheduled
1104 -- visit, which are defined at Route Operation and Disposition level
1105 -- Pre-reqs :
1106 -- Parameters :
1107 --
1108 -- Standard IN Parameters :
1109 -- p_api_version IN NUMBER Required
1110 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
1111 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
1112 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
1113 -- Based on this flag, the API will set the default attributes.
1114 -- p_module_type In VARCHAR2 Default NULL
1115 -- This will be null.
1116 -- Standard out Parameters :
1117 -- x_return_status OUT VARCHAR2 Required
1118 -- x_msg_count OUT NUMBER Required
1119 -- x_msg_data OUT VARCHAR2 Required
1120 --
1121 -- Create_Planned_Materials Parameters :
1122 -- p_visit_id IN NUMBER,Required
1123 --
1124 --
1125 PROCEDURE Create_Task_Materials (
1126 p_api_version IN NUMBER,
1127 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1128 p_commit IN VARCHAR2 := FND_API.g_false,
1129 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1130 p_visit_id IN NUMBER,
1131 p_visit_task_id IN NUMBER := NULL,
1132 p_start_time IN DATE := NULL,
1133 p_org_id IN NUMBER := NULL,
1134 x_return_status OUT NOCOPY VARCHAR2,
1135 x_msg_count OUT NOCOPY NUMBER,
1136 x_msg_data OUT NOCOPY VARCHAR2)
1137
1138 IS
1139 --Get visit details
1140 /*B6271339 - sowsubra - Modified the where clause to filter the records based on visit task id alone*/
1141 CURSOR Get_Visit_Tasks_cur(c_visit_task_id IN NUMBER) IS
1142 SELECT vs.visit_id,
1143 vs.organization_id,
1144 vt.visit_task_id,
1145 vt.mr_route_id,
1146 vt.instance_id,
1147 vt.start_date_time
1148 FROM ahl_visits_b vs,
1149 ahl_visit_tasks_b vt
1150 WHERE vs.visit_id = vt.visit_id
1151 AND vt.visit_task_id = C_VISIT_TASK_ID;
1152
1153 --Get Route details
1154 /*B6271339 - sowsubra - Modified the cursor to fetch only the route id*/
1155 CURSOR Get_Routes_cur(c_mr_route_id IN NUMBER)
1156 IS
1157 SELECT mr.route_id
1158 FROM ahl_mr_routes_app_v mr
1159 WHERE mr.mr_route_id = C_MR_ROUTE_ID;
1160
1161 CURSOR Visit_Valid_Cur(c_visit_id IN NUMBER)
1162 IS
1163 SELECT 1
1164 FROM ahl_visits_vl
1165 WHERE visit_id = C_VISIT_ID
1166 AND (organization_id IS NULL
1167 OR start_date_time IS NULL);
1168
1169 --Standard local variables
1170 l_api_name CONSTANT VARCHAR2(30) := 'Create_Task_Materials';
1171 l_api_version CONSTANT NUMBER := 1.0;
1172 l_msg_data VARCHAR2(2000);
1173 l_return_status VARCHAR2(1);
1174 l_msg_count NUMBER;
1175 l_dummy NUMBER;
1176 --Variables for derieve start times
1177 l_visit_start_time DATE := nvl(p_start_time,null);
1178 --
1179 l_route_id NUMBER;
1180 l_instance_id NUMBER;
1181 l_requirement_date DATE;
1182 l_visit_tasks_rec Get_visit_tasks_cur%ROWTYPE;
1183 l_route_mtl_req_tbl AHL_LTP_MTL_REQ_PVT.Route_Mtl_Req_Tbl_Type;
1184
1185 BEGIN
1186
1187 IF (l_log_procedure >= l_log_current_level)THEN
1188 fnd_log.string
1189 (
1190 l_log_procedure,
1191 'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Create_Task_Materials',
1192 'At the start of PLSQL procedure'
1193 );
1194 END IF;
1195 -- Standard start of API savepoint
1196 SAVEPOINT Create_Task_Materials;
1197 -- Initialize message list if p_init_msg_list is set to TRUE
1198 IF FND_API.To_Boolean( p_init_msg_list) THEN
1199 FND_MSG_PUB.Initialize;
1200 END IF;
1201 -- Initialize API return status to success
1202 x_return_status := FND_API.G_RET_STS_SUCCESS;
1203 --
1204 IF (l_log_statement >= l_log_current_level)THEN
1205 fnd_log.string
1206 (
1207 l_log_statement,
1208 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1209 'Request for Create Task Materials for Visit Id : '|| p_visit_id
1210 );
1211
1212 END IF;
1213
1214 --Get visit details
1215 /*B6271339 - sowsubra - Modified the where clause to filter the records based on visit task id alone*/
1216 OPEN Get_visit_tasks_cur(p_visit_task_id);
1217 FETCH Get_visit_tasks_cur INTO l_visit_tasks_rec;
1218 CLOSE Get_visit_tasks_cur;
1219
1220 IF (l_log_statement >= l_log_current_level)THEN
1221 fnd_log.string
1222 (
1223 l_log_statement,
1224 'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Create_Task_Materials',
1225 ' Visit Id: ' || p_visit_id
1226 );
1227 fnd_log.string
1228 (
1229 l_log_statement,
1230 'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Create_Task_Materials',
1231 ' Organization Id: ' || l_visit_tasks_rec.organization_id
1232 );
1233
1234 END IF;
1235
1236 --Check for visit Org, Dept, Start date should be not null
1237 OPEN Visit_Valid_Cur(p_visit_id);
1238 FETCH Visit_Valid_Cur INTO l_dummy;
1239 CLOSE Visit_Valid_Cur;
1240
1241 -- Derieve task start times
1242 IF (p_visit_id IS NOT NULL AND p_visit_id <> FND_API.G_MISS_NUM
1243 AND l_dummy IS NULL) THEN
1244 -- Derive task start time
1245
1246 IF (l_log_statement >= l_log_current_level)THEN
1247 fnd_log.string
1248 (
1249 l_log_statement,
1250 'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Create_Task_Materials',
1251 ' Visit Task Id: ' || l_visit_tasks_rec.visit_task_id
1252 );
1253 fnd_log.string
1254 (
1255 l_log_statement,
1256 'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Create_Task_Materials',
1257 ' Task Start Time: ' || l_visit_tasks_rec.start_date_time
1258 );
1259 fnd_log.string
1260 (
1261 l_log_statement,
1262 'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Create_Task_Materials',
1263 ' Mr Route Id: ' || l_visit_tasks_rec.mr_route_id
1264 );
1265
1266 END IF;
1267 -- Process all the items associated
1268 IF l_visit_tasks_rec.mr_route_id IS NOT NULL THEN
1269 -- Retrieve route and instance
1270
1271 /*B6271339 - sowsubra - Modified the cursor to fetch only the route id*/
1272 OPEN Get_Routes_cur(l_visit_tasks_rec.mr_route_id);
1273 FETCH Get_Routes_cur INTO l_route_id;
1274 CLOSE Get_Routes_cur;
1275 --
1276 IF (l_visit_tasks_rec.start_date_time IS NOT NULL AND TRUNC(l_visit_tasks_rec.start_date_time) < TRUNC(sysdate)
1277 ) THEN
1278 l_requirement_date := SYSDATE;
1279 --
1280 ELSE
1281 l_requirement_date := l_visit_tasks_rec.start_date_time;
1282 END IF;
1283
1284 IF (l_log_statement >= l_log_current_level)THEN
1285 fnd_log.string
1286 (
1287 l_log_statement,
1288 'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Create_Task_Materials',
1289 ' Before Calling Get Route Mtl Req, Route Id: ' || l_route_id
1290 );
1291 fnd_log.string
1292 (
1293 l_log_statement,
1294 'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Create_Task_Materials',
1295 ' Before Calling Get Route Mtl Req, Instance Id: ' || l_instance_id
1296 );
1297
1298 END IF;
1299
1300 IF (l_log_procedure >= l_log_current_level) THEN
1301 fnd_log.string
1302 (
1303 l_log_procedure,
1304 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1305 'Before calling ahl ltp mtl req pvt.Get Route Mtl Req'
1306 );
1307
1308 END IF;
1309
1310 AHL_LTP_MTL_REQ_PVT.Get_Route_Mtl_Req
1311 (p_api_version => l_api_version,
1312 p_init_msg_list => p_init_msg_list,
1313 p_validation_level => p_validation_level,
1314 x_return_status => l_return_status,
1315 x_msg_count => l_msg_count,
1316 x_msg_data => l_msg_data,
1317 p_route_id => l_route_id,
1318 p_mr_route_id => l_visit_tasks_rec.mr_route_id,
1319 p_item_instance_id => l_visit_tasks_rec.instance_id, /*B6271339 - sowsubra*/
1320 p_requirement_date => l_requirement_date,
1321 p_request_type => 'PLANNED',
1322 x_route_mtl_req_tbl => l_route_mtl_req_tbl);
1323 END IF; --MR Route not null
1324
1325 IF (l_log_procedure >= l_log_current_level) THEN
1326 fnd_log.string
1327 (
1328 l_log_procedure,
1329 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||': End API',
1330 'After calling ahl ltp mtl req pvt.Get Route Mtl Req, Return Status : '|| l_return_status
1331 );
1332 END IF;
1333 -- Check Error Message stack.
1334 IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
1335 l_msg_count := FND_MSG_PUB.count_msg;
1336 IF l_msg_count > 0 THEN
1337 RAISE FND_API.G_EXC_ERROR;
1338 END IF;
1339 END IF;
1340
1341 IF (l_log_statement >= l_log_current_level)THEN
1342 fnd_log.string
1343 (
1344 l_log_statement,
1345 'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Create_Task_Materials',
1346 ' After Calling Get Route Mtl Req, l_route_mtl_req_tbl COUNT: ' || l_route_mtl_req_tbl.COUNT
1347 );
1348
1349 END IF;
1350 -- Create planned item in schedule material entity
1351 IF l_route_mtl_req_tbl.COUNT > 0 THEN
1352 --Loop through
1353 FOR i IN l_route_mtl_req_tbl.FIRST..l_route_mtl_req_tbl.LAST
1354 LOOP
1355 --Call insert procedure
1356 Insert_Planned_Matrls(
1357 p_visit_id => p_visit_id,
1358 p_visit_task_id => l_visit_tasks_rec.visit_task_id,
1359 p_task_start_date => l_visit_tasks_rec.start_date_time,
1360 p_inventory_item_id => l_route_mtl_req_tbl(i).inventory_item_id,
1361 p_requested_quantity => l_route_mtl_req_tbl(i).quantity,
1362 p_uom_code => l_route_mtl_req_tbl(i).uom_code,
1363 p_item_group_id => l_route_mtl_req_tbl(i).item_group_id,
1364 p_rt_oper_material_id => l_route_mtl_req_tbl(i).rt_oper_material_id,
1365 p_position_path_id => l_route_mtl_req_tbl(i).position_path_id,
1366 p_relationship_id => l_route_mtl_req_tbl(i).relationship_id,
1367 p_mr_route_id => l_visit_tasks_rec.mr_route_id,
1368 p_item_comp_detail_id => l_route_mtl_req_tbl(i).item_comp_detail_id,
1369 p_inv_master_org_id => l_visit_tasks_rec.organization_id,
1370 x_return_status => l_return_status,
1371 x_msg_count => l_msg_count,
1372 x_msg_data => l_msg_data );
1373 --
1374 END LOOP;
1375 END IF; --l_route_mtl_req_tbl
1376 END IF;
1377
1378 IF (l_log_procedure >= l_log_current_level) THEN
1379 fnd_log.string
1380 (
1381 l_log_procedure,
1382 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||': End API',
1383 'After calling Insert Planned Materials, Return Status : '|| l_return_status
1384 );
1385 END IF;
1386
1387 -- Check Error Message stack.
1388 IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
1389 l_msg_count := FND_MSG_PUB.count_msg;
1390 IF l_msg_count > 0 THEN
1391 RAISE FND_API.G_EXC_ERROR;
1392 END IF;
1393 END IF;
1394
1395
1396 -- Standard check of p_commit
1397 IF FND_API.TO_BOOLEAN(p_commit) THEN
1398 COMMIT WORK;
1399 END IF;
1400
1401 IF (l_log_procedure >= l_log_current_level)THEN
1402 fnd_log.string
1403 (
1404 l_log_procedure,
1405 'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Create Task Materials.end',
1406 'At the end of PLSQL procedure'
1407 );
1408 END IF;
1409 EXCEPTION
1410 WHEN FND_API.G_EXC_ERROR THEN
1411 x_return_status := FND_API.G_RET_STS_ERROR;
1412 ROLLBACK TO Create_Task_Materials;
1413 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1414 p_data => x_msg_data,
1415 p_encoded => fnd_api.g_false);
1416
1417
1418 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1419 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1420 ROLLBACK TO Create_Task_Materials;
1421 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1422 p_data => x_msg_data,
1423 p_encoded => fnd_api.g_false);
1424
1425
1426 WHEN OTHERS THEN
1427 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1428 ROLLBACK TO Create_Task_Materials;
1429 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1430 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
1431 p_procedure_name => 'Create_Task_Materials',
1432 p_error_text => SUBSTR(SQLERRM,1,500));
1433 END IF;
1434 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1435 p_data => x_msg_data,
1436 p_encoded => fnd_api.g_false);
1437
1438
1439 END Create_Task_Materials;
1440 --
1441 -- Start of Comments --
1442 -- Procedure name : Modify_Visit_Task_Matrls
1443 -- Type : Private
1444 -- Function : This procedure Created Planned materials information associated to scheduled
1445 -- visit, which are defined at Route Operation and Disposition level
1446 -- Pre-reqs :
1447 -- Parameters :
1448 --
1449 -- Standard IN Parameters :
1450 -- p_api_version IN NUMBER Required
1451 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
1452 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
1453 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
1454 -- Based on this flag, the API will set the default attributes.
1455 -- p_module_type In VARCHAR2 Default NULL
1456 -- This will be null.
1457 -- Standard out Parameters :
1458 -- x_return_status OUT VARCHAR2 Required
1459 -- x_msg_count OUT NUMBER Required
1460 -- x_msg_data OUT VARCHAR2 Required
1461 --
1462 -- Modify_Visit_Task_Matrls Parameters :
1463 -- p_visit_id IN NUMBER,Required
1464 --
1465 --
1466 PROCEDURE Modify_Visit_Task_Matrls (
1467 p_api_version IN NUMBER,
1468 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1469 p_commit IN VARCHAR2 := FND_API.g_false,
1470 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1471 p_visit_id IN NUMBER,
1472 p_visit_task_id IN NUMBER := NULL,
1473 p_start_time IN DATE := NULL,
1474 p_org_id IN NUMBER := NULL,
1475 x_return_status OUT NOCOPY VARCHAR2,
1476 x_msg_count OUT NOCOPY NUMBER,
1477 x_msg_data OUT NOCOPY VARCHAR2)
1478 IS
1479
1480 CURSOR Get_Visit_Tasks_Cur(c_visit_id IN NUMBER)
1481 IS
1482 SELECT vs.visit_id,
1483 vs.organization_id,
1484 vt.visit_task_id,
1485 vt.mr_route_id,
1486 vt.instance_id,
1487 nvl(vt.start_date_time,vs.start_date_time) start_date_time,
1488 mr.route_id
1489
1490 FROM ahl_visits_vl vs,
1491 ahl_visit_tasks_vl vt,
1492 ahl_mr_routes_app_v mr
1493 WHERE vs.visit_id = vt.visit_id
1494 AND vt.mr_route_id = mr.mr_route_id
1495 AND vs.visit_id = C_VISIT_ID
1496 -- Modified by amagrawa based on Enhancement
1497 AND vt.status_code = 'PLANNING';
1498 --
1499 CURSOR Get_Routes_Cur(c_mr_route_id IN NUMBER)
1500 IS
1501 SELECT mr.route_id,
1502 vt.instance_id,
1503 vt.start_date_time
1504 FROM ahl_visit_tasks_vl vt,
1505 ahl_mr_routes_app_v mr
1506 WHERE vt.mr_route_id = mr.mr_route_id
1507 AND mr.mr_route_id = C_MR_ROUTE_ID;
1508
1509 -- yazhou 17-May-2006 starts
1510 -- bug fix#5232544
1511
1512 --Retrieve visit materials
1513 -- AnRaj: Added the condition for picking up materials for tasks in status DELETED also
1514 -- for soft deleting materials of deleted tasks from schedule materials table.
1515 CURSOR Deleted_Items_Cur (c_visit_id IN NUMBER)
1516 IS
1517 SELECT asm.visit_id,
1518 asm.scheduled_material_id scheduled_material_id,
1519 asm.object_version_number,
1520 asm.scheduled_quantity,
1521 asm.scheduled_date
1522 FROM ahl_visit_tasks_b tsk,ahl_schedule_materials asm
1523 WHERE asm.visit_id = C_VISIT_ID
1524 AND asm.visit_task_id = tsk.visit_task_id
1525 AND tsk.status_code ='DELETED'
1526 AND asm.status <> 'DELETED';
1527
1528
1529 CURSOR Planned_Items_cur (c_visit_task_id IN NUMBER, c_rt_oper_material_id IN NUMBER)
1530 IS
1531 SELECT requested_quantity,
1532 scheduled_material_id,
1533 object_version_number
1534 FROM ahl_schedule_materials
1535 WHERE visit_task_id = c_visit_task_id
1536 AND rt_oper_material_id = c_rt_oper_material_id
1537 AND NVL(STATUS, 'X') = 'ACTIVE';
1538
1539 l_Deleted_Items_Rec Deleted_Items_Cur%rowtype;
1540 l_requested_qty NUMBER;
1541
1542 -- yazhou 17-May-2006 ends
1543
1544 -- Added to fix the unlogged bug where org id was being incorrectly updated to inventory item's org id
1545 -- when a task was being deleted.
1546 l_visit_org_id NUMBER;
1547 CURSOR Get_Visit_Org_Id_Cur(c_visit_id IN NUMBER)
1548 IS
1549 SELECT organization_id
1550 FROM ahl_visits_b
1551 WHERE visit_id = C_VISIT_ID
1552 AND ( organization_id IS NOT NULL
1553 OR start_date_time IS NOT NULL
1554 OR department_id IS NOT NULL
1555 );
1556
1557 --Standard local variables
1558 l_api_name CONSTANT VARCHAR2(30) := 'Modify_Visit_Task_Matrls';
1559 l_api_version CONSTANT NUMBER := 1.0;
1560 l_msg_data VARCHAR2(2000);
1561 l_return_status VARCHAR2(1);
1562 l_msg_count NUMBER;
1563 l_dummy NUMBER;
1564 --Variables for derieve start times
1565 l_visit_start_time DATE := nvl(p_start_time,null);
1566 j NUMBER := 0;
1567 --
1568 l_route_id NUMBER;
1569 l_instance_id NUMBER;
1570 l_visit_tasks_rec Get_visit_tasks_cur%ROWTYPE;
1571 l_route_mtl_req_tbl AHL_LTP_MTL_REQ_PVT.Route_Mtl_Req_Tbl_Type;
1572 l_Planned_Items_Rec Planned_Items_cur%ROWTYPE;
1573 l_requirement_date DATE;
1574
1575 l_Visit_Task_Route_Tbl Visit_Task_Route_Tbl_Type;
1576 i_x NUMBER;
1577 BEGIN
1578
1579 IF (l_log_procedure >= l_log_current_level)THEN
1580 fnd_log.string
1581 (
1582 l_log_procedure,
1583 'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Modify_Visit_Task_Matrls',
1584 'At the start of PLSQL procedure'
1585 );
1586 END IF;
1587 -- Standard start of API savepoint
1588 SAVEPOINT Modify_Visit_Task_Matrls;
1589 -- Initialize message list if p_init_msg_list is set to TRUE
1590 IF FND_API.To_Boolean( p_init_msg_list) THEN
1591 FND_MSG_PUB.Initialize;
1592 END IF;
1593 -- Initialize API return status to success
1594 x_return_status := FND_API.G_RET_STS_SUCCESS;
1595 --
1596 IF (l_log_statement >= l_log_current_level)THEN
1597 fnd_log.string
1598 (
1599 l_log_statement,
1600 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1601 'Request for Modify Visit Task Materials for Visit Id : '|| p_visit_id
1602 );
1603
1604 END IF;
1605
1606 -- yazhou 17-May-2006 starts
1607 -- bug fix#5232544
1608 -- Delete all the requirements belong to tasks in DELETED status
1609
1610 OPEN Deleted_Items_cur(p_visit_id);
1611 LOOP
1612 FETCH Deleted_Items_cur INTO l_Deleted_Items_Rec;
1613 EXIT WHEN Deleted_Items_cur%NOTFOUND;
1614 --
1615 IF l_Deleted_Items_Rec.scheduled_material_id IS NOT NULL THEN
1616 IF (l_log_procedure >= l_log_current_level)THEN
1617 fnd_log.string
1618 (
1619 l_log_procedure,
1620 'AHL.PLSQL.AHL_LTP_REQST_MATRL_PVT.MODIFY_VISIT_TASK_MATRLS',
1621 'Updating the status to DELETED for Material Requirement' || l_Deleted_Items_Rec.scheduled_material_id
1622 );
1623 END IF;
1624
1625 UPDATE ahl_schedule_materials
1626 SET requested_quantity =0,
1627 status = 'DELETED',
1628 object_version_number = l_Deleted_Items_Rec.object_version_number + 1
1629 WHERE scheduled_material_id = l_Deleted_Items_Rec.scheduled_material_id ;
1630
1631 END IF;
1632 END LOOP;
1633 CLOSE Deleted_Items_cur;
1634
1635 -- yazhou 17-May-2006 ends
1636
1637 -- AnRaj : Added to fix the unlogged bug where org id was being incorrectly updated to inventory item's org id
1638 -- when a task was being deleted.
1639 -- START of Fix
1640 OPEN Get_Visit_Org_Id_Cur(p_visit_id);
1641 FETCH Get_Visit_Org_Id_Cur INTO l_visit_org_id;
1642 CLOSE Get_Visit_Org_Id_Cur;
1643 -- If the visit does not have a org id, no need to insert the materials again
1644 IF l_visit_org_id IS NULL THEN
1645 RETURN;
1646 ELSE
1647 IF p_org_id IS NOT NULL THEN
1648 l_visit_org_id := p_org_id;
1649 END IF;
1650 END IF;
1651 -- END of Fix
1652
1653
1654 IF (p_visit_id IS NOT NULL AND p_visit_id <> FND_API.G_MISS_NUM) THEN
1655 --
1656 OPEN Get_Visit_Tasks_Cur(p_visit_id);
1657 i_x := 0;
1658 LOOP
1659 FETCH Get_Visit_Tasks_Cur INTO l_visit_tasks_rec;
1660 EXIT WHEN Get_Visit_Tasks_Cur%NOTFOUND;
1661 IF l_visit_tasks_rec.route_id IS NOT NULL THEN
1662 --
1663 l_Visit_Task_Route_Tbl(i_x).visit_task_id := l_visit_tasks_rec.visit_task_id;
1664 l_Visit_Task_Route_Tbl(i_x).mr_route_id := l_visit_tasks_rec.mr_route_id;
1665 l_Visit_Task_Route_Tbl(i_x).route_id := l_visit_tasks_rec.route_id;
1666 l_Visit_Task_Route_Tbl(i_x).instance_id := l_visit_tasks_rec.instance_id;
1667 l_Visit_Task_Route_Tbl(i_x).task_start_date := l_visit_tasks_rec.start_date_time;
1668
1669 i_x := i_x + 1;
1670 END IF;
1671 END LOOP;
1672 CLOSE Get_Visit_Tasks_Cur;
1673 END IF;
1674
1675 --
1676 IF (l_log_statement >= l_log_current_level)THEN
1677 fnd_log.string
1678 (
1679 l_log_statement,
1680 'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Create_Planned_Materials',
1681 ' After Calling Derive task times, l_Visit_Task_Route_Tbl.COUNT: ' || l_Visit_Task_Route_Tbl.COUNT
1682 );
1683
1684 END IF;
1685
1686 IF l_Visit_Task_Route_Tbl.COUNT > 0 THEN
1687 FOR i IN l_Visit_Task_Route_Tbl.FIRST..l_Visit_Task_Route_Tbl.LAST
1688 LOOP
1689
1690 IF (l_log_statement >= l_log_current_level)THEN
1691 fnd_log.string
1692 (
1693 l_log_statement,
1694 'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Create_Planned_Materials',
1695 ' Before Calling Get Route Mtl Req, Route Id: ' || l_Visit_Task_Route_Tbl(i).route_id
1696 );
1697 fnd_log.string
1698 (
1699 l_log_statement,
1700 'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Create_Planned_Materials',
1701 ' Before Calling Get Route Mtl Req, Instance Id: ' || l_Visit_Task_Route_Tbl(i).instance_id
1702 );
1703 fnd_log.string
1704 (
1705 l_log_statement,
1706 'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Create_Planned_Materials',
1707 ' Before Calling Get Route Mtl Req, Task Start Time: ' || l_Visit_Task_Route_Tbl(i).task_start_date
1708 );
1709
1710 END IF;
1711
1712 IF (l_Visit_Task_Route_Tbl(i).task_start_date IS NOT NULL AND
1713 TRUNC(l_Visit_Task_Route_Tbl(i).task_start_date) < TRUNC(SYSDATE) )
1714 THEN
1715 l_requirement_date := sysdate;
1716 ELSE
1717 l_requirement_date := l_Visit_Task_Route_Tbl(i).task_start_date;
1718 END IF;
1719
1720 IF (l_log_procedure >= l_log_current_level) THEN
1721 fnd_log.string
1722 (
1723 l_log_procedure,
1724 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1725 'Before calling ahl ltp mtl req pvt.Get Route Mtl Req'
1726 );
1727
1728 END IF;
1729
1730 --Call to get items
1731 AHL_LTP_MTL_REQ_PVT.Get_Route_Mtl_Req
1732 (p_api_version => l_api_version,
1733 p_init_msg_list => p_init_msg_list,
1734 p_validation_level => p_validation_level,
1735 x_return_status => l_return_status,
1736 x_msg_count => l_msg_count,
1737 x_msg_data => l_msg_data,
1738 p_route_id => l_Visit_Task_Route_Tbl(i).route_id,
1739 p_mr_route_id => l_Visit_Task_Route_Tbl(i).mr_route_id,
1740 p_item_instance_id => l_Visit_Task_Route_Tbl(i).instance_id,
1741 p_requirement_date => l_requirement_date,
1742 p_request_type => 'PLANNED',
1743 x_route_mtl_req_tbl => l_route_mtl_req_tbl);
1744 --
1745 IF (l_log_procedure >= l_log_current_level) THEN
1746 fnd_log.string
1747 (
1748 l_log_procedure,
1749 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||': End API',
1750 'After calling ahl ltp mtl req pvt.Get Route Mtl Req, Return Status : '|| l_return_status
1751 );
1752 END IF;
1753 -- Check Error Message stack.
1754 IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
1755 l_msg_count := FND_MSG_PUB.count_msg;
1756 IF l_msg_count > 0 THEN
1757 RAISE FND_API.G_EXC_ERROR;
1758 END IF;
1759 END IF;
1760
1761 IF (l_log_statement >= l_log_current_level)THEN
1762 fnd_log.string
1763 (
1764 l_log_statement,
1765 'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Create_Planned_Materials',
1766 ' After Calling Get Route Mtl Req, l_route_mtl_req_tbl COUNT: ' || l_route_mtl_req_tbl.COUNT
1767 );
1768
1769 END IF;
1770
1771 IF l_route_mtl_req_tbl.COUNT > 0 THEN
1772 --
1773 FOR j IN l_route_mtl_req_tbl.FIRST..l_route_mtl_req_tbl.LAST
1774 LOOP
1775
1776 -- yazhou 17-May-2006 starts
1777 -- bug fix#5232544
1778
1779 -- For a given requirement
1780 -- 1)use the requested quantity in the existing requirement because it could have been
1781 -- changed by the user from LTP update material requirement UI.
1782 -- 2) Delete the existing requirement before creating the new one
1783
1784 l_requested_qty := null;
1785
1786 OPEN Planned_Items_cur(l_Visit_Task_Route_Tbl(i).visit_task_id,l_route_mtl_req_tbl(j).rt_oper_material_id);
1787 FETCH Planned_Items_cur INTO l_Planned_Items_Rec;
1788
1789 IF Planned_Items_cur%found THEN
1790
1791 IF l_Planned_Items_Rec.scheduled_material_id IS NOT NULL THEN
1792 IF (l_log_procedure >= l_log_current_level)THEN
1793 fnd_log.string
1794 (
1795 l_log_procedure,
1796 'AHL.PLSQL.AHL_LTP_REQST_MATRL_PVT.MODIFY_VISIT_TASK_MATRLS',
1797 'Updating the status to DELETED for Material Requirement' || l_Planned_Items_Rec.scheduled_material_id
1798 );
1799 END IF;
1800
1801 -- delete existing requirement for a given rt_oper_material_id and task combination
1802
1803 UPDATE ahl_schedule_materials
1804 SET requested_quantity =0,
1805 status = 'DELETED',
1806 object_version_number = l_Planned_Items_Rec.object_version_number + 1
1807 WHERE scheduled_material_id = l_Planned_Items_Rec.scheduled_material_id ;
1808
1809 -- use the requested quantity defined for the existing requirement
1810 l_requested_qty := l_Planned_Items_Rec.requested_quantity;
1811
1812 END IF; -- scheduled_material_id is not null
1813
1814 ELSE
1815 -- use the default quantity defined at the route
1816 l_requested_qty := l_route_mtl_req_tbl(j).quantity;
1817
1818 END IF; -- planned_item_cur%found
1819
1820 CLOSE Planned_Items_cur;
1821
1822 Insert_Planned_Matrls(
1823 p_visit_id => p_visit_id,
1824 p_visit_task_id => l_Visit_Task_Route_Tbl(i).visit_task_id,
1825 p_task_start_date => l_Visit_Task_Route_Tbl(i).task_start_date,
1826 p_inventory_item_id => l_route_mtl_req_tbl(j).inventory_item_id,
1827 p_requested_quantity => l_requested_qty,
1828 p_uom_code => l_route_mtl_req_tbl(j).uom_code,
1829 p_item_group_id => l_route_mtl_req_tbl(j).item_group_id,
1830 p_rt_oper_material_id => l_route_mtl_req_tbl(j).rt_oper_material_id,
1831 p_position_path_id => l_route_mtl_req_tbl(j).position_path_id,
1832 p_relationship_id => l_route_mtl_req_tbl(j).relationship_id,
1833 p_mr_route_id => l_Visit_Task_Route_Tbl(i).mr_route_id,
1834 p_item_comp_detail_id => l_route_mtl_req_tbl(j).item_comp_detail_id,
1835 -- AnRaj: changed the paramter, for fixing bug where org id was being incorrectly updated
1836 p_inv_master_org_id => l_visit_org_id ,
1837 x_return_status => l_return_status,
1838 x_msg_count => l_msg_count,
1839 x_msg_data => l_msg_data );
1840
1841 -- yazhou 17-May-2006 ends
1842 --
1843 END LOOP;
1844 END IF; --l_route_mtl_req_tbl
1845
1846 IF (l_log_procedure >= l_log_current_level) THEN
1847 fnd_log.string
1848 (
1849 l_log_procedure,
1850 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||': End API',
1851 'After calling Insert Planned Materials, Return Status : '|| l_return_status
1852 );
1853 END IF;
1854
1855 -- Check Error Message stack.
1856 IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
1857 l_msg_count := FND_MSG_PUB.count_msg;
1858 IF l_msg_count > 0 THEN
1859 RAISE FND_API.G_EXC_ERROR;
1860 END IF;
1861 END IF;
1862 --
1863 END LOOP;
1864 END IF;
1865
1866 -- Serial Number Reservation Enhancement Changes.
1867 -- If the date of the visit has changed then all reservation dates also should change accordingly
1868 Modify_Visit_Reservations (
1869 p_visit_id => p_visit_id,
1870 x_return_status => l_return_status);
1871
1872 IF (l_log_procedure >= l_log_current_level) THEN
1873 fnd_log.string
1874 (
1875 l_log_procedure,
1876 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||': End API',
1877 'After calling Modify_Visit_Reservations, Return Status : '|| l_return_status
1878 );
1879 END IF;
1880
1881 -- Check Error Message stack.
1882 IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
1883 l_msg_count := FND_MSG_PUB.count_msg;
1884 RAISE FND_API.G_EXC_ERROR;
1885 END IF;
1886
1887 -- Check Error Message stack.
1888 x_msg_count := FND_MSG_PUB.count_msg;
1889 IF x_msg_count > 0 THEN
1890 RAISE FND_API.G_EXC_ERROR;
1891 END IF;
1892
1893 -- Standard check of p_commit
1894 IF FND_API.TO_BOOLEAN(p_commit) THEN
1895 COMMIT WORK;
1896 END IF;
1897
1898 IF (l_log_procedure >= l_log_current_level)THEN
1899 fnd_log.string
1900 (
1901 l_log_procedure,
1902 'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Modify Visit Task Matrls.end',
1903 'At the end of PLSQL procedure'
1904 );
1905 END IF;
1906 EXCEPTION
1907 WHEN FND_API.G_EXC_ERROR THEN
1908 x_return_status := FND_API.G_RET_STS_ERROR;
1909 ROLLBACK TO Modify_Visit_Task_Matrls;
1910 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1911 p_data => x_msg_data,
1912 p_encoded => fnd_api.g_false);
1913
1914
1915 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1916 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1917 ROLLBACK TO Modify_Visit_Task_Matrls;
1918 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1919 p_data => x_msg_data,
1920 p_encoded => fnd_api.g_false);
1921
1922
1923 WHEN OTHERS THEN
1924 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1925 ROLLBACK TO Modify_Visit_Task_Matrls;
1926 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1927 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
1928 p_procedure_name => 'Modify_Visit_Task_Matrls',
1929 p_error_text => SUBSTR(SQLERRM,1,500));
1930 END IF;
1931 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1932 p_data => x_msg_data,
1933 p_encoded => fnd_api.g_false);
1934
1935
1936 END Modify_Visit_Task_Matrls;
1937 --
1938 -- Start of Comments --
1939 -- Procedure name : Unschedule_Visit_task_Items
1940 -- Type : Private
1941 -- Function : This procedure Checks any items scheduled
1942 -- which are defined at Route Operation and Disposition level
1943 -- Pre-reqs :
1944 -- Parameters :
1945 --
1946 -- Standard IN Parameters :
1947 -- p_api_version IN NUMBER Required
1948 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
1949 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
1950 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
1951 -- Based on this flag, the API will set the default attributes.
1952 -- This will be null.
1953 -- Standard out Parameters :
1954 -- x_return_status OUT VARCHAR2 Required
1955 -- x_msg_count OUT NUMBER Required
1956 -- x_msg_data OUT VARCHAR2 Required
1957 --
1958 -- Unschedule_Visit_Task_Items Parameters :
1959 -- p_visit_id IN NUMBER, Required
1960 -- p_visit_task_id IN NUMBER, Optional
1961 --
1962
1963 PROCEDURE Unschedule_visit_Task_Items
1964 (p_api_version IN NUMBER,
1965 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
1966 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
1967 p_visit_id IN NUMBER,
1968 p_visit_task_id IN NUMBER := NULL,
1969 x_return_status OUT NOCOPY VARCHAR2,
1970 x_msg_count OUT NOCOPY NUMBER,
1971 x_msg_data OUT NOCOPY VARCHAR2 )
1972 IS
1973 --
1974 CURSOR check_items_cur (C_VISIT_ID IN NUMBER)
1975 IS
1976 -- AnRaj :Changed for fixing performance bug#4919562
1977 SELECT ASMT.visit_id,
1978 ASMT.visit_task_id,
1979 ASMT.scheduled_material_id schedule_material_id,
1980 decode(sign( trunc(ASMT.scheduled_date) - trunc(requested_date)), 1, ASMT.scheduled_date, null) SCHEDULED_DATE,
1981 ASMT.SCHEDULED_QUANTITY
1982 FROM AHL_SCHEDULE_MATERIALS ASMT,
1983 AHL_VISIT_TASKS_B VTSK
1984 WHERE ASMT.STATUS <> 'DELETED'
1985 AND EXISTS ( Select 1
1986 from AHL_RT_OPER_MATERIALS RTOM
1987 where RTOM.RT_OPER_MATERIAL_ID = ASMT.RT_OPER_MATERIAL_ID)
1988 AND VTSK.VISIT_ID = ASMT.VISIT_ID
1989 AND VTSK.VISIT_TASK_ID = ASMT.VISIT_TASK_ID
1990 AND NVL(VTSK.STATUS_CODE,'X') <> 'DELETED'
1991 AND ASMT.VISIT_ID = C_VISIT_ID
1992 AND scheduled_date IS NOT NULL;
1993 /*
1994 SELECT visit_id,visit_task_id,schedule_material_id,
1995 scheduled_date,scheduled_quantity
1996 FROM ahl_visit_task_matrl_v
1997 WHERE visit_id = C_VISIT_ID
1998 AND scheduled_date IS NOT NULL;
1999 */
2000 --
2001 l_api_name CONSTANT VARCHAR2(30) := 'UNSCHEDULE_TASK_ITEMS';
2002 l_api_version CONSTANT NUMBER := 1.0;
2003 l_return_status VARCHAR2(1);
2004 l_msg_data VARCHAR2(200);
2005 l_msg_count NUMBER;
2006 l_schedule_items_rec check_items_cur%ROWTYPE;
2007 l_req_material_rec ahl_ltp_reqst_matrl_pub.Schedule_Mr_Rec;
2008 --
2009 BEGIN
2010 --------------------Initialize ----------------------------------
2011 -- Standard Start of API savepoint
2012 SAVEPOINT Unschedule_Task_Items;
2013 -- Check if API is called in debug mode. If yes, enable debug.
2014 IF G_DEBUG='Y' THEN
2015 AHL_DEBUG_PUB.enable_debug;
2016 END IF;
2017 -- Debug info.
2018 IF G_DEBUG='Y' THEN
2019 AHL_DEBUG_PUB.debug( 'enter ahl_ltp_reqst_matrl_pvt Unchedule Task Items ','+MAATP+');
2020 END IF;
2021 -- Standard call to check for call compatibility.
2022 IF FND_API.to_boolean(p_init_msg_list)
2023 THEN
2024 FND_MSG_PUB.initialize;
2025 END IF;
2026 -- Initialize API return status to success
2027 x_return_status := FND_API.G_RET_STS_SUCCESS;
2028 -- Initialize message list if p_init_msg_list is set to TRUE.
2029 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
2030 p_api_version,
2031 l_api_name,G_PKG_NAME)
2032 THEN
2033 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2034 END IF;
2035 ------------------------Start API Body ---------------------------------
2036 -- Check for any visit task items has been scheduled from MRP
2037 OPEN check_items_cur(p_visit_id);
2038 LOOP
2039 FETCH check_items_cur INTO l_schedule_items_rec;
2040 EXIT WHEN check_items_cur%NOTFOUND;
2041 IF (l_schedule_items_rec.visit_id IS NOT NULL AND
2042 l_schedule_items_rec.visit_task_id IS NOT NULL AND
2043 p_visit_task_id IS NULL) THEN
2044 -- Call Unschedule to load record into interface table
2045 --Assign the values
2046 l_req_material_rec.schedule_mat_id := l_schedule_items_rec.schedule_material_id;
2047 --
2048 -- Unschedule_Request (
2049 -- p_req_material_rec => l_req_material_rec);
2050 --
2051 ELSIF (l_schedule_items_rec.visit_id IS NOT NULL AND
2052 l_schedule_items_rec.visit_task_id IS NOT NULL AND
2053 l_schedule_items_rec.visit_task_id = p_visit_task_id ) THEN
2054 --Assign the values
2055 l_req_material_rec.schedule_mat_id := l_schedule_items_rec.schedule_material_id;
2056 --
2057 -- Unschedule_Request (
2058 -- p_req_material_rec => l_req_material_rec);
2059 --
2060 END IF;
2061 END LOOP;
2062 CLOSE check_items_cur;
2063
2064 ---------------------------End of Body---------------------------------------
2065 --Standard check to count messages
2066 l_msg_count := Fnd_Msg_Pub.count_msg;
2067
2068 IF l_msg_count > 0 THEN
2069 X_msg_count := l_msg_count;
2070 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2071 RAISE Fnd_Api.G_EXC_ERROR;
2072 END IF;
2073
2074 --Standard check for commit
2075 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
2076 COMMIT;
2077 END IF;
2078 -- Debug info
2079 IF G_DEBUG='Y' THEN
2080 Ahl_Debug_Pub.debug( 'End of private api Unschedule Task Items ','+MAMRP+');
2081 -- Check if API is called in debug mode. If yes, disable debug.
2082 Ahl_Debug_Pub.disable_debug;
2083 --
2084 END IF;
2085 EXCEPTION
2086 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2087 ROLLBACK TO Unschedule_Task_Items;
2088 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2089 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2090 p_count => x_msg_count,
2091 p_data => x_msg_data);
2092 IF G_DEBUG='Y' THEN
2093
2094 AHL_DEBUG_PUB.log_app_messages (
2095 x_msg_count, x_msg_data, 'ERROR' );
2096 AHL_DEBUG_PUB.debug( 'ahl_ltp_reqst_matrl_pvt. Unschedule Task Items ','+MAMRP+');
2097 -- Check if API is called in debug mode. If yes, disable debug.
2098 AHL_DEBUG_PUB.disable_debug;
2099 END IF;
2100
2101 WHEN FND_API.G_EXC_ERROR THEN
2102 ROLLBACK TO search_schedule_materials;
2103 X_return_status := FND_API.G_RET_STS_ERROR;
2104 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2105 p_count => x_msg_count,
2106 p_data => X_msg_data);
2107 IF G_DEBUG='Y' THEN
2108
2109 -- Debug info.
2110 AHL_DEBUG_PUB.log_app_messages (
2111 x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
2112 AHL_DEBUG_PUB.debug( 'ahl_ltp_reqst_matrl_pvt. Unschedule Task Items','+MAMRP+');
2113 -- Check if API is called in debug mode. If yes, disable debug.
2114 AHL_DEBUG_PUB.disable_debug;
2115 END IF;
2116
2117 WHEN OTHERS THEN
2118 ROLLBACK TO Unschedule_Task_Items;
2119 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2120 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2121 THEN
2122 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_REQST_MATRL_PVT',
2123 p_procedure_name => 'UNSCHEDULE_TASK_ITEMS',
2124 p_error_text => SUBSTR(SQLERRM,1,240));
2125 END IF;
2126 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2127 p_count => x_msg_count,
2128 p_data => X_msg_data);
2129 IF G_DEBUG='Y' THEN
2130
2131 -- Debug info.
2132 AHL_DEBUG_PUB.log_app_messages (
2133 x_msg_count, x_msg_data, 'SQL ERROR' );
2134 AHL_DEBUG_PUB.debug( 'ahl_ltp_reqst_matrl_pvt. Unschedule Task Items','+MTMRP+');
2135 -- Check if API is called in debug mode. If yes, disable debug.
2136 AHL_DEBUG_PUB.disable_debug;
2137 END IF;
2138
2139 END Unschedule_visit_Task_Items;
2140 --
2141 -- Start of Comments --
2142 -- Procedure name : Process_Planned_Materials
2143 -- Type : Private
2144 -- Function : This procedure Creates, Updates and Removes Planned materials information associated to scheduled
2145 -- visit, which are defined at Route Operation and Disposition level
2146 -- Pre-reqs :
2147 -- Parameters :
2148 --
2149 -- Standard IN Parameters :
2150 -- p_api_version IN NUMBER Required
2151 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
2152 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
2153 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
2154 -- Based on this flag, the API will set the default attributes.
2155 -- p_module_type In VARCHAR2 Default NULL
2156 -- This will be null.
2157 -- Standard out Parameters :
2158 -- x_return_status OUT VARCHAR2 Required
2159 -- x_msg_count OUT NUMBER Required
2160 -- x_msg_data OUT VARCHAR2 Required
2161 --
2162 -- Process_Planned_Materials Parameters :
2163 --
2164 --
2165 PROCEDURE Process_Planned_Materials (
2166 p_api_version IN NUMBER,
2167 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
2168 p_commit IN VARCHAR2 := FND_API.g_false,
2169 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
2170 p_visit_id IN NUMBER,
2171 p_visit_task_id IN NUMBER := NULL,
2172 p_org_id IN NUMBER := NULL,
2173 p_start_date IN DATE := NULL,
2174 p_visit_status IN VARCHAR2 := NULL,
2175 p_operation_flag IN VARCHAR2,
2176 x_planned_order_flag OUT NOCOPY VARCHAR2 ,
2177 x_return_status OUT NOCOPY VARCHAR2,
2178 x_msg_count OUT NOCOPY NUMBER,
2179 x_msg_data OUT NOCOPY VARCHAR2
2180 ) IS
2181
2182 --Standard local variables
2183 l_api_name CONSTANT VARCHAR2(30) := 'Process_Planned_Materials';
2184 l_api_version CONSTANT NUMBER := 1.0;
2185 l_msg_data VARCHAR2(2000);
2186 l_return_status VARCHAR2(1);
2187 l_msg_count NUMBER;
2188 l_commit VARCHAR2(10) := FND_API.g_false;
2189 l_planned_order_flag VARCHAR2(1) := 'N';
2190 l_assoc_id NUMBER ;
2191
2192 --priyan begin
2193 CURSOR get_assoc_primary_id (c_visit_id IN NUMBER)
2194 IS
2195 SELECT asso_primary_visit_id
2196 FROM ahl_visits_b
2197 WHERE visit_id = c_visit_id;
2198 --priyan end
2199 BEGIN
2200
2201 IF (l_log_procedure >= l_log_current_level)THEN
2202 fnd_log.string
2203 (
2204 l_log_procedure,
2205 'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Process_Planned_Materials',
2206 'At the start of PLSQL procedure'
2207 );
2208 END IF;
2209
2210 -- Standard start of API savepoint
2211 SAVEPOINT Process_Planned_Materials;
2212 -- Initialize message list if p_init_msg_list is set to TRUE
2213 IF FND_API.To_Boolean( p_init_msg_list) THEN
2214 FND_MSG_PUB.Initialize;
2215 END IF;
2216
2217 -- Initialize API return status to success
2218 x_return_status := FND_API.G_RET_STS_SUCCESS;
2219 --
2220 IF (l_log_statement >= l_log_current_level)THEN
2221 fnd_log.string
2222 (
2223 l_log_statement,
2224 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2225 'Request for Process Task Materials for Visit Id : '|| p_visit_id
2226 );
2227 fnd_log.string
2228 (
2229 l_log_statement,
2230 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2231 'Request for Process Task Materials for Visit Task Id : '|| p_visit_task_id
2232 );
2233 fnd_log.string
2234 (
2235 l_log_statement,
2236 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2237 'Request for Process Task Materials for Operation Flag : '|| p_operation_flag
2238 );
2239 END IF;
2240
2241 --priyan
2242 OPEN get_assoc_primary_id (p_visit_id);
2243 FETCH get_assoc_primary_id INTO l_assoc_id;
2244 CLOSE get_assoc_primary_id;
2245
2246 --priyan
2247 -- Added the check l_assoc_id IS NULL
2248 IF (p_visit_task_id IS NOT NULL AND l_assoc_id IS NULL AND p_visit_task_id <> FND_API.g_miss_num AND p_operation_flag = 'C' ) THEN
2249 -- if create
2250 IF (l_log_statement >= l_log_current_level)THEN
2251 fnd_log.string
2252 (
2253 l_log_statement,
2254 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2255 'Before Calling Create Task Materials for Visit Task Id : '|| p_visit_task_id
2256 );
2257 fnd_log.string
2258 (
2259 l_log_statement,
2260 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2261 'Before Calling Create Task Materials for Operation Flag : '|| p_operation_flag
2262 );
2263 END IF;
2264
2265 IF (l_log_procedure >= l_log_current_level) THEN
2266 fnd_log.string
2267 ( l_log_procedure,
2268 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2269 'Before calling Create Task Materials'
2270 );
2271 END IF;
2272
2273 Create_Task_Materials (
2274 p_api_version => l_api_version,
2275 p_init_msg_list => p_init_msg_list,
2276 p_commit => l_commit,
2277 p_validation_level => p_validation_level,
2278 p_visit_id => p_visit_id,
2279 p_visit_task_id => p_visit_task_id,
2280 x_return_status => l_return_status,
2281 x_msg_count => l_msg_count,
2282 x_msg_data => l_msg_data );
2283
2284 IF (l_log_procedure >= l_log_current_level) THEN
2285 fnd_log.string
2286 (
2287 l_log_procedure,
2288 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||': End API',
2289 'After calling Create Task Materials, Return Status : '|| l_return_status
2290 );
2291 END IF;
2292 -- Check Error Message stack.
2293 IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
2294 l_msg_count := FND_MSG_PUB.count_msg;
2295 IF l_msg_count > 0 THEN
2296 RAISE FND_API.G_EXC_ERROR;
2297 END IF;
2298 END IF;
2299 --priyan
2300 -- Added the check l_assoc_id IS NULL
2301 ELSIF (p_visit_id IS NOT NULL AND l_assoc_id IS NULL AND p_visit_id <> FND_API.g_miss_num AND p_operation_flag = 'U' ) THEN
2302 -- if update
2303 IF (l_log_statement >= l_log_current_level)THEN
2304 fnd_log.string
2305 (
2306 l_log_statement,
2307 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2308 'Request for Visit Org or Start date change for Visit Id : '|| p_visit_id
2309 );
2310 fnd_log.string
2311 (
2312 l_log_statement,
2313 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2314 'Request for Visit Org or Start date change for Operation Flag : '|| p_operation_flag
2315 );
2316 fnd_log.string
2317 (
2318 l_log_statement,
2319 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2320 'Request for Visit Org or Start date change for Org Id : '|| p_org_id
2321 );
2322 fnd_log.string
2323 (
2324 l_log_statement,
2325 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2326 'Request for Visit Org or Start date change for Start date : '|| p_start_date
2327 );
2328 END IF;
2329
2330 IF (l_log_procedure >= l_log_current_level) THEN
2331 fnd_log.string
2332 (
2333 l_log_procedure,
2334 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2335 'Before calling Modify Visit Task Materials'
2336 );
2337 END IF;
2338
2339 Modify_Visit_Task_Matrls (
2340 p_api_version => l_api_version,
2341 p_init_msg_list => p_init_msg_list,
2342 p_commit => l_commit,
2343 p_validation_level => p_validation_level,
2344 p_visit_id => p_visit_id,
2345 p_start_time => p_start_date,
2346 p_org_id => p_org_id,
2347 x_return_status => l_return_status,
2348 x_msg_count => l_msg_count,
2349 x_msg_data => l_msg_data);
2350
2351 IF (l_log_procedure >= l_log_current_level) THEN
2352 fnd_log.string
2353 (
2354 l_log_procedure,
2355 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||': End API',
2356 'After calling Create Planned Materials, Return Status : '|| l_return_status
2357 );
2358 END IF;
2359 -- Check Error Message stack.
2360 IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
2361 l_msg_count := FND_MSG_PUB.count_msg;
2362 IF l_msg_count > 0 THEN
2363 RAISE FND_API.G_EXC_ERROR;
2364 END IF;
2365 END IF;
2366
2367 -- anraj added
2368 ELSIF (p_visit_id IS NOT NULL AND p_visit_id <> FND_API.g_miss_num AND p_operation_flag = 'D') THEN
2369 -- delete mode called if org or dept or start date is nullified
2370 Unschedule_Visit_Materials (
2371 p_api_version => l_api_version,
2372 p_init_msg_list => p_init_msg_list,
2373 p_commit => l_commit,
2374 p_validation_level => p_validation_level,
2375 p_visit_id => p_visit_id,
2376 x_return_status => l_return_status,
2377 x_msg_count => l_msg_count,
2378 x_msg_data => l_msg_data);
2379
2380 -- Check Error Message stack.
2381 IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
2382 l_msg_count := FND_MSG_PUB.count_msg;
2383 IF l_msg_count > 0 THEN
2384 RAISE FND_API.G_EXC_ERROR;
2385 END IF;
2386 END IF;
2387 -- anraj
2388
2389 ELSIF (p_visit_id IS NOT NULL AND p_visit_id <> FND_API.g_miss_num AND p_operation_flag = 'R'
2390 AND p_visit_task_id IS NULL) THEN
2391 -- remove mode , with no task id
2392 IF (l_log_statement >= l_log_current_level)THEN
2393 fnd_log.string
2394 (
2395 l_log_statement,
2396 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2397 'Request for Removing visit materials for Visit Id : '|| p_visit_id
2398 );
2399 fnd_log.string
2400 (
2401 l_log_statement,
2402 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2403 'Request for Remove visit materials for Operation Flag : '|| p_operation_flag
2404 );
2405 END IF;
2406
2407 IF (l_log_procedure >= l_log_current_level) THEN
2408 fnd_log.string
2409 (
2410 l_log_procedure,
2411 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2412 'Before calling Remove Visit Task Materials'
2413 );
2414 END IF;
2415
2416 Remove_Visit_Task_Matrls (
2417 p_api_version => l_api_version,
2418 p_init_msg_list => p_init_msg_list,
2419 p_commit => l_commit,
2420 p_validation_level => p_validation_level,
2421 p_visit_id => p_visit_id,
2422 x_planned_order_flag => l_planned_order_flag ,
2423 x_return_status => l_return_status,
2424 x_msg_count => l_msg_count,
2425 x_msg_data => l_msg_data);
2426
2427 IF (l_log_procedure >= l_log_current_level) THEN
2428 fnd_log.string
2429 (
2430 l_log_procedure,
2431 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||': End API',
2432 'After calling Remove Visit Task Materials, Return Status : '|| l_return_status
2433 );
2434 END IF;
2435
2436 -- Check Error Message stack.
2437 IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
2438 l_msg_count := FND_MSG_PUB.count_msg;
2439 IF l_msg_count > 0 THEN
2440 RAISE FND_API.G_EXC_ERROR;
2441 END IF;
2442 END IF;
2443
2444 ELSIF (p_visit_task_id IS NOT NULL AND p_visit_task_id <> FND_API.g_miss_num AND p_operation_flag = 'R')
2445 THEN
2446 -- Remove mode with Task ID
2447 IF (l_log_statement >= l_log_current_level)THEN
2448 fnd_log.string
2449 (
2450 l_log_statement,
2451 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2452 'Request for Removing task materials for Visit Task Id : '|| p_visit_task_id
2453 );
2454 fnd_log.string
2455 (
2456 l_log_statement,
2457 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2458 'Request for Removing task materials for Operation Flag : '|| p_operation_flag
2459 );
2460 END IF;
2461
2462 IF (l_log_procedure >= l_log_current_level) THEN
2463 fnd_log.string
2464 (
2465 l_log_procedure,
2466 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2467 'Before calling Remove Visit Task Materials'
2468 );
2469 END IF;
2470
2471 Remove_Visit_Task_Matrls (
2472 p_api_version => l_api_version,
2473 p_init_msg_list => p_init_msg_list,
2474 p_commit => l_commit,
2475 p_validation_level => p_validation_level,
2476 p_visit_id => p_visit_id,
2477 p_visit_task_id => p_visit_task_id,
2478 x_planned_order_flag => l_planned_order_flag ,
2479 x_return_status => l_return_status,
2480 x_msg_count => l_msg_count,
2481 x_msg_data => l_msg_data);
2482
2483
2484 IF (l_log_procedure >= l_log_current_level) THEN
2485 fnd_log.string
2486 (
2487 l_log_procedure,
2488 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||': End API',
2489 'After calling Remove Visit Task Materials, Return Status : '|| l_return_status
2490 );
2491 END IF;
2492 -- Check Error Message stack.
2493 IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
2494 l_msg_count := FND_MSG_PUB.count_msg;
2495 IF l_msg_count > 0 THEN
2496 RAISE FND_API.G_EXC_ERROR;
2497 END IF;
2498 END IF;
2499
2500 ELSIF (p_visit_id IS NOT NULL AND p_visit_id <> FND_API.g_miss_num AND p_visit_status IN ('CLOSED', 'CANCELLED'))
2501 -- visitis in Closed or Cancelled status
2502 THEN
2503 IF (l_log_statement >= l_log_current_level)THEN
2504 fnd_log.string
2505 (
2506 l_log_statement,
2507 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2508 'Request for Visit Closed or Cancelled Update Unplanned materials for Visit Id : '|| p_visit_id
2509 );
2510 END IF;
2511
2512 IF (l_log_procedure >= l_log_current_level) THEN
2513 fnd_log.string
2514 (
2515 l_log_procedure,
2516 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2517 'Before calling Update Unplanned Visit Materials'
2518 );
2519 END IF;
2520
2521 Update_Unplanned_Matrls (
2522 p_api_version => l_api_version,
2523 p_init_msg_list => p_init_msg_list,
2524 p_commit => l_commit,
2525 p_validation_level => p_validation_level,
2526 p_visit_id => p_visit_id,
2527 x_return_status => l_return_status,
2528 x_msg_count => l_msg_count,
2529 x_msg_data => l_msg_data);
2530
2531 IF (l_log_procedure >= l_log_current_level) THEN
2532 fnd_log.string
2533 (
2534 l_log_procedure,
2535 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||': End API',
2536 'After calling Update Unplanned Materials, Return Status : '|| l_return_status
2537 );
2538 END IF;
2539 -- Check Error Message stack.
2540 IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
2541 l_msg_count := FND_MSG_PUB.count_msg;
2542 IF l_msg_count > 0 THEN
2543 RAISE FND_API.G_EXC_ERROR;
2544 END IF;
2545 END IF;
2546 END IF;
2547 -- Standard check of p_commit
2548 IF FND_API.TO_BOOLEAN(p_commit) THEN
2549 COMMIT WORK;
2550 END IF;
2551
2552 IF (l_log_procedure >= l_log_current_level)THEN
2553 fnd_log.string
2554 (
2555 l_log_procedure,
2556 'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Process Planned Materials.end',
2557 'At the end of PLSQL procedure'
2558 );
2559 END IF;
2560 EXCEPTION
2561 WHEN FND_API.G_EXC_ERROR THEN
2562 x_return_status := FND_API.G_RET_STS_ERROR;
2563 ROLLBACK TO Process_Planned_Materials;
2564 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2565 p_data => x_msg_data,
2566 p_encoded => fnd_api.g_false);
2567
2568
2569 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2570 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2571 ROLLBACK TO Process_Planned_Materials;
2572 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2573 p_data => x_msg_data,
2574 p_encoded => fnd_api.g_false);
2575
2576
2577 WHEN OTHERS THEN
2578 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2579 ROLLBACK TO Process_Planned_Materials;
2580 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2581 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
2582 p_procedure_name => 'Process_Planned_Materials',
2583 p_error_text => SUBSTR(SQLERRM,1,500));
2584 END IF;
2585 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2586 p_data => x_msg_data,
2587 p_encoded => fnd_api.g_false);
2588
2589
2590 END Process_Planned_Materials;
2591 --
2592 -- Start of Comments --
2593 -- Procedure name : Remove_Visit_Task_Matrls
2594 -- Type : Private
2595 -- Function : This procedure Created Planned materials information associated to scheduled
2596 -- visit, which are defined at Route Operation and Disposition level
2597 -- Pre-reqs :
2598 -- Parameters :
2599 --
2600 -- Standard IN Parameters :
2601 -- p_api_version IN NUMBER Required
2602 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
2603 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
2604 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
2605 -- Based on this flag, the API will set the default attributes.
2606 -- p_module_type In VARCHAR2 Default NULL
2607 -- This will be null.
2608 -- Standard out Parameters :
2609 -- x_return_status OUT VARCHAR2 Required
2610 -- x_msg_count OUT NUMBER Required
2611 -- x_msg_data OUT VARCHAR2 Required
2612 --
2613 -- Create_Planned_Materials Parameters :
2614 -- p_visit_id IN NUMBER,Required
2615 --
2616 --
2617 PROCEDURE Remove_Visit_Task_Matrls (
2618 p_api_version IN NUMBER,
2619 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
2620 p_commit IN VARCHAR2 := FND_API.g_false,
2621 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
2622 p_visit_id IN NUMBER,
2623 p_visit_task_id IN NUMBER := NULL,
2624 x_planned_order_flag OUT NOCOPY VARCHAR2 ,
2625 x_return_status OUT NOCOPY VARCHAR2,
2626 x_msg_count OUT NOCOPY NUMBER,
2627 x_msg_data OUT NOCOPY VARCHAR2)
2628 IS
2629
2630 CURSOR visit_task_details_cur (c_visit_id IN NUMBER,
2631 c_visit_task_id IN NUMBER)
2632 IS
2633 SELECT vs.visit_id,
2634 vs.organization_id,
2635 vt.visit_task_id
2636 FROM ahl_visits_vl vs,
2637 ahl_visit_tasks_vl vt
2638 WHERE vs.visit_id = vt.visit_id
2639 AND vs.visit_id = c_visit_id
2640 AND vt.visit_task_id = c_visit_task_id;
2641 --To Retrieve visit task planned materials
2642 CURSOR visit_task_mtrls_cur (c_visit_task_id IN NUMBER)
2643 IS
2644 SELECT visit_id,
2645 visit_task_id,
2646 schedule_material_id,
2647 object_version_number,
2648 inventory_item_id,
2649 scheduled_date,
2650 scheduled_quantity
2651 FROM ahl_visit_task_matrl_v
2652 WHERE visit_task_id = c_visit_task_id;
2653
2654 --Retrieve visit level planned materials
2655 CURSOR visit_mtrls_cur (c_visit_id IN NUMBER)
2656 IS
2657 SELECT visit_id,
2658 visit_task_id,
2659 schedule_material_id,
2660 object_version_number,
2661 inventory_item_id,
2662 scheduled_date,
2663 scheduled_quantity
2664 FROM ahl_visit_task_matrl_v
2665 WHERE visit_id = c_visit_id;
2666
2667 --Standard local variables
2668 l_api_name CONSTANT VARCHAR2(30) := 'Remove_Visit_Task_Matrls';
2669 l_api_version CONSTANT NUMBER := 1.0;
2670 l_msg_data VARCHAR2(2000);
2671 l_return_status VARCHAR2(1);
2672 l_msg_count NUMBER;
2673 l_visit_task_details_rec visit_task_details_cur%ROWTYPE;
2674 l_visit_task_mtrls_rec visit_task_mtrls_cur%ROWTYPE;
2675 l_visit_mtrls_rec visit_mtrls_cur%ROWTYPE;
2676 l_visit_id NUMBER := p_visit_id;
2677 l_visit_task_id NUMBER := p_visit_task_id;
2678 l_planned_order_flag VARCHAR2(1):= 'N';
2679 BEGIN
2680 IF (l_log_procedure >= l_log_current_level)THEN
2681 fnd_log.string
2682 (
2683 l_log_procedure,
2684 'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Remove_Visit_Task_Matrls',
2685 'At the start of PLSQL procedure'
2686 );
2687 END IF;
2688 -- Standard start of API savepoint
2689 SAVEPOINT Remove_Visit_Task_Matrls;
2690 -- Initialize message list if p_init_msg_list is set to TRUE
2691 IF FND_API.To_Boolean( p_init_msg_list) THEN
2692 FND_MSG_PUB.Initialize;
2693 END IF;
2694 -- Initialize API return status to success
2695 x_return_status := FND_API.G_RET_STS_SUCCESS;
2696
2697 IF (l_log_statement >= l_log_current_level)THEN
2698 fnd_log.string
2699 (
2700 l_log_statement,
2701 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2702 'Request for Remove Task Materials for Visit Id : '|| l_visit_id
2703 );
2704 fnd_log.string
2705 (
2706 l_log_statement,
2707 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2708 'Request for Remove Task Materials for Visit Task Id : '|| l_visit_task_id
2709 );
2710 END IF;
2711
2712 IF ( l_visit_id IS NOT NULL AND l_visit_id <> fnd_api.g_miss_num ) THEN
2713 --Get details
2714 OPEN visit_task_details_cur(l_visit_id,l_visit_task_id);
2715 FETCH visit_task_details_cur INTO l_visit_task_details_rec;
2716 CLOSE visit_task_details_cur;
2717
2718 IF (l_log_statement >= l_log_current_level)THEN
2719 fnd_log.string
2720 (
2721 l_log_statement,
2722 'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Remove_Visit_Task_Matrls',
2723 ' After visit task details cur, Visit Id: ' || l_visit_id
2724 );
2725 END IF;
2726
2727 --Check for deleting a visit
2728 IF (l_visit_task_id IS NOT NULL AND l_visit_task_id <> fnd_api.g_miss_num)
2729 THEN
2730 IF (l_log_statement >= l_log_current_level)THEN
2731 fnd_log.string
2732 (
2733 l_log_statement,
2734 'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Remove_Visit_Task_Matrls',
2735 ' Before Retrieving task materials cur, Visit Task Id: ' || l_visit_task_id
2736 );
2737 END IF;
2738 --Retrieve task materials only
2739 OPEN visit_task_mtrls_cur(l_visit_task_id);
2740 LOOP
2741 FETCH visit_task_mtrls_cur INTO l_visit_task_mtrls_rec;
2742 EXIT WHEN visit_task_mtrls_cur%NOTFOUND;
2743 -- update request quanity zero
2744 IF l_visit_task_mtrls_rec.schedule_material_id IS NOT NULL THEN
2745 UPDATE ahl_schedule_materials
2746 SET requested_quantity = 0,
2747 status = 'DELETED',
2748 object_version_number = l_visit_task_mtrls_rec.object_version_number + 1
2749 WHERE scheduled_material_id = l_visit_task_mtrls_rec.schedule_material_id;
2750 END IF; --Schedule material not null
2751 END LOOP;
2752 CLOSE visit_task_mtrls_cur;
2753 ELSE
2754 IF (l_log_statement >= l_log_current_level)THEN
2755 fnd_log.string
2756 (
2757 l_log_statement,
2758 'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Remove_Visit_Task_Matrls',
2759 ' Before Retrieving all visit task materials cur, For Visit Id: ' || l_visit_id
2760 );
2761 END IF;
2762
2763 -- Retrieve all the visit tasks
2764 OPEN visit_mtrls_cur(l_visit_id);
2765 LOOP
2766 FETCH visit_mtrls_cur INTO l_visit_mtrls_rec;
2767 EXIT WHEN visit_mtrls_cur%NOTFOUND;
2768 -- update request quanity zero
2769 IF l_visit_mtrls_rec.schedule_material_id IS NOT NULL THEN
2770 UPDATE ahl_schedule_materials
2771 SET requested_quantity = 0,
2772 status = 'DELETED',
2773 object_version_number = l_visit_mtrls_rec.object_version_number + 1
2774 WHERE scheduled_material_id = l_visit_mtrls_rec.schedule_material_id;
2775 END IF; --Schedule material not null
2776 END LOOP;
2777 CLOSE visit_mtrls_cur;
2778
2779 -- Serial Number reservation Enh.
2780 -- delete all reservations for this visit on organization change
2781 AHL_RSV_RESERVATIONS_PVT.DELETE_VISIT_RESERVATIONS(
2782 X_RETURN_STATUS => X_RETURN_STATUS,
2783 P_VISIT_ID => p_visit_id);
2784
2785 IF (l_log_statement >= l_log_current_level)THEN
2786 fnd_log.string
2787 (
2788 l_log_statement,
2789 'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Remove_Visit_Task_Matrls',
2790 ' After calling AHL_RSV_RESERVATIONS_PVT.DELETE_VISIT_RESERVATIONS ErrorMessage Status : ' || X_RETURN_STATUS
2791 );
2792 END IF;
2793
2794 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
2795 RAISE Fnd_Api.g_exc_error;
2796 END IF;
2797 END IF; --Just task deletion
2798 END IF;
2799 x_planned_order_flag := l_planned_order_flag;
2800
2801 --Standard check to count messages
2802 l_msg_count := Fnd_Msg_Pub.count_msg;
2803
2804 IF l_msg_count > 0 THEN
2805 X_msg_count := l_msg_count;
2806 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2807 RAISE Fnd_Api.G_EXC_ERROR;
2808 END IF;
2809
2810 -- Standard check of p_commit
2811 IF FND_API.TO_BOOLEAN(p_commit) THEN
2812 COMMIT WORK;
2813 END IF;
2814
2815 IF (l_log_procedure >= l_log_current_level)THEN
2816 fnd_log.string
2817 (
2818 l_log_procedure,
2819 'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Remove Visit Task Matrls.end',
2820 'At the end of PLSQL procedure'
2821 );
2822 END IF;
2823 EXCEPTION
2824 WHEN FND_API.G_EXC_ERROR THEN
2825 x_return_status := FND_API.G_RET_STS_ERROR;
2826 ROLLBACK TO Remove_Visit_Task_Matrls;
2827 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2828 p_data => x_msg_data,
2829 p_encoded => fnd_api.g_false);
2830
2831
2832 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2833 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2834 ROLLBACK TO Remove_Visit_Task_Matrls;
2835 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2836 p_data => x_msg_data,
2837 p_encoded => fnd_api.g_false);
2838
2839
2840 WHEN OTHERS THEN
2841 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2842 ROLLBACK TO Remove_Visit_Task_Matrls;
2843 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2844 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
2845 p_procedure_name => 'REMOVE_VISIT_TASK_MATRLS',
2846 p_error_text => SUBSTR(SQLERRM,1,500));
2847 END IF;
2848 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2849 p_data => x_msg_data,
2850 p_encoded => fnd_api.g_false);
2851
2852
2853 END Remove_Visit_Task_Matrls;
2854 --
2855 -- Start of Comments --
2856 -- Procedure name : Update_Unplanned_Matrls
2857 -- Type : Private
2858 -- Function : This procedure Created Planned materials information associated to scheduled
2859 -- visit, which are defined at Route Operation and Disposition level
2860 -- Pre-reqs :
2861 -- Parameters :
2862 --
2863 -- Standard IN Parameters :
2864 -- p_api_version IN NUMBER Required
2865 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
2866 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
2867 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
2868 -- Based on this flag, the API will set the default attributes.
2869 -- p_module_type In VARCHAR2 Default NULL
2870 -- This will be null.
2871 -- Standard out Parameters :
2872 -- x_return_status OUT VARCHAR2 Required
2873 -- x_msg_count OUT NUMBER Required
2874 -- x_msg_data OUT VARCHAR2 Required
2875 --
2876 -- Update_Unplanned_Materials Parameters :
2877 -- p_visit_id IN NUMBER,Required
2878 --
2879 --
2880 PROCEDURE Update_Unplanned_Matrls (
2881 p_api_version IN NUMBER,
2882 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
2883 p_commit IN VARCHAR2 := FND_API.g_false,
2884 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
2885 p_visit_id IN NUMBER,
2886 x_return_status OUT NOCOPY VARCHAR2,
2887 x_msg_count OUT NOCOPY NUMBER,
2888 x_msg_data OUT NOCOPY VARCHAR2)
2889 IS
2890 CURSOR visit_mtrls_cur (c_visit_id IN NUMBER)
2891 IS
2892 SELECT visit_id,
2893 visit_task_id,
2894 scheduled_material_id,
2895 object_version_number
2896 FROM ahl_schedule_materials
2897 WHERE visit_id = c_visit_id
2898 AND status = 'ACTIVE';
2899
2900 --Standard local variables
2901 l_api_name CONSTANT VARCHAR2(30) := 'Update_Unplanned_Matrls';
2902 l_api_version CONSTANT NUMBER := 1.0;
2903 l_msg_data VARCHAR2(2000);
2904 l_return_status VARCHAR2(1);
2905 l_msg_count NUMBER;
2906 l_visit_mtrls_rec visit_mtrls_cur%ROWTYPE;
2907
2908 BEGIN
2909 IF (l_log_procedure >= l_log_current_level)THEN
2910 fnd_log.string
2911 (
2912 l_log_procedure,
2913 'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Update_Unplanned_Matrls',
2914 'At the start of PLSQL procedure'
2915 );
2916 END IF;
2917
2918 -- Standard start of API savepoint
2919 SAVEPOINT Update_Unplanned_Matrls;
2920
2921 -- Initialize message list if p_init_msg_list is set to TRUE
2922 IF FND_API.To_Boolean( p_init_msg_list) THEN
2923 FND_MSG_PUB.Initialize;
2924 END IF;
2925 -- Initialize API return status to success
2926 x_return_status := FND_API.G_RET_STS_SUCCESS;
2927
2928 IF (l_log_statement >= l_log_current_level)THEN
2929 fnd_log.string
2930 (
2931 l_log_statement,
2932 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2933 'Request for Update Materials for Visit Id : '|| p_visit_id
2934 );
2935 END IF;
2936
2937 --Retrieve all the materials
2938 OPEN visit_mtrls_cur(p_visit_id);
2939 LOOP
2940 FETCH visit_mtrls_cur INTO l_visit_mtrls_rec;
2941 EXIT WHEN visit_mtrls_cur%NOTFOUND;
2942 IF l_visit_mtrls_rec.scheduled_material_id IS NOT NULL THEN
2943 UPDATE ahl_schedule_materials
2944 SET STATUS = 'HISTORY',
2945 OBJECT_VERSION_NUMBER = l_visit_mtrls_rec.object_version_number
2946 WHERE scheduled_material_id = l_visit_mtrls_rec.scheduled_material_id;
2947 END IF;
2948 END LOOP;
2949 CLOSE visit_mtrls_cur;
2950
2951 -- Serial Number reservation Enh.
2952 -- Delete all the reservations for this visit
2953 AHL_RSV_RESERVATIONS_PVT.DELETE_VISIT_RESERVATIONS(
2954 X_RETURN_STATUS => X_RETURN_STATUS,
2955 P_VISIT_ID => p_visit_id);
2956
2957 IF (l_log_statement >= l_log_current_level)THEN
2958 fnd_log.string
2959 (
2960 l_log_statement,
2961 'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Remove_Visit_Task_Matrls',
2962 ' After calling AHL_RSV_RESERVATIONS_PVT.DELETE_VISIT_RESERVATIONS ErrorMessage Status : ' || X_RETURN_STATUS
2963 );
2964 END IF;
2965
2966 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
2967 RAISE Fnd_Api.g_exc_error;
2968 END IF;
2969
2970 -- Standard check of p_commit
2971 IF FND_API.TO_BOOLEAN(p_commit) THEN
2972 COMMIT WORK;
2973 END IF;
2974
2975 IF (l_log_procedure >= l_log_current_level)THEN
2976 fnd_log.string
2977 (
2978 l_log_procedure,
2979 'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Update Unplanned Matrls.end',
2980 'At the end of PLSQL procedure'
2981 );
2982 END IF;
2983 EXCEPTION
2984 WHEN FND_API.G_EXC_ERROR THEN
2985 x_return_status := FND_API.G_RET_STS_ERROR;
2986 ROLLBACK TO Update_Unplanned_Matrls;
2987 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2988 p_data => x_msg_data,
2989 p_encoded => fnd_api.g_false);
2990 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2991 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2992 ROLLBACK TO Update_Unplanned_Matrls;
2993 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2994 p_data => x_msg_data,
2995 p_encoded => fnd_api.g_false);
2996 WHEN OTHERS THEN
2997 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2998 ROLLBACK TO Update_Unplanned_Matrls;
2999 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3000 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
3001 p_procedure_name => 'UPDATE_UNPLANNED_MATRLS',
3002 p_error_text => SUBSTR(SQLERRM,1,500));
3003 END IF;
3004 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3005 p_data => x_msg_data,
3006 p_encoded => fnd_api.g_false);
3007 END Update_Unplanned_Matrls;
3008
3009 --
3010 -- Start of Comments --
3011 -- Procedure name : MODIFY_VISIT_RESERVATIONS
3012 -- Type : Private
3013 -- Function : Handles Material reservation incase of change in Visit Organisation.
3014 -- : Added for Serial NUmber Reservation by Senthil.
3015 --
3016 -- Pre-reqs :
3017 -- Parameters :
3018 --
3019 -- Standard out Parameters :
3020 -- x_return_status OUT VARCHAR2 Required
3021 --
3022 -- Modify_Visit_Reservations Parameters :
3023 -- p_visit_id IN NUMBER,Required
3024 --
3025 --
3026 PROCEDURE Modify_Visit_Reservations (
3027 p_visit_id IN NUMBER,
3028 x_return_status OUT NOCOPY VARCHAR2)
3029
3030 IS
3031 -- AnRaj: Changed the WHERE clause , for Performance improvement
3032 CURSOR get_del_mtl_req_csr(c_visit_id IN NUMBER) IS
3033 SELECT mat.scheduled_material_id
3034 FROM ahl_schedule_materials mat,
3035 ahl_visit_tasks_b vt
3036 WHERE vt.visit_id = c_visit_id
3037 AND vt.status_code = 'DELETED'
3038 AND vt.visit_task_id = mat.visit_task_id
3039 AND EXISTS (SELECT reservation_id
3040 FROM mtl_reservations RSV
3041 WHERE RSV.external_source_code = 'AHL'
3042 AND RSV.demand_source_line_detail = mat.scheduled_material_id
3043 AND RSV.organization_id = mat.organization_id
3044 AND RSV.requirement_date = mat.requested_date
3045 AND RSV.inventory_item_id = mat.inventory_item_id );
3046
3047 CURSOR get_cur_org_csr(p_visit_id IN NUMBER) IS
3048 SELECT organization_id
3049 FROM ahl_visits_b
3050 WHERE visit_id = p_visit_id;
3051
3052 CURSOR get_prev_org_csr(p_visit_id IN NUMBER) IS
3053 SELECT organization_id
3054 FROM mtl_reservations
3055 WHERE external_source_code = 'AHL'
3056 AND demand_source_header_id in ( SELECT visit_task_id
3057 FROM ahl_visit_tasks_b
3058 WHERE visit_id = p_visit_id);
3059 --Standard local variables
3060 l_api_name CONSTANT VARCHAR2(30) := 'Modify_Visit_Reservations';
3061 l_api_version CONSTANT NUMBER := 1.0;
3062 l_msg_data VARCHAR2(2000);
3063 l_return_status VARCHAR2(1);
3064 l_msg_count NUMBER;
3065
3066 l_cur_org_id NUMBER;
3067 l_prev_org_id NUMBER;
3068 l_org_count NUMBER;
3069 l_scheduled_material_id NUMBER;
3070 BEGIN
3071 IF (l_log_procedure >= l_log_current_level)THEN
3072 fnd_log.string
3073 (
3074 l_log_procedure,
3075 'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Modify_Visit_Reservations.Start',
3076 'At the end of PLSQL procedure'
3077 );
3078 END IF;
3079
3080 OPEN get_cur_org_csr(p_visit_id) ;
3081 FETCH get_cur_org_csr into l_cur_org_id;
3082 CLOSE get_cur_org_csr;
3083
3084
3085 OPEN get_prev_org_csr (p_visit_id) ;
3086 FETCH get_prev_org_csr into l_prev_org_id;
3087 CLOSE get_prev_org_csr;
3088
3089
3090 SELECT count(distinct organization_id)
3091 INTO l_org_count
3092 FROM mtl_reservations
3093 WHERE external_source_code = 'AHL'
3094 AND demand_source_header_id in ( SELECT visit_task_id
3095 FROM ahl_visit_tasks_b
3096 WHERE visit_id = p_visit_id);
3097
3098 IF (l_log_statement >= l_log_current_level)THEN
3099 fnd_log.string
3100 (
3101 l_log_statement,
3102 'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Modify_Visit_Reservations',
3103 'l_org_count : '||l_org_count||' l_cur_org_id:'||l_cur_org_id||
3104 ' l_prev_org_id:'||l_prev_org_id
3105 );
3106 END IF;
3107
3108
3109 IF l_prev_org_id IS NULL THEN
3110 Return;
3111 ELSIF l_org_count > 1 THEN
3112 FND_MESSAGE.set_name('AHL', 'AHL_LTP_MULTI_ORG');
3113 FND_MSG_PUB.ADD;
3114 RAISE Fnd_Api.g_exc_error;
3115 END IF;
3116
3117 IF l_prev_org_id <> l_cur_org_id THEN
3118 -- delete all reservations for this visit on organization change
3119 AHL_RSV_RESERVATIONS_PVT.DELETE_VISIT_RESERVATIONS(
3120 X_RETURN_STATUS => X_RETURN_STATUS,
3121 P_VISIT_ID => p_visit_id);
3122
3123 IF (l_log_statement >= l_log_current_level)THEN
3124 fnd_log.string
3125 (
3126 l_log_statement,
3127 'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Modify_Visit_Reservations',
3128 'After calling AHL_RSV_RESERVATIONS_PVT.DELETE_VISIT_RESERVATIONS:X_RETURN_STATUS '||X_RETURN_STATUS
3129 );
3130 END IF;
3131
3132 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
3133 RAISE Fnd_Api.g_exc_error;
3134 END IF;
3135 ELSE
3136 IF (l_log_statement >= l_log_current_level)THEN
3137 fnd_log.string
3138 (
3139 l_log_statement,
3140 'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Modify_Visit_Reservations',
3141 'In the else part of check l_prev_org_id <> l_cur_org_id'
3142 );
3143 END IF;
3144 -- Get all the material requirements with reservation created for deleted tasks
3145 OPEN get_del_mtl_req_csr (p_visit_id);
3146 LOOP
3147 Fetch get_del_mtl_req_csr into l_scheduled_material_id;
3148 EXIT WHEN get_del_mtl_req_csr%NOTFOUND;
3149 -- Delete all the reservations made for this requirement
3150 AHL_RSV_RESERVATIONS_PVT.Delete_Reservation(
3151 p_module_type => NULL,
3152 x_return_status => l_return_status,
3153 x_msg_count => l_msg_count,
3154 x_msg_data => l_msg_data,
3155 p_scheduled_material_id => l_scheduled_material_id
3156 );
3157
3158 IF (l_log_statement >= l_log_current_level)THEN
3159 fnd_log.string
3160 (
3161 l_log_statement,
3162 'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Modify_Visit_Reservations',
3163 'After calling AHL_RSV_RESERVATIONS_PVT.Delete_Reservation:l_return_status '||l_return_status
3164 );
3165 END IF;
3166 -- Return status check and throw exception if return status is not success;
3167 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3168 CLOSE get_del_mtl_req_csr;
3169 RAISE Fnd_Api.g_exc_error;
3170 END IF;
3171 END LOOP; -- For all the material requirements of the deleted tasks
3172 CLOSE get_del_mtl_req_csr;
3173 -- Update all the reservations made for this visit with new requested date and scheduled material ID
3174
3175 AHL_RSV_RESERVATIONS_PVT.Update_Visit_Reservations(
3176 X_RETURN_STATUS => x_return_status,
3177 P_VISIT_ID => p_visit_id);
3178
3179 IF (l_log_statement >= l_log_current_level)THEN
3180 fnd_log.string
3181 (
3182 l_log_statement,
3183 'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Modify_Visit_Reservations',
3184 'After calling AHL_RSV_RESERVATIONS_PVT.Update_Visit_Reservations:x_return_status '||x_return_status
3185 );
3186 END IF;
3187
3188 -- Return status check and throw exception if return status is not success;
3189 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3190 RAISE Fnd_Api.g_exc_error;
3191 END IF;
3192 END IF; -- IF l_prev_org_id <> l_cur_org_id
3193
3194 IF (l_log_procedure >= l_log_current_level)THEN
3195 fnd_log.string
3196 (
3197 l_log_procedure,
3198 'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Modify_Visit_Reservations.end',
3199 'At the end of PLSQL procedure'
3200 );
3201 END IF;
3202 END Modify_Visit_Reservations;
3203
3204 --------------------------------------------------------------------
3205 -- PROCEDURE
3206 -- Update_Material_Reqrs_status
3207 --
3208 -- PURPOSE
3209 -- To update the status of material requirements to 'HISTORY'
3210 -- when the work-order is CANCELLED.
3211 --
3212 -- Bug#6898408 Initial Version Created by Richa
3213 --------------------------------------------------------------------
3214 PROCEDURE Update_Material_Reqrs_status
3215 ( p_api_version IN NUMBER,
3216 p_init_msg_list IN VARCHAR2,
3217 p_commit IN VARCHAR2,
3218 p_validation_level IN NUMBER,
3219 p_module_type IN VARCHAR2,
3220 p_visit_task_id IN NUMBER,
3221 x_return_status OUT NOCOPY VARCHAR2,
3222 x_msg_count OUT NOCOPY NUMBER,
3223 x_msg_data OUT NOCOPY VARCHAR2
3224 )
3225 IS
3226 -- Declare local variables
3227 l_api_name CONSTANT VARCHAR2(30) := 'Update_Material_Reqrs_status';
3228 l_api_version CONSTANT NUMBER := 1.0;
3229 l_init_msg_list VARCHAR2(1) := 'F';
3230 l_return_status VARCHAR2(1);
3231 l_msg_count NUMBER;
3232 l_msg_data VARCHAR2(2000);
3233 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
3234 l_sch_material_id NUMBER := 0;
3235
3236 BEGIN
3237 -- Standard start of API savepoint
3238 SAVEPOINT Update_Material_Reqrs_sts;
3239
3240 -- Initialize return status to success before any code logic/validation
3241 x_return_status:= FND_API.G_RET_STS_SUCCESS;
3242
3243 -- Standard call to check for call compatibility
3244 IF NOT FND_API.COMPATIBLE_API_CALL (l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
3245 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3246 END IF;
3247
3248 -- Initialize message list if p_init_msg_list = FND_API.G_TRUE
3249 IF FND_API.TO_BOOLEAN(p_init_msg_list) THEN
3250 FND_MSG_PUB.INITIALIZE;
3251 END IF;
3252
3253 -- Log API entry point
3254 IF (l_log_procedure >= l_log_current_level) THEN
3255 fnd_log.string( l_log_procedure,L_DEBUG_KEY ||'.begin','At the start of PL SQL procedure - Task id = '||p_visit_task_id);
3256 END IF;
3257
3258 IF (p_visit_task_id IS NULL) THEN
3259 IF (l_log_statement >= l_log_current_level) THEN
3260 fnd_log.string( l_log_statement,L_DEBUG_KEY,'Task id is null' );
3261 END IF;
3262 Fnd_Message.SET_NAME('AHL','AHL_VISIT_TASKID_NULL');
3263 Fnd_Msg_Pub.ADD;
3264 RAISE Fnd_Api.G_EXC_ERROR;
3265 END IF;
3266
3267 --Update the status of the record to 'HISTORY'
3268 UPDATE ahl_Schedule_materials
3269 SET STATUS = 'HISTORY',
3270 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
3271 LAST_UPDATE_DATE = sysdate,
3272 LAST_UPDATED_BY = Fnd_Global.USER_ID,
3273 LAST_UPDATE_LOGIN = Fnd_Global.LOGIN_ID
3274 WHERE visit_task_id = p_visit_task_id
3275 AND STATUS = 'ACTIVE';
3276
3277 -- Standard check of p_commit
3278 IF Fnd_Api.To_Boolean (p_commit) THEN
3279 COMMIT WORK;
3280 END IF;
3281
3282 IF (l_log_procedure >= l_log_current_level) THEN
3283 fnd_log.string(l_log_procedure,
3284 L_DEBUG_KEY ||'.end',
3285 'No of rows updated - '||SQL%ROWCOUNT);
3286 fnd_log.string(l_log_procedure,
3287 L_DEBUG_KEY ||'.end',
3288 'At the end of PL SQL procedure. Return Status =' || x_return_status);
3289 END IF;
3290
3291 EXCEPTION
3292 WHEN Fnd_Api.g_exc_error THEN
3293 ROLLBACK TO Update_Material_Reqrs_sts;
3294 x_return_status := Fnd_Api.g_ret_sts_error;
3295 Fnd_Msg_Pub.count_and_get (
3296 p_encoded => Fnd_Api.g_false,
3297 p_count => x_msg_count,
3298 p_data => x_msg_data);
3299
3300 WHEN Fnd_Api.g_exc_unexpected_error THEN
3301 ROLLBACK TO Update_Material_Reqrs_sts;
3302 x_return_status := Fnd_Api.g_ret_sts_unexp_error;
3303 Fnd_Msg_Pub.count_and_get (
3304 p_encoded => Fnd_Api.g_false,
3305 p_count => x_msg_count,
3306 p_data => x_msg_data);
3307
3308 WHEN OTHERS THEN
3309 ROLLBACK TO Update_Material_Reqrs_sts;
3310 x_return_status := Fnd_Api.g_ret_sts_unexp_error;
3311 Fnd_Msg_Pub.count_and_get (
3312 p_encoded => Fnd_Api.g_false,
3313 p_count => x_msg_count,
3314 p_data => x_msg_data );
3315 END Update_Material_Reqrs_status;
3316
3317 END AHL_LTP_REQST_MATRL_PVT;