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