[Home] [Help]
PACKAGE: APPS.PAY_DK_PAYMENT_PROCESS_PKG
Source
1 PACKAGE PAY_DK_PAYMENT_PROCESS_PKG AUTHID CURRENT_USER as
2 /* $Header: pydkpaypr.pkh 120.29.12020000.2 2013/03/15 05:59:44 rsahai ship $ */
3
4 level_cnt NUMBER;
5
6 FUNCTION get_collective_agr_no(p_asg_id NUMBER, p_date_earned DATE, p_bg_id number ) RETURN VARCHAR2; --15985354
7
8 --12759416
9 FUNCTION DK_PBS_GET_ATP_TAX_INFO(
10 p_payroll_action_id IN NUMBER,
11 p_transfer_info_type IN VARCHAR2,
12 p_transfer_total_amount IN VARCHAR2,
13 p_transfer_info_disposal_date IN VARCHAR2,
14 p_transfer_payer_reg_no IN VARCHAR2,
15 p_transfer_payer_acct_no IN VARCHAR2,
16 p_transfer_ds_cvr_no IN VARCHAR2,
17 p_transfer_payer_cvr_no IN VARCHAR2,
18 p_transfer_pbs_no IN VARCHAR2,
19 p_transfer_pmnt_start_date IN VARCHAR2,
20 p_transfer_pmnt_end_date IN VARCHAR2,
21 p_action_information_category IN VARCHAR2
22 )RETURN VARCHAR2;
23 --12759416
24
25 --12759416
26 FUNCTION DK_PBS_ARCH_ATP_TAX_INFO(
27 p_payroll_action_id IN NUMBER,
28 p_transfer_overforsels_type IN VARCHAR2,
29 p_transfer_info_type IN VARCHAR2,
30 p_transfer_total_amount IN VARCHAR2,
31 p_transfer_info_disposal_date IN VARCHAR2,
32 p_transfer_payer_reg_no IN VARCHAR2,
33 p_transfer_payer_acct_no IN VARCHAR2,
34 p_transfer_ds_cvr_no IN VARCHAR2,
35 p_transfer_payer_cvr_no IN VARCHAR2,
36 p_transfer_pbs_no IN VARCHAR2,
37 p_transfer_pmnt_start_date IN VARCHAR2,
38 p_transfer_pmnt_end_date IN VARCHAR2,
39 p_action_information_category IN VARCHAR2
40 )RETURN NUMBER;
41 --12759416
42
43 /* Added for bug fix 8501177 */
44 FUNCTION get_Assignment_Action (
45 p_assignment_id NUMBER
46 )
47 RETURN NUMBER;
48
49 FUNCTION get_defined_balance_id (
50 p_dimension_name VARCHAR2,
51 p_balance_name VARCHAR2
52 )
53 RETURN NUMBER;
54 /* Added for bug fix 8501177 */
55
56 FUNCTION get_parameter(
57 p_parameter_string IN VARCHAR2
58 ,p_token IN VARCHAR2
59 ,p_segment_number IN NUMBER DEFAULT NULL )RETURN VARCHAR2;
60
61 FUNCTION get_lookup_meaning (p_lookup_type varchar2,p_lookup_code varchar2) RETURN VARCHAR2 ;
62
63
64 /* Added for Third Party Payments */
65 FUNCTION get_ass_action_context(p_assignment_id NUMBER) RETURN NUMBER;
66
67 FUNCTION get_date_earned_context(p_assignment_id NUMBER) RETURN DATE;
68
69 --FUNCTION get_prev_bal_paid(p_assignment_id NUMBER, p_balance_name VARCHAR2) RETURN NUMBER;
70 /* Added p_org_id to function for pension changes */
71 FUNCTION get_prev_bal_paid(p_assignment_id NUMBER, p_org_id NUMBER, p_balance_name VARCHAR2) RETURN NUMBER;
72
73 FUNCTION get_phy_record_no(p_person_id NUMBER, p_assignment_id NUMBER, p_pp_id VARCHAR2) RETURN NUMBER;
74
75 /* Added for bug fix 4563148 */
76 FUNCTION check_numeric(p_text VARCHAR2) RETURN NUMBER;
77
78 /* Added during Holiday Pay plug-in and OS I10 enhancement */
79 /* Changed to return Varchar2 for bug */
80 FUNCTION get_pension_provider(p_org_name VARCHAR2) RETURN VARCHAR2;
81
82 FUNCTION get_ident_codes(p_bg_id IN NUMBER
83 ,p_effective_date IN DATE
84 ,p_tax_rc OUT NOCOPY VARCHAR2
85 ,p_amb_rc OUT NOCOPY VARCHAR2
86 ,p_sp_rc OUT NOCOPY VARCHAR2
87 ,p_hol_days_rc OUT NOCOPY VARCHAR2) RETURN NUMBER;
88 /* Added to support multiple pensions for OSI02 for bug fix 5563150*/
89 FUNCTION get_pen_values(p_eff_date DATE,p_ele_type_id NUMBER, p_ee_id NUMBER, p_iv_name VARCHAR2) RETURN VARCHAR2;
90
91 /* Added to support override for Use of Holiday Card for transfer to Holiday Bank for bug fix 5533140*/
92 FUNCTION get_use_hol_card(p_payroll_action_id NUMBER,p_date_earned DATE ) RETURN VARCHAR2;
93
94 FUNCTION get_pay_period_per_year(p_payroll_action_id NUMBER,p_date_earned DATE ) RETURN NUMBER;
95
96 FUNCTION get_parameters(p_payroll_action_id NUMBER, p_token IN VARCHAR2) RETURN VARCHAR2; --12660243
97
98
99 CURSOR get_ds_record_details IS
100 --9036229
101 SELECT 'CHECK_DIGIT_DS=P'
102 , to_char(PAY_DK_PAYMENT_PROCESS_PKG.get_parameter(legislative_parameters,'CHECK_DIGIT_DS',null))
103 , 'TRANSFER_DS_CVR_NO=P'
104 , hoi2.org_information1
105 , 'IDENTIFICATION_DELIVERY=P'
106 , fnd_global.conc_request_id
107 , 'TRANSFER_IDENTIFICATION_DS=P'
108 , NVL(PAY_DK_PAYMENT_PROCESS_PKG.get_parameter(legislative_parameters,'IDENTIFICATION_DS',null),' ')
109 , 'TRANSFER_PAYROLL_NAME=P'
110 , pap.PAYROLL_NAME
111 , 'CONSOLIDATION_NAME=P'
112 , pcs.consolidation_set_name
113 , 'START_DATE=P'
114 , to_char(ppa.start_date,'YYYYMMDD')
115 , 'END_DATE=P'
116 , to_char(ppa.effective_date,'YYYYMMDD')
117 , 'PAYMENT_METHOD=P'
118 , pop.org_payment_method_name
119 , 'DS_NAME=P'
120 , hou.name
121 , 'TRANSFER_PAYER_REG_NO=P'
122 , pea.segment1
123 , 'TRANSFER_PAYER_ACCT_NO=P'
124 , pea.segment3
125 FROM hr_organization_units hou
126 , hr_organization_information hoi1
127 , hr_organization_information hoi2
128 , pay_payroll_actions ppa
129 , pay_consolidation_sets pcs
130 , pay_all_payrolls_f pap
131 , pay_payment_types ppt
132 , pay_external_accounts pea
133 , pay_org_payment_methods_f pop
134 , PAY_ORG_PAY_METHOD_USAGES_F popmu --9036229
135 WHERE ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
136 AND pap.payroll_id = ppa.payroll_id
137 AND pap.payroll_id = nvl(APPS.PAY_DK_PAYMENT_PROCESS_PKG.get_parameter(legislative_parameters,'PAYROLL_ID',null), pap.payroll_id) --9036229
138 AND pcs.consolidation_set_id = pap.consolidation_set_id
139 AND pop.org_payment_method_id = nvl(PAY_DK_PAYMENT_PROCESS_PKG.get_parameter(legislative_parameters,'PAYMENT_METHOD_ID',null), pop.org_payment_method_id)
140 AND pop.external_account_id = pea.external_account_id
141 AND hou.business_group_id = ppa.business_group_id
142 AND hoi1.organization_id = hou.organization_id
143 AND hou.organization_id = nvl(PAY_DK_PAYMENT_PROCESS_PKG.get_parameter(legislative_parameters,'DS_NAME',null),hou.organization_id)
144 AND hoi1.org_information_context='CLASS'
145 AND hoi1.org_information1 = nvl2(PAY_DK_PAYMENT_PROCESS_PKG.get_parameter(legislative_parameters,'DS_NAME',null),'HR_LEGAL_EMPLOYER','DK_SERVICE_PROVIDER' )
146 AND hoi1.org_information2 ='Y'
147 AND hoi2.org_information_context=nvl2(PAY_DK_PAYMENT_PROCESS_PKG.get_parameter(legislative_parameters,'DS_NAME',null),'DK_LEGAL_ENTITY_DETAILS','DK_SERVICE_PROVIDER_DETAILS')
148 AND hoi2.organization_id = hoi1.organization_id
149 AND ppa.effective_date BETWEEN hou.date_from AND nvl(hou.date_to, ppa.effective_date)
150 AND ppa.effective_date BETWEEN pap.effective_start_date AND pap.effective_end_date
151 AND ppt.payment_type_id = PAY_DK_PAYMENT_PROCESS_PKG.get_parameter(legislative_parameters,'PT_ID')
152 AND ppt.payment_type_id = pop.payment_type_id
153 AND pop.business_group_id = ppa.business_group_id
154 AND pop.defined_balance_id is not null
155 --9036229
156 AND popmu.payroll_id = pap.payroll_id
157 AND popmu.org_payment_method_id = pop.org_payment_method_id
158 AND ppa.effective_date BETWEEN popmu.effective_start_date AND popmu.effective_end_date
159 --9036229
160 AND ppa.effective_date BETWEEN pop.effective_start_date AND pop.effective_end_date
161 AND ROWNUM = 1; --10089738
162 --9036229
163
164 /* ----9036229
165 SELECT 'CHECK_DIGIT_DS=P'
166 , to_char(PAY_DK_PAYMENT_PROCESS_PKG.get_parameter(legislative_parameters,'CHECK_DIGIT_DS',null))
167 , 'TRANSFER_DS_CVR_NO=P'
168 , hoi2.org_information1
169 , 'IDENTIFICATION_DELIVERY=P'
170 , fnd_global.conc_request_id
171 , 'TRANSFER_IDENTIFICATION_DS=P'
172 , PAY_DK_PAYMENT_PROCESS_PKG.get_parameter(legislative_parameters,'IDENTIFICATION_DS',null)
173 , 'TRANSFER_PAYROLL_NAME=P'
174 , pap.PAYROLL_NAME
175 , 'CONSOLIDATION_NAME=P'
176 , pcs.consolidation_set_name
177 , 'START_DATE=P'
178 , to_char(ppa.start_date,'YYYYMMDD')
179 , 'END_DATE=P'
180 , to_char(ppa.effective_date,'YYYYMMDD')
181 , 'PAYMENT_METHOD=P'
182 , pop.org_payment_method_name
183 , 'DS_NAME=P'
184 , hou.name
185 , 'TRANSFER_PAYER_REG_NO=P'
186 , pea.segment1
187 , 'TRANSFER_PAYER_ACCT_NO=P'
188 , pea.segment3
189 FROM hr_organization_units hou
190 , hr_organization_information hoi1
191 , hr_organization_information hoi2
192 , pay_payroll_actions ppa
193 , pay_consolidation_sets pcs
194 , pay_all_payrolls_f pap
195 , pay_external_accounts pea
196 , pay_org_payment_methods_f pop
197 WHERE ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
198 AND pap.payroll_id = ppa.payroll_id
199 AND pcs.consolidation_set_id = pap.consolidation_set_id
200 AND pop.org_payment_method_id = ppa.org_payment_method_id
201 AND pop.external_account_id = pea.external_account_id
202 AND hou.business_group_id = ppa.business_group_id
203 AND hoi1.organization_id = hou.organization_id
204 AND hou.organization_id = nvl(PAY_DK_PAYMENT_PROCESS_PKG.get_parameter(legislative_parameters,'DS_NAME',null),hou.organization_id)
205 AND hoi1.org_information_context='CLASS'
206 AND hoi1.org_information1 = nvl2(PAY_DK_PAYMENT_PROCESS_PKG.get_parameter(legislative_parameters,'DS_NAME',null),'HR_LEGAL_EMPLOYER','DK_SERVICE_PROVIDER' )
207 AND hoi1.org_information2 ='Y'
208 AND hoi2.org_information_context=nvl2(PAY_DK_PAYMENT_PROCESS_PKG.get_parameter(legislative_parameters,'DS_NAME',null),'DK_LEGAL_ENTITY_DETAILS','DK_SERVICE_PROVIDER_DETAILS')
209 AND hoi2.organization_id = hoi1.organization_id
210 AND ppa.effective_date BETWEEN hou.date_from AND nvl(hou.date_to, ppa.effective_date)
211 AND ppa.effective_date BETWEEN pap.effective_start_date AND pap.effective_end_date;
212 */ ----9036229
213
214
215 /* Added context for PAYROLL_ACTION_ID for enh 6344939 */
216 CURSOR get_section_record_details IS
217 SELECT 'TRANSFER_DISPOSAL_DATE=P'
218 , to_char(fnd_date.canonical_to_date(PAY_DK_PAYMENT_PROCESS_PKG.get_parameter(legislative_parameters,'PAYMENT_DD',null)),'DDMMYY')
219 , 'TRANSFER_PAYER_CVR_NO=P'
220 , hoi2.org_information1
221 , 'TRANSFER_LE_ID=P'
222 , to_char(hou.ORGANIZATION_ID)
223 , 'PAYROLL_ACTION_ID=C'
224 , 'TRANSFER_TYPE=P' --9036229
225 , '20' "type" --9036229
226 , pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
227 FROM pay_payroll_actions ppa
228 , hr_organization_units hou
229 , hr_organization_information hoi1
230 , hr_organization_information hoi2
231 WHERE ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
232 AND hou.business_group_id = ppa.business_group_id
233 AND hoi1.organization_id = hou.organization_id
234 AND hoi1.org_information_context='CLASS'
235 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
236 AND hoi1.org_information2 = 'Y'
237 AND hoi2.org_information_context='DK_LEGAL_ENTITY_DETAILS'
238 AND hoi2.organization_id = hoi1.organization_id
239 AND nvl(hoi2.org_information1,0)= nvl2(PAY_DK_PAYMENT_PROCESS_PKG.get_parameter(legislative_parameters,'DS_NAME',null),pay_magtape_generic.get_parameter_value('TRANSFER_DS_CVR_NO') ,nvl(hoi2.org_information1,0))
240 AND ppa.effective_date BETWEEN hou.date_from AND nvl(hou.date_to, ppa.effective_date);
241
242 CURSOR get_transfer_record_details IS
243 SELECT 'PAYEE_REG_NO=P'
244 , pea.segment1
245 , 'PAYEE_ACCT_NO=P'
246 , pea.segment3
247 , 'PAYEE_AMOUNT=P'
248 , to_char(ppp.value*100)
249 , 'IDENTIFICATION_PAYEE=P'
250 , substr(to_char(pap.national_identifier),1,instr(to_char(pap.national_identifier),'-')-1) ||substr(to_char(pap.national_identifier),instr(to_char(pap.national_identifier),'-')+1)--to_char(pap.national_identifier)
251 , 'FULL_NAME=P'
252 , pap.full_name
253 FROM per_all_assignments_f paf
254 , per_all_people_f pap
255 , hr_soft_coding_keyflex scl
256 , pay_payroll_actions ppa
257 , pay_assignment_actions paa
258 , pay_pre_payments ppp
259 , pay_external_accounts pea
260 , pay_personal_payment_methods_f ppm
261 , PAY_ORG_PAYMENT_METHODS_f pop --9036229
262 WHERE ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
263 AND paf.business_group_id = ppa.business_group_id
264 AND paf.payroll_id = ppa.PAYROLL_ID
265 AND pap.per_information_category ='DK'
266 AND paf.person_id = pap.person_id
267 AND paf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
268 AND ppa.effective_date between paf.effective_start_date AND paf.effective_end_date
269 AND ppa.effective_date between pap.effective_start_date AND pap.effective_end_date
270 AND scl.enabled_flag = 'Y'
271 AND scl.segment1 = pay_magtape_generic.get_parameter_value('TRANSFER_LE_ID')
272 AND paa.payroll_action_id = ppa.payroll_action_id
273 AND ppp.pre_payment_id = paa.pre_payment_id
274 AND paa.assignment_id = paf.assignment_id
275 AND ppm.personal_payment_method_id = ppp.personal_payment_method_id
276 AND ppp.value > 0
277 AND ppm.external_account_id = pea.external_account_id
278 AND ppa.effective_date BETWEEN ppm.effective_start_date AND ppm.effective_end_date
279 --9036229
280 AND pop.org_payment_method_id = ppm.org_payment_method_id
281 AND pop.business_group_id = ppa.business_group_id
282 AND pop.defined_balance_id is not null;
283
284
285 --9036229
286 CURSOR get_section_rp_details IS
287 SELECT 'TRANSFER_DISPOSAL_DATE=P'
288 --, to_char(fnd_date.canonical_to_date(PAY_DK_PAYMENT_PROCESS_PKG.get_parameter(legislative_parameters,'PAYMENT_DD',null)),'DDMMYY')
289 , to_char(fnd_date.canonical_to_date(PAY_DK_PAYMENT_PROCESS_PKG.get_parameter(legislative_parameters,'INFOTYPE_RATE_DD',null)),'DDMMYY') --10221019
290 , 'TRANSFER_PAYER_CVR_NO=P'
291 , hoi2.org_information1
292 , 'TRANSFER_LE_ID=P'
293 , to_char(hou.ORGANIZATION_ID)
294 , 'PAYROLL_ACTION_ID=C'
295 , pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
296 , 'TRANSFER_TYPE=P'
297 , '90' "type"
298 FROM pay_payroll_actions ppa
299 , hr_organization_units hou
300 , hr_organization_information hoi1
301 , hr_organization_information hoi2
302 WHERE ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
303 AND hou.business_group_id = ppa.business_group_id
304 AND hoi1.organization_id = hou.organization_id
305 AND hoi1.org_information_context='CLASS'
306 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
307 AND hoi1.org_information2 = 'Y'
308 AND hoi2.org_information_context='DK_LEGAL_ENTITY_DETAILS'
309 AND hoi2.organization_id = hoi1.organization_id
310 AND nvl(hoi2.org_information1,0)= nvl2(PAY_DK_PAYMENT_PROCESS_PKG.get_parameter(legislative_parameters,'DS_NAME',null),pay_magtape_generic.get_parameter_value('TRANSFER_DS_CVR_NO') ,nvl(hoi2.org_information1,0))
311 AND ppa.effective_date BETWEEN hou.date_from AND nvl(hou.date_to, ppa.effective_date)
312 AND hou.ORGANIZATION_ID = pay_magtape_generic.get_parameter_value('TRANSFER_LE_ID'); --9036229
313
314 CURSOR get_transfer_rp_record_details IS
315 SELECT 'PAYEE_REG_NO=P'
316 , pea.segment1
317 , 'PAYEE_ACCT_NO=P'
318 , pea.segment3
319 , 'PAYEE_AMOUNT=P'
320 , to_char(SUM(ppp.value*100))
321 , 'IDENTIFICATION_PAYEE=P'
322 , substr(to_char(pap.national_identifier),1,instr(to_char(pap.national_identifier),'-')-1) ||substr(to_char(pap.national_identifier),instr(to_char(pap.national_identifier),'-')+1)--to_char(pap.national_identifier)
323 , 'FULL_NAME=P'
324 , pap.full_name
325 FROM per_all_assignments_f paf
326 , per_all_people_f pap
327 , hr_soft_coding_keyflex scl
328 , pay_payroll_actions ppa
329 , pay_assignment_actions paa
330 , pay_pre_payments ppp
331 , pay_external_accounts pea
332 , pay_personal_payment_methods_f ppm
333 , PAY_ORG_PAYMENT_METHODS_f pop --9036229
334 WHERE ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
335 AND paf.business_group_id = ppa.business_group_id
336 AND paf.payroll_id = ppa.PAYROLL_ID
337 AND pap.per_information_category ='DK'
338 AND paf.person_id = pap.person_id
339 AND paf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
340 AND ppa.effective_date between paf.effective_start_date AND paf.effective_end_date
341 AND ppa.effective_date between pap.effective_start_date AND pap.effective_end_date
342 AND scl.enabled_flag = 'Y'
343 AND scl.segment1 = pay_magtape_generic.get_parameter_value('TRANSFER_LE_ID')
344 AND paa.payroll_action_id = ppa.payroll_action_id
345 AND ppp.pre_payment_id = paa.pre_payment_id
346 AND paa.assignment_id = paf.assignment_id
347 AND ppm.personal_payment_method_id = ppp.personal_payment_method_id
348 AND ppp.value > 0
349 AND ppm.external_account_id = pea.external_account_id
350 AND ppa.effective_date BETWEEN ppm.effective_start_date AND ppm.effective_end_date
351 AND pop.org_payment_method_id = ppm.org_payment_method_id
352 AND pop.business_group_id = ppa.business_group_id
353 AND pop.defined_balance_id is null
354 GROUP BY
355 'PAYEE_REG_NO=P'
356 , pea.segment1
357 , 'PAYEE_ACCT_NO=P'
358 , pea.segment3
359 , 'PAYEE_AMOUNT=P'
360 , 'IDENTIFICATION_PAYEE=P'
361 , substr(to_char(pap.national_identifier),1,instr(to_char(pap.national_identifier),'-')-1) ||substr(to_char(pap.national_identifier),instr(to_char(pap.national_identifier),'-')+1)--to_char(pap.national_identifier)
362 , 'FULL_NAME=P'
363 , pap.full_name;
364 --9036229
365
366
367 /* Added the following for Third Party Payments */
368
369 CURSOR get_info_record_details IS
370 SELECT 'TRANSFER_INFO_TYPE=P'
371 , puci.value
372 , 'TRANSFER_INFO_DISPOSAL_DATE=P'
373 , to_char(fnd_date.canonical_to_date(PAY_DK_PAYMENT_PROCESS_PKG.get_parameter(legislative_parameters,'INFOTYPE'||puci.value||'_DD',null)),'DDMMYY')
374 , 'TRANSFER_PBS_NO=P'
375 , puci1.value
376 , 'TRANSFER_RECEIVER_NAME=P'
377 , pur.row_low_range_or_name
378 FROM pay_payroll_actions ppa
379 , pay_user_tables put
380 , pay_user_columns puc
381 , pay_user_column_instances_f puci
382 , pay_user_rows_f pur
383 , pay_user_columns puc1
384 , pay_user_column_instances_f puci1
385 , pay_user_rows_f pur1
386 WHERE ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
387 AND put.user_table_name = 'DK_PBS_DATA'
388 AND put.LEGISLATION_CODE ='DK'
389 AND puc.user_table_id = put.user_table_id
390 AND puc.user_column_name = 'Information Type'
391 AND puci.user_column_id = puc.user_column_id
392 AND ppa.effective_date between puci.effective_start_date and puci.effective_end_date
393 AND pur.user_row_id = puci.user_row_id
394 AND ppa.effective_date between pur.effective_start_date and pur.effective_end_date
395 and puc1.user_table_id = put.user_table_id
396 and puc1.user_column_name = 'PBS Number'
397 and puci1.user_column_id = puc1.user_column_id
398 /* Added for bug fix 5071004 */
399 and puci1.business_group_id =ppa.business_group_id
400 and ppa.effective_date between puci1.effective_start_date and puci1.effective_end_date
401 and pur1.user_row_id = puci1.user_row_id
402 and pur1.user_row_id = pur.user_row_id
403 and ppa.effective_date between pur1.effective_start_date and pur1.effective_end_date
404 UNION
405 SELECT 'TRANSFER_INFO_TYPE=P'
406 , hoi2.ORG_INFORMATION2
407 , 'TRANSFER_INFO_DISPOSAL_DATE=P'
408 , to_char(fnd_date.canonical_to_date(PAY_DK_PAYMENT_PROCESS_PKG.get_parameter(legislative_parameters,'INFOTYPE_PENSION_DD',null)),'DDMMYY')
409 , 'TRANSFER_PBS_NO=P'
410 , hoi2.ORG_INFORMATION1
411 , 'TRANSFER_RECEIVER_NAME=P'
412 , hou.name
413 FROM pay_payroll_actions ppa
414 , hr_organization_units hou
415 , hr_organization_information hoi1
416 , hr_organization_information hoi2
417 WHERE ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
418 AND hou.business_group_id = ppa.business_group_id
419 AND hoi1.organization_id = hou.organization_id
420 AND hoi1.org_information_context='CLASS'
421 AND hoi1.org_information1 = 'DK_PENSION_PROVIDER'
422 AND hoi1.org_information2 = 'Y'
423 AND hoi2.org_information_context='DK_PENSION_PROVIDER_DETAILS'
424 AND hoi2.organization_id = hoi1.organization_id
425 AND ppa.effective_date BETWEEN hou.date_from AND nvl(hou.date_to, ppa.effective_date);
426
427
428 /* Modified for bug fix 4551283 to change FULL_NAME to Employer's name*/
429 /* Modified for bug fix 4554812 to filter on pension provider */
430 /* Modified for bug fix 5887000 for PAYMENT_END_DATE */
431 /* Modified for pension changes , also added UNION */
432 CURSOR get_info_record_00_details IS
433 SELECT 'IDENTIFICATION_PAYEE=P'
434 , substr(to_char(pap.national_identifier),1,instr(to_char(pap.national_identifier),'-')-1) ||substr(to_char(pap.national_identifier),instr(to_char(pap.national_identifier),'-')+1)--to_char(pap.national_identifier)
435 , 'FULL_NAME=P'
436 , pap.full_name
437 , 'PAYMENT_START_DATE=P'
438 , to_char(ppa.START_DATE,'YYYYMMDD')
439 , 'PAYMENT_END_DATE=P'
440 , to_char(PAY_DK_PAYMENT_PROCESS_PKG.get_date_earned_context(paa.ASSIGNMENT_ID),'YYYYMMDD')
441 , 'ASSIGNMENT_ACTION_ID=C'
442 , PAY_DK_PAYMENT_PROCESS_PKG.get_ass_action_context(paa.ASSIGNMENT_ID)
443 , 'TRANSFER_ASSIGNMENT_ID=P'
444 , paa.ASSIGNMENT_ID
445 , 'TRANSFER_TERMINATION_DATE=P'
446 /* Re-written to obtain correct dates*/
447 /*, to_char(fnd_date.canonical_to_date(nvl(scl.segment8,pap.effective_end_date)),'YYYYMMDD')*/
448 , to_char(decode(scl.segment8,null,pap.effective_end_date,fnd_date.canonical_to_date(scl.segment8)),'YYYYMMDD')
449 , 'TRANSFER_PERSON_ID=P'
450 , to_char(pap.person_id)
451 , 'TRANSFER_EMPLOYMENT_CATEGORY=P'
452 , paf.employment_category
453 , 'PHYS_RECO_NO=P'
454 , to_char(PAY_DK_PAYMENT_PROCESS_PKG.get_phy_record_no(pap.person_id, paf.assignment_id,peev.screen_entry_value))
455 /* Added for Holiday pay plug-in and OS I 10 enhancement */
456 , 'EFFECTIVE_DATE=P'
457 , to_char(ppa.effective_date)
458 , 'BUSINESS_GROUP_ID=P'
459 , to_char(ppa.business_group_id)
460 , 'PAYROLL_ACTION_ID=C'
461 , pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
462 , 'ORGANIZATION_ID=C'
463 , peev.screen_entry_value
464 , 'TRANSFER_ORGANIZATION_ID=P'
465 , peev.screen_entry_value
466 /* Added for bug fix 5533140 */
467 , 'ASSIGNMENT_ID=C'
468 , paa.ASSIGNMENT_ID
469 , 'DATE_EARNED=C'
470 , fnd_date.date_to_canonical(PAY_DK_PAYMENT_PROCESS_PKG.get_date_earned_context(paa.ASSIGNMENT_ID))
471 FROM per_all_assignments_f paf
472 , per_all_people_f pap
473 , hr_soft_coding_keyflex scl
474 , pay_payroll_actions ppa
475 , pay_assignment_actions paa
476 , pay_pre_payments ppp
477 , pay_external_accounts pea
478 , pay_personal_payment_methods_f ppm
479 /* Added join for bug fix 4554812 */
480 /* Removed and re-wrote in function get_pension_provider */
481 /*, hr_organization_units hou */
482 /* Added for Pension changes */
483 ,pay_element_entries_f peef
484 ,pay_element_types_f petf
485 ,pay_input_values_f pivf
486 ,pay_element_entry_values_f peev
487 ,per_assignment_status_types past -- 8501177
488 WHERE ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
489 AND paf.business_group_id = ppa.business_group_id
490 AND paf.payroll_id = ppa.payroll_id
491 AND pap.per_information_category ='DK'
492 AND paf.person_id = pap.person_id
493 AND paf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
494 AND ppa.effective_date between paf.effective_start_date AND paf.effective_end_date
495 AND ppa.effective_date between pap.effective_start_date AND pap.effective_end_date
496 AND scl.enabled_flag = 'Y'
497 AND scl.segment1 = pay_magtape_generic.get_parameter_value('TRANSFER_LE_ID')
498 /* Added for bug fix 4554812 */
499 /* Removed and re-wrote in function get_pension_provider */
500 /*AND hou.name = pay_magtape_generic.get_parameter_value('TRANSFER_RECEIVER_NAME')*/
501 /* Removed and re-written for pension changes to get pension provider */
502 /*AND nvl(scl.segment2,0) = decode(pay_magtape_generic.get_parameter_value('TRANSFER_INFO_TYPE')
503 , 300 , nvl(scl.segment2,0)
504 , 400 , nvl(scl.segment2,0)
505 , 800 , nvl(scl.segment2,0)
506 , 900 , nvl(scl.segment2,0)
507 , PAY_DK_PAYMENT_PROCESS_PKG.get_pension_provider(pay_magtape_generic.get_parameter_value('TRANSFER_RECEIVER_NAME')))--to_char(hou.organization_id))
508 */
509 /* Added for Pension changes -start */
510 AND pay_magtape_generic.get_parameter_value('TRANSFER_INFO_TYPE') NOT IN (300, 400, 800, 900)
511 AND peef.assignment_id = paf.assignment_id
512 AND ppa.effective_date BETWEEN peef.effective_start_date AND peef.effective_end_date
513 AND peef.element_type_id = petf.element_type_id
514 AND petf.legislation_code ='DK'
515 AND petf.element_name = 'Pension'
516 AND ppa.effective_date BETWEEN petf.effective_start_date AND petf.effective_end_date
517 AND pivf.element_type_id = petf.element_type_id
518 AND pivf.input_value_id = peev.input_value_id
519 AND pivf.name= 'Third Party Payee'
520 AND peev.element_entry_id = peef.element_entry_id
521 AND peev.screen_entry_value = PAY_DK_PAYMENT_PROCESS_PKG.get_pension_provider(pay_magtape_generic.get_parameter_value('TRANSFER_RECEIVER_NAME'))
522 AND ppa.effective_date BETWEEN pivf.effective_start_date AND pivf.effective_end_date
523 /* Added for Pension changes -end */
524 AND paa.payroll_action_id = ppa.payroll_action_id
525 AND ppp.pre_payment_id = paa.pre_payment_id
526 AND paa.assignment_id = paf.assignment_id
527 AND ppm.personal_payment_method_id = ppp.personal_payment_method_id
528 AND ppp.value > 0
529 AND ppm.external_account_id = pea.external_account_id
530 AND ppa.effective_date BETWEEN ppm.effective_start_date AND ppm.effective_end_date
531 AND paf.assignment_status_type_id = past.assignment_status_type_id
532 /* Added for bug fix 8501177 */
533 AND (past.per_system_status = 'ACTIVE_ASSIGN'
534 OR
535
536 (pay_balance_pkg.get_value ( PAY_DK_PAYMENT_PROCESS_PKG.get_defined_balance_id ('Payments', 'Pensionable Pay'),
537 PAY_DK_PAYMENT_PROCESS_PKG.get_Assignment_Action(paa.ASSIGNMENT_ID),
538 NULL,
539 NULL,
540 NULL,
541 NULL,
542 NULL,
543 NULL
544 ) )
545 +
546 (pay_balance_pkg.get_value ( PAY_DK_PAYMENT_PROCESS_PKG.get_defined_balance_id ('Payments', 'Pensionable Pay Adjustment'),
547 PAY_DK_PAYMENT_PROCESS_PKG.get_Assignment_Action(paa.ASSIGNMENT_ID),
548 NULL,
549 NULL,
550 NULL,
551 NULL,
552 NULL,
553 NULL
554 )
555 )> 0
556 )
557 UNION
558 SELECT 'IDENTIFICATION_PAYEE=P'
559 , substr(to_char(pap.national_identifier),1,instr(to_char(pap.national_identifier),'-')-1) ||substr(to_char(pap.national_identifier),instr(to_char(pap.national_identifier),'-')+1)--to_char(pap.national_identifier)
560 , 'FULL_NAME=P'
561 , pap.full_name
562 , 'PAYMENT_START_DATE=P'
563 , to_char(ppa.START_DATE,'YYYYMMDD')
564 , 'PAYMENT_END_DATE=P'
565 , to_char(PAY_DK_PAYMENT_PROCESS_PKG.get_date_earned_context(paa.ASSIGNMENT_ID),'YYYYMMDD')
566 , 'ASSIGNMENT_ACTION_ID=C'
567 , PAY_DK_PAYMENT_PROCESS_PKG.get_ass_action_context(paa.ASSIGNMENT_ID)
568 , 'TRANSFER_ASSIGNMENT_ID=P'
569 , paa.ASSIGNMENT_ID
570 , 'TRANSFER_TERMINATION_DATE=P'
571 /* Re-written to obtain correct dates*/
572 /*, to_char(fnd_date.canonical_to_date(nvl(scl.segment8,pap.effective_end_date)),'YYYYMMDD')*/
573 , to_char(decode(scl.segment8,null,pap.effective_end_date,fnd_date.canonical_to_date(scl.segment8)),'YYYYMMDD')
574 , 'TRANSFER_PERSON_ID=P'
575 , to_char(pap.person_id)
576 , 'TRANSFER_EMPLOYMENT_CATEGORY=P'
577 , paf.employment_category
578 , 'PHYS_RECO_NO=P'
579 , to_char(PAY_DK_PAYMENT_PROCESS_PKG.get_phy_record_no(pap.person_id, paf.assignment_id,null))
580 /* Added for Holiday pay plug-in and OS I 10 enhancement */
581 , 'EFFECTIVE_DATE=P'
582 , to_char(ppa.effective_date)
583 , 'BUSINESS_GROUP_ID=P'
584 , to_char(ppa.business_group_id)
585 , 'PAYROLL_ACTION_ID=C'
586 , pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
587 , 'ORGANIZATION_ID=C'
588 , null
589 , 'TRANSFER_ORGANIZATION_ID=P'
590 , null
591 /* Added for bug fix 5533140 */
592 , 'ASSIGNMENT_ID=C'
593 , paa.ASSIGNMENT_ID
594 , 'DATE_EARNED=C'
595 , fnd_date.date_to_canonical(PAY_DK_PAYMENT_PROCESS_PKG.get_date_earned_context(paa.ASSIGNMENT_ID))
596 FROM per_all_assignments_f paf
597 , per_all_people_f pap
598 , hr_soft_coding_keyflex scl
599 , pay_payroll_actions ppa
600 , pay_assignment_actions paa
601 , pay_pre_payments ppp
602 , pay_external_accounts pea
603 , pay_personal_payment_methods_f ppm
604 /* Added join for bug fix 4554812 */
605 /* Removed and re-wrote in function get_pension_provider */
606 /*, hr_organization_units hou */
607 WHERE ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
608 AND paf.business_group_id = ppa.business_group_id
609 AND paf.payroll_id = ppa.payroll_id
610 AND pap.per_information_category ='DK'
611 AND paf.person_id = pap.person_id
612 AND paf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
613 AND ppa.effective_date between paf.effective_start_date AND paf.effective_end_date
614 AND ppa.effective_date between pap.effective_start_date AND pap.effective_end_date
615 AND scl.enabled_flag = 'Y'
616 AND scl.segment1 = pay_magtape_generic.get_parameter_value('TRANSFER_LE_ID')
617 /* Added for bug fix 4554812 */
618 /* Removed and re-wrote in function get_pension_provider */
619 /*AND hou.name = pay_magtape_generic.get_parameter_value('TRANSFER_RECEIVER_NAME')*/
620 /* Removed and re-written for pension changes to get pension provider */
621 /*AND nvl(scl.segment2,0) = decode(pay_magtape_generic.get_parameter_value('TRANSFER_INFO_TYPE')
622 , 300 , nvl(scl.segment2,0)
623 , 400 , nvl(scl.segment2,0)
624 , 800 , nvl(scl.segment2,0)
625 , 900 , nvl(scl.segment2,0)
626 , PAY_DK_PAYMENT_PROCESS_PKG.get_pension_provider(pay_magtape_generic.get_parameter_value('TRANSFER_RECEIVER_NAME')))--to_char(hou.organization_id))
627 */
628 /* Added for Pension changes -start */
629 AND pay_magtape_generic.get_parameter_value('TRANSFER_INFO_TYPE') IN (300, 400, 800, 900)
630 /* Added for Pension changes -end */
631 AND paa.payroll_action_id = ppa.payroll_action_id
632 AND ppp.pre_payment_id = paa.pre_payment_id
633 AND paa.assignment_id = paf.assignment_id
634 AND ppm.personal_payment_method_id = ppp.personal_payment_method_id
635 AND ppp.value > 0
636 AND ppm.external_account_id = pea.external_account_id
637 AND ppa.effective_date BETWEEN ppm.effective_start_date AND ppm.effective_end_date;
638
639
640 /* Modified for bug fix 4551283 to change FULL_NAME to Employer's name*/
641 /* Modified for Pension changes */
642 CURSOR get_info_record_01_details IS
643 SELECT 'TRANSFER_PAY_APPL_DATE=P'
644 , to_char(min(pee2.effective_start_date) ,'YYYYMMDD')
645 , 'TRANSFER_PENSION_START_DATE=P'
646 , to_char(pee1.effective_start_date ,'YYYYMMDD')
647 , 'ASSIGNMENT_ID=C'
648 , paa.ASSIGNMENT_ID
649 , 'DATE_EARNED=C'
650 , fnd_date.date_to_canonical(PAY_DK_PAYMENT_PROCESS_PKG.get_date_earned_context(paa.ASSIGNMENT_ID))
651 , 'PAYROLL_ACTION_ID=C'
652 , pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
653 , 'FULL_NAME=P'
654 , hou.name /*bug fix 4551283*/
655 FROM pay_payroll_actions ppa
656 , pay_assignment_actions paa
657 , pay_element_entries_f pee1
658 , pay_element_types_f pet
659 , pay_element_entries_f pee2
660 , hr_organization_units hou /*bug fix 4551283*/
661 /* Added for Pension changes */
662 , pay_input_values_f pivf
663 , pay_element_entry_values_f peev1
664 , pay_element_entry_values_f peev2
665 WHERE ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
666 AND paa.payroll_action_id = ppa.payroll_action_id
667 AND pee1.assignment_id = paa.assignment_id
668 AND pet.element_name = 'Pension'
669 AND pet.legislation_code ='DK'
670 AND pee1.entry_type ='E'
671 AND pee1.element_type_id = pet.element_type_id
672 AND pee2.assignment_id = paa.assignment_id
673 AND pee2.entry_type ='E'
674 AND pee2.element_type_id = pet.element_type_id
675 /* Added for Pension changes -start */
676 AND pivf.element_type_id = pet.element_type_id
677 AND pivf.name= 'Third Party Payee'
678 AND ppa.effective_date BETWEEN pivf.effective_start_date AND pivf.effective_end_date
679 AND peev1.input_value_id = pivf.input_value_id
680 AND peev1.element_entry_id = pee1.element_entry_id
681 AND peev1.screen_entry_value = PAY_DK_PAYMENT_PROCESS_PKG.get_pension_provider(pay_magtape_generic.get_parameter_value('TRANSFER_RECEIVER_NAME'))
682 AND peev2.input_value_id = pivf.input_value_id
683 AND peev2.element_entry_id = pee2.element_entry_id
684 AND peev2.screen_entry_value = PAY_DK_PAYMENT_PROCESS_PKG.get_pension_provider(pay_magtape_generic.get_parameter_value('TRANSFER_RECEIVER_NAME'))
685 /* Added for Pension changes -end */
686 AND paa.assignment_id = pay_magtape_generic.get_parameter_value('TRANSFER_ASSIGNMENT_ID')
687 AND ppa.effective_date BETWEEN pet.effective_start_date and pet.effective_end_date
688 AND ppa.effective_date BETWEEN pee1.effective_start_date and pee1.effective_end_date
689 AND pee1.effective_start_date >= ppa.start_date
690 AND pay_magtape_generic.get_parameter_value('TRANSFER_INFO_TYPE') NOT IN (300, 400, 800, 900)
691 AND hou.organization_id = pay_magtape_generic.get_parameter_value('TRANSFER_LE_ID') /*bug fix 4551283*/
692 AND ppa.effective_date BETWEEN hou.date_from AND nvl(hou.date_to, ppa.effective_date) /*bug fix 4551283*/
693 GROUP BY pee1.effective_start_date,paa.assignment_id,pee1.element_entry_id,pet.element_type_id,hou.name;
694
695
696 /* Modified for Pension changes */
697 CURSOR get_info_record_02_details IS
698 SELECT 'TRANSFER_PAY_APPL_DATE=P'
699 , to_char(min(pee2.effective_start_date) ,'YYYYMMDD')
700 , 'TRANSFER_PENSION_START_DATE=P'
701 , to_char(pee1.effective_start_date ,'YYYYMMDD')
702 , 'ASSIGNMENT_ID=C'
703 , paa.ASSIGNMENT_ID
704 , 'DATE_EARNED=C'
705 , fnd_date.date_to_canonical(PAY_DK_PAYMENT_PROCESS_PKG.get_date_earned_context(paa.ASSIGNMENT_ID))
706 , 'ASSIGNMENT_ACTION_ID=C'
707 , PAY_DK_PAYMENT_PROCESS_PKG.get_ass_action_context(paa.ASSIGNMENT_ID)
708 , 'PAYROLL_ACTION_ID=C'
709 , pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
710 /* Added for bug fix 5563150 */
711 , 'ELEMENT_ENTRY_ID=P'
712 , to_char(pee1.element_entry_id)
713 , 'ELEMENT_TYPE_ID=P'
714 , to_char(pet.element_type_id)
715 FROM pay_payroll_actions ppa
716 , pay_assignment_actions paa
717 , pay_element_entries_f pee1
718 , pay_element_types_f pet
719 , pay_element_entries_f pee2
720 /* Added for Pension changes */
721 , pay_input_values_f pivf
722 , pay_element_entry_values_f peev1
723 , pay_element_entry_values_f peev2
724 WHERE ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
725 AND paa.payroll_action_id = ppa.payroll_action_id
726 AND pee1.assignment_id = paa.assignment_id
727 AND pet.element_name = 'Pension'
728 AND pet.legislation_code ='DK'
729 AND pee1.entry_type ='E'
730 AND pee1.element_type_id = pet.element_type_id
731 AND pee2.assignment_id = paa.assignment_id
732 AND pee2.entry_type ='E'
733 AND pee2.element_type_id = pet.element_type_id
734 /* Added for Pension changes -start */
735 AND pivf.element_type_id = pet.element_type_id
736 AND pivf.name= 'Third Party Payee'
737 AND ppa.effective_date BETWEEN pivf.effective_start_date AND pivf.effective_end_date
738 AND peev1.input_value_id = pivf.input_value_id
739 AND peev1.element_entry_id = pee1.element_entry_id
740 AND peev1.screen_entry_value = PAY_DK_PAYMENT_PROCESS_PKG.get_pension_provider(pay_magtape_generic.get_parameter_value('TRANSFER_RECEIVER_NAME'))
741 AND peev2.input_value_id = pivf.input_value_id
742 AND peev2.element_entry_id = pee2.element_entry_id
743 AND peev2.screen_entry_value = PAY_DK_PAYMENT_PROCESS_PKG.get_pension_provider(pay_magtape_generic.get_parameter_value('TRANSFER_RECEIVER_NAME'))
744 /* Added for Pension changes -end */
745 AND paa.assignment_id = pay_magtape_generic.get_parameter_value('TRANSFER_ASSIGNMENT_ID')
746 AND pay_magtape_generic.get_parameter_value('TRANSFER_INFO_TYPE') NOT IN (300, 400, 800, 900)
747 AND ppa.effective_date BETWEEN pet.effective_start_date and pet.effective_end_date
748 AND ppa.effective_date BETWEEN pee1.effective_start_date and pee1.effective_end_date
749 GROUP BY pee1.effective_start_date,paa.assignment_id,pee1.element_entry_id,pet.element_type_id;
750
751
752 /* Modified for Pension changes */
753 CURSOR get_info_record_03_details IS
754 SELECT 'TRANSFER_PAY_APPL_DATE=P'
755 , to_char(min(pee2.effective_start_date),'YYYYMMDD')
756 , 'TRANSFER_PENSION_START_DATE=P'
757 , to_char(pee1.effective_start_date ,'YYYYMMDD')
758 , 'ASSIGNMENT_ACTION_ID=C'
759 , PAY_DK_PAYMENT_PROCESS_PKG.get_ass_action_context(paa.ASSIGNMENT_ID)
760 , 'ASSIGNMENT_ID=C'
761 , paa.ASSIGNMENT_ID
762 ,'DATE_EARNED=C'
763 , fnd_date.date_to_canonical(PAY_DK_PAYMENT_PROCESS_PKG.get_date_earned_context(paa.ASSIGNMENT_ID))
764 , 'PAYROLL_ACTION_ID=C'
765 , pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
766 FROM pay_payroll_actions ppa
767 , pay_assignment_actions paa
768 , pay_element_entries_f pee1
769 , pay_element_types_f pet
770 , pay_element_entries_f pee2
771 /* Added for Pension changes */
772 , pay_input_values_f pivf
773 , pay_element_entry_values_f peev1
774 , pay_element_entry_values_f peev2
775 WHERE ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
776 AND paa.payroll_action_id = ppa.payroll_action_id
777 AND pee1.assignment_id = paa.assignment_id
778 AND pet.element_name = 'Pension'
779 AND pet.legislation_code ='DK'
780 AND pee1.entry_type ='E'
781 AND pee1.element_type_id = pet.element_type_id
782 AND pee2.assignment_id = paa.assignment_id
783 AND pee2.entry_type ='E'
784 AND pee2.element_type_id = pet.element_type_id
785 /* Added for Pension changes -start */
786 AND pivf.element_type_id = pet.element_type_id
787 AND pivf.name= 'Third Party Payee'
788 AND ppa.effective_date BETWEEN pivf.effective_start_date AND pivf.effective_end_date
789 AND peev1.input_value_id = pivf.input_value_id
790 AND peev1.element_entry_id = pee1.element_entry_id
791 AND peev1.screen_entry_value = PAY_DK_PAYMENT_PROCESS_PKG.get_pension_provider(pay_magtape_generic.get_parameter_value('TRANSFER_RECEIVER_NAME'))
792 AND peev2.input_value_id = pivf.input_value_id
793 AND peev2.element_entry_id = pee2.element_entry_id
794 AND peev2.screen_entry_value = PAY_DK_PAYMENT_PROCESS_PKG.get_pension_provider(pay_magtape_generic.get_parameter_value('TRANSFER_RECEIVER_NAME'))
795 /* Added for Pension changes -end */
796 AND pee2.effective_start_date < ppa.start_date
797 AND paa.assignment_id = pay_magtape_generic.get_parameter_value('TRANSFER_ASSIGNMENT_ID')
798 AND pay_magtape_generic.get_parameter_value('TRANSFER_INFO_TYPE') NOT IN (300, 400, 800, 900)
799 AND ppa.effective_date BETWEEN pet.effective_start_date and pet.effective_end_date
800 AND ppa.effective_date BETWEEN pee1.effective_start_date and pee1.effective_end_date
801 GROUP BY pee1.effective_start_date,paa.assignment_id,pee1.element_entry_id,pet.element_type_id;
802
803
804 /* Modified for Pension changes to restrict to a particular Pension Provider*/
805 CURSOR get_info_record_04_details IS
806 SELECT 'RETRO_EMPLOYEE_CONTR1=P'
807 , nvl(col1.RETRO_EMPLOYEE_CONTR,0)
808 , 'RETRO_EMPLOYER_CONTR1=P'
809 , nvl(col1.RETRO_EMPLOYER_CONTR,0)
810 , 'START_DATE1=P'
811 , nvl(to_char(col1.START_DATE,'YYYYMMDD'),0)
812 , 'END_DATE1=P'
813 , nvl(to_char(col1.END_DATE,'YYYYMMDD'),0)
814 , 'RETRO_EMPLOYEE_CONTR2=P'
815 , nvl(col2.RETRO_EMPLOYEE_CONTR,0)
816 , 'RETRO_EMPLOYER_CONTR2=P'
817 , nvl(col2.RETRO_EMPLOYER_CONTR,0)
818 , 'START_DATE2=P'
819 , nvl(to_char(col2.START_DATE,'YYYYMMDD'),0)
820 , 'END_DATE2=P'
821 , nvl(to_char(col2.END_DATE,'YYYYMMDD'),0)
822 , 'RETRO_EMPLOYEE_CONTR3=P'
823 , nvl(col3.RETRO_EMPLOYEE_CONTR,0)
824 , 'RETRO_EMPLOYER_CONTR3=P'
825 , nvl(col3.RETRO_EMPLOYER_CONTR,0)
826 , 'START_DATE3=P'
827 , nvl(to_char(col3.START_DATE,'YYYYMMDD'),0)
828 , 'END_DATE3=P'
829 , nvl(to_char(col3.END_DATE,'YYYYMMDD'),0)
830 FROM (SELECT ROWNUM count
831 , RETRO_EMPLOYEE_CONTR
832 , RETRO_EMPLOYER_CONTR
833 , START_DATE
834 , END_DATE
835 FROM (SELECT prrv1.RESULT_VALUE RETRO_EMPLOYEE_CONTR
836 , prrv2.RESULT_VALUE RETRO_EMPLOYER_CONTR
837 , prr1.start_date START_DATE
838 , prr1.end_date END_DATE
839 , ROWNUM COUNT1
840 FROM pay_run_results prr1
841 , pay_run_result_values prrv1
842 , pay_run_result_values prrv3
843 , pay_element_types_f pet1
844 , pay_input_values_f piv1
845 , pay_input_values_f piv3
846 , pay_run_results prr2
847 , pay_run_result_values prrv2
848 , pay_run_result_values prrv4
849 , pay_element_types_f pet2
850 , pay_input_values_f piv2
851 , pay_input_values_f piv4
852 , pay_assignment_actions paa
853 , pay_payroll_actions ppa
854 , pay_element_entries_f pee
855 WHERE ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
856 AND prr1.ELEMENT_TYPE_ID = pet1.ELEMENT_TYPE_ID
857 AND pee.ELEMENT_ENTRY_ID = prr1.ELEMENT_ENTRY_ID
858 AND prrv1.RUN_RESULT_ID = prr1.RUN_RESULT_ID
859 AND prrv3.RUN_RESULT_ID = prr1.RUN_RESULT_ID
860 AND pet1.element_name = 'Retro Pension'
861 AND pet1.legislation_code ='DK'
862 AND piv1.ELEMENT_TYPE_ID = pet1.element_type_id
863 AND piv1.NAME ='Pay Value'
864 AND prrv1.input_value_id = piv1.input_value_id
865 AND piv3.ELEMENT_TYPE_ID = pet1.element_type_id
866 AND piv3.NAME ='Third Party Payee'
867 AND prrv3.input_value_id = piv3.input_value_id
868 AND prrv3.RESULT_VALUE = pay_magtape_generic.get_parameter_value('TRANSFER_ORGANIZATION_ID')
869 AND prr2.ELEMENT_TYPE_ID =pet2.ELEMENT_TYPE_ID
870 AND prrv2.RUN_RESULT_ID = prr2.RUN_RESULT_ID
871 AND prrv4.RUN_RESULT_ID = prr2.RUN_RESULT_ID
872 AND prrv4.RESULT_VALUE = prrv3.RESULT_VALUE
873 AND pet2.element_name = 'Retro Employer Pension'
874 AND pet2.legislation_code ='DK'
875 AND piv2.ELEMENT_TYPE_ID = pet2.element_type_id
876 AND piv2.NAME ='Pay Value'
877 AND prrv2.input_value_id = piv2.input_value_id
878 AND piv4.ELEMENT_TYPE_ID = pet2.element_type_id
879 AND piv4.NAME ='Third Party Payee'
880 AND prrv4.input_value_id = piv4.input_value_id
881 AND prrv4.RESULT_VALUE = pay_magtape_generic.get_parameter_value('TRANSFER_ORGANIZATION_ID')
882 AND prr1.assignment_action_id = paa.assignment_action_id
883 AND prr1.assignment_action_id=prr2.assignment_action_id
884 AND prr1.start_date = prr2.start_date
885 AND prr1.end_date = prr2.end_date
886 AND pay_magtape_generic.get_parameter_value('TRANSFER_INFO_TYPE') NOT IN (300, 400, 800, 900)
887 AND paa.assignment_id = pay_magtape_generic.get_parameter_value('TRANSFER_ASSIGNMENT_ID')
888 AND ppa.effective_date BETWEEN pet1.effective_start_date and pet1.effective_end_date
889 AND ppa.effective_date BETWEEN pet2.effective_start_date and pet2.effective_end_date
890 AND ppa.effective_date BETWEEN piv1.effective_start_date and piv1.effective_end_date
891 AND ppa.effective_date BETWEEN piv2.effective_start_date and piv2.effective_end_date
892 AND ppa.effective_date BETWEEN pee.effective_start_date and pee.effective_end_date
893 AND ppa.effective_date BETWEEN piv3.effective_start_date and piv3.effective_end_date
894 AND ppa.effective_date BETWEEN piv4.effective_start_date and piv4.effective_end_date
895 ORDER BY prr1.run_result_id)
896 WHERE mod(count1, 3) = 1) col1,
897 (SELECT ROWNUM count
898 , RETRO_EMPLOYEE_CONTR
899 , RETRO_EMPLOYER_CONTR
900 , START_DATE
901 , END_DATE
902 FROM (SELECT prrv1.RESULT_VALUE RETRO_EMPLOYEE_CONTR
903 , prrv2.RESULT_VALUE RETRO_EMPLOYER_CONTR
904 , prr1.start_date START_DATE
905 , prr1.end_date END_DATE
906 , ROWNUM COUNT1
907 FROM pay_run_results prr1
908 , pay_run_result_values prrv1
909 , pay_run_result_values prrv3
910 , pay_element_types_f pet1
911 , pay_input_values_f piv1
912 , pay_input_values_f piv3
913 , pay_run_results prr2
914 , pay_run_result_values prrv2
915 , pay_run_result_values prrv4
916 , pay_element_types_f pet2
917 , pay_input_values_f piv2
918 , pay_input_values_f piv4
919 , pay_assignment_actions paa
920 , pay_payroll_actions ppa
921 , pay_element_entries_f pee
922 WHERE ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
923 AND prr1.ELEMENT_TYPE_ID = pet1.ELEMENT_TYPE_ID
924 AND pee.ELEMENT_ENTRY_ID = prr1.ELEMENT_ENTRY_ID
925 AND prrv1.RUN_RESULT_ID = prr1.RUN_RESULT_ID
926 AND prrv3.RUN_RESULT_ID = prr1.RUN_RESULT_ID
927 AND pet1.element_name = 'Retro Pension'
928 AND pet1.legislation_code ='DK'
929 AND piv1.ELEMENT_TYPE_ID = pet1.element_type_id
930 AND piv1.NAME ='Pay Value'
931 AND prrv1.input_value_id = piv1.input_value_id
932 AND piv3.ELEMENT_TYPE_ID = pet1.element_type_id
933 AND piv3.NAME ='Third Party Payee'
934 AND prrv3.input_value_id = piv3.input_value_id
935 AND prrv3.RESULT_VALUE = pay_magtape_generic.get_parameter_value('TRANSFER_ORGANIZATION_ID')
936 AND prr2.ELEMENT_TYPE_ID =pet2.ELEMENT_TYPE_ID
937 AND prrv2.RUN_RESULT_ID = prr2.RUN_RESULT_ID
938 AND prrv4.RUN_RESULT_ID = prr2.RUN_RESULT_ID
939 AND prrv4.RESULT_VALUE = prrv3.RESULT_VALUE
940 AND pet2.element_name = 'Retro Employer Pension'
941 AND pet2.legislation_code ='DK'
942 AND piv2.ELEMENT_TYPE_ID = pet2.element_type_id
943 AND piv2.NAME ='Pay Value'
944 AND prrv2.input_value_id = piv2.input_value_id
945 AND piv4.ELEMENT_TYPE_ID = pet2.element_type_id
946 AND piv4.NAME ='Third Party Payee'
947 AND prrv4.input_value_id = piv4.input_value_id
948 AND prrv4.RESULT_VALUE = pay_magtape_generic.get_parameter_value('TRANSFER_ORGANIZATION_ID')
949 AND prr1.assignment_action_id = paa.assignment_action_id
950 AND prr1.assignment_action_id=prr2.assignment_action_id
951 AND prr1.start_date = prr2.start_date
952 AND prr1.end_date = prr2.end_date
953 AND pay_magtape_generic.get_parameter_value('TRANSFER_INFO_TYPE') NOT IN (300, 400, 800, 900)
954 AND paa.assignment_id = pay_magtape_generic.get_parameter_value('TRANSFER_ASSIGNMENT_ID')
955 AND ppa.effective_date BETWEEN pet1.effective_start_date and pet1.effective_end_date
956 AND ppa.effective_date BETWEEN pet2.effective_start_date and pet2.effective_end_date
957 AND ppa.effective_date BETWEEN piv1.effective_start_date and piv1.effective_end_date
958 AND ppa.effective_date BETWEEN piv2.effective_start_date and piv2.effective_end_date
959 AND ppa.effective_date BETWEEN pee.effective_start_date and pee.effective_end_date
960 AND ppa.effective_date BETWEEN piv3.effective_start_date and piv3.effective_end_date
961 AND ppa.effective_date BETWEEN piv4.effective_start_date and piv4.effective_end_date
962 ORDER BY prr1.run_result_id)
963 WHERE mod(count1, 3) = 2) col2,
964 (SELECT ROWNUM count
965 , RETRO_EMPLOYEE_CONTR
966 , RETRO_EMPLOYER_CONTR
967 , START_DATE
968 , END_DATE
969 FROM (SELECT prrv1.RESULT_VALUE RETRO_EMPLOYEE_CONTR
970 , prrv2.RESULT_VALUE RETRO_EMPLOYER_CONTR
971 , prr1.start_date START_DATE
972 , prr1.end_date END_DATE
973 , ROWNUM COUNT1
974 FROM pay_run_results prr1
975 , pay_run_result_values prrv1
976 , pay_run_result_values prrv3
977 , pay_element_types_f pet1
978 , pay_input_values_f piv1
979 , pay_input_values_f piv3
980 , pay_run_results prr2
981 , pay_run_result_values prrv2
982 , pay_run_result_values prrv4
983 , pay_element_types_f pet2
984 , pay_input_values_f piv2
985 , pay_input_values_f piv4
986 , pay_assignment_actions paa
987 , pay_payroll_actions ppa
988 , pay_element_entries_f pee
989 WHERE ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
990 AND prr1.ELEMENT_TYPE_ID = pet1.ELEMENT_TYPE_ID
991 AND pee.ELEMENT_ENTRY_ID = prr1.ELEMENT_ENTRY_ID
992 AND prrv1.RUN_RESULT_ID = prr1.RUN_RESULT_ID
993 AND prrv3.RUN_RESULT_ID = prr1.RUN_RESULT_ID
994 AND pet1.element_name = 'Retro Pension'
995 AND pet1.legislation_code ='DK'
996 AND piv1.ELEMENT_TYPE_ID = pet1.element_type_id
997 AND piv1.NAME ='Pay Value'
998 AND prrv1.input_value_id = piv1.input_value_id
999 AND piv3.ELEMENT_TYPE_ID = pet1.element_type_id
1000 AND piv3.NAME ='Third Party Payee'
1001 AND prrv3.input_value_id = piv3.input_value_id
1002 AND prrv3.RESULT_VALUE = pay_magtape_generic.get_parameter_value('TRANSFER_ORGANIZATION_ID')
1003 AND prr2.ELEMENT_TYPE_ID =pet2.ELEMENT_TYPE_ID
1004 AND prrv2.RUN_RESULT_ID = prr2.RUN_RESULT_ID
1005 AND prrv4.RUN_RESULT_ID = prr2.RUN_RESULT_ID
1006 AND prrv4.RESULT_VALUE = prrv3.RESULT_VALUE
1007 AND pet2.element_name = 'Retro Employer Pension'
1008 AND pet2.legislation_code ='DK'
1009 AND piv2.ELEMENT_TYPE_ID = pet2.element_type_id
1010 AND piv2.NAME ='Pay Value'
1011 AND prrv2.input_value_id = piv2.input_value_id
1012 AND piv4.ELEMENT_TYPE_ID = pet2.element_type_id
1013 AND piv4.NAME ='Third Party Payee'
1014 AND prrv4.input_value_id = piv4.input_value_id
1015 AND prrv4.RESULT_VALUE = pay_magtape_generic.get_parameter_value('TRANSFER_ORGANIZATION_ID')
1016 AND prr1.assignment_action_id = paa.assignment_action_id
1017 AND prr1.assignment_action_id=prr2.assignment_action_id
1018 AND prr1.start_date = prr2.start_date
1019 AND prr1.end_date = prr2.end_date
1020 AND pay_magtape_generic.get_parameter_value('TRANSFER_INFO_TYPE') NOT IN (300, 400, 800, 900)
1021 AND paa.assignment_id = pay_magtape_generic.get_parameter_value('TRANSFER_ASSIGNMENT_ID')
1022 AND ppa.effective_date BETWEEN pet1.effective_start_date and pet1.effective_end_date
1023 AND ppa.effective_date BETWEEN pet2.effective_start_date and pet2.effective_end_date
1024 AND ppa.effective_date BETWEEN piv1.effective_start_date and piv1.effective_end_date
1025 AND ppa.effective_date BETWEEN piv2.effective_start_date and piv2.effective_end_date
1026 AND ppa.effective_date BETWEEN pee.effective_start_date and pee.effective_end_date
1027 AND ppa.effective_date BETWEEN piv3.effective_start_date and piv3.effective_end_date
1028 AND ppa.effective_date BETWEEN piv4.effective_start_date and piv4.effective_end_date
1029 ORDER BY prr1.run_result_id)
1030 WHERE mod(count1, 3) = 0) col3
1031 WHERE col1.count = col2.count (+)
1032 AND col2.count = col3.count (+);
1033
1034
1035
1036 CURSOR get_info_record_05_details IS
1037 /* Restricted input to only 32 characters for bug fix 4555311 */
1038 /* Modified for bug fix 4593682 to select addresses for all employees, even if they do not have an address */
1039 SELECT 'ADDRESS_1=P'
1040 , rpad(nvl(pad.address_line1,' '),32)
1041 , 'ADDRESS_2=P'
1042 , rpad(nvl(pad.address_line2,' ') /*||' '*/,32)
1043 , 'CITY_NAME=P'
1044 , nvl(substr(PAY_DK_PAYMENT_PROCESS_PKG.get_lookup_meaning('DK_POSTCODE_TOWN',pad.postal_code),5),' ') /*||' '*/
1045 , 'POST_CODE=P'
1046 , nvl(pad.postal_code,0)
1047 FROM per_addresses pad
1048 /* Modified for bug fix 4593682 */
1049 , per_all_people_f pap
1050 , pay_payroll_actions ppa
1051 WHERE pad.person_id (+)= pap.person_id
1052 AND pad.primary_flag = 'Y' --9403004
1053 AND ppa.effective_date BETWEEN nvl(pad.date_from,ppa.effective_date) AND nvl(pad.date_to,to_date('31-12-4712','dd-mm-rrrr')) --9403004
1054 /* pad.person_id = pay_magtape_generic.get_parameter_value('TRANSFER_PERSON_ID') */
1055 AND pap.person_id = pay_magtape_generic.get_parameter_value('TRANSFER_PERSON_ID')
1056 AND pay_magtape_generic.get_parameter_value('TRANSFER_INFO_TYPE') NOT IN (300, 400, 800, 900)
1057 /* Modified for bug fix 7664874 */
1058 AND ppa.payroll_action_id=pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
1059 AND ppa.effective_date BETWEEN pap.effective_start_date AND pap.effective_end_date;
1060
1061 END PAY_DK_PAYMENT_PROCESS_PKG;