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