[Home] [Help]
PACKAGE BODY: APPS.IGW_BUDGETS_TBH
Source
1 PACKAGE BODY IGW_BUDGETS_TBH AS
2 -- $Header: igwtbvsb.pls 115.7 2002/11/14 18:39:11 vmedikon ship $
3
4 procedure INSERT_ROW (
5 p_proposal_id NUMBER
6 ,p_version_id NUMBER
7 ,p_start_date DATE
8 ,p_end_date DATE
9 ,p_total_cost NUMBER
10 ,p_total_direct_cost NUMBER
11 ,p_total_indirect_cost NUMBER
12 ,p_cost_sharing_amount NUMBER
13 ,p_underrecovery_amount NUMBER
14 ,p_residual_funds NUMBER
15 ,p_total_cost_limit NUMBER
16 ,p_oh_rate_class_id NUMBER
17 ,p_proposal_form_number VARCHAR2
18 ,p_comments VARCHAR2
19 ,p_final_version_flag VARCHAR2
20 ,p_budget_type_code VARCHAR2
21 ,p_enter_budget_at_period_level VARCHAR2
22 ,p_apply_inflation_setup_rates VARCHAR2
23 ,p_apply_eb_setup_rates VARCHAR2
24 ,p_apply_oh_setup_rates VARCHAR2
25 ,p_attribute_category VARCHAR2
26 ,p_attribute1 VARCHAR2
27 ,p_attribute2 VARCHAR2
28 ,p_attribute3 VARCHAR2
29 ,p_attribute4 VARCHAR2
30 ,p_attribute5 VARCHAR2
31 ,p_attribute6 VARCHAR2
32 ,p_attribute7 VARCHAR2
33 ,p_attribute8 VARCHAR2
34 ,p_attribute9 VARCHAR2
35 ,p_attribute10 VARCHAR2
36 ,p_attribute11 VARCHAR2
37 ,p_attribute12 VARCHAR2
38 ,p_attribute13 VARCHAR2
39 ,p_attribute14 VARCHAR2
40 ,p_attribute15 VARCHAR2
41 ,x_rowid OUT NOCOPY VARCHAR2
42 ,x_return_status OUT NOCOPY VARCHAR2) IS
43
44 cursor c_budgets is
45 select rowid
46 from igw_budgets
47 where proposal_id = p_proposal_id
48 and version_id = p_version_id;
49
50 l_last_updated_by NUMBER := FND_GLOBAL.USER_ID;
51 l_last_update_login NUMBER := FND_GLOBAL.LOGIN_ID;
52 l_last_update_date DATE := SYSDATE;
53
54 begin
55 x_return_status := FND_API.G_RET_STS_SUCCESS;
56
57 insert into igw_budgets(
58 proposal_id
59 ,version_id
60 ,start_date
61 ,end_date
62 ,total_cost
63 ,total_direct_cost
64 ,total_indirect_cost
65 ,cost_sharing_amount
66 ,underrecovery_amount
67 ,residual_funds
68 ,total_cost_limit
69 ,oh_rate_class_id
70 ,proposal_form_number
71 ,comments
72 ,final_version_flag
73 ,budget_type_code
74 ,enter_budget_at_period_level
75 ,apply_inflation_setup_rates
76 ,apply_eb_setup_rates
77 ,apply_oh_setup_rates
78 ,last_update_date
79 ,last_updated_by
80 ,creation_date
81 ,created_by
82 ,last_update_login
83 ,attribute_category
84 ,attribute1
85 ,attribute2
86 ,attribute3
87 ,attribute4
88 ,attribute5
89 ,attribute6
90 ,attribute7
91 ,attribute8
92 ,attribute9
93 ,attribute10
94 ,attribute11
95 ,attribute12
96 ,attribute13
97 ,attribute14
98 ,attribute15
99 ,record_version_number)
100 values
101 ( p_proposal_id
102 ,p_version_id
103 ,p_start_date
104 ,p_end_date
105 ,p_total_cost
106 ,p_total_direct_cost
107 ,p_total_indirect_cost
108 ,p_cost_sharing_amount
109 ,p_underrecovery_amount
110 ,p_residual_funds
111 ,p_total_cost_limit
112 ,p_oh_rate_class_id
113 ,p_proposal_form_number
114 ,p_comments
115 ,p_final_version_flag
116 ,p_budget_type_code
117 ,nvl(p_enter_budget_at_period_level, 'N')
118 ,nvl(p_apply_inflation_setup_rates, 'Y')
119 ,nvl(p_apply_eb_setup_rates , 'Y')
120 ,nvl(p_apply_oh_setup_rates, 'Y')
121 ,l_last_update_date
122 ,l_last_updated_by
123 ,l_last_update_date
124 ,l_last_updated_by
125 ,l_last_update_login
126 ,p_attribute_category
127 ,p_attribute1
128 ,p_attribute2
129 ,p_attribute3
130 ,p_attribute4
131 ,p_attribute5
132 ,p_attribute6
133 ,p_attribute7
134 ,p_attribute8
135 ,p_attribute9
136 ,p_attribute10
137 ,p_attribute11
138 ,p_attribute12
139 ,p_attribute13
140 ,p_attribute14
141 ,p_attribute15
142 ,1);
143
144 open c_budgets;
145 fetch c_budgets into x_ROWID;
146 if (c_budgets%notfound) then
147 close c_budgets;
148 raise no_data_found;
149 end if;
150 close c_budgets;
151
152 exception
153 when others then
154 FND_MSG_PUB.add_exc_msg( p_pkg_name => G_package_name
155 ,p_procedure_name => 'INSERT_ROW' );
156 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
157 RAISE;
158 end insert_row;
159
160
161 -----------------------------------------------------------------------------------
162 procedure update_row(
163 p_rowid VARCHAR2
164 ,p_proposal_id NUMBER
165 ,p_version_id NUMBER
166 ,p_start_date DATE
167 ,p_end_date DATE
168 ,p_total_cost NUMBER
169 ,p_total_direct_cost NUMBER
170 ,p_total_indirect_cost NUMBER
171 ,p_cost_sharing_amount NUMBER
172 ,p_underrecovery_amount NUMBER
173 ,p_residual_funds NUMBER
174 ,p_total_cost_limit NUMBER
175 ,p_oh_rate_class_id NUMBER
176 ,p_proposal_form_number VARCHAR2
177 ,p_comments VARCHAR2
178 ,p_final_version_flag VARCHAR2
179 ,p_budget_type_code VARCHAR2
180 ,p_enter_budget_at_period_level VARCHAR2
181 ,p_apply_inflation_setup_rates VARCHAR2
182 ,p_apply_eb_setup_rates VARCHAR2
183 ,p_apply_oh_setup_rates VARCHAR2
184 ,p_attribute_category VARCHAR2
185 ,p_attribute1 VARCHAR2
186 ,p_attribute2 VARCHAR2
187 ,p_attribute3 VARCHAR2
188 ,p_attribute4 VARCHAR2
189 ,p_attribute5 VARCHAR2
190 ,p_attribute6 VARCHAR2
191 ,p_attribute7 VARCHAR2
192 ,p_attribute8 VARCHAR2
193 ,p_attribute9 VARCHAR2
194 ,p_attribute10 VARCHAR2
195 ,p_attribute11 VARCHAR2
196 ,p_attribute12 VARCHAR2
197 ,p_attribute13 VARCHAR2
198 ,p_attribute14 VARCHAR2
199 ,p_attribute15 VARCHAR2
200 ,p_record_version_number NUMBER
201 ,x_return_status OUT NOCOPY VARCHAR2) IS
202
203 l_last_updated_by NUMBER := FND_GLOBAL.USER_ID;
204 l_last_update_login NUMBER := FND_GLOBAL.LOGIN_ID;
205 l_last_update_date DATE := SYSDATE;
206
207 l_row_id ROWID := p_rowid;
208
209 CURSOR get_row_id IS
210 SELECT rowid
211 FROM IGW_BUDGETS
212 WHERE proposal_id = p_proposal_id
213 AND version_id = p_version_id;
214
215 begin
216 x_return_status := FND_API.G_RET_STS_SUCCESS;
217
218 IF l_row_id IS NULL THEN
219 OPEN get_row_id;
220 FETCH get_row_id INTO l_row_id;
221 CLOSE get_row_id;
222 END IF;
223
224 update igw_budgets
225 set start_date = p_start_date
226 , end_date = p_end_date
227 , total_cost = p_total_cost
228 , total_direct_cost = p_total_direct_cost
229 , total_indirect_cost = p_total_indirect_cost
230 , cost_sharing_amount = p_cost_sharing_amount
231 , underrecovery_amount = p_underrecovery_amount
232 , residual_funds = p_residual_funds
233 , total_cost_limit = p_total_cost_limit
234 , oh_rate_class_id = p_oh_rate_class_id
235 , proposal_form_number = p_proposal_form_number
236 , comments = p_comments
237 , final_version_flag = p_final_version_flag
238 /* no need to update the budget type code */
239 --, budget_type_code = p_budget_type_code
240 , enter_budget_at_period_level = p_enter_budget_at_period_level
241 , apply_inflation_setup_rates = p_apply_inflation_setup_rates
242 , apply_eb_setup_rates = p_apply_eb_setup_rates
243 , apply_oh_setup_rates = p_apply_oh_setup_rates
244 , record_version_number = record_version_number + 1
245 , ATTRIBUTE_CATEGORY = P_ATTRIBUTE_CATEGORY
246 , ATTRIBUTE1 = P_ATTRIBUTE1
247 , ATTRIBUTE2 = P_ATTRIBUTE2
248 , ATTRIBUTE3 = P_ATTRIBUTE3
249 , ATTRIBUTE4 = P_ATTRIBUTE4
250 , ATTRIBUTE5 = P_ATTRIBUTE5
251 , ATTRIBUTE6 = P_ATTRIBUTE6
252 , ATTRIBUTE7 = P_ATTRIBUTE7
253 , ATTRIBUTE8 = P_ATTRIBUTE8
254 , ATTRIBUTE9 = P_ATTRIBUTE9
255 , ATTRIBUTE10 = P_ATTRIBUTE10
256 , ATTRIBUTE11 = P_ATTRIBUTE11
257 , ATTRIBUTE12 = P_ATTRIBUTE12
258 , ATTRIBUTE13 = P_ATTRIBUTE13
259 , ATTRIBUTE14 = P_ATTRIBUTE14
260 , ATTRIBUTE15 = P_ATTRIBUTE15
261 where rowid = l_row_id
262 and record_version_number = p_record_version_number;
263
264 if (sql%notfound) then
265 FND_MESSAGE.SET_NAME('IGW','IGW_SS_RECORD_CHANGED');
266 FND_MSG_PUB.Add;
267 X_RETURN_STATUS := 'E';
268 end if;
269
270 exception
271 when others then
272 FND_MSG_PUB.add_exc_msg( p_pkg_name => G_package_name
273 ,p_procedure_name => 'UPDATE_ROW' );
274 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
275 RAISE;
276 end update_row;
277 --------------------------------------------------------------------------------------------------
278
279 procedure DELETE_ROW (
280 p_rowid IN ROWID
281 ,p_proposal_id IN NUMBER
282 ,p_version_id IN NUMBER
283 ,p_record_version_number IN NUMBER
284 ,x_return_status OUT NOCOPY VARCHAR2
285 ) is
286
287 l_row_id ROWID := p_rowid;
288
289 CURSOR get_row_id IS
290 SELECT rowid
291 FROM IGW_BUDGETS
292 WHERE proposal_id = p_proposal_id
293 AND version_id = p_version_id;
294
295 begin
296
297 x_return_status := FND_API.G_RET_STS_SUCCESS;
298
299 IF l_row_id IS NULL THEN
300 OPEN get_row_id;
301 FETCH get_row_id INTO l_row_id;
302 CLOSE get_row_id;
303 END IF;
304
305 delete from igw_budgets
306 where rowid = l_row_id
307 and record_version_number = p_record_version_number;
308
309 if (sql%notfound) then
310 FND_MESSAGE.SET_NAME('IGW','IGW_SS_RECORD_CHANGED');
311 FND_MSG_PUB.Add;
312 X_RETURN_STATUS := 'E';
313 end if;
314
315 EXCEPTION
316 WHEN OTHERS THEN
317 FND_MSG_PUB.add_exc_msg( p_pkg_name => G_package_name
318 ,p_procedure_name => 'DELETE_ROW' );
319 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
320 RAISE;
321 end DELETE_ROW;
322
323
324 END IGW_BUDGETS_TBH;