[Home] [Help]
PACKAGE BODY: APPS.AHL_SUBSCRIPTIONS_PKG
Source
1 package body AHL_SUBSCRIPTIONS_PKG as
2 /* $Header: AHLLSUBB.pls 115.5 2003/04/04 12:10:06 adharia noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_SUBSCRIPTION_ID in NUMBER,
6 X_ATTRIBUTE5 in VARCHAR2,
7 X_DOCUMENT_ID in NUMBER,
8 X_REQUESTED_BY_PARTY_ID in NUMBER,
9 X_ATTRIBUTE6 in VARCHAR2,
10 X_ATTRIBUTE7 in VARCHAR2,
11 X_ATTRIBUTE8 in VARCHAR2,
12 X_ATTRIBUTE9 in VARCHAR2,
13 X_ATTRIBUTE10 in VARCHAR2,
14 X_ATTRIBUTE11 in VARCHAR2,
15 X_ATTRIBUTE12 in VARCHAR2,
16 X_ATTRIBUTE13 in VARCHAR2,
17 X_ATTRIBUTE14 in VARCHAR2,
18 X_ATTRIBUTE_CATEGORY in VARCHAR2,
19 X_ATTRIBUTE1 in VARCHAR2,
20 X_ATTRIBUTE2 in VARCHAR2,
21 X_ATTRIBUTE3 in VARCHAR2,
22 X_ATTRIBUTE4 in VARCHAR2,
23 X_OBJECT_VERSION_NUMBER in NUMBER,
24 X_ATTRIBUTE15 in VARCHAR2,
25 X_SUBSCRIBED_FRM_PARTY_ID in NUMBER,
26 X_QUANTITY in NUMBER,
27 X_STATUS_CODE in VARCHAR2,
28 X_PURCHASE_ORDER_NO in VARCHAR2,
29 X_FREQUENCY_CODE in VARCHAR2,
30 X_SUBSCRIPTION_TYPE_CODE in VARCHAR2,
31 X_MEDIA_TYPE_CODE in VARCHAR2,
32 X_START_DATE in DATE,
33 X_END_DATE in DATE,
34 X_COMMENTS in VARCHAR2,
35 X_CREATION_DATE in DATE,
36 X_CREATED_BY in NUMBER,
37 X_LAST_UPDATE_DATE in DATE,
38 X_LAST_UPDATED_BY in NUMBER,
39 X_LAST_UPDATE_LOGIN in NUMBER
40 ) is
41 cursor C is select ROWID from AHL_SUBSCRIPTIONS_B
42 where SUBSCRIPTION_ID = X_SUBSCRIPTION_ID
43 ;
44 begin
45 insert into AHL_SUBSCRIPTIONS_B (
46 ATTRIBUTE5,
47 SUBSCRIPTION_ID,
48 DOCUMENT_ID,
49 REQUESTED_BY_PARTY_ID,
50 ATTRIBUTE6,
51 ATTRIBUTE7,
52 ATTRIBUTE8,
53 ATTRIBUTE9,
54 ATTRIBUTE10,
55 ATTRIBUTE11,
56 ATTRIBUTE12,
57 ATTRIBUTE13,
58 ATTRIBUTE14,
59 ATTRIBUTE_CATEGORY,
60 ATTRIBUTE1,
61 ATTRIBUTE2,
62 ATTRIBUTE3,
63 ATTRIBUTE4,
64 OBJECT_VERSION_NUMBER,
65 ATTRIBUTE15,
66 SUBSCRIBED_FRM_PARTY_ID,
67 QUANTITY,
68 STATUS_CODE,
69 PURCHASE_ORDER_NO,
70 FREQUENCY_CODE,
71 SUBSCRIPTION_TYPE_CODE,
72 MEDIA_TYPE_CODE,
73 START_DATE,
74 END_DATE,
75 CREATION_DATE,
76 CREATED_BY,
77 LAST_UPDATE_DATE,
78 LAST_UPDATED_BY,
79 LAST_UPDATE_LOGIN
80 ) values (
81 X_ATTRIBUTE5,
82 X_SUBSCRIPTION_ID,
83 X_DOCUMENT_ID,
84 X_REQUESTED_BY_PARTY_ID,
85 X_ATTRIBUTE6,
86 X_ATTRIBUTE7,
87 X_ATTRIBUTE8,
88 X_ATTRIBUTE9,
89 X_ATTRIBUTE10,
90 X_ATTRIBUTE11,
91 X_ATTRIBUTE12,
92 X_ATTRIBUTE13,
93 X_ATTRIBUTE14,
94 X_ATTRIBUTE_CATEGORY,
95 X_ATTRIBUTE1,
96 X_ATTRIBUTE2,
97 X_ATTRIBUTE3,
98 X_ATTRIBUTE4,
99 X_OBJECT_VERSION_NUMBER,
100 X_ATTRIBUTE15,
101 X_SUBSCRIBED_FRM_PARTY_ID,
102 X_QUANTITY,
103 X_STATUS_CODE,
104 X_PURCHASE_ORDER_NO,
105 X_FREQUENCY_CODE,
106 X_SUBSCRIPTION_TYPE_CODE,
107 X_MEDIA_TYPE_CODE,
108 X_START_DATE,
109 X_END_DATE,
110 X_CREATION_DATE,
111 X_CREATED_BY,
112 X_LAST_UPDATE_DATE,
113 X_LAST_UPDATED_BY,
114 X_LAST_UPDATE_LOGIN
115 );
116
117 insert into AHL_SUBSCRIPTIONS_TL (
118 CREATION_DATE,
119 CREATED_BY,
120 LAST_UPDATE_LOGIN,
121 COMMENTS,
122 SUBSCRIPTION_ID,
123 LAST_UPDATE_DATE,
124 LAST_UPDATED_BY,
125 LANGUAGE,
126 SOURCE_LANG
127 ) select
128 X_CREATION_DATE,
129 X_CREATED_BY,
130 X_LAST_UPDATE_LOGIN,
131 X_COMMENTS,
132 X_SUBSCRIPTION_ID,
133 X_LAST_UPDATE_DATE,
134 X_LAST_UPDATED_BY,
135 L.LANGUAGE_CODE,
136 userenv('LANG')
137 from FND_LANGUAGES L
138 where L.INSTALLED_FLAG in ('I', 'B')
139 and not exists
140 (select NULL
141 from AHL_SUBSCRIPTIONS_TL T
142 where T.SUBSCRIPTION_ID = X_SUBSCRIPTION_ID
143 and T.LANGUAGE = L.LANGUAGE_CODE);
144
145 open c;
146 fetch c into X_ROWID;
147 if (c%notfound) then
148 close c;
149 raise no_data_found;
150 end if;
151 close c;
152
153 end INSERT_ROW;
154
155 procedure LOCK_ROW (
156 X_SUBSCRIPTION_ID in NUMBER,
157 X_ATTRIBUTE5 in VARCHAR2,
158 X_DOCUMENT_ID in NUMBER,
159 X_REQUESTED_BY_PARTY_ID in NUMBER,
160 X_ATTRIBUTE6 in VARCHAR2,
161 X_ATTRIBUTE7 in VARCHAR2,
162 X_ATTRIBUTE8 in VARCHAR2,
163 X_ATTRIBUTE9 in VARCHAR2,
164 X_ATTRIBUTE10 in VARCHAR2,
165 X_ATTRIBUTE11 in VARCHAR2,
166 X_ATTRIBUTE12 in VARCHAR2,
167 X_ATTRIBUTE13 in VARCHAR2,
168 X_ATTRIBUTE14 in VARCHAR2,
169 X_ATTRIBUTE_CATEGORY in VARCHAR2,
170 X_ATTRIBUTE1 in VARCHAR2,
171 X_ATTRIBUTE2 in VARCHAR2,
172 X_ATTRIBUTE3 in VARCHAR2,
173 X_ATTRIBUTE4 in VARCHAR2,
174 X_OBJECT_VERSION_NUMBER in NUMBER,
175 X_ATTRIBUTE15 in VARCHAR2,
176 X_SUBSCRIBED_FRM_PARTY_ID in NUMBER,
177 X_QUANTITY in NUMBER,
178 X_STATUS_CODE in VARCHAR2,
179 X_PURCHASE_ORDER_NO in VARCHAR2,
180 X_FREQUENCY_CODE in VARCHAR2,
181 X_SUBSCRIPTION_TYPE_CODE in VARCHAR2,
182 X_MEDIA_TYPE_CODE in VARCHAR2,
183 X_START_DATE in DATE,
184 X_END_DATE in DATE,
185 X_COMMENTS in VARCHAR2
186 ) is
187 cursor c is select
188 ATTRIBUTE5,
189 DOCUMENT_ID,
190 REQUESTED_BY_PARTY_ID,
191 ATTRIBUTE6,
192 ATTRIBUTE7,
193 ATTRIBUTE8,
194 ATTRIBUTE9,
195 ATTRIBUTE10,
196 ATTRIBUTE11,
197 ATTRIBUTE12,
198 ATTRIBUTE13,
199 ATTRIBUTE14,
200 ATTRIBUTE_CATEGORY,
201 ATTRIBUTE1,
202 ATTRIBUTE2,
203 ATTRIBUTE3,
204 ATTRIBUTE4,
205 OBJECT_VERSION_NUMBER,
206 ATTRIBUTE15,
207 SUBSCRIBED_FRM_PARTY_ID,
208 QUANTITY,
209 STATUS_CODE,
210 PURCHASE_ORDER_NO,
211 FREQUENCY_CODE,
212 SUBSCRIPTION_TYPE_CODE,
213 MEDIA_TYPE_CODE,
214 START_DATE,
215 END_DATE
216 from AHL_SUBSCRIPTIONS_B
217 where SUBSCRIPTION_ID = X_SUBSCRIPTION_ID
218 for update of SUBSCRIPTION_ID nowait;
219 recinfo c%rowtype;
220
221 cursor c1 is select
222 COMMENTS,
223 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
224 from AHL_SUBSCRIPTIONS_TL
225 where SUBSCRIPTION_ID = X_SUBSCRIPTION_ID
226 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
227 for update of SUBSCRIPTION_ID nowait;
228 begin
229 open c;
230 fetch c into recinfo;
231 if (c%notfound) then
232 close c;
233 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
234 app_exception.raise_exception;
235 end if;
236 close c;
237 if ( ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
238 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
239 AND (recinfo.DOCUMENT_ID = X_DOCUMENT_ID)
240 AND (recinfo.REQUESTED_BY_PARTY_ID = X_REQUESTED_BY_PARTY_ID)
241 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
242 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
243 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
244 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
245 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
246 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
247 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
248 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
249 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
250 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
251 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
252 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
253 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
254 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
255 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
256 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
257 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
258 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
259 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
260 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
261 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
262 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
263 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
264 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
265 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
266 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
267 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
268 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
269 AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
270 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
271 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
272 AND ((recinfo.SUBSCRIBED_FRM_PARTY_ID = X_SUBSCRIBED_FRM_PARTY_ID)
273 OR ((recinfo.SUBSCRIBED_FRM_PARTY_ID is null) AND (X_SUBSCRIBED_FRM_PARTY_ID is null)))
274 AND (recinfo.QUANTITY = X_QUANTITY)
275 AND (recinfo.STATUS_CODE = X_STATUS_CODE)
276 AND ((recinfo.PURCHASE_ORDER_NO = X_PURCHASE_ORDER_NO)
277 OR ((recinfo.PURCHASE_ORDER_NO is null) AND (X_PURCHASE_ORDER_NO is null)))
278 AND ((recinfo.FREQUENCY_CODE = X_FREQUENCY_CODE)
279 OR ((recinfo.FREQUENCY_CODE is null) AND (X_FREQUENCY_CODE is null)))
280 AND ((recinfo.SUBSCRIPTION_TYPE_CODE = X_SUBSCRIPTION_TYPE_CODE)
281 OR ((recinfo.SUBSCRIPTION_TYPE_CODE is null) AND (X_SUBSCRIPTION_TYPE_CODE is null)))
282 AND ((recinfo.MEDIA_TYPE_CODE = X_MEDIA_TYPE_CODE)
283 OR ((recinfo.MEDIA_TYPE_CODE is null) AND (X_MEDIA_TYPE_CODE is null)))
284 AND ((recinfo.START_DATE = X_START_DATE)
285 OR ((recinfo.START_DATE is null) AND (X_START_DATE is null)))
286 AND ((recinfo.END_DATE = X_END_DATE)
287 OR ((recinfo.END_DATE is null) AND (X_END_DATE is null)))
288 ) then
289 null;
290 else
291 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
292 app_exception.raise_exception;
293 end if;
294
295 for tlinfo in c1 loop
296 if (tlinfo.BASELANG = 'Y') then
297 if ( ((tlinfo.COMMENTS = X_COMMENTS)
298 OR ((tlinfo.COMMENTS is null) AND (X_COMMENTS is null)))
299 ) then
300 null;
301 else
302 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
303 app_exception.raise_exception;
304 end if;
305 end if;
306 end loop;
307 return;
308 end LOCK_ROW;
309
310 procedure UPDATE_ROW (
311 X_SUBSCRIPTION_ID in NUMBER,
312 X_ATTRIBUTE5 in VARCHAR2,
313 X_DOCUMENT_ID in NUMBER,
314 X_REQUESTED_BY_PARTY_ID in NUMBER,
315 X_ATTRIBUTE6 in VARCHAR2,
316 X_ATTRIBUTE7 in VARCHAR2,
317 X_ATTRIBUTE8 in VARCHAR2,
318 X_ATTRIBUTE9 in VARCHAR2,
319 X_ATTRIBUTE10 in VARCHAR2,
320 X_ATTRIBUTE11 in VARCHAR2,
321 X_ATTRIBUTE12 in VARCHAR2,
322 X_ATTRIBUTE13 in VARCHAR2,
323 X_ATTRIBUTE14 in VARCHAR2,
324 X_ATTRIBUTE_CATEGORY in VARCHAR2,
325 X_ATTRIBUTE1 in VARCHAR2,
326 X_ATTRIBUTE2 in VARCHAR2,
327 X_ATTRIBUTE3 in VARCHAR2,
328 X_ATTRIBUTE4 in VARCHAR2,
329 X_OBJECT_VERSION_NUMBER in NUMBER,
330 X_ATTRIBUTE15 in VARCHAR2,
331 X_SUBSCRIBED_FRM_PARTY_ID in NUMBER,
332 X_QUANTITY in NUMBER,
333 X_STATUS_CODE in VARCHAR2,
334 X_PURCHASE_ORDER_NO in VARCHAR2,
335 X_FREQUENCY_CODE in VARCHAR2,
336 X_SUBSCRIPTION_TYPE_CODE in VARCHAR2,
337 X_MEDIA_TYPE_CODE in VARCHAR2,
338 X_START_DATE in DATE,
339 X_END_DATE in DATE,
340 X_COMMENTS in VARCHAR2,
341 X_LAST_UPDATE_DATE in DATE,
342 X_LAST_UPDATED_BY in NUMBER,
343 X_LAST_UPDATE_LOGIN in NUMBER
344 ) is
345 begin
346 update AHL_SUBSCRIPTIONS_B set
347 ATTRIBUTE5 = X_ATTRIBUTE5,
348 DOCUMENT_ID = X_DOCUMENT_ID,
349 REQUESTED_BY_PARTY_ID = X_REQUESTED_BY_PARTY_ID,
350 ATTRIBUTE6 = X_ATTRIBUTE6,
351 ATTRIBUTE7 = X_ATTRIBUTE7,
352 ATTRIBUTE8 = X_ATTRIBUTE8,
353 ATTRIBUTE9 = X_ATTRIBUTE9,
354 ATTRIBUTE10 = X_ATTRIBUTE10,
355 ATTRIBUTE11 = X_ATTRIBUTE11,
356 ATTRIBUTE12 = X_ATTRIBUTE12,
357 ATTRIBUTE13 = X_ATTRIBUTE13,
358 ATTRIBUTE14 = X_ATTRIBUTE14,
359 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
360 ATTRIBUTE1 = X_ATTRIBUTE1,
361 ATTRIBUTE2 = X_ATTRIBUTE2,
362 ATTRIBUTE3 = X_ATTRIBUTE3,
363 ATTRIBUTE4 = X_ATTRIBUTE4,
364 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
365 ATTRIBUTE15 = X_ATTRIBUTE15,
366 SUBSCRIBED_FRM_PARTY_ID = X_SUBSCRIBED_FRM_PARTY_ID,
367 QUANTITY = X_QUANTITY,
368 STATUS_CODE = X_STATUS_CODE,
369 PURCHASE_ORDER_NO = X_PURCHASE_ORDER_NO,
370 FREQUENCY_CODE = X_FREQUENCY_CODE,
371 SUBSCRIPTION_TYPE_CODE = X_SUBSCRIPTION_TYPE_CODE,
372 MEDIA_TYPE_CODE = X_MEDIA_TYPE_CODE,
373 START_DATE = X_START_DATE,
374 END_DATE = X_END_DATE,
375 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
376 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
377 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
378 where SUBSCRIPTION_ID = X_SUBSCRIPTION_ID;
379
380 if (sql%notfound) then
381 raise no_data_found;
382 end if;
383
384 update AHL_SUBSCRIPTIONS_TL set
385 COMMENTS = X_COMMENTS,
386 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
387 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
388 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
389 SOURCE_LANG = userenv('LANG')
390 where SUBSCRIPTION_ID = X_SUBSCRIPTION_ID
391 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
392
393 if (sql%notfound) then
394 raise no_data_found;
395 end if;
396 end UPDATE_ROW;
397
398 procedure DELETE_ROW (
399 X_SUBSCRIPTION_ID in NUMBER
400 ) is
401 begin
402 delete from AHL_SUBSCRIPTIONS_TL
403 where SUBSCRIPTION_ID = X_SUBSCRIPTION_ID;
404
405 if (sql%notfound) then
406 raise no_data_found;
407 end if;
408
409 delete from AHL_SUBSCRIPTIONS_B
410 where SUBSCRIPTION_ID = X_SUBSCRIPTION_ID;
411
412 if (sql%notfound) then
413 raise no_data_found;
414 end if;
415 end DELETE_ROW;
416
417 procedure ADD_LANGUAGE
418 is
419 begin
420 delete from AHL_SUBSCRIPTIONS_TL T
421 where not exists
422 (select NULL
423 from AHL_SUBSCRIPTIONS_B B
424 where B.SUBSCRIPTION_ID = T.SUBSCRIPTION_ID
425 );
426
427 update AHL_SUBSCRIPTIONS_TL T set (
428 COMMENTS
429 ) = (select
430 B.COMMENTS
431 from AHL_SUBSCRIPTIONS_TL B
432 where B.SUBSCRIPTION_ID = T.SUBSCRIPTION_ID
433 and B.LANGUAGE = T.SOURCE_LANG)
434 where (
435 T.SUBSCRIPTION_ID,
436 T.LANGUAGE
437 ) in (select
438 SUBT.SUBSCRIPTION_ID,
439 SUBT.LANGUAGE
440 from AHL_SUBSCRIPTIONS_TL SUBB, AHL_SUBSCRIPTIONS_TL SUBT
441 where SUBB.SUBSCRIPTION_ID = SUBT.SUBSCRIPTION_ID
442 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
443 and (SUBB.COMMENTS <> SUBT.COMMENTS
444 or (SUBB.COMMENTS is null and SUBT.COMMENTS is not null)
445 or (SUBB.COMMENTS is not null and SUBT.COMMENTS is null)
446 ));
447
448 insert into AHL_SUBSCRIPTIONS_TL (
449 CREATION_DATE,
450 CREATED_BY,
451 LAST_UPDATE_LOGIN,
452 COMMENTS,
453 SUBSCRIPTION_ID,
454 LAST_UPDATE_DATE,
455 LAST_UPDATED_BY,
456 LANGUAGE,
457 SOURCE_LANG
458 ) select
459 B.CREATION_DATE,
460 B.CREATED_BY,
461 B.LAST_UPDATE_LOGIN,
462 B.COMMENTS,
463 B.SUBSCRIPTION_ID,
464 B.LAST_UPDATE_DATE,
465 B.LAST_UPDATED_BY,
466 L.LANGUAGE_CODE,
467 B.SOURCE_LANG
468 from AHL_SUBSCRIPTIONS_TL B, FND_LANGUAGES L
469 where L.INSTALLED_FLAG in ('I', 'B')
470 and B.LANGUAGE = userenv('LANG')
471 and not exists
472 (select NULL
473 from AHL_SUBSCRIPTIONS_TL T
474 where T.SUBSCRIPTION_ID = B.SUBSCRIPTION_ID
475 and T.LANGUAGE = L.LANGUAGE_CODE);
476 end ADD_LANGUAGE;
477
478 procedure LOAD_ROW(
479 X_SUBSCRIPTION_ID in NUMBER,
480 X_ATTRIBUTE5 in VARCHAR2,
481 X_DOCUMENT_ID in NUMBER,
482 X_REQUESTED_BY_PARTY_ID in NUMBER,
483 X_ATTRIBUTE6 in VARCHAR2,
484 X_ATTRIBUTE7 in VARCHAR2,
485 X_ATTRIBUTE8 in VARCHAR2,
486 X_ATTRIBUTE9 in VARCHAR2,
487 X_ATTRIBUTE10 in VARCHAR2,
488 X_ATTRIBUTE11 in VARCHAR2,
489 X_ATTRIBUTE12 in VARCHAR2,
490 X_ATTRIBUTE13 in VARCHAR2,
491 X_ATTRIBUTE14 in VARCHAR2,
492 X_ATTRIBUTE_CATEGORY in VARCHAR2,
493 X_ATTRIBUTE1 in VARCHAR2,
494 X_ATTRIBUTE2 in VARCHAR2,
495 X_ATTRIBUTE3 in VARCHAR2,
496 X_ATTRIBUTE4 in VARCHAR2,
497 X_ATTRIBUTE15 in VARCHAR2,
498 X_SUBSCRIBED_FRM_PARTY_ID in NUMBER,
499 X_QUANTITY in NUMBER,
500 X_STATUS_CODE in VARCHAR2,
501 X_PURCHASE_ORDER_NO in VARCHAR2,
502 X_FREQUENCY_CODE in VARCHAR2,
503 X_SUBSCRIPTION_TYPE_CODE in VARCHAR2,
504 X_MEDIA_TYPE_CODE in VARCHAR2,
505 X_START_DATE in DATE,
506 X_END_DATE in DATE,
507 X_COMMENTS in VARCHAR2,
508
509 X_OWNER in VARCHAR2
510 )
511 IS
512 l_user_id number := 0;
513 l_obj_verno number;
514 l_dummy_char varchar2(1);
515 l_row_id varchar2(100);
516 l_obj_id number;
517
518
519 cursor c_obj_verno is
520 select object_version_number
521 from AHL_SUBSCRIPTIONS_B
522 where SUBSCRIPTION_ID = X_SUBSCRIPTION_ID;
523
524 cursor c_chk_rec_exists is
525 select 'x'
526 from AHL_SUBSCRIPTIONS_B
527 where SUBSCRIPTION_ID = X_SUBSCRIPTION_ID;
528
529 cursor c_get_rec_id is
530 select AHL_SUBSCRIPTIONS_B_S.NEXTVAL
531 from DUAL;
532
533 BEGIN
534
535 if X_OWNER = 'SEED' then
536 l_user_id := 1;
537 end if;
538
539 open c_chk_rec_exists;
540 fetch c_chk_rec_exists into l_dummy_char;
541 if c_chk_rec_exists%notfound
542 then
543 close c_chk_rec_exists;
544
545 if X_SUBSCRIPTION_ID is null then
546 open c_get_rec_id;
547 fetch c_get_rec_id into l_obj_id;
548 close c_get_rec_id;
549 else
550 l_obj_id := X_SUBSCRIPTION_ID;
551 end if ;
552
553 l_obj_verno := 1;
554
555 AHL_SUBSCRIPTIONS_PKG.INSERT_ROW (
556 X_ROWID => l_row_id ,
557 X_SUBSCRIPTION_ID => l_obj_id ,
558 X_ATTRIBUTE5 => X_ATTRIBUTE5 ,
559 X_DOCUMENT_ID => X_DOCUMENT_ID ,
560 X_REQUESTED_BY_PARTY_ID => X_REQUESTED_BY_PARTY_ID ,
561 X_ATTRIBUTE6 => X_ATTRIBUTE6 ,
562 X_ATTRIBUTE7 => X_ATTRIBUTE7 ,
563 X_ATTRIBUTE8 => X_ATTRIBUTE8 ,
564 X_ATTRIBUTE9 => X_ATTRIBUTE9 ,
565 X_ATTRIBUTE10 => X_ATTRIBUTE10 ,
566 X_ATTRIBUTE11 => X_ATTRIBUTE11 ,
567 X_ATTRIBUTE12 => X_ATTRIBUTE12 ,
568 X_ATTRIBUTE13 => X_ATTRIBUTE13 ,
569 X_ATTRIBUTE14 => X_ATTRIBUTE14 ,
570 X_ATTRIBUTE_CATEGORY => X_ATTRIBUTE_CATEGORY ,
571 X_ATTRIBUTE1 => X_ATTRIBUTE1 ,
572 X_ATTRIBUTE2 => X_ATTRIBUTE2 ,
573 X_ATTRIBUTE3 => X_ATTRIBUTE3 ,
574 X_ATTRIBUTE4 => X_ATTRIBUTE4 ,
575 X_OBJECT_VERSION_NUMBER => l_obj_verno ,
576 X_ATTRIBUTE15 => X_ATTRIBUTE15 ,
577 X_SUBSCRIBED_FRM_PARTY_ID => X_SUBSCRIBED_FRM_PARTY_ID ,
578 X_QUANTITY => X_QUANTITY ,
579 X_STATUS_CODE => X_STATUS_CODE ,
580 X_PURCHASE_ORDER_NO => X_PURCHASE_ORDER_NO ,
581 X_FREQUENCY_CODE => X_FREQUENCY_CODE ,
582 X_SUBSCRIPTION_TYPE_CODE => X_SUBSCRIPTION_TYPE_CODE ,
583 X_MEDIA_TYPE_CODE => X_MEDIA_TYPE_CODE ,
584 X_START_DATE => X_START_DATE ,
585 X_END_DATE => X_END_DATE ,
586 X_COMMENTS => X_COMMENTS ,
587
588 X_CREATION_DATE => SYSDATE ,
589 X_CREATED_BY => l_user_id ,
590 X_LAST_UPDATE_DATE => SYSDATE ,
591 X_LAST_UPDATED_BY => l_user_id ,
592 X_LAST_UPDATE_LOGIN => 0
593 );
594
595 else
596 close c_chk_rec_exists;
597 open c_obj_verno;
598 fetch c_obj_verno into l_obj_verno;
599 close c_obj_verno;
600
601 l_obj_verno := l_obj_verno +1;
602
603 AHL_SUBSCRIPTIONS_PKG.UPDATE_ROW (
604 X_SUBSCRIPTION_ID => X_SUBSCRIPTION_ID ,
605 X_ATTRIBUTE5 => X_ATTRIBUTE5 ,
606 X_DOCUMENT_ID => X_DOCUMENT_ID ,
607 X_REQUESTED_BY_PARTY_ID => X_REQUESTED_BY_PARTY_ID ,
608 X_ATTRIBUTE6 => X_ATTRIBUTE6 ,
609 X_ATTRIBUTE7 => X_ATTRIBUTE7 ,
610 X_ATTRIBUTE8 => X_ATTRIBUTE8 ,
611 X_ATTRIBUTE9 => X_ATTRIBUTE9 ,
612 X_ATTRIBUTE10 => X_ATTRIBUTE10 ,
613 X_ATTRIBUTE11 => X_ATTRIBUTE11 ,
614 X_ATTRIBUTE12 => X_ATTRIBUTE12 ,
615 X_ATTRIBUTE13 => X_ATTRIBUTE13 ,
616 X_ATTRIBUTE14 => X_ATTRIBUTE14 ,
617 X_ATTRIBUTE_CATEGORY => X_ATTRIBUTE_CATEGORY ,
618 X_ATTRIBUTE1 => X_ATTRIBUTE1 ,
619 X_ATTRIBUTE2 => X_ATTRIBUTE2 ,
620 X_ATTRIBUTE3 => X_ATTRIBUTE3 ,
621 X_ATTRIBUTE4 => X_ATTRIBUTE4 ,
622 X_OBJECT_VERSION_NUMBER => l_obj_verno ,
623 X_ATTRIBUTE15 => X_ATTRIBUTE15 ,
624 X_SUBSCRIBED_FRM_PARTY_ID => X_SUBSCRIBED_FRM_PARTY_ID ,
625 X_QUANTITY => X_QUANTITY ,
626 X_STATUS_CODE => X_STATUS_CODE ,
627 X_PURCHASE_ORDER_NO => X_PURCHASE_ORDER_NO ,
628 X_FREQUENCY_CODE => X_FREQUENCY_CODE ,
629 X_SUBSCRIPTION_TYPE_CODE => X_SUBSCRIPTION_TYPE_CODE ,
630 X_MEDIA_TYPE_CODE => X_MEDIA_TYPE_CODE ,
631 X_START_DATE => X_START_DATE ,
632 X_END_DATE => X_END_DATE ,
633 X_COMMENTS => X_COMMENTS ,
634
635 X_LAST_UPDATE_DATE => SYSDATE ,
636 X_LAST_UPDATED_BY => l_user_id ,
637 X_LAST_UPDATE_LOGIN => 0
638 );
639 end if;
640 END LOAD_ROW ;
641
642 procedure TRANSLATE_ROW(
643 X_SUBSCRIPTION_ID in NUMBER,
644 X_COMMENTS in VARCHAR2,
645 X_DESCRIPTION in VARCHAR2,
646 X_OWNER in VARCHAR2
647 ) IS
648
649 begin
650 update AHL_SUBSCRIPTIONS_TL set
651 comments = nvl(X_COMMENTS, comments),
652 source_lang = userenv('LANG'),
653 last_update_date = sysdate,
654 last_updated_by = decode(x_owner, 'SEED', 1, 0),
655 last_update_login = 0
656 where SUBSCRIPTION_ID = X_SUBSCRIPTION_ID
657 and userenv('LANG') in (language, source_lang);
658 end TRANSLATE_ROW;
659
660 end AHL_SUBSCRIPTIONS_PKG;