[Home] [Help]
PACKAGE BODY: APPS.PAY_KR_YEA_MAGTAPE_PKG
Source
1 package body pay_kr_yea_magtape_pkg as
2 /* $Header: pykryeam.pkb 120.3.12020000.3 2013/02/05 12:52:00 scireddy ship $ */
3 --
4 -- Constants
5 --
6 c_package constant varchar2(31) := ' pay_kr_yea_magtape_pkg.';
7 --
8 --Bug 5069923
9 report_for_var varchar2(50);
10
11 procedure populate_a
12 ------------------------------------------------------------------------
13 is
14 l_proc varchar2(61) := c_package || 'populate_a';
15 --
16 cursor csr_a is
17 select count(distinct hoi2.org_information2||hoi3.org_information9) --Bug# 2822459 count(*)
18 from hr_organization_information hoi2,
19 hr_organization_information hoi3,
20 hr_organization_units bp2,
21 hr_organization_information hoi1,
22 hr_organization_units bp1
23 where bp1.organization_id = g_business_place_id --Bug 5069923
24 and hoi1.organization_id = bp1.organization_id
25 and hoi1.org_information_context = 'KR_BUSINESS_PLACE_REGISTRATION'
26 --Bug 5069923
27 and ( (report_for_var='A')
28 or ( (hoi2.organization_id in (select posev.ORGANIZATION_ID_child
29 from PER_ORG_STRUCTURE_ELEMENTS posev
30 where posev.org_structure_version_id=(pay_magtape_generic.get_parameter_value('ORG_STRUC_VERSION_ID'))
31 and exists (select null
32 from hr_organization_information
33 where organization_id = posev.ORGANIZATION_ID_child
34 and org_information_context = 'CLASS'
35 and org_information1 = 'KR_BUSINESS_PLACE'
36 )
37 start with ORGANIZATION_ID_PARENT = (decode(report_for_var,'S',null,'SUB',g_business_place_id))
38 connect by prior ORGANIZATION_ID_child = ORGANIZATION_ID_PARENT
39 )
40 )
41 or (hoi2.organization_id = g_business_place_id
42 )
43 )
44 )
45 and bp2.business_group_id = bp1.business_group_id
46 and hoi2.organization_id = bp2.organization_id
47 and hoi2.org_information_context = 'KR_BUSINESS_PLACE_REGISTRATION'
48 and hoi2.org_information10 = hoi1.org_information10
49 and hoi3.organization_id = hoi2.organization_id
50 and hoi3.org_information_context = 'KR_INCOME_TAX_OFFICE'
51 and exists(
52 select null
53 from pay_assignment_actions paa,
54 pay_payroll_actions ppa
55 where ppa.report_type = 'YEA'
56 and ppa.report_qualifier = 'KR'
57 and ppa.business_group_id = bp1.business_group_id
58 -- Bug 3248513
59 and ( (ppa.report_category in (g_normal_yea, g_interim_yea, g_re_yea)) or (ppa.payroll_action_id = g_payroll_action_id ))
60 and to_number(to_char(ppa.effective_date, 'YYYY')) = g_target_year
61 --
62 and ppa.action_type in ('B','X')
63 and paa.payroll_action_id = ppa.payroll_action_id
64 and paa.tax_unit_id = bp2.organization_id
65 and paa.action_status = 'C');
66 begin
67 if g_business_place_id is null then
68 g_business_place_id := to_number(pay_magtape_generic.get_parameter_value('BUSINESS_PLACE_ID'));
69 g_target_year := to_number(pay_magtape_generic.get_parameter_value('TARGET_YEAR'));
70 --Bug 5069923
71 report_for_var := pay_magtape_generic.get_parameter_value('REPORT_FOR');
72 --
73 open csr_a;
74 fetch csr_a into g_b_records;
75 close csr_a;
76 end if;
77 end populate_a;
78
79 function GET_TAX_REDUCTION_DATE(p_assignment_id in number,
80 p_effective_date in date,
81 p_start_date out NOCOPY date,
82 p_end_date out NOCOPY date) return number
83 is
84
85 cursor get_tax_reduction_dates
86 is
87 select min(fnd_date.canonical_to_date(aei.AEI_INFORMATION1)),
88 max(fnd_date.canonical_to_date(aei.AEI_INFORMATION2))
89 from per_assignment_extra_info aei
90 where aei.information_type in ('KR_YEA_TAX_REDUCTION_SMB','KR_YEA_TAX_REDUCTION_TEACHERS','KR_YEA_TAX_REDUCTION_MARINE')
91 and aei.assignment_id = p_assignment_id;
92
93 cursor csr_get_emp_dates
94 is
95 select pds.DATE_START, pds.ACTUAL_TERMINATION_DATE
96 from per_periods_of_service pds,
97 per_assignments_f asg
98 where asg.assignment_id = p_assignment_id
99 and p_effective_date between asg.effective_start_date and asg.effective_end_date
100 and pds.period_of_service_id = asg.period_of_service_id;
101
102 l_emp_hire_date date := null;
103 l_emp_term_date date := null;
104 l_number number := 0;
105 Begin
106 open csr_get_emp_dates;
107 fetch csr_get_emp_dates into l_emp_hire_date,l_emp_term_date;
108 close csr_get_emp_dates;
109 if l_emp_hire_date is null then
110 l_emp_hire_date := to_date('1900/01/01','YYYY/MM/DD');
111 end if;
112 if l_emp_term_date is null then
113 l_emp_term_date := to_date(to_char(p_effective_date, 'YYYY')||'/12/31','YYYY/MM/DD');
114 end if;
115 open get_tax_reduction_dates;
116 fetch get_tax_reduction_dates into p_start_date,p_end_date;
117 if p_start_date is null then
118 p_start_date := l_emp_hire_date;
119 end if;
120 if p_end_date is null then
121 p_end_date := least(l_emp_term_date, last_day(add_months(p_start_date, 12*3)-1));
122 end if;
123 return 1;
124 End GET_TAX_REDUCTION_DATE;
125
126 ------------------------------------------------------------------------
127 -- Package initialization section
128 ------------------------------------------------------------------------
129 begin
130 declare
131 l_report_type varchar2(3);
132 --------------------------------------------------------
133 function user_entity_id(p_user_entity_name in varchar2) return number
134 --------------------------------------------------------
135 is
136 l_user_entity_id number;
137 begin
138 select user_entity_id
139 into l_user_entity_id
140 from ff_user_entities
141 where user_entity_name = p_user_entity_name
142 and legislation_code = 'KR'
143 and creator_type = 'X';
144 --
145 return l_user_entity_id;
146 end user_entity_id;
147 begin
148 -- Bug 3248513
149 g_payroll_action_id := pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');
150 g_assignment_set_id := to_number(pay_magtape_generic.get_parameter_value('ASSIGNMENT_SET_ID'));
151
152 if g_payroll_action_id is null then
153
154 l_report_type := pay_magtape_generic.get_parameter_value('REPORT_TYPE');
155
156 if l_report_type is null then
157 g_normal_yea := 'N';
158 g_interim_yea := 'I';
159 g_rep_period_code := '1'; -- Bug 9213683
160
161 elsif l_report_type = 'N' then
162 g_normal_yea := 'N';
163 g_rep_period_code := '1'; -- Bug 9213683
164
165 elsif l_report_type = 'I' then
166 g_interim_yea := 'I';
167 g_rep_period_code := '3'; -- Bug 9213683
168
169 elsif l_report_type = 'R' then
170 g_re_yea := 'R';
171 g_rep_period_code := '3'; -- Bug 9213683
172
173 elsif l_report_type = 'NI' then
174 g_normal_yea := 'N';
175 g_interim_yea := 'I';
176 g_rep_period_code := '1'; -- Bug 9213683
177
178 elsif l_report_type = 'NR' then
179 g_normal_yea := 'N';
180 g_re_yea := 'R';
181 g_rep_period_code := '1'; -- Bug 9213683
182
183 end if;
184 end if;
185 --
186 populate_a;
187
188 g_taxable_id := user_entity_id('X_YEA_TAXABLE');
189 g_annual_itax_id := user_entity_id('X_YEA_ANNUAL_ITAX');
190 g_annual_rtax_id := user_entity_id('X_YEA_ANNUAL_RTAX');
191 g_annual_stax_id := user_entity_id('X_YEA_ANNUAL_STAX');
192
193 end;
194 end pay_kr_yea_magtape_pkg;