[Home] [Help]
PACKAGE BODY: APPS.IEC_G_CAL_CALENDARS_PKG
Source
1 package body IEC_G_CAL_CALENDARS_PKG as
2 /* $Header: IECCCALB.pls 115.7 2003/08/22 20:41:17 hhuang noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_CALENDAR_ID in NUMBER,
6 X_CALENDAR_TYPE_CODE in VARCHAR2,
7 X_TERRITORY_CODE in VARCHAR2,
8 X_OVERRIDE_CC_CAL_FLAG in VARCHAR2,
9 X_OBJECT_VERSION_NUMBER in NUMBER,
10 X_CALENDAR_NAME in VARCHAR2,
11 X_CREATION_DATE in DATE,
12 X_CREATED_BY in NUMBER,
13 X_LAST_UPDATE_DATE in DATE,
14 X_LAST_UPDATED_BY in NUMBER,
15 X_LAST_UPDATE_LOGIN in NUMBER
16 ) is
17 cursor C is select ROWID from IEC_G_CAL_CALENDARS_B
18 where CALENDAR_ID = X_CALENDAR_ID
19 ;
20 begin
21 insert into IEC_G_CAL_CALENDARS_B (
22 CALENDAR_ID,
23 CALENDAR_TYPE_CODE,
24 TERRITORY_CODE,
25 OVERRIDE_CC_CAL_FLAG,
26 OBJECT_VERSION_NUMBER,
27 CREATION_DATE,
28 CREATED_BY,
29 LAST_UPDATE_DATE,
30 LAST_UPDATED_BY,
31 LAST_UPDATE_LOGIN
32 ) values (
33 X_CALENDAR_ID,
34 X_CALENDAR_TYPE_CODE,
35 X_TERRITORY_CODE,
36 X_OVERRIDE_CC_CAL_FLAG,
37 X_OBJECT_VERSION_NUMBER,
38 X_CREATION_DATE,
39 X_CREATED_BY,
40 X_LAST_UPDATE_DATE,
41 X_LAST_UPDATED_BY,
42 X_LAST_UPDATE_LOGIN
43 );
44
45 insert into IEC_G_CAL_CALENDARS_TL (
46 CALENDAR_ID,
47 CALENDAR_NAME,
48 CREATED_BY,
49 CREATION_DATE,
50 LAST_UPDATED_BY,
51 LAST_UPDATE_DATE,
52 LAST_UPDATE_LOGIN,
53 LANGUAGE,
54 SOURCE_LANG
55 ) select
56 X_CALENDAR_ID,
57 X_CALENDAR_NAME,
58 X_CREATED_BY,
59 X_CREATION_DATE,
60 X_LAST_UPDATED_BY,
61 X_LAST_UPDATE_DATE,
62 X_LAST_UPDATE_LOGIN,
63 L.LANGUAGE_CODE,
64 userenv('LANG')
65 from FND_LANGUAGES L
66 where L.INSTALLED_FLAG in ('I', 'B')
67 and not exists
68 (select NULL
69 from IEC_G_CAL_CALENDARS_TL T
70 where T.CALENDAR_ID = X_CALENDAR_ID
71 and T.LANGUAGE = L.LANGUAGE_CODE);
72
73 open c;
74 fetch c into X_ROWID;
75 if (c%notfound) then
76 close c;
77 raise no_data_found;
78 end if;
79 close c;
80
81 end INSERT_ROW;
82
83 procedure LOCK_ROW (
84 X_CALENDAR_ID in NUMBER,
85 X_CALENDAR_TYPE_CODE in VARCHAR2,
86 X_TERRITORY_CODE in VARCHAR2,
87 X_OVERRIDE_CC_CAL_FLAG in VARCHAR2,
88 X_OBJECT_VERSION_NUMBER in NUMBER,
89 X_CALENDAR_NAME in VARCHAR2
90 ) is
91 cursor c is select
92 CALENDAR_TYPE_CODE,
93 TERRITORY_CODE,
94 OVERRIDE_CC_CAL_FLAG,
95 OBJECT_VERSION_NUMBER
96 from IEC_G_CAL_CALENDARS_B
97 where CALENDAR_ID = X_CALENDAR_ID
98 for update of CALENDAR_ID nowait;
99 recinfo c%rowtype;
100
101 cursor c1 is select
102 CALENDAR_NAME,
103 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
104 from IEC_G_CAL_CALENDARS_TL
105 where CALENDAR_ID = X_CALENDAR_ID
106 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
107 for update of CALENDAR_ID nowait;
108 begin
109 open c;
110 fetch c into recinfo;
111 if (c%notfound) then
112 close c;
113 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
114 app_exception.raise_exception;
115 end if;
116 close c;
117 if ( (recinfo.CALENDAR_TYPE_CODE = X_CALENDAR_TYPE_CODE)
118 AND ((recinfo.TERRITORY_CODE = X_TERRITORY_CODE)
119 OR ((recinfo.TERRITORY_CODE is null) AND (X_TERRITORY_CODE is null)))
120 AND (recinfo.OVERRIDE_CC_CAL_FLAG = X_OVERRIDE_CC_CAL_FLAG)
121 AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
122 ) then
123 null;
124 else
125 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
126 app_exception.raise_exception;
127 end if;
128
129 for tlinfo in c1 loop
130 if (tlinfo.BASELANG = 'Y') then
131 if ( ((tlinfo.CALENDAR_NAME = X_CALENDAR_NAME)
132 OR ((tlinfo.CALENDAR_NAME is null) AND (X_CALENDAR_NAME is null)))
133 ) then
134 null;
135 else
136 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
137 app_exception.raise_exception;
138 end if;
139 end if;
140 end loop;
141 return;
142 end LOCK_ROW;
143
144 procedure UPDATE_ROW (
145 X_CALENDAR_ID in NUMBER,
146 X_CALENDAR_TYPE_CODE in VARCHAR2,
147 X_TERRITORY_CODE in VARCHAR2,
148 X_OVERRIDE_CC_CAL_FLAG in VARCHAR2,
149 X_OBJECT_VERSION_NUMBER in NUMBER,
150 X_CALENDAR_NAME in VARCHAR2,
151 X_LAST_UPDATE_DATE in DATE,
152 X_LAST_UPDATED_BY in NUMBER,
153 X_LAST_UPDATE_LOGIN in NUMBER
154 ) is
155 begin
156 update IEC_G_CAL_CALENDARS_B set
157 CALENDAR_TYPE_CODE = X_CALENDAR_TYPE_CODE,
158 TERRITORY_CODE = X_TERRITORY_CODE,
159 OVERRIDE_CC_CAL_FLAG = X_OVERRIDE_CC_CAL_FLAG,
160 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
161 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
162 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
163 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
164 where CALENDAR_ID = X_CALENDAR_ID;
165
166 if (sql%notfound) then
167 raise no_data_found;
168 end if;
169
170 update IEC_G_CAL_CALENDARS_TL set
171 CALENDAR_NAME = X_CALENDAR_NAME,
172 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
173 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
174 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
175 SOURCE_LANG = userenv('LANG')
176 where CALENDAR_ID = X_CALENDAR_ID
177 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
178
179 if (sql%notfound) then
180 raise no_data_found;
181 end if;
182 end UPDATE_ROW;
183
184 procedure DELETE_ROW (
185 X_CALENDAR_ID in NUMBER
186 ) is
187 begin
188 delete from IEC_G_CAL_CALENDARS_TL
189 where CALENDAR_ID = X_CALENDAR_ID;
190
191 if (sql%notfound) then
192 raise no_data_found;
193 end if;
194
195 delete from IEC_G_CAL_CALENDARS_B
196 where CALENDAR_ID = X_CALENDAR_ID;
197
198 if (sql%notfound) then
199 raise no_data_found;
200 end if;
201 end DELETE_ROW;
202
203 procedure ADD_LANGUAGE
204 is
205 begin
206 delete from IEC_G_CAL_CALENDARS_TL T
207 where not exists
208 (select NULL
209 from IEC_G_CAL_CALENDARS_B B
210 where B.CALENDAR_ID = T.CALENDAR_ID
211 );
212
213 update IEC_G_CAL_CALENDARS_TL T set (
214 CALENDAR_NAME
215 ) = (select
216 B.CALENDAR_NAME
217 from IEC_G_CAL_CALENDARS_TL B
218 where B.CALENDAR_ID = T.CALENDAR_ID
219 and B.LANGUAGE = T.SOURCE_LANG)
220 where (
221 T.CALENDAR_ID,
222 T.LANGUAGE
223 ) in (select
224 SUBT.CALENDAR_ID,
225 SUBT.LANGUAGE
226 from IEC_G_CAL_CALENDARS_TL SUBB, IEC_G_CAL_CALENDARS_TL SUBT
227 where SUBB.CALENDAR_ID = SUBT.CALENDAR_ID
228 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
229 and (SUBB.CALENDAR_NAME <> SUBT.CALENDAR_NAME
230 or (SUBB.CALENDAR_NAME is null and SUBT.CALENDAR_NAME is not null)
231 or (SUBB.CALENDAR_NAME is not null and SUBT.CALENDAR_NAME is null)
232 ));
233
234 insert into IEC_G_CAL_CALENDARS_TL (
235 CALENDAR_ID,
236 CALENDAR_NAME,
237 CREATED_BY,
238 CREATION_DATE,
239 LAST_UPDATED_BY,
240 LAST_UPDATE_DATE,
241 LAST_UPDATE_LOGIN,
242 LANGUAGE,
243 SOURCE_LANG
244 ) select
245 B.CALENDAR_ID,
246 B.CALENDAR_NAME,
247 B.CREATED_BY,
248 B.CREATION_DATE,
249 B.LAST_UPDATED_BY,
250 B.LAST_UPDATE_DATE,
251 B.LAST_UPDATE_LOGIN,
252 L.LANGUAGE_CODE,
253 B.SOURCE_LANG
254 from IEC_G_CAL_CALENDARS_TL B, FND_LANGUAGES L
255 where L.INSTALLED_FLAG in ('I', 'B')
256 and B.LANGUAGE = userenv('LANG')
257 and not exists
258 (select NULL
259 from IEC_G_CAL_CALENDARS_TL T
260 where T.CALENDAR_ID = B.CALENDAR_ID
261 and T.LANGUAGE = L.LANGUAGE_CODE);
262 end ADD_LANGUAGE;
263
264 procedure LOAD_ROW (
265 X_CALENDAR_ID in NUMBER,
266 X_CALENDAR_TYPE_CODE in VARCHAR2,
267 X_TERRITORY_CODE in VARCHAR2,
268 X_OVERRIDE_CC_CAL_FLAG in VARCHAR2,
269 X_CALENDAR_NAME in VARCHAR2,
270 X_OWNER in VARCHAR2
271 ) is
272
273 USER_ID NUMBER := 0;
274 ROW_ID VARCHAR2(500);
275
276 begin
277
278 if (X_OWNER = 'SEED') then
279 USER_ID := 1;
280 end if;
281
282 UPDATE_ROW ( X_CALENDAR_ID
283 , X_CALENDAR_TYPE_CODE
284 , X_TERRITORY_CODE
285 , X_OVERRIDE_CC_CAL_FLAG
286 , 0
287 , X_CALENDAR_NAME
288 , SYSDATE
289 , USER_ID
290 , 0);
291
292 exception
293 when no_data_found then
294 INSERT_ROW ( ROW_ID
295 , X_CALENDAR_ID
296 , X_CALENDAR_TYPE_CODE
297 , X_TERRITORY_CODE
298 , X_OVERRIDE_CC_CAL_FLAG
299 , 0
300 , X_CALENDAR_NAME
301 , SYSDATE
302 , USER_ID
303 , SYSDATE
304 , USER_ID
305 , 0);
306
307 end LOAD_ROW;
308
309 procedure TRANSLATE_ROW (
310 X_CALENDAR_ID in NUMBER,
311 X_CALENDAR_NAME in VARCHAR2,
312 X_OWNER in VARCHAR2
313 ) is
314 begin
315
316 -- only UPDATE rows that have not been altered by user
317
318 update IEC_G_CAL_CALENDARS_TL set
319 SOURCE_LANG = userenv('LANG'),
320 CALENDAR_NAME = X_CALENDAR_NAME,
321 LAST_UPDATE_DATE = SYSDATE,
322 LAST_UPDATED_BY = DECODE(X_OWNER, 'SEED', 1, 0),
323 LAST_UPDATE_LOGIN = 0
324 where CALENDAR_ID = X_CALENDAR_ID
325 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
326
327 end TRANSLATE_ROW;
328
329 end IEC_G_CAL_CALENDARS_PKG;