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_CREATE_CN_PERSON_ADDRESS

Source


4  * Generated for API: HR_CN_PERSON_ADDRESS_API.CREATE_CN_PERSON_ADDRESS
1 package body hrdpp_CREATE_CN_PERSON_ADDRESS as
2 /*
3  * Generated by hr_pump_meta_mapper at: 2012/11/27 04:11:12
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
81 ,p_link_value    in number default null
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
82 ,P_EFFECTIVE_DATE in date
83 ,P_PRADD_OVLAPVAL_OVERRIDE in boolean default null
84 ,P_VALIDATE_COUNTY in boolean default null
85 ,P_PRIMARY_FLAG in varchar2
86 ,P_DATE_FROM in date
87 ,P_DATE_TO in date default null
88 ,P_ADDRESS_TYPE in varchar2 default null
89 ,P_COMMENTS in clob default null
90 ,P_ADDRESS_LINE1 in varchar2
91 ,P_ADDRESS_LINE2 in varchar2 default null
92 ,P_PROVINCE_CITY_SAR in varchar2
93 ,P_POSTAL_CODE in varchar2 default null
94 ,P_TELEPHONE in varchar2 default null
95 ,P_FAX 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_ADD_INFORMATION13 in varchar2 default null
118 ,P_ADD_INFORMATION14 in varchar2 default null
119 ,P_ADD_INFORMATION15 in varchar2 default null
120 ,P_ADD_INFORMATION16 in varchar2 default null
121 ,P_ADD_INFORMATION17 in varchar2 default null
122 ,P_ADD_INFORMATION18 in varchar2 default null
123 ,P_ADD_INFORMATION19 in varchar2 default null
124 ,P_ADD_INFORMATION20 in varchar2 default null
125 ,P_PARTY_ID in number default null
126 ,P_ADDRESS_USER_KEY in varchar2
127 ,P_PERSON_USER_KEY in varchar2 default null
128 ,P_COUNTRY in varchar2) is
129 blid number := p_data_pump_batch_line_id;
130  L_PRADD_OVLAPVAL_OVERRIDE varchar2(5);
131  L_VALIDATE_COUNTY varchar2(5);
132 begin
133 if P_PRADD_OVLAPVAL_OVERRIDE is null then
134  L_PRADD_OVLAPVAL_OVERRIDE := null;
135 elsif P_PRADD_OVLAPVAL_OVERRIDE then
136  L_PRADD_OVLAPVAL_OVERRIDE := 'TRUE';
137 else 
138  L_PRADD_OVLAPVAL_OVERRIDE := 'FALSE';
139 end if;
140 if P_VALIDATE_COUNTY is null then
141  L_VALIDATE_COUNTY := null;
142 elsif P_VALIDATE_COUNTY then
143  L_VALIDATE_COUNTY := 'TRUE';
144 else 
145  L_VALIDATE_COUNTY := 'FALSE';
146 end if;
147 if blid is not null then
148 delete from hr_pump_batch_lines where batch_line_id = blid;
149 delete from hr_pump_batch_exceptions
150 where source_type = 'BATCH_LINE' and source_id = blid;
151 end if;
152 insert into hr_pump_batch_lines
153 (batch_id
154 ,batch_line_id
155 ,business_group_name
156 ,api_module_id
157 ,line_status
158 ,user_sequence
159 ,link_value
160 ,pval001
161 ,pval002
162 ,pval003
163 ,pval004
164 ,pval005
165 ,pval006
166 ,pval007
167 ,pval008
168 ,pval009
169 ,pval010
170 ,pval011
171 ,pval012
172 ,pval013
173 ,pval014
174 ,pval015
175 ,pval016
176 ,pval017
177 ,pval018
178 ,pval019
179 ,pval020
180 ,pval021
181 ,pval022
182 ,pval023
183 ,pval024
184 ,pval025
185 ,pval026
186 ,pval027
187 ,pval028
188 ,pval029
189 ,pval030
190 ,pval031
191 ,pval032
192 ,pval033
193 ,pval034
194 ,pval035
195 ,pval036
196 ,pval037
197 ,pval038
198 ,pval039
199 ,pval040
200 ,pval041
201 ,pval042
202 ,pval043
203 ,pval044
204 ,pval045
205 ,pval047
206 ,pval048)
207 values
208 (p_batch_id
209 ,nvl(blid,hr_pump_batch_lines_s.nextval)
210 ,p_data_pump_business_grp_name
211 ,2906
212 ,'U'
213 ,p_user_sequence
214 ,p_link_value
215 ,dc(P_EFFECTIVE_DATE)
216 ,L_PRADD_OVLAPVAL_OVERRIDE
217 ,L_VALIDATE_COUNTY
218 ,P_PRIMARY_FLAG
219 ,dc(P_DATE_FROM)
220 ,dc(P_DATE_TO)
221 ,P_ADDRESS_TYPE
222 ,P_COMMENTS
223 ,P_ADDRESS_LINE1
224 ,P_ADDRESS_LINE2
225 ,P_PROVINCE_CITY_SAR
226 ,P_POSTAL_CODE
227 ,P_TELEPHONE
228 ,P_FAX
229 ,P_ADDR_ATTRIBUTE_CATEGORY
230 ,P_ADDR_ATTRIBUTE1
231 ,P_ADDR_ATTRIBUTE2
232 ,P_ADDR_ATTRIBUTE3
233 ,P_ADDR_ATTRIBUTE4
234 ,P_ADDR_ATTRIBUTE5
235 ,P_ADDR_ATTRIBUTE6
236 ,P_ADDR_ATTRIBUTE7
237 ,P_ADDR_ATTRIBUTE8
238 ,P_ADDR_ATTRIBUTE9
239 ,P_ADDR_ATTRIBUTE10
240 ,P_ADDR_ATTRIBUTE11
241 ,P_ADDR_ATTRIBUTE12
242 ,P_ADDR_ATTRIBUTE13
243 ,P_ADDR_ATTRIBUTE14
244 ,P_ADDR_ATTRIBUTE15
245 ,P_ADDR_ATTRIBUTE16
246 ,P_ADDR_ATTRIBUTE17
247 ,P_ADDR_ATTRIBUTE18
248 ,P_ADDR_ATTRIBUTE19
249 ,P_ADDR_ATTRIBUTE20
250 ,P_ADD_INFORMATION13
251 ,P_ADD_INFORMATION14
252 ,P_ADD_INFORMATION15
253 ,P_ADD_INFORMATION16
254 ,P_ADD_INFORMATION17
255 ,P_ADD_INFORMATION18
256 ,P_ADD_INFORMATION19
257 ,P_ADD_INFORMATION20
258 ,P_PARTY_ID
259 ,P_ADDRESS_USER_KEY
260 ,P_PERSON_USER_KEY
261 ,P_COUNTRY);
262 end insert_batch_lines;
263 --
264 procedure call
265 (p_business_group_id in number,
269 decode(l.pval001,cn,dn,d(l.pval001)) p1,
266 p_batch_line_id     in number) is
267 cursor cr is
268 select l.rowid myrowid,
270 decode(l.pval002,cn,vn,vn,null,l.pval002) p2,
271 l.pval002 d2,
272 decode(l.pval003,cn,vn,vn,null,l.pval003) p3,
273 l.pval003 d3,
274 decode(l.pval004,cn,vn,
275  hr_pump_get.gl(l.pval004,'YES_NO',d(l.pval001),vn)) p4,
276 decode(l.pval005,cn,dn,d(l.pval005)) p5,
277 decode(l.pval006,cn,dn,vn,dn,d(l.pval006)) p6,
278 l.pval006 d6,
279 decode(l.pval007,cn,vn,vn,vn,
280  hr_pump_get.gl(l.pval007,'ADDRESS_TYPE',d(l.pval001),vn)) p7,
281 l.pval007 d7,
282 decode(l.pval008,cn,vn,vn,null,l.pval008) p8,
283 l.pval008 d8,
284 decode(l.pval009,cn,vn,l.pval009) p9,
285 decode(l.pval010,cn,vn,vn,vn,l.pval010) p10,
286 l.pval010 d10,
287 decode(l.pval011,cn,vn,l.pval011) p11,
288 decode(l.pval012,cn,vn,vn,vn,l.pval012) p12,
289 l.pval012 d12,
290 decode(l.pval013,cn,vn,vn,vn,l.pval013) p13,
291 l.pval013 d13,
292 decode(l.pval014,cn,vn,vn,vn,l.pval014) p14,
293 l.pval014 d14,
294 decode(l.pval015,cn,vn,vn,vn,l.pval015) p15,
295 l.pval015 d15,
296 decode(l.pval016,cn,vn,vn,vn,l.pval016) p16,
297 l.pval016 d16,
298 decode(l.pval017,cn,vn,vn,vn,l.pval017) p17,
299 l.pval017 d17,
300 decode(l.pval018,cn,vn,vn,vn,l.pval018) p18,
301 l.pval018 d18,
302 decode(l.pval019,cn,vn,vn,vn,l.pval019) p19,
303 l.pval019 d19,
304 decode(l.pval020,cn,vn,vn,vn,l.pval020) p20,
305 l.pval020 d20,
306 decode(l.pval021,cn,vn,vn,vn,l.pval021) p21,
307 l.pval021 d21,
308 decode(l.pval022,cn,vn,vn,vn,l.pval022) p22,
309 l.pval022 d22,
310 decode(l.pval023,cn,vn,vn,vn,l.pval023) p23,
311 l.pval023 d23,
312 decode(l.pval024,cn,vn,vn,vn,l.pval024) p24,
313 l.pval024 d24,
314 decode(l.pval025,cn,vn,vn,vn,l.pval025) p25,
315 l.pval025 d25,
316 decode(l.pval026,cn,vn,vn,vn,l.pval026) p26,
317 l.pval026 d26,
318 decode(l.pval027,cn,vn,vn,vn,l.pval027) p27,
319 l.pval027 d27,
320 decode(l.pval028,cn,vn,vn,vn,l.pval028) p28,
321 l.pval028 d28,
322 decode(l.pval029,cn,vn,vn,vn,l.pval029) p29,
323 l.pval029 d29,
324 decode(l.pval030,cn,vn,vn,vn,l.pval030) p30,
325 l.pval030 d30,
326 decode(l.pval031,cn,vn,vn,vn,l.pval031) p31,
327 l.pval031 d31,
328 decode(l.pval032,cn,vn,vn,vn,l.pval032) p32,
329 l.pval032 d32,
330 decode(l.pval033,cn,vn,vn,vn,l.pval033) p33,
331 l.pval033 d33,
332 decode(l.pval034,cn,vn,vn,vn,l.pval034) p34,
333 l.pval034 d34,
334 decode(l.pval035,cn,vn,vn,vn,l.pval035) p35,
335 l.pval035 d35,
336 decode(l.pval036,cn,vn,vn,vn,l.pval036) p36,
337 l.pval036 d36,
338 decode(l.pval037,cn,vn,vn,vn,l.pval037) p37,
339 l.pval037 d37,
340 decode(l.pval038,cn,vn,vn,vn,l.pval038) p38,
341 l.pval038 d38,
342 decode(l.pval039,cn,vn,vn,vn,l.pval039) p39,
343 l.pval039 d39,
344 decode(l.pval040,cn,vn,vn,vn,l.pval040) p40,
345 l.pval040 d40,
346 decode(l.pval041,cn,vn,vn,vn,l.pval041) p41,
347 l.pval041 d41,
348 decode(l.pval042,cn,vn,vn,vn,l.pval042) p42,
349 l.pval042 d42,
350 decode(l.pval043,cn,vn,vn,vn,l.pval043) p43,
351 l.pval043 d43,
352 decode(l.pval044,cn,nn,vn,nn,n(l.pval044)) p44,
353 l.pval044 d44,
354 l.pval045 p45,
355 l.pval046 p46,
356 decode(l.pval047,cn,vn,vn,vn,l.pval047) p47,
357 l.pval047 d47,
358 decode(l.pval048,cn,vn,l.pval048) p48
359 from hr_pump_batch_lines l
360 where l.batch_line_id = p_batch_line_id;
361 --
362 c cr%rowtype;
363 l_validate boolean := false;
364 L_PRADD_OVLAPVAL_OVERRIDE boolean;
365 L_VALIDATE_COUNTY boolean;
366 L_ADDRESS_ID number;
367 L_PERSON_ID number;
368 L_COUNTRY varchar2(2000);
369 --
370 begin
371 hr_data_pump.entry('call');
372 open cr;
373 fetch cr into c;
374 if cr%notfound then
375 hr_utility.set_message(800,'HR_50326_DP_NO_ROW');
376 hr_utility.set_message_token('TABLE','HR_PUMP_BATCH_LINES');
377 hr_utility.set_message_token('COLUMN','P_BATCH_LINE_ID');
378 hr_utility.set_message_token('VALUE',p_batch_line_id);
379 hr_utility.raise_error;
380 end if;
381 --
382 if upper(c.p2) = 'TRUE' then
383 L_PRADD_OVLAPVAL_OVERRIDE := true;
384 elsif upper(c.p2) = 'FALSE' then
385 L_PRADD_OVLAPVAL_OVERRIDE := false;
386 elsif c.p2 is not null then
387 hr_utility.set_message(800,'HR_50327_DP_TYPE_ERR');
388 hr_utility.set_message_token('TYPE','BOOLEAN');
389 hr_utility.set_message_token('PARAMETER','P_PRADD_OVLAPVAL_OVERRIDE');
390 hr_utility.set_message_token('VALUE',c.p2);
391 hr_utility.set_message_token('TABLE','HR_PUMP_BATCH_LINES');
392 hr_utility.raise_error;
393 end if;
394 --
395 if upper(c.p3) = 'TRUE' then
396 L_VALIDATE_COUNTY := true;
397 elsif upper(c.p3) = 'FALSE' then
398 L_VALIDATE_COUNTY := false;
399 elsif c.p3 is not null then
400 hr_utility.set_message(800,'HR_50327_DP_TYPE_ERR');
401 hr_utility.set_message_token('TYPE','BOOLEAN');
402 hr_utility.set_message_token('PARAMETER','P_VALIDATE_COUNTY');
403 hr_utility.set_message_token('VALUE',c.p3);
404 hr_utility.set_message_token('TABLE','HR_PUMP_BATCH_LINES');
405 hr_utility.raise_error;
406 end if;
407 --
408 if c.p47 is null then
409 L_PERSON_ID:=nn;
410 else
411 L_PERSON_ID := 
412 hr_pump_get.get_person_id
413 (P_PERSON_USER_KEY => c.p47);
414 end if;
415 --
416 if c.p48 is null then
417 L_COUNTRY:=vn;
418 else
419 L_COUNTRY := 
420 hr_pump_get.GET_COUNTRY
421 (P_COUNTRY => c.p48);
422 end if;
423 --
424 hr_data_pump.api_trc_on;
428 ,P_PRADD_OVLAPVAL_OVERRIDE => L_PRADD_OVLAPVAL_OVERRIDE
425 HR_CN_PERSON_ADDRESS_API.CREATE_CN_PERSON_ADDRESS
426 (p_validate => l_validate
427 ,P_EFFECTIVE_DATE => c.p1
429 ,P_VALIDATE_COUNTY => L_VALIDATE_COUNTY
430 ,P_PERSON_ID => L_PERSON_ID
431 ,P_PRIMARY_FLAG => c.p4
432 ,P_DATE_FROM => c.p5
433 ,P_DATE_TO => c.p6
434 ,P_ADDRESS_TYPE => c.p7
435 ,P_COMMENTS => c.p8
436 ,P_ADDRESS_LINE1 => c.p9
437 ,P_ADDRESS_LINE2 => c.p10
438 ,P_PROVINCE_CITY_SAR => c.p11
439 ,P_POSTAL_CODE => c.p12
440 ,P_COUNTRY => L_COUNTRY
441 ,P_TELEPHONE => c.p13
442 ,P_FAX => c.p14
443 ,P_ADDR_ATTRIBUTE_CATEGORY => c.p15
444 ,P_ADDR_ATTRIBUTE1 => c.p16
445 ,P_ADDR_ATTRIBUTE2 => c.p17
446 ,P_ADDR_ATTRIBUTE3 => c.p18
447 ,P_ADDR_ATTRIBUTE4 => c.p19
448 ,P_ADDR_ATTRIBUTE5 => c.p20
449 ,P_ADDR_ATTRIBUTE6 => c.p21
450 ,P_ADDR_ATTRIBUTE7 => c.p22
451 ,P_ADDR_ATTRIBUTE8 => c.p23
452 ,P_ADDR_ATTRIBUTE9 => c.p24
453 ,P_ADDR_ATTRIBUTE10 => c.p25
454 ,P_ADDR_ATTRIBUTE11 => c.p26
455 ,P_ADDR_ATTRIBUTE12 => c.p27
456 ,P_ADDR_ATTRIBUTE13 => c.p28
457 ,P_ADDR_ATTRIBUTE14 => c.p29
458 ,P_ADDR_ATTRIBUTE15 => c.p30
459 ,P_ADDR_ATTRIBUTE16 => c.p31
460 ,P_ADDR_ATTRIBUTE17 => c.p32
461 ,P_ADDR_ATTRIBUTE18 => c.p33
462 ,P_ADDR_ATTRIBUTE19 => c.p34
463 ,P_ADDR_ATTRIBUTE20 => c.p35
464 ,P_ADD_INFORMATION13 => c.p36
465 ,P_ADD_INFORMATION14 => c.p37
466 ,P_ADD_INFORMATION15 => c.p38
467 ,P_ADD_INFORMATION16 => c.p39
468 ,P_ADD_INFORMATION17 => c.p40
469 ,P_ADD_INFORMATION18 => c.p41
470 ,P_ADD_INFORMATION19 => c.p42
471 ,P_ADD_INFORMATION20 => c.p43
472 ,P_PARTY_ID => c.p44
473 ,P_ADDRESS_ID => L_ADDRESS_ID
474 ,P_OBJECT_VERSION_NUMBER => c.p46);
475 hr_data_pump.api_trc_off;
476 --
477 iuk(p_batch_line_id,c.p45,L_ADDRESS_ID);
478 --
479 update hr_pump_batch_lines l set
480 l.pval045 = decode(c.p45,null,cn,c.p45),
481 l.pval046 = decode(c.p46,null,cn,c.p46)
482 where l.rowid = c.myrowid;
483 --
484 close cr;
485 --
486 hr_data_pump.exit('call');
487 exception
488  when hr_multi_message.error_message_exist then
489    if cr%isopen then
490     close cr;
491    end if;
492    hr_pump_utils.set_multi_msg_error_flag(true);
493  when others then
494  if cr%isopen then
495   close cr;
496  end if;
497  raise;
498 end call;
499 end hrdpp_CREATE_CN_PERSON_ADDRESS;