1 PACKAGE BODY per_manage_contracts_pkg AS
2 /* $Header: pemancon.pkb 115.3 2002/12/06 11:56:19 pkakar noship $ */
3 --
4 --
5 -- Returns a summary flag indicating the type of association between a person and their contracts.
6 --
7 -- 'N' - Person has no contracts.
8 -- 'Y' - Person has contracts
9 --
10 FUNCTION contract_association
11 (p_person_id IN NUMBER) RETURN VARCHAR2 IS
12 CURSOR cContract(p_person_id NUMBER) IS
13 SELECT 'Y'
14 FROM per_people_f per
15 ,per_contracts_f con
16 ,fnd_sessions ses
17 WHERE per.person_id = p_person_id
18 AND con.person_id = per.person_id
19 AND ses.session_id = USERENV('sessionid')
20 AND ses.effective_date BETWEEN per.effective_start_date
21 AND per.effective_end_date
22 AND ses.effective_date BETWEEN con.effective_start_date
23 AND con.effective_end_date;
24 v_contracts_flag char;
25 BEGIN
26 OPEN cContract(p_person_id);
27 FETCH cContract INTO v_contracts_flag;
28 IF cContract%NOTFOUND THEN
29 -- Person has no contracts.
30 --
31 CLOSE cContract;
32 RETURN 'N';
33 ELSE
34 -- Person has contracts.
35 --
36 CLOSE cContract;
37 RETURN v_contracts_flag;
38 END IF;
39 END contract_association;
40 --
41 -- Returns the flexfield structures for a business group.
42 --
43 PROCEDURE get_flex_structures
44 (p_business_group_id IN NUMBER
45 ,p_grade_structure IN OUT NOCOPY NUMBER
46 ,p_people_group_structure IN OUT NOCOPY NUMBER
47 ,p_job_structure IN OUT NOCOPY NUMBER
48 ,p_position_structure IN OUT NOCOPY NUMBER) IS
49 CURSOR cFlexStructures(p_business_group_id NUMBER) IS
50 SELECT grade_structure
51 ,people_group_structure
52 ,job_structure
53 ,position_structure
54 FROM per_business_groups
55 WHERE business_group_id = p_business_group_id;
56 BEGIN
57 OPEN cFlexStructures(p_business_group_id);
58 FETCH cFlexStructures INTO p_grade_structure
59 ,p_people_group_structure
60 ,p_job_structure
61 ,p_position_structure;
62 CLOSE cFlexStructures;
63 END get_flex_structures;
64 --
65 -- Calls hr_contract_api.update_contract in date-track CORRECTION mode for all records
66 -- matching supplied contract_id argument, (excluding that which matches the supplied object_version_number
67 -- if the exclude flag is set to 'Y'), passing only the required arguements plus current values for
68 -- user_status and user_status_date_change.
69 --
70 PROCEDURE update_contracts
71 (p_contract_id IN number
72 ,p_object_version_number IN OUT NOCOPY number
73 ,p_doc_status IN varchar2
74 ,p_doc_status_change_date IN date
75 ,p_exclude_flag IN char)
76 IS
77 --
78 CURSOR c_all_con IS
79 SELECT contract_id,
80 effective_start_date,
81 object_version_number,
82 person_id,
83 reference,
84 type,
85 status
86 FROM per_contracts_f
87 WHERE contract_id = p_contract_id;
88 --
89 CURSOR c_exc_con IS
90 SELECT contract_id,
91 effective_start_date,
92 object_version_number,
93 person_id,
94 reference,
95 type,
96 status
97 FROM per_contracts_f
98 WHERE contract_id = p_contract_id
99 AND object_version_number <> p_object_version_number;
100 --
101 l_con c_all_con%ROWTYPE;
102 l_effective_start_date date;
103 l_effective_end_date date;
104 l_object_version_number number;
105 l_dt_mode varchar2(30) := 'CORRECTION';
106 l_get_current_ovn boolean := FALSE;
107 --
108 BEGIN
109 --
110 -- Use the appropriate cursor to use, based on flag parameter.
111 --
112 IF p_exclude_flag = 'N' THEN
113 --
114 -- update all records, including the current one (matching ovn)
115 OPEN c_all_con;
116 FETCH c_all_con INTO l_con;
117 WHILE c_all_con%FOUND LOOP
118 -- for each record, set the doc_status and doc_status_change_date attributes via
119 -- a call to hr_contract_api.update_contract using CORRECTION dt mode.
120 --
121 l_object_version_number := l_con.object_version_number;
122 --
123 -- if we are at the current record (same ovn), then set flag indicating we
124 -- should get the new ovn returned for the for current iteration.
125 IF l_object_version_number = p_object_version_number THEN
126 l_get_current_ovn := TRUE;
127 END IF;
128 --
129 hr_contract_api.update_contract
130 (p_contract_id => l_con.contract_id
131 ,p_effective_start_date => l_effective_start_date
132 ,p_effective_end_date => l_effective_end_date
133 ,p_object_version_number => l_object_version_number
134 ,p_person_id => l_con.person_id
135 ,p_reference => l_con.reference
136 ,p_type => l_con.type
137 ,p_status => l_con.status
138 ,p_doc_status => p_doc_status
139 ,p_doc_status_change_date => p_doc_status_change_date
140 ,p_effective_date => l_con.effective_start_date
141 ,p_datetrack_mode => l_dt_mode);
142 --
143 -- pass out the new ovn for the current record
144 IF l_get_current_ovn THEN
145 p_object_version_number := l_object_version_number;
146 l_get_current_ovn := FALSE;
147 END IF;
148 --
149 FETCH c_all_con INTO l_con;
150 --
151 END LOOP;
152 CLOSE c_all_con;
153 --
154 ELSIF p_exclude_flag = 'Y' THEN
155 --
156 -- update all records, excluding the current one (matching ovn)
157 OPEN c_exc_con;
158 FETCH c_exc_con INTO l_con;
159 WHILE c_exc_con%FOUND LOOP
160 -- for each record, set the doc_status and doc_status_change_date attributes via
161 -- a call to hr_contract_api.update_contract using CORRECTION dt mode.
162 --
163 l_object_version_number := l_con.object_version_number;
164 --
165 hr_contract_api.update_contract
166 (p_contract_id => l_con.contract_id
167 ,p_effective_start_date => l_effective_start_date
168 ,p_effective_end_date => l_effective_end_date
169 ,p_object_version_number => l_object_version_number
170 ,p_person_id => l_con.person_id
171 ,p_reference => l_con.reference
172 ,p_type => l_con.type
173 ,p_status => l_con.status
174 ,p_doc_status => p_doc_status
175 ,p_doc_status_change_date => p_doc_status_change_date
176 ,p_effective_date => l_con.effective_start_date
177 ,p_datetrack_mode => l_dt_mode);
178 --
179 FETCH c_exc_con INTO l_con;
180 --
181 END LOOP;
182 CLOSE c_exc_con;
183 -- pass back a null ovn value.
184 p_object_version_number := NULL;
185 --
186 --
187 END IF;
188 --
189 END update_contracts;
190 --
191 END per_manage_contracts_pkg;
[Home] [Help]