[Home] [Help]
PACKAGE BODY: APPS.OKL_AM_ASSET_RELOCATION_PVT
Source
1 PACKAGE BODY OKL_AM_ASSET_RELOCATION_PVT AS
2 /* $Header: OKLRAREB.pls 120.5 2005/12/29 22:05:58 sechawla noship $ */
3
4
5
6
7 SUBTYPE transaction_rec IS csi_datastructures_pub.transaction_rec;
8
9 ----------------------------------------------------------------------------------
10 -- Start of comments
11 --
12 -- Procedure Name : Relocate_Installed_item
13 -- Description : This procedure is used to relocate an asset in Installed Base
14 -- Business Rules :
15 -- Parameters : p_ialo_tbl
16 -- Version : 1.0
17 -- End of comments
18 ----------------------------------------------------------------------------------
19
20 PROCEDURE Relocate_Installed_Item
21 ( p_api_version IN NUMBER
22 , p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
23 , x_return_status OUT NOCOPY VARCHAR2
24 , x_msg_count OUT NOCOPY NUMBER
25 , x_msg_data OUT NOCOPY VARCHAR2
26 , p_ialo_tbl IN ialo_tbl_type) IS
27
28 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
29 l_loop_counter NUMBER :=0;
30
31 BEGIN
32
33 OKC_API.init_msg_list(p_init_msg_list);
34 IF (p_ialo_tbl.COUNT > 0) THEN
35
36 l_loop_counter := p_ialo_tbl.FIRST;
37 LOOP
38
39 IF p_ialo_tbl(l_loop_counter).p_instance_id IS NULL OR
40 p_ialo_tbl(l_loop_counter).p_instance_id = OKL_API.G_MISS_NUM THEN
41
42 x_return_status := OKL_API.G_RET_STS_ERROR;
43 -- asset_id is required
44 OKC_API.set_message( p_app_name => 'OKC',
45 p_msg_name => G_REQUIRED_VALUE,
46 p_token1 => G_COL_NAME_TOKEN,
47 p_token1_value => 'INSTANCE_ID');
48 RAISE OKC_API.G_EXCEPTION_ERROR;
49 END IF;
50
51 IF p_ialo_tbl(l_loop_counter).p_location_id IS NULL OR
52 p_ialo_tbl(l_loop_counter).p_location_id = OKL_API.G_MISS_NUM THEN
53
54 x_return_status := OKL_API.G_RET_STS_ERROR;
55 -- asset_id is required
56 OKC_API.set_message( p_app_name => 'OKC',
57 p_msg_name => G_REQUIRED_VALUE,
58 p_token1 => G_COL_NAME_TOKEN,
59 p_token1_value => 'LOCATION_ID');
60 RAISE OKC_API.G_EXCEPTION_ERROR;
61 END IF;
62
63 change_item_location (p_api_version => p_api_version,
64 p_init_msg_list => p_init_msg_list,
65 x_msg_count => x_msg_count,
66 x_msg_data => x_msg_data,
67 x_return_status => x_return_status,
68 p_instance_id => p_ialo_tbl(l_loop_counter).p_instance_id,
69 p_location_id => p_ialo_tbl(l_loop_counter).p_location_id,
70 p_install_location_id => p_ialo_tbl(l_loop_counter).p_install_location_id);
71
72 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
73 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
74 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
75 RAISE OKL_API.G_EXCEPTION_ERROR;
76 END IF;
77
78 EXIT WHEN (l_loop_counter = p_ialo_tbl.LAST);
79 l_loop_counter := p_ialo_tbl.NEXT(l_loop_counter );
80
81 END LOOP;
82
83 END IF;
84 x_return_status := l_return_status;
85
86 EXCEPTION
87
88 WHEN OTHERS THEN
89 Okl_api.set_message( p_app_name => g_app_name
90 , p_msg_name => g_unexpected_error
91 , p_token1 => g_sqlcode_token
92 , p_token1_value => SQLCODE
93 , p_token2 => g_sqlerrm_token
94 , p_token2_value => SQLERRM
95 ) ;
96 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
97
98 END Relocate_Installed_Item;
99
100
101
102 ----------------------------------------------------------------------------------
103 -- Start of comments
104 --
105 -- Procedure Name : Relocate_Fixed_Asset
106 -- Description : This procedure is used to relocate an asset in FA
107 -- Business Rules :
108 -- Parameters : p_falo_tbl
109 -- Version : 1.0
110 -- End of comments
111 ----------------------------------------------------------------------------------
112
113 PROCEDURE Relocate_Fixed_Asset
114 ( p_api_version IN NUMBER
115 , p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
116 , x_return_status OUT NOCOPY VARCHAR2
117 , x_msg_count OUT NOCOPY NUMBER
118 , x_msg_data OUT NOCOPY VARCHAR2
119 , p_falo_tbl IN falo_tbl_type) IS
120
121 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
122 l_asset_status VARCHAR2(100);
123
124 BEGIN
125
126 OKC_API.init_msg_list(p_init_msg_list);
127
128 Change_FA_Location ( p_api_version => p_api_version,
129 p_init_msg_list => p_init_msg_list,
130 x_msg_count => x_msg_count,
131 x_msg_data => x_msg_data,
132 x_return_status => x_return_status,
133 p_assets_tbl => p_falo_tbl );
134
135
136 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
137 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
138 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
139 RAISE OKL_API.G_EXCEPTION_ERROR;
140 END IF;
141
142 x_return_status := l_return_status;
143
144 EXCEPTION
145
146 WHEN OTHERS THEN
147 Okl_api.set_message( p_app_name => g_app_name
148 , p_msg_name => g_unexpected_error
149 , p_token1 => g_sqlcode_token
150 , p_token1_value => SQLCODE
151 , p_token2 => g_sqlerrm_token
152 , p_token2_value => SQLERRM
153 ) ;
154 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
155
156 END Relocate_Fixed_Asset;
157
158 ---------------------------------------------------------------------------
159 -- Start of comments
160 --
161 -- Procedure Name : Change_FA_Location
162 -- Description : This procedure is used to relocate an asset in FA
163 -- Business Rules :
164 -- Parameters : p_assets_tbl
165 -- Version : 1.0
166 -- History : sechawla 19-dec-2005 4895439 : Raise an exception and
167 -- set return status to 'E' if location change can not be
168 -- performed
169 -- End of comments
170 ---------------------------------------------------------------------------
171
172 PROCEDURE Change_FA_Location(
173 p_api_version IN NUMBER,
174 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
175 x_return_status OUT NOCOPY VARCHAR2,
176 x_msg_count OUT NOCOPY NUMBER,
177 x_msg_data OUT NOCOPY VARCHAR2,
178 p_assets_tbl IN falo_tbl_type ) IS
179
180 SUBTYPE thpv_rec_type IS okl_trx_assets_pub.thpv_rec_type;
181 SUBTYPE tlpv_rec_type IS okl_txl_assets_pub.tlpv_rec_type;
182
183 l_trans_rec FA_API_TYPES.trans_rec_type;
184 l_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
185 l_asset_dist_tbl FA_API_TYPES.asset_dist_tbl_type;
186
187 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
188 l_api_name CONSTANT VARCHAR2(30) := 'Change_FA_Location';
189
190 l_original_cost okl_txl_assets_v.original_cost%TYPE;
191
192
193
194 l_tsu_code VARCHAR2(30);
195
196 l_try_id okl_trx_types_v.id%TYPE;
197 lp_thpv_rec thpv_rec_type;
198 lx_thpv_rec thpv_rec_type;
199 lp_tlpv_rec tlpv_rec_type;
200 lx_tlpv_rec tlpv_rec_type;
201 i NUMBER;
202 l_ret VARCHAR2(1);
203 l_count_source NUMBER := 0;
204 l_count_tax NUMBER := 0;
205 l_api_version CONSTANT NUMBER := 1;
206 l_sysdate DATE;
207
208
209
210 -- This cursor is used to validate the new location Id
211 CURSOR l_location_csr(p_location_id NUMBER, p_sysdate DATE) IS
212 SELECT 'x'
213 FROM fa_locations
214 WHERE location_id = p_location_id
215 AND enabled_flag = 'Y'
216 AND p_sysdate BETWEEN NVL(START_DATE_ACTIVE,p_sysdate) AND NVL(END_DATE_ACTIVE,p_sysdate);
217
218 -- This cursor is used to get all the tax books for an asset
219 CURSOR l_taxbooks_csr(p_source VARCHAR2) IS
220 SELECT book_type_code
221 FROM fa_book_controls
222 WHERE distribution_source_book = p_source
223 AND book_class = 'TAX'
224 AND date_ineffective IS NULL;
225
226 -- This cursor is used to check if the depreciation has alraedy been run for an asset in any of the books
227 CURSOR l_deprnbookcnt_csr(p_book_type_code VARCHAR2,p_asset_id NUMBER) IS
228 SELECT count(*)
229 FROM fa_deprn_summary ds, fa_book_controls bc
230 WHERE bc.book_type_code = p_book_type_code
231 AND ds.book_type_code = bc.book_type_code
232 AND ds.period_counter = bc.last_period_counter + 1
233 AND ds.asset_id = p_asset_id
234 AND ds.deprn_source_code = 'DEPRN';
235
236
237
238 BEGIN
239
240 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
241 G_PKG_NAME,
242 p_init_msg_list,
243 l_api_version,
244 p_api_version,
245 '_PVT',
246 x_return_status);
247
248 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
249 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
250 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
251 RAISE OKC_API.G_EXCEPTION_ERROR;
252 END IF;
253
254 SELECT SYSDATE INTO l_sysdate FROM DUAL;
255
256 okl_am_util_pvt.get_transaction_id(p_try_name => 'Asset Relocation',
257 x_return_status => x_return_status,
258 x_try_id => l_try_id);
259 IF x_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
260 -- Unable to find a transaction type for this transaction .
261 OKL_API.set_message(p_app_name => 'OKL',
262 p_msg_name => 'OKL_AM_NO_TRX_TYPE_FOUND',
263 p_token1 => 'TRY_NAME',
264 p_token1_value => 'Asset Relocation');
265 RAISE OKC_API.G_EXCEPTION_ERROR;
266 END IF;
267
268
269
270 IF p_assets_tbl.COUNT > 0 THEN
271 i := p_assets_tbl.FIRST;
272 -- loop thru all the records in the input table, validate the data and then call FA transfer
273 -- API to relocate the asset
274 LOOP
275 IF p_assets_tbl(i).p_cle_id IS NULL OR p_assets_tbl(i).p_cle_id = okl_api.G_MISS_NUM THEN
276
277 x_return_status := OKL_API.G_RET_STS_ERROR;
278 -- cle_id is required
279 OKC_API.set_message( p_app_name => 'OKC',
280 p_msg_name => G_REQUIRED_VALUE,
281 p_token1 => G_COL_NAME_TOKEN,
282 p_token1_value => 'CLE_ID');
283 RAISE OKC_API.G_EXCEPTION_ERROR;
284 END IF;
285
286 IF p_assets_tbl(i).p_asset_id IS NULL OR p_assets_tbl(i).p_asset_id = okl_api.G_MISS_NUM THEN
287
288 x_return_status := OKL_API.G_RET_STS_ERROR;
289 -- asset_id is required
290 OKC_API.set_message( p_app_name => 'OKC',
291 p_msg_name => G_REQUIRED_VALUE,
292 p_token1 => G_COL_NAME_TOKEN,
293 p_token1_value => 'ASSET_ID');
294 RAISE OKC_API.G_EXCEPTION_ERROR;
295 END IF;
296
297 IF p_assets_tbl(i).p_asset_number IS NULL THEN
298
299 x_return_status := OKL_API.G_RET_STS_ERROR;
300 -- asset_number is required
301 OKC_API.set_message( p_app_name => 'OKC',
302 p_msg_name => G_REQUIRED_VALUE,
303 p_token1 => G_COL_NAME_TOKEN,
304 p_token1_value => 'ASSET_NUMBER');
305 RAISE OKC_API.G_EXCEPTION_ERROR;
306 END IF;
307
308 IF p_assets_tbl(i).p_corporate_book IS NULL THEN
309
310 x_return_status := OKL_API.G_RET_STS_ERROR;
311 -- corporate_book is required
312 OKC_API.set_message( p_app_name => 'OKC',
313 p_msg_name => G_REQUIRED_VALUE,
314 p_token1 => G_COL_NAME_TOKEN,
315 p_token1_value => 'CORPORATE_BOOK');
316 RAISE OKC_API.G_EXCEPTION_ERROR;
317 END IF;
318
319 IF p_assets_tbl(i).p_current_units IS NULL OR p_assets_tbl(i).p_current_units = okl_api.G_MISS_NUM THEN
320
321 x_return_status := OKL_API.G_RET_STS_ERROR;
322 -- current_units is required
323 OKC_API.set_message( p_app_name => 'OKC',
324 p_msg_name => G_REQUIRED_VALUE,
325 p_token1 => G_COL_NAME_TOKEN,
326 p_token1_value => 'CURRENT_UNITS');
327 RAISE OKC_API.G_EXCEPTION_ERROR;
328 END IF;
329
330 IF p_assets_tbl(i).p_distribution_id IS NULL OR p_assets_tbl(i).p_distribution_id = okl_api.G_MISS_NUM THEN
331
332 x_return_status := OKL_API.G_RET_STS_ERROR;
333 -- Distribution Id is required
334 OKC_API.set_message( p_app_name => 'OKC',
335 p_msg_name => G_REQUIRED_VALUE,
336 p_token1 => G_COL_NAME_TOKEN,
337 p_token1_value => 'DISTRIBUTION_ID');
338 RAISE OKC_API.G_EXCEPTION_ERROR;
339 END IF;
340
341 IF p_assets_tbl(i).p_units_assigned IS NULL OR p_assets_tbl(i).p_units_assigned = okl_api.G_MISS_NUM THEN
342
343 x_return_status := OKL_API.G_RET_STS_ERROR;
344 -- Units Assigned is required
345 OKC_API.set_message( p_app_name => 'OKC',
346 p_msg_name => G_REQUIRED_VALUE,
347 p_token1 => G_COL_NAME_TOKEN,
348 p_token1_value => 'UNITS_ASSIGNED');
352 IF p_assets_tbl(i).p_code_combination_id IS NULL OR p_assets_tbl(i).p_code_combination_id = okl_api.G_MISS_NUM THEN
349 RAISE OKC_API.G_EXCEPTION_ERROR;
350 END IF;
351
353
354 x_return_status := OKL_API.G_RET_STS_ERROR;
355 -- Code Combination Id is required
356 OKC_API.set_message( p_app_name => 'OKC',
357 p_msg_name => G_REQUIRED_VALUE,
358 p_token1 => G_COL_NAME_TOKEN,
359 p_token1_value => 'CODE_COMBINATION_ID');
360 RAISE OKC_API.G_EXCEPTION_ERROR;
361 END IF;
362
363 IF p_assets_tbl(i).p_old_location_id IS NULL OR p_assets_tbl(i).p_old_location_id = okl_api.G_MISS_NUM THEN
364
365 x_return_status := OKL_API.G_RET_STS_ERROR;
366 -- Old Location Id is required
367 OKC_API.set_message( p_app_name => 'OKC',
368 p_msg_name => G_REQUIRED_VALUE,
369 p_token1 => G_COL_NAME_TOKEN,
370 p_token1_value => 'OLD_LOCATION_ID');
371 RAISE OKC_API.G_EXCEPTION_ERROR;
372 END IF;
373
374 IF p_assets_tbl(i).p_new_location_id IS NULL OR p_assets_tbl(i).p_new_location_id = okl_api.G_MISS_NUM THEN
375 x_return_status := OKL_API.G_RET_STS_ERROR;
376 -- New Location Id is required
377 OKC_API.set_message( p_app_name => 'OKC',
378 p_msg_name => G_REQUIRED_VALUE,
379 p_token1 => G_COL_NAME_TOKEN,
380 p_token1_value => 'NEW_LOCATION_ID');
381 RAISE OKC_API.G_EXCEPTION_ERROR;
382 END IF;
383
384 IF p_assets_tbl(i).p_new_location_id = p_assets_tbl(i).p_old_location_id THEN
385 x_return_status := OKL_API.G_RET_STS_ERROR;
386 -- Can not create identical distributions. New Location Id should be different from the
387 -- Old Distribution Id
388 OKL_API.set_message( p_app_name => 'OKL',
389 p_msg_name => 'OKL_AM_IDENTICAL_DIST_LINES');
390 RAISE OKL_API.G_EXCEPTION_ERROR;
391 END IF;
392
393 OPEN l_location_csr(p_assets_tbl(i).p_new_location_id, l_sysdate);
394 FETCH l_location_csr INTO l_ret;
395
396 IF l_location_csr%NOTFOUND THEN
397 x_return_status := OKL_API.G_RET_STS_ERROR;
398 -- New Location Id is Invalid
399 OKC_API.set_message( p_app_name => 'OKC',
400 p_msg_name => G_INVALID_VALUE,
401 p_token1 => G_COL_NAME_TOKEN,
402 p_token1_value => 'NEW_LOCATION_ID');
403 RAISE OKC_API.G_EXCEPTION_ERROR;
404 END IF;
405 CLOSE l_location_csr;
406
407 -- check if depreciation has already been run for this asset in the distribution_source_book
408 OPEN l_deprnbookcnt_csr(p_assets_tbl(i).p_corporate_book,p_assets_tbl(i).p_asset_id);
409 FETCH l_deprnbookcnt_csr INTO l_count_source;
410 CLOSE l_deprnbookcnt_csr;
411
412
413
414 -- check if depreciation has already been run for this asset in any of the tax books
415 FOR l_taxbooks_rec IN l_taxbooks_csr(p_assets_tbl(i).p_corporate_book) LOOP
416
417 OPEN l_deprnbookcnt_csr(l_taxbooks_rec.book_type_code,p_assets_tbl(i).p_asset_id);
418 FETCH l_deprnbookcnt_csr INTO l_count_tax;
419 CLOSE l_deprnbookcnt_csr;
420
421 IF l_count_tax > 0 then
422 EXIT;
423 END IF;
424
425 END LOOP;
426
427
428 IF l_count_source > 0 OR l_count_tax > 0 THEN
429 -- Store the transaction in 'Error' if depreciation has already been run for the asset in
430 -- corporate book or any of the tax books
431
432 -- Depreciation has already been run for the asset ASSET_NUMBER in either corporate book or
433 -- one or more tax books. Can not perform relocation.
434
435 x_return_status := OKL_API.G_RET_STS_ERROR; -- sechawla 19-dec-2005 4895439 : added
436
437 OKL_API.set_message( p_app_name => 'OKL',
438 p_msg_name => 'OKL_AM_DEPRN_RAN_ALREADY',
439 p_token1 => 'ASSET_NUMBER',
440 p_token1_value => p_assets_tbl(i).p_asset_number);
441
442 RAISE OKC_API.G_EXCEPTION_ERROR; -- sechawla 19-dec-2005 4895439 : added
443
444 -- The following code will be commented out till we add an additional column in okl_txl_assets_v
445 -- to hold distribution Id
446 /*
447 l_tsu_code := 'ERROR';
448
449 -- create transaction header
450 lp_thpv_rec.tas_type := 'ALG';
451 lp_thpv_rec.tsu_code := l_tsu_code;
452 lp_thpv_rec.try_id := l_try_id;
456 x_return_status => x_return_status,
453 lp_thpv_rec.date_trans_occurred := l_sysdate;
454 OKL_TRX_ASSETS_PUB.create_trx_ass_h_def( p_api_version => p_api_version,
455 p_init_msg_list => p_init_msg_list,
457 x_msg_count => x_msg_count,
458 x_msg_data => x_msg_data,
459 p_thpv_rec => lp_thpv_rec,
460 x_thpv_rec => lx_thpv_rec);
461
462 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
463 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
464 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
465 RAISE OKC_API.G_EXCEPTION_ERROR;
466 END IF;
467
468 -- Create transaction Line
469 lp_tlpv_rec.tas_id := lx_thpv_rec.id; -- FK
470 lp_tlpv_rec.ilo_id := p_assets_tbl(i).p_new_location_id;
471 lp_tlpv_rec.ilo_id_old := p_assets_tbl(i).p_old_location_id;
472 lp_tlpv_rec.kle_id := p_assets_tbl(i).p_cle_id;
473 lp_tlpv_rec.line_number := 1;
474 lp_tlpv_rec.tal_type := 'AGL';
475 lp_tlpv_rec.asset_number := p_assets_tbl(i).p_asset_number;
476 lp_tlpv_rec.corporate_book := p_assets_tbl(i).p_corporate_book;
477 -- lp_tlpv_rec.original_cost := l_original_cost;
478 lp_tlpv_rec.current_units := p_assets_tbl(i).p_current_units;
479 lp_tlpv_rec.dnz_asset_id := p_assets_tbl(i).p_asset_id;
480
481
482
483 OKL_TXL_ASSETS_PUB.create_txl_asset_def(p_api_version => p_api_version,
484 p_init_msg_list => p_init_msg_list,
485 x_return_status => x_return_status,
486 x_msg_count => x_msg_count,
487 x_msg_data => x_msg_data,
488 p_tlpv_rec => lp_tlpv_rec,
489 x_tlpv_rec => lx_tlpv_rec);
490
491 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
492 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
493 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
494 RAISE OKC_API.G_EXCEPTION_ERROR;
495 END IF;
496 */
497
498
499 ELSE -- deprn has not been run for this asset in any of the books
500 -- This piece of code needs to stay commented out till FA transfer API gets fixed
501
502 -- transaction information
503 l_trans_rec.transaction_type_code := 'TRANSFER';
504 l_trans_rec.transaction_date_entered := NULL;
505
506 --SECHAWLA 29-DEC-05 3827148 : added
507 l_trans_rec.calling_interface := 'OKL:'||'Asset Relocation:';
508
509
510 -- header information
511 l_asset_hdr_rec.asset_id := p_assets_tbl(i).p_asset_id;
512 l_asset_hdr_rec.book_type_code := p_assets_tbl(i).p_corporate_book;
513 -- l_asset_hdr_rec.period_of_addition := null;
514
515
516
517 l_asset_dist_tbl.DELETE;
518
519 -- source distribution line
520 l_asset_dist_tbl(1).distribution_id := p_assets_tbl(i).p_distribution_id;
521 l_asset_dist_tbl(1).transaction_units := -(p_assets_tbl(i).p_units_assigned);
522
523 -- destination distribution line
524 l_asset_dist_tbl(2).transaction_units := p_assets_tbl(i).p_units_assigned;
525 l_asset_dist_tbl(2).assigned_to := p_assets_tbl(i).p_assigned_to;
526 l_asset_dist_tbl(2).expense_ccid := p_assets_tbl(i).p_code_combination_id;
527 l_asset_dist_tbl(2).location_ccid := p_assets_tbl(i).p_new_location_id;
528
529 FA_TRANSFER_PUB.do_transfer( p_api_version => p_api_version,
530 p_init_msg_list => p_init_msg_list,
531 p_commit => FND_API.G_FALSE,
532 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
533 p_calling_fn => NULL,
534 x_return_status => x_return_status,
535 x_msg_count => x_msg_count,
536 x_msg_data => x_msg_data,
537 px_trans_rec => l_trans_rec,
538 px_asset_hdr_rec => l_asset_hdr_rec,
539 px_asset_dist_tbl => l_asset_dist_tbl);
540
541
542 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
543 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
544 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
545 RAISE OKC_API.G_EXCEPTION_ERROR;
549
546 END IF;
547
548
550
551 -- This code will be commented out until we add an additional column in okl_txl_assets_v
552 -- to hold distribution Id
553 /*
554 -- store the transaction in OKL with status = 'PROCESSED'
555 l_tsu_code := 'PROCESSED';
556
557 --Currently we store only one transaction on OKL for both multiple and single distribution assets
558
559 -- create transaction header
560 lp_thpv_rec.tas_type := 'ALG';
561 lp_thpv_rec.tsu_code := l_tsu_code;
562 lp_thpv_rec.try_id := l_try_id;
563 lp_thpv_rec.date_trans_occurred := l_sysdate;
564 OKL_TRX_ASSETS_PUB.create_trx_ass_h_def( p_api_version => p_api_version,
565 p_init_msg_list => p_init_msg_list,
566 x_return_status => x_return_status,
567 x_msg_count => x_msg_count,
568 x_msg_data => x_msg_data,
569 p_thpv_rec => lp_thpv_rec,
570 x_thpv_rec => lx_thpv_rec);
571
572 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
573 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
574 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
575 RAISE OKC_API.G_EXCEPTION_ERROR;
576 END IF;
577
578 -- Create transaction Line
579 lp_tlpv_rec.tas_id := lx_thpv_rec.id; -- FK
580 lp_tlpv_rec.ilo_id := p_assets_tbl(i).p_new_location_id;
581 lp_tlpv_rec.ilo_id_old := p_assets_tbl(i).p_old_location_id;
582 lp_tlpv_rec.kle_id := p_assets_tbl(i).p_cle_id;
583 lp_tlpv_rec.line_number := 1;
584 lp_tlpv_rec.tal_type := 'AGL';
585 lp_tlpv_rec.asset_number := p_assets_tbl(i).p_asset_number;
586 lp_tlpv_rec.corporate_book := p_assets_tbl(i).p_corporate_book;
587 -- lp_tlpv_rec.original_cost := l_original_cost;
588 lp_tlpv_rec.current_units := p_assets_tbl(i).p_current_units;
589 lp_tlpv_rec.dnz_asset_id := p_assets_tbl(i).p_asset_id;
590 --lp_tlpv_rec.dnz_khr_id := p_contract_id;
591
592
593 OKL_TXL_ASSETS_PUB.create_txl_asset_def(p_api_version => p_api_version,
594 p_init_msg_list => p_init_msg_list,
595 x_return_status => x_return_status,
596 x_msg_count => x_msg_count,
597 x_msg_data => x_msg_data,
598 p_tlpv_rec => lp_tlpv_rec,
599 x_tlpv_rec => lx_tlpv_rec);
600
601 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
602 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
603 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
604 RAISE OKC_API.G_EXCEPTION_ERROR;
605 END IF;
606 */
607
608 END IF;
609
610
611 EXIT WHEN (i = p_assets_tbl.LAST);
612 i := p_assets_tbl.NEXT(i);
613 END LOOP;
614 ELSE
615 -- There were no Asset Relocation transactions to process.
616 OKL_API.set_message(p_app_name => 'OKL',
617 p_msg_name => 'OKL_AM_NO_RELOC_TRX');
618 END IF; -- if assets_tbl.count > 0
619
620 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
621 EXCEPTION
622 WHEN OKC_API.G_EXCEPTION_ERROR THEN
623
624
625 IF l_location_csr%ISOPEN THEN
626 CLOSE l_location_csr;
627 END IF;
628 IF l_taxbooks_csr%ISOPEN THEN
629 CLOSE l_taxbooks_csr;
630 END IF;
631 IF l_deprnbookcnt_csr%ISOPEN THEN
632 CLOSE l_deprnbookcnt_csr;
633 END IF;
634
635 x_return_status := OKC_API.HANDLE_EXCEPTIONS
636 (
637 l_api_name,
638 G_PKG_NAME,
639 'OKC_API.G_RET_STS_ERROR',
640 x_msg_count,
641 x_msg_data,
642 '_PVT'
643 );
644 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
645
646 IF l_location_csr%ISOPEN THEN
647 CLOSE l_location_csr;
648 END IF;
649 IF l_taxbooks_csr%ISOPEN THEN
650 CLOSE l_taxbooks_csr;
651 END IF;
652 IF l_deprnbookcnt_csr%ISOPEN THEN
653 CLOSE l_deprnbookcnt_csr;
654 END IF;
655 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
656 (
660 x_msg_count,
657 l_api_name,
658 G_PKG_NAME,
659 'OKC_API.G_RET_STS_UNEXP_ERROR',
661 x_msg_data,
662 '_PVT'
663 );
664 WHEN OTHERS THEN
665
666 IF l_location_csr%ISOPEN THEN
667 CLOSE l_location_csr;
668 END IF;
669 IF l_taxbooks_csr%ISOPEN THEN
670 CLOSE l_taxbooks_csr;
671 END IF;
672 IF l_deprnbookcnt_csr%ISOPEN THEN
673 CLOSE l_deprnbookcnt_csr;
674 END IF;
675 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
676 (
677 l_api_name,
678 G_PKG_NAME,
679 'OTHERS',
680 x_msg_count,
681 x_msg_data,
682 '_PVT'
683 );
684
685 END Change_FA_Location ;
686
687
688 ---------------------------------------------------------------------------
689 -- Start of comments
690 --
691 -- Procedure Name : initialize_txn_rec
692 -- Description : This procedure is used to initialize a transaction record
693 -- Business Rules :
694 -- Parameters : px_txn_rec
695 -- Version : 1.0
696 -- End of comments
697 ---------------------------------------------------------------------------
698 PROCEDURE initialize_txn_rec (
699 px_txn_rec IN OUT NOCOPY transaction_rec) IS
700 BEGIN
701
702 px_txn_rec.transaction_id := NULL;
703 px_txn_rec.transaction_date := SYSDATE;
704 px_txn_rec.source_transaction_date := SYSDATE;
705 px_txn_rec.transaction_type_id := 1;
706 px_txn_rec.txn_sub_type_id := NULL;
707 px_txn_rec.source_group_ref_id := NULL;
708 px_txn_rec.source_group_ref := '';
709 px_txn_rec.source_header_ref_id := NULL;
710 px_txn_rec.source_header_ref := '';
711 px_txn_rec.source_line_ref_id := NULL;
712 px_txn_rec.source_line_ref := '';
713 px_txn_rec.source_dist_ref_id1 := NULL;
714 px_txn_rec.source_dist_ref_id2 := NULL;
715 px_txn_rec.inv_material_transaction_id := NULL;
716 px_txn_rec.transaction_quantity := NULL;
717 px_txn_rec.transaction_uom_code := '';
718 px_txn_rec.transacted_by := NULL;
719 px_txn_rec.transaction_status_code := '';
720 px_txn_rec.transaction_action_code := '';
721 px_txn_rec.message_id := NULL;
722 px_txn_rec.context := '';
723 px_txn_rec.attribute1 := '';
724 px_txn_rec.attribute2 := '';
725 px_txn_rec.attribute3 := '';
726 px_txn_rec.attribute4 := '';
727 px_txn_rec.attribute5 := '';
728 px_txn_rec.attribute6 := '';
729 px_txn_rec.attribute7 := '';
730 px_txn_rec.attribute8 := '';
731 px_txn_rec.attribute9 := '';
732 px_txn_rec.attribute10 := '';
733 px_txn_rec.attribute11 := '';
734 px_txn_rec.attribute12 := '';
735 px_txn_rec.attribute13 := '';
736 px_txn_rec.attribute14 := '';
737 px_txn_rec.attribute15 := '';
738 px_txn_rec.object_version_number := NULL;
739 px_txn_rec.split_reason_code := '';
740
741 END initialize_txn_rec;
742
743 ---------------------------------------------------------------------------
744 -- Start of comments
745 --
746 -- Procedure Name : Change_Item_Location
747 -- Description : This procedure is used to update the item in Installed Base
748 -- Business Rules :
749 -- Parameters : p_instance_id, p_location_id, p_install_location_id
750 -- Version : 1.0
751 -- End of comments
752 ---------------------------------------------------------------------------
753
754
755
756 PROCEDURE Change_Item_Location (
757 p_api_version IN NUMBER,
758 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
759 x_msg_count OUT NOCOPY NUMBER,
760 x_msg_data OUT NOCOPY VARCHAR2,
761 x_return_status OUT NOCOPY VARCHAR2,
762 p_instance_id IN NUMBER,
763 p_location_id IN NUMBER,
764 p_install_location_id IN NUMBER DEFAULT NULL) IS
765
766 SUBTYPE instance_rec IS csi_datastructures_pub.instance_rec;
767 SUBTYPE id_tbl IS csi_datastructures_pub.id_tbl;
768 SUBTYPE instance_query_rec IS csi_datastructures_pub.instance_query_rec;
769 SUBTYPE party_query_rec IS csi_datastructures_pub.party_query_rec;
770 SUBTYPE party_account_query_rec IS csi_datastructures_pub.party_account_query_rec;
771 SUBTYPE instance_header_tbl IS csi_datastructures_pub.instance_header_tbl;
772 SUBTYPE extend_attrib_values_tbl IS csi_datastructures_pub.extend_attrib_values_tbl;
773 SUBTYPE party_tbl IS csi_datastructures_pub.party_tbl;
774 SUBTYPE party_account_tbl IS csi_datastructures_pub.party_account_tbl;
775 SUBTYPE pricing_attribs_tbl IS csi_datastructures_pub.pricing_attribs_tbl;
776 SUBTYPE organization_units_tbl IS csi_datastructures_pub.organization_units_tbl;
777 SUBTYPE instance_asset_tbl IS csi_datastructures_pub.instance_asset_tbl;
778
779 -- Get Item Instance parameters
780 l_instance_query_rec instance_query_rec;
781 l_party_query_rec party_query_rec;
782 l_account_query_rec party_account_query_rec;
783 l_instance_header_tbl instance_header_tbl;
784
788 l_instance_id_lst id_tbl;
785 -- Update Item Instance generic parameters
786 l_instance_rec instance_rec;
787 l_txn_rec transaction_rec;
789
790 -- Update Item Instance specific parameters
791 l_ext_attrib_values_tbl extend_attrib_values_tbl;
792 l_party_tbl party_tbl;
793 l_account_tbl party_account_tbl;
794 l_pricing_attrib_tbl pricing_attribs_tbl;
795 l_org_assignments_tbl organization_units_tbl;
796 l_asset_assignment_tbl instance_asset_tbl;
797
798 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
799 l_overall_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
800
801 l_api_name CONSTANT VARCHAR2(30) := 'change_item_location';
802 l_api_version CONSTANT NUMBER := G_API_VERSION;
803 l_msg_count NUMBER := FND_API.G_MISS_NUM;
804 l_msg_data VARCHAR2(2000);
805
806 BEGIN
807
808 -- ***************************************************************
809 -- Check API version, initialize message list and create savepoint
810 -- ***************************************************************
811
812 l_return_status := OKL_API.START_ACTIVITY (
813 l_api_name,
814 G_PKG_NAME,
815 p_init_msg_list,
816 l_api_version,
817 p_api_version,
818 '_PVT',
819 x_return_status);
820
821 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
822 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
823 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
824 RAISE OKL_API.G_EXCEPTION_ERROR;
825 END IF;
826
827 -- ************************
828 -- Get Item Instance record
829 -- ************************
830
831 l_instance_query_rec.instance_id := p_instance_id;
832
833 csi_item_instance_pub.get_item_instances (
834 p_api_version => l_api_version,
835 p_commit => FND_API.G_FALSE,
836 p_init_msg_list => FND_API.G_FALSE,
837 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
838 p_instance_query_rec => l_instance_query_rec,
839 p_party_query_rec => l_party_query_rec,
840 p_account_query_rec => l_account_query_rec,
841 p_transaction_id => NULL,
842 p_resolve_id_columns => FND_API.G_FALSE,
843 p_active_instance_only => FND_API.G_TRUE,
844 x_instance_header_tbl => l_instance_header_tbl,
845 x_return_status => l_return_status,
846 x_msg_count => l_msg_count,
847 x_msg_data => l_msg_data);
848
849 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
850 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
851 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
852 RAISE OKL_API.G_EXCEPTION_ERROR;
853 ELSIF (NVL (l_instance_header_tbl.COUNT, 0) <> 1) THEN
854 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
855 END IF;
856
857 -- *************************************
858 -- Initialize parameters to be passed in
859 -- *************************************
860
861 l_instance_rec.instance_id :=
862 l_instance_header_tbl(1).instance_id;
863 l_instance_rec.object_version_number :=
864 l_instance_header_tbl(1).object_version_number;
865 l_instance_rec.quantity :=
866 l_instance_header_tbl(1).quantity;
867
868 IF (p_location_id IS NULL)
869 OR (p_location_id = OKL_API.G_MISS_NUM) THEN
870 RAISE OKL_API.G_EXCEPTION_ERROR;
871 ELSE
872 -- GKADARKA - fixes for bug 3569441
873 IF (l_instance_header_tbl(1).location_type_code = 'HZ_LOCATIONS') THEN
874 l_instance_rec.location_id := p_location_id;
875 ELSIF l_instance_header_tbl(1).location_type_code = 'HZ_PARTY_SITES' THEN
876 l_instance_rec.location_id := p_install_location_id;
877 END IF;
878 -- GKADARKA - fixes for bug 3569441
879
880 --l_instance_rec.location_id := p_location_id;
881 END IF;
882
883 IF (p_install_location_id IS NULL)
884 OR (p_install_location_id = OKL_API.G_MISS_NUM) THEN
885 NULL;
886 ELSE
887 --- GKADARKA - fixes for bug 3569441 - Start
888
889 IF (l_instance_header_tbl(1).install_location_type_code = 'HZ_LOCATIONS') THEN
890 l_instance_rec.install_location_id := p_location_id;
891 ELSE
892 l_instance_rec.install_location_id := p_install_location_id;
893 END IF;
894 --- GKADARKA - fixes for bug 3569441 -End
895 END IF;
896
897 initialize_txn_rec (l_txn_rec);
898
899 -- **************************************
900 -- Call Installed Base API to update item
901 -- **************************************
902
903 csi_item_instance_pub.update_item_instance (
904 p_api_version => l_api_version,
905 p_commit => FND_API.G_FALSE,
906 p_init_msg_list => FND_API.G_FALSE,
907 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
908 p_instance_rec => l_instance_rec,
909 p_ext_attrib_values_tbl => l_ext_attrib_values_tbl,
910 p_party_tbl => l_party_tbl,
911 p_account_tbl => l_account_tbl,
912 p_pricing_attrib_tbl => l_pricing_attrib_tbl,
913 p_org_assignments_tbl => l_org_assignments_tbl,
914 p_asset_assignment_tbl => l_asset_assignment_tbl,
915 p_txn_rec => l_txn_rec,
916 x_instance_id_lst => l_instance_id_lst,
917 x_return_status => l_return_status,
918 x_msg_count => l_msg_count,
919 x_msg_data => l_msg_data);
920
921 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
922 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
923 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
924 RAISE OKL_API.G_EXCEPTION_ERROR;
925 END IF;
926
927 -- **************
928 -- Return results
929 -- **************
930
931 x_return_status := l_overall_status;
932
933 OKL_API.END_ACTIVITY (x_msg_count, x_msg_data);
934
935 EXCEPTION
936
937 WHEN OKL_API.G_EXCEPTION_ERROR THEN
938 x_return_status := OKL_API.HANDLE_EXCEPTIONS
939 (
940 l_api_name,
941 G_PKG_NAME,
942 'OKL_API.G_RET_STS_ERROR',
943 x_msg_count,
944 x_msg_data,
945 '_PVT'
946 );
947
948 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
949 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
950 (
951 l_api_name,
952 G_PKG_NAME,
953 'OKL_API.G_RET_STS_UNEXP_ERROR',
954 x_msg_count,
955 x_msg_data,
956 '_PVT'
957 );
958
959 WHEN OTHERS THEN
960
961 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
962 (
963 l_api_name,
964 G_PKG_NAME,
965 'OTHERS',
966 x_msg_count,
967 x_msg_data,
968 '_PVT'
969 );
970
971 END Change_Item_Location;
972
973 END OKL_AM_ASSET_RELOCATION_PVT;