1 PACKAGE BODY AHL_DI_DOC_REVISION_PUB AS
2 /* $Header: AHLPDORB.pls 120.0.12000000.2 2007/08/10 11:37:05 viagrawa ship $ */
3 --
4 G_PKG_NAME VARCHAR2(30) := 'AHL_DI_DOC_REVISION_PUB';
5 /*-----------------------------------------------------------*/
6 /* procedure name: Check_lookup_name_Or_Id(private procedure)*/
7 /* description : used to retrieve lookup code */
8 /* */
9 /*-----------------------------------------------------------*/
10
11 --G_DEBUG VARCHAR2(1):=FND_PROFILE.VALUE('AHL_API_FILE_DEBUG_ON');
12 G_DEBUG VARCHAR2(1) := AHL_DEBUG_PUB.is_log_enabled;
13 PROCEDURE Check_lookup_name_Or_Id
14 ( p_lookup_type IN FND_LOOKUPS.lookup_type%TYPE,
15 p_lookup_code IN FND_LOOKUPS.lookup_code%TYPE,
16 p_meaning IN FND_LOOKUPS.meaning%TYPE,
17 p_check_id_flag IN VARCHAR2,
18 x_lookup_code OUT NOCOPY FND_LOOKUPS.lookup_code%TYPE,
19 x_return_status OUT NOCOPY VARCHAR2)
20 IS
21
22 BEGIN
23 IF (p_lookup_code IS NOT NULL) THEN
24 IF (p_check_id_flag = 'Y') THEN
25 SELECT lookup_code INTO x_lookup_code
26 FROM FND_LOOKUP_VALUES_VL
27 WHERE lookup_type = p_lookup_type
28 AND lookup_code = p_lookup_code
29 AND sysdate between start_date_active
30 AND nvl(end_date_active,sysdate);
31 ELSE
32 x_lookup_code := p_lookup_code;
33 END IF;
34 ELSE
35 SELECT lookup_code INTO x_lookup_code
36 FROM FND_LOOKUP_VALUES_VL
37 WHERE lookup_type = p_lookup_type
38 AND meaning = p_meaning
39 AND sysdate between start_date_active
40 AND nvl(end_date_active,sysdate);
41 END IF;
42 x_return_status := FND_API.G_RET_STS_SUCCESS;
43 EXCEPTION
44 WHEN no_data_found THEN
45 x_return_status := FND_API.G_RET_STS_ERROR;
46 WHEN too_many_rows THEN
47 x_return_status := FND_API.G_RET_STS_ERROR;
48 WHEN OTHERS THEN
49 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
50 RAISE;
51 END;
52 /*---------------------------------------------------*/
53 /* procedure name: create_revision */
54 /* description : Creates new revision record */
55 /* for an associated document */
56 /* */
57 /*---------------------------------------------------*/
58 PROCEDURE CREATE_REVISION
59 (
60 p_api_version IN NUMBER := 1.0 ,
61 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
62 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
63 p_validate_only IN VARCHAR2 := FND_API.G_TRUE ,
64 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
65 p_x_revision_tbl IN OUT NOCOPY revision_tbl ,
66 p_module_type IN VARCHAR2 ,
67 x_return_status OUT NOCOPY VARCHAR2 ,
68 x_msg_count OUT NOCOPY NUMBER ,
69 x_msg_data OUT NOCOPY VARCHAR2)
70 IS
71
72 --Check for ID based on the name
73 CURSOR get_party_name(c_approved_by_pty_name IN VARCHAR2)
74 IS
75 --Modified pjha:07-Aug-2002 for performance
76 /*
77 SELECT party_id
78 FROM ahl_hz_per_employees_v
79 WHERE upper(party_name) = upper(c_approved_by_pty_name);
80 */
81 -- changes for performance pbarman 7.5.2003
82 SELECT party_id
83 FROM hz_parties
84 WHERE upper(PARTY_NAME) = upper(c_approved_by_pty_name)
85 AND AHL_DI_DOC_INDEX_PVT.GET_PRODUCT_INSTALL_STATUS('PER') IN ('N','L')
86 AND party_type = 'PERSON'
87 UNION
88 SELECT person_id
89 FROM per_people_f ppf,per_person_types ppt
90 WHERE upper(FULL_NAME) = upper(c_approved_by_pty_name)
91 AND trunc(sysdate) BETWEEN effective_start_date AND effective_end_date
92 AND nvl(current_employee_flag, 'X') = 'Y'
93 AND ppf.person_type_id = ppt.person_type_id
94 AND system_person_type = 'EMP'
95 AND AHL_DI_DOC_INDEX_PVT.GET_PRODUCT_INSTALL_STATUS('PER') IN ('I','S');
96
97 -- Check for ID
98 CURSOR approved_by_party_id(c_approved_by_pty_id IN NUMBER)
99 IS
100 SELECT party_id
101 FROM hz_parties
102 WHERE party_id = c_approved_by_pty_id;
103
104 --Check for Name
105 CURSOR approved_by_party_desc(c_approved_by_pty_name IN VARCHAR2)
106 IS
107 SELECT party_id
108 FROM hz_parties
109 WHERE UPPER(party_name) = UPPER(c_approved_by_pty_name);
110
111 --Used to retrieve the party id for party name and party id
112 CURSOR get_party_name_id (c_party_name IN VARCHAR2, c_party_id IN NUMBER)
113 IS
114 SELECT person_id
115 FROM per_people_f ppf, per_person_types ppt
116 WHERE upper(FULL_NAME) = upper(c_party_name)
117 AND ppf.person_id = c_party_id
118 AND trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
119 AND nvl(ppf.current_employee_flag,'x') = 'Y'
120 AND ppf.person_type_id = ppt.person_type_id
121 AND ppt.system_person_type ='EMP';
122
123
124
125
126 --
127 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_REVISION';
128 l_api_version CONSTANT NUMBER := 1.0;
129
130 l_num_rec NUMBER;
131 l_msg_count NUMBER;
132 l_msg_data VARCHAR2(2000);
133 l_return_status VARCHAR2(1);
134 l_revision_type_code VARCHAR2(30);
135 l_media_type_code VARCHAR2(30);
136 l_revision_status_code VARCHAR2(30);
137 l_revision_status_type VARCHAR2(30) := 'AHL_REVISION_STATUS_TYPE';
138 l_revision_type VARCHAR2(30) := 'AHL_REVISION_TYPE';
139 l_approved_by_party_id NUMBER;
140 l_revision_tbl AHL_DI_DOC_REVISION_PVT.revision_tbl;
141 l_init_msg_list VARCHAR2(10) := FND_API.G_TRUE;
142 l_sysdate DATE;
143
144
145 BEGIN
146 -- Standard Start of API savepoint
147 SAVEPOINT create_revision;
148 -- Check if API is called in debug mode. If yes, enable debug.
149 IF G_DEBUG='Y' THEN
150 AHL_DEBUG_PUB.enable_debug;
151
152 END IF;
153 -- Debug info.
154 IF G_DEBUG='Y' THEN
155 IF G_DEBUG='Y' THEN
156 AHL_DEBUG_PUB.debug( 'enter ahl_di_doc_revision_pub.Create Revision','+REV+');
157
158 END IF;
159 END IF;
160 -- Standard call to check for call compatibility.
161 IF FND_API.to_boolean(l_init_msg_list)
162 THEN
163 FND_MSG_PUB.initialize;
164 END IF;
165 -- Initialize API return status to success
166 x_return_status := 'S';
167 -- Initialize message list if p_init_msg_list is set to TRUE.
168 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
169 p_api_version,
170 l_api_name,G_PKG_NAME)
171 THEN
172 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
173 END IF;
174
175 --Start of API Body
176 IF p_x_revision_tbl.COUNT > 0
177 THEN
178 FOR i IN p_x_revision_tbl.FIRST..p_x_revision_tbl.LAST
179 LOOP
180 --For Approved by Party Id, Party Name is present
181 IF (p_x_revision_tbl(i).approved_by_pty_name IS NOT NULL)
182 THEN
183
184
185
186
187 IF ahl_di_doc_index_pvt.get_product_install_status('PER') in ('N','L')
188 THEN
189
190 OPEN approved_by_party_desc(p_x_revision_tbl(i).approved_by_pty_name);
191 FETCH approved_by_party_desc INTO l_approved_by_party_id;
192 IF approved_by_party_desc%FOUND
193 THEN
194
195 p_x_revision_tbl(i).approved_by_party_id := l_approved_by_party_id;
196 ELSE
197 FND_MESSAGE.SET_NAME('AHL','AHL_DI_APP_BY_PTY_ID_NOT_EXIST');
198 FND_MSG_PUB.ADD;
199 END IF;
200 CLOSE approved_by_party_desc;
201 ELSIF ahl_di_doc_index_pvt.get_product_install_status('PER') in ('I','S')
202 THEN
203 -- modified for bugfix 2193744
204
205 OPEN get_party_name_id (p_x_revision_tbl(i).approved_by_pty_name, p_x_revision_tbl(i).approved_by_party_id);
206 FETCH get_party_name_id INTO l_approved_by_party_id;
207
208 -- If 1 record retrieved then party id and name match, use party id
209 p_x_revision_tbl(i).approved_by_party_id := l_approved_by_party_id;
210
211
212 -- If no records, then party name has been changed
213 IF get_party_name_id%NOTFOUND THEN
214
215
216 IF (p_x_revision_tbl(i).approved_by_party_id IS NULL)
217 THEN
218
219
220 OPEN get_party_name(p_x_revision_tbl(i).approved_by_pty_name);
221 LOOP
222 FETCH get_party_name INTO l_approved_by_party_id;
223 EXIT WHEN get_party_name%NOTFOUND;
224 END LOOP;
225
226 IF get_party_name%ROWCOUNT = 0 THEN
227 FND_MESSAGE.SET_NAME('AHL','AHL_DI_APP_BY_PTY_ID_NOT_EXIST');
228 FND_MSG_PUB.ADD;
229 ELSIF get_party_name%ROWCOUNT = 1 THEN
230 p_x_revision_tbl(i).approved_by_party_id := l_approved_by_party_id;
231 ELSE
232 -- It will show the message to use LOV , so it would take care
233 -- for duplicate records as well
234
235 FND_MESSAGE.SET_NAME('AHL','AHL_DI_APPROVED_BY_USE_LOV');
236 FND_MSG_PUB.ADD;
237
238 END IF;
239 CLOSE get_party_name;
240 END IF;
241 CLOSE get_party_name_id;
242
243 END IF;
244 -- modified for bugfix 2193744
245
246
247 END IF;
248
249
250
251 ELSE
252 /* If Party Name is not available then set the Party Id also to null */
253
254 p_x_revision_tbl(i).approved_by_party_id := null;
255
256 END IF;
257 --For Revision Type Code
258 IF p_x_revision_tbl(i).revision_type_desc IS NOT NULL
259 THEN
260
261 Check_lookup_name_Or_Id (
262 p_lookup_type => 'AHL_REVISION_TYPE',
263 p_lookup_code => null,
264 p_meaning => p_x_revision_tbl(i).revision_type_desc,
265 p_check_id_flag => 'Y',
266 x_lookup_code => l_revision_tbl(i).revision_type_code,
267 x_return_status => l_return_status);
268
269 IF nvl(l_return_status, 'X') <> 'S'
270 THEN
271 FND_MESSAGE.SET_NAME('AHL','AHL_DI_REV_TYPE_CODE_NOT_EXIST');
272 FND_MSG_PUB.ADD;
273 END IF;
274 END IF;
275 -- If Code is present
276 IF p_x_revision_tbl(i).revision_type_code IS NOT NULL
277 THEN
278 l_revision_tbl(i).revision_type_code := p_x_revision_tbl(i).revision_type_code;
279 --If both are missing
280 ELSE
281 l_revision_tbl(i).revision_type_code := p_x_revision_tbl(i).revision_type_code;
282 END IF;
283 --For Media Type Code, meaning is present
284 IF p_x_revision_tbl(i).media_type_desc IS NOT NULL
285 THEN
286
287 Check_lookup_name_Or_Id (
288 p_lookup_type => 'AHL_MEDIA_TYPE',
289 p_lookup_code => null,
290 p_meaning => p_x_revision_tbl(i).media_type_desc,
291 p_check_id_flag => 'Y',
292 x_lookup_code => l_revision_tbl(i).media_type_code,
293 x_return_status => l_return_status);
294
295 IF nvl(l_return_status, 'X') <> 'S'
296 THEN
297 FND_MESSAGE.SET_NAME('AHL','AHL_DI_MEDTYP_CODE_NOT_EXISTS');
298 FND_MSG_PUB.ADD;
299 END IF;
300 END IF;
301 -- If ID presnt
302 IF p_x_revision_tbl(i).media_type_code IS NOT NULL
303 THEN
304 l_revision_tbl(i).media_type_code := p_x_revision_tbl(i).media_type_code;
305 --Both are missing
306 ELSE
307 l_revision_tbl(i).media_type_code := p_x_revision_tbl(i).media_type_code;
308 END IF;
309 --For Revision Status Code
310 IF p_x_revision_tbl(i).revision_status_desc IS NOT NULL
311 THEN
312
313 Check_lookup_name_Or_Id (
314 p_lookup_type => 'AHL_REVISION_STATUS_TYPE',
315 p_lookup_code => null,
316 p_meaning => p_x_revision_tbl(i).revision_status_desc,
317 p_check_id_flag => 'Y',
318 x_lookup_code => l_revision_tbl(i).revision_status_code,
319 x_return_status => l_return_status);
320
321 IF nvl(l_return_status, 'X') <> 'S'
322 THEN
323 FND_MESSAGE.SET_NAME('AHL','AHL_DI_REV_STAT_CODE_NOT_EXIST');
324 FND_MSG_PUB.ADD;
325 END IF;
326 END IF;
327 -- If Revision status code presents
328 IF p_x_revision_tbl(i).revision_status_code IS NOT NULL
329 THEN
330 l_revision_tbl(i).revision_status_code := p_x_revision_tbl(i).revision_status_code;
331 ELSE
332 --Both are missing
333 l_revision_tbl(i).revision_status_code := p_x_revision_tbl(i).revision_status_code;
334 END IF;
335 --
336
337 --validations put for enhancements
338 -- Enhancement no #2027019: pbarman : April 2003
339 --if rev date is null then rev date = sysdate.
340
341 IF p_x_revision_tbl(i).revision_date IS NULL
342 THEN
343 SELECT TRUNC(SYSDATE) into p_x_revision_tbl(i).revision_date FROM DUAL;
344 -- truncate time stamp
345 /* as per FP for ER 5859915 where PM had decided to remove following validation
346 ELSE
347 SELECT SYSDATE into l_sysdate FROM DUAL;
348 IF p_x_revision_tbl(i).revision_date < TRUNC(l_sysdate)
349 THEN
350 FND_MESSAGE.SET_NAME('AHL','AHL_DI_REVDT_LESS_SYSDT');
351 FND_MSG_PUB.ADD;
352 END IF;
353 */
354 END IF;
355 /* Vineet - As per the FP for 11510 Bug 5930628 where PM had decided to remove all date validations except Revision date
356 --if approved_date < revision_date then error
357
358 IF p_x_revision_tbl(i).approved_date IS NOT NULL
359 THEN
360 IF p_x_revision_tbl(i).approved_date < p_x_revision_tbl(i).revision_date
361 THEN
362 FND_MESSAGE.SET_NAME('AHL','AHL_DI_APVDT_LESS_REVDT');
363 FND_MSG_PUB.ADD;
364 END IF;
365 END IF;
366
367 -- As per the FP for bug 3662906 removing issue date validation
368 --if( issue date < either of (approved date,revision date)) then error.
369 --according to dld -- issue date has no validations but PM asked to put
370 IF p_x_revision_tbl(i).issue_date IS NOT NULL
371 THEN
372
373 IF p_x_revision_tbl(i).issue_date < nvl(p_x_revision_tbl(i).approved_date, p_x_revision_tbl(i).revision_date)
374 THEN
375 IF p_x_revision_tbl(i).approved_date IS NULL
376 THEN
377 FND_MESSAGE.SET_NAME('AHL', 'AHL_DI_ISSDT_LESS_REVDT');
378 FND_MSG_PUB.ADD;
379 ELSE
380 FND_MESSAGE.SET_NAME('AHL','AHL_DI_ISSDT_LESS_APVDT');
381 FND_MSG_PUB.ADD;
382 END IF;
383 END IF;
384 END IF;
385
386 --if(effective date < either of (approved date, revision date))
387
388 IF p_x_revision_tbl(i).effective_date IS NOT NULL
389 THEN
390
391 IF p_x_revision_tbl(i).effective_date < nvl(p_x_revision_tbl(i).approved_date, p_x_revision_tbl(i).revision_date)
392 THEN
393 IF p_x_revision_tbl(i).approved_date IS NULL
394 THEN
395 FND_MESSAGE.SET_NAME('AHL','AHL_DI_EFFDT_LESS_REVDT');
396 FND_MSG_PUB.ADD;
397 ELSE
398 FND_MESSAGE.SET_NAME('AHL','AHL_DI_EFFDT_LESS_APVDT');
399 FND_MSG_PUB.ADD;
400 END IF;
401 END IF;
402 END IF;
403
404 --if(received_date > revision_date)
405
406 IF p_x_revision_tbl(i).received_date IS NOT NULL
407 THEN
408 IF p_x_revision_tbl(i).received_date > p_x_revision_tbl(i).revision_date
409 THEN
410 FND_MESSAGE.SET_NAME('AHL','AHL_DI_RECDT_GT_REVDT');
411 FND_MSG_PUB.ADD;
412 END IF;
413 END IF;
414 */
415 -- if obsolete date is not null, check if it is less than any other date
416 IF p_x_revision_tbl(i).obsolete_date IS NOT NULL
417 THEN
418 IF p_x_revision_tbl(i).obsolete_date < p_x_revision_tbl(i).revision_date
419 THEN
420 FND_MESSAGE.SET_NAME('AHL','AHL_DI_OBSDT_LT_REVDT');
421 FND_MSG_PUB.ADD;
422 END IF;
423
424 /* Removing following validations too for FP for bug 5930628
425 IF p_x_revision_tbl(i).obsolete_date < p_x_revision_tbl(i).approved_date
426 THEN
427 FND_MESSAGE.SET_NAME('AHL','AHL_DI_OBSDT_LT_APVDT');
428 FND_MSG_PUB.ADD;
429 END IF;
430
431 IF p_x_revision_tbl(i).obsolete_date < p_x_revision_tbl(i).effective_date
432 THEN
433 FND_MESSAGE.SET_NAME('AHL','AHL_DI_OBSDT_LT_EFFDT');
434 FND_MSG_PUB.ADD;
435 END IF;
436
437 IF p_x_revision_tbl(i).obsolete_date < p_x_revision_tbl(i).issue_date
438 THEN
439 FND_MESSAGE.SET_NAME('AHL','AHL_DI_OBSDT_LT_ISSDT');
440 FND_MSG_PUB.ADD;
441 END IF;
442
443 IF p_x_revision_tbl(i).obsolete_date < p_x_revision_tbl(i).received_date
444 THEN
445 FND_MESSAGE.SET_NAME('AHL','AHL_DI_OBSDT_LT_RECDT');
446 FND_MSG_PUB.ADD;
447 END IF;
448 */
449 END IF;
450 -- if REVISION_STATUS_CODE == OBSOLETE and obsolete date is null then set it to sysdate.
451
452 IF p_x_revision_tbl(i).revision_status_code IS NOT NULL AND
453 p_x_revision_tbl(i).revision_status_code = 'OBSOLETE'
454 THEN
455 SELECT TRUNC(SYSDATE) into p_x_revision_tbl(i).obsolete_date FROM DUAL;
456 -- truncate time stamp
457 END IF;
458
459
460
461 l_revision_tbl(i).doc_revision_id := p_x_revision_tbl(i).doc_revision_id;
462
463 l_revision_tbl(i).approved_by_party_id := p_x_revision_tbl(i).approved_by_party_id;
464
465 l_revision_tbl(i).document_id := p_x_revision_tbl(i).document_id;
466 l_revision_tbl(i).revision_no := p_x_revision_tbl(i).revision_no;
467 l_revision_tbl(i).revision_date := p_x_revision_tbl(i).revision_date;
468 l_revision_tbl(i).approved_date := p_x_revision_tbl(i).approved_date;
469 l_revision_tbl(i).effective_date := p_x_revision_tbl(i).effective_date;
470 l_revision_tbl(i).obsolete_date := p_x_revision_tbl(i).obsolete_date;
471 l_revision_tbl(i).issue_date := p_x_revision_tbl(i).issue_date;
472 l_revision_tbl(i).received_date := p_x_revision_tbl(i).received_date;
473 l_revision_tbl(i).url := p_x_revision_tbl(i).url;
474 l_revision_tbl(i).volume := p_x_revision_tbl(i).volume;
475 l_revision_tbl(i).issue := p_x_revision_tbl(i).issue;
476 l_revision_tbl(i).issue_number := p_x_revision_tbl(i).issue_number;
477 l_revision_tbl(i).language := p_x_revision_tbl(i).language;
478 l_revision_tbl(i).source_lang := p_x_revision_tbl(i).source_lang;
479 l_revision_tbl(i).comments := p_x_revision_tbl(i).comments;
480 l_revision_tbl(i).attribute_category := p_x_revision_tbl(i).attribute_category;
481 l_revision_tbl(i).attribute1 := p_x_revision_tbl(i).attribute1;
482 l_revision_tbl(i).attribute2 := p_x_revision_tbl(i).attribute2;
483 l_revision_tbl(i).attribute3 := p_x_revision_tbl(i).attribute3;
484 l_revision_tbl(i).attribute4 := p_x_revision_tbl(i).attribute4;
485 l_revision_tbl(i).attribute5 := p_x_revision_tbl(i).attribute5;
486 l_revision_tbl(i).attribute6 := p_x_revision_tbl(i).attribute6;
487 l_revision_tbl(i).attribute7 := p_x_revision_tbl(i).attribute7;
488 l_revision_tbl(i).attribute8 := p_x_revision_tbl(i).attribute8;
489 l_revision_tbl(i).attribute9 := p_x_revision_tbl(i).attribute9;
490 l_revision_tbl(i).attribute10 := p_x_revision_tbl(i).attribute10;
491 l_revision_tbl(i).attribute11 := p_x_revision_tbl(i).attribute11;
492 l_revision_tbl(i).attribute12 := p_x_revision_tbl(i).attribute12;
493 l_revision_tbl(i).attribute13 := p_x_revision_tbl(i).attribute13;
494 l_revision_tbl(i).attribute14 := p_x_revision_tbl(i).attribute14;
495 l_revision_tbl(i).attribute15 := p_x_revision_tbl(i).attribute15;
496 l_revision_tbl(i).delete_flag := p_x_revision_tbl(i).delete_flag;
497 l_revision_tbl(i).object_version_number := p_x_revision_tbl(i).object_version_number;
498
499 -- Standard call to get message count and if count is get message info.
500 l_msg_count := FND_MSG_PUB.count_msg;
501
502 IF l_msg_count > 0 THEN
503 X_msg_count := l_msg_count;
504 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
505 THEN
506 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
507 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
508 ELSE
509 X_return_status := FND_API.G_RET_STS_ERROR;
510 RAISE FND_API.G_EXC_ERROR;
511 END IF;
512 END IF;
513 END LOOP;
514 END IF;
515
516
517 /*----------------------------------------------------------------- */
518 /* procedure name: AHL_DI_DOC_REVISION_CUHK.CREATE_REVISION_PRE */
519 /* AHL_DI_DOC_REVISION_VUHK.CREATE_REVISION_PRE */
520 /* description : Added by Siddhartha to call User Hooks */
521 /* Date : Dec 27 2001 */
522 /*----------------------------------------------------------------- */
523
524
525 IF JTF_USR_HKS.Ok_to_Execute( 'AHL_DI_DOC_REVISION_PUB','CREATE_REVISION',
526 'B', 'C' ) then
527
528
529 IF G_DEBUG='Y' THEN
530 AHL_DEBUG_PUB.debug( 'Start AHL_DI_DOC_REVISION_CUHK.CREATE_REVISION_PRE');
531
532 END IF;
533
534 AHL_DI_DOC_REVISION_CUHK.CREATE_REVISION_PRE
535 (
536
537 p_x_revision_tbl => l_revision_tbl ,
538 x_return_status => l_return_status,
539 x_msg_count => l_msg_count ,
540 x_msg_data => l_msg_data
541 );
542
543
544 IF G_DEBUG='Y' THEN
545 AHL_DEBUG_PUB.debug( 'end AHL_DI_DOC_REVISION_CUHK.CREATE_REVISION_PRE');
546
547 END IF;
548
549
550 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
551 RAISE FND_API.G_EXC_ERROR;
552 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
553 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
554 END IF;
555 END IF;
556
557 IF JTF_USR_HKS.Ok_to_Execute( 'AHL_DI_DOC_REVISION_PUB','CREATE_REVISION',
558 'B', 'V' ) then
559 IF G_DEBUG='Y' THEN
560 AHL_DEBUG_PUB.debug( 'Start AHL_DI_DOC_REVISION_VUHK.CREATE_REVISION_PRE');
561
562 END IF;
563
564 AHL_DI_DOC_REVISION_VUHK.CREATE_REVISION_PRE(
565 p_x_revision_tbl => l_revision_tbl ,
566 X_RETURN_STATUS => l_return_status ,
567 X_MSG_COUNT => l_msg_count ,
568 X_MSG_DATA => l_msg_data );
569
570
571 IF G_DEBUG='Y' THEN
572 AHL_DEBUG_PUB.debug( 'End AHL_DI_DOC_REVISION_VUHK.CREATE_REVISION_PRE');
573
574 END IF;
575
576 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
577 RAISE FND_API.G_EXC_ERROR;
578 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
579 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
580 END IF;
581
582
583
584 END IF;
585
586
587 /*---------------------------------------------------------*/
588 /* End ; Date : Dec 20 2001 */
589 /*---------------------------------------------------------*/
590
591
592 -- Call the Private API
593 AHL_DI_DOC_REVISION_PVT.CREATE_REVISION
594 (
595 p_api_version => 1.0,
596 p_init_msg_list => p_init_msg_list,
597 p_commit => p_commit,
598 p_validate_only => p_validate_only,
599 p_validation_level => p_validation_level,
600 p_x_revision_tbl => l_revision_tbl,
601 x_return_status => l_return_status,
602 x_msg_count => l_msg_count,
603 x_msg_data => l_msg_data
604 );
605
606 -- Standard call to get message count and if count is get message info.
607
608 l_msg_count := FND_MSG_PUB.count_msg;
609
610 IF l_msg_count > 0 THEN
611 X_msg_count := l_msg_count;
612 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
613 THEN
614 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
615 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
616 ELSE
617 X_return_status := FND_API.G_RET_STS_ERROR;
618 RAISE FND_API.G_EXC_ERROR;
619 END IF;
620 ELSE
621 FOR i IN 1..l_revision_tbl.COUNT
622 LOOP
623 p_x_revision_tbl(i).doc_revision_id := l_revision_tbl(i).doc_revision_id;
624 END LOOP;
625 END IF;
626
627
628
629 /*----------------------------------------------------------------------------- */
630 /* procedure name: AHL_DI_DOC_REVISION_VUHK.CREATE_REVISION_POST */
631 /* AHL_DI_DOC_REVISION_CUHK.CREATE_REVISION_POST */
632 /* */
633 /* description : Added by siddhartha to call User Hooks */
634 /* Date : Dec 27 2001 */
635 /*------------------------------------------------------------------------------*/
636
637
638 IF JTF_USR_HKS.Ok_to_Execute( 'AHL_DI_DOC_REVISION_PUB','CREATE_REVISION',
639 'A', 'V' ) then
640
641 IF G_DEBUG='Y' THEN
642 AHL_DEBUG_PUB.debug( 'Start AHL_DI_DOC_REVISION_VUHK.CREATE_REVISION_POST');
643
644 END IF;
645
646 AHL_DI_DOC_REVISION_VUHK.CREATE_REVISION_POST(
647 p_revision_tbl => l_revision_tbl,
648 X_RETURN_STATUS => l_return_status ,
649 X_MSG_COUNT => l_msg_count ,
650 X_MSG_DATA => l_msg_data );
651
652 IF G_DEBUG='Y' THEN
653 AHL_DEBUG_PUB.debug( 'End AHL_DI_DOC_REVISION_VUHK.CREATE_REVISION_POST');
654
655 END IF;
656
657 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
658 RAISE FND_API.G_EXC_ERROR;
659 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
660 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
661 END IF;
662
663 END IF;
664
665 IF JTF_USR_HKS.Ok_to_Execute( 'AHL_DI_DOC_REVISION_PUB','CREATE_REVISION',
666 'A', 'C' ) then
667
668 IF G_DEBUG='Y' THEN
669 AHL_DEBUG_PUB.debug( 'Start AHL_DI_DOC_REVISION_CUHK.CREATE_REVISION_POST');
670
671 END IF;
672
673 AHL_DI_DOC_REVISION_CUHK.CREATE_REVISION_POST(
674
675 p_revision_tbl => l_revision_tbl ,
676 X_RETURN_STATUS => l_return_status ,
677 X_MSG_COUNT => l_msg_count ,
678 X_MSG_DATA => l_msg_data );
679
680 IF G_DEBUG='Y' THEN
681 AHL_DEBUG_PUB.debug( 'End AHL_DI_DOC_REVISION_CUHK.CREATE_REVISION_POST');
682
683 END IF;
684
685 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
686 RAISE FND_API.G_EXC_ERROR;
687 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
688 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
689 END IF;
690
691 END IF;
692
693
694
695 /*---------------------------------------------------------*/
696 /* End ; Date : Dec 27 2001 */
697 /*---------------------------------------------------------*/
698
699
700
701 -- Standard check of p_commit.
702 IF FND_API.TO_BOOLEAN(p_commit) THEN
703 COMMIT;
704 END IF;
705 -- Debug info
706 IF G_DEBUG='Y' THEN
707 AHL_DEBUG_PUB.debug( 'End of public api Create Revision','+REV+');
708
709 END IF;
710 -- Check if API is called in debug mode. If yes, disable debug.
711 IF G_DEBUG='Y' THEN
712 AHL_DEBUG_PUB.disable_debug;
713
714 END IF;
715
716 x_msg_data := 'tHIS IS A cHECK'||TO_CHAR(p_x_revision_tbl(1).approved_by_party_id);
717 EXCEPTION
718 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
719 ROLLBACK TO create_revision;
720 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
721 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
722 p_count => x_msg_count,
723 p_data => x_msg_data);
724 IF G_DEBUG='Y' THEN
725 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'ERROR' );
726 AHL_DEBUG_PUB.debug( 'ahl_di_doc_revision_pub.Create Revision','+REV+');
727
728
729 -- Check if API is called in debug mode. If yes, disable debug.
730 AHL_DEBUG_PUB.disable_debug;
731
732 END IF;
733
734 WHEN FND_API.G_EXC_ERROR THEN
735 ROLLBACK TO create_revision;
736 X_return_status := FND_API.G_RET_STS_ERROR;
737 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
738 p_count => x_msg_count,
739 p_data => X_msg_data);
740 -- Debug info.
741 IF G_DEBUG='Y' THEN
742 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
743 AHL_DEBUG_PUB.debug( 'ahl_di_doc_revision_pub.Create Revision','+REV+');
744
745
746 -- Check if API is called in debug mode. If yes, disable debug.
747 AHL_DEBUG_PUB.disable_debug;
748
749 END IF;
750
751
752 WHEN OTHERS THEN
753 ROLLBACK TO create_revision;
754 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
755 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
756 THEN
757 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_DI_DOC_REVISION_PUB',
758 p_procedure_name => 'CREATE_REVISION',
759 p_error_text => SUBSTR(SQLERRM,1,240));
760 END IF;
761 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
762 p_count => x_msg_count,
763 p_data => X_msg_data);
764 -- Debug info.
765 IF G_DEBUG='Y' THEN
766 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'SQL ERROR' );
767 AHL_DEBUG_PUB.debug( 'ahl_di_doc_revision_pub.Create Revision','+REV+');
768
769 -- Check if API is called in debug mode. If yes, disable debug.
770 AHL_DEBUG_PUB.disable_debug;
771
772 END IF;
773
774 END CREATE_REVISION;
775 /*---------------------------------------------------*/
776 /* procedure name: modify_revision */
777 /* description : Update the existing revision record*/
778 /* */
779 /*---------------------------------------------------*/
780
781 PROCEDURE MODIFY_REVISION
782 (
783 p_api_version IN NUMBER := 1.0 ,
784 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
785 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
786 p_validate_only IN VARCHAR2 := FND_API.G_TRUE ,
787 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
788 p_x_revision_tbl IN OUT NOCOPY revision_tbl ,
789 p_module_type IN VARCHAR2,
790 x_return_status OUT NOCOPY VARCHAR2 ,
791 x_msg_count OUT NOCOPY NUMBER ,
792 x_msg_data OUT NOCOPY VARCHAR2
793 )
794 IS
795
796
797 CURSOR get_party_name(c_approved_by_pty_name IN VARCHAR2)
798 IS
799 --Modified pjha:07-Aug-2002 for performance
800 /*
801 SELECT party_id
802 FROM ahl_hz_per_employees_v
803 WHERE upper(party_name) = upper(c_approved_by_pty_name);
804 */
805 --changes due to performance pbarman 7.5.2003
806 SELECT party_id
807 FROM hz_parties
808 WHERE upper(PARTY_NAME) = upper(c_approved_by_pty_name)
809 AND AHL_DI_DOC_INDEX_PVT.GET_PRODUCT_INSTALL_STATUS('PER') IN ('N','L')
810 AND party_type = 'PERSON'
811 UNION
812 SELECT person_id
813 FROM per_people_f ppf,per_person_types ppt
814 WHERE upper(FULL_NAME) = upper(c_approved_by_pty_name)
815 AND trunc(sysdate) BETWEEN effective_start_date AND effective_end_date
816 AND nvl(current_employee_flag, 'X') = 'Y'
817 AND ppf.person_type_id = ppt.person_type_id
818 AND system_person_type = 'EMP'
819 AND AHL_DI_DOC_INDEX_PVT.GET_PRODUCT_INSTALL_STATUS('PER') IN ('I','S');
820
821 -- Check for ID
822 CURSOR approved_by_party_id(c_approved_by_pty_id IN NUMBER)
823 IS
824 SELECT party_id
825 FROM hz_parties
826 WHERE party_id = c_approved_by_pty_id;
827
828 --Check for Name
829 CURSOR approved_by_party_desc(c_approved_by_pty_name IN VARCHAR2)
830 IS
831 SELECT party_id
832 FROM hz_parties
833 WHERE UPPER(party_name) = UPPER(c_approved_by_pty_name);
834
835 --Used to retrieve the party id for party name and party id
836 CURSOR get_party_name_id (c_party_name IN VARCHAR2, c_party_id IN NUMBER)
837 IS
838 SELECT person_id
839 FROM per_people_f ppf, per_person_types ppt
840 WHERE upper(FULL_NAME) = upper(c_party_name)
841 AND ppf.person_id = c_party_id
842 AND trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
843 AND nvl(ppf.current_employee_flag,'x') = 'Y'
844 AND ppf.person_type_id = ppt.person_type_id
845 AND ppt.system_person_type ='EMP';
846
847
848
849 --
850 l_api_name CONSTANT VARCHAR2(30) := 'MODIFY_REVISION';
851 l_api_version CONSTANT NUMBER := 1.0;
852 l_num_rec NUMBER;
853 l_msg_count NUMBER;
854 l_msg_data VARCHAR2(2000);
855 l_return_status VARCHAR2(1);
856 l_revision_type_code VARCHAR2(30);
857 l_media_type_code VARCHAR2(30);
858 l_revision_status_code VARCHAR2(30);
859 l_approved_by_party_id NUMBER;
860 l_revision_tbl AHL_DI_DOC_REVISION_PVT.revision_tbl;
861 l_init_msg_list VARCHAR2(10) := FND_API.G_TRUE;
862 l_sysdate DATE;
863 l_current_revision_date DATE;
864
865
866 BEGIN
867 -- Standard Start of API savepoint
868 SAVEPOINT modify_revision;
869 -- Check if API is called in debug mode. If yes, enable debug.
870 IF G_DEBUG='Y' THEN
871 AHL_DEBUG_PUB.enable_debug;
872
873 END IF;
874 -- Debug info.
875 IF G_DEBUG='Y' THEN
876 IF G_DEBUG='Y' THEN
877 AHL_DEBUG_PUB.debug( 'enter ahl_di_doc_revision_pub.Modify Revision','+REV+');
878
879 END IF;
880 END IF;
881 -- Standard call to check for call compatibility.
882 IF FND_API.to_boolean(l_init_msg_list)
883 THEN
884 FND_MSG_PUB.initialize;
885 END IF;
886 -- Initialize API return status to success
887 x_return_status := 'S';
888 -- Initialize message list if p_init_msg_list is set to TRUE.
889 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
890 p_api_version,
891 l_api_name,G_PKG_NAME)
892 THEN
893 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
894 END IF;
895 --Start API Body
896 IF p_x_revision_tbl.COUNT > 0
897 THEN
898 FOR i IN p_x_revision_tbl.FIRST..p_x_revision_tbl.LAST
899 LOOP
900
901 --For Approved by Party Id, Party Name is present
902 IF (p_x_revision_tbl(i).approved_by_pty_name IS NOT NULL)
903 THEN
904
905
906
907 IF ahl_di_doc_index_pvt.get_product_install_status('PER') in ('N','L')
908 THEN
909 OPEN approved_by_party_desc(p_x_revision_tbl(i).approved_by_pty_name);
910 FETCH approved_by_party_desc INTO l_approved_by_party_id;
911 IF approved_by_party_desc%FOUND
912 THEN
913 p_x_revision_tbl(i).approved_by_party_id := l_approved_by_party_id;
914 ELSE
915 FND_MESSAGE.SET_NAME('AHL','AHL_DI_APP_BY_PTY_ID_NOT_EXIST');
916 FND_MSG_PUB.ADD;
917 END IF;
918 CLOSE approved_by_party_desc;
919
920
921 ELSIF ahl_di_doc_index_pvt.get_product_install_status('PER') in ('I','S')
922 THEN
923
924 -- modified for bugfix 2193744
925
926 OPEN get_party_name_id (p_x_revision_tbl(i).approved_by_pty_name, p_x_revision_tbl(i).approved_by_party_id);
927 FETCH get_party_name_id INTO l_approved_by_party_id;
928
929 -- If 1 record retrieved then party id and name match, use party id
930 p_x_revision_tbl(i).approved_by_party_id := l_approved_by_party_id;
931
932
933 -- If no records, then party name has been changed
934 IF get_party_name_id%NOTFOUND THEN
935
936
937 IF p_x_revision_tbl(i).APPROVED_BY_PARTY_ID IS NULL THEN
938 p_x_revision_tbl(i).approved_by_party_id := null;
939 END IF;
940
941
942 OPEN get_party_name(p_x_revision_tbl(i).approved_by_pty_name);
943 LOOP
944 FETCH get_party_name INTO l_approved_by_party_id;
945 EXIT WHEN get_party_name%NOTFOUND;
946 END LOOP;
947
948 IF get_party_name%ROWCOUNT = 0 THEN
949 FND_MESSAGE.SET_NAME('AHL','AHL_DI_APP_BY_PTY_ID_NOT_EXIST');
950 FND_MSG_PUB.ADD;
951 ELSIF get_party_name%ROWCOUNT = 1 THEN
952 p_x_revision_tbl(i).approved_by_party_id := l_approved_by_party_id;
953 ELSE
954 -- It will show the message to use LOV , so it would take care
955 -- for duplicate records as well
956
957
958 FND_MESSAGE.SET_NAME('AHL','AHL_DI_APPROVED_BY_USE_LOV');
959 FND_MSG_PUB.ADD;
960
961 END IF;
962 CLOSE get_party_name;
963 END IF;
964 CLOSE get_party_name_id;
965
966
967 -- modified for bugfix 2193744
968 END IF;
969
970
971 /* If Party Name is not available then set the Party Id also to null */
972 ELSE
973 --Party Name is missing
974
975 p_x_revision_tbl(i).approved_by_party_id := NULL;
976
977 END IF;
978 --For Revision Type Code
979 IF p_x_revision_tbl(i).revision_type_desc IS NOT NULL
980 THEN
981
982 Check_lookup_name_Or_Id (
983 p_lookup_type => 'AHL_REVISION_TYPE',
984 p_lookup_code => null,
985 p_meaning => p_x_revision_tbl(i).revision_type_desc,
986 p_check_id_flag => 'Y',
987 x_lookup_code => l_revision_tbl(i).revision_type_code,
988 x_return_status => l_return_status);
989
990 IF nvl(l_return_status, 'X') <> 'S'
991 THEN
992 FND_MESSAGE.SET_NAME('AHL','AHL_DI_REV_TYPE_CODE_NOT_EXIST');
993 FND_MSG_PUB.ADD;
994 END IF;
995 END IF;
996 -- If Code is present
997 IF p_x_revision_tbl(i).revision_type_code IS NOT NULL
998 THEN
999 l_revision_tbl(i).revision_type_code := p_x_revision_tbl(i).revision_type_code;
1000 --If both are missing
1001 ELSE
1002 l_revision_tbl(i).revision_type_code := p_x_revision_tbl(i).revision_type_code;
1003 END IF;
1004 --For Media Type Code, meaning is present
1005 IF p_x_revision_tbl(i).media_type_desc IS NOT NULL
1006 THEN
1007
1008 Check_lookup_name_Or_Id (
1009 p_lookup_type => 'AHL_MEDIA_TYPE',
1010 p_lookup_code => null,
1011 p_meaning => p_x_revision_tbl(i).media_type_desc,
1012 p_check_id_flag => 'Y',
1013 x_lookup_code => l_revision_tbl(i).media_type_code,
1014 x_return_status => l_return_status);
1015
1016 IF nvl(l_return_status, 'X') <> 'S'
1017 THEN
1018 FND_MESSAGE.SET_NAME('AHL','AHL_DI_MEDTYP_CODE_NOT_EXISTS');
1019 FND_MSG_PUB.ADD;
1020 END IF;
1021 END IF;
1022 -- If ID presnt
1023 IF p_x_revision_tbl(i).media_type_code IS NOT NULL
1024 THEN
1025 l_revision_tbl(i).media_type_code := p_x_revision_tbl(i).media_type_code;
1026 --Both are missing
1027 ELSE
1028 l_revision_tbl(i).media_type_code := p_x_revision_tbl(i).media_type_code;
1029 END IF;
1030
1031 --For Revision Status Code
1032 IF p_x_revision_tbl(i).revision_status_desc IS NOT NULL
1033 THEN
1034
1035 Check_lookup_name_Or_Id (
1036 p_lookup_type => 'AHL_REVISION_STATUS_TYPE',
1037 p_lookup_code => null,
1038 p_meaning => p_x_revision_tbl(i).revision_status_desc,
1039 p_check_id_flag => 'Y',
1040 x_lookup_code => l_revision_tbl(i).revision_status_code,
1041 x_return_status => l_return_status);
1042
1043 IF nvl(l_return_status, 'X') <> 'S'
1044 THEN
1045 FND_MESSAGE.SET_NAME('AHL','AHL_DI_REV_STAT_CODE_NOT_EXIST');
1046 FND_MSG_PUB.ADD;
1047 END IF;
1048 END IF;
1049 -- If Revision status code presents
1050 IF p_x_revision_tbl(i).revision_status_code IS NOT NULL
1051 THEN
1052 l_revision_tbl(i).revision_status_code := p_x_revision_tbl(i).revision_status_code;
1053 ELSE
1054 --Both are missing
1055 l_revision_tbl(i).revision_status_code := p_x_revision_tbl(i).revision_status_code;
1056 END IF;
1057 --
1058
1059 --validations put for enhancements
1060 -- Enhancement no #2027019: pbarman : April 2003
1061 --if rev date is null then rev date = sysdate.
1062
1063 IF p_x_revision_tbl(i).revision_date IS NULL OR p_x_revision_tbl(i).revision_date = FND_API.G_MISS_DATE
1064 THEN
1065 FND_MESSAGE.SET_NAME('AHL','AHL_DI_REVDT_NULL');
1066 FND_MSG_PUB.ADD;
1067 ELSE
1068 -- select the current revision date. the edited date cannot be less than that.
1069 SELECT REVISION_DATE INTO l_current_revision_date from
1070 ahl_doc_revisions_b
1071 where
1072 ahl_doc_revisions_b.doc_revision_id = p_x_revision_tbl(i).doc_revision_id
1073 and
1074 ahl_doc_revisions_b.document_id = p_x_revision_tbl(i).document_id;
1075
1076 IF p_x_revision_tbl(i).revision_date < l_current_revision_date
1077 THEN
1078 FND_MESSAGE.SET_NAME('AHL','AHL_DI_REVDT_LESS_PREVDATE');
1079 FND_MSG_PUB.ADD;
1080 END IF;
1081 END IF;
1082 /* Vineet - FP for Bug 5930628 - Removing all date validations as per PM discussion
1083 --if approved_date < revision_date then error
1084
1085 IF p_x_revision_tbl(i).approved_date IS NOT NULL
1086 THEN
1087 IF p_x_revision_tbl(i).approved_date < p_x_revision_tbl(i).revision_date
1088 THEN
1089 FND_MESSAGE.SET_NAME('AHL','AHL_DI_APVDT_LESS_REVDT');
1090 FND_MSG_PUB.ADD;
1091 END IF;
1092 END IF;
1093
1094 --if( issue date < either of (approved date,revision date)) then error.
1095
1096 -- bug 3662906
1097 IF p_x_revision_tbl(i).issue_date IS NOT NULL
1098 THEN
1099 IF p_x_revision_tbl(i).issue_date < nvl(p_x_revision_tbl(i).approved_date, p_x_revision_tbl(i).revision_date)
1100 THEN
1101 IF p_x_revision_tbl(i).approved_date IS NULL
1102 THEN
1103 FND_MESSAGE.SET_NAME('AHL', 'AHL_DI_ISSDT_LESS_REVDT');
1104 FND_MSG_PUB.ADD;
1105 ELSE
1106 FND_MESSAGE.SET_NAME('AHL','AHL_DI_ISSDT_LESS_APVDT');
1107 FND_MSG_PUB.ADD;
1108 END IF;
1109 END IF;
1110
1111 END IF;
1112
1113 --if(effective date < either of (approved date, revision date))
1114
1115 IF p_x_revision_tbl(i).effective_date IS NOT NULL
1116 THEN
1117
1118 IF p_x_revision_tbl(i).effective_date < nvl(p_x_revision_tbl(i).approved_date, p_x_revision_tbl(i).revision_date)
1119 THEN
1120 IF p_x_revision_tbl(i).approved_date IS NULL
1121 THEN
1122 FND_MESSAGE.SET_NAME('AHL','AHL_DI_EFFDT_LESS_REVDT');
1123 FND_MSG_PUB.ADD;
1124 ELSE
1125 FND_MESSAGE.SET_NAME('AHL','AHL_DI_EFFDT_LESS_APVDT');
1126 FND_MSG_PUB.ADD;
1127 END IF;
1128 END IF;
1129 END IF;
1130
1131 --if(received_date > revision_date)
1132
1133 IF p_x_revision_tbl(i).received_date IS NOT NULL
1134 THEN
1135 IF p_x_revision_tbl(i).received_date > p_x_revision_tbl(i).revision_date
1136 THEN
1137 FND_MESSAGE.SET_NAME('AHL','AHL_DI_RECDT_GT_REVDT');
1138 FND_MSG_PUB.ADD;
1139 END IF;
1140 END IF;
1141 */
1142 -- if obsolete date is not null, check if it is less than any other date
1143 IF p_x_revision_tbl(i).obsolete_date IS NOT NULL
1144 THEN
1145 IF p_x_revision_tbl(i).obsolete_date < p_x_revision_tbl(i).revision_date
1146 THEN
1147 FND_MESSAGE.SET_NAME('AHL','AHL_DI_OBSDT_LT_REVDT');
1148 FND_MSG_PUB.ADD;
1149 END IF;
1150
1151 /* IF p_x_revision_tbl(i).obsolete_date < p_x_revision_tbl(i).approved_date
1152 THEN
1153 FND_MESSAGE.SET_NAME('AHL','AHL_DI_OBSDT_LT_APVDT');
1154 FND_MSG_PUB.ADD;
1155 END IF;
1156
1157 IF p_x_revision_tbl(i).obsolete_date < p_x_revision_tbl(i).effective_date
1158 THEN
1159 FND_MESSAGE.SET_NAME('AHL','AHL_DI_OBSDT_LT_EFFDT');
1160 FND_MSG_PUB.ADD;
1161 END IF;
1162
1163 IF p_x_revision_tbl(i).obsolete_date < p_x_revision_tbl(i).issue_date
1164 THEN
1165 FND_MESSAGE.SET_NAME('AHL','AHL_DI_OBSDT_LT_ISSDT');
1166 FND_MSG_PUB.ADD;
1167 END IF;
1168
1169 IF p_x_revision_tbl(i).obsolete_date < p_x_revision_tbl(i).received_date
1170 THEN
1171 FND_MESSAGE.SET_NAME('AHL','AHL_DI_OBSDT_LT_RECDT');
1172 FND_MSG_PUB.ADD;
1173 END IF;
1174 */
1175 END IF;
1176 -- if REVISION_STATUS_CODE == OBSOLETE and obsolete date is null then set it to sysdate.
1177
1178 IF p_x_revision_tbl(i).revision_status_code IS NOT NULL AND
1179 p_x_revision_tbl(i).revision_status_code = 'OBSOLETE'
1180 THEN
1181 -- IF p_x_revision_tbl(i).obsolete_date IS NULL
1182 -- THEN
1183 SELECT TRUNC(SYSDATE) into p_x_revision_tbl(i).obsolete_date FROM DUAL;
1184 -- truncate time stamp
1185 -- END IF;
1186 END IF;
1187
1188 l_revision_tbl(i).doc_revision_id := p_x_revision_tbl(i).doc_revision_id;
1189
1190 l_revision_tbl(i).approved_by_party_id := p_x_revision_tbl(i).approved_by_party_id;
1191
1192 l_revision_tbl(i).document_id := p_x_revision_tbl(i).document_id;
1193 l_revision_tbl(i).revision_no := p_x_revision_tbl(i).revision_no;
1194 l_revision_tbl(i).revision_date := p_x_revision_tbl(i).revision_date;
1195 l_revision_tbl(i).approved_date := p_x_revision_tbl(i).approved_date;
1196 l_revision_tbl(i).effective_date := p_x_revision_tbl(i).effective_date;
1197 l_revision_tbl(i).obsolete_date := p_x_revision_tbl(i).obsolete_date;
1198 l_revision_tbl(i).issue_date := p_x_revision_tbl(i).issue_date;
1199 l_revision_tbl(i).received_date := p_x_revision_tbl(i).received_date;
1200 l_revision_tbl(i).url := p_x_revision_tbl(i).url;
1201 l_revision_tbl(i).volume := p_x_revision_tbl(i).volume;
1202 l_revision_tbl(i).issue := p_x_revision_tbl(i).issue;
1203 l_revision_tbl(i).issue_number := p_x_revision_tbl(i).issue_number;
1204 l_revision_tbl(i).language := p_x_revision_tbl(i).language;
1205 l_revision_tbl(i).source_lang := p_x_revision_tbl(i).source_lang;
1206 l_revision_tbl(i).comments := p_x_revision_tbl(i).comments;
1207 l_revision_tbl(i).attribute_category := p_x_revision_tbl(i).attribute_category;
1208 l_revision_tbl(i).attribute1 := p_x_revision_tbl(i).attribute1;
1209 l_revision_tbl(i).attribute2 := p_x_revision_tbl(i).attribute2;
1210 l_revision_tbl(i).attribute3 := p_x_revision_tbl(i).attribute3;
1211 l_revision_tbl(i).attribute4 := p_x_revision_tbl(i).attribute4;
1212 l_revision_tbl(i).attribute5 := p_x_revision_tbl(i).attribute5;
1213 l_revision_tbl(i).attribute6 := p_x_revision_tbl(i).attribute6;
1214 l_revision_tbl(i).attribute7 := p_x_revision_tbl(i).attribute7;
1215 l_revision_tbl(i).attribute8 := p_x_revision_tbl(i).attribute8;
1216 l_revision_tbl(i).attribute9 := p_x_revision_tbl(i).attribute9;
1217 l_revision_tbl(i).attribute10 := p_x_revision_tbl(i).attribute10;
1218 l_revision_tbl(i).attribute11 := p_x_revision_tbl(i).attribute11;
1219 l_revision_tbl(i).attribute12 := p_x_revision_tbl(i).attribute12;
1220 l_revision_tbl(i).attribute13 := p_x_revision_tbl(i).attribute13;
1221 l_revision_tbl(i).attribute14 := p_x_revision_tbl(i).attribute14;
1222 l_revision_tbl(i).attribute15 := p_x_revision_tbl(i).attribute15;
1223 l_revision_tbl(i).delete_flag := p_x_revision_tbl(i).delete_flag;
1224 l_revision_tbl(i).object_version_number := p_x_revision_tbl(i).object_version_number;
1225
1226 -- Standard call to get message count and if count is get message info.
1227 l_msg_count := FND_MSG_PUB.count_msg;
1228
1229 IF l_msg_count > 0 THEN
1230 X_msg_count := l_msg_count;
1231 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1232 THEN
1233 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1234 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1235 ELSE
1236 X_return_status := FND_API.G_RET_STS_ERROR;
1237 RAISE FND_API.G_EXC_ERROR;
1238 END IF;
1239 END IF;
1240 END LOOP;
1241 END IF;
1242
1243
1244
1245 /*---------------------------------------------------------------*/
1246 /* procedure name: AHL_DI_DOC_REVISION_CUHK.MODIFY_REVISION_PRE */
1247 /* AHL_DI_DOC_REVISION_VUHK.MODIFY_REVISION_PRE */
1248 /* description : Added by Siddhartha to call User Hooks */
1249 /* Date : Dec 27 2001 */
1250 /*---------------------------------------------------------------*/
1251
1252
1253 IF JTF_USR_HKS.Ok_to_Execute( 'AHL_DI_DOC_REVISION_PUB','MODIFY_REVISION',
1254 'B', 'C' ) then
1255 IF G_DEBUG='Y' THEN
1256 AHL_DEBUG_PUB.debug( 'Start AHL_DI_DOC_REVISION_CUHK.MODIFY_REVISION_PRE');
1257
1258 END IF;
1259
1260 AHL_DI_DOC_REVISION_CUHK.MODIFY_REVISION_PRE
1261 (
1262
1263 p_x_revision_tbl => l_revision_tbl ,
1264 x_return_status => l_return_status,
1265 x_msg_count => l_msg_count,
1266 x_msg_data => l_msg_data
1267 );
1268
1269
1270 IF G_DEBUG='Y' THEN
1271 AHL_DEBUG_PUB.debug( 'End AHL_DI_DOC_REVISION_CUHK.MODIFY_REVISION_PRE');
1272
1273 END IF;
1274
1275 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1276 RAISE FND_API.G_EXC_ERROR;
1277 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1278 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1279 END IF;
1280 END IF;
1281
1282 IF JTF_USR_HKS.Ok_to_Execute( 'AHL_DI_DOC_REVISION_PUB','MODIFY_REVISION',
1283 'B', 'V' ) then
1284 IF G_DEBUG='Y' THEN
1285 AHL_DEBUG_PUB.debug( 'Start AHL_DI_DOC_REVISION_VUHK.MODIFY_REVISION_PRE');
1286
1287 END IF;
1288
1289
1290 AHL_DI_DOC_REVISION_VUHK.MODIFY_REVISION_PRE(
1291 p_x_revision_tbl => l_revision_tbl ,
1292 X_RETURN_STATUS => l_return_status ,
1293 X_MSG_COUNT => l_msg_count ,
1294 X_MSG_DATA => l_msg_data );
1295
1296
1297 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1298 RAISE FND_API.G_EXC_ERROR;
1299 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1300 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1301 END IF;
1302 IF G_DEBUG='Y' THEN
1303 AHL_DEBUG_PUB.debug( 'End AHL_DI_DOC_REVISION_VUHK.MODIFY_REVISION_PRE');
1304
1305 END IF;
1306
1307 END IF;
1308
1309
1310 /*---------------------------------------------------------*/
1311 /* End ; Date : Dec 20 2001 */
1312 /*---------------------------------------------------------*/
1313
1314 -- Call the Private API
1315 AHL_DI_DOC_REVISION_PVT.MODIFY_REVISION
1316 (
1317 p_api_version => 1.0,
1318 p_init_msg_list => l_init_msg_list,
1319 p_commit => p_commit,
1320 p_validate_only => p_validate_only,
1321 p_validation_level => p_validation_level,
1322 p_x_revision_tbl => l_revision_tbl,
1323 x_return_status => l_return_status,
1324 x_msg_count => l_msg_count,
1325 x_msg_data => l_msg_data
1326 );
1327 -- Standard call to get message count and if count is get message info.
1328 l_msg_count := FND_MSG_PUB.count_msg;
1329
1330 IF l_msg_count > 0 THEN
1331 X_msg_count := l_msg_count;
1332 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1333 THEN
1334 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1335 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1336 ELSE
1337 X_return_status := FND_API.G_RET_STS_ERROR;
1338 RAISE FND_API.G_EXC_ERROR;
1339 END IF;
1340 END IF;
1341
1342
1343
1344 /*----------------------------------------------------------------------------- */
1345 /* procedure name: AHL_DI_DOC_REVISION_VUHK.MODIFY_REVISION_POST */
1346 /* AHL_DI_DOC_REVISION_CUHK.MODIFY_REVISION_POST */
1347 /* */
1348 /* description : Added by siddhartha to call User Hooks */
1349 /* Date : Dec 27 2001 */
1350 /*------------------------------------------------------------------------------*/
1351
1352
1353
1354 IF JTF_USR_HKS.Ok_to_Execute( 'AHL_DI_DOC_REVISION_PUB','MODIFY_REVISION',
1355 'A', 'V' ) then
1356 IF G_DEBUG='Y' THEN
1357 AHL_DEBUG_PUB.debug( 'Start AHL_DI_DOC_REVISION_VUHK.MODIFY_REVISION_POST');
1358
1359 END IF;
1360
1361 AHL_DI_DOC_REVISION_VUHK.MODIFY_REVISION_POST(
1362 p_revision_tbl => l_revision_tbl,
1363 X_RETURN_STATUS => l_return_status ,
1364 X_MSG_COUNT => l_msg_count ,
1365 X_MSG_DATA => l_msg_data );
1366
1367
1368 IF G_DEBUG='Y' THEN
1369 AHL_DEBUG_PUB.debug( 'End AHL_DI_DOC_REVISION_VUHK.MODIFY_REVISION_POST');
1370
1371 END IF;
1372
1373 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1374 RAISE FND_API.G_EXC_ERROR;
1375 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1376 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1377 END IF;
1378
1379
1380 END IF;
1381
1382 IF JTF_USR_HKS.Ok_to_Execute( 'AHL_DI_DOC_REVISION_PUB','MODIFY_REVISION',
1383 'A', 'C' ) then
1384 IF G_DEBUG='Y' THEN
1385 AHL_DEBUG_PUB.debug( 'Start AHL_DI_DOC_REVISION_CUHK.MODIFY_REVISION_POST');
1386
1387 END IF;
1388
1389 AHL_DI_DOC_REVISION_CUHK.MODIFY_REVISION_POST(
1390
1391 p_revision_tbl => l_revision_tbl ,
1392 X_RETURN_STATUS => l_return_status ,
1393 X_MSG_COUNT => l_msg_count ,
1394 X_MSG_DATA => l_msg_data );
1395
1396 IF G_DEBUG='Y' THEN
1397 AHL_DEBUG_PUB.debug( 'End AHL_DI_DOC_REVISION_CUHK.MODIFY_REVISION_POST');
1398
1399 END IF;
1400
1401 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1402 RAISE FND_API.G_EXC_ERROR;
1403 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1404 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1405 END IF;
1406
1407 END IF;
1408
1409
1410 /*---------------------------------------------------------*/
1411 /* End ; Date : Dec 27 2001 */
1412 /*---------------------------------------------------------*/
1413
1414
1415
1416 -- Standard check of p_commit.
1417 IF FND_API.TO_BOOLEAN(p_commit) THEN
1418 COMMIT;
1419 END IF;
1420 -- Debug info
1421 IF G_DEBUG='Y' THEN
1422 AHL_DEBUG_PUB.debug( 'End of public api Modify Revision','+REV+');
1423
1424 END IF;
1425 -- Check if API is called in debug mode. If yes, disable debug.
1426 IF G_DEBUG='Y' THEN
1427 AHL_DEBUG_PUB.disable_debug;
1428
1429 END IF;
1430
1431 x_msg_data := 'tHIS IS A cHECK'||TO_CHAR(p_x_revision_tbl(1).approved_by_party_id);
1432
1433 EXCEPTION
1434 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1435 ROLLBACK TO modify_revision;
1436 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1437 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1438 p_count => x_msg_count,
1439 p_data => x_msg_data);
1440 IF G_DEBUG='Y' THEN
1441 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'ERROR' );
1442 AHL_DEBUG_PUB.debug( 'ahl_di_doc_revision_pub.Modify Revision','+REV+');
1443
1444 -- Check if API is called in debug mode. If yes, disable debug.
1445 AHL_DEBUG_PUB.disable_debug;
1446
1447 END IF;
1448
1449 WHEN FND_API.G_EXC_ERROR THEN
1450 ROLLBACK TO modify_revision;
1451 X_return_status := FND_API.G_RET_STS_ERROR;
1452 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1453 p_count => x_msg_count,
1454 p_data => X_msg_data);
1455 -- Debug info.
1456 IF G_DEBUG='Y' THEN
1457 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
1458 AHL_DEBUG_PUB.debug( 'ahl_di_doc_revision_pub.Modify Revision','+REV+');
1459
1460
1461 -- Check if API is called in debug mode. If yes, disable debug.
1462 AHL_DEBUG_PUB.disable_debug;
1463
1464 END IF;
1465
1466 WHEN OTHERS THEN
1467 ROLLBACK TO modify_revision;
1468 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1469 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1470 THEN
1471 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_DI_DOC_REVISION_PUB',
1472 p_procedure_name => 'MODIFY_REVISION',
1473 p_error_text => SUBSTR(SQLERRM,1,240));
1474 END IF;
1475 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1476 p_count => x_msg_count,
1477 p_data => X_msg_data);
1478
1479 -- Debug info.
1480 IF G_DEBUG='Y' THEN
1481 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'SQL ERROR' );
1482 AHL_DEBUG_PUB.debug( 'ahl_di_doc_revision_pub.Modify Revision','+REV+');
1483
1484
1485 -- Check if API is called in debug mode. If yes, disable debug.
1486 AHL_DEBUG_PUB.disable_debug;
1487
1488 END IF;
1489
1490 END MODIFY_REVISION;
1491 END AHL_DI_DOC_REVISION_PUB;
[Home] [Help]