1 package body QPR_TRANSF_RULES_PKG as
2 /* $Header: QPRUTRRB.pls 120.0 2007/12/24 20:08:51 vinnaray noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_TRANSF_RULE_ID in NUMBER,
6 X_TRANSF_HEADER_ID in NUMBER,
7 X_LEVEL_VALUE_FROM in VARCHAR2,
8 X_LEVEL_VALUE_TO in VARCHAR2,
9 X_LEVEL_DESC_FROM in VARCHAR2,
10 X_LEVEL_DESC_TO in VARCHAR2,
11 X_LEVEL_VALUE_LIKE_FLAG in VARCHAR2,
12 X_LEVEL_VALUE_NUMBER_FLAG in VARCHAR2,
13 X_ATTRIBUTE1_FROM in VARCHAR2,
14 X_ATTRIBUTE1_TO in VARCHAR2,
15 X_ATTRIBUTE1_NUMBER_FLAG in VARCHAR2,
16 X_ATTRIBUTE2_FROM in VARCHAR2,
17 X_ATTRIBUTE2_TO in VARCHAR2,
18 X_ATTRIBUTE2_NUMBER_FLAG in VARCHAR2,
19 X_ATTRIBUTE3_FROM in VARCHAR2,
20 X_ATTRIBUTE3_TO in VARCHAR2,
21 X_ATTRIBUTE3_NUMBER_FLAG in VARCHAR2,
22 X_ATTRIBUTE4_FROM in VARCHAR2,
23 X_ATTRIBUTE4_TO in VARCHAR2,
24 X_ATTRIBUTE4_NUMBER_FLAG in VARCHAR2,
25 X_ATTRIBUTE5_FROM in VARCHAR2,
26 X_ATTRIBUTE5_TO in VARCHAR2,
27 X_ATTRIBUTE5_NUMBER_FLAG in VARCHAR2,
28 X_LIMIT_DIM_CODE in VARCHAR2,
29 X_LIMIT_DIM_LEVEL in VARCHAR2,
30 X_LIMIT_DIM_LEVEL_VALUE in VARCHAR2,
31 X_PROGRAM_LOGIN_ID in NUMBER,
32 X_REQUEST_ID in NUMBER,
33 X_NAME in VARCHAR2,
34 X_DESCRIPTION in VARCHAR2,
35 X_CREATION_DATE in DATE,
36 X_CREATED_BY in NUMBER,
37 X_LAST_UPDATE_DATE in DATE,
38 X_LAST_UPDATED_BY in NUMBER,
39 X_LAST_UPDATE_LOGIN in NUMBER
40 ) is
41 cursor C is select ROWID from QPR_TRANSF_RULES_B
42 where TRANSF_RULE_ID = X_TRANSF_RULE_ID
43 ;
44 begin
45 insert into QPR_TRANSF_RULES_B (
46 TRANSF_RULE_ID,
47 TRANSF_HEADER_ID,
48 LEVEL_VALUE_FROM,
49 LEVEL_VALUE_TO,
50 LEVEL_DESC_FROM,
51 LEVEL_DESC_TO,
52 LEVEL_VALUE_LIKE_FLAG,
53 LEVEL_VALUE_NUMBER_FLAG,
54 ATTRIBUTE1_FROM,
55 ATTRIBUTE1_TO,
56 ATTRIBUTE1_NUMBER_FLAG,
57 ATTRIBUTE2_FROM,
58 ATTRIBUTE2_TO,
59 ATTRIBUTE2_NUMBER_FLAG,
60 ATTRIBUTE3_FROM,
61 ATTRIBUTE3_TO,
62 ATTRIBUTE3_NUMBER_FLAG,
63 ATTRIBUTE4_FROM,
64 ATTRIBUTE4_TO,
65 ATTRIBUTE4_NUMBER_FLAG,
66 ATTRIBUTE5_FROM,
67 ATTRIBUTE5_TO,
68 ATTRIBUTE5_NUMBER_FLAG,
69 LIMIT_DIM_CODE,
70 LIMIT_DIM_LEVEL,
71 LIMIT_DIM_LEVEL_VALUE,
72 PROGRAM_LOGIN_ID,
73 REQUEST_ID,
74 CREATION_DATE,
75 CREATED_BY,
76 LAST_UPDATE_DATE,
77 LAST_UPDATED_BY,
78 LAST_UPDATE_LOGIN
79 ) values (
80 X_TRANSF_RULE_ID,
81 X_TRANSF_HEADER_ID,
82 X_LEVEL_VALUE_FROM,
83 X_LEVEL_VALUE_TO,
84 X_LEVEL_DESC_FROM,
85 X_LEVEL_DESC_TO,
86 X_LEVEL_VALUE_LIKE_FLAG,
87 X_LEVEL_VALUE_NUMBER_FLAG,
88 X_ATTRIBUTE1_FROM,
89 X_ATTRIBUTE1_TO,
90 X_ATTRIBUTE1_NUMBER_FLAG,
91 X_ATTRIBUTE2_FROM,
92 X_ATTRIBUTE2_TO,
93 X_ATTRIBUTE2_NUMBER_FLAG,
94 X_ATTRIBUTE3_FROM,
95 X_ATTRIBUTE3_TO,
96 X_ATTRIBUTE3_NUMBER_FLAG,
97 X_ATTRIBUTE4_FROM,
98 X_ATTRIBUTE4_TO,
99 X_ATTRIBUTE4_NUMBER_FLAG,
100 X_ATTRIBUTE5_FROM,
101 X_ATTRIBUTE5_TO,
102 X_ATTRIBUTE5_NUMBER_FLAG,
103 X_LIMIT_DIM_CODE,
104 X_LIMIT_DIM_LEVEL,
105 X_LIMIT_DIM_LEVEL_VALUE,
106 X_PROGRAM_LOGIN_ID,
107 X_REQUEST_ID,
108 X_CREATION_DATE,
109 X_CREATED_BY,
110 X_LAST_UPDATE_DATE,
111 X_LAST_UPDATED_BY,
112 X_LAST_UPDATE_LOGIN
113 );
114
115 insert into QPR_TRANSF_RULES_TL (
116 TRANSF_RULE_ID,
117 NAME,
118 DESCRIPTION,
119 CREATION_DATE,
120 CREATED_BY,
121 LAST_UPDATE_DATE,
122 LAST_UPDATED_BY,
123 LAST_UPDATE_LOGIN,
124 --PROGRAM_ID,
125 PROGRAM_LOGIN_ID,
126 --PROGRAM_APPLICATION_ID,
127 REQUEST_ID,
128 LANGUAGE,
129 SOURCE_LANG
130 ) select
131 X_TRANSF_RULE_ID,
132 X_NAME,
133 X_DESCRIPTION,
134 X_CREATION_DATE,
135 X_CREATED_BY,
136 X_LAST_UPDATE_DATE,
137 X_LAST_UPDATED_BY,
138 X_LAST_UPDATE_LOGIN,
139 --X_PROGRAM_ID,
140 X_PROGRAM_LOGIN_ID,
141 --X_PROGRAM_APPLICATION_ID,
142 X_REQUEST_ID,
143 L.LANGUAGE_CODE,
144 userenv('LANG')
145 from FND_LANGUAGES L
146 where L.INSTALLED_FLAG in ('I', 'B')
147 and not exists
148 (select NULL
149 from QPR_TRANSF_RULES_TL T
150 where T.TRANSF_RULE_ID = X_TRANSF_RULE_ID
151 and T.LANGUAGE = L.LANGUAGE_CODE);
152
153 open c;
154 fetch c into X_ROWID;
155 if (c%notfound) then
156 close c;
157 raise no_data_found;
158 end if;
159 close c;
160
161 end INSERT_ROW;
162
163 procedure LOCK_ROW (
164 X_TRANSF_RULE_ID in NUMBER,
165 X_TRANSF_HEADER_ID in NUMBER,
166 X_LEVEL_VALUE_FROM in VARCHAR2,
167 X_LEVEL_VALUE_TO in VARCHAR2,
168 X_LEVEL_DESC_FROM in VARCHAR2,
169 X_LEVEL_DESC_TO in VARCHAR2,
170 X_LEVEL_VALUE_LIKE_FLAG in VARCHAR2,
171 X_LEVEL_VALUE_NUMBER_FLAG in VARCHAR2,
172 X_ATTRIBUTE1_FROM in VARCHAR2,
173 X_ATTRIBUTE1_TO in VARCHAR2,
174 X_ATTRIBUTE1_NUMBER_FLAG in VARCHAR2,
175 X_ATTRIBUTE2_FROM in VARCHAR2,
176 X_ATTRIBUTE2_TO in VARCHAR2,
177 X_ATTRIBUTE2_NUMBER_FLAG in VARCHAR2,
178 X_ATTRIBUTE3_FROM in VARCHAR2,
179 X_ATTRIBUTE3_TO in VARCHAR2,
180 X_ATTRIBUTE3_NUMBER_FLAG in VARCHAR2,
181 X_ATTRIBUTE4_FROM in VARCHAR2,
182 X_ATTRIBUTE4_TO in VARCHAR2,
183 X_ATTRIBUTE4_NUMBER_FLAG in VARCHAR2,
184 X_ATTRIBUTE5_FROM in VARCHAR2,
185 X_ATTRIBUTE5_TO in VARCHAR2,
186 X_ATTRIBUTE5_NUMBER_FLAG in VARCHAR2,
187 X_LIMIT_DIM_CODE in VARCHAR2,
188 X_LIMIT_DIM_LEVEL in VARCHAR2,
189 X_LIMIT_DIM_LEVEL_VALUE in VARCHAR2,
190 X_PROGRAM_LOGIN_ID in NUMBER,
191 X_REQUEST_ID in NUMBER,
192 X_NAME in VARCHAR2,
193 X_DESCRIPTION in VARCHAR2
194 ) is
195 cursor c is select
196 TRANSF_HEADER_ID,
197 LEVEL_VALUE_FROM,
198 LEVEL_VALUE_TO,
199 LEVEL_DESC_FROM,
200 LEVEL_DESC_TO,
201 LEVEL_VALUE_LIKE_FLAG,
202 LEVEL_VALUE_NUMBER_FLAG,
203 ATTRIBUTE1_FROM,
204 ATTRIBUTE1_TO,
205 ATTRIBUTE1_NUMBER_FLAG,
206 ATTRIBUTE2_FROM,
207 ATTRIBUTE2_TO,
208 ATTRIBUTE2_NUMBER_FLAG,
209 ATTRIBUTE3_FROM,
210 ATTRIBUTE3_TO,
211 ATTRIBUTE3_NUMBER_FLAG,
212 ATTRIBUTE4_FROM,
213 ATTRIBUTE4_TO,
214 ATTRIBUTE4_NUMBER_FLAG,
215 ATTRIBUTE5_FROM,
216 ATTRIBUTE5_TO,
217 ATTRIBUTE5_NUMBER_FLAG,
218 LIMIT_DIM_CODE,
219 LIMIT_DIM_LEVEL,
220 LIMIT_DIM_LEVEL_VALUE,
221 PROGRAM_LOGIN_ID,
222 REQUEST_ID
223 from QPR_TRANSF_RULES_B
224 where TRANSF_RULE_ID = X_TRANSF_RULE_ID
225 for update of TRANSF_RULE_ID nowait;
226 recinfo c%rowtype;
227
228 cursor c1 is select
229 NAME,
230 DESCRIPTION,
231 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
232 from QPR_TRANSF_RULES_TL
233 where TRANSF_RULE_ID = X_TRANSF_RULE_ID
234 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
235 for update of TRANSF_RULE_ID nowait;
236 begin
237 open c;
238 fetch c into recinfo;
239 if (c%notfound) then
240 close c;
241 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
242 app_exception.raise_exception;
243 end if;
244 close c;
245 if ( (recinfo.TRANSF_HEADER_ID = X_TRANSF_HEADER_ID)
246 AND ((recinfo.LEVEL_VALUE_FROM = X_LEVEL_VALUE_FROM)
247 OR ((recinfo.LEVEL_VALUE_FROM is null) AND (X_LEVEL_VALUE_FROM is null)))
248 AND ((recinfo.LEVEL_VALUE_TO = X_LEVEL_VALUE_TO)
249 OR ((recinfo.LEVEL_VALUE_TO is null) AND (X_LEVEL_VALUE_TO is null)))
250 AND ((recinfo.LEVEL_DESC_FROM = X_LEVEL_DESC_FROM)
251 OR ((recinfo.LEVEL_DESC_FROM is null) AND (X_LEVEL_DESC_FROM is null)))
252 AND ((recinfo.LEVEL_DESC_TO = X_LEVEL_DESC_TO)
253 OR ((recinfo.LEVEL_DESC_TO is null) AND (X_LEVEL_DESC_TO is null)))
254 AND ((recinfo.LEVEL_VALUE_LIKE_FLAG = X_LEVEL_VALUE_LIKE_FLAG)
255 OR ((recinfo.LEVEL_VALUE_LIKE_FLAG is null) AND (X_LEVEL_VALUE_LIKE_FLAG is null)))
256 AND ((recinfo.LEVEL_VALUE_NUMBER_FLAG = X_LEVEL_VALUE_NUMBER_FLAG)
257 OR ((recinfo.LEVEL_VALUE_NUMBER_FLAG is null) AND (X_LEVEL_VALUE_NUMBER_FLAG is null)))
258 AND ((recinfo.ATTRIBUTE1_FROM = X_ATTRIBUTE1_FROM)
259 OR ((recinfo.ATTRIBUTE1_FROM is null) AND (X_ATTRIBUTE1_FROM is null)))
260 AND ((recinfo.ATTRIBUTE1_TO = X_ATTRIBUTE1_TO)
261 OR ((recinfo.ATTRIBUTE1_TO is null) AND (X_ATTRIBUTE1_TO is null)))
262 AND ((recinfo.ATTRIBUTE1_NUMBER_FLAG = X_ATTRIBUTE1_NUMBER_FLAG)
263 OR ((recinfo.ATTRIBUTE1_NUMBER_FLAG is null) AND (X_ATTRIBUTE1_NUMBER_FLAG is null)))
264 AND ((recinfo.ATTRIBUTE2_FROM = X_ATTRIBUTE2_FROM)
265 OR ((recinfo.ATTRIBUTE2_FROM is null) AND (X_ATTRIBUTE2_FROM is null)))
266 AND ((recinfo.ATTRIBUTE2_TO = X_ATTRIBUTE2_TO)
267 OR ((recinfo.ATTRIBUTE2_TO is null) AND (X_ATTRIBUTE2_TO is null)))
268 AND ((recinfo.ATTRIBUTE2_NUMBER_FLAG = X_ATTRIBUTE2_NUMBER_FLAG)
269 OR ((recinfo.ATTRIBUTE2_NUMBER_FLAG is null) AND (X_ATTRIBUTE2_NUMBER_FLAG is null)))
270 AND ((recinfo.ATTRIBUTE3_FROM = X_ATTRIBUTE3_FROM)
271 OR ((recinfo.ATTRIBUTE3_FROM is null) AND (X_ATTRIBUTE3_FROM is null)))
272 AND ((recinfo.ATTRIBUTE3_TO = X_ATTRIBUTE3_TO)
273 OR ((recinfo.ATTRIBUTE3_TO is null) AND (X_ATTRIBUTE3_TO is null)))
274 AND ((recinfo.ATTRIBUTE3_NUMBER_FLAG = X_ATTRIBUTE3_NUMBER_FLAG)
275 OR ((recinfo.ATTRIBUTE3_NUMBER_FLAG is null) AND (X_ATTRIBUTE3_NUMBER_FLAG is null)))
276 AND ((recinfo.ATTRIBUTE4_FROM = X_ATTRIBUTE4_FROM)
277 OR ((recinfo.ATTRIBUTE4_FROM is null) AND (X_ATTRIBUTE4_FROM is null)))
278 AND ((recinfo.ATTRIBUTE4_TO = X_ATTRIBUTE4_TO)
279 OR ((recinfo.ATTRIBUTE4_TO is null) AND (X_ATTRIBUTE4_TO is null)))
280 AND ((recinfo.ATTRIBUTE4_NUMBER_FLAG = X_ATTRIBUTE4_NUMBER_FLAG)
281 OR ((recinfo.ATTRIBUTE4_NUMBER_FLAG is null) AND (X_ATTRIBUTE4_NUMBER_FLAG is null)))
282 AND ((recinfo.ATTRIBUTE5_FROM = X_ATTRIBUTE5_FROM)
283 OR ((recinfo.ATTRIBUTE5_FROM is null) AND (X_ATTRIBUTE5_FROM is null)))
284 AND ((recinfo.ATTRIBUTE5_TO = X_ATTRIBUTE5_TO)
285 OR ((recinfo.ATTRIBUTE5_TO is null) AND (X_ATTRIBUTE5_TO is null)))
286 AND ((recinfo.ATTRIBUTE5_NUMBER_FLAG = X_ATTRIBUTE5_NUMBER_FLAG)
287 OR ((recinfo.ATTRIBUTE5_NUMBER_FLAG is null) AND (X_ATTRIBUTE5_NUMBER_FLAG is null)))
288 AND ((recinfo.LIMIT_DIM_CODE = X_LIMIT_DIM_CODE)
289 OR ((recinfo.LIMIT_DIM_CODE is null) AND (X_LIMIT_DIM_CODE is null)))
290 AND ((recinfo.LIMIT_DIM_LEVEL = X_LIMIT_DIM_LEVEL)
291 OR ((recinfo.LIMIT_DIM_LEVEL is null) AND (X_LIMIT_DIM_LEVEL is null)))
292 AND ((recinfo.LIMIT_DIM_LEVEL_VALUE = X_LIMIT_DIM_LEVEL_VALUE)
293 OR ((recinfo.LIMIT_DIM_LEVEL_VALUE is null) AND (X_LIMIT_DIM_LEVEL_VALUE is null)))
294 AND ((recinfo.PROGRAM_LOGIN_ID = X_PROGRAM_LOGIN_ID)
295 OR ((recinfo.PROGRAM_LOGIN_ID is null) AND (X_PROGRAM_LOGIN_ID is null)))
296 AND ((recinfo.REQUEST_ID = X_REQUEST_ID)
297 OR ((recinfo.REQUEST_ID is null) AND (X_REQUEST_ID is null)))
298 ) then
299 null;
300 else
301 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
302 app_exception.raise_exception;
303 end if;
304
305 for tlinfo in c1 loop
306 if (tlinfo.BASELANG = 'Y') then
307 if ( (tlinfo.NAME = X_NAME)
308 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
309 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
310 ) then
311 null;
312 else
313 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
314 app_exception.raise_exception;
315 end if;
316 end if;
317 end loop;
318 return;
319 end LOCK_ROW;
320
321 procedure UPDATE_ROW (
322 X_TRANSF_RULE_ID in NUMBER,
323 X_TRANSF_HEADER_ID in NUMBER,
324 X_LEVEL_VALUE_FROM in VARCHAR2,
325 X_LEVEL_VALUE_TO in VARCHAR2,
326 X_LEVEL_DESC_FROM in VARCHAR2,
327 X_LEVEL_DESC_TO in VARCHAR2,
328 X_LEVEL_VALUE_LIKE_FLAG in VARCHAR2,
329 X_LEVEL_VALUE_NUMBER_FLAG in VARCHAR2,
330 X_ATTRIBUTE1_FROM in VARCHAR2,
331 X_ATTRIBUTE1_TO in VARCHAR2,
332 X_ATTRIBUTE1_NUMBER_FLAG in VARCHAR2,
333 X_ATTRIBUTE2_FROM in VARCHAR2,
334 X_ATTRIBUTE2_TO in VARCHAR2,
335 X_ATTRIBUTE2_NUMBER_FLAG in VARCHAR2,
336 X_ATTRIBUTE3_FROM in VARCHAR2,
337 X_ATTRIBUTE3_TO in VARCHAR2,
338 X_ATTRIBUTE3_NUMBER_FLAG in VARCHAR2,
339 X_ATTRIBUTE4_FROM in VARCHAR2,
340 X_ATTRIBUTE4_TO in VARCHAR2,
341 X_ATTRIBUTE4_NUMBER_FLAG in VARCHAR2,
342 X_ATTRIBUTE5_FROM in VARCHAR2,
343 X_ATTRIBUTE5_TO in VARCHAR2,
344 X_ATTRIBUTE5_NUMBER_FLAG in VARCHAR2,
345 X_LIMIT_DIM_CODE in VARCHAR2,
346 X_LIMIT_DIM_LEVEL in VARCHAR2,
347 X_LIMIT_DIM_LEVEL_VALUE in VARCHAR2,
348 X_PROGRAM_LOGIN_ID in NUMBER,
349 X_REQUEST_ID in NUMBER,
350 X_NAME in VARCHAR2,
351 X_DESCRIPTION in VARCHAR2,
352 X_LAST_UPDATE_DATE in DATE,
353 X_LAST_UPDATED_BY in NUMBER,
354 X_LAST_UPDATE_LOGIN in NUMBER
355 ) is
356 begin
357 update QPR_TRANSF_RULES_B set
358 TRANSF_HEADER_ID = X_TRANSF_HEADER_ID,
359 LEVEL_VALUE_FROM = X_LEVEL_VALUE_FROM,
360 LEVEL_VALUE_TO = X_LEVEL_VALUE_TO,
361 LEVEL_DESC_FROM = X_LEVEL_DESC_FROM,
362 LEVEL_DESC_TO = X_LEVEL_DESC_TO,
363 LEVEL_VALUE_LIKE_FLAG = X_LEVEL_VALUE_LIKE_FLAG,
364 LEVEL_VALUE_NUMBER_FLAG = X_LEVEL_VALUE_NUMBER_FLAG,
365 ATTRIBUTE1_FROM = X_ATTRIBUTE1_FROM,
366 ATTRIBUTE1_TO = X_ATTRIBUTE1_TO,
367 ATTRIBUTE1_NUMBER_FLAG = X_ATTRIBUTE1_NUMBER_FLAG,
368 ATTRIBUTE2_FROM = X_ATTRIBUTE2_FROM,
369 ATTRIBUTE2_TO = X_ATTRIBUTE2_TO,
370 ATTRIBUTE2_NUMBER_FLAG = X_ATTRIBUTE2_NUMBER_FLAG,
371 ATTRIBUTE3_FROM = X_ATTRIBUTE3_FROM,
372 ATTRIBUTE3_TO = X_ATTRIBUTE3_TO,
373 ATTRIBUTE3_NUMBER_FLAG = X_ATTRIBUTE3_NUMBER_FLAG,
374 ATTRIBUTE4_FROM = X_ATTRIBUTE4_FROM,
375 ATTRIBUTE4_TO = X_ATTRIBUTE4_TO,
376 ATTRIBUTE4_NUMBER_FLAG = X_ATTRIBUTE4_NUMBER_FLAG,
377 ATTRIBUTE5_FROM = X_ATTRIBUTE5_FROM,
378 ATTRIBUTE5_TO = X_ATTRIBUTE5_TO,
379 ATTRIBUTE5_NUMBER_FLAG = X_ATTRIBUTE5_NUMBER_FLAG,
380 LIMIT_DIM_CODE = X_LIMIT_DIM_CODE,
381 LIMIT_DIM_LEVEL = X_LIMIT_DIM_LEVEL,
382 LIMIT_DIM_LEVEL_VALUE = X_LIMIT_DIM_LEVEL_VALUE,
383 PROGRAM_LOGIN_ID = X_PROGRAM_LOGIN_ID,
384 REQUEST_ID = X_REQUEST_ID,
385 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
386 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
387 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
388 where TRANSF_RULE_ID = X_TRANSF_RULE_ID;
389
390 if (sql%notfound) then
391 raise no_data_found;
392 end if;
393
394 update QPR_TRANSF_RULES_TL set
395 NAME = X_NAME,
396 DESCRIPTION = X_DESCRIPTION,
397 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
398 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
399 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
400 SOURCE_LANG = userenv('LANG')
401 where TRANSF_RULE_ID = X_TRANSF_RULE_ID
402 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
403
404 if (sql%notfound) then
405 raise no_data_found;
406 end if;
407 end UPDATE_ROW;
408
409 procedure DELETE_ROW (
410 X_TRANSF_RULE_ID in NUMBER
411 ) is
412 begin
413 delete from QPR_TRANSF_RULES_TL
414 where TRANSF_RULE_ID = X_TRANSF_RULE_ID;
415
416 if (sql%notfound) then
417 raise no_data_found;
418 end if;
419
420 delete from QPR_TRANSF_RULES_B
421 where TRANSF_RULE_ID = X_TRANSF_RULE_ID;
422
423 if (sql%notfound) then
424 raise no_data_found;
425 end if;
426 end DELETE_ROW;
427
428 procedure ADD_LANGUAGE
429 is
430 begin
431 delete from QPR_TRANSF_RULES_TL T
432 where not exists
433 (select NULL
434 from QPR_TRANSF_RULES_B B
435 where B.TRANSF_RULE_ID = T.TRANSF_RULE_ID
436 );
437
438 update QPR_TRANSF_RULES_TL T set (
439 NAME,
440 DESCRIPTION
441 ) = (select
442 B.NAME,
443 B.DESCRIPTION
444 from QPR_TRANSF_RULES_TL B
445 where B.TRANSF_RULE_ID = T.TRANSF_RULE_ID
446 and B.LANGUAGE = T.SOURCE_LANG)
447 where (
448 T.TRANSF_RULE_ID,
449 T.LANGUAGE
450 ) in (select
451 SUBT.TRANSF_RULE_ID,
452 SUBT.LANGUAGE
453 from QPR_TRANSF_RULES_TL SUBB, QPR_TRANSF_RULES_TL SUBT
454 where SUBB.TRANSF_RULE_ID = SUBT.TRANSF_RULE_ID
455 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
456 and (SUBB.NAME <> SUBT.NAME
457 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
458 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
459 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
460 ));
461
462 insert into QPR_TRANSF_RULES_TL (
463 TRANSF_RULE_ID,
464 NAME,
465 DESCRIPTION,
466 CREATION_DATE,
467 CREATED_BY,
468 LAST_UPDATE_DATE,
469 LAST_UPDATED_BY,
470 LAST_UPDATE_LOGIN,
471 PROGRAM_ID,
472 PROGRAM_LOGIN_ID,
473 PROGRAM_APPLICATION_ID,
474 REQUEST_ID,
475 LANGUAGE,
476 SOURCE_LANG
477 ) select /*+ ORDERED */
478 B.TRANSF_RULE_ID,
479 B.NAME,
480 B.DESCRIPTION,
481 B.CREATION_DATE,
482 B.CREATED_BY,
483 B.LAST_UPDATE_DATE,
484 B.LAST_UPDATED_BY,
485 B.LAST_UPDATE_LOGIN,
486 B.PROGRAM_ID,
487 B.PROGRAM_LOGIN_ID,
488 B.PROGRAM_APPLICATION_ID,
489 B.REQUEST_ID,
490 L.LANGUAGE_CODE,
491 B.SOURCE_LANG
492 from QPR_TRANSF_RULES_TL B, FND_LANGUAGES L
493 where L.INSTALLED_FLAG in ('I', 'B')
494 and B.LANGUAGE = userenv('LANG')
495 and not exists
496 (select NULL
497 from QPR_TRANSF_RULES_TL T
498 where T.TRANSF_RULE_ID = B.TRANSF_RULE_ID
499 and T.LANGUAGE = L.LANGUAGE_CODE);
500 end ADD_LANGUAGE;
501
502 end QPR_TRANSF_RULES_PKG;
[Home] [Help]