[Home] [Help]
PACKAGE BODY: APPS.ITA_SETUP_REC_VALUES_PKG
Source
1 package body ITA_SETUP_REC_VALUES_PKG as
2 /* $Header: itatrevb.pls 120.12.12000000.2 2007/10/09 11:06:58 shelango ship $ */
3 --
4 -- ************************************************************************/
5 -- ** PROCEDURE - custom_debug takes message as a parameter and the message can */
6 -- ** be inserted into any custome table */
7 -- ************************************************************************/
8 --
9
10
11 procedure custom_debug(X_DEBUG_MSG in VARCHAR2) IS
12 L_DUMMY_VAR VARCHAR2(10);
13 begin
14
15 L_DUMMY_VAR := null;
16
17
18 -- insert into sam_test
19 -- (id,
20 -- message,
21 -- creation_date
22 -- )
23 -- values
24 -- (
25 -- sam_test_s1.nextval,
26 -- X_DEBUG_MSG,
27 -- sysdate
28 -- );
29
30 --commit;
31
32
33 end custom_debug;
34
35 --
36 -- ************************************************************************/
37 -- ** PROCEDURE - getContextInfo It returns the context_id depending upon */
38 -- ** the org context name */
39 -- ************************************************************************/
40 --
41
42 procedure getContextInfo(
43 X_CONTEXT_ID OUT NOCOPY NUMBER,
44 P_CONTEXT_NAME IN VARCHAR2,
45 P_SETUP_GROUP_NAME IN VARCHAR2
46 )
47 IS
48 l_setup_group_substr varchar2(10);
49 ERR_MSG VARCHAR2(100);
50 ERR_CDE NUMBER;
51
52 Begin
53
54 IF P_CONTEXT_NAME IS NOT NULL THEN
55 BEGIN
56 l_setup_group_substr := SUBSTR(P_SETUP_GROUP_NAME, 1, 5);
57 custom_debug('SAM :: In side the getContextInfo ');
58 custom_debug('SAM :: l_setup_group_substr ' || l_setup_group_substr);
59
60 IF (l_setup_group_substr = 'SQLGL') THEN
61 begin
62 custom_debug('SAM :: l_setup_group_substr SQLGL ');
63 select distinct
64 SET_OF_BOOKS_ID into X_CONTEXT_ID
65 from GL_SETS_OF_BOOKS
66 where NAME = nvl(P_CONTEXT_NAME,999);
67 EXCEPTION
68 WHEN NO_DATA_FOUND THEN
69 X_CONTEXT_ID := null;
70 end;
71 ELSE
72 begin
73 custom_debug('SAM :: l_setup_group_substr NOT SQLGL ');
74 select distinct
75 org.ORGANIZATION_ID into X_CONTEXT_ID
76 from
77 HR_ALL_ORGANIZATION_UNITS org
78 where
79 org.NAME = nvl(P_CONTEXT_NAME,999);
80 EXCEPTION
81 WHEN NO_DATA_FOUND THEN
82 X_CONTEXT_ID := null;
83 end;
84 END IF;
85 custom_debug('SAM :: X_CONTEXT_ID ' || X_CONTEXT_ID);
86
87 END;
88 END IF;
89
90 End getContextInfo;
91
92 --
93 -- ************************************************************************/
94 -- ** PROCEDURE - GetParameterCode It returns the parametere code for the */
95 -- ** parameter name depending on the parameter code */
96 -- ************************************************************************/
97 --
98
99 procedure GetParameterCode (p_parameter_name IN VARCHAR2,
100 p_setup_group_code IN VARCHAR2,
101 X_PARAMETER_CODE OUT NOCOPY VARCHAR2)
102 IS
103 g_parameter_code VARCHAR2(111) := NULL;
104 ERR_MSG VARCHAR2(100);
105 ERR_CDE NUMBER;
106 BEGIN
107
108 IF p_parameter_name IS NOT NULL THEN
109 BEGIN
110 custom_debug('g_parameter_code p_parameter_name :: ' || p_parameter_name);
111
112 SELECT parameter_code
113 INTO X_PARAMETER_CODE
114 FROM ita_setup_parameters_vl
115 WHERE parameter_name = p_parameter_name and
116 setup_group_code = p_setup_group_code;
117 EXCEPTION
118 WHEN OTHERS THEN
119 ERR_MSG := SUBSTR(SQLERRM,1,100);
120 ERR_CDE := SQLCODE;
121 custom_debug('In the exception ERR_MSG :: ' || ERR_MSG || ' :: ERR_CDE :: ' || ERR_CDE);
122 X_PARAMETER_CODE := NULL;
123 END;
124 END IF;
125 custom_debug('X_PARAMETER_CODE :: ' || X_PARAMETER_CODE);
126 END GetParameterCode;
127
128
129 procedure INSERT_ROW (
130 X_REC_VALUE_ID in NUMBER,
131 X_PARAMETER_CODE in VARCHAR2,
132 X_CONTEXT_ORG_ID in NUMBER,
133 X_CONTEXT_ORG_NAME in VARCHAR2,
134 X_RECOMMENDED_VALUE in VARCHAR2,
135 X_DEFAULT_FLAG in VARCHAR2,
136 X_CREATION_DATE in DATE,
137 X_CREATED_BY in NUMBER,
138 X_LAST_UPDATE_DATE in DATE,
139 X_LAST_UPDATED_BY in NUMBER,
140 X_LAST_UPDATE_LOGIN in NUMBER,
141 X_SECURITY_GROUP_ID in NUMBER,
142 X_OBJECT_VERSION_NUMBER in NUMBER,
143 X_PK1_VALUE in VARCHAR2,
144 X_PK2_VALUE in VARCHAR2,
145 X_REC_INTERFACE_ID in NUMBER
146 )
147
148 is
149 begin
150
151 custom_debug('In to the INSERT_ROW X_PARAMETER_CODE ' || X_PARAMETER_CODE);
152
153 insert into ITA_SETUP_REC_VALUES_B (
154 REC_VALUE_ID,
155 PARAMETER_CODE,
156 CONTEXT_ORG_ID,
157 CONTEXT_ORG_NAME,
158 DEFAULT_FLAG,
159 CREATION_DATE,
160 CREATED_BY,
161 LAST_UPDATE_DATE,
162 LAST_UPDATED_BY,
163 LAST_UPDATE_LOGIN,
164 SECURITY_GROUP_ID,
165 OBJECT_VERSION_NUMBER,
166 PK1_VALUE,
167 PK2_VALUE,
168 REC_INTERFACE_ID
169 ) values (
170 X_REC_VALUE_ID,
171 X_PARAMETER_CODE,
172 X_CONTEXT_ORG_ID,
173 X_CONTEXT_ORG_NAME,
174 X_DEFAULT_FLAG,
175 X_CREATION_DATE,
176 X_CREATED_BY,
177 X_LAST_UPDATE_DATE,
178 X_LAST_UPDATED_BY,
179 X_LAST_UPDATE_LOGIN,
180 X_SECURITY_GROUP_ID,
181 X_OBJECT_VERSION_NUMBER,
182 X_PK1_VALUE,
183 X_PK2_VALUE,
184 X_REC_INTERFACE_ID
185 );
186
187 custom_debug('In to the INSERT_ROW callin insert in TL X_REC_VALUE_ID :: ' || X_REC_VALUE_ID);
188
189 insert into ITA_SETUP_REC_VALUES_TL (
190 REC_VALUE_ID,
191 PARAMETER_CODE,
192 CONTEXT_ORG_NAME,
193 RECOMMENDED_VALUE,
194 CREATION_DATE,
195 CREATED_BY,
196 LAST_UPDATE_DATE,
197 LAST_UPDATED_BY,
198 LAST_UPDATE_LOGIN,
199 SECURITY_GROUP_ID,
200 OBJECT_VERSION_NUMBER,
201 LANGUAGE,
202 SOURCE_LANG
203 ) select
204 X_REC_VALUE_ID,
205 X_PARAMETER_CODE,
206 X_CONTEXT_ORG_NAME,
207 X_RECOMMENDED_VALUE,
208 X_CREATION_DATE,
209 X_CREATED_BY,
210 X_LAST_UPDATE_DATE,
211 X_LAST_UPDATED_BY,
212 X_LAST_UPDATE_LOGIN,
213 X_SECURITY_GROUP_ID,
214 X_OBJECT_VERSION_NUMBER,
215 L.LANGUAGE_CODE,
216 userenv('LANG')
217 from FND_LANGUAGES L
218 where L.INSTALLED_FLAG in ('I', 'B')
219 and not exists (
220 select null
221 from ITA_SETUP_REC_VALUES_TL tl
222 where
223 (tl.REC_VALUE_ID = X_REC_VALUE_ID or
224 (PARAMETER_CODE = X_PARAMETER_CODE and CONTEXT_ORG_NAME = X_CONTEXT_ORG_NAME)) and
225 tl.LANGUAGE = L.LANGUAGE_CODE);
226
227
228 end INSERT_ROW;
229
230
231 procedure UPDATE_ROW (
232 X_REC_VALUE_ID in NUMBER,
233 X_PARAMETER_CODE in VARCHAR2,
234 X_CONTEXT_ORG_ID in NUMBER,
235 X_CONTEXT_ORG_NAME in VARCHAR2,
236 X_RECOMMENDED_VALUE in VARCHAR2,
237 X_DEFAULT_FLAG in VARCHAR2,
238 X_LAST_UPDATE_DATE in DATE,
239 X_LAST_UPDATED_BY in NUMBER,
240 X_LAST_UPDATE_LOGIN in NUMBER,
241 X_SECURITY_GROUP_ID in NUMBER,
242 X_OBJECT_VERSION_NUMBER in NUMBER,
243 X_PK1_VALUE in VARCHAR2,
244 X_PK2_VALUE in VARCHAR2,
245 X_REC_INTERFACE_ID NUMBER
246 ) is
247 begin
248 update ITA_SETUP_REC_VALUES_B set
249 REC_VALUE_ID = X_REC_VALUE_ID,
250 PARAMETER_CODE = X_PARAMETER_CODE,
251 CONTEXT_ORG_ID = X_CONTEXT_ORG_ID,
252 CONTEXT_ORG_NAME = X_CONTEXT_ORG_NAME,
253 DEFAULT_FLAG = X_DEFAULT_FLAG,
254 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
255 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
256 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
257 SECURITY_GROUP_ID = X_SECURITY_GROUP_ID,
258 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
259 PK1_VALUE = X_PK1_VALUE,
260 PK2_VALUE = X_PK2_VALUE,
261 REC_INTERFACE_ID = X_REC_INTERFACE_ID
262 where REC_VALUE_ID = X_REC_VALUE_ID or
263 (PARAMETER_CODE = X_PARAMETER_CODE and CONTEXT_ORG_NAME = X_CONTEXT_ORG_NAME);
264
265 if (sql%notfound) then
266 raise no_data_found;
267 end if;
268
269 update ITA_SETUP_REC_VALUES_TL set
270 REC_VALUE_ID = X_REC_VALUE_ID,
271 PARAMETER_CODE = X_PARAMETER_CODE,
272 CONTEXT_ORG_NAME = X_CONTEXT_ORG_NAME,
273 RECOMMENDED_VALUE = X_RECOMMENDED_VALUE,
274 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
275 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
276 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
277 SECURITY_GROUP_ID = X_SECURITY_GROUP_ID,
278 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
279 SOURCE_LANG = userenv('LANG')
280 where
281 (REC_VALUE_ID = X_REC_VALUE_ID or
282 (PARAMETER_CODE = X_PARAMETER_CODE and CONTEXT_ORG_NAME = X_CONTEXT_ORG_NAME)) and
283 userenv('LANG') in (LANGUAGE, SOURCE_LANG);
284
285 if (sql%notfound) then
286 raise no_data_found;
287 end if;
288 end UPDATE_ROW;
289
290
291 procedure LOAD_ROW (
292 X_REC_VALUE_ID in NUMBER,
293 X_PARAMETER_CODE in VARCHAR2,
294 X_CONTEXT_ORG_ID in NUMBER,
295 X_CONTEXT_ORG_NAME in VARCHAR2,
296 X_RECOMMENDED_VALUE in VARCHAR2,
297 X_DEFAULT_FLAG in VARCHAR2,
298 X_LAST_UPDATE_DATE in VARCHAR2,
299 X_PK1_VALUE in VARCHAR2,
300 X_PK2_VALUE in VARCHAR2,
301 X_REC_INTERFACE_ID NUMBER,
302 X_OWNER in VARCHAR2,
303 X_CUSTOM_MODE in VARCHAR2
304 ) is
305
306 f_luby number; -- entity owner in file
307 f_ludate date; -- entity update date in file
308 db_luby number; -- entity owner in db
309 db_ludate date; -- entity update date in db
310
311 begin
312 -- Translate owner to file_last_updated_by
313 f_luby := fnd_load_util.owner_id(X_OWNER);
314
315 -- Translate char last_update_date to date
316 f_ludate := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
317
318 select LAST_UPDATED_BY, LAST_UPDATE_DATE into db_luby, db_ludate
319 from ITA_SETUP_REC_VALUES_B
320 where REC_VALUE_ID = X_REC_VALUE_ID or
321 (PARAMETER_CODE = X_PARAMETER_CODE and CONTEXT_ORG_NAME = X_CONTEXT_ORG_NAME);
322
323 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby, db_ludate, X_CUSTOM_MODE))
324 then ITA_SETUP_REC_VALUES_PKG.UPDATE_ROW (
325 X_REC_VALUE_ID => X_REC_VALUE_ID,
326 X_PARAMETER_CODE => X_PARAMETER_CODE,
327 X_CONTEXT_ORG_ID => X_CONTEXT_ORG_ID,
328 X_CONTEXT_ORG_NAME => X_CONTEXT_ORG_NAME,
329 X_RECOMMENDED_VALUE => X_RECOMMENDED_VALUE,
330 X_DEFAULT_FLAG => X_DEFAULT_FLAG,
331 X_LAST_UPDATE_DATE => f_ludate,
332 X_LAST_UPDATED_BY => f_luby,
333 X_LAST_UPDATE_LOGIN => 0,
334 X_SECURITY_GROUP_ID => null,
335 X_OBJECT_VERSION_NUMBER => 1,
336 X_PK1_VALUE => X_PK1_VALUE,
337 X_PK2_VALUE => X_PK2_VALUE,
338 X_REC_INTERFACE_ID => X_REC_INTERFACE_ID);
339 end if;
340 exception when NO_DATA_FOUND
341 then ITA_SETUP_REC_VALUES_PKG.INSERT_ROW (
342 X_REC_VALUE_ID => X_REC_VALUE_ID,
343 X_PARAMETER_CODE => X_PARAMETER_CODE,
344 X_CONTEXT_ORG_ID => X_CONTEXT_ORG_ID,
345 X_CONTEXT_ORG_NAME => X_CONTEXT_ORG_NAME,
346 X_RECOMMENDED_VALUE => X_RECOMMENDED_VALUE,
347 X_DEFAULT_FLAG => X_DEFAULT_FLAG,
348 X_CREATION_DATE => f_ludate,
349 X_CREATED_BY => f_luby,
350 X_LAST_UPDATE_DATE => f_ludate,
351 X_LAST_UPDATED_BY => f_luby,
352 X_LAST_UPDATE_LOGIN => 0,
353 X_SECURITY_GROUP_ID => null,
354 X_OBJECT_VERSION_NUMBER => 1,
355 X_PK1_VALUE => X_PK1_VALUE,
356 X_PK2_VALUE => X_PK2_VALUE,
357 X_REC_INTERFACE_ID => X_REC_INTERFACE_ID);
358 end LOAD_ROW;
359
360
361 procedure LOAD_ROW_FOR_IMPORT (
362 X_REC_VALUE_ID in NUMBER,
363 X_PARAMETER_CODE in VARCHAR2,
364 X_CONTEXT_ORG_ID in NUMBER,
365 X_CONTEXT_ORG_NAME in VARCHAR2,
366 X_RECOMMENDED_VALUE in VARCHAR2,
367 X_DEFAULT_FLAG in VARCHAR2,
368 X_CREATION_DATE in DATE,
369 X_CREATED_BY in NUMBER,
370 X_LAST_UPDATE_DATE in DATE,
371 X_LAST_UPDATED_BY in NUMBER,
372 X_LAST_UPDATE_LOGIN in NUMBER,
373 X_SECURITY_GROUP_ID in NUMBER,
374 X_OBJECT_VERSION_NUMBER in NUMBER,
375 X_PK1_VALUE in VARCHAR2,
376 X_PK2_VALUE in VARCHAR2,
377 X_REC_INTERFACE_ID NUMBER
378 ) is
379
380 db_rec_value_id number;
381 db_luby number;
382 db_ludate date;
383
384 begin
385 select REC_VALUE_ID, LAST_UPDATED_BY, LAST_UPDATE_DATE into db_rec_value_id, db_luby, db_ludate
386 from ITA_SETUP_REC_VALUES_B
387 where REC_VALUE_ID = X_REC_VALUE_ID or
388 (PARAMETER_CODE = X_PARAMETER_CODE and CONTEXT_ORG_NAME = X_CONTEXT_ORG_NAME);
389
390 if (db_luby is not null and db_ludate is not null)
391 then ITA_SETUP_REC_VALUES_PKG.UPDATE_ROW (
392 X_REC_VALUE_ID => db_rec_value_id,
393 X_PARAMETER_CODE => X_PARAMETER_CODE,
394 X_CONTEXT_ORG_ID => X_CONTEXT_ORG_ID,
395 X_CONTEXT_ORG_NAME => X_CONTEXT_ORG_NAME,
396 X_RECOMMENDED_VALUE => X_RECOMMENDED_VALUE,
397 X_DEFAULT_FLAG => X_DEFAULT_FLAG,
398 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
399 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
400 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN,
401 X_SECURITY_GROUP_ID => X_SECURITY_GROUP_ID,
402 X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER,
403 X_PK1_VALUE => X_PK1_VALUE,
404 X_PK2_VALUE => X_PK2_VALUE,
405 X_REC_INTERFACE_ID => X_REC_INTERFACE_ID);
406 end if;
407 exception when NO_DATA_FOUND
408 then ITA_SETUP_REC_VALUES_PKG.INSERT_ROW (
409 X_REC_VALUE_ID => X_REC_VALUE_ID,
410 X_PARAMETER_CODE => X_PARAMETER_CODE,
411 X_CONTEXT_ORG_ID => X_CONTEXT_ORG_ID,
412 X_CONTEXT_ORG_NAME => X_CONTEXT_ORG_NAME,
413 X_RECOMMENDED_VALUE => X_RECOMMENDED_VALUE,
414 X_DEFAULT_FLAG => X_DEFAULT_FLAG,
415 X_CREATION_DATE => X_CREATION_DATE,
416 X_CREATED_BY => X_CREATED_BY,
417 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
418 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
419 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN,
420 X_SECURITY_GROUP_ID => X_SECURITY_GROUP_ID,
421 X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER,
422 X_PK1_VALUE => X_PK1_VALUE,
423 X_PK2_VALUE => X_PK2_VALUE,
424 X_REC_INTERFACE_ID => X_REC_INTERFACE_ID);
425 end LOAD_ROW_FOR_IMPORT;
426
427
428 procedure ADD_LANGUAGE
429 is
430 begin
431 delete from ITA_SETUP_REC_VALUES_TL tl
432 where not exists (
433 select null
434 from ITA_SETUP_REC_VALUES_B b
435 where
436 b.PARAMETER_CODE = tl.PARAMETER_CODE and
437 b.CONTEXT_ORG_NAME = tl.CONTEXT_ORG_NAME
438 );
439
440 update ITA_SETUP_REC_VALUES_TL tl set (
441 RECOMMENDED_VALUE
442 ) = (select
443 b.RECOMMENDED_VALUE
444 from ITA_SETUP_REC_VALUES_TL b
445 where
446 b.PARAMETER_CODE = tl.PARAMETER_CODE and
447 b.CONTEXT_ORG_NAME = tl.CONTEXT_ORG_NAME and
448 b.LANGUAGE = tl.SOURCE_LANG)
449 where (
450 tl.PARAMETER_CODE,
451 tl.CONTEXT_ORG_NAME,
452 tl.LANGUAGE
453 ) in (select
454 subtl.PARAMETER_CODE,
455 subtl.CONTEXT_ORG_NAME,
456 subtl.LANGUAGE
457 from ITA_SETUP_REC_VALUES_TL subb, ITA_SETUP_REC_VALUES_TL subtl
458 where
459 subb.PARAMETER_CODE = subtl.PARAMETER_CODE and
460 subb.CONTEXT_ORG_NAME = subtl.CONTEXT_ORG_NAME and
461 subb.LANGUAGE = subtl.SOURCE_LANG and
462 (subb.RECOMMENDED_VALUE <> subtl.RECOMMENDED_VALUE or
463 (subb.RECOMMENDED_VALUE is null and subtl.RECOMMENDED_VALUE is not null) or
464 (subb.RECOMMENDED_VALUE is not null and subtl.RECOMMENDED_VALUE is null)));
465
466 insert into ITA_SETUP_REC_VALUES_TL (
467 PARAMETER_CODE,
468 CONTEXT_ORG_ID,
469 CONTEXT_ORG_NAME,
470 RECOMMENDED_VALUE,
471 CREATED_BY,
472 CREATION_DATE,
473 LAST_UPDATED_BY,
474 LAST_UPDATE_DATE,
475 LAST_UPDATE_LOGIN,
476 SECURITY_GROUP_ID,
477 OBJECT_VERSION_NUMBER,
478 LANGUAGE,
479 SOURCE_LANG,
480 REC_VALUE_ID,
481 REQUEST_ID,
482 PROGRAM_APPLICATION_ID,
483 PROGRAM_ID,
484 PROGRAM_UPDATE_DATE
485 ) select
486 b.PARAMETER_CODE,
487 b.CONTEXT_ORG_ID,
488 b.CONTEXT_ORG_NAME,
489 b.RECOMMENDED_VALUE,
490 b.CREATED_BY,
491 b.CREATION_DATE,
492 b.LAST_UPDATED_BY,
493 b.LAST_UPDATE_DATE,
494 b.LAST_UPDATE_LOGIN,
495 b.SECURITY_GROUP_ID,
496 b.OBJECT_VERSION_NUMBER,
497 L.LANGUAGE_CODE,
498 b.SOURCE_LANG,
499 b.REC_VALUE_ID,
500 b.REQUEST_ID,
501 b.PROGRAM_APPLICATION_ID,
502 b.PROGRAM_ID,
503 b.PROGRAM_UPDATE_DATE
504 from ITA_SETUP_REC_VALUES_TL b, FND_LANGUAGES L
505 where
506 L.INSTALLED_FLAG in ('I', 'B') and
507 b.LANGUAGE = userenv('LANG') and
508 not exists (
509 select null
510 from ITA_SETUP_REC_VALUES_TL tl
511 where
512 tl.PARAMETER_CODE = b.PARAMETER_CODE and
513 tl.CONTEXT_ORG_NAME = b.CONTEXT_ORG_NAME and
514 tl.LANGUAGE = L.LANGUAGE_CODE);
515 end ADD_LANGUAGE;
516
517
518 procedure IMPORT (
519 BATCH_ID in NUMBER,
520 CREATED_BY in NUMBER
521 ) is
522
523 m_rec_value_id NUMBER;
524 m_current_date DATE;
525 m_org_name VARCHAR2(240);
526 m_org_id NUMBER;
527 m_parameter_code VARCHAR2(111);
528 m_setup_group_code VARCHAR2(81);
529 m_default_flag VARCHAR2(1);
530 m_rec_interface_id NUMBER;
531 --interface_row GET_INTERFACE_ROWS%ROWTYPE;
532 m_error_msg VARCHAR2(3000);
533
534 cursor GET_INTERFACE_ROWS (
535 X_BATCH_ID in NUMBER,
536 X_CREATED_BY in NUMBER
537 ) is
538 SELECT
539 distinct DEFAULT_FLAG,
540 PARAMETER_CODE,
541 SETUP_GROUP_CODE,
542 PK1_VALUE,
543 PK2_VALUE,
544 REC_VALUE,
545 CREATED_BY,
546 LAST_UPDATED_BY,
547 LAST_UPDATE_LOGIN,
548 SECURITY_GROUP_ID ,
549 OBJECT_VERSION_NUMBER,
550 REC_INTERFACE_ID
551 FROM
552 ITA_SETUP_REC_INTF
553 WHERE
554 BATCH_ID = X_BATCH_ID and
555 CREATED_BY = X_CREATED_BY and
556 IMPORTED_FLAG <> 'Y';
557 begin
558
559 for interface_row in GET_INTERFACE_ROWS(BATCH_ID, CREATED_BY) loop
560
561 m_rec_interface_id := interface_row.REC_INTERFACE_ID;
562 m_error_msg := null;
563
564 select ITA_SETUP_REC_VALUES_S1.nextval into m_rec_value_id from dual;
565 select sysdate into m_current_date from dual;
566
567
568
569 if (interface_row.PARAMETER_CODE IS NOT NULL) then
570 GetParameterCode (p_parameter_name => interface_row.PARAMETER_CODE,
571 p_setup_group_code => interface_row.SETUP_GROUP_CODE,
572 X_PARAMETER_CODE => m_parameter_code);
573
574 end if;
575
576 custom_debug('In to the INSERT_ROW m_parameter_code ' || m_parameter_code);
577
578 if (m_parameter_code IS NOT NULL) then
579
580
581 if (interface_row.SETUP_GROUP_CODE = 'FND.FND_PROFILE_OPTION_VALUES') then
582 BEGIN
583
584 LOAD_ROW_FOR_IMPORT (
585 X_REC_VALUE_ID => m_rec_value_id,
586 X_PARAMETER_CODE => m_parameter_code, --interface_row.PARAMETER_CODE,
587 X_CONTEXT_ORG_ID => null,
588 X_CONTEXT_ORG_NAME => null,
589 X_RECOMMENDED_VALUE => interface_row.REC_VALUE,
590 X_DEFAULT_FLAG => 'Y',
591 X_CREATION_DATE => m_current_date,
592 X_CREATED_BY => interface_row.CREATED_BY,
593 X_LAST_UPDATE_DATE => m_current_date,
594 X_LAST_UPDATED_BY => interface_row.LAST_UPDATED_BY,
595 X_LAST_UPDATE_LOGIN => interface_row.LAST_UPDATE_LOGIN,
596 X_SECURITY_GROUP_ID => interface_row.SECURITY_GROUP_ID,
597 X_OBJECT_VERSION_NUMBER => interface_row.OBJECT_VERSION_NUMBER,
598 X_PK1_VALUE => interface_row.PK1_VALUE,
599 X_PK2_VALUE => interface_row.PK2_VALUE,
600 X_REC_INTERFACE_ID => interface_row.REC_INTERFACE_ID
601 );
602
603
604 END;
605 elsif (interface_row.PK1_VALUE IS NOT NULL) then
606 BEGIN
607 getContextInfo(X_CONTEXT_ID => m_org_id,
608 P_CONTEXT_NAME => interface_row.PK1_VALUE,
609 P_SETUP_GROUP_NAME => interface_row.SETUP_GROUP_CODE);
610
611 m_default_flag := interface_row.DEFAULT_FLAG;
612 if (m_default_flag is null) then m_default_flag := 'N'; end if;
613
614 LOAD_ROW_FOR_IMPORT (
615 X_REC_VALUE_ID => m_rec_value_id,
616 X_PARAMETER_CODE => m_parameter_code, --interface_row.PARAMETER_CODE,
617 X_CONTEXT_ORG_ID => m_org_id, --interface_row.PK1_VALUE,
618 X_CONTEXT_ORG_NAME => interface_row.PK1_VALUE,-- m_org_name,
619 X_RECOMMENDED_VALUE => interface_row.REC_VALUE,
620 X_DEFAULT_FLAG => m_default_flag,
621 X_CREATION_DATE => m_current_date,
622 X_CREATED_BY => interface_row.CREATED_BY,
623 X_LAST_UPDATE_DATE => m_current_date,
624 X_LAST_UPDATED_BY => interface_row.LAST_UPDATED_BY,
625 X_LAST_UPDATE_LOGIN => interface_row.LAST_UPDATE_LOGIN,
626 X_SECURITY_GROUP_ID => interface_row.SECURITY_GROUP_ID,
627 X_OBJECT_VERSION_NUMBER => interface_row.OBJECT_VERSION_NUMBER,
628 X_PK1_VALUE => m_org_id,
629 X_PK2_VALUE => interface_row.PK2_VALUE,
630 X_REC_INTERFACE_ID => interface_row.REC_INTERFACE_ID
631 );
632
633 custom_debug('interface_row.PK1_VALUE IS NOT NULL');
634 END;
635 ELSE --ELSIF (interface_row.DEFAULT_FLAG ='Y') then
636 BEGIN
637
638 LOAD_ROW_FOR_IMPORT (
639 X_REC_VALUE_ID => m_rec_value_id,
640 X_PARAMETER_CODE => m_parameter_code, --interface_row.PARAMETER_CODE,
641 X_CONTEXT_ORG_ID => -1,
642 X_CONTEXT_ORG_NAME => '*',
643 X_RECOMMENDED_VALUE => interface_row.REC_VALUE,
644 X_DEFAULT_FLAG => 'Y',
645 X_CREATION_DATE => m_current_date,
646 X_CREATED_BY => interface_row.CREATED_BY,
647 X_LAST_UPDATE_DATE => m_current_date,
648 X_LAST_UPDATED_BY => interface_row.LAST_UPDATED_BY,
649 X_LAST_UPDATE_LOGIN => interface_row.LAST_UPDATE_LOGIN,
650 X_SECURITY_GROUP_ID => interface_row.SECURITY_GROUP_ID,
651 X_OBJECT_VERSION_NUMBER => interface_row.OBJECT_VERSION_NUMBER,
652 X_PK1_VALUE => -1,
653 X_PK2_VALUE => interface_row.PK2_VALUE,
654 X_REC_INTERFACE_ID => interface_row.REC_INTERFACE_ID
655 );
656
657 END;
658 end if;
659
660
661 --update ITA_SETUP_REC_INTF
662 --set IMPORTED_FLAG = 'Y'
663 --where REC_INTERFACE_ID = interface_row.REC_INTERFACE_ID;
664
665 if m_error_msg is null then
666 delete from ITA_SETUP_REC_INTF
667 where REC_INTERFACE_ID = interface_row.REC_INTERFACE_ID;
668 end if;
669
670 end if;
671
672
673 end loop;
674
675
676 EXCEPTION
677 WHEN OTHERS THEN
678 m_error_msg := substr(SQLERRM, 1, 3000);
679 update ITA_SETUP_REC_INTF
680 set STATUS_TXT = m_error_msg
681 where REC_INTERFACE_ID = m_rec_interface_id;
682
683
684 end IMPORT;
685
686
687 -- *****************************************
688 -- FUNCTION
689 -- getRecValueCode0
690 -- Input Parameters
691 -- context_org_id
692 -- parameter_code
693 -- Return Values
694 -- varchar2 recommended_value for
695 -- the org and parameter code
696 -- *****************************************
697 FUNCTION getRecValueCode0(
698 p_context_org_id IN VARCHAR2,
699 p_parameter_code IN VARCHAR2
700 )
701 return VARCHAR2 IS
702 l_rec_value_code VARCHAR2(3000);
703 BEGIN
704 l_rec_value_code := null;
705
706 select recommended_value
707 into l_rec_value_code
708 from ita_setup_rec_values_vl
709 where parameter_code = p_parameter_code
710 and context_org_id = to_number(p_context_org_id);
711
712 return l_rec_value_code;
713
714 EXCEPTION
715 when no_data_found then
716 select max(recommended_value)
717 into l_rec_value_code
718 from ita_setup_rec_values_vl
719 where parameter_code = p_parameter_code
720 and default_flag = 'Y';
721
722 return l_rec_value_code;
723
724 when others then
725 return null;
726
727 END getRecValueCode0;
728
729 -- hyuen start bug 5395104
730 -- *****************************************
731 -- FUNCTION
732 -- getRecValueCodeFromOrg
733 -- Input Parameters
734 -- context_org_id
735 -- parameter_code
736 -- overrideLevel
737 -- Return Values
738 -- varchar2 recommended_value for
739 -- the org parameter code
740 -- *****************************************
741 FUNCTION getRecValueCodeFromOrg(p_context_org_id IN VARCHAR2, p_parameter_code IN VARCHAR2, p_overridelevel IN INT) RETURN VARCHAR2 IS l_rec_value_code VARCHAR2(3000);
742 l_paramcodeorg VARCHAR2(3000);
743 BEGIN
744 l_rec_value_code := NULL;
745 l_paramcodeorg := NULL;
746
747 SELECT parameter_code
748 INTO l_paramcodeorg
749 FROM ita_parameter_hierarchy
750 WHERE override_parameter_code = p_parameter_code
751 AND override_level = p_overridelevel;
752
753 l_rec_value_code := getrecvaluecode0(p_context_org_id, l_paramcodeorg);
754
755 RETURN l_rec_value_code;
756 END getRecValueCodeFromOrg;
757
758 -- *****************************************
759 -- FUNCTION
760 -- getRecValueCodeFromSuppliers
761 -- Input Parameters
762 -- context_org_id
763 -- parameter_code
764 -- Return Values
765 -- varchar2 recommended_value for
766 -- the Suppliers parameter code
767 -- *****************************************
768 FUNCTION getRecValueCodeFromSuppliers(p_context_org_id IN VARCHAR2, p_parameter_code IN VARCHAR2) RETURN VARCHAR2 IS l_rec_value_code VARCHAR2(3000);
769 l_paramcodesupps VARCHAR2(3000);
770 BEGIN
771 l_rec_value_code := NULL;
772 l_paramcodesupps := NULL;
773
774 SELECT override_parameter_code
775 INTO l_paramcodesupps
776 FROM ita_parameter_hierarchy
777 WHERE parameter_code =
778 (SELECT parameter_code
779 FROM ita_parameter_hierarchy
780 WHERE override_parameter_code = p_parameter_code
781 AND override_level = 2)
782 AND override_level = 1;
783
784 l_rec_value_code := getrecvaluecode0(p_context_org_id, l_paramcodesupps);
785
786 RETURN l_rec_value_code;
787 END getRecValueCodeFromSuppliers;
788 -- hyuen end bug 8395104
789
790 -- *****************************************
791 -- FUNCTION
792 -- getRecValueCode
793 -- Input Parameters
794 -- context_org_id
795 -- parameter_code
796 -- Return Values
797 -- varchar2 recommended_value for
798 -- the org and parameter code
799 -- *****************************************
800 FUNCTION getRecValueCode(
801 p_context_org_id IN VARCHAR2,
802 p_parameter_code IN VARCHAR2
803 )
804 return VARCHAR2 IS
805 l_rec_value_code VARCHAR2(3000);
806 BEGIN
807 l_rec_value_code := null;
808
809 -- hyuen start bug 5395104
810 l_rec_value_code := getRecValueCode0(p_context_org_id,p_parameter_code);
811
812 IF(l_rec_value_code IS NULL) THEN
813 BEGIN
814 IF(SUBSTR(p_parameter_code, 1, 18) = 'SQLAP.AP_SUPPLIERS') THEN
815 l_rec_value_code := getrecvaluecodefromorg(p_context_org_id, p_parameter_code, 1);
816 ELSIF SUBSTR(p_parameter_code, 1, 27) = 'SQLAP.AP_SUPPLIER_SITES_ALL' THEN
817 BEGIN
818 l_rec_value_code := getrecvaluecodefromsuppliers(p_context_org_id, p_parameter_code);
819 IF l_rec_value_code IS NULL THEN
820 l_rec_value_code := getrecvaluecodefromorg(p_context_org_id, p_parameter_code, 2);
821 END IF;
822 END;
823 END IF;
824
825 END;
826 END IF;
827
828 return l_rec_value_code;
829 -- hyuen end bug 5395104
830 END getRecValueCode;
831
832 -- *****************************************
833 -- FUNCTION
834 -- getRecValueMeaning
835 -- Input Parameters
836 -- context_org_id
837 -- parameter_code
838 -- Return Values
839 -- varchar2 recommended_value meaning for
840 -- the org and parameter code
841 -- *****************************************
842 FUNCTION getRecValueMeaning(
843 p_context_org_id IN VARCHAR2,
844 p_parameter_code IN VARCHAR2
845 )
846 return VARCHAR2 IS
847 l_select_clause ITA_SETUP_PARAMETERS_B.SELECT_CLAUSE%TYPE;
848 l_from_clause ITA_SETUP_PARAMETERS_B.FROM_CLAUSE%TYPE;
849 l_where_clause ITA_SETUP_PARAMETERS_B.WHERE_CLAUSE%TYPE;
850 l_rec_value_meaning VARCHAR2(3000);
851 l_rec_value_code VARCHAR2(3000);
852 l_curr_sql VARCHAR2(32767);
853 BEGIN
854 l_rec_value_meaning := null;
855 l_rec_value_code := null;
856
857 l_rec_value_code := getRecValueCode(p_context_org_id,p_parameter_code);
858
859 --DBMS_OUTPUT.PUT_LINE('recValueCode: ' || l_rec_value_code);
860
861 if (l_rec_value_code is null) then
862 return null;
863 end if;
864
865 SELECT select_clause, from_clause, where_clause
866 INTO l_select_clause, l_from_clause, l_where_clause
867 FROM ita_setup_parameters_b
868 WHERE parameter_code = p_parameter_code;
869
870 --DBMS_OUTPUT.PUT_LINE('select clause: ' || l_select_clause);
871 --DBMS_OUTPUT.PUT_LINE('from clause: ' || l_from_clause);
872 --DBMS_OUTPUT.PUT_LINE('where clause: ' || l_where_clause);
873
874 IF l_select_clause IS NOT NULL THEN
875 l_select_clause := RTRIM(l_select_clause);
876
877 if l_select_clause is null then
878 --DBMS_OUTPUT.PUT_LINE('select is null: ' || l_rec_value_code);
879 return l_rec_value_code;
880 end if;
881
882 IF l_from_clause IS NOT NULL THEN
883 l_from_clause := RTRIM(l_from_clause);
884 END IF;
885
886 IF l_where_clause IS NOT NULL THEN
887 l_where_clause := RTRIM(l_where_clause);
888 END IF;
889
890 l_curr_sql := l_select_clause || ' ' || l_from_clause || ' ' || l_where_clause;
891
892 --DBMS_OUTPUT.PUT_LINE('SQL: ' || l_curr_sql);
893
894 -- hyuen start bug 5410296 handle Profile option
895 -- l_curr_sql := REPLACE(l_curr_sql,':1',l_rec_value_code);
896
897 -- EXECUTE IMMEDIATE l_curr_sql into l_rec_value_meaning;
898 IF(SUBSTR(p_parameter_code, 1, 29) = 'FND.FND_PROFILE_OPTION_VALUES') then
899 begin
900 l_rec_value_meaning := ITA_RECORD_CURR_STATUS_PKG.GET_PROFILE_VALUE_MEANING (l_curr_sql, l_rec_value_code);
901 end;
902 else
903 begin
904 l_curr_sql := REPLACE(l_curr_sql,':1',l_rec_value_code);
905 --DBMS_OUTPUT.PUT_LINE('Bind: ' || l_curr_sql);
906 EXECUTE IMMEDIATE l_curr_sql into l_rec_value_meaning;
907 end;
908 end if;
909
910 -- hyuen end bug 5410296 handle Profile option
911
912
913 END IF;
914
915 if (l_rec_value_meaning is null) then
916 return l_rec_value_code;
917 end if;
918
919 return l_rec_value_meaning;
920
921 EXCEPTION
922 when others then
923 return null;
924 END getRecValueMeaning;
925
926
927 end ITA_SETUP_REC_VALUES_PKG;