1 package body pa_expenditure_groups_pkg as
2 /* $Header: PAXTGRPB.pls 120.3 2005/08/16 02:57:34 vthakkar noship $ */
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 end if;
151
152 if ((x_expenditure_group_status = 'RELEASED') and
153 (x_orig_group.expenditure_group_status_code <> 'RELEASED')) then
154 release (x_expenditure_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_expenditure_group_status = 'WORKING') and
161 (x_orig_group.expenditure_group_status_code <> 'WORKING')) then
162 rework (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 end if;
167
168
169 END update_row;
170
171 -- The delete_row table handler cascades the delete to the
172 -- expenditures table by calling the expenditures delete_row
173 -- table handler.
174
175 procedure delete_row (x_rowid in VARCHAR2) is
176 cursor get_group is select expenditure_group,
177 expenditure_group_status_code
178 from pa_expenditure_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.expenditure_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 expenditures.
193 DECLARE
194 cursor expnds is select expenditure_id from pa_expenditures
195 where expenditure_group = groups_rec.expenditure_group
196 for update of expenditure_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 --
206 -- 3733123 - PJ.M:B5: QA:P11:OTH: MANUAL ENC/EXP FORM CREATING ORPHAN ADLS
207 -- delete award distribution lines..
208 --
209 gms_awards_dist_pkg.delete_adls(exp_rec.expenditure_id, NULL, 'EXP' ) ;
210
211 pa_expenditures_pkg.delete_row (exp_rec.expenditure_id);
212 end if;
213 END LOOP;
214
215 EXCEPTION
216 when APP_EXCEPTION.RECORD_LOCK_EXCEPTION then
217 fnd_message.set_name ('FND', 'FORM_UNABLE_TO_RESERVE_RECORD');
218 app_exception.raise_exception;
219 END;
220
221 delete from pa_expenditure_groups
222 where rowid = x_rowid;
223
224 END delete_row;
225
226
227 -- Locks the given row in the database. Does not check if
228 -- values have changed (currently not in use).
229
230 procedure lock_row (x_rowid in VARCHAR2) is
231 dummy NUMBER;
232 BEGIN
233 select 1 into dummy
234 from pa_expenditure_groups
235 where rowid = x_rowid
236 for update of expenditure_group nowait;
237 EXCEPTION
238
239 when OTHERS then null; -- pragma exception init ...
240
241 END lock_row;
242
243
244 /*************************************************************************
245 * These are the procedure calls to submit, release, or rework an
246 * existing expenditure group. They are called by the update_row
247 * table handler if the corresponding change to the status indicates
248 * one of the actions has been performed, or can be called by a user
249 * directly.
250 *************************************************************************/
251
252 -- Release an expenditure group. Modifies all expenditures for that
253 -- group to have status 'APPROVED'.
254
255 procedure release (x_expenditure_group in VARCHAR2,
256 x_err_code in out NOCOPY NUMBER,
257 x_return_status in out NOCOPY VARCHAR2) is
258
259 cursor c_orig_group is select * from pa_expenditure_groups
260 where expenditure_group = x_expenditure_group;
261
262 cursor lock_exps is select expenditure_id from pa_expenditures
263 where expenditure_group = x_expenditure_group
264 for update of expenditure_status_code nowait;
265
266 x_orig_group c_orig_group%rowtype;
267 x_exps lock_exps%rowtype;
268
269 BEGIN
270
271 open c_orig_group;
272 fetch c_orig_group into x_orig_group;
273
274 if (x_orig_group.expenditure_group_status_code <> 'SUBMITTED') then
275 x_err_code := 1;
276 x_return_status := 'Can only release Submitted group';
277 fnd_message.set_name ('PA', 'PA_TR_EPE_REL_ONLY_SUBMIT');
278 end if;
279
280 -- make sure the expenditures are not locked before making the change
281 open lock_exps;
282 fetch lock_exps into x_exps;
283
284 -- if it reaches here, the locks succeeded.
285
286 /* Bug 3754875 : Removed the literals and added bind variables.
287 update pa_expenditure_groups
288 set expenditure_group_status_code = 'RELEASED'
289 where expenditure_group = x_expenditure_group;
290 */
291
292 /* Bug 3754869 : Removed the literals and added bind variables.
293 update pa_expenditures
294 set expenditure_status_code = 'APPROVED'
295 where expenditure_group = x_expenditure_group;
296 */
297
298 /* Bug 3754869 and 3754875 */
299 update_exp ( p_expenditure_group => x_expenditure_group ,
300 p_exp_grp_status_code => 'RELEASED' ,
301 p_exp_status_code => 'APPROVED' );
302
303
304 EXCEPTION
305 when APP_EXCEPTION.RECORD_LOCK_EXCEPTION then
306 x_err_code := 1;
307 x_return_status := 'Could not lock expenditures';
308 fnd_message.set_name ('FND', 'FORM_UNABLE_TO_RESERVE_RECORD');
309 END release;
310
311
312 ----------------------------------------------------------------------------
313
314 -- Rework an expenditure group (set its status back to 'WORKING').
315 -- Modifies all expenditures for that group to have status 'WORKING'.
316
317 procedure rework (x_expenditure_group in VARCHAR2,
318 x_err_code in out NOCOPY NUMBER,
319 x_return_status in out NOCOPY VARCHAR2) is
320
321 cursor c_orig_group is select * from pa_expenditure_groups
322 where expenditure_group = x_expenditure_group
323 for update of expenditure_group_status_code nowait;
324
325 cursor lock_exps is select expenditure_id from pa_expenditures
326 where expenditure_group = x_expenditure_group
327 for update of expenditure_status_code nowait;
328
329 x_orig_group c_orig_group%rowtype;
330 x_exps lock_exps%rowtype;
331
332
333
334
335 BEGIN
336
337 open c_orig_group;
338 fetch c_orig_group into x_orig_group;
339
340 if (x_orig_group.expenditure_group_status_code <> 'SUBMITTED') then
341 x_err_code := 1;
342 x_return_status := 'Can only rework Submitted group';
343 fnd_message.set_name ('PA', 'PA_TR_EPE_REWORK_ONLY_SUBMIT');
344 end if;
345
346 -- make sure the expenditures are not locked before making the change
347 open lock_exps;
348 fetch lock_exps into x_exps;
349
350 -- if it reaches here, the locks succeeded.
351 /* Bug 3754875 : Removed the literals and added bind variables.
352 update pa_expenditure_groups
353 set expenditure_group_status_code = 'WORKING'
354 where expenditure_group = x_expenditure_group;
355 */
356
357 /* Bug 3754869 : Removed the literals and added bind variables.
358 update pa_expenditures
359 set expenditure_status_code = 'WORKING'
360 where expenditure_group = x_expenditure_group;
361 */
362
363 /* Bug 3754869 and 3754875 */
364 update_exp ( p_expenditure_group => x_expenditure_group ,
365 p_exp_grp_status_code => 'WORKING' ,
366 p_exp_status_code => 'WORKING' );
367
368
369
370 EXCEPTION
371 when APP_EXCEPTION.RECORD_LOCK_EXCEPTION then
372 x_err_code := 1;
373 x_return_status := 'Could not lock expenditures';
374 fnd_message.set_name ('FND', 'FORM_UNABLE_TO_RESERVE_RECORD');
375 END rework;
376
377 ----------------------------------------------------------------------------
378
379 -- Submitting an expenditure group is divided into two steps -
380 -- checking that the submit is allowed (there are quite a few checks
381 -- involved), and modifying all its expenditures to have status
382 -- 'SUBMITTED'
383
384 procedure submit (x_expenditure_group in VARCHAR2,
385 x_err_code in out NOCOPY NUMBER,
386 x_return_status in out NOCOPY VARCHAR2) is
387
388 BEGIN
389 check_submit_allowed (x_expenditure_group, x_err_code, x_return_status);
390 if (x_err_code = 0) then
391 execute_submit (x_expenditure_group, x_err_code, x_return_status);
392 end if;
393 END submit;
394
395
396 ----------------------------------------------------------------------------
397
398 procedure check_submit_allowed (x_expenditure_group in VARCHAR2,
399 x_err_code in out NOCOPY NUMBER,
400 x_return_status in out NOCOPY VARCHAR2) is
401
402 cursor c_orig_group is select * from pa_expenditure_groups
403 where expenditure_group = x_expenditure_group;
404
405 x_orig_group c_orig_group%rowtype;
406 BEGIN
407 x_err_code := 0;
408
409 open c_orig_group;
410 fetch c_orig_group into x_orig_group;
411
412 if (c_orig_group%notfound) then
413 x_err_code := 1;
414 x_return_status := 'group does not exist';
415 return;
416 end if;
417
418 -- Check that the group being submitted was 'WORKING' to begin with.
419 if (x_orig_group.expenditure_group_status_code <> 'WORKING') then
420 x_err_code := 1;
421 x_return_status := 'Can only submit working';
422 fnd_message.set_name ('PA', 'PA_TR_EPE_SUBMIT_ONLY_WORK');
423 -- app_exception.raise_exception;
424 return;
425 end if;
426
427 -- Check that expenditure items exist for the group
428 DECLARE
429 cursor count_ei is
430 select count(*) from pa_expenditure_items
431 where expenditure_id in
432 (select expenditure_id from pa_expenditures
433 where expenditure_group = x_expenditure_group);
434 x_count NUMBER;
435 BEGIN
436 open count_ei;
437 fetch count_ei into x_count;
438 if (x_count = 0) then
439 x_err_code := 1;
440 x_return_status := 'Exp items must exist';
441 fnd_message.set_name ('PA', 'PA_TR_EPE_SUBMIT_NO_ITEMS');
442 -- app_exception.raise_exception;
443 return;
444 end if;
445 END;
446
447 -- If control amounts were entered, make sure they match
448 -- the actual amounts.
449 DECLARE
450 cursor count_exp is
451 select count(*) from pa_expenditures
452 where expenditure_group = x_expenditure_group;
453
454 cursor totals is
455 select sum(quantity)
456 from pa_expenditure_items
457 where expenditure_id in
458 (select expenditure_id from pa_expenditures
459 where expenditure_group = x_expenditure_group);
460
461 x_count NUMBER;
462 x_total NUMBER;
463 BEGIN
464 if (x_orig_group.control_count is not null) then
465 open count_exp;
466 fetch count_exp into x_count;
467 close count_exp;
468
469 if (x_count <> x_orig_group.control_count) then
470 x_err_code := 1;
471 x_return_status := 'Control count does not match actual count';
472 fnd_message.set_name ('PA', 'PA_TR_EPE_SUBMIT_CTRL_CNT');
473 return;
474 end if;
475 end if;
476
477 if (x_orig_group.control_total_amount is not null) then
478 open totals;
479 fetch totals into x_total;
480 close totals;
481
482 if (x_total <> x_orig_group.control_total_amount) then
483 x_err_code := 1;
484 x_return_status := 'Control total does not match actual total';
485 fnd_message.set_name ('PA', 'PA_TR_EPE_SUBMIT_CTRL_AMTS');
486 return;
487 end if;
488 end if;
489
490 END;
491
492 -- Make sure no quantities of null have been entered.
493 DECLARE
494 cursor null_qty is
495 select count(*) from pa_expenditure_items
496 where expenditure_id in (
497 select expenditure_id from pa_expenditures
498 where expenditure_group = x_expenditure_group )
499 and quantity is null ;
500 number_of_nulls NUMBER ;
501 BEGIN
502 open null_qty;
503 fetch null_qty into number_of_nulls;
504 if (number_of_nulls > 0) then
505 x_err_code := 1;
506 x_return_status := 'Expenditure items have null quantities';
507 fnd_message.set_name('PA', 'PA_TR_EPE_SUBMIT_NULL_QTY');
508 return ;
509 end if;
510 END;
511
512 END check_submit_allowed;
513
514
515 ---------------------------------------------------------------------------
516
517 procedure execute_submit (x_expenditure_group VARCHAR2,
518 x_err_code in out NOCOPY NUMBER,
519 x_return_status in out NOCOPY VARCHAR2) is
520
521 cursor lock_exps is select expenditure_id from pa_expenditures
522 where expenditure_group = x_expenditure_group
523 for update of expenditure_status_code nowait;
524
525 x_exps lock_exps%rowtype;
526
527
528 BEGIN
529 -- make sure the expenditures are not locked before making the change
530 open lock_exps;
531 fetch lock_exps into x_exps;
532
533 /* Bug 3754875 : Removed the literals and added bind variables.
534 update pa_expenditure_groups
535 set expenditure_group_status_code = 'SUBMITTED'
536 where expenditure_group = x_expenditure_group;
537 */
538
539 /* Bug 3754869 : Removed the literals and added bind variables.
540 update pa_expenditures
541 set expenditure_status_code = 'SUBMITTED'
542 where expenditure_group = x_expenditure_group;
543 */
544
545 /* Bug 3754869 and 3754875 */
546 update_exp ( p_expenditure_group => x_expenditure_group ,
547 p_exp_grp_status_code => 'SUBMITTED' ,
548 p_exp_status_code => 'SUBMITTED' );
549
550
551
552 EXCEPTION
553 when APP_EXCEPTION.RECORD_LOCK_EXCEPTION then
554 x_err_code := 1;
555 x_return_status := 'Could not lock expenditures';
556 fnd_message.set_name ('FND', 'FORM_UNABLE_TO_RESERVE_RECORD');
557 END execute_submit;
558
559 ---------------------------------------------------------------------------
560 /* Bug 3754869 and 3754875 */
561 procedure update_exp (p_expenditure_group in varchar2 ,
562 p_exp_grp_status_code in Varchar2 ,
563 p_exp_status_code in Varchar2
564 )
565 Is
566
567 Begin
568
569 update pa_expenditure_groups
570 set expenditure_group_status_code = p_exp_grp_status_code
571 where expenditure_group = p_expenditure_group ;
572
573 update pa_expenditures
574 set expenditure_status_code = p_exp_status_code
575 where expenditure_group = p_expenditure_group ;
576
577 End;
578
579
580
581
582
583
584 END pa_expenditure_groups_pkg;
[Home] [Help]