[Home] [Help]
PACKAGE BODY: APPS.CSF_ACCESS_HOURS_PVT
Source
1 PACKAGE BODY CSF_ACCESS_HOURS_PVT as
2 /* $Header: CSFVACHB.pls 120.1 2005/10/27 00:19:25 srengana noship $ */
3 -- Start of Comments
4 --
5 -- Package name : CSF_ACCESS_HOURS_PVT
6 -- Purpose :
7 -- History :
8 -- 17-AUG-2004 : Changed the package name from CSF_ACCESS_HOURS_PKG to CSF_ACCESS_HOURS_PVT
9 -- :
10 -- NOTE :
11 -- End of Comments
12 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSF_ACCESS_HOUR_PVT';
13 G_FILE_NAME CONSTANT VARCHAR2(12) := 'CSFVACHB.pls';
14 -- ---------------------------------
15 -- private global package variables
16 -- ---------------------------------
17 g_user_id number;
18 g_login_id number;
19 -----------------------------------
20 --public api's
21 -----------------------------------
22
23
24 PROCEDURE CREATE_ACCESS_HOURS(
25 p_API_VERSION IN NUMBER,
26 p_INIT_MSG_LIST IN VARCHAR2 ,
27 x_ACCESS_HOUR_ID OUT NOCOPY NUMBER,
28 p_TASK_ID NUMBER,
29 p_ACCESS_HOUR_REQD VARCHAR2 ,
30 p_AFTER_HOURS_FLAG VARCHAR2 ,
31 p_MONDAY_FIRST_START DATE ,
32 p_MONDAY_FIRST_END DATE ,
33 p_TUESDAY_FIRST_START DATE ,
34 p_TUESDAY_FIRST_END DATE ,
35 p_WEDNESDAY_FIRST_START DATE ,
36 p_WEDNESDAY_FIRST_END DATE ,
37 p_THURSDAY_FIRST_START DATE ,
38 p_THURSDAY_FIRST_END DATE ,
39 p_FRIDAY_FIRST_START DATE ,
40 p_FRIDAY_FIRST_END DATE ,
41 p_SATURDAY_FIRST_START DATE ,
42 p_SATURDAY_FIRST_END DATE ,
43 p_SUNDAY_FIRST_START DATE ,
44 p_SUNDAY_FIRST_END DATE ,
45 p_MONDAY_SECOND_START DATE ,
46 p_MONDAY_SECOND_END DATE ,
47 p_TUESDAY_SECOND_START DATE ,
48 p_TUESDAY_SECOND_END DATE ,
49 p_WEDNESDAY_SECOND_START DATE ,
50 p_WEDNESDAY_SECOND_END DATE ,
51 p_THURSDAY_SECOND_START DATE ,
52 p_THURSDAY_SECOND_END DATE ,
53 p_FRIDAY_SECOND_START DATE ,
54 p_FRIDAY_SECOND_END DATE ,
55 p_SATURDAY_SECOND_START DATE ,
56 p_SATURDAY_SECOND_END DATE ,
57 p_SUNDAY_SECOND_START DATE ,
58 p_SUNDAY_SECOND_END DATE ,
59 p_DESCRIPTION VARCHAR2 ,
60 px_object_version_number in out nocopy number,
61 p_CREATED_BY NUMBER ,
62 p_CREATION_DATE DATE ,
63 p_LAST_UPDATED_BY NUMBER,
64 p_LAST_UPDATE_DATE DATE ,
65 p_LAST_UPDATE_LOGIN NUMBER ,
66 p_commit in varchar2 ,
67 x_return_status OUT NOCOPY VARCHAR2,
68 x_msg_data OUT NOCOPY VARCHAR2,
69 x_msg_count OUT NOCOPY NUMBER
70 )
71
72 IS
73 CURSOR c_next_seq IS SELECT CSF_ACCESS_HOURS_B_S1.nextval FROM sys.dual;
74
75 l_api_name_full constant varchar2(50) := 'CSF_ACCESS_HOURS_PVT.CREATE_ACCESS_HOURS';
76 -- l_access_hour_rec Access_Hours_Rec_Type;
77 l_return_status varchar2(100);
78 l_msg_count NUMBER;
79 l_msg_data varchar2(1000);
80 l_temp_b rowid;
81 l_temp_tl rowid;
82
83 Cursor c_check_b is select ROWID from csf_access_hours_b where access_hour_id=x_access_hour_id;
84 Cursor c_check_tl is select ROWID from csf_access_hours_tl where access_hour_id=x_access_hour_id;
85
86
87 BEGIN
88 SAVEPOINT create_access_hours_pvt;
89 x_return_status := fnd_api.g_ret_sts_success;
90
91 /* If (px_ACCESS_HOUR_ID IS NULL) OR (px_ACCESS_HOUR_ID = FND_API.G_MISS_NUM) then*/
92 OPEN c_next_seq;
93 FETCH c_next_seq INTO x_ACCESS_HOUR_ID;
94 CLOSE c_next_seq;
95 /* End If;*/
96
97
98 if px_object_version_number is null
99 then
100 px_object_version_number := 1;
101 end if;
102
103 INSERT INTO CSF_ACCESS_HOURS_B(
104 ACCESS_HOUR_ID,
105 TASK_ID ,
106 CREATED_BY ,
107 CREATION_DATE,
108 LAST_UPDATED_BY,
109 LAST_UPDATE_DATE,
110 LAST_UPDATE_LOGIN,
111 ACCESSHOUR_REQUIRED,
112 AFTER_HOURS_FLAG,
113 MONDAY_FIRST_START,
114 MONDAY_FIRST_END,
115 TUESDAY_FIRST_START,
116 TUESDAY_FIRST_END ,
117 WEDNESDAY_FIRST_START,
118 WEDNESDAY_FIRST_END,
119 THURSDAY_FIRST_START ,
120 THURSDAY_FIRST_END ,
121 FRIDAY_FIRST_START ,
122 FRIDAY_FIRST_END ,
123 SATURDAY_FIRST_START,
124 SATURDAY_FIRST_END ,
125 SUNDAY_FIRST_START ,
126 SUNDAY_FIRST_END,
127 MONDAY_SECOND_START,
128 MONDAY_SECOND_END,
129 TUESDAY_SECOND_START,
130 TUESDAY_SECOND_END ,
131 WEDNESDAY_SECOND_START,
132 WEDNESDAY_SECOND_END,
133 THURSDAY_SECOND_START ,
134 THURSDAY_SECOND_END ,
135 FRIDAY_SECOND_START ,
136 FRIDAY_SECOND_END ,
137 SATURDAY_SECOND_START,
138 SATURDAY_SECOND_END ,
139 SUNDAY_SECOND_START ,
140 SUNDAY_SECOND_END,
141 OBJECT_VERSION_NUMBER
142
143 ) VALUES (
144 x_ACCESS_HOUR_ID,
145 p_TASK_ID,
146 fnd_global.user_id,
147 sysdate,
148 g_user_id,
149 sysdate,
150 g_login_id,
151 p_ACCESS_HOUR_REQD,
152 p_AFTER_HOURS_FLAG,
153 decode( p_MONDAY_FIRST_START,TO_DATE(NULL), TO_DATE(NULL), TO_DATE('5-01-1970 '||TO_CHAR(p_MONDAY_FIRST_START,'hh24:mi'),'DD-MM-RRRR HH24:MI:SS')),
154 decode( p_MONDAY_FIRST_END, TO_DATE(NULL), TO_DATE(NULL), TO_DATE('5-01-1970 '||to_char(p_MONDAY_FIRST_END,'hh24:mi'),'DD-MM-RRRR HH24:MI:SS')),
155 decode( p_TUESDAY_FIRST_START, TO_DATE(NULL),TO_DATE(NULL), TO_DATE('6-01-1970'||to_char(p_TUESDAY_FIRST_START,'hh24:mi:ss'),'DD-MM-RRRR HH24:MI:SS')),
156 decode( p_TUESDAY_FIRST_END, TO_DATE(NULL), TO_DATE(NULL), TO_DATE('6-01-1970'||to_char(p_TUESDAY_FIRST_END,'hh24:mi:ss'),'DD-MM-RRRR HH24:MI:SS')),
157 decode( p_WEDNESDAY_FIRST_START, TO_DATE(NULL), TO_DATE(NULL), TO_DATE('7-01-1970'||to_char(p_WEDNESDAY_FIRST_START,'hh24:mi:ss'),'DD-MM-RRRR HH24:MI:SS')),
158 decode( p_WEDNESDAY_FIRST_END, TO_DATE(NULL), TO_DATE(NULL), TO_DATE('7-01-1970'||to_char(p_WEDNESDAY_FIRST_END,'hh24:mi:ss'),'DD-MM-RRRR HH24:MI:SS')),
159 decode( p_THURSDAY_FIRST_START, TO_DATE(NULL), TO_DATE(NULL), TO_DATE('1-01-1970'||to_char(p_THURSDAY_FIRST_START,'hh24:mi:ss'),'DD-MM-RRRR HH24:MI:SS')),
160 decode( p_THURSDAY_FIRST_END, TO_DATE(NULL), TO_DATE(NULL), TO_DATE('1-01-1970'||to_char(p_THURSDAY_FIRST_END,'hh24:mi:ss'),'DD-MM-RRRR HH24:MI:SS')),
161 decode( p_FRIDAY_FIRST_START, TO_DATE(NULL), TO_DATE(NULL), TO_DATE('2-01-1970'||to_char(p_FRIDAY_FIRST_START,'hh24:mi:ss'),'DD-MM-RRRR HH24:MI:SS')),
162 decode( p_FRIDAY_FIRST_END, TO_DATE(NULL), TO_DATE(NULL), TO_DATE('2-01-1970'||to_char(p_FRIDAY_FIRST_END,'hh24:mi:ss'),'DD-MM-RRRR HH24:MI:SS')),
163 decode( p_SATURDAY_FIRST_START, TO_DATE(NULL), TO_DATE(NULL), TO_DATE('3-01-1970'||to_char(p_SATURDAY_FIRST_START,'hh24:mi:ss'),'DD-MM-RRRR HH24:MI:SS')),
164 decode( p_SATURDAY_FIRST_END, TO_DATE(NULL), TO_DATE(NULL), TO_DATE('3-01-1970'||to_char(p_SATURDAY_FIRST_END,'hh24:mi:ss'),'DD-MM-RRRR HH24:MI:SS')),
165 decode( p_SUNDAY_FIRST_START, TO_DATE(NULL), TO_DATE(NULL), TO_DATE('4-01-1970'||to_char(p_SUNDAY_FIRST_START,'hh24:mi:ss'),'DD-MM-RRRR HH24:MI:SS')),
166 decode( p_SUNDAY_FIRST_END, TO_DATE(NULL), TO_DATE(NULL), TO_DATE('4-01-1970'||to_char(p_SUNDAY_FIRST_END,'hh24:mi:ss'),'DD-MM-RRRR HH24:MI:SS')),
167 decode( p_MONDAY_SECOND_START,TO_DATE(NULL), TO_DATE(NULL), TO_DATE('5-01-1970 '||TO_CHAR(p_MONDAY_SECOND_START,'hh24:mi'),'DD-MM-RRRR HH24:MI:SS')),
168 decode( p_MONDAY_SECOND_END, TO_DATE(NULL), TO_DATE(NULL), TO_DATE('5-01-1970 '||to_char(p_MONDAY_SECOND_END,'hh24:mi'),'DD-MM-RRRR HH24:MI:SS')),
169 decode( p_TUESDAY_SECOND_START, TO_DATE(NULL),TO_DATE(NULL), TO_DATE('6-01-1970'||to_char(p_TUESDAY_SECOND_START,'hh24:mi:ss'),'DD-MM-RRRR HH24:MI:SS')),
170 decode( p_TUESDAY_SECOND_END, TO_DATE(NULL), TO_DATE(NULL), TO_DATE('6-01-1970'||to_char(p_TUESDAY_SECOND_END,'hh24:mi:ss'),'DD-MM-RRRR HH24:MI:SS')),
171 decode( p_WEDNESDAY_SECOND_START, TO_DATE(NULL), TO_DATE(NULL), TO_DATE('7-01-1970'||to_char(p_WEDNESDAY_SECOND_START,'hh24:mi:ss'),'DD-MM-RRRR HH24:MI:SS')),
172 decode( p_WEDNESDAY_SECOND_END, TO_DATE(NULL), TO_DATE(NULL), TO_DATE('7-01-1970'||to_char(p_WEDNESDAY_SECOND_END,'hh24:mi:ss'),'DD-MM-RRRR HH24:MI:SS')),
173 decode( p_THURSDAY_SECOND_START, TO_DATE(NULL), TO_DATE(NULL), TO_DATE('1-01-1970'||to_char(p_THURSDAY_SECOND_START,'hh24:mi:ss'),'DD-MM-RRRR HH24:MI:SS')),
174 decode( p_THURSDAY_SECOND_END, TO_DATE(NULL), TO_DATE(NULL), TO_DATE('1-01-1970'||to_char(p_THURSDAY_SECOND_END,'hh24:mi:ss'),'DD-MM-RRRR HH24:MI:SS')),
175 decode( p_FRIDAY_SECOND_START, TO_DATE(NULL), TO_DATE(NULL), TO_DATE('2-01-1970'||to_char(p_FRIDAY_SECOND_START,'hh24:mi:ss'),'DD-MM-RRRR HH24:MI:SS')),
176 decode( p_FRIDAY_SECOND_END, TO_DATE(NULL), TO_DATE(NULL), TO_DATE('2-01-1970'||to_char(p_FRIDAY_SECOND_END,'hh24:mi:ss'),'DD-MM-RRRR HH24:MI:SS')),
177 decode( p_SATURDAY_SECOND_START, TO_DATE(NULL), TO_DATE(NULL), TO_DATE('3-01-1970'||to_char(p_SATURDAY_SECOND_START,'hh24:mi:ss'),'DD-MM-RRRR HH24:MI:SS')),
178 decode( p_SATURDAY_SECOND_END, TO_DATE(NULL), TO_DATE(NULL), TO_DATE('3-01-1970'||to_char(p_SATURDAY_SECOND_END,'hh24:mi:ss'),'DD-MM-RRRR HH24:MI:SS')),
179 decode( p_SUNDAY_SECOND_START, TO_DATE(NULL), TO_DATE(NULL), TO_DATE('4-01-1970'||to_char(p_SUNDAY_SECOND_START,'hh24:mi:ss'),'DD-MM-RRRR HH24:MI:SS')),
180 decode( p_SUNDAY_SECOND_END, TO_DATE(NULL), TO_DATE(NULL), TO_DATE('4-01-1970'||to_char(p_SUNDAY_SECOND_END,'hh24:mi:ss'),'DD-MM-RRRR HH24:MI:SS')),
181 decode (px_OBJECT_VERSION_NUMBER,NULL,1,px_OBJECT_VERSION_NUMBER)
182
183 );
184
185 open c_check_b ;
186 fetch c_check_b into l_temp_b ;
187 If c_check_b%notfound then
188 close c_check_b;
189 raise no_data_found;
190 end if;
191 close c_check_b;
192
193
194 insert into CSF_ACCESS_HOURS_TL(
195 ACCESS_HOUR_ID,
196 DESCRIPTION,
197 CREATED_BY,
198 CREATION_DATE,
199 LAST_UPDATED_BY,
200 LAST_UPDATE_DATE,
201 LAST_UPDATE_LOGIN,
202 LANGUAGE,
203 SOURCE_LANG
204 ) select
205 x_ACCESS_HOUR_ID,
206 p_DESCRIPTION,
207 fnd_global.user_id,
208 sysdate,
209 g_user_id,
210 sysdate,
211 g_login_id,
212 L.LANGUAGE_CODE,
213 userenv('LANG')
214 from FND_LANGUAGES L
215 where l.installed_flag in ('I','B')
216 and not exists
217 (select NULL
218 from CSF_ACCESS_HOURS_TL T
219 where T.ACCESS_HOUR_ID= x_ACCESS_HOUR_ID
220 and T.LANGUAGE = L.LANGUAGE_CODE);
221
222
223 open c_check_tl ;
224 fetch c_check_tl into l_temp_tl ;
225 If c_check_tl%notfound then
226 close c_check_tl;
227 raise no_data_found;
228 end if;
229 close c_check_tl;
230
231
232
233 -- Standard check of p_commit
234 IF fnd_api.to_boolean(p_commit) THEN
235 commit work;
236 END IF;
237
238 fnd_msg_pub.count_and_get(p_count => x_msg_count,p_data => x_msg_data);
239 EXCEPTION
240
241 WHEN fnd_api.g_exc_error then
242 ROLLBACK TO create_access_hours_pub;
243 x_return_status :=fnd_api.g_ret_sts_error;
244 /*x_msg_count := l_msg_count;
245 x_msg_data := l_msg_data;*/
246 fnd_msg_pub.count_and_get(p_count => x_msg_count,p_data => x_msg_data);
247
248 When fnd_api.g_exc_unexpected_error then
249 Rollback TO create_access_hours_pvt;
250 x_return_status:= fnd_api.g_ret_sts_unexp_error;
251 fnd_msg_pub.count_and_get(p_count => x_msg_count,p_data => x_msg_data);
252
253 When others then
254 Rollback TO create_access_hours_pvt;
255 x_return_status:= fnd_api.g_ret_sts_unexp_error;
256 fnd_msg_pub.count_and_get(p_count => x_msg_count,p_data => x_msg_data);
257
258 End CREATE_ACCESS_HOURS;
259
260 PROCEDURE Update_Access_Hours(
261 p_API_VERSION IN NUMBER,
262 p_INIT_MSG_LIST IN VARCHAR2 ,
263 p_ACCESS_HOUR_ID IN NUMBER,
264 p_TASK_ID NUMBER,
265 p_ACCESS_HOUR_REQD VARCHAR2,
266 p_AFTER_HOURS_FLAG VARCHAR2 ,
267 p_MONDAY_FIRST_START DATE ,
268 p_MONDAY_FIRST_END DATE ,
269 p_TUESDAY_FIRST_START DATE ,
270 p_TUESDAY_FIRST_END DATE ,
271 p_WEDNESDAY_FIRST_START DATE ,
272 p_WEDNESDAY_FIRST_END DATE ,
273 p_THURSDAY_FIRST_START DATE,
274 p_THURSDAY_FIRST_END DATE ,
275 p_FRIDAY_FIRST_START DATE ,
276 p_FRIDAY_FIRST_END DATE,
277 p_SATURDAY_FIRST_START DATE ,
278 p_SATURDAY_FIRST_END DATE ,
279 p_SUNDAY_FIRST_START DATE ,
280 p_SUNDAY_FIRST_END DATE ,
281 p_MONDAY_SECOND_START DATE,
282 p_MONDAY_SECOND_END DATE ,
283 p_TUESDAY_SECOND_START DATE ,
284 p_TUESDAY_SECOND_END DATE ,
285 p_WEDNESDAY_SECOND_START DATE ,
286 p_WEDNESDAY_SECOND_END DATE,
287 p_THURSDAY_SECOND_START DATE,
288 p_THURSDAY_SECOND_END DATE ,
289 p_FRIDAY_SECOND_START DATE ,
290 p_FRIDAY_SECOND_END DATE ,
291 p_SATURDAY_SECOND_START DATE ,
292 p_SATURDAY_SECOND_END DATE ,
293 p_SUNDAY_SECOND_START DATE ,
294 p_SUNDAY_SECOND_END DATE,
295 p_DESCRIPTION VARCHAR2,
296 px_object_version_number in out nocopy number,
297 p_CREATED_BY NUMBER,
298 p_CREATION_DATE DATE ,
299 p_LAST_UPDATED_BY NUMBER ,
300 p_LAST_UPDATE_DATE DATE ,
301 p_LAST_UPDATE_LOGIN NUMBER ,
302 p_commit in varchar2,
303 x_return_status OUT NOCOPY VARCHAR2,
304 x_msg_data OUT NOCOPY VARCHAR2,
305 x_msg_count OUT NOCOPY NUMBER
306 )
307
308 IS
309 l_api_name_full constant varchar2(50) := 'CSF_ACCESS_HOURS_PVT.UPDATE_ACCESS_HOURS';
310 -- l_access_hour_rec Access_Hours_Rec_Type;
311 l_return_status varchar2(100);
312 l_msg_count NUMBER;
316 x_return_status := fnd_api.g_ret_sts_success;
313 l_msg_data varchar2(1000);
314 BEGIN
315
317 px_object_version_number:=px_object_version_number+1;
318 Update CSF_ACCESS_HOURS_B
319 SET
320
321 LAST_UPDATE_DATE = sysdate,
322 LAST_UPDATED_BY = g_user_id,
323 LAST_UPDATE_LOGIN = g_login_id,
324 ACCESSHOUR_REQUIRED = p_ACCESS_HOUR_REQD,
325 AFTER_HOURS_FLAG = p_AFTER_HOURS_FLAG,
326 MONDAY_FIRST_START = decode( p_MONDAY_FIRST_START,TO_DATE(NULL), TO_DATE(NULL), TO_DATE('5-01-1970 '||TO_CHAR(p_MONDAY_FIRST_START,'hh24:mi'),'DD-MM-RRRR HH24:MI:SS')),
327 MONDAY_FIRST_END = decode( p_MONDAY_FIRST_END, TO_DATE(NULL), TO_DATE(NULL), TO_DATE('5-01-1970 '||to_char(p_MONDAY_FIRST_END,'hh24:mi'),'DD-MM-RRRR HH24:MI:SS')),
328 TUESDAY_FIRST_START = decode( p_TUESDAY_FIRST_START, TO_DATE(NULL),TO_DATE(NULL), TO_DATE('6-01-1970'||to_char(p_TUESDAY_FIRST_START,'hh24:mi:ss'),'DD-MM-RRRR HH24:MI:SS')),
329 TUESDAY_FIRST_END = decode( p_TUESDAY_FIRST_END, TO_DATE(NULL), TO_DATE(NULL), TO_DATE('6-01-1970'||to_char(p_TUESDAY_FIRST_END,'hh24:mi:ss'),'DD-MM-RRRR HH24:MI:SS')),
330 WEDNESDAY_FIRST_START = decode( p_WEDNESDAY_FIRST_START, TO_DATE(NULL), TO_DATE(NULL), TO_DATE('7-01-1970'||to_char(p_WEDNESDAY_FIRST_START,'hh24:mi:ss'),'DD-MM-RRRR HH24:MI:SS')),
331 WEDNESDAY_FIRST_END = decode( p_WEDNESDAY_FIRST_END, TO_DATE(NULL), TO_DATE(NULL), TO_DATE('7-01-1970'||to_char(p_WEDNESDAY_FIRST_END,'hh24:mi:ss'),'DD-MM-RRRR HH24:MI:SS')),
332 THURSDAY_FIRST_START = decode( p_THURSDAY_FIRST_START, TO_DATE(NULL), TO_DATE(NULL), TO_DATE('1-01-1970'||to_char(p_THURSDAY_FIRST_START,'hh24:mi:ss'),'DD-MM-RRRR HH24:MI:SS')),
333 THURSDAY_FIRST_END = decode( p_THURSDAY_FIRST_END, TO_DATE(NULL), TO_DATE(NULL), TO_DATE('1-01-1970'||to_char(p_THURSDAY_FIRST_END,'hh24:mi:ss'),'DD-MM-RRRR HH24:MI:SS')),
334 FRIDAY_FIRST_START = decode( p_FRIDAY_FIRST_START, TO_DATE(NULL), TO_DATE(NULL), TO_DATE('2-01-1970'||to_char(p_FRIDAY_FIRST_START,'hh24:mi:ss'),'DD-MM-RRRR HH24:MI:SS')),
335 FRIDAY_FIRST_END = decode( p_FRIDAY_FIRST_END, TO_DATE(NULL), TO_DATE(NULL), TO_DATE('2-01-1970'||to_char(p_FRIDAY_FIRST_END,'hh24:mi:ss'),'DD-MM-RRRR HH24:MI:SS')),
336 SATURDAY_FIRST_START = decode( p_SATURDAY_FIRST_START, TO_DATE(NULL), TO_DATE(NULL), TO_DATE('3-01-1970'||to_char(p_SATURDAY_FIRST_START,'hh24:mi:ss'),'DD-MM-RRRR HH24:MI:SS')),
337 SATURDAY_FIRST_END = decode( p_SATURDAY_FIRST_END, TO_DATE(NULL), TO_DATE(NULL), TO_DATE('3-01-1970'||to_char(p_SATURDAY_FIRST_END,'hh24:mi:ss'),'DD-MM-RRRR HH24:MI:SS')),
338 SUNDAY_FIRST_START = decode( p_SUNDAY_FIRST_START, TO_DATE(NULL), TO_DATE(NULL), TO_DATE('4-01-1970'||to_char(p_SUNDAY_FIRST_START,'hh24:mi:ss'),'DD-MM-RRRR HH24:MI:SS')),
339 SUNDAY_FIRST_END = decode( p_SUNDAY_FIRST_END, TO_DATE(NULL), TO_DATE(NULL), TO_DATE('4-01-1970'||to_char(p_SUNDAY_FIRST_END,'hh24:mi:ss'),'DD-MM-RRRR HH24:MI:SS')),
340 MONDAY_SECOND_START = decode( p_MONDAY_SECOND_START,TO_DATE(NULL), TO_DATE(NULL), TO_DATE('5-01-1970 '||TO_CHAR(p_MONDAY_SECOND_START,'hh24:mi'),'DD-MM-RRRR HH24:MI:SS')),
341 MONDAY_SECOND_END = decode( p_MONDAY_SECOND_END, TO_DATE(NULL), TO_DATE(NULL), TO_DATE('5-01-1970 '||to_char(p_MONDAY_SECOND_END,'hh24:mi'),'DD-MM-RRRR HH24:MI:SS')),
342 TUESDAY_SECOND_START = decode( p_TUESDAY_SECOND_START, TO_DATE(NULL),TO_DATE(NULL), TO_DATE('6-01-1970'||to_char(p_TUESDAY_SECOND_START,'hh24:mi:ss'),'DD-MM-RRRR HH24:MI:SS')),
343 TUESDAY_SECOND_END = decode( p_TUESDAY_SECOND_END, TO_DATE(NULL), TO_DATE(NULL), TO_DATE('6-01-1970'||to_char(p_TUESDAY_SECOND_END,'hh24:mi:ss'),'DD-MM-RRRR HH24:MI:SS')),
344 WEDNESDAY_SECOND_START = decode( p_WEDNESDAY_SECOND_START, TO_DATE(NULL), TO_DATE(NULL), TO_DATE('7-01-1970'||to_char(p_WEDNESDAY_SECOND_START,'hh24:mi:ss'),'DD-MM-RRRR HH24:MI:SS')),
345 WEDNESDAY_SECOND_END = decode( p_WEDNESDAY_SECOND_END, TO_DATE(NULL), TO_DATE(NULL), TO_DATE('7-01-1970'||to_char(p_WEDNESDAY_SECOND_END,'hh24:mi:ss'),'DD-MM-RRRR HH24:MI:SS')),
346 THURSDAY_SECOND_START = decode( p_THURSDAY_SECOND_START, TO_DATE(NULL), TO_DATE(NULL), TO_DATE('1-01-1970'||to_char(p_THURSDAY_SECOND_START,'hh24:mi:ss'),'DD-MM-RRRR HH24:MI:SS')),
347 THURSDAY_SECOND_END = decode( p_THURSDAY_SECOND_END, TO_DATE(NULL), TO_DATE(NULL), TO_DATE('1-01-1970'||to_char(p_THURSDAY_SECOND_END,'hh24:mi:ss'),'DD-MM-RRRR HH24:MI:SS')),
348 FRIDAY_SECOND_START = decode( p_FRIDAY_SECOND_START, TO_DATE(NULL), TO_DATE(NULL), TO_DATE('2-01-1970'||to_char(p_FRIDAY_SECOND_START,'hh24:mi:ss'),'DD-MM-RRRR HH24:MI:SS')),
349 FRIDAY_SECOND_END = decode( p_FRIDAY_SECOND_END, TO_DATE(NULL), TO_DATE(NULL), TO_DATE('2-01-1970'||to_char(p_FRIDAY_SECOND_END,'hh24:mi:ss'),'DD-MM-RRRR HH24:MI:SS')),
350 SATURDAY_SECOND_START = decode( p_SATURDAY_SECOND_START, TO_DATE(NULL), TO_DATE(NULL), TO_DATE('3-01-1970'||to_char(p_SATURDAY_SECOND_START,'hh24:mi:ss'),'DD-MM-RRRR HH24:MI:SS')),
351 SATURDAY_SECOND_END = decode( p_SATURDAY_SECOND_END, TO_DATE(NULL), TO_DATE(NULL), TO_DATE('3-01-1970'||to_char(p_SATURDAY_SECOND_END,'hh24:mi:ss'),'DD-MM-RRRR HH24:MI:SS')),
352 SUNDAY_SECOND_START = decode( p_SUNDAY_SECOND_START, TO_DATE(NULL), TO_DATE(NULL), TO_DATE('4-01-1970'||to_char(p_SUNDAY_SECOND_START,'hh24:mi:ss'),'DD-MM-RRRR HH24:MI:SS')),
353 SUNDAY_SECOND_END = decode( p_SUNDAY_SECOND_END, TO_DATE(NULL), TO_DATE(NULL), TO_DATE('4-01-1970'||to_char(p_SUNDAY_SECOND_END,'hh24:mi:ss'),'DD-MM-RRRR HH24:MI:SS')),
354 OBJECT_VERSION_NUMBER = px_object_version_number
355 where ACCESS_HOUR_ID = p_ACCESS_HOUR_ID
356 and TASK_ID=p_TASK_ID;
357
358 If (SQL%NOTFOUND) then
359 -- RAISE NO_DATA_FOUND;
360 Raise fnd_api.g_exc_unexpected_error;
361 end if;
362
363
364 update CSF_ACCESS_HOURS_TL set
365 DESCRIPTION = p_DESCRIPTION,
366 LAST_UPDATE_DATE = sysdate,
367 LAST_UPDATED_BY = g_user_id,
368 LAST_UPDATE_LOGIN = g_login_id,
369 SOURCE_LANG = userenv('LANG')
370 where ACCESS_HOUR_ID = p_ACCESS_HOUR_ID
371 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
372
373 If (SQL%NOTFOUND) then
377
374 -- RAISE NO_DATA_FOUND;
375 Raise fnd_api.g_exc_unexpected_error;
376 end if;
378
379 -- Standard check of p_commit
380 IF fnd_api.to_boolean(p_commit) THEN
381 commit work;
382 END IF;
383 fnd_msg_pub.count_and_get(p_count => x_msg_count,p_data => x_msg_data);
384
385 EXCEPTION
386
387 WHEN fnd_api.g_exc_error then
388 --ROLLBACK TO delete_access_hours_pub;
389 x_return_status :=fnd_api.g_ret_sts_error;
390 /*x_msg_count := l_msg_count;
391 x_msg_data := l_msg_data;*/
392 fnd_msg_pub.count_and_get(p_count => x_msg_count,p_data => x_msg_data);
393
394 WHEN fnd_api.g_exc_unexpected_error then
395 --ROLLBACK TO delete_access_hours_pub;
396 x_return_status :=fnd_api.g_ret_sts_unexp_error;
397 /*x_msg_count := l_msg_count;
398 x_msg_data := l_msg_data;*/
399 fnd_msg_pub.count_and_get(p_count => x_msg_count,p_data => x_msg_data);
400
401 WHEN OTHERS then
402 --ROLLBACK TO delete_access_hours_pub;
403 x_return_status := fnd_api.g_ret_sts_unexp_error;
404 /*x_msg_count := l_msg_count;
405 x_msg_data := l_msg_data;*/
406 fnd_msg_pub.count_and_get(p_count => x_msg_count,p_data => x_msg_data);
407
408 END Update_Access_Hours;
409
410 PROCEDURE Delete_Access_Hours(
411 p_API_VERSION IN NUMBER,
412 p_INIT_MSG_LIST IN VARCHAR2 ,
413 p_ACCESS_HOUR_ID NUMBER,
414 p_commit in varchar2 ,
415 x_return_status OUT NOCOPY VARCHAR2,
416 x_msg_data OUT NOCOPY VARCHAR2,
417 x_msg_count OUT NOCOPY NUMBER
418 )
419 IS
420 l_return_status varchar2(100);
421 l_msg_count NUMBER;
422 l_msg_data varchar2(1000);
423 l_api_name_full constant varchar2(50) := 'CSF_ACCESS_HOURS_PVT.DELETE_ACCESS_HOURS';
424 BEGIN
425 x_return_status := fnd_api.g_ret_sts_success;
426 DELETE FROM CSF_ACCESS_HOURS_TL
427 WHERE ACCESS_HOUR_ID=p_ACCESS_HOUR_ID;
428 If (SQL%NOTFOUND) then
429 -- RAISE NO_DATA_FOUND;
430 Raise fnd_api.g_exc_unexpected_error;
431 end if;
432
433 DELETE FROM CSF_ACCESS_HOURS_B
434 WHERE ACCESS_HOUR_ID=p_ACCESS_HOUR_ID;
435 If (SQL%NOTFOUND) then
436 -- RAISE NO_DATA_FOUND;
437 Raise fnd_api.g_exc_unexpected_error;
438 end if;
439
440 -- Standard check of p_commit
441 IF fnd_api.to_boolean(p_commit) THEN
442 commit work;
443 END IF;
444
445 fnd_msg_pub.count_and_get(p_count => x_msg_count,p_data => x_msg_data);
446
447 EXCEPTION
448
449 WHEN fnd_api.g_exc_error then
450 --ROLLBACK TO delete_access_hours_pub;
451 x_return_status := fnd_api.g_ret_sts_error;
452 /*x_msg_count := l_msg_count;
453 x_msg_data := l_msg_data;*/
454 fnd_msg_pub.count_and_get(p_count => x_msg_count,p_data => x_msg_data);
455
456 WHEN fnd_api.g_exc_unexpected_error then
457 --ROLLBACK TO delete_access_hours_pub;
458 x_return_status :=fnd_api.g_ret_sts_unexp_error;
459 /*x_msg_count := l_msg_count;
460 x_msg_data := l_msg_data;*/
461 fnd_msg_pub.count_and_get(p_count => x_msg_count,p_data => x_msg_data);
462
463 WHEN OTHERS then
464 --ROLLBACK TO delete_access_hours_pub;
465 x_return_status := fnd_api.g_ret_sts_unexp_error;
466 /*x_msg_count := l_msg_count;
467 x_msg_data := l_msg_data;*/
468 fnd_msg_pub.count_and_get(p_count => x_msg_count,p_data => x_msg_data);
469
470 END Delete_Access_Hours;
471
472
473 PROCEDURE lock_Access_hours
474 (
475 p_API_VERSION IN NUMBER,
476 p_INIT_MSG_LIST IN VARCHAR2 default NULL,
477 p_access_hour_id in number
478 , p_object_version_number in number,
479 x_return_status OUT NOCOPY VARCHAR2,
480 x_msg_data OUT NOCOPY VARCHAR2,
481 x_msg_count OUT NOCOPY NUMBER
482 )
483
484
485
486 IS
487 cursor c_ovn
488 is
489 select object_version_number
490 from csf_access_hours_b
491 where access_hour_id = p_access_hour_id
492 for update of access_hour_id nowait;
493
494 l_rec c_ovn%rowtype;
495
496
497
498 BEGIN
499
500 x_return_status := fnd_api.g_ret_sts_success;
501 open c_ovn;
502 fetch c_ovn into l_rec;
503 if c_ovn%notfound
504 then
505 close c_ovn;
506 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
507 app_exception.raise_exception;
508 end if;
509 close c_ovn;
510
511 if l_rec.object_version_number = p_object_version_number
512 then
513 null;
514 else
515 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
516 app_exception.raise_exception;
517 end if;
518
519 fnd_msg_pub.count_and_get(p_count => x_msg_count,p_data => x_msg_data);
520
521 EXCEPTION
522
523 WHEN fnd_api.g_exc_error then
524 --ROLLBACK TO delete_access_hours_pub;
525 x_return_status :=fnd_api.g_ret_sts_error;
526 /*x_msg_count := l_msg_count;
527 x_msg_data := l_msg_data;*/
528 fnd_msg_pub.count_and_get(p_count => x_msg_count,p_data => x_msg_data);
529
530 WHEN fnd_api.g_exc_unexpected_error then
531 --ROLLBACK TO delete_access_hours_pub;
532 x_return_status :=fnd_api.g_ret_sts_unexp_error;
533 /*x_msg_count := l_msg_count;
534 x_msg_data := l_msg_data;*/
535 fnd_msg_pub.count_and_get(p_count => x_msg_count,p_data => x_msg_data);
536
537 WHEN OTHERS then
541 x_msg_data := l_msg_data;*/
538 --ROLLBACK TO delete_access_hours_pub;
539 x_return_status := fnd_api.g_ret_sts_unexp_error;
540 /*x_msg_count := l_msg_count;
542 fnd_msg_pub.count_and_get(p_count => x_msg_count,p_data => x_msg_data);
543 END lock_Access_Hours;
544
545
546 BEGIN
547 -- ADD SESSION INFO
548 g_user_id := fnd_global.user_id;
549 g_login_id := fnd_global.login_id;
550
551 END CSF_ACCESS_HOURS_PVT;