[Home] [Help]
PACKAGE BODY: APPS.IGW_BUDGET_DETAILS_TBH
Source
1 PACKAGE BODY IGW_BUDGET_DETAILS_TBH AS
2 --$Header: igwtbdtb.pls 115.4 2002/11/14 18:40:00 vmedikon ship $
3
4 procedure INSERT_ROW (
5 p_proposal_id NUMBER
6 ,p_version_id NUMBER
7 ,p_budget_period_id NUMBER
8 ,p_line_item_id NUMBER
9 ,p_expenditure_type VARCHAR2
10 ,p_expenditure_category_flag VARCHAR2
11 ,p_budget_category_code VARCHAR2
12 ,p_line_item_description VARCHAR2
13 ,p_based_on_line_item NUMBER
14 ,p_line_item_cost NUMBER
15 ,p_cost_sharing_amount NUMBER
16 ,p_underrecovery_amount NUMBER
17 ,p_apply_inflation_flag VARCHAR2
18 ,p_budget_justification LONG
19 ,p_location_code VARCHAR2
20 ,p_project_id NUMBER
21 ,p_task_id NUMBER
22 ,p_award_id NUMBER
23 ,x_rowid OUT NOCOPY VARCHAR2
24 ,x_return_status OUT NOCOPY VARCHAR2) IS
25
26 cursor c_budget_line is
27 select rowid
28 from igw_budget_details
29 where proposal_id = p_proposal_id
30 and version_id = p_version_id
31 and budget_period_id = p_budget_period_id
32 and line_item_id = p_line_item_id;
33
34 l_last_updated_by NUMBER := FND_GLOBAL.USER_ID;
35 l_last_update_login NUMBER := FND_GLOBAL.LOGIN_ID;
36 l_last_update_date DATE := SYSDATE;
37
38 begin
39 x_return_status := FND_API.G_RET_STS_SUCCESS;
40
41 insert into igw_budget_details(
42 proposal_id
43 ,version_id
44 ,budget_period_id
45 ,line_item_id
46 ,expenditure_type
47 ,expenditure_category_flag
48 ,budget_category_code
49 ,line_item_description
50 ,based_on_line_item
51 ,line_item_cost
52 ,cost_sharing_amount
53 ,underrecovery_amount
54 ,apply_inflation_flag
55 ,budget_justification
56 ,location_code
57 ,project_id
58 ,task_id
59 ,award_id
60 ,last_update_date
61 ,last_updated_by
62 ,creation_date
63 ,created_by
64 ,last_update_login
65 ,record_version_number)
66 values
67 ( p_proposal_id
68 ,p_version_id
69 ,p_budget_period_id
70 ,p_line_item_id
71 ,p_expenditure_type
72 ,p_expenditure_category_flag
73 ,p_budget_category_code
74 ,p_line_item_description
75 ,p_based_on_line_item
76 ,p_line_item_cost
77 ,p_cost_sharing_amount
78 ,p_underrecovery_amount
79 ,p_apply_inflation_flag
80 ,p_budget_justification
81 ,p_location_code
82 ,p_project_id
83 ,p_task_id
84 ,p_award_id
85 ,l_last_update_date
86 ,l_last_updated_by
87 ,l_last_update_date
88 ,l_last_updated_by
89 ,l_last_update_login
90 ,1);
91
92 open c_budget_line;
93 fetch c_budget_line into x_ROWID;
94 if (c_budget_line%notfound) then
95 close c_budget_line;
96 raise no_data_found;
97 end if;
98 close c_budget_line;
99
100 exception
101 when others then
102 FND_MSG_PUB.add_exc_msg( p_pkg_name => G_package_name
103 ,p_procedure_name => 'INSERT_ROW' );
104 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
105 RAISE;
106 end insert_row;
107
108
109 -----------------------------------------------------------------------------------
110 procedure UPDATE_ROW (
111 p_rowid ROWID
112 ,p_proposal_id NUMBER
113 ,p_version_id NUMBER
114 ,p_budget_period_id NUMBER
115 ,p_line_item_id NUMBER
116 ,p_expenditure_type VARCHAR2
117 ,p_expenditure_category_flag VARCHAR2
118 ,p_budget_category_code VARCHAR2
119 ,p_line_item_description VARCHAR2
120 ,p_based_on_line_item NUMBER
121 ,p_line_item_cost NUMBER
122 ,p_cost_sharing_amount NUMBER
123 ,p_underrecovery_amount NUMBER
124 ,p_apply_inflation_flag VARCHAR2
125 ,p_budget_justification LONG
126 ,p_location_code VARCHAR2
127 ,p_project_id NUMBER
128 ,p_task_id NUMBER
129 ,p_award_id NUMBER
130 ,p_record_version_number NUMBER
131 ,x_return_status OUT NOCOPY VARCHAR2) IS
132
133 l_last_updated_by NUMBER := FND_GLOBAL.USER_ID;
134 l_last_update_login NUMBER := FND_GLOBAL.LOGIN_ID;
135 l_last_update_date DATE := SYSDATE;
136
137 l_row_id ROWID := p_rowid;
138
139 CURSOR get_row_id IS
140 SELECT rowid
141 from igw_budget_details
142 where proposal_id = p_proposal_id
143 and version_id = p_version_id
144 and budget_period_id = p_budget_period_id
145 and line_item_id = p_line_item_id;
146
147 begin
148 x_return_status := FND_API.G_RET_STS_SUCCESS;
149
150 IF l_row_id IS NULL THEN
151 OPEN get_row_id;
152 FETCH get_row_id INTO l_row_id;
153 CLOSE get_row_id;
154 END IF;
155
156 update igw_budget_details
157 set expenditure_type = p_expenditure_type
158 , expenditure_category_flag = p_expenditure_category_flag
159 , budget_category_code = p_budget_category_code
160 , line_item_description = p_line_item_description
161 , based_on_line_item = p_based_on_line_item
162 , line_item_cost = p_line_item_cost
163 , cost_sharing_amount = p_cost_sharing_amount
164 , underrecovery_amount = p_underrecovery_amount
165 , apply_inflation_flag = p_apply_inflation_flag
166 , budget_justification = p_budget_justification
167 , location_code = p_location_code
168 , project_id = decode(p_project_id, FND_API.G_MISS_NUM, NULL, p_project_id)
169 , task_id = decode(p_task_id, FND_API.G_MISS_NUM, NULL, p_task_id)
170 , award_id = decode(p_award_id , FND_API.G_MISS_NUM, NULL, p_award_id)
171 , record_version_number = record_version_number + 1
172 where rowid = l_row_id
173 and record_version_number = p_record_version_number;
174
175 if (sql%notfound) then
176 FND_MESSAGE.SET_NAME('IGW','IGW_SS_RECORD_CHANGED');
177 FND_MSG_PUB.Add;
178 X_RETURN_STATUS := 'E';
179 end if;
180
181 exception
182 when others then
183 FND_MSG_PUB.add_exc_msg( p_pkg_name => G_package_name
184 ,p_procedure_name => 'UPDATE_ROW' );
185 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
186 RAISE;
187 end update_row;
188 --------------------------------------------------------------------------------------------------
189
190
191 procedure DELETE_ROW (
192 p_rowid IN ROWID
193 ,p_proposal_id IN NUMBER
194 ,p_version_id IN NUMBER
195 ,p_budget_period_id IN NUMBER
196 ,p_line_item_id NUMBER
197 ,p_record_version_number IN NUMBER
198 ,x_return_status OUT NOCOPY VARCHAR2) is
199
200 l_row_id ROWID := p_rowid;
201
202 CURSOR get_row_id IS
203 SELECT rowid
204 from igw_budget_details
205 where proposal_id = p_proposal_id
206 and version_id = p_version_id
207 and budget_period_id = p_budget_period_id
208 and line_item_id = p_line_item_id;
209
210 begin
211
212 x_return_status := FND_API.G_RET_STS_SUCCESS;
213
214 IF l_row_id IS NULL THEN
215 OPEN get_row_id;
216 FETCH get_row_id INTO l_row_id;
217 CLOSE get_row_id;
218 END IF;
219
220 delete from igw_budget_details
221 where rowid = l_row_id
222 and record_version_number = p_record_version_number;
223
224 if (sql%notfound) then
225 FND_MESSAGE.SET_NAME('IGW','IGW_SS_RECORD_CHANGED');
226 FND_MSG_PUB.Add;
227 X_RETURN_STATUS := 'E';
228 end if;
229
230 EXCEPTION
231 WHEN OTHERS THEN
232 FND_MSG_PUB.add_exc_msg( p_pkg_name => G_package_name
233 ,p_procedure_name => 'DELETE_ROW' );
234 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
235 RAISE;
236 end DELETE_ROW;
237
238
239 END IGW_BUDGET_DETAILS_TBH;