1 package body QPR_TRANSF_HEADERS_PKG as
2 /* $Header: QPRUTRHB.pls 120.0 2007/12/24 20:07:54 vinnaray noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_TRANSF_HEADER_ID in NUMBER,
6 X_TRANSF_GROUP_ID in NUMBER,
7 X_FROM_DIM_MEAS_CODE in VARCHAR2,
8 X_FROM_DIM_HIER_CODE in VARCHAR2,
9 X_LIMIT_DIM_FLAG in VARCHAR2,
10 X_FROM_LEVEL_ID in NUMBER,
11 X_MEAS_CODE in VARCHAR2,
12 X_TO_DIM_CODE in VARCHAR2,
13 X_TO_HIER_CODE in VARCHAR2,
14 X_TO_LEVEL_ID in NUMBER,
15 X_TO_VALUE in VARCHAR2,
16 X_TO_VALUE_DESC in VARCHAR2,
17 X_PROGRAM_LOGIN_ID in NUMBER,
18 X_REQUEST_ID in NUMBER,
19 X_NAME in VARCHAR2,
20 X_DESCRIPTION in VARCHAR2,
21 X_CREATION_DATE in DATE,
22 X_CREATED_BY in NUMBER,
23 X_LAST_UPDATE_DATE in DATE,
24 X_LAST_UPDATED_BY in NUMBER,
25 X_LAST_UPDATE_LOGIN in NUMBER
26 ) is
27 cursor C is select ROWID from QPR_TRANSF_HEADERS_B
28 where TRANSF_HEADER_ID = X_TRANSF_HEADER_ID
29 ;
30 begin
31 insert into QPR_TRANSF_HEADERS_B (
32 TRANSF_GROUP_ID,
33 FROM_DIM_MEAS_CODE,
34 FROM_DIM_HIER_CODE,
35 LIMIT_DIM_FLAG,
36 FROM_LEVEL_ID,
37 MEAS_CODE,
38 TO_DIM_CODE,
39 TO_HIER_CODE,
40 TO_LEVEL_ID,
41 TO_VALUE,
42 TO_VALUE_DESC,
43 PROGRAM_LOGIN_ID,
44 REQUEST_ID,
45 TRANSF_HEADER_ID,
46 CREATION_DATE,
47 CREATED_BY,
48 LAST_UPDATE_DATE,
49 LAST_UPDATED_BY,
50 LAST_UPDATE_LOGIN
51 ) values (
52 X_TRANSF_GROUP_ID,
53 X_FROM_DIM_MEAS_CODE,
54 X_FROM_DIM_HIER_CODE,
55 X_LIMIT_DIM_FLAG,
56 X_FROM_LEVEL_ID,
57 X_MEAS_CODE,
58 X_TO_DIM_CODE,
59 X_TO_HIER_CODE,
60 X_TO_LEVEL_ID,
61 X_TO_VALUE,
62 X_TO_VALUE_DESC,
63 X_PROGRAM_LOGIN_ID,
64 X_REQUEST_ID,
65 X_TRANSF_HEADER_ID,
66 X_CREATION_DATE,
67 X_CREATED_BY,
68 X_LAST_UPDATE_DATE,
69 X_LAST_UPDATED_BY,
70 X_LAST_UPDATE_LOGIN
71 );
72
73 insert into QPR_TRANSF_HEADERS_TL (
74 TRANSF_HEADER_ID,
75 NAME,
76 DESCRIPTION,
77 CREATION_DATE,
78 CREATED_BY,
79 LAST_UPDATE_DATE,
80 LAST_UPDATED_BY,
81 LAST_UPDATE_LOGIN,
82 --PROGRAM_ID,
83 PROGRAM_LOGIN_ID,
84 --PROGRAM_APPLICATION_ID,
85 REQUEST_ID,
86 LANGUAGE,
87 SOURCE_LANG
88 ) select
89 X_TRANSF_HEADER_ID,
90 X_NAME,
91 X_DESCRIPTION,
92 X_CREATION_DATE,
93 X_CREATED_BY,
94 X_LAST_UPDATE_DATE,
95 X_LAST_UPDATED_BY,
96 X_LAST_UPDATE_LOGIN,
97 --X_PROGRAM_ID,
98 X_PROGRAM_LOGIN_ID,
99 --X_PROGRAM_APPLICATION_ID,
100 X_REQUEST_ID,
101 L.LANGUAGE_CODE,
102 userenv('LANG')
103 from FND_LANGUAGES L
104 where L.INSTALLED_FLAG in ('I', 'B')
105 and not exists
106 (select NULL
107 from QPR_TRANSF_HEADERS_TL T
108 where T.TRANSF_HEADER_ID = X_TRANSF_HEADER_ID
109 and T.LANGUAGE = L.LANGUAGE_CODE);
110
111 open c;
112 fetch c into X_ROWID;
113 if (c%notfound) then
114 close c;
115 raise no_data_found;
116 end if;
117 close c;
118
119 end INSERT_ROW;
120
121 procedure LOCK_ROW (
122 X_TRANSF_HEADER_ID in NUMBER,
123 X_TRANSF_GROUP_ID in NUMBER,
124 X_FROM_DIM_MEAS_CODE in VARCHAR2,
125 X_FROM_DIM_HIER_CODE in VARCHAR2,
126 X_LIMIT_DIM_FLAG in VARCHAR2,
127 X_FROM_LEVEL_ID in NUMBER,
128 X_MEAS_CODE in VARCHAR2,
129 X_TO_DIM_CODE in VARCHAR2,
130 X_TO_HIER_CODE in VARCHAR2,
131 X_TO_LEVEL_ID in NUMBER,
132 X_TO_VALUE in VARCHAR2,
133 X_TO_VALUE_DESC in VARCHAR2,
134 X_PROGRAM_LOGIN_ID in NUMBER,
135 X_REQUEST_ID in NUMBER,
136 X_NAME in VARCHAR2,
137 X_DESCRIPTION in VARCHAR2
138 ) is
139 cursor c is select
140 TRANSF_GROUP_ID,
141 FROM_DIM_MEAS_CODE,
142 FROM_DIM_HIER_CODE,
143 LIMIT_DIM_FLAG,
144 FROM_LEVEL_ID,
145 MEAS_CODE,
146 TO_DIM_CODE,
147 TO_HIER_CODE,
148 TO_LEVEL_ID,
149 TO_VALUE,
150 TO_VALUE_DESC,
151 PROGRAM_LOGIN_ID,
152 REQUEST_ID
153 from QPR_TRANSF_HEADERS_B
154 where TRANSF_HEADER_ID = X_TRANSF_HEADER_ID
155 for update of TRANSF_HEADER_ID nowait;
156 recinfo c%rowtype;
157
158 cursor c1 is select
159 NAME,
160 DESCRIPTION,
161 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
162 from QPR_TRANSF_HEADERS_TL
163 where TRANSF_HEADER_ID = X_TRANSF_HEADER_ID
164 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
165 for update of TRANSF_HEADER_ID nowait;
166 begin
167 open c;
168 fetch c into recinfo;
169 if (c%notfound) then
170 close c;
171 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
172 app_exception.raise_exception;
173 end if;
174 close c;
175 if ( (recinfo.TRANSF_GROUP_ID = X_TRANSF_GROUP_ID)
176 AND ((recinfo.FROM_DIM_MEAS_CODE = X_FROM_DIM_MEAS_CODE)
177 OR ((recinfo.FROM_DIM_MEAS_CODE is null) AND (X_FROM_DIM_MEAS_CODE is null)))
178 AND ((recinfo.FROM_DIM_HIER_CODE = X_FROM_DIM_HIER_CODE)
179 OR ((recinfo.FROM_DIM_HIER_CODE is null) AND (X_FROM_DIM_HIER_CODE is null)))
180 AND ((recinfo.LIMIT_DIM_FLAG = X_LIMIT_DIM_FLAG)
181 OR ((recinfo.LIMIT_DIM_FLAG is null) AND (X_LIMIT_DIM_FLAG is null)))
182 AND ((recinfo.FROM_LEVEL_ID = X_FROM_LEVEL_ID)
183 OR ((recinfo.FROM_LEVEL_ID is null) AND (X_FROM_LEVEL_ID is null)))
184 AND ((recinfo.MEAS_CODE = X_MEAS_CODE)
185 OR ((recinfo.MEAS_CODE is null) AND (X_MEAS_CODE is null)))
186 AND ((recinfo.TO_DIM_CODE = X_TO_DIM_CODE)
187 OR ((recinfo.TO_DIM_CODE is null) AND (X_TO_DIM_CODE is null)))
188 AND ((recinfo.TO_HIER_CODE = X_TO_HIER_CODE)
189 OR ((recinfo.TO_HIER_CODE is null) AND (X_TO_HIER_CODE is null)))
190 AND ((recinfo.TO_LEVEL_ID = X_TO_LEVEL_ID)
191 OR ((recinfo.TO_LEVEL_ID is null) AND (X_TO_LEVEL_ID is null)))
192 AND ((recinfo.TO_VALUE = X_TO_VALUE)
193 OR ((recinfo.TO_VALUE is null) AND (X_TO_VALUE is null)))
194 AND ((recinfo.TO_VALUE_DESC = X_TO_VALUE_DESC)
195 OR ((recinfo.TO_VALUE_DESC is null) AND (X_TO_VALUE_DESC is null)))
196 AND ((recinfo.PROGRAM_LOGIN_ID = X_PROGRAM_LOGIN_ID)
197 OR ((recinfo.PROGRAM_LOGIN_ID is null) AND (X_PROGRAM_LOGIN_ID is null)))
198 AND ((recinfo.REQUEST_ID = X_REQUEST_ID)
199 OR ((recinfo.REQUEST_ID is null) AND (X_REQUEST_ID is null)))
200 ) then
201 null;
202 else
203 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
204 app_exception.raise_exception;
205 end if;
206
207 for tlinfo in c1 loop
208 if (tlinfo.BASELANG = 'Y') then
209 if ( (tlinfo.NAME = X_NAME)
210 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
211 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
212 ) then
213 null;
214 else
215 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
216 app_exception.raise_exception;
217 end if;
218 end if;
219 end loop;
220 return;
221 end LOCK_ROW;
222
223 procedure UPDATE_ROW (
224 X_TRANSF_HEADER_ID in NUMBER,
225 X_TRANSF_GROUP_ID in NUMBER,
226 X_FROM_DIM_MEAS_CODE in VARCHAR2,
227 X_FROM_DIM_HIER_CODE in VARCHAR2,
228 X_LIMIT_DIM_FLAG in VARCHAR2,
229 X_FROM_LEVEL_ID in NUMBER,
230 X_MEAS_CODE in VARCHAR2,
231 X_TO_DIM_CODE in VARCHAR2,
232 X_TO_HIER_CODE in VARCHAR2,
233 X_TO_LEVEL_ID in NUMBER,
234 X_TO_VALUE in VARCHAR2,
235 X_TO_VALUE_DESC in VARCHAR2,
236 X_PROGRAM_LOGIN_ID in NUMBER,
237 X_REQUEST_ID in NUMBER,
238 X_NAME in VARCHAR2,
239 X_DESCRIPTION in VARCHAR2,
240 X_LAST_UPDATE_DATE in DATE,
241 X_LAST_UPDATED_BY in NUMBER,
242 X_LAST_UPDATE_LOGIN in NUMBER
243 ) is
244 begin
245 update QPR_TRANSF_HEADERS_B set
246 TRANSF_GROUP_ID = X_TRANSF_GROUP_ID,
247 FROM_DIM_MEAS_CODE = X_FROM_DIM_MEAS_CODE,
248 FROM_DIM_HIER_CODE = X_FROM_DIM_HIER_CODE,
249 LIMIT_DIM_FLAG = X_LIMIT_DIM_FLAG,
250 FROM_LEVEL_ID = X_FROM_LEVEL_ID,
251 MEAS_CODE = X_MEAS_CODE,
252 TO_DIM_CODE = X_TO_DIM_CODE,
253 TO_HIER_CODE = X_TO_HIER_CODE,
254 TO_LEVEL_ID = X_TO_LEVEL_ID,
255 TO_VALUE = X_TO_VALUE,
256 TO_VALUE_DESC = X_TO_VALUE_DESC,
257 PROGRAM_LOGIN_ID = X_PROGRAM_LOGIN_ID,
258 REQUEST_ID = X_REQUEST_ID,
259 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
260 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
261 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
262 where TRANSF_HEADER_ID = X_TRANSF_HEADER_ID;
263
264 if (sql%notfound) then
265 raise no_data_found;
266 end if;
267
268 update QPR_TRANSF_HEADERS_TL set
269 NAME = X_NAME,
270 DESCRIPTION = X_DESCRIPTION,
271 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
272 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
273 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
274 SOURCE_LANG = userenv('LANG')
275 where TRANSF_HEADER_ID = X_TRANSF_HEADER_ID
276 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
277
278 if (sql%notfound) then
279 raise no_data_found;
280 end if;
281 end UPDATE_ROW;
282
283 procedure DELETE_ROW (
284 X_TRANSF_HEADER_ID in NUMBER
285 ) is
286 begin
287 delete from QPR_TRANSF_HEADERS_TL
288 where TRANSF_HEADER_ID = X_TRANSF_HEADER_ID;
289
290 if (sql%notfound) then
291 raise no_data_found;
292 end if;
293
294 delete from QPR_TRANSF_HEADERS_B
295 where TRANSF_HEADER_ID = X_TRANSF_HEADER_ID;
296
297 if (sql%notfound) then
298 raise no_data_found;
299 end if;
300 end DELETE_ROW;
301
302 procedure ADD_LANGUAGE
303 is
304 begin
305 delete from QPR_TRANSF_HEADERS_TL T
306 where not exists
307 (select NULL
308 from QPR_TRANSF_HEADERS_B B
309 where B.TRANSF_HEADER_ID = T.TRANSF_HEADER_ID
310 );
311
312 update QPR_TRANSF_HEADERS_TL T set (
313 NAME,
314 DESCRIPTION
315 ) = (select
316 B.NAME,
317 B.DESCRIPTION
318 from QPR_TRANSF_HEADERS_TL B
319 where B.TRANSF_HEADER_ID = T.TRANSF_HEADER_ID
320 and B.LANGUAGE = T.SOURCE_LANG)
321 where (
322 T.TRANSF_HEADER_ID,
323 T.LANGUAGE
324 ) in (select
325 SUBT.TRANSF_HEADER_ID,
326 SUBT.LANGUAGE
327 from QPR_TRANSF_HEADERS_TL SUBB, QPR_TRANSF_HEADERS_TL SUBT
328 where SUBB.TRANSF_HEADER_ID = SUBT.TRANSF_HEADER_ID
329 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
330 and (SUBB.NAME <> SUBT.NAME
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 QPR_TRANSF_HEADERS_TL (
337 TRANSF_HEADER_ID,
338 NAME,
339 DESCRIPTION,
340 CREATION_DATE,
341 CREATED_BY,
342 LAST_UPDATE_DATE,
343 LAST_UPDATED_BY,
344 LAST_UPDATE_LOGIN,
345 PROGRAM_ID,
346 PROGRAM_LOGIN_ID,
347 PROGRAM_APPLICATION_ID,
348 REQUEST_ID,
349 LANGUAGE,
350 SOURCE_LANG
351 ) select /*+ ORDERED */
352 B.TRANSF_HEADER_ID,
353 B.NAME,
354 B.DESCRIPTION,
355 B.CREATION_DATE,
356 B.CREATED_BY,
357 B.LAST_UPDATE_DATE,
358 B.LAST_UPDATED_BY,
359 B.LAST_UPDATE_LOGIN,
360 B.PROGRAM_ID,
361 B.PROGRAM_LOGIN_ID,
362 B.PROGRAM_APPLICATION_ID,
363 B.REQUEST_ID,
364 L.LANGUAGE_CODE,
365 B.SOURCE_LANG
366 from QPR_TRANSF_HEADERS_TL B, FND_LANGUAGES L
367 where L.INSTALLED_FLAG in ('I', 'B')
368 and B.LANGUAGE = userenv('LANG')
369 and not exists
370 (select NULL
371 from QPR_TRANSF_HEADERS_TL T
372 where T.TRANSF_HEADER_ID = B.TRANSF_HEADER_ID
373 and T.LANGUAGE = L.LANGUAGE_CODE);
374 end ADD_LANGUAGE;
375
376 end QPR_TRANSF_HEADERS_PKG;
[Home] [Help]