[Home] [Help]
PACKAGE: APPS.PAY_KR_YEA_MAGTAPE_PKG
Source
1 package pay_kr_yea_magtape_pkg as
2 /* $Header: pykryeam.pkh 120.5.12010000.7 2008/08/06 07:48:33 ubhat ship $ */
3 --
4 level_cnt number;
5 --
6 g_business_place_id number;
7 g_target_year number;
8 g_b_records number;
9 -- Bug 3248513
10 g_normal_yea varchar2(1) := 'X';
11 g_interim_yea varchar2(1) := 'X';
12 g_re_yea varchar2(1) := 'X';
13 g_payroll_action_id number;
14 g_assignment_set_id number;
15 --
16 g_taxable_id number;
17 g_annual_itax_id number;
18 g_annual_rtax_id number;
19 g_annual_stax_id number;
20 --
21 ------------------------------------------------------------------------
22 cursor csr_a is
23 select
24 'BUSINESS_GROUP_ID=C', to_char(bp.business_group_id),
25 'BUSINESS_GROUP_ID=P', to_char(bp.business_group_id),
26 'BP_ORGANIZATION_NAME_A=P', bptl.name,
27 'TAX_OFFICE_CODE_A=P', hoi1.org_information9,
28 'BP_NUMBER_A=P', hoi2.org_information2,
29 'CORP_ORGANIZATION_NAME=P', corptl.name,
30 'CORP_NAME=P', hoi3.org_information1,
31 'CORP_NUMBER=P', hoi3.org_information2,
32 'CORP_REPR_NAME=P', hoi3.org_information6,
33 'CORP_PHONE_NUMBER=P', loc.telephone_number_1,
34 'B_RECORDS=P', to_char(g_b_records),
35 'HOME_TAX_ID=P', nvl(pay_magtape_generic.get_parameter_value('HOME_TAX_ID'), ' ')
36 from hr_organization_information hoi3,
37 hr_locations_all loc,
38 hr_all_organization_units_tl corptl,
39 hr_organization_units corp,
40 hr_organization_information hoi2,
41 hr_organization_information hoi1,
42 hr_all_organization_units_tl bptl,
43 hr_organization_units bp
44 where bp.organization_id = g_business_place_id
45 and bptl.organization_id = bp.organization_id
46 and bptl.language = userenv('LANG')
47 and hoi1.organization_id = bp.organization_id
48 and hoi1.org_information_context = 'KR_INCOME_TAX_OFFICE'
49 and hoi2.organization_id = hoi1.organization_id
50 and hoi2.org_information_context = 'KR_BUSINESS_PLACE_REGISTRATION'
51 and corp.organization_id = to_number(hoi2.org_information10)
52 and corptl.organization_id = corp.organization_id
53 and corptl.language = userenv('LANG')
54 and loc.location_id(+) = corp.location_id
55 and loc.style(+) = 'KR'
56 and hoi3.organization_id = corp.organization_id
57 and hoi3.org_information_context = 'KR_CORPORATE_INFORMATION';
58
59 cursor csr_b is
60 select DISTINCT 'BP_NUMBER=P', hoi2.org_information2,
61 'TAX_OFFICE_CODE=P', hoi3.org_information9,
62 'C_RECORDS=P', pay_kr_yea_magtape_fun_pkg.b_data(hoi2.org_information2, hoi3.org_information9, 'C_RECORDS'),
63 'TAXABLE=P', pay_kr_yea_magtape_fun_pkg.b_data(hoi2.org_information2, hoi3.org_information9, 'TAXABLE'),
64 'ANNUAL_ITAX=P', pay_kr_yea_magtape_fun_pkg.b_data(hoi2.org_information2, hoi3.org_information9, 'ANNUAL_ITAX'),
65 'ANNUAL_RTAX=P', pay_kr_yea_magtape_fun_pkg.b_data(hoi2.org_information2, hoi3.org_information9, 'ANNUAL_RTAX'),
66 'ANNUAL_STAX=P', pay_kr_yea_magtape_fun_pkg.b_data(hoi2.org_information2, hoi3.org_information9, 'ANNUAL_STAX'),
67 'D_RECORDS=P', pay_kr_yea_magtape_fun_pkg.b_data(hoi2.org_information2, hoi3.org_information9, 'D_RECORDS')
68 from hr_organization_information hoi3,
69 hr_organization_information hoi2,
70 hr_all_organization_units_tl bptl2,
71 hr_organization_units bp2,
72 hr_organization_information hoi1,
73 hr_organization_units bp1
74 where bp1.organization_id = pay_magtape_generic.get_parameter_value('BUSINESS_PLACE_ID') --Bug 5069923
75 and hoi1.organization_id = bp1.organization_id
76 and hoi1.org_information_context = 'KR_BUSINESS_PLACE_REGISTRATION'
77 and bp2.business_group_id = bp1.business_group_id
78 --Bug 5069923
79 and ( (pay_magtape_generic.get_parameter_value('REPORT_FOR')='A')
80 or ( (hoi2.organization_id in ( select posev.ORGANIZATION_ID_child
81 from PER_ORG_STRUCTURE_ELEMENTS posev
82 where posev.org_structure_version_id=(pay_magtape_generic.get_parameter_value('ORG_STRUC_VERSION_ID'))
83 and exists ( select null
84 from hr_organization_information
85 where organization_id = posev.ORGANIZATION_ID_child
86 and org_information_context = 'CLASS'
87 and org_information1 = 'KR_BUSINESS_PLACE'
88 )
89 start with ORGANIZATION_ID_PARENT = (decode(pay_magtape_generic.get_parameter_value('REPORT_FOR'),'S',null,'SUB',pay_magtape_generic.get_parameter_value('BUSINESS_PLACE_ID')))
90 connect by prior ORGANIZATION_ID_child = ORGANIZATION_ID_PARENT
91 )
92 )
93 or (hoi2.organization_id = pay_magtape_generic.get_parameter_value('BUSINESS_PLACE_ID')
94 )
95 )
96 )
97 and hoi2.organization_id = bp2.organization_id
98 and bptl2.organization_id = bp2.organization_id
99 and bptl2.language = userenv('LANG')
100 and hoi2.org_information_context = 'KR_BUSINESS_PLACE_REGISTRATION'
101 and hoi2.org_information10 = hoi1.org_information10
102 and hoi3.organization_id = hoi2.organization_id
103 and hoi3.org_information_context = 'KR_INCOME_TAX_OFFICE'
104 and exists(
105 select null
106 from pay_assignment_actions paa,
107 pay_payroll_actions ppa
108 where ppa.report_type = 'YEA'
109 and ppa.report_qualifier = 'KR'
110 and ppa.business_group_id = bp1.business_group_id
111 -- Bug 3248513
112 and ( (ppa.report_category in (g_normal_yea, g_interim_yea, g_re_yea)) or (ppa.payroll_action_id = g_payroll_action_id) )
113 and to_number(to_char(ppa.effective_date, 'YYYY')) = g_target_year
114 --
115 and ppa.action_type in ('B','X')
116 and paa.payroll_action_id = ppa.payroll_action_id
117 and paa.tax_unit_id = bp2.organization_id
118 and paa.action_status = 'C')
119 order by 4;
120
121 cursor csr_c(p_bp_number varchar2 default pay_magtape_generic.get_parameter_value('BP_NUMBER'),p_tax_office_code varchar2 default pay_magtape_generic.get_parameter_value('TAX_OFFICE_CODE')) is
122 select
123 'PAYROLL_ID=C', to_char(ppa.payroll_id),
124 'PAYROLL_ACTION_ID=C', to_char(ppa.payroll_action_id),
125 'ASSIGNMENT_ID=C', to_char(paa.assignment_id),
126 'ASSIGNMENT_ID=P', to_char(paa.assignment_id),
127 'ASSIGNMENT_ACTION_ID=C', to_char(paa.assignment_action_id),
128 'DATE_EARNED=C', fnd_date.date_to_canonical(ppa.effective_date),
129 'EFFECTIVE_DATE=P', fnd_date.date_to_canonical(ppa.effective_date),
130 'NATIONAL_IDENTIFIER=P', pp.national_identifier,
131 'FULL_NAME=P', pp.last_name || pp.first_name,
132 'HIRE_DATE=P', fnd_date.date_to_canonical(pds.date_start),
133 'TERMINATION_DATE=P', fnd_date.date_to_canonical(pds.actual_termination_date),
134 'D_RECORDS_PER_C=P', pay_kr_yea_magtape_fun_pkg.c_data(paa.assignment_id, 'D_RECORDS_PER_C'),
135 'COUNTRY_CODE=P', pp.country_of_birth,
136 'DEPENDENT_COUNT=P', pay_kr_yea_magtape_fun_pkg.e_record_count(paa.assignment_id,ppa.effective_date) -- 4738717
137 from per_people_f pp,
138 per_periods_of_service pds,
139 per_assignments_f pa,
140 pay_assignment_actions paa,
141 pay_payroll_actions ppa,
142 hr_organization_units bp,
143 fnd_territories ft,
144 hr_organization_information hoi1,
145 hr_organization_information hoi2
146 where hoi1.org_information2 = p_bp_number --Bug2822459
147 and hoi2.org_information9 = p_tax_office_code --Bug2822459
148 and hoi1.organization_id = bp.organization_id
149 and hoi1.organization_id = hoi2.organization_id
150 and hoi1.org_information_context = 'KR_BUSINESS_PLACE_REGISTRATION'
151 and hoi2.org_information_context = 'KR_INCOME_TAX_OFFICE'
152 and ppa.business_group_id = bp.business_group_id
153 and ppa.report_type = 'YEA'
154 and ppa.report_qualifier = 'KR'
155 -- Bug 3248513
156 and ( (ppa.report_category in (g_normal_yea, g_interim_yea, g_re_yea)) or (ppa.payroll_action_id = g_payroll_action_id) )
157 and to_number(to_char(ppa.effective_date, 'YYYY')) = g_target_year
158 --
159 and ppa.action_type in ('B','X')
160 and paa.payroll_action_id = ppa.payroll_action_id
161 and ppa.payroll_action_id = ppa.payroll_action_id
162 -- Bug 3248513
163 and ((g_assignment_set_id is null) or (hr_assignment_set.assignment_in_set(g_assignment_set_id, paa.assignment_id) = 'Y'))
164 and ((g_re_yea <> 'R') or (pay_kr_yea_magtape_fun_pkg.latest_yea_action(paa.assignment_action_id, g_payroll_action_id, g_target_year) = 'Y')) -- Bug 4726974
165 --
166 and paa.tax_unit_id = bp.organization_id
167 and paa.action_status = 'C'
168 and pa.assignment_id = paa.assignment_id
169 and ppa.effective_date
170 between pa.effective_start_date and pa.effective_end_date
171 and pds.period_of_service_id = pa.period_of_service_id
172 and pp.person_id = pds.person_id
173 and pp.country_of_birth = ft.territory_code (+)
174 and ppa.effective_date
175 between pp.effective_start_date and pp.effective_end_date
176 order by 16;
177
178 cursor csr_d(p_assignment_id number default to_number(pay_magtape_generic.get_parameter_value('ASSIGNMENT_ID'))) is
179 select
180 'PREV_BP_NUMBER=P', aei.aei_information3,
181 'PREV_BP_NAME=P', aei.aei_information2,
182 'PREV_TAXABLE_MTH=P', nvl(aei.aei_information4, 0),
183 'PREV_TAXABLE_BON=P', nvl(aei.aei_information5, 0),
184 'PREV_SP_IRREG_BONUS=P', nvl(aei.aei_information6, 0),
185 'PREV_STCK_PUR_OPT_EXEC_EARN=P', nvl(aei.aei_information17, 0) -- Bug 6622876
186 from per_assignment_extra_info aei
187 where aei.assignment_id = p_assignment_id
188 and aei.information_type = 'KR_YEA_PREV_ER_INFO'
189 and to_number(to_char(fnd_date.canonical_to_date(aei.aei_information1), 'YYYY')) = g_target_year
190 order by aei_information1, 2;
191 ------------------------------------------------------------------------
192
193 -- Note: Any change in this cursor must be included in
194 -- pay_kr_yea_magtape_fun_pkg.e_record_count also.
195
196 cursor csr_e( p_assignment_id varchar2 default pay_magtape_generic.get_parameter_value('ASSIGNMENT_ID'),
197 p_effective_date date default fnd_date.canonical_to_date(
198 pay_magtape_generic.get_parameter_value('EFFECTIVE_DATE'))
199 ) is
200 -- Bug 5654127
201 select 'CONTACT_TYPE=P', '0' contact_type,
202 'NATIONALITY=P', decode(pay_kr_ff_functions_pkg.ni_nationality(asg.assignment_id,p_effective_date),'K','1','9') nationality,
203 'BASIC_FLAG=P', '1' basic_exem_flag,
204 'DISABLE_FLAG=P', decode(pay_kr_ff_functions_pkg.disabled_flag(per.person_id,p_effective_date),'Y','1',' ') disabled_exem_flag,
205 'RAISING_CHILD=P', ' ' child_raising_exem,
206 'INS_PREM_NTS=P', '0' ins_prem_nts,
207 'INS_PREM_OTH=P', '0' ins_prem_oth,
208 'MED_EXP_NTS=P', '0' med_exp_nts,
209 'MED_EXP_OTH=P', '0' med_exp_oth,
210 'EDUC_EXP_NTS=P', '0' educ_exp_nts,
211 'EDUC_EXP_OTH=P', '0' educ_exp_oth,
212 'CARD_EXP_NTS=P', '0' card_exp_nts,
213 'CARD_EXP_OTH=P', '0' card_exp_oth,
214 'CASH_EXP_NTS=P', '0' cash_exp_nts,
215 'CONT_NAME=P', 'Y' full_name,
216 'CONT_NI=P', per.national_identifier national_identifier,
217 /* Bug 6622876 */
218 'SNR_FLAG=P', pay_kr_ff_functions_pkg.aged_flag(per.national_identifier,to_date('31-12-'||to_char(p_effective_date,'rrrr'),'dd-mm-rrrr')) snr_flag,
219 'SUPER_AGED_FLAG=P', pay_kr_ff_functions_pkg.super_aged_flag(per.national_identifier,to_date('31-12-'||to_char(p_effective_date,'rrrr'),'dd-mm-rrrr')) super_aged_flag,
220 /* Bug 6784288 */
221 'ADDTL_CHILD_FLAG=P', '0' addtl_child_flag,
222 /* End of Bug 6622876 */
223 '1' l_dummy
224 from per_people_f per,
225 per_assignments_f asg
226 where asg.assignment_id = p_assignment_id
227 and per.person_id = asg.person_id
228 and p_effective_date between per.effective_start_date and per.effective_end_date
229 and p_effective_date between asg.effective_start_date and asg.effective_end_date
230 union
231 select 'CONTACT_TYPE=P', pkc.rel_code contact_type,
232 'NATIONALITY=P', pkc.cont_nationality nationality,
233 'BASIC_FLAG=P', decode(pkc.cont_information2,'Y',decode(pay_kr_ff_functions_pkg.dpnt_eligible_for_basic_exem(
234 pkc.contact_type,pkc.national_identifier, pkc.cont_information2, pkc.cont_information4,
235 pkc.cont_information8, p_effective_date),'Y','1',' '),' ') basic_exem_flag,
236 'DISABLE_FLAG=P', decode(pkc.cont_information4,'Y','1',' ') disabled_exem_flag,
237 'RAISING_CHILD=P', decode(pkc.cont_information7 ,'Y',decode(pay_kr_ff_functions_pkg.child_flag(
238 pkc.national_identifier,p_effective_date), 'Y', '1', ' '), ' ') child_raising_exem,
239 'INS_PREM_NTS=P', to_char(nvl(cei.cei_information1, '0')+ nvl(cei.cei_information10, '0')) ins_prem_nts,
240 'INS_PREM_OTH=P', to_char(nvl(cei.cei_information2, '0')+ nvl(cei.cei_information11, '0')) ins_prem_oth,
241 'MED_EXP_NTS=P', nvl(cei.cei_information3, '0') med_exp_nts,
242 'MED_EXP_OTH=P', nvl(cei.cei_information4, '0') med_exp_oth,
243 'EDUC_EXP_NTS=P', nvl(cei.cei_information5, '0') educ_exp_nts,
244 'EDUC_EXP_OTH=P', nvl(cei.cei_information6, '0') educ_exp_oth,
245 'CARD_EXP_NTS=P', nvl(cei.cei_information7, '0') card_exp_nts,
246 'CARD_EXP_OTH=P', nvl(cei.cei_information8, '0') card_exp_oth,
247 'CASH_EXP_NTS=P', nvl(cei.cei_information9, '0') cash_exp_nts,
248 'CONT_NAME=P', pkc.full_name full_name,
249 'CONT_NI=P', pkc.national_identifier national_identifier,
250 /* Bug 6622876 */
251 'SNR_FLAG=P', decode(pkc.cont_information3,'Y',pay_kr_ff_functions_pkg.aged_flag(pkc.national_identifier,to_date('31-12-'||to_char(p_effective_date,'rrrr'),'dd-mm-rrrr'))) snr_flag,
252 'SUPER_AGED_FLAG=P', decode(pkc.cont_information3,'Y',pay_kr_ff_functions_pkg.super_aged_flag(pkc.national_identifier,to_date('31-12-'||to_char(p_effective_date,'rrrr'),'dd-mm-rrrr'))) super_aged_flag,
253 /* Bug 6784288 Bug 6825145 */
254 'ADDTL_CHILD_FLAG=P', decode(pkc.cont_information2,'Y',decode(decode(pkc.cont_information11,'4',pay_kr_ff_functions_pkg.underaged_dpnt_flag(pkc.contact_type,pkc.national_identifier,p_effective_date),
255 pay_kr_ff_functions_pkg.addtl_child_flag(pkc.contact_type,pkc.national_identifier,p_effective_date)),'Y','1','0'),'0') addtl_child_flag,
256 /* End of Bug 6622876 */
257 '2' l_dummy
258 from pay_kr_cont_details_v pkc,
259 per_contact_extra_info_f cei -- Bug 5872042
260 where pkc.assignment_id = p_assignment_id
261 and p_effective_date between pkc.emp_start_date and pkc.emp_end_date
262 and pay_kr_ff_functions_pkg.is_exempted_dependent(pkc.contact_type, pkc.national_identifier, pkc.cont_information2,
263 pkc.cont_information3,
264 pkc.cont_information4,
265 pkc.cont_information7,
266 pkc.cont_information8,
267 p_effective_date,
268 pkc.cont_information10,
269 pkc.cont_information12,
270 pkc.cont_information13,
271 pkc.cont_information14,
272 cei.contact_extra_info_id) = 'Y'
273 and to_char(cei.effective_start_date(+), 'yyyy') = to_char(p_effective_date,'yyyy')
274 and cei.information_type(+) = 'KR_DPNT_EXPENSE_INFO'
275 and cei.contact_relationship_id(+) = pkc.contact_relationship_id
276 and p_effective_date between nvl(pkc.date_start, p_effective_date)
277 and decode(pkc.cont_information9, 'D',trunc(add_months(nvl(pkc.date_end, p_effective_date),12),'YYYY')-1,
278 nvl(pkc.date_end, p_effective_date) )
279 and p_effective_date between nvl(ADDRESS_START_DATE, p_effective_date) and nvl(ADDRESS_END_DATE, p_effective_date)
280 and p_effective_date between pkc.CONT_START_DATE and pkc.CONT_END_DATE
281 order by l_dummy, national_identifier;
282 -- End of Bug 5872042
283 -- End of Bug 5654127
284 ------------------------------------------------------------------------
285
286 end pay_kr_yea_magtape_pkg;