DBA Data[Home] [Help]

PACKAGE BODY: APPS.HR_COPY_GSP

Source


1 package body hr_copy_gsp as
2 /* $Header: hrcpygsp.pkb 120.0 2005/05/30 23:24:39 appldev noship $ */
3 --
4 procedure template (p_template_id       in number,
5                     p_new_name          in varchar2,
6                     p_business_group_id in number,
7                     p_copy_ru           in varchar2,
8                     p_copy_rv           in varchar2,
9                     p_copy_kt           in varchar2) is
10 --
11 cursor csr_itu is
12   select item_type_id,
13          item_type_usage_id,
14          sequence_number,
15          name
16   from   hr_summary_item_type_usage
17   where  template_id = p_template_id
18   order by sequence_number;
19 --
20 cursor csr_ktu (p_itu_id in number) is
21   select valid_key_type_id
22   from   hr_summary_key_type_usage
23   where  item_type_usage_id = p_itu_id;
24 --
25 cursor csr_ru (p_itu_id in number) is
26   select ru.restriction_type,
27          ru.restriction_usage_id,
28          ru.valid_restriction_id
29   from   hr_summary_restriction_usage ru
30   where  ru.item_type_usage_id = p_itu_id;
31 --
32 cursor csr_rv (p_id in number) is
33   select value
34   from   hr_summary_restriction_value
35   where  restriction_usage_id = p_id;
36 --
37 l_template_id           number;
38 l_itu_id                number;
39 l_restriction_usage_id  number;
40 l_object_version_number number;
41 l_id_value              number;
42 --
43 begin
44 --
45 hr_utility.set_location('Entering: copy_gsp.template', 10);
46 --
47 hr_summary_api.CREATE_TEMPLATE (p_template_id           => l_template_id
48                                ,p_business_group_id     => p_business_group_id
49                                ,p_object_version_number => l_object_version_number
50                                ,p_seeded_data           => 'N'
51                                ,p_name                  => p_new_name);
52 --
53 for r_itu in csr_itu loop
54     --
55     hr_summary_api.CREATE_ITEM_TYPE_USAGE (p_item_type_usage_id    => l_itu_id
56                                           ,p_business_group_id     => p_business_group_id
57                                           ,p_object_version_number => l_object_version_number
58                                           ,p_sequence_number       => r_itu.sequence_number
59                                           ,p_name                  => r_itu.name
60                                           ,p_seeded_data           => 'N'
61                                           ,p_template_id           => l_template_id
62                                           ,p_item_type_id          => r_itu.item_type_id);
63     --
64     if p_copy_kt = 'Y' then
65        for r_ktu in csr_ktu(r_itu.item_type_usage_id) loop
66            hr_summary_api.CREATE_KEY_TYPE_USAGE (p_key_type_usage_id     => l_id_value
67                                                 ,p_business_group_id     => p_business_group_id
68                                                 ,p_object_version_number => l_object_version_number
69                                                 ,p_seeded_data           => 'N'
70                                                 ,p_item_type_usage_id    => l_itu_id
71                                                 ,p_valid_key_type_id     => r_ktu.valid_key_type_id);
72        end loop;
73     end if;
74     --
75     if p_copy_ru = 'Y' then
76        for r_ru in csr_ru(r_itu.item_type_usage_id) loop
77            hr_summary_api.CREATE_RESTRICTION_USAGE (p_restriction_usage_id  => l_restriction_usage_id
78                                                    ,p_business_group_id     => p_business_group_id
79                                                    ,p_object_version_number => l_object_version_number
80                                                    ,p_seeded_data           => 'N'
81                                                    ,p_item_type_usage_id    => l_itu_id
82                                                    ,p_valid_restriction_id  => r_ru.valid_restriction_id
83                                                    ,p_restriction_type      => r_ru.restriction_type);
84            --
85            if p_copy_rv = 'Y' then
86               for r_rv in csr_rv(r_ru.restriction_usage_id) loop
87                   hr_summary_api.CREATE_RESTRICTION_VALUE (p_restriction_value_id  => l_id_value
88                                                           ,p_business_group_id     => p_business_group_id
89                                                           ,p_object_version_number => l_object_version_number
90                                                           ,p_seeded_data           => 'N'
91                                                           ,p_restriction_usage_id  => l_restriction_usage_id
92                                                           ,p_value                 => r_rv.value);
93               end loop;
94            end if;
95        end loop;
96     end if;
97     --
98 end loop;
99 --
100 hr_utility.set_location('Leaving: copy_gsp.template', 20);
101 --
102 commit;
103 --
104 end template;
105 --
106 procedure itu (p_itu_id            in number,
107                p_template_id       in number,
108                p_name              in varchar2,
109                p_business_group_id in number,
110                p_copy_ru           in varchar2,
111                p_copy_rv           in varchar2,
112                p_copy_kt           in varchar2) is
113 --
114 cursor csr_itu is
115   select item_type_id
116   from   hr_summary_item_type_usage
117   where  item_type_usage_id = p_itu_id;
118 --
119 cursor csr_seq is
120   select nvl(max(sequence_number),0)+1
121   from   hr_summary_item_type_usage
122   where  template_id = p_template_id;
123 --
124 cursor csr_ktu (p_itu_id in number) is
125   select valid_key_type_id
126   from   hr_summary_key_type_usage
127   where  item_type_usage_id = p_itu_id;
128 --
129 cursor csr_ru (p_itu_id in number) is
130   select ru.restriction_type,
131          ru.restriction_usage_id,
132          ru.valid_restriction_id
133   from   hr_summary_restriction_usage ru
134   where  ru.item_type_usage_id = p_itu_id;
135 --
136 cursor csr_rv (p_id in number) is
137   select value
138   from   hr_summary_restriction_value
139   where  restriction_usage_id = p_id;
140 --
141 l_item_type_id          number;
142 l_sequence_number       number;
143 l_itu_id                number;
144 l_restriction_usage_id  number;
145 l_object_version_number number;
146 l_id_value              number;
147 --
148 begin
149 --
150 hr_utility.set_location('Entering: copy_gsp.itu', 10);
151 --
152 open csr_itu;
153 fetch csr_itu into l_item_type_id;
154 close csr_itu;
155 --
156 open csr_seq;
157 fetch csr_seq into l_sequence_number;
158 close csr_seq;
159 --
160 hr_summary_api.CREATE_ITEM_TYPE_USAGE (p_item_type_usage_id    => l_itu_id
161                                       ,p_business_group_id     => p_business_group_id
162                                       ,p_object_version_number => l_object_version_number
163                                       ,p_sequence_number       => l_sequence_number
164                                       ,p_name                  => p_name
165                                       ,p_seeded_data           => 'N'
166                                       ,p_template_id           => p_template_id
167                                       ,p_item_type_id          => l_item_type_id);
168 --
169 if p_copy_kt = 'Y' then
170    for r_ktu in csr_ktu(p_itu_id) loop
171        hr_summary_api.CREATE_KEY_TYPE_USAGE (p_key_type_usage_id     => l_id_value
172                                             ,p_business_group_id     => p_business_group_id
173                                             ,p_object_version_number => l_object_version_number
174                                             ,p_seeded_data           => 'N'
175                                             ,p_item_type_usage_id    => l_itu_id
176                                             ,p_valid_key_type_id     => r_ktu.valid_key_type_id);
177    end loop;
178 end if;
179 --
180 if p_copy_ru = 'Y' then
181    for r_ru in csr_ru(p_itu_id) loop
182        hr_summary_api.CREATE_RESTRICTION_USAGE (p_restriction_usage_id  => l_restriction_usage_id
183                                                ,p_business_group_id     => p_business_group_id
184                                                ,p_object_version_number => l_object_version_number
185                                                ,p_seeded_data           => 'N'
186                                                ,p_item_type_usage_id    => l_itu_id
187                                                ,p_valid_restriction_id  => r_ru.valid_restriction_id
188                                                ,p_restriction_type      => r_ru.restriction_type);
189        --
190        if p_copy_rv = 'Y' then
191           for r_rv in csr_rv(r_ru.restriction_usage_id) loop
192               hr_summary_api.CREATE_RESTRICTION_VALUE (p_restriction_value_id  => l_id_value
193                                                       ,p_business_group_id     => p_business_group_id
194                                                       ,p_object_version_number => l_object_version_number
195                                                       ,p_seeded_data           => 'N'
196                                                       ,p_restriction_usage_id  => l_restriction_usage_id
197                                                       ,p_value                 => r_rv.value);
198           end loop;
199        end if;
200        --
201    end loop;
202 end if;
203 --
204 commit;
205 --
206 hr_utility.set_location('Leaving: copy_gsp.itu', 10);
207 --
208 end itu;
209 --
210 procedure item_type (p_item_type_id      in number,
211                      p_new_name          in varchar2,
212                      p_business_group_id in number,
213                      p_copy_vr           in varchar2,
214                      p_copy_vkt          in varchar2) is
215 --
216 cursor csr_it is
217   select name,
218          title,
219          units,
220          datatype,
221          count_clause1,
222          count_clause2,
223          where_clause
224   from   hr_summary_item_type
225   where  item_type_id = p_item_type_id;
226 --
227 cursor csr_vr is
228   select restriction_type_id
229   from   hr_summary_valid_restriction
230   where  item_type_id = p_item_type_id;
231 --
232 cursor csr_vkt is
233   select key_type_id
234   from   hr_summary_valid_key_type
235   where  item_type_id = p_item_type_id;
236 --
237 r_it                    csr_it%rowtype;
238 l_item_type_id          number;
239 l_id_value              number;
240 l_object_version_number number;
241 --
242 begin
243 --
244 hr_utility.set_location('Entering: copy_gsp.item_type', 10);
245 --
246 open csr_it;
247 fetch csr_it into r_it;
248 close csr_it;
249 --
250 hr_summary_api.CREATE_ITEM_TYPE (p_item_type_id          => l_item_type_id
251                                 ,p_business_group_id     => p_business_group_id
252                                 ,p_object_version_number => l_object_version_number
253                                 ,p_name                  => p_new_name
254                                 ,p_units                 => r_it.units
255                                 ,p_datatype              => r_it.datatype
256                                 ,p_count_clause1         => r_it.count_clause1
257                                 ,p_seeded_data           => 'N'
258                                 ,p_count_clause2         => r_it.count_clause2
259                                 ,p_where_clause          => r_it.where_clause);
260 --
261 if p_copy_vr = 'Y' then
262    for r_vr in csr_vr loop
263        hr_summary_api.CREATE_VALID_RESTRICTION (p_valid_restriction_id  => l_id_value
264                                                ,p_business_group_id     => p_business_group_id
265                                                ,p_object_version_number => l_object_version_number
266                                                ,p_seeded_data           => 'N'
267                                                ,p_item_type_id          => l_item_type_id
268                                                ,p_restriction_type_id   => r_vr.restriction_type_id);
269    end loop;
270 end if;
271 --
272 if p_copy_vkt = 'Y' then
273    for r_vkt in csr_vkt loop
274        hr_summary_api.CREATE_VALID_KEY_TYPE (p_valid_key_type_id     => l_id_value
275                                             ,p_business_group_id     => p_business_group_id
276                                             ,p_object_version_number => l_object_version_number
277                                             ,p_seeded_data           => 'N'
278                                             ,p_item_type_id          => l_item_type_id
279                                             ,p_key_type_id           => r_vkt.key_type_id);
280    end loop;
281 end if;
282 --
283 commit;
284 --
285 hr_utility.set_location('Leaving: copy_gsp.item_type', 20);
286 --
287 end item_type;
288 --
289 end hr_copy_gsp;