[Home] [Help]
PACKAGE BODY: APPS.PQH_CEF_SHD
Source
1 Package Body pqh_cef_shd as
2 /* $Header: pqcefrhi.pkb 120.2 2005/10/12 20:18:19 srajakum noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_cef_shd.'; -- Global package name
9 --
10 --
11 -- ----------------------------------------------------------------------------
12 -- |---------------------------< constraint_error >---------------------------|
13 -- ----------------------------------------------------------------------------
14 Procedure constraint_error
15 (p_constraint_name in all_constraints.constraint_name%TYPE) Is
16 --
17 l_proc varchar2(72) := g_package||'constraint_error';
18 --
19 Begin
20 hr_utility.set_location('Entering:'||l_proc, 5);
21 --
22 If (p_constraint_name = 'PQH_COPY_ENTITY_FUNCTIONS_FK1') Then
23 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
24 hr_utility.set_message_token('PROCEDURE', l_proc);
25 hr_utility.set_message_token('STEP','5');
26 hr_utility.raise_error;
27 ElsIf (p_constraint_name = 'PQH_COPY_ENTITY_FUNCTIONS_FK2') Then
28 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
29 hr_utility.set_message_token('PROCEDURE', l_proc);
30 hr_utility.set_message_token('STEP','10');
31 hr_utility.raise_error;
32 Else
33 hr_utility.set_message(801, 'HR_7877_API_INVALID_CONSTRAINT');
34 hr_utility.set_message_token('PROCEDURE', l_proc);
35 hr_utility.set_message_token('CONSTRAINT_NAME', p_constraint_name);
36 hr_utility.raise_error;
37 End If;
38 --
39 hr_utility.set_location(' Leaving:'||l_proc, 10);
40 End constraint_error;
41 --
42 -- ----------------------------------------------------------------------------
43 -- |-----------------------------< api_updating >-----------------------------|
44 -- ----------------------------------------------------------------------------
45 Function api_updating
46 (
47 p_copy_entity_function_id in number,
48 p_object_version_number in number
49 ) Return Boolean Is
50 --
51 --
52 -- Cursor selects the 'current' row from the HR Schema
53 --
54 Cursor C_Sel1 is
55 select
56 copy_entity_function_id,
57 table_route_id,
58 function_type_cd,
59 pre_copy_function_name,
60 copy_function_name,
61 post_copy_function_name,
62 object_version_number,
63 context
64 from pqh_copy_entity_functions
65 where copy_entity_function_id = p_copy_entity_function_id;
66 --
67 l_proc varchar2(72) := g_package||'api_updating';
68 l_fct_ret boolean;
69 --
70 Begin
71 hr_utility.set_location('Entering:'||l_proc, 5);
72 --
73 If (
74 p_copy_entity_function_id is null and
75 p_object_version_number is null
76 ) Then
77 --
78 -- One of the primary key arguments is null therefore we must
79 -- set the returning function value to false
80 --
81 l_fct_ret := false;
82 Else
83 If (
84 p_copy_entity_function_id = g_old_rec.copy_entity_function_id and
85 p_object_version_number = g_old_rec.object_version_number
86 ) Then
87 hr_utility.set_location(l_proc, 10);
88 --
89 -- The g_old_rec is current therefore we must
90 -- set the returning function to true
91 --
92 l_fct_ret := true;
93 Else
94 --
95 -- Select the current row into g_old_rec
96 --
97 Open C_Sel1;
98 Fetch C_Sel1 Into g_old_rec;
99 If C_Sel1%notfound Then
100 Close C_Sel1;
101 --
102 -- The primary key is invalid therefore we must error
103 --
104 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
105 hr_utility.raise_error;
106 End If;
107 Close C_Sel1;
108 If (p_object_version_number <> g_old_rec.object_version_number) Then
109 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
110 hr_utility.raise_error;
111 End If;
112 hr_utility.set_location(l_proc, 15);
113 l_fct_ret := true;
114 End If;
115 End If;
116 hr_utility.set_location(' Leaving:'||l_proc, 20);
117 Return (l_fct_ret);
118 --
119 End api_updating;
120 --
121 -- ----------------------------------------------------------------------------
122 -- |---------------------------------< lck >----------------------------------|
123 -- ----------------------------------------------------------------------------
124 Procedure lck
125 (
126 p_copy_entity_function_id in number,
127 p_object_version_number in number
128 ) is
129 --
130 -- Cursor selects the 'current' row from the HR Schema
131 --
132 Cursor C_Sel1 is
133 select copy_entity_function_id,
134 table_route_id,
135 function_type_cd,
136 pre_copy_function_name,
137 copy_function_name,
138 post_copy_function_name,
139 object_version_number,
140 context
141 from pqh_copy_entity_functions
142 where copy_entity_function_id = p_copy_entity_function_id
143 for update nowait;
144 --
145 l_proc varchar2(72) := g_package||'lck';
146 --
147 Begin
148 hr_utility.set_location('Entering:'||l_proc, 5);
149 --
150 -- Add any mandatory argument checking here:
151 -- Example:
152 -- hr_api.mandatory_arg_error
153 -- (p_api_name => l_proc,
154 -- p_argument => 'object_version_number',
155 -- p_argument_value => p_object_version_number);
156 --
157 Open C_Sel1;
158 Fetch C_Sel1 Into g_old_rec;
159 If C_Sel1%notfound then
160 Close C_Sel1;
161 --
162 -- The primary key is invalid therefore we must error
163 --
164 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
165 hr_utility.raise_error;
166 End If;
167 Close C_Sel1;
168 If (p_object_version_number <> g_old_rec.object_version_number) Then
169 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
170 hr_utility.raise_error;
171 End If;
172 --
173 hr_utility.set_location(' Leaving:'||l_proc, 10);
174 --
175 -- We need to trap the ORA LOCK exception
176 --
177 Exception
178 When HR_Api.Object_Locked then
179 --
180 -- The object is locked therefore we need to supply a meaningful
181 -- error message.
182 --
183 hr_utility.set_message(801, 'HR_7165_OBJECT_LOCKED');
184 hr_utility.set_message_token('TABLE_NAME', 'pqh_copy_entity_functions');
185 hr_utility.raise_error;
186 End lck;
187 --
188 -- ----------------------------------------------------------------------------
189 -- |-----------------------------< convert_args >-----------------------------|
190 -- ----------------------------------------------------------------------------
191 Function convert_args
192 (
193 p_copy_entity_function_id in number,
194 p_table_route_id in number,
195 p_function_type_cd in varchar2,
196 p_pre_copy_function_name in varchar2,
197 p_copy_function_name in varchar2,
198 p_post_copy_function_name in varchar2,
199 p_object_version_number in number,
200 p_context in varchar2
201 )
202 Return g_rec_type is
203 --
204 l_rec g_rec_type;
205 l_proc varchar2(72) := g_package||'convert_args';
206 --
207 Begin
208 --
209 hr_utility.set_location('Entering:'||l_proc, 5);
210 --
211 -- Convert arguments into local l_rec structure.
212 --
213 l_rec.copy_entity_function_id := p_copy_entity_function_id;
214 l_rec.table_route_id := p_table_route_id;
215 l_rec.function_type_cd := p_function_type_cd;
216 l_rec.pre_copy_function_name := p_pre_copy_function_name;
217 l_rec.copy_function_name := p_copy_function_name;
218 l_rec.post_copy_function_name := p_post_copy_function_name;
219 l_rec.object_version_number := p_object_version_number;
220 l_rec.context := p_context;
221 --
222 -- Return the plsql record structure.
223 --
224 hr_utility.set_location(' Leaving:'||l_proc, 10);
225 Return(l_rec);
226 --
227 End convert_args;
228 --
229 -- ----------------------------------------------------------------------------
230 -- |----------------------------------< load_row >---------------------------------|
231 -- ----------------------------------------------------------------------------
232 --
233 Procedure load_row
234 ( p_table_alias in varchar2
235 ,p_function_type_cd in varchar2
236 ,p_pre_copy_function_name in varchar2
237 ,p_copy_function_name in varchar2
238 ,p_post_copy_function_name in varchar2
239 ,p_context in varchar2
240 ,p_owner in varchar2
241 ,p_last_update_date in varchar2
242 ) is
243 --
244 --
245 l_effective_date date := sysdate ;
246 l_object_version_number number := 1;
247 l_language varchar2(30) ;
248 --
249 l_table_route_id pqh_copy_entity_functions.table_route_id%TYPE;
250 l_copy_entity_function_id pqh_copy_entity_functions.copy_entity_function_id%TYPE := 0;
251 --
252 --
253 l_created_by pqh_copy_entity_functions.created_by%TYPE;
254 l_last_updated_by pqh_copy_entity_functions.last_updated_by%TYPE;
255 l_creation_date pqh_copy_entity_functions.creation_date%TYPE;
256 l_last_update_date pqh_copy_entity_functions.last_update_date%TYPE;
257 l_last_update_login pqh_copy_entity_functions.last_update_login%TYPE;
258 --
259 cursor c1 is select userenv('LANG') from dual ;
260 --
261 --
262 -- developer key is table_alias + function_type_cd + context
263 --
264 cursor csr_table_route_id(p_table_alias in varchar2) is
265 select table_route_id
266 from pqh_table_route
267 where table_alias = p_table_alias;
268 --
269 cursor csr_cef_id (p_table_route_id in number,
270 p_function_type_cd in varchar2,
271 p_context in varchar2) is
272 select copy_entity_function_id
273 from pqh_copy_entity_functions
274 where table_route_id = p_table_route_id
275 and function_type_cd = p_function_type_cd
276 and context = p_context;
277
278 --
279 l_data_migrator_mode varchar2(1);
280 --
281 --
282 --
283 --
284 Begin
285 --
286 -- key to ids
287 --
288 l_data_migrator_mode := hr_general.g_data_migrator_mode ;
289 hr_general.g_data_migrator_mode := 'Y';
290 --
291 open c1;
292 fetch c1 into l_language ;
293 close c1;
294 --
295 open csr_table_route_id(p_table_alias => p_table_alias );
296 fetch csr_table_route_id into l_table_route_id;
297 close csr_table_route_id;
298 --
299 open csr_cef_id(p_table_route_id => l_table_route_id,
300 p_function_type_cd => p_function_type_cd,
301 p_context => p_context);
302 fetch csr_cef_id into l_copy_entity_function_id;
303 close csr_cef_id;
304 --
305 --
306 -- populate WHO columns
307 --
308 /**
309 l_created_by := 1;
310 l_last_updated_by := 1;
311 **/
312 l_last_updated_by := fnd_load_util.owner_id(p_owner);
313 l_created_by := fnd_load_util.owner_id(p_owner);
314 l_creation_date := nvl(to_date(p_last_update_date,'YYYY/MM/DD'),trunc(sysdate));
315 l_last_update_date := nvl(to_date(p_last_update_date,'YYYY/MM/DD'),trunc(sysdate));
316 /**
317 l_creation_date := sysdate;
318 l_last_update_date := sysdate;
319 **/
320 l_last_update_login := 0;
321
322 --
323 --
324 Begin
325 --
326 --
327 if l_copy_entity_function_id <> 0 then
328 -- row exits so update
329 update pqh_copy_entity_functions
330 set pre_copy_function_name = p_pre_copy_function_name,
331 copy_function_name = p_copy_function_name,
332 post_copy_function_name = p_post_copy_function_name,
333 last_updated_by = l_last_updated_by,
334 last_update_date = l_last_update_date,
335 last_update_login = l_last_update_login
336 where copy_entity_function_id = l_copy_entity_function_id
337 and nvl(last_updated_by , -1) in (l_last_updated_by,-1,1);
338 else
339 -- insert row
340
341 select pqh_copy_entity_functions_s.nextval into l_copy_entity_function_id from dual;
342
343 insert into pqh_copy_entity_functions
344 ( copy_entity_function_id,
345 table_route_id,
346 function_type_cd,
347 pre_copy_function_name,
348 copy_function_name,
349 post_copy_function_name,
350 object_version_number,
351 context,
352 creation_date,
353 created_by,
354 last_update_date,
355 last_update_login,
356 last_updated_by
357 )
358 Values
359 (
360 l_copy_entity_function_id,
361 l_table_route_id,
362 p_function_type_cd,
363 p_pre_copy_function_name,
364 p_copy_function_name,
365 p_post_copy_function_name,
366 l_object_version_number,
367 p_context,
368 l_creation_date,
369 l_created_by,
370 l_last_update_date,
371 l_last_update_login,
372 l_last_updated_by
373 );
374
375 end if;
376
377 End;
378
379
380 hr_general.g_data_migrator_mode := l_data_migrator_mode;
381
382
383 end load_row;
384
385 --
386 --
387
388 end pqh_cef_shd;