[Home] [Help]
PACKAGE BODY: APPS.OKL_AM_PROCESS_ASSET_TRX_PVT
Source
1 PACKAGE BODY OKL_AM_PROCESS_ASSET_TRX_PVT AS
2 /* $Header: OKLRAMAB.pls 120.21.12010000.2 2008/08/28 14:20:00 rpillay ship $ */
3
4
5 -- Start of comments
6 --
7 -- Procedure Name : process_transactions_wrap
8 -- Description : This procedure is used to execute OKL_AM_PROCESS_ASSET_TRX_PVT
9 -- as a concurrent program. It has all the input parameters for
10 -- OKL_AM_PROCESS_ASSET_TRX_PVT and 2 standard OUT parameters - ERRBUF and RETCODE
11 -- Business Rules :
12 -- Parameters : p_contract_id - contract id
13 -- p_asset_id - asset_id
14 -- p_kle_id - line id
15 -- p_salvage_writedown_yn - flag indicating whether to process salvage valye transactions
16 --
17 -- Version : 1.0
18 -- History : SECHAWLA 16-JAN-03 Bug # 2754280
19 -- Changed the app name from OKL to OKC for g_unexpected_error
20 -- End of comments
21
22 PROCEDURE process_transactions_wrap( ERRBUF OUT NOCOPY VARCHAR2,
23 RETCODE OUT NOCOPY VARCHAR2 ,
24 p_api_version IN NUMBER,
25 p_init_msg_list IN VARCHAR2,
26 p_contract_id IN NUMBER ,
27 p_asset_id IN NUMBER ,
28 p_kle_id IN VARCHAR2 ,
29 p_salvage_writedown_yn IN VARCHAR2
30 ) IS
31
32
33 l_return_status VARCHAR2(1);
34 l_msg_count NUMBER;
35 l_msg_data VARCHAR2(2000);
36 l_api_name CONSTANT VARCHAR2(30) := 'process_transactions_wrap';
37 l_total_count NUMBER;
38 l_processed_count NUMBER;
39 l_error_count NUMBER;
40 lx_error_rec OKL_API.error_rec_type;
41 l_msg_idx INTEGER := FND_MSG_PUB.G_FIRST;
42
43 BEGIN
44
45 process_transactions(
46 p_api_version => p_api_version,
47 p_init_msg_list => p_init_msg_list ,
48 x_return_status => l_return_status,
49 x_msg_count => l_msg_count,
50 x_msg_data => l_msg_data,
51 p_contract_id => p_contract_id ,
52 p_asset_id => p_asset_id,
53 p_kle_id => TO_NUMBER(p_kle_id),
54 p_salvage_writedown_yn => p_salvage_writedown_yn,
55 x_total_count => l_total_count,
56 x_processed_count => l_processed_count,
57 x_error_count => l_error_count);
58
59
60 -- Add couple of blank lines
61 fnd_file.new_line(fnd_file.log,2);
62 fnd_file.new_line(fnd_file.output,2);
63
64 -- Get the messages in the log
65 LOOP
66
67 fnd_msg_pub.get(
68 p_msg_index => l_msg_idx,
69 p_encoded => FND_API.G_FALSE,
70 p_data => lx_error_rec.msg_data,
71 p_msg_index_out => lx_error_rec.msg_count);
72
73 IF (lx_error_rec.msg_count IS NOT NULL) THEN
74
75 fnd_file.put_line(fnd_file.log, lx_error_rec.msg_data);
76 fnd_file.put_line(fnd_file.output, lx_error_rec.msg_data);
77
78 END IF;
79
80 EXIT WHEN ((lx_error_rec.msg_count = FND_MSG_PUB.COUNT_MSG)
81 OR (lx_error_rec.msg_count IS NULL));
82
83 l_msg_idx := FND_MSG_PUB.G_NEXT;
84 END LOOP;
85
86
87 fnd_file.new_line(fnd_file.log,2);
88 fnd_file.new_line(fnd_file.output,2);
89
90 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
91 fnd_file.put_line(fnd_file.log, 'FA ADJUSTMENTS Failed, None of the transactions got processed');
92 fnd_file.put_line(fnd_file.output, 'FA ADJUSTMENTS Failed, None of the transactions got processed');
93 END IF;
94
95 IF l_total_count = 0 THEN
96 fnd_file.put_line(fnd_file.log, 'There were no Asset Management transactions to process.');
97 fnd_file.put_line(fnd_file.output,'There were no Asset Management transactions to process.');
98 ELSE
99
100 fnd_file.put_line(fnd_file.log, 'Total Transactions : '||l_total_count);
101 fnd_file.put_line(fnd_file.log, 'Transactions Processed Successfully : '||l_processed_count);
102 fnd_file.put_line(fnd_file.log, 'Transactions Failed : '||l_error_count);
103
104 fnd_file.put_line(fnd_file.output, 'Total Transactions : '||l_total_count);
105 fnd_file.put_line(fnd_file.output, 'Transactions Processed Successfully : '||l_processed_count);
106 fnd_file.put_line(fnd_file.output, 'Transactions Failed : '||l_error_count);
107
108 END IF;
109
110
111 EXCEPTION
112 WHEN OTHERS THEN
113 -- unexpected error
114 -- SECHAWLA 16-JAN-03 Bug # 2754280 : Changed the app name from OKL to OKC
115 OKL_API.set_message(p_app_name => 'OKC',
116 p_msg_name => g_unexpected_error,
117 p_token1 => g_sqlcode_token,
118 p_token1_value => sqlcode,
119 p_token2 => g_sqlerrm_token,
120 p_token2_value => sqlerrm);
121
122 END process_transactions_wrap;
123
124 -- Start of comments
125 --
126 -- Procedure Name : process_transactions
127 -- Description : This procedure is used to process amortization(AMT), Evergreen(AED) and
128 -- Salvage Value writedown(FSC) transactions. By default this procedure
129 -- will process only AMT and AED transactions. However, if the
130 -- parameter p_salvage_writedown_yn is set to 'Y', it will process
131 -- salvage value writedown transactions as well in Fixed Assets
132 -- Business Rules :
133 -- Parameters : p_contract_id - contract id
134 -- p_asset_id - aseet_id
135 -- p_kle_id - line id
136 -- p_salvage_writedown_yn - flag indicating whether to process salvage valye transactions
137 -- x_transaction_status - reflects the overall status of all transactions
138 -- Version : 1.0
139 -- History : SECHAWLA 09-DEC-02 Bug # 2701440
140 -- Added code to adjust the asset in all the tax books that the asset belongs to
141 -- SECHAWLA 06-MAY-04 Bug # 3578894
142 -- Process the depreciate flag updates independent of all other updates (dep method,
143 -- life, cost, sv) This is required because FA does not allow updating depreciation flag
144 -- with any other attribute. Details in bug 3501172
145 -- Re-wrote this procedure as we have a new logic in place to process amortization transactions
146 -- SECHAWLA 10-MAY-04 Bug # 3578894
147 -- for Asset depreciation transactions, check the depreciate flag before updating it
148 -- SECHAWLA 17-DEC-04 Bug # 4028371 update asset line transaction with FA trx date
149 -- rmunjulu Bug 4150696 Added code to set transaction_date_entered with Date Transaction Occurred
150 -- before calling the FA_ADJUSTMENT_PUB API
151 -- RBRUNO 23-Aug-07 Bug # 6360770 Added code to set
152 -- contract_id number when calling FA Adjustment API
153 -- sechawla 14-dec-07 6690811 Removed code to disassociate the contract_id from FA asset
154 --
155 -- End of comments
156 PROCEDURE process_transactions(
157 p_api_version IN NUMBER,
158 p_init_msg_list IN VARCHAR2,
159 x_return_status OUT NOCOPY VARCHAR2,
160 x_msg_count OUT NOCOPY NUMBER,
161 x_msg_data OUT NOCOPY VARCHAR2,
162 p_contract_id IN NUMBER ,
163 p_asset_id IN NUMBER ,
164 p_kle_id IN NUMBER ,
165 p_salvage_writedown_yn IN VARCHAR2 ,
166 x_total_count OUT NOCOPY NUMBER,
167 x_processed_count OUT NOCOPY NUMBER,
168 x_error_count OUT NOCOPY NUMBER ) IS
169
170 -- SECHAWLA Bug # 2701440 : new declarations
171 TYPE books_tbl_type IS TABLE OF VARCHAR2(15) INDEX BY BINARY_INTEGER;
172 l_books_tbl books_tbl_type;
173 l_empty_books_tbl books_tbl_type;
174 l_book_type_code VARCHAR2(15);
175 i NUMBER;
176 l_asset_books_error EXCEPTION;
177
178
179 SUBTYPE thpv_rec_type IS okl_trx_assets_pub.thpv_rec_type;
180
181 lp_thpv_rec thpv_rec_type;
182 lx_thpv_rec thpv_rec_type;
183 l_total_count NUMBER;
184 l_sysdate DATE;
185
186 --SECHAWLA 17-DEC-04 Bug # 4028371
187 SUBTYPE tlpv_rec_type IS okl_txl_assets_pub.tlpv_rec_type;
188
189 lp_tlpv_rec tlpv_rec_type;
190 lx_tlpv_rec tlpv_rec_type;
191
192
193 -- SECHAWLA 06-MAY-04 3578894 : Created a new cursor to get all the assets that need to be processed
194 -- This cursor picks up the amortization ,evergreen depreciation and salvage value writedown transactions to be
195 -- processed in Fixed Assets. AMT - amortization, AED - evergreen, FSC - Salvage value writedown.
196 CURSOR l_distinctasset_csr(cp_date IN DATE, p_org_id NUMBER) IS
197 SELECT DISTINCT l.asset_number, l.kle_id, b.authoring_org_id
198 FROM OKL_TRX_ASSETS h, OKL_TXL_ASSETS_B l -- SECHAWLA 19-FEB-04 3439647 : Use base tables instead of views
199 ,okc_k_headers_all_b b /* Bug 6459571 */
200 WHERE h.id = l.tas_id
201 AND h.tsu_code in('ENTERED','ERROR') -- SMODUGA 07-FEB-05 3578894 : process trx in entered and error status only
202 AND h.date_trans_occurred <= cp_date
203 -- SECHAWLA 06-MAY-04 3578894 : Removing 'AED' as evergreen transactions are not created
204 AND ( (p_salvage_writedown_yn = 'N' AND h.tas_type in ('AMT','AUD','AUS')) OR
205 (p_salvage_writedown_yn = 'Y' AND h.tas_type in ('AMT','AUD','AUS','FSC'))
206 )-- SECHAWLA 06-MAY-04 3578894 : Added new tas types 'AUD','AUS' to above 2 conditions
207 AND (
208 -- all 3 parameter values are provided
209 ( p_contract_id IS NOT NULL AND p_asset_id IS NOT NULL AND p_kle_id IS NOT NULL AND
210 l.dnz_khr_id = p_contract_id AND l.dnz_asset_id= p_asset_id AND l.kle_id = p_kle_id)
211 OR
212 -- none of the parameter values are provided
213 ( p_contract_id IS NULL AND p_asset_id IS NULL AND p_kle_id IS NULL)
214 OR
215 -- contract Id is provided, asset_id and kle_id not provided
216 (p_contract_id IS NOT NULL AND l.dnz_khr_id = p_contract_id AND p_asset_id IS NULL AND p_kle_id IS NULL)
217 OR
218 -- contract Id and asset Id are provided, kle_id not provided
219 (p_contract_id IS NOT NULL AND l.dnz_khr_id = p_contract_id AND p_asset_id IS NOT NULL AND l.dnz_asset_id= p_asset_id AND p_kle_id IS NULL)
220 OR
221 -- contarct Id and kle_id are provided, asset Id not provided
222 (p_contract_id IS NOT NULL AND l.dnz_khr_id = p_contract_id AND p_asset_id IS NULL AND p_kle_id IS NOT NULL AND l.kle_id = p_kle_id)
223 OR
224 -- asset Id is provided, cntract ID and kle Id not provided
225 (p_contract_id IS NULL AND p_asset_id IS NOT NULL AND l.dnz_asset_id= p_asset_id AND p_kle_id IS NULL)
226 OR
227 -- asset Id and kle Id are provided, contract Id not provided
228 (p_contract_id IS NULL AND p_asset_id IS NOT NULL AND l.dnz_asset_id= p_asset_id AND p_kle_id IS NOT NULL AND l.kle_id = p_kle_id)
229 OR
230 -- kle Id is provided, contarct Id and asset Id not provided
231 (p_contract_id IS NULL AND p_asset_id IS NULL AND p_kle_id IS NOT NULL AND l.kle_id = p_kle_id)
232 )
233 AND l.dnz_khr_id = b.id(+)
234 AND NVL(b.AUTHORING_ORG_ID, P_ORG_ID ) = P_ORG_ID
235 ORDER BY asset_number;
236
237 -- SECHAWLA 06-MAY-04 3578894 : Modified cursor definition to include tax transactions
238 -- Separated SVW transactions from this cursor
239 CURSOR l_assettrx_csr(cp_date IN DATE, cp_asset_number IN VARCHAR2) IS
240 SELECT h.id, l.id line_id, -- 17-DEC-04 SECHAWLA 4028371 : Added l.id
241 h.tas_type, FL.MEANING TAS_TYPE_MEANING, h.date_trans_occurred, l.depreciate_yn, l.dnz_asset_id,
242 decode(d.tax_book,NULL,l.CORPORATE_BOOK,d.tax_book ) ASSET_BOOK, fbc.book_class ASSET_BOOK_TYPE,
243 l.in_service_date, l.deprn_method, l.life_in_months, l.deprn_rate, --SECHAWLA 28-MAY-04 3645574 : Added deprn_rate
244 --nvl(l.depreciation_cost,0) depreciation_cost ,
245 l.depreciation_cost,
246 l.asset_number, nvl(l.salvage_value,0) salvage_value, nvl(l.old_salvage_value,0) old_salvage_value, l.kle_id,
247 to_char(h.trans_number) trans_number -- SECHAWLA 17-DEC-04 4028371 : added for stamping
248 --,l.currency_code func_currency_code -- SECHAWLA 29-JUL-05 4456005 : added
249 --,l.DNZ_KHR_ID -- SECHAWLA 29-JUL-05 4456005 : added
250 -- SGORANTL 22-MAR-06 5097643: changes made by kbbhavsa for bug 4717511 has been reversed
251 ,h.try_id --akrangan added for sla populate sources cr
252 FROM OKL_TRX_ASSETS h, OKL_TXL_ASSETS_B l, OKL_TXD_ASSETS_B d, fa_book_controls fbc, FND_LOOKUPS fl -- SECHAWLA 19-FEB-04 3439647 : Use base tables instead of views
253 WHERE h.id = l.tas_id
254 AND l.id = d.tal_id(+)
255 AND FL.LOOKUP_CODE = h.TAS_TYPE AND FL.LOOKUP_TYPE = 'OKL_TRANS_HEADER_TYPE'
256 AND decode(d.tax_book,NULL, l.CORPORATE_BOOK,d.tax_book ) = fbc.book_type_code
257 AND h.tsu_code IN ('ENTERED','ERROR') -- SMODUGA 07-FEB-05 4144322 : process trx in entered and error status only
258 AND h.date_trans_occurred <= cp_date
259 -- SECHAWLA 06-MAY-04 3578894 : Separate SVW transactions from Amortization transactions
260 AND h.tas_type in ('AMT','AUD','AUS')
261 -- SECHAWLA 06-MAY-04 3578894 : Added new tas types 'AUD','AUS' to above 2 conditions
262 AND l.asset_number = cp_asset_number
263 ORDER BY asset_book_type , date_trans_occurred, tas_type;
264
265
266 -- SECHAWLA 06-MAY-04 3578894 : Created separate cursor to process SVW transactions
267 -- As of now, SVW transactions are created only in corporate book.
268 CURSOR l_assetsvtrx_csr(cp_date IN DATE, cp_asset_number IN VARCHAR2) IS
269 SELECT h.id, h.tas_type, FL.MEANING TAS_TYPE_MEANING, h.date_trans_occurred, l.depreciate_yn, l.dnz_asset_id,
270 decode(d.tax_book,NULL,l.CORPORATE_BOOK,d.tax_book ) ASSET_BOOK, fbc.book_class ASSET_BOOK_TYPE,
271 l.in_service_date, l.deprn_method, l.life_in_months, l.deprn_rate, --SECHAWLA 28-MAY-04 3645574 : Added deprn_rate
272 --nvl(l.depreciation_cost,0) depreciation_cost ,
273 l.depreciation_cost,
274 l.asset_number, nvl(l.salvage_value,0) salvage_value, nvl(l.old_salvage_value,0) old_salvage_value, l.kle_id
275 --,l.currency_code func_currency_code -- SECHAWLA 29-JUL-05 4456005 : added
276 --,l.DNZ_KHR_ID -- SECHAWLA 29-JUL-05 4456005 : added
277 -- SGORANTL 22-MAR-06 5097643: changes made by kbbhavsa for bug 4717511 has been reversed
278 ,h.try_id --akrangan added for sla populate sources cr
279 ,l.id line_id --akrangan added for sla populate sources cr
280 FROM OKL_TRX_ASSETS h, OKL_TXL_ASSETS_B l, OKL_TXD_ASSETS_B d, fa_book_controls fbc, FND_LOOKUPS fl -- SECHAWLA 19-FEB-04 3439647 : Use base tables instead of views
281 WHERE h.id = l.tas_id
282 AND l.id = d.tal_id(+)
283 AND FL.LOOKUP_CODE = h.TAS_TYPE AND FL.LOOKUP_TYPE = 'OKL_TRANS_HEADER_TYPE'
284 AND decode(d.tax_book,NULL, l.CORPORATE_BOOK,d.tax_book ) = fbc.book_type_code
285 AND h.tsu_code IN ('ENTERED','ERROR') -- SMODUGA 07-FEB-05 4144322 : process trx in entered and error status only
286 AND h.date_trans_occurred <= cp_date
287 AND h.tas_type = 'FSC'
288 AND l.asset_number = cp_asset_number
289 ORDER BY asset_book_type;
290
291
292
293 -- Get the cost and sv for an asset
294 -- SECHAWLA 10-MAY-04 3578894 : added depreciate flag, deprn_method_code, fb.life_in_months to this cursor
295 CURSOR l_facostsv_csr(cp_asset_number IN VARCHAR2, cp_book_type_code IN VARCHAR2) IS
296 --SECHAWLA 28-MAY-04 3645574 : Added adjusted_rate
297 SELECT fb.cost, fb.salvage_value, fb.depreciate_flag, fb.deprn_method_code, fb.life_in_months, fb.adjusted_rate
298 FROM fa_books fb, fa_additions_b fab
299 WHERE fb.transaction_header_id_out is null
300 AND fb.book_type_code = cp_book_type_code
301 AND fab.asset_id = fb.asset_id
302 AND fab.asset_number = cp_asset_number;
303
304
305 -- This cursor is used to check if an accepted termination quote exists for an asset line.
306 CURSOR l_quotes_csr(p_kle_id IN NUMBER) IS
307 SELECT l.asset_number
308 FROM okl_trx_quotes_b qh, okl_txl_quote_lines_b ql, okx_asset_lines_v l
309 WHERE qh.id = ql.qte_id
310 AND qh.qst_code = 'ACCEPTED'
311 AND ql.qlt_code = 'AMCFIA'
312 AND ql.kle_id = l.parent_line_id
313 AND ql.kle_id = p_kle_id;
314
315
316 -- SECHAWLA 06-MAY-04 3578894 : Do not need this cursor for off lease trx , as tax trx are now created separately
317 -- SECHAWLA Bug # 2701440 : added a new cursor
318 -- This cursor is still being used for SVW transactions
319 -- This cursor is used to find all the tax books that an asset belongs to
320 /* SECHAWLA 28-DEC-05 4374620 : do not process SVW transactions in tax books
321 CURSOR l_fabookcntrl_csr(p_asset_id IN NUMBER) IS
322 SELECT a.book_type_code
323 FROM fa_books a, fa_book_controls b
324 WHERE a.asset_id = p_asset_id
325 AND a.book_type_code = b.book_type_code
326 AND b.book_class = 'TAX'
327 AND a.date_ineffective IS NULL
328 AND a.transaction_header_id_out IS NULL;
329 */
330
331
332 -- SECHAWLA 19-FEB-04 3439647 : New Declarations
333 -- get the deal type from the contract
334 CURSOR l_dealtype_csr(p_financial_asset_id IN NUMBER) IS
335 SELECT lkhr.id, lkhr.deal_type, khr.contract_number
336 FROM okl_k_headers lkhr, okc_k_lines_b cle, okc_k_headers_b khr
337 WHERE khr.id = cle.chr_id
338 AND lkhr.id = khr.id
339 AND cle.id = p_financial_asset_id;
340
341 -- SECHAWLA 14-FEB-05 3950089 : get the salvage type
342 CURSOR l_booksalvagetype_csr(cp_asset_id IN NUMBER, cp_booktype_code IN VARCHAR2) IS
343 SELECT salvage_type
344 FROM fa_books
345 WHERE asset_id = cp_asset_id
346 AND book_type_code = cp_booktype_code
347 AND transaction_header_id_out IS NULL
348 AND date_ineffective IS NULL;
349
350 l_salvage_type VARCHAR2(30);
351 -- SECHAWLA 14-FEB-05 3950089 : end new declarations
352
353
354 l_deal_type VARCHAR2(30);
355 l_chr_id NUMBER;
356 l_contract_number VARCHAR2(120);
357
358 l_rulv_rec okl_rule_pub.rulv_rec_type;
359
360 -- SECHAWLA 19-FEB-04 3439647 : End New Declarations
361
362
363 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
364 l_api_name CONSTANT VARCHAR2(30) := 'process_transactions';
365
366 l_trans_rec FA_API_TYPES.trans_rec_type;
367
368 -- SECHAWLA 06-MAY-04 3578894
369 l_trans_empty_rec FA_API_TYPES.trans_rec_type;
370
371 l_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
372
373 -- SECHAWLA 06-MAY-04 3578894
374 l_asset_hdr_empty_rec FA_API_TYPES.asset_hdr_rec_type;
375
376 l_asset_fin_rec_adj FA_API_TYPES.asset_fin_rec_type;
377 -- SECHAWLA 06-MAY-04 3578894
378 l_asset_fin_rec_empty_adj FA_API_TYPES.asset_fin_rec_type;
379
380 l_asset_fin_rec_new FA_API_TYPES.asset_fin_rec_type;
381 l_asset_fin_mrc_tbl_new FA_API_TYPES.asset_fin_tbl_type;
382
383 l_inv_trans_rec FA_API_TYPES.inv_trans_rec_type;
384 l_inv_tbl FA_API_TYPES.inv_tbl_type;
385 l_asset_deprn_rec_adj FA_API_TYPES.asset_deprn_rec_type;
386 l_asset_deprn_rec_new FA_API_TYPES.asset_deprn_rec_type;
387 l_asset_deprn_mrc_tbl_new FA_API_TYPES.asset_deprn_tbl_type;
388 l_group_reclass_options_rec FA_API_TYPES.group_reclass_options_rec_type;
389 l_method_code fa_methods.method_code%TYPE;
390 l_fa_cost NUMBER;
391 l_fa_salvage_value NUMBER;
392 l_delta_cost NUMBER;
393 l_delta_salvage_value NUMBER;
394 l_api_version CONSTANT NUMBER := 1;
395 l_transaction_status VARCHAR2(1);
396
397 l_process_count NUMBER;
398 l_asset_number VARCHAR2(15);
399 l_trx_count NUMBER;
400
401 -- SGORANTL 22-MAR-06 5097643: changes made by kbbhavsa for bug 4717511 has been reversed
402 l_dep_cost NUMBER;
403 l_sal_value NUMBER;
404
405
406 TYPE trxassets_rec_type IS RECORD( id NUMBER DEFAULT OKL_API.G_MISS_NUM,
407 asset_number VARCHAR2(15) DEFAULT OKL_API.G_MISS_CHAR,
408 asset_book VARCHAR2(70) DEFAULT OKL_API.G_MISS_CHAR,
409 tas_type_meaning VARCHAR2(80) DEFAULT OKL_API.G_MISS_CHAR
410 );
411
412 TYPE trxassets_tbl_type IS TABLE OF trxassets_rec_type INDEX BY BINARY_INTEGER;
413
414 l_trxassets_tbl trxassets_tbl_type;
415 l_trxassets_empty_tbl trxassets_tbl_type;
416 l_pos NUMBER;
417
418 l_update_status VARCHAR2(1);
419 j NUMBER;
420
421 -- SECHAWLA 10-MAY-04 3578894 : new declarations
422 l_fa_depreciate_flag VARCHAR2(10);
423 l_fa_deprn_method_code VARCHAR2(12);
424 l_fa_life_in_months NUMBER;
425
426 --SECHAWLA 28-MAY-04 3645574 : new declaration
427 l_fa_deprn_rate NUMBER;
428
429 -- 17-DEC-04 SECHAWLA 4028371 : new declarations
430 l_fa_trx_date DATE;
431 --akrangan sla populate sources cr start
432 l_fxhv_rec okl_fxh_pvt.fxhv_rec_type;
433 l_fxlv_rec okl_fxl_pvt.fxlv_rec_type;
434 --akrangan sla populate sources cr end
435
436 -- Bug 6459571
437 l_org_id NUMBER;
438 BEGIN
439
440 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
441 G_PKG_NAME,
442 p_init_msg_list,
443 l_api_version,
444 p_api_version,
445 '_PVT',
446 x_return_status);
447
448 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
449 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
450 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
451 RAISE OKC_API.G_EXCEPTION_ERROR;
452 END IF;
453
454
455
456 SELECT SYSDATE INTO l_sysdate FROM DUAL;
457 -- transaction information
458 -- SECHAWLA 06-MAY-04 3578894 : Moved the following statement inside the loop
459 -- l_trans_rec.transaction_subtype := G_TRANS_SUBTYPE;
460
461
462 l_total_count := 0;
463 l_process_count := 0;
464
465 -- Bug 6459571: Start
466 -- Fetch the Operating Unit for the Concurrent Program
467 l_org_id := mo_global.get_current_org_id();
468 -- Bug 6459571: End
469 FOR l_distinctasset_rec IN l_distinctasset_csr(l_sysdate, l_org_id) LOOP
470 l_transaction_status := OKC_API.G_RET_STS_SUCCESS;
471 l_trx_count := 0;
472 l_trxassets_tbl := l_trxassets_empty_tbl;
473 l_pos := 0;
474
475
476 BEGIN -- process amortization transactions begin
477
478 SAVEPOINT asset_updates;
479
480 -- loop thru all the transactions in the OKL tables and process them in FA
481 FOR l_assettrx_rec IN l_assettrx_csr(l_sysdate, l_distinctasset_rec.asset_number) LOOP
482
483 --l_transaction_status := OKC_API.G_RET_STS_SUCCESS;
484 l_total_count := l_total_count + 1;
485 l_trx_count := l_trx_count + 1;
486
487 /*-- initialize all the structures to null values
488 l_asset_fin_rec_adj.depreciate_flag := NULL;
489 l_asset_fin_rec_adj.deprn_method_code := NULL;
490 l_asset_fin_rec_adj.life_in_months := NULL;
491 l_asset_fin_rec_adj.cost := NULL;
492 l_asset_fin_rec_adj.salvage_value := NULL;
493 */ -- -- SECHAWLA 06-MAY-04 3578894
494 l_asset_fin_rec_adj := l_asset_fin_rec_empty_adj ;
495
496
497 -- SECHAWLA 06-MAY-04 3578894 : Initialize the amortization start date before every call to FA API.
498 -- This is required to avoid YTD and Accumulated depreciation calculation as per clarification
499 -- received from FA, bug 3559993
500 --l_trans_rec.amortization_start_date := NULL;
501 l_trans_rec := l_trans_empty_rec;
502 -- SECHAWLA 06-MAY-04 3578894 : Moved the following statement inside the loop
503 l_trans_rec.transaction_subtype := G_TRANS_SUBTYPE;
504
505
506 -- SECHAWLA 17-DEC-04 4028371 : stamp FA trx with OKL trx type
507 l_trans_rec.calling_interface := 'OKL:'||'Off Lease Amort:'||l_assettrx_rec.tas_type;
508 l_trans_rec.transaction_name := substr(l_assettrx_rec.TRANS_NUMBER,1,30);
509
510
511 -- SECHWLA 06-MAY-04 3578894 : Initialize all record types
512 l_asset_hdr_rec := l_asset_hdr_empty_rec;
513
514 -- SECHAWLA 17-DEC-04 4028371 : initailize l_fa_trx_date
515 l_fa_trx_date := NULL;
516
517
518 -- get the deal type from the contract
519 OPEN l_dealtype_csr(l_assettrx_rec.kle_id);
520 FETCH l_dealtype_csr INTO l_chr_id, l_deal_type, l_contract_number;
521 IF l_dealtype_csr%NOTFOUND OR l_deal_type IS NULL OR l_deal_type = OKL_API.G_MISS_CHAR THEN
522 -- Can not find deal type for asset ASSET_NUMBER.
523 OKC_API.set_message( p_app_name => 'OKL',
524 p_msg_name => 'OKL_AM_DEAL_TYPE_NOT_FOUND',
525 p_token1 => 'ASSET_NUMBER',
526 p_token1_value => l_assettrx_rec.asset_number);
527
528 l_transaction_status := OKC_API.G_RET_STS_ERROR;
529 ELSE
530
531 IF l_assettrx_rec.asset_book IS NULL OR l_assettrx_rec.asset_book = OKL_API.G_MISS_CHAR THEN
532 --Can not process TRX_TYPE transaction in Fixed Assets for asset ASSET_NUMBER as FIELD is missing.
533 OKC_API.set_message( p_app_name => 'OKL',
534 p_msg_name => 'OKL_AM_NO_ASSET_BOOK',
535 p_token1 => 'TRX_TYPE',
536 p_token1_value => l_assettrx_rec.TAS_TYPE_MEANING,
537 p_token2 => 'ASSET_NUMBER',
538 p_token2_value => l_assettrx_rec.asset_number,
539 p_token3 => 'FIELD',
540 p_token3_value => 'Asset Book');
541
542 l_transaction_status := OKC_API.G_RET_STS_ERROR;
543 -- SECHAWLA 06-MAY-04 3578894 Added new tas types 'AUD','AUS' to following condition
544 ELSIF (l_assettrx_rec.tas_type IN ('AMT','AUD','AUS')) AND (l_assettrx_rec.depreciate_yn IS NULL OR
545 l_assettrx_rec.depreciate_yn = OKL_API.G_MISS_CHAR) THEN
546 --Can not process TRX_TYPE transaction in Fixed Assets for asset ASSET_NUMBER and book ASSET_BOOK as depreciate (Y/N) flag is missing.
547 OKC_API.set_message( p_app_name => 'OKL',
548 p_msg_name => 'OKL_AM_DATA_REQUIRED',
549 p_token1 => 'TRX_TYPE',
550 p_token1_value => l_assettrx_rec.TAS_TYPE_MEANING,
551 p_token2 => 'ASSET_NUMBER',
552 p_token2_value => l_assettrx_rec.asset_number,
553 p_token3 => 'ASSET_BOOK',
554 p_token3_value => l_assettrx_rec.asset_book,
555 p_token4 => 'FIELD',
556 p_token4_value => 'Depreciate (Y/N) flag');
557
558 l_transaction_status := OKC_API.G_RET_STS_ERROR;
559 ELSIF l_assettrx_rec.dnz_asset_id IS NULL OR l_assettrx_rec.dnz_asset_id = OKL_API.G_MISS_NUM THEN
560 --Can not process TRX_TYPE transaction in Fixed Assets for asset ASSET_NUMBER and book ASSET_BOOK as Asset ID
561 -- is missing.
562 OKC_API.set_message( p_app_name => 'OKL',
563 p_msg_name => 'OKL_AM_DATA_REQUIRED',
564 p_token1 => 'TRX_TYPE',
565 p_token1_value => l_assettrx_rec.TAS_TYPE_MEANING,
566 p_token2 => 'ASSET_NUMBER',
567 p_token2_value => l_assettrx_rec.asset_number,
568 p_token3 => 'ASSET_BOOK',
569 p_token3_value => l_assettrx_rec.asset_book,
570 p_token4 => 'FIELD',
571 p_token4_value => 'Asset ID');
572
573 l_transaction_status := OKC_API.G_RET_STS_ERROR;
574
575 ELSE
576
577 OPEN l_facostsv_csr(l_assettrx_rec.asset_number, l_assettrx_rec.asset_book);
578 -- SECHAWLA 10-MAY-04 3578894 : added l_depreciate_flag, l_deprn_method_code, l_life_in_months
579 FETCH l_facostsv_csr INTO l_fa_cost, l_fa_salvage_value, l_fa_depreciate_flag, l_fa_deprn_method_code,
580 l_fa_life_in_months, l_fa_deprn_rate; --SECHAWLA 28-MAY-04 3645574 : Added deprn_rate
581
582 IF (l_facostsv_csr%NOTFOUND) THEN
583 --This combination of Asset ASSET and Book BOOK is invalid.
584
585 OKL_API.set_message(
586 p_app_name => 'OKL',
587 p_msg_name => 'OKL_AM_INVALID_ASSET_BOOK',
588 p_token1 => 'ASSET_NUMBER',
589 p_token1_value => l_assettrx_rec.asset_number,
590 p_token2 => 'BOOK',
591 p_token2_value => l_assettrx_rec.asset_book);
592
593 l_transaction_status := OKL_API.G_RET_STS_ERROR;
594
595 ELSIF (l_fa_life_in_months IS NULL AND l_fa_deprn_rate IS NULL) THEN
596
597 --Can not process TRX_TYPE transaction in Fixed Assets for asset ASSET_NUMBER and book ASSET_BOOK as Cost / salvage Value
598 -- is missing.
599 OKC_API.set_message( p_app_name => 'OKL',
600 p_msg_name => 'OKL_AM_DATA_REQUIRED',
601 p_token1 => 'TRX_TYPE',
602 p_token1_value => l_assettrx_rec.TAS_TYPE_MEANING,
603 p_token2 => 'ASSET_NUMBER',
604 p_token2_value => l_assettrx_rec.asset_number,
605 p_token3 => 'ASSET_BOOK',
606 p_token3_value => l_assettrx_rec.asset_book,
607 p_token4 => 'FIELD',
608 p_token4_value => 'Life In Months / Adjusted Rate');
609
610 l_transaction_status := OKL_API.G_RET_STS_ERROR;
611
612
613 -- SECHAWLA 06-MAY-04 3578894 : Added this scenario to just stop the depreciation
614 ELSIF l_assettrx_rec.tas_type = 'AUD' AND l_assettrx_rec.depreciate_yn = 'N' THEN
615
616 IF l_fa_depreciate_flag <> 'NO' THEN -- SECHAWLA 10-MAY-04 3578894 : FA update fails
617 -- if we try to update the flag to the same value
618 -- Operating Lease hold period, DF Lease hold period : 1st trx - stop depreciation
619 -- asset header information
620 l_asset_hdr_rec.asset_id := l_assettrx_rec.dnz_asset_id;
621
622 -- financial information
623 l_asset_fin_rec_adj.depreciate_flag := 'NO';
624 -- ? need to add another validation to make sure that depreciation has already been run for this asset
625 -- ? for the current open period. Which means we can not stop depreciation for an asset in the middle of the
626 -- ? period.
627
628
629
630 l_asset_hdr_rec.book_type_code := l_assettrx_rec.asset_book;
631
632 -- rmunjulu Bug 4150696 Added code to set transaction_date_entered with Date Transaction Occurred
633 l_trans_rec.transaction_date_entered := l_assettrx_rec.date_trans_occurred;
634
635 -- rbruno Bug 6360770 Added code to set contract_id
636 -- sechawla 14-dec-07 6690811 : Removed code to set contract id
637 --l_asset_fin_rec_adj.contract_id := FND_API.G_MISS_NUM;
638
639 fa_adjustment_pub.do_adjustment(
640 p_api_version => p_api_version,
641 p_init_msg_list => OKC_API.G_FALSE,
642 p_commit => FND_API.G_FALSE,
643 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
644 p_calling_fn => NULL,
645 x_return_status => l_return_status,
646 x_msg_count => x_msg_count,
647 x_msg_data => x_msg_data,
648 px_trans_rec => l_trans_rec,
649 px_asset_hdr_rec => l_asset_hdr_rec,
650 p_asset_fin_rec_adj => l_asset_fin_rec_adj,
651 x_asset_fin_rec_new => l_asset_fin_rec_new,
652 x_asset_fin_mrc_tbl_new => l_asset_fin_mrc_tbl_new,
653 px_inv_trans_rec => l_inv_trans_rec,
654 px_inv_tbl => l_inv_tbl,
655 p_asset_deprn_rec_adj => l_asset_deprn_rec_adj,
656 x_asset_deprn_rec_new => l_asset_deprn_rec_new,
657 x_asset_deprn_mrc_tbl_new => l_asset_deprn_mrc_tbl_new,
658 p_group_reclass_options_rec => l_group_reclass_options_rec);
659
660 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
661
662 -- Error processing TRX_TYPE transaction in Fixed Assets for asset ASSET_NUMBER in book BOOK.
663 OKC_API.set_message( p_app_name => 'OKL',
664 p_msg_name => 'OKL_AM_AMT_TRANS_FAILED',
665 p_token1 => 'TRX_TYPE',
666 p_token1_value => l_assettrx_rec.TAS_TYPE_MEANING,
667 p_token2 => 'ASSET_NUMBER',
668 p_token2_value => l_assettrx_rec.asset_number,
669 p_token3 => 'BOOK', -- SECHAWLA Bug # 2701440 : Added token2
670 p_token3_value => l_assettrx_rec.asset_book); -- SECHAWLA Bug # 2701440 : Added token2 value
671 l_transaction_status := l_return_status;
672 ELSE -- 17-DEC-04 SECHAWLA 4028371 added else section
673 l_fa_trx_date := l_trans_rec.transaction_date_entered;
674 END IF;
675 --akrangan populate sources cr changes start
676 -- header record
677 l_fxhv_rec.source_id := l_assettrx_rec.id;
678 l_fxhv_rec.source_table := 'OKL_TRX_ASSETS';
679 l_fxhv_rec.khr_id := l_chr_id;
680 l_fxhv_rec.try_id := l_assettrx_rec.try_id;
681 --line record
682 l_fxlv_rec.source_id := l_assettrx_rec.line_id;
683 l_fxlv_rec.source_table := 'OKL_TXL_ASSETS_B';
684 l_fxlv_rec.asset_id := l_assettrx_rec.dnz_asset_id;
685 l_fxlv_rec.kle_id := l_assettrx_rec.kle_id;
686 l_fxlv_rec.fa_transaction_id := l_trans_rec.transaction_header_id;
687 l_fxlv_rec.asset_book_type_name := l_assettrx_rec.asset_book;
688 --call api
689 okl_sla_acc_sources_pvt.populate_sources(p_api_version => p_api_version,
690 p_init_msg_list => okc_api.g_false,
691 p_fxhv_rec => l_fxhv_rec,
692 p_fxlv_rec => l_fxlv_rec,
693 x_return_status => x_return_status,
694 x_msg_count => x_msg_count,
695 x_msg_data => x_msg_data);
696
697 IF (x_return_status = okc_api.g_ret_sts_unexp_error)
698 THEN
699 RAISE okl_api.g_exception_unexpected_error;
700 ELSIF (x_return_status = okc_api.g_ret_sts_error)
701 THEN
702 RAISE okl_api.g_exception_error;
703 END IF;
704 --akrangan populate sources cr end
705 ELSE -- 17-DEC-04 SECHAWLA 4028371 added else section
706 OKL_ACCOUNTING_UTIL.get_fa_trx_date(
707 p_book_type_code => l_assettrx_rec.asset_book,
708 x_return_status => l_return_status,
709 x_fa_trx_date => l_fa_trx_date);
710
711 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
712 l_transaction_status := l_return_status;
713 END IF;
714
715 END IF;
716
717
718 -- SECHAWLA 06-MAY-04 3578894 : Changed the tas_type in the condition to AUS to handle
719 -- DF/Sales lease, hold period scenario, 2nd trx - to only updaet the asset cost
720
721 ELSIF l_assettrx_rec.tas_type = 'AUS' AND l_assettrx_rec.depreciate_yn = 'N' THEN
722
723 -- DF/Sales lease, hold period, 2nd trx - update asset cost
724 -- asset header information
725
726
727 -- SECHAWLA 07-SEP-05 4596776 : The following check should use dep cost in functional currency
728 -- So first convert the amount and then compare
729 /*IF l_fa_cost <> l_assettrx_rec.depreciation_cost THEN -- SECHAWLA 10-MAY-04 3578894 : FA update fails
730 -- if we try to update the cost with the same value
731 */
732 -- SECHAWLA 19-FEB-04 3439647 : Write asset cost up to NIV when we stop the depreciation, for DF/Sales lease
733 IF l_deal_type IN ('LEASEDF','LEASEST') THEN
734 -- 5097643 27-mar-06 5029064: changes made by kbbhavsa for bug 4717511 has been reversed
735 --SECHAWLA 21-DEC-05 4899337 : end
736 l_dep_cost := l_assettrx_rec.depreciation_cost; -- sgorantl 27-mar-06 5097643
737
738 IF l_fa_cost <> l_dep_cost THEN --SECHAWLA 21-DEC-05 4899337: added
739 -- kbbhavsa : added for bug 4717511 -- end
740
741 l_delta_cost := l_dep_cost - l_fa_cost;
742 l_asset_fin_rec_adj.cost := l_delta_cost;
743
744 l_asset_hdr_rec.asset_id := l_assettrx_rec.dnz_asset_id;
745 l_asset_hdr_rec.book_type_code := l_assettrx_rec.asset_book;
746 -- rbruno Bug 6360770 Added code to set contract_id
747 -- sechawla 14-dec-07 6690811 : Removed code to set contract id
748 -- l_asset_fin_rec_adj.contract_id := FND_API.G_MISS_NUM;
749 -- Bug 6965689 start
750 l_trans_rec.transaction_date_entered := l_assettrx_rec.date_trans_occurred;
751 -- Bug 6965689 end
752 fa_adjustment_pub.do_adjustment(
753 p_api_version => p_api_version,
754 p_init_msg_list => OKC_API.G_FALSE,
755 p_commit => FND_API.G_FALSE,
756 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
757 p_calling_fn => NULL,
758 x_return_status => l_return_status,
759 x_msg_count => x_msg_count,
760 x_msg_data => x_msg_data,
761 px_trans_rec => l_trans_rec,
762 px_asset_hdr_rec => l_asset_hdr_rec,
763 p_asset_fin_rec_adj => l_asset_fin_rec_adj,
764 x_asset_fin_rec_new => l_asset_fin_rec_new,
765 x_asset_fin_mrc_tbl_new => l_asset_fin_mrc_tbl_new,
766 px_inv_trans_rec => l_inv_trans_rec,
767 px_inv_tbl => l_inv_tbl,
768 p_asset_deprn_rec_adj => l_asset_deprn_rec_adj,
769 x_asset_deprn_rec_new => l_asset_deprn_rec_new,
770 x_asset_deprn_mrc_tbl_new => l_asset_deprn_mrc_tbl_new,
771 p_group_reclass_options_rec => l_group_reclass_options_rec);
772
773 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
774
775 -- Error processing TRX_TYPE transaction in Fixed Assets for asset ASSET_NUMBER in book BOOK.
776 OKC_API.set_message( p_app_name => 'OKL',
777 p_msg_name => 'OKL_AM_AMT_TRANS_FAILED',
778 p_token1 => 'TRX_TYPE',
779 p_token1_value => l_assettrx_rec.TAS_TYPE_MEANING,
780 p_token2 => 'ASSET_NUMBER', -- SECHAWLA Bug # 2701440 : Added token2 value
781 p_token2_value => l_assettrx_rec.asset_number,
782 p_token3 => 'BOOK',
783 p_token3_value => l_assettrx_rec.asset_book
784 );
785 l_transaction_status := l_return_status;
786
787 ELSE -- 17-DEC-04 SECHAWLA 4028371 added else section
788 l_fa_trx_date := l_trans_rec.transaction_date_entered;
789 END IF;
790 --akrangan populate sources cr changes start
791 -- header record
792 l_fxhv_rec.source_id := l_assettrx_rec.id;
793 l_fxhv_rec.source_table := 'OKL_TRX_ASSETS';
794 l_fxhv_rec.khr_id := l_chr_id;
795 l_fxhv_rec.try_id := l_assettrx_rec.try_id;
796 --line record
797 l_fxlv_rec.source_id := l_assettrx_rec.line_id;
798 l_fxlv_rec.source_table := 'OKL_TXL_ASSETS_B';
799 l_fxlv_rec.asset_id := l_assettrx_rec.dnz_asset_id;
800 l_fxlv_rec.kle_id := l_assettrx_rec.kle_id;
801 l_fxlv_rec.fa_transaction_id := l_trans_rec.transaction_header_id;
802 l_fxlv_rec.asset_book_type_name := l_assettrx_rec.asset_book;
803 --call api
804 okl_sla_acc_sources_pvt.populate_sources(p_api_version => p_api_version,
805 p_init_msg_list => okc_api.g_false,
806 p_fxhv_rec => l_fxhv_rec,
807 p_fxlv_rec => l_fxlv_rec,
808 x_return_status => x_return_status,
809 x_msg_count => x_msg_count,
810 x_msg_data => x_msg_data);
811
812 IF (x_return_status = okc_api.g_ret_sts_unexp_error)
813 THEN
814 RAISE okl_api.g_exception_unexpected_error;
815 ELSIF (x_return_status = okc_api.g_ret_sts_error)
816 THEN
817 RAISE okl_api.g_exception_error;
818 END IF;
819 --akrangan populate sources cr changes end
820 ELSIF l_deal_type IN ('LEASEDF','LEASEST') THEN -- 17-DEC-04 SECHAWLA 4028371 added else section
821 OKL_ACCOUNTING_UTIL.get_fa_trx_date(
822 p_book_type_code => l_assettrx_rec.asset_book,
823 x_return_status => l_return_status,
824 x_fa_trx_date => l_fa_trx_date);
825
826 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
827 l_transaction_status := l_return_status;
828 END IF;
829 END IF;
830
831 END IF;
832
833 -- SECHAWLA 19-FEB-04 3439647 : end
834
835 ELSIF
836 ( -- SECHAWLA 06-MAY-04 3578894 :
837 -- Op lease, hold period, 2nd trx - update dep method, life
838 -- DF/Sales lease, hold period, 3rd trx - update dep method, life, cost, sv
839 -- DF/Sales lease, no hold period, 1st trx - update dep method, life, cost, sv
840
841 --(l_assettrx_rec.tas_type IN ('AMT', 'AED')) AND (l_assettrx_rec.depreciate_yn = 'Y')
842 -- SECHAWLA 06-MAY-04 3578894 : Removing 'AED' as evergreen transactions are not created
843 (l_assettrx_rec.tas_type = 'AMT') AND (l_assettrx_rec.depreciate_yn = 'Y')
844
845 ) THEN
846
847 IF l_assettrx_rec.deprn_method IS NULL OR l_assettrx_rec.deprn_method = OKL_APi.G_MISS_CHAR THEN
848 -- Can not process TRX_TYPE transaction in Fixed Assets for asset ASSET_NUMBER and book ASSET_BOOK as FIELD is missing.
849 OKC_API.set_message( p_app_name => 'OKL',
850 p_msg_name => 'OKL_AM_DATA_REQUIRED',
851 p_token1 => 'TRX_TYPE',
852 p_token1_value => l_assettrx_rec.TAS_TYPE_MEANING,
853 p_token2 => 'ASSET_NUMBER',
854 p_token2_value => l_assettrx_rec.asset_number,
855 p_token3 => 'ASSET_BOOK',
856 p_token3_value => l_assettrx_rec.asset_book,
857 p_token4 => 'FIELD',
858 p_token4_value => 'Depreciation Method');
859 l_transaction_status := OKC_API.G_RET_STS_ERROR;
860 ELSIF ( (l_assettrx_rec.life_in_months IS NULL OR l_assettrx_rec.life_in_months = OKL_APi.G_MISS_NUM)
861 AND --SECHAWLA 28-MAY-04 3645574 : Added deprn_rate check
862 (l_assettrx_rec.deprn_rate IS NULL OR l_assettrx_rec.deprn_rate = OKL_APi.G_MISS_NUM)
863 ) THEN
864 -- Can not process transaction in Fixed Assets for asset ASSET_NUMBER and book ASSET_BOOK as FIELD is missing.
865 OKC_API.set_message( p_app_name => 'OKL',
866 p_msg_name => 'OKL_AM_DATA_REQUIRED',
867 p_token1 => 'TRX_TYPE',
868 p_token1_value => l_assettrx_rec.TAS_TYPE_MEANING,
869 p_token2 => 'ASSET_NUMBER',
870 p_token2_value => l_assettrx_rec.asset_number,
871 p_token3 => 'ASSET_BOOK',
872 p_token3_value => l_assettrx_rec.asset_book,
873 p_token4 => 'FIELD',
874 p_token4_value => 'Life In Months / Depreciation Rate'); --SECHAWLA 28-MAY-04 3645574 : Added deprn_rate
875 l_transaction_status := OKC_API.G_RET_STS_ERROR;
876
877 ELSIF l_assettrx_rec.salvage_value IS NULL OR l_assettrx_rec.salvage_value = OKL_APi.G_MISS_NUM THEN
878 -- Can not process transaction in Fixed Assets for asset ASSET_NUMBER and book ASSET_BOOK as FIELD is missing.
879 OKC_API.set_message( p_app_name => 'OKL',
880 p_msg_name => 'OKL_AM_DATA_REQUIRED',
881 p_token1 => 'TRX_TYPE',
882 p_token1_value => l_assettrx_rec.TAS_TYPE_MEANING,
883 p_token2 => 'ASSET_NUMBER',
884 p_token2_value => l_assettrx_rec.asset_number,
885 p_token3 => 'ASSET_BOOK',
886 p_token3_value => l_assettrx_rec.asset_book,
887 p_token4 => 'FIELD',
888 p_token4_value => 'Salvage Value');
889 l_transaction_status := OKC_API.G_RET_STS_ERROR;
890
891 ELSE
892 -- sgorantl 27-mar-06 5097643: changes made by kbbhavsa for bug 4717511 has been reversed
893 l_dep_cost := l_assettrx_rec.depreciation_cost; -- sgorantl 27-mar-06 5097643
894
895 l_sal_value := l_assettrx_rec.salvage_value; -- sgorantl 27-mar-06 5097643
896 --SECHAWLA 21-DEC-05 4899337 : Moved this piece here : end
897
898
899
900
901 IF (l_fa_deprn_method_code <> l_assettrx_rec.deprn_method) OR
902 -- SECHAWLA 28-MAY-04 3645574 : Added deprn_rate conditional checks
903 (( l_assettrx_rec.life_in_months IS NOT NULL) AND (l_fa_life_in_months IS NULL OR l_fa_life_in_months <> l_assettrx_rec.life_in_months)) OR
904 (( l_assettrx_rec.deprn_rate IS NOT NULL) AND (l_fa_deprn_rate IS NULL OR l_fa_deprn_rate <> l_assettrx_rec.deprn_rate)) OR
905 --Mar 05, 2006 sgorantl - 115.35 4631549 : compare FA cost with converted cost
906 (l_assettrx_rec.depreciation_cost IS NOT NULL AND l_fa_cost <> l_dep_cost) OR
907 --Mar 05, 2006 sgorantl - 115.35 4631549 : compare FA SV with converted SV
908 (l_fa_salvage_value <> l_sal_value) THEN -- SECHAWLA 10-MAY-04 3578894 : FA update fails
909
910
911 -- asset header information
912 l_asset_hdr_rec.asset_id := l_assettrx_rec.dnz_asset_id;
913
914 -- SECHAWLA Bug # 2701440 : commented out the following assignment
915 --l_asset_hdr_rec.book_type_code := l_assettrx_rec.corporate_book;
916
917
918 l_asset_fin_rec_adj.deprn_method_code := l_assettrx_rec.deprn_method;
919
920 --SECHAWLA 28-MAY-04 3645574 : update life or rate and nullify the other
921 IF l_assettrx_rec.life_in_months IS NOT NULL THEN
922 l_asset_fin_rec_adj.life_in_months := l_assettrx_rec.life_in_months;
923 l_asset_fin_rec_adj.adjusted_rate := NULL;
924 -- SECHAWLA 07-JUN-04 3645574 : FA requires both basic_rate and adjusted_rate to be set together
925 l_asset_fin_rec_adj.basic_rate := NULL;
926 ELSE
927 l_asset_fin_rec_adj.life_in_months := NULL;
928 l_asset_fin_rec_adj.adjusted_rate := l_assettrx_rec.deprn_rate;
929 -- SECHAWLA 07-JUN-04 3645574 : FA requires both basic_rate and adjusted_rate to be set together
930 -- Currently Authoring allows only those values for rates where basic rate = adjusted rate
931 l_asset_fin_rec_adj.basic_rate := l_assettrx_rec.deprn_rate;
932 END IF;
933
934
935 IF l_assettrx_rec.depreciation_cost IS NOT NULL THEN -- IF base product is DF Lease and rep product is OP lease,
936 -- then AMT trx will be created for corp/tax book (not null cost)
937 -- and also for rep book(null cost)
938
939 -- SECHAWLA 29-JUL-05 4456005 : convert dep cost to functional currency - end
940 --l_delta_cost := l_assettrx_rec.depreciation_cost - l_fa_cost; -- SECHAWLA 29-JUL-05 4456005
941
942 -- delta for cost is the new modified cost in OKL - original cost in FA
943 l_delta_cost := l_dep_cost - l_fa_cost; ---- SECHAWLA 29-JUL-05 4456005
944
945 l_asset_fin_rec_adj.cost := l_delta_cost;
946 END IF;
947
948
949 -- SECHAWLA 29-JUL-05 4456005 : convert dep cost to functional currency - end
950 --l_delta_salvage_value := l_assettrx_rec.salvage_value - l_fa_salvage_value; ---- SECHAWLA 29-JUL-05 4456005
951 l_delta_salvage_value := l_sal_value - l_fa_salvage_value;
952 l_asset_fin_rec_adj.salvage_value := l_delta_salvage_value; --SECHAWLA 14-FEB-05 3950089
953
954 IF l_delta_salvage_value <> 0 THEN -- Mar 05, 2006 sgorantl - 115.35 4631549 : added this condition
955
956 --SECHAWLA 14-FEB-05 3950089 : begin
957 OPEN l_booksalvagetype_csr(l_assettrx_rec.dnz_asset_id, l_assettrx_rec.asset_book);
958 FETCH l_booksalvagetype_csr INTO l_salvage_type;
959 CLOSE l_booksalvagetype_csr;
960
961 IF l_salvage_type = 'AMT' then
962 l_asset_fin_rec_adj.salvage_value := l_delta_salvage_value;
963 ELSIF l_salvage_type = 'PCT' THEN
964 l_asset_fin_rec_adj.salvage_value := l_assettrx_rec.salvage_value;
965 END IF;
966 l_asset_fin_rec_adj.salvage_type := 'AMT' ;
967 --SECHAWLA 14-FEB-05 3950089 : end
968 END IF;
969
970
971 -- rbruno Bug 6360770 Added code to set contract_id
972 -- sechawla 14-dec-07 6690811 : Removed code to set contract id
973 --l_asset_fin_rec_adj.contract_id := FND_API.G_MISS_NUM;
974
975 -- rmunjulu Bug 4150696 Added code to set transaction_date_entered with Date Transaction Occurred
976 l_trans_rec.transaction_date_entered := l_assettrx_rec.date_trans_occurred;
977
978 l_asset_hdr_rec.book_type_code := l_assettrx_rec.asset_book;
979 fa_adjustment_pub.do_adjustment(
980 p_api_version => p_api_version,
981 p_init_msg_list => OKC_API.G_FALSE,
982 p_commit => FND_API.G_FALSE,
983 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
984 p_calling_fn => NULL,
985 x_return_status => l_return_status,
986 x_msg_count => x_msg_count,
987 x_msg_data => x_msg_data,
988 px_trans_rec => l_trans_rec,
989 px_asset_hdr_rec => l_asset_hdr_rec,
990 p_asset_fin_rec_adj => l_asset_fin_rec_adj,
991 x_asset_fin_rec_new => l_asset_fin_rec_new,
992 x_asset_fin_mrc_tbl_new => l_asset_fin_mrc_tbl_new,
993 px_inv_trans_rec => l_inv_trans_rec,
994 px_inv_tbl => l_inv_tbl,
995 p_asset_deprn_rec_adj => l_asset_deprn_rec_adj,
996 x_asset_deprn_rec_new => l_asset_deprn_rec_new,
997 x_asset_deprn_mrc_tbl_new => l_asset_deprn_mrc_tbl_new,
998 p_group_reclass_options_rec => l_group_reclass_options_rec);
999
1000
1001 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1002 -- Error processing TRX_TYPE transaction in Fixed Assets for asset ASSET_NUMBER in book BOOK.
1003
1004 OKC_API.set_message( p_app_name => 'OKL',
1005 p_msg_name => 'OKL_AM_AMT_TRANS_FAILED',
1006 p_token1 => 'TRX_TYPE',
1007 p_token1_value => l_assettrx_rec.TAS_TYPE_MEANING,
1008 p_token2 => 'ASSET_NUMBER',
1009 p_token2_value => l_assettrx_rec.asset_number,
1010 p_token3 => 'BOOK', -- SECHAWLA Bug # 2701440 : Added token2
1011 p_token3_value => l_assettrx_rec.asset_book
1012 ); -- SECHAWLA Bug # 2701440 : Added token2 value
1013
1014 l_transaction_status := l_return_status;
1015 ELSE -- 17-DEC-04 SECHAWLA 4028371 added else section
1016 l_fa_trx_date := l_trans_rec.transaction_date_entered;
1017 END IF;
1018 --akrangan populate sources cr changes start
1019 -- header record
1020 l_fxhv_rec.source_id := l_assettrx_rec.id;
1021 l_fxhv_rec.source_table := 'OKL_TRX_ASSETS';
1022 l_fxhv_rec.khr_id := l_chr_id;
1023 l_fxhv_rec.try_id := l_assettrx_rec.try_id;
1024 --line record
1025 l_fxlv_rec.source_id := l_assettrx_rec.line_id;
1026 l_fxlv_rec.source_table := 'OKL_TXL_ASSETS_B';
1027 l_fxlv_rec.asset_id := l_assettrx_rec.dnz_asset_id;
1028 l_fxlv_rec.kle_id := l_assettrx_rec.kle_id;
1029 l_fxlv_rec.fa_transaction_id := l_trans_rec.transaction_header_id;
1030 l_fxlv_rec.asset_book_type_name := l_assettrx_rec.asset_book;
1031 --call api
1032 okl_sla_acc_sources_pvt.populate_sources(p_api_version => p_api_version,
1033 p_init_msg_list => okc_api.g_false,
1034 p_fxhv_rec => l_fxhv_rec,
1035 p_fxlv_rec => l_fxlv_rec,
1036 x_return_status => x_return_status,
1037 x_msg_count => x_msg_count,
1038 x_msg_data => x_msg_data);
1039
1040 IF (x_return_status = okc_api.g_ret_sts_unexp_error)
1041 THEN
1042 RAISE okl_api.g_exception_unexpected_error;
1043 ELSIF (x_return_status = okc_api.g_ret_sts_error)
1044 THEN
1045 RAISE okl_api.g_exception_error;
1046 END IF;
1047 --akrangan populate sources cr changes end
1048 ELSE -- 17-DEC-04 SECHAWLA 4028371 added else section
1049 OKL_ACCOUNTING_UTIL.get_fa_trx_date(
1050 p_book_type_code => l_assettrx_rec.asset_book,
1051 x_return_status => l_return_status,
1052 x_fa_trx_date => l_fa_trx_date);
1053
1054 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1055 l_transaction_status := l_return_status;
1056 END IF;
1057 END IF;
1058 END IF;
1059 ------------------------------------------------------
1060
1061 ELSIF
1062 ( -- SECHAWLA 06-MAY-04 3578894 : Added this scenario for :
1063 -- OP Lease, hold period, 3rd trx - start dep
1064 -- DF/Sales Lease, hold period, 4th trx - start dep
1065 -- DF/Sales Lease, no hold period, 2nd trx - start dep
1066 (l_assettrx_rec.tas_type = 'AUD') AND (l_assettrx_rec.depreciate_yn = 'Y')
1067 ) THEN
1068
1069 IF l_fa_depreciate_flag <> 'YES' THEN -- SECHAWLA 10-MAY-04 3578894 : if there is no hold period,
1070 -- the dep flag will normally = YES. FA update fails with error
1071 -- if we try to update the flag to the same value
1072 -- asset header information
1073 l_asset_hdr_rec.asset_id := l_assettrx_rec.dnz_asset_id;
1074
1075 -- SECHAWLA Bug # 2701440 : commented out the following assignment
1076 --l_asset_hdr_rec.book_type_code := l_assettrx_rec.corporate_book;
1077
1078 -- financial information
1079 -- SECHAWLA 06-MAY-04 3578894 : start the depreciation
1080 l_asset_fin_rec_adj.depreciate_flag := 'YES';
1081
1082
1083 -- rbruno Bug 6360770 Added code to set contract_id
1084 -- sechawla 14-dec-07 6690811 : Removed code to set contract id
1085 --l_asset_fin_rec_adj.contract_id := FND_API.G_MISS_NUM;
1086
1087 -- rmunjulu Bug 4150696 Added code to set transaction_date_entered with Date Transaction Occurred
1088 l_trans_rec.transaction_date_entered := l_assettrx_rec.date_trans_occurred;
1089
1090 l_asset_hdr_rec.book_type_code := l_assettrx_rec.asset_book;
1091 fa_adjustment_pub.do_adjustment(
1092 p_api_version => p_api_version,
1093 p_init_msg_list => OKC_API.G_FALSE,
1094 p_commit => FND_API.G_FALSE,
1095 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1096 p_calling_fn => NULL,
1097 x_return_status => l_return_status,
1098 x_msg_count => x_msg_count,
1099 x_msg_data => x_msg_data,
1100 px_trans_rec => l_trans_rec,
1101 px_asset_hdr_rec => l_asset_hdr_rec,
1102 p_asset_fin_rec_adj => l_asset_fin_rec_adj,
1103 x_asset_fin_rec_new => l_asset_fin_rec_new,
1104 x_asset_fin_mrc_tbl_new => l_asset_fin_mrc_tbl_new,
1105 px_inv_trans_rec => l_inv_trans_rec,
1106 px_inv_tbl => l_inv_tbl,
1107 p_asset_deprn_rec_adj => l_asset_deprn_rec_adj,
1108 x_asset_deprn_rec_new => l_asset_deprn_rec_new,
1109 x_asset_deprn_mrc_tbl_new => l_asset_deprn_mrc_tbl_new,
1110 p_group_reclass_options_rec => l_group_reclass_options_rec);
1111
1112
1113 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1114 -- Error processing TRX_TYPE transaction in Fixed Assets for asset ASSET_NUMBER in book BOOK.
1115
1116 OKC_API.set_message( p_app_name => 'OKL',
1117 p_msg_name => 'OKL_AM_AMT_TRANS_FAILED',
1118 p_token1 => 'TRX_TYPE',
1119 p_token1_value => l_assettrx_rec.TAS_TYPE_MEANING,
1120 p_token2 => 'ASSET_NUMBER',
1121 p_token2_value => l_assettrx_rec.asset_number,
1122 p_token3 => 'BOOK', -- SECHAWLA Bug # 2701440 : Added token2
1123 p_token3_value => l_assettrx_rec.asset_book
1124 ); -- SECHAWLA Bug # 2701440 : Added token2 value
1125
1126 l_transaction_status := l_return_status;
1127 ELSE -- 17-DEC-04 SECHAWLA 4028371 added else section
1128 l_fa_trx_date := l_trans_rec.transaction_date_entered;
1129
1130 END IF;
1131 --akrangan populate sources cr changes start
1132 -- header record
1133 l_fxhv_rec.source_id := l_assettrx_rec.id;
1134 l_fxhv_rec.source_table := 'OKL_TRX_ASSETS';
1135 l_fxhv_rec.khr_id := l_chr_id;
1136 l_fxhv_rec.try_id := l_assettrx_rec.try_id;
1137 --line record
1138 l_fxlv_rec.source_id := l_assettrx_rec.line_id;
1139 l_fxlv_rec.source_table := 'OKL_TXL_ASSETS_B';
1140 l_fxlv_rec.asset_id := l_assettrx_rec.dnz_asset_id;
1141 l_fxlv_rec.kle_id := l_assettrx_rec.kle_id;
1142 l_fxlv_rec.fa_transaction_id := l_trans_rec.transaction_header_id;
1143 l_fxlv_rec.asset_book_type_name := l_assettrx_rec.asset_book;
1144 --call api
1145 okl_sla_acc_sources_pvt.populate_sources(p_api_version => p_api_version,
1146 p_init_msg_list => okc_api.g_false,
1147 p_fxhv_rec => l_fxhv_rec,
1148 p_fxlv_rec => l_fxlv_rec,
1149 x_return_status => x_return_status,
1150 x_msg_count => x_msg_count,
1151 x_msg_data => x_msg_data);
1152
1153 IF (x_return_status = okc_api.g_ret_sts_unexp_error)
1154 THEN
1155 RAISE okl_api.g_exception_unexpected_error;
1156 ELSIF (x_return_status = okc_api.g_ret_sts_error)
1157 THEN
1158 RAISE okl_api.g_exception_error;
1159 END IF;
1160 --akrangan populate sources cr changes end
1161 ELSE -- 17-DEC-04 SECHAWLA 4028371 added else section
1162 OKL_ACCOUNTING_UTIL.get_fa_trx_date(
1163 p_book_type_code => l_assettrx_rec.asset_book,
1164 x_return_status => l_return_status,
1165 x_fa_trx_date => l_fa_trx_date);
1166
1167 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1168 l_transaction_status := l_return_status;
1169 END IF;
1170 END IF;
1171
1172
1173 -- SECHAWLA 06-MAY-04 3578894 : Process FSC transactions separately
1174 ------------------------------------------------------
1175
1176 END IF; --IF l_facostsv_csr%NOTFOUND THEN
1177 CLOSE l_facostsv_csr;
1178
1179 END IF; --IF l_assettrx_rec.asset_book IS NULL...
1180
1181
1182 END IF; --IF l_dealtype_csr%NOTFOUND OR l_deal_type IS NULL OR l_deal_type = OKL_API.G_MISS_CHAR THEN
1183 CLOSE l_dealtype_csr;
1184 --------------
1185
1186 -- 17-DEC-04 SECHAWLA 4028371 : update FA trx date on the trx line
1187 IF l_fa_trx_date IS NOT NULL THEN -- this particular asset trx was processed successfully in FA
1188 -- update date on the trx line
1189 lp_tlpv_rec.id := l_assettrx_rec.line_id;
1190 lp_tlpv_rec.FA_TRX_DATE := l_fa_trx_date;
1191
1192 OKL_TXL_ASSETS_PUB.update_txl_asset_Def(
1193 p_api_version => p_api_version,
1194 p_init_msg_list => OKC_API.G_FALSE,
1195 x_return_status => l_return_status,
1196 x_msg_count => x_msg_count,
1197 x_msg_data => x_msg_data,
1198 p_tlpv_rec => lp_tlpv_rec,
1199 x_tlpv_rec => lx_tlpv_rec);
1200
1201 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1202 -- Error updating Fixed Assets transaction date on TRX_TYPE transaction for asset ASSET_NUMBER in book BOOK.
1203 OKC_API.set_message( p_app_name => 'OKL',
1204 p_msg_name => 'OKL_AM_FA_DT_UPD_ERR',
1205 p_token1 => 'TRX_TYPE',
1206 p_token1_value => l_assettrx_rec.TAS_TYPE_MEANING,
1207 p_token2 => 'ASSET_NUMBER',
1208 p_token2_value => l_assettrx_rec.asset_number,
1209 p_token3 => 'BOOK',
1210 p_token3_value => l_assettrx_rec.asset_book);
1211
1212 l_transaction_status := OKL_API.G_RET_STS_ERROR;
1213 END IF;
1214 END IF;
1215 -- 17-DEC-04 SECHAWLA 4028371 : end
1216 ---------------------------------------
1217
1218 l_pos := l_pos + 1;
1219 -- Store the transaction header ids for all the transactions for a particular asset
1220 l_trxassets_tbl(l_pos).id := l_assettrx_rec.id;
1221 l_trxassets_tbl(l_pos).asset_number := l_assettrx_rec.asset_number;
1222 l_trxassets_tbl(l_pos).asset_book := l_assettrx_rec.asset_book;
1223 l_trxassets_tbl(l_pos).tas_type_meaning := l_assettrx_rec.tas_type_meaning;
1224
1225
1226 END LOOP;
1227
1228 ----------
1229
1230
1231 l_update_status := OKC_API.G_RET_STS_SUCCESS;
1232
1233 IF l_transaction_status = OKC_API.G_RET_STS_SUCCESS THEN -- all the trasnsactions for this asset were successful
1234 IF l_trxassets_tbl.COUNT > 0 THEN
1235 j := l_trxassets_tbl.FIRST;
1236 LOOP
1237
1238 -- update the staus (tsu_code) in okl_trx_assets_v
1239 lp_thpv_rec.id := l_trxassets_tbl(j).id;
1240 lp_thpv_rec.tsu_code := 'PROCESSED';
1241 OKL_TRX_ASSETS_PUB.update_trx_ass_h_def(
1242 p_api_version => p_api_version,
1243 p_init_msg_list => OKC_API.G_FALSE,
1244 x_return_status => l_return_status,
1245 x_msg_count => x_msg_count,
1246 x_msg_data => x_msg_data,
1247 p_thpv_rec => lp_thpv_rec,
1248 x_thpv_rec => lx_thpv_rec);
1249
1250 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1251 -- TRX_TYPE Transaction status STATUS could not be updated in OKL for asset ASSET_NUMBER and book ASSET_BOOK.
1252 OKC_API.set_message( p_app_name => 'OKL',
1253 p_msg_name => 'OKL_AM_TRXASSET_UPD_FAILED',
1254 p_token1 => 'TRX_TYPE',
1255 p_token1_value => l_trxassets_tbl(j).tas_type_meaning,
1256 p_token2 => 'STATUS',
1257 p_token2_value => 'PROCESSED',
1258 p_token3 => 'ASSET_NUMBER',
1259 p_token3_value => l_trxassets_tbl(j).asset_number,
1260 p_token4 => 'ASSET_BOOK',
1261 p_token4_value => l_trxassets_tbl(j).asset_book);
1262
1263 l_update_status := OKC_API.G_RET_STS_ERROR;
1264
1265 END IF;
1266
1267 EXIT WHEN (j = l_trxassets_tbl.LAST);
1268 j := l_trxassets_tbl.NEXT(j);
1269 END LOOP;
1270
1271 IF l_update_status = OKC_API.G_RET_STS_SUCCESS THEN -- trx status updated successfully for all the transactions for this asset
1272 COMMIT; -- it will commit changes in FA and also in OKL (trx status update)
1273 -- off-lease trx have been updated for asset ASSET_NUMBER in Fixed Assets.
1274 OKC_API.set_message( p_app_name => 'OKL',
1275 p_msg_name => 'OKL_AM_FA_UPD_DONE',
1276 p_token1 => 'ASSET_NUMBER',
1277 p_token1_value => l_distinctasset_rec.asset_number);
1278
1279 l_process_count := l_process_count + l_trx_count;
1280 ELSE
1281 ROLLBACK TO asset_updates; -- This will rollback FA changes and also OKL changes (trx status update, incase status was chnaged
1282 -- to PROCESSED for some of the transactions
1283 -- The transactions will stay in ENTERED status
1284 -- Off-Lease trasnactions were not processed for asset ASSET_NUMBER in Fixed Assets.
1285 OKC_API.set_message( p_app_name => 'OKL',
1286 p_msg_name => 'OKL_AM_FA_UPD_NOT_DONE',
1287 p_token1 => 'ASSET_NUMBER',
1288 p_token1_value => l_distinctasset_rec.asset_number);
1289 END IF;
1290
1291 END IF; --IF l_trxassets_tbl.COUNT > 0 THEN
1292
1293
1294 ELSE -- IF l_transaction_status <> OKC_API.G_RET_STS_SUCCESS THEN
1295 -- one or more transactions errored out for this asset
1296
1297 -- First rollback FA changes and then update trx status to ERROR
1298 ROLLBACK TO asset_updates;
1299 -- Off-Lease trasnactions were not processed for asset ASSET_NUMBER in Fixed Assets.
1300 OKC_API.set_message( p_app_name => 'OKL',
1301 p_msg_name => 'OKL_AM_FA_UPD_NOT_DONE',
1302 p_token1 => 'ASSET_NUMBER',
1303 p_token1_value => l_distinctasset_rec.asset_number);
1304
1305 BEGIN
1306
1307 SAVEPOINT trx_status_update;
1308 -- Update trx status to ERROR in OKL
1309 IF l_trxassets_tbl.COUNT > 0 THEN
1310 j := l_trxassets_tbl.FIRST;
1311 LOOP
1312 -- update the staus (tsu_code) in okl_trx_assets_v
1313 lp_thpv_rec.id := l_trxassets_tbl(j).id;
1314 lp_thpv_rec.tsu_code := 'ERROR';
1315 OKL_TRX_ASSETS_PUB.update_trx_ass_h_def(
1316 p_api_version => p_api_version,
1317 p_init_msg_list => OKC_API.G_FALSE,
1318 x_return_status => l_return_status,
1319 x_msg_count => x_msg_count,
1320 x_msg_data => x_msg_data,
1321 p_thpv_rec => lp_thpv_rec,
1322 x_thpv_rec => lx_thpv_rec);
1323
1324 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1325
1326 -- TRX_TYPE Transaction status STATUS could not be updated in OKL for asset ASSET_NUMBER and book ASSET_BOOK.
1327 OKC_API.set_message( p_app_name => 'OKL',
1328 p_msg_name => 'OKL_AM_TRXASSET_UPD_FAILED',
1329 p_token1 => 'TRX_TYPE',
1330 p_token1_value => l_trxassets_tbl(j).tas_type_meaning,
1331 p_token2 => 'STATUS',
1332 p_token2_value => 'ERROR',
1333 p_token3 => 'ASSET_NUMBER',
1334 p_token3_value => l_trxassets_tbl(j).asset_number,
1335 p_token4 => 'ASSET_BOOK',
1336 p_token4_value => l_trxassets_tbl(j).asset_book);
1337 l_update_status := OKC_API.G_RET_STS_ERROR;
1338 END IF;
1339
1340 EXIT WHEN (j = l_trxassets_tbl.LAST);
1341 j := l_trxassets_tbl.NEXT(j);
1342 END LOOP;
1343
1344 IF l_update_status = OKC_API.G_RET_STS_SUCCESS THEN -- trx status updated succesfully to 'ERROR' for all trx for this asset
1345 COMMIT; -- commit the trx status upadte to ERROR
1346 ELSE -- trx status could not be updated to ERROR for one or more transactions
1347 ROLLBACK to trx_status_update; -- trx status will remain ENTERED (fa trx have already been rolled back)
1348 END IF;
1349 END IF; --IF l_trxassets_tbl.COUNT > 0 THEN
1350 END;
1351
1352
1353 END IF; -- IF l_transaction_status = OKC_API.G_RET_STS_SUCCESS THEN
1354
1355
1356
1357 END; -- process amortization transactions end
1358
1359 IF p_salvage_writedown_yn = 'Y' THEN
1360 -- SECHAWLA 06-MAY-04 3578894 : Process SVW transactions separately
1361 BEGIN -- process SVW transactions begin
1362 l_transaction_status := OKC_API.G_RET_STS_SUCCESS;
1363
1364 -- As of now, this cursor will return only 1 row for a particulat asset. SVW transactions
1365 -- are being created only for corporate book but processed in all books
1366 FOR l_assetsvtrx_rec IN l_assetsvtrx_csr(l_sysdate, l_distinctasset_rec.asset_number) LOOP
1367
1368
1369 l_total_count := l_total_count + 1;
1370 --l_trx_count := l_trx_count + 1;
1371
1372 -- SECHAWLA Bug # 2701440 : initialize the asset books table
1373 l_books_tbl := l_empty_books_tbl;
1374
1375
1376 -- get the deal type from the contract
1377 OPEN l_dealtype_csr(l_assetsvtrx_rec.kle_id);
1378 FETCH l_dealtype_csr INTO l_chr_id, l_deal_type, l_contract_number;
1379 IF l_dealtype_csr%NOTFOUND OR l_deal_type IS NULL OR l_deal_type = OKL_API.G_MISS_CHAR THEN
1380 -- Can not find deal type for asset ASSET_NUMBER.
1381 OKC_API.set_message( p_app_name => 'OKL',
1382 p_msg_name => 'OKL_AM_DEAL_TYPE_NOT_FOUND',
1383 p_token1 => 'ASSET_NUMBER',
1384 p_token1_value => l_assetsvtrx_rec.asset_number);
1385
1386 l_transaction_status := OKC_API.G_RET_STS_ERROR;
1387 ELSE
1388
1389 IF l_assetsvtrx_rec.asset_book IS NULL OR l_assetsvtrx_rec.asset_book = OKL_API.G_MISS_CHAR THEN
1390 --Can not process TRX_TYPE transaction in Fixed Assets for asset ASSET_NUMBER as FIELD is missing.
1391 OKC_API.set_message( p_app_name => 'OKL',
1392 p_msg_name => 'OKL_AM_NO_ASSET_BOOK',
1393 p_token1 => 'TRX_TYPE',
1394 p_token1_value => l_assetsvtrx_rec.TAS_TYPE_MEANING,
1395 p_token2 => 'ASSET_NUMBER',
1396 p_token2_value => l_assetsvtrx_rec.asset_number,
1397 p_token3 => 'FIELD',
1398 p_token3_value => 'Asset Book');
1399
1400 l_transaction_status := OKC_API.G_RET_STS_ERROR;
1401 -- SECHAWLA 06-MAY-04 3578894 Added new tas types 'AUD','AUS' to following condition
1402
1403
1404
1405 ELSIF l_assetsvtrx_rec.dnz_asset_id IS NULL OR l_assetsvtrx_rec.dnz_asset_id = OKL_API.G_MISS_NUM THEN
1406 --Can not process TRX_TYPE transaction in Fixed Assets for asset ASSET_NUMBER and book ASSET_BOOK as Asset ID
1407 -- is missing.
1408 OKC_API.set_message( p_app_name => 'OKL',
1409 p_msg_name => 'OKL_AM_DATA_REQUIRED',
1410 p_token1 => 'TRX_TYPE',
1411 p_token1_value => l_assetsvtrx_rec.TAS_TYPE_MEANING,
1412 p_token2 => 'ASSET_NUMBER',
1413 p_token2_value => l_assetsvtrx_rec.asset_number,
1414 p_token3 => 'ASSET_BOOK',
1415 p_token3_value => l_assetsvtrx_rec.asset_book,
1416 p_token4 => 'FIELD',
1417 p_token4_value => 'Asset ID');
1418
1419 l_transaction_status := OKC_API.G_RET_STS_ERROR;
1420
1421 ELSE
1422
1423 OPEN l_facostsv_csr(l_assetsvtrx_rec.asset_number, l_assetsvtrx_rec.asset_book);
1424 -- SECHAWLA 10-MAY-04 3578894 : added l_depreciate_flag, l_deprn_method_code, l_life_in_months
1425 FETCH l_facostsv_csr INTO l_fa_cost, l_fa_salvage_value,l_fa_depreciate_flag, l_fa_deprn_method_code,
1426 l_fa_life_in_months , l_fa_deprn_rate; --SECHAWLA 28-MAY-04 3645574 : Added deprn_rate ;
1427 IF l_facostsv_csr%NOTFOUND OR l_fa_cost IS NULL OR l_fa_salvage_value IS NULL THEN
1428 --Can not process TRX_TYPE transaction in Fixed Assets for asset ASSET_NUMBER and book ASSET_BOOK as Cost / Salvage Value
1429 -- is missing.
1430
1431 OKC_API.set_message( p_app_name => 'OKL',
1432 p_msg_name => 'OKL_AM_DATA_REQUIRED',
1433 p_token1 => 'TRX_TYPE',
1434 p_token1_value => l_assetsvtrx_rec.TAS_TYPE_MEANING,
1435 p_token2 => 'ASSET_NUMBER',
1436 p_token2_value => l_assetsvtrx_rec.asset_number,
1437 p_token3 => 'ASSET_BOOK',
1438 p_token3_value => l_assetsvtrx_rec.asset_book,
1439 p_token4 => 'FIELD',
1440 p_token4_value => 'Cost / Salvage Value');
1441
1442
1443 l_transaction_status := OKL_API.G_RET_STS_ERROR;
1444
1445
1446 ELSIF l_assetsvtrx_rec.salvage_value IS NULL OR l_assetsvtrx_rec.salvage_value = OKL_API.G_MISS_NUM THEN
1447 -- Can not process TRX_TYPE transaction for asset ASSET_NUMBER in Fixed Assets as FIELD is missing.
1448 OKC_API.set_message( p_app_name => 'OKL',
1449 p_msg_name => 'OKL_AM_DATA_REQUIRED',
1450 p_token1 => 'TRX_TYPE',
1451 p_token1_value => l_assetsvtrx_rec.TAS_TYPE_MEANING,
1452 p_token2 => 'ASSET_NUMBER',
1453 p_token2_value => l_assetsvtrx_rec.asset_number,
1454 p_token3 => 'ASSET_BOOK',
1455 p_token3_value => l_assetsvtrx_rec.asset_book,
1456 p_token4 => 'FIELD',
1457 p_token4_value => 'Salvage Value');
1458
1459
1460 l_transaction_status := OKC_API.G_RET_STS_ERROR;
1461 ELSE
1462
1463 OPEN l_quotes_csr(l_assetsvtrx_rec.kle_id);
1464 FETCH l_quotes_csr INTO l_asset_number;
1465 IF l_quotes_csr%FOUND THEN
1466 -- Can not process Salvage Value for asset ASSET_NUMBER as an accepted termination quote exists for this asset.
1467 OKL_API.set_message( p_app_name => 'OKL',
1468 p_msg_name => 'OKL_AM_SVW_NOT_PROCESSED',
1469 p_token1 => 'ASSET_NUMBER',
1470 p_token1_value => l_asset_number);
1471 l_transaction_status := OKC_API.G_RET_STS_ERROR;
1472
1473 ELSE
1474
1475
1476 -- Salvage Value Writedown transaction
1477 -- asset header information
1478
1479 -- SECHAWLA 06-MAY-04 3578894 : Moved the following statement inside the books loop
1480 -- to first initialize the l_asset_hdr_rec structure before every call to FA API
1481 -- and then assign asset id
1482 -- l_asset_hdr_rec.asset_id := l_assetsvtrx_rec.dnz_asset_id;
1483
1484 -- sgorantl 27-mar-06 5097643: changes made by kbbhavsa for bug 4717511 has been reversed
1485 l_sal_value := l_assetsvtrx_rec.salvage_value; -- sgorantl 27-mar-06 5097643
1486 -- kbbhavsa : added for bug 4717511 -- end
1487
1488 l_delta_salvage_value := l_sal_value - l_fa_salvage_value;
1489 -- SECHAWLA 06-MAY-04 3578894 : Moved the following statement inside the books loop
1490 -- to first initialize the l_asset_fin_rec_adj structure before every call to FA API
1491 -- and then assign SV
1492
1493 --l_asset_fin_rec_adj.salvage_value := l_delta_salvage_value;
1494
1495
1496 BEGIN
1497
1498 SAVEPOINT process_fsc;
1499 -- SECHAWLA Bug # 2701440 : store all the tax books of an asset in a table
1500 -- The corporate book is at the 0th position
1501 i := 0;
1502 l_books_tbl(i) := l_assetsvtrx_rec.asset_book ; -- this will be corporate book
1503 -- as SVW transactions are still being
1504 -- created onl in corp book and processed
1505 -- in all books
1506 /* SECHAWLA 28-DEC-05 4374620 : do not process SVW transactions in tax books
1507 FOR l_fabookcntrl_rec IN l_fabookcntrl_csr(l_assetsvtrx_rec.dnz_asset_id) LOOP
1508 i := i + 1;
1509 l_books_tbl(i) := l_fabookcntrl_rec.book_type_code;
1510 END LOOP;
1511 */
1512 -- SECHAWLA Bug # 2701440 : Loop thru all the records in the tax book table and
1513 -- call fa adjustments for each book
1514 IF l_books_tbl.COUNT > 0 THEN -- SECHAWLA 28-DEC-05 4374620 : l_books_tbl will have only 1 row for corp book
1515 i := l_books_tbl.FIRST;
1516 LOOP -- SECHAWLA 28-DEC-05 4374620 : this loop will get executed only once for the corp book
1517 -- SECHAWLA 06-MAY-04 3578894 : Initialize the structures inside the loop
1518 l_asset_fin_rec_adj := l_asset_fin_rec_empty_adj ;
1519 l_trans_rec := l_trans_empty_rec;
1520 l_asset_hdr_rec := l_asset_hdr_empty_rec;
1521
1522 -- SECHAWLA 06-MAY-04 3578894 : Assign values after initialization
1523 l_trans_rec.transaction_subtype := G_TRANS_SUBTYPE;
1524
1525 --SECHAWLA 29-DEC-05 3827148 : Added
1526 l_trans_rec.calling_interface := 'OKL:'||'SVW:'||l_assetsvtrx_rec.tas_type;
1527
1528 l_asset_hdr_rec.asset_id := l_assetsvtrx_rec.dnz_asset_id;
1529 --l_asset_fin_rec_adj.salvage_value := l_delta_salvage_value; --SECHAWLA 14-FEB-05 3950089
1530
1531 IF l_delta_salvage_value <> 0 THEN -- Mar 05, 2006 sgorantl - 115.35 4631549 : added this condition
1532
1533 --SECHAWLA 14-FEB-05 3950089 : begin
1534 OPEN l_booksalvagetype_csr(l_assetsvtrx_rec.dnz_asset_id, l_books_tbl(i));
1535 FETCH l_booksalvagetype_csr INTO l_salvage_type;
1536 CLOSE l_booksalvagetype_csr;
1537
1538 IF l_salvage_type = 'AMT' then
1539 l_asset_fin_rec_adj.salvage_value := l_delta_salvage_value;
1540 ELSIF l_salvage_type = 'PCT' THEN
1541 l_asset_fin_rec_adj.salvage_value := l_assetsvtrx_rec.salvage_value;
1542 END IF;
1543 l_asset_fin_rec_adj.salvage_type := 'AMT' ;
1544 --SECHAWLA 14-FEB-05 3950089 : end
1545 END IF;
1546
1547 -- rbruno Bug 6360770 Added code to set contract_id
1548 -- sechawla 14-dec-07 6690811 : Removed code to set contract id
1549 --l_asset_fin_rec_adj.contract_id := FND_API.G_MISS_NUM;
1550
1551 ------------
1552 l_asset_hdr_rec.book_type_code := l_books_tbl(i);
1553
1554 fa_adjustment_pub.do_adjustment(
1555 p_api_version => p_api_version,
1556 p_init_msg_list => OKC_API.G_FALSE,
1557 p_commit => FND_API.G_FALSE,
1558 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1559 p_calling_fn => NULL,
1560 x_return_status => l_return_status,
1561 x_msg_count => x_msg_count,
1562 x_msg_data => x_msg_data,
1563 px_trans_rec => l_trans_rec,
1564 px_asset_hdr_rec => l_asset_hdr_rec,
1565 p_asset_fin_rec_adj => l_asset_fin_rec_adj,
1566 x_asset_fin_rec_new => l_asset_fin_rec_new,
1567 x_asset_fin_mrc_tbl_new => l_asset_fin_mrc_tbl_new,
1568 px_inv_trans_rec => l_inv_trans_rec,
1569 px_inv_tbl => l_inv_tbl,
1570 p_asset_deprn_rec_adj => l_asset_deprn_rec_adj,
1571 x_asset_deprn_rec_new => l_asset_deprn_rec_new,
1572 x_asset_deprn_mrc_tbl_new => l_asset_deprn_mrc_tbl_new,
1573 p_group_reclass_options_rec => l_group_reclass_options_rec);
1574
1575
1576
1577 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1578 -- Error processing TRX_TYPE transaction for asset ASSET_NUMBER in book BOOK.
1579 -- Salvage Value Writedown transaction was not processed for this asset in Fixed Assets.
1580 OKC_API.set_message( p_app_name => 'OKL',
1581 p_msg_name => 'OKL_AM_FSC_TRANS_FAILED',
1582 p_token1 => 'TRX_TYPE',
1583 p_token1_value => l_assetsvtrx_rec.TAS_TYPE_MEANING,
1584 p_token2 => 'ASSET_NUMBER',
1585 p_token2_value => l_assetsvtrx_rec.asset_number,
1586 p_token3 => 'BOOK', -- SECHAWLA Bug # 2701440 : Added token2
1587 p_token3_value => l_books_tbl(i)); -- SECHAWLA Bug # 2701440 : Added token2 value);
1588
1589 l_transaction_status := l_return_status;
1590 RAISE l_asset_books_error;
1591 END IF;
1592 --akrangan populate sources cr changes start
1593 -- header record
1594 l_fxhv_rec.source_id := l_assetsvtrx_rec.id;
1595 l_fxhv_rec.source_table := 'OKL_TRX_ASSETS';
1596 l_fxhv_rec.khr_id := l_chr_id;
1597 l_fxhv_rec.try_id := l_assetsvtrx_rec.try_id;
1598 --line record
1599 l_fxlv_rec.source_id := l_assetsvtrx_rec.line_id;
1600 l_fxlv_rec.source_table := 'OKL_TXL_ASSETS_B';
1601 l_fxlv_rec.asset_id := l_assetsvtrx_rec.dnz_asset_id;
1602 l_fxlv_rec.kle_id := l_assetsvtrx_rec.kle_id;
1603 l_fxlv_rec.fa_transaction_id := l_trans_rec.transaction_header_id;
1604 l_fxlv_rec.asset_book_type_name := l_books_tbl(i);
1605 --call api
1606 okl_sla_acc_sources_pvt.populate_sources(p_api_version => p_api_version,
1607 p_init_msg_list => okc_api.g_false,
1608 p_fxhv_rec => l_fxhv_rec,
1609 p_fxlv_rec => l_fxlv_rec,
1610 x_return_status => x_return_status,
1611 x_msg_count => x_msg_count,
1612 x_msg_data => x_msg_data);
1613
1614 IF (x_return_status = okc_api.g_ret_sts_unexp_error)
1615 THEN
1616 RAISE okl_api.g_exception_unexpected_error;
1617 ELSIF (x_return_status = okc_api.g_ret_sts_error)
1618 THEN
1619 RAISE okl_api.g_exception_error;
1620 END IF;
1621 --akrangan populate sources cr ends
1622 EXIT WHEN (i = l_books_tbl.LAST);
1623 i := l_books_tbl.NEXT(i);
1624 END LOOP;
1625 END IF;
1626 EXCEPTION
1627 WHEN l_asset_books_error THEN
1628 ROLLBACK TO process_fsc;
1629 END;
1630
1631
1632 END IF; -- IF l_quotes_csr%FOUND THEN
1633 CLOSE l_quotes_csr;
1634
1635 --END IF; --IF l_assetsvtrx_rec.salvage_value IS NULL OR l_assetsvtrx_rec.salvage_value = OKL_APi.G_MISS_NUM THEN
1636
1637 END IF; --IF l_facostsv_csr%NOTFOUND THEN
1638 CLOSE l_facostsv_csr;
1639
1640 END IF; --IF l_assetsvtrx_rec.asset_book IS NULL...
1641
1642 -- END IF; --IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1643
1644 END IF; --IF l_dealtype_csr%NOTFOUND OR l_deal_type IS NULL OR l_deal_type = OKL_API.G_MISS_CHAR THEN
1645 CLOSE l_dealtype_csr;
1646 --------------
1647
1648 IF l_transaction_status = OKC_API.G_RET_STS_SUCCESS THEN
1649 -- update the staus (tsu_code) in okl_trx_assets_v
1650 lp_thpv_rec.id := l_assetsvtrx_rec.id;
1651 lp_thpv_rec.tsu_code := 'PROCESSED';
1652 OKL_TRX_ASSETS_PUB.update_trx_ass_h_def(
1653 p_api_version => p_api_version,
1654 p_init_msg_list => OKC_API.G_FALSE,
1655 x_return_status => l_return_status,
1656 x_msg_count => x_msg_count,
1657 x_msg_data => x_msg_data,
1658 p_thpv_rec => lp_thpv_rec,
1659 x_thpv_rec => lx_thpv_rec);
1660
1661 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1662 -- Transaction status STATUS could not be updated in OKL for asset ASSET_NUMBER
1663 OKC_API.set_message( p_app_name => 'OKL',
1664 p_msg_name => 'OKL_AM_TRXASSET_UPD_FAILED',
1665 p_token1 => 'TRX_TYPE',
1666 p_token1_value => l_assetsvtrx_rec.TAS_TYPE_MEANING,
1667 p_token2 => 'STATUS',
1668 p_token2_value => 'PROCESSED',
1669 p_token3 => 'ASSET_NUMBER',
1670 p_token3_value => l_assetsvtrx_rec.asset_number,
1671 p_token4 => 'ASSET_BOOK',
1672 p_token4_value => l_assetsvtrx_rec.asset_book);
1673 ELSE
1674 l_process_count := l_process_count + 1;
1675 -- Asset Details have been updated for asset ASSET_NUMBER in Fixed Assets.
1676 OKC_API.set_message( p_app_name => 'OKL',
1677 --p_msg_name => 'OKL_AM_FA_UPD_DONE',
1678 p_msg_name => 'OKL_AM_SV_FA_UPD_DONE', -- SECHAWLA 06-MAY-04 3578894: Changed msg
1679 p_token1 => 'ASSET_NUMBER',
1680 p_token1_value => l_assetsvtrx_rec.asset_number);
1681 END IF;
1682 ELSE -- FA changes have already been rolled back at this point
1683 -- update the staus (tsu_code) in okl_trx_assets_v
1684 lp_thpv_rec.id := l_assetsvtrx_rec.id;
1685 lp_thpv_rec.tsu_code := 'ERROR';
1686 OKL_TRX_ASSETS_PUB.update_trx_ass_h_def(
1687 p_api_version => p_api_version,
1688 p_init_msg_list => OKC_API.G_FALSE,
1689 x_return_status => l_return_status,
1690 x_msg_count => x_msg_count,
1691 x_msg_data => x_msg_data,
1692 p_thpv_rec => lp_thpv_rec,
1693 x_thpv_rec => lx_thpv_rec);
1694
1695 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1696 -- Transaction status STATUS could not be updated in OKL for asset ASSET_NUMBER
1697 OKC_API.set_message( p_app_name => 'OKL',
1698 p_msg_name => 'OKL_AM_TRXASSET_UPD_FAILED',
1699 p_token1 => 'TRX_TYPE',
1700 p_token1_value => l_assetsvtrx_rec.TAS_TYPE_MEANING,
1701 p_token2 => 'STATUS',
1702 p_token2_value => 'ERROR',
1703 p_token3 => 'ASSET_NUMBER',
1704 p_token3_value => l_assetsvtrx_rec.asset_number,
1705 p_token4 => 'ASSET_BOOK',
1706 p_token4_value => l_assetsvtrx_rec.asset_book);
1707
1708
1709 END IF;
1710 END IF;
1711
1712
1713
1714 END LOOP;
1715
1716 END; -- process SVW transactions end
1717
1718 END IF; -- IF p_salvage_writedown_yn = 'Y'
1719
1720 END LOOP;
1721
1722
1723 x_total_count := l_total_count;
1724 x_processed_count := l_process_count;
1725 x_error_count := l_total_count - l_process_count;
1726
1727 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1728
1729 EXCEPTION
1730
1731 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1732
1733 IF l_distinctasset_csr%ISOPEN THEN
1734 CLOSE l_distinctasset_csr;
1735 END IF;
1736
1737 IF l_facostsv_csr%ISOPEN THEN
1738 CLOSE l_facostsv_csr;
1739 END IF;
1740 IF l_assettrx_csr%ISOPEN THEN
1741 CLOSE l_assettrx_csr;
1742 END IF;
1743
1744 IF l_assetsvtrx_csr%ISOPEN THEN
1745 CLOSE l_assetsvtrx_csr;
1746 END IF;
1747
1748 IF l_quotes_csr%ISOPEN THEN
1749 CLOSE l_quotes_csr;
1750 END IF;
1751
1752 -- SECHAWLA Bug # 2701440 : close the new cursor
1753 /* -- SECHAWLA 28-DEC-05 4374620
1754 IF l_fabookcntrl_csr%ISOPEN THEN
1755 CLOSE l_fabookcntrl_csr;
1756 END IF;
1757 */
1758 --SECHAWLA Bug # 3439647 : Close the new cursor
1759 IF l_dealtype_csr%ISOPEN THEN
1760 CLOSE l_dealtype_csr;
1761 END IF;
1762
1763
1764 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1765 (
1766 l_api_name,
1767 G_PKG_NAME,
1768 'OKC_API.G_RET_STS_ERROR',
1769 x_msg_count,
1770 x_msg_data,
1771 '_PVT'
1772 );
1773 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1774
1775 IF l_distinctasset_csr%ISOPEN THEN
1776 CLOSE l_distinctasset_csr;
1777 END IF;
1778
1779 IF l_facostsv_csr%ISOPEN THEN
1780 CLOSE l_facostsv_csr;
1781 END IF;
1782 IF l_assettrx_csr%ISOPEN THEN
1783 CLOSE l_assettrx_csr;
1784 END IF;
1785
1786 IF l_assetsvtrx_csr%ISOPEN THEN
1787 CLOSE l_assetsvtrx_csr;
1788 END IF;
1789
1790 IF l_quotes_csr%ISOPEN THEN
1791 CLOSE l_quotes_csr;
1792 END IF;
1793
1794 -- SECHAWLA Bug # 2701440 : close the new cursor
1795 /* -- SECHAWLA 28-DEC-05 4374620
1796 IF l_fabookcntrl_csr%ISOPEN THEN
1797 CLOSE l_fabookcntrl_csr;
1798 END IF;
1799 */
1800
1801 --SECHAWLA Bug # 3439647 : Close the new cursor
1802 IF l_dealtype_csr%ISOPEN THEN
1803 CLOSE l_dealtype_csr;
1804 END IF;
1805
1806 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1807 (
1808 l_api_name,
1809 G_PKG_NAME,
1810 'OKC_API.G_RET_STS_UNEXP_ERROR',
1811 x_msg_count,
1812 x_msg_data,
1813 '_PVT'
1814 );
1815 WHEN OTHERS THEN
1816 IF l_distinctasset_csr%ISOPEN THEN
1817 CLOSE l_distinctasset_csr;
1818 END IF;
1819
1820 IF l_facostsv_csr%ISOPEN THEN
1821 CLOSE l_facostsv_csr;
1822 END IF;
1823 IF l_assettrx_csr%ISOPEN THEN
1824 CLOSE l_assettrx_csr;
1825 END IF;
1826
1827 IF l_assetsvtrx_csr%ISOPEN THEN
1828 CLOSE l_assetsvtrx_csr;
1829 END IF;
1830
1831 IF l_quotes_csr%ISOPEN THEN
1832 CLOSE l_quotes_csr;
1833 END IF;
1834
1835 -- SECHAWLA Bug # 2701440 : close the new cursor
1836 /* -- SECHAWLA 28-DEC-05 4374620
1837 IF l_fabookcntrl_csr%ISOPEN THEN
1838 CLOSE l_fabookcntrl_csr;
1839 END IF;
1840 */
1841
1842 --SECHAWLA Bug # 3439647 : Close the new cursor
1843 IF l_dealtype_csr%ISOPEN THEN
1844 CLOSE l_dealtype_csr;
1845 END IF;
1846
1847 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1848 (
1849 l_api_name,
1850 G_PKG_NAME,
1851 'OTHERS',
1852 x_msg_count,
1853 x_msg_data,
1854 '_PVT'
1855 );
1856
1857
1858 END process_transactions;
1859
1860
1861 END;