4 level_cnt NUMBER; -- required by the generic magtape procedure.
1 PACKAGE PAY_GB_P11D_EDI_CONTROL AUTHID CURRENT_USER AS
2 /* $Header: pygbp11dc.pkh 120.13.12010000.3 2010/02/22 11:48:47 krreddy ship $ */
3
5
6 function fetch_total_benefit(p_assact_id in number,
7 p_pact_id in number,
8 p_employer_ref in varchar2) return number;
9
10 -- CURSOR --
11 cursor csr_p11d_header is
12 select 'TAX_YEAR=P',pay_gb_p11d_magtape.get_parameters(
13 pay_magtape_generic.get_parameter_value('ARCH_PAYROLL_ACTION_ID'),'Rep_Run'),
14 'TEST_SUBMISSION=P',pay_magtape_generic.get_parameter_value('TEST_SUBMISSION'),
15 'TRANSMISSION_DATE=P',to_char(sysdate,'YYYYMMDDHHMMSS'),
16 'PAYROLL_ACTION_ID=P',pay_magtape_generic.get_parameter_value('ARCH_PAYROLL_ACTION_ID')
17 from dual;
18
19 cursor csr_p11d_employer is
20 select 'TAX_OFFICE_NAME=P', NVL(UPPER(action_information4), ' '),
24 'EMPLOYERS_ADDRESS=P', NVL(UPPER(action_information8), ' '),
21 'TAX_OFFICE_PHONE_NO=P', NVL(UPPER(action_information5), ' '),
22 'EMPLOYERS_REF_NO=P', NVL(UPPER(action_information6), ' '),
23 'EMPLOYERS_NAME=P', NVL(UPPER(action_information7), ' '),
25 'MESSAGE_DATE=P', to_char(sysdate,'YYYYMMDD'),
26 'PARTY_NAME=P', pay_gb_p11d_magtape.get_parameters(
27 pay_magtape_generic.get_parameter_value('ARCH_PAYROLL_ACTION_ID'),
28 'PARTY_NAME',action_information6),
29 'SENDER_ID=P', pay_gb_p11d_magtape.get_parameters(
30 pay_magtape_generic.get_parameter_value('ARCH_PAYROLL_ACTION_ID'),'SENDER_ID',
31 action_information6),
32 'UNIQUE_REFERENCE=P', to_number(pay_gb_p11d_magtape.get_parameters(
33 pay_magtape_generic.get_parameter_value('ARCH_PAYROLL_ACTION_ID'),'REQUEST_ID')),
34 'SUBMITTER_REF_NO=P', pay_gb_p11d_magtape.get_parameters(
35 pay_magtape_generic.get_parameter_value('ARCH_PAYROLL_ACTION_ID'),'SUBMITTER_REF_NO',
36 action_information6)
37 from pay_assignment_actions paa,
38 pay_action_information pai
39 where paa.payroll_action_id = pay_magtape_generic.get_parameter_value('ARCH_PAYROLL_ACTION_ID')
40 and pai.action_context_id = paa.assignment_action_id
41 and pai.action_information_category = 'EMEA PAYROLL INFO'
42 and pai.action_context_type = 'AAP'
43 and (pay_magtape_generic.get_parameter_value('TAX_REFERENCE') is null
44 or
45 upper(pai.action_information6) = upper(pay_magtape_generic.get_parameter_value('TAX_REFERENCE')))
46 group by 'TAX_OFFICE_NAME=P', NVL(UPPER(action_information4), ' '),
47 'TAX_OFFICE_PHONE_NO=P', NVL(UPPER(action_information5), ' '),
48 'EMPLOYERS_REF_NO=P', NVL(UPPER(action_information6), ' '),
49 'EMPLOYERS_NAME=P', NVL(UPPER(action_information7), ' '),
50 'EMPLOYERS_ADDRESS=P', NVL(UPPER(action_information8), ' '),
51 'MESSAGE_DATE=P', to_char(sysdate,'YYYYMMDD'),
52 'PARTY_NAME=P', pay_gb_p11d_magtape.get_parameters(
53 pay_magtape_generic.get_parameter_value('ARCH_PAYROLL_ACTION_ID'),
54 'PARTY_NAME',action_information6),
55 'SENDER_ID=P', pay_gb_p11d_magtape.get_parameters(
56 pay_magtape_generic.get_parameter_value('ARCH_PAYROLL_ACTION_ID'),'SENDER_ID',
57 action_information6),
58 'UNIQUE_REFERENCE=P', to_number(pay_gb_p11d_magtape.get_parameters(
59 pay_magtape_generic.get_parameter_value('ARCH_PAYROLL_ACTION_ID'),'REQUEST_ID')),
60 'SUBMITTER_REF_NO=P', pay_gb_p11d_magtape.get_parameters(
61 pay_magtape_generic.get_parameter_value('ARCH_PAYROLL_ACTION_ID'),'SUBMITTER_REF_NO',
62 action_information6);
63
64
65 cursor csr_p11d_employee is
66 select /*+ ORDERED use_nl(paa,pai)
67 use_index(pai,pay_action_information_n2)*/
68 'PERSON_ID=P',pai.action_information10
69 from pay_assignment_actions paa,
70 pay_action_information pai
71 where paa.payroll_action_id = pay_magtape_generic.get_parameter_value('ARCH_PAYROLL_ACTION_ID')
72 and pai.action_context_id = paa.assignment_action_id
73 and pai.action_information_category = 'GB EMPLOYEE DETAILS'
74 and pai.action_context_type = 'AAP'
75 and upper(pai.action_information13) = upper(pay_magtape_generic.get_parameter_value('EMPLOYERS_REF_NO'))
76 and pay_gb_p11d_edi_control.fetch_total_benefit(paa.assignment_action_id,
77 paa.payroll_action_id,
78 pai.action_information13) > 0
79 group by 'PERSON_ID=P',pai.action_information10;
80
81 cursor csr_p11d_benefit is
82 select 'BENEFIT_TYPE=P', cat
83 from (
84 select cat
85 from (
86 -- Benefit from this select may occur only 1 time
87 -- Benefit from this select may occur only 1 time
88 select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
89 use_index(pai_person,pay_action_information_n2)
90 use_index(pai,pay_action_information_n2) */
91 decode(pai.action_information_category,
92 'ASSETS TRANSFERRED', 'A',
93 'PAYMENTS MADE FOR EMP', 'B',
94 'VOUCHERS OR CREDIT CARDS', 'C',
95 'LIVING ACCOMMODATION', 'D',
96 'MILEAGE ALLOWANCE AND PPAYMENT', 'E',
97 'VANS 2007', 'G', -- EOY 2008
98 'VANS 2005', 'G',
99 'VANS 2002_03', 'G',
100 'PVT MED TREATMENT OR INSURANCE', 'I',
101 'RELOCATION EXPENSES', 'J',
102 'SERVICES SUPPLIED', 'K',
103 'ASSETS AT EMP DISPOSAL', 'L',
104 'OTHER ITEMS', 'M',
105 'OTHER ITEMS NON 1A', 'M',
106 'EXPENSES PAYMENTS', 'N',
107 'MARORS', 'U') cat
108 from per_all_assignments_f paf,
109 pay_assignment_actions paa,
110 pay_action_information pai,
111 pay_action_information pai_person
112 where paf.person_id = pay_magtape_generic.get_parameter_value('PERSON_ID')
113 and paf.effective_end_date = (select max(paf2.effective_end_date)
114 from per_all_assignments_f paf2
118 and paa.payroll_action_id = pay_magtape_generic.get_parameter_value('ARCH_PAYROLL_ACTION_ID')
115 where paf2.assignment_id = paf.assignment_id
116 and paf2.person_id = pay_magtape_generic.get_parameter_value('PERSON_ID'))
117 and paf.assignment_id = paa.assignment_id
119 and pai.action_context_id = paa.assignment_action_id
120 and pai.action_context_type = 'AAP'
121 and pai.action_information_category = pai.action_information_category
122 and pai_person.action_context_id = paa.assignment_action_id
123 and pai_person.action_information_category = 'GB EMPLOYEE DETAILS'
124 and upper(pai_person.action_information13) = upper(pay_magtape_generic.get_parameter_value('EMPLOYERS_REF_NO'))
125 and pai_person.action_context_type = 'AAP')
126 group by cat -- pai.action_information_category
127 union all -- Benefit from this select may occur more than 1 time or
128 -- it has a special layout/format - ie Expenses Payments.
129 select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
130 use_index(pai_person,pay_action_information_n2)
131 use_index(pai,pay_action_information_n2) */
132 decode(pai.action_information_category,
133 'CAR AND CAR FUEL 2003_04', 'F',
134 'INT FREE AND LOW INT LOANS', 'H') cat
135 from per_all_assignments_f paf,
136 pay_assignment_actions paa,
137 pay_action_information pai,
138 pay_action_information pai_person
139 where paf.person_id = pay_magtape_generic.get_parameter_value('PERSON_ID')
140 and paf.effective_end_date = (select max(paf2.effective_end_date)
141 from per_all_assignments_f paf2
142 where paf2.assignment_id = paf.assignment_id
143 and paf2.person_id = pay_magtape_generic.get_parameter_value('PERSON_ID'))
144 and paf.assignment_id = paa.assignment_id
145 and paa.payroll_action_id = pay_magtape_generic.get_parameter_value('ARCH_PAYROLL_ACTION_ID')
146 and pai.action_context_id = paa.assignment_action_id
147 and pai.action_context_type = 'AAP'
148 and pai.action_information_category = pai.action_information_category
149 and pai_person.action_context_id = paa.assignment_action_id
150 and pai_person.action_information_category = 'GB EMPLOYEE DETAILS'
151 and upper(pai_person.action_information13) = upper(pay_magtape_generic.get_parameter_value('EMPLOYERS_REF_NO'))
152 and pai_person.action_context_type = 'AAP')
153 where cat in ('A',
154 'B',
155 'C',
156 'D',
157 'E',
158 'F',
159 'G',
160 'H',
161 'I',
162 'J',
163 'K',
164 'L',
165 'M',
166 'N',
167 'U')
168 order by cat;
169
170 function get_header(p_sender_id in varchar2,
171 p_transmission_date in varchar2,
172 p_test_transmission in varchar2,
173 p_unique_reference in varchar2,
174 p_tax_year in varchar2,
175 p_missing_val in out NoCopy number,
176 p_error_count in out NoCopy number,
177 p_error_msg1 out NoCopy varchar2,
178 p_error_msg2 out NoCopy varchar2,
179 p_error_msg3 out NoCopy varchar2,
180 p_error_msg4 out NoCopy varchar2,
181 p_error_msg5 out NoCopy varchar2,
182 p_error_msg6 out NoCopy varchar2,
183 p_edi_rec1 out NoCopy varchar2,
184 p_edi_rec2 out NoCopy varchar2,
185 p_edi_rec3 out NoCopy varchar2,
186 p_edi_rec4 out NoCopy varchar2,
187 p_edi_rec5 out NoCopy varchar2,
188 p_edi_rec6 out NoCopy varchar2) return number;
189
190 function get_employer(p_tax_office_name in varchar2,
191 p_tax_phone_no in varchar2,
192 p_employer_ref in varchar2,
193 p_employer_name in varchar2,
194 p_employer_addr in varchar2,
195 p_submitter_ref in varchar2,
196 p_message_date in varchar2,
197 p_tax_year in varchar2,
198 p_party in varchar2,
199 p_error_count in out NoCopy number,
200 p_error_msg1 out NoCopy varchar2,
201 p_error_msg2 out NoCopy varchar2,
202 p_error_msg3 out NoCopy varchar2,
203 p_error_msg4 out NoCopy varchar2,
204 p_error_msg5 out NoCopy varchar2,
205 p_error_msg6 out NoCopy varchar2,
206 p_error_msg7 out NoCopy varchar2,
207 p_error_msg8 out NoCopy varchar2,
208 p_error_msg9 out NoCopy varchar2,
209 p_edi_rec1 out NoCopy varchar2,
210 p_edi_rec2 out NoCopy varchar2,
211 p_edi_rec3 out NoCopy varchar2,
212 p_edi_rec4 out NoCopy varchar2,
216 p_edi_rec8 out NoCopy varchar2,
213 p_edi_rec5 out NoCopy varchar2,
214 p_edi_rec6 out NoCopy varchar2,
215 p_edi_rec7 out NoCopy varchar2,
217 p_edi_rec9 out NoCopy varchar2,
218 p_edi_rec10 out NoCopy varchar2,
219 p_edi_rec11 out NoCopy varchar2,
220 p_edi_rec12 out NoCopy varchar2) return number;
221
222 function get_employee(p_person_id in varchar2,
223 p_pact_id in varchar2,
224 p_tax_year in varchar2,
225 p_error_count in out NoCopy number,
226 p_error_msg1 out NoCopy varchar2,
227 p_error_msg2 out NoCopy varchar2,
228 p_error_msg3 out NoCopy varchar2,
229 p_error_msg4 out NoCopy varchar2,
230 p_error_msg5 out NoCopy varchar2,
231 p_error_msg6 out NoCopy varchar2,
232 p_error_msg7 out NoCopy varchar2,
233 p_error_msg8 out NoCopy varchar2,
234 p_edi_rec1 out NoCopy varchar2,
235 p_edi_rec2 out NoCopy varchar2,
236 p_edi_rec3 out NoCopy varchar2,
237 p_edi_rec4 out NoCopy varchar2,
238 p_edi_rec5 out NoCopy varchar2,
239 p_edi_rec6 out NoCopy varchar2,
240 p_edi_rec7 out NoCopy varchar2,
241 p_edi_rec8 out NoCopy varchar2) return number;
242
243 function get_benefit(p_benefit_type in varchar2,
244 p_person_id in varchar2,
245 p_employer_ref in varchar2,
246 p_tax_year in varchar2,
247 p_benefit_count in varchar2,
248 p_pact_id in varchar2,
249 p_value1 in out NoCopy varchar2,
250 p_value2 in out NoCopy varchar2,
251 p_value3 in out NoCopy varchar2,
252 p_value4 in out NoCopy varchar2,
253 p_value5 in out NoCopy varchar2,
254 p_value6 in out NoCopy varchar2,
255 p_value7 in out NoCopy varchar2,
256 p_value8 in out NoCopy varchar2,
257 p_value9 in out NoCopy varchar2,
258 p_value10 in out NoCopy varchar2,
259 p_value11 in out NoCopy varchar2,
260 p_value12 in out NoCopy varchar2,
261 p_value13 in out NoCopy varchar2,
262 p_value14 in out NoCopy varchar2,
263 p_value15 in out NoCopy varchar2,
264 p_value16 in out NoCopy varchar2,
265 p_value17 in out NoCopy varchar2,
266 p_value18 in out NoCopy varchar2,
267 p_value19 in out NoCopy varchar2,
268 p_value20 in out NoCopy varchar2,
269 p_value21 in out NoCopy varchar2,
270 p_value22 in out NoCopy varchar2,
271 p_value23 in out NoCopy varchar2,
272 p_value24 in out NoCopy varchar2,
273 p_value25 in out NoCopy varchar2,
274 p_value26 in out NoCopy varchar2,
275 p_value27 in out NoCopy varchar2,
276 p_value28 in out NoCopy varchar2,
277 p_value29 in out NoCopy varchar2,
278 p_value30 in out NoCopy varchar2,
279 p_edi_rec1 out NoCopy varchar2,
280 p_edi_rec2 out NoCopy varchar2,
281 p_edi_rec3 out NoCopy varchar2,
282 p_edi_rec4 out NoCopy varchar2,
283 p_edi_rec5 out NoCopy varchar2,
284 p_edi_rec6 out NoCopy varchar2,
285 p_edi_rec7 out NoCopy varchar2,
286 p_edi_rec8 out NoCopy varchar2,
287 p_edi_rec9 out NoCopy varchar2,
288 p_edi_rec10 out NoCopy varchar2,
289 p_edi_rec11 out NoCopy varchar2,
290 p_edi_rec12 out NoCopy varchar2,
291 p_edi_rec13 out NoCopy varchar2,
292 p_edi_rec14 out NoCopy varchar2,
293 p_edi_rec15 out NoCopy varchar2,
294 p_edi_rec16 out NoCopy varchar2,
295 p_edi_rec17 out NoCopy varchar2,
296 p_edi_rec18 out NoCopy varchar2,
297 p_edi_rec19 out NoCopy varchar2,
298 p_edi_rec20 out NoCopy varchar2,
299 p_edi_rec21 out NoCopy varchar2,
300 p_edi_rec22 out NoCopy varchar2,
301 p_edi_rec23 out NoCopy varchar2,
302 p_edi_rec24 out NoCopy varchar2,
303 p_edi_rec25 out NoCopy varchar2,
304 p_edi_rec26 out NoCopy varchar2,
305 p_edi_rec27 out NoCopy varchar2,
306 p_edi_rec28 out NoCopy varchar2,
310 function get_summary(p_benefit_type in varchar2,
307 p_edi_rec29 out NoCopy varchar2,
308 p_edi_rec30 out NoCopy varchar2) return number;
309
311 p_tax_year in varchar2,
312 p_value1 in varchar2,
313 p_value2 in varchar2,
314 p_value3 in varchar2,
315 p_value4 in varchar2,
316 p_value5 in varchar2,
317 p_value6 in varchar2,
318 p_value7 in varchar2,
319 p_value8 in varchar2,
320 p_value9 in varchar2,
321 p_value10 in varchar2,
322 p_value11 in varchar2,
323 p_value12 in varchar2,
324 p_value13 in varchar2,
325 p_value14 in varchar2,
326 p_value15 in varchar2,
327 p_value16 in varchar2,
328 p_value17 in varchar2,
329 p_value18 in varchar2,
330 p_value19 in varchar2,
331 p_value20 in varchar2,
332 p_value21 in varchar2,
333 p_value22 in varchar2,
334 p_value23 in varchar2,
335 p_value24 in varchar2,
336 p_value25 in varchar2,
337 p_value26 in varchar2,
338 p_value27 in varchar2,
339 p_value28 in varchar2,
340 p_value29 in varchar2,
341 p_value30 in varchar2,
342 p_edi_rec1 out NoCopy varchar2,
343 p_edi_rec2 out NoCopy varchar2,
344 p_edi_rec3 out NoCopy varchar2,
345 p_edi_rec4 out NoCopy varchar2,
346 p_edi_rec5 out NoCopy varchar2,
347 p_edi_rec6 out NoCopy varchar2,
348 p_edi_rec7 out NoCopy varchar2,
349 p_edi_rec8 out NoCopy varchar2,
350 p_edi_rec9 out NoCopy varchar2,
351 p_edi_rec10 out NoCopy varchar2,
352 p_edi_rec11 out NoCopy varchar2,
353 p_edi_rec12 out NoCopy varchar2,
354 p_edi_rec13 out NoCopy varchar2,
355 p_edi_rec14 out NoCopy varchar2,
356 p_edi_rec15 out NoCopy varchar2,
357 p_edi_rec16 out NoCopy varchar2,
358 p_edi_rec17 out NoCopy varchar2,
359 p_edi_rec18 out NoCopy varchar2,
360 p_edi_rec19 out NoCopy varchar2,
361 p_edi_rec20 out NoCopy varchar2,
362 p_edi_rec21 out NoCopy varchar2,
363 p_edi_rec22 out NoCopy varchar2,
364 p_edi_rec23 out NoCopy varchar2,
365 p_edi_rec24 out NoCopy varchar2,
366 p_edi_rec25 out NoCopy varchar2,
367 p_edi_rec26 out NoCopy varchar2,
368 p_edi_rec27 out NoCopy varchar2,
369 p_edi_rec28 out NoCopy varchar2,
370 p_edi_rec29 out NoCopy varchar2,
371 p_edi_rec30 out NoCopy varchar2) return number;
372
373 function get_footer(p_tax_year in varchar2,
374 p_record_count in varchar2,
375 p_error_count in varchar2,
376 p_missing_val in varchar2,
377 p_error_msg1 out NoCopy varchar2,
378 p_error_msg2 out NoCopy varchar2,
379 p_edi_rec1 out NoCopy varchar2,
380 p_edi_rec2 out NoCopy varchar2,
381 p_edi_rec3 out NoCopy varchar2) return number;
382
383 function count_occurrence(p_benefit_type in varchar2,
384 p_person_id in varchar2,
385 p_employer_ref in varchar2,
386 p_tax_year in varchar2,
387 p_pact_id in varchar2) return number;
388
389 function check_occurrence(p_benefit_type in varchar2,
390 p_tax_year in varchar2) return varchar2;
391
392 function get_benefit_name(p_benefit_type in varchar2,
393 p_tax_year in varchar2) return varchar2;
394
395 END PAY_GB_P11D_EDI_CONTROL;
[Home] [Help]