1 PACKAGE BODY AHL_DI_SUBSCRIPTION_PUB AS
2 /* $Header: AHLPSUBB.pls 115.35 2004/04/29 06:45:14 adharia noship $ */
3 --
4 G_PKG_NAME VARCHAR2(30) := 'AHL_DI_SUBSCRIPTION_PUB';
5
6 /*-----------------------------------------------------------*/
7 /* procedure name: Check_lookup_name_Or_Id(private procedure)*/
8 /* description : used to retrieve lookup code */
9 /* */
10 /*-----------------------------------------------------------*/
11
12 --G_DEBUG VARCHAR2(1):=FND_PROFILE.VALUE('AHL_API_FILE_DEBUG_ON');
13 G_DEBUG VARCHAR2(1) := AHL_DEBUG_PUB.is_log_enabled;
14
15 PROCEDURE Check_lookup_name_Or_Id
16 ( p_lookup_type IN FND_LOOKUPS.lookup_type%TYPE,
17 p_lookup_code IN FND_LOOKUPS.lookup_code%TYPE ,
18 p_meaning IN FND_LOOKUPS.meaning%TYPE,
19 p_check_id_flag IN VARCHAR2,
20 x_lookup_code OUT NOCOPY FND_LOOKUPS.lookup_code%TYPE,
21 x_return_status OUT NOCOPY VARCHAR2)
22 IS
23
24
25 BEGIN
26 IF (p_lookup_code IS NOT NULL) THEN
27 IF (p_check_id_flag = 'Y') THEN
28 SELECT lookup_code INTO x_lookup_code
29 FROM FND_LOOKUP_VALUES_VL
30 WHERE lookup_type = p_lookup_type
31 AND lookup_code = p_lookup_code
32 AND sysdate between start_date_active
33 AND nvl(end_date_active,sysdate);
34 ELSE
35 x_lookup_code := p_lookup_code;
36 END IF;
37 ELSE
38 SELECT lookup_code INTO x_lookup_code
39 FROM FND_LOOKUP_VALUES_VL
40 WHERE lookup_type = p_lookup_type
41 AND meaning = p_meaning
42 AND sysdate between start_date_active
43 AND nvl(end_date_active,sysdate);
44 END IF;
45 x_return_status := FND_API.G_RET_STS_SUCCESS;
46 EXCEPTION
47 WHEN no_data_found THEN
48 x_return_status := FND_API.G_RET_STS_ERROR;
49 WHEN too_many_rows THEN
50 x_return_status := FND_API.G_RET_STS_ERROR;
51 WHEN OTHERS THEN
52 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
53 RAISE;
54 END;
55 /*------------------------------------------------------*/
56 /* procedure name: create_subscription */
57 /* description : Creates new subscription record */
58 /* for an associated document */
59 /* */
60 /*------------------------------------------------------*/
61 PROCEDURE CREATE_SUBSCRIPTION
62 (
63 p_api_version IN NUMBER := 1.0 ,
64 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
65 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
66 p_validate_only IN VARCHAR2 := FND_API.G_TRUE ,
67 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
68 p_x_subscription_tbl IN OUT NOCOPY subscription_tbl ,
69 p_module_type IN VARCHAR2 ,
70 x_return_status OUT NOCOPY VARCHAR2 ,
71 x_msg_count OUT NOCOPY NUMBER ,
72 x_msg_data OUT NOCOPY VARCHAR2)
73 IS
74
75 --Used to retrieve the party id for party name
76 CURSOR for_party_name(c_party_name IN VARCHAR2)
77 IS
78 SELECT party_id
79 FROM hz_parties
80 WHERE upper(party_name) = upper(c_party_name);
81
82 --Get the party id from hz parties
83 CURSOR for_party_id(c_party_id IN NUMBER)
84 IS
85 SELECT party_id
86 FROM hz_parties
87 WHERE party_id = c_party_id;
88
89 -- enhancement #2525108: lov for PO Number : pbarman april 2003
90 -- Get Puchase Order Number from PO_PURCHASE_ORDER_V
91 CURSOR for_ponumber_id(c_ponumber IN VARCHAR2)
92 IS
93 SELECT distinct segment1
94 FROM PO_HEADERS_V
95 WHERE nvl(approved_flag, 'N')='Y' and upper(segment1) = upper(c_ponumber);
96
97 -- Used to retrieve vendor id from po vendors
98 CURSOR for_vendor_id(c_vendor_name IN VARCHAR2)
99 IS
100 SELECT vendor_id
101 FROM po_vendors
102 WHERE upper(vendor_name) = upper(c_vendor_name);
103
104 --Used to retrieve the party id from party name
105 CURSOR get_party_name (c_party_name IN VARCHAR2)
106 IS
107 --Modified pjha 07-Aug-2002 for performance
108 /*
109 SELECT person_id
110 FROM per_people_f ppf, per_person_types ppt
111 WHERE upper(ppf.FULL_NAME) = upper(c_party_name)
112 AND trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
113 AND nvl(ppf.current_employee_flag,'x') = 'Y'
114 AND ppf.person_type_id = ppt.person_type_id
115 AND ppt.system_person_type ='EMP';
116 */
117 --Modified pjha 29-Aug-2002 for bug#2536490(added trim function)
118 SELECT person_id
119 FROM per_all_people_f pap, per_person_types ppt
120 WHERE trim(upper(FULL_NAME)) = upper(c_party_name)
121 AND trunc(sysdate) between pap.effective_start_date and pap.effective_end_date
122 AND nvl(pap.current_employee_flag,'x') = 'Y'
123 AND pap.person_type_id = ppt.person_type_id
124 AND ppt.system_person_type ='EMP'
125 AND decode(HR_SECURITY.VIEW_ALL ,'Y' , 'TRUE', HR_SECURITY.SHOW_RECORD('PER_ALL_PEOPLE_F', pap.person_id,
126 pap.person_type_id, pap.employee_number,pap.applicant_number)) = 'TRUE'
127 AND decode(hr_general.get_xbg_profile,'Y',pap.business_group_id , hr_general.get_business_group_id)
128 = pap.business_group_id;
129
130 --
131 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_SUBSCRIPTION';
132 l_api_version CONSTANT NUMBER := 1.0;
133 l_msg_count NUMBER;
134 l_msg_data VARCHAR2(2000);
135 l_return_status VARCHAR2(1);
136 l_supplier_id NUMBER;
137 l_requested_by_party_id NUMBER;
138 l_media_type_code VARCHAR2(30);
139 l_frequency_code VARCHAR2(30);
140 l_subscription_type_code VARCHAR2(30);
141 l_status_code VARCHAR2(30);
142 l_subscription_tbl AHL_DI_SUBSCRIPTION_PVT.subscription_tbl;
143 l_init_msg_list VARCHAR2(10) := FND_API.G_TRUE;
144 -- Enhancement #2205830: pbarman april 2003
145 l_check_quantity NUMBER;
146 -- Enhancement #2525108: pbarman april 2003
147 l_purchase_order_no VARCHAR2(20);
148
149 BEGIN
150 -- Standard Start of API savepoint
151 SAVEPOINT create_subscription;
152 -- Check if API is called in debug mode. If yes, enable debug.
153 IF G_DEBUG='Y' THEN
154 AHL_DEBUG_PUB.enable_debug;
155
156 END IF;
157 -- Debug info.
158 IF G_DEBUG='Y' THEN
159 IF G_DEBUG='Y' THEN
160 AHL_DEBUG_PUB.debug( 'enter ahl_di_subscription_pub.Create Subscription','+SUB+');
161
162 END IF;
163 END IF;
164 -- Standard call to check for call compatibility.
165 IF FND_API.to_boolean(l_init_msg_list)
166 THEN
167 FND_MSG_PUB.initialize;
168 END IF;
169 -- Initialize API return status to success
170 x_return_status := 'S';
171 -- Initialize message list if p_init_msg_list is set to TRUE.
172 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
173 p_api_version,
174 l_api_name,G_PKG_NAME)
175 THEN
176 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
177 END IF;
178 --Start of API Body
179 IF p_x_subscription_tbl.count > 0
180 THEN
181 FOR i IN p_x_subscription_tbl.FIRST..p_x_subscription_tbl.LAST
182 LOOP
183 -- For Requested By...
184 -- Party Name is present
185 IF (p_x_subscription_tbl(i).requested_by_pty_name IS NOT NULL)
186 THEN
187
188 IF ahl_di_doc_index_pvt.get_product_install_status('PER') in ('N','L') THEN
189 -- Use cursor to retrieve party id using party name: party id will be unique for given party name
190 OPEN for_party_name(p_x_subscription_tbl(i).requested_by_pty_name);
191 FETCH for_party_name INTO l_requested_by_party_id;
192 IF for_party_name%FOUND THEN
193 p_x_subscription_tbl(i).requested_by_party_id := l_requested_by_party_id;
194 ELSE
195 FND_MESSAGE.SET_NAME('AHL','AHL_DI_REQ_PTY_ID_NOT_EXISTS');
196 FND_MSG_PUB.ADD;
197 END IF;
198 CLOSE for_party_name;
199
200 ELSIF ahl_di_doc_index_pvt.get_product_install_status('PER') in ('I','S') THEN
201 -- If party id is already present, use it; if not, retrieve using party name
202 -- Use cursor to retrieve party id using party name: party id may not be unique for given party name
203 OPEN get_party_name(p_x_subscription_tbl(i).requested_by_pty_name);
204 LOOP
205 FETCH get_party_name INTO l_requested_by_party_id;
206 EXIT WHEN get_party_name%NOTFOUND;
207 END LOOP;
208 -- If no records for name, then name is INVALID: show error
209 IF get_party_name%ROWCOUNT = 0 THEN
210 FND_MESSAGE.SET_NAME('AHL','AHL_DI_REQ_ID_NOT_EXISTS');
211 FND_MESSAGE.SET_TOKEN('REQ',p_x_subscription_tbl(i).requested_by_pty_name);
212
213
214 FND_MSG_PUB.ADD;
215 -- If only 1 record for name, use the id: id will be unique: use id
216 ELSIF get_party_name%ROWCOUNT = 1 THEN
217 p_x_subscription_tbl(i).requested_by_party_id := l_requested_by_party_id;
218 -- If more than 1 record, then error: id is not unique: ask user to choose from LOV
219 ELSIF
220 --p_x_subscription_tbl(i).requested_by_party_id IS NULL AND
221 p_x_subscription_tbl(i).requested_by_party_id IS NULL
222 THEN
223 -- FND_MESSAGE.SET_NAME('AHL','LOV dup val');
224 FND_MESSAGE.SET_NAME('AHL','AHL_DI_REQUESTED_BY_USE_LOV');
225 FND_MSG_PUB.ADD;
226 END IF;
227 CLOSE get_party_name;
228 END IF;
229
230 -- Party Name is not present: since id is mandatory: show error
231 ELSE
232 -- FND_MESSAGE.SET_NAME('AHL',' '|| p_x_subscription_tbl(i).requested_by_pty_name);
233 FND_MESSAGE.SET_NAME('AHL','AHL_DI_REQ_PTY_ID_NULL');
234 FND_MSG_PUB.ADD;
235
236 END IF;
237
238 -- For Subscribed From...
239 -- Party Name is present, retrieve party id from party name
240 IF (p_x_subscription_tbl(i).subscribed_frm_pty_name IS NOT NULL)
241 THEN
242
243 IF ahl_di_doc_index_pvt.get_product_install_status('PO') IN ('N','L') THEN
244 OPEN for_party_name(p_x_subscription_tbl(i).subscribed_frm_pty_name);
245 FETCH for_party_name INTO l_supplier_id;
246 IF for_party_name%FOUND
247 THEN
248 l_subscription_tbl(i).subscribed_frm_party_id := l_supplier_id;
249 ELSE
250 FND_MESSAGE.SET_NAME('AHL','AHL_DI_SUB_PTY_ID_INVALID');
251 FND_MSG_PUB.ADD;
252 END IF;
253 CLOSE for_party_name;
254
255 ELSIF ahl_di_doc_index_pvt.get_product_install_status('PO') IN ('I','S') THEN
256 OPEN for_vendor_id(p_x_subscription_tbl(i).subscribed_frm_pty_name);
257 FETCH for_vendor_id INTO l_supplier_id;
258 IF for_vendor_id%FOUND
259 THEN
260 l_subscription_tbl(i).subscribed_frm_party_id := l_supplier_id;
261 ELSE
262 --FND_MESSAGE.SET_NAME('AHL','AHL_DI_SUB_PTY_ID_INVALID');
263 FND_MESSAGE.SET_NAME('AHL','AHL_DI_SUB_ID_INVALID');
264 FND_MESSAGE.SET_TOKEN('SUP',p_x_subscription_tbl(i).subscribed_frm_pty_name);
265 FND_MSG_PUB.ADD;
266 END IF;
267 CLOSE for_vendor_id;
268
269 END IF;
270
271 -- Enhancement #2034767 : Party Name is mandatory. Throw error message : pbarman Arpil 2003
272 ELSE
273
274 -- l_subscription_tbl(i).subscribed_frm_party_id := null;
275 FND_MESSAGE.SET_NAME('AHL','AHL_DI_SUBSCRIPTION_REQD');
276 FND_MESSAGE.SET_TOKEN('FIELD1',p_x_subscription_tbl(i).requested_by_pty_name);
277 FND_MSG_PUB.ADD;
278 END IF;
279
280 --For Media Type Code
281 IF p_x_subscription_tbl(i).media_type_desc IS NOT NULL
282 THEN
283 --
284 Check_lookup_name_Or_Id (
285 p_lookup_type => 'AHL_MEDIA_TYPE',
286 p_lookup_code => null,
287 p_meaning => p_x_subscription_tbl(i).media_type_desc,
288 p_check_id_flag => 'Y',
289 x_lookup_code => l_subscription_tbl(i).media_type_code,
290 x_return_status => l_return_status);
291
292 IF nvl(l_return_status, 'X') <> 'S'
293 THEN
294 FND_MESSAGE.SET_NAME('AHL','AHL_DI_MEDTYP_CODE_NOT_EXISTS');
295 FND_MSG_PUB.ADD;
296 END IF;
297 END IF;
298 -- If id is present
299 -- Both are missing
300 l_subscription_tbl(i).media_type_code := p_x_subscription_tbl(i).media_type_code;
301
302 --For Subscription Type Code
303 IF p_x_subscription_tbl(i).subscription_type_desc IS NOT NULL
304 THEN
305 Check_lookup_name_Or_Id (
306 p_lookup_type => 'AHL_SUBSCRIPTION_TYPE',
307 p_lookup_code => null,
308 p_meaning => p_x_subscription_tbl(i).subscription_type_desc,
309 p_check_id_flag => 'Y',
310 x_lookup_code => l_subscription_tbl(i).subscription_type_code,
311 x_return_status => l_return_status);
312
313 IF nvl(l_return_status, 'X') <> 'S'
314 THEN
315 FND_MESSAGE.SET_NAME('AHL','AHL_DI_SUBTYP_CODE_NOT_EXISTS');
316 FND_MSG_PUB.ADD;
317 END IF;
318 END IF;
319 -- Code Presents
320 l_subscription_tbl(i).subscription_type_code := p_x_subscription_tbl(i).subscription_type_code;
321
322 --For Frequency Code
323 IF p_x_subscription_tbl(i).frequency_desc IS NOT NULL
324 THEN
325
326 Check_lookup_name_Or_Id (
327 p_lookup_type => 'AHL_FREQUENCY_TYPE',
328 p_lookup_code => null,
329 p_meaning => p_x_subscription_tbl(i).frequency_desc,
330 p_check_id_flag => 'Y',
331 x_lookup_code => l_subscription_tbl(i).frequency_code,
332 x_return_status => l_return_status);
333
334 IF nvl(l_return_status, 'X') <> 'S'
335 THEN
336 FND_MESSAGE.SET_NAME('AHL','AHL_DI_FREQCY_CODE_NOT_EXISTS');
337 FND_MSG_PUB.ADD;
338 END IF;
339 END IF;
340 -- Frequency Code present
341 l_subscription_tbl(i).frequency_code := p_x_subscription_tbl(i).frequency_code;
342
343 --For Status Code
344 IF p_x_subscription_tbl(i).status_desc IS NOT NULL
345 THEN
346
347 Check_lookup_name_Or_Id (
348 p_lookup_type => 'AHL_SUBSCRIBE_STATUS_TYPE',
349 p_lookup_code => null,
350 p_meaning => p_x_subscription_tbl(i).status_desc,
351 p_check_id_flag => 'Y',
352 x_lookup_code => l_subscription_tbl(i).status_code,
353 x_return_status => l_return_status);
354
355 IF nvl(l_return_status, 'X') <> 'S'
356 THEN
357 FND_MESSAGE.SET_NAME('AHL','AHL_DI_STATUS_CODE_NOT_EXISTS');
358 FND_MSG_PUB.ADD;
359 END IF;
360 END IF;
361 -- If Status Code is Avialable
362 IF p_x_subscription_tbl(i).status_code IS NOT NULL
363 THEN
364 l_subscription_tbl(i).status_code := p_x_subscription_tbl(i).status_code;
365 ELSE
366 FND_MESSAGE.SET_NAME('AHL','AHL_DI_STATUS_CODE_NULL');
367 FND_MSG_PUB.ADD;
368 END IF;
369
370
371 -- Enhancement #2205830:If quantity is non integral. pbarman march 2003
372
373 IF p_x_subscription_tbl(i).quantity IS NOT NULL
374 THEN
375 l_check_quantity := p_x_subscription_tbl(i).quantity;
376 IF l_check_quantity > TRUNC(l_check_quantity,0)
377 THEN
378 FND_MESSAGE.SET_NAME('AHL','AHL_DI_QTY_NON_INT');
379 FND_MSG_PUB.ADD;
380 END IF;
381 END IF;
382 -- Enhancement #2525108: check PO Number against PO Numbers in PO_PURCHASE_ORDER_V: pbarman april 2003
383
384 IF p_x_subscription_tbl(i).purchase_order_no IS NOT NULL
385 THEN
386
387 IF ahl_di_doc_index_pvt.get_product_install_status('PO') in ('I','S')THEN
388 OPEN for_ponumber_id(p_x_subscription_tbl(i).purchase_order_no);
389 FETCH for_ponumber_id INTO l_purchase_order_no;
390 IF for_ponumber_id%FOUND THEN
391 l_subscription_tbl(i).purchase_order_no := l_purchase_order_no;
392 ELSE
393 FND_MESSAGE.SET_NAME('AHL','AHL_DI_PO_NUM_NOT_EXISTS');
394 FND_MSG_PUB.ADD;
395 END IF;
396 CLOSE for_ponumber_id;
397
398 ELSIF ahl_di_doc_index_pvt.get_product_install_status('PO') in ('N','L')THEN
399 l_subscription_tbl(i).purchase_order_no := p_x_subscription_tbl(i).purchase_order_no;
400 END IF;
401
402 END IF;
403
404 --Assigning the values
405 l_subscription_tbl(i).document_id := p_x_subscription_tbl(i).document_id;
406 l_subscription_tbl(i).requested_by_party_id := p_x_subscription_tbl(i).requested_by_party_id;
407 l_subscription_tbl(i).quantity := p_x_subscription_tbl(i).quantity;
408 l_subscription_tbl(i).start_date := p_x_subscription_tbl(i).start_date;
409 l_subscription_tbl(i).end_date := p_x_subscription_tbl(i).end_date;
410 --l_subscription_tbl(i).purchase_order_no := p_x_subscription_tbl(i).purchase_order_no;
411 l_subscription_tbl(i).attribute_category := p_x_subscription_tbl(i).attribute_category;
412 l_subscription_tbl(i).attribute1 := p_x_subscription_tbl(i).attribute1;
413 l_subscription_tbl(i).attribute2 := p_x_subscription_tbl(i).attribute2;
414 l_subscription_tbl(i).attribute3 := p_x_subscription_tbl(i).attribute3;
415 l_subscription_tbl(i).attribute4 := p_x_subscription_tbl(i).attribute4;
416 l_subscription_tbl(i).attribute5 := p_x_subscription_tbl(i).attribute5;
417 l_subscription_tbl(i).attribute6 := p_x_subscription_tbl(i).attribute6;
418 l_subscription_tbl(i).attribute7 := p_x_subscription_tbl(i).attribute7;
419 l_subscription_tbl(i).attribute8 := p_x_subscription_tbl(i).attribute8;
420 l_subscription_tbl(i).attribute9 := p_x_subscription_tbl(i).attribute9;
421 l_subscription_tbl(i).attribute10 := p_x_subscription_tbl(i).attribute10;
422 l_subscription_tbl(i).attribute11 := p_x_subscription_tbl(i).attribute11;
423 l_subscription_tbl(i).attribute12 := p_x_subscription_tbl(i).attribute12;
424 l_subscription_tbl(i).attribute13 := p_x_subscription_tbl(i).attribute13;
425 l_subscription_tbl(i).attribute14 := p_x_subscription_tbl(i).attribute14;
426 l_subscription_tbl(i).attribute15 := p_x_subscription_tbl(i).attribute15;
427 l_subscription_tbl(i).delete_flag := p_x_subscription_tbl(i).delete_flag;
428 l_subscription_tbl(i).object_version_number := p_x_subscription_tbl(i).object_version_number;
429 l_subscription_tbl(i).source_lang := p_x_subscription_tbl(i).source_lang;
430 l_subscription_tbl(i).language := p_x_subscription_tbl(i).language;
431 l_subscription_tbl(i).comments := p_x_subscription_tbl(i).comments;
432
433
434 END LOOP;
435 END IF;
436
437
438 /*------------------------------------------------------------------------*/
439 /* procedure name: AHL_DI_SUBSCRIPTION_CUHK.CREATE_SUBSCRIPTION_PRE */
440 /* AHL_DI_SUBSCRIPTION_VUHK.CREATE_SUBSCRIPTION_PRE */
441 /* description : Added by siddhartha to call User Hooks */
442 /* Date : Dec 20 2001 */
443 /*------------------------------------------------------------------------*/
444
445 IF JTF_USR_HKS.Ok_to_Execute( 'AHL_DI_SUBSCRIPTION_PUB','CREATE_SUBSCRIPTION',
446 'B', 'C' ) then
447
448 IF G_DEBUG='Y' THEN
449 AHL_DEBUG_PUB.debug( 'Start AHL_DI_SUBSCRIPTION_CUHK.CREATE_SUBSCRIPTION_Pre');
450
451 END IF;
452 AHL_DI_SUBSCRIPTION_CUHK.CREATE_SUBSCRIPTION_Pre(
453 P_X_SUBSCRIPTION_TBL => l_subscription_tbl,
454 X_RETURN_STATUS => l_return_status ,
455 X_MSG_COUNT => l_msg_count ,
456 X_MSG_DATA => l_msg_data );
457
458 IF G_DEBUG='Y' THEN
459 AHL_DEBUG_PUB.debug( 'End AHL_DI_SUBSCRIPTION_CUHK.CREATE_SUBSCRIPTION_Pre');
460
461 END IF;
462
463 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
464 RAISE FND_API.G_EXC_ERROR;
465 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
466 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
467 END IF;
468 END IF;
469
470
471 IF JTF_USR_HKS.Ok_to_Execute( 'AHL_DI_SUBSCRIPTION_PUB','CREATE_SUBSCRIPTION',
472 'B', 'V' ) then
473 IF G_DEBUG='Y' THEN
474 AHL_DEBUG_PUB.debug( 'Start AHL_DI_SUBSCRIPTION_VUHK.CREATE_SUBSCRIPTION_Pre');
475
476 END IF;
477 AHL_DI_SUBSCRIPTION_VUHK.CREATE_SUBSCRIPTION_Pre(
478 P_X_SUBSCRIPTION_TBL => l_subscription_tbl,
479 X_RETURN_STATUS => l_return_status ,
480 X_MSG_COUNT => l_msg_count ,
481 X_MSG_DATA => l_msg_data );
482
483 IF G_DEBUG='Y' THEN
484 AHL_DEBUG_PUB.debug( 'END AHL_DI_SUBSCRIPTION_VUHK.CREATE_SUBSCRIPTION_Pre');
485
486 END IF;
487
488 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
489 RAISE FND_API.G_EXC_ERROR;
490 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
491 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
492 END IF;
493 END IF;
494
495
496 /*---------------------------------------------------------*/
497 /* End ; Date : Dec 20 2001 */
498 /*---------------------------------------------------------*/
499 -- Standard call to get message count and if count is get message info.
500 l_msg_count := FND_MSG_PUB.count_msg;
501
502 IF l_msg_count > 0 THEN
503 X_msg_count := l_msg_count;
504 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
505 THEN
506 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
507 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
508 ELSE
509 X_return_status := FND_API.G_RET_STS_ERROR;
510 RAISE FND_API.G_EXC_ERROR;
511 END IF;
512 END IF;
513
514
515 -- Call the Private API
516 AHL_DI_SUBSCRIPTION_PVT.CREATE_SUBSCRIPTION
517 (
518 p_api_version => 1.0 ,
519 p_init_msg_list => l_init_msg_list ,
520 p_commit => p_commit ,
521 p_validate_only => p_validate_only ,
522 p_validation_level => p_validation_level ,
523 p_x_subscription_tbl => l_subscription_tbl ,
524 x_return_status => l_return_status ,
525 x_msg_count => l_msg_count ,
526 x_msg_data => l_msg_data
527 );
528
529 -- Standard call to get message count and if count is get message info.
530 l_msg_count := FND_MSG_PUB.count_msg;
531
532 IF l_msg_count > 0 THEN
533 X_msg_count := l_msg_count;
534 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
535 THEN
536 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
537 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
538 ELSE
539 X_return_status := FND_API.G_RET_STS_ERROR;
540 RAISE FND_API.G_EXC_ERROR;
541 END IF;
542 ELSE
543 FOR i IN 1..l_subscription_tbl.COUNT
544 LOOP
545 p_x_subscription_tbl(i).subscription_id := l_subscription_tbl(i).subscription_id;
546 END LOOP;
547 END IF;
548
549
550 /*------------------------------------------------------------------------*/
551 /* procedure name: AHL_DI_SUBSCRIPTION_VUHK.CREATE_SUBSCRIPTION_Post */
552 /* AHL_DI_SUBSCRIPTION_CUHK.CREATE_SUBSCRIPTION_Post */
553 /* description : Added by siddhartha to call User Hooks */
554 /* Date : Dec 20 2001 */
555 /*------------------------------------------------------------------------*/
556
557
558 IF JTF_USR_HKS.Ok_to_Execute( 'AHL_DI_SUBSCRIPTION_PUB','CREATE_SUBSCRIPTION',
559 'A', 'V' ) then
560
561 IF G_DEBUG='Y' THEN
562 AHL_DEBUG_PUB.debug( 'Start AHL_DI_SUBSCRIPTION_VUHK.CREATE_SUBSCRIPTION_Post');
563
564 END IF;
565
566 AHL_DI_SUBSCRIPTION_VUHK.CREATE_SUBSCRIPTION_Post(
567 P_SUBSCRIPTION_TBL => l_subscription_tbl,
568 X_RETURN_STATUS => l_return_status ,
569 X_MSG_COUNT => l_msg_count ,
570 X_MSG_DATA => l_msg_data );
571
572 IF G_DEBUG='Y' THEN
573 AHL_DEBUG_PUB.debug( 'End AHL_DI_SUBSCRIPTION_VUHK.CREATE_SUBSCRIPTION_Post');
574
575 END IF;
576
577
578 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
579 RAISE FND_API.G_EXC_ERROR;
580 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
581 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
582 END IF;
583 END IF;
584
585 IF JTF_USR_HKS.Ok_to_Execute( 'AHL_DI_SUBSCRIPTION_PUB','CREATE_SUBSCRIPTION',
586 'A', 'C' ) then
587 IF G_DEBUG='Y' THEN
588 AHL_DEBUG_PUB.debug( 'Start AHL_DI_SUBSCRIPTION_CUHK.CREATE_SUBSCRIPTION_Post');
589
590 END IF;
591 AHL_DI_SUBSCRIPTION_CUHK.CREATE_SUBSCRIPTION_Post(
592 P_SUBSCRIPTION_TBL => l_subscription_tbl,
593 X_RETURN_STATUS => l_return_status ,
594 X_MSG_COUNT => l_msg_count ,
595 X_MSG_DATA => l_msg_data );
596
597 IF G_DEBUG='Y' THEN
598 AHL_DEBUG_PUB.debug( 'End AHL_DI_SUBSCRIPTION_CUHK.CREATE_SUBSCRIPTION_Post');
599
600 END IF;
601
602 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
603 RAISE FND_API.G_EXC_ERROR;
604 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
605 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
606 END IF;
607 END IF;
608
609
610 /*---------------------------------------------------------*/
611 /* End ; Date : Dec 20 2001 */
612 /*---------------------------------------------------------*/
613
614
615
616 -- Standard check of p_commit.
617 IF FND_API.TO_BOOLEAN(p_commit) THEN
618 COMMIT;
619 END IF;
620 -- Debug info
621 IF G_DEBUG='Y' THEN
622 AHL_DEBUG_PUB.debug( 'End of public api Create Subscription','+SUB+');
623
624 END IF;
625 -- Check if API is called in debug mode. If yes, disable debug.
626 IF G_DEBUG='Y' THEN
627 AHL_DEBUG_PUB.disable_debug;
628
629 END IF;
630
631 EXCEPTION
632 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
633 ROLLBACK TO create_subscription;
634 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
635 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
636 p_count => x_msg_count,
637 p_data => x_msg_data);
638 IF G_DEBUG='Y' THEN
639 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'ERROR' );
640 AHL_DEBUG_PUB.debug( 'ahl_di_subscription_pub.Create Subscription','+SUB+');
641
642
643 -- Check if API is called in debug mode. If yes, disable debug.
644 AHL_DEBUG_PUB.disable_debug;
645
646 END IF;
647
648 WHEN FND_API.G_EXC_ERROR THEN
649 ROLLBACK TO create_subscription;
650 X_return_status := FND_API.G_RET_STS_ERROR;
651 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
652 p_count => x_msg_count,
653 p_data => X_msg_data);
654 -- Debug info.
655 IF G_DEBUG='Y' THEN
656 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
657 AHL_DEBUG_PUB.debug( 'ahl_di_subscription_pub.Create Subscription','+SUB+');
658
659
660 -- Check if API is called in debug mode. If yes, disable debug.
661 AHL_DEBUG_PUB.disable_debug;
662
663 END IF;
664
665 WHEN OTHERS THEN
666 ROLLBACK TO create_subscription;
667 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
668 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
669 THEN
670 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_DI_SUBSCRIPTION_PUB',
671 p_procedure_name => 'CREATE_SUBSCRIPTION',
672 p_error_text => SUBSTR(SQLERRM,1,240));
673 END IF;
674 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
675 p_count => x_msg_count,
676 p_data => X_msg_data);
677
678 -- Debug info.
679 IF G_DEBUG='Y' THEN
680 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'SQL ERROR' );
681 AHL_DEBUG_PUB.debug( 'ahl_di_subscription_pub.Create Subscription','+SUB+');
682
683
684 -- Check if API is called in debug mode. If yes, disable debug.
685 AHL_DEBUG_PUB.disable_debug;
686
687 END IF;
688
689 END CREATE_SUBSCRIPTION;
690 /*------------------------------------------------------ */
691 /* procedure name: modify_subscription */
692 /* description : Update the existing subscription record*/
693 /* */
694 /*------------------------------------------------------ */
695 PROCEDURE MODIFY_SUBSCRIPTION
696 (
697 p_api_version IN NUMBER := 1.0 ,
698 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
699 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
700 p_validate_only IN VARCHAR2 := FND_API.G_TRUE ,
701 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
702 p_x_subscription_tbl IN OUT NOCOPY subscription_tbl ,
703 p_module_type IN VARCHAR2 ,
704 x_return_status OUT NOCOPY VARCHAR2 ,
705 x_msg_count OUT NOCOPY NUMBER ,
706 x_msg_data OUT NOCOPY VARCHAR2
707 )
708 IS
709
710 -- Get the party id from hz parties
711 CURSOR for_party_name(c_party_name IN VARCHAR2)
712 IS
713 SELECT party_id
714 FROM hz_parties
715 WHERE upper(party_name) = upper(c_party_name);
716
717 --Get the party id from hz parties
718 CURSOR for_party_id(c_party_id IN NUMBER)
719 IS
720 SELECT party_id
721 FROM hz_parties
722 WHERE party_id = c_party_id;
723
724 -- Enhancement #2525108: lov for PO Number : pbarman april 2003
725 -- Get Puchase Order Number from PO_PURCHASE_ORDER_V
726 CURSOR for_ponumber_id(c_ponumber IN VARCHAR2)
727 IS
728 SELECT distinct segment1
729 FROM PO_HEADERS_V
730 WHERE nvl(approved_flag, 'N')='Y' and upper(segment1) = upper(c_ponumber);
731
732 -- Used to retrieve vendor id from po vendors
733 CURSOR for_vendor_id(c_vendor_name IN VARCHAR2)
734 IS
735 SELECT vendor_id
736 FROM po_vendors
737 WHERE upper(vendor_name) = upper(c_vendor_name);
738
739 --Used to retrieve the party id for party name
740 CURSOR get_party_name (c_party_name IN VARCHAR2)
741 IS
742 --Modified pjha 07-Aug-2002 for performance
743 /*
744 SELECT person_id
745 FROM per_people_f ppf, per_person_types ppt
746 WHERE upper(ppf.first_name||' '||ppf.last_name) = upper(c_party_name)
747 AND trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
748 AND nvl(ppf.current_employee_flag,'x') = 'Y'
749 AND ppf.person_type_id = ppt.person_type_id
750 AND ppt.system_person_type ='EMP';
751 */
752 --Modified pjha 29-Aug-2002 for bug#2536490(added trim function)
753 SELECT person_id
754 FROM per_all_people_f pap, per_person_types ppt
755 WHERE trim(upper(FULL_NAME)) = upper(c_party_name)
756 AND trunc(sysdate) between pap.effective_start_date and pap.effective_end_date
757 AND nvl(pap.current_employee_flag,'x') = 'Y'
758 AND pap.person_type_id = ppt.person_type_id
759 AND ppt.system_person_type ='EMP'
760 AND decode(HR_SECURITY.VIEW_ALL ,'Y' , 'TRUE', HR_SECURITY.SHOW_RECORD('PER_ALL_PEOPLE_F', pap.person_id,
761 pap.person_type_id, pap.employee_number,pap.applicant_number)) = 'TRUE'
762 AND decode(hr_general.get_xbg_profile,'Y',pap.business_group_id , hr_general.get_business_group_id)
763 = pap.business_group_id;
764
765 --Used to retrieve the party id for party name and party id
766 CURSOR get_party_name_id (c_party_name IN VARCHAR2, c_party_id IN NUMBER)
767 IS
768 SELECT person_id
769 FROM per_people_f ppf, per_person_types ppt
770 WHERE upper(FULL_NAME) = upper(c_party_name)
771 AND ppf.person_id = c_party_id
772 AND trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
773 AND nvl(ppf.current_employee_flag,'x') = 'Y'
774 AND ppf.person_type_id = ppt.person_type_id
775 AND ppt.system_person_type ='EMP';
776
777 --
778 l_api_name CONSTANT VARCHAR2(30) := 'MODIFY_SUBSCRIPTION';
779 l_api_version CONSTANT NUMBER := 1.0;
780 l_num_rec NUMBER;
781 l_msg_count NUMBER;
782 l_msg_data VARCHAR2(2000);
783 l_return_status VARCHAR2(1);
784 l_supplier_id NUMBER;
785 l_requested_by_party_id NUMBER;
786 l_media_type_code VARCHAR2(30);
787 l_frequency_code VARCHAR2(30);
788 l_subscription_type_code VARCHAR2(30);
789 l_status_code VARCHAR2(30);
790 l_party_name VARCHAR2(80);
791 l_subscription_tbl AHL_DI_SUBSCRIPTION_PVT.subscription_tbl;
792 l_init_msg_list VARCHAR2(10) := FND_API.G_TRUE;
793
794 -- Enhancement #2205830: pbarman april 2003
795 l_check_quantity NUMBER;
796 -- Enhancement #2525108: pbarman april 2003
797 l_purchase_order_no VARCHAR2(20);
798
799 BEGIN
800 -- Standard Start of API savepoint
801 SAVEPOINT modify_subscription;
802 -- Check if API is called in debug mode. If yes, enable debug.
803 IF G_DEBUG='Y' THEN
804 AHL_DEBUG_PUB.enable_debug;
805
806 END IF;
807 -- Debug info.
808 IF G_DEBUG='Y' THEN
809 IF G_DEBUG='Y' THEN
810 AHL_DEBUG_PUB.debug( 'enter ahl_di_subscription_pub.Modify Subscription','+SUB+');
811
812 END IF;
813 END IF;
814 -- Standard call to check for call compatibility.
815 IF FND_API.to_boolean(l_init_msg_list)
816 THEN
817 FND_MSG_PUB.initialize;
818 END IF;
819 -- Initialize API return status to success
820 x_return_status := 'S';
821 -- Initialize message list if p_init_msg_list is set to TRUE.
822 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
823 p_api_version,
824 l_api_name,G_PKG_NAME)
825 THEN
826 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
827 END IF;
828 --Start of API Body
829 IF p_x_subscription_tbl.count > 0
830 THEN
831 FOR i IN p_x_subscription_tbl.FIRST..p_x_subscription_tbl.LAST
832 LOOP
833 -- For Requested By...
834 -- Party Name is present
835 IF (p_x_subscription_tbl(i).requested_by_pty_name IS NOT NULL)
836 THEN
837
838 IF ahl_di_doc_index_pvt.get_product_install_status('PER') in ('N','L') THEN
839 -- Use cursor to retrieve party id using party name: party id will be unique for given party name
840 OPEN for_party_name(p_x_subscription_tbl(i).requested_by_pty_name);
841 FETCH for_party_name INTO l_requested_by_party_id;
842 IF for_party_name%FOUND THEN
843 l_subscription_tbl(i).requested_by_party_id := l_requested_by_party_id;
844 ELSE
845 FND_MESSAGE.SET_NAME('AHL','AHL_DI_REQ_PTY_ID_NOT_EXISTS');
846 FND_MSG_PUB.ADD;
847 END IF;
848 CLOSE for_party_name;
849
850 ELSIF ahl_di_doc_index_pvt.get_product_install_status('PER') in ('I','S') THEN
851 -- If party id and name are present, retrieve party id using both
852 OPEN get_party_name_id (p_x_subscription_tbl(i).requested_by_pty_name, p_x_subscription_tbl(i).requested_by_party_id);
853 FETCH get_party_name_id INTO l_requested_by_party_id;
854 -- If 1 record retrieved then party id and name match, use party id
855 p_x_subscription_tbl(i).requested_by_party_id := l_requested_by_party_id;
856 -- If no records, then party name has been changed
857 IF get_party_name_id%NOTFOUND THEN
858 -- Retrieve party id using party name
859 OPEN get_party_name (p_x_subscription_tbl(i).requested_by_pty_name);
860 LOOP
861 FETCH get_party_name INTO l_requested_by_party_id;
862 EXIT WHEN get_party_name%NOTFOUND;
863 END LOOP;
864 -- If no records for name, then name is INVALID: show error
865 IF get_party_name%ROWCOUNT = 0 THEN
866
867 -- FND_MESSAGE.SET_NAME('AHL','AHL_DI_REQ_PTY_ID_NOT_EXISTS');
868 FND_MESSAGE.SET_NAME('AHL','AHL_DI_REQ_ID_NOT_EXISTS');
869 FND_MESSAGE.SET_TOKEN('REQ',p_x_subscription_tbl(i).requested_by_pty_name);
870 FND_MSG_PUB.ADD;
871
872
873 -- If only 1 record for name, use the id: id will be unique: use id
874 ELSIF get_party_name%ROWCOUNT = 1 THEN
875 p_x_subscription_tbl(i).requested_by_party_id := l_requested_by_party_id;
876 -- If more than 1 record, then error: id is not unique: ask user to choose from LOV
877 ELSIF p_x_subscription_tbl(i).requested_by_party_id IS NULL
878 THEN
879 -- FND_MESSAGE.SET_NAME('AHL','AHL_DI_edit');
880 FND_MESSAGE.SET_NAME('AHL','AHL_DI_REQUESTED_BY_USE_LOV');
881 FND_MSG_PUB.ADD;
882 END IF;
883 CLOSE get_party_name;
884 END IF;
885 CLOSE get_party_name_id;
886
887 END IF;
888
889 -- Party Name is not present: since id is mandatory: show error
890 ELSE
891 IF p_x_subscription_tbl(i).delete_flag = 'N'
892 THEN
893 FND_MESSAGE.SET_NAME('AHL','AHL_DI_REQ_PTY_ID_NULL');
894 FND_MSG_PUB.ADD;
895 END IF;
896 END IF;
897
898 -- For Subscribed From...
899 -- Party Name is available
900
901 IF (p_x_subscription_tbl(i).subscribed_frm_pty_name IS NOT NULL)
902 THEN
903 IF ahl_di_doc_index_pvt.get_product_install_status('PO') IN ('N','L') THEN
904 OPEN for_party_name(p_x_subscription_tbl(i).subscribed_frm_pty_name);
905 FETCH for_party_name INTO l_supplier_id;
906 IF for_party_name%FOUND
907 THEN
908 l_subscription_tbl(i).subscribed_frm_party_id := l_supplier_id;
909 ELSE
910 FND_MESSAGE.SET_NAME('AHL','AHL_DI_SUB_PTY_ID_INVALID');
911 FND_MSG_PUB.ADD;
912 END IF;
913 CLOSE for_party_name;
914
915 ELSIF ahl_di_doc_index_pvt.get_product_install_status('PO') IN ('I','S') THEN
916 OPEN for_vendor_id(p_x_subscription_tbl(i).subscribed_frm_pty_name);
917 FETCH for_vendor_id INTO l_supplier_id;
918 IF for_vendor_id%FOUND
919 THEN
920 l_subscription_tbl(i).subscribed_frm_party_id := l_supplier_id;
921 ELSE
922 --FND_MESSAGE.SET_NAME('AHL','AHL_DI_SUB_PTY_ID_INVALID');
923 FND_MESSAGE.SET_NAME('AHL','AHL_DI_SUB_ID_INVALID');
924 FND_MESSAGE.SET_TOKEN('SUP',p_x_subscription_tbl(i).subscribed_frm_pty_name);
925
926
927 FND_MSG_PUB.ADD;
928 END IF;
929 CLOSE for_vendor_id;
930
931 END IF;
932
933 -- Enhancement : #2034767 : Party Name is mandatory. So Throw error message. : pbarman april 2003
934 ELSE
935 --l_subscription_tbl(i).subscribed_frm_party_id := null;
936 IF p_x_subscription_tbl(i).delete_flag = 'N'
937 THEN
938 FND_MESSAGE.SET_NAME('AHL','AHL_DI_SUBSCRIPTION_REQD');
939 IF p_x_subscription_tbl(i).requested_by_pty_name IS NOT NULL THEN
940 FND_MESSAGE.SET_TOKEN('FIELD1',p_x_subscription_tbl(i).requested_by_pty_name);
941 ELSE
942 FND_MESSAGE.SET_TOKEN('FIELD1', '');
943 END IF;
944
945 FND_MSG_PUB.ADD;
946 END IF;
947 END IF;
948
949 --For Media Type Code
950 IF p_x_subscription_tbl(i).media_type_desc IS NOT NULL
951 THEN
952 --
953 Check_lookup_name_Or_Id (
954 p_lookup_type => 'AHL_MEDIA_TYPE',
955 p_lookup_code => null,
956 p_meaning => p_x_subscription_tbl(i).media_type_desc,
957 p_check_id_flag => 'Y',
958 x_lookup_code => l_subscription_tbl(i).media_type_code,
959 x_return_status => l_return_status);
960
961 IF nvl(l_return_status, 'X') <> 'S'
962 THEN
963 FND_MESSAGE.SET_NAME('AHL','AHL_DI_MEDTYP_CODE_NOT_EXISTS');
964 FND_MSG_PUB.ADD;
965 END IF;
966 END IF;
967 -- If id is present
968 l_subscription_tbl(i).media_type_code := p_x_subscription_tbl(i).media_type_code;
969
970 --For Subscription Type Code
971 IF p_x_subscription_tbl(i).subscription_type_desc IS NOT NULL
972 THEN
973 Check_lookup_name_Or_Id (
974 p_lookup_type => 'AHL_SUBSCRIPTION_TYPE',
975 p_lookup_code => null,
976 p_meaning => p_x_subscription_tbl(i).subscription_type_desc,
977 p_check_id_flag => 'Y',
978 x_lookup_code => l_subscription_tbl(i).subscription_type_code,
979 x_return_status => l_return_status);
980
981 IF nvl(l_return_status, 'X') <> 'S'
982 THEN
983 FND_MESSAGE.SET_NAME('AHL','AHL_DI_SUBTYP_CODE_NOT_EXISTS');
984 FND_MSG_PUB.ADD;
985 END IF;
986 END IF;
987 -- Subscription type code is available
988 l_subscription_tbl(i).subscription_type_code := p_x_subscription_tbl(i).subscription_type_code;
989
990 --For Frequency Code
991 IF p_x_subscription_tbl(i).frequency_desc IS NOT NULL
992 THEN
993
994 Check_lookup_name_Or_Id (
995 p_lookup_type => 'AHL_FREQUENCY_TYPE',
996 p_lookup_code => null,
997 p_meaning => p_x_subscription_tbl(i).frequency_desc,
998 p_check_id_flag => 'Y',
999 x_lookup_code => l_subscription_tbl(i).frequency_code,
1000 x_return_status => l_return_status);
1001
1002 IF nvl(l_return_status, 'X') <> 'S'
1003 THEN
1004 FND_MESSAGE.SET_NAME('AHL','AHL_DI_FREQCY_CODE_NOT_EXISTS');
1005 FND_MSG_PUB.ADD;
1006 END IF;
1007 END IF;
1008 -- Frequency Code present
1009 l_subscription_tbl(i).frequency_code := p_x_subscription_tbl(i).frequency_code;
1010
1011 --For Status Code
1012 IF p_x_subscription_tbl(i).status_desc IS NOT NULL
1013 THEN
1014
1015 Check_lookup_name_Or_Id (
1016 p_lookup_type => 'AHL_SUBSCRIBE_STATUS_TYPE',
1017 p_lookup_code => null,
1018 p_meaning => p_x_subscription_tbl(i).status_desc,
1019 p_check_id_flag => 'Y',
1020 x_lookup_code => l_subscription_tbl(i).status_code,
1021 x_return_status => l_return_status);
1022
1023 IF nvl(l_return_status, 'X') <> 'S'
1024 THEN
1025 FND_MESSAGE.SET_NAME('AHL','AHL_DI_STATUS_CODE_NOT_EXISTS');
1026 FND_MSG_PUB.ADD;
1027 END IF;
1028 END IF;
1029 -- If Status Code is Avialable
1030 l_subscription_tbl(i).status_code := p_x_subscription_tbl(i).status_code;
1031 --
1032 -- Enhancement #2205830 :If quantity is non integral. pbarman march 2003
1033
1034 IF p_x_subscription_tbl(i).quantity IS NOT NULL
1035 THEN
1036 l_check_quantity := p_x_subscription_tbl(i).quantity;
1037 IF l_check_quantity > TRUNC(l_check_quantity,0)
1038 THEN
1039 FND_MESSAGE.SET_NAME('AHL','AHL_DI_QTY_NON_INT');
1040 FND_MSG_PUB.ADD;
1041 END IF;
1042 END IF;
1043
1044 -- enhancement : #2525108 : check PO Number against PO Numbers in PO_PURCHASE_ORDER_V: pbarman april 2003
1045
1046 IF p_x_subscription_tbl(i).purchase_order_no IS NOT NULL
1047 THEN
1048
1049 IF ahl_di_doc_index_pvt.get_product_install_status('PO') in ('I','S')THEN
1050
1051
1052 OPEN for_ponumber_id(p_x_subscription_tbl(i).purchase_order_no);
1053 FETCH for_ponumber_id INTO l_purchase_order_no;
1054 IF for_ponumber_id%FOUND THEN
1055 l_subscription_tbl(i).purchase_order_no := l_purchase_order_no;
1056
1057 ELSE
1058 IF p_x_subscription_tbl(i).delete_flag = 'N'
1059 THEN
1060 FND_MESSAGE.SET_NAME('AHL','AHL_DI_PO_NUM_NOT_EXISTS');
1061 FND_MSG_PUB.ADD;
1062 END IF;
1063 END IF;
1064 CLOSE for_ponumber_id;
1065
1066 ELSIF ahl_di_doc_index_pvt.get_product_install_status('PO') in ('N','L')THEN
1067
1068 l_subscription_tbl(i).purchase_order_no := p_x_subscription_tbl(i).purchase_order_no;
1069 END IF;
1070 END IF;
1071
1072 l_subscription_tbl(i).subscription_id := p_x_subscription_tbl(i).subscription_id;
1073 l_subscription_tbl(i).requested_by_party_id := p_x_subscription_tbl(i).requested_by_party_id;
1074 l_subscription_tbl(i).document_id := p_x_subscription_tbl(i).document_id;
1075 l_subscription_tbl(i).quantity := p_x_subscription_tbl(i).quantity;
1076 l_subscription_tbl(i).start_date := p_x_subscription_tbl(i).start_date;
1077 l_subscription_tbl(i).end_date := p_x_subscription_tbl(i).end_date;
1078 --l_subscription_tbl(i).purchase_order_no := p_x_subscription_tbl(i).purchase_order_no;
1079 l_subscription_tbl(i).attribute_category := p_x_subscription_tbl(i).attribute_category;
1080 l_subscription_tbl(i).attribute1 := p_x_subscription_tbl(i).attribute1;
1081 l_subscription_tbl(i).attribute2 := p_x_subscription_tbl(i).attribute2;
1082 l_subscription_tbl(i).attribute3 := p_x_subscription_tbl(i).attribute3;
1083 l_subscription_tbl(i).attribute4 := p_x_subscription_tbl(i).attribute4;
1084 l_subscription_tbl(i).attribute5 := p_x_subscription_tbl(i).attribute5;
1085 l_subscription_tbl(i).attribute6 := p_x_subscription_tbl(i).attribute6;
1086 l_subscription_tbl(i).attribute7 := p_x_subscription_tbl(i).attribute7;
1087 l_subscription_tbl(i).attribute8 := p_x_subscription_tbl(i).attribute8;
1088 l_subscription_tbl(i).attribute9 := p_x_subscription_tbl(i).attribute9;
1089 l_subscription_tbl(i).attribute10 := p_x_subscription_tbl(i).attribute10;
1090 l_subscription_tbl(i).attribute11 := p_x_subscription_tbl(i).attribute11;
1091 l_subscription_tbl(i).attribute12 := p_x_subscription_tbl(i).attribute12;
1092 l_subscription_tbl(i).attribute13 := p_x_subscription_tbl(i).attribute13;
1093 l_subscription_tbl(i).attribute14 := p_x_subscription_tbl(i).attribute14;
1094 l_subscription_tbl(i).attribute15 := p_x_subscription_tbl(i).attribute15;
1095 l_subscription_tbl(i).delete_flag := p_x_subscription_tbl(i).delete_flag;
1096 l_subscription_tbl(i).object_version_number := p_x_subscription_tbl(i).object_version_number;
1097 l_subscription_tbl(i).source_lang := p_x_subscription_tbl(i).source_lang;
1098 l_subscription_tbl(i).language := p_x_subscription_tbl(i).language;
1099 l_subscription_tbl(i).comments := p_x_subscription_tbl(i).comments;
1100
1101 END LOOP;
1102 END IF;
1103
1104
1105 /*------------------------------------------------------------------------*/
1106 /* procedure name: AHL_DI_SUBSCRIPTION_CUHK.MODIFY_SUBSCRIPTION_PRE */
1107 /* AHL_DI_SUBSCRIPTION_VUHK.MODIFY_SUBSCRIPTION_PRE */
1108 /* description : Added by siddhartha to call User Hooks */
1109 /* Date : Dec 20 2001 */
1110 /*------------------------------------------------------------------------*/
1111
1112 IF JTF_USR_HKS.Ok_to_Execute( 'AHL_DI_SUBSCRIPTION_PUB','MODIFY_SUBSCRIPTION',
1113 'B', 'C' ) then
1114 IF G_DEBUG='Y' THEN
1115 AHL_DEBUG_PUB.debug( 'Start AHL_DI_SUBSCRIPTION_CUHK.MODIFY_SUBSCRIPTION_Pre');
1116
1117 END IF;
1118 AHL_DI_SUBSCRIPTION_CUHK.MODIFY_SUBSCRIPTION_Pre(
1119 P_X_SUBSCRIPTION_TBL => l_subscription_tbl,
1120 X_RETURN_STATUS => l_return_status ,
1121 X_MSG_COUNT => l_msg_count ,
1122 X_MSG_DATA => l_msg_data );
1123
1124
1125 IF G_DEBUG='Y' THEN
1126 AHL_DEBUG_PUB.debug( 'End AHL_DI_SUBSCRIPTION_CUHK.MODIFY_SUBSCRIPTION_Pre');
1127
1128 END IF;
1129
1130 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1131 RAISE FND_API.G_EXC_ERROR;
1132 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1133 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1134 END IF;
1135 END IF;
1136
1137 IF JTF_USR_HKS.Ok_to_Execute( 'AHL_DI_SUBSCRIPTION_PUB','MODIFY_SUBSCRIPTION',
1138 'B', 'V' ) then
1139 IF G_DEBUG='Y' THEN
1140 AHL_DEBUG_PUB.debug( 'Start AHL_DI_SUBSCRIPTION_VUHK.MODIFY_SUBSCRIPTION_Pre');
1141
1142 END IF;
1143
1144 AHL_DI_SUBSCRIPTION_VUHK.MODIFY_SUBSCRIPTION_Pre(
1145 P_X_SUBSCRIPTION_TBL => l_subscription_tbl,
1146 X_RETURN_STATUS => l_return_status ,
1147 X_MSG_COUNT => l_msg_count ,
1148 X_MSG_DATA => l_msg_data );
1149
1150 IF G_DEBUG='Y' THEN
1151 AHL_DEBUG_PUB.debug( 'End AHL_DI_SUBSCRIPTION_VUHK.MODIFY_SUBSCRIPTION_Pre');
1152
1153 END IF;
1154
1155 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1156 RAISE FND_API.G_EXC_ERROR;
1157 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1158 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1159 END IF;
1160 END IF;
1161 /*---------------------------------------------------------*/
1162 /* End ; Date : Dec 20 2001 */
1163 /*---------------------------------------------------------*/
1164
1165 -- Standard call to get message count and if count is get message info.
1166 l_msg_count := FND_MSG_PUB.count_msg;
1167 --
1168 IF l_msg_count > 0 THEN
1169 X_msg_count := l_msg_count;
1170 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1171 THEN
1172 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1173 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1174 ELSE
1175 X_return_status := FND_API.G_RET_STS_ERROR;
1176 RAISE FND_API.G_EXC_ERROR;
1177 END IF;
1178 END IF;
1179
1180
1181 -- Call the Private API
1182 AHL_DI_SUBSCRIPTION_PVT.MODIFY_SUBSCRIPTION
1183 (
1184 p_api_version => 1.0 ,
1185 p_init_msg_list => l_init_msg_list ,
1186 -- Modified pjha 15-May-2002 for modifying 'subscribed to' Begin
1187 --p_commit => p_commit ,
1188 p_commit => FND_API.G_FALSE ,
1189 -- Modified pjha 15-May-2002 for modifying 'subscribed to' End
1190 p_validate_only => p_validate_only ,
1191 p_validation_level => p_validation_level ,
1192 p_x_subscription_tbl => l_subscription_tbl ,
1193 x_return_status => l_return_status ,
1194 x_msg_count => l_msg_count ,
1195 x_msg_data => l_msg_data
1196 );
1197
1198 -- Standard call to get message count and if count is get message info.
1199 l_msg_count := FND_MSG_PUB.count_msg;
1200 --
1201 IF l_msg_count > 0 THEN
1202 X_msg_count := l_msg_count;
1203 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1204 THEN
1205 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1206 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1207 ELSE
1208 X_return_status := FND_API.G_RET_STS_ERROR;
1209 RAISE FND_API.G_EXC_ERROR;
1210 END IF;
1211 END IF;
1212
1213 /*------------------------------------------------------------------------*/
1214 /* procedure name: AHL_DI_SUBSCRIPTION_VUHK.MODIFY_SUBSCRIPTION_Post */
1215 /* AHL_DI_SUBSCRIPTION_CUHK.MODIFY_SUBSCRIPTION_Post */
1216 /* description : Added by siddhartha to call User Hooks */
1217 /* Date : Dec 20 2001 */
1218 /*------------------------------------------------------------------------*/
1219
1220
1221 IF JTF_USR_HKS.Ok_to_Execute( 'AHL_DI_SUBSCRIPTION_PUB','MODIFY_SUBSCRIPTION',
1222 'A', 'V' ) then
1223 IF G_DEBUG='Y' THEN
1224 AHL_DEBUG_PUB.debug( 'Start AHL_DI_SUBSCRIPTION_VUHK.MODIFY_SUBSCRIPTION_Post');
1225
1226 END IF;
1227
1228 AHL_DI_SUBSCRIPTION_VUHK.MODIFY_SUBSCRIPTION_Post(
1229 P_SUBSCRIPTION_TBL => l_subscription_tbl,
1230 X_RETURN_STATUS => l_return_status ,
1231 X_MSG_COUNT => l_msg_count ,
1232 X_MSG_DATA => l_msg_data );
1233
1234 IF G_DEBUG='Y' THEN
1235 AHL_DEBUG_PUB.debug( 'End AHL_DI_SUBSCRIPTION_VUHK.MODIFY_SUBSCRIPTION_Post');
1236
1237 END IF;
1238
1239 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1240 RAISE FND_API.G_EXC_ERROR;
1241 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1242 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1243 END IF;
1244 END IF;
1245
1246 IF JTF_USR_HKS.Ok_to_Execute( 'AHL_DI_SUBSCRIPTION_PUB','MODIFY_SUBSCRIPTION',
1247 'A', 'C' ) then
1248 IF G_DEBUG='Y' THEN
1249 AHL_DEBUG_PUB.debug( 'Start AHL_DI_SUBSCRIPTION_CUHK.MODIFY_SUBSCRIPTION_Post');
1250
1251 END IF;
1252 AHL_DI_SUBSCRIPTION_CUHK.MODIFY_SUBSCRIPTION_Post(
1253 P_SUBSCRIPTION_TBL => l_subscription_tbl,
1254 X_RETURN_STATUS => l_return_status ,
1255 X_MSG_COUNT => l_msg_count ,
1256 X_MSG_DATA => l_msg_data );
1257
1258 IF G_DEBUG='Y' THEN
1259 AHL_DEBUG_PUB.debug( 'End AHL_DI_SUBSCRIPTION_CUHK.MODIFY_SUBSCRIPTION_Post');
1260
1261 END IF;
1262
1263 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1264 RAISE FND_API.G_EXC_ERROR;
1265 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1266 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1267 END IF;
1268 END IF;
1269
1270
1271
1272 /*---------------------------------------------------------*/
1273 /* End ; Date : Dec 20 2001 */
1274 /*---------------------------------------------------------*/
1275
1276 -- Standard check of p_commit.
1277 IF FND_API.TO_BOOLEAN(p_commit) THEN
1278 COMMIT;
1279 END IF;
1280
1281
1282 -- Debug info
1283 IF G_DEBUG='Y' THEN
1284 AHL_DEBUG_PUB.debug( 'End of public api Modify Subscription','+SUB+');
1285
1286 END IF;
1287 -- Check if API is called in debug mode. If yes, disable debug.
1288 IF G_DEBUG='Y' THEN
1289 AHL_DEBUG_PUB.disable_debug;
1290
1291 END IF;
1292
1293 EXCEPTION
1294 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1295 ROLLBACK TO modify_subscription;
1296 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1297 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1298 p_count => x_msg_count,
1299 p_data => x_msg_data);
1300 IF G_DEBUG='Y' THEN
1301 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'ERROR' );
1302 AHL_DEBUG_PUB.debug( 'ahl_di_subscription_pub.Modify Subscription','+SUB+');
1303
1304
1305 -- Check if API is called in debug mode. If yes, disable debug.
1306 AHL_DEBUG_PUB.disable_debug;
1307
1308 END IF;
1309
1310 WHEN FND_API.G_EXC_ERROR THEN
1311 ROLLBACK TO modify_subscription;
1312 X_return_status := FND_API.G_RET_STS_ERROR;
1313 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1314 p_count => x_msg_count,
1315 p_data => X_msg_data);
1316 -- Debug info.
1317 IF G_DEBUG='Y' THEN
1318 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
1319 AHL_DEBUG_PUB.debug( 'ahl_di_subscription_pub.Modify Subscription','+SUB+');
1320
1321
1322 -- Check if API is called in debug mode. If yes, disable debug.
1323 AHL_DEBUG_PUB.disable_debug;
1324
1325 END IF;
1326
1327 WHEN OTHERS THEN
1328 ROLLBACK TO modify_subscription;
1329 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1330 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1331 THEN
1332 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_DI_SUBSCRIPTION_PUB',
1333 p_procedure_name => 'MODIFY_SUBSCRIPTION',
1334 p_error_text => SUBSTR(SQLERRM,1,240));
1335 END IF;
1336 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1337 p_count => x_msg_count,
1338 p_data => X_msg_data);
1339
1340 -- Debug info.
1341 IF G_DEBUG='Y' THEN
1342 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'SQL ERROR' );
1343 AHL_DEBUG_PUB.debug( 'ahl_di_subscription_pub.Modify Subscription','+SUB+');
1344
1345
1346 -- Check if API is called in debug mode. If yes, disable debug.
1347 AHL_DEBUG_PUB.disable_debug;
1348
1349 END IF;
1350
1351 END MODIFY_SUBSCRIPTION;
1352 --
1353 END AHL_DI_SUBSCRIPTION_PUB;
[Home] [Help]