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