1 PACKAGE pa_assignment_utils AS
2 -- $Header: PARAUTLS.pls 120.2 2005/08/19 16:48:42 mwasowic noship $
3
4 g_team_template_id PA_TEAM_TEMPLATES.team_template_id%TYPE := NULL;
5 g_team_template_name_token PA_TEAM_TEMPLATES.team_template_name%TYPE := NULL;
6 g_team_role_name_token PA_PROJECT_ASSIGNMENTS.assignment_name%TYPE := NULL;
7 g_provisional_hours NUMBER := 0;
8 g_confirmed_hours NUMBER := 0;
9 g_person_id_wo_name pa_resources_denorm.person_id%TYPE := NULL;
10 g_project_id_wo_name pa_project_assignments.project_id%TYPE := NULL;
11 g_ei_date_wo_name DATE := NULL;
12
13 g_person_id_w_name pa_resources_denorm.person_id%TYPE := NULL;
14 g_project_id_w_name pa_project_assignments.project_id%TYPE := NULL;
15 g_ei_date_w_name DATE := NULL;
16
17 g_in_asgmt_name pa_project_assignments.assignment_name%TYPE := NULL;
18 g_out_asgmt_name pa_project_assignments.assignment_name%TYPE := NULL;
19 g_assignment_id_w_name pa_project_assignments.assignment_id%TYPE := NULL;
20 g_assignment_id_wo_name pa_project_assignments.assignment_id%TYPE := NULL;
21
22 --
23 -- PROCEDURE
24 -- Check_Status_Is_In_use
25 -- PURPOSE
26 -- This procedure Checks whether a given status is used in
27 -- Assignments and assignment schedules
28 -- HISTORY
29 -- 16-JUL-2000 R. Krishnamurthy Created
30 --
31 PROCEDURE Check_Status_Is_In_use
32 ( p_status_code IN pa_project_statuses.project_status_code%TYPE
33 ,x_in_use_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
34 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
35 ,x_error_message_code OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
36
37 -- PROCEDURE
38 -- Validate_Asgmt_Competency
39 -- PURPOSE
40 -- This procedure validates the competencies for an assignment
41 -- HISTORY
42 -- 17-JUL-2000 R. Krishnamurthy Created
43 --
44 PROCEDURE Validate_Asgmt_Competency
45 ( p_project_id IN pa_projects_all.project_id%TYPE DEFAULT NULL
46 ,p_assignment_id IN pa_project_assignments.assignment_id%TYPE
47 ,p_competence_id IN per_competences.competence_id%TYPE
48 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
49 ,x_error_message_code OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
50
51 -- PROCEDURE
52 -- Get_Def_Asgmt_Statuses
53 -- This procedure returns the default assignment statuses
54 -- 17-JUL-2000 R. Krishnamurthy Created
55
56 PROCEDURE Get_Def_Asgmt_Statuses
57 (x_starting_oa_status OUT NOCOPY pa_project_statuses.project_status_code%TYPE, --File.Sql.39 bug 4440895
58 x_starting_sa_status OUT NOCOPY pa_project_statuses.project_status_code%TYPE, --File.Sql.39 bug 4440895
59 x_starting_fa_status OUT NOCOPY pa_project_statuses.project_status_code%TYPE, --File.Sql.39 bug 4440895
60 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
61 x_error_message_code OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
62
63 -- FUNCTION
64 -- Get_project_id
65 -- This function returns the project id for a given assignment
66
67 -- 17-JUL-2000 R. Krishnamurthy Created
68 FUNCTION Get_Project_Id (p_assignment_id IN NUMBER) RETURN NUMBER;
69
70 FUNCTION Is_Confirmed_Status
71 (p_status_code IN pa_project_statuses.project_status_code%TYPE ,
72 p_status_type IN pa_project_statuses.status_type%TYPE )
73 return VARCHAR2;
74 -- This function returns whether a given assignment status is
75 -- a confirmed status or not
76 -- 18-JUL-2000 R. Krishnamurthy Created
77
78 pragma RESTRICT_REFERENCES (Is_Confirmed_Status, WNDS, WNPS);
79
80 FUNCTION Is_Provisional_Status
81 (p_status_code IN pa_project_statuses.project_status_code%TYPE ,
82 p_status_type IN pa_project_statuses.status_type%TYPE )
83 return VARCHAR2;
84 -- This function returns whether a given assignment status is
85 -- a Provisional status or not
86 -- 18-JUL-2000 R. Krishnamurthy Created
87 pragma RESTRICT_REFERENCES (Is_Provisional_Status, WNDS, WNPS);
88
89 FUNCTION Is_Asgmt_Filled
90 (p_status_code IN pa_project_statuses.project_status_code%TYPE ,
91 p_status_type IN pa_project_statuses.status_type%TYPE )
92 return VARCHAR2;
93 -- This function returns whether a given assignment status is
94 -- a Filled status or not
95 -- 18-JUL-2000 R. Krishnamurthy Created
96 pragma RESTRICT_REFERENCES (Is_Asgmt_Filled, WNDS, WNPS);
97
98 FUNCTION Is_Asgmt_In_Open_Status
99 (p_status_code IN pa_project_statuses.project_status_code%TYPE ,
100 p_status_type IN pa_project_statuses.status_type%TYPE )
101 return VARCHAR2;
102 -- This function returns whether a given assignment status is
103 -- an Open status or not
104 -- 18-JUL-2000 R. Krishnamurthy Created
105 pragma RESTRICT_REFERENCES (Is_Asgmt_In_Open_Status, WNDS, WNPS);
106
107 FUNCTION Is_Open_Asgmt_Cancelled
108 (p_status_code IN pa_project_statuses.project_status_code%TYPE ,
109 p_status_type IN pa_project_statuses.status_type%TYPE )
110 return VARCHAR2;
111 -- This function returns whether a given open assignment status is
112 -- a cancelled status or not
113 -- 18-JUL-2000 R. Krishnamurthy Created
114 pragma RESTRICT_REFERENCES (Is_Open_Asgmt_Cancelled, WNDS, WNPS);
115
116 FUNCTION Is_Staffed_Asgmt_Cancelled
117 (p_status_code IN pa_project_statuses.project_status_code%TYPE ,
118 p_status_type IN pa_project_statuses.status_type%TYPE )
119 return VARCHAR2;
120 -- This function returns whether a given staffed assignment status is
121 -- a cancelled status or not
122 -- 18-JUL-2000 R. Krishnamurthy Created
123 pragma RESTRICT_REFERENCES (Is_Staffed_Asgmt_Cancelled, WNDS, WNPS);
124
125 FUNCTION Check_input_system_status
126 (p_status_code IN pa_project_statuses.project_status_code%TYPE ,
127 p_status_type IN pa_project_statuses.status_type%TYPE ,
128 p_in_system_status_code IN pa_project_statuses.project_system_status_code%TYPE)
129 return VARCHAR2;
130 -- This function returns whether a given status
131 -- has the specified system status
132 -- 18-JUL-2000 R. Krishnamurthy Created
133 pragma RESTRICT_REFERENCES (Check_input_system_status, WNDS, WNPS);
134
135 PROCEDURE Check_proj_Assignments_Exist
136 (p_project_id IN NUMBER
137 ,x_assignments_exist_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
138 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
139 ,x_error_message_code OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
140 -- PURPOSE
141 -- This procedure Checks whether a given project has
142 -- Assignments and assignment schedules
143
144 PROCEDURE Check_Assignment_Number_Or_Id( p_assignment_id IN pa_project_assignments.assignment_id%TYPE
145 ,p_assignment_number IN pa_project_assignments.assignment_number%TYPE
146 ,p_check_id_flag IN VARCHAR2 := 'A'
147 ,x_assignment_id OUT NOCOPY pa_project_assignments.assignment_id%TYPE --File.Sql.39 bug 4440895
148 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
149 ,x_error_message_code OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
150
151 --Validates the assignment number
152
153
154
155 --
156 --Validate the Staffing Priority Name/Code
157 --
158 PROCEDURE Check_STF_PriorityName_Or_Code (p_staffing_priority_code IN pa_project_assignments.staffing_priority_code%TYPE
159 ,p_staffing_priority_name IN pa_lookups.meaning%TYPE
160 ,p_check_id_flag IN VARCHAR2
161 ,x_staffing_priority_code OUT NOCOPY pa_project_assignments.staffing_priority_code%TYPE --File.Sql.39 bug 4440895
162 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
163 ,x_error_message_code OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
164 --
165 --Possible values for return: 'Roll On', 'Roll Off', 'Pending Approval'
166 --
167 --Use the dates passed in to decided if the assignment is rolling on, or rolling off or pending approval.
168
169 FUNCTION get_role_activity_text (p_assignment_id IN NUMBER,
170 p_start_date IN DATE,
171 p_end_date IN DATE,
172 p_apprvl_status_code IN VARCHAR2,
173 p_num_of_weeks IN NUMBER) RETURN VARCHAR2;
174
175
176 --
177 --Possible values for return: start_date, end_date
178 --
179 --IF assignment rolling on, then return start_date
180 --IF assignment rolling off, then return end_date
181 --IF pending approval, then return start_date
182
183 FUNCTION get_role_activity_date (p_assignment_id IN NUMBER,
184 p_start_date IN DATE,
185 p_end_date IN DATE,
186 p_apprvl_status_code IN VARCHAR2,
187 p_num_of_weeks IN NUMBER) RETURN DATE;
188
189 PROCEDURE Add_Message(p_app_short_name IN VARCHAR2,
190 p_msg_name IN VARCHAR2,
191 p_token1 IN VARCHAR2 DEFAULT NULL,
192 p_value1 IN VARCHAR2 DEFAULT NULL);
193
194 FUNCTION is_asgmt_allow_stus_ctl_check(p_asgmt_status_code IN pa_project_statuses.project_status_code%TYPE,
195 p_project_id IN pa_projects_all.project_id%TYPE,
196 p_add_message IN VARCHAR2)
197 RETURN VARCHAR2;
198
199 PROCEDURE Get_Person_Asgmt
200 ( p_person_id IN pa_resources_denorm.person_id%TYPE
201 ,p_project_id IN pa_project_assignments.project_id%TYPE
202 ,p_ei_date IN DATE
203 ,x_assignment_name IN OUT NOCOPY pa_project_assignments.assignment_name%TYPE --File.Sql.39 bug 4440895
204 ,x_assignment_id OUT NOCOPY pa_project_assignments.assignment_id%TYPE --File.Sql.39 bug 4440895
205 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
206 ,x_error_message_code OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
207
208 FUNCTION Get_Assignment_Measures
209 ( p_assignment_id IN pa_project_assignments.assignment_id%TYPE
210 ,p_resource_id IN pa_project_assignments.resource_id%TYPE
211 ,p_asgn_effort IN pa_project_assignments.assignment_effort%TYPE
212 ,p_asgn_start_date IN pa_project_assignments.start_date%TYPE
213 ,p_asgn_end_date IN pa_project_assignments.end_date%TYPE
214 ,p_multiple_status_flag IN pa_project_assignments.multiple_status_flag%TYPE)
215 RETURN NUMBER;
216
217 FUNCTION Get_Asgn_Provisional_Hours
218 RETURN NUMBER;
219
220 FUNCTION Get_Asgn_Confirmed_Hours
221 RETURN NUMBER;
222
223 --
224 -- PROCEDURE
225 -- Get Default Staffing Owner
226 -- PURPOSE
227 -- This procedure returns the default team role
228 -- staffing owner given the project_id and exp_org_id
229 -- HISTORY
230 -- 29-APR-2003 shyugen Created
231 --
232 PROCEDURE Get_Default_Staffing_Owner
233 ( p_project_id IN pa_projects_all.project_id%TYPE
234 ,p_exp_org_id IN pa_project_assignments.expenditure_org_id%TYPE := NULL
235 ,x_person_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
236 ,x_person_name OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
237 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
238 ,x_error_message_code OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
239
240 ------------------------------------------------------------------------------
241 -- PROCEDURE
242 -- Get All Staffing Owner
243 -- PURPOSE
244 -- This procedure returns the project and team role
245 -- staffing owners for a team role
246 -- HISTORY
247 -- 29-APR-2003 shyugen Created
248 ------------------------------------------------------------------------------
249 PROCEDURE Get_All_Staffing_Owners
250 ( p_assignment_id IN pa_project_assignments.assignment_id%TYPE
251 ,p_project_id IN pa_projects_all.project_id%TYPE
252 ,x_person_id_tbl OUT NOCOPY system.pa_num_tbl_type --File.Sql.39 bug 4440895
253 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
254 ,x_error_message_code OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
255
256 ------------------------------------------------------------------------------
257 -- PROCEDURE
258 -- Associate Planning Resource
259 -- PURPOSE
260 -- This procedure finds and associate planning resource to
261 -- existing Team Roles
262 -- HISTORY
263 -- 29-APR-2003 shyugen Created
264 ------------------------------------------------------------------------------
265 PROCEDURE Associate_Planning_Resources
266 ( p_project_id IN NUMBER
267 ,p_old_resource_list_id IN NUMBER
268 ,p_new_resource_list_id IN NUMBER
269 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
270 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
271 ,x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
272
273 FUNCTION Get_multi_team_role_flag RETURN VARCHAR2; /* Added new function to check if team role is associated with multiple assignement
274 or requirement for bug 3724780 */
275 /* Added for bug 3724780, to get assignment_id from pa_project_assignments */
276 FUNCTION Get_project_assignment_id
277 (p_resource_list_member_id IN NUMBER,
278 p_project_id IN NUMBER) RETURN NUMBER;
279 FUNCTION Get_project_assignment_type
280 (p_resource_list_member_id IN NUMBER,
281 p_project_id IN NUMBER) RETURN VARCHAR2;
282
283
284
285 ------------------------------------------------------------------------------
286 -- FUNCTION
287 -- Check_Res_Format_Used_For_TR
288 -- PURPOSE
289 -- This function checks if the resource format is the default
290 -- format for Requirement creation.
291 -- HISTORY
292 -- 22-JUL-2004 clevesqu Created
293 ------------------------------------------------------------------------------
294 FUNCTION Check_Res_Format_Used_For_TR(p_res_format_id IN NUMBER, p_resource_list_id IN NUMBER) RETURN VARCHAR2;
295
296 ------------------------------------------------------------------------------
297 -- FUNCTION
298 --
299 -- PURPOSE
300 -- This function checks if the resource list member_id has a single submitted status
301 -- for the project
302 -- HISTORY
303 -- 09-10-2004 jraj Created
304 ------------------------------------------------------------------------------
305 FUNCTION Get_single_submitted_status(p_project_id IN NUMBER, p_resource_list_member_id IN NUMBER) RETURN VARCHAR2;
306
307 ------------------------------------------------------------------------------
308 -- FUNCTION
309 --
310 -- PURPOSE
311 -- This function checks if the resource list member_id has task assignment's beyond team role dates
312 -- HISTORY
313 -- 09-15-2004 jraj Created
314 ------------------------------------------------------------------------------
315 FUNCTION Get_At_Risk_Status(p_project_id IN NUMBER, p_resource_list_member_id IN NUMBER, p_budget_version_id IN NUMBER, p_start IN VARCHAR2) RETURN VARCHAR2;
316
317
318 FUNCTION Get_Team_Role_Start(p_project_id IN NUMBER, p_resource_list_member_id IN NUMBER)
319 RETURN DATE;
320
321 FUNCTION Get_Team_Role_End(p_project_id IN NUMBER, p_resource_list_member_id IN NUMBER)
322 RETURN DATE ;
323
324 -- 4363092 Added following function for MOAC Changes
325 -- returns default org_id
326 FUNCTION Get_Dft_Info
327 RETURN NUMBER;
328
329 end PA_ASSIGNMENT_UTILS ;
[Home] [Help]