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