DBA Data[Home] [Help]

PACKAGE BODY: APPS.PQH_CRL_BUS

Source


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;