1 package PA_PROJECT_UTILS AUTHID CURRENT_USER as
2 -- $Header: PAXPUTLS.pls 120.4 2011/05/30 06:18:58 a5sharma ship $
3
4 /* Added for bug 2125791*/
5
6 glob_total_rec number := 0;
7 glob_project_status_code PA_PLSQL_DATATYPES.Char30TabTyp;
8 glob_proj_sys_status_code PA_PLSQL_DATATYPES.Char30TabTyp;
9 glob_action_code PA_PLSQL_DATATYPES.Char30TabTyp;
10 glob_enabled_flag PA_PLSQL_DATATYPES.Char1TabTyp;
11 null_pointer PA_PLSQL_DATATYPES.Char30TabTyp;
12 null_pointer1 PA_PLSQL_DATATYPES.Char1TabTyp;
13
14 /* End for bug 2125791*/
15
16 --
17 -- PROCEDURE
18 -- get_project_status_code
19 -- PURPOSE
20 -- This procedure retrieves project status code for a specified
21 -- project status.
22 -- HISTORY
23 -- 20-OCT-95 R. Chiu Created
24 --
25 procedure get_project_status_code ( x_project_status IN varchar2
26 , x_project_status_code OUT NOCOPY varchar2 --File.Sql.39 bug 4440895
27 , x_err_code IN OUT NOCOPY number --File.Sql.39 bug 4440895
28 , x_err_stage IN OUT NOCOPY varchar2 --File.Sql.39 bug 4440895
29 , x_err_stack IN OUT NOCOPY varchar2); --File.Sql.39 bug 4440895
30
31 --
32 -- PROCEDURE
33 -- get_distribution_rule_code
34 -- PURPOSE
35 -- This procedure retrieves distribution rule name given the
36 -- user-friendly name that describes the distribution rule.
37 --
38 -- HISTORY
39 -- 20-OCT-95 R. Chiu Created
40 --
41 procedure get_distribution_rule_code ( x_dist_name IN varchar2
42 , x_dist_code OUT NOCOPY varchar2 --File.Sql.39 bug 4440895
43 , x_err_code IN OUT NOCOPY number --File.Sql.39 bug 4440895
44 , x_err_stage IN OUT NOCOPY varchar2 --File.Sql.39 bug 4440895
45 , x_err_stack IN OUT NOCOPY varchar2); --File.Sql.39 bug 4440895
46
47 --
48 -- PROCEDURE
49 -- get_proj_type_class_code
50 -- PURPOSE
51 -- This procedure retrieves project type class code for
52 -- a given project type or project id. If both project type
53 -- and project id are passed, then procedure treated it as if
54 -- only project id were passed.
55 --
56 -- HISTORY
57 -- 20-OCT-95 R. Chiu Created
58 --
59 procedure get_proj_type_class_code ( x_project_type IN varchar2
60 , x_project_id IN number
61 , x_proj_type_class_code OUT NOCOPY varchar2 --File.Sql.39 bug 4440895
62 , x_err_code IN OUT NOCOPY number --File.Sql.39 bug 4440895
63 , x_err_stage IN OUT NOCOPY varchar2 --File.Sql.39 bug 4440895
64 , x_err_stack IN OUT NOCOPY varchar2); --File.Sql.39 bug 4440895
65
66 --
67 -- FUNCTION
68 -- check_unique_project_name
69 -- PURPOSE
70 -- This function returns 1 if a project name is not already
71 -- used in PA system and returns 0 if name is used.
72 -- If Oracle error occurs, Oracle error number is returned.
73 -- HISTORY
74 -- 20-OCT-95 R. Chiu Created
75 --
76 function check_unique_project_name (x_project_name IN varchar2,
77 x_rowid IN varchar2 ) return number;
78 pragma RESTRICT_REFERENCES (check_unique_project_name, WNDS, WNPS);
79
80
81 --
82 -- FUNCTION
83 -- check_unique_long_name
84 -- PURPOSE
85 -- This function returns 1 if a long name is not already
86 -- used in PA system and returns 0 if name is used.
87 -- If Oracle error occurs, Oracle error number is returned.
88 -- HISTORY
89 -- 26-OCT-02 MUMOHAN Created
90 --
91 function check_unique_long_name (x_long_name IN varchar2,
92 x_rowid IN varchar2 ) return number;
93 pragma RESTRICT_REFERENCES (check_unique_long_name, WNDS, WNPS);
94
95
96 --
97 -- FUNCTION
98 -- check_unique_project_number
99 -- PURPOSE
100 -- This function returns 1 if a project number is not already
101 -- used in PA system and returns 0 if name is used.
102 -- If Oracle error occurs, Oracle error number is returned.
103 -- HISTORY
104 -- 20-OCT-95 R. Chiu Created
105 --
106 function check_unique_project_number (x_project_number IN varchar2,
107 x_rowid IN varchar2 ) return number;
108 pragma RESTRICT_REFERENCES (check_unique_project_number, WNDS, WNPS);
109
110
111 --
112 -- FUNCTION
113 -- check_unique_proj_class
114 -- PURPOSE
115 -- This function returns 1 if a project class code is
116 -- not already used for a specified project and class
117 -- category in PA system and returns 0 otherwise.
118 -- If a user does not supply all the values for project id,
119 -- x_class_category, and x_class_code, then null will
120 -- be returned.
121 -- If Oracle error occurs, Oracle error number is returned.
122 --
123 -- HISTORY
124 -- 20-OCT-95 R. Chiu Created
125 --
126 function check_unique_proj_class (x_project_id IN number
127 , x_class_category IN varchar2
128 , x_class_code IN varchar2
129 , x_rowid IN varchar2 ) return number;
130 pragma RESTRICT_REFERENCES (check_unique_proj_class, WNDS, WNPS);
131
132 --
133 -- FUNCTION
134 -- check_unique_customer
135 -- PURPOSE
136 -- This function returns 1 if a customer is unique for
137 -- the specified project and returns 0 if that customer
138 -- already exists for that project. If a user does not
139 -- supply all the values, then null is returned. If Oracle
140 -- error occurs, Oracle error number is returned.
141 --
142 -- HISTORY
143 -- 20-OCT-95 R. Chiu Created
144 --
145 function check_unique_customer (x_project_id IN number
146 , x_customer_id IN varchar2
147 , x_rowid IN varchar2 ) return number;
148 pragma RESTRICT_REFERENCES (check_unique_customer, WNDS, WNPS);
149
150 --
151 -- FUNCTION
152 -- check_project_type_valid
153 -- PURPOSE
154 -- This function returns 1 if a project type is valid in
155 -- PA system and returns 0 if it's not valid.
156 -- If Oracle error occurs, Oracle error number is returned.
157 --
158 -- HISTORY
159 -- 20-OCT-95 R. Chiu Created
160 --
161 function check_project_type_valid (x_project_type IN varchar2 ) return number;
162 pragma RESTRICT_REFERENCES (check_project_type_valid, WNDS, WNPS);
163
164 --
165 -- FUNCTION
166 -- check_manager_exists
167 -- PURPOSE
168 -- This function returns 1 if a project has an acting
169 -- manager and returns 0 if no manage is found.
170 -- If Oracle error occurs, Oracle error number is returned.
171 --
172 -- HISTORY
173 -- 20-OCT-95 R. Chiu Created
174 --
175 function check_manager_exists (x_project_id IN number ) return number;
176 pragma RESTRICT_REFERENCES (check_manager_exists, WNDS, WNPS);
177
178 --
179 -- FUNCTION
180 -- check_bill_split
181 -- PURPOSE
182 -- This function returns 1 if a project has total customer
183 -- contribution of 100% and returns 0 if total contribution
184 -- is less than 100%.
185 -- If Oracle error occurs, Oracle error number is returned.
186 --
187 -- HISTORY
188 -- 20-OCT-95 R. Chiu Created
189 --
190 function check_bill_split (x_project_id IN number ) return number;
191 pragma RESTRICT_REFERENCES (check_bill_split, WNDS, WNPS);
192
193 -- FUNCTION
194 -- check_bill_contact_exists
195 -- PURPOSE
196 -- This function returns 1 if a project has a billing contact
197 -- for a customer whose contribution is greater than 0 and
198 -- returns 0 if this condition is not met for that project.
199 -- If Oracle error occurs, Oracle error number is returned.
200 --
201 -- HISTORY
202 -- 20-OCT-95 R. Chiu Created
203 --
204 function check_bill_contact_exists (x_project_id IN number ) return number;
205 pragma RESTRICT_REFERENCES (check_bill_contact_exists, WNDS, WNPS);
206
207 -- FUNCTION
208 -- check_class_category
209 -- PURPOSE
210 -- This function returns 1 if a project has all the mandatory
211 -- class categories and returns 0 if mandatory class category
212 -- is missing.
213 -- If Oracle error occurs, Oracle error number is returned.
214 --
215 -- HISTORY
216 -- 20-OCT-95 R. Chiu Created
217 --
218 function check_class_category (x_project_id IN number ) return number;
219 pragma RESTRICT_REFERENCES (check_class_category, WNDS, WNPS);
220
221
222 -- FUNCTION
223 -- check_draft_inv_exists
224 -- PURPOSE
225 -- This function returns 1 if draft invoice exists for a project
226 -- and returns 0 if no draft invoice is found.
227 --
228 -- If Oracle error occured, Oracle error code is returned.
229 --
230 -- HISTORY
231 -- 20-OCT-95 R. Chiu Created
232 --
233 function check_draft_inv_exists (x_project_id IN number ) return number;
234 pragma RESTRICT_REFERENCES (check_draft_inv_exists, WNDS, WNPS);
235
236
237 -- FUNCTION
238 -- check_draft_rev_exists
239 -- PURPOSE
240 -- This function returns 1 if draft revenue exists for a project
241 -- and returns 0 if no draft revenue is found.
242 --
243 -- If Oracle error occured, Oracle error code is returned.
244 --
245 -- HISTORY
246 -- 20-OCT-95 R. Chiu Created
247 --
248 function check_draft_rev_exists (x_project_id IN number ) return number;
249 pragma RESTRICT_REFERENCES (check_draft_rev_exists, WNDS, WNPS);
250
251
252 -- FUNCTION
253 -- check_created_proj_reference
254 -- PURPOSE
255 -- This function returns 1 if a project is referenced
256 -- by another project in pa_projects.created_from_project_id
257 -- and returns 0 if a project is not referenced.
258 --
259 -- If Oracle error occured, Oracle error code is returned.
260 --
261 -- HISTORY
262 -- 20-OCT-95 R. Chiu Created
263 --
264 function check_created_proj_reference (x_project_id IN number ) return number;
265 pragma RESTRICT_REFERENCES (check_created_proj_reference, WNDS, WNPS);
266
267
268 --
269 -- PROCEDURE
270 -- check_delete_project_ok
271 -- PURPOSE
272 -- This procedure checks if it is OK to delete a project
273 -- HISTORY
274 -- 04-JAN-96 S. Lee Created
275 --
276 procedure check_delete_project_ok ( x_project_id IN number
277 , x_validation_mode IN VARCHAR2 DEFAULT 'U' --Bug 2947492
278 , x_err_code IN OUT NOCOPY number --File.Sql.39 bug 4440895
279 , x_err_stage IN OUT NOCOPY varchar2 --File.Sql.39 bug 4440895
280 , x_err_stack IN OUT NOCOPY varchar2); --File.Sql.39 bug 4440895
281 --
282 -- PROCEDURE
283 -- change_pt_org_ok
284 -- PURPOSE
285 -- This procedure checks if a project has CDLs,Rev or
286 -- Draft invoices.If project has any of
287 -- these information, then it's not ok to change the project
288 -- type or org and specific reason will be returned.
289 -- If it's ok to change project type or org,
290 -- the x_err_code will be 0.
291 --
292 -- HISTORY
293 -- 13-JAN-96 R.Krishnamurthy Created
294 --
295
296 procedure change_pt_org_ok ( x_project_id IN number
297 , x_err_code IN OUT NOCOPY number --File.Sql.39 bug 4440895
298 , x_err_stage IN OUT NOCOPY varchar2 --File.Sql.39 bug 4440895
299 , x_err_stack IN OUT NOCOPY varchar2); --File.Sql.39 bug 4440895
300 --
301 -- PROCEDURE
302 -- change_proj_num_ok
303 -- PURPOSE
304 -- This procedure checks if a project has exp items,po reqs,
305 -- Draft invoices,po dists,ap invoices and ap inv dists .
306 -- If project has any of
307 -- these information, then it's not ok to change the project
308 -- number If it's ok to change project number
309 -- the x_err_code will be 0.
310 --
311 -- HISTORY
312 -- 15-JAN-96 R.Krishnamurthy Created
313 --
314
315 procedure change_proj_num_ok ( x_project_id IN number
316 , x_err_code IN OUT NOCOPY number --File.Sql.39 bug 4440895
317 , x_err_stage IN OUT NOCOPY varchar2 --File.Sql.39 bug 4440895
318 , x_err_stack IN OUT NOCOPY varchar2); --File.Sql.39 bug 4440895
319 -- FUNCTION
320 -- check_proj_funding
321 -- PURPOSE
322 -- This function returns 1 if funding exists for a project
323 -- with allocated amount > 0.Returns 0 if allocated amount <- 0
324 -- or there are no fundings for that project. If fundings
325 -- exist and allocated amount > 0 then , function returns 1.
326 -- If Oracle error occured, Oracle error code is returned.
327 --
328 -- HISTORY
329 -- 16-JAN-96 R. Krishnamurthy Created
330 --
331 function check_proj_funding (x_project_id IN number ) return number;
332 pragma RESTRICT_REFERENCES (check_proj_funding, WNDS, WNPS);
333
334 -- FUNCTION
335 -- check_option_child_exists
336 -- PURPOSE
337 -- This function returns Y if child exists for a project
338 -- option and N otherwise
339 --
340 -- HISTORY
341 -- 13-DEC-1996 D.Roy Created
342 --
343 function check_option_child_exists (p_option_code IN VARCHAR2 )
344 return VARCHAR2;
345 pragma RESTRICT_REFERENCES (check_option_child_exists, WNDS, WNPS);
346
347 -- PROCEDURE
348 -- check_dist_rule_chg_ok
349 -- PURPOSE
350 -- This procedure checks whether it is ok
351 -- to change the Distribution rule
352 -- If it's ok to change Distribution rule
353 -- the x_err_code will be 0.
354 --
355 -- HISTORY
356 -- 17-APR-96 R.Krishnamurthy Created
357 --
358
359 procedure check_dist_rule_chg_ok ( x_project_id IN number
360 , x_old_dist_rule IN varchar2
361 , x_new_dist_rule IN varchar2
362 , x_err_code IN OUT NOCOPY number --File.Sql.39 bug 4440895
363 , x_err_stage IN OUT NOCOPY varchar2 --File.Sql.39 bug 4440895
364 , x_err_stack IN OUT NOCOPY varchar2); --File.Sql.39 bug 4440895
365
366 FUNCTION GetProjNumMode RETURN VARCHAR2;
367 pragma RESTRICT_REFERENCES (GetProjNumMode, WNDS, WNPS);
368
369 FUNCTION GetProjNumType RETURN VARCHAR2;
370 pragma RESTRICT_REFERENCES (GetProjNumType, WNDS, WNPS);
371
372 -- FUNCTION
373 -- Check_project_action_allowed
374 -- PURPOSE
375 -- This function returns 'N' or 'Y'
376 -- depending on whether the given action is allowed for
377 -- a project. It returns the value returned by the
378 -- Check_prj_stus_action_allowed function
379
380 FUNCTION Check_project_action_allowed
381 (x_project_id IN NUMBER,
382 x_action_code IN VARCHAR2 ) return VARCHAR2;
383 pragma RESTRICT_REFERENCES (Check_project_action_allowed, WNDS);/*Removed WNPS for bug 2125791*/
384
385 -- FUNCTION
386 -- Check_prj_stus_action_allowed
387 -- PURPOSE
388 -- This function returns 'N' or 'Y'
389 -- depending on whether the given action is allowed for
390 -- the project status.
391
392 FUNCTION Check_prj_stus_action_allowed
393 (x_project_status_code IN VARCHAR2,
394 x_action_code IN VARCHAR2 ) return VARCHAR2;
395
396 pragma RESTRICT_REFERENCES (Check_prj_stus_action_allowed, WNDS);/*Removed WNPS for bug 2125791*/
397
398 -- FUNCTION
399 -- Check_sys_action_allowed
400 -- PURPOSE
401 -- This function returns 'N' or 'Y'
402 -- depending on whether the given action is allowed for
403 -- the project system status.
404
405 FUNCTION Check_sys_action_allowed
406 (x_project_system_status_code IN VARCHAR2,
407 x_action_code IN VARCHAR2 ) return VARCHAR2;
408
409 pragma RESTRICT_REFERENCES (Check_sys_action_allowed, WNDS); /*Removed WNPS for bug 2125791*/
410
411 --
412 -- FUNCTION
413 -- is_tp_schd_proj_task
414 -- PURPOSE
415 -- This function returns 'N' or 'Y'
416 -- depending on whether the given schedule_id is in any
417 -- of the project/task.
418 -- HISTORY
419 -- 03-AUG-99 sbalasub Created
420 --
421 function is_tp_schd_proj_task (p_tp_schedule_id IN Number) return varchar2;
422 pragma RESTRICT_REFERENCES (is_tp_schd_proj_task, WNDS, WNPS);
423
424
425 -- FUNCTION
426 -- Is_Admin_Project
427 -- PURPOSE
428 -- This function checks if a given project_id is
429 -- an Admin Project. If it is an Admin project
430 -- then the function returns 'Y'. If not, then the
431 -- function returns 'N'.
432 --
433 -- HISTORY
434 -- 21-NOV-00 A.Layton Created
435 --
436 FUNCTION Is_Admin_Project (p_project_id IN pa_projects_all.project_id%TYPE)
437 RETURN VARCHAR2;
438 pragma RESTRICT_REFERENCES (Is_Admin_Project, WNDS, WNPS);
439
440 -- FUNCTION
441 -- Is_Admin_Project
442 -- PURPOSE
443 -- This function checks if a given project_id is
444 -- an Admin Project. If it is an Admin project
445 -- then the function returns 'Y'. If not, then the
446 -- function returns 'N'.
447 --
448 -- HISTORY
449 -- 21-NOV-00 A.Layton Created
450 --
451 FUNCTION Is_Unassigned_Time_Project (p_project_id IN pa_projects_all.project_id%TYPE)
452 RETURN VARCHAR2;
453
454 FUNCTION IsUserProjectManager (p_project_id IN NUMBER,
455 p_user_id IN NUMBER) return varchar2;
456
457 pragma RESTRICT_REFERENCES (Is_Unassigned_Time_Project, WNDS, WNPS);
458
459 procedure check_delete_project_type_ok (
460 p_project_type_id IN NUMBER
461 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
462 ,x_error_message_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
463 );
464
465 /*Start: Addition of code for bug 2682806 */
466
467 Procedure check_delete_class_catg_ok (
468 p_class_category IN VARCHAR2
469 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
470 ,x_error_message_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
471 );
472
473 Procedure check_delete_class_code_ok (
474 p_class_category IN VARCHAR2
475 ,p_class_code IN VARCHAR2
476 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
477 ,x_error_message_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
478 );
479
480 /*End: Addition of code for bug 2682806 */
481
482 function check_unique_project_reference (p_proj_ref IN varchar2,
483 p_prod_code IN varchar2, -- added for bug 4870305
484 p_rowid IN varchar2 ) return number;
485 pragma RESTRICT_REFERENCES (check_unique_project_reference, WNDS, WNPS);
486
487 --bug#2984611
488 function check_ic_proj_type_allowed(p_project_id IN NUMBER
489 ,p_cc_prvdr_flag IN VARCHAR2)
490 RETURN NUMBER ;
491
492
493 -- Added for bug 3738892
494 function is_flex_enabled ( appl_id IN number, flex_name IN varchar2)
495 RETURN NUMBER;
496
497 -- Added for Bug 5647964
498 PROCEDURE VALIDATE_DFF
499 ( p_application_id IN NUMBER,
500 p_flexfield_name IN VARCHAR2,
501 p_attribute_category IN VARCHAR2,
502 p_calling_module IN VARCHAR2,
503 p_attribute1 IN VARCHAR2,
504 p_attribute2 IN VARCHAR2,
505 p_attribute3 IN VARCHAR2,
506 p_attribute4 IN VARCHAR2,
507 p_attribute5 IN VARCHAR2,
508 p_attribute6 IN VARCHAR2,
509 p_attribute7 IN VARCHAR2,
510 p_attribute8 IN VARCHAR2,
511 p_attribute9 IN VARCHAR2,
512 p_attribute10 IN VARCHAR2,
513 p_attribute11 IN VARCHAR2,
514 p_attribute12 IN VARCHAR2,
515 p_attribute13 IN VARCHAR2,
516 p_attribute14 IN VARCHAR2,
517 p_attribute15 IN VARCHAR2,
518 x_return_status OUT NOCOPY VARCHAR2,
519 x_msg_count OUT NOCOPY NUMBER,
520 x_msg_data OUT NOCOPY VARCHAR2);
521 --Added for bug 12345249
522 FUNCTION get_person_id(p_user_id IN NUMBER)
523 RETURN NUMBER;
524
525 end PA_PROJECT_UTILS ;
[Home] [Help]