1 PACKAGE body hr_assignment_set AS
2 /* $Header: pyusasst.pkb 120.0 2005/05/29 09:17:43 appldev noship $ */
3
4 /*
5 +=====================================================================+
6 | Copyright (c) 1997 Orcale Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +=====================================================================+
10 Name : pyusasst.pkb
11
12 Description : The set of fuction will check and return 'Y' or 'N'
13 based on the data. If the assignment_id or the person id
14 is part of assignment_set then the function will return
15 true else it will return false.
16 Change List
17 -----------
18
19 Version Date Author ER/CR No. Description of Change
20 -------+---------+---------------+---------+--------------------------
21 40.0 09-Aug-00 djoshi Date Created
22
23 =============================================================================
24
25 */
26 /* The following function will check if the assginment is
27 part of assignment set or Not. If no assignment_set is defined then
28 fuction will always return 'Y'. It is assumed that the
29 assignment_id will be a valid assignemnt_id for the given period of
30 time. The checking will be done in the query. In a multi-threaded rep
31 the assinment creation cursor will take care picking up the
32 correct assignemnt_id based on the date.
33 The logic of checking for person id is also the same. If the assignment_id
34 of the person_id is in assignment_set then the fuction will return 'Y' and
35 if no assignment_set_id is passed then also the fuction will return 'Y'
36 in all other case the fuction will return 'N'.
37 */
38
39 FUNCTION assignment_in_set(p_assignmentset_id number,
40 p_assignment_id number )
41 RETURN char IS
42
43
44
45 CURSOR c_assignment_set(c_assignmentset_id number,c_assignment_id number) IS
46 SELECT 'Y' FROM hr_assignment_set_amendments
47 WHERE assignment_set_id = c_assignmentset_id
48 AND assignment_id = c_assignment_id
49 AND upper(include_or_exclude) = 'I';
50
51
52
53 c_value varchar2(10);
54
55
56 BEGIN
57
58 IF p_assignmentset_id IS NULL THEN
59 return 'Y';
60 END IF;
61
62 OPEN c_assignment_set(p_assignmentset_id,p_assignment_id);
63 FETCH c_assignment_set INTO c_value;
64 IF c_value ='Y' THEN
65 return 'Y';
66 ELSE
67 return 'N';
68 END IF;
69 EXCEPTION
70 WHEN OTHERS THEN
71 return 'N';
72 END; /* assignment_in_set */
73
74 /* Following fuction checks to see if, for the person_id
75 and assignment_set_id combination is there any assignment_id
76 in the assignment set that iS included. If Any assignment_id
77 for a given person_id satisfies the above condition the fuction
78 return 'Y'. If no assignment_set_id is passed then also the
79 function will return 'Y'.Any error will make the fuction
80 return 'F' Any error will make the fuction
81 return 'F'
82
83 */
84
85
86 FUNCTION person_in_set(p_assignment_set_id number,
87 p_person_id number )
88 RETURN char IS
89
90
91 CURSOR c_person_set(c_assignmentset_id number,c_person_id number) IS
92 select 'Y' from
93 per_assignments_f paf,
94 hr_assignment_set_amendments hasa
95 where
96 hasa.assignment_set_id = C_ASSIGNMENTSET_ID AND
97 hasa.ASSIGNMENT_ID = paf.assignment_id AND
98 UPPER(hasa.include_or_exclude) = 'I' AND
99 paf.person_id = C_PERSON_ID;
100 c_value varchar2(10);
101 Begin
102 IF p_assignment_set_id IS NULL THEN
103 return 'Y';
104 END IF;
105
106 OPEN c_person_set(p_assignment_set_id,p_person_id);
107 FETCH c_person_set INTO c_value;
108 IF c_value ='Y' THEN
109 return 'Y';
110 ELSE
111 return 'N';
112 END IF;
113 EXCEPTION
114 WHEN OTHERS THEN
115 return 'N';
116 END;
117
118
119 END HR_ASSIGNMENT_SET;
[Home] [Help]