eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.CREATE_GB_RTI_EPS_RECORD

Source


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