[Home] [Help]
PACKAGE BODY: APPS.CREATE_GB_RTI_EPS_RECORD_13
Source
1 PACKAGE Body Create_GB_RTI_EPS_RECORD_13
2 /* $Header: pygbrtieps13.pkb 120.3.12020000.3 2013/01/03 07:29:56 rajganga noship $ */
3 AS
4
5 g_package VARCHAR2(50) := 'Create_GB_RTI_EPS_RECORD_13';
6 L_EFF_START_DATE DATE;
7 G_SMP_SAP_RATE NUMBER :=0;
8 G_SSP_RATE NUMBER :=0;
9 G_NIC_RATE NUMBER :=0;
10 g_paye_reference VARCHAR2(50);
11 G_BUSINESS_GROUP_ID NUMBER;
12 g_related_tax_year VARCHAR2(4);
13 g_employers_name VARCHAR2(35);
14 g_econ_number VARCHAR2(35);
15 g_acc_ref_no VARCHAR2(35);
16 g_service_provider VARCHAR2(1);
17 G_SMP_RECOVERY VARCHAR2(15) := 'Partial';
18 G_SSP_RECOVERY VARCHAR2(15) := 'No';
19 G_APPROVER_ID NUMBER;
20 G_user_ID NUMBER;
21 GLOBAL_MISSING EXCEPTION;
22 MANDATORY_VALUES_MISSING EXCEPTION;
23 EPS_ALREADY_PRESENT EXCEPTION;
24 g_ssp_id pay_defined_balances.defined_balance_id%TYPE;
25 g_smp_id pay_defined_balances.defined_balance_id%TYPE;
26 g_sap_id pay_defined_balances.defined_balance_id%TYPE;
27 g_spp_adopt_id pay_defined_balances.defined_balance_id%TYPE;
28 g_spp_birth_id pay_defined_balances.defined_balance_id%TYPE;
29 g_aspp_adopt_id pay_defined_balances.defined_balance_id%TYPE;
30 G_ASPP_BIRTH_ID PAY_DEFINED_BALANCES.DEFINED_BALANCE_ID%TYPE;
31
32
33 g_nic_smp_id pay_defined_balances.defined_balance_id%TYPE;
34 g_nic_sap_id pay_defined_balances.defined_balance_id%TYPE;
35 g_nic_spp_adopt_id pay_defined_balances.defined_balance_id%TYPE;
36 g_nic_spp_birth_id pay_defined_balances.defined_balance_id%TYPE;
37 g_nic_aspp_adopt_id pay_defined_balances.defined_balance_id%TYPE;
38 G_NIC_ASPP_BIRTH_ID PAY_DEFINED_BALANCES.DEFINED_BALANCE_ID%TYPE;
39
40
41 PROCEDURE get_globals
42 IS
43 --This is to fetch details from Organisation Details.
44 CURSOR c_org_details
45 IS
46 SELECT NVL(upper(SUBSTR(trim(hoi.org_information3),1,35)),' ') l_EMPLOYERS_NAME,
47 NVL(upper(SUBSTR(trim(hoi.org_information7),1,35)),' ') l_econ_number,
48 NVL(upper(SUBSTR(hoi.org_information6,1,35)),' ') l_acc_ref_no,
49 NVL(substr(TRIM(HOI.ORG_INFORMATION19),1,1),'N') L_SERVICE_PROVIDER,
50 NVL(TRIM(HOI.ORG_INFORMATION16),'Partial') L_SMP_RECOVERY,
51 NVL(TRIM(HOI.ORG_INFORMATION17),'None') L_SSP_RECOVERY,
52 TRIM(HOI.ORG_INFORMATION18) L_APPROVER_ID
53 FROM hr_organization_information hoi
54 WHERE hoi.organization_id = g_business_group_id
55 AND hoi.org_information_context = 'Tax Details References'
56 AND NVL(HOI.ORG_INFORMATION10,'UK') = 'UK'
57 AND hoi.org_information1 = g_paye_reference;
58
59 CURSOR c_globals(p_global_name1 VARCHAR2,p_global_name2 VARCHAR2,p_global_name3 VARCHAR2)
60 IS
61 SELECT MAX(DECODE(GLOBAL_NAME,P_GLOBAL_NAME1,GLOBAL_VALUE,NULL)) L_SMP_SAP_RATE,
62 MAX(DECODE(GLOBAL_NAME,p_global_name2,GLOBAL_VALUE,NULL)) l_ssp_rate,
63 MAX(DECODE(GLOBAL_NAME,p_global_name3,GLOBAL_VALUE,NULL)) l_nic_rate
64 FROM FF_GLOBALS_F
65 WHERE GLOBAL_NAME IN (p_global_name1, p_global_name2,p_global_name3)
66 AND L_EFF_START_DATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
67
68 -- get the defined balance id for specified balance and dimension
69 CURSOR get_defined_balance_id (p_balance_name VARCHAR2, p_dimension_name VARCHAR2)
70 IS
71 SELECT defined_balance_id
72 FROM pay_defined_balances db,
73 pay_balance_types b,
74 pay_balance_dimensions d
75 WHERE b.balance_name = p_balance_name
76 AND d.dimension_name = p_dimension_name
77 AND db.balance_type_id = b.balance_type_id
78 AND db.balance_dimension_id = d.balance_dimension_id;
79 --
80 BEGIN
81 HR_UTILITY.TRACE(' Entered Fetch Globals ');
82 OPEN c_org_details;
83 FETCH c_org_details
84 INTO g_employers_name,
85 g_econ_number,
86 g_acc_ref_no,
87 g_service_provider,
88 g_smp_recovery,
89 G_SSP_RECOVERY,
90 G_APPROVER_ID;
91 CLOSE c_org_details;
92
93
94 HR_UTILITY.TRACE(' Employer Name '||G_EMPLOYERS_NAME);
95 HR_UTILITY.TRACE(' Econ Number '||G_ECON_NUMBER);
96 HR_UTILITY.TRACE(' Account Office Number '||G_ACC_REF_NO);
97 HR_UTILITY.TRACE(' Service provider '||G_SERVICE_PROVIDER);
98 HR_UTILITY.TRACE(' SMP Recovery '||G_SMP_RECOVERY);
99 HR_UTILITY.TRACE(' SSP RECOVERY '||G_SSP_RECOVERY);
100 HR_UTILITY.TRACE(' APPROVER ID '||G_APPROVER_ID);
101 --13955276 Commented to handle validation in the create method
102 --13741983 begin
103 /*
104 if g_employers_name = ' ' or g_employers_name is null then
105 HR_UTILITY.TRACE('Error: The Employer name is missing.');
106 fnd_file.put_line(fnd_file.LOG,'Error: The Employer name is missing.');
107 RAISE MANDATORY_VALUES_MISSING;
108 elsif PAY_GB_RTI.validate_input(g_employers_name,'EMP_NAME') > 0 then
109 HR_UTILITY.TRACE('Error: The Employer name has invalid characters.');
110 fnd_file.put_line(fnd_file.LOG,'Error: The Employer name has invalid characters.');
111 RAISE MANDATORY_VALUES_MISSING;
112 end if;
113
114 if g_acc_ref_no = ' ' or g_acc_ref_no is null then
115 HR_UTILITY.TRACE('Error: The Account Office Reference Number is missing.');
116 fnd_file.put_line(fnd_file.LOG,'Error: The Account Office Reference Number is missing.');
117 RAISE MANDATORY_VALUES_MISSING;
118 elsif (length(g_acc_ref_no) > 13
119 OR REGEXP_INSTR(g_acc_ref_no,'^([[:digit:]]{1,3})P([[:alpha:]]{1})([[:digit:]]{1,7})([[:digit:]]|X)$') = 0 ) THEN
120 HR_UTILITY.TRACE('Error: Incorrect format entered for Accounts Office Ref Num field. Valid format is NNNPANNNNNNNX.');
121 fnd_file.put_line(fnd_file.LOG,'Error: Incorrect format entered for Accounts Office Ref Num field. Valid format is NNNPANNNNNNNX.');
122 RAISE MANDATORY_VALUES_MISSING;
123 end if;
124 --13741983 End
125 */
126
127 OPEN C_GLOBALS('SMP_RECOVERY_PERCENTAGE','SSP_RECOVERY_PERCENTAGE', 'NIC_COMPENSATION_RATE_ON_SMP');
128 FETCH C_GLOBALS INTO G_SMP_SAP_RATE,G_SSP_RATE, G_NIC_RATE;
129 IF C_GLOBALS%NOTFOUND THEN
130 CLOSE c_globals;
131 RAISE GLOBAL_MISSING;
132 END IF;
133 CLOSE C_GLOBALS;
134
135 IF G_SMP_RECOVERY = 'Full' THEN
136 G_SMP_SAP_RATE := 1;
137 ELSIF G_SMP_RECOVERY = 'None' THEN
138 G_SMP_SAP_RATE := 0;
139 END IF;
140
141 IF G_SSP_RECOVERY = 'None' THEN
142 G_SSP_RATE := 0;
143 END IF;
144
145 HR_UTILITY.TRACE(' SMP_RECOVERY_PERCENTAGE '||G_SMP_SAP_RATE);
146 HR_UTILITY.TRACE(' SSP_RECOVERY_PERCENTAGE '||G_SSP_RATE);
147 HR_UTILITY.TRACE(' NIC_COMPENSATION_RATE_ON_SMP '||G_NIC_RATE);
148
149 /*
150 --
151 OPEN get_defined_balance_id('SSP Total','_ASG_TD_YTD');
152 FETCH get_defined_balance_id INTO g_ssp_id;
153 CLOSE get_defined_balance_id;
154 --
155 OPEN get_defined_balance_id('SMP Total','_ASG_TD_YTD');
156 FETCH get_defined_balance_id INTO g_smp_id;
157 CLOSE get_defined_balance_id;
158 --
159 OPEN get_defined_balance_id('SAP Total','_ASG_TD_YTD');
160 FETCH get_defined_balance_id INTO g_sap_id;
161 CLOSE get_defined_balance_id;
162 --
163 OPEN get_defined_balance_id('SPP Adoption Total','_ASG_TD_YTD');
164 FETCH get_defined_balance_id INTO g_spp_adopt_id;
165 CLOSE get_defined_balance_id;
166 --
167 OPEN get_defined_balance_id('SPP Birth Total','_ASG_TD_YTD');
168 FETCH get_defined_balance_id INTO g_spp_birth_id;
169 CLOSE get_defined_balance_id;
170 --
171 OPEN get_defined_balance_id('ASPP Adoption Total','_ASG_TD_YTD');
172 FETCH get_defined_balance_id INTO g_aspp_adopt_id;
173 CLOSE get_defined_balance_id;
174 --
175 OPEN get_defined_balance_id('ASPP Birth Total','_ASG_TD_YTD');
176 FETCH get_defined_balance_id INTO g_aspp_birth_id;
177 CLOSE get_defined_balance_id;
178 */
179
180 OPEN get_defined_balance_id('SMP Recovery Amount','_ASG_TD_YTD');
181 FETCH get_defined_balance_id INTO g_smp_id;
182 CLOSE get_defined_balance_id;
183 --
184 OPEN get_defined_balance_id('SAP Recovery Amount','_ASG_TD_YTD');
185 FETCH get_defined_balance_id INTO g_sap_id;
186 CLOSE get_defined_balance_id;
187 --
188 OPEN get_defined_balance_id('SPP Adoption Recovery Amount','_ASG_TD_YTD');
189 FETCH get_defined_balance_id INTO g_spp_adopt_id;
190 CLOSE get_defined_balance_id;
191 --
192 OPEN get_defined_balance_id('SPP Birth Recovery Amount','_ASG_TD_YTD');
193 FETCH get_defined_balance_id INTO g_spp_birth_id;
194 CLOSE get_defined_balance_id;
195 --
196 OPEN get_defined_balance_id('ASPP Adoption Recovery Amount','_ASG_TD_YTD');
197 FETCH get_defined_balance_id INTO g_aspp_adopt_id;
198 CLOSE get_defined_balance_id;
199 --
200 OPEN get_defined_balance_id('ASPP Birth Recovery Amount','_ASG_TD_YTD');
201 FETCH get_defined_balance_id INTO g_aspp_birth_id;
202 CLOSE get_defined_balance_id;
203 ---------------
204
205 OPEN get_defined_balance_id('SMP NIC Compensation','_ASG_TD_YTD');
206 FETCH get_defined_balance_id INTO g_nic_smp_id;
207 CLOSE get_defined_balance_id;
208 --
209 OPEN get_defined_balance_id('SAP NIC Compensation','_ASG_TD_YTD');
210 FETCH get_defined_balance_id INTO g_nic_sap_id;
211 CLOSE get_defined_balance_id;
212 --
213 OPEN get_defined_balance_id('SPP Adoption NIC Compensation','_ASG_TD_YTD');
214 FETCH get_defined_balance_id INTO g_nic_spp_adopt_id;
215 CLOSE get_defined_balance_id;
216 --
217 OPEN get_defined_balance_id('SPP Birth NIC Compensation','_ASG_TD_YTD');
218 FETCH get_defined_balance_id INTO g_nic_spp_birth_id;
219 CLOSE get_defined_balance_id;
220 --
221 OPEN get_defined_balance_id('ASPP Adoption NIC Compensation','_ASG_TD_YTD');
222 FETCH get_defined_balance_id INTO g_nic_aspp_adopt_id;
223 CLOSE get_defined_balance_id;
224 --
225 OPEN get_defined_balance_id('ASPP Birth NIC Compensation','_ASG_TD_YTD');
226 FETCH get_defined_balance_id INTO g_nic_aspp_birth_id;
227 CLOSE get_defined_balance_id;
228 --
229 END get_globals;
230
231 PROCEDURE Create_EPS_RECORD(
232 errbuf OUT nocopy VARCHAR2,
233 RETCODE OUT NOCOPY NUMBER,
234 p_paye_reference IN VARCHAR2,
235 p_effective_dt IN VARCHAR2,
236 p_business_group_id IN NUMBER,
237 p_final_submission IN VARCHAR2)
238 IS
239 --
240 l_date_soy DATE;
241 l_date_eoy DATE;
242 l_dummy VARCHAR2(1);
243 l_calculate_ssp BOOLEAN := TRUE;
244 l_calculate_smp BOOLEAN := TRUE;
245 l_nic_ssp_rate NUMBER;
246 l_nic_smp_rate NUMBER;
247 l_smp NUMBER(15) :=0;
248 l_ssp NUMBER(15) :=0;
249 l_sap NUMBER(15) :=0;
250 l_spp_adopt NUMBER(15) :=0;
251 l_spp_birth NUMBER(15) :=0;
252 l_spp NUMBER(15) :=0;
253 l_aspp_adopt NUMBER(15) :=0;
254 l_aspp_birth NUMBER(15) :=0;
255 l_aspp NUMBER(15) :=0;
256 l_nic_ssp NUMBER(15) := 0;
257 l_nic_smp NUMBER(15) := 0;
258 l_nic_sap NUMBER(15) := 0;
259 l_nic_spp NUMBER(15) := 0;
260 l_nic_aspp NUMBER(15) := 0;
261
262
263
264
265
266
267 l_nic_spp_adopt NUMBER(15) :=0;
268 l_nic_spp_birth NUMBER(15) :=0;
269 l_nic_aspp_adopt NUMBER(15) :=0;
270 l_nic_aspp_birth NUMBER(15) :=0;
271 L_MSG_OUTPUT VARCHAR2(200);
272 L_CREATER_ID NUMBER;
273 l_final_submission_ceased varchar2(1) := 'N';
274 l_final_submission_year varchar2(1) := 'N';
275
276 --Check if already EPS record exists for PAYE reference and Effective date combination
277 CURSOR c_check_eps
278 IS
279 SELECT 1
280 FROM PAY_GB_EPS_DETAILS
281 WHERE EFFECTIVE_DATE = l_eff_start_date
282 AND EMP_PAYE_REFERENCE = p_paye_reference
283 AND BUSINESS_GROUP_ID = p_business_group_id;
284
285 --To fetch the assignment actions in the current financial year for all assignments
286 --for the selected payrolls in the given PAYE reference
287 CURSOR c_lat_assignment_actions
288 IS
289 SELECT
290 /*+ USE_NL(paa, pact, ptp) */
291 to_number(SUBSTR(MAX(lpad(paa.action_sequence,15,'0')
292 || paa.assignment_action_id),16)) lat_assignment_action_id,
293 MAX(pact.effective_date) effective_date,
294 paa.assignment_id
295 FROM pay_assignment_actions paa,
296 pay_payroll_actions pact
297 WHERE paa.payroll_action_id = pact.payroll_action_id
298 AND pact.action_type IN ('Q','R','B','I','V')
299 AND paa.action_status IN ('C','S')
300 AND pact.effective_date BETWEEN l_date_soy AND l_eff_start_date
301 AND pact.payroll_id IN
302 (
303 -- Effective date, Business_group_id, PAYE reference, tax_year_start and Tax_year_end
304 -- To fetch all payrolls belonging to this PAYE reference.
305 SELECT DISTINCT papf.payroll_id
306 FROM pay_all_payrolls_f papf,
307 hr_soft_coding_keyflex flex,
308 per_time_periods ptp
309 WHERE papf.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
310 AND flex.segment1 = p_paye_reference
311 AND flex.segment10 IS NOT NULL
312 AND papf.business_group_id = p_business_group_id
313 AND ptp.payroll_id = papf.payroll_id
314 AND l_eff_start_date BETWEEN papf.effective_start_date AND papf.effective_end_date
315 AND ptp.regular_payment_date BETWEEN l_date_soy AND l_date_eoy
316 )
317 GROUP BY assignment_id;
318
319 cursor csr_get_request_details is
320 select PROGRAM_APPLICATION_ID,CONCURRENT_PROGRAM_ID,LAST_UPDATE_DATE from fnd_concurrent_requests
321 where request_id = FND_GLOBAL.CONC_REQUEST_ID;
322
323 l_request_rec csr_get_request_details%rowtype;
324
325 BEGIN
326 -- HR_UTILITY.TRACE_ON(NULL,'gag');
327 HR_UTILITY.TRACE('Create_EPS_RECORD Started '||to_char(sysdate,'dd-mon-yyyy hh24:mm:ss'));
328 G_PAYE_REFERENCE := P_PAYE_REFERENCE;
329 g_business_group_id := p_business_group_id;
330 HR_UTILITY.TRACE(' Eff Date: '||P_EFFECTIVE_DT);
331 HR_UTILITY.TRACE(' PAYE ref :'||P_PAYE_REFERENCE);
332 HR_UTILITY.TRACE(' Business group id '||p_business_group_id);
333 L_EFF_START_DATE := FND_DATE.CANONICAL_TO_DATE(P_EFFECTIVE_DT);
334 HR_UTILITY.TRACE(' Eff Date: '||L_EFF_START_DATE);
335 IF L_EFF_START_DATE >= to_date('06-04-'||SUBSTR(TO_CHAR(l_eff_start_date,'YYYY/MON/DD'),1,4),'DD-MM-YYYY' ) THEN
336 l_date_soy := to_date('06-04-'||SUBSTR(TO_CHAR(l_eff_start_date,'YYYY/MON/DD'),1,4),'DD-MM-YYYY' ) ;
337 l_date_eoy := to_date('05-04-'||TO_CHAR(to_number(SUBSTR(TO_CHAR(l_eff_start_date,'YYYY/MON/DD'),1,4))+1 ),'DD-MM-YYYY') ;
338 ELSE
339 l_date_soy := to_date('06-04-'||TO_CHAR(to_number(SUBSTR(TO_CHAR(l_eff_start_date,'YYYY/MON/DD'),1,4))-1 ),'DD-MM-YYYY') ;
340 l_date_eoy := to_date('05-04-'||SUBSTR(TO_CHAR(l_eff_start_date,'YYYY/MON/DD'),1,4),'DD-MM-YYYY') ;
341 END IF;
342
343 if p_final_submission = 'Yes - Final for Ceased PAYE Ref' then
344 l_final_submission_ceased := 'Y';
345 elsif p_final_submission = 'Yes - Final for the Year' then
346 l_final_submission_year := 'Y';
347 end if;
348
349 --Check Record Exists
350 OPEN c_check_eps;
351 FETCH c_check_eps INTO l_dummy;
352 IF c_check_eps%FOUND THEN
353 CLOSE c_check_eps;
354 --No need to proceed further
355 --Raise Error message and skip
356 RAISE EPS_ALREADY_PRESENT;
357 END IF;
358 CLOSE c_check_eps;
359 GET_GLOBALS;
360 G_RELATED_TAX_YEAR := TO_CHAR(L_DATE_EOY,'YYYY');
361 -- L_CREATER_ID := FND_GLOBAL.USER_ID;
362 select EMPLOYEE_ID INTO L_CREATER_ID FRom FND_USER
363 WHERE USER_ID = FND_GLOBAL.USER_ID;
364 HR_UTILITY.TRACE(' CREATER_ID '||L_CREATER_ID);
365 l_nic_ssp_rate := G_NIC_RATE;
366 L_NIC_SMP_RATE := G_NIC_RATE;
367 IF g_ssp_recovery = 'None' THEN
368 --No need to calculate SSP balances
369 l_calculate_ssp := FALSE;
370 L_NIC_SSP_RATE :=0;
371 END IF;
372 IF g_smp_recovery = 'None' THEN
373 --No need to calculate SMP, SAP, OSP and ASPP balances
374 l_calculate_smp := FALSE;
375 elsif g_smp_recovery = 'Partial' THEN
376 --NIC balances will be zero
377 l_nic_smp_rate :=0;
378 END IF;
379
380 --Bug 13955276: Call to Validation of Econ Number, PAYE reference
381 IF PAY_GB_EPS_13.validate(P_PAYE_REFERENCE, G_ACC_REF_NO, G_EMPLOYERS_NAME, G_ECON_NUMBER, G_RELATED_TAX_YEAR,l_final_submission_ceased,l_final_submission_year)=1 THEN
382 IF L_CALCULATE_SSP OR L_CALCULATE_SMP THEN
383 --Either SSP or SMP calculate should be true.
384 FOR I IN c_lat_assignment_actions
385 LOOP
386 /* Commenting the code for SSP
387 IF L_CALCULATE_SSP THEN
388 l_ssp := l_ssp + 100 * hr_dirbal.get_balance(I.lat_assignment_action_id,g_ssp_id);
389 END IF;
390 */
391 IF L_CALCULATE_SMP THEN
392 L_SMP := L_SMP + 100 * HR_DIRBAL.GET_BALANCE(I.lat_assignment_action_id,G_SMP_ID);
393 l_sap := l_sap + 100 * hr_dirbal.get_balance(I.lat_assignment_action_id,g_sap_id);
394 L_SPP_ADOPT := L_SPP_ADOPT + 100 * HR_DIRBAL.GET_BALANCE(I.lat_assignment_action_id,G_SPP_ADOPT_ID);
395 L_SPP_BIRTH := L_SPP_BIRTH + 100 * HR_DIRBAL.GET_BALANCE(I.lat_assignment_action_id,G_SPP_BIRTH_ID);
396 L_ASPP_ADOPT := L_ASPP_ADOPT + 100 * HR_DIRBAL.GET_BALANCE(I.lat_assignment_action_id,G_ASPP_ADOPT_ID);
397 l_aspp_birth := l_aspp_birth + 100 * hr_dirbal.get_balance(I.lat_assignment_action_id,g_aspp_birth_id);
398
399 L_nic_SMP := L_nic_SMP + 100 * HR_DIRBAL.GET_BALANCE(I.lat_assignment_action_id,G_nic_SMP_ID);
400 l_nic_sap := l_nic_sap + 100 * hr_dirbal.get_balance(I.lat_assignment_action_id,g_nic_sap_id);
401 L_nic_SPP_ADOPT := L_nic_SPP_ADOPT + 100 * HR_DIRBAL.GET_BALANCE(I.lat_assignment_action_id,G_nic_SPP_ADOPT_ID);
402 L_nic_SPP_BIRTH := L_nic_SPP_BIRTH + 100 * HR_DIRBAL.GET_BALANCE(I.lat_assignment_action_id,G_nic_SPP_BIRTH_ID);
403 L_nic_ASPP_ADOPT := L_nic_ASPP_ADOPT + 100 * HR_DIRBAL.GET_BALANCE(I.lat_assignment_action_id,G_nic_ASPP_ADOPT_ID);
404 l_nic_aspp_birth := l_nic_aspp_birth + 100 * hr_dirbal.get_balance(I.lat_assignment_action_id,g_nic_aspp_birth_id);
405 END IF;
406 END LOOP;
407 END IF;
408 /* Commenting the code for SSP
409 IF G_SSP_RECOVERY = 'Full' THEN
410 l_ssp := l_ssp * G_SSP_RATE;
411 END IF;
412 */
413 /* IF g_smp_recovery = 'Partial' THEN
414 l_smp := l_smp * g_smp_sap_rate;
415 l_sap := l_sap * g_smp_sap_rate;
416 l_spp_adopt := l_spp_adopt * g_smp_sap_rate;
417 l_spp_birth := l_spp_birth * g_smp_sap_rate;
418 l_aspp_adopt := l_aspp_adopt * g_smp_sap_rate;
419 l_aspp_birth := l_aspp_birth * g_smp_sap_rate;
420 END IF;*/
421 hr_utility.trace(' Total SSP '||l_ssp);
422 hr_utility.trace(' Total SMP '||l_smp);
423 hr_utility.trace(' Total SAP '||l_sap);
424 hr_utility.trace(' Total OSPPA '||l_spp_adopt);
425 hr_utility.trace(' Total OSPPB '||l_spp_birth);
426 hr_utility.trace(' Total ASPPA '||l_aspp_adopt);
427 hr_utility.trace(' Total ASPPB '||l_aspp_birth);
428 l_spp := l_spp_adopt + l_spp_birth;
429 l_aspp := l_aspp_adopt + l_aspp_birth;
430 hr_utility.trace(' Total OSPP '||l_spp);
431 hr_utility.trace(' Total ASPP '||l_aspp);
432 hr_utility.trace(' NIC SSP Rate '||l_nic_ssp_rate);
433 hr_utility.trace(' NIC SMP Rate '||l_nic_smp_rate);
434 --NIC calculations
435 /* l_nic_ssp := l_ssp * l_nic_ssp_rate;
436 l_nic_smp := l_smp * l_nic_smp_rate;
437 l_nic_sap := l_sap * l_nic_smp_rate;
438 l_nic_spp := l_spp * l_nic_smp_rate;
439 l_nic_aspp := l_aspp * l_nic_smp_rate;
440 */
441 hr_utility.trace(' NIC SSP '||l_nic_ssp);
442 hr_utility.trace(' NIC SMP '||l_nic_smp);
443 hr_utility.trace(' NIC SAP '||l_nic_sap);
444 hr_utility.trace(' NIC OSPP '||l_nic_spp);
445 HR_UTILITY.TRACE(' NIC ASPP '||L_NIC_ASPP);
446
447 l_nic_spp := l_nic_spp_adopt + l_nic_spp_birth;
448 l_nic_aspp := l_nic_aspp_adopt + l_nic_aspp_birth;
449
450 open csr_get_request_details;
451 fetch csr_get_request_details into l_request_rec;
452 close csr_get_request_details;
453
454 --Insert EPS record
455 pay_gb_eps_13.INSERT_EPS_RECORD ( L_EFF_START_DATE, P_PAYE_REFERENCE, G_ACC_REF_NO, p_business_group_id, G_EMPLOYERS_NAME, G_ECON_NUMBER,
456 G_RELATED_TAX_YEAR,l_final_submission_ceased,l_final_submission_year, G_SMP_SAP_RATE, G_SSP_RATE, G_NIC_RATE, G_SERVICE_PROVIDER,
457 l_smp/100, l_sap/100, l_spp/100, l_aspp/100, l_ssp/100, L_NIC_SMP/100, l_nic_sap/100, l_nic_spp/100, l_nic_aspp/100, L_NIC_SSP/100, 'Saved',
458 L_CREATER_ID, G_APPROVER_ID , FND_GLOBAL.CONC_REQUEST_ID,l_request_rec.PROGRAM_APPLICATION_ID,l_request_rec.CONCURRENT_PROGRAM_ID,l_request_rec.LAST_UPDATE_DATE);
459
460
461 HR_UTILITY.TRACE(' EPS record insert complete ');
462 --Write Output Information
463 l_msg_output:= ' RTI - Employer Payment Summary - Created ';
464 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, L_MSG_OUTPUT);
465 L_MSG_OUTPUT:= ' ';
466 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, L_MSG_OUTPUT);
467 l_msg_output:= ' ';
468 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, L_MSG_OUTPUT);
469 L_MSG_OUTPUT:= 'PAYE Reference : '||rpad(P_PAYE_REFERENCE,20,' ') ||' Effective Date: '||FND_DATE.CANONICAL_TO_DATE(P_EFFECTIVE_DT);
470 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, L_MSG_OUTPUT);
471 L_MSG_OUTPUT:= ' ';
472 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, L_MSG_OUTPUT);
473 --Start of 13726549
474 IF G_SMP_RECOVERY = 'Full' THEN
475 l_msg_output:= 'SMP/SAP/OSPP/ASPP Recovery Type: '||rpad(g_smp_recovery,20,' ')||' Recovery Percentage Used: '||(100);
476 ELSIF G_SMP_RECOVERY = 'Partial' THEN
477 l_msg_output:= 'SMP/SAP/OSPP/ASPP Recovery Type: '||rpad(g_smp_recovery,20,' ')||' Recovery Percentage Used: '||(g_smp_sap_rate*100);
478 ELSE
479 l_msg_output:= 'SMP/SAP/OSPP/ASPP Recovery Type: '||rpad(g_smp_recovery,20,' ')||' Recovery Percentage Used: '||(0);
480 END IF;
481 --End of 13726549
482 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, L_MSG_OUTPUT);
483 IF G_SSP_RECOVERY = 'Full' THEN
484 L_MSG_OUTPUT:= 'SSP Recovery Type : '||rpad(G_SSP_RECOVERY,20,' ')||' Recovery Percentage Used: '||'100';
485 ELSE
486 L_MSG_OUTPUT:= 'SSP Recovery Type : '||rpad(G_SSP_RECOVERY,20,' ')||' Recovery Percentage Used: '||'0';
487 END IF;
488
489 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, L_MSG_OUTPUT);
490 L_MSG_OUTPUT:= ' ';
491 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, L_MSG_OUTPUT);
492 L_MSG_OUTPUT:= 'SMP Amount Recovered : '||rpad(to_char(L_SMP/100,'FM9999999990D00'),30,' ')||' NIC Compensation on SMP : '||to_char(L_NIC_SMP/100,'FM9999999990D00');
493 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, L_MSG_OUTPUT);
494 L_MSG_OUTPUT:= 'SAP Amount Recovered : '||rpad(to_char(L_SAP/100,'FM9999999990D00'),30,' ')||' NIC Compensation on SAP : '||to_char(L_NIC_SAP/100,'FM9999999990D00');
495 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, L_MSG_OUTPUT);
496 L_MSG_OUTPUT:= 'OSPP Amount Recovered : '||rpad(to_char(L_SPP/100,'FM9999999990D00'),30,' ')||' NIC Compensation on OSPP : '||to_char(L_NIC_SPP/100,'FM9999999990D00');
497 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, L_MSG_OUTPUT);
498 L_MSG_OUTPUT:= 'ASPP Amount Recovered : '||rpad(to_char(L_ASPP/100,'FM9999999990D00'),30,' ')||' NIC Compensation on ASPP : '||to_char(L_NIC_ASPP/100,'FM9999999990D00');
499 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, L_MSG_OUTPUT);
500 L_MSG_OUTPUT:= 'SSP Amount Recovered : '||rpad(to_char(L_SSP/100,'FM9999999990D00'),30,' ')||' NIC Compensation on SSP : '||to_char(L_NIC_SSP/100,'FM9999999990D00');
501 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, L_MSG_OUTPUT);
502 HR_UTILITY.TRACE('Create_EPS_RECORD completed '||to_char(sysdate,'dd-mon-yyyy hh24:mm:ss'));
503 END IF;
504 EXCEPTION
505 --13741983 begin
506 WHEN MANDATORY_VALUES_MISSING THEN
507 hr_utility.trace('Error: Mandatory values required for processing EPS are not present. Please check ');
508 raise_application_error(-20001,' Mandatory Values missing');
509 --13741983 end
510 WHEN Global_missing THEN
511 hr_utility.trace('Error: The globals required for processing EPS are not present. Please check ');
512 l_msg_output := 'Error: The globals required for processing EPS are not present. Please check ';
513 fnd_file.put_line(fnd_file.log,l_msg_output);
514 raise_application_error(-20001,' Globals missing');
515 WHEN EPS_ALREADY_PRESENT THEN
516 l_msg_output:= 'EPS record already created for the given parameters. Please check ';
517 hr_utility.trace('EPS record already created for the given parameters. Please check ');
518 fnd_file.put_line(fnd_file.log,l_msg_output);
519 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,L_MSG_OUTPUT);
520 raise_application_error(-20001,' EPS record already created for the given parameters. Please check');
521 WHEN OTHERS THEN
522 fnd_file.put_line(fnd_file.log, SUBSTR(sqlerrm(SQLCODE),1,80));
523 RAISE_APPLICATION_ERROR(-20001,' Error'||SQLERRM(SQLCODE));
524 rollback;
525 END Create_EPS_RECORD;
526 /* This function is called from formula function: calc
527 to calculate the recovery amounts and NIC amounts for EPS.
528 */
529 function calc_rec_nic_amounts(
530 p_asg_act_id in number,
531 p_ele_type_id in number,
532 p_amount in number,
533 p_rec_amount out NOCOPY number,
534 p_nic_amount out NOCOPY number
535 ) return number
536 is
537 begin
538 fnd_file.put_line(fnd_file.log, 'Entered calc_rec_nic_amounts');
539 fnd_file.put_line(fnd_file.log, 'p_asg_act_id: ' || p_asg_act_id );
540 fnd_file.put_line(fnd_file.log, 'p_amount: ' || p_amount);
541 p_rec_amount := p_amount * 0.92;
542 p_nic_amount := p_amount * 1;
543 fnd_file.put_line(fnd_file.log, 'p_rec_amount: ' || p_rec_amount );
544 fnd_file.put_line(fnd_file.log, 'p_nic_amount: ' || p_nic_amount);
545 return 1;
546
547
548 end calc_rec_nic_amounts;
549
550 END Create_GB_RTI_EPS_RECORD_13;