[Home] [Help]
PACKAGE BODY: APPS.IBW_REFERRAL_PVT
Source
1 PACKAGE BODY IBW_REFERRAL_PVT AS
2 /* $Header: IBWREFB.pls 120.10 2005/12/15 00:57 vekancha noship $*/
3
4
5 --
6 --
7 -- Start of Comments
8 --
9 -- NAME
10 -- IBW_REFERRAL_PVT
11 --
12 -- PURPOSE
13 -- Private API for inserting records into referral categories and also referral patterns. Mainly used by offline engine
14 --
15 -- NOTES
16 -- Offline engine uses this API to insert records into IBW_REFERRAL_CATEGORIES_B, IBW_REFERRAL_CATEGORIES_TL,
17 -- IBW_REFERRAL_PATTERNS_B, IBW_REFERRAL_PATTERNS_TL
18
19 -- HISTORY
20 -- 05/10/2005 VEKANCHA Created
21
22 -- **************************************************************************
23
24 G_PKG_NAME CONSTANT VARCHAR2(30):= 'IBW_REFERRAL_PVT';
25 G_FILE_NAME CONSTANT VARCHAR2(12):= 'IBWREFB.pls';
26
27
28 -- ****************************************************************************
29 -- ****************************************************************************
30 -- TABLE HANDLERS
31 -- 1. insert_row
32 -- ****************************************************************************
33 -- ****************************************************************************
34
35
36 -- ****************************************************************************
37 -- insert row into referral categories and referral patterns tables
38 -- ****************************************************************************
39
40 PROCEDURE insert_row (
41 referral_category_id OUT NOCOPY NUMBER,
42 x_referral_category_name IN VARCHAR2,
43 x_referral_pattern IN VARCHAR2,
44 error_messages OUT NOCOPY VARCHAR2
45 )
46
47 IS
48
49 x_ref_cat_id NUMBER;
50 x_ref_pat_id NUMBER;
51 x_status VARCHAR2(1);
52 x_user_def_flag VARCHAR2(1);
53 created_by NUMBER;
54 creation_date DATE;
55 last_updated_by NUMBER;
56 last_update_date DATE;
57 last_update_login NUMBER;
58 obj_ver_number NUMBER;
59 program_id NUMBER;
60 program_login_id NUMBER;
61 program_app_id NUMBER;
62 request_id NUMBER;
63
64 CURSOR c IS SELECT ibw_referral_categories_b_s1.nextval FROM dual;
65
66 CURSOR c1 IS SELECT ibw_url_patterns_b_s1.nextval FROM dual;
67
68 CURSOR c2 IS SELECT referral_category_id FROM ibw_referral_categories_b
69 WHERE referral_category_id = x_ref_cat_id;
70
71 BEGIN
72 x_status := 'Y';
73 x_user_def_flag := 'N';
74 obj_ver_number := 1;
75 OPEN c;
76 FETCH c INTO x_ref_cat_id;
77 CLOSE c;
78
79 OPEN c1;
80 FETCH c1 into x_ref_pat_id;
81 CLOSE c1;
82
83 FND_PROFILE.GET('USER_ID', created_by);
84
85 creation_date := SYSDATE;
86
87 last_updated_by := created_by;
88
89 last_update_date := SYSDATE;
90
91 FND_PROFILE.GET('LOGIN_ID', last_update_login);
92
93 FND_PROFILE.GET('CONC_PROGRAM_ID', program_id);
94
95 FND_PROFILE.GET('CONC_LOGIN_ID', program_login_id);
96
97 FND_PROFILE.GET('CONC_PROGRAM_APPLICATION_ID', program_app_id);
98
99 FND_PROFILE.GET('CONC_REQUEST_ID', request_id);
100
101 INSERT INTO ibw_referral_categories_b (referral_category_id, status, user_defined_flag, created_by, creation_date,
102 last_updated_by, last_update_date, last_update_login, object_version_number,
103 program_id, program_login_id, program_application_id, request_id)
104 VALUES (x_ref_cat_id, x_status, x_user_def_flag, created_by, creation_date,
105 last_updated_by, last_update_date, last_update_login, obj_ver_number,
106 program_id, program_login_id, program_app_id, request_id);
107
108 INSERT INTO ibw_referral_categories_tl(referral_category_id, language, source_lang, referral_category_name, description, created_by, creation_date,
109 last_updated_by, last_update_date, last_update_login, object_version_number,
110 program_id, program_login_id, program_application_id, request_id)
111 SELECT M.referral_category_id, l.language_code, b.language_code, x_referral_category_name, NULL, M.created_by, M.creation_date,
112 M.last_updated_by, M.last_update_date, M.last_update_login, M.object_version_number,
113 M.program_id, M.program_login_id, M.program_application_id, M.request_id
114 FROM ibw_referral_categories_b M, fnd_languages l, fnd_languages b
115 WHERE l.installed_flag IN ('I','B') AND b.installed_flag='B' AND M.referral_category_id=x_ref_cat_id;
116
117
118 INSERT INTO ibw_url_patterns_b (url_pattern_id, url_pattern, type_id, type, created_by, creation_date,
119 last_updated_by, last_update_date, last_update_login, object_version_number,
120 program_id, program_login_id, program_application_id, request_id)
121 VALUES (x_ref_pat_id, x_referral_pattern, x_ref_cat_id, 'R', created_by, creation_date,
122 last_updated_by, last_update_date, last_update_login, obj_ver_number,
123 program_id, program_login_id, program_app_id, request_id);
124
125 INSERT INTO ibw_url_patterns_tl(url_pattern_id, language, source_lang, description, created_by, creation_date,
126 last_updated_by, last_update_date, last_update_login, object_version_number,
127 program_id, program_login_id, program_application_id, request_id)
128 SELECT M.url_pattern_id, l.language_code, b.language_code, NULL, M.created_by, M.creation_date,
129 M.last_updated_by, M.last_update_date, M.last_update_login, M.object_version_number,
130 M.program_id, M.program_login_id, M.program_application_id, M.request_id
131 FROM ibw_url_patterns_b M, fnd_languages l, fnd_languages b
132 WHERE l.installed_flag IN ('I','B') AND b.installed_flag='B' AND M.url_pattern_id=x_ref_pat_id;
133
134
135 OPEN c2;
136 FETCH c2 INTO referral_category_id;
137 IF (c2%NOTFOUND) THEN
138 CLOSE c2;
139 ROLLBACK;
140 RAISE NO_DATA_FOUND;
141 END IF;
142 CLOSE c2;
143
144 -- COMMIT;
145
146 END insert_row;
147
148
149 procedure ADD_LANGUAGE
150 is
151 begin
152 delete from IBW_REFERRAL_CATEGORIES_TL T
153 where not exists
154 (select NULL
155 from IBW_REFERRAL_CATEGORIES_B B
156 where B.REFERRAL_CATEGORY_ID = T.REFERRAL_CATEGORY_ID
157 );
158
159 update IBW_REFERRAL_CATEGORIES_TL T set (
160 REFERRAL_CATEGORY_NAME,
161 DESCRIPTION
162 ) = (select
163 B.REFERRAL_CATEGORY_NAME,
164 B.DESCRIPTION
165 from IBW_REFERRAL_CATEGORIES_TL B
166 where B.REFERRAL_CATEGORY_ID = T.REFERRAL_CATEGORY_ID
167 and B.LANGUAGE = T.SOURCE_LANG)
168 where (
169 T.REFERRAL_CATEGORY_ID,
170 T.LANGUAGE
171 ) in (select
172 SUBT.REFERRAL_CATEGORY_ID,
173 SUBT.LANGUAGE
174 from IBW_REFERRAL_CATEGORIES_TL SUBB, IBW_REFERRAL_CATEGORIES_TL SUBT
175 where SUBB.REFERRAL_CATEGORY_ID = SUBT.REFERRAL_CATEGORY_ID
176 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
177 and (SUBB.REFERRAL_CATEGORY_NAME <> SUBT.REFERRAL_CATEGORY_NAME
178 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
179 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
180 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
181 ));
182
183 insert into IBW_REFERRAL_CATEGORIES_TL (
184 REFERRAL_CATEGORY_ID,
185 REFERRAL_CATEGORY_NAME,
186 DESCRIPTION,
187 OBJECT_VERSION_NUMBER,
188 CREATED_BY,
189 CREATION_DATE,
190 LAST_UPDATED_BY,
191 LAST_UPDATE_DATE,
192 LAST_UPDATE_LOGIN,
193 PROGRAM_ID,
194 PROGRAM_LOGIN_ID,
195 PROGRAM_APPLICATION_ID,
196 REQUEST_ID,
197 LANGUAGE,
198 SOURCE_LANG
199 ) select /*+ ORDERED */
200 B.REFERRAL_CATEGORY_ID,
201 B.REFERRAL_CATEGORY_NAME,
202 B.DESCRIPTION,
203 B.OBJECT_VERSION_NUMBER,
204 B.CREATED_BY,
205 B.CREATION_DATE,
206 B.LAST_UPDATED_BY,
207 B.LAST_UPDATE_DATE,
208 B.LAST_UPDATE_LOGIN,
209 B.PROGRAM_ID,
210 B.PROGRAM_LOGIN_ID,
211 B.PROGRAM_APPLICATION_ID,
212 B.REQUEST_ID,
213 L.LANGUAGE_CODE,
214 B.SOURCE_LANG
215 from IBW_REFERRAL_CATEGORIES_TL B, FND_LANGUAGES L
216 where L.INSTALLED_FLAG in ('I', 'B')
217 and B.LANGUAGE = userenv('LANG')
218 and not exists
219 (select NULL
220 from IBW_REFERRAL_CATEGORIES_TL T
221 where T.REFERRAL_CATEGORY_ID = B.REFERRAL_CATEGORY_ID
222 and T.LANGUAGE = L.LANGUAGE_CODE);
223 end ADD_LANGUAGE;
224
225 END IBW_REFERRAL_PVT;