[Home] [Help]
PACKAGE BODY: APPS.PAY_PAYJPGMT_XMLP_PKG
Source
1 PACKAGE BODY PAY_PAYJPGMT_XMLP_PKG AS
2 /* $Header: PAYJPGMTB.pls 120.2 2008/04/02 10:11:58 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
13 begin
14 --HR_standard.event('BEFORE REPORT');
15 P_CONC_REQUEST_ID := fnd_global.CONC_REQUEST_ID;
16
17 if P_FILE_NAME is NULL then
18 l_file_name := 'g' || lpad(to_char(P_CONC_REQUEST_ID - trunc(P_CONC_REQUEST_ID,-7)),7,'0') || '.mf';
19 else
20 l_file_name := P_FILE_NAME;
21 end if;
22 CP_MAGFILE_NAME := l_file_name;
23
24 G_BLOCK_COUNT :=1;
25 G_REC_COUNT :=0;
26
27 l_legislation_code := pay_jp_balance_pkg.get_legislation_code(P_BUSINESS_GROUP_ID);
28
29 l_element_type_id := pay_jp_balance_pkg.get_element_type_id('COM_SI_INFO',P_BUSINESS_GROUP_ID,l_legislation_code);
30
31 G_SI_SEX_IV_ID := pay_jp_balance_pkg.get_input_value_id(l_element_type_id,'SI_SEX');
32
33 l_element_type_id := pay_jp_balance_pkg.get_element_type_id('COM_WP_SMR_INFO',P_BUSINESS_GROUP_ID,l_legislation_code);
34 G_WP_OLD_COMP_IV_ID := pay_jp_balance_pkg.get_input_value_id(l_element_type_id,'PRIOR_SMR');
35 G_WP_NEW_COMP_IV_ID := pay_jp_balance_pkg.get_input_value_id(l_element_type_id,'REVISED_SMR');
36 G_WP_COMP_APPLIED_DATE_IV_ID := pay_jp_balance_pkg.get_input_value_id(l_element_type_id,'APPLY_MTH');
37
38 IF p_date_earned < TO_DATE('2003/04/01', 'YYYY/MM/DD') THEN
39 G_SANTEI_ELM_ID := pay_jp_balance_pkg.get_element_type_id('SAN_BEFORE_20030331_RSLT',P_BUSINESS_GROUP_ID,l_legislation_code);
40 G_SAN_PAYMENT_DAYS3_IV_ID := pay_jp_balance_pkg.get_input_value_id(G_SANTEI_ELM_ID,'PAY_BASE_DAYS_MAY');
41 G_SAN_PAYMENT_DAYS2_IV_ID := pay_jp_balance_pkg.get_input_value_id(G_SANTEI_ELM_ID,'PAY_BASE_DAYS_JUN');
42 G_SAN_PAYMENT_DAYS1_IV_ID := pay_jp_balance_pkg.get_input_value_id(G_SANTEI_ELM_ID,'PAY_BASE_DAYS_JUL');
43 G_SAN_SIMPLE_COMP3_IV_ID := pay_jp_balance_pkg.get_input_value_id(G_SANTEI_ELM_ID,'ERN_MONEY_MAY');
44 G_SAN_SIMPLE_COMP2_IV_ID := pay_jp_balance_pkg.get_input_value_id(G_SANTEI_ELM_ID,'ERN_MONEY_JUN');
45 G_SAN_SIMPLE_COMP1_IV_ID := pay_jp_balance_pkg.get_input_value_id(G_SANTEI_ELM_ID,'ERN_MONEY_JUL');
46 G_SAN_SIMPLE_MTR_COMP3_IV_ID := pay_jp_balance_pkg.get_input_value_id(G_SANTEI_ELM_ID,'ERN_KIND_MAY');
47 G_SAN_SIMPLE_MTR_COMP2_IV_ID := pay_jp_balance_pkg.get_input_value_id(G_SANTEI_ELM_ID,'ERN_KIND_JUN');
48 G_SAN_SIMPLE_MTR_COMP1_IV_ID := pay_jp_balance_pkg.get_input_value_id(G_SANTEI_ELM_ID,'ERN_KIND_JUL');
49 pay_jp_report_pkg.to_era( to_date(to_char(P_DATE_EARNED,'YYYY') || '1001','YYYYMMDD'),
50 l_date_era_code,
51 l_date_year,
52 l_date_month,
53 l_date_day);
54 ELSE
55 G_SANTEI_ELM_ID := pay_jp_balance_pkg.get_element_type_id('SAN_RSLT',P_BUSINESS_GROUP_ID,l_legislation_code);
56 G_SAN_PAYMENT_DAYS3_IV_ID := pay_jp_balance_pkg.get_input_value_id(G_SANTEI_ELM_ID,'PAY_BASE_DAYS_APR');
57 G_SAN_PAYMENT_DAYS2_IV_ID := pay_jp_balance_pkg.get_input_value_id(G_SANTEI_ELM_ID,'PAY_BASE_DAYS_MAY');
58 G_SAN_PAYMENT_DAYS1_IV_ID := pay_jp_balance_pkg.get_input_value_id(G_SANTEI_ELM_ID,'PAY_BASE_DAYS_JUN');
59 G_SAN_SIMPLE_COMP3_IV_ID := pay_jp_balance_pkg.get_input_value_id(G_SANTEI_ELM_ID,'ERN_MONEY_APR');
60 G_SAN_SIMPLE_COMP2_IV_ID := pay_jp_balance_pkg.get_input_value_id(G_SANTEI_ELM_ID,'ERN_MONEY_MAY');
61 G_SAN_SIMPLE_COMP1_IV_ID := pay_jp_balance_pkg.get_input_value_id(G_SANTEI_ELM_ID,'ERN_MONEY_JUN');
62 G_SAN_SIMPLE_MTR_COMP3_IV_ID := pay_jp_balance_pkg.get_input_value_id(G_SANTEI_ELM_ID,'ERN_KIND_APR');
63 G_SAN_SIMPLE_MTR_COMP2_IV_ID := pay_jp_balance_pkg.get_input_value_id(G_SANTEI_ELM_ID,'ERN_KIND_MAY');
64 G_SAN_SIMPLE_MTR_COMP1_IV_ID := pay_jp_balance_pkg.get_input_value_id(G_SANTEI_ELM_ID,'ERN_KIND_JUN');
65 pay_jp_report_pkg.to_era( to_date(to_char(P_DATE_EARNED,'YYYY') || '0901','YYYYMMDD'),
66 l_date_era_code,
67 l_date_year,
68 l_date_month,
69 l_date_day);
70 END IF;
71 G_SAN_SIMPLE_COMP_TOTAL_IV_ID:= pay_jp_balance_pkg.get_input_value_id(G_SANTEI_ELM_ID,'TOTAL');
72 G_SAN_SIMPLE_COMP_AVG_IV_ID := pay_jp_balance_pkg.get_input_value_id(G_SANTEI_ELM_ID,'SIMPLE_AVG');
73 G_SAN_ADJUSTED_COMP_AVG_IV_ID:= pay_jp_balance_pkg.get_input_value_id(G_SANTEI_ELM_ID,'CORRECT_AVG');
74
75 G_SANTEI_BIKOU_ELM_ID := pay_jp_balance_pkg.get_element_type_id('SAN_REPORT_RMKS_RSLT',P_BUSINESS_GROUP_ID,l_legislation_code);
76 G_PART_TIMER_IV_ID := pay_jp_balance_pkg.get_input_value_id(G_SANTEI_BIKOU_ELM_ID,'SHORT_TIME_WORKER_FLAG');
77
78
79 G_GEPPEN_ELM_ID := pay_jp_balance_pkg.get_element_type_id('GEP_RSLT',P_BUSINESS_GROUP_ID,l_legislation_code);
80 G_GEP_PAYMENT_DAYS3_IV_ID := pay_jp_balance_pkg.get_input_value_id(G_GEPPEN_ELM_ID,'PAY_BASE_DAYS_3MTH_AGO');
81 G_GEP_PAYMENT_DAYS2_IV_ID := pay_jp_balance_pkg.get_input_value_id(G_GEPPEN_ELM_ID,'PAY_BASE_DAYS_2MTH_AGO');
82 G_GEP_PAYMENT_DAYS1_IV_ID := pay_jp_balance_pkg.get_input_value_id(G_GEPPEN_ELM_ID,'PAY_BASE_DAYS_1MTH_AGO');
83 G_GEP_SIMPLE_COMP3_IV_ID := pay_jp_balance_pkg.get_input_value_id(G_GEPPEN_ELM_ID,'ERN_MONEY_3MTH_AGO');
84 G_GEP_SIMPLE_COMP2_IV_ID := pay_jp_balance_pkg.get_input_value_id(G_GEPPEN_ELM_ID,'ERN_MONEY_2MTH_AGO');
85 G_GEP_SIMPLE_COMP1_IV_ID := pay_jp_balance_pkg.get_input_value_id(G_GEPPEN_ELM_ID,'ERN_MONEY_1MTH_AGO');
86 G_GEP_SIMPLE_MTR_COMP3_IV_ID := pay_jp_balance_pkg.get_input_value_id(G_GEPPEN_ELM_ID,'ERN_KIND_3MTH_AGO');
87 G_GEP_SIMPLE_MTR_COMP2_IV_ID := pay_jp_balance_pkg.get_input_value_id(G_GEPPEN_ELM_ID,'ERN_KIND_2MTH_AGO');
88 G_GEP_SIMPLE_MTR_COMP1_IV_ID := pay_jp_balance_pkg.get_input_value_id(G_GEPPEN_ELM_ID,'ERN_KIND_1MTH_AGO');
89 G_GEP_SIMPLE_COMP_TOTAL_IV_ID := pay_jp_balance_pkg.get_input_value_id(G_GEPPEN_ELM_ID,'TOTAL');
90 G_GEP_SIMPLE_COMP_AVG_IV_ID := pay_jp_balance_pkg.get_input_value_id(G_GEPPEN_ELM_ID,'SIMPLE_AVG');
91 G_GEP_ADJUSTED_COMP_AVG_IV_ID := pay_jp_balance_pkg.get_input_value_id(G_GEPPEN_ELM_ID,'CORRECT_AVG');
92
93 l_date_year := l_date_year - trunc(l_date_year,-2);
94 G_SANTEI_DATE_APPLIED_YYMM := lpad(to_char(l_date_year),2,'0') || lpad(to_char(l_date_month),2,'0');
95
96 pay_jp_report_pkg.to_era( P_DATE_EARNED,
97 l_date_era_code,
98 l_date_year,
99 l_date_month,
100 l_date_day);
101 l_date_year := l_date_year - trunc(l_date_year,-2);
102 G_GEPPEN_DATE_APPLIED_YYMM := lpad(to_char(l_date_year),2,'0') || lpad(to_char(l_date_month),2,'0');
103 hr_utility.set_message(801,'PAY_JP_SANTEI');
104 g_san_message := hr_utility.get_message;
105 hr_utility.set_message(801,'PAY_JP_GEPPEN');
106 g_gep_message := hr_utility.get_message;
107 hr_utility.set_message(801,'PAY_JP_INS_SERIAL_NUM');
108 g_wp_num_message := hr_utility.get_message;
109 hr_utility.set_message(800,'HR_JP_DATE_OF_BIRTH');
110 g_birth_date_message := hr_utility.get_message;
111 hr_utility.set_message(800,'HR_JP_FULL_NAME');
112 g_full_name_message := hr_utility.get_message;
113 hr_utility.set_message(801,'PAY_JP_SI_SEX');
114 g_si_sex_message := hr_utility.get_message;
115 --FILE_IO.open_magfile;
116
117 return (TRUE);
118 end;
119
120 function cf_emp_recordformula (ASSIGNMENT_ID in number, EFFECTIVE_DATE in date, date_earned in date, APPLIED_CATEGORY in varchar2, ASSIGNMENT_ACTION_ID in number, WP_NUMBER in varchar2,
121 DATE_OF_BIRTH in date, LAST_NAME_KANA in varchar2, FIRST_NAME_KANA in varchar2) return number is
122 l_emp_failure_item VARCHAR2(100);
123 l_wp_std_monthly_comp_old varchar2(60);
124 l_wp_applied_date_old date;
125 begin
126
127 G_REC_COUNT := G_REC_COUNT + 1;
128 CP_RECORD_NUMBER := G_REC_COUNT;
129 CP_BLOCK_SERIAL_NUMBER := G_BLOCK_COUNT;
130 if G_REC_COUNT = 10 then
131 G_REC_COUNT := 0;
132 G_BLOCK_COUNT := G_BLOCK_COUNT + 1;
133 end if;
134
135 CP_SI_SEX_CODE := pay_jp_balance_pkg.get_entry_value_char(G_SI_SEX_IV_ID,ASSIGNMENT_ID,EFFECTIVE_DATE);
136 CP_SI_SEX := hr_general.decode_lookup('JP_SI_SEX',CP_SI_SEX_CODE);
137
138 get_latest_std_mth_comp_info(
139 assignment_id,
140 effective_date,
141 date_earned,
142 g_wp_comp_applied_date_iv_id,
143 g_wp_new_comp_iv_id,
144 g_wp_old_comp_iv_id,
145 l_wp_applied_date_old,
146 l_wp_std_monthly_comp_old);
147
148 IF l_wp_std_monthly_comp_old IS NULL THEN
149 cp_wp_std_monthly_comp_old := NULL;
150 ELSE
151 CP_WP_STD_MONTHLY_COMP_OLD := lpad(to_char(floor(to_number(l_wp_std_monthly_comp_old)/1000)),4,'0');
152 END IF;
153 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');
154
155 CP_TOTAL_CNT := CP_TOTAL_CNT + 1;
156
157 if APPLIED_CATEGORY = 'S' then
158 CP_SANTEI_CNT := CP_SANTEI_CNT + 1;
159
160 CP_APPLIED_CATEGORY := g_san_message;
161 CP_APPLIED_DATE := G_SANTEI_DATE_APPLIED_YYMM;
162
163 IF p_date_earned < TO_DATE('2003/04/01', 'YYYY/MM/DD') THEN
164 CP_3MTH_AGO := 5;
165 CP_2MTH_AGO := 6;
166 CP_1MTH_AGO := 7;
167 ELSE
168 CP_3MTH_AGO := 4;
169 CP_2MTH_AGO := 5;
170 CP_1MTH_AGO := 6;
171 END IF;
172
173 CP_PAYMENT_DAYS_3MTH_AGO := nvl(pay_jp_balance_pkg.get_result_value_number(G_SANTEI_ELM_ID,G_SAN_PAYMENT_DAYS3_IV_ID,ASSIGNMENT_ACTION_ID),0);
174 CP_PAYMENT_DAYS_2MTH_AGO := nvl(pay_jp_balance_pkg.get_result_value_number(G_SANTEI_ELM_ID,G_SAN_PAYMENT_DAYS2_IV_ID,ASSIGNMENT_ACTION_ID),0);
175 CP_PAYMENT_DAYS_1MTH_AGO := nvl(pay_jp_balance_pkg.get_result_value_number(G_SANTEI_ELM_ID,G_SAN_PAYMENT_DAYS1_IV_ID,ASSIGNMENT_ACTION_ID),0);
176
177 CP_SIMPLE_COMP_3MTH_AGO := nvl(pay_jp_balance_pkg.get_result_value_number(G_SANTEI_ELM_ID,G_SAN_SIMPLE_COMP3_IV_ID,ASSIGNMENT_ACTION_ID),0);
178 CP_SIMPLE_COMP_2MTH_AGO := nvl(pay_jp_balance_pkg.get_result_value_number(G_SANTEI_ELM_ID,G_SAN_SIMPLE_COMP2_IV_ID,ASSIGNMENT_ACTION_ID),0);
179 CP_SIMPLE_COMP_1MTH_AGO := nvl(pay_jp_balance_pkg.get_result_value_number(G_SANTEI_ELM_ID,G_SAN_SIMPLE_COMP1_IV_ID,ASSIGNMENT_ACTION_ID),0);
180
181 CP_SIMPLE_MTR_COMP_3MTH_AGO := nvl(pay_jp_balance_pkg.get_result_value_number(G_SANTEI_ELM_ID,G_SAN_SIMPLE_MTR_COMP3_IV_ID,ASSIGNMENT_ACTION_ID),0);
182 CP_SIMPLE_MTR_COMP_2MTH_AGO := nvl(pay_jp_balance_pkg.get_result_value_number(G_SANTEI_ELM_ID,G_SAN_SIMPLE_MTR_COMP2_IV_ID,ASSIGNMENT_ACTION_ID),0);
183 CP_SIMPLE_MTR_COMP_1MTH_AGO := nvl(pay_jp_balance_pkg.get_result_value_number(G_SANTEI_ELM_ID,G_SAN_SIMPLE_MTR_COMP1_IV_ID,ASSIGNMENT_ACTION_ID),0);
184
185 CP_SIMPLE_COMP_TOTAL_3MTH_AGO := CP_SIMPLE_COMP_3MTH_AGO + CP_SIMPLE_MTR_COMP_3MTH_AGO;
186 CP_SIMPLE_COMP_TOTAL_2MTH_AGO := CP_SIMPLE_COMP_2MTH_AGO + CP_SIMPLE_MTR_COMP_2MTH_AGO;
187 CP_SIMPLE_COMP_TOTAL_1MTH_AGO := CP_SIMPLE_COMP_1MTH_AGO + CP_SIMPLE_MTR_COMP_1MTH_AGO;
188
189 CP_SIMPLE_COMP_TOTAL := nvl(pay_jp_balance_pkg.get_result_value_number(G_SANTEI_ELM_ID,G_SAN_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_SAN_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_SAN_ADJUSTED_COMP_AVG_IV_ID,ASSIGNMENT_ACTION_ID);
194
195 CP_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');
196
197 else
198
199 CP_GEPPEN_CNT := CP_GEPPEN_CNT + 1;
200
201 CP_APPLIED_CATEGORY := g_gep_message;
202 CP_APPLIED_DATE := G_GEPPEN_DATE_APPLIED_YYMM;
203
204 CP_3MTH_AGO := to_number(to_char(add_months(P_DATE_EARNED,-3),'MM'));
205 CP_2MTH_AGO := to_number(to_char(add_months(P_DATE_EARNED,-2),'MM'));
206 CP_1MTH_AGO := to_number(to_char(add_months(P_DATE_EARNED,-1),'MM'));
207
208 CP_PAYMENT_DAYS_3MTH_AGO := nvl(pay_jp_balance_pkg.get_result_value_number(G_GEPPEN_ELM_ID,G_GEP_PAYMENT_DAYS3_IV_ID,ASSIGNMENT_ACTION_ID),0);
209 CP_PAYMENT_DAYS_2MTH_AGO := nvl(pay_jp_balance_pkg.get_result_value_number(G_GEPPEN_ELM_ID,G_GEP_PAYMENT_DAYS2_IV_ID,ASSIGNMENT_ACTION_ID),0);
210 CP_PAYMENT_DAYS_1MTH_AGO := nvl(pay_jp_balance_pkg.get_result_value_number(G_GEPPEN_ELM_ID,G_GEP_PAYMENT_DAYS1_IV_ID,ASSIGNMENT_ACTION_ID),0);
211
212 CP_SIMPLE_COMP_3MTH_AGO := nvl(pay_jp_balance_pkg.get_result_value_number(G_GEPPEN_ELM_ID,G_GEP_SIMPLE_COMP3_IV_ID,ASSIGNMENT_ACTION_ID),0);
213 CP_SIMPLE_COMP_2MTH_AGO := nvl(pay_jp_balance_pkg.get_result_value_number(G_GEPPEN_ELM_ID,G_GEP_SIMPLE_COMP2_IV_ID,ASSIGNMENT_ACTION_ID),0);
214 CP_SIMPLE_COMP_1MTH_AGO := nvl(pay_jp_balance_pkg.get_result_value_number(G_GEPPEN_ELM_ID,G_GEP_SIMPLE_COMP1_IV_ID,ASSIGNMENT_ACTION_ID),0);
215
216 CP_SIMPLE_MTR_COMP_3MTH_AGO := nvl(pay_jp_balance_pkg.get_result_value_number(G_GEPPEN_ELM_ID,G_GEP_SIMPLE_MTR_COMP3_IV_ID,ASSIGNMENT_ACTION_ID),0);
217 CP_SIMPLE_MTR_COMP_2MTH_AGO := nvl(pay_jp_balance_pkg.get_result_value_number(G_GEPPEN_ELM_ID,G_GEP_SIMPLE_MTR_COMP2_IV_ID,ASSIGNMENT_ACTION_ID),0);
218 CP_SIMPLE_MTR_COMP_1MTH_AGO := nvl(pay_jp_balance_pkg.get_result_value_number(G_GEPPEN_ELM_ID,G_GEP_SIMPLE_MTR_COMP1_IV_ID,ASSIGNMENT_ACTION_ID),0);
219
220 CP_SIMPLE_COMP_TOTAL_3MTH_AGO := CP_SIMPLE_COMP_3MTH_AGO + CP_SIMPLE_MTR_COMP_3MTH_AGO;
221 CP_SIMPLE_COMP_TOTAL_2MTH_AGO := CP_SIMPLE_COMP_2MTH_AGO + CP_SIMPLE_MTR_COMP_2MTH_AGO;
222 CP_SIMPLE_COMP_TOTAL_1MTH_AGO := CP_SIMPLE_COMP_1MTH_AGO + CP_SIMPLE_MTR_COMP_1MTH_AGO;
223
224 CP_SIMPLE_COMP_TOTAL := nvl(pay_jp_balance_pkg.get_result_value_number(G_GEPPEN_ELM_ID,G_GEP_SIMPLE_COMP_TOTAL_IV_ID,ASSIGNMENT_ACTION_ID),0);
225
226 CP_SIMPLE_COMP_AVG := nvl(pay_jp_balance_pkg.get_result_value_number(G_GEPPEN_ELM_ID,G_GEP_SIMPLE_COMP_AVG_IV_ID,ASSIGNMENT_ACTION_ID),0);
227
228 CP_ADJUSTED_COMP_AVG := pay_jp_balance_pkg.get_result_value_number(G_GEPPEN_ELM_ID,G_GEP_ADJUSTED_COMP_AVG_IV_ID,ASSIGNMENT_ACTION_ID);
229
230 CP_PART_TIMER_FLAG := ' ';
231 end if;
232
233 l_emp_failure_item := ' ';
234 if nvl(WP_NUMBER,' ') = ' ' then
235 l_emp_failure_item := l_emp_failure_item||g_wp_num_message;
236 end if;
237 if nvl(to_char(DATE_OF_BIRTH),' ') = ' ' then
238 l_emp_failure_item := l_emp_failure_item||g_birth_date_message;
239 end if;
240 if nvl(LAST_NAME_KANA,' ') = ' ' and nvl(FIRST_NAME_KANA,' ') = ' 'then
241 l_emp_failure_item := l_emp_failure_item||g_full_name_message;
242 end if;
243 if nvl(CP_SI_SEX_CODE,' ') = ' ' then
244 l_emp_failure_item := l_emp_failure_item||g_si_sex_message;
245 end if;
246 CP_EMP_FAILURE_ITEM := l_emp_failure_item;
247 return('');
248 end;
249
250 function AfterReport return boolean is
251 begin
252 if G_REC_COUNT <> 0 then
253 --FILE_IO.put('DATA');
254 --FILE_IO.put(lpad(to_char(G_BLOCK_COUNT),8,'0'));
255 --FILE_IO.put('0060');
256 --FILE_IO.put('1440');
257 --FILE_IO.put(lpad(to_char(G_REC_COUNT),2,'0'));
258 --FILE_IO.put(lpad(' ',38,' '));
259
260 --FILE_IO.put(G_ORG_REC);
261
262 --FILE_IO.put(G_EMP_REC_1);
263 --FILE_IO.put(G_EMP_REC_2);
264 --FILE_IO.put(G_EMP_REC_3);
265 --FILE_IO.put(G_EMP_REC_4);
266 --FILE_IO.put(G_EMP_REC_5);
267 --FILE_IO.put(G_EMP_REC_6);
268 --FILE_IO.put(G_EMP_REC_7);
269 --FILE_IO.put(G_EMP_REC_8);
270 --FILE_IO.put(G_EMP_REC_9);
271 --FILE_IO.put(G_EMP_REC_10);
272 NULL;
273 end if;
274
275 if G_REC_COUNT > 0 or G_BLOCK_COUNT > 1 then
276 --FILE_IO.put('TCTL');
277 --FILE_IO.put('0');
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(' ',250,' '));
283 --FILE_IO.put(lpad(' ',245,' '));
284 NULL;
285 end if;
286 --FILE_IO.close_magfile;
287 --HR_standard.event('AFTER REPORT');
288 return (TRUE);
289 end;
290
291 pROCEDURE get_latest_std_mth_comp_info(p_assignment_id IN NUMBER, p_effective_date IN DATE, p_date_earned IN DATE, p_applied_mth_iv_id IN NUMBER, p_new_std_mth_comp_iv_id IN NUMBER,
292 p_old_std_mth_comp_iv_id IN NUMBER, p_latest_applied_date OUT nocopy DATE, p_latest_std_mth_comp OUT nocopy VARCHAR2) IS
293
294 CURSOR csr_past_std_mth_comp IS
295 SELECT pee.element_entry_id ee_id,
296 pee.effective_start_date ee_esd,
297 pee.effective_end_date ee_eed,
298 peev1.screen_entry_value applied_mth,
299 peev2.screen_entry_value new_std_mth_comp
300 FROM pay_element_entry_values_f peev2,
301 pay_element_entry_values_f peev1,
302 pay_link_input_values_f pliv2,
303 pay_link_input_values_f pliv1,
304 pay_element_entries_f pee
305 WHERE pee.assignment_id = p_assignment_id
306
307 AND pee.effective_start_date < p_effective_date
308 AND pliv1.element_link_id = pee.element_link_id
309 AND pliv2.element_link_id = pee.element_link_id
310 AND pliv1.input_value_id = p_applied_mth_iv_id
311 AND pliv2.input_value_id = p_new_std_mth_comp_iv_id
312 AND pee.effective_start_date BETWEEN pliv1.effective_start_date
313 AND pliv1.effective_end_date
314 AND pee.effective_start_date BETWEEN pliv2.effective_start_date
315 AND pliv2.effective_end_date
316 AND peev1.element_entry_id = pee.element_entry_id
317 AND peev1.input_value_id = pliv1.input_value_id
318 AND peev1.effective_start_date = pee.effective_start_date
319 AND peev1.effective_end_date = pee.effective_end_date
320 AND peev2.element_entry_id = pee.element_entry_id
321 AND peev2.input_value_id = pliv2.input_value_id
322 AND peev2.effective_start_date = pee.effective_start_date
323 AND peev2.effective_end_date = pee.effective_end_date
324 ORDER BY pee.effective_start_date;
325 l_csr_past_std_mth_comp csr_past_std_mth_comp % rowtype;
326 l_applied_mth_one_day_before VARCHAR2(60);
327 l_applied_mth VARCHAR2(60);
328 l_applied_mth_old VARCHAR2(60);
329 l_std_mth_comp_old VARCHAR2(60);
330 --pragma autonomous_transaction;
331 BEGIN
332 l_applied_mth := pay_jp_balance_pkg.get_entry_value_char(p_applied_mth_iv_id, p_assignment_id, p_effective_date);
333 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);
334
335 IF TRUNC(to_date(nvl(l_applied_mth_one_day_before, '000101') || '01', 'YYYYMMDD'), 'MM') < TRUNC(p_date_earned, 'MM') THEN
336 l_applied_mth_old := l_applied_mth_one_day_before;
337
338 IF nvl(l_applied_mth, '000101') <> nvl(l_applied_mth_one_day_before, '000101') THEN
339 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);
340
341 ELSE
342
343 IF l_applied_mth IS NOT NULL
344 AND l_applied_mth_one_day_before IS NOT NULL THEN
345
346 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);
347 END IF;
348
349 END IF;
350
351
352
353 ELSE
354
355 OPEN csr_past_std_mth_comp;
356 LOOP
357 FETCH csr_past_std_mth_comp
358 INTO l_csr_past_std_mth_comp;
359 EXIT
360 WHEN csr_past_std_mth_comp % NOTFOUND;
361
362 IF TRUNC(to_date(nvl(l_csr_past_std_mth_comp.applied_mth, '000101') || '01', 'YYYYMMDD'), 'MM') < TRUNC(p_date_earned, 'MM') THEN
363 l_applied_mth_old := l_csr_past_std_mth_comp.applied_mth;
364
365 IF l_applied_mth_old IS NOT NULL THEN
366 l_std_mth_comp_old := l_csr_past_std_mth_comp.new_std_mth_comp;
367 END IF;
368 EXIT;
369 END IF;
370
371 END LOOP;
372
373 CLOSE csr_past_std_mth_comp;
374 END IF;
375
376 IF l_applied_mth_old IS NOT NULL THEN
377 p_latest_applied_date := to_date(l_applied_mth_old || '01', 'YYYYMMDD');
378 ELSE
379 p_latest_applied_date := NULL;
380 END IF;
381
382 p_latest_std_mth_comp := l_std_mth_comp_old;
383 END get_latest_std_mth_comp_info;
384
385
386 --Functions to refer Oracle report placeholders--
387
388 Function CP_EMP_FAILURE_ITEM_p return varchar2 is
389 Begin
390 return CP_EMP_FAILURE_ITEM;
391 END;
392 Function CP_RECORD_NUMBER_p return number is
393 Begin
394 return CP_RECORD_NUMBER;
395 END;
396 Function CP_BLOCK_SERIAL_NUMBER_p return number is
397 Begin
398 return CP_BLOCK_SERIAL_NUMBER;
399 END;
400 Function CP_SI_SEX_p return varchar2 is
401 Begin
402 return CP_SI_SEX;
403 END;
404 Function CP_SI_SEX_CODE_p return varchar2 is
405 Begin
406 return CP_SI_SEX_CODE;
407 END;
408 Function CP_APPLIED_DATE_p return varchar2 is
409 Begin
410 return CP_APPLIED_DATE;
411 END;
412 Function CP_APPLIED_CATEGORY_p return varchar2 is
413 Begin
414 return CP_APPLIED_CATEGORY;
415 END;
416 Function CP_PART_TIMER_FLAG_p return varchar2 is
417 Begin
418 return CP_PART_TIMER_FLAG;
419 END;
420 Function CP_WP_STD_MONTHLY_COMP_NEW_p return varchar2 is
421 Begin
422 return CP_WP_STD_MONTHLY_COMP_NEW;
423 END;
424 Function CP_WP_STD_MONTHLY_COMP_OLD_p return varchar2 is
425 Begin
426 return CP_WP_STD_MONTHLY_COMP_OLD;
427 END;
428 Function CP_3MTH_AGO_p return number is
429 Begin
430 return CP_3MTH_AGO;
431 END;
432 Function CP_2MTH_AGO_p return number is
433 Begin
434 return CP_2MTH_AGO;
435 END;
436 Function CP_1MTH_AGO_p return number is
437 Begin
438 return CP_1MTH_AGO;
439 END;
440 Function CP_PAYMENT_DAYS_3MTH_AGO_p return number is
441 Begin
442 return CP_PAYMENT_DAYS_3MTH_AGO;
443 END;
444 Function CP_PAYMENT_DAYS_2MTH_AGO_p return number is
445 Begin
446 return CP_PAYMENT_DAYS_2MTH_AGO;
447 END;
448 Function CP_PAYMENT_DAYS_1MTH_AGO_p return number is
449 Begin
450 return CP_PAYMENT_DAYS_1MTH_AGO;
451 END;
452 Function CP_SIMPLE_COMP_3MTH_AGO_p return number is
453 Begin
454 return CP_SIMPLE_COMP_3MTH_AGO;
455 END;
456 Function CP_SIMPLE_COMP_2MTH_AGO_p return number is
457 Begin
458 return CP_SIMPLE_COMP_2MTH_AGO;
459 END;
460 Function CP_SIMPLE_COMP_1MTH_AGO_p return number is
461 Begin
462 return CP_SIMPLE_COMP_1MTH_AGO;
463 END;
464 Function CP_SIMPLE_MTR_COMP_3MTH_AGO_p return number is
465 Begin
466 return CP_SIMPLE_MTR_COMP_3MTH_AGO;
467 END;
468 Function CP_SIMPLE_MTR_COMP_2MTH_AGO_p return number is
469 Begin
470 return CP_SIMPLE_MTR_COMP_2MTH_AGO;
471 END;
472 Function CP_SIMPLE_MTR_COMP_1MTH_AGO_p return number is
473 Begin
474 return CP_SIMPLE_MTR_COMP_1MTH_AGO;
475 END;
476 Function CP_SIMPLE_COMP_TOTAL_3MTH_AGO_ return number is
477 Begin
478 return CP_SIMPLE_COMP_TOTAL_3MTH_AGO;
479 END;
480 Function CP_SIMPLE_COMP_TOTAL_2MTH_AGO_ return number is
481 Begin
482 return CP_SIMPLE_COMP_TOTAL_2MTH_AGO;
483 END;
484 Function CP_SIMPLE_COMP_TOTAL_1MTH_AGO_ return number is
485 Begin
486 return CP_SIMPLE_COMP_TOTAL_1MTH_AGO;
487 END;
488 Function CP_SIMPLE_COMP_TOTAL_p return number is
489 Begin
490 return CP_SIMPLE_COMP_TOTAL;
491 END;
492 Function CP_SIMPLE_COMP_AVG_p return number is
493 Begin
494 return CP_SIMPLE_COMP_AVG;
495 END;
496 Function CP_ADJUSTED_COMP_AVG_p return number is
497 Begin
498 return CP_ADJUSTED_COMP_AVG;
499 END;
500 Function CP_REPORT_TITLE_p return varchar2 is
501 Begin
502 return CP_REPORT_TITLE;
503 END;
504 Function CP_MAGFILE_NAME_p return varchar2 is
505 Begin
506 return CP_MAGFILE_NAME;
507 END;
508 Function CP_TOTAL_CNT_p return number is
509 Begin
510 return CP_TOTAL_CNT;
511 END;
512 Function CP_SANTEI_CNT_p return number is
513 Begin
514 return CP_SANTEI_CNT;
515 END;
516 Function CP_GEPPEN_CNT_p return number is
517 Begin
518 return CP_GEPPEN_CNT;
519 END;
520 END PAY_PAYJPGMT_XMLP_PKG ;