eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.AS_SALES_LEAD_QUALS_PKG

Source


1 package body AS_SALES_LEAD_QUALS_PKG as
2 /* $Header: asxtslqb.pls 120.1 2005/06/24 17:05:28 appldev ship $ */
3 procedure INSERT_ROW (
4   X_ROWID IN OUT NOCOPY  VARCHAR2,
5   X_SEED_QUAL_ID in NUMBER,
6   X_ENABLED_FLAG in VARCHAR2,
7   X_LOV_SQL in VARCHAR2,
8   X_RANGE_FLAG in VARCHAR2,
9   X_DATA_TYPE in VARCHAR2,
10   X_SOURCE_TABLE_NAME in VARCHAR2,
11   X_SOURCE_COLUMN_NAME in VARCHAR2,
12   X_MEANING in VARCHAR2,
13   X_DESCRIPTION in VARCHAR2,
14   X_CREATION_DATE in DATE,
15   X_CREATED_BY in NUMBER,
16   X_LAST_UPDATE_DATE in DATE,
17   X_LAST_UPDATED_BY in NUMBER,
18   X_LAST_UPDATE_LOGIN in NUMBER
19 ) is
20   cursor C is select ROWID from AS_SALES_LEAD_QUALS_B
21     where SEED_QUAL_ID = X_SEED_QUAL_ID
22     ;
23 begin
24   insert into AS_SALES_LEAD_QUALS_B (
25     SEED_QUAL_ID,
26     ENABLED_FLAG,
27     LOV_SQL,
28     RANGE_FLAG,
29     DATA_TYPE,
30     SOURCE_TABLE_NAME,
31     SOURCE_COLUMN_NAME,
32     CREATION_DATE,
33     CREATED_BY,
34     LAST_UPDATE_DATE,
35     LAST_UPDATED_BY,
36     LAST_UPDATE_LOGIN
37   ) values (
38     X_SEED_QUAL_ID,
39     X_ENABLED_FLAG,
40     X_LOV_SQL,
41     X_RANGE_FLAG,
42     X_DATA_TYPE,
43     X_SOURCE_TABLE_NAME,
44     X_SOURCE_COLUMN_NAME,
45     X_CREATION_DATE,
46     X_CREATED_BY,
47     X_LAST_UPDATE_DATE,
48     X_LAST_UPDATED_BY,
49     X_LAST_UPDATE_LOGIN
50   );
51 
52   insert into AS_SALES_LEAD_QUALS_TL (
53     SEED_QUAL_ID,
54     LAST_UPDATE_DATE,
55     LAST_UPDATED_BY,
56     CREATION_DATE,
57     CREATED_BY,
58     LAST_UPDATE_LOGIN,
59     MEANING,
60     DESCRIPTION,
61     LANGUAGE,
62     SOURCE_LANG
63   ) select
64     X_SEED_QUAL_ID,
65     X_LAST_UPDATE_DATE,
66     X_LAST_UPDATED_BY,
67     X_CREATION_DATE,
68     X_CREATED_BY,
69     X_LAST_UPDATE_LOGIN,
70     X_MEANING,
71     X_DESCRIPTION,
72     L.LANGUAGE_CODE,
73     userenv('LANG')
74   from FND_LANGUAGES L
75   where L.INSTALLED_FLAG in ('I', 'B')
76   and not exists
77     (select NULL
78     from AS_SALES_LEAD_QUALS_TL T
79     where T.SEED_QUAL_ID = X_SEED_QUAL_ID
80     and T.LANGUAGE = L.LANGUAGE_CODE);
81 
82   open c;
83   fetch c into X_ROWID;
84   if (c%notfound) then
85     close c;
86     raise no_data_found;
87   end if;
88   close c;
89 
90 end INSERT_ROW;
91 
92 procedure LOCK_ROW (
93   X_SEED_QUAL_ID in NUMBER,
94   X_ENABLED_FLAG in VARCHAR2,
95   X_LOV_SQL in VARCHAR2,
96   X_RANGE_FLAG in VARCHAR2,
97   X_DATA_TYPE in VARCHAR2,
98   X_SOURCE_TABLE_NAME in VARCHAR2,
99   X_SOURCE_COLUMN_NAME in VARCHAR2,
100   X_MEANING in VARCHAR2,
101   X_DESCRIPTION in VARCHAR2
102 ) is
103   cursor c is select
104       ENABLED_FLAG,
105       LOV_SQL,
106       RANGE_FLAG,
107       DATA_TYPE,
108       SOURCE_TABLE_NAME,
109       SOURCE_COLUMN_NAME
110     from AS_SALES_LEAD_QUALS_B
111     where SEED_QUAL_ID = X_SEED_QUAL_ID
112     for update of SEED_QUAL_ID nowait;
113   recinfo c%rowtype;
114 
115   cursor c1 is select
116       MEANING,
117       DESCRIPTION,
118       decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
119     from AS_SALES_LEAD_QUALS_TL
120     where SEED_QUAL_ID = X_SEED_QUAL_ID
121     and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
122     for update of SEED_QUAL_ID nowait;
123 begin
124   open c;
125   fetch c into recinfo;
126   if (c%notfound) then
127     close c;
128     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
129     app_exception.raise_exception;
130   end if;
131   close c;
132   if ( (recinfo.ENABLED_FLAG = X_ENABLED_FLAG)
133       AND (recinfo.LOV_SQL = X_LOV_SQL)
134       AND (recinfo.RANGE_FLAG = X_RANGE_FLAG)
135       AND (recinfo.DATA_TYPE = X_DATA_TYPE)
136       AND ((recinfo.SOURCE_TABLE_NAME = X_SOURCE_TABLE_NAME)
137            OR ((recinfo.SOURCE_TABLE_NAME is null) AND (X_SOURCE_TABLE_NAME is null)))
138       AND ((recinfo.SOURCE_COLUMN_NAME = X_SOURCE_COLUMN_NAME)
139            OR ((recinfo.SOURCE_COLUMN_NAME is null) AND (X_SOURCE_COLUMN_NAME is null)))
140   ) then
141     null;
142   else
143     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
144     app_exception.raise_exception;
145   end if;
146 
147   for tlinfo in c1 loop
148     if (tlinfo.BASELANG = 'Y') then
149       if (    (tlinfo.MEANING = X_MEANING)
150           AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
151                OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
152       ) then
153         null;
154       else
155         fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
156         app_exception.raise_exception;
157       end if;
158     end if;
159   end loop;
160   return;
161 end LOCK_ROW;
162 
163 procedure UPDATE_ROW (
164   X_SEED_QUAL_ID in NUMBER,
165   X_ENABLED_FLAG in VARCHAR2,
166   X_LOV_SQL in VARCHAR2,
167   X_RANGE_FLAG in VARCHAR2,
168   X_DATA_TYPE in VARCHAR2,
169   X_SOURCE_TABLE_NAME in VARCHAR2,
170   X_SOURCE_COLUMN_NAME in VARCHAR2,
171   X_MEANING in VARCHAR2,
172   X_DESCRIPTION in VARCHAR2,
173   X_LAST_UPDATE_DATE in DATE,
174   X_LAST_UPDATED_BY in NUMBER,
175   X_LAST_UPDATE_LOGIN in NUMBER
176 ) is
177 begin
178   update AS_SALES_LEAD_QUALS_B set
179     ENABLED_FLAG = X_ENABLED_FLAG,
180     LOV_SQL = X_LOV_SQL,
181     RANGE_FLAG = X_RANGE_FLAG,
182     DATA_TYPE = X_DATA_TYPE,
183     SOURCE_TABLE_NAME = X_SOURCE_TABLE_NAME,
184     SOURCE_COLUMN_NAME = X_SOURCE_COLUMN_NAME,
185     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
186     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
187     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
188   where SEED_QUAL_ID = X_SEED_QUAL_ID;
189 
190   if (sql%notfound) then
191     raise no_data_found;
192   end if;
193 
194   update AS_SALES_LEAD_QUALS_TL set
195     MEANING = X_MEANING,
196     DESCRIPTION = X_DESCRIPTION,
197     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
198     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
199     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
200     SOURCE_LANG = userenv('LANG')
201   where SEED_QUAL_ID = X_SEED_QUAL_ID
202   and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
203 
204   if (sql%notfound) then
205     raise no_data_found;
206   end if;
207 end UPDATE_ROW;
208 
209 procedure DELETE_ROW (
210   X_SEED_QUAL_ID in NUMBER
211 ) is
212 begin
213   delete from AS_SALES_LEAD_QUALS_TL
214   where SEED_QUAL_ID = X_SEED_QUAL_ID;
215 
216   if (sql%notfound) then
217     raise no_data_found;
218   end if;
219 
220   delete from AS_SALES_LEAD_QUALS_B
221   where SEED_QUAL_ID = X_SEED_QUAL_ID;
222 
223   if (sql%notfound) then
224     raise no_data_found;
225   end if;
226 end DELETE_ROW;
227 
228 procedure LOAD_ROW (
229   X_SEED_QUAL_ID in NUMBER,
230   X_ENABLED_FLAG in VARCHAR2,
231   X_LOV_SQL in VARCHAR2,
232   X_RANGE_FLAG in VARCHAR2,
233   X_DATA_TYPE in VARCHAR2,
234   X_SOURCE_TABLE_NAME in VARCHAR2,
235   X_SOURCE_COLUMN_NAME in VARCHAR2,
236   X_MEANING in VARCHAR2,
237   X_DESCRIPTION in VARCHAR2,
238   X_OWNER in VARCHAR2)
239 IS
240     user_id            number := 0;
241     row_id             varchar2(64);
242 
243     -- FFANG 112700 FOR bug 1505582
244     CURSOR c_get_last_updated (c_SEED_QUAL_ID NUMBER) IS
245         SELECT last_updated_by
246         FROM AS_SALES_LEAD_QUALS_B
247         WHERE seed_qual_id = c_seed_qual_id;
248     l_last_updated_by  NUMBER;
249     -- END FFANG 112700
250 
251 begin
252     -- FFANG 112700 FOR bug 1505582
253     -- If last_updated_by is not 1, means this record has been updated by
254     -- customer, we should not overwrite it.
255     OPEN c_get_last_updated (x_SEED_QUAL_ID);
256     FETCH c_get_last_updated INTO l_last_updated_by;
257     CLOSE c_get_last_updated;
258 
259     IF nvl(l_last_updated_by, 1) = 1
260     THEN
261         if (X_OWNER = 'SEED') then
262             user_id := 1;
263         end if;
264 
265         AS_SALES_LEAD_QUALS_PKG.UPDATE_ROW(
266       		X_SEED_QUAL_ID       => X_SEED_QUAL_ID ,
267       		X_ENABLED_FLAG       => X_ENABLED_FLAG,
268       		X_LOV_SQL            => X_LOV_SQL,
269       		X_RANGE_FLAG         => X_RANGE_FLAG,
270       		X_DATA_TYPE          => X_DATA_TYPE,
271       		X_SOURCE_TABLE_NAME  => X_SOURCE_TABLE_NAME,
272       		X_SOURCE_COLUMN_NAME => X_SOURCE_COLUMN_NAME,
273       		X_MEANING            => X_MEANING,
274       		X_DESCRIPTION        => X_DESCRIPTION,
275       		X_LAST_UPDATE_DATE   => sysdate,
276       		X_LAST_UPDATED_BY    => user_id,
277       		X_LAST_UPDATE_LOGIN  => 0
278           );
279     END IF;
280 
281     exception
282 	  when NO_DATA_FOUND then
283          	AS_SALES_LEAD_QUALS_PKG.INSERT_ROW(
284   			X_ROWID              => row_id,
285       		X_SEED_QUAL_ID       => X_SEED_QUAL_ID ,
286       		X_ENABLED_FLAG       => X_ENABLED_FLAG,
287       		X_LOV_SQL            => X_LOV_SQL,
288       		X_RANGE_FLAG         => X_RANGE_FLAG,
289       		X_DATA_TYPE          => X_DATA_TYPE,
290       		X_SOURCE_TABLE_NAME  => X_SOURCE_TABLE_NAME,
291       		X_SOURCE_COLUMN_NAME => X_SOURCE_COLUMN_NAME,
292       		X_MEANING            => X_MEANING,
293       		X_DESCRIPTION        => X_DESCRIPTION,
294  		     X_CREATION_DATE      => sysdate,
295   			X_CREATED_BY         => 0,
296   			X_LAST_UPDATE_DATE   => sysdate,
297   			X_LAST_UPDATED_BY    => user_id,
298   			X_LAST_UPDATE_LOGIN  => 0
299          );
300 end LOAD_ROW;
301 
302 procedure ADD_LANGUAGE
303 is
304 begin
305   delete from AS_SALES_LEAD_QUALS_TL T
306   where not exists
307     (select NULL
308     from AS_SALES_LEAD_QUALS_B B
309     where B.SEED_QUAL_ID = T.SEED_QUAL_ID
310     );
311 
312   update AS_SALES_LEAD_QUALS_TL T set (
313       MEANING,
314       DESCRIPTION
315     ) = (select
316       B.MEANING,
317       B.DESCRIPTION
318     from AS_SALES_LEAD_QUALS_TL B
319     where B.SEED_QUAL_ID = T.SEED_QUAL_ID
320     and B.LANGUAGE = T.SOURCE_LANG)
321   where (
322       T.SEED_QUAL_ID,
323       T.LANGUAGE
324   ) in (select
325       SUBT.SEED_QUAL_ID,
326       SUBT.LANGUAGE
327     from AS_SALES_LEAD_QUALS_TL SUBB, AS_SALES_LEAD_QUALS_TL SUBT
328     where SUBB.SEED_QUAL_ID = SUBT.SEED_QUAL_ID
329     and SUBB.LANGUAGE = SUBT.SOURCE_LANG
330     and (SUBB.MEANING <> SUBT.MEANING
331       or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
332       or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
333       or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
334   ));
335 
336   insert into AS_SALES_LEAD_QUALS_TL (
337     SEED_QUAL_ID,
338     LAST_UPDATE_DATE,
339     LAST_UPDATED_BY,
340     CREATION_DATE,
341     CREATED_BY,
342     LAST_UPDATE_LOGIN,
343     MEANING,
344     DESCRIPTION,
345     LANGUAGE,
346     SOURCE_LANG
347   ) select
348     B.SEED_QUAL_ID,
349     B.LAST_UPDATE_DATE,
350     B.LAST_UPDATED_BY,
351     B.CREATION_DATE,
352     B.CREATED_BY,
353     B.LAST_UPDATE_LOGIN,
354     B.MEANING,
355     B.DESCRIPTION,
356     L.LANGUAGE_CODE,
357     B.SOURCE_LANG
358   from AS_SALES_LEAD_QUALS_TL B, FND_LANGUAGES L
359   where L.INSTALLED_FLAG in ('I', 'B')
360   and B.LANGUAGE = userenv('LANG')
361   and not exists
362     (select NULL
363     from AS_SALES_LEAD_QUALS_TL T
364     where T.SEED_QUAL_ID = B.SEED_QUAL_ID
365     and T.LANGUAGE = L.LANGUAGE_CODE);
366 end ADD_LANGUAGE;
367 
368 procedure TRANSLATE_ROW (
369   X_SEED_QUAL_ID in NUMBER,
370   X_DESCRIPTION in VARCHAR2,
371   X_MEANING in VARCHAR2,
372   X_OWNER in VARCHAR2)
373 IS
374 begin
375  -- only update rows that have not been altered by user
376     update AS_SALES_LEAD_QUALS_TL
377     set description = X_DESCRIPTION,
378 	   meaning     = X_MEANING,
379 	   source_lang = userenv('LANG'),
380 	   last_update_date = sysdate,
381 	   last_updated_by = decode(X_OWNER, 'SEED', 1, 0),
382         last_update_login = 0
383      where seed_qual_id = X_SEED_QUAL_ID
384 	 and userenv('LANG') in (language, source_lang);
385 end TRANSLATE_ROW;
386 
387 end AS_SALES_LEAD_QUALS_PKG;