[Home] [Help]
156: It will be used to get Starting and
157: Ending slip numbers.
158: 14-SEP-2011 rgottipa 115.39 11694701 Romoved the new cursor c_get_rl2_pdf_slip.
159: Trying to fetch Starting and Ending slip
160: numbers from hr_organization_information.
161: 19-SEP-2011 rgottipa 115.40 12996280 Handling no_data_found exception
162: while capturing starting and ending
163: slip numbers.
164: 20-Jan-2012 rgottipa 115.41 13584299 Added new procedure
982: AND ASG.effective_end_date >= l_period_start
983: AND SCL.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
984: AND rtrim(ltrim(SCL.segment12)) in
985: (select to_char(hoi.organization_id)
986: from hr_organization_information hoi
987: where hoi.org_information_context = 'Canada Employer Identification'
988: and hoi.org_information2 = l_pre_org_id
989: and hoi.org_information5 = 'T4A/RL2')
990: AND PPY.payroll_id = ASG.payroll_id
1014: AND ppa.action_type in ('R','Q','V','B','I')
1015: AND ppa.business_group_id +0 = l_bus_group_id
1016: AND ppa.payroll_action_id = paa.payroll_action_id
1017: AND paa.tax_unit_id in (select hoi.organization_id
1018: from hr_organization_information hoi
1019: where hoi.org_information_context ||''= 'Canada Employer Identification'
1020: and hoi.org_information2 = l_pre_org_id
1021: and hoi.org_information5 = 'T4A/RL2')
1022: AND paa.assignment_id = asg.assignment_id
1051: AND ppa.action_type in ('R','Q','V','B','I')
1052: AND ppa.business_group_id +0 = l_bus_group_id
1053: AND ppa.payroll_action_id = paa.payroll_action_id
1054: AND paa.tax_unit_id in (select hoi.organization_id
1055: from hr_organization_information hoi
1056: where hoi.org_information_context ||''= 'Canada Employer Identification'
1057: and hoi.org_information2 = l_pre_org_id
1058: and hoi.org_information5 = 'T4A/RL2')
1059: AND paa.assignment_id = asg.assignment_id
1076: ORDER BY 1, 3 DESC, 2;
1077:
1078: cursor c_all_qbin_gres is
1079: select hoi.organization_id
1080: from hr_organization_information hoi
1081: where hoi.org_information_context = 'Canada Employer Identification'
1082: and hoi.org_information2 = l_pre_org_id
1083: and hoi.org_information5 = 'T4A/RL2';
1084:
1510: substr(target1.ORG_INFORMATION18,1,8) RL2_Slip_Number,
1511: decode(target1.org_information3,'Y',target1.organization_id,
1512: to_number(target1.ORG_INFORMATION20)),
1513: target1.ORG_INFORMATION3
1514: from hr_organization_information target1,
1515: hr_all_organization_units target2
1516: where target1.organization_id = to_number(p_pre_org_id)
1517: and target2.business_group_id = l_business_group_id
1518: and target2.organization_id = target1.organization_id
2032:
2033: begin
2034: select to_number(target.ORG_INFORMATION1),to_number(target.ORG_INFORMATION2)
2035: into l_rl2_starting_slip_num,l_rl2_ending_slip_num
2036: from hr_organization_information target
2037: where target.organization_id = p_transmitter_id
2038: and target.org_information_context = 'Prov Reporting Est3'
2039: and exists (select 'X' from hr_organization_information target1
2040: where target1.organization_id = p_transmitter_id
2035: into l_rl2_starting_slip_num,l_rl2_ending_slip_num
2036: from hr_organization_information target
2037: where target.organization_id = p_transmitter_id
2038: and target.org_information_context = 'Prov Reporting Est3'
2039: and exists (select 'X' from hr_organization_information target1
2040: where target1.organization_id = p_transmitter_id
2041: and target1.org_information_context = 'Prov Reporting Est'
2042: and target1.ORG_INFORMATION3 = 'Y');
2043: exception -- handling this exception for bug 12996280
2131: cursor c_all_gres(asgactid number) is
2132: select hoi.organization_id
2133: from pay_action_information pac,
2134: pay_assignment_actions paa,
2135: hr_organization_information hoi
2136: where paa.assignment_action_id = asgactid
2137: and pac.action_context_id = paa.payroll_action_id
2138: and pac.action_information_category = 'CAEOY TRANSMITTER INFO'
2139: and pac.action_information1 = 'RL2'
2621: select hoi.organization_id ,
2622: hoi.org_information5
2623: from pay_action_information pac,
2624: pay_assignment_actions paa,
2625: hr_organization_information hoi
2626: where paa.assignment_action_id = asgactid
2627: and pac.action_context_id = paa.payroll_action_id
2628: and pac.action_information_category = 'CAEOY TRANSMITTER INFO'
2629: and pac.action_information1 = 'RL2'
2637: select hoi.organization_id ,
2638: hoi.org_information5
2639: from pay_action_information pac,
2640: pay_assignment_actions paa,
2641: hr_organization_information hoi
2642: where paa.assignment_action_id = asgactid
2643: and pac.action_context_id = paa.payroll_action_id
2644: and pac.action_information_category = 'CAEOY TRANSMITTER INFO'
2645: and pac.action_information1 = 'RL2'
2900:
2901: select decode(hoi.org_information3,'Y',hoi.organization_id,
2902: hoi.org_information20)
2903: into l_transmitter_id
2904: from hr_organization_information hoi,
2905: hr_all_organization_units hou
2906: WHERE hou.business_group_id = l_business_group_id
2907: and hoi.organization_id = hou.organization_id
2908: and hoi.org_information_context = 'Prov Reporting Est'
2912:
2913: begin
2914: select hoi.org_information1,hoi.org_information2
2915: into l_pre_source_of_income,l_pre_description
2916: from hr_organization_information hoi
2917: where hoi.organization_id = l_transmitter_id
2918: and hoi.org_information_context = 'Prov Reporting Est2';
2919:
2920: exception
4097: and ppa.business_group_id + 0 = '||to_char(l_business_group)||'
4098: and ppa.payroll_action_id = paa.payroll_action_id
4099: and paa.tax_unit_id in
4100: (select hoi.organization_id
4101: from hr_organization_information hoi
4102: where hoi.org_information_context = ''Canada Employer Identification''
4103: and hoi.org_information2 = '''|| l_pre_org_id ||''''||'
4104: and hoi.org_information5 = ''T4A/RL2'')
4105: and paa.action_status = ''C''