[Home] [Help]
PACKAGE BODY: APPS.AHL_DI_DOC_INDEX_PUB
Source
1 PACKAGE BODY AHL_DI_DOC_INDEX_PUB AS
2 /* $Header: AHLPDIXB.pls 120.1 2006/02/07 03:51:04 sagarwal noship $ */
3 --
4 G_PKG_NAME VARCHAR2(30) := 'AHL_DI_DOC_INDEX_PUB';
5 --
6 /*-----------------------------------------------------------*/
7 /* procedure name: Check_lookup_name_Or_Id(private procedure)*/
8 /* description : used to retrieve lookup code */
9 /* */
10 /*-----------------------------------------------------------*/
11
12 PROCEDURE Check_lookup_name_Or_Id
13 ( p_lookup_type IN FND_LOOKUPS.lookup_type%TYPE,
14 p_lookup_code IN FND_LOOKUPS.lookup_code%TYPE,
15 p_meaning IN FND_LOOKUPS.meaning%TYPE,
16 p_check_id_flag IN VARCHAR2,
17 x_lookup_code OUT NOCOPY VARCHAR2,
18 x_return_status OUT NOCOPY VARCHAR2)
19 IS
20
21
22 BEGIN
23 IF (p_lookup_code IS NOT NULL) THEN
24 IF (p_check_id_flag = 'Y') THEN
25 SELECT lookup_code INTO x_lookup_code
26 FROM FND_LOOKUP_VALUES_VL
27 WHERE lookup_type = p_lookup_type
28 AND lookup_code = p_lookup_code
29 AND ENABLED_FLAG= 'Y'
30 AND sysdate between nvl(start_date_active,sysdate)
31 AND nvl(end_date_active,sysdate);
32 ELSE
33 x_lookup_code := p_lookup_code;
34 END IF;
35 ELSE
36 SELECT lookup_code INTO x_lookup_code
37 FROM FND_LOOKUP_VALUES_VL
38 WHERE lookup_type = p_lookup_type
39 AND upper(meaning) = upper(p_meaning)
40 AND ENABLED_FLAG= 'Y'
41 AND sysdate between nvl(start_date_active,sysdate)
42 AND nvl(end_date_active,sysdate);
43 END IF;
44 x_return_status := FND_API.G_RET_STS_SUCCESS;
45 EXCEPTION
46 WHEN no_data_found THEN
47 x_return_status := FND_API.G_RET_STS_ERROR;
48 WHEN too_many_rows THEN
49 x_return_status := FND_API.G_RET_STS_ERROR;
50 WHEN OTHERS THEN
51 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
52 RAISE;
53 END;
54
55
56 /*------------------------------------------------------*/
57 /* procedure name: create_document */
58 /* description : Creates new document record and its */
59 /* associated suppliers, recipients, */
60 /* subscriptions,revision and copies */
61 /* */
62 /*------------------------------------------------------*/
63
64 PROCEDURE CREATE_DOCUMENT
65 (
66 p_api_version IN NUMBER := '1.0' ,
67 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
68 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
69 p_validate_only IN VARCHAR2 := FND_API.G_TRUE ,
70 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
71 p_x_document_tbl IN OUT NOCOPY Document_Tbl ,
72 -- p_x_doc_rev_tbl IN OUT NOCOPY AHL_DI_DOC_REVISION_PUB.Revision_Tbl ,
73 -- p_x_doc_rev_copy_tbl IN OUT NOCOPY AHL_DI_DOC_REVISION_PUB.Revision_Copy_Tbl,
74 -- p_x_subscription_tbl IN OUT NOCOPY AHL_DI_SUBSCRIPTION_PUB.Subscription_Tbl,
75 p_x_supplier_tbl IN OUT NOCOPY Supplier_Tbl ,
76 p_x_recipient_tbl IN OUT NOCOPY Recipient_Tbl ,
77 p_module_type IN VARCHAR2 ,
78 x_return_status OUT NOCOPY VARCHAR2 ,
79 x_msg_count OUT NOCOPY NUMBER ,
80 x_msg_data OUT NOCOPY VARCHAR2)
81 IS
82 -- Used to retrieve the party id for passed meaning
83 CURSOR for_party_name(c_party_number IN VARCHAR2)
84 IS
85 SELECT party_id
86 FROM hz_parties
87 WHERE party_number = c_party_number;
88 -- Used to retrieve party id for passed id
89 CURSOR for_party_id(c_party_id IN NUMBER)
90 IS
91 SELECT party_id
92 FROM hz_parties
93 WHERE party_id = c_party_id;
94 -- Used to retrieve vendor id from po vendors
95 CURSOR for_vendor_id(c_segment1 IN VARCHAR2)
96 IS
97 SELECT vendor_id
98 FROM po_vendors
99 WHERE segment1 = c_segment1;
100
101 --Enhancement #2275357: pbarman 1st April 2003
102 --Cursor to retrieve operator code from hz parties
103 CURSOR get_operator_name_hz(c_operator_name VARCHAR2)
104 IS
105 SELECT party_id
106 FROM HZ_PARTIES
107 WHERE upper(party_name) = upper(c_operator_name)
108 AND ( party_type ='ORGANIZATION' or party_type = 'PERSON' );
109
110 -- For Bug Fix #3446159
111 CURSOR get_operator_name_hz_id(c_operator_name VARCHAR2, c_operator_id NUMBER)
112 IS
113 SELECT party_id
114 FROM HZ_PARTIES
115 WHERE upper(party_name) = upper(c_operator_name)
116 AND party_id = c_operator_id
117 AND ( party_type ='ORGANIZATION' or party_type = 'PERSON' );
118
119
120 --
121 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_DOCUMENT';
122 l_api_version CONSTANT NUMBER := 1.0;
123 l_num_rec NUMBER;
124 l_msg_count NUMBER;
125 l_msg_data VARCHAR2(2000);
126 l_return_status VARCHAR2(1);
127 l_source_party_id NUMBER;
128 l_supplier_id NUMBER;
129 l_recipient_id NUMBER;
130 l_recipient_party_id NUMBER;
131 l_requested_by_party_id NUMBER;
132 l_approved_by_party_id NUMBER;
133 l_received_by_party_id NUMBER;
134 l_doc_type_code VARCHAR2(30);
135 l_doc_sub_type_code VARCHAR2(30);
136 l_preference_code VARCHAR2(30);
137 l_status_code VARCHAR2(30);
138 l_media_type_code VARCHAR2(30);
139 l_frequency_code VARCHAR2(30);
140 l_subscription_type_code VARCHAR2(30);
141 l_revision_type_code VARCHAR2(30);
142 l_revision_status_code VARCHAR2(30);
143 l_copy_type_code VARCHAR2(30);
144 l_operator_code VARCHAR2(30);
145 l_product_type_code VARCHAR2(30);
146 l_document_tbl AHL_DI_DOC_INDEX_PVT.document_tbl;
147 -- l_revision_tbl AHL_DI_DOC_REVISION_PVT.revision_tbl;
148 -- l_revision_copy_tbl AHL_DI_DOC_REVISION_PVT.revision_copy_tbl;
149 -- l_subscription_tbl AHL_DI_SUBSCRIPTION_PVT.subscription_tbl;
150 l_supplier_tbl AHL_DI_DOC_INDEX_PVT.supplier_tbl;
151 l_recipient_tbl AHL_DI_DOC_INDEX_PVT.recipient_tbl;
152 l_init_msg_list VARCHAR2(10) := FND_API.G_TRUE;
153
154
155 BEGIN
156 -- Standard Start of API savepoint
157 SAVEPOINT create_document;
158 -- Check if API is called in debug mode. If yes, enable debug.
159 AHL_DEBUG_PUB.enable_debug;
160 -- Debug info.
161 IF AHL_DEBUG_PUB.G_FILE_DEBUG THEN
162 AHL_DEBUG_PUB.debug( 'enter ahl_di_doc_index_pub.Create Document','+DI+');
163 END IF;
164
165 -- Standard call to check for call compatibility.
166 IF FND_API.to_boolean(l_init_msg_list)
167 THEN
168 FND_MSG_PUB.initialize;
169 END IF;
170 -- Initialize API return status to success
171 x_return_status := 'S';
172 -- Initialize message list if p_init_msg_list is set to TRUE.
173 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
174 p_api_version,
175 l_api_name,G_PKG_NAME)
176 THEN
177 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
178 END IF;
179 -- Start of API Body
180 IF p_x_document_tbl.COUNT > 0
181 THEN
182 FOR i IN p_x_document_tbl.FIRST..p_x_document_tbl.LAST
183 LOOP
184 --Process flag is 'D' i.e Create Document
185 IF p_x_document_tbl(i).process_flag = 'D'
186 THEN
187 -- Module type is 'JSP' then make it null for the following fields
188 IF (p_module_type = 'JSP') THEN
189 p_x_document_tbl(i).source_party_id := null;
190 p_x_document_tbl(i).product_type_code := null;
191 END IF;
192 --For Source by Party Id
193 IF (p_x_document_tbl(i).source_party_id IS NULL) OR
194 (p_x_document_tbl(i).source_party_id = FND_API.G_MISS_NUM)
195 THEN
196 -- If Party Name is available
197 IF (p_x_document_tbl(i).source_party_number IS NOT NULL) AND
198 (p_x_document_tbl(i).source_party_number <> FND_API.G_MISS_CHAR)
199 THEN
200 OPEN for_party_name(p_x_document_tbl(i).source_party_number);
201 FETCH for_party_name INTO l_source_party_id;
202 IF for_party_name%FOUND
203 THEN
204 l_document_tbl(i).source_party_id := l_source_party_id;
205 ELSE
206 FND_MESSAGE.SET_NAME('AHL','AHL_DI_SOURCE_ID_NOT_EXISTS');
207 FND_MSG_PUB.ADD;
208 END IF;
209 CLOSE for_party_name;
210 ELSE
211 --Both Party Id and Name are missing
212 FND_MESSAGE.SET_NAME('AHL','AHL_DI_SOURCE_PARTY_ID_NULL');
213 FND_MSG_PUB.ADD;
214 END IF;
215 --Check for If ID present
216 ELSIF (p_x_document_tbl(i).source_party_id IS NOT NULL AND
217 p_x_document_tbl(i).source_party_id <> FND_API.G_MISS_NUM)
218 THEN
219 OPEN for_party_id(p_x_document_tbl(i).source_party_id);
220 FETCH for_party_id INTO l_document_tbl(i).source_party_id;
221 IF for_party_id%FOUND
222 THEN
223 l_document_tbl(i).source_party_id := p_x_document_tbl(i).source_party_id;
224 ELSE
225 FND_MESSAGE.SET_NAME('AHL','AHL_DI_SOURCE_ID_INVALID');
226 FND_MSG_PUB.ADD;
227 END IF;
228 CLOSE for_party_id;
229 END IF;
230
231 --For Operator Code, Meaning presnts
232 IF p_x_document_tbl(i).operator_name IS NOT NULL AND
233 p_x_document_tbl(i).operator_name <> FND_API.G_MISS_CHAR
234 THEN
235 -- Check if operator name and id match
236 OPEN get_operator_name_hz_id(p_x_document_tbl(i).operator_name, TO_NUMBER(p_x_document_tbl(i).operator_code));
237
238 FETCH get_operator_name_hz_id INTO l_document_tbl(i).operator_code;
239 IF get_operator_name_hz_id%NOTFOUND THEN
240 -- the operator name has been changed
241
242 --Enhancement #2275357: pbarman 1st April 2003
243 CLOSE get_operator_name_hz_id;
244 OPEN get_operator_name_hz(p_x_document_tbl(i).operator_name);
245 LOOP
246 FETCH get_operator_name_hz INTO l_document_tbl(i).operator_code;
247 EXIT WHEN get_operator_name_hz%NOTFOUND;
248 END LOOP;
249
250
251 IF get_operator_name_hz%ROWCOUNT = 0
252 THEN
253 FND_MESSAGE.SET_NAME('AHL','AHL_DI_OPERATOR_CODE_NOT_EXIST');
254 FND_MSG_PUB.ADD;
255 ELSIF get_operator_name_hz%ROWCOUNT > 1
256 THEN
257 FND_MESSAGE.SET_NAME('AHL','AHL_DI_OPERATOR_USE_LOV');
258 FND_MSG_PUB.ADD;
259 END IF;
260 CLOSE get_operator_name_hz;
261 END IF;
262
263 END IF;
264
265 --For Product type Code, Meaning presnts
266 IF p_x_document_tbl(i).product_type_desc IS NOT NULL AND
267 p_x_document_tbl(i).product_type_desc <> FND_API.G_MISS_CHAR
268 THEN
269 Check_lookup_name_Or_Id (
270 --Enhancement #2525604 pbarman : April 2003
271 p_lookup_type => 'ITEM_TYPE',
272 p_lookup_code => null,
273 p_meaning => p_x_document_tbl(i).product_type_desc,
274 p_check_id_flag => 'Y',
275 x_lookup_code => l_document_tbl(i).product_type_code,
276 x_return_status => l_return_status);
277
278 IF nvl(l_return_status, 'X') <> 'S'
279 THEN
280 FND_MESSAGE.SET_NAME('AHL','AHL_DI_PRODTYPE_CODE_NOT_EXIST');
281 FND_MSG_PUB.ADD;
282 END IF;
283
284 --ID presntes
285 ELSIF p_x_document_tbl(i).product_type_code IS NOT NULL AND
286 p_x_document_tbl(i).product_type_code <> FND_API.G_MISS_CHAR
287 THEN
288 l_document_tbl(i).product_type_code := p_x_document_tbl(i).product_type_code;
289 --Both missing
290 ELSE
291 l_document_tbl(i).product_type_code := p_x_document_tbl(i).product_type_code;
292 END IF;
293
294 --For Doc Type Code
295 IF p_x_document_tbl(i).doc_type_desc IS NOT NULL AND
296 p_x_document_tbl(i).doc_type_desc <> FND_API.G_MISS_CHAR
297 THEN
298 Check_lookup_name_Or_Id (
299 p_lookup_type => 'AHL_DOC_TYPE',
300 p_lookup_code => null,
301 p_meaning => p_x_document_tbl(i).doc_type_desc,
302 p_check_id_flag => 'Y',
303 x_lookup_code => l_document_tbl(i).doc_type_code,
304 x_return_status => l_return_status);
305
306 IF nvl(l_return_status, 'X') <> 'S'
307 THEN
308 FND_MESSAGE.SET_NAME('AHL','AHL_DI_DOC_TYPE_CODE_NOT_EXIST');
309 FND_MSG_PUB.ADD;
310 END IF;
311 END IF;
312 -- Id presents
313 IF p_x_document_tbl(i).doc_type_code IS NOT NULL AND
314 p_x_document_tbl(i).doc_type_code <> FND_API.G_MISS_CHAR
315 THEN
316 l_document_tbl(i).doc_type_code := p_x_document_tbl(i).doc_type_code;
317 ELSE
318 FND_MESSAGE.SET_NAME('AHL','AHL_DI_DOC_TYPE_CODE_NOT_EXIST');
319 FND_MSG_PUB.ADD;
320 END IF;
321
322 --For Doc Sub Type Code, meaning presents
323 IF p_x_document_tbl(i).doc_sub_type_desc IS NOT NULL AND
324 p_x_document_tbl(i).doc_sub_type_desc <> FND_API.G_MISS_CHAR
325 THEN
326
327 Check_lookup_name_Or_Id (
328 p_lookup_type => 'AHL_DOC_SUB_TYPE_CODE',
329 p_lookup_code => null,
330 p_meaning => p_x_document_tbl(i).doc_sub_type_desc,
331 p_check_id_flag => 'Y',
332 x_lookup_code => l_document_tbl(i).doc_sub_type_code,
333 x_return_status => l_return_status);
334
335 IF nvl(l_return_status, 'X') <> 'S'
336 THEN
337 FND_MESSAGE.SET_NAME('AHL','AHL_DI_DOC_SUBT_COD_NOT_EXISTS');
338 FND_MSG_PUB.ADD;
339 END IF;
340 END IF;
341 --Id presents
342 IF p_x_document_tbl(i).doc_sub_type_code IS NOT NULL AND
343 p_x_document_tbl(i).doc_sub_type_code <> FND_API.G_MISS_CHAR
344 THEN
345 l_document_tbl(i).doc_sub_type_code := p_x_document_tbl(i).doc_sub_type_code;
346 ELSE
347 l_document_tbl(i).doc_sub_type_code := p_x_document_tbl(i).doc_sub_type_code;
348 END IF;
349
350
351 --
352
353 l_document_tbl(i).document_id := p_x_document_tbl(i).document_id;
354 l_document_tbl(i).document_no := p_x_document_tbl(i).document_no;
355 l_document_tbl(i).subscribe_avail_flag := p_x_document_tbl(i).subscribe_avail_flag;
356 l_document_tbl(i).subscribe_to_flag := p_x_document_tbl(i).subscribe_to_flag;
357 l_document_tbl(i).document_title := p_x_document_tbl(i).document_title;
358 l_document_tbl(i).language := p_x_document_tbl(i).language;
359 l_document_tbl(i).source_lang := p_x_document_tbl(i).source_lang;
360 l_document_tbl(i).attribute_category := p_x_document_tbl(i).attribute_category;
361 l_document_tbl(i).attribute1 := p_x_document_tbl(i).attribute1;
362 l_document_tbl(i).attribute2 := p_x_document_tbl(i).attribute2;
363 l_document_tbl(i).attribute3 := p_x_document_tbl(i).attribute3;
364 l_document_tbl(i).attribute4 := p_x_document_tbl(i).attribute4;
365 l_document_tbl(i).attribute5 := p_x_document_tbl(i).attribute5;
366 l_document_tbl(i).attribute6 := p_x_document_tbl(i).attribute6;
367 l_document_tbl(i).attribute7 := p_x_document_tbl(i).attribute7;
368 l_document_tbl(i).attribute8 := p_x_document_tbl(i).attribute8;
369 l_document_tbl(i).attribute9 := p_x_document_tbl(i).attribute9;
370 l_document_tbl(i).attribute10 := p_x_document_tbl(i).attribute10;
371 l_document_tbl(i).attribute11 := p_x_document_tbl(i).attribute11;
372 l_document_tbl(i).attribute12 := p_x_document_tbl(i).attribute12;
373 l_document_tbl(i).attribute13 := p_x_document_tbl(i).attribute13;
374 l_document_tbl(i).attribute14 := p_x_document_tbl(i).attribute14;
375 l_document_tbl(i).attribute15 := p_x_document_tbl(i).attribute15;
376 l_document_tbl(i).delete_flag := p_x_document_tbl(i).delete_flag;
377 l_document_tbl(i).object_version_number := p_x_document_tbl(i).object_version_number;
378
379 --Standard check to count messages
380 l_msg_count := FND_MSG_PUB.count_msg;
381
382 IF l_msg_count > 0 THEN
383 X_msg_count := l_msg_count;
384 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
385 THEN
386 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
387 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
388 ELSE
389 X_return_status := FND_API.G_RET_STS_ERROR;
390 RAISE FND_API.G_EXC_ERROR;
391 END IF;
392 END IF;
393 -- Debug info.
394 IF AHL_DEBUG_PUB.G_FILE_DEBUG THEN
395 AHL_DEBUG_PUB.debug( 'Before processing Supplier Record ahl_di_doc_index_pub.create_document','+DI+');
396 END IF;
397 --For Supplier Record, If the process flag is 'S' i.e Create Supplier
398 ELSIF p_x_document_tbl(i).process_flag = 'S'
399 THEN
400 --For Supplier Id
401 IF p_x_supplier_tbl.COUNT > 0
402 THEN
403 FOR i IN p_x_supplier_tbl.FIRST..p_x_supplier_tbl.LAST
404 LOOP
405 IF (p_x_supplier_tbl(i).supplier_id IS NULL) OR
406 (p_x_supplier_tbl(i).supplier_id = FND_API.G_MISS_NUM) THEN
407 -- If Supplier Name is available
408 IF (p_x_supplier_tbl(i).supplier_number IS NOT NULL) AND
409 (p_x_supplier_tbl(i).supplier_number <> FND_API.G_MISS_CHAR)
410 THEN
411 IF ahl_di_doc_index_pvt.get_product_install_status('PO') IN ('N','L')
412 THEN
413 OPEN for_party_name(p_x_supplier_tbl(i).supplier_number);
414 FETCH for_party_name INTO l_supplier_id;
415 IF for_party_name%FOUND
416 THEN
417 l_supplier_tbl(i).supplier_id := l_supplier_id;
418 ELSE
419 FND_MESSAGE.SET_NAME('AHL','AHL_DI_SUPPL_ID_NOT_EXIST');
420 FND_MESSAGE.SET_TOKEN('SUPNAME',p_x_supplier_tbl(i).supplier_number);
421 FND_MSG_PUB.ADD;
422 END IF;
423 CLOSE for_party_name;
424 ELSIF ahl_di_doc_index_pvt.get_product_install_status('PO') IN ('I','S')
425 THEN
426 OPEN for_vendor_id(p_x_supplier_tbl(i).supplier_number);
427 FETCH for_vendor_id INTO l_supplier_id;
428 IF for_vendor_id%FOUND
429 THEN
430 l_supplier_tbl(i).supplier_id := l_supplier_id;
431 ELSE
432 FND_MESSAGE.SET_NAME('AHL','AHL_DI_SUPPL_ID_NOT_EXIST');
433 FND_MESSAGE.SET_TOKEN('SUPNAME',p_x_supplier_tbl(i).supplier_number);
434 FND_MSG_PUB.ADD;
435 END IF;
436 CLOSE for_vendor_id;
437 END IF;
438 --Id presents
439 ELSIF (p_x_supplier_tbl(i).supplier_id IS NOT NULL) AND
440 (p_x_supplier_tbl(i).supplier_id <> FND_API.G_MISS_NUM)
441 THEN
442 l_supplier_tbl(i).supplier_id := p_x_supplier_tbl(i).supplier_id;
443 ELSE
444 --Both Supplier Id and Name are missing
445 FND_MESSAGE.SET_NAME('AHL','AHL_DI_SUPPL_ID_NOT_EXIST');
446 FND_MESSAGE.SET_TOKEN('SUPNAME',p_x_supplier_tbl(i).supplier_number);
447 FND_MSG_PUB.ADD;
448 END IF;
449 END IF;
450 -- For Preference Code, meaning presents
451 IF p_x_supplier_tbl(i).preference_desc IS NOT NULL AND
452 p_x_supplier_tbl(i).preference_desc <> FND_API.G_MISS_CHAR
453 THEN
454
455 Check_lookup_name_Or_Id (
456 p_lookup_type => 'AHL_SUPPLIER_PREF_TYPE',
457 p_lookup_code => null,
458 p_meaning => p_x_supplier_tbl(i).preference_desc,
459 p_check_id_flag => 'Y',
460 x_lookup_code => l_supplier_tbl(i).preference_code,
461 x_return_status => l_return_status);
462
463 IF nvl(l_return_status, 'X') <> 'S'
464 THEN
465 FND_MESSAGE.SET_NAME('AHL','AHL_DI_SUPPLIER_PREF_TYPE');
466 FND_MSG_PUB.ADD;
467 END IF;
468 END IF;
469 -- Pref Code presents
470 IF p_x_supplier_tbl(i).preference_code IS NOT NULL AND
471 p_x_supplier_tbl(i).preference_code <> FND_API.G_MISS_CHAR
472 THEN
473 l_supplier_tbl(i).preference_code := p_x_supplier_tbl(i).preference_code;
474 ELSE
475 --Both missing
476 l_supplier_tbl(i).preference_code := p_x_supplier_tbl(i).preference_code;
477 END IF;
478
479 l_supplier_tbl(i).supplier_document_id := p_x_supplier_tbl(i).supplier_document_id;
480 l_supplier_tbl(i).document_id := p_x_supplier_tbl(i).document_id;
481 l_supplier_tbl(i).attribute_category := p_x_supplier_tbl(i).attribute_category;
482 l_supplier_tbl(i).attribute1 := p_x_supplier_tbl(i).attribute1;
483 l_supplier_tbl(i).attribute2 := p_x_supplier_tbl(i).attribute2;
484 l_supplier_tbl(i).attribute3 := p_x_supplier_tbl(i).attribute3;
485 l_supplier_tbl(i).attribute4 := p_x_supplier_tbl(i).attribute4;
486 l_supplier_tbl(i).attribute5 := p_x_supplier_tbl(i).attribute5;
487 l_supplier_tbl(i).attribute6 := p_x_supplier_tbl(i).attribute6;
488 l_supplier_tbl(i).attribute7 := p_x_supplier_tbl(i).attribute7;
489 l_supplier_tbl(i).attribute8 := p_x_supplier_tbl(i).attribute8;
490 l_supplier_tbl(i).attribute9 := p_x_supplier_tbl(i).attribute9;
491 l_supplier_tbl(i).attribute10 := p_x_supplier_tbl(i).attribute10;
492 l_supplier_tbl(i).attribute11 := p_x_supplier_tbl(i).attribute11;
493 l_supplier_tbl(i).attribute12 := p_x_supplier_tbl(i).attribute12;
494 l_supplier_tbl(i).attribute13 := p_x_supplier_tbl(i).attribute13;
495 l_supplier_tbl(i).attribute14 := p_x_supplier_tbl(i).attribute14;
496 l_supplier_tbl(i).attribute15 := p_x_supplier_tbl(i).attribute15;
497 l_supplier_tbl(i).delete_flag := p_x_supplier_tbl(i).delete_flag;
498 l_supplier_tbl(i).object_version_number := p_x_supplier_tbl(i).object_version_number;
499 --Standard check to count messages
500 --{{adharia
501 /*
502 l_msg_count := FND_MSG_PUB.count_msg;
503
504 IF l_msg_count > 0 THEN
505 X_msg_count := l_msg_count;
506 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
507 THEN
508 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
509 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
510 ELSE
511 X_return_status := FND_API.G_RET_STS_ERROR;
512 RAISE FND_API.G_EXC_ERROR;
513 END IF;
514 END IF;
515 */
516 --}}adharia
517 END LOOP;
518 END IF;
519 -- Debug info.
520 IF AHL_DEBUG_PUB.G_FILE_DEBUG THEN
521 AHL_DEBUG_PUB.debug( 'Before processing Recipient Record ahl_di_doc_index_pub.create_document','+DI+');
522 END IF;
523 --for Creating Recipient Record, 'R'
524 ELSIF p_x_document_tbl(i).process_flag = 'R'
525 THEN
526 IF p_x_recipient_tbl.COUNT > 0
527 THEN
528 FOR i IN p_x_recipient_tbl.FIRST..p_x_recipient_tbl.LAST
529 LOOP
530 --For Recipient Id
531 IF (p_x_recipient_tbl(i).recipient_party_id IS NULL) OR
532 (p_x_recipient_tbl(i).recipient_party_id = FND_API.G_MISS_NUM)
533 THEN
534 -- If Recipient Name is available
535 IF (p_x_recipient_tbl(i).recipient_party_number IS NOT NULL) AND
536 (p_x_recipient_tbl(i).recipient_party_number <> FND_API.G_MISS_CHAR)
537 THEN
538 OPEN for_party_name(p_x_recipient_tbl(i).recipient_party_number);
539 FETCH for_party_name INTO l_recipient_id;
540 IF for_party_name%FOUND
541 THEN
542 l_recipient_tbl(i).recipient_party_id := l_recipient_id;
543 ELSE
544 FND_MESSAGE.SET_NAME('AHL','AHL_DI_RECIPIENT_ID_NOT_EXIST');
545 FND_MESSAGE.SET_TOKEN('RECPNAME',p_x_recipient_tbl(i).recipient_party_number);
546 FND_MSG_PUB.ADD;
547 END IF;
548 CLOSE for_party_name;
549 --ID presents
550 ELSIF (p_x_recipient_tbl(i).recipient_party_id IS NOT NULL) AND
551 (p_x_recipient_tbl(i).recipient_party_id <> FND_API.G_MISS_NUM)
552 THEN
553 l_recipient_tbl(i).recipient_party_id := p_x_recipient_tbl(i).recipient_party_id;
554 ELSE
555 --Both Recipient Id and Name are missing
556 FND_MESSAGE.SET_NAME('AHL','AHL_DI_RECIPIENT_ID_NOT_EXIST');
557 FND_MESSAGE.SET_TOKEN('RECPNAME',p_x_recipient_tbl(i).recipient_party_number);
558 FND_MSG_PUB.ADD;
559 END IF;
560 END IF;
561 l_recipient_tbl(i).recipient_document_id := p_x_recipient_tbl(i).recipient_document_id;
562 l_recipient_tbl(i).document_id := p_x_recipient_tbl(i).document_id;
563 l_recipient_tbl(i).attribute_category := p_x_recipient_tbl(i).attribute_category;
564 l_recipient_tbl(i).attribute1 := p_x_recipient_tbl(i).attribute1;
565 l_recipient_tbl(i).attribute2 := p_x_recipient_tbl(i).attribute2;
566 l_recipient_tbl(i).attribute3 := p_x_recipient_tbl(i).attribute3;
567 l_recipient_tbl(i).attribute4 := p_x_recipient_tbl(i).attribute4;
568 l_recipient_tbl(i).attribute5 := p_x_recipient_tbl(i).attribute5;
569 l_recipient_tbl(i).attribute6 := p_x_recipient_tbl(i).attribute6;
570 l_recipient_tbl(i).attribute7 := p_x_recipient_tbl(i).attribute7;
571 l_recipient_tbl(i).attribute8 := p_x_recipient_tbl(i).attribute8;
572 l_recipient_tbl(i).attribute9 := p_x_recipient_tbl(i).attribute9;
573 l_recipient_tbl(i).attribute10 := p_x_recipient_tbl(i).attribute10;
574 l_recipient_tbl(i).attribute11 := p_x_recipient_tbl(i).attribute11;
575 l_recipient_tbl(i).attribute12 := p_x_recipient_tbl(i).attribute12;
576 l_recipient_tbl(i).attribute13 := p_x_recipient_tbl(i).attribute13;
577 l_recipient_tbl(i).attribute14 := p_x_recipient_tbl(i).attribute14;
578 l_recipient_tbl(i).attribute15 := p_x_recipient_tbl(i).attribute15;
579 l_recipient_tbl(i).delete_flag := p_x_recipient_tbl(i).delete_flag;
580 l_recipient_tbl(i).object_version_number := p_x_recipient_tbl(i).object_version_number;
581 --Standard check to count messages
582 --{{adharia
583 /*
584 l_msg_count := FND_MSG_PUB.count_msg;
585
586 IF l_msg_count > 0 THEN
587 X_msg_count := l_msg_count;
588 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
589 THEN
590 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
591 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
592 ELSE
593 X_return_status := FND_API.G_RET_STS_ERROR;
594 RAISE FND_API.G_EXC_ERROR;
595 END IF;
596 END IF;
597 */
598 --{{adharia
599
600 END LOOP;
601 END IF;
602 END IF;
603 END LOOP;
604 END IF;
605 /*
606 -- For Creating Subscriptions Record
607 IF p_x_subscription_tbl.count > 0
608 THEN
609 FOR i IN p_x_subscription_tbl.FIRST..p_x_subscription_tbl.LAST
610 LOOP
611 -- Module type is 'JSP' then make it null for the following fields
612 IF (p_module_type = 'JSP') THEN
613 p_x_subscription_tbl(i).requested_by_party_id := null;
614 p_x_subscription_tbl(i).subscribed_frm_party_id := null;
615 END IF;
616 -- For Requested by party Id
617 IF (p_x_subscription_tbl(i).requested_by_pty_name IS NOT NULL) AND
618 (p_x_subscription_tbl(i).requested_by_pty_name <> FND_API.G_MISS_CHAR)
619 THEN
620 OPEN for_party_name(p_x_subscription_tbl(i).requested_by_pty_name);
621 FETCH for_party_name INTO l_requested_by_party_id;
622 IF for_party_name%FOUND
623 THEN
624 l_subscription_tbl(i).requested_by_party_id := l_requested_by_party_id;
625 ELSE
626 FND_MESSAGE.SET_NAME('AHL','AHL_DI_REQ_PTY_ID_NOT_EXISTS');
627 FND_MSG_PUB.ADD;
628 END IF;
629 CLOSE for_party_name;
630
631 -- Party Id is present
632 ELSIF (p_x_subscription_tbl(i).requested_by_party_id IS NOT NULL) AND
633 (p_x_subscription_tbl(i).requested_by_party_id <> FND_API.G_MISS_NUM)
634 THEN
635 OPEN for_party_id(p_x_subscription_tbl(i).requested_by_party_id);
636 FETCH for_party_id INTO l_requested_by_party_id;
637 IF for_party_id%FOUND
638 THEN
639 l_subscription_tbl(i).requested_by_party_id := l_requested_by_party_id;
640 ELSE
641 FND_MESSAGE.SET_NAME('AHL','AHL_DI_REQ_PTY_ID_NOT_EXISTS');
642 FND_MSG_PUB.ADD;
643 END IF;
644 CLOSE for_party_id;
645 ELSE
646 --Both Party Id and Name are missing
647 FND_MESSAGE.SET_NAME('AHL','AHL_DI_REQ_PTY_ID_NULL');
648 FND_MSG_PUB.ADD;
649 END IF;
650 --For Subscribed from party id
651 -- If subscribed from Party Name is available
652 IF (p_x_subscription_tbl(i).subscribed_frm_pty_name IS NOT NULL) AND
653 (p_x_subscription_tbl(i).subscribed_frm_pty_name <> FND_API.G_MISS_CHAR)
654 THEN
655 OPEN for_party_name(p_x_subscription_tbl(i).subscribed_frm_pty_name);
656 FETCH for_party_name INTO l_supplier_id;
657 IF for_party_name%FOUND
658 THEN
659 l_subscription_tbl(i).subscribed_frm_party_id := l_supplier_id;
660 ELSE
661 l_subscription_tbl(i).subscribed_frm_party_id := p_x_subscription_tbl(i).subscribed_frm_party_id;
662 END IF;
663 CLOSE for_party_name;
664 -- If Part id is present
665 ELSIF (p_x_subscription_tbl(i).subscribed_frm_party_id IS NOT NULL AND
666 p_x_subscription_tbl(i).subscribed_frm_party_id <> FND_API.G_MISS_NUM)
667 THEN
668 OPEN for_party_id(p_x_subscription_tbl(i).subscribed_frm_party_id);
669 FETCH for_party_id INTO l_supplier_id;
670 IF for_party_id%FOUND
671 THEN
672 l_subscription_tbl(i).subscribed_frm_party_id := l_supplier_id;
673 ELSE
674 l_subscription_tbl(i).subscribed_frm_party_id := p_x_subscription_tbl(i).subscribed_frm_party_id;
675 END IF;
676 CLOSE for_party_id;
677 --Both are missing
678 ELSE
679 l_subscription_tbl(i).subscribed_frm_party_id := p_x_subscription_tbl(i).subscribed_frm_party_id;
680 END IF;
681 --For Media Type Code
682 IF p_x_subscription_tbl(i).media_type_desc IS NOT NULL AND
683 p_x_subscription_tbl(i).media_type_desc <> FND_API.G_MISS_CHAR
684 THEN
685 --
686 Check_lookup_name_Or_Id (
687 p_lookup_type => 'AHL_MEDIA_TYPE',
688 p_lookup_code => null,
689 p_meaning => p_x_subscription_tbl(i).media_type_desc,
690 p_check_id_flag => 'Y',
691 x_lookup_code => l_subscription_tbl(i).media_type_code,
692 x_return_status => l_return_status);
693
694 IF nvl(l_return_status, 'X') <> 'S'
695 THEN
696 FND_MESSAGE.SET_NAME('AHL','AHL_DI_MEDTYP_CODE_NOT_EXISTS');
697 FND_MSG_PUB.ADD;
698 END IF;
699 END IF;
700 -- If id is present
701 IF p_x_subscription_tbl(i).media_type_code IS NOT NULL AND
702 p_x_subscription_tbl(i).media_type_code <> FND_API.G_MISS_CHAR
703 THEN
704 l_subscription_tbl(i).media_type_code := p_x_subscription_tbl(i).media_type_code;
705 ELSE
706 -- Both are missing
707 l_subscription_tbl(i).media_type_code := p_x_subscription_tbl(i).media_type_code;
708 END IF;
709
710 --For Subscription Type Code
711 IF p_x_subscription_tbl(i).subscription_type_desc IS NOT NULL AND
712 p_x_subscription_tbl(i).subscription_type_desc <> FND_API.G_MISS_CHAR
713 THEN
714 Check_lookup_name_Or_Id (
715 p_lookup_type => 'AHL_SUBSCRIPTION_TYPE',
716 p_lookup_code => null,
717 p_meaning => p_x_subscription_tbl(i).subscription_type_desc,
718 p_check_id_flag => 'Y',
719 x_lookup_code => l_subscription_tbl(i).subscription_type_code,
720 x_return_status => l_return_status);
721
722 IF nvl(l_return_status, 'X') <> 'S'
723 THEN
724 FND_MESSAGE.SET_NAME('AHL','AHL_DI_SUBTYP_CODE_NOT_EXISTS');
725 FND_MSG_PUB.ADD;
726 END IF;
727 END IF;
728 -- Code Presents
729 IF p_x_subscription_tbl(i).subscription_type_code IS NOT NULL AND
730 p_x_subscription_tbl(i).subscription_type_code <> FND_API.G_MISS_CHAR
731 THEN
732 l_subscription_tbl(i).subscription_type_code := p_x_subscription_tbl(i).subscription_type_code;
733 ELSE
734 l_subscription_tbl(i).subscription_type_code := p_x_subscription_tbl(i).subscription_type_code;
735 END IF;
736
737 --For Frequency Code
738 IF p_x_subscription_tbl(i).frequency_desc IS NOT NULL AND
739 p_x_subscription_tbl(i).frequency_desc <> FND_API.G_MISS_CHAR
740 THEN
741
742 Check_lookup_name_Or_Id (
743 p_lookup_type => 'AHL_FREQUENCY_TYPE',
744 p_lookup_code => null,
745 p_meaning => p_x_subscription_tbl(i).frequency_desc,
746 p_check_id_flag => 'Y',
747 x_lookup_code => l_subscription_tbl(i).frequency_code,
748 x_return_status => l_return_status);
749
750 IF nvl(l_return_status, 'X') <> 'S'
751 THEN
752 FND_MESSAGE.SET_NAME('AHL','AHL_DI_FREQCY_CODE_NOT_EXISTS');
753 FND_MSG_PUB.ADD;
754 END IF;
755 END IF;
756 -- Frequency Code present
757 IF p_x_subscription_tbl(i).frequency_code IS NOT NULL AND
758 p_x_subscription_tbl(i).frequency_code <> FND_API.G_MISS_CHAR
759 THEN
760 l_subscription_tbl(i).frequency_code := p_x_subscription_tbl(i).frequency_code;
761 -- both missing
762 ELSE
763 l_subscription_tbl(i).frequency_code := p_x_subscription_tbl(i).frequency_code;
764 END IF;
765
766 --For Status Code
767 IF p_x_subscription_tbl(i).status_desc IS NOT NULL AND
768 p_x_subscription_tbl(i).status_desc <> FND_API.G_MISS_CHAR
769 THEN
770
771 Check_lookup_name_Or_Id (
772 p_lookup_type => 'AHL_SUBSCRIBE_STATUS_TYPE',
773 p_lookup_code => null,
774 p_meaning => p_x_subscription_tbl(i).status_desc,
775 p_check_id_flag => 'Y',
776 x_lookup_code => l_subscription_tbl(i).status_code,
777 x_return_status => l_return_status);
778
779 IF nvl(l_return_status, 'X') <> 'S'
780 THEN
781 FND_MESSAGE.SET_NAME('AHL','AHL_DI_STATUS_CODE_NOT_EXISTS');
782 FND_MSG_PUB.ADD;
783 END IF;
784 END IF;
785 -- If Status Code is Avialable
786 IF p_x_subscription_tbl(i).status_code IS NOT NULL AND
787 p_x_subscription_tbl(i).status_code <> FND_API.G_MISS_CHAR
788 THEN
789 l_subscription_tbl(i).status_code := p_x_subscription_tbl(i).status_code;
790 -- If both are missing
791 ELSE
792 FND_MESSAGE.SET_NAME('AHL','AHL_DI_STATUS_CODE_NULL');
793 FND_MSG_PUB.ADD;
794 END IF;
795
796 l_subscription_tbl(i).document_id := p_x_subscription_tbl(i).document_id;
797 l_subscription_tbl(i).quantity := p_x_subscription_tbl(i).quantity;
798 l_subscription_tbl(i).start_date := p_x_subscription_tbl(i).start_date;
799 l_subscription_tbl(i).end_date := p_x_subscription_tbl(i).end_date;
800 l_subscription_tbl(i).purchase_order_no := p_x_subscription_tbl(i).purchase_order_no;
801 l_subscription_tbl(i).attribute_category := p_x_subscription_tbl(i).attribute_category;
802 l_subscription_tbl(i).attribute1 := p_x_subscription_tbl(i).attribute1;
803 l_subscription_tbl(i).attribute2 := p_x_subscription_tbl(i).attribute2;
804 l_subscription_tbl(i).attribute3 := p_x_subscription_tbl(i).attribute3;
805 l_subscription_tbl(i).attribute4 := p_x_subscription_tbl(i).attribute4;
806 l_subscription_tbl(i).attribute5 := p_x_subscription_tbl(i).attribute5;
807 l_subscription_tbl(i).attribute6 := p_x_subscription_tbl(i).attribute6;
808 l_subscription_tbl(i).attribute7 := p_x_subscription_tbl(i).attribute7;
809 l_subscription_tbl(i).attribute8 := p_x_subscription_tbl(i).attribute8;
810 l_subscription_tbl(i).attribute9 := p_x_subscription_tbl(i).attribute9;
811 l_subscription_tbl(i).attribute10 := p_x_subscription_tbl(i).attribute10;
812 l_subscription_tbl(i).attribute11 := p_x_subscription_tbl(i).attribute11;
813 l_subscription_tbl(i).attribute12 := p_x_subscription_tbl(i).attribute12;
814 l_subscription_tbl(i).attribute13 := p_x_subscription_tbl(i).attribute13;
815 l_subscription_tbl(i).attribute14 := p_x_subscription_tbl(i).attribute14;
816 l_subscription_tbl(i).attribute15 := p_x_subscription_tbl(i).attribute15;
817 l_subscription_tbl(i).delete_flag := p_x_subscription_tbl(i).delete_flag;
818 l_subscription_tbl(i).object_version_number := p_x_subscription_tbl(i).object_version_number;
819 l_subscription_tbl(i).source_lang := p_x_subscription_tbl(i).source_lang;
820 l_subscription_tbl(i).language := p_x_subscription_tbl(i).language;
821 l_subscription_tbl(i).comments := p_x_subscription_tbl(i).comments;
822 --Standard check to count messages
823 l_msg_count := FND_MSG_PUB.count_msg;
824 --
825 IF l_msg_count > 0 THEN
826 X_msg_count := l_msg_count;
827 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
828 THEN
829 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
830 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
831 ELSE
832 X_return_status := FND_API.G_RET_STS_ERROR;
833 RAISE FND_API.G_EXC_ERROR;
834 END IF;
835 END IF;
836 END LOOP;
837 END IF; */
838 -- For creating revisions
839 /* IF p_x_doc_rev_tbl.COUNT > 0
840 THEN
841 FOR i IN p_x_doc_rev_tbl.FIRST..p_x_doc_rev_tbl.LAST
842 LOOP
843 -- Module type is 'JSP' then make it null for the following fields
844 IF (p_module_type = 'JSP') THEN
845 p_x_doc_rev_tbl(i).approved_by_party_id := null;
846 END IF;
847
848 --For Approved by Party Id, Party Name is present
849 IF (p_x_doc_rev_tbl(i).approved_by_pty_name IS NOT NULL) AND
850 (p_x_doc_rev_tbl(i).approved_by_pty_name <> FND_API.G_MISS_CHAR)
851 THEN
852 OPEN for_party_name(p_x_doc_rev_tbl(i).approved_by_pty_name);
853 FETCH for_party_name INTO l_approved_by_party_id;
854 IF for_party_name%FOUND
855 THEN
856 l_revision_tbl(i).approved_by_party_id := l_approved_by_party_id;
857 ELSE
858 FND_MESSAGE.SET_NAME('AHL','AHL_DI_APP_BY_PTY_ID_NOT_EXIST');
859 FND_MSG_PUB.ADD;
860 END IF;
861 CLOSE for_party_name;
862 --If Party Id is present
863 ELSIF (p_x_doc_rev_tbl(i).approved_by_party_id IS NOT NULL) AND
864 (p_x_doc_rev_tbl(i).approved_by_party_id <> FND_API.G_MISS_NUM)
865 THEN
866 OPEN for_party_id(p_x_doc_rev_tbl(i).approved_by_party_id);
867 FETCH for_party_id INTO l_approved_by_party_id;
868 IF for_party_id%FOUND
869 THEN
870 l_revision_tbl(i).approved_by_party_id := l_approved_by_party_id;
871 ELSE
872 FND_MESSAGE.SET_NAME('AHL','AHL_DI_APP_BY_PTY_ID_NOT_EXIST');
873 FND_MSG_PUB.ADD;
874 END IF;
875 CLOSE for_party_id;
876 ELSE
877 --Both Party Id and Name are missing
878
879 l_revision_tbl(i).approved_by_party_id := p_x_doc_rev_tbl(i).approved_by_party_id;
880 END IF;
881 --For Revision Type Code
882 IF p_x_doc_rev_tbl(i).revision_type_desc IS NOT NULL AND
883 p_x_doc_rev_tbl(i).revision_type_desc <> FND_API.G_MISS_CHAR
884 THEN
885
886 Check_lookup_name_Or_Id (
887 p_lookup_type => 'AHL_REVISION_TYPE',
888 p_lookup_code => null,
889 p_meaning => p_x_doc_rev_tbl(i).revision_type_desc,
890 p_check_id_flag => 'Y',
891 x_lookup_code => l_revision_tbl(i).revision_type_code,
892 x_return_status => l_return_status);
893
894 IF nvl(l_return_status, 'X') <> 'S'
895 THEN
896 FND_MESSAGE.SET_NAME('AHL','AHL_DI_REV_TYPE_CODE_NOT_EXIST');
897 FND_MSG_PUB.ADD;
898 END IF;
899 END IF;
900 -- If Code is present
901 IF (p_x_doc_rev_tbl(i).revision_type_code IS NOT NULL AND
902 p_x_doc_rev_tbl(i).revision_type_code <> FND_API.G_MISS_CHAR)
903 THEN
904 l_revision_tbl(i).revision_type_code := p_x_doc_rev_tbl(i).revision_type_code;
905 --If both are missing
906 ELSE
907 l_revision_tbl(i).revision_type_code := p_x_doc_rev_tbl(i).revision_type_code;
908 END IF;
909 --For Media Type Code, meaning is present
910 IF p_x_doc_rev_tbl(i).media_type_desc IS NOT NULL AND
911 p_x_doc_rev_tbl(i).media_type_desc <> FND_API.G_MISS_CHAR
912 THEN
913
914 Check_lookup_name_Or_Id (
915 p_lookup_type => 'AHL_MEDIA_TYPE',
916 p_lookup_code => null,
917 p_meaning => p_x_doc_rev_tbl(i).media_type_desc,
918 p_check_id_flag => 'Y',
919 x_lookup_code => l_revision_tbl(i).media_type_code,
920 x_return_status => l_return_status);
921
922 IF nvl(l_return_status, 'X') <> 'S'
923 THEN
924 FND_MESSAGE.SET_NAME('AHL','AHL_DI_MEDTYP_CODE_NOT_EXISTS');
925 FND_MSG_PUB.ADD;
926 END IF;
927 END IF;
928 -- If ID presnt
929 IF p_x_doc_rev_tbl(i).media_type_code IS NOT NULL AND
930 p_x_doc_rev_tbl(i).media_type_code <> FND_API.G_MISS_CHAR
931 THEN
932 l_revision_tbl(i).media_type_code := p_x_doc_rev_tbl(i).media_type_code;
933 --Both are missing
934 ELSE
935 l_revision_tbl(i).media_type_code := p_x_doc_rev_tbl(i).media_type_code;
936 END IF;
937 --For Revision Status Code
938 IF p_x_doc_rev_tbl(i).revision_status_desc IS NOT NULL AND
939 p_x_doc_rev_tbl(i).revision_status_desc <> FND_API.G_MISS_CHAR
940 THEN
941
942 Check_lookup_name_Or_Id (
943 p_lookup_type => 'AHL_REVISION_STATUS_TYPE',
944 p_lookup_code => null,
945 p_meaning => p_x_doc_rev_tbl(i).revision_status_desc,
946 p_check_id_flag => 'Y',
947 x_lookup_code => l_revision_tbl(i).revision_status_code,
948 x_return_status => l_return_status);
949
950 IF nvl(l_return_status, 'X') <> 'S'
951 THEN
952 FND_MESSAGE.SET_NAME('AHL','AHL_DI_REV_STAT_CODE_NOT_EXIST');
953 FND_MSG_PUB.ADD;
954 END IF;
955 END IF;
956 -- If Revision status code presents
957 IF p_x_doc_rev_tbl(i).revision_status_code IS NOT NULL AND
958 p_x_doc_rev_tbl(i).revision_status_code <> FND_API.G_MISS_CHAR
959 THEN
960 l_revision_tbl(i).revision_status_code := p_x_doc_rev_tbl(i).revision_status_code;
961 ELSE
962 --Both are missing
963 l_revision_tbl(i).revision_status_code := p_x_doc_rev_tbl(i).revision_status_code;
964 END IF;
965 --
966 l_revision_tbl(i).doc_revision_id := p_x_doc_rev_tbl(i).doc_revision_id;
967 l_revision_tbl(i).document_id := p_x_doc_rev_tbl(i).document_id;
968 l_revision_tbl(i).revision_no := p_x_doc_rev_tbl(i).revision_no;
969 l_revision_tbl(i).revision_date := p_x_doc_rev_tbl(i).revision_date;
970 l_revision_tbl(i).approved_date := p_x_doc_rev_tbl(i).approved_date;
971 l_revision_tbl(i).effective_date := p_x_doc_rev_tbl(i).effective_date;
972 l_revision_tbl(i).obsolete_date := p_x_doc_rev_tbl(i).obsolete_date;
973 l_revision_tbl(i).issue_date := p_x_doc_rev_tbl(i).issue_date;
974 l_revision_tbl(i).received_date := p_x_doc_rev_tbl(i).received_date;
975 l_revision_tbl(i).url := p_x_doc_rev_tbl(i).url;
976 l_revision_tbl(i).volume := p_x_doc_rev_tbl(i).volume;
977 l_revision_tbl(i).issue := p_x_doc_rev_tbl(i).issue;
978 l_revision_tbl(i).issue_number := p_x_doc_rev_tbl(i).issue_number;
979 l_revision_tbl(i).language := p_x_doc_rev_tbl(i).language;
980 l_revision_tbl(i).source_lang := p_x_doc_rev_tbl(i).source_lang;
981 l_revision_tbl(i).comments := p_x_doc_rev_tbl(i).comments;
982 l_revision_tbl(i).attribute_category := p_x_doc_rev_tbl(i).attribute_category;
983 l_revision_tbl(i).attribute1 := p_x_doc_rev_tbl(i).attribute1;
984 l_revision_tbl(i).attribute2 := p_x_doc_rev_tbl(i).attribute2;
985 l_revision_tbl(i).attribute3 := p_x_doc_rev_tbl(i).attribute3;
986 l_revision_tbl(i).attribute4 := p_x_doc_rev_tbl(i).attribute4;
987 l_revision_tbl(i).attribute5 := p_x_doc_rev_tbl(i).attribute5;
988 l_revision_tbl(i).attribute6 := p_x_doc_rev_tbl(i).attribute6;
989 l_revision_tbl(i).attribute7 := p_x_doc_rev_tbl(i).attribute7;
990 l_revision_tbl(i).attribute8 := p_x_doc_rev_tbl(i).attribute8;
991 l_revision_tbl(i).attribute9 := p_x_doc_rev_tbl(i).attribute9;
992 l_revision_tbl(i).attribute10 := p_x_doc_rev_tbl(i).attribute10;
993 l_revision_tbl(i).attribute11 := p_x_doc_rev_tbl(i).attribute11;
994 l_revision_tbl(i).attribute12 := p_x_doc_rev_tbl(i).attribute12;
995 l_revision_tbl(i).attribute13 := p_x_doc_rev_tbl(i).attribute13;
996 l_revision_tbl(i).attribute14 := p_x_doc_rev_tbl(i).attribute14;
997 l_revision_tbl(i).attribute15 := p_x_doc_rev_tbl(i).attribute15;
998 l_revision_tbl(i).delete_flag := p_x_doc_rev_tbl(i).delete_flag;
999 l_revision_tbl(i).object_version_number := p_x_doc_rev_tbl(i).object_version_number;
1000
1001 l_msg_count := FND_MSG_PUB.count_msg;
1002
1003 IF l_msg_count > 0 THEN
1004 X_msg_count := l_msg_count;
1005 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1006 THEN
1007 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1008 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1009 ELSE
1010 X_return_status := FND_API.G_RET_STS_ERROR;
1011 RAISE FND_API.G_EXC_ERROR;
1012 END IF;
1013 END IF;
1014 END LOOP;
1015 END IF; */
1016
1017
1018 /*-----------------------------------------------------------*/
1019 /* procedure name: AHL_DI_DOC_INDEX_CHUK.CREATE_DOCUMENT_PRE */
1020 /* AHL_DI_DOC_INDEX_VHUK.CREATE_DOCUMENT_PRE */
1021 /* */
1022 /* description : Added by Siddhartha to call User Hooks */
1023 /* Date : Dec 28 2001 */
1024 /*-----------------------------------------------------------*/
1025
1026
1027 IF JTF_USR_HKS.Ok_to_Execute( 'AHL_DI_DOC_INDEX_PUB','CREATE_DOCUMENT',
1028 'B', 'C' ) then
1029 AHL_DEBUG_PUB.debug( 'Start AHL_DI_DOC_INDEX_CUHK.CREATE_DOCUMENT_PRE');
1030
1031 AHL_DI_DOC_INDEX_CUHK.CREATE_DOCUMENT_PRE
1032 (
1033
1034 p_x_document_tbl => l_document_tbl ,
1035 p_x_supplier_tbl => l_supplier_tbl,
1036 p_x_recipient_tbl => l_recipient_tbl,
1037 x_return_status => l_return_status,
1038 x_msg_count => l_msg_count ,
1039 x_msg_data => l_msg_data
1040 );
1041
1042
1043 AHL_DEBUG_PUB.debug( 'End AHL_DI_DOC_INDEX_CUHK.CREATE_DOCUMENT_PRE');
1044
1045 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1046 RAISE FND_API.G_EXC_ERROR;
1047 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1048 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1049 END IF;
1050 END IF;
1051
1052 IF JTF_USR_HKS.Ok_to_Execute( 'AHL_DI_DOC_INDEX_PUB','CREATE_DOCUMENT','B', 'V' )
1053 then
1054
1055 AHL_DEBUG_PUB.debug( 'Start AHL_DI_DOC_INDEX_VUHK.CREATE_DOCUMENT_PRE');
1056
1057 AHL_DI_DOC_INDEX_VUHK.CREATE_DOCUMENT_PRE
1058 (
1059 p_x_document_tbl => l_document_tbl ,
1060 p_x_supplier_tbl => l_supplier_tbl,
1061 p_x_recipient_tbl => l_recipient_tbl,
1062 X_RETURN_STATUS => l_return_status ,
1063 X_MSG_COUNT => l_msg_count ,
1064 X_MSG_DATA => l_msg_data );
1065
1066 /*
1067 --Standard check to count messages
1068 l_msg_count := FND_MSG_PUB.count_msg;
1069 */
1070 AHL_DEBUG_PUB.debug( 'End AHL_DI_DOC_INDEX_VUHK.CREATE_DOCUMENT_PRE');
1071
1072 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1073 RAISE FND_API.G_EXC_ERROR;
1074 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1075 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1076 END IF;
1077
1078
1079
1080 END IF;
1081
1082
1083 /*---------------------------------------------------------*/
1084 /* End ; Date : Dec 28 2001 */
1085 /*---------------------------------------------------------*/
1086
1087 -- Call the Private API
1088 --{{adharia
1089 l_msg_count := FND_MSG_PUB.count_msg;
1090
1091 IF l_msg_count > 0 THEN
1092 X_msg_count := l_msg_count;
1093 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1094 THEN
1095 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1096 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1097 ELSE
1098 X_return_status := FND_API.G_RET_STS_ERROR;
1099 RAISE FND_API.G_EXC_ERROR;
1100 END IF;
1101 END IF;
1102 --}}adharia
1103
1104 AHL_DI_DOC_INDEX_PVT.CREATE_DOCUMENT(
1105 p_api_version => 1.0,
1106 p_init_msg_list => p_init_msg_list,
1107 p_commit => p_commit,
1108 p_validate_only => p_validate_only,
1109 p_validation_level => p_validation_level,
1110 p_x_document_tbl => l_document_tbl,
1111 p_x_supplier_tbl => l_supplier_tbl,
1112 p_x_recipient_tbl => l_recipient_tbl,
1113 x_return_status => l_return_status,
1114 x_msg_count => l_msg_count,
1115 x_msg_data => l_msg_data);
1116
1117 --Standard check to count messages
1118 l_msg_count := FND_MSG_PUB.count_msg;
1119
1120
1121 IF l_msg_count > 0 THEN
1122 X_msg_count := l_msg_count;
1123 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1124 THEN
1125 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1126 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1127 ELSE
1128 X_return_status := FND_API.G_RET_STS_ERROR;
1129 RAISE FND_API.G_EXC_ERROR;
1130 END IF;
1131 END IF;
1132
1133
1134 /*----------------------------------------------------------------------------- */
1135 /* procedure name: AHL_DI_DOC_INDEX_VHUK.CREATE_DOCUMENT_POST */
1136 /* AHL_DI_DOC_INDEX_CHUK.CREATE_DOCUMENT_POST */
1137 /* */
1138 /* description : Added by ssaklani to call User Hooks */
1139 /* Date : Dec 28 2001 */
1140 /*------------------------------------------------------------------------------*/
1141
1142 IF JTF_USR_HKS.Ok_to_Execute( 'AHL_DI_DOC_INDEX_PUB','CREATE_DOCUMENT',
1143 'A', 'V' ) then
1144
1145 AHL_DEBUG_PUB.debug( 'Start AHL_DI_DOC_INDEX_VUHK.CREATE_DOCUMENT_POST ');
1146
1147
1148 AHL_DI_DOC_INDEX_VUHK.CREATE_DOCUMENT_POST
1149 (
1150 p_document_tbl => l_document_tbl ,
1151 p_supplier_tbl => l_supplier_tbl,
1152 p_recipient_tbl => l_recipient_tbl,
1153 X_RETURN_STATUS => l_return_status ,
1154 X_MSG_COUNT => l_msg_count ,
1155 X_MSG_DATA => l_msg_data );
1156
1157 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1158 RAISE FND_API.G_EXC_ERROR;
1159 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1160 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1161 END IF;
1162
1163 AHL_DEBUG_PUB.debug( 'End AHL_DI_DOC_INDEX_VUHK.CREATE_DOCUMENT_POST ');
1164
1165 END IF;
1166
1167 IF JTF_USR_HKS.Ok_to_Execute( 'AHL_DI_DOC_INDEX_PUB','CREATE_DOCUMENT',
1168 'A', 'C' ) then
1169
1170 AHL_DEBUG_PUB.debug( 'Start AHL_DI_DOC_INDEX_CUHK.CREATE_DOCUMENT_POST');
1171
1172
1173 AHL_DI_DOC_INDEX_CUHK.CREATE_DOCUMENT_POST(
1174
1175 p_document_tbl => l_document_tbl ,
1176 p_supplier_tbl => l_supplier_tbl,
1177 p_recipient_tbl => l_recipient_tbl,
1178 X_RETURN_STATUS => l_return_status ,
1179 X_MSG_COUNT => l_msg_count ,
1180 X_MSG_DATA => l_msg_data );
1181
1182
1183 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1184 RAISE FND_API.G_EXC_ERROR;
1185 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1186 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1187 END IF;
1188
1189 AHL_DEBUG_PUB.debug( 'End AHL_DI_DOC_INDEX_CUHK.CREATE_DOCUMENT_POST');
1190
1191
1192 END IF;
1193
1194
1195 /*---------------------------------------------------------*/
1196 /* End ; Date : Dec 28 2001 */
1197 /*---------------------------------------------------------*/
1198
1199
1200 IF l_document_tbl.COUNT > 0
1201 THEN
1202 FOR i IN l_document_tbl.FIRST..l_document_tbl.LAST
1203 LOOP
1204 p_x_document_tbl(i).document_id := l_document_tbl(i).document_id;
1205 END LOOP;
1206 END IF;
1207 --Assign Suppliers
1208 IF l_supplier_tbl.COUNT > 0
1209 THEN
1210 FOR i IN l_supplier_tbl.FIRST..l_supplier_tbl.LAST
1211 LOOP
1212 p_x_supplier_tbl(i).supplier_document_id := l_supplier_tbl(i).supplier_document_id;
1213 END LOOP;
1214 END IF;
1215 -- Assign Recipients
1216 IF l_recipient_tbl.COUNT > 0
1217 THEN
1218 FOR i IN l_recipient_tbl.FIRST..l_recipient_tbl.LAST
1219 LOOP
1220 p_x_recipient_tbl(i).recipient_document_id := l_recipient_tbl(i).recipient_document_id;
1221 END LOOP;
1222 END IF;
1223 --Standard check for commit
1224 IF FND_API.TO_BOOLEAN(p_commit) THEN
1225 COMMIT;
1226 END IF;
1227 -- Debug info
1228 AHL_DEBUG_PUB.debug( 'End of public api Create Document','+DI+');
1229 -- Check if API is called in debug mode. If yes, disable debug.
1230 AHL_DEBUG_PUB.disable_debug;
1231
1232 EXCEPTION
1233 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1234 ROLLBACK TO create_document;
1235 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1236 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1237 p_count => x_msg_count,
1238 p_data => x_msg_data);
1239 IF AHL_DEBUG_PUB.G_FILE_DEBUG THEN
1240 AHL_DEBUG_PUB.log_app_messages (
1241 x_msg_count, x_msg_data, 'ERROR' );
1242 AHL_DEBUG_PUB.debug( 'ahl_di_doc_index_pub.Create Document','+DI+');
1243 END IF;
1244 -- Check if API is called in debug mode. If yes, disable debug.
1245 AHL_DEBUG_PUB.disable_debug;
1246
1247 WHEN FND_API.G_EXC_ERROR THEN
1248 ROLLBACK TO create_document;
1249 X_return_status := FND_API.G_RET_STS_ERROR;
1250 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1251 p_count => x_msg_count,
1252 p_data => X_msg_data);
1253 -- Debug info.
1254 IF AHL_DEBUG_PUB.G_FILE_DEBUG THEN
1255 AHL_DEBUG_PUB.log_app_messages (
1256 x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
1257 AHL_DEBUG_PUB.debug( 'ahl_di_doc_index_pub.Create Document','+DI+');
1258 END IF;
1259 -- Check if API is called in debug mode. If yes, disable debug.
1260 AHL_DEBUG_PUB.disable_debug;
1261
1262 WHEN OTHERS THEN
1263 ROLLBACK TO create_document;
1264 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1265 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1266 THEN
1267 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_DI_DOC_INDEX_PUB',
1268 p_procedure_name => 'CREATE_DOCUMENT',
1269 p_error_text => SUBSTR(SQLERRM,1,240));
1270 END IF;
1271 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1272 p_count => x_msg_count,
1273 p_data => X_msg_data);
1274
1275 -- Debug info.
1276 IF AHL_DEBUG_PUB.G_FILE_DEBUG THEN
1277 AHL_DEBUG_PUB.log_app_messages (
1278 x_msg_count, x_msg_data, 'SQL ERROR' );
1279 AHL_DEBUG_PUB.debug( 'ahl_di_doc_index_pub.Create Document','+DI+');
1280 END IF;
1281 -- Check if API is called in debug mode. If yes, disable debug.
1282 AHL_DEBUG_PUB.disable_debug;
1283
1284 END CREATE_DOCUMENT;
1285 /*------------------------------------------------------*/
1286 /* procedure name: modify_document */
1287 /* description : Updates existing document record and its */
1288 /* associated suppliers, recipients, */
1289 /* subscriptions,revision and copies */
1290 /* */
1291 /*------------------------------------------------------*/
1292
1293 PROCEDURE MODIFY_DOCUMENT
1294 (
1295 p_api_version IN NUMBER := '1.0' ,
1296 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
1297 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
1298 p_validate_only IN VARCHAR2 := FND_API.G_TRUE ,
1299 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1300 p_x_document_tbl IN OUT NOCOPY document_tbl ,
1301 -- p_x_doc_rev_tbl IN OUT NOCOPY AHL_DI_DOC_REVISION_PUB.Revision_Tbl ,
1302 -- p_x_doc_rev_copy_tbl IN OUT NOCOPY AHL_DI_DOC_REVISION_PUB.Revision_Copy_Tbl,
1303 -- p_x_subscription_tbl IN OUT NOCOPY AHL_DI_SUBSCRIPTION_PUB.Subscription_Tbl,
1304 p_x_supplier_tbl IN OUT NOCOPY Supplier_Tbl ,
1305 p_x_recipient_tbl IN OUT NOCOPY Recipient_Tbl ,
1306 p_module_type IN VARCHAR2 ,
1307 x_return_status OUT NOCOPY VARCHAR2 ,
1308 x_msg_count OUT NOCOPY NUMBER ,
1309 x_msg_data OUT NOCOPY VARCHAR2)
1310 IS
1311 -- Cursor is used to retrieve party id
1312 CURSOR for_party_name(c_party_number IN VARCHAR2)
1313 IS
1314 SELECT party_id
1315 FROM hz_parties
1316 WHERE party_number = c_party_number;
1317 --
1318 CURSOR for_party_id(c_party_id IN NUMBER)
1319 IS
1320 SELECT party_id
1321 FROM hz_parties
1322 WHERE party_id = c_party_id;
1323 -- Used to retrieve vendor id from po vendors
1324 CURSOR for_vendor_id(c_segment1 IN VARCHAR2)
1325 IS
1326 SELECT vendor_id
1327 FROM po_vendors
1328 WHERE segment1 = c_segment1;
1329 --Added pjha for Restricting Subscription Avail to 'Yes' If supplier or subscription
1330 --Exists for the doc: Begin
1331 -- Cursor used to find if a supplier exist for the document
1332 CURSOR check_sup_exists(c_document_id NUMBER)
1333 IS
1334 SELECT 'X'
1335 FROM ahl_supplier_documents
1336 WHERE document_id = c_document_id;
1337 -- Cursor to find if there are active subscription for the document
1338 -- adharia -- added nvl to end_date;--11 July 2002
1339 CURSOR check_sub_exists(c_document_id NUMBER)
1340 IS
1341 SELECT 'X'
1342 FROM ahl_subscriptions_b
1343 WHERE document_id = c_document_id
1344 AND NVL(end_date, sysdate) >= TRUNC(sysdate);
1345 --Added pjha for Restricting Subscription Avail to 'Yes' If supplier or subscription
1346 --Exists for the doc: End
1347
1348 --Enhancement #2275357: pbarman 1st April 2003
1349 --Cursor to retrieve operator code from hz parties
1350 CURSOR get_operator_name_hz(c_operator_name VARCHAR2)
1351 IS
1352 SELECT party_id
1353 FROM HZ_PARTIES
1354 WHERE upper(party_name) = upper(c_operator_name)
1355 AND ( party_type ='ORGANIZATION' or party_type = 'PERSON' );
1356 -- For Bug Fix #3446159
1357 CURSOR get_operator_name_hz_id(c_operator_name VARCHAR2, c_operator_id NUMBER)
1358 IS
1359 SELECT party_id
1360 FROM HZ_PARTIES
1361 WHERE upper(party_name) = upper(c_operator_name)
1362 AND party_id = c_operator_id
1363 AND ( party_type ='ORGANIZATION' or party_type = 'PERSON' );
1364
1365 --pbarman : 28th July 2003
1366 CURSOR for_subcr_validate(c_supplier_id NUMBER, c_doc_id NUMBER)
1367 IS
1368 SELECT subscription_id
1369 FROM AHL_SUBSCRIPTIONS_B
1370 WHERE SUBSCRIBED_FRM_PARTY_ID = c_supplier_id
1371 AND DOCUMENT_ID = c_doc_id;
1372
1373 -- Bug : Perf Fixes for 4918997
1374 /*
1375 CURSOR for_supplier_name(c_supplier_no NUMBER )
1376 IS
1377 SELECT DISTINCT party_name
1378 FROM AHL_HZ_PO_SUPPLIERS_V
1379 WHERE PARTY_NUMBER = c_supplier_no;
1380 */
1381
1382 CURSOR for_supplier_name_hz(c_supplier_no NUMBER)
1383 IS
1384 SELECT DISTINCT PARTY_NAME
1385 FROM HZ_PARTIES
1386 WHERE PARTY_NUMBER =c_supplier_no;
1387
1388 CURSOR for_supplier_name_po(c_supplier_no NUMBER)
1389 IS
1390 SELECT DISTINCT VENDOR_NAME
1391 FROM PO_VENDORS
1392 WHERE SEGMENT1 =c_supplier_no;
1393
1394 --
1395
1396 l_api_name CONSTANT VARCHAR2(30) := 'MODIFY_DOCUMENT';
1397 l_api_version CONSTANT NUMBER := 1.0;
1398 l_msg_count NUMBER;
1399 l_num_rec NUMBER;
1400 l_msg_data VARCHAR2(2000);
1401 l_return_status VARCHAR2(1);
1402 l_source_party_id NUMBER;
1403 l_supplier_id NUMBER;
1404 l_recipient_id NUMBER;
1405 l_recipient_party_id NUMBER;
1406 l_requested_by_party_id NUMBER;
1407 l_approved_by_party_id NUMBER;
1408 l_received_by_party_id NUMBER;
1409 l_doc_type_code VARCHAR2(30);
1410 l_doc_sub_type_code VARCHAR2(30);
1411 l_preference_code VARCHAR2(30);
1412 l_status_code VARCHAR2(30);
1413 l_media_type_code VARCHAR2(30);
1414 l_frequency_code VARCHAR2(30);
1415 l_subscription_type_code VARCHAR2(30);
1416 l_revision_type_code VARCHAR2(30);
1417 l_revision_status_code VARCHAR2(30);
1418 l_copy_type_code VARCHAR2(30);
1419 l_operator_code VARCHAR2(30);
1420 l_product_type_code VARCHAR2(30);
1421 l_document_tbl AHL_DI_DOC_INDEX_PVT.document_tbl;
1422 -- l_revision_tbl AHL_DI_DOC_REVISION_PVT.revision_tbl;
1423 -- l_revision_copy_tbl AHL_DI_DOC_REVISION_PVT.revision_copy_tbl;
1424 -- l_subscription_tbl AHL_DI_SUBSCRIPTION_PVT.subscription_tbl;
1425 l_supplier_tbl AHL_DI_DOC_INDEX_PVT.supplier_tbl;
1426 l_recipient_tbl AHL_DI_DOC_INDEX_PVT.recipient_tbl;
1427 l_init_msg_list VARCHAR2(10) := FND_API.G_TRUE;
1428 l_subscription_id NUMBER;
1429 l_supplier_name VARCHAR2(360);
1430 l_prod_install_status VARCHAR2(30);
1431
1432 --
1433
1434 BEGIN
1435 -- Standard Start of API savepoint
1436 SAVEPOINT modify_document;
1437
1438 -- Check if API is called in debug mode. If yes, enable debug.
1439 AHL_DEBUG_PUB.enable_debug;
1440 -- Debug info.
1441 IF AHL_DEBUG_PUB.G_FILE_DEBUG THEN
1442 AHL_DEBUG_PUB.debug( 'enter ahl_di_doc_index_pub.Modify Document','+DI+');
1443 END IF;
1444 -- Standard call to check for call compatibility.
1445 IF FND_API.to_boolean(l_init_msg_list)
1446 THEN
1447 FND_MSG_PUB.initialize;
1448 END IF;
1449 -- Initialize API return status to success
1450 x_return_status := 'S';
1451 -- Initialize message list if p_init_msg_list is set to TRUE.
1452 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
1453 p_api_version,
1454 l_api_name,G_PKG_NAME)
1455 THEN
1456 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1457 END IF;
1458 --Start of API Body
1459
1460 BEGIN
1461 IF AHL_DEBUG_PUB.G_FILE_DEBUG THEN
1462 AHL_DEBUG_PUB.debug( 'Fetching Product Installation Status','+DI+');
1463 END IF;
1464 Select AHL_DI_DOC_INDEX_PVT.GET_PRODUCT_INSTALL_STATUS('PO')
1465 into l_prod_install_status
1466 from dual;
1467 END;
1468
1469 IF p_x_document_tbl.COUNT > 0
1470 THEN
1471 FOR i IN p_x_document_tbl.FIRST..p_x_document_tbl.LAST
1472 LOOP
1473 -- Process flag 'D' means Modifying Document record
1474 IF p_x_document_tbl(i).process_flag = 'D'
1475 THEN
1476 -- Module type is 'JSP' then make it null for the following fields
1477 IF (p_module_type = 'JSP') THEN
1478 p_x_document_tbl(i).source_party_id := null;
1479 p_x_document_tbl(i).product_type_code := null;
1480 --p_x_document_tbl(i).operator_code := null;
1481 END IF;
1482 --For Source by Party Id
1483 IF (p_x_document_tbl(i).source_party_id IS NULL) OR
1484 (p_x_document_tbl(i).source_party_id = FND_API.G_MISS_NUM)
1485 THEN
1486
1487 -- If Party Name is available
1488 IF (p_x_document_tbl(i).source_party_number IS NOT NULL) AND
1489 (p_x_document_tbl(i).source_party_number <> FND_API.G_MISS_CHAR)
1490 THEN
1491 OPEN for_party_name(p_x_document_tbl(i).source_party_number);
1492 FETCH for_party_name INTO l_source_party_id;
1493 IF for_party_name%FOUND
1494 THEN
1495 l_document_tbl(i).source_party_id := l_source_party_id;
1496 ELSE
1497 FND_MESSAGE.SET_NAME('AHL','AHL_DI_SOURCE_ID_NOT_EXISTS');
1498 FND_MSG_PUB.ADD;
1499 END IF;
1500 CLOSE for_party_name;
1501 ELSE
1502 --Both Party Id and Name are missing
1503 FND_MESSAGE.SET_NAME('AHL','AHL_DI_SOURCE_PARTY_ID_NULL');
1504 FND_MSG_PUB.ADD;
1505 END IF;
1506
1507 --Check for If ID present
1508 ELSIF (p_x_document_tbl(i).source_party_id IS NOT NULL AND
1509 p_x_document_tbl(i).source_party_id <> FND_API.G_MISS_NUM)
1510 THEN
1511 OPEN for_party_id(p_x_document_tbl(i).source_party_id);
1512 FETCH for_party_id INTO l_document_tbl(i).source_party_id;
1513 IF for_party_id%FOUND
1514 THEN
1515 l_document_tbl(i).source_party_id := p_x_document_tbl(i).source_party_id;
1516 ELSE
1517 l_document_tbl(i).source_party_id := p_x_document_tbl(i).source_party_id;
1518 END IF;
1519 CLOSE for_party_id;
1520 ELSE
1521 --Both Party Id and Name are missing
1522 FND_MESSAGE.SET_NAME('AHL','AHL_DI_SOURCE_PARTY_ID_NULL');
1523 FND_MSG_PUB.ADD;
1524 END IF;
1525 --For Operator Code, Meaning presnts
1526 IF p_x_document_tbl(i).operator_name IS NOT NULL AND
1527 p_x_document_tbl(i).operator_name <> FND_API.G_MISS_CHAR
1528 THEN
1529 -- Check if operator name and id match
1530 OPEN get_operator_name_hz_id(p_x_document_tbl(i).operator_name, TO_NUMBER(p_x_document_tbl(i).operator_code));
1531
1532 FETCH get_operator_name_hz_id INTO l_document_tbl(i).operator_code;
1533 IF get_operator_name_hz_id%NOTFOUND THEN
1534 -- the operator name has been changed
1535
1536 --Enhancement #2275357: pbarman 1st April 2003
1537 CLOSE get_operator_name_hz_id;
1538 OPEN get_operator_name_hz(p_x_document_tbl(i).operator_name);
1539 LOOP
1540 FETCH get_operator_name_hz INTO l_document_tbl(i).operator_code;
1541 EXIT WHEN get_operator_name_hz%NOTFOUND;
1542 END LOOP;
1543
1544
1545 IF get_operator_name_hz%ROWCOUNT = 0
1546 THEN
1547 FND_MESSAGE.SET_NAME('AHL','AHL_DI_OPERATOR_CODE_NOT_EXIST');
1548 FND_MSG_PUB.ADD;
1549 ELSIF get_operator_name_hz%ROWCOUNT > 1
1550 THEN
1551 FND_MESSAGE.SET_NAME('AHL','AHL_DI_OPERATOR_USE_LOV');
1552 FND_MSG_PUB.ADD;
1553 END IF;
1554 CLOSE get_operator_name_hz;
1555 END IF;
1556
1557 END IF;
1558
1559 --For Product type Code, Meaning presnts
1560 IF p_x_document_tbl(i).product_type_desc IS NOT NULL AND
1561 p_x_document_tbl(i).product_type_desc <> FND_API.G_MISS_CHAR
1562 THEN
1563 Check_lookup_name_Or_Id (
1564 --Enhancement #2525604 pbarman : April 2003
1565 p_lookup_type => 'ITEM_TYPE',
1566 p_lookup_code => null,
1567 p_meaning => p_x_document_tbl(i).product_type_desc,
1568 p_check_id_flag => 'Y',
1569 x_lookup_code => l_document_tbl(i).product_type_code,
1570 x_return_status => l_return_status);
1571
1572 IF nvl(l_return_status, 'X') <> 'S'
1573 THEN
1574 FND_MESSAGE.SET_NAME('AHL','AHL_DI_PRODTYPE_CODE_NOT_EXIST');
1575 FND_MSG_PUB.ADD;
1576 END IF;
1577
1578 --ID presntes
1579 ELSIF p_x_document_tbl(i).product_type_code IS NOT NULL AND
1580 p_x_document_tbl(i).product_type_code <> FND_API.G_MISS_CHAR
1581 THEN
1582 l_document_tbl(i).product_type_code := p_x_document_tbl(i).product_type_code;
1583 --Both missing
1584 ELSE
1585 l_document_tbl(i).product_type_code := p_x_document_tbl(i).product_type_code;
1586 END IF;
1587
1588 --For Doc Type Code
1589 IF p_x_document_tbl(i).doc_type_desc IS NOT NULL AND
1590 p_x_document_tbl(i).doc_type_desc <> FND_API.G_MISS_CHAR
1591 THEN
1592 Check_lookup_name_Or_Id (
1593 p_lookup_type => 'AHL_DOC_TYPE',
1594 p_lookup_code => null,
1595 p_meaning => p_x_document_tbl(i).doc_type_desc,
1596 p_check_id_flag => 'Y',
1597 x_lookup_code => l_document_tbl(i).doc_type_code,
1598 x_return_status => l_return_status);
1599
1600 IF nvl(l_return_status, 'X') <> 'S'
1601 THEN
1602 FND_MESSAGE.SET_NAME('AHL','AHL_DI_DOC_TYPE_CODE_NOT_EXIST');
1603 FND_MSG_PUB.ADD;
1604 END IF;
1605 END IF;
1606 -- Id presents
1607 IF p_x_document_tbl(i).doc_type_code IS NOT NULL AND
1608 p_x_document_tbl(i).doc_type_code <> FND_API.G_MISS_CHAR
1609 THEN
1610 l_document_tbl(i).doc_type_code := p_x_document_tbl(i).doc_type_code;
1611 ELSE
1612 FND_MESSAGE.SET_NAME('AHL','AHL_DI_DOC_TYPE_CODE_NOT_EXIST');
1613 FND_MSG_PUB.ADD;
1614 END IF;
1615
1616 --For Doc Sub Type Code, meaning presents
1617 IF p_x_document_tbl(i).doc_sub_type_desc IS NOT NULL AND
1618 p_x_document_tbl(i).doc_sub_type_desc <> FND_API.G_MISS_CHAR
1619 THEN
1620
1621 Check_lookup_name_Or_Id (
1622 p_lookup_type => 'AHL_DOC_SUB_TYPE_CODE',
1623 p_lookup_code => null,
1624 p_meaning => p_x_document_tbl(i).doc_sub_type_desc,
1625 p_check_id_flag => 'Y',
1626 x_lookup_code => l_document_tbl(i).doc_sub_type_code,
1627 x_return_status => l_return_status);
1628
1629 IF nvl(l_return_status, 'X') <> 'S'
1630 THEN
1631 FND_MESSAGE.SET_NAME('AHL','AHL_DI_DOC_SUBT_COD_NOT_EXISTS');
1632 FND_MSG_PUB.ADD;
1633 END IF;
1634 END IF;
1635 --Id presents
1636 IF p_x_document_tbl(i).doc_sub_type_code IS NOT NULL AND
1637 p_x_document_tbl(i).doc_sub_type_code <> FND_API.G_MISS_CHAR
1638 THEN
1639 l_document_tbl(i).doc_sub_type_code := p_x_document_tbl(i).doc_sub_type_code;
1640 ELSE
1641 l_document_tbl(i).doc_sub_type_code := p_x_document_tbl(i).doc_sub_type_code;
1642 END IF;
1643 --Added pjha: 08-Jul-2002 for Restricting Subscription Avail to 'Yes' If supplier or subscription
1644 --Exists for the doc: Begin
1645 IF (p_x_document_tbl(i).subscribe_avail_flag = 'N')
1646 THEN
1647 OPEN check_sub_exists(p_x_document_tbl(i).document_id);
1648 FETCH check_sub_exists INTO l_operator_code;
1649 IF check_sub_exists%FOUND THEN
1650 FND_MESSAGE.SET_NAME('AHL','AHL_DI_SUBSC_AVAIL_FLAG_NO');
1651 FND_MSG_PUB.ADD;
1652 ELSE
1653 p_x_document_tbl(i).subscribe_to_flag := 'N';
1654 END IF;
1655 CLOSE check_sub_exists;
1656
1657 OPEN check_sup_exists(p_x_document_tbl(i).document_id);
1658 FETCH check_sup_exists INTO l_operator_code;
1659 IF check_sup_exists%FOUND
1660 THEN
1661 FND_MESSAGE.SET_NAME('AHL','AHL_DI_SUPPLIER_EXISTS');
1662 FND_MSG_PUB.ADD;
1663 END IF;
1664 CLOSE check_sup_exists;
1665 END IF;
1666 --Added pjha: 08-Jul-2002 for Restricting Subscription Avail to 'Yes' If supplier or subscription
1667 --Exists for the doc: End
1668 --
1669 l_document_tbl(i).document_id := p_x_document_tbl(i).document_id;
1670 l_document_tbl(i).document_no := p_x_document_tbl(i).document_no;
1671 l_document_tbl(i).subscribe_avail_flag := p_x_document_tbl(i).subscribe_avail_flag;
1672 l_document_tbl(i).subscribe_to_flag := p_x_document_tbl(i).subscribe_to_flag;
1673 l_document_tbl(i).document_title := p_x_document_tbl(i).document_title;
1674 l_document_tbl(i).language := p_x_document_tbl(i).language;
1675 l_document_tbl(i).source_lang := p_x_document_tbl(i).source_lang;
1676 l_document_tbl(i).attribute_category := p_x_document_tbl(i).attribute_category;
1677 l_document_tbl(i).attribute1 := p_x_document_tbl(i).attribute1;
1678 l_document_tbl(i).attribute2 := p_x_document_tbl(i).attribute2;
1679 l_document_tbl(i).attribute3 := p_x_document_tbl(i).attribute3;
1680 l_document_tbl(i).attribute4 := p_x_document_tbl(i).attribute4;
1681 l_document_tbl(i).attribute5 := p_x_document_tbl(i).attribute5;
1682 l_document_tbl(i).attribute6 := p_x_document_tbl(i).attribute6;
1683 l_document_tbl(i).attribute7 := p_x_document_tbl(i).attribute7;
1684 l_document_tbl(i).attribute8 := p_x_document_tbl(i).attribute8;
1685 l_document_tbl(i).attribute9 := p_x_document_tbl(i).attribute9;
1686 l_document_tbl(i).attribute10 := p_x_document_tbl(i).attribute10;
1687 l_document_tbl(i).attribute11 := p_x_document_tbl(i).attribute11;
1688 l_document_tbl(i).attribute12 := p_x_document_tbl(i).attribute12;
1689 l_document_tbl(i).attribute13 := p_x_document_tbl(i).attribute13;
1690 l_document_tbl(i).attribute14 := p_x_document_tbl(i).attribute14;
1691 l_document_tbl(i).attribute15 := p_x_document_tbl(i).attribute15;
1692 l_document_tbl(i).delete_flag := p_x_document_tbl(i).delete_flag;
1693 l_document_tbl(i).object_version_number := p_x_document_tbl(i).object_version_number;
1694
1695 --Standard check for count messages
1696 l_msg_count := FND_MSG_PUB.count_msg;
1697 IF l_msg_count > 0 THEN
1698 X_msg_count := l_msg_count;
1699 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1700 THEN
1701 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1702 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1703 ELSE
1704 X_return_status := FND_API.G_RET_STS_ERROR;
1705 RAISE FND_API.G_EXC_ERROR;
1706 END IF;
1707 END IF;
1708 -- Debug info.
1709 IF AHL_DEBUG_PUB.G_FILE_DEBUG THEN
1710 AHL_DEBUG_PUB.debug( 'Before processing Supplier Record ahl_di_doc_index_pub.modify document','+DI+');
1711 END IF;
1712
1713 --For update supplier record
1714 ELSIF p_x_document_tbl(i).process_flag = 'S'
1715 THEN
1716 IF p_x_supplier_tbl.COUNT > 0
1717 THEN
1718 FOR i IN p_x_supplier_tbl.FIRST..p_x_supplier_tbl.LAST
1719 LOOP
1720 IF (p_x_supplier_tbl(i).supplier_id IS NULL) OR
1721 (p_x_supplier_tbl(i).supplier_id = FND_API.G_MISS_NUM) THEN
1722 -- If Supplier Name is available
1723 IF (p_x_supplier_tbl(i).supplier_number IS NOT NULL) AND
1724 (p_x_supplier_tbl(i).supplier_number <> FND_API.G_MISS_CHAR)
1725 THEN
1726 IF ahl_di_doc_index_pvt.get_product_install_status('PO') IN ('N','L')
1727 THEN
1728 OPEN for_party_name(p_x_supplier_tbl(i).supplier_number);
1729 FETCH for_party_name INTO l_supplier_id;
1730 IF for_party_name%FOUND
1731 THEN
1732 l_supplier_tbl(i).supplier_id := l_supplier_id;
1733 ELSE
1734 FND_MESSAGE.SET_NAME('AHL','AHL_DI_SUPPL_ID_NOT_EXIST');
1735 FND_MESSAGE.SET_TOKEN('SUPNAME',p_x_supplier_tbl(i).supplier_number);
1736 FND_MSG_PUB.ADD;
1737 END IF;
1738 CLOSE for_party_name;
1739 ELSIF ahl_di_doc_index_pvt.get_product_install_status('PO') IN ('I','S')
1740 THEN
1741 OPEN for_vendor_id(p_x_supplier_tbl(i).supplier_number);
1742 FETCH for_vendor_id INTO l_supplier_id;
1743 IF for_vendor_id%FOUND
1744 THEN
1745 l_supplier_tbl(i).supplier_id := l_supplier_id;
1746 ELSE
1747 FND_MESSAGE.SET_NAME('AHL','AHL_DI_SUPPL_ID_NOT_EXIST');
1748 FND_MESSAGE.SET_TOKEN('SUPNAME',p_x_supplier_tbl(i).supplier_number);
1749 FND_MSG_PUB.ADD;
1750 END IF;
1751 CLOSE for_vendor_id;
1752 END IF;
1753 --Id presents
1754 ELSIF (p_x_supplier_tbl(i).supplier_id IS NOT NULL) AND
1755 (p_x_supplier_tbl(i).supplier_id <> FND_API.G_MISS_NUM)
1756 THEN
1757 l_supplier_tbl(i).supplier_id := p_x_supplier_tbl(i).supplier_id;
1758 ELSE
1759 IF p_x_supplier_tbl(i).delete_flag <> 'Y' THEN
1760 --Both Supplier Id and Name are missing
1761 FND_MESSAGE.SET_NAME('AHL','AHL_DI_SUPPL_ID_NOT_EXIST');
1762 FND_MESSAGE.SET_TOKEN('SUPNAME',p_x_supplier_tbl(i).supplier_number);
1763 FND_MSG_PUB.ADD;
1764 ELSE
1765 l_supplier_tbl(i).supplier_id := p_x_supplier_tbl(i).supplier_id;
1766 END IF;
1767 END IF;
1768 END IF;
1769 -- For Preference Code, meaning presents
1770 IF p_x_supplier_tbl(i).preference_desc IS NOT NULL AND
1771 p_x_supplier_tbl(i).preference_desc <> FND_API.G_MISS_CHAR
1772 THEN
1773
1774 Check_lookup_name_Or_Id (
1775 p_lookup_type => 'AHL_SUPPLIER_PREF_TYPE',
1776 p_lookup_code => null,
1777 p_meaning => p_x_supplier_tbl(i).preference_desc,
1778 p_check_id_flag => 'Y',
1779 x_lookup_code => l_supplier_tbl(i).preference_code,
1780 x_return_status => l_return_status);
1781
1782 IF nvl(l_return_status, 'X') <> 'S'
1783 THEN
1784 FND_MESSAGE.SET_NAME('AHL','AHL_DI_SUPPLIER_PREF_TYPE');
1785 FND_MSG_PUB.ADD;
1786 END IF;
1787 END IF;
1788 -- Pref Code presents
1789 IF p_x_supplier_tbl(i).preference_code IS NOT NULL AND
1790 p_x_supplier_tbl(i).preference_code <> FND_API.G_MISS_CHAR
1791 THEN
1792 l_supplier_tbl(i).preference_code := p_x_supplier_tbl(i).preference_code;
1793 ELSE
1794 --Both missing
1795 l_supplier_tbl(i).preference_code := p_x_supplier_tbl(i).preference_code;
1796 END IF;
1797
1798 --if a Subscription exists that Subscribes from the particular Supplier, this Supplier cannot be deleted :prithwi
1799 IF p_x_supplier_tbl(i).delete_flag = 'Y' THEN
1800 if p_x_supplier_tbl(i).document_id IS NULL then
1801 FND_MESSAGE.SET_NAME('AHL','AHL_DI_SUPPL_DOC_ID_NULL');
1802 FND_MSG_PUB.ADD;
1803 end if;
1804 OPEN for_subcr_validate(l_supplier_tbl(i).supplier_id, p_x_supplier_tbl(i).document_id );
1805 FETCH for_subcr_validate INTO l_subscription_id;
1806 IF for_subcr_validate%FOUND
1807 THEN
1808 FND_MESSAGE.SET_NAME('AHL','AHL_DI_SUPPL_ASSOC_EXISTS');
1809
1810 -- Bug : Perf Fixes for 4918997
1811 /*
1812 OPEN for_supplier_name(p_x_supplier_tbl(i).supplier_number);
1813 FETCH for_supplier_name INTO l_supplier_name;
1814 CLOSE for_supplier_name;
1815 */
1816 IF l_prod_install_status IN ('N','L') THEN
1817 OPEN for_supplier_name_hz(p_x_supplier_tbl(i).supplier_number);
1818 FETCH for_supplier_name_hz INTO l_supplier_name;
1819 CLOSE for_supplier_name_hz;
1820 ELSIF l_prod_install_status IN ('I','S') THEN
1821 OPEN for_supplier_name_po(p_x_supplier_tbl(i).supplier_number);
1822 FETCH for_supplier_name_po INTO l_supplier_name;
1823 CLOSE for_supplier_name_po;
1824 END IF;
1825
1826 FND_MESSAGE.SET_TOKEN('SUPP',l_supplier_name);
1827 FND_MSG_PUB.ADD;
1828 END IF;
1829 CLOSE for_subcr_validate;
1830
1831 END IF;
1832
1833 l_supplier_tbl(i).supplier_document_id := p_x_supplier_tbl(i).supplier_document_id;
1834 l_supplier_tbl(i).document_id := p_x_supplier_tbl(i).document_id;
1835 l_supplier_tbl(i).attribute_category := p_x_supplier_tbl(i).attribute_category;
1836 l_supplier_tbl(i).attribute1 := p_x_supplier_tbl(i).attribute1;
1837 l_supplier_tbl(i).attribute2 := p_x_supplier_tbl(i).attribute2;
1838 l_supplier_tbl(i).attribute3 := p_x_supplier_tbl(i).attribute3;
1839 l_supplier_tbl(i).attribute4 := p_x_supplier_tbl(i).attribute4;
1840 l_supplier_tbl(i).attribute5 := p_x_supplier_tbl(i).attribute5;
1841 l_supplier_tbl(i).attribute6 := p_x_supplier_tbl(i).attribute6;
1842 l_supplier_tbl(i).attribute7 := p_x_supplier_tbl(i).attribute7;
1843 l_supplier_tbl(i).attribute8 := p_x_supplier_tbl(i).attribute8;
1844 l_supplier_tbl(i).attribute9 := p_x_supplier_tbl(i).attribute9;
1845 l_supplier_tbl(i).attribute10 := p_x_supplier_tbl(i).attribute10;
1846 l_supplier_tbl(i).attribute11 := p_x_supplier_tbl(i).attribute11;
1847 l_supplier_tbl(i).attribute12 := p_x_supplier_tbl(i).attribute12;
1848 l_supplier_tbl(i).attribute13 := p_x_supplier_tbl(i).attribute13;
1849 l_supplier_tbl(i).attribute14 := p_x_supplier_tbl(i).attribute14;
1850 l_supplier_tbl(i).attribute15 := p_x_supplier_tbl(i).attribute15;
1851 l_supplier_tbl(i).delete_flag := p_x_supplier_tbl(i).delete_flag;
1852 l_supplier_tbl(i).object_version_number := p_x_supplier_tbl(i).object_version_number;
1853 --Standard check for count messages
1854 /*--{{adharia
1855 l_msg_count := FND_MSG_PUB.count_msg;
1856
1857 IF l_msg_count > 0 THEN
1858 X_msg_count := l_msg_count;
1859 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1860 THEN
1861 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1862 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1863 ELSE
1864 X_return_status := FND_API.G_RET_STS_ERROR;
1865 RAISE FND_API.G_EXC_ERROR;
1866 END IF;
1867 END IF;
1868 --{{adharia
1869 */
1870 END LOOP;
1871 END IF;
1872 -- Debug info.
1873 IF AHL_DEBUG_PUB.G_FILE_DEBUG THEN
1874 AHL_DEBUG_PUB.debug( 'Before processing Recipient Record ahl_di_doc_index_pub.modify document','+DI+');
1875 END IF;
1876 --For Update Recipient Record
1877 ELSIF p_x_document_tbl(i).process_flag = 'R'
1878 THEN
1879 IF p_x_recipient_tbl.COUNT > 0
1880 THEN
1881 FOR i IN p_x_recipient_tbl.FIRST..p_x_recipient_tbl.LAST
1882 LOOP
1883 --For Recipient Id
1884
1885 IF (p_x_recipient_tbl(i).recipient_party_id IS NULL) OR
1886 (p_x_recipient_tbl(i).recipient_party_id = FND_API.G_MISS_NUM) THEN
1887 -- If Recipient Name is available
1888 IF (p_x_recipient_tbl(i).recipient_party_number IS NOT NULL) AND
1889 (p_x_recipient_tbl(i).recipient_party_number <> FND_API.G_MISS_CHAR)
1890 THEN
1891
1892 OPEN for_party_name(p_x_recipient_tbl(i).recipient_party_number);
1893 FETCH for_party_name INTO l_recipient_id;
1894 IF for_party_name%FOUND
1895 THEN
1896 l_recipient_tbl(i).recipient_party_id := l_recipient_id;
1897 ELSE
1898 FND_MESSAGE.SET_NAME('AHL','AHL_DI_RECIPIENT_ID_NOT_EXIST');
1899 FND_MESSAGE.SET_TOKEN('RECPNAME',p_x_recipient_tbl(i).recipient_party_number);
1900 FND_MSG_PUB.ADD;
1901 END IF;
1902 CLOSE for_party_name;
1903 --ID presents
1904 ELSIF (p_x_recipient_tbl(i).recipient_party_id IS NOT NULL) AND
1905 (p_x_recipient_tbl(i).recipient_party_id <> FND_API.G_MISS_NUM)
1906 THEN
1907 l_recipient_tbl(i).recipient_party_id := p_x_recipient_tbl(i).recipient_party_id;
1908 ELSE
1909 --Both Recipient Id and Name are missing
1910 l_recipient_tbl(i).recipient_party_id := p_x_recipient_tbl(i).recipient_party_id;
1911 END IF;
1912 END IF;
1913 l_recipient_tbl(i).recipient_document_id := p_x_recipient_tbl(i).recipient_document_id;
1914 l_recipient_tbl(i).document_id := p_x_recipient_tbl(i).document_id;
1915 --Added pjha 24-Jul-2002 for bug#2473425
1916 l_recipient_tbl(i).recipient_party_number := p_x_recipient_tbl(i).recipient_party_number;
1917 l_recipient_tbl(i).attribute_category := p_x_recipient_tbl(i).attribute_category;
1918 l_recipient_tbl(i).attribute1 := p_x_recipient_tbl(i).attribute1;
1919 l_recipient_tbl(i).attribute2 := p_x_recipient_tbl(i).attribute2;
1920 l_recipient_tbl(i).attribute3 := p_x_recipient_tbl(i).attribute3;
1921 l_recipient_tbl(i).attribute4 := p_x_recipient_tbl(i).attribute4;
1922 l_recipient_tbl(i).attribute5 := p_x_recipient_tbl(i).attribute5;
1923 l_recipient_tbl(i).attribute6 := p_x_recipient_tbl(i).attribute6;
1924 l_recipient_tbl(i).attribute7 := p_x_recipient_tbl(i).attribute7;
1925 l_recipient_tbl(i).attribute8 := p_x_recipient_tbl(i).attribute8;
1926 l_recipient_tbl(i).attribute9 := p_x_recipient_tbl(i).attribute9;
1927 l_recipient_tbl(i).attribute10 := p_x_recipient_tbl(i).attribute10;
1928 l_recipient_tbl(i).attribute11 := p_x_recipient_tbl(i).attribute11;
1929 l_recipient_tbl(i).attribute12 := p_x_recipient_tbl(i).attribute12;
1930 l_recipient_tbl(i).attribute13 := p_x_recipient_tbl(i).attribute13;
1931 l_recipient_tbl(i).attribute14 := p_x_recipient_tbl(i).attribute14;
1932 l_recipient_tbl(i).attribute15 := p_x_recipient_tbl(i).attribute15;
1933 l_recipient_tbl(i).delete_flag := p_x_recipient_tbl(i).delete_flag;
1934 l_recipient_tbl(i).object_version_number := p_x_recipient_tbl(i).object_version_number;
1935 --Standard check for count messages
1936 /*
1937 --{{adharia
1938 l_msg_count := FND_MSG_PUB.count_msg;
1939
1940 IF l_msg_count > 0 THEN
1941 X_msg_count := l_msg_count;
1942 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1943 THEN
1944 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1945 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1946 ELSE
1947 X_return_status := FND_API.G_RET_STS_ERROR;
1948 RAISE FND_API.G_EXC_ERROR;
1949 END IF;
1950 END IF;
1951 --}}adharia
1952 */
1953
1954 END LOOP;
1955 END IF;
1956 END IF;
1957 END LOOP;
1958 END IF;
1959
1960
1961 /*-----------------------------------------------------------*/
1962 /* procedure name: AHL_DI_DOC_INDEX_CHUK.MODIFY_DOCUMENT_PRE */
1963 /* AHL_DI_DOC_INDEX_VHUK.MODIFY_DOCUMENT_PRE */
1964 /* */
1965 /* description : Added by Siddhartha to call User Hooks */
1966 /* Date : Dec 28 2001 */
1967 /*-----------------------------------------------------------*/
1968
1969
1970 IF JTF_USR_HKS.Ok_to_Execute( 'AHL_DI_DOC_INDEX_PUB','MODIFY_DOCUMENT',
1971 'B', 'C' ) then
1972 AHL_DEBUG_PUB.debug( 'Start AHL_DI_DOC_INDEX_CUHK.MODIFY_DOCUMENT_PRE');
1973
1974 AHL_DI_DOC_INDEX_CUHK.MODIFY_DOCUMENT_PRE
1975 (
1976
1977 p_x_document_tbl => l_document_tbl ,
1978 p_x_supplier_tbl => l_supplier_tbl,
1979 p_x_recipient_tbl => l_recipient_tbl,
1980 x_return_status => l_return_status,
1981 x_msg_count => l_msg_count ,
1982 x_msg_data => l_msg_data
1983 );
1984
1985
1986 AHL_DEBUG_PUB.debug( 'End AHL_DI_DOC_INDEX_CUHK.MODIFY_DOCUMENT_PRE');
1987
1988
1989 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1990 RAISE FND_API.G_EXC_ERROR;
1991 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1992 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1993 END IF;
1994 END IF;
1995
1996 IF JTF_USR_HKS.Ok_to_Execute( 'AHL_DI_DOC_INDEX_PUB','MODIFY_DOCUMENT',
1997 'B', 'V' ) then
1998
1999 AHL_DEBUG_PUB.debug( 'Start AHL_DI_DOC_INDEX_VUHK.MODIFY_DOCUMENT_PRE');
2000
2001 AHL_DI_DOC_INDEX_VUHK.MODIFY_DOCUMENT_PRE
2002 (
2003 p_x_document_tbl => l_document_tbl ,
2004 p_x_supplier_tbl => l_supplier_tbl,
2005 p_x_recipient_tbl => l_recipient_tbl,
2006 X_RETURN_STATUS => l_return_status ,
2007 X_MSG_COUNT => l_msg_count ,
2008 X_MSG_DATA => l_msg_data );
2009
2010 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2011 RAISE FND_API.G_EXC_ERROR;
2012 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2013 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2014 END IF;
2015
2016 AHL_DEBUG_PUB.debug( 'End AHL_DI_DOC_INDEX_VUHK.MODIFY_DOCUMENT_PRE');
2017
2018 END IF;
2019
2020
2021 /*---------------------------------------------------------*/
2022 /* End ; Date : Dec 28 2001 */
2023 /*---------------------------------------------------------*/
2024
2025
2026
2027
2028 -- Call the Private API
2029 --{{adharia
2030 l_msg_count := FND_MSG_PUB.count_msg;
2031 IF l_msg_count > 0 THEN
2032 X_msg_count := l_msg_count;
2033 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
2034 THEN
2035 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2036 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2037 ELSE
2038 X_return_status := FND_API.G_RET_STS_ERROR;
2039 RAISE FND_API.G_EXC_ERROR;
2040 END IF;
2041 END IF;
2042 --{{adharia
2043
2044 AHL_DI_DOC_INDEX_PVT.MODIFY_DOCUMENT
2045 (
2046 p_api_version => 1.0,
2047 p_init_msg_list => p_init_msg_list,
2048 p_commit => p_commit,
2049 p_validate_only => p_validate_only,
2050 p_validation_level => p_validation_level,
2051 p_x_document_tbl => l_document_tbl,
2052 p_x_supplier_tbl => l_supplier_tbl,
2053 p_x_recipient_tbl => l_recipient_tbl,
2054 x_return_status => l_return_status,
2055 x_msg_count => l_msg_count,
2056 x_msg_data => l_msg_data
2057 );
2058 --Standard check for count messages
2059 l_msg_count := FND_MSG_PUB.count_msg;
2060
2061 IF l_msg_count > 0 THEN
2062 X_msg_count := l_msg_count;
2063 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
2064 THEN
2065 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2066 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2067 ELSE
2068 X_return_status := FND_API.G_RET_STS_ERROR;
2069 RAISE FND_API.G_EXC_ERROR;
2070 END IF;
2071 END IF;
2072
2073
2074
2075
2076 /*----------------------------------------------------------------------------- */
2077 /* procedure name: AHL_DI_DOC_INDEX_VHUK.MODIFY_DOCUMENT_POST */
2078 /* AHL_DI_DOC_INDEX_CHUK.MODIFY_DOCUMENT_POST */
2079 /* */
2080 /* description : Added by ssaklani to call User Hooks */
2081 /* Date : Dec 28 2001 */
2082 /*------------------------------------------------------------------------------*/
2083
2084
2085
2086 IF JTF_USR_HKS.Ok_to_Execute( 'AHL_DI_DOC_INDEX_PUB','MODIFY_DOCUMENT','A', 'V' )
2087 then
2088 AHL_DEBUG_PUB.debug( 'Start AHL_DI_DOC_INDEX_VUHK.MODIFY_DOCUMENT_POST');
2089
2090 AHL_DI_DOC_INDEX_VUHK.MODIFY_DOCUMENT_POST
2091 (
2092 p_document_tbl => l_document_tbl ,
2093 p_supplier_tbl => l_supplier_tbl,
2094 p_recipient_tbl => l_recipient_tbl,
2095 X_RETURN_STATUS => l_return_status ,
2096 X_MSG_COUNT => l_msg_count ,
2097 X_MSG_DATA => l_msg_data );
2098
2099 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2100 RAISE FND_API.G_EXC_ERROR;
2101 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2102 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2103 END IF;
2104
2105 AHL_DEBUG_PUB.debug( 'End AHL_DI_DOC_INDEX_VUHK.MODIFY_DOCUMENT_POST');
2106
2107 END IF;
2108
2109 IF JTF_USR_HKS.Ok_to_Execute( 'AHL_DI_DOC_INDEX_PUB','MODIFY_DOCUMENT','A', 'C' )
2110 then
2111
2112 AHL_DEBUG_PUB.debug( 'Start AHL_DI_DOC_INDEX_CUHK.MODIFY_DOCUMENT_POST');
2113
2114 AHL_DI_DOC_INDEX_CUHK.MODIFY_DOCUMENT_POST(
2115
2116 p_document_tbl => l_document_tbl ,
2117 p_supplier_tbl => l_supplier_tbl,
2118 p_recipient_tbl => l_recipient_tbl,
2119 X_RETURN_STATUS => l_return_status,
2120 X_MSG_COUNT => l_msg_count,
2121 X_MSG_DATA => l_msg_data);
2122
2123
2124
2125 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2126 RAISE FND_API.G_EXC_ERROR;
2127 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2128 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2129 END IF;
2130
2131 AHL_DEBUG_PUB.debug( 'End AHL_DI_DOC_INDEX_CUHK.MODIFY_DOCUMENT_POST');
2132
2133 END IF;
2134
2135 /*---------------------------------------------------------*/
2136 /* End ; Date : Dec 28 2001 */
2137 /*---------------------------------------------------------*/
2138
2139
2140
2141
2142 --Standard check for commit
2143 IF FND_API.TO_BOOLEAN(p_commit) THEN
2144 COMMIT;
2145 END IF;
2146 -- Debug info
2147 AHL_DEBUG_PUB.debug( 'End of public api Modify Document','+DI+');
2148 -- Check if API is called in debug mode. If yes, disable debug.
2149 AHL_DEBUG_PUB.disable_debug;
2150
2151 EXCEPTION
2152 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2153 ROLLBACK TO modify_document;
2154 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2155 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2156 p_count => x_msg_count,
2157 p_data => x_msg_data);
2158 IF AHL_DEBUG_PUB.G_FILE_DEBUG THEN
2159 AHL_DEBUG_PUB.log_app_messages (
2160 x_msg_count, x_msg_data, 'ERROR' );
2161 AHL_DEBUG_PUB.debug( 'ahl_di_doc_index_pub.Modify Document','+DI+');
2162 END IF;
2163
2164 -- Check if API is called in debug mode. If yes, disable debug.
2165 AHL_DEBUG_PUB.disable_debug;
2166
2167 WHEN FND_API.G_EXC_ERROR THEN
2168 ROLLBACK TO modify_document;
2169 X_return_status := FND_API.G_RET_STS_ERROR;
2170 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2171 p_count => x_msg_count,
2172 p_data => X_msg_data);
2173
2174 -- Debug info.
2175 IF AHL_DEBUG_PUB.G_FILE_DEBUG THEN
2176 AHL_DEBUG_PUB.log_app_messages (
2177 x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
2178 AHL_DEBUG_PUB.debug( 'ahl_di_doc_index_pub.Modify Document','+DI+');
2179 END IF;
2180 -- Check if API is called in debug mode. If yes, disable debug.
2181 AHL_DEBUG_PUB.disable_debug;
2182
2183 WHEN OTHERS THEN
2184 ROLLBACK TO modify_document;
2185 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2186 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2187 THEN
2188 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_DOCUMENTS_PUB',
2189 p_procedure_name => 'MODIFY_DOCUMENT',
2190 p_error_text => SUBSTR(SQLERRM,1,240));
2191 END IF;
2192 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2193 p_count => x_msg_count,
2194 p_data => X_msg_data);
2195 -- Debug info.
2196 IF AHL_DEBUG_PUB.G_FILE_DEBUG THEN
2197 AHL_DEBUG_PUB.log_app_messages (
2198 x_msg_count, x_msg_data, 'SQL ERROR' );
2199 AHL_DEBUG_PUB.debug( 'ahl_di_doc_index_pub.Modify Document','+DI+');
2200 END IF;
2201
2202 -- Check if API is called in debug mode. If yes, disable debug.
2203 AHL_DEBUG_PUB.disable_debug;
2204
2205 END MODIFY_DOCUMENT;
2206
2207 END AHL_DI_DOC_INDEX_PUB;