[Home] [Help]
PACKAGE BODY: APPS.PAY_JP_CUSTOM_PKG
Source
4 TYPE itax_rec IS RECORD(
1 PACKAGE BODY PAY_JP_CUSTOM_PKG AS
2 /* $Header: pyjpcust.pkb 120.4.12020000.6 2013/03/09 18:23:26 dduvvuri ship $ */
3 -- Element Type IDs and Input Value IDs for Result Value and Entry Value.
5 sal_elm_id NUMBER,
6 sal_iv_id NUMBER,
7 bon_elm_id NUMBER,
8 bon_iv_id NUMBER,
9 sp_bon_elm_id NUMBER,
10 sp_bon_iv_id NUMBER,
11 yea_elm_id NUMBER,
12 yea_category_iv_id NUMBER,
13 yea_iv_id NUMBER,
14 gen_elm_id NUMBER,
15 gen_iv_id NUMBER,
16 gen_nr_iv_id NUMBER,
17 non_res_elm_id NUMBER,
18 non_res_date_iv_id NUMBER,
19 res_date_iv_id NUMBER,
20 ci_prem_sal_ee_elm_id NUMBER,
21 ci_prem_sal_ee_iv_id NUMBER,
22 ci_prem_sal_ee_elm_nonres_id NUMBER,
23 ci_prem_sal_ee_iv_nonres_id NUMBER,
24 ci_prem_sal_er_elm_id NUMBER,
25 ci_prem_sal_er_iv_id NUMBER,
26 ci_prem_sal_ee_elm_n_id NUMBER,
27 ci_prem_sal_ee_iv_n_id NUMBER,
28 ci_prem_sal_ee_elm_nonres_n_id NUMBER,
29 ci_prem_sal_ee_iv_nonres_n_id NUMBER,
30 ci_prem_sal_er_elm_n_id NUMBER,
31 ci_prem_sal_er_iv_n_id NUMBER,
32 ci_prem_bon_ee_elm_id NUMBER,
33 ci_prem_bon_ee_iv_id NUMBER,
34 ci_prem_bon_ee_elm_nonres_id NUMBER,
35 ci_prem_bon_ee_iv_nonres_id NUMBER,
36 ci_prem_bon_er_elm_id NUMBER,
37 ci_prem_bon_er_iv_id NUMBER);
38 -- Input Value IDs for Entry Value.
39 TYPE iv_rec IS RECORD(
40 itax_org NUMBER,
41 hi_org NUMBER,
42 wp_org NUMBER,
43 wpf_org NUMBER,
44 ui_org NUMBER,
45 wai_org NUMBER,
46 gen_ltax_district_code NUMBER,
47 term_ltax_district_code NUMBER,
48 ui_category NUMBER,
49 wai_category NUMBER);
50 -- Defined Balance IDs.
51 TYPE bal_rec IS RECORD(
52 -- Taxable Amount
53 sal_taxable_sal NUMBER,
54 sal_taxable_mat NUMBER,
55 sal_taxable_sal_nr NUMBER,
56 sal_taxable_mat_nr NUMBER,
57 bon_taxable_sal NUMBER,
58 bon_taxable_mat NUMBER,
59 bon_taxable_sal_nr NUMBER,
60 bon_taxable_mat_nr NUMBER,
61 sp_bon_taxable_sal NUMBER,
62 sp_bon_taxable_mat NUMBER,
63 sp_bon_taxable_sal_nr NUMBER,
64 sp_bon_taxable_mat_nr NUMBER,
65 term_taxable_sal NUMBER,
66 term_taxable_mat NUMBER,
67 term_taxable_sal_nr NUMBER,
68 term_taxable_mat_nr NUMBER,
69 term_taxable_sal_spc NUMBER,
70 term_taxable_mat_spc NUMBER,
71 -- Health Ins Premium
72 sal_hi_prem_ee NUMBER,
73 bon_hi_prem_ee NUMBER,
74 gen_hi_prem_er NUMBER,
75 -- Welfare Pension Ins Premium
76 sal_wp_prem_ee NUMBER,
77 bon_wp_prem_ee NUMBER,
78 gen_wp_prem_er NUMBER,
79 -- Welfare Pension Fund Premium
80 sal_wpf_prem_ee NUMBER,
81 bon_wpf_prem_ee NUMBER, -- added for bug 3803871
82 gen_wpf_prem_er NUMBER,
83 -- Unemployment Ins Premium
84 sal_ui_prem_ee NUMBER,
85 sal_ui_sal NUMBER,
86 bon_ui_prem_ee NUMBER,
87 bon_ui_sal NUMBER,
88 sp_bon_ui_prem_ee NUMBER,
89 sp_bon_ui_sal NUMBER,
90 -- Work Accident Ins Premium
91 sal_wai_sal NUMBER,
92 bon_wai_sal NUMBER,
93 sp_bon_wai_sal NUMBER,
94 -- Income Tax
95 sal_itax NUMBER,
96 bon_itax NUMBER,
97 sp_bon_itax NUMBER,
98 term_itax NUMBER,
99 yea_itax NUMBER,
100 -- Local Tax
101 sal_ltax NUMBER,
102 gen_ltax_lumpsum NUMBER,
103 term_ltax NUMBER,
104 term_ltax_income NUMBER,
105 term_ltax_shi NUMBER,
106 term_ltax_to NUMBER,
107 -- Defined Contribution Premum
108 sal_mutual_aid NUMBER,
109 bon_mutual_aid NUMBER,
110 sp_bon_mutual_aid NUMBER,
111 -- Disaster Tax Reduction
112 disaster_tax_reduction NUMBER);
113 TYPE name_tl IS RECORD(
114 salary VARCHAR2(80),
115 bonus VARCHAR2(80),
116 sp_bonus VARCHAR2(80),
117 sp_bonus2 VARCHAR2(80),
118 yea VARCHAR2(80),
119 yea2 VARCHAR2(80),
120 re_yea VARCHAR2(80),
121 term VARCHAR2(80),
122 term2 VARCHAR2(80),
123 santei VARCHAR2(80),
124 geppen VARCHAR2(80),
125 na VARCHAR2(80),
126 bal_init_prefix VARCHAR2(80),
127 itax_category VARCHAR2(80),
128 yea_category VARCHAR2(80),
129 yea_category2 VARCHAR2(80),
130 non_res VARCHAR2(80),
131 non_res_date VARCHAR2(80),
132 res_date VARCHAR2(80),
133 yea_element pay_element_types_f.element_name%type,
134 reyea_element pay_element_types_f.element_name%type);
135 g_itax itax_rec;
136 g_iv iv_rec;
137 g_bal bal_rec;
138 g_name_tl name_tl;
139 g_business_group_id NUMBER;
140 g_legislation_code VARCHAR2(2);
141 g_owner CONSTANT VARCHAR2(30) := 'PAYJPPRT';
142 --------------------------------------------------------------
143 PROCEDURE SETUP_GLOBALS(
144 --------------------------------------------------------------
148 BEGIN
145 p_business_group_id IN NUMBER)
146 IS
147 l_legislation_code VARCHAR2(2);
149 hr_utility.set_location('pay_jp_custom_pkg.set_globals',10);
150
151 if nvl(g_business_group_id,-1) <> p_business_group_id then
152 hr_utility.set_location('pay_jp_custom_pkg.set_globals',20);
153
154 -- Name Translation
155 g_name_tl.salary := fnd_message.get_string('PAY','PAY_JP_SALARY');
156 g_name_tl.bonus := fnd_message.get_string('PAY','PAY_JP_BONUS');
157 g_name_tl.sp_bonus := fnd_message.get_string('PAY','PAY_JP_SP_BONUS');
158 g_name_tl.sp_bonus2 := fnd_message.get_string('PAY','PAY_JP_SP_BON');
159 g_name_tl.yea := fnd_message.get_string('PAY','PAY_JP_YEAR_END_ADJ');
160 g_name_tl.yea2 := fnd_message.get_string('PAY','PAY_JP_YEA');
161 g_name_tl.re_yea := fnd_message.get_string('PAY','PAY_JP_RE_YEAR_END_ADJ');
162 g_name_tl.term := fnd_message.get_string('PAY','PAY_JP_TERM_PAY');
163 g_name_tl.term2 := fnd_message.get_string('PAY','PAY_JP_TERM');
164 g_name_tl.santei := fnd_message.get_string('PAY','PAY_JP_SANTEI');
165 g_name_tl.geppen := fnd_message.get_string('PAY','PAY_JP_GEPPEN');
166 g_name_tl.na := fnd_message.get_string('PAY','PAY_JP_AMBIGUOUS');
167 g_name_tl.bal_init_prefix := fnd_message.get_string('PAY','PAY_JP_INIT_PREFIX');
168 g_name_tl.itax_category := 'ITX_TYPE';
169 g_name_tl.yea_category := 'INCLUDE_FLAG';
170 g_name_tl.yea_category2 := 'INCLUDE_FLAG';
171 g_name_tl.non_res := 'NRES_FLAG';
172 g_name_tl.yea_element := 'YEA_ITX';
173 g_name_tl.reyea_element := 'REY_ITX';
174
175 l_legislation_code := hr_jp_id_pkg.legislation_code(p_business_group_id);
176 if l_legislation_code is NULL then
177 fnd_message.set_name(800,'HR_51255_PYP_INVALID_BUS_GROUP');
178 fnd_message.raise_error;
179 end if;
180
181 g_business_group_id := p_business_group_id;
182 g_legislation_code := l_legislation_code;
183
184 g_itax.sal_elm_id := hr_jp_id_pkg.element_type_id('SAL_ITX',NULL,l_legislation_code);
185 g_itax.sal_iv_id := hr_jp_id_pkg.input_value_id(g_itax.sal_elm_id,g_name_tl.itax_category);
186 g_itax.bon_elm_id := hr_jp_id_pkg.element_type_id('BON_ITX',NULL,l_legislation_code);
187 g_itax.bon_iv_id := hr_jp_id_pkg.input_value_id(g_itax.bon_elm_id,g_name_tl.itax_category);
188 g_itax.sp_bon_elm_id := hr_jp_id_pkg.element_type_id('SPB_ITX',NULL,l_legislation_code);
189 g_itax.sp_bon_iv_id := hr_jp_id_pkg.input_value_id(g_itax.sp_bon_elm_id,g_name_tl.itax_category);
190 g_itax.yea_elm_id := hr_jp_id_pkg.element_type_id('YEA_AMT_AFTER_EMP_INCOME_DCT_RSLT',NULL,l_legislation_code);
191 g_itax.yea_category_iv_id := hr_jp_id_pkg.input_value_id(g_itax.yea_elm_id,g_name_tl.yea_category);
192 g_itax.yea_iv_id := hr_jp_id_pkg.input_value_id(g_itax.yea_elm_id,g_name_tl.itax_category);
193 g_itax.gen_elm_id := hr_jp_id_pkg.element_type_id('COM_ITX_INFO',NULL,l_legislation_code);
194 g_itax.gen_iv_id := hr_jp_id_pkg.input_value_id(g_itax.gen_elm_id,g_name_tl.itax_category);
195 g_itax.gen_nr_iv_id := hr_jp_id_pkg.input_value_id(g_itax.gen_elm_id,g_name_tl.non_res);
196
197 g_itax.non_res_elm_id := hr_jp_id_pkg.element_type_id('COM_NRES_INFO',NULL,l_legislation_code);
198 g_name_tl.non_res_date := 'NRES_START_DATE';
199 g_name_tl.res_date := 'PROJECTED_RES_DATE';
200 g_itax.non_res_date_iv_id := hr_jp_id_pkg.input_value_id(g_itax.non_res_elm_id,g_name_tl.non_res_date);
201 g_itax.res_date_iv_id := hr_jp_id_pkg.input_value_id(g_itax.non_res_elm_id,g_name_tl.res_date);
202
203
204 g_iv.itax_org := hr_jp_id_pkg.input_value_id('COM_ITX_INFO','WITHHOLD_AGENT',NULL,l_legislation_code);
205 g_iv.hi_org := hr_jp_id_pkg.input_value_id('COM_SI_INFO','HI_LOCATION',NULL,l_legislation_code);
206 g_iv.wp_org := hr_jp_id_pkg.input_value_id('COM_SI_INFO','WP_LOCATION',NULL,l_legislation_code);
207 g_iv.wpf_org := hr_jp_id_pkg.input_value_id('COM_SI_INFO','WPF_LOCATION',NULL,l_legislation_code);
208 g_iv.ui_org := hr_jp_id_pkg.input_value_id('COM_LI_INFO','EI_LOCATION',NULL,l_legislation_code);
209 g_iv.wai_org := hr_jp_id_pkg.input_value_id('COM_LI_INFO','WAI_LOCATION',NULL,l_legislation_code);
210 g_iv.gen_ltax_district_code := hr_jp_id_pkg.input_value_id('COM_LTX_INFO','MUNICIPAL_CODE',NULL,l_legislation_code);
211 g_iv.term_ltax_district_code := hr_jp_id_pkg.input_value_id('TRM_LTX_SP_WITHHOLD_PROC','MUNICIPAL_CODE',NULL,l_legislation_code);
212 g_iv.ui_category := hr_jp_id_pkg.input_value_id('COM_LI_INFO','EI_TYPE',NULL,l_legislation_code);
213 g_iv.wai_category := hr_jp_id_pkg.input_value_id('COM_LI_INFO','WAI_TYPE',NULL,l_legislation_code);
214
215 -- Taxable Amount
216 g_bal.sal_taxable_sal := hr_jp_id_pkg.balance_type_id('B_SAL_TXBL_ERN_MONEY',NULL,l_legislation_code);
217 g_bal.sal_taxable_mat := hr_jp_id_pkg.balance_type_id('B_SAL_TXBL_ERN_KIND',NULL,l_legislation_code);
218 g_bal.sal_taxable_sal_nr := hr_jp_id_pkg.balance_type_id('B_SAL_TXBL_ERN_MONEY_NRES',NULL,l_legislation_code);
219 g_bal.sal_taxable_mat_nr := hr_jp_id_pkg.balance_type_id('B_SAL_TXBL_ERN_KIND_NRES',NULL,l_legislation_code);
220 g_bal.bon_taxable_sal := hr_jp_id_pkg.balance_type_id('B_BON_TXBL_ERN_MONEY',NULL,l_legislation_code);
221 g_bal.bon_taxable_mat := hr_jp_id_pkg.balance_type_id('B_BON_TXBL_ERN_KIND',NULL,l_legislation_code);
222 g_bal.bon_taxable_sal_nr := hr_jp_id_pkg.balance_type_id('B_BON_TXBL_ERN_MONEY_NRES',NULL,l_legislation_code);
223 g_bal.bon_taxable_mat_nr := hr_jp_id_pkg.balance_type_id('B_BON_TXBL_ERN_KIND_NRES',NULL,l_legislation_code);
224 g_bal.sp_bon_taxable_sal := hr_jp_id_pkg.balance_type_id('B_SPB_TXBL_ERN_MONEY',NULL,l_legislation_code);
225 g_bal.sp_bon_taxable_mat := hr_jp_id_pkg.balance_type_id('B_SPB_TXBL_ERN_KIND',NULL,l_legislation_code);
226 g_bal.sp_bon_taxable_sal_nr := hr_jp_id_pkg.balance_type_id('B_SPB_TXBL_ERN_MONEY_NRES',NULL,l_legislation_code);
227 g_bal.sp_bon_taxable_mat_nr := hr_jp_id_pkg.balance_type_id('B_SPB_TXBL_ERN_KIND_NRES',NULL,l_legislation_code);
231 g_bal.term_taxable_mat_nr := hr_jp_id_pkg.balance_type_id('B_TRM_TXBL_ERN_KIND_NRES',NULL,l_legislation_code);
228 g_bal.term_taxable_sal := hr_jp_id_pkg.balance_type_id('B_TRM_TXBL_ERN_MONEY',NULL,l_legislation_code);
229 g_bal.term_taxable_mat := hr_jp_id_pkg.balance_type_id('B_TRM_TXBL_ERN_KIND',NULL,l_legislation_code);
230 g_bal.term_taxable_sal_nr := hr_jp_id_pkg.balance_type_id('B_TRM_TXBL_ERN_MONEY_NRES',NULL,l_legislation_code);
232 g_bal.term_taxable_sal_spc := hr_jp_id_pkg.balance_type_id('B_TRM_TXBL_ERN_MONEY_SPC_EXC',NULL,l_legislation_code);
233 g_bal.term_taxable_mat_spc := hr_jp_id_pkg.balance_type_id('B_TRM_TXBL_ERN_KIND_SPC_EXC',NULL,l_legislation_code);
234 -- Health Ins Premium
235 g_bal.sal_hi_prem_ee := hr_jp_id_pkg.balance_type_id('B_SAL_HI_PREM',NULL,l_legislation_code);
236 g_bal.bon_hi_prem_ee := hr_jp_id_pkg.balance_type_id('B_BON_HI_PREM',NULL,l_legislation_code);
237 g_bal.gen_hi_prem_er := hr_jp_id_pkg.balance_type_id('B_COM_HI_PREM_ER',NULL,l_legislation_code);
238 -- Care Ins Premium
239 g_itax.ci_prem_sal_ee_elm_id := hr_jp_id_pkg.element_type_id('SAL_CI_PREM_EE',NULL,l_legislation_code);
240 g_itax.ci_prem_sal_ee_iv_id := hr_jp_id_pkg.input_value_id('SAL_CI_PREM_EE','Pay Value',NULL,l_legislation_code);
241 g_itax.ci_prem_sal_ee_elm_nonres_id := hr_jp_id_pkg.element_type_id('SAL_CI_PREM_EE_NRES',NULL,l_legislation_code);
242 g_itax.ci_prem_sal_ee_iv_nonres_id := hr_jp_id_pkg.input_value_id('SAL_CI_PREM_EE_NRES','Pay Value',NULL,l_legislation_code);
243 g_itax.ci_prem_sal_er_elm_id := hr_jp_id_pkg.element_type_id('SAL_CI_PREM_ER',NULL,l_legislation_code);
244 g_itax.ci_prem_sal_er_iv_id := hr_jp_id_pkg.input_value_id('SAL_CI_PREM_ER','Pay Value',NULL,l_legislation_code);
245 g_itax.ci_prem_sal_ee_elm_n_id := hr_jp_id_pkg.element_type_id('SAL_CI_PREM_EE_TRM',NULL,l_legislation_code);
246 g_itax.ci_prem_sal_ee_iv_n_id := hr_jp_id_pkg.input_value_id('SAL_CI_PREM_EE_TRM','Pay Value',NULL,l_legislation_code);
247 g_itax.ci_prem_sal_ee_elm_nonres_n_id := hr_jp_id_pkg.element_type_id('SAL_CI_PREM_EE_NRES_TRM',NULL,l_legislation_code);
248 g_itax.ci_prem_sal_ee_iv_nonres_n_id := hr_jp_id_pkg.input_value_id('SAL_CI_PREM_EE_NRES_TRM','Pay Value',NULL,l_legislation_code);
249 g_itax.ci_prem_sal_er_elm_n_id := hr_jp_id_pkg.element_type_id('SAL_CI_PREM_ER_TRM',NULL,l_legislation_code);
250 g_itax.ci_prem_sal_er_iv_n_id := hr_jp_id_pkg.input_value_id('SAL_CI_PREM_ER_TRM','Pay Value',NULL,l_legislation_code);
251 g_itax.ci_prem_bon_ee_elm_id := hr_jp_id_pkg.element_type_id('BON_CI_PREM_EE',NULL,l_legislation_code);
252 g_itax.ci_prem_bon_ee_iv_id := hr_jp_id_pkg.input_value_id('BON_CI_PREM_EE','Pay Value',NULL,l_legislation_code);
253 g_itax.ci_prem_bon_ee_elm_nonres_id := hr_jp_id_pkg.element_type_id('BON_CI_PREM_EE_NRES',NULL,l_legislation_code);
254 g_itax.ci_prem_bon_ee_iv_nonres_id := hr_jp_id_pkg.input_value_id('BON_CI_PREM_EE_NRES','Pay Value',NULL,l_legislation_code);
255 g_itax.ci_prem_bon_er_elm_id := hr_jp_id_pkg.element_type_id('BON_CI_PREM_ER',NULL,l_legislation_code);
256 g_itax.ci_prem_bon_er_iv_id := hr_jp_id_pkg.input_value_id('BON_CI_PREM_ER','Pay Value',NULL,l_legislation_code);
257 -- Welfare Pension Ins Premium
258 g_bal.sal_wp_prem_ee := hr_jp_id_pkg.balance_type_id('B_SAL_WP_PREM',NULL,l_legislation_code);
259 g_bal.bon_wp_prem_ee := hr_jp_id_pkg.balance_type_id('B_BON_WP_PREM',NULL,l_legislation_code);
260 g_bal.gen_wp_prem_er := hr_jp_id_pkg.balance_type_id('B_COM_WP_PREM_ER',NULL,l_legislation_code);
261 -- Welfare Pension Fund Premium
262 g_bal.sal_wpf_prem_ee := hr_jp_id_pkg.balance_type_id('B_SAL_WPF_PREM',NULL,l_legislation_code);
263 -- added for bug 3803871
264 g_bal.bon_wpf_prem_ee := hr_jp_id_pkg.balance_type_id('B_BON_WPF_PREM',NULL,l_legislation_code);
265 g_bal.gen_wpf_prem_er := hr_jp_id_pkg.balance_type_id('B_COM_WPF_PREM_ER',NULL,l_legislation_code);
266 -- Unemployment Ins Premium
267 g_bal.sal_ui_prem_ee := hr_jp_id_pkg.balance_type_id('B_SAL_EI_PREM',NULL,l_legislation_code);
268 g_bal.sal_ui_sal := hr_jp_id_pkg.balance_type_id('B_SAL_ERN_SUBJ_EI',NULL,l_legislation_code);
269 g_bal.bon_ui_prem_ee := hr_jp_id_pkg.balance_type_id('B_BON_EI_PREM',NULL,l_legislation_code);
270 g_bal.bon_ui_sal := hr_jp_id_pkg.balance_type_id('B_BON_ERN_SUBJ_EI',NULL,l_legislation_code);
271 g_bal.sp_bon_ui_prem_ee := hr_jp_id_pkg.balance_type_id('B_SPB_EI_PREM',NULL,l_legislation_code);
272 g_bal.sp_bon_ui_sal := hr_jp_id_pkg.balance_type_id('B_SPB_ERN_SUBJ_EI',NULL,l_legislation_code);
273 -- Work Accident Ins Premium
274 g_bal.sal_wai_sal := hr_jp_id_pkg.balance_type_id('B_SAL_ERN_SUBJ_WAI',NULL,l_legislation_code);
275 g_bal.bon_wai_sal := hr_jp_id_pkg.balance_type_id('B_BON_ERN_SUBJ_WAI',NULL,l_legislation_code);
276 g_bal.sp_bon_wai_sal := hr_jp_id_pkg.balance_type_id('B_SPB_ERN_SUBJ_WAI',NULL,l_legislation_code);
277 -- Income Tax
278 g_bal.sal_itax := hr_jp_id_pkg.balance_type_id('B_SAL_ITX',NULL,l_legislation_code);
279 g_bal.bon_itax := hr_jp_id_pkg.balance_type_id('B_BON_ITX',NULL,l_legislation_code);
280 g_bal.sp_bon_itax := hr_jp_id_pkg.balance_type_id('B_SPB_ITX',NULL,l_legislation_code);
281 g_bal.term_itax := hr_jp_id_pkg.balance_type_id('B_TRM_ITX',NULL,l_legislation_code);
282 g_bal.yea_itax := hr_jp_id_pkg.balance_type_id('B_YEA_TAX_PAY',NULL,l_legislation_code);
283 -- Local Tax
284 g_bal.sal_ltax := hr_jp_id_pkg.balance_type_id('B_SAL_LTX',NULL,l_legislation_code);
285 g_bal.gen_ltax_lumpsum := hr_jp_id_pkg.balance_type_id('B_COM_LTX_LUMP_SUM_WITHHOLD',NULL,l_legislation_code);
286 g_bal.term_ltax := hr_jp_id_pkg.balance_type_id('B_TRM_LTX_SP_WITHHOLD_TAX',NULL,l_legislation_code);
287 g_bal.term_ltax_income := hr_jp_id_pkg.balance_type_id('B_TRM_LTX_SP_WITHHOLD_TRM_INCOME',NULL,l_legislation_code);
288 g_bal.term_ltax_shi := hr_jp_id_pkg.balance_type_id('B_TRM_LTX_SP_WITHHOLD_MUNICIPAL_TAX',NULL,l_legislation_code);
289 g_bal.term_ltax_to := hr_jp_id_pkg.balance_type_id('B_TRM_LTX_SP_WITHHOLD_PREFECTURAL_TAX',NULL,l_legislation_code);
290 -- Mutual Aid
294 -- Disaster Tax Reduction
291 g_bal.sal_mutual_aid := hr_jp_id_pkg.balance_type_id('B_SAL_SMALL_COMPANY_MUTUAL_AID_PREM',NULL,l_legislation_code);
292 g_bal.bon_mutual_aid := hr_jp_id_pkg.balance_type_id('B_BON_SMALL_COMPANY_MUTUAL_AID_PREM',NULL,l_legislation_code);
293 g_bal.sp_bon_mutual_aid := hr_jp_id_pkg.balance_type_id('B_SPB_SMALL_COMPANY_MUTUAL_AID_PREM',NULL,l_legislation_code);
295 g_bal.disaster_tax_reduction := hr_jp_id_pkg.balance_type_id('B_YEA_GRACE_ITX',NULL,l_legislation_code);
296 end if;
297
298 hr_utility.set_location('pay_jp_custom_pkg.set_globals',30);
299 END SETUP_GLOBALS;
300
301 -----------------------------------------------------------------------
302 PROCEDURE VALIDATE_RECORD(
303 -----------------------------------------------------------------------
304 p_value IN value_rec,
305 p_assignment_id IN number,
306 p_locking_assignment_action_id IN NUMBER,
307 p_locked_assignment_action_id IN NUMBER,
308 p_action_status OUT NOCOPY VARCHAR2,
309 p_message OUT NOCOPY VARCHAR2)
310 IS
311 -- Bug 16322188
312 --l_asg_num varchar2(10);
313 l_asg_num per_all_assignments_f.assignment_number%TYPE;
314 BEGIN
315 hr_utility.set_location('pay_jp_custom_pkg.validate_record',10);
316
317 p_action_status := 'C';
318 select distinct assignment_number into l_asg_num from per_all_assignments_f where assignment_id = p_assignment_id;
319 -- Write your own validation here.
320 -- 1) Health Insurance Premium Validation
321 if not(p_value.hi_prem_ee = 0 or p_value.hi_prem_er = 0 or p_value.ci_prem_ee = 0 or p_value.ci_prem_er = 0) and (p_value.hi_org_id is NULL) then
322 p_action_status := 'I';
323 fnd_message.set_name('PAY','PAY_JP_INVALID_HI_UNION');
324 fnd_message.set_token('ASG_NUM',l_asg_num);
325 fnd_message.set_token('ASG_ID',p_assignment_id);
326 fnd_message.set_token('ARCH_ASG_ID',p_locking_assignment_action_id);
327 fnd_message.set_token('RUN_ASG_ID',p_locked_assignment_action_id);
328 p_message := fnd_message.get;
329 return;
330 end if;
331
332 -- 2) Welfare Pension Insurance Premium Validation
333 if not(p_value.wp_prem_ee = 0 or p_value.wp_prem_er = 0) and (p_value.wp_org_id is NULL) then
334 p_action_status := 'I';
335 fnd_message.set_name('PAY','PAY_JP_INVALID_WP_UNION');
336 fnd_message.set_token('ASG_NUM',l_asg_num);
337 fnd_message.set_token('ASG_ID',p_assignment_id);
338 fnd_message.set_token('ARCH_ASG_ID',p_locking_assignment_action_id);
339 fnd_message.set_token('RUN_ASG_ID',p_locked_assignment_action_id);
340 p_message := fnd_message.get;
341 return;
342 end if;
343
344 -- 3) Welfare Pension Fund Premium Validation
345 if not(p_value.wpf_prem_ee = 0 or p_value.wpf_prem_er = 0) and (p_value.wpf_org_id is NULL) then
346 p_action_status := 'I';
347 fnd_message.set_name('PAY','PAY_JP_INVALID_WP_FUND');
348 fnd_message.set_token('ASG_NUM',l_asg_num);
349 fnd_message.set_token('ASG_ID',p_assignment_id);
350 fnd_message.set_token('ARCH_ASG_ID',p_locking_assignment_action_id);
351 fnd_message.set_token('RUN_ASG_ID',p_locked_assignment_action_id);
352 p_message := fnd_message.get;
353 return;
354 end if;
355
356 -- 4) Unemployment Insurance Premium Validation
357 if not(p_value.ui_prem_ee = 0) and (nvl(p_value.ui_category,'E') = 'E' or p_value.ui_org_id is NULL) then
358 p_action_status := 'I';
359 fnd_message.set_name('PAY','PAY_JP_INVALID_UI_UNION');
360 fnd_message.set_token('ASG_NUM',l_asg_num);
361 fnd_message.set_token('ASG_ID',p_assignment_id);
362 fnd_message.set_token('ARCH_ASG_ID',p_locking_assignment_action_id);
363 fnd_message.set_token('RUN_ASG_ID',p_locked_assignment_action_id);
364 p_message := fnd_message.get;
365 return;
366 end if;
367
368 -- 5) Income Tax Validation
369 if not(p_value.taxable_sal_amt = 0 and p_value.taxable_mat_amt = 0 and p_value.itax = 0 and p_value.itax_adjustment = 0)
370 and (p_value.itax_org_id is NULL or nvl(p_value.itax_category,'E') = 'E') then
371 p_action_status := 'I';
372 fnd_message.set_name('PAY','PAY_JP_INVALID_ITAX_SWOT');
373 fnd_message.set_token('ASG_NUM',l_asg_num);
374 fnd_message.set_token('ASG_ID',p_assignment_id);
375 fnd_message.set_token('ARCH_ASG_ID',p_locking_assignment_action_id);
376 fnd_message.set_token('RUN_ASG_ID',p_locked_assignment_action_id);
377 p_message := fnd_message.get;
378 return;
379 end if;
380
381 -- 6) Local Tax Validation
382 if (p_value.sp_ltax = 0
383 and (not(p_value.ltax = 0 and p_value.ltax_lumpsum = 0)
384 and (p_value.itax_org_id is NULL or p_value.ltax_district_code is NULL)))
385 or
386 (p_value.sp_ltax <> 0
387 and (p_value.sp_ltax_district_code is NULL
388 or p_value.itax_org_id is NULL
389 or (p_value.ltax_lumpsum <> 0 and p_value.ltax_district_code is NULL))) then
390 --if not(p_value.ltax = 0 and p_value.ltax_lumpsum = 0 and p_value.sp_ltax = 0)
391 --and (p_value.itax_org_id is NULL or p_value.ltax_district_code is NULL) then
392 p_action_status := 'I';
393 fnd_message.set_name('PAY','PAY_JP_INVALID_LTAX_SWOT');
394 fnd_message.set_token('ASG_NUM',l_asg_num);
395 fnd_message.set_token('ASG_ID',p_assignment_id);
396 fnd_message.set_token('ARCH_ASG_ID',p_locking_assignment_action_id);
397 fnd_message.set_token('RUN_ASG_ID',p_locked_assignment_action_id);
398 p_message := fnd_message.get;
399 return;
400 end if;
401
402 hr_utility.set_location('pay_jp_custom_pkg.validate_record',20);
403 END VALIDATE_RECORD;
404 --------------------------------------------------------------
408 p_salary_category OUT NOCOPY VARCHAR2,
405 PROCEDURE GET_ITAX_CATEGORY(
406 --------------------------------------------------------------
407 p_assignment_action_id IN NUMBER,
409 p_itax_category OUT NOCOPY VARCHAR2,
410 p_itax_yea_category OUT NOCOPY VARCHAR2)
411 IS
412 l_business_group_id NUMBER;
413 l_effective_date DATE;
414 -- l_date_earned DATE;
415 l_assignment_id NUMBER;
416 l_element_set_id number;
417 l_element_type_id number;
418 l_element_name pay_element_types_f.element_name%type;
419 l_element_set_name PAY_ELEMENT_SETS.ELEMENT_SET_NAME%TYPE;
420 l_legislation_code pay_element_sets.legislation_code%type;
421 l_classification_name pay_element_classifications.classification_name%type;
422 l_salary_category VARCHAR2(30) := NULL;
423 l_itax_category VARCHAR2(30) := NULL;
424 l_itax_yea_category VARCHAR2(30) := NULL;
425 l_nonresident VARCHAR2(30) := NULL;
426 l_non_res_date DATE;
427 l_res_date DATE;
428 /*
429 CURSOR csr_element_set_name(p_assignment_action_id IN NUMBER) IS
430 select pes.element_set_name,
431 ppa.business_group_id,
432 ppa.date_earned,
433 paa.assignment_id,
434 ppa.effective_date
435 from pay_element_sets pes,
436 pay_payroll_actions ppa,
437 pay_assignment_actions paa
438 where paa.assignment_action_id=p_assignment_action_id
439 and ppa.payroll_action_id=paa.payroll_action_id
440 and pes.element_set_id(+)=ppa.element_set_id;
441 */
442 CURSOR csr_classification_name(p_assignment_action_id IN NUMBER) IS
443 select pec.balance_initialization_flag,
444 pec.classification_name,
445 pet.element_name,
446 pec.legislation_code
447 from pay_element_classifications pec,
448 pay_element_types_f pet,
449 pay_run_results prr,
450 pay_payroll_actions ppa,
451 pay_assignment_actions paa
452 where paa.assignment_action_id=p_assignment_action_id
453 and ppa.payroll_action_id=paa.payroll_action_id
454 and prr.assignment_action_id=paa.assignment_action_id
455 and pet.element_type_id=prr.element_type_id
456 and ppa.effective_date
457 between pet.effective_start_date and pet.effective_end_date
458 and pec.classification_id=pet.classification_id;
459 CURSOR csr_itax_yea_category(p_assignment_action_id IN NUMBER) IS
460 select prrv.result_value
461 from pay_input_values_f piv,
462 pay_element_types_f pet,
463 pay_run_result_values prrv,
464 pay_run_results prr,
465 pay_payroll_actions ppa2,
466 pay_assignment_actions paa2,
467 pay_payroll_actions ppa,
468 pay_assignment_actions paa
469 where paa.assignment_action_id=p_assignment_action_id
470 and ppa.payroll_action_id=paa.payroll_action_id
471 and ppa.action_type='I'
472 and paa2.assignment_id=paa.assignment_id
473 and ppa2.payroll_action_id=paa2.payroll_action_id
474 and ppa2.effective_date=ppa.effective_date
475 and ppa2.action_type='I'
476 and prr.assignment_action_id=paa2.assignment_action_id
477 and prrv.run_result_id=prr.run_result_id
478 and prrv.result_value is not NULL
479 and pet.element_type_id=prr.element_type_id
480 and ppa2.effective_date
481 between pet.effective_start_date and pet.effective_end_date
482 and (pet.element_name = g_name_tl.bal_init_prefix || g_name_tl.yea || '2'
483 or (pet.element_name = 'INI_YEA2' and pet.legislation_code = 'JP'))
484 and piv.input_value_id=prrv.input_value_id
485 and ppa2.effective_date
486 between piv.effective_start_date and piv.effective_end_date
487 and piv.name=g_name_tl.yea_category2;
488 --
489 function get_salary_category(
490 p_classification_name in varchar2,
491 p_legislation_code in varchar2 default g_legislation_code) return varchar2
492 is
493 l_sal_category varchar2(30) := 'NA';
494 begin
495 if (p_classification_name like '% ' || g_name_tl.salary || ' %'
496 or (p_classification_name like 'SAL_%' and p_legislation_code = 'JP')) then
497 l_sal_category := 'SALARY';
498 elsif (p_classification_name like '% ' || g_name_tl.bonus || ' %'
499 or (p_classification_name like 'BON_%' and p_legislation_code = 'JP')) then
500 l_sal_category := 'BONUS';
501 elsif (p_classification_name like '% ' || g_name_tl.sp_bonus || ' %'
502 or (p_classification_name like 'SPB_%' and p_legislation_code = 'JP')) then
503 l_sal_category := 'SP_BONUS';
504 elsif (p_classification_name like '% ' || g_name_tl.yea || '%'
505 or (p_classification_name like 'YEA_%' and p_legislation_code = 'JP')) then
506 l_sal_category := 'YEA';
507 elsif (p_classification_name like '% ' || g_name_tl.term || ' %'
508 or (p_classification_name like 'TRM_%' and p_legislation_code = 'JP')) then
509 l_sal_category := 'TERM';
510 elsif (p_classification_name like '% ' || g_name_tl.santei
511 or (p_classification_name = 'SAN' and p_legislation_code = 'JP')) then
512 l_sal_category := 'SANTEI';
513 elsif (p_classification_name like '% ' || g_name_tl.geppen
514 or (p_classification_name = 'GEP' and p_legislation_code = 'JP')) then
515 l_sal_category := 'GEPPEN';
516 end if;
517 --
518 return l_sal_category;
519 end get_salary_category;
520 BEGIN
521 hr_utility.set_location('pay_jp_custom_pkg.get_itax_category',10);
522 /*
523 open csr_element_set_name(p_assignment_action_id);
524 fetch csr_element_set_name into l_element_set_name,l_business_group_id,l_date_earned,l_assignment_id,l_effective_date;
525 if csr_element_set_name%NOTFOUND then
529 close csr_element_set_name;
526 close csr_element_set_name;
527 return;
528 end if;
530 */
531 --
532 begin
533 select ppa.business_group_id,
534 ppa.effective_date,
535 -- ppa.date_earned,
536 paa.assignment_id,
537 ppa.element_set_id,
538 ppa.element_type_id
539 into l_business_group_id,
540 l_effective_date,
541 -- l_date_earned,
542 l_assignment_id,
543 l_element_set_id,
544 l_element_type_id
545 from pay_assignment_actions paa,
546 pay_payroll_actions ppa
547 where paa.assignment_action_id = p_assignment_action_id
548 and ppa.payroll_action_id = paa.payroll_action_id;
549 exception
550 when others then
551 return;
552 end;
553 --
554 -- Initialize IDs.
555 --
556 setup_globals(l_business_group_id);
557 --
558 -- Identify "Salary Category" and "YEA Category"
559 --
560 -- if l_element_set_name is not NULL then
561 if l_element_set_id is not NULL then
562 hr_utility.set_location('pay_jp_custom_pkg.get_itax_category',20);
563 --
564 select element_set_name,
565 legislation_code
566 into l_element_set_name, l_legislation_code
567 from pay_element_sets
568 where element_set_id = l_element_set_id;
569 --
570 if (l_element_set_name like g_name_tl.salary || '%'
571 or (l_element_set_name = 'SAL' and l_legislation_code = 'JP')) then
572 l_salary_category := 'SALARY';
573 elsif (l_element_set_name like g_name_tl.bonus || '%'
574 or (l_element_set_name = 'BON' and l_legislation_code = 'JP')) then
575 l_salary_category := 'BONUS';
576 elsif (l_element_set_name like g_name_tl.sp_bonus || '%'
577 or (l_element_set_name = 'SPB' and l_legislation_code = 'JP')) then
578 l_salary_category := 'SP_BONUS';
579 elsif (l_element_set_name like g_name_tl.yea || '%'
580 or (l_element_set_name = 'YEA' and l_legislation_code = 'JP')) then
581 l_salary_category := 'YEA';
582 elsif (l_element_set_name like g_name_tl.re_yea || '%'
583 or (l_element_set_name = 'REY' and l_legislation_code = 'JP')) then
584 l_salary_category := 'RE_YEA';
585 elsif (l_element_set_name like g_name_tl.term || '%'
586 or (l_element_set_name = 'TRM' and l_legislation_code = 'JP')) then
587 l_salary_category := 'TERM';
588 elsif (l_element_set_name like g_name_tl.santei || '%'
589 or (l_element_set_name = 'SAN' and l_legislation_code = 'JP')) then
590 l_salary_category := 'SANTEI';
591 elsif (l_element_set_name like g_name_tl.geppen || '%'
592 or (l_element_set_name = 'GEP' and l_legislation_code = 'JP')) then
593 l_salary_category := 'GEPPEN';
594 else
595 l_salary_category := 'NA';
596 end if;
597 else
598 hr_utility.set_location('pay_jp_custom_pkg.get_itax_category',30);
599 --
600 if l_element_type_id is not null then
601 select pet.element_name,
602 pec.classification_name,
603 pec.legislation_code
604 into l_element_name,
605 l_classification_name,
606 l_legislation_code
607 from pay_element_types_f pet,
608 pay_element_classifications pec
609 where pet.element_type_id = l_element_type_id
610 and l_effective_date
611 between pet.effective_start_date and pet.effective_end_date
612 and pec.classification_id = pet.classification_id;
613 --
614 -- This is special code branch for YEA by Balance Adjustment.
615 --
616 if l_element_name = g_name_tl.yea_element then
617 l_salary_category := 'YEA';
618 elsif l_element_name = g_name_tl.reyea_element then
619 l_salary_category := 'RE_YEA';
620 else
621 l_salary_category := get_salary_category(l_classification_name,l_legislation_code);
622 end if;
623 --
624 else
625 -- This routine can't judge whether YEA and RE-YEA.
626 -- So it's not recommended to use QuickPay Run and Balance Adjustment.
627 for l_rec in csr_classification_name(p_assignment_action_id) loop
628 if nvl(l_rec.balance_initialization_flag,'N') = 'Y' then
629 hr_utility.set_location('pay_jp_custom_pkg.get_itax_category',40);
630 --
631 if (l_rec.element_name like g_name_tl.bal_init_prefix || g_name_tl.salary || '%'
632 and (l_rec.element_name like 'INI_SAL%' and l_rec.legislation_code = 'JP')) then
633 l_salary_category := 'SALARY';
634 exit;
635 elsif (l_rec.element_name like g_name_tl.bal_init_prefix || g_name_tl.bonus || '%'
636 and (l_rec.element_name like 'INI_BON%' and l_rec.legislation_code = 'JP')) then
637 l_salary_category := 'BONUS';
638 exit;
639 elsif (l_rec.element_name like g_name_tl.bal_init_prefix || g_name_tl.sp_bonus2 || '%'
640 and (l_rec.element_name like 'INI_SPB%' and l_rec.legislation_code = 'JP')) then
641 l_salary_category := 'SP_BONUS';
642 exit;
643 elsif (l_rec.element_name like g_name_tl.bal_init_prefix || g_name_tl.yea2 || '%'
644 and (l_rec.element_name like 'INI_YEA%' and l_rec.legislation_code = 'JP')) then
645 if (l_rec.element_name = g_name_tl.bal_init_prefix || g_name_tl.yea2 || '1'
646 and (l_rec.element_name = 'INI_YEA1' and l_rec.legislation_code = 'JP')) then
647 open csr_itax_yea_category(p_assignment_action_id);
648 fetch csr_itax_yea_category into l_itax_yea_category;
649 if csr_itax_yea_category%FOUND then
650 l_salary_category := 'YEA';
654 end if;
651 else
652 l_salary_category := 'NA';
653 l_itax_yea_category := NULL;
655 close csr_itax_yea_category;
656 else
657 l_salary_category := 'NA';
658 end if;
659 exit;
660 elsif (l_rec.element_name like g_name_tl.bal_init_prefix || g_name_tl.term2 || '%'
661 and (l_rec.element_name like 'INI_YEA%' and l_rec.legislation_code = 'JP')) then
662 l_salary_category := 'TERM';
663 exit;
664 elsif (l_rec.element_name like g_name_tl.bal_init_prefix || g_name_tl.santei || g_name_tl.geppen || '%'
665 and (l_rec.element_name like 'INI_SAN_GEP%' and l_rec.legislation_code = 'JP')) then
666 l_salary_category := 'SANTEI';
667 exit;
668 else
669 l_salary_category := 'NA';
670 exit;
671 end if;
672 else
673 hr_utility.set_location('pay_jp_custom_pkg.get_itax_category',50);
674 --
675 l_salary_category := get_salary_category(l_rec.classification_name, l_rec.legislation_code);
676 if l_salary_category <> 'NA' then
677 exit;
678 end if;
679 end if;
680 end loop;
681 end if;
682 end if;
683
684 -- Initialize "Income Tax Category" and "YEA Category(If not initialized.)".
685 if l_salary_category='SALARY' then
686 l_itax_category := pay_jp_balance_pkg.get_result_value_char(g_itax.sal_elm_id,g_itax.sal_iv_id,p_assignment_action_id);
687 elsif l_salary_category='BONUS' then
688 l_itax_category := pay_jp_balance_pkg.get_result_value_char(g_itax.bon_elm_id,g_itax.bon_iv_id,p_assignment_action_id);
689 elsif l_salary_category='SP_BONUS' then
690 l_itax_category := pay_jp_balance_pkg.get_result_value_char(g_itax.sp_bon_elm_id,g_itax.sp_bon_iv_id,p_assignment_action_id);
691 elsif l_salary_category in ('YEA','RE_YEA') then
692 if l_itax_yea_category is NULL then
693 l_itax_yea_category := nvl(pay_jp_balance_pkg.get_result_value_char(g_itax.yea_elm_id,g_itax.yea_category_iv_id,p_assignment_action_id),7);
694 end if;
695 --
696 if l_itax_yea_category = '5' then
697 l_itax_category := 'NON_RES';
698 else
699 --
700 -- This is short term solution for non-resident case.
701 -- Formula "KyuyoShotokuKoujogonoKingaku" should be fixed instead of this SQL.
702 --
703 if pay_jp_balance_pkg.get_entry_value_char(g_itax.gen_nr_iv_id,l_assignment_id,l_effective_date) = 'Y' then
704 l_itax_category := 'NON_RES';
705 else
706 l_itax_category := pay_jp_balance_pkg.get_result_value_char(g_itax.yea_elm_id,g_itax.yea_iv_id,p_assignment_action_id);
707 end if;
708 end if;
709 elsif l_salary_category = 'TERM' then
710 l_itax_category := NULL;
711 else
712 l_itax_category := 'NA';
713 end if;
714
715 if l_itax_category is NULL then
716 hr_utility.set_location('pay_jp_custom_pkg.get_itax_category',60);
717
718 -- l_nonresident := pay_jp_balance_pkg.get_entry_value_char(g_itax.gen_nr_iv_id,l_assignment_id,l_date_earned);
719
720 l_non_res_date := pay_jp_balance_pkg.get_entry_value_date(g_itax.non_res_date_iv_id,l_assignment_id,l_effective_date);
721 l_res_date := pay_jp_balance_pkg.get_entry_value_date(g_itax.res_date_iv_id,l_assignment_id,l_effective_date);
722
723 if l_non_res_date is not null then
724 if (l_non_res_date <= l_effective_date) and (l_effective_date < NVL(l_res_date,TO_DATE('47121231','yyyymmdd'))) then
725 l_nonresident := 'Y';
726 else
727 l_nonresident := 'N';
728 end if;
729 else
730 l_nonresident := pay_jp_balance_pkg.get_entry_value_char(g_itax.gen_nr_iv_id,l_assignment_id,l_effective_date);
731 end if;
732
733 if nvl(l_nonresident,'N') = 'Y' then
734 l_itax_category := 'NON_RES';
735 else
736 l_itax_category := nvl(pay_jp_balance_pkg.get_entry_value_char(g_itax.gen_iv_id,l_assignment_id,l_effective_date),'E');
737 end if;
738 elsif l_itax_category = 'NA' then
739 hr_utility.set_location('pay_jp_custom_pkg.get_itax_category',70);
740
741 l_itax_category := NULL;
742 end if;
743
744 p_salary_category := l_salary_category;
745 p_itax_category := l_itax_category;
746 p_itax_yea_category := l_itax_yea_category;
747
748 hr_utility.set_location('pay_jp_custom_pkg.get_itax_category',80);
749 END GET_ITAX_CATEGORY;
750
751 -----------------------------------------------------------------------
752 PROCEDURE FETCH_VALUES(
753 -----------------------------------------------------------------------
754 P_BUSINESS_GROUP_ID IN NUMBER,
755 P_ASSIGNMENT_ACTION_ID IN NUMBER,
756 P_ASSIGNMENT_ID IN NUMBER,
757 P_DATE_EARNED IN DATE,
758 P_VALUE OUT NOCOPY value_rec)
759 IS
760 l_value value_rec;
761 l_taxable_sal_spc NUMBER;
762 l_taxable_mat_spc NUMBER;
763
764 BEGIN
765 hr_utility.set_location('pay_jp_custom_pkg.get_itax_category',10);
766
767 -- Get Salary Category, Itax Category and Itax Year-End-Adjustment Process Category.
768 pay_jp_custom_pkg.get_itax_category(
769 P_ASSIGNMENT_ACTION_ID => p_assignment_action_id,
770 P_SALARY_CATEGORY => l_value.salary_category,
771 P_ITAX_CATEGORY => l_value.itax_category,
772 P_ITAX_YEA_CATEGORY => l_value.itax_yea_category);
773
774 if l_value.salary_category = 'SALARY' then
775 if l_value.itax_category = 'NON_RES' then
776 l_value.taxable_sal_amt := pay_jp_balance_pkg.get_balance_value_asg_run(g_bal.sal_taxable_sal_nr,p_assignment_action_id);
777 l_value.taxable_mat_amt := pay_jp_balance_pkg.get_balance_value_asg_run(g_bal.sal_taxable_mat_nr,p_assignment_action_id);
778 else
782 -- Disaster Tax Reduction is available only for Residents.
779 l_value.taxable_sal_amt := pay_jp_balance_pkg.get_balance_value_asg_run(g_bal.sal_taxable_sal,p_assignment_action_id);
780 l_value.taxable_mat_amt := pay_jp_balance_pkg.get_balance_value_asg_run(g_bal.sal_taxable_mat,p_assignment_action_id);
781 --
783 --
784 l_value.disaster_tax_reduction := pay_jp_balance_pkg.get_balance_value_asg_run(g_bal.disaster_tax_reduction,p_assignment_action_id);
785 end if;
786 l_value.hi_org_id := pay_jp_balance_pkg.get_entry_value_number(g_iv.hi_org,p_assignment_id,p_date_earned);
787 l_value.hi_prem_ee := pay_jp_balance_pkg.get_balance_value_asg_run(g_bal.sal_hi_prem_ee,p_assignment_action_id);
788 l_value.hi_prem_er := pay_jp_balance_pkg.get_balance_value_asg_run(g_bal.gen_hi_prem_er,p_assignment_action_id);
789 -- l_value.ci_prem_ee := nvl(pay_jp_balance_pkg.get_result_value_number(g_itax.ci_prem_sal_ee_elm_id,g_itax.ci_prem_sal_ee_iv_id,p_assignment_action_id),0)
790 -- + nvl(pay_jp_balance_pkg.get_result_value_number(g_itax.ci_prem_sal_ee_elm_nonres_id,g_itax.ci_prem_sal_ee_iv_nonres_id,p_assignment_action_id),0);
791 -- l_value.ci_prem_er := nvl(pay_jp_balance_pkg.get_result_value_number(g_itax.ci_prem_sal_er_elm_id,g_itax.ci_prem_sal_er_iv_id,p_assignment_action_id),0);
792 l_value.ci_prem_ee := nvl(pay_jp_balance_pkg.get_result_value_number(g_itax.ci_prem_sal_ee_elm_id,g_itax.ci_prem_sal_ee_iv_id,p_assignment_action_id),0)
793 + nvl(pay_jp_balance_pkg.get_result_value_number(g_itax.ci_prem_sal_ee_elm_nonres_id,g_itax.ci_prem_sal_ee_iv_nonres_id,p_assignment_action_id),0)
794 + nvl(pay_jp_balance_pkg.get_result_value_number(g_itax.ci_prem_sal_ee_elm_n_id,g_itax.ci_prem_sal_ee_iv_n_id,p_assignment_action_id),0)
795 + nvl(pay_jp_balance_pkg.get_result_value_number(g_itax.ci_prem_sal_ee_elm_nonres_n_id,g_itax.ci_prem_sal_ee_iv_nonres_n_id,p_assignment_action_id),0);
796 l_value.ci_prem_er := nvl(pay_jp_balance_pkg.get_result_value_number(g_itax.ci_prem_sal_er_elm_id,g_itax.ci_prem_sal_er_iv_id,p_assignment_action_id),0)
797 + nvl(pay_jp_balance_pkg.get_result_value_number(g_itax.ci_prem_sal_er_elm_n_id,g_itax.ci_prem_sal_er_iv_n_id,p_assignment_action_id),0);
798 l_value.wp_org_id := pay_jp_balance_pkg.get_entry_value_number(g_iv.wp_org,p_assignment_id,p_date_earned);
799 l_value.wp_prem_ee := pay_jp_balance_pkg.get_balance_value_asg_run(g_bal.sal_wp_prem_ee,p_assignment_action_id);
800 l_value.wp_prem_er := pay_jp_balance_pkg.get_balance_value_asg_run(g_bal.gen_wp_prem_er,p_assignment_action_id);
801 l_value.wpf_org_id := pay_jp_balance_pkg.get_entry_value_number(g_iv.wpf_org,p_assignment_id,p_date_earned);
802 l_value.wpf_prem_ee := pay_jp_balance_pkg.get_balance_value_asg_run(g_bal.sal_wpf_prem_ee,p_assignment_action_id);
803 l_value.wpf_prem_er := pay_jp_balance_pkg.get_balance_value_asg_run(g_bal.gen_wpf_prem_er,p_assignment_action_id);
804 l_value.ui_org_id := pay_jp_balance_pkg.get_entry_value_number(g_iv.ui_org,p_assignment_id,p_date_earned);
805 l_value.ui_category := pay_jp_balance_pkg.get_entry_value_char(g_iv.ui_category,p_assignment_id,p_date_earned);
806 l_value.ui_prem_ee := pay_jp_balance_pkg.get_balance_value_asg_run(g_bal.sal_ui_prem_ee,p_assignment_action_id);
807 l_value.ui_sal_amt := pay_jp_balance_pkg.get_balance_value_asg_run(g_bal.sal_ui_sal,p_assignment_action_id);
808 l_value.wai_org_id := pay_jp_balance_pkg.get_entry_value_number(g_iv.wai_org,p_assignment_id,p_date_earned);
809 l_value.wai_category := pay_jp_balance_pkg.get_entry_value_char(g_iv.wai_category,p_assignment_id,p_date_earned);
810 l_value.wai_sal_amt := pay_jp_balance_pkg.get_balance_value_asg_run(g_bal.sal_wai_sal,p_assignment_action_id);
811 l_value.itax_org_id := pay_jp_balance_pkg.get_entry_value_number(g_iv.itax_org,p_assignment_id,p_date_earned);
812 l_value.itax := pay_jp_balance_pkg.get_balance_value_asg_run(g_bal.sal_itax,p_assignment_action_id);
813 l_value.ltax_district_code := pay_jp_balance_pkg.get_entry_value_char(g_iv.gen_ltax_district_code,p_assignment_id,p_date_earned);
814 l_value.ltax := pay_jp_balance_pkg.get_balance_value_asg_run(g_bal.sal_ltax,p_assignment_action_id);
815 l_value.ltax_lumpsum := pay_jp_balance_pkg.get_balance_value_asg_run(g_bal.gen_ltax_lumpsum,p_assignment_action_id);
816 l_value.mutual_aid := pay_jp_balance_pkg.get_balance_value_asg_run(g_bal.sal_mutual_aid,p_assignment_action_id);
817 elsif l_value.salary_category = 'BONUS' then
818 if l_value.itax_category = 'NON_RES' then
819 l_value.taxable_sal_amt := pay_jp_balance_pkg.get_balance_value_asg_run(g_bal.bon_taxable_sal_nr,p_assignment_action_id);
820 l_value.taxable_mat_amt := pay_jp_balance_pkg.get_balance_value_asg_run(g_bal.bon_taxable_mat_nr,p_assignment_action_id);
821 else
822 l_value.taxable_sal_amt := pay_jp_balance_pkg.get_balance_value_asg_run(g_bal.bon_taxable_sal,p_assignment_action_id);
823 l_value.taxable_mat_amt := pay_jp_balance_pkg.get_balance_value_asg_run(g_bal.bon_taxable_mat,p_assignment_action_id);
824 --
825 -- Disaster Tax Reduction is available only for Residents.
826 --
827 l_value.disaster_tax_reduction := pay_jp_balance_pkg.get_balance_value_asg_run(g_bal.disaster_tax_reduction,p_assignment_action_id);
828 end if;
829 l_value.hi_org_id := pay_jp_balance_pkg.get_entry_value_number(g_iv.hi_org,p_assignment_id,p_date_earned);
830 l_value.hi_prem_ee := pay_jp_balance_pkg.get_balance_value_asg_run(g_bal.bon_hi_prem_ee,p_assignment_action_id);
831 l_value.hi_prem_er := pay_jp_balance_pkg.get_balance_value_asg_run(g_bal.gen_hi_prem_er,p_assignment_action_id);
832 l_value.ci_prem_ee := nvl(pay_jp_balance_pkg.get_result_value_number(g_itax.ci_prem_bon_ee_elm_id,g_itax.ci_prem_bon_ee_iv_id,p_assignment_action_id),0)
833 + nvl(pay_jp_balance_pkg.get_result_value_number(g_itax.ci_prem_bon_ee_elm_nonres_id,g_itax.ci_prem_bon_ee_iv_nonres_id,p_assignment_action_id),0);
837 l_value.wp_prem_er := pay_jp_balance_pkg.get_balance_value_asg_run(g_bal.gen_wp_prem_er,p_assignment_action_id);
834 l_value.ci_prem_er := nvl(pay_jp_balance_pkg.get_result_value_number(g_itax.ci_prem_bon_er_elm_id,g_itax.ci_prem_bon_er_iv_id,p_assignment_action_id),0);
835 l_value.wp_org_id := pay_jp_balance_pkg.get_entry_value_number(g_iv.wp_org,p_assignment_id,p_date_earned);
836 l_value.wp_prem_ee := pay_jp_balance_pkg.get_balance_value_asg_run(g_bal.bon_wp_prem_ee,p_assignment_action_id);
838 -- added for bug 3803871
839 l_value.wpf_org_id := pay_jp_balance_pkg.get_entry_value_number(g_iv.wpf_org,p_assignment_id,p_date_earned);
840 l_value.wpf_prem_ee := pay_jp_balance_pkg.get_balance_value_asg_run(g_bal.bon_wpf_prem_ee,p_assignment_action_id);
841 l_value.wpf_prem_er := pay_jp_balance_pkg.get_balance_value_asg_run(g_bal.gen_wpf_prem_er,p_assignment_action_id);
842 --
843 l_value.ui_org_id := pay_jp_balance_pkg.get_entry_value_number(g_iv.ui_org,p_assignment_id,p_date_earned);
844 l_value.ui_category := pay_jp_balance_pkg.get_entry_value_char(g_iv.ui_category,p_assignment_id,p_date_earned);
845 l_value.ui_prem_ee := pay_jp_balance_pkg.get_balance_value_asg_run(g_bal.bon_ui_prem_ee,p_assignment_action_id);
846 l_value.ui_sal_amt := pay_jp_balance_pkg.get_balance_value_asg_run(g_bal.bon_ui_sal,p_assignment_action_id);
847 l_value.wai_org_id := pay_jp_balance_pkg.get_entry_value_number(g_iv.wai_org,p_assignment_id,p_date_earned);
848 l_value.wai_category := pay_jp_balance_pkg.get_entry_value_char(g_iv.wai_category,p_assignment_id,p_date_earned);
849 l_value.wai_sal_amt := pay_jp_balance_pkg.get_balance_value_asg_run(g_bal.bon_wai_sal,p_assignment_action_id);
850 l_value.itax_org_id := pay_jp_balance_pkg.get_entry_value_number(g_iv.itax_org,p_assignment_id,p_date_earned);
851 l_value.itax := pay_jp_balance_pkg.get_balance_value_asg_run(g_bal.bon_itax,p_assignment_action_id);
852 l_value.ltax_district_code := pay_jp_balance_pkg.get_entry_value_char(g_iv.gen_ltax_district_code,p_assignment_id,p_date_earned);
853 l_value.ltax_lumpsum := pay_jp_balance_pkg.get_balance_value_asg_run(g_bal.gen_ltax_lumpsum,p_assignment_action_id);
854 l_value.mutual_aid := pay_jp_balance_pkg.get_balance_value_asg_run(g_bal.bon_mutual_aid,p_assignment_action_id);
855 elsif l_value.salary_category = 'SP_BONUS' then
856 if l_value.itax_category = 'NON_RES' then
857 l_value.taxable_sal_amt := pay_jp_balance_pkg.get_balance_value_asg_run(g_bal.sp_bon_taxable_sal_nr,p_assignment_action_id);
858 l_value.taxable_mat_amt := pay_jp_balance_pkg.get_balance_value_asg_run(g_bal.sp_bon_taxable_mat_nr,p_assignment_action_id);
859 else
860 l_value.taxable_sal_amt := pay_jp_balance_pkg.get_balance_value_asg_run(g_bal.sp_bon_taxable_sal,p_assignment_action_id);
861 l_value.taxable_mat_amt := pay_jp_balance_pkg.get_balance_value_asg_run(g_bal.sp_bon_taxable_mat,p_assignment_action_id);
862 --
863 -- Disaster Tax Reduction is available only for Residents.
864 --
865 l_value.disaster_tax_reduction := pay_jp_balance_pkg.get_balance_value_asg_run(g_bal.disaster_tax_reduction,p_assignment_action_id);
866 end if;
867 l_value.ui_org_id := pay_jp_balance_pkg.get_entry_value_number(g_iv.ui_org,p_assignment_id,p_date_earned);
868 l_value.ui_category := pay_jp_balance_pkg.get_entry_value_char(g_iv.ui_category,p_assignment_id,p_date_earned);
869 l_value.ui_prem_ee := pay_jp_balance_pkg.get_balance_value_asg_run(g_bal.sp_bon_ui_prem_ee,p_assignment_action_id);
870 l_value.ui_sal_amt := pay_jp_balance_pkg.get_balance_value_asg_run(g_bal.sp_bon_ui_sal,p_assignment_action_id);
871 l_value.wai_org_id := pay_jp_balance_pkg.get_entry_value_number(g_iv.wai_org,p_assignment_id,p_date_earned);
872 l_value.wai_category := pay_jp_balance_pkg.get_entry_value_char(g_iv.wai_category,p_assignment_id,p_date_earned);
873 l_value.wai_sal_amt := pay_jp_balance_pkg.get_balance_value_asg_run(g_bal.sp_bon_wai_sal,p_assignment_action_id);
874 l_value.itax_org_id := pay_jp_balance_pkg.get_entry_value_number(g_iv.itax_org,p_assignment_id,p_date_earned);
875 l_value.itax := pay_jp_balance_pkg.get_balance_value_asg_run(g_bal.sp_bon_itax,p_assignment_action_id);
876 l_value.mutual_aid := pay_jp_balance_pkg.get_balance_value_asg_run(g_bal.sp_bon_mutual_aid,p_assignment_action_id);
877 elsif l_value.salary_category in ('YEA','RE_YEA') then
878 l_value.itax_org_id := pay_jp_balance_pkg.get_entry_value_number(g_iv.itax_org,p_assignment_id,p_date_earned);
879 l_value.itax_adjustment := pay_jp_balance_pkg.get_balance_value_asg_run(g_bal.yea_itax,p_assignment_action_id);
880 elsif l_value.salary_category = 'TERM' then
881 if l_value.itax_category = 'NON_RES' then
882 l_value.taxable_sal_amt := pay_jp_balance_pkg.get_balance_value_asg_run(g_bal.term_taxable_sal_nr,p_assignment_action_id);
883 l_value.taxable_mat_amt := pay_jp_balance_pkg.get_balance_value_asg_run(g_bal.term_taxable_mat_nr,p_assignment_action_id);
884 else
885 l_value.taxable_sal_amt := pay_jp_balance_pkg.get_balance_value_asg_run(g_bal.term_taxable_sal,p_assignment_action_id);
886 l_value.taxable_mat_amt := pay_jp_balance_pkg.get_balance_value_asg_run(g_bal.term_taxable_mat,p_assignment_action_id);
887 l_taxable_sal_spc := pay_jp_balance_pkg.get_balance_value_asg_run(g_bal.term_taxable_sal_spc,p_assignment_action_id);
888 l_taxable_mat_spc := pay_jp_balance_pkg.get_balance_value_asg_run(g_bal.term_taxable_mat_spc,p_assignment_action_id);
889 l_value.taxable_sal_amt := l_value.taxable_sal_amt + l_taxable_sal_spc;
890 l_value.taxable_mat_amt := l_value.taxable_mat_amt + l_taxable_mat_spc;
891 end if;
892 l_value.itax_org_id := pay_jp_balance_pkg.get_entry_value_number(g_iv.itax_org,p_assignment_id,p_date_earned);
893 l_value.itax := pay_jp_balance_pkg.get_balance_value_asg_run(g_bal.term_itax,p_assignment_action_id);
894 l_value.ltax_district_code := pay_jp_balance_pkg.get_entry_value_char(g_iv.gen_ltax_district_code,p_assignment_id,p_date_earned);
898 l_value.sp_ltax_income := pay_jp_balance_pkg.get_balance_value_asg_run(g_bal.term_ltax_income,p_assignment_action_id);
895 l_value.ltax_lumpsum := pay_jp_balance_pkg.get_balance_value_asg_run(g_bal.gen_ltax_lumpsum,p_assignment_action_id);
896 l_value.sp_ltax_district_code := pay_jp_balance_pkg.get_entry_value_char(g_iv.term_ltax_district_code,p_assignment_id,p_date_earned);
897 l_value.sp_ltax := pay_jp_balance_pkg.get_balance_value_asg_run(g_bal.term_ltax,p_assignment_action_id);
899 l_value.sp_ltax_shi := pay_jp_balance_pkg.get_balance_value_asg_run(g_bal.term_ltax_shi,p_assignment_action_id);
900 l_value.sp_ltax_to := pay_jp_balance_pkg.get_balance_value_asg_run(g_bal.term_ltax_to,p_assignment_action_id);
901 else -- in case 'GEPPEN', 'SANTEI', 'NA'.
902 NULL;
903 end if;
904
905 p_value := l_value;
906
907 hr_utility.set_location('pay_jp_custom_pkg.get_itax_category',20);
908 END FETCH_VALUES;
909 END PAY_JP_CUSTOM_PKG;