1 PACKAGE BODY PER_GB_PENSRV_SVPN AS
2 /* $Header: pegbasgp.pkb 120.2.12010000.2 2008/08/28 12:08:45 skpatil ship $ */
3
4 --Globals
5 gv_package_name VARCHAR2(100);
6 g_svpn_flag VARCHAR2(4);
7
8 -- This procedure is used to calculate service period numbers.
9 -- ----------------------------------------------------------------------------
10 -- |-------------------------< derive_svpn >--------------------------|
11 -- ----------------------------------------------------------------------------
12
13 PROCEDURE create_gb_spn(p_assignment_id IN NUMBER,
14 p_effective_date IN DATE)
15
16 IS
17 -- local variables
18
19 l_procedure_name VARCHAR2(100);
20 l_ass_attribute_category VARCHAR2(300);
21 l_column_name VARCHAR2(30);
22 l_pvc_information2 VARCHAR2(30);
23 l_bg_id NUMBER;
24 l_query VARCHAR2(1000);
25 l_value VARCHAR2(30);
26 l_spn NUMBER;
27 l_assignment_extra_info_id NUMBER;
28 l_object_version_number NUMBER;
29 g_assignment_id NUMBER;
30 g_effective_date DATE;
31 l_ni_number VARCHAR2(30);
32 l_pri_flag VARCHAR2(4);
33
34
35 TYPE base_table_ref_csr_typ IS REF CURSOR;
36 c_base_table base_table_ref_csr_typ;
37
38 -- Cursor to fetch the business_group_id
39
40 CURSOR get_bg_id(c_assignment_id NUMBER,
41 c_effective_date DATE)
42 IS
43 SELECT business_group_id
44 FROM per_all_assignments_f
45 WHERE assignment_id = c_assignment_id
46 AND c_effective_date BETWEEN effective_start_date AND effective_end_date;
47
48
49 -- Cursor to fetch the columns name agaist which the Pension scheme is configured
50
51 CURSOR get_penserv_config(c_business_group_id NUMBER)
52 IS
53 SELECT pcv_information1,pcv_information2
54 FROM pqp_configuration_values pcv,
55 pqp_configuration_types pct
56 WHERE pcv.pcv_information_category = pct.configuration_type
57 AND pct.configuration_type = 'PQP_GB_PENSERVER_ELIGBLTY_CONF'
58 AND pcv.business_group_id = c_business_group_id;
59
60 -- Cursor to check if emp is elegible for spn calculation
61 -- if SPN already exist, no calculation required*/
62
63 CURSOR cur_get_eit_info(c_assignment_id NUMBER)
64 IS
65 SELECT aei_information1
66 FROM per_assignment_Extra_info
67 WHERE assignment_id = c_assignment_id
68 AND aei_information_category = 'PQP_GB_PENSERV_SVPN';
69
70 -- Cursor to fetch maximum service period number
71
72 CURSOR cur_get_svpn(c_ni_number VARCHAR2)
73 IS
74 SELECT max(to_number(paei.aei_information1))+1
75 FROM per_all_people_f papf,
76 per_all_assignments_f paaf,
77 per_assignment_extra_info paei
78 WHERE papf.NATIONAL_IDENTIFIER = c_ni_number
79 AND papf.person_id = paaf.person_id
80 AND paei.assignment_id = paaf.assignment_id
81 AND paei.aei_information_category = 'PQP_GB_PENSERV_SVPN';
82
83 -- Cursor to fetch NI number for the give assignment
84 CURSOR cur_get_NI_number(c_assignment_id NUMBER,
85 c_effective_date DATE)
86 IS
87 SELECT papf1.NATIONAL_IDENTIFIER
88 FROM per_all_people_f papf1,
89 per_all_assignments_f paaf1
90 WHERE paaf1.assignment_id = c_assignment_id
91 AND c_effective_date BETWEEN paaf1.effective_start_date AND paaf1.effective_end_date
92 AND papf1.person_id = paaf1.person_id
93 AND c_effective_date BETWEEN papf1.effective_start_date AND papf1.effective_end_date;
94
95 -- Cursor to fetch Matain service period number flag
96
97 CURSOR csr_svpn_maintain_flag(c_business_group_id NUMBER)
98 IS
99 SELECT pcv_information4
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_PAYPOINT_INFO'
104 AND pcv.business_group_id = c_business_group_id;
105
106 -- Cursor to fetch assignment details (Primary or not)
107 CURSOR csr_get_asg_detials(c_assignment_id NUMBER,
108 c_effective_date DATE)
109 IS
110 SELECT paaf.primary_flag
111 FROM per_all_assignments_f paaf
112 WHERE paaf.assignment_id = c_assignment_id
113 AND c_effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date;
114
115 BEGIN
116
117 l_procedure_name := '.create_gb_spn';
118 g_assignment_id := p_assignment_id;
119 g_effective_date := p_effective_date;
120
121 hr_utility.set_location('attrbute_category is :'||l_ass_attribute_category, 10);
122 hr_utility.set_location('g_assignment_id is :'||g_assignment_id, 20);
123 hr_utility.set_location('g_effective_date is :'||g_effective_date, 30);
124
125 -- fetching primary assignment details
126 OPEN csr_get_asg_detials(g_assignment_id,g_effective_date);
127 FETCH csr_get_asg_detials INTO l_pri_flag;
128 CLOSE csr_get_asg_detials;
129
130
131
132 -- if the assignment is primary assignment then proceed
133 IF l_pri_flag = 'Y' THEN
134 -- fetching business groupid
135 OPEN get_bg_id(g_assignment_id,g_effective_date);
136 FETCH get_bg_id INTO l_bg_id;
137 CLOSE get_bg_id;
138
139 /* Start Bug 7132727 */
140 OPEN get_penserv_config(l_bg_id);
141 FETCH get_penserv_config INTO l_ass_attribute_category,
142 l_column_name;
143 CLOSE get_penserv_config;
144
145 IF l_ass_attribute_category is not null
146 then
147
148 /* End Bug 7132727 */
149
150 -- fetching Maintaion service period flag
151 OPEN csr_svpn_maintain_flag(l_bg_id);
152 FETCH csr_svpn_maintain_flag INTO g_svpn_flag;
153 CLOSE csr_svpn_maintain_flag;
154
155 hr_utility.set_location(' l_bg_id :'||l_bg_id,40);
156 hr_utility.set_location('g_svpn_flag'||g_svpn_flag,45);
157
158 IF g_svpn_flag = 'Yes' THEN
159
160
161 -- Query to fetch configured value for the assignment.
162 l_query := 'select '||l_column_name||' '||
163 'from per_all_assignments_f'||' '||
164 'where business_group_id = '||l_bg_id||' '||
165 'and assignment_id = '||g_assignment_id||' ';
166 IF l_ass_attribute_category <> 'Global Data Elements' THEN
167 l_query := l_query||
168 'and ASS_ATTRIBUTE_CATEGORY = '''||l_ass_attribute_category||''''||' ';
169 END IF;
170
171 l_query := l_query||
172 'and to_date('''||TO_CHAR(g_effective_date,'dd/mm/yyyy')||''',''dd/mm/yyyy'') between effective_start_date'||' '||
173 'and effective_end_date';
174
175 hr_utility.set_location('l_query: '||l_query,50);
176
177 -- fetch the value of from the column name
178 OPEN c_base_table FOR l_query;
179 FETCH c_base_table INTO l_value;
180 CLOSE c_base_table;
181
182 IF l_value IS NULL THEN
183 hr_utility.set_location('value is NULL :', 60);
184 hr_utility.set_location(' l_value'||l_value,70);
185 NULL;
186 ELSE
187 hr_utility.set_location('attribute value is not NULL :', 80);
188 hr_utility.set_location(' l_value'||l_value,90);
189
190 -- fetch EIT information
191 OPEN cur_get_eit_info(c_assignment_id => g_assignment_id);
192 FETCH cur_get_eit_info INTO l_spn;
193 CLOSE cur_get_eit_info;
194
195 hr_utility.set_location(' spn value :'||l_spn, 100);
196
197 -- Check whether service period number is null or not
198
199 IF l_spn IS NULL THEN
200 OPEN cur_get_NI_number(g_assignment_id,g_effective_date);
201 FETCH cur_get_NI_number INTO l_ni_number;
202 CLOSE cur_get_NI_number;
203
204 -- Checking whether NI number is null or not
205 IF l_ni_number IS NOT NULL
206 AND UPPER(SUBSTR(l_ni_number,1,2)) <> 'TN' THEN
207
208 OPEN cur_get_svpn(l_ni_number);
209 FETCH cur_get_svpn INTO l_spn;
210 CLOSE cur_get_svpn;
211 hr_utility.set_location(' l_spn value :'||l_spn, 105);
212 IF l_spn IS null THEN
213 l_spn := 1;
214 END IF;
215 -- Inserting the service period number
216 HR_ASSIGNMENT_EXTRA_INFO_API.create_assignment_extra_info(p_assignment_id => g_assignment_id
217 ,p_information_type => 'PQP_GB_PENSERV_SVPN'
218 ,p_aei_information_category => 'PQP_GB_PENSERV_SVPN'
219 ,p_aei_information1 => lpad(l_spn,2,'0')
220 ,p_assignment_extra_info_id => l_assignment_extra_info_id
221 ,p_object_version_number => l_object_version_number);
222 hr_utility.set_location(' inserted successfully :'||l_spn, 100);
223 END IF;
224 ELSE
225 hr_utility.set_location(' Spn calculation not required :',110);
226 END IF;
227 END IF;
228 ELSE
229 fnd_file.put_line(fnd_file.log,'Maitain Service Period No. is not set to "Yes" for the configuration PAYPOINT and CUTOVER INFORMATION
230 and hence service period number will not be populated for this assignment');
231 END IF;
232 /* Start Bug 7132727 */
233 else
234 NULL;
235 end if;
236 /* End Bug 7132727 */
237 END IF;
238 fnd_file.put_line(fnd_file.log,'Leaving ' || gv_package_name || l_procedure_name);
239
240 END create_gb_spn;
241
242 BEGIN
243 gv_package_name := 'per_gb_pensrv_svpn';
244 END per_gb_pensrv_svpn;
245
[Home] [Help]