[Home] [Help]
PACKAGE BODY: APPS.OKL_SUBSIDY_POOL_APPROVAL_PVT
Source
1 PACKAGE BODY okl_subsidy_pool_approval_pvt AS
2 /* $Header: OKLRSICB.pls 120.1 2005/10/30 03:17:05 appldev noship $ */
3
4 -- Global Message Constants
5 G_INVALID_POOL_STATUS CONSTANT fnd_new_messages.message_name%TYPE DEFAULT 'OKL_SUB_POOL_STATUS_INVALID';
6 G_POOL_HAS_NO_LINES CONSTANT fnd_new_messages.message_name%TYPE DEFAULT 'OKL_SUB_POOL_HAS_NO_LINES';
7 G_POOL_HAS_MORE_LINES CONSTANT fnd_new_messages.message_name%TYPE DEFAULT 'OKL_SUB_POOL_HAS_MORE_LINES';
8 G_POOL_LINE_INVALID CONSTANT fnd_new_messages.message_name%TYPE DEFAULT 'OKL_SUB_POOL_LINE_INVALID';
9 G_POOL_IS_NOT_ACTIVE CONSTANT fnd_new_messages.message_name%TYPE DEFAULT 'OKL_SUB_POOL_STS_NOT_ACTIVE';
10 G_SUB_POOL_EXIPRED_WF CONSTANT fnd_new_messages.message_name%TYPE DEFAULT 'OKL_SUBSIDY_POOL_EXPIRED_WF';
11 G_POOL_LINE_INVALID_AMT CONSTANT fnd_new_messages.message_name%TYPE DEFAULT 'OKL_SUB_POOL_LINE_AMT_BALANCE';
12 G_POOL_NO_SUB_ASSOC CONSTANT fnd_new_messages.message_name%TYPE DEFAULT 'OKL_SUB_POOL_HAS_NO_SUBSIDY';
13 G_BUDGET_IS_NOT_NEW CONSTANT fnd_new_messages.message_name%TYPE DEFAULT 'OKL_SUB_POOL_HAS_NO_SUBSIDY';
14 -- Global Constants
15 G_PENDING_STATUS_CODE CONSTANT fnd_lookups.lookup_code%TYPE DEFAULT 'PENDING';
16 G_ACTIVE_STATUS_CODE CONSTANT fnd_lookups.lookup_code%TYPE DEFAULT 'ACTIVE';
17
18 PROCEDURE submit_pool_for_approval(p_api_version IN NUMBER
19 ,p_init_msg_list IN VARCHAR2
20 ,x_return_status OUT NOCOPY VARCHAR2
21 ,x_msg_count OUT NOCOPY NUMBER
22 ,x_msg_data OUT NOCOPY VARCHAR2
23 ,p_subsidy_pool_id IN okl_subsidy_pools_b.id%TYPE
24 ,x_pool_status OUT NOCOPY okl_subsidy_pools_b.decision_status_code%TYPE
25 ,x_total_budgets OUT NOCOPY okl_subsidy_pools_b.total_budgets%TYPE) IS
26 CURSOR c_get_pool_details_csr IS
27 SELECT decision_status_code
28 ,pool_type_code
29 ,subsidy_pool_name
30 ,effective_from_date
31 ,effective_to_date
32 ,id
33 FROM okl_subsidy_pools_b
34 WHERE id = p_subsidy_pool_id;
35 cv_pool_details_csr c_get_pool_details_csr%ROWTYPE;
36
37 CURSOR c_get_pool_line_number IS
38 SELECT count(*) number_of_lines
39 FROM okl_subsidy_pool_budgets_b
40 WHERE subsidy_pool_id = p_subsidy_pool_id;
41 lv_number_of_lines NUMBER;
42
43 CURSOR c_get_pool_line_details_csr IS
44 SELECT id
45 ,budget_type_code
46 ,decision_status_code
47 ,budget_amount
48 FROM okl_subsidy_pool_budgets_b
49 WHERE subsidy_pool_id = p_subsidy_pool_id;
50 cv_get_pool_line_details_csr c_get_pool_line_details_csr%ROWTYPE;
51
52 CURSOR c_chk_subsidy_assoc_csr IS
53 SELECT 'X'
54 FROM okl_subsidies_b
55 WHERE subsidy_pool_id = p_subsidy_pool_id;
56 lv_dummy_var VARCHAR2(1);
57
58 l_approval_process VARCHAR2(30);
59 l_pool_status okl_subsidy_pools_b.decision_status_code%TYPE;
60
61 l_api_version CONSTANT NUMBER DEFAULT 1.0;
62 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'SUBMIT_POOL_FOR_APPROVAL';
63
64 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_SUBSIDY_POOL_APPROVAL_PVT.SUBMIT_POOL_FOR_APPROVAL';
65 l_debug_enabled VARCHAR2(10);
66 is_debug_procedure_on BOOLEAN;
67 is_debug_statement_on BOOLEAN;
68
69 BEGIN
70 x_return_status := OKL_API.G_RET_STS_SUCCESS;
71
72 l_debug_enabled := okl_debug_pub.check_log_enabled;
73 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_PROCEDURE);
74 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
75 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRSICB.pls call submit_pool_for_approval');
76 END IF;
77
78 -- call START_ACTIVITY to create savepoint, check compatibility and initialize message list
79 x_return_status := OKL_API.START_ACTIVITY(
80 p_api_name => l_api_name
81 ,p_pkg_name => G_PKG_NAME
82 ,p_init_msg_list => p_init_msg_list
83 ,l_api_version => l_api_version
84 ,p_api_version => p_api_version
85 ,p_api_type => g_api_type
86 ,x_return_status => x_return_status);
87
88 -- check if activity started successfully
89 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
90 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
91 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
92 RAISE OKL_API.G_EXCEPTION_ERROR;
93 END IF;
94
95 -- check for logging on STATEMENT level
96 is_debug_statement_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_STATEMENT);
97
98 x_total_budgets := 0;
99 -- validate if subsidy pool id passed is valid
100 IF(p_subsidy_pool_id IS NULL OR p_subsidy_pool_id = OKL_API.G_MISS_NUM)THEN
101 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'p_subsidy_pool_id');
102 x_return_status := OKC_API.G_RET_STS_ERROR;
103 RAISE OKL_API.G_EXCEPTION_ERROR;
104 END IF;
105 -- get the subsidy pool details to process
106 OPEN c_get_pool_details_csr; FETCH c_get_pool_details_csr INTO cv_pool_details_csr;
107 CLOSE c_get_pool_details_csr;
108
109 -- if the pool status is not new, then error out. initial pool approval should have the pool status and the line status as NEW
110 IF(cv_pool_details_csr.decision_status_code <> 'NEW')THEN
111 OKC_API.set_message(G_APP_NAME, G_INVALID_POOL_STATUS, 'POOL_NAME', cv_pool_details_csr.subsidy_pool_name);
112 x_return_status := OKC_API.G_RET_STS_ERROR;
113 RAISE OKL_API.G_EXCEPTION_ERROR;
114 END IF;
115
116 -- check for pool expiration here. pool could be logically expired by the time user submits for approval.
117 -- in such a case, expire the pool and throw the error
118 IF(TRUNC(SYSDATE) > NVL(cv_pool_details_csr.effective_to_date,okl_accounting_util.g_final_date))THEN
119 okl_subsidy_pool_pvt.expire_sub_pool(p_api_version => p_api_version
120 ,p_init_msg_list => p_init_msg_list
121 ,x_return_status => x_return_status
122 ,x_msg_count => x_msg_count
123 ,x_msg_data => x_msg_data
124 ,p_subsidy_pool_id => cv_pool_details_csr.id
125 );
126 -- write to log
127 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
128 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
129 l_module,
130 'expiring subsidy pool '||cv_pool_details_csr.subsidy_pool_name||' with effective end date '||cv_pool_details_csr.effective_to_date
131 );
132 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
133
134 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR)THEN
135 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
136 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR)THEN
137 RAISE OKL_API.G_EXCEPTION_ERROR;
138 END IF;
139 -- cannot submit pool for approval while the pool is expired.
140 OKL_API.set_message(G_APP_NAME, G_SUB_POOL_EXIPRED_WF, 'POOL_NAME', cv_pool_details_csr.subsidy_pool_name);
141 RAISE OKL_API.G_EXCEPTION_ERROR;
142 END IF;
143
144 -- verify if the subsidy pool has been associated with any subsidy. for approval, the pool should be associated to
145 -- at least one subsidy
146 OPEN c_chk_subsidy_assoc_csr; FETCH c_chk_subsidy_assoc_csr INTO lv_dummy_var;
147 CLOSE c_chk_subsidy_assoc_csr;
148 IF(NVL(lv_dummy_var,'N')<> 'X')THEN
149 OKC_API.set_message(G_APP_NAME, G_POOL_NO_SUB_ASSOC, 'POOL_NAME', cv_pool_details_csr.subsidy_pool_name);
150 x_return_status := OKC_API.G_RET_STS_ERROR;
151 RAISE OKL_API.G_EXCEPTION_ERROR;
152 END IF;
153 -- get the number of subsidy pool budget lines
154 lv_number_of_lines:=0;
155 OPEN c_get_pool_line_number; FETCH c_get_pool_line_number INTO lv_number_of_lines;
156 CLOSE c_get_pool_line_number;
157 IF(lv_number_of_lines = 0)THEN
158 OKC_API.set_message(G_APP_NAME, G_POOL_HAS_NO_LINES, 'POOL_NAME', cv_pool_details_csr.subsidy_pool_name);
159 x_return_status := OKC_API.G_RET_STS_ERROR;
160 RAISE OKL_API.G_EXCEPTION_ERROR;
161 ELSIF(lv_number_of_lines > 1)THEN -- for initial pool approval, only one budget line is allowed.
162 OKC_API.set_message(G_APP_NAME, G_POOL_HAS_MORE_LINES, 'POOL_NAME', cv_pool_details_csr.subsidy_pool_name);
163 x_return_status := OKC_API.G_RET_STS_ERROR;
164 RAISE OKL_API.G_EXCEPTION_ERROR;
165 END IF;
166
167 -- get the budget line details
168 OPEN c_get_pool_line_details_csr; FETCH c_get_pool_line_details_csr INTO cv_get_pool_line_details_csr;
169 CLOSE c_get_pool_line_details_csr;
170 IF(cv_get_pool_line_details_csr.budget_type_code <> 'ADDITION' OR cv_get_pool_line_details_csr.budget_amount <= 0
171 OR cv_get_pool_line_details_csr.decision_status_code <> 'NEW')THEN
172 OKC_API.set_message(G_APP_NAME, G_POOL_LINE_INVALID, 'POOL_NAME', cv_pool_details_csr.subsidy_pool_name
173 ,'AMOUNT',cv_get_pool_line_details_csr.budget_amount);
174 x_return_status := OKC_API.G_RET_STS_ERROR;
175 RAISE OKL_API.G_EXCEPTION_ERROR;
176 END IF;
177
178 -- now that all the required validations are passed, update the pool and line status to pending approval
179 l_pool_status := G_PENDING_STATUS_CODE;
180 okl_subsidy_pool_pvt.set_decision_status_code(p_api_version => p_api_version
181 ,p_init_msg_list => p_init_msg_list
182 ,x_return_status => x_return_status
183 ,x_msg_count => x_msg_count
184 ,x_msg_data => x_msg_data
185 ,p_subsidy_pool_id => p_subsidy_pool_id
186 ,p_decision_status_code => l_pool_status
187 );
188 -- write to log
189 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
190 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
191 l_module,
192 'okl_subsidy_pool_pvt.set_decision_status_code to pending returned with status '||x_return_status|| ' x_msg_data '||x_msg_data
193 );
194 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
195
196 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
197 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
198 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
199 RAISE OKL_API.G_EXCEPTION_ERROR;
200 END IF;
201 l_pool_status := G_PENDING_STATUS_CODE;
202 okl_subsidy_pool_budget_pvt.set_decision_status_code(p_api_version => p_api_version
203 ,p_init_msg_list => p_init_msg_list
204 ,x_return_status => x_return_status
205 ,x_msg_count => x_msg_count
206 ,x_msg_data => x_msg_data
207 ,p_sub_pool_budget_id => cv_get_pool_line_details_csr.id
208 ,p_decision_status_code => l_pool_status
209 );
210 -- write to log
211 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
212 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
213 l_module,
214 'okl_subsidy_pool_budget_pvt.set_decision_status_code to pending returned with status '||x_return_status|| ' x_msg_data '||x_msg_data
215 );
216 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
217
218 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
219 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
220 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
221 RAISE OKL_API.G_EXCEPTION_ERROR;
222 END IF;
223 -- copy this value back to the out parameter once pending for approval is set successfully
224 x_pool_status := G_PENDING_STATUS_CODE;
225 -- read the profile OKL: Subsidy Pool Approval Process Access
226 l_approval_process := fnd_profile.value('OKL_SUBSIDY_POOL_APPROVAL_PROCESS');
227
228 IF(NVL(l_approval_process,'NONE')='NONE')THEN
229 -- since no approval process is selected in the profile, approve the pool by default
230 l_pool_status := G_ACTIVE_STATUS_CODE;
231 okl_subsidy_pool_pvt.set_decision_status_code(p_api_version => p_api_version
232 ,p_init_msg_list => p_init_msg_list
233 ,x_return_status => x_return_status
234 ,x_msg_count => x_msg_count
235 ,x_msg_data => x_msg_data
236 ,p_subsidy_pool_id => p_subsidy_pool_id
237 ,p_decision_status_code => l_pool_status
238 );
239 -- write to log
240 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
241 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
242 l_module,
243 'okl_subsidy_pool_pvt.set_decision_status_code to active returned with status '||x_return_status||' x_msg_data '||x_msg_data
244 );
245 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
246
247 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
248 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
249 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
250 RAISE OKL_API.G_EXCEPTION_ERROR;
251 END IF;
252 -- since no approval process is selected, approve the line by default
253 l_pool_status := G_ACTIVE_STATUS_CODE;
254 okl_subsidy_pool_budget_pvt.set_decision_status_code(p_api_version => p_api_version
255 ,p_init_msg_list => p_init_msg_list
256 ,x_return_status => x_return_status
257 ,x_msg_count => x_msg_count
258 ,x_msg_data => x_msg_data
259 ,p_sub_pool_budget_id => cv_get_pool_line_details_csr.id
260 ,p_decision_status_code => l_pool_status
261 );
262 -- write to log
263 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
264 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
265 l_module,
266 'okl_subsidy_pool_budget_pvt.set_decision_status_code to active returned with status '||x_return_status||' x_msg_data ' ||x_msg_data
267 );
268 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
269
270 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
271 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
272 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
273 RAISE OKL_API.G_EXCEPTION_ERROR;
274 END IF;
275 -- since this is the initial approval, the total budget amount equals the budget amount on the line
276 okl_subsidy_pool_pvt.update_total_budget(p_api_version => p_api_version
277 ,p_init_msg_list => p_init_msg_list
278 ,x_return_status => x_return_status
279 ,x_msg_count => x_msg_count
280 ,x_msg_data => x_msg_data
281 ,p_subsidy_pool_id => p_subsidy_pool_id
282 ,p_total_budget_amt => cv_get_pool_line_details_csr.budget_amount);
283 -- write to log
284 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
285 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
286 l_module,
287 'okl_subsidy_pool_pvt.update_total_budget returned with status '||x_return_status||' x_msg_data '||x_msg_data
288 );
289 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
290
291 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
292 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
293 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
294 RAISE OKL_API.G_EXCEPTION_ERROR;
295 END IF;
296
297 -- copy this value back to the out parameter once activated successfully
298 x_pool_status := G_ACTIVE_STATUS_CODE;
299 x_total_budgets := cv_get_pool_line_details_csr.budget_amount;
300 ELSIF(l_approval_process in ('AME','WF'))THEN
301 -- raise subsidy pool approval event, which will then process via AME or workflow
302 okl_subsidy_pool_wf.raise_pool_event_approval(p_api_version => p_api_version
303 ,p_init_msg_list => p_init_msg_list
304 ,x_return_status => x_return_status
305 ,x_msg_count => x_msg_count
306 ,x_msg_data => x_msg_data
307 ,p_subsidy_pool_id => p_subsidy_pool_id);
308 -- write to log
309 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
310 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
311 l_module,
312 'okl_subsidy_pool_wf.raise_pool_event_approval returned with status '||x_return_status||' x_msg_data '||x_msg_data
313 );
314 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
315
316 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
317 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
318 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
319 RAISE OKL_API.G_EXCEPTION_ERROR;
320 END IF;
321 END IF;
322
323 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
324 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRSICB.pls call submit_pool_for_approval');
325 END IF;
326
327 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
328
329 EXCEPTION
330 WHEN OKL_API.G_EXCEPTION_ERROR THEN
331 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
332 p_api_name => l_api_name,
333 p_pkg_name => G_PKG_NAME,
334 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
335 x_msg_count => x_msg_count,
336 x_msg_data => x_msg_data,
337 p_api_type => g_api_type);
338
339 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
340 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
341 p_api_name => l_api_name,
342 p_pkg_name => G_PKG_NAME,
343 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
344 x_msg_count => x_msg_count,
345 x_msg_data => x_msg_data,
346 p_api_type => g_api_type);
347 WHEN OTHERS THEN
348 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
349 p_api_name => l_api_name,
350 p_pkg_name => G_PKG_NAME,
351 p_exc_name => 'OTHERS',
352 x_msg_count => x_msg_count,
353 x_msg_data => x_msg_data,
354 p_api_type => g_api_type);
355
356 END submit_pool_for_approval;
357
358 PROCEDURE submit_budget_for_approval(p_api_version IN NUMBER
359 ,p_init_msg_list IN VARCHAR2
360 ,x_return_status OUT NOCOPY VARCHAR2
361 ,x_msg_count OUT NOCOPY NUMBER
362 ,x_msg_data OUT NOCOPY VARCHAR2
363 ,p_subsidy_pool_budget_id IN okl_subsidy_pool_budgets_b.id%TYPE
364 ,x_pool_budget_status OUT NOCOPY okl_subsidy_pool_budgets_b.decision_status_code%TYPE) IS
365 CURSOR c_get_pool_info_csr IS
366 SELECT pool.subsidy_pool_name
367 ,pool.decision_status_code pool_status
368 ,pool.effective_from_date pool_start_date
369 ,pool.effective_to_date pool_end_date
370 ,pool.total_budgets
371 ,pool.id pool_id
372 ,line.budget_amount
373 ,line.budget_type_code
374 ,line.decision_status_code line_status
375 ,line.id budget_line_id
376 FROM okl_subsidy_pools_b pool
377 ,okl_subsidy_pool_budgets_b line
378 WHERE pool.id = line.subsidy_pool_id
379 AND line.id = p_subsidy_pool_budget_id;
380 cv_get_pool_info c_get_pool_info_csr%ROWTYPE;
381
382 l_approval_process VARCHAR2(30);
383
384 l_budget_status okl_subsidy_pool_budgets_b.decision_status_code%TYPE;
385 lv_calc_total_budget okl_subsidy_pools_b.total_budgets%TYPE;
386
387 l_api_version CONSTANT NUMBER DEFAULT 1.0;
388 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'SUBMIT_BUDGET_APPROVAL';
389
390 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_SUBSIDY_POOL_APPROVAL_PVT.SUBMIT_BUDGET_FOR_APPROVAL';
391 l_debug_enabled VARCHAR2(10);
392 l_level_procedure fnd_log_messages.log_level%TYPE;
393 is_debug_procedure_on BOOLEAN;
394 is_debug_statement_on BOOLEAN;
395
396 BEGIN
397 x_return_status := OKL_API.G_RET_STS_SUCCESS;
398
399 l_debug_enabled := okl_debug_pub.check_log_enabled;
400 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_PROCEDURE);
401 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
402 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRSICB.pls call submit_budget_for_approval');
403 END IF;
404
405 -- call START_ACTIVITY to create savepoint, check compatibility and initialize message list
406 x_return_status := OKL_API.START_ACTIVITY(
407 p_api_name => l_api_name
408 ,p_pkg_name => G_PKG_NAME
409 ,p_init_msg_list => p_init_msg_list
410 ,l_api_version => l_api_version
411 ,p_api_version => p_api_version
412 ,p_api_type => g_api_type
413 ,x_return_status => x_return_status);
414
415 -- check if activity started successfully
416 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
417 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
418 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
419 RAISE OKL_API.G_EXCEPTION_ERROR;
420 END IF;
421
422 -- check for logging on STATEMENT level
423 is_debug_statement_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_STATEMENT);
424
425 -- validate if subsidy pool budget id passed is valid
426 IF(p_subsidy_pool_budget_id IS NULL OR p_subsidy_pool_budget_id = OKL_API.G_MISS_NUM)THEN
427 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'p_subsidy_pool_budget_id');
428 x_return_status := OKC_API.G_RET_STS_ERROR;
429 RAISE OKL_API.G_EXCEPTION_ERROR;
430 END IF;
431
432 -- get the pool and the budget line information
433 OPEN c_get_pool_info_csr; FETCH c_get_pool_info_csr INTO cv_get_pool_info;
434 CLOSE c_get_pool_info_csr;
435
436 -- check if the pool status is ACTIVE, if not ACTIVE, throw error
437 IF(cv_get_pool_info.pool_status <> 'ACTIVE')THEN
438 OKC_API.set_message(G_APP_NAME, G_POOL_IS_NOT_ACTIVE, 'POOL_NAME', cv_get_pool_info.subsidy_pool_name);
439 x_return_status := OKC_API.G_RET_STS_ERROR;
440 RAISE OKL_API.G_EXCEPTION_ERROR;
441 END IF;
442
443 -- check for pool expiration here. pool could be logically expired by the time user submits line for approval.
444 -- in such a case, expire the pool and throw the error
445 IF(TRUNC(SYSDATE) > NVL(cv_get_pool_info.pool_end_date,okl_accounting_util.g_final_date))THEN
446 okl_subsidy_pool_pvt.expire_sub_pool(p_api_version => p_api_version
447 ,p_init_msg_list => p_init_msg_list
448 ,x_return_status => x_return_status
449 ,x_msg_count => x_msg_count
450 ,x_msg_data => x_msg_data
451 ,p_subsidy_pool_id => cv_get_pool_info.pool_id
452 );
453 -- write to log
454 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
455 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
456 l_module,
457 'expiring subsidy pool '||cv_get_pool_info.subsidy_pool_name||' with effective end date '||cv_get_pool_info.pool_end_date
458 );
459 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
460
461 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR)THEN
462 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
463 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR)THEN
464 RAISE OKL_API.G_EXCEPTION_ERROR;
465 END IF;
466 -- cannot submit pool for approval while the pool is expired.
467 OKL_API.set_message(G_APP_NAME, G_SUB_POOL_EXIPRED_WF, 'POOL_NAME', cv_get_pool_info.subsidy_pool_name);
468 RAISE OKL_API.G_EXCEPTION_ERROR;
469 END IF;
470 -- check if the line status is NEW, if not throw an error. this check is required as the user might resubmit the same
471 -- line for approval, by clicking on the refresh icon (even when the submit for approval is disabled)
472 IF(cv_get_pool_info.line_status <> 'NEW')THEN
473 OKC_API.set_message(G_APP_NAME, G_POOL_LINE_INVALID, 'POOL_NAME', cv_get_pool_info.subsidy_pool_name
474 ,'AMOUNT',cv_get_pool_info.budget_amount);
475 x_return_status := OKC_API.G_RET_STS_ERROR;
476 RAISE OKL_API.G_EXCEPTION_ERROR;
477 END IF;
478
479 -- now that the validations have been passed, set the budget line to pending for approval
480 l_budget_status := G_PENDING_STATUS_CODE;
481 okl_subsidy_pool_budget_pvt.set_decision_status_code(p_api_version => p_api_version
482 ,p_init_msg_list => p_init_msg_list
483 ,x_return_status => x_return_status
484 ,x_msg_count => x_msg_count
485 ,x_msg_data => x_msg_data
486 ,p_sub_pool_budget_id => cv_get_pool_info.budget_line_id
487 ,p_decision_status_code => l_budget_status
488 );
489 -- write to log
490 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
491 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
492 l_module,
493 'okl_subsidy_pool_budget_pvt.set_decision_status_code to pending returned with status '||x_return_status||' x_msg_data '||x_msg_data
494 );
495 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
496
497 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
498 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
499 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
500 RAISE OKL_API.G_EXCEPTION_ERROR;
501 END IF;
502
503 -- check if the operation is of type REDUCTION, in which case the budget amount is reduced from the total budget immediately.
504 -- if this REDUCTION request is subsequently rejected, then the amount is added back to the total budget
505 IF(cv_get_pool_info.budget_type_code = 'REDUCTION')THEN
506 lv_calc_total_budget := 0;
507 lv_calc_total_budget := cv_get_pool_info.total_budgets - cv_get_pool_info.budget_amount;
508 IF(lv_calc_total_budget <= 0)THEN
509 OKL_API.set_message(G_APP_NAME, G_POOL_LINE_INVALID_AMT, 'AMOUNT', cv_get_pool_info.budget_amount);
510 RAISE OKL_API.G_EXCEPTION_ERROR;
511 ELSE
512 okl_subsidy_pool_pvt.update_total_budget(p_api_version => p_api_version
513 ,p_init_msg_list => p_init_msg_list
514 ,x_return_status => x_return_status
515 ,x_msg_count => x_msg_count
516 ,x_msg_data => x_msg_data
517 ,p_subsidy_pool_id => cv_get_pool_info.pool_id
518 ,p_total_budget_amt => lv_calc_total_budget);
519 -- write to log
520 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
521 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
522 l_module,
523 'okl_subsidy_pool_pvt.update_total_budget with lv_calc_total_budget '||lv_calc_total_budget||' returned with '||x_return_status||
524 ' x_msg_data '||x_msg_data
525 );
526 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
527
528 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
529 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
530 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
531 RAISE OKL_API.G_EXCEPTION_ERROR;
532 END IF;
533 END IF;
534 END IF; -- end of cv_get_pool_info.budget_type_code = 'REDUCTION'
535
536 x_pool_budget_status := G_PENDING_STATUS_CODE;
537 -- read the profile OKL: Subsidy Pool Approval Process Access
538 l_approval_process := fnd_profile.value('OKL_SUBSIDY_POOL_APPROVAL_PROCESS');
539
540 IF(NVL(l_approval_process,'NONE')='NONE')THEN
541 -- since no approval process is selected in the profile, approve the pool budget line by default
542 l_budget_status := G_ACTIVE_STATUS_CODE;
543 okl_subsidy_pool_budget_pvt.set_decision_status_code(p_api_version => p_api_version
544 ,p_init_msg_list => p_init_msg_list
545 ,x_return_status => x_return_status
546 ,x_msg_count => x_msg_count
547 ,x_msg_data => x_msg_data
548 ,p_sub_pool_budget_id => cv_get_pool_info.budget_line_id
549 ,p_decision_status_code => l_budget_status
550 );
551 -- write to log
552 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
553 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
554 l_module,
555 'okl_subsidy_pool_budget_pvt.set_decision_status_code to active returned with status '||x_return_status||' x_msg_data '||x_msg_data
556 );
557 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
558
559 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
560 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
561 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
562 RAISE OKL_API.G_EXCEPTION_ERROR;
563 END IF;
564 IF(cv_get_pool_info.budget_type_code = 'ADDITION')THEN
565 lv_calc_total_budget := 0;
566 lv_calc_total_budget := cv_get_pool_info.total_budgets + cv_get_pool_info.budget_amount;
567
568 okl_subsidy_pool_pvt.update_total_budget(p_api_version => p_api_version
569 ,p_init_msg_list => p_init_msg_list
570 ,x_return_status => x_return_status
571 ,x_msg_count => x_msg_count
572 ,x_msg_data => x_msg_data
573 ,p_subsidy_pool_id => cv_get_pool_info.pool_id
574 ,p_total_budget_amt => lv_calc_total_budget);
575 -- write to log
576 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
577 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
578 l_module,
579 'okl_subsidy_pool_pvt.update_total_budget with lv_calc_total_budget '||lv_calc_total_budget||' status '||x_return_status||
580 ' x_msg_data '||x_msg_data
581 );
582 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
583
584 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
585 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
586 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
587 RAISE OKL_API.G_EXCEPTION_ERROR;
588 END IF;
589 END IF;
590 x_pool_budget_status := G_ACTIVE_STATUS_CODE;
591 ELSIF(l_approval_process in ('AME','WF'))THEN
592 -- raise subsidy pool budget approval event, which will then process via AME or workflow
593 okl_subsidy_pool_wf.raise_budget_event_approval(p_api_version => p_api_version
594 ,p_init_msg_list => p_init_msg_list
595 ,x_return_status => x_return_status
596 ,x_msg_count => x_msg_count
597 ,x_msg_data => x_msg_data
598 ,p_subsidy_pool_budget_id => p_subsidy_pool_budget_id);
599 -- write to log
600 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
601 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
602 l_module,
603 'okl_subsidy_pool_wf.raise_budget_event_approval returned with status '||x_return_status||' x_msg_data '||x_msg_data
604 );
605 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
606
607 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
608 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
609 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
610 RAISE OKL_API.G_EXCEPTION_ERROR;
611 END IF;
612 END IF;
613
614 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
615 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRSICB.pls call submit_budget_for_approval');
616 END IF;
617
618 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
619
620 EXCEPTION
621 WHEN OKL_API.G_EXCEPTION_ERROR THEN
622 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
623 p_api_name => l_api_name,
624 p_pkg_name => G_PKG_NAME,
625 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
626 x_msg_count => x_msg_count,
627 x_msg_data => x_msg_data,
628 p_api_type => g_api_type);
629
630 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
631 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
632 p_api_name => l_api_name,
633 p_pkg_name => G_PKG_NAME,
634 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
635 x_msg_count => x_msg_count,
636 x_msg_data => x_msg_data,
637 p_api_type => g_api_type);
638 WHEN OTHERS THEN
639 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
640 p_api_name => l_api_name,
641 p_pkg_name => G_PKG_NAME,
642 p_exc_name => 'OTHERS',
643 x_msg_count => x_msg_count,
644 x_msg_data => x_msg_data,
645 p_api_type => g_api_type);
646
647 END submit_budget_for_approval;
648
649 END okl_subsidy_pool_approval_pvt;