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_CONTACT_EXTRA_

Source


1 package body hrdpp_update_in_contact_extra_ as
2 /*
3  * Generated by hr_pump_meta_mapper at: 2007/01/03 23:01:28
4  * Generated for API: hr_in_contact_extra_info_api.update_in_contact_extra_info
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_DATETRACK_UPDATE_MODE in varchar2
84 ,P_INFORMATION_TYPE in varchar2 default null
85 ,P_NOMINATION_TYPE in varchar2 default null
86 ,P_PERCENT_SHARE in varchar2 default null
87 ,P_NOMINATION_CHANGE_REASON in varchar2 default null
88 ,P_CEI_ATTRIBUTE_CATEGORY in varchar2 default null
89 ,P_CEI_ATTRIBUTE1 in varchar2 default null
90 ,P_CEI_ATTRIBUTE2 in varchar2 default null
91 ,P_CEI_ATTRIBUTE3 in varchar2 default null
92 ,P_CEI_ATTRIBUTE4 in varchar2 default null
93 ,P_CEI_ATTRIBUTE5 in varchar2 default null
94 ,P_CEI_ATTRIBUTE6 in varchar2 default null
95 ,P_CEI_ATTRIBUTE7 in varchar2 default null
96 ,P_CEI_ATTRIBUTE8 in varchar2 default null
97 ,P_CEI_ATTRIBUTE9 in varchar2 default null
98 ,P_CEI_ATTRIBUTE10 in varchar2 default null
99 ,P_CEI_ATTRIBUTE11 in varchar2 default null
100 ,P_CEI_ATTRIBUTE12 in varchar2 default null
101 ,P_CEI_ATTRIBUTE13 in varchar2 default null
102 ,P_CEI_ATTRIBUTE14 in varchar2 default null
103 ,P_CEI_ATTRIBUTE15 in varchar2 default null
104 ,P_CEI_ATTRIBUTE16 in varchar2 default null
105 ,P_CEI_ATTRIBUTE17 in varchar2 default null
106 ,P_CEI_ATTRIBUTE18 in varchar2 default null
107 ,P_CEI_ATTRIBUTE19 in varchar2 default null
108 ,P_CEI_ATTRIBUTE20 in varchar2 default null
109 ,P_CONTACT_USER_KEY in varchar2 default null
110 ,P_CONTACTEE_USER_KEY in varchar2 default null
111 ,P_CONTACT_EXTRA_INFO_USER_KEY 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 ,pval027
154 ,pval030
155 ,pval031
156 ,pval032)
157 values
158 (p_batch_id
159 ,nvl(blid,hr_pump_batch_lines_s.nextval)
160 ,p_data_pump_business_grp_name
161 ,3222
162 ,'U'
163 ,p_user_sequence
164 ,p_link_value
165 ,dc(P_EFFECTIVE_DATE)
166 ,P_DATETRACK_UPDATE_MODE
167 ,P_INFORMATION_TYPE
168 ,P_NOMINATION_TYPE
169 ,P_PERCENT_SHARE
170 ,P_NOMINATION_CHANGE_REASON
171 ,P_CEI_ATTRIBUTE_CATEGORY
172 ,P_CEI_ATTRIBUTE1
173 ,P_CEI_ATTRIBUTE2
174 ,P_CEI_ATTRIBUTE3
175 ,P_CEI_ATTRIBUTE4
176 ,P_CEI_ATTRIBUTE5
177 ,P_CEI_ATTRIBUTE6
178 ,P_CEI_ATTRIBUTE7
179 ,P_CEI_ATTRIBUTE8
180 ,P_CEI_ATTRIBUTE9
181 ,P_CEI_ATTRIBUTE10
182 ,P_CEI_ATTRIBUTE11
183 ,P_CEI_ATTRIBUTE12
184 ,P_CEI_ATTRIBUTE13
185 ,P_CEI_ATTRIBUTE14
186 ,P_CEI_ATTRIBUTE15
187 ,P_CEI_ATTRIBUTE16
188 ,P_CEI_ATTRIBUTE17
189 ,P_CEI_ATTRIBUTE18
190 ,P_CEI_ATTRIBUTE19
191 ,P_CEI_ATTRIBUTE20
192 ,P_CONTACT_USER_KEY
193 ,P_CONTACTEE_USER_KEY
194 ,P_CONTACT_EXTRA_INFO_USER_KEY);
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,dn,d(l.pval001)) p1,
203 decode(l.pval002,cn,vn,l.pval002) p2,
204 decode(l.pval003,cn,vn,vn,vh,l.pval003) p3,
205 l.pval003 d3,
206 decode(l.pval004,cn,vn,vn,vh,
207  hr_pump_get.gl(l.pval004,'IN_NOMINATION_TYPES',d(l.pval001),vn)) p4,
208 l.pval004 d4,
209 decode(l.pval005,cn,vn,vn,vh,l.pval005) p5,
210 l.pval005 d5,
211 decode(l.pval006,cn,vn,vn,vh,l.pval006) p6,
212 l.pval006 d6,
213 decode(l.pval007,cn,vn,vn,vh,l.pval007) p7,
214 l.pval007 d7,
215 decode(l.pval008,cn,vn,vn,vh,l.pval008) p8,
216 l.pval008 d8,
217 decode(l.pval009,cn,vn,vn,vh,l.pval009) p9,
218 l.pval009 d9,
219 decode(l.pval010,cn,vn,vn,vh,l.pval010) p10,
220 l.pval010 d10,
221 decode(l.pval011,cn,vn,vn,vh,l.pval011) p11,
222 l.pval011 d11,
223 decode(l.pval012,cn,vn,vn,vh,l.pval012) p12,
224 l.pval012 d12,
225 decode(l.pval013,cn,vn,vn,vh,l.pval013) p13,
226 l.pval013 d13,
227 decode(l.pval014,cn,vn,vn,vh,l.pval014) p14,
228 l.pval014 d14,
229 decode(l.pval015,cn,vn,vn,vh,l.pval015) p15,
230 l.pval015 d15,
231 decode(l.pval016,cn,vn,vn,vh,l.pval016) p16,
232 l.pval016 d16,
233 decode(l.pval017,cn,vn,vn,vh,l.pval017) p17,
234 l.pval017 d17,
235 decode(l.pval018,cn,vn,vn,vh,l.pval018) p18,
236 l.pval018 d18,
237 decode(l.pval019,cn,vn,vn,vh,l.pval019) p19,
238 l.pval019 d19,
239 decode(l.pval020,cn,vn,vn,vh,l.pval020) p20,
240 l.pval020 d20,
241 decode(l.pval021,cn,vn,vn,vh,l.pval021) p21,
242 l.pval021 d21,
243 decode(l.pval022,cn,vn,vn,vh,l.pval022) p22,
244 l.pval022 d22,
245 decode(l.pval023,cn,vn,vn,vh,l.pval023) p23,
246 l.pval023 d23,
247 decode(l.pval024,cn,vn,vn,vh,l.pval024) p24,
248 l.pval024 d24,
249 decode(l.pval025,cn,vn,vn,vh,l.pval025) p25,
250 l.pval025 d25,
251 decode(l.pval026,cn,vn,vn,vh,l.pval026) p26,
252 l.pval026 d26,
253 decode(l.pval027,cn,vn,vn,vh,l.pval027) p27,
254 l.pval027 d27,
255 decode(l.pval028,cn,dn,d(l.pval028)) p28,
256 decode(l.pval029,cn,dn,d(l.pval029)) p29,
257 decode(l.pval030,cn,vn,vn,vn,l.pval030) p30,
258 l.pval030 d30,
259 decode(l.pval031,cn,vn,vn,vn,l.pval031) p31,
260 l.pval031 d31,
261 decode(l.pval032,cn,vn,l.pval032) p32
262 from hr_pump_batch_lines l
263 where l.batch_line_id = p_batch_line_id;
264 --
265 c cr%rowtype;
266 l_validate boolean := false;
267 L_CONTACT_RELATIONSHIP_ID number;
268 L_CONTACT_EXTRA_INFO_ID number;
269 L_OBJECT_VERSION_NUMBER number;
270 --
271 begin
272 hr_data_pump.entry('call');
273 open cr;
274 fetch cr into c;
275 if cr%notfound then
276 hr_utility.set_message(800,'HR_50326_DP_NO_ROW');
277 hr_utility.set_message_token('TABLE','HR_PUMP_BATCH_LINES');
278 hr_utility.set_message_token('COLUMN','P_BATCH_LINE_ID');
279 hr_utility.set_message_token('VALUE',p_batch_line_id);
280 hr_utility.raise_error;
281 end if;
282 --
283 if c.d30=cn or
284 c.d31=cn then
285 L_CONTACT_RELATIONSHIP_ID:=nn;
286 elsif c.d30 is null or
287 c.d31 is null then 
288 L_CONTACT_RELATIONSHIP_ID:=nh;
289 else
290 L_CONTACT_RELATIONSHIP_ID := 
291 hr_pump_get.get_contact_relationship_id
292 (P_CONTACT_USER_KEY => c.p30
293 ,P_CONTACTEE_USER_KEY => c.p31);
294 end if;
295 --
296 if c.p32 is null then
297 L_CONTACT_EXTRA_INFO_ID:=nn;
298 else
299 L_CONTACT_EXTRA_INFO_ID := 
300 PER_IN_DATA_PUMP.get_contact_extra_info_id
301 (P_CONTACT_EXTRA_INFO_USER_KEY => c.p32);
302 end if;
303 --
304 if c.p32 is null or
305 c.p1 is null then
306 L_OBJECT_VERSION_NUMBER:=nn;
307 else
308 L_OBJECT_VERSION_NUMBER := 
309 PER_IN_DATA_PUMP.GET_CONTACT_EXTRA_INFO_OVN
310 (P_CONTACT_EXTRA_INFO_USER_KEY => c.p32
311 ,P_EFFECTIVE_DATE => c.p1);
312 end if;
313 --
314 hr_data_pump.api_trc_on;
315 hr_in_contact_extra_info_api.update_in_contact_extra_info
316 (p_validate => l_validate
317 ,P_EFFECTIVE_DATE => c.p1
318 ,P_DATETRACK_UPDATE_MODE => c.p2
319 ,P_CONTACT_RELATIONSHIP_ID => L_CONTACT_RELATIONSHIP_ID
320 ,P_INFORMATION_TYPE => c.p3
321 ,P_NOMINATION_TYPE => c.p4
322 ,P_PERCENT_SHARE => c.p5
323 ,P_NOMINATION_CHANGE_REASON => c.p6
324 ,P_CEI_ATTRIBUTE_CATEGORY => c.p7
325 ,P_CEI_ATTRIBUTE1 => c.p8
326 ,P_CEI_ATTRIBUTE2 => c.p9
327 ,P_CEI_ATTRIBUTE3 => c.p10
328 ,P_CEI_ATTRIBUTE4 => c.p11
329 ,P_CEI_ATTRIBUTE5 => c.p12
330 ,P_CEI_ATTRIBUTE6 => c.p13
331 ,P_CEI_ATTRIBUTE7 => c.p14
332 ,P_CEI_ATTRIBUTE8 => c.p15
333 ,P_CEI_ATTRIBUTE9 => c.p16
334 ,P_CEI_ATTRIBUTE10 => c.p17
335 ,P_CEI_ATTRIBUTE11 => c.p18
336 ,P_CEI_ATTRIBUTE12 => c.p19
337 ,P_CEI_ATTRIBUTE13 => c.p20
338 ,P_CEI_ATTRIBUTE14 => c.p21
339 ,P_CEI_ATTRIBUTE15 => c.p22
340 ,P_CEI_ATTRIBUTE16 => c.p23
341 ,P_CEI_ATTRIBUTE17 => c.p24
342 ,P_CEI_ATTRIBUTE18 => c.p25
343 ,P_CEI_ATTRIBUTE19 => c.p26
344 ,P_CEI_ATTRIBUTE20 => c.p27
345 ,P_CONTACT_EXTRA_INFO_ID => L_CONTACT_EXTRA_INFO_ID
346 ,P_OBJECT_VERSION_NUMBER => L_OBJECT_VERSION_NUMBER
347 ,P_EFFECTIVE_START_DATE => c.p28
348 ,P_EFFECTIVE_END_DATE => c.p29);
349 hr_data_pump.api_trc_off;
350 
351 --
352 update hr_pump_batch_lines l set
353 l.pval028 = decode(c.p28,null,cn,dc(c.p28)),
354 l.pval029 = decode(c.p29,null,cn,dc(c.p29))
355 where l.rowid = c.myrowid;
356 --
357 close cr;
358 --
359 hr_data_pump.exit('call');
360 exception
361  when hr_multi_message.error_message_exist then
362    if cr%isopen then
363     close cr;
364    end if;
365    hr_pump_utils.set_multi_msg_error_flag(true);
366  when others then
367  if cr%isopen then
368   close cr;
369  end if;
370  raise;
371 end call;
372 end hrdpp_update_in_contact_extra_;