eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.CSF_ACCESS_HOURS_PVT

Source


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