[Home] [Help]
PACKAGE BODY: APPS.AS_SALES_LEAD_RANKS_PKG
Source
1 package body AS_SALES_LEAD_RANKS_PKG as
2 /* #$Header: asxtrnkb.pls 115.10 2002/11/19 22:24:11 chchandr ship $ */
3
4 AS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
5 AS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
6 AS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
7 AS_DEBUG_ERROR_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_ERROR);
8
9 procedure INSERT_ROW (
10 X_RANK_ID in OUT NOCOPY NUMBER,
11 X_MIN_SCORE in NUMBER,
12 X_MAX_SCORE in NUMBER,
13 X_ENABLED_FLAG in VARCHAR2,
14 X_MEANING in VARCHAR2,
15 X_DESCRIPTION IN VARCHAR2,
16 X_CREATION_DATE in DATE,
17 X_CREATED_BY in NUMBER,
18 X_LAST_UPDATE_DATE in DATE,
19 X_LAST_UPDATED_BY in NUMBER,
20 X_LAST_UPDATE_LOGIN in NUMBER
21 ) is
22 /*cursor C is select ROWID from AS_SALES_LEAD_RANKS_B
23 where RANK_ID = X_RANK_ID
24 ;
25 l_rowid number;
26 */
27 begin
28 insert into AS_SALES_LEAD_RANKS_B (
29 RANK_ID,
30 MIN_SCORE,
31 MAX_SCORE,
32 ENABLED_FLAG,
33 CREATION_DATE,
34 CREATED_BY,
35 LAST_UPDATE_DATE,
36 LAST_UPDATED_BY,
37 LAST_UPDATE_LOGIN
38 ) values (
39 X_RANK_ID,
40 X_MIN_SCORE,
41 X_MAX_SCORE,
42 X_ENABLED_FLAG,
43 X_CREATION_DATE,
44 X_CREATED_BY,
45 X_LAST_UPDATE_DATE,
46 X_LAST_UPDATED_BY,
47 X_LAST_UPDATE_LOGIN
48 );
49
50 insert into AS_SALES_LEAD_RANKS_TL (
51 RANK_ID,
52 LAST_UPDATE_DATE,
53 LAST_UPDATED_BY,
54 CREATION_DATE,
55 CREATED_BY,
56 LAST_UPDATE_LOGIN,
57 MEANING,
58 DESCRIPTION,
59 LANGUAGE,
60 SOURCE_LANG
61 ) select
62 X_RANK_ID,
63 X_LAST_UPDATE_DATE,
64 X_LAST_UPDATED_BY,
65 X_CREATION_DATE,
66 X_CREATED_BY,
67 X_LAST_UPDATE_LOGIN,
68 X_MEANING,
69 X_DESCRIPTION,
70 L.LANGUAGE_CODE,
71 userenv('LANG')
72 from FND_LANGUAGES L
73 where L.INSTALLED_FLAG in ('I', 'B')
74 and not exists
75 (select NULL
76 from AS_SALES_LEAD_RANKS_TL T
77 where T.RANK_ID = X_RANK_ID
78 and T.LANGUAGE = L.LANGUAGE_CODE);
79
80 /*
81 open c;
82 fetch c into l_ROWID;
83 if (c%notfound) then
84 close c;
85 raise no_data_found;
86 end if;
87 close c;
88 */
89 end INSERT_ROW;
90
91 procedure LOCK_ROW (
92 X_RANK_ID in NUMBER,
93 X_MIN_SCORE in NUMBER,
94 X_MAX_SCORE in NUMBER,
95 X_ENABLED_FLAG in VARCHAR2,
96 X_MEANING in VARCHAR2
97 ) is
98 cursor c is select
99 MIN_SCORE,
100 MAX_SCORE,
101 ENABLED_FLAG
102 from AS_SALES_LEAD_RANKS_B
103 where RANK_ID = X_RANK_ID
104 for update of RANK_ID nowait;
105 recinfo c%rowtype;
106
107 cursor c1 is select
108 MEANING,
109 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
110 from AS_SALES_LEAD_RANKS_TL
111 where RANK_ID = X_RANK_ID
112 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
113 for update of RANK_ID nowait;
114 begin
115 open c;
116 fetch c into recinfo;
117 if (c%notfound) then
118 close c;
119 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
120 app_exception.raise_exception;
121 end if;
122 close c;
123 if ((recinfo.MIN_SCORE = X_MIN_SCORE)
124 AND (recinfo.MAX_SCORE = X_MAX_SCORE)
125 AND (recinfo.ENABLED_FLAG = X_ENABLED_FLAG)
126 ) then
127 null;
128 else
129 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
130 app_exception.raise_exception;
131 end if;
132
133 for tlinfo in c1 loop
134 if (tlinfo.BASELANG = 'Y') then
135 if ( (tlinfo.MEANING = X_MEANING)
136 ) then
137 null;
138 else
139 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
140 app_exception.raise_exception;
141 end if;
142 end if;
143 end loop;
144 return;
145 end LOCK_ROW;
146
147 procedure UPDATE_ROW (
148 X_RANK_ID in NUMBER,
149 X_MIN_SCORE in NUMBER,
150 X_MAX_SCORE in NUMBER,
151 X_ENABLED_FLAG in VARCHAR2,
152 X_MEANING in VARCHAR2,
153 X_DESCRIPTION in VARCHAR2,
154 X_LAST_UPDATE_DATE in DATE,
155 X_LAST_UPDATED_BY in NUMBER,
156 X_LAST_UPDATE_LOGIN in NUMBER
157 ) is
158 begin
159 update AS_SALES_LEAD_RANKS_B set
160 MIN_SCORE = X_MIN_SCORE,
161 MAX_SCORE = X_MAX_SCORE,
162 ENABLED_FLAG = X_ENABLED_FLAG,
163 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
164 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
165 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
166 where RANK_ID = X_RANK_ID;
167
168 if (sql%notfound) then
169 raise no_data_found;
170 end if;
171
172 update AS_SALES_LEAD_RANKS_TL set
173 MEANING = X_MEANING,
174 DESCRIPTION = X_DESCRIPTION,
175 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
176 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
177 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
178 SOURCE_LANG = userenv('LANG')
179 where RANK_ID = X_RANK_ID
180 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
181
182 if (sql%notfound) then
183 raise no_data_found;
184 end if;
185 end UPDATE_ROW;
186
187 procedure DELETE_ROW (
188 X_RANK_ID in NUMBER
189 ) is
190 begin
191 delete from AS_SALES_LEAD_RANKS_TL
192 where RANK_ID = X_RANK_ID;
193
194 if (sql%notfound) then
195 raise no_data_found;
196 end if;
197
198 delete from AS_SALES_LEAD_RANKS_B
199 where RANK_ID = X_RANK_ID;
200
201 if (sql%notfound) then
202 raise no_data_found;
203 end if;
204 end DELETE_ROW;
205
206 procedure ADD_LANGUAGE
207 is
208 begin
209 delete from AS_SALES_LEAD_RANKS_TL T
210 where not exists
211 (select NULL
212 from AS_SALES_LEAD_RANKS_B B
213 where B.RANK_ID = T.RANK_ID
214 );
215
216 update AS_SALES_LEAD_RANKS_TL T set (
217 MEANING
218 ) = (select
219 B.MEANING
220 from AS_SALES_LEAD_RANKS_TL B
221 where B.RANK_ID = T.RANK_ID
222 and B.LANGUAGE = T.SOURCE_LANG)
223 where (
224 T.RANK_ID,
225 T.LANGUAGE
226 ) in (select
227 SUBT.RANK_ID,
228 SUBT.LANGUAGE
229 from AS_SALES_LEAD_RANKS_TL SUBB, AS_SALES_LEAD_RANKS_TL SUBT
230 where SUBB.RANK_ID = SUBT.RANK_ID
231 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
232 and (SUBB.MEANING <> SUBT.MEANING
233 ));
234
235 insert into AS_SALES_LEAD_RANKS_TL (
236 --SECURITY_GROUP_ID,
237 RANK_ID,
238 LAST_UPDATE_DATE,
239 LAST_UPDATED_BY,
240 CREATION_DATE,
241 CREATED_BY,
242 LAST_UPDATE_LOGIN,
243 MEANING,
244 DESCRIPTION,
245 LANGUAGE,
246 SOURCE_LANG
247 ) select
248 -- B.SECURITY_GROUP_ID,
249 B.RANK_ID,
250 B.LAST_UPDATE_DATE,
251 B.LAST_UPDATED_BY,
252 B.CREATION_DATE,
253 B.CREATED_BY,
254 B.LAST_UPDATE_LOGIN,
255 B.MEANING,
256 B.DESCRIPTION,
257 L.LANGUAGE_CODE,
258 B.SOURCE_LANG
259 from AS_SALES_LEAD_RANKS_TL B, FND_LANGUAGES L
260 where L.INSTALLED_FLAG in ('I', 'B')
261 and B.LANGUAGE = userenv('LANG')
262 and not exists
263 (select NULL
264 from AS_SALES_LEAD_RANKS_TL T
265 where T.RANK_ID = B.RANK_ID
266 and T.LANGUAGE = L.LANGUAGE_CODE);
267 end ADD_LANGUAGE;
268
269 PROCEDURE Load_Row (
270 X_RANK_ID in OUT NOCOPY NUMBER,
271 X_MIN_SCORE in NUMBER,
272 X_MAX_SCORE in NUMBER,
273 X_ENABLED_FLAG in VARCHAR2,
274 X_MEANING in VARCHAR2,
275 X_DESCRIPTION IN VARCHAR2,
276 X_OWNER in VARCHAR2)
277 IS
278 user_id number := 0;
279 row_id varchar2(64);
280
281 -- FFANG 112700 FOR bug 1505582
282 CURSOR c_get_last_updated (c_rank_id NUMBER) IS
283 SELECT last_updated_by
284 FROM AS_SALES_LEAD_RANKS_B
285 WHERE rank_id = c_rank_id;
286 l_last_updated_by NUMBER;
287 -- END FFANG 112700
288
289 BEGIN
290 -- FFANG 112700 FOR bug 1505582
291 -- If last_updated_by is not 1, means this record has been updated by
292 -- customer, we should not overwrite it.
293 OPEN c_get_last_updated (x_RANK_ID);
294 FETCH c_get_last_updated INTO l_last_updated_by;
295 CLOSE c_get_last_updated;
296
297 IF nvl(l_last_updated_by, 1) = 1
298 THEN
299 if (X_OWNER = 'SEED') then
300 user_id := 1;
301 end if;
302
303 Update_Row(x_RANK_ID => x_RANK_ID,
304 x_MIN_SCORE => x_MIN_SCORE,
305 x_MAX_SCORE => x_MAX_SCORE,
306 x_enabled_flag => x_enabled_flag,
307 x_meaning => x_meaning,
308 x_description => x_description,
309 X_LAST_UPDATE_DATE => sysdate,
310 X_LAST_UPDATED_BY => user_id,
311 X_LAST_UPDATE_LOGIN => 0
312 );
313 END IF;
314
315 EXCEPTION
316 when no_data_found then
317 Insert_Row(x_RANK_ID => x_RANK_ID,
318 x_MIN_SCORE => x_MIN_SCORE,
319 x_MAX_SCORE => x_MAX_SCORE,
320 x_enabled_flag => x_enabled_flag,
321 x_meaning => x_meaning,
322 x_description => x_description,
323 x_creation_date => sysdate,
324 x_created_by => 0,
325 X_LAST_UPDATE_DATE => sysdate,
326 X_LAST_UPDATED_BY => user_id,
327 X_LAST_UPDATE_LOGIN => 0
328 );
329 END load_row;
330
331
332
333 PROCEDURE translate_row (
334 P_sales_lead_rank_id IN NUMBER,
335 P_meaning IN VARCHAR2,
336 P_owner IN VARCHAR2) IS
337 BEGIN
338
339 -- only UPDATE rows that have not been altered by user
340 UPDATE AS_SALES_LEAD_RANKS_TL SET
341 meaning = p_meaning,
342 source_lang = userenv('LANG'),
343 last_update_date = sysdate,
344 last_updated_by = decode(p_owner, 'SEED', -1, 0),
345 last_update_login = 0
346 WHERE rank_id = P_sales_lead_rank_id
347 AND userenv('LANG') IN (language, source_lang);
348 END translate_row;
349 end AS_SALES_LEAD_RANKS_PKG;