1 PACKAGE BODY PQH_FR_ASSIGNMENT_CHK As
2 /* $Header: pqasgchk.pkb 120.0 2005/05/29 01:25 appldev noship $ */
3 --
4 -- To modify this template, edit file PKGBODY.TXT in TEMPLATE
5 -- directory of SQL Navigator
6 --
7 -- Purpose: Briefly explain the functionality of the package body
8 --
9 -- MODIFICATION HISTORY
10 -- Person Date Comments
11 -- --------- ------ ------------------------------------------
12 -- Enter procedure, function bodies as shown below
13
14 g_fut_chk number;
15 procedure chk_Identifier(p_identifier in Varchar2)
16 IS
17 Cursor csr_identifier IS
18 Select null
19 from per_all_assignments_f asg,
20 hr_soft_coding_keyflex scl
21 where asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
22 and scl.segment23 = p_identifier;
23 -- As in Update Case Never Identifier is gonna change and we are not supporting
24 -- Any PUI Updates we need't have to check the assignment_id <> current_assignment_id
25 -----
26 l_temp varchar2(10);
27 Begin
28 If p_identifier is not null then
29 Open csr_identifier;
30 Fetch csr_identifier into l_temp;
31 If csr_identifier%FOUND Then
32 --
33 fnd_message.set_name('PQH','PQH_FR_IDENTIFIER_EXIST');
34 hr_multi_message.add();
35 --
36 End If;
37 Close csr_identifier;
38 End if;
39 --
40 End chk_Identifier;
41
42 procedure chk_percent_affected(p_percent_effected in varchar2, p_person_id in number, p_effective_date in date, p_assignment_id number default Null)
43 IS
44 l_percent_affected number;
45 l_fut_start_date date;
46
47 Cursor csr_future_eff is
48 select effective_start_date
49 from per_all_assignments_f assign
50 where effective_start_date > p_effective_date
51 and person_id = p_person_id
52 And assign.primary_flag = 'N';
53
54 Begin
55
56 l_percent_affected := fnd_number.canonical_to_number(p_percent_effected);
57
58 If (l_percent_affected <=0 Or l_percent_affected >100 ) Then
59 ---
60 fnd_message.set_name('PQH','PQH_FR_INVALID_PERCENTAGE');
61 hr_multi_message.add();
62 --
63 End If;
64 g_fut_chk := 0;
65 chk_tot_percent_affected(p_percent_effected,p_person_id,p_effective_date,p_assignment_id);
66
67 open csr_future_eff;
68 loop
69 fetch csr_future_eff into l_fut_start_date;
70 exit when csr_future_eff%notfound ;
71 g_fut_chk := 1;
72 chk_tot_percent_affected(p_percent_effected,p_person_id,l_fut_start_date,p_assignment_id);
73 end loop;
74 g_fut_chk := 0;
75 close csr_future_eff;
76
77 --
78 End chk_percent_affected;
79
80 procedure chk_tot_percent_affected(p_percent_effected in varchar2, p_person_id in number, p_effective_date in date, p_assignment_id number default Null)
81 IS
82 l_percent_affected number;
83 l_tot_percent_affected number;
84 l_asg_percent_affected number;
85 l_proc varchar2(30);
86 Cursor csr_tot_percent_effected Is
87 Select Sum(nvl(scl.segment25,0)) Percenteffected
88 From per_all_assignments_f assign,
89 hr_soft_coding_keyflex scl
90 Where person_id = p_person_id
91 And assign.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
92 And assign.primary_flag = 'N'
93 And p_effective_date Between effective_start_date And effective_end_date
94 And assign.assignment_status_type_id = 1;
95
96 Cursor csr_asg_percent_effected Is
97 Select nvl(scl.segment25,0) Percenteffected
98 From per_all_assignments_f assign,
99 hr_soft_coding_keyflex scl
100 Where person_id = p_person_id
101 And assign.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
102 And assign.primary_flag = 'N'
103 And p_effective_date Between effective_start_date And effective_end_date
104 And assign.assignment_status_type_id = 1
105 and assign.assignment_id = p_assignment_id;
106
107 Begin
108 l_proc := 'chk_tot_percent_affected';
109 hr_utility.set_location(' Entering '|| l_proc,10);
110 l_percent_affected := fnd_number.canonical_to_number(p_percent_effected);
111
112 if p_assignment_id is not null then
113 Open csr_asg_percent_effected;
114 Fetch csr_asg_percent_effected into l_asg_percent_affected;
115 Close csr_asg_percent_effected;
116 else
117 l_asg_percent_affected := 0;
118 end if;
119
120 Open csr_tot_percent_effected;
121 Fetch csr_tot_percent_effected into l_tot_percent_affected;
122 If csr_tot_percent_effected%FOUND Then
123 --
124 l_tot_percent_affected := l_tot_percent_affected + l_percent_affected - l_asg_percent_affected;
125 If (l_tot_percent_affected >100 ) Then
126 --
127 if nvl(g_fut_chk,0) = 0 then
128 fnd_message.set_name('PQH','PQH_FR_TOT_PERCENTAGE');
129 else
130 fnd_message.set_name('PQH','PQH_FR_FUT_TOT_PERCENT');
131 fnd_message.set_token('FUTDATE',to_char(p_effective_date));
132 end if;
133 hr_multi_message.add();
134 --
135 End If;
136 --
137 End If;
138 Close csr_tot_percent_effected;
139 --
140
141 hr_utility.set_location(' Exiting '|| l_proc,10);
142 --
143 End chk_tot_percent_affected;
144
145 procedure chk_position(p_position_id in Number, p_person_id in Number,p_effective_date in DATE)
146 IS
147
148 Cursor csr_tit_pos IS
149 Select nvl(information1,'N')
150 from hr_all_positions_f
151 where position_id = p_position_id;
152 --
153 Cursor csr_person_info IS
154 Select per_information15
155 from per_all_people_f
156 where person_id =p_person_id
157 and p_effective_date between effective_start_date and effective_end_date;
158 ---
159 l_titulaire_pos varchar2(10);
160 l_agent_type varchar2(10);
161
162 Begin
163
164 Open csr_tit_pos;
165 fetch csr_tit_pos into l_titulaire_pos;
166 Close csr_tit_pos;
167
168 Open csr_person_info;
169 Fetch csr_person_info into l_agent_type;
170 Close csr_person_info;
171
172 If (l_agent_type = '01' ) then -- Functionnaire
173 --
174 If (l_titulaire_pos ='N') Then
175 --
176 fnd_message.set_name('PQH','PQH_FR_NOFONC_ON_NONTIT_POS');
177 hr_multi_message.add();
178
179 --
180 End If;
181 --
182 End If;
183
184 End chk_position;
185
186 procedure chk_type(p_type in varchar2, p_person_id in Number, p_effective_date in DATE,p_position_id in Number)
187 IS
188 Cursor csr_person_info IS
189 Select per_information15
190 from per_all_people_f
191 where person_id =p_person_id
192 and p_effective_date between effective_start_date and effective_end_date;
193 --
194 Cursor csr_tit_pos IS
195 Select nvl(information1,'N')
196 from hr_all_positions_f
197 where position_id = p_position_id;
198 --
199 l_agent_type varchar2(10);
200 l_titulaire_pos varchar2(10);
201
202 Begin
203 ---
204 -- For NonTitulaire : Getting placed on Titulare position and With Type Parmenant is not allowed
205 -- P Parmanent , T Temporary
206 Open csr_person_info;
207 Fetch csr_person_info into l_agent_type;
208 Close csr_person_info;
209
210 Open csr_tit_pos;
211 fetch csr_tit_pos into l_titulaire_pos;
212 Close csr_tit_pos;
213
214
215 If (l_agent_type = '02' ) then -- Non Titulaire
216
217 If (p_type = 'P') And (l_titulaire_pos = 'Y') Then
218 --
219 fnd_message.set_name('PQH','PQH_FR_NOPERM_FOR_NONTIT');
220 hr_multi_message.add();
221 --
222 End If;
223 --
224 End If;
225 ---
226 End chk_type;
227
228 procedure chk_Primary_affectation(p_person_id in number, p_effective_date in date, p_admin_career_id in number)
229 IS
230
231 l_temp varchar2(10);
232 l_fut_start_date date;
233 -- Cursor to check if there exist any primary affectation at the effective date
234
235 Cursor is_primary_eff_exist_csr IS
236 Select null
237 from per_all_assignments_f asg, hr_soft_coding_keyflex scl
238 where segment26 = p_admin_career_id
239 and segment27 ='Y'
240 and asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
241 and p_effective_date between asg.effective_start_date and asg.effective_end_date
242 and asg.assignment_status_type_id <> 3;
243
244 -- Cursor to check if there exist any primary affectation at any future date
245
246 Cursor is_fut_primary_eff_exist_csr IS
247 Select effective_start_date
248 from per_all_assignments_f asg, hr_soft_coding_keyflex scl
249 where segment26 = p_admin_career_id
250 and segment27 ='Y'
251 and asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
252 and asg.effective_start_date > p_effective_date;
253
254 Begin
255
256 Open is_primary_eff_exist_csr;
257 Fetch is_primary_eff_exist_csr into l_temp;
258 If is_primary_eff_exist_csr%found then
259 Close is_primary_eff_exist_csr;
260 fnd_message.set_name('PQH', 'PQH_FR_TURN_OFF_EXISTING_PRI');
261 hr_multi_message.add();
262 End if;
263
264 Open is_fut_primary_eff_exist_csr;
265 loop
266 Fetch is_fut_primary_eff_exist_csr into l_fut_start_date;
267 Exit when is_fut_primary_eff_exist_csr%notfound;
268 fnd_message.set_name('PQH','PQH_FR_FUT_PRIMARY_AFF');
269 fnd_message.set_token('FUTUREDATE',to_char(l_fut_start_date));
270 hr_multi_message.add();
271 end loop;
272
273 End chk_Primary_affectation;
274
275 procedure chk_situation(p_person_id in Number,
276 p_effective_date in DATE)
277 IS
278
279 Cursor csr_person_info IS
280 Select per_information15
281 from per_all_people_f
282 where person_id =p_person_id
283 and p_effective_date between effective_start_date and effective_end_date;
284
285 Cursor csr_situation_info IS
286 select ss.situation_type
287 from pqh_fr_emp_stat_situations ess,
288 pqh_fr_stat_situations_v ss
289 where person_id = p_person_id
290 and p_effective_date between actual_start_date and nvl(actual_end_date,provisional_end_date)
291 and ess.statutory_situation_id = ss.statutory_situation_id;
292 --
293 l_agent_type varchar2(10);
294 l_situation_type varchar2(10);
295
296 Begin
297 ---
298 Open csr_person_info;
299 Fetch csr_person_info into l_agent_type;
300 Close csr_person_info;
301
302 if (l_agent_type = '01') then
303 Open csr_situation_info;
304 Fetch csr_situation_info into l_situation_type;
305 Close csr_situation_info;
306
307 if l_situation_type <> 'IA' then
308 if l_situation_type <> 'SC' then
309 fnd_message.set_name('PQH', 'PQH_FR_NOT_ACT_SIT');
310 hr_multi_message.add();
311 end if;
312 end if;
313 end if;
314
315 End chk_situation;
316
317 -- Enter further code below as specified in the Package spec.
318 END PQH_FR_ASSIGNMENT_CHK;
[Home] [Help]