CREATE OR REPLACE PROCEDURE XXX_UPDATE_UNPLANNED(errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
p_asset_number IN VARCHAR2,
p_tax_book IN VARCHAR2)
AS
-----This Procedure will do the following
------Uncheck the depreciation flag
------Chenage the Depr Method to STL
------Update the unplanned amount
------Change the Depr method back
---You can remove the functionality which you do not need.
---Assuming that data to be processed exists in a temporary table whose structure is as follows:
/*
CREATE TABLE XXX_unplan_depr_temp
(asset_number VARCHAR2(50),
account VARCHAR2(100),
deprn_amount NUMBER,
tax_book VARCHAR2(50),
error_message VARCHAR2(4000),
process_status VARCHAR2(1));
*/
l_return_status VARCHAR2(1);
l_msg_count NUMBER := 0;
l_msg_data VARCHAR2(4000);
l_trans_rec FA_API_TYPES.trans_rec_type;
l_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
l_asset_hdr_rec1 FA_API_TYPES.asset_hdr_rec_type;
l_asset_fin_rec_adj FA_API_TYPES.asset_fin_rec_type;
l_asset_fin_rec_adj1 FA_API_TYPES.asset_fin_rec_type;
l_asset_fin_rec_new FA_API_TYPES.asset_fin_rec_type;
l_asset_fin_mrc_tbl_new FA_API_TYPES.asset_fin_tbl_type;
l_inv_trans_rec FA_API_TYPES.inv_trans_rec_type;
l_inv_tbl FA_API_TYPES.inv_tbl_type;
l_inv_rate_tbl FA_API_TYPES.inv_rate_tbl_type;
l_asset_deprn_rec_adj FA_API_TYPES.asset_deprn_rec_type;
l_asset_deprn_rec_new FA_API_TYPES.asset_deprn_rec_type;
l_asset_deprn_mrc_tbl_new FA_API_TYPES.asset_deprn_tbl_type;
l_inv_rec FA_API_TYPES.inv_rec_type;
l_group_reclass_options_rec FA_API_TYPES.group_reclass_options_rec_type;
l_trx_header_id NUMBER;
l_unplanned_deprn_rec FA_API_TYPES.unplanned_deprn_rec_type;
l_deprn_method_code VARCHAR2(50);
l_life_in_months NUMBER;
l_ccid NUMBER;
CURSOR main_csr IS
SELECT asset_number,
deprn_amount,
account,
tax_book,
rowid frowid
FROM XXX_unplan_depr_temp
WHERE asset_number = NVL(p_asset_number, asset_number)
AND tax_book = NVL(p_tax_book, tax_book)
AND process_status = 'N';
CURSOR asset_csr(v_asset_number VARCHAR2, v_tax_book VARCHAR2) IS
SELECT fa.asset_id, deprn_method_code , life_in_months , depreciate_flag
FROM apps.fa_books book, fa_additions_b fa
WHERE book.asset_id =fa.asset_id
AND book.book_type_code = v_tax_book
AND date_ineffective IS NULL
AND fa.asset_number = v_asset_number;
CURSOR ccid_csr(v_account VARCHAR2) IS
SELECT code_Combination_id
FROM gl_code_combinations_kfv
WHERE concatenated_segments = v_account;
l_asset_id NUMBER;
l_header_seq_id NUMBER;
l_depreciate_flag VARCHAR2(10);
l_error_tbl okl_Accounting_util.error_message_type;
l_error_message VARCHAR2(2000);
PROCEDURE update_temp_tbl(p_error_msg IN VARCHAR2,
p_return_status IN VARCHAR2,
p_rowid IN ROWID)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
UPDATE XXX_unplan_depr_temp
SET process_status = l_return_status,
error_message = p_error_msg
WHERE rowid = p_rowid;
COMMIT;
END update_temp_tbl;
BEGIN
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Asset Number' || CHR(9) ||
'Account' || CHR(9) ||
'Deprn Amount' || CHR(9) ||
'Tax Book' || CHR(9) ||
'Process Status ' || CHR(9) ||
'Error Message');
FOR main_rec IN main_csr
LOOP
l_return_status := 'S';
l_error_message := '';
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Processing Asset Number ' || main_rec.asset_number);
OPEN asset_csr(main_rec.asset_number, main_rec.tax_book);
FETCH asset_csr INTO l_asset_id,
l_deprn_method_code,
l_life_in_months,
l_depreciate_flag;
CLOSE asset_csr;
-----Update the asset depreciation Flag to NO
IF (l_depreciate_flag <> 'NO') THEN
----Remove the Depreciation Flag
fa_trans_api_pub.DO_ADJUSTMENT(P_API_VERSION => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data,
P_CALLING_FN => 'XXX',
X_TRANSACTION_HEADER_ID => l_trx_header_id,
P_AMORTIZATION_START_DATE => sysdate,
P_LAST_UPDATE_DATE => SYSDATE,
P_LAST_UPDATED_BY => fnd_global.user_id,
P_CREATED_BY => fnd_global.user_id,
P_CREATION_DATE => sysdate,
P_LAST_UPDATE_LOGIN => 1,
P_ASSET_ID => l_asset_id,
P_BOOK_TYPE_CODE => main_rec.tax_book,
P_DEPRECIATE_FLAG => 'NO');
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Return Status After Depreciation Flag Change is ' || l_return_status);
IF (l_return_status <> 'S') THEN
l_error_tbl.DELETE;
OKL_ACCOUNTING_UTIL.GET_ERROR_msg(l_error_tbl);
l_error_message := NULL;
FOR i IN 1..l_error_tbl.COUNT
LOOP
FND_FILE.PUT_LINE(FND_FILE.LOG, l_error_tbl(i));
l_error_message := l_error_message || '|' || l_error_tbl(i);
END LOOP;
ROLLBACK;
ELSE
COMMIT WORK;
END IF;
END IF;
-----Update the Depreciation Method to STL
IF (l_return_status = 'S') AND (l_deprn_method_code <> 'STL') THEN
fa_trans_api_pub.DO_ADJUSTMENT(P_API_VERSION => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data,
P_CALLING_FN => 'XXX',
X_TRANSACTION_HEADER_ID => l_trx_header_id,
P_AMORTIZATION_START_DATE => sysdate,
P_LAST_UPDATE_DATE => SYSDATE,
P_LAST_UPDATED_BY => fnd_global.user_id,
P_CREATED_BY => fnd_global.user_id,
P_CREATION_DATE => sysdate,
P_LAST_UPDATE_LOGIN => 1,
P_ASSET_ID => l_asset_id,
P_BOOK_TYPE_CODE => main_rec.tax_book,
p_deprn_method_code => 'STL',
p_life_in_months => l_life_in_months);
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Return Status After Method Change is ' || l_return_status);
IF (l_return_status <> 'S') THEN
l_error_tbl.DELETE;
OKL_ACCOUNTING_UTIL.GET_ERROR_msg(l_error_tbl);
l_error_message := '';
FOR i IN 1..l_error_tbl.COUNT
LOOP
FND_FILE.PUT_LINE(FND_FILE.LOG,l_error_tbl(i));
l_error_message := l_error_message || '|' || l_error_tbl(i);
END LOOP;
ROLLBACK;
ELSE
COMMIT WORK;
END IF;
END IF;
OPEN ccid_csr(main_rec.account);
FETCH ccid_csr INTO l_ccid;
IF (ccid_csr%NOTFOUND) THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Account is Not correct, not updating the unplanned Depreciation');
l_return_status := 'A';
END IF;
CLOSE ccid_csr;
----Create the Unplanned Depreciation
IF (l_return_status = 'S') THEN
FA_TRANS_API_PUB.DO_UNPLANNED(P_API_VERSION => 1.0,
p_init_msg_list => 'T',
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data,
P_CALLING_FN => 'XXX',
X_TRANSACTION_HEADER_ID => l_trx_header_id,
p_transaction_date_entered => sysdate,
p_amortization_start_date => sysdate,
P_ASSET_ID => l_asset_id,
P_BOOK_TYPE_CODE => main_rec.tax_book,
P_CODE_COMBINATION_ID => l_ccid,
P_UNPLANNED_AMOUNT => main_rec.deprn_amount,
P_UNPLANNED_TYPE => 'SWIFT',
P_LAST_UPDATE_DATE => sysdate,
P_LAST_UPDATED_BY => 1,
P_CREATED_BY => 1,
P_CREATION_DATE => sysdate,
P_LAST_UPDATE_LOGIN => 1);
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Status after call of Unplanned is ' || l_return_status);
IF (l_return_status <> 'S') THEN
l_error_tbl.DELETE;
OKL_ACCOUNTING_UTIL.GET_ERROR_msg(l_error_tbl);
l_error_message := NULL;
FOR i IN 1..l_error_tbl.COUNT
LOOP
FND_FILE.PUT_LINE(FND_FILE.LOG, l_error_tbl(i));
l_error_message := l_error_message || '|' || l_error_tbl(i);
END LOOP;
ROLLBACK;
ELSE
COMMIT WORK;
END IF;
END IF;
----Reset the Depreciation method to the old Value
IF (l_return_status = 'S') THEN
fa_trans_api_pub.DO_ADJUSTMENT(P_API_VERSION => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data,
P_CALLING_FN => 'XXX',
X_TRANSACTION_HEADER_ID => l_trx_header_id,
P_AMORTIZATION_START_DATE => sysdate,
P_LAST_UPDATE_DATE => SYSDATE,
P_LAST_UPDATED_BY => fnd_global.user_id,
P_CREATED_BY => fnd_global.user_id,
P_CREATION_DATE => sysdate,
P_LAST_UPDATE_LOGIN => 1,
P_ASSET_ID => l_asset_id,
P_BOOK_TYPE_CODE => main_rec.tax_book,
p_deprn_method_code => l_deprn_method_code,
p_life_in_months => l_life_in_months);
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Return Status After Method Change Back is ' || l_return_status);
IF (l_return_status <> 'S') THEN
l_error_tbl.DELETE;
OKL_ACCOUNTING_UTIL.GET_ERROR_msg(l_error_tbl);
l_error_message := NULL;
FOR i IN 1..l_error_tbl.COUNT
LOOP
FND_FILE.PUT_LINE(FND_FILE.LOG, l_error_tbl(i));
l_error_message := l_error_message || '|' || l_error_tbl(i);
END LOOP;
ROLLBACK;
ELSE
COMMIT WORK;
END IF;
END IF;
update_temp_tbl(p_error_msg => l_error_message,
p_return_status => l_return_status,
p_rowid => main_rec.frowid);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,main_rec.asset_number || CHR(9) ||
main_rec.account || CHR(9) ||
main_rec.deprn_amount || CHR(9) ||
main_rec.tax_book || CHR(9) ||
l_return_status || CHR(9) ||
l_error_message);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG, SQLERRM);
END XXX_UPDATE_UNPLANNED;
No comments:
Post a Comment