1 Package per_qua_bus as
2 /* $Header: pequarhi.pkh 120.0.12010000.1 2008/07/28 05:32:25 appldev ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- |------------------------< set_security_group_id >-------------------------|
6 -- ----------------------------------------------------------------------------
7 -- {Start Of Comments}
8 --
9 -- Description:
10 -- Set the security_group_id in CLIENT_INFO for the qualification's business
11 -- group context.
12 --
13 -- Prerequisites:
14 -- None,
15 --
16 -- In Parameters:
17 -- Name Reqd Type Description
18 -- p_qualification_id Yes Number Qualification to use for
19 -- deriving the security group
20 -- context.
21 --
22 -- Post Success:
23 -- The security_group_id will be set in CLIENT_INFO.
24 --
25 -- Post Failure:
26 -- An error is raised if the qualification_id does not exist.
27 --
28 -- Access Status:
29 -- Internal Development Use Only.
30 --
31 -- {End Of Comments}
32 --
33 -- ----------------------------------------------------------------------------
34 procedure set_security_group_id
35 (
36 p_qualification_id in per_qualifications.qualification_id%TYPE
37 ,p_associated_column1 in varchar2 default null
38 );
39 --
40 -- ---------------------------------------------------------------------------
41 -- |---------------------< return_legislation_code >-------------------------|
42 -- ---------------------------------------------------------------------------
43 -- {Start Of Comments}
44 --
45 -- Description:
46 -- Return the legislation code for a specific primary key value
47 --
48 -- Prerequisites:
49 -- The primary key identified by p_qualification_id
50 -- already exists.
51 --
52 -- In Arguments:
53 -- p_qualification_id
54 --
55 --
56 -- Post Success:
57 -- The business group's legislation code will be returned.
58 --
59 -- Post Failure:
60 -- An error is raised if the value does not exist.
61 --
62 -- Access Status:
63 -- Internal Development Use Only.
64 --
65 -- {End Of Comments}
66 -- ---------------------------------------------------------------------------
67 FUNCTION return_legislation_code
68 (p_qualification_id in number
69 ) RETURN varchar2;
70 --
71 --
72 -- ----------------------------------------------------------------------------
73 -- |---------------------------< chk_awarded_date >---------------------------|
74 -- ----------------------------------------------------------------------------
75 --
76 -- Description
77 -- This procedure checks that the awarded date is after the start date and
78 -- later than or equal to the end date.
79 --
80 -- Pre-Conditions
81 -- None.
82 --
83 -- In Parameters
84 -- p_qualification_id PK
85 -- p_awarded_date status of qualification
86 -- p_object_version_number object version number
87 --
88 -- Post Success
89 -- Processing continues
90 --
91 -- Post Failure
92 -- Error raised.
93 --
94 -- Access Status
95 -- Internal table handler use only.
96 --
97 Procedure chk_awarded_date (p_qualification_id in number,
98 p_awarded_date in date,
99 p_start_date in date,
100 p_object_version_number in number);
101
102 Procedure chk_awarded_date (p_qualification_id in number,
103 p_awarded_date in date,
104 p_start_date in date,
105 p_end_date in date,
106 p_projected_completion_date in date,
107 p_object_version_number in number);
108
109 --
110 -- ----------------------------------------------------------------------------
111 -- |---------------------------< chk_fee >------------------------------------|
112 -- ----------------------------------------------------------------------------
113 --
114 -- Description
115 -- This procedure checks that the fee value is correct. If the fee has been
116 -- entered then the fee currency must lso be entered, likewise if the fee is
117 -- blank then the fee currency must also be blank.
118 --
119 -- Pre-Conditions
120 -- None.
121 --
122 -- In Parameters
123 -- p_qualification_id PK
124 -- p_fee value of fee to take qualification
125 -- p_fee_currency currency of fee
126 -- p_object_version_number object version number
127 --
128 -- Post Success
129 -- Processing continues
130 --
131 -- Post Failure
132 -- Error raised.
133 --
134 -- Access Status
135 -- Internal table handler use only.
136 --
137 Procedure chk_fee (p_qualification_id in number,
138 p_fee in number,
139 p_fee_currency in varchar2,
140 p_object_version_number in number);
141 --
142 -- ----------------------------------------------------------------------------
143 -- |---------------------------< chk_start_date >-----------------------------|
144 -- ----------------------------------------------------------------------------
145 --
146 -- Description
147 -- This procedure checks that the start date and end date are valid values.
148 -- The end_date must be after the start_date. The start and end dates must
149 -- bound all subjects taken and be within the dates of the establishment
150 -- attendance.
151 --
152 -- Bug: 1664055 Starts here.
153 -- This procedure also checks that the start date is greater than the Date of
154 -- Birth of the person if date of birth is not null. The start date can be
155 -- provided only if date of birth is not null.
156 --
157 --
158 -- Pre-Conditions
159 -- None.
160 --
161 -- In Parameters
162 -- p_qualification_id PK
163 -- p_attendance_id id of establishment attendance
164 -- p_start_date start date of qualification
165 -- p_end_date end date of qualification
166 -- p_object_version_number object version number
167 -- p_effective_date Effective date
168 -- p_person_id id of the person
169 --
170 -- Post Success
171 -- Processing continues
172 --
173 -- Post Failure
174 -- Error raised.
175 --
176 -- Access Status
177 -- Internal table handler use only.
178 --
179 Procedure chk_start_date (p_qualification_id in number,
180 p_attendance_id in number,
181 p_start_date in date,
182 p_end_date in date,
183 p_object_version_number in number,
184 -- Bug: 1664055 Starts here.
185 p_effective_date in date,
186 p_person_id in number);
187 -- Bug: 1664055 Ends here.
188 --
189 -- ----------------------------------------------------------------------------
190 -- |-----------------------< chk_projected_completion_date--------------------|
191 -- ----------------------------------------------------------------------------
192 --
193 -- Description
194 -- This procedure checks that the projected completion date is after the
195 -- start date of the qualification.
196 --
197 -- Pre-Conditions
198 -- None.
199 --
200 -- In Parameters
201 -- p_qualification_id PK
202 -- p_start_date start date of qualification
203 -- p_projected_completion_date projected completion date.
204 -- p_object_version_number object version number
205 --
206 -- Post Success
207 -- Processing continues
208 --
209 -- Post Failure
210 -- Error raised.
211 --
212 -- Access Status
213 -- Internal table handler use only.
214 --
215 Procedure chk_projected_completion_date
216 (p_qualification_id in number,
217 p_start_date in date,
218 p_projected_completion_date in date,
219 p_object_version_number in number);
220 --
221 -- ----------------------------------------------------------------------------
222 -- |-----------------------< chk_qual_overlap >-------------------------------|
223 -- ----------------------------------------------------------------------------
224 --
225 -- Description
226 -- This procedure checks that the qualification does not overlap for the
227 -- same person. The qualification is distinguished by business_group_id,
228 -- person_id, attendance_id, qualification_id and start date. The start date
229 -- must not overlap an identical qualification for the same person.
230 --
231 -- Pre-Conditions
232 -- None.
233 --
234 -- In Parameters
235 -- p_qualification_id PK
236 -- p_qualification_type_id id of related qualification type
237 -- p_person_id id of person
238 -- p_attendance_id id of related establishment attendance
239 -- p_business_group_id id of business group
240 -- p_start_date start date of qualification
241 -- p_end_date end date of qualification
242 -- p_title title of course taken
243 -- p_object_version_number object version number
244 -- p_party_id id of party -- HR/TCA merge
245 --
246 -- Post Success
247 -- Processing continues
248 --
249 -- Post Failure
250 -- Error raised.
251 --
252 -- Access Status
253 -- Internal table handler use only.
254 --
255 Procedure chk_qual_overlap (p_qualification_id in number,
256 p_qualification_type_id in number,
257 p_person_id in number,
258 p_attendance_id in number,
259 p_business_group_id in number,
260 p_start_date in date,
261 p_end_date in date,
262 p_title in varchar2,
263 p_object_version_number in number,
264 p_party_id in number default null
265 );
266 --
267 -- ----------------------------------------------------------------------------
268 -- |---------------------------< insert_validate >----------------------------|
269 -- ----------------------------------------------------------------------------
270 -- {Start Of Comments}
271 --
272 -- Description:
273 -- This procedure controls the execution of all insert business rules
274 -- validation.
275 --
276 -- Pre Conditions:
277 -- This private procedure is called from ins procedure.
278 --
279 -- In Parameters:
280 -- A Pl/Sql record structre.
281 --
282 -- Post Success:
283 -- Processing continues.
284 --
285 -- Post Failure:
286 -- If a business rules fails the error will not be handled by this procedure
287 -- unless explicity coded.
288 --
289 -- Developer Implementation Notes:
290 -- For insert, your business rules should be executed from this procedure and
291 -- should ideally (unless really necessary) just be straight procedure or
292 -- function calls. Try and avoid using conditional branching logic.
293 --
294 -- Access Status:
295 -- Internal Table Handler Use Only.
296 --
297 -- {End Of Comments}
298 -- ----------------------------------------------------------------------------
299 Procedure insert_validate(p_rec in out nocopy per_qua_shd.g_rec_type,
300 p_effective_date in date);
301 --
302 -- ----------------------------------------------------------------------------
303 -- |---------------------------< update_validate >----------------------------|
304 -- ----------------------------------------------------------------------------
305 -- {Start Of Comments}
306 --
307 -- Description:
308 -- This procedure controls the execution of all update business rules
309 -- validation.
310 --
311 -- Pre Conditions:
312 -- This private procedure is called from upd procedure.
313 --
314 -- In Parameters:
315 -- A Pl/Sql record structre.
316 --
317 -- Post Success:
318 -- Processing continues.
319 --
320 -- Post Failure:
321 -- If a business rules fails the error will not be handled by this procedure
322 -- unless explicity coded.
323 --
324 -- Developer Implementation Notes:
325 -- For update, your business rules should be executed from this procedure and
326 -- should ideally (unless really necessary) just be straight procedure or
327 -- function calls. Try and avoid using conditional branching logic.
328 --
329 -- Access Status:
330 -- Internal Table Handler Use Only.
331 --
332 -- {End Of Comments}
333 -- ----------------------------------------------------------------------------
334 Procedure update_validate(p_rec in out nocopy per_qua_shd.g_rec_type,
335 p_effective_date in date);
336 --
337 -- ----------------------------------------------------------------------------
338 -- |---------------------------< delete_validate >----------------------------|
339 -- ----------------------------------------------------------------------------
340 -- {Start Of Comments}
341 --
342 -- Description:
343 -- This procedure controls the execution of all delete business rules
344 -- validation.
345 --
346 -- Pre Conditions:
347 -- This private procedure is called from del procedure.
348 --
349 -- In Parameters:
350 -- A Pl/Sql record structre.
351 --
352 -- Post Success:
353 -- Processing continues.
354 --
355 -- Post Failure:
356 -- If a business rules fails the error will not be handled by this procedure
357 -- unless explicity coded.
358 --
359 -- Developer Implementation Notes:
360 -- For delete, your business rules should be executed from this procedure and
361 -- should ideally (unless really necessary) just be straight procedure or
362 -- function calls. Try and avoid using conditional branching logic.
363 --
364 -- Access Status:
365 -- Internal Table Handler Use Only.
366 --
367 -- {End Of Comments}
368 -- ----------------------------------------------------------------------------
369 Procedure delete_validate(p_rec in per_qua_shd .g_rec_type);
370 --
371 end per_qua_bus;