1 PACKAGE BODY AHL_DI_DOC_INDEX_PVT AS
2 /* $Header: AHLVDIXB.pls 120.5.12020000.2 2012/12/14 07:56:19 shnatu ship $ */
3 --
4 G_PKG_NAME VARCHAR2(30) := 'AHL_DI_DOC_INDEX_PVT';
5 G_DEBUG VARCHAR2(1):=AHL_DEBUG_PUB.is_log_enabled;
6 --
7 /* ===========================================================================
8 G_DEBUG VARCHAR2(1):=AHL_DEBUG_PUB.is_log_enabled;
9 FUNCTION NAME : get_product_install_status (x_product_name IN VARCHAR2)
10 RETURN VARCHAR2
11
12 DESCRIPTION : Returns the product's installation status
13
14 CLIENT/SERVER : SERVER
15
16 PARAMETERS : x_product_name - Name of the product
17 For eg - 'PER','PO','ENG'
18
19 ALGORITHM : Use fnd_installation.get function to retreive
20 the status of product installation.
21 Function expects product id to be passed
22 Product Id will be derived from FND_APPLICATION table
23 Product Product Id
24 -------- -----------
25 INV 401
26 PO 201
27
28 NOTES : valid installation status:
29 I - Product is installed
30 S - Product is partially installed
31 N - Product is not installed
32 L - Product is a local (custom) application
33
34
35 =========================================================================== */
36
37 FUNCTION get_product_install_status ( x_product_name IN VARCHAR2) RETURN VARCHAR2 IS
38 x_progress VARCHAR2(3) := NULL;
39 x_app_id NUMBER;
40 x_install BOOLEAN;
41 x_status VARCHAR2(1);
42 x_org VARCHAR2(1);
43 x_temp_product_name varchar2(10);
44 BEGIN
45 --Retreive product id from fnd_application based on product name
46 x_progress := 10;
47
48 SELECT application_id
49 INTO x_app_id
50 FROM fnd_application
51 WHERE application_short_name = x_product_name ;
52
53 --get product installation status
54 x_progress := 20;
55 x_install := fnd_installation.get(x_app_id,x_app_id,x_status,x_org);
56
57 if x_product_name in ('OE', 'ONT') then
58
59 if Oe_install.get_active_product() in ('OE', 'ONT') then
60 x_status := 'I';
61 else
62 x_status := 'N';
63 end if;
64 end if;
65
66 RETURN(x_status);
67
68 EXCEPTION
69 WHEN NO_DATA_FOUND then
70 null;
71 RETURN(null);
72 WHEN OTHERS THEN
73 po_message_s.sql_error('get_product_install_status', x_progress, sqlcode);
74 RAISE;
75
76 END get_product_install_status;
77
78 /*---------------------------------------------------------*/
79 /* procedure name: validate_document(private procedure) */
80 /* description : Validation checks for before inserting */
81 /* new record as well before modification */
82 /* takes place */
83 /*---------------------------------------------------------*/
84
85 PROCEDURE VALIDATE_DOCUMENT
86 (
87 p_document_id IN NUMBER ,
88 p_source_party_id IN NUMBER ,
89 p_doc_type_code IN VARCHAR2 ,
90 p_doc_sub_type_code IN VARCHAR2 ,
91 p_document_no IN VARCHAR2 ,
92 p_operator_code IN VARCHAR2 ,
93 p_product_type_code IN VARCHAR2 ,
94 p_subscribe_avail_flag IN VARCHAR2 ,
95 p_subscribe_to_flag IN VARCHAR2 ,
96 p_object_version_number IN NUMBER ,
97 p_delete_flag IN VARCHAR2 := 'N'
98 )
99 IS
100 -- Cursor to retrieve doc type code from fnd lookups
101 CURSOR get_doc_type_code(c_doc_type_code VARCHAR2)
102 IS
103 SELECT lookup_code
104 FROM FND_LOOKUPS
105 WHERE lookup_code = c_doc_type_code
106 AND lookup_type = 'AHL_DOC_TYPE'
107 AND ENABLED_FLAG = 'Y'
108 -- pbarman April 2003
109 AND sysdate between nvl(start_date_active,sysdate)
110 AND nvl(end_date_active,sysdate);
111 --Cursor to retrieve doc sub type code from fnd lookups
112 CURSOR get_doc_sub_type_code(c_doc_sub_type_code VARCHAR2)
113 IS
114 SELECT lookup_code
115 FROM FND_LOOKUPS
116 WHERE lookup_code = c_doc_sub_type_code
117 AND lookup_type = 'AHL_DOC_SUB_TYPE'
118 AND ENABLED_FLAG = 'Y'
119 -- pbarman April 2003
120 AND sysdate between nvl(start_date_active,sysdate)
121 AND nvl(end_date_active,sysdate);
122 --Cursor to retrieve operator code from fnd lookups
123 --CURSOR get_operator_code(c_operator_code VARCHAR2)
124 -- IS
125 --SELECT lookup_code
126 -- FROM FND_LOOKUPS
127 -- WHERE lookup_code = c_operator_code
128 -- AND lookup_type = 'AHL_OPERATOR_TYPE'
129 -- AND sysdate between nvl(start_date_active,sysdate)
130 -- AND nvl(end_date_active,sysdate);
131
132 --Cursor to retrieve operator code from hz parties
133 --Enhancement no #2275357 : pbarman : April 2003
134 CURSOR get_operator_code_hz(c_operator_code VARCHAR2)
135 IS
136 SELECT party_id
137 FROM HZ_PARTIES
138 WHERE party_id = c_operator_code
139 AND ( party_type ='ORGANIZATION' or party_type = 'PERSON' );
140
141
142 --Cursor to retrieve product type code from fnd lookups
143
144 CURSOR get_product_type_code(c_product_type_code VARCHAR2)
145 IS
146 SELECT lookup_code
147 FROM FND_LOOKUP_VALUES_VL
148 WHERE lookup_code = c_product_type_code
149 --Enhancement #2525604: pbarman : April 2003
150 AND lookup_type = 'ITEM_TYPE'
151
152 AND sysdate between nvl(start_date_active,sysdate)
153 AND nvl(end_date_active,sysdate)
154 AND enabled_flag = 'Y'
155 AND view_Application_id = 3;
156
157 --Cursor to used retrieve the record from base table
158 CURSOR get_doc_rec_b_info (c_document_id NUMBER)
159 IS
160 SELECT source_party_id,
161 doc_type_code,
162 doc_sub_type_code,
163 document_no,
164 operator_code,
165 product_type_code,
166 subscribe_avail_flag,
167 subscribe_to_flag
168 FROM AHL_DOCUMENTS_B
169 WHERE document_id = c_document_id;
170
171 CURSOR get_sub_type_exists(c_doc_type_code VARCHAR2,
172 c_doc_sub_type_code VARCHAR2)
173 IS
174 SELECT doc_sub_type_code
175 FROM AHL_DOCUMENT_SUB_TYPES
176 WHERE doc_type_code like c_doc_type_code
177 AND doc_sub_type_code like c_doc_sub_type_code;
178
179 -- Cursor used to verify for duplicate record based on document no
180 CURSOR dup_rec(c_source_party_id NUMBER,
181 c_document_no VARCHAR2)
182 IS
183 SELECT 'X'
184 FROM AHL_DOCUMENTS_B
185 WHERE document_no = c_document_no
186 AND source_party_id = c_source_party_id;
187
188
189 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_DOCUMENT';
190 l_api_version CONSTANT NUMBER := 1.0;
191 l_dummy VARCHAR2(2000);
192 l_document_id NUMBER;
193 l_source_party_id NUMBER;
194 l_doc_type_code VARCHAR2(30);
195 l_doc_sub_type_code VARCHAR2(30);
196 l_document_no VARCHAR2(30);
197 l_operator_code VARCHAR2(30);
198 l_product_type_code VARCHAR2(30);
199 l_subscribe_avail_flag VARCHAR2(1);
200 l_subscribe_to_flag VARCHAR2(1);
201 l_delete_flag VARCHAR2(1);
202 BEGIN
203 --When the action is insert or update
204 -- Check if API is called in debug mode. If yes, enable debug.
205 /*FND_MESSAGE.SET_NAME('AHL','i am in validate'|| p_Delete_flag);
206 FND_MSG_PUB.ADD;
207 */
208 l_delete_flag := nvl(p_delete_flag, 'N');
209
210 AHL_DEBUG_PUB.enable_debug;
211 -- Debug info.
212 IF AHL_DEBUG_PUB.G_FILE_DEBUG THEN
213 AHL_DEBUG_PUB.debug( 'enter ahl_di_doc_index_pvt.VALIDATE_DOCUMENT','+DI+');
214 END IF;
215 IF l_delete_flag <> 'Y'
216 THEN
217 IF p_document_id IS NOT NULL
218 THEN
219 OPEN get_doc_rec_b_info (p_document_id);
220 FETCH get_doc_rec_b_info INTO l_source_party_id, l_doc_type_code,
221 l_doc_sub_type_code,l_document_no,
222 l_operator_code, l_product_type_code,
223 l_subscribe_avail_flag,
224 l_subscribe_to_flag;
225 CLOSE get_doc_rec_b_info;
226 END IF;
227 --
228 IF p_document_id IS NOT NULL
229 THEN
230 l_document_id := p_document_id;
231 END IF;
232 --
233 IF p_source_party_id IS NOT NULL
234 THEN
235 l_source_party_id := p_source_party_id;
236 END IF;
237 --
238 IF p_doc_type_code IS NOT NULL
239 THEN
240 l_doc_type_code := p_doc_type_code;
241 END IF;
242 --
243 IF p_doc_sub_type_code IS NOT NULL
244 THEN
245 l_doc_sub_type_code := p_doc_sub_type_code;
246 END IF;
247 --
248 IF p_document_no IS NOT NULL
249 THEN
250 l_document_no := p_document_no;
251 END IF;
252 --
253 IF p_operator_code IS NOT NULL
254 THEN
255 l_operator_code := p_operator_code;
256 END IF;
257 --
258 IF p_product_type_code IS NOT NULL
259 THEN
260 l_product_type_code := p_product_type_code;
261 END IF;
262 --
263 IF p_subscribe_avail_flag IS NOT NULL
264 THEN
265 l_subscribe_avail_flag := p_subscribe_avail_flag;
266 END IF;
267 --
268 IF p_subscribe_to_flag IS NOT NULL
269 THEN
270 l_subscribe_to_flag := p_subscribe_to_flag;
271 END IF;
272 --
273
274 IF p_document_id IS NULL THEN
275 l_document_id := null;
276 ELSE
277 l_document_id := p_document_id;
278 END IF;
279 --This condition checks for Source Party Id
280 IF ((p_document_id IS NULL AND
281 p_source_party_id IS NULL)
282 OR
283 (p_document_id IS NOT NULL
284 AND l_source_party_id IS NULL))
285 THEN
286 FND_MESSAGE.SET_NAME('AHL','AHL_DI_SOURCE_PARTY_ID_NULL');
287 FND_MSG_PUB.ADD;
288 END IF;
289 --This condition checks for doc type code
290 IF ((p_document_id IS NULL AND
291 p_doc_type_code IS NULL)
292 OR
293 (p_document_id IS NOT NULL
294 AND l_doc_type_code IS NULL))
295 THEN
296 FND_MESSAGE.SET_NAME('AHL','AHL_DI_DOC_TYPE_CODE_NULL');
297 FND_MSG_PUB.ADD;
298 END IF;
299 --This condition checks for document number
300 IF ((p_document_id IS NULL AND
301 p_document_no IS NULL)
302 OR
303 (p_document_id IS NOT NULL
304 AND l_document_no IS NULL))
305 THEN
306 FND_MESSAGE.SET_NAME('AHL','AHL_DI_DOCUMENT_NO_NULL');
307 FND_MSG_PUB.ADD;
308 END IF;
309 --This condition checks for Subscriptions available flag
310 IF ((p_document_id IS NULL AND
311 p_subscribe_avail_flag IS NULL)
312 OR
313 (p_document_id IS NOT NULL
314 AND l_subscribe_avail_flag IS NULL))
315 THEN
316 FND_MESSAGE.SET_NAME('AHL','AHL_DI_SUBSC_AVAIL_FLAG_NULL');
317 FND_MSG_PUB.ADD;
318 END IF;
319 --This condition checks for subscribe to flag
320 IF ((p_document_id IS NULL AND
321 p_subscribe_to_flag IS NULL)
322 OR
323 (p_document_id IS NOT NULL
324 AND l_subscribe_to_flag IS NULL))
325 THEN
326 FND_MESSAGE.SET_NAME('AHL','AHL_DI_SUBSC_TO_FLAG_NULL');
327 FND_MSG_PUB.ADD;
328 END IF;
329 --Modified pjha 25-Jun-2002 for restricting Subscription available based on Subscribed To : Begin
330 /*
331 IF (l_subscribe_to_flag = 'Y' AND l_subscribe_avail_flag = 'N')
332 THEN
333 FND_MESSAGE.SET_NAME('AHL','AHL_DI_SUBSC_AVAIL_FLAG_NO');
334 FND_MSG_PUB.ADD;
335 END IF;
336 --Modified pjha 25-Jun-2002 for restricting Subscription available based on Subscribed To : End
337
338 --Added pjha 02-Jul-2002 for Restricting Subscription Avail to 'Yes' If supplier
339 --Exists for the doc: Begin
340 IF (l_subscribe_avail_flag = 'N' AND p_document_id IS NOT NULL)
341 THEN
342 OPEN check_sup_exists(p_document_id);
343 FETCH check_sup_exists INTO l_dummy;
344 IF check_sup_exists%FOUND
345 THEN
346 FND_MESSAGE.SET_NAME('AHL','AHL_DI_SUPPLIER_EXISTS');
347 FND_MSG_PUB.ADD;
348 END IF;
349 CLOSE check_sup_exists;
350 END IF;
351 */
352 --Added pjha 02-Jul-2002 for Restricting Subscription Avail to 'Yes' If supplier
353 --Exists for the doc: End
354
355 --This condition checks for existence of doc type code in fnd lookups \
356
357 IF p_doc_type_code IS NOT NULL
358 THEN
359 OPEN get_doc_type_code(p_doc_type_code);
360 FETCH get_doc_type_code INTO l_dummy;
361 IF get_doc_type_code%NOTFOUND
362 THEN
363 FND_MESSAGE.SET_NAME('AHL','AHL_DI_DOC_TYPE_CODE_NOT_EXIST');
364 FND_MSG_PUB.ADD;
365 END IF;
366 CLOSE get_doc_type_code;
367 END IF;
368 --Checks for sub type code in fnd lookups
369
370
371 IF p_doc_sub_type_code IS NOT NULL
372 THEN
373 OPEN get_doc_sub_type_code(p_doc_sub_type_code);
374 FETCH get_doc_sub_type_code INTO l_dummy;
375 IF get_doc_sub_type_code%NOTFOUND
376 THEN
377 FND_MESSAGE.SET_NAME('AHL','AHL_DI_DOC_SUBT_COD_NOT_EXISTS');
378 FND_MSG_PUB.ADD;
379 END IF;
380 CLOSE get_doc_sub_type_code;
381 -- Checks for sub_type_Code in ahl_document_subtypes
382 OPEN get_sub_type_exists(p_doc_type_code, p_doc_sub_type_code);
383 FETCH get_sub_type_exists INTO l_dummy;
384 IF get_sub_type_exists%NOTFOUND
385 THEN
386 FND_MESSAGE.SET_NAME('AHL','AHL_DI_DOC_SUBT_COD_NOT_EXISTS');
387 FND_MSG_PUB.ADD;
388 END IF;
389
390 CLOSE get_sub_type_exists;
391 END IF;
392 --Checks for Operator code in fnd lookups
393 IF p_operator_code IS NOT NULL
394 THEN
395 --Enhancement no #2275357 : pbarman : April 2003
396 OPEN get_operator_code_hz(p_operator_code);
397 FETCH get_operator_code_hz INTO l_dummy;
398 IF get_operator_code_hz%NOTFOUND
399 THEN
400 FND_MESSAGE.SET_NAME('AHL','AHL_DI_OPERATOR_CODE_NOT_EXIST');
401 FND_MSG_PUB.ADD;
402 END IF;
403 CLOSE get_operator_code_hz;
404
405 END IF;
406 --Checks for Product Type Code
407 IF p_product_type_code IS NOT NULL
408 THEN
409 OPEN get_product_type_code(p_product_type_code);
410 FETCH get_product_type_code INTO l_dummy;
411 IF get_product_type_code%NOTFOUND
412 THEN
413 FND_MESSAGE.SET_NAME('AHL','AHL_DI_PRODTYPE_CODE_NOT_EXIST');
414 FND_MSG_PUB.ADD;
415 END IF;
416 CLOSE get_product_type_code;
417 END IF;
418 --Checks for Duplicate Record
419 IF p_document_id IS NULL
420 THEN
421 OPEN dup_rec(l_source_party_id ,l_document_no);
422 FETCH dup_rec INTO l_dummy;
423 IF dup_rec%FOUND THEN
424 FND_MESSAGE.SET_NAME('AHL','AHL_DI_DOCUMENT_DUP_RECORD');
425 FND_MSG_PUB.ADD;
426 END IF;
427 CLOSE dup_rec;
428 END IF;
429
430
431 END IF;
432 IF AHL_DEBUG_PUB.G_FILE_DEBUG THEN
433 AHL_DEBUG_PUB.debug( 'exit ahl_di_doc_index_pvt.VALIDATE_DOCUMENT','+DI+');
434 END IF;
435 --
436 END VALIDATE_DOCUMENT;
437 /*------------------------------------------------------*/
438 /* procedure name: create_document */
439 /* description : Creates new document record and its */
440 /* suppliers, recipients/* */
441 /*------------------------------------------------------*/
442
443 PROCEDURE CREATE_DOCUMENT
444 (
445 p_api_version IN NUMBER := 1.0 ,
446 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
447 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
448 p_validate_only IN VARCHAR2 := FND_API.G_TRUE ,
449 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
450 p_x_document_tbl IN OUT NOCOPY Document_Tbl ,
451 p_x_supplier_tbl IN OUT NOCOPY Supplier_Tbl ,
452 p_x_recipient_tbl IN OUT NOCOPY Recipient_Tbl ,
453 x_return_status OUT NOCOPY VARCHAR2 ,
454 x_msg_count OUT NOCOPY NUMBER ,
455 x_msg_data OUT NOCOPY VARCHAR2)
456 IS
457 -- Cursor to check for uniqueness
458 CURSOR unique_rec(c_document_no VARCHAR2)
459 IS
460 SELECT 'X'
461 FROM AHL_DOCUMENTS_B
462 WHERE document_no = c_document_no;
463 --
464 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_DOCUMENT';
465 l_api_version CONSTANT NUMBER := 1.0;
466 l_num_rec NUMBER;
467 l_msg_count NUMBER;
468 l_dummy VARCHAR2(2000);
469 l_rowid ROWID;
470 l_document_id NUMBER;
471 l_document_info doc_rec;
472 l_rowid1 varchar2(30);
473 BEGIN
474 -- Standard Start of API savepoint
475 SAVEPOINT create_document;
476 -- Check if API is called in debug mode. If yes, enable debug.
477 IF G_DEBUG='Y' THEN
478 AHL_DEBUG_PUB.enable_debug;
479
480 END IF;
481 -- Debug info.
482 IF G_DEBUG='Y' THEN
483 IF G_DEBUG='Y' THEN
484 AHL_DEBUG_PUB.debug( 'enter ahl_di_doc_index_pvt.create_document','+DI+');
485
486 END IF;
487 END IF;
488 -- Standard call to check for call compatibility.
489 IF FND_API.to_boolean(p_init_msg_list)
490 THEN
491 FND_MSG_PUB.initialize;
492 END IF;
493 -- Initialize API return status to success
494 x_return_status := 'S';
495 -- Initialize message list if p_init_msg_list is set to TRUE.
496 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
497 p_api_version,
498 l_api_name,G_PKG_NAME)
499 THEN
500 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
501 END IF;
502 --Starts API Body
503 IF p_x_document_tbl.COUNT > 0
504 THEN
505 FOR i IN p_x_document_tbl.FIRST..p_x_document_tbl.LAST
506 LOOP
507 VALIDATE_DOCUMENT
508 (
509 p_document_id => p_x_document_tbl(i).document_id,
510 p_source_party_id => p_x_document_tbl(i).source_party_id,
511 p_doc_type_code => p_x_document_tbl(i).doc_type_code,
512 p_doc_sub_type_code => p_x_document_tbl(i).doc_sub_type_code,
513 p_document_no => p_x_document_tbl(i).document_no,
514 p_operator_code => p_x_document_tbl(i).operator_code,
515 p_product_type_code => p_x_document_tbl(i).product_type_code,
516 p_subscribe_avail_flag => p_x_document_tbl(i).subscribe_avail_flag,
517 p_subscribe_to_flag => p_x_document_tbl(i).subscribe_to_flag,
518 p_object_version_number => p_x_document_tbl(i).object_version_number,
519 p_delete_flag => p_x_document_tbl(i).delete_flag);
520
521 END LOOP;
522 --Standard Call to count messages
523 l_msg_count := FND_MSG_PUB.count_msg;
524
525 IF l_msg_count > 0 THEN
526 X_msg_count := l_msg_count;
527 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
528 RAISE FND_API.G_EXC_ERROR;
529 END IF;
530
531 FOR i IN p_x_document_tbl.FIRST..p_x_document_tbl.LAST
532 LOOP
533 IF p_x_document_tbl(i).document_id IS NULL
534 THEN
535 -- Thease conditions are required for optional fields, Frequency code
536
537 l_document_info.doc_sub_type_code := p_x_document_tbl(i).doc_sub_type_code;
538 l_document_info.operator_code := p_x_document_tbl(i).operator_code;
539 l_document_info.product_type_code := p_x_document_tbl(i).product_type_code;
540 l_document_info.document_title := p_x_document_tbl(i).document_title;
541 l_document_info.attribute_category := p_x_document_tbl(i).attribute_category;
542 l_document_info.attribute1 := p_x_document_tbl(i).attribute1;
543 l_document_info.attribute2 := p_x_document_tbl(i).attribute2;
544 l_document_info.attribute3 := p_x_document_tbl(i).attribute3;
545 l_document_info.attribute4 := p_x_document_tbl(i).attribute4;
546 l_document_info.attribute5 := p_x_document_tbl(i).attribute5;
547 l_document_info.attribute6 := p_x_document_tbl(i).attribute6;
548 l_document_info.attribute7 := p_x_document_tbl(i).attribute7;
549 l_document_info.attribute8 := p_x_document_tbl(i).attribute8;
550 l_document_info.attribute9 := p_x_document_tbl(i).attribute9;
551 l_document_info.attribute10 := p_x_document_tbl(i).attribute10;
552 l_document_info.attribute11 := p_x_document_tbl(i).attribute11;
553 l_document_info.attribute12 := p_x_document_tbl(i).attribute12;
554 l_document_info.attribute13 := p_x_document_tbl(i).attribute13;
555 l_document_info.attribute14 := p_x_document_tbl(i).attribute14;
556 l_document_info.attribute15 := p_x_document_tbl(i).attribute15;
557 --Check for uniquences
558 OPEN unique_rec(p_x_document_tbl (i).document_no);
559 FETCH unique_rec INTO l_dummy;
560 IF unique_rec%FOUND THEN
561 FND_MESSAGE.SET_NAME('AHL','AHL_DI_DOCUMENT_DUP_RECORD');
562 FND_MSG_PUB.ADD;
563 RAISE FND_API.G_EXC_ERROR;
564 END IF;
565 CLOSE unique_rec;
566 -- Get the sequence number
567
568 SELECT AHL_DOCUMENTS_B_S.Nextval INTO
569 l_document_id from DUAL;
570 /*-------------------------------------------------------- */
571 /* procedure name: AHL_DI_DOCUMENTS_PKG.INSERT_ROW */
572 /* description : Added by Senthil to call Table Handler */
573 /* Date : Dec 07 2001 */
574 /*---------------------------------------------------------*/
575
576 AHL_DOCUMENTS_PKG.INSERT_ROW(
577 X_ROWID => l_rowid1 ,
578 X_DOCUMENT_ID => l_document_id,
579 X_SUBSCRIBE_AVAIL_FLAG => p_x_document_tbl (i).subscribe_avail_flag,
580 X_SUBSCRIBE_TO_FLAG => p_x_document_tbl (i).subscribe_to_flag ,
581 X_DOC_TYPE_CODE => p_x_document_tbl (i).doc_type_code,
582 X_DOC_SUB_TYPE_CODE => l_document_info.doc_sub_type_code,
583 X_OPERATOR_CODE => l_document_info.operator_code,
584 X_PRODUCT_TYPE_CODE => l_document_info.product_type_code,
585 X_ATTRIBUTE_CATEGORY => l_document_info.attribute_category ,
586 X_ATTRIBUTE1 => l_document_info.attribute1,
587 X_ATTRIBUTE2 => l_document_info.attribute2,
588 X_ATTRIBUTE3 => l_document_info.attribute3,
589 X_ATTRIBUTE4 => l_document_info.attribute4,
590 X_ATTRIBUTE5 => l_document_info.attribute5,
591 X_ATTRIBUTE6 => l_document_info.attribute6,
592 X_ATTRIBUTE7 => l_document_info.attribute7,
593 X_ATTRIBUTE8 => l_document_info.attribute8,
594 X_ATTRIBUTE9 => l_document_info.attribute9,
595 X_ATTRIBUTE10 => l_document_info.attribute10,
596 X_ATTRIBUTE11 => l_document_info.attribute11,
597 X_ATTRIBUTE12 => l_document_info.attribute12,
598 X_ATTRIBUTE13 => l_document_info.attribute13,
599 X_ATTRIBUTE14 => l_document_info.attribute14,
600 X_ATTRIBUTE15 => l_document_info.attribute15,
601 X_OBJECT_VERSION_NUMBER => 1,
602 X_SOURCE_PARTY_ID => p_x_document_tbl (i).source_party_id,
603 X_DOCUMENT_NO => p_x_document_tbl (i).document_no,
604 X_DOCUMENT_TITLE => l_document_info.document_title,
605 X_CREATION_DATE => sysdate ,
606 X_CREATED_BY => fnd_global.user_id,
607 X_LAST_UPDATE_DATE => sysdate ,
608 X_LAST_UPDATED_BY => fnd_global.user_id,
609 X_LAST_UPDATE_LOGIN => fnd_global.login_id
610
611 );
612 -- Assign the value for out parameter
613 p_x_document_tbl(i).document_id := l_document_id;
614
615 l_msg_count := FND_MSG_PUB.count_msg;
616
617 IF l_msg_count > 0 THEN
618 X_msg_count := l_msg_count;
619 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
620 RAISE FND_API.G_EXC_ERROR;
621 END IF;
622 --
623 END IF;
624 END LOOP;
625 END IF;
626 -- Debug info.
627 IF G_DEBUG='Y' THEN
628 IF G_DEBUG='Y' THEN
629 AHL_DEBUG_PUB.debug( 'Before processing Supplier Record ahl_di_doc_index_pvt.create_document','+DI+');
630
631 END IF;
632 END IF;
633 -- Checks for any suppliers exists for the document index
634 IF p_x_supplier_tbl.COUNT > 0 THEN
635 create_supplier
636 ( p_api_version => p_api_version ,
637 p_init_msg_list => FND_API.G_TRUE ,
638 p_commit => FND_API.G_FALSE ,
639 p_validate_only => FND_API.G_TRUE ,
640 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
641 p_x_supplier_tbl => p_x_supplier_tbl ,
642 x_return_status => x_return_status ,
643 x_msg_count => x_msg_count ,
644 x_msg_data => x_msg_data
645 );
646 END IF;
647 -- Debug info.
648 IF G_DEBUG='Y' THEN
649 IF G_DEBUG='Y' THEN
650 AHL_DEBUG_PUB.debug( 'Before processing Recipient Record ahl_di_doc_index_pub.create_document','+DI+');
651
652 END IF;
653 END IF;
654 -- Checks for any Recipients exists for the document index
655 IF p_x_recipient_tbl.COUNT > 0 THEN
656 create_recipient
657 ( p_api_version => p_api_version ,
658 p_init_msg_list => FND_API.G_TRUE ,
659 p_commit => FND_API.G_FALSE ,
660 p_validate_only => FND_API.G_TRUE ,
661 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
662 p_x_recipient_tbl => p_x_recipient_tbl ,
663 x_return_status => x_return_status ,
664 x_msg_count => x_msg_count ,
665 x_msg_data => x_msg_data
666 );
667 END IF;
668 --Standard check for message count
669 l_msg_count := FND_MSG_PUB.count_msg;
670
671 IF l_msg_count > 0 THEN
672 X_msg_count := l_msg_count;
673 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
674 RAISE FND_API.G_EXC_ERROR;
675 END IF;
676 --Standard check for commit;
677 IF FND_API.TO_BOOLEAN(p_commit) THEN
678 COMMIT;
679 --DBMS_OUTPUT.PUT_LINE('THE RECORD IS NOT COMMITTED .THE TEST RUN HAS BEEN SUCESSFUL');
680 --ROLLBACK;
681 END IF;
682
683 EXCEPTION
684 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
685 ROLLBACK TO create_document;
686 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
687 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
688 p_count => x_msg_count,
689 p_data => x_msg_data);
690
691 IF G_DEBUG='Y' THEN
692 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'ERROR' );
693 AHL_DEBUG_PUB.debug( 'ahl_di_doc_index_pvt.create document','+DI+');
694
695
696 -- Check if API is called in debug mode. If yes, disable debug.
697
698 AHL_DEBUG_PUB.disable_debug;
699
700 END IF;
701 WHEN FND_API.G_EXC_ERROR THEN
702 ROLLBACK TO create_document;
703 X_return_status := FND_API.G_RET_STS_ERROR;
704 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
705 p_count => x_msg_count,
706 p_data => X_msg_data);
707 -- Debug info.
708 IF G_DEBUG='Y' THEN
709 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
710 AHL_DEBUG_PUB.debug( 'ahl_di_doc_index_pvt.create document','+DI+');
711
712
713
714 -- Check if API is called in debug mode. If yes, disable debug.
715
716 AHL_DEBUG_PUB.disable_debug;
717
718 END IF;
719 WHEN OTHERS THEN
720 ROLLBACK TO create_document;
721 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
722 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
723 THEN
724 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_DI_DOC_INDEX_PVT',
725 p_procedure_name => 'CREATE_DOCUMENT',
726 p_error_text => SUBSTR(SQLERRM,1,240));
727 END IF;
728 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
729 p_count => x_msg_count,
730 p_data => X_msg_data);
731
732 -- Debug info.
733 IF G_DEBUG='Y' THEN
734 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'SQL ERROR' );
735 AHL_DEBUG_PUB.debug( 'ahl_di_doc_index_pvt.create document','+DI+');
736
737
738
739 -- Check if API is called in debug mode. If yes, disable debug.
740
741 AHL_DEBUG_PUB.disable_debug;
742
743 END IF;
744 END CREATE_DOCUMENT;
745 /*------------------------------------------------------*/
746 /* procedure name: modify_document */
747 /* description : Updates the document record and its */
748 /* associated suppliers, recipients */
749 /* */
750 /*------------------------------------------------------*/
751
752 PROCEDURE MODIFY_DOCUMENT
753 (
754 p_api_version IN NUMBER := 1.0 ,
755 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
756 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
757 p_validate_only IN VARCHAR2 := FND_API.G_TRUE ,
758 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
759 p_x_document_tbl IN OUT NOCOPY document_tbl ,
760 p_x_supplier_tbl IN OUT NOCOPY Supplier_Tbl ,
761 p_x_recipient_tbl IN OUT NOCOPY Recipient_Tbl ,
762 x_return_status OUT NOCOPY VARCHAR2 ,
763 x_msg_count OUT NOCOPY NUMBER ,
764 x_msg_data OUT NOCOPY VARCHAR2
765 )
766 IS
767 --Used to retrieve the existing record
768 CURSOR get_doc_rec_b_info(c_document_id NUMBER)
769 IS
770 SELECT ROWID,
771 document_id,
772 source_party_id,
773 doc_type_code,
774 doc_sub_type_code,
775 document_no,
776 operator_code,
777 product_type_code,
778 subscribe_avail_flag,
779 subscribe_to_flag,
780 object_version_number,
781 attribute_category,
782 attribute1,
783 attribute2,
784 attribute3,
785 attribute4,
786 attribute5,
787 attribute6,
788 attribute7,
789 attribute8,
790 attribute9,
791 attribute10,
792 attribute11,
793 attribute12,
794 attribute13,
795 attribute14,
796 attribute15
797 FROM AHL_DOCUMENTS_B
798 WHERE document_id = c_document_id;
799 --FOR UPDATE OF object_version_number NOWAIT;
800
801 --
802 l_api_name CONSTANT VARCHAR2(30) := 'MODIFY_DOCUMENT';
803 l_api_version CONSTANT NUMBER := 1.0;
804 l_msg_count NUMBER;
805 l_num_rec NUMBER;
806 l_rowid ROWID;
807 --l_document_title VARCHAR2(80);
808 l_document_title VARCHAR2(240);
809 l_document_info get_doc_rec_b_info%ROWTYPE;
810 --
811 l_num VARCHAR2(10);
812 BEGIN
813 -- Standard Start of API savepoint
814 SAVEPOINT modify_document;
815
816 -- Check if API is called in debug mode. If yes, enable debug.
817 IF G_DEBUG='Y' THEN
818 AHL_DEBUG_PUB.enable_debug;
819
820 END IF;
821 -- Debug info.
822 IF G_DEBUG='Y' THEN
823 IF G_DEBUG='Y' THEN
824 AHL_DEBUG_PUB.debug( 'enter ahl_di_doc_index_pvt.modify_document','+DI+');
825
826 END IF;
827 END IF;
828 -- Standard call to check for call compatibility.
829 IF FND_API.to_boolean(p_init_msg_list)
830 THEN
831 FND_MSG_PUB.initialize;
832 END IF;
833 -- Initialize API return status to success
834 x_return_status := 'S';
835 -- Initialize message list if p_init_msg_list is set to TRUE.
836 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
837 p_api_version,
838 l_api_name,G_PKG_NAME)
839 THEN
840 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
841 END IF;
842
843
844 --Start API Body
845 IF p_x_document_tbl.COUNT > 0
846 THEN
847 FOR i IN p_x_document_tbl.FIRST..p_x_document_tbl.LAST
848 LOOP
849
850
851 VALIDATE_DOCUMENT(
852 p_document_id => p_x_document_tbl(i).document_id,
853 p_source_party_id => p_x_document_tbl(i).source_party_id,
854 p_doc_type_code => p_x_document_tbl(i).doc_type_code,
855 p_doc_sub_type_code => p_x_document_tbl(i).doc_sub_type_code,
856 p_document_no => p_x_document_tbl(i).document_no,
857 p_operator_code => p_x_document_tbl(i).operator_code,
858 p_product_type_code => p_x_document_tbl(i).product_type_code,
859 p_subscribe_avail_flag => p_x_document_tbl(i).subscribe_avail_flag,
860 p_subscribe_to_flag => p_x_document_tbl(i).subscribe_to_flag,
861 p_object_version_number => p_x_document_tbl(i).object_version_number,
862 p_delete_flag => p_x_document_tbl(i).delete_flag);
863
864
865 END LOOP;
866 --Standard check to count messages
867 l_msg_count := FND_MSG_PUB.count_msg;
868
869 IF l_msg_count > 0 THEN
870 X_msg_count := l_msg_count;
871 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
872 RAISE FND_API.G_EXC_ERROR;
873 END IF;
874
875
876 FOR i IN p_x_document_tbl.FIRST..p_x_document_tbl.LAST
877 LOOP
878 OPEN get_doc_rec_b_info(p_x_document_tbl(i).document_id);
879 FETCH get_doc_rec_b_info INTO l_document_info;
880 CLOSE get_doc_rec_b_info;
881
882 --
883
884 --pekambar changes for bug # 9226988 --start
885 if (p_x_document_tbl(i).attribute1 IS NULL ) THEN
886 p_x_document_tbl(i).attribute1 := l_document_info.attribute1;
887 ELSIF(p_x_document_tbl(i).attribute1 = FND_API.G_MISS_CHAR ) THEN
888 p_x_document_tbl(i).attribute1 := NULL;
889 END IF;
890 if (p_x_document_tbl(i).attribute2 IS NULL ) THEN
891 p_x_document_tbl(i).attribute2 := l_document_info.attribute2;
892 ELSIF(p_x_document_tbl(i).attribute2 = FND_API.G_MISS_CHAR) THEN
893 p_x_document_tbl(i).attribute2 := NULL;
894 END IF;
895 if (p_x_document_tbl(i).attribute3 IS NULL ) THEN
896 p_x_document_tbl(i).attribute3 := l_document_info.attribute3;
897 ELSIF(p_x_document_tbl(i).attribute3 = FND_API.G_MISS_CHAR ) THEN
898 p_x_document_tbl(i).attribute3 := l_document_info.attribute1;
899 END IF;
900 if (p_x_document_tbl(i).attribute4 IS NULL ) THEN
901 p_x_document_tbl(i).attribute4 := l_document_info.attribute4;
902 ELSIF(p_x_document_tbl(i).attribute4 = FND_API.G_MISS_CHAR ) THEN
903 p_x_document_tbl(i).attribute4 := NULL;
904 END IF;
905 if (p_x_document_tbl(i).attribute5 IS NULL ) THEN
906 p_x_document_tbl(i).attribute5 := l_document_info.attribute5;
907 ELSIF(p_x_document_tbl(i).attribute5 = FND_API.G_MISS_CHAR ) THEN
908 p_x_document_tbl(i).attribute5 := NULL;
909 END IF;
910 if (p_x_document_tbl(i).attribute6 IS NULL ) THEN
911 p_x_document_tbl(i).attribute6:= l_document_info.attribute6;
912 ELSIF(p_x_document_tbl(i).attribute6 = FND_API.G_MISS_CHAR ) THEN
913 p_x_document_tbl(i).attribute6 := NULL;
914 END IF;
915 if (p_x_document_tbl(i).attribute7 IS NULL ) THEN
916 p_x_document_tbl(i).attribute7:= l_document_info.attribute7;
917 ELSIF(p_x_document_tbl(i).attribute7 = FND_API.G_MISS_CHAR ) THEN
918 p_x_document_tbl(i).attribute7 := NULL;
919 END IF;
920 if (p_x_document_tbl(i).attribute8 IS NULL ) THEN
921 p_x_document_tbl(i).attribute8 := l_document_info.attribute8;
922 ELSIF(p_x_document_tbl(i).attribute8 = FND_API.G_MISS_CHAR ) THEN
923 p_x_document_tbl(i).attribute8 := NULL;
924 END IF;
925 if (p_x_document_tbl(i).attribute9 IS NULL ) THEN
926 p_x_document_tbl(i).attribute9 := l_document_info.attribute9;
927 ELSIF(p_x_document_tbl(i).attribute9 = FND_API.G_MISS_CHAR ) THEN
928 p_x_document_tbl(i).attribute9 := NULL;
929 END IF;
930 if (p_x_document_tbl(i).attribute10 IS NULL ) THEN
931 p_x_document_tbl(i).attribute10 := l_document_info.attribute10;
932 ELSIF(p_x_document_tbl(i).attribute10 = FND_API.G_MISS_CHAR ) THEN
933 p_x_document_tbl(i).attribute10 := NULL;
934 END IF;
935 if (p_x_document_tbl(i).attribute11 IS NULL ) THEN
936 p_x_document_tbl(i).attribute11 := l_document_info.attribute11;
937 ELSIF(p_x_document_tbl(i).attribute11 = FND_API.G_MISS_CHAR) THEN
938 p_x_document_tbl(i).attribute11 := NULL;
939 END IF;
940 if (p_x_document_tbl(i).attribute12 IS NULL ) THEN
941 p_x_document_tbl(i).attribute12 := l_document_info.attribute12;
942 ELSIF(p_x_document_tbl(i).attribute12 = FND_API.G_MISS_CHAR) THEN
943 p_x_document_tbl(i).attribute12 := NULL;
944 END IF;
945 if (p_x_document_tbl(i).attribute13 IS NULL ) THEN
946 p_x_document_tbl(i).attribute13 := l_document_info.attribute13;
947 ELSIF(p_x_document_tbl(i).attribute13 = FND_API.G_MISS_CHAR ) THEN
948 p_x_document_tbl(i).attribute13 := NULL;
949 END IF;
950 if (p_x_document_tbl(i).attribute14 IS NULL ) THEN
951 p_x_document_tbl(i).attribute14 := l_document_info.attribute14;
952 ELSIF(p_x_document_tbl(i).attribute14 = FND_API.G_MISS_CHAR ) THEN
953 p_x_document_tbl(i).attribute14 := NULL;
954 END IF;
955 if (p_x_document_tbl(i).attribute15 IS NULL ) THEN
956 p_x_document_tbl(i).attribute15 := l_document_info.attribute15;
957 ELSIF(p_x_document_tbl(i).attribute15 = FND_API.G_MISS_CHAR) THEN
958 p_x_document_tbl(i).attribute15 := NULL;
959 END IF;
960 --pekambar changes for bug # 9226988 --end
961
962
963 -- This condition will take care of lost update data bug when concurrent users are
964 -- updating same record...02/05/02
965 if (l_document_info.object_version_number <>p_x_document_tbl(i).object_version_number)
966 then
967 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
968 FND_MSG_PUB.ADD;
969 RAISE FND_API.G_EXC_ERROR;
970 end if;
971 -- The following conditions compare the new record value with old record
972 -- value, if its different then assign the new value else continue
973 IF p_x_document_tbl(i).document_id IS NOT NULL
974 THEN
975 --Update the document table
976 /*-------------------------------------------------------- */
977 /* procedure name: AHL_DOCUMENTS_PKG.UPDATE_ROW */
978 /* description : Added by Senthil to call Table Handler */
979 /* Date : Dec 07 2001 */
980 /*---------------------------------------------------------*/
981
982
983 AHL_DOCUMENTS_PKG.UPDATE_ROW (
984 X_DOCUMENT_ID => p_x_document_tbl(i).document_id,
985 X_SUBSCRIBE_AVAIL_FLAG => p_x_document_tbl(i).subscribe_avail_flag,
986 X_SUBSCRIBE_TO_FLAG => p_x_document_tbl(i).subscribe_to_flag,
987 X_DOC_TYPE_CODE => p_x_document_tbl(i).doc_type_code,
988 X_DOC_SUB_TYPE_CODE => p_x_document_tbl(i).doc_sub_type_code,
989 X_OPERATOR_CODE => p_x_document_tbl(i).operator_code,
990 X_PRODUCT_TYPE_CODE => p_x_document_tbl(i).product_type_code,
991 X_ATTRIBUTE_CATEGORY => p_x_document_tbl(i).attribute_category,
992 X_ATTRIBUTE1 => p_x_document_tbl(i).attribute1,
993 X_ATTRIBUTE2 => p_x_document_tbl(i).attribute2,
994 X_ATTRIBUTE3 => p_x_document_tbl(i).attribute3,
995 X_ATTRIBUTE4 => p_x_document_tbl(i).attribute4,
996 X_ATTRIBUTE5 => p_x_document_tbl(i).attribute5,
997 X_ATTRIBUTE6 => p_x_document_tbl(i).attribute6 ,
998 X_ATTRIBUTE7 => p_x_document_tbl(i).attribute7 ,
999 X_ATTRIBUTE8 => p_x_document_tbl(i).attribute8 ,
1000 X_ATTRIBUTE9 => p_x_document_tbl(i).attribute9 ,
1001 X_ATTRIBUTE10 => p_x_document_tbl(i).attribute10 ,
1002 X_ATTRIBUTE11 => p_x_document_tbl(i).attribute11 ,
1003 X_ATTRIBUTE12 => p_x_document_tbl(i).attribute12 ,
1004 X_ATTRIBUTE13 => p_x_document_tbl(i).attribute13 ,
1005 X_ATTRIBUTE14 => p_x_document_tbl(i).attribute14 ,
1006 X_ATTRIBUTE15 => p_x_document_tbl(i).attribute15 ,
1007 X_OBJECT_VERSION_NUMBER => p_x_document_tbl(i).object_version_number+1,
1008 X_SOURCE_PARTY_ID => p_x_document_tbl(i).source_party_id,
1009 X_DOCUMENT_NO => p_x_document_tbl(i).document_no,
1010 X_DOCUMENT_TITLE => p_x_document_tbl(i).document_title,
1011 X_LAST_UPDATE_DATE => sysdate ,
1012 X_LAST_UPDATED_BY => fnd_global.user_id ,
1013 X_LAST_UPDATE_LOGIN => fnd_global.login_id
1014 );
1015 END IF;
1016 END LOOP;
1017 END IF;
1018 -- Debug info.
1019 IF G_DEBUG='Y' THEN
1020 IF G_DEBUG='Y' THEN
1021 AHL_DEBUG_PUB.debug( 'Before start processing Supplier Record ahl_di_doc_index_pvt.modify document','+DI+');
1022
1023 END IF;
1024 END IF;
1025
1026 -- Checks for any suppliers modifications exists for the document index
1027 IF p_x_supplier_tbl.COUNT > 0 THEN
1028 modify_supplier
1029 ( p_api_version => p_api_version ,
1030 p_init_msg_list => FND_API.G_TRUE ,
1031 p_commit => FND_API.G_FALSE ,
1032 p_validate_only => FND_API.G_TRUE ,
1033 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1034 p_supplier_tbl => p_x_supplier_tbl ,
1035 x_return_status => x_return_status ,
1036 x_msg_count => x_msg_count ,
1037 x_msg_data => x_msg_data
1038 );
1039 END IF;
1040 -- Debug info.
1041 IF G_DEBUG='Y' THEN
1042 IF G_DEBUG='Y' THEN
1043 AHL_DEBUG_PUB.debug( 'Before start processing Recipient Record ahl_di_doc_index_pvt.modify document','+DI+');
1044
1045 END IF;
1046 END IF;
1047
1048 -- Checks for any Recipients exists for the document index
1049 IF p_x_recipient_tbl.COUNT > 0 THEN
1050 modify_recipient
1051 ( p_api_version => p_api_version ,
1052 p_init_msg_list => FND_API.G_TRUE ,
1053 p_commit => FND_API.G_FALSE ,
1054 p_validate_only => FND_API.G_TRUE ,
1055 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1056 p_recipient_tbl => p_x_recipient_tbl ,
1057 x_return_status => x_return_status ,
1058 x_msg_count => x_msg_count ,
1059 x_msg_data => x_msg_data
1060 );
1061 END IF;
1062 --Standard check for message count
1063 l_msg_count := FND_MSG_PUB.count_msg;
1064
1065 IF l_msg_count > 0 THEN
1066 X_msg_count := l_msg_count;
1067 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1068 RAISE FND_API.G_EXC_ERROR;
1069 END IF;
1070 --Standard check for commit
1071 IF FND_API.TO_BOOLEAN(p_commit) THEN
1072 COMMIT;
1073 END IF;
1074 -- Debug info
1075 IF G_DEBUG='Y' THEN
1076 AHL_DEBUG_PUB.debug( 'End of private api modify document','+DI+');
1077
1078 END IF;
1079 -- Check if API is called in debug mode. If yes, disable debug.
1080 IF G_DEBUG='Y' THEN
1081 AHL_DEBUG_PUB.disable_debug;
1082
1083 END IF;
1084
1085 EXCEPTION
1086 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1087 ROLLBACK TO modify_document;
1088 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1089 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1090 p_count => x_msg_count,
1091 p_data => x_msg_data);
1092
1093 IF G_DEBUG='Y' THEN
1094 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'ERROR' );
1095 AHL_DEBUG_PUB.debug( 'ahl_di_doc_index_pvt.modify document','+DI+');
1096
1097
1098
1099 -- Check if API is called in debug mode. If yes, disable debug.
1100
1101 AHL_DEBUG_PUB.disable_debug;
1102
1103 END IF;
1104
1105 WHEN FND_API.G_EXC_ERROR THEN
1106 ROLLBACK TO modify_document;
1107 X_return_status := FND_API.G_RET_STS_ERROR;
1108 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1109 p_count => x_msg_count,
1110 p_data => X_msg_data);
1111 -- Debug info.
1112 IF G_DEBUG='Y' THEN
1113 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
1114 AHL_DEBUG_PUB.debug( 'ahl_di_doc_index_pvt.modify document','+DI+');
1115
1116
1117 -- Check if API is called in debug mode. If yes, disable debug.
1118
1119 AHL_DEBUG_PUB.disable_debug;
1120
1121 END IF;
1122
1123 WHEN OTHERS THEN
1124 ROLLBACK TO modify_document;
1125 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1126 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1127 THEN
1128 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_DI_DOC_INDEX_PVT',
1129 p_procedure_name => 'MODIFY_DOCUMENT',
1130 p_error_text => SUBSTR(SQLERRM,1,240));
1131 END IF;
1132 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1133 p_count => x_msg_count,
1134 p_data => X_msg_data);
1135
1136 -- Debug info.
1137 IF G_DEBUG='Y' THEN
1138 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'SQL ERROR' );
1139 AHL_DEBUG_PUB.debug( 'ahl_di_doc_index_pvt.modify document','+DI+');
1140
1141
1142
1143 -- Check if API is called in debug mode. If yes, disable debug.
1144 AHL_DEBUG_PUB.disable_debug;
1145
1146 END IF;
1147
1148 END MODIFY_DOCUMENT;
1149 /*-----------------------------------------------------*/
1150 /* procedure name: validate_supplier(private procedure)*/
1151 /* description : Validation checks for before insert */
1152 /* new record as well before update */
1153 /*-----------------------------------------------------*/
1154 PROCEDURE VALIDATE_SUPPLIER
1155 ( P_SUPPLIER_DOCUMENT_ID IN NUMBER ,
1156 P_SUPPLIER_ID IN NUMBER ,
1157 P_DOCUMENT_ID IN NUMBER ,
1158 P_PREFERENCE_CODE IN VARCHAR2 ,
1159 --P_OBJECT_VERSION_NUMBER IN NUMBER,
1160 P_DELETE_FLAG IN VARCHAR2 := 'N')
1161 IS
1162 -- Cursor to retrieve the preference code from fnd lookups
1163 CURSOR get_preference_code(c_preference_code VARCHAR2)
1164 IS
1165 SELECT lookup_code
1166 FROM FND_LOOKUP_VALUES_VL
1167 WHERE lookup_code = c_preference_code
1168 AND lookup_type = 'AHL_SUPPLIER_PREF_TYPE'
1169 AND sysdate between nvl(start_date_active,sysdate)
1170 AND nvl(end_date_active,sysdate);
1171 -- Used to validate document id
1172 CURSOR check_doc_info(c_document_id NUMBER)
1173 IS
1174 SELECT 'X'
1175 FROM AHL_DOCUMENTS_B
1176 WHERE document_id = c_document_id;
1177 --Cursor to get supplier info
1178 CURSOR get_supplier_rec_info (c_supplier_document_id NUMBER)
1179 IS
1180 SELECT supplier_id,
1181 document_id,
1182 preference_code
1183 FROM AHL_SUPPLIER_DOCUMENTS
1184 WHERE supplier_document_id = c_supplier_document_id;
1185 -- Used to check Duplicate Record
1186 CURSOR dup_rec(c_supplier_id NUMBER,
1187 c_document_id NUMBER)
1188 IS
1189 SELECT 'X'
1190 FROM AHL_SUPPLIER_DOCUMENTS
1191 WHERE supplier_id = c_supplier_id
1192 AND document_id = c_document_id;
1193
1194 -- Perf Bug Fix 4919011.
1195 -- Replacing get_supplier_name by get_supplier_name_hz and get_supplier_name_po below
1196 /*
1197 CURSOR get_supplier_name(c_supplier_id NUMBER)
1198 IS
1199 SELECT party_number
1200 FROM AHL_HZ_PO_SUPPLIERS_V
1201 WHERE party_id =c_supplier_id;
1202 */
1203
1204 CURSOR get_supplier_name_hz(c_supplier_id NUMBER)
1205 IS
1206 SELECT party_number
1207 FROM HZ_PARTIES
1208 WHERE party_id =c_supplier_id;
1209
1210 CURSOR get_supplier_name_po(c_supplier_id NUMBER)
1211 IS
1212 SELECT SEGMENT1
1213 FROM PO_VENDORS
1214 WHERE VENDOR_ID =c_supplier_id;
1215
1216 --
1217 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_SUPPLIER';
1218 l_api_version CONSTANT NUMBER := 1.0;
1219 l_dummy VARCHAR2(2000);
1220 l_supplier_id NUMBER;
1221 l_document_id NUMBER;
1222 l_preference_code VARCHAR2(30);
1223 l_supplier_document_id NUMBER;
1224 l_supplier_name VARCHAR2(30);
1225 l_prod_install_status VARCHAR2(30);
1226
1227 BEGIN
1228
1229 -- Perf Bug Fix 4919011.
1230 BEGIN
1231 IF G_DEBUG='Y' THEN
1232 AHL_DEBUG_PUB.debug( 'Fetching Installation Status of PO','+SUP+');
1233 END IF;
1234 SELECT AHL_DI_DOC_INDEX_PVT.GET_PRODUCT_INSTALL_STATUS('PO')
1235 INTO l_prod_install_status
1236 FROM DUAL;
1237 END;
1238
1239 --When the process is insert or update(FLAG <> 'YES')
1240 IF p_delete_flag <> 'Y'
1241 THEN
1242 IF p_supplier_document_id IS NOT NULL
1243 THEN
1244 OPEN get_supplier_rec_info(p_supplier_document_id);
1245 FETCH get_supplier_rec_info INTO l_supplier_id,
1246 l_document_id,
1247 l_preference_code;
1248 CLOSE get_supplier_rec_info;
1249 END IF;
1250 --
1251 IF p_supplier_id IS NOT NULL
1252 THEN
1253 l_supplier_id := p_supplier_id;
1254 END IF;
1255 --
1256 IF p_document_id IS NOT NULL
1257 THEN
1258 l_document_id := p_document_id;
1259 END IF;
1260 --
1261 IF p_preference_code IS NOT NULL
1262 THEN
1263 l_preference_code := p_preference_code;
1264 END IF;
1265 --
1266 l_supplier_document_id := p_supplier_document_id;
1267 -- This condition checks for supplier id value is Null
1268 IF ((p_supplier_document_id IS NULL AND
1269 p_supplier_id IS NULL)
1270 OR
1271 (p_supplier_document_id IS NOT NULL
1272 AND l_supplier_id IS NULL))
1273 THEN
1274 FND_MESSAGE.SET_NAME('AHL','AHL_DI_SUPPLIER_ID_NULL');
1275 FND_MSG_PUB.ADD;
1276 END IF;
1277 -- This condition checks for Document id Is Null
1278 IF ((p_supplier_document_id IS NULL AND
1279 p_document_id IS NULL)
1280 OR
1281 (p_supplier_document_id IS NOT NULL
1282 AND l_document_id IS NULL))
1283 THEN
1284 FND_MESSAGE.SET_NAME('AHL','AHL_DI_DOCUMENT_ID_NULL');
1285 FND_MSG_PUB.ADD;
1286 END IF;
1287 -- This condition checks for existence of preference code in fnd lookups
1288 IF p_preference_code IS NOT NULL
1289 THEN
1290 OPEN get_preference_code(p_preference_code);
1291 FETCH get_preference_code INTO l_dummy;
1292 IF get_preference_code%NOTFOUND
1293 THEN
1294 FND_MESSAGE.SET_NAME('AHL','AHL_DI_PREF_CODE_NOT_EXIST');
1295 FND_MSG_PUB.ADD;
1296 END IF;
1297 CLOSE get_preference_code;
1298 END IF;
1299 -- This condition checks for document record in ahl documents table
1300 IF p_document_id IS NOT NULL
1301 THEN
1302 OPEN Check_doc_info(p_document_id);
1303 FETCH Check_doc_info INTO l_dummy;
1304 IF Check_doc_info%NOTFOUND
1305 THEN
1306 FND_MESSAGE.SET_NAME('AHL','AHL_DI_DOC_ID_NOT_EXISTS');
1307 FND_MSG_PUB.ADD;
1308 END IF;
1309 CLOSE Check_doc_info;
1310 END IF;
1311 --Check for Duplicate Record
1312 IF p_supplier_document_id IS NULL
1313 THEN
1314 OPEN dup_rec(l_supplier_id, l_document_id);
1315 FETCH dup_rec INTO l_dummy;
1316 IF dup_rec%FOUND THEN
1317 -- Perf Bug Fix 4919011.
1318 /*
1319 OPEN get_supplier_name(l_supplier_id);
1320 FETCH get_supplier_name INTO l_supplier_name;
1321 CLOSE get_supplier_name;
1322 */
1323 IF l_prod_install_status IN ('N','L') THEN
1324 OPEN get_supplier_name_hz(l_supplier_id);
1325 FETCH get_supplier_name_hz INTO l_supplier_name;
1326 CLOSE get_supplier_name_hz;
1327 ELSIF l_prod_install_status IN ('I','S') THEN
1328 OPEN get_supplier_name_po(l_supplier_id);
1329 FETCH get_supplier_name_po INTO l_supplier_name;
1330 CLOSE get_supplier_name_po;
1331 END IF;
1332 FND_MESSAGE.SET_NAME('AHL','AHL_DI_SUPPLIER_DUP_RECORD');
1333 FND_MESSAGE.SET_TOKEN('SUPNAME',l_supplier_name);
1334 FND_MSG_PUB.ADD;
1335 END IF;
1336 CLOSE dup_rec;
1337 END IF;
1338 END IF;
1339
1340 END VALIDATE_SUPPLIER;
1341
1342 /*--------------------------------------------------*/
1343 /* procedure name: create_supplier */
1344 /* description : Creates new supplier record */
1345 /* for an associated document */
1346 /* */
1347 /*--------------------------------------------------*/
1348 PROCEDURE CREATE_SUPPLIER
1349 (
1350 p_api_version IN NUMBER := 1.0 ,
1351 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
1352 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
1353 p_validate_only IN VARCHAR2 := FND_API.G_TRUE ,
1354 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1355 p_x_supplier_tbl IN OUT NOCOPY supplier_tbl ,
1356 x_return_status OUT NOCOPY VARCHAR2 ,
1357 x_msg_count OUT NOCOPY NUMBER ,
1358 x_msg_data OUT NOCOPY VARCHAR2)
1359 IS
1360 -- Used to check Duplicate Record
1361 CURSOR dup_rec(c_supplier_id NUMBER,
1362 c_document_id NUMBER)
1363 IS
1364 SELECT 'X'
1365 FROM AHL_SUPPLIER_DOCUMENTS
1366 WHERE supplier_id = c_supplier_id
1367 AND document_id = c_document_id;
1368
1369 -- Perf Bug Fix 4919011.
1370 -- Replacing get_supplier_name by get_supplier_name_hz and get_supplier_name_po below
1371 /*
1372 CURSOR get_supplier_name(c_supplier_id NUMBER)
1373 IS
1374 SELECT party_number
1375 FROM AHL_HZ_PO_SUPPLIERS_V
1376 WHERE party_id =c_supplier_id;
1377 */
1378
1379 CURSOR get_supplier_name_hz(c_supplier_id NUMBER)
1380 IS
1381 SELECT party_number
1382 FROM HZ_PARTIES
1383 WHERE party_id =c_supplier_id;
1384
1385 CURSOR get_supplier_name_po(c_supplier_id NUMBER)
1386 IS
1387 SELECT SEGMENT1
1388 FROM PO_VENDORS
1389 WHERE VENDOR_ID =c_supplier_id;
1390
1391 --
1392 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_SUPPLIER';
1393 l_api_version CONSTANT NUMBER := 1.0;
1394 l_msg_count NUMBER;
1395 l_supplier_document_id NUMBER;
1396 l_dummy VARCHAR2(2000);
1397 l_supplier_name VARCHAR2(360);
1398 l_supplier_info supplier_rec;
1399 l_prod_install_status VARCHAR2(30);
1400 BEGIN
1401 -- Standard Start of API savepoint
1402 SAVEPOINT create_supplier;
1403 -- Check if API is called in debug mode. If yes, enable debug.
1404 IF G_DEBUG='Y' THEN
1405 AHL_DEBUG_PUB.enable_debug;
1406 END IF;
1407 -- Debug info.
1408 IF G_DEBUG='Y' THEN
1409 AHL_DEBUG_PUB.debug( 'enter ahl_di_doc_index_pvt.Create Supplier','+SUP+');
1410 END IF;
1411
1412 -- Standard call to check for call compatibility.
1413 IF FND_API.to_boolean(p_init_msg_list)
1414 THEN
1415 FND_MSG_PUB.initialize;
1416 END IF;
1417 -- Initialize API return status to success
1418 x_return_status := 'S';
1419 -- Initialize message list if p_init_msg_list is set to TRUE.
1420 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
1421 p_api_version,
1422 l_api_name,G_PKG_NAME)
1423 THEN
1424 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1425 END IF;
1426
1427 -- Perf Bug Fix 4919011.
1428 BEGIN
1429 IF G_DEBUG='Y' THEN
1430 AHL_DEBUG_PUB.debug( 'Fetching Installation Status of P0','+SUP+');
1431 END IF;
1432 SELECT AHL_DI_DOC_INDEX_PVT.GET_PRODUCT_INSTALL_STATUS('PO')
1433 INTO l_prod_install_status
1434 FROM DUAL;
1435 END;
1436
1437 --Start API Body
1438 IF p_x_supplier_tbl.COUNT > 0
1439 THEN
1440 FOR i IN p_x_supplier_tbl.FIRST..p_x_supplier_tbl.LAST
1441 LOOP
1442 VALIDATE_SUPPLIER
1443 (
1444 p_supplier_document_id => p_x_supplier_tbl(i).supplier_document_id,
1445 p_supplier_id => p_x_supplier_tbl(i).supplier_id,
1446 p_document_id => p_x_supplier_tbl(i).document_id,
1447 p_preference_code => p_x_supplier_tbl(i).preference_code,
1448 p_delete_flag => p_x_supplier_tbl(i).delete_flag
1449 );
1450 END LOOP;
1451 -- Standard call to get message count and if count is get message info.
1452 l_msg_count := FND_MSG_PUB.count_msg;
1453
1454 IF l_msg_count > 0 THEN
1455 X_msg_count := l_msg_count;
1456 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1457 RAISE FND_API.G_EXC_ERROR;
1458 END IF;
1459
1460 FOR i IN p_x_supplier_tbl.FIRST..p_x_supplier_tbl.LAST
1461 LOOP
1462 IF (p_x_supplier_tbl(i).supplier_document_id IS NULL)
1463 THEN
1464 --The following conditions are required for null columns
1465 l_supplier_info.preference_code := p_x_supplier_tbl(i).preference_code;
1466 l_supplier_info.attribute_category := p_x_supplier_tbl(i).attribute_category;
1467 l_supplier_info.attribute1 := p_x_supplier_tbl(i).attribute1;
1468 l_supplier_info.attribute2 := p_x_supplier_tbl(i).attribute2;
1469 l_supplier_info.attribute3 := p_x_supplier_tbl(i).attribute3;
1470 l_supplier_info.attribute4 := p_x_supplier_tbl(i).attribute4;
1471 l_supplier_info.attribute5 := p_x_supplier_tbl(i).attribute5;
1472 l_supplier_info.attribute6 := p_x_supplier_tbl(i).attribute6;
1473 l_supplier_info.attribute7 := p_x_supplier_tbl(i).attribute7;
1474 l_supplier_info.attribute8 := p_x_supplier_tbl(i).attribute8;
1475 l_supplier_info.attribute9 := p_x_supplier_tbl(i).attribute9;
1476 l_supplier_info.attribute10 := p_x_supplier_tbl(i).attribute10;
1477 l_supplier_info.attribute11 := p_x_supplier_tbl(i).attribute11;
1478 l_supplier_info.attribute12 := p_x_supplier_tbl(i).attribute12;
1479 l_supplier_info.attribute13 := p_x_supplier_tbl(i).attribute13;
1480 l_supplier_info.attribute14 := p_x_supplier_tbl(i).attribute14;
1481 l_supplier_info.attribute15 := p_x_supplier_tbl(i).attribute15;
1482 -- check for duplicate records
1483 OPEN dup_rec(p_x_supplier_tbl(i).supplier_id,
1484 p_x_supplier_tbl(i).document_id);
1485 FETCH dup_rec INTO l_dummy;
1486 IF dup_rec%FOUND THEN
1487 -- Perf Bug Fix 4919011.
1488 /*
1489 OPEN get_supplier_name(p_x_supplier_tbl(i).supplier_id);
1490 FETCH get_supplier_name INTO l_supplier_name;
1491 CLOSE get_supplier_name;
1492 */
1493 IF l_prod_install_status IN ('N','L') THEN
1494 OPEN get_supplier_name_hz(p_x_supplier_tbl(i).supplier_id);
1495 FETCH get_supplier_name_hz INTO l_supplier_name;
1496 CLOSE get_supplier_name_hz;
1497 ELSIF l_prod_install_status IN ('I','S') THEN
1498 OPEN get_supplier_name_po(p_x_supplier_tbl(i).supplier_id);
1499 FETCH get_supplier_name_po INTO l_supplier_name;
1500 CLOSE get_supplier_name_po;
1501 END IF;
1502 FND_MESSAGE.SET_NAME('AHL','AHL_DI_SUPPLIER_DUP_RECORD');
1503 FND_MESSAGE.SET_TOKEN('SUPNAME',l_supplier_name);
1504 FND_MSG_PUB.ADD;
1505
1506 --AD RAISE FND_API.G_EXC_ERROR;
1507 --AD END IF;
1508 --ad CLOSE dup_rec;
1509
1510 ELSE
1511 --Retrieve the sequence number
1512 SELECT AHL_SUPPLIER_DOCUMENTS_S.Nextval INTO
1513 l_supplier_document_id from DUAL;
1514 --Insert the record into supplier documents table
1515 INSERT INTO AHL_SUPPLIER_DOCUMENTS
1516 (
1517 SUPPLIER_DOCUMENT_ID,
1518 SUPPLIER_ID,
1519 DOCUMENT_ID,
1520 PREFERENCE_CODE,
1521 OBJECT_VERSION_NUMBER,
1522 ATTRIBUTE_CATEGORY,
1523 ATTRIBUTE1,
1524 ATTRIBUTE2,
1525 ATTRIBUTE3,
1526 ATTRIBUTE4,
1527 ATTRIBUTE5,
1528 ATTRIBUTE6,
1529 ATTRIBUTE7,
1530 ATTRIBUTE8,
1531 ATTRIBUTE9,
1532 ATTRIBUTE10,
1533 ATTRIBUTE11,
1534 ATTRIBUTE12,
1535 ATTRIBUTE13,
1536 ATTRIBUTE14,
1537 ATTRIBUTE15,
1538 LAST_UPDATE_DATE,
1539 LAST_UPDATED_BY,
1540 CREATION_DATE,
1541 CREATED_BY,
1542 LAST_UPDATE_LOGIN
1543 )
1544 VALUES
1545 (
1546 l_supplier_document_id,
1547 p_x_supplier_tbl(i).supplier_id,
1548 p_x_supplier_tbl(i).document_id,
1549 l_supplier_info.preference_code,
1550 1,
1551 l_supplier_info.attribute_category,
1552 l_supplier_info.attribute1,
1553 l_supplier_info.attribute2,
1554 l_supplier_info.attribute3,
1555 l_supplier_info.attribute4,
1556 l_supplier_info.attribute5,
1557 l_supplier_info.attribute6,
1558 l_supplier_info.attribute7,
1559 l_supplier_info.attribute8,
1560 l_supplier_info.attribute9,
1561 l_supplier_info.attribute10,
1562 l_supplier_info.attribute11,
1563 l_supplier_info.attribute12,
1564 l_supplier_info.attribute13,
1565 l_supplier_info.attribute14,
1566 l_supplier_info.attribute15,
1567 sysdate,
1568 fnd_global.user_id,
1569 sysdate,
1570 fnd_global.user_id,
1571 fnd_global.login_id
1572 );
1573 p_x_supplier_tbl(i).supplier_document_id := l_supplier_document_id;
1574 END IF;--ad
1575 CLOSE dup_rec;--ad
1576
1577 /*
1578 --{{adharia comment
1579 l_msg_count := FND_MSG_PUB.count_msg;
1580 IF l_msg_count > 0 THEN
1581 X_msg_count := l_msg_count;
1582 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1583 RAISE FND_API.G_EXC_ERROR;
1584 END IF;
1585 --{{adharia comment
1586 */
1587 END IF;
1588 END LOOP;
1589 END IF;
1590
1591 --{{adharia added
1592 l_msg_count := FND_MSG_PUB.count_msg;
1593 IF l_msg_count > 0 THEN
1594 X_msg_count := l_msg_count;
1595 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1596 RAISE FND_API.G_EXC_ERROR;
1597 END IF;
1598 --{{adharia
1599
1600 -- Standard check of p_commit.
1601 IF FND_API.TO_BOOLEAN(p_commit) THEN
1602 COMMIT;
1603 END IF;
1604 -- Debug info
1605 IF G_DEBUG='Y' THEN
1606 AHL_DEBUG_PUB.debug( 'End of private api Create Supplier','+SUP+');
1607
1608 END IF;
1609 -- Check if API is called in debug mode. If yes, disable debug.
1610 IF G_DEBUG='Y' THEN
1611 AHL_DEBUG_PUB.disable_debug;
1612
1613 END IF;
1614
1615 EXCEPTION
1616 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1617 ROLLBACK TO create_supplier;
1618 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1619 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1620 p_count => x_msg_count,
1621 p_data => x_msg_data);
1622
1623 IF G_DEBUG='Y' THEN
1624 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'ERROR' );
1625 AHL_DEBUG_PUB.debug( 'ahl_di_doc_index_pvt.Create Supplier','+SUP+');
1626
1627
1628 -- Check if API is called in debug mode. If yes, disable debug.
1629
1630 AHL_DEBUG_PUB.disable_debug;
1631
1632 END IF;
1633
1634 WHEN FND_API.G_EXC_ERROR THEN
1635 ROLLBACK TO create_supplier;
1636 X_return_status := FND_API.G_RET_STS_ERROR;
1637 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1638 p_count => x_msg_count,
1639 p_data => X_msg_data);
1640 -- Debug info.
1641 IF G_DEBUG='Y' THEN
1642 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
1643 AHL_DEBUG_PUB.debug( 'ahl_di_doc_index_pub.Create Supplier','+SUP+');
1644
1645
1646 -- Check if API is called in debug mode. If yes, disable debug.
1647 AHL_DEBUG_PUB.disable_debug;
1648
1649 END IF;
1650
1651 WHEN OTHERS THEN
1652 ROLLBACK TO create_supplier;
1653 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1654 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1655 THEN
1656 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_DI_DOC_INDEX_PVT',
1657 p_procedure_name => 'CREATE_SUPPLIER',
1658 p_error_text => SUBSTR(SQLERRM,1,240));
1659 END IF;
1660 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1661 p_count => x_msg_count,
1662 p_data => X_msg_data);
1663
1664 -- Debug info.
1665 IF G_DEBUG='Y' THEN
1666 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'SQL ERROR' );
1667 AHL_DEBUG_PUB.debug( 'ahl_di_doc_index_pvt.Create Supplier','+SUP+');
1668
1669
1670 -- Check if API is called in debug mode. If yes, disable debug.
1671 AHL_DEBUG_PUB.disable_debug;
1672
1673 END IF;
1674
1675 END CREATE_SUPPLIER;
1676 /*------------------------------------------------------*/
1677 /* procedure name: modify_supplier */
1678 /* description : Update the existing supplier record */
1679 /* for an associated document */
1680 /* */
1681 /*------------------------------------------------------*/
1682
1683 PROCEDURE MODIFY_SUPPLIER
1684 (
1685 p_api_version IN NUMBER := 1.0 ,
1686 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
1687 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
1688 p_validate_only IN VARCHAR2 := FND_API.G_TRUE ,
1689 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1690 p_supplier_tbl IN supplier_tbl ,
1691 x_return_status OUT NOCOPY VARCHAR2 ,
1692 x_msg_count OUT NOCOPY NUMBER ,
1693 x_msg_data OUT NOCOPY VARCHAR2
1694 )
1695 IS
1696 -- To get the supplier info
1697 CURSOR get_supplier_rec_info(c_supplier_document_id NUMBER)
1698 IS
1699 SELECT ROWID,
1700 supplier_id,
1701 document_id,
1702 preference_code,
1703 attribute_category,
1704 attribute1,
1705 attribute2,
1706 attribute3,
1707 attribute4,
1708 attribute5,
1709 attribute6,
1710 attribute7,
1711 attribute8,
1712 attribute9,
1713 attribute10,
1714 attribute11,
1715 attribute12,
1716 attribute13,
1717 attribute14,
1718 attribute15,
1719 object_version_number
1720 FROM AHL_SUPPLIER_DOCUMENTS
1721 WHERE supplier_document_id = c_supplier_document_id
1722 FOR UPDATE OF object_version_number NOWAIT;
1723
1724 -- Perf Bug Fix 4919011.
1725 -- Replacing get_supplier_name by get_supplier_name_hz and get_supplier_name_po below
1726 /*
1727 CURSOR get_supplier_name(c_supplier_id NUMBER)
1728 IS
1729 SELECT party_number
1730 FROM AHL_HZ_PO_SUPPLIERS_V
1731 WHERE party_id =c_supplier_id;
1732 */
1733
1734 CURSOR get_supplier_name_hz(c_supplier_id NUMBER)
1735 IS
1736 SELECT party_number
1737 FROM HZ_PARTIES
1738 WHERE party_id =c_supplier_id;
1739
1740 CURSOR get_supplier_name_po(c_supplier_id NUMBER)
1741 IS
1742 SELECT SEGMENT1
1743 FROM PO_VENDORS
1744 WHERE VENDOR_ID =c_supplier_id;
1745
1746
1747 --
1748 l_api_name CONSTANT VARCHAR2(30) := 'MODIFY_SUPPLIER';
1749 l_api_version CONSTANT NUMBER := 1.0;
1750 l_msg_count NUMBER;
1751 l_num_rec NUMBER;
1752 l_rowid ROWID;
1753 l_supplier_name VARCHAR2(390);
1754 l_supplier_info get_supplier_rec_info%ROWTYPE;
1755 l_prod_install_status VARCHAR2(30);
1756 BEGIN
1757 -- Standard Start of API savepoint
1758 SAVEPOINT modify_supplier;
1759 -- Check if API is called in debug mode. If yes, enable debug.
1760 IF G_DEBUG='Y' THEN
1761 AHL_DEBUG_PUB.enable_debug;
1762
1763 END IF;
1764 -- Debug info.
1765 IF G_DEBUG='Y' THEN
1766 AHL_DEBUG_PUB.debug( 'anand enter ahl_di_doc_index_pvt.Modify Supplier','+SUP+');
1767 END IF;
1768
1769 -- Standard call to check for call compatibility.
1770 IF FND_API.to_boolean(p_init_msg_list)
1771 THEN
1772 FND_MSG_PUB.initialize;
1773 END IF;
1774 -- Initialize API return status to success
1775 x_return_status := 'S';
1776 -- Initialize message list if p_init_msg_list is set to TRUE.
1777 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
1778 p_api_version,
1779 l_api_name,G_PKG_NAME)
1780 THEN
1781 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1782 END IF;
1783 -- Start API Body
1784
1785 -- Perf Bug Fix 4919011.
1786 BEGIN
1787 IF G_DEBUG='Y' THEN
1788 AHL_DEBUG_PUB.debug( 'Fetching Installation Status of PO','+SUP+');
1789 END IF;
1790 SELECT AHL_DI_DOC_INDEX_PVT.GET_PRODUCT_INSTALL_STATUS('PO')
1791 INTO l_prod_install_status
1792 FROM DUAL;
1793 END;
1794
1795 IF p_supplier_tbl.COUNT > 0
1796 THEN
1797 FOR i IN p_supplier_tbl.FIRST..p_supplier_tbl.LAST
1798 LOOP
1799 -- Calling validate suppliers
1800 --ad
1801 IF G_DEBUG='Y' THEN
1802 IF G_DEBUG='Y' THEN
1803 AHL_DEBUG_PUB.debug( ' anand enter ahl_di_doc_index_pvt.Modify Supplier before validate supplier ','+SUP+');
1804
1805 END IF;
1806 END IF;
1807 --ad
1808 VALIDATE_SUPPLIER
1809 (
1810 p_supplier_document_id => p_supplier_tbl(i).supplier_document_id,
1811 p_supplier_id => p_supplier_tbl(i).supplier_id,
1812 p_document_id => p_supplier_tbl(i).document_id,
1813 p_preference_code => p_supplier_tbl(i).preference_code,
1814 p_delete_flag => p_supplier_tbl(i).delete_flag
1815 );
1816 END LOOP;
1817 --End of Validations
1818 -- Standard call to get message count
1819 l_msg_count := FND_MSG_PUB.count_msg;
1820 --ad
1821 IF G_DEBUG='Y' THEN
1822 IF G_DEBUG='Y' THEN
1823 AHL_DEBUG_PUB.debug( ' anand enter ahl_di_doc_index_pvt.Modify Supplier after validate sup '||l_msg_count,'+SUP+');
1824
1825 END IF;
1826 END IF;
1827 --ad
1828
1829
1830 IF l_msg_count > 0 THEN
1831 X_msg_count := l_msg_count;
1832 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1833 RAISE FND_API.G_EXC_ERROR;
1834 END IF;
1835 FOR i IN p_supplier_tbl.FIRST..p_supplier_tbl.LAST
1836 LOOP
1837 --Retrieve the existing supplier record
1838 OPEN get_supplier_rec_info(p_supplier_tbl(i).supplier_document_id);
1839 FETCH get_supplier_rec_info INTO l_supplier_info;
1840 CLOSE get_supplier_rec_info;
1841
1842 -- This condition will take care of lost update data bug when concurrent users are
1843 -- updating same record...02/05/02
1844 IF l_supplier_info.object_version_number <>p_supplier_tbl(i).object_version_number
1845 THEN
1846 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
1847 FND_MSG_PUB.ADD;
1848 --AD RAISE FND_API.G_EXC_ERROR;
1849 ELSE --AD
1850 -- The following conditions compare the new record value with old record
1851 -- value, if its different then assign the new value else continue
1852 IF p_supplier_tbl(i).supplier_document_id IS NOT NULL
1853 AND p_supplier_tbl(i).delete_flag <> 'Y'
1854 THEN
1855 l_supplier_info.supplier_id := p_supplier_tbl(i).supplier_id;
1856 l_supplier_info.document_id := p_supplier_tbl(i).document_id;
1857 l_supplier_info.preference_code := p_supplier_tbl(i).preference_code;
1858 l_supplier_info.attribute_category := p_supplier_tbl(i).attribute_category;
1859 l_supplier_info.attribute1 := p_supplier_tbl(i).attribute1;
1860 l_supplier_info.attribute2 := p_supplier_tbl(i).attribute2;
1861 l_supplier_info.attribute3 := p_supplier_tbl(i).attribute3;
1862 l_supplier_info.attribute4 := p_supplier_tbl(i).attribute4;
1863 l_supplier_info.attribute5 := p_supplier_tbl(i).attribute5;
1864 l_supplier_info.attribute6 := p_supplier_tbl(i).attribute6;
1865 l_supplier_info.attribute7 := p_supplier_tbl(i).attribute7;
1866 l_supplier_info.attribute8 := p_supplier_tbl(i).attribute8;
1867 l_supplier_info.attribute9 := p_supplier_tbl(i).attribute9;
1868 l_supplier_info.attribute10 := p_supplier_tbl(i).attribute10;
1869 l_supplier_info.attribute11 := p_supplier_tbl(i).attribute11;
1870 l_supplier_info.attribute12 := p_supplier_tbl(i).attribute12;
1871 l_supplier_info.attribute13 := p_supplier_tbl(i).attribute13;
1872 l_supplier_info.attribute14 := p_supplier_tbl(i).attribute14;
1873 l_supplier_info.attribute15 := p_supplier_tbl(i).attribute15;
1874 -- Perf Bug Fix 4919011.
1875 /*
1876 OPEN get_supplier_name(l_supplier_info.supplier_id);
1877 FETCH get_supplier_name INTO l_supplier_name;
1878 CLOSE get_supplier_name;
1879 */
1880 IF l_prod_install_status IN ('N','L') THEN
1881 OPEN get_supplier_name_hz(l_supplier_info.supplier_id);
1882 FETCH get_supplier_name_hz INTO l_supplier_name;
1883 CLOSE get_supplier_name_hz;
1884 ELSIF l_prod_install_status IN ('I','S') THEN
1885 OPEN get_supplier_name_po(l_supplier_info.supplier_id);
1886 FETCH get_supplier_name_po INTO l_supplier_name;
1887 CLOSE get_supplier_name_po;
1888 END IF;
1889
1890
1891
1892 --Updates the supplier table
1893 UPDATE AHL_SUPPLIER_DOCUMENTS
1894 SET supplier_id = l_supplier_info.supplier_id,
1895 document_id = l_supplier_info.document_id,
1896 preference_code = l_supplier_info.preference_code,
1897 object_version_number = l_supplier_info.object_version_number+1,
1898 attribute_category = l_supplier_info.attribute_category,
1899 attribute1 = l_supplier_info.attribute1,
1900 attribute2 = l_supplier_info.attribute2,
1901 attribute3 = l_supplier_info.attribute3,
1902 attribute4 = l_supplier_info.attribute4,
1903 attribute5 = l_supplier_info.attribute5,
1904 attribute6 = l_supplier_info.attribute6,
1905 attribute7 = l_supplier_info.attribute7,
1906 attribute8 = l_supplier_info.attribute8,
1907 attribute9 = l_supplier_info.attribute9,
1908 attribute10 = l_supplier_info.attribute10,
1909 attribute11 = l_supplier_info.attribute11,
1910 attribute12 = l_supplier_info.attribute12,
1911 attribute13 = l_supplier_info.attribute13,
1912 attribute14 = l_supplier_info.attribute14,
1913 attribute15 = l_supplier_info.attribute15,
1914 last_update_date = sysdate,
1915 last_updated_by = fnd_global.user_id,
1916 last_update_login = fnd_global.login_id
1917 WHERE ROWID = l_supplier_info.rowid;
1918 END IF;
1919 --Incase of delete supplier record
1920 IF (p_supplier_tbl(i).supplier_document_id IS NOT NULL AND
1921 p_supplier_tbl(i).delete_flag = 'Y' )
1922 THEN
1923 DELETE_SUPPLIER
1924 (
1925 p_api_version => 1.0 ,
1926 p_init_msg_list => FND_API.G_FALSE ,
1927 p_commit => FND_API.G_FALSE ,
1928 p_validate_only => FND_API.G_TRUE ,
1929 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1930 p_supplier_rec => p_supplier_tbl(i) ,
1931 x_return_status => x_return_status ,
1932 x_msg_count => x_msg_count ,
1933 x_msg_data => x_msg_data
1934 );
1935 END IF;
1936
1937
1938 END IF;--AD IF THERE IS ERROR DONT INSERT
1939
1940 END LOOP;
1941 END IF;
1942 --{{ADHARIA
1943
1944 l_msg_count := FND_MSG_PUB.count_msg;
1945 --ad
1946 IF G_DEBUG='Y' THEN
1947 IF G_DEBUG='Y' THEN
1948 AHL_DEBUG_PUB.debug( ' anand enter ahl_di_doc_index_pvt.Modify Supplier after modify sup '||l_msg_count,'+SUP+');
1949
1950 END IF;
1951 END IF;
1952 --ad
1953 IF l_msg_count > 0 THEN
1954 X_msg_count := l_msg_count;
1955 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1956 RAISE FND_API.G_EXC_ERROR;
1957 END IF;
1958 --{{ADHARIA
1959
1960 -- Standard check of p_commit.
1961 IF FND_API.TO_BOOLEAN(p_commit) THEN
1962 COMMIT;
1963 END IF;
1964
1965 -- Debug info
1966 IF G_DEBUG='Y' THEN
1967 AHL_DEBUG_PUB.debug( 'End of private api Modify Supplier','+SUP+');
1968
1969 END IF;
1970 -- Check if API is called in debug mode. If yes, disable debug.
1971 IF G_DEBUG='Y' THEN
1972 AHL_DEBUG_PUB.disable_debug;
1973
1974 END IF;
1975
1976 EXCEPTION
1977 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1978 ROLLBACK TO modify_supplier;
1979 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1980 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1981 p_count => x_msg_count,
1982 p_data => x_msg_data);
1983
1984 IF G_DEBUG='Y' THEN
1985 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'ERROR' );
1986 AHL_DEBUG_PUB.debug( 'ahl_di_doc_index_pvt.Modify supplier','+SUP+');
1987
1988
1989
1990 -- Check if API is called in debug mode. If yes, disable debug.
1991 AHL_DEBUG_PUB.disable_debug;
1992
1993 END IF;
1994
1995 WHEN FND_API.G_EXC_ERROR THEN
1996 ROLLBACK TO modify_supplier;
1997 X_return_status := FND_API.G_RET_STS_ERROR;
1998 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1999 p_count => x_msg_count,
2000 p_data => X_msg_data);
2001 -- Debug info.
2002 IF G_DEBUG='Y' THEN
2003 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
2004 AHL_DEBUG_PUB.debug( 'ahl_di_doc_index_pvt.Modify Supplier','+SUP+');
2005
2006
2007
2008 -- Check if API is called in debug mode. If yes, disable debug.
2009 AHL_DEBUG_PUB.disable_debug;
2010
2011 END IF;
2012
2013 WHEN DUP_VAL_ON_INDEX THEN
2014 ROLLBACK TO modify_supplier;
2015 X_return_status := FND_API.G_RET_STS_ERROR;
2016 FND_MESSAGE.SET_NAME('AHL','AHL_DI_SUPPLIER_DUP_RECORD');
2017 FND_MESSAGE.SET_TOKEN('SUPNAME',l_supplier_name);
2018 FND_MSG_PUB.ADD;
2019 -- Check if API is called in debug mode. If yes, disable debug.
2020 IF G_DEBUG='Y' THEN
2021 AHL_DEBUG_PUB.disable_debug;
2022
2023 END IF;
2024
2025 WHEN OTHERS THEN
2026 ROLLBACK TO modify_supplier;
2027 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2028 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2029 THEN
2030 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_DI_DOC_INDEX_PVT',
2031 p_procedure_name => 'MODIFY_SUPPLIER',
2032 p_error_text => SUBSTR(SQLERRM,1,240));
2033 END IF;
2034 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2035 p_count => x_msg_count,
2036 p_data => X_msg_data);
2037
2038 -- Debug info.
2039 IF G_DEBUG='Y' THEN
2040 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'SQL ERROR' );
2041 AHL_DEBUG_PUB.debug( 'ahl_di_doc_index_pvt.Modify Supplier','+SUP+');
2042
2043
2044
2045 -- Check if API is called in debug mode. If yes, disable debug.
2046 AHL_DEBUG_PUB.disable_debug;
2047
2048 END IF;
2049
2050 END MODIFY_SUPPLIER;
2051 /*------------------------------------------------------*/
2052 /* procedure name: delete_supplier */
2053 /* description : Removes the supplier record */
2054 /* for an associated document */
2055 /* */
2056 /*------------------------------------------------------*/
2057
2058 PROCEDURE DELETE_SUPPLIER
2059 (
2060 p_api_version IN NUMBER := 1.0 ,
2061 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
2062 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
2063 p_validate_only IN VARCHAR2 := FND_API.G_TRUE ,
2064 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2065 p_supplier_rec IN supplier_rec ,
2066 x_return_status OUT NOCOPY VARCHAR2 ,
2067 x_msg_count OUT NOCOPY NUMBER ,
2068 x_msg_data OUT NOCOPY VARCHAR2)
2069 IS
2070 --To get the supplier info
2071 CURSOR get_supplier_rec_info(c_supplier_document_id NUMBER)
2072 IS
2073 SELECT ROWID ,
2074 supplier_id,
2075 document_id,
2076 object_version_number
2077 FROM AHL_SUPPLIER_DOCUMENTS
2078 WHERE supplier_document_id = c_supplier_document_id
2079 FOR UPDATE OF object_version_number NOWAIT;
2080 --Cursor to check the record exists in Subscriptions table
2081 --Cursor modified to check only active subscriptions: pjha: 16-Jul-2002
2082 CURSOR get_subc_rec(c_supplier_id NUMBER,
2083 c_document_id NUMBER)
2084 IS
2085 SELECT 'X'
2086 FROM AHL_SUBSCRIPTIONS_B
2087 WHERE document_id = c_document_id
2088 --AND subscribed_frm_party_id = c_supplier_id;
2089 AND subscribed_frm_party_id = c_supplier_id
2090 AND NVL(end_date,sysdate) >= TRUNC(sysdate);
2091
2092 -- Perf Bug Fix 4919011.
2093 -- Replacing get_supplier_name by get_supplier_name_hz and get_supplier_name_po below
2094 /*
2095 CURSOR get_supplier_name(c_supplier_id NUMBER)
2096 IS
2097 SELECT party_number
2098 FROM AHL_HZ_PO_SUPPLIERS_V
2099 WHERE party_id =c_supplier_id;
2100 */
2101
2102 CURSOR get_supplier_name_hz(c_supplier_id NUMBER)
2103 IS
2104 SELECT party_number
2105 FROM HZ_PARTIES
2106 WHERE party_id =c_supplier_id;
2107
2108 CURSOR get_supplier_name_po(c_supplier_id NUMBER)
2109 IS
2110 SELECT SEGMENT1
2111 FROM PO_VENDORS
2112 WHERE VENDOR_ID =c_supplier_id;
2113
2114 --
2115 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_SUPPLIER';
2116 l_api_version CONSTANT NUMBER := 1.0;
2117 l_rowid ROWID;
2118 l_msg_count NUMBER;
2119 l_object_version_number NUMBER;
2120 l_supplier_id NUMBER;
2121 l_document_id NUMBER;
2122 l_supplier_name VARCHAR2(30);
2123 l_dummy VARCHAR2(2000);
2124 l_prod_install_status VARCHAR2(30);
2125 BEGIN
2126 -- Standard Start of API savepoint
2127 SAVEPOINT delete_supplier;
2128 -- Check if API is called in debug mode. If yes, enable debug.
2129 IF G_DEBUG='Y' THEN
2130 AHL_DEBUG_PUB.enable_debug;
2131
2132 END IF;
2133 -- Debug info.
2134 IF G_DEBUG='Y' THEN
2135 AHL_DEBUG_PUB.debug( 'enter ahl_di_doc_index_pvt.Delete Supplier','+SUP+');
2136 END IF;
2137 -- Standard call to check for call compatibility.
2138 IF FND_API.to_boolean(p_init_msg_list)
2139 THEN
2140 FND_MSG_PUB.initialize;
2141 END IF;
2142 -- Initialize API return status to success
2143 x_return_status := 'S';
2144 -- Initialize message list if p_init_msg_list is set to TRUE.
2145 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
2146 p_api_version,
2147 l_api_name,G_PKG_NAME)
2148 THEN
2149 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2150 END IF;
2151
2152 -- Perf Bug Fix 4919011.
2153 BEGIN
2154 IF G_DEBUG='Y' THEN
2155 AHL_DEBUG_PUB.debug( 'Fetching Product Install Status for PO');
2156 END IF;
2157 SELECT AHL_DI_DOC_INDEX_PVT.GET_PRODUCT_INSTALL_STATUS('PO')
2158 INTO l_prod_install_status
2159 FROM DUAL;
2160 END;
2161
2162 --IF p_supplier_tbl.COUNT > 0
2163 --THEN
2164 OPEN get_supplier_rec_info(p_supplier_rec.supplier_document_id);
2165 l_rowid := null;
2166 l_supplier_id := 0;
2167 l_document_id := 0;
2168 l_object_version_number := 0;
2169 FETCH get_supplier_rec_info INTO l_rowid,
2170 l_supplier_id,
2171 l_document_id,
2172 l_object_version_number;
2173 IF (get_supplier_rec_info%NOTFOUND)
2174 THEN
2175 FND_MESSAGE.SET_NAME('AHL','AHL_DI_SUPPLIER_REC_INVALID');
2176 FND_MSG_PUB.ADD;
2177 END IF;
2178 CLOSE get_supplier_rec_info;
2179 -- Check for version number
2180 IF (l_object_version_number <> p_supplier_rec.object_version_number)
2181 THEN
2182 FND_MESSAGE.SET_NAME('AHL','AHL_DI_DOC_TL_REC_CHANGED');
2183 FND_MSG_PUB.ADD;
2184 END IF;
2185 --Check for Subscriptions Record
2186 IF p_supplier_rec.supplier_document_id IS NOT NULL
2187 THEN
2188 OPEN get_subc_rec(l_supplier_id,l_document_id);
2189 FETCH get_subc_rec INTO l_dummy;
2190 IF get_subc_rec%FOUND
2191 THEN
2192 -- Perf Bug Fix 4919011.
2193 /*
2194 OPEN get_supplier_name(l_supplier_id);
2195 FETCH get_supplier_name INTO l_supplier_name;
2196 CLOSE get_supplier_name;
2197 */
2198 IF l_prod_install_status IN ('N','L') THEN
2199 OPEN get_supplier_name_hz(l_supplier_id);
2200 FETCH get_supplier_name_hz INTO l_supplier_name;
2201 CLOSE get_supplier_name_hz;
2202 ELSIF l_prod_install_status IN ('I','S') THEN
2203 OPEN get_supplier_name_po(l_supplier_id);
2204 FETCH get_supplier_name_po INTO l_supplier_name;
2205 CLOSE get_supplier_name_po;
2206 END IF;
2207 FND_MESSAGE.SET_NAME('AHL','AHL_DI_SUBC_REC_EXISTS');
2208 FND_MESSAGE.SET_TOKEN('SUPNAME',l_supplier_name);
2209 FND_MSG_PUB.ADD;
2210 END IF;
2211 CLOSE get_subc_rec;
2212 END IF;
2213 -- Delete the record from suppliers table
2214 DELETE FROM AHL_SUPPLIER_DOCUMENTS
2215 WHERE ROWID = l_rowid;
2216 --END IF;
2217 -- Standard call to get message count
2218 l_msg_count := FND_MSG_PUB.count_msg;
2219
2220 IF l_msg_count > 0 THEN
2221 X_msg_count := l_msg_count;
2222 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2223 --AD RAISE FND_API.G_EXC_ERROR;
2224 END IF;
2225
2226 -- Standard check of p_commit.
2227 IF FND_API.TO_BOOLEAN(p_commit) THEN
2228 COMMIT;
2229 END IF;
2230 -- Debug info
2231 IF G_DEBUG='Y' THEN
2232 AHL_DEBUG_PUB.debug( 'End of private api Delete Supplier','+SUP+');
2233
2234 END IF;
2235 -- Check if API is called in debug mode. If yes, disable debug.
2236 IF G_DEBUG='Y' THEN
2237 AHL_DEBUG_PUB.disable_debug;
2238
2239 END IF;
2240
2241 EXCEPTION
2242 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2243 ROLLBACK TO delete_supplier;
2244 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2245 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2246 p_count => x_msg_count,
2247 p_data => x_msg_data);
2248
2249 IF G_DEBUG='Y' THEN
2250 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'ERROR' );
2251 AHL_DEBUG_PUB.debug( 'ahl_di_doc_index_pvt.delete Supplier','+SUP+');
2252
2253
2254 -- Check if API is called in debug mode. If yes, disable debug.
2255 AHL_DEBUG_PUB.disable_debug;
2256
2257 END IF;
2258
2259 WHEN FND_API.G_EXC_ERROR THEN
2260 ROLLBACK TO delete_supplier;
2261 X_return_status := FND_API.G_RET_STS_ERROR;
2262 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2263 p_count => x_msg_count,
2264 p_data => X_msg_data);
2265 -- Debug info.
2266 IF G_DEBUG='Y' THEN
2267 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
2268 AHL_DEBUG_PUB.debug( 'ahl_di_doc_index_pvt.Delete Supplier','+SUP+');
2269
2270
2271 -- Check if API is called in debug mode. If yes, disable debug.
2272 AHL_DEBUG_PUB.disable_debug;
2273
2274 END IF;
2275
2276 WHEN OTHERS THEN
2277 ROLLBACK TO delete_supplier;
2278 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2279 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2280 THEN
2281 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_DI_DOC_INDEX_PVT',
2282 p_procedure_name => 'DELETE_SUPPLIER',
2283 p_error_text => SUBSTR(SQLERRM,1,240));
2284 END IF;
2285 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2286 p_count => x_msg_count,
2287 p_data => X_msg_data);
2288
2289 -- Debug info.
2290 IF G_DEBUG='Y' THEN
2291 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'SQL ERROR' );
2292 AHL_DEBUG_PUB.debug( 'ahl_di_doc_index_pvt.Delete Supplier','+SUP+');
2293
2294
2295 -- Check if API is called in debug mode. If yes, disable debug.
2296 AHL_DEBUG_PUB.disable_debug;
2297
2298 END IF;
2299
2300 END DELETE_SUPPLIER;
2301 /*-------------------------------------------------------*/
2302 /* procedure name: validate_recipient(private procedure) */
2303 /* description : Validation checks for before inserting */
2304 /* new record as well before update */
2305 /* */
2306 /*-------------------------------------------------------*/
2307
2308 PROCEDURE VALIDATE_RECIPIENT
2309 ( p_recipient_document_id IN NUMBER ,
2310 p_recipient_party_id IN NUMBER ,
2311 p_document_id IN NUMBER ,
2312 p_object_version_number IN NUMBER ,
2313 p_delete_flag IN VARCHAR2 := 'N')
2314 IS
2315 -- Cursor to get the recipient info
2316 CURSOR get_recipient_rec_info (c_recipient_document_id NUMBER)
2317 IS
2318 SELECT recipient_party_id,
2319 document_id
2320 FROM AHL_RECIPIENT_DOCUMENTS
2321 WHERE recipient_document_id = c_recipient_document_id;
2322
2323 -- Used to validate the document id
2324 CURSOR check_doc_info(c_document_id NUMBER)
2325 IS
2326 SELECT 'X'
2327 FROM AHL_DOCUMENTS_B
2328 WHERE document_id = c_document_id;
2329
2330 --Cursor to check duplicate record
2331 CURSOR dup_rec(c_recipient_party_id NUMBER,
2332 c_document_id NUMBER)
2333 IS
2334 SELECT 'X'
2335 FROM AHL_RECIPIENT_DOCUMENTS
2336 WHERE recipient_party_id = c_recipient_party_id
2337 AND document_id = c_document_id;
2338
2339 CURSOR DUP_REC_NAME(c_recipient_party_id NUMBER)
2340 IS
2341 SELECT party_number
2342 FROM hz_parties
2343 WHERE party_id = c_recipient_party_id;
2344
2345
2346
2347 --
2348 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_RECIPIENT';
2349 l_api_version CONSTANT NUMBER := 1.0;
2350 l_dummy VARCHAR2(2000);
2351 l_recipient_party_id NUMBER;
2352 l_document_id NUMBER;
2353 l_recipient_document_id NUMBER;
2354 l_dup_rec_name varchar2(360);
2355 BEGIN
2356 --When the action is insert or update
2357 IF p_delete_flag <> 'Y'
2358 THEN
2359 IF p_recipient_document_id IS NOT NULL
2360 THEN
2361 OPEN get_recipient_rec_info(p_recipient_document_id);
2362 FETCH get_recipient_rec_info INTO l_recipient_party_id,
2363 l_document_id;
2364 CLOSE get_recipient_rec_info;
2365 END IF;
2366 --
2367 IF p_recipient_party_id IS NOT NULL
2368 THEN
2369 l_recipient_party_id := p_recipient_party_id;
2370 END IF;
2371 --
2372 IF p_document_id IS NOT NULL
2373 THEN
2374 l_document_id := p_document_id;
2375 END IF;
2376 --
2377 l_recipient_document_id := p_recipient_document_id;
2378 --This condition checks for recipient party id null
2379 IF ((p_recipient_document_id IS NULL AND
2380 p_recipient_party_id IS NULL)
2381 OR
2382 (p_recipient_document_id IS NOT NULL
2383 AND l_recipient_party_id IS NULL))
2384 THEN
2385 FND_MESSAGE.SET_NAME('AHL','AHL_DI_RECIPIENT_PTY_ID_NULL');
2386 FND_MSG_PUB.ADD;
2387 END IF;
2388 --This condition checks for Document Id
2389 IF ((p_recipient_document_id IS NULL AND
2390 p_document_id IS NULL)
2391 OR
2392 (p_recipient_document_id IS NOT NULL
2393 AND l_document_id IS NULL))
2394 THEN
2395 FND_MESSAGE.SET_NAME('AHL','AHL_DI_DOCUMENT_ID_NULL');
2396 FND_MSG_PUB.ADD;
2397 END IF;
2398 -- This condition checks for existence of document record in ahl documents table
2399 IF p_document_id IS NOT NULL
2400 THEN
2401 OPEN Check_doc_info(p_document_id);
2402 FETCH Check_doc_info INTO l_dummy;
2403 IF Check_doc_info%NOTFOUND
2404 THEN
2405 FND_MESSAGE.SET_NAME('AHL','AHL_DI_DOC_ID_NOT_EXISTS');
2406 FND_MSG_PUB.ADD;
2407 END IF;
2408 CLOSE Check_doc_info;
2409 END IF;
2410 -- Check for Duplicate Record
2411 IF p_recipient_document_id IS NULL
2412 THEN
2413 OPEN dup_rec(l_recipient_party_id, l_document_id);
2414 FETCH dup_rec INTO l_dummy;
2415 IF dup_rec%FOUND THEN
2416 OPEN DUP_REC_NAME(l_recipient_party_id);
2417 FETCH DUP_REC_NAME INTO L_DUP_REC_NAME;
2418 FND_MESSAGE.SET_NAME('AHL','AHL_DI_RECIPIENT_DUP_RECORD');
2419 FND_MESSAGE.SET_TOKEN('RECPTID',l_DUP_REC_NAME);
2420 FND_MSG_PUB.ADD;
2421 CLOSE DUP_REC_NAME;
2422 END IF;
2423 CLOSE dup_rec;
2424 END IF;
2425 END IF;
2426
2427 END VALIDATE_RECIPIENT;
2428 /*------------------------------------------------------*/
2429 /* procedure name: create_recipient */
2430 /* description : Creates new recipient record */
2431 /* for an associated document */
2432 /* */
2433 /*------------------------------------------------------*/
2434
2435 PROCEDURE CREATE_RECIPIENT
2436 (
2437 p_api_version IN NUMBER := 1.0 ,
2438 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
2439 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
2440 p_validate_only IN VARCHAR2 := FND_API.G_TRUE ,
2441 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2442 p_x_recipient_tbl IN OUT NOCOPY recipient_tbl ,
2443 x_return_status OUT NOCOPY VARCHAR2 ,
2444 x_msg_count OUT NOCOPY NUMBER ,
2445 x_msg_data OUT NOCOPY VARCHAR2)
2446 IS
2447 --Check for same record multiple times
2448 CURSOR dup_rec(c_recipient_party_id NUMBER,
2449 c_document_id NUMBER)
2450 IS
2451 SELECT 'X'
2452 FROM AHL_RECIPIENT_DOCUMENTS
2453 WHERE recipient_party_id = c_recipient_party_id
2454 AND document_id = c_document_id;
2455
2456 CURSOR DUP_REC_NAME(c_recipient_party_id NUMBER)
2457 IS
2458 SELECT party_number
2459 FROM hz_parties
2460 WHERE party_id = c_recipient_party_id;
2461
2462 l_dup_rec_name varchar2(360);
2463 --
2464 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_RECIPIENT';
2465 l_api_version CONSTANT NUMBER := 1.0;
2466 l_msg_count NUMBER;
2467 l_dummy VARCHAR2(2000);
2468 l_recipient_document_id NUMBER;
2469 l_recipient_info Recipient_rec;
2470 BEGIN
2471 -- Standard Start of API savepoint
2472 SAVEPOINT create_recipient;
2473 -- Check if API is called in debug mode. If yes, enable debug.
2474 IF G_DEBUG='Y' THEN
2475 AHL_DEBUG_PUB.enable_debug;
2476
2477 END IF;
2478 -- Debug info.
2479 IF G_DEBUG='Y' THEN
2480 IF G_DEBUG='Y' THEN
2481 AHL_DEBUG_PUB.debug( 'enter ahl_di_doc_index_pvt.Create Recipient','+REP+');
2482
2483 END IF;
2484 END IF;
2485 -- Standard call to check for call compatibility.
2486 IF FND_API.to_boolean(p_init_msg_list)
2487 THEN
2488 FND_MSG_PUB.initialize;
2489 END IF;
2490 -- Initialize API return status to success
2491 x_return_status := 'S';
2492 -- Initialize message list if p_init_msg_list is set to TRUE.
2493 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
2494 p_api_version,
2495 l_api_name,G_PKG_NAME)
2496 THEN
2497 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2498 END IF;
2499 --Start API Body
2500 IF p_x_recipient_tbl.COUNT > 0
2501 THEN
2502 FOR i IN p_x_recipient_tbl.FIRST..p_x_recipient_tbl.LAST
2503 LOOP
2504 VALIDATE_RECIPIENT
2505 (
2506 p_recipient_document_id => p_x_recipient_tbl(i).recipient_document_id,
2507 p_recipient_party_id => p_x_recipient_tbl(i).recipient_party_id,
2508 p_document_id => p_x_recipient_tbl(i).document_id,
2509 p_object_version_number => p_x_recipient_tbl(i).object_version_number,
2510 p_delete_flag => p_x_recipient_tbl(i).delete_flag);
2511 END LOOP;
2512 -- Standard call to get message count and if count is get message info.
2513 l_msg_count := FND_MSG_PUB.count_msg;
2514
2515 IF l_msg_count > 0 THEN
2516 X_msg_count := l_msg_count;
2517 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2518 RAISE FND_API.G_EXC_ERROR;
2519 END IF;
2520
2521 FOR i IN p_x_recipient_tbl.FIRST..p_x_recipient_tbl.LAST
2522 LOOP
2523 IF p_x_recipient_tbl(i).recipient_document_id IS NULL
2524 THEN
2525 --
2526 l_recipient_info.attribute_category := p_x_recipient_tbl(i).attribute_category;
2527 l_recipient_info.attribute1 := p_x_recipient_tbl(i).attribute1;
2528 l_recipient_info.attribute2 := p_x_recipient_tbl(i).attribute2;
2529 l_recipient_info.attribute3 := p_x_recipient_tbl(i).attribute3;
2530 l_recipient_info.attribute4 := p_x_recipient_tbl(i).attribute4;
2531 l_recipient_info.attribute5 := p_x_recipient_tbl(i).attribute5;
2532 l_recipient_info.attribute6 := p_x_recipient_tbl(i).attribute6;
2533 l_recipient_info.attribute7 := p_x_recipient_tbl(i).attribute7;
2534 l_recipient_info.attribute8 := p_x_recipient_tbl(i).attribute8;
2535 l_recipient_info.attribute9 := p_x_recipient_tbl(i).attribute9;
2536 l_recipient_info.attribute10 := p_x_recipient_tbl(i).attribute10;
2537 l_recipient_info.attribute11 := p_x_recipient_tbl(i).attribute11;
2538 l_recipient_info.attribute12 := p_x_recipient_tbl(i).attribute12;
2539 l_recipient_info.attribute13 := p_x_recipient_tbl(i).attribute13;
2540 l_recipient_info.attribute14 := p_x_recipient_tbl(i).attribute14;
2541 l_recipient_info.attribute15 := p_x_recipient_tbl(i).attribute15;
2542
2543 --Check for duplication
2544 OPEN dup_rec(p_x_recipient_tbl(i).recipient_party_id,
2545 p_x_recipient_tbl(i).document_id);
2546 FETCH dup_rec INTO l_dummy;
2547 IF dup_rec%FOUND THEN
2548 OPEN DUP_REC_NAME(p_x_recipient_tbl(i).recipient_party_id);
2549 FETCH DUP_REC_NAME INTO L_DUP_REC_NAME;
2550 FND_MESSAGE.SET_NAME('AHL','AHL_DI_RECIPIENT_DUP_RECORD');
2551 FND_MESSAGE.SET_TOKEN('RECPTID',l_DUP_REC_NAME);
2552 FND_MSG_PUB.ADD;
2553 CLOSE DUP_REC_NAME;
2554 --ad RAISE FND_API.G_EXC_ERROR;
2555 --ad END IF;
2556 --ad CLOSE dup_rec;
2557 else --ad
2558 --Retrieves the sequence number
2559 SELECT AHL_RECIPIENT_DOCUMENTS_S.Nextval INTO
2560 l_recipient_document_id from DUAL;
2561 --Insert the record into recipient documents
2562 INSERT INTO AHL_RECIPIENT_DOCUMENTS
2563 (
2564 RECIPIENT_DOCUMENT_ID,
2565 RECIPIENT_PARTY_ID,
2566 DOCUMENT_ID,
2567 OBJECT_VERSION_NUMBER,
2568 ATTRIBUTE_CATEGORY,
2569 ATTRIBUTE1,
2570 ATTRIBUTE2,
2571 ATTRIBUTE3,
2572 ATTRIBUTE4,
2573 ATTRIBUTE5,
2574 ATTRIBUTE6,
2575 ATTRIBUTE7,
2576 ATTRIBUTE8,
2577 ATTRIBUTE9,
2578 ATTRIBUTE10,
2579 ATTRIBUTE11,
2580 ATTRIBUTE12,
2581 ATTRIBUTE13,
2582 ATTRIBUTE14,
2583 ATTRIBUTE15,
2584 LAST_UPDATE_DATE,
2585 LAST_UPDATED_BY,
2586 CREATION_DATE,
2587 CREATED_BY,
2588 LAST_UPDATE_LOGIN
2589 )
2590 VALUES
2591 (
2592 l_recipient_document_id,
2593 p_x_recipient_tbl(i).recipient_party_id,
2594 p_x_recipient_tbl(i).document_id,
2595 1,
2596 l_recipient_info.attribute_category,
2597 l_recipient_info.attribute1,
2598 l_recipient_info.attribute2,
2599 l_recipient_info.attribute3,
2600 l_recipient_info.attribute4,
2601 l_recipient_info.attribute5,
2602 l_recipient_info.attribute6,
2603 l_recipient_info.attribute7,
2604 l_recipient_info.attribute8,
2605 l_recipient_info.attribute9,
2606 l_recipient_info.attribute10,
2607 l_recipient_info.attribute11,
2608 l_recipient_info.attribute12,
2609 l_recipient_info.attribute13,
2610 l_recipient_info.attribute14,
2611 l_recipient_info.attribute15,
2612 sysdate,
2613 fnd_global.user_id,
2614 sysdate,
2615 fnd_global.user_id,
2616 fnd_global.login_id
2617 );
2618 p_x_recipient_tbl(i).recipient_document_id := l_recipient_document_id;
2619 -- Standard check to count messages
2620 /*adharia
2621 l_msg_count := FND_MSG_PUB.count_msg;
2622
2623 IF l_msg_count > 0 THEN
2624 X_msg_count := l_msg_count;
2625 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2626 RAISE FND_API.G_EXC_ERROR;
2627 END IF;--ad
2628 adharia*/
2629 END IF;
2630 CLOSE dup_rec;--ad
2631 END IF;
2632 END LOOP;
2633 END IF;
2634 --adharia
2635 l_msg_count := FND_MSG_PUB.count_msg;
2636
2637 IF l_msg_count > 0 THEN
2638 X_msg_count := l_msg_count;
2639 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2640 RAISE FND_API.G_EXC_ERROR;
2641 end if;
2642 --adharia
2643
2644 -- Standard check of p_commit.
2645 IF FND_API.TO_BOOLEAN(p_commit) THEN
2646 COMMIT;
2647 END IF;
2648 -- Debug info
2649 IF G_DEBUG='Y' THEN
2650 AHL_DEBUG_PUB.debug( 'End of private api Create Recipient','+REP+');
2651
2652 END IF;
2653 -- Check if API is called in debug mode. If yes, disable debug.
2654 IF G_DEBUG='Y' THEN
2655 AHL_DEBUG_PUB.disable_debug;
2656
2657 END IF;
2658
2659 EXCEPTION
2660 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2661 ROLLBACK TO create_recipient;
2662 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2663 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2664 p_count => x_msg_count,
2665 p_data => x_msg_data);
2666
2667 IF G_DEBUG='Y' THEN
2668 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'ERROR' );
2669 AHL_DEBUG_PUB.debug( 'ahl_di_doc_index_pvt.Create Recipient','+REP+');
2670
2671
2672 -- Check if API is called in debug mode. If yes, disable debug.
2673 AHL_DEBUG_PUB.disable_debug;
2674
2675 END IF;
2676
2677 WHEN FND_API.G_EXC_ERROR THEN
2678 ROLLBACK TO create_recipient;
2679 X_return_status := FND_API.G_RET_STS_ERROR;
2680 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2681 p_count => x_msg_count,
2682 p_data => X_msg_data);
2683 -- Debug info.
2684 IF G_DEBUG='Y' THEN
2685 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
2686 AHL_DEBUG_PUB.debug( 'ahl_di_doc_index_pvt.Create Recipient','+REP+');
2687
2688
2689 -- Check if API is called in debug mode. If yes, disable debug.
2690 AHL_DEBUG_PUB.disable_debug;
2691
2692 END IF;
2693
2694 WHEN OTHERS THEN
2695 ROLLBACK TO create_recipient;
2696 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2697 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2698 THEN
2699 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_DI_DOC_INDEX_PVT',
2700 p_procedure_name => 'CREATE_RECIPIENT',
2701 p_error_text => SUBSTR(SQLERRM,1,240));
2702 END IF;
2703 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2704 p_count => x_msg_count,
2705 p_data => X_msg_data);
2706
2707 -- Debug info.
2708 IF G_DEBUG='Y' THEN
2709 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'SQL ERROR' );
2710 AHL_DEBUG_PUB.debug( 'ahl_di_doc_index_pvt.Create Recipient','+REP+');
2711
2712
2713 -- Check if API is called in debug mode. If yes, disable debug.
2714 AHL_DEBUG_PUB.disable_debug;
2715
2716 END IF;
2717
2718 END CREATE_RECIPIENT;
2719 /*----------------------------------------------------*/
2720 /* procedure name: modify_recipient */
2721 /* description : Update the existing recipient record*/
2722 /* for an associated document */
2723 /* */
2724 /*----------------------------------------------------*/
2725
2726 PROCEDURE MODIFY_RECIPIENT
2727 (
2728 p_api_version IN NUMBER := 1.0 ,
2729 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
2730 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
2731 p_validate_only IN VARCHAR2 := FND_API.G_TRUE ,
2732 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2733 p_recipient_tbl IN OUT NOCOPY recipient_tbl ,
2734 x_return_status OUT NOCOPY VARCHAR2 ,
2735 x_msg_count OUT NOCOPY NUMBER ,
2736 x_msg_data OUT NOCOPY VARCHAR2)
2737 IS
2738 -- To get the exisitng record
2739 CURSOR get_recipient_rec_info(c_recipient_document_id NUMBER)
2740 IS
2741 SELECT ROWID,
2742 recipient_party_id,
2743 document_id,
2744 object_version_number,
2745 attribute_category,
2746 attribute1,
2747 attribute2,
2748 attribute3,
2749 attribute4,
2750 attribute5,
2751 attribute6,
2752 attribute7,
2753 attribute8,
2754 attribute9,
2755 attribute10,
2756 attribute11,
2757 attribute12,
2758 attribute13,
2759 attribute14,
2760 attribute15
2761 FROM AHL_RECIPIENT_DOCUMENTS
2762 WHERE recipient_document_id = c_recipient_document_id
2763 FOR UPDATE OF object_version_number NOWAIT;
2764 --
2765 l_api_name CONSTANT VARCHAR2(30) := 'MODIFY_RECIPIENT';
2766 l_api_version CONSTANT NUMBER := 1.0;
2767 l_msg_count NUMBER;
2768 l_num_rec NUMBER;
2769 l_rowid ROWID;
2770 l_document_id NUMBER;
2771 l_recipient_document_id NUMBER;
2772 l_recipient_info get_recipient_rec_info%ROWTYPE;
2773 BEGIN
2774 -- Standard Start of API savepoint
2775 SAVEPOINT modify_recipient;
2776 -- Check if API is called in debug mode. If yes, enable debug.
2777
2778 IF G_DEBUG='Y' THEN
2779 AHL_DEBUG_PUB.enable_debug;
2780
2781 END IF;
2782 -- Debug info.
2783 IF G_DEBUG='Y' THEN
2784 IF G_DEBUG='Y' THEN
2785 AHL_DEBUG_PUB.debug( 'enter ahl_di_doc_index_pvt.Modify Recipient','+REP+');
2786
2787 END IF;
2788 END IF;
2789 -- Standard call to check for call compatibility.
2790 IF FND_API.to_boolean(p_init_msg_list)
2791 THEN
2792 FND_MSG_PUB.initialize;
2793 END IF;
2794 -- Initialize API return status to success
2795 x_return_status := 'S';
2796 -- Initialize message list if p_init_msg_list is set to TRUE.
2797 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
2798 p_api_version,
2799 l_api_name,G_PKG_NAME)
2800 THEN
2801 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2802 END IF;
2803 -- Start API Body
2804 IF p_recipient_tbl.COUNT > 0
2805 THEN
2806 FOR i IN p_recipient_tbl.FIRST..p_recipient_tbl.LAST
2807 LOOP
2808 -- Calling validate recipients
2809 VALIDATE_RECIPIENT
2810 ( p_recipient_document_id => p_recipient_tbl(i).recipient_document_id,
2811 p_recipient_party_id => p_recipient_tbl(i).recipient_party_id,
2812 p_document_id => p_recipient_tbl(i).document_id,
2813 p_object_version_number => p_recipient_tbl(i).object_version_number,
2814 p_delete_flag => p_recipient_tbl(i).delete_flag
2815 );
2816 END LOOP;
2817 --Standard call to count messages
2818 l_msg_count := FND_MSG_PUB.count_msg;
2819
2820 IF l_msg_count > 0 THEN
2821 X_msg_count := l_msg_count;
2822 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2823 RAISE FND_API.G_EXC_ERROR;
2824 END IF;
2825
2826 FOR i IN p_recipient_tbl.FIRST..p_recipient_tbl.LAST
2827 LOOP
2828
2829 --Retrieve the existing recipient record
2830 OPEN get_recipient_rec_info(p_recipient_tbl(i).recipient_document_id);
2831 FETCH get_recipient_rec_info INTO l_recipient_info;
2832 CLOSE get_recipient_rec_info;
2833
2834 -- This condition will take care of lost update data bug when concurrent users are
2835 -- updating same record...02/05/02
2836
2837 if (l_recipient_info.object_version_number <>p_recipient_tbl(i).object_version_number)
2838 then
2839 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
2840 FND_MSG_PUB.ADD;
2841 --ad RAISE FND_API.G_EXC_ERROR;
2842 --ad end if;
2843 else --ad
2844
2845 -- The following conditions compare the new record value with old record
2846 -- value, if its different then assign the new value else continue
2847 IF (p_recipient_tbl(i).recipient_document_id IS NOT NULL
2848 AND p_recipient_tbl(i).delete_flag <> 'Y')
2849 THEN
2850 l_recipient_info.recipient_party_id := p_recipient_tbl(i).recipient_party_id;
2851 l_recipient_info.document_id := p_recipient_tbl(i).document_id;
2852 l_recipient_info.attribute_category := p_recipient_tbl(i).attribute_category;
2853 l_recipient_info.attribute1 := p_recipient_tbl(i).attribute1;
2854 l_recipient_info.attribute2 := p_recipient_tbl(i).attribute2;
2855 l_recipient_info.attribute3 := p_recipient_tbl(i).attribute3;
2856 l_recipient_info.attribute3 := p_recipient_tbl(i).attribute3;
2857 l_recipient_info.attribute4 := p_recipient_tbl(i).attribute4;
2858 l_recipient_info.attribute5 := p_recipient_tbl(i).attribute5;
2859 l_recipient_info.attribute6 := p_recipient_tbl(i).attribute6;
2860 l_recipient_info.attribute7 := p_recipient_tbl(i).attribute7;
2861 l_recipient_info.attribute8 := p_recipient_tbl(i).attribute8;
2862 l_recipient_info.attribute9 := p_recipient_tbl(i).attribute9;
2863 l_recipient_info.attribute10 := p_recipient_tbl(i).attribute10;
2864 l_recipient_info.attribute11 := p_recipient_tbl(i).attribute11;
2865 l_recipient_info.attribute12 := p_recipient_tbl(i).attribute12;
2866 l_recipient_info.attribute13 := p_recipient_tbl(i).attribute13;
2867 l_recipient_info.attribute14 := p_recipient_tbl(i).attribute14;
2868 l_recipient_info.attribute15 := p_recipient_tbl(i).attribute15;
2869 -- update the table
2870 UPDATE AHL_RECIPIENT_DOCUMENTS
2871 SET recipient_party_id = l_recipient_info.recipient_party_id,
2872 document_id = l_recipient_info.document_id,
2873 object_version_number = l_recipient_info.object_version_number+1,
2874 attribute_category = l_recipient_info.attribute_category,
2875 attribute1 = l_recipient_info.attribute1,
2876 attribute2 = l_recipient_info.attribute2,
2877 attribute3 = l_recipient_info.attribute3,
2878 attribute4 = l_recipient_info.attribute4,
2879 attribute5 = l_recipient_info.attribute5,
2880 attribute6 = l_recipient_info.attribute6,
2881 attribute7 = l_recipient_info.attribute7,
2882 attribute8 = l_recipient_info.attribute8,
2883 attribute9 = l_recipient_info.attribute9,
2884 attribute10 = l_recipient_info.attribute10,
2885 attribute11 = l_recipient_info.attribute11,
2886 attribute12 = l_recipient_info.attribute12,
2887 attribute13 = l_recipient_info.attribute13,
2888 attribute14 = l_recipient_info.attribute14,
2889 attribute15 = l_recipient_info.attribute15,
2890 last_update_date = sysdate,
2891 last_updated_by = fnd_global.user_id,
2892 last_update_login = fnd_global.login_id
2893 WHERE ROWID = l_recipient_info.rowid;
2894
2895 --Incase of delete a recipient record
2896 ELSIF (p_recipient_tbl(i).recipient_document_id IS NOT NULL AND
2897 p_recipient_tbl(i).delete_flag = 'Y')
2898 THEN
2899 DELETE_RECIPIENT
2900 ( p_api_version => 1.0 ,
2901 p_init_msg_list => FND_API.G_FALSE ,
2902 p_commit => FND_API.G_FALSE ,
2903 p_validate_only => FND_API.G_TRUE ,
2904 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2905 p_recipient_rec => p_recipient_tbl(i) ,
2906 x_return_status => x_return_status ,
2907 x_msg_count => x_msg_count ,
2908 x_msg_data => x_msg_data);
2909 END IF;
2910 end if;--ad
2911 END LOOP;
2912 END IF;
2913 -- Standard check of p_commit.
2914 IF FND_API.TO_BOOLEAN(p_commit) THEN
2915 COMMIT;
2916 END IF;
2917 -- Debug info
2918 IF G_DEBUG='Y' THEN
2919 AHL_DEBUG_PUB.debug( 'End of private api Modify Recipient','+REP+');
2920
2921 END IF;
2922 -- Check if API is called in debug mode. If yes, disable debug.
2923 IF G_DEBUG='Y' THEN
2924 AHL_DEBUG_PUB.disable_debug;
2925
2926 END IF;
2927
2928 EXCEPTION
2929 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2930 ROLLBACK TO modify_recipient;
2931 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2932 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2933 p_count => x_msg_count,
2934 p_data => x_msg_data);
2935 IF G_DEBUG='Y' THEN
2936 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'ERROR' );
2937 AHL_DEBUG_PUB.debug( 'ahl_di_doc_index_pvt.Modify Recipient','+REP+');
2938
2939 -- Check if API is called in debug mode. If yes, disable debug.
2940 AHL_DEBUG_PUB.disable_debug;
2941
2942 END IF;
2943
2944 WHEN FND_API.G_EXC_ERROR THEN
2945 ROLLBACK TO modify_recipient;
2946 X_return_status := FND_API.G_RET_STS_ERROR;
2947 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2948 p_count => x_msg_count,
2949 p_data => X_msg_data);
2950 -- Debug info.
2951 IF G_DEBUG='Y' THEN
2952 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
2953 AHL_DEBUG_PUB.debug( 'ahl_di_doc_index_pvt.Modify Recipient','+REP+');
2954
2955
2956 -- Check if API is called in debug mode. If yes, disable debug.
2957 AHL_DEBUG_PUB.disable_debug;
2958
2959 END IF;
2960
2961 WHEN DUP_VAL_ON_INDEX THEN
2962 ROLLBACK TO modify_recipient;
2963 X_return_status := FND_API.G_RET_STS_ERROR;
2964 FND_MESSAGE.SET_NAME('AHL','AHL_DI_RECIPIENT_DUP_RECORD');
2965 FND_MSG_PUB.ADD;
2966 -- Check if API is called in debug mode. If yes, disable debug.
2967 IF G_DEBUG='Y' THEN
2968 AHL_DEBUG_PUB.disable_debug;
2969
2970 END IF;
2971 WHEN OTHERS THEN
2972 ROLLBACK TO modify_recipient;
2973 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2974 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2975 THEN
2976 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_DI_DOC_INDEX_PVT',
2977 p_procedure_name => 'MODIFY_RECIPIENT',
2978 p_error_text => SUBSTR(SQLERRM,1,240));
2979 END IF;
2980 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2981 p_count => x_msg_count,
2982 p_data => X_msg_data);
2983
2984 -- Debug info.
2985 IF G_DEBUG='Y' THEN
2986 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'SQL ERROR' );
2987 AHL_DEBUG_PUB.debug( 'ahl_di_doc_index_pvt.Modify Recipient','+REP+');
2988
2989
2990 -- Check if API is called in debug mode. If yes, disable debug.
2991 AHL_DEBUG_PUB.disable_debug;
2992
2993 END IF;
2994
2995 END MODIFY_RECIPIENT;
2996 /*----------------------------------------------------*/
2997 /* procedure name: delete_recipient */
2998 /* description : Removes the recipient record for an */
2999 /* associated document */
3000 /* */
3001 /*----------------------------------------------------*/
3002
3003 PROCEDURE DELETE_RECIPIENT
3004 (
3005 p_api_version IN NUMBER := 1.0 ,
3006 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
3007 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
3008 p_validate_only IN VARCHAR2 := FND_API.G_TRUE ,
3009 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3010 p_recipient_rec IN recipient_rec ,
3011 x_return_status OUT NOCOPY VARCHAR2 ,
3012 x_msg_count OUT NOCOPY NUMBER ,
3013 x_msg_data OUT NOCOPY VARCHAR2)
3014 IS
3015 --
3016 --Code commented: pjha 23-Jul-2002 :because recipient is uneditable after creation, hence no need.
3017 --also bug#2473425
3018 CURSOR get_recipient_rec_info(c_recipient_document_id NUMBER)
3019 IS
3020 SELECT ROWID ,
3021 object_version_number
3022 FROM AHL_RECIPIENT_DOCUMENTS
3023 WHERE recipient_document_id = c_recipient_document_id
3024 FOR UPDATE OF object_version_number NOWAIT;
3025
3026 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_RECIPIENT';
3027 l_api_version CONSTANT NUMBER := 1.0;
3028 l_rowid ROWID;
3029 l_object_version_number NUMBER;
3030
3031 BEGIN
3032 -- Standard Start of API savepoint
3033 SAVEPOINT delete_recipient;
3034 -- Check if API is called in debug mode. If yes, enable debug.
3035
3036 IF G_DEBUG='Y' THEN
3037 AHL_DEBUG_PUB.enable_debug;
3038
3039 END IF;
3040 -- Debug info.
3041 IF G_DEBUG='Y' THEN
3042 IF G_DEBUG='Y' THEN
3043 AHL_DEBUG_PUB.debug( 'enter ahl_di_doc_index_pvt.Delete Recipient','+REP+');
3044
3045 END IF;
3046 END IF;
3047 -- Standard call to check for call compatibility.
3048 IF FND_API.to_boolean(p_init_msg_list)
3049 THEN
3050 FND_MSG_PUB.initialize;
3051 END IF;
3052 -- Initialize API return status to success
3053 x_return_status := 'S';
3054 -- Initialize message list if p_init_msg_list is set to TRUE.
3055 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
3056 p_api_version,
3057 l_api_name,G_PKG_NAME)
3058 THEN
3059 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3060 END IF;
3061 -- IF p_recipient_tbl.COUNT > 0
3062 -- THEN
3063 -- FOR i IN p_recipient_tbl.FIRST..p_recipient_tbl.LAST
3064 -- LOOP
3065 OPEN get_recipient_rec_info(p_recipient_rec.recipient_document_id);
3066 FETCH get_recipient_rec_info INTO l_rowid,
3067 l_object_version_number;
3068 IF (get_recipient_rec_info%NOTFOUND) THEN
3069 --Modified pjha 24-Jul-2002 for bug#2473425: Begin
3070 --FND_MESSAGE.SET_NAME('AHL','AHL_DI_RECIP_PTY_ID_INVALID');
3071 FND_MESSAGE.SET_NAME('AHL','AHL_DI_RECIPIENT_DELETED');
3072 FND_MESSAGE.SET_TOKEN('RECPNAME',p_recipient_rec.recipient_party_number);
3073 FND_MSG_PUB.ADD;
3074 RAISE FND_API.G_EXC_ERROR;
3075 --Modified pjha 24-Jul-2002 for bug#2473425: End
3076 END IF;
3077 CLOSE get_recipient_rec_info;
3078 --Commented pjha 24-Jul-2002 no need of this check since record can only be
3079 -- deleted and hence would previous check would suffice
3080 /*
3081 -- Check for version number
3082 IF (l_object_version_number <> p_recipient_rec.object_version_number)
3083 THEN
3084 FND_MESSAGE.SET_NAME('AHL','AHL_DI_DOC_TL_REC_CHANGED');
3085 FND_MSG_PUB.ADD;
3086 END IF;
3087 */
3088
3089 -- Delete the record from suppliers table
3090 DELETE FROM AHL_RECIPIENT_DOCUMENTS
3091 WHERE ROWID = l_rowid;
3092 -- END LOOP;
3093 -- END IF;
3094 --Standarad check for commit
3095 IF FND_API.TO_BOOLEAN(p_commit) THEN
3096 COMMIT;
3097 END IF;
3098 -- Debug info
3099 IF G_DEBUG='Y' THEN
3100 AHL_DEBUG_PUB.debug( 'End of private api Delete Recipient','+REP+');
3101
3102 END IF;
3103 -- Check if API is called in debug mode. If yes, disable debug.
3104 IF G_DEBUG='Y' THEN
3105 AHL_DEBUG_PUB.disable_debug;
3106
3107 END IF;
3108
3109 EXCEPTION
3110 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3111 ROLLBACK TO delete_recipient;
3112 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3113 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
3114 p_count => x_msg_count,
3115 p_data => x_msg_data);
3116 IF G_DEBUG='Y' THEN
3117 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'ERROR' );
3118 AHL_DEBUG_PUB.debug( 'ahl_di_doc_index_pvt.Delete Recipient','+REP+');
3119
3120
3121 -- Check if API is called in debug mode. If yes, disable debug.
3122 AHL_DEBUG_PUB.disable_debug;
3123
3124 END IF;
3125
3126 WHEN FND_API.G_EXC_ERROR THEN
3127 ROLLBACK TO delete_recipient;
3128 X_return_status := FND_API.G_RET_STS_ERROR;
3129 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
3130 p_count => x_msg_count,
3131 p_data => X_msg_data);
3132 -- Debug info.
3133 IF G_DEBUG='Y' THEN
3134 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
3135 AHL_DEBUG_PUB.debug( 'ahl_di_doc_index_pvt.Delete Recipient','+REP+');
3136
3137
3138 -- Check if API is called in debug mode. If yes, disable debug.
3139 AHL_DEBUG_PUB.disable_debug;
3140
3141 END IF;
3142
3143 WHEN OTHERS THEN
3144 ROLLBACK TO delete_recipient;
3145 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3146 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3147 THEN
3148 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_DI_DOC_INDEX_PVT',
3149 p_procedure_name => 'DELETE_RECIPIENT',
3150 p_error_text => SUBSTR(SQLERRM,1,240));
3151 END IF;
3152 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
3153 p_count => x_msg_count,
3154 p_data => X_msg_data);
3155
3156 -- Debug info.
3157 IF G_DEBUG='Y' THEN
3158 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'SQL ERROR' );
3159 AHL_DEBUG_PUB.debug( 'ahl_di_doc_index_pvt.Delete Recipient','+REP+');
3160
3161
3162 -- Check if API is called in debug mode. If yes, disable debug.
3163 AHL_DEBUG_PUB.disable_debug;
3164
3165 END IF;
3166
3167 END DELETE_RECIPIENT;
3168
3169 END AHL_DI_DOC_INDEX_PVT;
[Home] [Help]