[Home] [Help]
PACKAGE BODY: APPS.PSB_WS_LINE_YEAR_O_PVT
Source
1 PACKAGE BODY PSB_WS_LINE_YEAR_O_PVT AS
2 /* $Header: PSBWLYOB.pls 120.3 2005/09/23 08:40:26 shtripat ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PSB_WS_LINE_YEAR_O_PVT';
5
6 /*==========================================================================+
7 | PROCEDURE Update_Row |
8 +==========================================================================*/
9
10 -- Bug#4571412.
11 -- Added p_year_name_C1..12 parameters to
12 -- pass Budget Year name to Create_Notes API.
13
14 PROCEDURE Update_Row
15 (
16 p_api_version IN NUMBER,
17 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
18 p_commit IN VARCHAR2 := FND_API.G_FALSE,
19 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
20 p_return_status OUT NOCOPY VARCHAR2,
21 p_msg_count OUT NOCOPY NUMBER,
22 p_msg_data OUT NOCOPY VARCHAR2,
23 --
24 p_worksheet_id IN NUMBER,
25 p_position_line_id IN NUMBER,
26 p_element_set_id IN NUMBER,
27 p_salary_account_line IN VARCHAR2,
28 p_budget_group_id IN NUMBER,
29 p_service_package_id IN NUMBER,
30 p_flex_code IN NUMBER,
31 p_concatenated_segments IN VARCHAR2,
32 p_currency_code IN VARCHAR2,
33 p_note IN VARCHAR2,
34 p_sbi_year_option_year_id IN NUMBER,
35 p_column_count IN NUMBER,
36 --
37 p_wal_id_C1 IN NUMBER,
38 p_year_id_C1 IN NUMBER,
39 p_year_name_C1 IN VARCHAR2,
40 p_balance_type_C1 IN VARCHAR2,
41 p_ytd_amount_C1 IN NUMBER,
42 p_wal_id_C2 IN NUMBER,
43 p_year_id_C2 IN NUMBER,
44 p_year_name_C2 IN VARCHAR2,
45 p_balance_type_C2 IN VARCHAR2,
46 p_ytd_amount_C2 IN NUMBER,
47 p_wal_id_C3 IN NUMBER,
48 p_year_id_C3 IN NUMBER,
49 p_year_name_C3 IN VARCHAR2,
50 p_balance_type_C3 IN VARCHAR2,
51 p_ytd_amount_C3 IN NUMBER,
52 p_wal_id_C4 IN NUMBER,
53 p_year_id_C4 IN NUMBER,
54 p_year_name_C4 IN VARCHAR2,
55 p_balance_type_C4 IN VARCHAR2,
56 p_ytd_amount_C4 IN NUMBER,
57 p_wal_id_C5 IN NUMBER,
58 p_year_id_C5 IN NUMBER,
59 p_year_name_C5 IN VARCHAR2,
60 p_balance_type_C5 IN VARCHAR2,
61 p_ytd_amount_C5 IN NUMBER,
62 p_wal_id_C6 IN NUMBER,
63 p_year_id_C6 IN NUMBER,
64 p_year_name_C6 IN VARCHAR2,
65 p_balance_type_C6 IN VARCHAR2,
66 p_ytd_amount_C6 IN NUMBER,
67 p_wal_id_C7 IN NUMBER,
68 p_year_id_C7 IN NUMBER,
69 p_year_name_C7 IN VARCHAR2,
70 p_balance_type_C7 IN VARCHAR2,
71 p_ytd_amount_C7 IN NUMBER,
72 p_wal_id_C8 IN NUMBER,
73 p_year_id_C8 IN NUMBER,
74 p_year_name_C8 IN VARCHAR2,
75 p_balance_type_C8 IN VARCHAR2,
76 p_ytd_amount_C8 IN NUMBER,
77 p_wal_id_C9 IN NUMBER,
78 p_year_id_C9 IN NUMBER,
79 p_year_name_C9 IN VARCHAR2,
80 p_balance_type_C9 IN VARCHAR2,
81 p_ytd_amount_C9 IN NUMBER,
82 p_wal_id_C10 IN NUMBER,
83 p_year_id_C10 IN NUMBER,
84 p_year_name_C10 IN VARCHAR2,
85 p_balance_type_C10 IN VARCHAR2,
86 p_ytd_amount_C10 IN NUMBER,
87 p_wal_id_C11 IN NUMBER,
88 p_year_id_C11 IN NUMBER,
89 p_year_name_C11 IN VARCHAR2,
90 p_balance_type_C11 IN VARCHAR2,
91 p_ytd_amount_C11 IN NUMBER,
92 p_wal_id_C12 IN NUMBER,
93 p_year_id_C12 IN NUMBER,
94 p_year_name_C12 IN VARCHAR2,
95 p_balance_type_C12 IN VARCHAR2,
96 p_ytd_amount_C12 IN NUMBER
97 )
98 IS
99 --
100 l_api_name CONSTANT VARCHAR2(30) := 'Update_Row';
101 l_api_version CONSTANT NUMBER := 1.0;
102 --
103 --
104 l_period_amount PSB_WS_ACCT1.g_prdamt_tbl_type;
105 l_index BINARY_INTEGER;
106
107
108 --
109 l_wal_id NUMBER;
110 l_budget_year_id NUMBER;
111 l_year_type VARCHAR2(2);
112 l_balance_type VARCHAR2(1);
113 l_ytd_amount NUMBER;
114 --
115 l_return_status VARCHAR2(1);
116 --
117 l_account_line_id NUMBER;
118
119 l_budget_year_name VARCHAR2(100);
120
121 BEGIN
122 --
123 SAVEPOINT Update_Row_Pvt ;
124 --
125 IF NOT FND_API.Compatible_API_Call ( l_api_version,
126 p_api_version,
127 l_api_name,
128 G_PKG_NAME )
129 THEN
130 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
131 END IF;
132 --
133
134 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
135 FND_MSG_PUB.initialize ;
136 END IF;
137 --
138 p_return_status := FND_API.G_RET_STS_SUCCESS ;
139 --
140 --
141
142 -- Bug#4571412.
143 -- Get Budget Year Name.
144 FOR i in 1..p_column_count LOOP
145 IF i = 1 THEN
146 l_wal_id := p_wal_id_C1;
147 l_budget_year_id := p_year_id_C1;
148 l_budget_year_name := p_year_name_C1;
149 l_balance_type := p_balance_type_C1;
150 l_ytd_amount := p_ytd_amount_C1;
151 ELSIF i =2 THEN
152 l_wal_id := p_wal_id_C2;
153 l_budget_year_id := p_year_id_C2;
154 l_budget_year_name := p_year_name_C2;
155 l_balance_type := p_balance_type_C2;
156 l_ytd_amount := p_ytd_amount_C2;
157 ELSIF i =3 THEN
158 l_wal_id := p_wal_id_C3;
159 l_budget_year_id := p_year_id_C3;
160 l_budget_year_name := p_year_name_C3;
161 l_balance_type := p_balance_type_C3;
162 l_ytd_amount := p_ytd_amount_C3;
163 ELSIF i =4 THEN
164 l_wal_id := p_wal_id_C4;
165 l_budget_year_id := p_year_id_C4;
166 l_budget_year_name := p_year_name_C4;
167 l_balance_type := p_balance_type_C4;
168 l_ytd_amount := p_ytd_amount_C4;
169 ELSIF i =5 THEN
170 l_wal_id := p_wal_id_C5;
171 l_budget_year_id := p_year_id_C5;
172 l_budget_year_name := p_year_name_C5;
173 l_balance_type := p_balance_type_C5;
174 l_ytd_amount := p_ytd_amount_C5;
175 ELSIF i =6 THEN
176 l_wal_id := p_wal_id_C6;
177 l_budget_year_id := p_year_id_C6;
178 l_budget_year_name := p_year_name_C6;
179 l_balance_type := p_balance_type_C6;
180 l_ytd_amount := p_ytd_amount_C6;
181 ELSIF i =7 THEN
182 l_wal_id := p_wal_id_C7;
183 l_budget_year_id := p_year_id_C7;
184 l_budget_year_name := p_year_name_C7;
185 l_balance_type := p_balance_type_C7;
186 l_ytd_amount := p_ytd_amount_C7;
187 ELSIF i =8 THEN
188 l_wal_id := p_wal_id_C8;
189 l_budget_year_id := p_year_id_C8;
190 l_budget_year_name := p_year_name_C8;
191 l_balance_type := p_balance_type_C8;
192 l_ytd_amount := p_ytd_amount_C8;
193 ELSIF i =9 THEN
194 l_wal_id := p_wal_id_C9;
195 l_budget_year_id := p_year_id_C9;
196 l_budget_year_name := p_year_name_C9;
197 l_balance_type := p_balance_type_C9;
198 l_ytd_amount := p_ytd_amount_C9;
199 ELSIF i =10 THEN
200 l_wal_id := p_wal_id_C10;
201 l_budget_year_id := p_year_id_C10;
202 l_budget_year_name := p_year_name_C10;
203 l_balance_type := p_balance_type_C10;
204 l_ytd_amount := p_ytd_amount_C10;
205 ELSIF i =11 THEN
206 l_wal_id := p_wal_id_C11;
207 l_budget_year_id := p_year_id_C11;
208 l_budget_year_name := p_year_name_C11;
209 l_balance_type := p_balance_type_C11;
210 l_ytd_amount := p_ytd_amount_C11;
211 ELSIF i =12 THEN
212 l_wal_id := p_wal_id_C12;
213 l_budget_year_id := p_year_id_C12;
214 l_budget_year_name := p_year_name_C12;
215 l_balance_type := p_balance_type_C12;
216 l_ytd_amount := p_ytd_amount_C12;
217 END IF;
218
219 -- Initialize the table
220 FOR l_index in 1..PSB_WS_ACCT1.G_MAX_NUM_AMOUNTS LOOP
221 l_period_amount(l_index) := NULL;
222 END LOOP;
223
224
225 -- amount types can be B-Budget, A-Actuals, E- Estimate, F -FTE
226 -- Update rows only for current and proposed years
227 -- and only when amount type is not FTE
228 IF l_balance_type = 'E' THEN
229
230 -- user enters a value for an year for which no row currently exists
231 -- create row
232 IF nvl(l_wal_id,0) = 0 and nvl(l_ytd_amount,0) <> 0 then
233
234 IF p_position_line_id IS NULL THEN
235
236 PSB_WS_ACCT_PVT.Create_Account_Dist
237 (
238 p_api_version => 1.0,
239 p_init_msg_list => FND_API.G_FALSE,
240 p_commit => FND_API.G_FALSE,
241 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
242 p_return_status => l_return_status,
243 p_msg_count => p_msg_count,
244 p_msg_data => p_msg_data,
245 p_account_line_id => l_account_line_id,
246 p_worksheet_id => p_worksheet_id,
247 p_map_accounts => TRUE,
248 p_budget_year_id => l_budget_year_id,
249 p_budget_group_id => p_budget_group_id,
250 p_flex_code => p_flex_code,
251 p_concatenated_segments => p_concatenated_segments,
252 p_currency_code => p_currency_code,
253 p_balance_type => 'E',
254 p_ytd_amount => l_ytd_amount,
255 p_distribute_flag => FND_API.G_TRUE,
256 p_period_amount => l_period_amount,
257 p_service_package_id => p_service_package_id
258 );
259
260 IF NVL(p_sbi_year_option_year_id,l_budget_year_id) = l_budget_year_id
261 THEN
262 if p_note is not null then
263 begin
264 -- Bug#4571412
265 -- Adding parameters to the call to make it sync
266 -- with it's definiiton.
267 PSB_WS_ACCT1.Create_Note
268 (p_return_status => l_return_status,
269 p_account_line_id => l_account_line_id,
270 p_note => p_note,
271 p_chart_of_accounts_id => NULL,
272 p_budget_year => l_budget_year_name,
273 p_cc_id => NULL,
274 p_concatenated_segments => p_concatenated_segments
275 );
276 end;
277 end if;
278 END IF ;
279
280 ELSE
281 PSB_WS_ACCT_PVT.Create_Account_Dist
282 (
283 p_api_version => 1.0,
284 p_init_msg_list => FND_API.G_FALSE,
285 p_commit => FND_API.G_FALSE,
286 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
287 p_return_status => l_return_status,
288 p_msg_count => p_msg_count,
289 p_msg_data => p_msg_data,
290 p_account_line_id => l_account_line_id,
291 p_worksheet_id => p_worksheet_id,
292 p_map_accounts => TRUE,
293 p_budget_year_id => l_budget_year_id,
294 p_budget_group_id => p_budget_group_id,
295 p_flex_code => p_flex_code,
296 p_concatenated_segments => p_concatenated_segments,
297 p_currency_code => p_currency_code,
298 p_balance_type => 'E',
299 p_ytd_amount => l_ytd_amount,
300 p_distribute_flag => FND_API.G_TRUE,
301 p_period_amount => l_period_amount,
302 p_position_line_id => p_position_line_id,
303 p_element_set_id => p_element_set_id,
304 p_salary_account_line => p_salary_account_line,
305 p_service_package_id => p_service_package_id
306 );
307 END IF;
308
309 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
310 RAISE FND_API.G_EXC_ERROR ;
311 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
312 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
313 END IF;
314
315
316 ELSIF nvl(l_wal_id,0) <> 0 THEN --update row
317
318 PSB_WS_ACCT_PVT.Create_Account_Dist
319 (
320 p_api_version => 1.0,
321 p_init_msg_list => FND_API.G_FALSE,
322 p_commit => FND_API.G_FALSE,
323 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
324 p_return_status => l_return_status,
325 p_msg_count => p_msg_count,
326 p_msg_data => p_msg_data,
327 p_distribute_flag => FND_API.G_TRUE,
328 p_worksheet_id => p_worksheet_id,
329 p_account_line_id => l_wal_id,
330 p_service_package_id => p_service_package_id,
331 p_ytd_amount => l_ytd_amount,
332 p_period_amount => l_period_amount
333 );
334
335 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
336 RAISE FND_API.G_EXC_ERROR ;
337 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
338 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
339 END IF;
340
341 IF NVL(p_sbi_year_option_year_id,l_budget_year_id) = l_budget_year_id
342 THEN
343 if p_note is not null then
344 begin
345 -- Bug#4571412
346 -- Adding parameters to the call to make it sync
347 -- with it's definiiton.
348 PSB_WS_ACCT1.Create_Note
349 (p_return_status => l_return_status,
350 p_account_line_id => l_account_line_id,
351 p_note => p_note,
352 p_chart_of_accounts_id => NULL,
353 p_budget_year => l_budget_year_name,
354 p_cc_id => NULL,
355 p_concatenated_segments => p_concatenated_segments
356 );
357 end;
358 end if;
359 END IF;
360
361 END IF;
362
363 END IF;
364
365 END LOOP;
366 --
367 IF FND_API.To_Boolean ( p_commit ) THEN
368 COMMIT WORK;
369 END iF;
370 --
371 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
372 p_data => p_msg_data );
373 --
374 EXCEPTION
375 --
376 WHEN FND_API.G_EXC_ERROR THEN
377 --
378 ROLLBACK TO Update_Row_Pvt ;
379 p_return_status := FND_API.G_RET_STS_ERROR;
380 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
381 p_data => p_msg_data );
382 --
383 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
384 --
385 ROLLBACK TO Update_Row_Pvt ;
386 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
387 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
388 p_data => p_msg_data );
389 --
390 WHEN OTHERS THEN
391 --
392 ROLLBACK TO Update_Row_Pvt ;
393 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
394 --
395 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
396 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
397 l_api_name);
398 END if;
399 --
400 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
401 p_data => p_msg_data );
402 --
403 END Update_Row;
404 /* ----------------------------------------------------------------------- */
405
406
407 END PSB_WS_LINE_YEAR_O_PVT;