[Home] [Help]
PACKAGE BODY: APPS.HRDPP_UPDATE_USER_COLUMN_INSTA
Source
1 package body hrdpp_UPDATE_USER_COLUMN_INSTA as
2 /*
3 * Generated by hr_pump_meta_mapper at: 2007/01/03 23:01:03
4 * Generated for API: PAY_USER_COLUMN_INSTANCE_API.UPDATE_USER_COLUMN_INSTANCE
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_VALUE in varchar2 default null
85 ,P_USER_COLUMN_USER_KEY in varchar2
86 ,P_USER_ROW_USER_KEY in varchar2) is
87 blid number := p_data_pump_batch_line_id;
88 begin
89 if blid is not null then
90 delete from hr_pump_batch_lines where batch_line_id = blid;
91 delete from hr_pump_batch_exceptions
92 where source_type = 'BATCH_LINE' and source_id = blid;
93 end if;
94 insert into hr_pump_batch_lines
95 (batch_id
96 ,batch_line_id
97 ,business_group_name
98 ,api_module_id
99 ,line_status
100 ,user_sequence
101 ,link_value
102 ,pval001
103 ,pval002
104 ,pval003
105 ,pval006
106 ,pval007)
107 values
108 (p_batch_id
109 ,nvl(blid,hr_pump_batch_lines_s.nextval)
110 ,p_data_pump_business_grp_name
111 ,3048
112 ,'U'
113 ,p_user_sequence
114 ,p_link_value
115 ,dc(P_EFFECTIVE_DATE)
116 ,P_DATETRACK_UPDATE_MODE
117 ,P_VALUE
118 ,P_USER_COLUMN_USER_KEY
119 ,P_USER_ROW_USER_KEY);
120 end insert_batch_lines;
121 --
122 procedure call
123 (p_business_group_id in number,
124 p_batch_line_id in number) is
125 cursor cr is
126 select l.rowid myrowid,
127 decode(l.pval001,cn,dn,d(l.pval001)) p1,
128 decode(l.pval002,cn,vn,l.pval002) p2,
129 decode(l.pval003,cn,vn,vn,vh,l.pval003) p3,
130 l.pval003 d3,
131 decode(l.pval004,cn,dn,d(l.pval004)) p4,
132 decode(l.pval005,cn,dn,d(l.pval005)) p5,
133 decode(l.pval006,cn,vn,l.pval006) p6,
134 decode(l.pval007,cn,vn,l.pval007) p7
135 from hr_pump_batch_lines l
136 where l.batch_line_id = p_batch_line_id;
137 --
138 c cr%rowtype;
139 l_validate boolean := false;
140 L_USER_COLUMN_INSTANCE_ID number;
141 L_OBJECT_VERSION_NUMBER number;
142 --
143 begin
144 hr_data_pump.entry('call');
145 open cr;
146 fetch cr into c;
147 if cr%notfound then
148 hr_utility.set_message(800,'HR_50326_DP_NO_ROW');
149 hr_utility.set_message_token('TABLE','HR_PUMP_BATCH_LINES');
150 hr_utility.set_message_token('COLUMN','P_BATCH_LINE_ID');
151 hr_utility.set_message_token('VALUE',p_batch_line_id);
152 hr_utility.raise_error;
153 end if;
154 --
155 if c.p6 is null or
156 c.p7 is null or
157 c.p1 is null then
158 L_USER_COLUMN_INSTANCE_ID:=nn;
159 else
160 L_USER_COLUMN_INSTANCE_ID :=
161 PAY_USER_TABLE_DATA_PUMP.get_user_column_instance_id
162 (P_USER_COLUMN_USER_KEY => c.p6
163 ,P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
164 ,P_USER_ROW_USER_KEY => c.p7
165 ,P_EFFECTIVE_DATE => c.p1);
166 end if;
167 --
168 if c.p6 is null or
169 c.p7 is null or
170 c.p1 is null then
171 L_OBJECT_VERSION_NUMBER:=nn;
172 else
173 L_OBJECT_VERSION_NUMBER :=
174 PAY_USER_TABLE_DATA_PUMP.GET_USER_COLUMN_INSTANCE_OVN
175 (P_USER_COLUMN_USER_KEY => c.p6
176 ,P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
177 ,P_USER_ROW_USER_KEY => c.p7
178 ,P_EFFECTIVE_DATE => c.p1);
179 end if;
180 --
181 hr_data_pump.api_trc_on;
182 PAY_USER_COLUMN_INSTANCE_API.UPDATE_USER_COLUMN_INSTANCE
183 (p_validate => l_validate
184 ,P_EFFECTIVE_DATE => c.p1
185 ,P_USER_COLUMN_INSTANCE_ID => L_USER_COLUMN_INSTANCE_ID
186 ,P_DATETRACK_UPDATE_MODE => c.p2
187 ,P_VALUE => c.p3
188 ,P_OBJECT_VERSION_NUMBER => L_OBJECT_VERSION_NUMBER
189 ,P_EFFECTIVE_START_DATE => c.p4
190 ,P_EFFECTIVE_END_DATE => c.p5);
191 hr_data_pump.api_trc_off;
192
193 --
194 update hr_pump_batch_lines l set
195 l.pval004 = decode(c.p4,null,cn,dc(c.p4)),
196 l.pval005 = decode(c.p5,null,cn,dc(c.p5))
197 where l.rowid = c.myrowid;
198 --
199 close cr;
200 --
201 hr_data_pump.exit('call');
202 exception
203 when hr_multi_message.error_message_exist then
204 if cr%isopen then
205 close cr;
206 end if;
207 hr_pump_utils.set_multi_msg_error_flag(true);
208 when others then
209 if cr%isopen then
210 close cr;
211 end if;
212 raise;
213 end call;
214 end hrdpp_UPDATE_USER_COLUMN_INSTA;