[Home] [Help]
PACKAGE BODY: APPS.CSF_MAP_ACC_HRS_PKG
Source
1 PACKAGE BODY CSF_MAP_ACC_HRS_PKG as
2 /* $Header: csfmaccb.pls 120.1 2005/11/29 17:03:15 rhungund noship $ */
3
4
5 PROCEDURE Insert_Row(
6 px_ACCESS_HOUR_MAP_ID IN OUT NOCOPY NUMBER,
7 p_CUSTOMER_ID IN NUMBER,
8 p_CUSTOMER_SITE_ID IN NUMBER,
9 p_CUSTOMER_LOCATION_ID IN NUMBER,
10 p_ACCESSHOUR_REQUIRED IN VARCHAR2,
11 p_AFTER_HOURS_FLAG IN VARCHAR2,
12 p_MONDAY_FIRST_START IN DATE,
13 p_MONDAY_FIRST_END IN DATE,
14 p_MONDAY_SECOND_START IN DATE,
15 p_MONDAY_SECOND_END IN DATE,
16 p_TUESDAY_FIRST_START IN DATE,
17 p_TUESDAY_FIRST_END IN DATE,
18 p_TUESDAY_SECOND_START IN DATE,
19 p_TUESDAY_SECOND_END IN DATE,
20 p_WEDNESDAY_FIRST_START IN DATE,
21 p_WEDNESDAY_FIRST_END IN DATE,
22 p_WEDNESDAY_SECOND_START IN DATE,
23 p_WEDNESDAY_SECOND_END IN DATE,
24 p_THURSDAY_FIRST_START IN DATE,
25 p_THURSDAY_FIRST_END IN DATE,
26 p_THURSDAY_SECOND_START IN DATE,
27 p_THURSDAY_SECOND_END IN DATE,
28 p_FRIDAY_FIRST_START IN DATE,
29 p_FRIDAY_FIRST_END IN DATE,
30 p_FRIDAY_SECOND_START IN DATE,
31 p_FRIDAY_SECOND_END IN DATE,
32 p_SATURDAY_FIRST_START IN DATE,
33 p_SATURDAY_FIRST_END IN DATE,
34 p_SATURDAY_SECOND_START IN DATE,
35 p_SATURDAY_SECOND_END IN DATE,
36 p_SUNDAY_FIRST_START IN DATE,
37 p_SUNDAY_FIRST_END IN DATE,
38 p_SUNDAY_SECOND_START IN DATE,
39 p_SUNDAY_SECOND_END IN DATE,
40 p_DESCRIPTION IN VARCHAR2,
41 X_OBJECT_VERSION_NUMBER Out NOCOPY NUMBER,
42 p_CREATED_BY IN NUMBER,
43 p_CREATION_DATE IN DATE,
44 p_LAST_UPDATED_BY IN NUMBER,
45 p_LAST_UPDATE_DATE IN DATE,
46 p_LAST_UPDATE_LOGIN IN NUMBER,
47 p_security_group_id IN NUMBER) is
48
49 l_object_version_number NUMBER := 1;
50
51 begin
52
53 insert into CSF_MAP_ACCESS_HOURS_B (
54 ACCESS_HOUR_MAP_ID,
55 CUSTOMER_ID,
56 CUSTOMER_SITE_ID,
57 CUSTOMER_LOCATION_ID,
58 ACCESSHOUR_REQUIRED,
59 AFTER_HOURS_FLAG,
60 MONDAY_FIRST_START,
61 MONDAY_FIRST_END,
62 MONDAY_SECOND_START,
63 MONDAY_SECOND_END,
64 TUESDAY_FIRST_START,
65 TUESDAY_FIRST_END,
66 TUESDAY_SECOND_START,
67 TUESDAY_SECOND_END,
68 WEDNESDAY_FIRST_START,
69 WEDNESDAY_FIRST_END,
70 WEDNESDAY_SECOND_START,
71 WEDNESDAY_SECOND_END,
72 THURSDAY_FIRST_START,
73 THURSDAY_FIRST_END,
74 THURSDAY_SECOND_START,
75 THURSDAY_SECOND_END,
76 FRIDAY_FIRST_START,
77 FRIDAY_FIRST_END,
78 FRIDAY_SECOND_START,
79 FRIDAY_SECOND_END,
80 SATURDAY_FIRST_START,
81 SATURDAY_FIRST_END,
82 SATURDAY_SECOND_START,
83 SATURDAY_SECOND_END,
84 SUNDAY_FIRST_START,
85 SUNDAY_FIRST_END,
86 SUNDAY_SECOND_START,
87 SUNDAY_SECOND_END,
88 OBJECT_VERSION_NUMBER,
89 CREATION_DATE,
90 CREATED_BY,
91 LAST_UPDATE_DATE,
92 LAST_UPDATED_BY,
93 LAST_UPDATE_LOGIN,
94 security_group_id
95 ) values (
96 px_ACCESS_HOUR_MAP_ID,
97 p_CUSTOMER_ID,
98 p_CUSTOMER_SITE_ID,
99 p_CUSTOMER_LOCATION_ID,
100 p_ACCESSHOUR_REQUIRED,
101 p_AFTER_HOURS_FLAG,
102 p_MONDAY_FIRST_START,
103 p_MONDAY_FIRST_END,
104 p_MONDAY_SECOND_START,
105 p_MONDAY_SECOND_END,
106 p_TUESDAY_FIRST_START,
107 p_TUESDAY_FIRST_END,
108 p_TUESDAY_SECOND_START,
109 p_TUESDAY_SECOND_END,
110 p_WEDNESDAY_FIRST_START,
111 p_WEDNESDAY_FIRST_END,
112 p_WEDNESDAY_SECOND_START,
113 p_WEDNESDAY_SECOND_END,
114 p_THURSDAY_FIRST_START,
115 p_THURSDAY_FIRST_END,
116 p_THURSDAY_SECOND_START,
117 p_THURSDAY_SECOND_END,
118 p_FRIDAY_FIRST_START,
119 p_FRIDAY_FIRST_END,
120 p_FRIDAY_SECOND_START,
121 p_FRIDAY_SECOND_END,
122 p_SATURDAY_FIRST_START,
123 p_SATURDAY_FIRST_END,
124 p_SATURDAY_SECOND_START,
125 p_SATURDAY_SECOND_END,
126 p_SUNDAY_FIRST_START,
127 p_SUNDAY_FIRST_END,
128 p_SUNDAY_SECOND_START,
129 p_SUNDAY_SECOND_END,
130 l_OBJECT_VERSION_NUMBER,
131 decode(P_CREATION_DATE,NULL,SYSDATE,P_CREATION_DATE),
132 P_CREATED_BY,
133 decode(P_LAST_UPDATE_DATE,NULL,SYSDATE,P_LAST_UPDATE_DATE),
134 P_LAST_UPDATED_BY,
135 P_LAST_UPDATE_LOGIN,
136 p_security_group_id
137 ) ;
138
139 insert into CSF_MAP_ACCESS_HOURS_TL (
140 ACCESS_HOUR_MAP_ID,
141 DESCRIPTION,
142 CREATED_BY,
143 CREATION_DATE,
144 LAST_UPDATED_BY,
145 LAST_UPDATE_DATE,
146 LAST_UPDATE_LOGIN,
147 security_group_id,
148 LANGUAGE,
149 SOURCE_LANG
150 ) select
151 PX_ACCESS_HOUR_MAP_ID,
152 P_DESCRIPTION,
153 P_CREATED_BY,
154 P_CREATION_DATE,
155 P_LAST_UPDATED_BY,
156 P_LAST_UPDATE_DATE,
157 P_LAST_UPDATE_LOGIN,
158 p_security_group_id,
159 L.LANGUAGE_CODE,
160 userenv('LANG')
161 from FND_LANGUAGES L
162 where L.INSTALLED_FLAG in ('I', 'B')
163 and not exists
164 (select NULL
165 from CSF_MAP_ACCESS_HOURS_TL T
166 where T.ACCESS_HOUR_MAP_ID = PX_ACCESS_HOUR_MAP_ID
167 and T.LANGUAGE = L.LANGUAGE_CODE);
168
169 X_OBJECT_VERSION_NUMBER := l_object_Version_number;
170 end INSERT_ROW;
171
172
173
174 PROCEDURE Update_Row(
175 p_ACCESS_HOUR_MAP_ID IN NUMBER,
176 p_CUSTOMER_ID IN NUMBER,
177 p_CUSTOMER_SITE_ID IN NUMBER,
178 p_CUSTOMER_LOCATION_ID IN NUMBER,
179 p_ACCESSHOUR_REQUIRED IN VARCHAR2,
180 p_AFTER_HOURS_FLAG IN VARCHAR2,
181 p_MONDAY_FIRST_START IN DATE,
182 p_MONDAY_FIRST_END IN DATE,
183 p_MONDAY_SECOND_START IN DATE,
184 p_MONDAY_SECOND_END IN DATE,
185 p_TUESDAY_FIRST_START IN DATE,
186 p_TUESDAY_FIRST_END IN DATE,
187 p_TUESDAY_SECOND_START IN DATE,
188 p_TUESDAY_SECOND_END IN DATE,
189 p_WEDNESDAY_FIRST_START IN DATE,
190 p_WEDNESDAY_FIRST_END IN DATE,
191 p_WEDNESDAY_SECOND_START IN DATE,
192 p_WEDNESDAY_SECOND_END IN DATE,
193 p_THURSDAY_FIRST_START IN DATE,
194 p_THURSDAY_FIRST_END IN DATE,
195 p_THURSDAY_SECOND_START IN DATE,
196 p_THURSDAY_SECOND_END IN DATE,
197 p_FRIDAY_FIRST_START IN DATE,
198 p_FRIDAY_FIRST_END IN DATE,
199 p_FRIDAY_SECOND_START IN DATE,
200 p_FRIDAY_SECOND_END IN DATE,
201 p_SATURDAY_FIRST_START IN DATE,
202 p_SATURDAY_FIRST_END IN DATE,
203 p_SATURDAY_SECOND_START IN DATE,
204 p_SATURDAY_SECOND_END IN DATE,
205 p_SUNDAY_FIRST_START IN DATE,
206 p_SUNDAY_FIRST_END IN DATE,
207 p_SUNDAY_SECOND_START IN DATE,
208 p_SUNDAY_SECOND_END IN DATE,
209 p_DESCRIPTION IN VARCHAR2,
210 X_OBJECT_VERSION_NUMBER Out NOCOPY NUMBER,
211 p_LAST_UPDATED_BY IN NUMBER,
212 p_LAST_UPDATE_DATE IN DATE,
213 p_LAST_UPDATE_LOGIN IN NUMBER,
214 p_security_group_id IN NUMBER) is
215
216 l_object_Version_number number;
217
218 begin
219 update CSF_MAP_ACCESS_HOURS_B set
220 CUSTOMER_ID = p_CUSTOMER_ID,
221 CUSTOMER_SITE_ID = p_CUSTOMER_SITE_ID,
222 CUSTOMER_LOCATION_ID = p_CUSTOMER_LOCATION_ID,
223 ACCESSHOUR_REQUIRED = p_ACCESSHOUR_REQUIRED,
224 AFTER_HOURS_FLAG = p_AFTER_HOURS_FLAG,
225 MONDAY_FIRST_START = p_MONDAY_FIRST_START,
226 MONDAY_FIRST_END = p_MONDAY_FIRST_END,
227 MONDAY_SECOND_START = p_MONDAY_SECOND_START,
228 MONDAY_SECOND_END = p_MONDAY_SECOND_END,
229 TUESDAY_FIRST_START = p_TUESDAY_FIRST_START,
230 TUESDAY_FIRST_END = p_TUESDAY_FIRST_END,
231 TUESDAY_SECOND_START = p_TUESDAY_SECOND_START,
232 TUESDAY_SECOND_END = p_TUESDAY_SECOND_END,
233 WEDNESDAY_FIRST_START = p_WEDNESDAY_FIRST_START,
234 WEDNESDAY_FIRST_END = p_WEDNESDAY_FIRST_END,
235 WEDNESDAY_SECOND_START = p_WEDNESDAY_SECOND_START,
236 WEDNESDAY_SECOND_END = p_WEDNESDAY_SECOND_END,
237 THURSDAY_FIRST_START = p_THURSDAY_FIRST_START,
238 THURSDAY_FIRST_END= p_THURSDAY_FIRST_END,
239 THURSDAY_SECOND_START = p_THURSDAY_SECOND_START,
240 THURSDAY_SECOND_END = p_THURSDAY_SECOND_END,
241 FRIDAY_FIRST_START = p_FRIDAY_FIRST_START,
242 FRIDAY_FIRST_END = p_FRIDAY_FIRST_END,
243 FRIDAY_SECOND_START = p_FRIDAY_SECOND_START,
244 FRIDAY_SECOND_END = p_FRIDAY_SECOND_END,
245 SATURDAY_FIRST_START = p_SATURDAY_FIRST_START,
246 SATURDAY_FIRST_END = p_SATURDAY_FIRST_END,
247 SATURDAY_SECOND_START = p_SATURDAY_SECOND_START,
248 SATURDAY_SECOND_END = p_SATURDAY_SECOND_END,
249 SUNDAY_FIRST_START = p_SUNDAY_FIRST_START,
250 SUNDAY_FIRST_END = p_SUNDAY_FIRST_END,
251 SUNDAY_SECOND_START = p_SUNDAY_SECOND_START,
252 SUNDAY_SECOND_END = p_SUNDAY_SECOND_END,
253 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
254 LAST_UPDATE_DATE = P_LAST_UPDATE_DATE,
255 LAST_UPDATED_BY = P_LAST_UPDATED_BY,
256 LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN,
257 security_group_id = p_security_group_id
258 where ACCESS_HOUR_MAP_ID = p_ACCESS_HOUR_MAP_ID
259 RETURNING OBJECT_VERSION_NUMBER INTO L_OBJECT_VERSION_NUMBER;
260
261 X_OBJECT_VERSION_NUMBER := l_object_version_number;
262
263 if (sql%notfound) then
264 raise no_data_found;
265 end if;
266
267 update CSF_MAP_ACCESS_HOURS_TL set
268 DESCRIPTION = P_DESCRIPTION,
269 LAST_UPDATE_DATE = P_LAST_UPDATE_DATE,
270 LAST_UPDATED_BY = P_LAST_UPDATED_BY,
271 LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN,
272 SOURCE_LANG = userenv('LANG')
273 where ACCESS_HOUR_MAP_ID = p_ACCESS_HOUR_MAP_ID
274 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
275
276 if (sql%notfound) then
277 raise no_data_found;
278 end if;
279 end UPDATE_ROW;
280
281
282
283
284
285 PROCEDURE Lock_Row(
286 p_ACCESS_HOUR_MAP_ID IN NUMBER,
287 P_OBJECT_VERSION_NUMBER in NUMBER) is
288
289 cursor c is select
290 OBJECT_VERSION_NUMBER
291 from CSF_MAP_ACCESS_HOURS_VL
292 where ACCESS_HOUR_MAP_ID = p_ACCESS_HOUR_MAP_ID
293 for update of ACCESS_HOUR_MAP_ID nowait;
294
295 l_object_Version_number number := 0;
296
297 begin
298 open c;
299 fetch c into l_object_Version_number;
300 if (c%notfound) then
301 close c;
302 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
303 app_exception.raise_exception;
304 end if;
305 close c;
306
307 if (l_object_version_number = P_OBJECT_VERSION_NUMBER) then
308 null;
309 else
310 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
311 app_exception.raise_exception;
312 end if;
313
314 return;
315 end LOCK_ROW;
316
317
318
319 PROCEDURE Delete_Row(
320 p_ACCESS_HOUR_MAP_ID IN NUMBER) is
321 begin
322 delete from CSF_MAP_ACCESS_HOURS_TL
323 where ACCESS_HOUR_MAP_ID = p_ACCESS_HOUR_MAP_ID;
324
325 if (sql%notfound) then
326 raise no_data_found;
327 end if;
328
329 delete from CSF_MAP_ACCESS_HOURS_B
330 where ACCESS_HOUR_MAP_ID = p_ACCESS_HOUR_MAP_ID;
331
332 if (sql%notfound) then
333 raise no_data_found;
334 end if;
335 end DELETE_ROW;
336
337
338
339 PROCEDURE ADD_LANGUAGE is
340 begin
341 delete from CSF_MAP_ACCESS_HOURS_TL T
342 where not exists
343 (select NULL
344 from CSF_MAP_ACCESS_HOURS_B B
345 where B.ACCESS_HOUR_MAP_ID = T.ACCESS_HOUR_MAP_ID
346 );
347
348 update CSF_MAP_ACCESS_HOURS_TL T set (
349 DESCRIPTION
350 ) = (select
351 B.DESCRIPTION
352 from CSF_MAP_ACCESS_HOURS_TL B
353 where B.ACCESS_HOUR_MAP_ID = T.ACCESS_HOUR_MAP_ID
354 and B.LANGUAGE = T.SOURCE_LANG)
355 where (
356 T.ACCESS_HOUR_MAP_ID,
357 T.LANGUAGE
358 ) in (select
359 SUBT.ACCESS_HOUR_MAP_ID,
360 SUBT.LANGUAGE
361 from CSF_MAP_ACCESS_HOURS_TL SUBB, CSF_MAP_ACCESS_HOURS_TL SUBT
362 where SUBB.ACCESS_HOUR_MAP_ID = SUBT.ACCESS_HOUR_MAP_ID
363 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
364 and (
365 SUBB.DESCRIPTION <> SUBT.DESCRIPTION
366 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
367 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
368 ));
369
370 insert into CSF_MAP_ACCESS_HOURS_TL (
371 DESCRIPTION,
372 CREATION_DATE,
373 CREATED_BY,
374 LAST_UPDATE_DATE,
375 LAST_UPDATED_BY,
376 LAST_UPDATE_LOGIN,
377 ACCESS_HOUR_MAP_ID,
378 security_group_id,
379 LANGUAGE,
380 SOURCE_LANG
381 ) select
382 B.DESCRIPTION,
383 B.CREATION_DATE,
384 B.CREATED_BY,
385 B.LAST_UPDATE_DATE,
386 B.LAST_UPDATED_BY,
387 B.LAST_UPDATE_LOGIN,
388 B.ACCESS_HOUR_MAP_ID,
389 B.security_group_id,
390 L.LANGUAGE_CODE,
391 B.SOURCE_LANG
392 from CSF_MAP_ACCESS_HOURS_TL B, FND_LANGUAGES L
393 where L.INSTALLED_FLAG in ('I', 'B')
394 and B.LANGUAGE = userenv('LANG')
395 and not exists
396 (select NULL
397 from CSF_MAP_ACCESS_HOURS_TL T
398 where T.ACCESS_HOUR_MAP_ID = B.ACCESS_HOUR_MAP_ID
399 and T.LANGUAGE = L.LANGUAGE_CODE);
400 end ADD_LANGUAGE;
401
402
403
404 PROCEDURE Load_Row(
405 p_ACCESS_HOUR_MAP_ID IN NUMBER,
406 p_CUSTOMER_ID IN NUMBER,
407 p_CUSTOMER_SITE_ID IN NUMBER,
408 p_CUSTOMER_LOCATION_ID IN NUMBER,
409 p_ACCESSHOUR_REQUIRED IN VARCHAR2,
410 p_AFTER_HOURS_FLAG IN VARCHAR2,
411 p_MONDAY_FIRST_START IN DATE,
412 p_MONDAY_FIRST_END IN DATE,
413 p_MONDAY_SECOND_START IN DATE,
414 p_MONDAY_SECOND_END IN DATE,
415 p_TUESDAY_FIRST_START IN DATE,
416 p_TUESDAY_FIRST_END IN DATE,
417 p_TUESDAY_SECOND_START IN DATE,
418 p_TUESDAY_SECOND_END IN DATE,
419 p_WEDNESDAY_FIRST_START IN DATE,
420 p_WEDNESDAY_FIRST_END IN DATE,
421 p_WEDNESDAY_SECOND_START IN DATE,
422 p_WEDNESDAY_SECOND_END IN DATE,
423 p_THURSDAY_FIRST_START IN DATE,
424 p_THURSDAY_FIRST_END IN DATE,
425 p_THURSDAY_SECOND_START IN DATE,
426 p_THURSDAY_SECOND_END IN DATE,
427 p_FRIDAY_FIRST_START IN DATE,
431 p_SATURDAY_FIRST_START IN DATE,
428 p_FRIDAY_FIRST_END IN DATE,
429 p_FRIDAY_SECOND_START IN DATE,
430 p_FRIDAY_SECOND_END IN DATE,
432 p_SATURDAY_FIRST_END IN DATE,
433 p_SATURDAY_SECOND_START IN DATE,
434 p_SATURDAY_SECOND_END IN DATE,
435 p_SUNDAY_FIRST_START IN DATE,
436 p_SUNDAY_FIRST_END IN DATE,
437 p_SUNDAY_SECOND_START IN DATE,
438 p_SUNDAY_SECOND_END IN DATE,
439 p_DESCRIPTION IN VARCHAR2,
440 P_OBJECT_VERSION_NUMBER IN NUMBER,
441 P_OWNER IN VARCHAR2,
442 p_CREATED_BY IN NUMBER,
443 p_CREATION_DATE IN DATE,
444 p_LAST_UPDATED_BY IN NUMBER,
445 p_LAST_UPDATE_DATE IN DATE,
446 p_LAST_UPDATE_LOGIN IN NUMBER,
447 p_security_group_id IN NUMBER)
448 IS
449
450 -- Out local variables for the update / insert row procedures.
451 lx_object_version_number NUMBER := 0;
452 l_user_id NUMBER := 0;
453
454 -- needed to be passed as the parameter value for the insert's in/out
455 -- parameter.
456 l_action_code VARCHAR2(30);
457
458 BEGIN
459
460 if ( p_owner = 'SEED' ) then
461 l_user_id := 1;
462 end if;
463
464 end Load_Row;
465
466
467
468 PROCEDURE Translate_Row( X_ACCESS_HOUR_MAP_ID in NUMBER,
469 X_DESCRIPTION in varchar2,
470 X_LAST_UPDATE_DATE in date,
471 X_LAST_UPDATE_LOGIN in number,
472 X_OWNER in varchar2)
473 is
474
475 l_user_id number;
476
477 begin
478
479 if X_OWNER = 'SEED' then
480 l_user_id := 1;
481 else
482 l_user_id := 0;
483 end if;
484
485 update CSF_MAP_ACCESS_HOURS_TL set
486 description = nvl(x_description,'none'),
487 last_update_date = nvl(x_last_update_date,sysdate),
488 last_updated_by = l_user_id,
489 last_update_login = 0,
490 source_lang = userenv('LANG')
491 where ACCESS_HOUR_MAP_ID = X_ACCESS_HOUR_MAP_ID
492 and userenv('LANG') in (LANGUAGE,SOURCE_LANG);
493
494 end TRANSLATE_ROW;
495
496
497 END CSF_MAP_ACC_HRS_PKG;
498