1 PACKAGE BODY AHL_UC_APPROVALS_PVT AS
2 /* $Header: AHLVUAPB.pls 120.2 2008/05/05 08:09:25 sathapli ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AHL_UC_APPROVALS_PVT';
5
6 -- To check if AHL DEBUG is turned ON
7 G_DEBUG VARCHAR2(1):=AHL_DEBUG_PUB.is_log_enabled;
8
9 ------------------------
10 -- Define Procedures --
11 ------------------------
12 --------------------------------------------------------------------------------------------
13 -- Start of Comments --
14 -- Procedure name : INITIATE_UC_APPROVALS
15 -- Type : Private
16 -- Function : This procedure is called to initiate the approval process for a Unit
17 -- Configuration, once the user submits it for Approvals.
18 -- Pre-reqs :
19 -- Parameters :
20 --
21 -- Standard IN Parameters :
22 -- p_api_version IN NUMBER Required
23 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_TRUE
24 -- p_commit IN VARCHAR2 Default FND_API.G_TRUE
25 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
26 -- Standard OUT Parameters :
27 -- x_return_status OUT VARCHAR2 Required
28 -- x_msg_count OUT NUMBER Required
29 -- x_msg_data OUT VARCHAR2 Required
30 --
31 -- INITIATE_UC_APPROVALS Parameters :
32 -- p_uc_header_id IN NUMBER Required
33 -- The header identifier of the Unit Configuration.
34 -- p_object_version_number IN NUMBER Required
35 -- The object version number of the Unit Configuration.
36 --
37 -- History:
38 -- 06/02/03 SBethi CREATED
39 --
40 -- Version :
41 -- Initial Version 1.0
42 --
43 -- End of Comments.
44 --------------------------------------------------------------------------------------------
45
46 PROCEDURE INITIATE_UC_APPROVALS
47 (
48 p_api_version IN NUMBER,
49 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
50 p_commit IN VARCHAR2 := FND_API.G_FALSE,
51 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
52 p_uc_header_id IN NUMBER,
53 p_object_version_number IN NUMBER,
54 x_return_status OUT NOCOPY VARCHAR2,
55 x_msg_count OUT NOCOPY NUMBER,
56 x_msg_data OUT NOCOPY VARCHAR2
57 )
58 IS
59 --
60 --Fetch the node detail information
61 CURSOR get_uc_header_det(c_uc_header_id in number,
62 c_object_version_number in number)
63 IS
64 SELECT unit_config_header_id, name, object_version_number,
65 unit_config_status_code, active_uc_status_code, parent_uc_header_id
66 FROM ahl_unit_config_headers
67 WHERE trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
68 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
69 AND unit_config_header_id = c_uc_header_id
70 AND object_version_number = c_object_version_number;
71
72 --
73 l_api_version CONSTANT NUMBER := 1.0;
74 l_api_name CONSTANT VARCHAR2(30) := 'INITIATE_UC_APPROVALS';
75
76 l_object VARCHAR2(30);
77 l_approval_type VARCHAR2(100):='CONCEPT';
78 l_active VARCHAR2(50):= 'N';
79 l_process_name VARCHAR2(50);
80 l_item_type VARCHAR2(50);
81
82 l_return_status VARCHAR2(50);
83 l_msg_count NUMBER;
84 l_msg_data VARCHAR2(2000);
85
86 l_activity_id NUMBER:=p_uc_header_id;
87 l_object_version_number NUMBER:=p_object_version_number;
88 l_uc_header_rec get_uc_header_det%ROWTYPE;
89
90 --
91 BEGIN
92 SAVEPOINT INITIATE_UC_APPROVALS;
93
94 -- Check if API is called in debug mode. If yes, enable debug.
95 IF G_DEBUG='Y' THEN
96 AHL_DEBUG_PUB.enable_debug;
97 AHL_DEBUG_PUB.debug( 'Enter Initiate UC Approvals');
98 END IF;
99
100 -- Initialize message list if p_init_msg_list is set to TRUE
101 IF FND_API.To_Boolean(p_init_msg_list) THEN
102 FND_MSG_PUB.Initialize;
103 END IF;
104
105 -- Initialize API return status to success
106 x_return_status := FND_API.G_RET_STS_SUCCESS;
107
108 -- Begin Processing
109 --1)Validate uc_header_id is valid
110 IF (p_uc_header_id IS NULL OR p_uc_header_id = FND_API.G_MISS_NUM) THEN
111 x_return_status := FND_API.G_RET_STS_ERROR;
112 FND_MESSAGE.set_name('AHL', 'AHL_COM_INVALID_PROCEDURE_CALL');
113 FND_MESSAGE.set_token('PROCEDURE', G_PKG_NAME);
114 FND_MSG_PUB.add;
115 RAISE FND_API.G_EXC_ERROR;
116 END IF;
117
118 --2) Fetch the uc header details
119 OPEN get_uc_header_det(p_uc_header_id, p_object_version_number);
120 FETCH get_uc_header_det into l_uc_header_rec;
121 IF (get_uc_header_det%NOTFOUND) THEN
122 fnd_message.set_name('AHL', 'AHL_UC_HEADER_ID_INVALID');
123 fnd_message.set_token('UC_HEADER_ID', p_uc_header_id, false);
124 FND_MSG_PUB.add;
125 CLOSE get_uc_header_det;
126 RAISE FND_API.G_EXC_ERROR;
127 END IF;
128 CLOSE get_uc_header_det;
129
130 --3) Make sure parent header id is null
131 IF (l_uc_header_rec.parent_uc_header_id is not null) THEN
132 fnd_message.set_name('AHL','AHL_UC_APRV_SUBUNIT');
133 FND_MSG_PUB.add;
134 RAISE FND_API.G_EXC_ERROR;
135 END IF;
136
137
138 --4) If status is draft or approval rejected
139 IF (l_uc_header_rec.unit_config_status_code = 'DRAFT' OR
140 l_uc_header_rec.unit_config_status_code = 'APPROVAL_REJECTED') THEN
141
142 l_object := 'UC';
143
144 -- Get the work Flow Process name
145 ahl_utility_pvt.get_wf_process_name(
146 p_object =>l_object,
147 x_active =>l_active,
148 x_process_name =>l_process_name,
149 x_item_type =>l_item_type,
150 x_return_status=>l_return_status,
151 x_msg_count =>l_msg_count,
152 x_msg_data =>l_msg_data);
153
154 IF G_DEBUG = 'Y' THEN
155 AHL_DEBUG_PUB.debug(' l_process_name:' || l_process_name);
156 AHL_DEBUG_PUB.debug(' l_active:' || l_active);
157 END IF;
158
159 --If the approvals WF is turned on
160 IF (l_active='Y' AND l_process_name IS NOT NULL) THEN
161
162 --Approval process started for unit_config_status_code
163 UPDATE ahl_unit_config_headers
164 SET unit_config_status_code='APPROVAL_PENDING',
165 object_version_number=object_version_number+1
166 WHERE unit_config_header_id=p_uc_header_id
167 And object_version_number=p_object_version_number;
168
169 IF sql%rowcount=0 THEN
170 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
171 FND_MSG_PUB.ADD;
172 ELSE
173
174 ahl_generic_aprv_pvt.START_WF_PROCESS(
175 P_OBJECT =>l_object,
176 P_ACTIVITY_ID =>l_activity_id,
177 P_APPROVAL_TYPE =>l_approval_type,
178 P_OBJECT_VERSION_NUMBER =>p_object_version_number+1,
179 P_ORIG_STATUS_CODE =>'DRAFT',
180 P_NEW_STATUS_CODE =>'APPROVED',
181 P_REJECT_STATUS_CODE =>'APPROVAL_REJECTED',
182 P_REQUESTER_USERID => fnd_global.user_id,
183 P_NOTES_FROM_REQUESTER =>null,
184 P_WORKFLOWPROCESS =>l_process_name,
185 P_ITEM_TYPE =>l_item_type);
186
187 END IF; --end sql%rowcount
188 ELSE
189
190 --Not active, push through to complete
191 --Approval process started for unit_config_status_code
192 UPDATE ahl_unit_config_headers
193 SET unit_config_status_code='APPROVAL_PENDING',
194 object_version_number=object_version_number+1
195 WHERE unit_config_header_id=p_uc_header_id
196 And object_version_number=p_object_version_number;
197
198 IF sql%rowcount=0 THEN
199 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
200 FND_MSG_PUB.ADD;
201 ELSE
202 -- Call the Complete UC Approval API
203 AHL_UC_APPROVALS_PVT.COMPLETE_UC_APPROVAL
204 (
205 p_api_version =>1.0,
206 p_init_msg_list =>FND_API.G_TRUE,
207 p_commit =>FND_API.G_FALSE,
208 p_validation_level =>NULL,
209 p_approval_status =>'APPROVED',
210 p_uc_header_id =>l_activity_id,
211 p_object_version_number =>l_object_version_number+1,
212 x_return_status =>x_return_status,
213 x_msg_count =>x_msg_count ,
214 x_msg_data =>x_msg_data);
215
216 END IF; --sql%COUNT
217
218 END IF;
219
220 --5) Now for the alternative WF process
221 ELSIF ((l_uc_header_rec.unit_config_status_code = 'COMPLETE' OR
222 l_uc_header_rec.unit_config_status_code = 'INCOMPLETE') AND
223 (l_uc_header_rec.active_uc_status_code = 'UNAPPROVED' OR
224 l_uc_header_rec.active_uc_status_code is null) )THEN
225
226 --Active status code WF
227 l_object := 'UC_ACTST';
228
229 -- Get the work Flow Process name
230 ahl_utility_pvt.get_wf_process_name(
231 p_object =>l_object,
232 x_active =>l_active,
233 x_process_name =>l_process_name,
234 x_item_type =>l_item_type,
235 x_return_status=>l_return_status,
236 x_msg_count =>l_msg_count,
237 x_msg_data =>l_msg_data);
238
239 IF G_DEBUG = 'Y' THEN
240 AHL_DEBUG_PUB.debug(' l_process_name:' || l_process_name);
241 AHL_DEBUG_PUB.debug(' l_active:' || l_active);
242 END IF;
243
244 --If the approvals WF is turned on
245 IF (l_active='Y' AND l_process_name IS NOT NULL) THEN
246
247 --Approval process started for active_uc_status_code
248 UPDATE ahl_unit_config_headers
249 SET active_uc_status_code='APPROVAL_PENDING',
250 object_version_number=object_version_number+1
251 WHERE unit_config_header_id=p_uc_header_id
252 And object_version_number=p_object_version_number;
253
254 IF sql%rowcount=0 THEN
255 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
256 FND_MSG_PUB.ADD;
257 ELSE
258 ahl_generic_aprv_pvt.START_WF_PROCESS(
259 P_OBJECT =>l_object,
260 P_ACTIVITY_ID =>l_activity_id,
261 P_APPROVAL_TYPE =>l_approval_type,
262 P_OBJECT_VERSION_NUMBER =>p_object_version_number+1,
263 P_ORIG_STATUS_CODE =>'UNAPPROVED',
264 P_NEW_STATUS_CODE =>'APPROVED',
265 P_REJECT_STATUS_CODE =>'UNAPPROVED',
266 P_REQUESTER_USERID =>fnd_global.user_id,
267 P_NOTES_FROM_REQUESTER =>null,
268 P_WORKFLOWPROCESS =>l_process_name,
269 P_ITEM_TYPE =>l_item_type);
270 END IF; --sql%count;
271
272 ELSE --not active, push through to complete
273
274
275 --Approval process started for active_uc_status_code
276 UPDATE ahl_unit_config_headers
277 SET active_uc_status_code='APPROVAL_PENDING',
278 object_version_number=object_version_number+1
279 WHERE unit_config_header_id=p_uc_header_id
280 And object_version_number=p_object_version_number;
281
282 IF sql%rowcount=0 THEN
283 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
284 FND_MSG_PUB.ADD;
285 ELSE
286
287 --Call the complete UC Approvals API
288 AHL_UC_APPROVALS_PVT.COMPLETE_UC_APPROVAL
289 (
290 p_api_version =>1.0,
291 p_init_msg_list =>FND_API.G_TRUE,
292 p_commit =>FND_API.G_FALSE,
293 p_validation_level =>NULL,
294 p_uc_header_id =>l_activity_id,
295 p_object_version_number =>p_object_version_number+1,
296 p_approval_status =>'APPROVED',
297 x_return_status =>x_return_status,
298 x_msg_count =>x_msg_count,
299 x_msg_data =>x_msg_data
300 );
301
302 END IF; --sql%COUNT
303 END IF; --end active_status
304
305 ELSE
306 --Not the right status to submit for approvals.
307 fnd_message.set_name('AHL','AHL_UC_APRV_IN_PROGRESS');
308 fnd_message.set_token('NAME', l_uc_header_rec.name, false);
309 FND_MSG_PUB.add;
310 RAISE FND_API.G_EXC_ERROR;
311
312 END IF;
313
314
315 l_msg_count := FND_MSG_PUB.count_msg;
316
317 IF l_msg_count > 0 THEN
318 X_msg_count := l_msg_count;
319 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
320 RAISE FND_API.G_EXC_ERROR;
321 END IF;
322
323 IF FND_API.TO_BOOLEAN(p_commit) THEN
324 COMMIT;
325 END IF;
326
327 EXCEPTION
328 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
329 ROLLBACK TO INITIATE_UC_APPROVALS;
330 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
331 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
332 p_count => x_msg_count,
333 p_data => x_msg_data);
334 IF G_DEBUG='Y' THEN
335 AHL_DEBUG_PUB.disable_debug;
336 END IF;
337
338 WHEN FND_API.G_EXC_ERROR THEN
339 ROLLBACK TO INITIATE_UC_APPROVALS;
340 X_return_status := FND_API.G_RET_STS_ERROR;
341 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
342 p_count => x_msg_count,
343 p_data => X_msg_data);
344 IF G_DEBUG='Y' THEN
345 AHL_DEBUG_PUB.disable_debug;
346 END IF;
347
348 WHEN OTHERS THEN
349 ROLLBACK TO INITIATE_UC_APPROVALS;
350 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
351 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
352 THEN
353 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
354 p_procedure_name => l_api_name,
355 p_error_text => SUBSTR(SQLERRM,1,240));
356 END IF;
357 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
358 p_count => x_msg_count,
359 p_data => X_msg_data);
360 IF G_DEBUG='Y' THEN
361 AHL_DEBUG_PUB.disable_debug;
362 END IF;
363
364 END INITIATE_UC_APPROVALS;
365
366 --------------------------------------------------------------------------------------------
367 -- Start of Comments --
368 -- Procedure name : COMPLETE_UC_APPROVAL
369 -- Type : Private
370 -- Function : This procedure is called internally to complete the Approval Process.
371 --
372 -- Pre-reqs :
373 -- Parameters :
374 --
375 -- Standard IN Parameters :
376 -- p_api_version IN NUMBER Required
377 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_TRUE
378 -- p_commit IN VARCHAR2 Default FND_API.G_TRUE
379 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
380 -- Standard OUT Parameters :
381 -- x_return_status OUT VARCHAR2 Required
382 -- x_msg_count OUT NUMBER Required
383 -- x_msg_data OUT VARCHAR2 Required
384 --
385 -- INITIATE_UC_APPROVALS Parameters :
386 -- p_uc_header_id IN NUMBER Required
387 -- The header identifier of the Unit Configuration.
388 -- p_object_version_number IN NUMBER Required
389 -- The object version number of the Unit Configuration.
390 -- p_approval_status IN VARCHAR2 Required
391 -- The approval status of the Unit Configuration after the approval process
392 --
393 -- History:
394 -- 06/02/03 SBethi CREATED
395 --
396 -- Version :
397 -- Initial Version 1.0
398 --
399 -- End of Comments.
400 --------------------------------------------------------------------------------------------
401 PROCEDURE COMPLETE_UC_APPROVAL(
402 p_api_version IN NUMBER,
403 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
404 p_commit IN VARCHAR2 := FND_API.G_FALSE,
405 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
406 p_uc_header_id IN NUMBER,
407 p_object_version_number IN NUMBER,
408 p_approval_status IN VARCHAR2,
409 x_return_status OUT NOCOPY VARCHAR2,
410 x_msg_count OUT NOCOPY NUMBER,
411 x_msg_data OUT NOCOPY VARCHAR2
412
413 )
414 IS
415
416 l_api_version CONSTANT NUMBER := 1.0;
417 l_api_name CONSTANT VARCHAR2(30) := 'COMPLETE_UC_APPROVAL';
418
419 CURSOR get_uc_header_det(c_uc_header_id in number)
420 IS
421 SELECT unit_config_header_id, name, object_version_number, unit_config_status_code, active_uc_status_code
422 FROM ahl_unit_config_headers
423 WHERE trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
424 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
425 AND unit_config_header_id = c_uc_header_id;
426
427 l_uc_header_rec get_uc_header_det%ROWTYPE;
428 l_status VARCHAR2(30);
429 l_evaluation_status VARCHAR2(1);
430
431 l_return_status VARCHAR2(50);
432 l_msg_count NUMBER;
433 l_msg_data VARCHAR2(2000);
434
435 -- SATHAPLI::Bug 7018042, 05-May-2008
436 l_uc_status VARCHAR2(30);
437
438 BEGIN
439
440 SAVEPOINT COMPLETE_UC_APPROVAL;
441
442 -- Check if API is called in debug mode. If yes, enable debug.
443 IF G_DEBUG='Y' THEN
444 AHL_DEBUG_PUB.enable_debug;
445 AHL_DEBUG_PUB.debug( 'Enter Complete UC Approvals');
446 END IF;
447
448 -- Initialize message list if p_init_msg_list is set to TRUE
449 IF FND_API.To_Boolean(p_init_msg_list) THEN
450 FND_MSG_PUB.Initialize;
451 END IF;
452
453 -- Initialize API return status to success
454 x_return_status := FND_API.G_RET_STS_SUCCESS;
455
456 -- Begin Processing
457 --1) Validate uc_header_id
458 IF (p_uc_header_id IS NULL OR p_uc_header_id = FND_API.G_MISS_NUM) THEN
459 x_return_status := FND_API.G_RET_STS_ERROR;
460 FND_MESSAGE.set_name('AHL', 'AHL_COM_INVALID_PROCEDURE_CALL');
461 FND_MESSAGE.set_token('PROCEDURE', G_PKG_NAME);
462 FND_MSG_PUB.add;
463 RAISE FND_API.G_EXC_ERROR;
464 END IF;
465
466 --2) Validate uc_header_id
467 OPEN get_uc_header_det(p_uc_header_id);
468 FETCH get_uc_header_det into l_uc_header_rec;
469 IF (get_uc_header_det%NOTFOUND) THEN
470 fnd_message.set_name('AHL', 'AHL_UC_HEADER_ID_INVALID');
471 fnd_message.set_token('UC_HEADER_ID', p_uc_header_id, false);
472 FND_MSG_PUB.add;
473 CLOSE get_uc_header_det;
474 RAISE FND_API.G_EXC_ERROR;
475 END IF;
476 CLOSE get_uc_header_det;
477
478
479 IF ( l_uc_header_rec.unit_config_status_code ='APPROVAL_PENDING' ) THEN
480
481 IF( p_approval_status='APPROVED' ) THEN
482
483 IF G_DEBUG='Y' THEN
484 AHL_DEBUG_PUB.debug( 'UC: AHL_UC_APPROVALS_PVT.complete_uc_approval--> approval_status=APPROVED');
485 END IF;
486
487 --call the completeness check API
488 AHL_UC_VALIDATION_PUB.check_completeness(
489 p_api_version => 1.0,
490 p_init_msg_list => FND_API.G_TRUE,
491 p_commit => FND_API.G_FALSE,
492 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
493 p_unit_header_id => p_uc_header_id,
494 x_evaluation_status => l_evaluation_status,
495 x_return_status => l_return_status,
496 x_msg_count => l_msg_count,
497 x_msg_data => l_msg_data
498 );
499
500 IF G_DEBUG='Y' THEN
501 AHL_DEBUG_PUB.debug('SQLERRM' || SQLERRM );
502 AHL_DEBUG_PUB.debug('l_return_status' || l_return_status);
503 AHL_DEBUG_PUB.debug('l_msg_count' || l_msg_count);
504 AHL_DEBUG_PUB.debug('l_msg_data' || l_msg_data);
505 AHL_DEBUG_PUB.debug( 'UC: AHL_UC_APPROVALS_PVT.complete_uc_approval-->After Completeness Check API call');
506 END IF;
507
508 l_msg_count := FND_MSG_PUB.count_msg;
509 IF l_msg_count > 0 THEN
510 AHL_DEBUG_PUB.debug('FAiled Check Completeness API');
511 x_msg_count := l_msg_count;
512 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
513 END IF;
514
515 IF ( l_evaluation_status = 'T' ) THEN
516 l_status := 'COMPLETE';
517 ELSE
518 l_status := 'INCOMPLETE';
519 END IF;
520
521 --update table and write to history table
522 UPDATE ahl_unit_config_headers
523 SET unit_config_status_code=l_status,
524 active_uc_status_code='APPROVED',
525 object_version_number=object_version_number+1
526 WHERE unit_config_header_id=p_uc_header_id;
527
528 AHL_UTIL_UC_PKG.COPY_UC_HEADER_TO_HISTORY(p_uc_header_id, x_return_status);
529
530 ELSE
531
532 l_status := 'APPROVAL_REJECTED';
533 --update table
534 UPDATE ahl_unit_config_headers
535 SET unit_config_status_code=l_status,
536 object_version_number=object_version_number+1
537 WHERE unit_config_header_id=p_uc_header_id;
538
539 END IF; --p_approval_status
540
541 ELSIF ( l_uc_header_rec.active_uc_status_code ='APPROVAL_PENDING' ) THEN
542
543 IF( p_approval_status='APPROVED' ) THEN
544 l_status:='APPROVED';
545 ELSE
546 l_status:='UNAPPROVED';
547 END IF; --p_approval_status
548
549 -- SATHAPLI::Bug 7018042, 05-May-2008, Fix start
550 -- The UC is in status 'Complete' or 'Incomplete'. Check for the completeness and update the status accordingly.
551 AHL_UC_VALIDATION_PUB.check_completeness(
552 p_api_version => 1.0,
553 p_init_msg_list => FND_API.G_TRUE,
554 p_commit => FND_API.G_FALSE,
555 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
556 p_unit_header_id => p_uc_header_id,
557 x_evaluation_status => l_evaluation_status,
558 x_return_status => l_return_status,
559 x_msg_count => l_msg_count,
560 x_msg_data => l_msg_data
561 );
562
563 -- Set the UC status in l_uc_status based on the above API call
564 IF ( l_evaluation_status = 'T' ) THEN
565 l_uc_status := 'COMPLETE';
566 ELSE
567 l_uc_status := 'INCOMPLETE';
568 END IF;
569
570 --update the active_uc_status_code column and copy to history
571 -- Update the unit_config_status_code column as well.
572 UPDATE ahl_unit_config_headers
573 SET active_uc_status_code=l_status
574 ,unit_config_status_code = l_uc_status
575 WHERE unit_config_header_id=p_uc_header_id;
576 -- SATHAPLI::Bug 7018042, 05-May-2008, Fix end
577
578 AHL_UTIL_UC_PKG.COPY_UC_HEADER_TO_HISTORY(p_uc_header_id, x_return_status);
579
580 END IF; --uc_status_codes
581
582 --End Processing
583
584 l_msg_count := FND_MSG_PUB.count_msg;
585
586 IF l_msg_count > 0 THEN
587 X_msg_count := l_msg_count;
588 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
589 RAISE FND_API.G_EXC_ERROR;
590 END IF;
591
592
593 IF FND_API.TO_BOOLEAN(p_commit) THEN
594 COMMIT;
595 END IF;
596
597 EXCEPTION
598 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
599 ROLLBACK TO COMPLETE_UC_APPROVAL;
600 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
601 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
602 p_count => x_msg_count,
603 p_data => x_msg_data);
604 IF G_DEBUG='Y' THEN
605 AHL_DEBUG_PUB.disable_debug;
606 END IF;
607
608
609 WHEN FND_API.G_EXC_ERROR THEN
610 ROLLBACK TO COMPLETE_UC_APPROVAL;
611 X_return_status := FND_API.G_RET_STS_ERROR;
612 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
613 p_count => x_msg_count,
614 p_data => X_msg_data);
615 IF G_DEBUG='Y' THEN
616 AHL_DEBUG_PUB.disable_debug;
617 END IF;
618
619 WHEN OTHERS THEN
620 ROLLBACK TO COMPLETE_UC_APPROVAL;
621 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
622 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
623 THEN
624 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
625 p_procedure_name => l_api_name,
626 p_error_text => SUBSTR(SQLERRM,1,240));
627 END IF;
628 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
629 p_count => x_msg_count,
630 p_data => X_msg_data);
631 IF G_DEBUG='Y' THEN
632 AHL_DEBUG_PUB.disable_debug;
633 END IF;
634
635 END COMPLETE_UC_APPROVAL;
636
637
638
639 --------------------------------------------------------------------------------------------
640 -- Start of Comments --
641 -- Procedure name : INITIATE_QUARANTINE
642 -- Type : Private
643 -- Function : This procedure is called to initiate the approval process for a Unit
644 -- Configuration Quarantine, once the user submits it for Approvals.
645 -- Pre-reqs :
646 -- Parameters :
647 --
648 -- Standard IN Parameters :
649 -- p_api_version IN NUMBER Required
650 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_TRUE
651 -- p_commit IN VARCHAR2 Default FND_API.G_TRUE
652 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
653 -- Standard OUT Parameters :
654 -- x_return_status OUT VARCHAR2 Required
655 -- x_msg_count OUT NUMBER Required
656 -- x_msg_data OUT VARCHAR2 Required
657 --
658 -- INITIATE_QUARANTINE Parameters :
659 -- p_uc_header_id IN NUMBER Required
660 -- The header identifier of the Unit Configuration.
661 -- p_object_version_number IN NUMBER Required
662 -- The object version number of the Unit Configuration.
663 --
664 -- History:
665 --
666 -- Version :
667 -- Initial Version 1.0
668 --
669 -- End of Comments.
670 --------------------------------------------------------------------------------------------
671
672 PROCEDURE INITIATE_QUARANTINE
673 (
674 p_api_version IN NUMBER,
675 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
676 p_commit IN VARCHAR2 := FND_API.G_FALSE,
677 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
678 p_uc_header_id IN NUMBER,
679 p_object_version_number IN NUMBER,
680 x_return_status OUT NOCOPY VARCHAR2,
681 x_msg_count OUT NOCOPY NUMBER,
682 x_msg_data OUT NOCOPY VARCHAR2
683 )
684 IS
685
686 --Fetch the node detail information
687 CURSOR get_uc_header_det(c_uc_header_id in number,
688 c_object_version_number in number)
689 IS
690 SELECT unit_config_header_id,
691 name,
692 object_version_number,
693 unit_config_status_code,
694 active_uc_status_code,
695 parent_uc_header_id
696 FROM ahl_unit_config_headers
697 WHERE trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
698 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
699 AND unit_config_header_id = c_uc_header_id
700 AND object_version_number = c_object_version_number;
701
702 l_api_version CONSTANT NUMBER := 1.0;
703 l_api_name CONSTANT VARCHAR2(30) := 'INITIATE_QUARANTINE';
704
705 l_object VARCHAR2(30);
706 l_approval_type VARCHAR2(100):='CONCEPT';
707 l_active VARCHAR2(50):= 'N';
708 l_process_name VARCHAR2(50);
709 l_item_type VARCHAR2(50);
710
711 l_return_status VARCHAR2(50);
712 l_msg_count NUMBER;
713 l_msg_data VARCHAR2(2000);
714
715 l_activity_id NUMBER:=p_uc_header_id;
716 l_object_version_number NUMBER:=p_object_version_number;
717 l_uc_header_rec get_uc_header_det%ROWTYPE;
718
719 BEGIN
720 SAVEPOINT INITIATE_QUARANTINE_SP;
721
722 -- Check if API is called in debug mode. If yes, enable debug.
723 IF G_DEBUG='Y' THEN
724 AHL_DEBUG_PUB.enable_debug;
725 AHL_DEBUG_PUB.debug( 'Enter Initiate UC-ACL Quarantine Approvals');
726 END IF;
727
728 -- Initialize message list if p_init_msg_list is set to TRUE
729 IF FND_API.To_Boolean(p_init_msg_list) THEN
730 FND_MSG_PUB.Initialize;
731 END IF;
732
733 -- Initialize API return status to success
734 x_return_status := FND_API.G_RET_STS_SUCCESS;
735
736 -- Begin Processing
737 -- Validate uc_header_id is valid
738 IF (p_uc_header_id IS NULL OR p_uc_header_id = FND_API.G_MISS_NUM) THEN
739 x_return_status := FND_API.G_RET_STS_ERROR;
740 FND_MESSAGE.set_name('AHL', 'AHL_COM_INVALID_PROCEDURE_CALL');
741 FND_MESSAGE.set_token('PROCEDURE', G_PKG_NAME);
742 FND_MSG_PUB.add;
743 RAISE FND_API.G_EXC_ERROR;
744 END IF;
745
746 -- Fetch the uc header details
747 OPEN get_uc_header_det(p_uc_header_id, p_object_version_number);
748 FETCH get_uc_header_det into l_uc_header_rec;
749 IF (get_uc_header_det%NOTFOUND) THEN
750 fnd_message.set_name('AHL', 'AHL_UC_HEADER_ID_INVALID');
751 fnd_message.set_token('UC_HEADER_ID', p_uc_header_id, false);
752 FND_MSG_PUB.add;
753 CLOSE get_uc_header_det;
754 RAISE FND_API.G_EXC_ERROR;
755 END IF;
756 CLOSE get_uc_header_det;
757
758 -- Make sure parent header id is null
759 IF (l_uc_header_rec.parent_uc_header_id is not null) THEN
760 fnd_message.set_name('AHL','AHL_UC_APRV_SUBUNIT');
761 FND_MSG_PUB.add;
762 RAISE FND_API.G_EXC_ERROR;
763 END IF;
764
765 IF G_DEBUG='Y' THEN
766 AHL_DEBUG_PUB.debug( 'l_uc_header_rec.active_uc_status_code : '||l_uc_header_rec.active_uc_status_code);
767 AHL_DEBUG_PUB.debug( 'l_uc_header_rec.active_uc_status_code : '||l_uc_header_rec.unit_config_status_code);
768 END IF;
769
770 -- Workflow process to be started only if status is APPROVED.
771 IF ((l_uc_header_rec.active_uc_status_code = 'APPROVED') AND
772 (l_uc_header_rec.unit_config_status_code IN ('COMPLETE','INCOMPLETE'))) THEN
773
774 l_object := 'UC_ACL';
775
776 -- Get the work Flow Process name
777 ahl_utility_pvt.get_wf_process_name(
778 p_object =>l_object,
779 x_active =>l_active,
780 x_process_name =>l_process_name,
781 x_item_type =>l_item_type,
782 x_return_status=>l_return_status,
783 x_msg_count =>l_msg_count,
784 x_msg_data =>l_msg_data);
785
786 IF G_DEBUG = 'Y' THEN
787 AHL_DEBUG_PUB.debug(' l_process_name:' || l_process_name);
788 AHL_DEBUG_PUB.debug(' l_active:' || l_active);
789 END IF;
790
791 -- Check if the approvals WF is turned on
792 IF (l_active='Y' AND l_process_name IS NOT NULL) THEN
793
794 --Approval process started for unit_config_status_code
795 UPDATE ahl_unit_config_headers
796 SET active_uc_status_code='APPROVAL_PENDING',
797 unit_config_status_code='QUARANTINE',
798 object_version_number=object_version_number+1
799 WHERE unit_config_header_id=p_uc_header_id
800 AND object_version_number=p_object_version_number;
801
802 IF sql%rowcount=0 THEN
803 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
804 FND_MSG_PUB.ADD;
805 -- To be verified if Error is supposed to be raised here or not.
806 ELSE
807
808 ahl_generic_aprv_pvt.START_WF_PROCESS(P_OBJECT => l_object,
809 P_ACTIVITY_ID => l_activity_id,
810 P_APPROVAL_TYPE => l_approval_type,
811 P_OBJECT_VERSION_NUMBER => p_object_version_number+1,
812 P_ORIG_STATUS_CODE => 'APPROVED',
813 P_NEW_STATUS_CODE => 'APPROVED',
814 P_REJECT_STATUS_CODE => 'APPROVAL_REJECTED',
815 P_REQUESTER_USERID => fnd_global.user_id,
816 P_NOTES_FROM_REQUESTER => null,
817 P_WORKFLOWPROCESS => l_process_name,
818 P_ITEM_TYPE => l_item_type);
819
820 END IF; --end sql%rowcount
821
822 ELSE -- Workflow process is not active, push through to complete
823
824 --Approval process started for unit_config_status_code
825 UPDATE ahl_unit_config_headers
826 SET active_uc_status_code = 'APPROVAL_PENDING',
827 unit_config_status_code='QUARANTINE',
828 object_version_number=object_version_number+1
829 WHERE unit_config_header_id=p_uc_header_id
830 And object_version_number=p_object_version_number;
831
832 IF sql%rowcount=0 THEN
833 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
834 FND_MSG_PUB.ADD;
835 -- To be verified if Error is supposed to be raised here or not.
836 ELSE
837 -- Call the Complete UC Approval API
838 AHL_UC_APPROVALS_PVT.COMPLETE_QUARANTINE_APPROVAL(p_api_version =>1.0,
839 p_init_msg_list =>FND_API.G_TRUE,
840 p_commit =>FND_API.G_FALSE,
841 p_validation_level =>NULL,
842 p_approval_status =>'APPROVED',
843 p_uc_header_id =>l_activity_id,
844 p_object_version_number =>l_object_version_number+1,
845 x_return_status =>x_return_status,
846 x_msg_count =>x_msg_count ,
847 x_msg_data =>x_msg_data);
848 END IF; --sql%COUNT
849 END IF; -- Active Workflow Check
850 ELSE --Not the right status to submit for approvals.
851 fnd_message.set_name('AHL','AHL_UC_APRV_IN_PROGRESS');
852 fnd_message.set_token('UNIT_NAME', l_uc_header_rec.name, false);
853 FND_MSG_PUB.add;
854 RAISE FND_API.G_EXC_ERROR;
855 END IF; -- Active Status Check.
856
857 l_msg_count := FND_MSG_PUB.count_msg;
858
859 IF l_msg_count > 0 THEN
860 X_msg_count := l_msg_count;
861 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
862 RAISE FND_API.G_EXC_ERROR;
863 END IF;
864
865 IF FND_API.TO_BOOLEAN(p_commit) THEN
866 COMMIT;
867 END IF;
868
869 EXCEPTION
870 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
871 ROLLBACK TO INITIATE_QUARANTINE_SP;
872 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
873 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
874 p_count => x_msg_count,
875 p_data => x_msg_data);
876 IF G_DEBUG='Y' THEN
877 AHL_DEBUG_PUB.disable_debug;
878 END IF;
879
880 WHEN FND_API.G_EXC_ERROR THEN
881 ROLLBACK TO INITIATE_QUARANTINE_SP;
882 X_return_status := FND_API.G_RET_STS_ERROR;
883 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
884 p_count => x_msg_count,
885 p_data => X_msg_data);
886 IF G_DEBUG='Y' THEN
887 AHL_DEBUG_PUB.disable_debug;
888 END IF;
889
890 WHEN OTHERS THEN
891 ROLLBACK TO INITIATE_QUARANTINE_SP;
892 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
893 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
894 THEN
895 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
896 p_procedure_name => l_api_name,
897 p_error_text => SUBSTR(SQLERRM,1,240));
898 END IF;
899 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
900 p_count => x_msg_count,
901 p_data => X_msg_data);
902 IF G_DEBUG='Y' THEN
903 AHL_DEBUG_PUB.disable_debug;
904 END IF;
905
906 END INITIATE_QUARANTINE;
907
908
909 --------------------------------------------------------------------------------------------
910 -- Start of Comments --
911 -- Procedure name : INITIATE_DEACTIVATE_QUARANTINE
912 -- Type : Private
913 -- Function : This procedure is called to initiate the approval process for a Unit
914 -- Configuration deactivate Quarantine, once the user submits it for Approvals.
915 -- Pre-reqs :
916 -- Parameters :
917 --
918 -- Standard IN Parameters :
919 -- p_api_version IN NUMBER Required
920 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_TRUE
921 -- p_commit IN VARCHAR2 Default FND_API.G_TRUE
922 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
923 -- Standard OUT Parameters :
924 -- x_return_status OUT VARCHAR2 Required
925 -- x_msg_count OUT NUMBER Required
926 -- x_msg_data OUT VARCHAR2 Required
927 --
928 -- INITIATE_DEACTIVATE_QUARANTINE Parameters :
929 -- p_uc_header_id IN NUMBER Required
930 -- The header identifier of the Unit Configuration.
931 -- p_object_version_number IN NUMBER Required
932 -- The object version number of the Unit Configuration.
933 --
934 -- History:
935 --
936 -- Version :
937 -- Initial Version 1.0
938 --
939 -- End of Comments.
940 --------------------------------------------------------------------------------------------
941
942 PROCEDURE INITIATE_DEACTIVATE_QUARANTINE
943 (
944 p_api_version IN NUMBER,
945 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
946 p_commit IN VARCHAR2 := FND_API.G_FALSE,
947 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
948 p_uc_header_id IN NUMBER,
949 p_object_version_number IN NUMBER,
950 x_return_status OUT NOCOPY VARCHAR2,
951 x_msg_count OUT NOCOPY NUMBER,
952 x_msg_data OUT NOCOPY VARCHAR2
953 )
954 IS
955
956 --Fetch the node detail information
957 CURSOR get_uc_header_det(c_uc_header_id in number,
958 c_object_version_number in number)
959 IS
960 SELECT unit_config_header_id,
961 name,
962 object_version_number,
963 unit_config_status_code,
964 active_uc_status_code,
965 parent_uc_header_id
966 FROM ahl_unit_config_headers
967 WHERE trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
968 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
969 AND unit_config_header_id = c_uc_header_id
970 AND object_version_number = c_object_version_number;
971
972 l_api_version CONSTANT NUMBER := 1.0;
973 l_api_name CONSTANT VARCHAR2(30) := 'INITIATE_DEACTIVATE_QUARANTINE';
974
975 l_object VARCHAR2(30);
976 l_approval_type VARCHAR2(100):='CONCEPT';
977 l_active VARCHAR2(50):= 'N';
978 l_process_name VARCHAR2(50);
979 l_item_type VARCHAR2(50);
980
981 l_return_status VARCHAR2(50);
982 l_msg_count NUMBER;
983 l_msg_data VARCHAR2(2000);
984
985 l_activity_id NUMBER:=p_uc_header_id;
986 l_object_version_number NUMBER:=p_object_version_number;
987 l_uc_header_rec get_uc_header_det%ROWTYPE;
988
989 BEGIN
990 SAVEPOINT INITIATE_QUARANTINE_SP;
991
992 -- Check if API is called in debug mode. If yes, enable debug.
993 IF G_DEBUG='Y' THEN
994 AHL_DEBUG_PUB.enable_debug;
995 AHL_DEBUG_PUB.debug( 'Enter Initiate UC-ACL Deactivate Quarantine Approvals');
996 END IF;
997
998 -- Initialize message list if p_init_msg_list is set to TRUE
999 IF FND_API.To_Boolean(p_init_msg_list) THEN
1000 FND_MSG_PUB.Initialize;
1001 END IF;
1002
1003 -- Initialize API return status to success
1004 x_return_status := FND_API.G_RET_STS_SUCCESS;
1005
1006 -- Begin Processing
1007 -- Validate uc_header_id is valid
1008 IF (p_uc_header_id IS NULL OR p_uc_header_id = FND_API.G_MISS_NUM) THEN
1009 x_return_status := FND_API.G_RET_STS_ERROR;
1010 FND_MESSAGE.set_name('AHL', 'AHL_COM_INVALID_PROCEDURE_CALL');
1011 FND_MESSAGE.set_token('PROCEDURE', G_PKG_NAME);
1012 FND_MSG_PUB.add;
1013 RAISE FND_API.G_EXC_ERROR;
1014 END IF;
1015
1016 -- Fetch the uc header details
1017 OPEN get_uc_header_det(p_uc_header_id, p_object_version_number);
1018 FETCH get_uc_header_det into l_uc_header_rec;
1019 IF (get_uc_header_det%NOTFOUND) THEN
1020 fnd_message.set_name('AHL', 'AHL_UC_HEADER_ID_INVALID');
1021 fnd_message.set_token('UC_HEADER_ID', p_uc_header_id, false);
1022 FND_MSG_PUB.add;
1023 CLOSE get_uc_header_det;
1024 RAISE FND_API.G_EXC_ERROR;
1025 END IF;
1026 CLOSE get_uc_header_det;
1027
1028 -- Make sure parent header id is null
1029 IF (l_uc_header_rec.parent_uc_header_id is not null) THEN
1030 fnd_message.set_name('AHL','AHL_UC_APRV_SUBUNIT');
1031 FND_MSG_PUB.add;
1032 RAISE FND_API.G_EXC_ERROR;
1033 END IF;
1034
1035 -- Workflow process to be started only if status is APPROVED.
1036 IF ((l_uc_header_rec.active_uc_status_code = 'APPROVED') AND
1037 (l_uc_header_rec.unit_config_status_code = 'QUARANTINE')) THEN
1038
1039 l_object := 'UC_ACL';
1040
1041 -- Get the work Flow Process name
1042 ahl_utility_pvt.get_wf_process_name(
1043 p_object =>l_object,
1044 x_active =>l_active,
1045 x_process_name =>l_process_name,
1046 x_item_type =>l_item_type,
1047 x_return_status=>l_return_status,
1048 x_msg_count =>l_msg_count,
1049 x_msg_data =>l_msg_data);
1050
1051 IF G_DEBUG = 'Y' THEN
1052 AHL_DEBUG_PUB.debug(' l_process_name:' || l_process_name);
1053 AHL_DEBUG_PUB.debug(' l_active:' || l_active);
1054 END IF;
1055
1056 -- Check if the approvals WF is turned on
1057 IF (l_active='Y' AND l_process_name IS NOT NULL) THEN
1058
1059 --Approval process started for unit_config_status_code
1060 UPDATE ahl_unit_config_headers
1061 SET active_uc_status_code='APPROVAL_PENDING',
1062 unit_config_status_code='DEACTIVATE_QUARANTINE',
1063 object_version_number=object_version_number+1
1064 WHERE unit_config_header_id=p_uc_header_id
1065 AND object_version_number=p_object_version_number;
1066
1067 IF sql%rowcount=0 THEN
1068 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
1069 FND_MSG_PUB.ADD;
1070 -- To be verified if Error is supposed to be raised here or not.
1071 ELSE
1072
1073 ahl_generic_aprv_pvt.START_WF_PROCESS(P_OBJECT => l_object,
1074 P_ACTIVITY_ID => l_activity_id,
1075 P_APPROVAL_TYPE => l_approval_type,
1076 P_OBJECT_VERSION_NUMBER => p_object_version_number+1,
1077 P_ORIG_STATUS_CODE => 'APPROVED',
1078 P_NEW_STATUS_CODE => 'APPROVED',
1079 P_REJECT_STATUS_CODE => 'APPROVAL_REJECTED',
1080 P_REQUESTER_USERID => fnd_global.user_id,
1081 P_NOTES_FROM_REQUESTER => null,
1082 P_WORKFLOWPROCESS => l_process_name,
1083 P_ITEM_TYPE => l_item_type);
1084
1085 END IF; --end sql%rowcount
1086
1087 ELSE -- Workflow process is not active, push through to complete
1088
1089 --Approval process started for unit_config_status_code
1090 UPDATE ahl_unit_config_headers
1091 SET active_uc_status_code = 'APPROVAL_PENDING',
1092 unit_config_status_code='DEACTIVATE_QUARANTINE',
1093 object_version_number=object_version_number+1
1094 WHERE unit_config_header_id=p_uc_header_id
1095 And object_version_number=p_object_version_number;
1096
1097 IF sql%rowcount=0 THEN
1098 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
1099 FND_MSG_PUB.ADD;
1100 -- To be verified if Error is supposed to be raised here or not.
1101 ELSE
1102 -- Call the Complete UC Approval API
1103 AHL_UC_APPROVALS_PVT.COMPLETE_QUARANTINE_APPROVAL(p_api_version =>1.0,
1104 p_init_msg_list =>FND_API.G_TRUE,
1105 p_commit =>FND_API.G_FALSE,
1106 p_validation_level =>NULL,
1107 p_approval_status =>'APPROVED',
1108 p_uc_header_id =>l_activity_id,
1109 p_object_version_number =>l_object_version_number+1,
1110 x_return_status =>x_return_status,
1111 x_msg_count =>x_msg_count ,
1112 x_msg_data =>x_msg_data);
1113 END IF; --sql%COUNT
1114 END IF; -- Active Workflow Check
1115 ELSE --Not the right status to submit for approvals.
1116 fnd_message.set_name('AHL','AHL_UC_APRV_IN_PROGRESS');
1117 fnd_message.set_token('UNIT_NAME', l_uc_header_rec.name, false);
1118 FND_MSG_PUB.add;
1119 RAISE FND_API.G_EXC_ERROR;
1120 END IF; -- Active Status Check.
1121
1122 l_msg_count := FND_MSG_PUB.count_msg;
1123
1124 IF l_msg_count > 0 THEN
1125 X_msg_count := l_msg_count;
1126 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1127 RAISE FND_API.G_EXC_ERROR;
1128 END IF;
1129
1130 IF FND_API.TO_BOOLEAN(p_commit) THEN
1131 COMMIT;
1132 END IF;
1133
1134 EXCEPTION
1135 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1136 ROLLBACK TO INITIATE_QUARANTINE_SP;
1137 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1138 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
1139 p_count => x_msg_count,
1140 p_data => x_msg_data);
1141 IF G_DEBUG='Y' THEN
1142 AHL_DEBUG_PUB.disable_debug;
1143 END IF;
1144
1145 WHEN FND_API.G_EXC_ERROR THEN
1146 ROLLBACK TO INITIATE_QUARANTINE_SP;
1147 X_return_status := FND_API.G_RET_STS_ERROR;
1148 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
1149 p_count => x_msg_count,
1150 p_data => X_msg_data);
1151 IF G_DEBUG='Y' THEN
1152 AHL_DEBUG_PUB.disable_debug;
1153 END IF;
1154
1155 WHEN OTHERS THEN
1156 ROLLBACK TO INITIATE_QUARANTINE_SP;
1157 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1158 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1159 THEN
1160 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
1161 p_procedure_name => l_api_name,
1162 p_error_text => SUBSTR(SQLERRM,1,240));
1163 END IF;
1164 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
1165 p_count => x_msg_count,
1166 p_data => X_msg_data);
1167 IF G_DEBUG='Y' THEN
1168 AHL_DEBUG_PUB.disable_debug;
1169 END IF;
1170
1171 END INITIATE_DEACTIVATE_QUARANTINE;
1172
1173 --------------------------------------------------------------------------------------------
1174 -- Start of Comments --
1175 -- Procedure name : COMPLETE_QUARANTINE_APPROVAL
1176 -- Type : Private
1177 -- Function : This procedure is called internally to complete the Approval Process.
1178 --
1179 -- Pre-reqs :
1180 -- Parameters :
1181 --
1182 -- Standard IN Parameters :
1183 -- p_api_version IN NUMBER Required
1184 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_TRUE
1185 -- p_commit IN VARCHAR2 Default FND_API.G_TRUE
1186 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
1187 -- Standard OUT Parameters :
1188 -- x_return_status OUT VARCHAR2 Required
1189 -- x_msg_count OUT NUMBER Required
1190 -- x_msg_data OUT VARCHAR2 Required
1191 --
1192 -- COMPLETE_QUARANTINE_APPROVAL Parameters :
1193 -- p_uc_header_id IN NUMBER Required
1194 -- The header identifier of the Unit Configuration.
1195 -- p_object_version_number IN NUMBER Required
1196 -- The object version number of the Unit Configuration.
1197 -- p_approval_status IN VARCHAR2 Required
1198 -- The approval status of the Unit Configuration after the approval process
1199 --
1200 -- History:
1201 --
1202 -- Version :
1203 -- Initial Version 1.0
1204 --
1205 -- End of Comments.
1206 --------------------------------------------------------------------------------------------
1207 PROCEDURE COMPLETE_QUARANTINE_APPROVAL(
1208 p_api_version IN NUMBER,
1209 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
1210 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1211 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1212 p_uc_header_id IN NUMBER,
1213 p_object_version_number IN NUMBER,
1214 p_approval_status IN VARCHAR2,
1215 x_return_status OUT NOCOPY VARCHAR2,
1216 x_msg_count OUT NOCOPY NUMBER,
1217 x_msg_data OUT NOCOPY VARCHAR2
1218 )
1219 IS
1220
1221 l_api_version CONSTANT NUMBER := 1.0;
1222 l_api_name CONSTANT VARCHAR2(30) := 'COMPLETE_QUARANTINE_APPROVAL';
1223
1224 CURSOR get_uc_header_det(c_uc_header_id in number)
1225 IS
1226 SELECT unit_config_header_id,
1227 name,
1228 object_version_number,
1229 unit_config_status_code,
1230 active_uc_status_code
1231 FROM ahl_unit_config_headers
1232 WHERE trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
1233 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
1234 AND unit_config_header_id = c_uc_header_id;
1235
1236 l_uc_header_rec get_uc_header_det%ROWTYPE;
1237 l_status VARCHAR2(30);
1238 l_evaluation_status VARCHAR2(1);
1239
1240 l_return_status VARCHAR2(50);
1241 l_msg_count NUMBER;
1242 l_msg_data VARCHAR2(2000);
1243
1244
1245 BEGIN
1246 SAVEPOINT COMPLETE_Q_APPROVAL_SP;
1247
1248 -- Check if API is called in debug mode. If yes, enable debug.
1249 IF G_DEBUG='Y' THEN
1250 AHL_DEBUG_PUB.enable_debug;
1251 AHL_DEBUG_PUB.debug( 'Enter Complete UC Approvals');
1252 END IF;
1253
1254 -- Initialize message list if p_init_msg_list is set to TRUE
1255 IF FND_API.To_Boolean(p_init_msg_list) THEN
1256 FND_MSG_PUB.Initialize;
1257 END IF;
1258
1259 -- Initialize API return status to success
1260 x_return_status := FND_API.G_RET_STS_SUCCESS;
1261
1262 IF G_DEBUG='Y' THEN
1263 AHL_DEBUG_PUB.debug( 'UC: AHL_UC_APPROVALS_PVT.complete_uc_approval--> check for Valid UC');
1264 END IF;
1265
1266 -- Begin Processing
1267 --1) Validate if uc_header_id is passed.
1268 IF (p_uc_header_id IS NULL OR p_uc_header_id = FND_API.G_MISS_NUM) THEN
1269 x_return_status := FND_API.G_RET_STS_ERROR;
1270 FND_MESSAGE.set_name('AHL', 'AHL_COM_INVALID_PROCEDURE_CALL');
1271 FND_MESSAGE.set_token('PROCEDURE', G_PKG_NAME);
1272 FND_MSG_PUB.add;
1273 RAISE FND_API.G_EXC_ERROR;
1274 END IF;
1275
1276 IF G_DEBUG='Y' THEN
1277 AHL_DEBUG_PUB.debug( 'UC: AHL_UC_APPROVALS_PVT.complete_uc_approval--> UC is Valid 1 ');
1278 END IF;
1279
1280 --2) Validate uc_header_id passed is valid
1281 OPEN get_uc_header_det(p_uc_header_id);
1282 FETCH get_uc_header_det into l_uc_header_rec;
1283 IF (get_uc_header_det%NOTFOUND) THEN
1284 fnd_message.set_name('AHL', 'AHL_UC_HEADER_ID_INVALID');
1285 fnd_message.set_token('UC_HEADER_ID', p_uc_header_id, false);
1286 FND_MSG_PUB.add;
1287 CLOSE get_uc_header_det;
1288 RAISE FND_API.G_EXC_ERROR;
1289 END IF;
1290 CLOSE get_uc_header_det;
1291
1292 IF G_DEBUG='Y' THEN
1293 AHL_DEBUG_PUB.debug( 'UC: AHL_UC_APPROVALS_PVT.complete_uc_approval--> UC is Valid 2 ');
1294 END IF;
1295
1296
1297 IF ((l_uc_header_rec.unit_config_status_code = 'QUARANTINE' AND p_approval_status='APPROVED') OR
1298 (l_uc_header_rec.unit_config_status_code = 'DEACTIVATE_QUARANTINE' AND p_approval_status='APPROVAL_REJECTED')) THEN
1299
1300 l_status := 'QUARANTINE';
1301
1302 IF G_DEBUG='Y' THEN
1303 AHL_DEBUG_PUB.debug( 'UC: AHL_UC_APPROVALS_PVT.complete_uc_approval--> l_status 2 : '||l_status);
1304 END IF;
1305
1306
1307 ELSIF ((l_uc_header_rec.unit_config_status_code = 'QUARANTINE' AND p_approval_status = 'APPROVAL_REJECTED') OR
1308 (l_uc_header_rec.unit_config_status_code = 'DEACTIVATE_QUARANTINE' AND p_approval_status='APPROVED')) THEN
1309
1310 IF G_DEBUG='Y' THEN
1311 AHL_DEBUG_PUB.debug( 'UC: AHL_UC_APPROVALS_PVT.complete_uc_approval--> approval_status=APPROVED');
1312 END IF;
1313
1314 --call the completeness check API
1315 AHL_UC_VALIDATION_PUB.check_completeness(
1316 p_api_version => 1.0,
1317 p_init_msg_list => FND_API.G_TRUE,
1318 p_commit => FND_API.G_FALSE,
1319 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1320 p_unit_header_id => p_uc_header_id,
1321 x_evaluation_status => l_evaluation_status,
1322 x_return_status => l_return_status,
1323 x_msg_count => l_msg_count,
1324 x_msg_data => l_msg_data
1325 );
1326
1327 IF G_DEBUG='Y' THEN
1328 AHL_DEBUG_PUB.debug('SQLERRM' || SQLERRM );
1329 AHL_DEBUG_PUB.debug('l_return_status' || l_return_status);
1330 AHL_DEBUG_PUB.debug('l_msg_count' || l_msg_count);
1331 AHL_DEBUG_PUB.debug('l_msg_data' || l_msg_data);
1332 AHL_DEBUG_PUB.debug( 'UC: AHL_UC_APPROVALS_PVT.complete_uc_approval-->After Completeness Check API call');
1333 END IF;
1334
1335 l_msg_count := FND_MSG_PUB.count_msg;
1336 IF l_msg_count > 0 THEN
1337 AHL_DEBUG_PUB.debug('Failed Check Completeness API');
1338 x_msg_count := l_msg_count;
1339 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1340 END IF;
1341
1342 IF ( l_evaluation_status = 'T' ) THEN
1343 l_status := 'COMPLETE';
1344 ELSE
1345 l_status := 'INCOMPLETE';
1346 END IF;
1347
1348 IF G_DEBUG='Y' THEN
1349 AHL_DEBUG_PUB.debug( 'UC: AHL_UC_APPROVALS_PVT.complete_uc_approval--> l_status 3 : '||l_status);
1350 END IF;
1351 END IF;
1352
1353 --update table and write to history table
1354 BEGIN
1355
1356 IF G_DEBUG='Y' THEN
1357 AHL_DEBUG_PUB.debug( 'UC: AHL_UC_APPROVALS_PVT.complete_uc_approval--> Before Update');
1358 END IF;
1359
1360 UPDATE ahl_unit_config_headers
1361 SET unit_config_status_code=l_status,
1362 active_uc_status_code='APPROVED',
1363 object_version_number=object_version_number+1
1364 WHERE unit_config_header_id=p_uc_header_id;
1365
1366 IF G_DEBUG='Y' THEN
1367 AHL_DEBUG_PUB.debug( 'UC: AHL_UC_APPROVALS_PVT.complete_uc_approval--> After Update');
1368 END IF;
1369
1370 EXCEPTION
1371 WHEN OTHERS THEN
1372 IF G_DEBUG='Y' THEN
1373 AHL_DEBUG_PUB.debug('Unexpected Error during Update');
1374 END IF;
1375 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1376 END;
1377
1378 IF G_DEBUG='Y' THEN
1379 AHL_DEBUG_PUB.debug( 'UC: AHL_UC_APPROVALS_PVT.complete_uc_approval--> Before History');
1380 END IF;
1381
1382 AHL_UTIL_UC_PKG.COPY_UC_HEADER_TO_HISTORY(p_uc_header_id, x_return_status);
1383
1384 IF G_DEBUG='Y' THEN
1385 AHL_DEBUG_PUB.debug( 'UC: AHL_UC_APPROVALS_PVT.complete_uc_approval--> After History');
1386 END IF;
1387
1388 l_msg_count := FND_MSG_PUB.count_msg;
1389
1390 IF l_msg_count > 0 THEN
1391 X_msg_count := l_msg_count;
1392 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1393 RAISE FND_API.G_EXC_ERROR;
1394 END IF;
1395
1396
1397 IF FND_API.TO_BOOLEAN(p_commit) THEN
1398 COMMIT;
1399 END IF;
1400
1401 EXCEPTION
1402 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1403 ROLLBACK TO COMPLETE_Q_APPROVAL_SP;
1404 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1405 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
1406 p_count => x_msg_count,
1407 p_data => x_msg_data);
1408 IF G_DEBUG='Y' THEN
1409 AHL_DEBUG_PUB.disable_debug;
1410 END IF;
1411
1412
1413 WHEN FND_API.G_EXC_ERROR THEN
1414 ROLLBACK TO COMPLETE_Q_APPROVAL_SP;
1415 X_return_status := FND_API.G_RET_STS_ERROR;
1416 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
1417 p_count => x_msg_count,
1418 p_data => X_msg_data);
1419 IF G_DEBUG='Y' THEN
1420 AHL_DEBUG_PUB.disable_debug;
1421 END IF;
1422
1423 WHEN OTHERS THEN
1424 ROLLBACK TO COMPLETE_Q_APPROVAL_SP;
1425 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1426 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1427 THEN
1428 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
1429 p_procedure_name => l_api_name,
1430 p_error_text => SUBSTR(SQLERRM,1,240));
1431 END IF;
1432 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
1433 p_count => x_msg_count,
1434 p_data => X_msg_data);
1435 IF G_DEBUG='Y' THEN
1436 AHL_DEBUG_PUB.disable_debug;
1437 END IF;
1438
1439 END COMPLETE_QUARANTINE_APPROVAL;
1440
1441 END AHL_UC_APPROVALS_PVT;
[Home] [Help]