[Home] [Help]
PACKAGE BODY: APPS.PQH_SAT_SHD
Source
1 Package Body pqh_sat_shd as
2 /* $Header: pqsatrhi.pkb 120.2 2005/10/12 20:19:29 srajakum noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_sat_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_SPECIAL_ATTRIBUTES_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_SPECIAL_ATTRIBUTES_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_special_attribute_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 special_attribute_id,
57 txn_category_attribute_id,
58 attribute_type_cd,
59 key_attribute_type,
60 enable_flag,
61 flex_code,
62 object_version_number,
63 ddf_column_name,
64 ddf_value_column_name,
65 context
66 from pqh_special_attributes
67 where special_attribute_id = p_special_attribute_id;
68 --
69 l_proc varchar2(72) := g_package||'api_updating';
70 l_fct_ret boolean;
71 --
72 Begin
73 hr_utility.set_location('Entering:'||l_proc, 5);
74 --
75 If (
76 p_special_attribute_id is null and
77 p_object_version_number is null
78 ) Then
79 --
80 -- One of the primary key arguments is null therefore we must
81 -- set the returning function value to false
82 --
83 l_fct_ret := false;
84 Else
85 If (
86 p_special_attribute_id = g_old_rec.special_attribute_id and
87 p_object_version_number = g_old_rec.object_version_number
88 ) Then
89 hr_utility.set_location(l_proc, 10);
90 --
91 -- The g_old_rec is current therefore we must
92 -- set the returning function to true
93 --
94 l_fct_ret := true;
95 Else
96 --
97 -- Select the current row into g_old_rec
98 --
99 Open C_Sel1;
100 Fetch C_Sel1 Into g_old_rec;
101 If C_Sel1%notfound Then
102 Close C_Sel1;
103 --
104 -- The primary key is invalid therefore we must error
105 --
106 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
107 hr_utility.raise_error;
108 End If;
109 Close C_Sel1;
110 If (p_object_version_number <> g_old_rec.object_version_number) Then
111 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
112 hr_utility.raise_error;
113 End If;
114 hr_utility.set_location(l_proc, 15);
115 l_fct_ret := true;
116 End If;
117 End If;
118 hr_utility.set_location(' Leaving:'||l_proc, 20);
119 Return (l_fct_ret);
120 --
121 End api_updating;
122 --
123 -- ----------------------------------------------------------------------------
124 -- |---------------------------------< lck >----------------------------------|
125 -- ----------------------------------------------------------------------------
126 Procedure lck
127 (
128 p_special_attribute_id in number,
129 p_object_version_number in number
130 ) is
131 --
132 -- Cursor selects the 'current' row from the HR Schema
133 --
134 Cursor C_Sel1 is
135 select special_attribute_id,
136 txn_category_attribute_id,
137 attribute_type_cd,
138 key_attribute_type,
139 enable_flag,
140 flex_code,
141 object_version_number,
142 ddf_column_name,
143 ddf_value_column_name,
144 context
145 from pqh_special_attributes
146 where special_attribute_id = p_special_attribute_id
147 for update nowait;
148 --
149 l_proc varchar2(72) := g_package||'lck';
150 --
151 Begin
152 hr_utility.set_location('Entering:'||l_proc, 5);
153 --
154 -- Add any mandatory argument checking here:
155 -- Example:
156 -- hr_api.mandatory_arg_error
157 -- (p_api_name => l_proc,
158 -- p_argument => 'object_version_number',
159 -- p_argument_value => p_object_version_number);
160 --
161 Open C_Sel1;
162 Fetch C_Sel1 Into g_old_rec;
163 If C_Sel1%notfound then
164 Close C_Sel1;
165 --
166 -- The primary key is invalid therefore we must error
167 --
168 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
169 hr_utility.raise_error;
170 End If;
171 Close C_Sel1;
172 If (p_object_version_number <> g_old_rec.object_version_number) Then
173 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
174 hr_utility.raise_error;
175 End If;
176 --
177 hr_utility.set_location(' Leaving:'||l_proc, 10);
178 --
179 -- We need to trap the ORA LOCK exception
180 --
181 Exception
182 When HR_Api.Object_Locked then
183 --
184 -- The object is locked therefore we need to supply a meaningful
185 -- error message.
186 --
187 hr_utility.set_message(801, 'HR_7165_OBJECT_LOCKED');
188 hr_utility.set_message_token('TABLE_NAME', 'pqh_special_attributes');
189 hr_utility.raise_error;
190 End lck;
191 --
192 -- ----------------------------------------------------------------------------
193 -- |-----------------------------< convert_args >-----------------------------|
194 -- ----------------------------------------------------------------------------
195 Function convert_args
196 (
197 p_special_attribute_id in number,
198 p_txn_category_attribute_id in number,
199 p_attribute_type_cd in varchar2,
200 p_key_attribute_type in varchar2,
201 p_enable_flag in varchar2,
202 p_flex_code in varchar2,
203 p_object_version_number in number,
204 p_ddf_column_name in varchar2,
205 p_ddf_value_column_name in varchar2,
206 p_context in varchar2
207 )
208 Return g_rec_type is
209 --
210 l_rec g_rec_type;
211 l_proc varchar2(72) := g_package||'convert_args';
212 --
213 Begin
214 --
215 hr_utility.set_location('Entering:'||l_proc, 5);
216 --
217 -- Convert arguments into local l_rec structure.
218 --
219 l_rec.special_attribute_id := p_special_attribute_id;
220 l_rec.txn_category_attribute_id := p_txn_category_attribute_id;
221 l_rec.attribute_type_cd := p_attribute_type_cd;
222 l_rec.key_attribute_type := p_key_attribute_type;
223 l_rec.enable_flag := p_enable_flag;
224 l_rec.flex_code := p_flex_code;
225 l_rec.object_version_number := p_object_version_number;
226 l_rec.ddf_column_name := p_ddf_column_name;
227 l_rec.ddf_value_column_name := p_ddf_value_column_name;
228 l_rec.context := p_context;
229 --
230 -- Return the plsql record structure.
231 --
232 hr_utility.set_location(' Leaving:'||l_proc, 10);
233 Return(l_rec);
234 --
235 End convert_args;
236 --
237 -- ----------------------------------------------------------------------------
238 -- |----------------------------------< load_row >---------------------------------|
239 -- ----------------------------------------------------------------------------
240 --
241 Procedure load_row
242 ( p_txn_category_short_name in varchar2
243 ,p_attribute_table_alias in varchar2
244 ,p_attribute_column_name in varchar2
245 ,p_key_attribute_type in varchar2
246 ,p_attribute_type_cd in varchar2
247 ,p_enable_flag in varchar2
248 ,p_flex_code in varchar2
249 ,p_ddf_column_name in varchar2
250 ,p_ddf_value_column_name in varchar2
251 ,p_context in varchar2
252 ,p_owner in varchar2
253 ,p_last_update_date in varchar2
254 ) is
255 --
256 l_effective_date date := sysdate ;
257 l_object_version_number number := 1;
258 l_language varchar2(30) ;
259 --
260 l_attribute_id pqh_attributes.attribute_id%TYPE ;
261 l_table_route_id pqh_attributes.master_table_route_id%TYPE;
262 l_transaction_category_id pqh_transaction_categories.transaction_category_id%TYPE;
263 l_special_attribute_id pqh_special_attributes.special_attribute_id%TYPE := 0;
264 l_txn_category_attribute_id pqh_special_attributes.txn_category_attribute_id%TYPE;
265 --
266 l_created_by pqh_special_attributes.created_by%TYPE;
267 l_last_updated_by pqh_special_attributes.last_updated_by%TYPE;
268 l_creation_date pqh_special_attributes.creation_date%TYPE;
269 l_last_update_date pqh_special_attributes.last_update_date%TYPE;
270 l_last_update_login pqh_special_attributes.last_update_login%TYPE;
271 --
272 cursor c1 is select userenv('LANG') from dual ;
273 --
274 --
275 -- developer key is TXN_CATEGORY_ATTRIBUTE_ID + ATTRIBUTE_TYPE_CD + CONTEXT
276 --
277 cursor csr_attribute_id(p_column_name IN VARCHAR2, p_table_id IN NUMBER) is
278 select attribute_id
279 from pqh_attributes
280 where key_column_name = p_column_name
281 and legislation_code is null
282 and nvl(master_table_route_id,-999) = nvl(p_table_id, -999);
283 --
284 cursor cst_txn_cat_id(p_short_name IN VARCHAR2) is
285 select transaction_category_id
286 from pqh_transaction_categories
287 where short_name = p_short_name
288 and business_group_id is null;
289 --
290 --
291 cursor csr_table_id (p_table_alias IN VARCHAR2) is
292 select table_route_id
293 from pqh_table_route
294 where table_alias = p_table_alias;
295 --
296 cursor csr_txn_cat_att_id (p_attribute_id in number, p_transaction_category_id in number) is
297 select txn_category_attribute_id
298 from pqh_txn_category_attributes
299 where attribute_id = p_attribute_id
300 and transaction_category_id = p_transaction_category_id;
301 --
302 cursor csr_special_att_id(p_txn_category_attribute_id in number,
303 p_key_attribute_type in varchar2,
304 p_context in varchar2) is
305 select special_attribute_id
306 from pqh_special_attributes
307 where txn_category_attribute_id = p_txn_category_attribute_id
308 and key_attribute_type = p_key_attribute_type
309 and context = p_context;
310 --
311 --
312 l_data_migrator_mode varchar2(1);
313 --
314 --
315 Begin
316 --
317 -- key to ids
318 --
319 l_data_migrator_mode := hr_general.g_data_migrator_mode ;
320 hr_general.g_data_migrator_mode := 'Y';
321 open c1;
322 fetch c1 into l_language ;
323 close c1;
324 --
325 open cst_txn_cat_id(p_short_name => p_txn_category_short_name );
326 fetch cst_txn_cat_id into l_transaction_category_id;
327 close cst_txn_cat_id;
328 --
329 open csr_table_id(p_table_alias => p_attribute_table_alias );
330 fetch csr_table_id into l_table_route_id;
331 close csr_table_id;
332 --
333 --
334 open csr_attribute_id(p_column_name => p_attribute_column_name, p_table_id => l_table_route_id);
335 fetch csr_attribute_id into l_attribute_id;
336 if csr_attribute_id%notfound then
337 fnd_message.set_name(8302,'PQH_INVALID_ATTRIBUTE');
338 fnd_message.set_token('ATTRIBUTE_COLUMN_NAME',p_attribute_column_name);
339 fnd_message.raise_error;
340 end if;
341 close csr_attribute_id;
342 --
343 open csr_txn_cat_att_id(p_attribute_id => l_attribute_id, p_transaction_category_id => l_transaction_category_id);
344 fetch csr_txn_cat_att_id into l_txn_category_attribute_id;
345 close csr_txn_cat_att_id;
346 --
347 open csr_special_att_id(p_txn_category_attribute_id => l_txn_category_attribute_id,
348 p_key_attribute_type => p_key_attribute_type,
349 p_context => p_context);
350 fetch csr_special_att_id into l_special_attribute_id;
351 close csr_special_att_id;
352 --
353 -- populate WHO columns
354 --
355 /**
356 if p_owner = 'SEED' then
357 l_created_by := 1;
358 l_last_updated_by := -1;
359 else
360 l_created_by := 0;
361 l_last_updated_by := -1;
362 end if;
363 **/
364 l_last_updated_by := fnd_load_util.owner_id(p_owner);
365 l_created_by := fnd_load_util.owner_id(p_owner);
366 l_creation_date := nvl(to_date(p_last_update_date,'YYYY/MM/DD'),trunc(sysdate));
367 l_last_update_date := nvl(to_date(p_last_update_date,'YYYY/MM/DD'),trunc(sysdate));
368 /**
369 l_creation_date := sysdate;
370 l_last_update_date := sysdate;
371 **/
372 l_last_update_login := 0;
373
374 --
375 --
376 Begin
377 --
378 if l_special_attribute_id <> 0 then
379 -- row exits so update
380 update pqh_special_attributes
381 set flex_code = p_flex_code,
382 ddf_column_name = p_ddf_column_name,
383 ddf_value_column_name = p_ddf_value_column_name,
384 key_attribute_type = p_key_attribute_type,
385 attribute_type_cd = nvl(p_attribute_type_cd,p_key_attribute_type),
386 enable_flag = nvl(p_enable_flag,'Y'),
387 last_updated_by = l_last_updated_by,
388 last_update_date = l_last_update_date,
389 last_update_login = l_last_update_login
390 where special_attribute_id = l_special_attribute_id
391 and nvl(last_updated_by, -1) in (l_last_updated_by,-1,0,1);
392
393 else
394 -- insert
395 select pqh_special_attributes_s.nextval into l_special_attribute_id from dual;
396
397
398 insert into pqh_special_attributes
399 ( special_attribute_id,
400 txn_category_attribute_id,
401 key_attribute_type,
402 attribute_type_cd,
403 enable_flag,
404 flex_code,
405 object_version_number,
406 ddf_column_name,
407 ddf_value_column_name,
408 context,
409 created_by,
410 creation_date,
411 last_updated_by,
412 last_update_date ,
413 last_update_login
414
415 )
416 Values
417 ( l_special_attribute_id,
418 l_txn_category_attribute_id,
419 p_key_attribute_type,
420 nvl(p_attribute_type_cd,p_key_attribute_type),
421 nvl(p_enable_flag,'Y'),
422 p_flex_code,
423 l_object_version_number,
424 p_ddf_column_name,
425 p_ddf_value_column_name,
426 p_context,
427 l_created_by,
428 l_creation_date,
429 l_last_updated_by,
430 l_last_update_date,
431 l_last_update_login
432 );
433 end if;
434
435 End;
436 hr_general.g_data_migrator_mode := l_data_migrator_mode;
437 end load_row;
438
439
440 end pqh_sat_shd;