1 PACKAGE BODY HR_JP_GENERAL_PKG AS
2 /* $Header: hrjpgen.pkb 115.8 2003/12/09 21:59:33 ttagawa ship $ */
3 --------------------------------------------------------------
4 FUNCTION GET_SESSION_DATE
5 --------------------------------------------------------------
6 RETURN DATE
7 IS
8 l_effective_date DATE;
9 CURSOR cur_effective_date IS
10 select effective_date
11 from fnd_sessions
12 where session_id=userenv('sessionid');
13 BEGIN
14 open cur_effective_date;
15 fetch cur_effective_date into l_effective_date;
16 if cur_effective_date%NOTFOUND then
17 l_effective_date:=NULL;
18 end if;
19 close cur_effective_date;
20
21 return l_effective_date;
22 END GET_SESSION_DATE;
23
24 --------------------------------------------------------------
25 FUNCTION DECODE_ORG(
26 --------------------------------------------------------------
27 P_ORGANIZATION_ID IN NUMBER)
28 RETURN VARCHAR2
29 IS
30 l_name HR_ALL_ORGANIZATION_UNITS_TL.NAME%TYPE;
31 CURSOR cur_org_name IS
32 select tl.name
33 from hr_all_organization_units_tl tl,
34 hr_all_organization_units hou
35 where hou.organization_id=p_organization_id
36 and tl.organization_id=hou.organization_id
37 and tl.language=userenv('LANG');
38 BEGIN
39 open cur_org_name;
40 fetch cur_org_name into l_name;
41 if cur_org_name%notfound then
42 l_name:=NULL;
43 end if;
44 close cur_org_name;
45
46 return l_name;
47 END DECODE_ORG;
48
49 --------------------------------------------------------------
50 FUNCTION DECODE_DISTRICT(
51 --------------------------------------------------------------
52 P_DISTRICT_CODE IN VARCHAR2)
53 RETURN VARCHAR2
54 IS
55 l_district_name PER_JP_ADDRESS_LOOKUPS.ADDRESS_LINE_1%TYPE;
56 CURSOR cur_district_name IS
57 select address_line_1
58 from per_jp_address_lookups
59 where district_code=p_district_code;
60 BEGIN
61 open cur_district_name;
62 fetch cur_district_name into l_district_name;
63 if cur_district_name%NOTFOUND then
64 l_district_name:=NULL;
65 end if;
66 close cur_district_name;
67
68 return l_district_name;
69 END DECODE_DISTRICT;
70
71 --------------------------------------------------------------
72 FUNCTION GET_ADDRESS(
73 --------------------------------------------------------------
74 P_PERSON_ID IN NUMBER,
75 P_ADDRESS_TYPE IN VARCHAR2,
76 P_EFFECTIVE_DATE IN DATE)
77 RETURN VARCHAR2
78 IS
79 l_address VARCHAR2(180);
80 CURSOR cur_address IS
81 select pad.address_line1 || pad.address_line2 || pad.address_line3
82 from per_addresses pad
83 where pad.person_id=p_person_id
84 and pad.address_type=p_address_type
85 and p_effective_date
86 between pad.date_from and nvl(pad.date_to,p_effective_date);
87 BEGIN
88 l_address := NULL;
89
90 open cur_address;
91 fetch cur_address into l_address;
92 if cur_address%NOTFOUND then
93 l_address := NULL;
94 end if;
95 close cur_address;
96
97 return l_address;
98 END GET_ADDRESS;
99
100 --------------------------------------------------------------
101 FUNCTION GET_DISTRICT_CODE(
102 --------------------------------------------------------------
103 P_PERSON_ID IN NUMBER,
104 P_ADDRESS_TYPE IN VARCHAR2,
105 P_EFFECTIVE_DATE IN DATE)
106 RETURN VARCHAR2
107 IS
108 l_district_code VARCHAR2(30);
109 CURSOR cur_district_code IS
110 select pad.town_or_city
111 from per_addresses pad
112 where pad.person_id=p_person_id
113 and pad.address_type=p_address_type
114 and p_effective_date
115 between pad.date_from and nvl(pad.date_to,p_effective_date);
116 BEGIN
117 l_district_code := NULL;
118
119 open cur_district_code;
120 fetch cur_district_code into l_district_code;
121 if cur_district_code%NOTFOUND then
122 l_district_code := NULL;
123 end if;
124 close cur_district_code;
125
126 return l_district_code;
127 END GET_DISTRICT_CODE;
128
129 --------------------------------------------------------------
130 FUNCTION RUN_ASSACT_EXISTS(
131 -- This function elapses cpu-time about 0.012 sec per 1 call.
132 --------------------------------------------------------------
133 p_assignment_id IN NUMBER,
134 p_element_set_name IN VARCHAR2,
135 p_validation_start_date IN DATE DEFAULT NULL,
136 p_validation_end_date IN DATE DEFAULT NULL,
137 p_effective_date IN DATE DEFAULT NULL) RETURN VARCHAR2
138 IS
139 l_effective_date DATE;
140 l_found VARCHAR2(5) := 'FALSE';
141
142 CURSOR cur_effective_date IS
143 select effective_date
144 from fnd_sessions
145 where session_id=userenv('sessionid');
146 -- It doesn't matter whether action_status is 'C' or other values.
147 CURSOR cur_run_assact IS
148 select /*+ ORDERED USE_NL(PPA PES PTP) */
149 'TRUE'
150 from pay_assignment_actions paa,
151 pay_payroll_actions ppa,
152 pay_element_sets pes,
153 per_time_periods ptp
154 where paa.assignment_id=p_assignment_id
155 and ppa.payroll_action_id=paa.payroll_action_id
156 -- Element set will be supported in near future.
157 and ppa.action_type in ('R','Q')
158 and pes.element_set_id=ppa.element_set_id
159 and pes.element_set_name=p_element_set_name
160 and ptp.time_period_id=ppa.time_period_id
161 and l_effective_date
162 between nvl(p_validation_start_date,ptp.start_date) and nvl(p_validation_end_date,ptp.end_date);
163 BEGIN
164 if p_effective_date is NULL then
165 open cur_effective_date;
166 fetch cur_effective_date into l_effective_date;
167 close cur_effective_date;
168 else
169 l_effective_date := p_effective_date;
170 end if;
171
172 open cur_run_assact;
173 fetch cur_run_assact into l_found;
174 if cur_run_assact%NOTFOUND then
175 l_found := 'FALSE';
176 end if;
177 close cur_run_assact;
178
179 return l_found;
180 END run_assact_exists;
181
182 --------------------------------------------------------------
183 FUNCTION GET_ORG_SHORT_NAME(
184 --------------------------------------------------------------
185 p_organization_id in number
186 ,p_column_name in varchar2 default 'NAME1')
187 return varchar2
188 IS
189 cursor cur_org_short_name is
190 select /*+ NO_INDEX(HOI1 HR_ORGANIZATION_INFORMATIO_IX1)
191 NO_INDEX(HOI2 HR_ORGANIZATION_INFORMATIO_FK1) */
192 hoi2.org_information1
193 ,hoi2.org_information2
194 ,hoi2.org_information3
195 ,hoi2.org_information4
196 ,hoi2.org_information5
197 from
198 hr_organization_information hoi1
199 ,hr_organization_information hoi2
200 where
201 hoi1.organization_id = p_organization_id
202 and hoi1.org_information_context = 'CLASS'
203 and hoi1.org_information1 = 'JP_EXTRA_NAME'
204 and hoi1.org_information2 = 'Y'
205 and hoi2.org_information_context = 'JP_ORG_SHORT_NAME'
206 and hoi2.organization_id = hoi1.organization_id
207 ;
208
209 l_rec_org_short_name cur_org_short_name%rowtype;
210 l_return_value varchar2(150);
211 begin
212 --
213 -- Open and fetch cursor for organization information
214 --
215 open cur_org_short_name;
216 fetch cur_org_short_name into l_rec_org_short_name;
217
218 --
219 -- Check the return column name
220 --
221 if p_column_name = 'NAME1' then
222 l_return_value := l_rec_org_short_name.org_information1;
223 elsif p_column_name = 'NAME2' then
224 l_return_value := l_rec_org_short_name.org_information2;
225 elsif p_column_name = 'NAME3' then
226 l_return_value := l_rec_org_short_name.org_information3;
227 elsif p_column_name = 'NAME4' then
228 l_return_value := l_rec_org_short_name.org_information4;
229 elsif p_column_name = 'NAME5' then
230 l_return_value := l_rec_org_short_name.org_information5;
231 end if;
232
233 --
234 -- Return value
235 --
236 return l_return_value;
237
238 end get_org_short_name;
239 --
240 -- The following function is to avoid bug.2668811
241 --
242 --------------------------------------------------------------
243 function date_to_jp_char(
244 --------------------------------------------------------------
245 p_date in date,
246 p_format in varchar2) return varchar2
247 is
248 l_char varchar2(255);
249 begin
250 --
251 -- The following code is to avoid the PL/SQL to_char call.
252 -- Never call PL/SQL to_char with nlsparam specified
253 -- which will raise ORA-06502(Bug.2668811).
254 --
255 select to_char(p_date, p_format, 'NLS_CALENDAR=''Japanese Imperial''')
256 into l_char
257 from dual;
258 --
259 return l_char;
260 end date_to_jp_char;
261 --
262 --------------------------------------------------------------
263 FUNCTION DECODE_VEHICLE(
264 --------------------------------------------------------------
265 P_VEHICLE_ALLOCATION_ID IN NUMBER,
266 P_EFFECTIVE_DATE IN DATE)
267 RETURN VARCHAR2
268 IS
269 l_name PQP_VEHICLE_REPOSITORY_F.REGISTRATION_NUMBER%TYPE;
270 CURSOR cur_vehicle_name IS
271 select pvr.registration_number
272 from pqp_vehicle_allocations_f pva,
273 pqp_vehicle_repository_f pvr
274 where pva.vehicle_allocation_id=p_vehicle_allocation_id
275 and p_effective_date
276 between pva.effective_start_date and nvl(pva.effective_end_date,p_effective_date)
277 and pvr.vehicle_repository_id = pva.vehicle_repository_id
278 and p_effective_date
279 between pvr.effective_start_date and nvl(pvr.effective_end_date,p_effective_date);
280 BEGIN
281 open cur_vehicle_name;
282 fetch cur_vehicle_name into l_name;
283 if cur_vehicle_name%notfound then
284 l_name:=NULL;
285 end if;
286 close cur_vehicle_name;
287
288 return l_name;
289 END DECODE_VEHICLE;
290 --
291 END HR_JP_GENERAL_PKG;
[Home] [Help]