[Home] [Help]
PACKAGE: APPS.PAY_JP_ISDF_SS_PKG
Source
1 package pay_jp_isdf_ss_pkg AUTHID CURRENT_USER as
2 /* $Header: pyjpisfs.pkh 120.3.12020000.2 2012/09/14 09:38:39 dduvvuri ship $ */
3 --
4 c_life_gen_calc_udt varchar2(80) := 'T_YEA_LIFE_INS_EXM';
5 c_life_pens_calc_udt varchar2(80) := 'T_YEA_INDIVIDUAL_PENSION_INS_EXM';
6
7 c_life_gen_new_calc_udt varchar2(80) := 'T_YEA_GEN_LIFE_INS_EXM_2012';
8 c_life_pens_new_calc_udt varchar2(80) := 'T_YEA_INDIVIDUAL_PENSION_INS_EXM_2012';
9
10 c_nonlife_long_calc_udt varchar2(80) := 'T_YEA_LONG_NONLIFE_INS_EXM';
11 c_nonlife_short_calc_udt varchar2(80) := 'T_YEA_SHORT_NONLIFE_INS_EXM';
12 --
13 c_rate_udtcol varchar2(80) := 'RATE';
14 c_add_adj_udtcol varchar2(80) := 'ADD_AMT';
15 --
16 c_yea_calc_max_udt varchar2(80) := 'T_YEA_MAX_AMT';
17 c_max_udtcol varchar2(80) := 'MAX';
18 c_nonlife_udtrow varchar2(80) := 'NONLIFE_INS_EXM';
19 c_earthquake_udtrow varchar2(80) := 'EARTHQUAKE_INS_EXM';
20 c_sp_emp_income_udtrow varchar2(80) := 'SPOUSE_SP_EXM_EARNER_ANNUAL_INCOME';
21 c_sp_dctable_sp_income_udtrow varchar2(80) := 'SPOUSE_EXM_SPOUSE_ANNUAL_INCOME';
22 c_sp_spouse_income_udtrow varchar2(80) := 'SPOUSE_SP_EXM_SPOUSE_ANNUAL_INCOME';
23 --
24 c_spouse_calc_udt varchar2(80) := 'T_YEA_SPOUSE_SP_EXM_RECKONER';
25 c_dct_udtcol varchar2(80) := 'EXM';
26 --
27 c_nonlife_long_year number := 10;
28 c_sp_calc_unit number := 10000;
29 c_sp_earned_inc_exp number := 650000;
30 c_sp_calc_earned_inc_calc1 number := 0;
31 c_sp_calc_other_inc_calc_rate number := 0.5;
32 --
33 c_nonlife_max number;
34 c_earthquake_max number;
35 c_emp_income_max number;
36 c_inc_spouse_dct_max number;
37 c_spouse_income_max number;
38 --
39 g_life_range1b varchar2(80);
40 g_life_range2a varchar2(80);
41 g_life_range2b varchar2(80);
42 g_life_range3a varchar2(80);
43 g_life_range3b varchar2(80);
44 g_life_range4a varchar2(80);
45 g_life_calc2 varchar2(80);
46 g_life_calc3 varchar2(80);
47 g_life_calc4 varchar2(80);
48 g_life_gen_max varchar2(80);
49 g_life_pens_max varchar2(80);
50 g_life_ins_max varchar2(80);
51 g_earthquake_max varchar2(80);
52 g_lnonlife_range1b varchar2(80);
53 g_lnonlife_calc2 varchar2(80);
54 g_lnonlife_year varchar2(80);
55 g_snonlife_range1b varchar2(80);
56 g_snonlife_calc2 varchar2(80);
57 g_lnonlife_max varchar2(80);
58 g_snonlife_max varchar2(80);
59 g_nonlife_max varchar2(80);
60 g_nonlife_max_2007 varchar2(80);
61 g_sp_emp_inc_max varchar2(80);
62 g_sp_spdct_max varchar2(80);
63 g_sp_spinc_max varchar2(80);
64 g_sp_calc_unit number;
65 g_sp_calc_exp1b number;
66 g_sp_calc_exp1b_fmt varchar2(80);
67 g_sp_calc_cal1 varchar2(80);
68 g_sp_calc_cal6 varchar2(80);
69 g_sp_calc_dct_range1a varchar2(80);
70 g_sp_calc_dct_range1b varchar2(80);
71 g_sp_calc_dct1 varchar2(80);
72 g_sp_calc_dct_range2a varchar2(80);
73 g_sp_calc_dct_range2b varchar2(80);
74 g_sp_calc_dct2 varchar2(80);
75 g_sp_calc_dct_range3a varchar2(80);
76 g_sp_calc_dct_range3b varchar2(80);
77 g_sp_calc_dct3 varchar2(80);
78 g_sp_calc_dct_range4a varchar2(80);
79 g_sp_calc_dct_range4b varchar2(80);
80 g_sp_calc_dct4 varchar2(80);
81 g_sp_calc_dct_range5a varchar2(80);
82 g_sp_calc_dct_range5b varchar2(80);
83 g_sp_calc_dct5 varchar2(80);
84 g_sp_calc_dct_range6a varchar2(80);
85 g_sp_calc_dct_range6b varchar2(80);
86 g_sp_calc_dct6 varchar2(80);
87 g_sp_calc_dct_range7a varchar2(80);
88 g_sp_calc_dct_range7b varchar2(80);
89 g_sp_calc_dct7 varchar2(80);
90 g_sp_calc_dct_range8a varchar2(80);
91 g_sp_calc_dct_range8b varchar2(80);
92 g_sp_calc_dct8 varchar2(80);
93 g_sp_calc_dct_range9a varchar2(80);
94 g_sp_calc_dct_range9b varchar2(80);
95 g_sp_calc_dct9 varchar2(80);
96 g_new_life_range1b varchar2(80);
97 g_new_life_range2a varchar2(80);
98 g_new_life_range2b varchar2(80);
99 g_new_life_range3a varchar2(80);
100 g_new_life_range3b varchar2(80);
101 g_new_life_range4a varchar2(80);
102 g_old_life_range1b varchar2(80);
103 g_old_life_range2a varchar2(80);
104 g_old_life_range2b varchar2(80);
105 g_old_life_range3a varchar2(80);
106 g_old_life_range3b varchar2(80);
107 g_old_life_range4a varchar2(80);
108 g_new_life_calc2a varchar2(80);
109 g_new_life_calc3a varchar2(80);
110 g_new_life_calc4a varchar2(80);
111 g_old_life_calc2a varchar2(80);
112 g_old_life_calc3a varchar2(80);
113 g_old_life_calc4a varchar2(80);
114
115 --
116 g_msg_life_range1 fnd_new_messages.message_text%type;
117 g_msg_life_range2 fnd_new_messages.message_text%type;
118 g_msg_life_range3 fnd_new_messages.message_text%type;
119 g_msg_life_range4 fnd_new_messages.message_text%type;
120 g_msg_life_calc2 fnd_new_messages.message_text%type;
121 g_msg_life_calc3 fnd_new_messages.message_text%type;
122 g_msg_life_calc4 fnd_new_messages.message_text%type;
123 g_msg_life_gen_max fnd_new_messages.message_text%type;
124 g_msg_life_pens_max fnd_new_messages.message_text%type;
125 g_msg_life_ins_max fnd_new_messages.message_text%type;
126 g_msg_nonlife_2007 fnd_new_messages.message_text%type;
127 g_msg_nonlife_ap_2007 fnd_new_messages.message_text%type;
128 g_msg_eqnonlife_s_2007 fnd_new_messages.message_text%type;
129 g_msg_lnonlife_s_2007 fnd_new_messages.message_text%type;
130 g_msg_lnonlife fnd_new_messages.message_text%type;
131 g_msg_eqnonlife_2007 fnd_new_messages.message_text%type;
132 g_msg_lnonlife_2007 fnd_new_messages.message_text%type;
133 g_msg_lnonlife_dct fnd_new_messages.message_text%type;
134 g_msg_snonlife_dct fnd_new_messages.message_text%type;
135 g_msg_lnonlife_dct_2007 fnd_new_messages.message_text%type;
136 g_msg_earthquake_max fnd_new_messages.message_text%type;
137 g_msg_nonlife_long_max fnd_new_messages.message_text%type;
138 g_msg_nonlife_short_max fnd_new_messages.message_text%type;
139 g_msg_nonlife_ins_max fnd_new_messages.message_text%type;
140 g_msg_nonlife_ins_max_2007 fnd_new_messages.message_text%type;
141 g_msg_sp_emp_inc_max fnd_new_messages.message_text%type;
142 g_msg_sp_sp_inc_max fnd_new_messages.message_text%type;
143 g_msg_sp_calc_cal1 fnd_new_messages.message_text%type;
144 g_msg_sp_calc_cal6 fnd_new_messages.message_text%type;
145 g_msg_sp_calc_dct_range1 fnd_new_messages.message_text%type;
146 g_msg_sp_calc_dct_range2 fnd_new_messages.message_text%type;
147 g_msg_sp_calc_dct_range3 fnd_new_messages.message_text%type;
148 g_msg_sp_calc_dct_range4 fnd_new_messages.message_text%type;
149 g_msg_sp_calc_dct_range5 fnd_new_messages.message_text%type;
150 g_msg_sp_calc_dct_range6 fnd_new_messages.message_text%type;
151 g_msg_sp_calc_dct_range7 fnd_new_messages.message_text%type;
152 g_msg_sp_calc_dct_range8 fnd_new_messages.message_text%type;
153 g_msg_sp_calc_dct_range9 fnd_new_messages.message_text%type;
154 g_msg_sp_calc_dct1 fnd_new_messages.message_text%type;
155 g_msg_sp_calc_dct2 fnd_new_messages.message_text%type;
156 g_msg_sp_calc_dct3 fnd_new_messages.message_text%type;
157 g_msg_sp_calc_dct4 fnd_new_messages.message_text%type;
158 g_msg_sp_calc_dct5 fnd_new_messages.message_text%type;
159 g_msg_sp_calc_dct6 fnd_new_messages.message_text%type;
160 g_msg_sp_calc_dct7 fnd_new_messages.message_text%type;
161 g_msg_sp_calc_dct8 fnd_new_messages.message_text%type;
162 g_msg_sp_calc_dct9 fnd_new_messages.message_text%type;
163 --
164 g_new_old_class_2012 fnd_new_messages.message_text%type;
165 g_new_ins_prem_2012 fnd_new_messages.message_text%type;
166 g_old_ins_prem_2012 fnd_new_messages.message_text%type;
167 g_new_dedn_ins_2012 fnd_new_messages.message_text%type;
168 g_old_dedn_ins_2012 fnd_new_messages.message_text%type;
169 g_tot_ins_2012 fnd_new_messages.message_text%type;
170 g_fin_ins_2012 fnd_new_messages.message_text%type;
171 g_cmi_prem_2012 fnd_new_messages.message_text%type;
172 g_cmi_dedn_2012 fnd_new_messages.message_text%type;
173 g_cmi_tot_2012 fnd_new_messages.message_text%type;
174 g_new_dedn_pens_2012 fnd_new_messages.message_text%type;
175 g_old_dedn_pens_2012 fnd_new_messages.message_text%type;
176 g_tot_pens_2012 fnd_new_messages.message_text%type;
177 g_fin_pens_2012 fnd_new_messages.message_text%type;
178 g_pens_tot_2012 fnd_new_messages.message_text%type;
179 g_life_tab_new_2012 fnd_new_messages.message_text%type;
180 g_col1_new_2012 fnd_new_messages.message_text%type;
181 g_form_dedn_amt_2012 fnd_new_messages.message_text%type;
182 g_row1_col2_2012 fnd_new_messages.message_text%type;
183 g_life_tab_old_2012 fnd_new_messages.message_text%type;
184 g_col3_new_2012 fnd_new_messages.message_text%type;
185 g_row1_col4_2012 fnd_new_messages.message_text%type;
186 g_tot_lid_2012 fnd_new_messages.message_text%type;
187 g_corp_type_2012 fnd_new_messages.message_text%type;
188 g_new_life_range1 fnd_new_messages.message_text%type;
189 g_new_life_range2 fnd_new_messages.message_text%type;
190 g_new_life_range3 fnd_new_messages.message_text%type;
191 g_new_life_range4 fnd_new_messages.message_text%type;
192 g_old_life_range1 fnd_new_messages.message_text%type;
193 g_old_life_range2 fnd_new_messages.message_text%type;
194 g_old_life_range3 fnd_new_messages.message_text%type;
195 g_old_life_range4 fnd_new_messages.message_text%type;
196 g_new_life_calc1 fnd_new_messages.message_text%type;
197 g_new_life_calc2 fnd_new_messages.message_text%type;
198 g_new_life_calc3 fnd_new_messages.message_text%type;
199 g_new_life_calc4 fnd_new_messages.message_text%type;
200 g_old_life_calc1 fnd_new_messages.message_text%type;
201 g_old_life_calc2 fnd_new_messages.message_text%type;
202 g_old_life_calc3 fnd_new_messages.message_text%type;
203 g_old_life_calc4 fnd_new_messages.message_text%type;
204
205 g_payroll_action_id number;
206 g_business_group_id number;
207 g_effective_date date;
208 --
209 type t_action_info_rec is record(
210 action_information_id pay_action_information.action_information_id%type,
211 action_context_id pay_action_information.action_context_id%type,
212 action_context_type pay_action_information.action_context_type%type,
213 object_version_number pay_action_information.object_version_number%type,
214 action_information_category pay_action_information.action_information_category%type,
215 action_information1 pay_action_information.action_information1%type,
216 action_information2 pay_action_information.action_information2%type,
217 action_information3 pay_action_information.action_information3%type,
218 action_information4 pay_action_information.action_information4%type,
219 action_information5 pay_action_information.action_information5%type,
220 action_information6 pay_action_information.action_information6%type,
221 action_information7 pay_action_information.action_information7%type,
222 action_information8 pay_action_information.action_information8%type,
223 action_information9 pay_action_information.action_information9%type,
224 action_information10 pay_action_information.action_information10%type,
225 action_information11 pay_action_information.action_information11%type,
226 action_information12 pay_action_information.action_information12%type,
227 action_information13 pay_action_information.action_information13%type,
228 action_information14 pay_action_information.action_information14%type,
229 action_information15 pay_action_information.action_information15%type,
230 action_information16 pay_action_information.action_information16%type,
231 action_information17 pay_action_information.action_information17%type,
232 action_information18 pay_action_information.action_information18%type,
233 action_information19 pay_action_information.action_information19%type,
234 action_information20 pay_action_information.action_information20%type,
235 action_information21 pay_action_information.action_information21%type,
236 action_information22 pay_action_information.action_information22%type,
237 action_information23 pay_action_information.action_information23%type,
238 action_information24 pay_action_information.action_information24%type,
239 action_information25 pay_action_information.action_information25%type,
240 action_information26 pay_action_information.action_information26%type,
241 action_information27 pay_action_information.action_information27%type,
242 action_information28 pay_action_information.action_information28%type,
243 action_information29 pay_action_information.action_information29%type,
244 action_information30 pay_action_information.action_information30%type,
245 effective_date pay_action_information.effective_date%type,
246 assignment_id pay_action_information.assignment_id%type);
247 --
248 -- #2243411 bulk collect bug fix is available from 9.2
249 type t_action_info_tbl is table of t_action_info_rec index by binary_integer;
250 --
251 type t_calc_total_rec is record(
252 life_gen number,
253 life_pens number,
254 life_gen_new number,
255 life_pens_new number,
256 care_med_new number,
257 earthquake number,
258 nonlife_long number,
259 nonlife_short number,
260 national_pens number,
261 social number,
262 mutual_aid_ec number,
263 mutual_aid_p number,
264 mutual_aid_dsc number,
265 sp_emp_inc number,
266 sp_spouse_inc number,
267 sp_sp_type varchar2(60),
268 sp_wid_type varchar2(60),
269 sp_dct_exc varchar2(60),
273 sp_business_inc number,
270 sp_inc_cnt number,
271 sp_earned_inc number,
272 sp_earned_inc_exp number,
274 sp_business_inc_exp number,
275 sp_miscellaneous_inc number,
276 sp_miscellaneous_inc_exp number,
277 sp_dividend_inc number,
278 sp_dividend_inc_exp number,
279 sp_real_estate_inc number,
280 sp_real_estate_inc_exp number,
281 sp_retirement_inc number,
282 sp_retirement_inc_exp number,
283 sp_other_inc number,
284 sp_other_inc_exp number,
285 sp_other_inc_exp_dct number,
286 sp_other_inc_exp_tmp number,
287 sp_other_inc_exp_tmp_exp number);
288 --
289 type t_calc_spouse_inc_rec is record(
290 sp_earned_inc_calc number,
291 sp_business_inc_calc number,
292 sp_miscellaneous_inc_calc number,
293 sp_dividend_inc_calc number,
294 sp_real_estate_inc_calc number,
295 sp_retirement_inc_calc number,
296 sp_other_inc_calc number,
297 sp_inc_calc number);
298 --
299 type t_calc_dct_rec is record(
300 life_gen_ins_prem number,
301 life_pens_ins_prem number,
302 life_gen_new_ins_prem number,
303 life_pens_new_ins_prem number,
304 life_gen_ins_calc_prem number,
305 life_pens_ins_calc_prem number,
306 life_gen_new_ins_calc_prem number,
307 life_pens_new_ins_calc_prem number,
308 life_ins_deduction number,
309 care_med_new_ins_prem number,
310 care_med_new_ins_calc_prem number,
311 nonlife_long_ins_prem number,
312 nonlife_short_ins_prem number,
313 earthquake_ins_prem number,
314 nonlife_long_ins_calc_prem number,
315 nonlife_short_ins_calc_prem number,
316 earthquake_ins_calc_prem number,
317 nonlife_ins_deduction number,
318 national_pens_ins_prem number,
319 social_ins_prem number,
320 social_ins_deduction number,
321 mutual_aid_deduction number,
322 sp_earned_inc_calc number,
323 sp_business_inc_calc number,
324 sp_miscellaneous_inc_calc number,
325 sp_dividend_inc_calc number,
326 sp_real_estate_inc_calc number,
327 sp_retirement_inc_calc number,
328 sp_other_inc_calc number,
329 sp_inc_calc number,
330 spouse_inc number,
331 spouse_deduction number);
332 --
333 function get_spouse_type(
334 p_assignment_id in number,
335 p_effective_date in date,
336 p_payroll_id in number)
337 return varchar2;
338 --
339 function get_widow_type(
340 p_assignment_id in number,
341 p_effective_date in date)
342 return varchar2;
343 --
344 procedure set_form_pg_prompt(
345 p_action_information_id in number);
346 --
347 procedure set_form_pg_prompt2012(
348 p_action_information_id in number);
349 --
350 procedure do_new(
351 p_action_information_id in number,
352 p_object_version_number in out nocopy number);
353 --
354 procedure do_apply(
355 p_action_information_id in number,
356 p_object_version_number in out nocopy number);
357 --
358 procedure do_calculate(
359 p_action_information_id in number,
360 p_object_version_number in out nocopy number);
361 --
362 procedure do_finalize(
363 p_action_information_id in number,
364 p_object_version_number in out nocopy number,
365 p_user_comments in varchar2);
366 --
367 procedure do_reject(
368 p_action_information_id in number,
369 p_object_version_number in out nocopy number,
370 p_admin_comments in varchar2);
371 --
372 procedure do_return(
373 p_action_information_id in number,
374 p_object_version_number in out nocopy number,
375 p_admin_comments in varchar2);
376 --
377 procedure do_approve(
378 p_action_information_id in number,
379 p_object_version_number in out nocopy number);
380 --
381 procedure do_transfer(
382 p_action_information_id in number,
383 p_object_version_number in out nocopy number,
384 p_transfer_date in date,
385 p_create_session in boolean default true,
386 p_expire_after_transfer in varchar2 default 'N');
387 --
388 procedure do_expire(
389 p_action_information_id in number,
390 p_object_version_number in out nocopy number,
391 p_expiry_date in date,
392 p_create_session in boolean default true,
393 p_mode in varchar2 default null);
394 --
395 -- internal use only
396 procedure do_finalize(
397 errbuf out nocopy varchar2,
398 retcode out nocopy varchar2,
399 p_payroll_action_id in number,
400 p_user_comments in varchar2);
401 --
402 procedure do_approve(
403 errbuf out nocopy varchar2,
404 retcode out nocopy varchar2,
405 p_payroll_action_id in number);
406 --
407 procedure do_transfer(
408 errbuf out nocopy varchar2,
409 retcode out nocopy varchar2,
410 p_payroll_action_id in number,
411 p_transfer_date in varchar2,
412 p_expire_after_transfer in varchar2 default 'N');
413 --
414 procedure do_expire(
415 errbuf out nocopy varchar2,
416 retcode out nocopy varchar2,
417 p_payroll_action_id in number,
418 p_expiry_date in varchar2,
419 p_mode in varchar2 default null);
420 --
421 end pay_jp_isdf_ss_pkg;