eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.PAY_PAYJPSQD_XMLP_PKG

Source


1 PACKAGE BODY PAY_PAYJPSQD_XMLP_PKG AS
2 /* $Header: PAYJPSQDB.pls 120.1 2008/04/01 09:47:40 vjaganat noship $ */
3 
4 function BeforeReport  return boolean is
5 	l_date_era_code		NUMBER;
6 	l_date_year				NUMBER;
7 	l_date_month			NUMBER;
8 	l_date_day				NUMBER;
9 	l_element_type_id	NUMBER;
10 	l_legislation_code	PER_BUSINESS_GROUPS.LEGISLATION_CODE%TYPE;
11 begin
12  -- hr_standard.event('BEFORE REPORT');
13  P_CONC_REQUEST_ID := fnd_global.CONC_REQUEST_ID;
14 	G_REC_COUNT				:=0;
15 	G_FD_COUNT					:=0;
16 	G_FD_SERIAL_NUMBER	:=0;
17 	G_COUNT_OF_RECORD	:=0;
18 	G_HEADER_OUT				:=0;
19 
20 	CP_REPORT_TITLE := 'Notification of Qualfication Checklist';
21 
22 	if P_FILE_NAME is NULL then
23 		g_magfile_name := 'q' || lpad(to_char(P_CONC_REQUEST_ID - trunc(P_CONC_REQUEST_ID,-7)),7,'0') || '.mf';
24 	else
25 		g_magfile_name := P_FILE_NAME;
26 	end if;
27 
28 	pay_jp_report_pkg.to_era(	P_PREPARE_DATE,
29 					l_date_era_code,
30 					l_date_year,
31 					l_date_month,
32 					l_date_day);
33 	l_date_year := l_date_year - trunc(l_date_year,-2);
34 	G_PREPARE_DATE_YEAR	:= lpad(to_char(l_date_year),2,'0');
35 	G_PREPARE_DATE_MONTH	:= lpad(to_char(l_date_month),2,'0');
36 	G_PREPARE_DATE_DAY	:= lpad(to_char(l_date_day),2,'0');
37 	l_legislation_code := pay_jp_balance_pkg.get_legislation_code(P_BUSINESS_GROUP_ID);
38 	l_element_type_id := pay_jp_balance_pkg.get_element_type_id('COM_SI_INFO',P_BUSINESS_GROUP_ID,l_legislation_code);
39 	G_BASIC_PENSION_NUMBER_IV_ID	:= pay_jp_balance_pkg.get_input_value_id(l_element_type_id,'BASIC_PENSION_NUM');
40 	G_SI_SEX_IV_ID			:= pay_jp_balance_pkg.get_input_value_id(l_element_type_id,'SI_SEX');
41 	l_element_type_id := pay_jp_balance_pkg.get_element_type_id('COM_SI_MR_AT_QUALIFY_INFO',P_BUSINESS_GROUP_ID,l_legislation_code);
42 	G_SALARY_IV_ID			:= pay_jp_balance_pkg.get_input_value_id(l_element_type_id,'ERN_MONEY');
43 	G_MATERIAL_SALARY_IV_ID		:= pay_jp_balance_pkg.get_input_value_id(l_element_type_id,'ERN_KIND');
44 	l_element_type_id := pay_jp_balance_pkg.get_element_type_id('COM_WP_SMR_INFO',P_BUSINESS_GROUP_ID,l_legislation_code);
45 	G_WP_COMP_DATE_APPLIED_IV_ID	:= pay_jp_balance_pkg.get_input_value_id(l_element_type_id,'APPLY_MTH');
46 	G_WP_NEW_COMP_IV_ID		:= pay_jp_balance_pkg.get_input_value_id(l_element_type_id,'REVISED_SMR');
47 	G_WP_OLD_COMP_IV_ID		:= pay_jp_balance_pkg.get_input_value_id(l_element_type_id,'PRIOR_SMR');
48 	l_element_type_id := pay_jp_balance_pkg.get_element_type_id('COM_SI_PENSION_BOOK_NUM_INFO',P_BUSINESS_GROUP_ID,l_legislation_code);
49 	G_KOUSEI_NUMBER_1_IV_ID	:= pay_jp_balance_pkg.get_input_value_id(l_element_type_id,'WP_BOOK_OFFICE_NUM');
50 	G_KOUSEI_NUMBER_2_IV_ID	:= pay_jp_balance_pkg.get_input_value_id(l_element_type_id,'WP_BOOK_NUM');
51 	G_KOKUMIN_NUMBER_1_IV_ID	:= pay_jp_balance_pkg.get_input_value_id(l_element_type_id,'NP_BOOK_OFFICE_NUM');
52 	G_KOKUMIN_NUMBER_2_IV_ID	:= pay_jp_balance_pkg.get_input_value_id(l_element_type_id,'NP_BOOK_NUM');
53 	G_SENIN_NUMBER_1_IV_ID	:= pay_jp_balance_pkg.get_input_value_id(l_element_type_id,'SAILOR_INS_BOOK_OFFICE_NUM');
54 	G_SENIN_NUMBER_2_IV_ID	:= pay_jp_balance_pkg.get_input_value_id(l_element_type_id,'SAILOR_INS_BOOK_NUM');
55 	return (TRUE);
56 end;
57 
58 function cf_emp_recordformula (TOTAL_REC in number, ASSIGNMENT_ID in number, EFFECTIVE_DATE in date, ADDRESS_KANA1 in varchar2, DISTRICT_NAME_KANA in varchar2, ADDRESS_KANA2_3 in varchar2, ADDRESS1 in varchar2
59 ,ADDRESS2_3 in varchar2,DATE_QUALIFIED in date,WP_NUMBER in varchar2,DATE_OF_BIRTH in date,ADDRESS_COUNTRY in varchar2, POSTAL_CODE in varchar2) return number is
60 	l_address_kana				VARCHAR2(240);
61 	l_address							VARCHAR2(240);
62 	l_date_applied				VARCHAR2(6);
63 	l_wp_std_monthly_comp	VARCHAR2(38);
64 	l_iv_id								NUMBER;
65 	l_emp_failure_item		VARCHAR2(100);
66 
67 begin
68 	G_REC_COUNT := G_REC_COUNT  + 1;
69 
70 	if G_FD_COUNT  <> trunc((G_REC_COUNT -1)/2500)+1 then
71 		G_FD_COUNT  := trunc((G_REC_COUNT -1)/2500)+1;
72 		if G_FD_SERIAL_NUMBER  = 0 then
73 			G_FD_SERIAL_NUMBER  := P_FD_SERIAL_NUMBER;
74 			CP_MAGFILE_NAME := g_magfile_name;
75 		else
76  					G_FD_SERIAL_NUMBER  := G_FD_SERIAL_NUMBER  + 1;
77 			if G_FD_SERIAL_NUMBER  > 999 then
78 				G_FD_SERIAL_NUMBER  := 1;
79 			end if;
80 			CP_MAGFILE_NAME := CP_MAGFILE_NAME || ', ' || g_magfile_name || to_char(G_FD_COUNT);
81 		end if;
82 
83   	if G_FD_COUNT*2500 <= TOTAL_REC then
84 			G_COUNT_OF_RECORD := G_FD_COUNT*2500 - (G_FD_COUNT-1)*2500;
85 		else
86 			G_COUNT_OF_RECORD := TOTAL_REC - (G_FD_COUNT-1)*2500;
87 		end if;
88 
89 	end if;
90 
91 	CP_RECORD_NUMBER := G_REC_COUNT  - (G_FD_COUNT -1)*2500;
92 	CP_COUNT_OF_RECORD := G_COUNT_OF_RECORD;
93 	CP_FD_SERIAL_NUMBER := lpad(to_char(G_FD_SERIAL_NUMBER),3,'0');
94 	CP_FD_COUNT := G_FD_COUNT;
95 	CP_BASIC_PENSION_NUMBER := pay_jp_report_pkg.substrb2(pay_jp_balance_pkg.get_entry_value_char(g_basic_pension_number_iv_id,ASSIGNMENT_ID,EFFECTIVE_DATE),1,10);
96 
97 	CP_SI_SEX_CODE := pay_jp_balance_pkg.get_entry_value_char(g_si_sex_iv_id,ASSIGNMENT_ID,EFFECTIVE_DATE);
98 	CP_SI_SEX := hr_general.decode_lookup('JP_SI_SEX',CP_SI_SEX_CODE);
99 
100 	l_address_kana := pay_jp_report_pkg.substrb2((pay_jp_report_pkg.set_space_on_address(ADDRESS_KANA1,DISTRICT_NAME_KANA,1)||' '||ADDRESS_KANA2_3),1,240);
101 	l_address_kana := replace(replace(l_address_kana,hr_jp_standard_pkg.sjhextochar('8140'),' '),'  ',' ');
102 	CP_ADDRESS_KANA := l_address_kana;
103 	l_address := replace(pay_jp_report_pkg.substrb2((pay_jp_report_pkg.set_space_on_address(ADDRESS1,'',0)||' '||ADDRESS2_3),1,240),'  ',' ');
104 	CP_ADDRESS := l_address;
105 
106 	CP_KOUSEI_NENKIN_NUMBER1 := NULL;
107 	CP_KOUSEI_NENKIN_NUMBER2 := NULL;
108 	CP_KOKUMIN_NENKIN_NUMBER1 := NULL;
109 	CP_KOKUMIN_NENKIN_NUMBER2 := NULL;
110 	CP_SENIN_HOKEN_NUMBER1 := NULL;
111 	CP_SENIN_HOKEN_NUMBER2 := NULL;
112 
113 	CP_KOUSEI_NENKIN_NUMBER1 := pay_jp_balance_pkg.get_entry_value_char(g_kousei_number_1_iv_id,ASSIGNMENT_ID,EFFECTIVE_DATE);
114 	CP_KOUSEI_NENKIN_NUMBER2 := pay_jp_balance_pkg.get_entry_value_char(g_kousei_number_2_iv_id,ASSIGNMENT_ID,EFFECTIVE_DATE);
115 	CP_KOKUMIN_NENKIN_NUMBER1 := pay_jp_balance_pkg.get_entry_value_char(g_kokumin_number_1_iv_id,ASSIGNMENT_ID,EFFECTIVE_DATE);
116 	CP_KOKUMIN_NENKIN_NUMBER2 := pay_jp_balance_pkg.get_entry_value_char(g_kokumin_number_2_iv_id,ASSIGNMENT_ID,EFFECTIVE_DATE);
117 	CP_SENIN_HOKEN_NUMBER1 := pay_jp_balance_pkg.get_entry_value_char(g_senin_number_1_iv_id,ASSIGNMENT_ID,EFFECTIVE_DATE);
118 	CP_SENIN_HOKEN_NUMBER2 := pay_jp_balance_pkg.get_entry_value_char(g_senin_number_2_iv_id,ASSIGNMENT_ID,EFFECTIVE_DATE);
119 
120 	CP_SALARY := nvl(pay_jp_balance_pkg.get_entry_value_number(g_salary_iv_id,ASSIGNMENT_ID,EFFECTIVE_DATE),0);
121 	CP_MATERIAL_SALARY := nvl(pay_jp_balance_pkg.get_entry_value_number(g_material_salary_iv_id,ASSIGNMENT_ID,EFFECTIVE_DATE),0);
122 	CP_SALARY_SUM := CP_SALARY + CP_MATERIAL_SALARY;
123 
124 	CP_WP_STD_MONTHLY_COMP := NULL;
125 
126 	l_date_applied := nvl(pay_jp_balance_pkg.get_entry_value_char(g_wp_comp_date_applied_iv_id,ASSIGNMENT_ID,EFFECTIVE_DATE),'000101');
127 	if to_number(to_char(DATE_QUALIFIED,'YYYYMM')) - to_number(l_date_applied) < 0 then
128 		l_iv_id := g_wp_old_comp_iv_id;
129 	else
130 		l_iv_id := g_wp_new_comp_iv_id;
131 	end if;
132 	l_wp_std_monthly_comp := pay_jp_report_pkg.substrb2(to_char(pay_jp_balance_pkg.get_entry_value_number(l_iv_id,ASSIGNMENT_ID,EFFECTIVE_DATE)),1,38);
133 	if nvl(l_wp_std_monthly_comp,' ') <> ' ' then
134 		CP_WP_STD_MONTHLY_COMP := lpad(to_char(floor(to_number(l_wp_std_monthly_comp)/1000)),4,'0');
135 	end if;
136 	l_emp_failure_item := ' ';
137 	if nvl(WP_NUMBER,' ') = ' ' then
138 		l_emp_failure_item := l_emp_failure_item||fnd_message.get_string('PAY','PAY_JP_INS_SERIAL_NUM');
139 	end if;
140 	if nvl(to_char(DATE_OF_BIRTH),' ') = ' ' then
141 		l_emp_failure_item := l_emp_failure_item||fnd_message.get_string('PAY','HR_JP_DATE_OF_BIRTH');
142 	end if;
143 	if nvl(CP_SI_SEX_CODE,' ') = ' ' then
144 		l_emp_failure_item := l_emp_failure_item||fnd_message.get_string('PAY','PAY_JP_SI_SEX');
145 	end if;
146 	if nvl(ADDRESS_COUNTRY,'JP') = 'JP' then
147 		if nvl(POSTAL_CODE,' ') = ' ' then
148 			l_emp_failure_item := l_emp_failure_item||fnd_message.get_string('PAY','PAY_JP_POSTAL_CODE');
149 		end if;
150 		if nvl(CP_ADDRESS_KANA,' ') = ' ' then
151 			l_emp_failure_item := l_emp_failure_item||fnd_message.get_string('PAY','PAY_JP_KANA_ADDRESS');
152 		end if;
153 	end if;
154 	if CP_SALARY_SUM = 0 then
155 		l_emp_failure_item := l_emp_failure_item||fnd_message.get_string('PAY','PAY_JP_AMOUNT_MONEY_AND_KIND');
156 	end if;
157 
158 	CP_EMP_FAILURE_ITEM := l_emp_failure_item;
159 	return('');
160 end;
161 
162 function AfterReport  return boolean is
163 begin
164  -- file_io.close_magfile;
165  -- hr_standard.event('AFTER REPORT');
166 	return (TRUE);
167 end;
168 
169 --Functions to refer Oracle report placeholders--
170 
171  Function CP_WP_STD_MONTHLY_COMP_p return varchar2 is
172 	Begin
173 	 return CP_WP_STD_MONTHLY_COMP;
174 	 END;
175  Function CP_SALARY_p return number is
176 	Begin
177 	 return CP_SALARY;
178 	 END;
179  Function CP_MATERIAL_SALARY_p return number is
180 	Begin
181 	 return CP_MATERIAL_SALARY;
182 	 END;
183  Function CP_SALARY_SUM_p return number is
184 	Begin
185 	 return CP_SALARY_SUM;
186 	 END;
187  Function CP_SI_SEX_p return varchar2 is
188 	Begin
189 	 return CP_SI_SEX;
190 	 END;
191  Function CP_SI_SEX_CODE_p return varchar2 is
192 	Begin
193 	 return CP_SI_SEX_CODE;
194 	 END;
195  Function CP_BASIC_PENSION_NUMBER_p return varchar2 is
196 	Begin
197 	 return CP_BASIC_PENSION_NUMBER;
198 	 END;
199  Function CP_KOUSEI_NENKIN_NUMBER1_p return varchar2 is
200 	Begin
201 	 return CP_KOUSEI_NENKIN_NUMBER1;
202 	 END;
203  Function CP_KOUSEI_NENKIN_NUMBER2_p return varchar2 is
204 	Begin
205 	 return CP_KOUSEI_NENKIN_NUMBER2;
206 	 END;
207  Function CP_KOKUMIN_NENKIN_NUMBER1_p return varchar2 is
208 	Begin
209 	 return CP_KOKUMIN_NENKIN_NUMBER1;
210 	 END;
211  Function CP_KOKUMIN_NENKIN_NUMBER2_p return varchar2 is
212 	Begin
213 	 return CP_KOKUMIN_NENKIN_NUMBER2;
214 	 END;
215  Function CP_SENIN_HOKEN_NUMBER1_p return varchar2 is
216 	Begin
217 	 return CP_SENIN_HOKEN_NUMBER1;
218 	 END;
219  Function CP_SENIN_HOKEN_NUMBER2_p return varchar2 is
220 	Begin
221 	 return CP_SENIN_HOKEN_NUMBER2;
222 	 END;
223  Function CP_EMP_FAILURE_ITEM_p return varchar2 is
224 	Begin
225 	 return CP_EMP_FAILURE_ITEM;
226 	 END;
227  Function CP_ADDRESS_p return varchar2 is
228 	Begin
229 	 return CP_ADDRESS;
230 	 END;
231  Function CP_FD_SERIAL_NUMBER_p return varchar2 is
232 	Begin
233 	 return CP_FD_SERIAL_NUMBER;
234 	 END;
235  Function CP_COUNT_OF_RECORD_p return number is
236 	Begin
237 	 return CP_COUNT_OF_RECORD;
238 	 END;
239  Function CP_RECORD_NUMBER_p return number is
240 	Begin
241 	 return CP_RECORD_NUMBER;
242 	 END;
243  Function CP_FD_COUNT_p return number is
244 	Begin
245 	 return CP_FD_COUNT;
246 	 END;
247  Function CP_ADDRESS_KANA_p return varchar2 is
248 	Begin
249 	 return CP_ADDRESS_KANA;
250 	 END;
251  Function CP_REPORT_TITLE_p return varchar2 is
252 	Begin
253 	 return CP_REPORT_TITLE;
254 	 END;
255  Function CP_MAGFILE_NAME_p return varchar2 is
256 	Begin
257 	 return CP_MAGFILE_NAME;
258 	 END;
259 END PAY_PAYJPSQD_XMLP_PKG ;