1 package body QPR_REPORT_HDRS_PKG as
2 /* $Header: QPRURPHB.pls 120.0 2007/12/24 20:05:54 vinnaray noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_REPORT_HEADER_ID in NUMBER,
6 X_PROGRAM_LOGIN_ID in NUMBER,
7 X_REQUEST_ID in NUMBER,
8 X_REPORT_TYPE_HEADER_ID in NUMBER,
9 X_USER_ID in NUMBER,
10 X_PLAN_ID in NUMBER,
11 X_SEEDED_REPORT_FLAG in VARCHAR2,
12 X_REPORT_VALID_FLAG in VARCHAR2,
13 X_ENABLED_OPTIONS in CLOB,
14 X_REPORT_NAME in VARCHAR2,
15 X_REPORT_TITLE 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 QPR_REPORT_HDRS_B
23 where REPORT_HEADER_ID = X_REPORT_HEADER_ID
24 ;
25 begin
26 insert into QPR_REPORT_HDRS_B (
27 PROGRAM_LOGIN_ID,
28 REQUEST_ID,
29 REPORT_HEADER_ID,
30 REPORT_TYPE_HEADER_ID,
31 USER_ID,
32 PLAN_ID,
33 SEEDED_REPORT_FLAG,
34 REPORT_VALID_FLAG,
35 ENABLED_OPTIONS,
36 CREATION_DATE,
37 CREATED_BY,
38 LAST_UPDATE_DATE,
39 LAST_UPDATED_BY,
40 LAST_UPDATE_LOGIN
41 ) values (
42 X_PROGRAM_LOGIN_ID,
43 X_REQUEST_ID,
44 X_REPORT_HEADER_ID,
45 X_REPORT_TYPE_HEADER_ID,
46 X_USER_ID,
47 X_PLAN_ID,
48 X_SEEDED_REPORT_FLAG,
49 X_REPORT_VALID_FLAG,
50 X_ENABLED_OPTIONS,
51 X_CREATION_DATE,
52 X_CREATED_BY,
53 X_LAST_UPDATE_DATE,
54 X_LAST_UPDATED_BY,
55 X_LAST_UPDATE_LOGIN
56 );
57
58 insert into QPR_REPORT_HDRS_TL (
59 REPORT_HEADER_ID,
60 REPORT_NAME,
61 REPORT_TITLE,
62 CREATION_DATE,
63 CREATED_BY,
64 LAST_UPDATE_DATE,
65 LAST_UPDATED_BY,
66 LAST_UPDATE_LOGIN,
67 --PROGRAM_ID,
68 --PROGRAM_APPLICATION_ID,
69 PROGRAM_LOGIN_ID,
70 REQUEST_ID,
71 LANGUAGE,
72 SOURCE_LANG
73 ) select
74 X_REPORT_HEADER_ID,
75 X_REPORT_NAME,
76 X_REPORT_TITLE,
77 X_CREATION_DATE,
78 X_CREATED_BY,
79 X_LAST_UPDATE_DATE,
80 X_LAST_UPDATED_BY,
81 X_LAST_UPDATE_LOGIN,
82 --X_PROGRAM_ID,
83 --X_PROGRAM_APPLICATION_ID,
84 X_PROGRAM_LOGIN_ID,
85 X_REQUEST_ID,
86 L.LANGUAGE_CODE,
87 userenv('LANG')
88 from FND_LANGUAGES L
89 where L.INSTALLED_FLAG in ('I', 'B')
90 and not exists
91 (select NULL
92 from QPR_REPORT_HDRS_TL T
93 where T.REPORT_HEADER_ID = X_REPORT_HEADER_ID
94 and T.LANGUAGE = L.LANGUAGE_CODE);
95
96 open c;
97 fetch c into X_ROWID;
98 if (c%notfound) then
99 close c;
100 raise no_data_found;
101 end if;
102 close c;
103
104 end INSERT_ROW;
105
106 procedure LOCK_ROW (
107 X_REPORT_HEADER_ID in NUMBER,
108 X_PROGRAM_LOGIN_ID in NUMBER,
109 X_REQUEST_ID in NUMBER,
110 X_REPORT_TYPE_HEADER_ID in NUMBER,
111 X_USER_ID in NUMBER,
112 X_PLAN_ID in NUMBER,
113 X_SEEDED_REPORT_FLAG in VARCHAR2,
114 X_REPORT_VALID_FLAG in VARCHAR2,
115 X_ENABLED_OPTIONS in CLOB,
116 X_REPORT_NAME in VARCHAR2,
117 X_REPORT_TITLE in VARCHAR2
118 ) is
119 cursor c is select
120 PROGRAM_LOGIN_ID,
121 REQUEST_ID,
122 REPORT_TYPE_HEADER_ID,
123 USER_ID,
124 PLAN_ID,
125 SEEDED_REPORT_FLAG,
126 REPORT_VALID_FLAG,
127 ENABLED_OPTIONS
128 from QPR_REPORT_HDRS_B
129 where REPORT_HEADER_ID = X_REPORT_HEADER_ID
130 for update of REPORT_HEADER_ID nowait;
131 recinfo c%rowtype;
132
133 cursor c1 is select
134 REPORT_NAME,
135 REPORT_TITLE,
136 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
137 from QPR_REPORT_HDRS_TL
138 where REPORT_HEADER_ID = X_REPORT_HEADER_ID
139 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
140 for update of REPORT_HEADER_ID nowait;
141 begin
142 open c;
143 fetch c into recinfo;
144 if (c%notfound) then
145 close c;
146 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
147 app_exception.raise_exception;
148 end if;
149 close c;
150 if ( ((recinfo.PROGRAM_LOGIN_ID = X_PROGRAM_LOGIN_ID)
151 OR ((recinfo.PROGRAM_LOGIN_ID is null) AND (X_PROGRAM_LOGIN_ID is null)))
152 AND ((recinfo.REQUEST_ID = X_REQUEST_ID)
153 OR ((recinfo.REQUEST_ID is null) AND (X_REQUEST_ID is null)))
154 AND ((recinfo.REPORT_TYPE_HEADER_ID = X_REPORT_TYPE_HEADER_ID)
155 OR ((recinfo.REPORT_TYPE_HEADER_ID is null) AND (X_REPORT_TYPE_HEADER_ID is null)))
156 AND ((recinfo.USER_ID = X_USER_ID)
157 OR ((recinfo.USER_ID is null) AND (X_USER_ID is null)))
158 AND ((recinfo.PLAN_ID = X_PLAN_ID)
159 OR ((recinfo.PLAN_ID is null) AND (X_PLAN_ID is null)))
160 AND ((recinfo.SEEDED_REPORT_FLAG = X_SEEDED_REPORT_FLAG)
161 OR ((recinfo.SEEDED_REPORT_FLAG is null) AND (X_SEEDED_REPORT_FLAG is null)))
162 AND ((recinfo.REPORT_VALID_FLAG = X_REPORT_VALID_FLAG)
163 OR ((recinfo.REPORT_VALID_FLAG is null) AND (X_REPORT_VALID_FLAG is null)))
164 AND ((recinfo.ENABLED_OPTIONS = X_ENABLED_OPTIONS)
165 OR ((recinfo.ENABLED_OPTIONS is null) AND (X_ENABLED_OPTIONS is null)))
166 ) then
167 null;
168 else
169 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
170 app_exception.raise_exception;
171 end if;
172
173 for tlinfo in c1 loop
174 if (tlinfo.BASELANG = 'Y') then
175 if ( ((tlinfo.REPORT_NAME = X_REPORT_NAME)
176 OR ((tlinfo.REPORT_NAME is null) AND (X_REPORT_NAME is null)))
177 AND ((tlinfo.REPORT_TITLE = X_REPORT_TITLE)
178 OR ((tlinfo.REPORT_TITLE is null) AND (X_REPORT_TITLE is null)))
179 ) then
180 null;
181 else
182 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
183 app_exception.raise_exception;
184 end if;
185 end if;
186 end loop;
187 return;
188 end LOCK_ROW;
189
190 procedure UPDATE_ROW (
191 X_REPORT_HEADER_ID in NUMBER,
192 X_PROGRAM_LOGIN_ID in NUMBER,
193 X_REQUEST_ID in NUMBER,
194 X_REPORT_TYPE_HEADER_ID in NUMBER,
195 X_USER_ID in NUMBER,
196 X_PLAN_ID in NUMBER,
197 X_SEEDED_REPORT_FLAG in VARCHAR2,
198 X_REPORT_VALID_FLAG in VARCHAR2,
199 X_ENABLED_OPTIONS in CLOB,
200 X_REPORT_NAME in VARCHAR2,
201 X_REPORT_TITLE in VARCHAR2,
202 X_LAST_UPDATE_DATE in DATE,
203 X_LAST_UPDATED_BY in NUMBER,
204 X_LAST_UPDATE_LOGIN in NUMBER
205 ) is
206 begin
207 update QPR_REPORT_HDRS_B set
208 PROGRAM_LOGIN_ID = X_PROGRAM_LOGIN_ID,
209 REQUEST_ID = X_REQUEST_ID,
210 REPORT_TYPE_HEADER_ID = X_REPORT_TYPE_HEADER_ID,
211 USER_ID = X_USER_ID,
212 PLAN_ID = X_PLAN_ID,
213 SEEDED_REPORT_FLAG = X_SEEDED_REPORT_FLAG,
214 REPORT_VALID_FLAG = X_REPORT_VALID_FLAG,
215 ENABLED_OPTIONS = X_ENABLED_OPTIONS,
216 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
217 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
218 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
219 where REPORT_HEADER_ID = X_REPORT_HEADER_ID;
220
221 if (sql%notfound) then
222 raise no_data_found;
223 end if;
224
225 update QPR_REPORT_HDRS_TL set
226 REPORT_NAME = X_REPORT_NAME,
227 REPORT_TITLE = X_REPORT_TITLE,
228 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
229 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
230 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
231 SOURCE_LANG = userenv('LANG')
232 where REPORT_HEADER_ID = X_REPORT_HEADER_ID
233 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
234
235 if (sql%notfound) then
236 raise no_data_found;
237 end if;
238 end UPDATE_ROW;
239
240 procedure DELETE_ROW (
241 X_REPORT_HEADER_ID in NUMBER
242 ) is
243 begin
244 delete from QPR_REPORT_HDRS_TL
245 where REPORT_HEADER_ID = X_REPORT_HEADER_ID;
246
247 if (sql%notfound) then
248 raise no_data_found;
249 end if;
250
251 delete from QPR_REPORT_HDRS_B
252 where REPORT_HEADER_ID = X_REPORT_HEADER_ID;
253
254 if (sql%notfound) then
255 raise no_data_found;
256 end if;
257 end DELETE_ROW;
258
259 procedure ADD_LANGUAGE
260 is
261 begin
262 delete from QPR_REPORT_HDRS_TL T
263 where not exists
264 (select NULL
265 from QPR_REPORT_HDRS_B B
266 where B.REPORT_HEADER_ID = T.REPORT_HEADER_ID
267 );
268
269 update QPR_REPORT_HDRS_TL T set (
270 REPORT_NAME,
271 REPORT_TITLE
272 ) = (select
273 B.REPORT_NAME,
274 B.REPORT_TITLE
275 from QPR_REPORT_HDRS_TL B
276 where B.REPORT_HEADER_ID = T.REPORT_HEADER_ID
277 and B.LANGUAGE = T.SOURCE_LANG)
278 where (
279 T.REPORT_HEADER_ID,
280 T.LANGUAGE
281 ) in (select
282 SUBT.REPORT_HEADER_ID,
283 SUBT.LANGUAGE
284 from QPR_REPORT_HDRS_TL SUBB, QPR_REPORT_HDRS_TL SUBT
285 where SUBB.REPORT_HEADER_ID = SUBT.REPORT_HEADER_ID
286 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
287 and (SUBB.REPORT_NAME <> SUBT.REPORT_NAME
288 or (SUBB.REPORT_NAME is null and SUBT.REPORT_NAME is not null)
289 or (SUBB.REPORT_NAME is not null and SUBT.REPORT_NAME is null)
290 or SUBB.REPORT_TITLE <> SUBT.REPORT_TITLE
291 or (SUBB.REPORT_TITLE is null and SUBT.REPORT_TITLE is not null)
292 or (SUBB.REPORT_TITLE is not null and SUBT.REPORT_TITLE is null)
293 ));
294
295 insert into QPR_REPORT_HDRS_TL (
296 REPORT_HEADER_ID,
297 REPORT_NAME,
298 REPORT_TITLE,
299 CREATION_DATE,
300 CREATED_BY,
301 LAST_UPDATE_DATE,
302 LAST_UPDATED_BY,
303 LAST_UPDATE_LOGIN,
304 PROGRAM_ID,
305 PROGRAM_APPLICATION_ID,
306 PROGRAM_LOGIN_ID,
307 REQUEST_ID,
308 LANGUAGE,
309 SOURCE_LANG
310 ) select /*+ ORDERED */
311 B.REPORT_HEADER_ID,
312 B.REPORT_NAME,
313 B.REPORT_TITLE,
314 B.CREATION_DATE,
315 B.CREATED_BY,
316 B.LAST_UPDATE_DATE,
317 B.LAST_UPDATED_BY,
318 B.LAST_UPDATE_LOGIN,
319 B.PROGRAM_ID,
320 B.PROGRAM_APPLICATION_ID,
321 B.PROGRAM_LOGIN_ID,
322 B.REQUEST_ID,
323 L.LANGUAGE_CODE,
324 B.SOURCE_LANG
325 from QPR_REPORT_HDRS_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 QPR_REPORT_HDRS_TL T
331 where T.REPORT_HEADER_ID = B.REPORT_HEADER_ID
332 and T.LANGUAGE = L.LANGUAGE_CODE);
333 end ADD_LANGUAGE;
334
335 end QPR_REPORT_HDRS_PKG;
[Home] [Help]