[Home] [Help]
PACKAGE BODY: APPS.HXC_ENTITY_GROUPS_UPLOAD_PKG
Source
1 PACKAGE BODY hxc_entity_groups_upload_pkg AS
2 /* $Header: hxcetgupl.pkb 120.2 2005/09/23 08:08:24 sechandr noship $ */
3
4 g_debug boolean :=hr_utility.debug_enabled;
5
6 FUNCTION GET_ENTITY_GROUP_ID(p_entity_type in varchar2, p_name in varchar2)
7 RETURN NUMBER IS
8
9 cursor c_get_egroup_id (l_p_entity_type in varchar2, l_p_name in varchar2) IS
10 SELECT entity_group_id FROM hxc_entity_groups
11 WHERE entity_type = l_p_entity_type and name = l_p_name;
12 l_entity_group_id HXC_ENTITY_GROUPS.ENTITY_GROUP_ID%TYPE;
13 BEGIN
14 OPEN c_get_egroup_id(p_entity_type, p_name);
15 FETCH c_get_egroup_id INTO l_entity_group_id ;
16 CLOSE c_get_egroup_id;
17
18 RETURN l_entity_group_id ;
19
20 END GET_ENTITY_GROUP_ID;
21
22
23 PROCEDURE LOAD_ENTITY_GROUP_ROW(
24 P_NAME IN VARCHAR2,
25 P_ENTITY_TYPE IN VARCHAR2,
26 P_OWNER IN VARCHAR2,
27 P_CUSTOM_MODE IN VARCHAR2 DEFAULT NULL) IS
28
29 l_entity_group_id HXC_ENTITY_GROUPS.ENTITY_GROUP_ID%TYPE;
30 l_entity_type HXC_ENTITY_GROUPS.ENTITY_TYPE%TYPE;
31 l_ovn HXC_ENTITY_GROUPS.object_version_number%TYPE;
32 l_owner VARCHAR2(6);
33
34 BEGIN
35
36 g_debug:=hr_utility.debug_enabled;
37 if g_debug then
38 hr_utility.trace('P_NAME ='||P_NAME );
39 end if;
40 SELECT entity_group_id,
41 entity_type,
42 object_version_number,
43 DECODE( NVL(last_updated_by,-1), 1, 'SEED', 'CUSTOM')
44 INTO
45 l_entity_group_id,
46 l_entity_type ,
47 l_ovn,
48 l_owner
49 FROM hxc_entity_groups
50 WHERE name = p_name and entity_type = p_entity_type;
51
52 -- IF ( p_custom_mode = 'FORCE' OR l_owner = 'SEED' )
53 -- THEN
54 -- only update if the entity type has actually changed
55 IF ( ( p_entity_type <> l_entity_type ) )
56 THEN
57 hxc_heg_upd.upd
58 (p_entity_group_id => l_entity_group_id
59 ,p_object_version_number => l_ovn
60 ,p_name => p_name
61 ,p_entity_type => p_entity_type
62 ) ;
63 END IF;
64 -- END IF;
65
66 EXCEPTION WHEN NO_DATA_FOUND
67 THEN
68 BEGIN hxc_heg_ins.ins
69 (p_name => p_name
70 ,p_entity_type => p_entity_type
71 ,p_entity_group_id => l_entity_group_id
72 ,p_object_version_number => l_ovn
73 ) ;
74 END;
75 END LOAD_ENTITY_GROUP_ROW;
76
77
78 PROCEDURE LOAD_ENTITY_GROUP_COMPS_ROW(
79 P_ENTITY_TYPE IN VARCHAR2,
80 P_NAME IN VARCHAR2,
81 P_ATTRIBUTE_CATEGORY IN VARCHAR2,
82 P_ATTRIBUTE1 IN VARCHAR2,
83 P_ATTRIBUTE2 IN VARCHAR2,
84 P_ATTRIBUTE3 IN VARCHAR2,
85 P_OWNER IN VARCHAR2,
86 P_CUSTOM_MODE IN VARCHAR2 ) IS
87
88 l_entity_group_comp_id HXC_ENTITY_GROUP_COMPS.ENTITY_GROUP_comp_ID%TYPE;
89 l_object_version_number HXC_ENTITY_GROUP_COMPS.OBJECT_VERSION_NUMBER%TYPE;
90 l_entity_group_id HXC_ENTITY_GROUPS.ENTITY_GROUP_ID%TYPE;
91
92 l_owner VARCHAR2(6);
93
94 BEGIN
95 -- check to see row exists
96
97
98 l_entity_group_id := GET_ENTITY_GROUP_ID(P_ENTITY_TYPE,P_NAME);
99 g_debug:=hr_utility.debug_enabled;
100 if g_debug then
101 hr_utility.trace('l_entity_group_id'||l_entity_group_id);
102 end if;
103
104 SELECT
105 entity_group_comp_id,
106 OBJECT_VERSION_NUMBER,
107 DECODE( NVL(last_updated_by,-1),1, 'SEED', 'CUSTOM')
108 INTO
109 l_entity_group_comp_id,
110 l_object_version_number,
111 l_owner
112 FROM hxc_entity_group_comps
113 WHERE entity_group_id = l_entity_group_id
114 AND attribute_category = P_ATTRIBUTE_CATEGORY;
115
116 if g_debug then
117 hr_utility.trace('l_entity_group_comp_id=2='||l_entity_group_comp_id);
118 hr_utility.trace('p_custom_mode='||p_custom_mode);
119 hr_utility.trace('l_owner ='||l_owner );
120 end if;
121
122 -- IF ( p_custom_mode = 'FORCE' OR l_owner = 'SEED' )
123 -- THEN
124 if g_debug then
125 hr_utility.trace('Starting to upd --');
126 end if;
127 hxc_egc_upd.upd(
128 p_entity_group_comp_id => l_entity_group_comp_id
129 ,p_object_version_number => l_object_version_number
130 ,p_entity_group_id => l_entity_group_id
131 ,p_entity_id => -1
132 ,p_entity_type => P_ENTITY_TYPE
133 ,p_attribute_category => P_ATTRIBUTE_CATEGORY
134 ,p_attribute1 => P_ATTRIBUTE1
135 ,p_attribute2 => P_ATTRIBUTE2
136 ,p_attribute3 => P_ATTRIBUTE3
137 ,p_effective_date => sysdate
138 ,p_called_from_form => null
139 );
140 if g_debug then
141 hr_utility.trace('Finishing from upd --');
142 end if;
143 -- END IF;
144
145 EXCEPTION WHEN NO_DATA_FOUND
146 THEN
147 BEGIN
148 if g_debug then
149 hr_utility.trace('Starting to ins--');
150 end if;
151
152 hxc_egc_ins.ins(
153 p_entity_group_comp_id => l_entity_group_comp_id
154 ,p_object_version_number => l_object_version_number
155 ,p_entity_group_id => l_entity_group_id
156 ,p_entity_id => -1
157 ,p_entity_type => P_ENTITY_TYPE
158 ,p_attribute_category => P_ATTRIBUTE_CATEGORY
159 ,p_attribute1 => P_ATTRIBUTE1
160 ,p_attribute2 => P_ATTRIBUTE2
161 ,p_attribute3 => P_ATTRIBUTE3
162 ,p_effective_date => sysdate
163 ,p_called_from_form => null
164 );
165 if g_debug then
166 hr_utility.trace('Finishing to ins--');
167 end if;
168 END;
169
170 END LOAD_ENTITY_GROUP_COMPS_ROW;
171
172 PROCEDURE LOAD_ENTITY_GROUP_ROW(
173 P_NAME IN VARCHAR2,
174 P_ENTITY_TYPE IN VARCHAR2,
175 P_OWNER IN VARCHAR2,
176 P_CUSTOM_MODE IN VARCHAR2 DEFAULT NULL
177 ,p_last_update_date IN VARCHAR2) IS
178
179 l_entity_group_id HXC_ENTITY_GROUPS.ENTITY_GROUP_ID%TYPE;
180 l_entity_type HXC_ENTITY_GROUPS.ENTITY_TYPE%TYPE;
181 l_ovn HXC_ENTITY_GROUPS.object_version_number%TYPE;
182 l_last_update_date_db HXC_ENTITY_GROUPS.last_update_date%TYPE;
183 l_last_updated_by_db HXC_ENTITY_GROUPS.last_updated_by%TYPE;
184 l_last_updated_by_f HXC_ENTITY_GROUPS.last_updated_by%TYPE;
185 l_last_update_date_f HXC_ENTITY_GROUPS.last_update_date%TYPE;
186
187 BEGIN
188
189 l_last_updated_by_f := fnd_load_util.owner_id(p_owner);
190 l_last_update_date_f := nvl(to_date(p_last_update_date, 'YYYY/MM/DD'), sysdate);
191 g_debug:=hr_utility.debug_enabled;
192
193 if g_debug then
194 hr_utility.trace('P_NAME ='||P_NAME );
195 end if;
196
197 SELECT entity_group_id,
198 entity_type,
199 object_version_number
200 ,last_update_date
201 ,last_updated_by
202
203 INTO
204 l_entity_group_id,
205 l_entity_type ,
206 l_ovn
207 ,l_last_update_date_db
208 ,l_last_updated_by_db
209
210 FROM hxc_entity_groups
211 WHERE name = p_name and entity_type = p_entity_type;
212
213 IF (fnd_load_util.upload_test( l_last_updated_by_f,
214 l_last_update_date_f,
215 l_last_updated_by_db,
216 l_last_update_date_db ,
217 p_custom_mode))
218 THEN
219 -- only update if the entity type has actually changed
220 IF ( ( p_entity_type <> l_entity_type ) )
221 THEN
222 hxc_heg_upd.upd
223 (p_entity_group_id => l_entity_group_id
224 ,p_object_version_number => l_ovn
225 ,p_name => p_name
226 ,p_entity_type => p_entity_type
227 ) ;
228 END IF;
229 END IF;
230
231 EXCEPTION WHEN NO_DATA_FOUND
232 THEN
233 BEGIN hxc_heg_ins.ins
234 (p_name => p_name
235 ,p_entity_type => p_entity_type
236 ,p_entity_group_id => l_entity_group_id
237 ,p_object_version_number => l_ovn
238 ) ;
239 END;
240 END LOAD_ENTITY_GROUP_ROW;
241
242
243 PROCEDURE LOAD_ENTITY_GROUP_COMPS_ROW(
244 P_ENTITY_TYPE IN VARCHAR2,
245 P_NAME IN VARCHAR2,
246 P_ATTRIBUTE_CATEGORY IN VARCHAR2,
247 P_ATTRIBUTE1 IN VARCHAR2,
248 P_ATTRIBUTE2 IN VARCHAR2,
249 P_ATTRIBUTE3 IN VARCHAR2,
250 P_OWNER IN VARCHAR2,
251 P_CUSTOM_MODE IN VARCHAR2
252 ,p_last_update_date IN VARCHAR2) IS
253
254 l_entity_group_comp_id HXC_ENTITY_GROUP_COMPS.ENTITY_GROUP_comp_ID%TYPE;
255 l_object_version_number HXC_ENTITY_GROUP_COMPS.OBJECT_VERSION_NUMBER%TYPE;
256 l_entity_group_id HXC_ENTITY_GROUPS.ENTITY_GROUP_ID%TYPE;
257
258 l_last_update_date_db HXC_ENTITY_GROUP_COMPS.last_update_date%TYPE;
259 l_last_updated_by_db HXC_ENTITY_GROUP_COMPS.last_updated_by%TYPE;
260 l_last_updated_by_f HXC_ENTITY_GROUP_COMPS.last_updated_by%TYPE;
261 l_last_update_date_f HXC_ENTITY_GROUP_COMPS.last_update_date%TYPE;
262
263 BEGIN
264 -- check to see row exists
265
266 l_last_updated_by_f := fnd_load_util.owner_id(p_owner);
267 l_last_update_date_f := nvl(to_date(p_last_update_date, 'YYYY/MM/DD'), sysdate);
268 l_entity_group_id := GET_ENTITY_GROUP_ID(P_ENTITY_TYPE,P_NAME);
269 g_debug:=hr_utility.debug_enabled;
270
271 if g_debug then
272 hr_utility.trace('l_entity_group_id'||l_entity_group_id);
273 end if;
274
275 SELECT
276 entity_group_comp_id,
277 OBJECT_VERSION_NUMBER
278 ,last_update_date
279 ,last_updated_by
280 INTO
281 l_entity_group_comp_id,
282 l_object_version_number
283 ,l_last_update_date_db
284 ,l_last_updated_by_db
285 FROM hxc_entity_group_comps
286 WHERE entity_group_id = l_entity_group_id
287 AND attribute_category = P_ATTRIBUTE_CATEGORY;
288
289 if g_debug then
290 hr_utility.trace('l_entity_group_comp_id=2='||l_entity_group_comp_id);
291 hr_utility.trace('p_custom_mode='||p_custom_mode);
292 end if;
293
294 IF (fnd_load_util.upload_test( l_last_updated_by_f,
295 l_last_update_date_f,
296 l_last_updated_by_db,
297 l_last_update_date_db ,
298 p_custom_mode))
299 THEN
300 if g_debug then
301 hr_utility.trace('Starting to upd --');
302 end if;
303 hxc_egc_upd.upd(
304 p_entity_group_comp_id => l_entity_group_comp_id
305 ,p_object_version_number => l_object_version_number
306 ,p_entity_group_id => l_entity_group_id
307 ,p_entity_id => -1
308 ,p_entity_type => P_ENTITY_TYPE
309 ,p_attribute_category => P_ATTRIBUTE_CATEGORY
310 ,p_attribute1 => P_ATTRIBUTE1
311 ,p_attribute2 => P_ATTRIBUTE2
312 ,p_attribute3 => P_ATTRIBUTE3
313 ,p_effective_date => sysdate
314 ,p_called_from_form => null
315 );
316 if g_debug then
317 hr_utility.trace('Finishing from upd --');
318 end if;
319 END IF;
320
321 EXCEPTION WHEN NO_DATA_FOUND
322 THEN
323 BEGIN
324 if g_debug then
325 hr_utility.trace('Starting to ins--');
326 end if;
327
328 hxc_egc_ins.ins(
329 p_entity_group_comp_id => l_entity_group_comp_id
330 ,p_object_version_number => l_object_version_number
331 ,p_entity_group_id => l_entity_group_id
332 ,p_entity_id => -1
333 ,p_entity_type => P_ENTITY_TYPE
334 ,p_attribute_category => P_ATTRIBUTE_CATEGORY
335 ,p_attribute1 => P_ATTRIBUTE1
336 ,p_attribute2 => P_ATTRIBUTE2
337 ,p_attribute3 => P_ATTRIBUTE3
338 ,p_effective_date => sysdate
339 ,p_called_from_form => null
340 );
341 if g_debug then
342 hr_utility.trace('Finishing to ins--');
343 end if;
344 END;
345
346 END LOAD_ENTITY_GROUP_COMPS_ROW;
347
348
349 END hxc_entity_groups_upload_pkg;