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