[Home] [Help]
PACKAGE BODY: APPS.PAY_PAYJPSMT_XMLP_PKG
Source
1 PACKAGE BODY PAY_PAYJPSMT_XMLP_PKG AS
2 /* $Header: PAYJPSMTB.pls 120.3 2008/04/03 07:31:31 amakrish noship $ */
3
4 function BeforeReport return boolean is
5 l_element_type_id NUMBER;
6 l_legislation_code VARCHAR2(2);
7 l_date_era_code NUMBER;
8 l_date_year NUMBER;
9 l_date_month NUMBER;
10 l_date_day NUMBER;
11 l_file_name VARCHAR2(100);
12 l_apply_date DATE;
13 begin
14 --hr_standard.event('BEFORE REPORT');
15
16 CP_REPORT_TITLE := 'Notification of Santei Checklist';
17 LP_DATE_EARNED := to_char(P_DATE_EARNED,'DD-MON-YYYY');
18 LP_DATE_FROM := TO_CHAR(P_DATE_FROM,'DD-MON-YYYY');
19 LP_DATE_TO := TO_CHAR(P_DATE_TO,'DD-MON-YYYY');
20 P_CONC_REQUEST_ID := FND_GLOBAL.conc_request_id;
21 if P_FILE_NAME is NULL then
22 l_file_name := 's' || lpad(to_char(P_CONC_REQUEST_ID - trunc(P_CONC_REQUEST_ID,-7)),7,'0') || '.mf';
23 else
24 l_file_name := P_FILE_NAME;
25 end if;
26 CP_MAGFILE_NAME := l_file_name;
27
28 G_BLOCK_COUNT :=1;
29 G_REC_COUNT :=0;
30
31 l_legislation_code := pay_jp_balance_pkg.get_legislation_code(P_BUSINESS_GROUP_ID);
32
33 l_element_type_id := pay_jp_balance_pkg.get_element_type_id('COM_SI_INFO',P_BUSINESS_GROUP_ID,l_legislation_code);
34 G_SI_SEX_IV_ID := pay_jp_balance_pkg.get_input_value_id(l_element_type_id,'SI_SEX');
35
36 l_element_type_id := pay_jp_balance_pkg.get_element_type_id('COM_WP_SMR_INFO',P_BUSINESS_GROUP_ID,l_legislation_code);
37 G_WP_OLD_COMP_IV_ID := pay_jp_balance_pkg.get_input_value_id(l_element_type_id,'PRIOR_SMR');
38 G_WP_NEW_COMP_IV_ID := pay_jp_balance_pkg.get_input_value_id(l_element_type_id,'REVISED_SMR');
39 G_WP_COMP_APPLIED_DATE_IV_ID := pay_jp_balance_pkg.get_input_value_id(l_element_type_id,'APPLY_MTH');
40
41 IF p_date_earned < TO_DATE('2003/04/01', 'YYYY/MM/DD') THEN
42 G_SANTEI_ELM_ID := pay_jp_balance_pkg.get_element_type_id('SAN_BEFORE_20030331_RSLT',P_BUSINESS_GROUP_ID,l_legislation_code);
43 G_PAYMENT_DAYS3_IV_ID := pay_jp_balance_pkg.get_input_value_id(G_SANTEI_ELM_ID,'PAY_BASE_DAYS_MAY');
44 G_PAYMENT_DAYS2_IV_ID := pay_jp_balance_pkg.get_input_value_id(G_SANTEI_ELM_ID,'PAY_BASE_DAYS_JUN');
45 G_PAYMENT_DAYS1_IV_ID := pay_jp_balance_pkg.get_input_value_id(G_SANTEI_ELM_ID,'PAY_BASE_DAYS_JUL');
46 G_SIMPLE_COMP3_IV_ID := pay_jp_balance_pkg.get_input_value_id(G_SANTEI_ELM_ID,'ERN_MONEY_MAY');
47 G_SIMPLE_COMP2_IV_ID := pay_jp_balance_pkg.get_input_value_id(G_SANTEI_ELM_ID,'ERN_MONEY_JUN');
48 G_SIMPLE_COMP1_IV_ID := pay_jp_balance_pkg.get_input_value_id(G_SANTEI_ELM_ID,'ERN_MONEY_JUL');
49 G_SIMPLE_MTR_COMP3_IV_ID := pay_jp_balance_pkg.get_input_value_id(G_SANTEI_ELM_ID,'ERN_KIND_MAY');
50 G_SIMPLE_MTR_COMP2_IV_ID := pay_jp_balance_pkg.get_input_value_id(G_SANTEI_ELM_ID,'ERN_KIND_JUN');
51 G_SIMPLE_MTR_COMP1_IV_ID := pay_jp_balance_pkg.get_input_value_id(G_SANTEI_ELM_ID,'ERN_KIND_JUL');
52 ELSE
53 G_SANTEI_ELM_ID := pay_jp_balance_pkg.get_element_type_id('SAN_RSLT',P_BUSINESS_GROUP_ID,l_legislation_code);
54 G_PAYMENT_DAYS3_IV_ID := pay_jp_balance_pkg.get_input_value_id(G_SANTEI_ELM_ID,'PAY_BASE_DAYS_APR');
55 G_PAYMENT_DAYS2_IV_ID := pay_jp_balance_pkg.get_input_value_id(G_SANTEI_ELM_ID,'PAY_BASE_DAYS_MAY');
56 G_PAYMENT_DAYS1_IV_ID := pay_jp_balance_pkg.get_input_value_id(G_SANTEI_ELM_ID,'PAY_BASE_DAYS_JUN');
57 G_SIMPLE_COMP3_IV_ID := pay_jp_balance_pkg.get_input_value_id(G_SANTEI_ELM_ID,'ERN_MONEY_APR');
58 G_SIMPLE_COMP2_IV_ID := pay_jp_balance_pkg.get_input_value_id(G_SANTEI_ELM_ID,'ERN_MONEY_MAY');
59 G_SIMPLE_COMP1_IV_ID := pay_jp_balance_pkg.get_input_value_id(G_SANTEI_ELM_ID,'ERN_MONEY_JUN');
60 G_SIMPLE_MTR_COMP3_IV_ID := pay_jp_balance_pkg.get_input_value_id(G_SANTEI_ELM_ID,'ERN_KIND_APR');
61 G_SIMPLE_MTR_COMP2_IV_ID := pay_jp_balance_pkg.get_input_value_id(G_SANTEI_ELM_ID,'ERN_KIND_MAY');
62 G_SIMPLE_MTR_COMP1_IV_ID := pay_jp_balance_pkg.get_input_value_id(G_SANTEI_ELM_ID,'ERN_KIND_JUN');
63 END IF;
64 G_SIMPLE_COMP_TOTAL_IV_ID:= pay_jp_balance_pkg.get_input_value_id(G_SANTEI_ELM_ID,'TOTAL');
65 G_SIMPLE_COMP_AVG_IV_ID := pay_jp_balance_pkg.get_input_value_id(G_SANTEI_ELM_ID,'SIMPLE_AVG');
66 G_ADJUSTED_COMP_AVG_IV_ID:= pay_jp_balance_pkg.get_input_value_id(G_SANTEI_ELM_ID,'CORRECT_AVG');
67
68 G_SANTEI_BIKOU_ELM_ID := pay_jp_balance_pkg.get_element_type_id('SAN_REPORT_RMKS_RSLT',P_BUSINESS_GROUP_ID,l_legislation_code);
69 G_PART_TIMER_IV_ID := pay_jp_balance_pkg.get_input_value_id(G_SANTEI_BIKOU_ELM_ID,'SHORT_TIME_WORKER_FLAG');
70
71 G_SANTEI_GEPPEN_MTH_ELM_ID := pay_jp_balance_pkg.get_element_type_id('SAN_GEP_MTH_RSLT',P_BUSINESS_GROUP_ID,l_legislation_code);
72 G_KOUNEN_MTH_IV_ID := pay_jp_balance_pkg.get_input_value_id(G_SANTEI_GEPPEN_MTH_ELM_ID,'WP');
73 IF P_DATE_EARNED < TO_DATE('2003/04/01', 'YYYY/MM/DD') THEN
74 pay_jp_report_pkg.to_era( to_date(to_char(P_DATE_EARNED,'YYYY') || '1001','YYYYMMDD'),
75 l_date_era_code,
76 l_date_year,
77 l_date_month,
78 l_date_day);
79 elsif p_date_earned >= TO_DATE('2003/04/01', 'YYYY/MM/DD') THEN
80 pay_jp_report_pkg.to_era( to_date(to_char(P_DATE_EARNED,'YYYY') || '0901','YYYYMMDD'),
81 l_date_era_code,
82 l_date_year,
83 l_date_month,
84 l_date_day);
85 end if;
86 l_date_year := l_date_year - trunc(l_date_year,-2);
87 G_DATE_APPLIED_YYMM := lpad(to_char(l_date_year),2,'0') || lpad(to_char(l_date_month),2,'0');
88 if p_date_earned < TO_DATE('2003/04/01', 'YYYY/MM/DD') THEN
89 l_apply_date := to_date(to_char(P_DATE_EARNED,'YYYY') || '1001','YYYYMMDD');
90 elsif p_date_earned >= TO_DATE('2003/04/01', 'YYYY/MM/DD') THEN
91 l_apply_date := to_date(to_char(P_DATE_EARNED,'YYYY') || '0901','YYYYMMDD');
92 end if;
93 G_WP_MAX_STD_COMP := pay_jp_report_pkg.get_max_value('T_COM_SMR','WP_SMR',l_apply_date);
94 G_WP_MIN_STD_COMP := pay_jp_report_pkg.get_min_value('T_COM_SMR','WP_SMR',l_apply_date);
95 --file_io.open_magfile;
96
97 return (TRUE);
98 end;
99
100 function cf_emp_recordformula (ASSIGNMENT_ID in number, EFFECTIVE_DATE in date, ASSIGNMENT_ACTION_ID in number, date_earned in date, WP_NUMBER in varchar2, DATE_OF_BIRTH in date, LAST_NAME_KANA in varchar2, FIRST_NAME_KANA in varchar2) return number is
101 l_emp_failure_item VARCHAR2(100);
102 l_wp_std_monthly_comp_old varchar2(60);
103 l_wp_applied_date_old date;
104 l_part_timer_flag varchar2(60);
105 l_legislation_code VARCHAR2(2);
106 begin
107 G_REC_COUNT := G_REC_COUNT + 1;
108 CP_RECORD_NUMBER := G_REC_COUNT;
109 CP_BLOCK_SERIAL_NUMBER := G_BLOCK_COUNT;
110 if G_REC_COUNT = 10 then
111 G_REC_COUNT := 0;
112 G_BLOCK_COUNT := G_BLOCK_COUNT + 1;
113 end if;
114
115 CP_SI_SEX_CODE := pay_jp_balance_pkg.get_entry_value_char(G_SI_SEX_IV_ID,ASSIGNMENT_ID,EFFECTIVE_DATE);
116 CP_SI_SEX := hr_general.decode_lookup('JP_SI_SEX',CP_SI_SEX_CODE);
117
118 if nvl(to_char(ASSIGNMENT_ACTION_ID),' ') = ' ' then
119 CP_DISQUALIFIED := 'Yes';
120 else
121 CP_DISQUALIFIED := 'No';
122 end if;
123
124 CP_TOTAL_CNT := CP_TOTAL_CNT + 1;
125
126 if CP_DISQUALIFIED = 'Yes' then
127 CP_GEPPEN_MTH := ' ';
128 CP_DISQUALIFIED_CNT := CP_DISQUALIFIED_CNT + 1;
129 else
130 CP_GEPPEN_MTH := nvl(to_char(pay_jp_balance_pkg.get_result_value_number(G_SANTEI_GEPPEN_MTH_ELM_ID,G_KOUNEN_MTH_IV_ID,ASSIGNMENT_ACTION_ID)),' ');
131 if CP_GEPPEN_MTH = ' ' then
132 CP_SANTEI_CNT := CP_SANTEI_CNT + 1;
133 else
134 if p_date_earned < TO_DATE('2003/04/01', 'YYYY/MM/DD') THEN
135 if CP_GEPPEN_MTH = '8' then
136 CP_GEPPEN_AUG_CNT := CP_GEPPEN_AUG_CNT + 1;
137 end if;
138 if CP_GEPPEN_MTH = '9' then
139 CP_GEPPEN_SEP_CNT := CP_GEPPEN_SEP_CNT + 1;
140 end if;
141 if CP_GEPPEN_MTH = '10' then
142 CP_GEPPEN_OCT_CNT := CP_GEPPEN_OCT_CNT + 1;
143 end if;
144 elsif p_date_earned >= TO_DATE('2003/04/01', 'YYYY/MM/DD') THEN
145 if CP_GEPPEN_MTH = '7' then
146 CP_GEPPEN_AUG_CNT := CP_GEPPEN_AUG_CNT + 1;
147 end if;
148 if CP_GEPPEN_MTH = '8' then
149 CP_GEPPEN_SEP_CNT := CP_GEPPEN_SEP_CNT + 1;
150 end if;
151 if CP_GEPPEN_MTH = '9' then
152 CP_GEPPEN_OCT_CNT := CP_GEPPEN_OCT_CNT + 1;
153 end if;
154 end if;
155 end if;
156 end if;
157
158 if CP_DISQUALIFIED = 'No' and CP_GEPPEN_MTH = ' ' then
159 CP_APPLIED_DATE := G_DATE_APPLIED_YYMM;
160
161 get_latest_std_mth_comp_info(
162 assignment_id,
163 effective_date,
164 date_earned,
165 g_wp_comp_applied_date_iv_id,
166 g_wp_new_comp_iv_id,
167 g_wp_old_comp_iv_id,
168 l_wp_applied_date_old,
169 l_wp_std_monthly_comp_old);
170 CP_WP_STD_MONTHLY_COMP_OLD := lpad(to_char(floor(to_number(nvl(l_wp_std_monthly_comp_old,'0'))/1000)),4,'0');
171 CP_WP_STD_MONTHLY_COMP_NEW := lpad(to_char(floor(nvl(pay_jp_balance_pkg.get_entry_value_number(G_WP_NEW_COMP_IV_ID,ASSIGNMENT_ID,EFFECTIVE_DATE),0))/1000),4,'0');
172
173 CP_PAYMENT_DAYS_MAY := nvl(pay_jp_balance_pkg.get_result_value_number(G_SANTEI_ELM_ID,G_PAYMENT_DAYS3_IV_ID,ASSIGNMENT_ACTION_ID),0);
174 CP_PAYMENT_DAYS_JUN := nvl(pay_jp_balance_pkg.get_result_value_number(G_SANTEI_ELM_ID,G_PAYMENT_DAYS2_IV_ID,ASSIGNMENT_ACTION_ID),0);
175 CP_PAYMENT_DAYS_JUL := nvl(pay_jp_balance_pkg.get_result_value_number(G_SANTEI_ELM_ID,G_PAYMENT_DAYS1_IV_ID,ASSIGNMENT_ACTION_ID),0);
176
177 CP_SIMPLE_COMP_MAY := nvl(pay_jp_balance_pkg.get_result_value_number(G_SANTEI_ELM_ID,G_SIMPLE_COMP3_IV_ID,ASSIGNMENT_ACTION_ID),0);
178 CP_SIMPLE_COMP_JUN := nvl(pay_jp_balance_pkg.get_result_value_number(G_SANTEI_ELM_ID,G_SIMPLE_COMP2_IV_ID,ASSIGNMENT_ACTION_ID),0);
179 CP_SIMPLE_COMP_JUL := nvl(pay_jp_balance_pkg.get_result_value_number(G_SANTEI_ELM_ID,G_SIMPLE_COMP1_IV_ID,ASSIGNMENT_ACTION_ID),0);
180
181 CP_SIMPLE_MTR_COMP_MAY := nvl(pay_jp_balance_pkg.get_result_value_number(G_SANTEI_ELM_ID,G_SIMPLE_MTR_COMP3_IV_ID,ASSIGNMENT_ACTION_ID),0);
182 CP_SIMPLE_MTR_COMP_JUN := nvl(pay_jp_balance_pkg.get_result_value_number(G_SANTEI_ELM_ID,G_SIMPLE_MTR_COMP2_IV_ID,ASSIGNMENT_ACTION_ID),0);
183 CP_SIMPLE_MTR_COMP_JUL := nvl(pay_jp_balance_pkg.get_result_value_number(G_SANTEI_ELM_ID,G_SIMPLE_MTR_COMP1_IV_ID,ASSIGNMENT_ACTION_ID),0);
184
185 CP_SIMPLE_COMP_TOTAL_MAY := CP_SIMPLE_COMP_MAY + CP_SIMPLE_MTR_COMP_MAY;
186 CP_SIMPLE_COMP_TOTAL_JUN := CP_SIMPLE_COMP_JUN + CP_SIMPLE_MTR_COMP_JUN;
187 CP_SIMPLE_COMP_TOTAL_JUL := CP_SIMPLE_COMP_JUL + CP_SIMPLE_MTR_COMP_JUL;
188
189 CP_SIMPLE_COMP_TOTAL := nvl(pay_jp_balance_pkg.get_result_value_number(G_SANTEI_ELM_ID,G_SIMPLE_COMP_TOTAL_IV_ID,ASSIGNMENT_ACTION_ID),0);
190
191 CP_SIMPLE_COMP_AVG := nvl(pay_jp_balance_pkg.get_result_value_number(G_SANTEI_ELM_ID,G_SIMPLE_COMP_AVG_IV_ID,ASSIGNMENT_ACTION_ID),0);
192
193 CP_ADJUSTED_COMP_AVG := pay_jp_balance_pkg.get_result_value_number(G_SANTEI_ELM_ID,G_ADJUSTED_COMP_AVG_IV_ID,ASSIGNMENT_ACTION_ID);
194
195 if nvl(g_effective_date,to_date('4712/12/31','YYYY/MM/DD')) <> trunc(effective_date,'DD') then
196 l_legislation_code := pay_jp_balance_pkg.get_legislation_code(P_BUSINESS_GROUP_ID);
197 g_g_kyou_sangep_pay_day := get_global_value('G_COM_PAY_BASE_DAYS_MIN',p_business_group_id,l_legislation_code,effective_date);
198 g_effective_date := trunc(effective_date,'DD');
199 end if;
200 l_part_timer_flag := nvl(pay_jp_balance_pkg.get_result_value_char(G_SANTEI_BIKOU_ELM_ID,G_PART_TIMER_IV_ID,ASSIGNMENT_ACTION_ID),'No');
201 if cp_payment_days_may < nvl(g_g_kyou_sangep_pay_day,0)
202 and cp_payment_days_jun < nvl(g_g_kyou_sangep_pay_day,0)
203 and cp_payment_days_jul < nvl(g_g_kyou_sangep_pay_day,0)
204 and l_part_timer_flag = 'Yes' then
205 cp_part_timer_flag := l_part_timer_flag;
206 else
207 cp_part_timer_flag := 'No';
208 end if;
209 else
210 CP_APPLIED_DATE := NULL;
211 CP_WP_STD_MONTHLY_COMP_OLD := NULL;
212 CP_WP_STD_MONTHLY_COMP_NEW := NULL;
213 CP_PAYMENT_DAYS_MAY := NULL;
214 CP_PAYMENT_DAYS_JUN := NULL;
215 CP_PAYMENT_DAYS_JUL := NULL;
216 CP_SIMPLE_COMP_MAY := NULL;
217 CP_SIMPLE_COMP_JUN := NULL;
218 CP_SIMPLE_COMP_JUL := NULL;
219 CP_SIMPLE_MTR_COMP_MAY := NULL;
220 CP_SIMPLE_MTR_COMP_JUN := NULL;
221 CP_SIMPLE_MTR_COMP_JUL := NULL;
222 CP_SIMPLE_COMP_TOTAL_MAY := NULL;
223 CP_SIMPLE_COMP_TOTAL_JUN := NULL;
224 CP_SIMPLE_COMP_TOTAL_JUL := NULL;
225 CP_SIMPLE_COMP_TOTAL := NULL;
226 CP_SIMPLE_COMP_AVG := NULL;
227 CP_ADJUSTED_COMP_AVG := NULL;
228 CP_PART_TIMER_FLAG := NULL;
229 end if;
230
231 l_emp_failure_item := ' ';
232 if nvl(WP_NUMBER,' ') = ' ' then
233 l_emp_failure_item := l_emp_failure_item||hr_jp_standard_pkg.sjhextochar('94ED95DB8CAF8ED290AE979D94D48D8620');
234 end if;
235 if nvl(to_char(DATE_OF_BIRTH),' ') = ' ' then
236 l_emp_failure_item := l_emp_failure_item||hr_jp_standard_pkg.sjhextochar('90B6944E8C8E93FA20');
237 end if;
238 if nvl(LAST_NAME_KANA,' ') = ' ' and nvl(FIRST_NAME_KANA,' ') = ' 'then
239 l_emp_failure_item := l_emp_failure_item||hr_jp_standard_pkg.sjhextochar('8E8196BC20');
240 end if;
241 if nvl(CP_SI_SEX_CODE,' ') = ' ' then
242 l_emp_failure_item := l_emp_failure_item||hr_jp_standard_pkg.sjhextochar('8EED95CA20');
243 end if;
244 CP_EMP_FAILURE_ITEM := l_emp_failure_item;
245 return('');
246 end;
247
248 function AfterReport return boolean is
249 begin
250 if G_REC_COUNT <> 0 then
251
252 /* file_io.put('DATA');
253 file_io.put(lpad(to_char(G_BLOCK_COUNT),8,'0'));
254 file_io.put('0060');
255 file_io.put('1440');
256 file_io.put(lpad(to_char(G_REC_COUNT),2,'0'));
257 file_io.put(lpad(' ',38,' '));
258
259 file_io.put(G_ORG_REC);
260
261 file_io.put(G_EMP_REC_1);
262 file_io.put(G_EMP_REC_2);
263 file_io.put(G_EMP_REC_3);
264 file_io.put(G_EMP_REC_4);
265 file_io.put(G_EMP_REC_5);
266 file_io.put(G_EMP_REC_6);
267 file_io.put(G_EMP_REC_7);
268 file_io.put(G_EMP_REC_8);
269 file_io.put(G_EMP_REC_9);
270 file_io.put(G_EMP_REC_10);*/
271 null;
272 end if;
273
274 if G_REC_COUNT > 0 or G_BLOCK_COUNT > 1 then
275 /* file_io.put('TCTL');
276 file_io.put('0');
277 file_io.put(lpad(' ',250,' '));
278 file_io.put(lpad(' ',250,' '));
279 file_io.put(lpad(' ',250,' '));
280 file_io.put(lpad(' ',250,' '));
281 file_io.put(lpad(' ',250,' '));
282 file_io.put(lpad(' ',245,' '));*/
283 null;
284 end if;
285
286 --file_io.close_magfile;
287
288 --hr_standard.event('AFTER REPORT');
289 return (TRUE);
290 end;
291
292 procedure get_latest_std_mth_comp_info(
293 p_assignment_id in number,
294 p_effective_date in date,
295 p_date_earned in date,
296 p_applied_mth_iv_id in number,
297 p_new_std_mth_comp_iv_id in number,
298 p_old_std_mth_comp_iv_id in number,
299 p_latest_applied_date out nocopy date,
300 p_latest_std_mth_comp out nocopy varchar2)
301 is
302
303
304
305 cursor csr_past_std_mth_comp is
306 select pee.element_entry_id ee_id,
307 pee.effective_start_date ee_esd,
308 pee.effective_end_date ee_eed,
309 peev1.screen_entry_value applied_mth,
310 peev2.screen_entry_value new_std_mth_comp
311 from pay_element_entry_values_f peev2,
312 pay_element_entry_values_f peev1,
313 pay_link_input_values_f pliv2,
314 pay_link_input_values_f pliv1,
315 pay_element_entries_f pee
316 where pee.assignment_id = p_assignment_id
317
318
319
320
321
322 and pee.effective_start_date < p_effective_date
323 and pliv1.element_link_id = pee.element_link_id
324 and pliv2.element_link_id = pee.element_link_id
325 and pliv1.input_value_id = p_applied_mth_iv_id
326 and pliv2.input_value_id = p_new_std_mth_comp_iv_id
327 and pee.effective_start_date
328 between pliv1.effective_start_date and pliv1.effective_end_date
329 and pee.effective_start_date
330 between pliv2.effective_start_date and pliv2.effective_end_date
331 and peev1.element_entry_id = pee.element_entry_id
332 and peev1.input_value_id = pliv1.input_value_id
333 and peev1.effective_start_date = pee.effective_start_date
334 and peev1.effective_end_date = pee.effective_end_date
335 and peev2.element_entry_id = pee.element_entry_id
336 and peev2.input_value_id = pliv2.input_value_id
337 and peev2.effective_start_date = pee.effective_start_date
338 and peev2.effective_end_date = pee.effective_end_date
339 order by pee.effective_start_date;
340 l_csr_past_std_mth_comp csr_past_std_mth_comp%rowtype;
341 l_applied_mth_one_day_before varchar2(60);
342 l_applied_mth varchar2(60);
343 l_applied_mth_old varchar2(60);
344 l_std_mth_comp_old varchar2(60);
345 begin
346 l_applied_mth := pay_jp_balance_pkg.get_entry_value_char(p_applied_mth_iv_id,p_assignment_id,p_effective_date);
347 l_applied_mth_one_day_before := pay_jp_balance_pkg.get_entry_value_char(p_applied_mth_iv_id,p_assignment_id,p_effective_date - 1);
348 if trunc(to_date(nvl(l_applied_mth_one_day_before,'000101')||'01','YYYYMMDD'),'MM')
349 < trunc(p_date_earned,'MM') then
350 l_applied_mth_old := l_applied_mth_one_day_before;
351 if nvl(l_applied_mth,'000101') <> nvl(l_applied_mth_one_day_before,'000101') then
352 l_std_mth_comp_old := pay_jp_balance_pkg.get_entry_value_char(p_old_std_mth_comp_iv_id,p_assignment_id,p_effective_date);
353 else
354 if l_applied_mth is not null and l_applied_mth_one_day_before is not null then
355
356 l_std_mth_comp_old := pay_jp_balance_pkg.get_entry_value_char(p_new_std_mth_comp_iv_id,p_assignment_id,p_date_earned);
357 end if;
358 end if;
359 else
360 open csr_past_std_mth_comp;
361 loop
362 fetch csr_past_std_mth_comp into l_csr_past_std_mth_comp;
363 exit when csr_past_std_mth_comp%notfound;
364
365
366
367 if trunc(to_date(nvl(l_csr_past_std_mth_comp.applied_mth,'000101')||'01','YYYYMMDD'),'MM')
368 < trunc(p_date_earned,'MM') then
369 l_applied_mth_old := l_csr_past_std_mth_comp.applied_mth;
370 if l_applied_mth_old is not null then
371 l_std_mth_comp_old := l_csr_past_std_mth_comp.new_std_mth_comp;
372 end if;
373 exit;
374 end if;
375 end loop;
376 close csr_past_std_mth_comp;
377 end if;
378 if l_applied_mth_old is not null then
379 p_latest_applied_date := to_date(l_applied_mth_old||'01','YYYYMMDD');
380 else
381 p_latest_applied_date := null;
382 end if;
383 p_latest_std_mth_comp := l_std_mth_comp_old;
384 end get_latest_std_mth_comp_info;
385
386 function get_global_value(
387 p_global_name in varchar2,
388 p_business_group_id in number,
389 p_legislation_code in varchar2,
390 p_effective_date in date) return number
391 is
392 l_global_id ff_globals_f.global_id%type;
393 l_global_value ff_globals_f.global_value%type;
394 cursor get_global_value
395 is
396 select to_number(decode(fg.data_type,'N',fg.global_value,null))
397 from ff_globals_f fg
398 where fg.global_id = l_global_id
399 and p_effective_date
400 between fg.effective_start_date and fg.effective_end_date;
401 function get_global_id(
402 p_global_name in varchar2,
403 p_business_group_id in number,
404 p_legislation_code in varchar2)
405 return number
406 is
407 l_global_id number;
408 cursor get_global_id
409 is
410 select min(global_id)
411 from ff_globals_f fg
412 where fg.global_name = p_global_name
413 and nvl(fg.business_group_id,nvl(p_business_group_id,-1)) = nvl(p_business_group_id,-1)
414 and nvl(fg.legislation_code,nvl(p_legislation_code,'JP')) = nvl(p_legislation_code,'JP');
415 begin
416 open get_global_id;
417 fetch get_global_id into l_global_id;
418 if get_global_id%notfound then
419 l_global_id := null;
420 end if;
421 close get_global_id;
422 return l_global_id;
423 end get_global_id;
424 begin
425 l_global_id := get_global_id(
426 p_global_name => p_global_name,
427 p_business_group_id => p_business_group_id,
428 p_legislation_code => p_legislation_code);
429 if l_global_id is not null then
430 open get_global_value;
431 fetch get_global_value into l_global_value;
432 if get_global_value%notfound then
433 l_global_value := null;
434 end if;
435 close get_global_value;
436 else
437 l_global_value := null;
438 end if;
439 return l_global_value;
440 end get_global_value;
441
442 --Functions to refer Oracle report placeholders--
443
444 Function CP_EMP_FAILURE_ITEM_p return varchar2 is
445 Begin
446 return CP_EMP_FAILURE_ITEM;
447 END;
448 Function CP_RECORD_NUMBER_p return number is
449 Begin
450 return CP_RECORD_NUMBER;
451 END;
452 Function CP_BLOCK_SERIAL_NUMBER_p return number is
453 Begin
454 return CP_BLOCK_SERIAL_NUMBER;
455 END;
456 Function CP_SI_SEX_p return varchar2 is
457 Begin
458 return CP_SI_SEX;
459 END;
460 Function CP_SI_SEX_CODE_p return varchar2 is
461 Begin
462 return CP_SI_SEX_CODE;
463 END;
464 Function CP_DISQUALIFIED_p return varchar2 is
465 Begin
466 return CP_DISQUALIFIED;
467 END;
468 Function CP_GEPPEN_MTH_p return varchar2 is
469 Begin
470 return CP_GEPPEN_MTH;
471 END;
472 Function CP_APPLIED_DATE_p return varchar2 is
473 Begin
474 return CP_APPLIED_DATE;
475 END;
476 Function CP_PART_TIMER_FLAG_p return varchar2 is
477 Begin
478 return CP_PART_TIMER_FLAG;
479 END;
480 Function CP_WP_STD_MONTHLY_COMP_NEW_p return varchar2 is
481 Begin
482 return CP_WP_STD_MONTHLY_COMP_NEW;
483 END;
484 Function CP_WP_STD_MONTHLY_COMP_OLD_p return varchar2 is
485 Begin
486 return CP_WP_STD_MONTHLY_COMP_OLD;
487 END;
488 Function CP_PAYMENT_DAYS_MAY_p return number is
489 Begin
490 return CP_PAYMENT_DAYS_MAY;
491 END;
492 Function CP_PAYMENT_DAYS_JUN_p return number is
493 Begin
494 return CP_PAYMENT_DAYS_JUN;
495 END;
496 Function CP_PAYMENT_DAYS_JUL_p return number is
497 Begin
498 return CP_PAYMENT_DAYS_JUL;
499 END;
500 Function CP_SIMPLE_COMP_MAY_p return number is
501 Begin
502 return CP_SIMPLE_COMP_MAY;
503 END;
504 Function CP_SIMPLE_COMP_JUN_p return number is
505 Begin
506 return CP_SIMPLE_COMP_JUN;
507 END;
508 Function CP_SIMPLE_COMP_JUL_p return number is
509 Begin
510 return CP_SIMPLE_COMP_JUL;
511 END;
512 Function CP_SIMPLE_MTR_COMP_MAY_p return number is
513 Begin
514 return CP_SIMPLE_MTR_COMP_MAY;
515 END;
516 Function CP_SIMPLE_MTR_COMP_JUN_p return number is
517 Begin
518 return CP_SIMPLE_MTR_COMP_JUN;
519 END;
520 Function CP_SIMPLE_MTR_COMP_JUL_p return number is
521 Begin
522 return CP_SIMPLE_MTR_COMP_JUL;
523 END;
524 Function CP_SIMPLE_COMP_TOTAL_MAY_p return number is
525 Begin
526 return CP_SIMPLE_COMP_TOTAL_MAY;
527 END;
528 Function CP_SIMPLE_COMP_TOTAL_JUN_p return number is
529 Begin
530 return CP_SIMPLE_COMP_TOTAL_JUN;
531 END;
532 Function CP_SIMPLE_COMP_TOTAL_JUL_p return number is
533 Begin
534 return CP_SIMPLE_COMP_TOTAL_JUL;
535 END;
536 Function CP_SIMPLE_COMP_TOTAL_p return number is
537 Begin
538 return CP_SIMPLE_COMP_TOTAL;
539 END;
540 Function CP_SIMPLE_COMP_AVG_p return number is
541 Begin
542 return CP_SIMPLE_COMP_AVG;
543 END;
544 Function CP_ADJUSTED_COMP_AVG_p return number is
545 Begin
546 return CP_ADJUSTED_COMP_AVG;
547 END;
548 Function CP_REPORT_TITLE_p return varchar2 is
549 Begin
550 return CP_REPORT_TITLE;
551 END;
552 Function CP_MAGFILE_NAME_p return varchar2 is
553 Begin
554 return CP_MAGFILE_NAME;
555 END;
556 Function CP_TOTAL_CNT_p return number is
557 Begin
558 return CP_TOTAL_CNT;
559 END;
560 Function CP_SANTEI_CNT_p return number is
561 Begin
562 return CP_SANTEI_CNT;
563 END;
564 Function CP_GEPPEN_AUG_CNT_p return number is
565 Begin
566 return CP_GEPPEN_AUG_CNT;
567 END;
568 Function CP_GEPPEN_SEP_CNT_p return number is
569 Begin
570 return CP_GEPPEN_SEP_CNT;
571 END;
572 Function CP_GEPPEN_OCT_CNT_p return number is
573 Begin
574 return CP_GEPPEN_OCT_CNT;
575 END;
576 Function CP_DISQUALIFIED_CNT_p return number is
577 Begin
578 return CP_DISQUALIFIED_CNT;
579 END;
580 Function CP_DATE_EARNED_BEFORE return varchar2 is
581 l_tempdate varchar2(20):='01'||'-APR-'||'2003';
582 Begin
583 if (P_DATE_EARNED < to_date(l_tempdate)) then
584 return 'BEFORE';
585 else
586 return 'AFTER';
587 end if;
588 End;
589 END PAY_PAYJPSMT_XMLP_PKG ;