1 PACKAGE BODY GMS_PA_XFACE AS
2 --$Header: gmsexadb.pls 115.3 2002/08/01 09:43:53 gnema ship $
3
4 -- VERIFY is a wrapper Function which is called from PAXTADJB.pll to check
5 -- 1. The expenditure_type is allowed for the dest_award. GMS_CHECK_EXP_TYPE() will return TRUE
6 -- if the exp_type is allowed for the allowability_sechdule_id of the dest_award_id
7
8 -- 2. The expenditure_item_date is less than or equal to the End_date of the dest_award. GMS_CHECK_AWARD_DATES
9 -- will return TRUE if the expenditure_item_date is less than or equal to the End_date of the dest_award
10
11 -- 3. Whether the Invoice distribution line in AP is Reversed out or not. GMS_CHECK_BURDEN_COST will return
12 -- FALSE if the query does not find a record in AP tables which is Reversed out . In that case we will fail the record
13
14 -- ===================================================================================================================
15 -- This function will verify whether the project is SPONSORED PROJECT or NOT
16 -- ===================================================================================================================
17 FUNCTION GMS_IS_SPON_PROJECT (x_project_id IN NUMBER ) RETURN BOOLEAN IS
18
19 CURSOR c_project IS
20 SELECT 'X'
21 FROM pa_projects p,
22 gms_project_types gpt
23 WHERE p.project_id = x_project_id
24 AND p.project_type = gpt.project_type
25 AND gpt.sponsored_flag= 'Y' ;
26
27 x_dummy varchar2(1) ;
28 x_return BOOLEAN ;
29 BEGIN
30 OPEN c_project ;
31 fetch c_project into x_dummy ;
32 IF c_project%FOUND then
33 x_return := TRUE ;
34 ELSE
35 x_return := FALSE ;
36 END IF ;
37 return (x_return ) ;
38 CLOSE c_project ;
39 EXCEPTION
40 WHEN OTHERS THEN
41 return FALSE ;
42 END GMS_IS_SPON_PROJECT ;
43
44
45 -- ===================================================================================================================
46
47 FUNCTION VERIFY (x_expenditure_item_id IN NUMBER ) RETURN BOOLEAN IS
48
49 BEGIN
50 IF
51 GMS_IS_SPON_PROJECT (SOURCE_PROJECT_ID ) -- If the project is sponsored project then only perform other things
52 AND GMS_CHECK_AWARD_DATES(x_expenditure_item_id ) -- with in the Award_end_date , hence TRANSFER
53 AND GMS_CHECK_EXP_TYPE(x_expenditure_item_id ) -- This expenditure_type is allowable, hence TRANSFER
54 AND NOT GMS_CHECK_BURDENCOST (x_expenditure_item_id ) -- This item_id is not Reversed out in AP , hence TRANSFER
55 THEN
56 return FALSE ;
57 END IF;
58 return TRUE; -- Don't TRANSFER
59 END VERIFY ;
60
61 -- ========================================================================================================
62 -- This procedure will be called from PAXTRAPE (Expenditure Inquiry ) form when an expenditure_item is SPLIT.
63 -- This will insert a reversed expendtiure_item record and two new expenditure_items records into ADL table.
64 -- =========================================================================================================
65 PROCEDURE GMS_SPLIT (x_expenditure_item_id IN NUMBER ) IS
66
67 adl_rec gms_award_distributions%ROWTYPE;
68 x_flag varchar2(1);
69 CURSOR rev_item(x_expenditure_item_id NUMBER ) IS
70 SELECT * from pa_expenditure_items_all
71 WHERE adjusted_expenditure_item_id = x_expenditure_item_id ;
72
73 CURSOR new_item(x_expenditure_item_id NUMBER ) IS
74 SELECT * from pa_expenditure_items_all
75 WHERE transferred_from_exp_item_id = x_expenditure_item_id ;
76
77 BEGIN
78
79 FOR rev_rec IN rev_item(x_expenditure_item_id) LOOP
80
81 begin
82 select award_id , bill_hold_flag into source_award_id ,x_flag from gms_award_distributions adl
83 where adl.expenditure_item_id = x_expenditure_item_id
84 and adl.document_type = 'EXP' ;
85 exception
86 when too_many_rows then
87 null;
88 end ;
89
90 adl_rec.expenditure_item_id := rev_rec.expenditure_item_id;
91 adl_rec.cost_distributed_flag := 'N';
92 adl_rec.project_id := SOURCE_PROJECT_ID;
93 adl_rec.task_id := rev_rec.task_id;
94 adl_rec.cdl_line_num := 1;
95 adl_rec.adl_line_num := 1;
96 adl_rec.distribution_value := 100;
97 adl_rec.line_type :='R';
98 adl_rec.adl_status := 'A';
99 adl_rec.document_type := 'EXP';
100 adl_rec.billed_flag := 'N';
101 adl_rec.bill_hold_flag := x_flag ;
102 adl_rec.award_set_id := gms_awards_dist_pkg.get_award_set_id;
103 adl_rec.award_id := source_award_id ;
104 adl_rec.raw_cost := rev_rec.raw_cost;
105 adl_rec.last_update_date := rev_rec.last_update_date;
106 adl_rec.creation_date := rev_rec.creation_date;
107 adl_rec.last_updated_by := rev_rec.last_updated_by;
108 adl_rec.created_by := rev_rec.created_by;
109 adl_rec.last_update_login := rev_rec.last_update_login;
110 gms_awards_dist_pkg.create_adls(adl_rec);
111 END LOOP;
112
113 FOR new_rec IN new_item (x_expenditure_item_id) LOOP
114 adl_rec.expenditure_item_id := new_rec.expenditure_item_id;
115 adl_rec.project_id := SOURCE_PROJECT_ID;
116 adl_rec.task_id := new_rec.task_id;
117 adl_rec.cost_distributed_flag := 'N';
118 adl_rec.cdl_line_num := 1;
119 adl_rec.adl_line_num := 1;
120 adl_rec.distribution_value := 100 ;
121 adl_rec.line_type :='R';
122 adl_rec.adl_status := 'A';
123 adl_rec.document_type := 'EXP';
124 adl_rec.billed_flag := 'N';
125 adl_rec.bill_hold_flag := x_flag ;
126 adl_rec.award_set_id := gms_awards_dist_pkg.get_award_set_id;
127 adl_rec.award_id := source_award_id;
128 adl_rec.raw_cost := new_rec.raw_cost;
129 adl_rec.last_update_date := new_rec.last_update_date;
130 adl_rec.creation_date := new_rec.creation_date;
131 adl_rec.last_updated_by := new_rec.last_updated_by;
132 adl_rec.created_by := new_rec.created_by;
133 adl_rec.last_update_login := new_rec.last_update_login;
134 gms_awards_dist_pkg.create_adls(adl_rec);
135 END LOOP;
136
137 EXCEPTION
138
139 when others then
140 raise ;
141 END GMS_SPLIT;
142
143 -- ==============================================================================================
144 -- GMS_CHECK_EXP_TYPE() will return TRUE
145 -- if the exp_type is allowed for the allowability_sechdule_id of the dest_award_id
146 -- This function will be called from PAXTRPAE( Expenditure_Inquiry ) while trasnferring to check
147 -- whether the expenditure_item is with in the award end_date.
148 -- ===============================================================================================
149
150 FUNCTION GMS_CHECK_EXP_TYPE (x_expenditure_item_id IN NUMBER ) RETURN BOOLEAN IS
151
152 CURSOR exp_type IS
153 select expenditure_type
154 from gms_allowable_expenditures
155 where allowability_schedule_id = x_allowable_id
156 and expenditure_type = x_expenditure_type;
157
158 BEGIN
159 OPEN exp_type ;
160 FETCH exp_type INTO x_type ;
161 IF exp_type%FOUND THEN
162 return TRUE ;
163 CLOSE exp_type ;
164 END IF;
165 return FALSE ;
166
167 EXCEPTION
168 WHEN OTHERS THEN
169 RAISE;
170 END GMS_CHECK_EXP_TYPE ;
171
172 -- =======================================================================================================
173 -- GMS_CHECK_AWARD_DATES
174 -- will return TRUE if the expenditure_item_date is less than or equal to the End_date of the dest_award
175 -- ========================================================================================================
176 FUNCTION GMS_CHECK_AWARD_DATES(x_expenditure_item_id IN NUMBER ) RETURN BOOLEAN IS
177
178 CURSOR C_AWARDS(dest_award_id IN NUMBER) IS
179 select allowable_schedule_id ,end_date_active from gms_awards_all
180 where award_id = dest_award_id ;
181
182 CURSOR C_EXP(x_expenditure_item_id IN NUMBER ) IS
183 select expenditure_type,expenditure_item_date
184 from pa_expenditure_items_all
185 where expenditure_item_id = x_expenditure_item_id;
186
187 BEGIN
188
189 FOR awards_rec IN C_AWARDS (dest_award_id) LOOP
190 x_allowable_id := awards_rec.allowable_schedule_id ;
191 x_end_date := awards_rec.end_date_active ;
192 END LOOP;
193
194 FOR exp_rec IN C_EXP (x_expenditure_item_id) LOOP
195 x_expenditure_type := exp_rec.expenditure_type ;
196 x_item_date := exp_rec.expenditure_item_date ;
197 END LOOP;
198
199 IF x_item_date <= x_end_date THEN
200 return TRUE;
201 END IF;
202 return FALSE;
203
204 EXCEPTION
205 WHEN OTHERS THEN
206 RAISE;
207 END GMS_CHECK_AWARD_DATES ;
208
209 -- ========================================================================================
210 -- GMS_CHECK_BURDEN_COST will return
211 -- FALSE if the query finds a record in AP tables which is Reversed out
212 -- ========================================================================================
213 FUNCTION GMS_CHECK_BURDENCOST (x_expenditure_item_id IN NUMBER ) RETURN BOOLEAN IS
214 X_REF2 VARCHAR2(30);
215 X_REF3 VARCHAR2(30);
216 CURSOR C_CDL IS
217 select CDL.system_reference2 , -- AP INVOICE_ID
218 CDL.system_reference3 -- AP DIST LINE NUM
219 from pa_cost_distribution_lines CDL,
220 ap_invoice_distributions ADL
221 where CDL.expenditure_item_id = X_expenditure_item_id
222 and CDL.system_reference2 = ADL.invoice_id
223 and CDL.system_reference3 = ADL.distribution_line_number
224 and ADL.attribute6 IS NULL
225 and CDL.line_num = 1
226 and CDL.system_reference2 IS NOT NULL
227 and CDL.system_reference3 IS NOT NULL;
228
229 BEGIN
230
231
232 OPEN c_cdl;
233 fetch c_cdl into x_ref2,x_ref3;
234 IF c_cdl%FOUND THEN
235 RETURN TRUE ;
236 -- ERROR : NOT ALLOWED ANY ADJUSTMENTS (SPLIT, TRASFER )
237 END IF;
238 CLOSE c_cdl;
239 RETURN FALSE;
240
241 EXCEPTION
242 WHEN NO_DATA_FOUND THEN
243 RAISE ;
244 WHEN OTHERS THEN
245 RAISE;
246 END GMS_CHECK_BURDENCOST;
247
248 -- ==============================================================================
249
250 FUNCTION GMS_COMP_AWARDS(X_ADJUST_ACTION IN VARCHAR2 ) RETURN VARCHAR2 IS
251
252 BEGIN
253 If GMS_IS_SPON_PROJECT( source_project_id ) AND SOURCE_AWARD_ID = DEST_AWARD_ID THEN
254 return 'Y';
255 end if;
256 RETURN 'N' ;
257 END GMS_COMP_AWARDS;
258
259 -- ================================================================================
260
261 PROCEDURE GMS_SET_AWARD (X_SOURCE_AWARD_ID IN NUMBER,
262 X_DEST_AWARD_ID IN NUMBER) IS
263
264 BEGIN
265
266 SOURCE_AWARD_ID := X_SOURCE_AWARD_ID;
267 DEST_AWARD_ID := X_DEST_AWARD_ID;
268
269 END GMS_SET_AWARD;
270
271 -- ================================================================================
272 PROCEDURE GMS_SET_PROJECT_ID (X_SOURCE_PROJECT_ID IN NUMBER,
273 X_DEST_PROJECT_ID IN NUMBER) IS
274
275 BEGIN
276
277 SOURCE_PROJECT_ID := X_SOURCE_PROJECT_ID;
278 DEST_PROJECT_ID := X_DEST_PROJECT_ID;
279
280 END GMS_SET_PROJECT_ID;
281
282 -- ======================================================================================================================
283 -- This proceudre is called from PAXTRANB.pls while the expenditure_ites are transferred or MassAdjusted.
284 -- For TRASFER x_rows will be 1 since expenditure_items are loaded into LaoadEi record by record and
285 -- transferred one at a time. Where as while MassAdjusting all the expenditures_items are loaded into LoadEi at one shot
286 -- =======================================================================================================================
287
288 PROCEDURE GMS_ADJUST_ITEMS (X_CALLING_PROCESS IN VARCHAR2,
289 X_ROWS IN NUMBER ) IS
290
291 adl_rec gms_award_distributions%ROWTYPE;
292 x_exp_item_id NUMBER;
293 x_new_item_id NUMBER;
294 x_flag varchar2(1) ;
295 CURSOR rev_item(x_exp_item_id NUMBER ) IS
296 SELECT * from pa_expenditure_items_all
297 WHERE adjusted_expenditure_item_id = x_exp_item_id ;
298
299 CURSOR new_item(x_exp_item_id NUMBER ) IS
300 SELECT * from pa_expenditure_items_all
301 WHERE transferred_from_exp_item_id = x_exp_item_id ;
302
303 BEGIN
304
305 IF X_CALLING_PROCESS IN ( 'TRANSFER') and GMS_IS_SPON_PROJECT (SOURCE_PROJECT_ID ) THEN
306
307 FOR i IN 1..X_ROWS LOOP
308
309 x_exp_item_id := PA_TRANSACTIONS.TfrEiTab(i);
310
311 begin
312 select bill_hold_flag, award_id into x_flag, source_award_id from gms_award_distributions
313 where expenditure_item_id = x_exp_item_id
314 and award_id = source_award_id
315 and document_type = 'EXP' ;
316 exception
317 when others then
318 null;
319 end ;
320 FOR rev_rec IN rev_item(x_exp_item_id) LOOP
321 adl_rec.expenditure_item_id := rev_rec.expenditure_item_id;
322 adl_rec.cost_distributed_flag := 'N';
323 adl_rec.project_id := SOURCE_PROJECT_ID;
324 adl_rec.task_id := rev_rec.task_id;
325 adl_rec.cdl_line_num := 1;
326 adl_rec.adl_line_num := 1;
327 adl_rec.distribution_value := 100;
328 adl_rec.line_type :='R';
329 adl_rec.adl_status := 'A';
330 adl_rec.document_type := 'EXP';
331 adl_rec.billed_flag := 'N';
332 adl_rec.bill_hold_flag := x_flag ;
333 adl_rec.award_set_id := gms_awards_dist_pkg.get_award_set_id;
334 adl_rec.award_id := SOURCE_AWARD_ID;
335 adl_rec.raw_cost := rev_rec.raw_cost;
336 adl_rec.last_update_date := rev_rec.last_update_date;
337 adl_rec.creation_date := rev_rec.creation_date;
338 adl_rec.last_updated_by := rev_rec.last_updated_by;
339 adl_rec.created_by := rev_rec.created_by;
340 adl_rec.last_update_login := rev_rec.last_update_login;
341 gms_awards_dist_pkg.create_adls(adl_rec);
342 END LOOP;
343
344 FOR new_rec IN new_item (x_exp_item_id) LOOP
345 adl_rec.expenditure_item_id := new_rec.expenditure_item_id;
346 adl_rec.project_id := DEST_PROJECT_ID;
347 adl_rec.task_id := new_rec.task_id;
348 adl_rec.cost_distributed_flag := 'N';
349 adl_rec.cdl_line_num := 1;
350 adl_rec.adl_line_num := 1;
351 adl_rec.distribution_value := 100;
352 adl_rec.line_type :='R';
353 adl_rec.adl_status := 'A';
354 adl_rec.document_type := 'EXP';
355 adl_rec.billed_flag := 'N';
356 adl_rec.bill_hold_flag := x_flag ;
357 adl_rec.award_set_id := gms_awards_dist_pkg.get_award_set_id;
358 adl_rec.award_id := DEST_AWARD_ID;
359 adl_rec.raw_cost := new_rec.raw_cost;
360 adl_rec.last_update_date := new_rec.last_update_date;
361 adl_rec.creation_date := new_rec.creation_date;
362 adl_rec.last_updated_by := new_rec.last_updated_by;
363 adl_rec.created_by := new_rec.created_by;
364 adl_rec.last_update_login := new_rec.last_update_login;
365 gms_awards_dist_pkg.create_adls(adl_rec);
366 END LOOP;
367
368 END LOOP;
369
370 END IF;
371
372 -- These are commented out because the source project_id is getting NULL when the TRANSFER is done for more than one record.
373 -- These values are set before calling SPLIT,TRANSFER and MASADJUST.
374
375 -- SOURCE_AWARD_ID := '';
376 -- DEST_AWARD_ID := '';
377 -- SOURCE_PROJECT_ID := '';
378 -- DEST_PROJECT_ID := '';
379
380 EXCEPTION
381
382 when others then
383 RAISE;
384 END GMS_ADJUST_ITEMS;
385 END GMS_PA_XFACE;
[Home] [Help]