1 package body gms_encumbrance_groups_pkg as
2 /* $Header: GMSEGRPB.pls 120.1 2007/02/06 09:46:42 rshaik ship $ */
3
4 -- forward declarations
5 procedure check_submit_allowed (x_encumbrance_group in VARCHAR2,
6 x_err_code in out NOCOPY NUMBER,
7 x_return_status in out NOCOPY VARCHAR2);
8
9 procedure execute_submit (x_encumbrance_group VARCHAR2,
10 x_err_code in out NOCOPY NUMBER,
11 x_return_status in out NOCOPY VARCHAR2);
12
13
14 procedure insert_row (x_rowid in out NOCOPY VARCHAR2 ,
15 x_encumbrance_group in VARCHAR2,
16 x_last_update_date in DATE,
17 x_last_updated_by in NUMBER,
18 x_creation_date in DATE,
19 x_created_by in NUMBER,
20 x_encumbrance_group_status in VARCHAR2,
21 x_encumbrance_ending_date in DATE,
22 x_system_linkage_function in VARCHAR2,
23 x_control_count in NUMBER DEFAULT NULL,
24 x_control_total_amount in NUMBER DEFAULT NULL,
25 x_description in VARCHAR2 DEFAULT NULL,
26 x_last_update_login in NUMBER DEFAULT NULL,
27 x_transaction_source in VARCHAR2 DEFAULT NULL,
28 x_org_id in NUMBER,
29 x_request_id IN NUMBER DEFAULT NULL /* Bug 5689213 */
30 ) is
31
32 cursor c is select rowid from gms_encumbrance_groups
33 where encumbrance_group = x_encumbrance_group;
34 x_err_code NUMBER;
35 x_return_status VARCHAR2(630);
36 x_status VARCHAR2(30) := 'WORKING' ;
37
38 BEGIN
39 -- Fix start for bug : 2111317
40 If x_encumbrance_group_status = 'RELEASED'THEN
41 x_status := 'RELEASED' ;
42 end if ;
43 -- Fix end for bug : 2111317
44
45 insert into gms_encumbrance_groups (encumbrance_group,
46 last_update_date,
47 last_updated_by ,
48 creation_date,
49 created_by,
50 encumbrance_group_status_code,
51 encumbrance_ending_date,
52 system_linkage_function,
53 control_count,
54 control_total_amount,
55 description,
56 last_update_login,
57 transaction_source,
58 org_id,
59 request_id) /* Bug 5689213 */
60 values (x_encumbrance_group,
61 x_last_update_date,
62 x_last_updated_by,
63 x_creation_date,
64 x_created_by,
65 --'WORKING', -- Fix for bug : 2111317
66 x_status ,
67 x_encumbrance_ending_date, -- Fix for bug : 2111317
68 x_system_linkage_function,
69 x_control_count,
70 x_control_total_amount,
71 x_description,
72 x_last_update_login,
73 x_transaction_source ,
74 x_org_id,
75 x_request_id); /* Bug 5689213 */
76
77 open c;
78 fetch c into x_rowid;
79 if (c%notfound) then
80 raise NO_DATA_FOUND;
81 end if;
82 close c;
83
84 -- We always initially insert the row with status 'WORKING' - if
85 -- the row being inserted had status 'SUBMITTED', we then call
86 -- the 'SUBMIT' function to set the status to 'SUBMITTED'.
87 if (x_encumbrance_group_status = 'SUBMITTED') then
88 -- we bypass the error checking that submit does, since
89 -- it should already have been done at the form level
90 execute_submit (x_encumbrance_group, x_err_code, x_return_status);
91 if (x_err_code = 1) then
92 app_exception.raise_exception;
93 end if;
94 end if;
95
96 END insert_row;
97
98 procedure update_row (x_rowid in VARCHAR2,
99 x_encumbrance_group in VARCHAR2,
100 x_last_update_date in DATE,
101 x_last_updated_by in NUMBER,
102 x_encumbrance_group_status in VARCHAR2,
103 x_encumbrance_ending_date in DATE,
104 x_system_linkage_function in VARCHAR2,
105 x_control_count in NUMBER,
106 x_control_total_amount in NUMBER,
107 x_description in VARCHAR2,
108 x_last_update_login in NUMBER,
109 x_transaction_source in VARCHAR2) is
110
111 cursor c_orig_group is select * from gms_encumbrance_groups
112 where rowid = x_rowid;
113
114 x_orig_group c_orig_group%rowtype;
115 x_err_code NUMBER;
116 x_return_status VARCHAR2(630);
117
118 BEGIN
119
120 open c_orig_group;
121 fetch c_orig_group into x_orig_group;
122
123 -- update all the columns to the new values, except for the
124 -- encumbrance_group_status_code column - we call on the appropriate
125 -- submit/rework/release procedures to handle that.
126
127 update gms_encumbrance_groups
128 set encumbrance_group = x_encumbrance_group,
129 last_update_date = x_last_update_date,
130 last_updated_by = x_last_updated_by,
131 encumbrance_ending_date = x_encumbrance_ending_date,
132 system_linkage_function = x_system_linkage_function,
133 control_count = x_control_count,
134 control_total_amount = x_control_total_amount,
135 description = x_description,
136 last_update_login = x_last_update_login,
137 transaction_source = x_transaction_source
138 where rowid = x_rowid;
139
140
141 if ((x_encumbrance_group_status = 'SUBMITTED') and
142 (x_orig_group.encumbrance_group_status_code <> 'SUBMITTED')) then
143 -- submit (x_encumbrance_group, x_err_code, x_return_status);
144 -- we bypass the error checking that submit does, since
145 -- it should already have been done at the form level
146 execute_submit (x_encumbrance_group, x_err_code, x_return_status);
147 if (x_err_code = 1) then
148 app_exception.raise_exception;
149 end if;
150 end if;
151
152 if ((x_encumbrance_group_status = 'RELEASED') and
153 (x_orig_group.encumbrance_group_status_code <> 'RELEASED')) then
154 release (x_encumbrance_group, x_err_code, x_return_status);
155 if (x_err_code = 1) then
156 app_exception.raise_exception;
157 end if;
158 end if;
159
160 if ((x_encumbrance_group_status = 'WORKING') and
161 (x_orig_group.encumbrance_group_status_code <> 'WORKING')) then
162 rework (x_encumbrance_group, x_err_code, x_return_status);
163 if (x_err_code = 1) then
164 app_exception.raise_exception;
165 end if;
166 end if;
167
168
169 END update_row;
170
171 -- The delete_row table handler cascades the delete to the
172 -- encumbrances table by calling the encumbrances delete_row
173 -- table handler.
174
175 procedure delete_row (x_rowid in VARCHAR2) is
176 cursor get_group is select encumbrance_group,
177 encumbrance_group_status_code
178 from gms_encumbrance_groups
179 where rowid = x_rowid;
180 groups_rec get_group%rowtype;
181
182 BEGIN
183 open get_group;
184 fetch get_group into groups_rec;
185 -- check notfound?
186
187 if (groups_rec.encumbrance_group_status_code <> 'WORKING') then
188 fnd_message.set_name ('PA', 'PA_TR_EPE_ONLY_DEL_WORK');
189 app_exception.raise_exception;
190 end if;
191
192 -- cascade delete to encumbrances.
193 DECLARE
194 cursor expnds is select encumbrance_id from gms_encumbrances
195 where encumbrance_group = groups_rec.encumbrance_group
196 for update of encumbrance_id nowait;
197 exp_rec expnds%rowtype;
198 BEGIN
199 open expnds;
200 LOOP
201 fetch expnds into exp_rec;
202 if (expnds%notfound) then
203 exit;
204 else
205 -- 3733123 - PJ.M:B5: QA:P11:OTH: MANUAL ENC/EXP FORM CREATING ORPHAN ADLS
206 -- ---------------------------------------------------------------------
207 gms_awards_dist_pkg.delete_adls(exp_rec.encumbrance_id, NULL, 'ENC' ) ;
208
209 gms_encumbrances_pkg.delete_row (exp_rec.encumbrance_id);
210 end if;
211 END LOOP;
212
213 EXCEPTION
214 when APP_EXCEPTION.RECORD_LOCK_EXCEPTION then
215 fnd_message.set_name ('FND', 'FORM_UNABLE_TO_RESERVE_RECORD');
216 app_exception.raise_exception;
217 END;
218
219 delete from gms_encumbrance_groups
220 where rowid = x_rowid;
221
222 END delete_row;
223
224
225 -- Locks the given row in the database. Does not check if
226 -- values have changed (currently not in use).
227
228 procedure lock_row (x_rowid in VARCHAR2) is
229 dummy NUMBER;
230 BEGIN
231 select 1 into dummy
232 from gms_encumbrance_groups
233 where rowid = x_rowid
234 for update of encumbrance_group nowait;
235 EXCEPTION
236
237 when OTHERS then null; -- pragma exception init ...
238
239 END lock_row;
240
241
242 /*************************************************************************
243 * These are the procedure calls to submit, release, or rework an
244 * existing encumbrance group. They are called by the update_row
245 * table handler if the corresponding change to the status indicates
246 * one of the actions has been performed, or can be called by a user
247 * directly.
248 *************************************************************************/
249
250 -- Release an encumbrance group. Modifies all encumbrances for that
251 -- group to have status 'APPROVED'.
252
253 procedure release (x_encumbrance_group in VARCHAR2,
254 x_err_code in out NOCOPY NUMBER,
255 x_return_status in out NOCOPY VARCHAR2) is
256
257 cursor c_orig_group is select * from gms_encumbrance_groups
258 where encumbrance_group = x_encumbrance_group;
259
260 cursor lock_exps is select encumbrance_id from gms_encumbrances
261 where encumbrance_group = x_encumbrance_group
262 for update of encumbrance_status_code nowait;
263
264 x_orig_group c_orig_group%rowtype;
265 x_exps lock_exps%rowtype;
266
267 BEGIN
268
269 open c_orig_group;
270 fetch c_orig_group into x_orig_group;
271
272 if (x_orig_group.encumbrance_group_status_code <> 'SUBMITTED') then
273 x_err_code := 1;
274 x_return_status := 'Can only release Submitted group';
275 fnd_message.set_name ('PA', 'PA_TR_EPE_REL_ONLY_SUBMIT');
276 end if;
277
278 -- make sure the encumbrances are not locked before making the change
279 open lock_exps;
280 fetch lock_exps into x_exps;
281
282 -- if it reaches here, the locks succeeded.
283 update gms_encumbrance_groups
284 set encumbrance_group_status_code = 'RELEASED'
285 where encumbrance_group = x_encumbrance_group;
286
287 update gms_encumbrances
288 set encumbrance_status_code = 'APPROVED'
289 where encumbrance_group = x_encumbrance_group;
290
291 EXCEPTION
292 when APP_EXCEPTION.RECORD_LOCK_EXCEPTION then
293 x_err_code := 1;
294 x_return_status := 'Could not lock encumbrances';
295 fnd_message.set_name ('FND', 'FORM_UNABLE_TO_RESERVE_RECORD');
296 END release;
297
298
299 ----------------------------------------------------------------------------
300
301 -- Rework an encumbrance group (set its status back to 'WORKING').
302 -- Modifies all encumbrances for that group to have status 'WORKING'.
303
304 procedure rework (x_encumbrance_group in VARCHAR2,
305 x_err_code in out NOCOPY NUMBER,
306 x_return_status in out NOCOPY VARCHAR2) is
307
308 cursor c_orig_group is select * from gms_encumbrance_groups
309 where encumbrance_group = x_encumbrance_group
310 for update of encumbrance_group_status_code nowait;
311
312 cursor lock_exps is select encumbrance_id from gms_encumbrances
313 where encumbrance_group = x_encumbrance_group
314 for update of encumbrance_status_code nowait;
315
316 x_orig_group c_orig_group%rowtype;
317 x_exps lock_exps%rowtype;
318 BEGIN
319
320 open c_orig_group;
321 fetch c_orig_group into x_orig_group;
322
323 if (x_orig_group.encumbrance_group_status_code <> 'SUBMITTED') then
324 x_err_code := 1;
325 x_return_status := 'Can only rework Submitted group';
326 fnd_message.set_name ('PA', 'PA_TR_EPE_REWORK_ONLY_SUBMIT');
327 end if;
328
329 -- make sure the encumbrances are not locked before making the change
330 open lock_exps;
331 fetch lock_exps into x_exps;
332
333 -- if it reaches here, the locks succeeded.
334 update gms_encumbrance_groups
335 set encumbrance_group_status_code = 'WORKING'
336 where encumbrance_group = x_encumbrance_group;
337
338 update gms_encumbrances
339 set encumbrance_status_code = 'WORKING'
340 where encumbrance_group = x_encumbrance_group;
341
342 EXCEPTION
343 when APP_EXCEPTION.RECORD_LOCK_EXCEPTION then
344 x_err_code := 1;
345 x_return_status := 'Could not lock encumbrances';
346 fnd_message.set_name ('FND', 'FORM_UNABLE_TO_RESERVE_RECORD');
347 END rework;
348
349 ----------------------------------------------------------------------------
350
351 -- Submitting an encumbrance group is divided into two steps -
352 -- checking that the submit is allowed (there are quite a few checks
353 -- involved), and modifying all its encumbrances to have status
354 -- 'SUBMITTED'
355
356 procedure submit (x_encumbrance_group in VARCHAR2,
357 x_err_code in out NOCOPY NUMBER,
358 x_return_status in out NOCOPY VARCHAR2) is
359
360 BEGIN
361 check_submit_allowed (x_encumbrance_group, x_err_code, x_return_status);
362 if (x_err_code = 0) then
363 execute_submit (x_encumbrance_group, x_err_code, x_return_status);
364 end if;
365 END submit;
366
367
368 ----------------------------------------------------------------------------
369
370 procedure check_submit_allowed (x_encumbrance_group in VARCHAR2,
371 x_err_code in out NOCOPY NUMBER,
372 x_return_status in out NOCOPY VARCHAR2) is
373
374 cursor c_orig_group is select * from gms_encumbrance_groups
375 where encumbrance_group = x_encumbrance_group;
376
377 x_orig_group c_orig_group%rowtype;
378 BEGIN
379 x_err_code := 0;
380
381 open c_orig_group;
382 fetch c_orig_group into x_orig_group;
383
384 if (c_orig_group%notfound) then
385 x_err_code := 1;
386 x_return_status := 'group does not exist';
387 return;
388 end if;
389
390 -- Check that the group being submitted was 'WORKING' to begin with.
391 if (x_orig_group.encumbrance_group_status_code <> 'WORKING') then
392 x_err_code := 1;
393 x_return_status := 'Can only submit working';
394 fnd_message.set_name ('PA', 'PA_TR_EPE_SUBMIT_ONLY_WORK');
395 -- app_exception.raise_exception;
396 return;
397 end if;
398
399 -- Check that encumbrance items exist for the group
400 DECLARE
401 cursor count_ei is
402 select count(*) from gms_encumbrance_items
403 where encumbrance_id in
404 (select encumbrance_id from gms_encumbrances
405 where encumbrance_group = x_encumbrance_group);
406 x_count NUMBER;
407 BEGIN
408 open count_ei;
409 fetch count_ei into x_count;
410 if (x_count = 0) then
411 x_err_code := 1;
412 x_return_status := 'Exp items must exist';
413 fnd_message.set_name ('PA', 'PA_TR_EPE_SUBMIT_NO_ITEMS');
414 -- app_exception.raise_exception;
415 return;
416 end if;
417 END;
418
419 -- If control amounts were entered, make sure they match
420 -- the actual amounts.
421 DECLARE
422 cursor count_exp is
423 select count(*) from gms_encumbrances
424 where encumbrance_group = x_encumbrance_group;
425
426 cursor totals is
427 select sum(amount)
428 from gms_encumbrance_items
429 where encumbrance_id in
430 (select encumbrance_id from gms_encumbrances
431 where encumbrance_group = x_encumbrance_group);
432
433 x_count NUMBER;
434 x_total NUMBER;
435 BEGIN
436 if (x_orig_group.control_count is not null) then
437 open count_exp;
438 fetch count_exp into x_count;
439 close count_exp;
440
441 if (x_count <> x_orig_group.control_count) then
442 x_err_code := 1;
443 x_return_status := 'Control count does not match actual count';
444 fnd_message.set_name ('PA', 'PA_TR_EPE_SUBMIT_CTRL_CNT');
445 return;
446 end if;
447 end if;
448
449 if (x_orig_group.control_total_amount is not null) then
450 open totals;
451 fetch totals into x_total;
452 close totals;
453
454 if (x_total <> x_orig_group.control_total_amount) then
455 x_err_code := 1;
456 x_return_status := 'Control total does not match actual total';
457 fnd_message.set_name ('PA', 'PA_TR_EPE_SUBMIT_CTRL_AMTS');
458 return;
459 end if;
460 end if;
461
462 END;
463
464 -- Make sure no quantities of null have been entered.
465 DECLARE
466 cursor null_qty is
467 select count(*) from gms_encumbrance_items
468 where encumbrance_id in (
469 select encumbrance_id from gms_encumbrances
470 where encumbrance_group = x_encumbrance_group )
471 and amount is null ;
472 number_of_nulls NUMBER ;
473 BEGIN
474 open null_qty;
475 fetch null_qty into number_of_nulls;
476 if (number_of_nulls > 0) then
477 x_err_code := 1;
478 x_return_status := 'Expenditure items have null quantities';
479 fnd_message.set_name('PA', 'PA_TR_EPE_SUBMIT_NULL_QTY');
480 return ;
481 end if;
482 END;
483
484 END check_submit_allowed;
485
486
487 ---------------------------------------------------------------------------
488
489 procedure execute_submit (x_encumbrance_group VARCHAR2,
490 x_err_code in out NOCOPY NUMBER,
491 x_return_status in out NOCOPY VARCHAR2) is
492
493 cursor lock_exps is select encumbrance_id from gms_encumbrances
494 where encumbrance_group = x_encumbrance_group
495 for update of encumbrance_status_code nowait;
496
497 x_exps lock_exps%rowtype;
498 BEGIN
499 -- make sure the encumbrances are not locked before making the change
500 open lock_exps;
501 fetch lock_exps into x_exps;
502
503 update gms_encumbrance_groups
504 set encumbrance_group_status_code = 'SUBMITTED'
505 where encumbrance_group = x_encumbrance_group;
506
507 update gms_encumbrances
508 set encumbrance_status_code = 'SUBMITTED'
509 where encumbrance_group = x_encumbrance_group;
510
511
512 EXCEPTION
513 when APP_EXCEPTION.RECORD_LOCK_EXCEPTION then
514 x_err_code := 1;
515 x_return_status := 'Could not lock encumbrances';
516 fnd_message.set_name ('FND', 'FORM_UNABLE_TO_RESERVE_RECORD');
517 END execute_submit;
518
519 ---------------------------------------------------------------------------
520
521
522 END gms_encumbrance_groups_pkg;
[Home] [Help]