[Home] [Help]
271: PROCEDURE gen_body_xml_main(p_asg_action_id IN NUMBER
272: ,p_abp_output_type IN VARCHAR2
273: ,x IN OUT NOCOPY NUMBER) IS
274:
275: CURSOR csr_asg_details(c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE) IS
276: SELECT pai.action_information1 emp_num
277: ,pai.action_information2 asg_num
278: ,to_char(fnd_date.canonical_to_date(pai.action_information3),'RRRR-MM-DD') asg_start_date
279: ,to_char(fnd_date.canonical_to_date(pai.action_information4),'RRRR-MM-DD') asg_end_date
289: WHERE pai.action_context_id = c_assignment_action_id
290: AND pai.action_information_category = 'NL_ABP_ASG_INFO'
291: AND pai.action_context_type = 'AAP';
292: --=============================================================================
293: CURSOR csr_emp_details (c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE )IS
294: SELECT pai.action_information2 employee_number
295: ,pai.action_information3 sofi_number
296: ,pai.action_information4 last_name
297: ,pai.action_information5 init
322: WHERE pai.action_context_id = c_assignment_action_id
323: AND pai.action_information_category = 'NL_ABP_EMP_INFO'
324: AND pai.action_context_type = 'AAP';
325: --=============================================================================
326: CURSOR csr_pay_period_details(c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE) IS
327: SELECT to_char(fnd_date.canonical_to_date(pai.action_information1),'RRRR-MM-DD') start_date
328: ,to_char(fnd_date.canonical_to_date(pai.action_information2),'RRRR-MM-DD') end_date
329: ,pai.action_information3 kop
330: ,pai.action_information4 income_code
335: AND pai.action_information_category = 'NL_ABP_PAY_PERIOD_INFO'
336: AND action_context_type = 'AAP'
337: ORDER BY start_date;
338: --=============================================================================
339: CURSOR csr_pension_details(c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE,c_effective_date VARCHAR2) IS
340: SELECT pai.action_information1 pension_type
341: ,pai.action_information2 pension_type_variant
342: ,to_char(fnd_date.canonical_to_date(pai.action_information3),'RRRR-MM-DD') pension_type_sdate
343: ,to_char(fnd_date.canonical_to_date(pai.action_information4),'RRRR-MM-DD') pension_type_info_sdate
353: AND pai.action_information_category = 'NL_ABP_PENSION_INFO'
354: AND action_context_type = 'AAP'
355: AND to_char(fnd_date.canonical_to_date(pai.action_information4),'RRRR-MM-DD') <= c_effective_date; --Bug 16301893
356: --=============================================================================
357: CURSOR csr_get_pact_id(c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE) IS
358: SELECT ppa.payroll_action_id
359: FROM pay_payroll_actions ppa
360: ,pay_assignment_actions paa
361: WHERE paa.payroll_action_id = ppa.payroll_action_id
356: --=============================================================================
357: CURSOR csr_get_pact_id(c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE) IS
358: SELECT ppa.payroll_action_id
359: FROM pay_payroll_actions ppa
360: ,pay_assignment_actions paa
361: WHERE paa.payroll_action_id = ppa.payroll_action_id
362: AND paa.assignment_action_id = c_assignment_action_id;
363: --=============================================================================
364: CURSOR csr_get_nationality(c_code VARCHAR2) IS
741: -- =============================================================================
742: PROCEDURE gen_body_xml(x IN OUT NOCOPY NUMBER)
743: IS
744:
745: CURSOR csr_get_child_act_id (c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE )IS
746: SELECT assignment_action_id ass_act_id
747: FROM pay_assignment_actions
748: WHERE source_action_id = c_assignment_action_id;
749: --=============================================================================
743: IS
744:
745: CURSOR csr_get_child_act_id (c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE )IS
746: SELECT assignment_action_id ass_act_id
747: FROM pay_assignment_actions
748: WHERE source_action_id = c_assignment_action_id;
749: --=============================================================================
750: CURSOR csr_get_asg_action_id(c_payroll_action_id IN NUMBER
751: ,c_employer_id IN NUMBER) IS
749: --=============================================================================
750: CURSOR csr_get_asg_action_id(c_payroll_action_id IN NUMBER
751: ,c_employer_id IN NUMBER) IS
752: SELECT assignment_action_id
753: FROM pay_assignment_actions
754: WHERE payroll_action_id = c_payroll_action_id
755: AND tax_unit_id = c_employer_id;
756: --=============================================================================
757: CURSOR csr_chk_asg_ctxt(c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE) IS
753: FROM pay_assignment_actions
754: WHERE payroll_action_id = c_payroll_action_id
755: AND tax_unit_id = c_employer_id;
756: --=============================================================================
757: CURSOR csr_chk_asg_ctxt(c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE) IS
758: SELECT 1
759: FROM pay_action_information pai
760: WHERE pai.action_context_id = c_assignment_action_id
761: AND pai.action_information_category = 'NL_ABP_ASG_INFO'
760: WHERE pai.action_context_id = c_assignment_action_id
761: AND pai.action_information_category = 'NL_ABP_ASG_INFO'
762: AND pai.action_context_type = 'AAP';
763: --=============================================================================
764: CURSOR csr_asg_withdrawal(c_payroll_action_id pay_assignment_actions.payroll_action_id%TYPE) IS
765: SELECT pai.action_information1 emp_num
766: , pai.action_information2 asg_num
767: , to_char (fnd_date.canonical_to_date (pai.action_information3), 'RRRR-MM-DD') start_date
768: , pai.action_information4 sofi_num
770: FROM pay_action_information pai
771: WHERE pai.action_context_id IN
772: (
773: SELECT assignment_action_id
774: FROM pay_assignment_actions
775: WHERE payroll_action_id = c_payroll_action_id
776: )
777: AND pai.action_context_type = 'AAP'
778: AND pai.action_information_category IN ('NL_ABP_COMP_WITHDRAWAL_INFO', 'NL_ABP_WITHDRAWAL_INFO')