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_IN_PASSPORT_DETAI

Source


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