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_PASSPORT_DETAI

Source


1 package body hrdpp_update_in_passport_detai as
2 /*
3  * Generated by hr_pump_meta_mapper at: 2007/01/03 23:01:30
4  * Generated for API: hr_in_person_extra_info_api.update_in_passport_details
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_PEI_ATTRIBUTE_CATEGORY in varchar2 default null
83 ,P_PEI_ATTRIBUTE1 in varchar2 default null
84 ,P_PEI_ATTRIBUTE2 in varchar2 default null
85 ,P_PEI_ATTRIBUTE3 in varchar2 default null
86 ,P_PEI_ATTRIBUTE4 in varchar2 default null
87 ,P_PEI_ATTRIBUTE5 in varchar2 default null
88 ,P_PEI_ATTRIBUTE6 in varchar2 default null
89 ,P_PEI_ATTRIBUTE7 in varchar2 default null
90 ,P_PEI_ATTRIBUTE8 in varchar2 default null
91 ,P_PEI_ATTRIBUTE9 in varchar2 default null
92 ,P_PEI_ATTRIBUTE10 in varchar2 default null
93 ,P_PEI_ATTRIBUTE11 in varchar2 default null
94 ,P_PEI_ATTRIBUTE12 in varchar2 default null
95 ,P_PEI_ATTRIBUTE13 in varchar2 default null
96 ,P_PEI_ATTRIBUTE14 in varchar2 default null
97 ,P_PEI_ATTRIBUTE15 in varchar2 default null
98 ,P_PEI_ATTRIBUTE16 in varchar2 default null
99 ,P_PEI_ATTRIBUTE17 in varchar2 default null
100 ,P_PEI_ATTRIBUTE18 in varchar2 default null
101 ,P_PEI_ATTRIBUTE19 in varchar2 default null
102 ,P_PEI_ATTRIBUTE20 in varchar2 default null
103 ,P_PASSPORT_NAME in varchar2 default null
104 ,P_PASSPORT_NUMBER in varchar2 default null
105 ,P_PLACE_OF_ISSUE in varchar2 default null
106 ,P_ECNR_REQUIRED in varchar2 default null
107 ,P_PERSON_EXTRA_INFO_USER_KEY in varchar2
108 ,P_ISSUE_DATE in varchar2 default null
109 ,P_EXPIRY_DATE in varchar2 default null
110 ,P_ISSUING_COUNTRY in varchar2 default null) is
111 blid number := p_data_pump_batch_line_id;
112 begin
113 if blid is not null then
114 delete from hr_pump_batch_lines where batch_line_id = blid;
115 delete from hr_pump_batch_exceptions
116 where source_type = 'BATCH_LINE' and source_id = blid;
117 end if;
118 insert into hr_pump_batch_lines
119 (batch_id
120 ,batch_line_id
121 ,business_group_name
122 ,api_module_id
123 ,line_status
124 ,user_sequence
125 ,link_value
126 ,pval001
127 ,pval002
128 ,pval003
129 ,pval004
130 ,pval005
131 ,pval006
132 ,pval007
133 ,pval008
134 ,pval009
135 ,pval010
136 ,pval011
137 ,pval012
138 ,pval013
139 ,pval014
140 ,pval015
141 ,pval016
142 ,pval017
143 ,pval018
144 ,pval019
145 ,pval020
146 ,pval021
147 ,pval022
148 ,pval023
149 ,pval024
150 ,pval025
151 ,pval026
152 ,pval027
153 ,pval028
154 ,pval029)
155 values
156 (p_batch_id
157 ,nvl(blid,hr_pump_batch_lines_s.nextval)
158 ,p_data_pump_business_grp_name
159 ,3212
160 ,'U'
161 ,p_user_sequence
162 ,p_link_value
163 ,P_PEI_ATTRIBUTE_CATEGORY
164 ,P_PEI_ATTRIBUTE1
165 ,P_PEI_ATTRIBUTE2
166 ,P_PEI_ATTRIBUTE3
167 ,P_PEI_ATTRIBUTE4
168 ,P_PEI_ATTRIBUTE5
169 ,P_PEI_ATTRIBUTE6
170 ,P_PEI_ATTRIBUTE7
171 ,P_PEI_ATTRIBUTE8
172 ,P_PEI_ATTRIBUTE9
173 ,P_PEI_ATTRIBUTE10
174 ,P_PEI_ATTRIBUTE11
175 ,P_PEI_ATTRIBUTE12
176 ,P_PEI_ATTRIBUTE13
177 ,P_PEI_ATTRIBUTE14
178 ,P_PEI_ATTRIBUTE15
179 ,P_PEI_ATTRIBUTE16
180 ,P_PEI_ATTRIBUTE17
181 ,P_PEI_ATTRIBUTE18
182 ,P_PEI_ATTRIBUTE19
183 ,P_PEI_ATTRIBUTE20
184 ,P_PASSPORT_NAME
185 ,P_PASSPORT_NUMBER
186 ,P_PLACE_OF_ISSUE
187 ,P_ECNR_REQUIRED
188 ,P_PERSON_EXTRA_INFO_USER_KEY
189 ,P_ISSUE_DATE
190 ,P_EXPIRY_DATE
191 ,P_ISSUING_COUNTRY);
192 end insert_batch_lines;
193 --
194 procedure call
195 (p_business_group_id in number,
196 p_batch_line_id     in number) is
197 cursor cr is
198 select l.rowid myrowid,
199 decode(l.pval001,cn,vn,vn,vh,l.pval001) p1,
200 l.pval001 d1,
201 decode(l.pval002,cn,vn,vn,vh,l.pval002) p2,
202 l.pval002 d2,
203 decode(l.pval003,cn,vn,vn,vh,l.pval003) p3,
204 l.pval003 d3,
205 decode(l.pval004,cn,vn,vn,vh,l.pval004) p4,
206 l.pval004 d4,
207 decode(l.pval005,cn,vn,vn,vh,l.pval005) p5,
208 l.pval005 d5,
209 decode(l.pval006,cn,vn,vn,vh,l.pval006) p6,
210 l.pval006 d6,
211 decode(l.pval007,cn,vn,vn,vh,l.pval007) p7,
212 l.pval007 d7,
213 decode(l.pval008,cn,vn,vn,vh,l.pval008) p8,
214 l.pval008 d8,
215 decode(l.pval009,cn,vn,vn,vh,l.pval009) p9,
216 l.pval009 d9,
217 decode(l.pval010,cn,vn,vn,vh,l.pval010) p10,
218 l.pval010 d10,
219 decode(l.pval011,cn,vn,vn,vh,l.pval011) p11,
220 l.pval011 d11,
221 decode(l.pval012,cn,vn,vn,vh,l.pval012) p12,
222 l.pval012 d12,
223 decode(l.pval013,cn,vn,vn,vh,l.pval013) p13,
224 l.pval013 d13,
225 decode(l.pval014,cn,vn,vn,vh,l.pval014) p14,
226 l.pval014 d14,
227 decode(l.pval015,cn,vn,vn,vh,l.pval015) p15,
228 l.pval015 d15,
229 decode(l.pval016,cn,vn,vn,vh,l.pval016) p16,
230 l.pval016 d16,
231 decode(l.pval017,cn,vn,vn,vh,l.pval017) p17,
232 l.pval017 d17,
233 decode(l.pval018,cn,vn,vn,vh,l.pval018) p18,
234 l.pval018 d18,
235 decode(l.pval019,cn,vn,vn,vh,l.pval019) p19,
236 l.pval019 d19,
237 decode(l.pval020,cn,vn,vn,vh,l.pval020) p20,
238 l.pval020 d20,
239 decode(l.pval021,cn,vn,vn,vh,l.pval021) p21,
240 l.pval021 d21,
241 decode(l.pval022,cn,vn,vn,vh,l.pval022) p22,
242 l.pval022 d22,
243 decode(l.pval023,cn,vn,vn,vh,l.pval023) p23,
244 l.pval023 d23,
245 decode(l.pval024,cn,vn,vn,vh,l.pval024) p24,
246 l.pval024 d24,
247 decode(l.pval025,cn,vn,vn,vh,l.pval025) p25,
248 l.pval025 d25,
249 decode(l.pval026,cn,vn,l.pval026) p26,
250 decode(l.pval027,cn,vn,vn,vh,l.pval027) p27,
251 l.pval027 d27,
252 decode(l.pval028,cn,vn,vn,vh,l.pval028) p28,
253 l.pval028 d28,
254 decode(l.pval029,cn,vn,vn,vh,l.pval029) p29,
255 l.pval029 d29
256 from hr_pump_batch_lines l
257 where l.batch_line_id = p_batch_line_id;
258 --
259 c cr%rowtype;
260 l_validate boolean := false;
261 L_PERSON_EXTRA_INFO_ID number;
262 L_OBJECT_VERSION_NUMBER number;
263 L_ISSUE_DATE varchar2(2000);
264 L_EXPIRY_DATE varchar2(2000);
265 L_ISSUING_COUNTRY varchar2(2000);
266 --
267 begin
268 hr_data_pump.entry('call');
269 open cr;
270 fetch cr into c;
271 if cr%notfound then
272 hr_utility.set_message(800,'HR_50326_DP_NO_ROW');
273 hr_utility.set_message_token('TABLE','HR_PUMP_BATCH_LINES');
274 hr_utility.set_message_token('COLUMN','P_BATCH_LINE_ID');
275 hr_utility.set_message_token('VALUE',p_batch_line_id);
276 hr_utility.raise_error;
277 end if;
278 --
279 if c.p26 is null then
280 L_PERSON_EXTRA_INFO_ID:=nn;
281 else
282 L_PERSON_EXTRA_INFO_ID := 
283 PER_IN_DATA_PUMP.get_person_extra_info_id
284 (P_PERSON_EXTRA_INFO_USER_KEY => c.p26);
285 end if;
286 --
287 if c.p26 is null then
288 L_OBJECT_VERSION_NUMBER:=nn;
289 else
290 L_OBJECT_VERSION_NUMBER := 
291 PER_IN_DATA_PUMP.GET_PERSON_EXTRA_INFO_OVN
292 (P_PERSON_EXTRA_INFO_USER_KEY => c.p26);
293 end if;
294 --
295 if c.d27=cn then
296 L_ISSUE_DATE:=vn;
297 elsif c.d27 is null then 
298 L_ISSUE_DATE:=vh;
299 else
300 L_ISSUE_DATE := 
301 PER_IN_DATA_PUMP.GET_ISSUE_DATE
302 (P_ISSUE_DATE => c.p27);
303 end if;
304 --
305 if c.d28=cn then
306 L_EXPIRY_DATE:=vn;
307 elsif c.d28 is null then 
308 L_EXPIRY_DATE:=vh;
309 else
310 L_EXPIRY_DATE := 
311 PER_IN_DATA_PUMP.GET_EXPIRY_DATE
312 (P_EXPIRY_DATE => c.p28);
313 end if;
314 --
315 if c.d29=cn then
316 L_ISSUING_COUNTRY:=vn;
317 elsif c.d29 is null then 
318 L_ISSUING_COUNTRY:=vh;
319 else
320 L_ISSUING_COUNTRY := 
321 PER_IN_DATA_PUMP.GET_PASSPORT_COUNTRY
322 (P_ISSUING_COUNTRY => c.p29);
323 end if;
324 --
325 hr_data_pump.api_trc_on;
326 hr_in_person_extra_info_api.update_in_passport_details
327 (p_validate => l_validate
328 ,P_PERSON_EXTRA_INFO_ID => L_PERSON_EXTRA_INFO_ID
329 ,P_OBJECT_VERSION_NUMBER => L_OBJECT_VERSION_NUMBER
330 ,P_PEI_ATTRIBUTE_CATEGORY => c.p1
331 ,P_PEI_ATTRIBUTE1 => c.p2
332 ,P_PEI_ATTRIBUTE2 => c.p3
333 ,P_PEI_ATTRIBUTE3 => c.p4
334 ,P_PEI_ATTRIBUTE4 => c.p5
335 ,P_PEI_ATTRIBUTE5 => c.p6
336 ,P_PEI_ATTRIBUTE6 => c.p7
337 ,P_PEI_ATTRIBUTE7 => c.p8
338 ,P_PEI_ATTRIBUTE8 => c.p9
339 ,P_PEI_ATTRIBUTE9 => c.p10
340 ,P_PEI_ATTRIBUTE10 => c.p11
341 ,P_PEI_ATTRIBUTE11 => c.p12
342 ,P_PEI_ATTRIBUTE12 => c.p13
343 ,P_PEI_ATTRIBUTE13 => c.p14
344 ,P_PEI_ATTRIBUTE14 => c.p15
345 ,P_PEI_ATTRIBUTE15 => c.p16
346 ,P_PEI_ATTRIBUTE16 => c.p17
347 ,P_PEI_ATTRIBUTE17 => c.p18
348 ,P_PEI_ATTRIBUTE18 => c.p19
349 ,P_PEI_ATTRIBUTE19 => c.p20
350 ,P_PEI_ATTRIBUTE20 => c.p21
351 ,P_PASSPORT_NAME => c.p22
352 ,P_PASSPORT_NUMBER => c.p23
353 ,P_PLACE_OF_ISSUE => c.p24
354 ,P_ISSUE_DATE => L_ISSUE_DATE
355 ,P_EXPIRY_DATE => L_EXPIRY_DATE
356 ,P_ECNR_REQUIRED => c.p25
357 ,P_ISSUING_COUNTRY => L_ISSUING_COUNTRY);
358 hr_data_pump.api_trc_off;
359 
360 --
361 
362 --
363 close cr;
364 --
365 hr_data_pump.exit('call');
366 exception
367  when hr_multi_message.error_message_exist then
368    if cr%isopen then
369     close cr;
370    end if;
371    hr_pump_utils.set_multi_msg_error_flag(true);
372  when others then
373  if cr%isopen then
374   close cr;
375  end if;
376  raise;
377 end call;
378 end hrdpp_update_in_passport_detai;