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