[Home] [Help]
PACKAGE BODY: APPS.HZ_TRANS_FUNCTIONS_PKG
Source
1 package BODY HZ_TRANS_FUNCTIONS_PKG AS
2 /*$Header: ARHDQTFB.pls 120.11 2006/03/18 12:41:14 rarajend noship $ */
3
4 function get_valid_tx_column(X_STAGED_ATTRIBUTE_TABLE VARCHAR2,
5 X_STAGED_ATTRIBUTE_COLUMN VARCHAR2) return varchar2;
6 procedure INSERT_ROW (
7 X_FUNCTION_ID IN OUT NOCOPY NUMBER,
8 X_STAGED_ATTRIBUTE_TABLE in VARCHAR2,
9 X_STAGED_ATTRIBUTE_COLUMN in VARCHAR2,
10 X_STAGED_FLAG in VARCHAR2,
11 X_ATTRIBUTE_ID in NUMBER,
12 X_PROCEDURE_NAME in VARCHAR2,
13 X_ACTIVE_FLAG in VARCHAR2,
14 X_PRIMARY_FLAG in VARCHAR2,
15 X_INDEX_REQUIRED_FLAG in VARCHAR2,
16 X_TRANSFORMATION_NAME in VARCHAR2,
17 X_DESCRIPTION in VARCHAR2,
18 X_CREATION_DATE in DATE,
19 X_CREATED_BY in NUMBER,
20 X_LAST_UPDATE_DATE in DATE,
21 X_LAST_UPDATED_BY in NUMBER,
22 X_LAST_UPDATE_LOGIN in NUMBER,
23 X_OBJECT_VERSION_NUMBER in NUMBER
24 ) is
25 CURSOR C2 IS SELECT HZ_TRANS_FUNCTIONS_s.nextval FROM sys.dual;
26 l_success VARCHAR2(1) := 'N';
27 l_staged_attribute_column varchar2(255);
28 begin
29 l_staged_attribute_column := get_valid_tx_column(X_STAGED_ATTRIBUTE_TABLE,X_STAGED_ATTRIBUTE_COLUMN);
30 WHILE l_success = 'N' LOOP
31 BEGIN
32 IF ( X_FUNCTION_ID IS NULL) OR (X_FUNCTION_ID = FND_API.G_MISS_NUM) THEN
33 OPEN C2;
34 FETCH C2 INTO X_FUNCTION_ID;
35 CLOSE C2;
36 END IF;
37
38 insert into HZ_TRANS_FUNCTIONS_B (
39 STAGED_ATTRIBUTE_TABLE,
40 STAGED_ATTRIBUTE_COLUMN,
41 STAGED_FLAG,
42 FUNCTION_ID,
43 ATTRIBUTE_ID,
44 PROCEDURE_NAME,
45 ACTIVE_FLAG,
46 PRIMARY_FLAG,
47 INDEX_REQUIRED_FLAG,
48 CREATION_DATE,
49 CREATED_BY,
50 LAST_UPDATE_DATE,
51 LAST_UPDATED_BY,
52 LAST_UPDATE_LOGIN,
53 OBJECT_VERSION_NUMBER
54 ) values (
55 X_STAGED_ATTRIBUTE_TABLE,
56 l_staged_attribute_column, --Bug No:4260144
57 nvl(X_STAGED_FLAG,'N'),
58 X_FUNCTION_ID,
59 X_ATTRIBUTE_ID,
60 X_PROCEDURE_NAME,
61 X_ACTIVE_FLAG,
62 X_PRIMARY_FLAG,
63 X_INDEX_REQUIRED_FLAG,
64 X_CREATION_DATE,
65 X_CREATED_BY,
66 X_LAST_UPDATE_DATE,
67 X_LAST_UPDATED_BY,
68 X_LAST_UPDATE_LOGIN,
69 1
70 );
71
72 l_success := 'Y';
73 EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
74 IF INSTRB( SQLERRM, 'HZ_TRANS_FUNCTIONS_B_U1' ) <> 0 THEN
75 DECLARE
76 l_count NUMBER;
77 l_dummy VARCHAR2(1);
78 BEGIN
79 l_count := 1;
80 WHILE l_count > 0 LOOP
81 SELECT HZ_TRANS_FUNCTIONS_s.nextval
82 into X_FUNCTION_ID FROM sys.dual;
83 BEGIN
84 SELECT 'Y' INTO l_dummy
85 FROM HZ_TRANS_FUNCTIONS_B
86 WHERE FUNCTION_ID = X_FUNCTION_ID;
87 l_count := 1;
88 EXCEPTION WHEN NO_DATA_FOUND THEN
89 l_count := 0;
90 END;
91 END LOOP;
92 END;
93 END IF;
94 END;
95 END LOOP;
96
97 insert into HZ_TRANS_FUNCTIONS_TL (
98 LAST_UPDATE_DATE,
99 CREATION_DATE,
100 CREATED_BY,
101 LAST_UPDATED_BY,
102 LAST_UPDATE_LOGIN,
103 FUNCTION_ID,
104 TRANSFORMATION_NAME,
105 DESCRIPTION,
106 LANGUAGE,
107 SOURCE_LANG,
108 OBJECT_VERSION_NUMBER
109 ) select
110 X_LAST_UPDATE_DATE,
111 X_CREATION_DATE,
112 X_CREATED_BY,
113 X_LAST_UPDATED_BY,
114 X_LAST_UPDATE_LOGIN,
115 X_FUNCTION_ID,
116 X_TRANSFORMATION_NAME,
117 X_DESCRIPTION,
118 L.LANGUAGE_CODE,
119 userenv('LANG'),
120 1
121 from FND_LANGUAGES L
122 where L.INSTALLED_FLAG in ('I', 'B')
123 and not exists
124 (select NULL
125 from HZ_TRANS_FUNCTIONS_TL T
126 where T.FUNCTION_ID = X_FUNCTION_ID
127 and T.LANGUAGE = L.LANGUAGE_CODE);
128
129 end INSERT_ROW;
130
131
132 procedure LOCK_ROW (
133 X_FUNCTION_ID in NUMBER,
134 X_OBJECT_VERSION_NUMBER IN NUMBER
135 ) is
136 cursor c is select
137 OBJECT_VERSION_NUMBER
138 from HZ_TRANS_FUNCTIONS_B
139 where FUNCTION_ID = X_FUNCTION_ID
140 for update of FUNCTION_ID nowait;
141 recinfo c%rowtype;
142
143 cursor c1 is select
144 OBJECT_VERSION_NUMBER,
145 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
146 from HZ_TRANS_FUNCTIONS_TL
147 where FUNCTION_ID = X_FUNCTION_ID
148 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
149 for update of FUNCTION_ID nowait;
150 begin
151 open c;
152 fetch c into recinfo;
153 if (c%notfound) then
154 close c;
155 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
156 app_exception.raise_exception;
157 end if;
158 close c;
159 if ( ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
160 OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
161 ) then
162 null;
163 else
164 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
165 app_exception.raise_exception;
166 end if;
167 for tlinfo in c1 loop
168 if (tlinfo.BASELANG = 'Y') then
169 if ( ((tlinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
170 OR ((tlinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
171 ) then
172 null;
173 else
174 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
175 app_exception.raise_exception;
176 end if;
177 end if;
178 end loop;
179 return;
180 end LOCK_ROW;
181
182 procedure LOCK_ROW (
183 X_FUNCTION_ID in NUMBER,
184 X_OBJECT_VERSION_NUMBER IN NUMBER,
185 X_TRANSFORMATION_NAME IN VARCHAR2,
186 X_DESCRIPTION IN VARCHAR2
187
188 ) is
189 cursor c is select
190 OBJECT_VERSION_NUMBER
191 from HZ_TRANS_FUNCTIONS_B
192 where FUNCTION_ID = X_FUNCTION_ID
193 for update of FUNCTION_ID nowait;
194 recinfo c%rowtype;
195
196 cursor c1 is select
197 TRANSFORMATION_NAME,DESCRIPTION,
198 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
199 from HZ_TRANS_FUNCTIONS_TL
200 where FUNCTION_ID = X_FUNCTION_ID
201 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
202 for update of FUNCTION_ID nowait;
203 begin
204 open c;
205 fetch c into recinfo;
206 if (c%notfound) then
207 close c;
208 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
209 app_exception.raise_exception;
210 end if;
211 close c;
212 if ( ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
213 OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
214 ) then
215 null;
216 else
217 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
218 app_exception.raise_exception;
219 end if;
220 for tlinfo in c1 loop
221 if (tlinfo.BASELANG = 'Y') then
222 if ( ((tlinfo.TRANSFORMATION_NAME = X_TRANSFORMATION_NAME)
223 OR ((tlinfo.TRANSFORMATION_NAME is null) AND ( X_TRANSFORMATION_NAME is null)))
224 ) then
225 if ( ((tlinfo.DESCRIPTION = X_DESCRIPTION)
226 OR ((tlinfo.DESCRIPTION is null) AND ( X_DESCRIPTION is null)))
227 ) then
228 null;
229 else
230 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
231 app_exception.raise_exception;
232 end if;
233 end if;
234 end if;
235 end loop;
236 return;
237 end LOCK_ROW;
238
239
240 procedure UPDATE_ROW (
241 X_FUNCTION_ID in NUMBER,
242 X_STAGED_ATTRIBUTE_TABLE in VARCHAR2,
243 X_STAGED_ATTRIBUTE_COLUMN in VARCHAR2,
244 X_STAGED_FLAG in VARCHAR2,
245 X_ATTRIBUTE_ID in NUMBER,
246 X_PROCEDURE_NAME in VARCHAR2,
247 X_ACTIVE_FLAG in VARCHAR2,
248 X_PRIMARY_FLAG in VARCHAR2,
249 X_INDEX_REQUIRED_FLAG in VARCHAR2,
250 X_TRANSFORMATION_NAME in VARCHAR2,
251 X_DESCRIPTION in VARCHAR2,
252 X_LAST_UPDATE_DATE in DATE,
253 X_LAST_UPDATED_BY in NUMBER,
254 X_LAST_UPDATE_LOGIN in NUMBER,
255 X_OBJECT_VERSION_NUMBER IN out NOCOPY NUMBER
256 ) is
257
258 l_object_version_number NUMBER;
259 l_db_act_flag VARCHAR2(1);
260 l_db_proc_name VARCHAR2(256);
264 l_db_desc VARCHAR2(1000);
261 l_db_primary_flag VARCHAR2(1);
262 l_db_upd_by NUMBER;
263 l_db_trans_name VARCHAR2(100);
265 l_db_stg_atr_col VARCHAR2(30);
266 l_db_stg_flag VARCHAR2(1);
267 l_db_index_req_flag VARCHAR2(1);
268 L_STAGED_FLAG VARCHAR2(1);
269 L_STAGED_ATTRIBUTE_COLUMN VARCHAR2(255);
270 TMP NUMBER;
271
272 begin
273
274 SELECT 1 INTO TMP FROM HZ_TRANS_FUNCTIONS_VL
275 WHERE function_id = X_FUNCTION_ID;
276
277 SELECT nvl(ACTIVE_FLAG,'Y'), PROCEDURE_NAME,
278 nvl(PRIMARY_FLAG,'N'), last_updated_by, staged_flag,
279 transformation_name, description,
280 nvl(INDEX_REQUIRED_FLAG, 'N'), STAGED_ATTRIBUTE_COLUMN
281 into l_db_act_flag, l_db_proc_name, l_db_primary_flag, l_db_upd_by, l_db_stg_flag,
282 l_db_trans_name, l_db_desc, l_db_index_req_flag, l_db_stg_atr_col
283 from HZ_TRANS_FUNCTIONS_VL
284 where function_id =X_FUNCTION_ID;
285 l_object_version_number := NVL(X_object_version_number, 1) + 1;
286
287 IF (X_LAST_UPDATED_BY = 1 AND l_db_upd_by <> 1) THEN
288 -- coming from seed and data modified by user
289 IF (X_PROCEDURE_NAME <>l_db_proc_name) THEN
290 update HZ_TRANS_FUNCTIONS_B set
291 PROCEDURE_NAME = X_PROCEDURE_NAME,
292 STAGED_FLAG = 'N',
293 OBJECT_VERSION_NUMBER = l_object_version_number
294 where FUNCTION_ID = X_FUNCTION_ID;
295 update HZ_TRANS_FUNCTIONS_TL set
296 TRANSFORMATION_NAME = X_TRANSFORMATION_NAME,
297 DESCRIPTION = X_DESCRIPTION,
298 OBJECT_VERSION_NUMBER = l_object_version_number
299 where FUNCTION_ID = X_FUNCTION_ID
300 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
301 END IF;
302 ELSE
303 IF ((l_db_act_flag<>nvl(X_ACTIVE_FLAG,'Y'))
304 OR ((l_db_primary_flag='N') AND (nvl(X_PRIMARY_FLAG,'N')='Y'))
305 OR (X_PROCEDURE_NAME<>l_db_proc_name)) THEN
306 L_STAGED_FLAG:='N';
307 ELSE
308 L_STAGED_FLAG:=l_db_stg_flag;
309 END IF;
310 IF ((l_db_act_flag = 'N') AND (X_ACTIVE_FLAG = 'Y')) THEN
311 l_staged_attribute_column := get_valid_tx_column(X_STAGED_ATTRIBUTE_TABLE,X_STAGED_ATTRIBUTE_COLUMN); --Bug No:4260144
312 --L_STAGED_ATTRIBUTE_COLUMN := X_STAGED_ATTRIBUTE_COLUMN;
313 ELSE
314 L_STAGED_ATTRIBUTE_COLUMN := l_db_stg_atr_col;
315 END IF;
316 update HZ_TRANS_FUNCTIONS_B set
317 STAGED_ATTRIBUTE_TABLE = X_STAGED_ATTRIBUTE_TABLE,
318 STAGED_ATTRIBUTE_COLUMN = L_STAGED_ATTRIBUTE_COLUMN,
319 ATTRIBUTE_ID = X_ATTRIBUTE_ID,
320 PROCEDURE_NAME = X_PROCEDURE_NAME,
321 ACTIVE_FLAG = X_ACTIVE_FLAG,
322 PRIMARY_FLAG = X_PRIMARY_FLAG,
323 INDEX_REQUIRED_FLAG = X_INDEX_REQUIRED_FLAG,
324 STAGED_FLAG = L_STAGED_FLAG,
325 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
326 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
327 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
328 OBJECT_VERSION_NUMBER = l_object_version_number
329 where FUNCTION_ID = X_FUNCTION_ID;
330 update HZ_TRANS_FUNCTIONS_TL set
331 TRANSFORMATION_NAME = X_TRANSFORMATION_NAME,
332 DESCRIPTION = X_DESCRIPTION,
333 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
334 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
335 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
336 SOURCE_LANG = userenv('LANG')
337 -- OBJECT_VERSION_NUMBER = l_object_version_number
338 where FUNCTION_ID = X_FUNCTION_ID
339 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
340 END IF;
341 X_object_version_number := l_object_version_number;
342 end UPDATE_ROW;
343
344
345 procedure DELETE_ROW (
346 X_FUNCTION_ID in NUMBER
347 ) is
348 begin
349 delete from HZ_TRANS_FUNCTIONS_TL
350 where FUNCTION_ID = X_FUNCTION_ID;
351
352 if (sql%notfound) then
353 raise no_data_found;
354 end if;
355
356 delete from HZ_TRANS_FUNCTIONS_B
357 where FUNCTION_ID = X_FUNCTION_ID;
358
359 if (sql%notfound) then
360 raise no_data_found;
361 end if;
362 end DELETE_ROW;
363
364 procedure ADD_LANGUAGE
365 is
366 begin
367 delete from HZ_TRANS_FUNCTIONS_TL T
368 where not exists
369 (select NULL
370 from HZ_TRANS_FUNCTIONS_B B
371 where B.FUNCTION_ID = T.FUNCTION_ID
372 );
373
374 update HZ_TRANS_FUNCTIONS_TL T set (
375 TRANSFORMATION_NAME,
376 DESCRIPTION
377 ) = (select
378 B.TRANSFORMATION_NAME,
379 B.DESCRIPTION
380 from HZ_TRANS_FUNCTIONS_TL B
381 where B.FUNCTION_ID = T.FUNCTION_ID
382 and B.LANGUAGE = T.SOURCE_LANG)
383 where (
384 T.FUNCTION_ID,
385 T.LANGUAGE
386 ) in (select
387 SUBT.FUNCTION_ID,
388 SUBT.LANGUAGE
389 from HZ_TRANS_FUNCTIONS_TL SUBB, HZ_TRANS_FUNCTIONS_TL SUBT
390 where SUBB.FUNCTION_ID = SUBT.FUNCTION_ID
391 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
392 and (SUBB.TRANSFORMATION_NAME <> SUBT.TRANSFORMATION_NAME
393 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
394 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
395 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
396 ));
397
398 insert into HZ_TRANS_FUNCTIONS_TL (
399 LAST_UPDATE_DATE,
400 CREATION_DATE,
401 CREATED_BY,
402 LAST_UPDATED_BY,
403 LAST_UPDATE_LOGIN,
404 FUNCTION_ID,
405 TRANSFORMATION_NAME,
406 DESCRIPTION,
407 LANGUAGE,
408 SOURCE_LANG
409 ) select
410 B.LAST_UPDATE_DATE,
411 B.CREATION_DATE,
412 B.CREATED_BY,
413 B.LAST_UPDATED_BY,
417 B.DESCRIPTION,
414 B.LAST_UPDATE_LOGIN,
415 B.FUNCTION_ID,
416 B.TRANSFORMATION_NAME,
418 L.LANGUAGE_CODE,
419 B.SOURCE_LANG
420 from HZ_TRANS_FUNCTIONS_TL B, FND_LANGUAGES L
421 where L.INSTALLED_FLAG in ('I', 'B')
422 and B.LANGUAGE = userenv('LANG')
423 and L.LANGUAGE_CODE <> B.LANGUAGE
424 and not exists
425 (select NULL
426 from HZ_TRANS_FUNCTIONS_TL T
427 where T.FUNCTION_ID = B.FUNCTION_ID
428 and T.LANGUAGE = L.LANGUAGE_CODE);
429 end ADD_LANGUAGE;
430
431 procedure LOAD_ROW (
432 X_FUNCTION_ID in NUMBER,
433 X_STAGED_ATTRIBUTE_TABLE in VARCHAR2,
434 X_STAGED_ATTRIBUTE_COLUMN in VARCHAR2,
435 X_STAGED_FLAG in VARCHAR2,
436 X_ATTRIBUTE_ID in NUMBER,
437 X_PROCEDURE_NAME in VARCHAR2,
438 X_ACTIVE_FLAG in VARCHAR2,
439 X_PRIMARY_FLAG in VARCHAR2,
440 X_INDEX_REQUIRED_FLAG in VARCHAR2,
441 X_TRANSFORMATION_NAME in VARCHAR2,
442 X_DESCRIPTION in VARCHAR2,
443 X_LAST_UPDATE_DATE in DATE,
444 X_LAST_UPDATED_BY in NUMBER,
445 X_LAST_UPDATE_LOGIN in NUMBER,
446 X_OBJECT_VERSION_NUMBER in NUMBER,
447 X_OWNER in VARCHAR2) IS
448
449 begin
450
451 declare
452 user_id number := 0;
453 row_id varchar2(64);
454 L_FUNCTION_ID NUMBER := X_FUNCTION_ID;
455 L_OBJECT_VERSION_NUMBER number;
456
457 begin
458
459 if (X_OWNER = 'SEED') then
460 user_id := 1;
461 end if;
462
463 L_OBJECT_VERSION_NUMBER := NVL(X_OBJECT_VERSION_NUMBER, 1) + 1;
464
465 HZ_TRANS_FUNCTIONS_PKG.UPDATE_ROW(
466 X_FUNCTION_ID =>X_FUNCTION_ID,
467 X_STAGED_ATTRIBUTE_TABLE =>X_STAGED_ATTRIBUTE_TABLE,
468 X_STAGED_ATTRIBUTE_COLUMN =>X_STAGED_ATTRIBUTE_COLUMN,
469 X_STAGED_FLAG =>X_STAGED_FLAG,
470 X_ATTRIBUTE_ID =>X_ATTRIBUTE_ID,
471 X_PROCEDURE_NAME =>X_PROCEDURE_NAME,
472 X_ACTIVE_FLAG =>X_ACTIVE_FLAG,
473 X_PRIMARY_FLAG =>X_PRIMARY_FLAG,
474 X_INDEX_REQUIRED_FLAG =>X_INDEX_REQUIRED_FLAG,
475 X_TRANSFORMATION_NAME =>X_TRANSFORMATION_NAME,
476 X_DESCRIPTION =>X_DESCRIPTION,
477 X_LAST_UPDATE_DATE => sysdate,
478 X_LAST_UPDATED_BY => user_id,
479 X_LAST_UPDATE_LOGIN => 0,
480 X_OBJECT_VERSION_NUMBER => L_OBJECT_VERSION_NUMBER);
481
482 exception
483 when NO_DATA_FOUND then
484
485 HZ_TRANS_FUNCTIONS_PKG.INSERT_ROW(
486 X_FUNCTION_ID =>L_FUNCTION_ID,
487 X_STAGED_ATTRIBUTE_TABLE =>X_STAGED_ATTRIBUTE_TABLE,
488 X_STAGED_ATTRIBUTE_COLUMN =>X_STAGED_ATTRIBUTE_COLUMN,
489 X_STAGED_FLAG =>X_STAGED_FLAG,
490 X_ATTRIBUTE_ID =>X_ATTRIBUTE_ID,
491 X_PROCEDURE_NAME =>X_PROCEDURE_NAME,
492 X_ACTIVE_FLAG =>X_ACTIVE_FLAG,
493 X_PRIMARY_FLAG =>X_PRIMARY_FLAG,
494 X_INDEX_REQUIRED_FLAG =>X_INDEX_REQUIRED_FLAG,
495 X_TRANSFORMATION_NAME =>X_TRANSFORMATION_NAME,
496 X_DESCRIPTION =>X_DESCRIPTION,
497 X_CREATION_DATE=>SYSDATE ,
498 X_CREATED_BY =>USER_ID,
499 X_LAST_UPDATE_DATE => sysdate,
500 X_LAST_UPDATED_BY => user_id,
501 X_LAST_UPDATE_LOGIN => 0,
502 X_OBJECT_VERSION_NUMBER => 1);
503
504 end;
505 end LOAD_ROW;
506
507 procedure TRANSLATE_ROW (
508 X_FUNCTION_ID in NUMBER,
509 X_TRANSFORMATION_NAME in varchar2,
510 X_DESCRIPTION in varchar2,
511 X_OWNER in VARCHAR2) IS
512
513 begin
514 -- only update rows that have not been altered by user
515 update HZ_TRANS_FUNCTIONS_TL set
516 TRANSFORMATION_NAME = X_TRANSFORMATION_NAME,
517 DESCRIPTION = X_DESCRIPTION,
518 source_lang = userenv('LANG'),
519 last_update_date = sysdate,
520 last_updated_by = decode(X_OWNER, 'SEED', 1, 0),
521 last_update_login = 0
522 where FUNCTION_ID = X_FUNCTION_ID
523 and userenv('LANG') in (language, source_lang);
524
525 end TRANSLATE_ROW;
526
527 FUNCTION get_valid_tx_column(x_staged_attribute_table VARCHAR2,
528 x_staged_attribute_column VARCHAR2)
529 return varchar2
530 IS
531 CURSOR c_stg_attr_col IS select substr(staged_attribute_column,3)-1 from hz_trans_functions_vl vl1
532 where staged_attribute_table = x_staged_attribute_table
533 and substr(staged_attribute_column,3) > 2
534 and NOT EXISTS(
535 select 'Y'from hz_trans_functions_vl vl2
536 where vl2.staged_attribute_table=vl1.staged_attribute_table
537 and substr(vl2.staged_attribute_column,3) = substr(vl1.staged_attribute_column,3)-1
538 )
539 and rownum=1 ;
540 CURSOR c_max_stg_col IS select max(to_number(substr(staged_attribute_column,3)))+1 from hz_trans_functions_vl
541 where staged_attribute_table = X_STAGED_ATTRIBUTE_TABLE;
542 l_staged_attribute_column VARCHAR2(255);
543 l_prefix VARCHAR2(2);
544 BEGIN
545 l_prefix := 'TX';
546 IF( (x_staged_attribute_column IS NULL) OR (substr(x_staged_attribute_column,3) > 255)) THEN
547 IF(x_staged_attribute_column IS NOT NULL) THEN
548 OPEN c_stg_attr_col;
549 FETCH c_stg_attr_col INTO l_staged_attribute_column;
550 CLOSE c_stg_attr_col;
551 END IF;
552 IF l_staged_attribute_column is null then
553 open c_max_stg_col;
554 fetch c_max_stg_col INTO l_staged_attribute_column;
555 close c_max_stg_col;
556 END IF;
557 IF( nvl(l_staged_attribute_column,256) > 255)THEN
558 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_TRANSFORMATION_LIMIT');
559 FND_MSG_PUB.ADD;
560 RAISE FND_API.G_EXC_ERROR;
561 ELSE
562 l_staged_attribute_column := l_prefix || l_staged_attribute_column;
563 END IF;
564 ELSE
565 l_staged_attribute_column := X_STAGED_ATTRIBUTE_COLUMN;
566 END IF;
567 RETURN l_staged_attribute_column;
568 END;
569 end HZ_TRANS_FUNCTIONS_PKG;