1 PACKAGE BODY AP_MAP_TYPES_PKG as
2 /* $Header: apmaptpb.pls 115.3 2004/03/30 23:14:20 kmizuta noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_MAP_TYPE_CODE in VARCHAR2,
6 X_FROM_APPLICATION_ID in NUMBER,
7 X_FROM_LOOKUP_TYPE in VARCHAR2,
8 X_TO_APPLICATION_ID in NUMBER,
9 X_TO_LOOKUP_TYPE in VARCHAR2,
10 X_DEFAULT_LOOKUP_CODE in VARCHAR2,
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 AP_MAP_TYPES_B
20 where MAP_TYPE_CODE = X_MAP_TYPE_CODE
21 ;
22 begin
23 insert into AP_MAP_TYPES_B (
24 MAP_TYPE_CODE,
25 FROM_APPLICATION_ID,
26 FROM_LOOKUP_TYPE,
27 TO_APPLICATION_ID,
28 TO_LOOKUP_TYPE,
29 DEFAULT_LOOKUP_CODE,
30 CREATION_DATE,
31 CREATED_BY,
32 LAST_UPDATE_DATE,
33 LAST_UPDATED_BY,
34 LAST_UPDATE_LOGIN
35 ) values (
36 X_MAP_TYPE_CODE,
37 X_FROM_APPLICATION_ID,
38 X_FROM_LOOKUP_TYPE,
39 X_TO_APPLICATION_ID,
40 X_TO_LOOKUP_TYPE,
41 X_DEFAULT_LOOKUP_CODE,
42 X_CREATION_DATE,
43 X_CREATED_BY,
44 X_LAST_UPDATE_DATE,
45 X_LAST_UPDATED_BY,
46 X_LAST_UPDATE_LOGIN
47 );
48
49 insert into AP_MAP_TYPES_TL (
50 MAP_TYPE_CODE,
51 MEANING,
52 DESCRIPTION,
53 CREATED_BY,
54 CREATION_DATE,
55 LAST_UPDATED_BY,
56 LAST_UPDATE_DATE,
57 LAST_UPDATE_LOGIN,
58 LANGUAGE,
59 SOURCE_LANG
60 ) select
61 X_MAP_TYPE_CODE,
62 X_MEANING,
63 X_DESCRIPTION,
64 X_CREATED_BY,
65 X_CREATION_DATE,
66 X_LAST_UPDATED_BY,
67 X_LAST_UPDATE_DATE,
68 X_LAST_UPDATE_LOGIN,
69 L.LANGUAGE_CODE,
70 userenv('LANG')
71 from FND_LANGUAGES L
72 where L.INSTALLED_FLAG in ('I', 'B')
73 and not exists
74 (select NULL
75 from AP_MAP_TYPES_TL T
76 where T.MAP_TYPE_CODE = X_MAP_TYPE_CODE
77 and T.LANGUAGE = L.LANGUAGE_CODE);
78
79 open c;
80 fetch c into X_ROWID;
81 if (c%notfound) then
82 close c;
83 raise no_data_found;
84 end if;
85 close c;
86
87 end INSERT_ROW;
88
89 procedure LOCK_ROW (
90 X_MAP_TYPE_CODE in VARCHAR2,
91 X_FROM_APPLICATION_ID in NUMBER,
92 X_FROM_LOOKUP_TYPE in VARCHAR2,
93 X_TO_APPLICATION_ID in NUMBER,
94 X_TO_LOOKUP_TYPE in VARCHAR2,
95 X_DEFAULT_LOOKUP_CODE in VARCHAR2,
96 X_MEANING in VARCHAR2,
97 X_DESCRIPTION in VARCHAR2
98 ) is
99 cursor c is select
100 FROM_APPLICATION_ID,
101 FROM_LOOKUP_TYPE,
102 TO_APPLICATION_ID,
103 TO_LOOKUP_TYPE,
104 DEFAULT_LOOKUP_CODE
105 from AP_MAP_TYPES_B
106 where MAP_TYPE_CODE = X_MAP_TYPE_CODE
107 for update of MAP_TYPE_CODE nowait;
108 recinfo c%rowtype;
109
110 cursor c1 is select
111 MEANING,
112 DESCRIPTION,
113 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
114 from AP_MAP_TYPES_TL
115 where MAP_TYPE_CODE = X_MAP_TYPE_CODE
116 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
117 for update of MAP_TYPE_CODE nowait;
118 begin
119 open c;
120 fetch c into recinfo;
121 if (c%notfound) then
122 close c;
123 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
124 app_exception.raise_exception;
125 end if;
126 close c;
127 if ( ((recinfo.FROM_APPLICATION_ID = X_FROM_APPLICATION_ID)
128 OR ((recinfo.FROM_APPLICATION_ID is null) AND (X_FROM_APPLICATION_ID is null)))
129 AND ((recinfo.FROM_LOOKUP_TYPE = X_FROM_LOOKUP_TYPE)
130 OR ((recinfo.FROM_LOOKUP_TYPE is null) AND (X_FROM_LOOKUP_TYPE is null)))
131 AND ((recinfo.TO_APPLICATION_ID = X_TO_APPLICATION_ID)
132 OR ((recinfo.TO_APPLICATION_ID is null) AND (X_TO_APPLICATION_ID is null)))
133 AND ((recinfo.TO_LOOKUP_TYPE = X_TO_LOOKUP_TYPE)
134 OR ((recinfo.TO_LOOKUP_TYPE is null) AND (X_TO_LOOKUP_TYPE is null)))
135 AND ((recinfo.DEFAULT_LOOKUP_CODE = X_DEFAULT_LOOKUP_CODE)
136 OR ((recinfo.DEFAULT_LOOKUP_CODE is null) AND (X_DEFAULT_LOOKUP_CODE is null)))
137 ) then
138 null;
139 else
140 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
141 app_exception.raise_exception;
142 end if;
143
144 for tlinfo in c1 loop
145 if (tlinfo.BASELANG = 'Y') then
146 if ( ((tlinfo.MEANING = X_MEANING)
147 OR ((tlinfo.MEANING is null) AND (X_MEANING is null)))
148 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
149 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
150 ) then
151 null;
152 else
153 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
154 app_exception.raise_exception;
155 end if;
156 end if;
157 end loop;
158 return;
159 end LOCK_ROW;
160
161 procedure UPDATE_ROW (
162 X_MAP_TYPE_CODE in VARCHAR2,
163 X_FROM_APPLICATION_ID in NUMBER,
164 X_FROM_LOOKUP_TYPE in VARCHAR2,
165 X_TO_APPLICATION_ID in NUMBER,
166 X_TO_LOOKUP_TYPE in VARCHAR2,
167 X_DEFAULT_LOOKUP_CODE in VARCHAR2,
168 X_MEANING in VARCHAR2,
169 X_DESCRIPTION in VARCHAR2,
170 X_LAST_UPDATE_DATE in DATE,
171 X_LAST_UPDATED_BY in NUMBER,
172 X_LAST_UPDATE_LOGIN in NUMBER
173 ) is
174 begin
175 update AP_MAP_TYPES_B set
176 FROM_APPLICATION_ID = X_FROM_APPLICATION_ID,
177 FROM_LOOKUP_TYPE = X_FROM_LOOKUP_TYPE,
178 TO_APPLICATION_ID = X_TO_APPLICATION_ID,
179 TO_LOOKUP_TYPE = X_TO_LOOKUP_TYPE,
180 DEFAULT_LOOKUP_CODE = X_DEFAULT_LOOKUP_CODE,
181 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
182 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
183 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
184 where MAP_TYPE_CODE = X_MAP_TYPE_CODE;
185
186 if (sql%notfound) then
187 raise no_data_found;
188 end if;
189
190 update AP_MAP_TYPES_TL set
191 MEANING = X_MEANING,
192 DESCRIPTION = X_DESCRIPTION,
193 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
194 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
195 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
196 SOURCE_LANG = userenv('LANG')
197 where MAP_TYPE_CODE = X_MAP_TYPE_CODE
198 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
199
200 if (sql%notfound) then
201 raise no_data_found;
202 end if;
203 end UPDATE_ROW;
204
205 procedure DELETE_ROW (
206 X_MAP_TYPE_CODE in VARCHAR2
207 ) is
208 begin
209 delete from AP_MAP_CODES
210 where MAP_TYPE_CODE = X_MAP_TYPE_CODE;
211
212 delete from AP_MAP_TYPES_TL
213 where MAP_TYPE_CODE = X_MAP_TYPE_CODE;
214
215 if (sql%notfound) then
216 raise no_data_found;
217 end if;
218
219 delete from AP_MAP_TYPES_B
220 where MAP_TYPE_CODE = X_MAP_TYPE_CODE;
221
222 if (sql%notfound) then
223 raise no_data_found;
224 end if;
225 end DELETE_ROW;
226
227 procedure ADD_LANGUAGE
228 is
229 begin
230 delete from AP_MAP_TYPES_TL T
231 where not exists
232 (select NULL
233 from AP_MAP_TYPES_B B
234 where B.MAP_TYPE_CODE = T.MAP_TYPE_CODE
235 );
236
237 update AP_MAP_TYPES_TL T set (
238 MEANING,
239 DESCRIPTION
240 ) = (select
241 B.MEANING,
242 B.DESCRIPTION
243 from AP_MAP_TYPES_TL B
244 where B.MAP_TYPE_CODE = T.MAP_TYPE_CODE
245 and B.LANGUAGE = T.SOURCE_LANG)
246 where (
247 T.MAP_TYPE_CODE,
248 T.LANGUAGE
249 ) in (select
250 SUBT.MAP_TYPE_CODE,
251 SUBT.LANGUAGE
252 from AP_MAP_TYPES_TL SUBB, AP_MAP_TYPES_TL SUBT
253 where SUBB.MAP_TYPE_CODE = SUBT.MAP_TYPE_CODE
254 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
255 and (SUBB.MEANING <> SUBT.MEANING
256 or (SUBB.MEANING is null and SUBT.MEANING is not null)
257 or (SUBB.MEANING is not null and SUBT.MEANING is null)
258 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
259 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
260 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
261 ));
262
263 insert into AP_MAP_TYPES_TL (
264 MAP_TYPE_CODE,
265 MEANING,
266 DESCRIPTION,
267 CREATED_BY,
268 CREATION_DATE,
269 LAST_UPDATED_BY,
270 LAST_UPDATE_DATE,
271 LAST_UPDATE_LOGIN,
272 LANGUAGE,
273 SOURCE_LANG
274 ) select
275 B.MAP_TYPE_CODE,
276 B.MEANING,
277 B.DESCRIPTION,
278 B.CREATED_BY,
279 B.CREATION_DATE,
280 B.LAST_UPDATED_BY,
281 B.LAST_UPDATE_DATE,
282 B.LAST_UPDATE_LOGIN,
283 L.LANGUAGE_CODE,
284 B.SOURCE_LANG
285 from AP_MAP_TYPES_TL B, FND_LANGUAGES L
286 where L.INSTALLED_FLAG in ('I', 'B')
287 and B.LANGUAGE = userenv('LANG')
288 and not exists
289 (select NULL
290 from AP_MAP_TYPES_TL T
291 where T.MAP_TYPE_CODE = B.MAP_TYPE_CODE
292 and T.LANGUAGE = L.LANGUAGE_CODE);
293 end ADD_LANGUAGE;
294
295 --
296 -- Function to return the destination lookup code
297 -- for a given source lookup code.
298 -- If no mapping is found, then the default is returned.
299 function GET_MAP_TO_CODE(x_map_type_code IN VARCHAR2, x_map_from_code IN VARCHAR2)
300 return VARCHAR2 IS
301 l_map_to_code VARCHAR2(30);
302 begin
303 SELECT to_lookup_code
304 INTO l_map_to_code
305 FROM ap_map_codes
306 WHERE map_type_code = x_map_type_code
307 AND from_lookup_code = x_map_from_code;
308
309 return l_map_to_code;
310 exception
311 when no_data_found then
312 SELECT default_lookup_code
313 INTO l_map_to_code
314 FROM ap_map_types_b
315 WHERE map_type_code = x_map_type_code;
316
317 return l_map_to_code;
318 end GET_MAP_TO_CODE;
319
320
321 FUNCTION IS_LOOKUP_UNUSABLE(x_lookup_app_id in number,
322 x_lookup_type in varchar2,
323 x_lookup_code in varchar2)
324 return boolean is
325 l_end_date date;
326 l_enabled varchar2(1);
327 begin
328 if x_lookup_code is null then
329 return false;
330 end if;
331
332 select end_date_active, enabled_flag
333 into l_end_date, l_enabled
334 from fnd_lookup_values
335 WHERE LANGUAGE = userenv('LANG')
336 AND SECURITY_GROUP_ID = fnd_global.lookup_security_group(LOOKUP_TYPE, VIEW_APPLICATION_ID)
337 AND view_application_id = x_lookup_app_id
338 AND lookup_type = x_lookup_type
339 AND lookup_code = x_lookup_code;
340
341 if (l_end_date < sysdate or l_enabled <> 'Y') then
342 return true;
343 else
344 return false;
345 end if;
346 end IS_LOOKUP_UNUSABLE;
347
348 PROCEDURE CLEAR_DISABLED_CODES(x_map_type_code IN VARCHAR2) IS
349 PRAGMA autonomous_transaction;
350 l_to_app number;
351 l_to_type varchar2(30);
352 l_default_code varchar2(30);
353
354 l_from_app number;
355 l_from_type varchar2(30);
356
357 cursor crules is select from_lookup_code, to_lookup_code
358 from ap_map_codes
359 where map_type_code = x_map_type_code;
360 BEGIN
361 select from_application_id, from_lookup_type, to_application_id, to_lookup_type, default_lookup_code
362 into l_from_app, l_from_type, l_to_app, l_to_type, l_default_code
363 from ap_map_types_b
364 where map_type_code = x_map_type_code;
365
366 if is_lookup_unusable(l_to_app, l_to_type, l_default_code) then
367 update ap_map_types_b set default_lookup_code = null
368 where map_type_code = x_map_type_code;
369 end if;
370
371 for crec in crules loop
372 if is_lookup_unusable(l_to_app, l_to_type, crec.to_lookup_code) or
373 is_lookup_unusable(l_from_app, l_from_type, crec.from_lookup_code) then
374 delete from ap_map_codes
375 where map_type_code = x_map_type_code
376 and from_lookup_code = crec.from_lookup_code;
377 end if;
378 end loop;
379
380 COMMIT;
381 END clear_disabled_codes;
382
383 end AP_MAP_TYPES_PKG;
[Home] [Help]