[Home] [Help]
PACKAGE: APPS.PAY_GB_EDI_P46_CAR
Source
1 PACKAGE pay_gb_edi_p46_car AUTHID CURRENT_USER AS
2 /* $Header: pygbp46c.pkh 120.8 2011/11/30 13:04:40 rajganga ship $ */
3
4 ----------------------------
5 -- PROCEDURE range_cursor --
6 ----------------------------
7 -- Procedure which returns a varchar2 defining a SQL Statement to select
8 -- all the people in the business group.
9 -- The archiver uses this cursor to split the people into chunks for parallel
10 -- processing.
11 PROCEDURE range_cursor (pactid IN NUMBER,
12 sqlstr out nocopy varchar2);
13
14 -- For bug 8986543
15 ----------------------------
16 -- PROCEDURE range_code_v3 for V3 --
17 ----------------------------
18 -- Procedure which returns a varchar2 defining a SQL Statement to select
19 -- all the people in the business group.
20 -- The archiver uses this cursor to split the people into chunks for parallel
21 -- processing.
22 PROCEDURE range_code_v3 (pactid IN NUMBER,
23 sqlstr out nocopy varchar2);
24
25 -- For bug 10095492
26 ----------------------------
27 -- PROCEDURE range_code_v4 for V4 --
28 ----------------------------
29 -- Procedure which returns a varchar2 defining a SQL Statement to select
30 -- all the people in the business group.
31 -- The archiver uses this cursor to split the people into chunks for parallel
32 -- processing.
33 PROCEDURE range_code_v4 (pactid IN NUMBER,
34 sqlstr out nocopy varchar2);
35
36 -- For bug 13400872
37 ----------------------------
38 -- PROCEDURE range_code_v5 for V5 --
39 ----------------------------
40 -- Procedure which returns a varchar2 defining a SQL Statement to select
41 -- all the people in the business group.
42 -- The archiver uses this cursor to split the people into chunks for parallel
43 -- processing.
44 PROCEDURE range_code_v5 (pactid IN NUMBER,
45 sqlstr out nocopy varchar2);
46
47 -- For bug 6652235
48 ------------------------------
49 -- PROCEDURE create_asg_act_v2 --
50 ------------------------------
51 PROCEDURE create_asg_act_v2(pactid IN NUMBER,
52 stperson IN NUMBER,
53 endperson IN NUMBER,
54 chunk IN NUMBER);
55
56 -- For bug 8986543
57 ------------------------------
58 -- PROCEDURE create_asg_act_v3 --
59 ------------------------------
60 PROCEDURE create_asg_act_v3(pactid IN NUMBER,
61 stperson IN NUMBER,
62 endperson IN NUMBER,
63 chunk IN NUMBER);
64
65 -- For bug 10095492
66 ------------------------------
67 -- PROCEDURE create_asg_act_v4 --
68 ------------------------------
69 PROCEDURE create_asg_act_v4(pactid IN NUMBER,
70 stperson IN NUMBER,
71 endperson IN NUMBER,
72 chunk IN NUMBER);
73
74 -- For bug 13400872
75 ------------------------------
76 -- PROCEDURE create_asg_act_v5 --
77 ------------------------------
78 PROCEDURE create_asg_act_v5(pactid IN NUMBER,
79 stperson IN NUMBER,
80 endperson IN NUMBER,
81 chunk IN NUMBER);
82
83 -- For bug 6652235
84 ------------------------------
85 -- PROCEDURE archive_code_v2 --
86 ------------------------------
87 PROCEDURE archive_code_v2(p_assactid IN NUMBER, p_effective_date IN DATE);
88
89
90 -- For bug 8986543
91 ------------------------------
92 -- PROCEDURE archive_code_v3 --
93 ------------------------------
94 PROCEDURE archive_code_v3(p_assactid IN NUMBER, p_effective_date IN DATE);
95
96
97 -- For bug 10095492
98 ------------------------------
99 -- PROCEDURE archive_code_v4 --
100 ------------------------------
101 PROCEDURE archive_code_v4(p_assactid IN NUMBER, p_effective_date IN DATE);
102
103
104 -- For bug 13400872
105 ------------------------------
106 -- PROCEDURE archive_code_v5 --
107 ------------------------------
108 PROCEDURE archive_code_v5(p_assactid IN NUMBER, p_effective_date IN DATE);
109
110
111 ------------------------------
112 -- Employee Details Cursor V5 --
113 ------------------------------
114 CURSOR c_emp_details_v5 IS
115 SELECT
116 'ASSIGNMENT_ACTION_ID=C',max(act_edi.assignment_action_id),
117 'PERSON_ID=P', pai.action_information1,
118 'ADDRESS_LINE1=P', nvl(max(action_information6), ' '),
119 'ADDRESS_LINE2=P', nvl(max(action_information7), ' '),
120 'ADDRESS_LINE3=P', nvl(max(action_information8), ' '),
121 'COUNTY=P', nvl(max(action_information10), ' '),
122 'FIRST_NAME=P', nvl(max(action_information3), ' '),
123 'LAST_NAME=P', max(action_information2),
124 'NI_NO=P', max(action_information5),
125 'POSTAL_CODE=P', ' ', -- bug 5169434 not to output post code
126 'TITLE=P', nvl(max(action_information4), ' '),
127 'TOWN_OR_CITY=P', nvl(max(action_information9), ' '),
128 'DOB=P', nvl(max(ACTION_INFORMATION12),' '),
129 'GENDER=P',nvl(max(ACTION_INFORMATION13), ' ')
130 FROM pay_assignment_actions act_edi,
131 ----------------------------------------------------------
132 -- Commented out following joins to fix performance issue
133 -- raised in bug 3374673.
134 -- pay_action_interlocks pail,
135 -- pay_assignment_actions act,
136 ----------------------------------------------------------
137 -- Bug 4059844 - SQL Repository Perf
138 -- pay_payroll_actions pact,
139 pay_action_information pai
140 WHERE act_edi.payroll_action_id = pay_magtape_generic.get_parameter_value
141 ('TRANSFER_PAYROLL_ACTION_ID')
142 AND act_edi.assignment_action_id = pai.action_context_id
143 AND pai.action_context_type = 'AAP'
144 AND pai.action_information_category = 'GB P46 CAR EDI EMPLOYEE DETAIL'
145 GROUP BY pai.action_information1
146 ORDER BY 14, 12;
147
148
149 -----------------------------
150 -- Employer Header Cursor V5 --
151 -----------------------------
152 CURSOR c_employer_header_v5 IS
153 SELECT 'SENDER_ID=P', upper(nvl(org_information11,' ')),
154 'RECEIVER_ID=P', 'HMRC',
155 'TEST_INDICATOR=P', decode(pay_gb_eoy_archive.get_parameter
156 (legislative_parameters,
157 'TEST'),'Y','1',' '),
158 'URGENT_MARKER=P', ' ',/*2008*/
159 'REQUEST_ID=P', fnd_number.number_to_canonical(pact.request_id),
160 'FORM_TYPE=P', '11',
161 'FORM_TYPE_MEANING=P', 'P46 Car EDI',
162 'TAX_DIST_NO=P', substr(hoi.org_information1,1,3),
163 'TAX_DIST_REF=P',
164 upper(substr(ltrim(substr(hoi.org_information1,4,11),'/') ,1,10)), /* Bug no 4086307 */
165 'TAX_DISTRICT=P', upper(nvl(substr(hoi.org_information2 ,1,40),' ')),
166 'EMPLOYERS_ADDRESS_LINE=P',
167 upper(nvl(substr(hoi.org_information4,1,60),' ')),
168 'EMPLOYERS_NAME=P', upper(nvl(substr(hoi.org_information3,1,36),' ')),
169 'TAX_YEAR=P', to_char(to_char(to_date(substr(pact.legislative_parameters,
170 instr(pact.legislative_parameters,'END_DATE=')+9, 10),'YYYY/MM/DD'),'YYYY')
171 + decode(sign(to_date(substr(pact.legislative_parameters,
172 instr(pact.legislative_parameters,'END_DATE=')+9, 10),'YYYY/MM/DD')
173 -to_date('06-04-'|| to_char(to_date(substr(pact.legislative_parameters,
174 instr(pact.legislative_parameters,'END_DATE=')+9, 10),'YYYY/MM/DD'),
175 'YYYY'),'DD-MM-YYYY')), -1,0,1))
176 FROM pay_payroll_actions pact,
177 hr_organization_information hoi
178 WHERE pact.payroll_action_id = pay_magtape_generic.get_parameter_value
179 ('TRANSFER_PAYROLL_ACTION_ID')
180 AND hoi.org_information_context = 'Tax Details References'
181 AND substr(pact.legislative_parameters,
182 instr(pact.legislative_parameters,'TAX_REF=') + 8,
183 instr(pact.legislative_parameters||' ',' ',
184 instr(pact.legislative_parameters,'TAX_REF=')+8)
185 - instr(pact.legislative_parameters,'TAX_REF=') - 8)
186 = hoi.org_information1
187 AND hoi.organization_id = pact.business_group_id;
188
189 --------------------------------
190 -- Allocation Details Cursor V5 --
191 --------------------------------
192 CURSOR c_allocation_details_v5 IS
193 SELECT distinct
194 'VEHICLE_ALLOCATION_ID=P', pai.action_information2,
195 'ACTION_FLAG=P', pai.action_information1,
196 'VEHICLE_ALLOCATION_EFF_START_DATE=P', pai.action_information3,
197 'VEHICLE_ALLOCATION_EFF_END_DATE=P', pai.action_information4,
198 'VEHICLE_REPOSITORY_ID=P', pai.action_information5,
199 'P46_REPLACED_VEHICLE_ALLOCATION_ID=P', nvl(action_information20, ' '),
200 'P46_REPLACED_VEHICLE_ALLOC_EFF_END_DATE=P', nvl(action_information21, ' '),
201 'P46_SECOND_CAR_FLAG=P', nvl(action_information15, ' '),
202 'CAR_LIST_PRICE=P', action_information6,
203 'PRICE_OF_ACCESSORIES=P', action_information7,
204 'EMPLOYEE_CAPITAL_CONTRIBUTIONS=P', action_information8,
205 'EMPLOYEE_PRIVATE_CONTRIBUTIONS=P', action_information9,
206 'REPLACED_CAR_MAKE_AND_MODEL=P', nvl(action_information18, ' '),
207 'ENGINE_SIZE_OF_CAR_REPLACED=P', nvl(action_information19, ' '),
208 'CAR_MAKE_AND_MODEL=P', action_information16,
209 'ENGINE_SIZE_OF_CAR=P', action_information17,
210 'FUEL_TYPE=P', action_information10,
211 'CO2_EMISSIONS_FIGURE=P', action_information11,
212 'FUEL_FOR_PRIVATE_USE_FLAG=P', action_information12,
213 'DATE_CAR_FIRST_REGISTERED=P', action_information13,
214 'DATE_CAR_FIRST_AVAILABLE=P', nvl(action_information14, ' '),
215 'INTERVAL_OF_PAYMENT=P', nvl(action_information22, 'Y') -- Added for the bug 9354919
216 FROM pay_assignment_actions act_edi,
217 per_all_assignments_f asg,
218 pay_action_information pai
219 WHERE act_edi.payroll_action_id = pay_magtape_generic.get_parameter_value
220 ('TRANSFER_PAYROLL_ACTION_ID')
221 AND asg.person_id = pay_magtape_generic.get_parameter_value(
222 'PERSON_ID')
223 AND act_edi.assignment_id = asg.assignment_id
224 AND act_edi.assignment_action_id = pai.action_context_id
225 AND pai.action_context_type = 'AAP'
226 AND pai.action_information_category = 'GB P46 CAR EDI ALLOCATION'
227 ORDER BY 2, 6, 4;
228
229 ------------------------------
230 -- Employee Details Cursor V4 --
231 ------------------------------
232 CURSOR c_emp_details_v4 IS
233 SELECT
234 'ASSIGNMENT_ACTION_ID=C',max(act_edi.assignment_action_id),
235 'PERSON_ID=P', pai.action_information1,
236 'ADDRESS_LINE1=P', nvl(max(action_information6), ' '),
237 'ADDRESS_LINE2=P', nvl(max(action_information7), ' '),
238 'ADDRESS_LINE3=P', nvl(max(action_information8), ' '),
239 'COUNTY=P', nvl(max(action_information10), ' '),
240 'FIRST_NAME=P', nvl(max(action_information3), ' '),
241 'LAST_NAME=P', max(action_information2),
242 'NI_NO=P', max(action_information5),
243 'POSTAL_CODE=P', ' ', -- bug 5169434 not to output post code
244 'TITLE=P', nvl(max(action_information4), ' '),
245 'TOWN_OR_CITY=P', nvl(max(action_information9), ' '),
246 'DOB=P', nvl(max(ACTION_INFORMATION12),' '),
247 'GENDER=P',nvl(max(ACTION_INFORMATION13), ' ')
248 FROM pay_assignment_actions act_edi,
249 ----------------------------------------------------------
250 -- Commented out following joins to fix performance issue
251 -- raised in bug 3374673.
252 -- pay_action_interlocks pail,
253 -- pay_assignment_actions act,
254 ----------------------------------------------------------
255 -- Bug 4059844 - SQL Repository Perf
256 -- pay_payroll_actions pact,
257 pay_action_information pai
258 WHERE act_edi.payroll_action_id = pay_magtape_generic.get_parameter_value
259 ('TRANSFER_PAYROLL_ACTION_ID')
260 AND act_edi.assignment_action_id = pai.action_context_id
261 AND pai.action_context_type = 'AAP'
262 AND pai.action_information_category = 'GB P46 CAR EDI EMPLOYEE DETAIL'
263 GROUP BY pai.action_information1
264 ORDER BY 14, 12;
265
266
267 -- For bug 8986543
268 -----------------------------
269 -- Employer Header Cursor V3 --
270 -----------------------------
271 CURSOR c_employer_header_v3 IS
272 SELECT 'SENDER_ID=P', upper(nvl(org_information11,' ')),
273 'RECEIVER_ID=P', 'HMRC',
274 'TEST_INDICATOR=P', decode(pay_gb_eoy_archive.get_parameter
275 (legislative_parameters,
276 'TEST'),'Y','1',' '),
277 'URGENT_MARKER=P', ' ',/*2008*/
278 'REQUEST_ID=P', fnd_number.number_to_canonical(pact.request_id),
279 'FORM_TYPE=P', '11',
280 'FORM_TYPE_MEANING=P', 'P46 Car EDI',
281 'TAX_DIST_NO=P', substr(hoi.org_information1,1,3),
282 'TAX_DIST_REF=P',
283 upper(substr(ltrim(substr(hoi.org_information1,4,11),'/') ,1,10)), /* Bug no 4086307 */
284 'TAX_DISTRICT=P', upper(nvl(substr(hoi.org_information2 ,1,40),' ')),
285 'EMPLOYERS_ADDRESS_LINE=P',
286 upper(nvl(substr(hoi.org_information4,1,60),' ')),
287 'EMPLOYERS_NAME=P', upper(nvl(substr(hoi.org_information3,1,36),' ')),
288 'TAX_YEAR=P', to_char(to_char(to_date(substr(pact.legislative_parameters,
289 instr(pact.legislative_parameters,'END_DATE=')+9, 10),'YYYY/MM/DD'),'YYYY')
290 + decode(sign(to_date(substr(pact.legislative_parameters,
291 instr(pact.legislative_parameters,'END_DATE=')+9, 10),'YYYY/MM/DD')
292 -to_date('06-04-'|| to_char(to_date(substr(pact.legislative_parameters,
293 instr(pact.legislative_parameters,'END_DATE=')+9, 10),'YYYY/MM/DD'),
294 'YYYY'),'DD-MM-YYYY')), -1,0,1))
295 FROM pay_payroll_actions pact,
296 hr_organization_information hoi
297 WHERE pact.payroll_action_id = pay_magtape_generic.get_parameter_value
301 instr(pact.legislative_parameters,'TAX_REF=') + 8,
298 ('TRANSFER_PAYROLL_ACTION_ID')
299 AND hoi.org_information_context = 'Tax Details References'
300 AND substr(pact.legislative_parameters,
302 instr(pact.legislative_parameters||' ',' ',
303 instr(pact.legislative_parameters,'TAX_REF=')+8)
304 - instr(pact.legislative_parameters,'TAX_REF=') - 8)
305 = hoi.org_information1
306 AND hoi.organization_id = pact.business_group_id;
307
308 -----------------------------
309 -- Employer Header Cursor V4 --
310 -----------------------------
311 CURSOR c_employer_header_v4 IS
312 SELECT 'SENDER_ID=P', upper(nvl(org_information11,' ')),
313 'RECEIVER_ID=P', 'HMRC',
314 'TEST_INDICATOR=P', decode(pay_gb_eoy_archive.get_parameter
315 (legislative_parameters,
316 'TEST'),'Y','1',' '),
317 'URGENT_MARKER=P', ' ',/*2008*/
318 'REQUEST_ID=P', fnd_number.number_to_canonical(pact.request_id),
319 'FORM_TYPE=P', '11',
320 'FORM_TYPE_MEANING=P', 'P46 Car EDI',
321 'TAX_DIST_NO=P', substr(hoi.org_information1,1,3),
322 'TAX_DIST_REF=P',
323 upper(substr(ltrim(substr(hoi.org_information1,4,11),'/') ,1,10)), /* Bug no 4086307 */
324 'TAX_DISTRICT=P', upper(nvl(substr(hoi.org_information2 ,1,40),' ')),
325 'EMPLOYERS_ADDRESS_LINE=P',
326 upper(nvl(substr(hoi.org_information4,1,60),' ')),
327 'EMPLOYERS_NAME=P', upper(nvl(substr(hoi.org_information3,1,36),' ')),
328 'TAX_YEAR=P', to_char(to_char(to_date(substr(pact.legislative_parameters,
329 instr(pact.legislative_parameters,'END_DATE=')+9, 10),'YYYY/MM/DD'),'YYYY')
330 + decode(sign(to_date(substr(pact.legislative_parameters,
331 instr(pact.legislative_parameters,'END_DATE=')+9, 10),'YYYY/MM/DD')
332 -to_date('06-04-'|| to_char(to_date(substr(pact.legislative_parameters,
333 instr(pact.legislative_parameters,'END_DATE=')+9, 10),'YYYY/MM/DD'),
334 'YYYY'),'DD-MM-YYYY')), -1,0,1))
335 FROM pay_payroll_actions pact,
336 hr_organization_information hoi
337 WHERE pact.payroll_action_id = pay_magtape_generic.get_parameter_value
338 ('TRANSFER_PAYROLL_ACTION_ID')
339 AND hoi.org_information_context = 'Tax Details References'
340 AND substr(pact.legislative_parameters,
341 instr(pact.legislative_parameters,'TAX_REF=') + 8,
342 instr(pact.legislative_parameters||' ',' ',
343 instr(pact.legislative_parameters,'TAX_REF=')+8)
344 - instr(pact.legislative_parameters,'TAX_REF=') - 8)
345 = hoi.org_information1
346 AND hoi.organization_id = pact.business_group_id;
347
348 --------------------------------
349 -- Allocation Details Cursor V4 --
350 --------------------------------
351 CURSOR c_allocation_details_v4 IS
352 SELECT distinct
353 'VEHICLE_ALLOCATION_ID=P', pai.action_information2,
354 'ACTION_FLAG=P', pai.action_information1,
355 'VEHICLE_ALLOCATION_EFF_START_DATE=P', pai.action_information3,
356 'VEHICLE_ALLOCATION_EFF_END_DATE=P', pai.action_information4,
357 'VEHICLE_REPOSITORY_ID=P', pai.action_information5,
358 'P46_REPLACED_VEHICLE_ALLOCATION_ID=P', nvl(action_information20, ' '),
359 'P46_REPLACED_VEHICLE_ALLOC_EFF_END_DATE=P', nvl(action_information21, ' '),
360 'P46_SECOND_CAR_FLAG=P', nvl(action_information15, ' '),
361 'CAR_LIST_PRICE=P', action_information6,
362 'PRICE_OF_ACCESSORIES=P', action_information7,
363 'EMPLOYEE_CAPITAL_CONTRIBUTIONS=P', action_information8,
364 'EMPLOYEE_PRIVATE_CONTRIBUTIONS=P', action_information9,
365 'REPLACED_CAR_MAKE_AND_MODEL=P', nvl(action_information18, ' '),
366 'ENGINE_SIZE_OF_CAR_REPLACED=P', nvl(action_information19, ' '),
367 'CAR_MAKE_AND_MODEL=P', action_information16,
368 'ENGINE_SIZE_OF_CAR=P', action_information17,
369 'FUEL_TYPE=P', action_information10,
370 'CO2_EMISSIONS_FIGURE=P', action_information11,
371 'FUEL_FOR_PRIVATE_USE_FLAG=P', action_information12,
372 'DATE_CAR_FIRST_REGISTERED=P', action_information13,
373 'DATE_CAR_FIRST_AVAILABLE=P', nvl(action_information14, ' '),
374 'INTERVAL_OF_PAYMENT=P', nvl(action_information22, 'Y') -- Added for the bug 9354919
375 FROM pay_assignment_actions act_edi,
376 per_all_assignments_f asg,
377 pay_action_information pai
378 WHERE act_edi.payroll_action_id = pay_magtape_generic.get_parameter_value
382 AND act_edi.assignment_id = asg.assignment_id
379 ('TRANSFER_PAYROLL_ACTION_ID')
380 AND asg.person_id = pay_magtape_generic.get_parameter_value(
381 'PERSON_ID')
383 AND act_edi.assignment_action_id = pai.action_context_id
384 AND pai.action_context_type = 'AAP'
385 AND pai.action_information_category = 'GB P46 CAR EDI ALLOCATION'
386 ORDER BY 2, 6, 4;
387
388 ------------------------------
389 -- Employee Details Cursor V3 --
390 ------------------------------
391 CURSOR c_emp_details_v3 IS
392 SELECT
393 'ASSIGNMENT_ACTION_ID=C',max(act_edi.assignment_action_id),
394 'PERSON_ID=P', pai.action_information1,
395 'ADDRESS_LINE1=P', nvl(max(action_information6), ' '),
396 'ADDRESS_LINE2=P', nvl(max(action_information7), ' '),
397 'ADDRESS_LINE3=P', nvl(max(action_information8), ' '),
398 'COUNTY=P', nvl(max(action_information10), ' '),
399 'FIRST_NAME=P', nvl(max(action_information3), ' '),
400 'LAST_NAME=P', max(action_information2),
401 'NI_NO=P', max(action_information5),
402 'POSTAL_CODE=P', ' ', -- bug 5169434 not to output post code
403 'TITLE=P', nvl(max(action_information4), ' '),
404 'TOWN_OR_CITY=P', nvl(max(action_information9), ' '),
405 'DOB=P', nvl(max(ACTION_INFORMATION12),' '),
406 'GENDER=P',nvl(max(ACTION_INFORMATION13), ' ')
407 FROM pay_assignment_actions act_edi,
408 ----------------------------------------------------------
409 -- Commented out following joins to fix performance issue
410 -- raised in bug 3374673.
411 -- pay_action_interlocks pail,
412 -- pay_assignment_actions act,
413 ----------------------------------------------------------
414 -- Bug 4059844 - SQL Repository Perf
415 -- pay_payroll_actions pact,
416 pay_action_information pai
417 WHERE act_edi.payroll_action_id = pay_magtape_generic.get_parameter_value
418 ('TRANSFER_PAYROLL_ACTION_ID')
419 AND act_edi.assignment_action_id = pai.action_context_id
420 AND pai.action_context_type = 'AAP'
421 AND pai.action_information_category = 'GB P46 CAR EDI EMPLOYEE DETAIL'
422 GROUP BY pai.action_information1
423 ORDER BY 14, 12;
424
425 --------------------------------
426 -- Allocation Details Cursor V3 --
427 --------------------------------
428 CURSOR c_allocation_details_v3 IS
429 SELECT distinct
430 'VEHICLE_ALLOCATION_ID=P', pai.action_information2,
431 'ACTION_FLAG=P', pai.action_information1,
432 'VEHICLE_ALLOCATION_EFF_START_DATE=P', pai.action_information3,
433 'VEHICLE_ALLOCATION_EFF_END_DATE=P', pai.action_information4,
434 'VEHICLE_REPOSITORY_ID=P', pai.action_information5,
435 'P46_SECOND_CAR_FLAG=P', nvl(action_information15, ' '),
436 'CAR_LIST_PRICE=P', action_information6,
437 'PRICE_OF_ACCESSORIES=P', action_information7,
438 'EMPLOYEE_CAPITAL_CONTRIBUTIONS=P', action_information8,
439 'EMPLOYEE_PRIVATE_CONTRIBUTIONS=P', action_information9,
440 'CAR_MAKE_AND_MODEL=P', action_information16,
441 'ENGINE_SIZE_OF_CAR=P', action_information17,
442 'FUEL_TYPE=P', action_information10,
443 'CO2_EMISSIONS_FIGURE=P', action_information11,
444 'FUEL_FOR_PRIVATE_USE_FLAG=P', action_information12,
445 'DATE_CAR_FIRST_REGISTERED=P', action_information13,
446 'DATE_CAR_FIRST_AVAILABLE=P', nvl(action_information14, ' '),
447 'INTERVAL_OF_PAYMENT=P', nvl(action_information22, 'Y') -- Added for the bug 9354919
448 FROM pay_assignment_actions act_edi,
449 per_all_assignments_f asg,
450 pay_action_information pai
451 WHERE act_edi.payroll_action_id = pay_magtape_generic.get_parameter_value
452 ('TRANSFER_PAYROLL_ACTION_ID')
453 AND asg.person_id = pay_magtape_generic.get_parameter_value(
454 'PERSON_ID')
455 AND act_edi.assignment_id = asg.assignment_id
456 AND act_edi.assignment_action_id = pai.action_context_id
457 AND pai.action_context_type = 'AAP'
458 AND pai.action_information_category = 'GB P46 CAR EDI ALLOCATION'
459 ORDER BY 2,4;
460
461 --
462
463 level_cnt NUMBER;
464
465 END pay_gb_edi_p46_car;