1 Package Body pqh_txn_cat_documents_swi As
2 /* $Header: pqtcdswi.pkb 115.0 2003/05/11 12:45:51 svorugan noship $ */
3 --
4 -- Package variables
5 --
6 g_package varchar2(33) := 'pqh_txn_cat_documents_swi.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |------------------------< create_txn_cat_document >-----------------------|
10 -- ----------------------------------------------------------------------------
11 PROCEDURE create_txn_cat_document
12 (p_validate in number default hr_api.g_false_num
13 ,p_effective_date in date
14 ,p_document_id in number
15 ,p_transaction_category_id in number
16 ,p_type_code in varchar2
17 ,p_object_version_number out nocopy number
18 ,p_return_status out nocopy varchar2
19 ) is
20 --
21 -- Variables for API Boolean parameters
22 l_validate boolean;
23 --
24 -- Variables for IN/OUT parameters
25 --
26 -- Other variables
27 l_proc varchar2(72) := g_package ||'create_txn_cat_document';
28 Begin
29 hr_utility.set_location(' Entering:' || l_proc,10);
30 --
31 -- Issue a savepoint
32 --
33 savepoint create_txn_cat_document_swi;
34 --
35 -- Initialise Multiple Message Detection
36 --
37 hr_multi_message.enable_message_list;
38 --
39 -- Remember IN OUT parameter IN values
40 --
41 --
42 -- Convert constant values to their corresponding boolean value
43 --
44 l_validate :=
45 hr_api.constant_to_boolean
46 (p_constant_value => p_validate);
47 --
48 -- Register Surrogate ID or user key values
49 --
50 --
51 -- Call API
52 --
53 pqh_txn_cat_documents_api.create_txn_cat_document
54 (p_validate => l_validate
55 ,p_effective_date => p_effective_date
56 ,p_document_id => p_document_id
57 ,p_transaction_category_id => p_transaction_category_id
58 ,p_type_code => p_type_code
59 ,p_object_version_number => p_object_version_number
60 );
61 --
62 -- Convert API warning boolean parameter values to specific
63 -- messages and add them to Multiple Message List
64 --
65 --
66 -- Convert API non-warning boolean parameter values
67 --
68 --
69 -- Derive the API return status value based on whether
70 -- messages of any type exist in the Multiple Message List.
71 -- Also disable Multiple Message Detection.
72 --
73 p_return_status := hr_multi_message.get_return_status_disable;
74 hr_utility.set_location(' Leaving:' || l_proc,20);
75 --
76 exception
77 when hr_multi_message.error_message_exist then
78 --
79 -- Catch the Multiple Message List exception which
80 -- indicates API processing has been aborted because
81 -- at least one message exists in the list.
82 --
83 rollback to create_txn_cat_document_swi;
84 --
85 -- Reset IN OUT parameters and set OUT parameters
86 --
87 p_object_version_number := null;
88 p_return_status := hr_multi_message.get_return_status_disable;
89 hr_utility.set_location(' Leaving:' || l_proc, 30);
90 when others then
91 --
92 -- When Multiple Message Detection is enabled catch
93 -- any Application specific or other unexpected
94 -- exceptions. Adding appropriate details to the
95 -- Multiple Message List. Otherwise re-raise the
96 -- error.
97 --
98 rollback to create_txn_cat_document_swi;
99 if hr_multi_message.unexpected_error_add(l_proc) then
100 hr_utility.set_location(' Leaving:' || l_proc,40);
101 raise;
102 end if;
103 --
104 -- Reset IN OUT and set OUT parameters
105 --
106 p_object_version_number := null;
107 p_return_status := hr_multi_message.get_return_status_disable;
108 hr_utility.set_location(' Leaving:' || l_proc,50);
109 end create_txn_cat_document;
110 -- ----------------------------------------------------------------------------
111 -- |------------------------< delete_txn_cat_document >-----------------------|
112 -- ----------------------------------------------------------------------------
113 PROCEDURE delete_txn_cat_document
114 (p_validate in number default hr_api.g_false_num
115 ,p_document_id in number
116 ,p_transaction_category_id in number
117 ,p_object_version_number in number
118 ,p_return_status out nocopy varchar2
119 ) is
120 --
121 -- Variables for API Boolean parameters
122 l_validate boolean;
123 --
124 -- Variables for IN/OUT parameters
125 --
126 -- Other variables
127 l_proc varchar2(72) := g_package ||'delete_txn_cat_document';
128 Begin
129 hr_utility.set_location(' Entering:' || l_proc,10);
130 --
131 -- Issue a savepoint
132 --
133 savepoint delete_txn_cat_document_swi;
134 --
135 -- Initialise Multiple Message Detection
136 --
137 hr_multi_message.enable_message_list;
138 --
139 -- Remember IN OUT parameter IN values
140 --
141 --
142 -- Convert constant values to their corresponding boolean value
143 --
144 l_validate :=
145 hr_api.constant_to_boolean
146 (p_constant_value => p_validate);
147 --
148 -- Register Surrogate ID or user key values
149 --
150 --
151 -- Call API
152 --
153 pqh_txn_cat_documents_api.delete_txn_cat_document
154 (p_validate => l_validate
155 ,p_document_id => p_document_id
156 ,p_transaction_category_id => p_transaction_category_id
157 ,p_object_version_number => p_object_version_number
158 );
159 --
160 -- Convert API warning boolean parameter values to specific
161 -- messages and add them to Multiple Message List
162 --
163 --
164 -- Convert API non-warning boolean parameter values
165 --
166 --
167 -- Derive the API return status value based on whether
168 -- messages of any type exist in the Multiple Message List.
169 -- Also disable Multiple Message Detection.
170 --
171 p_return_status := hr_multi_message.get_return_status_disable;
172 hr_utility.set_location(' Leaving:' || l_proc,20);
173 --
174 exception
175 when hr_multi_message.error_message_exist then
176 --
177 -- Catch the Multiple Message List exception which
178 -- indicates API processing has been aborted because
179 -- at least one message exists in the list.
180 --
181 rollback to delete_txn_cat_document_swi;
182 --
183 -- Reset IN OUT parameters and set OUT parameters
184 --
185 p_return_status := hr_multi_message.get_return_status_disable;
186 hr_utility.set_location(' Leaving:' || l_proc, 30);
187 when others then
188 --
189 -- When Multiple Message Detection is enabled catch
190 -- any Application specific or other unexpected
191 -- exceptions. Adding appropriate details to the
192 -- Multiple Message List. Otherwise re-raise the
193 -- error.
194 --
195 rollback to delete_txn_cat_document_swi;
196 if hr_multi_message.unexpected_error_add(l_proc) then
197 hr_utility.set_location(' Leaving:' || l_proc,40);
198 raise;
199 end if;
200 --
201 -- Reset IN OUT and set OUT parameters
202 --
203 p_return_status := hr_multi_message.get_return_status_disable;
204 hr_utility.set_location(' Leaving:' || l_proc,50);
205 end delete_txn_cat_document;
206 --- svorugan--
207
208 -- ----------------------------------------------------------------------------
209 -- |------------------------< delete_group >-----------------------------------|
210 -- ----------------------------------------------------------------------------
211 PROCEDURE delete_group
212 (p_validate in number default hr_api.g_false_num
213 ,p_transaction_category_id in number
214 ,p_object_version_number in number
215 ,p_effective_date in date default sysdate
216 ,p_return_status out nocopy varchar2
217 ) is
218 --
219 -- Variables for API Boolean parameters
220 l_validate boolean;
221 --
222 -- Variables for IN/OUT parameters
223 --
224 -- Other variables
225 l_proc varchar2(72) := g_package ||'delete_group';
226 ---
227 ---
228 Cursor group_document_csr is
229 Select document_id , object_version_number
230 from pqh_txn_category_documents
231 where transaction_category_id = p_transaction_category_id;
232
233 Begin
234 hr_utility.set_location(' Entering:' || l_proc,10);
235 --
236 -- Issue a savepoint
237 --
238 savepoint delete_txn_cat_document_swi;
239 --
240 -- Initialise Multiple Message Detection
241 --
242 hr_multi_message.enable_message_list;
243 --
244 -- Remember IN OUT parameter IN values
245 --
246 --
247 -- Convert constant values to their corresponding boolean value
248 --
249 l_validate :=
250 hr_api.constant_to_boolean
251 (p_constant_value => p_validate);
252 --
253 -- Register Surrogate ID or user key values
254 --
255 --
256 -- Call API for Child Records
257 --
258 for l_rec in group_document_csr
259 loop
260 --
261 pqh_txn_cat_documents_api.delete_txn_cat_document
262 (p_validate => l_validate
263 ,p_document_id => l_rec.document_id
264 ,p_transaction_category_id => p_transaction_category_id
265 ,p_object_version_number => l_rec.object_version_number
266 );
267 --
268 end loop;
269
270 --
271 -- Call API
272 --
273 pqh_tran_category_api.delete_tran_category
274 (p_validate => l_validate
275 ,p_transaction_category_id => p_transaction_category_id
276 ,p_object_version_number => p_object_version_number
277 ,p_effective_date => trunc(p_effective_date)
278 );
279 --
280 -- Convert API warning boolean parameter values to specific
281 -- messages and add them to Multiple Message List
282 --
283 --
284 -- Convert API non-warning boolean parameter values
285 --
286 --
287 -- Derive the API return status value based on whether
288 -- messages of any type exist in the Multiple Message List.
289 -- Also disable Multiple Message Detection.
290 --
291 p_return_status := hr_multi_message.get_return_status_disable;
292 hr_utility.set_location(' Leaving:' || l_proc,20);
293 --
294 exception
295 when hr_multi_message.error_message_exist then
296 --
297 -- Catch the Multiple Message List exception which
298 -- indicates API processing has been aborted because
299 -- at least one message exists in the list.
300 --
301 rollback to delete_txn_cat_document_swi;
302 --
303 -- Reset IN OUT parameters and set OUT parameters
304 --
305 p_return_status := hr_multi_message.get_return_status_disable;
306 hr_utility.set_location(' Leaving:' || l_proc, 30);
307 when others then
308 --
309 -- When Multiple Message Detection is enabled catch
310 -- any Application specific or other unexpected
311 -- exceptions. Adding appropriate details to the
312 -- Multiple Message List. Otherwise re-raise the
313 -- error.
314 --
315 rollback to delete_txn_cat_document_swi;
316 if hr_multi_message.unexpected_error_add(l_proc) then
317 hr_utility.set_location(' Leaving:' || l_proc,40);
318 raise;
319 end if;
320 --
321 -- Reset IN OUT and set OUT parameters
322 --
323 p_return_status := hr_multi_message.get_return_status_disable;
324 hr_utility.set_location(' Leaving:' || l_proc,50);
325 end delete_group;
326 -- ----------------------------------------------------------------------------
327
328
329 -- svorugan --
330 -- ----------------------------------------------------------------------------
331 -- |------------------------< update_txn_cat_document >-----------------------|
332 -- ----------------------------------------------------------------------------
333 PROCEDURE update_txn_cat_document
334 (p_validate in number default hr_api.g_false_num
335 ,p_effective_date in date
336 ,p_document_id in number
337 ,p_transaction_category_id in number
338 ,p_type_code in varchar2 default hr_api.g_varchar2
339 ,p_object_version_number in out nocopy number
340 ,p_return_status out nocopy varchar2
341 ) is
342 --
343 -- Variables for API Boolean parameters
344 l_validate boolean;
345 --
346 -- Variables for IN/OUT parameters
347 l_object_version_number number;
348 --
349 -- Other variables
350 l_proc varchar2(72) := g_package ||'update_txn_cat_document';
351 Begin
352 hr_utility.set_location(' Entering:' || l_proc,10);
353 --
354 -- Issue a savepoint
355 --
356 savepoint update_txn_cat_document_swi;
357 --
358 -- Initialise Multiple Message Detection
359 --
360 hr_multi_message.enable_message_list;
361 --
362 -- Remember IN OUT parameter IN values
363 --
364 l_object_version_number := p_object_version_number;
365 --
366 -- Convert constant values to their corresponding boolean value
367 --
368 l_validate :=
369 hr_api.constant_to_boolean
370 (p_constant_value => p_validate);
371 --
372 -- Register Surrogate ID or user key values
373 --
374 --
375 -- Call API
376 --
377 pqh_txn_cat_documents_api.update_txn_cat_document
378 (p_validate => l_validate
379 ,p_effective_date => p_effective_date
380 ,p_document_id => p_document_id
381 ,p_transaction_category_id => p_transaction_category_id
382 ,p_type_code => p_type_code
383 ,p_object_version_number => p_object_version_number
384 );
385 --
386 -- Convert API warning boolean parameter values to specific
387 -- messages and add them to Multiple Message List
388 --
389 --
390 -- Convert API non-warning boolean parameter values
391 --
392 --
393 -- Derive the API return status value based on whether
394 -- messages of any type exist in the Multiple Message List.
395 -- Also disable Multiple Message Detection.
396 --
397 p_return_status := hr_multi_message.get_return_status_disable;
398 hr_utility.set_location(' Leaving:' || l_proc,20);
399 --
400 exception
401 when hr_multi_message.error_message_exist then
402 --
403 -- Catch the Multiple Message List exception which
404 -- indicates API processing has been aborted because
405 -- at least one message exists in the list.
406 --
407 rollback to update_txn_cat_document_swi;
408 --
409 -- Reset IN OUT parameters and set OUT parameters
410 --
411 p_object_version_number := l_object_version_number;
412 p_return_status := hr_multi_message.get_return_status_disable;
413 hr_utility.set_location(' Leaving:' || l_proc, 30);
414 when others then
415 --
416 -- When Multiple Message Detection is enabled catch
417 -- any Application specific or other unexpected
418 -- exceptions. Adding appropriate details to the
419 -- Multiple Message List. Otherwise re-raise the
420 -- error.
421 --
422 rollback to update_txn_cat_document_swi;
423 if hr_multi_message.unexpected_error_add(l_proc) then
424 hr_utility.set_location(' Leaving:' || l_proc,40);
425 raise;
426 end if;
427 --
428 -- Reset IN OUT and set OUT parameters
429 --
430 p_object_version_number := l_object_version_number;
431 p_return_status := hr_multi_message.get_return_status_disable;
432 hr_utility.set_location(' Leaving:' || l_proc,50);
433 end update_txn_cat_document;
434 end pqh_txn_cat_documents_swi;
[Home] [Help]