[Home] [Help]
PACKAGE: APPS.PAY_SG_CPFLINE
Source
1 package pay_sg_cpfline as
2 /* $Header: pysgcpfl.pkh 120.0 2005/05/29 08:43:41 appldev noship $ */
3 level_cnt number;
4 --------------------------------------------------------------------
5 -- These are PUBLIC procedures are required by the Archive process.
6 -- Their names are stored in PAY_REPORT_FORMAT_MAPPINGS_F so that
7 -- the archive process knows what code to execute for each step of
8 -- the archive.
9 --------------------------------------------------------------------
10 procedure range_code
11 ( p_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
12 p_sql out nocopy varchar2);
13 --
14 procedure assignment_action_code
15 ( p_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
16 p_start_person_id in per_all_people_f.person_id%type,
17 p_end_person_id in per_all_people_f.person_id%type,
18 p_chunk in number );
19 --
20 procedure initialization_code
21 ( p_payroll_action_id in pay_payroll_actions.payroll_action_id%type);
22 --
23 procedure archive_code
24 ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
25 p_effective_date in date);
26 --
27 procedure deinit_code
28 ( p_payroll_action_id in pay_payroll_actions.payroll_action_id%type ) ;
29 --
30 --------------------------------------------------------------------------
31 -- company_identification cursor
32 -- Bug#3501950 - Added CPF_Interest and FWL_Interest parameters
33 --------------------------------------------------------------------------
34 cursor company_identification is
35 select 'BUSINESS_GROUP_ID=C',
36 pay_magtape_generic.get_parameter_value('BUSINESS_GROUP_ID') business_group_id,
37 'TAX_UNIT_ID=C',
38 pay_magtape_generic.get_parameter_value('LEGAL_ENTITY_ID') tax_unit_id,
39 'DOCUMENT_DATE=P',
40 to_char(sysdate,'YYYYMMDD') document_date,
41 'LEGAL_ENTITY=P',
42 hou.name legal_entity,
43 'MONTH=P',
44 pay_magtape_generic.get_parameter_value('MONTH') month,
45 'ADVICE_CODE=P',
46 '01' advice_code,
47 'AV1_AMOUNT=P',
48 to_char(pay_sg_cpfline_balances.stat_type_amount
49 ( pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID'), 'AV1')) av1_amount,
50 'AV3_AMOUNT=P',
51 to_char(pay_sg_cpfline_balances.stat_type_amount
52 ( pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID'), 'AV3')) av3_amount,
53 'AV4_AMOUNT=P',
54 to_char(pay_sg_cpfline_balances.stat_type_amount
55 ( pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID'), 'AV4')) av4_amount,
56 'AV5_AMOUNT=P',
57 to_char(pay_sg_cpfline_balances.stat_type_amount
58 ( pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID'), 'AV5')) av5_amount,
59 'AV7_AMOUNT=P',
60 to_char(pay_sg_cpfline_balances.stat_type_amount
61 ( pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID'), 'AV7')) av7_amount,
62 'AVA_AMOUNT=P',
63 to_char(pay_sg_cpfline_balances.stat_type_amount
64 ( pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID'), 'AVA')) ava_amount,
65 'AVE_AMOUNT=P',
66 to_char(pay_sg_cpfline_balances.stat_type_amount
67 ( pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID'), 'AVE')) ave_amount,
68 'AVG_AMOUNT=P',
69 to_char(pay_sg_cpfline_balances.stat_type_amount
70 ( pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID'), 'AVG')) avg_amount,
71 'MUS_COUNT=P',
72 to_char(pay_sg_cpfline_balances.stat_type_count
73 ( pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID'), 'MUS')) mus_count,
74 'SHA_COUNT=P',
75 to_char(pay_sg_cpfline_balances.stat_type_count
76 ( pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID'), 'SHA')) sha_count,
77 'SIN_COUNT=P',
78 to_char(pay_sg_cpfline_balances.stat_type_count
79 ( pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID'), 'SIN')) sin_count,
80 'CDA_COUNT=P',
81 to_char(pay_sg_cpfline_balances.stat_type_count
82 ( pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID'), 'CDA')) cda_count,
83 'ECF_COUNT=P',
84 to_char(pay_sg_cpfline_balances.stat_type_count
85 ( pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID'), 'ECF')) ecf_count,
86 'TOTAL_CONTRIBUTION=P',
87 to_char(pay_sg_cpfline_balances.stat_type_amount
88 ( pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID'), 'TOT')) total_contribution,
89 'CPF_INTEREST=P',
90 pay_sg_cpfline_balances.get_cpf_interest
91 (pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')) cpf_interest,
92 'FWL_INTEREST=P',
93 pay_sg_cpfline_balances.get_fwl_interest
94 (pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')) fwl_interest
95 from hr_organization_units hou,
96 hr_organization_information hoi1,
97 hr_organization_information hoi2
98 where hou.organization_id = pay_magtape_generic.get_parameter_value('LEGAL_ENTITY_ID')
99 and hou.organization_id = hoi1.organization_id(+)
100 and hou.organization_id = hoi2.organization_id
101 and hoi1.org_information_context = 'SG_LEGAL_ENTITY'
102 and hoi2.org_information_context = 'CLASS'
103 and hoi2.org_information1 = 'HR_LEGAL'
104 and hoi2.org_information2 = 'Y' ;
105 ----------------------------------
106 -- Existing_Employees cursor
107 -- Bug#3153476 Modified the 'sort parameter'.
108 -- Now cursor passes SORT=1 for employee with permit type 'WP' or 'EP'.
109 -- Bug#3298317 Added one more parameter 'permit_type' to function call 'get_balance_value'.
110 -- Rounding the cpf balannces so there are no rounding discrepancies
111 -- between AV1 and sum of AV2 in the file.
112 -- Bug: 3619297 - Modified the inner queries added checks on action_context_type.
113 ----------------------------------
114 cursor existing_employees is
115 select 'DEPARTMENT=P',
116 pai.action_information22 department,
117 'ASSIGNMENT_ACTION_ID=C',
118 paa.assignment_action_id,
119 'TAX_UNIT_ID=C',
120 paa.tax_unit_id,
121 'DATE_EARNED=C',
122 pai.action_information20 date_earned,
123 'ASSIGNMENT_ID=C',
124 paa.assignment_id,
125 'SORT=P',
126 decode(pai.action_information1,null,'1',(decode(pai.action_information21,'WP','1',(decode(pai.action_information21,'EP','1',(decode(pai.action_information21,'SP','1','0'))))))) sort,
127 pai.action_information3 hire_date,
128 'VOL_CPF_LIAB=P',
129 to_char(to_number(pai.action_information4))||'#'|| to_char(to_number(pai.action_information4)) vol_cpf_liab,
130 'CPF_LIAB=P',
131 to_char(to_number(pai.action_information6))||'#'|| to_char(to_number(pai.action_information6)) cpf_liab,
132 'VOL_CPF_WITHHELD=P',
133 to_char(to_number(pai.action_information5))||'#'|| to_char(to_number(pai.action_information5)) vol_cpf_withheld,
134 'CPF_WITHHELD=P',
135 to_char(to_number(pai.action_information7))||'#'|| to_char(to_number(pai.action_information7)) cpf_withheld,
136 'MBMF_WITHHELD=P',
137 pai.action_information8||'#'||pai.action_information8 mbmf_withheld,
138 'SINDA_WITHHELD=P',
139 pai.action_information9||'#'||pai.action_information9 sinda_withheld,
140 'CDAC_WITHHELD=P',
141 pai.action_information10||'#'||pai.action_information10 cdac_withheld,
142 'ECF_WITHHELD=P',
143 pai.action_information11||'#'||pai.action_information11 ecf_withheld,
144 'CPF_ORD_ELIG_COMP=P',
145 pai.action_information12||'#'||pai.action_information12 cpf_ord_elig_comp,
146 'CPF_ADD_ELIG_COMP=P',
147 pai.action_information13||'#'||pai.action_information13 cpf_add_elig_comp ,
148 'LEGAL_NAME=P',
149 pai.action_information17||'#'||pai.action_information17 legal_name,
150 'EMPLOYEE_NUMBER=P',
151 pai.action_information18||'#'||pai.action_information18 employee_number,
152 'EMP_TERMINATION_DATE=P',
153 nvl(to_char(fnd_date.canonical_to_date(pai.action_information19),'dd/mm/yyyy'),'01/01/1900') emp_termination_date
154 from pay_payroll_actions ppa,
155 pay_assignment_actions paa,
156 pay_action_information pai
157 where ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
158 and ppa.payroll_action_id = paa.payroll_action_id
159 and paa.assignment_action_id = pai.action_context_id
160 and pai.action_information_category = 'SG CPF DETAILS'
161 and pai.action_context_type = 'AAC'
162 and pai.action_information2 = 'EE'
163 and not exists ( select 1
164 from pay_action_information pai_dup,
165 pay_assignment_actions paa_dup
166 where pai.action_information_category = pai_dup.action_information_category
167 and pai.rowid <> pai_dup.rowid
168 and paa_dup.payroll_action_id = ppa.payroll_action_id
169 and paa_dup.assignment_action_id = pai_dup.action_context_id
170 and pai.action_information1 = pai_dup.action_information1
171 and pai_dup.action_context_type = 'AAC' )
172 union all
173 select 'DEPARTMENT=P',
174 pai.action_information22 department,
175 'ASSIGNMENT_ACTION_ID=C',
176 paa.assignment_action_id,
177 'TAX_UNIT_ID=C',
178 paa.tax_unit_id,
179 'DATE_EARNED=C',
180 pai.action_information20 date_earned,
181 'ASSIGNMENT_ID=C',
182 paa.assignment_id,
183 'SORT=P',
184 decode(pai.action_information1,null,'1',(decode(pai.action_information21,'WP','1',(decode(pai.action_information21,'EP','1',(decode(pai.action_information21,'SP','1','0'))))))) sort,
185 pai.action_information3 hire_date,
186 'VOL_CPF_LIAB=P',
187 pay_sg_cpfline_balances.get_balance_value( 'EE',
188 paa.assignment_id,
189 nvl(pai.action_information1,pai.source_id),
190 pai.action_information22,
191 paa.assignment_action_id,
192 paa.tax_unit_id,
193 'Voluntary CPF Liability',
194 pai.action_information4,
195 ppa.payroll_action_id,
196 pai.action_information21
197 ) vol_cpf_liab,
198 'CPF_LIAB=P',
199 pay_sg_cpfline_balances.get_balance_value( 'EE',
200 paa.assignment_id,
201 nvl(pai.action_information1,pai.source_id),
202 pai.action_information22,
203 paa.assignment_action_id,
204 paa.tax_unit_id,
205 'CPF Liability',
206 pai.action_information6,
207 ppa.payroll_action_id,
208 pai.action_information21
209 ) cpf_liab,
210 'VOL_CPF_WITHHELD=P',
211 pay_sg_cpfline_balances.get_balance_value( 'EE',
212 paa.assignment_id,
213 nvl(pai.action_information1,pai.source_id),
214 pai.action_information22,
215 paa.assignment_action_id,
216 paa.tax_unit_id,
217 'Voluntary CPF Withheld',
218 pai.action_information5,
219 ppa.payroll_action_id,
220 pai.action_information21
221 ) vol_cpf_withheld,
222 'CPF_WITHHELD=P',
223 pay_sg_cpfline_balances.get_balance_value( 'EE',
224 paa.assignment_id,
225 nvl(pai.action_information1,pai.source_id),
226 pai.action_information22,
227 paa.assignment_action_id,
228 paa.tax_unit_id,
229 'CPF Withheld',
230 pai.action_information7,
231 ppa.payroll_action_id,
232 pai.action_information21
233 ) cpf_withheld,
234 'MBMF_WITHHELD=P',
235 pay_sg_cpfline_balances.get_balance_value( 'EE',
236 paa.assignment_id,
237 nvl(pai.action_information1,pai.source_id),
238 pai.action_information22,
239 paa.assignment_action_id,
240 paa.tax_unit_id,
241 'MBMF Withheld',
242 pai.action_information8,
243 ppa.payroll_action_id,
244 pai.action_information21
245 ) mbmf_withheld,
246 'SINDA_WITHHELD=P',
247 pay_sg_cpfline_balances.get_balance_value( 'EE',
248 paa.assignment_id,
249 nvl(pai.action_information1,pai.source_id),
250 pai.action_information22,
251 paa.assignment_action_id,
252 paa.tax_unit_id,
253 'SINDA Withheld',
254 pai.action_information9,
255 ppa.payroll_action_id,
256 pai.action_information21
257 ) sinda_withheld,
258 'CDAC_WITHHELD=P',
259 pay_sg_cpfline_balances.get_balance_value( 'EE',
260 paa.assignment_id,
261 nvl(pai.action_information1,pai.source_id),
262 pai.action_information22,
263 paa.assignment_action_id,
264 paa.tax_unit_id,
265 'CDAC Withheld',
266 pai.action_information10,
267 ppa.payroll_action_id,
268 pai.action_information21
269 ) cdac_withheld,
270 'ECF_WITHHELD=P',
271 pay_sg_cpfline_balances.get_balance_value( 'EE',
272 paa.assignment_id,
273 nvl(pai.action_information1,pai.source_id),
274 pai.action_information22,
275 paa.assignment_action_id,
276 paa.tax_unit_id,
277 'ECF Withheld',
278 pai.action_information11,
279 ppa.payroll_action_id,
280 pai.action_information21
281 ) ecf_withheld,
282 'CPF_ORD_ELIG_COMP=P',
283 pay_sg_cpfline_balances.get_balance_value( 'EE',
284 paa.assignment_id,
285 nvl(pai.action_information1,pai.source_id),
286 pai.action_information22,
287 paa.assignment_action_id,
288 paa.tax_unit_id,
289 'CPF Ordinary Earnings Eligible Comp',
290 pai.action_information12,
291 ppa.payroll_action_id,
292 pai.action_information21
293 ) cpf_ord_elig_comp,
294 'CPF_ADD_ELIG_COMP=P',
295 pay_sg_cpfline_balances.get_balance_value( 'EE',
296 paa.assignment_id,
297 nvl(pai.action_information1,pai.source_id),
298 pai.action_information22,
299 paa.assignment_action_id,
300 paa.tax_unit_id,
301 'CPF Additional Earnings Eligible Comp',
302 pai.action_information13,
303 ppa.payroll_action_id,
304 pai.action_information21
305 ) cpf_add_elig_comp ,
306 'LEGAL_NAME=P',
307 pay_sg_cpfline_balances.get_balance_value( 'EE',
308 paa.assignment_id,
309 nvl(pai.action_information1,pai.source_id),
310 pai.action_information22,
311 paa.assignment_action_id,
312 paa.tax_unit_id,
313 'Legal_Name',
314 pai.action_information17,
315 ppa.payroll_action_id,
316 pai.action_information21
317 ) legal_name,
318 'EMPLOYEE_NUMBER=P',
319 pay_sg_cpfline_balances.get_balance_value( 'EE',
320 paa.assignment_id,
321 nvl(pai.action_information1,pai.source_id),
322 pai.action_information22,
323 paa.assignment_action_id,
324 paa.tax_unit_id,
325 'Employee_Number',
326 pai.action_information18,
327 ppa.payroll_action_id,
328 pai.action_information21
329 ) employee_number,
330 'EMP_TERMINATION_DATE=P',
331 pay_sg_cpfline_balances.get_balance_value( 'EE',
332 paa.assignment_id,
333 nvl(pai.action_information1,pai.source_id),
334 pai.action_information22,
335 paa.assignment_action_id,
336 paa.tax_unit_id,
337 'Emp_Term_Date',
338 pai.action_information19,
339 ppa.payroll_action_id,
340 pai.action_information21
341 ) emp_termination_date
342 from pay_payroll_actions ppa,
343 pay_assignment_actions paa,
344 pay_action_information pai
345 where ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
346 and ppa.payroll_action_id = paa.payroll_action_id
347 and paa.assignment_action_id = pai.action_context_id
348 and pai.action_information_category = 'SG CPF DETAILS'
349 and pai.action_context_type = 'AAC'
350 and pai.action_information2 = 'EE'
351 and exists ( select 1
352 from pay_action_information pai_dup,
353 pay_assignment_actions paa_dup
354 where pai.action_information_category = pai_dup.action_information_category
355 and pai.rowid <> pai_dup.rowid
356 and paa_dup.payroll_action_id = ppa.payroll_action_id
357 and paa_dup.assignment_action_id = pai_dup.action_context_id
358 and pai.action_information1 = pai_dup.action_information1
359 and pai_dup.action_context_type = 'AAC' )
360 order by sort asc,department asc, hire_date desc;
361 ----------------------------------
362 -- New_Employees cursor
363 -- Bug#3153476 Modified the 'sort parameter'.
364 -- Now cursor passes SORT=1 for employee with permit type 'WP' or 'EP'.
365 -- Bug#3298317 Added one more parameter 'permit_type' to function call 'get_balance_value'.
366 -- Rounding the cpf balannces so there are no rounding discrepancies
367 -- between AV1 and sum of AV2 in the file.
368 -- Bug: 3619297 - Modified the inner queries added checks on action_context_type.
369 ----------------------------------
373 'ASSIGNMENT_ACTION_ID=C',
370 cursor new_employees is
371 select 'DEPARTMENT=P',
372 pai.action_information22 department,
374 paa.assignment_action_id,
375 'TAX_UNIT_ID=C',
376 paa.tax_unit_id,
377 'DATE_EARNED=C',
378 pai.action_information20 date_earned,
379 'ASSIGNMENT_ID=C',
380 paa.assignment_id,
381 'SORT=P',
382 decode(pai.action_information1,null,'1',(decode(pai.action_information21,'WP','1',(decode(pai.action_information21,'EP','1',(decode(pai.action_information21,'SP','1','0'))))))) sort,
383 pai.action_information3 hire_date,
384 'VOL_CPF_LIAB=P',
385 to_char(to_number(pai.action_information4))||'#'|| to_char(to_number(pai.action_information4)) vol_cpf_liab,
386 'CPF_LIAB=P',
387 to_char(to_number(pai.action_information6))||'#'|| to_char(to_number(pai.action_information6)) cpf_liab,
388 'VOL_CPF_WITHHELD=P',
389 to_char(to_number(pai.action_information5))||'#'|| to_char(to_number(pai.action_information5)) vol_cpf_withheld,
390 'CPF_WITHHELD=P',
391 to_char(to_number(pai.action_information7))||'#'|| to_char(to_number(pai.action_information7)) cpf_withheld,
392 'MBMF_WITHHELD=P',
393 pai.action_information8||'#'||pai.action_information8 mbmf_withheld,
394 'SINDA_WITHHELD=P',
395 pai.action_information9||'#'||pai.action_information9 sinda_withheld,
396 'CDAC_WITHHELD=P',
397 pai.action_information10||'#'||pai.action_information10 cdac_withheld,
398 'ECF_WITHHELD=P',
399 pai.action_information11||'#'||pai.action_information11 ecf_withheld,
400 'CPF_ORD_ELIG_COMP=P',
401 pai.action_information12||'#'||pai.action_information12 cpf_ord_elig_comp,
402 'CPF_ADD_ELIG_COMP=P',
403 pai.action_information13||'#'||pai.action_information13 cpf_add_elig_comp ,
404 'LEGAL_NAME=P',
405 pai.action_information17||'#'||pai.action_information17 legal_name,
406 'EMPLOYEE_NUMBER=P',
407 pai.action_information18||'#'||pai.action_information18 employee_number,
408 'EMP_TERMINATION_DATE=P',
409 nvl(to_char(fnd_date.canonical_to_date(pai.action_information19),'dd/mm/yyyy'),'01/01/1900') emp_termination_date
410 from pay_payroll_actions ppa,
411 pay_assignment_actions paa,
412 pay_action_information pai
413 where ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
414 and ppa.payroll_action_id = paa.payroll_action_id
415 and paa.assignment_action_id = pai.action_context_id
416 and pai.action_information_category = 'SG CPF DETAILS'
417 and pai.action_context_type = 'AAC'
418 and pai.action_information2 = 'NEW'
419 and not exists ( select 1
420 from pay_action_information pai_dup,
421 pay_assignment_actions paa_dup
422 where pai.action_information_category = pai_dup.action_information_category
423 and pai.rowid <> pai_dup.rowid
424 and paa_dup.payroll_action_id = ppa.payroll_action_id
425 and paa_dup.assignment_action_id = pai_dup.action_context_id
426 and pai.action_information1 = pai_dup.action_information1
427 and pai_dup.action_context_type = 'AAC' )
428 union all
429 select 'DEPARTMENT=P',
430 pai.action_information22 department,
431 'ASSIGNMENT_ACTION_ID=C',
432 paa.assignment_action_id,
433 'TAX_UNIT_ID=C',
434 paa.tax_unit_id,
435 'DATE_EARNED=C',
436 pai.action_information20 date_earned,
437 'ASSIGNMENT_ID=C',
438 paa.assignment_id,
439 'SORT=P',
440 decode(pai.action_information1,null,'1',(decode(pai.action_information21,'WP','1',(decode(pai.action_information21,'EP','1',(decode(pai.action_information21,'SP','1','0'))))))) sort,
441 pai.action_information3 hire_date,
442 'VOL_CPF_LIAB=P',
443 pay_sg_cpfline_balances.get_balance_value( 'NEW',
444 paa.assignment_id,
445 nvl(pai.action_information1,pai.source_id),
446 pai.action_information22,
447 paa.assignment_action_id,
448 paa.tax_unit_id,
449 'Voluntary CPF Liability',
450 pai.action_information4,
451 ppa.payroll_action_id,
452 pai.action_information21
453 ) vol_cpf_liab,
454 'CPF_LIAB=P',
455 pay_sg_cpfline_balances.get_balance_value( 'NEW',
456 paa.assignment_id,
457 nvl(pai.action_information1,pai.source_id),
458 pai.action_information22,
462 pai.action_information6,
459 paa.assignment_action_id,
460 paa.tax_unit_id,
461 'CPF Liability',
463 ppa.payroll_action_id,
464 pai.action_information21
465 ) cpf_liab,
466 'VOL_CPF_WITHHELD=P',
467 pay_sg_cpfline_balances.get_balance_value( 'NEW',
468 paa.assignment_id,
469 nvl(pai.action_information1,pai.source_id),
470 pai.action_information22,
471 paa.assignment_action_id,
472 paa.tax_unit_id,
473 'Voluntary CPF Withheld',
474 pai.action_information5,
475 ppa.payroll_action_id,
476 pai.action_information21
477 ) vol_cpf_withheld,
478 'CPF_WITHHELD=P',
479 pay_sg_cpfline_balances.get_balance_value( 'NEW',
480 paa.assignment_id,
481 nvl(pai.action_information1,pai.source_id),
482 pai.action_information22,
483 paa.assignment_action_id,
484 paa.tax_unit_id,
485 'CPF Withheld',
486 pai.action_information7,
487 ppa.payroll_action_id,
488 pai.action_information21
489 ) cpf_withheld,
490 'MBMF_WITHHELD=P',
491 pay_sg_cpfline_balances.get_balance_value( 'NEW',
492 paa.assignment_id,
493 nvl(pai.action_information1,pai.source_id),
494 pai.action_information22,
495 paa.assignment_action_id,
496 paa.tax_unit_id,
497 'MBMF Withheld',
498 pai.action_information8,
499 ppa.payroll_action_id,
500 pai.action_information21
501 ) mbmf_withheld,
502 'SINDA_WITHHELD=P',
503 pay_sg_cpfline_balances.get_balance_value( 'NEW',
504 paa.assignment_id,
505 nvl(pai.action_information1,pai.source_id),
506 pai.action_information22,
507 paa.assignment_action_id,
508 paa.tax_unit_id,
509 'SINDA Withheld',
510 pai.action_information9,
511 ppa.payroll_action_id,
512 pai.action_information21
513 ) sinda_withheld,
514 'CDAC_WITHHELD=P',
515 pay_sg_cpfline_balances.get_balance_value( 'NEW',
516 paa.assignment_id,
517 nvl(pai.action_information1,pai.source_id),
518 pai.action_information22,
519 paa.assignment_action_id,
520 paa.tax_unit_id,
521 'CDAC Withheld',
522 pai.action_information10,
523 ppa.payroll_action_id,
524 pai.action_information21
525 ) cdac_withheld,
526 'ECF_WITHHELD=P',
527 pay_sg_cpfline_balances.get_balance_value( 'NEW',
528 paa.assignment_id,
529 nvl(pai.action_information1,pai.source_id),
530 pai.action_information22,
531 paa.assignment_action_id,
532 paa.tax_unit_id,
533 'ECF Withheld',
534 pai.action_information11,
535 ppa.payroll_action_id,
536 pai.action_information21
537 ) ecf_withheld,
538 'CPF_ORD_ELIG_COMP=P',
539 pay_sg_cpfline_balances.get_balance_value( 'NEW',
540 paa.assignment_id,
541 nvl(pai.action_information1,pai.source_id),
542 pai.action_information22,
543 paa.assignment_action_id,
544 paa.tax_unit_id,
545 'CPF Ordinary Earnings Eligible Comp',
546 pai.action_information12,
547 ppa.payroll_action_id,
548 pai.action_information21
549 ) cpf_ord_elig_comp,
550 'CPF_ADD_ELIG_COMP=P',
551 pay_sg_cpfline_balances.get_balance_value( 'NEW',
552 paa.assignment_id,
553 nvl(pai.action_information1,pai.source_id),
554 pai.action_information22,
555 paa.assignment_action_id,
556 paa.tax_unit_id,
557 'CPF Additional Earnings Eligible Comp',
558 pai.action_information13,
559 ppa.payroll_action_id,
560 pai.action_information21
561 ) cpf_add_elig_comp ,
562 'LEGAL_NAME=P',
563 pay_sg_cpfline_balances.get_balance_value( 'NEW',
564 paa.assignment_id,
565 nvl(pai.action_information1,pai.source_id),
566 pai.action_information22,
567 paa.assignment_action_id,
568 paa.tax_unit_id,
569 'Legal_Name',
570 pai.action_information17,
571 ppa.payroll_action_id,
572 pai.action_information21
573 ) legal_name,
574 'EMPLOYEE_NUMBER=P',
575 pay_sg_cpfline_balances.get_balance_value( 'NEW',
576 paa.assignment_id,
577 nvl(pai.action_information1,pai.source_id),
578 pai.action_information22,
579 paa.assignment_action_id,
580 paa.tax_unit_id,
581 'Employee_Number',
582 pai.action_information18,
583 ppa.payroll_action_id,
584 pai.action_information21
585 ) employee_number,
586 'EMP_TERMINATION_DATE=P',
587 pay_sg_cpfline_balances.get_balance_value( 'NEW',
588 paa.assignment_id,
589 nvl(pai.action_information1,pai.source_id),
590 pai.action_information22,
591 paa.assignment_action_id,
592 paa.tax_unit_id,
593 'Emp_Term_Date',
594 pai.action_information19,
595 ppa.payroll_action_id,
596 pai.action_information21
597 ) emp_termination_date
598 from pay_payroll_actions ppa,
599 pay_assignment_actions paa,
600 pay_action_information pai
601 where ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
602 and ppa.payroll_action_id = paa.payroll_action_id
603 and paa.assignment_action_id = pai.action_context_id
604 and pai.action_information_category = 'SG CPF DETAILS'
605 and pai.action_context_type = 'AAC'
606 and pai.action_information2 = 'NEW'
607 and exists ( select 1
608 from pay_action_information pai_dup,
609 pay_assignment_actions paa_dup
610 where pai.action_information_category = pai_dup.action_information_category
611 and pai.rowid <> pai_dup.rowid
612 and paa_dup.payroll_action_id = ppa.payroll_action_id
613 and paa_dup.assignment_action_id = pai_dup.action_context_id
614 and pai.action_information1 = pai_dup.action_information1
615 and pai_dup.action_context_type = 'AAC' )
616 order by sort asc,department asc, hire_date desc;
617
618 ----------------------------------------------------------
619 -- Bug: 3619297 - Used lookup SG_CPFLINE_COMM_FUNDS
623 select 'EMPLOYER_SERVICE=P',
620 -- instead of using dual table to sort the rows.
621 ----------------------------------------------------------
622 cursor er_service_employees is
624 hl.meaning employer_service,
625 'LEGAL_NAME=P',
626 pai.action_information17 legal_name,
627 'VALUE=P',
628 sum(decode(hl.meaning,'SDL', pai.action_information15,
629 'MBMF Fund', pai.action_information8,
630 'SINDA Fund', pai.action_information9,
631 'CDAC Fund', pai.action_information10,
632 'ECF Fund', pai.action_information11,
633 'FWL', pai.action_information16,
634 'SHARE Program Donations', pai.action_information14)) value
635 from pay_payroll_actions ppa,
636 pay_assignment_actions paa,
637 pay_action_information pai,
638 hr_lookups hl
639 where ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
640 and ppa.payroll_action_id = paa.payroll_action_id
641 and paa.assignment_action_id = pai.action_context_id
642 and pai.action_information_category = 'SG CPF DETAILS'
643 and pai.action_context_type = 'AAC'
644 and hl.lookup_type = 'SG_CPFLINE_COMM_FUNDS'
645 and decode(hl.meaning,'SDL', pai.action_information15,
646 'MBMF Fund', pai.action_information8,
647 'SINDA Fund', pai.action_information9,
648 'CDAC Fund', pai.action_information10,
649 'ECF Fund', pai.action_information11,
650 'FWL', pai.action_information16,
651 'SHARE Program Donations', pai.action_information14) <> 0
652 group by hl.meaning,hl.lookup_code, pai.action_information17, nvl(pai.action_information1,pai.source_id)
653 order by hl.lookup_code, pai.action_information17;
654
655
656 end pay_sg_cpfline;