[Home] [Help]
PACKAGE BODY: APPS.PQH_RFT_SHD
Source
4 -- ----------------------------------------------------------------------------
1 Package Body pqh_rft_shd as
2 /* $Header: pqrftrhi.pkb 120.3 2011/04/28 09:39:53 sidsaxen ship $ */
3 --
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
96 ) Then
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
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
248 cursor c_select_flag(p_attribute_id number, p_template_id number) is
245 and attribute_id = p_attribute_id
246 FOR UPDATE ;
247 --
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 --
330 -- Added the following code as a part of Zero Downtime Patching Project.
331 -- Code Starts Here.
332 --
333 PER_RIC_PKG.chk_integrity(
334 p_entity_name=>'PQH_TEMPLATE_ATTRIBUTES',
335 p_ref_entity_info=>
336 PER_RIC_PKG.ref_entity_tbl(
337 PER_RIC_PKG.ref_info_rec('PQH_ATTRIBUTES',PER_RIC_PKG.column_info_tbl(
338 PER_RIC_PKG.col_info_rec('ATTRIBUTE_ID',NULL,p_copied_attributes(cnt).attribute_id, NULL))),
339 PER_RIC_PKG.ref_info_rec('PQH_TEMPLATES',PER_RIC_PKG.column_info_tbl(
340 PER_RIC_PKG.col_info_rec('TEMPLATE_ID',NULL,p_template_id, NULL)))),
341 p_ref_type=>'INS');
342 --
343 -- Code Ends Here
344 --
345
346 insert into pqh_template_attributes
347 (
348 required_flag
349 ,view_flag
350 ,edit_flag
351 ,template_attribute_id
352 ,attribute_id
353 ,template_id
354 ,object_version_number
355 ,LAST_UPDATE_DATE, LAST_UPDATED_BY,
356 LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE
357 )
358 values
359 (
360 p_copied_attributes(cnt).reqd_flag
361 ,l_view_flag
362 ,l_edit_flag
363 ,pqh_template_attributes_s.nextval
364 ,p_copied_attributes(cnt).attribute_id
365 ,p_template_id
366 ,1
367 ,L_LAST_UPDATE_DATE, L_LAST_UPDATED_BY,
368 L_LAST_UPDATE_LOGIN, L_CREATED_BY, L_CREATION_DATE);
369
370 --
371 End if;
372 --
373 Close csr_tem_attr;
374 end if;
375 close c_select_flag;
376 --
377
378 End loop;
379 --
380 hr_utility.set_location(' Leaving:'||l_proc, 70);
381 --
382 end create_update_copied_attribute;
383 --
384 --
385 procedure apply_copy_template(
386 p_parent_template_id number,
387 p_base_template_id number,
388 p_owner in varchar2 default null) is
389 --
390 copied_attr_tab pqh_prvcalc.t_attid_priv;
391 begin
392 pqh_prvcalc.task_result_update(p_parent_template_id,
393 'T',
397 copied_attr_tab);
394 copied_attr_tab);
395 pqh_prvcalc.task_result_update(p_base_template_id,
396 'T',
398 --
399 -- Save up the copied attributes in the database
400 --
401 If copied_attr_tab.count > 0 then
402
403 create_update_copied_attribute
404 (p_copied_attributes => copied_attr_tab
405 ,p_template_id => p_parent_template_id
406 ,p_owner => p_owner);
407 --
408 --
409 --
410 End if;
411 end;
412 --
413 procedure LOAD_ROW
414 (p_parent_template_short_name in varchar2
415 ,p_base_template_short_name in varchar2
416 ,p_reference_type_cd in varchar2
417 ,p_owner in varchar2
418 ,p_last_update_date in varchar2
419 ) is
420 l_dummy varchar2(10);
421 l_object_version_number number(15) := 1;
422 l_parent_template_id pqh_ref_templates.parent_template_id%type;
423 l_base_template_id pqh_ref_templates.base_template_id%type;
424 --
425 l_created_by pqh_templates.created_by%TYPE;
426 l_last_updated_by pqh_templates.last_updated_by%TYPE;
427 l_creation_date pqh_templates.creation_date%TYPE;
428 l_last_update_date pqh_templates.last_update_date%TYPE;
429 l_last_update_login pqh_templates.last_update_login%TYPE;
430 --
431 cursor c_templates(p_template_short_name varchar2) is
432 select template_id
433 from pqh_templates_vl
434 where short_name = p_template_short_name;
435 --
436 cursor c_ref_templates(p_parent_template_id varchar2,
437 p_base_template_id varchar2) is
438 select 'x'
439 from pqh_ref_templates
440 where parent_template_id = p_parent_template_id
441 and base_template_id = p_base_template_id;
442 --
443 --
444 l_data_migrator_mode varchar2(1);
445 --
446 BEGIN
447 --
448 --
449 -- populate WHO columns
450 --
451 /**
452 if p_owner = 'SEED' then
453 l_created_by := 1;
454 l_last_updated_by := -1;
455 else
456 l_created_by := 0;
457 l_last_updated_by := 0;
458 end if;
459 **/
460 l_data_migrator_mode := hr_general.g_data_migrator_mode ;
461 hr_general.g_data_migrator_mode := 'Y';
462 l_last_updated_by := fnd_load_util.owner_id(p_owner);
463 l_created_by := fnd_load_util.owner_id(p_owner);
464 --
465 l_creation_date := nvl(to_date(p_last_update_date,'YYYY/MM/DD'),trunc(sysdate));
466 l_last_update_date := nvl(to_date(p_last_update_date,'YYYY/MM/DD'),trunc(sysdate));
467 /**
468 l_creation_date := sysdate;
469 l_last_update_date := sysdate;
470 **/
471 l_last_update_login := 0;
472 --
473 open c_templates(p_parent_template_short_name);
474 fetch c_templates into l_parent_template_id;
475 if c_templates%found then
476 close c_templates;
477 open c_templates(p_base_template_short_name);
478 fetch c_templates into l_base_template_id;
479 if c_templates%found then
480 close c_templates;
481 --
482 open c_ref_templates(l_parent_template_id, l_base_template_id);
483 fetch c_ref_templates into l_dummy;
484 if c_ref_templates%found then
485 close c_ref_templates;
486 update PQH_REF_TEMPLATES
487 set reference_type_cd = p_reference_type_cd,
488 last_update_date = l_last_update_date,
489 last_updated_by = l_last_updated_by,
490 last_update_login = l_last_update_login
491 where parent_template_id = l_parent_template_id
492 and base_template_id = l_base_template_id;
493 else
494 close c_ref_templates;
495 --
496 PER_RIC_PKG.chk_integrity(
497 p_entity_name=>'PQH_REF_TEMPLATES',
498 p_ref_entity_info=>
499 PER_RIC_PKG.ref_entity_tbl(
500 PER_RIC_PKG.ref_info_rec('PQH_TEMPLATES',PER_RIC_PKG.column_info_tbl(
501 PER_RIC_PKG.col_info_rec('TEMPLATE_ID',NULL,L_PARENT_TEMPLATE_ID, NULL))),
502 PER_RIC_PKG.ref_info_rec('PQH_TEMPLATES',PER_RIC_PKG.column_info_tbl(
503 PER_RIC_PKG.col_info_rec('TEMPLATE_ID',NULL,L_BASE_TEMPLATE_ID, NULL)))),
504 p_ref_type=>'INS');
505
506 insert into pqh_ref_templates
507 (REF_TEMPLATE_ID, BASE_TEMPLATE_ID, PARENT_TEMPLATE_ID,
508 OBJECT_VERSION_NUMBER, LAST_UPDATE_DATE, LAST_UPDATED_BY,
509 LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE,
510 REFERENCE_TYPE_CD )
511 values
512 (PQH_REF_TEMPLATES_S.NEXTVAL, L_BASE_TEMPLATE_ID, L_PARENT_TEMPLATE_ID,
513 L_OBJECT_VERSION_NUMBER, L_LAST_UPDATE_DATE, L_LAST_UPDATED_BY,
514 L_LAST_UPDATE_LOGIN, L_CREATED_BY, L_CREATION_DATE,
515 P_REFERENCE_TYPE_CD );
516 --
517 if p_reference_type_cd = 'COPY' then
518 --
519 apply_copy_template(l_parent_template_id, l_base_template_id,p_owner);
520 end if;
521 end if;
522 else
523 close c_templates;
524 fnd_message.set_name(8302,'PQH_INVALID_TEMPLATE');
525 fnd_message.set_token('TEMPLATE_SHORT_NAME',p_base_template_short_name);
526 fnd_message.raise_error;
527 end if;
528 else
529 close c_templates;
530 fnd_message.set_name(8302,'PQH_INVALID_TEMPLATE');
534 hr_general.g_data_migrator_mode := l_data_migrator_mode;
531 fnd_message.set_token('TEMPLATE_SHORT_NAME',p_parent_template_short_name);
532 fnd_message.raise_error;
533 end if;
535 END;
536 --
537 end pqh_rft_shd;