1 package body AK_LOOKUP_CODES_PKG as
2 /* $Header: AKDLKUPB.pls 120.3 2006/01/25 15:57:21 tshort ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_LOOKUP_CODE in VARCHAR2,
6 X_LOOKUP_TYPE in VARCHAR2,
7 X_DESCRIPTION in VARCHAR2,
8 X_CREATION_DATE in DATE,
9 X_CREATED_BY in NUMBER,
10 X_LAST_UPDATE_DATE in DATE,
11 X_LAST_UPDATED_BY in NUMBER,
12 X_LAST_UPDATE_LOGIN in NUMBER
13 ) is
14 cursor C is select ROWID from AK_LOOKUP_CODES
15 where LOOKUP_CODE = X_LOOKUP_CODE
16 and LOOKUP_TYPE = X_LOOKUP_TYPE
17 and LANGUAGE = userenv('LANG')
18 ;
19 begin
20 insert into AK_LOOKUP_CODES (
21 LOOKUP_CODE,
22 LOOKUP_TYPE,
23 DESCRIPTION,
24 LAST_UPDATE_DATE,
25 LAST_UPDATED_BY,
26 LAST_UPDATE_LOGIN,
27 CREATION_DATE,
28 CREATED_BY,
29 LANGUAGE,
30 SOURCE_LANG
31 ) select
32 X_LOOKUP_CODE,
33 X_LOOKUP_TYPE,
34 X_DESCRIPTION,
35 X_LAST_UPDATE_DATE,
36 X_LAST_UPDATED_BY,
37 X_LAST_UPDATE_LOGIN,
38 X_CREATION_DATE,
39 X_CREATED_BY,
40 L.LANGUAGE_CODE,
41 userenv('LANG')
42 from FND_LANGUAGES L
43 where L.INSTALLED_FLAG in ('I', 'B')
44 and not exists
45 (select NULL
46 from AK_LOOKUP_CODES T
47 where T.LOOKUP_CODE = X_LOOKUP_CODE
48 and T.LOOKUP_TYPE = X_LOOKUP_TYPE
49 and T.LANGUAGE = L.LANGUAGE_CODE);
50
51 open c;
52 fetch c into X_ROWID;
53 if (c%notfound) then
54 close c;
55 raise no_data_found;
56 end if;
57 close c;
58
59 end INSERT_ROW;
60
61 procedure LOCK_ROW (
62 X_LOOKUP_CODE in VARCHAR2,
63 X_LOOKUP_TYPE in VARCHAR2,
64 X_DESCRIPTION in VARCHAR2
65 ) is
66 cursor c1 is select
67 DESCRIPTION
68 from AK_LOOKUP_CODES
69 where LOOKUP_CODE = X_LOOKUP_CODE
70 and LOOKUP_TYPE = X_LOOKUP_TYPE
71 and LANGUAGE = userenv('LANG')
72 for update of LOOKUP_CODE nowait;
73 tlinfo c1%rowtype;
74
75 begin
76 open c1;
77 fetch c1 into tlinfo;
78 if (c1%notfound) then
79 close c1;
80 return;
81 end if;
82 close c1;
83
84 if ( ((tlinfo.DESCRIPTION = X_DESCRIPTION)
85 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
86 ) then
87 null;
88 else
89 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
90 app_exception.raise_exception;
91 end if;
92 return;
93 end LOCK_ROW;
94
95 procedure UPDATE_ROW (
96 X_LOOKUP_CODE in VARCHAR2,
97 X_LOOKUP_TYPE in VARCHAR2,
98 X_DESCRIPTION in VARCHAR2,
99 X_LAST_UPDATE_DATE in DATE,
100 X_LAST_UPDATED_BY in NUMBER,
101 X_LAST_UPDATE_LOGIN in NUMBER
102 ) is
103 begin
104 update AK_LOOKUP_CODES set
105 DESCRIPTION = X_DESCRIPTION,
106 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
107 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
108 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
109 SOURCE_LANG = userenv('LANG')
110 where LOOKUP_CODE = X_LOOKUP_CODE
111 and LOOKUP_TYPE = X_LOOKUP_TYPE
112 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
113
114 if (sql%notfound) then
115 raise no_data_found;
116 end if;
117 end UPDATE_ROW;
118
119 procedure DELETE_ROW (
120 X_LOOKUP_CODE in VARCHAR2,
121 X_LOOKUP_TYPE in VARCHAR2
122 ) is
123 begin
124 delete from AK_LOOKUP_CODES
125 where LOOKUP_CODE = X_LOOKUP_CODE
126 and LOOKUP_TYPE = X_LOOKUP_TYPE;
127
128 if (sql%notfound) then
129 raise no_data_found;
130 end if;
131
132 end DELETE_ROW;
133
134 procedure ADD_LANGUAGE
135 is
136 begin
137 /* Mar/19/03 requested by Ric Ginsberg */
138 /* The following delete and update statements are commented out */
139 /* as a quick workaround to fix the time-consuming table handler issue */
140 /* Eventually we'll need to turn them into a separate fix_language procedure */
141 /*
142
143 update AK_LOOKUP_CODES T set (
144 DESCRIPTION
145 ) = (select
146 B.DESCRIPTION
147 from AK_LOOKUP_CODES B
148 where B.LOOKUP_CODE = T.LOOKUP_CODE
149 and B.LOOKUP_TYPE = T.LOOKUP_TYPE
150 and B.LANGUAGE = T.SOURCE_LANG)
151 where (
152 T.LOOKUP_CODE,
153 T.LOOKUP_TYPE,
154 T.LANGUAGE
155 ) in (select
156 SUBT.LOOKUP_CODE,
157 SUBT.LOOKUP_TYPE,
158 SUBT.LANGUAGE
159 from AK_LOOKUP_CODES SUBB, AK_LOOKUP_CODES SUBT
160 where SUBB.LOOKUP_CODE = SUBT.LOOKUP_CODE
161 and SUBB.LOOKUP_TYPE = SUBT.LOOKUP_TYPE
162 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
163 and (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
164 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
165 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
166 ));
167
168 */
169
170 insert /*+ append parallel(tt) */ into AK_LOOKUP_CODES tt (
171 LOOKUP_CODE,
172 LOOKUP_TYPE,
173 DESCRIPTION,
174 LAST_UPDATE_DATE,
175 LAST_UPDATED_BY,
176 LAST_UPDATE_LOGIN,
177 CREATION_DATE,
178 CREATED_BY,
179 LANGUAGE,
180 SOURCE_LANG
181 ) select /*+ parallel(v) parallel(t) use_nl(t) */ v.* from
182 (select /*+ no_merge ordered parallel(b) */
183 B.LOOKUP_CODE,
184 B.LOOKUP_TYPE,
185 B.DESCRIPTION,
186 B.LAST_UPDATE_DATE,
187 B.LAST_UPDATED_BY,
188 B.LAST_UPDATE_LOGIN,
189 B.CREATION_DATE,
190 B.CREATED_BY,
191 L.LANGUAGE_CODE,
192 B.SOURCE_LANG
193 from AK_LOOKUP_CODES B, FND_LANGUAGES L
194 where L.INSTALLED_FLAG in ('I', 'B')
195 and B.LANGUAGE = userenv('LANG')
196 ) v, AK_LOOKUP_CODES T
197 where T.LOOKUP_CODE(+) = v.LOOKUP_CODE
198 and T.LOOKUP_TYPE(+) = v.LOOKUP_TYPE
199 and T.LANGUAGE(+) = v.LANGUAGE_CODE
200 and T.LOOKUP_CODE is NULL
201 and T.LOOKUP_TYPE is NULL;
202
203 end ADD_LANGUAGE;
204
205 procedure TRANSLATE_ROW (
206 X_LOOKUP_CODE in VARCHAR2,
207 X_LOOKUP_TYPE in VARCHAR2,
208 X_DESCRIPTION in VARCHAR2,
209 X_LAST_UPDATE_DATE in DATE,
210 X_LAST_UPDATED_BY in NUMBER,
211 X_LAST_UPDATE_LOGIN in NUMBER
212 ) is
213 begin
214 update AK_LOOKUP_CODES set
215 DESCRIPTION = X_DESCRIPTION,
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 SOURCE_LANG = userenv('LANG')
220 where LOOKUP_CODE = X_LOOKUP_CODE
221 and LOOKUP_TYPE = X_LOOKUP_TYPE
222 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
223
224 end TRANSLATE_ROW;
225
226
227 end AK_LOOKUP_CODES_PKG;
[Home] [Help]