[Home] [Help]
PACKAGE BODY: APPS.PQH_TCA_SHD
Source
1 Package Body pqh_tca_shd as
2 /* $Header: pqtcarhi.pkb 120.2 2005/10/12 20:19:48 srajakum noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_tca_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_TXN_CATEGORY_ATTRIBUTES_PK') 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_TXN_CATEGORY_ATTRIBUTE_FK1') 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 ElsIf (p_constraint_name = 'PQH_TXN_CATEGORY_ATTRIBUTE_FK2') Then
33 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
34 hr_utility.set_message_token('PROCEDURE', l_proc);
35 hr_utility.set_message_token('STEP','15');
36 hr_utility.raise_error;
37 ElsIf (p_constraint_name = 'PQH_TXN_CAT_ATTRIBUTES_FK4') Then
38 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
39 hr_utility.set_message_token('PROCEDURE', l_proc);
40 hr_utility.set_message_token('STEP','20');
41 hr_utility.raise_error;
42 ElsIf (p_constraint_name = 'PQH_TXN_CAT_ATTRIBUTES_FK5') Then
43 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
44 hr_utility.set_message_token('PROCEDURE', l_proc);
45 hr_utility.set_message_token('STEP','25');
46 hr_utility.raise_error;
47 Else
48 hr_utility.set_message(801, 'HR_7877_API_INVALID_CONSTRAINT');
49 hr_utility.set_message_token('PROCEDURE', l_proc);
50 hr_utility.set_message_token('CONSTRAINT_NAME', p_constraint_name);
51 hr_utility.raise_error;
52 End If;
53 --
54 hr_utility.set_location(' Leaving:'||l_proc, 10);
55 End constraint_error;
56 --
57 -- ----------------------------------------------------------------------------
58 -- |-----------------------------< api_updating >-----------------------------|
59 -- ----------------------------------------------------------------------------
60 Function api_updating
61 (
62 p_txn_category_attribute_id in number,
63 p_object_version_number in number
64 ) Return Boolean Is
65 --
66 --
67 -- Cursor selects the 'current' row from the HR Schema
68 --
69 Cursor C_Sel1 is
70 select
71 txn_category_attribute_id,
72 attribute_id,
73 transaction_category_id,
74 value_set_id,
75 object_version_number,
76 transaction_table_route_id,
77 form_column_name,
78 identifier_flag,
79 list_identifying_flag,
80 member_identifying_flag,
81 refresh_flag,
82 select_flag,
83 value_style_cd
84 from pqh_txn_category_attributes
85 where txn_category_attribute_id = p_txn_category_attribute_id;
86 --
87 l_proc varchar2(72) := g_package||'api_updating';
88 l_fct_ret boolean;
89 --
90 Begin
91 hr_utility.set_location('Entering:'||l_proc, 5);
92 --
93 If (
94 p_txn_category_attribute_id is null and
95 p_object_version_number is null
96 ) Then
97 --
98 -- One of the primary key arguments is null therefore we must
99 -- set the returning function value to false
100 --
101 l_fct_ret := false;
102 Else
103 If (
104 p_txn_category_attribute_id = g_old_rec.txn_category_attribute_id and
105 p_object_version_number = g_old_rec.object_version_number
106 ) Then
107 hr_utility.set_location(l_proc, 10);
108 --
109 -- The g_old_rec is current therefore we must
110 -- set the returning function to true
111 --
112 l_fct_ret := true;
113 Else
114 --
115 -- Select the current row into g_old_rec
116 --
117 Open C_Sel1;
118 Fetch C_Sel1 Into g_old_rec;
119 If C_Sel1%notfound Then
120 Close C_Sel1;
121 --
122 -- The primary key is invalid therefore we must error
123 --
124 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
125 hr_utility.raise_error;
126 End If;
127 Close C_Sel1;
128 If (p_object_version_number <> g_old_rec.object_version_number) Then
129 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
130 hr_utility.raise_error;
131 End If;
132 hr_utility.set_location(l_proc, 15);
133 l_fct_ret := true;
134 End If;
135 End If;
136 hr_utility.set_location(' Leaving:'||l_proc, 20);
137 Return (l_fct_ret);
138 --
139 End api_updating;
140 --
141 -- ----------------------------------------------------------------------------
142 -- |---------------------------------< lck >----------------------------------|
143 -- ----------------------------------------------------------------------------
144 Procedure lck
145 (
146 p_txn_category_attribute_id in number,
147 p_object_version_number in number
148 ) is
149 --
150 -- Cursor selects the 'current' row from the HR Schema
151 --
152 Cursor C_Sel1 is
153 select txn_category_attribute_id,
154 attribute_id,
155 transaction_category_id,
156 value_set_id,
157 object_version_number,
158 transaction_table_route_id,
159 form_column_name,
160 identifier_flag,
161 list_identifying_flag,
162 member_identifying_flag,
163 refresh_flag,
164 select_flag,
165 value_style_cd
166 from pqh_txn_category_attributes
167 where txn_category_attribute_id = p_txn_category_attribute_id
168 for update nowait;
169 --
170 l_proc varchar2(72) := g_package||'lck';
171 --
172 Begin
173 hr_utility.set_location('Entering:'||l_proc, 5);
174 --
175 -- Add any mandatory argument checking here:
176 -- Example:
177 -- hr_api.mandatory_arg_error
178 -- (p_api_name => l_proc,
179 -- p_argument => 'object_version_number',
180 -- p_argument_value => p_object_version_number);
181 --
182 Open C_Sel1;
183 Fetch C_Sel1 Into g_old_rec;
184 If C_Sel1%notfound then
185 Close C_Sel1;
186 --
187 -- The primary key is invalid therefore we must error
188 --
189 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
190 hr_utility.raise_error;
191 End If;
192 Close C_Sel1;
193 If (p_object_version_number <> g_old_rec.object_version_number) Then
194 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
195 hr_utility.raise_error;
196 End If;
197 --
198 hr_utility.set_location(' Leaving:'||l_proc, 10);
199 --
200 -- We need to trap the ORA LOCK exception
201 --
202 Exception
203 When HR_Api.Object_Locked then
204 --
205 -- The object is locked therefore we need to supply a meaningful
206 -- error message.
207 --
208 hr_utility.set_message(801, 'HR_7165_OBJECT_LOCKED');
209 hr_utility.set_message_token('TABLE_NAME', 'pqh_txn_category_attributes');
210 hr_utility.raise_error;
211 End lck;
212 --
213 -- ----------------------------------------------------------------------------
214 -- |-----------------------------< convert_args >-----------------------------|
215 -- ----------------------------------------------------------------------------
216 Function convert_args
217 (
218 p_txn_category_attribute_id in number,
219 p_attribute_id in number,
220 p_transaction_category_id in number,
221 p_value_set_id in number,
222 p_object_version_number in number,
223 p_transaction_table_route_id in number,
224 p_form_column_name in varchar2,
225 p_identifier_flag in varchar2,
226 p_list_identifying_flag in varchar2,
227 p_member_identifying_flag in varchar2,
228 p_refresh_flag in varchar2,
229 p_select_flag in varchar2,
230 p_value_style_cd in varchar2
231 )
232 Return g_rec_type is
233 --
234 l_rec g_rec_type;
235 l_proc varchar2(72) := g_package||'convert_args';
236 --
237 Begin
238 --
239 hr_utility.set_location('Entering:'||l_proc, 5);
240 --
241 -- Convert arguments into local l_rec structure.
242 --
243 l_rec.txn_category_attribute_id := p_txn_category_attribute_id;
244 l_rec.attribute_id := p_attribute_id;
245 l_rec.transaction_category_id := p_transaction_category_id;
246 l_rec.value_set_id := p_value_set_id;
247 l_rec.object_version_number := p_object_version_number;
248 l_rec.transaction_table_route_id := p_transaction_table_route_id;
249 l_rec.form_column_name := p_form_column_name;
250 l_rec.identifier_flag := p_identifier_flag;
251 l_rec.list_identifying_flag := p_list_identifying_flag;
252 l_rec.member_identifying_flag := p_member_identifying_flag;
253 l_rec.refresh_flag := p_refresh_flag;
254 l_rec.select_flag := p_select_flag ;
255 l_rec.value_style_cd := p_value_style_cd;
256 --
257 -- Return the plsql record structure.
258 --
259 hr_utility.set_location(' Leaving:'||l_proc, 10);
260 Return(l_rec);
261 --
262 End convert_args;
263 --
264 -- ----------------------------------------------------------------------------
265 -- |----------------------------< load_row >---------------------------------|
266 -- ----------------------------------------------------------------------------
267 --
268 Procedure load_row
269 ( p_att_col_name in varchar2
270 ,p_att_master_table_alias_name in varchar2
271 ,p_tran_cat_short_name in varchar2
272 ,p_value_set_name in varchar2
273 ,p_transaction_table_alias_name in varchar2
274 ,p_form_column_name in varchar2
275 ,p_identifier_flag in varchar2
276 ,p_list_identifying_flag in varchar2
277 ,p_member_identifying_flag in varchar2
278 ,p_refresh_flag in varchar2
279 ,p_select_flag in varchar2
280 ,p_value_style_cd in varchar2
281 ,p_legislation_code in varchar2
282 ,p_copy_to_bg_attr in varchar2
283 ,p_owner in varchar2
284 ,p_last_update_date in varchar2
285 ) is
286 --
287 --
288 l_effective_date date := sysdate ;
289 l_object_version_number number := 1;
290 l_language varchar2(30) ;
291 l_delete_attr_ranges_flag varchar2(30) := 'N';
292 --
293 l_txn_category_attribute_id pqh_txn_category_attributes.txn_category_attribute_id%TYPE := 0 ;
294 l_attribute_id pqh_attributes.attribute_id%TYPE := 0 ;
295 l_transaction_category_id pqh_txn_category_attributes.transaction_category_id%TYPE := 0 ;
296 l_transaction_table_route_id pqh_txn_category_attributes.transaction_table_route_id%TYPE;
297 l_att_master_table_route_id pqh_txn_category_attributes.transaction_table_route_id%TYPE;
298 l_flex_value_set_id fnd_flex_value_sets.flex_value_set_id%TYPE;
299 --
300 l_created_by pqh_attributes.created_by%TYPE;
301 l_last_updated_by pqh_attributes.last_updated_by%TYPE;
302 l_creation_date pqh_attributes.creation_date%TYPE;
303 l_last_update_date pqh_attributes.last_update_date%TYPE;
304 l_last_update_login pqh_attributes.last_update_login%TYPE;
305 --
306 cursor c1 is select userenv('LANG') from dual ;
307 --
308 --
309 -- developer key is short_name
310 --
311 cursor csr_attribute_id(p_column_name IN VARCHAR2,
312 p_table_id IN NUMBER,
313 p_legislation_code varchar2) is
314 select attribute_id
315 from pqh_attributes
316 where key_column_name = p_column_name
317 and nvl(master_table_route_id,-999) = nvl(p_table_id, -999)
318 and nvl(legislation_code,'$$$') = nvl(p_legislation_code, '$$$');
319 --
320 cursor csr_table_id (p_table_alias IN VARCHAR2) is
321 select table_route_id
322 from pqh_table_route
323 where table_alias = p_table_alias;
324 --
325 cursor csr_txn_cat_id (p_tran_cat_short_name IN VARCHAR2) is
326 select transaction_category_id
327 from pqh_transaction_categories
328 where short_name = p_tran_cat_short_name
329 and business_group_id is null;
330 --
331 cursor csr_txn_cat_att_id ( p_attribute_id IN NUMBER, p_txn_category_id IN NUMBER ) is
332 select txn_category_attribute_id
333 from pqh_txn_category_attributes
334 where attribute_id = p_attribute_id
335 and transaction_category_id = p_txn_category_id;
336 --
337 cursor csr_value_set_id (p_value_set_name IN VARCHAR2) is
338 select flex_value_set_id
339 from fnd_flex_value_sets
340 where flex_value_set_name = p_value_set_name;
341 --
342 --
343 Cursor csr_local_txn_cat_id is
344 select transaction_category_id
345 from pqh_transaction_categories
346 where short_name = p_tran_cat_short_name
347 and business_group_id is not null;
348 --
349 --
350 l_data_migrator_mode varchar2(1);
351 --
352 --
353 Begin
354 --
355 l_data_migrator_mode := hr_general.g_data_migrator_mode ;
356 hr_general.g_data_migrator_mode := 'Y';
357 -- key to ids
358 --
359 open c1;
360 fetch c1 into l_language ;
361 close c1;
362 --
363 open csr_table_id(p_table_alias => p_transaction_table_alias_name );
364 fetch csr_table_id into l_transaction_table_route_id;
365 close csr_table_id;
366 --
367 open csr_table_id(p_table_alias => p_att_master_table_alias_name );
368 fetch csr_table_id into l_att_master_table_route_id;
369 close csr_table_id;
370 --
371 open csr_attribute_id(p_column_name => p_att_col_name,
372 p_table_id => l_att_master_table_route_id,
373 p_legislation_code => p_legislation_code);
374 fetch csr_attribute_id into l_attribute_id;
375 if csr_attribute_id%notfound then
376 fnd_message.set_name(8302,'PQH_INVALID_ATTRIBUTE');
377 fnd_message.set_token('ATTRIBUTE_COLUMN_NAME',p_att_col_name);
378 fnd_message.set_token('TABLE_ROUTE',p_att_master_table_alias_name);
379 fnd_message.set_token('LEGISLATION_CODE',p_legislation_code);
380 fnd_message.raise_error;
381 end if;
382 close csr_attribute_id;
383 --
384 open csr_txn_cat_id(p_tran_cat_short_name => p_tran_cat_short_name );
385 fetch csr_txn_cat_id into l_transaction_category_id;
386 close csr_txn_cat_id;
387 --
388 open csr_txn_cat_att_id(p_attribute_id => l_attribute_id,
389 p_txn_category_id => l_transaction_category_id );
390 fetch csr_txn_cat_att_id into l_txn_category_attribute_id;
391 close csr_txn_cat_att_id;
392 --
393 open csr_value_set_id (p_value_set_name => p_value_set_name );
394 fetch csr_value_set_id into l_flex_value_set_id;
395 close csr_value_set_id;
396 --
397 -- populate WHO columns
398 --
399 /**
400 if p_owner = 'SEED' then
404 l_created_by := 0;
401 l_created_by := 1;
402 l_last_updated_by := -1;
403 else
405 l_last_updated_by := 0;
406 end if;
407 **/
408 l_last_updated_by := fnd_load_util.owner_id(p_owner);
409 l_created_by := fnd_load_util.owner_id(p_owner);
410 l_creation_date := nvl(to_date(p_last_update_date,'YYYY/MM/DD'),trunc(sysdate));
411 l_last_update_date := nvl(to_date(p_last_update_date,'YYYY/MM/DD'),trunc(sysdate));
412 /**
413 l_creation_date := sysdate;
414 l_last_update_date := sysdate;
415 **/
416 l_last_update_login := 0;
417 --
418 if l_txn_category_attribute_id <> 0 then
419 -- row exits so update
420 UPDATE pqh_txn_category_attributes
421 SET value_set_id = l_flex_value_set_id,
422 transaction_table_route_id = l_transaction_table_route_id,
423 form_column_name = p_form_column_name,
424 identifier_flag = p_identifier_flag,
425 -- list_identifying_flag = p_list_identifying_flag,
426 -- member_identifying_flag = p_member_identifying_flag,
427 refresh_flag = p_refresh_flag,
428 value_style_cd = p_value_style_cd,
429 select_flag = p_select_flag,
430 last_updated_by = l_last_updated_by,
431 last_update_date = l_last_update_date,
432 last_update_login = l_last_update_login
433 WHERE txn_category_attribute_id = l_txn_category_attribute_id;
434 --
435 -- The foll lne was commented to allow attaching a value set
436 -- later to the txn category attribute, after it has been updated by user.
437 -- AND NVL(last_updated_by,-1) in (1,-1);
438 --
439 -- Check if the local txn categories have to be updated.
440 --
441 If p_copy_to_bg_attr = 'Y' then
442 --
443 UPDATE pqh_txn_category_attributes
444 SET value_set_id = l_flex_value_set_id,
445 transaction_table_route_id = l_transaction_table_route_id,
446 form_column_name = p_form_column_name,
447 identifier_flag = p_identifier_flag,
448 refresh_flag = p_refresh_flag,
449 value_style_cd = p_value_style_cd,
450 select_flag = p_select_flag,
451 last_updated_by = l_last_updated_by,
452 last_update_date = l_last_update_date,
453 last_update_login = l_last_update_login
454 WHERE attribute_id = l_attribute_id
455 -- AND NVL(last_updated_by,-1) in (1,-1);
456 AND transaction_category_id in (
457 select transaction_category_id
458 from pqh_transaction_categories
459 where short_name = p_tran_cat_short_name
460 and business_group_id is not null);
461 End if; -- Propogate changes to bg specific txn category attributes
462
463 else
464
465 -- insert into pqh_txn_category_attributes table
466
467 select pqh_txn_category_attributes_s.nextval into l_txn_category_attribute_id from dual;
468
469 INSERT INTO pqh_txn_category_attributes
470 (txn_category_attribute_id,
471 attribute_id,
472 transaction_category_id,
473 value_set_id,
474 created_by,
475 creation_date,
476 last_updated_by,
477 last_update_date ,
478 last_update_login,
479 object_version_number,
480 transaction_table_route_id,
481 form_column_name,
482 identifier_flag,
483 list_identifying_flag,
484 member_identifying_flag,
485 refresh_flag,
486 select_flag,
487 value_style_cd)
488 VALUES
489 (l_txn_category_attribute_id,
490 l_attribute_id,
491 l_transaction_category_id,
492 l_flex_value_set_id,
493 l_created_by,
494 l_creation_date,
495 l_last_updated_by,
496 l_last_update_date ,
497 l_last_update_login,
498 l_object_version_number,
499 l_transaction_table_route_id,
500 p_form_column_name,
501 p_identifier_flag,
502 p_list_identifying_flag,
503 p_member_identifying_flag,
504 p_refresh_flag,
505 p_select_flag,
506 p_value_style_cd);
507 --
508 -- Insert the new attribute into local tct. Added as a part of pqseedtca.sql cleanup
509 --
510 --
511 For lcl_tca_rec in csr_local_txn_cat_id loop
512 --
513 l_transaction_category_id := lcl_tca_rec.transaction_category_id;
514
515 select pqh_txn_category_attributes_s.nextval into l_txn_category_attribute_id from dual;
516
517 INSERT INTO pqh_txn_category_attributes
518 (txn_category_attribute_id,
519 attribute_id,
520 transaction_category_id,
521 value_set_id,
522 created_by,
523 creation_date,
524 last_updated_by,
525 last_update_date ,
526 last_update_login,
527 object_version_number,
528 transaction_table_route_id,
529 form_column_name,
530 identifier_flag,
531 list_identifying_flag,
532 member_identifying_flag,
533 refresh_flag,
534 select_flag,
535 value_style_cd)
536 VALUES
537 (l_txn_category_attribute_id,
538 l_attribute_id,
539 l_transaction_category_id,
540 l_flex_value_set_id,
541 l_created_by,
542 l_creation_date,
543 l_last_updated_by,
544 l_last_update_date ,
545 l_last_update_login,
546 l_object_version_number,
547 l_transaction_table_route_id,
548 p_form_column_name,
549 p_identifier_flag,
550 p_list_identifying_flag,
551 p_member_identifying_flag,
552 p_refresh_flag,
553 p_select_flag,
554 p_value_style_cd);
555 --
556 --
557 End loop;
558 --
559 end if;
560 --
561 hr_general.g_data_migrator_mode := l_data_migrator_mode;
562 --
563 End load_row;
564 --
565 --
566 End pqh_tca_shd;
567 --