[Home] [Help]
PACKAGE BODY: APPS.PQH_DOA_SHD
Source
4 -- ----------------------------------------------------------------------------
1 Package Body pqh_doa_shd as
2 /* $Header: pqdoarhi.pkb 115.0 2003/01/06 09:20:29 svorugan noship $ */
3 --
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_doa_shd.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------------------------< return_api_dml_status >-------------------------|
12 -- ----------------------------------------------------------------------------
13 Function return_api_dml_status Return Boolean Is
14 --
15 Begin
16 --
17 Return (nvl(g_api_dml, false));
18 --
19 End return_api_dml_status;
20 --
21 -- ----------------------------------------------------------------------------
22 -- |---------------------------< constraint_error >---------------------------|
23 -- ----------------------------------------------------------------------------
24 Procedure constraint_error
25 (p_constraint_name in all_constraints.constraint_name%TYPE
26 ) Is
27 --
28 l_proc varchar2(72) := g_package||'constraint_error';
29 --
30 Begin
31 --
32 If (p_constraint_name = 'IND1') Then
33 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
34 fnd_message.set_token('PROCEDURE', l_proc);
35 fnd_message.set_token('STEP','5');
36 fnd_message.raise_error;
37 ElsIf (p_constraint_name = 'SYS_C00303275') Then
38 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
39 fnd_message.set_token('PROCEDURE', l_proc);
40 fnd_message.set_token('STEP','10');
41 fnd_message.raise_error;
42 Else
43 fnd_message.set_name('PAY', 'HR_7877_API_INVALID_CONSTRAINT');
44 fnd_message.set_token('PROCEDURE', l_proc);
45 fnd_message.set_token('CONSTRAINT_NAME', p_constraint_name);
46 fnd_message.raise_error;
47 End If;
48 --
49 End constraint_error;
50 --
51 -- ----------------------------------------------------------------------------
52 -- |-----------------------------< api_updating >-----------------------------|
53 -- ----------------------------------------------------------------------------
54 Function api_updating
55 (p_effective_date in date
56 ,p_document_attribute_id in number
57 ,p_object_version_number in number
58 ) Return Boolean Is
62 Cursor C_Sel1 is
59 --
60 -- Cursor selects the 'current' row from the HR Schema
61 --
63 select
64 document_attribute_id
65 ,effective_start_date
66 ,effective_end_date
67 ,document_id
68 ,attribute_id
69 ,tag_name
70 ,object_version_number
71 from pqh_document_attributes_f
72 where document_attribute_id = p_document_attribute_id
73 and p_effective_date
74 between effective_start_date and effective_end_date;
75 --
76 l_fct_ret boolean;
77 --
78 Begin
79 --
80 If (p_effective_date is null or
81 p_document_attribute_id is null or
82 p_object_version_number is null) Then
83 --
84 -- One of the primary key arguments is null therefore we must
85 -- set the returning function value to false
86 --
87 l_fct_ret := false;
88 Else
89 If (p_document_attribute_id =
90 pqh_doa_shd.g_old_rec.document_attribute_id and
91 p_object_version_number =
92 pqh_doa_shd.g_old_rec.object_version_number
93 ) Then
94 --
95 -- The g_old_rec is current therefore we must
96 -- set the returning function to true
97 --
98 l_fct_ret := true;
99 Else
100 --
101 -- Select the current row
102 --
103 Open C_Sel1;
104 Fetch C_Sel1 Into pqh_doa_shd.g_old_rec;
105 If C_Sel1%notfound Then
106 Close C_Sel1;
107 --
108 -- The primary key is invalid therefore we must error
109 --
110 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
111 fnd_message.raise_error;
112 End If;
113 Close C_Sel1;
114 If (p_object_version_number
115 <> pqh_doa_shd.g_old_rec.object_version_number) Then
116 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
117 fnd_message.raise_error;
118 End If;
119 l_fct_ret := true;
120 End If;
121 End If;
122 Return (l_fct_ret);
123 --
124 End api_updating;
125 --
126 -- ----------------------------------------------------------------------------
127 -- |---------------------------< find_dt_upd_modes >--------------------------|
128 -- ----------------------------------------------------------------------------
129 Procedure find_dt_upd_modes
130 (p_effective_date in date
131 ,p_base_key_value in number
132 ,p_correction out NOCOPY boolean
133 ,p_update out NOCOPY boolean
134 ,p_update_override out NOCOPY boolean
135 ,p_update_change_insert out NOCOPY boolean
136 ) is
137 --
138 l_proc varchar2(72) := g_package||'find_dt_upd_modes';
139 --
140 Begin
141 hr_utility.set_location('Entering:'||l_proc, 5);
142 --
143 -- Call the corresponding datetrack api
144 --
145 dt_api.find_dt_upd_modes
146 (p_effective_date => p_effective_date
147 ,p_base_table_name => 'pqh_document_attributes_f'
148 ,p_base_key_column => 'document_attribute_id'
149 ,p_base_key_value => p_base_key_value
150 ,p_correction => p_correction
151 ,p_update => p_update
152 ,p_update_override => p_update_override
153 ,p_update_change_insert => p_update_change_insert
154 );
155 --
156 hr_utility.set_location(' Leaving:'||l_proc, 10);
157 End find_dt_upd_modes;
158 --
159 -- ----------------------------------------------------------------------------
160 -- |---------------------------< find_dt_del_modes >--------------------------|
161 -- ----------------------------------------------------------------------------
162 Procedure find_dt_del_modes
163 (p_effective_date in date
164 ,p_base_key_value in number
165 ,p_zap out NOCOPY boolean
166 ,p_delete out NOCOPY boolean
167 ,p_future_change out NOCOPY boolean
168 ,p_delete_next_change out NOCOPY boolean
169 ) is
170 --
171 l_proc varchar2(72) := g_package||'find_dt_del_modes';
172 --
173 --
174 Begin
175 hr_utility.set_location('Entering:'||l_proc, 5);
176 --
177 -- Call the corresponding datetrack api
178 --
179 dt_api.find_dt_del_modes
180 (p_effective_date => p_effective_date
181 ,p_base_table_name => 'pqh_document_attributes_f'
182 ,p_base_key_column => 'document_attribute_id'
183 ,p_base_key_value => p_base_key_value
184 ,p_zap => p_zap
185 ,p_delete => p_delete
186 ,p_future_change => p_future_change
187 ,p_delete_next_change => p_delete_next_change
188 );
189 --
190 hr_utility.set_location(' Leaving:'||l_proc, 10);
191 End find_dt_del_modes;
192 --
193 -- ----------------------------------------------------------------------------
194 -- |-----------------------< upd_effective_end_date >-------------------------|
195 -- ----------------------------------------------------------------------------
196 Procedure upd_effective_end_date
197 (p_effective_date in date
198 ,p_base_key_value in number
199 ,p_new_effective_end_date in date
200 ,p_validation_start_date in date
201 ,p_validation_end_date in date
202 ,p_object_version_number out NOCOPY number
206 l_object_version_number number;
203 ) is
204 --
205 l_proc varchar2(72) := g_package||'upd_effective_end_date';
207 --
208 Begin
209 hr_utility.set_location('Entering:'||l_proc, 5);
210 --
211 -- Because we are updating a row we must get the next object
212 -- version number.
213 --
214 l_object_version_number :=
215 dt_api.get_object_version_number
216 (p_base_table_name => 'pqh_document_attributes_f'
217 ,p_base_key_column => 'document_attribute_id'
218 ,p_base_key_value => p_base_key_value
219 );
220 --
221 hr_utility.set_location(l_proc, 10);
222 pqh_doa_shd.g_api_dml := true; -- Set the api dml status
223 --
224 -- Update the specified datetrack row setting the effective
225 -- end date to the specified new effective end date.
226 --
227 update pqh_document_attributes_f t
228 set t.effective_end_date = p_new_effective_end_date
229 , t.object_version_number = l_object_version_number
230 where t.document_attribute_id = p_base_key_value
231 and p_effective_date
232 between t.effective_start_date and t.effective_end_date;
233 --
234 pqh_doa_shd.g_api_dml := false; -- Unset the api dml status
235 p_object_version_number := l_object_version_number;
236 hr_utility.set_location(' Leaving:'||l_proc, 15);
237 --
238 Exception
239 When Others Then
240 pqh_doa_shd.g_api_dml := false; -- Unset the api dml status
241 Raise;
242 --
243 End upd_effective_end_date;
244 --
245 -- ----------------------------------------------------------------------------
246 -- |---------------------------------< lck >----------------------------------|
247 -- ----------------------------------------------------------------------------
248 Procedure lck
249 (p_effective_date in date
250 ,p_datetrack_mode in varchar2
251 ,p_document_attribute_id in number
252 ,p_object_version_number in number
253 ,p_validation_start_date out NOCOPY date
254 ,p_validation_end_date out NOCOPY date
255 ) is
256 --
257 l_proc varchar2(72) := g_package||'lck';
258 l_validation_start_date date;
259 l_validation_end_date date;
260 l_argument varchar2(30);
261 --
262 -- Cursor C_Sel1 selects the current locked row as of session date
263 -- ensuring that the object version numbers match.
264 --
265 Cursor C_Sel1 is
266 select
267 document_attribute_id
268 ,effective_start_date
269 ,effective_end_date
270 ,document_id
271 ,attribute_id
272 ,tag_name
273 ,object_version_number
274 from pqh_document_attributes_f
275 where document_attribute_id = p_document_attribute_id
276 and p_effective_date
277 between effective_start_date and effective_end_date
278 for update nowait;
279 --
280 --
281 --
282 Begin
283 hr_utility.set_location('Entering:'||l_proc, 5);
284 --
285 -- Ensure that all the mandatory arguments are not null
286 --
287 hr_api.mandatory_arg_error(p_api_name => l_proc
288 ,p_argument => 'effective_date'
289 ,p_argument_value => p_effective_date
290 );
291 --
292 hr_api.mandatory_arg_error(p_api_name => l_proc
293 ,p_argument => 'datetrack_mode'
294 ,p_argument_value => p_datetrack_mode
295 );
296 --
297 hr_api.mandatory_arg_error(p_api_name => l_proc
298 ,p_argument => 'document_attribute_id'
299 ,p_argument_value => p_document_attribute_id
300 );
301 --
302 hr_api.mandatory_arg_error(p_api_name => l_proc
303 ,p_argument => 'object_version_number'
304 ,p_argument_value => p_object_version_number
305 );
306 --
307 -- Check to ensure the datetrack mode is not INSERT.
308 --
309 If (p_datetrack_mode <> hr_api.g_insert) then
310 --
311 -- We must select and lock the current row.
312 --
313 Open C_Sel1;
314 Fetch C_Sel1 Into pqh_doa_shd.g_old_rec;
315 If C_Sel1%notfound then
316 Close C_Sel1;
317 --
318 -- The primary key is invalid therefore we must error
319 --
320 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
321 fnd_message.raise_error;
322 End If;
323 Close C_Sel1;
324 If (p_object_version_number
325 <> pqh_doa_shd.g_old_rec.object_version_number) Then
326 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
327 fnd_message.raise_error;
328 End If;
329 --
330 --
331 -- Validate the datetrack mode mode getting the validation start
332 -- and end dates for the specified datetrack operation.
333 --
334 dt_api.validate_dt_mode
338 ,p_base_key_column => 'document_attribute_id'
335 (p_effective_date => p_effective_date
336 ,p_datetrack_mode => p_datetrack_mode
337 ,p_base_table_name => 'pqh_document_attributes_f'
342 ,p_parent_key_value1 => pqh_doa_shd.g_old_rec.document_id
339 ,p_base_key_value => p_document_attribute_id
340 ,p_parent_table_name1 => 'pqh_documents_f'
341 ,p_parent_key_column1 => 'document_id'
343 ,p_enforce_foreign_locking => true
344 ,p_validation_start_date => l_validation_start_date
345 ,p_validation_end_date => l_validation_end_date
346 );
347 Else
348 --
349 -- We are doing a datetrack 'INSERT' which is illegal within this
350 -- procedure therefore we must error (note: to lck on insert the
351 -- private procedure ins_lck should be called).
352 --
353 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
354 fnd_message.set_token('PROCEDURE', l_proc);
355 fnd_message.set_token('STEP','20');
356 fnd_message.raise_error;
357 End If;
358 --
359 -- Set the validation start and end date OUT arguments
360 --
361 p_validation_start_date := l_validation_start_date;
362 p_validation_end_date := l_validation_end_date;
363 --
364 hr_utility.set_location(' Leaving:'||l_proc, 30);
365 --
366 -- We need to trap the ORA LOCK exception
367 --
368 Exception
369 When HR_Api.Object_Locked then
370 --
371 -- The object is locked therefore we need to supply a meaningful
372 -- error message.
373 --
374 fnd_message.set_name('PAY', 'HR_7165_OBJECT_LOCKED');
375 fnd_message.set_token('TABLE_NAME', 'pqh_document_attributes_f');
376 fnd_message.raise_error;
377 End lck;
378 --
379 -- ----------------------------------------------------------------------------
380 -- |-----------------------------< convert_args >-----------------------------|
381 -- ----------------------------------------------------------------------------
382 Function convert_args
383 (p_document_attribute_id in number
384 ,p_effective_start_date in date
385 ,p_effective_end_date in date
386 ,p_document_id in number
387 ,p_attribute_id in number
388 ,p_tag_name in varchar2
389 ,p_object_version_number in number
390 )
391 Return g_rec_type is
392 --
393 l_rec g_rec_type;
394 --
395 Begin
396 --
397 -- Convert arguments into local l_rec structure.
398 --
399 l_rec.document_attribute_id := p_document_attribute_id;
400 l_rec.effective_start_date := p_effective_start_date;
401 l_rec.effective_end_date := p_effective_end_date;
402 l_rec.document_id := p_document_id;
403 l_rec.attribute_id := p_attribute_id;
404 l_rec.tag_name := p_tag_name;
405 l_rec.object_version_number := p_object_version_number;
406 --
407 -- Return the plsql record structure.
408 --
409 Return(l_rec);
410 --
411 End convert_args;
412 --
413 end pqh_doa_shd;