1 Package per_phn_bus as
2 /* $Header: pephnrhi.pkh 120.0 2005/05/31 14:21:23 appldev noship $ */
3 --
4 --
5 -- The following two global variables are only to be
6 -- used by the return_legislation_code function.
7 --
8 g_legislation_code varchar2(150) default null;
9 g_phone_id number default null;
10 --
11 -- ---------------------------------------------------------------------------
12 -- |----------------------------< chk_date_from >----------------------------|
13 -- ---------------------------------------------------------------------------
14 --
15 -- Desciption :
16 --
17 -- DATE_FROM is mandatory
18 -- DATE_FROM must be less than DATE_TO
19 --
20 -- Pre-conditions :
21 -- Format for date_from and date_to must be correct
22 --
23 -- In Arguments :
24 -- p_phone_id
25 -- p_date_from
26 -- p_date_to
27 -- p_object_version_number
28 --
29 -- Post Success :
30 -- Processing continues
31 --
32 -- Post Failure :
33 -- An application error will be raised and processing is
34 -- terminated
35 --
36 -- Access Status :
37 -- Internal Table Handler Use only.
38 --
39 -- {End of Comments}
40 --
41 -- ---------------------------------------------------------------------------
42 procedure chk_date_from
43 (p_phone_id in per_phones.phone_id%TYPE
44 ,p_date_from in per_phones.date_from%TYPE
45 ,p_date_to in per_phones.date_to%TYPE
46 ,p_object_version_number in per_phones.object_version_number%TYPE
47 );
48 --
49 -- ---------------------------------------------------------------------------
50 -- |-------------------------< chk_phone_type >---------------------------|
51 -- ---------------------------------------------------------------------------
52 --
53 -- Description:
54 -- Validates that an phone type exists in table hr_lookups
55 -- where lookup_type is 'PHONE_TYPE' and enabled_flag is 'Y' and
56 -- effective_date is between the active dates (if they are not null).
57 -- Phone type is mandatory.
58 -- Phone number is mandatory.
59 --
60 -- Pre-conditions:
61 -- Effective_date must be valid.
62 --
63 -- In Arguments:
64 -- p_phone_id
65 -- p_phone_type
66 -- p_phone_number
67 -- p_effective_date
68 -- p_object_version_number
69 --
70 -- Post Success:
71 -- If a row does exist in hr_lookups for the given phone code then
72 -- processing continues.
73 --
74 -- Post Failure:
75 -- If a row does not exist in hr_lookups for the given phone code then
76 -- an application error will be raised and processing is terminated.
77 --
78 -- Access Status:
79 -- Internal Table Handler Use Only.
80 --
81 -- {End Of Comments}
82 -- ----------------------------------------------------------------------------
83 procedure chk_phone_type
84 (p_phone_id in per_phones.phone_id%TYPE
85 ,p_phone_type in per_phones.phone_type%TYPE
86 ,p_phone_number in per_phones.phone_number%TYPE
87 ,p_effective_date in date
88 ,p_object_version_number in per_phones.object_version_number%TYPE
89 );
90 --
91 -- ---------------------------------------------------------------------------
92 -- |-------------------------< chk_phone_type_limits >---------------------|
93 -- ---------------------------------------------------------------------------
94 --
95 -- Description:
96 -- Only allow one primary home and one primary work per person at a given
97 -- time.
98 --
99 -- Pre-conditions:
100 --
101 -- In Arguments:
102 -- p_phone_id
103 -- p_date_from
104 -- p_date_to
105 -- p_phone_type
106 -- p_parent_id
107 -- p_parent_table
108 -- p_party_id -- HR/TCA merge
109 -- p_object_version_number
110 --
111 -- Post Success:
112 -- Processing continues.
113 --
114 -- Post Failure:
115 -- An application error will be raised and processing is terminated.
116 --
117 -- Access Status:
118 -- Internal Table Handler Use Only.
119 --
120 -- {End Of Comments}
121 -- ----------------------------------------------------------------------------
122 procedure chk_phone_type_limits
123 (p_phone_id in per_phones.phone_id%TYPE
124 ,p_date_from in per_phones.date_from%TYPE
125 ,p_date_to in per_phones.date_to%TYPE
126 ,p_phone_type in per_phones.phone_type%TYPE
127 ,p_parent_id in per_phones.parent_id%TYPE
128 ,p_parent_table in per_phones.parent_table%TYPE
129 ,p_party_id in per_phones.party_id%TYPE -- HR/TCA merge
130 ,p_object_version_number in per_phones.object_version_number%TYPE);
131 --
132 -- ---------------------------------------------------------------------------
133 -- |----------------------------< chk_parent_id >----------------------------|
134 -- ---------------------------------------------------------------------------
135 --
136 -- Desciption :
137 --
138 -- If PARENT_TABLE = 'PER_ALL_PEOPLE_F', verify that the value in PARENT_ID
139 -- is in the per_all_people_f table. This is just a temporary solution which
140 -- will require re-thinking when new parent tables are added because we
141 -- probably dont want to hard code all these.
142 --
143 -- Pre-conditions :
144 --
145 --
146 -- In Arguments :
147 -- p_phone_id
148 -- p_parent_id
149 -- p_parent_table
150 -- p_object_version_number
151 --
152 -- Post Success :
153 -- Processing continues
154 --
155 -- Post Failure :
156 -- An application error will be raised and processing is
157 -- terminated
158 --
159 -- Access Status :
160 -- Internal Table Handler Use only.
161 --
162 -- {End of Comments}
163 --
164 -- ---------------------------------------------------------------------------
165 procedure chk_parent_id
166 (p_phone_id in per_phones.phone_id%TYPE
167 ,p_parent_id in per_phones.parent_id%TYPE
168 ,p_parent_table in per_phones.parent_table%TYPE
169 ,p_object_version_number in per_phones.object_version_number%TYPE
170 );
171 --
172 -- ----------------------------------------------------------------------------
173 -- |---------------------------< insert_validate >----------------------------|
174 -- ----------------------------------------------------------------------------
175 -- {Start Of Comments}
176 --
177 -- Description:
178 -- This procedure controls the execution of all insert business rules
179 -- validation.
180 --
181 -- Pre Conditions:
182 -- This private procedure is called from ins procedure.
183 --
184 -- In Parameters:
185 -- A Pl/Sql record structre.
186 --
187 -- Post Success:
188 -- Processing continues.
189 --
190 -- Post Failure:
191 -- If a business rules fails the error will not be handled by this procedure
192 -- unless explicity coded.
193 --
194 -- Developer Implementation Notes:
195 -- For insert, your business rules should be executed from this procedure and
196 -- should ideally (unless really necessary) just be straight procedure or
197 -- function calls. Try and avoid using conditional branching logic.
198 --
199 -- Access Status:
200 -- Internal Table Handler Use Only.
201 --
202 -- {End Of Comments}
203 -- ----------------------------------------------------------------------------
204 Procedure insert_validate(p_rec in out nocopy per_phn_shd.g_rec_type
205 ,p_effective_date in date
206 );
207 --
208 -- ----------------------------------------------------------------------------
209 -- |---------------------------< update_validate >----------------------------|
210 -- ----------------------------------------------------------------------------
211 -- {Start Of Comments}
212 --
213 -- Description:
214 -- This procedure controls the execution of all update business rules
215 -- validation.
216 --
217 -- Pre Conditions:
218 -- This private procedure is called from upd procedure.
219 --
220 -- In Parameters:
221 -- A Pl/Sql record structre.
222 --
223 -- Post Success:
224 -- Processing continues.
225 --
226 -- Post Failure:
227 -- If a business rules fails the error will not be handled by this procedure
228 -- unless explicity coded.
229 --
230 -- Developer Implementation Notes:
231 -- For update, your business rules should be executed from this procedure and
232 -- should ideally (unless really necessary) just be straight procedure or
233 -- function calls. Try and avoid using conditional branching logic.
234 --
235 -- Access Status:
236 -- Internal Table Handler Use Only.
237 --
238 -- {End Of Comments}
239 -- ----------------------------------------------------------------------------
240 Procedure update_validate(p_rec in per_phn_shd.g_rec_type
241 ,p_effective_date in date
242 );
243 --
244 -- ----------------------------------------------------------------------------
245 -- |---------------------------< delete_validate >----------------------------|
246 -- ----------------------------------------------------------------------------
247 -- {Start Of Comments}
248 --
249 -- Description:
250 -- This procedure controls the execution of all delete business rules
251 -- validation.
252 --
253 -- Pre Conditions:
254 -- This private procedure is called from del procedure.
255 --
256 -- In Parameters:
257 -- A Pl/Sql record structre.
258 --
259 -- Post Success:
260 -- Processing continues.
261 --
262 -- Post Failure:
263 -- If a business rules fails the error will not be handled by this procedure
264 -- unless explicity coded.
265 --
266 -- Developer Implementation Notes:
267 -- For delete, your business rules should be executed from this procedure and
268 -- should ideally (unless really necessary) just be straight procedure or
269 -- function calls. Try and avoid using conditional branching logic.
270 --
271 -- Access Status:
272 -- Internal Table Handler Use Only.
273 --
274 -- {End Of Comments}
275 -- ----------------------------------------------------------------------------
276 Procedure delete_validate(p_rec in per_phn_shd.g_rec_type);
277 --
278 --
279 -- ---------------------------------------------------------------------------
280 -- |---------------------< return_legislation_code >-------------------------|
281 -- ---------------------------------------------------------------------------
282 -- {Start Of Comments}
283 --
284 -- Description:
285 -- This procedure returns the business group id of the parent row.
286 --
287 -- Pre Conditions:
288 -- That the phone row has been created.
289 --
290 -- In Parameters:
291 -- Primary key for the phones table.
292 --
293 -- Post Success:
294 -- Processing continues.
295 --
296 -- Post Failure:
297 -- An error is raised.
298 --
299 -- Developer Implementation Notes:
300 -- This return_legislation_code function is slightly different from others in
301 -- that the cursor does a join on the parent table (in this case the parent
302 -- table is always PER_ALL_PEOPLE_F and retrieves the business_group_id from
303 -- there.
304 --
305 -- Access Status:
306 -- Public
307 --
308 -- {End Of Comments}
309 -- ----------------------------------------------------------------------------
310 --
311 function return_legislation_code
312 (p_phone_id in number
313 ) return varchar2;
314 --
315
316 function return_legislation_parent
317 (p_parent_id in number
318 ,p_parent_table in varchar2
319 ) return varchar2;
320
321 end per_phn_bus;