1 package body pqh_corps_copy as
2 /* $Header: pqcpdcpy.pkb 115.9 2003/11/26 02:34:01 kgowripe noship $ */
3 g_package varchar2(100) := 'PQH_CORPS_COPY.' ;
4 procedure copy_corps(p_corps_definition_id in number,
5 p_effective_date in date,
6 p_name in varchar2,
7 p_nature_cd in varchar2,
8 p_business_group_id in number default null,
9 p_copy_grades in varchar2 default 'Y',
10 p_copy_docs in varchar2 default 'Y',
11 p_copy_exams in varchar2 default 'Y',
12 p_copy_training in varchar2 default 'Y',
13 p_copy_organizations in varchar2 default 'Y',
14 p_copy_others in varchar2 default 'Y',
15 p_copy_career in varchar2 default 'Y',
16 p_filere1_cd in varchar2 default 'NO',
17 p_filere2_cd in varchar2 default null,
18 p_filere3_cd in varchar2 default null,
19 p_filere4_cd in varchar2 default null,
20 p_filere5_cd in varchar2 default null,
21 p_filere6_cd in varchar2 default null,
22 p_filere7_cd in varchar2 default null,
23 p_filere8_cd in varchar2 default null,
24 p_filere9_cd in varchar2 default null,
25 p_filere10_cd in varchar2 default null
26 ) is
27 l_proc varchar2(100) := g_package||'copy_corps' ;
28 begin
29 null;
30 end copy_corps;
31 function get_step_name(p_grade_step_id in number) return varchar2 is
32 l_step_name varchar2(30);
33 begin
34 select spt.spinal_point step_name
35 into l_step_name
36 from per_spinal_point_steps_f sps, per_spinal_points spt
37 where sps.spinal_point_id = spt.spinal_point_id
38 and sps.step_id = p_grade_step_id
39 and trunc(sysdate) between sps.effective_start_date and sps.effective_end_date
40 ;
41 return l_step_name;
42 end get_step_name;
43 function get_grade_name(p_grade_step_id in number) return varchar2 is
44 l_grade_name varchar2(30);
45 begin
46 select grd.name
47 into l_grade_name
48 from per_spinal_point_steps_f sps, per_grade_spines_f gsp, per_grades_vl grd
49 where sps.grade_spine_id = gsp.grade_spine_id
50 and gsp.grade_id = grd.grade_id
51 and sps.step_id = p_grade_step_id
52 and trunc(sysdate) between sps.effective_start_date and sps.effective_end_date
53 and trunc(sysdate) between gsp.effective_start_date and gsp.effective_end_date
54 ;
55 return l_grade_name;
56 end get_grade_name;
57 function get_grade_id(p_grade_step_id in number) return number is
58 l_grade_id number;
59 begin
60 select grd.grade_id
61 into l_grade_id
62 from per_spinal_point_steps_f sps, per_grade_spines_f gsp, per_grades grd
63 where sps.grade_spine_id = gsp.grade_spine_id
64 and gsp.grade_id = grd.grade_id
65 and sps.step_id = p_grade_step_id
66 and trunc(sysdate) between sps.effective_start_date and sps.effective_end_date
67 and trunc(sysdate) between gsp.effective_start_date and gsp.effective_end_date
68 ;
69 return l_grade_id;
70 end get_grade_id;
71 function get_hier_ver return number is
72 l_hier_ver_id number;
73 l_nodes number;
74 cursor c1 is
75 select ghv.hierarchy_version_id hier_ver
76 from per_gen_hierarchy ghr, per_gen_hierarchy_versions ghv
77 where ghr.hierarchy_id = ghv.hierarchy_id
78 and ghr.type ='CAREER_PATH'
79 order by ghv.hierarchy_version_id;
80 begin
81 for i in c1 loop
82 if l_hier_ver_id is null then
83 l_hier_ver_id := i.hier_ver;
84 end if;
85 select count(*) into l_nodes
86 from per_gen_hierarchy_nodes
87 where hierarchy_version_id = i.hier_ver;
88 if l_nodes >0 then
89 l_hier_ver_id := i.hier_ver;
90 exit;
91 end if;
92 end loop;
93 return l_hier_ver_id;
94 end get_hier_ver;
95 procedure insert_career_path(p_effective_date in date,
96 p_hierarchy_version_id in number,
97 p_business_group_id in number,
98 p_from_corps_definition_id in number,
99 p_starting_grade_step_id in number,
100 p_ending_grade_step_id in number,
101 p_to_corps_definition_id in number,
102 p_from_seniority in number default null,
103 p_from_seniority_uom in varchar2 default null,
104 p_to_seniority in number default null,
105 p_to_seniority_uom in varchar2 default null,
106 p_node_id out nocopy number,
107 p_object_version_number out nocopy number) is
108 l_node_type varchar2(30) := 'CAREER_NODE';
109 l_path varchar2(30);
110 cursor csr_career_path is select 'X' from per_gen_hierarchy_nodes
111 where entity_id = to_char(p_starting_grade_step_id)
112 and information3 = p_ending_grade_step_id
113 and information4 = p_from_corps_definition_id
114 and information9 = p_to_corps_definition_id
115 and information_category = l_node_type;
116 begin
117 open csr_career_path;
118 fetch csr_career_path into l_path;
119 if csr_career_path%found then
120 close csr_career_path;
121 hr_utility.set_message(8302, 'PQH_DUPLICATE_CORPS_CAREER');
122 hr_utility.raise_error;
123 end if;
124 close csr_career_path;
125 if p_to_seniority is not null and p_to_seniority_uom is null then
126 hr_utility.set_message(8302, 'PQH_CORPS_UOM_MISSING');
127 hr_utility.raise_error;
128 end if;
129 if p_from_seniority is not null and p_from_seniority_uom is null then
130 hr_utility.set_message(8302, 'PQH_CORPS_UOM_MISSING');
131 hr_utility.raise_error;
132 end if;
133 if p_from_corps_definition_id = p_to_corps_definition_id and
134 p_starting_grade_step_id = p_ending_grade_step_id then
135 hr_utility.set_message(8302, 'PQH_CORPS_CAREER_INVALID');
136 hr_utility.raise_error;
137 end if;
138 per_pgn_ins.ins
139 (p_effective_date => p_effective_date
140 ,p_business_group_id => p_business_group_id
141 ,p_entity_id => p_starting_grade_step_id
142 ,p_hierarchy_version_id => p_hierarchy_version_id
143 ,p_node_type => l_node_type
144 ,p_seq => 40
145 ,p_information_category => l_node_type
146 ,p_information3 => p_ending_grade_step_id
147 ,p_information4 => p_from_corps_definition_id
148 ,p_information5 => p_from_seniority
149 ,p_information6 => p_from_seniority_uom
150 ,p_information7 => p_to_seniority
151 ,p_information8 => p_to_seniority_uom
152 ,p_information9 => p_to_corps_definition_id
153 ,p_hierarchy_node_id => p_node_id
154 ,p_object_version_number => p_object_version_number );
155 exception when others then
156 p_node_id := null;
157 p_object_version_number := null;
158 raise;
159 end insert_career_path;
160 procedure update_career_path(p_effective_date in date,
161 p_node_id in number,
162 p_from_corps_definition_id in number default null,
163 p_starting_grade_step_id in number default null,
164 p_ending_grade_step_id in number default null,
165 p_to_corps_definition_id in number default null,
166 p_from_seniority in number default null,
167 p_from_seniority_uom in varchar2 default null,
168 p_to_seniority in number default null,
169 p_to_seniority_uom in varchar2 default null,
170 p_object_version_number in out nocopy number
171 ) is
172 l_node_type varchar2(30) := 'CAREER_NODE';
173 l_path varchar2(30);
174 l_object_version_number number := p_object_version_number;
175 cursor csr_check is select 'X' from per_gen_hierarchy_nodes
176 where hierarchy_node_id = p_node_id
177 and object_version_number = p_object_version_number;
178 cursor csr_career_path is select 'X' from per_gen_hierarchy_nodes
179 where entity_id = to_char(p_starting_grade_step_id)
180 and information3 = p_ending_grade_step_id
181 and information4 = p_from_corps_definition_id
182 and information9 = p_to_corps_definition_id
183 and information_category = l_node_type
184 and hierarchy_node_id <> p_node_id;
185 begin
186 open csr_check;
187 fetch csr_check into l_path;
188 if csr_check%notfound then
189 close csr_check;
190 else
191 close csr_check;
192 open csr_career_path;
193 fetch csr_career_path into l_path;
194 if csr_career_path%found then
195 close csr_career_path;
196 hr_utility.set_message(8302, 'PQH_DUPLICATE_CORPS_CAREER');
197 hr_utility.raise_error;
198 end if;
199 close csr_career_path;
200 if p_to_seniority is not null and p_to_seniority_uom is null then
201 hr_utility.set_message(8302, 'PQH_CORPS_UOM_MISSING');
202 hr_utility.raise_error;
203 end if;
204 if p_from_seniority is not null and p_from_seniority_uom is null then
205 hr_utility.set_message(8302, 'PQH_CORPS_UOM_MISSING');
206 hr_utility.raise_error;
207 end if;
208 if p_from_corps_definition_id = p_to_corps_definition_id and
209 p_starting_grade_step_id = p_ending_grade_step_id then
210 hr_utility.set_message(8302, 'PQH_CORPS_CAREER_INVALID');
211 hr_utility.raise_error;
212 end if;
213 per_pgn_upd.upd
214 (p_effective_date => p_effective_date
215 ,p_entity_id => p_starting_grade_step_id
216 ,p_node_type => l_node_type
217 ,p_seq => 40
218 ,p_information_category => l_node_type
219 ,p_information3 => p_ending_grade_step_id
220 ,p_information4 => p_from_corps_definition_id
221 ,p_information5 => p_from_seniority
222 ,p_information6 => p_from_seniority_uom
223 ,p_information7 => p_to_seniority
224 ,p_information8 => p_to_seniority_uom
225 ,p_information9 => p_to_corps_definition_id
226 ,p_hierarchy_node_id => p_node_id
227 ,p_object_version_number => p_object_version_number );
228 end if;
229 exception when others then
230 p_object_version_number := l_object_version_number;
231 raise;
232 end update_career_path;
233 procedure delete_career_path(p_node_id in number,
234 p_object_version_number in number) is
235 cursor csr_career_path is select 'X' from per_gen_hierarchy_nodes
236 where hierarchy_node_id = p_node_id
237 and object_version_number = p_object_version_number;
238 l_check varchar2(30);
239 begin
240 open csr_career_path;
241 fetch csr_career_path into l_check;
242 if csr_career_path%found then
243 close csr_career_path;
244 per_pgn_del.del
245 (p_hierarchy_node_id => p_node_id
246 ,p_object_version_number => p_object_version_number
247 );
248 else
249 close csr_career_path;
250 end if;
251 end delete_career_path;
252
253 --
254 procedure add_corps_fileres(p_corps_definition_id in number,
255 p_effective_date in date,
256 p_filere1_cd in varchar2 ,
257 p_filere2_cd in varchar2 default null,
258 p_filere3_cd in varchar2 default null) is
259 begin
260 NULL;
261 end add_corps_fileres;
262 --
263 --
264 procedure delete_corps_fileres(p_corps_definition_id in number,
265 p_filere_cd in varchar2 default null) is
266 begin
267 NULL;
268 end delete_corps_fileres;
269 --
270 procedure delete_corps(p_corps_definition_id in number) is
271 begin
272 NULL;
273 end delete_corps;
274 --
275 procedure delete_corps_grade(p_corps_definition_id in number,
276 p_grade_id in number) is
277 begin
278 NULL;
279 end delete_corps_grade;
280 --
281 end pqh_corps_copy;
[Home] [Help]