[Home] [Help]
PACKAGE BODY: APPS.PAY_GB_P11D_MAGTAPE
Source
1 Package Body PAY_GB_P11D_MAGTAPE as
2 /* $Header: pygbpdtp.pkb 120.4.12000000.2 2007/04/05 10:24:24 kthampan noship $ */
3 /*===========================================================================+
4 | Copyright (c) 1993 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
7 +============================================================================
8 Name
9 PAY_GB_P11D_MAGTAPE
10 Purpose
11
12 This is a UK Specific payroll package.
13 Notes
14
15 History
16 ==============================================================================*/
17 --
18 -- Globals
19 g_package CONSTANT VARCHAR2(20) := 'pay_gb_p11d_magtape.';
20
21 --
22 function get_parameters (p_payroll_action_id in number,
23 p_token_name in varchar2,
24 p_tax_ref in varchar2 default null)return varchar2
25 is
26 cursor csr_parameter_info(p_pact_id number,
27 p_token char)
28 is
29 select substr(legislative_parameters,
30 instr(legislative_parameters, p_token) + (length (p_token) + 1),
31 (decode(instr (legislative_parameters, ' ', instr (legislative_parameters, p_token)),
32 0, decode (instr (legislative_parameters, p_token),0, .5,length (legislative_parameters)),
33 instr (legislative_parameters, ' ', instr (legislative_parameters, p_token)) -
34 (instr (legislative_parameters, p_token) + (length (p_token) + 1))))),
35 business_group_id,
36 request_id,
37 start_date,
38 effective_date -- this will be the benefit end date
39 from pay_payroll_actions
40 where payroll_action_id = p_pact_id;
41
42 cursor csr_org_info(p_business_group_id number,
43 p_tax_ref varchar2)
44 is
45 select org.org_information11,
46 substr(org.org_information3,1,36),
47 org.org_information13
48 from hr_organization_information org
49 where org.organization_id = p_business_group_id
50 and upper(org.org_information1) = upper(p_tax_ref)
51 and org.org_information_context = 'Tax Details References';
52
53 cursor csr_count_taxref(p_pact_id number)
54 is
55 select count(*),
56 action_information6
57 from pay_assignment_actions paa,
58 pay_action_information pai
59 where paa.payroll_action_id = p_pact_id
60 and pai.action_context_id = paa.assignment_action_id
61 and pai.action_information_category = 'EMEA PAYROLL INFO'
62 and pai.action_context_type = 'AAP'
63 group by action_information6;
64
65 l_business_group_id VARCHAR2(20);
66 l_benefit_start_date VARCHAR2(20);
67 l_benefit_end_date VARCHAR2(20);
68 l_party_name VARCHAR2(36);
69 l_request_id VARCHAR2(50);
70 l_sender_id VARCHAR2(50);
71 l_submitter_id VARCHAR2(50);
72 l_token_value VARCHAR2(50);
73 l_proc VARCHAR2(50) := g_package || 'get_parameters';
74 l_tax_ref VARCHAR2(50);
75 l_count NUMBER;
76 begin
77 hr_utility.set_location ('Entering ' || l_proc, 10);
78 hr_utility.set_location ('Step ' || l_proc, 20);
79 hr_utility.set_location ('p_token_name = ' || p_token_name, 20);
80 open csr_parameter_info (p_payroll_action_id, p_token_name);
81 fetch csr_parameter_info into l_token_value,
82 l_business_group_id,
83 l_request_id,
84 l_benefit_start_date,
85 l_benefit_end_date;
86 close csr_parameter_info;
87
88 if p_token_name = 'BG_ID'
89 then
90 l_token_value := l_business_group_id;
91 elsif p_token_name = 'REQUEST_ID'
92 then
93 l_token_value := l_request_id;
94 elsif p_token_name = 'SENDER_ID' or p_token_name = 'PARTY_NAME' or p_token_name = 'SUBMITTER_REF_NO'
95 then
96 open csr_count_taxref(p_payroll_action_id);
97 loop
98 fetch csr_count_taxref into l_count, l_tax_ref;
99 exit when csr_count_taxref%notfound;
100 end loop;
101 l_count := csr_count_taxref%rowcount;
102 close csr_count_taxref;
103
104 if l_count = 1 then
105 open csr_org_info(l_business_group_id,l_tax_ref);
106 fetch csr_org_info into l_sender_id,
107 l_party_name,
108 l_submitter_id;
109 close csr_org_info;
110 else
111 open csr_org_info(l_business_group_id,nvl(p_tax_ref,l_tax_ref));
112 fetch csr_org_info into l_sender_id,
113 l_party_name,
114 l_submitter_id;
115 close csr_org_info;
116 end if;
117 if p_token_name = 'SENDER_ID'
118 then
119 l_token_value := l_sender_id;
120 elsif p_token_name = 'PARTY_NAME'
121 then
122 l_token_value := l_party_name;
123 elsif p_token_name = 'SUBMITTER_REF_NO'
124 then
125 l_token_value := l_submitter_id;
126 end if;
127 elsif p_token_name = 'BENEFIT_START_DATE'
128 then
129 l_token_value := fnd_date.date_to_canonical (l_benefit_start_date);
130 elsif p_token_name = 'BENEFIT_END_DATE'
131 then
132 l_token_value := fnd_date.date_to_canonical (l_benefit_end_date);
133 else
134 l_token_value := l_token_value;
135 end if;
136
137 hr_utility.set_location ('l_token_value = ' || l_token_value, 60);
138 hr_utility.set_location ('Leaving ' || l_proc, 70);
139 return l_token_value;
140 end get_parameters;
141
142 PROCEDURE range_cursor (
143 pactid IN NUMBER,
144 sqlstr OUT NOCOPY VARCHAR2
145 )
146 IS
147 l_proc CONSTANT VARCHAR2(35) := g_package || 'range_cursor';
148 BEGIN
149 --
150 hr_utility.set_location ('Entering: ' || l_proc, 1);
151 --
152 -- Note: There must be one and only one entry of :payroll_action_id in
153 -- the string, and the statement must be, order by person_id
154 --
155 sqlstr := 'SELECT DISTINCT person_id
156 FROM per_people_f ppf,
157 pay_payroll_actions ppa
158 WHERE ppa.payroll_action_id = :payroll_action_id
159 AND ppa.business_group_id +0= ppf.business_group_id
160 ORDER BY ppf.person_id';
161
162 hr_utility.set_location ('Leaving ' || l_proc, 20);
163 --
164 END range_cursor;
165
166 --
167 --
168 --
169 PROCEDURE action_creation (
170 pactid IN NUMBER,
171 stperson IN NUMBER,
172 endperson IN NUMBER,
173 chunk IN NUMBER
174 )
175 IS
176 --
177 CURSOR csr_assignments (
178 v_archive_payroll_action_id NUMBER,
179 v_tax_ref VARCHAR2
180 )
181 is
182 select /*+ ordered
183 use_nl(paa,pai_person)
184 use_index(pai_person,pay_action_information_n2) */
185 distinct paa.assignment_id
186 from pay_assignment_actions paa,
187 pay_action_information pai_person,
188 pay_action_information pai_emp
189 where paa.payroll_action_id = v_archive_payroll_action_id
190 and pai_person.action_context_id = paa.assignment_action_id
191 and pai_person.action_information_category = 'ADDRESS DETAILS'
192 and pai_person.action_information14 = 'Employee Address'
193 and pai_person.action_information1 between stperson and endperson
194 and pai_person.action_context_type = 'AAP'
195 and pai_emp.action_context_id = paa.assignment_action_id
196 and pai_emp.action_context_type = 'AAP'
197 and pai_emp.action_information_category = 'EMEA PAYROLL INFO'
198 and (v_tax_ref is null
199 or
200 pai_emp.action_information6 = v_tax_ref)
201 order by paa.assignment_id;
202
203 --
204 l_ass_act_id pay_assignment_actions.assignment_action_id%TYPE;
205 l_proc CONSTANT VARCHAR2(35) := g_package || 'action_creation';
206 l_arch_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE;
207 l_tax_ref varchar2(30);
208 BEGIN
209 --
210 hr_utility.set_location ('Entering: ' || l_proc, 1);
211 --
212 l_arch_payroll_action_id := get_parameters (pactid, 'ARCH_PAYROLL_ACTION_ID');
213 l_tax_ref := get_parameters (pactid, 'TAX_REFERENCE');
214
215 hr_utility.set_location ('arch pay action id ' || l_arch_payroll_action_id, 1);
216
217 --
218 FOR asgrec IN csr_assignments (l_arch_payroll_action_id,l_tax_ref)
219 LOOP
220 --
221 -- Create the assignment_action to represent the preson / tax unit combination
222 --
223 SELECT pay_assignment_actions_s.nextval
224 INTO l_ass_act_id
225 FROM dual;
226 --
227 -- insert into pay_assignment_actions.
228 hr_utility.set_location ('assignment id ' || asgrec.assignment_id, 1);
229
230 hr_nonrun_asact.insact (l_ass_act_id, asgrec.assignment_id, pactid, chunk, NULL);
231
232 END LOOP;
233
234 --
235 hr_utility.set_location (' Leaving: ' || l_proc, 100);
236 --
237 -- hr_utility.trace_off;
238
239 END action_creation;
240 --
241 --
242
243 Function format_edi_currency (
244 l_input_value varchar2
245 )
246 return varchar2
247 is
248 l_output_value varchar2(36);
249 l_input_value_number number;
250 begin
251 l_input_value_number := to_number(l_input_value);
252 l_input_value_number := l_input_value_number * 100;
253 if sign(l_input_value_number) = -1
254 then
255 l_input_value_number := l_input_value_number * -1;
256 l_output_value := '-' || lpad(to_char(l_input_value_number),34,'0');
257 else
258 l_output_value := lpad(to_char(l_input_value_number),35,'0');
259 end if;
260 l_output_value := l_output_value || ' ';
261 return l_output_value;
262
263 Exception
264 when others then
265 fnd_file.put_line (fnd_file.LOG, 'Error: ' || sqlerrm);
266 fnd_file.put_line (fnd_file.LOG, 'input_value ' ||l_input_value );
267
268 fnd_message.raise_error;
269 end;
270
271 Function round_and_pad (
272 l_input_value varchar2,
273 l_cut_to_size number
274 )
275 return varchar2
276 is
277 l_output_value varchar2(10);
278 l_input_value_number number;
279 begin
280 l_input_value_number := to_number(l_input_value);
281 select
282 decode(sign(l_input_value),
283 1,lpad(trunc(l_input_value),l_cut_to_size,0),
284 0,lpad(trunc(l_input_value),l_cut_to_size,0),
285 -1,'-' || lpad(trunc(abs(l_input_value)),l_cut_to_size-1,0)
286 ) into l_output_value
287 from dual;
288 return l_output_value;
289 Exception
290 when others then
291 fnd_file.put_line (fnd_file.LOG, 'Error: ' || sqlerrm);
292 fnd_file.put_line (fnd_file.LOG, 'input_value ' ||l_input_value );
293 fnd_file.put_line (fnd_file.LOG, 'cut to size ' ||l_cut_to_size);
294
295 fnd_message.raise_error;
296 end;
297
298 Function get_description (
299 l_lookup_code varchar2,
300 l_lookup_type varchar2,
301 l_effective_date varchar2
302 )
303 return varchar2
304 is
305 l_description fnd_lookup_values.DESCRIPTION%type;
306 begin
307 /* select upper(description) into l_description
308 from fnd_lookup_values flv
309 where flv.lookup_type = l_lookup_type
310 and flv.lookup_code = l_lookup_code
311 and flv.ENABLED_FLAG = 'Y'
312 and fnd_date.canonical_to_date(l_effective_date) between
313 nvl(flv.START_DATE_ACTIVE,
314 fnd_date.canonical_to_date(l_effective_date)) and
315 nvl(flv.END_DATE_ACTIVE,
316 fnd_date.canonical_to_date(l_effective_date));*/
317 /*Bug No. 3237648*/
318 /*Fetching from hr_lookups instead of fnd_lookup_values*/
319 select upper(description) into l_description
320 from hr_lookups hlu
321 where hlu.lookup_type = l_lookup_type
322 and hlu.lookup_code = l_lookup_code
323 and hlu.ENABLED_FLAG = 'Y'
324 and fnd_date.canonical_to_date(l_effective_date) between
325 nvl(hlu.START_DATE_ACTIVE,
326 fnd_date.canonical_to_date(l_effective_date)) and
327 nvl(hlu.END_DATE_ACTIVE,
328 fnd_date.canonical_to_date(l_effective_date));
329
330 return l_description;
331 exception
332 when Others then
333 fnd_file.put_line (fnd_file.LOG, 'Error: ' || sqlerrm);
334 fnd_file.put_line (fnd_file.LOG, 'effective date ' ||l_effective_date );
335 fnd_file.put_line (fnd_file.LOG, 'lookup_type ' ||l_lookup_type );
336 fnd_file.put_line (fnd_file.LOG, 'lookup_code ' ||l_lookup_code );
337
338 fnd_message.raise_error;
339 end;
340 END; -- Package Body PAY_GB_P11D_MAGTAPE