[Home] [Help]
PACKAGE: APPS.PA_SCHEDULE_PVT
Source
1 PACKAGE PA_SCHEDULE_PVT as
2 /* $Header: PARGPVTS.pls 120.7 2007/10/23 08:40:18 sugupta ship $ */
3 TempIDTab SYSTEM.PA_NUM_TBL_TYPE;
4
5 TYPE WorkPatternRecord IS RECORD (
6 assignment_id NUMBER,
7 start_date DATE,
8 end_date DATE,
9 monday_hours NUMBER,
10 tuesday_hours NUMBER,
11 wednesday_hours NUMBER,
12 thursday_hours NUMBER,
13 friday_hours NUMBER,
14 saturday_hours NUMBER,
15 sunday_hours NUMBER);
16
17 TYPE WorkPatternTabTyp IS TABLE OF WorkPatternRecord INDEX BY BINARY_INTEGER;
18
19 --SUBTYPE DayOfWeekType IS VARCHAR2(3); bug 5926172
20 SUBTYPE DayOfWeekType IS VARCHAR2(15);
21
22 -- Procedure : merge_work_pattern
23 -- Purpose : Merges same work pattern
24 -- Parameters :
25 --
26 PROCEDURE merge_work_pattern(
27 p_project_id IN NUMBER,
28 p_assignment_id IN NUMBER,
29 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
30 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
31 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
32 );
33
34
35 PROCEDURE get_calendar_schedule ( p_calendar_id IN NUMBER,
36 p_start_date IN DATE,
37 p_end_date IN DATE,
38 x_sch_record_tab OUT NOCOPY PA_SCHEDULE_GLOB.ScheduleTabTyp,
39 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
40 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
41 x_msg_data OUT NOCOPY VARCHAR2 ); --File.Sql.39 bug 4440895
42
43 --
44 -- Procedure : get_calendar_schedule
45 -- Purpose : Gets schedule details for Calendar.
46 -- Parameters :
47 --
48
49
50 PROCEDURE get_assignment_schedule ( p_assignment_id IN NUMBER,
51 p_start_date IN DATE,
52 p_end_date IN DATE,
53 x_sch_record_tab OUT NOCOPY PA_SCHEDULE_GLOB.ScheduleTabTyp,
54 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
55 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
56 x_msg_data OUT NOCOPY VARCHAR2 ); --File.Sql.39 bug 4440895
57
58 --
59 -- Procedure : get_assignment_schedule
60 -- Purpose : Gets schedule details for project assignment.
61 -- Parameters :
62 --
63
64
65 PROCEDURE get_assignment_schedule ( p_assignment_id IN NUMBER,
66 x_sch_record_tab OUT NOCOPY PA_SCHEDULE_GLOB.ScheduleTabTyp,
67 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
68 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
69 x_msg_data OUT NOCOPY VARCHAR2 ); --File.Sql.39 bug 4440895
70
71 --
72 -- Procedure : get_assignment_schedule
73 -- Purpose : Gets schedule details for project assignment.
74 -- Parameters :
75 --
76
77 PROCEDURE get_resource_schedule ( p_source_id IN NUMBER,
78 p_source_type IN VARCHAR2,
79 p_start_date IN DATE,
80 p_end_date IN DATE,
81 x_sch_record_tab IN OUT NOCOPY PA_SCHEDULE_GLOB.ScheduleTabTyp,
82 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
83 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
84 x_msg_data OUT NOCOPY VARCHAR2 ); --File.Sql.39 bug 4440895
85 --
86 -- Procedure : get_resource_schedule
87 -- Purpose : Gets schedule details for project resource.
88 -- Parameters :
89 --
90
91 PROCEDURE apply_schedule_change( p_chg_sch_record_tab IN PA_SCHEDULE_GLOB.ScheduleTabTyp,
92 p_del_sch_record_tab IN PA_SCHEDULE_GLOB.ScheduleTabTyp,
93 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
94 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
95 x_msg_data OUT NOCOPY VARCHAR2 ); --File.Sql.39 bug 4440895
96
97 --
98 -- Procedure : apply_schedule_change
99 -- Purpose : Applys the resultant schedule details after applying exceptions on
100 -- the schedule related tables.
101 -- Parameters :
102 --
103
104 PROCEDURE create_new_schedule(
105 p_sch_except_record IN pa_schedule_glob.SchExceptRecord,
106 p_sch_record IN pa_schedule_glob.ScheduleRecord,
107 x_sch_record_tab IN OUT NOCOPY pa_schedule_glob.ScheduleTabTyp,
108 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
109 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
110 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
111 );
112 --
113 -- Procedure : create_new_schedule
114 -- Purpose :
115 -- Parameters :
116 --
117
118 PROCEDURE create_new_calendar(
119 p_sch_except_record IN pa_schedule_glob.SchExceptRecord,
120 p_sch_record IN pa_schedule_glob.ScheduleRecord,
121 x_sch_record_tab IN OUT NOCOPY pa_schedule_glob.ScheduleTabTyp,
122 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
123 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
124 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
125 );
126
127 --
128 -- Procedure : create_new_calendar
129 -- Purpose :
130 -- Parameters :
131 --
132
133
134 PROCEDURE create_new_hours(
135 p_sch_except_record IN pa_schedule_glob.SchExceptRecord,
136 p_sch_record IN pa_schedule_glob.ScheduleRecord,
137 x_sch_record_tab IN OUT NOCOPY pa_schedule_glob.ScheduleTabTyp,
138 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
139 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
140 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
141 );
142
143 --
144 -- Procedure : create_new_hours
145 -- Purpose :
146 -- Parameters :
147 --
148
149
150 PROCEDURE create_new_duration(
151 p_sch_except_record IN pa_schedule_glob.SchExceptRecord,
152 p_sch_record IN pa_schedule_glob.ScheduleRecord,
153 x_sch_record_tab IN OUT NOCOPY pa_schedule_glob.ScheduleTabTyp,
154 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
155 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
156 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
157 );
158
159 --
160 -- Procedure : create_new_hours
161 -- Purpose :
162 -- Parameters :
163 --
164
165 PROCEDURE create_new_pattern(
166 p_sch_except_record IN pa_schedule_glob.SchExceptRecord,
167 p_sch_record IN pa_schedule_glob.ScheduleRecord,
168 x_sch_record_tab IN OUT NOCOPY pa_schedule_glob.ScheduleTabTyp,
169 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
170 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
171 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
172 );
173
174 --
175 -- Procedure : create_new_hours
176 -- Purpose :
177 -- Parameters :
178 --
179
180 PROCEDURE create_new_status(
181 p_sch_except_record IN pa_schedule_glob.SchExceptRecord,
182 p_sch_record IN pa_schedule_glob.ScheduleRecord,
183 x_sch_record_tab IN OUT NOCOPY pa_schedule_glob.ScheduleTabTyp,
184 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
185 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
186 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
187 );
188
189 --
190 -- Procedure : create_new_hours
191 -- Purpose :
192 -- Parameters :
193 --
194
195 PROCEDURE apply_change_duration (
196 p_sch_record_tab IN pa_schedule_glob.ScheduleTabTyp,
197 p_sch_except_record IN pa_schedule_glob.SchExceptRecord,
198 x_sch_record_tab IN OUT NOCOPY pa_schedule_glob.ScheduleTabTyp,
199 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
200 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
201 x_msg_data OUT NOCOPY VARCHAR2 ) ; --File.Sql.39 bug 4440895
202 --
203 -- Procedure : apply_change_duration
204 -- Purpose :
205 -- Parameters :
206 --
207
208 PROCEDURE apply_other_changes (
209 p_sch_record_tab IN pa_schedule_glob.ScheduleTabTyp,
210 p_sch_except_record IN pa_schedule_glob.SchExceptRecord,
211 x_sch_record_tab IN OUT NOCOPY pa_schedule_glob.ScheduleTabTyp,
212 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
213 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
214 x_msg_data OUT NOCOPY VARCHAR2 ); --File.Sql.39 bug 4440895
215 --
216 -- Procedure : apply_other_changes
217 -- Purpose :
218 -- Parameters :
219 --
220
221 PROCEDURE apply_assignment_change (
222 p_record_version_number IN NUMBER,
223 chg_tr_sch_rec_tab IN PA_SCHEDULE_GLOB.ScheduleTabTyp,
224 sch_except_record_tab IN PA_SCHEDULE_GLOB.SchExceptTabTyp,
225 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
226 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
227 x_msg_data OUT NOCOPY VARCHAR2 ); --File.Sql.39 bug 4440895
228 --
229 -- Procedure : apply_assignment_change
230 -- Purpose : Update the column for multiple flag in PA_PROJECT_ASSIGNMENTS table if the assignment
231 -- has more than one status but it will keep only one status
232 -- Parameters :
233 --
234
235 PROCEDURE get_periodic_start_end(
236 p_start_date IN DATE,
237 p_end_date IN DATE,
238 p_project_assignment_id IN NUMBER,
239 p_task_assignment_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE,
240 x_min_start_date OUT NOCOPY DATE, --File.Sql.39 bug 4440895
241 x_max_end_date OUT NOCOPY DATE, --File.Sql.39 bug 4440895
242 p_project_id IN NUMBER,
243 p_budget_version_id IN NUMBER,
244 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
245 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
246 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
247 );
248 -- Procedure : get_periodic_start_end
249 -- Purpose : Get Periodic Start/End Dates for Task Assignments.
250
251 PROCEDURE create_opn_asg_schedule(p_project_id IN NUMBER,
252 p_calendar_id IN NUMBER,
253 p_assignment_id IN NUMBER,
254 p_start_date IN DATE,
255 p_end_date IN DATE,
256 p_assignment_status_code IN VARCHAR2,
257 p_work_type_id IN NUMBER:=NULL,
258 p_task_id IN NUMBER:=NULL,
259 p_task_percentage IN NUMBER:=NULL,
260 p_sum_tasks_flag IN VARCHAR2,
261 p_task_assignment_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE(),
262 p_budget_version_id IN NUMBER:=NULL,
263 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
264 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
265 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
266 );
267
268 -- Procedure : create_opn_asg_schedule
269 -- Purpose : Create schedule for open assignments
270 PROCEDURE create_opn_asg_schedule(
271 p_project_id IN NUMBER :=NULL,
272 p_asgn_creation_mode IN VARCHAR2 := NULL, /* Added for Bug 6145532 */
273 p_calendar_id IN NUMBER :=NULL,
274 p_assignment_id_tbl IN PA_ASSIGNMENTS_PUB.assignment_id_tbl_type,
275 p_assignment_source_id IN NUMBER :=NULL,
276 p_start_date IN DATE:=NULL,
277 p_end_date IN DATE := NULL,
278 p_assignment_status_code IN VARCHAR2:= NULL,
279 p_sum_tasks_flag IN VARCHAR2 default null,
280 p_task_assignment_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE(),
281 p_budget_version_id IN NUMBER:=NULL,
282 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
283 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
284 x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
285
286
287 -- Procedure : create_opn_asg_schedule
288 -- Purpose : Add multiple open assignment schedules. Copy
289 -- assignment schedules from an open or staffed
290 -- assignment.
291 PROCEDURE create_stf_asg_schedule(p_project_id IN NUMBER,
292 p_schedule_basis_flag IN VARCHAR2,
293 p_project_party_id IN NUMBER,
294 p_calendar_id IN NUMBER,
295 p_assignment_id IN NUMBER,
296 p_open_assignment_id IN NUMBER,
297 p_resource_calendar_percent IN NUMBER,
298 p_start_date IN DATE,
299 p_end_date IN DATE,
300 p_assignment_status_code IN VARCHAR2,
301 p_work_type_id IN NUMBER,
302 p_task_id IN NUMBER,
303 p_task_percentage IN NUMBER,
304 p_sum_tasks_flag IN VARCHAR2 default null,
305 p_task_assignment_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE(),
306 p_budget_version_id IN NUMBER:=NULL,
307 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
308 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
309 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
310 );
311
312
313 -- Procedure : create_stf_asg_schedule
314 -- Purpose : Create schedule for staffed assignments.
315
316
317 PROCEDURE delete_asgn_schedules ( p_assignment_id IN NUMBER,
318 p_perm_delete IN VARCHAR2 := FND_API.G_TRUE,
319 p_change_id IN NUMBER := null,
320 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
321 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
322 x_msg_data OUT NOCOPY VARCHAR2 ); --File.Sql.39 bug 4440895
323
324 --
325 -- Procedure : Delete_Asgn_Schedules
326 -- Purpose : This procedure will delete the schedule,exception records corresponding to
327 -- the passed assignment id
328 --
329 -- Parameters :
330
331 PROCEDURE update_sch_wf_failure(
332 p_assignment_id IN NUMBER,
333 p_record_version_number IN NUMBER,
334 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
335 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
336 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
337 );
338
339 -- Effects: Changes the schedule statuses of the assignment to the
340 -- appropriate failure status.
341
342 PROCEDURE update_sch_wf_success(
343 p_assignment_id IN NUMBER,
344 p_record_version_number IN NUMBER,
345 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
346 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
347 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
348 );
349
350 -- Effects: Changes the schedule statuses of the assignment to the
351 -- appropriate success status.
352
353 PROCEDURE revert_to_last_approved(
354 p_assignment_id IN NUMBER,
355 p_change_id IN NUMBER,
356 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
357 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
358 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
359 );
360
361 -- Effects: Reverts the schedule back to the last approved schedule.
362 -- Impl Notes: Copies schedule records with p_assignment_id, from schedules
363 -- history to schedules table. Do not update if there are
364 -- no records with last_approved_flag = 'Y'. Delete those records from
365 -- pa_schedule_history. Be sure to use delete schedule API to remove old
366 -- schedules. Also, call create_timeline.
367
368 PROCEDURE update_history_table(
369 p_assignment_id IN NUMBER,
370 p_change_id IN NUMBER,
371 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
372 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
373 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
374 );
375
376 -- Effects: Adds schedule records for p_assignment_id to pa_schedules_history
377 -- if they do not already exist.
378 -- Impl Notes: If records already exist in pa_schedule_history with change_id,
379 -- then do nothing. Otherwise, uncheck any records with last_approved_flag
380 -- and copy schedule records there with correct change_id with
381 -- last_approved_flag checked.
382
383
384 --
385 -- Procedure : Update_asgmt_changed_items_tab
386 -- Purpose : Poplulates new and old values for schedule changes to pa_asgmt_changed_items
387 -- table which stores the assignment_changes that are pending approval.
388 -- Parameter
389 -- p_populate_mode : SAVED/ASSIGNMENT_UPDATED/SCHEDULE_UPDATED
390 --
391 PROCEDURE update_asgmt_changed_items_tab
392 ( p_assignment_id IN NUMBER
393 ,p_populate_mode IN VARCHAR2 := 'SAVED'
394 ,p_change_id IN NUMBER
395 ,p_exception_type_code IN VARCHAR2 := NULL
396 ,p_start_date IN DATE := NULL
397 ,p_end_date IN DATE := NULL
398 ,p_requirement_status_code IN VARCHAR2 := NULL
399 ,p_assignment_status_code IN VARCHAR2 := NULL
400 ,p_start_date_tbl IN SYSTEM.PA_DATE_TBL_TYPE := NULL
401 ,p_end_date_tbl IN SYSTEM.PA_DATE_TBL_TYPE := NULL
402 ,p_monday_hours_tbl IN SYSTEM.PA_NUM_TBL_TYPE := NULL
403 ,p_tuesday_hours_tbl IN SYSTEM.PA_NUM_TBL_TYPE := NULL
404 ,p_wednesday_hours_tbl IN SYSTEM.PA_NUM_TBL_TYPE := NULL
405 ,p_thursday_hours_tbl IN SYSTEM.PA_NUM_TBL_TYPE := NULL
406 ,p_friday_hours_tbl IN SYSTEM.PA_NUM_TBL_TYPE := NULL
407 ,p_saturday_hours_tbl IN SYSTEM.PA_NUM_TBL_TYPE := NULL
408 ,p_sunday_hours_tbl IN SYSTEM.PA_NUM_TBL_TYPE := NULL
409 ,p_non_working_day_flag IN VARCHAR2 := 'N'
410 ,p_change_hours_type_code IN VARCHAR2 := NULL
411 ,p_hrs_per_day IN NUMBER := NULL
412 ,p_calendar_percent IN NUMBER := NULL
413 ,p_change_calendar_type_code IN VARCHAR2 := NULL
414 ,p_change_calendar_name IN VARCHAR2 := NULL
415 ,p_change_calendar_id IN NUMBER := NULL
416 ,p_duration_shift_type_code IN VARCHAR2 := NULL
417 ,p_duration_shift_unit_code IN VARCHAR2 := NULL
418 ,p_number_of_shift IN NUMBER := NULL
419 ,x_return_status OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
420
421
422 -- Procedure : check_overcommitment_single
423 -- Purpose : First checks if this assignment alone causes resource
424 -- overcommitment. If Yes, then stores self-conflict and user
425 -- action in PA_ASSIGNMENT_CONFLICT_HIST.
426 PROCEDURE check_overcommitment_single( p_assignment_id IN NUMBER,
427 p_resolve_conflict_action_code IN VARCHAR2,
428 p_conflict_group_id IN NUMBER := NULL,
429 x_overcommitment_flag OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
430 x_conflict_group_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
431 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
432 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
433 x_msg_data OUT NOCOPY VARCHAR2 ); --File.Sql.39 bug 4440895
434
435
436 -- Procedure : check_overcommitment_mult
437 -- Purpose : First checks if this assignment alone causes resource
438 -- overcommitment. If Yes, then stores self-conflict and user
439 -- action in PA_ASSIGNMENT_CONFLICT_HIST.
440 PROCEDURE check_overcommitment_mult(p_item_type IN PA_WF_PROCESSES.item_type%TYPE,
441 p_item_key IN PA_WF_PROCESSES.item_key%TYPE,
442 p_conflict_group_id IN NUMBER := NULL,
443 p_resolve_conflict_action_code IN VARCHAR2,
444 x_overcommitment_flag OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
445 x_conflict_group_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
446 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
447 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
448 x_msg_data OUT NOCOPY VARCHAR2 ); --File.Sql.39 bug 4440895
449
450
451 -- Procedure : check_self_conflict
452 -- Purpose : Check if the assignment is causing self conflict.
453 --
454 PROCEDURE check_self_conflict(p_assignment_id IN NUMBER,
455 p_resource_id IN NUMBER,
456 p_start_date IN DATE,
457 p_end_date IN DATE,
458 x_self_conflict_flag OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
459 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
460 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
461 x_msg_data OUT NOCOPY VARCHAR2 ); --File.Sql.39 bug 4440895
462
463
464 -- Procedure : resolve_conflicts
465 -- Purpose : Resolves remaining conflicts by taking action chosen to user
466 -- detailed in PA_ASSIGNMENT_CONFLICT_HIST. Updates
467 -- processed_flag in the table once complete.
468 PROCEDURE resolve_conflicts( p_conflict_group_id IN NUMBER,
469 p_assignment_id IN NUMBER,
470 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
471 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
472 x_msg_data OUT NOCOPY VARCHAR2 ); --File.Sql.39 bug 4440895
473
474
475 PROCEDURE insert_work_pattern_record( p_assignment_id IN NUMBER,
476 p_item_quantity IN NUMBER,
477 p_item_date IN DATE,
478 p_week_end_date IN DATE,
479 x_work_pattern_tbl IN OUT NOCOPY WorkPatternTabTyp,
480 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
481 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
482 x_msg_data OUT NOCOPY VARCHAR2 ); --File.Sql.39 bug 4440895
483
484
485 PROCEDURE update_work_pattern_record(p_overcom_quantity IN NUMBER,
486 p_count IN NUMBER,
487 p_item_date IN DATE,
488 x_work_pattern_tbl IN OUT NOCOPY WorkPatternTabTyp,
489 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
490 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
491 x_msg_data OUT NOCOPY VARCHAR2 ); --File.Sql.39 bug 4440895
492
493
494 PROCEDURE insert_work_pattern_tab(p_cur_work_pattern_tbl IN WorkPatternTabTyp,
495 x_work_pattern_tbl IN OUT NOCOPY WorkPatternTabTyp,
496 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
497 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
498 x_msg_data OUT NOCOPY VARCHAR2 ); --File.Sql.39 bug 4440895
499
500
501 -- Procedure : overcom_post_aprvl_processing
502 -- Purpose : Completes post-processing for overcommitment module after
503 -- approval is complete.
504 PROCEDURE overcom_post_aprvl_processing(p_conflict_group_id IN NUMBER,
505 p_fnd_user_name IN VARCHAR2,
506 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
507 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
508 x_msg_data OUT NOCOPY VARCHAR2 ); --File.Sql.39 bug 4440895
509
510
511 -- Procedure : will_resolve_conflicts_by_rmvl
512 -- Purpose : Returns 'Y' if user has chosen to remove one or more
513 -- conflicts.
514 PROCEDURE will_resolve_conflicts_by_rmvl(p_conflict_group_id IN NUMBER,
515 x_resolve_conflicts_by_rmvl OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
516 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
517 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
518 x_msg_data OUT NOCOPY VARCHAR2 ); --File.Sql.39 bug 4440895
519
520
521 -- Procedure : has_resolved_conflicts_by_rmvl
522 -- Purpose : Returns 'Y' if remove conflicts has been sucessful.
523 PROCEDURE has_resolved_conflicts_by_rmvl(p_conflict_group_id IN NUMBER,
524 p_assignment_id IN NUMBER,
525 x_resolve_conflicts_by_rmvl OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
526 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
527 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
528 x_msg_data OUT NOCOPY VARCHAR2 ); --File.Sql.39 bug 4440895
529
530
531 -- Procedure : cancel_overcom_txn_items
532 -- Purpose : Cancels transaction items marked with CANCEL_TXN_ITEM.
533 -- Updates processed_flag in the table once complete.
534 PROCEDURE cancel_overcom_txn_items (p_conflict_group_id IN NUMBER,
535 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
536 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
537 x_msg_data OUT NOCOPY VARCHAR2 ); --File.Sql.39 bug 4440895
538
539
540 -- Procedure : revert_overcom_txn_items
541 -- Purpose : Reverts transaction items marked with REVERT_TXN_ITEM.
542 -- Updates processed_flag in the table once complete.
543 PROCEDURE revert_overcom_txn_items (p_conflict_group_id IN NUMBER,
544 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
545 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
546 x_msg_data OUT NOCOPY VARCHAR2 ); --File.Sql.39 bug 4440895
547
548
549 -- Procedure : get_conflicting_asgmt_count
550 -- Purpose : Returns number of assignments causing conflict including
551 -- self conflict.
552 PROCEDURE get_conflicting_asgmt_count (p_conflict_group_id IN NUMBER,
553 x_assignment_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
554 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
555 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
556 x_msg_data OUT NOCOPY VARCHAR2 ); --File.Sql.39 bug 4440895
557
558
559 PROCEDURE has_action_taken_on_conflicts (p_conflict_group_id IN
560 NUMBER,
561 x_action_taken OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
562 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
563 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
564 x_msg_data OUT NOCOPY VARCHAR2 ); --File.Sql.39 bug 4440895
565
566 PROCEDURE check_asgmt_apprvl_working (p_conflict_group_id IN
567 NUMBER,
568 x_result OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
569 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
570 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
571 x_msg_data OUT NOCOPY VARCHAR2 ); --File.Sql.39 bug 4440895
572
573 -- Procedure : sum_task_assignments
574 -- Purpose : Sums the task assignments given a schedule of working days and output a
575 -- new project schedule spanning from p_start_date to p_end_date
576 --Bug 5126919: Added parameter x_total_hours which will contain the total hours for which the x_schedule_tbl
577 --will be prepared.
578 PROCEDURE sum_task_assignments (
579 p_task_assignments_tbl IN SYSTEM.PA_NUM_TBL_TYPE ,
580 p_schedule_tbl IN PA_SCHEDULE_GLOB.ScheduleTabTyp ,
581 p_start_date IN DATE ,
582 p_end_date IN DATE ,
583 x_total_hours OUT NOCOPY NUMBER , -- Bug 5126919
584 x_schedule_tbl OUT NOCOPY PA_SCHEDULE_GLOB.ScheduleTabTyp , --File.Sql.39 bug 4440895
585 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
586 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
587 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
588 );
589
590 -- Procedure : set_hours_by_day_of_wee
591 -- Purpose : sets the number of hours in a given schedule record for a particular
592 -- day of the week
593 PROCEDURE set_hours_by_day_of_week (
594 p_schedule_record IN OUT NOCOPY PA_SCHEDULE_GLOB.ScheduleRecord ,
595 p_day_of_week IN PA_SCHEDULE_PVT.DayOfWeekType ,
596 p_hours IN NUMBER);
597
598 -- Function : Get_changed_item_name_text
599 -- Purpose : Returns the changed item name display text for
600 -- p_exception_type_code.
601 FUNCTION get_changed_item_name_text( p_exception_type_code IN VARCHAR2)
602 RETURN VARCHAR2;
603
604
605 -- Function : Get_date_range_text
606 -- Purpose : Returns the display text for the date range of the
607 -- assignment.
608 FUNCTION Get_date_range_text ( p_start_date IN DATE,
609 p_end_date IN DATE) RETURN VARCHAR2;
610
611
612 -- Function : Get_old_value_text
613 -- Purpose : Returns the display text for the old schedule value
614 -- of the assignment.
615 FUNCTION Get_old_value_text (p_exception_type_code IN VARCHAR2,
616 p_assignment_id IN NUMBER,
617 p_start_date IN DATE,
618 p_end_date IN DATE) RETURN VARCHAR2;
619
620 -- Function : Get_new_value_text
621 -- Purpose : Returns the display text for the new schedule value
622 -- of the assignment.
623 FUNCTION Get_new_value_text (p_exception_type_code IN VARCHAR2,
624 p_new_calendar_id IN NUMBER,
625 p_new_start_date IN DATE,
626 p_new_end_date IN DATE,
627 p_new_status_code IN VARCHAR2,
628 p_new_change_calendar_id IN NUMBER,
629 p_new_monday_hours IN NUMBER,
630 p_new_tuesday_hours IN NUMBER,
631 p_new_wednesday_hours IN NUMBER,
632 p_new_thursday_hours IN NUMBER,
633 p_new_friday_hours IN NUMBER,
634 p_new_saturday_hours IN NUMBER,
635 p_new_sunday_hours IN NUMBER,
636 p_new_change_hours_type_code IN VARCHAR2,
637 p_new_non_working_day_flag IN VARCHAR2,
638 p_new_hours_per_day IN NUMBER,
639 p_new_calendar_percent IN NUMBER,
640 p_new_change_cal_type_code IN VARCHAR2 := null,
641 p_new_change_calendar_name IN VARCHAR2 := null)
642 RETURN VARCHAR2;
643
644 -- Function : get_num_days_of_conflict
645 -- Purpose : Return number of days in assignment that are in conflict with
646 -- existing confirmed assignments, and potentially in conflict
647 -- with other assignments in transaction including itself.
648 FUNCTION get_num_days_of_conflict (p_assignment_id IN NUMBER,
649 p_resource_id IN NUMBER,
650 p_conflict_group_id IN NUMBER) RETURN NUMBER;
651
652
653 -- Function : column_val_conflict_exists
654 -- Purpose : Returns value to display in 'Conflict Exists' column ('Yes',
655 -- 'No')
656 FUNCTION column_val_conflict_exists (p_conflict_group_id IN NUMBER,
657 p_assignment_id IN NUMBER ) RETURN VARCHAR2;
658
659
660 -- Function : column_val_conflict_action
661 -- Purpose : Returns value to display in 'Action on Approval' column
662 -- ('Remove Conflicts', Continue with Conflicts', ''). A
663 -- self-conflict would imply 'Continue with Conflicts'. No value
664 -- would be shown for those assignments not causing
665 -- overcommitment.
666 FUNCTION column_val_conflict_action (p_conflict_group_id IN NUMBER,
667 p_assignment_id IN NUMBER ) RETURN VARCHAR2;
668
669
670 -- Function : check_conflict_proj_affected
671 -- Purpose : Returns a value to the View Conflicts page to filter for
672 -- the assignments that are in conflict with the assignments in
673 -- a particular conflicting project.
674 FUNCTION check_conflict_proj_affected (p_conflict_group_id IN NUMBER,
675 p_assignment_id IN NUMBER,
676 p_conflict_project_id IN NUMBER) RETURN VARCHAR2;
677
678 -- Function : check_self_conflict_exist
679 -- Purpose : Returns a value to the View Conflicts page to filter for
680 -- the assignments with self_conflict_flag = 'Y' and being chosen to
681 -- remove conflicts.
682 FUNCTION check_self_conflict_exist(p_conflict_group_id IN NUMBER,
683 p_assignment_id IN NUMBER) RETURN VARCHAR2;
684
685 --
686 -- Returns ak attribute label corresponding p_region_code, p_attribute_code
687 --
688 FUNCTION get_ak_attribute_label (p_region_code IN VARCHAR2,
689 p_attribute_code IN VARCHAR2)
690 RETURN VARCHAR2;
691
692 -- Function : get_day_of_week
693 -- Purpose : Determines the day of the week given a particular date
694 FUNCTION get_day_of_week (p_date IN DATE) RETURN PA_SCHEDULE_PVT.DayOfWeekType;
695
696 -- Function : check_self_conflict_exist
697 -- Purpose : Returns the number of hours in a given schedule record for a
698 -- particular day of the week
699 FUNCTION get_hours_by_day_of_week (
700 p_schedule_record IN PA_SCHEDULE_GLOB.ScheduleRecord ,
701 p_day_of_week IN PA_SCHEDULE_PVT.DayOfWeekType )
702 RETURN NUMBER;
703
704 END PA_SCHEDULE_PVT;