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

Source


1 package body FA_RX_SUMMARY_PKG as
2 /* $Header: faxrxsmb.pls 120.5 2006/05/30 12:10:48 rravunny ship $ */
3 procedure INSERT_ROW (
4   X_ROWID in out nocopy VARCHAR2,
5   X_REPORT_ID in NUMBER,
6   X_ATTRIBUTE_SET in VARCHAR2,
7   X_COLUMN_NAME in VARCHAR2,
8   X_SUMMARY_FUNCTION in VARCHAR2,
9   X_PRINT_LEVEL in NUMBER,
10   X_RESET_LEVEL in NUMBER,
11   X_COMPUTE_LEVEL in NUMBER,
12   X_DISPLAY_STATUS in VARCHAR2,
13   X_SUMMARY_PROMPT 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 FA_RX_SUMMARY_TL
21     where REPORT_ID = X_REPORT_ID
22     and ATTRIBUTE_SET = X_ATTRIBUTE_SET
23     and COLUMN_NAME = X_COLUMN_NAME
24     and SUMMARY_FUNCTION = X_SUMMARY_FUNCTION
25     and PRINT_LEVEL = X_PRINT_LEVEL
26     and RESET_LEVEL = X_RESET_LEVEL
27     and COMPUTE_LEVEL = X_COMPUTE_LEVEL
28     and LANGUAGE = userenv('LANG')
29     ;
30 begin
31   insert into FA_RX_SUMMARY_TL (
32     REPORT_ID,
33     ATTRIBUTE_SET,
34     COLUMN_NAME,
35     PRINT_LEVEL,
36     RESET_LEVEL,
37     COMPUTE_LEVEL,
38     SUMMARY_FUNCTION,
39     SUMMARY_PROMPT,
40     DISPLAY_STATUS,
41     LAST_UPDATE_DATE,
42     LAST_UPDATE_LOGIN,
43     LAST_UPDATED_BY,
44     CREATED_BY,
45     CREATION_DATE,
46     LANGUAGE,
47     SOURCE_LANG
48   ) select
49     X_REPORT_ID,
50     X_ATTRIBUTE_SET,
51     X_COLUMN_NAME,
52     X_PRINT_LEVEL,
53     X_RESET_LEVEL,
54     X_COMPUTE_LEVEL,
55     X_SUMMARY_FUNCTION,
56     X_SUMMARY_PROMPT,
57     X_DISPLAY_STATUS,
58     X_LAST_UPDATE_DATE,
59     X_LAST_UPDATE_LOGIN,
60     X_LAST_UPDATED_BY,
61     X_CREATED_BY,
62     X_CREATION_DATE,
63     L.LANGUAGE_CODE,
64     userenv('LANG')
65   from FND_LANGUAGES L
66   where L.INSTALLED_FLAG in ('I', 'B')
67   and not exists
68     (select NULL
69     from FA_RX_SUMMARY_TL T
70     where T.REPORT_ID = X_REPORT_ID
71     and T.ATTRIBUTE_SET = X_ATTRIBUTE_SET
72     and T.COLUMN_NAME = X_COLUMN_NAME
73     and T.SUMMARY_FUNCTION = X_SUMMARY_FUNCTION
74     and T.PRINT_LEVEL = X_PRINT_LEVEL
75     and T.RESET_LEVEL = X_RESET_LEVEL
76     and T.COMPUTE_LEVEL = X_COMPUTE_LEVEL
77     and T.LANGUAGE = L.LANGUAGE_CODE);
78 
79   open c;
80   fetch c into X_ROWID;
81   if (c%notfound) then
82     close c;
83     raise no_data_found;
84   end if;
85   close c;
86 
87 end INSERT_ROW;
88 
89 procedure LOCK_ROW (
90   X_REPORT_ID in NUMBER,
91   X_ATTRIBUTE_SET in VARCHAR2,
92   X_COLUMN_NAME in VARCHAR2,
93   X_SUMMARY_FUNCTION in VARCHAR2,
94   X_PRINT_LEVEL in NUMBER,
95   X_RESET_LEVEL in NUMBER,
96   X_COMPUTE_LEVEL in NUMBER,
97   X_DISPLAY_STATUS in VARCHAR2,
98   X_SUMMARY_PROMPT in VARCHAR2
99 ) is
100   cursor c1 is select
101       DISPLAY_STATUS,
102       SUMMARY_PROMPT,
103       decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
104     from FA_RX_SUMMARY_TL
105     where REPORT_ID = X_REPORT_ID
106     and ATTRIBUTE_SET = X_ATTRIBUTE_SET
107     and COLUMN_NAME = X_COLUMN_NAME
108     and SUMMARY_FUNCTION = X_SUMMARY_FUNCTION
109     and PRINT_LEVEL = X_PRINT_LEVEL
110     and RESET_LEVEL = X_RESET_LEVEL
111     and COMPUTE_LEVEL = X_COMPUTE_LEVEL
112     and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
113     for update of REPORT_ID nowait;
114 begin
115   for tlinfo in c1 loop
116     if (tlinfo.BASELANG = 'Y') then
117       if (    ((tlinfo.SUMMARY_PROMPT = X_SUMMARY_PROMPT)
118                OR ((tlinfo.SUMMARY_PROMPT is null) AND (X_SUMMARY_PROMPT is null)))
119           AND ((tlinfo.DISPLAY_STATUS = X_DISPLAY_STATUS)
120                OR ((tlinfo.DISPLAY_STATUS is null) AND (X_DISPLAY_STATUS is null)))
121       ) then
122         null;
123       else
124         fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
125         app_exception.raise_exception;
126       end if;
127     end if;
128   end loop;
129   return;
130 end LOCK_ROW;
131 
132 procedure UPDATE_ROW (
133   X_REPORT_ID in NUMBER,
134   X_ATTRIBUTE_SET in VARCHAR2,
135   X_COLUMN_NAME in VARCHAR2,
136   X_SUMMARY_FUNCTION in VARCHAR2,
137   X_PRINT_LEVEL in NUMBER,
138   X_RESET_LEVEL in NUMBER,
139   X_COMPUTE_LEVEL in NUMBER,
140   X_DISPLAY_STATUS in VARCHAR2,
141   X_SUMMARY_PROMPT in VARCHAR2,
142   X_LAST_UPDATE_DATE in DATE,
143   X_LAST_UPDATED_BY in NUMBER,
144   X_LAST_UPDATE_LOGIN in NUMBER
145 ) is
146 begin
147   update FA_RX_SUMMARY_TL set
148     DISPLAY_STATUS = X_DISPLAY_STATUS,
149     SUMMARY_PROMPT = X_SUMMARY_PROMPT,
150     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
151     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
152     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
153     SOURCE_LANG = userenv('LANG')
154   where REPORT_ID = X_REPORT_ID
155   and ATTRIBUTE_SET = X_ATTRIBUTE_SET
156   and COLUMN_NAME = X_COLUMN_NAME
157   and SUMMARY_FUNCTION = X_SUMMARY_FUNCTION
158   and PRINT_LEVEL = X_PRINT_LEVEL
159   and RESET_LEVEL = X_RESET_LEVEL
160   and COMPUTE_LEVEL = X_COMPUTE_LEVEL
161   and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
162 
163   if (sql%notfound) then
164     raise no_data_found;
165   end if;
166 end UPDATE_ROW;
167 
168 procedure DELETE_ROW (
169   X_REPORT_ID in NUMBER,
170   X_ATTRIBUTE_SET in VARCHAR2,
171   X_COLUMN_NAME in VARCHAR2,
172   X_SUMMARY_FUNCTION in VARCHAR2,
173   X_PRINT_LEVEL in NUMBER,
174   X_RESET_LEVEL in NUMBER,
175   X_COMPUTE_LEVEL in NUMBER
176 ) is
177 begin
178   delete from FA_RX_SUMMARY_TL
179   where REPORT_ID = X_REPORT_ID
180   and ATTRIBUTE_SET = X_ATTRIBUTE_SET
181   and COLUMN_NAME = X_COLUMN_NAME
182   and SUMMARY_FUNCTION = X_SUMMARY_FUNCTION
183   and PRINT_LEVEL = X_PRINT_LEVEL
184   and RESET_LEVEL = X_RESET_LEVEL
185   and COMPUTE_LEVEL = X_COMPUTE_LEVEL;
186 
187   if (sql%notfound) then
188     raise no_data_found;
189   end if;
190 
191 end DELETE_ROW;
192 
193 procedure ADD_LANGUAGE
194 is
195 begin
196   update FA_RX_SUMMARY_TL T set (
197       SUMMARY_PROMPT
198     ) = (select
199       B.SUMMARY_PROMPT
200     from FA_RX_SUMMARY_TL B
201     where B.REPORT_ID = T.REPORT_ID
202     and B.ATTRIBUTE_SET = T.ATTRIBUTE_SET
203     and B.COLUMN_NAME = T.COLUMN_NAME
204     and B.SUMMARY_FUNCTION = T.SUMMARY_FUNCTION
205     and B.PRINT_LEVEL = T.PRINT_LEVEL
206     and B.RESET_LEVEL = T.RESET_LEVEL
207     and B.COMPUTE_LEVEL = T.COMPUTE_LEVEL
208     and B.LANGUAGE = T.SOURCE_LANG)
209   where (
210       T.REPORT_ID,
211       T.ATTRIBUTE_SET,
212       T.COLUMN_NAME,
213       T.SUMMARY_FUNCTION,
214       T.PRINT_LEVEL,
215       T.RESET_LEVEL,
216       T.COMPUTE_LEVEL,
217       T.LANGUAGE
218   ) in (select
219       SUBT.REPORT_ID,
220       SUBT.ATTRIBUTE_SET,
221       SUBT.COLUMN_NAME,
222       SUBT.SUMMARY_FUNCTION,
223       SUBT.PRINT_LEVEL,
224       SUBT.RESET_LEVEL,
225       SUBT.COMPUTE_LEVEL,
226       SUBT.LANGUAGE
227     from FA_RX_SUMMARY_TL SUBB, FA_RX_SUMMARY_TL SUBT
228     where SUBB.REPORT_ID = SUBT.REPORT_ID
229     and SUBB.ATTRIBUTE_SET = SUBT.ATTRIBUTE_SET
230     and SUBB.COLUMN_NAME = SUBT.COLUMN_NAME
231     and SUBB.SUMMARY_FUNCTION = SUBT.SUMMARY_FUNCTION
232     and SUBB.PRINT_LEVEL = SUBT.PRINT_LEVEL
233     and SUBB.RESET_LEVEL = SUBT.RESET_LEVEL
234     and SUBB.COMPUTE_LEVEL = SUBT.COMPUTE_LEVEL
235     and SUBB.LANGUAGE = SUBT.SOURCE_LANG
236     and (SUBB.SUMMARY_PROMPT <> SUBT.SUMMARY_PROMPT
237       or (SUBB.SUMMARY_PROMPT is null and SUBT.SUMMARY_PROMPT is not null)
238       or (SUBB.SUMMARY_PROMPT is not null and SUBT.SUMMARY_PROMPT is null)
239   ));
240 
241   insert into FA_RX_SUMMARY_TL (
242     REPORT_ID,
243     ATTRIBUTE_SET,
244     COLUMN_NAME,
245     PRINT_LEVEL,
246     RESET_LEVEL,
247     COMPUTE_LEVEL,
248     SUMMARY_FUNCTION,
249     SUMMARY_PROMPT,
250     DISPLAY_STATUS,
251     LAST_UPDATE_DATE,
252     LAST_UPDATE_LOGIN,
253     LAST_UPDATED_BY,
254     CREATED_BY,
255     CREATION_DATE,
256     LANGUAGE,
257     SOURCE_LANG
258   ) select
259     B.REPORT_ID,
260     B.ATTRIBUTE_SET,
261     B.COLUMN_NAME,
262     B.PRINT_LEVEL,
263     B.RESET_LEVEL,
264     B.COMPUTE_LEVEL,
265     B.SUMMARY_FUNCTION,
266     B.SUMMARY_PROMPT,
267     B.DISPLAY_STATUS,
268     B.LAST_UPDATE_DATE,
269     B.LAST_UPDATE_LOGIN,
270     B.LAST_UPDATED_BY,
271     B.CREATED_BY,
272     B.CREATION_DATE,
273     L.LANGUAGE_CODE,
274     B.SOURCE_LANG
275   from FA_RX_SUMMARY_TL B, FND_LANGUAGES L
276   where L.INSTALLED_FLAG in ('I', 'B')
277   and B.LANGUAGE = userenv('LANG')
278   and not exists
279     (select NULL
280     from FA_RX_SUMMARY_TL T
281     where T.REPORT_ID = B.REPORT_ID
282     and T.ATTRIBUTE_SET = B.ATTRIBUTE_SET
283     and T.COLUMN_NAME = B.COLUMN_NAME
284     and T.SUMMARY_FUNCTION = B.SUMMARY_FUNCTION
285     and T.PRINT_LEVEL = B.PRINT_LEVEL
286     and T.RESET_LEVEL = B.RESET_LEVEL
287     and T.COMPUTE_LEVEL = B.COMPUTE_LEVEL
288     and T.LANGUAGE = L.LANGUAGE_CODE);
289 end ADD_LANGUAGE;
290 
291 procedure LOAD_ROW (
292   X_REPORT_ID in NUMBER,
293   X_ATTRIBUTE_SET in VARCHAR2,
294   X_COLUMN_NAME in VARCHAR2,
295   X_SUMMARY_FUNCTION in VARCHAR2,
296   X_PRINT_LEVEL in NUMBER,
297   X_RESET_LEVEL in NUMBER,
298   X_COMPUTE_LEVEL in NUMBER,
299   X_DISPLAY_STATUS in VARCHAR2,
300   X_SUMMARY_PROMPT in VARCHAR2,
301   X_OWNER in VARCHAR2
302   ) is
303 begin
304 	LOAD_ROW (
305 	  X_REPORT_ID => X_REPORT_ID ,
306 	  X_ATTRIBUTE_SET => X_ATTRIBUTE_SET ,
307 	  X_COLUMN_NAME  => X_COLUMN_NAME ,
308 	  X_SUMMARY_FUNCTION =>  X_SUMMARY_FUNCTION ,
309 	  X_PRINT_LEVEL  => X_PRINT_LEVEL ,
310 	  X_RESET_LEVEL  => X_RESET_LEVEL ,
311 	  X_COMPUTE_LEVEL  => X_COMPUTE_LEVEL ,
312 	  X_DISPLAY_STATUS => X_DISPLAY_STATUS,
313 	  X_SUMMARY_PROMPT  => X_SUMMARY_PROMPT ,
314 	  X_OWNER  => X_OWNER ,
315 	  X_LAST_UPDATE_DATE  => null,
316 	  X_CUSTOM_MODE  => null
317 	  );
318 end LOAD_ROW;
319 
320 procedure LOAD_ROW (
321   X_REPORT_ID in NUMBER,
322   X_ATTRIBUTE_SET in VARCHAR2,
323   X_COLUMN_NAME in VARCHAR2,
324   X_SUMMARY_FUNCTION in VARCHAR2,
325   X_PRINT_LEVEL in NUMBER,
326   X_RESET_LEVEL in NUMBER,
327   X_COMPUTE_LEVEL in NUMBER,
328   X_DISPLAY_STATUS in VARCHAR2,
329   X_SUMMARY_PROMPT in VARCHAR2,
330   X_OWNER in VARCHAR2,
331   X_LAST_UPDATE_DATE in VARCHAR2,
332   X_CUSTOM_MODE in VARCHAR2
333   ) is
334 	--* Bug#5102292, rravunny
335 	--* Begin
336 	--*
337 			f_luby number;  -- entity owner in file
338 			f_ludate date;  -- entity update date in file
339 			db_luby number; -- entity owner in db
340 			db_ludate date; -- entity update date in db
341 
342 	--* End
343 	--*
344 begin
345   declare
346     row_id varchar2(64);
347     user_id number := 0;
348   begin
349   	--* Bug#5102292, rravunny
350 	--* Begin
351 	--*
352 		f_luby := fnd_load_util.owner_id(X_Owner);
353 
354 		-- Translate char last_update_date to date
355 		f_ludate := nvl(to_date(X_Last_Update_Date, 'YYYY/MM/DD HH24:MI:SS'), sysdate);
356 
357 		select	LAST_UPDATED_BY, LAST_UPDATE_DATE
358 		into	db_luby, db_ludate
359 		from	FA_RX_SUMMARY_TL
360 		where	REPORT_ID = X_REPORT_ID
361 		and ATTRIBUTE_SET = X_ATTRIBUTE_SET
362 		and COLUMN_NAME = X_COLUMN_NAME
363 		and SUMMARY_FUNCTION = X_SUMMARY_FUNCTION
364 		and PRINT_LEVEL = X_PRINT_LEVEL
365 		and RESET_LEVEL = X_RESET_LEVEL
366 		and COMPUTE_LEVEL = X_COMPUTE_LEVEL
367 		and language = userenv('LANG');
368 
369 	--* End
370 	--*
371 	If (fnd_load_util.upload_test(f_luby, f_ludate, db_luby, db_ludate, X_CUSTOM_MODE)) Then
372 		UPDATE_ROW(
373 			X_REPORT_ID =>		X_REPORT_ID,
374 			X_ATTRIBUTE_SET =>	X_ATTRIBUTE_SET,
375 			X_COLUMN_NAME =>	X_COLUMN_NAME,
376 			X_SUMMARY_FUNCTION =>	X_SUMMARY_FUNCTION,
377 			X_PRINT_LEVEL =>	X_PRINT_LEVEL,
378 			X_RESET_LEVEL =>	X_RESET_LEVEL,
379 			X_COMPUTE_LEVEL =>	X_COMPUTE_LEVEL,
380 			X_DISPLAY_STATUS =>	X_DISPLAY_STATUS,
381 			X_SUMMARY_PROMPT =>	X_SUMMARY_PROMPT,
382 			X_LAST_UPDATE_DATE =>	f_ludate,
383 			X_LAST_UPDATED_BY =>	f_luby,
384 			X_LAST_UPDATE_LOGIN =>	0);
385 	End If;
386 	exception
387   when NO_DATA_FOUND then
388 	INSERT_ROW(
389 		X_ROWID =>		row_id,
390 		X_REPORT_ID =>		X_REPORT_ID,
391 		X_ATTRIBUTE_SET =>	X_ATTRIBUTE_SET,
392 		X_COLUMN_NAME =>	X_COLUMN_NAME,
393 		X_SUMMARY_FUNCTION =>	X_SUMMARY_FUNCTION,
394 		X_PRINT_LEVEL =>	X_PRINT_LEVEL,
395 		X_RESET_LEVEL =>	X_RESET_LEVEL,
396 		X_COMPUTE_LEVEL =>	X_COMPUTE_LEVEL,
397 		X_DISPLAY_STATUS =>	X_DISPLAY_STATUS,
398 		X_SUMMARY_PROMPT =>	X_SUMMARY_PROMPT,
399 		X_CREATION_DATE =>	f_ludate,
400 		X_CREATED_BY =>		f_luby,
401 		X_LAST_UPDATE_DATE =>	f_ludate,
402 		X_LAST_UPDATED_BY =>	f_luby,
403 		X_LAST_UPDATE_LOGIN =>	0);
404   end;
405 end LOAD_ROW;
406 
407 procedure TRANSLATE_ROW (
408   X_REPORT_ID in NUMBER,
409   X_ATTRIBUTE_SET in VARCHAR2,
410   X_COLUMN_NAME in VARCHAR2,
411   X_SUMMARY_FUNCTION in VARCHAR2,
412   X_PRINT_LEVEL in NUMBER,
413   X_RESET_LEVEL in NUMBER,
414   X_COMPUTE_LEVEL in NUMBER,
415   X_SUMMARY_PROMPT in VARCHAR2,
416   X_OWNER in VARCHAR2
417   ) is
418 begin
419 	TRANSLATE_ROW (
420 	  X_REPORT_ID => X_REPORT_ID ,
421 	  X_ATTRIBUTE_SET => X_ATTRIBUTE_SET ,
422 	  X_COLUMN_NAME  => X_COLUMN_NAME ,
423 	  X_SUMMARY_FUNCTION =>  X_SUMMARY_FUNCTION ,
424 	  X_PRINT_LEVEL  => X_PRINT_LEVEL ,
425 	  X_RESET_LEVEL  => X_RESET_LEVEL ,
426 	  X_COMPUTE_LEVEL  => X_COMPUTE_LEVEL ,
427 	  X_SUMMARY_PROMPT  => X_SUMMARY_PROMPT ,
428 	  X_OWNER  => X_OWNER ,
429 	  X_LAST_UPDATE_DATE  => null,
430 	  X_CUSTOM_MODE  => null
431 	  ) ;
432 End TRANSLATE_ROW;
433 
434 procedure TRANSLATE_ROW (
435   X_REPORT_ID in NUMBER,
436   X_ATTRIBUTE_SET in VARCHAR2,
437   X_COLUMN_NAME in VARCHAR2,
438   X_SUMMARY_FUNCTION in VARCHAR2,
439   X_PRINT_LEVEL in NUMBER,
440   X_RESET_LEVEL in NUMBER,
441   X_COMPUTE_LEVEL in NUMBER,
442   X_SUMMARY_PROMPT in VARCHAR2,
443   X_OWNER in VARCHAR2,
444   X_LAST_UPDATE_DATE in VARCHAR2,
445   X_CUSTOM_MODE in VARCHAR2
446   ) is
447   f_luby number;  -- entity owner in file
448   f_ludate date;  -- entity update date in file
449 begin
450 	--* Bug#5102292, rravunny
451 	--* Begin
452 	--*
453 	f_luby := fnd_load_util.owner_id(X_Owner);
454 
455 	-- Translate char last_update_date to date
456 	f_ludate := nvl(to_date(X_Last_Update_Date, 'YYYY/MM/DD HH24:MI:SS'), sysdate);
457 
458 	--* End
459 	--*
460 
461 	update FA_RX_SUMMARY_TL
462 	set	SUMMARY_PROMPT = X_SUMMARY_PROMPT,
463 		LAST_UPDATE_DATE = f_ludate,
464 		LAST_UPDATED_BY = f_luby,
465 		LAST_UPDATE_LOGIN = 0,
466 		SOURCE_LANG = userenv('LANG')
467 	where REPORT_ID = X_REPORT_ID
468 	and ATTRIBUTE_SET = X_ATTRIBUTE_SET
469 	and COLUMN_NAME = X_COLUMN_NAME
470 	and SUMMARY_FUNCTION = X_SUMMARY_FUNCTION
471 	and PRINT_LEVEL = X_PRINT_LEVEL
472 	and RESET_LEVEL = X_RESET_LEVEL
473 	and COMPUTE_LEVEL = X_COMPUTE_LEVEL
474 	and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
475 end TRANSLATE_ROW;
476 
477 end FA_RX_SUMMARY_PKG;