[Home] [Help]
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)
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
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
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'
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'
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
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
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'
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
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
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
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'
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'
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
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 */
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)
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)
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
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'
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
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
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 */
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)
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)
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 */
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
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
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'
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 */
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
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)
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:
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:
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'
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'
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 */
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
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;
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:
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
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'
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 */
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)
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;
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:
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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')
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;
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:
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;