1 package body FWK_TBX_LOOKUP_TYPES_PKG as
2 /* $Header: fwktbxlookuptypestlb.pls 120.2.12000000.4 2007/07/19 12:04:00 pbhamidi ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_LOOKUP_TYPE in VARCHAR2,
6 X_DISPLAY_NAME 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 FWK_TBX_LOOKUP_TYPES_TL
15 where LOOKUP_TYPE = X_LOOKUP_TYPE
16 and LANGUAGE = userenv('LANG')
17 ;
18 begin
19 insert into FWK_TBX_LOOKUP_TYPES_TL (
20 LAST_UPDATE_DATE,
21 LAST_UPDATED_BY,
22 CREATION_DATE,
23 CREATED_BY,
24 LAST_UPDATE_LOGIN,
25 LOOKUP_TYPE,
26 DISPLAY_NAME,
27 DESCRIPTION,
28 LANGUAGE,
29 SOURCE_LANG
30 ) select
31 X_LAST_UPDATE_DATE,
32 X_LAST_UPDATED_BY,
33 X_CREATION_DATE,
34 X_CREATED_BY,
35 X_LAST_UPDATE_LOGIN,
36 X_LOOKUP_TYPE,
37 X_DISPLAY_NAME,
38 X_DESCRIPTION,
39 L.LANGUAGE_CODE,
40 userenv('LANG')
41 from FND_LANGUAGES L
42 where L.INSTALLED_FLAG in ('I', 'B')
43 and not exists
44 (select NULL
45 from FWK_TBX_LOOKUP_TYPES_TL T
46 where T.LOOKUP_TYPE = X_LOOKUP_TYPE
47 and T.LANGUAGE = L.LANGUAGE_CODE);
48
49 open c;
50 fetch c into X_ROWID;
51 if (c%notfound) then
52 close c;
53 raise no_data_found;
54 end if;
55 close c;
56
57 end INSERT_ROW;
58
59 procedure LOCK_ROW (
60 X_LOOKUP_TYPE in VARCHAR2,
61 X_DISPLAY_NAME in VARCHAR2,
62 X_DESCRIPTION in VARCHAR2
63 ) is
64 cursor c1 is select
65 DISPLAY_NAME,
66 DESCRIPTION,
67 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
68 from FWK_TBX_LOOKUP_TYPES_TL
69 where LOOKUP_TYPE = X_LOOKUP_TYPE
70 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
71 for update of LOOKUP_TYPE nowait;
72 begin
73 for tlinfo in c1 loop
74 if (tlinfo.BASELANG = 'Y') then
75 if ( (tlinfo.DISPLAY_NAME = X_DISPLAY_NAME)
76 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
77 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
78 ) then
79 null;
80 else
81 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
82 app_exception.raise_exception;
83 end if;
84 end if;
85 end loop;
86 return;
87 end LOCK_ROW;
88
89 procedure UPDATE_ROW (
90 X_LOOKUP_TYPE in VARCHAR2,
91 X_DISPLAY_NAME in VARCHAR2,
92 X_DESCRIPTION in VARCHAR2,
93 X_LAST_UPDATE_DATE in DATE,
94 X_LAST_UPDATED_BY in NUMBER,
95 X_LAST_UPDATE_LOGIN in NUMBER
96 ) is
97 begin
98 update FWK_TBX_LOOKUP_TYPES_TL set
99 DISPLAY_NAME = X_DISPLAY_NAME,
100 DESCRIPTION = X_DESCRIPTION,
101 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
102 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
103 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
104 SOURCE_LANG = userenv('LANG')
105 where LOOKUP_TYPE = X_LOOKUP_TYPE
106 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
107
108 if (sql%notfound) then
109 raise no_data_found;
110 end if;
111 end UPDATE_ROW;
112
113 procedure DELETE_ROW (
114 X_LOOKUP_TYPE in VARCHAR2
115 ) is
116 begin
117 delete from FWK_TBX_LOOKUP_TYPES_TL
118 where LOOKUP_TYPE = X_LOOKUP_TYPE;
119
120 if (sql%notfound) then
121 raise no_data_found;
122 end if;
123
124 end DELETE_ROW;
125
126 procedure ADD_LANGUAGE
127 is
128 begin
129 update FWK_TBX_LOOKUP_TYPES_TL T set (
130 DISPLAY_NAME,
131 DESCRIPTION
132 ) = (select
133 B.DISPLAY_NAME,
134 B.DESCRIPTION
135 from FWK_TBX_LOOKUP_TYPES_TL B
136 where B.LOOKUP_TYPE = T.LOOKUP_TYPE
137 and B.LANGUAGE = T.SOURCE_LANG)
138 where (
139 T.LOOKUP_TYPE,
140 T.LANGUAGE
141 ) in (select
142 SUBT.LOOKUP_TYPE,
143 SUBT.LANGUAGE
144 from FWK_TBX_LOOKUP_TYPES_TL SUBB, FWK_TBX_LOOKUP_TYPES_TL SUBT
145 where SUBB.LOOKUP_TYPE = SUBT.LOOKUP_TYPE
146 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
147 and (SUBB.DISPLAY_NAME <> SUBT.DISPLAY_NAME
148 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
149 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
150 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
151 ));
152
153 insert into FWK_TBX_LOOKUP_TYPES_TL (
154 LAST_UPDATE_DATE,
155 LAST_UPDATED_BY,
156 CREATION_DATE,
157 CREATED_BY,
158 LAST_UPDATE_LOGIN,
159 LOOKUP_TYPE,
160 DISPLAY_NAME,
161 DESCRIPTION,
162 LANGUAGE,
163 SOURCE_LANG
164 ) select /*+ ORDERED */
165 B.LAST_UPDATE_DATE,
166 B.LAST_UPDATED_BY,
167 B.CREATION_DATE,
168 B.CREATED_BY,
169 B.LAST_UPDATE_LOGIN,
170 B.LOOKUP_TYPE,
171 B.DISPLAY_NAME,
172 B.DESCRIPTION,
173 L.LANGUAGE_CODE,
174 B.SOURCE_LANG
175 from FWK_TBX_LOOKUP_TYPES_TL B, FND_LANGUAGES L
176 where L.INSTALLED_FLAG in ('I', 'B')
177 and B.LANGUAGE = userenv('LANG')
178 and not exists
179 (select NULL
180 from FWK_TBX_LOOKUP_TYPES_TL T
181 where T.LOOKUP_TYPE = B.LOOKUP_TYPE
182 and T.LANGUAGE = L.LANGUAGE_CODE);
183 end ADD_LANGUAGE;
184
185 procedure TRANSLATE_ROW (
186 X_LOOKUP_TYPE in VARCHAR2,
187 X_OWNER in VARCHAR2,
188 X_DISPLAY_NAME in VARCHAR2,
189 X_DESCRIPTION in VARCHAR2,
190 X_LAST_UPDATE_DATE in VARCHAR2,
191 X_CUSTOM_MODE in VARCHAR2)
192 is
193 f_luby number; -- entity owner in file
194 f_ludate date; -- entity update date in file
195 db_luby number; -- entity owner in db
196 db_ludate date; -- entity update date in db
197 begin
198 f_luby := fnd_load_util.owner_id(x_owner);
199 f_ludate := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
200
201 select last_updated_by, last_update_date
202 into db_luby, db_ludate
203 from FWK_TBX_LOOKUP_TYPES_TL
204 where lookup_type = X_LOOKUP_TYPE
205 and language = userenv('LANG');
206
207 -- We want the values from file to be populated, if db has null value
208 db_ludate := nvl(db_ludate, to_date('1990/01/01', 'YYYY/MM/DD'));
209 -- Default last updated by to SEED, if db has null value
210 db_luby := nvl(db_luby, fnd_load_util.owner_id('SEED'));
211
212 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
213 db_ludate, X_CUSTOM_MODE)) then
214 update FWK_TBX_LOOKUP_TYPES_TL set
215 display_name = nvl(X_DISPLAY_NAME, display_name),
216 description = nvl(X_DESCRIPTION, description),
217 last_update_date = f_ludate,
218 last_updated_by = f_luby,
219 last_update_login = 0,
220 source_lang = userenv('LANG')
221 where lookup_type = X_LOOKUP_TYPE
222 and userenv('LANG') in (language, source_lang);
223 end if;
224
225 end TRANSLATE_ROW;
226
227 procedure LOAD_ROW (
228 X_ROWID in out nocopy VARCHAR2,
229 X_LOOKUP_TYPE in VARCHAR2,
230 X_DISPLAY_NAME in VARCHAR2,
231 X_DESCRIPTION in VARCHAR2,
232 X_CREATION_DATE in DATE,
233 X_CREATED_BY in NUMBER,
234 X_LAST_UPDATE_DATE in DATE,
235 X_LAST_UPDATED_BY in NUMBER,
236 X_LAST_UPDATE_LOGIN in NUMBER,
237 X_OWNER in VARCHAR2,
238 X_CUSTOM_MODE in VARCHAR2)
239 is
240 f_luby number; -- entity owner in file
241 f_ludate date; -- entity update date in file
242 db_luby number; -- entity owner in db
243 db_ludate date; -- entity update date in db
244 begin
245 f_luby := fnd_load_util.owner_id(X_OWNER);
246 f_ludate := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
247
248 select last_updated_by, last_update_date
249 into db_luby, db_ludate
250 from FWK_TBX_LOOKUP_TYPES_TL
251 where lookup_type = X_LOOKUP_TYPE
252 and language = userenv('LANG');
253
254 -- We want the values from file to be populated, if db has null value
255 db_ludate := nvl(db_ludate, to_date('1990/01/01', 'YYYY/MM/DD'));
256 -- Default last updated by to SEED, if db has null value
257 db_luby := nvl(db_luby, fnd_load_util.owner_id('SEED'));
258
259 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
260 db_ludate, X_CUSTOM_MODE)) then
261 FWK_TBX_LOOKUP_TYPES_PKG.UPDATE_ROW (
262 X_LOOKUP_TYPE => X_LOOKUP_TYPE,
263 X_DISPLAY_NAME => X_DISPLAY_NAME,
264 X_DESCRIPTION => X_DESCRIPTION,
265 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
266 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
267 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN );
268 end if;
269 exception
270 when no_data_found then
271 -- Record doesn't exist - insert in all cases
272
273 FWK_TBX_LOOKUP_TYPES_PKG.INSERT_ROW(
274 X_ROWID => X_ROWID,
275 X_LOOKUP_TYPE => X_LOOKUP_TYPE,
276 X_DISPLAY_NAME => X_DISPLAY_NAME,
277 X_DESCRIPTION => X_DESCRIPTION,
278 X_CREATION_DATE => X_CREATION_DATE,
279 X_CREATED_BY => X_CREATED_BY,
280 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
281 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
282 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN );
283
284 end LOAD_ROW;
285
286 end FWK_TBX_LOOKUP_TYPES_PKG;
[Home] [Help]