[Home] [Help]
PACKAGE BODY: APPS.PQH_RLT_BUS
Source
1 Package Body pqh_rlt_bus as
2 /* $Header: pqrltrhi.pkb 115.8 2004/02/26 10:32:25 srajakum noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_rlt_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_routing_list_id >------|
12 -- ----------------------------------------------------------------------------
13 --
14 -- Description
15 -- This procedure is used to check that the primary key for the table
16 -- is created properly. It should be null on insert and
17 -- should not be able to be updated.
18 --
19 -- Pre Conditions
20 -- None.
21 --
22 -- In Parameters
23 -- routing_list_id PK of record being inserted or updated.
24 -- object_version_number Object version number of record being
25 -- inserted or updated.
26 --
27 -- Post Success
28 -- Processing continues
29 --
30 -- Post Failure
31 -- Errors handled by the procedure
32 --
33 -- Access Status
34 -- Internal table handler use only.
35 --
36 Procedure chk_routing_list_id(p_routing_list_id in number,
37 p_object_version_number in number) is
38 --
39 l_proc varchar2(72) := g_package||'chk_routing_list_id';
40 l_api_updating boolean;
41 --
42 Begin
43 --
44 hr_utility.set_location('Entering:'||l_proc, 5);
45 --
46 l_api_updating := pqh_rlt_shd.api_updating
47 (p_routing_list_id => p_routing_list_id,
48 p_object_version_number => p_object_version_number);
49 --
50 if (l_api_updating
51 and nvl(p_routing_list_id,hr_api.g_number)
52 <> pqh_rlt_shd.g_old_rec.routing_list_id) then
53 --
54 -- raise error as PK has changed
55 --
56 pqh_rlt_shd.constraint_error('PQH_ROUTING_LISTS_PK');
57 --
58 elsif not l_api_updating then
59 --
60 -- check if PK is null
61 --
62 if p_routing_list_id is not null then
63 --
64 -- raise error as PK is not null
65 --
66 pqh_rlt_shd.constraint_error('PQH_ROUTING_LISTS_PK');
67 --
68 end if;
69 --
70 end if;
71 --
72 hr_utility.set_location('Leaving:'||l_proc, 10);
73 --
74 End chk_routing_list_id;
75 --
76 -- ----------------------------------------------------------------------------
77 -- |------< chk_routing_list_name >------|
78 -- ----------------------------------------------------------------------------
79 --
80 -- Description
81 -- This procedure is used to check that the primary key for the table
82 -- is created properly. It should be null on insert and
83 -- should not be able to be updated.
84 --
85 -- Pre Conditions
86 -- None.
87 --
88 -- In Parameters
89 -- routing_list_id PK of record being inserted or updated.
90 -- routing_list_name name of the routing list
91 -- object_version_number Object version number of record being
92 -- inserted or updated.
93 --
94 -- Post Success
95 -- Processing continues
96 --
97 -- Post Failure
98 -- Errors handled by the procedure
99 --
100 -- Access Status
101 -- Internal table handler use only.
102 --
103 Procedure chk_routing_list_name(p_routing_list_id in number,
104 p_routing_list_name in varchar2,
105 p_object_version_number in number) is
106 --
107 l_proc varchar2(72) := g_package||'chk_routing_list_name';
108 l_api_updating boolean;
109 l_dummy varchar2(10);
110 --
111 cursor c1 is
112 select 'x'
113 from pqh_routing_lists
114 where nvl(routing_list_name,hr_api.g_varchar2) = nvl(p_routing_list_name,hr_api.g_varchar2);
115 Begin
116 --
117 hr_utility.set_location('Entering:'||l_proc, 5);
118 --
119 l_api_updating := pqh_rlt_shd.api_updating
120 (p_routing_list_id => p_routing_list_id,
121 p_object_version_number => p_object_version_number);
122 --
123 if ((l_api_updating
124 and nvl(p_routing_list_name,hr_api.g_varchar2)
125 <> nvl(pqh_rlt_shd.g_old_rec.routing_list_name,hr_api.g_varchar2)) or not l_api_updating )then
126
127 if p_routing_list_name is not null then
128 --
129 open c1;
130 fetch c1 into l_dummy;
131 if c1%found then
132 close c1;
133 hr_utility.set_message(8302,'PQH_ROUTING_LIST_NAME_EXISTS');
134 hr_utility.raise_error;
135 -- pqh_rlt_shd.constraint_error('PQH_ROUTING_LISTS_PK');
136 end if;
137 close c1;
138 --
139 end if;
140 --
141 end if;
142 --
143 hr_utility.set_location('Leaving:'||l_proc, 10);
144 --
145 End chk_routing_list_name;
146 --
147 -- ----------------------------------------------------------------------------
148 -- |------< chk_for_pending_txns >------|
149 -- ----------------------------------------------------------------------------
150 --
151 -- Description
152 -- This procedure is used to check that the lookup value is valid.
153 --
154 -- Pre Conditions
155 -- None.
156 --
157 -- In Parameters
158 -- routing_list_member_id PK of record being inserted or updated.
159 --
160 -- Post Success
161 -- Processing continues
162 --
163 -- Post Failure
164 -- Error handled by procedure
165 --
166 -- Access Status
167 -- Internal table handler use only.
168 --
169 Procedure chk_for_pending_txns(p_routing_list_id in number) is
170 --
171 l_name pqh_transaction_categories_vl.name%type;
172 l_proc varchar2(72) := g_package||'chk_for_pending_txns';
173 l_bus_grp_name varchar2(240);
174 l_bus_grp_id pqh_transaction_categories_vl.business_group_id%type;
175 l_api_updating boolean;
176 --
177 cursor c_txn_cats(p_routing_list_id number) is
178 select distinct transaction_category_id
179 from pqh_routing_list_members rlm, pqh_routing_categories rct
180 where rlm.routing_list_id = rct.routing_list_id
181 and rct.routing_list_id = p_routing_list_id;
182 --
183 Cursor csr_txn_cat_name(p_transaction_category_id in number) is
184 Select name,business_group_id
185 From pqh_transaction_categories_vl
186 Where transaction_category_id = p_transaction_category_id;
187 --
188 Begin
189 --
190 hr_utility.set_location('Entering:'||l_proc, 5);
191 --
192 for r_txn_cat in c_txn_cats(p_routing_list_id)
193 loop
194 --
195 if nvl(pqh_tct_bus.chk_active_transaction_exists(r_txn_cat.transaction_category_id),'N')
196 = 'Y' then
197 --
198 Open csr_txn_cat_name(p_transaction_category_id => r_txn_cat.transaction_category_id);
199 Fetch csr_txn_cat_name into l_name,l_bus_grp_id;
200 Close csr_txn_cat_name;
201 --
202 hr_utility.set_message(8302,'PQH_CANT_DEL_RL_PENDING_TXNS');
203 hr_utility.set_message_token('TRANSACTION_CATEGORY',l_name);
204 if (l_bus_grp_id is not null) then
205 l_bus_grp_name := hr_general.DECODE_ORGANIZATION(l_bus_grp_id);
206 else
207 l_bus_grp_name := hr_general.decode_lookup('PQH_TCT_SCOPE', 'GLOBAL');
208 end if;
209 --
210 hr_utility.set_message_token('BUSINESS_GROUP', l_bus_grp_name);
211 --
212 hr_utility.raise_error;
213 --
214 end if;
215 --
216 end loop;
217 --
218 hr_utility.set_location('Leaving:'||l_proc,10);
219 --
220 end chk_for_pending_txns;
221 --
222 --
223 --
224 -- ----------------------------------------------------------------------------
225 -- |---------------------------< insert_validate >----------------------------|
226 -- ----------------------------------------------------------------------------
227 Procedure insert_validate(p_rec in pqh_rlt_shd.g_rec_type) is
228 --
229 l_proc varchar2(72) := g_package||'insert_validate';
230 --
231 Begin
232 hr_utility.set_location('Entering:'||l_proc, 5);
233 --
234 -- Call all supporting business operations
235 --
236 chk_routing_list_id
237 (p_routing_list_id => p_rec.routing_list_id,
238 p_object_version_number => p_rec.object_version_number);
239 --
240 chk_routing_list_name
241 (p_routing_list_id => p_rec.routing_list_id,
242 p_routing_list_name => p_rec.routing_list_name,
243 p_object_version_number => p_rec.object_version_number);
244 --
245 --
246 --
247 hr_utility.set_location(' Leaving:'||l_proc, 10);
248 End insert_validate;
249 --
250 -- ----------------------------------------------------------------------------
251 -- |---------------------------< update_validate >----------------------------|
252 -- ----------------------------------------------------------------------------
253 Procedure update_validate(p_rec in pqh_rlt_shd.g_rec_type) is
254 --
255 l_proc varchar2(72) := g_package||'update_validate';
256 --
257 Begin
258 hr_utility.set_location('Entering:'||l_proc, 5);
259 --
260 -- Call all supporting business operations
261 --
262 chk_routing_list_id
263 (p_routing_list_id => p_rec.routing_list_id,
264 p_object_version_number => p_rec.object_version_number);
265 --
266 --
267 chk_routing_list_name
268 (p_routing_list_id => p_rec.routing_list_id,
269 p_routing_list_name => p_rec.routing_list_name,
270 p_object_version_number => p_rec.object_version_number);
271 --
272 --
273 hr_utility.set_location(' Leaving:'||l_proc, 10);
274 End update_validate;
275 --
276 -- ----------------------------------------------------------------------------
277 -- |---------------------------< delete_validate >----------------------------|
278 -- ----------------------------------------------------------------------------
279 Procedure delete_validate(p_rec in pqh_rlt_shd.g_rec_type) is
280 --
281 l_proc varchar2(72) := g_package||'delete_validate';
282 --
283 Begin
284 hr_utility.set_location('Entering:'||l_proc, 5);
285 --
286 -- Call all supporting business operations
287 --
288 chk_for_pending_txns(p_rec.routing_list_id);
289 --
290 hr_utility.set_location(' Leaving:'||l_proc, 10);
291 End delete_validate;
292 --
293 end pqh_rlt_bus;