[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;