[Home] [Help]
PACKAGE BODY: APPS.PA_SCHEDULE_UTILS
Source
1 PACKAGE BODY PA_SCHEDULE_UTILS as
2 /* $Header: PARGUTLB.pls 120.2 2005/08/25 03:40:56 sunkalya noship $ */
3
4 P_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
5
6 l_empty_tab_record EXCEPTION; -- Variable to raise the exception if the passing table of records is empty
7
8 -- This procedure will copy the one record from another
9 -- Input parameters
10 -- Parameters Type Required Description
11 -- P_Sch_Record_Tab ScheduleTabTyp YES It contains the schedule records
12 -- P_Start_Id NUMBER YES stat id of the schedule record for which schedule is to be copied
13 -- P_End_id NUMBER YES end id of the schedule to which schedule is to be copied
14 -- In Out parameters
15 -- X_Sch_Record_Tab ScheduleTabTyp YES It stores the copied scheduled records
16 --
17
18 PROCEDURE copy_schedule_rec_tab ( p_sch_record_tab IN PA_SCHEDULE_GLOB.ScheduleTabTyp,
19 p_start_id IN NUMBER,
20 p_end_id IN NUMBER,
21 x_sch_record_tab IN OUT NOCOPY PA_SCHEDULE_GLOB.ScheduleTabTyp,
22 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
23 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
24 x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
25 IS
26
27 l_oidx NUMBER;
28 l_iidx NUMBER;
29
30 BEGIN
31 l_oidx := 1;
32 l_iidx := p_start_id;
33 x_return_status := FND_API.G_RET_STS_SUCCESS;
34 x_sch_record_tab.delete;
35
36 /* Checking for the empty table of record */
37 IF (p_sch_record_tab.count = 0 ) THEN
38 raise l_empty_tab_record;
39 END IF;
40
41 /* Copying all the structure elements */
42 Loop
43
44 x_sch_record_tab(l_oidx).schrowid := p_sch_record_tab(l_iidx).schrowid;
45 x_sch_record_tab(l_oidx).schedule_id := p_sch_record_tab(l_iidx).schedule_id;
46 x_sch_record_tab(l_oidx).calendar_id := p_sch_record_tab(l_iidx).calendar_id;
47 x_sch_record_tab(l_oidx).assignment_id := p_sch_record_tab(l_iidx).assignment_id;
48 x_sch_record_tab(l_oidx).project_id := p_sch_record_tab(l_iidx).project_id;
49 x_sch_record_tab(l_oidx).schedule_type_code := p_sch_record_tab(l_iidx).schedule_type_code;
50 x_sch_record_tab(l_oidx).assignment_status_code := p_sch_record_tab(l_iidx).assignment_status_code;
51 x_sch_record_tab(l_oidx).system_status_code := p_sch_record_tab(l_iidx).system_status_code;
52 x_sch_record_tab(l_oidx).start_date := p_sch_record_tab(l_iidx).start_date;
53 x_sch_record_tab(l_oidx).end_date := p_sch_record_tab(l_iidx).end_date;
54 x_sch_record_tab(l_oidx).monday_hours := p_sch_record_tab(l_iidx).monday_hours;
55 x_sch_record_tab(l_oidx).tuesday_hours := p_sch_record_tab(l_iidx).tuesday_hours;
56 x_sch_record_tab(l_oidx).wednesday_hours := p_sch_record_tab(l_iidx).wednesday_hours;
57 x_sch_record_tab(l_oidx).thursday_hours := p_sch_record_tab(l_iidx).thursday_hours;
58 x_sch_record_tab(l_oidx).friday_hours := p_sch_record_tab(l_iidx).friday_hours;
59 x_sch_record_tab(l_oidx).saturday_hours := p_sch_record_tab(l_iidx).saturday_hours;
60 x_sch_record_tab(l_oidx).sunday_hours := p_sch_record_tab(l_iidx).sunday_hours;
61 x_sch_record_tab(l_oidx).change_type_code := p_sch_record_tab(l_iidx).change_type_code;
62
63
64 IF (p_end_id = l_iidx)then
65 EXIT;
66 ELSE
67 l_iidx := p_sch_record_tab.next(l_iidx);
68 l_oidx := l_oidx + 1;
69 END IF;
70
71 END LOOP;
72
73
74 EXCEPTION
75 WHEN l_empty_tab_record THEN
76 null;
77 WHEN OTHERS THEN
78 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
79 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_UTILS',
80 p_procedure_name => 'copy_schedule_rec_tab');
81 raise;
82
83 END copy_schedule_rec_tab;
84
85
86 -- This procedure will append the records
87 -- Input parameters
88 -- Parameters Type Required Description
89 -- P_Sch_Record_Tab ScheduleTabTyp YES It contains the schedule records
90 -- P_Start_Id NUMBER YES stat id of the schedule record
91 -- P_End_id NUMBER YES end id of the schedule record
92 -- In Out parameters
93 -- PX_Sch_Record_Tab ScheduleTabTyp YES It stores the added scheduled records
94 --
95
96 PROCEDURE add_schedule_rec_tab ( p_sch_record_tab IN PA_SCHEDULE_GLOB.ScheduleTabTyp,
97 p_start_id IN NUMBER,
98 p_end_id IN NUMBER,
99 px_sch_record_tab IN OUT NOCOPY PA_SCHEDULE_GLOB.ScheduleTabTyp,
100 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
101 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
102 x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
103 IS
104 l_oidx NUMBER;
105 l_iidx NUMBER;
106 l_cnt NUMBER;
107
108 BEGIN
109 l_iidx := p_start_id;
110 l_cnt := px_sch_record_tab.count;
111
112 x_return_status := FND_API.G_RET_STS_SUCCESS;
113
114 /* Validating the rows in the table for Null */
115 IF (l_cnt = 0 ) THEN
116 l_oidx := 1;
117 ELSE
118 l_oidx := px_sch_record_tab.last + 1;
119 END IF;
120
121 /* Appending all the structure elements */
122 Loop
123
124 px_sch_record_tab(l_oidx).schrowid := p_sch_record_tab(l_iidx).schrowid;
125 px_sch_record_tab(l_oidx).schedule_id := p_sch_record_tab(l_iidx).schedule_id;
126 px_sch_record_tab(l_oidx).calendar_id := p_sch_record_tab(l_iidx).calendar_id;
127 px_sch_record_tab(l_oidx).assignment_id := p_sch_record_tab(l_iidx).assignment_id;
128 px_sch_record_tab(l_oidx).project_id := p_sch_record_tab(l_iidx).project_id;
129 px_sch_record_tab(l_oidx).schedule_type_code := p_sch_record_tab(l_iidx).schedule_type_code;
130 px_sch_record_tab(l_oidx).assignment_status_code := p_sch_record_tab(l_iidx).assignment_status_code;
131 px_sch_record_tab(l_oidx).system_status_code := p_sch_record_tab(l_iidx).system_status_code;
132 px_sch_record_tab(l_oidx).start_date := p_sch_record_tab(l_iidx).start_date;
133 px_sch_record_tab(l_oidx).end_date := p_sch_record_tab(l_iidx).end_date;
134 px_sch_record_tab(l_oidx).monday_hours := p_sch_record_tab(l_iidx).monday_hours;
135 px_sch_record_tab(l_oidx).tuesday_hours := p_sch_record_tab(l_iidx).tuesday_hours;
136 px_sch_record_tab(l_oidx).wednesday_hours := p_sch_record_tab(l_iidx).wednesday_hours;
137 px_sch_record_tab(l_oidx).thursday_hours := p_sch_record_tab(l_iidx).thursday_hours;
138 px_sch_record_tab(l_oidx).friday_hours := p_sch_record_tab(l_iidx).friday_hours;
139 px_sch_record_tab(l_oidx).saturday_hours := p_sch_record_tab(l_iidx).saturday_hours;
140 px_sch_record_tab(l_oidx).sunday_hours := p_sch_record_tab(l_iidx).sunday_hours;
141 px_sch_record_tab(l_oidx).change_type_code := p_sch_record_tab(l_iidx).change_type_code;
142
143 IF (p_end_id = l_iidx)then
144 EXIT;
145 ELSE
146 l_iidx := p_sch_record_tab.next(l_iidx);
147 l_oidx := l_oidx + 1;
148 END IF;
149
150 END LOOP;
151
152 EXCEPTION
153 WHEN OTHERS THEN
154 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
155 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_UTILS',
156 p_procedure_name => 'add_schedule_rec_tab');
157 raise;
158
159 END add_schedule_rec_tab;
160
161
162 -- This procedure will delete the records
163 -- Input parameters
164 -- Parameters Type Required Description
165 -- P_Start_Id NUMBER YES stat id of the schedule record
166 -- P_End_id NUMBER YES end id of the schedule record
167 -- marked for deletion
168 -- In Out parameters
169 -- PX_Sch_Record_Tab ScheduleTabTyp YES It stores the scheduled recordswhich are marked for deletion
170 --
171
172 PROCEDURE mark_del_sch_rec_tab ( p_start_id IN NUMBER,
173 p_end_id IN NUMBER,
174 px_sch_record_tab IN OUT NOCOPY PA_SCHEDULE_GLOB.ScheduleTabTyp,
175 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
176 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
177 x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
178 IS
179 l_iidx NUMBER;
180
181 BEGIN
182 l_iidx := p_start_id;
183 x_return_status := FND_API.G_RET_STS_SUCCESS;
184
185 /* Marking for deletion */
186 LOOP
187
188 px_sch_record_tab(l_iidx).change_type_code := 'D';
189
190
191 IF (p_end_id = l_iidx)then
192 EXIT;
193 ELSE
194 l_iidx := px_sch_record_tab.next(l_iidx);
195 END IF;
196
197 END LOOP;
198
199 EXCEPTION
200 WHEN OTHERS THEN
201 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
202 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_UTILS',
203 p_procedure_name => 'mark_del_sch_rec_tab');
204 raise;
205
206 END mark_del_sch_rec_tab;
207
208
209 -- This procedure will seperate delete or non delete records
210 -- Input parameters
211 -- Parameters Type Required Description
212 -- P_Sch_Record_Tab ScheduleTabTyp YES It contains the schedule records
213 -- Out parameters
214 -- X_Del_Sch_Record_Tab ScheduleTabTyp YES It stores scheduled records which are marked for deletion
215 -- X_Sch_Record_Tab ScheduleTabTyp YES It stores scheduled records whic are marked for insertion
216 -- and updation
217 --
218
219 PROCEDURE sep_del_sch_rec_tab ( p_sch_record_tab IN PA_SCHEDULE_GLOB.ScheduleTabTyp,
220 x_del_sch_rec_tab IN OUT NOCOPY PA_SCHEDULE_GLOB.ScheduleTabTyp,
221 x_sch_record_tab OUT NOCOPY PA_SCHEDULE_GLOB.ScheduleTabTyp,
222 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
223 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
224 x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
225 IS
226 l_didx NUMBER;
227 l_cidx NUMBER;
228 l_iidx NUMBER;
229 BEGIN
230
231 IF (x_del_sch_rec_tab.COUNT > 0) THEN
232 l_didx := x_del_sch_rec_tab.count + 1; --Added for bug 4176968
233
234 -- l_didx := x_del_sch_rec_tab.FIRST + 1; Commented for bug 4176968
235 ELSE
236 l_didx := 1;
237 END IF;
238
239 l_cidx := 1;
240 x_return_status := FND_API.G_RET_STS_SUCCESS;
241
242 IF (p_sch_record_tab.count = 0 ) THEN
243 Raise l_empty_tab_record;
244 ELSE
245 l_iidx := p_sch_record_tab.first;
246 END IF;
247
248 /* Seprating the rows which are marked for deletion */
249 Loop
250
251 IF (p_sch_record_tab(l_iidx).change_type_code = 'D' ) THEN
252
253 x_del_sch_rec_tab(l_didx).schrowid := p_sch_record_tab(l_iidx).schrowid;
254 x_del_sch_rec_tab(l_didx).schedule_id := p_sch_record_tab(l_iidx).schedule_id;
255 x_del_sch_rec_tab(l_didx).calendar_id := p_sch_record_tab(l_iidx).calendar_id;
256 x_del_sch_rec_tab(l_didx).assignment_id := p_sch_record_tab(l_iidx).assignment_id;
257 x_del_sch_rec_tab(l_didx).project_id := p_sch_record_tab(l_iidx).project_id;
258 x_del_sch_rec_tab(l_didx).schedule_type_code := p_sch_record_tab(l_iidx).schedule_type_code;
259 x_del_sch_rec_tab(l_didx).assignment_status_code := p_sch_record_tab(l_iidx).assignment_status_code;
260 x_del_sch_rec_tab(l_didx).system_status_code := p_sch_record_tab(l_iidx).system_status_code;
261 x_del_sch_rec_tab(l_didx).start_date := p_sch_record_tab(l_iidx).start_date;
262 x_del_sch_rec_tab(l_didx).end_date := p_sch_record_tab(l_iidx).end_date;
263 x_del_sch_rec_tab(l_didx).monday_hours := p_sch_record_tab(l_iidx).monday_hours;
264 x_del_sch_rec_tab(l_didx).tuesday_hours := p_sch_record_tab(l_iidx).tuesday_hours;
265 x_del_sch_rec_tab(l_didx).wednesday_hours := p_sch_record_tab(l_iidx).wednesday_hours;
266 x_del_sch_rec_tab(l_didx).thursday_hours := p_sch_record_tab(l_iidx).thursday_hours;
267 x_del_sch_rec_tab(l_didx).friday_hours := p_sch_record_tab(l_iidx).friday_hours;
268 x_del_sch_rec_tab(l_didx).saturday_hours := p_sch_record_tab(l_iidx).saturday_hours;
269 x_del_sch_rec_tab(l_didx).sunday_hours := p_sch_record_tab(l_iidx).sunday_hours;
270 x_del_sch_rec_tab(l_didx).change_type_code := p_sch_record_tab(l_iidx).change_type_code;
271
272 l_didx := l_didx + 1;
273
274 ELSE
275
276 x_sch_record_tab(l_cidx).schrowid := p_sch_record_tab(l_iidx).schrowid;
277 x_sch_record_tab(l_cidx).schedule_id := p_sch_record_tab(l_iidx).schedule_id;
278 x_sch_record_tab(l_cidx).calendar_id := p_sch_record_tab(l_iidx).calendar_id;
279 x_sch_record_tab(l_cidx).assignment_id := p_sch_record_tab(l_iidx).assignment_id;
280 x_sch_record_tab(l_cidx).project_id := p_sch_record_tab(l_iidx).project_id;
281 x_sch_record_tab(l_cidx).schedule_type_code := p_sch_record_tab(l_iidx).schedule_type_code;
282 x_sch_record_tab(l_cidx).assignment_status_code := p_sch_record_tab(l_iidx).assignment_status_code;
283 x_sch_record_tab(l_cidx).system_status_code := p_sch_record_tab(l_iidx).system_status_code;
284 x_sch_record_tab(l_cidx).start_date := p_sch_record_tab(l_iidx).start_date;
285 x_sch_record_tab(l_cidx).end_date := p_sch_record_tab(l_iidx).end_date;
286 x_sch_record_tab(l_cidx).monday_hours := p_sch_record_tab(l_iidx).monday_hours;
287 x_sch_record_tab(l_cidx).tuesday_hours := p_sch_record_tab(l_iidx).tuesday_hours;
288 x_sch_record_tab(l_cidx).wednesday_hours := p_sch_record_tab(l_iidx).wednesday_hours;
289 x_sch_record_tab(l_cidx).thursday_hours := p_sch_record_tab(l_iidx).thursday_hours;
290 x_sch_record_tab(l_cidx).friday_hours := p_sch_record_tab(l_iidx).friday_hours;
291 x_sch_record_tab(l_cidx).saturday_hours := p_sch_record_tab(l_iidx).saturday_hours;
292 x_sch_record_tab(l_cidx).sunday_hours := p_sch_record_tab(l_iidx).sunday_hours;
293 x_sch_record_tab(l_cidx).change_type_code := p_sch_record_tab(l_iidx).change_type_code;
294
295 l_cidx := l_cidx + 1;
296
297 END IF;
298
299 IF (l_iidx = p_sch_record_tab.last) THEN
300 EXIT;
301 ELSE
302 l_iidx := p_sch_record_tab.next(l_iidx);
303 END IF;
304
305 END LOOP;
306
307 EXCEPTION
308 WHEN l_empty_tab_record THEN
309 x_return_status := FND_API.G_RET_STS_SUCCESS;
310 NULL;
311 WHEN OTHERS THEN
312 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
313 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_UTILS',
314 p_procedure_name => 'sep_del_sch_rec_tab');
315 raise;
316
317 END sep_del_sch_rec_tab;
318
319 -- This procedure will update records
320 -- Input parameters
321 -- Parameters Type Required Description
322 -- P_Project_Id NUMBER YES project id of the associated calendar
323 -- P_Calendar_Id NUMBER YES Id for that calendar which is associated to this assignmen
324 -- P_Assignment_Id NUMBER YES Assignment id of the schedule records
325 -- P_Schedule_Type_Code VARCHAR2 YES It is schedule type code e.g changed hours/changed duration
326 -- P_Assignment_Status_Code VARCHAR2 YES Status of the assignment e.g OPEN/CONFIRM/PROVISIONAL
327 -- P_Start_Date DATE YES stat date of the schedule from which schedule is to be updated
328 -- P_End_Date DATE YES end date of the schedule to which schedule is to be updated
329 -- P_Monday_Hours NUMBER YES No. of hours of this day
330 -- P_Tuesday_Hours NUMBER YES No. of hours of this day
331 -- P_Wednesday_Hours NUMBER YES No. of hours of this day
332 -- P_Thursday_Hours NUMBER YES No. of hours of this day
333 -- P_Friday_Hours NUMBER YES No. of hours of this day
334 -- P_Saturday_Hours NUMBER YES No. of hours of this day
335 -- P_Sunday_Hours NUMBER YES No. of hours of this day
336 -- P_Change_Type_Code VARCHAR2 YES It is change dtype code e.g U,I or D i.e. update insert
337 -- In Out parameters
338 -- PX_Sch_Record_Tab ScheduleTabTyp YES It stores the updated scheduled records
339 --
340
341 PROCEDURE update_sch_rec_tab ( px_sch_record_tab IN OUT NOCOPY PA_SCHEDULE_GLOB.ScheduleTabTyp,
342 p_project_id IN NUMBER DEFAULT NULL,
343 p_calendar_id IN NUMBER DEFAULT NULL,
344 p_assignment_id IN NUMBER DEFAULT NULL,
345 p_schedule_type_code IN VARCHAR2 DEFAULT NULL,
346 p_assignment_status_code IN VARCHAR2 DEFAULT NULL,
347 p_system_status_code IN VARCHAR2 DEFAULT NULL,
348 p_start_date IN DATE DEFAULT NULL,
349 p_end_date IN DATE DEFAULT NULL,
350 p_monday_hours IN NUMBER DEFAULT NULL,
351 p_tuesday_hours IN NUMBER DEFAULT NULL,
352 p_wednesday_hours IN NUMBER DEFAULT NULL,
353 p_thursday_hours IN NUMBER DEFAULT NULL,
354 p_friday_hours IN NUMBER DEFAULT NULL,
355 p_saturday_hours IN NUMBER DEFAULT NULL,
356 p_sunday_hours IN NUMBER DEFAULT NULL,
357 p_change_type_code IN VARCHAR2 DEFAULT NULL,
358 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
359 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
360 x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
361 IS
362
363 l_iidx NUMBER;
364
365 BEGIN
366 x_return_status := FND_API.G_RET_STS_SUCCESS;
367 IF (px_sch_record_tab.count = 0) THEN
368 Raise l_empty_tab_record;
369 ELSE
370 l_iidx := px_sch_record_tab.first;
371 END IF;
372
373
374 LOOP
375
376 /* Updating the Rows with the given data */
377 px_sch_record_tab(l_iidx).project_id := NVL(p_project_id,px_sch_record_tab(l_iidx).project_id);
378 px_sch_record_tab(l_iidx).calendar_id := NVL(p_calendar_id,px_sch_record_tab(l_iidx).calendar_id);
379 px_sch_record_tab(l_iidx).assignment_id := NVL(p_assignment_id,px_sch_record_tab(l_iidx).assignment_id);
380 px_sch_record_tab(l_iidx).schedule_type_code := NVL(p_schedule_type_code,px_sch_record_tab(l_iidx).schedule_type_code);
381 px_sch_record_tab(l_iidx).assignment_status_code := NVL(p_assignment_status_code,px_sch_record_tab(l_iidx).assignment_status_code);
382 px_sch_record_tab(l_iidx).system_status_code := NVL(p_system_status_code,px_sch_record_tab(l_iidx).system_status_code);
383 px_sch_record_tab(l_iidx).start_date := NVL(p_start_date,px_sch_record_tab(l_iidx).start_date);
384 px_sch_record_tab(l_iidx).end_date := NVL(p_end_date,px_sch_record_tab(l_iidx).end_date);
385 px_sch_record_tab(l_iidx).monday_hours := NVL(p_monday_hours,px_sch_record_tab(l_iidx).monday_hours);
386 px_sch_record_tab(l_iidx).tuesday_hours := NVL(p_tuesday_hours,px_sch_record_tab(l_iidx).tuesday_hours);
387 px_sch_record_tab(l_iidx).wednesday_hours := NVL(p_wednesday_hours,px_sch_record_tab(l_iidx).wednesday_hours);
388 px_sch_record_tab(l_iidx).thursday_hours := NVL(p_thursday_hours,px_sch_record_tab(l_iidx).thursday_hours);
389 px_sch_record_tab(l_iidx).friday_hours := NVL(p_friday_hours,px_sch_record_tab(l_iidx).friday_hours);
390 px_sch_record_tab(l_iidx).saturday_hours := NVL(p_saturday_hours,px_sch_record_tab(l_iidx).saturday_hours);
391 px_sch_record_tab(l_iidx).sunday_hours := NVL(p_sunday_hours,px_sch_record_tab(l_iidx).sunday_hours);
392 px_sch_record_tab(l_iidx).change_type_code := NVL(p_change_type_code,px_sch_record_tab(l_iidx).change_type_code);
393
394 IF (l_iidx = px_sch_record_tab.last) THEN
395 EXIT;
396 ELSE
397 l_iidx := px_sch_record_tab.next(l_iidx);
398 END IF;
399
400 END LOOP;
401
402 EXCEPTION
403 WHEN l_empty_tab_record THEN
404 x_return_status := FND_API.G_RET_STS_SUCCESS;
405 NULL;
406 WHEN OTHERS THEN
407 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
408 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_UTILS',
409 p_procedure_name => 'update_sch_rec_tab');
410 raise;
411
412
413 END update_sch_rec_tab;
414
415 -- This procedure will apply percentage on the basis of resource availabilty
416 -- Input parameters
417 -- Parameters Type Required Description
418 -- P_Percentage NUMBER YES It is percentage
419 -- In Out parameters
420 -- PX_Sch_Record_Tab ScheduleTabTyp YES It stores the updated scheduled records
421 --
422 PROCEDURE apply_percentage ( px_sch_record_tab IN OUT NOCOPY PA_SCHEDULE_GLOB.ScheduleTabTyp,
423 p_percentage IN NUMBER ,
424 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
425 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
426 x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
427 IS
428
429 l_iidx NUMBER;
430
431 BEGIN
432 x_return_status := FND_API.G_RET_STS_SUCCESS;
433 IF (px_sch_record_tab.count = 0) THEN
434 Raise l_empty_tab_record;
435 ELSE
436 l_iidx := px_sch_record_tab.first;
437 END IF;
438
439 LOOP
440
441 /* Applying percentage on the Rows with the given data */
442 px_sch_record_tab(l_iidx).monday_hours := (p_percentage/100) * ( px_sch_record_tab(l_iidx).monday_hours);
443 px_sch_record_tab(l_iidx).tuesday_hours := (p_percentage/100) * ( px_sch_record_tab(l_iidx).tuesday_hours);
444 px_sch_record_tab(l_iidx).wednesday_hours := (p_percentage/100) * ( px_sch_record_tab(l_iidx).wednesday_hours);
445 px_sch_record_tab(l_iidx).thursday_hours := (p_percentage/100) * ( px_sch_record_tab(l_iidx).thursday_hours);
446 px_sch_record_tab(l_iidx).friday_hours := (p_percentage/100) * ( px_sch_record_tab(l_iidx).friday_hours);
447 px_sch_record_tab(l_iidx).saturday_hours := (p_percentage/100) * ( px_sch_record_tab(l_iidx).saturday_hours);
448 px_sch_record_tab(l_iidx).sunday_hours := (p_percentage/100) * ( px_sch_record_tab(l_iidx).sunday_hours);
449
450 IF (l_iidx = px_sch_record_tab.last) THEN
451 EXIT;
452 ELSE
453 l_iidx := px_sch_record_tab.next(l_iidx);
454 END IF;
455
456 END LOOP;
457
458 EXCEPTION
459 WHEN l_empty_tab_record THEN
460 x_return_status := FND_API.G_RET_STS_SUCCESS;
461 NULL;
462 WHEN OTHERS THEN
463 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
464 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_UTILS',
465 p_procedure_name => 'apply_percentage');
466 raise;
467
468
469 END apply_percentage;
470
471
472 -- This procedure will copy the exception records
473 -- Input parameters
474 -- Parameters Type Required Description
475 -- P_Except_Record SchExceptRecord YES It contains the exceptions
476 -- Out Parameters
477 -- X_Except_Record SchExceptRecord YES It stores the copied exception records
478 --
479
480 PROCEDURE copy_except_record ( p_except_record IN PA_SCHEDULE_GLOB.SchExceptRecord,
481 x_except_record OUT NOCOPY PA_SCHEDULE_GLOB.SchExceptRecord,
482 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
483 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
484 x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
485 IS
486
487
488 BEGIN
489
490 x_return_status := FND_API.G_RET_STS_SUCCESS;
491
492 /* Copying all the structure elements */
493
494 x_except_record.exceptRowid := p_except_record.exceptRowid;
495 x_except_record.schedule_exception_id := p_except_record.schedule_exception_id;
496 x_except_record.calendar_id := p_except_record.calendar_id;
497 x_except_record.assignment_id := p_except_record.assignment_id;
498 x_except_record.project_id := p_except_record.project_id;
499 x_except_record.exception_type_code := p_except_record.exception_type_code;
500 x_except_record.schedule_type_code := p_except_record.schedule_type_code;
501 x_except_record.assignment_status_code := p_except_record.assignment_status_code;
502 x_except_record.start_date := p_except_record.start_date;
503 x_except_record.end_date := p_except_record.end_date;
504 x_except_record.resource_calendar_percent := p_except_record.resource_calendar_percent;
505 x_except_record.non_working_day_flag := p_except_record.non_working_day_flag;
506 x_except_record.change_hours_type_code := p_except_record.change_hours_type_code;
507 x_except_record.change_calendar_type_code := p_except_record.change_calendar_type_code;
508 --x_except_record.change_calendar_name := p_except_record.change_calendar_name;
509 x_except_record.change_calendar_id := p_except_record.change_calendar_id;
510 x_except_record.duration_shift_type_code := p_except_record.duration_shift_type_code;
511 x_except_record.duration_shift_unit_code := p_except_record.duration_shift_unit_code;
512 x_except_record.number_of_shift := p_except_record.number_of_shift;
513 x_except_record.monday_hours := p_except_record.monday_hours;
514 x_except_record.tuesday_hours := p_except_record.tuesday_hours;
515 x_except_record.wednesday_hours := p_except_record.wednesday_hours;
516 x_except_record.thursday_hours := p_except_record.thursday_hours;
517 x_except_record.friday_hours := p_except_record.friday_hours;
518 x_except_record.saturday_hours := p_except_record.saturday_hours;
519 x_except_record.sunday_hours := p_except_record.sunday_hours;
520
521 EXCEPTION
522 WHEN OTHERS THEN
523 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
524 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_UTILS',
525 p_procedure_name => 'copy_except_record');
526 raise;
527
528 END copy_except_record;
529
530 -- This procedure will update the exception records
531 -- Input parameters
532 -- Parameters Type Required Description
533 -- P_Project_Id NUMBER YES project id of the associated calendar
534 -- P_Calendar_Id NUMBER YES Id for that calendar which is associated to this assignmen
535 -- P_Assignment_Id NUMBER YES Assignment id of the exception records
536 -- P_Schedule_Type_Code VARCHAR2 YES It is schedule type code e.g changed hours/changed duration
537 -- P_Assignment_Status_Code VARCHAR2 YES Status of the assignment e.g OPEN/CONFIRM/PROVISIONAL
538 -- P_Start_Date DATE YES stat date of the schedule from which exception is to
539 -- be updated
540 -- P_End_Date DATE YES end date of the schedule to which exception is to be updated
541 -- P_Resource_Calendar_Percent NUMBER YES it is the resource calendar percentage
542 -- P_Non_Working_Flag VARCHAR2 YES It is non working day flag which means should include or not
543 -- i.e Y,N.
544 -- P_Change_Hours_Type_Code VARCHAR2 YES It is change hours type code which is used when changeing the
545 -- hours e.g. HOURS or PERCENTAGE
546 -- P_Monday_Hours NUMBER YES No. of hours of this day
547 -- P_Tuesday_Hours NUMBER YES No. of hours of this day
548 -- P_Wednesday_Hours NUMBER YES No. of hours of this day
549 -- P_Thursday_Hours NUMBER YES No. of hours of this day
550 -- P_Friday_Hours NUMBER YES No. of hours of this day
551 -- P_Saturday_Hours NUMBER YES No. of hours of this day
552 -- P_Sunday_Hours NUMBER YES No. of hours of this day
553 -- In Out parameters
554 -- PX_Except_Record SchExceptRecord YES It stores the updated exception records
555 --
556 PROCEDURE update_except_record( px_except_record IN OUT NOCOPY PA_SCHEDULE_GLOB.SchExceptRecord,
557 p_project_id IN NUMBER DEFAULT NULL,
558 p_calendar_id IN NUMBER DEFAULT NULL,
559 p_assignment_id IN NUMBER DEFAULT NULL,
560 p_schedule_type_code IN VARCHAR2 DEFAULT NULL,
561 p_assignment_status_code IN VARCHAR2 DEFAULT NULL,
562 p_start_date IN DATE DEFAULT NULL,
563 p_end_date IN DATE DEFAULT NULL,
564 p_resource_calendar_percent IN NUMBER DEFAULT NULL,
565 p_non_working_day_flag IN VARCHAR2 DEFAULT NULL,
566 p_change_hours_type_code IN VARCHAR2 DEFAULT NULL,
567 p_monday_hours IN NUMBER DEFAULT NULL,
568 p_tuesday_hours IN NUMBER DEFAULT NULL,
569 p_wednesday_hours IN NUMBER DEFAULT NULL,
570 p_thursday_hours IN NUMBER DEFAULT NULL,
571 p_friday_hours IN NUMBER DEFAULT NULL,
572 p_saturday_hours IN NUMBER DEFAULT NULL,
573 p_sunday_hours IN NUMBER DEFAULT NULL,
574 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
575 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
576 x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
577 IS
578
579
580 BEGIN
581
582 x_return_status := FND_API.G_RET_STS_SUCCESS;
583
584 px_except_record.project_id := NVL(p_project_id,px_except_record.project_id);
585 px_except_record.calendar_id := NVL(p_calendar_id,px_except_record.calendar_id);
586 px_except_record.assignment_id := NVL(p_assignment_id,px_except_record.assignment_id);
587 px_except_record.schedule_type_code := NVL(p_schedule_type_code,px_except_record.schedule_type_code);
588 px_except_record.assignment_status_code := NVL(p_assignment_status_code,px_except_record.assignment_status_code);
589 px_except_record.start_date := NVL(p_start_date,px_except_record.start_date);
590 px_except_record.end_date := NVL(p_end_date,px_except_record.end_date);
591 px_except_record.monday_hours := NVL(p_monday_hours,px_except_record.monday_hours);
592 px_except_record.tuesday_hours := NVL(p_tuesday_hours,px_except_record.tuesday_hours);
593 px_except_record.wednesday_hours := NVL(p_wednesday_hours,px_except_record.wednesday_hours);
594 px_except_record.thursday_hours := NVL(p_thursday_hours,px_except_record.thursday_hours);
595 px_except_record.friday_hours := NVL(p_friday_hours,px_except_record.friday_hours);
596 px_except_record.saturday_hours := NVL(p_saturday_hours,px_except_record.saturday_hours);
597 px_except_record.sunday_hours := NVL(p_sunday_hours,px_except_record.sunday_hours);
598
599 EXCEPTION
600 WHEN OTHERS THEN
601 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
602 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_UTILS',
603 p_procedure_name => 'update_except_record');
604 raise;
605
606
607 END update_except_record;
608
609 -- This procedure will print the passed message it is overloaded procedure
610 -- Input parameters
611 -- Parameters Type Required Description
612 -- P_Level1 NUMBER YES it is level used for suppresing the message
613 -- P_Msg1 VARCHAR2 YES it is used to take message text
614 --
615
616 PROCEDURE log_message( level1 IN NUMBER,
617 msg1 IN VARCHAR2)
618
619 IS
620 BEGIN
621 /*
622 IF( level1 <= 10 ) THEN
623 IF ( level1 = 2 ) THEN
624 DBMS_OUTPUT.PUT_LINE('2...........'||msg1);
625 ELSE
626 DBMS_OUTPUT.PUT_LINE(msg1);
627 END IF;
628 END IF;
629 */
630
631 IF (P_DEBUG_MODE ='Y') THEN
632 PA_DEBUG.WRITE_LOG(
633 x_module => 'pa.plsql.pa_schedule_pub',
634 x_msg => msg1,
635 x_log_level => 6);
636 pa_debug.write_file('print_message: ' || 'Log :'||msg1);
637 end if;
638 END log_message;
639
640 PROCEDURE debug(p_module IN VARCHAR2,
641 p_msg IN VARCHAR2,
642 p_log_level IN NUMBER DEFAULT 6) IS
643 BEGIN
644 -- dbms_output.put_line('log : ' || p_module || ' : ' || p_msg);
645 IF (P_DEBUG_MODE ='Y') THEN
646 PA_DEBUG.WRITE_LOG(
647 x_module => p_module,
648 x_msg => p_msg,
649 x_log_level => p_log_level);
650 pa_debug.write_file('print_message: ' || 'Log :'||p_msg);
651 end if;
652 END debug;
653
654
655 PROCEDURE debug(p_msg IN VARCHAR2) IS
656 BEGIN
657 -- dbms_output.put_line('log : '||'pa.plsql.pa_schedule_pvt'|| ' : ' || p_msg);
658 IF (P_DEBUG_MODE ='Y') THEN
659 PA_DEBUG.WRITE_LOG(
660 x_module => 'pa.plsql.pa_schedule_pvt',
661 x_msg => p_msg,
662 x_log_level => 6);
663 pa_debug.write_file('print_message: ' || 'Log :'||p_msg);
664 End If;
665 END debug;
666
667
668 -- This procedure will print the passed message and the structure of the table of records. it is overloaded procedure
669 -- Input parameters
670 -- Parameters Type Required Description
671 -- P_Level1 NUMBER YES it is level used for suppresing the message
672 -- P_Msg1 VARCHAR2 YES it is used to take message text
673 -- P_Wr_Tab ScheduleTabTyp YES it is used to print the table structure column
674 --
675
676 PROCEDURE log_message( level1 IN NUMBER,
677 msg1 IN VARCHAR2,
678 wr_tab IN PA_SCHEDULE_GLOB.ScheduleTabTyp)
679
680 IS
681 BEGIN
682 null;
683 /*
684 IF (pa_schedule_utils.l_print_log ) then
685 IF ( level1 <= 1000 ) then
686 IF ( wr_tab.count > 0 ) then
687 FOR i IN wr_tab.first..wr_tab.last LOOP
688
689 DBMS_OUTPUT.PUT_LINE(msg1||' '||to_char(wr_tab(i).start_date)||' '||to_char(wr_tab(i).end_date)
690 ||' chg_typ: ' ||wr_tab(i).change_type_code||' mon_hrs: '
691 ||to_char(wr_tab(i).monday_hours)||'cal id '||to_char(wr_tab(i).calendar_id));
692 END LOOP;
693 ELSE
694 DBMS_OUTPUT.PUT_LINE(msg1||' COUNT is '||wr_tab.count);
695 END IF;
696 END IF;
697 END IF;
698 */
699 END log_message;
700
701 -- This procedure will validate the passed date
702 -- Input parameters
703 -- Parameters Type Required Description
704 -- P_From_Date DATE YES it is from date
705 -- P_To_Date DATE YES it is to date
706 --
707
708 PROCEDURE validate_date_range( p_from_date IN DATE,
709 p_to_date IN DATE,
710 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
711 x_error_message_code OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
712 IS
713 BEGIN
714
715 -- Storing the value for error tracking
716 x_return_status := FND_API.G_RET_STS_SUCCESS;
717
718 /* validating the date for starting date should not be more then the ending date */
719 IF (( p_from_date IS NOT NULL ) AND ( p_to_date IS NOT NULL )) THEN
720 IF (p_from_date <= p_to_date ) THEN
721 NULL;
722 ELSE
723 x_return_status := FND_API.G_RET_STS_ERROR;
724 x_error_message_code := 'PA_SCH_INVALID_DATE_RANGE';
725 --DBMS_OUTPUT.PUT_LINE('DATE ERROR '||x_return_status);
726 END IF;
727 END IF;
728
729 EXCEPTION
730 WHEN OTHERS THEN
731 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
732 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_UTILS',
733 p_procedure_name => 'validate_date_range');
734 raise;
735
736 END validate_date_range;
737
738 -- Function : Get_num_hours
739 -- Purpose : This function returns the number of hours scheduled
740 -- for this assignment. We are also requiring
741 -- project_id for performance.
742
743 FUNCTION get_num_hours( p_project_id IN NUMBER,
744 p_assignment_id IN NUMBER) RETURN NUMBER
745 IS
746 l_num_hours NUMBER := null;
747
748 BEGIN
749 SELECT sum(item_quantity)
750 INTO l_num_hours
751 FROM pa_forecast_items
752 WHERE assignment_id = p_assignment_id
753 AND delete_flag = 'N';
754
755 l_num_hours := NVL(l_num_hours, 0);
756 return(l_num_hours);
757
758 END get_num_hours;
759
760
761 -- Function : Get_res_calendar
762 -- Purpose : Returns the calendar_id for the
763 -- calendar associated with this resource for the
764 -- start and end date specified. Returns null
765 -- if 0 or more than 1 calendar is specified for
766 -- the given dates.
767 FUNCTION get_res_calendar( p_resource_id IN NUMBER,
768 p_start_date IN DATE,
769 p_end_date IN DATE) RETURN NUMBER
770 IS
771 l_cal_id NUMBER;
772 l_tc_start_date DATE;
773 l_tc_end_date DATE;
774 l_jtf_res_id NUMBER;
775 l_temp_start_date DATE;
776 l_temp_end_date DATE;
777 l_invalid_resource_id EXCEPTION;
778 l_count NUMBER;
779 x_return_status VARCHAR2(30);
780 x_msg_data VARCHAR2(250);
781
782 -- jmarques: 1965288: local vars
783 l_resource_organization_id NUMBER;
784 l_resource_ou_id NUMBER;
785 l_calendar_id NUMBER;
786
787 -- jmarques: 1786935: Modified cursor to include resource_type_code
788 -- since resource_id is not unique. Also, added calendar_id > 0
789 -- condition so that calendar_id, resource_id index would be used.
790
791 CURSOR C1 IS SELECT calendar_id
792 FROM jtf_cal_resource_assign jtf_res
793 WHERE jtf_res.resource_id = l_jtf_res_id
794 AND jtf_res.resource_type_code = 'RS_EMPLOYEE'
795 AND jtf_res.primary_calendar_flag = 'Y'
796 AND jtf_res.calendar_id > 0
797 AND ( ( l_tc_start_date BETWEEN trunc(jtf_res.start_date_time) AND
798 nvl(trunc(jtf_res.end_date_time),l_tc_end_date))
799 OR ( l_tc_end_date BETWEEN jtf_res.start_date_time AND
800 nvl(trunc(jtf_res.end_date_time),l_tc_end_date))
801 OR ( l_tc_start_date < jtf_res.start_date_time AND
802 l_tc_end_date > nvl(trunc(jtf_res.end_date_time),l_tc_end_date)) ) ;
803
804 BEGIN
805 -- Calling resource API That will return the resource id
806 PA_RESOURCE_UTILS.get_crm_res_id( p_project_player_id => NULL,
807 p_resource_id => p_resource_id,
808 x_jtf_resource_id => l_jtf_res_id,
809 x_return_status => x_return_status,
810 x_error_message_code => x_msg_data );
811
812 IF ( x_return_status <> fnd_api.g_ret_sts_success ) THEN
813 RAISE l_invalid_resource_id;
814 END IF;
815
816 PA_SCHEDULE_UTILS.log_message(1,'JTF Res ID: ' || l_jtf_res_id);
817 PA_SCHEDULE_UTILS.log_message(1,'status ... '||x_return_status);
818
819 -- 1965288: Work around for CRM bug (cannot create future dated resources).
820 -- If the jtf_resource_id is null, then we need to use the default
821 -- calendar instead of going to the jtf_cal_resource_assign table.
822
823 -- Start 1965288 bugfix.
824 if (l_jtf_res_id is null) then
825
826 -- Get resource's organization on their
827 -- min(resource_effective_start_date)
828 select resource_organization_id
829 into l_resource_organization_id
830 from pa_resources_denorm
831 where resource_effective_start_date =
832 (select min(res1.resource_effective_start_date)
833 from pa_resources_denorm res1
834 where res1.resource_id = p_resource_id)
835 and resource_id = p_resource_id;
836
837 -- Get default calendar using organization id.
838 pa_resource_utils.get_org_defaults(
839 p_organization_id => l_resource_organization_id,
840 x_default_ou => l_resource_ou_id,
841 x_default_cal_id => l_calendar_id,
842 x_return_status => x_return_status);
843
844 if (l_calendar_id is null) then
845 l_calendar_id := fnd_profile.value_specific('PA_PRM_DEFAULT_CALENDAR');
846 end if;
847
848 return l_calendar_id;
849
850 -- End 1965288 bug fix.
851
852 else
853 -- Taking care if the passing start or end date is null if the dates are null take the value from table
854
855 -- jmarques: 1786935: Modified cursor to include resource_type_code
856 -- since resource_id is not unique. Also, added calendar_id > 0
857 -- condition so that calendar_id, resource_id index would be used.
858
859 IF (p_start_date IS NULL OR p_end_date IS NULL) THEN
860 SELECT MIN(start_date_time),MAX(NVL(end_date_time,TO_DATE('01/01/2050','MM/DD/YYYY')))
861 INTO l_temp_start_date,l_temp_end_date
862 FROM jtf_cal_resource_assign
863 WHERE jtf_cal_resource_assign.resource_id = l_jtf_res_id
864 AND jtf_cal_resource_assign.resource_type_code = 'RS_EMPLOYEE'
865 AND jtf_cal_resource_assign.calendar_id > 0
866 AND jtf_cal_resource_assign.primary_calendar_flag = 'Y';
867 END IF;
868
869 PA_SCHEDULE_UTILS.log_message(1,'Start date ... '||to_char(l_temp_start_date)||to_char(p_start_date));
870 PA_SCHEDULE_UTILS.log_message(1,'end date ... '||to_char(l_temp_end_date)||to_char(p_end_date));
871 IF (p_start_date IS NULL ) THEN
872 l_tc_start_date := l_temp_start_date;
873 ELSE
874 l_tc_start_date := p_start_date;
875 END IF;
876
877 IF (p_end_date IS NULL ) THEN
878 l_tc_end_date := l_temp_end_date;
879 ELSE
880 l_tc_end_date := p_end_date;
881 END IF;
882
883 l_count := 0;
884 FOR v_c1 IN C1 LOOP
885 l_cal_id := v_c1.calendar_id;
886 l_count := l_count + 1;
887 END LOOP;
888
889 IF l_count = 1 THEN
890 return l_cal_id;
891 ELSE
892 return null;
893 END IF;
894 END IF;
895
896 END get_res_calendar;
897
898
899 -- Function : Get_res_calendar_name
900 -- Purpose : Returns the calendar_name for the
901 -- calendar associated with this resource for the
902 -- given date. Returns null
903 -- if 0 or more than 1 calendar is specified for
904 -- the given dates.
905
906 FUNCTION get_res_calendar_name( p_resource_id IN NUMBER,
907 p_date IN DATE,
908 p_person_id IN NUMBER DEFAULT NULL) RETURN VARCHAR2
909 IS
910 l_calendar_name VARCHAR2(50) := NULL;
911 l_resource_id NUMBER := NULL;
912 BEGIN
913
914 IF p_person_id IS NOT NULL THEN
915 SELECT resource_id
916 INTO l_resource_id
917 FROM PA_RESOURCES_DENORM
918 WHERE person_id = p_person_id
919 AND rownum = 1;
920 ELSE l_resource_id := p_resource_id;
921 END IF;
922
923 IF l_resource_id IS NULL THEN
924 RETURN NULL;
925 END IF;
926
927 SELECT jtf_cal.calendar_name
928 INTO l_calendar_name
929 FROM jtf_cal_resource_assign jtf_cal_res,
930 jtf_calendars_vl jtf_cal,
931 pa_resources res
932 WHERE res.resource_id = l_resource_id
933 AND jtf_cal_res.resource_id = res.jtf_resource_id
934 AND jtf_cal_res.resource_type_code = 'RS_EMPLOYEE'
935 AND jtf_cal_res.primary_calendar_flag = 'Y'
936 AND jtf_cal_res.calendar_id > 0
937 AND p_date BETWEEN jtf_cal_res.start_date_time
938 AND nvl(jtf_cal_res.end_date_time, p_date+1)
939 AND jtf_cal_res.calendar_id = jtf_cal.calendar_id;
940
941 return l_calendar_name;
942
943 EXCEPTION
944 WHEN NO_DATA_FOUND THEN
945 return l_calendar_name;
946 WHEN OTHERS THEN
947 return l_calendar_name;
948
949 END get_res_calendar_name;
950
951
952 -- Returns 'Y' if requirement/assignment is in the desired system
953 -- status for the entire duration of the requirement/assignment.
954 -- Otherwise returns 'N'.
955 -- p_assignment_id - assignment/requirement id
956 -- p_status_type - The value is either 'OPEN_ASGMT'
957 -- or 'STAFFED_ASGMT'. Please see pa_project_statuses.status_type
958 -- for list of current values.
959 -- p_in_system_status_code - Desired system status code.
960
961 FUNCTION check_input_system_status
962 (p_assignment_id IN pa_project_assignments.assignment_id%TYPE,
963 p_status_type IN pa_project_statuses.status_type%TYPE,
964 p_in_system_status_code IN pa_project_statuses.project_system_status_code%TYPE)
965 return VARCHAR2 IS
966
967 l_flag VARCHAR2(1) := 'Y';
968 CURSOR l_sch_csr IS
969 SELECT status_code
970 FROM pa_schedules
971 WHERE assignment_id = p_assignment_id;
972
973 BEGIN
974 FOR l_sch_rec IN l_sch_csr LOOP
975 l_flag := pa_assignment_utils.check_input_system_status(
976 p_status_code => l_sch_rec.status_code,
977 p_status_type => p_status_type,
978 p_in_system_status_code => p_in_system_status_code);
979 IF l_flag = 'N' THEN
980 RETURN l_flag;
981 END IF;
982 END LOOP;
983 RETURN l_flag;
984 EXCEPTION
985 WHEN OTHERS THEN
986 RAISE;
987 END check_input_system_status;
988
989 -- Returns x_return_status = FND_API.G_RET_STS_SUCCESS if calendar(s)
990 -- assigned to p_resource_id or jtf_resource_id are valid. By valid we mean:
991 -- * For all primary calendars assigned to the resource,
992 -- min(start_date_time) <= max(p_start_date,sysdate-avail duration) and
993 -- max(end_date_time) >= min(p_end_date, sysdate+avail duration).
994 -- Otherwise, adds error: PA_NO_ACTIVE_CALENDAR
995 -- * All active calendars must be contiguous. If they overlap, then
996 -- adds error: PA_OVERLAPPING_CALENDARS. If there is a gap, then
997 -- adds error: PA_NO_ACTIVE_CALENDAR
998 -- * For all primary calendars assigned to the resource that overlap
999 -- p_start_date to p_end_date, call check_calendar(calendar_id)
1000 -- passing in max(start_date_time,max(p_start_date,sysdate-avail duration))
1001 -- and min(end_date_time,min(p_end_date,sysdate+avail duration)).
1002 -- Otherwise x_return_status <> FND_API.G_RET_STS_SUCCESS.
1003 PROCEDURE check_calendar(p_resource_id IN NUMBER := null,
1004 p_jtf_resource_id IN NUMBER := null,
1005 p_start_date IN DATE,
1006 p_end_date IN DATE,
1007 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1008 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1009 x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
1010 IS
1011 l_first_cal_flag VARCHAR2(1) := 'Y';
1012 l_msg_index_out NUMBER;
1013 l_avail_duration NUMBER := FND_NUMBER.CANONICAL_TO_NUMBER(FND_PROFILE.VALUE('PA_AVAILABILITY_DURATION'));
1014 l_start_date DATE;
1015 l_end_date DATE;
1016 l_cal_start_date DATE;
1017 l_cal_end_date DATE;
1018 l_prev_cal_start_date DATE;
1019 l_prev_cal_end_date DATE;
1020 l_error_start_date DATE;
1021 l_error_end_date DATE;
1022 l_check_cal_start_date DATE;
1023 l_check_cal_end_date DATE;
1024 l_no_active_calendar EXCEPTION;
1025 l_duplicate_calendars EXCEPTION;
1026 l_invalid_param EXCEPTION;
1027 l_error_message VARCHAR2(200);
1028 --Bug: 4537865
1029 l_new_msg_data VARCHAR2(2000);
1030 --Bug: 4537865
1031
1032 -- The select statement takes care of two cases (passing in
1033 -- p_jtf_resource_id and passing in p_resource_id)
1034 CURSOR C_CAL IS
1035 SELECT calendar_id, start_date_time, end_date_time
1036 FROM (
1037 SELECT jtf_res.calendar_id calendar_id,
1038 NVL(jtf_res.start_date_time, l_start_date) start_date_time,
1039 NVL(jtf_res.end_date_time, l_end_date) end_date_time
1040 FROM jtf_cal_resource_assign jtf_res, pa_resources pa_res
1041 WHERE pa_res.resource_id = p_resource_id
1042 and jtf_res.resource_id = pa_res.jtf_resource_id
1043 and jtf_res.resource_type_code = 'RS_EMPLOYEE'
1044 and jtf_res.calendar_id > -1
1045 and jtf_res.primary_calendar_flag = 'Y'
1046 and NVL(jtf_res.start_date_time,l_start_date) <= l_end_date
1047 and NVL(jtf_res.end_date_time,l_end_date) >= l_start_date
1048 and p_resource_id is not null
1049 UNION ALL
1050 SELECT jtf_res.calendar_id calendar_id,
1051 NVL(jtf_res.start_date_time, l_start_date) start_date_time,
1052 NVL(jtf_res.end_date_time, l_end_date) end_date_time
1053 FROM jtf_cal_resource_assign jtf_res
1054 WHERE jtf_res.resource_id = p_jtf_resource_id
1055 and jtf_res.resource_type_code = 'RS_EMPLOYEE'
1056 and jtf_res.calendar_id > -1
1057 and jtf_res.primary_calendar_flag = 'Y'
1058 and NVL(jtf_res.start_date_time,l_start_date) <= l_end_date
1059 and NVL(jtf_res.end_date_time,l_end_date) >= l_start_date
1060 and p_jtf_resource_id is not null)
1061 order by start_date_time;
1062
1063 rec_cal c_cal%ROWTYPE;
1064 BEGIN
1065 log_message(1,'Entering check_calendar for resource');
1066 x_return_status := FND_API.G_RET_STS_SUCCESS;
1067
1068 if (p_resource_id is not null and p_jtf_resource_id is not null) then
1069 l_error_message := 'p_resource_id and p_jtf_resource_id cannot both be not null.';
1070 raise l_invalid_param;
1071 end if;
1072
1073 if (p_resource_id is null and p_jtf_resource_id is null) then
1074 l_error_message := 'p_resource_id and p_jtf_resource_id cannot both be null.';
1075 raise l_invalid_param;
1076 end if;
1077
1078 l_start_date := ADD_MONTHS(sysdate,-(l_avail_duration*12));
1079 if (p_start_date > l_start_date) then
1080 l_start_date := p_start_date;
1081 end if;
1082 log_message(1,'l_start_date: ' || l_start_date);
1083
1084 l_end_date := ADD_MONTHS(sysdate,(l_avail_duration*12));
1085 if (l_end_date > p_end_date) then
1086 l_end_date := p_end_date;
1087 end if;
1088 log_message(1,'l_end_date: ' || l_end_date);
1089
1090 -- Bug 2202654: No need to check calendar if l_start_date or l_end_date is outside
1091 -- the window.
1092 if (l_start_date > ADD_MONTHS(sysdate,(l_avail_duration*12))
1093 OR l_end_date < ADD_MONTHS(sysdate,-(l_avail_duration*12))) then
1094 RETURN;
1095 end if;
1096
1097 log_message(1,'Entering calendar loop');
1098 FOR rec_cal in c_cal LOOP
1099 if (x_return_status = FND_API.G_RET_STS_SUCCESS) then
1100 l_cal_start_date := trunc(rec_cal.start_date_time);
1101 l_cal_end_date := trunc(rec_cal.end_date_time);
1102 log_message(1,'l_cal_start_date: ' || l_cal_start_date);
1103 log_message(1,'l_cal_end_date: ' || l_cal_end_date);
1104
1105 if (l_first_cal_flag = 'Y' and l_cal_start_date > l_start_date) then
1106 l_error_start_date := l_start_date;
1107 l_error_end_date := l_cal_start_date-1;
1108 raise l_no_active_calendar;
1109
1110 elsif (l_first_cal_flag = 'N' and
1111 l_cal_start_date > l_prev_cal_end_date+1) then
1112 l_error_start_date := l_prev_cal_end_date+1;
1113 l_error_end_date := l_cal_start_date-1;
1114 raise l_no_active_calendar;
1115
1116 elsif (l_first_cal_flag = 'N' and
1117 l_cal_start_date < l_prev_cal_end_date+1) then
1118 l_error_start_date := l_cal_start_date;
1119 if (l_prev_cal_start_date > l_cal_start_date) then
1120 l_error_start_date := l_prev_cal_start_date;
1121 end if;
1122 l_error_end_date := l_prev_cal_end_date;
1123 raise l_duplicate_calendars;
1124 end if;
1125
1126 l_first_cal_flag := 'N';
1127 l_prev_cal_start_date := l_cal_start_date;
1128 l_prev_cal_end_date := l_cal_end_date;
1129
1130 l_check_cal_start_date := l_cal_start_date;
1131 if (l_cal_start_date < l_start_date) then
1132 l_check_cal_start_date := l_start_date;
1133 end if;
1134 log_message(1,'l_check_cal_start_date: ' || l_check_cal_start_date);
1135
1136 l_check_cal_end_date := l_cal_end_date;
1137 if (l_cal_end_date > l_end_date) then
1138 l_check_cal_end_date := l_end_date;
1139 end if;
1140 log_message(1,'l_check_cal_end_date: ' || l_check_cal_end_date);
1141
1142 check_calendar(
1143 p_calendar_id => rec_cal.calendar_id,
1144 p_start_date => l_check_cal_start_date,
1145 p_end_date => l_check_cal_end_date,
1146 x_return_status => x_return_status,
1147 x_msg_count => x_msg_count,
1148 x_msg_data => x_msg_data);
1149 END IF;
1150 END LOOP;
1151 log_message(1,'Done calendar loop');
1152
1153 if (l_first_cal_flag = 'Y') then
1154 l_error_start_date := l_start_date;
1155 l_error_end_date := l_end_date;
1156 raise l_no_active_calendar;
1157 -- Bug 2202654: Added trunc() before doing date comparison.
1158 elsif (trunc(l_end_date) > trunc(l_prev_cal_end_date)) then
1159 l_error_start_date := l_prev_cal_end_date + 1;
1160 l_error_end_date := l_end_date;
1161 raise l_no_active_calendar;
1162 end if;
1163
1164 log_message(1,'Leaving check_calendar for resource');
1165 EXCEPTION
1166 WHEN l_invalid_param THEN
1167 PA_SCHEDULE_UTILS.log_message(1,'ERROR in check_calendar: '|| l_error_message);
1168 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1169 x_msg_count := 1;
1170 x_msg_data := substr(l_error_message,1,240);
1171 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_UTILS',
1172 p_procedure_name => 'check_calendar');
1173 If x_msg_count = 1 THEN
1174 pa_interface_utils_pub.get_messages
1175 (p_encoded => FND_API.G_TRUE,
1176 p_msg_index => 1,
1177 p_msg_count => x_msg_count,
1178 p_msg_data => x_msg_data,
1179 --p_data => x_msg_data, * Bug: 4537865
1180 p_data => l_new_msg_data, --Bug: 4537865
1181 p_msg_index_out => l_msg_index_out );
1182
1183 --Bug: 4537865
1184 x_msg_data := l_new_msg_data;
1185 --Bug: 4537865
1186 End If;
1187 RAISE;
1188 WHEN l_no_active_calendar THEN
1189 PA_SCHEDULE_UTILS.log_message(1,'ERROR: l_no_active_calendar');
1190 PA_UTILS.add_message('PA','PA_NO_ACTIVE_CALENDAR',
1191 'START_DATE', l_error_start_date,
1192 'END_DATE', l_error_end_date);
1193 x_return_status := FND_API.G_RET_STS_ERROR;
1194 x_msg_data := 'PA_NO_ACTIVE_CALENDAR';
1195 x_msg_count := FND_MSG_PUB.Count_Msg;
1196 If x_msg_count = 1 THEN
1197 pa_interface_utils_pub.get_messages
1198 (p_encoded => FND_API.G_TRUE,
1199 p_msg_index => 1,
1200 p_msg_count => x_msg_count,
1201 p_msg_data => x_msg_data,
1202 --p_data => x_msg_data, * bug: 4537865
1203 p_data => l_new_msg_data, --bug: 4537865
1204 p_msg_index_out => l_msg_index_out );
1205 --Bug: 4537865
1206 x_msg_data := l_new_msg_data;
1207 --Bug: 4537865
1208 End If;
1209 WHEN l_duplicate_calendars THEN
1210 PA_SCHEDULE_UTILS.log_message(1,'ERROR: l_duplicate_calendars');
1211 PA_UTILS.add_message('PA','PA_DUPLICATE_CALENDARS',
1212 'START_DATE', l_error_start_date,
1213 'END_DATE', l_error_end_date);
1214 x_return_status := FND_API.G_RET_STS_ERROR;
1215 x_msg_data := 'PA_DUPLICATE_CALENDARS';
1216 x_msg_count := FND_MSG_PUB.Count_Msg;
1217 If x_msg_count = 1 THEN
1218 pa_interface_utils_pub.get_messages
1219 (p_encoded => FND_API.G_TRUE,
1220 p_msg_index => 1,
1221 p_msg_count => x_msg_count,
1222 p_msg_data => x_msg_data,
1223 --p_data => x_msg_data, * bug: 4537865
1224 p_data => l_new_msg_data, --bug: 4537865
1225 p_msg_index_out => l_msg_index_out );
1226 --bug: 4537865
1227 x_msg_data := l_new_msg_data;
1228 --bug: 4537865
1229 End If;
1230 WHEN OTHERS THEN
1231 PA_SCHEDULE_UTILS.log_message(1,'ERROR in check_calendar: '||sqlerrm);
1232 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1233 x_msg_count := 1;
1234 x_msg_data := substr(SQLERRM,1,240);
1235 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_UTILS',
1236 p_procedure_name => 'check_calendar');
1237 If x_msg_count = 1 THEN
1238 pa_interface_utils_pub.get_messages
1239 (p_encoded => FND_API.G_TRUE,
1240 p_msg_index => 1,
1241 p_msg_count => x_msg_count,
1242 p_msg_data => x_msg_data,
1243 --p_data => x_msg_data, * bug: 4537865
1244 p_data => l_new_msg_data, --bug: 4537865
1245 p_msg_index_out => l_msg_index_out );
1246 --bug: 4537865
1247 x_msg_data := l_new_msg_data;
1248 --bug: 4537865
1249 End If;
1250 RAISE;
1251 END check_calendar;
1252
1253 -- Returns x_return_status = FND_API.G_RET_STS_SUCCESS if p_calendar_id
1254 -- is valid between p_start_date and p_end_date. By valid we mean:
1255 -- * A schedule record exists for the calendar.
1256 -- Otherwise, adds error: PA_MISSING_CALENDAR_SCHEDULES
1257 -- * The calendar start date is <= p_start_date.
1258 -- Otherwise, adds error: PA_CALENDAR_NOT_ACTIVE
1259 -- * The calendar end date is >= p_end_date.
1260 -- Otherwise, adds error: PA_CALENDAR_NOT_ACTIVE
1261 -- * The calendar has at least 1 non-zero shift.
1262 -- Otherwise, adds error: PA_CAL_MISSING_VALID_SHIFT
1263 -- Otherwise x_return_status <> FND_API.G_RET_STS_SUCCESS.
1264 PROCEDURE check_calendar(p_calendar_id IN NUMBER,
1265 p_start_date IN DATE,
1266 p_end_date IN DATE,
1267 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1268 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1269 x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
1270 IS
1271 l_sch_exists_flag VARCHAR2(1) := 'N';
1272 l_msg_index_out NUMBER;
1273
1274 -- These vars are for fetching from cursor.
1275 l_min_start_date DATE;
1276 l_max_end_date DATE;
1277 l_max_total_hours NUMBER;
1278
1279 l_missing_calendar_schedules EXCEPTION;
1280 l_calendar_not_active EXCEPTION;
1281 l_cal_missing_valid_shift EXCEPTION;
1282
1283 l_error_start_date DATE;
1284 l_error_end_date DATE;
1285 --bug: 4537865
1286 l_new_msg_data VARCHAR2(2000);
1287 --bug: 4537865
1288
1289 CURSOR C_SCH IS
1290 select trunc(min(start_date)) min_start_date,
1291 trunc(max(end_date)) max_end_date,
1292 max(monday_hours) + max(tuesday_hours) + max(wednesday_hours) +
1293 max(thursday_hours) + max(friday_hours) + max(saturday_hours) +
1294 max(sunday_hours) max_total_hours
1295 from pa_schedules
1296 where schedule_type_code = 'CALENDAR'
1297 and calendar_id = p_calendar_id;
1298
1299 BEGIN
1300 log_message(1,'Entering check_calendar for calendar');
1301 x_return_status := FND_API.G_RET_STS_SUCCESS;
1302
1303 open c_sch;
1304 fetch c_sch
1305 into l_min_start_date, l_max_end_date, l_max_total_hours;
1306 if c_sch%NOTFOUND then
1307 close c_sch;
1308 raise l_missing_calendar_schedules;
1309 end if;
1310 close c_sch;
1311
1312 if (l_min_start_date is null) then
1313 raise l_missing_calendar_schedules;
1314 end if;
1315
1316 log_message(1,'l_min_start_date: ' || l_min_start_date);
1317 log_message(1,'l_max_end_date: ' || l_max_end_date);
1318 log_message(1,'l_max_total_hours: ' || l_max_total_hours);
1319
1320 if (l_min_start_date > p_start_date) then
1321 l_error_start_date := p_start_date;
1322 l_error_end_date := l_min_start_date - 1;
1323 raise l_calendar_not_active;
1324 end if;
1325
1326 if (l_max_end_date < p_end_date) then
1327 l_error_start_date := l_max_end_date + 1;
1328 l_error_end_date := p_end_date;
1329 raise l_calendar_not_active;
1330 end if;
1331
1332 if (l_max_total_hours = 0) then
1333 raise l_cal_missing_valid_shift;
1334 end if;
1335
1336 log_message(1,'Leaving check_calendar for calendar');
1337 EXCEPTION
1338 WHEN l_missing_calendar_schedules THEN
1339 PA_SCHEDULE_UTILS.log_message(1,'ERROR: l_missing_calendar_schedules');
1340 PA_UTILS.add_message('PA','PA_MISSING_CALENDAR_SCHEDULES');
1341 x_return_status := FND_API.G_RET_STS_ERROR;
1342 x_msg_data := 'PA_MISSING_CALENDAR_SCHEDULES';
1343 x_msg_count := FND_MSG_PUB.Count_Msg;
1344 If x_msg_count = 1 THEN
1345 pa_interface_utils_pub.get_messages
1346 (p_encoded => FND_API.G_TRUE,
1347 p_msg_index => 1,
1348 p_msg_count => x_msg_count,
1349 p_msg_data => x_msg_data,
1350 --p_data => x_msg_data, * Bug: 4537865
1351 p_data => l_new_msg_data, --Bug: 4537865
1352 p_msg_index_out => l_msg_index_out );
1353 --bug: 4537865
1354 x_msg_data := l_new_msg_data;
1355 --bug: 4537865
1356 End If;
1357 WHEN l_calendar_not_active THEN
1358 PA_SCHEDULE_UTILS.log_message(1,'ERROR: l_calendar_not_active');
1359 PA_UTILS.add_message('PA','PA_CALENDAR_NOT_ACTIVE',
1360 'START_DATE', l_error_start_date,
1361 'END_DATE', l_error_end_date);
1362 x_return_status := FND_API.G_RET_STS_ERROR;
1363 x_msg_data := 'PA_CALENDAR_NOT_ACTIVE';
1364 x_msg_count := FND_MSG_PUB.Count_Msg;
1365 If x_msg_count = 1 THEN
1366 pa_interface_utils_pub.get_messages
1367 (p_encoded => FND_API.G_TRUE,
1368 p_msg_index => 1,
1369 p_msg_count => x_msg_count,
1370 p_msg_data => x_msg_data,
1371 --p_data => x_msg_data, * Bug: 4537865
1372 p_data => l_new_msg_data, --Bug: 4537865
1373 p_msg_index_out => l_msg_index_out );
1374 --Bug: 4537865
1375 x_msg_data := l_new_msg_data;
1376 --Bug: 4537865
1377 End If;
1378 WHEN l_cal_missing_valid_shift THEN
1379 PA_SCHEDULE_UTILS.log_message(1,'ERROR: l_cal_missing_valid_shift');
1380 PA_UTILS.add_message('PA','PA_CAL_MISSING_VALID_SHIFT');
1381 x_return_status := FND_API.G_RET_STS_ERROR;
1382 x_msg_data := 'PA_CAL_MISSING_VALID_SHIFT';
1383 x_msg_count := FND_MSG_PUB.Count_Msg;
1384 If x_msg_count = 1 THEN
1385 pa_interface_utils_pub.get_messages
1386 (p_encoded => FND_API.G_TRUE,
1387 p_msg_index => 1,
1388 p_msg_count => x_msg_count,
1389 p_msg_data => x_msg_data,
1390 --p_data => x_msg_data, * Bug: 4537865
1391 p_data => l_new_msg_data, --Bug: 4537865
1392 p_msg_index_out => l_msg_index_out );
1393 --Bug: 4537865
1394 x_msg_data := l_new_msg_data;
1395 --Bug: 4537865
1396 End If;
1397 WHEN OTHERS THEN
1398 PA_SCHEDULE_UTILS.log_message(1,'ERROR in check_calendar: '||sqlerrm);
1399 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1400 x_msg_count := 1;
1401 x_msg_data := substr(SQLERRM,1,240);
1402 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_UTILS',
1403 p_procedure_name => 'check_calendar');
1404 If x_msg_count = 1 THEN
1405 pa_interface_utils_pub.get_messages
1406 (p_encoded => FND_API.G_TRUE,
1407 p_msg_index => 1,
1408 p_msg_count => x_msg_count,
1409 p_msg_data => x_msg_data,
1410 --p_data => x_msg_data, * Bug: 4537865
1411 p_data => l_new_msg_data, --Bug: 4537865
1412 p_msg_index_out => l_msg_index_out );
1413 --Bug: 4537865
1414 x_msg_data := l_new_msg_data;
1415 --Bug: 4537865
1416 End If;
1417 RAISE;
1418 END check_calendar;
1419
1420 -- If p_calendar_type = 'RESOURCE', then checks calendars assigned
1421 -- to p_resource_id between p_start_date and p_end_date.
1422 -- Otherwise, checks p_calendar_id between p_start_date and p_end_date.
1423 -- See other check_calendar procedures for more details.
1424 PROCEDURE check_calendar(p_calendar_type IN VARCHAR2,
1425 p_calendar_id IN NUMBER := null,
1426 p_resource_id IN NUMBER := null,
1427 p_start_date IN DATE,
1428 p_end_date IN DATE,
1429 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1430 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1431 x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
1432 IS
1433 l_invalid_param EXCEPTION;
1434 l_error_message VARCHAR2(200);
1435 l_msg_index_out NUMBER;
1436 --Bug: 4537865
1437 l_new_msg_data VARCHAR2(2000);
1438 --Bug: 4537865
1439
1440 BEGIN
1441 log_message(1,'Entering check_calendar main');
1442 x_return_status := FND_API.G_RET_STS_SUCCESS;
1443
1444 if (p_calendar_type = 'RESOURCE') then
1445 if (p_resource_id is null) then
1446 l_error_message := 'p_resource_id cannot be null if p_calendar_type = RESOURCE.';
1447 raise l_invalid_param;
1448 end if;
1449
1450 check_calendar(p_resource_id => p_resource_id,
1451 p_start_date => p_start_date,
1452 p_end_date => p_end_date,
1453 x_return_status => x_return_status,
1454 x_msg_count => x_msg_count,
1455 x_msg_data => x_msg_data);
1456
1457 else
1458 if (p_calendar_id is null) then
1459 l_error_message := 'p_calendar_id cannot be null if p_calendar_type <> RESOURCE.';
1460 raise l_invalid_param;
1461 end if;
1462
1463 if (p_start_date is null) then
1464 l_error_message := 'p_start_date cannot be null if p_calendar_type <> RESOURCE.';
1465 raise l_invalid_param;
1466 end if;
1467
1468 if (p_end_date is null) then
1469 l_error_message := 'p_end_date cannot be null if p_calendar_type <> RESOURCE.';
1470 raise l_invalid_param;
1471 end if;
1472
1473 check_calendar(p_calendar_id => p_calendar_id,
1474 p_start_date => p_start_date,
1475 p_end_date => p_end_date,
1476 x_return_status => x_return_status,
1477 x_msg_count => x_msg_count,
1478 x_msg_data => x_msg_data);
1479 end if;
1480 log_message(1,'Leaving check_calendar main');
1481 EXCEPTION
1482 WHEN l_invalid_param THEN
1483 PA_SCHEDULE_UTILS.log_message(1,'ERROR in check_calendar: '|| l_error_message);
1484 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1485 x_msg_count := 1;
1486 x_msg_data := substr(l_error_message,1,240);
1487 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_UTILS',
1488 p_procedure_name => 'check_calendar');
1489 If x_msg_count = 1 THEN
1490 pa_interface_utils_pub.get_messages
1491 (p_encoded => FND_API.G_TRUE,
1492 p_msg_index => 1,
1493 p_msg_count => x_msg_count,
1494 p_msg_data => x_msg_data,
1495 --p_data => x_msg_data, * Bug: 4537865
1496 p_data => l_new_msg_data, --Bug: 4537865
1497 p_msg_index_out => l_msg_index_out );
1498 --Bug: 4537865
1499 x_msg_data := l_new_msg_data;
1500 --Bug: 4537865
1501 End If;
1502 RAISE;
1503 WHEN OTHERS THEN
1504 PA_SCHEDULE_UTILS.log_message(1,'ERROR in check_calendar: '||sqlerrm);
1505 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1506 x_msg_count := 1;
1507 x_msg_data := substr(SQLERRM,1,240);
1508 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_UTILS',
1509 p_procedure_name => 'check_calendar');
1510 If x_msg_count = 1 THEN
1511 pa_interface_utils_pub.get_messages
1512 (p_encoded => FND_API.G_TRUE,
1513 p_msg_index => 1,
1514 p_msg_count => x_msg_count,
1515 p_msg_data => x_msg_data,
1516 --p_data => x_msg_data, Bug: 4537865
1517 p_data => l_new_msg_data, --Bug: 4537865
1518 p_msg_index_out => l_msg_index_out );
1519 --Bug: 4537865
1520 x_msg_data := l_new_msg_data;
1521 --Bug: 4537865
1522 End If;
1523 RAISE;
1524
1525 END check_calendar;
1526
1527
1528 END PA_SCHEDULE_UTILS;