1 package pay_kr_paykryrs_pkg AUTHID CURRENT_USER as
2 /* $Header: paykryrs.pkh 120.16.12020000.3 2012/12/11 19:29:28 mdubasi ship $ */
3 ------------------------------------------------------------------------
4 -- Bug 9079478
5 type t_varchar2_tbl is table of varchar2(255) index by binary_integer;
6 type t_number_tbl is table of number index by binary_integer;
7
8 type t_element is record(
9 element_type_id number,
10 input_value_id_tbl hr_entry.number_table,
11 input_value_name_tbl t_varchar2_tbl);
12
13 function get_balance_value( p_assignment_id number,
14 p_year number,
15 p_ytd_balance_name varchar2) return number;
16
17 procedure data(
18 p_assignment_id in number,
19 p_year in number,
20 ----------------Education Exp--------------------
21 p_edu_p_count out nocopy number,
22 p_edu_h_count out nocopy number,
23 p_edu_u_count out nocopy number,
24 p_edu_d_count out nocopy number,
25 p_edu_exp_p out nocopy number,
26 p_edu_exp_h out nocopy number,
27 p_edu_exp_u out nocopy number,
28 p_edu_exp_d out nocopy number,
29 p_edu_exp_total out nocopy number,
30 --------------- Housing Saving Total--------------
31 p_hou_exp out nocopy number,
32 p_resident_type out nocopy varchar2, -- Bug 9079478
33 --------------- Working Period ------------------
34 p_emp_start_date out nocopy date,
35 p_period_end_date out nocopy date,
36 --------------- Special Exems --------------------
37 p_pers_ins_prem out nocopy number,
38 p_dis_ins_prem out nocopy number,
39 p_gen_med_exp out nocopy number,
40 p_emp_aged_dis_med_exp out nocopy number,
41 p_med_exp_card out nocopy number, -- Bug 4336742
42 p_prv_hou_loan_repay out nocopy number, -- Bug 13080235
43 p_hou_loan_repay out nocopy number,
44 p_lt_hou_int_repay out nocopy number,
45 p_lt_1500_won_limit out nocopy number, -- Bug 9079478
46 p_donations out nocopy number,
47 p_emp_educ out nocopy number,
48 p_sp_prem_hi_sub out nocopy number,
49 p_sp_prem_ltci_sub out nocopy number, -- Bug 7644535
50 p_sp_prem_ei_sub out nocopy number,
51 p_100p_donation out nocopy number,
52 p_50p_donation out nocopy number,
53 p_30p_donation out nocopy number,
54 p_15p_donation out nocopy number, -- Bug 7508706
55 p_10p_donation out nocopy number,
56 p_political_donation out nocopy number,
57 p_political_100p out nocopy number, -- Bug 9079478
58 p_marr_reloc_funr_exem out nocopy number,
59 p_lt_hou_int_repay_gt_15 out nocopy number,
60 --------------- Other Exems --------------------
61 p_pers_pen_prem out nocopy number,
62 p_pers_pen_sav out nocopy number,
63 p_inv_part_fin2 out nocopy number,
64 p_inv_part_fin3 out nocopy number, -- Bug 9079478
65 p_emp_crd_exp out nocopy number,
66 p_dep_crd_exp out nocopy number,
67 p_emp_stk_opt out nocopy number,
68 p_tuition_giro out nocopy number,
69 p_cash_receipt out nocopy number, -- Bug 4336742
70 p_other_exem_np_prem out nocopy number,
71 p_other_exem_pen_prem out nocopy number, -- Bug 6655323
72 p_small_bus_install out nocopy number, -- Bug 7508706
73 p_company_related_exp out nocopy number, -- Bug 7615517
74 p_long_term_stck_fund_1yr out nocopy number, -- Bug 7615517
75 p_long_term_stck_fund_2yr out nocopy number, -- Bug 7615517
76 p_long_term_stck_fund_3yr out nocopy number, -- Bug 7615517
77 --------------- Tax Breaks -----------------------
78 p_hou_loan_int_repay_break out nocopy number,
79 p_lt_stk_sav1 out nocopy number,
80 p_lt_stk_sav2 out nocopy number,
81 --------------- FW Info --------------------------
82 p_emp_fw_exp out nocopy number,
83 --------------- Ovs Tax Break --------------------
84 p_ovs_tax_paid_fc out nocopy number,
85 p_ovs_tax_paid_lc out nocopy number,
86 p_ovs_country out nocopy varchar2,
87 p_ovs_paid_date out nocopy varchar2,
88 p_ovs_submit_date out nocopy varchar2,
89 p_ovs_location out nocopy varchar2,
90 p_ovs_period out nocopy varchar2,
91 p_ovs_title out nocopy varchar2,
92 --------------- FW Tax Break ---------------------
93 p_immigration_purpose out nocopy varchar2,
94 p_fw_contract_date out nocopy varchar2,
95 p_fw_expiry_date out nocopy varchar2,
96 p_fw_application_date out nocopy varchar2,
97 p_fw_submit_date out nocopy varchar2,
98 -----------------TAX Reductions-------------------
99 p_smb_start_date out nocopy varchar2,
100 p_smb_end_date out nocopy varchar2,
101 p_teach_application_date out nocopy varchar2,
102 p_teach_submit_date out nocopy varchar2,
103 p_marine_confirm_date out nocopy varchar2,
104 p_marine_submit_date out nocopy varchar2,
105 p_tradition_market out nocopy number,
106 p_lt_fixed_interest out nocopy number,
107 p_lt_others out nocopy number,
108 p_inv_fin_2011 out nocopy number,
109 p_inv_fin_2012_ordinary out nocopy number,
110 p_inv_fin_2012_venture out nocopy number,
111 --------------- Prev Employer Info ---------------
112 p_total_hi_prem out nocopy number,
113 p_total_ei_prem out nocopy number,
114 p_total_np_prem out nocopy number,
115 p_total_pen_prem out nocopy number, /* Bug 6655323 */
116 p_total_sep_pension out nocopy number, /* Bug 7508706 */
117 p_total_ltci_prem out nocopy number, /* Bug 7644535 */
118 --------------- Balance Values -------------------
119 p_np_prem_main out nocopy number,
120 p_pen_prem_main out nocopy number, /* Bug 6655323 */
121 p_ei_prem_main out nocopy number,
122 p_hi_prem_main out nocopy number,
123 p_ltci_prem_main out nocopy number, /* Bug 7644535 */
124 p_corp_pension out nocopy number,
125 p_emp_ins_included out nocopy varchar2,
126 p_emp_med_included out nocopy varchar2,
127 p_emp_edu_included out nocopy varchar2,
128 p_emp_card_included out nocopy varchar2,
129 p_ovs_earn out nocopy number, /* Bug 9079478 */
130 p_smb_income_exem out nocopy number,
131 -------------- Bug 10184055 ---------------------
132 p_tax_law_don out nocopy number,
133 p_inv_part_fin_tot out nocopy number,
134 p_sep_pen_Details_count out nocopy number,
135 p_pen_sav_Details_count out nocopy number,
136 p_hou_Sav_details_count out nocopy number,
137 p_lt_stck_Details_count out nocopy number,
138 p_tot_pen_saving_details_cnt out nocopy number,
139 p_house_holder_flag out nocopy varchar2,
140 p_nationality_type out nocopy varchar2,
141 p_tax_reduction_period out nocopy varchar2,
142 p_housing_rent out nocopy number,
143 p_hou_sub_saving out nocopy number,
144 p_emp_hou_pro_saving out nocopy number,
145 p_lt_hou_pro_saving out nocopy number,
146 p_total_hou_saving out nocopy number,
147 p_emp_crd_drt_exp out nocopy number,
148 p_dep_crd_drt_exp out nocopy number,
149 p_sep_pen_priority1 out nocopy number,
150 p_sep_pen_priority2 out nocopy number,
151 p_pen_sav_priority1 out nocopy number,
152 p_pen_sav_priority2 out nocopy number,
153 p_hou_sav_priority1 out nocopy number,
154 p_hou_sav_priority2 out nocopy number,
155 p_hou_sav_priority3 out nocopy number,
156 p_lt_stck_sav_priority1 out nocopy number,
157 p_lt_stck_sav_priority2 out nocopy number,
158 p_lt_stck_sav_priority3 out nocopy number,
159 p_sep_pen_type1 out nocopy varchar2,
160 p_sep_pen_fin_inst1 out nocopy varchar2,
161 p_sep_pen_acc_number1 out nocopy varchar2,
162 p_sep_pen_cont_amt1 out nocopy number,
163 p_sep_pen_type2 out nocopy varchar2,
164 p_sep_pen_fin_inst2 out nocopy varchar2,
165 p_sep_pen_acc_number2 out nocopy varchar2,
166 p_sep_pen_cont_amt2 out nocopy number,
167 p_pen_sav_type1 out nocopy varchar2,
168 p_pen_sav_fin_inst1 out nocopy varchar2,
169 p_pen_sav_acc_number1 out nocopy varchar2,
170 p_pen_sav_cont_amt1 out nocopy number,
171 p_pen_sav_type2 out nocopy varchar2,
172 p_pen_sav_fin_inst2 out nocopy varchar2,
173 p_pen_sav_acc_number2 out nocopy varchar2,
174 p_pen_sav_cont_amt2 out nocopy number,
175 p_hou_sav_type1 out nocopy varchar2,
176 p_hou_sav_fin_inst1 out nocopy varchar2,
177 p_hou_sav_acc_number1 out nocopy varchar2,
178 p_hou_sav_cont_amt1 out nocopy number,
179 p_hou_sav_type2 out nocopy varchar2,
180 p_hou_sav_fin_inst2 out nocopy varchar2,
181 p_hou_sav_acc_number2 out nocopy varchar2,
182 p_hou_sav_cont_amt2 out nocopy number,
183 p_hou_sav_type3 out nocopy varchar2,
184 p_hou_sav_fin_inst3 out nocopy varchar2,
185 p_hou_sav_acc_number3 out nocopy varchar2,
186 p_hou_sav_cont_amt3 out nocopy number,
187 p_lt_stck_sav_type1 out nocopy varchar2,
188 p_lt_stck_sav_fin_inst1 out nocopy varchar2,
189 p_lt_stck_sav_acc_number1 out nocopy varchar2,
190 p_lt_stck_sav_cont_amt1 out nocopy number,
191 p_lt_stck_sav_type2 out nocopy varchar2,
192 p_lt_stck_sav_fin_inst2 out nocopy varchar2,
193 p_lt_stck_sav_acc_number2 out nocopy varchar2,
194 p_lt_stck_sav_cont_amt2 out nocopy number,
195 p_lt_stck_sav_type3 out nocopy varchar2,
196 p_lt_stck_sav_fin_inst3 out nocopy varchar2,
197 p_lt_stck_sav_acc_number3 out nocopy varchar2,
198 p_lt_stck_sav_cont_amt3 out nocopy number
199 );
200
201 procedure EMP_EXPENSE_DETAILS (p_emp_assignment_id in number
202 ,p_year in number
203 ,p_emp_ins_exp_nts out nocopy number
204 ,p_emp_ins_exp_oth out nocopy number
205 ,p_emp_med_exp_nts out nocopy number
206 ,p_emp_med_exp_oth out nocopy number
207 ,p_emp_edu_exp_nts out nocopy number
208 ,p_emp_edu_exp_oth out nocopy number
209 ,p_emp_card_exp_nts out nocopy number
210 ,p_emp_card_exp_oth out nocopy number
211 ,p_emp_cash_exp_nts out nocopy number
212 ,p_emp_don_exp_nts out nocopy number
213 ,p_emp_don_exp_oth out nocopy number
214 ,p_emp_direct_exp_nts out nocopy number
215 ,p_emp_direct_exp_oth out nocopy number
216 ,p_emp_tm_exp_nts out nocopy number
217 ,p_emp_tm_exp_oth out nocopy number
218 ,p_emp_tution_exp_oth out nocopy number);
219
220 /* Bug 5856504 Modified parameter name in the function EMP_EXPENSE_DETAILS */
221 function EMP_EXPENSE_DETAILS (p_assignment_id in number
222 ,p_year in number
223 ,p_emp_ins_exp_nts out nocopy number
224 ,p_emp_ins_exp_oth out nocopy number
225 ,p_emp_med_exp_nts out nocopy number
226 ,p_emp_med_exp_oth out nocopy number
227 ,p_emp_edu_exp_nts out nocopy number
228 ,p_emp_edu_exp_oth out nocopy number
229 ,p_emp_card_exp_nts out nocopy number
230 ,p_emp_card_exp_oth out nocopy number
231 ,p_emp_cash_exp_nts out nocopy number
232 ,p_emp_don_exp_nts out nocopy number
233 ,p_emp_don_exp_oth out nocopy number
234 ,p_emp_direct_exp_nts out nocopy number
235 ,p_emp_direct_exp_oth out nocopy number
236 ,p_emp_tm_exp_nts out nocopy number
237 ,p_emp_tm_exp_oth out nocopy number
238 ,p_emp_tution_exp_oth out nocopy number) return number;
239
240 /* Bug 9079478 - Function to get the element type id and input values id of elements */
241 function element(p_element_name in varchar2) return t_element;
242
243
244 end pay_kr_paykryrs_pkg;