1 PACKAGE BODY okl_subsidy_pool_auth_trx_pvt AS
2 /* $Header: OKLRSIUB.pls 120.2.12010000.2 2008/10/01 22:34:17 rkuttiya ship $ */
3
4 G_NO_SUB_POOL_TRX_FOUND CONSTANT fnd_new_messages.message_name%TYPE DEFAULT 'OKL_NO_SUB_POOL_TRX_FOUND';
5 G_TRX_SOURCE_TYPE_CODE CONSTANT fnd_new_messages.message_name%TYPE DEFAULT 'LEASE_CONTRACT';
6 G_ADDITION_TYPE_CODE CONSTANT okl_trx_subsidy_pools.trx_type_code%TYPE DEFAULT 'ADDITION';
7 G_REDUCTION_TYPE_CODE CONSTANT okl_trx_subsidy_pools.trx_type_code%TYPE DEFAULT 'REDUCTION';
8 G_CONTRACT_REVERSAL_CODE CONSTANT fnd_lookups.lookup_code%TYPE DEFAULT 'REVERSE_CONTRACT';
9 G_CONTRACT_BOOK_CODE CONSTANT fnd_lookups.lookup_code%TYPE DEFAULT 'ACTIVATE_CONTRACT';
10 G_CONTRACT_REBOOK_CODE CONSTANT fnd_lookups.lookup_code%TYPE DEFAULT 'REBOOK_CONTRACT';
11 G_CONTRACT_SPLIT_CODE CONSTANT fnd_lookups.lookup_code%TYPE DEFAULT 'SPLIT_CONTRACT';
12 G_SOURCE_TRX_DATE_POOL_VAL CONSTANT fnd_new_messages.message_name%TYPE DEFAULT 'OKL_INVALID_RBK_DATE';
13 G_NO_POOL_TRANSACTION_EXISTS CONSTANT fnd_new_messages.message_name%TYPE DEFAULT 'OKL_NO_POOL_TRX_EXISTS';
14
15 -- start: cklee 07/12/2005
16 G_ORIG_CODE_LEASE_APP CONSTANT varchar2(30) := 'OKL_LEASE_APP';
17 G_ORIG_CODE_QUOTE CONSTANT varchar2(30) := 'OKL_QUOTE';
18 -- end: cklee 07/12/2005
19
20 -- local procedure 1. START
21 PROCEDURE pool_trx_khr_rbk(p_source_trx_date IN okl_trx_subsidy_pools.source_trx_date%TYPE
22 ,p_six_tbl IN okl_six_pvt.sixv_tbl_type
23 ,x_return_status OUT NOCOPY VARCHAR2)IS
24
25 CURSOR c_get_pool_dates_csr(cp_subsidy_pool_id okl_subsidy_pools_b.id%TYPE) IS
26 SELECT effective_from_date
27 ,effective_to_date
28 ,subsidy_pool_name
29 FROM okl_subsidy_pools_b
30 WHERE id = cp_subsidy_pool_id;
31 cv_get_pool_dates_csr c_get_pool_dates_csr%ROWTYPE;
32
33 CURSOR c_get_pool_trx_details(cp_source_object_id okl_trx_subsidy_pools.source_object_id%TYPE
34 ,cp_subsidy_pool_id okl_trx_subsidy_pools.subsidy_pool_id%TYPE
35 ,cp_pool_currency_code okl_trx_subsidy_pools.subsidy_pool_currency_code%TYPE
36 ,cp_vendor_id okl_trx_subsidy_pools.vendor_id%TYPE
37 ,cp_asset_number okl_trx_subsidy_pools.dnz_asset_number%TYPE
38 ,cp_subsidy_id okl_trx_subsidy_pools.subsidy_id%TYPE
39 ,cp_trx_amount okl_trx_subsidy_pools.trx_amount%TYPE
40 ,cp_trx_currency_code okl_trx_subsidy_pools.trx_currency_code%TYPE
41 ) IS
42 SELECT trx_reason_code
43 ,subsidy_pool_amount
44 ,conversion_rate
45 FROM okl_trx_subsidy_pools
46 WHERE trx_type_code = 'REDUCTION'
47 AND source_type_code = G_TRX_SOURCE_TYPE_CODE
48 AND source_object_id = cp_source_object_id
49 AND subsidy_pool_id = cp_subsidy_pool_id
50 AND subsidy_pool_currency_code = cp_pool_currency_code
51 AND vendor_id = cp_vendor_id
52 AND subsidy_id = cp_subsidy_id
53 AND dnz_asset_number = cp_asset_number
54 AND trx_amount = cp_trx_amount
55 AND trx_currency_code = cp_trx_currency_code;
56 cv_get_pool_trx_details c_get_pool_trx_details%ROWTYPE;
57
58 lv_six_tbl okl_six_pvt.sixv_tbl_type;
59 lx_six_tbl okl_six_pvt.sixv_tbl_type;
60 idx PLS_INTEGER;
61 total_num_recs NUMBER;
62 x_msg_count NUMBER;
63 l_api_version CONSTANT NUMBER DEFAULT '1.0';
64 x_msg_data VARCHAR2(100);
65
66 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_SUBSIDY_POOL_AUTH_TRX_PVT.POOL_TRX_KHR_RBK';
67 l_debug_enabled VARCHAR2(10);
68 is_debug_statement_on BOOLEAN;
69
70 BEGIN
71 x_return_status := OKL_API.G_RET_STS_SUCCESS;
72
73 l_debug_enabled := okl_debug_pub.check_log_enabled;
74 -- check for logging on STATEMENT level
75 is_debug_statement_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_STATEMENT);
76
77 lv_six_tbl := p_six_tbl;
78 idx := 0;
79 total_num_recs := p_six_tbl.COUNT;
80
81 FOR idx IN 1 .. total_num_recs LOOP
82 -- get the subsidy pool effective dates
83 OPEN c_get_pool_dates_csr(lv_six_tbl(idx).subsidy_pool_id); FETCH c_get_pool_dates_csr INTO cv_get_pool_dates_csr;
84 CLOSE c_get_pool_dates_csr;
85 -- validate if the source transaction date is between the passed subsidy pool dates
86 IF NOT(TRUNC(p_source_trx_date) BETWEEN cv_get_pool_dates_csr.effective_from_date AND
87 NVL(cv_get_pool_dates_csr.effective_to_date,OKL_ACCOUNTING_UTIL.g_final_date))THEN
88 OKL_API.set_message(G_APP_NAME, G_SOURCE_TRX_DATE_POOL_VAL
89 ,'TRX_DATE', p_source_trx_date
90 ,'POOL_NAME', cv_get_pool_dates_csr.subsidy_pool_name
91 ,'ASSET_NUMBER',lv_six_tbl(idx).dnz_asset_number);
92 x_return_status := OKL_API.G_RET_STS_ERROR;
93 -- any errors here would be rolled up to display as a bundled error; i.e. all subsidy elements are
94 -- processed to show applicable errors. hence no raise statement
95 ELSE
96 -- assign the source trx date to the date of the rebook
97 lv_six_tbl(idx).source_trx_date := p_source_trx_date;
98 -- write to log
99 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
100 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
101 l_module,
102 idx||' source trx date check passed for subsidy '||lv_six_tbl(idx).subsidy_id
103 );
104 END IF; -- end of NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on
105 END IF;
106 -- for ADDITION to pool balance, check if there was a prior REDUCTION operation.
107 -- this validation ensures that we are not adding back to the pool balance without ever reducing from it
108 -- in ideal scenarios, this check never fails, but this check is essential to stop data corruption
109 IF(lv_six_tbl(idx).trx_type_code = 'ADDITION' AND x_return_status = OKL_API.G_RET_STS_SUCCESS)THEN
110 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
111 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
112 l_module,
113 ' finding trx for pool '||lv_six_tbl(idx).subsidy_pool_id || ' lv_six_tbl(idx).source_object_id '||lv_six_tbl(idx).source_object_id
114 ||' lv_six_tbl(idx).vendor_id '||lv_six_tbl(idx).vendor_id||' lv_six_tbl(idx).dnz_asset_number '||lv_six_tbl(idx).dnz_asset_number
115 ||' lv_six_tbl(idx).subsidy_id '||lv_six_tbl(idx).subsidy_id ||' lv_six_tbl(idx).subsidy_pool_currency_code '||lv_six_tbl(idx).subsidy_pool_currency_code
116 ||' lv_six_tbl(idx).trx_currency_code '||lv_six_tbl(idx).trx_currency_code
117 ||' lv_six_tbl(idx).trx_amount '||lv_six_tbl(idx).trx_amount
118 );
119 END IF; -- end of NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on
120 -- fill up other important columns from the subsidy pool trx table. we are not recalculating
121 -- the amount into subsidy pool currency again for consistency reasons.
122 OPEN c_get_pool_trx_details(cp_source_object_id => lv_six_tbl(idx).source_object_id
123 ,cp_subsidy_pool_id => lv_six_tbl(idx).subsidy_pool_id
124 ,cp_pool_currency_code => lv_six_tbl(idx).subsidy_pool_currency_code
125 ,cp_vendor_id => lv_six_tbl(idx).vendor_id
126 ,cp_asset_number => lv_six_tbl(idx).dnz_asset_number
127 ,cp_subsidy_id => lv_six_tbl(idx).subsidy_id
128 ,cp_trx_amount => lv_six_tbl(idx).trx_amount
129 ,cp_trx_currency_code => lv_six_tbl(idx).trx_currency_code
130 );
131 FETCH c_get_pool_trx_details INTO cv_get_pool_trx_details;
132 IF(c_get_pool_trx_details%NOTFOUND)THEN
133 CLOSE c_get_pool_trx_details;
134 OKL_API.set_message(G_APP_NAME, G_NO_POOL_TRANSACTION_EXISTS
135 ,'ASSET_NUMBER', lv_six_tbl(idx).dnz_asset_number);
136 x_return_status := OKL_API.G_RET_STS_ERROR;
137 END IF;
138 CLOSE c_get_pool_trx_details;
139 lv_six_tbl(idx).subsidy_pool_amount := cv_get_pool_trx_details.subsidy_pool_amount;
140 lv_six_tbl(idx).conversion_rate := cv_get_pool_trx_details.conversion_rate;
141 -- write to log
142 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
143 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
144 l_module,
145 idx||' found trx for pool '||lv_six_tbl(idx).subsidy_pool_id || ' lv_six_tbl(idx).source_object_id '||lv_six_tbl(idx).source_object_id
146 ||' lv_six_tbl(idx).vendor_id '||lv_six_tbl(idx).vendor_id||' lv_six_tbl(idx).dnz_asset_number '||lv_six_tbl(idx).dnz_asset_number
147 ||' lv_six_tbl(idx).subsidy_id '||lv_six_tbl(idx).subsidy_id
148 );
149 END IF; -- end of NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on
150 END IF; -- end of lv_six_tbl(idx).trx_type_code = 'ADDITION' AND x_return_status = OKL_API.G_RET_STS_SUCCESS
151 lv_six_tbl(idx).source_type_code := G_TRX_SOURCE_TYPE_CODE;
152 lv_six_tbl(idx).trx_reason_code := G_CONTRACT_REBOOK_CODE;
153 END LOOP;
154 -- now call the create pool transacation api
155 IF(x_return_status = OKL_API.G_RET_STS_SUCCESS)THEN
156 okl_subsidy_pool_trx_pvt.create_pool_transaction(p_api_version => l_api_version
157 ,p_init_msg_list => OKL_API.G_TRUE
158 ,x_return_status => x_return_status
159 ,x_msg_count => x_msg_count
160 ,x_msg_data => x_msg_data
161 ,p_sixv_tbl => lv_six_tbl
162 ,x_sixv_tbl => lx_six_tbl
163 );
164 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
165 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
166 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
167 RAISE OKL_API.G_EXCEPTION_ERROR;
168 END IF;
169 END IF;
170 EXCEPTION WHEN OTHERS THEN
171 x_return_status := OKL_API.G_RET_STS_ERROR;
172 END pool_trx_khr_rbk;
173 -- local procedure 1. END
174
175 -- local procedure 2. START
176 PROCEDURE reduce_pool_balance(p_khr_id IN okc_k_headers_b.id%TYPE
177 ,p_source_trx_date IN okl_trx_subsidy_pools.source_trx_date%TYPE
178 ,x_return_status OUT NOCOPY VARCHAR2) IS
179
180 -- cursor to fetch all subsidies attached to assets on the contract
181 CURSOR c_subsidy_csr(cp_chr_id IN okc_k_headers_b.id%TYPE) IS
182 SELECT kle.subsidy_id
183 ,clet.name
184 ,clet.item_description
185 ,kle.amount
186 ,kle.subsidy_override_amount
187 ,cplb.object1_id1 vendor_id
188 ,cplb.id cpl_id
189 ,kle.sty_id sty_id
190 ,cleb.cle_id asset_id
191 ,cleb.currency_code
192 FROM okl_k_lines kle
193 ,okc_k_lines_tl clet
194 ,okc_k_lines_b cleb
195 ,okc_statuses_b stsb
196 ,okc_line_styles_b lseb
197 ,okc_k_party_roles_b cplb
198 WHERE kle.id = cleb.id
199 AND clet.id = cleb.id
200 AND clet.language = userenv('LANG')
201 AND cleb.dnz_chr_id = cp_chr_id
202 AND stsb.code = cleb.sts_code
203 AND stsb.ste_code not in ('CANCELLED')
204 AND lseb.id = cleb.lse_id
205 AND lseb.lty_code = 'SUBSIDY'
206 AND cplb.cle_id = cleb.id
207 AND cplb.rle_code = 'OKL_VENDOR'
208 AND cplb.dnz_chr_id = cp_chr_id;
209
210 CURSOR c_get_asset_csr (cp_asset_id okc_k_lines_b.id%TYPE)IS
211 SELECT clev_asst.name asset_number
212 FROM okc_k_lines_v clev_asst
213 WHERE clev_asst.id = cp_asset_id;
214 lv_asset_number okc_k_lines_v.name%TYPE;
215
216 lx_subsidy_pool_id okl_subsidy_pools_b.id%TYPE;
217 lx_sub_pool_curr_code okl_subsidy_pools_b.currency_code%TYPE;
218 lv_subsidy_applic VARCHAR2(10);
219 lv_six_tbl okl_six_pvt.sixv_tbl_type;
220 lx_six_tbl okl_six_pvt.sixv_tbl_type;
221 idx NUMBER;
222 total_num_recs NUMBER;
223 x_msg_count NUMBER;
224 l_api_version CONSTANT NUMBER DEFAULT 1.0;
225 x_msg_data VARCHAR2(1000);
226
227 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_SUBSIDY_POOL_AUTH_TRX_PVT.REDUCE_POOL_BALANCE';
228 l_debug_enabled VARCHAR2(10);
229 is_debug_statement_on BOOLEAN;
230
231 BEGIN
232 l_debug_enabled := okl_debug_pub.check_log_enabled;
233 -- check for logging on STATEMENT level
234 is_debug_statement_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_STATEMENT);
235
236 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
237 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
238 l_module,
239 'inside local procedure reduce_pool_balance '||p_khr_id
240 );
241 END IF; -- end of NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on
242
243
244 x_return_status := OKL_API.G_RET_STS_SUCCESS;
245
246 -- initialize tbl type parameters and idx
247 lv_six_tbl.DELETE; lx_six_tbl.DELETE;
248 idx := 0;
249
250 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
251 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
252 l_module,
253 'before processing REDUCTION records '||p_khr_id
254 );
255 END IF; -- end of NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on
256
257
258 FOR cv_susidy_rec IN c_subsidy_csr(cp_chr_id => p_khr_id) LOOP
259 lx_subsidy_pool_id := NULL;
260 lx_sub_pool_curr_code := NULL;
261 lv_subsidy_applic := 'N';
262 lv_subsidy_applic := okl_asset_subsidy_pvt.is_sub_assoc_with_pool(p_subsidy_id => cv_susidy_rec.subsidy_id
263 ,x_subsidy_pool_id => lx_subsidy_pool_id
264 ,x_sub_pool_curr_code => lx_sub_pool_curr_code);
265 IF(lv_subsidy_applic = 'Y')THEN
266 idx := idx + 1;
267 lv_six_tbl(idx).trx_type_code := G_REDUCTION_TYPE_CODE;
268 lv_six_tbl(idx).source_type_code := G_TRX_SOURCE_TYPE_CODE;
269 lv_six_tbl(idx).source_object_id := p_khr_id;
270 lv_six_tbl(idx).subsidy_pool_id := lx_subsidy_pool_id;
271 lv_asset_number := NULL;
272 OPEN c_get_asset_csr(cv_susidy_rec.asset_id); FETCH c_get_asset_csr INTO lv_asset_number;
273 CLOSE c_get_asset_csr;
274 lv_six_tbl(idx).dnz_asset_number := lv_asset_number;
275 lv_six_tbl(idx).vendor_id := cv_susidy_rec.vendor_id;
276 lv_six_tbl(idx).source_trx_date := p_source_trx_date;
277 lv_six_tbl(idx).subsidy_id := cv_susidy_rec.subsidy_id;
278 lv_six_tbl(idx).trx_reason_code := G_CONTRACT_BOOK_CODE;
279 lv_six_tbl(idx).trx_currency_code := cv_susidy_rec.currency_code;
280 lv_six_tbl(idx).trx_amount := NVL(cv_susidy_rec.subsidy_override_amount,NVL(cv_susidy_rec.amount,0));
281 lv_six_tbl(idx).subsidy_pool_currency_code := lx_sub_pool_curr_code;
282 END IF;
283 END LOOP;
284 -- now check the length of the table lv_sixv_tbl and then call the okl_subsidy_pool_trx_pvt api to create ADDITION transactions
285 IF(lv_six_tbl.COUNT > 0)THEN
286 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
287 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
288 l_module,
289 lv_six_tbl.COUNT||' records found for reversing from the contract '||p_khr_id || ' p_source_trx_date '|| p_source_trx_date
290 );
291 END IF; -- end of NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on
292
293 okl_subsidy_pool_trx_pvt.create_pool_transaction(p_api_version => l_api_version
294 ,p_init_msg_list => OKL_API.G_FALSE
295 ,x_return_status => x_return_status
296 ,x_msg_count => x_msg_count
297 ,x_msg_data => x_msg_data
298 ,p_sixv_tbl => lv_six_tbl
299 ,x_sixv_tbl => lx_six_tbl
300 );
301 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
302 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
303 l_module,
304 'okl_subsidy_pool_trx_pvt.create_pool_transaction from reduce_pool_balance returned with status '||x_return_status
305 ||' x_msg_data '||x_msg_data
306 );
307 END IF; -- end of NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on
308
309 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
310 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
311 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
312 RAISE OKL_API.G_EXCEPTION_ERROR;
313 END IF;
314 END IF;
315
316 EXCEPTION WHEN OTHERS THEN
317 x_return_status := OKL_API.G_RET_STS_ERROR;
318 END reduce_pool_balance;
319 -- local procedure 2. END
320
321 PROCEDURE create_pool_trx_khr_book(p_api_version IN NUMBER
322 ,p_init_msg_list IN VARCHAR2
323 ,x_return_status OUT NOCOPY VARCHAR2
324 ,x_msg_count OUT NOCOPY NUMBER
325 ,x_msg_data OUT NOCOPY VARCHAR2
326 ,p_chr_id IN okc_k_headers_b.id%TYPE
327 ,p_asset_id IN okc_k_lines_b.id%TYPE
328 ,p_subsidy_id IN okl_subsidies_b.id%TYPE
329 ,p_subsidy_pool_id IN okl_subsidy_pools_b.id%TYPE
330 ,p_trx_amount IN okl_k_lines.amount%TYPE
331 ) IS
332
333 -- cursor to fetch asset details
334 CURSOR c_get_asset_csr IS
335 SELECT clev_asst.name asset_number
336 ,clev_asst.start_date asset_start_date
337 ,clev_asst.currency_code
338 FROM okc_k_lines_v clev_asst
339 WHERE clev_asst.id = p_asset_id;
340 cv_get_asset_rec c_get_asset_csr%ROWTYPE;
341
342 -- cursor to check if the contract has been originated from quote or lease application
343 CURSOR c_chk_orig_source_csr IS
344 SELECT orig_system_id1
345 ,orig_system_source_code
346 ,scs_code
347 FROM okc_k_headers_b
348 WHERE id = p_chr_id;
349 cv_chk_orig_source c_chk_orig_source_csr%ROWTYPE;
350
351 -- cursor to fetch the vendor details of the asset
352 CURSOR c_get_vendor_csr IS
353 SELECT vendor_id
354 FROM okl_asset_subsidy_uv
355 WHERE asset_cle_id = p_asset_id;
356 cv_get_vendor c_get_vendor_csr%ROWTYPE;
357
358 lv_sixv_rec sixv_rec_type;
359 lx_sixv_rec sixv_rec_type;
360 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'POOL_TRX_KHR_BOOK';
361
362 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_SUBSIDY_POOL_AUTH_TRX_PVT.CREATE_POOL_TRX_KHR_BOOK';
363 l_debug_enabled VARCHAR2(10);
364 is_debug_procedure_on BOOLEAN;
365 is_debug_statement_on BOOLEAN;
366
367 BEGIN
368
369 x_return_status := OKL_API.G_RET_STS_SUCCESS;
370
371 l_debug_enabled := okl_debug_pub.check_log_enabled;
372 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_PROCEDURE);
373 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
374 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRSIUB.pls call create_pool_trx_khr_book');
375 END IF;
376
377 -- Call start_activity to create savepoint, check compatibility and initialize message list
378 x_return_status := OKL_API.START_ACTIVITY (
379 l_api_name
380 ,p_init_msg_list
381 ,'_PVT'
382 ,x_return_status);
383 -- Check if activity started successfully
384 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
385 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
386 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
387 RAISE OKL_API.G_EXCEPTION_ERROR;
388 END IF;
389
390 -- check for logging on STATEMENT level
391 is_debug_statement_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_STATEMENT);
392
393 -- populate all the attributes that are necessary for the subsidy pool transaction record
394 lv_sixv_rec.source_object_id := p_chr_id;
395 lv_sixv_rec.subsidy_id := p_subsidy_id;
396 lv_sixv_rec.subsidy_pool_id := p_subsidy_pool_id;
397
398 -- derive immediate values from the parameters passed
399 -- derive asset number.
400 OPEN c_get_asset_csr; FETCH c_get_asset_csr INTO cv_get_asset_rec;
401 CLOSE c_get_asset_csr;
402 lv_sixv_rec.dnz_asset_number := cv_get_asset_rec.asset_number;
403 -- source transaction date is the start date of the asset.
404 lv_sixv_rec.source_trx_date := TRUNC(cv_get_asset_rec.asset_start_date);
405 -- trx currency code is the contract or asset currency code.
406 lv_sixv_rec.trx_currency_code := cv_get_asset_rec.currency_code;
407 lv_sixv_rec.trx_amount := p_trx_amount;
408
409 -- now get the vendor on the asset subsidy
410 OPEN c_get_vendor_csr; FETCH c_get_vendor_csr INTO cv_get_vendor;
411 CLOSE c_get_vendor_csr;
412 lv_sixv_rec.vendor_id := cv_get_vendor.vendor_id;
413
414 -- fetch the scs code from p_chr_id
415 OPEN c_chk_orig_source_csr; FETCH c_chk_orig_source_csr INTO cv_chk_orig_source;
416 CLOSE c_chk_orig_source_csr;
417
418 lv_sixv_rec.source_type_code := G_TRX_SOURCE_TYPE_CODE;
419
420 -- write to log
421 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
422 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
423 l_module,
424 'lv_sixv_rec.source_object_id '||lv_sixv_rec.source_object_id || ' lv_sixv_rec.subsidy_id '||lv_sixv_rec.subsidy_id ||
425 ' lv_sixv_rec.subsidy_pool_id '||lv_sixv_rec.subsidy_pool_id ||' lv_sixv_rec.dnz_asset_number '||lv_sixv_rec.dnz_asset_number||
426 ' lv_sixv_rec.source_trx_date '||lv_sixv_rec.source_trx_date ||' lv_sixv_rec.trx_amount '||lv_sixv_rec.trx_amount||
427 ' lv_sixv_rec.vendor_id '||lv_sixv_rec.vendor_id||' lv_sixv_rec.source_type_code '||lv_sixv_rec.source_type_code
428 );
429 END IF; -- end of NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on
430
431
432 -- now switch based on the orig_system_source_code
433 -- 09/09/2005 cklee:comment out IF(cv_chk_orig_source.orig_system_source_code = G_ORIG_CODE_QUOTE)THEN
434 IF(cv_chk_orig_source.orig_system_source_code IN (G_ORIG_CODE_QUOTE, G_ORIG_CODE_LEASE_APP))THEN
435 -- the contract has been originated from a Sales Quote. here we need to add back the subsidy amount from
436 -- Sales Quote (if not earlier added back) and reduce the subsidy amount on the Contract from the Pool. datamodel is not
437 -- yet ready. work in progress code
438
439 -- START 09/09/2005 cklee: integration with Quote/Lease App API
440 OKL_LEASE_QUOTE_SUBPOOL_PVT.process_active_contract (p_api_version => p_api_version
441 ,p_init_msg_list => p_init_msg_list
442 ,x_return_status => x_return_status
443 ,x_msg_count => x_msg_count
444 ,x_msg_data => x_msg_data
445 ,p_transaction_control => FND_API.G_TRUE
446 ,p_contract_id => p_chr_id
447 );
448 -- write to log
449 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
450 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
451 l_module,
452 'OKL_LEASE_QUOTE_SUBPOOL_PVT.process_active_contract with status '||x_return_status||' x_msg_data '||x_msg_data
453 );
454 END IF; -- end of NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on
455
456 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
457 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
458 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
459 RAISE OKL_API.G_EXCEPTION_ERROR;
460 END IF;
461
462 END IF;
463 -- END 09/09/2005 cklee
464
465 -- start: cklee 07/12/2005
466 -- 09/09/2005 cklee:comment out ELSIF(cv_chk_orig_source.orig_system_source_code = G_ORIG_CODE_LEASE_APP)THEN
467 -- contract has been originated from Lease Application. here we need to add back the subsidy amount on
468 -- the Lease Application (if not earlier added back) and reduce the subsidy amount on the Contract from the Pool. datamodel is not
469 -- yet ready. work in progress code
470 -- NULL;
471 -- ELSIF(cv_chk_orig_source.orig_system_source_code = 'OKC_HDR')THEN
472 -- This column may be null if user create contract from scrach. This column could be OKL_IMPORT, however,
473 -- this is an internal API called from OKL booking procesdure, no further validations needed at this point. cklee
474 -- ELSE
475 -- end: cklee 07/12/2005
476
477 lv_sixv_rec.trx_type_code := 'REDUCTION';
478 lv_sixv_rec.trx_reason_code := G_CONTRACT_BOOK_CODE;
479 OKL_SUBSIDY_POOL_TRX_PVT.create_pool_transaction(p_api_version => p_api_version
480 ,p_init_msg_list => p_init_msg_list
481 ,x_return_status => x_return_status
482 ,x_msg_count => x_msg_count
483 ,x_msg_data => x_msg_data
484 ,p_sixv_rec => lv_sixv_rec
485 ,x_sixv_rec => lx_sixv_rec
486 );
487 -- write to log
488 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
489 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
490 l_module,
491 'OKL_SUBSIDY_POOL_TRX_PVT.create_pool_transaction with status '||x_return_status||' x_msg_data '||x_msg_data
492 );
493 END IF; -- end of NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on
494
495 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
496 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
497 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
498 RAISE OKL_API.G_EXCEPTION_ERROR;
499 END IF;
500 -- start: cklee 07/12/2005
501 /* ELSIF(cv_chk_orig_source.orig_system_source_code = 'LEASE_APPLICATION')THEN
502 -- contract has been originated from Lease Application. here we need to add back the subsidy amount on
503 -- the Lease Application (if not earlier added back) and reduce the subsidy amount on the Contract from the Pool. datamodel is not
504 -- yet ready. work in progress code
505 NULL;
506 */
507 -- 09/09/2005 cklee:comment out END IF;
508 -- end: cklee 07/12/2005
509
510 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
511
512 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
513 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRSIUB.pls call create_pool_trx_khr_book');
514 END IF;
515
516 EXCEPTION
517 WHEN OKL_API.G_EXCEPTION_ERROR THEN
518 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
519 p_api_name => l_api_name,
520 p_pkg_name => G_PKG_NAME,
521 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
522 x_msg_count => x_msg_count,
523 x_msg_data => x_msg_data,
524 p_api_type => g_api_type);
525
526 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
527 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
528 p_api_name => l_api_name,
529 p_pkg_name => G_PKG_NAME,
530 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
531 x_msg_count => x_msg_count,
532 x_msg_data => x_msg_data,
533 p_api_type => g_api_type);
534
535 WHEN OTHERS THEN
536 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
537 p_api_name => l_api_name,
538 p_pkg_name => G_PKG_NAME,
539 p_exc_name => 'OTHERS',
540 x_msg_count => x_msg_count,
541 x_msg_data => x_msg_data,
542 p_api_type => g_api_type);
543 END create_pool_trx_khr_book;
544
545 PROCEDURE create_pool_trx_khr_reverse(p_api_version IN NUMBER
546 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
547 ,x_return_status OUT NOCOPY VARCHAR2
548 ,x_msg_count OUT NOCOPY NUMBER
549 ,x_msg_data OUT NOCOPY VARCHAR2
550 ,p_chr_id IN okc_k_headers_b.id%TYPE
551 ,p_reversal_date IN DATE
552 ,p_override_trx_reason IN okl_trx_subsidy_pools.trx_reason_code%TYPE
553 ) IS
554
555 -- cursor to fetch all subsidies attached in the contract
556 CURSOR c_subsidy_csr(cp_chr_id IN okc_k_headers_b.id%TYPE) IS
557 SELECT clet_sub.name subsidy_name,
558 clet_asst.name asset_number,
559 subb.id subsidy_id
560 FROM okl_subsidies_b subb,
561 okl_k_lines kle_sub,
562 okc_k_lines_tl clet_sub,
563 okc_k_lines_b cleb_sub,
564 okc_line_styles_b lseb_sub,
565 okc_k_lines_tl clet_asst,
566 okc_k_lines_b cleb_asst,
567 okc_line_styles_b lseb_asst
568 WHERE subb.id = kle_sub.subsidy_id
569 AND kle_sub.id = cleb_sub.id
570 AND clet_sub.id = cleb_sub.id
571 AND clet_sub.language = userenv('LANG')
572 AND cleb_sub.cle_id = cleb_asst.id
573 AND cleb_sub.dnz_chr_id = cleb_asst.dnz_chr_id
574 AND cleb_sub.sts_code <> 'ABANDONED'
575 AND lseb_sub.id = cleb_sub.lse_id
576 AND lseb_sub.lty_code = 'SUBSIDY'
577 AND clet_asst.id = cleb_asst.id
578 AND clet_asst.language = userenv('LANG')
579 AND cleb_asst.chr_id = cp_chr_id
580 AND cleb_asst.dnz_chr_id = cp_chr_id
581 AND lseb_asst.id = cleb_asst.lse_id
582 AND lseb_asst.lty_code = 'FREE_FORM1'
583 AND cleb_asst.sts_code <> 'ABANDONED';
584
585 -- cursor to fetch the pool transaction records with REDUCTION operation, created when the contract was booked
586 CURSOR c_sub_pool_trx_csr (cp_chr_id IN okc_k_headers_b.id%TYPE
587 ,cp_asset_number IN okc_k_lines_tl.name%TYPE
588 ,cp_subsidy_id IN okl_subsidies_b.id%TYPE
589 ,cp_subsidy_pool_id IN okl_subsidy_pools_b.id%TYPE
590 ) IS
591 SELECT source_type_code
592 ,source_object_id
593 ,subsidy_pool_id
594 ,dnz_asset_number
595 ,vendor_id
596 ,source_trx_date
597 ,trx_date
598 ,subsidy_id
599 ,trx_reason_code
600 ,trx_currency_code
601 ,trx_amount
602 ,subsidy_pool_currency_code
603 ,subsidy_pool_amount
604 ,conversion_rate
605 FROM okl_trx_subsidy_pools
606 WHERE source_object_id = cp_chr_id
607 AND dnz_asset_number = cp_asset_number
608 AND subsidy_id = cp_subsidy_id
609 AND subsidy_pool_id = cp_subsidy_pool_id
610 AND source_type_code = G_TRX_SOURCE_TYPE_CODE
611 AND trx_type_code = G_REDUCTION_TYPE_CODE;
612 cv_sub_pool_trx_rec c_sub_pool_trx_csr%ROWTYPE;
613
614 lx_sub_pool_id okl_subsidy_pools_b.id%TYPE;
615 lx_sub_pool_curr_code okl_subsidy_pools_b.currency_code%TYPE;
616 lv_sixv_tbl sixv_tbl_type;
617 lx_sixv_tbl sixv_tbl_type;
618 idx PLS_INTEGER;
619 l_sub_pool_applicable VARCHAR2(10);
620 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'POOL_TRX_KHR_REVERSE';
621
622 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_SUBSIDY_POOL_AUTH_TRX_PVT.CREATE_POOL_TRX_KHR_REVERSE';
623 l_debug_enabled VARCHAR2(10);
624 is_debug_procedure_on BOOLEAN;
625 is_debug_statement_on BOOLEAN;
626
627 BEGIN
628 x_return_status := OKL_API.G_RET_STS_SUCCESS;
629
630 l_debug_enabled := okl_debug_pub.check_log_enabled;
631 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_PROCEDURE);
632 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
633 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRSIUB.pls call create_pool_trx_khr_reverse');
634 END IF;
635
636 -- Call start_activity to create savepoint, check compatibility and initialize message list
637 x_return_status := OKL_API.START_ACTIVITY (
638 l_api_name
639 ,p_init_msg_list
640 ,'_PVT'
641 ,x_return_status);
642 -- Check if activity started successfully
643 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
644 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
645 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
646 RAISE OKL_API.G_EXCEPTION_ERROR;
647 END IF;
648
649 -- check for logging on STATEMENT level
650 is_debug_statement_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_STATEMENT);
651
652 idx := 0; lv_sixv_tbl.DELETE; lx_sixv_tbl.DELETE;
653 -- loop for all subsidies and process for subsidy pool transaction reversal
654 FOR c_subsidy_csr_rec IN c_subsidy_csr(cp_chr_id => p_chr_id) LOOP
655 -- check if the subsidy is associated with a subsidy pool
656 -- initialize for every iteration
657 l_sub_pool_applicable := 'N';
658 -- check if the subisdy is associated with a subsidy pool, process further only if subsidy pool exists.
659 l_sub_pool_applicable := okl_asset_subsidy_pvt.is_sub_assoc_with_pool(p_subsidy_id => c_subsidy_csr_rec.subsidy_id
660 ,x_subsidy_pool_id => lx_sub_pool_id
661 ,x_sub_pool_curr_code => lx_sub_pool_curr_code
662 );
663
664 IF(l_sub_pool_applicable = 'Y')THEN
665 -- now that the pool is applicable, verify if there exists a pool transaction for the contract, asset, subsidy, subsidy_pool,
666 -- and trx_type_code of REDUCTION. this record should be present for reversal. if the record is not found then we need to
667 -- raise an error. this is because the transaction reversal cannot be done until the transaction was not created in the first instance
668 -- there can never be the case that a subsidy pool is applicable to a subsidy and there are no REDUCTION type transactions
669 -- for that subsidy in the okl_trx_subsidy_pools table
670 OPEN c_sub_pool_trx_csr(cp_chr_id => p_chr_id
671 ,cp_asset_number => c_subsidy_csr_rec.asset_number
672 ,cp_subsidy_id => c_subsidy_csr_rec.subsidy_id
673 ,cp_subsidy_pool_id => lx_sub_pool_id
674 );
675 FETCH c_sub_pool_trx_csr INTO cv_sub_pool_trx_rec;
676 IF(c_sub_pool_trx_csr%NOTFOUND)THEN
677 CLOSE c_sub_pool_trx_csr;
678 x_return_status := OKL_API.G_RET_STS_ERROR;
679 OKL_API.Set_Message(p_app_name => G_APP_NAME,
680 p_msg_name => G_NO_SUB_POOL_TRX_FOUND,
681 p_token1 => 'SUBSIDY',
682 p_token1_value => c_subsidy_csr_rec.subsidy_name,
683 p_token2 => 'ASSET_NUMBER',
684 p_token2_value => c_subsidy_csr_rec.asset_number);
685 ELSE
686 -- increment the index
687 idx := idx + 1;
688 -- since we are adding back to pool balance, the trx_type_code is ADDITION
689 lv_sixv_tbl(idx).trx_type_code := G_ADDITION_TYPE_CODE;
690 lv_sixv_tbl(idx).source_type_code := cv_sub_pool_trx_rec.source_type_code;
691 lv_sixv_tbl(idx).source_object_id := cv_sub_pool_trx_rec.source_object_id;
692 lv_sixv_tbl(idx).subsidy_pool_id := cv_sub_pool_trx_rec.subsidy_pool_id;
693 lv_sixv_tbl(idx).dnz_asset_number := cv_sub_pool_trx_rec.dnz_asset_number;
694 lv_sixv_tbl(idx).vendor_id := cv_sub_pool_trx_rec.vendor_id;
695 lv_sixv_tbl(idx).source_trx_date := p_reversal_date;
696 lv_sixv_tbl(idx).subsidy_id := cv_sub_pool_trx_rec.subsidy_id;
697 -- note that the default trx_reason_code is that of G_CONTRACT_REVERSAL_CODE
698 -- but this api can also be called from split contract, in which case the p_override_trx_reason is substitued for the
699 -- transaction reason code
700 lv_sixv_tbl(idx).trx_reason_code := NVL(p_override_trx_reason, G_CONTRACT_REVERSAL_CODE);
701 lv_sixv_tbl(idx).trx_currency_code := cv_sub_pool_trx_rec.trx_currency_code;
702 -- the transaction amount is that amount that was initially reduced.
703 lv_sixv_tbl(idx).trx_amount := cv_sub_pool_trx_rec.trx_amount;
704 lv_sixv_tbl(idx).subsidy_pool_currency_code := cv_sub_pool_trx_rec.subsidy_pool_currency_code;
705 -- the subsidy pool amount is that amount that was initially reduced. this is not recalculated for ADDITION operation
706 -- as the conversion rate factor might result into a different amount the differential becomes then unexplained
707 lv_sixv_tbl(idx).subsidy_pool_amount := cv_sub_pool_trx_rec.subsidy_pool_amount;
708 lv_sixv_tbl(idx).conversion_rate := cv_sub_pool_trx_rec.conversion_rate;
709
710 -- write to log
711 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
712 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
713 l_module,
714 idx ||' record processed for '||lv_sixv_tbl(idx).trx_type_code||' lv_sixv_tbl(idx).trx_reason_code '||lv_sixv_tbl(idx).trx_reason_code
715 );
716 END IF; -- end of NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on
717 CLOSE c_sub_pool_trx_csr;
718 END IF; -- end of c_sub_pool_trx_csr%NOTFOUND
719 ELSE
720 -- case of subsidy is a stand alone subsidy
721 -- write to log
722 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
723 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
724 l_module,
725 'c_subsidy_csr_rec.subsidy_id '||c_subsidy_csr_rec.subsidy_id||' l_sub_pool_applicable '||l_sub_pool_applicable
726 );
727 END IF; -- end of NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on
728 END IF; -- end of l_sub_pool_applicable = 'Y'
729 END LOOP; -- end of for c_subsidy_csr_rec IN c_subsidy_csr(cp_chr_id => p_chr_id) loop
730
731 -- now check the length of the table lv_sixv_tbl and then call the okl_subsidy_pool_trx_pvt api to create ADDITION transactions
732 IF(lv_sixv_tbl.COUNT > 0)THEN
733 okl_subsidy_pool_trx_pvt.create_pool_transaction(p_api_version => p_api_version
734 ,p_init_msg_list => OKL_API.G_TRUE
735 ,x_return_status => x_return_status
736 ,x_msg_count => x_msg_count
737 ,x_msg_data => x_msg_data
738 ,p_sixv_tbl => lv_sixv_tbl
739 ,x_sixv_tbl => lx_sixv_tbl
740 );
741 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
742 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
743 l_module,
744 'okl_subsidy_pool_trx_pvt.create_pool_transaction with status '||x_return_status || ' x_msg_data '||x_msg_data
745 );
746 END IF; -- end of NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on
747 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
748 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
749 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
750 RAISE OKL_API.G_EXCEPTION_ERROR;
751 END IF;
752 END IF;
753
754 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
755 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRSIUB.pls call create_pool_trx_khr_reverse');
756 END IF;
757
758 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
759
760 EXCEPTION
761 WHEN OKL_API.G_EXCEPTION_ERROR THEN
762 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
763 p_api_name => l_api_name,
764 p_pkg_name => G_PKG_NAME,
765 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
766 x_msg_count => x_msg_count,
767 x_msg_data => x_msg_data,
768 p_api_type => g_api_type);
769
770 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
771 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
772 p_api_name => l_api_name,
773 p_pkg_name => G_PKG_NAME,
774 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
775 x_msg_count => x_msg_count,
776 x_msg_data => x_msg_data,
777 p_api_type => g_api_type);
778
779 WHEN OTHERS THEN
780 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
781 p_api_name => l_api_name,
782 p_pkg_name => G_PKG_NAME,
783 p_exc_name => 'OTHERS',
784 x_msg_count => x_msg_count,
785 x_msg_data => x_msg_data,
786 p_api_type => g_api_type);
787
788 END create_pool_trx_khr_reverse;
789
790 PROCEDURE create_pool_trx_khr_rbk(p_api_version IN NUMBER
791 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
792 ,x_return_status OUT NOCOPY VARCHAR2
793 ,x_msg_count OUT NOCOPY NUMBER
794 ,x_msg_data OUT NOCOPY VARCHAR2
795 ,p_rbk_chr_id IN okc_k_headers_b.id%TYPE
796 ,p_orig_chr_id IN okc_k_headers_b.id%TYPE
797 ) IS
798
799 ---------------------------------------------------------------------------
800 --get subsidy elements from rebook copy contract
801 ---------------------------------------------------------------------------
802 CURSOR l_subelm_rbk_csr(p_chr_id okc_k_headers_b.id%TYPE) IS
803 SELECT kle.SUBSIDY_ID
804 ,clet.NAME
805 ,clet.ITEM_DESCRIPTION
806 ,kle.AMOUNT
807 ,kle.SUBSIDY_OVERRIDE_AMOUNT
808 ,cleb.orig_system_id1
809 ,cplb.object1_id1 vendor_id
810 ,cplb.id cpl_id
811 ,kle.sty_id sty_id
812 ,cleb.cle_id asset_id
813 ,cleb.currency_code
814 FROM okl_k_lines kle
815 ,okc_k_lines_tl clet
816 ,okc_k_lines_b cleb
817 ,okc_statuses_b stsb
818 ,okc_line_styles_b lseb
819 ,okc_k_party_roles_b cplb
820 WHERE kle.id = cleb.id
821 AND clet.id = cleb.id
822 AND clet.language = userenv('LANG')
823 AND cleb.dnz_chr_id = p_chr_id
824 AND cleb.orig_system_id1 is not null
825 AND stsb.code = cleb.sts_code
826 AND stsb.ste_code not in ('CANCELLED')
827 AND lseb.id = cleb.lse_id
828 AND lseb.lty_code = 'SUBSIDY'
829 AND cplb.cle_id = cleb.id
830 AND cplb.rle_code = 'OKL_VENDOR'
831 AND cplb.dnz_chr_id = p_chr_id;
832 l_subelm_rbk_rec l_subelm_rbk_csr%ROWTYPE;
833
834 ---------------------------------------------------------------------------
835 --get subsidy elements from original contract
836 ---------------------------------------------------------------------------
837 cursor l_subelm_orig_csr(p_cle_id in number,
838 p_chr_id okc_k_headers_b.id%TYPE) is
839 SELECT kle.SUBSIDY_ID
840 ,clet.NAME
841 ,clet.ITEM_DESCRIPTION
842 ,kle.AMOUNT
843 ,kle.SUBSIDY_OVERRIDE_AMOUNT
844 ,cplb.object1_id1 vendor_id
845 ,cplb.id cpl_id
846 ,kle.sty_id sty_id
847 ,cleb.cle_id asset_id
848 ,cleb.currency_code
849 FROM okl_k_lines kle
850 ,okc_k_lines_tl clet
851 ,okc_k_lines_b cleb
852 ,okc_statuses_b stsb
853 ,okc_line_styles_b lseb
854 ,okc_k_party_roles_b cplb
855 WHERE kle.id = cleb.id
856 AND clet.id = cleb.id
857 AND clet.language = userenv('LANG')
858 AND cleb.id = p_cle_id
859 AND cleb.dnz_chr_id = p_chr_id
860 AND stsb.code = cleb.sts_code
861 AND stsb.ste_code not in ('CANCELLED')
862 AND lseb.id = cleb.lse_id
863 AND lseb.lty_code = 'SUBSIDY'
864 AND cplb.cle_id = cleb.id
865 AND cplb.rle_code = 'OKL_VENDOR'
866 AND cplb.dnz_chr_id = p_chr_id;
867 l_subelm_orig_rec l_subelm_orig_csr%ROWTYPE;
868
869 ------------------------------------------------------------------------------
870 --cursor to find out subsidy line which has been deleted
871 ------------------------------------------------------------------------------
872 cursor l_del_sub_csr (p_orig_chr_id okc_k_headers_b.id%TYPE,
873 p_rbk_chr_id okc_k_headers_b.id%TYPE) is
874 SELECT cleb.id subsidy_id
875 ,cplb.id cpl_id
876 ,cplb.object1_id1 vendor_id
877 ,kle.AMOUNT
878 ,kle.SUBSIDY_OVERRIDE_AMOUNT
879 ,cleb.cle_id asset_id
880 ,cleb.currency_code
881 ,kle.subsidy_id subsidy
882 FROM okc_k_lines_b cleb
883 ,okc_line_styles_b lseb
884 ,okc_k_party_roles_b cplb
885 ,okl_k_lines kle
886 WHERE cleb.dnz_chr_id = p_orig_chr_id
887 AND lseb.id = cleb.lse_id
888 AND lseb.lty_code = 'SUBSIDY'
889 AND cplb.cle_id = cleb.id
890 AND cplb.dnz_chr_id = p_orig_chr_id
891 AND cplb.rle_code = 'OKL_VENDOR'
892 AND kle.id = cleb.id
893 --line was deleted from rebook copy :
894 AND NOT EXISTS (SELECT '1'
895 FROM okc_k_lines_b cleb2
896 WHERE cleb2.orig_system_id1 = cleb.id
897 AND cleb2.dnz_chr_id = p_rbk_chr_id)
898 --line is not a new line created during this rebook
899 AND NOT EXISTS (SELECT '1'
900 FROM okc_k_lines_b cleb3
901 WHERE cleb3.id = cleb.orig_system_id1
902 AND cleb3.dnz_chr_id = p_rbk_chr_id);
903 lv_rec_del_sub_csr l_del_sub_csr%ROWTYPE;
904
905 ------------------------------------------------------------------------------
906 --cursor to find out new subsidy lines which have been added
907 ------------------------------------------------------------------------------
908 CURSOR l_new_sub_csr (p_chr_id okc_k_headers_b.id%TYPE) IS
909 SELECT kle.subsidy_id subsidy_id
910 ,cleb.id subsidy_cle_id
911 ,clet.name name
912 ,kle.amount amount
913 ,kle.subsidy_override_amount subsidy_override_amount
914 ,cleb.dnz_chr_id dnz_chr_id
915 ,cleb.cle_id asset_cle_id
916 ,cplb.id cpl_id
917 ,cplb.object1_id1 vendor_id
918 ,cleb.lse_id lse_id
919 ,cleb.start_date start_date
920 ,cleb.end_date end_date
921 ,cleb.currency_code currency_code
922 ,cleb.sts_code sts_code
923 ,kle.sty_id sty_id
924 ,asst_cleb.orig_system_id1 orig_asst_cle_id
925 FROM okc_k_lines_b asst_cleb
926 ,okc_statuses_b asst_sts
927 ,okc_k_party_roles_b cplb
928 ,okc_k_lines_tl clet
929 ,okl_k_lines kle
930 ,okc_line_styles_b lseb
931 ,okc_k_lines_b cleb
932 WHERE asst_cleb.id = cleb.cle_id
933 AND asst_cleb.dnz_chr_id = cleb.dnz_chr_id
934 AND asst_sts.code = asst_cleb.sts_code
935 AND asst_sts.ste_code not in ('HOLD','EXPIRED','TERMINATED','CANCELLED')
936 AND cplb.jtot_object1_code = 'OKX_VENDOR'
937 AND cplb.dnz_chr_id = cleb.dnz_chr_id
938 AND cplb.cle_id = cleb.id
939 AND cplb.rle_code = 'OKL_VENDOR'
940 AND clet.id = cleb.id
941 AND clet.language = userenv('LANG')
942 AND kle.id = cleb.id
943 AND lseb.id = cleb.lse_id
944 AND lseb.lty_code = 'SUBSIDY'
945 AND cleb.dnz_chr_id = p_chr_id
946 AND cleb.orig_system_id1 is null
947 AND asst_cleb.orig_system_id1 is not null
948 AND cleb.sts_code <> 'ABANDONED';
949 l_new_sub_rec l_new_sub_csr%ROWTYPE;
950
951 -- cursor to get the asset number for pool transaction record
952 -- added for subsidy pools enhancement
953 CURSOR c_get_asst_number (cp_asset_id okc_k_lines_b.id%TYPE)IS
954 SELECT name asset_number
955 FROM okc_k_lines_v
956 WHERE id = cp_asset_id;
957 lv_asset_number okc_k_lines_v.name%TYPE;
958
959 CURSOR c_get_orig_asst (cp_asset_id okc_k_lines_b.id%TYPE) IS
960 SELECT orig_system_id1
961 FROM okc_k_lines_b
962 WHERE id = cp_asset_id;
963 cv_get_orig_asst c_get_orig_asst%ROWTYPE;
964
965 lv_orig_subsidy_applic VARCHAR2(10);
966 lv_rbk_subsidy_applic VARCHAR2(10);
967 lx_subsidy_pool_id okl_subsidy_pools_b.id%TYPE;
968 lx_sub_pool_curr_code okl_subsidy_pools_b.currency_code%TYPE;
969 -- sjalasut, added variables for subsidy pools enhancement. END
970
971 CURSOR l_chk_rbk_csr IS
972 SELECT ktrx.date_transaction_occurred
973 FROM okc_k_headers_b chr
974 ,okl_trx_contracts ktrx
975 WHERE ktrx.khr_id_new = chr.id
976 AND ktrx.tsu_code = 'ENTERED'
977 AND ktrx.rbr_code IS NOT NULL
978 AND ktrx.tcn_type = 'TRBK'
979 --rkuttiya added for 12.1.1 Multi GAAP
980 AND ktrx.representation_type = 'PRIMARY'
981 --
982 AND CHR.id = p_rbk_chr_id
983 AND CHR.ORIG_SYSTEM_SOURCE_CODE = 'OKL_REBOOK';
984
985 lv_source_trx_date okl_trx_subsidy_pools.source_trx_date%TYPE;
986 -- counter to manage all pool transaction rows.
987 idx PLS_INTEGER;
988
989 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'POOL_TRX_KHR_RBK';
990 lv_six_tbl sixv_tbl_type;
991 lx_six_tbl sixv_tbl_type;
992
993 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_SUBSIDY_POOL_AUTH_TRX_PVT.CREATE_POOL_TRX_KHR_RBK';
994 l_debug_enabled VARCHAR2(10);
995 is_debug_procedure_on BOOLEAN;
996 is_debug_statement_on BOOLEAN;
997
998 BEGIN
999 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1000
1001 l_debug_enabled := okl_debug_pub.check_log_enabled;
1002 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_PROCEDURE);
1003 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
1004 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRSIUB.pls call create_pool_trx_khr_rbk');
1005 END IF;
1006
1007 -- Call start_activity to create savepoint, check compatibility and initialize message list
1008 x_return_status := OKL_API.START_ACTIVITY(l_api_name,p_init_msg_list,'_PVT',x_return_status);
1009 -- Check if activity started successfully
1010 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1011 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1012 ELSIF(x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1013 RAISE OKL_API.G_EXCEPTION_ERROR;
1014 END IF;
1015
1016 -- check for logging on STATEMENT level
1017 is_debug_statement_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_STATEMENT);
1018
1019 -- initialize the counter. this is done only once for the complete pool transaction synchronization
1020 idx := 0;
1021 -- initialize the plsql table
1022 lv_six_tbl.DELETE; lx_six_tbl.DELETE;
1023 -- start of processing. first compare the lines that have changed between the original contract and rebook copy
1024 OPEN l_subelm_rbk_csr(p_chr_id => p_rbk_chr_id);
1025 LOOP
1026 FETCH l_subelm_rbk_csr INTO l_subelm_rbk_rec;
1027 EXIT WHEN l_subelm_rbk_csr%NOTFOUND;
1028
1029 --2. Fetch subsidy line attributes for original contract
1030 OPEN l_subelm_orig_csr(p_cle_id => l_subelm_rbk_rec.orig_system_id1,p_chr_id => p_orig_chr_id);
1031 FETCH l_subelm_orig_csr INTO l_subelm_orig_rec;
1032 IF l_subelm_orig_csr%NOTFOUND THEN
1033 NULL;
1034 ELSE
1035 --3. syncronize subsidy line attributes in case of differences
1036 IF(NVL(l_subelm_orig_rec.amount,0) <> NVL(l_subelm_rbk_rec.Amount,0)) OR
1037 (NVL(l_subelm_orig_rec.subsidy_override_amount,0) <> NVL(l_subelm_rbk_rec.subsidy_override_Amount,0)) OR
1038 (l_subelm_orig_rec.subsidy_id <> l_subelm_rbk_rec.subsidy_id) OR
1039 (l_subelm_orig_rec.sty_id <> l_subelm_rbk_rec.sty_id) THEN
1040
1041 lv_orig_subsidy_applic := 'N';
1042 lv_orig_subsidy_applic := okl_asset_subsidy_pvt.is_sub_assoc_with_pool(p_subsidy_id => l_subelm_orig_rec.subsidy_id
1043 ,x_subsidy_pool_id => lx_subsidy_pool_id
1044 ,x_sub_pool_curr_code => lx_sub_pool_curr_code);
1045 IF(lv_orig_subsidy_applic = 'Y')THEN
1046 idx := idx + 1;
1047 lv_six_tbl(idx).trx_type_code := G_ADDITION_TYPE_CODE;
1048 lv_six_tbl(idx).source_object_id := p_orig_chr_id; -- this will always be p_orig_chr_id for a rebook case
1049 lv_six_tbl(idx).subsidy_pool_id := lx_subsidy_pool_id;
1050 lv_six_tbl(idx).vendor_id := l_subelm_orig_rec.vendor_id;
1051 lv_six_tbl(idx).subsidy_id := l_subelm_orig_rec.subsidy_id;
1052 lv_six_tbl(idx).trx_amount := NVL(l_subelm_orig_rec.subsidy_override_amount,NVL(l_subelm_orig_rec.amount,0));
1053 lv_six_tbl(idx).trx_currency_code := l_subelm_orig_rec.currency_code;
1054 lv_six_tbl(idx).subsidy_pool_currency_code := lx_sub_pool_curr_code;
1055 -- since this is the original contract, the asset line is the line we are interested to get asset_number from
1056 lv_asset_number := NULL;
1057 OPEN c_get_asst_number(l_subelm_orig_rec.asset_id); FETCH c_get_asst_number INTO lv_asset_number;
1058 CLOSE c_get_asst_number;
1059 lv_six_tbl(idx).dnz_asset_number := lv_asset_number;
1060 -- write to log
1061 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
1062 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
1063 l_module,
1064 'synch ADD lv_six_tbl('||idx||').source_object_id'||lv_six_tbl(idx).source_object_id||
1065 ' subsidy_pool_id '||lx_subsidy_pool_id||' vendor_id '||lv_six_tbl(idx).vendor_id||
1066 ' subsidy_id '||l_subelm_orig_rec.subsidy_id||' trx_amount '||lv_six_tbl(idx).trx_amount||
1067 ' dnz_asset_number '||lv_six_tbl(idx).dnz_asset_number
1068 );
1069 END IF; -- end of NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on
1070 END IF;
1071
1072 lx_subsidy_pool_id := NULL;
1073 lx_sub_pool_curr_code := NULL;
1074 -- check to see if the subsidy on the rebook contract is associated with a subsidy pool, and if so then reduce the pool balance
1075 lv_rbk_subsidy_applic := 'N';
1076 lv_rbk_subsidy_applic := okl_asset_subsidy_pvt.is_sub_assoc_with_pool(p_subsidy_id => l_subelm_rbk_rec.subsidy_id
1077 ,x_subsidy_pool_id => lx_subsidy_pool_id
1078 ,x_sub_pool_curr_code => lx_sub_pool_curr_code);
1079 IF(lv_rbk_subsidy_applic = 'Y')THEN
1080 idx := idx + 1;
1081 lv_six_tbl(idx).trx_type_code := G_REDUCTION_TYPE_CODE;
1082 lv_six_tbl(idx).source_object_id := p_orig_chr_id; -- this will always be p_orig_chr_id for a rebook case
1083 lv_six_tbl(idx).subsidy_pool_id := lx_subsidy_pool_id;
1084 lv_six_tbl(idx).vendor_id := l_subelm_rbk_rec.vendor_id;
1085 lv_six_tbl(idx).subsidy_id := l_subelm_rbk_rec.subsidy_id;
1086 lv_six_tbl(idx).trx_amount := NVL(l_subelm_rbk_rec.subsidy_override_Amount,NVL(l_subelm_rbk_rec.Amount,0));
1087 lv_six_tbl(idx).trx_currency_code := l_subelm_rbk_rec.currency_code;
1088 lv_six_tbl(idx).subsidy_pool_currency_code := lx_sub_pool_curr_code;
1089 -- fetch the original asset number from the new asset line on the rebook copy
1090 OPEN c_get_orig_asst (l_subelm_rbk_rec.asset_id); FETCH c_get_orig_asst INTO cv_get_orig_asst;
1091 CLOSE c_get_orig_asst;
1092 lv_asset_number := NULL;
1093 OPEN c_get_asst_number(cv_get_orig_asst.orig_system_id1); FETCH c_get_asst_number INTO lv_asset_number;
1094 CLOSE c_get_asst_number;
1095 lv_six_tbl(idx).dnz_asset_number := lv_asset_number;
1096 -- write to log
1097 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
1098 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
1099 l_module,
1100 'synch REDUCE lv_six_tbl('||idx||').source_object_id'||lv_six_tbl(idx).source_object_id||
1101 ' subsidy_pool_id '||lx_subsidy_pool_id||' vendor_id '||lv_six_tbl(idx).vendor_id||
1102 ' subsidy_id '||lv_six_tbl(idx).subsidy_id||' trx_amount '||lv_six_tbl(idx).trx_amount||
1103 ' dnz_asset_number '||lv_six_tbl(idx).dnz_asset_number
1104 );
1105 END IF; -- end of NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on
1106 END IF; -- end of lv_rbk_subsidy_applic = 'Y'
1107 END IF; -- end of amount comparision
1108 END IF; -- end of l_subelm_orig_csr%NOTFOUND
1109 CLOSE L_subelm_orig_csr;
1110 END LOOP;
1111 CLOSE l_subelm_rbk_csr;
1112
1113 -- process subsidy pool transactions whose subsidy lines have been deleted during rebook
1114 FOR lv_rec_del_sub_csr IN l_del_sub_csr (p_orig_chr_id => p_orig_chr_id, p_rbk_chr_id => p_rbk_chr_id) LOOP
1115 lx_subsidy_pool_id := NULL;
1116 lx_sub_pool_curr_code := NULL;
1117 lv_rbk_subsidy_applic := 'N';
1118 lv_rbk_subsidy_applic := okl_asset_subsidy_pvt.is_sub_assoc_with_pool(p_subsidy_id => lv_rec_del_sub_csr.subsidy
1119 ,x_subsidy_pool_id => lx_subsidy_pool_id
1120 ,x_sub_pool_curr_code => lx_sub_pool_curr_code);
1121 IF(lv_rbk_subsidy_applic = 'Y')THEN
1122 idx := idx + 1;
1123 lv_six_tbl(idx).trx_type_code := G_ADDITION_TYPE_CODE;
1124 lv_six_tbl(idx).source_object_id := p_orig_chr_id; -- this will always be p_orig_chr_id for a rebook case
1125 lv_six_tbl(idx).subsidy_pool_id := lx_subsidy_pool_id;
1126 lv_six_tbl(idx).vendor_id := lv_rec_del_sub_csr.vendor_id;
1127 lv_six_tbl(idx).subsidy_id := lv_rec_del_sub_csr.subsidy;
1128 lv_six_tbl(idx).trx_amount := NVL(lv_rec_del_sub_csr.subsidy_override_amount,NVL(lv_rec_del_sub_csr.amount,0));
1129 lv_six_tbl(idx).trx_currency_code := lv_rec_del_sub_csr.currency_code;
1130 lv_six_tbl(idx).subsidy_pool_currency_code := lx_sub_pool_curr_code;
1131 lv_asset_number := NULL;
1132 OPEN c_get_asst_number(lv_rec_del_sub_csr.asset_id); FETCH c_get_asst_number INTO lv_asset_number;
1133 CLOSE c_get_asst_number;
1134 lv_six_tbl(idx).dnz_asset_number := lv_asset_number;
1135
1136 -- write to log
1137 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
1138 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
1139 l_module,
1140 'synch ADD for deleted subsidy lv_six_tbl('||idx||').source_object_id'||lv_six_tbl(idx).source_object_id||
1141 ' subsidy_pool_id '||lx_subsidy_pool_id||' vendor_id '||lv_six_tbl(idx).vendor_id||
1142 ' subsidy_id '||lv_six_tbl(idx).subsidy_id||' trx_amount '||lv_six_tbl(idx).trx_amount||
1143 ' dnz_asset_number '||lv_six_tbl(idx).dnz_asset_number
1144 );
1145 END IF; -- end of NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on
1146 END IF; -- end of lv_rbk_subsidy_applic = 'Y'
1147 END LOOP;
1148
1149 -- process for subsidy pool transactions for new subsidy lines that have been added on the rebook copy
1150 FOR l_new_sub_rec IN l_new_sub_csr (p_chr_id => p_rbk_chr_id)
1151 LOOP
1152 lx_subsidy_pool_id := NULL;
1153 lx_sub_pool_curr_code := NULL;
1154 -- check to see if the subsidy on the rebook contract is associated with a subsidy pool, and if so
1155 -- then reduce the pool balance
1156 lv_rbk_subsidy_applic := 'N';
1157 lv_rbk_subsidy_applic := okl_asset_subsidy_pvt.is_sub_assoc_with_pool(p_subsidy_id => l_subelm_rbk_rec.subsidy_id
1158 ,x_subsidy_pool_id => lx_subsidy_pool_id
1159 ,x_sub_pool_curr_code => lx_sub_pool_curr_code);
1160 IF(lv_rbk_subsidy_applic = 'Y')THEN
1161 idx := idx + 1;
1162 lv_six_tbl(idx).trx_type_code := G_REDUCTION_TYPE_CODE;
1163 lv_six_tbl(idx).source_object_id := p_orig_chr_id; -- this will always be p_orig_chr_id for a rebook case
1164 lv_six_tbl(idx).subsidy_pool_id := lx_subsidy_pool_id;
1165 lv_six_tbl(idx).vendor_id := l_new_sub_rec.vendor_id;
1166 lv_six_tbl(idx).subsidy_id := l_new_sub_rec.subsidy_id;
1167 lv_six_tbl(idx).trx_amount := NVL(l_new_sub_rec.subsidy_override_Amount,NVL(l_new_sub_rec.Amount,0));
1168 lv_six_tbl(idx).trx_currency_code := l_new_sub_rec.currency_code;
1169 lv_six_tbl(idx).subsidy_pool_currency_code := lx_sub_pool_curr_code;
1170 lv_asset_number := NULL;
1171 OPEN c_get_asst_number(l_new_sub_rec.asset_cle_id); FETCH c_get_asst_number INTO lv_asset_number;
1172 CLOSE c_get_asst_number;
1173 lv_six_tbl(idx).dnz_asset_number := lv_asset_number;
1174
1175 -- write to log
1176 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
1177 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
1178 l_module,
1179 'synch REDUCE for new subsidy lv_six_tbl('||idx||').source_object_id'||lv_six_tbl(idx).source_object_id||
1180 ' subsidy_pool_id '||lx_subsidy_pool_id||' vendor_id '||lv_six_tbl(idx).vendor_id||
1181 ' subsidy_id '||lv_six_tbl(idx).subsidy_id||' trx_amount '||lv_six_tbl(idx).trx_amount||
1182 ' dnz_asset_number '||lv_six_tbl(idx).dnz_asset_number
1183 );
1184 END IF; -- end of NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on
1185 END IF; -- end of lv_rbk_subsidy_applic = 'Y'
1186 END LOOP;
1187
1188 IF(idx > 0) THEN
1189 -- derive the source transaction date
1190 lv_source_trx_date := NULL;
1191 OPEN l_chk_rbk_csr; FETCH l_chk_rbk_csr INTO lv_source_trx_date;
1192 CLOSE l_chk_rbk_csr;
1193 -- call local procedure that populates other parameters and then calls the okl_subsidy_pool_trx_pvt.create_pool_transaction
1194 pool_trx_khr_rbk(p_source_trx_date => lv_source_trx_date, p_six_tbl => lv_six_tbl, x_return_status => x_return_status);
1195 -- write to log
1196 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
1197 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
1198 l_module,
1199 'pool_trx_khr_rbk returned with status '||x_return_status || ' x_msg_data '||x_msg_data
1200 );
1201 END IF; -- end of NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on
1202
1203 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1204 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1205 ELSIF(x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1206 RAISE OKL_API.G_EXCEPTION_ERROR;
1207 END IF;
1208 END IF;
1209
1210 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
1211 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRSIUB.pls call create_pool_trx_khr_rbk');
1212 END IF;
1213
1214 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
1215 EXCEPTION
1216 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1217 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1218 p_api_name => l_api_name,
1219 p_pkg_name => G_PKG_NAME,
1220 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1221 x_msg_count => x_msg_count,
1222 x_msg_data => x_msg_data,
1223 p_api_type => g_api_type);
1224
1225 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1226 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1227 p_api_name => l_api_name,
1228 p_pkg_name => G_PKG_NAME,
1229 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1230 x_msg_count => x_msg_count,
1231 x_msg_data => x_msg_data,
1232 p_api_type => g_api_type);
1233
1234 WHEN OTHERS THEN
1235 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1236 p_api_name => l_api_name,
1237 p_pkg_name => G_PKG_NAME,
1238 p_exc_name => 'OTHERS',
1239 x_msg_count => x_msg_count,
1240 x_msg_data => x_msg_data,
1241 p_api_type => g_api_type);
1242
1243 END create_pool_trx_khr_rbk;
1244
1245 PROCEDURE create_pool_trx_khr_split(p_api_version IN NUMBER
1246 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
1247 ,x_return_status OUT NOCOPY VARCHAR2
1248 ,x_msg_count OUT NOCOPY NUMBER
1249 ,x_msg_data OUT NOCOPY VARCHAR2
1250 ,p_new1_chr_id IN okc_k_headers_b.id%TYPE
1251 ,p_new2_chr_id IN okc_k_headers_b.id%TYPE
1252 ) IS
1253
1254 CURSOR c_get_orig_khr_csr(cp_chr_id okc_k_headers_b.id%TYPE) IS
1255 SELECT orig_system_id1
1256 FROM okc_k_headers_b
1257 WHERE id = cp_chr_id
1258 AND orig_system_source_code = 'OKL_SPLIT';
1259 lv_orig_khr_id okc_k_headers_b.id%TYPE;
1260
1261 CURSOR c_split_info_csr(cp_chr_id okc_k_headers_b.id%TYPE) IS
1262 SELECT khr.id, khr.contract_number, khr.start_date, trx.date_transaction_occurred
1263 FROM okc_k_headers_b khr
1264 ,okl_trx_contracts trx
1265 WHERE trx.khr_id = khr.id
1266 AND trx.tsu_code = 'PROCESSED'
1267 AND trx.tcn_type = 'SPLC'
1268 --rkuttiya added for 12.1.1 Multi GAAP
1269 AND trx.representation_type = 'PRIMARY'
1270 --
1271 AND khr.id = cp_chr_id;
1272 cv_split_info_rec c_split_info_csr%ROWTYPE;
1273
1274 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'POOL_TRX_KHR_SPLIT';
1275
1276 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_SUBSIDY_POOL_AUTH_TRX_PVT.CREATE_POOL_TRX_KHR_SPLIT';
1277 l_debug_enabled VARCHAR2(10);
1278 is_debug_procedure_on BOOLEAN;
1279 is_debug_statement_on BOOLEAN;
1280
1281 BEGIN
1282 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1283
1284 l_debug_enabled := okl_debug_pub.check_log_enabled;
1285 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_PROCEDURE);
1286 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
1287 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRSIUB.pls call create_pool_trx_khr_split');
1288 END IF;
1289
1290 -- Call start_activity to create savepoint, check compatibility and initialize message list
1291 x_return_status := OKL_API.START_ACTIVITY(l_api_name,p_init_msg_list,'_PVT',x_return_status);
1292 -- Check if activity started successfully
1293 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1294 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1295 ELSIF(x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1296 RAISE OKL_API.G_EXCEPTION_ERROR;
1297 END IF;
1298
1299 -- check for logging on STATEMENT level
1300 is_debug_statement_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_STATEMENT);
1301
1302 -- derive the original contract information from the first split contract
1303 lv_orig_khr_id := NULL;
1304 OPEN c_get_orig_khr_csr(cp_chr_id => p_new1_chr_id); FETCH c_get_orig_khr_csr INTO lv_orig_khr_id;
1305 CLOSE c_get_orig_khr_csr;
1306
1307 -- derive the split contract date from the contract id
1308 OPEN c_split_info_csr(cp_chr_id => lv_orig_khr_id); FETCH c_split_info_csr INTO cv_split_info_rec;
1309 CLOSE c_split_info_csr;
1310
1311 -- write to log
1312 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
1313 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
1314 l_module,
1315 'first split copy khr '||lv_orig_khr_id||' cv_split_info_rec.contract_number '||
1316 cv_split_info_rec.contract_number||' cv_split_info_rec.date_transaction_occurred '||
1317 cv_split_info_rec.date_transaction_occurred
1318 );
1319 END IF; -- end of NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on
1320
1321 -- for this original contract, all the subsidy pool transactions should be reversed.
1322 -- in pool terms, the pool balances should be added back. only those amounts are added back, whose subsidy lines
1323 -- are attached with a subsidy pool
1324 -- call the create_pool_trx_khr_reverse api as the logic is similar, pass the split contract date, contract id
1325 -- and the p_override_trx_reason
1326 create_pool_trx_khr_reverse(p_api_version => p_api_version
1327 ,p_init_msg_list => p_init_msg_list
1328 ,x_return_status => x_return_status
1329 ,x_msg_count => x_msg_count
1330 ,x_msg_data => x_msg_data
1331 ,p_chr_id => lv_orig_khr_id
1332 ,p_reversal_date => cv_split_info_rec.date_transaction_occurred
1333 ,p_override_trx_reason => G_CONTRACT_SPLIT_CODE
1334 );
1335 -- write to log
1336 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
1337 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
1338 l_module,
1339 'create_pool_trx_khr_reverse called from create_pool_trx_khr_split return status '||x_return_status || ' x_msg_data '||x_msg_data
1340 );
1341 END IF; -- end of NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on
1342
1343 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1344 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1345 ELSIF(x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1346 RAISE OKL_API.G_EXCEPTION_ERROR;
1347 END IF;
1348
1349 -- write to log
1350 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
1351 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
1352 l_module,
1353 'now reducing pool balance from the first split copy contract '||p_new1_chr_id||' '||trunc(cv_split_info_rec.date_transaction_occurred)
1354 );
1355 END IF; -- end of NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on
1356
1357 -- now process the first split contract. reduce from the pool balance for all the subsidy lines which are associated with a subsidy pool
1358 -- call local api for this reduction operation
1359 reduce_pool_balance(p_khr_id => to_number(p_new1_chr_id)
1360 ,p_source_trx_date => trunc(cv_split_info_rec.date_transaction_occurred)
1361 ,x_return_status => x_return_status);
1362 -- write to log
1363 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
1364 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
1365 l_module,
1366 'reduce pool balance from first contract api return status '||x_return_status||' x_msg_data '||x_msg_data
1367 );
1368 END IF; -- end of NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on
1369
1370 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1371 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1372 ELSIF(x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1373 RAISE OKL_API.G_EXCEPTION_ERROR;
1374 END IF;
1375
1376 -- no errors from the first contract, process the second split contract for pool balance reduction.
1377 -- call local api for this reduction operation
1378 reduce_pool_balance(p_khr_id => to_number(p_new2_chr_id)
1379 ,p_source_trx_date => trunc(cv_split_info_rec.date_transaction_occurred)
1380 ,x_return_status => x_return_status);
1381 -- write to log
1382 IF(NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on) THEN
1383 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,
1384 l_module,
1385 'reduce pool balance from second contract api return status '||x_return_status|| ' x_msg_data '||x_msg_data
1386 );
1387 END IF; -- end of NVL(l_debug_enabled,'N')='Y' AND is_debug_statement_on
1388
1389 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1390 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1391 ELSIF(x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1392 RAISE OKL_API.G_EXCEPTION_ERROR;
1393 END IF;
1394
1395 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
1396 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRSIUB.pls call create_pool_trx_khr_split');
1397 END IF;
1398
1399 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
1400 EXCEPTION
1401 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1402 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1403 p_api_name => l_api_name,
1404 p_pkg_name => G_PKG_NAME,
1405 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1406 x_msg_count => x_msg_count,
1407 x_msg_data => x_msg_data,
1408 p_api_type => g_api_type);
1409
1410 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1411 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1412 p_api_name => l_api_name,
1413 p_pkg_name => G_PKG_NAME,
1414 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1415 x_msg_count => x_msg_count,
1416 x_msg_data => x_msg_data,
1417 p_api_type => g_api_type);
1418
1419 WHEN OTHERS THEN
1420 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1421 p_api_name => l_api_name,
1422 p_pkg_name => G_PKG_NAME,
1423 p_exc_name => 'OTHERS',
1424 x_msg_count => x_msg_count,
1425 x_msg_data => x_msg_data,
1426 p_api_type => g_api_type);
1427 END create_pool_trx_khr_split;
1428
1429 END okl_subsidy_pool_auth_trx_pvt;
[Home] [Help]