1 PACKAGE BODY PA_SCHEDULE_PKG as
2 --/* $Header: PARGSCHB.pls 120.5 2007/11/05 11:03:57 rthumma ship $ */
3
4 l_empty_tab_record EXCEPTION; -- Variable to raise the exception if the passing table of records is empty
5
6 -- This function will generate the schedule id
7 FUNCTION get_nextval RETURN NUMBER
8 IS
9 l_nextval NUMBER;
10 BEGIN
11
12 SELECT pa_schedules_s.nextval
13 INTO l_nextval
14 FROM SYS.DUAL;
15
16 RETURN(l_nextval);
17
18 EXCEPTION
19 WHEN OTHERS
20 THEN
21 RAISE;
22 END get_nextval;
23
24 -- This procedure will insert the record in pa_schedules table
25 -- Input parameters
26 -- Parameters Type Required Description
27 -- P_Sch_Record_Tab ScheduleTabTyp YES It contains the schedule record
28 --
29 --Bug 5126919: Added parameter p_total_hours. This will contain the total hours for
30 --which the schedule should be created. This will be used to make sure that schedule is created
31 --correctly (for the whole p_total_hours) even after rounding.
32 PROCEDURE insert_rows ( p_sch_record_tab IN PA_SCHEDULE_GLOB.ScheduleTabTyp,
33 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
34 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
35 x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
36 p_total_hours IN NUMBER DEFAULT NULL) --Bug 5126919
37 IS
38 l_schedule_id PA_PLSQL_DATATYPES.IdTabTyp;
39 l_calendar_id PA_PLSQL_DATATYPES.IdTabTyp;
40 l_assignment_id PA_PLSQL_DATATYPES.IdTabTyp;
41 l_project_id PA_PLSQL_DATATYPES.IdTabTyp;
42 l_schedule_type_code PA_PLSQL_DATATYPES.Char30TabTyp;
43 l_assignment_status_code PA_PLSQL_DATATYPES.Char30TabTyp;
44 l_start_date PA_PLSQL_DATATYPES.DateTabTyp;
45 l_end_date PA_PLSQL_DATATYPES.DateTabTyp;
46 l_monday_hours PA_PLSQL_DATATYPES.NumTabTyp;
47 l_tuesday_hours PA_PLSQL_DATATYPES.NumTabTyp;
48 l_wednesday_hours PA_PLSQL_DATATYPES.NumTabTyp;
49 l_thursday_hours PA_PLSQL_DATATYPES.NumTabTyp;
50 l_friday_hours PA_PLSQL_DATATYPES.NumTabTyp;
51 l_saturday_hours PA_PLSQL_DATATYPES.NumTabTyp;
52 l_sunday_hours PA_PLSQL_DATATYPES.NumTabTyp;
53
54 --Bug 5126919
55 l_rounded_total NUMBER;
56 l_last_rec_index NUMBER;
57 l_adj_day VARCHAR2(10);
58 l_adj_date DATE;
59 l_adj_hours NUMBER;
60 l_multiple_day_instances_flag VARCHAR2(1);
61 l_mon_hrs_in_new_rec NUMBER;
62 l_tue_hrs_in_new_rec NUMBER;
63 l_wed_hrs_in_new_rec NUMBER;
64 l_thu_hrs_in_new_rec NUMBER;
65 l_fri_hrs_in_new_rec NUMBER;
66 l_sat_hrs_in_new_rec NUMBER;
67 l_sun_hrs_in_new_rec NUMBER;
68 l_new_rec_start_date DATE;
69 l_new_rec_end_date DATE;
70 l_temp NUMBER ;
71 l_temp_date DATE;
72 l_temp_day VARCHAR2(10);
73
74 --Added for bug Bug 5684828
75 l_sch_except_rec PA_SCHEDULE_GLOB.SchExceptRecord;
76 l_out_sch_rec_tab PA_SCHEDULE_GLOB.ScheduleTabTyp;
77 l_x_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS ;
78
79 --Added for bug 5856987
80 K NUMBER;
81
82
83 BEGIN
84
85 x_return_status := FND_API.G_RET_STS_SUCCESS;
86
87 /* Checking for the empty table of record */
88 IF (p_sch_record_tab.count = 0 ) THEN
89 PA_SCHEDULE_UTILS.log_message(1,'count 0 ... before return ... ');
90 raise l_empty_tab_record;
91 END IF;
92
93 PA_SCHEDULE_UTILS.log_message(1,'start of the schedule inser row .... ');
94
95 l_rounded_total := 0; --Bug 5126919
96
97 FOR J IN p_sch_record_tab.first..p_sch_record_tab.last LOOP
98 l_schedule_id(J) := get_nextval;
99 l_calendar_id(J) := p_sch_record_tab(J).calendar_id;
100 l_assignment_id(J) := p_sch_record_tab(J).assignment_id;
101 l_project_id(J) := p_sch_record_tab(J).project_id;
102 l_schedule_type_code(J) := p_sch_record_tab(J).schedule_type_code;
103 l_assignment_status_code(J) := p_sch_record_tab(J).assignment_status_code;
104 l_start_date(J) := trunc(p_sch_record_tab(J).start_date);
105 l_end_date(J) := trunc(p_sch_record_tab(J).end_date);
106 l_monday_hours(J) := trunc(p_sch_record_tab(J).monday_hours, 2);
107 l_tuesday_hours(J) := trunc(p_sch_record_tab(J).tuesday_hours, 2);
108 l_wednesday_hours(J) := trunc(p_sch_record_tab(J).wednesday_hours, 2);
109 l_thursday_hours(J) := trunc(p_sch_record_tab(J).thursday_hours, 2);
110 l_friday_hours(J) := trunc(p_sch_record_tab(J).friday_hours, 2);
111 l_saturday_hours(J) := trunc(p_sch_record_tab(J).saturday_hours, 2);
112 l_sunday_hours(J) := trunc(p_sch_record_tab(J).sunday_hours, 2);
113
114 --Bug 5126919: The below block will be used to find out the total hours that this schedule will contain,
115 --with the hours derived after rounding as above.
116 l_temp_date := l_start_date(J);
117 l_temp_day := to_char(l_temp_date,'DY','NLS_DATE_LANGUAGE = AMERICAN');
118 LOOP
119
120 IF l_temp_day ='MON' THEN
121
122 l_rounded_total := l_rounded_total + l_monday_hours(J);
123
124 END IF;
125
126 IF l_temp_day ='TUE' THEN
127
128 l_rounded_total := l_rounded_total + l_tuesday_hours(J);
129
130 END IF;
131
132 IF l_temp_day ='WED' THEN
133
134 l_rounded_total := l_rounded_total + l_wednesday_hours(J);
135
136 END IF;
137
138 IF l_temp_day ='THU' THEN
139
140 l_rounded_total := l_rounded_total + l_thursday_hours(J);
141
142 END IF;
143
144 IF l_temp_day ='FRI' THEN
145
146 l_rounded_total := l_rounded_total + l_friday_hours(J);
147
148 END IF;
149
150 IF l_temp_day ='SAT' THEN
151
152 l_rounded_total := l_rounded_total + l_saturday_hours(J);
153
154 END IF;
155
156 IF l_temp_day ='SUN' THEN
157
158 l_rounded_total := l_rounded_total + l_sunday_hours(J);
159
160 END IF;
161
162 EXIT WHEN l_temp_date = l_end_date(J);
163 l_temp_date := l_temp_date + 1;
164 l_temp_day := to_char(l_temp_date,'DY','NLS_DATE_LANGUAGE = AMERICAN');
165
166 END LOOP;
167
168 END LOOP;
169
170 --Bug 5126919. If rounded total is not same as the total for which the schedule should be created then
171 ----1.Find out the last record in the schedule that contains atleast one day having non-zero hours.Going forward
172 ----lets point to this record as last_record and the day as last_day (i.e. monday for example) and
173 ----date of last_day as last_date
174 ----2.If the last_record spans for only one day then the difference in rounded total and actual total will
175 ----be accomodated in the appropriate day of that record
176 ----3.If the last record spans for more than one day then a new record will be created to accomodate the difference.
177 ------For new record start_date will be last_date and end_date will be end_date of last_record. For
178 ------last_record the end date will be changed to a date which is last_date-1. New record
179 ------will contain the hours in the last_day of last_record + (difference of rounded total and actual total)
180 ------3.1 If last_day is repeated more than once in the last_record (i.e. for example if last_day is monday and if
181 ------ it occurs more than once in last_record's duration) then the hours in the last_day of last_record will
182 ------ be retained. If it is not repeated then the hours of the last_day of last_record will be zeroed out
183 ------ since no date in that duration will fall on that day (for example if the last_date falls on monday
184 ------ and none of the other dates in the last_record is monday)
185 IF NVL(p_total_hours,l_rounded_total) <> l_rounded_total THEN
186
187 l_last_rec_index:=p_sch_record_tab.last;
188 LOOP
189 EXIT WHEN ( nvl(l_last_rec_index,0) < 1 ); -- Added for Bug 6154177 , as have to check l_last_rec_index to be positive.
190 EXIT WHEN l_monday_hours(l_last_rec_index) <> 0 OR
191 l_tuesday_hours(l_last_rec_index) <> 0 OR
192 l_wednesday_hours(l_last_rec_index) <> 0 OR
193 l_thursday_hours(l_last_rec_index) <> 0 OR
194 l_friday_hours(l_last_rec_index) <> 0 OR
195 l_saturday_hours(l_last_rec_index) <> 0 OR
196 l_sunday_hours(l_last_rec_index) <> 0 ;
197
198 l_last_rec_index:=l_last_rec_index-1;
199 --Note that atleast one set of records should have a non-zero value because the control
200 --will not come here otherwise (i.e. p_total_hours will be same as l_rounded_total)
201
202 END LOOP;
203
204 /*Added for Bug 6154177: The above loop may make l_last_rec_index = 0 if the schedule has just one record */
205 IF (l_last_rec_index = 0) THEN
206 l_last_rec_index := l_last_rec_index + 1;
207 END IF ;
208 /*End for Bug 6154177 */
209
210 l_mon_hrs_in_new_rec := 0;
211 l_tue_hrs_in_new_rec := 0;
212 l_wed_hrs_in_new_rec := 0;
213 l_thu_hrs_in_new_rec := 0;
214 l_fri_hrs_in_new_rec := 0;
215 l_sat_hrs_in_new_rec := 0;
216 l_sun_hrs_in_new_rec := 0;
217 l_adj_date := l_end_date(l_last_rec_index);
218 l_adj_day := to_char(l_adj_date,'DY','NLS_DATE_LANGUAGE = AMERICAN');
219
220 LOOP
221
222 --If l_adj_date is the last date on which hours exist then a check should be made if the
223 --the value for this day in the record should be zeored out or not. The value should be zeroed out
224 --if the day occurs only once in the period duration.
225 IF l_start_date(l_last_rec_index) <= (l_adj_date-7) THEN
226
227 l_multiple_day_instances_flag := 'Y';
228
229 END IF;
230
231 IF l_adj_day ='MON' THEN
232
233 IF l_monday_hours(l_last_rec_index) <> 0 THEN
234
235 l_mon_hrs_in_new_rec := l_monday_hours(l_last_rec_index) + trunc((p_total_hours-l_rounded_total),2);
236 IF l_multiple_day_instances_flag = 'N' THEN
237
238 l_monday_hours(l_last_rec_index):= 0;
239
240 END IF;
241
242 EXIT;
243
244 END IF;
245
246 END IF;
247
248 IF l_adj_day ='TUE' THEN
249
250 IF l_tuesday_hours(l_last_rec_index) <> 0 THEN
251
252 l_tue_hrs_in_new_rec := l_tuesday_hours(l_last_rec_index) + trunc((p_total_hours-l_rounded_total),2);
253 IF l_multiple_day_instances_flag = 'N' THEN
254
255 l_tuesday_hours(l_last_rec_index):= 0;
256
257 END IF;
258
259 EXIT;
260
261 END IF;
262
263 END IF;
264
265 IF l_adj_day ='WED' THEN
266
267 IF l_wednesday_hours(l_last_rec_index) <> 0 THEN
268
269 l_wed_hrs_in_new_rec := l_wednesday_hours(l_last_rec_index) + trunc((p_total_hours-l_rounded_total),2);
270 IF l_multiple_day_instances_flag = 'N' THEN
271
272 l_wednesday_hours(l_last_rec_index):= 0;
273
274 END IF;
275
276 EXIT;
277
278 END IF;
279
280 END IF;
281
282 IF l_adj_day ='THU' THEN
283
284 IF l_thursday_hours(l_last_rec_index) <> 0 THEN
285
286 l_thu_hrs_in_new_rec := l_thursday_hours(l_last_rec_index) + trunc((p_total_hours-l_rounded_total),2);
287 IF l_multiple_day_instances_flag = 'N' THEN
288
289 l_thursday_hours(l_last_rec_index):= 0;
290
291 END IF;
292
293 EXIT;
294
295 END IF;
296
297 END IF;
298
299 IF l_adj_day ='FRI' THEN
300
301 IF l_friday_hours(l_last_rec_index) <> 0 THEN
302
303 l_fri_hrs_in_new_rec := l_friday_hours(l_last_rec_index) + trunc((p_total_hours-l_rounded_total),2);
304 IF l_multiple_day_instances_flag = 'N' THEN
305
306 l_friday_hours(l_last_rec_index):= 0;
307
308 END IF;
309
310 EXIT;
311
312 END IF;
313
314 END IF;
315
316 IF l_adj_day ='SAT' THEN
317
318 IF l_saturday_hours(l_last_rec_index) <> 0 THEN
319
320 l_sat_hrs_in_new_rec := l_saturday_hours(l_last_rec_index) + trunc((p_total_hours-l_rounded_total),2);
321 IF l_multiple_day_instances_flag = 'N' THEN
322
323 l_saturday_hours(l_last_rec_index):= 0;
324
325 END IF;
326
327 EXIT;
328
329 END IF;
330
331 END IF;
332
333 IF l_adj_day ='SUN' THEN
334
335 IF l_sunday_hours(l_last_rec_index) <> 0 THEN
336
337 l_sun_hrs_in_new_rec := l_sunday_hours(l_last_rec_index) + trunc((p_total_hours-l_rounded_total),2);
338 IF l_multiple_day_instances_flag = 'N' THEN
339
340 l_sunday_hours(l_last_rec_index):= 0;
341
342 END IF;
343
344 EXIT;
345
346 END IF;
347
348 END IF;
349
350 l_adj_date := l_adj_date-1;
351 l_adj_day := to_char(l_adj_date,'DY','NLS_DATE_LANGUAGE = AMERICAN');
352 --Note that l_adj_date can not get set to a date which is before l_start_date(l_last_rec_index)
353 --since one of 7-day hours is non-zero
354
355 /*Added this exit cond for Bug 6154177: The above loop should not go beyond l_adj_date < l_start_date(l_last_rec_index) */
356 EXIT WHEN (l_adj_date < l_start_date(l_last_rec_index));
357
358 END LOOP;
359 --Note that after the above loop only of l_(mon-sun)_hrs_in_new_rec will contain a non-zero value
360 --and all others will contan zeroes
361
362 /*Added for Bug 6154177: The above loop may make l_adj_date < l_start_date(l_last_rec_index)*/
363 IF (l_adj_date < l_start_date(l_last_rec_index)) THEN
364 l_adj_date := l_adj_date + 1;
365 END IF ;
366 /*End for Bug 6154177 */
367
368 /*Commented for bug 5684828
369
370 l_new_rec_start_date := l_adj_date;
371 l_new_rec_end_date := l_end_date(l_last_rec_index);
372
373 --Change the end date of the last record with non-zero hours if the period duration is for more than one day
374 --Update the same record if the duration is only for one day.
375 IF l_end_date(l_last_rec_index) - l_start_date(l_last_rec_index) > 0 THEN
376
377 l_end_date(l_last_rec_index) := l_new_rec_start_date -1;
378
379 --Create new record to accomodate rounding difference
380 l_temp := l_schedule_id.last + 1;
381 l_schedule_id(l_temp) := get_nextval;
382 l_calendar_id(l_temp) := p_sch_record_tab(l_last_rec_index).calendar_id;
383 l_assignment_id(l_temp) := p_sch_record_tab(l_last_rec_index).assignment_id;
384 l_project_id(l_temp) := p_sch_record_tab(l_last_rec_index).project_id;
385 l_schedule_type_code(l_temp) := p_sch_record_tab(l_last_rec_index).schedule_type_code;
386 l_assignment_status_code(l_temp) := p_sch_record_tab(l_last_rec_index).assignment_status_code;
387 l_start_date(l_temp) := l_new_rec_start_date;
388 l_end_date(l_temp) := l_new_rec_end_date;
389 l_monday_hours(l_temp) := l_mon_hrs_in_new_rec;
390 l_tuesday_hours(l_temp) := l_tue_hrs_in_new_rec;
391 l_wednesday_hours(l_temp) := l_wed_hrs_in_new_rec;
392 l_thursday_hours(l_temp) := l_thu_hrs_in_new_rec;
393 l_friday_hours(l_temp) := l_fri_hrs_in_new_rec;
394 l_saturday_hours(l_temp) := l_sat_hrs_in_new_rec;
395 l_sunday_hours(l_temp) := l_sun_hrs_in_new_rec;
396
397 ELSE
398
399 --Update the above found record so as to accomodate rounding difference
400 l_temp := l_last_rec_index;
401 l_monday_hours(l_temp) := l_mon_hrs_in_new_rec;
402 l_tuesday_hours(l_temp) := l_tue_hrs_in_new_rec;
403 l_wednesday_hours(l_temp) := l_wed_hrs_in_new_rec;
404 l_thursday_hours(l_temp) := l_thu_hrs_in_new_rec;
405 l_friday_hours(l_temp) := l_fri_hrs_in_new_rec;
406 l_saturday_hours(l_temp) := l_sat_hrs_in_new_rec;
407 l_sunday_hours(l_temp) := l_sun_hrs_in_new_rec;
408
409 END IF;
410 End of Commented for bug 5684828
411 */
412
413 --- Added below code for bug 5684828
414
415 --Setting the dates to adjustment date
416 l_new_rec_start_date := l_adj_date;
417 l_new_rec_end_date := l_adj_date; --5684828
418
419 --Copying the basic schedule information into exception record
420 l_sch_except_rec.assignment_id := p_sch_record_tab(l_last_rec_index).assignment_id;
421 l_sch_except_rec.calendar_id := p_sch_record_tab(l_last_rec_index).calendar_id;
422 l_sch_except_rec.project_id := p_sch_record_tab(l_last_rec_index).project_id;
423 l_sch_except_rec.schedule_type_code := p_sch_record_tab(l_last_rec_index).schedule_type_code;
424 l_sch_except_rec.assignment_status_code := p_sch_record_tab(l_last_rec_index).assignment_status_code;
425
426 --Copying the date and hours information into exception record
427 l_sch_except_rec.start_date := l_new_rec_start_date;
428 l_sch_except_rec.end_date := l_new_rec_end_date;
429 l_sch_except_rec.monday_hours := l_mon_hrs_in_new_rec;
430 l_sch_except_rec.tuesday_hours := l_tue_hrs_in_new_rec;
431 l_sch_except_rec.wednesday_hours := l_wed_hrs_in_new_rec;
432 l_sch_except_rec.thursday_hours := l_thu_hrs_in_new_rec;
433 l_sch_except_rec.friday_hours := l_fri_hrs_in_new_rec;
434 l_sch_except_rec.saturday_hours := l_sat_hrs_in_new_rec;
435 l_sch_except_rec.sunday_hours := l_sun_hrs_in_new_rec;
436
437 --Setting exception_type_code for update workpattern in the exception record
438 l_sch_except_rec.exception_type_code := 'CHANGE_WORK_PATTERN';
439
440 PA_SCHEDULE_UTILS.log_message(1, 'Insert_Row before apply_other_changes ....');
441
442 PA_SCHEDULE_PVT.apply_other_changes(p_sch_record_tab,
443 l_sch_except_rec,
444 l_out_sch_rec_tab,
445 l_x_return_status,
446 x_msg_count,
447 x_msg_data);
448
449 PA_SCHEDULE_UTILS.log_message(1, 'Insert_Row After apply_other_changes ....' || l_x_return_status);
450 PA_SCHEDULE_UTILS.log_message(1,'l_out_sch_rec_tab (change ) : ',l_out_sch_rec_tab );
451
452
453 IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
454
455 IF l_out_sch_rec_tab.count < p_sch_record_tab.COUNT THEN
456 -- This condition will not happen since apply_other_changes will only append the change_type_code = D it will
457 -- not delete any record from p_sch_record_tab pl/sql table while generating l_out_sch_rec_tab
458 -- This has only been placed for debugging purposes in future to trap the error point in apply_other_changes
459 PA_SCHEDULE_UTILS.log_message(1,'apply_other_changes retuned less count ');
460 RAISE FND_API.G_EXC_ERROR;
461 ELSE
462 --Added for bug 5856987
463 l_schedule_id.delete;
464 l_calendar_id.delete;
465 l_assignment_id.delete;
466 l_project_id.delete;
467 l_schedule_type_code.delete;
468 l_assignment_status_code.delete;
469 l_start_date.delete;
470 l_end_date.delete;
471 l_monday_hours.delete;
472 l_tuesday_hours.delete;
473 l_wednesday_hours.delete;
474 l_thursday_hours.delete;
475 l_friday_hours.delete;
476 l_saturday_hours.delete;
477 l_sunday_hours.delete;
478 K := 1;
479 --End for bug 5856987
480
481 FOR J IN l_out_sch_rec_tab.first..l_out_sch_rec_tab.last LOOP
482
483 IF nvl(l_out_sch_rec_tab(J).change_type_code,'X') <> 'D' THEN --Added for bug 5856987
484
485 IF (NVL(l_out_sch_rec_tab(J).schedule_id,-1) <= 0) THEN
486 l_schedule_id(K) := get_nextval;
487 ELSE
488 l_schedule_id(K) := l_out_sch_rec_tab(J).schedule_id;
489 END IF;
490
491 l_calendar_id(K) := l_out_sch_rec_tab(J).calendar_id;
492 l_assignment_id(K) := l_out_sch_rec_tab(J).assignment_id;
493 l_project_id(K) := l_out_sch_rec_tab(J).project_id;
494 l_schedule_type_code(K) := l_out_sch_rec_tab(J).schedule_type_code;
495 l_assignment_status_code(K) := l_out_sch_rec_tab(J).assignment_status_code;
496 l_start_date(K) := trunc(l_out_sch_rec_tab(J).start_date);
497 l_end_date(K) := trunc(l_out_sch_rec_tab(J).end_date);
498 l_monday_hours(K) := trunc(l_out_sch_rec_tab(J).monday_hours, 2);
499 l_tuesday_hours(K) := trunc(l_out_sch_rec_tab(J).tuesday_hours, 2);
500 l_wednesday_hours(K) := trunc(l_out_sch_rec_tab(J).wednesday_hours, 2);
501 l_thursday_hours(K) := trunc(l_out_sch_rec_tab(J).thursday_hours, 2);
502 l_friday_hours(K) := trunc(l_out_sch_rec_tab(J).friday_hours, 2);
503 l_saturday_hours(K) := trunc(l_out_sch_rec_tab(J).saturday_hours, 2);
504 l_sunday_hours(K) := trunc(l_out_sch_rec_tab(J).sunday_hours, 2);
505
506 K := K + 1; --Added for bug 5856987
507 END IF; --Added for bug 5856987
508
509 END LOOP;
510 END IF;
511 ELSE
512 PA_SCHEDULE_UTILS.log_message(1,'l_x_return_status is not success ');
513 RAISE FND_API.G_EXC_ERROR;
514 END IF;
515 --- End of bug 5684828
516
517
518 END IF;--IF p_total_hours > l_rounded_total THEN
519
520 --FORALL J IN p_sch_record_tab.first..p_sch_record_tab.last -- Commented for Bug 5126919
521 FORALL J IN l_schedule_id.first..l_schedule_id.last -- Added for Bug 5126919
522 INSERT INTO PA_SCHEDULES
523 ( schedule_id ,
524 calendar_id ,
525 assignment_id ,
526 project_id ,
527 schedule_type_code ,
528 status_code ,
529 start_date ,
530 end_date ,
531 monday_hours ,
532 tuesday_hours ,
533 wednesday_hours ,
534 thursday_hours ,
535 friday_hours ,
536 saturday_hours ,
537 sunday_hours ,
538 forecast_txn_version_number,
539 forecast_txn_generated_flag,
540 creation_date ,
541 created_by ,
542 last_update_date ,
543 last_update_by ,
544 last_update_login ,
545 request_id ,
546 program_application_id ,
547 program_id ,
548 program_update_date )
549 VALUES
550 ( l_schedule_id(J) ,
551 l_calendar_id(J) ,
552 l_assignment_id(J) ,
553 l_project_id(J) ,
554 l_schedule_type_code(J) ,
555 l_assignment_status_code(J) ,
556 l_start_date(J) ,
557 l_end_date(J) ,
558 l_monday_hours(J) ,
559 l_tuesday_hours(J) ,
560 l_wednesday_hours(J) ,
561 l_thursday_hours(J) ,
562 l_friday_hours(J) ,
563 l_saturday_hours(J) ,
564 l_sunday_hours(J) ,
565 1 ,
566 'N' ,
567 sysdate ,
568 fnd_global.user_id ,
569 sysdate ,
570 fnd_global.user_id ,
571 fnd_global.login_id ,
572 fnd_global.conc_request_id() ,
573 fnd_global.prog_appl_id () ,
574 fnd_global.conc_program_id() ,
575 trunc(sysdate) );
576
577 PA_SCHEDULE_UTILS.log_message(1,'end of the schedule inser row .... ');
578
579 EXCEPTION
580 WHEN FND_API.G_EXC_ERROR THEN --Added for bug 5684828
581 x_return_status := l_x_return_status;
582 WHEN l_empty_tab_record THEN
583 NULL;
584 WHEN OTHERS THEN
585 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
586 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PKG',
587 p_procedure_name => 'insert_rows');
588 raise;
589
590 PA_SCHEDULE_UTILS.log_message(1,'ERROR ....'||sqlerrm);
591 END insert_rows;
592
593 -- This procedure will insert the record in pa_schedules table
594 -- Input parameters
595 -- Parameters Type Required Description
596 -- P_Calendar_Id NUMBER YES Id for that calendar which is associated to this assignment
597 -- P_Assignment_Id NUMBER YES Assignment id of the schedule records
598 -- P_Project_Id NUMBER YES project id of the associated calendar
599 -- P_Schedule_Type_Code VARCHAR2 YES It is schedule type code e.g changed hours/changed duration
600 -- P_Assignment_Status_Code VARCHAR2 YES Status of the assignment e.g OPEN/CONFIRM/PROVISIONAL
601 -- P_Start_Date DATE YES stat date of the schedule
602 -- P_End_Date DATE YES end date of the schedule
603 -- P_Monday_Hours NUMBER YES No. of hours of this day
604 -- P_Tuesday_Hours NUMBER YES No. of hours of this day
605 -- P_Wednesday_Hours NUMBER YES No. of hours of this day
606 -- P_Thursday_Hours NUMBER YES No. of hours of this day
607 -- P_Friday_Hours NUMBER YES No. of hours of this day
608 -- P_Saturday_Hours NUMBER YES No. of hours of this day
609 -- P_Sunday_Hours NUMBER YES No. of hours of this day
610 --
611
612 PROCEDURE insert_rows
613 ( p_calendar_id IN Number Default null ,
614 p_assignment_id IN Number Default null ,
615 p_project_id IN Number Default null ,
616 p_schedule_type_code IN varchar2 ,
617 p_assignment_status_code IN varchar2 Default null ,
618 p_start_date IN date ,
619 p_end_date IN date ,
620 p_monday_hours IN Number ,
621 p_tuesday_hours IN Number ,
622 p_wednesday_hours IN Number ,
623 p_thursday_hours IN Number ,
624 p_friday_hours IN Number ,
625 p_saturday_hours IN Number ,
626 p_sunday_hours IN Number ,
627 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
628 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
629 x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
630 IS
631 l_t_schedule_id NUMBER;
632 BEGIN
633 /* 1799636 The following line of code was commented to resolve the performance issue */
634 -- l_t_schedule_id := get_nextval;
635
636 x_return_status := FND_API.G_RET_STS_SUCCESS;
637
638 INSERT INTO PA_SCHEDULES
639 ( schedule_id ,
640 calendar_id ,
641 assignment_id ,
642 project_id ,
643 schedule_type_code ,
644 status_code ,
645 start_date ,
646 end_date ,
647 monday_hours ,
648 tuesday_hours ,
649 wednesday_hours ,
650 thursday_hours ,
651 friday_hours ,
652 saturday_hours ,
653 sunday_hours ,
654 forecast_txn_version_number,
655 forecast_txn_generated_flag,
656 creation_date ,
657 created_by ,
658 last_update_date ,
659 last_update_by ,
660 last_update_login ,
661 request_id ,
662 program_application_id ,
663 program_id ,
664 program_update_date )
665 VALUES
666 -- ( l_t_schedule_id ,
667 (pa_schedules_s.nextval,
668 p_calendar_id ,
669 p_assignment_id ,
670 p_project_id ,
671 p_schedule_type_code ,
672 p_assignment_status_code ,
673 trunc(p_start_date) ,
674 trunc(p_end_date) ,
675 p_monday_hours ,
676 p_tuesday_hours ,
677 p_wednesday_hours ,
678 p_thursday_hours ,
679 p_friday_hours ,
680 p_saturday_hours ,
681 p_sunday_hours ,
682 1 ,
683 'N' ,
684 sysdate ,
685 fnd_global.user_id ,
686 sysdate ,
687 fnd_global.user_id ,
688 fnd_global.login_id ,
689 fnd_global.conc_request_id() ,
690 fnd_global.prog_appl_id () ,
691 fnd_global.conc_program_id() ,
692 trunc(sysdate) );
693
694 EXCEPTION
695 WHEN OTHERS THEN
696 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
697 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PKG',
698 p_procedure_name => 'insert_rows');
699 raise;
700
701 END insert_rows;
702
703 -- This procedure will update the record in pa_schedules table
704 -- Input parameters
705 -- Parameters Type Required Description
706 -- P_Sch_Record_Tab ScheduleTabTyp YES It contains the schedule record
707 --
708
709 PROCEDURE update_rows ( p_sch_record_tab IN PA_SCHEDULE_GLOB.ScheduleTabTyp,
710 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
711 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
712 x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
713 IS
714 l_schrowid PA_PLSQL_DATATYPES.RowidTabTyp;
715 l_schedule_id PA_PLSQL_DATATYPES.IdTabTyp;
716 l_calendar_id PA_PLSQL_DATATYPES.IdTabTyp;
717 l_assignment_id PA_PLSQL_DATATYPES.IdTabTyp;
718 l_project_id PA_PLSQL_DATATYPES.IdTabTyp;
719 l_schedule_type_code PA_PLSQL_DATATYPES.Char30TabTyp;
720 l_assignment_status_code PA_PLSQL_DATATYPES.Char30TabTyp;
721 l_start_date PA_PLSQL_DATATYPES.DateTabTyp;
722 l_end_date PA_PLSQL_DATATYPES.DateTabTyp;
723 l_monday_hours PA_PLSQL_DATATYPES.NumTabTyp;
724 l_tuesday_hours PA_PLSQL_DATATYPES.NumTabTyp;
725 l_wednesday_hours PA_PLSQL_DATATYPES.NumTabTyp;
726 l_thursday_hours PA_PLSQL_DATATYPES.NumTabTyp;
727 l_friday_hours PA_PLSQL_DATATYPES.NumTabTyp;
728 l_saturday_hours PA_PLSQL_DATATYPES.NumTabTyp;
729 l_sunday_hours PA_PLSQL_DATATYPES.NumTabTyp;
730
731 BEGIN
732 x_return_status := FND_API.G_RET_STS_SUCCESS;
733
734 PA_SCHEDULE_UTILS.log_message(1,'start of the schedule inser row .... ');
735 if ( p_sch_record_tab.count = 0 ) then
736 PA_SCHEDULE_UTILS.log_message(1,'count 0 ... and returning ');
737 raise l_empty_tab_record;
738 end if;
739
740 FOR J IN p_sch_record_tab.first..p_sch_record_tab.last LOOP
741
742 PA_SCHEDULE_UTILS.log_message(1,' J '||to_char(J)||' sch_id '||to_char(p_sch_record_tab(J).schedule_id)
743 || ' start_date '||p_sch_record_tab(J).start_date);
744 l_schrowid(J) := p_sch_record_tab(J).schrowid;
745 l_schedule_id(J) := p_sch_record_tab(J).schedule_id;
746 l_calendar_id(J) := p_sch_record_tab(J).calendar_id;
747 l_assignment_id(J) := p_sch_record_tab(J).assignment_id;
748 l_project_id(J) := p_sch_record_tab(J).project_id;
749 l_schedule_type_code(J) := p_sch_record_tab(J).schedule_type_code;
750 l_assignment_status_code(J) := p_sch_record_tab(J).assignment_status_code;
751 l_start_date(J) := trunc(p_sch_record_tab(J).start_date);
752 l_end_date(J) := trunc(p_sch_record_tab(J).end_date);
753 l_monday_hours(J) := trunc(p_sch_record_tab(J).monday_hours, 2);
754 l_tuesday_hours(J) := trunc(p_sch_record_tab(J).tuesday_hours, 2);
755 l_wednesday_hours(J) := trunc(p_sch_record_tab(J).wednesday_hours, 2);
756 l_thursday_hours(J) := trunc(p_sch_record_tab(J).thursday_hours, 2);
757 l_friday_hours(J) := trunc(p_sch_record_tab(J).friday_hours, 2);
758 l_saturday_hours(J) := trunc(p_sch_record_tab(J).saturday_hours, 2);
759 l_sunday_hours(J) := trunc(p_sch_record_tab(J).sunday_hours, 2);
760
761 PA_SCHEDULE_UTILS.log_message(1,' J '||to_char(J)||' l_sch_id '||to_char(l_schedule_id(J))||'start_date '|| to_char(l_start_date(J)));
762 END LOOP;
763
764 FORALL J IN p_sch_record_tab.first..p_sch_record_tab.last
765 UPDATE PA_SCHEDULES
766 SET
767 calendar_id = l_calendar_id(J),
768 assignment_id = l_assignment_id(J),
769 project_id = l_project_id(J),
770 schedule_type_code = l_schedule_type_code(J),
771 status_code = l_assignment_status_code(J),
772 start_date = l_start_date(J),
773 end_date = l_end_date(J),
774 monday_hours = l_monday_hours(J),
775 tuesday_hours = l_tuesday_hours(J),
776 wednesday_hours = l_wednesday_hours(J),
777 thursday_hours = l_thursday_hours(J),
778 friday_hours = l_friday_hours(J),
779 saturday_hours = l_saturday_hours(J),
780 sunday_hours = l_sunday_hours(J),
781 forecast_txn_version_number = forecast_txn_version_number+1,
782 forecast_txn_generated_flag = 'N',
783 last_update_date = sysdate ,
784 last_update_by = fnd_global.user_id,
785 last_update_login = fnd_global.login_id
786 WHERE schedule_id = l_schedule_id(J);
787
788 PA_SCHEDULE_UTILS.log_message(1,'end of update row .... ');
789 EXCEPTION
790 WHEN l_empty_tab_record THEN
791 NULL;
792 WHEN OTHERS THEN
793 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
794 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PKG',
795 p_procedure_name => 'update_rows');
796 raise;
797
798 PA_SCHEDULE_UTILS.log_message(1,'ERROR in update row '||sqlerrm);
799 END update_rows;
800
801 -- This procedure will update the record in pa_schedules table
802 -- Input parameters
803 -- Parameters Type Required Description
804 -- P_Schedule_Id NUMBER YES Id for the corresponding schedule
805 -- P_Calendar_Id NUMBER YES Id for that calendar which is associated to this assignmen
806 -- P_Assignment_Id NUMBER YES Assignment id of the schedule records
807 -- P_Project_Id NUMBER YES project id of the associated calendar
808 -- P_Schedule_Type_Code VARCHAR2 YES It is schedule type code e.g changed hours/changed duration
809 -- P_Assignment_Status_Code VARCHAR2 YES Status of the assignment e.g OPEN/CONFIRM/PROVISIONAL
810 -- P_Start_Date DATE YES stat date of the schedule
811 -- P_End_Date DATE YES end date of the schedule
812 -- P_Monday_Hours NUMBER YES No. of hours of this day
813 -- P_Tuesday_Hours NUMBER YES No. of hours of this day
814 -- P_Wednesday_Hours NUMBER YES No. of hours of this day
815 -- P_Thursday_Hours NUMBER YES No. of hours of this day
816 -- P_Friday_Hours NUMBER YES No. of hours of this day
817 -- P_Saturday_Hours NUMBER YES No. of hours of this day
818 -- P_Sunday_Hours NUMBER YES No. of hours of this day
819 --
820
821 PROCEDURE update_rows
822 ( p_schedule_id IN NUMBER ,
823 p_calendar_id IN Number Default null ,
824 p_assignment_id IN Number Default null ,
825 p_project_id IN Number Default null ,
826 p_schedule_type_code IN varchar2 Default null ,
827 p_assignment_status_code IN varchar2 Default null ,
828 p_start_date IN date Default null ,
829 p_end_date IN date Default null ,
830 p_monday_hours IN Number Default null ,
831 p_tuesday_hours IN Number Default null ,
832 p_wednesday_hours IN Number Default null ,
833 p_thursday_hours IN Number Default null ,
834 p_friday_hours IN Number Default null ,
835 p_saturday_hours IN Number Default null ,
836 p_sunday_hours IN Number Default null ,
837 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
838 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
839 x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
840 IS
841 BEGIN
842
843 x_return_status := FND_API.G_RET_STS_SUCCESS;
844 UPDATE PA_SCHEDULES
845 SET
846 calendar_id = p_calendar_id,
847 assignment_id = p_assignment_id,
848 project_id = p_project_id,
849 schedule_type_code = p_schedule_type_code,
850 status_code = p_assignment_status_code,
851 start_date = trunc(p_start_date),
852 end_date = trunc(p_end_date),
853 monday_hours = p_monday_hours,
854 tuesday_hours = p_tuesday_hours,
855 wednesday_hours = p_wednesday_hours,
856 thursday_hours = p_thursday_hours,
857 friday_hours = p_friday_hours,
858 saturday_hours = p_saturday_hours,
859 sunday_hours = p_sunday_hours,
860 last_update_date = sysdate ,
861 last_update_by = fnd_global.user_id,
862 last_update_login = fnd_global.login_id
863 WHERE schedule_id = p_schedule_id;
864
865
866 EXCEPTION
867 WHEN OTHERS THEN
868 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
869 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PKG',
870 p_procedure_name => 'update_rows');
871 raise;
872
873 END update_rows;
874
875 -- This procedure will update the record in pa_schedules table
876 -- Input parameters
877 -- Parameters Type Required Description
878 -- P_Schrowid ROWID YES Row id for the corresponding schedule row
879 -- P_Calendar_Id NUMBER YES Id for that calendar which is associated to this assignmen
880 -- P_Assignment_Id NUMBER YES Assignment id of the schedule records
881 -- P_Project_Id NUMBER YES project id of the associated calendar
882 -- P_Schedule_Type_Code VARCHAR2 YES It is schedule type code e.g changed hours/changed duration
883 -- P_Assignment_Status_Code VARCHAR2 YES Status of the assignment e.g OPEN/CONFIRM/PROVISIONAL
884 -- P_Start_Date DATE YES stat date of the schedule
885 -- P_End_Date DATE YES end date of the schedule
886 -- P_Monday_Hours NUMBER YES No. of hours of this day
887 -- P_Tuesday_Hours NUMBER YES No. of hours of this day
888 -- P_Wednesday_Hours NUMBER YES No. of hours of this day
889 -- P_Thursday_Hours NUMBER YES No. of hours of this day
890 -- P_Friday_Hours NUMBER YES No. of hours of this day
891 -- P_Saturday_Hours NUMBER YES No. of hours of this day
892 -- P_Sunday_Hours NUMBER YES No. of hours of this day
893 --
894
895 PROCEDURE update_rows
896 ( p_schrowid IN rowid ,
897 p_schedule_id IN NUMBER ,
898 p_calendar_id IN Number Default null ,
899 p_assignment_id IN Number Default null ,
900 p_project_id IN Number Default null ,
901 p_schedule_type_code IN varchar2 Default null ,
902 p_assignment_status_code IN varchar2 Default null ,
903 p_start_date IN date Default null ,
904 p_end_date IN date Default null ,
905 p_monday_hours IN Number Default null ,
906 p_tuesday_hours IN Number Default null ,
907 p_wednesday_hours IN Number Default null ,
908 p_thursday_hours IN Number Default null ,
909 p_friday_hours IN Number Default null ,
910 p_saturday_hours IN Number Default null ,
911 p_sunday_hours IN Number Default null ,
912 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
913 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
914 x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
915 IS
916 BEGIN
917 x_return_status := FND_API.G_RET_STS_SUCCESS;
918
919 UPDATE PA_SCHEDULES
920 SET
921 calendar_id = p_calendar_id,
922 assignment_id = p_assignment_id,
923 project_id = p_project_id,
924 schedule_type_code = p_schedule_type_code,
925 status_code = p_assignment_status_code,
926 start_date = trunc(p_start_date),
927 end_date = trunc(p_end_date),
928 monday_hours = p_monday_hours,
929 tuesday_hours = p_tuesday_hours,
930 wednesday_hours = p_wednesday_hours,
931 thursday_hours = p_thursday_hours,
932 friday_hours = p_friday_hours,
933 saturday_hours = p_saturday_hours,
934 sunday_hours = p_sunday_hours,
935 last_update_date = sysdate ,
936 last_update_by = fnd_global.user_id,
937 last_update_login = fnd_global.login_id
938 WHERE schedule_id = p_schedule_id;
939
940
941 EXCEPTION
942 WHEN OTHERS THEN
943 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
944 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PKG',
945 p_procedure_name => 'update_rows');
946 raise;
947
948 END update_rows;
949
950 -- This procedure will delete the record in pa_schedules table
951 -- Input parameters
952 -- Parameters Type Required Description
953 -- P_Sch_Record_Tab ScheduleTabTyp YES It contains the schedule record
954 --
955
956 PROCEDURE delete_rows ( p_sch_record_tab IN PA_SCHEDULE_GLOB.ScheduleTabTyp,
957 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
958 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
959 x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
960 IS
961 l_schedule_id PA_PLSQL_DATATYPES.IdTabTyp;
962
963 BEGIN
964 x_return_status := FND_API.G_RET_STS_SUCCESS;
965
966 PA_SCHEDULE_UTILS.log_message(1,'start of the delete row ..... ');
967 if ( p_sch_record_tab.count = 0 ) then
968 PA_SCHEDULE_UTILS.log_message(1,'count 0 ... and returning ');
969 raise l_empty_tab_record;
970 end if;
971
972 FOR J IN p_sch_record_tab.first..p_sch_record_tab.last LOOP
973 PA_SCHEDULE_UTILS.log_message(1,' I : '||to_char(J)||' sch_id '||to_char(p_sch_record_tab(J).schedule_id));
974 l_schedule_id(J) := p_sch_record_tab(J).schedule_id;
975
976 END LOOP;
977
978 FORALL J IN l_schedule_id.first..l_schedule_id.last
979 DELETE FROM PA_SCHEDULES WHERE schedule_id = l_schedule_id(J);
980
981 PA_SCHEDULE_UTILS.log_message(1,'end of the delete row ..... ');
982 EXCEPTION
983 WHEN l_empty_tab_record THEN
984 NULL;
985 WHEN OTHERS THEN
986 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
987 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PKG',
988 p_procedure_name => 'delete_rows');
989 raise;
990
991 PA_SCHEDULE_UTILS.log_message(1,'ERROR in the delete row ..... '||sqlerrm);
992 END delete_rows;
993
994 -- This procedure will delete the record in pa_schedules table
995 -- Input parameters
996 -- Parameters Type Required Description
997 -- P_Schedule_Id NUMBER YES Schedule id for deletion
998 --
999
1000 PROCEDURE delete_rows
1001 ( p_schedule_id IN Number ,
1002 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
1003 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
1004 x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
1005 IS
1006 BEGIN
1007 x_return_status := FND_API.G_RET_STS_SUCCESS;
1008
1009 DELETE
1010 FROM PA_SCHEDULES
1011 WHERE schedule_id = p_schedule_id;
1012
1013 EXCEPTION
1014 WHEN OTHERS THEN
1015 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1016 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PKG',
1017 p_procedure_name => 'delete_rows');
1018 raise;
1019
1020 END delete_rows;
1021
1022 -- This procedure will delete the record in pa_schedules table
1023 -- Input parameters
1024 -- Parameters Type Required Description
1025 -- P_Schrowid ROWID YES rowid of the schedule record for deletion
1026 --
1027
1028 PROCEDURE delete_rows
1029 ( p_schrowid IN rowid ,
1030 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
1031 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
1032 x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
1033 IS
1034 BEGIN
1035 x_return_status := FND_API.G_RET_STS_SUCCESS;
1036
1037 DELETE
1038 FROM PA_SCHEDULES
1039 WHERE rowid = p_schrowid ;
1040
1041 EXCEPTION
1042 WHEN OTHERS THEN
1043 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1044 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PKG',
1045 p_procedure_name => 'delete_rows');
1046 raise;
1047
1048 END delete_rows;
1049 END PA_SCHEDULE_PKG;
[Home] [Help]