1 PACKAGE BODY AHL_DI_DOC_INDEX_PVT AS
2 /* $Header: AHLVDIXB.pls 120.2 2006/02/07 03:49:04 sagarwal noship $ */
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
434 END IF;
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+');
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;
550 l_document_info.attribute9 := p_x_document_tbl(i).attribute9;
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;
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 ,
661 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
658 p_init_msg_list => FND_API.G_TRUE ,
659 p_commit => FND_API.G_FALSE ,
660 p_validate_only => FND_API.G_TRUE ,
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,
792 attribute11,
789 attribute8,
790 attribute9,
791 attribute10,
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_info get_doc_rec_b_info%ROWTYPE;
809 --
810 l_num VARCHAR2(10);
811 BEGIN
812 -- Standard Start of API savepoint
813 SAVEPOINT modify_document;
814
815 -- Check if API is called in debug mode. If yes, enable debug.
816 IF G_DEBUG='Y' THEN
817 AHL_DEBUG_PUB.enable_debug;
818
819 END IF;
820 -- Debug info.
821 IF G_DEBUG='Y' THEN
822 IF G_DEBUG='Y' THEN
823 AHL_DEBUG_PUB.debug( 'enter ahl_di_doc_index_pvt.modify_document','+DI+');
824
825 END IF;
826 END IF;
827 -- Standard call to check for call compatibility.
828 IF FND_API.to_boolean(p_init_msg_list)
829 THEN
830 FND_MSG_PUB.initialize;
831 END IF;
832 -- Initialize API return status to success
833 x_return_status := 'S';
834 -- Initialize message list if p_init_msg_list is set to TRUE.
835 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
836 p_api_version,
837 l_api_name,G_PKG_NAME)
838 THEN
839 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
840 END IF;
841
842 --Start API Body
843 IF p_x_document_tbl.COUNT > 0
844 THEN
845 FOR i IN p_x_document_tbl.FIRST..p_x_document_tbl.LAST
846 LOOP
847 VALIDATE_DOCUMENT(
848 p_document_id => p_x_document_tbl(i).document_id,
849 p_source_party_id => p_x_document_tbl(i).source_party_id,
850 p_doc_type_code => p_x_document_tbl(i).doc_type_code,
851 p_doc_sub_type_code => p_x_document_tbl(i).doc_sub_type_code,
852 p_document_no => p_x_document_tbl(i).document_no,
853 p_operator_code => p_x_document_tbl(i).operator_code,
854 p_product_type_code => p_x_document_tbl(i).product_type_code,
855 p_subscribe_avail_flag => p_x_document_tbl(i).subscribe_avail_flag,
856 p_subscribe_to_flag => p_x_document_tbl(i).subscribe_to_flag,
857 p_object_version_number => p_x_document_tbl(i).object_version_number,
858 p_delete_flag => p_x_document_tbl(i).delete_flag);
859 END LOOP;
860 --Standard check to count messages
861 l_msg_count := FND_MSG_PUB.count_msg;
862
863 IF l_msg_count > 0 THEN
864 X_msg_count := l_msg_count;
865 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
866 RAISE FND_API.G_EXC_ERROR;
867 END IF;
868
869 FOR i IN p_x_document_tbl.FIRST..p_x_document_tbl.LAST
870 LOOP
871 OPEN get_doc_rec_b_info(p_x_document_tbl(i).document_id);
872 FETCH get_doc_rec_b_info INTO l_document_info;
873 CLOSE get_doc_rec_b_info;
874
875 --
876
877 -- This condition will take care of lost update data bug when concurrent users are
878 -- updating same record...02/05/02
879 if (l_document_info.object_version_number <>p_x_document_tbl(i).object_version_number)
880 then
881 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
882 FND_MSG_PUB.ADD;
883 RAISE FND_API.G_EXC_ERROR;
884 end if;
885 -- The following conditions compare the new record value with old record
886 -- value, if its different then assign the new value else continue
887 IF p_x_document_tbl(i).document_id IS NOT NULL
888 THEN
889 --Update the document table
890 /*-------------------------------------------------------- */
891 /* procedure name: AHL_DOCUMENTS_PKG.UPDATE_ROW */
892 /* description : Added by Senthil to call Table Handler */
893 /* Date : Dec 07 2001 */
894 /*---------------------------------------------------------*/
895
896
897 AHL_DOCUMENTS_PKG.UPDATE_ROW (
898 X_DOCUMENT_ID => p_x_document_tbl(i).document_id,
899 X_SUBSCRIBE_AVAIL_FLAG => p_x_document_tbl(i).subscribe_avail_flag,
900 X_SUBSCRIBE_TO_FLAG => p_x_document_tbl(i).subscribe_to_flag,
901 X_DOC_TYPE_CODE => p_x_document_tbl(i).doc_type_code,
902 X_DOC_SUB_TYPE_CODE => p_x_document_tbl(i).doc_sub_type_code,
903 X_OPERATOR_CODE => p_x_document_tbl(i).operator_code,
904 X_PRODUCT_TYPE_CODE => p_x_document_tbl(i).product_type_code,
905 X_ATTRIBUTE_CATEGORY => p_x_document_tbl(i).attribute_category,
906 X_ATTRIBUTE1 => p_x_document_tbl(i).attribute1,
907 X_ATTRIBUTE2 => p_x_document_tbl(i).attribute2,
908 X_ATTRIBUTE3 => p_x_document_tbl(i).attribute3,
909 X_ATTRIBUTE4 => p_x_document_tbl(i).attribute4,
910 X_ATTRIBUTE5 => p_x_document_tbl(i).attribute5,
911 X_ATTRIBUTE6 => p_x_document_tbl(i).attribute6 ,
912 X_ATTRIBUTE7 => p_x_document_tbl(i).attribute7 ,
913 X_ATTRIBUTE8 => p_x_document_tbl(i).attribute8 ,
917 X_ATTRIBUTE12 => p_x_document_tbl(i).attribute12 ,
914 X_ATTRIBUTE9 => p_x_document_tbl(i).attribute9 ,
915 X_ATTRIBUTE10 => p_x_document_tbl(i).attribute10 ,
916 X_ATTRIBUTE11 => p_x_document_tbl(i).attribute11 ,
918 X_ATTRIBUTE13 => p_x_document_tbl(i).attribute13 ,
919 X_ATTRIBUTE14 => p_x_document_tbl(i).attribute14 ,
920 X_ATTRIBUTE15 => p_x_document_tbl(i).attribute15 ,
921 X_OBJECT_VERSION_NUMBER => p_x_document_tbl(i).object_version_number+1,
922 X_SOURCE_PARTY_ID => p_x_document_tbl(i).source_party_id,
923 X_DOCUMENT_NO => p_x_document_tbl(i).document_no,
924 X_DOCUMENT_TITLE => p_x_document_tbl(i).document_title,
925 X_LAST_UPDATE_DATE => sysdate ,
926 X_LAST_UPDATED_BY => fnd_global.user_id ,
927 X_LAST_UPDATE_LOGIN => fnd_global.login_id
928 );
929 END IF;
930 END LOOP;
931 END IF;
932 -- Debug info.
933 IF G_DEBUG='Y' THEN
934 IF G_DEBUG='Y' THEN
935 AHL_DEBUG_PUB.debug( 'Before start processing Supplier Record ahl_di_doc_index_pvt.modify document','+DI+');
936
937 END IF;
938 END IF;
939
940 -- Checks for any suppliers modifications exists for the document index
941 IF p_x_supplier_tbl.COUNT > 0 THEN
942 modify_supplier
943 ( p_api_version => p_api_version ,
944 p_init_msg_list => FND_API.G_TRUE ,
945 p_commit => FND_API.G_FALSE ,
946 p_validate_only => FND_API.G_TRUE ,
947 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
948 p_supplier_tbl => p_x_supplier_tbl ,
949 x_return_status => x_return_status ,
950 x_msg_count => x_msg_count ,
951 x_msg_data => x_msg_data
952 );
953 END IF;
954 -- Debug info.
955 IF G_DEBUG='Y' THEN
956 IF G_DEBUG='Y' THEN
957 AHL_DEBUG_PUB.debug( 'Before start processing Recipient Record ahl_di_doc_index_pvt.modify document','+DI+');
958
959 END IF;
960 END IF;
961
962 -- Checks for any Recipients exists for the document index
963 IF p_x_recipient_tbl.COUNT > 0 THEN
964 modify_recipient
965 ( p_api_version => p_api_version ,
966 p_init_msg_list => FND_API.G_TRUE ,
967 p_commit => FND_API.G_FALSE ,
968 p_validate_only => FND_API.G_TRUE ,
969 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
970 p_recipient_tbl => p_x_recipient_tbl ,
971 x_return_status => x_return_status ,
972 x_msg_count => x_msg_count ,
973 x_msg_data => x_msg_data
974 );
975 END IF;
976 --Standard check for message count
977 l_msg_count := FND_MSG_PUB.count_msg;
978
979 IF l_msg_count > 0 THEN
980 X_msg_count := l_msg_count;
981 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
982 RAISE FND_API.G_EXC_ERROR;
983 END IF;
984 --Standard check for commit
985 IF FND_API.TO_BOOLEAN(p_commit) THEN
986 COMMIT;
987 END IF;
988 -- Debug info
989 IF G_DEBUG='Y' THEN
990 AHL_DEBUG_PUB.debug( 'End of private api modify document','+DI+');
991
992 END IF;
993 -- Check if API is called in debug mode. If yes, disable debug.
994 IF G_DEBUG='Y' THEN
995 AHL_DEBUG_PUB.disable_debug;
996
997 END IF;
998
999 EXCEPTION
1000 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1001 ROLLBACK TO modify_document;
1002 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1003 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1004 p_count => x_msg_count,
1005 p_data => x_msg_data);
1006
1007 IF G_DEBUG='Y' THEN
1008 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'ERROR' );
1009 AHL_DEBUG_PUB.debug( 'ahl_di_doc_index_pvt.modify document','+DI+');
1010
1011
1012
1013 -- Check if API is called in debug mode. If yes, disable debug.
1014
1015 AHL_DEBUG_PUB.disable_debug;
1016
1017 END IF;
1018
1019 WHEN FND_API.G_EXC_ERROR THEN
1020 ROLLBACK TO modify_document;
1021 X_return_status := FND_API.G_RET_STS_ERROR;
1022 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1023 p_count => x_msg_count,
1024 p_data => X_msg_data);
1025 -- Debug info.
1026 IF G_DEBUG='Y' THEN
1027 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
1028 AHL_DEBUG_PUB.debug( 'ahl_di_doc_index_pvt.modify document','+DI+');
1029
1030
1031 -- Check if API is called in debug mode. If yes, disable debug.
1032
1033 AHL_DEBUG_PUB.disable_debug;
1034
1035 END IF;
1036
1037 WHEN OTHERS THEN
1038 ROLLBACK TO modify_document;
1039 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1040 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1041 THEN
1042 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_DI_DOC_INDEX_PVT',
1043 p_procedure_name => 'MODIFY_DOCUMENT',
1044 p_error_text => SUBSTR(SQLERRM,1,240));
1045 END IF;
1046 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1050 -- Debug info.
1047 p_count => x_msg_count,
1048 p_data => X_msg_data);
1049
1051 IF G_DEBUG='Y' THEN
1052 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'SQL ERROR' );
1053 AHL_DEBUG_PUB.debug( 'ahl_di_doc_index_pvt.modify document','+DI+');
1054
1055
1056
1057 -- Check if API is called in debug mode. If yes, disable debug.
1058 AHL_DEBUG_PUB.disable_debug;
1059
1060 END IF;
1061
1062 END MODIFY_DOCUMENT;
1063 /*-----------------------------------------------------*/
1064 /* procedure name: validate_supplier(private procedure)*/
1065 /* description : Validation checks for before insert */
1066 /* new record as well before update */
1067 /*-----------------------------------------------------*/
1068 PROCEDURE VALIDATE_SUPPLIER
1069 ( P_SUPPLIER_DOCUMENT_ID IN NUMBER ,
1070 P_SUPPLIER_ID IN NUMBER ,
1071 P_DOCUMENT_ID IN NUMBER ,
1072 P_PREFERENCE_CODE IN VARCHAR2 ,
1073 --P_OBJECT_VERSION_NUMBER IN NUMBER,
1074 P_DELETE_FLAG IN VARCHAR2 := 'N')
1075 IS
1076 -- Cursor to retrieve the preference code from fnd lookups
1077 CURSOR get_preference_code(c_preference_code VARCHAR2)
1078 IS
1079 SELECT lookup_code
1080 FROM FND_LOOKUP_VALUES_VL
1081 WHERE lookup_code = c_preference_code
1082 AND lookup_type = 'AHL_SUPPLIER_PREF_TYPE'
1083 AND sysdate between nvl(start_date_active,sysdate)
1084 AND nvl(end_date_active,sysdate);
1085 -- Used to validate document id
1086 CURSOR check_doc_info(c_document_id NUMBER)
1087 IS
1088 SELECT 'X'
1089 FROM AHL_DOCUMENTS_B
1090 WHERE document_id = c_document_id;
1091 --Cursor to get supplier info
1092 CURSOR get_supplier_rec_info (c_supplier_document_id NUMBER)
1093 IS
1094 SELECT supplier_id,
1095 document_id,
1096 preference_code
1097 FROM AHL_SUPPLIER_DOCUMENTS
1098 WHERE supplier_document_id = c_supplier_document_id;
1099 -- Used to check Duplicate Record
1100 CURSOR dup_rec(c_supplier_id NUMBER,
1101 c_document_id NUMBER)
1102 IS
1103 SELECT 'X'
1104 FROM AHL_SUPPLIER_DOCUMENTS
1105 WHERE supplier_id = c_supplier_id
1106 AND document_id = c_document_id;
1107
1108 -- Perf Bug Fix 4919011.
1109 -- Replacing get_supplier_name by get_supplier_name_hz and get_supplier_name_po below
1110 /*
1111 CURSOR get_supplier_name(c_supplier_id NUMBER)
1112 IS
1113 SELECT party_number
1114 FROM AHL_HZ_PO_SUPPLIERS_V
1115 WHERE party_id =c_supplier_id;
1116 */
1117
1118 CURSOR get_supplier_name_hz(c_supplier_id NUMBER)
1119 IS
1120 SELECT party_number
1121 FROM HZ_PARTIES
1122 WHERE party_id =c_supplier_id;
1123
1124 CURSOR get_supplier_name_po(c_supplier_id NUMBER)
1125 IS
1126 SELECT SEGMENT1
1127 FROM PO_VENDORS
1128 WHERE VENDOR_ID =c_supplier_id;
1129
1130 --
1131 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_SUPPLIER';
1132 l_api_version CONSTANT NUMBER := 1.0;
1133 l_dummy VARCHAR2(2000);
1134 l_supplier_id NUMBER;
1135 l_document_id NUMBER;
1136 l_preference_code VARCHAR2(30);
1137 l_supplier_document_id NUMBER;
1138 l_supplier_name VARCHAR2(30);
1139 l_prod_install_status VARCHAR2(30);
1140
1141 BEGIN
1142
1143 -- Perf Bug Fix 4919011.
1144 BEGIN
1145 IF G_DEBUG='Y' THEN
1146 AHL_DEBUG_PUB.debug( 'Fetching Installation Status of PO','+SUP+');
1147 END IF;
1148 SELECT AHL_DI_DOC_INDEX_PVT.GET_PRODUCT_INSTALL_STATUS('PO')
1149 INTO l_prod_install_status
1150 FROM DUAL;
1151 END;
1152
1153 --When the process is insert or update(FLAG <> 'YES')
1154 IF p_delete_flag <> 'Y'
1155 THEN
1156 IF p_supplier_document_id IS NOT NULL
1157 THEN
1158 OPEN get_supplier_rec_info(p_supplier_document_id);
1159 FETCH get_supplier_rec_info INTO l_supplier_id,
1160 l_document_id,
1161 l_preference_code;
1162 CLOSE get_supplier_rec_info;
1163 END IF;
1164 --
1165 IF p_supplier_id IS NOT NULL
1166 THEN
1167 l_supplier_id := p_supplier_id;
1168 END IF;
1169 --
1170 IF p_document_id IS NOT NULL
1171 THEN
1172 l_document_id := p_document_id;
1173 END IF;
1174 --
1175 IF p_preference_code IS NOT NULL
1176 THEN
1177 l_preference_code := p_preference_code;
1178 END IF;
1179 --
1180 l_supplier_document_id := p_supplier_document_id;
1181 -- This condition checks for supplier id value is Null
1182 IF ((p_supplier_document_id IS NULL AND
1183 p_supplier_id IS NULL)
1184 OR
1185 (p_supplier_document_id IS NOT NULL
1186 AND l_supplier_id IS NULL))
1187 THEN
1188 FND_MESSAGE.SET_NAME('AHL','AHL_DI_SUPPLIER_ID_NULL');
1189 FND_MSG_PUB.ADD;
1190 END IF;
1191 -- This condition checks for Document id Is Null
1192 IF ((p_supplier_document_id IS NULL AND
1196 AND l_document_id IS NULL))
1193 p_document_id IS NULL)
1194 OR
1195 (p_supplier_document_id IS NOT NULL
1197 THEN
1198 FND_MESSAGE.SET_NAME('AHL','AHL_DI_DOCUMENT_ID_NULL');
1199 FND_MSG_PUB.ADD;
1200 END IF;
1201 -- This condition checks for existence of preference code in fnd lookups
1202 IF p_preference_code IS NOT NULL
1203 THEN
1204 OPEN get_preference_code(p_preference_code);
1205 FETCH get_preference_code INTO l_dummy;
1206 IF get_preference_code%NOTFOUND
1207 THEN
1208 FND_MESSAGE.SET_NAME('AHL','AHL_DI_PREF_CODE_NOT_EXIST');
1209 FND_MSG_PUB.ADD;
1210 END IF;
1211 CLOSE get_preference_code;
1212 END IF;
1213 -- This condition checks for document record in ahl documents table
1214 IF p_document_id IS NOT NULL
1215 THEN
1216 OPEN Check_doc_info(p_document_id);
1217 FETCH Check_doc_info INTO l_dummy;
1218 IF Check_doc_info%NOTFOUND
1219 THEN
1220 FND_MESSAGE.SET_NAME('AHL','AHL_DI_DOC_ID_NOT_EXISTS');
1221 FND_MSG_PUB.ADD;
1222 END IF;
1223 CLOSE Check_doc_info;
1224 END IF;
1225 --Check for Duplicate Record
1226 IF p_supplier_document_id IS NULL
1227 THEN
1228 OPEN dup_rec(l_supplier_id, l_document_id);
1229 FETCH dup_rec INTO l_dummy;
1230 IF dup_rec%FOUND THEN
1231 -- Perf Bug Fix 4919011.
1232 /*
1233 OPEN get_supplier_name(l_supplier_id);
1234 FETCH get_supplier_name INTO l_supplier_name;
1235 CLOSE get_supplier_name;
1236 */
1237 IF l_prod_install_status IN ('N','L') THEN
1238 OPEN get_supplier_name_hz(l_supplier_id);
1239 FETCH get_supplier_name_hz INTO l_supplier_name;
1240 CLOSE get_supplier_name_hz;
1241 ELSIF l_prod_install_status IN ('I','S') THEN
1242 OPEN get_supplier_name_po(l_supplier_id);
1243 FETCH get_supplier_name_po INTO l_supplier_name;
1244 CLOSE get_supplier_name_po;
1245 END IF;
1246 FND_MESSAGE.SET_NAME('AHL','AHL_DI_SUPPLIER_DUP_RECORD');
1247 FND_MESSAGE.SET_TOKEN('SUPNAME',l_supplier_name);
1248 FND_MSG_PUB.ADD;
1249 END IF;
1250 CLOSE dup_rec;
1251 END IF;
1252 END IF;
1253
1254 END VALIDATE_SUPPLIER;
1255
1256 /*--------------------------------------------------*/
1257 /* procedure name: create_supplier */
1258 /* description : Creates new supplier record */
1259 /* for an associated document */
1260 /* */
1261 /*--------------------------------------------------*/
1262 PROCEDURE CREATE_SUPPLIER
1263 (
1264 p_api_version IN NUMBER := 1.0 ,
1265 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
1266 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
1267 p_validate_only IN VARCHAR2 := FND_API.G_TRUE ,
1268 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1269 p_x_supplier_tbl IN OUT NOCOPY supplier_tbl ,
1270 x_return_status OUT NOCOPY VARCHAR2 ,
1271 x_msg_count OUT NOCOPY NUMBER ,
1272 x_msg_data OUT NOCOPY VARCHAR2)
1273 IS
1274 -- Used to check Duplicate Record
1275 CURSOR dup_rec(c_supplier_id NUMBER,
1276 c_document_id NUMBER)
1277 IS
1278 SELECT 'X'
1279 FROM AHL_SUPPLIER_DOCUMENTS
1280 WHERE supplier_id = c_supplier_id
1281 AND document_id = c_document_id;
1282
1283 -- Perf Bug Fix 4919011.
1284 -- Replacing get_supplier_name by get_supplier_name_hz and get_supplier_name_po below
1285 /*
1286 CURSOR get_supplier_name(c_supplier_id NUMBER)
1287 IS
1288 SELECT party_number
1289 FROM AHL_HZ_PO_SUPPLIERS_V
1290 WHERE party_id =c_supplier_id;
1291 */
1292
1293 CURSOR get_supplier_name_hz(c_supplier_id NUMBER)
1294 IS
1295 SELECT party_number
1296 FROM HZ_PARTIES
1297 WHERE party_id =c_supplier_id;
1298
1299 CURSOR get_supplier_name_po(c_supplier_id NUMBER)
1300 IS
1301 SELECT SEGMENT1
1302 FROM PO_VENDORS
1303 WHERE VENDOR_ID =c_supplier_id;
1304
1305 --
1306 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_SUPPLIER';
1307 l_api_version CONSTANT NUMBER := 1.0;
1308 l_msg_count NUMBER;
1309 l_supplier_document_id NUMBER;
1310 l_dummy VARCHAR2(2000);
1311 l_supplier_name VARCHAR2(360);
1312 l_supplier_info supplier_rec;
1313 l_prod_install_status VARCHAR2(30);
1314 BEGIN
1315 -- Standard Start of API savepoint
1316 SAVEPOINT create_supplier;
1317 -- Check if API is called in debug mode. If yes, enable debug.
1318 IF G_DEBUG='Y' THEN
1319 AHL_DEBUG_PUB.enable_debug;
1320 END IF;
1321 -- Debug info.
1322 IF G_DEBUG='Y' THEN
1323 AHL_DEBUG_PUB.debug( 'enter ahl_di_doc_index_pvt.Create Supplier','+SUP+');
1324 END IF;
1325
1326 -- Standard call to check for call compatibility.
1327 IF FND_API.to_boolean(p_init_msg_list)
1328 THEN
1332 x_return_status := 'S';
1329 FND_MSG_PUB.initialize;
1330 END IF;
1331 -- Initialize API return status to success
1333 -- Initialize message list if p_init_msg_list is set to TRUE.
1334 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
1335 p_api_version,
1336 l_api_name,G_PKG_NAME)
1337 THEN
1338 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1339 END IF;
1340
1341 -- Perf Bug Fix 4919011.
1342 BEGIN
1343 IF G_DEBUG='Y' THEN
1344 AHL_DEBUG_PUB.debug( 'Fetching Installation Status of P0','+SUP+');
1345 END IF;
1346 SELECT AHL_DI_DOC_INDEX_PVT.GET_PRODUCT_INSTALL_STATUS('PO')
1347 INTO l_prod_install_status
1348 FROM DUAL;
1349 END;
1350
1351 --Start API Body
1352 IF p_x_supplier_tbl.COUNT > 0
1353 THEN
1354 FOR i IN p_x_supplier_tbl.FIRST..p_x_supplier_tbl.LAST
1355 LOOP
1356 VALIDATE_SUPPLIER
1357 (
1358 p_supplier_document_id => p_x_supplier_tbl(i).supplier_document_id,
1359 p_supplier_id => p_x_supplier_tbl(i).supplier_id,
1360 p_document_id => p_x_supplier_tbl(i).document_id,
1361 p_preference_code => p_x_supplier_tbl(i).preference_code,
1362 p_delete_flag => p_x_supplier_tbl(i).delete_flag
1363 );
1364 END LOOP;
1365 -- Standard call to get message count and if count is get message info.
1366 l_msg_count := FND_MSG_PUB.count_msg;
1367
1368 IF l_msg_count > 0 THEN
1369 X_msg_count := l_msg_count;
1370 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1371 RAISE FND_API.G_EXC_ERROR;
1372 END IF;
1373
1374 FOR i IN p_x_supplier_tbl.FIRST..p_x_supplier_tbl.LAST
1375 LOOP
1376 IF (p_x_supplier_tbl(i).supplier_document_id IS NULL)
1377 THEN
1378 --The following conditions are required for null columns
1379 l_supplier_info.preference_code := p_x_supplier_tbl(i).preference_code;
1380 l_supplier_info.attribute_category := p_x_supplier_tbl(i).attribute_category;
1381 l_supplier_info.attribute1 := p_x_supplier_tbl(i).attribute1;
1382 l_supplier_info.attribute2 := p_x_supplier_tbl(i).attribute2;
1383 l_supplier_info.attribute3 := p_x_supplier_tbl(i).attribute3;
1384 l_supplier_info.attribute4 := p_x_supplier_tbl(i).attribute4;
1385 l_supplier_info.attribute5 := p_x_supplier_tbl(i).attribute5;
1386 l_supplier_info.attribute6 := p_x_supplier_tbl(i).attribute6;
1387 l_supplier_info.attribute7 := p_x_supplier_tbl(i).attribute7;
1388 l_supplier_info.attribute8 := p_x_supplier_tbl(i).attribute8;
1389 l_supplier_info.attribute9 := p_x_supplier_tbl(i).attribute9;
1390 l_supplier_info.attribute10 := p_x_supplier_tbl(i).attribute10;
1391 l_supplier_info.attribute11 := p_x_supplier_tbl(i).attribute11;
1392 l_supplier_info.attribute12 := p_x_supplier_tbl(i).attribute12;
1393 l_supplier_info.attribute13 := p_x_supplier_tbl(i).attribute13;
1394 l_supplier_info.attribute14 := p_x_supplier_tbl(i).attribute14;
1395 l_supplier_info.attribute15 := p_x_supplier_tbl(i).attribute15;
1396 -- check for duplicate records
1397 OPEN dup_rec(p_x_supplier_tbl(i).supplier_id,
1398 p_x_supplier_tbl(i).document_id);
1399 FETCH dup_rec INTO l_dummy;
1400 IF dup_rec%FOUND THEN
1401 -- Perf Bug Fix 4919011.
1402 /*
1403 OPEN get_supplier_name(p_x_supplier_tbl(i).supplier_id);
1404 FETCH get_supplier_name INTO l_supplier_name;
1405 CLOSE get_supplier_name;
1406 */
1407 IF l_prod_install_status IN ('N','L') THEN
1408 OPEN get_supplier_name_hz(p_x_supplier_tbl(i).supplier_id);
1409 FETCH get_supplier_name_hz INTO l_supplier_name;
1410 CLOSE get_supplier_name_hz;
1411 ELSIF l_prod_install_status IN ('I','S') THEN
1412 OPEN get_supplier_name_po(p_x_supplier_tbl(i).supplier_id);
1413 FETCH get_supplier_name_po INTO l_supplier_name;
1414 CLOSE get_supplier_name_po;
1415 END IF;
1416 FND_MESSAGE.SET_NAME('AHL','AHL_DI_SUPPLIER_DUP_RECORD');
1417 FND_MESSAGE.SET_TOKEN('SUPNAME',l_supplier_name);
1418 FND_MSG_PUB.ADD;
1419
1420 --AD RAISE FND_API.G_EXC_ERROR;
1421 --AD END IF;
1422 --ad CLOSE dup_rec;
1423
1424 ELSE
1425 --Retrieve the sequence number
1426 SELECT AHL_SUPPLIER_DOCUMENTS_S.Nextval INTO
1427 l_supplier_document_id from DUAL;
1428 --Insert the record into supplier documents table
1429 INSERT INTO AHL_SUPPLIER_DOCUMENTS
1430 (
1431 SUPPLIER_DOCUMENT_ID,
1432 SUPPLIER_ID,
1433 DOCUMENT_ID,
1434 PREFERENCE_CODE,
1435 OBJECT_VERSION_NUMBER,
1436 ATTRIBUTE_CATEGORY,
1437 ATTRIBUTE1,
1438 ATTRIBUTE2,
1439 ATTRIBUTE3,
1440 ATTRIBUTE4,
1441 ATTRIBUTE5,
1442 ATTRIBUTE6,
1446 ATTRIBUTE10,
1443 ATTRIBUTE7,
1444 ATTRIBUTE8,
1445 ATTRIBUTE9,
1447 ATTRIBUTE11,
1448 ATTRIBUTE12,
1449 ATTRIBUTE13,
1450 ATTRIBUTE14,
1451 ATTRIBUTE15,
1452 LAST_UPDATE_DATE,
1453 LAST_UPDATED_BY,
1454 CREATION_DATE,
1455 CREATED_BY,
1456 LAST_UPDATE_LOGIN
1457 )
1458 VALUES
1459 (
1460 l_supplier_document_id,
1461 p_x_supplier_tbl(i).supplier_id,
1462 p_x_supplier_tbl(i).document_id,
1463 l_supplier_info.preference_code,
1464 1,
1465 l_supplier_info.attribute_category,
1466 l_supplier_info.attribute1,
1467 l_supplier_info.attribute2,
1468 l_supplier_info.attribute3,
1469 l_supplier_info.attribute4,
1470 l_supplier_info.attribute5,
1471 l_supplier_info.attribute6,
1472 l_supplier_info.attribute7,
1473 l_supplier_info.attribute8,
1474 l_supplier_info.attribute9,
1475 l_supplier_info.attribute10,
1476 l_supplier_info.attribute11,
1477 l_supplier_info.attribute12,
1478 l_supplier_info.attribute13,
1479 l_supplier_info.attribute14,
1480 l_supplier_info.attribute15,
1481 sysdate,
1482 fnd_global.user_id,
1483 sysdate,
1484 fnd_global.user_id,
1485 fnd_global.login_id
1486 );
1487 p_x_supplier_tbl(i).supplier_document_id := l_supplier_document_id;
1488 END IF;--ad
1489 CLOSE dup_rec;--ad
1490
1491 /*
1492 --{{adharia comment
1493 l_msg_count := FND_MSG_PUB.count_msg;
1494 IF l_msg_count > 0 THEN
1495 X_msg_count := l_msg_count;
1496 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1497 RAISE FND_API.G_EXC_ERROR;
1498 END IF;
1499 --{{adharia comment
1500 */
1501 END IF;
1502 END LOOP;
1503 END IF;
1504
1505 --{{adharia added
1506 l_msg_count := FND_MSG_PUB.count_msg;
1507 IF l_msg_count > 0 THEN
1508 X_msg_count := l_msg_count;
1509 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1510 RAISE FND_API.G_EXC_ERROR;
1511 END IF;
1512 --{{adharia
1513
1514 -- Standard check of p_commit.
1515 IF FND_API.TO_BOOLEAN(p_commit) THEN
1516 COMMIT;
1517 END IF;
1518 -- Debug info
1519 IF G_DEBUG='Y' THEN
1520 AHL_DEBUG_PUB.debug( 'End of private api Create Supplier','+SUP+');
1521
1522 END IF;
1523 -- Check if API is called in debug mode. If yes, disable debug.
1524 IF G_DEBUG='Y' THEN
1525 AHL_DEBUG_PUB.disable_debug;
1526
1527 END IF;
1528
1529 EXCEPTION
1530 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1531 ROLLBACK TO create_supplier;
1532 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1533 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1534 p_count => x_msg_count,
1535 p_data => x_msg_data);
1536
1537 IF G_DEBUG='Y' THEN
1538 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'ERROR' );
1539 AHL_DEBUG_PUB.debug( 'ahl_di_doc_index_pvt.Create Supplier','+SUP+');
1540
1541
1542 -- Check if API is called in debug mode. If yes, disable debug.
1543
1544 AHL_DEBUG_PUB.disable_debug;
1545
1546 END IF;
1547
1548 WHEN FND_API.G_EXC_ERROR THEN
1549 ROLLBACK TO create_supplier;
1550 X_return_status := FND_API.G_RET_STS_ERROR;
1551 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1552 p_count => x_msg_count,
1553 p_data => X_msg_data);
1554 -- Debug info.
1555 IF G_DEBUG='Y' THEN
1556 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
1557 AHL_DEBUG_PUB.debug( 'ahl_di_doc_index_pub.Create Supplier','+SUP+');
1558
1559
1560 -- Check if API is called in debug mode. If yes, disable debug.
1561 AHL_DEBUG_PUB.disable_debug;
1562
1563 END IF;
1564
1565 WHEN OTHERS THEN
1566 ROLLBACK TO create_supplier;
1567 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1568 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1569 THEN
1570 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_DI_DOC_INDEX_PVT',
1571 p_procedure_name => 'CREATE_SUPPLIER',
1572 p_error_text => SUBSTR(SQLERRM,1,240));
1573 END IF;
1574 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1575 p_count => x_msg_count,
1576 p_data => X_msg_data);
1577
1578 -- Debug info.
1579 IF G_DEBUG='Y' THEN
1583
1580 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'SQL ERROR' );
1581 AHL_DEBUG_PUB.debug( 'ahl_di_doc_index_pvt.Create Supplier','+SUP+');
1582
1584 -- Check if API is called in debug mode. If yes, disable debug.
1585 AHL_DEBUG_PUB.disable_debug;
1586
1587 END IF;
1588
1589 END CREATE_SUPPLIER;
1590 /*------------------------------------------------------*/
1591 /* procedure name: modify_supplier */
1592 /* description : Update the existing supplier record */
1593 /* for an associated document */
1594 /* */
1595 /*------------------------------------------------------*/
1596
1597 PROCEDURE MODIFY_SUPPLIER
1598 (
1599 p_api_version IN NUMBER := 1.0 ,
1600 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
1601 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
1602 p_validate_only IN VARCHAR2 := FND_API.G_TRUE ,
1603 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1604 p_supplier_tbl IN supplier_tbl ,
1605 x_return_status OUT NOCOPY VARCHAR2 ,
1606 x_msg_count OUT NOCOPY NUMBER ,
1607 x_msg_data OUT NOCOPY VARCHAR2
1608 )
1609 IS
1610 -- To get the supplier info
1611 CURSOR get_supplier_rec_info(c_supplier_document_id NUMBER)
1612 IS
1613 SELECT ROWID,
1614 supplier_id,
1615 document_id,
1616 preference_code,
1617 attribute_category,
1618 attribute1,
1619 attribute2,
1620 attribute3,
1621 attribute4,
1622 attribute5,
1623 attribute6,
1624 attribute7,
1625 attribute8,
1626 attribute9,
1627 attribute10,
1628 attribute11,
1629 attribute12,
1630 attribute13,
1631 attribute14,
1632 attribute15,
1633 object_version_number
1634 FROM AHL_SUPPLIER_DOCUMENTS
1635 WHERE supplier_document_id = c_supplier_document_id
1636 FOR UPDATE OF object_version_number NOWAIT;
1637
1638 -- Perf Bug Fix 4919011.
1639 -- Replacing get_supplier_name by get_supplier_name_hz and get_supplier_name_po below
1640 /*
1641 CURSOR get_supplier_name(c_supplier_id NUMBER)
1642 IS
1643 SELECT party_number
1644 FROM AHL_HZ_PO_SUPPLIERS_V
1645 WHERE party_id =c_supplier_id;
1646 */
1647
1648 CURSOR get_supplier_name_hz(c_supplier_id NUMBER)
1649 IS
1650 SELECT party_number
1651 FROM HZ_PARTIES
1652 WHERE party_id =c_supplier_id;
1653
1654 CURSOR get_supplier_name_po(c_supplier_id NUMBER)
1655 IS
1656 SELECT SEGMENT1
1657 FROM PO_VENDORS
1658 WHERE VENDOR_ID =c_supplier_id;
1659
1660
1661 --
1662 l_api_name CONSTANT VARCHAR2(30) := 'MODIFY_SUPPLIER';
1663 l_api_version CONSTANT NUMBER := 1.0;
1664 l_msg_count NUMBER;
1665 l_num_rec NUMBER;
1666 l_rowid ROWID;
1667 l_supplier_name VARCHAR2(390);
1668 l_supplier_info get_supplier_rec_info%ROWTYPE;
1669 l_prod_install_status VARCHAR2(30);
1670 BEGIN
1671 -- Standard Start of API savepoint
1672 SAVEPOINT modify_supplier;
1673 -- Check if API is called in debug mode. If yes, enable debug.
1674 IF G_DEBUG='Y' THEN
1675 AHL_DEBUG_PUB.enable_debug;
1676
1677 END IF;
1678 -- Debug info.
1679 IF G_DEBUG='Y' THEN
1680 AHL_DEBUG_PUB.debug( 'anand enter ahl_di_doc_index_pvt.Modify Supplier','+SUP+');
1681 END IF;
1682
1683 -- Standard call to check for call compatibility.
1684 IF FND_API.to_boolean(p_init_msg_list)
1685 THEN
1686 FND_MSG_PUB.initialize;
1687 END IF;
1688 -- Initialize API return status to success
1689 x_return_status := 'S';
1690 -- Initialize message list if p_init_msg_list is set to TRUE.
1691 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
1692 p_api_version,
1693 l_api_name,G_PKG_NAME)
1694 THEN
1695 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1696 END IF;
1697 -- Start API Body
1698
1699 -- Perf Bug Fix 4919011.
1700 BEGIN
1701 IF G_DEBUG='Y' THEN
1702 AHL_DEBUG_PUB.debug( 'Fetching Installation Status of PO','+SUP+');
1703 END IF;
1704 SELECT AHL_DI_DOC_INDEX_PVT.GET_PRODUCT_INSTALL_STATUS('PO')
1705 INTO l_prod_install_status
1706 FROM DUAL;
1707 END;
1708
1709 IF p_supplier_tbl.COUNT > 0
1710 THEN
1711 FOR i IN p_supplier_tbl.FIRST..p_supplier_tbl.LAST
1712 LOOP
1713 -- Calling validate suppliers
1714 --ad
1715 IF G_DEBUG='Y' THEN
1716 IF G_DEBUG='Y' THEN
1717 AHL_DEBUG_PUB.debug( ' anand enter ahl_di_doc_index_pvt.Modify Supplier before validate supplier ','+SUP+');
1718
1719 END IF;
1720 END IF;
1721 --ad
1722 VALIDATE_SUPPLIER
1723 (
1724 p_supplier_document_id => p_supplier_tbl(i).supplier_document_id,
1728 p_delete_flag => p_supplier_tbl(i).delete_flag
1725 p_supplier_id => p_supplier_tbl(i).supplier_id,
1726 p_document_id => p_supplier_tbl(i).document_id,
1727 p_preference_code => p_supplier_tbl(i).preference_code,
1729 );
1730 END LOOP;
1731 --End of Validations
1732 -- Standard call to get message count
1733 l_msg_count := FND_MSG_PUB.count_msg;
1734 --ad
1735 IF G_DEBUG='Y' THEN
1736 IF G_DEBUG='Y' THEN
1737 AHL_DEBUG_PUB.debug( ' anand enter ahl_di_doc_index_pvt.Modify Supplier after validate sup '||l_msg_count,'+SUP+');
1738
1739 END IF;
1740 END IF;
1741 --ad
1742
1743
1744 IF l_msg_count > 0 THEN
1745 X_msg_count := l_msg_count;
1746 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1747 RAISE FND_API.G_EXC_ERROR;
1748 END IF;
1749 FOR i IN p_supplier_tbl.FIRST..p_supplier_tbl.LAST
1750 LOOP
1751 --Retrieve the existing supplier record
1752 OPEN get_supplier_rec_info(p_supplier_tbl(i).supplier_document_id);
1753 FETCH get_supplier_rec_info INTO l_supplier_info;
1754 CLOSE get_supplier_rec_info;
1755
1756 -- This condition will take care of lost update data bug when concurrent users are
1757 -- updating same record...02/05/02
1758 IF l_supplier_info.object_version_number <>p_supplier_tbl(i).object_version_number
1759 THEN
1760 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
1761 FND_MSG_PUB.ADD;
1762 --AD RAISE FND_API.G_EXC_ERROR;
1763 ELSE --AD
1764 -- The following conditions compare the new record value with old record
1765 -- value, if its different then assign the new value else continue
1766 IF p_supplier_tbl(i).supplier_document_id IS NOT NULL
1767 AND p_supplier_tbl(i).delete_flag <> 'Y'
1768 THEN
1769 l_supplier_info.supplier_id := p_supplier_tbl(i).supplier_id;
1770 l_supplier_info.document_id := p_supplier_tbl(i).document_id;
1771 l_supplier_info.preference_code := p_supplier_tbl(i).preference_code;
1772 l_supplier_info.attribute_category := p_supplier_tbl(i).attribute_category;
1773 l_supplier_info.attribute1 := p_supplier_tbl(i).attribute1;
1774 l_supplier_info.attribute2 := p_supplier_tbl(i).attribute2;
1775 l_supplier_info.attribute3 := p_supplier_tbl(i).attribute3;
1776 l_supplier_info.attribute4 := p_supplier_tbl(i).attribute4;
1777 l_supplier_info.attribute5 := p_supplier_tbl(i).attribute5;
1778 l_supplier_info.attribute6 := p_supplier_tbl(i).attribute6;
1779 l_supplier_info.attribute7 := p_supplier_tbl(i).attribute7;
1780 l_supplier_info.attribute8 := p_supplier_tbl(i).attribute8;
1781 l_supplier_info.attribute9 := p_supplier_tbl(i).attribute9;
1782 l_supplier_info.attribute10 := p_supplier_tbl(i).attribute10;
1783 l_supplier_info.attribute11 := p_supplier_tbl(i).attribute11;
1784 l_supplier_info.attribute12 := p_supplier_tbl(i).attribute12;
1785 l_supplier_info.attribute13 := p_supplier_tbl(i).attribute13;
1786 l_supplier_info.attribute14 := p_supplier_tbl(i).attribute14;
1787 l_supplier_info.attribute15 := p_supplier_tbl(i).attribute15;
1788 -- Perf Bug Fix 4919011.
1789 /*
1790 OPEN get_supplier_name(l_supplier_info.supplier_id);
1791 FETCH get_supplier_name INTO l_supplier_name;
1792 CLOSE get_supplier_name;
1793 */
1794 IF l_prod_install_status IN ('N','L') THEN
1795 OPEN get_supplier_name_hz(l_supplier_info.supplier_id);
1796 FETCH get_supplier_name_hz INTO l_supplier_name;
1797 CLOSE get_supplier_name_hz;
1798 ELSIF l_prod_install_status IN ('I','S') THEN
1799 OPEN get_supplier_name_po(l_supplier_info.supplier_id);
1800 FETCH get_supplier_name_po INTO l_supplier_name;
1801 CLOSE get_supplier_name_po;
1802 END IF;
1803
1804
1805
1806 --Updates the supplier table
1807 UPDATE AHL_SUPPLIER_DOCUMENTS
1808 SET supplier_id = l_supplier_info.supplier_id,
1809 document_id = l_supplier_info.document_id,
1810 preference_code = l_supplier_info.preference_code,
1811 object_version_number = l_supplier_info.object_version_number+1,
1812 attribute_category = l_supplier_info.attribute_category,
1813 attribute1 = l_supplier_info.attribute1,
1814 attribute2 = l_supplier_info.attribute2,
1815 attribute3 = l_supplier_info.attribute3,
1816 attribute4 = l_supplier_info.attribute4,
1817 attribute5 = l_supplier_info.attribute5,
1818 attribute6 = l_supplier_info.attribute6,
1819 attribute7 = l_supplier_info.attribute7,
1820 attribute8 = l_supplier_info.attribute8,
1821 attribute9 = l_supplier_info.attribute9,
1822 attribute10 = l_supplier_info.attribute10,
1823 attribute11 = l_supplier_info.attribute11,
1824 attribute12 = l_supplier_info.attribute12,
1825 attribute13 = l_supplier_info.attribute13,
1829 last_updated_by = fnd_global.user_id,
1826 attribute14 = l_supplier_info.attribute14,
1827 attribute15 = l_supplier_info.attribute15,
1828 last_update_date = sysdate,
1830 last_update_login = fnd_global.login_id
1831 WHERE ROWID = l_supplier_info.rowid;
1832 END IF;
1833 --Incase of delete supplier record
1834 IF (p_supplier_tbl(i).supplier_document_id IS NOT NULL AND
1835 p_supplier_tbl(i).delete_flag = 'Y' )
1836 THEN
1837 DELETE_SUPPLIER
1838 (
1839 p_api_version => 1.0 ,
1840 p_init_msg_list => FND_API.G_FALSE ,
1841 p_commit => FND_API.G_FALSE ,
1842 p_validate_only => FND_API.G_TRUE ,
1843 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1844 p_supplier_rec => p_supplier_tbl(i) ,
1845 x_return_status => x_return_status ,
1846 x_msg_count => x_msg_count ,
1847 x_msg_data => x_msg_data
1848 );
1849 END IF;
1850
1851
1852 END IF;--AD IF THERE IS ERROR DONT INSERT
1853
1854 END LOOP;
1855 END IF;
1856 --{{ADHARIA
1857
1858 l_msg_count := FND_MSG_PUB.count_msg;
1859 --ad
1860 IF G_DEBUG='Y' THEN
1861 IF G_DEBUG='Y' THEN
1862 AHL_DEBUG_PUB.debug( ' anand enter ahl_di_doc_index_pvt.Modify Supplier after modify sup '||l_msg_count,'+SUP+');
1863
1864 END IF;
1865 END IF;
1866 --ad
1867 IF l_msg_count > 0 THEN
1868 X_msg_count := l_msg_count;
1869 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1870 RAISE FND_API.G_EXC_ERROR;
1871 END IF;
1872 --{{ADHARIA
1873
1874 -- Standard check of p_commit.
1875 IF FND_API.TO_BOOLEAN(p_commit) THEN
1876 COMMIT;
1877 END IF;
1878
1879 -- Debug info
1880 IF G_DEBUG='Y' THEN
1881 AHL_DEBUG_PUB.debug( 'End of private api Modify Supplier','+SUP+');
1882
1883 END IF;
1884 -- Check if API is called in debug mode. If yes, disable debug.
1885 IF G_DEBUG='Y' THEN
1886 AHL_DEBUG_PUB.disable_debug;
1887
1888 END IF;
1889
1890 EXCEPTION
1891 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1892 ROLLBACK TO modify_supplier;
1893 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1894 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1895 p_count => x_msg_count,
1896 p_data => x_msg_data);
1897
1898 IF G_DEBUG='Y' THEN
1899 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'ERROR' );
1900 AHL_DEBUG_PUB.debug( 'ahl_di_doc_index_pvt.Modify supplier','+SUP+');
1901
1902
1903
1904 -- Check if API is called in debug mode. If yes, disable debug.
1905 AHL_DEBUG_PUB.disable_debug;
1906
1907 END IF;
1908
1909 WHEN FND_API.G_EXC_ERROR THEN
1910 ROLLBACK TO modify_supplier;
1911 X_return_status := FND_API.G_RET_STS_ERROR;
1912 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1913 p_count => x_msg_count,
1914 p_data => X_msg_data);
1915 -- Debug info.
1916 IF G_DEBUG='Y' THEN
1917 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
1918 AHL_DEBUG_PUB.debug( 'ahl_di_doc_index_pvt.Modify Supplier','+SUP+');
1919
1920
1921
1922 -- Check if API is called in debug mode. If yes, disable debug.
1923 AHL_DEBUG_PUB.disable_debug;
1924
1925 END IF;
1926
1927 WHEN DUP_VAL_ON_INDEX THEN
1928 ROLLBACK TO modify_supplier;
1929 X_return_status := FND_API.G_RET_STS_ERROR;
1930 FND_MESSAGE.SET_NAME('AHL','AHL_DI_SUPPLIER_DUP_RECORD');
1931 FND_MESSAGE.SET_TOKEN('SUPNAME',l_supplier_name);
1932 FND_MSG_PUB.ADD;
1933 -- Check if API is called in debug mode. If yes, disable debug.
1934 IF G_DEBUG='Y' THEN
1935 AHL_DEBUG_PUB.disable_debug;
1936
1937 END IF;
1938
1939 WHEN OTHERS THEN
1940 ROLLBACK TO modify_supplier;
1941 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1942 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1943 THEN
1944 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_DI_DOC_INDEX_PVT',
1945 p_procedure_name => 'MODIFY_SUPPLIER',
1946 p_error_text => SUBSTR(SQLERRM,1,240));
1947 END IF;
1948 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1949 p_count => x_msg_count,
1950 p_data => X_msg_data);
1951
1952 -- Debug info.
1953 IF G_DEBUG='Y' THEN
1954 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'SQL ERROR' );
1955 AHL_DEBUG_PUB.debug( 'ahl_di_doc_index_pvt.Modify Supplier','+SUP+');
1956
1957
1958
1959 -- Check if API is called in debug mode. If yes, disable debug.
1960 AHL_DEBUG_PUB.disable_debug;
1961
1962 END IF;
1963
1964 END MODIFY_SUPPLIER;
1968 /* for an associated document */
1965 /*------------------------------------------------------*/
1966 /* procedure name: delete_supplier */
1967 /* description : Removes the supplier record */
1969 /* */
1970 /*------------------------------------------------------*/
1971
1972 PROCEDURE DELETE_SUPPLIER
1973 (
1974 p_api_version IN NUMBER := 1.0 ,
1975 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
1976 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
1977 p_validate_only IN VARCHAR2 := FND_API.G_TRUE ,
1978 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1979 p_supplier_rec IN supplier_rec ,
1980 x_return_status OUT NOCOPY VARCHAR2 ,
1981 x_msg_count OUT NOCOPY NUMBER ,
1982 x_msg_data OUT NOCOPY VARCHAR2)
1983 IS
1984 --To get the supplier info
1985 CURSOR get_supplier_rec_info(c_supplier_document_id NUMBER)
1986 IS
1987 SELECT ROWID ,
1988 supplier_id,
1989 document_id,
1990 object_version_number
1991 FROM AHL_SUPPLIER_DOCUMENTS
1992 WHERE supplier_document_id = c_supplier_document_id
1993 FOR UPDATE OF object_version_number NOWAIT;
1994 --Cursor to check the record exists in Subscriptions table
1995 --Cursor modified to check only active subscriptions: pjha: 16-Jul-2002
1996 CURSOR get_subc_rec(c_supplier_id NUMBER,
1997 c_document_id NUMBER)
1998 IS
1999 SELECT 'X'
2000 FROM AHL_SUBSCRIPTIONS_B
2001 WHERE document_id = c_document_id
2002 --AND subscribed_frm_party_id = c_supplier_id;
2003 AND subscribed_frm_party_id = c_supplier_id
2004 AND NVL(end_date,sysdate) >= TRUNC(sysdate);
2005
2006 -- Perf Bug Fix 4919011.
2007 -- Replacing get_supplier_name by get_supplier_name_hz and get_supplier_name_po below
2008 /*
2009 CURSOR get_supplier_name(c_supplier_id NUMBER)
2010 IS
2011 SELECT party_number
2012 FROM AHL_HZ_PO_SUPPLIERS_V
2013 WHERE party_id =c_supplier_id;
2014 */
2015
2016 CURSOR get_supplier_name_hz(c_supplier_id NUMBER)
2017 IS
2018 SELECT party_number
2019 FROM HZ_PARTIES
2020 WHERE party_id =c_supplier_id;
2021
2022 CURSOR get_supplier_name_po(c_supplier_id NUMBER)
2023 IS
2024 SELECT SEGMENT1
2025 FROM PO_VENDORS
2026 WHERE VENDOR_ID =c_supplier_id;
2027
2028 --
2029 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_SUPPLIER';
2030 l_api_version CONSTANT NUMBER := 1.0;
2031 l_rowid ROWID;
2032 l_msg_count NUMBER;
2033 l_object_version_number NUMBER;
2034 l_supplier_id NUMBER;
2035 l_document_id NUMBER;
2036 l_supplier_name VARCHAR2(30);
2037 l_dummy VARCHAR2(2000);
2038 l_prod_install_status VARCHAR2(30);
2039 BEGIN
2040 -- Standard Start of API savepoint
2041 SAVEPOINT delete_supplier;
2042 -- Check if API is called in debug mode. If yes, enable debug.
2043 IF G_DEBUG='Y' THEN
2044 AHL_DEBUG_PUB.enable_debug;
2045
2046 END IF;
2047 -- Debug info.
2048 IF G_DEBUG='Y' THEN
2049 AHL_DEBUG_PUB.debug( 'enter ahl_di_doc_index_pvt.Delete Supplier','+SUP+');
2050 END IF;
2051 -- Standard call to check for call compatibility.
2052 IF FND_API.to_boolean(p_init_msg_list)
2053 THEN
2054 FND_MSG_PUB.initialize;
2055 END IF;
2056 -- Initialize API return status to success
2057 x_return_status := 'S';
2058 -- Initialize message list if p_init_msg_list is set to TRUE.
2059 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
2060 p_api_version,
2061 l_api_name,G_PKG_NAME)
2062 THEN
2063 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2064 END IF;
2065
2066 -- Perf Bug Fix 4919011.
2067 BEGIN
2068 IF G_DEBUG='Y' THEN
2069 AHL_DEBUG_PUB.debug( 'Fetching Product Install Status for PO');
2070 END IF;
2071 SELECT AHL_DI_DOC_INDEX_PVT.GET_PRODUCT_INSTALL_STATUS('PO')
2072 INTO l_prod_install_status
2073 FROM DUAL;
2074 END;
2075
2076 --IF p_supplier_tbl.COUNT > 0
2077 --THEN
2078 OPEN get_supplier_rec_info(p_supplier_rec.supplier_document_id);
2079 l_rowid := null;
2080 l_supplier_id := 0;
2081 l_document_id := 0;
2082 l_object_version_number := 0;
2083 FETCH get_supplier_rec_info INTO l_rowid,
2084 l_supplier_id,
2085 l_document_id,
2086 l_object_version_number;
2087 IF (get_supplier_rec_info%NOTFOUND)
2088 THEN
2089 FND_MESSAGE.SET_NAME('AHL','AHL_DI_SUPPLIER_REC_INVALID');
2090 FND_MSG_PUB.ADD;
2091 END IF;
2092 CLOSE get_supplier_rec_info;
2093 -- Check for version number
2094 IF (l_object_version_number <> p_supplier_rec.object_version_number)
2095 THEN
2096 FND_MESSAGE.SET_NAME('AHL','AHL_DI_DOC_TL_REC_CHANGED');
2097 FND_MSG_PUB.ADD;
2098 END IF;
2102 OPEN get_subc_rec(l_supplier_id,l_document_id);
2099 --Check for Subscriptions Record
2100 IF p_supplier_rec.supplier_document_id IS NOT NULL
2101 THEN
2103 FETCH get_subc_rec INTO l_dummy;
2104 IF get_subc_rec%FOUND
2105 THEN
2106 -- Perf Bug Fix 4919011.
2107 /*
2108 OPEN get_supplier_name(l_supplier_id);
2109 FETCH get_supplier_name INTO l_supplier_name;
2110 CLOSE get_supplier_name;
2111 */
2112 IF l_prod_install_status IN ('N','L') THEN
2113 OPEN get_supplier_name_hz(l_supplier_id);
2114 FETCH get_supplier_name_hz INTO l_supplier_name;
2115 CLOSE get_supplier_name_hz;
2116 ELSIF l_prod_install_status IN ('I','S') THEN
2117 OPEN get_supplier_name_po(l_supplier_id);
2118 FETCH get_supplier_name_po INTO l_supplier_name;
2119 CLOSE get_supplier_name_po;
2120 END IF;
2121 FND_MESSAGE.SET_NAME('AHL','AHL_DI_SUBC_REC_EXISTS');
2122 FND_MESSAGE.SET_TOKEN('SUPNAME',l_supplier_name);
2123 FND_MSG_PUB.ADD;
2124 END IF;
2125 CLOSE get_subc_rec;
2126 END IF;
2127 -- Delete the record from suppliers table
2128 DELETE FROM AHL_SUPPLIER_DOCUMENTS
2129 WHERE ROWID = l_rowid;
2130 --END IF;
2131 -- Standard call to get message count
2132 l_msg_count := FND_MSG_PUB.count_msg;
2133
2134 IF l_msg_count > 0 THEN
2135 X_msg_count := l_msg_count;
2136 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2137 --AD RAISE FND_API.G_EXC_ERROR;
2138 END IF;
2139
2140 -- Standard check of p_commit.
2141 IF FND_API.TO_BOOLEAN(p_commit) THEN
2142 COMMIT;
2143 END IF;
2144 -- Debug info
2145 IF G_DEBUG='Y' THEN
2146 AHL_DEBUG_PUB.debug( 'End of private api Delete Supplier','+SUP+');
2147
2148 END IF;
2149 -- Check if API is called in debug mode. If yes, disable debug.
2150 IF G_DEBUG='Y' THEN
2151 AHL_DEBUG_PUB.disable_debug;
2152
2153 END IF;
2154
2155 EXCEPTION
2156 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2157 ROLLBACK TO delete_supplier;
2158 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2159 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2160 p_count => x_msg_count,
2161 p_data => x_msg_data);
2162
2163 IF G_DEBUG='Y' THEN
2164 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'ERROR' );
2165 AHL_DEBUG_PUB.debug( 'ahl_di_doc_index_pvt.delete Supplier','+SUP+');
2166
2167
2168 -- Check if API is called in debug mode. If yes, disable debug.
2169 AHL_DEBUG_PUB.disable_debug;
2170
2171 END IF;
2172
2173 WHEN FND_API.G_EXC_ERROR THEN
2174 ROLLBACK TO delete_supplier;
2175 X_return_status := FND_API.G_RET_STS_ERROR;
2176 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2177 p_count => x_msg_count,
2178 p_data => X_msg_data);
2179 -- Debug info.
2180 IF G_DEBUG='Y' THEN
2181 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
2182 AHL_DEBUG_PUB.debug( 'ahl_di_doc_index_pvt.Delete Supplier','+SUP+');
2183
2184
2185 -- Check if API is called in debug mode. If yes, disable debug.
2186 AHL_DEBUG_PUB.disable_debug;
2187
2188 END IF;
2189
2190 WHEN OTHERS THEN
2191 ROLLBACK TO delete_supplier;
2192 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2193 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2194 THEN
2195 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_DI_DOC_INDEX_PVT',
2196 p_procedure_name => 'DELETE_SUPPLIER',
2197 p_error_text => SUBSTR(SQLERRM,1,240));
2198 END IF;
2199 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2200 p_count => x_msg_count,
2201 p_data => X_msg_data);
2202
2203 -- Debug info.
2204 IF G_DEBUG='Y' THEN
2205 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'SQL ERROR' );
2206 AHL_DEBUG_PUB.debug( 'ahl_di_doc_index_pvt.Delete Supplier','+SUP+');
2207
2208
2209 -- Check if API is called in debug mode. If yes, disable debug.
2210 AHL_DEBUG_PUB.disable_debug;
2211
2212 END IF;
2213
2214 END DELETE_SUPPLIER;
2215 /*-------------------------------------------------------*/
2216 /* procedure name: validate_recipient(private procedure) */
2217 /* description : Validation checks for before inserting */
2218 /* new record as well before update */
2219 /* */
2220 /*-------------------------------------------------------*/
2221
2222 PROCEDURE VALIDATE_RECIPIENT
2223 ( p_recipient_document_id IN NUMBER ,
2224 p_recipient_party_id IN NUMBER ,
2225 p_document_id IN NUMBER ,
2226 p_object_version_number IN NUMBER ,
2230 CURSOR get_recipient_rec_info (c_recipient_document_id NUMBER)
2227 p_delete_flag IN VARCHAR2 := 'N')
2228 IS
2229 -- Cursor to get the recipient info
2231 IS
2232 SELECT recipient_party_id,
2233 document_id
2234 FROM AHL_RECIPIENT_DOCUMENTS
2235 WHERE recipient_document_id = c_recipient_document_id;
2236
2237 -- Used to validate the document id
2238 CURSOR check_doc_info(c_document_id NUMBER)
2239 IS
2240 SELECT 'X'
2241 FROM AHL_DOCUMENTS_B
2242 WHERE document_id = c_document_id;
2243
2244 --Cursor to check duplicate record
2245 CURSOR dup_rec(c_recipient_party_id NUMBER,
2246 c_document_id NUMBER)
2247 IS
2248 SELECT 'X'
2249 FROM AHL_RECIPIENT_DOCUMENTS
2250 WHERE recipient_party_id = c_recipient_party_id
2251 AND document_id = c_document_id;
2252
2253 CURSOR DUP_REC_NAME(c_recipient_party_id NUMBER)
2254 IS
2255 SELECT party_number
2256 FROM hz_parties
2257 WHERE party_id = c_recipient_party_id;
2258
2259
2260
2261 --
2262 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_RECIPIENT';
2263 l_api_version CONSTANT NUMBER := 1.0;
2264 l_dummy VARCHAR2(2000);
2265 l_recipient_party_id NUMBER;
2266 l_document_id NUMBER;
2267 l_recipient_document_id NUMBER;
2268 l_dup_rec_name varchar2(360);
2269 BEGIN
2270 --When the action is insert or update
2271 IF p_delete_flag <> 'Y'
2272 THEN
2273 IF p_recipient_document_id IS NOT NULL
2274 THEN
2275 OPEN get_recipient_rec_info(p_recipient_document_id);
2276 FETCH get_recipient_rec_info INTO l_recipient_party_id,
2277 l_document_id;
2278 CLOSE get_recipient_rec_info;
2279 END IF;
2280 --
2281 IF p_recipient_party_id IS NOT NULL
2282 THEN
2283 l_recipient_party_id := p_recipient_party_id;
2284 END IF;
2285 --
2286 IF p_document_id IS NOT NULL
2287 THEN
2288 l_document_id := p_document_id;
2289 END IF;
2290 --
2291 l_recipient_document_id := p_recipient_document_id;
2292 --This condition checks for recipient party id null
2293 IF ((p_recipient_document_id IS NULL AND
2294 p_recipient_party_id IS NULL)
2295 OR
2296 (p_recipient_document_id IS NOT NULL
2297 AND l_recipient_party_id IS NULL))
2298 THEN
2299 FND_MESSAGE.SET_NAME('AHL','AHL_DI_RECIPIENT_PTY_ID_NULL');
2300 FND_MSG_PUB.ADD;
2301 END IF;
2302 --This condition checks for Document Id
2303 IF ((p_recipient_document_id IS NULL AND
2304 p_document_id IS NULL)
2305 OR
2306 (p_recipient_document_id IS NOT NULL
2307 AND l_document_id IS NULL))
2308 THEN
2309 FND_MESSAGE.SET_NAME('AHL','AHL_DI_DOCUMENT_ID_NULL');
2310 FND_MSG_PUB.ADD;
2311 END IF;
2312 -- This condition checks for existence of document record in ahl documents table
2313 IF p_document_id IS NOT NULL
2314 THEN
2315 OPEN Check_doc_info(p_document_id);
2316 FETCH Check_doc_info INTO l_dummy;
2317 IF Check_doc_info%NOTFOUND
2318 THEN
2319 FND_MESSAGE.SET_NAME('AHL','AHL_DI_DOC_ID_NOT_EXISTS');
2320 FND_MSG_PUB.ADD;
2321 END IF;
2322 CLOSE Check_doc_info;
2323 END IF;
2324 -- Check for Duplicate Record
2325 IF p_recipient_document_id IS NULL
2326 THEN
2327 OPEN dup_rec(l_recipient_party_id, l_document_id);
2328 FETCH dup_rec INTO l_dummy;
2329 IF dup_rec%FOUND THEN
2330 OPEN DUP_REC_NAME(l_recipient_party_id);
2331 FETCH DUP_REC_NAME INTO L_DUP_REC_NAME;
2332 FND_MESSAGE.SET_NAME('AHL','AHL_DI_RECIPIENT_DUP_RECORD');
2333 FND_MESSAGE.SET_TOKEN('RECPTID',l_DUP_REC_NAME);
2334 FND_MSG_PUB.ADD;
2335 CLOSE DUP_REC_NAME;
2336 END IF;
2337 CLOSE dup_rec;
2338 END IF;
2339 END IF;
2340
2341 END VALIDATE_RECIPIENT;
2342 /*------------------------------------------------------*/
2343 /* procedure name: create_recipient */
2344 /* description : Creates new recipient record */
2345 /* for an associated document */
2346 /* */
2347 /*------------------------------------------------------*/
2348
2349 PROCEDURE CREATE_RECIPIENT
2350 (
2351 p_api_version IN NUMBER := 1.0 ,
2352 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
2353 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
2354 p_validate_only IN VARCHAR2 := FND_API.G_TRUE ,
2355 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2356 p_x_recipient_tbl IN OUT NOCOPY recipient_tbl ,
2357 x_return_status OUT NOCOPY VARCHAR2 ,
2358 x_msg_count OUT NOCOPY NUMBER ,
2359 x_msg_data OUT NOCOPY VARCHAR2)
2360 IS
2361 --Check for same record multiple times
2362 CURSOR dup_rec(c_recipient_party_id NUMBER,
2363 c_document_id NUMBER)
2364 IS
2365 SELECT 'X'
2369
2366 FROM AHL_RECIPIENT_DOCUMENTS
2367 WHERE recipient_party_id = c_recipient_party_id
2368 AND document_id = c_document_id;
2370 CURSOR DUP_REC_NAME(c_recipient_party_id NUMBER)
2371 IS
2372 SELECT party_number
2373 FROM hz_parties
2374 WHERE party_id = c_recipient_party_id;
2375
2376 l_dup_rec_name varchar2(360);
2377 --
2378 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_RECIPIENT';
2379 l_api_version CONSTANT NUMBER := 1.0;
2380 l_msg_count NUMBER;
2381 l_dummy VARCHAR2(2000);
2382 l_recipient_document_id NUMBER;
2383 l_recipient_info Recipient_rec;
2384 BEGIN
2385 -- Standard Start of API savepoint
2386 SAVEPOINT create_recipient;
2387 -- Check if API is called in debug mode. If yes, enable debug.
2388 IF G_DEBUG='Y' THEN
2389 AHL_DEBUG_PUB.enable_debug;
2390
2391 END IF;
2392 -- Debug info.
2393 IF G_DEBUG='Y' THEN
2394 IF G_DEBUG='Y' THEN
2395 AHL_DEBUG_PUB.debug( 'enter ahl_di_doc_index_pvt.Create Recipient','+REP+');
2396
2397 END IF;
2398 END IF;
2399 -- Standard call to check for call compatibility.
2400 IF FND_API.to_boolean(p_init_msg_list)
2401 THEN
2402 FND_MSG_PUB.initialize;
2403 END IF;
2404 -- Initialize API return status to success
2405 x_return_status := 'S';
2406 -- Initialize message list if p_init_msg_list is set to TRUE.
2407 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
2408 p_api_version,
2409 l_api_name,G_PKG_NAME)
2410 THEN
2411 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2412 END IF;
2413 --Start API Body
2414 IF p_x_recipient_tbl.COUNT > 0
2415 THEN
2416 FOR i IN p_x_recipient_tbl.FIRST..p_x_recipient_tbl.LAST
2417 LOOP
2418 VALIDATE_RECIPIENT
2419 (
2420 p_recipient_document_id => p_x_recipient_tbl(i).recipient_document_id,
2421 p_recipient_party_id => p_x_recipient_tbl(i).recipient_party_id,
2422 p_document_id => p_x_recipient_tbl(i).document_id,
2423 p_object_version_number => p_x_recipient_tbl(i).object_version_number,
2424 p_delete_flag => p_x_recipient_tbl(i).delete_flag);
2425 END LOOP;
2426 -- Standard call to get message count and if count is get message info.
2427 l_msg_count := FND_MSG_PUB.count_msg;
2428
2429 IF l_msg_count > 0 THEN
2430 X_msg_count := l_msg_count;
2431 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2432 RAISE FND_API.G_EXC_ERROR;
2433 END IF;
2434
2435 FOR i IN p_x_recipient_tbl.FIRST..p_x_recipient_tbl.LAST
2436 LOOP
2437 IF p_x_recipient_tbl(i).recipient_document_id IS NULL
2438 THEN
2439 --
2440 l_recipient_info.attribute_category := p_x_recipient_tbl(i).attribute_category;
2441 l_recipient_info.attribute1 := p_x_recipient_tbl(i).attribute1;
2442 l_recipient_info.attribute2 := p_x_recipient_tbl(i).attribute2;
2443 l_recipient_info.attribute3 := p_x_recipient_tbl(i).attribute3;
2444 l_recipient_info.attribute4 := p_x_recipient_tbl(i).attribute4;
2445 l_recipient_info.attribute5 := p_x_recipient_tbl(i).attribute5;
2446 l_recipient_info.attribute6 := p_x_recipient_tbl(i).attribute6;
2447 l_recipient_info.attribute7 := p_x_recipient_tbl(i).attribute7;
2448 l_recipient_info.attribute8 := p_x_recipient_tbl(i).attribute8;
2449 l_recipient_info.attribute9 := p_x_recipient_tbl(i).attribute9;
2450 l_recipient_info.attribute10 := p_x_recipient_tbl(i).attribute10;
2451 l_recipient_info.attribute11 := p_x_recipient_tbl(i).attribute11;
2452 l_recipient_info.attribute12 := p_x_recipient_tbl(i).attribute12;
2453 l_recipient_info.attribute13 := p_x_recipient_tbl(i).attribute13;
2454 l_recipient_info.attribute14 := p_x_recipient_tbl(i).attribute14;
2455 l_recipient_info.attribute15 := p_x_recipient_tbl(i).attribute15;
2456
2457 --Check for duplication
2458 OPEN dup_rec(p_x_recipient_tbl(i).recipient_party_id,
2459 p_x_recipient_tbl(i).document_id);
2460 FETCH dup_rec INTO l_dummy;
2461 IF dup_rec%FOUND THEN
2462 OPEN DUP_REC_NAME(p_x_recipient_tbl(i).recipient_party_id);
2463 FETCH DUP_REC_NAME INTO L_DUP_REC_NAME;
2464 FND_MESSAGE.SET_NAME('AHL','AHL_DI_RECIPIENT_DUP_RECORD');
2465 FND_MESSAGE.SET_TOKEN('RECPTID',l_DUP_REC_NAME);
2466 FND_MSG_PUB.ADD;
2467 CLOSE DUP_REC_NAME;
2468 --ad RAISE FND_API.G_EXC_ERROR;
2469 --ad END IF;
2470 --ad CLOSE dup_rec;
2471 else --ad
2472 --Retrieves the sequence number
2473 SELECT AHL_RECIPIENT_DOCUMENTS_S.Nextval INTO
2474 l_recipient_document_id from DUAL;
2475 --Insert the record into recipient documents
2476 INSERT INTO AHL_RECIPIENT_DOCUMENTS
2477 (
2478 RECIPIENT_DOCUMENT_ID,
2479 RECIPIENT_PARTY_ID,
2480 DOCUMENT_ID,
2481 OBJECT_VERSION_NUMBER,
2482 ATTRIBUTE_CATEGORY,
2483 ATTRIBUTE1,
2484 ATTRIBUTE2,
2485 ATTRIBUTE3,
2486 ATTRIBUTE4,
2490 ATTRIBUTE8,
2487 ATTRIBUTE5,
2488 ATTRIBUTE6,
2489 ATTRIBUTE7,
2491 ATTRIBUTE9,
2492 ATTRIBUTE10,
2493 ATTRIBUTE11,
2494 ATTRIBUTE12,
2495 ATTRIBUTE13,
2496 ATTRIBUTE14,
2497 ATTRIBUTE15,
2498 LAST_UPDATE_DATE,
2499 LAST_UPDATED_BY,
2500 CREATION_DATE,
2501 CREATED_BY,
2502 LAST_UPDATE_LOGIN
2503 )
2504 VALUES
2505 (
2506 l_recipient_document_id,
2507 p_x_recipient_tbl(i).recipient_party_id,
2508 p_x_recipient_tbl(i).document_id,
2509 1,
2510 l_recipient_info.attribute_category,
2511 l_recipient_info.attribute1,
2512 l_recipient_info.attribute2,
2513 l_recipient_info.attribute3,
2514 l_recipient_info.attribute4,
2515 l_recipient_info.attribute5,
2516 l_recipient_info.attribute6,
2517 l_recipient_info.attribute7,
2518 l_recipient_info.attribute8,
2519 l_recipient_info.attribute9,
2520 l_recipient_info.attribute10,
2521 l_recipient_info.attribute11,
2522 l_recipient_info.attribute12,
2523 l_recipient_info.attribute13,
2524 l_recipient_info.attribute14,
2525 l_recipient_info.attribute15,
2526 sysdate,
2527 fnd_global.user_id,
2528 sysdate,
2529 fnd_global.user_id,
2530 fnd_global.login_id
2531 );
2532 p_x_recipient_tbl(i).recipient_document_id := l_recipient_document_id;
2533 -- Standard check to count messages
2534 /*adharia
2535 l_msg_count := FND_MSG_PUB.count_msg;
2536
2537 IF l_msg_count > 0 THEN
2538 X_msg_count := l_msg_count;
2539 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2540 RAISE FND_API.G_EXC_ERROR;
2541 END IF;--ad
2542 adharia*/
2543 END IF;
2544 CLOSE dup_rec;--ad
2545 END IF;
2546 END LOOP;
2547 END IF;
2548 --adharia
2549 l_msg_count := FND_MSG_PUB.count_msg;
2550
2551 IF l_msg_count > 0 THEN
2552 X_msg_count := l_msg_count;
2553 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2554 RAISE FND_API.G_EXC_ERROR;
2555 end if;
2556 --adharia
2557
2558 -- Standard check of p_commit.
2559 IF FND_API.TO_BOOLEAN(p_commit) THEN
2560 COMMIT;
2561 END IF;
2562 -- Debug info
2563 IF G_DEBUG='Y' THEN
2564 AHL_DEBUG_PUB.debug( 'End of private api Create Recipient','+REP+');
2565
2566 END IF;
2567 -- Check if API is called in debug mode. If yes, disable debug.
2568 IF G_DEBUG='Y' THEN
2569 AHL_DEBUG_PUB.disable_debug;
2570
2571 END IF;
2572
2573 EXCEPTION
2574 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2575 ROLLBACK TO create_recipient;
2576 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2577 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2578 p_count => x_msg_count,
2579 p_data => x_msg_data);
2580
2581 IF G_DEBUG='Y' THEN
2582 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'ERROR' );
2583 AHL_DEBUG_PUB.debug( 'ahl_di_doc_index_pvt.Create Recipient','+REP+');
2584
2585
2586 -- Check if API is called in debug mode. If yes, disable debug.
2587 AHL_DEBUG_PUB.disable_debug;
2588
2589 END IF;
2590
2591 WHEN FND_API.G_EXC_ERROR THEN
2592 ROLLBACK TO create_recipient;
2593 X_return_status := FND_API.G_RET_STS_ERROR;
2594 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2595 p_count => x_msg_count,
2596 p_data => X_msg_data);
2597 -- Debug info.
2598 IF G_DEBUG='Y' THEN
2599 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
2600 AHL_DEBUG_PUB.debug( 'ahl_di_doc_index_pvt.Create Recipient','+REP+');
2601
2602
2603 -- Check if API is called in debug mode. If yes, disable debug.
2604 AHL_DEBUG_PUB.disable_debug;
2605
2606 END IF;
2607
2608 WHEN OTHERS THEN
2609 ROLLBACK TO create_recipient;
2610 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2611 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2612 THEN
2613 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_DI_DOC_INDEX_PVT',
2614 p_procedure_name => 'CREATE_RECIPIENT',
2615 p_error_text => SUBSTR(SQLERRM,1,240));
2616 END IF;
2617 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2618 p_count => x_msg_count,
2619 p_data => X_msg_data);
2620
2621 -- Debug info.
2625
2622 IF G_DEBUG='Y' THEN
2623 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'SQL ERROR' );
2624 AHL_DEBUG_PUB.debug( 'ahl_di_doc_index_pvt.Create Recipient','+REP+');
2626
2627 -- Check if API is called in debug mode. If yes, disable debug.
2628 AHL_DEBUG_PUB.disable_debug;
2629
2630 END IF;
2631
2632 END CREATE_RECIPIENT;
2633 /*----------------------------------------------------*/
2634 /* procedure name: modify_recipient */
2635 /* description : Update the existing recipient record*/
2636 /* for an associated document */
2637 /* */
2638 /*----------------------------------------------------*/
2639
2640 PROCEDURE MODIFY_RECIPIENT
2641 (
2642 p_api_version IN NUMBER := 1.0 ,
2643 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
2644 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
2645 p_validate_only IN VARCHAR2 := FND_API.G_TRUE ,
2646 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2647 p_recipient_tbl IN OUT NOCOPY recipient_tbl ,
2648 x_return_status OUT NOCOPY VARCHAR2 ,
2649 x_msg_count OUT NOCOPY NUMBER ,
2650 x_msg_data OUT NOCOPY VARCHAR2)
2651 IS
2652 -- To get the exisitng record
2653 CURSOR get_recipient_rec_info(c_recipient_document_id NUMBER)
2654 IS
2655 SELECT ROWID,
2656 recipient_party_id,
2657 document_id,
2658 object_version_number,
2659 attribute_category,
2660 attribute1,
2661 attribute2,
2662 attribute3,
2663 attribute4,
2664 attribute5,
2665 attribute6,
2666 attribute7,
2667 attribute8,
2668 attribute9,
2669 attribute10,
2670 attribute11,
2671 attribute12,
2672 attribute13,
2673 attribute14,
2674 attribute15
2675 FROM AHL_RECIPIENT_DOCUMENTS
2676 WHERE recipient_document_id = c_recipient_document_id
2677 FOR UPDATE OF object_version_number NOWAIT;
2678 --
2679 l_api_name CONSTANT VARCHAR2(30) := 'MODIFY_RECIPIENT';
2680 l_api_version CONSTANT NUMBER := 1.0;
2681 l_msg_count NUMBER;
2682 l_num_rec NUMBER;
2683 l_rowid ROWID;
2684 l_document_id NUMBER;
2685 l_recipient_document_id NUMBER;
2686 l_recipient_info get_recipient_rec_info%ROWTYPE;
2687 BEGIN
2688 -- Standard Start of API savepoint
2689 SAVEPOINT modify_recipient;
2690 -- Check if API is called in debug mode. If yes, enable debug.
2691
2692 IF G_DEBUG='Y' THEN
2693 AHL_DEBUG_PUB.enable_debug;
2694
2695 END IF;
2696 -- Debug info.
2697 IF G_DEBUG='Y' THEN
2698 IF G_DEBUG='Y' THEN
2699 AHL_DEBUG_PUB.debug( 'enter ahl_di_doc_index_pvt.Modify Recipient','+REP+');
2700
2701 END IF;
2702 END IF;
2703 -- Standard call to check for call compatibility.
2704 IF FND_API.to_boolean(p_init_msg_list)
2705 THEN
2706 FND_MSG_PUB.initialize;
2707 END IF;
2708 -- Initialize API return status to success
2709 x_return_status := 'S';
2710 -- Initialize message list if p_init_msg_list is set to TRUE.
2711 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
2712 p_api_version,
2713 l_api_name,G_PKG_NAME)
2714 THEN
2715 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2716 END IF;
2717 -- Start API Body
2718 IF p_recipient_tbl.COUNT > 0
2719 THEN
2720 FOR i IN p_recipient_tbl.FIRST..p_recipient_tbl.LAST
2721 LOOP
2722 -- Calling validate recipients
2723 VALIDATE_RECIPIENT
2724 ( p_recipient_document_id => p_recipient_tbl(i).recipient_document_id,
2725 p_recipient_party_id => p_recipient_tbl(i).recipient_party_id,
2726 p_document_id => p_recipient_tbl(i).document_id,
2727 p_object_version_number => p_recipient_tbl(i).object_version_number,
2728 p_delete_flag => p_recipient_tbl(i).delete_flag
2729 );
2730 END LOOP;
2731 --Standard call to count messages
2732 l_msg_count := FND_MSG_PUB.count_msg;
2733
2734 IF l_msg_count > 0 THEN
2735 X_msg_count := l_msg_count;
2736 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2737 RAISE FND_API.G_EXC_ERROR;
2738 END IF;
2739
2740 FOR i IN p_recipient_tbl.FIRST..p_recipient_tbl.LAST
2741 LOOP
2742
2743 --Retrieve the existing recipient record
2744 OPEN get_recipient_rec_info(p_recipient_tbl(i).recipient_document_id);
2745 FETCH get_recipient_rec_info INTO l_recipient_info;
2746 CLOSE get_recipient_rec_info;
2747
2748 -- This condition will take care of lost update data bug when concurrent users are
2749 -- updating same record...02/05/02
2750
2751 if (l_recipient_info.object_version_number <>p_recipient_tbl(i).object_version_number)
2752 then
2753 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
2754 FND_MSG_PUB.ADD;
2758
2755 --ad RAISE FND_API.G_EXC_ERROR;
2756 --ad end if;
2757 else --ad
2759 -- The following conditions compare the new record value with old record
2760 -- value, if its different then assign the new value else continue
2761 IF (p_recipient_tbl(i).recipient_document_id IS NOT NULL
2762 AND p_recipient_tbl(i).delete_flag <> 'Y')
2763 THEN
2764 l_recipient_info.recipient_party_id := p_recipient_tbl(i).recipient_party_id;
2765 l_recipient_info.document_id := p_recipient_tbl(i).document_id;
2766 l_recipient_info.attribute_category := p_recipient_tbl(i).attribute_category;
2767 l_recipient_info.attribute1 := p_recipient_tbl(i).attribute1;
2768 l_recipient_info.attribute2 := p_recipient_tbl(i).attribute2;
2769 l_recipient_info.attribute3 := p_recipient_tbl(i).attribute3;
2770 l_recipient_info.attribute3 := p_recipient_tbl(i).attribute3;
2771 l_recipient_info.attribute4 := p_recipient_tbl(i).attribute4;
2772 l_recipient_info.attribute5 := p_recipient_tbl(i).attribute5;
2773 l_recipient_info.attribute6 := p_recipient_tbl(i).attribute6;
2774 l_recipient_info.attribute7 := p_recipient_tbl(i).attribute7;
2775 l_recipient_info.attribute8 := p_recipient_tbl(i).attribute8;
2776 l_recipient_info.attribute9 := p_recipient_tbl(i).attribute9;
2777 l_recipient_info.attribute10 := p_recipient_tbl(i).attribute10;
2778 l_recipient_info.attribute11 := p_recipient_tbl(i).attribute11;
2779 l_recipient_info.attribute12 := p_recipient_tbl(i).attribute12;
2780 l_recipient_info.attribute13 := p_recipient_tbl(i).attribute13;
2781 l_recipient_info.attribute14 := p_recipient_tbl(i).attribute14;
2782 l_recipient_info.attribute15 := p_recipient_tbl(i).attribute15;
2783 -- update the table
2784 UPDATE AHL_RECIPIENT_DOCUMENTS
2785 SET recipient_party_id = l_recipient_info.recipient_party_id,
2786 document_id = l_recipient_info.document_id,
2787 object_version_number = l_recipient_info.object_version_number+1,
2788 attribute_category = l_recipient_info.attribute_category,
2789 attribute1 = l_recipient_info.attribute1,
2790 attribute2 = l_recipient_info.attribute2,
2791 attribute3 = l_recipient_info.attribute3,
2792 attribute4 = l_recipient_info.attribute4,
2793 attribute5 = l_recipient_info.attribute5,
2794 attribute6 = l_recipient_info.attribute6,
2795 attribute7 = l_recipient_info.attribute7,
2796 attribute8 = l_recipient_info.attribute8,
2797 attribute9 = l_recipient_info.attribute9,
2798 attribute10 = l_recipient_info.attribute10,
2799 attribute11 = l_recipient_info.attribute11,
2800 attribute12 = l_recipient_info.attribute12,
2801 attribute13 = l_recipient_info.attribute13,
2802 attribute14 = l_recipient_info.attribute14,
2803 attribute15 = l_recipient_info.attribute15,
2804 last_update_date = sysdate,
2805 last_updated_by = fnd_global.user_id,
2806 last_update_login = fnd_global.login_id
2807 WHERE ROWID = l_recipient_info.rowid;
2808
2809 --Incase of delete a recipient record
2810 ELSIF (p_recipient_tbl(i).recipient_document_id IS NOT NULL AND
2811 p_recipient_tbl(i).delete_flag = 'Y')
2812 THEN
2813 DELETE_RECIPIENT
2814 ( p_api_version => 1.0 ,
2815 p_init_msg_list => FND_API.G_FALSE ,
2816 p_commit => FND_API.G_FALSE ,
2817 p_validate_only => FND_API.G_TRUE ,
2818 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2819 p_recipient_rec => p_recipient_tbl(i) ,
2820 x_return_status => x_return_status ,
2821 x_msg_count => x_msg_count ,
2822 x_msg_data => x_msg_data);
2823 END IF;
2824 end if;--ad
2825 END LOOP;
2826 END IF;
2827 -- Standard check of p_commit.
2828 IF FND_API.TO_BOOLEAN(p_commit) THEN
2829 COMMIT;
2830 END IF;
2831 -- Debug info
2832 IF G_DEBUG='Y' THEN
2833 AHL_DEBUG_PUB.debug( 'End of private api Modify Recipient','+REP+');
2834
2835 END IF;
2836 -- Check if API is called in debug mode. If yes, disable debug.
2837 IF G_DEBUG='Y' THEN
2838 AHL_DEBUG_PUB.disable_debug;
2839
2840 END IF;
2841
2842 EXCEPTION
2843 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2844 ROLLBACK TO modify_recipient;
2845 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2846 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2847 p_count => x_msg_count,
2848 p_data => x_msg_data);
2849 IF G_DEBUG='Y' THEN
2850 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'ERROR' );
2851 AHL_DEBUG_PUB.debug( 'ahl_di_doc_index_pvt.Modify Recipient','+REP+');
2852
2853 -- Check if API is called in debug mode. If yes, disable debug.
2854 AHL_DEBUG_PUB.disable_debug;
2855
2856 END IF;
2857
2858 WHEN FND_API.G_EXC_ERROR THEN
2859 ROLLBACK TO modify_recipient;
2860 X_return_status := FND_API.G_RET_STS_ERROR;
2861 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2862 p_count => x_msg_count,
2863 p_data => X_msg_data);
2864 -- Debug info.
2865 IF G_DEBUG='Y' THEN
2866 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
2867 AHL_DEBUG_PUB.debug( 'ahl_di_doc_index_pvt.Modify Recipient','+REP+');
2868
2869
2870 -- Check if API is called in debug mode. If yes, disable debug.
2871 AHL_DEBUG_PUB.disable_debug;
2872
2873 END IF;
2874
2875 WHEN DUP_VAL_ON_INDEX THEN
2876 ROLLBACK TO modify_recipient;
2877 X_return_status := FND_API.G_RET_STS_ERROR;
2878 FND_MESSAGE.SET_NAME('AHL','AHL_DI_RECIPIENT_DUP_RECORD');
2879 FND_MSG_PUB.ADD;
2880 -- Check if API is called in debug mode. If yes, disable debug.
2881 IF G_DEBUG='Y' THEN
2882 AHL_DEBUG_PUB.disable_debug;
2883
2884 END IF;
2885 WHEN OTHERS THEN
2886 ROLLBACK TO modify_recipient;
2887 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2888 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2889 THEN
2893 END IF;
2890 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_DI_DOC_INDEX_PVT',
2891 p_procedure_name => 'MODIFY_RECIPIENT',
2892 p_error_text => SUBSTR(SQLERRM,1,240));
2894 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2895 p_count => x_msg_count,
2896 p_data => X_msg_data);
2897
2898 -- Debug info.
2899 IF G_DEBUG='Y' THEN
2900 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'SQL ERROR' );
2901 AHL_DEBUG_PUB.debug( 'ahl_di_doc_index_pvt.Modify Recipient','+REP+');
2902
2903
2904 -- Check if API is called in debug mode. If yes, disable debug.
2905 AHL_DEBUG_PUB.disable_debug;
2906
2907 END IF;
2908
2909 END MODIFY_RECIPIENT;
2910 /*----------------------------------------------------*/
2911 /* procedure name: delete_recipient */
2912 /* description : Removes the recipient record for an */
2913 /* associated document */
2914 /* */
2915 /*----------------------------------------------------*/
2916
2917 PROCEDURE DELETE_RECIPIENT
2918 (
2919 p_api_version IN NUMBER := 1.0 ,
2920 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
2921 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
2922 p_validate_only IN VARCHAR2 := FND_API.G_TRUE ,
2923 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2924 p_recipient_rec IN recipient_rec ,
2925 x_return_status OUT NOCOPY VARCHAR2 ,
2926 x_msg_count OUT NOCOPY NUMBER ,
2927 x_msg_data OUT NOCOPY VARCHAR2)
2928 IS
2929 --
2930 --Code commented: pjha 23-Jul-2002 :because recipient is uneditable after creation, hence no need.
2931 --also bug#2473425
2932 CURSOR get_recipient_rec_info(c_recipient_document_id NUMBER)
2933 IS
2934 SELECT ROWID ,
2935 object_version_number
2936 FROM AHL_RECIPIENT_DOCUMENTS
2937 WHERE recipient_document_id = c_recipient_document_id
2938 FOR UPDATE OF object_version_number NOWAIT;
2939
2940 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_RECIPIENT';
2941 l_api_version CONSTANT NUMBER := 1.0;
2942 l_rowid ROWID;
2943 l_object_version_number NUMBER;
2944
2945 BEGIN
2946 -- Standard Start of API savepoint
2947 SAVEPOINT delete_recipient;
2948 -- Check if API is called in debug mode. If yes, enable debug.
2949
2950 IF G_DEBUG='Y' THEN
2951 AHL_DEBUG_PUB.enable_debug;
2952
2953 END IF;
2954 -- Debug info.
2955 IF G_DEBUG='Y' THEN
2956 IF G_DEBUG='Y' THEN
2957 AHL_DEBUG_PUB.debug( 'enter ahl_di_doc_index_pvt.Delete Recipient','+REP+');
2958
2959 END IF;
2960 END IF;
2961 -- Standard call to check for call compatibility.
2962 IF FND_API.to_boolean(p_init_msg_list)
2963 THEN
2964 FND_MSG_PUB.initialize;
2965 END IF;
2966 -- Initialize API return status to success
2967 x_return_status := 'S';
2968 -- Initialize message list if p_init_msg_list is set to TRUE.
2969 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
2970 p_api_version,
2971 l_api_name,G_PKG_NAME)
2972 THEN
2973 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2974 END IF;
2975 -- IF p_recipient_tbl.COUNT > 0
2976 -- THEN
2977 -- FOR i IN p_recipient_tbl.FIRST..p_recipient_tbl.LAST
2978 -- LOOP
2979 OPEN get_recipient_rec_info(p_recipient_rec.recipient_document_id);
2980 FETCH get_recipient_rec_info INTO l_rowid,
2981 l_object_version_number;
2982 IF (get_recipient_rec_info%NOTFOUND) THEN
2983 --Modified pjha 24-Jul-2002 for bug#2473425: Begin
2984 --FND_MESSAGE.SET_NAME('AHL','AHL_DI_RECIP_PTY_ID_INVALID');
2985 FND_MESSAGE.SET_NAME('AHL','AHL_DI_RECIPIENT_DELETED');
2986 FND_MESSAGE.SET_TOKEN('RECPNAME',p_recipient_rec.recipient_party_number);
2987 FND_MSG_PUB.ADD;
2988 RAISE FND_API.G_EXC_ERROR;
2989 --Modified pjha 24-Jul-2002 for bug#2473425: End
2990 END IF;
2991 CLOSE get_recipient_rec_info;
2992 --Commented pjha 24-Jul-2002 no need of this check since record can only be
2993 -- deleted and hence would previous check would suffice
2994 /*
2995 -- Check for version number
2996 IF (l_object_version_number <> p_recipient_rec.object_version_number)
2997 THEN
2998 FND_MESSAGE.SET_NAME('AHL','AHL_DI_DOC_TL_REC_CHANGED');
2999 FND_MSG_PUB.ADD;
3000 END IF;
3001 */
3002
3003 -- Delete the record from suppliers table
3004 DELETE FROM AHL_RECIPIENT_DOCUMENTS
3005 WHERE ROWID = l_rowid;
3006 -- END LOOP;
3007 -- END IF;
3008 --Standarad check for commit
3009 IF FND_API.TO_BOOLEAN(p_commit) THEN
3010 COMMIT;
3011 END IF;
3012 -- Debug info
3013 IF G_DEBUG='Y' THEN
3014 AHL_DEBUG_PUB.debug( 'End of private api Delete Recipient','+REP+');
3015
3016 END IF;
3017 -- Check if API is called in debug mode. If yes, disable debug.
3018 IF G_DEBUG='Y' THEN
3019 AHL_DEBUG_PUB.disable_debug;
3020
3021 END IF;
3022
3023 EXCEPTION
3024 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3025 ROLLBACK TO delete_recipient;
3026 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3027 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
3031 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'ERROR' );
3028 p_count => x_msg_count,
3029 p_data => x_msg_data);
3030 IF G_DEBUG='Y' THEN
3032 AHL_DEBUG_PUB.debug( 'ahl_di_doc_index_pvt.Delete Recipient','+REP+');
3033
3034
3035 -- Check if API is called in debug mode. If yes, disable debug.
3036 AHL_DEBUG_PUB.disable_debug;
3037
3038 END IF;
3039
3040 WHEN FND_API.G_EXC_ERROR THEN
3041 ROLLBACK TO delete_recipient;
3042 X_return_status := FND_API.G_RET_STS_ERROR;
3043 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
3044 p_count => x_msg_count,
3045 p_data => X_msg_data);
3046 -- Debug info.
3047 IF G_DEBUG='Y' THEN
3048 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
3049 AHL_DEBUG_PUB.debug( 'ahl_di_doc_index_pvt.Delete Recipient','+REP+');
3050
3051
3052 -- Check if API is called in debug mode. If yes, disable debug.
3053 AHL_DEBUG_PUB.disable_debug;
3054
3055 END IF;
3056
3057 WHEN OTHERS THEN
3058 ROLLBACK TO delete_recipient;
3059 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3060 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3061 THEN
3062 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_DI_DOC_INDEX_PVT',
3063 p_procedure_name => 'DELETE_RECIPIENT',
3064 p_error_text => SUBSTR(SQLERRM,1,240));
3065 END IF;
3066 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
3067 p_count => x_msg_count,
3068 p_data => X_msg_data);
3069
3070 -- Debug info.
3071 IF G_DEBUG='Y' THEN
3072 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'SQL ERROR' );
3073 AHL_DEBUG_PUB.debug( 'ahl_di_doc_index_pvt.Delete Recipient','+REP+');
3074
3075
3076 -- Check if API is called in debug mode. If yes, disable debug.
3077 AHL_DEBUG_PUB.disable_debug;
3078
3079 END IF;
3080
3081 END DELETE_RECIPIENT;
3082
3083 END AHL_DI_DOC_INDEX_PVT;
[Home] [Help]