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