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