[Home] [Help]
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,
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;
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) ;
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:
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:
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:
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) ;
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:
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
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:
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,
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