1 package body FEM_MAP_RULE_TYPES_PKG as
2 /* $Header: fem_maprltyp_pkb.plb 120.2 2008/02/20 07:00:47 jcliving ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_MAP_RULE_TYPE_CODE in VARCHAR2,
6 X_ENABLED_FLAG in VARCHAR2,
7 X_OBJECT_VERSION_NUMBER in NUMBER,
8 X_MAP_RULE_TYPE_NAME in VARCHAR2,
9 X_DESCRIPTION in VARCHAR2,
10 X_CREATION_DATE in DATE,
11 X_CREATED_BY in NUMBER,
12 X_LAST_UPDATE_DATE in DATE,
13 X_LAST_UPDATED_BY in NUMBER,
14 X_LAST_UPDATE_LOGIN in NUMBER
15 ) is
16 cursor C is select ROWID from FEM_MAP_RULE_TYPES_B
17 where MAP_RULE_TYPE_CODE = X_MAP_RULE_TYPE_CODE
18 ;
19 begin
20 insert into FEM_MAP_RULE_TYPES_B (
21 MAP_RULE_TYPE_CODE,
22 ENABLED_FLAG,
23 OBJECT_VERSION_NUMBER,
24 CREATION_DATE,
25 CREATED_BY,
26 LAST_UPDATE_DATE,
27 LAST_UPDATED_BY,
28 LAST_UPDATE_LOGIN
29 ) values (
30 X_MAP_RULE_TYPE_CODE,
31 X_ENABLED_FLAG,
32 X_OBJECT_VERSION_NUMBER,
33 X_CREATION_DATE,
34 X_CREATED_BY,
35 X_LAST_UPDATE_DATE,
36 X_LAST_UPDATED_BY,
37 X_LAST_UPDATE_LOGIN
38 );
39
40 insert into FEM_MAP_RULE_TYPES_TL (
41 MAP_RULE_TYPE_CODE,
42 MAP_RULE_TYPE_NAME,
43 DESCRIPTION,
44 CREATION_DATE,
45 CREATED_BY,
46 LAST_UPDATED_BY,
47 LAST_UPDATE_DATE,
48 LAST_UPDATE_LOGIN,
49 LANGUAGE,
50 SOURCE_LANG
51 ) select
52 X_MAP_RULE_TYPE_CODE,
53 X_MAP_RULE_TYPE_NAME,
54 X_DESCRIPTION,
55 X_CREATION_DATE,
56 X_CREATED_BY,
57 X_LAST_UPDATED_BY,
58 X_LAST_UPDATE_DATE,
59 X_LAST_UPDATE_LOGIN,
60 L.LANGUAGE_CODE,
61 userenv('LANG')
62 from FND_LANGUAGES L
63 where L.INSTALLED_FLAG in ('I', 'B')
64 and not exists
65 (select NULL
66 from FEM_MAP_RULE_TYPES_TL T
67 where T.MAP_RULE_TYPE_CODE = X_MAP_RULE_TYPE_CODE
68 and T.LANGUAGE = L.LANGUAGE_CODE);
69
70 open c;
71 fetch c into X_ROWID;
72 if (c%notfound) then
73 close c;
74 raise no_data_found;
75 end if;
76 close c;
77
78 end INSERT_ROW;
79
80 procedure LOCK_ROW (
81 X_MAP_RULE_TYPE_CODE in VARCHAR2,
82 X_ENABLED_FLAG in VARCHAR2,
83 X_OBJECT_VERSION_NUMBER in NUMBER,
84 X_MAP_RULE_TYPE_NAME in VARCHAR2,
85 X_DESCRIPTION in VARCHAR2
86 ) is
87 cursor c is select
88 ENABLED_FLAG,
89 OBJECT_VERSION_NUMBER
90 from FEM_MAP_RULE_TYPES_B
91 where MAP_RULE_TYPE_CODE = X_MAP_RULE_TYPE_CODE
92 for update of MAP_RULE_TYPE_CODE nowait;
93 recinfo c%rowtype;
94
95 cursor c1 is select
96 MAP_RULE_TYPE_NAME,
97 DESCRIPTION,
98 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
99 from FEM_MAP_RULE_TYPES_TL
100 where MAP_RULE_TYPE_CODE = X_MAP_RULE_TYPE_CODE
101 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
102 for update of MAP_RULE_TYPE_CODE nowait;
103 begin
104 open c;
105 fetch c into recinfo;
106 if (c%notfound) then
107 close c;
108 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
109 app_exception.raise_exception;
110 end if;
111 close c;
112 if ( (recinfo.ENABLED_FLAG = X_ENABLED_FLAG)
113 AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
114 ) then
115 null;
116 else
117 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
118 app_exception.raise_exception;
119 end if;
120
121 for tlinfo in c1 loop
122 if (tlinfo.BASELANG = 'Y') then
123 if ( (tlinfo.MAP_RULE_TYPE_NAME = X_MAP_RULE_TYPE_NAME)
124 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
125 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
126 ) then
127 null;
128 else
129 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
130 app_exception.raise_exception;
131 end if;
132 end if;
133 end loop;
134 return;
135 end LOCK_ROW;
136
137 procedure UPDATE_ROW (
138 X_MAP_RULE_TYPE_CODE in VARCHAR2,
139 X_ENABLED_FLAG in VARCHAR2,
140 X_OBJECT_VERSION_NUMBER in NUMBER,
141 X_MAP_RULE_TYPE_NAME in VARCHAR2,
142 X_DESCRIPTION in VARCHAR2,
143 X_LAST_UPDATE_DATE in DATE,
144 X_LAST_UPDATED_BY in NUMBER,
145 X_LAST_UPDATE_LOGIN in NUMBER
146 ) is
147 begin
148 update FEM_MAP_RULE_TYPES_B set
149 ENABLED_FLAG = X_ENABLED_FLAG,
150 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
151 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
152 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
153 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
154 where MAP_RULE_TYPE_CODE = X_MAP_RULE_TYPE_CODE;
155
156 if (sql%notfound) then
157 raise no_data_found;
158 end if;
159
160 update FEM_MAP_RULE_TYPES_TL set
161 MAP_RULE_TYPE_NAME = X_MAP_RULE_TYPE_NAME,
162 DESCRIPTION = X_DESCRIPTION,
163 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
164 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
165 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
166 SOURCE_LANG = userenv('LANG')
167 where MAP_RULE_TYPE_CODE = X_MAP_RULE_TYPE_CODE
168 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
169
170 if (sql%notfound) then
171 raise no_data_found;
172 end if;
173 end UPDATE_ROW;
174
175 procedure DELETE_ROW (
176 X_MAP_RULE_TYPE_CODE in VARCHAR2
177 ) is
178 begin
179 delete from FEM_MAP_RULE_TYPES_TL
180 where MAP_RULE_TYPE_CODE = X_MAP_RULE_TYPE_CODE;
181
182 if (sql%notfound) then
183 raise no_data_found;
184 end if;
185
186 delete from FEM_MAP_RULE_TYPES_B
187 where MAP_RULE_TYPE_CODE = X_MAP_RULE_TYPE_CODE;
188
189 if (sql%notfound) then
190 raise no_data_found;
191 end if;
192 end DELETE_ROW;
193
194 procedure ADD_LANGUAGE
195 is
196 begin
197 delete from FEM_MAP_RULE_TYPES_TL T
198 where not exists
199 (select NULL
200 from FEM_MAP_RULE_TYPES_B B
201 where B.MAP_RULE_TYPE_CODE = T.MAP_RULE_TYPE_CODE
202 );
203
204 update FEM_MAP_RULE_TYPES_TL T set (
205 MAP_RULE_TYPE_NAME,
206 DESCRIPTION
207 ) = (select
208 B.MAP_RULE_TYPE_NAME,
209 B.DESCRIPTION
210 from FEM_MAP_RULE_TYPES_TL B
211 where B.MAP_RULE_TYPE_CODE = T.MAP_RULE_TYPE_CODE
212 and B.LANGUAGE = T.SOURCE_LANG)
213 where (
214 T.MAP_RULE_TYPE_CODE,
215 T.LANGUAGE
216 ) in (select
217 SUBT.MAP_RULE_TYPE_CODE,
218 SUBT.LANGUAGE
219 from FEM_MAP_RULE_TYPES_TL SUBB, FEM_MAP_RULE_TYPES_TL SUBT
220 where SUBB.MAP_RULE_TYPE_CODE = SUBT.MAP_RULE_TYPE_CODE
221 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
222 and (SUBB.MAP_RULE_TYPE_NAME <> SUBT.MAP_RULE_TYPE_NAME
223 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
224 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
225 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
226 ));
227
228 insert into FEM_MAP_RULE_TYPES_TL (
229 MAP_RULE_TYPE_CODE,
230 MAP_RULE_TYPE_NAME,
231 DESCRIPTION,
232 CREATION_DATE,
233 CREATED_BY,
234 LAST_UPDATED_BY,
235 LAST_UPDATE_DATE,
236 LAST_UPDATE_LOGIN,
237 LANGUAGE,
238 SOURCE_LANG
239 ) select /*+ ORDERED */
240 B.MAP_RULE_TYPE_CODE,
241 B.MAP_RULE_TYPE_NAME,
242 B.DESCRIPTION,
243 B.CREATION_DATE,
244 B.CREATED_BY,
245 B.LAST_UPDATED_BY,
246 B.LAST_UPDATE_DATE,
247 B.LAST_UPDATE_LOGIN,
248 L.LANGUAGE_CODE,
249 B.SOURCE_LANG
250 from FEM_MAP_RULE_TYPES_TL B, FND_LANGUAGES L
251 where L.INSTALLED_FLAG in ('I', 'B')
252 and B.LANGUAGE = userenv('LANG')
253 and not exists
254 (select NULL
255 from FEM_MAP_RULE_TYPES_TL T
256 where T.MAP_RULE_TYPE_CODE = B.MAP_RULE_TYPE_CODE
257 and T.LANGUAGE = L.LANGUAGE_CODE);
258 end ADD_LANGUAGE;
259 PROCEDURE TRANSLATE_ROW(
260 x_MAP_RULE_TYPE_CODE in varchar2,
261 x_owner in varchar2,
262 x_last_update_date in varchar2,
263 x_MAP_RULE_TYPE_NAME in varchar2,
264 x_description in varchar2,
265 x_custom_mode in varchar2) is
266
267 owner_id number;
268 ludate date;
269 row_id varchar2(64);
270 f_luby number; -- entity owner in file
271 f_ludate date; -- entity update date in file
272 db_luby number; -- entity owner in db
273 db_ludate date; -- entity update date in db
274 begin
275
276
277 -- Translate owner to file_last_updated_by
278 f_luby := fnd_load_util.owner_id(x_owner);
279
280 -- Translate char last_update_date to date
281 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
282 begin
283 select LAST_UPDATED_BY, LAST_UPDATE_DATE
284 into db_luby, db_ludate
285 from FEM_MAP_RULE_TYPES_TL
286 where MAP_RULE_TYPE_CODE = x_MAP_RULE_TYPE_CODE
287 and LANGUAGE = userenv('LANG');
288
289 -- Test for customization and version
290 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
291 db_ludate, x_custom_mode)) then
292 -- Update translations for this language
293 update FEM_MAP_RULE_TYPES_TL set
294 MAP_RULE_TYPE_NAME = decode(x_MAP_RULE_TYPE_NAME,
295 fnd_load_util.null_value, null, -- Real null
296 null, x_MAP_RULE_TYPE_NAME, -- No change
297 x_MAP_RULE_TYPE_NAME),
298 DESCRIPTION = nvl(x_description, DESCRIPTION),
299 LAST_UPDATE_DATE = f_ludate,
300 LAST_UPDATED_BY = f_luby,
301 LAST_UPDATE_LOGIN = 0,
302 SOURCE_LANG = userenv('LANG')
303 where userenv('LANG') in (LANGUAGE, SOURCE_LANG)
304 and MAP_RULE_TYPE_CODE = x_MAP_RULE_TYPE_CODE;
305 end if;
306 exception
307 when no_data_found then
308 -- Do not insert missing translations, skip this row
309 null;
310 end;
311 end TRANSLATE_ROW;
312
313
314 end FEM_MAP_RULE_TYPES_PKG;
[Home] [Help]