1 PACKAGE BODY GMS_EXP_ADJUSTMENT_PKG AS
2 --$Header: gmsrevab.pls 120.1 2007/02/06 09:52:54 rshaik ship $
3
4 -- ============================================================================================================
5 -- This procedure will be called from PAXTREPE (Expenditure Entry form ) while an expenditure batch is Copied.
6 -- This will retrive all the expenditure_items for a perticular batch and insert into ADL table .
7 -- ============================================================================================================
8 PROCEDURE GMS_EXP_COPY(X_NEW_GROUP IN VARCHAR2, X_ORG_GROUP IN VARCHAR2, P_OUTCOME IN OUT NOCOPY VARCHAR2 ) IS
9 x_adl_rec gms_award_distributions%ROWTYPE;
10 x_award_id NUMBER ;
11 x_project_id NUMBER ;
12 x_count NUMBER ;
13 x_expenditure_type VARCHAR2(30) ;
14 x_task_id NUMBER ;
15 x_quantity NUMBER ;
16
17 -- ===================================================================================================
18 -- This cursor verifies whether any duplicate records are there in gms_award_distributions table
19 -- before copying the items by comparing the exp_type,task_id and quantity of origional and new items.
20 -- This is done as there is no link between the new and origional item. If a duplicate record is
21 -- found in ADL table then this expenditure item is deleted from pa_expenditure_items_all
22 -- table otherwise adl is created .
23 -- ===================================================================================================
24
25 CURSOR NEW_ITEMS (x_new_group VARCHAR2 ) IS
26 select e1.expenditure_item_id,
27 e1.expenditure_type,
28 e1.expenditure_item_date ,
29 e1.task_id ,
30 e1.quantity ,
31 e1.creation_date ,
32 e1.created_by ,
33 e1.last_update_date ,
34 e1.last_updated_by ,
35 e1.last_update_login
36 from pa_expenditure_items_all e1
37 ,pa_expenditures_all e2
38
39 where e2.expenditure_group = x_new_group
40 and e2.expenditure_id = e1.expenditure_id
41 and e1.expenditure_type = x_expenditure_type
42 and e1.task_id = x_task_id
43 and e1.quantity = x_quantity
44 and not exists (select 'X' from gms_award_distributions gad
45 where gad.expenditure_item_id = e1.expenditure_item_id
46 and gad.adl_status = 'A'
47 and gad.document_type ='EXP'
48 )
49 order by e1.expenditure_item_id asc ;
50
51
52 CURSOR ORG_ITEMS (x_org_group VARCHAR2 ) IS
53 select e1.expenditure_item_id,
54 e1.expenditure_type,
55 e1.expenditure_item_date ,
56 e1.quantity,
57 e1.task_id ,
58 e1.creation_date ,
59 e1.created_by ,
60 e1.last_update_date ,
61 e1.last_updated_by ,
62 e1.last_update_login
63
64 from pa_expenditure_items_all e1
65 ,pa_expenditures_all e2
66 ,pa_expenditure_groups_all e3
67 where e3.expenditure_group = x_org_group
68 and e3.expenditure_group = e2.expenditure_group
69 and e2.expenditure_id = e1.expenditure_id
70 order by e1.expenditure_item_id asc;
71
72 BEGIN
73
74 FOR org_rec IN ORG_ITEMS (x_org_group ) LOOP
75
76 x_expenditure_type := org_rec.expenditure_type ;
77 x_task_id := org_rec.task_id ;
78 x_quantity := org_rec.quantity ;
79
80 FOR new_rec IN NEW_ITEMS (x_new_group ) LOOP
81
82 IF NEW_ITEMS%FOUND THEN
83
84 -- Fix starts for bug number : 1708977
85 select project_id into x_project_id
86 from pa_tasks
87 where task_id = x_task_id ;
88
89 IF GMS_PA_API.IS_SPONSORED_PROJECT(x_project_id ) then
90 -- Fix ends for bug number : 1708977
91
92 Begin
93 select distinct(adl.project_id), adl.award_id into x_project_id , x_award_id
94 from gms_award_distributions adl
95 where adl.expenditure_item_id = org_rec.expenditure_item_id
96 and adl.document_type = 'EXP'
97 and adl.adl_status = 'A' ;
98 Exception
99 WHEN others then
100 Raise ;
101 END ;
102
103 gms_transactions_pub.validate_transaction (x_project_id ,
104 new_rec.task_id,
105 x_award_id ,
106 new_rec.expenditure_type ,
107 new_rec.expenditure_item_date ,
108 'EXP',
109 p_outcome ) ;
110
111 -- ==========================================================================
112 -- Here we rollback if outcome is not null . So the records inserted by PA in
113 -- expenditures and ITEMS table will de deleted because of the rollback .
114 -- ==========================================================================
115 If p_outcome is NOT NULL THEN
116 rollback ;
117 return ;
118 End if;
119
120 x_adl_rec.award_id := x_award_id ;
121 x_adl_rec.adl_line_num := 1;
122 x_adl_rec.project_id := x_project_id ;
123 x_adl_rec.task_id := new_rec.task_id ;
124 x_adl_rec.distribution_value := 100 ;
125 x_adl_rec.adl_status := 'A' ;
126 x_adl_rec.line_type := 'R' ;
127 x_adl_rec.document_type := 'EXP' ;
128 x_adl_rec.award_set_id := gms_awards_dist_pkg.get_award_set_id;
129 x_adl_rec.expenditure_item_id := new_rec.expenditure_item_id;
130 x_adl_rec.billed_flag := 'N' ;
131 x_adl_rec.last_update_date := new_rec.last_update_date;
132 x_adl_rec.creation_date := new_rec.creation_date;
133 x_adl_rec.last_updated_by := new_rec.last_updated_by;
134 x_adl_rec.created_by := new_rec.created_by;
135 x_adl_rec.last_update_login := new_rec.last_update_login;
136
137 gms_awards_dist_pkg.create_adls(x_adl_rec);
138 EXIT ; -- Fix for bug : 2642650
139 -- Fix for bug number: 1708977
140 END IF ; -- if for IF GMS_PA_API.IS_SPONSORED_PROJECT(x_project_id ) Check
141
142 ELSE
143
144 DELETE from pa_expenditure_items_all
145 WHERE expenditure_item_id = new_rec.expenditure_item_id ;
146
147 END IF ; -- End if for IF NEW_ITEMS%FOUND check
148
149 END LOOP; -- For New_Items cursor
150
151 END LOOP ; -- For ORG_ITEMS cursor
152
153 EXCEPTION
154 when others then
155 raise ;
156 END GMS_EXP_COPY ;
157 -- ============================================================================================================
158 -- This procedure will be called from PAXTREPE (Expenditure Entry form ) while an expenditure batch is Reversed.
159 -- This will retrive all the expenditure_items for a perticular batch and insert into ADL table .
160 -- ============================================================================================================
161 PROCEDURE GMS_EXP_REVERSE(X_REVERSE_GROUP IN VARCHAR2) IS
162 x_adl_rec gms_award_distributions%ROWTYPE;
163 x_award_id NUMBER ;
164 x_project_id NUMBER ;
165 CURSOR EXP_ITEMS (x_reverse_group VARCHAR2 ) IS
166 select e1.expenditure_item_id,
167 -- e1.project_id ,
168 e1.task_id ,
169 e1.creation_date ,
170 e1.created_by ,
171 e1.last_update_date ,
172 e1.last_updated_by ,
173 e1.last_update_login ,
174 e1.adjusted_expenditure_item_id
175
176 from pa_expenditure_items_all e1
177 ,pa_expenditures_all e2
178
179 where e2.expenditure_group = x_reverse_group
180 and e2.expenditure_id = e1.expenditure_id ;
181 BEGIN
182
183 FOR rev_rec IN EXP_ITEMS (x_reverse_group ) LOOP
184 -- Fix starts for bug number : 1708977
185 select project_id into x_project_id
186 from pa_tasks
187 where task_id = rev_rec.task_id ;
188 IF GMS_PA_API.IS_SPONSORED_PROJECT(x_project_id ) then
189 -- Fix ends for bug number : 1708977
190
191 begin
192 select distinct(project_id), award_id into x_project_id ,x_award_id
193 from gms_award_distributions adl
194 where expenditure_item_id = rev_rec.adjusted_expenditure_item_id
195 and adl.document_type = 'EXP'
196 and adl.adl_status = 'A' ;
197 exception
198 when others then
199 Raise;
200 end ;
201 -- Chage made for bug fix : 1708977
202 -- IF GMS_PA_XFACE.GMS_IS_SPON_PROJECT (x_project_id ) THEN
203 x_adl_rec.award_id := x_award_id ;
204 x_adl_rec.adl_line_num := 1;
205 x_adl_rec.project_id := x_project_id ;
206 x_adl_rec.task_id := rev_rec.task_id ;
207 x_adl_rec.distribution_value := 100 ;
208 x_adl_rec.adl_status := 'A' ;
209 x_adl_rec.line_type := 'R' ;
210 x_adl_rec.document_type := 'EXP' ;
211 x_adl_rec.award_set_id := gms_awards_dist_pkg.get_award_set_id;
212 x_adl_rec.expenditure_item_id := rev_rec.expenditure_item_id;
213 x_adl_rec.billed_flag := 'N' ;
214 x_adl_rec.bill_hold_flag := 'N' ;
215 x_adl_rec.last_update_date := rev_rec.last_update_date;
216 x_adl_rec.creation_date := rev_rec.creation_date;
217 x_adl_rec.last_updated_by := rev_rec.last_updated_by;
218 x_adl_rec.created_by := rev_rec.created_by;
219 x_adl_rec.last_update_login := rev_rec.last_update_login;
220
221 gms_awards_dist_pkg.create_adls(x_adl_rec);
222
223 END IF ;
224 END LOOP;
225
226 EXCEPTION
227 when others then
228 raise ;
229 END GMS_EXP_REVERSE;
230
231
232 -- ============================================================================================================
233 -- This procedure will be called from GMSTRENE(Encumbrance Entry form ) while an encumbrance batch is copied.
234 -- This will retrive all the encumbrance_items for a perticular batch and insert into ADL table .
235 -- ============================================================================================================
236 PROCEDURE GMS_ENC_COPY(X_NEW_GROUP IN VARCHAR2, X_ORG_GROUP IN VARCHAR2 , P_OUTCOME IN OUT NOCOPY VARCHAR2 ) IS
237 x_adl_rec gms_award_distributions%ROWTYPE;
238 x_award_id NUMBER ;
239 x_project_id NUMBER ;
240 x_count NUMBER ;
241 x_encumbrance_type VARCHAR2(30) ;
242 x_task_id NUMBER ;
243 x_amount NUMBER ;
244
245 -- ===================================================================================================
246 -- This cursor verifies whether any duplicate records are there in gms_award_distributions table
247 -- before copying the items by comparing the exp_type,task_id and quantity of origional and new items.
248 -- This is done as there is no link between the new and origional item. If a duplicate record is
249 -- found in ADL table then this encumbrance item is deleted from gms_encumbrance_items_all
250 -- table otherwise adl is created .
251 -- ===================================================================================================
252 CURSOR NEW_ITEMS (x_new_group VARCHAR2 ) IS
253 select e1.encumbrance_item_id,
254 e1.encumbrance_type ,
255 e1.encumbrance_item_date ,
256 e1.task_id ,
257 e1.amount ,
258 e1.creation_date ,
259 e1.created_by ,
260 e1.last_update_date ,
261 e1.last_updated_by ,
262 e1.last_update_login
263 from gms_encumbrance_items_all e1
264 ,gms_encumbrances_all e2
265
266 where e2.encumbrance_group = x_new_group
267 and e2.encumbrance_id = e1.encumbrance_id
268 and e1.encumbrance_type = x_encumbrance_type
269 and e1.task_id = x_task_id
270 and e1.amount = x_amount
271 and not exists (select 'X' from gms_award_distributions gad
272 where gad.expenditure_item_id = e1.encumbrance_item_id
273 and gad.adl_status = 'A'
274 and gad.document_type ='ENC'
275 )
276 order by e1.encumbrance_item_id asc;
277
278 CURSOR ORG_ITEMS (x_org_group VARCHAR2 ) IS
279 select e1.encumbrance_item_id ,
280 e1.encumbrance_type ,
281 e1.encumbrance_item_date ,
282 e1.amount ,
283 e1.task_id ,
284 e1.creation_date ,
285 e1.created_by ,
286 e1.last_update_date ,
287 e1.last_updated_by ,
288 e1.last_update_login
289 from gms_encumbrance_items_all e1
290 ,gms_encumbrances_all e2
291 ,gms_encumbrance_groups_all e3
292 where e3.encumbrance_group = x_org_group
293 and e3.encumbrance_group = e2.encumbrance_group
294 and e2.encumbrance_id = e1.encumbrance_id
295 order by e1.encumbrance_item_id asc ;
296
297
298 BEGIN
299
300 FOR org_rec IN ORG_ITEMS (x_org_group ) LOOP
301
302 x_encumbrance_type := org_rec.encumbrance_type ;
303 x_task_id := org_rec.task_id ;
304 x_amount := org_rec.amount ;
305
306 FOR new_rec IN NEW_ITEMS (x_new_group ) LOOP
307
308 IF NEW_ITEMS%FOUND THEN
309
310 -- Fix starts for bug number : 1708977
311 select project_id into x_project_id
312 from pa_tasks
313 where task_id = x_task_id ;
314
315 IF GMS_PA_API.IS_SPONSORED_PROJECT(x_project_id ) then
316
317 -- Fix ends for bug number : 1708977
318
319 Begin
320 select distinct(adl.project_id) ,adl.award_id into x_project_id , x_award_id
321 from gms_award_distributions adl
322 where adl.expenditure_item_id = org_rec.encumbrance_item_id
323 and adl.document_type = 'ENC'
324 and nvl(adl.reversed_flag, 'N') = 'N' --Bug 5726575
325 and adl.line_num_reversed is null --Bug 5726575
326 and adl.adl_status = 'A' ;
327
328 Exception
329 WHEN others then
330 Raise ;
331 END ;
332
333 gms_transactions_pub.validate_transaction (x_project_id ,
334 new_rec.task_id ,
335 x_award_id ,
336 new_rec.encumbrance_type ,
337 new_rec.encumbrance_item_date ,
338 'ENC' ,
339 p_outcome ) ;
340
341 -- ==========================================================================
342 -- Here we rollback if outcome is not null . So the records inserted by GMS in
343 -- encumbrance and ITEMS table will de deleted because of the rollback .
344 -- ==========================================================================
345
346 If p_outcome is NOT NULL THEN
347 rollback ;
348 return ;
349 End if;
350
351 x_adl_rec.award_id := x_award_id ;
352 x_adl_rec.adl_line_num := 1;
353 x_adl_rec.project_id := x_project_id ;
354 x_adl_rec.task_id := new_rec.task_id ;
355 x_adl_rec.distribution_value := 100 ;
356 x_adl_rec.adl_status := 'A' ;
357 x_adl_rec.line_type := 'R' ;
358 x_adl_rec.cdl_line_num := 1 ;
359 x_adl_rec.document_type := 'ENC' ;
360 x_adl_rec.award_set_id := gms_awards_dist_pkg.get_award_set_id;
361 x_adl_rec.expenditure_item_id := new_rec.encumbrance_item_id;
362 x_adl_rec.billed_flag := 'N' ;
363 x_adl_rec.bill_hold_flag := 'N' ;
364 x_adl_rec.last_update_date := new_rec.last_update_date;
365 x_adl_rec.creation_date := new_rec.creation_date;
366 x_adl_rec.last_updated_by := new_rec.last_updated_by;
367 x_adl_rec.created_by := new_rec.created_by;
371 EXIT ; -- Fix for bug : 2642650
368 x_adl_rec.last_update_login := new_rec.last_update_login;
369
370 gms_awards_dist_pkg.create_adls(x_adl_rec);
372
373 END IF ; -- if for IF GMS_PA_API.IS_SPONSORED_PROJECT(x_project_id ) Check
374
375 End if ;
376 END LOOP;
377 END LOOP;
378 EXCEPTION
379 when others then
380 raise ;
381 END GMS_ENC_COPY;
382
383 -- ============================================================================================================
384 -- This procedure will be called from GMSTRENE(Encumbrance Entry form ) while an encumbrance batch is Reversed.
385 -- This will retrive all the encumbrance_items for a perticular batch and insert into ADL table .
386 -- ============================================================================================================
387 PROCEDURE GMS_ENC_REVERSE(X_NEW_GROUP IN VARCHAR2) IS
388 x_adl_rec gms_award_distributions%ROWTYPE;
389 x_award_id NUMBER ;
390 x_project_id NUMBER ;
391 CURSOR ENC_ITEMS (x_new_group VARCHAR2 ) IS
392 select e1.encumbrance_item_id,
393 e1.task_id ,
394 e1.creation_date ,
395 e1.created_by ,
396 e1.last_update_date ,
397 e1.last_updated_by ,
398 e1.last_update_login ,
399 e1.adjusted_encumbrance_item_id
400
401 from gms_encumbrance_items_all e1
402 ,gms_encumbrances_all e2
403
404 where e2.encumbrance_group = x_new_group
405 and e2.encumbrance_id = e1.encumbrance_id;
406
407
408 BEGIN
409
410 FOR rev_rec IN ENC_ITEMS (x_new_group ) LOOP
411
412 -- Fix starts for bug number : 1708977
413 select project_id into x_project_id
414 from pa_tasks
415 where task_id = rev_rec.task_id ;
416
417 IF GMS_PA_API.IS_SPONSORED_PROJECT(x_project_id ) then
418
419 -- Fix ends for bug number : 1708977
420 Begin
421 select distinct(adl.project_id), adl.award_id into x_project_id , x_award_id
422 from gms_award_distributions adl
423 where expenditure_item_id = rev_rec.adjusted_encumbrance_item_id
424 and adl.document_type ='ENC'
425 and nvl(adl.reversed_flag, 'N') = 'N' --Bug 5726575
426 and adl.line_num_reversed is null --Bug 5726575
427 and adl.adl_status = 'A' ;
428
429 Exception
430 WHEN others then
431 Raise ;
432 END ;
433
434 x_adl_rec.award_id := x_award_id ;
435 x_adl_rec.adl_line_num := 1;
436 x_adl_rec.project_id := x_project_id ;
437 x_adl_rec.task_id := rev_rec.task_id ;
438 x_adl_rec.distribution_value := 100 ;
439 x_adl_rec.adl_status := 'A' ;
440 x_adl_rec.line_type := 'R' ;
441 x_adl_rec.cdl_line_num := 1 ;
442 x_adl_rec.document_type := 'ENC' ;
443 x_adl_rec.award_set_id := gms_awards_dist_pkg.get_award_set_id;
444 x_adl_rec.expenditure_item_id := rev_rec.encumbrance_item_id;
445 x_adl_rec.billed_flag := 'N' ;
446 x_adl_rec.bill_hold_flag := 'N' ;
447 x_adl_rec.last_update_date := rev_rec.last_update_date;
448 x_adl_rec.creation_date := rev_rec.creation_date;
449 x_adl_rec.last_updated_by := rev_rec.last_updated_by;
450 x_adl_rec.created_by := rev_rec.created_by;
451 x_adl_rec.last_update_login := rev_rec.last_update_login;
452
453 gms_awards_dist_pkg.create_adls(x_adl_rec);
454
455 END IF ; -- if for IF GMS_PA_API.IS_SPONSORED_PROJECT(x_project_id ) Check
456
457 END LOOP;
458 EXCEPTION
459 when others then
460 raise ;
461 END GMS_ENC_REVERSE;
462
463 END GMS_EXP_ADJUSTMENT_PKG;
[Home] [Help]