[Home] [Help]
PACKAGE BODY: APPS.PA_SCH_EXCEPT_PKG
Source
1 PACKAGE BODY PA_SCH_EXCEPT_PKG as
2 --/* $Header: PARGEXPB.pls 120.1.12000000.2 2007/03/08 10:14:27 vgovvala ship $ */
3 l_empty_tab_record EXCEPTION; -- Variable to raise the exception if the passing table of records is empty
4
5 -- This function will generate the exception id
6 FUNCTION get_nextval RETURN NUMBER
7 IS
8 l_nextval NUMBER;
9 BEGIN
10
11 SELECT pa_schedule_exceptions_s.nextval
12 INTO l_nextval
13 FROM SYS.DUAL;
14
15 RETURN(l_nextval);
16
17 EXCEPTION
18 WHEN OTHERS
19 THEN
20 RAISE;
21 END get_nextval;
22
23 -- This procedure will insert the record into the pa_schedule_exception table
24 -- Input parameters
25 -- Parameters Type Required Description
26 -- P_Sch_Except_Record_Tab SchExceptTabTyp YES It contains the exception record
27 --
28
29 PROCEDURE insert_rows ( p_sch_except_record_tab IN PA_SCHEDULE_GLOB.SchExceptTabTyp,
30 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
31 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
32 x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
33 IS
34 l_schedule_exception_id PA_PLSQL_DATATYPES.IdTabTyp;
35 l_calendar_id PA_PLSQL_DATATYPES.IdTabTyp;
36 l_assignment_id PA_PLSQL_DATATYPES.IdTabTyp;
37 l_project_id PA_PLSQL_DATATYPES.IdTabTyp;
38 l_schedule_type_code PA_PLSQL_DATATYPES.Char30TabTyp;
39 l_assignment_status_code PA_PLSQL_DATATYPES.Char30TabTyp;
40 l_exception_type_code PA_PLSQL_DATATYPES.Char30TabTyp;
41 l_duration_shift_type_code PA_PLSQL_DATATYPES.Char30TabTyp;
42 l_duration_shift_unit_code PA_PLSQL_DATATYPES.Char30TabTyp;
43 l_number_of_shift PA_PLSQL_DATATYPES.NumTabTyp;
44 l_start_date PA_PLSQL_DATATYPES.DateTabTyp;
45 l_end_date PA_PLSQL_DATATYPES.DateTabTyp;
46 l_resource_calendar_percent PA_PLSQL_DATATYPES.NumTabTyp;
47 l_non_working_day_flag PA_PLSQL_DATATYPES.Char1TabTyp;
48 l_change_hours_type_code PA_PLSQL_DATATYPES.Char30TabTyp;
49 l_change_calendar_type_code PA_PLSQL_DATATYPES.Char30TabTyp;
50 -- l_change_calendar_name PA_PLSQL_DATATYPES.Char30TabTyp;
51 l_change_calendar_id PA_PLSQL_DATATYPES.NumTabTyp;
52 l_monday_hours PA_PLSQL_DATATYPES.NumTabTyp;
53 l_tuesday_hours PA_PLSQL_DATATYPES.NumTabTyp;
54 l_wednesday_hours PA_PLSQL_DATATYPES.NumTabTyp;
55 l_thursday_hours PA_PLSQL_DATATYPES.NumTabTyp;
56 l_friday_hours PA_PLSQL_DATATYPES.NumTabTyp;
57 l_saturday_hours PA_PLSQL_DATATYPES.NumTabTyp;
58 l_sunday_hours PA_PLSQL_DATATYPES.NumTabTyp;
59
60 BEGIN
61 x_return_status := FND_API.G_RET_STS_SUCCESS;
62
63 /* Checking for the empty table of record */
64 -- IF (p_sch_except_record_tab.count = 0 ) THEN
65 -- raise l_empty_tab_record;
66 -- END IF;
67
68
69 FOR l_J IN p_sch_except_record_tab.first..p_sch_except_record_tab.last LOOP
70 l_schedule_exception_id(l_J) := get_nextval;
71 l_calendar_id(l_J) := p_sch_except_record_tab(l_J).calendar_id;
72 l_assignment_id(l_J) := p_sch_except_record_tab(l_J).assignment_id;
73 l_project_id(l_J) := p_sch_except_record_tab(l_J).project_id;
74 l_schedule_type_code(l_J) := p_sch_except_record_tab(l_J).schedule_type_code;
75 l_assignment_status_code(l_J) := p_sch_except_record_tab(l_J).assignment_status_code;
76 l_exception_type_code(l_J) := p_sch_except_record_tab(l_J).exception_type_code;
77 l_duration_shift_type_code(l_J) := p_sch_except_record_tab(l_J).duration_shift_type_code;
78 l_duration_shift_unit_code(l_J) := p_sch_except_record_tab(l_J).duration_shift_unit_code;
79 l_number_of_shift(l_J) := p_sch_except_record_tab(l_J).number_of_shift;
80 l_start_date(l_J) := trunc(p_sch_except_record_tab(l_J).start_date);
81 l_end_date(l_J) := trunc(p_sch_except_record_tab(l_J).end_date);
82 l_resource_calendar_percent(l_J) := p_sch_except_record_tab(l_J).resource_calendar_percent;
83 l_non_working_day_flag(l_J) := p_sch_except_record_tab(l_J).non_working_day_flag;
84 l_change_hours_type_code(l_J) := p_sch_except_record_tab(l_J).change_hours_type_code;
85 l_change_calendar_type_code(l_J) := p_sch_except_record_tab(l_J).change_calendar_type_code;
86 -- l_change_calendar_name(l_J) := p_sch_except_record_tab(l_J).change_calendar_name;
87 l_change_calendar_id(l_J) := p_sch_except_record_tab(l_J).change_calendar_id;
88 l_monday_hours(l_J) := p_sch_except_record_tab(l_J).monday_hours;
89 l_tuesday_hours(l_J) := p_sch_except_record_tab(l_J).tuesday_hours;
90 l_wednesday_hours(l_J) := p_sch_except_record_tab(l_J).wednesday_hours;
91 l_thursday_hours(l_J) := p_sch_except_record_tab(l_J).thursday_hours;
92 l_friday_hours(l_J) := p_sch_except_record_tab(l_J).friday_hours;
93 l_saturday_hours(l_J) := p_sch_except_record_tab(l_J).saturday_hours;
94 l_sunday_hours(l_J) := p_sch_except_record_tab(l_J).sunday_hours;
95
96 END LOOP;
97
98
99
100 FORALL l_J IN p_sch_except_record_tab.first..p_sch_except_record_tab.last
101
102 INSERT INTO PA_SCHEDULE_EXCEPTIONS
103 ( schedule_exception_id ,
104 calendar_id ,
105 assignment_id ,
106 project_id ,
107 schedule_type_code ,
108 status_code ,
109 exception_type_code ,
110 duration_shift_type_code,
111 duration_shift_unit_code,
112 number_of_shift ,
113 start_date ,
114 end_date ,
115 resource_calendar_percent,
116 non_working_day_flag ,
117 change_hours_type_code ,
118 change_calendar_type_code,
119 -- change_calendar_name ,
120 change_calendar_id ,
121 monday_hours ,
122 tuesday_hours ,
123 wednesday_hours ,
124 thursday_hours ,
125 friday_hours ,
126 saturday_hours ,
127 sunday_hours ,
128 creation_date ,
129 created_by ,
130 last_update_date ,
131 last_update_by ,
132 last_update_login)
133 VALUES
134 ( l_schedule_exception_id(l_J) ,
135 l_calendar_id(l_J) ,
136 l_assignment_id(l_J) ,
137 l_project_id(l_J) ,
138 l_schedule_type_code(l_J) ,
139 l_assignment_status_code(l_J) ,
140 l_exception_type_code(l_J) ,
141 l_duration_shift_type_code(l_J),
142 l_duration_shift_unit_code(l_J),
143 l_number_of_shift(l_J) ,
144 l_start_date(l_J) ,
145 l_end_date(l_J) ,
146 l_resource_calendar_percent(l_J) ,
147 l_non_working_day_flag(l_J) ,
148 l_change_hours_type_code(l_J) ,
149 l_change_calendar_type_code(l_J),
150 -- l_change_calendar_name(l_J) ,
151 l_change_calendar_id(l_J) ,
152 l_monday_hours(l_J) ,
153 l_tuesday_hours(l_J) ,
154 l_wednesday_hours(l_J) ,
155 l_thursday_hours(l_J) ,
156 l_friday_hours(l_J) ,
157 l_saturday_hours(l_J) ,
158 l_sunday_hours(l_J) ,
159 sysdate ,
160 fnd_global.user_id ,
161 sysdate ,
162 fnd_global.user_id ,
163 fnd_global.login_id);
164
165
166 EXCEPTION
167 WHEN l_empty_tab_record THEN
168 NULL;
169 WHEN OTHERS THEN
170 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
171 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCH_EXCEPT_PKG',
172 p_procedure_name => 'insert_rows');
173 raise;
174
175 END insert_rows;
176
177
178 -- This procedure will insert the record into the pa_schedule_exception table
179 -- Input parameters
180 -- Parameters Type Required Description
181 -- P_Calendar_Id NUMBER YES Id for that calendar which is associated to this assignmen
182 -- P_Assignment_Id NUMBER YES Assignment id of the exception records
183 -- P_Project_Id NUMBER YES project id of the associated calendar
184 -- P_Schedule_Type_Code VARCHAR2 YES It is schedule type code e.g changed hours/changed duration
185 -- P_Assignment_Status_Code VARCHAR2 YES Status of the assignment e.g OPEN/CONFIRM/PROVISIONAL
186 -- P_Exception_Type_Code VARCHAR2 YES It is exception type code e.g changed hours/changed duration
187 -- P_Start_Date DATE YES stat date of the exceptions
188 -- P_End_Date DATE YES end date of the exception
189 -- P_Resource_Calendar_Percent NUMBER YES it is the resource calendar percentage
190 -- P_Non_Working_Flag VARCHAR2 YES It is non working day flag which means should include or no
191 -- t i.e Y,N.
192 -- P_Change_Hours_Type_Code VARCHAR2 YES It is change hours type code which is used when changeing t
193 -- he hours e.g. HOURS or PERCENTAGE
194 -- P_Monday_Hours NUMBER YES No. of hours of this day
195 -- P_Tuesday_Hours NUMBER YES No. of hours of this day
196 -- P_Wednesday_Hours NUMBER YES No. of hours of this day
197 -- P_Thursday_Hours NUMBER YES No. of hours of this day
198 -- P_Friday_Hours NUMBER YES No. of hours of this day
199 -- P_Saturday_Hours NUMBER YES No. of hours of this day
200 -- P_Sunday_Hours NUMBER YES No. of hours of this day
201 --
202
203 PROCEDURE insert_rows
204 ( p_calendar_id IN Number DEFAULT NULL ,
205 p_assignment_id IN Number DEFAULT NULL ,
206 p_project_id IN Number DEFAULT NULL ,
207 p_schedule_type_code IN varchar2 ,
208 p_assignment_status_code IN varchar2 DEFAULT NULL ,
209 p_exception_type_code IN varchar2 ,
210 p_duration_shift_type_code IN varchar2 DEFAULT NULL ,
211 p_duration_shift_unit_code IN varchar2 DEFAULT NULL ,
212 p_number_of_shift IN number DEFAULT NULL ,
213 p_start_date IN date ,
214 p_end_date IN date ,
215 p_resource_calendar_percent IN Number DEFAULT NULL ,
216 p_non_working_day_flag IN varchar2 DEFAULT NULL ,
217 p_change_hours_type_code IN varchar2 DEFAULT NULL ,
218 p_change_calendar_type_code IN varchar2 DEFAULT NULL ,
219 -- p_change_calendar_name IN varchar2 DEFAULT NULL ,
220 p_change_calendar_id IN number DEFAULT NULL ,
221 p_monday_hours IN Number DEFAULT NULL ,
222 p_tuesday_hours IN Number DEFAULT NULL ,
223 p_wednesday_hours IN Number DEFAULT NULL ,
224 p_thursday_hours IN Number DEFAULT NULL ,
225 p_friday_hours IN Number DEFAULT NULL ,
226 p_saturday_hours IN Number DEFAULT NULL ,
227 p_sunday_hours IN Number DEFAULT NULL ,
228 x_exception_id OUT NOCOPY Number , --File.Sql.39 bug 4440895
229 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
230 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
231 x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
232 IS
233 l_t_schedule_exception_id NUMBER;
234 BEGIN
235 /* 1799636 following line of code was commented to enhance the performance */
236 -- l_t_schedule_exception_id := get_nextval;
237 x_return_status := FND_API.G_RET_STS_SUCCESS;
238
239 /*Bug 5854571: Added following for inserting l_t_schedule_exception_id
240 as schedule_exception_id into PA_SCHEDULE_EXCEPTIONS */
241 SELECT pa_schedule_exceptions_s.nextval
242 INTO l_t_schedule_exception_id
243 FROM dual;
244
245
246 INSERT INTO PA_SCHEDULE_EXCEPTIONS
247 ( schedule_exception_id ,
248 calendar_id ,
249 assignment_id ,
250 project_id ,
251 schedule_type_code ,
252 status_code ,
253 exception_type_code ,
254 duration_shift_type_code,
255 duration_shift_unit_code,
256 number_of_shift ,
257 start_date ,
258 end_date ,
259 resource_calendar_percent,
260 non_working_day_flag ,
261 change_hours_type_code ,
262 change_calendar_type_code,
263 -- change_calendar_name ,
264 change_calendar_id ,
265 monday_hours ,
266 tuesday_hours ,
267 wednesday_hours ,
268 thursday_hours ,
269 friday_hours ,
270 saturday_hours ,
271 sunday_hours ,
272 creation_date ,
273 created_by ,
274 last_update_date ,
275 last_update_by ,
276 last_update_login)
277 VALUES
278 (l_t_schedule_exception_id, -- removed pa_schedule_exceptions_s.nextval /*Bug 5854571*/
279 p_calendar_id ,
280 p_assignment_id ,
281 p_project_id ,
282 p_schedule_type_code ,
283 p_assignment_status_code ,
284 p_exception_type_code ,
285 p_duration_shift_type_code ,
286 p_duration_shift_unit_code ,
287 p_number_of_shift ,
288 trunc(p_start_date) ,
289 trunc(p_end_date) ,
290 p_resource_calendar_percent ,
291 p_non_working_day_flag ,
292 p_change_hours_type_code ,
293 p_change_calendar_type_code ,
294 --p_change_calendar_name ,
295 p_change_calendar_id ,
296 p_monday_hours ,
297 p_tuesday_hours ,
298 p_wednesday_hours ,
299 p_thursday_hours ,
300 p_friday_hours ,
301 p_saturday_hours ,
302 p_sunday_hours ,
303 sysdate ,
304 fnd_global.user_id ,
305 sysdate ,
306 fnd_global.user_id ,
307 fnd_global.login_id);
308
309 x_exception_id := l_t_schedule_exception_id;
310
311 EXCEPTION
312 WHEN OTHERS THEN
313 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
314 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCH_EXCEPT_PKG',
315 p_procedure_name => 'insert_rows');
316 raise;
317
318
319 END insert_rows;
320
321
322
323 -- This procedure will update the record into the pa_schedule_exception table
324 -- Input parameters
325 -- Parameters Type Required Description
326 -- P_Sch_Except_Record_Tab TABLETYPE YES It contains the exception record
327 --
328
329 PROCEDURE update_rows ( p_sch_except_record_tab IN PA_SCHEDULE_GLOB.SchExceptTabTyp,
330 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
331 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
332 x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
333 IS
334 l_exceptrowid PA_PLSQL_DATATYPES.RowidTabTyp;
335 l_schedule_exception_id PA_PLSQL_DATATYPES.IdTabTyp;
336 l_calendar_id PA_PLSQL_DATATYPES.IdTabTyp;
337 l_assignment_id PA_PLSQL_DATATYPES.IdTabTyp;
338 l_project_id PA_PLSQL_DATATYPES.IdTabTyp;
339 l_schedule_type_code PA_PLSQL_DATATYPES.Char30TabTyp;
340 l_assignment_status_code PA_PLSQL_DATATYPES.Char30TabTyp;
341 l_exception_type_code PA_PLSQL_DATATYPES.Char30TabTyp;
342 l_duration_shift_type_code PA_PLSQL_DATATYPES.Char30TabTyp;
343 l_duration_shift_unit_code PA_PLSQL_DATATYPES.Char30TabTyp;
344 l_number_of_shift PA_PLSQL_DATATYPES.NumTabTyp;
345 l_start_date PA_PLSQL_DATATYPES.DateTabTyp;
346 l_end_date PA_PLSQL_DATATYPES.DateTabTyp;
347 l_resource_calendar_percent PA_PLSQL_DATATYPES.NumTabTyp;
348 l_non_working_day_flag PA_PLSQL_DATATYPES.Char1TabTyp;
349 l_change_hours_type_code PA_PLSQL_DATATYPES.Char30TabTyp;
350 l_change_calendar_type_code PA_PLSQL_DATATYPES.Char30TabTyp;
351 -- l_change_calendar_name PA_PLSQL_DATATYPES.Char30TabTyp;
352 l_change_calendar_id PA_PLSQL_DATATYPES.NumTabTyp;
353 l_monday_hours PA_PLSQL_DATATYPES.NumTabTyp;
354 l_tuesday_hours PA_PLSQL_DATATYPES.NumTabTyp;
355 l_wednesday_hours PA_PLSQL_DATATYPES.NumTabTyp;
356 l_thursday_hours PA_PLSQL_DATATYPES.NumTabTyp;
357 l_friday_hours PA_PLSQL_DATATYPES.NumTabTyp;
358 l_saturday_hours PA_PLSQL_DATATYPES.NumTabTyp;
359 l_sunday_hours PA_PLSQL_DATATYPES.NumTabTyp;
360
361 BEGIN
362
363 x_return_status := FND_API.G_RET_STS_SUCCESS;
364
365 /* Checking for the empty table of record */
366 IF (p_sch_except_record_tab.count = 0 ) THEN
367 raise l_empty_tab_record;
368 END IF;
369
370 FOR l_J IN p_sch_except_record_tab.first..p_sch_except_record_tab.last LOOP
371 l_exceptrowid(l_j) := p_sch_except_record_tab(l_j).exceptRowid;
372 l_schedule_exception_id(l_J) := p_sch_except_record_tab(l_J).schedule_exception_id;
373 l_calendar_id(l_J) := p_sch_except_record_tab(l_J).calendar_id;
374 l_assignment_id(l_J) := p_sch_except_record_tab(l_J).assignment_id;
375 l_project_id(l_J) := p_sch_except_record_tab(l_J).project_id;
376 l_schedule_type_code(l_J) := p_sch_except_record_tab(l_J).schedule_type_code;
377 l_assignment_status_code(l_J) := p_sch_except_record_tab(l_J).assignment_status_code;
378 l_exception_type_code(l_J) := p_sch_except_record_tab(l_J).exception_type_code;
379 l_duration_shift_type_code(l_J) := p_sch_except_record_tab(l_J).duration_shift_type_code;
380 l_duration_shift_unit_code(l_J) := p_sch_except_record_tab(l_J).duration_shift_unit_code;
381 l_number_of_shift(l_J) := p_sch_except_record_tab(l_J).number_of_shift;
382 l_start_date(l_J) := trunc(p_sch_except_record_tab(l_J).start_date);
383 l_end_date(l_J) := trunc(p_sch_except_record_tab(l_J).end_date);
384 l_resource_calendar_percent(l_J) := p_sch_except_record_tab(l_J).resource_calendar_percent;
385 l_non_working_day_flag(l_J) := p_sch_except_record_tab(l_J).non_working_day_flag;
386 l_change_hours_type_code(l_J) := p_sch_except_record_tab(l_J).change_hours_type_code;
387 l_change_calendar_type_code(l_J) := p_sch_except_record_tab(l_J).change_calendar_type_code;
388 -- l_change_calendar_name(l_J) := p_sch_except_record_tab(l_J).change_calendar_name;
389 l_change_calendar_id(l_J) := p_sch_except_record_tab(l_J).change_calendar_id;
390 l_monday_hours(l_J) := p_sch_except_record_tab(l_J).monday_hours;
391 l_tuesday_hours(l_J) := p_sch_except_record_tab(l_J).tuesday_hours;
392 l_wednesday_hours(l_J) := p_sch_except_record_tab(l_J).wednesday_hours;
393 l_thursday_hours(l_J) := p_sch_except_record_tab(l_J).thursday_hours;
394 l_friday_hours(l_J) := p_sch_except_record_tab(l_J).friday_hours;
395 l_saturday_hours(l_J) := p_sch_except_record_tab(l_J).saturday_hours;
396 l_sunday_hours(l_J) := p_sch_except_record_tab(l_J).sunday_hours;
397
398 END LOOP;
399
400 FORALL l_J IN p_sch_except_record_tab.first..p_sch_except_record_tab.last
401 UPDATE PA_SCHEDULE_EXCEPTIONS
402 SET
403 calendar_id = l_calendar_id(l_J),
404 assignment_id = l_assignment_id(l_J),
405 project_id = l_project_id(l_J),
406 schedule_type_code = l_schedule_type_code(l_J),
407 status_code = l_assignment_status_code(l_J),
408 exception_type_code = l_exception_type_code(l_J),
409 duration_shift_type_code = l_duration_shift_type_code(l_J),
410 duration_shift_unit_code = l_duration_shift_unit_code(l_J),
411 number_of_shift = l_number_of_shift(l_J),
412 start_date = l_start_date(l_J),
413 end_date = l_end_date(l_J),
414 resource_calendar_percent = l_resource_calendar_percent(l_J),
415 non_working_day_flag = l_non_working_day_flag(l_J) ,
416 change_hours_type_code = l_change_hours_type_code(l_J) ,
417 change_calendar_type_code = l_change_calendar_type_code(l_J),
418 -- change_calendar_name = l_change_calendar_name(l_J),
419 change_calendar_id = l_change_calendar_id(l_J),
420 monday_hours = l_monday_hours(l_J),
421 tuesday_hours = l_tuesday_hours(l_J),
422 wednesday_hours = l_wednesday_hours(l_J),
423 thursday_hours = l_thursday_hours(l_J),
424 friday_hours = l_friday_hours(l_J),
425 saturday_hours = l_saturday_hours(l_J),
426 sunday_hours = l_sunday_hours(l_J),
427 last_update_date = sysdate,
428 last_update_by = fnd_global.user_id,
429 last_update_login = fnd_global.login_id
430 WHERE rowid = l_exceptrowid(l_J);
431
432 EXCEPTION
433 WHEN l_empty_tab_record THEN
434 NULL;
435 WHEN OTHERS THEN
436 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
437 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCH_EXCEPT_PKG',
438 p_procedure_name => 'update_rows');
439 raise;
440
441
442 END update_rows;
443
444
445 -- This procedure will update the record into the pa_schedule_exception table
446 -- Input parameters
447 -- Parameters Type Required Description
448 -- P_Exceptrowid ROWID YES Id for the exception records
449 -- P_Schedule_Exception_Id NUMBER YES Id for the schedule records
450 -- P_Calendar_Id NUMBER YES Id for that calendar which is associated to this assignmen
451 -- P_Assignment_Id NUMBER YES Assignment id of the exception records
452 -- P_Project_Id NUMBER YES project id of the associated calendar
453 -- P_Schedule_Type_Code VARCHAR2 YES It is schedule type code e.g changed hours/changed duration
454 -- P_Assignment_Status_Code VARCHAR2 YES Status of the assignment e.g OPEN/CONFIRM/PROVISIONAL
455 -- P_Exception_Type_Code VARCHAR2 YES It is exception type code e.g changed hours/changed duration
456 -- P_Start_Date DATE YES stat date of the exceptions
457 -- P_End_Date DATE YES end date of the exception
458 -- P_Resource_Calendar_Percent NUMBER YES it is the resource calendar percentage
459 -- P_Non_Working_Flag VARCHAR2 YES It is non working day flag which means should include or no
460 -- t i.e Y,N.
461 -- P_Change_Hours_Type_Code VARCHAR2 YES It is change hours type code which is used when changeing t
462 -- he hours e.g. HOURS or PERCENTAGE
463 -- P_Monday_Hours NUMBER YES No. of hours of this day
464 -- P_Tuesday_Hours NUMBER YES No. of hours of this day
465 -- P_Wednesday_Hours NUMBER YES No. of hours of this day
466 -- P_Thursday_Hours NUMBER YES No. of hours of this day
467 -- P_Friday_Hours NUMBER YES No. of hours of this day
468 -- P_Saturday_Hours NUMBER YES No. of hours of this day
469 -- P_Sunday_Hours NUMBER YES No. of hours of this day
470 --
471
472 PROCEDURE update_rows
473 ( p_exceptrowid IN rowid ,
474 p_schedule_exception_id IN NUMBER,
475 p_calendar_id IN Number DEFAULT NULL ,
476 p_assignment_id IN Number DEFAULT NULL ,
477 p_project_id IN Number DEFAULT NULL ,
478 p_schedule_type_code IN varchar2 ,
479 p_assignment_status_code IN varchar2 DEFAULT NULL ,
480 p_exception_type_code IN varchar2 ,
481 p_duration_shift_type_code IN varchar2 DEFAULT NULL ,
482 p_duration_shift_unit_code IN varchar2 DEFAULT NULL ,
483 p_number_of_shift IN number DEFAULT NULL ,
484 p_start_date IN date ,
485 p_end_date IN date ,
486 p_resource_calendar_percent IN Number DEFAULT NULL ,
487 p_non_working_day_flag IN varchar2 DEFAULT NULL ,
488 p_change_hours_type_code IN varchar2 DEFAULT NULL ,
489 p_change_calendar_type_code IN varchar2 DEFAULT NULL ,
490 -- p_change_calendar_name IN varchar2 DEFAULT NULL ,
491 p_change_calendar_id IN number DEFAULT NULL ,
492 p_monday_hours IN Number DEFAULT NULL ,
493 p_tuesday_hours IN Number DEFAULT NULL ,
494 p_wednesday_hours IN Number DEFAULT NULL ,
495 p_thursday_hours IN Number DEFAULT NULL ,
496 p_friday_hours IN Number DEFAULT NULL ,
497 p_saturday_hours IN Number DEFAULT NULL ,
498 p_sunday_hours IN Number DEFAULT NULL ,
499 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
500 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
501 x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
502 IS
503 BEGIN
504
505 x_return_status := FND_API.G_RET_STS_SUCCESS;
506
507 UPDATE PA_SCHEDULE_EXCEPTIONS
508 SET
509 calendar_id = p_calendar_id,
510 assignment_id = p_assignment_id,
511 project_id = p_project_id,
512 schedule_type_code = p_schedule_type_code,
513 status_code = p_assignment_status_code,
514 exception_type_code = p_exception_type_code,
515 duration_shift_type_code = p_duration_shift_type_code,
516 duration_shift_unit_code = p_duration_shift_unit_code,
517 number_of_shift = p_number_of_shift,
518 start_date = trunc(p_start_date),
519 end_date = trunc(p_end_date),
520 resource_calendar_percent = p_resource_calendar_percent ,
521 non_working_day_flag = p_non_working_day_flag ,
522 change_hours_type_code = p_change_hours_type_code ,
523 change_calendar_type_code = p_change_calendar_type_code,
524 -- change_calendar_name = p_change_calendar_name,
525 change_calendar_id = p_change_calendar_id,
526 monday_hours = p_monday_hours,
527 tuesday_hours = p_tuesday_hours,
528 wednesday_hours = p_wednesday_hours,
529 thursday_hours = p_thursday_hours,
530 friday_hours = p_friday_hours,
531 saturday_hours = p_saturday_hours,
532 sunday_hours = p_sunday_hours,
533 last_update_date = sysdate,
534 last_update_by = fnd_global.user_id,
535 last_update_login = fnd_global.login_id
536 WHERE rowid = p_exceptrowid;
537
538
539 EXCEPTION
540 WHEN OTHERS THEN
541 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
542 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCH_EXCEPT_PKG',
543 p_procedure_name => 'update_rows');
544 raise;
545
546 END update_rows;
547
548 -- This procedure will update the record into the pa_schedule_exception table
549 -- Input parameters
550 -- Parameters Type Required Description
551 -- P_Schedule_Exception_Id NUMBER YES Id for the schedule records
552 -- P_Calendar_Id NUMBER YES Id for that calendar which is associated to this assignmen
553 -- P_Assignment_Id NUMBER YES Assignment id of the exception records
554 -- P_Project_Id NUMBER YES project id of the associated calendar
555 -- P_Schedule_Type_Code VARCHAR2 YES It is schedule type code e.g changed hours/changed duration
556 -- P_Assignment_Status_Code VARCHAR2 YES Status of the assignment e.g OPEN/CONFIRM/PROVISIONAL
557 -- P_Exception_Type_Code VARCHAR2 YES It is exception type code e.g changed hours/changed duration
558 -- P_Start_Date DATE YES stat date of the exceptions
559 -- P_End_Date DATE YES end date of the exception
560 -- P_Resource_Calendar_Percent NUMBER YES it is the resource calendar percentage
561 -- P_Non_Working_Flag VARCHAR2 YES It is non working day flag which means should include or no
562 -- t i.e Y,N.
563 -- P_Change_Hours_Type_Code VARCHAR2 YES It is change hours type code which is used when changeing t
564 -- he hours e.g. HOURS or PERCENTAGE
565 -- P_Monday_Hours NUMBER YES No. of hours of this day
566 -- P_Tuesday_Hours NUMBER YES No. of hours of this day
567 -- P_Wednesday_Hours NUMBER YES No. of hours of this day
568 -- P_Thursday_Hours NUMBER YES No. of hours of this day
569 -- P_Friday_Hours NUMBER YES No. of hours of this day
570 -- P_Saturday_Hours NUMBER YES No. of hours of this day
571 -- P_Sunday_Hours NUMBER YES No. of hours of this day
572 --
573
574 PROCEDURE update_rows
575 ( p_schedule_exception_id IN NUMBER ,
576 p_calendar_id IN Number DEFAULT NULL ,
577 p_assignment_id IN Number DEFAULT NULL ,
578 p_project_id IN Number DEFAULT NULL ,
579 p_schedule_type_code IN varchar2 ,
580 p_assignment_status_code IN varchar2 DEFAULT NULL ,
581 p_exception_type_code IN varchar2 ,
582 p_duration_shift_type_code IN varchar2 DEFAULT NULL ,
583 p_duration_shift_unit_code IN varchar2 DEFAULT NULL ,
584 p_number_of_shift IN number DEFAULT NULL ,
585 p_start_date IN date ,
586 p_end_date IN date ,
587 p_resource_calendar_percent IN Number DEFAULT NULL ,
588 p_non_working_day_flag IN varchar2 DEFAULT NULL ,
589 p_change_hours_type_code IN varchar2 DEFAULT NULL ,
590 p_change_calendar_type_code IN varchar2 DEFAULT NULL ,
591 -- p_change_calendar_name IN varchar2 DEFAULT NULL ,
592 p_change_calendar_id IN number DEFAULT NULL ,
593 p_monday_hours IN Number DEFAULT NULL ,
594 p_tuesday_hours IN Number DEFAULT NULL ,
595 p_wednesday_hours IN Number DEFAULT NULL ,
596 p_thursday_hours IN Number DEFAULT NULL ,
597 p_friday_hours IN Number DEFAULT NULL ,
598 p_saturday_hours IN Number DEFAULT NULL ,
599 p_sunday_hours IN Number DEFAULT NULL ,
600 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
601 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
602 x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
603 IS
604 BEGIN
605
606 x_return_status := FND_API.G_RET_STS_SUCCESS;
607
608 UPDATE PA_SCHEDULE_EXCEPTIONS
609 SET
610 calendar_id = p_calendar_id,
611 assignment_id = p_assignment_id,
612 project_id = p_project_id,
613 schedule_type_code = p_schedule_type_code,
614 status_code = p_assignment_status_code,
615 exception_type_code = p_exception_type_code,
616 duration_shift_type_code = p_duration_shift_type_code,
617 duration_shift_unit_code = p_duration_shift_unit_code,
618 number_of_shift = p_number_of_shift,
619 start_date = trunc(p_start_date),
620 end_date = trunc(p_end_date),
621 resource_calendar_percent = p_resource_calendar_percent ,
622 non_working_day_flag = p_non_working_day_flag ,
623 change_hours_type_code = p_change_hours_type_code ,
624 change_calendar_type_code = p_change_calendar_type_code,
625 -- change_calendar_name = p_change_calendar_name,
626 change_calendar_id = p_change_calendar_id,
627 monday_hours = p_monday_hours,
628 tuesday_hours = p_tuesday_hours,
629 wednesday_hours = p_wednesday_hours,
630 thursday_hours = p_thursday_hours,
631 friday_hours = p_friday_hours,
632 saturday_hours = p_saturday_hours,
633 sunday_hours = p_sunday_hours,
634 last_update_date = sysdate,
635 last_update_by = fnd_global.user_id,
636 last_update_login = fnd_global.login_id
637 WHERE schedule_exception_id = p_schedule_exception_id;
638
639
640 EXCEPTION
641 WHEN OTHERS THEN
642 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
643 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCH_EXCEPT_PKG',
644 p_procedure_name => 'update_rows');
645 raise;
646
647 END update_rows;
648
649 -- This procedure will delete the records in pa_schedule_exceptions table
650 -- Input parameters
651 -- Parameters Type Required Description
652 -- P_Sch_Except_Record_Tab TABLETYPE YES It contains the exception record
653 --
654
655 PROCEDURE delete_rows ( p_sch_except_record_tab IN PA_SCHEDULE_GLOB.SchExceptTabTyp,
656 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
657 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
658 x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
659 IS
660 l_schedule_exception_id PA_PLSQL_DATATYPES.IdTabTyp;
661
662 BEGIN
663
664 x_return_status := FND_API.G_RET_STS_SUCCESS;
665
666 /* Checking for the empty table of record */
667 IF (p_sch_except_record_tab.count = 0 ) THEN
668 raise l_empty_tab_record;
669 END IF;
670
671 FOR l_J IN p_sch_except_record_tab.first..p_sch_except_record_tab.last LOOP
672 l_schedule_exception_id(l_J) := p_sch_except_record_tab(l_J).schedule_exception_id;
673
674 END LOOP;
675
676 FORALL l_J IN l_schedule_exception_id.first..l_schedule_exception_id.last
677 DELETE FROM PA_SCHEDULE_EXCEPTIONS WHERE schedule_exception_id = l_schedule_exception_id(l_J);
678
679 EXCEPTION
680 WHEN OTHERS THEN
681 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
682 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCH_EXCEPT_PKG',
683 p_procedure_name => 'delete_rows');
684 raise;
685
686 END delete_rows;
687
688 -- This procedure will delete the records in pa_schedule_exceptions table
689 -- Input parameters
690 -- Parameters Type Required Description
691 -- P_Schedule_Exception_Id NUMBER YES It schedule exception id
692 --
693
694 PROCEDURE delete_rows
695 ( p_schedule_exception_id IN Number ,
696 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
697 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
698 x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
699 IS
700 BEGIN
701
702 x_return_status := FND_API.G_RET_STS_SUCCESS;
703
704 DELETE
705 FROM PA_SCHEDULE_EXCEPTIONS
706 WHERE schedule_exception_id = p_schedule_exception_id;
707
708 EXCEPTION
709 WHEN OTHERS THEN
710 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
711 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCH_EXCEPT_PKG',
712 p_procedure_name => 'delete_rows');
713 raise;
714
715 END delete_rows;
716
717 -- This procedure will delete the records in pa_schedule_exceptions table
718 -- Input parameters
719 -- Parameters Type Required Description
720 -- P_Exceptrowid ROWID YES It exception row id
721 --
722
723 PROCEDURE delete_rows
724 ( p_exceptrowid IN rowid ,
725 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
726 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
727 x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
728 IS
729 BEGIN
730
731 x_return_status := FND_API.G_RET_STS_SUCCESS;
732
733 DELETE
734 FROM PA_SCHEDULE_EXCEPTIONS
735 WHERE rowid = p_exceptrowid;
736
737 EXCEPTION
738 WHEN OTHERS THEN
739 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
740 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCH_EXCEPT_PKG',
741 p_procedure_name => 'delete_rows');
742 raise;
743
744
745 END delete_rows;
746 END PA_SCH_EXCEPT_PKG;