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.XDP_DQ_CONFIGURATION_PKG

Source


1 PACKAGE BODY XDP_DQ_CONFIGURATION_PKG AS
2 /* $Header: XDPDQCNB.pls 120.1 2005/06/15 22:47:12 appldev  $ */
3 procedure INSERT_ROW (
4   X_ROWID in OUT NOCOPY VARCHAR2,
5   X_INTERNAL_Q_NAME in VARCHAR2,
6   X_Q_ALIAS in VARCHAR2,
7   X_QUEUE_TABLE_NAME in VARCHAR2,
8   X_PAYLOAD_TYPE in VARCHAR2,
9   X_NUM_OF_DQER in NUMBER,
10   X_DQ_PROC_NAME in VARCHAR2,
11   X_MODULE_NAME in VARCHAR2,
12   X_IS_AQ_FLAG in VARCHAR2,
13   X_STATE in VARCHAR2,
14   X_DISPLAY_SEQUENCE in NUMBER,
15   X_EXCEPTION_QUEUE_NAME in VARCHAR2,
16   X_MAX_RETRIES in NUMBER,
17   X_DISPLAY_NAME in VARCHAR2,
18   X_DESCRIPTION in VARCHAR2,
19   X_CREATION_DATE in DATE,
20   X_CREATED_BY in NUMBER,
21   X_LAST_UPDATE_DATE in DATE,
22   X_LAST_UPDATED_BY in NUMBER,
23   X_LAST_UPDATE_LOGIN in NUMBER
24 ) is
25   cursor C is select ROWID from XDP_DQ_CONFIGURATION
26     where INTERNAL_Q_NAME = X_INTERNAL_Q_NAME
27     ;
28 begin
29   insert into XDP_DQ_CONFIGURATION (
30     INTERNAL_Q_NAME,
31     Q_ALIAS,
32     QUEUE_TABLE_NAME,
33     PAYLOAD_TYPE,
34     NUM_OF_DQER,
35     DQ_PROC_NAME,
36     MODULE_NAME,
37     IS_AQ_FLAG,
38     STATE,
39     DISPLAY_SEQUENCE,
40     EXCEPTION_QUEUE_NAME,
41     MAX_RETRIES,
42     CREATION_DATE,
43     CREATED_BY,
44     LAST_UPDATE_DATE,
45     LAST_UPDATED_BY,
46     LAST_UPDATE_LOGIN
47   ) values (
48     X_INTERNAL_Q_NAME,
49     X_Q_ALIAS,
50     X_QUEUE_TABLE_NAME,
51     X_PAYLOAD_TYPE,
52     X_NUM_OF_DQER,
53     X_DQ_PROC_NAME,
54     X_MODULE_NAME,
55     X_IS_AQ_FLAG,
56     X_STATE,
57     X_DISPLAY_SEQUENCE,
58     X_EXCEPTION_QUEUE_NAME,
59     X_MAX_RETRIES,
60     X_CREATION_DATE,
61     X_CREATED_BY,
62     X_LAST_UPDATE_DATE,
63     X_LAST_UPDATED_BY,
64     X_LAST_UPDATE_LOGIN
65   );
66 
67   insert into XDP_DQ_CONFIGURATION_TL (
68     LAST_UPDATE_DATE,
69     LAST_UPDATE_LOGIN,
70     INTERNAL_Q_NAME,
71     DISPLAY_NAME,
72     DESCRIPTION,
73     CREATED_BY,
74     CREATION_DATE,
75     LAST_UPDATED_BY,
76     LANGUAGE,
77     SOURCE_LANG
78   ) select
79     X_LAST_UPDATE_DATE,
80     X_LAST_UPDATE_LOGIN,
81     X_INTERNAL_Q_NAME,
82     X_DISPLAY_NAME,
83     X_DESCRIPTION,
84     X_CREATED_BY,
85     X_CREATION_DATE,
86     X_LAST_UPDATED_BY,
87     L.LANGUAGE_CODE,
88     userenv('LANG')
89   from FND_LANGUAGES L
90   where L.INSTALLED_FLAG in ('I', 'B')
91   and not exists
92     (select NULL
93     from XDP_DQ_CONFIGURATION_TL T
94     where T.INTERNAL_Q_NAME = X_INTERNAL_Q_NAME
95     and T.LANGUAGE = L.LANGUAGE_CODE);
96 
97   open c;
98   fetch c into X_ROWID;
99   if (c%notfound) then
100     close c;
101     raise no_data_found;
102   end if;
103   close c;
104 
105 end INSERT_ROW;
106 
107 procedure LOCK_ROW (
108   X_INTERNAL_Q_NAME in VARCHAR2,
109   X_Q_ALIAS in VARCHAR2,
110   X_QUEUE_TABLE_NAME in VARCHAR2,
111   X_PAYLOAD_TYPE in VARCHAR2,
112   X_NUM_OF_DQER in NUMBER,
113   X_DQ_PROC_NAME in VARCHAR2,
114   X_MODULE_NAME in VARCHAR2,
115   X_IS_AQ_FLAG in VARCHAR2,
116   X_STATE in VARCHAR2,
117   X_DISPLAY_SEQUENCE in NUMBER,
118   X_EXCEPTION_QUEUE_NAME in VARCHAR2,
119   X_MAX_RETRIES in NUMBER,
120   X_DISPLAY_NAME in VARCHAR2,
121   X_DESCRIPTION in VARCHAR2
122 ) is
123   cursor c is select
124       Q_ALIAS,
125       QUEUE_TABLE_NAME,
126       PAYLOAD_TYPE,
127       NUM_OF_DQER,
128       DQ_PROC_NAME,
129       MODULE_NAME,
130       IS_AQ_FLAG,
131       STATE,
132       DISPLAY_SEQUENCE,
133       EXCEPTION_QUEUE_NAME,
134       MAX_RETRIES
135     from XDP_DQ_CONFIGURATION
136     where INTERNAL_Q_NAME = X_INTERNAL_Q_NAME
137     for update of INTERNAL_Q_NAME nowait;
138   recinfo c%rowtype;
139 
140   cursor c1 is select
141       DISPLAY_NAME,
142       DESCRIPTION,
143       decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
144     from XDP_DQ_CONFIGURATION_TL
145     where INTERNAL_Q_NAME = X_INTERNAL_Q_NAME
146     and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
147     for update of INTERNAL_Q_NAME nowait;
148 begin
149   open c;
150   fetch c into recinfo;
151   if (c%notfound) then
152     close c;
153     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
154     app_exception.raise_exception;
155   end if;
156   close c;
157   if (    (recinfo.Q_ALIAS = X_Q_ALIAS)
158       AND (recinfo.QUEUE_TABLE_NAME = X_QUEUE_TABLE_NAME)
159       AND (recinfo.PAYLOAD_TYPE = X_PAYLOAD_TYPE)
160       AND (recinfo.NUM_OF_DQER = X_NUM_OF_DQER)
161       AND (recinfo.DQ_PROC_NAME = X_DQ_PROC_NAME)
162       AND (recinfo.MODULE_NAME = X_MODULE_NAME)
163       AND (recinfo.IS_AQ_FLAG = X_IS_AQ_FLAG)
164       AND (recinfo.STATE = X_STATE)
165       AND ((recinfo.DISPLAY_SEQUENCE = X_DISPLAY_SEQUENCE)
166            OR ((recinfo.DISPLAY_SEQUENCE is null) AND (X_DISPLAY_SEQUENCE is null)))
167       AND ((recinfo.EXCEPTION_QUEUE_NAME = X_EXCEPTION_QUEUE_NAME)
168            OR ((recinfo.EXCEPTION_QUEUE_NAME is null) AND (X_EXCEPTION_QUEUE_NAME is null)))
169       AND ((recinfo.MAX_RETRIES = X_MAX_RETRIES)
170            OR ((recinfo.MAX_RETRIES is null) AND (X_MAX_RETRIES is null)))
171   ) then
172     null;
173   else
174     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
175     app_exception.raise_exception;
176   end if;
177 
178   for tlinfo in c1 loop
179     if (tlinfo.BASELANG = 'Y') then
180       if (    (tlinfo.DISPLAY_NAME = X_DISPLAY_NAME)
181           AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
182                OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
183       ) then
184         null;
185       else
186         fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
187         app_exception.raise_exception;
188       end if;
189     end if;
190   end loop;
191   return;
192 end LOCK_ROW;
193 
194 procedure UPDATE_ROW (
195   X_INTERNAL_Q_NAME in VARCHAR2,
196   X_Q_ALIAS in VARCHAR2,
197   X_QUEUE_TABLE_NAME in VARCHAR2,
198   X_PAYLOAD_TYPE in VARCHAR2,
199   X_NUM_OF_DQER in NUMBER,
200   X_DQ_PROC_NAME in VARCHAR2,
201   X_MODULE_NAME in VARCHAR2,
202   X_IS_AQ_FLAG in VARCHAR2,
203   X_STATE in VARCHAR2,
204   X_DISPLAY_SEQUENCE in NUMBER,
205   X_EXCEPTION_QUEUE_NAME in VARCHAR2,
206   X_MAX_RETRIES in NUMBER,
207   X_DISPLAY_NAME in VARCHAR2,
208   X_DESCRIPTION in VARCHAR2,
209   X_LAST_UPDATE_DATE in DATE,
210   X_LAST_UPDATED_BY in NUMBER,
211   X_LAST_UPDATE_LOGIN in NUMBER
212 ) is
213 begin
214   update XDP_DQ_CONFIGURATION set
215     Q_ALIAS = X_Q_ALIAS,
216     QUEUE_TABLE_NAME = X_QUEUE_TABLE_NAME,
217     PAYLOAD_TYPE = X_PAYLOAD_TYPE,
218     NUM_OF_DQER = X_NUM_OF_DQER,
219     DQ_PROC_NAME = X_DQ_PROC_NAME,
220     MODULE_NAME = X_MODULE_NAME,
221     IS_AQ_FLAG = X_IS_AQ_FLAG,
222     STATE = X_STATE,
223     DISPLAY_SEQUENCE = X_DISPLAY_SEQUENCE,
224     EXCEPTION_QUEUE_NAME = X_EXCEPTION_QUEUE_NAME,
225     MAX_RETRIES = X_MAX_RETRIES,
226     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
227     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
228     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
229   where INTERNAL_Q_NAME = X_INTERNAL_Q_NAME;
230 
231   if (sql%notfound) then
232     raise no_data_found;
233   end if;
234 
235   update XDP_DQ_CONFIGURATION_TL set
236     DISPLAY_NAME = X_DISPLAY_NAME,
237     DESCRIPTION = X_DESCRIPTION,
238     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
239     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
240     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
241     SOURCE_LANG = userenv('LANG')
242   where INTERNAL_Q_NAME = X_INTERNAL_Q_NAME
243   and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
244 
245   if (sql%notfound) then
246     raise no_data_found;
247   end if;
248 end UPDATE_ROW;
249 
250 procedure DELETE_ROW (
251   X_INTERNAL_Q_NAME in VARCHAR2
252 ) is
253 begin
254   delete from XDP_DQ_CONFIGURATION_TL
255   where INTERNAL_Q_NAME = X_INTERNAL_Q_NAME;
256 
257   if (sql%notfound) then
258     raise no_data_found;
259   end if;
260 
261   delete from XDP_DQ_CONFIGURATION
262   where INTERNAL_Q_NAME = X_INTERNAL_Q_NAME;
263 
264   if (sql%notfound) then
265     raise no_data_found;
266   end if;
267 end DELETE_ROW;
268 
269 procedure ADD_LANGUAGE
270 is
271 begin
272   delete from XDP_DQ_CONFIGURATION_TL T
273   where not exists
274     (select NULL
275     from XDP_DQ_CONFIGURATION B
276     where B.INTERNAL_Q_NAME = T.INTERNAL_Q_NAME
277     );
278 
279   update XDP_DQ_CONFIGURATION_TL T set (
280       DISPLAY_NAME,
281       DESCRIPTION
282     ) = (select
283       B.DISPLAY_NAME,
284       B.DESCRIPTION
285     from XDP_DQ_CONFIGURATION_TL B
286     where B.INTERNAL_Q_NAME = T.INTERNAL_Q_NAME
287     and B.LANGUAGE = T.SOURCE_LANG)
288   where (
289       T.INTERNAL_Q_NAME,
290       T.LANGUAGE
291   ) in (select
292       SUBT.INTERNAL_Q_NAME,
293       SUBT.LANGUAGE
294     from XDP_DQ_CONFIGURATION_TL SUBB, XDP_DQ_CONFIGURATION_TL SUBT
295     where SUBB.INTERNAL_Q_NAME = SUBT.INTERNAL_Q_NAME
296     and SUBB.LANGUAGE = SUBT.SOURCE_LANG
297     and (SUBB.DISPLAY_NAME <> SUBT.DISPLAY_NAME
298       or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
299       or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
300       or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
301   ));
302 
303   insert into XDP_DQ_CONFIGURATION_TL (
304     LAST_UPDATE_DATE,
305     LAST_UPDATE_LOGIN,
306     INTERNAL_Q_NAME,
307     DISPLAY_NAME,
308     DESCRIPTION,
309     CREATED_BY,
310     CREATION_DATE,
311     LAST_UPDATED_BY,
312     LANGUAGE,
313     SOURCE_LANG
314   ) select
315     B.LAST_UPDATE_DATE,
316     B.LAST_UPDATE_LOGIN,
317     B.INTERNAL_Q_NAME,
318     B.DISPLAY_NAME,
319     B.DESCRIPTION,
320     B.CREATED_BY,
321     B.CREATION_DATE,
322     B.LAST_UPDATED_BY,
323     L.LANGUAGE_CODE,
324     B.SOURCE_LANG
325   from XDP_DQ_CONFIGURATION_TL B, FND_LANGUAGES L
326   where L.INSTALLED_FLAG in ('I', 'B')
327   and B.LANGUAGE = userenv('LANG')
328   and not exists
329     (select NULL
330     from XDP_DQ_CONFIGURATION_TL T
331     where T.INTERNAL_Q_NAME = B.INTERNAL_Q_NAME
332     and T.LANGUAGE = L.LANGUAGE_CODE);
333 end ADD_LANGUAGE;
334 
335 procedure LOAD_ROW (
336   X_INTERNAL_Q_NAME in VARCHAR2,
337   X_Q_ALIAS in VARCHAR2,
338   X_QUEUE_TABLE_NAME in VARCHAR2,
339   X_PAYLOAD_TYPE in VARCHAR2,
340   X_NUM_OF_DQER in NUMBER,
341   X_DQ_PROC_NAME in VARCHAR2,
342   X_MODULE_NAME in VARCHAR2,
343   X_IS_AQ_FLAG in VARCHAR2,
344   X_STATE in VARCHAR2,
345   X_DISPLAY_SEQUENCE in NUMBER,
346   X_EXCEPTION_QUEUE_NAME in VARCHAR2,
347   X_MAX_RETRIES in NUMBER,
348   X_DISPLAY_NAME in VARCHAR2,
349   X_DESCRIPTION in VARCHAR2,
350   X_OWNER in VARCHAR2) IS
351 begin
352 
353   declare
354      user_id            number := 0;
355      row_id             varchar2(64);
356 
357   begin
358 
359      if (X_OWNER = 'SEED') then
360         user_id := 1;
361      end if;
362 
363      XDP_DQ_CONFIGURATION_PKG.UPDATE_ROW (
364   	X_INTERNAL_Q_NAME => X_INTERNAL_Q_NAME,
365   	X_Q_ALIAS => X_Q_ALIAS,
366   	X_QUEUE_TABLE_NAME => X_QUEUE_TABLE_NAME,
367   	X_PAYLOAD_TYPE => X_PAYLOAD_TYPE,
368   	X_NUM_OF_DQER => X_NUM_OF_DQER,
369   	X_DQ_PROC_NAME => X_DQ_PROC_NAME,
370   	X_MODULE_NAME => X_MODULE_NAME,
371   	X_IS_AQ_FLAG => X_IS_AQ_FLAG,
372   	X_STATE => X_STATE,
373   	X_DISPLAY_SEQUENCE => X_DISPLAY_SEQUENCE,
374   	X_EXCEPTION_QUEUE_NAME => X_EXCEPTION_QUEUE_NAME,
375   	X_MAX_RETRIES => X_MAX_RETRIES,
376   	X_DISPLAY_NAME => X_DISPLAY_NAME,
377   	X_DESCRIPTION => X_DESCRIPTION,
378         X_LAST_UPDATE_DATE => sysdate,
379         X_LAST_UPDATED_BY => user_id,
380         X_LAST_UPDATE_LOGIN => 0);
381 
382     exception
383        when NO_DATA_FOUND then
384           XDP_DQ_CONFIGURATION_PKG.INSERT_ROW (
385              	X_ROWID => row_id,
386   	  	X_INTERNAL_Q_NAME => X_INTERNAL_Q_NAME,
387   		X_Q_ALIAS => X_Q_ALIAS,
388   		X_QUEUE_TABLE_NAME => X_QUEUE_TABLE_NAME,
389   		X_PAYLOAD_TYPE => X_PAYLOAD_TYPE,
390   		X_NUM_OF_DQER => X_NUM_OF_DQER,
391   		X_DQ_PROC_NAME => X_DQ_PROC_NAME,
392   		X_MODULE_NAME => X_MODULE_NAME,
393   		X_IS_AQ_FLAG => X_IS_AQ_FLAG,
394   		X_STATE => X_STATE,
395   		X_DISPLAY_SEQUENCE => X_DISPLAY_SEQUENCE,
396   		X_EXCEPTION_QUEUE_NAME => X_EXCEPTION_QUEUE_NAME,
397   		X_MAX_RETRIES => X_MAX_RETRIES,
398              	X_DISPLAY_NAME => X_DISPLAY_NAME,
399              	X_DESCRIPTION => X_DESCRIPTION,
400              	X_CREATION_DATE => sysdate,
401              	X_CREATED_BY => user_id,
402              	X_LAST_UPDATE_DATE => sysdate,
403              	X_LAST_UPDATED_BY => user_id,
404              	X_LAST_UPDATE_LOGIN => 0);
405    end;
406 end LOAD_ROW;
407 
408 procedure TRANSLATE_ROW (
409    X_INTERNAL_Q_NAME in VARCHAR2,
410    X_DISPLAY_NAME in VARCHAR2,
411    X_DESCRIPTION in VARCHAR2,
412    X_OWNER in VARCHAR2) IS
413 
414 begin
415 
416     -- only update rows that have not been altered by user
417 
418     update XDP_DQ_CONFIGURATION_TL
419     set display_name = X_DISPLAY_NAME,
420         description = X_DESCRIPTION,
421         source_lang = userenv('LANG'),
422         last_update_date = sysdate,
423         last_updated_by = decode(X_OWNER, 'SEED', 1, 0),
424         last_update_login = 0
425     where internal_q_name = X_INTERNAL_Q_NAME
426     and   userenv('LANG') in (language, source_lang);
427 
428 end TRANSLATE_ROW;
429 
430 
431 end XDP_DQ_CONFIGURATION_PKG;