DBA Data[Home] [Help]

PACKAGE BODY: APPS.PQH_DOCUMENTS_API

Source


1 Package BODY pqh_documents_api as
2 /* $Header: pqdocapi.pkb 120.1 2005/09/15 14:14:51 rthiagar noship $ */
3 --
4 --
5 -- Package Variables
6 --
7 g_package  varchar2(33) := '  PQH_DOCUMENTS_API.';
8 
9 -- ----------------------------------------------------------------------------
10 -- |--------------------------<CREATE_ELIMINATED_REC>--------------------------|-- ----------------------------------------------------------------------------
11 --
12 procedure create_dummy_rec(
13   p_document_id number,
14   p_object_version_number in out nocopy number,
15   p_effective_date date) is
16 --
17 l_proc                     varchar2(72) ;
18 l_effective_start_date date;
19 l_effective_end_date date ;
20 l_date_effective date;
21 l_short_name varchar2(30);
22 l_document_name varchar2(100);
23 l_file_id number;
24 l_formula_id number;
25 l_enable_flag varchar2(15) := 'N';
26 l_object_version_number number(9);
27 l_document_category varchar2(30);
28 /* Added for XDO changes */
29 l_lob_code  varchar2(80);
30 l_language  varchar2(6);
31 l_territory  varchar2(6);
32 --
33 cursor c1 is
34 select effective_start_date,effective_end_date,short_name,document_name,
35 file_id,formula_id,object_version_number,document_category,lob_code,language,territory
36 from pqh_documents_f
37 where document_id = p_document_id
38 and p_effective_date between effective_start_date and effective_end_date;
39 --
40 begin
41   l_proc  := g_package||'create_dummy_rec';
42   hr_utility.set_location('Entering:'|| l_proc, 5);
43   --
44   open c1;
45   fetch c1 into l_effective_start_date,l_effective_end_date,
46   l_short_name,l_document_name,l_file_id,l_formula_id,l_object_version_number,l_document_category,l_lob_code,l_language,l_territory;
47   close c1;
48   --
49 
50   if (p_document_id is not null
51    and p_object_version_number is not null
52    and p_effective_date is not null) then
53     --
54     pqh_documents_api.update_document
55     (p_validate             => false
56     ,p_effective_date       => p_effective_date+1
57     ,p_datetrack_mode       => hr_api.g_update
58     ,p_short_name           => l_short_name
59     ,p_document_name        => l_document_name
60     ,p_file_id              => l_file_id
61     ,p_formula_id           => l_formula_id
62     ,p_enable_flag          => l_enable_flag
63     ,p_document_id          =>  p_document_id
64     ,p_document_category    =>  l_document_category
65     ,p_object_version_number => p_object_version_number
66     ,p_effective_start_date  => l_effective_start_date
67     ,p_effective_end_date    => l_effective_end_date
68     /* Added for XDO */
69     ,p_lob_code              => l_lob_code
70     ,p_language              => l_language
71     ,p_territory             => l_territory
72      );
73   end if;
74 
75 end;
76 
77 
78 --
79 --
80 -- ----------------------------------------------------------------------------
81 -- |--------------------------<CREATE_PRINT_DOCUMENT>--------------------------|
82 -- ----------------------------------------------------------------------------
83 --
84 procedure create_print_document
85   (p_validate                       in     boolean  default false
86   ,p_effective_date                 in     date
87   ,p_short_name                     in     varchar2
88   ,p_document_name                  in 	   varchar2
89   ,p_file_id                        in     number
90   ,p_formula_id                     in     number
91   ,p_enable_flag                    in     varchar2
92   ,p_document_category              in     varchar2
93   ,p_document_id                    out NOCOPY     number
94   ,p_object_version_number          out NOCOPY     number
95   ,p_effective_start_date           out NOCOPY     date
96   ,p_effective_end_date     	    out NOCOPY	   date
97   /* Added for XDO changes */
98   ,p_lob_code                       in     varchar2
99   ,p_language                       in     varchar2
100   ,p_territory                      in     varchar2
101   ) is
102   --
103   -- Declare cursors and local variables
104   --
105    l_proc                   varchar2(72) := g_package||'CREATE_PRINT_DOCUMENT';
106 
107    l_document_id            pqh_documents_f.document_id%TYPE;
108    l_object_version_number  pqh_documents_f.object_version_number%TYPE;
109    l_effective_start_date   pqh_documents_f.effective_start_date%TYPE;
110    l_effective_end_date     pqh_documents_f.effective_end_date%TYPE;
111     --
112   begin
113   hr_utility.set_location('Entering:'|| l_proc, 10);
114   --
115     -- Issue a savepoint
116     --
117     savepoint CREATE_PRINT_DOCUMENT;
118     --
119     -- Truncate the time portion from all IN date parameters
120     --
121 
122     --
123     -- Call Before Process User Hook
124   begin
125        PQH_DOCUMENTS_BK1.create_print_document_b
126         (p_effective_date                => p_effective_date
127            ,p_short_name                 => p_short_name
128            ,p_document_name              => p_document_name
129            ,p_file_id                    => p_file_id
130            ,p_formula_id                 => p_formula_id
131            ,p_enable_flag                => p_enable_flag
132            ,p_document_category          => p_document_category
133            /* Added for XDO changes */
134            ,p_lob_code                   => p_lob_code
135            ,p_language                   => p_language
136            ,p_territory                  => p_territory
137  	);
138    exception
139       when hr_api.cannot_find_prog_unit then
140         hr_api.cannot_find_prog_unit_error
141           (p_module_name => 'CREATE_PRINT_DOCUMENT'
142           ,p_hook_type   => 'BP'
143           );
144    end;
145   --
146   -- Validation in addition to Row Handlers
147   --
148   --
149   -- Process Logic
150   --
151   	pqh_doc_ins.ins
152     	 (p_effective_date            => p_effective_date
153 	  ,p_short_name               => p_short_name
154 	  ,p_document_name            => p_document_name
155 	  ,p_file_id                  => p_file_id
156 	  ,p_enable_flag              => p_enable_flag
157 	  ,p_formula_id               => p_formula_id
158 	  ,p_document_category        => p_document_category
159 	  ,p_document_id              => l_document_id
160 	  ,p_object_version_number    => l_object_version_number
161  	  ,p_effective_start_date     => l_effective_start_date
162   	  ,p_effective_end_date       => l_effective_end_date
163           /* Added for XDO */
164           ,p_lob_code              => p_lob_code
165           ,p_language              => p_language
166           ,p_territory             => p_territory
167          );
168   --
169   -- Call After Process User Hook
170   --
171   begin
172        PQH_DOCUMENTS_BK1.create_print_document_a
173        (p_effective_date            =>   p_effective_date
174        ,p_short_name                =>   p_short_name
175        ,p_document_name             =>   p_document_name
176        ,p_file_id                   =>   p_file_id
177        ,p_formula_id                =>   p_formula_id
178        ,p_enable_flag               =>   p_enable_flag
179        ,p_document_category         =>   p_document_category
180        ,p_document_id               =>   l_document_id
181        ,p_object_version_number     =>   l_object_version_number
182        ,p_effective_start_date      =>   l_effective_start_date
183        ,p_effective_end_date        =>   l_effective_end_date
184        /* Added for XDO */
185        ,p_lob_code                  =>   p_lob_code
186        ,p_language                  =>   p_language
187        ,p_territory                 =>   p_territory
188 	);
189    exception
190         when hr_api.cannot_find_prog_unit then
191           hr_api.cannot_find_prog_unit_error
192             (p_module_name => 'CREATE_PRINT_DOCUMENT'
193             ,p_hook_type   => 'AP'
194             );
195    end;
196 
197   --
198   -- When in validation only mode raise the Validate_Enabled exception
199   --
200   if p_validate then
201     raise hr_api.validate_enabled;
202   end if;
203   --
204   -- Set all output arguments
205   --
206   p_document_id       	   := l_document_id;
207   p_object_version_number  := l_object_version_number;
208   p_effective_start_date   := l_effective_start_date;
209   p_effective_end_date     := l_effective_end_date;
210   --
211   hr_utility.set_location(' Leaving:'||l_proc, 70);
212 exception
213   when hr_api.validate_enabled then
214     --
215     -- As the Validate_Enabled exception has been raised
216     -- we must rollback to the savepoint
217     --
218     rollback to CREATE_PRINT_DOCUMENT;
219     --
220     -- Only set output warning arguments
221     -- (Any key or derived arguments must be set to null
222     -- when validation only mode is being used.)
223     --
224     p_document_id            := null;
225     p_object_version_number  := null;
226     p_effective_start_date   := null;
227     p_effective_end_date     := null;
228     hr_utility.set_location(' Leaving:'||l_proc, 80);
229 --
230 when others then
231     --
232     -- A validation or unexpected error has occured
233     --
234     p_document_id       := null;
235     p_object_version_number  := null;
236     p_effective_start_date   := null;
237     p_effective_end_date     := null;
238 
239     rollback to CREATE_PRINT_DOCUMENT;
240     hr_utility.set_location(' Leaving:'||l_proc, 90);
241     raise;
242 end create_print_document;
243 
244 --
245 --
246 --
247 
248 procedure create_document
249   (p_validate                       in     boolean  default false
250   ,p_effective_date                 in     date
251   ,p_short_name                     in     varchar2
252   ,p_document_name                  in 	   varchar2
253   ,p_file_id                        in     number
254   ,p_formula_id                     in     number
255   ,p_enable_flag                    in     varchar2
256   ,p_document_category              in     varchar2
257   ,p_document_id                    out NOCOPY     number
258   ,p_object_version_number          out NOCOPY     number
259   ,p_effective_start_date           out NOCOPY     date
260   ,p_effective_end_date     	    out NOCOPY	   date
261   /* Added for XDO changes */
262   ,p_lob_code                       in     varchar2
263   ,p_language                       in     varchar2
264   ,p_territory                      in     varchar2
265   ) is
266 begin
267 --
268 -- Wrapper for UI
269 --
270 create_print_document
271   (p_validate                       => p_validate
272   ,p_effective_date                 => p_effective_date
273   ,p_short_name                     => p_short_name
274   ,p_document_name                  => p_document_name
275   ,p_file_id                        => p_file_iD
276   ,p_formula_id                     => p_formula_id
277   ,p_enable_flag                    => p_enable_flag
278   ,p_document_id                    => p_document_id
279   ,p_document_category              => p_document_category
280   ,p_object_version_number          => p_object_version_number
281   ,p_effective_start_date           => p_effective_start_date
282   ,p_effective_end_date     	    => p_effective_end_date
283   /* Added for XDO changes */
284   ,p_lob_code                       => p_lob_code
285   ,p_language                       => p_language
286   ,p_territory                      => p_territory
287   );
288 
289 end create_document;
290 --
291 --
292 -- ----------------------------------------------------------------------------
293 -- |--------------------------<update_print_document>--------------------------|
294 -- ----------------------------------------------------------------------------
295 --
296 procedure update_print_document
297   (p_validate                       in     boolean  default false
298   ,p_effective_date                 in     date
302   ,p_file_id                        in     number   default hr_api.g_number
299   ,p_datetrack_mode                 in     varchar2
300   ,p_short_name                     in     varchar2 default hr_api.g_varchar2
301   ,p_document_name                  in 	   varchar2 default hr_api.g_varchar2
303   ,p_formula_id                     in     number   default hr_api.g_number
304   ,p_enable_flag                    in     varchar2 default hr_api.g_varchar2
305   ,p_document_category              in     varchar2 default hr_api.g_varchar2
306   ,p_document_id                    in     number
307   ,p_object_version_number          in OUT NOCOPY     number
308   ,p_effective_start_date           OUT    NOCOPY     date
309   ,p_effective_end_date     	    OUT    NOCOPY     date
310   /* Added for XDO changes */
311   ,p_lob_code                       in     varchar2
312   ,p_language                       in     varchar2
313   ,p_territory                      in     varchar2
314   ) is
315  --
316   -- Declare cursors and local variables
317   --
318 
319   l_proc                   varchar2(72) := g_package||'UPDATE_PRINT_DOCUMENT';
320 
321   l_effective_start_date   pqh_documents_f.effective_start_date%TYPE;
322   l_effective_end_date     pqh_documents_f.effective_end_date%TYPE;
323   l_object_version_number number := p_object_version_number;
324   --
325 begin
326   hr_utility.set_location('Entering:'|| l_proc, 10);
327   --
328   -- Issue a savepoint
329   --
330   savepoint UPDATE_PRINT_DOCUMENT;
331   --
332   -- Truncate the time portion from all IN date parameters
333   --
334 
335   --
336   -- Call Before Process User Hook
337  begin
338     PQH_DOCUMENTS_BK2.update_print_document_b
339     (p_effective_date             => p_effective_date
340     ,p_datetrack_mode             => p_datetrack_mode
341     ,p_short_name                 => p_short_name
342     ,p_document_name              => p_document_name
343     ,p_file_id                    => p_file_id
344     ,p_formula_id                 => p_formula_id
345     ,p_enable_flag                => p_enable_flag
346     ,p_document_id                => p_document_id
347     ,p_document_category          => p_document_category
348     ,p_object_version_number      => p_object_version_number
349     /* Added for XDO changes */
350     ,p_lob_code                   => p_lob_code
351     ,p_language                   => p_language
352     ,p_territory                  => p_territory
353     );
354  exception
355     when hr_api.cannot_find_prog_unit then
356       hr_api.cannot_find_prog_unit_error
357         (p_module_name => 'UPDATE_PRINT_DOCUMENT'
358         ,p_hook_type   => 'BP'
359         );
360   end;
361   --
362   -- Validation in addition to Row Handlers
363   --
364   -- Process Logic
365   --
366   pqh_doc_upd.upd
367     (p_effective_date              => p_effective_date
368     ,p_datetrack_mode             => p_datetrack_mode
369     ,p_document_id                => p_document_id
370     ,p_object_version_number      => p_object_version_number
371     ,p_short_name                 => p_short_name
372     ,p_document_name              => p_document_name
373     ,p_file_id                    => p_file_id
374     ,p_enable_flag                => p_enable_flag
375     ,p_formula_id                 => p_formula_id
376     ,p_document_category          => p_document_category
377     ,p_effective_start_date       => l_effective_start_date
378     ,p_effective_end_date         => l_effective_end_date
379     /* Added for XDO changes */
380     ,p_lob_code                   => p_lob_code
381     ,p_language                   => p_language
382     ,p_territory                  => p_territory
383     );
384 
385  --
386  -- Call After Process User Hook
387  --
388  begin
389     PQH_DOCUMENTS_BK2.update_print_document_a
390     (p_effective_date             => p_effective_date
391     ,p_datetrack_mode             => p_datetrack_mode
392     ,p_short_name                 => p_short_name
393     ,p_document_name              => p_document_name
394     ,p_file_id                    => p_file_id
395     ,p_formula_id                 => p_formula_id
396     ,p_enable_flag                => p_enable_flag
397     ,p_document_id                => p_document_id
398     ,p_document_category          => p_document_category
399     ,p_object_version_number      => p_object_version_number
400     ,p_effective_start_date       => l_effective_start_date
401     ,p_effective_end_date         => l_effective_end_date
402     /* Added for XDO changes */
403     ,p_lob_code                   => p_lob_code
404     ,p_language                   => p_language
405     ,p_territory                  => p_territory
406     );
407  exception
408     when hr_api.cannot_find_prog_unit then
409       hr_api.cannot_find_prog_unit_error
410         (p_module_name => 'UPDATE_PRINT_DOCUMENT'
411         ,p_hook_type   => 'AP'
412         );
413   end;
414  --
415   -- When in validation only mode raise the Validate_Enabled exception
416   --
417   if p_validate then
421   -- Set all output arguments
418     raise hr_api.validate_enabled;
419   end if;
420   --
422   --
423 
424   p_object_version_number  := l_object_version_number;
425   p_effective_start_date   := l_effective_start_date;
426   p_effective_end_date     := l_effective_end_date;
427   --
428   hr_utility.set_location(' Leaving:'||l_proc, 70);
429 exception
430   when hr_api.validate_enabled then
431     --
432     -- As the Validate_Enabled exception has been raised
433     -- we must rollback to the savepoint
434     --
435     rollback to UPDATE_PRINT_DOCUMENT;
436     --
437     -- Only set output warning arguments
438     -- (Any key or derived arguments must be set to null
439     -- when validation only mode is being used.)
440     --
441    -- p_document_id            := null;
442     p_object_version_number  := null;
443     p_effective_start_date   := null;
444     p_effective_end_date     := null;
445     hr_utility.set_location(' Leaving:'||l_proc, 80);
446 --
447 when others then
448     --
449     -- A validation or unexpected error has occured
450     --
451  --   p_document_id       := null;
452     p_object_version_number  := null;
453     p_effective_start_date   := null;
454     p_effective_end_date     := null;
455 
456     rollback to UPDATE_PRINT_DOCUMENT;
457     hr_utility.set_location(' Leaving:'||l_proc, 90);
458     raise;
459 end update_print_document;
460 --
461 --
462 procedure update_document
463   (p_validate                       in     boolean  default false
464   ,p_effective_date                 in     date
465   ,p_datetrack_mode                 in     varchar2
466   ,p_short_name                     in     varchar2 default hr_api.g_varchar2
467   ,p_document_name                  in 	   varchar2 default hr_api.g_varchar2
468   ,p_file_id                        in     number   default hr_api.g_number
469   ,p_formula_id                     in     number   default hr_api.g_number
470   ,p_enable_flag                    in     varchar2 default hr_api.g_varchar2
471   ,p_document_category              in     varchar2 default hr_api.g_varchar2
472   ,p_document_id                    in     number
473   ,p_object_version_number          in OUT NOCOPY     number
474   ,p_effective_start_date           OUT    NOCOPY     date
475   ,p_effective_end_date     	    OUT    NOCOPY     date
476   /* Added for XDO changes */
477   ,p_lob_code                       in     varchar2
478   ,p_language                       in     varchar2
479   ,p_territory                      in     varchar2
480   ) is
481 
482 begin
483 update_print_document
484   (p_validate                       => p_validate
485   ,p_effective_date                 => p_effective_date
486   ,p_datetrack_mode                 => p_datetrack_mode
487   ,p_short_name                     => p_short_name
488   ,p_document_name                  => p_document_name
489   ,p_file_id                        => p_file_iD
490   ,p_formula_id                     => p_formula_id
491   ,p_enable_flag                    => p_enable_flag
492   ,p_document_id                    => p_document_id
493   ,p_document_category              => p_document_category
494   ,p_object_version_number          => p_object_version_number
495   ,p_effective_start_date           => p_effective_start_date
496   ,p_effective_end_date             => p_effective_end_date
497   /* Added for XDO changes */
498   ,p_lob_code                       => p_lob_code
499   ,p_language                       => p_language
500   ,p_territory                      => p_territory
501   );
502 
503 end update_document;
504 --
505 -- Wrapper for UI
506 --
507 -- ----------------------------------------------------------------------------
508 -- |--------------------------<DELETE_PRINT_DOCUMENT>--------------------------|
509 -- ----------------------------------------------------------------------------
510 -- {Start Of Comments}
511 --
512 -- Description:
513 --
514 --
515 -- Prerequisites:
516 --
517 --
518 -- In Parameters:
519 --   Name                           Reqd Type     Description
520 --
521 --
522 -- Post Success:
523 --
527 -- Post Failure:
524 --
525 --   Name                           Type     Description
526 --
528 --
529 --
530 -- Access Status:
531 --   Public.
532 --
533 -- {End Of Comments}
534 --
535 procedure delete_print_document
536   (p_validate                       in     boolean  default false
537   ,p_effective_date                 in     date
538   ,p_datetrack_mode                 in     varchar2
539   ,p_document_id                    in     number
540   ,p_object_version_number          in OUT NOCOPY     number
541   ,p_effective_start_date           OUT    NOCOPY     date
542   ,p_effective_end_date     	    OUT    NOCOPY     date
543   )
544   is
545     --
546     -- Declare cursors and local variables
547     --
548 
549     l_proc      varchar2(72) := g_package||'DELETE_PRINT_DOCUMENT';
550     l_effective_start_date   pqh_documents_f.effective_start_date%TYPE;
551     l_effective_end_date     pqh_documents_f.effective_end_date%TYPE;
552   l_object_version_number number :=       p_object_version_number;
553     --
554   begin
555     hr_utility.set_location('Entering:'|| l_proc, 10);
556     --
557     -- Issue a savepoint
558     --
559     savepoint DELETE_PRINT_DOCUMENT;
560     --
561     -- Truncate the time portion from all IN date parameters
562     --
563 
564     --
565     -- Call Before Process User Hook
566     --
567   begin
568    PQH_DOCUMENTS_BK3.delete_print_document_b
569     (p_effective_date            => p_effective_date
570     ,p_datetrack_mode            => p_datetrack_mode
571     ,p_document_id               => p_document_id
572     ,p_object_version_number     => p_object_version_number
573     );
574     exception
575        when hr_api.cannot_find_prog_unit then
576          hr_api.cannot_find_prog_unit_error
577            (p_module_name => 'DELETE_PRINT_DOCUMENT'
578            ,p_hook_type   => 'BP'
579            );
580   end;
581  --
582  -- Validation in addition to Row Handlers
583  --
584  -- Process Logic
585  --
586  pqh_doc_del.del
587   (p_effective_date                  => p_effective_date
588    ,p_datetrack_mode                  => p_datetrack_mode
589    ,p_document_id                     => p_document_id
590    ,p_object_version_number           => p_object_version_number
591    ,p_effective_start_date            => l_effective_start_date
592    ,p_effective_end_date              => l_effective_end_date
593    );
594 
595  --
596   -- Call After Process User Hook
597   --
598   begin
599    PQH_DOCUMENTS_BK3.delete_print_document_a
600        (p_effective_date            => p_effective_date
601        ,p_datetrack_mode            => p_datetrack_mode
602        ,p_document_id               => p_document_id
603        ,p_object_version_number     => p_object_version_number
604        ,p_effective_start_date      => l_effective_start_date
605        ,p_effective_end_date        => l_effective_end_date
606     );
607      exception
608         when hr_api.cannot_find_prog_unit then
609           hr_api.cannot_find_prog_unit_error
610             (p_module_name => 'DELETE_PRINT_DOCUMENT'
611             ,p_hook_type   => 'AP'
612             );
613   end;
614   --
615     -- When in validation only mode raise the Validate_Enabled exception
616     --
617     if p_validate then
618       raise hr_api.validate_enabled;
619     end if;
620     --
621     p_object_version_number  := p_object_version_number;
622     p_effective_start_date   := l_effective_start_date;
623     p_effective_end_date     := l_effective_end_date;
624     --
625     --
626     hr_utility.set_location(' Leaving:'||l_proc, 70);
627   exception
628     when hr_api.validate_enabled then
629       --
630       -- As the Validate_Enabled exception has been raised
631       -- we must rollback to the savepoint
632       --
633       rollback to DELETE_PRINT_DOCUMENT;
634       --
635       -- Only set output warning arguments
636       -- (Any key or derived arguments must be set to null
637       -- when validation only mode is being used.)
638       --
639       p_object_version_number  := l_object_version_number;
640       p_effective_start_date   := null;
641       p_effective_end_date     := null;
642       --
643       hr_utility.set_location(' Leaving:'||l_proc, 80);
644     when others then
645       --
646       -- A validation or unexpected error has occured
647       --
648       p_object_version_number  := l_object_version_number;
649       p_effective_start_date   := null;
650       p_effective_end_date     := null;
651 
652       rollback to DELETE_PRINT_DOCUMENT;
653       hr_utility.set_location(' Leaving:'||l_proc, 90);
654     raise;
655  --
656  end delete_print_document;
657 --
658 --
659 procedure delete_document
660   (p_validate                       in     boolean  default false
661   ,p_effective_date                 in     date
662   ,p_datetrack_mode                 in     varchar2
663   ,p_document_id                    in     number
664   ,p_object_version_number          in OUT NOCOPY     number
665   ,p_effective_start_date           OUT    NOCOPY     date
666   ,p_effective_end_date     	    OUT    NOCOPY     date
667   ) is
668 
669 begin
670 
671  if ( p_datetrack_mode = hr_api.g_delete) then
672     create_dummy_rec(
673        p_document_id => p_document_id,
674        p_object_version_number => p_object_version_number,
675        p_effective_date        => p_effective_date);
676  else
677     delete_print_document
678       (p_validate                       => p_validate
679       ,p_effective_date                 => p_effective_date
680       ,p_datetrack_mode                 => p_datetrack_mode
681       ,p_document_id                    => p_document_id
682       ,p_object_version_number          => p_object_version_number
683       ,p_effective_start_date           => p_effective_start_date
684       ,p_effective_end_date             => p_effective_end_date
685      );
686 end if;
687 
688 end delete_document;
689 --
690 --
691 end pqh_documents_api;