[Home] [Help]
PACKAGE BODY: APPS.IGW_AWARDS_TBH
Source
1 PACKAGE BODY Igw_Awards_Tbh AS
2 /* $Header: igwtawib.pls 115.3 2002/11/15 18:23:02 vmedikon noship $ */
3
4 ---------------------------------------------------------------------------
5
6 G_PKG_NAME VARCHAR2(30) := 'IGW_AWARDS_TBH';
7
8 ---------------------------------------------------------------------------
9
10 PROCEDURE Insert_Row
11 (
12 x_rowid OUT NOCOPY VARCHAR2,
13 x_proposal_award_id OUT NOCOPY NUMBER,
14 p_proposal_id IN NUMBER,
15 p_award_template_id IN NUMBER,
16 p_award_id IN NUMBER,
17 p_award_number IN VARCHAR2,
18 p_award_short_name IN VARCHAR2,
19 p_award_full_name IN VARCHAR2,
20 p_funding_source_id IN NUMBER,
21 p_funding_source_award_number IN VARCHAR2,
22 p_start_date IN DATE,
23 p_end_date IN DATE,
24 p_close_date IN DATE,
25 p_award_type IN VARCHAR2,
26 p_award_purpose_code IN VARCHAR2,
27 p_award_organization_id IN NUMBER,
28 p_award_status_code IN VARCHAR2,
29 p_award_manager_id IN NUMBER,
30 p_revenue_distribution_rule IN VARCHAR2,
31 p_billing_distribution_rule IN VARCHAR2,
32 p_billing_term_id IN NUMBER,
33 p_billing_cycle_id IN NUMBER,
34 p_labor_invoice_format_id IN NUMBER,
35 p_non_labor_invoice_format_id IN NUMBER,
36 p_allowable_schedule_id IN NUMBER,
37 p_indirect_schedule_id IN NUMBER,
38 p_amount_type_code IN VARCHAR2,
39 p_boundary_code IN VARCHAR2,
40 p_transfer_as IN VARCHAR2,
41 p_transferred_flag IN VARCHAR2,
42 p_attribute_category IN VARCHAR2,
43 p_attribute1 IN VARCHAR2,
44 p_attribute2 IN VARCHAR2,
45 p_attribute3 IN VARCHAR2,
46 p_attribute4 IN VARCHAR2,
47 p_attribute5 IN VARCHAR2,
48 p_attribute6 IN VARCHAR2,
49 p_attribute7 IN VARCHAR2,
50 p_attribute8 IN VARCHAR2,
51 p_attribute9 IN VARCHAR2,
52 p_attribute10 IN VARCHAR2,
53 p_attribute11 IN VARCHAR2,
54 p_attribute12 IN VARCHAR2,
55 p_attribute13 IN VARCHAR2,
56 p_attribute14 IN VARCHAR2,
57 p_attribute15 IN VARCHAR2,
58 x_return_status OUT NOCOPY VARCHAR2,
59 p_mode IN VARCHAR2
60 ) IS
61
62 l_api_name CONSTANT VARCHAR2(30) := 'Insert_Row';
63
64 l_last_update_date DATE := SYSDATE;
65 l_last_updated_by NUMBER := Nvl(Fnd_Global.User_Id,-1);
66 l_last_update_login NUMBER := Nvl(Fnd_Global.Login_Id,-1);
67
68 CURSOR c IS
69 SELECT rowid
70 FROM igw_awards
71 WHERE proposal_award_id = x_proposal_award_id;
72
73 BEGIN
74
75 x_return_status := Fnd_Api.G_Ret_Sts_Success;
76
77 IF p_mode = 'I' THEN
78
79 l_last_updated_by := 1;
80 l_last_update_login := 0;
81
82 ELSIF p_mode <> 'R' THEN
83
84 Fnd_Message.Set_Name('FND','SYSTEM-INVALID ARGS');
85 App_Exception.Raise_Exception;
86
87 END IF;
88
89 INSERT INTO igw_awards
90 (
91 proposal_award_id,
92 proposal_id,
93 award_template_id,
94 award_id,
95 award_number,
96 award_short_name,
97 award_full_name,
98 funding_source_id,
99 funding_source_award_number,
100 start_date,
101 end_date,
102 close_date,
103 award_type,
104 award_purpose_code,
105 award_organization_id,
106 award_status_code,
107 award_manager_id,
108 revenue_distribution_rule,
109 billing_distribution_rule,
110 billing_term_id,
111 billing_cycle_id,
112 labor_invoice_format_id,
113 non_labor_invoice_format_id,
114 allowable_schedule_id,
115 indirect_schedule_id,
116 amount_type_code,
117 boundary_code,
118 transfer_as,
119 transferred_flag,
120 attribute_category,
121 attribute1,
122 attribute2,
123 attribute3,
124 attribute4,
125 attribute5,
126 attribute6,
127 attribute7,
128 attribute8,
129 attribute9,
130 attribute10,
131 attribute11,
132 attribute12,
133 attribute13,
134 attribute14,
135 attribute15,
136 record_version_number,
137 creation_date,
138 created_by,
139 last_update_date,
140 last_updated_by,
141 last_update_login
142 )
143 VALUES
144 (
145 igw_awards_s.nextval,
146 p_proposal_id,
147 p_award_template_id,
148 p_award_id,
149 p_award_number,
150 p_award_short_name,
151 p_award_full_name,
152 p_funding_source_id,
153 p_funding_source_award_number,
154 p_start_date,
155 p_end_date,
156 p_close_date,
157 p_award_type,
158 p_award_purpose_code,
159 p_award_organization_id,
160 p_award_status_code,
161 p_award_manager_id,
162 p_revenue_distribution_rule,
163 p_billing_distribution_rule,
164 p_billing_term_id,
165 p_billing_cycle_id,
166 p_labor_invoice_format_id,
167 p_non_labor_invoice_format_id,
168 p_allowable_schedule_id,
169 p_indirect_schedule_id,
170 p_amount_type_code,
171 p_boundary_code,
172 p_transfer_as,
173 p_transferred_flag,
174 p_attribute_category,
175 p_attribute1,
176 p_attribute2,
177 p_attribute3,
178 p_attribute4,
179 p_attribute5,
180 p_attribute6,
181 p_attribute7,
182 p_attribute8,
183 p_attribute9,
184 p_attribute10,
185 p_attribute11,
186 p_attribute12,
187 p_attribute13,
188 p_attribute14,
189 p_attribute15,
190 1,
191 l_last_update_date,
192 l_last_updated_by,
193 l_last_update_date,
194 l_last_updated_by,
195 l_last_update_login
196 )
197 RETURNING
198 proposal_award_id
199 INTO
200 x_proposal_award_id;
201
202 OPEN c;
203 FETCH c INTO x_rowid;
204
205 IF c%NotFound THEN
206
207 CLOSE c;
208 RAISE no_data_found;
209
210 END IF;
211
212 CLOSE c;
213
214 EXCEPTION
215
216 WHEN others THEN
217
218 x_return_status := Fnd_Api.G_Ret_Sts_Unexp_Error;
219
220 Fnd_Msg_Pub.Add_Exc_Msg
221 (
222 p_pkg_name => G_PKG_NAME,
223 p_procedure_name => l_api_name
224 );
225
226 RAISE Fnd_Api.G_Exc_Unexpected_Error;
227
228 END Insert_Row;
229
230 ---------------------------------------------------------------------------
231
232 PROCEDURE Update_Row
233 (
234 p_rowid IN VARCHAR2,
235 p_proposal_award_id IN NUMBER,
236 p_proposal_id IN NUMBER,
237 p_award_template_id IN NUMBER,
238 p_award_id IN NUMBER,
239 p_award_number IN VARCHAR2,
240 p_award_short_name IN VARCHAR2,
241 p_award_full_name IN VARCHAR2,
242 p_funding_source_id IN NUMBER,
243 p_funding_source_award_number IN VARCHAR2,
244 p_start_date IN DATE,
245 p_end_date IN DATE,
246 p_close_date IN DATE,
247 p_award_type IN VARCHAR2,
248 p_award_purpose_code IN VARCHAR2,
249 p_award_organization_id IN NUMBER,
250 p_award_status_code IN VARCHAR2,
251 p_award_manager_id IN NUMBER,
252 p_revenue_distribution_rule IN VARCHAR2,
253 p_billing_distribution_rule IN VARCHAR2,
254 p_billing_term_id IN NUMBER,
255 p_billing_cycle_id IN NUMBER,
256 p_labor_invoice_format_id IN NUMBER,
257 p_non_labor_invoice_format_id IN NUMBER,
258 p_allowable_schedule_id IN NUMBER,
259 p_indirect_schedule_id IN NUMBER,
260 p_amount_type_code IN VARCHAR2,
261 p_boundary_code IN VARCHAR2,
262 p_transfer_as IN VARCHAR2,
263 p_transferred_flag IN VARCHAR2,
264 p_attribute_category IN VARCHAR2,
265 p_attribute1 IN VARCHAR2,
266 p_attribute2 IN VARCHAR2,
267 p_attribute3 IN VARCHAR2,
268 p_attribute4 IN VARCHAR2,
269 p_attribute5 IN VARCHAR2,
270 p_attribute6 IN VARCHAR2,
271 p_attribute7 IN VARCHAR2,
272 p_attribute8 IN VARCHAR2,
273 p_attribute9 IN VARCHAR2,
274 p_attribute10 IN VARCHAR2,
275 p_attribute11 IN VARCHAR2,
276 p_attribute12 IN VARCHAR2,
277 p_attribute13 IN VARCHAR2,
278 p_attribute14 IN VARCHAR2,
279 p_attribute15 IN VARCHAR2,
280 x_return_status OUT NOCOPY VARCHAR2,
281 p_mode IN VARCHAR2
282 ) IS
283
284 l_api_name CONSTANT VARCHAR2(30) := 'Update_Row';
285
286 l_last_update_date DATE := SYSDATE;
287 l_last_updated_by NUMBER := Nvl(Fnd_Global.User_Id,-1);
288 l_last_update_login NUMBER := Nvl(Fnd_Global.Login_Id,-1);
289
290 BEGIN
291
292 x_return_status := Fnd_Api.G_Ret_Sts_Success;
293
294 IF p_mode = 'I' THEN
295
296 l_last_updated_by := 1;
297 l_last_update_login := 0;
298
299 ELSIF p_mode <> 'R' THEN
300
301 Fnd_Message.Set_Name('FND','SYSTEM-INVALID ARGS');
302 Fnd_Msg_Pub.Add;
303 App_Exception.Raise_Exception;
304
305 END IF;
306
307 UPDATE igw_awards
308 SET proposal_id = p_proposal_id,
309 award_template_id = p_award_template_id,
310 award_number = p_award_number,
311 award_short_name = p_award_short_name,
312 award_full_name = p_award_full_name,
313 funding_source_id = p_funding_source_id,
314 funding_source_award_number = p_funding_source_award_number,
315 start_date = p_start_date,
316 end_date = p_end_date,
317 close_date = p_close_date,
318 award_type = p_award_type,
319 award_purpose_code = p_award_purpose_code,
320 award_organization_id = p_award_organization_id,
321 award_status_code = p_award_status_code,
322 award_manager_id = p_award_manager_id,
323 revenue_distribution_rule = p_revenue_distribution_rule,
324 billing_distribution_rule = p_billing_distribution_rule,
325 billing_term_id = p_billing_term_id,
326 billing_cycle_id = p_billing_cycle_id,
327 labor_invoice_format_id = p_labor_invoice_format_id,
328 non_labor_invoice_format_id = p_non_labor_invoice_format_id,
329 allowable_schedule_id = p_allowable_schedule_id,
330 indirect_schedule_id = p_indirect_schedule_id,
331 amount_type_code = p_amount_type_code,
332 boundary_code = p_boundary_code,
333 transfer_as = p_transfer_as,
334 attribute_category = p_attribute_category,
335 attribute1 = p_attribute1,
336 attribute2 = p_attribute2,
337 attribute3 = p_attribute3,
338 attribute4 = p_attribute4,
339 attribute5 = p_attribute5,
340 attribute6 = p_attribute6,
341 attribute7 = p_attribute7,
342 attribute8 = p_attribute8,
343 attribute9 = p_attribute9,
344 attribute10 = p_attribute10,
345 attribute11 = p_attribute11,
346 attribute12 = p_attribute12,
347 attribute13 = p_attribute13,
348 attribute14 = p_attribute14,
349 attribute15 = p_attribute15,
350 record_version_number = record_version_number + 1,
351 last_update_date = l_last_update_date,
352 last_updated_by = l_last_updated_by,
353 last_update_login = l_last_update_login
354 WHERE (rowid = p_rowid OR proposal_award_id = p_proposal_award_id);
355
356 IF SQL%NotFound THEN
357
358 Fnd_Message.Set_Name('IGW','IGW_SS_RECORD_CHANGED');
359 Fnd_Msg_Pub.Add;
360 App_Exception.Raise_Exception;
361
362 END IF;
363
364 EXCEPTION
365
366 WHEN others THEN
367
368 x_return_status := Fnd_Api.G_Ret_Sts_Unexp_Error;
369
370 Fnd_Msg_Pub.Add_Exc_Msg
371 (
372 p_pkg_name => G_PKG_NAME,
373 p_procedure_name => l_api_name
374 );
375
376 RAISE Fnd_Api.G_Exc_Unexpected_Error;
377
378 END Update_Row;
379
380 ---------------------------------------------------------------------------
381
382 PROCEDURE Delete_Row
383 (
384 p_proposal_award_id IN NUMBER,
385 x_return_status OUT NOCOPY VARCHAR2
386 ) IS
387
388 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Row';
389
390 BEGIN
391
392 x_return_status := Fnd_Api.G_Ret_Sts_Success;
393
394 DELETE igw_project_fundings
395 WHERE proposal_installment_id IN
396 ( SELECT proposal_installment_id
397 FROM igw_installments
398 WHERE proposal_award_id = p_proposal_award_id );
399
400 DELETE igw_installments
401 WHERE proposal_award_id = p_proposal_award_id;
402
403 DELETE igw_awards
404 WHERE proposal_award_id = p_proposal_award_id;
405
406 EXCEPTION
407
408 WHEN others THEN
409
410 x_return_status := Fnd_Api.G_Ret_Sts_Unexp_Error;
411
412 Fnd_Msg_Pub.Add_Exc_Msg
413 (
414 p_pkg_name => G_PKG_NAME,
415 p_procedure_name => l_api_name
416 );
417
418 RAISE Fnd_Api.G_Exc_Unexpected_Error;
419
420 END Delete_Row;
421
422 ---------------------------------------------------------------------------
423
424 END Igw_Awards_Tbh;