1 Package Body pqh_crl_bus as
2 /* $Header: pqcrlrhi.pkb 120.2 2005/12/07 13:23 srajakum noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_crl_bus.'; -- Global package name
9 --
10 -- The following two global variables are only to be
11 -- used by the return_legislation_code function.
12 --
13 g_legislation_code varchar2(150) default null;
14 g_criteria_rate_defn_id number default null;
15 g_language varchar2(4) default null;
16 --
17 -- ---------------------------------------------------------------------------
18 -- |----------------------< set_security_group_id >--------------------------|
19 -- ---------------------------------------------------------------------------
20 --
21 Procedure set_security_group_id
22 (p_criteria_rate_defn_id in number
23 ,p_associated_column1 in varchar2 default null
24 ) is
25 --
26 -- Declare cursor
27 --
28 cursor csr_sec_grp is
29 select pbg.security_group_id,
30 pbg.legislation_code
31 from per_business_groups_perf pbg
32 , pqh_criteria_rate_defn_tl crl
33 , pqh_criteria_rate_defn crd
34 where crl.criteria_rate_defn_id = p_criteria_rate_defn_id
35 and crd.criteria_rate_defn_id = crl.criteria_rate_defn_id
36 and pbg.business_group_id = crd.business_group_id;
37 --
38 -- Declare local variables
39 --
40 l_security_group_id number;
41 l_proc varchar2(72) := g_package||'set_security_group_id';
42 l_legislation_code varchar2(150);
43 --
44 begin
45 --
46 hr_utility.set_location('Entering:'|| l_proc, 10);
47 --
48 -- Ensure that all the mandatory parameter are not null
49 --
50 hr_api.mandatory_arg_error
51 (p_api_name => l_proc
52 ,p_argument => 'criteria_rate_defn_id'
53 ,p_argument_value => p_criteria_rate_defn_id
54 );
55 --
56 --
57 open csr_sec_grp;
58 fetch csr_sec_grp into l_security_group_id
59 , l_legislation_code;
60 --
61 if csr_sec_grp%notfound then
62 --
63 close csr_sec_grp;
64 --
65 -- The primary key is invalid therefore we must error
66 --
67 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
68 hr_multi_message.add
69 (p_associated_column1
70 => nvl(p_associated_column1,'CRITERIA_RATE_DEFN_ID')
71 );
72 --
73 else
74 close csr_sec_grp;
75 --
76 -- Set the security_group_id in CLIENT_INFO
77 --
78 hr_api.set_security_group_id
79 (p_security_group_id => l_security_group_id
80 );
81 --
82 -- Set the sessions legislation context in HR_SESSION_DATA
83 --
84 hr_api.set_legislation_context(l_legislation_code);
85 end if;
86 --
87 hr_utility.set_location(' Leaving:'|| l_proc, 20);
88 --
89 end set_security_group_id;
90 --
91 -- ---------------------------------------------------------------------------
92 -- |---------------------< return_legislation_code >-------------------------|
93 -- ---------------------------------------------------------------------------
94 --
95 Function return_legislation_code
96 (p_criteria_rate_defn_id in number
97 ,p_language in varchar2
98 )
99 Return Varchar2 Is
100 --
101 -- Declare cursor
102 --
103 -- EDIT_HERE In the following cursor statement add join(s) between
104 -- pqh_criteria_rate_defn_tl and PER_BUSINESS_GROUPS_PERF
105 -- so that the legislation_code for
106 -- the current business group context can be derived.
107 -- Remove this comment when the edit has been completed.
108 cursor csr_leg_code is
109 select pbg.legislation_code
110 from per_business_groups_perf pbg
111 , pqh_criteria_rate_defn_tl crl
112 -- , EDIT_HERE table_name(s) 333
113 where crl.criteria_rate_defn_id = p_criteria_rate_defn_id
114 and crl.language = p_language;
115 -- and pbg.business_group_id = EDIT_HERE 333.business_group_id;
116 --
117 -- Declare local variables
118 --
119 l_legislation_code varchar2(150);
120 l_proc varchar2(72) := g_package||'return_legislation_code';
121 --
122 Begin
123 --
124 hr_utility.set_location('Entering:'|| l_proc, 10);
125 --
126 -- Ensure that all the mandatory parameter are not null
127 --
128 hr_api.mandatory_arg_error
129 (p_api_name => l_proc
130 ,p_argument => 'criteria_rate_defn_id'
131 ,p_argument_value => p_criteria_rate_defn_id
132 );
133 --
134 --
135 if (( nvl(pqh_crl_bus.g_criteria_rate_defn_id, hr_api.g_number)
136 = p_criteria_rate_defn_id)
137 and ( nvl(pqh_crl_bus.g_language, hr_api.g_varchar2)
138 = p_language)) then
139 --
140 -- The legislation code has already been found with a previous
141 -- call to this function. Just return the value in the global
142 -- variable.
143 --
144 l_legislation_code := pqh_crl_bus.g_legislation_code;
145 hr_utility.set_location(l_proc, 20);
146 else
147 --
148 -- The ID is different to the last call to this function
149 -- or this is the first call to this function.
150 --
151 open csr_leg_code;
152 fetch csr_leg_code into l_legislation_code;
153 --
154 if csr_leg_code%notfound then
155 --
156 -- The primary key is invalid therefore we must error
157 --
158 close csr_leg_code;
159 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
160 fnd_message.raise_error;
161 end if;
162 hr_utility.set_location(l_proc,30);
163 --
164 -- Set the global variables so the values are
165 -- available for the next call to this function.
166 --
167 close csr_leg_code;
168 pqh_crl_bus.g_criteria_rate_defn_id := p_criteria_rate_defn_id;
169 pqh_crl_bus.g_language := p_language;
170 pqh_crl_bus.g_legislation_code := l_legislation_code;
171 end if;
172 hr_utility.set_location(' Leaving:'|| l_proc, 40);
173 return l_legislation_code;
174 end return_legislation_code;
175 --
176 -- ----------------------------------------------------------------------------
177 -- |-----------------------< chk_non_updateable_args >------------------------|
178 -- ----------------------------------------------------------------------------
179 -- {Start Of Comments}
180 --
181 -- Description:
182 -- This procedure is used to ensure that non updateable attributes have
183 -- not been updated. If an attribute has been updated an error is generated.
184 --
185 -- Pre Conditions:
186 -- g_old_rec has been populated with details of the values currently in
187 -- the database.
188 --
189 -- In Arguments:
190 -- p_rec has been populated with the updated values the user would like the
191 -- record set to.
192 --
193 -- Post Success:
194 -- Processing continues if all the non updateable attributes have not
195 -- changed.
196 --
197 -- Post Failure:
198 -- An application error is raised if any of the non updatable attributes
199 -- have been altered.
200 --
201 -- {End Of Comments}
202 -- ----------------------------------------------------------------------------
203 Procedure chk_non_updateable_args
204 (p_effective_date in date
205 ,p_rec in pqh_crl_shd.g_rec_type
206 ) IS
207 --
208 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
209 --
210 Begin
211 --
212 -- Only proceed with the validation if a row exists for the current
213 -- record in the HR Schema.
214 --
215 IF NOT pqh_crl_shd.api_updating
216 (p_criteria_rate_defn_id => p_rec.criteria_rate_defn_id
217 ,p_language => p_rec.language
218 ) THEN
219 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
220 fnd_message.set_token('PROCEDURE ', l_proc);
221 fnd_message.set_token('STEP ', '5');
222 fnd_message.raise_error;
223 END IF;
224 --
225 -- EDIT_HERE: Add checks to ensure non-updateable args have
226 -- not been updated.
227 --
228 End chk_non_updateable_args;
229 --
230 -- ----------------------------------------------------------------------------
231 -- |---------------------------< insert_validate >----------------------------|
232 -- ----------------------------------------------------------------------------
233 Procedure insert_validate
234 (p_effective_date in date
235 ,p_rec in pqh_crl_shd.g_rec_type
236 ) is
237 --
238 l_proc varchar2(72) := g_package||'insert_validate';
239 --
240 Begin
241 hr_utility.set_location('Entering:'||l_proc, 5);
242 --
243 -- Call all supporting business operations
244 --
245 --
246 -- EDIT_HERE: As this table does not have a mandatory business_group_id
247 -- column, ensure client_info is populated by calling a suitable
248 -- ???_???_bus.set_security_group_id procedure, or add one of the following
249 -- comments:
250 -- "-- No business group context. HR_STANDARD_LOOKUPS used for validation."
251 -- "-- CLIENT_INFO not set. No lookup validation or joins to HR_LOOKUPS."
252 --
253 -- Validate Dependent Attributes
254 --
255 --
256 hr_utility.set_location(' Leaving:'||l_proc, 10);
257 End insert_validate;
258 --
259 -- ----------------------------------------------------------------------------
260 -- |---------------------------< update_validate >----------------------------|
261 -- ----------------------------------------------------------------------------
262 Procedure update_validate
263 (p_effective_date in date
264 ,p_rec in pqh_crl_shd.g_rec_type
265 ) is
266 --
267 l_proc varchar2(72) := g_package||'update_validate';
268 --
269 Begin
270 hr_utility.set_location('Entering:'||l_proc, 5);
271 --
272 -- Call all supporting business operations
273 --
274 --
275 -- EDIT_HERE: As this table does not have a mandatory business_group_id
276 -- column, ensure client_info is populated by calling a suitable
277 -- ???_???_bus.set_security_group_id procedure, or add one of the following
278 -- comments:
279 -- "-- No business group context. HR_STANDARD_LOOKUPS used for validation."
280 -- "-- CLIENT_INFO not set. No lookup validation or joins to HR_LOOKUPS."
281 --
282 -- Validate Dependent Attributes
283 --
284 chk_non_updateable_args
285 (p_effective_date => p_effective_date
286 ,p_rec => p_rec
287 );
288 --
289 --
290 hr_utility.set_location(' Leaving:'||l_proc, 10);
291 End update_validate;
292 --
293 -- ----------------------------------------------------------------------------
294 -- |---------------------------< delete_validate >----------------------------|
295 -- ----------------------------------------------------------------------------
296 Procedure delete_validate
297 (p_rec in pqh_crl_shd.g_rec_type
298 ) is
299 --
300 l_proc varchar2(72) := g_package||'delete_validate';
301 --
302 Begin
303 hr_utility.set_location('Entering:'||l_proc, 5);
304 --
305 -- Call all supporting business operations
306 --
307 hr_utility.set_location(' Leaving:'||l_proc, 10);
308 End delete_validate;
309 --
310 end pqh_crl_bus;