[Home] [Help]
PACKAGE BODY: APPS.PQH_RFT_SHD
Source
1 Package Body pqh_rft_shd as
2 /* $Header: pqrftrhi.pkb 120.2 2005/10/12 20:19:02 srajakum noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_rft_shd.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |---------------------------< constraint_error >---------------------------|
12 -- ----------------------------------------------------------------------------
13 Procedure constraint_error
14 (p_constraint_name in all_constraints.constraint_name%TYPE) Is
15 --
16 l_proc varchar2(72) := g_package||'constraint_error';
17 --
18 Begin
19 hr_utility.set_location('Entering:'||l_proc, 5);
20 --
21 If (p_constraint_name = 'PQH_REF_TEMPLATES_FK1') Then
22 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
23 hr_utility.set_message_token('PROCEDURE', l_proc);
24 hr_utility.set_message_token('STEP','5');
25 hr_utility.raise_error;
26 ElsIf (p_constraint_name = 'PQH_REF_TEMPLATES_PK') Then
27 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
28 hr_utility.set_message_token('PROCEDURE', l_proc);
29 hr_utility.set_message_token('STEP','10');
30 hr_utility.raise_error;
31 ElsIf (p_constraint_name = 'PQH_REF_TEMPLATES_FK2') Then
32 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
33 hr_utility.set_message_token('PROCEDURE', l_proc);
34 hr_utility.set_message_token('STEP','15');
35 hr_utility.raise_error;
36 ElsIf (p_constraint_name = 'PQH_REF_TEMPLATES_UK') Then
37 hr_utility.set_message(8302, 'PQH_DUPLICATE_REF_TEMPLATE');
38 hr_utility.raise_error;
39 /**
40 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
41 hr_utility.set_message_token('PROCEDURE', l_proc);
42 hr_utility.set_message_token('STEP','20');
43 hr_utility.raise_error;
44 **/
45 Else
46 hr_utility.set_message(801, 'HR_7877_API_INVALID_CONSTRAINT');
47 hr_utility.set_message_token('PROCEDURE', l_proc);
48 hr_utility.set_message_token('CONSTRAINT_NAME', p_constraint_name);
49 hr_utility.raise_error;
50 End If;
51 --
52 hr_utility.set_location(' Leaving:'||l_proc, 10);
53 End constraint_error;
54 --
55 -- ----------------------------------------------------------------------------
56 -- |-----------------------------< api_updating >-----------------------------|
57 -- ----------------------------------------------------------------------------
58 Function api_updating
59 (
60 p_ref_template_id in number,
61 p_object_version_number in number
62 ) Return Boolean Is
63 --
64 --
65 -- Cursor selects the 'current' row from the HR Schema
66 --
67 Cursor C_Sel1 is
68 select
69 ref_template_id,
70 base_template_id,
71 parent_template_id,
72 reference_type_cd,
73 object_version_number
74 from pqh_ref_templates
75 where ref_template_id = p_ref_template_id;
76 --
77 l_proc varchar2(72) := g_package||'api_updating';
78 l_fct_ret boolean;
79 --
80 Begin
81 hr_utility.set_location('Entering:'||l_proc, 5);
82 --
83 If (
84 p_ref_template_id is null and
85 p_object_version_number is null
86 ) Then
87 --
88 -- One of the primary key arguments is null therefore we must
89 -- set the returning function value to false
90 --
91 l_fct_ret := false;
92 Else
93 If (
94 p_ref_template_id = g_old_rec.ref_template_id and
95 p_object_version_number = g_old_rec.object_version_number
96 ) Then
97 hr_utility.set_location(l_proc, 10);
98 --
99 -- The g_old_rec is current therefore we must
100 -- set the returning function to true
101 --
102 l_fct_ret := true;
103 Else
104 --
105 -- Select the current row into g_old_rec
106 --
107 Open C_Sel1;
108 Fetch C_Sel1 Into g_old_rec;
109 If C_Sel1%notfound Then
110 Close C_Sel1;
111 --
112 -- The primary key is invalid therefore we must error
113 --
114 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
115 hr_utility.raise_error;
116 End If;
117 Close C_Sel1;
118 If (p_object_version_number <> g_old_rec.object_version_number) Then
119 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
120 hr_utility.raise_error;
121 End If;
122 hr_utility.set_location(l_proc, 15);
123 l_fct_ret := true;
124 End If;
125 End If;
126 hr_utility.set_location(' Leaving:'||l_proc, 20);
127 Return (l_fct_ret);
128 --
129 End api_updating;
130 --
131 -- ----------------------------------------------------------------------------
132 -- |---------------------------------< lck >----------------------------------|
133 -- ----------------------------------------------------------------------------
134 Procedure lck
135 (
136 p_ref_template_id in number,
137 p_object_version_number in number
138 ) is
139 --
140 -- Cursor selects the 'current' row from the HR Schema
141 --
142 Cursor C_Sel1 is
143 select ref_template_id,
144 base_template_id,
145 parent_template_id,
146 reference_type_cd,
147 object_version_number
148 from pqh_ref_templates
149 where ref_template_id = p_ref_template_id
150 for update nowait;
151 --
152 l_proc varchar2(72) := g_package||'lck';
153 --
154 Begin
155 hr_utility.set_location('Entering:'||l_proc, 5);
156 --
157 -- Add any mandatory argument checking here:
158 -- Example:
159 -- hr_api.mandatory_arg_error
160 -- (p_api_name => l_proc,
161 -- p_argument => 'object_version_number',
162 -- p_argument_value => p_object_version_number);
163 --
164 Open C_Sel1;
165 Fetch C_Sel1 Into g_old_rec;
166 If C_Sel1%notfound then
167 Close C_Sel1;
168 --
169 -- The primary key is invalid therefore we must error
170 --
171 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
172 hr_utility.raise_error;
173 End If;
174 Close C_Sel1;
175 If (p_object_version_number <> g_old_rec.object_version_number) Then
176 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
177 hr_utility.raise_error;
178 End If;
179 --
180 hr_utility.set_location(' Leaving:'||l_proc, 10);
181 --
182 -- We need to trap the ORA LOCK exception
183 --
184 Exception
185 When HR_Api.Object_Locked then
186 --
187 -- The object is locked therefore we need to supply a meaningful
188 -- error message.
189 --
190 hr_utility.set_message(801, 'HR_7165_OBJECT_LOCKED');
191 hr_utility.set_message_token('TABLE_NAME', 'pqh_ref_templates');
192 hr_utility.raise_error;
193 End lck;
194 --
195 -- ----------------------------------------------------------------------------
196 -- |-----------------------------< convert_args >-----------------------------|
197 -- ----------------------------------------------------------------------------
198 Function convert_args
199 (
200 p_ref_template_id in number,
201 p_base_template_id in number,
202 p_parent_template_id in number,
203 p_reference_type_cd in varchar2,
204 p_object_version_number in number
205 )
206 Return g_rec_type is
207 --
208 l_rec g_rec_type;
209 l_proc varchar2(72) := g_package||'convert_args';
210 --
211 Begin
212 --
213 hr_utility.set_location('Entering:'||l_proc, 5);
214 --
215 -- Convert arguments into local l_rec structure.
216 --
217 l_rec.ref_template_id := p_ref_template_id;
218 l_rec.base_template_id := p_base_template_id;
219 l_rec.parent_template_id := p_parent_template_id;
220 l_rec.reference_type_cd := p_reference_type_cd;
221 l_rec.object_version_number := p_object_version_number;
222 --
223 -- Return the plsql record structure.
224 --
225 hr_utility.set_location(' Leaving:'||l_proc, 10);
226 Return(l_rec);
227 --
228 End convert_args;
229 --
230 --
231 -------------------------------------------------------------------------------
232 -- |----------------< create_update_copied_attribute >------------------------
233 -------------------------------------------------------------------------------
234 --
235 procedure create_update_copied_attribute
236 (
237 p_copied_attributes in pqh_prvcalc.t_attid_priv,
238 p_template_id in number,
239 p_owner in varchar2 default null) is
240 --
241 Cursor csr_tem_attr(p_attribute_id in number) is
242 Select template_attribute_id,object_version_number
243 From pqh_template_attributes
244 Where template_id = p_template_id
245 and attribute_id = p_attribute_id
246 FOR UPDATE ;
247 --
248 cursor c_select_flag(p_attribute_id number, p_template_id number) is
249 select 'x'
250 from pqh_attributes att, pqh_txn_category_attributes tct, pqh_templates tem
251 where
252 att.attribute_id = p_attribute_id
253 and att.attribute_id = tct.attribute_id
254 and tem.template_id = p_template_id
255 and tct.transaction_category_id = tem.transaction_category_id
256 and nvl(tct.select_flag,'N')='Y';
257 --
258 l_template_attribute_id pqh_template_attributes.template_attribute_id%type;
259 l_ovn pqh_template_attributes.object_version_number%type;
260 l_view_flag pqh_template_attributes.view_flag%type;
261 l_edit_flag pqh_template_attributes.edit_flag%type;
262 --
263 l_dummy varchar2(30);
264 --
265 l_proc varchar2(72) := g_package||'create_update_copied_attribute';
266 --
267 --
268 l_created_by pqh_template_attributes.created_by%TYPE;
269 l_last_updated_by pqh_template_attributes.last_updated_by%TYPE;
270 l_creation_date pqh_template_attributes.creation_date%TYPE;
271 l_last_update_date pqh_template_attributes.last_update_date%TYPE;
272 l_last_update_login pqh_template_attributes.last_update_login%TYPE;
273 --
274 begin
275 --
276 hr_utility.set_location('Entering:'|| l_proc, 10);
277 --
278 /**
279 if p_owner = 'SEED' then
280 l_created_by := 1;
281 l_last_updated_by := -1;
282 else
283 l_created_by := 0;
284 l_last_updated_by := 0;
285 end if;
286 **/
287 l_last_updated_by := fnd_load_util.owner_id(p_owner);
288 l_created_by := l_last_updated_by;
289
290 l_creation_date := sysdate;
291 l_last_update_date := sysdate;
292 l_last_update_login := 0;
293 --
294 For cnt in p_copied_attributes.FIRST .. p_copied_attributes.LAST loop
295 --
296 open c_select_flag(p_copied_attributes(cnt).attribute_id, p_template_id);
297 fetch c_select_flag into l_dummy;
298 if c_select_flag%found then
299 --
300 If p_copied_attributes(cnt).mode_flag = 'E' then
301 l_view_flag := 'Y';
302 l_edit_flag := 'Y';
303 elsif p_copied_attributes(cnt).mode_flag ='V' then
304 l_view_flag := 'Y';
305 l_edit_flag := 'N';
306 Else
307 l_view_flag := 'N';
308 l_edit_flag := 'N';
309 End if;
310 --
311 Open csr_tem_attr(p_attribute_id =>p_copied_attributes(cnt).attribute_id);
312 --
313 Fetch csr_tem_attr into l_template_attribute_id,l_ovn;
314 --
315 If csr_tem_attr%found then
316 --
317 update pqh_template_attributes
318 set
319 required_flag = p_copied_attributes(cnt).reqd_flag
320 ,view_flag = l_view_flag
321 ,edit_flag = l_edit_flag
322 ,last_update_date = l_last_update_date
323 ,last_updated_by = l_last_updated_by
324 ,last_update_login = l_last_update_login
325 where template_attribute_id = l_template_attribute_id;
326 --
327 Else
328 --
329 insert into pqh_template_attributes
330 (
331 required_flag
332 ,view_flag
333 ,edit_flag
334 ,template_attribute_id
335 ,attribute_id
336 ,template_id
337 ,object_version_number
338 ,LAST_UPDATE_DATE, LAST_UPDATED_BY,
339 LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE
340 )
341 values
342 (
343 p_copied_attributes(cnt).reqd_flag
344 ,l_view_flag
345 ,l_edit_flag
346 ,pqh_template_attributes_s.nextval
347 ,p_copied_attributes(cnt).attribute_id
348 ,p_template_id
349 ,1
350 ,L_LAST_UPDATE_DATE, L_LAST_UPDATED_BY,
351 L_LAST_UPDATE_LOGIN, L_CREATED_BY, L_CREATION_DATE);
352
353 --
354 End if;
355 --
356 Close csr_tem_attr;
357 end if;
358 close c_select_flag;
359 --
360
361 End loop;
362 --
363 hr_utility.set_location(' Leaving:'||l_proc, 70);
364 --
365 end create_update_copied_attribute;
366 --
367 --
368 procedure apply_copy_template(
369 p_parent_template_id number,
370 p_base_template_id number,
371 p_owner in varchar2 default null) is
372 --
373 copied_attr_tab pqh_prvcalc.t_attid_priv;
374 begin
375 pqh_prvcalc.task_result_update(p_parent_template_id,
376 'T',
377 copied_attr_tab);
378 pqh_prvcalc.task_result_update(p_base_template_id,
379 'T',
380 copied_attr_tab);
381 --
382 -- Save up the copied attributes in the database
383 --
384 If copied_attr_tab.count > 0 then
385
386 create_update_copied_attribute
387 (p_copied_attributes => copied_attr_tab
388 ,p_template_id => p_parent_template_id
389 ,p_owner => p_owner);
390 --
391 --
392 --
393 End if;
394 end;
395 --
396 procedure LOAD_ROW
397 (p_parent_template_short_name in varchar2
398 ,p_base_template_short_name in varchar2
399 ,p_reference_type_cd in varchar2
400 ,p_owner in varchar2
401 ,p_last_update_date in varchar2
402 ) is
403 l_dummy varchar2(10);
404 l_object_version_number number(15) := 1;
405 l_parent_template_id pqh_ref_templates.parent_template_id%type;
406 l_base_template_id pqh_ref_templates.base_template_id%type;
407 --
408 l_created_by pqh_templates.created_by%TYPE;
409 l_last_updated_by pqh_templates.last_updated_by%TYPE;
410 l_creation_date pqh_templates.creation_date%TYPE;
411 l_last_update_date pqh_templates.last_update_date%TYPE;
412 l_last_update_login pqh_templates.last_update_login%TYPE;
413 --
414 cursor c_templates(p_template_short_name varchar2) is
415 select template_id
416 from pqh_templates_vl
417 where short_name = p_template_short_name;
418 --
419 cursor c_ref_templates(p_parent_template_id varchar2,
420 p_base_template_id varchar2) is
421 select 'x'
422 from pqh_ref_templates
423 where parent_template_id = p_parent_template_id
424 and base_template_id = p_base_template_id;
425 --
426 --
427 l_data_migrator_mode varchar2(1);
428 --
429 BEGIN
430 --
431 --
432 -- populate WHO columns
433 --
434 /**
435 if p_owner = 'SEED' then
436 l_created_by := 1;
437 l_last_updated_by := -1;
438 else
439 l_created_by := 0;
440 l_last_updated_by := 0;
441 end if;
442 **/
443 l_data_migrator_mode := hr_general.g_data_migrator_mode ;
444 hr_general.g_data_migrator_mode := 'Y';
445 l_last_updated_by := fnd_load_util.owner_id(p_owner);
446 l_created_by := fnd_load_util.owner_id(p_owner);
447 --
448 l_creation_date := nvl(to_date(p_last_update_date,'YYYY/MM/DD'),trunc(sysdate));
449 l_last_update_date := nvl(to_date(p_last_update_date,'YYYY/MM/DD'),trunc(sysdate));
450 /**
451 l_creation_date := sysdate;
452 l_last_update_date := sysdate;
453 **/
454 l_last_update_login := 0;
455 --
456 open c_templates(p_parent_template_short_name);
457 fetch c_templates into l_parent_template_id;
458 if c_templates%found then
459 close c_templates;
460 open c_templates(p_base_template_short_name);
461 fetch c_templates into l_base_template_id;
462 if c_templates%found then
463 close c_templates;
464 --
465 open c_ref_templates(l_parent_template_id, l_base_template_id);
466 fetch c_ref_templates into l_dummy;
467 if c_ref_templates%found then
468 close c_ref_templates;
469 update PQH_REF_TEMPLATES
470 set reference_type_cd = p_reference_type_cd,
471 last_update_date = l_last_update_date,
472 last_updated_by = l_last_updated_by,
473 last_update_login = l_last_update_login
474 where parent_template_id = l_parent_template_id
475 and base_template_id = l_base_template_id;
476 else
477 close c_ref_templates;
478 --
479 insert into pqh_ref_templates
480 (REF_TEMPLATE_ID, BASE_TEMPLATE_ID, PARENT_TEMPLATE_ID,
481 OBJECT_VERSION_NUMBER, LAST_UPDATE_DATE, LAST_UPDATED_BY,
482 LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE,
483 REFERENCE_TYPE_CD )
484 values
485 (PQH_REF_TEMPLATES_S.NEXTVAL, L_BASE_TEMPLATE_ID, L_PARENT_TEMPLATE_ID,
486 L_OBJECT_VERSION_NUMBER, L_LAST_UPDATE_DATE, L_LAST_UPDATED_BY,
487 L_LAST_UPDATE_LOGIN, L_CREATED_BY, L_CREATION_DATE,
488 P_REFERENCE_TYPE_CD );
489 --
490 if p_reference_type_cd = 'COPY' then
491 --
492 apply_copy_template(l_parent_template_id, l_base_template_id,p_owner);
493 end if;
494 end if;
495 else
496 close c_templates;
497 fnd_message.set_name(8302,'PQH_INVALID_TEMPLATE');
498 fnd_message.set_token('TEMPLATE_SHORT_NAME',p_base_template_short_name);
499 fnd_message.raise_error;
500 end if;
501 else
502 close c_templates;
503 fnd_message.set_name(8302,'PQH_INVALID_TEMPLATE');
504 fnd_message.set_token('TEMPLATE_SHORT_NAME',p_parent_template_short_name);
505 fnd_message.raise_error;
506 end if;
507 hr_general.g_data_migrator_mode := l_data_migrator_mode;
508 END;
509 --
510 end pqh_rft_shd;