[Home] [Help]
PACKAGE BODY: APPS.IEC_G_REP_REPORTS_PKG
Source
1 package body IEC_G_REP_REPORTS_PKG as
2 /* $Header: IECREPRB.pls 115.11 2003/08/22 20:42:30 hhuang ship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID out nocopy VARCHAR2,
6 X_REPORT_ID in NUMBER,
7 X_LAST_GENERATED in DATE,
8 X_OBJECT_VERSION_NUMBER in NUMBER,
9 X_REPORT_TITLE in VARCHAR2,
10 X_CREATION_DATE in DATE,
11 X_CREATED_BY in NUMBER,
12 X_LAST_UPDATE_DATE in DATE,
13 X_LAST_UPDATED_BY in NUMBER,
14 X_LAST_UPDATE_LOGIN in NUMBER
15 ) is
16 cursor C is select ROWID from IEC_G_REP_REPORTS_B
17 where REPORT_ID = X_REPORT_ID
18 ;
19 begin
20 x_rowid := NULL;
21
22 insert into IEC_G_REP_REPORTS_B (
23 REPORT_ID,
24 LAST_GENERATED,
25 OBJECT_VERSION_NUMBER,
26 CREATION_DATE,
27 CREATED_BY,
28 LAST_UPDATE_DATE,
29 LAST_UPDATED_BY,
30 LAST_UPDATE_LOGIN
31 ) values (
32 X_REPORT_ID,
33 X_LAST_GENERATED,
34 X_OBJECT_VERSION_NUMBER,
35 X_CREATION_DATE,
36 X_CREATED_BY,
37 X_LAST_UPDATE_DATE,
38 X_LAST_UPDATED_BY,
39 X_LAST_UPDATE_LOGIN
40 );
41
42 insert into IEC_G_REP_REPORTS_TL (
43 REPORT_TITLE,
44 REPORT_ID,
45 CREATED_BY,
46 CREATION_DATE,
47 LAST_UPDATED_BY,
48 LAST_UPDATE_DATE,
49 LAST_UPDATE_LOGIN,
50 OBJECT_VERSION_NUMBER,
51 LANGUAGE,
52 SOURCE_LANG
53 ) select
54 X_REPORT_TITLE,
55 X_REPORT_ID,
56 X_CREATED_BY,
57 X_CREATION_DATE,
58 X_LAST_UPDATED_BY,
59 X_LAST_UPDATE_DATE,
60 X_LAST_UPDATE_LOGIN,
61 X_OBJECT_VERSION_NUMBER,
62 L.LANGUAGE_CODE,
63 userenv('LANG')
64 from FND_LANGUAGES L
65 where L.INSTALLED_FLAG in ('I', 'B')
66 and not exists
67 (select NULL
68 from IEC_G_REP_REPORTS_TL T
69 where T.REPORT_ID = X_REPORT_ID
70 and T.LANGUAGE = L.LANGUAGE_CODE);
71
72 open c;
73 fetch c into X_ROWID;
74 if (c%notfound) then
75 close c;
76 raise no_data_found;
77 end if;
78 close c;
79
80 end INSERT_ROW;
81
82 procedure LOCK_ROW (
83 X_REPORT_ID in NUMBER,
84 X_LAST_GENERATED in DATE,
85 X_OBJECT_VERSION_NUMBER in NUMBER,
86 X_REPORT_TITLE in VARCHAR2
87 ) is
88 cursor c is select
89 LAST_GENERATED,
90 OBJECT_VERSION_NUMBER
91 from IEC_G_REP_REPORTS_B
92 where REPORT_ID = X_REPORT_ID
93 for update of REPORT_ID nowait;
94 recinfo c%rowtype;
95
96 cursor c1 is select
97 REPORT_TITLE,
98 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
99 from IEC_G_REP_REPORTS_TL
100 where REPORT_ID = X_REPORT_ID
101 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
102 for update of REPORT_ID nowait;
103 begin
104 open c;
105 fetch c into recinfo;
106 if (c%notfound) then
107 close c;
108 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
109 app_exception.raise_exception;
110 end if;
111 close c;
112 if ( ((recinfo.LAST_GENERATED = X_LAST_GENERATED)
113 OR ((recinfo.LAST_GENERATED is null) AND (X_LAST_GENERATED is null)))
114 AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
115 ) then
116 null;
117 else
118 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
119 app_exception.raise_exception;
120 end if;
121
122 for tlinfo in c1 loop
123 if (tlinfo.BASELANG = 'Y') then
124 if ( (tlinfo.REPORT_TITLE = X_REPORT_TITLE)
125 ) then
126 null;
127 else
128 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
129 app_exception.raise_exception;
130 end if;
131 end if;
132 end loop;
133 return;
134 end LOCK_ROW;
135
136 procedure UPDATE_ROW (
137 X_REPORT_ID in NUMBER,
138 X_LAST_GENERATED in DATE,
139 X_OBJECT_VERSION_NUMBER in NUMBER,
140 X_REPORT_TITLE in VARCHAR2,
141 X_LAST_UPDATE_DATE in DATE,
142 X_LAST_UPDATED_BY in NUMBER,
143 X_LAST_UPDATE_LOGIN in NUMBER
144 ) is
145 begin
146 update IEC_G_REP_REPORTS_B set
147 LAST_GENERATED = X_LAST_GENERATED,
148 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
149 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
150 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
151 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
152 where REPORT_ID = X_REPORT_ID;
153
154 if (sql%notfound) then
155 raise no_data_found;
156 end if;
157
158 update IEC_G_REP_REPORTS_TL set
159 REPORT_TITLE = X_REPORT_TITLE,
160 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
161 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
162 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
163 SOURCE_LANG = userenv('LANG')
164 where REPORT_ID = X_REPORT_ID
165 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
166
167 if (sql%notfound) then
168 raise no_data_found;
169 end if;
170 end UPDATE_ROW;
171
172 procedure DELETE_ROW (
173 X_REPORT_ID in NUMBER
174 ) is
175 begin
176 delete from IEC_G_REP_REPORTS_TL
177 where REPORT_ID = X_REPORT_ID;
178
179 if (sql%notfound) then
180 raise no_data_found;
181 end if;
182
183 delete from IEC_G_REP_REPORTS_B
184 where REPORT_ID = X_REPORT_ID;
185
186 if (sql%notfound) then
187 raise no_data_found;
188 end if;
189 end DELETE_ROW;
190
191 procedure ADD_LANGUAGE
192 is
193 begin
194 delete from IEC_G_REP_REPORTS_TL T
195 where not exists
196 (select NULL
197 from IEC_G_REP_REPORTS_B B
198 where B.REPORT_ID = T.REPORT_ID
199 );
200
201 update IEC_G_REP_REPORTS_TL T set (
202 REPORT_TITLE
203 ) = (select
204 B.REPORT_TITLE
205 from IEC_G_REP_REPORTS_TL B
206 where B.REPORT_ID = T.REPORT_ID
207 and B.LANGUAGE = T.SOURCE_LANG)
208 where (
209 T.REPORT_ID,
210 T.LANGUAGE
211 ) in (select
212 SUBT.REPORT_ID,
213 SUBT.LANGUAGE
214 from IEC_G_REP_REPORTS_TL SUBB, IEC_G_REP_REPORTS_TL SUBT
215 where SUBB.REPORT_ID = SUBT.REPORT_ID
216 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
217 and (SUBB.REPORT_TITLE <> SUBT.REPORT_TITLE
218 ));
219
220 insert into IEC_G_REP_REPORTS_TL (
221 REPORT_TITLE,
222 REPORT_ID,
223 CREATED_BY,
224 CREATION_DATE,
225 LAST_UPDATED_BY,
226 LAST_UPDATE_DATE,
227 LAST_UPDATE_LOGIN,
228 OBJECT_VERSION_NUMBER,
229 LANGUAGE,
230 SOURCE_LANG
231 ) select /*+ ORDERED */
232 B.REPORT_TITLE,
233 B.REPORT_ID,
234 B.CREATED_BY,
235 B.CREATION_DATE,
236 B.LAST_UPDATED_BY,
237 B.LAST_UPDATE_DATE,
238 B.LAST_UPDATE_LOGIN,
239 B.OBJECT_VERSION_NUMBER,
240 L.LANGUAGE_CODE,
241 B.SOURCE_LANG
242 from IEC_G_REP_REPORTS_TL B, FND_LANGUAGES L
243 where L.INSTALLED_FLAG in ('I', 'B')
244 and B.LANGUAGE = userenv('LANG')
245 and not exists
246 (select NULL
247 from IEC_G_REP_REPORTS_TL T
248 where T.REPORT_ID = B.REPORT_ID
249 and T.LANGUAGE = L.LANGUAGE_CODE);
250 end ADD_LANGUAGE;
251
252 procedure LOAD_ROW (
253 X_REPORT_ID in NUMBER,
254 X_LAST_GENERATED in DATE,
255 X_REPORT_TITLE in VARCHAR2,
256 X_OWNER in VARCHAR2
257 ) is
258
259 USER_ID NUMBER := 0;
260 ROW_ID VARCHAR2(500);
261 begin
262
263 if (X_OWNER = 'SEED') then
264 USER_ID := 1;
265 end if;
266
267 UPDATE_ROW (X_REPORT_ID, X_LAST_GENERATED, 0, X_REPORT_TITLE, SYSDATE, USER_ID, 0);
268
269 exception
270 when no_data_found then
271 INSERT_ROW (ROW_ID, X_REPORT_ID, X_LAST_GENERATED, 0, X_REPORT_TITLE, SYSDATE, USER_ID, SYSDATE, USER_ID, 0);
272
273 end LOAD_ROW;
274
275 procedure TRANSLATE_ROW (
276 X_REPORT_ID in NUMBER,
277 X_REPORT_TITLE in VARCHAR2,
278 X_OWNER in VARCHAR2
279 ) is
280 begin
281
282 -- only UPDATE rows that have not been altered by user
283
284 update IEC_G_REP_REPORTS_TL set
285 REPORT_TITLE = X_REPORT_TITLE,
286 SOURCE_LANG = userenv('LANG'),
287 LAST_UPDATE_DATE = SYSDATE,
288 LAST_UPDATED_BY = DECODE(X_OWNER, 'SEED', 1, 0),
289 LAST_UPDATE_LOGIN = 0
290 where REPORT_ID = X_REPORT_ID
291 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
292
293 end TRANSLATE_ROW;
294
295 end IEC_G_REP_REPORTS_PKG;