[Home] [Help]
PACKAGE BODY: APPS.PAY_KR_YEA_PKG
Source
1 package body pay_kr_yea_pkg as
2 /* $Header: pykryea.pkb 120.18.12010000.6 2008/08/12 10:26:35 vaisriva ship $ */
3 ------------------------------------------------------------------------
4 -- Constants
5 ------------------------------------------------------------------------
6 c_00010101 constant date := fnd_date.canonical_to_date('0001/01/01');
7 c_20020101 constant date := fnd_date.canonical_to_date('2002/01/01');
8 c_20030101 constant date := fnd_date.canonical_to_date('2003/01/01');
9 c_20040101 constant date := fnd_date.canonical_to_date('2004/01/01');
10 c_20050101 constant date := fnd_date.canonical_to_date('2005/01/01');
11 c_20060101 constant date := fnd_date.canonical_to_date('2006/01/01');
12 c_20070101 constant date := fnd_date.canonical_to_date('2007/01/01'); -- Bug 5734313
13 c_20080101 constant date := fnd_date.canonical_to_date('2008/01/01'); -- Bug 6705170
14 ------------------------------------------------------------------------
15 -- Global Variables
16 ------------------------------------------------------------------------
17 g_package constant varchar2(31) := ' pay_kr_yea_pkg.';
18 ------------------------------------------------------------------------
19 -- Defined Balance and Balance Value Cache
20 ------------------------------------------------------------------------
21 g_balance_value_tab pay_balance_pkg.t_balance_value_tab;
22 g_tax_adj_balance_tab pay_balance_pkg.t_balance_value_tab;
23 ------------------------------------------------------------------------
24 -- Element Cache
25 ------------------------------------------------------------------------
26 type t_element is record(
27 element_type_id number,
28 input_value_id_tbl hr_entry.number_table,
29 input_value_name_tbl t_varchar2_tbl);
30 --
31 g_tax t_element;
32 g_itax_adj t_element;
33 g_rtax_adj t_element;
34 g_stax_adj t_element;
35 ------------------------------------------------------------------------
36 -- User Entity Cache
37 ------------------------------------------------------------------------
38 type t_user_entity_id is record(
39 non_resident_flag number,
40 foreign_residency_flag number, -- Bug 6615356
41 fw_fixed_tax_rate number, -- 3546993
42 cur_taxable_mth number,
43 cur_taxable_bon number,
44 cur_sp_irreg_bonus number,
45 cur_stck_pur_opt_exec_earn number, -- Bug 6012258
46 cur_taxable number,
47 prev_taxable_mth number,
48 prev_taxable_bon number,
49 prev_sp_irreg_bonus number,
50 prev_stck_pur_opt_exec_earn number, -- Bug 6024342
51 prev_taxable number,
52 taxable_mth number,
53 taxable_bon number,
54 sp_irreg_bonus number,
55 stck_pur_opt_exec_earn number, -- Bug 6024342
56 research_payment number, -- Bug 6012258
57 taxable number,
58 non_taxable_ovs number,
59 non_taxable_ovt number,
60 non_taxable_oth number,
61 non_taxable number,
62 foreign_worker_income_exem number, -- 3546993
63 basic_income_exem number,
64 taxable_income number,
65 ee_tax_exem number,
66 dpnt_spouse_flag number,
67 dpnt_spouse_tax_exem number,
68 num_of_aged_dpnts number,
69 num_of_adult_dpnts number,
70 num_of_underaged_dpnts number,
71 num_of_dpnts number,
72 dpnt_tax_exem number,
73 num_of_ageds number,
74 -- Bug 3172960
75 num_of_super_ageds number,
76 num_of_new_born_adopted number, -- Bug 6705170
77 new_born_adopted_tax_exem number, -- Bug 6705170
78 aged_tax_exem number,
79 num_of_disableds number,
80 disabled_tax_exem number,
81 female_ee_flag number,
82 female_ee_tax_exem number,
83 num_of_children number,
84 child_tax_exem number,
85 num_of_addtl_child number, -- Bug 6784288
86 addl_child_tax_exem number, -- Bug 5756690
87 supp_tax_exem number,
88 hi_prem number,
89 hi_prem_tax_exem number,
90 long_term_ins_prem number, -- Bug 7164589
91 long_term_ins_prem_tax_exem number, -- Bug 7164589
92 ei_prem number,
93 ei_prem_tax_exem number,
94 pers_ins_name number,
95 pers_ins_prem number,
96 pers_ins_prem_tax_exem number,
97 disabled_ins_prem number,
98 disabled_ins_prem_tax_exem number,
99 ins_prem_tax_exem number,
100 med_exp number,
101 med_exp_disabled number,
102 med_exp_aged number,
103 -- Bug 3172960
104 med_exp_emp number,
105 max_med_exp_tax_exem number,
106 med_exp_tax_exem number,
107 ee_educ_exp number,
108 spouse_educ_exp number,
109 disabled_educ_exp number,
110 dpnt_educ_exp number,
111 educ_exp_tax_exem number,
112 housing_saving_type number,
113 housing_saving number,
114 housing_purchase_date number,
115 housing_loan_date number,
116 housing_loan_repay number,
117 lt_housing_loan_date number,
118 lt_housing_loan_interest_repay number,
119 lt_housing_loan_date_1 number,
120 lt_housing_loan_intr_repay_1 number,
121 max_housing_exp_tax_exem number,
122 housing_exp_tax_exem number,
123 donation1 number,
124 political_donation1 number,
125 political_donation2 number,
126 political_donation3 number,
127 donation1_tax_exem number,
128 donation2 number,
129 donation3 number,
130 max_donation2_tax_exem number,
131 max_donation3_tax_exem number,
132 donation2_tax_exem number,
133 donation3_tax_exem number,
134 donation_tax_exem number,
135 marriage_exemption number,
136 funeral_exemption number,
137 relocation_exemption number,
138 marr_fun_relo_exemption number,
139 sp_tax_exem number,
140 std_sp_tax_exem number,
141 np_prem number,
142 np_prem_tax_exem number,
143 pen_prem number, -- Bug 6024342
144 taxable_income2 number,
145 pers_pension_prem number,
146 pers_pension_prem_tax_exem number,
147 -- Bug 4750653
148 corp_pension_prem number,
149 corp_pension_prem_tax_exem number,
150 pers_pension_saving number,
151 pers_pension_saving_tax_exem number,
152 invest_partner_fin1 number,
153 invest_partner_fin2 number,
154 invest_partner_fin_tax_exem number,
155 small_bus_install number, -- Bug 6895093
156 small_bus_install_exem number, -- Bug 6895093
157 credit_card_exp number,
158 credit_card_exp_tax_exem number,
159 emp_stk_own_contri number,
160 emp_stk_own_contri_tax_exem number,
161 taxation_base number,
162 calc_tax number,
163 basic_tax_break number,
164 housing_loan_interest_repay number,
165 housing_exp_tax_break number,
166 stock_saving number,
167 stock_saving_tax_break number,
168 lt_stock_saving1 number,
169 lt_stock_saving2 number,
170 lt_stock_saving_tax_break number,
171 ovstb_tax_paid_date number,
172 ovstb_territory_code number,
173 ovstb_currency_code number,
174 ovstb_taxable number,
175 ovstb_taxable_subj_tax_break number,
176 ovstb_tax_break_rate number,
177 ovstb_tax_foreign_currency number,
178 ovstb_tax number,
179 ovstb_application_date number,
180 ovstb_submission_date number,
181 ovs_tax_break number,
182 total_tax_break number,
183 fwtb_immigration_purpose number,
184 fwtb_contract_date number,
185 fwtb_expiry_date number,
186 fwtb_application_date number,
187 fwtb_submission_date number,
188 foreign_worker_tax_break1 number,
189 foreign_worker_tax_break2 number,
190 foreign_worker_tax_break number,
191 annual_itax number,
192 annual_rtax number,
193 annual_stax number,
194 prev_itax number,
195 prev_rtax number,
196 prev_stax number,
197 cur_itax number,
198 cur_rtax number,
199 cur_stax number,
200 itax_adj number,
201 rtax_adj number,
202 stax_adj number,
203 -- Bug 3966549
204 don_tax_break2004 number,
205 -- End of 3966549
206 cash_receipt_expense number, -- 4738717
207 -- Bug 6630135
208 tot_med_exp_cards number,
209 med_exp_paid_not_inc_med_exem number,
210 -- End of 6630135
211 double_exem_amt number -- 6716401
212 ) ;
213 --
214 g_user_entity_id t_user_entity_id;
215 ------------------------------------------------------------------------
216 -- PAY_REPORT_FORMAT_ITEMS_F Cache
217 ------------------------------------------------------------------------
218 type t_archive_item is record(
219 report_type pay_report_format_mappings_f.report_type%TYPE,
220 report_qualifier pay_report_format_mappings_f.report_qualifier%TYPE,
221 report_category pay_report_format_mappings_f.report_category%TYPE,
222 effective_date date,
223 archive_item_tbl t_varchar2_tbl); -- user_entity_id indexed PL/SQL table
224 g_archive_item t_archive_item;
225 ------------------------------------------------------------------------
226 -- Global variable for debugging
227 ------------------------------------------------------------------------
228 g_debug constant boolean := hr_utility.debug_enabled;
229 ------------------------------------------------------------------------
230 function convert_to_rec(
231 p_user_entity_id_tbl in t_number_tbl,
232 p_archive_item_value_tbl in t_varchar2_tbl) return t_yea_info
233 ------------------------------------------------------------------------
234 is
235 l_yea_info t_yea_info;
236 l_archive_item_value_tbl t_varchar2_tbl;
237 ----------------------------------------------------------------
238 procedure set_archive_item(
239 p_user_entity_id in number,
240 p_archive_item_value in out nocopy varchar2)
241 ----------------------------------------------------------------
242 is
243 begin
244 if l_archive_item_value_tbl.exists(p_user_entity_id) then
245 p_archive_item_value := l_archive_item_value_tbl(p_user_entity_id);
246 end if;
247 end set_archive_item;
248 ----------------------------------------------------------------
249 procedure set_archive_item(
250 p_user_entity_id in number,
251 p_archive_item_value in out nocopy number)
252 ----------------------------------------------------------------
253 is
254 begin
255 if l_archive_item_value_tbl.exists(p_user_entity_id) then
256 p_archive_item_value := fnd_number.canonical_to_number(l_archive_item_value_tbl(p_user_entity_id)); -- Bug 7149878
257 end if;
258 end set_archive_item;
259 ----------------------------------------------------------------
260 procedure set_archive_item(
261 p_user_entity_id in number,
262 p_archive_item_value in out nocopy date)
263 ----------------------------------------------------------------
264 is
265 begin
266 if l_archive_item_value_tbl.exists(p_user_entity_id) then
267 p_archive_item_value := fnd_date.canonical_to_date(l_archive_item_value_tbl(p_user_entity_id));
268 end if;
269 end set_archive_item;
270 begin
271 ------------------------------------------------------------------------
272 -- Re-construct above PL/SQL table to user_entity_id indexed PL/SQL table.
273 ------------------------------------------------------------------------
274 for i in 1..p_user_entity_id_tbl.count loop
275 l_archive_item_value_tbl(p_user_entity_id_tbl(i)) := p_archive_item_value_tbl(i);
276 end loop;
277 ------------------------------------------------------------------------
278 -- Convert user_entity_id indexed PL/SQL table to PL/SQL record
279 ------------------------------------------------------------------------
280 set_archive_item(g_user_entity_id.non_resident_flag, l_yea_info.non_resident_flag);
281 set_archive_item(g_user_entity_id.foreign_residency_flag, l_yea_info.foreign_residency_flag); -- Bug 6615356
282 set_archive_item(g_user_entity_id.fw_fixed_tax_rate, l_yea_info.fixed_tax_rate); -- 3546993
283 set_archive_item(g_user_entity_id.cur_taxable_mth, l_yea_info.cur_taxable_mth);
284 set_archive_item(g_user_entity_id.cur_taxable_bon, l_yea_info.cur_taxable_bon);
285 set_archive_item(g_user_entity_id.cur_sp_irreg_bonus, l_yea_info.cur_sp_irreg_bonus);
286 set_archive_item(g_user_entity_id.cur_taxable, l_yea_info.cur_taxable);
287 set_archive_item(g_user_entity_id.prev_taxable_mth, l_yea_info.prev_taxable_mth);
288 set_archive_item(g_user_entity_id.prev_taxable_bon, l_yea_info.prev_taxable_bon);
289 set_archive_item(g_user_entity_id.prev_sp_irreg_bonus, l_yea_info.prev_sp_irreg_bonus);
290 --Bug 6024342
291 set_archive_item(g_user_entity_id.prev_stck_pur_opt_exec_earn, l_yea_info.prev_stck_pur_opt_exec_earn);
292 --
293 -- bug 6012258
294 set_archive_item(g_user_entity_id.cur_stck_pur_opt_exec_earn, l_yea_info.cur_stck_pur_opt_exec_earn);
295 set_archive_item(g_user_entity_id.research_payment, l_yea_info.research_payment);
296 --
297 -- Bug 6024342
298 set_archive_item(g_user_entity_id.stck_pur_opt_exec_earn, l_yea_info.stck_pur_opt_exec_earn);
299 --
300 set_archive_item(g_user_entity_id.prev_taxable, l_yea_info.prev_taxable);
301 set_archive_item(g_user_entity_id.taxable_mth, l_yea_info.taxable_mth);
302 set_archive_item(g_user_entity_id.taxable_bon, l_yea_info.taxable_bon);
303 set_archive_item(g_user_entity_id.sp_irreg_bonus, l_yea_info.sp_irreg_bonus);
304 set_archive_item(g_user_entity_id.taxable, l_yea_info.taxable);
305 set_archive_item(g_user_entity_id.non_taxable_ovs, l_yea_info.non_taxable_ovs);
306 set_archive_item(g_user_entity_id.non_taxable_ovt, l_yea_info.non_taxable_ovt);
307 set_archive_item(g_user_entity_id.non_taxable_oth, l_yea_info.non_taxable_oth);
308 set_archive_item(g_user_entity_id.non_taxable, l_yea_info.non_taxable);
309 set_archive_item(g_user_entity_id.foreign_worker_income_exem, l_yea_info.foreign_worker_income_exem); -- 3546993
310 set_archive_item(g_user_entity_id.basic_income_exem, l_yea_info.basic_income_exem);
311 set_archive_item(g_user_entity_id.taxable_income, l_yea_info.taxable_income);
312 set_archive_item(g_user_entity_id.ee_tax_exem, l_yea_info.ee_tax_exem);
313 set_archive_item(g_user_entity_id.dpnt_spouse_flag, l_yea_info.dpnt_spouse_flag);
314 set_archive_item(g_user_entity_id.dpnt_spouse_tax_exem, l_yea_info.dpnt_spouse_tax_exem);
315 set_archive_item(g_user_entity_id.num_of_aged_dpnts, l_yea_info.num_of_aged_dpnts);
316 set_archive_item(g_user_entity_id.num_of_adult_dpnts, l_yea_info.num_of_adult_dpnts);
317 set_archive_item(g_user_entity_id.num_of_underaged_dpnts, l_yea_info.num_of_underaged_dpnts);
318 set_archive_item(g_user_entity_id.num_of_dpnts, l_yea_info.num_of_dpnts);
319 set_archive_item(g_user_entity_id.dpnt_tax_exem, l_yea_info.dpnt_tax_exem);
320 set_archive_item(g_user_entity_id.num_of_ageds, l_yea_info.num_of_ageds);
321 -- Bug 3172960
322 set_archive_item(g_user_entity_id.num_of_super_ageds, l_yea_info.num_of_super_ageds);
323 -- Bug 6705170
324 set_archive_item(g_user_entity_id.num_of_new_born_adopted, l_yea_info.num_of_new_born_adopted);
325 set_archive_item(g_user_entity_id.new_born_adopted_tax_exem, l_yea_info.new_born_adopted_tax_exem);
326 --
327 -- Bug 6784288
328 set_archive_item(g_user_entity_id.num_of_addtl_child, l_yea_info.num_of_addtl_child);
329 --
330 set_archive_item(g_user_entity_id.aged_tax_exem, l_yea_info.aged_tax_exem);
331 set_archive_item(g_user_entity_id.num_of_disableds, l_yea_info.num_of_disableds);
332 set_archive_item(g_user_entity_id.disabled_tax_exem, l_yea_info.disabled_tax_exem);
333 set_archive_item(g_user_entity_id.female_ee_flag, l_yea_info.female_ee_flag);
334 set_archive_item(g_user_entity_id.female_ee_tax_exem, l_yea_info.female_ee_tax_exem);
335 set_archive_item(g_user_entity_id.num_of_children, l_yea_info.num_of_children);
336 set_archive_item(g_user_entity_id.child_tax_exem, l_yea_info.child_tax_exem);
337 -- Bug 5756690
338 set_archive_item(g_user_entity_id.addl_child_tax_exem, l_yea_info.addl_child_tax_exem);
339 set_archive_item(g_user_entity_id.supp_tax_exem, l_yea_info.supp_tax_exem);
340 set_archive_item(g_user_entity_id.hi_prem, l_yea_info.hi_prem);
341 set_archive_item(g_user_entity_id.hi_prem_tax_exem, l_yea_info.hi_prem_tax_exem);
342 -- Bug 7164589
343 set_archive_item(g_user_entity_id.long_term_ins_prem, l_yea_info.long_term_ins_prem);
344 set_archive_item(g_user_entity_id.long_term_ins_prem_tax_exem, l_yea_info.long_term_ins_prem_tax_exem);
345 -- End of Bug 7164589
346 set_archive_item(g_user_entity_id.ei_prem, l_yea_info.ei_prem);
347 set_archive_item(g_user_entity_id.ei_prem_tax_exem, l_yea_info.ei_prem_tax_exem);
348 set_archive_item(g_user_entity_id.pers_ins_name, l_yea_info.pers_ins_name);
349 set_archive_item(g_user_entity_id.pers_ins_prem, l_yea_info.pers_ins_prem);
350 set_archive_item(g_user_entity_id.pers_ins_prem_tax_exem, l_yea_info.pers_ins_prem_tax_exem);
351 set_archive_item(g_user_entity_id.disabled_ins_prem, l_yea_info.disabled_ins_prem);
352 set_archive_item(g_user_entity_id.disabled_ins_prem_tax_exem, l_yea_info.disabled_ins_prem_tax_exem);
353 set_archive_item(g_user_entity_id.ins_prem_tax_exem, l_yea_info.ins_prem_tax_exem);
354 set_archive_item(g_user_entity_id.med_exp, l_yea_info.med_exp);
355 set_archive_item(g_user_entity_id.med_exp_disabled, l_yea_info.med_exp_disabled);
356 set_archive_item(g_user_entity_id.med_exp_aged, l_yea_info.med_exp_aged);
357 -- Bug 3172960
358 set_archive_item(g_user_entity_id.med_exp_emp, l_yea_info.med_exp_emp);
359 set_archive_item(g_user_entity_id.max_med_exp_tax_exem, l_yea_info.max_med_exp_tax_exem);
360 set_archive_item(g_user_entity_id.med_exp_tax_exem, l_yea_info.med_exp_tax_exem);
361 set_archive_item(g_user_entity_id.ee_educ_exp, l_yea_info.ee_educ_exp);
362 set_archive_item(g_user_entity_id.spouse_educ_exp, l_yea_info.spouse_educ_exp);
363 set_archive_item(g_user_entity_id.disabled_educ_exp, l_yea_info.disabled_educ_exp);
364 set_archive_item(g_user_entity_id.dpnt_educ_exp, l_yea_info.dpnt_educ_exp);
365 set_archive_item(g_user_entity_id.educ_exp_tax_exem, l_yea_info.educ_exp_tax_exem);
366 set_archive_item(g_user_entity_id.housing_saving_type, l_yea_info.housing_saving_type);
367 set_archive_item(g_user_entity_id.housing_saving, l_yea_info.housing_saving);
368 set_archive_item(g_user_entity_id.housing_purchase_date, l_yea_info.housing_purchase_date);
369 set_archive_item(g_user_entity_id.housing_loan_date, l_yea_info.housing_loan_date);
370 set_archive_item(g_user_entity_id.housing_loan_repay, l_yea_info.housing_loan_repay);
371 set_archive_item(g_user_entity_id.lt_housing_loan_date, l_yea_info.lt_housing_loan_date);
372 set_archive_item(g_user_entity_id.lt_housing_loan_interest_repay, l_yea_info.lt_housing_loan_interest_repay);
373 set_archive_item(g_user_entity_id.max_housing_exp_tax_exem, l_yea_info.max_housing_exp_tax_exem);
374 set_archive_item(g_user_entity_id.housing_exp_tax_exem, l_yea_info.housing_exp_tax_exem);
375 set_archive_item(g_user_entity_id.donation1, l_yea_info.donation1);
376 set_archive_item(g_user_entity_id.political_donation1, l_yea_info.political_donation1);
377 set_archive_item(g_user_entity_id.political_donation2, l_yea_info.political_donation2);
378 set_archive_item(g_user_entity_id.political_donation3, l_yea_info.political_donation3);
379 set_archive_item(g_user_entity_id.donation1_tax_exem, l_yea_info.donation1_tax_exem);
380 set_archive_item(g_user_entity_id.donation2, l_yea_info.donation2);
381 set_archive_item(g_user_entity_id.donation3, l_yea_info.donation3);
382 set_archive_item(g_user_entity_id.max_donation2_tax_exem, l_yea_info.max_donation2_tax_exem);
383 set_archive_item(g_user_entity_id.max_donation3_tax_exem, l_yea_info.max_donation3_tax_exem);
384 set_archive_item(g_user_entity_id.donation2_tax_exem, l_yea_info.donation2_tax_exem);
385 set_archive_item(g_user_entity_id.donation3_tax_exem, l_yea_info.donation3_tax_exem);
386 set_archive_item(g_user_entity_id.donation_tax_exem, l_yea_info.donation_tax_exem);
387 -- Bug 3966549
388 set_archive_item(g_user_entity_id.don_tax_break2004, l_yea_info.don_tax_break2004) ;
389 -- End of 3966549
390 set_archive_item(g_user_entity_id.marriage_exemption, l_yea_info.marriage_exemption);
391 set_archive_item(g_user_entity_id.funeral_exemption, l_yea_info.funeral_exemption);
392 set_archive_item(g_user_entity_id.relocation_exemption, l_yea_info.relocation_exemption);
393 set_archive_item(g_user_entity_id.marr_fun_relo_exemption, l_yea_info.marr_fun_relo_exemption);
394 set_archive_item(g_user_entity_id.sp_tax_exem, l_yea_info.sp_tax_exem);
395 set_archive_item(g_user_entity_id.std_sp_tax_exem, l_yea_info.std_sp_tax_exem);
396 set_archive_item(g_user_entity_id.np_prem, l_yea_info.np_prem);
397 set_archive_item(g_user_entity_id.np_prem_tax_exem, l_yea_info.np_prem_tax_exem);
398 -- Bug 6024342
399 set_archive_item(g_user_entity_id.pen_prem, l_yea_info.pen_prem);
400 --
401 set_archive_item(g_user_entity_id.taxable_income2, l_yea_info.taxable_income2);
402 set_archive_item(g_user_entity_id.pers_pension_prem, l_yea_info.pers_pension_prem);
403 set_archive_item(g_user_entity_id.pers_pension_prem_tax_exem, l_yea_info.pers_pension_prem_tax_exem);
404 -- Bug 4750653
405 set_archive_item(g_user_entity_id.corp_pension_prem, l_yea_info.corp_pension_prem);
406 set_archive_item(g_user_entity_id.corp_pension_prem_tax_exem, l_yea_info.corp_pension_prem_tax_exem);
407 -- End of Bug 4750653
408 set_archive_item(g_user_entity_id.pers_pension_saving, l_yea_info.pers_pension_saving);
409 set_archive_item(g_user_entity_id.pers_pension_saving_tax_exem, l_yea_info.pers_pension_saving_tax_exem);
410 set_archive_item(g_user_entity_id.invest_partner_fin1, l_yea_info.invest_partner_fin1);
411 set_archive_item(g_user_entity_id.invest_partner_fin2, l_yea_info.invest_partner_fin2);
412 set_archive_item(g_user_entity_id.invest_partner_fin_tax_exem, l_yea_info.invest_partner_fin_tax_exem);
413 -- Bug 6895093
414 set_archive_item(g_user_entity_id.small_bus_install, l_yea_info.small_bus_install);
415 set_archive_item(g_user_entity_id.small_bus_install_exem, l_yea_info.small_bus_install_exem);
416 -- End of Bug 6895093
417 set_archive_item(g_user_entity_id.credit_card_exp, l_yea_info.credit_card_exp);
418 set_archive_item(g_user_entity_id.credit_card_exp_tax_exem, l_yea_info.credit_card_exp_tax_exem);
419 set_archive_item(g_user_entity_id.emp_stk_own_contri, l_yea_info.emp_stk_own_contri);
420 set_archive_item(g_user_entity_id.emp_stk_own_contri_tax_exem, l_yea_info.emp_stk_own_contri_tax_exem);
421 set_archive_item(g_user_entity_id.taxation_base, l_yea_info.taxation_base);
422 set_archive_item(g_user_entity_id.calc_tax, l_yea_info.calc_tax);
423 set_archive_item(g_user_entity_id.basic_tax_break, l_yea_info.basic_tax_break);
424 set_archive_item(g_user_entity_id.housing_loan_interest_repay, l_yea_info.housing_loan_interest_repay);
425 set_archive_item(g_user_entity_id.housing_exp_tax_break, l_yea_info.housing_exp_tax_break);
426 set_archive_item(g_user_entity_id.stock_saving, l_yea_info.stock_saving);
427 set_archive_item(g_user_entity_id.stock_saving_tax_break, l_yea_info.stock_saving_tax_break);
428 set_archive_item(g_user_entity_id.lt_stock_saving1, l_yea_info.lt_stock_saving1);
429 set_archive_item(g_user_entity_id.lt_stock_saving2, l_yea_info.lt_stock_saving2);
430 set_archive_item(g_user_entity_id.lt_stock_saving_tax_break, l_yea_info.lt_stock_saving_tax_break);
431 set_archive_item(g_user_entity_id.ovstb_tax_paid_date, l_yea_info.ovstb_tax_paid_date);
432 set_archive_item(g_user_entity_id.ovstb_territory_code, l_yea_info.ovstb_territory_code);
433 set_archive_item(g_user_entity_id.ovstb_currency_code, l_yea_info.ovstb_currency_code);
434 set_archive_item(g_user_entity_id.ovstb_taxable, l_yea_info.ovstb_taxable);
435 set_archive_item(g_user_entity_id.ovstb_taxable_subj_tax_break, l_yea_info.ovstb_taxable_subj_tax_break);
436 set_archive_item(g_user_entity_id.ovstb_tax_break_rate, l_yea_info.ovstb_tax_break_rate);
437 set_archive_item(g_user_entity_id.ovstb_tax_foreign_currency, l_yea_info.ovstb_tax_foreign_currency);
438 set_archive_item(g_user_entity_id.ovstb_tax, l_yea_info.ovstb_tax);
439 set_archive_item(g_user_entity_id.ovstb_application_date, l_yea_info.ovstb_application_date);
440 set_archive_item(g_user_entity_id.ovstb_submission_date, l_yea_info.ovstb_submission_date);
441 set_archive_item(g_user_entity_id.ovs_tax_break, l_yea_info.ovs_tax_break);
442 set_archive_item(g_user_entity_id.total_tax_break, l_yea_info.total_tax_break);
443 set_archive_item(g_user_entity_id.fwtb_immigration_purpose, l_yea_info.fwtb_immigration_purpose);
444 set_archive_item(g_user_entity_id.fwtb_contract_date, l_yea_info.fwtb_contract_date);
445 set_archive_item(g_user_entity_id.fwtb_expiry_date, l_yea_info.fwtb_expiry_date);
446 set_archive_item(g_user_entity_id.fwtb_application_date, l_yea_info.fwtb_application_date);
447 set_archive_item(g_user_entity_id.fwtb_submission_date, l_yea_info.fwtb_submission_date);
448 set_archive_item(g_user_entity_id.foreign_worker_tax_break1, l_yea_info.foreign_worker_tax_break1);
449 set_archive_item(g_user_entity_id.foreign_worker_tax_break2, l_yea_info.foreign_worker_tax_break2);
450 set_archive_item(g_user_entity_id.foreign_worker_tax_break, l_yea_info.foreign_worker_tax_break);
451 set_archive_item(g_user_entity_id.annual_itax, l_yea_info.annual_itax);
452 set_archive_item(g_user_entity_id.annual_rtax, l_yea_info.annual_rtax);
453 set_archive_item(g_user_entity_id.annual_stax, l_yea_info.annual_stax);
454 set_archive_item(g_user_entity_id.prev_itax, l_yea_info.prev_itax);
455 set_archive_item(g_user_entity_id.prev_rtax, l_yea_info.prev_rtax);
456 set_archive_item(g_user_entity_id.prev_stax, l_yea_info.prev_stax);
457 set_archive_item(g_user_entity_id.cur_itax, l_yea_info.cur_itax);
458 set_archive_item(g_user_entity_id.cur_rtax, l_yea_info.cur_rtax);
459 set_archive_item(g_user_entity_id.cur_stax, l_yea_info.cur_stax);
460 set_archive_item(g_user_entity_id.itax_adj, l_yea_info.itax_adj);
461 set_archive_item(g_user_entity_id.rtax_adj, l_yea_info.rtax_adj);
462 set_archive_item(g_user_entity_id.stax_adj, l_yea_info.stax_adj);
463 --
464 -- Bug 6630135
465 set_archive_item(g_user_entity_id.tot_med_exp_cards,l_yea_info.tot_med_exp_cards);
466 set_archive_item(g_user_entity_id.med_exp_paid_not_inc_med_exem,l_yea_info.med_exp_paid_not_inc_med_exem);
467 -- End of Bug 6630135
468 -- Bug 6716401
469 set_archive_item(g_user_entity_id.double_exem_amt,l_yea_info.double_exem_amt);
470 -- End of Bug 6716401
471
472
473 return l_yea_info;
474 end convert_to_rec;
475 ------------------------------------------------------------------------
476 procedure convert_to_tbl(
477 p_report_type in varchar2,
478 p_report_qualifier in varchar2,
479 p_report_category in varchar2,
480 p_effective_date in date,
481 p_yea_info in t_yea_info,
482 p_user_entity_id_tbl out nocopy t_number_tbl,
483 p_archive_item_value_tbl out nocopy t_varchar2_tbl)
484 ------------------------------------------------------------------------
485 is
486 l_user_entity_id_tbl t_number_tbl;
487 ----------------------------------------------------------------
488 procedure set_archive_item(
489 p_user_entity_id in number,
490 p_archive_item_value in varchar2)
491 ----------------------------------------------------------------
492 is
493 l_index number;
494 begin
495 --------------------------------------------------------
496 -- If ARCHIVE_ITEM is available in current report_type
497 --------------------------------------------------------
498 if g_archive_item.archive_item_tbl.exists(p_user_entity_id) then
499 l_index := p_user_entity_id_tbl.count + 1;
500 p_user_entity_id_tbl(l_index) := p_user_entity_id;
501 p_archive_item_value_tbl(l_index) := p_archive_item_value;
502 end if;
503 end set_archive_item;
504 ----------------------------------------------------------------
505 procedure set_archive_item(
506 p_user_entity_id in number,
507 p_archive_item_value in number)
508 ----------------------------------------------------------------
509 is
510 begin
511 set_archive_item(p_user_entity_id, to_char(p_archive_item_value));
512 end set_archive_item;
513 ----------------------------------------------------------------
514 procedure set_archive_item(
515 p_user_entity_id in number,
516 p_archive_item_value in date)
517 ----------------------------------------------------------------
518 is
519 begin
520 set_archive_item(p_user_entity_id, fnd_date.date_to_canonical(p_archive_item_value));
521 end set_archive_item;
522 begin
523 if p_report_type = g_archive_item.report_type
524 and p_report_qualifier = g_archive_item.report_qualifier
525 and p_report_category = g_archive_item.report_category
526 and p_effective_date = g_archive_item.effective_date then
527 null;
528 else
529 ------------------------------------------------------------------------
530 -- Refresh PAY_REPORT_FORMAT_ITEMS_F cache information
531 ------------------------------------------------------------------------
532 select user_entity_id
533 bulk collect into
534 l_user_entity_id_tbl
535 from pay_report_format_items_f
536 where report_type = p_report_type
537 and report_qualifier = p_report_qualifier
538 and report_category = p_report_category
539 and p_effective_date
540 between effective_start_date and effective_end_date;
541 -----------------------------------------------------------------------
542 g_archive_item.report_type := p_report_type;
543 g_archive_item.report_qualifier := p_report_qualifier;
544 g_archive_item.report_category := p_report_category;
545 g_archive_item.effective_date := p_effective_date;
546 ------------------------------------------------------------------------
547 -- Re-construct above PL/SQL table to user_entity_id indexed PL/SQL table.
548 ------------------------------------------------------------------------
549 g_archive_item.archive_item_tbl.delete;
550 for i in 1..l_user_entity_id_tbl.count loop
551 g_archive_item.archive_item_tbl(l_user_entity_id_tbl(i)) := null;
552 end loop;
553 end if;
554 --
555 set_archive_item(g_user_entity_id.non_resident_flag, p_yea_info.non_resident_flag);
556 set_archive_item(g_user_entity_id.foreign_residency_flag, p_yea_info.foreign_residency_flag); -- Bug 6615356
557 set_archive_item(g_user_entity_id.fw_fixed_tax_rate, p_yea_info.fixed_tax_rate); -- 3546993
558 set_archive_item(g_user_entity_id.cur_taxable_mth, p_yea_info.cur_taxable_mth);
559 set_archive_item(g_user_entity_id.cur_taxable_bon, p_yea_info.cur_taxable_bon);
560 set_archive_item(g_user_entity_id.cur_sp_irreg_bonus, p_yea_info.cur_sp_irreg_bonus);
561 set_archive_item(g_user_entity_id.cur_taxable, p_yea_info.cur_taxable);
562 --
563 if p_yea_info.prev_termination_date_tbl.count > 0 then
564 set_archive_item(g_user_entity_id.prev_taxable_mth, p_yea_info.prev_taxable_mth);
565 set_archive_item(g_user_entity_id.prev_taxable_bon, p_yea_info.prev_taxable_bon);
566 set_archive_item(g_user_entity_id.prev_sp_irreg_bonus, p_yea_info.prev_sp_irreg_bonus);
567 --Bug 6024342
568 set_archive_item(g_user_entity_id.prev_stck_pur_opt_exec_earn, p_yea_info.prev_stck_pur_opt_exec_earn);
569 --
570 set_archive_item(g_user_entity_id.prev_taxable, p_yea_info.prev_taxable);
571 set_archive_item(g_user_entity_id.prev_itax, p_yea_info.prev_itax);
572 set_archive_item(g_user_entity_id.prev_rtax, p_yea_info.prev_rtax);
573 set_archive_item(g_user_entity_id.prev_stax, p_yea_info.prev_stax);
574 end if;
575 --
576 -- bug 6012258
577 set_archive_item(g_user_entity_id.cur_stck_pur_opt_exec_earn, p_yea_info.cur_stck_pur_opt_exec_earn);
578 set_archive_item(g_user_entity_id.research_payment, p_yea_info.research_payment);
579 --
580 set_archive_item(g_user_entity_id.taxable_mth, p_yea_info.taxable_mth);
581 set_archive_item(g_user_entity_id.taxable_bon, p_yea_info.taxable_bon);
582 set_archive_item(g_user_entity_id.sp_irreg_bonus, p_yea_info.sp_irreg_bonus);
583 -- Bug 6024342
584 set_archive_item(g_user_entity_id.stck_pur_opt_exec_earn, p_yea_info.stck_pur_opt_exec_earn);
585 --
586 set_archive_item(g_user_entity_id.taxable, p_yea_info.taxable);
587 set_archive_item(g_user_entity_id.non_taxable_ovs, p_yea_info.non_taxable_ovs);
588 set_archive_item(g_user_entity_id.non_taxable_ovt, p_yea_info.non_taxable_ovt);
589 set_archive_item(g_user_entity_id.non_taxable_oth, p_yea_info.non_taxable_oth);
590 set_archive_item(g_user_entity_id.non_taxable, p_yea_info.non_taxable);
591 set_archive_item(g_user_entity_id.foreign_worker_income_exem, p_yea_info.foreign_worker_income_exem); --3546993
592 set_archive_item(g_user_entity_id.basic_income_exem, p_yea_info.basic_income_exem);
593 set_archive_item(g_user_entity_id.taxable_income, p_yea_info.taxable_income);
594 set_archive_item(g_user_entity_id.ee_tax_exem, p_yea_info.ee_tax_exem);
595 set_archive_item(g_user_entity_id.dpnt_spouse_flag, p_yea_info.dpnt_spouse_flag);
596 set_archive_item(g_user_entity_id.dpnt_spouse_tax_exem, p_yea_info.dpnt_spouse_tax_exem);
597 set_archive_item(g_user_entity_id.num_of_aged_dpnts, p_yea_info.num_of_aged_dpnts);
598 set_archive_item(g_user_entity_id.num_of_adult_dpnts, p_yea_info.num_of_adult_dpnts);
599 set_archive_item(g_user_entity_id.num_of_underaged_dpnts, p_yea_info.num_of_underaged_dpnts);
600 set_archive_item(g_user_entity_id.num_of_dpnts, p_yea_info.num_of_dpnts);
601 set_archive_item(g_user_entity_id.dpnt_tax_exem, p_yea_info.dpnt_tax_exem);
602 set_archive_item(g_user_entity_id.num_of_ageds, p_yea_info.num_of_ageds);
603 -- Bug 3172960
604 set_archive_item(g_user_entity_id.num_of_super_ageds, p_yea_info.num_of_super_ageds);
605 -- Bug 6705170
606 set_archive_item(g_user_entity_id.num_of_new_born_adopted, p_yea_info.num_of_new_born_adopted);
607 set_archive_item(g_user_entity_id.new_born_adopted_tax_exem, p_yea_info.new_born_adopted_tax_exem);
608 --
609 -- Bug 6784288
610 set_archive_item(g_user_entity_id.num_of_addtl_child, p_yea_info.num_of_addtl_child);
611 --
612 set_archive_item(g_user_entity_id.aged_tax_exem, p_yea_info.aged_tax_exem);
613 set_archive_item(g_user_entity_id.num_of_disableds, p_yea_info.num_of_disableds);
614 set_archive_item(g_user_entity_id.disabled_tax_exem, p_yea_info.disabled_tax_exem);
615 set_archive_item(g_user_entity_id.female_ee_flag, p_yea_info.female_ee_flag);
616 set_archive_item(g_user_entity_id.female_ee_tax_exem, p_yea_info.female_ee_tax_exem);
617 set_archive_item(g_user_entity_id.num_of_children, p_yea_info.num_of_children);
618 set_archive_item(g_user_entity_id.child_tax_exem, p_yea_info.child_tax_exem);
619 -- Bug 5756690
620 set_archive_item(g_user_entity_id.addl_child_tax_exem, p_yea_info.addl_child_tax_exem);
621 set_archive_item(g_user_entity_id.supp_tax_exem, p_yea_info.supp_tax_exem);
622 set_archive_item(g_user_entity_id.hi_prem, p_yea_info.hi_prem);
623 set_archive_item(g_user_entity_id.hi_prem_tax_exem, p_yea_info.hi_prem_tax_exem);
624 -- Bug 7164589
625 set_archive_item(g_user_entity_id.long_term_ins_prem, p_yea_info.long_term_ins_prem);
626 set_archive_item(g_user_entity_id.long_term_ins_prem_tax_exem, p_yea_info.long_term_ins_prem_tax_exem);
627 -- End of Bug 7164589
628 set_archive_item(g_user_entity_id.ei_prem, p_yea_info.ei_prem);
629 set_archive_item(g_user_entity_id.ei_prem_tax_exem, p_yea_info.ei_prem_tax_exem);
630 set_archive_item(g_user_entity_id.pers_ins_name, p_yea_info.pers_ins_name);
631 set_archive_item(g_user_entity_id.pers_ins_prem, p_yea_info.pers_ins_prem);
632 set_archive_item(g_user_entity_id.pers_ins_prem_tax_exem, p_yea_info.pers_ins_prem_tax_exem);
633 set_archive_item(g_user_entity_id.disabled_ins_prem, p_yea_info.disabled_ins_prem);
634 set_archive_item(g_user_entity_id.disabled_ins_prem_tax_exem, p_yea_info.disabled_ins_prem_tax_exem);
635 set_archive_item(g_user_entity_id.ins_prem_tax_exem, p_yea_info.ins_prem_tax_exem);
636 set_archive_item(g_user_entity_id.med_exp, p_yea_info.med_exp);
637 set_archive_item(g_user_entity_id.med_exp_disabled, p_yea_info.med_exp_disabled);
638 set_archive_item(g_user_entity_id.med_exp_aged, p_yea_info.med_exp_aged);
639 -- Bug 3172960
640 set_archive_item(g_user_entity_id.med_exp_emp, p_yea_info.med_exp_emp);
641 set_archive_item(g_user_entity_id.max_med_exp_tax_exem, p_yea_info.max_med_exp_tax_exem);
642 set_archive_item(g_user_entity_id.med_exp_tax_exem, p_yea_info.med_exp_tax_exem);
643 set_archive_item(g_user_entity_id.ee_educ_exp, p_yea_info.ee_educ_exp);
644 set_archive_item(g_user_entity_id.spouse_educ_exp, p_yea_info.spouse_educ_exp);
645 set_archive_item(g_user_entity_id.disabled_educ_exp, p_yea_info.disabled_educ_exp);
646 set_archive_item(g_user_entity_id.dpnt_educ_exp, p_yea_info.dpnt_educ_exp);
647 set_archive_item(g_user_entity_id.educ_exp_tax_exem, p_yea_info.educ_exp_tax_exem);
648 set_archive_item(g_user_entity_id.housing_saving_type, p_yea_info.housing_saving_type);
649 set_archive_item(g_user_entity_id.housing_saving, p_yea_info.housing_saving);
650 set_archive_item(g_user_entity_id.housing_purchase_date, p_yea_info.housing_purchase_date);
651 set_archive_item(g_user_entity_id.housing_loan_date, p_yea_info.housing_loan_date);
652 set_archive_item(g_user_entity_id.housing_loan_repay, p_yea_info.housing_loan_repay);
653 set_archive_item(g_user_entity_id.lt_housing_loan_date, p_yea_info.lt_housing_loan_date);
654 set_archive_item(g_user_entity_id.lt_housing_loan_interest_repay, p_yea_info.lt_housing_loan_interest_repay);
655 set_archive_item(g_user_entity_id.lt_housing_loan_date_1, p_yea_info.lt_housing_loan_date_1);
656 set_archive_item(g_user_entity_id.lt_housing_loan_intr_repay_1, p_yea_info.lt_housing_loan_intr_repay_1);
657 set_archive_item(g_user_entity_id.max_housing_exp_tax_exem, p_yea_info.max_housing_exp_tax_exem);
658 set_archive_item(g_user_entity_id.housing_exp_tax_exem, p_yea_info.housing_exp_tax_exem);
659 set_archive_item(g_user_entity_id.donation1, p_yea_info.donation1);
660 set_archive_item(g_user_entity_id.political_donation1, p_yea_info.political_donation1);
661 set_archive_item(g_user_entity_id.political_donation2, p_yea_info.political_donation2);
662 set_archive_item(g_user_entity_id.political_donation3, p_yea_info.political_donation3);
663 set_archive_item(g_user_entity_id.donation1_tax_exem, p_yea_info.donation1_tax_exem);
664 set_archive_item(g_user_entity_id.donation2, p_yea_info.donation2);
665 set_archive_item(g_user_entity_id.donation3, p_yea_info.donation3);
666 set_archive_item(g_user_entity_id.max_donation2_tax_exem, p_yea_info.max_donation2_tax_exem);
667 set_archive_item(g_user_entity_id.max_donation3_tax_exem, p_yea_info.max_donation3_tax_exem);
668 set_archive_item(g_user_entity_id.donation2_tax_exem, p_yea_info.donation2_tax_exem);
669 set_archive_item(g_user_entity_id.donation3_tax_exem, p_yea_info.donation3_tax_exem);
670 set_archive_item(g_user_entity_id.donation_tax_exem, p_yea_info.donation_tax_exem);
671 -- Bug 3966549
672 set_archive_item(g_user_entity_id.don_tax_break2004, p_yea_info.don_tax_break2004) ;
673 -- End of 3966549
674 set_archive_item(g_user_entity_id.marriage_exemption, p_yea_info.marriage_exemption);
675 set_archive_item(g_user_entity_id.funeral_exemption, p_yea_info.funeral_exemption);
676 set_archive_item(g_user_entity_id.relocation_exemption, p_yea_info.relocation_exemption);
677 set_archive_item(g_user_entity_id.marr_fun_relo_exemption, p_yea_info.marr_fun_relo_exemption);
678 set_archive_item(g_user_entity_id.sp_tax_exem, p_yea_info.sp_tax_exem);
679 set_archive_item(g_user_entity_id.std_sp_tax_exem, p_yea_info.std_sp_tax_exem);
680 set_archive_item(g_user_entity_id.np_prem, p_yea_info.np_prem);
681 set_archive_item(g_user_entity_id.np_prem_tax_exem, p_yea_info.np_prem_tax_exem);
682 -- Bug 6024342
683 set_archive_item(g_user_entity_id.pen_prem, p_yea_info.pen_prem);
684 --
685 set_archive_item(g_user_entity_id.taxable_income2, p_yea_info.taxable_income2);
686 set_archive_item(g_user_entity_id.pers_pension_prem, p_yea_info.pers_pension_prem);
687 set_archive_item(g_user_entity_id.pers_pension_prem_tax_exem, p_yea_info.pers_pension_prem_tax_exem);
688 -- Bug 4750653
689 set_archive_item(g_user_entity_id.corp_pension_prem, p_yea_info.corp_pension_prem);
690 set_archive_item(g_user_entity_id.corp_pension_prem_tax_exem, p_yea_info.corp_pension_prem_tax_exem);
691 --End of Bug 4750653
692 set_archive_item(g_user_entity_id.pers_pension_saving, p_yea_info.pers_pension_saving);
693 set_archive_item(g_user_entity_id.pers_pension_saving_tax_exem, p_yea_info.pers_pension_saving_tax_exem);
694 set_archive_item(g_user_entity_id.invest_partner_fin1, p_yea_info.invest_partner_fin1);
695 set_archive_item(g_user_entity_id.invest_partner_fin2, p_yea_info.invest_partner_fin2);
696 set_archive_item(g_user_entity_id.invest_partner_fin_tax_exem, p_yea_info.invest_partner_fin_tax_exem);
697 -- Bug 6895093
698 set_archive_item(g_user_entity_id.small_bus_install, p_yea_info.small_bus_install);
699 set_archive_item(g_user_entity_id.small_bus_install_exem, p_yea_info.small_bus_install_exem);
700 -- End of Bug 6895093
701 set_archive_item(g_user_entity_id.credit_card_exp, p_yea_info.credit_card_exp);
702 set_archive_item(g_user_entity_id.credit_card_exp_tax_exem, p_yea_info.credit_card_exp_tax_exem);
703 --
704 -- Bug 3201332
705 -- Bug 3374792 added nvl
706 set_archive_item(g_user_entity_id.emp_stk_own_contri, p_yea_info.emp_stk_own_contri + nvl(p_yea_info.fw_educ_expense,0) + nvl(p_yea_info.fw_house_rent,0));
707 set_archive_item(g_user_entity_id.emp_stk_own_contri_tax_exem, p_yea_info.emp_stk_own_contri_tax_exem);
708 set_archive_item(g_user_entity_id.taxation_base, p_yea_info.taxation_base);
709 set_archive_item(g_user_entity_id.calc_tax, p_yea_info.calc_tax);
710 set_archive_item(g_user_entity_id.basic_tax_break, p_yea_info.basic_tax_break);
711 set_archive_item(g_user_entity_id.housing_loan_interest_repay, p_yea_info.housing_loan_interest_repay);
712 set_archive_item(g_user_entity_id.housing_exp_tax_break, p_yea_info.housing_exp_tax_break);
713 set_archive_item(g_user_entity_id.stock_saving, p_yea_info.stock_saving);
714 set_archive_item(g_user_entity_id.stock_saving_tax_break, p_yea_info.stock_saving_tax_break);
715 set_archive_item(g_user_entity_id.lt_stock_saving1, p_yea_info.lt_stock_saving1);
716 set_archive_item(g_user_entity_id.lt_stock_saving2, p_yea_info.lt_stock_saving2);
717 set_archive_item(g_user_entity_id.lt_stock_saving_tax_break, p_yea_info.lt_stock_saving_tax_break);
718 --
719 if p_yea_info.ovstb_tax_paid_date is not null then
720 set_archive_item(g_user_entity_id.ovstb_tax_paid_date, p_yea_info.ovstb_tax_paid_date);
721 set_archive_item(g_user_entity_id.ovstb_territory_code, p_yea_info.ovstb_territory_code);
722 set_archive_item(g_user_entity_id.ovstb_currency_code, p_yea_info.ovstb_currency_code);
723 set_archive_item(g_user_entity_id.ovstb_taxable, p_yea_info.ovstb_taxable);
724 set_archive_item(g_user_entity_id.ovstb_taxable_subj_tax_break, p_yea_info.ovstb_taxable_subj_tax_break);
725 set_archive_item(g_user_entity_id.ovstb_tax_break_rate, p_yea_info.ovstb_tax_break_rate);
726 set_archive_item(g_user_entity_id.ovstb_tax_foreign_currency, p_yea_info.ovstb_tax_foreign_currency);
727 set_archive_item(g_user_entity_id.ovstb_tax, p_yea_info.ovstb_tax);
728 set_archive_item(g_user_entity_id.ovstb_application_date, p_yea_info.ovstb_application_date);
729 set_archive_item(g_user_entity_id.ovstb_submission_date, p_yea_info.ovstb_submission_date);
730 set_archive_item(g_user_entity_id.ovs_tax_break, p_yea_info.ovs_tax_break);
731 end if;
732 --
733 set_archive_item(g_user_entity_id.total_tax_break, p_yea_info.total_tax_break);
734 --
735 if p_yea_info.fwtb_immigration_purpose is not null then
736 set_archive_item(g_user_entity_id.fwtb_immigration_purpose, p_yea_info.fwtb_immigration_purpose);
737 set_archive_item(g_user_entity_id.fwtb_contract_date, p_yea_info.fwtb_contract_date);
738 set_archive_item(g_user_entity_id.fwtb_expiry_date, p_yea_info.fwtb_expiry_date);
739 set_archive_item(g_user_entity_id.fwtb_application_date, p_yea_info.fwtb_application_date);
740 set_archive_item(g_user_entity_id.fwtb_submission_date, p_yea_info.fwtb_submission_date);
741 set_archive_item(g_user_entity_id.foreign_worker_tax_break1, p_yea_info.foreign_worker_tax_break1);
742 set_archive_item(g_user_entity_id.foreign_worker_tax_break2, p_yea_info.foreign_worker_tax_break2);
743 set_archive_item(g_user_entity_id.foreign_worker_tax_break, p_yea_info.foreign_worker_tax_break);
744 end if;
745 --
746 set_archive_item(g_user_entity_id.annual_itax, p_yea_info.annual_itax);
747 set_archive_item(g_user_entity_id.annual_rtax, p_yea_info.annual_rtax);
748 set_archive_item(g_user_entity_id.annual_stax, p_yea_info.annual_stax);
749 set_archive_item(g_user_entity_id.cur_itax, p_yea_info.cur_itax);
750 set_archive_item(g_user_entity_id.cur_rtax, p_yea_info.cur_rtax);
751 set_archive_item(g_user_entity_id.cur_stax, p_yea_info.cur_stax);
752 set_archive_item(g_user_entity_id.itax_adj, p_yea_info.itax_adj);
753 set_archive_item(g_user_entity_id.rtax_adj, p_yea_info.rtax_adj);
754 set_archive_item(g_user_entity_id.stax_adj, p_yea_info.stax_adj);
755 -- 4738717
756 set_archive_item(g_user_entity_id.cash_receipt_expense, p_yea_info.cash_receipt_exp2005);
757 -- Bug 6630135
758 set_archive_item(g_user_entity_id.tot_med_exp_cards,p_yea_info.tot_med_exp_cards);
759 set_archive_item(g_user_entity_id.med_exp_paid_not_inc_med_exem,p_yea_info.med_exp_paid_not_inc_med_exem);
760 -- End of Bug 6630135
761 -- Bug 6716401
762 set_archive_item(g_user_entity_id.double_exem_amt,p_yea_info.double_exem_amt);
763 -- End of Bug 6716401
764
765 end convert_to_tbl;
766 ------------------------------------------------------------------------
767 procedure prev_er_info(
768 p_assignment_id in number,
769 p_effective_date in date,
770 p_termination_date_tbl out nocopy t_date_tbl,
771 p_corp_name_tbl out nocopy t_varchar2_tbl,
772 p_bp_number_tbl out nocopy t_varchar2_tbl,
773 p_taxable_mth_tbl out nocopy t_number_tbl,
774 p_taxable_bon_tbl out nocopy t_number_tbl,
775 p_sp_irreg_bonus_tbl out nocopy t_number_tbl,
776 p_stck_pur_opt_exec_earn_tbl out nocopy t_number_tbl, -- Bug 6024342
777 p_non_taxable_ovs_tbl out nocopy t_number_tbl,
778 p_non_taxable_ovt_tbl out nocopy t_number_tbl,
779 p_non_taxable_oth_tbl out nocopy t_number_tbl,
780 p_hi_prem_tbl out nocopy t_number_tbl,
781 p_ltci_prem_tbl out nocopy t_number_tbl, -- Bug 7260606
782 p_ei_prem_tbl out nocopy t_number_tbl,
783 p_np_prem_tbl out nocopy t_number_tbl,
784 p_pen_prem_tbl out nocopy t_number_tbl, -- Bug 6024342
785 p_itax_tbl out nocopy t_number_tbl,
786 p_rtax_tbl out nocopy t_number_tbl,
787 p_stax_tbl out nocopy t_number_tbl)
788 ------------------------------------------------------------------------
789 is
790 cursor csr_aei is
791 select fnd_date.canonical_to_date(aei_information1),
792 aei_information2,
793 hr_ni_chk_pkg.chk_nat_id_format(aei_information3, 'DDD-DD-DDDDD'),
794 nvl(to_number(aei_information4), 0),
795 nvl(to_number(aei_information5), 0),
796 nvl(to_number(aei_information6), 0),
797 nvl(to_number(aei_information7), 0),
798 nvl(to_number(aei_information8), 0),
799 nvl(to_number(aei_information9), 0),
800 nvl(to_number(aei_information10), 0),
801 nvl(to_number(aei_information18), 0), -- Bug 7260606
802 nvl(to_number(aei_information11), 0),
803 nvl(to_number(aei_information12), 0),
804 nvl(to_number(aei_information13), 0),
805 nvl(to_number(aei_information14), 0),
806 nvl(to_number(aei_information15), 0),
807 nvl(to_number(aei_information16), 0), -- Bug 6024342
808 nvl(to_number(aei_information17), 0) -- Bug 6024342
809 from per_assignment_extra_info
810 where assignment_id = p_assignment_id
811 and information_type = 'KR_YEA_PREV_ER_INFO'
812 and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY')
813 order by 1;
814 begin
815 open csr_aei;
816 fetch csr_aei bulk collect into
817 p_termination_date_tbl,
818 p_corp_name_tbl,
819 p_bp_number_tbl,
820 p_taxable_mth_tbl,
821 p_taxable_bon_tbl,
822 p_sp_irreg_bonus_tbl,
823 p_non_taxable_ovs_tbl,
824 p_non_taxable_ovt_tbl,
825 p_non_taxable_oth_tbl,
826 p_hi_prem_tbl,
827 p_ltci_prem_tbl, -- Bug 7260606
828 p_ei_prem_tbl,
829 p_np_prem_tbl,
830 p_itax_tbl,
831 p_rtax_tbl,
832 p_stax_tbl,
833 p_pen_prem_tbl, -- Bug 6024342
834 p_stck_pur_opt_exec_earn_tbl; -- Bug 6024342
835 close csr_aei;
836 end prev_er_info;
837 ------------------------------------------------------------------------
838 procedure sp_tax_exem_info(
839 p_assignment_id in number,
840 p_effective_date in date,
841 p_hi_prem in out nocopy number,
842 p_ltci_prem in out nocopy number, -- Bug 7260606
843 p_ei_prem in out nocopy number,
844 p_pers_ins_name in out nocopy varchar2,
845 p_pers_ins_prem in out nocopy number,
846 p_disabled_ins_prem in out nocopy number,
847 p_med_exp in out nocopy number,
848 p_med_exp_card_emp in out nocopy number, -- Bug 4704848
849 p_med_exp_disabled in out nocopy number,
850 p_med_exp_aged in out nocopy number,
851 p_ee_educ_exp in out nocopy number,
852 p_ee_occupation_educ_exp2005 in out nocopy number, -- Bug 3971542
853 p_housing_purchase_date in out nocopy date,
854 p_housing_loan_date in out nocopy date,
855 p_housing_loan_repay in out nocopy number,
856 p_lt_housing_loan_date in out nocopy date,
857 p_lt_housing_loan_intr_repay in out nocopy number,
858 p_lt_housing_loan_date_1 in out nocopy date,
859 p_lt_housing_loan_intr_repay_1 in out nocopy number,
860 p_donation1 in out nocopy number,
861 p_political_donation1 in out nocopy number,
862 p_political_donation2 in out nocopy number,
863 p_political_donation3 in out nocopy number,
864 p_donation2 in out nocopy number,
865 p_donation3 in out nocopy number,
866 -- Bug 3966549
867 p_esoa_don2004 in out nocopy number,
868 -- End of 3966549
869 p_marriage_exemption in out nocopy varchar2,
870 p_funeral_exemption in out nocopy varchar2,
871 p_relocation_exemption in out nocopy varchar2,
872 -- Bug 3172960
873 p_med_exp_emp in out nocopy number)
874 ------------------------------------------------------------------------
875 is
876 l_promotional_fund_donation NUMBER := 0 ;
877 l_religious_donation NUMBER := 0 ;
878 -- Bug 5255234
879 l_dpnt_pers_ins_nts NUMBER := 0 ;
880 l_dpnt_pers_ins_oth NUMBER := 0 ;
881 -- Bug 5667762
882 l_dpnt_dis_ins_nts NUMBER := 0 ;
883 l_dpnt_dis_ins_oth NUMBER := 0 ;
884 l_chk_box_med_tot_det VARCHAR2(1); -- Bug 6737106
885 l_med_det_emp_tot NUMBER := 0; -- Bug 6737106
886 l_med_det_dis_tot NUMBER := 0; -- Bug 6737106
887 l_med_det_aged_tot NUMBER := 0; -- Bug 6737106
888 l_med_det_dep_tot NUMBER := 0; -- Bug 6737106
889
890 cursor csr_aei is
891 select nvl(to_number(aei_information2), 0),
892 nvl(to_number(aei_information3), 0),
893 aei_information4,
894 nvl(to_number(aei_information5), 0),
895 nvl(to_number(aei_information6), 0),
896 nvl(to_number(aei_information7), 0),
897 nvl(to_number(aei_information8), 0),
898 nvl(to_number(aei_information9), 0),
899 nvl(to_number(aei_information10), 0),
900 fnd_date.canonical_to_date(aei_information13),
901 fnd_date.canonical_to_date(aei_information14),
902 nvl(to_number(aei_information15), 0),
903 fnd_date.canonical_to_date(aei_information16),
904 nvl(to_number(aei_information17), 0),
905 fnd_date.canonical_to_date(aei_information25),
906 nvl(to_number(aei_information26), 0),
907 nvl(to_number(aei_information18), 0),
908 nvl(to_number(aei_information19), 0),
909 nvl(to_number(aei_information20), 0),
910 nvl(to_number(aei_information21), 0),
911 nvl(to_number(aei_information22), 0),
912 nvl(to_number(aei_information23), 0),
913 -- Bug 3966549
914 nvl(to_number(aei_information30), 0), -- ESOA Donation
915 -- End of 3966549
916 aei_information27,
917 aei_information28,
918 aei_information29,
919 -- Bug 3172960
920 nvl(to_number(aei_information24), 0)
921 from per_assignment_extra_info
922 where assignment_id = p_assignment_id
923 and information_type = 'KR_YEA_SP_TAX_EXEM_INFO'
924 and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY');
925
926 cursor csr_aei2 is
927 select nvl(to_number(aei_information2), 0) -- Bug 3971542 Employee's occupational training (educational) expense
928 ,nvl(to_number(aei_information3), 0) -- Bug 4704848
929 ,nvl(to_number(aei_information4), 0)
930 ,nvl(to_number(aei_information5), 0)
931 ,nvl(aei_information10,'N') -- Bug 6737106 Check Box Medical Total and Details
932 ,nvl(to_number(aei_information11), 0) -- Bug 7260606
933 from per_assignment_extra_info
934 where assignment_id = p_assignment_id
935 and information_type = 'KR_YEA_SP_TAX_EXEM_INFO2'
936 and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY');
937
938 --
939 -- Bug 5255234 Cursor to get the dependent Insurnace Expenses.
940 --
941 -- Bug 5879106
942 cursor csr_get_dpnt_insurance_prem is
943 select sum(cei_information1),
944 sum(cei_information2),
945 sum(cei_information10),
946 sum(cei_information11)
947 from pay_kr_cont_details_v pkc,
948 per_contact_extra_info_f cei
949 where assignment_id = p_assignment_id
950 -- Bug 5879106
951 and cei.information_type(+) = 'KR_DPNT_EXPENSE_INFO'
952 and cei.contact_relationship_id(+) = pkc.contact_relationship_id
953 and to_char(cei.effective_start_date(+), 'YYYY') = to_char(p_effective_date, 'YYYY')
954 --
955 and p_effective_date between emp_start_date and emp_end_date
956 and p_effective_date between cont_start_date and cont_end_date
957 and p_effective_date between nvl(ADDRESS_START_DATE,p_effective_date) and nvl(ADDRESS_END_DATE, p_effective_date)
958 and p_effective_date between nvl(pkc.date_start, p_effective_date)
959 and decode(pkc.cont_information9, 'D', trunc(add_months(nvl(pkc.date_end, p_effective_date),12),'YYYY')-1, nvl(pkc.date_end, p_effective_date) )
960 and pay_kr_ff_functions_pkg.is_exempted_dependent( pkc.contact_type,
961 pkc.national_identifier,
962 pkc.cont_information2,
963 pkc.cont_information3,
964 pkc.cont_information4,
965 pkc.cont_information7,
966 pkc.cont_information8,
967 p_effective_date,
968 pkc.cont_information10,
969 pkc.cont_information12,
970 pkc.cont_information13,
971 pkc.cont_information14,
972 cei.contact_extra_info_id
973 ) = 'Y';
974
975 /* Bug 6737106 Added cursor csr_aei3 to get Detailed Information from 'KR_YEA_DETAIL_MEDICAL_EXP_INFO' */
976 cursor csr_aei3 is
977 select nvl(sum(decode(aei_information9,'A',0,'B',0,decode(aei_information7,0,(nvl(aei_information3,0) + nvl(aei_information11,0))))),0) -- Employee Total
978 ,nvl(sum(decode(aei_information9,'A',(nvl(aei_information3,0) + nvl(aei_information11,0)))),0) -- Disabled Total
979 ,nvl(sum(decode(aei_information9,'B',(nvl(aei_information3,0) + nvl(aei_information11,0)))),0) -- Aged Total
980 ,nvl(sum(decode(aei_information9,'A',0,'B',0,decode(aei_information7,0,0,(nvl(aei_information3,0) + nvl(aei_information11,0))))),0) -- Dependent Total
981 from per_assignment_extra_info
982 where assignment_id = p_assignment_id
983 and information_type = 'KR_YEA_DETAIL_MEDICAL_EXP_INFO'
984 and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY');
985 begin
986 open csr_aei;
987 fetch csr_aei into
988 p_hi_prem,
989 p_ei_prem,
990 p_pers_ins_name,
991 p_pers_ins_prem,
992 p_disabled_ins_prem,
993 p_med_exp,
994 p_med_exp_disabled,
995 p_med_exp_aged,
996 p_ee_educ_exp,
997 p_housing_purchase_date,
998 p_housing_loan_date,
999 p_housing_loan_repay,
1000 p_lt_housing_loan_date,
1001 p_lt_housing_loan_intr_repay,
1002 p_lt_housing_loan_date_1,
1003 p_lt_housing_loan_intr_repay_1,
1004 p_donation1,
1005 p_political_donation1,
1006 p_political_donation2,
1007 p_political_donation3,
1008 p_donation2,
1009 p_donation3,
1010 -- Bug 3966549
1011 p_esoa_don2004,
1012 -- End of 3966549
1013 p_marriage_exemption,
1014 p_funeral_exemption,
1015 p_relocation_exemption,
1016 -- Bug 3172960
1017 p_med_exp_emp;
1018 close csr_aei;
1019
1020 open csr_aei2 ;
1021 fetch csr_aei2 into
1022 p_ee_occupation_educ_exp2005 -- Bug 3971542
1023 ,p_med_exp_card_emp
1024 ,l_promotional_fund_donation
1025 ,l_religious_donation
1026 ,l_chk_box_med_tot_det -- Bug 6737106
1027 ,p_ltci_prem; -- Bug 7260606
1028 close csr_aei2 ;
1029
1030 /* Bug 6737106 Added cursor csr_aei3 to get Detailed Information from 'KR_YEA_DETAIL_MEDICAL_EXP_INFO' */
1031 if l_chk_box_med_tot_det ='Y' then
1032 open csr_aei3;
1033 fetch csr_aei3
1034 into l_med_det_emp_tot,
1035 l_med_det_dis_tot,
1036 l_med_det_aged_tot,
1037 l_med_det_dep_tot ;
1038 close csr_aei3;
1039
1040 p_med_exp_emp := p_med_exp_emp + l_med_det_emp_tot;
1041 p_med_exp_disabled := p_med_exp_disabled + l_med_det_dis_tot;
1042 p_med_exp_aged := p_med_exp_aged + l_med_det_aged_tot;
1043 p_med_exp := p_med_exp + l_med_det_dep_tot;
1044 end if;
1045 /*End Bug 6737106 */
1046 -- Bug 5255234
1047 open csr_get_dpnt_insurance_prem;
1048 fetch csr_get_dpnt_insurance_prem into l_dpnt_pers_ins_nts,l_dpnt_pers_ins_oth
1049 ,l_dpnt_dis_ins_nts,l_dpnt_dis_ins_oth ;
1050 close csr_get_dpnt_insurance_prem;
1051
1052 -- Bug 5667762
1053 -- Dependent Insurance Expense amounts are now classified as Personal Insurance
1054 -- and Disabled Insurance. Therefore they are no more added to Health Insurance Premium.
1055
1056 -- p_hi_prem := p_hi_prem + nvl(l_dpnt_ins_nts,0) + nvl(l_dpnt_ins_oth,0);
1057
1058 p_pers_ins_prem := p_pers_ins_prem + nvl(l_dpnt_pers_ins_nts,0) + nvl(l_dpnt_pers_ins_oth,0);
1059 p_disabled_ins_prem := p_disabled_ins_prem + nvl(l_dpnt_dis_ins_nts,0) + nvl(l_dpnt_dis_ins_oth,0);
1060
1061 ------------------------------------------------------------------------
1062 -- Bug : 4776711
1063 -- Since Promotional fund donation receives 100% exeption,it is added to the
1064 -- Statutory donation. Religious donation receives 10% exemption,it is
1065 -- added with Specified donation.
1066 ------------------------------------------------------------------------
1067
1068 p_donation1 := nvl(p_donation1,0) + nvl(l_promotional_fund_donation,0);
1069 p_donation2 := nvl(p_donation2,0) + nvl(l_religious_donation,0);
1070 end sp_tax_exem_info;
1071 ------------------------------------------------------------------------
1072 procedure dpnt_educ_tax_exem_info(
1073 p_assignment_id in number,
1074 p_effective_date in date,
1075 p_contact_type_tbl out nocopy t_varchar2_tbl,
1076 p_school_type_tbl out nocopy t_varchar2_tbl,
1077 p_exp_tbl out nocopy t_number_tbl)
1078 ------------------------------------------------------------------------
1079 is
1080 cursor csr_aei is
1081 select aei_information2,
1082 aei_information3,
1083 nvl(to_number(aei_information4), 0)
1084 from per_assignment_extra_info
1085 where assignment_id = p_assignment_id
1086 and information_type = 'KR_YEA_DPNT_EDUC_TAX_EXEM_INFO'
1087 and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY');
1088 begin
1089 open csr_aei;
1090 fetch csr_aei bulk collect into
1091 p_contact_type_tbl,
1092 p_school_type_tbl,
1093 p_exp_tbl;
1094 close csr_aei;
1095 end dpnt_educ_tax_exem_info;
1096 ------------------------------------------------------------------------
1097 /* Changes for Bug 3201332 */
1098 ------------------------------------------------------------------------
1099 procedure fw_tax_exem_info(
1100 p_assignment_id in number,
1101 p_effective_date in date,
1102 p_educ_expense out nocopy number,
1103 p_house_rent out nocopy number)
1104 ------------------------------------------------------------------------
1105 is
1106 cursor csr_aei is
1107 select nvl(to_number(aei_information2), 0),
1108 nvl(to_number(aei_information3), 0)
1109 from per_assignment_extra_info
1110 where assignment_id = p_assignment_id
1111 and information_type = 'KR_YEA_FW_TAX_EXEM_INFO'
1112 and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY');
1113 begin
1114 open csr_aei;
1115 fetch csr_aei into
1116 p_educ_expense,
1117 p_house_rent;
1118 close csr_aei;
1119 end fw_tax_exem_info;
1120 ------------------------------------------------------------------------
1121 /* Changes for Bug 2523481 */
1122
1123 procedure hous_exp_tax_exem_info(
1124 p_assignment_id in number,
1125 p_effective_date in date,
1126 p_saving_type_tbl out nocopy t_varchar2_tbl,
1127 p_saving_tbl out nocopy t_number_tbl)
1128 ------------------------------------------------------------------------
1129 is
1130 cursor csr_aei is
1131 select aei_information2,
1132 nvl(to_number(aei_information3), 0)
1133 from per_assignment_extra_info
1134 where assignment_id = p_assignment_id
1135 and information_type = 'KR_YEA_HOU_EXP_TAX_EXEM_INFO'
1136 and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY');
1137 begin
1138 open csr_aei;
1139 fetch csr_aei bulk collect into
1140 p_saving_type_tbl,
1141 p_saving_tbl;
1142 close csr_aei;
1143 end hous_exp_tax_exem_info;
1144 ------------------------------------------------------------------------
1145 procedure tax_exem_info(
1146 p_assignment_id in number,
1147 p_effective_date in date,
1148 p_np_prem in out nocopy number,
1149 p_pen_prem in out nocopy number, -- Bug 6024342
1150 p_pers_pension_prem in out nocopy number,
1151 p_corp_pension_prem in out nocopy number, -- Bug : 4750653
1152 p_pers_pension_saving in out nocopy number,
1153 p_invest_partner_fin1 in out nocopy number,
1154 p_invest_partner_fin2 in out nocopy number,
1155 p_small_bus_install in out nocopy number, -- Bug 6895093
1156 p_credit_card_exp in out nocopy number,
1157 p_direct_card_exp in out nocopy number,
1158 -- Bug 3966549
1159 p_emp_cre_card_direct_exp2004 in out nocopy number,
1160 p_dpnt_cre_card_dir_exp2004 in out nocopy number,
1161 p_giro_tuition_paid_exp2004 in out nocopy number,
1162 -- End of 3966549
1163 -- Bug No 3506168
1164 p_cash_receipt_exp2005 in out nocopy number,
1165 --
1166 p_emp_stk_own_contri in out nocopy number,
1167 --Bug 6630135
1168 p_tot_med_exp_cards in out nocopy number,
1169 p_dpnt_med_exp_cards in out nocopy number
1170 --End of Bug 6630135
1171 )
1172 ------------------------------------------------------------------------
1173 is
1174 -- Bug 5255234
1175 l_dpnt_card_nts NUMBER;
1176 l_dpnt_card_oth NUMBER;
1177 l_dpnt_cash_nts NUMBER;
1178
1179 cursor csr_aei is
1180 select nvl(to_number(aei_information2), 0),
1181 nvl(to_number(aei_information3), 0),
1182 nvl(to_number(aei_information14), 0), -- Bug 4750653
1183 nvl(to_number(aei_information4), 0),
1184 nvl(to_number(aei_information5), 0),
1185 nvl(to_number(aei_information6), 0),
1186 -- Modified for Bug# 2706537
1187 nvl(to_number(aei_information7), 0) + nvl(to_number(aei_information9), 0),
1188 -- Added for fix 2879008
1189 -- Bug 3966549
1190 -- Added second term for Bug 3966549
1191 nvl(to_number(aei_information10),0) + nvl(to_number(aei_information11), 0), -- employee's direct payment + dependents' direct payments
1192 nvl(to_number(aei_information7), 0) + nvl(to_number(aei_information10), 0), -- emp credit card + emp direct card
1193 nvl(to_number(aei_information9), 0) + nvl(to_number(aei_information11), 0), -- dpdnt credit card + dpdnt direct card
1194 nvl(to_number(aei_information12), 0), -- giro tuition paid
1195 -- End of 3966549
1196 -- Bug No 3506168
1197 nvl(to_number(aei_information13), 0), -- Cash Receipt expenses
1198 --
1199 nvl(to_number(aei_information8), 0),
1200 nvl(to_number(aei_information15), 0),
1201 nvl(to_number(aei_information16), 0), -- Total Medical Expense Paid in Cards Bug 6630135
1202 nvl(to_number(aei_information17), 0), -- Medical Expense Paid in Cards For Dependents who are not eligible for Basic Exemption Bug 6630135
1203 nvl(to_number(aei_information20), 0) -- Small Business Installment Amount Bug 6895093
1204 from per_assignment_extra_info
1205 where assignment_id = p_assignment_id
1206 and information_type = 'KR_YEA_TAX_EXEM_INFO'
1207 and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY');
1208
1209 --Bug 5255234
1210 -- Bug 5879106
1211 cursor csr_get_dpnt_card_expense is
1212 select nvl(sum(cei_information7),0), -- Dependent Cards(NTS) Expense
1213 nvl(sum(cei_information8),0), -- Dependent Cards(Other) Expense
1214 nvl(sum(cei_information9),0) -- Dependent Cash(NTS) Expense
1215 from pay_kr_cont_details_v pkc,
1216 per_contact_extra_info_f cei
1217 where assignment_id = p_assignment_id
1218 -- Bug 5879106
1219 and cei.information_type(+) = 'KR_DPNT_EXPENSE_INFO'
1220 and cei.contact_relationship_id(+) = pkc.contact_relationship_id
1221 and to_char(cei.effective_start_date(+), 'YYYY') = to_char(p_effective_date, 'YYYY')
1222 --
1223 and p_effective_date between emp_start_date and emp_end_date
1224 and p_effective_date between cont_start_date and cont_end_date
1225 and p_effective_date between nvl(ADDRESS_START_DATE,p_effective_date) and nvl(ADDRESS_END_DATE, p_effective_date)
1226 and p_effective_date between nvl(pkc.date_start, p_effective_date)
1227 and decode(pkc.cont_information9, 'D', trunc(add_months(nvl(pkc.date_end, p_effective_date),12),'YYYY')-1, nvl(pkc.date_end, p_effective_date) )
1228 and pay_kr_ff_functions_pkg.is_exempted_dependent( pkc.contact_type,
1229 pkc.national_identifier,
1230 pkc.cont_information2,
1231 pkc.cont_information3,
1232 pkc.cont_information4,
1233 pkc.cont_information7,
1234 pkc.cont_information8,
1235 p_effective_date,
1236 pkc.cont_information10,
1237 pkc.cont_information12,
1238 pkc.cont_information13,
1239 pkc.cont_information14,
1240 cei.contact_extra_info_id
1241 ) = 'Y';
1242 begin
1243 open csr_aei;
1244 fetch csr_aei into
1245 p_np_prem,
1246 p_pers_pension_prem,
1247 p_corp_pension_prem, -- Bug : 4750653
1248 p_pers_pension_saving,
1249 p_invest_partner_fin1,
1250 p_invest_partner_fin2,
1251 p_credit_card_exp,
1252 p_direct_card_exp,
1253 -- Bug 3966549
1254 p_emp_cre_card_direct_exp2004,
1255 p_dpnt_cre_card_dir_exp2004,
1256 p_giro_tuition_paid_exp2004,
1257 -- End of bug 3966549
1258 -- Bug No 3506168
1259 p_cash_receipt_exp2005,
1260 --
1261 p_emp_stk_own_contri,
1262 p_pen_prem, -- Bug 6024342
1263 p_tot_med_exp_cards, -- Bug 6630135
1264 p_dpnt_med_exp_cards, -- Bug 6630135
1265 p_small_bus_install; -- Bug 6895093
1266 close csr_aei;
1267
1268 -- Bug 5255234
1269 -- Get Dependent Information from Contact Extra Information
1270 -- and consolidate with the corresponding Employee Expenses
1271 open csr_get_dpnt_card_expense;
1272 fetch csr_get_dpnt_card_expense into l_dpnt_card_nts,
1273 l_dpnt_card_oth,
1274 l_dpnt_cash_nts;
1275 close csr_get_dpnt_card_expense;
1276 --
1277 p_dpnt_cre_card_dir_exp2004 := p_dpnt_cre_card_dir_exp2004 + nvl(l_dpnt_card_nts,0) + nvl(l_dpnt_card_oth,0);
1278 p_cash_receipt_exp2005 := p_cash_receipt_exp2005 + nvl(l_dpnt_cash_nts,0);
1279
1280 end tax_exem_info;
1281 ------------------------------------------------------------------------
1282 procedure tax_break_info(
1283 p_assignment_id in number,
1284 p_effective_date in date,
1285 p_housing_loan_interest_repay in out nocopy number,
1286 p_stock_saving in out nocopy number,
1287 p_lt_stock_saving1 in out nocopy number,
1288 p_lt_stock_saving2 in out nocopy number)
1289 ------------------------------------------------------------------------
1290 is
1291 cursor csr_aei is
1292 select nvl(to_number(aei_information2), 0),
1293 nvl(to_number(aei_information3), 0),
1294 nvl(to_number(aei_information4), 0),
1295 nvl(to_number(aei_information5), 0)
1296 from per_assignment_extra_info
1297 where assignment_id = p_assignment_id
1298 and information_type = 'KR_YEA_TAX_BREAK_INFO'
1299 and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY');
1300 begin
1301 open csr_aei;
1302 fetch csr_aei into
1303 p_housing_loan_interest_repay,
1304 p_stock_saving,
1305 p_lt_stock_saving1,
1306 p_lt_stock_saving2;
1307 close csr_aei;
1308 end tax_break_info;
1309 ------------------------------------------------------------------------
1310 procedure ovs_tax_break_info(
1311 p_assignment_id in number,
1312 p_effective_date in date,
1313 p_tax_paid_date in out nocopy date,
1314 p_territory_code in out nocopy varchar2,
1315 p_currency_code in out nocopy varchar2,
1316 p_taxable in out nocopy number,
1317 p_taxable_subj_tax_break in out nocopy number,
1318 p_tax_break_rate in out nocopy number,
1319 p_tax_foreign_currency in out nocopy number,
1320 p_tax in out nocopy number,
1321 p_application_date in out nocopy date,
1322 p_submission_date in out nocopy date)
1323 ------------------------------------------------------------------------
1324 is
1325 cursor csr_aei is
1326 select fnd_date.canonical_to_date(aei_information1),
1327 aei_information2,
1328 aei_information3,
1329 nvl(to_number(aei_information4), 0),
1330 nvl(to_number(aei_information5), 0),
1331 nvl(to_number(aei_information6), 0),
1332 nvl(to_number(aei_information7), 0),
1333 nvl(to_number(aei_information8), 0),
1334 fnd_date.canonical_to_date(aei_information9),
1335 fnd_date.canonical_to_date(aei_information10)
1336 from per_assignment_extra_info
1337 where assignment_id = p_assignment_id
1338 and information_type = 'KR_YEA_OVS_TAX_BREAK_INFO'
1339 and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY');
1340 begin
1341 open csr_aei;
1342 fetch csr_aei into
1343 p_tax_paid_date,
1344 p_territory_code,
1345 p_currency_code,
1346 p_taxable,
1347 p_taxable_subj_tax_break,
1348 p_tax_break_rate,
1349 p_tax_foreign_currency,
1350 p_tax,
1351 p_application_date,
1352 p_submission_date;
1353 close csr_aei;
1354 end ovs_tax_break_info;
1355 ------------------------------------------------------------------------
1356 -- Bug 5083240: Updated to calculate earnings during contract, new
1357 -- parameters p_assignment_action_id, p_contr_taxable_earn,
1358 -- p_contr_non_taxable_earn
1359 ------------------------------------------------------------------------
1360 procedure fw_tax_break_info(
1361 p_assignment_action_id in number,
1362 p_assignment_id in number,
1363 p_effective_date in date,
1364 p_immigration_purpose in out nocopy varchar2,
1365 p_contract_date in out nocopy date,
1366 p_expiry_date in out nocopy date,
1367 p_application_date in out nocopy date,
1368 p_submission_date in out nocopy date,
1369 p_contr_taxable_earn in out nocopy number,
1370 p_contr_non_taxable_earn in out nocopy number)
1371 ------------------------------------------------------------------------
1372 is
1373 cursor csr_aei is
1374 select aei_information1,
1375 fnd_date.canonical_to_date(aei_information2),
1376 fnd_date.canonical_to_date(aei_information3),
1377 fnd_date.canonical_to_date(aei_information5),
1378 fnd_date.canonical_to_date(aei_information6)
1379 from per_assignment_extra_info
1380 where assignment_id = p_assignment_id
1381 and information_type = 'KR_YEA_FW_TAX_BREAK_INFO'
1382 and p_effective_date between -- Bug 5083240: Consider end date to be last day of contract_expiry_date year
1383 fnd_date.canonical_to_date(aei_information2)
1384 and (add_months(trunc(fnd_date.canonical_to_date(aei_information3), 'YYYY'), 12) - 1) ;
1385 --
1386 --
1387 cursor csr_fw_contract_earn(
1388 p_contr_start_date date,
1389 p_contr_expiry_date date
1390 ) is
1391 select BAL_TYPE.balance_name,
1392 nvl(sum(fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale), 0)
1393 from pay_balance_feeds_f FEED,
1394 pay_balance_types BAL_TYPE,
1395 pay_run_result_values TARGET,
1396 pay_run_results RR,
1397 pay_payroll_actions PACT,
1398 pay_assignment_actions ASSACT,
1399 pay_payroll_actions BACT,
1400 pay_assignment_actions BAL_ASSACT,
1401 pay_run_types_f RTYPE
1402 where BAL_ASSACT.assignment_action_id = p_assignment_action_id
1403 and BAL_TYPE.balance_name in ('TOTAL_TAXABLE_EARNINGS', 'TOTAL_NON_TAXABLE_EARNINGS')
1404 and BAL_TYPE.legislation_code = 'KR'
1405 and FEED.balance_type_id = BAL_TYPE.balance_type_id
1406 and BACT.payroll_action_id = BAL_ASSACT.payroll_action_id
1407 and ASSACT.assignment_id = BAL_ASSACT.assignment_id
1408 and ASSACT.action_sequence <= BAL_ASSACT.action_sequence
1409 and PACT.payroll_action_id = ASSACT.payroll_action_id
1410 and trunc(PACT.effective_date, 'YYYY') = trunc(BACT.effective_date, 'YYYY')
1411 and PACT.effective_date between p_contr_start_date
1412 and
1413 (add_months(trunc(p_contr_expiry_date, 'MONTH'), 1) - 1 )
1414 and (
1415 RTYPE.run_type_name = 'MTH'
1416 or
1417 RTYPE.run_type_name like 'BON\_%' escape '\'
1418 )
1419 and RTYPE.legislation_code = 'KR'
1420 and PACT.effective_date between RTYPE.effective_start_date
1421 and RTYPE.effective_end_date
1422 and PACT.run_type_id = RTYPE.run_type_id
1423 and RR.assignment_action_id = ASSACT.assignment_action_id
1424 and RR.status in ('P', 'PA')
1425 and TARGET.run_result_id = RR.run_result_id
1426 and nvl(TARGET.result_value, '0') <> '0'
1427 and FEED.input_value_id = TARGET.input_value_id
1428 and PACT.effective_date between FEED.effective_start_date
1429 and
1430 FEED.effective_end_date
1431 group by
1432 BAL_TYPE.balance_name ;
1433 --
1434 l_bal_name pay_balance_types.balance_name%type ;
1435 l_bal_value number ;
1436 --
1437 begin
1438 --
1439 p_contr_taxable_earn := 0 ;
1440 p_contr_non_taxable_earn := 0 ;
1441 p_immigration_purpose := null ;
1442 --
1443 open csr_aei;
1444 fetch csr_aei into
1445 p_immigration_purpose,
1446 p_contract_date,
1447 p_expiry_date,
1448 p_application_date,
1449 p_submission_date;
1450 close csr_aei;
1451 --
1452 if p_immigration_purpose is not null then
1453
1454 l_bal_name := 'DUMMY' ;
1455 l_bal_value := 0 ;
1456
1457 open csr_fw_contract_earn(
1458 p_contr_start_date => p_contract_date,
1459 p_contr_expiry_date => p_expiry_date
1460 ) ;
1461 --
1462 loop
1463
1464 fetch csr_fw_contract_earn into l_bal_name, l_bal_value ;
1465 exit when csr_fw_contract_earn%notfound ;
1466
1467 if l_bal_name = 'TOTAL_TAXABLE_EARNINGS' then
1468 p_contr_taxable_earn := l_bal_value ;
1469 elsif l_bal_name = 'TOTAL_NON_TAXABLE_EARNINGS' then
1470 p_contr_non_taxable_earn := l_bal_value ;
1471 end if ;
1472 l_bal_name := 'DUMMY' ;
1473 l_bal_value := 0 ;
1474 end loop ;
1475 --
1476 close csr_fw_contract_earn ;
1477 --
1478 end if ;
1479 --
1480 end fw_tax_break_info;
1481 ------------------------------------------------------------------------
1482 procedure yea_info(
1483 p_assignment_id in number,
1484 p_assignment_action_id in number,
1485 p_effective_date in date,
1486 p_business_group_id in number,
1487 p_payroll_id in number,
1488 p_yea_info out nocopy t_yea_info,
1489 p_taxable_earnings_warning out nocopy boolean,
1490 p_taxable_income_warning out nocopy boolean,
1491 p_taxation_base_warning out nocopy boolean,
1492 p_calc_tax_warning out nocopy boolean,
1493 p_itax_warning out nocopy boolean,
1494 p_rtax_warning out nocopy boolean,
1495 -- Bug 2878937
1496 p_tax_adj_warning out nocopy boolean)
1497 ------------------------------------------------------------------------
1498 is
1499 l_dummy number;
1500 l_index number;
1501 l_assignment_action_id number;
1502 l_cur_sp_irreg_bonus_mth number := 0;
1503 l_cur_sp_irreg_bonus_bon number := 0;
1504 l_cur_taxable_mth number := 0;
1505 l_cur_taxable_bon number := 0;
1506 l_hi_prem number := 0;
1507 l_ltci_prem number := 0; -- Bug 7260606
1508 l_ei_prem number := 0;
1509 l_np_prem number := 0;
1510 l_pen_prem number := 0; -- Bug 6024342
1511 -- Bug 4750653
1512 l_corp_pension_prem_bal_value number := 0;
1513 -- Bug 3201332
1514 l_ni varchar2(14);
1515 l_stock_pur_opt_exec_earn_mth number := 0;
1516 l_stock_pur_opt_exec_earn_bon number := 0;
1517 l_research_payment_mth number := 0;
1518 l_research_payment_bon number := 0;
1519
1520 cursor csr_assact is
1521 select
1522 paa.assignment_action_id
1523 from pay_payroll_actions ppa,
1524 pay_assignment_actions paa
1525 where paa.assignment_id = p_assignment_id
1526 and paa.source_action_id is null
1527 and ppa.business_group_id = p_business_group_id
1528 and ppa.payroll_id = p_payroll_id
1529 and ppa.payroll_action_id = paa.payroll_action_id
1530 and ppa.action_type in ('B', 'I', 'V', 'R', 'Q')
1531 and paa.assignment_action_id <> p_assignment_action_id
1532 and ppa.effective_date between trunc(p_effective_date, 'YYYY') and p_effective_date
1533 order by paa.action_sequence desc;
1534
1535 l_input_value_id_tbl t_number_tbl;
1536 l_screen_entry_value_tbl t_varchar2_tbl;
1537
1538 cursor csr_ee(p_element_type_id number) is
1539 select
1540 peev.input_value_id,
1541 peev.screen_entry_value
1542 from pay_element_entry_values_f peev,
1543 pay_element_entries_f pee,
1544 pay_element_links_f pel
1545 where pel.element_type_id = p_element_type_id
1546 and p_effective_date
1547 between pel.effective_start_date and pel.effective_end_date
1548 and pee.element_link_id = pel.element_link_id
1549 and pee.assignment_id = p_assignment_id
1550 and nvl(pee.entry_type, 'E') = 'E'
1551 and p_effective_date
1552 between pee.effective_start_date and pee.effective_end_date
1553 and peev.element_entry_id = pee.element_entry_id
1554 and peev.effective_start_date = pee.effective_start_date
1555 and peev.effective_end_date = pee.effective_end_date;
1556 -- Bug 3172960
1557 Cursor csr_fixed_tax_rate
1558 IS
1559 Select aei_information1
1560 From per_assignment_extra_info
1561 Where assignment_id = p_assignment_id
1562 And information_type = 'KR_YEA_FOREIGN_WORKER_TAX';
1563 begin
1564 --
1565 if g_debug then
1566 hr_utility.set_location('pay_kr_yea_pkg.yea_info.',10);
1567 end if;
1568 --
1569 ------------------------------------------------------------------------
1570 -- Derive Resident/Non-resident Information from element entry.
1571 ------------------------------------------------------------------------
1572 open csr_ee(g_tax.element_type_id);
1573 fetch csr_ee bulk collect into l_input_value_id_tbl, l_screen_entry_value_tbl;
1574 close csr_ee;
1575 p_yea_info.non_resident_flag := nvl(l_screen_entry_value_tbl(1), 'N');
1576
1577 /* Bug 6716506 */
1578 begin
1579
1580 p_yea_info.foreign_residency_flag := nvl(l_screen_entry_value_tbl(2), 'N');
1581
1582 exception
1583 when no_data_found then
1584 p_yea_info.foreign_residency_flag := 'N';
1585 end;
1586 /* End of Bug 6716506 */
1587 --
1588 if g_debug then
1589 hr_utility.set_location('pay_kr_yea_pkg.yea_info.',15);
1590 end if;
1591 ------------------------------------------------------------------------
1592 -- Bug 3201332 Derive Nationality from national identifier
1593 -- Bug 3172960 Calling pay_kr_ff_functions_pkg for nationality
1594 -- Bug 6615356 Modified logic for Korean/Foreigner
1595 ------------------------------------------------------------------------
1596 if (pay_kr_ff_functions_pkg.ni_nationality(p_assignment_id, p_effective_date) = 'F' or p_yea_info.foreign_residency_flag = 'Y') then
1597 p_yea_info.nationality := 'F';
1598 else
1599 p_yea_info.nationality := 'K';
1600 end if;
1601 --
1602 if p_yea_info.nationality = 'F' then
1603
1604 OPEN csr_fixed_tax_rate;
1605 FETCH csr_fixed_tax_rate INTO p_yea_info.fixed_tax_rate;
1606
1607 if csr_fixed_tax_rate%NOTFOUND then
1608 p_yea_info.fixed_tax_rate := 'N';
1609 end if;
1610
1611 CLOSE csr_fixed_tax_rate;
1612 end if;
1613 --
1614 if g_debug then
1615 hr_utility.set_location('pay_kr_yea_pkg.yea_info.',20);
1616 end if;
1617 --
1618 ------------------------------------------------------------------------
1619 -- Get latest assignment action in this calendar year.
1620 ------------------------------------------------------------------------
1621 open csr_assact;
1622 fetch csr_assact into l_assignment_action_id;
1623 if csr_assact%FOUND then
1624 --
1625 if g_debug then
1626 hr_utility.trace('l_assignment_action_id '||to_char(l_assignment_action_id));
1627 end if;
1628 --
1629 ------------------------------------------------------------------------
1630 -- Current Employer's Information
1631 -- Modified for Batch balance retrival, fix 3039649
1632 ------------------------------------------------------------------------
1633 for i in 1..g_balance_value_tab.count loop
1634 g_balance_value_tab(i).balance_value := null;
1635 end loop;
1636 --
1637 pay_balance_pkg.get_value ( p_assignment_action_id => l_assignment_action_id
1638 ,p_defined_balance_lst => g_balance_value_tab );
1639 --
1640 if g_debug then
1641 hr_utility.set_location('pay_kr_yea_pkg.yea_info.',30);
1642 end if;
1643 --
1644 l_cur_sp_irreg_bonus_mth := g_balance_value_tab(1).balance_value;
1645 l_cur_sp_irreg_bonus_bon := g_balance_value_tab(2).balance_value;
1646 l_cur_taxable_mth := g_balance_value_tab(3).balance_value;
1647 l_cur_taxable_bon := g_balance_value_tab(4).balance_value;
1648 p_yea_info.cur_taxable_mth := l_cur_taxable_mth - l_cur_sp_irreg_bonus_mth;
1649 p_yea_info.cur_taxable_bon := l_cur_taxable_bon - l_cur_sp_irreg_bonus_bon;
1650 p_yea_info.cur_sp_irreg_bonus := l_cur_sp_irreg_bonus_mth + l_cur_sp_irreg_bonus_bon;
1651 p_yea_info.non_taxable_ovs := g_balance_value_tab(7).balance_value
1652 + g_balance_value_tab(8).balance_value;
1653 p_yea_info.non_taxable_ovt := g_balance_value_tab(9).balance_value
1654 + g_balance_value_tab(10).balance_value;
1655 if p_effective_date >= c_20060101 then
1656 p_yea_info.non_taxable_oth := g_balance_value_tab(5).balance_value
1657 + g_balance_value_tab(6).balance_value
1658 + g_balance_value_tab(22).balance_value -- Bug 5756699
1659 + g_balance_value_tab(23).balance_value
1660 - p_yea_info.non_taxable_ovs
1661 - p_yea_info.non_taxable_ovt;
1662 else
1663 p_yea_info.non_taxable_oth := g_balance_value_tab(5).balance_value
1664 + g_balance_value_tab(6).balance_value
1665 - p_yea_info.non_taxable_ovs
1666 - p_yea_info.non_taxable_ovt;
1667 end if;
1668 --
1669 -- bug 6012258
1670 l_stock_pur_opt_exec_earn_mth := g_balance_value_tab(24).balance_value;
1671 l_stock_pur_opt_exec_earn_bon := g_balance_value_tab(25).balance_value;
1672 l_research_payment_mth := g_balance_value_tab(26).balance_value;
1673 l_research_payment_bon := g_balance_value_tab(27).balance_value;
1674 p_yea_info.cur_stck_pur_opt_exec_earn := l_stock_pur_opt_exec_earn_mth + l_stock_pur_opt_exec_earn_bon;
1675 p_yea_info.research_payment := l_research_payment_mth + l_research_payment_bon;
1676 --
1677 p_yea_info.hi_prem := greatest(nvl(g_balance_value_tab(11).balance_value,0),0); -- Bug 6726096
1678 p_yea_info.ei_prem := greatest(nvl(g_balance_value_tab(12).balance_value,0),0); -- Bug 6726096
1679 p_yea_info.np_prem := g_balance_value_tab(13).balance_value;
1680 p_yea_info.pen_prem := g_balance_value_tab(28).balance_value; -- Bug 6024342
1681 --
1682 p_yea_info.long_term_ins_prem := greatest(nvl(g_balance_value_tab(29).balance_value,0),0); -- Bug 7164589
1683 --
1684 -- need to add Adjustment Amounts because now run_type for YEA process is not MTH
1685 --
1686 p_yea_info.cur_itax := g_balance_value_tab(14).balance_value
1687 + g_balance_value_tab(15).balance_value;
1688 p_yea_info.cur_rtax := g_balance_value_tab(16).balance_value
1689 + g_balance_value_tab(17).balance_value;
1690 p_yea_info.cur_stax := g_balance_value_tab(18).balance_value
1691 + g_balance_value_tab(19).balance_value;
1692 -- Added for bug 3201332
1693 p_yea_info.monthly_reg_earning := g_balance_value_tab(20).balance_value;
1694 --
1695 --
1696 -- Bug : 4750653
1697 l_corp_pension_prem_bal_value := g_balance_value_tab(21).balance_value+
1698 p_yea_info.corp_pension_prem;
1699 --
1700 if g_debug then
1701 hr_utility.trace('p_yea_info.cur_itax '||to_char(p_yea_info.cur_itax));
1702 hr_utility.trace('p_yea_info.cur_rtax '||to_char(p_yea_info.cur_rtax));
1703 hr_utility.trace('p_yea_info.cur_stax '||to_char(p_yea_info.cur_stax));
1704 end if;
1705 --
1706 end if;
1707 close csr_assact;
1708 ------------------------------------------------------------------------
1709 -- Derive Dependent Information.
1710 -- This code will be changed in the near future by using PER_CONTACT_RELATIONSHIPS.
1711 ------------------------------------------------------------------------
1712 --
1713 if g_debug then
1714 hr_utility.set_location('pay_kr_yea_pkg.yea_info.',40);
1715 end if;
1716 --
1717 l_dummy := pay_kr_ff_functions_pkg.get_dependent_info(
1718 p_assignment_id => p_assignment_id,
1719 p_date_earned => p_effective_date,
1720 p_non_resident_flag => p_yea_info.non_resident_flag,
1721 p_dpnt_spouse_flag => p_yea_info.dpnt_spouse_flag,
1722 p_num_of_aged_dpnts => p_yea_info.num_of_aged_dpnts,
1723 p_num_of_adult_dpnts => p_yea_info.num_of_adult_dpnts,
1724 p_num_of_underaged_dpnts => p_yea_info.num_of_underaged_dpnts,
1725 p_num_of_dpnts => p_yea_info.num_of_dpnts,
1726 p_num_of_ageds => p_yea_info.num_of_ageds,
1727 p_num_of_disableds => p_yea_info.num_of_disableds,
1728 p_female_ee_flag => p_yea_info.female_ee_flag,
1729 p_num_of_children => p_yea_info.num_of_children,
1730 -- Bug 3172960
1731 p_num_of_super_ageds => p_yea_info.num_of_super_ageds,
1732 -- Bug 6705170
1733 p_num_of_new_born_adopted => p_yea_info.num_of_new_born_adopted,
1734 -- Bug 6784288
1735 p_num_of_addtl_child => p_yea_info.num_of_addtl_child);
1736 ------------------------------------------------------------------------
1737 -- Derive YEA Information from assignment EIT.
1738 ------------------------------------------------------------------------
1739 if g_debug then
1740 hr_utility.set_location('pay_kr_yea_pkg.yea_info.',50);
1741 end if;
1742 ------------------------------------------------------------------------
1743 -- Previous Employers' Information
1744 ------------------------------------------------------------------------
1745 prev_er_info(
1746 p_assignment_id => p_assignment_id,
1747 p_effective_date => p_effective_date,
1748 p_termination_date_tbl => p_yea_info.prev_termination_date_tbl,
1749 p_corp_name_tbl => p_yea_info.prev_corp_name_tbl,
1750 p_bp_number_tbl => p_yea_info.prev_bp_number_tbl,
1751 p_taxable_mth_tbl => p_yea_info.prev_taxable_mth_tbl,
1752 p_taxable_bon_tbl => p_yea_info.prev_taxable_bon_tbl,
1753 p_sp_irreg_bonus_tbl => p_yea_info.prev_sp_irreg_bonus_tbl,
1754 p_stck_pur_opt_exec_earn_tbl => p_yea_info.prev_stck_pur_opt_exe_earn_tbl, -- Bug 6024342
1755 p_non_taxable_ovs_tbl => p_yea_info.prev_non_taxable_ovs_tbl,
1756 p_non_taxable_ovt_tbl => p_yea_info.prev_non_taxable_ovt_tbl,
1757 p_non_taxable_oth_tbl => p_yea_info.prev_non_taxable_oth_tbl,
1758 p_hi_prem_tbl => p_yea_info.prev_hi_prem_tbl,
1759 p_ltci_prem_tbl => p_yea_info.prev_ltci_prem_tbl, -- Bug 7260606
1760 p_ei_prem_tbl => p_yea_info.prev_ei_prem_tbl,
1761 p_np_prem_tbl => p_yea_info.prev_np_prem_tbl,
1762 p_pen_prem_tbl => p_yea_info.prev_pen_prem_tbl, -- Bug 6024342
1763 p_itax_tbl => p_yea_info.prev_itax_tbl,
1764 p_rtax_tbl => p_yea_info.prev_rtax_tbl,
1765 p_stax_tbl => p_yea_info.prev_stax_tbl);
1766 for i in 1..p_yea_info.prev_termination_date_tbl.count loop
1767 p_yea_info.prev_taxable_mth := p_yea_info.prev_taxable_mth + p_yea_info.prev_taxable_mth_tbl(i);
1768 p_yea_info.prev_taxable_bon := p_yea_info.prev_taxable_bon + p_yea_info.prev_taxable_bon_tbl(i);
1769 p_yea_info.prev_sp_irreg_bonus := p_yea_info.prev_sp_irreg_bonus + p_yea_info.prev_sp_irreg_bonus_tbl(i);
1770 -- Bug 6024342
1771 p_yea_info.prev_stck_pur_opt_exec_earn := p_yea_info.prev_stck_pur_opt_exec_earn + p_yea_info.prev_stck_pur_opt_exe_earn_tbl(i);
1772 --
1773 p_yea_info.non_taxable_ovs := p_yea_info.non_taxable_ovs + p_yea_info.prev_non_taxable_ovs_tbl(i);
1774 p_yea_info.non_taxable_ovt := p_yea_info.non_taxable_ovt + p_yea_info.prev_non_taxable_ovt_tbl(i);
1775 p_yea_info.non_taxable_oth := p_yea_info.non_taxable_oth + p_yea_info.prev_non_taxable_oth_tbl(i);
1776 p_yea_info.hi_prem := p_yea_info.hi_prem + p_yea_info.prev_hi_prem_tbl(i);
1777 p_yea_info.long_term_ins_prem := p_yea_info.long_term_ins_prem + p_yea_info.prev_ltci_prem_tbl(i); -- Bug 7260606
1778 p_yea_info.ei_prem := p_yea_info.ei_prem + p_yea_info.prev_ei_prem_tbl(i);
1779 p_yea_info.np_prem := p_yea_info.np_prem + p_yea_info.prev_np_prem_tbl(i);
1780 p_yea_info.pen_prem := p_yea_info.pen_prem + p_yea_info.prev_pen_prem_tbl(i); -- Bug 6024342
1781 p_yea_info.prev_itax := p_yea_info.prev_itax + p_yea_info.prev_itax_tbl(i);
1782 p_yea_info.prev_rtax := p_yea_info.prev_rtax + p_yea_info.prev_rtax_tbl(i);
1783 p_yea_info.prev_stax := p_yea_info.prev_stax + p_yea_info.prev_stax_tbl(i);
1784 end loop;
1785 ------------------------------------------------------------------------
1786 -- Special Tax Exemption Information
1787 ------------------------------------------------------------------------
1788 if g_debug then
1789 hr_utility.set_location('pay_kr_yea_pkg.yea_info.',60);
1790 end if;
1791 --
1792 sp_tax_exem_info(
1793 p_assignment_id => p_assignment_id,
1794 p_effective_date => p_effective_date,
1795 p_hi_prem => l_hi_prem,
1796 p_ltci_prem => l_ltci_prem, -- Bug 7260606
1797 p_ei_prem => l_ei_prem,
1798 p_pers_ins_name => p_yea_info.pers_ins_name,
1799 p_pers_ins_prem => p_yea_info.pers_ins_prem,
1800 p_disabled_ins_prem => p_yea_info.disabled_ins_prem,
1801 p_med_exp => p_yea_info.med_exp,
1802 p_med_exp_card_emp => p_yea_info.med_exp_card_emp,
1803 p_med_exp_disabled => p_yea_info.med_exp_disabled,
1804 p_med_exp_aged => p_yea_info.med_exp_aged,
1805 p_ee_educ_exp => p_yea_info.ee_educ_exp,
1806 p_ee_occupation_educ_exp2005 => p_yea_info.ee_occupation_educ_exp2005, -- Bug 3971542
1807 p_housing_purchase_date => p_yea_info.housing_purchase_date,
1808 p_housing_loan_date => p_yea_info.housing_loan_date,
1809 p_housing_loan_repay => p_yea_info.housing_loan_repay,
1810 p_lt_housing_loan_date => p_yea_info.lt_housing_loan_date,
1811 p_lt_housing_loan_intr_repay => p_yea_info.lt_housing_loan_interest_repay,
1812 p_lt_housing_loan_date_1 => p_yea_info.lt_housing_loan_date_1,
1813 p_lt_housing_loan_intr_repay_1 => p_yea_info.lt_housing_loan_intr_repay_1,
1814 p_donation1 => p_yea_info.donation1,
1815 p_political_donation1 => p_yea_info.political_donation1,
1816 p_political_donation2 => p_yea_info.political_donation2,
1817 p_political_donation3 => p_yea_info.political_donation3,
1818 p_donation2 => p_yea_info.donation2,
1819 p_donation3 => p_yea_info.donation3,
1820 -- Bug 3966549
1821 p_esoa_don2004 => p_yea_info.esoa_don2004,
1822 -- End of 3966549
1823 p_marriage_exemption => p_yea_info.marriage_exemption,
1824 p_funeral_exemption => p_yea_info.funeral_exemption,
1825 p_relocation_exemption => p_yea_info.relocation_exemption,
1826 -- Bug 3172960
1827 p_med_exp_emp => p_yea_info.med_exp_emp);
1828 ------------------------------------------------------------------------
1829 p_yea_info.hi_prem := p_yea_info.hi_prem + l_hi_prem;
1830 p_yea_info.long_term_ins_prem := p_yea_info.long_term_ins_prem + l_ltci_prem; -- Bug 7260606
1831 p_yea_info.ei_prem := p_yea_info.ei_prem + l_ei_prem;
1832 ------------------------------------------------------------------------
1833 -- Dependent Education Expense Tax Exemption Information
1834 ------------------------------------------------------------------------
1835 if g_debug then
1836 hr_utility.set_location('pay_kr_yea_pkg.yea_info.',70);
1837 end if;
1838 --
1839 dpnt_educ_tax_exem_info(
1840 p_assignment_id => p_assignment_id,
1841 p_effective_date => p_effective_date,
1842 p_contact_type_tbl => p_yea_info.dpnt_educ_contact_type_tbl,
1843 p_school_type_tbl => p_yea_info.dpnt_educ_school_type_tbl,
1844 p_exp_tbl => p_yea_info.dpnt_educ_exp_tbl);
1845 ------------------------------------------------------------------------
1846 -- Changes for Bug 3201332
1847 -- Foreign Worker PreTax Deduction Information
1848 ------------------------------------------------------------------------
1849 if g_debug then
1850 hr_utility.set_location('pay_kr_yea_pkg.yea_info.',75);
1851 end if;
1852 --
1853 fw_tax_exem_info(
1854 p_assignment_id => p_assignment_id,
1855 p_effective_date => p_effective_date,
1856 p_educ_expense => p_yea_info.fw_educ_expense,
1857 p_house_rent => p_yea_info.fw_house_rent);
1858 ------------------------------------------------------------------------
1859 -- Housing Expenses Tax Exemption
1860 -- Changed for Bug 2523481
1861 ------------------------------------------------------------------------
1862 if g_debug then
1863 hr_utility.set_location('pay_kr_yea_pkg.yea_info.',80);
1864 end if;
1865 --
1866 hous_exp_tax_exem_info(
1867 p_assignment_id => p_assignment_id,
1868 p_effective_date => p_effective_date,
1869 p_saving_type_tbl => p_yea_info.housing_saving_type_tbl,
1870 p_saving_tbl => p_yea_info.housing_saving_tbl);
1871 ------------------------------------------------------------------------
1872 -- Tax Exemption Information
1873 -- Direct card expenses introduced for fix 2879008
1874 ------------------------------------------------------------------------
1875 if g_debug then
1876 hr_utility.set_location('pay_kr_yea_pkg.yea_info.',90);
1877 end if;
1878 --
1879 tax_exem_info(
1880 p_assignment_id => p_assignment_id,
1881 p_effective_date => p_effective_date,
1882 p_np_prem => l_np_prem,
1883 p_pen_prem => l_pen_prem, -- Bug 6024342
1884 p_pers_pension_prem => p_yea_info.pers_pension_prem,
1885 p_corp_pension_prem => p_yea_info.corp_pension_prem,
1886 p_pers_pension_saving => p_yea_info.pers_pension_saving,
1887 p_invest_partner_fin1 => p_yea_info.invest_partner_fin1,
1888 p_invest_partner_fin2 => p_yea_info.invest_partner_fin2,
1889 p_small_bus_install => p_yea_info.small_bus_install, -- Bug 6895093
1890 p_credit_card_exp => p_yea_info.credit_card_exp,
1891 p_direct_card_exp => p_yea_info.direct_card_exp,
1892 -- Bug 3966549
1893 p_emp_cre_card_direct_exp2004 => p_yea_info.emp_cre_card_direct_exp2004,
1894 p_dpnt_cre_card_dir_exp2004 => p_yea_info.dpnt_cre_card_direct_exp2004,
1895 p_giro_tuition_paid_exp2004 => p_yea_info.giro_tuition_paid_exp2004,
1896 -- Bug 3506168
1897 p_cash_receipt_exp2005 => p_yea_info.cash_receipt_exp2005,
1898 --
1899 -- End of 3966549
1900 p_emp_stk_own_contri => p_yea_info.emp_stk_own_contri,
1901 -- Bug 6630135
1902 p_tot_med_exp_cards => p_yea_info.tot_med_exp_cards,
1903 p_dpnt_med_exp_cards => p_yea_info.dpnt_med_exp_cards
1904 -- End of Bug 6630135
1905 );
1906 ------------------------------------------------------------------------
1907 p_yea_info.np_prem := p_yea_info.np_prem + l_np_prem;
1908 p_yea_info.pen_prem := p_yea_info.pen_prem + l_pen_prem; -- Bug 6024342
1909 p_yea_info.corp_pension_prem := p_yea_info.corp_pension_prem + l_corp_pension_prem_bal_value; --Bug 4750653
1910 ------------------------------------------------------------------------
1911 -- Tax Break Information
1912 ------------------------------------------------------------------------
1913 if g_debug then
1914 hr_utility.set_location('pay_kr_yea_pkg.yea_info.',100);
1915 end if;
1916 --
1917 tax_break_info(
1918 p_assignment_id => p_assignment_id,
1919 p_effective_date => p_effective_date,
1920 p_housing_loan_interest_repay => p_yea_info.housing_loan_interest_repay,
1921 p_stock_saving => p_yea_info.stock_saving,
1922 p_lt_stock_saving1 => p_yea_info.lt_stock_saving1,
1923 p_lt_stock_saving2 => p_yea_info.lt_stock_saving2);
1924 ------------------------------------------------------------------------
1925 -- Overseas Tax Break Information
1926 ------------------------------------------------------------------------
1927 if g_debug then
1928 hr_utility.set_location('pay_kr_yea_pkg.yea_info.',110);
1929 end if;
1930 --
1931 ovs_tax_break_info(
1932 p_assignment_id => p_assignment_id,
1933 p_effective_date => p_effective_date,
1934 p_tax_paid_date => p_yea_info.ovstb_tax_paid_date,
1935 p_territory_code => p_yea_info.ovstb_territory_code,
1936 p_currency_code => p_yea_info.ovstb_currency_code,
1937 p_taxable => p_yea_info.ovstb_taxable,
1938 p_taxable_subj_tax_break => p_yea_info.ovstb_taxable_subj_tax_break,
1939 p_tax_break_rate => p_yea_info.ovstb_tax_break_rate,
1940 p_tax_foreign_currency => p_yea_info.ovstb_tax_foreign_currency,
1941 p_tax => p_yea_info.ovstb_tax,
1942 p_application_date => p_yea_info.ovstb_application_date,
1943 p_submission_date => p_yea_info.ovstb_submission_date);
1944 --
1945 -- 2311505
1946 --
1947 p_yea_info.cur_taxable_mth := p_yea_info.cur_taxable_mth
1948 + p_yea_info.ovstb_taxable
1949 - trunc(p_yea_info.ovstb_taxable_subj_tax_break * p_yea_info.ovstb_tax_break_rate / 100);
1950 ------------------------------------------------------------------------
1951 -- Foreign Worker Tax Break Information
1952 ------------------------------------------------------------------------
1953 if g_debug then
1954 hr_utility.set_location('pay_kr_yea_pkg.yea_info.',120);
1955 end if;
1956 --
1957 -- Bug 5083240: Update call for new parameters p_assignment_action_id, p_contr_taxable_earn, and p_contr_non_taxable_earn
1958 fw_tax_break_info(
1959 p_assignment_action_id => p_assignment_action_id,
1960 p_assignment_id => p_assignment_id,
1961 p_effective_date => p_effective_date,
1962 p_immigration_purpose => p_yea_info.fwtb_immigration_purpose,
1963 p_contract_date => p_yea_info.fwtb_contract_date,
1964 p_expiry_date => p_yea_info.fwtb_expiry_date,
1965 p_application_date => p_yea_info.fwtb_application_date,
1966 p_submission_date => p_yea_info.fwtb_submission_date,
1967 p_contr_taxable_earn => p_yea_info.fw_contr_taxable_earn,
1968 p_contr_non_taxable_earn => p_yea_info.fw_contr_non_taxable_earn);
1969 -- End of 5083240
1970 --
1971 -- Bug 6012258
1972 p_yea_info.cur_taxable := p_yea_info.cur_taxable_mth + p_yea_info.cur_taxable_bon + p_yea_info.cur_sp_irreg_bonus + p_yea_info.cur_stck_pur_opt_exec_earn;
1973 --
1974 -- Bug 6024342
1975 p_yea_info.prev_taxable := p_yea_info.prev_taxable_mth + p_yea_info.prev_taxable_bon + p_yea_info.prev_sp_irreg_bonus + p_yea_info.prev_stck_pur_opt_exec_earn;
1976 --
1977 p_yea_info.taxable_mth := p_yea_info.cur_taxable_mth + p_yea_info.prev_taxable_mth;
1978 p_yea_info.taxable_bon := p_yea_info.cur_taxable_bon + p_yea_info.prev_taxable_bon;
1979 p_yea_info.sp_irreg_bonus := p_yea_info.cur_sp_irreg_bonus + p_yea_info.prev_sp_irreg_bonus;
1980 p_yea_info.stck_pur_opt_exec_earn := p_yea_info.cur_stck_pur_opt_exec_earn + p_yea_info.prev_stck_pur_opt_exec_earn;
1981 p_yea_info.taxable := p_yea_info.cur_taxable + p_yea_info.prev_taxable;
1982 -- Bug 6012258
1983 p_yea_info.non_taxable := p_yea_info.non_taxable_ovs
1984 + p_yea_info.non_taxable_ovt
1985 + p_yea_info.non_taxable_oth
1986 + p_yea_info.research_payment;
1987 --
1988 ------------------------------------------------------------------------
1989 -- Derive Tax Exemption, Tax Break, Tax Adjustment etc.
1990 -- which depends on effective_date.
1991 ------------------------------------------------------------------------
1992 if g_debug then
1993 hr_utility.set_location('pay_kr_yea_pkg.yea_info.',130);
1994 end if;
1995 --
1996 -- Bug 6705170
1997 --
1998 if p_effective_date >= c_20080101 then
1999 pay_kr_yea20080101_pkg.yea(
2000 p_assignment_id => p_assignment_id,
2001 p_effective_date => p_effective_date,
2002 p_business_group_id => p_business_group_id,
2003 p_yea_info => p_yea_info,
2004 p_tax_adj_warning => p_tax_adj_warning);
2005 --
2006 -- Bug 5734313
2007 --
2008 elsif p_effective_date >= c_20070101 then
2009 pay_kr_yea20070101_pkg.yea(
2010 p_assignment_id => p_assignment_id,
2011 p_effective_date => p_effective_date,
2012 p_business_group_id => p_business_group_id,
2013 p_yea_info => p_yea_info,
2014 p_tax_adj_warning => p_tax_adj_warning);
2015 --
2016 elsif p_effective_date >= c_20060101 then
2017 pay_kr_yea20060101_pkg.yea(
2018 p_assignment_id => p_assignment_id,
2019 p_effective_date => p_effective_date,
2020 p_business_group_id => p_business_group_id,
2021 p_yea_info => p_yea_info,
2022 p_tax_adj_warning => p_tax_adj_warning);
2023
2024 elsif p_effective_date >= c_20050101 then
2025 pay_kr_yea20050101_pkg.yea(
2026 p_assignment_id => p_assignment_id,
2027 p_effective_date => p_effective_date,
2028 p_business_group_id => p_business_group_id,
2029 p_yea_info => p_yea_info,
2030 p_tax_adj_warning => p_tax_adj_warning);
2031
2032 elsif p_effective_date >= c_20040101 then
2033 pay_kr_yea20040101_pkg.yea(
2034 p_assignment_id => p_assignment_id,
2035 p_effective_date => p_effective_date,
2036 p_business_group_id => p_business_group_id,
2037 p_yea_info => p_yea_info,
2038 p_tax_adj_warning => p_tax_adj_warning);
2039
2040 elsif p_effective_date >= c_20030101 then
2041 pay_kr_yea20030101_pkg.yea(
2042 p_assignment_id => p_assignment_id,
2043 p_effective_date => p_effective_date,
2044 p_business_group_id => p_business_group_id,
2045 p_yea_info => p_yea_info,
2046 p_tax_adj_warning => p_tax_adj_warning);
2047
2048 elsif p_effective_date >= c_20020101 then
2049 pay_kr_yea20020101_pkg.yea(
2050 p_assignment_id => p_assignment_id,
2051 p_effective_date => p_effective_date,
2052 p_business_group_id => p_business_group_id,
2053 p_yea_info => p_yea_info,
2054 -- Bug 2878937
2055 p_tax_adj_warning => p_tax_adj_warning);
2056
2057 elsif p_effective_date >= c_00010101 then
2058 pay_kr_yea00010101_pkg.yea(
2059 p_assignment_id => p_assignment_id,
2060 p_effective_date => p_effective_date,
2061 p_business_group_id => p_business_group_id,
2062 p_yea_info => p_yea_info);
2063 end if;
2064 ---------------
2065 if g_debug then
2066 hr_utility.trace('=====================================================');
2067 hr_utility.trace('Current Taxable : '||to_char(p_yea_info.cur_taxable));
2068 hr_utility.trace('Previous Taxable : '||to_char(p_yea_info.prev_taxable));
2069 hr_utility.trace('Total Taxable : '||to_char(p_yea_info.taxable));
2070 hr_utility.trace('Income Tax : '||to_char(p_yea_info.cur_itax));
2071 hr_utility.trace('Resident Tax : '||to_char(p_yea_info.cur_itax));
2072 hr_utility.trace('Income Tax Adjustment : '||to_char(p_yea_info.itax_adj));
2073 hr_utility.trace('Resident Tax Adjustment : '||to_char(p_yea_info.rtax_adj));
2074 hr_utility.trace('=====================================================');
2075 end if;
2076 ----------------
2077 ------------------------------------------------------------------------
2078 -- Setup output warnings
2079 ------------------------------------------------------------------------
2080 p_taxable_earnings_warning := false;
2081 p_taxable_income_warning := false;
2082 p_taxation_base_warning := false;
2083 p_calc_tax_warning := false;
2084 p_itax_warning := false;
2085 p_rtax_warning := false;
2086 if p_yea_info.taxable <= 0 then
2087 p_taxable_earnings_warning := true;
2088 else
2089 if p_yea_info.taxable_income = 0 then
2090 p_taxable_income_warning := true;
2091 else
2092 if p_yea_info.taxation_base = 0 then
2093 p_taxation_base_warning := true;
2094 else
2095 if p_yea_info.calc_tax = 0 then
2096 p_calc_tax_warning := true;
2097 end if;
2098 end if;
2099 end if;
2100 end if;
2101 if p_yea_info.annual_itax = 0 then
2102 p_itax_warning := true;
2103 end if;
2104 if p_yea_info.annual_rtax = 0 then
2105 p_rtax_warning := true;
2106 end if;
2107 end yea_info;
2108 ------------------------------------------------------------------------
2109 function yea_info(
2110 p_assignment_action_id in number) return t_yea_info
2111 ------------------------------------------------------------------------
2112 is
2113 l_assignment_id number;
2114 l_effective_date date;
2115 l_user_entity_id_tbl t_number_tbl;
2116 l_archive_item_value_tbl t_varchar2_tbl;
2117 l_yea_info t_yea_info;
2118 --
2119 cursor csr_archive is
2120 select
2121 u.user_entity_id,
2122 a.value
2123 from pay_report_format_items_f i,
2124 ff_database_items d,
2125 ff_user_entities u,
2126 ff_archive_items a,
2127 pay_payroll_actions ppa,
2128 pay_assignment_actions paa
2129 where paa.assignment_action_id = p_assignment_action_id
2130 and ppa.payroll_action_id = paa.payroll_action_id
2131 and a.context1 = paa.assignment_action_id
2132 and a.value is not null
2133 and u.user_entity_id = a.user_entity_id
2134 and d.user_entity_id = u.user_entity_id
2135 --
2136 -- Default value for data type number is "0" in t_yea_info
2137 --
2138 and decode(a.value, '0', decode(d.data_type, 'N', 'N', 'Y'), 'Y') = 'Y'
2139 and i.report_type = nvl(ppa.report_type, u.user_entity_name)
2140 and i.report_qualifier = ppa.report_qualifier
2141 and i.report_category = ppa.report_category
2142 and i.user_entity_id = u.user_entity_id
2143 and ppa.effective_date
2144 between i.effective_start_date and i.effective_end_date
2145 order by i.display_sequence;
2146 begin
2147 select paa.assignment_id,
2148 ppa.effective_date
2149 into l_assignment_id,
2150 l_effective_date
2151 from pay_payroll_actions ppa,
2152 pay_assignment_actions paa
2153 where paa.assignment_action_id = p_assignment_action_id
2154 and ppa.payroll_action_id = paa.payroll_action_id;
2155 --
2156 -- Bulk collect archive items. Bulk collect is for better performance.
2157 --
2158 open csr_archive;
2159 fetch csr_archive bulk collect into l_user_entity_id_tbl, l_archive_item_value_tbl;
2160 close csr_archive;
2161 --
2162 l_yea_info := convert_to_rec(
2163 p_user_entity_id_tbl => l_user_entity_id_tbl,
2164 p_archive_item_value_tbl => l_archive_item_value_tbl);
2165 ------------------------------------------------------------------------
2166 -- Previous Employers' Information
2167 ------------------------------------------------------------------------
2168 prev_er_info(
2169 p_assignment_id => l_assignment_id,
2170 p_effective_date => l_effective_date,
2171 p_termination_date_tbl => l_yea_info.prev_termination_date_tbl,
2172 p_corp_name_tbl => l_yea_info.prev_corp_name_tbl,
2173 p_bp_number_tbl => l_yea_info.prev_bp_number_tbl,
2174 p_taxable_mth_tbl => l_yea_info.prev_taxable_mth_tbl,
2175 p_taxable_bon_tbl => l_yea_info.prev_taxable_bon_tbl,
2176 p_sp_irreg_bonus_tbl => l_yea_info.prev_sp_irreg_bonus_tbl,
2177 p_stck_pur_opt_exec_earn_tbl => l_yea_info.prev_stck_pur_opt_exe_earn_tbl, -- Bug 6024342
2178 p_non_taxable_ovs_tbl => l_yea_info.prev_non_taxable_ovs_tbl,
2179 p_non_taxable_ovt_tbl => l_yea_info.prev_non_taxable_ovt_tbl,
2180 p_non_taxable_oth_tbl => l_yea_info.prev_non_taxable_oth_tbl,
2181 p_hi_prem_tbl => l_yea_info.prev_hi_prem_tbl,
2182 p_ltci_prem_tbl => l_yea_info.prev_ltci_prem_tbl, -- Bug 7260606
2183 p_ei_prem_tbl => l_yea_info.prev_ei_prem_tbl,
2184 p_np_prem_tbl => l_yea_info.prev_np_prem_tbl,
2185 p_pen_prem_tbl => l_yea_info.prev_pen_prem_tbl, -- Bug 6024342
2186 p_itax_tbl => l_yea_info.prev_itax_tbl,
2187 p_rtax_tbl => l_yea_info.prev_rtax_tbl,
2188 p_stax_tbl => l_yea_info.prev_stax_tbl);
2189 ------------------------------------------------------------------------
2190 -- Dependent Education Expense Tax Exemption Information
2191 ------------------------------------------------------------------------
2192 dpnt_educ_tax_exem_info(
2193 p_assignment_id => l_assignment_id,
2194 p_effective_date => l_effective_date,
2195 p_contact_type_tbl => l_yea_info.dpnt_educ_contact_type_tbl,
2196 p_school_type_tbl => l_yea_info.dpnt_educ_school_type_tbl,
2197 p_exp_tbl => l_yea_info.dpnt_educ_exp_tbl);
2198 --
2199 return l_yea_info;
2200 end yea_info;
2201 --------------------------------------------------------------------------------
2202 procedure chk_assignment_id(
2203 p_business_group_id in number,
2204 p_assignment_id in number,
2205 p_bal_asg_action_id in number,
2206 p_effective_date in date,
2207 p_payroll_id in number,
2208 p_report_category in varchar2,
2209 p_error out nocopy varchar2)
2210 --------------------------------------------------------------------------------
2211 is
2212 l_proc constant varchar2(61) := g_package || 'chk_assignment_id';
2213 l_business_group_id number;
2214 l_esd date;
2215 l_eed date;
2216 l_payroll_id number;
2217
2218 cursor csr_asg is
2219 select
2220 pa.business_group_id,
2221 pa.effective_start_date,
2222 pa.effective_end_date,
2223 pa.payroll_id
2224 from per_assignments_f pa
2225 where pa.assignment_id = p_assignment_id
2226 and pa.assignment_type = 'E'
2227 and pa.effective_start_date <= p_effective_date
2228 and pa.effective_end_date >= trunc(p_effective_date, 'YYYY')
2229 order by pa.effective_start_date desc
2230 for update of pa.assignment_id;
2231
2232 l_exists varchar2(1);
2233
2234 cursor csr_incomplete_exists is
2235 select
2236 'Y'
2237 from dual
2238 where exists(
2239 select null
2240 from pay_action_classifications pac,
2241 pay_payroll_actions ppa,
2242 pay_assignment_actions paa
2243 where paa.assignment_id = p_assignment_id
2244 and paa.action_status not in ('C', 'S') -- Bug 4442484: A 'S'kipped assact is not an errored one
2245 and paa.assignment_action_id <> p_bal_asg_action_id
2246 and paa.source_action_id is null
2247 and ppa.payroll_action_id = paa.payroll_action_id
2248 and ppa.effective_date <= p_effective_date
2249 and pac.action_type = ppa.action_type
2250 and pac.classification_name = 'SEQUENCED');
2251
2252 l_effective_date date;
2253
2254 cursor csr_archive_exists
2255 is
2256 select ppa.effective_date
2257 from pay_payroll_actions ppa,
2258 pay_assignment_actions paa
2259 where paa.assignment_id = p_assignment_id
2260 and ppa.payroll_action_id = paa.payroll_action_id
2261 and paa.source_action_id is null
2262 and ppa.action_type IN ('X','B')
2263 and ppa.report_type = 'YEA'
2264 and ppa.report_qualifier = 'KR'
2265 and ppa.report_category IN ('N','I')
2266 and trunc(ppa.effective_date, 'YYYY') = trunc(p_effective_date, 'YYYY')
2267 order by paa.action_sequence desc;
2268
2269 cursor csr_bal_adj_exists(p_payroll_id pay_payroll_actions.payroll_id%type)
2270 is
2271 select ppa.effective_date
2272 from pay_payroll_actions ppa,
2273 pay_assignment_actions paa
2274 where paa.assignment_id = p_assignment_id
2275 and ppa.payroll_action_id = paa.payroll_action_id
2276 and ppa.payroll_id = p_payroll_id -- Bug 5045110
2277 and paa.assignment_action_id <> p_bal_asg_action_id
2278 and paa.source_action_id is null
2279 and ppa.action_type = 'B'
2280 and pay_kr_ff_functions_pkg.get_legislative_parameter(ppa.payroll_action_id, 'REPORT_TYPE', null) = 'YEA'
2281 and pay_kr_ff_functions_pkg.get_legislative_parameter(ppa.payroll_action_id, 'REPORT_QUALIFIER', null) = 'KR'
2282 and pay_kr_ff_functions_pkg.get_legislative_parameter(ppa.payroll_action_id, 'REPORT_CATEGORY', null) IN ('N','I')
2283 and ppa.effective_date between trunc(p_effective_date, 'YYYY')
2284 and (trunc(add_months(p_effective_date, 12), 'YYYY') - 1) -- Bug 5045110
2285 order by paa.action_sequence desc;
2286
2287 begin
2288 --
2289 if g_debug then
2290 hr_utility.set_location('pay_kr_yea_pkg.chk_assignment_id.',10);
2291 end if;
2292 --
2293 hr_api.mandatory_arg_error(
2294 p_api_name => l_proc,
2295 p_argument => 'business_group_id',
2296 p_argument_value => p_business_group_id);
2297 hr_api.mandatory_arg_error(
2298 p_api_name => l_proc,
2299 p_argument => 'assignment_id',
2300 p_argument_value => p_assignment_id);
2301 hr_api.mandatory_arg_error(
2302 p_api_name => l_proc,
2303 p_argument => 'effective_date',
2304 p_argument_value => p_effective_date);
2305 hr_api.mandatory_arg_error(
2306 p_api_name => l_proc,
2307 p_argument => 'payroll_id',
2308 p_argument_value => p_payroll_id);
2309 hr_api.mandatory_arg_error(
2310 p_api_name => l_proc,
2311 p_argument => 'report_category',
2312 p_argument_value => p_report_category);
2313 --
2314 if g_debug then
2315 hr_utility.set_location('pay_kr_yea_pkg.chk_assignment_id.',20);
2316 end if;
2317 --
2318 open csr_asg;
2319 fetch csr_asg into l_business_group_id, l_esd, l_eed, l_payroll_id;
2320
2321 if csr_asg%NOTFOUND then
2322 --
2323 close csr_asg;
2324 --
2325 if g_debug then
2326 hr_utility.set_location('pay_kr_yea_pkg.chk_assignment_id.',30);
2327 end if;
2328 --
2329 fnd_message.set_name('PAY', 'PAY_KR_INV_ASG');
2330 p_error := fnd_message.get;
2331 return;
2332 else
2333 --
2334 close csr_asg;
2335 --
2336 if g_debug then
2337 hr_utility.set_location('pay_kr_yea_pkg.chk_assignment_id.',40);
2338 end if;
2339 --
2340 --
2341 -- Business Group Validation
2342 --
2343 if l_business_group_id <> p_business_group_id then
2344 fnd_message.set_name('PAY', 'PAY_KR_INV_ASG_BG');
2345 p_error := fnd_message.get;
2346 return;
2347 end if;
2348 --
2349 -- Effective Date Validation
2350 --
2351 if p_effective_date not between l_esd and l_eed then
2352 fnd_message.set_name('PAY', 'PAY_KR_INV_ASG');
2353 p_error := fnd_message.get;
2354 return;
2355 end if;
2356 --
2357 -- Payroll Validation
2358 --
2359 if l_payroll_id <> p_payroll_id then
2360 fnd_message.set_name('PAY', 'PAY_KR_INV_ASG_PAYROLL');
2361 p_error := fnd_message.get;
2362 return;
2363 end if;
2364 end if;
2365 --
2366 if g_debug then
2367 hr_utility.set_location('pay_kr_yea_pkg.chk_assignment_id.',50);
2368 end if;
2369 ------------------------------------------------------------------------
2370 -- If invalid assacts exist before effective_date, raise error.
2371 ------------------------------------------------------------------------
2372 open csr_incomplete_exists;
2373 fetch csr_incomplete_exists into l_exists;
2374
2375 if csr_incomplete_exists%FOUND then
2376 close csr_incomplete_exists;
2377 fnd_message.set_name('PAY', 'PAY_KR_INCOMP_ASSACT_EXISTS');
2378 p_error := fnd_message.get;
2379 return;
2380 end if;
2381 --
2382 if csr_incomplete_exists%ISOPEN then
2383 close csr_incomplete_exists;
2384 end if;
2385 --
2386 if g_debug then
2387 hr_utility.set_location('pay_kr_yea_pkg.chk_assignment_id.',60);
2388 end if;
2389 ------------------------------------------------------------------------
2390 -- YEA Type Validation
2391 ------------------------------------------------------------------------
2392 open csr_archive_exists;
2393 fetch csr_archive_exists into l_effective_date;
2394
2395 if csr_archive_exists%FOUND then
2396 --
2397 if g_debug then
2398 hr_utility.set_location('pay_kr_yea_pkg.chk_assignment_id.',70);
2399 end if;
2400 --
2401 close csr_archive_exists;
2402
2403 if l_effective_date > p_effective_date then
2404 fnd_message.set_name('PAY', 'PAY_KR_YEA_ALREADY_PROCESSED');
2405 fnd_message.set_token('EFFECTIVE_DATE', fnd_date.date_to_displaydate(l_effective_date));
2406 p_error := fnd_message.get;
2407 return;
2408 else
2409 if p_report_category in ('N', 'I') then
2410 fnd_message.set_name('PAY', 'PAY_KR_RUN_REYEA_NOT_YEA');
2411 p_error := fnd_message.get;
2412 return;
2413 end if;
2414 end if;
2415 else
2416 --
2417 if g_debug then
2418 hr_utility.set_location('pay_kr_yea_pkg.chk_assignment_id.',80);
2419 end if;
2420 --
2421 close csr_archive_exists;
2422
2423 open csr_bal_adj_exists(l_payroll_id) ;
2424 fetch csr_bal_adj_exists into l_effective_date;
2425
2426 if csr_bal_adj_exists%FOUND then
2427
2428 close csr_bal_adj_exists;
2429 fnd_message.set_name('PAY', 'PAY_KR_ROLLBACK_BAL_ADJ');
2430 p_error := fnd_message.get;
2431 return;
2432 else
2433 close csr_bal_adj_exists;
2434
2435 if p_report_category = 'R' then
2436 fnd_message.set_name('PAY', 'PAY_KR_RUN_YEA_NOT_REYEA');
2437 p_error := fnd_message.get;
2438 return;
2439 end if;
2440 end if;
2441 end if;
2442
2443 end chk_assignment_id;
2444 --------------------------------------------------------------------------------
2445 procedure process_assignment(
2446 p_validate in boolean,
2447 p_business_group_id in number,
2448 p_assignment_id in number,
2449 p_assignment_action_id in number,
2450 p_bal_asg_action_id in number,
2451 p_report_type in out nocopy varchar2,
2452 p_report_qualifier in out nocopy varchar2,
2453 p_report_category in out nocopy varchar2,
2454 p_effective_date in out nocopy date,
2455 p_payroll_id in out nocopy number,
2456 p_consolidation_set_id in out nocopy number,
2457 p_archive_type_used in out nocopy varchar2) -- Bug 5036734
2458 --------------------------------------------------------------------------------
2459 is
2460 l_yea_info t_yea_info;
2461 l_taxable_earnings_warning boolean;
2462 l_taxable_income_warning boolean;
2463 l_taxation_base_warning boolean;
2464 l_calc_tax_warning boolean;
2465 l_itax_warning boolean;
2466 l_rtax_warning boolean;
2467 -- Bug 2878937
2468 l_tax_adj_warning boolean;
2469 l_entry_value_tbl hr_entry.varchar2_table;
2470 l_user_entity_id_tbl t_number_tbl;
2471 l_archive_item_value_tbl t_varchar2_tbl;
2472 l_message_level_tbl t_varchar2_tbl;
2473 l_message_text_tbl t_varchar2_tbl;
2474 --------------------------------------------------------------------------------
2475 procedure set_message(
2476 p_condition in boolean,
2477 p_message_level in varchar2,
2478 p_application_short_name in varchar2,
2479 p_message_name in varchar2,
2480 p_token_name1 in varchar2,
2481 p_token_value1 in varchar2)
2482 --------------------------------------------------------------------------------
2483 is
2484 l_index number;
2485 begin
2486 if p_condition then
2487 l_index := l_message_level_tbl.count + 1;
2488 fnd_message.set_name(p_application_short_name, p_message_name);
2489 if p_token_name1 is not null then
2490 fnd_message.set_token(p_token_name1, p_token_value1);
2491 end if;
2492 l_message_level_tbl(l_index) := p_message_level;
2493 l_message_text_tbl(l_index) := fnd_message.get;
2494 end if;
2495 end set_message;
2496 begin
2497 ------------------------------------------------------------------------
2498 -- Process YEA
2499 ------------------------------------------------------------------------
2500 if g_debug then
2501 hr_utility.trace('Running YEA ..........................................');
2502 end if;
2503 --
2504 yea_info(
2505 p_assignment_id => p_assignment_id,
2506 p_assignment_action_id => p_bal_asg_action_id,
2507 p_effective_date => p_effective_date,
2508 p_business_group_id => p_business_group_id,
2509 p_payroll_id => p_payroll_id,
2510 p_yea_info => l_yea_info,
2511 p_taxable_earnings_warning => l_taxable_earnings_warning,
2512 p_taxable_income_warning => l_taxable_income_warning,
2513 p_taxation_base_warning => l_taxation_base_warning,
2514 p_calc_tax_warning => l_calc_tax_warning,
2515 p_itax_warning => l_itax_warning,
2516 p_rtax_warning => l_rtax_warning,
2517 p_tax_adj_warning => l_tax_adj_warning);
2518
2519 --
2520 if g_debug then
2521 hr_utility.trace('p_yea_info.cur_itax '||to_char(l_yea_info.cur_itax));
2522 hr_utility.trace('p_yea_info.cur_rtax '||to_char(l_yea_info.cur_rtax));
2523 hr_utility.trace('p_yea_info.cur_stax '||to_char(l_yea_info.cur_stax));
2524 hr_utility.trace('p_yea_info.itax_adj '||to_char(l_yea_info.itax_adj));
2525 hr_utility.trace('p_yea_info.rtax_adj '||to_char(l_yea_info.rtax_adj));
2526 hr_utility.trace('p_yea_info.stax_adj '||to_char(l_yea_info.stax_adj));
2527 hr_utility.trace('Checking Balance...........');
2528 end if;
2529 --
2530 pay_balance_pkg.get_value ( p_assignment_action_id => p_bal_asg_action_id
2531 ,p_defined_balance_lst => g_tax_adj_balance_tab );
2532 --
2533 if g_debug then
2534 hr_utility.trace('Balance Value of itax_adj = '||to_char(g_tax_adj_balance_tab(1).balance_value));
2535 hr_utility.trace('Balance Value of rtax_adj = '||to_char(g_tax_adj_balance_tab(2).balance_value));
2536 hr_utility.trace('Balance Value of stax_adj = '||to_char(g_tax_adj_balance_tab(3).balance_value));
2537 end if;
2538 -----------------------------------------------------------------------
2539 if g_tax_adj_balance_tab(1).balance_value <> l_yea_info.itax_adj OR
2540 g_tax_adj_balance_tab(2).balance_value <> l_yea_info.rtax_adj OR
2541 g_tax_adj_balance_tab(3).balance_value <> l_yea_info.stax_adj
2542 then
2543 --
2544 if g_debug then
2545 hr_utility.trace('Incorrect Balance Adjustment amount');
2546 end if;
2547 --
2548 fnd_message.set_name('PAY', 'PAY_KR_INCORRECT_ADJ_AMT');
2549 fnd_message.set_token('ASSIGNMENT_ID', p_assignment_id);
2550 --
2551 insert into pay_message_lines(
2552 LINE_SEQUENCE,
2553 PAYROLL_ID,
2554 MESSAGE_LEVEL,
2555 SOURCE_ID,
2556 SOURCE_TYPE,
2557 LINE_TEXT)
2558 values( pay_message_lines_s.nextval,
2559 p_payroll_id,
2560 'F',
2561 p_assignment_action_id,
2562 'A',
2563 fnd_message.get);
2564 --
2565 fnd_message.raise_error;
2566 else
2567 --
2568 if g_debug then
2569 hr_utility.trace('Archiving...................');
2570 end if;
2571 ------------------------------------------------------------------------
2572 -- Convert from YEA result from record variable to PL/SQL table.
2573 ------------------------------------------------------------------------
2574 convert_to_tbl(
2575 p_report_type => p_report_type,
2576 p_report_qualifier => p_report_qualifier,
2577 p_report_category => p_report_category,
2578 p_effective_date => p_effective_date,
2579 p_yea_info => l_yea_info,
2580 p_user_entity_id_tbl => l_user_entity_id_tbl,
2581 p_archive_item_value_tbl => l_archive_item_value_tbl);
2582 --
2583 -- Bug 5036734 : Need to delete archive records while retrying
2584 -- an archive process that used AAC archive type
2585 if p_archive_type_used <> 'AAP' then -- old archive, delete manually.
2586
2587 delete from ff_archive_items
2588 where CONTEXT1 = p_assignment_action_id
2589 and ARCHIVE_TYPE = 'AAC';
2590
2591 end if;
2592
2593 ------------------------------------------------------------------------
2594 -- Insert into FF_ARCHIVE_ITEMS
2595 ------------------------------------------------------------------------
2596 forall i in 1..l_user_entity_id_tbl.count
2597 insert into ff_archive_items(
2598 ARCHIVE_ITEM_ID,
2599 USER_ENTITY_ID,
2600 CONTEXT1,
2601 VALUE,
2602 ARCHIVE_TYPE)
2603 values( ff_archive_items_s.nextval,
2604 l_user_entity_id_tbl(i),
2605 p_assignment_action_id,
2606 l_archive_item_value_tbl(i),
2607 p_archive_type_used); -- Bug 5036734
2608 --
2609 ------------------------------------------------------------------------
2610 -- Convert message information to PL/SQL table.
2611 ------------------------------------------------------------------------
2612 --
2613 set_message(l_taxable_earnings_warning, 'W', 'PAY', 'PAY_KR_NGTV_TAXABLE_EARNINGS', 'TAXABLE_EARNINGS', to_char(l_yea_info.taxable));
2614 set_message(l_taxable_income_warning, 'I', 'PAY', 'PAY_KR_TAXABLE_INCOME_ZERO',null,null);
2615 set_message(l_taxation_base_warning, 'I', 'PAY', 'PAY_KR_TAXATION_BASE_ZERO',null,null);
2616 set_message(l_calc_tax_warning, 'I', 'PAY', 'PAY_KR_CALC_TAX_ZERO',null,null);
2617 set_message(l_itax_warning, 'I', 'PAY', 'PAY_KR_ITAX_ZERO',null,null);
2618 set_message(l_rtax_warning, 'I', 'PAY', 'PAY_KR_RTAX_ZERO',null,null);
2619 set_message(l_tax_adj_warning, 'I', 'PAY', 'PAY_KR_ADJ_LESS_THAN_THOUSAND', null, null);
2620
2621 --
2622 ------------------------------------------------------------------------
2623 -- Insert into PAY_MESSAGE_LINES
2624 ------------------------------------------------------------------------
2625 forall i in 1..l_message_level_tbl.count
2626 insert into pay_message_lines(
2627 LINE_SEQUENCE,
2628 PAYROLL_ID,
2629 MESSAGE_LEVEL,
2630 SOURCE_ID,
2631 SOURCE_TYPE,
2632 LINE_TEXT)
2633 values( pay_message_lines_s.nextval,
2634 p_payroll_id,
2635 l_message_level_tbl(i),
2636 p_assignment_action_id,
2637 'A',
2638 l_message_text_tbl(i));
2639 --
2640 end if;
2641
2642 exception
2643 when hr_api.validate_enabled then
2644 fnd_message.raise_error;
2645 end process_assignment;
2646 --
2647 ---------------------------------------------------------------------------------------------------------------
2648 -- New function added to get the adj amount and feed it to ITAX_ADJ,RTAX_ADJ,STAX_ADJ elements
2649 -- using formula results
2650 ---------------------------------------------------------------------------------------------------------------
2651 function calculate_adjustment(
2652 p_assignment_id in pay_assignment_actions.assignment_id%type,
2653 p_business_group_id in pay_payroll_actions.business_group_id%type,
2654 p_effective_date in pay_payroll_actions.effective_date%type,
2655 p_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
2656 p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
2657 p_itax_adj out nocopy number,
2658 p_rtax_adj out nocopy number,
2659 p_stax_adj out nocopy number,
2660 p_error out nocopy varchar2
2661 ) return number
2662 ---------------------------------------------------------------------------------------------------------------
2663 is
2664 l_yea_info t_yea_info;
2665 l_tax_adj_warning boolean;
2666 l_taxable_earnings_warning boolean;
2667 l_taxable_income_warning boolean;
2668 l_taxation_base_warning boolean;
2669 l_calc_tax_warning boolean;
2670 l_itax_warning boolean;
2671 l_rtax_warning boolean;
2672 l_return number;
2673 ------------------------------------------------------------------------------
2674 cursor csr_pact
2675 IS
2676 select pay_kr_ff_functions_pkg.get_legislative_parameter(ppa.payroll_action_id, 'REPORT_CATEGORY', null) report_category
2677 ,ppa.element_type_id element_type_id
2678 ,ppa.payroll_id payroll_id
2679 from pay_payroll_actions ppa
2680 where ppa.payroll_action_id = p_payroll_action_id;
2681 -------------------------------------------
2682 r_pact csr_pact%rowtype;
2683 -------------------------------------------
2684 begin
2685 --
2686 hr_api.validate_bus_grp_id(p_business_group_id);
2687 --
2688 if g_debug then
2689 hr_utility.trace('Assignment Id: '||to_char(p_assignment_id)||' Payroll Action Id: '||to_char(p_payroll_action_id));
2690 hr_utility.trace('Effective Date: '||to_char(p_effective_date)||' Business Group Id: '||to_char(p_business_group_id));
2691 end if;
2692 --
2693 open csr_pact;
2694 fetch csr_pact into r_pact;
2695 close csr_pact;
2696 --
2697 if g_debug then
2698 hr_utility.trace('Checking Assignment');
2699 end if;
2700 ------------------------------------------------------------------------
2701 -- Assignment validation
2702 ------------------------------------------------------------------------
2703 chk_assignment_id(
2704 p_business_group_id => p_business_group_id,
2705 p_assignment_id => p_assignment_id,
2706 p_bal_asg_action_id => p_assignment_action_id,
2707 p_effective_date => p_effective_date,
2708 p_payroll_id => r_pact.payroll_id,
2709 p_report_category => r_pact.report_category,
2710 p_error => p_error);
2711 --
2712 if g_debug then
2713 hr_utility.trace('After checking assignment');
2714 end if;
2715 --
2716 if p_error is not null then
2717 return 1; -- failure
2718 else
2719 --
2720 if g_debug then
2721 hr_utility.trace('Calculating Adjustments');
2722 end if;
2723 --
2724 -------------------------------------------------------------------------
2725 -- Calculate Tax Adjustments
2726 -------------------------------------------------------------------------
2727 yea_info(p_assignment_id => p_assignment_id,
2728 p_assignment_action_id => p_assignment_action_id,
2729 p_effective_date => p_effective_date,
2730 p_business_group_id => p_business_group_id,
2731 p_payroll_id => r_pact.payroll_id,
2732 p_yea_info => l_yea_info,
2733 p_taxable_earnings_warning => l_taxable_earnings_warning,
2734 p_taxable_income_warning => l_taxable_income_warning,
2735 p_taxation_base_warning => l_taxation_base_warning,
2736 p_calc_tax_warning => l_calc_tax_warning,
2737 p_itax_warning => l_itax_warning,
2738 p_rtax_warning => l_rtax_warning,
2739 p_tax_adj_warning => l_tax_adj_warning);
2740
2741
2742 p_itax_adj := l_yea_info.itax_adj;
2743 p_rtax_adj := l_yea_info.rtax_adj;
2744 p_stax_adj := l_yea_info.stax_adj;
2745
2746 if g_debug then
2747 hr_utility.trace('p_itax_adj '||to_char(p_itax_adj));
2748 hr_utility.trace('p_rtax_adj '||to_char(p_rtax_adj));
2749 hr_utility.trace('p_stax_adj '||to_char(p_stax_adj));
2750 end if;
2751
2752 return 0; -- success
2753 end if;
2754 --
2755 ---------------------------------------------------------------------------------------------------------------
2756 end calculate_adjustment;
2757 ---------------------------------------------------------------------------------------------------------------
2758 BEGIN
2759 ------------------------------------------------------------------------
2760 -- Package initialization section
2761 ------------------------------------------------------------------------
2762 declare
2763 l_user_entity_id_tbl t_number_tbl;
2764 l_user_entity_name_tbl t_varchar2_tbl;
2765 --------------------------------------------------------
2766 function defined_balance_id(p_user_name in varchar2) return number
2767 --------------------------------------------------------
2768 is
2769 l_defined_balance_id number;
2770 begin
2771 select
2772 u.creator_id
2773 into l_defined_balance_id
2774 from ff_user_entities u,
2775 ff_database_items d
2776 where d.user_name = p_user_name
2777 and u.user_entity_id = d.user_entity_id
2778 and u.legislation_code = 'KR'
2779 and u.business_group_id is null
2780 and u.creator_type = 'B';
2781 --
2782 return l_defined_balance_id;
2783 end defined_balance_id;
2784 --------------------------------------------------------
2785 function element(p_element_name in varchar2) return t_element
2786 --------------------------------------------------------
2787 is
2788 l_element t_element;
2789 begin
2790 select
2791 element_type_id
2792 into l_element.element_type_id
2793 from pay_element_types_f
2794 where element_name = p_element_name
2795 and legislation_code = 'KR'
2796 and business_group_id is null
2797 group by element_type_id;
2798 --
2799 select
2800 input_value_id,
2801 min(name)
2802 bulk collect into
2803 l_element.input_value_id_tbl,
2804 l_element.input_value_name_tbl
2805 from pay_input_values_f
2806 where element_type_id = l_element.element_type_id
2807 and legislation_code = 'KR'
2808 and business_group_id is null
2809 group by input_value_id;
2810 --
2811 return l_element;
2812 end element;
2813 --------------------------------------------------------
2814 function user_entity_id(p_user_entity_name in varchar2) return number
2815 --------------------------------------------------------
2816 is
2817 l_index number;
2818 l_user_entity_id number;
2819 begin
2820 l_index := l_user_entity_id_tbl.first;
2821 while l_index is not null loop
2822 if l_user_entity_name_tbl(l_index) = p_user_entity_name then
2823 l_user_entity_id := l_user_entity_id_tbl(l_index);
2824 l_user_entity_id_tbl.delete(l_index);
2825 l_user_entity_name_tbl.delete(l_index);
2826 exit;
2827 end if;
2828 l_index := l_user_entity_id_tbl.next(l_index);
2829 end loop;
2830 --
2831 if l_user_entity_id is null then
2832 raise no_data_found;
2833 end if;
2834 --
2835 return l_user_entity_id;
2836 end user_entity_id;
2837 begin
2838 ------------------------------------------------------------------------
2839 -- Collecting Defined Balance Id into g_balance_value_tab table
2840 ------------------------------------------------------------------------
2841 g_balance_value_tab(1).defined_balance_id := defined_balance_id('SP_IRREG_BONUS_ASG_YTD_MTH');
2842 g_balance_value_tab(2).defined_balance_id := defined_balance_id('SP_IRREG_BONUS_ASG_YTD_BON');
2843 g_balance_value_tab(3).defined_balance_id := defined_balance_id('TOTAL_TAXABLE_EARNINGS_ASG_YTD_MTH');
2844 g_balance_value_tab(4).defined_balance_id := defined_balance_id('TOTAL_TAXABLE_EARNINGS_ASG_YTD_BON');
2845 g_balance_value_tab(5).defined_balance_id := defined_balance_id('TOTAL_NON_TAXABLE_EARNINGS_ASG_YTD_MTH');
2846 g_balance_value_tab(6).defined_balance_id := defined_balance_id('TOTAL_NON_TAXABLE_EARNINGS_ASG_YTD_BON');
2847 g_balance_value_tab(7).defined_balance_id := defined_balance_id('NON_TAXABLE_OVS_EARNINGS_ASG_YTD_MTH');
2848 g_balance_value_tab(8).defined_balance_id := defined_balance_id('NON_TAXABLE_OVS_EARNINGS_ASG_YTD_BON');
2849 g_balance_value_tab(9).defined_balance_id := defined_balance_id('NON_TAXABLE_OVT_EARNINGS_ASG_YTD_MTH');
2850 g_balance_value_tab(10).defined_balance_id := defined_balance_id('NON_TAXABLE_OVT_EARNINGS_ASG_YTD_BON');
2851 g_balance_value_tab(11).defined_balance_id := defined_balance_id('HI_PREM_EE_ASG_YTD');
2852 -- Bug 7164589
2853 g_balance_value_tab(29).defined_balance_id := defined_balance_id('LTCI_PREM_EE_ASG_YTD');
2854 -- End of Bug 7164589
2855 g_balance_value_tab(12).defined_balance_id := defined_balance_id('EI_PREM_ASG_YTD');
2856 g_balance_value_tab(13).defined_balance_id := defined_balance_id('NP_PREM_EE_ASG_YTD');
2857 -- Bug 6024342
2858 g_balance_value_tab(28).defined_balance_id := defined_balance_id('PENSION_PREMIUM_ASG_YTD');
2859 --
2860 g_balance_value_tab(14).defined_balance_id := defined_balance_id('ITAX_ASG_YTD_MTH');
2861 g_balance_value_tab(15).defined_balance_id := defined_balance_id('ITAX_ASG_YTD_BON');
2862 g_balance_value_tab(16).defined_balance_id := defined_balance_id('RTAX_ASG_YTD_MTH');
2863 g_balance_value_tab(17).defined_balance_id := defined_balance_id('RTAX_ASG_YTD_BON');
2864 g_balance_value_tab(18).defined_balance_id := defined_balance_id('STAX_ASG_YTD_MTH');
2865 g_balance_value_tab(19).defined_balance_id := defined_balance_id('STAX_ASG_YTD_BON');
2866 -- Bug 3201332
2867 g_balance_value_tab(20).defined_balance_id := defined_balance_id('MONTHLY_REGULAR_EARNINGS_ASG_YTD');
2868 -- Added for Bug3021585
2869 -- Bug 4750653
2870 g_balance_value_tab(21).defined_balance_id := defined_balance_id('CORPORATE_PENSION_ASG_YTD');
2871 -- End of Bug 4750653
2872 -- Bug 5756699
2873 g_balance_value_tab(22).defined_balance_id := defined_balance_id('ADDITIONAL_OTHER_NON_TAXABLE_EARNINGS_ASG_YTD_MTH');
2874 g_balance_value_tab(23).defined_balance_id := defined_balance_id('ADDITIONAL_OTHER_NON_TAXABLE_EARNINGS_ASG_YTD_BON');
2875 -- Bug 6012258
2876 g_balance_value_tab(24).defined_balance_id := defined_balance_id('STOCK_PURCHASE_OPTION_EXECUTION_EARNING_ASG_YTD_MTH');
2877 g_balance_value_tab(25).defined_balance_id := defined_balance_id('STOCK_PURCHASE_OPTION_EXECUTION_EARNING_ASG_YTD_BON');
2878 g_balance_value_tab(26).defined_balance_id := defined_balance_id('RESEARCH_PAYMENT_ASG_YTD_MTH');
2879 g_balance_value_tab(27).defined_balance_id := defined_balance_id('RESEARCH_PAYMENT_ASG_YTD_BON');
2880 --End of Bug 6012258
2881 -- End of Bug 5756699
2882 g_tax_adj_balance_tab(1).defined_balance_id := defined_balance_id('INCOME_TAX_ADJUSTMENT_ASG_RUN');
2883 g_tax_adj_balance_tab(2).defined_balance_id := defined_balance_id('RESIDENT_TAX_ADJUSTMENT_ASG_RUN');
2884 g_tax_adj_balance_tab(3).defined_balance_id := defined_balance_id('SPECIAL_TAX_ADJUSTMENT_ASG_RUN');
2885 ------------------------------------------------------------------------
2886 -- Element Type ID and Input Value ID
2887 ------------------------------------------------------------------------
2888 g_tax := element('TAX');
2889 g_itax_adj := element('ITAX_ADJ');
2890 g_rtax_adj := element('RTAX_ADJ');
2891 g_stax_adj := element('STAX_ADJ');
2892 ------------------------------------------------------------------------
2893 -- User Entity ID
2894 ------------------------------------------------------------------------
2895 -- Load all Extract-type db items.
2896 ------------------------------------------------------------------------
2897 select
2898 distinct
2899 u.user_entity_id,
2900 u.user_entity_name
2901 bulk collect into
2902 l_user_entity_id_tbl,
2903 l_user_entity_name_tbl
2904 from ff_user_entities u,
2905 pay_report_format_items_f i
2906 where i.report_type = 'YEA'
2907 and i.report_qualifier = 'KR'
2908 and u.user_entity_id = i.user_entity_id;
2909 --
2910 g_user_entity_id.non_resident_flag := user_entity_id('X_YEA_NON_RESIDENT_FLAG');
2911 g_user_entity_id.foreign_residency_flag := user_entity_id('X_YEA_FOREIGN_RESIDENCY_FLAG'); -- Bug 6615356
2912 g_user_entity_id.fw_fixed_tax_rate := user_entity_id('X_FW_FIX_TAX_RATE_ELIGIBILITY'); -- 3546993
2913 g_user_entity_id.cur_taxable_mth := user_entity_id('X_YEA_CUR_TAXABLE_MTH');
2914 g_user_entity_id.cur_taxable_bon := user_entity_id('X_YEA_CUR_TAXABLE_BON');
2915 g_user_entity_id.cur_sp_irreg_bonus := user_entity_id('X_YEA_CUR_SP_IRREG_BONUS');
2916 g_user_entity_id.cur_taxable := user_entity_id('X_YEA_CUR_TAXABLE');
2917 g_user_entity_id.prev_taxable_mth := user_entity_id('X_YEA_PREV_TAXABLE_MTH');
2918 g_user_entity_id.prev_taxable_bon := user_entity_id('X_YEA_PREV_TAXABLE_BON');
2919 g_user_entity_id.prev_sp_irreg_bonus := user_entity_id('X_YEA_PREV_SP_IRREG_BONUS');
2920 g_user_entity_id.prev_stck_pur_opt_exec_earn := user_entity_id('X_YEA_PREV_STCK_PUR_OPT_EXEC_EARN'); -- Bug 6024432
2921 g_user_entity_id.prev_taxable := user_entity_id('X_YEA_PREV_TAXABLE');
2922 g_user_entity_id.taxable_mth := user_entity_id('X_YEA_TAXABLE_MTH');
2923 g_user_entity_id.taxable_bon := user_entity_id('X_YEA_TAXABLE_BON');
2924 g_user_entity_id.sp_irreg_bonus := user_entity_id('X_YEA_SP_IRREG_BONUS');
2925 -- Bug 6012258
2926 g_user_entity_id.cur_stck_pur_opt_exec_earn := user_entity_id('X_YEA_STOCK_PUR_OPT_EXEC_EARN');
2927 g_user_entity_id.research_payment := user_entity_id('X_YEA_RESEARCH_PAYMENT');
2928 --
2929 g_user_entity_id.stck_pur_opt_exec_earn := user_entity_id('X_YEA_TOT_STCK_PUR_OPT_EXEC_EARN'); -- Bug 6024432
2930 g_user_entity_id.taxable := user_entity_id('X_YEA_TAXABLE');
2931 g_user_entity_id.non_taxable_ovs := user_entity_id('X_YEA_NON_TAXABLE_OVS');
2932 g_user_entity_id.non_taxable_ovt := user_entity_id('X_YEA_NON_TAXABLE_OVT');
2933 g_user_entity_id.non_taxable_oth := user_entity_id('X_YEA_NON_TAXABLE_OTH');
2934 g_user_entity_id.non_taxable := user_entity_id('X_YEA_NON_TAXABLE');
2935 g_user_entity_id.foreign_worker_income_exem := user_entity_id('X_YEA_FOREIGN_WORKER_INCOME_EXEM'); -- 3546993
2936 g_user_entity_id.basic_income_exem := user_entity_id('X_YEA_BASIC_INCOME_EXEM');
2937 g_user_entity_id.taxable_income := user_entity_id('X_YEA_TAXABLE_INCOME');
2938 g_user_entity_id.ee_tax_exem := user_entity_id('X_YEA_EE_TAX_EXEM');
2939 g_user_entity_id.dpnt_spouse_flag := user_entity_id('X_YEA_DPNT_SPOUSE_FLAG');
2940 g_user_entity_id.dpnt_spouse_tax_exem := user_entity_id('X_YEA_DPNT_SPOUSE_TAX_EXEM');
2941 g_user_entity_id.num_of_aged_dpnts := user_entity_id('X_YEA_NUM_OF_AGED_DPNTS');
2942 g_user_entity_id.num_of_adult_dpnts := user_entity_id('X_YEA_NUM_OF_ADULT_DPNTS');
2943 g_user_entity_id.num_of_underaged_dpnts := user_entity_id('X_YEA_NUM_OF_UNDERAGED_DPNTS');
2944 g_user_entity_id.num_of_dpnts := user_entity_id('X_YEA_NUM_OF_DPNTS');
2945 g_user_entity_id.dpnt_tax_exem := user_entity_id('X_YEA_DPNT_TAX_EXEM');
2946 g_user_entity_id.num_of_ageds := user_entity_id('X_YEA_NUM_OF_AGEDS');
2947 -- Bug 3172960, 3637372
2948 g_user_entity_id.num_of_super_ageds := user_entity_id('X_YEA_NUM_OF_SUPER_AGEDS');
2949 -- Bug 6705170
2950 g_user_entity_id.num_of_new_born_adopted := user_entity_id('X_YEA_NUM_OF_NEW_BORN_ADOPTED');
2951 g_user_entity_id.new_born_adopted_tax_exem := user_entity_id('X_YEA_NEW_BORN_ADOPTED_TAX_EXEM');
2952 --
2953 -- Bug 6784288
2954 g_user_entity_id.num_of_addtl_child := user_entity_id('X_YEA_NUM_OF_ADDTL_CHILD');
2955 --
2956 g_user_entity_id.aged_tax_exem := user_entity_id('X_YEA_AGED_TAX_EXEM');
2957 g_user_entity_id.num_of_disableds := user_entity_id('X_YEA_NUM_OF_DISABLEDS');
2958 g_user_entity_id.disabled_tax_exem := user_entity_id('X_YEA_DISABLED_TAX_EXEM');
2959 g_user_entity_id.female_ee_flag := user_entity_id('X_YEA_FEMALE_EE_FLAG');
2960 g_user_entity_id.female_ee_tax_exem := user_entity_id('X_YEA_FEMALE_EE_TAX_EXEM');
2961 g_user_entity_id.num_of_children := user_entity_id('X_YEA_NUM_OF_CHILDREN');
2962 g_user_entity_id.child_tax_exem := user_entity_id('X_YEA_CHILD_TAX_EXEM');
2963 -- Bug 5756690
2964 g_user_entity_id.addl_child_tax_exem := user_entity_id('X_YEA_ADDITIONAL_CHILD_TAX_EXEM');
2965 g_user_entity_id.supp_tax_exem := user_entity_id('X_YEA_SUPP_TAX_EXEM');
2966 g_user_entity_id.hi_prem := user_entity_id('X_YEA_HI_PREM');
2967 g_user_entity_id.hi_prem_tax_exem := user_entity_id('X_YEA_HI_PREM_TAX_EXEM');
2968 -- Bug 7164589
2969 g_user_entity_id.long_term_ins_prem := user_entity_id('X_YEA_LTCI_PREM');
2970 g_user_entity_id.long_term_ins_prem_tax_exem := user_entity_id('X_YEA_LTCI_PREM_TAX_EXEM');
2971 -- End of Bug 7164589
2972 g_user_entity_id.ei_prem := user_entity_id('X_YEA_EI_PREM');
2973 g_user_entity_id.ei_prem_tax_exem := user_entity_id('X_YEA_EI_PREM_TAX_EXEM');
2974 g_user_entity_id.pers_ins_name := user_entity_id('X_YEA_PERS_INS_NAME');
2975 g_user_entity_id.pers_ins_prem := user_entity_id('X_YEA_PERS_INS_PREM');
2976 g_user_entity_id.pers_ins_prem_tax_exem := user_entity_id('X_YEA_PERS_INS_PREM_TAX_EXEM');
2977 g_user_entity_id.disabled_ins_prem := user_entity_id('X_YEA_DISABLED_INS_PREM');
2978 g_user_entity_id.disabled_ins_prem_tax_exem := user_entity_id('X_YEA_DISABLED_INS_PREM_TAX_EXEM');
2979 g_user_entity_id.ins_prem_tax_exem := user_entity_id('X_YEA_INS_PREM_TAX_EXEM');
2980 g_user_entity_id.med_exp := user_entity_id('X_YEA_MED_EXP');
2981 g_user_entity_id.med_exp_disabled := user_entity_id('X_YEA_MED_EXP_DISABLED');
2982 g_user_entity_id.med_exp_aged := user_entity_id('X_YEA_MED_EXP_AGED');
2983 -- Bug 3172960, 3637372
2984 g_user_entity_id.med_exp_emp := user_entity_id('X_YEA_MED_EXP_EMP');
2985 g_user_entity_id.max_med_exp_tax_exem := user_entity_id('X_YEA_MAX_MED_EXP_TAX_EXEM');
2986 g_user_entity_id.med_exp_tax_exem := user_entity_id('X_YEA_MED_EXP_TAX_EXEM');
2987 g_user_entity_id.ee_educ_exp := user_entity_id('X_YEA_EE_EDUC_EXP');
2988 g_user_entity_id.spouse_educ_exp := user_entity_id('X_YEA_SPOUSE_EDUC_EXP');
2989 g_user_entity_id.disabled_educ_exp := user_entity_id('X_YEA_DISABLED_EDUC_EXP');
2990 g_user_entity_id.dpnt_educ_exp := user_entity_id('X_YEA_DPNT_EDUC_EXP');
2991 g_user_entity_id.educ_exp_tax_exem := user_entity_id('X_YEA_EDUC_EXP_TAX_EXEM');
2992 g_user_entity_id.housing_saving_type := user_entity_id('X_YEA_HOUSING_SAVING_TYPE');
2993 g_user_entity_id.housing_saving := user_entity_id('X_YEA_HOUSING_SAVING');
2994 g_user_entity_id.housing_purchase_date := user_entity_id('X_YEA_HOUSING_PURCHASE_DATE');
2995 g_user_entity_id.housing_loan_date := user_entity_id('X_YEA_HOUSING_LOAN_DATE');
2996 g_user_entity_id.housing_loan_repay := user_entity_id('X_YEA_HOUSING_LOAN_REPAY');
2997 g_user_entity_id.lt_housing_loan_date := user_entity_id('X_YEA_LT_HOUSING_LOAN_DATE');
2998 g_user_entity_id.lt_housing_loan_interest_repay := user_entity_id('X_YEA_LT_HOUSING_LOAN_INTEREST_REPAY');
2999 g_user_entity_id.lt_housing_loan_date_1 := user_entity_id('X_YEA_LT_HOUSING_LOAN_DATE_1');
3000 g_user_entity_id.lt_housing_loan_intr_repay_1 := user_entity_id('X_YEA_LT_HOUSING_LOAN_INTEREST_REPAY_1');
3001 g_user_entity_id.max_housing_exp_tax_exem := user_entity_id('X_YEA_MAX_HOUSING_EXP_TAX_EXEM');
3002 g_user_entity_id.housing_exp_tax_exem := user_entity_id('X_YEA_HOUSING_EXP_TAX_EXEM');
3003 g_user_entity_id.donation1 := user_entity_id('X_YEA_DONATION1');
3004 g_user_entity_id.political_donation1 := user_entity_id('X_YEA_POLITICAL_DONATION1');
3005 g_user_entity_id.political_donation2 := user_entity_id('X_YEA_POLITICAL_DONATION2');
3006 g_user_entity_id.political_donation3 := user_entity_id('X_YEA_POLITICAL_DONATION3');
3007 g_user_entity_id.donation1_tax_exem := user_entity_id('X_YEA_DONATION1_TAX_EXEM');
3008 g_user_entity_id.donation2 := user_entity_id('X_YEA_DONATION2');
3009 g_user_entity_id.donation3 := user_entity_id('X_YEA_DONATION3');
3010 g_user_entity_id.max_donation2_tax_exem := user_entity_id('X_YEA_MAX_DONATION2_TAX_EXEM');
3011 g_user_entity_id.max_donation3_tax_exem := user_entity_id('X_YEA_MAX_DONATION3_TAX_EXEM');
3012 g_user_entity_id.donation2_tax_exem := user_entity_id('X_YEA_DONATION2_TAX_EXEM');
3013 g_user_entity_id.donation3_tax_exem := user_entity_id('X_YEA_DONATION3_TAX_EXEM');
3014 g_user_entity_id.donation_tax_exem := user_entity_id('X_YEA_DONATION_TAX_EXEM');
3015 g_user_entity_id.marriage_exemption := user_entity_id('X_YEA_MARRIAGE_EXEMPTION');
3016 g_user_entity_id.funeral_exemption := user_entity_id('X_YEA_FUNERAL_EXEMPTION');
3017 g_user_entity_id.relocation_exemption := user_entity_id('X_YEA_RELOCATION_EXEMPTION');
3018 g_user_entity_id.marr_fun_relo_exemption := user_entity_id('X_YEA_MARR_FUN_RELO_EXEMPTION');
3019 g_user_entity_id.sp_tax_exem := user_entity_id('X_YEA_SP_TAX_EXEM');
3020 g_user_entity_id.std_sp_tax_exem := user_entity_id('X_YEA_STD_SP_TAX_EXEM');
3021 g_user_entity_id.np_prem := user_entity_id('X_YEA_NP_PREM');
3022 g_user_entity_id.np_prem_tax_exem := user_entity_id('X_YEA_NP_PREM_TAX_EXEM');
3023 -- Bug 6024342
3024 g_user_entity_id.pen_prem := user_entity_id('X_YEA_PEN_PREM');
3025 --
3026 g_user_entity_id.taxable_income2 := user_entity_id('X_YEA_TAXABLE_INCOME2');
3027 g_user_entity_id.pers_pension_prem := user_entity_id('X_YEA_PERS_PENSION_PREM');
3028 g_user_entity_id.pers_pension_prem_tax_exem := user_entity_id('X_YEA_PERS_PENSION_PREM_TAX_EXEM');
3029 -- Bug 4750653
3030 g_user_entity_id.corp_pension_prem := user_entity_id('X_YEA_CORP_PENSION_PREM');
3031 g_user_entity_id.corp_pension_prem_tax_exem := user_entity_id('X_YEA_CORP_PENSION_PREM_TAX_EXEM');
3032 -- end of Bug 4750653
3033 g_user_entity_id.pers_pension_saving := user_entity_id('X_YEA_PERS_PENSION_SAVING');
3034 g_user_entity_id.pers_pension_saving_tax_exem := user_entity_id('X_YEA_PERS_PENSION_SAVING_TAX_EXEM');
3035 g_user_entity_id.invest_partner_fin1 := user_entity_id('X_YEA_INVEST_PARTNER_FIN1');
3036 g_user_entity_id.invest_partner_fin2 := user_entity_id('X_YEA_INVEST_PARTNER_FIN2');
3037 g_user_entity_id.invest_partner_fin_tax_exem := user_entity_id('X_YEA_INVEST_PARTNER_FIN_TAX_EXEM');
3038 -- Bug 6895093
3039 g_user_entity_id.small_bus_install := user_entity_id('X_YEA_SMALL_BUS_INSTALL');
3040 g_user_entity_id.small_bus_install_exem := user_entity_id('X_YEA_SMALL_BUS_INSTALL_EXEM');
3041 -- End of Bug 6895093
3042 g_user_entity_id.credit_card_exp := user_entity_id('X_YEA_CREDIT_CARD_EXP');
3043 g_user_entity_id.credit_card_exp_tax_exem := user_entity_id('X_YEA_CREDIT_CARD_EXP_TAX_EXEM');
3044 g_user_entity_id.emp_stk_own_contri := user_entity_id('X_YEA_EMP_STK_OWN_CONTRI');
3045 g_user_entity_id.emp_stk_own_contri_tax_exem := user_entity_id('X_YEA_EMP_STK_OWN_CONTRI_TAX_EXEM');
3046 g_user_entity_id.taxation_base := user_entity_id('X_YEA_TAXATION_BASE');
3047 g_user_entity_id.calc_tax := user_entity_id('X_YEA_CALC_TAX');
3048 g_user_entity_id.basic_tax_break := user_entity_id('X_YEA_BASIC_TAX_BREAK');
3049 g_user_entity_id.housing_loan_interest_repay := user_entity_id('X_YEA_HOUSING_LOAN_INTEREST_REPAY');
3050 g_user_entity_id.housing_exp_tax_break := user_entity_id('X_YEA_HOUSING_EXP_TAX_BREAK');
3051 g_user_entity_id.stock_saving := user_entity_id('X_YEA_STOCK_SAVING');
3052 g_user_entity_id.stock_saving_tax_break := user_entity_id('X_YEA_STOCK_SAVING_TAX_BREAK');
3053 g_user_entity_id.lt_stock_saving1 := user_entity_id('X_YEA_LT_STOCK_SAVING1');
3054 g_user_entity_id.lt_stock_saving2 := user_entity_id('X_YEA_LT_STOCK_SAVING2');
3055 g_user_entity_id.lt_stock_saving_tax_break := user_entity_id('X_YEA_LT_STOCK_SAVING_TAX_BREAK');
3056 g_user_entity_id.ovstb_tax_paid_date := user_entity_id('X_YEA_OVSTB_TAX_PAID_DATE');
3057 g_user_entity_id.ovstb_territory_code := user_entity_id('X_YEA_OVSTB_TERRITORY_CODE');
3058 g_user_entity_id.ovstb_currency_code := user_entity_id('X_YEA_OVSTB_CURRENCY_CODE');
3059 g_user_entity_id.ovstb_taxable := user_entity_id('X_YEA_OVSTB_TAXABLE');
3060 g_user_entity_id.ovstb_taxable_subj_tax_break := user_entity_id('X_YEA_OVSTB_TAXABLE_SUBJ_TAX_BREAK');
3061 g_user_entity_id.ovstb_tax_break_rate := user_entity_id('X_YEA_OVSTB_TAX_BREAK_RATE');
3062 g_user_entity_id.ovstb_tax_foreign_currency := user_entity_id('X_YEA_OVSTB_TAX_FOREIGN_CURRENCY');
3063 g_user_entity_id.ovstb_tax := user_entity_id('X_YEA_OVSTB_TAX');
3064 g_user_entity_id.ovstb_application_date := user_entity_id('X_YEA_OVSTB_APPLICATION_DATE');
3065 g_user_entity_id.ovstb_submission_date := user_entity_id('X_YEA_OVSTB_SUBMISSION_DATE');
3066 g_user_entity_id.ovs_tax_break := user_entity_id('X_YEA_OVS_TAX_BREAK');
3067 g_user_entity_id.total_tax_break := user_entity_id('X_YEA_TOTAL_TAX_BREAK');
3068 g_user_entity_id.fwtb_immigration_purpose := user_entity_id('X_YEA_FWTB_IMMIGRATION_PURPOSE');
3069 g_user_entity_id.fwtb_contract_date := user_entity_id('X_YEA_FWTB_CONTRACT_DATE');
3070 g_user_entity_id.fwtb_expiry_date := user_entity_id('X_YEA_FWTB_EXPIRY_DATE');
3071 g_user_entity_id.fwtb_application_date := user_entity_id('X_YEA_FWTB_APPLICATION_DATE');
3072 g_user_entity_id.fwtb_submission_date := user_entity_id('X_YEA_FWTB_SUBMISSION_DATE');
3073 g_user_entity_id.foreign_worker_tax_break1 := user_entity_id('X_YEA_FOREIGN_WORKER_TAX_BREAK1');
3074 g_user_entity_id.foreign_worker_tax_break2 := user_entity_id('X_YEA_FOREIGN_WORKER_TAX_BREAK2');
3075 g_user_entity_id.foreign_worker_tax_break := user_entity_id('X_YEA_FOREIGN_WORKER_TAX_BREAK');
3076 g_user_entity_id.annual_itax := user_entity_id('X_YEA_ANNUAL_ITAX');
3077 g_user_entity_id.annual_rtax := user_entity_id('X_YEA_ANNUAL_RTAX');
3078 g_user_entity_id.annual_stax := user_entity_id('X_YEA_ANNUAL_STAX');
3079 g_user_entity_id.prev_itax := user_entity_id('X_YEA_PREV_ITAX');
3080 g_user_entity_id.prev_rtax := user_entity_id('X_YEA_PREV_RTAX');
3081 g_user_entity_id.prev_stax := user_entity_id('X_YEA_PREV_STAX');
3082 g_user_entity_id.cur_itax := user_entity_id('X_YEA_CUR_ITAX');
3083 g_user_entity_id.cur_rtax := user_entity_id('X_YEA_CUR_RTAX');
3084 g_user_entity_id.cur_stax := user_entity_id('X_YEA_CUR_STAX');
3085 g_user_entity_id.itax_adj := user_entity_id('X_YEA_ITAX_ADJ');
3086 g_user_entity_id.rtax_adj := user_entity_id('X_YEA_RTAX_ADJ');
3087 g_user_entity_id.stax_adj := user_entity_id('X_YEA_STAX_ADJ');
3088 -- Bug 3966549
3089 g_user_entity_id.don_tax_break2004 := user_entity_id('X_YEA_DONATION_TAX_BREAK') ;
3090 -- End of 3966549
3091 g_user_entity_id.cash_receipt_expense := user_entity_id('X_YEA_CASH_RECEIPT_EXPENSE'); -- 4738717
3092 -- Bug 6630135
3093 g_user_entity_id.tot_med_exp_cards := user_entity_id('X_YEA_TOT_MED_EXP_CARDS') ;
3094 g_user_entity_id.med_exp_paid_not_inc_med_exem := user_entity_id('X_YEA_MED_EXP_PAID_NOT_INC_MED_EXEM') ;
3095 -- End of 6630135
3096 --Bug 6716401
3097 g_user_entity_id.double_exem_amt := user_entity_id('X_YEA_DOUBLE_EXEM_AMT') ;
3098 --End 6716401
3099 end;
3100 end pay_kr_yea_pkg;