eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE: APPS.PAY_DK_PAYMENT_PROCESS_PKG

Source


1 PACKAGE PAY_DK_PAYMENT_PROCESS_PKG as
2 /* $Header: pydkpaypr.pkh 120.8.12000000.4 2007/08/24 05:23:54 saurai noship $ */
3 
4 level_cnt NUMBER;
5 
6 
7 FUNCTION get_parameter(
8                  p_parameter_string  IN VARCHAR2
9                 ,p_token             IN VARCHAR2
10                 ,p_segment_number    IN NUMBER DEFAULT NULL )RETURN VARCHAR2;
11 
12 FUNCTION get_lookup_meaning (p_lookup_type varchar2,p_lookup_code varchar2) RETURN VARCHAR2 ;
13 
14 
15 /* Added for Third Party Payments */
16 FUNCTION get_ass_action_context(p_assignment_id NUMBER) RETURN NUMBER;
17 
18 FUNCTION get_date_earned_context(p_assignment_id NUMBER) RETURN DATE;
19 
20 --FUNCTION get_prev_bal_paid(p_assignment_id NUMBER, p_balance_name VARCHAR2) RETURN NUMBER;
21   /* Added p_org_id to function for pension changes */
22 FUNCTION get_prev_bal_paid(p_assignment_id NUMBER, p_org_id NUMBER, p_balance_name VARCHAR2) RETURN NUMBER;
23 
24 FUNCTION get_phy_record_no(p_person_id NUMBER, p_assignment_id NUMBER, p_pp_id VARCHAR2) RETURN NUMBER;
25 
26 /* Added for bug fix 4563148 */
27 FUNCTION check_numeric(p_text VARCHAR2) RETURN NUMBER;
28 
29 /* Added during Holiday Pay plug-in and OS I10 enhancement */
30 /* Changed to return Varchar2 for bug */
31 FUNCTION get_pension_provider(p_org_name VARCHAR2) RETURN VARCHAR2;
32 
33 FUNCTION get_ident_codes(p_bg_id               IN  NUMBER
34                         ,p_effective_date      IN DATE
35 			,p_tax_rc              OUT NOCOPY VARCHAR2
36 			,p_amb_rc              OUT NOCOPY VARCHAR2
37 			,p_sp_rc               OUT NOCOPY VARCHAR2
38 			,p_hol_days_rc         OUT NOCOPY VARCHAR2) RETURN NUMBER;
39 /* Added to support multiple pensions for OSI02 for bug fix 5563150*/
40 FUNCTION get_pen_values(p_eff_date DATE,p_ele_type_id NUMBER, p_ee_id NUMBER, p_iv_name VARCHAR2) RETURN VARCHAR2;
41 
42 /* Added to support override for Use of Holiday Card for transfer to Holiday Bank for bug fix 5533140*/
43 FUNCTION get_use_hol_card(p_payroll_action_id NUMBER,p_date_earned DATE ) RETURN VARCHAR2;
44 
45 FUNCTION get_pay_period_per_year(p_payroll_action_id NUMBER,p_date_earned DATE ) RETURN NUMBER;
46 
47 
48 
49 
50 CURSOR get_ds_record_details IS
51 SELECT   'CHECK_DIGIT_DS=P'
52        ,  to_char(PAY_DK_PAYMENT_PROCESS_PKG.get_parameter(legislative_parameters,'CHECK_DIGIT_DS',null))
53        , 'TRANSFER_DS_CVR_NO=P'
54        ,  hoi2.org_information1
55        , 'IDENTIFICATION_DELIVERY=P'
56        ,  fnd_global.conc_request_id
57        , 'TRANSFER_IDENTIFICATION_DS=P'
58        ,  PAY_DK_PAYMENT_PROCESS_PKG.get_parameter(legislative_parameters,'IDENTIFICATION_DS',null)
59        , 'TRANSFER_PAYROLL_NAME=P'
60        ,  pap.PAYROLL_NAME
61        , 'CONSOLIDATION_NAME=P'
62        ,  pcs.consolidation_set_name
63        , 'START_DATE=P'
64        ,  to_char(ppa.start_date,'YYYYMMDD')
65        , 'END_DATE=P'
66        ,  to_char(ppa.effective_date,'YYYYMMDD')
67        , 'PAYMENT_METHOD=P'
68        ,  pop.org_payment_method_name
69        , 'DS_NAME=P'
70        ,  hou.name
71        , 'TRANSFER_PAYER_REG_NO=P'
72        ,  pea.segment1
73        , 'TRANSFER_PAYER_ACCT_NO=P'
74        ,  pea.segment3
75 FROM    hr_organization_units		hou
76       , hr_organization_information	hoi1
77       , hr_organization_information	hoi2
78       , pay_payroll_actions		ppa
79       , pay_consolidation_sets          pcs
80       , pay_all_payrolls_f		pap
81       , pay_external_accounts		pea
82       , pay_org_payment_methods_f	pop
83 WHERE ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
84 AND pap.payroll_id = ppa.payroll_id
85 AND pcs.consolidation_set_id = pap.consolidation_set_id
86 AND pop.org_payment_method_id = ppa.org_payment_method_id
87 AND pop.external_account_id   = pea.external_account_id
88 AND hou.business_group_id = ppa.business_group_id
89 AND hoi1.organization_id = hou.organization_id
90 AND hou.organization_id = nvl(PAY_DK_PAYMENT_PROCESS_PKG.get_parameter(legislative_parameters,'DS_NAME',null),hou.organization_id)
91 AND hoi1.org_information_context='CLASS'
92 AND hoi1.org_information1 = nvl2(PAY_DK_PAYMENT_PROCESS_PKG.get_parameter(legislative_parameters,'DS_NAME',null),'HR_LEGAL_EMPLOYER','DK_SERVICE_PROVIDER' )
93 AND hoi1.org_information2 ='Y'
94 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')
95 AND hoi2.organization_id =  hoi1.organization_id
96 AND ppa.effective_date BETWEEN hou.date_from AND nvl(hou.date_to, ppa.effective_date)
97 AND ppa.effective_date BETWEEN pap.effective_start_date AND pap.effective_end_date;
98 
99 /* Added context for PAYROLL_ACTION_ID for enh 6344939 */
100 CURSOR get_section_record_details IS
101 SELECT   'TRANSFER_DISPOSAL_DATE=P'
102        , to_char(fnd_date.canonical_to_date(PAY_DK_PAYMENT_PROCESS_PKG.get_parameter(legislative_parameters,'PAYMENT_DD',null)),'DDMMYY')
103        , 'TRANSFER_PAYER_CVR_NO=P'
104        , hoi2.org_information1
105        , 'TRANSFER_LE_ID=P'
106        , to_char(hou.ORGANIZATION_ID)
107        , 'PAYROLL_ACTION_ID=C'
108        , pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
109 FROM       pay_payroll_actions		ppa
110        , hr_organization_units		hou
111        , hr_organization_information	hoi1
112        , hr_organization_information	hoi2
113 WHERE ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
114 AND hou.business_group_id =  ppa.business_group_id
115 AND hoi1.organization_id = hou.organization_id
116 AND hoi1.org_information_context='CLASS'
117 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
118 AND hoi1.org_information2 = 'Y'
119 AND hoi2.org_information_context='DK_LEGAL_ENTITY_DETAILS'
120 AND hoi2.organization_id =  hoi1.organization_id
121 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))
122 AND ppa.effective_date BETWEEN hou.date_from AND nvl(hou.date_to, ppa.effective_date);
123 
124 
125 CURSOR get_transfer_record_details IS
126 SELECT   'PAYEE_REG_NO=P'
127 	, pea.segment1
128 	, 'PAYEE_ACCT_NO=P'
129 	, pea.segment3
130 	, 'PAYEE_AMOUNT=P'
131 	, to_char(ppp.value*100)
132 	, 'IDENTIFICATION_PAYEE=P'
133 	, 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)
134 	, 'FULL_NAME=P'
135 	, pap.full_name
136 FROM per_all_assignments_f		paf
137    , per_all_people_f			pap
138    , hr_soft_coding_keyflex		scl
139    , pay_payroll_actions		ppa
140    , pay_assignment_actions		paa
141    , pay_pre_payments			ppp
142    , pay_external_accounts		pea
143    , pay_personal_payment_methods_f	ppm
144 WHERE ppa.payroll_action_id =  pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
145 AND paf.business_group_id = ppa.business_group_id
146 AND paf.payroll_id = ppa.PAYROLL_ID
147 AND pap.per_information_category ='DK'
148 AND paf.person_id = pap.person_id
149 AND paf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
150 AND ppa.effective_date  between  paf.effective_start_date  AND paf.effective_end_date
151 AND ppa.effective_date  between  pap.effective_start_date  AND pap.effective_end_date
152 AND scl.enabled_flag = 'Y'
153 AND scl.segment1 = pay_magtape_generic.get_parameter_value('TRANSFER_LE_ID')
154 AND    paa.payroll_action_id = ppa.payroll_action_id
155 AND    ppp.pre_payment_id  = paa.pre_payment_id
156 AND    paa.assignment_id = paf.assignment_id
157 AND    ppm.personal_payment_method_id  = ppp.personal_payment_method_id
158 AND    ppp.value > 0
159 AND ppm.external_account_id   = pea.external_account_id
160 AND ppa.effective_date  BETWEEN  ppm.effective_start_date  AND ppm.effective_end_date;
161 
162 /* Added the following for Third Party Payments */
163 
164 CURSOR get_info_record_details IS
165 SELECT  'TRANSFER_INFO_TYPE=P'
166        , puci.value
167        , 'TRANSFER_INFO_DISPOSAL_DATE=P'
168        , to_char(fnd_date.canonical_to_date(PAY_DK_PAYMENT_PROCESS_PKG.get_parameter(legislative_parameters,'INFOTYPE'||puci.value||'_DD',null)),'DDMMYY')
169        , 'TRANSFER_PBS_NO=P'
170        , puci1.value
171        , 'TRANSFER_RECEIVER_NAME=P'
172        , pur.row_low_range_or_name
173 FROM     pay_payroll_actions		ppa
174        , pay_user_tables		put
175        , pay_user_columns		puc
176        , pay_user_column_instances_f	puci
177        , pay_user_rows_f		pur
178        , pay_user_columns puc1
179        , pay_user_column_instances_f puci1
180        , pay_user_rows_f pur1
181 WHERE ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
182 AND put.user_table_name = 'DK_PBS_DATA'
183 AND put.LEGISLATION_CODE ='DK'
184 AND puc.user_table_id = put.user_table_id
185 AND puc.user_column_name =  'Information Type'
186 AND puci.user_column_id = puc.user_column_id
187 AND ppa.effective_date between puci.effective_start_date and puci.effective_end_date
188 AND pur.user_row_id = puci.user_row_id
189 AND ppa.effective_date between pur.effective_start_date and pur.effective_end_date
190 and puc1.user_table_id = put.user_table_id
191 and puc1.user_column_name = 'PBS Number'
192 and puci1.user_column_id = puc1.user_column_id
193 /* Added for bug fix 5071004 */
194 and puci1.business_group_id =ppa.business_group_id
195 and ppa.effective_date between puci1.effective_start_date and puci1.effective_end_date
196 and pur1.user_row_id = puci1.user_row_id
197 and pur1.user_row_id = pur.user_row_id
198 and ppa.effective_date  between pur1.effective_start_date and pur1.effective_end_date
199 UNION
200 SELECT  'TRANSFER_INFO_TYPE=P'
201        , hoi2.ORG_INFORMATION2
202        , 'TRANSFER_INFO_DISPOSAL_DATE=P'
203        , to_char(fnd_date.canonical_to_date(PAY_DK_PAYMENT_PROCESS_PKG.get_parameter(legislative_parameters,'INFOTYPE_PENSION_DD',null)),'DDMMYY')
204        , 'TRANSFER_PBS_NO=P'
205        , hoi2.ORG_INFORMATION1
206        , 'TRANSFER_RECEIVER_NAME=P'
207        , hou.name
208 FROM     pay_payroll_actions		ppa
209        , hr_organization_units		hou
210        , hr_organization_information	hoi1
211        , hr_organization_information	hoi2
212 WHERE ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
213 AND hou.business_group_id =  ppa.business_group_id
214 AND hoi1.organization_id = hou.organization_id
215 AND hoi1.org_information_context='CLASS'
216 AND hoi1.org_information1 = 'DK_PENSION_PROVIDER'
217 AND hoi1.org_information2 = 'Y'
218 AND hoi2.org_information_context='DK_PENSION_PROVIDER_DETAILS'
219 AND hoi2.organization_id =  hoi1.organization_id
220 AND ppa.effective_date BETWEEN hou.date_from AND nvl(hou.date_to, ppa.effective_date);
221 
222 
223 /* Modified for bug fix 4551283 to change FULL_NAME to Employer's name*/
224 /* Modified for bug fix 4554812 to filter on pension provider */
225 /* Modified for bug fix 5887000 for PAYMENT_END_DATE */
226 /* Modified for pension changes , also added UNION */
227 CURSOR get_info_record_00_details IS
228 SELECT    'IDENTIFICATION_PAYEE=P'
229 	,  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)
230 	, 'FULL_NAME=P'
231 	,  pap.full_name
232 	, 'PAYMENT_START_DATE=P'
233 	,  to_char(ppa.START_DATE,'YYYYMMDD')
234 	, 'PAYMENT_END_DATE=P'
235 	,  to_char(PAY_DK_PAYMENT_PROCESS_PKG.get_date_earned_context(paa.ASSIGNMENT_ID),'YYYYMMDD')
236 	, 'ASSIGNMENT_ACTION_ID=C'
237         , PAY_DK_PAYMENT_PROCESS_PKG.get_ass_action_context(paa.ASSIGNMENT_ID)
238 	, 'TRANSFER_ASSIGNMENT_ID=P'
239 	, paa.ASSIGNMENT_ID
240 	, 'TRANSFER_TERMINATION_DATE=P'
241 	/* Re-written to obtain correct dates*/
242         /*, to_char(fnd_date.canonical_to_date(nvl(scl.segment8,pap.effective_end_date)),'YYYYMMDD')*/
243 	, to_char(decode(scl.segment8,null,pap.effective_end_date,fnd_date.canonical_to_date(scl.segment8)),'YYYYMMDD')
244 	, 'TRANSFER_PERSON_ID=P'
245 	, to_char(pap.person_id)
246 	, 'TRANSFER_EMPLOYMENT_CATEGORY=P'
247 	, paf.employment_category
248 	, 'PHYS_RECO_NO=P'
249 	, to_char(PAY_DK_PAYMENT_PROCESS_PKG.get_phy_record_no(pap.person_id, paf.assignment_id,peev.screen_entry_value))
250 	/* Added for Holiday pay plug-in and OS I 10 enhancement */
251 	, 'EFFECTIVE_DATE=P'
252 	, to_char(ppa.effective_date)
253 	, 'BUSINESS_GROUP_ID=P'
254 	, to_char(ppa.business_group_id)
255         , 'PAYROLL_ACTION_ID=C'
256         , pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
257         , 'ORGANIZATION_ID=C'
258         , peev.screen_entry_value
259         , 'TRANSFER_ORGANIZATION_ID=P'
260         , peev.screen_entry_value
261 	/* Added for bug fix 5533140 */
262         , 'ASSIGNMENT_ID=C'
263         , paa.ASSIGNMENT_ID
264         , 'DATE_EARNED=C'
265         , fnd_date.date_to_canonical(PAY_DK_PAYMENT_PROCESS_PKG.get_date_earned_context(paa.ASSIGNMENT_ID))
266 FROM per_all_assignments_f		paf
267    , per_all_people_f			pap
268    , hr_soft_coding_keyflex		scl
269    , pay_payroll_actions		ppa
270    , pay_assignment_actions		paa
271    , pay_pre_payments			ppp
272    , pay_external_accounts		pea
273    , pay_personal_payment_methods_f	ppm
274    /* Added join for bug fix 4554812 */
275    /* Removed and re-wrote in function get_pension_provider */
276    /*, hr_organization_units		hou */
277    /* Added for Pension changes */
278    ,pay_element_entries_f		peef
279    ,pay_element_types_f			petf
280    ,pay_input_values_f			pivf
281    ,pay_element_entry_values_f		peev
282 WHERE ppa.payroll_action_id =  pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
283 AND paf.business_group_id = ppa.business_group_id
284 AND paf.payroll_id = ppa.payroll_id
285 AND pap.per_information_category ='DK'
286 AND paf.person_id = pap.person_id
287 AND paf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
288 AND ppa.effective_date  between  paf.effective_start_date  AND paf.effective_end_date
289 AND ppa.effective_date  between  pap.effective_start_date  AND pap.effective_end_date
290 AND scl.enabled_flag = 'Y'
291 AND scl.segment1 = pay_magtape_generic.get_parameter_value('TRANSFER_LE_ID')
292 /* Added for bug fix 4554812 */
293 /* Removed and re-wrote in function get_pension_provider */
294 /*AND hou.name = pay_magtape_generic.get_parameter_value('TRANSFER_RECEIVER_NAME')*/
295 /* Removed and re-written for pension changes to get pension provider */
296 /*AND nvl(scl.segment2,0) = decode(pay_magtape_generic.get_parameter_value('TRANSFER_INFO_TYPE')
297                           , 300 , nvl(scl.segment2,0)
298 			  , 400 , nvl(scl.segment2,0)
302 */
299 			  , 800 , nvl(scl.segment2,0)
300 			  , 900 , nvl(scl.segment2,0)
301 			  , PAY_DK_PAYMENT_PROCESS_PKG.get_pension_provider(pay_magtape_generic.get_parameter_value('TRANSFER_RECEIVER_NAME')))--to_char(hou.organization_id))
303 /* Added for Pension changes -start */
304 AND  pay_magtape_generic.get_parameter_value('TRANSFER_INFO_TYPE') NOT IN (300, 400, 800, 900)
305 AND  peef.assignment_id  = paf.assignment_id
306 AND  ppa.effective_date BETWEEN peef.effective_start_date AND peef.effective_end_date
307 AND  peef.element_type_id   = petf.element_type_id
308 AND  petf.legislation_code  ='DK'
309 AND  petf.element_name  =  'Pension'
310 AND  ppa.effective_date BETWEEN petf.effective_start_date AND petf.effective_end_date
311 AND  pivf.element_type_id   = petf.element_type_id
312 AND  pivf.input_value_id    = peev.input_value_id
313 AND  pivf.name= 'Third Party Payee'
314 AND  peev.element_entry_id = peef.element_entry_id
315 AND  peev.screen_entry_value = PAY_DK_PAYMENT_PROCESS_PKG.get_pension_provider(pay_magtape_generic.get_parameter_value('TRANSFER_RECEIVER_NAME'))
316 AND  ppa.effective_date BETWEEN pivf.effective_start_date AND pivf.effective_end_date
317 /* Added for Pension changes -end */
318 AND paa.payroll_action_id = ppa.payroll_action_id
319 AND ppp.pre_payment_id  = paa.pre_payment_id
320 AND paa.assignment_id = paf.assignment_id
321 AND ppm.personal_payment_method_id  = ppp.personal_payment_method_id
322 AND ppp.value > 0
323 AND ppm.external_account_id   = pea.external_account_id
324 AND ppa.effective_date  BETWEEN  ppm.effective_start_date  AND ppm.effective_end_date
325 UNION
326 SELECT    'IDENTIFICATION_PAYEE=P'
327 	,  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)
328 	, 'FULL_NAME=P'
329 	,  pap.full_name
330 	, 'PAYMENT_START_DATE=P'
331 	,  to_char(ppa.START_DATE,'YYYYMMDD')
332 	, 'PAYMENT_END_DATE=P'
333 	,  to_char(PAY_DK_PAYMENT_PROCESS_PKG.get_date_earned_context(paa.ASSIGNMENT_ID),'YYYYMMDD')
334 	, 'ASSIGNMENT_ACTION_ID=C'
335         , PAY_DK_PAYMENT_PROCESS_PKG.get_ass_action_context(paa.ASSIGNMENT_ID)
336 	, 'TRANSFER_ASSIGNMENT_ID=P'
337 	, paa.ASSIGNMENT_ID
338 	, 'TRANSFER_TERMINATION_DATE=P'
339 	/* Re-written to obtain correct dates*/
340         /*, to_char(fnd_date.canonical_to_date(nvl(scl.segment8,pap.effective_end_date)),'YYYYMMDD')*/
341 	, to_char(decode(scl.segment8,null,pap.effective_end_date,fnd_date.canonical_to_date(scl.segment8)),'YYYYMMDD')
342 	, 'TRANSFER_PERSON_ID=P'
343 	, to_char(pap.person_id)
344 	, 'TRANSFER_EMPLOYMENT_CATEGORY=P'
345 	, paf.employment_category
346 	, 'PHYS_RECO_NO=P'
347 	, to_char(PAY_DK_PAYMENT_PROCESS_PKG.get_phy_record_no(pap.person_id, paf.assignment_id,null))
348 	/* Added for Holiday pay plug-in and OS I 10 enhancement */
349 	, 'EFFECTIVE_DATE=P'
350 	, to_char(ppa.effective_date)
351 	, 'BUSINESS_GROUP_ID=P'
352 	, to_char(ppa.business_group_id)
353         , 'PAYROLL_ACTION_ID=C'
354         , pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
355         , 'ORGANIZATION_ID=C'
356         , null
357         , 'TRANSFER_ORGANIZATION_ID=P'
358         , null
359 	/* Added for bug fix 5533140 */
360         , 'ASSIGNMENT_ID=C'
361         , paa.ASSIGNMENT_ID
362         , 'DATE_EARNED=C'
363         , fnd_date.date_to_canonical(PAY_DK_PAYMENT_PROCESS_PKG.get_date_earned_context(paa.ASSIGNMENT_ID))
364 FROM per_all_assignments_f		paf
365    , per_all_people_f			pap
369    , pay_pre_payments			ppp
366    , hr_soft_coding_keyflex		scl
367    , pay_payroll_actions		ppa
368    , pay_assignment_actions		paa
370    , pay_external_accounts		pea
371    , pay_personal_payment_methods_f	ppm
372    /* Added join for bug fix 4554812 */
373    /* Removed and re-wrote in function get_pension_provider */
374    /*, hr_organization_units		hou */
375 WHERE ppa.payroll_action_id =  pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
376 AND paf.business_group_id = ppa.business_group_id
377 AND paf.payroll_id = ppa.payroll_id
378 AND pap.per_information_category ='DK'
379 AND paf.person_id = pap.person_id
380 AND paf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
381 AND ppa.effective_date  between  paf.effective_start_date  AND paf.effective_end_date
382 AND ppa.effective_date  between  pap.effective_start_date  AND pap.effective_end_date
383 AND scl.enabled_flag = 'Y'
384 AND scl.segment1 = pay_magtape_generic.get_parameter_value('TRANSFER_LE_ID')
385 /* Added for bug fix 4554812 */
386 /* Removed and re-wrote in function get_pension_provider */
387 /*AND hou.name = pay_magtape_generic.get_parameter_value('TRANSFER_RECEIVER_NAME')*/
388 /* Removed and re-written for pension changes to get pension provider */
389 /*AND nvl(scl.segment2,0) = decode(pay_magtape_generic.get_parameter_value('TRANSFER_INFO_TYPE')
390                           , 300 , nvl(scl.segment2,0)
391 			  , 400 , nvl(scl.segment2,0)
392 			  , 800 , nvl(scl.segment2,0)
393 			  , 900 , nvl(scl.segment2,0)
394 			  , PAY_DK_PAYMENT_PROCESS_PKG.get_pension_provider(pay_magtape_generic.get_parameter_value('TRANSFER_RECEIVER_NAME')))--to_char(hou.organization_id))
395 */
396 /* Added for Pension changes -start */
397 AND  pay_magtape_generic.get_parameter_value('TRANSFER_INFO_TYPE') IN (300, 400, 800, 900)
398 /* Added for Pension changes -end */
399 AND paa.payroll_action_id = ppa.payroll_action_id
400 AND ppp.pre_payment_id  = paa.pre_payment_id
401 AND paa.assignment_id = paf.assignment_id
402 AND ppm.personal_payment_method_id  = ppp.personal_payment_method_id
403 AND ppp.value > 0
404 AND ppm.external_account_id   = pea.external_account_id
405 AND ppa.effective_date  BETWEEN  ppm.effective_start_date  AND ppm.effective_end_date;
406 
407 
408 /* Modified for bug fix 4551283 to change FULL_NAME to Employer's name*/
409 /* Modified for Pension changes */
410 CURSOR get_info_record_01_details IS
411 SELECT   'TRANSFER_PAY_APPL_DATE=P'
412        , to_char(min(pee2.effective_start_date) ,'YYYYMMDD')
413        , 'TRANSFER_PENSION_START_DATE=P'
414        , to_char(pee1.effective_start_date ,'YYYYMMDD')
415        , 'ASSIGNMENT_ID=C'
416        , paa.ASSIGNMENT_ID
417        , 'DATE_EARNED=C'
418        , fnd_date.date_to_canonical(PAY_DK_PAYMENT_PROCESS_PKG.get_date_earned_context(paa.ASSIGNMENT_ID))
419        , 'PAYROLL_ACTION_ID=C'
420        , pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
421        , 'FULL_NAME=P'
422        , hou.name /*bug fix 4551283*/
423 FROM pay_payroll_actions		ppa
424    , pay_assignment_actions		paa
425    , pay_element_entries_f              pee1
426    , pay_element_types_f                pet
427    , pay_element_entries_f              pee2
428    , hr_organization_units              hou /*bug fix 4551283*/
429    /* Added for Pension changes */
430    , pay_input_values_f			pivf
431    , pay_element_entry_values_f		peev1
432    , pay_element_entry_values_f		peev2
433 WHERE  ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
434 AND    paa.payroll_action_id = ppa.payroll_action_id
435 AND    pee1.assignment_id = paa.assignment_id
436 AND    pet.element_name  = 'Pension'
437 AND    pet.legislation_code ='DK'
438 AND    pee1.entry_type ='E'
439 AND    pee1.element_type_id = pet.element_type_id
440 AND    pee2.assignment_id = paa.assignment_id
441 AND    pee2.entry_type ='E'
442 AND    pee2.element_type_id = pet.element_type_id
443 /* Added for Pension changes -start */
444 AND  pivf.element_type_id   = pet.element_type_id
445 AND  pivf.name= 'Third Party Payee'
446 AND  ppa.effective_date BETWEEN pivf.effective_start_date AND pivf.effective_end_date
447 AND  peev1.input_value_id = pivf.input_value_id
448 AND  peev1.element_entry_id = pee1.element_entry_id
449 AND  peev1.screen_entry_value = PAY_DK_PAYMENT_PROCESS_PKG.get_pension_provider(pay_magtape_generic.get_parameter_value('TRANSFER_RECEIVER_NAME'))
450 AND  peev2.input_value_id = pivf.input_value_id
451 AND  peev2.element_entry_id = pee2.element_entry_id
452 AND  peev2.screen_entry_value = PAY_DK_PAYMENT_PROCESS_PKG.get_pension_provider(pay_magtape_generic.get_parameter_value('TRANSFER_RECEIVER_NAME'))
453 /* Added for Pension changes -end */
454 AND    paa.assignment_id = pay_magtape_generic.get_parameter_value('TRANSFER_ASSIGNMENT_ID')
455 AND    ppa.effective_date BETWEEN pet.effective_start_date and pet.effective_end_date
456 AND    ppa.effective_date BETWEEN pee1.effective_start_date and pee1.effective_end_date
457 AND    pee1.effective_start_date >= ppa.start_date
458 AND    pay_magtape_generic.get_parameter_value('TRANSFER_INFO_TYPE') NOT IN (300, 400, 800, 900)
459 AND    hou.organization_id = pay_magtape_generic.get_parameter_value('TRANSFER_LE_ID') /*bug fix 4551283*/
460 AND    ppa.effective_date  BETWEEN  hou.date_from AND nvl(hou.date_to, ppa.effective_date) /*bug fix 4551283*/
461 GROUP BY pee1.effective_start_date,paa.assignment_id,pee1.element_entry_id,pet.element_type_id,hou.name;
462 
463 
464 /* Modified for Pension changes */
465 CURSOR get_info_record_02_details IS
466 SELECT   'TRANSFER_PAY_APPL_DATE=P'
470        , 'ASSIGNMENT_ID=C'
467        , to_char(min(pee2.effective_start_date) ,'YYYYMMDD')
468        , 'TRANSFER_PENSION_START_DATE=P'
469        , to_char(pee1.effective_start_date ,'YYYYMMDD')
471        , paa.ASSIGNMENT_ID
472        , 'DATE_EARNED=C'
473        , fnd_date.date_to_canonical(PAY_DK_PAYMENT_PROCESS_PKG.get_date_earned_context(paa.ASSIGNMENT_ID))
474        , 'ASSIGNMENT_ACTION_ID=C'
475        , PAY_DK_PAYMENT_PROCESS_PKG.get_ass_action_context(paa.ASSIGNMENT_ID)
476        , 'PAYROLL_ACTION_ID=C'
477        , pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
478        /* Added for bug fix 5563150 */
479        , 'ELEMENT_ENTRY_ID=P'
480        , to_char(pee1.element_entry_id)
481        , 'ELEMENT_TYPE_ID=P'
482        , to_char(pet.element_type_id)
483 FROM pay_payroll_actions		ppa
484    , pay_assignment_actions		paa
485    , pay_element_entries_f              pee1
486    , pay_element_types_f                pet
487    , pay_element_entries_f              pee2
488    /* Added for Pension changes */
489    , pay_input_values_f			pivf
490    , pay_element_entry_values_f		peev1
491    , pay_element_entry_values_f		peev2
492 WHERE  ppa.payroll_action_id =  pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
493 AND    paa.payroll_action_id = ppa.payroll_action_id
494 AND    pee1.assignment_id = paa.assignment_id
495 AND    pet.element_name  = 'Pension'
496 AND    pet.legislation_code ='DK'
497 AND    pee1.entry_type ='E'
498 AND    pee1.element_type_id = pet.element_type_id
499 AND    pee2.assignment_id = paa.assignment_id
500 AND    pee2.entry_type ='E'
501 AND    pee2.element_type_id = pet.element_type_id
502 /* Added for Pension changes -start */
503 AND  pivf.element_type_id   = pet.element_type_id
504 AND  pivf.name= 'Third Party Payee'
505 AND  ppa.effective_date BETWEEN pivf.effective_start_date AND pivf.effective_end_date
506 AND  peev1.input_value_id = pivf.input_value_id
507 AND  peev1.element_entry_id = pee1.element_entry_id
508 AND  peev1.screen_entry_value = PAY_DK_PAYMENT_PROCESS_PKG.get_pension_provider(pay_magtape_generic.get_parameter_value('TRANSFER_RECEIVER_NAME'))
509 AND  peev2.input_value_id = pivf.input_value_id
510 AND  peev2.element_entry_id = pee2.element_entry_id
511 AND  peev2.screen_entry_value = PAY_DK_PAYMENT_PROCESS_PKG.get_pension_provider(pay_magtape_generic.get_parameter_value('TRANSFER_RECEIVER_NAME'))
512 /* Added for Pension changes -end */
513 AND    paa.assignment_id = pay_magtape_generic.get_parameter_value('TRANSFER_ASSIGNMENT_ID')
514 AND    pay_magtape_generic.get_parameter_value('TRANSFER_INFO_TYPE') NOT IN (300, 400, 800, 900)
515 AND    ppa.effective_date BETWEEN pet.effective_start_date and pet.effective_end_date
516 AND    ppa.effective_date BETWEEN pee1.effective_start_date and pee1.effective_end_date
517 GROUP BY pee1.effective_start_date,paa.assignment_id,pee1.element_entry_id,pet.element_type_id;
518 
519 
520 /* Modified for Pension changes */
521 CURSOR get_info_record_03_details IS
522 SELECT   'TRANSFER_PAY_APPL_DATE=P'
523        , to_char(min(pee2.effective_start_date),'YYYYMMDD')
524        , 'TRANSFER_PENSION_START_DATE=P'
525        , to_char(pee1.effective_start_date ,'YYYYMMDD')
526        , 'ASSIGNMENT_ACTION_ID=C'
527        , PAY_DK_PAYMENT_PROCESS_PKG.get_ass_action_context(paa.ASSIGNMENT_ID)
528        , 'ASSIGNMENT_ID=C'
529        , paa.ASSIGNMENT_ID
530        ,'DATE_EARNED=C'
531        , fnd_date.date_to_canonical(PAY_DK_PAYMENT_PROCESS_PKG.get_date_earned_context(paa.ASSIGNMENT_ID))
532        , 'PAYROLL_ACTION_ID=C'
533        , pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
534 FROM pay_payroll_actions		ppa
535    , pay_assignment_actions		paa
536    , pay_element_entries_f              pee1
537    , pay_element_types_f                pet
538    , pay_element_entries_f              pee2
539    /* Added for Pension changes */
540    , pay_input_values_f			pivf
541    , pay_element_entry_values_f		peev1
542    , pay_element_entry_values_f		peev2
543 WHERE  ppa.payroll_action_id =  pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
544 AND    paa.payroll_action_id = ppa.payroll_action_id
545 AND    pee1.assignment_id = paa.assignment_id
546 AND    pet.element_name  = 'Pension'
547 AND    pet.legislation_code ='DK'
548 AND    pee1.entry_type ='E'
549 AND    pee1.element_type_id = pet.element_type_id
550 AND    pee2.assignment_id = paa.assignment_id
551 AND    pee2.entry_type ='E'
552 AND    pee2.element_type_id = pet.element_type_id
553 /* Added for Pension changes -start */
554 AND  pivf.element_type_id   = pet.element_type_id
555 AND  pivf.name= 'Third Party Payee'
556 AND  ppa.effective_date BETWEEN pivf.effective_start_date AND pivf.effective_end_date
557 AND  peev1.input_value_id = pivf.input_value_id
558 AND  peev1.element_entry_id = pee1.element_entry_id
559 AND  peev1.screen_entry_value = PAY_DK_PAYMENT_PROCESS_PKG.get_pension_provider(pay_magtape_generic.get_parameter_value('TRANSFER_RECEIVER_NAME'))
560 AND  peev2.input_value_id = pivf.input_value_id
561 AND  peev2.element_entry_id = pee2.element_entry_id
562 AND  peev2.screen_entry_value = PAY_DK_PAYMENT_PROCESS_PKG.get_pension_provider(pay_magtape_generic.get_parameter_value('TRANSFER_RECEIVER_NAME'))
563 /* Added for Pension changes -end */
564 AND    pee2.effective_start_date < ppa.start_date
565 AND    paa.assignment_id = pay_magtape_generic.get_parameter_value('TRANSFER_ASSIGNMENT_ID')
566 AND    pay_magtape_generic.get_parameter_value('TRANSFER_INFO_TYPE') NOT IN (300, 400, 800, 900)
567 AND    ppa.effective_date BETWEEN pet.effective_start_date and pet.effective_end_date
571 
568 AND    ppa.effective_date BETWEEN pee1.effective_start_date and pee1.effective_end_date
569 GROUP BY pee1.effective_start_date,paa.assignment_id,pee1.element_entry_id,pet.element_type_id;
570 
572 /* Modified for Pension changes to restrict to a particular Pension Provider*/
573 CURSOR get_info_record_04_details IS
574 SELECT 'RETRO_EMPLOYEE_CONTR1=P'
575      , nvl(col1.RETRO_EMPLOYEE_CONTR,0)
576      , 'RETRO_EMPLOYER_CONTR1=P'
577      , nvl(col1.RETRO_EMPLOYER_CONTR,0)
578      , 'START_DATE1=P'
579      , nvl(to_char(col1.START_DATE,'YYYYMMDD'),0)
580      , 'END_DATE1=P'
581      , nvl(to_char(col1.END_DATE,'YYYYMMDD'),0)
582      , 'RETRO_EMPLOYEE_CONTR2=P'
583      , nvl(col2.RETRO_EMPLOYEE_CONTR,0)
584      , 'RETRO_EMPLOYER_CONTR2=P'
585      , nvl(col2.RETRO_EMPLOYER_CONTR,0)
586      , 'START_DATE2=P'
587      , nvl(to_char(col2.START_DATE,'YYYYMMDD'),0)
588      , 'END_DATE2=P'
589      , nvl(to_char(col2.END_DATE,'YYYYMMDD'),0)
590      , 'RETRO_EMPLOYEE_CONTR3=P'
591      , nvl(col3.RETRO_EMPLOYEE_CONTR,0)
592      , 'RETRO_EMPLOYER_CONTR3=P'
593      , nvl(col3.RETRO_EMPLOYER_CONTR,0)
594      , 'START_DATE3=P'
595      , nvl(to_char(col3.START_DATE,'YYYYMMDD'),0)
596      , 'END_DATE3=P'
597      , nvl(to_char(col3.END_DATE,'YYYYMMDD'),0)
598 FROM	(SELECT		ROWNUM count
599 		      , RETRO_EMPLOYEE_CONTR
600 		      , RETRO_EMPLOYER_CONTR
601 		      , START_DATE
602 		      , END_DATE
603 	 FROM   (SELECT	prrv1.RESULT_VALUE       RETRO_EMPLOYEE_CONTR
604 		      , prrv2.RESULT_VALUE       RETRO_EMPLOYER_CONTR
605 		      , prr1.start_date          START_DATE
606 		      , prr1.end_date            END_DATE
607 		      , ROWNUM                   COUNT1
608 		 FROM 	pay_run_results			prr1
609 		       , pay_run_result_values		prrv1
610                        , pay_run_result_values          prrv3
611 		       , pay_element_types_f            pet1
612 		       , pay_input_values_f		piv1
613                        , pay_input_values_f             piv3
614 		       , pay_run_results		prr2
615 		       , pay_run_result_values		prrv2
616                        , pay_run_result_values          prrv4
617 		       , pay_element_types_f            pet2
618 		       , pay_input_values_f		piv2
619                        , pay_input_values_f             piv4
620 		       , pay_assignment_actions         paa
621 		       , pay_payroll_actions            ppa
622 		       , pay_element_entries_f          pee
623 		 WHERE ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
624 		 AND   prr1.ELEMENT_TYPE_ID = pet1.ELEMENT_TYPE_ID
625 		 AND   pee.ELEMENT_ENTRY_ID = prr1.ELEMENT_ENTRY_ID
626 		 AND   prrv1.RUN_RESULT_ID = prr1.RUN_RESULT_ID
627                  AND   prrv3.RUN_RESULT_ID = prr1.RUN_RESULT_ID
628 		 AND   pet1.element_name  = 'Retro Pension'
629 		 AND   pet1.legislation_code ='DK'
630 		 AND   piv1.ELEMENT_TYPE_ID = pet1.element_type_id
631 		 AND   piv1.NAME ='Pay Value'
632 		 AND   prrv1.input_value_id = piv1.input_value_id
633 		 AND   piv3.ELEMENT_TYPE_ID = pet1.element_type_id
634 		 AND   piv3.NAME ='Third Party Payee'
635 		 AND   prrv3.input_value_id = piv3.input_value_id
636 		 AND   prrv3.RESULT_VALUE = pay_magtape_generic.get_parameter_value('TRANSFER_ORGANIZATION_ID')
637 		 AND   prr2.ELEMENT_TYPE_ID =pet2.ELEMENT_TYPE_ID
638 		 AND   prrv2.RUN_RESULT_ID = prr2.RUN_RESULT_ID
639 		 AND   prrv4.RUN_RESULT_ID = prr2.RUN_RESULT_ID
640 		 AND   prrv4.RESULT_VALUE = prrv3.RESULT_VALUE
641 		 AND   pet2.element_name  = 'Retro Employer Pension'
642 		 AND   pet2.legislation_code ='DK'
643 		 AND   piv2.ELEMENT_TYPE_ID = pet2.element_type_id
644 		 AND   piv2.NAME ='Pay Value'
645 		 AND   prrv2.input_value_id = piv2.input_value_id
646 		 AND   piv4.ELEMENT_TYPE_ID = pet2.element_type_id
647 		 AND   piv4.NAME ='Third Party Payee'
648 		 AND   prrv4.input_value_id = piv4.input_value_id
649 		 AND   prrv4.RESULT_VALUE = pay_magtape_generic.get_parameter_value('TRANSFER_ORGANIZATION_ID')
650 		 AND   prr1.assignment_action_id = paa.assignment_action_id
651 		 AND   prr1.assignment_action_id=prr2.assignment_action_id
652 		 AND   prr1.start_date = prr2.start_date
653 		 AND   prr1.end_date = prr2.end_date
654 		 AND   pay_magtape_generic.get_parameter_value('TRANSFER_INFO_TYPE') NOT IN (300, 400, 800, 900)
655 		 AND   paa.assignment_id = pay_magtape_generic.get_parameter_value('TRANSFER_ASSIGNMENT_ID')
656 		 AND   ppa.effective_date BETWEEN pet1.effective_start_date and pet1.effective_end_date
657 		 AND   ppa.effective_date BETWEEN pet2.effective_start_date and pet2.effective_end_date
658 		 AND   ppa.effective_date BETWEEN piv1.effective_start_date and piv1.effective_end_date
659 		 AND   ppa.effective_date BETWEEN piv2.effective_start_date and piv2.effective_end_date
660 		 AND   ppa.effective_date BETWEEN pee.effective_start_date  and pee.effective_end_date
661 		 AND   ppa.effective_date BETWEEN piv3.effective_start_date and piv3.effective_end_date
662 		 AND   ppa.effective_date BETWEEN piv4.effective_start_date and piv4.effective_end_date
663 		ORDER BY prr1.run_result_id)
664 	 WHERE mod(count1, 3) = 1) col1,
665 	(SELECT		ROWNUM count
666 		      , RETRO_EMPLOYEE_CONTR
667 		      , RETRO_EMPLOYER_CONTR
668 		      , START_DATE
669 		      , END_DATE
670 	 FROM   (SELECT	prrv1.RESULT_VALUE       RETRO_EMPLOYEE_CONTR
671 		      , prrv2.RESULT_VALUE       RETRO_EMPLOYER_CONTR
672 		      , prr1.start_date          START_DATE
673 		      , prr1.end_date            END_DATE
674 		      , ROWNUM                   COUNT1
675 		 FROM 	pay_run_results			prr1
679 		       , pay_input_values_f		piv1
676 		       , pay_run_result_values		prrv1
677                        , pay_run_result_values          prrv3
678 		       , pay_element_types_f            pet1
680                        , pay_input_values_f             piv3
681 		       , pay_run_results		prr2
682 		       , pay_run_result_values		prrv2
683                        , pay_run_result_values          prrv4
684 		       , pay_element_types_f            pet2
685 		       , pay_input_values_f		piv2
686                        , pay_input_values_f             piv4
687 		       , pay_assignment_actions         paa
688 		       , pay_payroll_actions            ppa
689 		       , pay_element_entries_f          pee
690 		 WHERE ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
691 		 AND   prr1.ELEMENT_TYPE_ID = pet1.ELEMENT_TYPE_ID
692 		 AND   pee.ELEMENT_ENTRY_ID = prr1.ELEMENT_ENTRY_ID
693 		 AND   prrv1.RUN_RESULT_ID = prr1.RUN_RESULT_ID
694                  AND   prrv3.RUN_RESULT_ID = prr1.RUN_RESULT_ID
695 		 AND   pet1.element_name  = 'Retro Pension'
696 		 AND   pet1.legislation_code ='DK'
697 		 AND   piv1.ELEMENT_TYPE_ID = pet1.element_type_id
698 		 AND   piv1.NAME ='Pay Value'
699 		 AND   prrv1.input_value_id = piv1.input_value_id
700 		 AND   piv3.ELEMENT_TYPE_ID = pet1.element_type_id
701 		 AND   piv3.NAME ='Third Party Payee'
702 		 AND   prrv3.input_value_id = piv3.input_value_id
703 		 AND   prrv3.RESULT_VALUE = pay_magtape_generic.get_parameter_value('TRANSFER_ORGANIZATION_ID')
704 		 AND   prr2.ELEMENT_TYPE_ID =pet2.ELEMENT_TYPE_ID
705 		 AND   prrv2.RUN_RESULT_ID = prr2.RUN_RESULT_ID
706 		 AND   prrv4.RUN_RESULT_ID = prr2.RUN_RESULT_ID
707 		 AND   prrv4.RESULT_VALUE = prrv3.RESULT_VALUE
708 		 AND   pet2.element_name  = 'Retro Employer Pension'
709 		 AND   pet2.legislation_code ='DK'
710 		 AND   piv2.ELEMENT_TYPE_ID = pet2.element_type_id
711 		 AND   piv2.NAME ='Pay Value'
712 		 AND   prrv2.input_value_id = piv2.input_value_id
713 		 AND   piv4.ELEMENT_TYPE_ID = pet2.element_type_id
714 		 AND   piv4.NAME ='Third Party Payee'
715 		 AND   prrv4.input_value_id = piv4.input_value_id
716 		 AND   prrv4.RESULT_VALUE = pay_magtape_generic.get_parameter_value('TRANSFER_ORGANIZATION_ID')
717 		 AND   prr1.assignment_action_id = paa.assignment_action_id
718 		 AND   prr1.assignment_action_id=prr2.assignment_action_id
719 		 AND   prr1.start_date = prr2.start_date
720 		 AND   prr1.end_date = prr2.end_date
721 		 AND   pay_magtape_generic.get_parameter_value('TRANSFER_INFO_TYPE') NOT IN (300, 400, 800, 900)
722 		 AND   paa.assignment_id = pay_magtape_generic.get_parameter_value('TRANSFER_ASSIGNMENT_ID')
723 		 AND   ppa.effective_date BETWEEN pet1.effective_start_date and pet1.effective_end_date
724 		 AND   ppa.effective_date BETWEEN pet2.effective_start_date and pet2.effective_end_date
725 		 AND   ppa.effective_date BETWEEN piv1.effective_start_date and piv1.effective_end_date
726 		 AND   ppa.effective_date BETWEEN piv2.effective_start_date and piv2.effective_end_date
727 		 AND   ppa.effective_date BETWEEN pee.effective_start_date  and pee.effective_end_date
728 		 AND   ppa.effective_date BETWEEN piv3.effective_start_date and piv3.effective_end_date
729 		 AND   ppa.effective_date BETWEEN piv4.effective_start_date and piv4.effective_end_date
730 		ORDER BY prr1.run_result_id)
731 	 WHERE mod(count1, 3) = 2) col2,
732 	(SELECT		ROWNUM count
733 		      , RETRO_EMPLOYEE_CONTR
734 		      , RETRO_EMPLOYER_CONTR
735 		      , START_DATE
736 		      , END_DATE
737 	 FROM   (SELECT	prrv1.RESULT_VALUE       RETRO_EMPLOYEE_CONTR
738 		      , prrv2.RESULT_VALUE       RETRO_EMPLOYER_CONTR
739 		      , prr1.start_date          START_DATE
740 		      , prr1.end_date            END_DATE
741 		      , ROWNUM                   COUNT1
742 		 FROM 	pay_run_results			prr1
743 		       , pay_run_result_values		prrv1
744                        , pay_run_result_values          prrv3
745 		       , pay_element_types_f            pet1
746 		       , pay_input_values_f		piv1
747                        , pay_input_values_f             piv3
748 		       , pay_run_results		prr2
749 		       , pay_run_result_values		prrv2
750                        , pay_run_result_values          prrv4
751 		       , pay_element_types_f            pet2
752 		       , pay_input_values_f		piv2
753                        , pay_input_values_f             piv4
754 		       , pay_assignment_actions         paa
755 		       , pay_payroll_actions            ppa
756 		       , pay_element_entries_f          pee
757 		 WHERE ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
758 		 AND   prr1.ELEMENT_TYPE_ID = pet1.ELEMENT_TYPE_ID
759 		 AND   pee.ELEMENT_ENTRY_ID = prr1.ELEMENT_ENTRY_ID
760 		 AND   prrv1.RUN_RESULT_ID = prr1.RUN_RESULT_ID
761                  AND   prrv3.RUN_RESULT_ID = prr1.RUN_RESULT_ID
762 		 AND   pet1.element_name  = 'Retro Pension'
763 		 AND   pet1.legislation_code ='DK'
764 		 AND   piv1.ELEMENT_TYPE_ID = pet1.element_type_id
765 		 AND   piv1.NAME ='Pay Value'
766 		 AND   prrv1.input_value_id = piv1.input_value_id
767 		 AND   piv3.ELEMENT_TYPE_ID = pet1.element_type_id
768 		 AND   piv3.NAME ='Third Party Payee'
769 		 AND   prrv3.input_value_id = piv3.input_value_id
770 		 AND   prrv3.RESULT_VALUE = pay_magtape_generic.get_parameter_value('TRANSFER_ORGANIZATION_ID')
771 		 AND   prr2.ELEMENT_TYPE_ID =pet2.ELEMENT_TYPE_ID
772 		 AND   prrv2.RUN_RESULT_ID = prr2.RUN_RESULT_ID
773 		 AND   prrv4.RUN_RESULT_ID = prr2.RUN_RESULT_ID
774 		 AND   prrv4.RESULT_VALUE = prrv3.RESULT_VALUE
775 		 AND   pet2.element_name  = 'Retro Employer Pension'
776 		 AND   pet2.legislation_code ='DK'
777 		 AND   piv2.ELEMENT_TYPE_ID = pet2.element_type_id
778 		 AND   piv2.NAME ='Pay Value'
779 		 AND   prrv2.input_value_id = piv2.input_value_id
780 		 AND   piv4.ELEMENT_TYPE_ID = pet2.element_type_id
781 		 AND   piv4.NAME ='Third Party Payee'
782 		 AND   prrv4.input_value_id = piv4.input_value_id
783 		 AND   prrv4.RESULT_VALUE = pay_magtape_generic.get_parameter_value('TRANSFER_ORGANIZATION_ID')
784 		 AND   prr1.assignment_action_id = paa.assignment_action_id
785 		 AND   prr1.assignment_action_id=prr2.assignment_action_id
786 		 AND   prr1.start_date = prr2.start_date
787 		 AND   prr1.end_date = prr2.end_date
788 		 AND   pay_magtape_generic.get_parameter_value('TRANSFER_INFO_TYPE') NOT IN (300, 400, 800, 900)
789 		 AND   paa.assignment_id = pay_magtape_generic.get_parameter_value('TRANSFER_ASSIGNMENT_ID')
790 		 AND   ppa.effective_date BETWEEN pet1.effective_start_date and pet1.effective_end_date
791 		 AND   ppa.effective_date BETWEEN pet2.effective_start_date and pet2.effective_end_date
792 		 AND   ppa.effective_date BETWEEN piv1.effective_start_date and piv1.effective_end_date
793 		 AND   ppa.effective_date BETWEEN piv2.effective_start_date and piv2.effective_end_date
794 		 AND   ppa.effective_date BETWEEN pee.effective_start_date  and pee.effective_end_date
795 		 AND   ppa.effective_date BETWEEN piv3.effective_start_date and piv3.effective_end_date
796 		 AND   ppa.effective_date BETWEEN piv4.effective_start_date and piv4.effective_end_date
797 		 ORDER BY prr1.run_result_id)
798 	 WHERE mod(count1, 3) = 0) col3
799 WHERE    col1.count = col2.count (+)
800 AND      col2.count = col3.count (+);
801 
802 
803 
804 CURSOR get_info_record_05_details IS
805 /* Restricted input to only 32 characters for bug fix 4555311 */
806 /* Modified for bug fix 4593682 to select addresses for all employees, even if they do not have an address */
807 SELECT   'ADDRESS_1=P'
808        ,  rpad(nvl(pad.address_line1,' '),32)
809        , 'ADDRESS_2=P'
810        ,  rpad(nvl(pad.address_line2,' ') /*||' '*/,32)
811        , 'CITY_NAME=P'
812        ,  nvl(substr(PAY_DK_PAYMENT_PROCESS_PKG.get_lookup_meaning('DK_POSTCODE_TOWN',pad.postal_code),5),' ') /*||' '*/
813        , 'POST_CODE=P'
814        , nvl(pad.postal_code,0)
815 FROM  per_addresses   pad
816 /* Modified for bug fix 4593682 */
817     , per_all_people_f  pap
818 WHERE  pad.person_id (+)= pap.person_id
819     /* pad.person_id = pay_magtape_generic.get_parameter_value('TRANSFER_PERSON_ID') */
820 AND    pap.person_id = pay_magtape_generic.get_parameter_value('TRANSFER_PERSON_ID')
821 AND    pay_magtape_generic.get_parameter_value('TRANSFER_INFO_TYPE') NOT IN (300, 400, 800, 900);
822 
823 END PAY_DK_PAYMENT_PROCESS_PKG;