1 PACKAGE BODY PQP_GB_PENSRV_SVPN AS
2 /* $Header: pqpgbpsispn.pkb 120.4 2008/01/09 03:59:44 rlingama noship $ */
3
4 -- Globals
5
6 g_package_name VARCHAR2(100);
7 g_legislation_code per_business_groups.legislation_code%TYPE := 'GB';
8 g_business_group_id NUMBER := NULL;
9 g_svpn_flag VARCHAR2(4);
10 g_eff_start_date DATE := to_date('31-07-2007','DD-MM-YYYY');
11 g_eff_end_date DATE;
12 g_asg_membership_context VARCHAR2(30);
13 g_asg_membership_col VARCHAR2(30);
14
15 --
16 -- This Function is used to check whther the assignment is eligible for to
17 -- generate service period number
18 --
19 FUNCTION chk_emp_eligibility(p_assignment_id IN NUMBER
20 ) RETURN DATE
21 IS
22 l_query VARCHAR2(2000);
23 l_value DATE := NULL;
24
25 TYPE base_table_ref_typ IS REF CURSOR;
26 cur_get_value base_table_ref_typ;
27
28 BEGIN
29
30 l_query := 'select max(effective_start_date)'||' '||
31 'from per_all_assignments_f '||' '||
32 'where business_group_id = '||g_business_group_id||' '||
33 'and assignment_id = '||p_assignment_id||' '||
34 'and effective_start_date <= to_date('''||TO_CHAR(g_eff_end_date,'dd/mm/yyyy')||''',''dd/mm/yyyy'') '||' '||
35 'and effective_end_date >= to_date('''||TO_CHAR(g_eff_start_date,'dd/mm/yyyy')||''',''dd/mm/yyyy'')'||' '||
36 'and '||g_asg_membership_col||' '||'IS NOT NULL'||' ';
37
38 IF g_asg_membership_context <> 'Global Data Elements' THEN
39 l_query := l_query||
40 'and ASS_ATTRIBUTE_CATEGORY = '''||g_asg_membership_context||''''||' ';
41 END IF;
42
43 OPEN cur_get_value FOR l_query;
44 FETCH cur_get_value INTO l_value;
45 CLOSE cur_get_value;
46
47 RETURN l_value;
48
49 END;
50
51 -- This procedure is used to generate service period numbers.
52 -- ----------------------------------------------------------------------------
53 -- |-------------------------< derive_svpn >--------------------------|
54 -- ----------------------------------------------------------------------------
55 PROCEDURE derive_svpn( errbuf OUT NOCOPY VARCHAR2,
56 retcode OUT NOCOPY VARCHAR2,
57 p_business_group_id IN NUMBER,
58 p_eff_end_date IN VARCHAR2,
59 p_execution_mode IN VARCHAR2)
60 IS
61
62 -- Cursor to fetch all assignments
63
64 CURSOR csr_fetch_all_assignments (c_eff_end_date IN Date)
65 IS
66 SELECT DISTINCT papf.national_identifier,papf.person_id,paaf.assignment_id,
67 ppos.period_of_service_id,papf.employee_number,paaf.assignment_number
68 FROM per_all_people_f papf,
69 per_all_assignments_f paaf,
70 per_periods_of_service ppos
71 WHERE papf.business_group_id = g_business_group_id
72 AND papf.effective_start_date <= c_eff_end_date
73 AND papf.effective_end_date >= g_eff_start_date
74 AND papf.person_id = ppos.person_id
75 AND NVL(ppos.final_process_date,g_eff_start_date ) >= g_eff_start_date
76 AND ppos.period_of_service_id = paaf.period_of_service_id
77 AND paaf.PRIMARY_FLAG = 'Y'
78 AND paaf.business_group_id = g_business_group_id
79 AND paaf.effective_start_date <= c_eff_end_date
80 AND paaf.effective_end_date >= g_eff_start_date
81 AND paaf.effective_start_date = PQP_GB_PENSRV_SVPN.chk_emp_eligibility(paaf.assignment_id)
82 ORDER BY papf.national_identifier,ppos.period_of_service_id;
83
84 -- Cursor to fetch Maintain Service period Number flag
85
86 CURSOR csr_svpn_maintain_flag
87 IS
88 SELECT pcv_information4
89 FROM pqp_configuration_values pcv,
90 pqp_configuration_types pct
91 WHERE pcv.pcv_information_category = pct.configuration_type
92 AND pct.configuration_type = 'PQP_GB_PENSERVER_PAYPOINT_INFO'
93 AND pcv.business_group_id = g_business_group_id;
94
95 -- Cursor to fetch pension scheme eligibility configuration values
96
97 CURSOR csr_pensrv_eligibility
98 IS
99 SELECT pcv_information1,pcv_information2
100 FROM pqp_configuration_values pcv,
101 pqp_configuration_types pct
102 WHERE pcv.pcv_information_category = pct.configuration_type
103 AND pct.configuration_type = 'PQP_GB_PENSERVER_ELIGBLTY_CONF'
104 AND pcv.business_group_id = g_business_group_id;
105
106 -- Cursor to fetch EIT information for Penserver Service Period Number
107
108 CURSOR cur_get_eit_info(c_assignment_id NUMBER)
109 IS
110 SELECT aei_information_category
111 FROM per_assignment_Extra_info
112 WHERE assignment_id = c_assignment_id
113 AND aei_information_category = 'PQP_GB_PENSERV_SVPN';
114
115 -- Local variables
116
117 l_procedure_name VARCHAR2(100);
118 l_old_NI_number VARCHAR2(100);
119 l_new_NI_number VARCHAR2(100);
120 l_spn NUMBER;
121 l_assignment_extra_info_id NUMBER;
122 l_aei_information_category VARCHAR2(100);
123 l_object_version_number NUMBER;
124 l_eff_start_date DATE;
125 l_eff_end_date DATE;
126 l_execution_mode BOOLEAN;
127
128 l_tp_ni_increment NUMBER :=1;
129
130 TYPE character_data_table IS TABLE OF VARCHAR2(280)
131 INDEX BY BINARY_INTEGER;
132 l_ni_number character_data_table;
133 l_person_number character_data_table;
134 l_assignment_number character_data_table;
135 l_spn_number character_data_table;
136
137 BEGIN
138
139 l_procedure_name := '.derive_svpn';
140
141 hr_utility.set_location('Entering ' || g_package_name || l_procedure_name,0);
142
143 fnd_file.put_line(fnd_file.log,'Entering ' || g_package_name || l_procedure_name);
144 --fnd_file.put_line(fnd_file.log,'p_business_group_id '||p_business_group_id);
145 --fnd_file.put_line(fnd_file.log,'p_eff_end_date '||p_eff_end_date);
146 --fnd_file.put_line(fnd_file.log,'p_execution_mode '||p_execution_mode);
147
148 g_business_group_id := p_business_group_id;
149 g_eff_end_date := fnd_date.canonical_to_date(p_eff_end_date);
150
151 IF g_eff_end_date < g_eff_start_date THEN
152 fnd_file.put_line(fnd_file.log,'Effective End Date should be greater than or equal to 31-JUL-2007.');
153 ELSE
154 OPEN csr_svpn_maintain_flag;
155 FETCH csr_svpn_maintain_flag INTO g_svpn_flag;
156 CLOSE csr_svpn_maintain_flag;
157
158 hr_utility.set_location('g_svpn_flag'||g_svpn_flag,10);
159
160 -- Checking whether Maintain Service period flag is enabled or not.
161
162 IF g_svpn_flag = 'Yes' THEN
163
164 fnd_file.put_line(fnd_file.log,'Maintain Service Period Number flag is set to Yes for the configuration PAYPOINT and CUTOVER INFORMATION.
165 Concurrent Process will be executed to set the SPN only when the flag is not set to Yes.');
166 ELSE
167
168 OPEN csr_pensrv_eligibility;
169 FETCH csr_pensrv_eligibility INTO g_asg_membership_context,
170 g_asg_membership_col;
171 CLOSE csr_pensrv_eligibility;
172
173 --fnd_file.put_line(fnd_file.log,'g_asg_membership_context'||g_asg_membership_context);
174 --fnd_file.put_line(fnd_file.log,'g_asg_membership_col '||g_asg_membership_col);
175
176 -- Checking whether Pension Scheme Eligibility Configuration is configured or not
177
178 IF g_asg_membership_context IS NULL
179 OR
180 g_asg_membership_col IS NULL
181 THEN
182 fnd_file.put_line(fnd_file.log,'Assignment Flexfield Context and Assignment Flexfield Column Name is
183 Not configured under Pension Scheme Eligibilty Configuration for Penserver Interfaces.');
184 END IF;
185 END IF;
186
187 -- IF Pension Scheme Eligibility Configuration is configured successfully the proceed
188
189 IF g_asg_membership_context IS NOT NULL AND g_asg_membership_col IS NOT NULL
190 THEN
191 l_spn := 1;
192
193 --fnd_file.put_line(fnd_file.log,' g_eff_end_date '|| g_eff_end_date );
194
195 -- Service Period Number details writing into the out put file
196
197 fnd_file.put_line(FND_FILE.OUTPUT,'------------------------------------------------------------------------------------------------------------------------------------');
198 fnd_file.put_line(FND_FILE.OUTPUT,' Service Period Number details for employees in Business group: '||rpad(g_business_group_id,30));
199 fnd_file.put_line(FND_FILE.OUTPUT,' report date : '|| sysdate);
200 fnd_file.put_line(FND_FILE.OUTPUT,'------------------------------------------------------------------------------------------------------------------------------------');
201 fnd_file.put_line(FND_FILE.OUTPUT,' ');
202 fnd_file.put_line(FND_FILE.OUTPUT, rpad('National Identifier',20)||' '||rpad('Person Number',20)||' '||rpad('Assignment Number',20)||' '||rpad('Service Period Number',22));
203 fnd_file.put_line(FND_FILE.OUTPUT,'------------------------------------------------------------------------------------------------------------------------------------');
204
205 -- For each assignment checking whether the assignment is eligible for genertion of service period number
206 -- and generting SPN for valid assignments
207
208 FOR l_query_re IN csr_fetch_all_assignments (g_eff_end_date) LOOP
209
210 l_new_NI_number := l_query_re.national_identifier;
211 IF l_old_NI_number = l_new_NI_number THEN
212 l_spn := l_spn + 1;
213 ELSIF l_old_NI_number <> l_new_NI_number OR l_old_NI_number IS NULL THEN
214 l_spn := 1;
215 END IF;
216
217 l_old_NI_number := l_new_NI_number;
218
219 l_aei_information_category := null;
220
221 OPEN cur_get_eit_info(l_query_re.assignment_id);
222 FETCH cur_get_eit_info INTO l_aei_information_category;
223 CLOSE cur_get_eit_info;
224
225 -- Checking whether EIT for this assignment exists or not
226
227 IF l_aei_information_category IS NULL THEN
228
229 IF l_query_re.national_identifier IS NOT NULL
230 AND UPPER(SUBSTR(l_query_re.national_identifier,1,2)) <> 'TN' THEN
231 fnd_file.put_line(FND_FILE.OUTPUT,rpad(l_query_re.national_identifier,20)||' '||rpad(l_query_re.employee_number,20)||' '||
232 rpad(l_query_re.assignment_number,20)||' '||rpad(lpad(l_spn,2,'0'),20));
233 ELSE
234 l_spn := NULL;
235 IF l_query_re.national_identifier IS NULL THEN
236 l_query_re.national_identifier := 'No NI Number';
237 END IF;
238 l_ni_number(l_tp_ni_increment) := l_query_re.national_identifier;
239 l_person_number(l_tp_ni_increment) := l_query_re.employee_number;
240 l_assignment_number(l_tp_ni_increment) := l_query_re.assignment_number;
241 l_spn_number(l_tp_ni_increment) := l_spn;
242 l_tp_ni_increment := l_tp_ni_increment + 1;
243 END IF;
244
245 -- Checking whether the execution mode is commit or not
246 IF p_execution_mode = 'Commit' THEN
247 l_execution_mode := FALSE;
248 ELSE
249 l_execution_mode := TRUE;
250 END IF; -- end of p_execution_mode
251
252 IF l_spn IS NOT NULL THEN
253 -- inserting service period number
254 HR_ASSIGNMENT_EXTRA_INFO_API.create_assignment_extra_info(p_validate => l_execution_mode
255 ,p_assignment_id => l_query_re.assignment_id
256 ,p_information_type => 'PQP_GB_PENSERV_SVPN'
257 ,p_aei_information_category => 'PQP_GB_PENSERV_SVPN'
258 ,p_aei_information1 => lpad(l_spn,2,'0')
259 ,p_assignment_extra_info_id => l_assignment_extra_info_id
260 ,p_object_version_number => l_object_version_number);
261 END IF; -- end of l_spn
262 END IF; -- end of l_aei_information_category
263 END LOOP;
264
265 -- writing warning heading in to the out put file
266 fnd_file.put_line(FND_FILE.OUTPUT,rpad(' ',20));
267 fnd_file.put_line(FND_FILE.OUTPUT,'Warnings: SPN is not set for the following employees');
268 fnd_file.put_line(FND_FILE.OUTPUT,rpad('---------',20));
269
270 FOR i in 1 .. l_tp_ni_increment-1 LOOP
271 fnd_file.put_line(FND_FILE.OUTPUT,rpad(l_ni_number(i),20)||' '||rpad(l_person_number(i),20)||' '||
272 rpad(l_assignment_number(i),20)||' '||rpad(lpad(l_spn_number(i),2,'0'),20));
273 END LOOP;
274 END IF;
275 END IF; -- end for checking whether start date is greater than end date
276 fnd_file.put_line(fnd_file.log,'Leaving ' || g_package_name || l_procedure_name);
277
278 EXCEPTION
279 WHEN others
280 THEN
281 fnd_file.put_line(fnd_file.log,g_package_name || l_procedure_name);
282 fnd_file.put_line(fnd_file.log,'ERROR:' || sqlcode ||'-'|| substr(sqlerrm,1,80));
283
284 RAISE;
285 END derive_svpn;
286
287 BEGIN
288 g_package_name := 'pqp_gb_pensrv_svpn';
289 END pqp_gb_pensrv_svpn;
290
[Home] [Help]