[Home] [Help]
PACKAGE BODY: APPS.AHL_VWP_PLAN_TASKS_PVT
Source
1 PACKAGE BODY AHL_VWP_PLAN_TASKS_PVT AS
2 /* $Header: AHLVPLNB.pls 120.9 2008/04/09 06:10:05 rnahata ship $ */
3 -----------------------------------------------------------
4 -- PACKAGE
5 -- AHL_VWP_PLAN_TASKS_PVT
6 --
7 -- PURPOSE
8 -- This package is a Private API for Creating VWP Visit Planned Tasks in
9 -- CMRO. It contains specification for pl/sql records and tables
10 --
11 -- Create_Planned_Task (see below for specification)
12 -- Create_Summary_Child_Tasks (see below for specification)
13 -- Asso_Inst_Dept_to_Tasks (see below for specification)
14 -- Update_Planned_Task (see below for specification)
15 -- Delete_Planned_Task (see below for specification)
16 --
17 -- NOTES
18 --
19 --
20 -- HISTORY
21 -- 12-MAY_2002 Shbhanda Created.
22 -- 21-FEB-2003 YAZHOU Separated from Task package
23 -- 06-AUG-2003 SHBHANDA 11.5.10 Changes.
24
25 -----------------------------------------------------------
26 -- Define Global CONSTANTS -------
27 -----------------------------------------------------------
28 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AHL_VWP_PLAN_TASKS_PVT';
29 -----------------------------------------------------------------
30
31 ------------------------------------------------------------------
32 -- START: Defining local functions and procedures SIGNATURES --
33 --------------------------------------------------------------------
34 -- To Check_Visit_Task_Req_Items
35 PROCEDURE Check_Visit_Task_Req_Items (
36 p_task_rec IN AHL_VWP_RULES_PVT.Task_Rec_Type,
37 x_return_status OUT NOCOPY VARCHAR2
38 );
39
40 -- To Check_Visit_Task_UK_Items
41 PROCEDURE Check_Visit_Task_UK_Items (
42 p_task_rec IN AHL_VWP_RULES_PVT.Task_Rec_Type,
43 p_validation_mode IN VARCHAR2 := Jtf_Plsql_Api.g_create,
44 x_return_status OUT NOCOPY VARCHAR2
45 );
46
47 -- To Check_Task_Items
48 PROCEDURE Check_Task_Items (
49 p_Task_rec IN AHL_VWP_RULES_PVT.task_rec_type,
50 p_validation_mode IN VARCHAR2 := Jtf_Plsql_Api.g_create,
51 x_return_status OUT NOCOPY VARCHAR2
52 );
53
54 PROCEDURE create_mr_tasks(
55 p_ue_id IN NUMBER,
56 p_parent_ue_id IN NUMBER,
57 p_visit_id IN NUMBER,
58 p_department_id IN NUMBER,
59 p_service_request_id IN NUMBER,
60 -- Added by rnahata for Issue 105 - pass the qty
61 p_quantity IN NUMBER,
62 p_type IN VARCHAR2
63 );
64
65 -- To Validate_Visit_Task
66 /*
67 PROCEDURE Validate_Visit_Task (
68 p_api_version IN NUMBER,
69 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
70 p_commit IN VARCHAR2 := Fnd_Api.g_false,
71 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
72 p_Task_rec IN AHL_VWP_RULES_PVT.task_rec_type,
73 x_return_status OUT NOCOPY VARCHAR2,
74 x_msg_count OUT NOCOPY NUMBER,
75 x_msg_data OUT NOCOPY VARCHAR2
76 );
77 */
78 -- To assign Null to missing attributes of visit while creation/updation.
79 PROCEDURE Default_Missing_Attribs(
80 p_x_task_rec IN OUT NOCOPY AHL_VWP_RULES_PVT.Task_Rec_Type
81 );
82
83 -- To associated Service Request Or Serial Number to Tasks
84 PROCEDURE Asso_Inst_Dept_to_Tasks (
85 p_module_type IN VARCHAR2,
86 p_x_task_Rec IN OUT NOCOPY AHL_VWP_RULES_PVT.Task_Rec_Type
87 );
88
89 --------------------------------------------------------------------
90 -- END: Defining local functions and procedures SIGNATURES --
91 --------------------------------------------------------------------
92
93 --------------------------------------------------------------------
94 -- START: Defining local functions and procedures BODY --
95 --------------------------------------------------------------------
96 --------------------------------------------------------------------
97 -- PROCEDURE
98 -- Default_Missing_Attribs
99 --
100 -- PURPOSE
101 -- For all optional fields check if its g_miss_num/g_miss_char/
102 -- g_miss_date then Null else the value
103
104 --------------------------------------------------------------------
105 PROCEDURE Default_Missing_Attribs
106 ( p_x_task_rec IN OUT NOCOPY AHL_VWP_RULES_PVT.Task_Rec_Type)
107 AS
108 BEGIN
109 IF p_x_task_rec.DURATION = Fnd_Api.G_MISS_NUM THEN
110 p_x_task_rec.DURATION := NULL;
111 ELSE
112 p_x_task_rec.DURATION := p_x_task_rec.DURATION;
113 END IF;
114
115 IF p_x_task_rec.PROJECT_TASK_ID = Fnd_Api.G_MISS_NUM THEN
116 p_x_task_rec.PROJECT_TASK_ID := NULL;
117 ELSE
118 p_x_task_rec.PROJECT_TASK_ID := p_x_task_rec.PROJECT_TASK_ID;
119 END IF;
120
121 IF p_x_task_rec.COST_PARENT_ID = Fnd_Api.G_MISS_NUM THEN
122 p_x_task_rec.COST_PARENT_ID := NULL;
123 ELSE
124 p_x_task_rec.COST_PARENT_ID := p_x_task_rec.COST_PARENT_ID;
125 END IF;
126
127 IF p_x_task_rec.MR_ROUTE_ID = Fnd_Api.G_MISS_NUM THEN
128 p_x_task_rec.MR_ROUTE_ID := NULL;
129 ELSE
130 p_x_task_rec.MR_ROUTE_ID := p_x_task_rec.MR_ROUTE_ID;
131 END IF;
132
133 IF p_x_task_rec.MR_ID = Fnd_Api.G_MISS_NUM THEN
134 p_x_task_rec.MR_ID := NULL;
135 ELSE
136 p_x_task_rec.MR_ID := p_x_task_rec.MR_ID;
137 END IF;
138
139 IF p_x_task_rec.UNIT_EFFECTIVITY_ID = Fnd_Api.G_MISS_NUM THEN
140 p_x_task_rec.UNIT_EFFECTIVITY_ID := NULL;
141 ELSE
142 p_x_task_rec.UNIT_EFFECTIVITY_ID := p_x_task_rec.UNIT_EFFECTIVITY_ID;
143 END IF;
144
145 IF p_x_task_rec.START_FROM_HOUR = Fnd_Api.G_MISS_NUM THEN
146 p_x_task_rec.START_FROM_HOUR := NULL;
147 ELSE
148 p_x_task_rec.START_FROM_HOUR := p_x_task_rec.START_FROM_HOUR;
149 END IF;
150
151 IF p_x_task_rec.PRIMARY_VISIT_TASK_ID = Fnd_Api.G_MISS_NUM THEN
152 p_x_task_rec.PRIMARY_VISIT_TASK_ID := NULL;
153 ELSE
154 p_x_task_rec.PRIMARY_VISIT_TASK_ID := p_x_task_rec.PRIMARY_VISIT_TASK_ID;
155 END IF;
156
157 IF p_x_task_rec.ORIGINATING_TASK_ID = Fnd_Api.G_MISS_NUM THEN
158 p_x_task_rec.ORIGINATING_TASK_ID := NULL;
159 ELSE
160 p_x_task_rec.ORIGINATING_TASK_ID := p_x_task_rec.ORIGINATING_TASK_ID;
161 END IF;
162
163 IF p_x_task_rec.SERVICE_REQUEST_ID = Fnd_Api.G_MISS_NUM THEN
164 p_x_task_rec.SERVICE_REQUEST_ID := NULL;
165 ELSE
166 p_x_task_rec.SERVICE_REQUEST_ID := p_x_task_rec.SERVICE_REQUEST_ID;
167 END IF;
168
169 IF p_x_task_rec.attribute_category = Fnd_Api.G_MISS_CHAR THEN
170 p_x_task_rec.attribute_category := NULL;
171 ELSE
172 p_x_task_rec.attribute_category := p_x_task_rec.attribute_category;
173 END IF;
174 --
175 IF p_x_task_rec.attribute1 = Fnd_Api.G_MISS_CHAR THEN
176 p_x_task_rec.attribute1 := NULL;
177 ELSE
178 p_x_task_rec.attribute1 := p_x_task_rec.attribute1;
179 END IF;
180 --
181 IF p_x_task_rec.attribute2 = Fnd_Api.G_MISS_CHAR THEN
182 p_x_task_rec.attribute2 := NULL;
183 ELSE
184 p_x_task_rec.attribute2 := p_x_task_rec.attribute2;
185 END IF;
186 --
187 IF p_x_task_rec.attribute3 = Fnd_Api.G_MISS_CHAR THEN
188 p_x_task_rec.attribute3 := NULL;
189 ELSE
190 p_x_task_rec.attribute3 := p_x_task_rec.attribute3;
191 END IF;
192 --
193 IF p_x_task_rec.attribute4 = Fnd_Api.G_MISS_CHAR THEN
194 p_x_task_rec.attribute4 := NULL;
195 ELSE
196 p_x_task_rec.attribute4 := p_x_task_rec.attribute4;
197 END IF;
198 --
199 IF p_x_task_rec.attribute5 = Fnd_Api.G_MISS_CHAR THEN
200 p_x_task_rec.attribute5 := NULL;
201 ELSE
202 p_x_task_rec.attribute5 := p_x_task_rec.attribute5;
203 END IF;
204 --
205 IF p_x_task_rec.attribute6 = Fnd_Api.G_MISS_CHAR THEN
206 p_x_task_rec.attribute6 := NULL;
207 ELSE
208 p_x_task_rec.attribute6 := p_x_task_rec.attribute6;
209 END IF;
210 --
211 IF p_x_task_rec.attribute7 = Fnd_Api.G_MISS_CHAR THEN
212 p_x_task_rec.attribute7 := NULL;
213 ELSE
214 p_x_task_rec.attribute7 := p_x_task_rec.attribute7;
215 END IF;
216 --
217 IF p_x_task_rec.attribute8 = Fnd_Api.G_MISS_CHAR THEN
218 p_x_task_rec.attribute8 := NULL;
219 ELSE
220 p_x_task_rec.attribute8 := p_x_task_rec.attribute8;
221 END IF;
222 --
223 IF p_x_task_rec.attribute9 = Fnd_Api.G_MISS_CHAR THEN
224 p_x_task_rec.attribute9 := NULL;
225 ELSE
226 p_x_task_rec.attribute9 := p_x_task_rec.attribute9;
227 END IF;
228 --
229 IF p_x_task_rec.attribute10 = Fnd_Api.G_MISS_CHAR THEN
230 p_x_task_rec.attribute10 := NULL;
231 ELSE
232 p_x_task_rec.attribute10 := p_x_task_rec.attribute10;
233 END IF;
234 --
235 IF p_x_task_rec.attribute11 = Fnd_Api.G_MISS_CHAR THEN
236 p_x_task_rec.attribute11 := NULL;
237 ELSE
238 p_x_task_rec.attribute11 := p_x_task_rec.attribute11;
239 END IF;
240 --
241 IF p_x_task_rec.attribute12 = Fnd_Api.G_MISS_CHAR THEN
242 p_x_task_rec.attribute12 := NULL;
243 ELSE
244 p_x_task_rec.attribute12 := p_x_task_rec.attribute12;
245 END IF;
246 --
247 IF p_x_task_rec.attribute13 = Fnd_Api.G_MISS_CHAR THEN
248 p_x_task_rec.attribute13 := NULL;
249 ELSE
250 p_x_task_rec.attribute13 := p_x_task_rec.attribute13;
251 END IF;
252 --
253 IF p_x_task_rec.attribute14 = Fnd_Api.G_MISS_CHAR THEN
254 p_x_task_rec.attribute14 := NULL;
255 ELSE
256 p_x_task_rec.attribute14 := p_x_task_rec.attribute14;
257 END IF;
258 --
259 IF p_x_task_rec.attribute15 = Fnd_Api.G_MISS_CHAR THEN
260 p_x_task_rec.attribute15 := NULL;
261 ELSE
262 p_x_task_rec.attribute15 := p_x_task_rec.attribute15;
263 END IF;
264 --
265 IF p_x_task_rec.description = Fnd_Api.G_MISS_CHAR THEN
266 p_x_task_rec.description := NULL;
267 ELSE
268 p_x_task_rec.description := p_x_task_rec.description;
269 END IF;
270
271 IF p_x_task_rec.STAGE_NAME = Fnd_Api.G_MISS_CHAR THEN
272 p_x_task_rec.STAGE_NAME := NULL;
273 ELSE
274 p_x_task_rec.STAGE_NAME := p_x_task_rec.STAGE_NAME;
275 END IF;
276
277 END Default_Missing_Attribs;
278
279 --------------------------------------------------------------------
280 -- PROCEDURE
281 -- Asso_Inst_Dept_to_Tasks
282 --
283 --------------------------------------------------------------------
284 PROCEDURE Asso_Inst_Dept_to_Tasks
285 (
286 p_module_type IN VARCHAR2,
287 p_x_task_Rec IN OUT NOCOPY AHL_VWP_RULES_PVT.task_rec_type
288 )
289 IS
290 L_API_NAME CONSTANT VARCHAR2(30) := 'Asso_Inst_Dept_to_Tasks';
291 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
292 L_DEBUG CONSTANT VARCHAR2(90) := 'ahl.plsql.'||L_FULL_NAME;
293
294 -- local variables defined for the procedure
295 l_return_status VARCHAR2(1);
296 l_chk_flag VARCHAR2(1);
297 l_msg_data VARCHAR2(2000);
298 l_msg_count NUMBER;
299
300 -- To find visit related information
301 CURSOR c_visit(x_id IN NUMBER) IS
302 SELECT * FROM AHL_VISITS_VL
303 WHERE VISIT_ID = x_id;
304 c_visit_rec c_visit%ROWTYPE;
305
306 BEGIN
307
308 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
309 fnd_log.string(fnd_log.level_procedure,L_DEBUG||'.begin','At the start of PL SQL procedure ');
310 END IF;
311
312 --------------------Value OR ID conversion---------------------------
313 --Start API Body
314 IF p_module_type = 'JSP' THEN
315 p_x_task_Rec.instance_id := NULL;
316 p_x_task_Rec.department_id := NULL;
317 END IF;
318
319 OPEN c_visit(p_x_task_Rec.visit_id);
320 FETCH c_visit INTO c_visit_rec;
321 CLOSE c_visit;
322
323 IF c_visit_rec.organization_id IS NOT NULL THEN
324 -- Get dept code using dept description
325 IF (p_x_task_Rec.dept_name IS NOT NULL AND p_x_task_Rec.dept_name <> Fnd_Api.G_MISS_CHAR ) THEN
326 AHL_VWP_RULES_PVT.Check_Dept_Desc_Or_Id
327 (p_organization_id => c_visit_rec.organization_id,
328 p_dept_name => p_x_task_Rec.dept_name,
329 p_department_id => Null,
330 x_department_id => p_x_task_Rec.department_id,
331 x_return_status => l_return_status,
332 x_error_msg_code => l_msg_data);
333
334 IF NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
335 Fnd_Message.SET_NAME('AHL','AHL_VWP_DEPT_NOT_EXISTS');
336 Fnd_Msg_Pub.ADD;
337 RAISE Fnd_Api.G_EXC_ERROR;
338 END IF;
339
340 -- Changes for Post 11.5.10 by amagrawa
341 AHL_VWP_RULES_PVT.CHECK_DEPARTMENT_SHIFT
342 (P_DEPT_ID => p_x_task_Rec.department_id,
343 X_RETURN_STATUS => l_return_status);
344
345 IF (NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS) THEN
346 Fnd_Message.SET_NAME('AHL','AHL_VWP_NO_DEPT_SHIFT');
347 Fnd_Msg_Pub.ADD;
348 RAISE Fnd_Api.G_EXC_ERROR;
349 END IF;
350 ELSE
351 p_x_task_Rec.dept_name := NULL;
352 -- Post 11.5.10 Changes by Senthil.
353 -- Fixed as per bug # 4073163
354 --p_x_task_Rec.department_id := c_visit_rec.department_id;
355 p_x_task_Rec.department_id := NULL;
356 END IF;
357
358 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
359 fnd_log.string(fnd_log.level_statement,L_DEBUG,' Dept ID= ' || p_x_task_Rec.department_id);
360 END IF;
361 ELSE -- Else of if visit org not exists
362 IF (p_x_task_Rec.dept_name IS NOT NULL AND p_x_task_Rec.dept_name <> Fnd_Api.G_MISS_CHAR ) THEN
363 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
364 fnd_log.string(fnd_log.level_statement,L_DEBUG,' NO ORGANIZATION FOR VISIT');
365 END IF;
366 Fnd_Message.SET_NAME('AHL','AHL_VWP_VISIT_NO_ORG_EXISTS');
367 Fnd_Msg_Pub.ADD;
368 RAISE Fnd_Api.G_EXC_ERROR;
369 END IF;
370 END IF; -- End of if visit org exists
371
372 /*Convert service request number to service request id
373 IF (p_x_task_Rec.service_request_number IS NOT NULL AND p_x_task_Rec.service_request_number <> Fnd_Api.G_MISS_CHAR ) THEN
374 AHL_VWP_RULES_PVT.Check_SR_Request_Number_Or_Id
375 (p_service_id => Null,
376 p_service_number => p_x_task_Rec.service_request_number,
377 x_service_id => p_x_task_Rec.service_request_id,
378 x_return_status => l_return_status,
379 x_error_msg_code => l_msg_data);
380
381 IF NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
382 Fnd_Message.SET_NAME('AHL','AHL_VWP_SERVICE_REQ_NOT_EXISTS');
383 Fnd_Msg_Pub.ADD;
384 RAISE Fnd_Api.g_exc_error;
385 END IF;
386
387 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
388 fnd_log.string(fnd_log.level_procedure,'ahl.plsql.', l_full_name ||': Service ID= ' || p_x_task_Rec.service_request_id);
389 END IF;
390 ELSE
391 p_x_task_Rec.service_request_id := NULL;
392 p_x_task_Rec.service_request_number := NULL;
393 END IF;
394 */
395
396 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
397 fnd_log.string(fnd_log.level_procedure,L_DEBUG||'.end','At the end of PL SQL procedure ');
398 END IF;
399
400 END Asso_Inst_Dept_to_Tasks;
401
402 PROCEDURE Create_Planned_Task (
403 p_api_version IN NUMBER,
404 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
405 p_commit IN VARCHAR2 := Fnd_Api.g_false,
406 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
407 p_module_type IN VARCHAR2 := 'JSP',
408 p_x_task_Rec IN OUT NOCOPY AHL_VWP_RULES_PVT.task_rec_type,
409 x_return_status OUT NOCOPY VARCHAR2,
410 x_msg_count OUT NOCOPY NUMBER,
411 x_msg_data OUT NOCOPY VARCHAR2
412 )
413 IS
414 L_API_VERSION CONSTANT NUMBER := 1.0;
415 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_Planned_Task';
416 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
417 L_DEBUG CONSTANT VARCHAR2(90) := 'ahl.plsql.'||L_FULL_NAME;
418
419 -- local variables defined for the procedure
420 l_msg_data VARCHAR2(2000);
421 l_return_status VARCHAR2(1);
422 l_check_flag VARCHAR2(1);
423
424 l_visit_id NUMBER;
425 l_parent_task_id NUMBER;
426 l_temp_parent_task_id NUMBER;
427 l_service_req_id NUMBER;
428 l_department_id NUMBER;
429 l_unit_effectivity_id NUMBER;
430 l_msg_count NUMBER;
431 l_serial_id NUMBER;
432 l_org_id NUMBER;
433 l_item_id NUMBER;
434 l_MR_route_id NUMBER;
435 l_mr_id NUMBER;
436 l_task_id NUMBER;
437 l_parent_mr_id NUMBER;
438 l_header_id NUMBER;
439 l_unit_id NUMBER;
440 l_parent_unit_id NUMBER;
441 l_visit_number NUMBER;
442 l_object_type VARCHAR2(3);
443 l_count NUMBER;
444 l_workflow_process_id NUMBER;
445 l_incident_id NUMBER;
446
447 -- AnRaj: changed for Fixing Siberian Airlines Bug#5007335
448 l_incident_number CS_INCIDENTS_ALL_B.INCIDENT_NUMBER%TYPE;
449 l_object_version_number CS_INCIDENTS_ALL_B.OBJECT_VERSION_NUMBER%TYPE;
450 l_incident_status_id CS_INCIDENTS_ALL_B.INCIDENT_ID%TYPE;
451 -- End Of Fix Bug#5007335
452
453 l_status_name cs_incident_statuses_tl.name%type;
454 l_interaction_id NUMBER;
455 l_service_request_rec CS_SERVICEREQUEST_PUB.service_request_rec_type;
456 l_contacts_table CS_ServiceRequest_PUB.contacts_table;
457 l_notes_table CS_ServiceRequest_PUB.notes_table;
458
459 i NUMBER:=0;
460 k NUMBER:=0;
461 x NUMBER:=0;
462 y NUMBER:=0;
463 l_dummy varchar2(1);
464
465 -- To find on the basis of input unit effectivity the related information
466 CURSOR c_info(x_mr_header_id IN NUMBER, x_unit_id IN NUMBER) IS
467 SELECT AUEB.CSI_ITEM_INSTANCE_ID
468 FROM AHL_UNIT_EFFECTIVITIES_VL AUEB, CSI_ITEM_INSTANCES CSI
469 WHERE AUEB.CSI_ITEM_INSTANCE_ID = CSI.INSTANCE_ID
470 AND (AUEB.STATUS_CODE IS NULL OR AUEB.STATUS_CODE = 'INIT-DUE')
471 AND AUEB.UNIT_EFFECTIVITY_ID = x_unit_id AND AUEB.MR_HEADER_ID = x_mr_header_id;
472
473 -- To find all Unit Effectvities i.e main root UEId, if any parent UEIds or
474 -- any child UEIds under it which acts as SUMMARY TASK
475 /* For Bug# 3152532 fix by shbhanda dated 02-Dec-03
476 Modified the query to fetch same result Dec 20 2003 sjayacha */
477 CURSOR c_relation (x_ue_id IN NUMBER) IS
478 SELECT AUR.RELATED_UE_ID "UNIT_ID"
479 FROM AHL_UE_RELATIONSHIPS AUR
480 START WITH AUR.UE_ID IN (SELECT AUEB.unit_effectivity_id
481 FROM AHL_UNIT_EFFECTIVITIES_VL AUEB
482 WHERE (AUEB.STATUS_CODE IS NULL OR AUEB.STATUS_CODE = 'INIT-DUE')
483 AND AUEB.unit_effectivity_id = x_ue_id
484 )
485 CONNECT BY PRIOR AUR.RELATED_UE_ID = AUR.UE_ID;
486 c_relation_rec c_relation%ROWTYPE;
487
488 -- Added by sjayacha for Servie Request Integration
489 -- To check whether any child UE exists
490 CURSOR c_check_child_ue(p_ue_id IN NUMBER) IS
491 SELECT 'X'
492 FROM ahl_ue_relationships AUR, ahl_unit_effectivities_vl AUEB
493 WHERE AUR.ue_id = AUEB.unit_effectivity_id
494 AND (AUEB.status_code IS NULL OR AUEB.status_code = 'INIT-DUE')
495 AND AUR.ue_id = p_ue_id;
496
497 -- To find MR Header Id for any related Sub Unit Effectivity Id
498 -- or for main Unit Effectivity Id
499 CURSOR c_header (x_unit_id IN NUMBER) IS
500 /*SELECT MR_HEADER_ID
501 FROM AHL_UNIT_EFFECTIVITIES_VL AUEB
502 WHERE (STATUS_CODE IS NULL OR STATUS_CODE IN ('INIT-DUE', 'DEFERRED'))
503 AND UNIT_EFFECTIVITY_ID = x_unit_id;*/
504 /* For Bug# 3152532 fix by shbhanda dated 02-Dec-03*/
505 SELECT AUEB.MR_HEADER_ID
506 FROM AHL_UNIT_EFFECTIVITIES_VL AUEB, AHL_MR_HEADERS_B AMHB
507 WHERE AUEB.MR_HEADER_ID = AMHB.MR_HEADER_ID
508 AND AMHB.MR_STATUS_CODE = 'COMPLETE'
509 AND AMHB.VERSION_NUMBER IN
510 ( SELECT MAX(VERSION_NUMBER)
511 FROM AHL_MR_HEADERS_B
512 WHERE TITLE = AMHB.TITLE
513 AND TRUNC(SYSDATE)
514 BETWEEN TRUNC(EFFECTIVE_FROM)
515 AND TRUNC(NVL(EFFECTIVE_TO,SYSDATE+1))
516 AND MR_STATUS_CODE = 'COMPLETE'
517 )
518 AND (AUEB.STATUS_CODE IS NULL OR AUEB.STATUS_CODE = 'INIT-DUE')
519 AND AUEB.UNIT_EFFECTIVITY_ID = x_unit_id;
520 c_header_rec c_header%ROWTYPE;
521
522 -- Record type for storing all Maintainence Requirement n Unit Effectivity
523 TYPE MR_Header_Rec_Type IS RECORD
524 (
525 Unit_Effect_ID NUMBER,
526 MR_Header_ID NUMBER
527 );
528
529 -- Table type for storing all Maintainence Requirement n Unit Effectivity
530 TYPE MR_Header_Tbl_Type IS TABLE OF MR_Header_Rec_Type
531 INDEX BY BINARY_INTEGER;
532
533 -- Table type for storing 'MR_Serial_Rec_Type' record datatype
534 MR_Header_Tbl MR_Header_Tbl_Type;
535 MR_Serial_Tbl AHL_VWP_RULES_PVT.MR_Serial_Tbl_Type;
536
537 -- To find visit related information
538 CURSOR c_Visit (p_visit_id IN NUMBER) IS
539 SELECT Any_Task_Chg_Flag, Visit_Id
540 FROM Ahl_Visits_VL
541 WHERE VISIT_ID = p_visit_id;
542 l_visit_csr_rec c_Visit%ROWTYPE;
543
544 -- To find if this Unit has been planned in other visits already
545 CURSOR c_unit (x_unit_id IN NUMBER) IS
546 SELECT VISIT_NUMBER
547 FROM AHL_VISITS_B
548 WHERE VISIT_ID IN ( SELECT DISTINCT VISIT_ID
549 FROM AHL_VISIT_TASKS_B
550 WHERE Unit_Effectivity_Id = x_unit_id
551 )
552 and status_code not in ('CANCELLED','DELETED');
553
554 CURSOR c_unit_object_type(p_unit_id IN NUMBER)
555 IS
556 SELECT OBJECT_TYPE
557 FROM AHL_UNIT_EFFECTIVITIES_VL
558 WHERE UNIT_EFFECTIVITY_ID = p_unit_id;
559
560 -- To find the Item Id, Inv Org Id and Serial Number
561 CURSOR c_item_info(p_unit_id IN NUMBER) IS
562 SELECT AUEB.CSI_ITEM_INSTANCE_ID,
563 AUEB.CS_INCIDENT_ID,
564 CSI.INV_MASTER_ORGANIZATION_ID,
565 CSI.INVENTORY_ITEM_ID
566 FROM AHL_UNIT_EFFECTIVITIES_VL AUEB, CSI_ITEM_INSTANCES CSI
567 WHERE AUEB.CSI_ITEM_INSTANCE_ID = CSI.INSTANCE_ID
568 AND (AUEB.STATUS_CODE IS NULL OR AUEB.STATUS_CODE = 'INIT-DUE')
569 AND AUEB.UNIT_EFFECTIVITY_ID = p_unit_id ;
570
571 CURSOR c_service_details(P_service_id IN NUMBER)
572 IS
573 SELECT INCIDENT_ID,
574 INCIDENT_NUMBER,
575 OBJECT_VERSION_NUMBER
576 FROM CS_INCIDENTS_ALL_B
577 WHERE INCIDENT_ID=P_service_id;
578
579 -- AnRaj:Changed cursor for issues mentioned in bug#5007335
580 CURSOR c_service_status
581 IS
582 select incident_status_id,
583 name
584 from cs_incident_statuses_tl
585 -- where name = 'Planned';
586 where incident_status_id = 52
587 and language = userenv('lang');
588
589 /*NR-MR Changes - sowsubra */
590 CURSOR c_task_for_ue(p_visit_id IN NUMBER, p_ue_id IN NUMBER)
591 IS
592 SELECT visit_task_id
593 FROM ahl_visit_tasks_b
594 WHERE visit_id = p_visit_id
595 AND unit_effectivity_id = p_ue_id
596 AND NVL(status_code,'Y') <> 'DELETED';
597
598 /* Cursor added by rnahata for Bug 6939329 */
599 CURSOR c_task_id_for_ue(c_visit_id IN NUMBER, c_ue_id IN NUMBER) IS
600 SELECT visit_task_id
601 FROM ahl_visit_tasks_b
602 WHERE visit_id = c_visit_id
603 AND unit_effectivity_id = c_ue_id
604 AND NVL(status_code, 'PLANNING') <> 'DELETED'
605 AND TASK_TYPE_CODE = 'SUMMARY';
606
607 BEGIN
608 --------------------- initialize -----------------------
609 SAVEPOINT Create_Planned_Task;
610
611 -- Debug info.
612 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
613 fnd_log.string(fnd_log.level_procedure, L_DEBUG||'.begin','At the start of PLSQL procedure');
614 END IF;
615
616 IF Fnd_Api.to_boolean (p_init_msg_list) THEN
617 Fnd_Msg_Pub.initialize;
618 END IF;
619
620 IF NOT Fnd_Api.compatible_api_call (
621 L_API_VERSION,
622 p_api_version,
623 L_API_NAME,
624 G_PKG_NAME
625 ) THEN
626 RAISE Fnd_Api.g_exc_unexpected_error;
627 END IF;
628
629 x_return_status := Fnd_Api.g_ret_sts_success;
630
631 -- Calling Asso_Inst_Dept_to_Tasks API
632 Asso_Inst_Dept_to_Tasks (
633 p_module_type => p_module_type,
634 p_x_task_Rec => p_x_task_Rec
635 );
636
637 -- Assigning record attributes in local variables
638 l_visit_id := p_x_task_Rec.visit_id;
639 l_service_req_id := p_x_task_Rec.service_request_id;
640 l_department_id := p_x_task_Rec.department_id;
641 l_unit_effectivity_id := p_x_task_Rec.unit_effectivity_id;
642
643 IF l_department_id = FND_API.g_miss_num THEN
644 l_department_id := NULL;
645 END IF;
646
647 -- Cursor to retrieve visit info
648 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
649 fnd_log.string(fnd_log.level_statement, L_DEBUG,'Visit Id: Unit Effe Id ' || l_visit_id || '-' || l_unit_effectivity_id);
650 fnd_log.string(fnd_log.level_statement, L_DEBUG ,'Service Req Id: Department Id:' || l_service_req_id || '-' || l_department_id);
651 END IF;
652
653 IF l_unit_effectivity_id IS NOT NULL THEN
654 OPEN c_unit (l_unit_effectivity_id);
655 FETCH c_unit INTO l_visit_number;
656
657 -- If this UE has already been planned in some other Visit
658 /*NR-MR Changes - sowsubra */
659 /*It is possible to update the SR with more MR's added through backward flow.(Included the
660 condition p_module_type <> 'SR')*/
661 IF c_unit%FOUND AND p_module_type <> 'SR' THEN
662 CLOSE c_unit;
663 -- ERROR MESSAGE
664 x_return_status := Fnd_Api.g_ret_sts_error;
665 Fnd_Message.SET_NAME('AHL','AHL_VWP_UNIT_FOUND');
666 Fnd_Message.SET_TOKEN('VISIT_NUMBER', l_visit_number);
667 Fnd_Msg_Pub.ADD;
668 ELSE -- UE not planned in any other Visit
669 CLOSE c_unit;
670
671 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
672 fnd_log.string(fnd_log.level_statement, L_DEBUG,'Unit effectivity' || l_unit_effectivity_id);
673 END IF;
674
675 -- Get the Object_type code to check whether it is SR or MR.
676 OPEN c_unit_object_type (l_unit_effectivity_id);
677 FETCH c_unit_object_type INTO l_object_type;
678 CLOSE c_unit_object_type;
679
680 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
681 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'Before l_object_type check' );
682 END IF;
683
684 IF l_object_type = 'MR' THEN
685 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
686 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'Before calling create_mr_tasks');
687 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'ue id =>' || l_unit_effectivity_id);
688 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'parent ue id =>' || 'null');
689 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'visit id =>' || l_visit_id);
690 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'department id =>' || l_department_id);
691 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'service_request id =>' || l_service_req_id);
692 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'quantity =>' || p_x_task_Rec.quantity);
693 END IF;
694
695 create_mr_tasks(p_ue_id => l_unit_effectivity_id,
696 p_parent_ue_id => null,
697 p_visit_id => l_visit_id,
698 p_department_id => l_department_id,
699 p_service_request_id => l_service_req_id,
700 -- Added by rnahata for Issue 105 - pass the qty
701 p_quantity => p_x_task_Rec.quantity,
702 p_type => 'MR'
703 );
704
705 -- if object type is SR
706 ELSIF l_object_type = 'SR' THEN
707 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
708 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'l_object_type = SR');
709 END IF;
710 -- Get the details of the UE
711 OPEN c_item_info (l_unit_effectivity_id);
712 FETCH c_item_info INTO l_serial_id,l_service_req_id,l_org_id,l_item_id;
713 CLOSE c_item_info;
714
715 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
716 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'Before calling AHL_VWP_RULES_PVT.Insert_Tasks');
717 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'ue_id =>' || l_unit_effectivity_id);
718 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'parent ue id =>' || 'null');
719 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'visit id =>' || l_visit_id);
720 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'department id =>' || l_department_id);
721 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'service request id =>' || l_service_req_id);
722 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'quantity =>' || p_x_task_Rec.quantity);
723 END IF;
724
725 OPEN c_task_for_ue(l_visit_id, l_unit_effectivity_id);
726 FETCH c_task_for_ue INTO l_parent_task_id;
727 /*NR-MR Changes - sowsubra*/
728 --Call Insert_Tasks only if summary task for the SR has not already been created.
729 IF c_task_for_ue%NOTFOUND THEN
730 AHL_VWP_RULES_PVT.Insert_Tasks
731 (p_visit_id => l_visit_id,
732 p_unit_id => l_unit_effectivity_id,
733 p_serial_id => l_serial_id,
734 p_service_id => l_service_req_id,
735 p_dept_id => l_department_id,
736 p_item_id => l_item_id,
737 p_item_org_id => l_org_id,
738 p_mr_id => NULL,
739 p_mr_route_id => NULL,
740 /* NR-MR Changes - sowsubra - Make the originating workorder as the originating task of NR Summary task*/
741 p_parent_id => p_x_task_Rec.ORIGINATING_TASK_ID,
742 p_flag => 'Y',
743 -- Added by rnahata for Issue 105 - pass the qty for summary task created for the SR
744 p_quantity => p_x_task_Rec.quantity,
745 x_task_id => l_parent_task_id,
746 x_return_status => l_return_status,
747 x_msg_count => l_msg_count,
748 x_msg_data => l_msg_data
749 );
750
751 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
752 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'After AHL_VWP_RULES_PVT.Insert_Tasks for Planned Task');
753 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'l_return_status' || l_return_status);
754 END IF;
755
756 IF NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
757 CLOSE c_task_for_ue; -- NR-MR Changes - sowsubra
758 RAISE Fnd_Api.G_EXC_ERROR;
759 END IF;
760 /*NR-MR Changes - sowsubra*/
761 END IF; --c_task_for_ue%NOTFOUND
762 CLOSE c_task_for_ue;
763
764 -- Check if any valid child UE exist
765 OPEN c_check_child_ue(l_unit_effectivity_id);
766 FETCH c_check_child_ue INTO l_dummy;
767 IF c_check_child_ue%FOUND THEN
768 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
769 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'Before calling create_mr_tasks');
770 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'c_check_child_ue%FOUND is TRUE');
771 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'ue id =>' || l_unit_effectivity_id);
772 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'parent ue id =>' || l_parent_task_id);
773 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'visit id =>' || l_visit_id);
774 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'department id =>' || l_department_id);
775 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'service request id =>' || l_service_req_id);
776 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'quantity =>' || p_x_task_Rec.quantity);
777 END IF;
778 -- The New API which would recursively create tasks for all the MRs which are the children of the SR
779 -- the Task id returned by Insert_Tasks is passed as the parent id here
780 create_mr_tasks(p_ue_id => l_unit_effectivity_id,
781 p_parent_ue_id => l_parent_task_id,
782 p_visit_id => l_visit_id,
783 p_department_id => l_department_id,
784 p_service_request_id => l_service_req_id,
785 -- Added by rnahata for Issue 105 - pass the qty for SR
786 p_quantity => p_x_task_Rec.quantity,
787 p_type => 'SR'
788 );
789
790 ELSE -- No Child UEs
791 -- Create one Summary Task and a Planned Task
792
793 -- NR-MR Changes - sowsubra
794 -- Done to allow creation of a task for an instance that has already been removed.
795 /***
796 IF AHL_VWP_RULES_PVT.instance_in_config_tree(l_visit_id, l_serial_id) = FND_API.G_RET_STS_ERROR THEN
797 Fnd_Message.SET_NAME('AHL','AHL_VWP_TASK_SERIAL');
798 Fnd_Msg_Pub.ADD;
799 RAISE Fnd_Api.G_EXC_ERROR;
800 END IF; ***/
801
802 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
803 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'Inside No Child UEs ELSE BLOCK');
804 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'Before calling AHL_VWP_RULES_PVT.Insert_Tasks for Planned Task');
805 END IF;
806
807 -- Create a Planned Task
808 AHL_VWP_RULES_PVT.Insert_Tasks
809 (p_visit_id => l_visit_id,
810 p_unit_id => l_unit_effectivity_id,
811 p_serial_id => l_serial_id,
812 p_service_id => l_service_req_id,
813 p_dept_id => l_department_id,
814 p_item_id => l_item_id,
815 p_item_org_id => l_org_id,
816 p_mr_id => null,
817 p_mr_route_id => NULL,
818 p_parent_id => l_parent_task_id,
819 p_flag => 'N',
820 /* Added by rnahata for Issue 105 - pass the qty as 0 for
821 the planned task created when there are no MR's associated to the SR*/
822 p_quantity => p_x_task_Rec.quantity,
823 x_task_id => l_task_id,
824 x_return_status => l_return_status,
825 x_msg_count => l_msg_count,
826 x_msg_data => l_msg_data
827 );
828
829 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
830 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'After AHL_VWP_RULES_PVT.Insert_Tasks for Planned Task - l_return_status : '|| l_return_status);
831 END IF;
832
833 IF NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
834 RAISE Fnd_Api.G_EXC_ERROR;
835 END IF;
836 END IF; -- Child UE Check
837
838 -- Call Service Request package to update the status.
839 --CS_SERVICEREQUEST_PUB.Update_ServiceRequest
840 OPEN c_service_details(l_service_req_id);
841 FETCH c_service_details into l_incident_id,l_incident_number,l_object_version_number;
842 CLOSE c_service_details;
843
844 OPEN c_service_status;
845 FETCH c_service_status into l_incident_status_id,l_status_name;
846 CLOSE c_service_status;
847
848 CS_SERVICEREQUEST_PUB.initialize_rec(l_service_request_rec);
849
850 -- Assign the SR rec values
851 l_service_request_rec.status_id := l_incident_status_id;
852 --l_service_request_rec.status_name := l_status_name;
853 /*
854 CS_SERVICEREQUEST_PUB.Update_ServiceRequest(
855 p_api_version => 3.0,
856 p_init_msg_list => FND_API.G_TRUE,
857 p_commit => FND_API.G_FALSE,
858 x_return_status => x_return_status,
859 x_msg_count => l_msg_count,
860 x_msg_data => l_msg_data,
861 p_request_id => l_incident_id,
862 --p_request_number => l_incident_number,
863 p_request_number => NUll,
864 p_audit_comments => Null,
865 p_object_version_number => l_object_version_number,
866 p_resp_appl_id => NULL,
867 p_resp_id => NULL,
868 p_last_updated_by => NULL,
869 p_last_update_login => NULL,
870 p_last_update_date => NULL,
871 p_service_request_rec => l_service_request_rec,
872 p_notes => l_notes_table,
873 p_contacts => l_contacts_table,
874 p_called_by_workflow => NULL,
875 p_workflow_process_id => NULL,
876 x_workflow_process_id => l_workflow_process_id,
877 x_interaction_id => l_interaction_id
878 );
879 */
880
881 -- Check Error Message stack.
882 x_msg_count := FND_MSG_PUB.count_msg;
883 IF x_msg_count > 0 THEN
884 RAISE FND_API.G_EXC_ERROR;
885 END IF;
886
887 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
888 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'Before CS_ServiceRequest_PUB.Update_Status ');
889 END IF;
890 -- anraj changed the api
891 CS_ServiceRequest_PUB.Update_Status
892 (
893 p_api_version => 2.0,
894 p_init_msg_list => FND_API.G_TRUE,
895 p_commit => FND_API.G_FALSE,
896 p_resp_appl_id => NULL,
897 p_resp_id => NULL,
898 p_user_id => NULL,
899 p_login_id => NULL,
900 p_status_id => 52,
901 p_closed_date => NULL,
902 p_audit_comments => NULL,
903 p_called_by_workflow => FND_API.G_FALSE,
904 p_workflow_process_id => NULL,
905 p_comments => NULL,
906 p_public_comment_flag => FND_API.G_FALSE,
907 p_validate_sr_closure => 'N',
908 p_auto_close_child_entities => 'N',
909 p_request_id => l_incident_id,
910 p_request_number => NULL,
911 x_return_status => x_return_status,
912 x_msg_count => l_msg_count,
913 x_msg_data => l_msg_data,
914 p_object_version_number => l_object_version_number,
915 p_status => NULL,
916 x_interaction_id => l_interaction_id
917 );
918
919 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
920 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'After CS_ServiceRequest_PUB.Update_Status - Return Status - '||x_return_status );
921 END IF;
922
923 IF NVL(x_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
924 RAISE Fnd_Api.G_EXC_ERROR;
925 ELSE
926 Fnd_Msg_Pub.initialize;
927 END IF;
928 END IF; -- SR/MR
929 /* Added by rnahata for Bug 6939329 */
930 OPEN c_task_id_for_ue(l_visit_id, l_unit_effectivity_id);
931 FETCH c_task_id_for_ue INTO p_x_task_Rec.visit_task_id;
932 CLOSE c_task_id_for_ue;
933
934 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
935 fnd_log.string(fnd_log.level_statement, L_DEBUG, 'p_x_task_Rec.visit_task_id = ' || p_x_task_Rec.visit_task_id);
936 END IF;
937 /* End changes by rnahata for Bug 6939329 */
938 END IF; -- c_unit%FOUND
939 ELSE -- l_unit_effectivity_id
940 Fnd_Message.SET_NAME('AHL','AHL_VWP_NO_UNIT_EFFECTIVITY');
941 Fnd_Msg_Pub.ADD;
942 RAISE FND_API.G_EXC_ERROR;
943 END IF; -- End of unit effectivity check
944
945 OPEN C_VISIT(l_visit_id);
946 fetch c_visit into l_visit_csr_rec;
947 IF C_VISIT%FOUND THEN
948 IF l_visit_csr_rec.Any_Task_Chg_Flag='N' THEN
949 AHL_VWP_RULES_PVT.update_visit_task_flag(
950 p_visit_id =>l_visit_csr_rec.visit_id,
951 p_flag =>'Y',
952 x_return_status =>x_return_status);
953 END IF;
954
955 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
956 CLOSE C_VISIT;
957 RAISE FND_API.G_EXC_ERROR;
958 END IF;
959 END IF;
960 CLOSE C_VISIT;
961
962 ------------------------- finish -------------------------------
963 -- Standard call to get message count and if count is 1, get message info
964 Fnd_Msg_Pub.count_and_get
965 (
966 p_encoded => Fnd_Api.g_false,
967 p_count => x_msg_count,
968 p_data => x_msg_data
969 );
970
971 -- Check Error Message stack.
972 x_msg_count := FND_MSG_PUB.count_msg;
973 IF x_msg_count > 0 THEN
974 RAISE FND_API.G_EXC_ERROR;
975 END IF;
976
977 -- Standard check of p_commit.
978 IF Fnd_Api.To_Boolean ( p_commit ) THEN
979 COMMIT WORK;
980 END IF;
981
982 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
983 fnd_log.string(fnd_log.level_procedure,L_DEBUG||'.end','At the end of PLSQL procedure');
984 END IF;
985
986 EXCEPTION
987 WHEN Fnd_Api.g_exc_error THEN
988 ROLLBACK TO Create_Planned_Task;
989 x_return_status := Fnd_Api.g_ret_sts_error;
990 Fnd_Msg_Pub.count_and_get(
991 p_encoded => Fnd_Api.g_false,
992 p_count => x_msg_count,
993 p_data => x_msg_data
994 );
995 WHEN Fnd_Api.g_exc_unexpected_error THEN
996 ROLLBACK TO Create_Planned_Task;
997 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
998 Fnd_Msg_Pub.count_and_get (
999 p_encoded => Fnd_Api.g_false,
1000 p_count => x_msg_count,
1001 p_data => x_msg_data
1002 );
1003 WHEN OTHERS THEN
1004 ROLLBACK TO Create_Planned_Task;
1005 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
1006 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_unexp_error)
1007 THEN
1008 Fnd_Msg_Pub.add_exc_msg (G_PKG_NAME, l_api_name);
1009 END IF;
1010 Fnd_Msg_Pub.count_and_get (
1011 p_encoded => Fnd_Api.g_false,
1012 p_count => x_msg_count,
1013 p_data => x_msg_data
1014 );
1015 END Create_Planned_Task;
1016
1017 -------------------------------------------------------------------------
1018 -- PROCEDURE
1019 -- create_mr_tasks
1020 -- AnRaj: Created
1021 -- PURPOSE
1022 -- Seperates the Task creating functionality from Create_Planned_Task
1023 -------------------------------------------------------------------------
1024 PROCEDURE create_mr_tasks(p_ue_id IN NUMBER,
1025 p_parent_ue_id IN NUMBER,
1026 p_visit_id IN NUMBER,
1027 p_department_id IN NUMBER,
1028 p_service_request_id IN NUMBER,
1029 -- Added by rnahata for Issue 105
1030 p_quantity IN NUMBER,
1031 p_type IN VARCHAR2
1032 )
1033 IS
1034 CURSOR c_header (x_unit_id IN NUMBER) IS
1035 SELECT aueb.mr_header_id
1036 FROM ahl_unit_effectivities_vl aueb, ahl_mr_headers_b amhb
1037 WHERE aueb.mr_header_id = amhb.mr_header_id
1038 AND amhb.mr_status_code = 'COMPLETE'
1039 AND amhb.version_number IN
1040 (SELECT MAX(version_number)
1041 FROM ahl_mr_headers_b
1042 WHERE title = amhb.title
1043 AND TRUNC(SYSDATE)
1044 BETWEEN TRUNC(effective_from)
1045 AND TRUNC(NVL(effective_to,SYSDATE+1))
1046 AND mr_status_code = 'COMPLETE'
1047 )
1048 AND (aueb.status_code IS NULL OR aueb.status_code = 'INIT-DUE')
1049 AND aueb.unit_effectivity_id = x_unit_id;
1050 c_header_rec c_header%ROWTYPE;
1051
1052 -- To find on the basis of input unit effectivity the related information
1053 CURSOR c_info(x_mr_header_id IN NUMBER, x_unit_id IN NUMBER) IS
1054 SELECT aueb.csi_item_instance_id
1055 FROM ahl_unit_effectivities_vl aueb, csi_item_instances csi
1056 WHERE aueb.csi_item_instance_id = csi.instance_id
1057 AND (aueb.status_code IS NULL OR aueb.status_code = 'INIT-DUE')
1058 AND aueb.unit_effectivity_id = x_unit_id
1059 AND aueb.mr_header_id = x_mr_header_id;
1060
1061 CURSOR c_relation (x_ue_id IN NUMBER) IS
1062 SELECT aur.related_ue_id
1063 FROM ahl_ue_relationships aur,
1064 ahl_unit_effectivities_vl aueb
1065 WHERE aur.ue_id = x_ue_id
1066 AND aur.ue_id = aueb.unit_effectivity_id
1067 AND (aueb.status_code IS NULL OR aueb.status_code = 'INIT-DUE');
1068 c_relation_rec c_relation%ROWTYPE;
1069
1070 /*NR-MR Changes - sowsubra*/
1071 CURSOR c_task_for_ue(p_visit_id IN NUMBER, p_ue_id IN NUMBER) IS
1072 SELECT visit_task_id
1073 FROM ahl_visit_tasks_b
1074 WHERE visit_id = p_visit_id
1075 AND unit_effectivity_id = p_ue_id
1076 AND NVL(status_code,'Y') <> 'DELETED';
1077 c_task_for_ue_rec c_task_for_ue%ROWTYPE;
1078
1079 -- Begin changes by rnahata for Issue 105
1080 --Cursor to fetch the instance id when effectivity is given
1081 CURSOR c_get_prev_instance_id (p_unit_effectivity IN NUMBER) IS
1082 SELECT csi_item_instance_id FROM AHL_UNIT_EFFECTIVITIES_B
1083 WHERE UNIT_EFFECTIVITY_ID = p_unit_effectivity;
1084
1085 --Cursor to fetch instance quantity
1086 CURSOR c_get_instance_qty(p_unit_effectivity IN NUMBER) IS
1087 SELECT csii.quantity, ue.csi_item_instance_id
1088 FROM csi_item_instances csii, ahl_unit_effectivities_b ue
1089 WHERE ue.unit_effectivity_id = p_unit_effectivity
1090 AND csii.instance_id = ue.csi_item_instance_id;
1091
1092 l_instance_qty NUMBER := 0;
1093 l_instance_id NUMBER := 0;
1094 l_prev_instance_id NUMBER := 0;
1095 -- End changes by rnahata for Issue 105
1096
1097 l_mr_header_id NUMBER;
1098 l_unit_eff_id NUMBER;
1099 l_parent_unit_eff_id NUMBER;
1100 l_serial_id NUMBER;
1101 l_parent_MR_Id NUMBER;
1102 l_department_id NUMBER;
1103 l_return_status VARCHAR2(1);
1104 l_service_request_id NUMBER;
1105
1106 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || 'CREATE_MR_TASKS';
1107 L_DEBUG CONSTANT VARCHAR2(90) := 'ahl.plsql.'||L_FULL_NAME;
1108 MR_Serial_Tbl AHL_VWP_RULES_PVT.MR_Serial_Tbl_Type;
1109
1110 BEGIN
1111 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1112 fnd_log.string(fnd_log.level_procedure,L_DEBUG||'.begin', 'At the start of the procedure..');
1113 fnd_log.string(fnd_log.level_procedure,L_DEBUG, 'p_ue_id' || p_ue_id);
1114 END IF;
1115
1116 l_unit_eff_id := p_ue_id;
1117 l_service_request_id := p_service_request_id;
1118 l_department_id := p_department_id;
1119 l_parent_MR_Id := p_parent_ue_id;
1120
1121 IF p_type = 'MR' THEN
1122 -- Get the MR Header for the UE
1123 OPEN c_header (p_ue_id);
1124 FETCH c_header INTO c_header_rec;
1125 IF c_header%FOUND THEN
1126 CLOSE c_header;
1127 l_mr_header_id := c_header_rec.MR_Header_Id;
1128
1129 OPEN c_info (l_mr_header_id, l_unit_eff_id);
1130 FETCH c_info INTO l_serial_id;
1131 CLOSE c_info;
1132
1133 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1134 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'l_mr_header_id =>' || l_mr_header_id);
1135 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'l_unit_eff_id =>' ||l_unit_eff_id);
1136 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'l_serial_id =>' || l_serial_id);
1137 END IF;
1138
1139 -- NR-MR Changes - sowsubra
1140 -- Done to allow creation of a task for an instance that has already been removed.
1141 /***
1142 IF AHL_VWP_RULES_PVT.instance_in_config_tree(p_visit_id, l_serial_id) = FND_API.G_RET_STS_ERROR THEN
1143 Fnd_Message.SET_NAME('AHL','AHL_VWP_TASK_SERIAL');
1144 Fnd_Msg_Pub.ADD;
1145 RAISE Fnd_Api.G_EXC_ERROR;
1146 END IF;
1147 ***/
1148
1149 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1150 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'Calling ahl_vwp_rules_pvt.create_tasks_for_mr');
1151 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'p_visit_id =>' || p_visit_id);
1152 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'p_serial_id =>' || l_serial_id);
1153 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'p_mr_id =>' || l_mr_header_id);
1154 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'p_department_id =>' || l_department_id);
1155 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'p_service_req_id =>' || l_service_request_id);
1156 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'p_x_parent_MR_Id =>' || l_parent_MR_Id);
1157 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'p_service_req_id =>' || l_service_request_id);
1158 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'p_quantity =>' || p_quantity);
1159 END IF;
1160
1161 ahl_vwp_rules_pvt.create_tasks_for_mr(p_visit_id => p_visit_id,
1162 p_unit_id => l_unit_eff_id,
1163 p_item_id => NULL,
1164 p_org_id => NULL,
1165 p_serial_id => l_serial_id,
1166 p_mr_id => l_mr_header_id,
1167 p_department_id => l_department_id,
1168 p_service_req_id => l_service_request_id,
1169 -- Added by rnahata for Issue 105
1170 p_quantity => p_quantity,
1171 p_x_parent_MR_Id => l_parent_MR_Id,
1172 x_return_status => l_return_status
1173 );
1174
1175 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1176 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'After ahl_vwp_rules_pvt.create_tasks_for_mr');
1177 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'p_x_parent_MR_Id = ' || l_parent_MR_Id );
1178 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'x_return_status is ' || l_return_status );
1179 END IF;
1180
1181 IF NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
1182 RAISE Fnd_Api.G_EXC_ERROR;
1183 END IF;
1184
1185 MR_Serial_Tbl(0).MR_ID := l_mr_header_id ;
1186 MR_Serial_Tbl(0).Serial_ID := l_serial_id;
1187 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1188 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'Calling AHL_VWP_RULES_PVT.Tech_Dependency');
1189 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'MR_Serial_Tbl(0).MR_ID -->'|| MR_Serial_Tbl(0).MR_ID);
1190 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'MR_Serial_Tbl(0).Serial_ID'|| MR_Serial_Tbl(0).Serial_ID);
1191 END IF;
1192
1193 AHL_VWP_RULES_PVT.Tech_Dependency
1194 (p_visit_id => p_visit_id,
1195 p_task_type => 'PLANNED',
1196 p_MR_Serial_Tbl => MR_Serial_Tbl,
1197 x_return_status => l_return_status
1198 );
1199
1200 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1201 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'After AHL_VWP_RULES_PVT.Tech_Dependency - l_return_status : '||l_return_status);
1202 END IF;
1203
1204 IF NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
1205 RAISE Fnd_Api.G_EXC_ERROR;
1206 END IF;
1207 ELSE
1208 CLOSE c_header;
1209 END IF;
1210 END IF;
1211
1212 -- Begin changes by rnahata for Issue 105
1213 -- This part of the code has to be done for both SR as well as MR
1214 --fetches the instance id for the previous MR
1215 OPEN c_get_prev_instance_id (l_unit_eff_id);
1216 FETCH c_get_prev_instance_id INTO l_prev_instance_id;
1217 CLOSE c_get_prev_instance_id;
1218 -- End changes by rnahata for Issue 105
1219
1220 OPEN c_relation (l_unit_eff_id);
1221 LOOP
1222 FETCH c_relation INTO c_relation_rec;
1223 EXIT WHEN c_relation%NOTFOUND;
1224 /*NR-MR Changes - sowsubra*/
1225 --Call create_mr_tasks only if tasks for the MR have not already been created.
1226 OPEN c_task_for_ue(p_visit_id,c_relation_rec.related_ue_id);
1227 FETCH c_task_for_ue INTO c_task_for_ue_rec;
1228 IF c_task_for_ue%NOTFOUND THEN
1229 -- Begin changes by rnahata for Issue 105
1230 -- get the instance qty for the child MR's
1231 OPEN c_get_instance_qty (c_relation_rec.related_ue_id);
1232 FETCH c_get_instance_qty INTO l_instance_qty,l_instance_id;
1233 CLOSE c_get_instance_qty;
1234
1235 IF (l_instance_id = l_prev_instance_id) THEN
1236 l_instance_qty := p_quantity;
1237 END IF;
1238 -- End changes by rnahata for Issue 105
1239
1240 -- Call create_mr_tasks recursively for the next level of UEs
1241 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1242 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'Before calling create_mr_tasks');
1243 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'ue id =>' || c_relation_rec.related_ue_id);
1244 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'parent ue id =>' || l_parent_MR_Id);
1245 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'visit id =>' || p_visit_id);
1246 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'department id =>' || l_department_id);
1247 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'service request id =>' || l_service_request_id);
1248 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'quantity =>' || l_instance_qty);
1249 END IF;
1250
1251 create_mr_tasks(p_ue_id => c_relation_rec.related_ue_id,
1252 p_parent_ue_id => l_parent_MR_Id,
1253 p_visit_id => p_visit_id,
1254 p_department_id => l_department_id,
1255 p_service_request_id => l_service_request_id,
1256 -- Added by rnahata for Issue 105
1257 p_quantity => l_instance_qty,
1258 p_type => 'MR'
1259 );
1260 /*NR-MR Changes - sowsubra*/
1261 END IF; --c_task_for_ue%NOTFOUND
1262 CLOSE c_task_for_ue;
1263 END LOOP;
1264 CLOSE c_relation;
1265
1266 END create_mr_tasks;
1267
1268 --------------------------------------------------------------------
1269 -- PROCEDURE
1270 -- Update_Planned_Task
1271 --
1272 -- PURPOSE
1273 -- To update Planned task for the Maintainance visit.
1274 --------------------------------------------------------------------
1275 PROCEDURE Update_Planned_Task (
1276 p_api_version IN NUMBER,
1277 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
1278 p_commit IN VARCHAR2 := Fnd_Api.g_false,
1279 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
1280 p_module_type IN VARCHAR2 := 'JSP',
1281 p_x_task_rec IN OUT NOCOPY AHL_VWP_RULES_PVT.Task_Rec_Type,
1282 x_return_status OUT NOCOPY VARCHAR2,
1283 x_msg_count OUT NOCOPY NUMBER,
1284 x_msg_data OUT NOCOPY VARCHAR2
1285 )
1286 IS
1287 L_API_VERSION CONSTANT NUMBER := 1.0;
1288 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Planned_Task';
1289 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
1290 L_DEBUG CONSTANT VARCHAR2(90) := 'ahl.plsql.'||L_FULL_NAME;
1291
1292 -- local variables defined for the procedure
1293 l_task_rec AHL_VWP_RULES_PVT.Task_Rec_Type := p_x_task_rec;
1294 l_return_status VARCHAR2(1);
1295 l_msg_data VARCHAR2(2000);
1296 l_planned_order_flag VARCHAR2(1);
1297 l_msg_count NUMBER;
1298 l_cost_parent_id NUMBER;
1299 l_department_id NUMBER;
1300
1301 -- To find visit related information
1302 CURSOR c_Visit (x_id IN NUMBER) IS
1303 SELECT * FROM Ahl_Visits_VL
1304 WHERE VISIT_ID = x_id;
1305 c_Visit_rec c_Visit%ROWTYPE;
1306
1307 -- To find task related information
1308 CURSOR c_Task (x_id IN NUMBER) IS
1309 SELECT * FROM Ahl_Visit_Tasks_VL
1310 WHERE VISIT_TASK_ID = x_id;
1311 c_Task_rec c_Task%ROWTYPE;
1312 c_upd_Task_rec c_Task%ROWTYPE;
1313
1314 BEGIN
1315 --------------------- initialize -----------------------
1316 SAVEPOINT Update_Planned_Task;
1317
1318 -- Debug info.
1319 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1320 fnd_log.string(fnd_log.level_procedure,L_DEBUG||'.begin','At the start of PLSQL procedure');
1321 END IF;
1322
1323 IF Fnd_Api.to_boolean (p_init_msg_list) THEN
1324 Fnd_Msg_Pub.initialize;
1325 END IF;
1326
1327 -- Initialize API return status to success
1328 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1329
1330 -- Standard call to check for call compatibility.
1331 IF NOT Fnd_Api.compatible_api_call(
1332 l_api_version,
1333 p_api_version,
1334 l_api_name,
1335 G_PKG_NAME
1336 ) THEN
1337 RAISE Fnd_Api.g_exc_unexpected_error;
1338 END IF;
1339
1340 ------------------------Start of API Body------------------------------------
1341
1342 OPEN c_Visit(l_Task_rec.visit_id);
1343 FETCH c_Visit INTO c_Visit_rec;
1344 CLOSE c_Visit;
1345
1346 OPEN c_Task(l_Task_rec.visit_task_id);
1347 FETCH c_Task INTO c_Task_rec;
1348 CLOSE c_Task;
1349
1350 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1351 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'Visit Id/Task Id = ' || l_task_rec.visit_id || '-' || l_task_rec.visit_task_id);
1352 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Inventory Id /org/name =' || l_task_rec.inventory_item_id || '-' || l_task_rec.item_organization_id || '-' || l_task_rec.item_name);
1353 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Cost Id -- Number=' || l_task_rec.cost_parent_id || '**' || l_task_rec.cost_parent_number );
1354 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Originating Id/Number=' || l_task_rec.originating_task_id || '**' || l_task_rec.orginating_task_number);
1355 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Object version number = ' || l_task_rec.object_version_number);
1356 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Duration from record = ' || l_task_rec.duration);
1357 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Visit start from hour/duration=' || '-' || l_task_rec.start_from_hour || '-' || l_task_rec.duration);
1358 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Task Type code/value=' || l_task_rec.task_type_code || '-' || l_task_rec.task_type_value );
1359 fnd_log.string(fnd_log.level_statement,L_DEBUG,'department_id = ' || l_task_rec.department_id );
1360 END IF;
1361
1362 ----------- Start defining and validate all LOVs on Create Visit's Task UI Screen---
1363 --
1364 -- For DEPARTMENT
1365 -- Convert department name to department id
1366 IF (l_task_rec.dept_name IS NOT NULL AND l_task_rec.dept_name <> Fnd_Api.G_MISS_CHAR ) THEN
1367
1368 AHL_VWP_RULES_PVT.Check_Dept_Desc_Or_Id
1369 (p_organization_id => c_visit_rec.organization_id,
1370 p_dept_name => l_task_rec.dept_name,
1371 p_department_id => NULL,
1372 x_department_id => l_department_id,
1373 x_return_status => l_return_status,
1374 x_error_msg_code => l_msg_data);
1375
1376 IF NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS
1377 THEN
1378 Fnd_Message.SET_NAME('AHL','AHL_VWP_DEPT_NOT_EXISTS');
1379 Fnd_Msg_Pub.ADD;
1380 RAISE Fnd_Api.G_EXC_ERROR;
1381 END IF;
1382
1383 --Assign the returned value
1384 l_task_rec.department_id := l_department_id;
1385 END IF;
1386
1387 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1388 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Dept ID= ' || l_Task_rec.department_id );
1389 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Cost parent= ' || l_Task_rec.cost_parent_number);
1390 END IF;
1391
1392 --
1393 -- For COST PARENT TASK
1394 -- Convert cost parent number to id
1395 IF (l_Task_rec.cost_parent_number IS NOT NULL AND
1396 l_Task_rec.cost_parent_number <> Fnd_Api.G_MISS_NUM ) THEN
1397
1398 AHL_VWP_RULES_PVT.Check_Visit_Task_Number_OR_ID
1399 (p_visit_task_id => l_Task_rec.cost_parent_id,
1400 p_visit_task_number => l_Task_rec.cost_parent_number,
1401 p_visit_id => l_Task_rec.visit_id,
1402 x_visit_task_id => l_cost_parent_id,
1403 x_return_status => l_return_status,
1404 x_error_msg_code => l_msg_data);
1405
1406 IF NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS
1407 THEN
1408 Fnd_Message.SET_NAME('AHL','AHL_VWP_PARENT_NOT_EXISTS');
1409 Fnd_Msg_Pub.ADD;
1410 RAISE Fnd_Api.g_exc_error;
1411 END IF;
1412
1413 --Assign the returned value
1414 l_Task_rec.cost_parent_id := l_cost_parent_id;
1415 END IF;
1416
1417 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1418 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Cost parent ID = ' || l_Task_rec.cost_parent_id);
1419 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Validation: Start -- For COST PARENT ');
1420 END IF;
1421
1422 -- To Check for cost parent task id not forming loop
1423 IF (l_Task_rec.cost_parent_id IS NOT NULL AND
1424 l_Task_rec.cost_parent_id <> Fnd_Api.G_MISS_NUM ) THEN
1425
1426 AHL_VWP_RULES_PVT.Check_Cost_Parent_Loop
1427 (p_visit_id => l_Task_rec.visit_id,
1428 p_visit_task_id => l_Task_rec.visit_task_id ,
1429 p_cost_parent_id => l_Task_rec.cost_parent_id
1430 );
1431
1432 END IF;
1433
1434 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1435 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Validation: End -- For COST PARENT ');
1436 END IF;
1437
1438 ----------- End defining and validate all LOVs on Create Visit's Task UI Screen---
1439
1440 ----------------------- validate ----------------------
1441
1442 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1443 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Validate');
1444 END IF;
1445
1446 -- For all optional fields check if its g_miss_num/g_miss_char/g_miss_date
1447 -- then Null else the value call Default_Missing_Attribs procedure
1448 Default_Missing_Attribs
1449 (
1450 p_x_task_rec => l_Task_rec
1451 );
1452
1453 IF p_validation_level >= Jtf_Plsql_Api.g_valid_level_item THEN
1454 Check_Task_Items (
1455 p_task_rec => p_x_task_rec,
1456 p_validation_mode => Jtf_Plsql_Api.g_update,
1457 x_return_status => l_return_status
1458 );
1459
1460 IF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
1461 RAISE Fnd_Api.g_exc_unexpected_error;
1462 ELSIF l_return_status = Fnd_Api.g_ret_sts_error THEN
1463 RAISE Fnd_Api.g_exc_error;
1464 END IF;
1465 END IF;
1466
1467 -- Check Object version number.
1468 IF (c_task_rec.object_version_number <> l_task_rec.object_version_number) THEN
1469 Fnd_Message.Set_Name('AHL','AHL_COM_RECORD_CHANGED');
1470 Fnd_Msg_Pub.ADD;
1471 RAISE Fnd_Api.g_exc_error;
1472 END IF;
1473
1474 -- Post 11.5.10 Changes by Senthil.
1475 IF(L_task_rec.STAGE_ID IS NOT NULL OR L_task_rec.STAGE_NAME IS NOT NULL) THEN
1476
1477 AHL_VWP_VISITS_STAGES_PVT.VALIDATE_STAGE_UPDATES(
1478 P_API_VERSION => 1.0,
1479 P_VISIT_ID => l_Task_rec.visit_id,
1480 P_VISIT_TASK_ID => l_Task_rec.visit_task_id,
1481 P_STAGE_NAME => L_task_rec.STAGE_NAME,
1482 X_STAGE_ID => L_task_rec.STAGE_ID,
1483 X_RETURN_STATUS => l_return_status,
1484 X_MSG_COUNT => l_msg_count,
1485 X_MSG_DATA => l_msg_data );
1486
1487 END IF;
1488
1489 -- Standard check to count messages
1490 l_msg_count := Fnd_Msg_Pub.count_msg;
1491
1492 IF l_msg_count > 0 OR NVL(l_return_status,'X') <> Fnd_Api.g_ret_sts_success THEN
1493 X_msg_count := l_msg_count;
1494 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1495 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1496 END IF;
1497
1498 -------------------------- update --------------------
1499 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1500 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Update');
1501 END IF;
1502
1503 --Modified by mpothuku to fix LTP forum issue #208 on 04/19/05
1504 IF( nvl(p_module_type,'XXX') <> 'LTP') THEN
1505 l_task_rec.originating_task_id := c_task_rec.originating_task_id;
1506 END IF;
1507 --End mpothuku
1508
1509 Ahl_Visit_Tasks_Pkg.UPDATE_ROW (
1510 X_VISIT_TASK_ID => l_task_rec.visit_task_id,
1511 X_VISIT_TASK_NUMBER => c_task_rec.visit_task_number,
1512 X_OBJECT_VERSION_NUMBER => l_task_rec.OBJECT_VERSION_NUMBER + 1,
1513 X_VISIT_ID => l_task_rec.visit_id,
1514 X_PROJECT_TASK_ID => c_task_rec.project_task_id,
1515 X_COST_PARENT_ID => l_task_rec.cost_parent_id,
1516 X_MR_ROUTE_ID => c_task_rec.mr_route_id,
1517 X_MR_ID => c_task_rec.mr_id,
1518 X_DURATION => c_task_rec.duration,
1519 X_UNIT_EFFECTIVITY_ID => c_task_rec.unit_effectivity_id,
1520 X_START_FROM_HOUR => l_task_rec.start_from_hour,
1521 X_INVENTORY_ITEM_ID => c_task_rec.inventory_item_id,
1522 X_ITEM_ORGANIZATION_ID => c_task_rec.item_organization_id,
1523 X_INSTANCE_ID => c_Task_rec.instance_id,
1524 X_PRIMARY_VISIT_TASK_ID => c_task_rec.primary_visit_task_id,
1525 X_ORIGINATING_TASK_ID => l_task_rec.originating_task_id, --c_task_rec.originating_task_id,
1526 X_SERVICE_REQUEST_ID => c_task_rec.service_request_id,
1527 X_TASK_TYPE_CODE => l_task_rec.TASK_TYPE_CODE,
1528 X_DEPARTMENT_ID => l_task_rec.DEPARTMENT_ID,
1529 X_SUMMARY_TASK_FLAG => 'N',
1530 X_PRICE_LIST_ID => c_task_rec.price_list_id,
1531 X_STATUS_CODE => c_task_rec.status_code,
1532 X_ESTIMATED_PRICE => c_task_rec.estimated_price,
1533 X_ACTUAL_PRICE => c_task_rec.actual_price,
1534 X_ACTUAL_COST => c_task_rec.actual_cost,
1535 -- Changes for 11.5.10 by Senthil.
1536 X_STAGE_ID => l_task_rec.STAGE_ID,
1537 -- Added cxcheng POST11510--------------
1538 X_START_DATE_TIME => NULL,
1539 X_END_DATE_TIME => NULL,
1540 X_ATTRIBUTE_CATEGORY => c_task_rec.ATTRIBUTE_CATEGORY,
1541 X_ATTRIBUTE1 => c_task_rec.ATTRIBUTE1,
1542 X_ATTRIBUTE2 => c_task_rec.ATTRIBUTE2,
1543 X_ATTRIBUTE3 => c_task_rec.ATTRIBUTE3,
1544 X_ATTRIBUTE4 => c_task_rec.ATTRIBUTE4,
1545 X_ATTRIBUTE5 => c_task_rec.ATTRIBUTE5,
1546 X_ATTRIBUTE6 => c_task_rec.ATTRIBUTE6,
1547 X_ATTRIBUTE7 => c_task_rec.ATTRIBUTE7,
1548 X_ATTRIBUTE8 => c_task_rec.ATTRIBUTE8,
1549 X_ATTRIBUTE9 => c_task_rec.ATTRIBUTE9,
1550 X_ATTRIBUTE10 => c_task_rec.ATTRIBUTE10,
1551 X_ATTRIBUTE11 => c_task_rec.ATTRIBUTE11,
1552 X_ATTRIBUTE12 => c_task_rec.ATTRIBUTE12,
1553 X_ATTRIBUTE13 => c_task_rec.ATTRIBUTE13,
1554 X_ATTRIBUTE14 => c_task_rec.ATTRIBUTE14,
1555 X_ATTRIBUTE15 => c_task_rec.ATTRIBUTE15,
1556 X_VISIT_TASK_NAME => l_task_rec.visit_task_name,
1557 X_DESCRIPTION => l_task_rec.description,
1558 X_QUANTITY => c_task_rec.QUANTITY, -- Added by rnahata for Issue 105
1559 X_LAST_UPDATE_DATE => SYSDATE,
1560 X_LAST_UPDATED_BY => Fnd_Global.USER_ID,
1561 X_LAST_UPDATE_LOGIN => Fnd_Global.LOGIN_ID );
1562
1563 -- Added cxcheng POST11510--------------
1564 --Now adjust the times derivation for task
1565 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1566 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Before calling AHL_VWP_TIMES_PVT.Adjust_Task_Times');
1567 END IF;
1568
1569 AHL_VWP_TIMES_PVT.Adjust_Task_Times(p_api_version => 1.0,
1570 p_init_msg_list => Fnd_Api.G_FALSE,
1571 p_commit => Fnd_Api.G_FALSE,
1572 p_validation_level => Fnd_Api.G_VALID_LEVEL_FULL,
1573 x_return_status => l_return_status,
1574 x_msg_count => l_msg_count,
1575 x_msg_data => l_msg_data,
1576 p_task_id => l_task_rec.visit_task_id);
1577
1578 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1579 fnd_log.string(fnd_log.level_statement,L_DEBUG,'After calling AHL_VWP_TIMES_PVT.Adjust_Task_Times - l_return_status : '||l_return_status);
1580 END IF;
1581
1582 --Standard check to count messages
1583 l_msg_count := Fnd_Msg_Pub.count_msg;
1584
1585 IF l_msg_count > 0 OR NVL(l_return_status,'X') <> Fnd_Api.g_ret_sts_success THEN
1586 x_msg_count := l_msg_count;
1587 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1588 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1589 END IF;
1590
1591 -- Assign back to in/out parameter
1592 p_x_task_rec := l_task_rec;
1593
1594 -- To Update visit attribute any_task_chg_flag for costing purpose
1595 -- Looking for changes in 'Start from hour' attributes of task
1596
1597 IF NVL(l_task_rec.Start_from_hour,-30) <> NVL(c_task_rec.Start_from_hour,-30) OR
1598 NVL(l_task_rec.STAGE_ID,-30) <> NVL(c_task_rec.STAGE_ID,-30) OR
1599 NVL(l_task_rec.department_id,-30) <> NVL(c_task_rec.department_id,-30) THEN
1600 OPEN c_Task(l_Task_rec.visit_task_id);
1601 FETCH c_Task INTO c_upd_Task_rec;
1602 CLOSE c_Task;
1603
1604 IF c_upd_Task_rec.start_date_time IS NOT NULL THEN
1605 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1606 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Before calling AHL_LTP_REQST_MATRL_PVT.Process_Planned_Materials');
1607 END IF;
1608
1609 AHL_LTP_REQST_MATRL_PVT.Process_Planned_Materials (
1610 p_api_version => 1.0,
1611 p_init_msg_list => FND_API.g_false,
1612 p_commit => FND_API.g_false,
1613 p_validation_level => FND_API.g_valid_level_full,
1614 p_visit_id => l_task_rec.visit_id,
1615 p_visit_task_id => NULL,
1616 p_org_id => NULL,
1617 p_start_date => NULL,
1618 p_operation_flag => 'U',
1619 x_planned_order_flag => l_planned_order_flag ,
1620 x_return_status => l_return_status,
1621 x_msg_count => l_msg_count,
1622 x_msg_data => l_msg_data );
1623
1624 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1625 fnd_log.string(fnd_log.level_statement,L_DEBUG,'After AHL_LTP_REQST_MATRL_PVT.Process_Planned_Materials - l_return_status : '||l_return_status);
1626 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Planned Order Flag : ' || l_planned_order_flag);
1627 END IF;
1628
1629 IF l_return_status <> 'S' THEN
1630 RAISE Fnd_Api.G_EXC_ERROR;
1631 END IF;
1632 END IF;
1633
1634 IF c_visit_rec.any_task_chg_flag = 'N' THEN
1635 AHL_VWP_RULES_PVT.Update_Visit_Task_Flag
1636 (p_visit_id => l_task_rec.visit_id,
1637 p_flag => 'Y',
1638 x_return_status => x_return_status);
1639 END IF;
1640 END IF;
1641
1642 --Standard check to count messages
1643 l_msg_count := Fnd_Msg_Pub.count_msg;
1644
1645 IF l_msg_count > 0 THEN
1646 X_msg_count := l_msg_count;
1647 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1648 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1649 END IF;
1650
1651 --Standard check for commit
1652 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
1653 COMMIT;
1654 END IF;
1655
1656 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1657 fnd_log.string(fnd_log.level_procedure,L_DEBUG ||'.end','At the end of PLSQL procedure');
1658 END IF;
1659
1660 EXCEPTION
1661 WHEN Fnd_Api.g_exc_error THEN
1662 ROLLBACK TO Update_Planned_Task;
1663 x_return_status := Fnd_Api.g_ret_sts_error;
1664 Fnd_Msg_Pub.count_and_get (
1665 p_encoded => Fnd_Api.g_false,
1666 p_count => x_msg_count,
1667 p_data => x_msg_data
1668 );
1669 WHEN Fnd_Api.g_exc_unexpected_error THEN
1670 ROLLBACK TO Update_Planned_Task;
1671 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
1672 Fnd_Msg_Pub.count_and_get (
1673 p_encoded => Fnd_Api.g_false,
1674 p_count => x_msg_count,
1675 p_data => x_msg_data
1676 );
1677 WHEN OTHERS THEN
1678 ROLLBACK TO Update_Planned_Task;
1679 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
1680 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_unexp_error) THEN
1681 Fnd_Msg_Pub.add_exc_msg (G_PKG_NAME, l_api_name);
1682 END IF;
1683 Fnd_Msg_Pub.count_and_get (
1684 p_encoded => Fnd_Api.g_false,
1685 p_count => x_msg_count,
1686 p_data => x_msg_data
1687 );
1688 END Update_Planned_Task;
1689
1690 --------------------------------------------------------------------
1691 -- PROCEDURE
1692 -- Delete_Planned_Task
1693 --
1694 -- PURPOSE
1695 -- To delete Planned tasks for the Maintenace visit.
1696 --------------------------------------------------------------------
1697 PROCEDURE Delete_Planned_Task (
1698 p_api_version IN NUMBER,
1699 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
1700 p_commit IN VARCHAR2 := Fnd_Api.g_false,
1701 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
1702 p_module_type IN VARCHAR2 := 'JSP',
1703 p_visit_task_ID IN NUMBER,
1704
1705 x_return_status OUT NOCOPY VARCHAR2,
1706 x_msg_count OUT NOCOPY NUMBER,
1707 x_msg_data OUT NOCOPY VARCHAR2
1708 )
1709
1710 IS
1711
1712 l_api_version CONSTANT NUMBER := 1.0;
1713 l_api_name CONSTANT VARCHAR2(30) := 'Delete Planned Task';
1714 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_Api_name;
1715 l_debug CONSTANT VARCHAR2(90) := 'ahl.plsql.'||L_FULL_NAME;
1716 -- local variables defined for the procedure
1717 l_origin_id NUMBER;
1718
1719 -- To find all tasks related information
1720 CURSOR c_Task (x_id IN NUMBER) IS
1721 SELECT * FROM Ahl_Visit_Tasks_VL
1722 WHERE Visit_Task_ID = x_id;
1723 c_task_rec c_Task%ROWTYPE;
1724 BEGIN
1725 --------------------- initialize -----------------------
1726 SAVEPOINT Delete_Planned_Task;
1727 -- Check if API is called in debug mode. If yes, enable debug.
1728 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1729 fnd_log.string(fnd_log.level_procedure,l_debug||'.begin','At the start of PLSQL procedure');
1730 END IF;
1731
1732 IF Fnd_Api.to_boolean (p_init_msg_list) THEN
1733 Fnd_Msg_Pub.initialize;
1734 END IF;
1735
1736 -- Initialize API return status to success
1737 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1738
1739 -- Standard call to check for call compatibility.
1740 IF NOT Fnd_Api.compatible_api_call(
1741 l_api_version,
1742 p_api_version,
1743 l_api_name,
1744 G_PKG_NAME
1745 ) THEN
1746 RAISE Fnd_Api.g_exc_unexpected_error;
1747 END IF;
1748
1749 ------------------------Start of API Body------------------------------------
1750 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1751 fnd_log.string(fnd_log.level_statement,l_debug,'Task Id' || p_visit_task_ID);
1752 END IF;
1753
1754 -- To check if the input taskid exists in task entity.
1755 OPEN c_Task(p_Visit_Task_ID);
1756 FETCH c_Task INTO c_task_rec;
1757
1758 IF c_Task%NOTFOUND THEN
1759 CLOSE c_Task;
1760 Fnd_Message.set_name('AHL', 'AHL_VWP_TASK_ID_INVALID');
1761 FND_MESSAGE.SET_TOKEN('TASK_ID',p_visit_task_id,false);
1762 Fnd_Msg_Pub.ADD;
1763 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1764 fnd_log.string(fnd_log.level_statement,l_debug,'Invalid Task Id' || p_visit_task_ID);
1765 END IF;
1766 RAISE Fnd_Api.g_exc_error;
1767 ELSE
1768 CLOSE c_Task;
1769 -- To find the visit related information
1770 l_origin_id:= c_task_rec.originating_task_id;
1771
1772 If l_origin_id is Not Null then
1773 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1774 fnd_log.string(fnd_log.level_statement,l_debug,'Before calling AHL_VWP_TASKS_PVT.Delete_Summary_Task');
1775 END IF;
1776
1777 AHL_VWP_TASKS_PVT.Delete_Summary_Task(
1778 p_api_version => p_api_version,
1779 p_init_msg_list => Fnd_Api.g_false,
1780 p_commit => Fnd_Api.g_false,
1781 p_validation_level => Fnd_Api.g_valid_level_full,
1782 p_module_type => NULL,
1783 p_Visit_Task_Id => l_origin_id,
1784 x_return_status => x_return_status,
1785 x_msg_count => x_msg_count,
1786 x_msg_data => x_msg_data);
1787
1788 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1789 fnd_log.string(fnd_log.level_statement,l_debug,'After calling AHL_VWP_TASKS_PVT.Delete_Summary_Task : x_return_status - '||x_return_status);
1790 END IF;
1791
1792 IF x_return_status <> Fnd_Api.g_ret_sts_success THEN
1793 RAISE Fnd_Api.G_EXC_ERROR;
1794 END IF;
1795 ELSE
1796 Fnd_Message.SET_NAME('AHL','AHL_VWP_PLANNEDTASKMR');
1797 Fnd_Msg_Pub.ADD;
1798 RAISE Fnd_Api.G_EXC_ERROR;
1799 End IF;
1800 END IF;
1801 ------------------------End of API Body------------------------------------
1802 IF Fnd_Api.to_boolean (p_commit) THEN
1803 COMMIT;
1804 END IF;
1805
1806 Fnd_Msg_Pub.count_and_get (
1807 p_encoded => Fnd_Api.g_false,
1808 p_count => x_msg_count,
1809 p_data => x_msg_data
1810 );
1811
1812 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1813 fnd_log.string(fnd_log.level_procedure,l_debug ||'.end','At the end of PLSQL procedure');
1814 END IF;
1815 EXCEPTION
1816 WHEN Fnd_Api.g_exc_error THEN
1817 ROLLBACK TO Delete_Planned_Task;
1818 x_return_status := Fnd_Api.g_ret_sts_error;
1819 Fnd_Msg_Pub.count_and_get (
1820 p_encoded => Fnd_Api.g_false,
1821 p_count => x_msg_count,
1822 p_data => x_msg_data
1823 );
1824
1825 WHEN Fnd_Api.g_exc_unexpected_error THEN
1826 ROLLBACK TO Delete_Planned_Task;
1827 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
1828 Fnd_Msg_Pub.count_and_get (
1829 p_encoded => Fnd_Api.g_false,
1830 p_count => x_msg_count,
1831 p_data => x_msg_data
1832 );
1833
1834 WHEN OTHERS THEN
1835 ROLLBACK TO Delete_Planned_Task;
1836 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
1837 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_unexp_error)
1838 THEN
1839 Fnd_Msg_Pub.add_exc_msg (G_PKG_NAME, l_api_name);
1840 END IF;
1841 Fnd_Msg_Pub.count_and_get (
1842 p_encoded => Fnd_Api.g_false,
1843 p_count => x_msg_count,
1844 p_data => x_msg_data
1845 );
1846
1847 END Delete_Planned_Task;
1848
1849 ---------------------------------------------------------------------
1850 -- PROCEDURE
1851 -- Check_Task_Items
1852 --
1853 -- PURPOSE
1854 --
1855 ---------------------------------------------------------------------
1856 PROCEDURE Check_Task_Items (
1857 p_task_rec IN AHL_VWP_RULES_PVT.Task_Rec_Type,
1858 p_validation_mode IN VARCHAR2 := Jtf_Plsql_Api.g_create,
1859
1860 x_return_status OUT NOCOPY VARCHAR2
1861 )
1862 IS
1863 l_api_name CONSTANT VARCHAR2(30) := 'Check_Task_Items';
1864 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_Api_name;
1865 l_debug CONSTANT VARCHAR2(90) := 'ahl.plsql.'||L_FULL_NAME;
1866
1867 BEGIN
1868 --
1869 -- Validate required items.
1870 x_return_status := FND_API.G_RET_STS_SUCCESS;
1871
1872 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1873 fnd_log.string(fnd_log.level_procedure,l_debug||'.begin', 'At the start of the procedure..');
1874 fnd_log.string(fnd_log.level_procedure,l_debug, 'Before Check_Visit_Task_Req_Items');
1875 END IF;
1876
1877 Check_Visit_Task_Req_Items (
1878 p_task_rec => p_task_rec,
1879 x_return_status => x_return_status
1880 );
1881 IF x_return_status <> Fnd_Api.g_ret_sts_success THEN
1882 RETURN;
1883 END IF;
1884
1885 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1886 fnd_log.string(fnd_log.level_statement,l_debug,'After Check_Visit_Task_Req_Items');
1887 END IF;
1888
1889 -- Validate uniqueness.
1890 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1891 fnd_log.string(fnd_log.level_statement,l_debug,'Before Check_Visit_Task_UK_Items');
1892 END IF;
1893
1894 Check_Visit_Task_UK_Items (
1895 p_task_rec => p_task_rec,
1896 p_validation_mode => p_validation_mode,
1897 x_return_status => x_return_status
1898 );
1899 IF x_return_status <> Fnd_Api.g_ret_sts_success THEN
1900 RETURN;
1901 END IF;
1902
1903 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1904 fnd_log.string(fnd_log.level_procedure,l_debug,'After Check_Visit_Task_UK_Items..');
1905 fnd_log.string(fnd_log.level_procedure,l_debug||'.end','At the end of the procedure');
1906 END IF;
1907 END Check_Task_Items;
1908
1909 ---------------------------------------------------------------------
1910 -- PROCEDURE
1911 -- Complete_Visit_Task_Rec
1912 --
1913 -- PURPOSE
1914 --
1915 ---------------------------------------------------------------------
1916 /* It doesn't seem to be used anywhere
1917 PROCEDURE Complete_Visit_Task_Rec (
1918 p_task_rec IN AHL_VWP_RULES_PVT.Task_Rec_Type,
1919 x_complete_rec OUT NOCOPY AHL_VWP_RULES_PVT.Task_Rec_Type
1920 )
1921 IS
1922 CURSOR c_Visit_Task IS
1923 SELECT *
1924 FROM Ahl_Visit_Tasks_vl
1925 WHERE Visit_Task_ID = p_task_rec.Visit_Task_ID;
1926 --
1927 -- This is the only exception for using %ROWTYPE.
1928 -- We are selecting from the VL view, which may
1929 -- have some denormalized columns as compared to
1930 -- the base tables.
1931 l_task_rec c_Visit_Task%ROWTYPE;
1932 BEGIN
1933 x_complete_rec := p_task_rec;
1934 OPEN c_Visit_Task;
1935 FETCH c_Visit_Task INTO l_task_rec;
1936 IF c_Visit_Task%NOTFOUND THEN
1937 CLOSE c_Visit_Task;
1938 Fnd_Message.set_name('AHL', 'AHL_API_RECORD_NOT_FOUND');
1939 Fnd_Msg_Pub.ADD;
1940 RAISE Fnd_Api.g_exc_error;
1941 END IF;
1942 CLOSE c_Visit_Task;
1943
1944 END Complete_Visit_Task_Rec;
1945 */
1946
1947 ---------------------------------------------------------------------
1948 -- PROCEDURE
1949 -- Check_Visit_Task_Req_Items
1950 --
1951 -- PURPOSE
1952 --
1953 ---------------------------------------------------------------------
1954 PROCEDURE Check_Visit_Task_Req_Items (
1955 p_task_rec IN AHL_VWP_RULES_PVT.Task_Rec_Type,
1956 x_return_status OUT NOCOPY VARCHAR2
1957 )
1958 IS
1959 l_api_name CONSTANT VARCHAR2(30) := 'Check_Visit_Task_Req_Items';
1960 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_Api_name;
1961 l_debug CONSTANT VARCHAR2(90) := 'ahl.plsql.'||L_FULL_NAME;
1962
1963 BEGIN
1964
1965 x_return_status := FND_API.G_RET_STS_SUCCESS;
1966
1967 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1968 fnd_log.string(fnd_log.level_procedure,l_debug,'At the Start of Check_Visit_Task_Req_Items ');
1969 END IF;
1970
1971 -- TASK NAME ==== NAME
1972 IF (p_task_rec.VISIT_TASK_NAME IS NULL OR p_Task_rec.VISIT_TASK_NAME = Fnd_Api.G_MISS_CHAR) THEN
1973 Fnd_Message.set_name ('AHL', 'AHL_VWP_NAME_MISSING');
1974 Fnd_Msg_Pub.ADD;
1975 x_return_status := Fnd_Api.g_ret_sts_error;
1976 RETURN;
1977 END IF;
1978
1979 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1980 fnd_log.string(fnd_log.level_statement,l_debug,'Inside Validation Start from Hour = ' || p_task_rec.START_FROM_HOUR);
1981 END IF;
1982
1983 IF (p_task_rec.START_FROM_HOUR IS NOT NULL and p_Task_rec.START_FROM_HOUR <> Fnd_Api.G_MISS_NUM) THEN
1984 IF p_task_rec.START_FROM_HOUR < 0 OR FLOOR(p_task_rec.START_FROM_HOUR) <> p_task_rec.START_FROM_HOUR THEN
1985 Fnd_Message.set_name ('AHL', 'AHL_VWP_ONLY_POSITIVE_VALUE');
1986 Fnd_Msg_Pub.ADD;
1987 x_return_status := Fnd_Api.g_ret_sts_error;
1988 RETURN;
1989 END IF;
1990 END IF;
1991
1992 END Check_Visit_Task_Req_Items;
1993
1994 ---------------------------------------------------------------------
1995 -- PROCEDURE
1996 -- Check_Visit_Task_UK_Items
1997 --
1998 -- PURPOSE
1999 --
2000 ---------------------------------------------------------------------
2001 PROCEDURE Check_Visit_Task_UK_Items (
2002 p_task_rec IN AHL_VWP_RULES_PVT.Task_Rec_Type,
2003 p_validation_mode IN VARCHAR2 := Jtf_Plsql_Api.g_create,
2004 x_return_status OUT NOCOPY VARCHAR2
2005 )
2006 IS
2007 l_valid_flag VARCHAR2(1);
2008 l_api_name CONSTANT VARCHAR2(30) := 'Check_Visit_Task_Req_Items';
2009 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_Api_name;
2010 l_debug CONSTANT VARCHAR2(90) := 'ahl.plsql.'||L_FULL_NAME;
2011
2012 BEGIN
2013 x_return_status := Fnd_Api.g_ret_sts_success;
2014 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2015 fnd_log.string(fnd_log.level_procedure,l_debug,'At the Start of Check_Visit_Task_UK_Items ');
2016 END IF;
2017 --
2018 -- For Task, when ID is passed in, we need to
2019 -- check if this ID is unique.
2020 IF p_validation_mode = Jtf_Plsql_Api.g_create AND p_task_rec.Visit_Task_ID IS NOT NULL THEN
2021 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2022 fnd_log.string(fnd_log.level_statement,'ahl.plsql.',': = Check_Visit_Task_UK_Items Uniqueness Of ID');
2023 END IF;
2024 -- FOR CREATION
2025 IF Ahl_Utility_Pvt.check_uniqueness(
2026 'Ahl_Visit_Tasks_vl',
2027 'Visit_Task_ID = ' || p_task_rec.Visit_Task_ID
2028 ) = Fnd_Api.g_false
2029 THEN
2030 Fnd_Message.set_name ('AHL', 'AHL_VWP_DUPLICATE_TASK_ID');
2031 Fnd_Msg_Pub.ADD;
2032 x_return_status := Fnd_Api.g_ret_sts_error;
2033 RETURN;
2034 END IF;
2035 END IF;
2036
2037 END Check_Visit_Task_UK_Items;
2038
2039 END AHL_VWP_PLAN_TASKS_PVT;