PACKAGE BODY: APPS.HR_TIPS_DML

Source

1 package body hr_tips_dml as
2 /* $Header: hrtipdml.pkb 115.1 99/10/05 17:58:53 porting ship $ */
3 --
4 procedure addTip(p_filename            varchar2
5                 ,p_screen              varchar2
6                 ,p_field               varchar2
7                 ,p_language            varchar2
8                 ,p_business_group_id   number      default null
9                 ,p_text                long
10                 ,p_mode                varchar2
11       ) is
12 --
13 cursor csr_exists_tip is
14 select 'Y'
15   from hr_tips t
16  where upper(t.screen)             = upper(p_screen)
17    and upper(t.field)              = upper(p_field)
18    and upper(t.language_code)      = upper(p_language)
19    and nvl(t.business_group_id, 0) = nvl(p_business_group_id, 0);
20 --
21 l_exists varchar2(1);
22 --
23 begin
24 --
25    open csr_exists_tip;
26    fetch csr_exists_tip into l_exists;
27    --
28 
29    if p_mode = 'UPDATE' then
30       -- if the tip already exists in the database then update it
31       if csr_exists_tip%found then
32          -- update it
33          update hr_tips
34             set text                      = p_text
35           where upper(screen)             = upper(p_screen)
36             and upper(field)              = upper(p_field)
37             and upper(language_code)      = upper(p_language)
38             and nvl(business_group_id, 0) = nvl(p_business_group_id, 0);
39       else
40          -- insert it
41          insert into hr_tips
42                      (tip_id
43                      ,filename
44                      ,screen
45                      ,field
46                      ,language_code
47                      ,business_group_id
48                      ,text
49                      ,enabled_flag
50                      )
51                      values
52                      (hr_tips_s.nextval
53                      ,upper(p_filename)
54                      ,upper(p_screen)
55                      ,upper(p_field)
56                      ,upper(p_language)
57                      ,p_business_group_id
58                      ,p_text
59                      ,'Y'
60                      );
61       end if;
62    elsif (p_mode = 'NOUPDATE' or p_mode = 'DELETE') then
63    null;
64       -- only insert tips into the database if they are not already there
65       if csr_exists_tip%notfound then
66          -- insert it
67          insert into hr_tips
68                      (tip_id
69                      ,filename
70                      ,screen
71                      ,field
72                      ,language_code
73                      ,business_group_id
74                      ,text
75                      ,enabled_flag
76                      )
77                      values
78                      (hr_tips_s.nextval
79                      ,upper(p_filename)
80                      ,upper(p_screen)
81                      ,upper(p_field)
82                      ,upper(p_language)
83                      ,p_business_group_id
84                      ,p_text
85                      ,'Y'
86                      );
87       end if;
88    end if;
89 end;
90 --
91 procedure clearTips(p_filename           varchar2
92                    ,p_language           varchar2
93                    ,p_business_group_id  number    default null) is
94 begin
95    -- clear out the tips for the specified filename, language and
96    -- business group id
97    delete from hr_tips
98          where upper(filename)           = upper(p_filename)
99            and upper(language_code)      = upper(p_language)
100            and nvl(business_group_id, 0) = nvl(p_business_group_id, 0);
101 end;
102 --
103 --
104 end hr_tips_dml;