[Home] [Help]
PACKAGE BODY: APPS.PQH_DOCUMENTS_SWI
Source
1 Package Body pqh_documents_swi As
2 /* $Header: pqdocswi.pkb 120.1 2005/09/15 14:17:33 rthiagar noship $ */
3 --
4 -- Package variables
5 --
6 g_package varchar2(33) := 'pqh_documents_swi.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |----------------------------< create_document >---------------------------|
10 -- ----------------------------------------------------------------------------
11 PROCEDURE create_document
12 (p_validate in number default hr_api.g_false_num
13 ,p_effective_date in date
14 ,p_short_name in varchar2
15 ,p_document_name in varchar2
16 ,p_file_id in number
17 ,p_formula_id in number
18 ,p_enable_flag in varchar2
19 ,p_document_category in varchar2
20 ,p_document_id out NOCOPY number
21 ,p_object_version_number out NOCOPY number
22 ,p_effective_start_date out NOCOPY date
23 ,p_effective_end_date out NOCOPY date
24 ,p_return_status out NOCOPY varchar2
25 /* Added for XDO changes */
26 ,p_lob_code in varchar2
27 ,p_language in varchar2
28 ,p_territory in varchar2
29 ) is
30 --
31 -- Variables for API Boolean parameters
32 l_validate boolean;
33 --
34 -- Variables for IN/OUT parameters
35 --
36 -- Other variables
37 l_document_id number;
38 l_proc varchar2(72) := g_package ||'create_document';
39 Begin
40 hr_utility.set_location(' Entering:' || l_proc,10);
41 --
42 -- Issue a savepoint
43 --
44 savepoint create_document_swi;
45 --
46 -- Initialise Multiple Message Detection
47 --
48 hr_multi_message.enable_message_list;
49 --
50 -- Remember IN OUT parameter IN values
51 --
52 --
53 -- Convert constant values to their corresponding boolean value
54 --
55 l_validate :=
56 hr_api.constant_to_boolean
57 (p_constant_value => p_validate);
58 --
59 -- Register Surrogate ID or user key values
60 --
61 -- pqh_doc_ins.set_base_key_value
62 -- (p_document_id => p_document_id
63 -- );
64 --
65 -- Call API
66 --
67 pqh_documents_api.create_print_document
68 (p_validate => l_validate
69 ,p_effective_date => p_effective_date
70 ,p_short_name => p_short_name
71 ,p_document_name => p_document_name
72 ,p_file_id => p_file_id
73 ,p_formula_id => p_formula_id
74 ,p_enable_flag => p_enable_flag
75 ,p_document_id => l_document_id
76 ,p_document_category => p_document_category
77 ,p_object_version_number => p_object_version_number
78 ,p_effective_start_date => p_effective_start_date
79 ,p_effective_end_date => p_effective_end_date
80 /* Added for XDO changes */
81 ,p_lob_code => p_lob_code
82 ,p_language => p_language
83 ,p_territory => p_territory
84 );
85 --
86 hr_utility.set_location(' Document Id '||l_document_id,18);
87 p_document_id := l_document_id;
88 -- Convert API warning boolean parameter values to specific
89 -- messages and add them to Multiple Message List
90 --
91 --
92 -- Convert API non-warning boolean parameter values
93 --
94 --
95 -- Derive the API return status value based on whether
96 -- messages of any type exist in the Multiple Message List.
97 -- Also disable Multiple Message Detection.
98 --
99 p_return_status := hr_multi_message.get_return_status_disable;
100 hr_utility.set_location(' Leaving:' || l_proc,20);
101 --
102 exception
103 when hr_multi_message.error_message_exist then
104 --
105 -- Catch the Multiple Message List exception which
106 -- indicates API processing has been aborted because
107 -- at least one message exists in the list.
108 --
109 rollback to create_document_swi;
110 --
111 -- Reset IN OUT parameters and set OUT parameters
112 --
113 p_document_id := null;
114 p_object_version_number := null;
115 p_effective_start_date := null;
116 p_effective_end_date := null;
117 p_return_status := hr_multi_message.get_return_status_disable;
118 hr_utility.set_location(' Leaving:' || l_proc, 30);
119 when others then
120 --
121 -- When Multiple Message Detection is enabled catch
122 -- any Application specific or other unexpected
123 -- exceptions. Adding appropriate details to the
124 -- Multiple Message List. Otherwise re-raise the
125 -- error.
126 --
127 rollback to create_document_swi;
128 if hr_multi_message.unexpected_error_add(l_proc) then
129 hr_utility.set_location(' Leaving:' || l_proc,40);
130 raise;
131 end if;
132 --
133 -- Reset IN OUT and set OUT parameters
134 --
135 p_document_id := null;
136 p_object_version_number := null;
137 p_effective_start_date := null;
138 p_effective_end_date := null;
139 p_return_status := hr_multi_message.get_return_status_disable;
140 hr_utility.set_location(' Leaving:' || l_proc,50);
141 end create_document;
142 -- ----------------------------------------------------------------------------
143 -- |----------------------------< delete_document >---------------------------|
144 -- ----------------------------------------------------------------------------
145 PROCEDURE delete_document
146 (p_validate in number default hr_api.g_false_num
147 ,p_effective_date in date
148 ,p_datetrack_mode in varchar2
149 ,p_document_id in number
150 ,p_object_version_number in out NOCOPY number
151 ,p_effective_start_date out NOCOPY date
152 ,p_effective_end_date out NOCOPY date
153 ,p_return_status out NOCOPY varchar2
154 ) is
155 --
156 -- Variables for API Boolean parameters
157 l_validate boolean;
158 --
159 -- Variables for IN/OUT parameters
160 l_object_version_number number;
161 --
162 -- Other variables
163 l_proc varchar2(72) := g_package ||'delete_document';
164 Begin
165 hr_utility.set_location(' Entering:' || l_proc,10);
166 --
167 -- Issue a savepoint
168 --
169 savepoint delete_document_swi;
170 --
171 -- Initialise Multiple Message Detection
172 --
173 hr_multi_message.enable_message_list;
174 --
175 -- Remember IN OUT parameter IN values
176 --
177 l_object_version_number := p_object_version_number;
178 --
179 -- Convert constant values to their corresponding boolean value
180 --
181 l_validate :=
182 hr_api.constant_to_boolean
183 (p_constant_value => p_validate);
184 --
185 -- Register Surrogate ID or user key values
186 --
187 --
188 -- Call API
189 --
190 pqh_documents_api.delete_print_document
191 (p_validate => l_validate
192 ,p_effective_date => p_effective_date
193 ,p_datetrack_mode => p_datetrack_mode
194 ,p_document_id => p_document_id
195 ,p_object_version_number => p_object_version_number
196 ,p_effective_start_date => p_effective_start_date
197 ,p_effective_end_date => p_effective_end_date
198 );
199 --
200 -- Convert API warning boolean parameter values to specific
201 -- messages and add them to Multiple Message List
202 --
203 --
204 -- Convert API non-warning boolean parameter values
205 --
206 --
207 -- Derive the API return status value based on whether
208 -- messages of any type exist in the Multiple Message List.
209 -- Also disable Multiple Message Detection.
210 --
211 p_return_status := hr_multi_message.get_return_status_disable;
212 hr_utility.set_location(' Leaving:' || l_proc,20);
213 --
214 exception
215 when hr_multi_message.error_message_exist then
216 --
217 -- Catch the Multiple Message List exception which
218 -- indicates API processing has been aborted because
219 -- at least one message exists in the list.
220 --
221 rollback to delete_document_swi;
222 --
223 -- Reset IN OUT parameters and set OUT parameters
224 --
225 p_object_version_number := l_object_version_number;
226 p_effective_start_date := null;
227 p_effective_end_date := null;
228 p_return_status := hr_multi_message.get_return_status_disable;
229 hr_utility.set_location(' Leaving:' || l_proc, 30);
230 when others then
231 --
232 -- When Multiple Message Detection is enabled catch
233 -- any Application specific or other unexpected
234 -- exceptions. Adding appropriate details to the
235 -- Multiple Message List. Otherwise re-raise the
236 -- error.
237 --
238 rollback to delete_document_swi;
239 if hr_multi_message.unexpected_error_add(l_proc) then
240 hr_utility.set_location(' Leaving:' || l_proc,40);
241 raise;
242 end if;
243 --
244 -- Reset IN OUT and set OUT parameters
245 --
246 p_object_version_number := l_object_version_number;
247 p_effective_start_date := null;
248 p_effective_end_date := null;
249 p_return_status := hr_multi_message.get_return_status_disable;
250 hr_utility.set_location(' Leaving:' || l_proc,50);
251 end delete_document;
252 -- ----------------------------------------------------------------------------
253 -- |----------------------------< update_document >---------------------------|
254 -- ----------------------------------------------------------------------------
255 PROCEDURE update_document
256 (p_validate in number default hr_api.g_false_num
257 ,p_effective_date in date
258 ,p_datetrack_mode in varchar2
259 ,p_short_name in varchar2 default hr_api.g_varchar2
260 ,p_document_name in varchar2 default hr_api.g_varchar2
261 ,p_file_id in number default hr_api.g_number
262 ,p_formula_id in number default hr_api.g_number
263 ,p_enable_flag in varchar2 default hr_api.g_varchar2
264 ,p_document_category in varchar2 default hr_api.g_varchar2
265 ,p_document_id in number
266 ,p_object_version_number in out NOCOPY number
267 ,p_effective_start_date out NOCOPY date
268 ,p_effective_end_date out NOCOPY date
269 ,p_return_status out NOCOPY varchar2
270 /* Added for XDO changes */
271 ,p_lob_code in varchar2
272 ,p_language in varchar2
273 ,p_territory in varchar2
274 ) is
275 --
276 -- Variables for API Boolean parameters
277 l_validate boolean;
278 --
279 -- Variables for IN/OUT parameters
280 l_object_version_number number;
281 --
282 -- Other variables
283 l_proc varchar2(72) := g_package ||'update_document';
284 --
285 Cursor csr_get_file_id IS
286 Select file_id exisiting_file_id
287 from pqh_documents_f
288 where document_id = p_document_id
289 and p_effective_date between effective_start_date and effective_end_date;
290 --
291 --
292 Cursor csr_child_records_4_zap IS
293 Select document_attribute_id , object_version_number ,effective_start_date
294 from pqh_document_attributes_f
295 where document_id =p_document_id
296 and effective_start_date > p_effective_date
297 and document_attribute_id NOT IN (select document_attribute_id
298 from pqh_document_attributes_f
299 where document_id =p_document_id
300 and p_effective_date between effective_start_date and effective_end_date);
301 --
302 --
303 Cursor csr_child_records_4_fut_del IS
304 Select document_attribute_id , object_version_number
305 from pqh_document_attributes_f
306 where document_id =p_document_id
307 and effective_start_date > p_effective_date
308 and document_attribute_id IN (select document_attribute_id
309 from pqh_document_attributes_f
310 where document_id =p_document_id
311 and p_effective_date between effective_start_date and effective_end_date);
312 --
313 --
314 Cursor csr_child_records_4_del IS
315 Select document_attribute_id, object_version_number,effective_start_date
316 from pqh_document_attributes_f
317 where document_id = p_document_id
318 and p_effective_date between effective_start_date and effective_end_date
319 and effective_end_date = hr_general.end_of_time;
320
321 --
322 --
323 l_current_file_id number;
324 l_existing_file_id number;
325 l_document_attribute_id number;
326 l_ovn number;
327 l_esd date;
328 l_eed date;
329 l_return_status varchar2(100);
330 l_eff_date date;
331 Begin
332 hr_utility.set_location(' Entering:' || l_proc,10);
333 --
334 -- Issue a savepoint
335 --
336 savepoint update_document_swi;
337 --
338 -- Initialise Multiple Message Detection
339 --
340 hr_multi_message.enable_message_list;
341 --
342 -- Remember IN OUT parameter IN values
343 --
344 l_object_version_number := p_object_version_number;
345 --
346 -- Convert constant values to their corresponding boolean value
347 --
348 l_validate :=
349 hr_api.constant_to_boolean
350 (p_constant_value => p_validate);
351 --
352 -- Register Surrogate ID or user key values
353 --
354 --
355 -- Call API
356 -- Added Code here for deletion Bug testing
357
358
359 l_existing_file_id := p_file_id;
360
361 OPEN csr_get_file_id;
362 FETCH csr_get_file_id into l_current_file_id;
363 CLOSE csr_get_file_id;
364
365 if (l_current_file_id <> l_existing_file_id) then
366 --
367 -- If attached file is changed then need to remove all the dependent child records for that
368 -- record . Those deletions can be of following types
369 -- 1. Do No operation on the records which are already end-dated prior to the effective date
370 -- 2. Find the records which has future start date and ZAP those records
371 -- 3. Find the effective records , which has no future versions , DELETE
372 -- 4. Find the effective records, which has future versions , DO FUTURE_CHANGE delete then DELETE
373 --
374
375 For l_rec in csr_child_records_4_zap loop
376
377 l_document_attribute_id := l_rec.document_attribute_id;
378 l_ovn := l_rec.object_version_number;
379
380 pqh_document_attributes_swi.delete_document_attribute
381 (
382 p_validate => p_validate,
383 p_effective_date => l_rec.effective_start_date,
384 p_datetrack_mode => 'ZAP' ,
385 p_document_attribute_id => l_document_attribute_id,
386 p_object_version_number => l_ovn ,
387 p_effective_start_date => l_esd,
388 p_effective_end_date => l_esd,
389 p_return_status => l_return_status
390 );
391 end loop;
392 --
393 --
394 For l_rec in csr_child_records_4_fut_del loop
395 l_document_attribute_id := l_rec.document_attribute_id;
396 l_ovn := l_rec.object_version_number;
397 pqh_document_attributes_swi.delete_document_attribute
398 (
399 p_validate => p_validate,
400 p_effective_date => p_effective_date,
401 p_datetrack_mode => 'DELETE' ,
402 p_document_attribute_id => l_document_attribute_id,
403 p_object_version_number => l_ovn ,
404 p_effective_start_date => l_esd,
405 p_effective_end_date => l_esd,
406 p_return_status => l_return_status
407 );
408 end loop;
409 --
410 --
411
412 For l_rec in csr_child_records_4_del loop
413 l_document_attribute_id := l_rec.document_attribute_id;
414 l_ovn := l_rec.object_version_number;
415 l_eff_date := l_rec.effective_start_date;
416
417 if (l_eff_date = p_effective_date) then
418
419 pqh_document_attributes_swi.delete_document_attribute
420 (
421 p_validate => p_validate,
422 p_effective_date => p_effective_date,
423 p_datetrack_mode => 'ZAP' ,
424 p_document_attribute_id => l_document_attribute_id,
425 p_object_version_number => l_ovn ,
426 p_effective_start_date => l_esd,
427 p_effective_end_date => l_esd,
428 p_return_status => l_return_status
429 );
430 else
431 pqh_document_attributes_swi.delete_document_attribute
432 (
433 p_validate => p_validate,
434 p_effective_date => p_effective_date-1,
435 p_datetrack_mode => 'DELETE' ,
436 p_document_attribute_id => l_document_attribute_id,
437 p_object_version_number => l_ovn ,
438 p_effective_start_date => l_esd,
439 p_effective_end_date => l_esd,
440 p_return_status => l_return_status
441 );
442
443 end if;
444
445
446 end loop;
447 --
448 --
449 end if;
450 --
451 --
452 pqh_documents_api.update_print_document
453 (p_validate => l_validate
454 ,p_effective_date => p_effective_date
455 ,p_datetrack_mode => p_datetrack_mode
456 ,p_short_name => p_short_name
457 ,p_document_name => p_document_name
458 ,p_file_id => p_file_id
459 ,p_formula_id => p_formula_id
460 ,p_enable_flag => p_enable_flag
461 ,p_document_category => p_document_category
462 ,p_document_id => p_document_id
463 ,p_object_version_number => p_object_version_number
464 ,p_effective_start_date => p_effective_start_date
465 ,p_effective_end_date => p_effective_end_date
466 /* Added for XDO changes */
467 ,p_lob_code => p_lob_code
468 ,p_language => p_language
469 ,p_territory => p_territory
470 );
471 --
472 -- Convert API warning boolean parameter values to specific
473 -- messages and add them to Multiple Message List
474 --
475 --
476 -- Convert API non-warning boolean parameter values
477 --
478 --
479 -- Derive the API return status value based on whether
480 -- messages of any type exist in the Multiple Message List.
481 -- Also disable Multiple Message Detection.
482 --
483 p_return_status := hr_multi_message.get_return_status_disable;
484 hr_utility.set_location(' Leaving:' || l_proc,20);
485 --
486 exception
487 when hr_multi_message.error_message_exist then
488 --
489 -- Catch the Multiple Message List exception which
490 -- indicates API processing has been aborted because
491 -- at least one message exists in the list.
492 --
493 rollback to update_document_swi;
494 --
495 -- Reset IN OUT parameters and set OUT parameters
496 --
497 p_object_version_number := l_object_version_number;
498 p_effective_start_date := null;
499 p_effective_end_date := null;
500 p_return_status := hr_multi_message.get_return_status_disable;
501 hr_utility.set_location(' Leaving:' || l_proc, 30);
502 when others then
503 --
504 -- When Multiple Message Detection is enabled catch
505 -- any Application specific or other unexpected
506 -- exceptions. Adding appropriate details to the
507 -- Multiple Message List. Otherwise re-raise the
508 -- error.
509 --
510 rollback to update_document_swi;
511 if hr_multi_message.unexpected_error_add(l_proc) then
512 hr_utility.set_location(' Leaving:' || l_proc,40);
513 raise;
514 end if;
515 --
516 -- Reset IN OUT and set OUT parameters
517 --
518 p_object_version_number := l_object_version_number;
519 p_effective_start_date := null;
520 p_effective_end_date := null;
521 p_return_status := hr_multi_message.get_return_status_disable;
522 hr_utility.set_location(' Leaving:' || l_proc,50);
523 end update_document;
524 end pqh_documents_swi;