1 PACKAGE BODY AHL_DI_SUBSCRIPTION_PVT AS
2 /* $Header: AHLVSUBB.pls 120.2 2006/02/07 03:49:26 sagarwal noship $ */
3 --
4 G_PKG_NAME VARCHAR2(30) := 'AHL_DI_SUBSCRIPTION_PVT';
5 --
6
7 -- Validates the Subscriptions Info
8 /*---------------------------------------------------------*/
9 /* procedure name: validate_subscription(private procedure)*/
10 /* description : Validation checks for before inserting */
11 /* new record as well before modification */
12 /* takes place */
13 /*---------------------------------------------------------*/
14
15 G_DEBUG VARCHAR2(1):=AHL_DEBUG_PUB.is_log_enabled;
16 PROCEDURE VALIDATE_SUBSCRIPTION
17 (
18 P_SUBSCRIPTION_ID IN NUMBER ,
19 P_DOCUMENT_ID IN NUMBER ,
20 P_STATUS_CODE IN VARCHAR2 ,
21 P_REQUESTED_BY_PARTY_ID IN NUMBER ,
22 P_QUANTITY IN NUMBER ,
23 P_FREQUENCY_CODE IN VARCHAR2 ,
24 P_SUBSCRIBED_FRM_PARTY_ID IN NUMBER ,
25 P_START_DATE IN DATE ,
26 P_END_DATE IN DATE ,
27 P_MEDIA_TYPE_CODE IN VARCHAR2 ,
28 P_SUBSCRIPTION_TYPE_CODE IN VARCHAR2 ,
29 P_PURCHASE_ORDER_NO IN VARCHAR2 ,
30 P_DELETE_FLAG IN VARCHAR2 := 'N')
31 IS
32
33 -- Cursor to retrieve the status code from fnd lookups table
34 CURSOR get_status_code(c_status_code VARCHAR2)
35 IS
36 SELECT lookup_code
37 FROM FND_LOOKUP_VALUES_VL
38 WHERE lookup_code = c_status_code
39 AND lookup_type = 'AHL_SUBSCRIBE_STATUS_TYPE'
40 AND sysdate between start_date_active
41 AND nvl(end_date_active,sysdate);
42 -- Cursor to retrieve the frequency from fnd lookups
43 CURSOR get_frequency_code(c_frequency_code VARCHAR2)
44 IS
45 SELECT lookup_code
46 FROM FND_LOOKUP_VALUES_VL
47 WHERE lookup_code = c_frequency_code
48 AND lookup_type = 'AHL_FREQUENCY_TYPE'
49 AND sysdate between start_date_active
50 AND nvl(end_date_active,sysdate);
51 -- Cursor to retrieve the sub type code from fnd lookups table
52 CURSOR get_sub_type_code(c_subscription_type_code VARCHAR2)
53 IS
54 SELECT lookup_code
55 FROM FND_LOOKUP_VALUES_VL
56 WHERE lookup_code = c_subscription_type_code
57 AND lookup_type = 'AHL_SUBSCRIPTION_TYPE'
58 AND sysdate between start_date_active
59 AND nvl(end_date_active,sysdate);
60 -- Cursor to retrieve the media type code from fnd lookups
61 CURSOR get_media_type_code(c_media_type_code VARCHAR2)
62 IS
63 SELECT lookup_code
64 FROM FND_LOOKUP_VALUES_VL
65 WHERE lookup_code = c_media_type_code
66 AND lookup_type = 'AHL_MEDIA_TYPE'
67 AND sysdate between start_date_active
68 AND nvl(end_date_active,sysdate);
69
70 --Cursor is used to check the record exists in supplier documents
71 --Modified pjha 05-Jul-2002 for bug# 2448536: validating supplier: Begin
72 /*
73 CURSOR subc_from_pty_info(c_subscribed_frm_pty_id IN NUMBER)
74 IS
75 SELECT 'x'
76 FROM AHL_SUPPLIER_DOCUMENTS S
77 WHERE S.supplier_id = c_subscribed_frm_pty_id;
78 */
79 CURSOR subc_from_pty_info(c_subscribed_frm_pty_id IN NUMBER,
80 c_document_id IN NUMBER)
81 IS
82 SELECT 'x'
83 FROM AHL_SUPPLIER_DOCUMENTS S
84 WHERE S.supplier_id = c_subscribed_frm_pty_id
85 AND S.document_id = c_document_id;
86 --Modified pjha 05-Jul-2002 for bug# 2448536: validating supplier: End
87
88 -- Used to validate the document id
89 CURSOR check_doc_info(c_document_id NUMBER)
90 IS
91 SELECT 'X'
92 FROM AHL_DOCUMENTS_B
93 WHERE document_id = c_document_id;
94 -- Cursor to retrieve the exisiting subscription record from base table
95 CURSOR get_sub_rec_b_info (c_subscription_id NUMBER)
96 IS
97 SELECT document_id,
98 status_code,
99 requested_by_party_id,
100 quantity,
101 frequency_code,
102 subscribed_frm_party_id,
103 start_date,
104 end_date,
105 media_type_code
106 FROM AHL_SUBSCRIPTIONS_B
107 WHERE subscription_id = c_subscription_id;
108 -- Cursor is used to check for duplicate record
109 CURSOR dup_rec(c_document_id NUMBER,
110 c_requested_by_party_id NUMBER)
111 IS
112 SELECT 'X'
113 FROM AHL_SUBSCRIPTIONS_B
114 WHERE document_id = c_document_id
115 AND requested_by_party_id = c_requested_by_party_id;
116 --
117
118 -- Cursor is used to get requested by name given requested id
119 -- Perf Fixes for 4919023
120 /*
121 CURSOR get_requested_name(c_requested_by_party_id NUMBER)
122 IS
123 SELECT party_name
124 FROM ahl_hz_per_employees_v
125 WHERE party_id= c_requested_by_party_id;
126 */
127
128 CURSOR get_requested_name_hz(c_requested_by_party_id NUMBER)
129 IS
130 SELECT PARTY_NAME
131 FROM HZ_PARTIES
132 WHERE party_id= c_requested_by_party_id
133 AND PARTY_TYPE ='PERSON';
134
135 CURSOR get_requested_name_ppf(c_requested_by_party_id NUMBER)
136 IS
137 SELECT PPF.FULL_NAME
138 FROM PER_PEOPLE_F PPF, PER_PERSON_TYPES PPT
139 WHERE PPF.PERSON_ID= c_requested_by_party_id
140 AND TRUNC(SYSDATE) BETWEEN PPF.EFFECTIVE_START_DATE AND PPF.EFFECTIVE_END_DATE
141 AND NVL(PPF.CURRENT_EMPLOYEE_FLAG, 'X') = 'Y'
142 AND PPF.PERSON_TYPE_ID = PPT.PERSON_TYPE_ID
143 AND PPT.SYSTEM_PERSON_TYPE ='EMP';
144
145 -- enhancement #2525108: lov for PO Number : pbarman april 2003
146 -- Get Puchase Order Number from PO_PURCHASE_ORDER_V
147 CURSOR for_ponumber_id(c_ponumber IN VARCHAR2)
148 IS
149 SELECT distinct segment1
150 FROM PO_HEADERS_V
151 WHERE nvl(approved_flag, 'N')='Y' and upper(segment1) = upper(c_ponumber);
152 --
153
154 l_requested_by_name VARCHAR2(301) := NULL;
155 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_SUBSCRIPTION';
156 l_api_version CONSTANT NUMBER := 1.0;
157 l_dummy VARCHAR2(2000);
158 l_subscription_id NUMBER;
159 l_document_id NUMBER;
160 l_status_code VARCHAR2(30);
161 l_requested_by_party_id NUMBER;
162 l_quantity NUMBER;
163 l_frequency_code VARCHAR2(30);
164 l_subscription_frm_party_id NUMBER;
165 l_start_date DATE;
166 l_end_date DATE;
167 l_subscription_type_code VARCHAR2(30);
168 l_media_type_code VARCHAR2(30);
169 l_purchase_order_no VARCHAR2(20);
170 l_prod_install_status VARCHAR2(30);
171
172 --
173 BEGIN
174 -- When the delte flag is 'YES' means either insert or update
175 --Enhancement nos #2034767 and #2205830: pbarman : April 2003
176 IF (NVL(p_delete_flag, 'N') = 'N' )
177 THEN
178 IF G_DEBUG='Y' THEN
179 AHL_DEBUG_PUB.enable_debug;
180 END IF;
181
182 -- Perf Fixes for 4919023
183 BEGIN
184 IF G_DEBUG='Y' THEN
185 AHL_DEBUG_PUB.debug( 'Fetching Installation Status of PER','+SUB+');
186 END IF;
187 SELECT AHL_DI_DOC_INDEX_PVT.GET_PRODUCT_INSTALL_STATUS('PER')
188 INTO l_prod_install_status
189 FROM DUAL;
190 END;
191
192 -- Debug info.
193 IF G_DEBUG='Y' THEN
194 AHL_DEBUG_PUB.debug( 'VALIDATION START');
195 END IF;
196 --When the process is update
197 IF p_subscription_id IS NOT NULL
198 THEN
199 OPEN get_sub_rec_b_info (p_subscription_id);
200 FETCH get_sub_rec_b_info INTO l_document_id,
201 l_status_code,
202 l_requested_by_party_id,
203 l_quantity,
204 l_frequency_code,
205 l_subscription_frm_party_id,
206 l_start_date,
207 l_end_date,
208 l_media_type_code;
209 CLOSE get_sub_rec_b_info;
210 END IF;
211 --
212 IF p_document_id IS NOT NULL
213 THEN
214 l_document_id := p_document_id;
215 END IF;
216 --
217 IF p_status_code IS NOT NULL
218 THEN
219 l_status_code := p_status_code;
220 END IF;
221 --
222 IF p_requested_by_party_id IS NOT NULL
223 THEN
224 l_requested_by_party_id := p_requested_by_party_id;
225 END IF;
226 --
227 IF p_quantity IS NOT NULL
228 THEN
229 l_quantity := p_quantity;
230 END IF;
231 --
232 IF p_frequency_code IS NOT NULL
233 THEN
234 l_frequency_code := p_frequency_code;
235 END IF;
236 --
237 IF p_start_date IS NOT NULL
238 THEN
239 l_start_date := p_start_date;
240 END IF;
241 --
242 IF p_end_date IS NOT NULL
243 THEN
244 l_end_date := p_end_date;
245 END IF;
246 --
247 IF p_media_type_code IS NOT NULL
248 THEN
249 l_media_type_code := p_media_type_code;
250 END IF;
251 --
252 IF p_subscribed_frm_party_id IS NOT NULL
253 THEN
254 l_subscription_frm_party_id := p_subscribed_frm_party_id;
255 END IF;
256
257 l_subscription_id := p_subscription_id;
258 -- This condition checks Document Id, when the action is insert or update
259 IF ((p_subscription_id IS NULL AND
260 p_document_id IS NULL) OR
261
262 (p_subscription_id IS NOT NULL
263 AND l_document_id IS NULL))
264
265 THEN
266 FND_MESSAGE.SET_NAME('AHL','AHL_DI_DOCUMENT_ID_NULL');
267 FND_MSG_PUB.ADD;
268 END IF;
269 -- This condition checks Status Code, When the action is insert or update
270 IF ((p_subscription_id IS NULL AND
271 p_status_code IS NULL) OR
272
273 (p_subscription_id IS NOT NULL
274 AND l_status_code IS NULL))
275 THEN
276 FND_MESSAGE.SET_NAME('AHL','AHL_DI_STATUS_CODE_NULL');
277 FND_MSG_PUB.ADD;
278 END IF;
279
280 -- Perf Fixes for 4919023
281 /*
282 OPEN get_requested_name(l_requested_by_party_id);
283 FETCH get_requested_name INTO l_requested_by_name;
284 CLOSE get_requested_name;
285 */
286
287 IF l_prod_install_status IN ('N','L') THEN
288 OPEN get_requested_name_hz(l_requested_by_party_id);
289 FETCH get_requested_name_hz INTO l_requested_by_name;
290 CLOSE get_requested_name_hz;
291 ELSIF l_prod_install_status IN ('I','S') THEN
292 OPEN get_requested_name_ppf(l_requested_by_party_id);
293 FETCH get_requested_name_ppf INTO l_requested_by_name;
294 CLOSE get_requested_name_ppf;
295 END IF;
296
297 -- This condition checks Quantity Field, When the action is insert or update
298 IF ((p_subscription_id IS NULL AND
299 p_quantity IS NULL) OR
300
301 (p_subscription_id IS NOT NULL
302 AND p_quantity IS NULL))
303 THEN
304 FND_MESSAGE.SET_NAME('AHL','AHL_DI_QUAN_REQID_NULL');
305 FND_MESSAGE.SET_TOKEN('REQ',l_requested_by_name);
306 FND_MSG_PUB.ADD;
307 END IF;
308
309
310 -- Enhancement #2525108: check PO Number against PO Numbers in PO_PURCHASE_ORDER_V: pbarman april 2003
311
312 IF p_purchase_order_no IS NOT NULL
313 THEN
314
315 IF ahl_di_doc_index_pvt.get_product_install_status('PO') in ('I','S')THEN
316 OPEN for_ponumber_id(p_purchase_order_no);
317 FETCH for_ponumber_id INTO l_purchase_order_no;
318 IF for_ponumber_id%NOTFOUND THEN
319 FND_MESSAGE.SET_NAME('AHL','AHL_DI_PO_NUM_NOT_EXISTS');
320 FND_MSG_PUB.ADD;
321 END IF;
322 CLOSE for_ponumber_id;
323 END IF;
324 END IF;
325 -- Checks for Valid Subscription from party id, Record should exist in supplier
326 -- documents table
327 --Modified pjha 12-Jul-2002 added end_date conditions to do the validation
328 --only in the case of active subscriptions: Begin
329 --IF (l_subscription_frm_party_id IS NOT NULL AND
330 -- l_subscription_frm_party_id IS NOT NULL)
331 IF (l_subscription_frm_party_id IS NOT NULL AND
332 (p_end_date IS NULL OR
333 p_end_date >= TRUNC(sysdate)))
334
335 --Modified pjha 12-Jul-2002 added end_date conditions to do the validation
336 --only in the case of active subscriptions: End
337 THEN
338 --Modified pjha 05-Jul-2002 for bug# 2448536: validating supplier: Begin
339 --OPEN subc_from_pty_info(l_subscription_frm_party_id);
340 OPEN subc_from_pty_info(l_subscription_frm_party_id,
341 l_document_id);
342 --Modified pjha 05-Jul-2002 for bug# 2448536: validating supplier: End
343 FETCH subc_from_pty_info INTO l_dummy;
344 IF subc_from_pty_info%NOTFOUND
345 THEN
346 FND_MESSAGE.SET_NAME('AHL','AHL_DI_SUB_PTY_ID_INVALID');
347 FND_MSG_PUB.ADD;
348 END IF;
349 CLOSE subc_from_pty_info;
350 END IF;
351 --Validates Requested by
352 IF ((p_subscription_id IS NULL AND
353 p_requested_by_party_id IS NULL)
354 OR
355
356 (p_subscription_id IS NOT NULL
357 AND l_requested_by_party_id IS NULL))
358 THEN
359
360 FND_MESSAGE.SET_NAME('AHL','AHL_DI_REQUES_BY_PARTY_ID_NULL');
361 FND_MSG_PUB.ADD;
362 END IF;
363 -- Validates the Quantity Field
364 IF p_quantity IS NOT NULL
365 OR
366 l_quantity IS NOT NULL
367 THEN
368 IF(p_quantity <= 0 or l_quantity <= 0) THEN
369 --FND_MESSAGE.SET_NAME('AHL','AHL_DI_QUANTITY_INVALID');
370
371 FND_MESSAGE.SET_NAME('AHL','AHL_DI_QUAN_REQID_NULL');
372 FND_MESSAGE.SET_TOKEN('REQ',l_requested_by_name);
373 FND_MSG_PUB.ADD;
374 END IF;
375 END IF;
376 -- Validations for start date
377 IF (p_start_date IS NOT NULL
378 AND p_end_date IS NOT NULL)
379 OR
380 (l_start_date IS NOT NULL
381 AND l_end_date IS NOT NULL)
382
383 THEN
384 --Modified pjha 12-Jun-2002 for date range check and for picking right message bug# 2314334 Begin
385 /*
386 IF(p_start_date > nvl(l_end_date, p_start_date)) OR
387 (l_start_date > nvl(l_end_date,l_start_date))
388 THEN
389 FND_MESSAGE.SET_NAME('AHL','AHL_DI_INVALID_DATE');
390 FND_MSG_PUB.ADD;
391 END IF;
392 */
393 IF(p_start_date >= nvl(l_end_date, p_start_date)) OR
394 (l_start_date >= nvl(l_end_date,l_start_date))
395 THEN
396 FND_MESSAGE.SET_NAME('AHL','AHL_DI_INVALID_DATE_RANGE');
397 FND_MSG_PUB.ADD;
398 END IF;
399
400 --Modified pjha 12-Jun-2002 for date range check and for picking right message bug# 2314334 End
401 END IF;
402 -- Checks for existence of status code in fnd lookups
403 IF p_status_code IS NOT NULL
404 THEN
405 OPEN get_status_code(p_status_code);
406 FETCH get_status_code INTO l_dummy;
407 IF get_status_code%NOTFOUND
408 THEN
409 FND_MESSAGE.SET_NAME('AHL','AHL_DI_STATUS_CODE_NOT_EXISTS');
410 FND_MSG_PUB.ADD;
411 END IF;
412 CLOSE get_status_code;
413 END IF;
414 -- This condition checks the document id exists in ahl documents table
415 IF p_document_id IS NOT NULL
416 THEN
417 OPEN Check_doc_info(p_document_id);
418 FETCH Check_doc_info INTO l_dummy;
419 IF Check_doc_info%NOTFOUND
420 THEN
421 FND_MESSAGE.SET_NAME('AHL','AHL_DI_DOCUMENT_ID_NOT_EXISTS');
422 FND_MSG_PUB.ADD;
423 END IF;
424 CLOSE Check_doc_info;
425 END IF;
426 -- Checks for existence of status code in fnd lookups
427 IF p_media_type_code IS NOT NULL
428 THEN
429 OPEN get_media_type_code(p_media_type_code);
430 FETCH get_media_type_code INTO l_dummy;
431 IF get_media_type_code%NOTFOUND
432 THEN
433 FND_MESSAGE.SET_NAME('AHL','AHL_DI_MEDTYP_CODE_NOT_EXISTS');
434 FND_MSG_PUB.ADD;
435 END IF;
436 CLOSE get_media_type_code;
437 END IF;
438 -- Checks for existence of subscription type code in fnd lookups
439 IF p_subscription_type_code IS NOT NULL
440 THEN
441 OPEN get_sub_type_code(p_subscription_type_code);
442 FETCH get_sub_type_code INTO l_dummy;
443 IF get_sub_type_code%NOTFOUND
444 THEN
445 FND_MESSAGE.SET_NAME('AHL','AHL_DI_SUBTYP_CODE_NOT_EXISTS');
446 FND_MSG_PUB.ADD;
447 END IF;
448 CLOSE get_sub_type_code;
449 END IF;
450 -- Checks for existence of frequency code in fnd lookups
451 IF p_frequency_code IS NOT NULL
452 THEN
453 OPEN get_frequency_code(p_frequency_code);
454 FETCH get_frequency_code INTO l_dummy;
455 IF get_frequency_code%NOTFOUND
456 THEN
457 FND_MESSAGE.SET_NAME('AHL','AHL_DI_FREQCY_CODE_NOT_EXISTS');
458 FND_MSG_PUB.ADD;
459 END IF;
460 CLOSE get_frequency_code;
461 END IF;
462 -- Checks for Duplicate Record, when inserting new subscription record
463 IF p_subscription_id IS NULL
464 THEN
465 OPEN dup_rec(l_document_id ,
466 l_requested_by_party_id);
467 FETCH dup_rec INTO l_dummy;
468 IF dup_rec%FOUND THEN
469 --FND_MESSAGE.SET_NAME('AHL','AHL_DI_SUBSC_DUP_RECORD');
470
471 /*
472 OPEN get_requested_name(l_requested_by_party_id);
473 FETCH get_requested_name INTO l_requested_by_name;
474 CLOSE get_requested_name;
475 */
476
477 FND_MESSAGE.SET_NAME('AHL','AHL_DI_SUBS_DUP_RECORD');
478 FND_MESSAGE.SET_TOKEN('REQID',l_requested_by_name);
479 FND_MSG_PUB.ADD;
480 END IF;
481 CLOSE dup_rec;
482 END IF;
483 END IF;
484
485 END VALIDATE_SUBSCRIPTION;
486 /*------------------------------------------------------*/
487 /* procedure name: create_subscription */
488 /* description : Creates new subscription record */
489 /* for an associated document */
490 /* */
491 /*------------------------------------------------------*/
492
493 PROCEDURE CREATE_SUBSCRIPTION
494 (
495 p_api_version IN NUMBER := 1.0 ,
496 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
497 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
498 p_validate_only IN VARCHAR2 := FND_API.G_TRUE ,
499 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
500 p_x_subscription_tbl IN OUT NOCOPY subscription_tbl ,
501 x_return_status OUT NOCOPY VARCHAR2 ,
502 x_msg_count OUT NOCOPY NUMBER ,
503 x_msg_data OUT NOCOPY VARCHAR2
504 )
505 IS
506 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_SUBSCRIPTION';
507 l_api_version CONSTANT NUMBER := 1.0;
508 l_num_rec NUMBER;
509 l_msg_count NUMBER;
510 l_rowid ROWID;
511 l_subscription_id NUMBER;
512 l_requested_by_party_id NUMBER;
513 l_subscription_info subscription_rec;
514 -- Added pjha 15-May-2002 for modifying 'subscribed to' Begin
515 l_subscribe_to_flag VARCHAR2(1);
516 -- Added pjha 15-May-2002 for modifying 'subscribed to' End
517 BEGIN
518 -- Standard Start of API savepoint
519 SAVEPOINT create_subscription;
520
521 -- Check if API is called in debug mode. If yes, enable debug.
522 IF G_DEBUG='Y' THEN
523 AHL_DEBUG_PUB.enable_debug;
524
525 END IF;
526 -- Debug info.
527 IF G_DEBUG='Y' THEN
528 IF G_DEBUG='Y' THEN
529 AHL_DEBUG_PUB.debug( 'enter ahl_di_subscription_pvt.Create Subscription','+SUB+');
530
531 END IF;
532 END IF;
533 -- Standard call to check for call compatibility.
534 IF FND_API.to_boolean(p_init_msg_list)
535 THEN
536 FND_MSG_PUB.initialize;
537 END IF;
538 -- Initialize API return status to success
539 x_return_status := FND_API.G_RET_STS_SUCCESS;
540 -- Initialize message list if p_init_msg_list is set to TRUE.
541 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
542 p_api_version,
543 l_api_name,G_PKG_NAME)
544 THEN
545 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
546 END IF;
547 --Start API Body
548 IF p_x_subscription_tbl.COUNT > 0
549 THEN
550 FOR i IN p_x_subscription_tbl.FIRST..p_x_subscription_tbl.LAST
551 LOOP
552 VALIDATE_SUBSCRIPTION
553 (
554 p_subscription_id => p_x_subscription_tbl(i).subscription_id,
555 p_document_id =>p_x_subscription_tbl(i).document_id,
556 p_status_code =>p_x_subscription_tbl(i).status_code,
557 p_requested_by_party_id =>p_x_subscription_tbl(i).requested_by_party_id,
558 p_quantity =>p_x_subscription_tbl(i).quantity,
559 p_frequency_code =>p_x_subscription_tbl(i).frequency_code,
560 p_subscribed_frm_party_id =>p_x_subscription_tbl(i).subscribed_frm_party_id,
561 p_start_date =>p_x_subscription_tbl(i).start_date,
562 p_end_date =>p_x_subscription_tbl(i).end_date,
563 p_media_type_code =>p_x_subscription_tbl(i).media_type_code,
564 p_subscription_type_code =>p_x_subscription_tbl(i).subscription_type_code,
565 p_purchase_order_no =>p_x_subscription_tbl(i).purchase_order_no,
566 p_delete_flag =>p_x_subscription_tbl(i).delete_flag
567 );
568 END LOOP;
569 -- Standard call to get message count and if count is get message info.
570 l_msg_count := FND_MSG_PUB.count_msg;
571
572 IF l_msg_count > 0 THEN
573 X_msg_count := l_msg_count;
574 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
575 RAISE FND_API.G_EXC_ERROR;
576 END IF;
577
578 FOR i IN p_x_subscription_tbl.FIRST..p_x_subscription_tbl.LAST
579 LOOP
580 IF (p_x_subscription_tbl(i).subscription_id IS NULL)
581 THEN
582 -- These conditions are required for optional fields, Frequency code
583 l_subscription_info.frequency_code := p_x_subscription_tbl(i).frequency_code;
584 -- Subscribed from party id
585 l_subscription_info.subscribed_frm_party_id := p_x_subscription_tbl(i).subscribed_frm_party_id;
586
587 -- Start Date
588 IF p_x_subscription_tbl(i).start_date IS NULL
589 THEN
590 --Modified pjha 03-Jul-2002 for making default start date: Begin
591 --l_subscription_info.start_date := null;
592 l_subscription_info.start_date := sysdate;
593 --Modified pjha 03-Jul-2002 for making default start date: End
594 ELSE
595 l_subscription_info.start_date := p_x_subscription_tbl(i).start_date;
596 END IF;
597 l_subscription_info.end_date := p_x_subscription_tbl(i).end_date;
598 l_subscription_info.purchase_order_no := p_x_subscription_tbl(i).purchase_order_no;
599 l_subscription_info.subscription_type_code := p_x_subscription_tbl(i).subscription_type_code;
600 l_subscription_info.media_type_code := p_x_subscription_tbl(i).media_type_code;
601 l_subscription_info.comments := p_x_subscription_tbl(i).comments;
602 l_subscription_info.attribute_category := p_x_subscription_tbl(i).attribute_category;
603 l_subscription_info.attribute1 := p_x_subscription_tbl(i).attribute1;
604 l_subscription_info.attribute2 := p_x_subscription_tbl(i).attribute2;
605 l_subscription_info.attribute3 := p_x_subscription_tbl(i).attribute3;
606 l_subscription_info.attribute4 := p_x_subscription_tbl(i).attribute4;
607 l_subscription_info.attribute5 := p_x_subscription_tbl(i).attribute5;
608 l_subscription_info.attribute6 := p_x_subscription_tbl(i).attribute6;
609 l_subscription_info.attribute7 := p_x_subscription_tbl(i).attribute7;
610 l_subscription_info.attribute8 := p_x_subscription_tbl(i).attribute8;
611 l_subscription_info.attribute9 := p_x_subscription_tbl(i).attribute9;
612 l_subscription_info.attribute10 := p_x_subscription_tbl(i).attribute10;
613 l_subscription_info.attribute11 := p_x_subscription_tbl(i).attribute11;
614 l_subscription_info.attribute12 := p_x_subscription_tbl(i).attribute12;
615 l_subscription_info.attribute13 := p_x_subscription_tbl(i).attribute13;
616 l_subscription_info.attribute14 := p_x_subscription_tbl(i).attribute14;
617 l_subscription_info.attribute15 := p_x_subscription_tbl(i).attribute15;
618 -- Retrive the subscription id from sequence
619 Select AHL_SUBSCRIPTIONS_B_S.Nextval Into
620 l_subscription_id from dual;
621 /*-------------------------------------------------------- */
622 /* procedure name: AHL_SUBSCRIPTIONS_PKG.INSERT_ROW */
623 /* description : Added by Senthil to call Table Handler */
624 /* Date : Dec 31 2001 */
625 /*---------------------------------------------------------*/
626 -- Insert the new record into subscriptions table and trans table
627 AHL_SUBSCRIPTIONS_PKG.INSERT_ROW (
628 X_ROWID => l_rowid,
629 X_SUBSCRIPTION_ID => l_subscription_id,
630 X_ATTRIBUTE5 => l_subscription_info.attribute5,
631 X_DOCUMENT_ID => p_x_subscription_tbl(i).document_id,
632 X_REQUESTED_BY_PARTY_ID => p_x_subscription_tbl(i).requested_by_party_id,
633 X_ATTRIBUTE6 => l_subscription_info.attribute6,
634 X_ATTRIBUTE7 => l_subscription_info.attribute7,
635 X_ATTRIBUTE8 => l_subscription_info.attribute8,
636 X_ATTRIBUTE9 => l_subscription_info.attribute9,
637 X_ATTRIBUTE10 => l_subscription_info.attribute10,
638 X_ATTRIBUTE11 => l_subscription_info.attribute11,
639 X_ATTRIBUTE12 => l_subscription_info.attribute12,
640 X_ATTRIBUTE13 => l_subscription_info.attribute13,
641 X_ATTRIBUTE14 => l_subscription_info.attribute14,
642 X_ATTRIBUTE_CATEGORY => l_subscription_info.attribute_category,
643 X_ATTRIBUTE1 => l_subscription_info.attribute1,
644 X_ATTRIBUTE2 => l_subscription_info.attribute2,
645 X_ATTRIBUTE3 => l_subscription_info.attribute3,
646 X_ATTRIBUTE4 => l_subscription_info.attribute4,
647 X_OBJECT_VERSION_NUMBER => 1,
648 X_ATTRIBUTE15 => l_subscription_info.attribute15,
649 X_SUBSCRIBED_FRM_PARTY_ID => l_subscription_info.subscribed_frm_party_id,
650 X_QUANTITY => p_x_subscription_tbl(i).quantity,
651 X_STATUS_CODE => p_x_subscription_tbl(i).status_code,
652 X_PURCHASE_ORDER_NO => l_subscription_info.purchase_order_no,
653 X_FREQUENCY_CODE => l_subscription_info.frequency_code,
654 X_SUBSCRIPTION_TYPE_CODE => l_subscription_info.subscription_type_code,
655 X_MEDIA_TYPE_CODE => l_subscription_info.media_type_code,
656 X_START_DATE => l_subscription_info.start_date,
657 X_END_DATE => l_subscription_info.end_date,
658 X_COMMENTS => l_subscription_info.comments,
659 X_CREATION_DATE => sysdate,
660 X_CREATED_BY => fnd_global.user_id,
661 X_LAST_UPDATE_DATE => sysdate,
662 X_LAST_UPDATED_BY => fnd_global.user_id,
663 X_LAST_UPDATE_LOGIN => fnd_global.login_id
664 ) ;
665 /*
666 -- Insert the new record into subscriptions table
667 INSERT INTO AHL_SUBSCRIPTIONS_B
668 (
669 SUBSCRIPTION_ID,
670 DOCUMENT_ID,
671 STATUS_CODE,
672 REQUESTED_BY_PARTY_ID,
673 QUANTITY,
674 FREQUENCY_CODE,
675 SUBSCRIBED_FRM_PARTY_ID,
676 START_DATE,
677 END_DATE,
678 PURCHASE_ORDER_NO,
679 SUBSCRIPTION_TYPE_CODE,
680 MEDIA_TYPE_CODE,
681 OBJECT_VERSION_NUMBER,
682 ATTRIBUTE_CATEGORY,
683 ATTRIBUTE1,
684 ATTRIBUTE2,
685 ATTRIBUTE3,
686 ATTRIBUTE4,
687 ATTRIBUTE5,
688 ATTRIBUTE6,
689 ATTRIBUTE7,
690 ATTRIBUTE8,
691 ATTRIBUTE9,
692 ATTRIBUTE10,
693 ATTRIBUTE11,
694 ATTRIBUTE12,
695 ATTRIBUTE13,
696 ATTRIBUTE14,
697 ATTRIBUTE15,
698 LAST_UPDATE_DATE,
699 LAST_UPDATED_BY,
700 CREATION_DATE,
701 CREATED_BY,
702 LAST_UPDATE_LOGIN
703 )
704 VALUES
705 (
706 l_subscription_id,
707 p_x_subscription_tbl(i).document_id,
708 p_x_subscription_tbl(i).status_code,
709 p_x_subscription_tbl(i).requested_by_party_id,
710 p_x_subscription_tbl(i).quantity,
711 l_subscription_info.frequency_code,
712 l_subscription_info.subscribed_frm_party_id,
713 l_subscription_info.start_date,
714 l_subscription_info.end_date,
715 l_subscription_info.purchase_order_no,
716 l_subscription_info.subscription_type_code,
717 l_subscription_info.media_type_code,
718 1,
719 l_subscription_info.attribute_category,
720 l_subscription_info.attribute1,
721 l_subscription_info.attribute2,
722 l_subscription_info.attribute3,
723 l_subscription_info.attribute4,
724 l_subscription_info.attribute5,
725 l_subscription_info.attribute6,
726 l_subscription_info.attribute7,
727 l_subscription_info.attribute8,
728 l_subscription_info.attribute9,
729 l_subscription_info.attribute10,
730 l_subscription_info.attribute11,
731 l_subscription_info.attribute12,
732 l_subscription_info.attribute13,
733 l_subscription_info.attribute14,
734 l_subscription_info.attribute15,
735 sysdate,
736 fnd_global.user_id,
737 sysdate,
738 fnd_global.user_id,
739 fnd_global.login_id
740 );
741 p_x_subscription_tbl(i).subscription_id := l_subscription_id;
742 p_x_subscription_tbl(i).object_version_number := 1;
743 -- Insert the record into trans table
744 INSERT INTO AHL_SUBSCRIPTIONS_TL
745 (
746 SUBSCRIPTION_ID,
747 LAST_UPDATE_DATE,
748 LAST_UPDATED_BY,
749 CREATION_DATE,
750 CREATED_BY,
751 COMMENTS,
752 LANGUAGE,
753 SOURCE_LANG
754 )
755 SELECT
756 l_subscription_id,
757 sysdate,
758 fnd_global.user_id,
759 sysdate,
760 fnd_global.user_id,
761 l_subscription_info.comments,
762 L.LANGUAGE_CODE,
763 userenv('LANG')
764 FROM FND_LANGUAGES L
765 WHERE L.INSTALLED_FLAG IN ('I','B')
766 AND NOT EXISTS
767 (SELECT NULL
768 FROM AHL_SUBSCRIPTIONS_TL T
769 WHERE T.subscription_id = l_subscription_id
770 AND T.language = L.LANGUAGE_CODE);
771 */
772
773 -- Added pjha 15-May-2002 for modifying 'subscribed to' Begin
774
775 --Check whether Subscribed To is Yes or No in AHL_DOCUMENTS_B
776 SELECT subscribe_to_flag
777 INTO l_subscribe_to_flag
778 FROM AHL_DOCUMENTS_B
779 WHERE document_id = p_x_subscription_tbl(i).document_id;
780
781 --If Subscribed To is no, then make it yes
782 IF l_subscribe_to_flag = 'N' THEN
783 UPDATE AHL_DOCUMENTS_B
784 SET subscribe_to_flag = 'Y'
785 WHERE document_id = p_x_subscription_tbl(i).document_id;
786 END IF;
787
788 -- Added pjha 15-May-2002 for modifying 'subscribed to' End
789
790 --Assign the values
791 p_x_subscription_tbl(i).subscription_id := l_subscription_id;
792 p_x_subscription_tbl(i).object_version_number := 1;
793 --
794 l_msg_count := FND_MSG_PUB.count_msg;
795 IF l_msg_count > 0 THEN
796 X_msg_count := l_msg_count;
797 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
798 RAISE FND_API.G_EXC_ERROR;
799 END IF;
800 END IF;
801 END LOOP;
802 END IF;
803 -- Standard check of p_commit.
804 IF FND_API.TO_BOOLEAN(p_commit) THEN
805 COMMIT;
806 END IF;
807 -- Debug info
808 IF G_DEBUG='Y' THEN
809 AHL_DEBUG_PUB.debug( 'End of private api Create Subscription','+SUB+');
810
811 END IF;
812 -- Check if API is called in debug mode. If yes, disable debug.
813 IF G_DEBUG='Y' THEN
814 AHL_DEBUG_PUB.disable_debug;
815
816 END IF;
817
818 EXCEPTION
819 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
820 ROLLBACK TO create_subscription;
821 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
822 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
823 p_count => x_msg_count,
824 p_data => x_msg_data);
825
826 IF G_DEBUG='Y' THEN
827 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'ERROR' );
828 AHL_DEBUG_PUB.debug( 'ahl_di_subscription_pvt.Create Subscription','+SUB+');
829
830
831 -- Check if API is called in debug mode. If yes, disable debug.
832 AHL_DEBUG_PUB.disable_debug;
833
834 END IF;
835
836 WHEN FND_API.G_EXC_ERROR THEN
837 ROLLBACK TO create_subscription;
838 X_return_status := FND_API.G_RET_STS_ERROR;
839 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
840 p_count => x_msg_count,
841 p_data => X_msg_data);
842 -- Debug info.
843 IF G_DEBUG='Y' THEN
844 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
845 AHL_DEBUG_PUB.debug( 'ahl_di_subscription_pvt.Create Subscription','+SUB+');
846
847
848 -- Check if API is called in debug mode. If yes, disable debug.
849 AHL_DEBUG_PUB.disable_debug;
850
851 END IF;
852
853
854 WHEN DUP_VAL_ON_INDEX THEN
855 ROLLBACK TO create_subscription;
856 X_return_status := FND_API.G_RET_STS_ERROR;
857 FND_MESSAGE.SET_NAME('AHL','AHL_DI_SUBSC_DUP_RECORD');
858 FND_MSG_PUB.ADD;
859 -- Check if API is called in debug mode. If yes, disable debug.
860 IF G_DEBUG='Y' THEN
861 AHL_DEBUG_PUB.disable_debug;
862
863 END IF;
864
865
866 WHEN OTHERS THEN
867 ROLLBACK TO create_subscription;
868 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
869 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
870 THEN
871 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_DI_SUBSCRIPTION_PVT',
872 p_procedure_name => 'CREATE_SUBSCRIPTION',
873 p_error_text => SUBSTR(SQLERRM,1,240));
874 END IF;
875 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
876 p_count => x_msg_count,
877 p_data => X_msg_data);
878 -- Debug info.
879 IF G_DEBUG='Y' THEN
880 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'SQL ERROR' );
881 AHL_DEBUG_PUB.debug( 'ahl_di_subscription_pvt.Create Subscription','+SUB+');
882
883
884 -- Check if API is called in debug mode. If yes, disable debug.
885 AHL_DEBUG_PUB.disable_debug;
886
887 END IF;
888
889 END CREATE_SUBSCRIPTION;
890 /*------------------------------------------------------*/
891 /* procedure name: modify_subscription */
892 /* description : Update the existing subscription recor*/
893 /* d and removes the subscription record */
894 /* for an associated document */
895 /* */
896 /*------------------------------------------------------*/
897 PROCEDURE MODIFY_SUBSCRIPTION
898 (
899 p_api_version IN NUMBER := 1.0 ,
900 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
901 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
902 p_validate_only IN VARCHAR2 := FND_API.G_TRUE ,
903 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
904 p_x_subscription_tbl IN OUT NOCOPY subscription_tbl ,
905 x_return_status OUT NOCOPY VARCHAR2 ,
906 x_msg_count OUT NOCOPY NUMBER ,
907 x_msg_data OUT NOCOPY VARCHAR2
908 )
909 IS
910 -- Cursor to retrieve the existing subscriptions record
911 CURSOR get_sub_rec_b_info(c_subscription_id NUMBER)
912 IS
913 SELECT ROWID row_id,
914 document_id,
915 status_code,
916 requested_by_party_id,
917 quantity,
918 frequency_code,
919 subscribed_frm_party_id,
920 start_date,
921 end_date,
922 purchase_order_no,
923 subscription_type_code,
924 media_type_code,
925 object_version_number,
926 attribute_category,
927 attribute1,
928 attribute2,
929 attribute3,
930 attribute4,
931 attribute5,
932 attribute6,
933 attribute7,
934 attribute8,
935 attribute9,
936 attribute10,
937 attribute11,
938 attribute12,
939 attribute13,
940 attribute14,
941 attribute15
942 FROM AHL_SUBSCRIPTIONS_B
943 WHERE subscription_id = c_subscription_id
944 FOR UPDATE OF object_version_number NOWAIT;
945 -- Cursor to retrieve the record from trans table
946 CURSOR get_sub_rec_tl_info(c_subscription_id NUMBER)
947 IS
948 SELECT ROWID,
949 comments
950 FROM AHL_SUBSCRIPTIONS_TL
951 WHERE subscription_id = c_subscription_id
952 FOR UPDATE OF subscription_id NOWAIT;
953
954 -- modified the code for fixing Bug 2183529
955 -- Cursor to check for old subscriptions
956 CURSOR get_old_sub ( c_document_id NUMBER,
957 c_requested_by_party_id NUMBER)
958 IS
959 SELECT subscription_id
960 FROM AHL_SUBSCRIPTIONS_B
961 WHERE document_id = c_document_id AND
962 requested_by_party_id = c_requested_by_party_id;
963
964 --
965 --added pjha 28-Jun-2002 for proper update of Subscribed to
966 --Cursor to get maximum end_date for subscriptions for the document
967 CURSOR get_max_end_date(c_document_id NUMBER)
968 IS
969 SELECT MAX(NVL(end_date,SYSDATE))
970 FROM AHL_SUBSCRIPTIONS_B
971 WHERE document_id = c_document_id;
972
973 --
974 -- Cursor is used to get requested by name given requested id
975 -- Perf Fixes for 4919023
976 /*
977 CURSOR get_requested_name(c_requested_by_party_id NUMBER)
978 IS
979 SELECT party_name
980 FROM ahl_hz_per_employees_v
981 WHERE party_id= c_requested_by_party_id;
982 */
983
984 CURSOR get_requested_name_hz(c_requested_by_party_id NUMBER)
985 IS
986 SELECT PARTY_NAME
987 FROM HZ_PARTIES
988 WHERE party_id= c_requested_by_party_id
989 AND PARTY_TYPE ='PERSON';
990
991 CURSOR get_requested_name_ppf(c_requested_by_party_id NUMBER)
992 IS
993 SELECT PPF.FULL_NAME
994 FROM PER_PEOPLE_F PPF, PER_PERSON_TYPES PPT
995 WHERE PPF.PERSON_ID= c_requested_by_party_id
996 AND TRUNC(SYSDATE) BETWEEN PPF.EFFECTIVE_START_DATE AND PPF.EFFECTIVE_END_DATE
997 AND NVL(PPF.CURRENT_EMPLOYEE_FLAG, 'X') = 'Y'
998 AND PPF.PERSON_TYPE_ID = PPT.PERSON_TYPE_ID
999 AND PPT.SYSTEM_PERSON_TYPE ='EMP';
1000
1001
1002 --
1003 l_requested_by_party_id NUMBER;
1004 l_requested_by_name VARCHAR2(301) := NULL;
1005 l_api_name CONSTANT VARCHAR2(30) := 'MODIFY_SUBSCRIPTION';
1006 l_api_version CONSTANT NUMBER := 1.0;
1007 l_comments VARCHAR2(2000);
1008 l_msg_count NUMBER;
1009 l_num_rec NUMBER;
1010 l_rowid ROWID;
1011 l_language VARCHAR2(4);
1012 l_source_lang VARCHAR2(4);
1013 l_subscription_info get_sub_rec_b_info%ROWTYPE;
1014 l_old_sub_id NUMBER;
1015 l_dup_flag VARCHAR2(1) := 'N';
1016 l_prod_install_status VARCHAR2(30);
1017
1018 -- Added pjha 28-Jun-2002 for bug#2438718: Begin
1019 l_subscription_tbl subscription_tbl;
1020 -- Added pjha 28-Jun-2002 for bug#2438718: End
1021
1022 -- Added pjha 28-Jun-2002 for modifying 'subscribed to' Begin
1023 l_end_date DATE;
1024 -- Added pjha 28-Jun-2002 for modifying 'subscribed to' End
1025
1026 --
1027 BEGIN
1028 -- Standard Start of API savepoint
1029 SAVEPOINT modify_subscription;
1030 -- Check if API is called in debug mode. If yes, enable debug.
1031 IF G_DEBUG='Y' THEN
1032 AHL_DEBUG_PUB.enable_debug;
1033
1034 END IF;
1035 -- Debug info.
1036 IF G_DEBUG='Y' THEN
1037 AHL_DEBUG_PUB.debug( 'enter ahl_di_subscription_pvt.Modify Subscription','+SUB+');
1038 END IF;
1039 -- Standard call to check for call compatibility.
1040 IF FND_API.to_boolean(p_init_msg_list)
1041 THEN
1042 FND_MSG_PUB.initialize;
1043 END IF;
1044 -- Initialize API return status to success
1045 x_return_status := 'S';
1046 -- Initialize message list if p_init_msg_list is set to TRUE.
1047 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
1048 p_api_version,
1049 l_api_name,G_PKG_NAME)
1050 THEN
1051 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1052 END IF;
1053
1054 -- Start API Body
1055
1056 -- Perf Fixes for 4919023
1057 BEGIN
1058 IF G_DEBUG='Y' THEN
1059 AHL_DEBUG_PUB.debug( 'Fetching Installation Status of PER','+SUB+');
1060 END IF;
1061 SELECT AHL_DI_DOC_INDEX_PVT.GET_PRODUCT_INSTALL_STATUS('PER')
1062 INTO l_prod_install_status
1063 FROM DUAL;
1064 END;
1065
1066 IF p_x_subscription_tbl.COUNT > 0
1067 THEN
1068
1069 FOR i IN p_x_subscription_tbl.FIRST..p_x_subscription_tbl.LAST
1070 LOOP
1071 --Enhancement nos #2034767 and #2205830: pbarman : April 2003
1072 IF (NVL(p_x_subscription_tbl(i).delete_flag, 'N') = 'N' )
1073 THEN
1074
1075 -- Calling validate subscriptions
1076 VALIDATE_SUBSCRIPTION
1077 (
1078 p_subscription_id => p_x_subscription_tbl(i).subscription_id,
1079 p_document_id =>p_x_subscription_tbl(i).document_id,
1080 p_status_code =>p_x_subscription_tbl(i).status_code,
1081 p_requested_by_party_id =>p_x_subscription_tbl(i).requested_by_party_id,
1082 p_quantity =>p_x_subscription_tbl(i).quantity,
1083 p_frequency_code =>p_x_subscription_tbl(i).frequency_code,
1084 p_subscribed_frm_party_id =>p_x_subscription_tbl(i).subscribed_frm_party_id,
1085 p_start_date =>p_x_subscription_tbl(i).start_date,
1086 p_end_date =>p_x_subscription_tbl(i).end_date,
1087 p_media_type_code =>p_x_subscription_tbl(i).media_type_code,
1088 p_subscription_type_code =>p_x_subscription_tbl(i).subscription_type_code,
1089 p_purchase_order_no =>p_x_subscription_tbl(i).purchase_order_no,
1090 p_delete_flag =>p_x_subscription_tbl(i).delete_flag
1091 );
1092
1093
1094
1095 OPEN get_old_sub (p_x_subscription_tbl(i).document_id,
1096 p_x_subscription_tbl(i).requested_by_party_id);
1097 FETCH get_old_sub INTO l_old_sub_id;
1098 IF (l_old_sub_id <> p_x_subscription_tbl(i).subscription_id) THEN
1099 --FND_MESSAGE.SET_NAME('AHL','AHL_DI_SUBSC_DUP_RECORD');
1100 FOR j IN p_x_subscription_tbl.FIRST..p_x_subscription_tbl.LAST
1101 LOOP
1102 IF ((l_old_sub_id = p_x_subscription_tbl(j).subscription_id) AND (l_old_sub_id <> p_x_subscription_tbl(i).subscription_id)) THEN
1103 l_dup_flag := 'Y';
1104 IF(p_x_subscription_tbl(i).requested_by_party_id = p_x_subscription_tbl(j).requested_by_party_id) THEN
1105
1106 -- Perf Fixes for 4919023
1107 /*
1108 OPEN get_requested_name(p_x_subscription_tbl(i).requested_by_party_id);
1109 FETCH get_requested_name INTO l_requested_by_name;
1110 CLOSE get_requested_name;
1111 */
1112
1113 IF l_prod_install_status IN ('N','L') THEN
1114 OPEN get_requested_name_hz(p_x_subscription_tbl(i).requested_by_party_id);
1115 FETCH get_requested_name_hz INTO l_requested_by_name;
1116 CLOSE get_requested_name_hz;
1117 ELSIF l_prod_install_status IN ('I','S') THEN
1118 OPEN get_requested_name_ppf(p_x_subscription_tbl(i).requested_by_party_id);
1119 FETCH get_requested_name_ppf INTO l_requested_by_name;
1120 CLOSE get_requested_name_ppf;
1121 END IF;
1122
1123 FND_MESSAGE.SET_NAME('AHL','AHL_DI_SUBS_DUP_RECORD');
1124 FND_MESSAGE.SET_TOKEN('REQID',l_requested_by_name);
1125 FND_MSG_PUB.ADD;
1126 END IF;
1127 END IF;
1128 END LOOP;
1129 IF (l_dup_flag = 'N') THEN
1130 -- Perf Fixes for 4919023
1131 /*
1132 OPEN get_requested_name(p_x_subscription_tbl(i).requested_by_party_id);
1133 FETCH get_requested_name INTO l_requested_by_name;
1134 CLOSE get_requested_name;
1135 */
1136
1137 IF l_prod_install_status IN ('N','L') THEN
1138 OPEN get_requested_name_hz(p_x_subscription_tbl(i).requested_by_party_id);
1139 FETCH get_requested_name_hz INTO l_requested_by_name;
1140 CLOSE get_requested_name_hz;
1141 ELSIF l_prod_install_status IN ('I','S') THEN
1142 OPEN get_requested_name_ppf(p_x_subscription_tbl(i).requested_by_party_id);
1143 FETCH get_requested_name_ppf INTO l_requested_by_name;
1144 CLOSE get_requested_name_ppf;
1145 END IF;
1146
1147 FND_MESSAGE.SET_NAME('AHL','AHL_DI_SUBS_DUP_RECORD');
1148 FND_MESSAGE.SET_TOKEN('REQID',l_requested_by_name);
1149 FND_MSG_PUB.ADD;
1150 END IF;
1151 l_dup_flag := 'N';
1152 END IF;
1153 CLOSE get_old_sub;
1154 END IF;
1155 END LOOP;
1156 --End of Validations
1157 -- Standard call to get message count
1158 l_msg_count := FND_MSG_PUB.count_msg;
1159
1160 IF l_msg_count > 0 THEN
1161 X_msg_count := l_msg_count;
1162 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1163 RAISE FND_API.G_EXC_ERROR;
1164 END IF;
1165 --Start of API Body
1166 FOR i IN p_x_subscription_tbl.FIRST..p_x_subscription_tbl.LAST
1167 LOOP
1168 --Retrieve the existing subscription record for passed subscription id
1169 OPEN get_sub_rec_b_info(p_x_subscription_tbl(i).subscription_id);
1170 FETCH get_sub_rec_b_info INTO l_subscription_info;
1171 CLOSE get_sub_rec_b_info;
1172 --Retrieves the existing tranlation record
1173 OPEN get_sub_rec_tl_info(p_x_subscription_tbl(i).subscription_id);
1174 FETCH get_sub_rec_tl_info INTO l_rowid,
1175 l_comments;
1176 CLOSE get_sub_rec_tl_info;
1177
1178 --This is a fix for earlier bug when concurrent users are
1179 -- updating same record...02/05/02
1180 if (l_subscription_info.object_version_number <>p_x_subscription_tbl(i).object_version_number)
1181 then
1182 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
1183 FND_MSG_PUB.ADD;
1184 RAISE FND_API.G_EXC_ERROR;
1185 end if;
1186 -- The following conditions compare the new record value with old record
1187 -- value, if its different then assign the new value else continue
1188 IF p_x_subscription_tbl(i).subscription_id IS NOT NULL
1189 THEN
1190 l_subscription_info.status_code := p_x_subscription_tbl(i).status_code;
1191 l_subscription_info.requested_by_party_id := p_x_subscription_tbl(i).requested_by_party_id;
1192 l_subscription_info.quantity := p_x_subscription_tbl(i).quantity;
1193 l_subscription_info.frequency_code := p_x_subscription_tbl(i).frequency_code;
1194 l_subscription_info.subscribed_frm_party_id := p_x_subscription_tbl(i).subscribed_frm_party_id;
1195 l_subscription_info.start_date := p_x_subscription_tbl(i).start_date;
1196 l_subscription_info.end_date := p_x_subscription_tbl(i).end_date;
1197 l_subscription_info.purchase_order_no := p_x_subscription_tbl(i).purchase_order_no;
1198 l_subscription_info.media_type_code := p_x_subscription_tbl(i).media_type_code;
1199 l_subscription_info.subscription_type_code := p_x_subscription_tbl(i).subscription_type_code;
1200 l_comments := p_x_subscription_tbl(i).comments;
1201 l_subscription_info.attribute_category := p_x_subscription_tbl(i).attribute_category;
1202 l_subscription_info.attribute1 := p_x_subscription_tbl(i).attribute1;
1203 l_subscription_info.attribute2 := p_x_subscription_tbl(i).attribute2;
1204 l_subscription_info.attribute3 := p_x_subscription_tbl(i).attribute3;
1205 l_subscription_info.attribute3 := p_x_subscription_tbl(i).attribute3;
1206 l_subscription_info.attribute4 := p_x_subscription_tbl(i).attribute4;
1207 l_subscription_info.attribute5 := p_x_subscription_tbl(i).attribute5;
1208 l_subscription_info.attribute6 := p_x_subscription_tbl(i).attribute6;
1209 l_subscription_info.attribute7 := p_x_subscription_tbl(i).attribute7;
1210 l_subscription_info.attribute8 := p_x_subscription_tbl(i).attribute8;
1211 l_subscription_info.attribute9 := p_x_subscription_tbl(i).attribute9;
1212 l_subscription_info.attribute10 := p_x_subscription_tbl(i).attribute10;
1213 l_subscription_info.attribute11 := p_x_subscription_tbl(i).attribute11;
1214 l_subscription_info.attribute12 := p_x_subscription_tbl(i).attribute12;
1215 l_subscription_info.attribute13 := p_x_subscription_tbl(i).attribute13;
1216 l_subscription_info.attribute14 := p_x_subscription_tbl(i).attribute14;
1217 l_subscription_info.attribute15 := p_x_subscription_tbl(i).attribute15;
1218
1219 /*-------------------------------------------------------- */
1220 /* procedure name: AHL_SUBSCRIPTIONS_PKG.UPDATE_ROW */
1221 /* description : Added by Senthil to call Table Handler */
1222 /* Date : Dec 31 2001 */
1223 /*---------------------------------------------------------*/
1224 -- Updates subscriptions record and tranlation table
1225 --Enhancement nos #2034767 and #2205830: pbarman : April 2003
1226 IF (p_x_subscription_tbl(i).subscription_id IS NOT NULL AND
1227 NVL(p_x_subscription_tbl(i).delete_flag, 'N') = 'N' )
1228 THEN
1229 AHL_SUBSCRIPTIONS_PKG.UPDATE_ROW (
1230 X_SUBSCRIPTION_ID => p_x_subscription_tbl(i).subscription_id,
1231 X_ATTRIBUTE5 => l_subscription_info.attribute5,
1232 X_DOCUMENT_ID => l_subscription_info.document_id,
1233 X_REQUESTED_BY_PARTY_ID => l_subscription_info.requested_by_party_id,
1234 X_ATTRIBUTE6 => l_subscription_info.attribute6,
1235 X_ATTRIBUTE7 => l_subscription_info.attribute7,
1236 X_ATTRIBUTE8 => l_subscription_info.attribute8,
1237 X_ATTRIBUTE9 => l_subscription_info.attribute9,
1238 X_ATTRIBUTE10 => l_subscription_info.attribute10,
1239 X_ATTRIBUTE11 => l_subscription_info.attribute11,
1240 X_ATTRIBUTE12 => l_subscription_info.attribute12,
1241 X_ATTRIBUTE13 => l_subscription_info.attribute13,
1242 X_ATTRIBUTE14 => l_subscription_info.attribute14,
1243 X_ATTRIBUTE_CATEGORY => l_subscription_info.attribute_category,
1244 X_ATTRIBUTE1 => l_subscription_info.attribute1,
1245 X_ATTRIBUTE2 => l_subscription_info.attribute2,
1246 X_ATTRIBUTE3 => l_subscription_info.attribute3,
1247 X_ATTRIBUTE4 => l_subscription_info.attribute4,
1248 X_OBJECT_VERSION_NUMBER => l_subscription_info.object_version_number+1,
1249 X_ATTRIBUTE15 => l_subscription_info.attribute15,
1250 X_SUBSCRIBED_FRM_PARTY_ID => l_subscription_info.subscribed_frm_party_id,
1251 X_QUANTITY => l_subscription_info.quantity,
1252 X_STATUS_CODE => l_subscription_info.status_code,
1253 X_PURCHASE_ORDER_NO => l_subscription_info.purchase_order_no,
1254 X_FREQUENCY_CODE => l_subscription_info.frequency_code,
1255 X_SUBSCRIPTION_TYPE_CODE => l_subscription_info.subscription_type_code,
1256 X_MEDIA_TYPE_CODE => l_subscription_info.media_type_code,
1257 X_START_DATE => l_subscription_info.start_date,
1258 X_END_DATE => l_subscription_info.end_date,
1259 X_COMMENTS => l_comments,
1260 X_LAST_UPDATE_DATE => sysdate,
1261 X_LAST_UPDATED_BY => fnd_global.user_id,
1262 X_LAST_UPDATE_LOGIN => fnd_global.login_id
1263 );
1264 /*
1265 -- Updates subscriptions record
1266
1267
1268 UPDATE AHL_SUBSCRIPTIONS_B
1269 SET document_id = l_subscription_info.document_id,
1270 status_code = l_subscription_info.status_code,
1271 requested_by_party_id = l_subscription_info.requested_by_party_id,
1272 quantity = l_subscription_info.quantity,
1273 frequency_code = l_subscription_info.frequency_code,
1274 subscribed_frm_party_id =l_subscription_info.subscribed_frm_party_id,
1275 start_date = l_subscription_info.start_date,
1276 end_date = l_subscription_info.end_date,
1277 purchase_order_no = l_subscription_info.purchase_order_no,
1278 subscription_type_code = l_subscription_info.subscription_type_code,
1279 media_type_code = l_subscription_info.media_type_code,
1280 object_version_number = l_subscription_info.object_version_number+1,
1281 attribute_category = l_subscription_info.attribute_category,
1282 attribute1 = l_subscription_info.attribute1,
1283 attribute2 = l_subscription_info.attribute2,
1284 attribute3 = l_subscription_info.attribute3,
1285 attribute4 = l_subscription_info.attribute4,
1286 attribute5 = l_subscription_info.attribute5,
1287 attribute6 = l_subscription_info.attribute6,
1288 attribute7 = l_subscription_info.attribute7,
1289 attribute8 = l_subscription_info.attribute8,
1290 attribute9 = l_subscription_info.attribute9,
1291 attribute10 = l_subscription_info.attribute10,
1292 attribute11 = l_subscription_info.attribute11,
1293 attribute12 = l_subscription_info.attribute12,
1294 attribute13 = l_subscription_info.attribute13,
1295 attribute14 = l_subscription_info.attribute14,
1296 attribute15 = l_subscription_info.attribute15,
1297 last_update_date = sysdate,
1298 last_updated_by = fnd_global.user_id,
1299 last_update_login = fnd_global.login_id
1300 WHERE subscription_id = p_x_subscription_tbl(i).subscription_id;
1301 --Update the tranlation table
1302 UPDATE AHL_SUBSCRIPTIONS_TL
1303 SET comments = l_comments,
1304 last_update_date = sysdate,
1305 last_updated_by = fnd_global.user_id,
1306 last_update_login = fnd_global.login_id
1307 WHERE SUBSCRIPTION_ID = p_x_subscription_tbl(i).subscription_id
1308 AND userenv('LANG') in (LANGUAGE, SOURCE_LANG);
1309
1310
1311 */
1312 -- Incase of delte a subscription record set the end date,
1313 -- At this point we are not using delete subscriptions
1314
1315 -- Modified pjha 28-Jun-2002 for modifying 'subscribed to' Begin
1316
1317 --Check if the document is subscribed and then update accordingly
1318 OPEN get_max_end_date(p_x_subscription_tbl(i).document_id);
1319 FETCH get_max_end_date INTO l_end_date;
1320 --Modified pjha 09-Jul-2002 for fixing bug#2452714: Begin
1321 IF (l_end_date IS NULL OR get_max_end_date%NOTFOUND OR l_end_date < TRUNC(sysdate)) THEN
1322 --IF (get_max_end_date%NOTFOUND OR l_end_date < TRUNC(sysdate)) THEN
1323 --Modified pjha 09-Jul-2002 for fixing bug#2452714: End
1324 UPDATE AHL_DOCUMENTS_B
1325 SET subscribe_to_flag = 'N'
1326 WHERE document_id = p_x_subscription_tbl(i).document_id;
1327 ELSE
1328 UPDATE AHL_DOCUMENTS_B
1329 SET subscribe_to_flag = 'Y'
1330 WHERE document_id = p_x_subscription_tbl(i).document_id;
1331 END IF;
1332 CLOSE get_max_end_date;
1333
1334 END IF;
1335 -- Modified pjha 28-Jun-2002 for modifying 'subscribed to' End
1336
1337 END IF;
1338
1339 IF (p_x_subscription_tbl(i).subscription_id IS NOT NULL AND
1340 NVL(p_x_subscription_tbl(i).delete_flag, 'N') = 'Y' )
1341
1342 THEN
1343 -- Added pjha 28-Jun-2002 for bug#2438718: Begin
1344 l_subscription_tbl(1).subscription_id := p_x_subscription_tbl(i).subscription_id;
1345 DELETE_SUBSCRIPTION
1346 (
1347 p_api_version => 1.0 ,
1348 p_init_msg_list => FND_API.G_TRUE ,
1349 p_commit => FND_API.G_FALSE ,
1350 p_validate_only => FND_API.G_TRUE ,
1351 p_validation_level => FND_API.G_VALID_LEVEL_FULL ,
1352 p_x_subscription_tbl => l_subscription_tbl ,
1353 x_return_status => x_return_status ,
1354 x_msg_count => x_msg_count ,
1355 x_msg_data => x_msg_data);
1356
1357 -- Added pjha 28-Jun-2002 for bug#2438718: End
1358
1359
1360 -- Modified pjha 28-Jun-2002 for modifying 'subscribed to' Begin
1361
1362 --Check if the document is subscribed and then update accordingly
1363
1364 OPEN get_max_end_date(p_x_subscription_tbl(i).document_id);
1365 FETCH get_max_end_date INTO l_end_date;
1366 --Modified pjha 09-Jul-2002 for fixing bug#2452714: Begin
1367 IF (l_end_date IS NULL OR get_max_end_date%NOTFOUND OR l_end_date < TRUNC(sysdate)) THEN
1368 --IF (get_max_end_date%NOTFOUND OR l_end_date < TRUNC(sysdate)) THEN
1369 --Modified pjha 09-Jul-2002 for fixing bug#2452714: End
1370 UPDATE AHL_DOCUMENTS_B
1371 SET subscribe_to_flag = 'N'
1372 WHERE document_id = p_x_subscription_tbl(i).document_id;
1373 ELSE
1374 UPDATE AHL_DOCUMENTS_B
1375 SET subscribe_to_flag = 'Y'
1376 WHERE document_id = p_x_subscription_tbl(i).document_id;
1377 END IF;
1378 CLOSE get_max_end_date;
1379
1380
1381
1382
1383 -- Modified pjha 28-Jun-2002 for modifying 'subscribed to' End
1384
1385 END IF;
1386 END LOOP;
1387 END IF;
1388 -- Standard check of p_commit.
1389 IF FND_API.TO_BOOLEAN(p_commit) THEN
1390 COMMIT;
1391 END IF;
1392 -- Debug info
1393 IF G_DEBUG='Y' THEN
1394 AHL_DEBUG_PUB.debug( 'End of private api Modify Subscription','+SUB+');
1395
1396 END IF;
1397 -- Check if API is called in debug mode. If yes, disable debug.
1398 IF G_DEBUG='Y' THEN
1399 AHL_DEBUG_PUB.disable_debug;
1400
1401 END IF;
1402
1403 EXCEPTION
1404 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1405 ROLLBACK TO modify_subscription;
1406 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1407 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1408 p_count => x_msg_count,
1409 p_data => x_msg_data);
1410
1411 IF G_DEBUG='Y' THEN
1412 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'ERROR' );
1413 AHL_DEBUG_PUB.debug( 'ahl_di_subscription_pvt.Modify Subscription','+SUB+');
1414
1415
1416 -- Check if API is called in debug mode. If yes, disable debug.
1417 AHL_DEBUG_PUB.disable_debug;
1418
1419 END IF;
1420
1421 WHEN FND_API.G_EXC_ERROR THEN
1422 ROLLBACK TO modify_subscription;
1423 X_return_status := FND_API.G_RET_STS_ERROR;
1424 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1425 p_count => x_msg_count,
1426 p_data => X_msg_data);
1427 -- Debug info.
1428 IF G_DEBUG='Y' THEN
1429 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
1430 AHL_DEBUG_PUB.debug( 'ahl_di_subscription_pvt.Modify Subscription','+SUB+');
1431
1432
1433 -- Check if API is called in debug mode. If yes, disable debug.
1434 AHL_DEBUG_PUB.disable_debug;
1435
1436 END IF;
1437
1438 WHEN OTHERS THEN
1439 ROLLBACK TO modify_subscription;
1440 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1441 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1442 THEN
1443 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_DI_SUBSCRIPTION_PVT',
1444 p_procedure_name => 'MODIFY_SUBSCRIPTION',
1445 p_error_text => SUBSTR(SQLERRM,1,240));
1446 END IF;
1447 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1448 p_count => x_msg_count,
1449 p_data => X_msg_data);
1450
1451 -- Debug info.
1452 IF G_DEBUG='Y' THEN
1453 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'SQL ERROR' );
1454 AHL_DEBUG_PUB.debug( 'ahl_di_subscription_pvt.Modify Subscription','+SUB+');
1455
1456
1457 -- Check if API is called in debug mode. If yes, disable debug.
1458 AHL_DEBUG_PUB.disable_debug;
1459
1460 END IF;
1461
1462 END MODIFY_SUBSCRIPTION;
1463 /*-------------------------------------------------------*/
1464 /* procedure name: delete_subscription */
1465 /* description :we are not using this procedure, probably*/
1466 /* next phase */
1467 /* */
1468 /*-------------------------------------------------------*/
1469
1470
1471 PROCEDURE DELETE_SUBSCRIPTION
1472 (
1473 p_api_version IN NUMBER := 1.0 ,
1474 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
1475 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
1476 p_validate_only IN VARCHAR2 := FND_API.G_TRUE ,
1477 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1478 p_x_subscription_tbl IN OUT NOCOPY subscription_tbl ,
1479 x_return_status OUT NOCOPY VARCHAR2 ,
1480 x_msg_count OUT NOCOPY NUMBER ,
1481 x_msg_data OUT NOCOPY VARCHAR2
1482 )
1483 IS
1484
1485 CURSOR get_sub_rec_b_info(c_subscription_id NUMBER)
1486 IS
1487 SELECT ROWID,
1488 start_date,
1489 end_date,
1490 object_version_number
1491 FROM AHL_SUBSCRIPTIONS_B
1492 WHERE subscription_id = c_subscription_id
1493 FOR UPDATE OF object_version_number NOWAIT;
1494
1495 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_SUBSCRIPTION';
1496 l_api_version CONSTANT NUMBER := 1.0;
1497 l_rowid ROWID;
1498 l_object_version_number NUMBER;
1499 l_end_date DATE;
1500 l_start_date DATE;
1501 BEGIN
1502 -- Standard Start of API savepoint
1503 SAVEPOINT delete_subscriptions;
1504 -- Standard call to check for call compatibility.
1505 IF FND_API.to_boolean(p_init_msg_list)
1506 THEN
1507 FND_MSG_PUB.initialize;
1508 END IF;
1509 -- Initialize API return status to success
1510 x_return_status := 'S';
1511 -- Initialize message list if p_init_msg_list is set to TRUE.
1512 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
1513 p_api_version,
1514 l_api_name,G_PKG_NAME)
1515 THEN
1516 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1517 END IF;
1518 --Start of API Body
1519 IF p_x_subscription_tbl.COUNT > 0
1520 THEN
1521 FOR i IN p_x_subscription_tbl.FIRST..p_x_subscription_tbl.LAST
1522 LOOP
1523 OPEN get_sub_rec_b_info(p_x_subscription_tbl(i).subscription_id);
1524 FETCH get_sub_rec_b_info INTO l_rowid,
1525 l_start_date,
1526 l_end_date,
1527 l_object_version_number;
1528
1529 IF (get_sub_rec_b_info%NOTFOUND)
1530 THEN
1531 FND_MESSAGE.SET_NAME('AHL','AHL_DI_SUB_RECORD_NOT_EXISTS');
1532 FND_MSG_PUB.ADD;
1533 END IF;
1534 CLOSE get_sub_rec_b_info;
1535 /* No need, it's done in modify document
1536 -- Check for version number
1537 IF (l_object_version_number <> p_x_subscription_tbl(i).object_version_number)
1538 THEN
1539 FND_MESSAGE.SET_NAME('AHL','AHL_DI_SUB_RECORD_CHANGED');
1540 FND_MSG_PUB.ADD;
1541 END IF;
1542 */
1543 -- Validate with end date
1544 /* Not required, user should be able to delete subscriptions
1545 after they have become obsolete: pjha 09-Jul-2002
1546 IF (l_end_date IS NOT NULL AND l_end_date < sysdate )
1547 THEN
1548 FND_MESSAGE.SET_NAME('AHL','AHL_DI_SUB_RECORD_CLOSED');
1549 FND_MSG_PUB.ADD;
1550 END IF;
1551 */
1552 /* Not required
1553 -- Check for start date
1554 IF l_start_date IS NULL
1555 THEN
1556 FND_MESSAGE.SET_NAME('AHL','AHL_DI_START_DATE_INVALID');
1557 FND_MSG_PUB.ADD;
1558 END IF;
1559
1560 --Check for End Date
1561 IF (TRUNC(NVL(l_end_date, SYSDATE)) >
1562 TRUNC(NVL(p_x_subscription_tbl(i).end_date,SYSDATE)))
1563 THEN
1564 FND_MESSAGE.SET_NAME('AHL','AHL_DI_SUB_E_DATE_INVALID');
1565 FND_MSG_PUB.ADD;
1566 END IF;
1567 */
1568 -- Update the end date in subscriptions table
1569 -- Modified pjha 15-May-2002 for modifying 'subscribed to' Begin
1570
1571
1572 -- Modified pjha 14-Jun-2002 for deleting the row: Begin
1573 AHL_SUBSCRIPTIONS_PKG.DELETE_ROW(
1574 X_SUBSCRIPTION_ID => p_x_subscription_tbl(i).subscription_id);
1575
1576
1577 /*
1578 UPDATE AHL_SUBSCRIPTIONS_B
1579 SET END_DATE = sysdate
1580 WHERE ROWID = l_rowid;
1581 */
1582
1583 -- Modified pjha 14-Jun-2002 for deleting the row: End
1584 END LOOP;
1585 -- Modified pjha 15-May-2002 for modifying 'subscribed to' End
1586 END IF;
1587 -- Standard check of p_commit.
1588 IF FND_API.TO_BOOLEAN(p_commit) THEN
1589 COMMIT;
1590 END IF;
1591
1592 EXCEPTION
1593 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1594 ROLLBACK TO delete_subscription;
1595 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1596 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1597 p_count => x_msg_count,
1598 p_data => x_msg_data);
1599
1600 WHEN FND_API.G_EXC_ERROR THEN
1601 ROLLBACK TO delete_subscription;
1602 X_return_status := FND_API.G_RET_STS_ERROR;
1603 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1604 p_count => x_msg_count,
1605 p_data => X_msg_data);
1606 WHEN OTHERS THEN
1607 ROLLBACK TO delete_subscription;
1608 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1609 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1610 THEN
1611 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_DI_SUBSCRIPTION_PVT',
1612 p_procedure_name => 'DELETE_SUBSCRIPTION',
1613 p_error_text => SUBSTR(SQLERRM,1,240));
1614 END IF;
1615 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1616 p_count => x_msg_count,
1617 p_data => X_msg_data);
1618
1619 END DELETE_SUBSCRIPTION;
1620
1621 END AHL_DI_SUBSCRIPTION_PVT;
[Home] [Help]