[Home] [Help]
PACKAGE: APPS.PAY_KR_YEA_MAGTAPE_PKG
Source
1 package pay_kr_yea_magtape_pkg AUTHID CURRENT_USER as
2 /* $Header: pykryeam.pkh 120.26.12020000.6 2013/02/20 07:06:10 scireddy 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 g_rep_period_code varchar2(1) := '2'; -- Bug 9213683
22 --
23 ------------------------------------------------------------------------
24 cursor csr_a is
25 select
26 'BUSINESS_GROUP_ID=C', to_char(bp.business_group_id),
27 'BUSINESS_GROUP_ID=P', to_char(bp.business_group_id),
28 'BP_ORGANIZATION_NAME_A=P', bptl.name,
29 'TAX_OFFICE_CODE_A=P', hoi1.org_information9,
30 'BP_NUMBER_A=P', hoi2.org_information2,
31 'CORP_ORGANIZATION_NAME=P', corptl.name,
32 'CORP_NAME=P', hoi3.org_information1,
33 'CORP_NUMBER=P', hoi3.org_information2,
34 'CORP_REPR_NAME=P', hoi3.org_information6,
35 'CORP_PHONE_NUMBER=P', loc.telephone_number_1,
36 'B_RECORDS=P', to_char(g_b_records),
37 'HOME_TAX_ID=P', nvl(pay_magtape_generic.get_parameter_value('HOME_TAX_ID'), ' ')
38 from hr_organization_information hoi3,
39 hr_locations_all loc,
40 hr_all_organization_units_tl corptl,
41 hr_organization_units corp,
42 hr_organization_information hoi2,
43 hr_organization_information hoi1,
44 hr_all_organization_units_tl bptl,
45 hr_organization_units bp
46 where bp.organization_id = g_business_place_id
47 and bptl.organization_id = bp.organization_id
48 and bptl.language = userenv('LANG')
49 and hoi1.organization_id = bp.organization_id
50 and hoi1.org_information_context = 'KR_INCOME_TAX_OFFICE'
51 and hoi2.organization_id = hoi1.organization_id
52 and hoi2.org_information_context = 'KR_BUSINESS_PLACE_REGISTRATION'
53 and corp.organization_id = to_number(hoi2.org_information10)
54 and corptl.organization_id = corp.organization_id
55 and corptl.language = userenv('LANG')
56 and loc.location_id(+) = corp.location_id
57 and loc.style(+) = 'KR'
58 and hoi3.organization_id = corp.organization_id
59 and hoi3.org_information_context = 'KR_CORPORATE_INFORMATION';
60
61 cursor csr_b is
62 select DISTINCT 'BP_NUMBER=P', hoi2.org_information2,
63 'TAX_OFFICE_CODE=P', hoi3.org_information9,
64 'C_RECORDS=P', pay_kr_yea_magtape_fun_pkg.b_data(hoi2.org_information2, hoi3.org_information9, 'C_RECORDS'),
65 'TAXABLE=P', pay_kr_yea_magtape_fun_pkg.b_data(hoi2.org_information2, hoi3.org_information9, 'TAXABLE'),
66 'ANNUAL_ITAX=P', pay_kr_yea_magtape_fun_pkg.b_data(hoi2.org_information2, hoi3.org_information9, 'ANNUAL_ITAX'),
67 'ANNUAL_RTAX=P', pay_kr_yea_magtape_fun_pkg.b_data(hoi2.org_information2, hoi3.org_information9, 'ANNUAL_RTAX'),
68 'ANNUAL_STAX=P', pay_kr_yea_magtape_fun_pkg.b_data(hoi2.org_information2, hoi3.org_information9, 'ANNUAL_STAX'),
69 'D_RECORDS=P', pay_kr_yea_magtape_fun_pkg.b_data(hoi2.org_information2, hoi3.org_information9, 'D_RECORDS'),
70 'REP_PERIOD_CODE=P', g_rep_period_code
71 from hr_organization_information hoi3,
72 hr_organization_information hoi2,
73 hr_all_organization_units_tl bptl2,
74 hr_organization_units bp2,
75 hr_organization_information hoi1,
76 hr_organization_units bp1
77 where bp1.organization_id = pay_magtape_generic.get_parameter_value('BUSINESS_PLACE_ID') --Bug 5069923
78 and hoi1.organization_id = bp1.organization_id
79 and hoi1.org_information_context = 'KR_BUSINESS_PLACE_REGISTRATION'
80 and bp2.business_group_id = bp1.business_group_id
81 --Bug 5069923
82 and ( (pay_magtape_generic.get_parameter_value('REPORT_FOR')='A')
83 or ( (hoi2.organization_id in ( select posev.ORGANIZATION_ID_child
84 from PER_ORG_STRUCTURE_ELEMENTS posev
85 where posev.org_structure_version_id=(pay_magtape_generic.get_parameter_value('ORG_STRUC_VERSION_ID'))
86 and exists ( select null
87 from hr_organization_information
88 where organization_id = posev.ORGANIZATION_ID_child
89 and org_information_context = 'CLASS'
90 and org_information1 = 'KR_BUSINESS_PLACE'
91 )
92 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')))
93 connect by prior ORGANIZATION_ID_child = ORGANIZATION_ID_PARENT
94 )
95 )
96 or (hoi2.organization_id = pay_magtape_generic.get_parameter_value('BUSINESS_PLACE_ID')
97 )
98 )
99 )
100 and hoi2.organization_id = bp2.organization_id
101 and bptl2.organization_id = bp2.organization_id
102 and bptl2.language = userenv('LANG')
103 and hoi2.org_information_context = 'KR_BUSINESS_PLACE_REGISTRATION'
104 and hoi2.org_information10 = hoi1.org_information10
105 and hoi3.organization_id = hoi2.organization_id
106 and hoi3.org_information_context = 'KR_INCOME_TAX_OFFICE'
107 and exists(
108 select null
109 from pay_assignment_actions paa,
110 pay_payroll_actions ppa
111 where ppa.report_type = 'YEA'
112 and ppa.report_qualifier = 'KR'
113 and ppa.business_group_id = bp1.business_group_id
114 -- Bug 3248513
115 and ( (ppa.report_category in (g_normal_yea, g_interim_yea, g_re_yea)) or (ppa.payroll_action_id = g_payroll_action_id) )
116 and to_number(to_char(ppa.effective_date, 'YYYY')) = g_target_year
117 --
118 and ppa.action_type in ('B','X')
119 and paa.payroll_action_id = ppa.payroll_action_id
120 and paa.tax_unit_id = bp2.organization_id
121 and paa.action_status = 'C')
122 order by 4;
123
124 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
125 select
126 'PAYROLL_ID=C', to_char(ppa.payroll_id),
127 'PAYROLL_ACTION_ID=C', to_char(ppa.payroll_action_id),
128 'ASSIGNMENT_ID=C', to_char(paa.assignment_id),
129 'ASSIGNMENT_ID=P', to_char(paa.assignment_id),
130 'ASSIGNMENT_ACTION_ID=C', to_char(paa.assignment_action_id),
131 'ASSIGNMENT_ACTION_ID=P', to_char(paa.assignment_action_id),
132 'DATE_EARNED=C', fnd_date.date_to_canonical(ppa.effective_date),
133 'EFFECTIVE_DATE=P', fnd_date.date_to_canonical(ppa.effective_date),
134 'NATIONAL_IDENTIFIER=P', pp.national_identifier,
135 'FULL_NAME=P', pp.last_name || pp.first_name,
136 'HIRE_DATE=P', fnd_date.date_to_canonical(pds.date_start),
137 'TERMINATION_DATE=P', fnd_date.date_to_canonical(pds.actual_termination_date),
138 'D_RECORDS_PER_C=P', pay_kr_yea_magtape_fun_pkg.c_data(paa.assignment_id, 'D_RECORDS_PER_C'),
139 'COUNTRY_CODE=P', ft.territory_code,
140 'DEPENDENT_COUNT=P', pay_kr_yea_magtape_fun_pkg.e_record_count(paa.assignment_id,ppa.effective_date) , -- 4738717
141 /* Bug 10184055 */
142 'NATIONALITY_CODE=P', pp.NATIONALITY,
143 'REPORT_CATEGORY=P', ppa.report_category
144 from per_people_f pp,
145 per_periods_of_service pds,
146 per_assignments_f pa,
147 pay_assignment_actions paa,
148 pay_payroll_actions ppa,
149 hr_organization_units bp,
150 fnd_territories ft,
151 per_addresses adr,
152 hr_organization_information hoi1,
153 hr_organization_information hoi2,
154 hr_organization_information hoi3
155 where hoi1.org_information2 = p_bp_number --Bug2822459
156 and hoi2.org_information9 = p_tax_office_code --Bug2822459
157 and hoi1.organization_id = bp.organization_id
158 and hoi1.organization_id = hoi2.organization_id
159 and hoi1.org_information_context = 'KR_BUSINESS_PLACE_REGISTRATION'
160 and hoi2.org_information_context = 'KR_INCOME_TAX_OFFICE'
161 and hoi3.organization_id = to_number(hoi1.org_information10)
162 and hoi3.org_information_context = 'KR_CORPORATE_INFORMATION'
163 and ppa.business_group_id = bp.business_group_id
164 and ppa.report_type = 'YEA'
165 and ppa.report_qualifier = 'KR'
166 -- Bug 3248513
167 and ( (ppa.report_category in (g_normal_yea, g_interim_yea, g_re_yea)) or (ppa.payroll_action_id = g_payroll_action_id) )
168 and to_number(to_char(ppa.effective_date, 'YYYY')) = g_target_year
169 --
170 and ppa.action_type in ('B','X')
171 and paa.payroll_action_id = ppa.payroll_action_id
172 and ppa.payroll_action_id = ppa.payroll_action_id
173 -- Bug 3248513
174 and ((g_assignment_set_id is null) or (hr_assignment_set.assignment_in_set(g_assignment_set_id, paa.assignment_id) = 'Y'))
175 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
176 --
177 and paa.tax_unit_id = bp.organization_id
178 and paa.action_status = 'C'
179 and pa.assignment_id = paa.assignment_id
180 and ppa.effective_date
181 between pa.effective_start_date and pa.effective_end_date
182 and pds.period_of_service_id = pa.period_of_service_id
183 and pp.person_id = pds.person_id
184 and adr.person_id(+) = pp.person_id
185 and adr.style(+) = 'KR'
186 and adr.address_type(+) = 'KR_C'
187 and adr.country = ft.territory_code(+)
188 and ppa.effective_date between nvl(adr.date_from,ppa.effective_date) and nvl(adr.date_to,to_Date('31-12-4712','dd-mm-yyyy')) /* Bug 11793954 */
189 and ppa.effective_date
190 between pp.effective_start_date and pp.effective_end_date
191 order by 16;
192
193 cursor csr_d(p_assignment_id number default to_number(pay_magtape_generic.get_parameter_value('ASSIGNMENT_ID'))) is
194 select
195 'PREV_BP_NUMBER=P', aei.aei_information3,
196 'PREV_BP_NAME=P', aei.aei_information2,
197 'PREV_TAXABLE_MTH=P', nvl(aei.aei_information4, 0),
198 'PREV_TAXABLE_BON=P', nvl(aei.aei_information5, 0),
199 'PREV_SP_IRREG_BONUS=P', nvl(aei.aei_information6, 0),
200 'PREV_STCK_PUR_OPT_EXEC_EARN=P', nvl(aei.aei_information17, 0), -- Bug 6622876
201 -- Start of Bug 9213683: Added new columns for the 2009 YEA Efile Updates
202 'PREV_HIRE_DATE=P', aei.aei_information23,
203 'PREV_TERM_DATE=P', aei.aei_information1,
204 -- Start of Bug 9386289
205 'PREV_TAX_BRK_FDATE=P', nvl(aei.aei_information24,fnd_date.date_to_canonical(to_date('1900/01/01','YYYY/MM/DD'))),
206 'PREV_TAX_BRK_TDATE=P', nvl(aei.aei_information25,fnd_date.date_to_canonical(to_date('1900/01/01','YYYY/MM/DD'))),
207 -- End of Bug 9386289
208 'PREV_ESOP_EARN=P', nvl(aei.aei_information26, 0),
209 'PREV_NTAX_G01=P', pay_kr_yea_magtape_fun_pkg.prev_non_tax_values(p_assignment_id,aei.aei_information3,'G01',fnd_date.canonical_to_date(pay_magtape_generic.get_parameter_value('EFFECTIVE_DATE'))),
210 'PREV_NTAX_H01=P', pay_kr_yea_magtape_fun_pkg.prev_non_tax_values(p_assignment_id,aei.aei_information3,'H01',fnd_date.canonical_to_date(pay_magtape_generic.get_parameter_value('EFFECTIVE_DATE'))),
211 'PREV_NTAX_H05=P', pay_kr_yea_magtape_fun_pkg.prev_non_tax_values(p_assignment_id,aei.aei_information3,'H05',fnd_date.canonical_to_date(pay_magtape_generic.get_parameter_value('EFFECTIVE_DATE'))),
212 'PREV_NTAX_H06=P', pay_kr_yea_magtape_fun_pkg.prev_non_tax_values(p_assignment_id,aei.aei_information3,'H06',fnd_date.canonical_to_date(pay_magtape_generic.get_parameter_value('EFFECTIVE_DATE'))),
213 'PREV_NTAX_H07=P', pay_kr_yea_magtape_fun_pkg.prev_non_tax_values(p_assignment_id,aei.aei_information3,'H07',fnd_date.canonical_to_date(pay_magtape_generic.get_parameter_value('EFFECTIVE_DATE'))),
214 'PREV_NTAX_H08=P', pay_kr_yea_magtape_fun_pkg.prev_non_tax_values(p_assignment_id,aei.aei_information3,'H08',fnd_date.canonical_to_date(pay_magtape_generic.get_parameter_value('EFFECTIVE_DATE'))),
215 'PREV_NTAX_H09=P', pay_kr_yea_magtape_fun_pkg.prev_non_tax_values(p_assignment_id,aei.aei_information3,'H09',fnd_date.canonical_to_date(pay_magtape_generic.get_parameter_value('EFFECTIVE_DATE'))),
216 'PREV_NTAX_H10=P', pay_kr_yea_magtape_fun_pkg.prev_non_tax_values(p_assignment_id,aei.aei_information3,'H10',fnd_date.canonical_to_date(pay_magtape_generic.get_parameter_value('EFFECTIVE_DATE'))),
217 --Start of Bug 15911822 2012 YEA EFile Updates
218 'PREV_NTAX_H14=P', pay_kr_yea_magtape_fun_pkg.prev_non_tax_values(p_assignment_id,aei.aei_information3,'H14',fnd_date.canonical_to_date(pay_magtape_generic.get_parameter_value('EFFECTIVE_DATE'))),
219 'PREV_NTAX_H15=P', pay_kr_yea_magtape_fun_pkg.prev_non_tax_values(p_assignment_id,aei.aei_information3,'H15',fnd_date.canonical_to_date(pay_magtape_generic.get_parameter_value('EFFECTIVE_DATE'))),
220 --End of Bug 15911822
221 'PREV_NTAX_H11=P', pay_kr_yea_magtape_fun_pkg.prev_non_tax_values(p_assignment_id,aei.aei_information3,'H11',fnd_date.canonical_to_date(pay_magtape_generic.get_parameter_value('EFFECTIVE_DATE'))),
222 'PREV_NTAX_H12=P', pay_kr_yea_magtape_fun_pkg.prev_non_tax_values(p_assignment_id,aei.aei_information3,'H12',fnd_date.canonical_to_date(pay_magtape_generic.get_parameter_value('EFFECTIVE_DATE'))),
223 'PREV_NTAX_H13=P', pay_kr_yea_magtape_fun_pkg.prev_non_tax_values(p_assignment_id,aei.aei_information3,'H13',fnd_date.canonical_to_date(pay_magtape_generic.get_parameter_value('EFFECTIVE_DATE'))),
224 'PREV_NTAX_I01=P', pay_kr_yea_magtape_fun_pkg.prev_non_tax_values(p_assignment_id,aei.aei_information3,'I01',fnd_date.canonical_to_date(pay_magtape_generic.get_parameter_value('EFFECTIVE_DATE'))),
225 'PREV_NTAX_K01=P', pay_kr_yea_magtape_fun_pkg.prev_non_tax_values(p_assignment_id,aei.aei_information3,'K01',fnd_date.canonical_to_date(pay_magtape_generic.get_parameter_value('EFFECTIVE_DATE'))),
226 'PREV_NTAX_M01=P', pay_kr_yea_magtape_fun_pkg.prev_non_tax_values(p_assignment_id,aei.aei_information3,'M01',fnd_date.canonical_to_date(pay_magtape_generic.get_parameter_value('EFFECTIVE_DATE'))),
227 'PREV_NTAX_M02=P', pay_kr_yea_magtape_fun_pkg.prev_non_tax_values(p_assignment_id,aei.aei_information3,'M02',fnd_date.canonical_to_date(pay_magtape_generic.get_parameter_value('EFFECTIVE_DATE'))),
228 'PREV_NTAX_M03=P', pay_kr_yea_magtape_fun_pkg.prev_non_tax_values(p_assignment_id,aei.aei_information3,'M03',fnd_date.canonical_to_date(pay_magtape_generic.get_parameter_value('EFFECTIVE_DATE'))),
229 'PREV_NTAX_O01=P', nvl(aei.aei_information8, 0),
230 'PREV_NTAX_Q01=P', nvl(aei.aei_information21, 0),
231 -- Bug : 13484283 : 2011 YEA Efile Updates.
232 'PREV_NTAX_R10=P', pay_kr_yea_magtape_fun_pkg.prev_non_tax_values(p_assignment_id,aei.aei_information3,'R10',fnd_date.canonical_to_date(pay_magtape_generic.get_parameter_value('EFFECTIVE_DATE'))),
233 'PREV_NTAX_S01=P', pay_kr_yea_magtape_fun_pkg.prev_non_tax_values(p_assignment_id,aei.aei_information3,'S01',fnd_date.canonical_to_date(pay_magtape_generic.get_parameter_value('EFFECTIVE_DATE'))),
234 'PREV_NTAX_T01=P', pay_kr_yea_magtape_fun_pkg.prev_non_tax_values(p_assignment_id,aei.aei_information3,'T01',fnd_date.canonical_to_date(pay_magtape_generic.get_parameter_value('EFFECTIVE_DATE'))),
235 'PREV_NTAX_X01=P', nvl(aei.aei_information22, 0),
236 'PREV_NTAX_Y01=P', pay_kr_yea_magtape_fun_pkg.prev_non_tax_values(p_assignment_id,aei.aei_information3,'Y01',fnd_date.canonical_to_date(pay_magtape_generic.get_parameter_value('EFFECTIVE_DATE'))),
237 'PREV_NTAX_Y02=P', pay_kr_yea_magtape_fun_pkg.prev_non_tax_values(p_assignment_id,aei.aei_information3,'Y02',fnd_date.canonical_to_date(pay_magtape_generic.get_parameter_value('EFFECTIVE_DATE'))),
238 'PREV_NTAX_Y03=P', pay_kr_yea_magtape_fun_pkg.prev_non_tax_values(p_assignment_id,aei.aei_information3,'Y03',fnd_date.canonical_to_date(pay_magtape_generic.get_parameter_value('EFFECTIVE_DATE'))),
239 'PREV_NTAX_Y20=P', pay_kr_yea_magtape_fun_pkg.prev_non_tax_values(p_assignment_id,aei.aei_information3,'Y20',fnd_date.canonical_to_date(pay_magtape_generic.get_parameter_value('EFFECTIVE_DATE'))),
240 'PREV_NTAX_Z01=P', pay_kr_yea_magtape_fun_pkg.prev_non_tax_values(p_assignment_id,aei.aei_information3,'Z01',fnd_date.canonical_to_date(pay_magtape_generic.get_parameter_value('EFFECTIVE_DATE'))),
241 'PREV_NTAX_Y21=P', pay_kr_yea_magtape_fun_pkg.prev_non_tax_values(p_assignment_id,aei.aei_information3,'Y21',fnd_date.canonical_to_date(pay_magtape_generic.get_parameter_value('EFFECTIVE_DATE'))),
242 -- Start of Bug 15911822 2012 YEA EFile Updates
243 'PREV_NTAX_T10=P', pay_kr_yea_magtape_fun_pkg.prev_non_tax_values(p_assignment_id,aei.aei_information3,'T10',fnd_date.canonical_to_date(pay_magtape_generic.get_parameter_value('EFFECTIVE_DATE'))),
244 'PREV_NTAX_T20=P', pay_kr_yea_magtape_fun_pkg.prev_non_tax_values(p_assignment_id,aei.aei_information3,'T20',fnd_date.canonical_to_date(pay_magtape_generic.get_parameter_value('EFFECTIVE_DATE'))),
245 -- Bug : 13484283
246 'PREV_NTAX_ZZ=P', pay_kr_yea_magtape_fun_pkg.prev_non_tax_values(p_assignment_id,aei.aei_information3,'Y22',fnd_date.canonical_to_date(pay_magtape_generic.get_parameter_value('EFFECTIVE_DATE'))),
247 'PREV_ITAX=P', nvl(aei.aei_information13, 0),
248 'PREV_RTAX=P', nvl(aei.aei_information14, 0),
249 'PREV_STAX=P', nvl(aei.aei_information15, 0),
250 'PREV_EFF_DATE=P', pay_magtape_generic.get_parameter_value('EFFECTIVE_DATE') -- Bug 9386289
251 -- End of Bug 9213683
252 from per_assignment_extra_info aei
253 where aei.assignment_id = p_assignment_id
254 and aei.information_type = 'KR_YEA_PREV_ER_INFO'
255 and to_number(to_char(fnd_date.canonical_to_date(aei.aei_information1), 'YYYY')) = g_target_year
256 order by aei_information1, 2;
257 ------------------------------------------------------------------------
258
259 -- Note: Any change in this cursor must be included in
260 -- pay_kr_yea_magtape_fun_pkg.e_record_count also.
261
262 cursor csr_e( p_assignment_id varchar2 default pay_magtape_generic.get_parameter_value('ASSIGNMENT_ID'),
263 p_effective_date date default fnd_date.canonical_to_date(pay_magtape_generic.get_parameter_value('EFFECTIVE_DATE'))
264 ) is
265 -- Bug 5654127
266 -- Bug 7661820
267 select 'CONTACT_TYPE=P', '0' contact_type,
268 'NATIONALITY=P', decode(pay_kr_ff_functions_pkg.ni_nationality(asg.assignment_id,p_effective_date),'K','1','9') nationality,
269 'BASIC_FLAG=P', '1' basic_exem_flag,
270 'DISABLE_FLAG=P', decode(pay_kr_ff_functions_pkg.disabled_flag(per.person_id,p_effective_date),'Y','1',' ') disabled_exem_flag,
271 'RAISING_CHILD=P', ' ' child_raising_exem,
272 'INS_PREM_NTS=P', '0' ins_prem_nts,
273 'INS_PREM_OTH=P', '0' ins_prem_oth,
274 'MED_EXP_NTS=P', '0' med_exp_nts,
275 'MED_EXP_OTH=P', '0' med_exp_oth,
276 'EDUC_EXP_NTS=P', '0' educ_exp_nts,
277 'EDUC_EXP_OTH=P', '0' educ_exp_oth,
278 'CARD_EXP_NTS=P', '0' card_exp_nts,
279 'CARD_EXP_OTH=P', '0' card_exp_oth,
280 'CASH_EXP_NTS=P', '0' cash_exp_nts,
281 'DON_EXP_NTS=P', '0' don_exp_nts, -- Bug 9213683
282 'DON_EXP_OTH=P', '0' don_exp_oth, -- Bug 7799077
283 'DIRECT_PAYMENT_NTS=P', '0' direct_payment_nts,
284 'DIRECT_PAYMENT_OTH=P', '0' direct_payment_oth,
285 'TM_EXP_NTS=P', '0' tm_exp_nts, -- Bug 15911822
286 'TM_EXP_OTH=P', '0' tm_exp_oth, -- Bug 15911822
287 'TUTION_EXP_OTH=P', '0' tution_exp_oth, -- Bug 15911822
288 'CONT_NAME=P', 'Y' full_name,
289 'CONT_NI=P', per.national_identifier national_identifier,
290 'NEW_BORN_ADOPTED=P', ' ' new_born_adopted, -- Bug 7799077
291 /* Bug 6622876 */
292 '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,
293 '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,
294 /* End of Bug 6622876 */
295 '1' l_dummy
296 from per_people_f per,
297 per_assignments_f asg
298 where asg.assignment_id = p_assignment_id
299 and per.person_id = asg.person_id
300 and p_effective_date between per.effective_start_date and per.effective_end_date
301 and p_effective_date between asg.effective_start_date and asg.effective_end_date
302 union
303 -- Bug 8644512 : Added code to return correct contact type depending on the year
304 select 'CONTACT_TYPE=P', pay_kr_ff_functions_pkg.get_cont_lookup_code(rel_code,
305 to_number(to_char(p_effective_date,'YYYY'))) contact_type,
306 'NATIONALITY=P', pkc.cont_nationality nationality,
307 'BASIC_FLAG=P', decode(pkc.cont_information2,'Y',decode(pay_kr_ff_functions_pkg.dpnt_eligible_for_basic_exem(
308 pkc.contact_type,pkc.cont_information11,pkc.national_identifier, pkc.cont_information2,
309 pkc.cont_information4,pkc.cont_information8, p_effective_date),'Y','1',' '),' ') basic_exem_flag,
310 'DISABLE_FLAG=P', decode(pkc.cont_information4,'Y','1',' ') disabled_exem_flag,
311 'RAISING_CHILD=P', decode(pkc.cont_information7 ,'Y',decode(pay_kr_ff_functions_pkg.child_flag(
312 pkc.cont_information11,pkc.contact_type,
313 pkc.national_identifier,p_effective_date), 'Y', '1', ' '), ' ') child_raising_exem,
314 'INS_PREM_NTS=P', to_char(nvl(cei.cei_information1, '0')+ nvl(cei.cei_information10, '0')) ins_prem_nts,
315 'INS_PREM_OTH=P', to_char(nvl(cei.cei_information2, '0')+ nvl(cei.cei_information11, '0')) ins_prem_oth,
316 'MED_EXP_NTS=P', nvl(cei.cei_information3, '0') med_exp_nts,
317 'MED_EXP_OTH=P', nvl(cei.cei_information4, '0') med_exp_oth,
318 'EDUC_EXP_NTS=P', nvl(cei.cei_information5, '0') educ_exp_nts,
319 'EDUC_EXP_OTH=P', nvl(cei.cei_information6, '0') educ_exp_oth,
320 'CARD_EXP_NTS=P', nvl(cei.cei_information7, '0') card_exp_nts,
321 'CARD_EXP_OTH=P', nvl(cei.cei_information8, '0') card_exp_oth,
322 'CASH_EXP_NTS=P', nvl(cei.cei_information9, '0') cash_exp_nts,
323 'DON_EXP_NTS=P', nvl(cei.cei_information14, '0') don_exp_nts, -- Bug 9213683
324 'DON_EXP_OTH=P', nvl(cei.cei_information15, '0') don_exp_oth, -- Bug 7799077
325 'DIRECT_PAYMENT_NTS=P', nvl(cei.cei_information16, '0') direct_payment_nts,
326 'DIRECT_PAYMENT_OTH=P', nvl(cei.cei_information17, '0') direct_payment_oth,
327 'TM_EXP_NTS=P', to_char( nvl(cei.cei_information18, '0') + nvl(cei.cei_information20, '0')+nvl(cei.cei_information21, '0')) tm_exp_nts, -- Bug 16358967
328 'TM_EXP_OTH=P', to_char( nvl(cei.cei_information19, '0')+ nvl(cei.cei_information22, '0')) tm_exp_oth, -- Bug 16358967
329 'TUTION_EXP_OTH=P', to_char( nvl(cei.cei_information24, '0')) tution_exp_oth, -- Bug 16358967
330 'CONT_NAME=P', pkc.full_name full_name,
331 'CONT_NI=P', pkc.national_identifier national_identifier,
332 'NEW_BORN_ADOPTED=P', decode(nvl(cei.cei_information13, 'N'),'Y','1',' ') new_born_adopted, -- Bug 7799077
333 /* Bug 6622876 */
334 '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,
335 '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,
336 /* End of Bug 6622876 */
337 '2' l_dummy
338 from pay_kr_cont_details_v pkc,
339 per_contact_extra_info_f cei -- Bug 5872042
340 where pkc.assignment_id = p_assignment_id
341 and p_effective_date between pkc.emp_start_date and pkc.emp_end_date
342 and pay_kr_ff_functions_pkg.is_exempted_dependent(pkc.contact_type, pkc.cont_information11,pkc.national_identifier, pkc.cont_information2,
343 pkc.cont_information3,
344 pkc.cont_information4,
345 pkc.cont_information7,
346 pkc.cont_information8,
347 p_effective_date,
348 pkc.cont_information10,
349 pkc.cont_information12,
350 pkc.cont_information13,
351 pkc.cont_information14,
352 cei.contact_extra_info_id) = 'Y'
353 and to_char(cei.effective_start_date(+), 'yyyy') = to_char(p_effective_date,'yyyy')
354 and cei.information_type(+) = 'KR_DPNT_EXPENSE_INFO'
355 and cei.contact_relationship_id(+) = pkc.contact_relationship_id
356 and p_effective_date between nvl(pkc.date_start, p_effective_date)
357 and decode(pkc.cont_information9, 'D',trunc(add_months(nvl(pkc.date_end, p_effective_date),12),'YYYY')-1,
358 nvl(pkc.date_end, p_effective_date) )
359 and p_effective_date between nvl(ADDRESS_START_DATE, p_effective_date) and nvl(ADDRESS_END_DATE, p_effective_date)
360 and p_effective_date between pkc.CONT_START_DATE and pkc.CONT_END_DATE
361 order by l_dummy, national_identifier;
362 -- End of Bug 5872042
363 -- End of Bug 5654127
364 ------------------------------------------------------------------------
365 -- Bug 10184055
366 cursor csr_f( p_assignment_id varchar2 default pay_magtape_generic.get_parameter_value('ASSIGNMENT_ID'),
367 p_effective_date date default fnd_date.canonical_to_date(pay_magtape_generic.get_parameter_value('EFFECTIVE_DATE')),
368 p_assignment_action_id varchar2 default pay_magtape_generic.get_parameter_value('ASSIGNMENT_ACTION_ID')
369 ) is
370 select * from (
371 select
372 'INFORMATION_TYPE=P', information_type,
373 'EXEMPTION_TYPE=P', decode(aei_information2,'1','11','2','12') exemption_type,
374 'FIN_INST_CODE=P', aei_information3,
375 'FIN_INST_NAME=P', hr_general.decode_lookup('KR_YEA_FIN_INST_CODES',aei_information3),
376 'ACCOUNT_NUMBER=P', aei_information4,
377 'YEAR=P', '00' year,
378 'CONT_AMOUNT=P', aei_information5,
379 'F_RECORD_COUNT=P', pay_kr_yea_magtape_fun_pkg.f_record_count(p_assignment_id,p_effective_date,p_assignment_action_id),
380 'EXEMPTION_AMOUNT=P', pay_kr_yea_magtape_fun_pkg.get_sep_pen_eligible(p_assignment_id,p_assignment_action_id,p_effective_date, information_type,aei_information6)
381 from per_assignment_extra_info
382 where
383 information_type = 'KR_YEA_SEP_PEN_DETAILS'
384 and assignment_id = p_assignment_id
385 and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY')
386 and pay_kr_yea_magtape_fun_pkg.get_sep_pen_eligible(p_assignment_id,p_assignment_action_id,
387 p_effective_date, information_type,aei_information6) > 0
388 order by to_number(aei_information6))
389 union all
390
391 select * from (
392 select
393 'INFORMATION_TYPE=P', information_type,
394 'EXEMPTION_TYPE=P', decode(aei_information2,'1','21','2','22') exemption_type,
395 'FIN_INST_CODE=P', aei_information3,
396 'FIN_INST_NAME=P', hr_general.decode_lookup('KR_YEA_FIN_INST_CODES',aei_information3),
397 'ACCOUNT_NUMBER=P', aei_information4,
398 'YEAR=P', '00' year,
399 'CONT_AMOUNT=P', aei_information5,
400 'F_RECORD_COUNT=P', pay_kr_yea_magtape_fun_pkg.f_record_count(p_assignment_id,p_effective_date,p_assignment_action_id),
401 'EXEMPTION_AMOUNT=P', pay_kr_yea_magtape_fun_pkg.get_sep_pen_eligible(p_assignment_id,p_assignment_action_id,p_effective_date, information_type,aei_information6)
402 from per_assignment_extra_info
403 where
404 information_type = 'KR_YEA_PEN_SAVING_DETAILS'
405 and assignment_id = p_assignment_id
406 and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY')
407 and pay_kr_yea_magtape_fun_pkg.get_sep_pen_eligible(p_assignment_id,p_assignment_action_id,
408 p_effective_date, information_type,aei_information6) > 0
409 order by to_number(aei_information6)
410 )
411 union all
412
413 select * from (
414 select
415 'INFORMATION_TYPE=P', information_type,
416 'EXEMPTION_TYPE=P', decode(aei_information2,'HST1','31','HST5','32','HST4','33','HST3','34') exemption_type,
417 'FIN_INST_CODE=P', aei_information3,
418 'FIN_INST_NAME=P', hr_general.decode_lookup('KR_YEA_FIN_INST_CODES',aei_information3),
419 'ACCOUNT_NUMBER=P', aei_information4,
420 'YEAR=P', '00' year,
421 'CONT_AMOUNT=P', aei_information5,
422 'F_RECORD_COUNT=P', pay_kr_yea_magtape_fun_pkg.f_record_count(p_assignment_id,p_effective_date,p_assignment_action_id),
423 'EXEMPTION_AMOUNT=P', pay_kr_yea_magtape_fun_pkg.get_sep_pen_eligible(p_assignment_id,p_assignment_action_id,p_effective_date, information_type,aei_information6)
424 from per_assignment_extra_info
425 where
426 information_type = 'KR_YEA_HOU_SAVING_DETAILS'
427 and assignment_id = p_assignment_id
428 and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY')
429 and pay_kr_yea_magtape_fun_pkg.get_sep_pen_eligible(p_assignment_id,p_assignment_action_id,
430 p_effective_date, information_type,aei_information6) > 0
431 order by to_number(aei_information6)
432 )
433 union all
434
435 select * from (
436 select
437 'INFORMATION_TYPE=P', information_type,
438 'EXEMPTION_TYPE=P', '41' exemption_type,
439 'FIN_INST_CODE=P', aei_information3,
440 'FIN_INST_NAME=P', hr_general.decode_lookup('KR_YEA_FIN_INST_CODES',aei_information3),
441 'ACCOUNT_NUMBER=P', aei_information4,
442 'YEAR=P', decode(aei_information2,'1','01','2','02','3','03') year,
443 'CONT_AMOUNT=P', aei_information5,
444 'F_RECORD_COUNT=P', pay_kr_yea_magtape_fun_pkg.f_record_count(p_assignment_id,p_effective_date,p_assignment_action_id),
445 'EXEMPTION_AMOUNT=P', pay_kr_yea_magtape_fun_pkg.get_sep_pen_eligible(p_assignment_id,p_assignment_action_id,p_effective_date, information_type,aei_information6)
446 from per_assignment_extra_info
447 where
448 information_type = 'KR_YEA_LT_STOCK_SAVING_DETAILS'
449 and assignment_id = p_assignment_id
450 and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY')
451 and pay_kr_yea_magtape_fun_pkg.get_sep_pen_eligible(p_assignment_id,p_assignment_action_id,
452 p_effective_date, information_type,aei_information6) > 0
453 order by to_number(aei_information6));
454
455 function GET_TAX_REDUCTION_DATE(p_assignment_id in number,
456 p_effective_date in date,
457 p_start_date out NOCOPY date,
458 p_end_date out NOCOPY date) return number;
459 ------------------------------------------------------------------------
460 end pay_kr_yea_magtape_pkg;