1 PACKAGE PA_SCHEDULE_UTILS as
2 /* $Header: PARGUTLS.pls 120.1 2005/08/19 16:53:47 mwasowic noship $ */
3
4 PROCEDURE copy_schedule_rec_tab ( p_sch_record_tab IN PA_SCHEDULE_GLOB.ScheduleTabTyp,
5 p_start_id IN NUMBER,
6 p_end_id IN NUMBER,
7 x_sch_record_tab IN OUT NOCOPY PA_SCHEDULE_GLOB.ScheduleTabTyp,
8 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
9 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
10 x_msg_data OUT NOCOPY VARCHAR2 ); --File.Sql.39 bug 4440895
11
12 --
13 -- Procedure : Copy_schedule_rec_tab
14 -- Purpose : Copying Rows from one table to another in
15 -- array processing.
16 -- Parameters :
17 -- p_sch_record_tab - Table of schedule details Records
18
19 PROCEDURE add_schedule_rec_tab ( p_sch_record_tab IN PA_SCHEDULE_GLOB.ScheduleTabTyp,
20 p_start_id IN NUMBER,
21 p_end_id IN NUMBER,
22 px_sch_record_tab IN OUT NOCOPY PA_SCHEDULE_GLOB.ScheduleTabTyp,
23 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
24 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
25 x_msg_data OUT NOCOPY VARCHAR2 ); --File.Sql.39 bug 4440895
26
27 --
28 -- Procedure : Add_schedule_rec_tab
29 -- Purpose : Adding Rows from one table to another in
30 -- array processing.
31 -- Parameters :
32 -- p_sch_record_tab - Table of schedule details Records
33
34 PROCEDURE mark_del_sch_rec_tab ( p_start_id IN NUMBER,
35 p_end_id IN NUMBER,
36 px_sch_record_tab IN OUT NOCOPY PA_SCHEDULE_GLOB.ScheduleTabTyp,
37 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
38 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
39 x_msg_data OUT NOCOPY VARCHAR2 ); --File.Sql.39 bug 4440895
40
41 --
42 -- Procedure : Mark_del_sch_rec_tab
43 -- Purpose : Marking Rows for deletion in
44 -- array processing.
45 -- Parameters :
46 -- px_sch_record_tab - Table of schedule details Records
47
48 PROCEDURE sep_del_sch_rec_tab ( p_sch_record_tab IN PA_SCHEDULE_GLOB.ScheduleTabTyp,
49 x_del_sch_rec_tab IN OUT NOCOPY PA_SCHEDULE_GLOB.ScheduleTabTyp,
50 x_sch_record_tab OUT NOCOPY PA_SCHEDULE_GLOB.ScheduleTabTyp,
51 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
52 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
53 x_msg_data OUT NOCOPY VARCHAR2 ); --File.Sql.39 bug 4440895
54
55 --
56 -- Procedure : Sep_del_sch_rec_tab
57 -- Purpose : Seprating Rows from deleted one to non deleted one in
58 -- array processing.
59 -- Parameters :
60 -- p_sch_record_tab - Table of schedule details Records
61
62 PROCEDURE update_sch_rec_tab ( px_sch_record_tab IN OUT NOCOPY PA_SCHEDULE_GLOB.ScheduleTabTyp,
63 p_project_id IN NUMBER DEFAULT NULL,
64 p_calendar_id IN NUMBER DEFAULT NULL,
65 p_assignment_id IN NUMBER DEFAULT NULL,
66 p_schedule_type_code IN VARCHAR2 DEFAULT NULL,
67 p_assignment_status_code IN VARCHAR2 DEFAULT NULL,
68 p_system_status_code IN VARCHAR2 DEFAULT NULL,
69 p_start_date IN DATE DEFAULT NULL,
70 p_end_date IN DATE DEFAULT NULL,
71 p_monday_hours IN NUMBER DEFAULT NULL,
72 p_tuesday_hours IN NUMBER DEFAULT NULL,
73 p_wednesday_hours IN NUMBER DEFAULT NULL,
74 p_thursday_hours IN NUMBER DEFAULT NULL,
75 p_friday_hours IN NUMBER DEFAULT NULL,
76 p_saturday_hours IN NUMBER DEFAULT NULL,
77 p_sunday_hours IN NUMBER DEFAULT NULL,
78 p_change_type_code IN VARCHAR2 DEFAULT NULL,
79 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
80 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
81 x_msg_data OUT NOCOPY VARCHAR2 ); --File.Sql.39 bug 4440895
82
83 --
84 -- Procedure : Update_sch_rec_tab
85 -- Purpose : Updating Rows with the given date in
86 -- array processing.
87 -- Parameters :
88 -- px_sch_record_tab - Table of schedule details Records
89
90 PROCEDURE apply_percentage ( px_sch_record_tab IN OUT NOCOPY PA_SCHEDULE_GLOB.ScheduleTabTyp,
91 p_percentage IN NUMBER ,
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 --
97 -- Procedure : Apply_percentage
98 -- Purpose : Applying the percentage availabilty factor on the resource schedule. But can be used to
99 -- apply percentage of availablity on the work pattern of the schedul in
100 -- array processing.
101 -- Parameters :
102 -- px_sch_record_tab - Table of schedule details Records
103
104 PROCEDURE copy_except_record ( p_except_record IN PA_SCHEDULE_GLOB.SchExceptRecord,
105 x_except_record OUT NOCOPY PA_SCHEDULE_GLOB.SchExceptRecord,
106 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
107 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
108 x_msg_data OUT NOCOPY VARCHAR2 ); --File.Sql.39 bug 4440895
109
110 --
111 -- Procedure : Copy_Except_Record
112 -- Purpose :It copy the record in array processing.
113 -- Parameters :
114
115 PROCEDURE update_except_record( px_except_record IN OUT NOCOPY PA_SCHEDULE_GLOB.SchExceptRecord,
116 p_project_id IN NUMBER DEFAULT NULL,
117 p_calendar_id IN NUMBER DEFAULT NULL,
118 p_assignment_id IN NUMBER DEFAULT NULL,
119 p_schedule_type_code IN VARCHAR2 DEFAULT NULL,
120 p_assignment_status_code IN VARCHAR2 DEFAULT NULL,
121 p_start_date IN DATE DEFAULT NULL,
122 p_end_date IN DATE DEFAULT NULL,
123 p_resource_calendar_percent IN NUMBER DEFAULT NULL,
124 p_non_working_day_flag IN VARCHAR2 DEFAULT NULL,
125 p_change_hours_type_code IN VARCHAR2 DEFAULT NULL,
126 p_monday_hours IN NUMBER DEFAULT NULL,
127 p_tuesday_hours IN NUMBER DEFAULT NULL,
128 p_wednesday_hours IN NUMBER DEFAULT NULL,
129 p_thursday_hours IN NUMBER DEFAULT NULL,
130 p_friday_hours IN NUMBER DEFAULT NULL,
131 p_saturday_hours IN NUMBER DEFAULT NULL,
132 p_sunday_hours IN NUMBER DEFAULT NULL,
133 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
134 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
135 x_msg_data OUT NOCOPY VARCHAR2 ); --File.Sql.39 bug 4440895
136 --
137 -- Procedure :Update_Except_Record
138 -- Purpose : This procedure will update the record in array processing.
139 -- Parameters :
140
141 PROCEDURE log_message( level1 IN NUMBER,
142 msg1 IN VARCHAR2);
143 --
144 -- Procedure : Log_Message
145 -- Purpose : This procedure will print message with the given lavel It is oveloaded procedure.
146 --
147 -- .
148 -- Parameters :
149
150 PROCEDURE debug(p_module IN VARCHAR2,
151 p_msg IN VARCHAR2,
152 p_log_level IN NUMBER DEFAULT 6);
153
154
155 PROCEDURE debug(p_msg IN VARCHAR2);
156
157 PROCEDURE log_message( level1 IN NUMBER,
158 msg1 IN VARCHAR2,
159 wr_tab IN PA_SCHEDULE_GLOB.ScheduleTabTyp);
160 --
161 -- Procedure : Log_message
162 -- Purpose : This procedure will print value of structure of passing array with the given lavel
163 -- it is oveloaded procedure
164 --
165 -- Parameters :
166
167
168 PROCEDURE validate_date_range( p_from_date IN DATE,
169 p_to_date IN DATE,
170 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
171 x_error_message_code OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
172 --
173 -- Procedure : Validate_date_range
174 -- Purpose : This procedure will validate the passing date
175 --
176 -- Parameters :
177
178 FUNCTION get_num_hours( p_project_id IN NUMBER,
179 p_assignment_id IN NUMBER) RETURN NUMBER;
180 --
181 -- Function : Get_num_hours
182 -- Purpose : This function returns the number of hours scheduled
183 -- for this assignment. We are also requiring
184 -- project_id for performance.
185
186
187 FUNCTION get_res_calendar( p_resource_id IN NUMBER,
188 p_start_date IN DATE,
189 p_end_date IN DATE) RETURN NUMBER;
190
191 -- Function : Get_res_calendar
192 -- Purpose : Returns the calendar_id for the
193 -- calendar associated with this resource for the
194 -- start and end date specified. Returns null
195 -- if 0 or more than 1 calendar is specified for
196 -- the given dates.
197
198
199 -- Function : get_res_calendar_name
200 -- Purpose : Returns the calendar_name for the
201 -- calendar associated with this resource for the
202 -- given date. Returns null
203 -- if 0 or more than 1 calendar is specified for
204 -- the given dates.
205 FUNCTION get_res_calendar_name( p_resource_id IN NUMBER,
206 p_date IN DATE,
207 p_person_id IN NUMBER DEFAULT NULL) RETURN VARCHAR2;
208
209
210 -- Returns 'Y' if requirement/assignment is in the desired system
211 -- status for the entire duration of the requirement/assignment.
212 -- Otherwise returns 'N'.
213 -- p_assignment_id - assignment/requirement id
214 -- p_status_type - The value is either 'OPEN_ASGMT'
215 -- or 'STAFFED_ASGMT'. Please see pa_project_statuses.status_type
216 -- for list of current values.
217 -- p_in_system_status_code - Desired system status code.
218
219 FUNCTION check_input_system_status
220 (p_assignment_id IN pa_project_assignments.assignment_id%TYPE,
221 p_status_type IN pa_project_statuses.status_type%TYPE,
222 p_in_system_status_code IN pa_project_statuses.project_system_status_code%TYPE) return VARCHAR2;
223
224 PROCEDURE check_calendar(p_resource_id IN NUMBER := null,
225 p_jtf_resource_id IN NUMBER := null,
226 p_start_date IN DATE,
227 p_end_date IN DATE,
228 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
229 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
230 x_msg_data OUT NOCOPY VARCHAR2 ); --File.Sql.39 bug 4440895
231
232 PROCEDURE check_calendar(p_calendar_id IN NUMBER,
233 p_start_date IN DATE,
234 p_end_date IN DATE,
235 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
236 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
237 x_msg_data OUT NOCOPY VARCHAR2 ); --File.Sql.39 bug 4440895
238
239
240 PROCEDURE check_calendar(p_calendar_type IN VARCHAR2,
241 p_calendar_id IN NUMBER := null,
242 p_resource_id IN NUMBER := null,
243 p_start_date IN DATE,
244 p_end_date IN DATE,
245 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
246 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
247 x_msg_data OUT NOCOPY VARCHAR2 ); --File.Sql.39 bug 4440895
248
249 END PA_SCHEDULE_UTILS;