[Home] [Help]
PACKAGE BODY: APPS.OKL_SUBSIDY_POOL_TRX_PVT
Source
1 PACKAGE BODY okl_subsidy_pool_trx_pvt AS
2 /* $Header: OKLRSIXB.pls 120.1 2005/10/30 03:17:16 appldev noship $ */
3
4 -- Global Message Constants
5 G_SUB_POOL_EXIPRED CONSTANT fnd_new_messages.message_name%TYPE DEFAULT 'OKL_SUBSIDY_POOL_EXPIRED';
6 G_TRX_AMT_GT_TOT_SUBSIDY CONSTANT fnd_new_messages.message_name%TYPE DEFAULT 'OKL_SUB_TRX_AMT_MORE_THAN_SUB';
7 G_TRX_AMT_GT_TOT_BUDGET CONSTANT fnd_new_messages.message_name%TYPE DEFAULT 'OKL_SUB_TRX_AMT_MORE_THAN_TOT';
8 G_NO_SUBSIDY_ID CONSTANT fnd_new_messages.message_name%TYPE DEFAULT 'OKL_SUB_NO_SUBSIDY';
9 G_TRX_REASON_CD_REVERSE CONSTANT fnd_lookups.lookup_code%TYPE DEFAULT 'REVERSE_CONTRACT';
10 -------------------------------------------------------------------------------
11 -- PROCEDURE create_pool_transaction
12 -------------------------------------------------------------------------------
13 -- Start of comments
14 --
15 -- Procedure Name : create_pool_transaction
16 -- Description : This procedure is a wrapper that creates transaction records for
17 -- : subsidy pool. Note that this procedure will not report any error
18 -- if the subsidy is not attached with a pool
19 --
20 -- Business Rules : this procedure is used to add to pool balance or reduce from pool
21 -- balance. the trx_type_code determines this action.
22 -- this procedure inserts records into the OKL_TRX_SUBSIDY_POOLS table
23 -- irrespective of trx_type_code. records can never be updated or
24 -- deleted from this table.
25 --
26 -- Parameters : required parameters are source_type_code, source_object_id,
27 -- subsidy_id, trx_type_code, trx_reason_code, trx_amount, source_trx_date
28 -- Version : 1.0
29 -- History : 01-FEB-2005 SJALASUT created
30 -- End of comments
31
32 PROCEDURE create_pool_transaction(p_api_version IN NUMBER,
33 p_init_msg_list IN VARCHAR2,
34 x_return_status OUT NOCOPY VARCHAR2,
35 x_msg_count OUT NOCOPY NUMBER,
36 x_msg_data OUT NOCOPY VARCHAR2,
37 p_sixv_rec IN sixv_rec_type,
38 x_sixv_rec OUT NOCOPY sixv_rec_type)IS
39
40 CURSOR c_get_pool_dates_csr IS
41 SELECT pool.id
42 , pool.effective_from_date
43 , pool.effective_to_date
44 , pool.decision_status_code
45 , sub.name
46 , pool.currency_code
47 , pool.currency_conversion_type
48 , nvl(pool.total_budgets,0) total_budgets
49 , nvl(pool.total_subsidy_amount,0) total_subsidy_amount
50 , pool.subsidy_pool_name
51 FROM okl_subsidy_pools_b pool,
52 okl_subsidies_b sub
53 WHERE sub.subsidy_pool_id = pool.id
54 AND sub.id = p_sixv_rec.subsidy_id;
55 cv_pool_details c_get_pool_dates_csr%ROWTYPE;
56
57 l_sixv_rec sixv_rec_type;
58 l_api_version CONSTANT NUMBER DEFAULT 1.0;
59 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'CREATE_POOL_TRANSACTION';
60 l_return_status VARCHAR2(1);
61 lv_pool_expired VARCHAR2(1);
62 lv_conv_rate NUMBER;
63 lv_subsidy_pool_amt okl_trx_subsidy_pools.SUBSIDY_POOL_AMOUNT%TYPE;
64 lv_subsidy_pool_round_amt okl_trx_subsidy_pools.SUBSIDY_POOL_AMOUNT%TYPE;
65 lv_total_subsidy_amt okl_subsidy_pools_b.total_subsidy_amount%TYPE;
66
67 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_SUBSIDY_POOL_TRX_PVT.CREATE_POOL_TRANSACTION';
68 l_debug_enabled VARCHAR2(10);
69 is_debug_procedure_on BOOLEAN;
70 is_debug_statement_on BOOLEAN;
71 BEGIN
72 x_return_status := OKL_API.G_RET_STS_SUCCESS;
73
74 l_debug_enabled := okl_debug_pub.check_log_enabled;
75 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_PROCEDURE);
76
77 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
78 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRSIXB.pls call create_pool_transaction');
79 END IF;
80 -- check for logging on STATEMENT level
81 is_debug_statement_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_STATEMENT);
82
83 lv_pool_expired := OKL_API.G_FALSE;
84 l_sixv_rec := p_sixv_rec;
85
86 -- call START_ACTIVITY to create savepoint, check compatibility
87 -- and initialize message list
88 l_return_status := OKL_API.START_ACTIVITY(
89 p_api_name => l_api_name
90 ,p_pkg_name => G_PKG_NAME
91 ,p_init_msg_list => p_init_msg_list
92 ,l_api_version => l_api_version
93 ,p_api_version => p_api_version
94 ,p_api_type => g_api_type
95 ,x_return_status => x_return_status);
96 -- check if activity started successfully
97 IF(l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
98 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
99 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
100 RAISE OKL_API.G_EXCEPTION_ERROR;
101 END IF;
102
103 -- check if the subsidy id is passed, all further logic depend on subsidy id
104 IF(p_sixv_rec.subsidy_id IS NULL OR p_sixv_rec.subsidy_id = OKL_API.G_MISS_NUM)THEN
105 OKC_API.set_message(G_APP_NAME, G_NO_SUBSIDY_ID);
106 x_return_status := OKC_API.G_RET_STS_ERROR;
107 RAISE OKL_API.G_EXCEPTION_ERROR;
108 END IF;
109
110 -- fetch the subsidy and associated pool details if any
111 OPEN c_get_pool_dates_csr; FETCH c_get_pool_dates_csr INTO cv_pool_details;
112 CLOSE c_get_pool_dates_csr;
113
114 -- if subsidy pool id is not found, do not throw an error, just return
115 IF(cv_pool_details.id IS NOT NULL)THEN
116 l_sixv_rec.subsidy_pool_id := cv_pool_details.id;
117
118 -- check the status of the pool attached to the subsidy and expire the pool if need be
119 -- if the pool is expired, then raise an error that no transactions are permitted on an expired pool
120 -- the code check before date check actually save a DML call
121 -- also, for ADDITION type transaction, the expiration check is not required as the pool balance is being
122 -- augmented and no harm is being done.
123 IF(NVL(cv_pool_details.effective_to_date,to_date('31/12/4712','DD/MM/RRRR')) < TRUNC(SYSDATE) AND p_sixv_rec.trx_type_code <> 'ADDITION')THEN
124 lv_pool_expired := OKL_API.G_TRUE;
125 IF(cv_pool_details.decision_status_code <> 'EXPIRED')THEN
126 -- pool though expired by dates, is not set to status EXPIRED. set the status and do not permit this transaction
127 -- THE EXPIRATION IS AN AUTONOMOUS TRANSACTION, see okl_subsidy_pools_pvt for more details
128 okl_subsidy_pool_pvt.expire_sub_pool(p_api_version => p_api_version
129 ,p_init_msg_list => p_init_msg_list
130 ,x_return_status => x_return_status
131 ,x_msg_count => x_msg_count
132 ,x_msg_data => x_msg_data
133 ,p_subsidy_pool_id => cv_pool_details.id
134 );
135 -- write to log
136 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
137 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
138 l_module,
139 'l_sixv_rec.subsidy_pool_id '||to_char(l_sixv_rec.subsidy_pool_id) || ' p_sixv_rec.source_trx_date '||p_sixv_rec.source_trx_date ||
140 ' expiring subsidy pool with ret status '||x_return_status||' x_msg_data '||x_msg_data
141 );
142 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
143
144 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR)THEN
145 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
146 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR)THEN
147 RAISE OKL_API.G_EXCEPTION_ERROR;
148 END IF;
149 END IF; -- end decision status code check
150 END IF; -- end effective to date check
151 IF(lv_pool_expired = OKL_API.G_TRUE)THEN
152 OKL_API.set_message(G_APP_NAME, G_SUB_POOL_EXIPRED, 'SUB_NAME', cv_pool_details.name, 'ASSET', p_sixv_rec.dnz_asset_number);
153 RAISE OKL_API.G_EXCEPTION_ERROR;
154 END IF;
155
156 -- commenting this for there are no lookups as of now
157 -- call validations on the transaction record
158 OKL_SIX_PVT.validate_row(p_api_version => p_api_version
159 ,p_init_msg_list => p_init_msg_list
160 ,x_return_status => x_return_status
161 ,x_msg_count => x_msg_count
162 ,x_msg_data => x_msg_data
163 ,p_sixv_rec => l_sixv_rec
164 );
165 -- write to log
166 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
167 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
168 l_module,
169 'OKL_SIX_PVT.validate_row returned with status '||x_return_status||' x_msg_data '||x_msg_data
170 );
171 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
172
173 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR)THEN
174 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
175 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR)THEN
176 RAISE OKL_API.G_EXCEPTION_ERROR;
177 END IF;
178
179 -- now validate if the ADDITION operation on the subsidy pool balance is not reducing the subsidy less than 0
180 IF(l_sixv_rec.trx_type_code = 'ADDITION')THEN
181 IF(l_sixv_rec.subsidy_pool_amount > cv_pool_details.total_subsidy_amount)THEN
182 OKL_API.set_message(G_APP_NAME, G_TRX_AMT_GT_TOT_SUBSIDY
183 ,'TRX_AMOUNT', l_sixv_rec.trx_amount
184 ,'SUBSIDY', cv_pool_details.name
185 ,'POOL_NAME',cv_pool_details.subsidy_pool_name);
186 RAISE OKL_API.G_EXCEPTION_ERROR;
187 END IF;
188 ELSIF(l_sixv_rec.trx_type_code = 'REDUCTION') THEN
189 -- determine the pool amount from the transaction amount.
190 IF(l_sixv_rec.trx_currency_code <> cv_pool_details.currency_code)THEN
191 lv_conv_rate := 0;
192 -- currency conversion date is as on the subsidy pool transaction date which is sysdate
193 okl_accounting_util.get_curr_con_rate(p_api_version => p_api_version
194 ,p_init_msg_list => p_init_msg_list
195 ,x_return_status => x_return_status
196 ,x_msg_count => x_msg_count
197 ,x_msg_data => x_msg_data
198 ,p_from_curr_code => l_sixv_rec.trx_currency_code
199 ,p_to_curr_code => cv_pool_details.currency_code
200 ,p_con_date => TRUNC(SYSDATE)
201 ,p_con_type => cv_pool_details.currency_conversion_type
202 ,x_conv_rate => lv_conv_rate
203 );
204 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR)THEN
205 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
206 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR)THEN
207 RAISE OKL_API.G_EXCEPTION_ERROR;
208 END IF;
209 -- compute pool amount
210 lv_subsidy_pool_amt := 0;
211 lv_subsidy_pool_amt := lv_conv_rate * l_sixv_rec.trx_amount;
212 l_sixv_rec.conversion_rate := lv_conv_rate;
213 -- now round off the amount
214 lv_subsidy_pool_round_amt := 0;
215 okl_accounting_util.cross_currency_round_amount(p_api_version => p_api_version
216 ,p_init_msg_list => p_init_msg_list
217 ,x_return_status => x_return_status
218 ,x_msg_count => x_msg_count
219 ,x_msg_data => x_msg_data
220 ,p_amount => lv_subsidy_pool_amt
221 ,p_currency_code => cv_pool_details.currency_code
222 ,x_rounded_amount => lv_subsidy_pool_round_amt
223 );
224 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR)THEN
225 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
226 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR)THEN
227 RAISE OKL_API.G_EXCEPTION_ERROR;
228 END IF;
229 -- write to log
230 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
231 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
232 l_module,
233 'lv_conv_rate '||lv_conv_rate||' l_sixv_rec.trx_amount '||l_sixv_rec.trx_amount||' lv_subsidy_pool_round_amt '||lv_subsidy_pool_round_amt
234 );
235 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
236 ELSE -- currency codes are the same, so need to convert
237 lv_subsidy_pool_round_amt := l_sixv_rec.trx_amount;
238 l_sixv_rec.conversion_rate := 1.0;
239 END IF; -- end of trx currency code check
240
241 -- REDUCTION operation on the pool balance is not overshooting the total budget
242 -- in other words, REDUCTION operation should not be more than the remaining balance
243 IF((lv_subsidy_pool_round_amt + cv_pool_details.total_subsidy_amount) > cv_pool_details.total_budgets)THEN
244 OKL_API.set_message(G_APP_NAME, G_TRX_AMT_GT_TOT_BUDGET
245 ,'TRX_AMOUNT', l_sixv_rec.trx_amount
246 ,'SUBSIDY', cv_pool_details.name
247 ,'POOL_NAME',cv_pool_details.subsidy_pool_name);
248 RAISE OKL_API.G_EXCEPTION_ERROR;
249 END IF;
250 l_sixv_rec.subsidy_pool_currency_code := cv_pool_details.currency_code;
251 l_sixv_rec.subsidy_pool_amount := lv_subsidy_pool_round_amt;
252 END IF; -- end of trx_type_code check
253
254 l_sixv_rec.trx_date := trunc(sysdate);
255 -- call the TAPI insert_row to create a subsidy pool transaction
256 OKL_SIX_PVT.insert_row(p_api_version => p_api_version
257 ,p_init_msg_list => p_init_msg_list
258 ,x_return_status => x_return_status
259 ,x_msg_count => x_msg_count
260 ,x_msg_data => x_msg_data
261 ,p_sixv_rec => l_sixv_rec
262 ,x_sixv_rec => x_sixv_rec
263 );
264 -- write to log
265 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
266 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
267 l_module,
268 'created pool transaction record with status '||x_return_status || ' x_msg_data '||x_msg_data
269 );
270 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
271
272 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR)THEN
273 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
274 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR)THEN
275 RAISE OKL_API.G_EXCEPTION_ERROR;
276 END IF;
277 lv_total_subsidy_amt := 0;
278 IF(l_sixv_rec.trx_type_code = 'ADDITION')THEN
279 lv_total_subsidy_amt := cv_pool_details.total_subsidy_amount - l_sixv_rec.subsidy_pool_amount;
280 ELSIF(l_sixv_rec.trx_type_code = 'REDUCTION')THEN
281 lv_total_subsidy_amt := cv_pool_details.total_subsidy_amount + l_sixv_rec.subsidy_pool_amount;
282 END IF;
283 -- note that (total budget - subsidy amount) = remaining balance
284 -- based on the transaction type, the subsidy amount is increased (in case of REDUCTION transaction or reducing pool balance)
285 -- or subsidy amount is decreased (in case of ADDITION transaction or adding back to pool balance)
286 okl_subsidy_pool_pvt.update_subsidy_amount(p_api_version => p_api_version
287 ,p_init_msg_list => p_init_msg_list
288 ,x_return_status => x_return_status
289 ,x_msg_count => x_msg_count
290 ,x_msg_data => x_msg_data
291 ,p_subsidy_pool_id => cv_pool_details.id
292 ,p_total_subsidy_amt => lv_total_subsidy_amt
293 );
294 -- write to log
295 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
296 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
297 l_module,
298 'updated total_subsisy_amount '||lv_total_subsidy_amt||' with status '||x_return_status || ' x_msg_data '||x_msg_data
299 );
300 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
301
302 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR)THEN
303 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
304 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR)THEN
305 RAISE OKL_API.G_EXCEPTION_ERROR;
306 END IF;
307 END IF; -- end of subsidy pool id is not null
308
309 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
310
311 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
312 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRSIXB.pls call create_pool_transaction');
313 END IF;
314
315 EXCEPTION
316 WHEN OKL_API.G_EXCEPTION_ERROR THEN
317 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
318 p_api_name => l_api_name,
319 p_pkg_name => G_PKG_NAME,
320 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
321 x_msg_count => x_msg_count,
322 x_msg_data => x_msg_data,
323 p_api_type => g_api_type);
324
325 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
326 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
327 p_api_name => l_api_name,
328 p_pkg_name => G_PKG_NAME,
329 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
330 x_msg_count => x_msg_count,
331 x_msg_data => x_msg_data,
332 p_api_type => g_api_type);
333
334 WHEN OTHERS THEN
335 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
336 p_api_name => l_api_name,
337 p_pkg_name => G_PKG_NAME,
338 p_exc_name => 'OTHERS',
339 x_msg_count => x_msg_count,
340 x_msg_data => x_msg_data,
341 p_api_type => g_api_type);
342 END create_pool_transaction;
343
344
345 PROCEDURE create_pool_transaction(p_api_version IN NUMBER,
346 p_init_msg_list IN VARCHAR2,
347 x_return_status OUT NOCOPY VARCHAR2,
348 x_msg_count OUT NOCOPY NUMBER,
349 x_msg_data OUT NOCOPY VARCHAR2,
350 p_sixv_tbl IN sixv_tbl_type,
351 x_sixv_tbl OUT NOCOPY sixv_tbl_type) IS
352 l_return_status VARCHAR2(1);
353 l_api_name CONSTANT varchar2(30) := 'CREATE_POOL_TRANSACTION';
354 i NUMBER := 0;
355 l_six_tbl sixv_tbl_type;
356 BEGIN
357 x_return_status := OKL_API.G_RET_STS_SUCCESS;
358
359 l_six_tbl := p_sixv_tbl;
360 IF(l_six_tbl.COUNT > 0 )THEN
361 i := l_six_tbl.FIRST;
362 LOOP
363 create_pool_transaction(
364 p_api_version => p_api_version,
365 p_init_msg_list => p_init_msg_list,
366 x_return_status => x_return_status,
367 x_msg_count => x_msg_count,
368 x_msg_data => x_msg_data,
369 p_sixv_rec => l_six_tbl(i),
370 x_sixv_rec => x_sixv_tbl(i));
371 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
372 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
373 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
374 RAISE OKL_API.G_EXCEPTION_ERROR;
375 END IF;
376 EXIT WHEN (i = l_six_tbl.LAST);
377 i := l_six_tbl.NEXT(i);
378 END LOOP;
379 END IF;
380 EXCEPTION
381 WHEN OKL_API.G_EXCEPTION_ERROR THEN
382 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
383 p_api_name => l_api_name,
384 p_pkg_name => G_PKG_NAME,
385 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
386 x_msg_count => x_msg_count,
387 x_msg_data => x_msg_data,
388 p_api_type => g_api_type);
389
390 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
391 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
392 p_api_name => l_api_name,
393 p_pkg_name => G_PKG_NAME,
394 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
395 x_msg_count => x_msg_count,
396 x_msg_data => x_msg_data,
397 p_api_type => g_api_type);
398
399 WHEN OTHERS THEN
400 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
401 p_api_name => l_api_name,
402 p_pkg_name => G_PKG_NAME,
403 p_exc_name => 'OTHERS',
404 x_msg_count => x_msg_count,
405 x_msg_data => x_msg_data,
406 p_api_type => g_api_type);
407
408 END create_pool_transaction;
409
410 END okl_subsidy_pool_trx_pvt;