[Home] [Help]
PACKAGE BODY: APPS.FEM_BAL_SHEET_MODELS_PKG
Source
1 package body FEM_BAL_SHEET_MODELS_PKG as
2 /* $Header: fem_blshtmdl_pkb.plb 120.0 2005/06/06 19:52:38 appldev noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_BAL_SHEET_MODEL_CODE in VARCHAR2,
6 X_BAL_SHEET_CALC_TYPE_CODE in VARCHAR2,
7 X_BAL_SHEET_DEF_TYPE_CODE in VARCHAR2,
8 X_ENABLED_FLAG in VARCHAR2,
9 X_READ_ONLY_FLAG in VARCHAR2,
10 X_PERSONAL_FLAG in VARCHAR2,
11 X_BAL_SHEET_MODEL_NAME 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 FEM_BAL_SHEET_MODELS_B
20 where BAL_SHEET_MODEL_CODE = X_BAL_SHEET_MODEL_CODE
21 ;
22 begin
23 insert into FEM_BAL_SHEET_MODELS_B (
24 BAL_SHEET_MODEL_CODE,
25 BAL_SHEET_CALC_TYPE_CODE,
26 BAL_SHEET_DEF_TYPE_CODE,
27 ENABLED_FLAG,
28 READ_ONLY_FLAG,
29 PERSONAL_FLAG,
30 CREATION_DATE,
31 CREATED_BY,
32 LAST_UPDATE_DATE,
33 LAST_UPDATED_BY,
34 LAST_UPDATE_LOGIN
35 ) values (
36 X_BAL_SHEET_MODEL_CODE,
37 X_BAL_SHEET_CALC_TYPE_CODE,
38 X_BAL_SHEET_DEF_TYPE_CODE,
39 X_ENABLED_FLAG,
40 X_READ_ONLY_FLAG,
41 X_PERSONAL_FLAG,
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 FEM_BAL_SHEET_MODELS_TL (
50 LAST_UPDATE_LOGIN,
51 DESCRIPTION,
52 CREATION_DATE,
53 CREATED_BY,
54 LAST_UPDATED_BY,
55 LAST_UPDATE_DATE,
56 BAL_SHEET_MODEL_CODE,
57 BAL_SHEET_MODEL_NAME,
58 LANGUAGE,
59 SOURCE_LANG
60 ) select
61 X_LAST_UPDATE_LOGIN,
62 X_DESCRIPTION,
63 X_CREATION_DATE,
64 X_CREATED_BY,
65 X_LAST_UPDATED_BY,
66 X_LAST_UPDATE_DATE,
67 X_BAL_SHEET_MODEL_CODE,
68 X_BAL_SHEET_MODEL_NAME,
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 FEM_BAL_SHEET_MODELS_TL T
76 where T.BAL_SHEET_MODEL_CODE = X_BAL_SHEET_MODEL_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_BAL_SHEET_MODEL_CODE in VARCHAR2,
91 X_BAL_SHEET_CALC_TYPE_CODE in VARCHAR2,
92 X_BAL_SHEET_DEF_TYPE_CODE in VARCHAR2,
93 X_ENABLED_FLAG in VARCHAR2,
94 X_READ_ONLY_FLAG in VARCHAR2,
95 X_PERSONAL_FLAG in VARCHAR2,
96 X_BAL_SHEET_MODEL_NAME in VARCHAR2,
97 X_DESCRIPTION in VARCHAR2
98 ) is
99 cursor c is select
100 BAL_SHEET_CALC_TYPE_CODE,
101 BAL_SHEET_DEF_TYPE_CODE,
102 ENABLED_FLAG,
103 READ_ONLY_FLAG,
104 PERSONAL_FLAG
105 from FEM_BAL_SHEET_MODELS_B
106 where BAL_SHEET_MODEL_CODE = X_BAL_SHEET_MODEL_CODE
107 for update of BAL_SHEET_MODEL_CODE nowait;
108 recinfo c%rowtype;
109
110 cursor c1 is select
111 BAL_SHEET_MODEL_NAME,
112 DESCRIPTION,
113 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
114 from FEM_BAL_SHEET_MODELS_TL
115 where BAL_SHEET_MODEL_CODE = X_BAL_SHEET_MODEL_CODE
116 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
117 for update of BAL_SHEET_MODEL_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.BAL_SHEET_CALC_TYPE_CODE = X_BAL_SHEET_CALC_TYPE_CODE)
128 AND (recinfo.BAL_SHEET_DEF_TYPE_CODE = X_BAL_SHEET_DEF_TYPE_CODE)
129 AND (recinfo.ENABLED_FLAG = X_ENABLED_FLAG)
130 AND (recinfo.READ_ONLY_FLAG = X_READ_ONLY_FLAG)
131 AND (recinfo.PERSONAL_FLAG = X_PERSONAL_FLAG)
132 ) then
133 null;
134 else
135 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
136 app_exception.raise_exception;
137 end if;
138
139 for tlinfo in c1 loop
140 if (tlinfo.BASELANG = 'Y') then
141 if ( (tlinfo.BAL_SHEET_MODEL_NAME = X_BAL_SHEET_MODEL_NAME)
142 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
143 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
144 ) then
145 null;
146 else
147 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
148 app_exception.raise_exception;
149 end if;
150 end if;
151 end loop;
152 return;
153 end LOCK_ROW;
154
155 procedure UPDATE_ROW (
156 X_BAL_SHEET_MODEL_CODE in VARCHAR2,
157 X_BAL_SHEET_CALC_TYPE_CODE in VARCHAR2,
158 X_BAL_SHEET_DEF_TYPE_CODE in VARCHAR2,
159 X_ENABLED_FLAG in VARCHAR2,
160 X_READ_ONLY_FLAG in VARCHAR2,
161 X_PERSONAL_FLAG in VARCHAR2,
162 X_BAL_SHEET_MODEL_NAME in VARCHAR2,
163 X_DESCRIPTION in VARCHAR2,
164 X_LAST_UPDATE_DATE in DATE,
165 X_LAST_UPDATED_BY in NUMBER,
166 X_LAST_UPDATE_LOGIN in NUMBER
167 ) is
168 begin
169 update FEM_BAL_SHEET_MODELS_B set
170 BAL_SHEET_CALC_TYPE_CODE = X_BAL_SHEET_CALC_TYPE_CODE,
171 BAL_SHEET_DEF_TYPE_CODE = X_BAL_SHEET_DEF_TYPE_CODE,
172 ENABLED_FLAG = X_ENABLED_FLAG,
173 READ_ONLY_FLAG = X_READ_ONLY_FLAG,
174 PERSONAL_FLAG = X_PERSONAL_FLAG,
175 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
176 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
177 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
178 where BAL_SHEET_MODEL_CODE = X_BAL_SHEET_MODEL_CODE;
179
180 if (sql%notfound) then
181 raise no_data_found;
182 end if;
183
184 update FEM_BAL_SHEET_MODELS_TL set
185 BAL_SHEET_MODEL_NAME = X_BAL_SHEET_MODEL_NAME,
186 DESCRIPTION = X_DESCRIPTION,
187 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
188 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
189 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
190 SOURCE_LANG = userenv('LANG')
191 where BAL_SHEET_MODEL_CODE = X_BAL_SHEET_MODEL_CODE
192 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
193
194 if (sql%notfound) then
195 raise no_data_found;
196 end if;
197 end UPDATE_ROW;
198
199 procedure DELETE_ROW (
200 X_BAL_SHEET_MODEL_CODE in VARCHAR2
201 ) is
202 begin
203 delete from FEM_BAL_SHEET_MODELS_TL
204 where BAL_SHEET_MODEL_CODE = X_BAL_SHEET_MODEL_CODE;
205
206 if (sql%notfound) then
207 raise no_data_found;
208 end if;
209
210 delete from FEM_BAL_SHEET_MODELS_B
211 where BAL_SHEET_MODEL_CODE = X_BAL_SHEET_MODEL_CODE;
212
213 if (sql%notfound) then
214 raise no_data_found;
215 end if;
216 end DELETE_ROW;
217
218 procedure ADD_LANGUAGE
219 is
220 begin
221 delete from FEM_BAL_SHEET_MODELS_TL T
222 where not exists
223 (select NULL
224 from FEM_BAL_SHEET_MODELS_B B
225 where B.BAL_SHEET_MODEL_CODE = T.BAL_SHEET_MODEL_CODE
226 );
227
228 update FEM_BAL_SHEET_MODELS_TL T set (
229 BAL_SHEET_MODEL_NAME,
230 DESCRIPTION
231 ) = (select
232 B.BAL_SHEET_MODEL_NAME,
233 B.DESCRIPTION
234 from FEM_BAL_SHEET_MODELS_TL B
235 where B.BAL_SHEET_MODEL_CODE = T.BAL_SHEET_MODEL_CODE
236 and B.LANGUAGE = T.SOURCE_LANG)
237 where (
238 T.BAL_SHEET_MODEL_CODE,
239 T.LANGUAGE
240 ) in (select
241 SUBT.BAL_SHEET_MODEL_CODE,
242 SUBT.LANGUAGE
243 from FEM_BAL_SHEET_MODELS_TL SUBB, FEM_BAL_SHEET_MODELS_TL SUBT
244 where SUBB.BAL_SHEET_MODEL_CODE = SUBT.BAL_SHEET_MODEL_CODE
245 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
246 and (SUBB.BAL_SHEET_MODEL_NAME <> SUBT.BAL_SHEET_MODEL_NAME
247 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
248 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
249 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
250 ));
251
252 insert into FEM_BAL_SHEET_MODELS_TL (
253 LAST_UPDATE_LOGIN,
254 DESCRIPTION,
255 CREATION_DATE,
256 CREATED_BY,
257 LAST_UPDATED_BY,
258 LAST_UPDATE_DATE,
259 BAL_SHEET_MODEL_CODE,
260 BAL_SHEET_MODEL_NAME,
261 LANGUAGE,
262 SOURCE_LANG
263 ) select /*+ ORDERED */
264 B.LAST_UPDATE_LOGIN,
265 B.DESCRIPTION,
266 B.CREATION_DATE,
267 B.CREATED_BY,
268 B.LAST_UPDATED_BY,
269 B.LAST_UPDATE_DATE,
270 B.BAL_SHEET_MODEL_CODE,
271 B.BAL_SHEET_MODEL_NAME,
272 L.LANGUAGE_CODE,
273 B.SOURCE_LANG
274 from FEM_BAL_SHEET_MODELS_TL B, FND_LANGUAGES L
275 where L.INSTALLED_FLAG in ('I', 'B')
276 and B.LANGUAGE = userenv('LANG')
277 and not exists
278 (select NULL
279 from FEM_BAL_SHEET_MODELS_TL T
280 where T.BAL_SHEET_MODEL_CODE = B.BAL_SHEET_MODEL_CODE
281 and T.LANGUAGE = L.LANGUAGE_CODE);
282 end ADD_LANGUAGE;
283 PROCEDURE TRANSLATE_ROW(
284 x_BAL_SHEET_MODEL_CODE in varchar2,
285 x_owner in varchar2,
286 x_last_update_date in varchar2,
287 x_BAL_SHEET_MODEL_NAME in varchar2,
288 x_description in varchar2,
289 x_custom_mode in varchar2) is
290
291 owner_id number;
292 ludate date;
293 row_id varchar2(64);
294 f_luby number; -- entity owner in file
295 f_ludate date; -- entity update date in file
296 db_luby number; -- entity owner in db
297 db_ludate date; -- entity update date in db
298 begin
299
300
301 -- Translate owner to file_last_updated_by
302 f_luby := fnd_load_util.owner_id(x_owner);
303
304 -- Translate char last_update_date to date
305 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
306 begin
307 select LAST_UPDATED_BY, LAST_UPDATE_DATE
308 into db_luby, db_ludate
309 from FEM_BAL_SHEET_MODELS_TL
310 where BAL_SHEET_MODEL_CODE = x_BAL_SHEET_MODEL_CODE
311 and LANGUAGE = userenv('LANG');
312
313 -- Test for customization and version
314 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
315 db_ludate, x_custom_mode)) then
316 -- Update translations for this language
317 update FEM_BAL_SHEET_MODELS_TL set
318 BAL_SHEET_MODEL_NAME = decode(x_BAL_SHEET_MODEL_NAME,
319 fnd_load_util.null_value, null, -- Real null
320 null, x_BAL_SHEET_MODEL_NAME, -- No change
321 x_BAL_SHEET_MODEL_NAME),
322 DESCRIPTION = nvl(x_description, DESCRIPTION),
323 LAST_UPDATE_DATE = f_ludate,
324 LAST_UPDATED_BY = f_luby,
325 LAST_UPDATE_LOGIN = 0,
326 SOURCE_LANG = userenv('LANG')
327 where userenv('LANG') in (LANGUAGE, SOURCE_LANG)
328 and BAL_SHEET_MODEL_CODE = x_BAL_SHEET_MODEL_CODE;
329 end if;
330 exception
331 when no_data_found then
332 -- Do not insert missing translations, skip this row
333 null;
334 end;
335 end TRANSLATE_ROW;
336
337
338 end FEM_BAL_SHEET_MODELS_PKG;