[Home] [Help]
PACKAGE BODY: APPS.GMS_REPORT_SF272
Source
1 PACKAGE BODY GMS_REPORT_SF272 AS
2 --$Header: gmsgrfcb.pls 120.1 2006/02/14 01:50:15 lveerubh noship $
3
4 Procedure INSERT_GMS_272_HISTORY(X_Award_Id IN NUMBER
5 ,X_Document_Number IN VARCHAR2
6 ,X_Total_Actuals IN NUMBER
7 ,X_Cash_On_Hand_Beginning IN NUMBER
8 ,X_Version IN NUMBER
9 ,X_Status_Code IN VARCHAR2
10 ,X_Report_Start_Date IN DATE
11 ,X_Report_End_Date IN DATE
12 ,X_Err_Code OUT NOCOPY VARCHAR2
13 ,X_Err_Stage OUT NOCOPY VARCHAR2) IS
14
15
16 Begin
17
18 Begin
19 INSERT INTO GMS_272_HISTORY
20 (
21 AWARD_ID
22 ,VERSION
23 ,STATUS_CODE
24 ,CREATION_DATE
25 ,CREATED_BY
26 ,LAST_UPDATE_DATE
27 ,LAST_UPDATED_BY
28 ,LAST_UPDATE_LOGIN
29 ,DOCUMENT_NUMBER
30 ,RUN_DATE
31 ,REPORT_START_DATE
32 ,REPORT_END_DATE
33 ,LETTER_OF_CREDIT
34 ,LAST_PAYMENT_VOUCHER_NUMBER
35 ,PAYMENT_VOUCHER_CREDIT
36 ,TREASURY_CHECK
37 ,CASH_IN_HAND_BEGIN
38 ,LETTER_OF_CREDIT_WITHDRAWLS
39 ,TREASURY_CHECK_PAYMENTS
40 ,TOTAL_RECEIPTS
41 ,TOTAL_CASH_AVAIABLE
42 ,GROSS_DISBURSEMENTS
43 ,FEDERAL_SHARE_INCOME
44 ,NET_DISBURSEMENTS
45 ,ADJUSTMENTS_PRIOR_PERIODS
46 ,CASH_IN_HAND_END
47 ,DAYS
48 ,INTEREST_INCOME
49 ,ADV_TO_SUBGRANTEES
50 ,REMARKS
51 )
52 values
53 (
54 X_Award_Id
55 ,X_Version
56 ,X_Status_Code
57 ,SYSDATE
58 ,fnd_global.user_id
59 ,SYSDATE
60 ,fnd_global.user_id
61 ,fnd_global.login_id
62 ,X_Document_Number
63 ,SYSDATE
64 ,X_Report_Start_Date
65 ,X_Report_End_Date
66 ,0
67 ,0
68 ,0
69 ,0
70 ,X_Cash_On_Hand_Beginning
71 ,0
72 ,0
73 ,0
74 ,0
75 ,X_Total_Actuals
76 ,0
77 ,0
78 ,0
79 ,0
80 ,0
81 ,0
82 ,0
83 ,NULL
84 );
85 commit;
86 X_Err_Code := 'S';
87
88 EXCEPTION
89 WHEN OTHERS THEN
90 X_Err_Code := 'U';
91 FND_MESSAGE.SET_NAME('GMS','GMS_UNEXPECTED_ERROR');
92 FND_MESSAGE.SET_TOKEN('PROGRAM_NAME','GMS_REPORT_272: INSERT_GMS_272_HISTORY');
93 FND_MESSAGE.SET_TOKEN('OERRNO',SQLCODE);
94 FND_MESSAGE.SET_TOKEN('OERRM',SQLERRM);
95 RAISE FND_API.G_EXC_ERROR;
96 End;
97
98 EXCEPTION
99 WHEN FND_API.G_EXC_ERROR THEN
100 RETURN;
101
102 End INSERT_GMS_272_HISTORY;
103
104
105
106
107
108
109 Procedure Populate_272_History(X_Award_Id IN NUMBER,
110 X_Report_Start_Date IN DATE,
111 X_Report_End_Date IN DATE,
112 RETCODE OUT NOCOPY VARCHAR2,
113 ERRBUF OUT NOCOPY VARCHAR2) IS
114
115 -- Bug 4005755 : splitted the cursor defination to remove outer join.
116 -- Functionality remains same.
117 -- Bug fixes 1356234,2701130,2831665 fixes are ported into these splitted new sql's
118 --
119 cursor c_sum_amount is
120 Select SUM(nvl(c.amount,0))
121 from pa_expenditure_items_all ei,
122 pa_cost_distribution_lines_all c,
123 gms_award_distributions g
124 where c.gl_date between X_Report_Start_Date and X_Report_End_Date
125 and c.expenditure_item_id = ei.expenditure_item_id
126 and g.award_id = X_Award_Id
127 and g.document_type = 'EXP'
128 and g.adl_line_num = 1
129 and g.adl_status = 'A'
130 and g.expenditure_item_id = c.expenditure_item_id
131 and c.line_type = 'R'
132 and ei.system_linkage_function <> 'BTC' -- Put the correct code for system linkage function
133 and ei.project_id in ( select gbv.project_id
134 from gms_budget_versions gbv
135 where gbv.budget_type_code = 'AC'
136 and gbv.budget_status_code in ('S','W' )
137 and gbv.award_id = X_Award_Id )
138 ;
139 --Bug Fix 4940833 :SQL repository Issue
140 /*
141
142 cursor c_sum_burden is
143 Select sum(nvl(bv.burden_cost,0))
144 from pa_expenditure_items_all ei,
145 pa_cost_distribution_lines_all c,
146 gms_cdl_burden_detail_v bv
147 where c.gl_date between X_Report_Start_Date and X_Report_End_Date
148 and c.expenditure_item_id = ei.expenditure_item_id
149 and bv.award_id = X_Award_Id
150 and c.line_type = 'R'
151 and ei.system_linkage_function <> 'BTC' -- Put the correct code for system linkage function
152 and bv.expenditure_item_id = c.expenditure_item_id
153 and bv.line_num = c.line_num
154 and bv.project_id = ei.project_id
155 and ei.project_id in ( select gbv.project_id
156 from gms_budget_versions gbv
157 where gbv.budget_type_code = 'AC'
158 and gbv.budget_status_code in ('S','W' )
159 and gbv.award_id = X_Award_Id )
160 and bv.project_id in ( select gbv.project_id
161 from gms_budget_versions gbv
162 where gbv.budget_type_code = 'AC'
163 and gbv.budget_status_code in ('S','W' )
164 and gbv.award_id = X_Award_Id ) ;
165 */
166 cursor c_sum_burden is
167 Select sum(nvl(bv.burden_cost,0))
168 FROM gms_cdl_burden_detail_v bv,
169 gms_budget_versions gbv
170 WHERE bv.gl_date between X_Report_Start_Date and X_Report_End_Date
171 and bv.award_id = X_Award_Id
172 and bv.line_type = 'R'
173 and bv.system_linkage_function <> 'BTC' -- Put the correct code for system linkage function
174 and gbv.budget_type_code = 'AC'
175 and gbv.budget_status_code in ('S','W' )
176 and gbv.award_id = X_Award_Id
177 and bv.project_id = gbv.project_id;
178
179 --End of Bug fix 4940833
180 /* Start of changes for BUG : 2357578 */
181 -- Cursor to fetch Report periods
182 CURSOR report_period_date_cur IS
183 SELECT GREATEST(X_Report_Start_Date, start_date_active),
184 LEAST(X_Report_End_Date, end_date_active)
185 FROM gms_awards
186 WHERE award_id = X_Award_Id;
187
188 L_Report_Start_Date gms_272_history.report_start_date%type;
189 L_Report_End_Date gms_272_history.report_end_date%type;
190
191 /* End of changes for BUG : 2357578 */
192
193 X_Expenditure_Item_Id NUMBER := NULL;
194 X_Line_Num NUMBER := NULL;
195 X_Transfer_Status_Code VARCHAR2(1) := NULL;
196 X_Sum_Raw_Cost NUMBER(22,5) := 0;
197 X_Sum_Burden_Cost NUMBER(22,5) := 0;
198 X_Document_Number VARCHAR2(30);
199 X_Status_Code VARCHAR2(1) := NULL;
200 X_Cash_On_Hand_Beginning NUMBER(22,5) := 0;
201 X_Total_Actuals NUMBER(22,5) := 0;
202 X_Version NUMBER;
203
204 X_Err_Code VARCHAR2(1);
205 X_Err_Buff VARCHAR2(2000);
206
207 Begin
208
209 /* To set project_id NULL to be able to use Suresh's view to derive Burden Components and Burden Cost */
210 -- GMS_BURDEN_COSTING.SET_CURRENT_PROJECT_ID(NULL);
211 -- The above line has been commented out NOCOPY for the bug 2442827
212 /* Get Funding Source Award Number as the default document Number */
213 Begin
214 select
215 funding_source_award_number
216 into
217 X_Document_Number
218 from
219 GMS_AWARDS
220 where
221 award_id = X_Award_Id;
222 EXCEPTION
223 WHEN NO_DATA_FOUND THEN
224 NULL;
225 End;
226
227
228 -- Bug 4005755 : Modified the logic for performance fix
229
230 open c_sum_amount ;
231 fetch c_sum_amount into X_Sum_Raw_Cost ;
232 close c_sum_amount ;
233
234 open c_sum_burden ;
235 fetch c_sum_burden into X_Sum_Burden_Cost ;
236 close c_sum_burden ;
237
238 --Added for Bug 2357578
239 OPEN report_period_date_cur;
240 FETCH report_period_date_cur INTO L_Report_Start_Date,
241 L_Report_End_Date;
242 CLOSE report_period_date_cur;
243
244 X_Total_Actuals := NVL(X_Sum_Raw_Cost,0) + NVL(X_Sum_Burden_Cost,0) ;
245
246 -- End of fix 4005755
247
248 Begin
249 /* To get the cash_in_hand at the beginning of the reporting period */
250
251 select nvl(CASH_IN_HAND_END,0)
252 into X_Cash_On_hand_beginning
253 from gms_272_history
254 where award_id = X_Award_Id
255 and status_code = 'F'
256 and version = (select max(version) from gms_272_history gh
257 where gh.award_id = X_Award_Id
258 and status_code = 'F');
259 EXCEPTION
260 WHEN NO_DATA_FOUND THEN
261 NULL;
262 End;
263
264 Begin
265 /* Getting the latest version to be inserted */
266 select (nvl(max(version),0) +1)
267 into
268 X_Version
269 from gms_272_history
270 where award_id = X_Award_Id
271 and status_code = 'O';
272 End;
273
274 /* Inserting TWO Rows in GMS_272_HISTORY Table, one with a status of 'O' and one with a status of 'D' */
275 Begin
276 X_Status_Code := 'O';
277 INSERT_GMS_272_HISTORY(X_Award_Id
278 ,X_Document_Number
279 ,X_Total_actuals
280 ,X_Cash_On_Hand_Beginning
281 ,X_Version
282 ,X_Status_Code
283 ,L_Report_Start_Date --Bug 2357578
284 ,L_Report_End_Date --Bug 2357578
285 ,X_Err_Code
286 ,X_Err_Buff);
287 If X_Err_Code <> 'S' then
288 RAISE FND_API.G_EXC_ERROR;
289 End If;
290
291 X_Status_Code := 'D';
292 INSERT_GMS_272_HISTORY(X_Award_Id
293 ,X_Document_Number
294 ,X_Total_Actuals
295 ,X_Cash_On_Hand_Beginning
296 ,X_Version
297 ,X_Status_Code
298 ,L_Report_Start_Date --Bug 2357578
299 ,L_Report_End_Date --Bug 2357578
300 ,X_Err_Code
301 ,X_Err_Buff);
302
303 If X_Err_Code <> 'S' then
304 RAISE FND_API.G_EXC_ERROR;
305 End If;
306
307
308 End;
309
310 EXCEPTION
311 WHEN FND_API.G_EXC_ERROR THEN
312 RETCODE := X_Err_Code;
313 ERRBUF := X_Err_Buff;
314
315 End Populate_272_History;
316
317 End GMS_REPORT_SF272;