1 package body FND_CONC_STATE_LOOKUPS_PKG as
2 /* $Header: AFCPSC4B.pls 120.2 2005/08/19 11:10:27 ddhulla ship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in out NOCOPY VARCHAR2,
6 X_LOOKUP_TYPE_ID in NUMBER,
7 X_LOOKUP_VALUE in NUMBER,
8 X_ENABLED_FLAG in VARCHAR2,
9 X_START_DATE_ACTIVE in DATE,
10 X_END_DATE_ACTIVE in DATE,
11 X_MEANING in VARCHAR2,
12 X_DESCRIPTION in VARCHAR2,
13 X_CREATION_DATE in DATE,
14 X_CREATED_BY in NUMBER,
15 X_LAST_UPDATE_DATE in DATE,
16 X_LAST_UPDATED_BY in NUMBER,
17 X_LAST_UPDATE_LOGIN in NUMBER
18 ) is
19 cursor C is select ROWID from FND_CONC_STATE_LOOKUPS
20 where LOOKUP_TYPE_ID = X_LOOKUP_TYPE_ID
21 and LOOKUP_VALUE = X_LOOKUP_VALUE
22 ;
23 begin
24 insert into FND_CONC_STATE_LOOKUPS (
25 LOOKUP_TYPE_ID,
26 LOOKUP_VALUE,
27 ENABLED_FLAG,
28 START_DATE_ACTIVE,
29 END_DATE_ACTIVE,
30 CREATION_DATE,
31 CREATED_BY,
32 LAST_UPDATE_DATE,
33 LAST_UPDATED_BY,
34 LAST_UPDATE_LOGIN
35 ) values (
36 X_LOOKUP_TYPE_ID,
37 X_LOOKUP_VALUE,
38 X_ENABLED_FLAG,
39 X_START_DATE_ACTIVE,
40 X_END_DATE_ACTIVE,
41 X_CREATION_DATE,
42 X_CREATED_BY,
43 X_LAST_UPDATE_DATE,
44 X_LAST_UPDATED_BY,
45 X_LAST_UPDATE_LOGIN
46 );
47
48 insert into FND_CONC_STATE_LOOKUPS_TL (
49 LOOKUP_TYPE_ID,
50 LOOKUP_VALUE,
51 MEANING,
52 DESCRIPTION,
53 LAST_UPDATE_DATE,
54 LAST_UPDATED_BY,
55 LAST_UPDATE_LOGIN,
56 CREATION_DATE,
57 CREATED_BY,
58 LANGUAGE,
59 SOURCE_LANG
60 ) select
61 X_LOOKUP_TYPE_ID,
62 X_LOOKUP_VALUE,
63 X_MEANING,
64 X_DESCRIPTION,
65 X_LAST_UPDATE_DATE,
66 X_LAST_UPDATED_BY,
67 X_LAST_UPDATE_LOGIN,
68 X_CREATION_DATE,
69 X_CREATED_BY,
70 L.LANGUAGE_CODE,
71 userenv('LANG')
72 from FND_LANGUAGES L
73 where L.INSTALLED_FLAG in ('I', 'B')
74 and not exists
75 (select NULL
76 from FND_CONC_STATE_LOOKUPS_TL T
77 where T.LOOKUP_TYPE_ID = X_LOOKUP_TYPE_ID
78 and T.LOOKUP_VALUE = X_LOOKUP_VALUE
79 and T.LANGUAGE = L.LANGUAGE_CODE);
80
81 open c;
82 fetch c into X_ROWID;
83 if (c%notfound) then
84 close c;
85 raise no_data_found;
86 end if;
87 close c;
88
89 end INSERT_ROW;
90
91 procedure LOCK_ROW (
92 X_LOOKUP_TYPE_ID in NUMBER,
93 X_LOOKUP_VALUE in NUMBER,
94 X_ENABLED_FLAG in VARCHAR2,
95 X_START_DATE_ACTIVE in DATE,
96 X_END_DATE_ACTIVE in DATE,
97 X_MEANING in VARCHAR2,
98 X_DESCRIPTION in VARCHAR2
99 ) is
100 cursor c is select
101 ENABLED_FLAG,
102 START_DATE_ACTIVE,
103 END_DATE_ACTIVE
104 from FND_CONC_STATE_LOOKUPS
105 where LOOKUP_TYPE_ID = X_LOOKUP_TYPE_ID
106 and LOOKUP_VALUE = X_LOOKUP_VALUE
107 for update of LOOKUP_TYPE_ID nowait;
108 recinfo c%rowtype;
109
110 cursor c1 is select
111 MEANING,
112 DESCRIPTION
113 from FND_CONC_STATE_LOOKUPS_TL
114 where LOOKUP_TYPE_ID = X_LOOKUP_TYPE_ID
115 and LOOKUP_VALUE = X_LOOKUP_VALUE
116 and LANGUAGE = userenv('LANG')
117 for update of LOOKUP_TYPE_ID nowait;
118 tlinfo c1%rowtype;
119
120 begin
121 open c;
122 fetch c into recinfo;
123 if (c%notfound) then
124 close c;
125 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
126 app_exception.raise_exception;
127 end if;
128 close c;
129 if ( (recinfo.ENABLED_FLAG = X_ENABLED_FLAG)
130 AND ((recinfo.START_DATE_ACTIVE = X_START_DATE_ACTIVE)
131 OR ((recinfo.START_DATE_ACTIVE is null) AND (X_START_DATE_ACTIVE is null)))
132 AND ((recinfo.END_DATE_ACTIVE = X_END_DATE_ACTIVE)
133 OR ((recinfo.END_DATE_ACTIVE is null) AND (X_END_DATE_ACTIVE is null)))
134 ) then
135 null;
136 else
137 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
138 app_exception.raise_exception;
139 end if;
140
141 open c1;
142 fetch c1 into tlinfo;
143 if (c1%notfound) then
144 close c1;
145 return;
146 end if;
147 close c1;
148
149 if ( (tlinfo.MEANING = X_MEANING)
150 AND (tlinfo.DESCRIPTION = X_DESCRIPTION)
151 ) then
152 null;
153 else
154 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
155 app_exception.raise_exception;
156 end if;
157 return;
158 end LOCK_ROW;
159
160 procedure UPDATE_ROW (
161 X_LOOKUP_TYPE_ID in NUMBER,
162 X_LOOKUP_VALUE in NUMBER,
163 X_ENABLED_FLAG in VARCHAR2,
164 X_START_DATE_ACTIVE in DATE,
165 X_END_DATE_ACTIVE in DATE,
166 X_MEANING in VARCHAR2,
167 X_DESCRIPTION in VARCHAR2,
168 X_LAST_UPDATE_DATE in DATE,
169 X_LAST_UPDATED_BY in NUMBER,
170 X_LAST_UPDATE_LOGIN in NUMBER
171 ) is
172 begin
173 update FND_CONC_STATE_LOOKUPS set
174 ENABLED_FLAG = X_ENABLED_FLAG,
175 START_DATE_ACTIVE = X_START_DATE_ACTIVE,
176 END_DATE_ACTIVE = X_END_DATE_ACTIVE,
177 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
178 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
179 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
180 where LOOKUP_TYPE_ID = X_LOOKUP_TYPE_ID
181 and LOOKUP_VALUE = X_LOOKUP_VALUE;
182
183 if (sql%notfound) then
184 raise no_data_found;
185 end if;
186
187 update FND_CONC_STATE_LOOKUPS_TL set
188 MEANING = X_MEANING,
189 DESCRIPTION = X_DESCRIPTION,
190 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
191 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
192 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
193 SOURCE_LANG = userenv('LANG')
194 where LOOKUP_TYPE_ID = X_LOOKUP_TYPE_ID
195 and LOOKUP_VALUE = X_LOOKUP_VALUE
196 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
197
198 if (sql%notfound) then
199 raise no_data_found;
200 end if;
201 end UPDATE_ROW;
202
203 procedure DELETE_ROW (
204 X_LOOKUP_TYPE_ID in NUMBER,
205 X_LOOKUP_VALUE in NUMBER
206 ) is
207 begin
208 delete from FND_CONC_STATE_LOOKUPS
209 where LOOKUP_TYPE_ID = X_LOOKUP_TYPE_ID
210 and LOOKUP_VALUE = X_LOOKUP_VALUE;
211
212 if (sql%notfound) then
213 raise no_data_found;
214 end if;
215
216 delete from FND_CONC_STATE_LOOKUPS_TL
217 where LOOKUP_TYPE_ID = X_LOOKUP_TYPE_ID
218 and LOOKUP_VALUE = X_LOOKUP_VALUE;
219
220 if (sql%notfound) then
221 raise no_data_found;
222 end if;
223 end DELETE_ROW;
224
225 procedure ADD_LANGUAGE
226 is
227 begin
228
229 /* Mar/19/03 requested by Ric Ginsberg */
230 /* The following delete and update statements are commented out */
231 /* as a quick workaround to fix the time-consuming table handler issue */
232 /* Eventually we'll need to turn them into a separate fix_language procedure */
233 /*
234
235 delete from FND_CONC_STATE_LOOKUPS_TL T
236 where not exists
237 (select NULL
238 from FND_CONC_STATE_LOOKUPS B
239 where B.LOOKUP_TYPE_ID = T.LOOKUP_TYPE_ID
240 and B.LOOKUP_VALUE = T.LOOKUP_VALUE
241 );
242
243 update FND_CONC_STATE_LOOKUPS_TL T set (
244 MEANING,
245 DESCRIPTION
246 ) = (select
247 B.MEANING,
248 B.DESCRIPTION
249 from FND_CONC_STATE_LOOKUPS_TL B
250 where B.LOOKUP_TYPE_ID = T.LOOKUP_TYPE_ID
251 and B.LOOKUP_VALUE = T.LOOKUP_VALUE
252 and B.LANGUAGE = T.SOURCE_LANG)
253 where (
254 T.LOOKUP_TYPE_ID,
255 T.LOOKUP_VALUE,
256 T.LANGUAGE
257 ) in (select
258 SUBT.LOOKUP_TYPE_ID,
259 SUBT.LOOKUP_VALUE,
260 SUBT.LANGUAGE
261 from FND_CONC_STATE_LOOKUPS_TL SUBB, FND_CONC_STATE_LOOKUPS_TL SUBT
262 where SUBB.LOOKUP_TYPE_ID = SUBT.LOOKUP_TYPE_ID
263 and SUBB.LOOKUP_VALUE = SUBT.LOOKUP_VALUE
264 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
265 and (SUBB.MEANING <> SUBT.MEANING
266 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
267 ));
268 */
269
270 insert into FND_CONC_STATE_LOOKUPS_TL (
271 LOOKUP_TYPE_ID,
272 LOOKUP_VALUE,
273 MEANING,
274 DESCRIPTION,
275 LAST_UPDATE_DATE,
276 LAST_UPDATED_BY,
277 LAST_UPDATE_LOGIN,
278 CREATION_DATE,
279 CREATED_BY,
280 LANGUAGE,
281 SOURCE_LANG
282 ) select
283 B.LOOKUP_TYPE_ID,
284 B.LOOKUP_VALUE,
285 B.MEANING,
286 B.DESCRIPTION,
287 B.LAST_UPDATE_DATE,
288 B.LAST_UPDATED_BY,
289 B.LAST_UPDATE_LOGIN,
290 B.CREATION_DATE,
291 B.CREATED_BY,
292 L.LANGUAGE_CODE,
293 B.SOURCE_LANG
294 from FND_CONC_STATE_LOOKUPS_TL B, FND_LANGUAGES L
295 where L.INSTALLED_FLAG in ('I', 'B')
296 and B.LANGUAGE = userenv('LANG')
297 and not exists
298 (select NULL
299 from FND_CONC_STATE_LOOKUPS_TL T
300 where T.LOOKUP_TYPE_ID = B.LOOKUP_TYPE_ID
301 and T.LOOKUP_VALUE = B.LOOKUP_VALUE
302 and T.LANGUAGE = L.LANGUAGE_CODE);
303 end ADD_LANGUAGE;
304
305 end FND_CONC_STATE_LOOKUPS_PKG;
[Home] [Help]