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.HRDPP_UPDATE_IN_PERSON_ADDRESS

Source


1 package body hrdpp_update_in_person_address as
2 /*
3  * Generated by hr_pump_meta_mapper at: 2007/01/03 23:01:17
4  * Generated for API: hr_in_person_address_api.update_in_person_address
5  */
6 --
7 dh constant date := hr_api.g_date;
8 nh constant number := hr_api.g_number;
9 vh constant varchar2(64) := hr_api.g_varchar2;
10 c_sot constant date := to_date('01010001','DDMMYYYY');
11 cn constant varchar2(32) := '<NULL>';
12 dn constant date := null;
13 nn constant number := null;
14 vn constant varchar2(1) := null;
15 --
16 function dc(p in date) return varchar2 is
17 begin
18 if p<c_sot then
19  if p<>trunc(p) then
20   return to_char(p,'SYYYY/MM/DD HH24:MI:SS');
21  end if;
22  return to_char(p,'SYYYY/MM/DD');
23 elsif p<>trunc(p) then
24  return to_char(p,'YYYY/MM/DD HH24:MI:SS');
25 end if;
26 return to_char(p,'YYYY/MM/DD');
27 end dc;
28 function d(p in varchar2) return date is
29 begin
30 if length(p)=10 then
31 return to_date(p,'YYYY/MM/DD');
32 elsif length(p)=19 then
33 return to_date(p,'YYYY/MM/DD HH24:MI:SS');
34 elsif length(p)=11 then
35 return to_date(p,'SYYYY/MM/DD');
36 elsif length(p)=20 then
37 return to_date(p,'SYYYY/MM/DD HH24:MI:SS');
38 end if;
39 -- Try default format as last resort.
40 return to_date(p,'YYYY/MM/DD');
41 end d;
42 function n(p in varchar2) return number is
43 begin
44 return to_number(p);
45 end n;
46 function dd(p in date,i in varchar2)
47 return varchar2 is
48 begin
49 if upper(i) = 'N' then return dc(p);
50 else return cn; end if;
51 end dd;
52 function nd(p in number,i in varchar2)
53 return varchar2 is
54 begin
55 if upper(i) = 'N' then return to_char(p);
56 else return cn; end if;
57 end nd;
58 --
59 procedure iuk
60 (p_batch_line_id  in number,
61 p_user_key_value in varchar2,
62 p_unique_key_id  in number)
63 is
64 begin
65 hr_data_pump.entry('ins_user_key');
66 insert into hr_pump_batch_line_user_keys
67 (user_key_id, batch_line_id,user_key_value,unique_key_id)
68 values
69 (hr_pump_batch_line_user_keys_s.nextval,
70 p_batch_line_id,
71 p_user_key_value,
72 p_unique_key_id);
73 hr_data_pump.exit('ins_user_key');
74 end iuk;
75 --
76 procedure insert_batch_lines
77 (p_batch_id      in number
78 ,p_data_pump_batch_line_id in number default null
79 ,p_data_pump_business_grp_name in varchar2 default null
80 ,p_user_sequence in number default null
81 ,p_link_value    in number default null
82 ,P_EFFECTIVE_DATE in date
83 ,P_DATE_FROM in date default null
84 ,I_DATE_FROM in varchar2 default 'N'
85 ,P_DATE_TO in date default null
86 ,I_DATE_TO in varchar2 default 'N'
87 ,P_ADDRESS_TYPE in varchar2 default null
88 ,P_COMMENTS in long default null
89 ,P_FLAT_DOOR_BLOCK in varchar2 default null
90 ,P_BUILDING_VILLAGE in varchar2 default null
91 ,P_ROAD_STREET in varchar2 default null
92 ,P_AREA_LOCALITY in varchar2 default null
93 ,P_TOWN_OR_CITY in varchar2 default null
94 ,P_STATE_UT in varchar2 default null
95 ,P_PIN_CODE in varchar2 default null
96 ,P_ADDR_ATTRIBUTE_CATEGORY in varchar2 default null
97 ,P_ADDR_ATTRIBUTE1 in varchar2 default null
98 ,P_ADDR_ATTRIBUTE2 in varchar2 default null
99 ,P_ADDR_ATTRIBUTE3 in varchar2 default null
100 ,P_ADDR_ATTRIBUTE4 in varchar2 default null
101 ,P_ADDR_ATTRIBUTE5 in varchar2 default null
102 ,P_ADDR_ATTRIBUTE6 in varchar2 default null
103 ,P_ADDR_ATTRIBUTE7 in varchar2 default null
104 ,P_ADDR_ATTRIBUTE8 in varchar2 default null
105 ,P_ADDR_ATTRIBUTE9 in varchar2 default null
106 ,P_ADDR_ATTRIBUTE10 in varchar2 default null
107 ,P_ADDR_ATTRIBUTE11 in varchar2 default null
108 ,P_ADDR_ATTRIBUTE12 in varchar2 default null
109 ,P_ADDR_ATTRIBUTE13 in varchar2 default null
110 ,P_ADDR_ATTRIBUTE14 in varchar2 default null
111 ,P_ADDR_ATTRIBUTE15 in varchar2 default null
112 ,P_ADDR_ATTRIBUTE16 in varchar2 default null
113 ,P_ADDR_ATTRIBUTE17 in varchar2 default null
114 ,P_ADDR_ATTRIBUTE18 in varchar2 default null
115 ,P_ADDR_ATTRIBUTE19 in varchar2 default null
116 ,P_ADDR_ATTRIBUTE20 in varchar2 default null
117 ,P_ADDRESS_USER_KEY in varchar2
118 ,P_COUNTRY in varchar2 default null) is
119 blid number := p_data_pump_batch_line_id;
120 begin
121 if blid is not null then
122 delete from hr_pump_batch_lines where batch_line_id = blid;
123 delete from hr_pump_batch_exceptions
124 where source_type = 'BATCH_LINE' and source_id = blid;
125 end if;
126 insert into hr_pump_batch_lines
127 (batch_id
128 ,batch_line_id
129 ,business_group_name
130 ,api_module_id
131 ,line_status
132 ,user_sequence
133 ,link_value
134 ,pval001
135 ,pval002
136 ,pval003
137 ,pval004
138 ,plongval
139 ,pval006
140 ,pval007
141 ,pval008
142 ,pval009
143 ,pval010
144 ,pval011
145 ,pval012
146 ,pval013
147 ,pval014
148 ,pval015
149 ,pval016
150 ,pval017
151 ,pval018
152 ,pval019
153 ,pval020
154 ,pval021
155 ,pval022
156 ,pval023
157 ,pval024
158 ,pval025
159 ,pval026
160 ,pval027
161 ,pval028
162 ,pval029
163 ,pval030
164 ,pval031
165 ,pval032
166 ,pval033
167 ,pval034
168 ,pval035)
169 values
170 (p_batch_id
171 ,nvl(blid,hr_pump_batch_lines_s.nextval)
172 ,p_data_pump_business_grp_name
173 ,3218
174 ,'U'
175 ,p_user_sequence
176 ,p_link_value
177 ,dc(P_EFFECTIVE_DATE)
178 ,dd(P_DATE_FROM,I_DATE_FROM)
179 ,dd(P_DATE_TO,I_DATE_TO)
180 ,P_ADDRESS_TYPE
181 ,P_COMMENTS
182 ,P_FLAT_DOOR_BLOCK
183 ,P_BUILDING_VILLAGE
184 ,P_ROAD_STREET
185 ,P_AREA_LOCALITY
186 ,P_TOWN_OR_CITY
187 ,P_STATE_UT
188 ,P_PIN_CODE
189 ,P_ADDR_ATTRIBUTE_CATEGORY
190 ,P_ADDR_ATTRIBUTE1
191 ,P_ADDR_ATTRIBUTE2
192 ,P_ADDR_ATTRIBUTE3
193 ,P_ADDR_ATTRIBUTE4
194 ,P_ADDR_ATTRIBUTE5
195 ,P_ADDR_ATTRIBUTE6
196 ,P_ADDR_ATTRIBUTE7
197 ,P_ADDR_ATTRIBUTE8
198 ,P_ADDR_ATTRIBUTE9
199 ,P_ADDR_ATTRIBUTE10
200 ,P_ADDR_ATTRIBUTE11
201 ,P_ADDR_ATTRIBUTE12
202 ,P_ADDR_ATTRIBUTE13
203 ,P_ADDR_ATTRIBUTE14
204 ,P_ADDR_ATTRIBUTE15
205 ,P_ADDR_ATTRIBUTE16
206 ,P_ADDR_ATTRIBUTE17
207 ,P_ADDR_ATTRIBUTE18
208 ,P_ADDR_ATTRIBUTE19
209 ,P_ADDR_ATTRIBUTE20
210 ,P_ADDRESS_USER_KEY
211 ,P_COUNTRY);
212 end insert_batch_lines;
213 --
214 procedure call
215 (p_business_group_id in number,
216 p_batch_line_id     in number) is
217 cursor cr is
218 select l.rowid myrowid,
219 decode(l.pval001,cn,dn,d(l.pval001)) p1,
220 decode(l.pval002,cn,dn,vn,dh,d(l.pval002)) p2,
221 l.pval002 d2,
222 decode(l.pval003,cn,dn,vn,dh,d(l.pval003)) p3,
223 l.pval003 d3,
224 decode(l.pval004,cn,vn,vn,vh,
225  hr_pump_get.gl(l.pval004,'ADDRESS_TYPE',d(l.pval001),vn)) p4,
226 l.pval004 d4,
227 l.plongval plongval,
228 decode(l.pval006,cn,vn,vn,vh,l.pval006) p6,
229 l.pval006 d6,
230 decode(l.pval007,cn,vn,vn,vh,l.pval007) p7,
231 l.pval007 d7,
232 decode(l.pval008,cn,vn,vn,vh,l.pval008) p8,
233 l.pval008 d8,
234 decode(l.pval009,cn,vn,vn,vh,l.pval009) p9,
235 l.pval009 d9,
236 decode(l.pval010,cn,vn,vn,vh,l.pval010) p10,
237 l.pval010 d10,
238 decode(l.pval011,cn,vn,vn,vh,
239  hr_pump_get.gl(l.pval011,'IN_STATES',d(l.pval001),vn)) p11,
240 l.pval011 d11,
241 decode(l.pval012,cn,vn,vn,vh,l.pval012) p12,
242 l.pval012 d12,
243 decode(l.pval013,cn,vn,vn,vh,l.pval013) p13,
244 l.pval013 d13,
245 decode(l.pval014,cn,vn,vn,vh,l.pval014) p14,
246 l.pval014 d14,
247 decode(l.pval015,cn,vn,vn,vh,l.pval015) p15,
248 l.pval015 d15,
249 decode(l.pval016,cn,vn,vn,vh,l.pval016) p16,
250 l.pval016 d16,
251 decode(l.pval017,cn,vn,vn,vh,l.pval017) p17,
252 l.pval017 d17,
253 decode(l.pval018,cn,vn,vn,vh,l.pval018) p18,
254 l.pval018 d18,
255 decode(l.pval019,cn,vn,vn,vh,l.pval019) p19,
256 l.pval019 d19,
257 decode(l.pval020,cn,vn,vn,vh,l.pval020) p20,
258 l.pval020 d20,
259 decode(l.pval021,cn,vn,vn,vh,l.pval021) p21,
260 l.pval021 d21,
261 decode(l.pval022,cn,vn,vn,vh,l.pval022) p22,
262 l.pval022 d22,
263 decode(l.pval023,cn,vn,vn,vh,l.pval023) p23,
264 l.pval023 d23,
265 decode(l.pval024,cn,vn,vn,vh,l.pval024) p24,
266 l.pval024 d24,
267 decode(l.pval025,cn,vn,vn,vh,l.pval025) p25,
268 l.pval025 d25,
269 decode(l.pval026,cn,vn,vn,vh,l.pval026) p26,
270 l.pval026 d26,
271 decode(l.pval027,cn,vn,vn,vh,l.pval027) p27,
272 l.pval027 d27,
273 decode(l.pval028,cn,vn,vn,vh,l.pval028) p28,
274 l.pval028 d28,
275 decode(l.pval029,cn,vn,vn,vh,l.pval029) p29,
276 l.pval029 d29,
277 decode(l.pval030,cn,vn,vn,vh,l.pval030) p30,
278 l.pval030 d30,
279 decode(l.pval031,cn,vn,vn,vh,l.pval031) p31,
280 l.pval031 d31,
281 decode(l.pval032,cn,vn,vn,vh,l.pval032) p32,
282 l.pval032 d32,
283 decode(l.pval033,cn,vn,vn,vh,l.pval033) p33,
284 l.pval033 d33,
285 decode(l.pval034,cn,vn,l.pval034) p34,
286 decode(l.pval035,cn,vn,vn,vh,l.pval035) p35,
287 l.pval035 d35
288 from hr_pump_batch_lines l
289 where l.batch_line_id = p_batch_line_id;
290 --
291 c cr%rowtype;
292 l_validate boolean := false;
293 L_COMMENTS varchar2(32767);
294 L_ADDRESS_ID number;
295 L_OBJECT_VERSION_NUMBER number;
296 L_COUNTRY varchar2(2000);
297 --
298 begin
299 hr_data_pump.entry('call');
300 open cr;
301 fetch cr into c;
302 if cr%notfound then
303 hr_utility.set_message(800,'HR_50326_DP_NO_ROW');
304 hr_utility.set_message_token('TABLE','HR_PUMP_BATCH_LINES');
305 hr_utility.set_message_token('COLUMN','P_BATCH_LINE_ID');
306 hr_utility.set_message_token('VALUE',p_batch_line_id);
307 hr_utility.raise_error;
308 end if;
309 --
310 L_COMMENTS := c.plongval;
311 if L_COMMENTS = cn then
312 L_COMMENTS := null;
313 end if;
314 --
315 if c.p34 is null then
316 L_ADDRESS_ID:=nn;
317 else
318 L_ADDRESS_ID := 
319 hr_pump_get.get_address_id
320 (P_ADDRESS_USER_KEY => c.p34);
321 end if;
322 --
323 if c.p34 is null or
324 c.p1 is null then
325 L_OBJECT_VERSION_NUMBER:=nn;
326 else
327 L_OBJECT_VERSION_NUMBER := 
328 hr_pump_get.GET_ADR_OVN
329 (P_ADDRESS_USER_KEY => c.p34
330 ,P_EFFECTIVE_DATE => c.p1);
331 end if;
332 --
333 if c.d35=cn then
334 L_COUNTRY:=vn;
335 elsif c.d35 is null then 
336 L_COUNTRY:=vh;
337 else
338 L_COUNTRY := 
339 hr_pump_get.GET_COUNTRY
340 (P_COUNTRY => c.p35);
341 end if;
342 --
343 hr_data_pump.api_trc_on;
344 hr_in_person_address_api.update_in_person_address
345 (p_validate => l_validate
346 ,P_EFFECTIVE_DATE => c.p1
347 ,P_ADDRESS_ID => L_ADDRESS_ID
348 ,P_OBJECT_VERSION_NUMBER => L_OBJECT_VERSION_NUMBER
349 ,P_DATE_FROM => c.p2
350 ,P_DATE_TO => c.p3
351 ,P_ADDRESS_TYPE => c.p4
352 ,P_COMMENTS => L_COMMENTS
353 ,P_FLAT_DOOR_BLOCK => c.p6
354 ,P_BUILDING_VILLAGE => c.p7
355 ,P_ROAD_STREET => c.p8
356 ,P_AREA_LOCALITY => c.p9
357 ,P_TOWN_OR_CITY => c.p10
358 ,P_STATE_UT => c.p11
359 ,P_PIN_CODE => c.p12
360 ,P_COUNTRY => L_COUNTRY
361 ,P_ADDR_ATTRIBUTE_CATEGORY => c.p13
362 ,P_ADDR_ATTRIBUTE1 => c.p14
363 ,P_ADDR_ATTRIBUTE2 => c.p15
364 ,P_ADDR_ATTRIBUTE3 => c.p16
365 ,P_ADDR_ATTRIBUTE4 => c.p17
366 ,P_ADDR_ATTRIBUTE5 => c.p18
367 ,P_ADDR_ATTRIBUTE6 => c.p19
368 ,P_ADDR_ATTRIBUTE7 => c.p20
369 ,P_ADDR_ATTRIBUTE8 => c.p21
370 ,P_ADDR_ATTRIBUTE9 => c.p22
371 ,P_ADDR_ATTRIBUTE10 => c.p23
372 ,P_ADDR_ATTRIBUTE11 => c.p24
373 ,P_ADDR_ATTRIBUTE12 => c.p25
374 ,P_ADDR_ATTRIBUTE13 => c.p26
375 ,P_ADDR_ATTRIBUTE14 => c.p27
376 ,P_ADDR_ATTRIBUTE15 => c.p28
377 ,P_ADDR_ATTRIBUTE16 => c.p29
378 ,P_ADDR_ATTRIBUTE17 => c.p30
379 ,P_ADDR_ATTRIBUTE18 => c.p31
380 ,P_ADDR_ATTRIBUTE19 => c.p32
381 ,P_ADDR_ATTRIBUTE20 => c.p33);
382 hr_data_pump.api_trc_off;
383 
384 --
385 
386 --
387 close cr;
388 --
389 hr_data_pump.exit('call');
390 exception
391  when hr_multi_message.error_message_exist then
392    if cr%isopen then
393     close cr;
394    end if;
395    hr_pump_utils.set_multi_msg_error_flag(true);
396  when others then
397  if cr%isopen then
398   close cr;
399  end if;
400  raise;
401 end call;
402 end hrdpp_update_in_person_address;