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