[Home] [Help]
PACKAGE BODY: APPS.AHL_VISIT_TYPE_PVT
Source
1 PACKAGE BODY AHL_VISIT_TYPE_PVT AS
2 /* $Header: AHLVVTPB.pls 120.2.12020000.2 2012/12/11 05:39:21 prakkum noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AHL_VISIT_TYPE_PVT';
5 G_DEBUG VARCHAR2(1) := AHL_DEBUG_PUB.is_log_enabled;
6
7 -- constants for WHO Columns
8 G_LAST_UPDATE_DATE DATE := SYSDATE;
9 G_LAST_UPDATED_BY NUMBER(15) := FND_GLOBAL.user_id;
10 G_LAST_UPDATE_LOGIN NUMBER(15) := FND_GLOBAL.login_id;
11 G_CREATION_DATE DATE := SYSDATE;
12 G_CREATED_BY NUMBER(15) := FND_GLOBAL.user_id;
13
14
15 -------------------------------------------------------------------
16 -- Procedure name : Delete_Visit_Type
17 -- Function : To delete a visit type
18 -- Parameters : p_visit_type_id
19 ----------------------------------------------------------------------
20 PROCEDURE Delete_Visit_Type (
21 p_api_version IN NUMBER,
22 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
23 p_commit IN VARCHAR2 := Fnd_Api.g_false,
24 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
25 p_module_type IN VARCHAR2 := NULL,
26 p_visit_type_id IN NUMBER,
27 x_return_status OUT NOCOPY VARCHAR2,
28 x_msg_count OUT NOCOPY NUMBER,
29 x_msg_data OUT NOCOPY VARCHAR2
30 )
31 IS
32
33 L_API_VERSION CONSTANT NUMBER := 1.0;
34 L_API_NAME CONSTANT VARCHAR2(30) := 'Delete_Visit_Type';
35 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
36 L_DEBUG CONSTANT VARCHAR2(90) := 'ahl.plsql.'||L_FULL_NAME;
37
38
39 BEGIN
40
41 -- Standard Start of API savepoint
42 SAVEPOINT Delete_Visit_Type;
43
44 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
45 fnd_log.string(fnd_log.level_procedure,L_DEBUG||'.begin','At the start of PLSQL procedure');
46 END IF;
47
48 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
49 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Visit Type id of the record to be deleted '|| p_visit_type_id);
50 END IF;
51
52 -- Initialize API return status to success
53 -- Initialize message list if p_init_msg_list is set to TRUE.
54 IF FND_API.to_boolean(p_init_msg_list) THEN
55 FND_MSG_PUB.initialize;
56 END IF;
57
58 x_return_status := FND_API.G_RET_STS_SUCCESS;
59
60 -- Standard call to check for call compatibility.
61 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
62 p_api_version,
63 l_api_name,G_PKG_NAME) THEN
64 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
65 END IF;
66
67 ------------------------Start of API Body------------------------------------
68
69 -- To delete a Visit Type following records need to be deleted
70 -- All the Stage Type associated to the stages of the Visit Type
71 -- All the Stage rules defined between the stages of the Visit Type
72 -- All the Stages associated to the visit type
73 -- Visit Type record
74
75 -- Delete Stage Types
76 DELETE FROM AHL_VST_TYP_STG_TYP_ASOC
77 WHERE VISIT_TYPE_STAGE_ID IN (SELECT VISIT_TYPE_STAGE_ID
78 FROM AHL_VISIT_TYPE_STAGES_B AVTSB
79 WHERE VISIT_TYPE_ID = p_visit_type_id);
80
81 -- Delete Stage Links
82 DELETE FROM AHL_VST_TYP_STAGE_LINKS
83 WHERE OBJECT_ID IN (SELECT VISIT_TYPE_STAGE_ID
84 FROM AHL_VISIT_TYPE_STAGES_B AVTSB
85 WHERE VISIT_TYPE_ID = p_visit_type_id)
86 OR SUBJECT_ID IN(SELECT VISIT_TYPE_STAGE_ID
87 FROM AHL_VISIT_TYPE_STAGES_B AVTSB
88 WHERE VISIT_TYPE_ID = p_visit_type_id);
89
90 -- Delete Stages from TL
91 DELETE FROM AHL_VISIT_TYPE_STAGES_TL
92 WHERE VISIT_TYPE_STAGE_ID IN (SELECT VISIT_TYPE_STAGE_ID
93 FROM AHL_VISIT_TYPE_STAGES_B AVTSB
94 WHERE VISIT_TYPE_ID = p_visit_type_id);
95
96 -- Delete Stages
97 DELETE FROM AHL_VISIT_TYPE_STAGES_B
98 WHERE VISIT_TYPE_ID = p_visit_type_id;
99
100 -- Delete Visit Types from TL
101 DELETE FROM AHL_VISIT_TYPES_TL
102 WHERE VISIT_TYPE_ID = p_visit_type_id;
103
104 -- Delete Visit Types
105 DELETE FROM AHL_VISIT_TYPES_B
106 WHERE VISIT_TYPE_ID = p_visit_type_id;
107
108 IF ( SQL%ROWCOUNT = 0 ) THEN
109 FND_MESSAGE.set_name('AHL','AHL_VST_TYP_REC_CHNGD');
110 FND_MSG_PUB.add;
111 x_return_status := FND_API.G_RET_STS_ERROR;
112 END IF;
113
114 ------------------------End of API Body--------------------------------------
115
116 --Standard check for commit
117 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
118 COMMIT;
119 END IF;
120
121 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
122 fnd_log.string(fnd_log.level_procedure,L_DEBUG||'.end','At the end of PLSQL procedure');
123 END IF;
124
125 EXCEPTION
126
127 WHEN Fnd_Api.g_exc_error THEN
128 ROLLBACK TO Delete_Visit_Type;
129 x_return_status := Fnd_Api.g_ret_sts_error;
130 Fnd_Msg_Pub.count_and_get (
131 p_encoded => Fnd_Api.g_false,
132 p_count => x_msg_count,
133 p_data => x_msg_data);
134 AHL_DEBUG_PUB.debug('Delete_Visit_Type: In g_exc_unexpected_error block ' || ' x_msg_count ' || x_msg_count );
135
136 WHEN Fnd_Api.g_exc_unexpected_error THEN
137 ROLLBACK TO Delete_Visit_Type;
138 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
139 Fnd_Msg_Pub.count_and_get (
140 p_encoded => Fnd_Api.g_false,
141 p_count => x_msg_count,
142 p_data => x_msg_data);
143 AHL_DEBUG_PUB.debug('Delete_Visit_Type: In g_exc_unexpected_error block ' || ' x_msg_count ' || x_msg_count );
144
145
146 WHEN OTHERS THEN
147 ROLLBACK TO Delete_Visit_Type;
148 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
149 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_unexp_error)
150 THEN
151 Fnd_Msg_Pub.add_exc_msg (G_PKG_NAME, l_api_name);
152 END IF;
153 Fnd_Msg_Pub.count_and_get (
154 p_encoded => Fnd_Api.g_false,
155 p_count => x_msg_count,
156 p_data => x_msg_data);
157 AHL_DEBUG_PUB.debug('Delete_Visit_Type: In OTHERS block ' || ' x_msg_count ' || x_msg_count );
158
159 END Delete_Visit_Type;
160
161 -------------------------------------------------------------------
162 -- Procedure name : Complete_Visit_Type
163 -- Function : To complete a visit type
164 -- Parameters : p_visit_type_id
165 -- Parameters : p_obj_version_num
166 ----------------------------------------------------------------------
167 PROCEDURE Complete_Visit_Type (
168 p_api_version IN NUMBER,
169 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
170 p_commit IN VARCHAR2 := Fnd_Api.g_false,
171 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
172 p_module_type IN VARCHAR2 := NULL,
173 p_visit_type_id IN NUMBER,
174 p_obj_version_num IN NUMBER,
175 x_return_status OUT NOCOPY VARCHAR2,
176 x_msg_count OUT NOCOPY NUMBER,
177 x_msg_data OUT NOCOPY VARCHAR2
178 )
179 IS
180
181 L_visit_type VARCHAR(30);
182 L_count_stage NUMBER;
183 L_linked_vst_typ_id NUMBER;
184 L_visit_type_code VARCHAR(30);
185 L_MC_Name VARCHAR(30);
186 l_component_visit_flag VARCHAR2(1);
187 L_API_VERSION CONSTANT NUMBER := 1.0;
188 L_API_NAME CONSTANT VARCHAR2(30) := 'Complete_Visit_Type';
189 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
190 L_DEBUG CONSTANT VARCHAR2(90) := 'ahl.plsql.'||L_FULL_NAME;
191 l_msg_count NUMBER;
192 l_msg_data VARCHAR2(2000);
193 l_return_status VARCHAR2(1);
194
195 -- cursor to get the count of stages in the visit type
196 CURSOR count_stage(c_visit_type_id IN Number) IS
197 Select COUNT(VISIT_TYPE_STAGE_ID)
198 FROM AHL_VISIT_TYPE_STAGES_B AVTSB
199 WHERE VISIT_TYPE_ID = c_visit_type_id;
200
201
202 -- cursor to count the number of stage types in the
203 -- stages of the visit_type
204 CURSOR count_stage_type(c_visit_type_id IN Number) IS
205 SELECT AVTSB.STAGE_NUMBER,
206 COUNT(VISIT_TYP_STAGE_TYP_ASOC_ID) as Count
207 FROM AHL_VST_TYP_STG_TYP_ASOC AVTSTA,
208 AHL_VISIT_TYPE_STAGES_B AVTSB
209 WHERE AVTSB.VISIT_TYPE_STAGE_ID = AVTSTA.VISIT_TYPE_STAGE_ID(+)
210 AND AVTSB.VISIT_TYPE_ID = c_visit_type_id
211 group by AVTSB.STAGE_NUMBER;
212
213 -- cursor to get the visit type corresponding to the visit type id
214 /* MANESING::Component Maintenance Planning Project, 05-Jul-2011
215 * 1. Fetch component visit flag also.
216 * 2. Removed mc_name from view AHL_VISIT_TYPES_VL, thereby added outer join with table AHL_MC_HEADERS_B.
217 */
218 CURSOR get_visit_type(c_visit_type_id in Number) IS
219 SELECT AVTV.visit_type_code,
220 AVTV.visit_type_name,
221 AMHB.name mc_name,
222 AVTV.linked_visit_type_id,
223 AVTV.component_visit_flag
224 FROM AHL_VISIT_TYPES_VL AVTV,
225 AHL_MC_HEADERS_B AMHB
226 WHERE AVTV.visit_type_id = c_visit_type_id
227 AND AVTV.mc_id = AMHB.mc_header_id (+);
228
229 BEGIN
230
231 -- Standard Start of API savepoint
232 SAVEPOINT Complete_Visit_Type;
233
234 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
235 fnd_log.string(fnd_log.level_procedure,L_DEBUG||'.begin','At the start of PLSQL procedure');
236 END IF;
237
238 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
239 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Visit Type id of the record to be Completed '|| p_visit_type_id);
240 END IF;
241
242 -- Initialize API return status to success
243 -- Initialize message list if p_init_msg_list is set to TRUE.
244 IF FND_API.to_boolean(p_init_msg_list) THEN
245 FND_MSG_PUB.initialize;
246 END IF;
247
248 x_return_status := FND_API.G_RET_STS_SUCCESS;
249
250 -- Standard call to check for call compatibility.
251 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
252 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
253 END IF;
254
255
256 ------------------------Start of API Body------------------------------------
257
258 -- MANESING::Component Maintenance Planning Project, 05-Jul-2011, get component visit flag also
259 OPEN get_visit_type(p_visit_type_id);
260 FETCH get_visit_type INTO L_visit_type_code,L_visit_type,L_MC_Name,L_linked_vst_typ_id,l_component_visit_flag;
261 CLOSE get_visit_type;
262
263 /* A visit type can be completed only if it has atleast one Stage associated and
264 * atleast one of the stages have a stage type associated. So before completing the visit
265 * type checks are done.
266 */
267 /* MANESING::Component Maintenance Planning Project, 05-Jul-2011, the above said checks are not
268 * applicable to Component Visit Types as there won't be any Stage associated with them.
269 */
270 IF (NVL(l_component_visit_flag, 'N') = 'N') THEN
271
272 -- check if the visit type has a stage associated
273 -- if no stage then error is thrown
274 OPEN count_stage(p_visit_type_id);
275 FETCH count_stage INTO L_count_stage;
276 CLOSE count_stage;
277
278 if (l_count_stage = 0) THEN
279 Fnd_Message.SET_NAME('AHL','AHL_VWP_VST_TYPE_STG_REQ');
280 Fnd_Message.Set_Token('VISIT_TYPE', l_visit_type);
281 Fnd_Message.Set_Token('MASTER_CONFIG', L_MC_Name);
282 Fnd_Msg_Pub.ADD;
283 RAISE Fnd_Api.G_EXC_ERROR;
284 END IF;
285
286 -- check if any of the stages have stage type count as 0, If yes
287 -- error is thrown that atleast one stage type has to be there
288 FOR stage_rec in count_stage_type(p_visit_type_id)
289 LOOP
290
291 If( stage_rec.count = 0) THEN
292 Fnd_Message.SET_NAME('AHL','AHL_VWP_STG_TYP_REQ');
293 Fnd_Message.Set_Token('VISIT_TYPE', l_visit_type);
294 Fnd_Message.Set_Token('MASTER_CONFIG', L_MC_Name);
295 Fnd_Message.Set_Token('STAGE_NUM', stage_rec.STAGE_NUMBER);
296 Fnd_Msg_Pub.ADD;
297 RAISE Fnd_Api.G_EXC_ERROR;
298 END IF;
299
300 END LOOP;
301
302 END IF; -- if NVL(l_component_visit_flag, 'N') = 'N'
303
304 -- If the link visit type id is not null, it means that a record in completed status already exists
305 -- So that needs to be deleted first. Delete method is called to delete the Complete Visit Type record
306 IF L_linked_vst_typ_id is not NULL THEN
307
308 Delete_Visit_Type (
309 p_api_version => 1.0,
310 p_init_msg_list => p_init_msg_list,
311 p_commit => FND_API.G_FALSE,
312 p_validation_level => p_validation_level,
313 p_module_type => p_module_type,
314 p_visit_type_id => L_linked_vst_typ_id,
315 x_return_status => l_return_status,
316 x_msg_count => l_msg_count,
317 x_msg_data => l_msg_data
318 );
319
320 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
321 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Return Status after calling Delete_Visit_Type = '|| l_return_status);
322 END IF;
323
324 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
325 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
326 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
327 RAISE FND_API.G_EXC_ERROR;
328 END IF;
329
330 END IF;
331
332
333 --If Return Status is successful, there has been no error in deleting the record already in complete status
334 IF(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
335
336 AHL_DEBUG_PUB.debug(L_DEBUG || ': p_visit_type_id-' || p_visit_type_id || ': About to Complete visit type');
337
338 UPDATE AHL_VISIT_TYPES_B
339 SET OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
340 LAST_UPDATE_DATE = G_LAST_UPDATE_DATE,
341 LAST_UPDATED_BY = G_LAST_UPDATED_BY,
342 LAST_UPDATE_LOGIN = G_LAST_UPDATE_LOGIN,
343 STATUS_CODE = 'COMPLETE',
344 LINKED_VISIT_TYPE_ID = null
345 WHERE VISIT_TYPE_ID = p_visit_type_id
346 and object_version_number = p_obj_version_num;
347
348 IF(SQL%ROWCOUNT = 0 ) THEN
349 FND_MESSAGE.set_name('AHL','AHL_VWP_VST_TYP_REC_CHNGD');
350 FND_MSG_PUB.add;
351 x_return_status := FND_API.G_RET_STS_ERROR;
352 END IF;
353 END IF;
354
355
356 ------------------------End of API Body------------------------------------
357
358 --Standard check for commit
359 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
360 COMMIT;
361 END IF;
362
363 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
364 fnd_log.string(fnd_log.level_procedure,L_DEBUG||'.end','At the end of PLSQL procedure');
365 END IF;
366
367 EXCEPTION
368
369 WHEN Fnd_Api.g_exc_error THEN
370 ROLLBACK TO Complete_Visit_Type;
371 x_return_status := Fnd_Api.g_ret_sts_error;
372 Fnd_Msg_Pub.count_and_get (
373 p_encoded => Fnd_Api.g_false,
374 p_count => x_msg_count,
375 p_data => x_msg_data);
376 AHL_DEBUG_PUB.debug('Complete_Visit_Type: In g_exc_error block ' || ' x_msg_count ' || x_msg_count );
377
378 WHEN Fnd_Api.g_exc_unexpected_error THEN
379 ROLLBACK TO Complete_Visit_Type;
380 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
381 Fnd_Msg_Pub.count_and_get (
382 p_encoded => Fnd_Api.g_false,
383 p_count => x_msg_count,
384 p_data => x_msg_data);
385 AHL_DEBUG_PUB.debug('Complete_Visit_Type: In G_EXC_UNEXPECTED_ERROR block ' || ' x_msg_count ' || x_msg_count );
386
387
388 WHEN OTHERS THEN
389 ROLLBACK TO Complete_Visit_Type;
390 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
391 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_unexp_error)
392 THEN
393 Fnd_Msg_Pub.add_exc_msg (G_PKG_NAME, l_api_name);
394 END IF;
395 Fnd_Msg_Pub.count_and_get (
396 p_encoded => Fnd_Api.g_false,
397 p_count => x_msg_count,
398 p_data => x_msg_data);
399 AHL_DEBUG_PUB.debug('Complete_Visit_Type: In OTHERS block ' || ' x_msg_count ' || x_msg_count );
400
401 END Complete_Visit_Type;
402
403 -------------------------------------------------------------------
404 -- Procedure name : Create_Rev_Visit_Type
405 -- Function : To Create a revision of the complete visit type
406 -- Parameters : p_par_visit_type_id
407 -- Parameters : p_visit_type_id
408 ----------------------------------------------------------------------
409 PROCEDURE Create_Rev_Visit_Type (
410 p_api_version IN NUMBER,
411 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
412 p_commit IN VARCHAR2 := Fnd_Api.g_false,
413 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
414 p_module_type IN VARCHAR2 := NULL,
415 p_visit_type_id IN NUMBER,
416 p_par_visit_type_id IN NUMBER,
417 x_return_status OUT NOCOPY VARCHAR2,
418 x_msg_count OUT NOCOPY NUMBER,
419 x_msg_data OUT NOCOPY VARCHAR2
420 )
421 IS
422
423 L_API_VERSION CONSTANT NUMBER := 1.0;
424 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_Rev_Visit_Type';
425 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
426 L_DEBUG CONSTANT VARCHAR2(90) := 'ahl.plsql.'||L_FULL_NAME;
427 l_msg_count NUMBER;
428 l_msg_data VARCHAR2(2000);
429 l_return_status VARCHAR2(1);
430 L_stage_id NUMBER;
431 L_visit_type VARCHAR2(30);
432 L_mc_name VARCHAR2(30);
433 l_component_visit_flag VARCHAR2(1);
434
435 CURSOR get_stages(c_visit_type_id IN Number) IS
436 SELECT VISIT_TYPE_STAGE_ID
437 FROM AHL_VISIT_TYPE_STAGES_B
438 WHERE VISIT_TYPE_ID = c_visit_type_id;
439
440 CURSOR get_seq_stage_id IS
441 SELECT AHL_VISIT_TYPE_STAGES_B_S.nextVal
442 FROM dual;
443
444 /* MANESING::Component Maintenance Planning Project, 05-Jul-2011,
445 * Removed mc_name from view AHL_VISIT_TYPES_VL, thereby added outer join with table AHL_MC_HEADERS_B.
446 */
447 CURSOR draft_exists(c_par_visit_type_id IN Number) IS
448 SELECT AVTV.visit_type_name,
449 AMHB.name mc_name
450 FROM AHL_VISIT_TYPES_VL AVTV,
451 AHL_MC_HEADERS_B AMHB
452 WHERE AVTV.linked_visit_type_id = c_par_visit_type_id
453 AND AVTV.mc_id = AMHB.mc_header_id (+);
454
455 /* MANESING::Component Maintenance Planning Project, 05-Jul-2011
456 * Added cursor to get component visit flag for the given Visit Type.
457 */
458 CURSOR get_visit_type_dtls_csr (c_visit_type_id NUMBER) IS
459 SELECT component_visit_flag
460 FROM AHL_VISIT_TYPES_B
461 WHERE visit_type_id = c_visit_type_id;
462
463 BEGIN
464
465 -- Standard Start of API savepoint
466 SAVEPOINT Create_Rev_Visit_Type;
467
468 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
469 fnd_log.string(fnd_log.level_procedure,L_DEBUG||'.begin','At the start of PLSQL procedure');
470 END IF;
471
472 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
473 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Visit Type id of the record whose revision is created '|| p_par_visit_type_id);
474 END IF;
475
476 -- Initialize message list if p_init_msg_list is set to TRUE.
477 IF FND_API.to_boolean(p_init_msg_list) THEN
478 FND_MSG_PUB.initialize;
479 END IF;
480
481 -- Initialize API return status to success
482 x_return_status := FND_API.G_RET_STS_SUCCESS;
483
484 -- Standard call to check for call compatibility.
485 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
486 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
487 END IF;
488
489 ------------------------Start of API Body------------------------------------
490
491 OPEN draft_exists(p_par_visit_type_id);
492 FETCH draft_exists INTO L_visit_type,L_mc_name;
493 CLOSE draft_exists;
494
495 -- If a draft record already exists then the visit type can not be completed
496 -- So corresponding error is thrown
497 if (L_visit_type is not null) THEN
498 Fnd_Message.SET_NAME('AHL','AHL_VWP_VST_TYP_DRAFT_EXISTS');
499 Fnd_Message.Set_Token('VISIT_TYPE', L_visit_type);
500 Fnd_Message.Set_Token('MC_NAME', L_mc_name);
501 Fnd_Msg_Pub.ADD;
502 RAISE Fnd_Api.G_EXC_ERROR;
503 END IF;
504
505
506 -- To Create a revision a new record has to be created with the same data. For it following records need to be created
507 -- Visit Type
508 -- Stages
509 -- Stage Links
510 -- Stage Types
511
512 -- Insert Visit Type Record
513 INSERT INTO
514 AHL_VISIT_TYPES_B
515 (VISIT_TYPE_ID ,
516 VISIT_TYPE_CODE ,
517 SERVICE_CATEGORY_CODE,
518 STATUS_CODE ,
519 ESTIMATED_DURATION ,
520 MC_ID ,
521 LINKED_VISIT_TYPE_ID ,
522 TRANSIT_TYPE_FLAG ,
523 --MANESING::Component Maintenance Planning Project, 05-Jul-2011, added component visit flag
524 COMPONENT_VISIT_FLAG ,
525 SECURITY_GROUP_ID ,
526 OBJECT_VERSION_NUMBER,
527 LAST_UPDATE_DATE ,
528 LAST_UPDATED_BY ,
529 CREATION_DATE ,
530 CREATED_BY ,
531 LAST_UPDATE_LOGIN ,
532 ATTRIBUTE_CATEGORY ,
533 ATTRIBUTE1 ,
534 ATTRIBUTE2 ,
535 ATTRIBUTE3 ,
536 ATTRIBUTE4 ,
537 ATTRIBUTE5 ,
538 ATTRIBUTE6 ,
539 ATTRIBUTE7 ,
540 ATTRIBUTE8 ,
541 ATTRIBUTE9 ,
542 ATTRIBUTE10 ,
543 ATTRIBUTE11 ,
544 ATTRIBUTE12 ,
545 ATTRIBUTE13 ,
546 ATTRIBUTE14 ,
547 ATTRIBUTE15)
548 SELECT
549 p_visit_type_id ,
550 VISIT_TYPE_CODE ,
551 SERVICE_CATEGORY_CODE,
552 'DRAFT' ,
553 ESTIMATED_DURATION ,
554 MC_ID ,
555 p_par_visit_type_id ,
556 TRANSIT_TYPE_FLAG ,
557 --MANESING::Component Maintenance Planning Project, 05-Jul-2011, select component visit flag also
558 COMPONENT_VISIT_FLAG ,
559 SECURITY_GROUP_ID ,
560 1 ,
561 G_LAST_UPDATE_DATE ,
562 G_LAST_UPDATED_BY ,
563 G_CREATION_DATE ,
564 G_CREATED_BY ,
565 G_LAST_UPDATE_LOGIN ,
566 ATTRIBUTE_CATEGORY ,
567 ATTRIBUTE1 ,
568 ATTRIBUTE2 ,
569 ATTRIBUTE3 ,
570 ATTRIBUTE4 ,
571 ATTRIBUTE5 ,
572 ATTRIBUTE6 ,
573 ATTRIBUTE7 ,
574 ATTRIBUTE8 ,
575 ATTRIBUTE9 ,
576 ATTRIBUTE10 ,
577 ATTRIBUTE11 ,
578 ATTRIBUTE12 ,
579 ATTRIBUTE13 ,
580 ATTRIBUTE14 ,
581 ATTRIBUTE15
582 FROM AHL_VISIT_TYPES_B
583 WHERE VISIT_TYPE_ID = p_par_visit_type_id;
584
585 -- Insert Into Visit Type _TL table
586 INSERT INTO
587 AHL_VISIT_TYPES_TL
588 (VISIT_TYPE_ID ,
589 DESCRIPTION ,
590 LANGUAGE ,
591 SOURCE_LANG ,
592 SECURITY_GROUP_ID ,
593 LAST_UPDATE_DATE ,
594 LAST_UPDATED_BY ,
595 CREATION_DATE ,
596 CREATED_BY ,
597 LAST_UPDATE_LOGIN
598 )
599 SELECT
600 p_visit_type_id ,
601 DESCRIPTION ,
602 LANGUAGE ,
603 SOURCE_LANG ,
604 SECURITY_GROUP_ID ,
605 G_LAST_UPDATE_DATE ,
606 G_LAST_UPDATED_BY ,
607 G_CREATION_DATE ,
608 G_CREATED_BY ,
609 G_LAST_UPDATE_LOGIN
610 FROM AHL_VISIT_TYPES_TL
611 WHERE visit_type_id = p_par_visit_type_id;
612
613 /* MANESING::Component Maintenance Planning Project, 05-Jul-2011, following insertions to Stage related
614 * tables are not needed for Component Visit Types as there won't be any Stage associated with them.
615 */
616 OPEN get_visit_type_dtls_csr (p_visit_type_id);
617 FETCH get_visit_type_dtls_csr INTO l_component_visit_flag;
618 CLOSE get_visit_type_dtls_csr;
619
620 IF (NVL(l_component_visit_flag, 'N') = 'N') THEN
621
622 -- Iteration is done over all the stages of the visit type and new records are created for every stage
623 FOR stage_rec in get_stages(p_par_visit_type_id)
624 LOOP
625
626 OPEN get_seq_stage_id;
627 FETCH get_seq_stage_id INTO L_STAGE_ID;
628 CLOSE get_seq_stage_id;
629
630 -- Insert into Stages Table
631 INSERT INTO
632 AHL_VISIT_TYPE_STAGES_B
633 (VISIT_TYPE_STAGE_ID ,
634 VISIT_TYPE_ID ,
635 DURATION ,
636 STAGE_NUMBER ,
637 SECURITY_GROUP_ID ,
638 OBJECT_VERSION_NUMBER ,
639 LAST_UPDATE_DATE ,
640 LAST_UPDATED_BY ,
641 CREATION_DATE ,
642 CREATED_BY ,
643 LAST_UPDATE_LOGIN ,
644 ATTRIBUTE_CATEGORY ,
645 ATTRIBUTE1 ,
646 ATTRIBUTE2 ,
647 ATTRIBUTE3 ,
648 ATTRIBUTE4 ,
649 ATTRIBUTE5 ,
650 ATTRIBUTE6 ,
651 ATTRIBUTE7 ,
652 ATTRIBUTE8 ,
653 ATTRIBUTE9 ,
654 ATTRIBUTE10 ,
655 ATTRIBUTE11 ,
656 ATTRIBUTE12 ,
657 ATTRIBUTE13 ,
658 ATTRIBUTE14 ,
659 ATTRIBUTE15
660 )
661 SELECT
662 L_STAGE_ID ,
663 p_visit_type_id ,
664 DURATION ,
665 STAGE_NUMBER ,
666 SECURITY_GROUP_ID ,
667 1 ,
668 G_LAST_UPDATE_DATE ,
669 G_LAST_UPDATED_BY ,
670 G_CREATION_DATE ,
671 G_CREATED_BY ,
672 G_LAST_UPDATE_LOGIN ,
673 ATTRIBUTE_CATEGORY ,
674 ATTRIBUTE1 ,
675 ATTRIBUTE2 ,
676 ATTRIBUTE3 ,
677 ATTRIBUTE4 ,
678 ATTRIBUTE5 ,
679 ATTRIBUTE6 ,
680 ATTRIBUTE7 ,
681 ATTRIBUTE8 ,
682 ATTRIBUTE9 ,
683 ATTRIBUTE10 ,
684 ATTRIBUTE11 ,
685 ATTRIBUTE12 ,
686 ATTRIBUTE13 ,
687 ATTRIBUTE14 ,
688 ATTRIBUTE15
689 FROM AHL_VISIT_TYPE_STAGES_B
690 WHERE VISIT_TYPE_STAGE_ID = stage_rec.VISIT_TYPE_STAGE_ID;
691
692 -- Insert into Stages _TL table
693 INSERT INTO
694 AHL_VISIT_TYPE_STAGES_TL
695 (VISIT_TYPE_STAGE_ID ,
696 STAGE_NAME ,
697 LANGUAGE ,
698 SOURCE_LANG ,
699 SECURITY_GROUP_ID ,
700 LAST_UPDATE_DATE ,
701 LAST_UPDATED_BY ,
702 CREATION_DATE ,
703 CREATED_BY ,
704 LAST_UPDATE_LOGIN
705 )
706 SELECT
707 L_STAGE_ID ,
708 STAGE_NAME ,
709 LANGUAGE ,
710 SOURCE_LANG ,
711 SECURITY_GROUP_ID ,
712 G_LAST_UPDATE_DATE ,
713 G_LAST_UPDATED_BY ,
714 G_CREATION_DATE ,
715 G_CREATED_BY ,
716 G_LAST_UPDATE_LOGIN
717 FROM AHL_VISIT_TYPE_STAGES_TL
718 WHERE VISIT_TYPE_STAGE_ID = stage_rec.VISIT_TYPE_STAGE_ID;
719
720 -- Each stage has many stage types associated. So new records are created for them
721 -- Insert into Associated Stage Type table
722 INSERT INTO
723 AHL_VST_TYP_STG_TYP_ASOC
724 (VISIT_TYP_STAGE_TYP_ASOC_ID ,
725 VISIT_TYPE_STAGE_ID ,
726 STAGE_TYPE_CODE ,
727 SECURITY_GROUP_ID ,
728 LAST_UPDATE_DATE ,
729 LAST_UPDATED_BY ,
730 CREATION_DATE ,
731 CREATED_BY ,
732 LAST_UPDATE_LOGIN ,
733 ATTRIBUTE_CATEGORY ,
734 ATTRIBUTE1 ,
735 ATTRIBUTE2 ,
736 ATTRIBUTE3 ,
737 ATTRIBUTE4 ,
738 ATTRIBUTE5 ,
739 ATTRIBUTE6 ,
740 ATTRIBUTE7 ,
741 ATTRIBUTE8 ,
742 ATTRIBUTE9 ,
743 ATTRIBUTE10 ,
744 ATTRIBUTE11 ,
745 ATTRIBUTE12 ,
746 ATTRIBUTE13 ,
747 ATTRIBUTE14 ,
748 ATTRIBUTE15
749 )
750 SELECT
751 AHL_VST_TYP_STG_TYP_ASOC_S.nextVal,
752 L_STAGE_ID ,
753 STAGE_TYPE_CODE ,
754 SECURITY_GROUP_ID ,
755 G_LAST_UPDATE_DATE ,
756 G_LAST_UPDATED_BY ,
757 G_CREATION_DATE ,
758 G_CREATED_BY ,
759 G_LAST_UPDATE_LOGIN ,
760 ATTRIBUTE_CATEGORY ,
761 ATTRIBUTE1 ,
762 ATTRIBUTE2 ,
763 ATTRIBUTE3 ,
764 ATTRIBUTE4 ,
765 ATTRIBUTE5 ,
766 ATTRIBUTE6 ,
767 ATTRIBUTE7 ,
768 ATTRIBUTE8 ,
769 ATTRIBUTE9 ,
770 ATTRIBUTE10 ,
771 ATTRIBUTE11 ,
772 ATTRIBUTE12 ,
773 ATTRIBUTE13 ,
774 ATTRIBUTE14 ,
775 ATTRIBUTE15
776 FROM AHL_VST_TYP_STG_TYP_ASOC
777 WHERE VISIT_TYPE_STAGE_ID = stage_rec.VISIT_TYPE_STAGE_ID;
778
779 END LOOP;
780
781 -- All the stage links of all the stages of the Visit type are copied into new records
782 INSERT INTO
783 AHL_VST_TYP_STAGE_LINKS
784 (VISIT_TYPE_STAGE_LINK_ID ,
785 OBJECT_ID ,
786 SUBJECT_ID ,
787 RELATION_TYPE ,
788 SECURITY_GROUP_ID ,
789 OBJECT_VERSION_NUMBER ,
790 LAST_UPDATE_DATE ,
791 LAST_UPDATED_BY ,
792 CREATION_DATE ,
793 CREATED_BY ,
794 LAST_UPDATE_LOGIN ,
795 ATTRIBUTE_CATEGORY ,
796 ATTRIBUTE1 ,
797 ATTRIBUTE2 ,
798 ATTRIBUTE3 ,
799 ATTRIBUTE4 ,
800 ATTRIBUTE5 ,
801 ATTRIBUTE6 ,
802 ATTRIBUTE7 ,
803 ATTRIBUTE8 ,
804 ATTRIBUTE9 ,
805 ATTRIBUTE10 ,
806 ATTRIBUTE11 ,
807 ATTRIBUTE12 ,
808 ATTRIBUTE13 ,
809 ATTRIBUTE14 ,
810 ATTRIBUTE15
811 )
812 SELECT
813 AHL_VST_TYP_STAGE_LINKS_S.nextVal ,
814 STAGES2.VISIT_TYPE_STAGE_ID ,
815 STAGES3.VISIT_TYPE_STAGE_ID ,
816 LINKS.RELATION_TYPE ,
817 LINKS.SECURITY_GROUP_ID ,
818 1 ,
819 G_LAST_UPDATE_DATE ,
820 G_LAST_UPDATED_BY ,
821 G_CREATION_DATE ,
822 G_CREATED_BY ,
823 G_LAST_UPDATE_LOGIN ,
824 LINKS.ATTRIBUTE_CATEGORY ,
825 LINKS.ATTRIBUTE1 ,
826 LINKS.ATTRIBUTE2 ,
827 LINKS.ATTRIBUTE3 ,
828 LINKS.ATTRIBUTE4 ,
829 LINKS.ATTRIBUTE5 ,
830 LINKS.ATTRIBUTE6 ,
831 LINKS.ATTRIBUTE7 ,
832 LINKS.ATTRIBUTE8 ,
833 LINKS.ATTRIBUTE9 ,
834 LINKS.ATTRIBUTE10 ,
835 LINKS.ATTRIBUTE11 ,
836 LINKS.ATTRIBUTE12 ,
837 LINKS.ATTRIBUTE13 ,
838 LINKS.ATTRIBUTE14 ,
839 LINKS.ATTRIBUTE15
840 FROM AHL_VST_TYP_STAGE_LINKS LINKS,
841 AHL_VISIT_TYPE_STAGES_B Stages1,
842 AHL_VISIT_TYPE_STAGES_B Stages2,
843 AHL_VISIT_TYPE_STAGES_B Stages3
844 WHERE LINKS.OBJECT_ID = Stages1.VISIT_TYPE_STAGE_ID
845 AND Stages1.VISIT_TYPE_ID = p_par_visit_type_id
846 --Object id and Subject Id to be inserted in the new record have to be of the newly created stages.
847 --So these ids of the Stages having the same stage number as the old stages are taken
848 AND Stages2.VISIT_TYPE_ID = p_visit_type_id
849 AND Stages2.STAGE_NUMBER = (SELECT STAGE_NUMBER
850 FROM AHL_VISIT_TYPE_STAGES_B
851 WHERE VISIT_TYPE_STAGE_ID = LINKS.OBJECT_ID)
852 AND Stages3.VISIT_TYPE_ID = p_visit_type_id
853 AND Stages3.STAGE_NUMBER = (SELECT STAGE_NUMBER
854 FROM AHL_VISIT_TYPE_STAGES_B
855 WHERE VISIT_TYPE_STAGE_ID = LINKS.SUBJECT_ID);
856
857 END IF; -- NVL(l_component_visit_flag, 'N') = 'N'
858
859
860 ------------------------End of API Body------------------------------------
861
862 --Standard check for commit
863 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
864 COMMIT;
865 END IF;
866
867 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
868 fnd_log.string(fnd_log.level_procedure,L_DEBUG||'.end','At the end of PLSQL procedure');
869 END IF;
870
871 EXCEPTION
872
873 WHEN Fnd_Api.g_exc_error THEN
874 ROLLBACK TO Create_Rev_Visit_Type;
875 x_return_status := Fnd_Api.g_ret_sts_error;
876 Fnd_Msg_Pub.count_and_get (
877 p_encoded => Fnd_Api.g_false,
878 p_count => x_msg_count,
879 p_data => x_msg_data);
880
881 WHEN Fnd_Api.g_exc_unexpected_error THEN
882 ROLLBACK TO Create_Rev_Visit_Type;
883 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
884 Fnd_Msg_Pub.count_and_get (
885 p_encoded => Fnd_Api.g_false,
886 p_count => x_msg_count,
887 p_data => x_msg_data);
888
889 WHEN OTHERS THEN
890 ROLLBACK TO Create_Rev_Visit_Type;
891 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
892 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_unexp_error)
893 THEN
894 Fnd_Msg_Pub.add_exc_msg (G_PKG_NAME, l_api_name);
895 END IF;
896 Fnd_Msg_Pub.count_and_get (
897 p_encoded => Fnd_Api.g_false,
898 p_count => x_msg_count,
899 p_data => x_msg_data);
900
901 END Create_Rev_Visit_Type;
902
903 -------------------------------------------------------------------
904 -- Procedure name : Get_Last_Update_Type
905 -- Function : To get the last update date for a Visit Type among header, stages, stage types & stage rules
906 -- Parameters : p_visit_type_id
907 ----------------------------------------------------------------------
908 FUNCTION Get_Last_Update_Date (
909 p_visit_type_id IN NUMBER
910 )
911 RETURN DATE
912 IS
913
914 L_API_VERSION CONSTANT NUMBER := 1.0;
915 L_API_NAME CONSTANT VARCHAR2(30) := 'Get_Last_Update_Date';
916 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
917 L_DEBUG CONSTANT VARCHAR2(90) := 'ahl.plsql.'||L_FULL_NAME;
918 L_MAX_UPDATE_DATE DATE;
919
920 CURSOR GET_MAX_UPDATE_DATE(C_VISIT_TYPE_ID NUMBER) IS
921 SELECT MAX(LAST_UPDATE_DATE) MAX_LUD FROM(
922 SELECT VST_TYP.LAST_UPDATE_DATE LAST_UPDATE_DATE
923 FROM AHL_VISIT_TYPES_B VST_TYP
924 WHERE VST_TYP.VISIT_TYPE_ID = C_VISIT_TYPE_ID
925
926 UNION ALL
927
928 SELECT MAX(STAGES.LAST_UPDATE_DATE) LAST_UPDATE_DATE
929 FROM AHL_VISIT_TYPE_STAGES_B STAGES, AHL_VISIT_TYPES_B VST_TYP
930 WHERE STAGES.VISIT_TYPE_ID = VST_TYP.VISIT_TYPE_ID
931 AND VST_TYP.VISIT_TYPE_ID = C_VISIT_TYPE_ID
932
933 UNION ALL
934
935 SELECT MAX(LINKS.LAST_UPDATE_DATE) LAST_UPDATE_DATE
936 FROM AHL_VST_TYP_STAGE_LINKS LINKS, AHL_VISIT_TYPE_STAGES_B STAGES, AHL_VISIT_TYPES_B VST_TYP
937 WHERE VST_TYP.VISIT_TYPE_ID = STAGES.VISIT_TYPE_ID
938 AND STAGES.VISIT_TYPE_STAGE_ID = LINKS.OBJECT_ID
939 AND VST_TYP.VISIT_TYPE_ID = C_VISIT_TYPE_ID
940
941 UNION ALL
942
943 SELECT MAX(STG_TYP.LAST_UPDATE_DATE) LAST_UPDATE_DATE
944 FROM AHL_VST_TYP_STG_TYP_ASOC STG_TYP, AHL_VISIT_TYPE_STAGES_B STAGES, AHL_VISIT_TYPES_B VST_TYP
945 WHERE VST_TYP.VISIT_TYPE_ID = STAGES.VISIT_TYPE_ID
946 AND STAGES.VISIT_TYPE_STAGE_ID = STG_TYP.VISIT_TYPE_STAGE_ID
947 AND VST_TYP.VISIT_TYPE_ID = C_VISIT_TYPE_ID) QRSLT;
948
949 BEGIN
950
951 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
952 fnd_log.string(fnd_log.level_procedure,L_DEBUG||'.begin','At the start of PLSQL procedure');
953 END IF;
954
955 OPEN GET_MAX_UPDATE_DATE(P_VISIT_TYPE_ID);
956 FETCH GET_MAX_UPDATE_DATE INTO L_MAX_UPDATE_DATE;
957 CLOSE GET_MAX_UPDATE_DATE;
958
959 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
960 fnd_log.string(fnd_log.level_procedure,L_DEBUG||'.begin','At the end of PLSQL procedure');
961 END IF;
962
963 RETURN L_MAX_UPDATE_DATE;
964
965 END Get_Last_Update_Date;
966
967 END AHL_VISIT_TYPE_PVT;