[Home] [Help]
PACKAGE BODY: APPS.PAY_PAYJPBON_XMLP_PKG
Source
1 PACKAGE BODY PAY_PAYJPBON_XMLP_PKG AS
2 /* $Header: PAYJPBONB.pls 120.0 2007/12/13 12:17:37 amakrish noship $ */
3
4 function BeforeReport return boolean is
5 l_date date;
6 l_date_era_code number;
7 l_date_yyyy number;
8 l_date_yy number;
9 l_date_mm number;
10 l_date_dd number;
11 l_legislation_code per_business_groups_perf.legislation_code%type;
12 l_element_type_id number;
13 begin
14 -- hr_standard.event('BEFORE REPORT');
15
16 g_rec_cnt := 0;
17
18
19 pay_jp_report_pkg.to_era(p_payment_date,
20 l_date_era_code,
21 l_date_yyyy,
22 l_date_mm,
23 l_date_dd);
24 l_date_yy := l_date_yyyy - trunc(l_date_yyyy,-2);
25 cp_payment_date_yy := lpad(to_char(l_date_yy),2,'0');
26 cp_payment_date_mm := lpad(to_char(l_date_mm),2,'0');
27 cp_payment_date_dd := lpad(to_char(l_date_dd),2,'0');
28 cp_payment_date_yymmdd := cp_payment_date_yy||'.'||cp_payment_date_mm||'.'||cp_payment_date_dd;
29
30 l_date := to_date(p_scheduled_payment_yyyymm||'01','YYYYMMDD');
31 pay_jp_report_pkg.to_era(l_date,
32 l_date_era_code,
33 l_date_yyyy,
34 l_date_mm,
35 l_date_dd);
36 l_date_yy := l_date_yyyy - trunc(l_date_yyyy,-2);
37 cp_scheduled_payment_yy := lpad(to_char(l_date_yy),2,'0');
38 cp_scheduled_payment_mm := lpad(to_char(l_date_mm),2,'0');
39 cp_scheduled_payment_yymm := cp_scheduled_payment_yy||'.'||cp_scheduled_payment_mm;
40
41 pay_jp_report_pkg.to_era(p_reported_date,
42 l_date_era_code,
43 l_date_yyyy,
44 l_date_mm,
45 l_date_dd);
46 l_date_yy := l_date_yyyy - trunc(l_date_yyyy,-2);
47 cp_reported_date_yy := lpad(to_char(l_date_yy),2,'0');
48 cp_reported_date_mm := lpad(to_char(l_date_mm),2,'0');
49 cp_reported_date_dd := lpad(to_char(l_date_dd),2,'0');
50 cp_reported_date_yymmdd := cp_reported_date_yy||'.'||cp_reported_date_mm||'.'||cp_reported_date_dd;
51
52
53 l_legislation_code := pay_jp_balance_pkg.get_legislation_code(p_business_group_id);
54 l_element_type_id := pay_jp_balance_pkg.get_element_type_id('COM_HI_QUALIFY_INFO',p_business_group_id,l_legislation_code);
55 g_hi_qualified_date_iv_id := pay_jp_balance_pkg.get_input_value_id(l_element_type_id,'QUALIFY_DATE');
56 g_hi_disqualified_date_iv_id := pay_jp_balance_pkg.get_input_value_id(l_element_type_id,'DISQUALIFY_DATE');
57 l_element_type_id := pay_jp_balance_pkg.get_element_type_id('COM_WP_QUALIFY_INFO',p_business_group_id,l_legislation_code);
58 g_wp_qualified_date_iv_id := pay_jp_balance_pkg.get_input_value_id(l_element_type_id,'QUALIFY_DATE');
59 g_wp_disqualified_date_iv_id := pay_jp_balance_pkg.get_input_value_id(l_element_type_id,'DISQUALIFY_DATE');
60 l_element_type_id := pay_jp_balance_pkg.get_element_type_id('COM_WPF_QUALIFY_INFO',p_business_group_id,l_legislation_code);
61 g_wpf_qualified_date_iv_id := pay_jp_balance_pkg.get_input_value_id(l_element_type_id,'QUALIFY_DATE');
62 g_wpf_disqualified_date_iv_id := pay_jp_balance_pkg.get_input_value_id(l_element_type_id,'DISQUALIFY_DATE');
63 l_element_type_id := pay_jp_balance_pkg.get_element_type_id('COM_SI_INFO',p_business_group_id,l_legislation_code);
64 g_si_sex_iv_id := pay_jp_balance_pkg.get_input_value_id(l_element_type_id,'SI_SEX');
65 g_bon_hi_std_prem_elm_id := pay_jp_balance_pkg.get_element_type_id('BON_HI_STD_BON',p_business_group_id,l_legislation_code);
66 g_earn_sj_hi_prem_iv_id := pay_jp_balance_pkg.get_input_value_id(g_bon_hi_std_prem_elm_id,'ERN_MONEY');
67 g_earn_kind_sj_hi_prem_iv_id := pay_jp_balance_pkg.get_input_value_id(g_bon_hi_std_prem_elm_id,'ERN_KIND');
68 g_bon_wp_std_prem_elm_id := pay_jp_balance_pkg.get_element_type_id('BON_WP_STD_BON',p_business_group_id,l_legislation_code);
69 g_earn_sj_wp_prem_iv_id := pay_jp_balance_pkg.get_input_value_id(g_bon_wp_std_prem_elm_id,'ERN_MONEY');
70 g_earn_kind_sj_wp_prem_iv_id := pay_jp_balance_pkg.get_input_value_id(g_bon_wp_std_prem_elm_id,'ERN_KIND');
71 return (TRUE);
72 end;
73
74 function cf_dataformula(sort_order in varchar2, si_type in number,date_of_birth in date,
75 effective_date in date,
76 ASSIGNMENT_ACTION_ID in number,assignment_id in number,
77 LAST_NAME in varchar2, FIRST_NAME in varchar2, ins_number in number, last_name_kana in varchar2, first_name_kana in varchar2) return number is
78 l_date_era_code number;
79 l_date_yyyy number;
80 l_date_yy number;
81 l_date_mm number;
82 l_date_dd number;
83 l_exclude varchar2(1);
84 l_wp_only varchar2(1);
85 l_hi_only varchar2(1);
86 l_bon_comp number;
87 l_bon_mtr_comp number;
88 l_bon_comp_total number;
89 l_emp_failure_item varchar2(100);
90 l_emp_error_message varchar2(1000);
91 begin
92 g_rec_cnt := g_rec_cnt + 1;
93
94
95 if sort_order = 'HI_NUMBER' then
96 l_exclude := validate_output(g_hi_qualified_date_iv_id, g_hi_disqualified_date_iv_id,assignment_id,effective_date) ;
97 else
98 if si_type = 4 then
99 l_exclude := validate_output(g_wpf_qualified_date_iv_id, g_wpf_disqualified_date_iv_id,assignment_id,effective_date) ;
100 else
101 l_exclude := validate_output(g_wp_qualified_date_iv_id,g_wp_disqualified_date_iv_id,assignment_id,effective_date) ;
102 end if;
103 end if;
104 if l_exclude = 'Y' then
105 cp_exclude := hr_general.decode_lookup('YES_NO',l_exclude);
106 else
107 cp_exclude := '';
108 end if;
109
110 pay_jp_report_pkg.to_era(date_of_birth,
111 l_date_era_code,
112 l_date_yyyy,
113 l_date_mm,
114 l_date_dd);
115 l_date_yy := l_date_yyyy - trunc(l_date_yyyy,-2);
116 cp_birth_date_era := lpad(to_char(l_date_era_code),2,'0');
117 cp_birth_date_yy := lpad(to_char(l_date_yy),2,'0');
118 cp_birth_date_mm := lpad(to_char(l_date_mm),2,'0');
119 cp_birth_date_dd := lpad(to_char(l_date_dd),2,'0');
120 cp_birth_date_erayymmdd := cp_birth_date_era||'.'||cp_birth_date_yy||'.'||cp_birth_date_mm||'.'||cp_birth_date_dd;
121
122 if trunc(effective_date,'DD') <> trunc(p_payment_date,'DD') then
123 pay_jp_report_pkg.to_era(effective_date,
124 l_date_era_code,
125 l_date_yyyy,
126 l_date_mm,
127 l_date_dd);
128 l_date_yy := l_date_yyyy - trunc(l_date_yyyy,-2);
129 cp_bon_payment_date_yy := lpad(to_char(l_date_yy),2,'0');
130 cp_bon_payment_date_mm := lpad(to_char(l_date_mm),2,'0');
131 cp_bon_payment_date_dd := lpad(to_char(l_date_dd),2,'0');
132 cp_bon_payment_date_yymmdd := cp_bon_payment_date_yy||'.'||cp_bon_payment_date_mm||'.'||cp_bon_payment_date_dd;
133 else
134 cp_bon_payment_date_yy := null;
135 cp_bon_payment_date_mm := null;
136 cp_bon_payment_date_dd := null;
137 cp_bon_payment_date_yymmdd := null;
138 end if;
139
140 if sort_order = 'HI_NUMBER' then
141 l_bon_comp := pay_jp_balance_pkg.get_result_value_number(g_bon_hi_std_prem_elm_id,g_earn_sj_hi_prem_iv_id,assignment_action_id);
142 l_bon_mtr_comp := pay_jp_balance_pkg.get_result_value_number(g_bon_hi_std_prem_elm_id,g_earn_kind_sj_hi_prem_iv_id,assignment_action_id);
143 else
144 l_bon_comp := pay_jp_balance_pkg.get_result_value_number(g_bon_wp_std_prem_elm_id,g_earn_sj_wp_prem_iv_id,assignment_action_id);
145 l_bon_mtr_comp := pay_jp_balance_pkg.get_result_value_number(g_bon_wp_std_prem_elm_id,g_earn_kind_sj_wp_prem_iv_id,assignment_action_id);
146 end if;
147 cp_bon_comp := to_char(l_bon_comp);
148 cp_bon_mtr_comp := to_char(l_bon_mtr_comp);
149
150 l_bon_comp_total := nvl(l_bon_comp,0) + nvl(l_bon_mtr_comp,0);
151 cp_bon_comp_total := to_char(l_bon_comp_total);
152 if l_bon_comp_total < 10000000 then
153 cp_d_bon_comp_total := lpad(to_char(floor(l_bon_comp_total/1000)),4,'0');
154 else
155 cp_d_bon_comp_total := to_char(floor(l_bon_comp_total/1000));
156 end if;
157
158 cp_si_sex_code := pay_jp_balance_pkg.get_entry_value_char(g_si_sex_iv_id,assignment_id,effective_date);
159
160 cp_full_name := last_name||' '||first_name;
161
162
163 cp_emp_failure_item := l_emp_failure_item;
164 cp_error_message := l_emp_error_message;
165 return('');
166 end;
167
168 function AfterPForm return boolean is
169 l_where_clause_for_assid varchar2(150);
170 l_legislation_code per_business_groups_perf.legislation_code%type;
171 begin
172 l_legislation_code := pay_jp_balance_pkg.get_legislation_code(p_business_group_id);
173
174 l_where_clause_for_assid := pay_jp_report_pkg.get_concatenated_numbers(
175 to_number(p_assignment_id1),
176 to_number(p_assignment_id2),
177 to_number(p_assignment_id3),
178 to_number(p_assignment_id4),
179 to_number(p_assignment_id5),
180 to_number(p_assignment_id6),
181 to_number(p_assignment_id7),
182 to_number(p_assignment_id8),
183 to_number(p_assignment_id9),
184 to_number(p_assignment_id10));
185 if l_where_clause_for_assid is not NULL then
186 p_where_clause_for_assid := 'and pjsbp.assignment_id in (' || l_where_clause_for_assid || ')';
187 end if;
188 return (TRUE);
189 end;
190
191 function validate_output(p_qualified_date_iv_id in number, p_disqualified_date_iv_id in number, ASSIGNMENT_ID in number, EFFECTIVE_DATE in date) return varchar2 is
192 l_exclude varchar2(1) := 'N';
193 l_qualified_date date;
194 l_disqualified_date date;
195
196 begin
197
198 l_qualified_date := pay_jp_balance_pkg.get_entry_value_date(p_qualified_date_iv_id,assignment_id,effective_date);
199 l_disqualified_date := pay_jp_balance_pkg.get_entry_value_date(p_disqualified_date_iv_id,assignment_id,effective_date);
200
201 if l_qualified_date is null then
202 if l_disqualified_date is null then
203 l_qualified_date := g_eot;
204 l_disqualified_date := g_sot;
205 else
206 l_qualified_date := g_eot;
207 end if;
208 else
209 if l_disqualified_date is null then
210 l_disqualified_date := g_eot;
211 end if;
212 end if;
213 if effective_date < l_qualified_date then
214 l_exclude := 'Y';
215 else
216 if trunc(l_disqualified_date,'MM') <= effective_date then
217 if effective_date <= last_day(l_qualified_date) then
218 null;
219 else
220 l_exclude := 'Y';
221 end if;
222 end if;
223 end if;
224 return l_exclude;
225 end validate_output;
226
227 /*(
228 p_disqualified_date_iv_id,
229 p_qualified_date_iv_id,assignment_id,effective_date)*/
230
231 function AfterReport return boolean is
232 begin
233 -- hr_standard.event('AFTER REPORT');
234 return (TRUE);
235 end;
236
237 --Functions to refer Oracle report placeholders--
238
239 Function cp_full_name_p return varchar2 is
240 Begin
241 return cp_full_name;
242 END;
243 Function cp_birth_date_era_p return varchar2 is
244 Begin
245 return cp_birth_date_era;
246 END;
247 Function cp_d_birth_date_era_p return varchar2 is
248 Begin
249 return cp_d_birth_date_era;
250 END;
251 Function cp_birth_date_yy_p return varchar2 is
252 Begin
253 return cp_birth_date_yy;
254 END;
255 Function cp_birth_date_mm_p return varchar2 is
256 Begin
257 return cp_birth_date_mm;
258 END;
259 Function cp_birth_date_dd_p return varchar2 is
260 Begin
261 return cp_birth_date_dd;
262 END;
263 Function cp_birth_date_erayymmdd_p return varchar2 is
264 Begin
265 return cp_birth_date_erayymmdd;
266 END;
267 Function cp_si_sex_code_p return varchar2 is
268 Begin
269 return cp_si_sex_code;
270 END;
271 Function cp_bon_payment_date_p return date is
272 Begin
273 return cp_bon_payment_date;
274 END;
275 Function cp_bon_payment_date_yy_p return varchar2 is
276 Begin
277 return cp_bon_payment_date_yy;
278 END;
279 Function cp_bon_payment_date_mm_p return varchar2 is
280 Begin
281 return cp_bon_payment_date_mm;
282 END;
283 Function cp_bon_payment_date_dd_p return varchar2 is
284 Begin
285 return cp_bon_payment_date_dd;
286 END;
287 Function cp_bon_payment_date_yymmdd_p return varchar2 is
288 Begin
289 return cp_bon_payment_date_yymmdd;
290 END;
291 Function cp_bon_comp_p return varchar2 is
292 Begin
293 return cp_bon_comp;
294 END;
295 Function cp_bon_mtr_comp_p return varchar2 is
296 Begin
297 return cp_bon_mtr_comp;
298 END;
299 Function cp_bon_comp_total_p return varchar2 is
300 Begin
301 return cp_bon_comp_total;
302 END;
303 Function cp_d_bon_comp_total_p return varchar2 is
304 Begin
305 return cp_d_bon_comp_total;
306 END;
307 Function cp_hi_only_p return varchar2 is
308 Begin
309 return cp_hi_only;
310 END;
311 Function cp_wp_only_p return varchar2 is
312 Begin
313 return cp_wp_only;
314 END;
315 Function cp_exclude_p return varchar2 is
316 Begin
317 return cp_exclude;
318 END;
319 Function cp_emp_failure_item_p return varchar2 is
320 Begin
321 return cp_emp_failure_item;
322 END;
323 Function cp_error_message_p return varchar2 is
324 Begin
325 return cp_error_message;
326 END;
327 Function cp_payment_date_yy_p return varchar2 is
328 Begin
329 return cp_payment_date_yy;
330 END;
331 Function cp_payment_date_mm_p return varchar2 is
332 Begin
333 return cp_payment_date_mm;
334 END;
335 Function cp_payment_date_dd_p return varchar2 is
336 Begin
337 return cp_payment_date_dd;
338 END;
339 Function cp_scheduled_payment_yy_p return varchar2 is
340 Begin
341 return cp_scheduled_payment_yy;
342 END;
343 Function cp_scheduled_payment_mm_p return varchar2 is
344 Begin
345 return cp_scheduled_payment_mm;
346 END;
347 Function cp_reported_date_yy_p return varchar2 is
348 Begin
349 return cp_reported_date_yy;
350 END;
351 Function cp_reported_date_mm_p return varchar2 is
352 Begin
353 return cp_reported_date_mm;
354 END;
355 Function cp_reported_date_dd_p return varchar2 is
356 Begin
357 return cp_reported_date_dd;
358 END;
359 Function cp_payment_date_yymmdd_p return varchar2 is
360 Begin
361 return cp_payment_date_yymmdd;
362 END;
363 Function cp_scheduled_payment_yymm_p return varchar2 is
364 Begin
365 return cp_scheduled_payment_yymm;
366 END;
367 Function cp_reported_date_yymmdd_p return varchar2 is
368 Begin
369 return cp_reported_date_yymmdd;
370 END;
371 END PAY_PAYJPBON_XMLP_PKG ;