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