[Home] [Help]
PACKAGE BODY: APPS.PQH_TAT_SHD
Source
1 Package Body pqh_tat_shd as
2 /* $Header: pqtatrhi.pkb 120.2 2005/10/12 20:19:38 srajakum noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_tat_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_TEMPLATE_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_TEMPLATE_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 ElsIf (p_constraint_name = 'PQH_TEMPLATE_ATTRIBUTES_PK') 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_TEMPLATE_ATTRIBUTES_UK') Then
38 hr_utility.set_message(8302, 'PQH_DUPLICATE_TEM_ATTRIBUTE');
39 hr_utility.raise_error;
40 /**
41 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
42 hr_utility.set_message_token('PROCEDURE', l_proc);
43 hr_utility.set_message_token('STEP','20');
44 hr_utility.raise_error;
45 **/
46 Else
47 hr_utility.set_message(801, 'HR_7877_API_INVALID_CONSTRAINT');
48 hr_utility.set_message_token('PROCEDURE', l_proc);
49 hr_utility.set_message_token('CONSTRAINT_NAME', p_constraint_name);
50 hr_utility.raise_error;
51 End If;
52 --
53 hr_utility.set_location(' Leaving:'||l_proc, 10);
54 End constraint_error;
55 --
56 -- ----------------------------------------------------------------------------
57 -- |-----------------------------< api_updating >-----------------------------|
58 -- ----------------------------------------------------------------------------
59 Function api_updating
60 (
61 p_template_attribute_id in number,
62 p_object_version_number in number
63 ) Return Boolean Is
64 --
65 --
66 -- Cursor selects the 'current' row from the HR Schema
67 --
68 Cursor C_Sel1 is
69 select
70 required_flag,
71 view_flag,
72 edit_flag,
73 template_attribute_id,
74 attribute_id,
75 template_id,
76 object_version_number
77 from pqh_template_attributes
78 where template_attribute_id = p_template_attribute_id;
79 --
80 l_proc varchar2(72) := g_package||'api_updating';
81 l_fct_ret boolean;
82 --
83 Begin
84 hr_utility.set_location('Entering:'||l_proc, 5);
85 --
86 If (
87 p_template_attribute_id is null and
88 p_object_version_number is null
89 ) Then
90 --
91 -- One of the primary key arguments is null therefore we must
92 -- set the returning function value to false
93 --
94 l_fct_ret := false;
95 Else
96 If (
97 p_template_attribute_id = g_old_rec.template_attribute_id and
98 p_object_version_number = g_old_rec.object_version_number
99 ) Then
100 hr_utility.set_location(l_proc, 10);
101 --
102 -- The g_old_rec is current therefore we must
103 -- set the returning function to true
104 --
105 l_fct_ret := true;
106 Else
107 --
108 -- Select the current row into g_old_rec
109 --
110 Open C_Sel1;
111 Fetch C_Sel1 Into g_old_rec;
112 If C_Sel1%notfound Then
113 Close C_Sel1;
114 --
115 -- The primary key is invalid therefore we must error
116 --
117 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
118 hr_utility.raise_error;
119 End If;
120 Close C_Sel1;
121 If (p_object_version_number <> g_old_rec.object_version_number) Then
122 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
123 hr_utility.raise_error;
124 End If;
125 hr_utility.set_location(l_proc, 15);
126 l_fct_ret := true;
127 End If;
128 End If;
129 hr_utility.set_location(' Leaving:'||l_proc, 20);
130 Return (l_fct_ret);
131 --
132 End api_updating;
133 --
134 -- ----------------------------------------------------------------------------
135 -- |---------------------------------< lck >----------------------------------|
136 -- ----------------------------------------------------------------------------
137 Procedure lck
138 (
139 p_template_attribute_id in number,
140 p_object_version_number in number
141 ) is
142 --
143 -- Cursor selects the 'current' row from the HR Schema
144 --
145 Cursor C_Sel1 is
146 select required_flag,
147 view_flag,
148 edit_flag,
149 template_attribute_id,
150 attribute_id,
151 template_id,
152 object_version_number
153 from pqh_template_attributes
154 where template_attribute_id = p_template_attribute_id
155 for update nowait;
156 --
157 l_proc varchar2(72) := g_package||'lck';
158 --
159 Begin
160 hr_utility.set_location('Entering:'||l_proc, 5);
161 --
162 -- Add any mandatory argument checking here:
163 -- Example:
164 -- hr_api.mandatory_arg_error
165 -- (p_api_name => l_proc,
166 -- p_argument => 'object_version_number',
167 -- p_argument_value => p_object_version_number);
168 --
169 Open C_Sel1;
170 Fetch C_Sel1 Into g_old_rec;
171 If C_Sel1%notfound then
172 Close C_Sel1;
173 --
174 -- The primary key is invalid therefore we must error
175 --
176 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
177 hr_utility.raise_error;
178 End If;
179 Close C_Sel1;
180 If (p_object_version_number <> g_old_rec.object_version_number) Then
181 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
182 hr_utility.raise_error;
183 End If;
184 --
185 hr_utility.set_location(' Leaving:'||l_proc, 10);
186 --
187 -- We need to trap the ORA LOCK exception
188 --
189 Exception
190 When HR_Api.Object_Locked then
191 --
192 -- The object is locked therefore we need to supply a meaningful
193 -- error message.
194 --
195 hr_utility.set_message(801, 'HR_7165_OBJECT_LOCKED');
196 hr_utility.set_message_token('TABLE_NAME', 'pqh_template_attributes');
197 hr_utility.raise_error;
198 End lck;
199 --
200 -- ----------------------------------------------------------------------------
201 -- |-----------------------------< convert_args >-----------------------------|
202 -- ----------------------------------------------------------------------------
203 Function convert_args
204 (
205 p_required_flag in varchar2,
206 p_view_flag in varchar2,
207 p_edit_flag in varchar2,
208 p_template_attribute_id in number,
209 p_attribute_id in number,
210 p_template_id in number,
211 p_object_version_number in number
212 )
213 Return g_rec_type is
214 --
215 l_rec g_rec_type;
216 l_proc varchar2(72) := g_package||'convert_args';
217 --
218 Begin
219 --
220 hr_utility.set_location('Entering:'||l_proc, 5);
221 --
222 -- Convert arguments into local l_rec structure.
223 --
224 l_rec.required_flag := p_required_flag;
225 l_rec.view_flag := p_view_flag;
226 l_rec.edit_flag := p_edit_flag;
227 l_rec.template_attribute_id := p_template_attribute_id;
228 l_rec.attribute_id := p_attribute_id;
229 l_rec.template_id := p_template_id;
230 l_rec.object_version_number := p_object_version_number;
231 --
232 -- Return the plsql record structure.
233 --
234 hr_utility.set_location(' Leaving:'||l_proc, 10);
235 Return(l_rec);
236 --
237 End convert_args;
238 --
239 --
240 procedure LOAD_ROW
241 (p_required_flag in varchar2
242 ,p_view_flag in varchar2
243 ,p_edit_flag in varchar2
244 ,p_att_column_name in varchar2
245 ,p_att_master_table_alias_name in varchar2
246 ,p_template_short_name in varchar2
247 ,p_legislation_code in varchar2
248 ,p_owner in varchar2
249 ,p_last_update_date in varchar2
250 ) is
251 l_object_version_number number(15);
252 l_template_id pqh_template_attributes.template_id%type;
253 l_tem_txn_category_id pqh_templates.transaction_category_id%type;
254 l_att_txn_category_id pqh_transaction_categories.transaction_category_id%type;
255 l_attribute_id pqh_template_attributes.attribute_id%type;
256 l_template_attribute_id pqh_template_attributes.template_attribute_id%type;
257 --
258 l_created_by pqh_templates.created_by%TYPE;
259 l_last_updated_by pqh_templates.last_updated_by%TYPE;
260 l_creation_date pqh_templates.creation_date%TYPE;
261 l_last_update_date pqh_templates.last_update_date%TYPE;
262 l_last_update_login pqh_templates.last_update_login%TYPE;
263 --
264 cursor c_attributes is
265 select attribute_id
266 from pqh_attributes a,pqh_table_route t
267 where a.key_column_name = p_att_column_name
268 and a.master_table_route_id= t.table_route_id(+)
269 and nvl(t.table_alias,'$$$$$') = nvl(p_att_master_table_alias_name,'$$$$$')
270 and nvl(a.legislation_code,'$$$$$') = nvl(p_legislation_code,'$$$$$');
271
272 cursor c_templates is
273 select template_id, transaction_category_id
274 from pqh_templates_vl
275 where short_name = p_template_short_name;
276
277 cursor c_att_txn_category(p_attribute_id number,p_tem_txn_category_id number) is
278 select transaction_category_id
279 from pqh_txn_category_attributes
280 where attribute_id = p_attribute_id
281 and transaction_category_id = p_tem_txn_category_id;
282
283 cursor c_template_attributes (p_template_id number, p_attribute_id number) is
284 select template_attribute_id
285 from pqh_template_attributes
286 where template_id = p_template_id
287 and attribute_id = p_attribute_id;
288 --
289 l_data_migrator_mode varchar2(1);
290 --
291 BEGIN
292 --
293 l_data_migrator_mode := hr_general.g_data_migrator_mode ;
294 hr_general.g_data_migrator_mode := 'Y';
295 --
296 -- populate WHO columns
297 --
298 /**
299 if p_owner = 'SEED' then
300 l_created_by := 1;
301 l_last_updated_by := -1;
302 else
303 l_created_by := 0;
304 l_last_updated_by := 0;
305 end if;
306 **/
307
308 l_created_by := fnd_load_util.owner_id(p_owner);
309 l_last_updated_by := fnd_load_util.owner_id(p_owner);
310 /**
311 l_creation_date := sysdate;
312 l_last_update_date := sysdate;
313 **/
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 l_last_update_login := 0;
317 --
318 open c_attributes;
319 fetch c_attributes into l_attribute_id;
320
321 if c_attributes%found then
322 close c_attributes;
323 open c_templates;
324 fetch c_templates into l_template_id, l_tem_txn_category_id;
325
326 if c_templates%found then
327 close c_templates;
328 open c_att_txn_category(l_attribute_id ,l_tem_txn_category_id);
329 fetch c_att_txn_category into l_template_attribute_id;
330
331 if c_att_txn_category%found then
332 close c_att_txn_category;
333 open c_template_attributes(l_template_id, l_attribute_id);
334 fetch c_template_attributes into l_att_txn_category_id;
335
336 if c_template_attributes%found then
337 close c_template_attributes;
338 update PQH_TEMPLATE_ATTRIBUTES
339 set required_flag = p_required_flag,
340 view_flag = p_view_flag,
341 edit_flag = p_edit_flag,
342 last_update_date = l_last_update_date,
343 last_updated_by = l_last_updated_by,
344 last_update_login = l_last_update_login
345 where attribute_id = l_attribute_id
346 and template_id = l_template_id;
347 --AND NVL(last_updated_by,-1) in (1,-1);
348 else
349 close c_template_attributes;
350 --
351 insert into pqh_template_attributes
352 (required_flag, view_flag, edit_flag, template_attribute_id, attribute_id,
353 template_id, object_version_number, last_update_date, last_updated_by, last_update_login,
354 created_by, creation_date)
355 values
356 (p_required_flag, p_view_flag, p_edit_flag, pqh_template_attributes_s.nextval, l_attribute_id,
357 l_template_id, 1, l_last_update_date, l_last_updated_by, l_last_update_login,
358 l_created_by, l_creation_date);
359 --
360 end if;
361 else
362 close c_att_txn_category;
363 fnd_message.set_name(8302,'PQH_INVALID_TXN_CAT_ATTR');
364 fnd_message.set_token('ATTRIBUTE_COLUMN_NAME',p_att_column_name);
365 fnd_message.set_token('TEMPLATE_SHORT_NAME',p_template_short_name);
366 fnd_message.raise_error;
367 end if;
368 else
369 close c_templates;
370 fnd_message.set_name(8302,'PQH_INVALID_TEMPLATE');
371 fnd_message.set_token('TEMPLATE_SHORT_NAME',p_template_short_name);
372 fnd_message.raise_error;
373 end if;
374 else
375 close c_attributes;
376 fnd_message.set_name(8302,'PQH_INVALID_ATTRIBUTE');
377 fnd_message.set_token('ATTRIBUTE_COLUMN_NAME',p_att_column_name);
378 fnd_message.raise_error;
379 end if;
380 hr_general.g_data_migrator_mode := l_data_migrator_mode;
381 END;
382 --
383 --
384 end pqh_tat_shd;