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