-- Name
-- FND_CONCURRENT.Cancel_Request.
-- Purpose
-- It Cancels given Concurrent Request.
--
-- Arguments (input)
-- request_id - request id of the request you want to cancel.
--
-- (out args)
-- message - API will fill the message with any errors while canceling
-- request.
--
-- Returns:
-- Returns TRUE if success or FALSE on failure.
Function Cancel_Request( Request_Id in NUMBER,
Message out NOCOPY VARCHAR2) return boolean is
ret_val number;
submitter number;
request_missing exception;
no_privilege exception;
======================================================================================
-- Procedure
-- enable_program
--
-- Purpose
-- enable or disable the concurrent program.
--
-- Arguments
-- program_short_name - Short name of the program.
-- program_application - Application of the program.
-- enabled - 'Y' or 'N' values.
--
PROCEDURE enable_program(short_name IN VARCHAR2,
application IN VARCHAR2,
enabled IN VARCHAR2);
=======================================================================================
--
-- Name
-- set_repeat_options
-- Purpose
-- Called before submitting request if the request to be submitted
-- is a repeating request.
--
-- Arguments
-- repeat_time - Time of day at which it has to be repeated
-- repeat_interval - Frequency at which it has to be repeated
-- - This will be used/applied only when repeat_time
-- - is NULL ( non null repeat_interval overrides )
-- repeat_unit - Unit for repeat interval. Default is DAYS.
-- - MONTHS/DAYS/HOURS/MINUTES
-- repeat_type - Apply repeat interval from START or END of request
-- - default is START. START/END
-- repeat_end_time - Time at which the repetition should be stopped
-- incrment_dates - 'Y' if dates should be incremented each run,
-- otherwise 'N'
--
function set_repeat_options (repeat_time IN varchar2 default NULL,
repeat_interval IN number default NULL,
repeat_unit IN varchar2 default 'DAYS',
repeat_type IN varchar2 default 'START',
repeat_end_time IN varchar2 default NULL,
increment_dates IN varchar2 default NULL) return boolean;
Thursday, December 30, 2010
sample package for fail the concurrent program
CREATE OR REPLACE PACKAGE APPS.XXTP_SAMPLE_PKG1
AS
PROCEDURE xxtp_sample_proc1 (v_errbuf out VARCHAR2, v_retcode out NUMBER);
END;
/
CREATE
AS
PROCEDURE xxtp_sample_proc1 (v_errbuf out VARCHAR2, v_retcode out NUMBER)
AS
l_date date;
BEGIN
l_date:=null;---select sysdate into l_date from dual;
Raise_Application_Error(-20001,'Program failed');
FND_FILE
END;
.PUT_LINE (FND_FILE.LOG,'sample program');END XXTP_SAMPLE_PKG1; / OR REPLACE PACKAGE BODY APPS.XXTP_SAMPLE_PKG1
AS
PROCEDURE xxtp_sample_proc1 (v_errbuf out VARCHAR2, v_retcode out NUMBER);
END;
/
CREATE
AS
PROCEDURE xxtp_sample_proc1 (v_errbuf out VARCHAR2, v_retcode out NUMBER)
AS
l_date date;
BEGIN
l_date:=null;---select sysdate into l_date from dual;
Raise_Application_Error(-20001,'Program failed');
FND_FILE
END;
.PUT_LINE (FND_FILE.LOG,'sample program');END XXTP_SAMPLE_PKG1; / OR REPLACE PACKAGE BODY APPS.XXTP_SAMPLE_PKG1
Tuesday, December 28, 2010
Tuesday, December 21, 2010
Script for Creating Unplanned Depreciation (FA)
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;
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;
Script for Creating Tax Reserve Adjustments in FA
CREATE OR REPLACE PROCEDURE XXX_TAX_RESERVE_ADJUST(errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
p_asset_number IN VARCHAR2,
p_fiscal_year IN NUMBER,
p_tax_book IN VARCHAR2)
AS
/* This Script will create Tax Reserve Adjustments for assets. The assets and their amounts can be stored in a temporary table
whose structure is as follows;
CREATE TABLE XXX_tax_reserve_temp
(asset_number VARCHAR2(50),
fiscal_Year NUMBER,
deprn_amount NUMBER,
tax_book VARCHAR2(50),
error_message VARCHAR2(4000),
process_status VARCHAR2(1));
*/
l_return_status varchar2(5);
x_return_status BOOLEAN;
l_faxcat BOOLEAN;
l_dummy 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_fin_rec_adj 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_transaction_header_id number(15);
l_new_cost NUMBER;
l_cost NUMBER;
l_adjusted_cost NUMBER;
l_date_placed_in_service DATE;
l_prorate_date DATE;
l_deprn_start_date DATE;
l_ceiling_name VARCHAR2(30);
l_bonus_rule VARCHAR2(30);
l_deprn_method_code VARCHAR2(12);
l_recoverable_cost NUMBER;
l_adjusted_rate NUMBER;
l_life_in_months NUMBER(4);
l_salvage_value NUMBER;
l_depreciate_flag VARCHAR2(3);
l_rate_adjustment_factor NUMBER;
l_reval_amortization_basis NUMBER;
l_production_capacity NUMBER;
l_perd_fully_reserved NUMBER(38);
l_current_units NUMBER;
l_asset_category_id NUMBER(15);
l_asset_type VARCHAR2(11);
l_rowid VARCHAR2(100);
l_tax_book VARCHAR2(50);
l_trx_date DATE;
l_fiscal_year NUMBER ;
l_old_deprn_amount NUMBER;
retire_count number:=0;
h_status BOOLEAN := TRUE;
CURSOR main_csr IS
SELECT rowid frowid,
asset_number,
fiscal_year,
deprn_amount,
tax_book
FROM XXX_tax_reserve_temp
WHERE process_status = 'N'
AND asset_number = NVL(p_asset_number, asset_number)
AND fiscal_year = NVL(p_fiscal_year, fiscal_year)
AND tax_book = NVL(p_tax_book, tax_book);
CURSOR asset_csr(v_asset_number VARCHAR2) IS
SELECT asset_id
FROM fa_additions_b
WHERE asset_number = v_asset_number;
l_asset_id NUMBER(15);
l_asset_number VARCHAR2(15);
l_header_seq_id NUMBER;
l_deprn_resv NUMBER;
l_error_tbl okl_Accounting_util.error_message_type;
l_error_message VARCHAR2(2000);
l_error_message1 VARCHAR2(2000);
CURSOR tax_book_csr IS
SELECT book_type_code
FROM fa_book_controls
WHERE set_of_books_id = okl_accounting_util.get_set_of_books_id
AND book_class = 'TAX';
CURSOR curr_fiscal_csr(v_tax_book VARCHAR2) IS
SELECT fadp.fiscal_year
FROM fa_deprn_periods fadp
WHERE fadp.book_type_code = v_tax_book
AND fadp.period_close_date is null;
CURSOR dtl_csr(v_asset_id VARCHAR2, v_tax_book VARCHAR2) IS
SELECT fab.cost,
fab.adjusted_cost,
fab.date_placed_in_service,
fab.prorate_date,
fab.deprn_start_date,
fab.ceiling_name,
fab.bonus_rule,
fab.deprn_method_code, fab.recoverable_cost,
fab.adjusted_rate, fab.life_in_months, fab.salvage_value,
fab.depreciate_flag, fab.rate_adjustment_factor,
nvl(fab.reval_amortization_basis,0),
nvl(fab.production_capacity,0),
nvl(fab.period_counter_fully_reserved,0),
fa.current_units,
fa.asset_category_id,
fa.asset_type
FROM fa_books fab,
fa_additions_b fa
where fab.asset_id = fa.asset_id
AND fa.asset_id = v_asset_id
AND fab.book_type_code = v_tax_book
AND fab.date_ineffective is null;
CURSOR trx_date_csr(v_asset_id NUMBER, v_fiscal_year NUMBER, v_tax_book VARCHAR2) IS
SELECT dp.calendar_period_close_date
FROM apps.fa_deprn_periods dp
WHERE dp.period_counter =
(SELECT max(dp1.period_counter)
FROM fa_deprn_periods dp1, fa_deprn_summary ds1
WHERE dp1.fiscal_year = v_fiscal_year
and dp1.period_counter = ds1.period_counter
and dp1.book_type_code = v_tax_book
AND ds1.asset_id = v_asset_id
AND ds1.book_type_code = v_tax_book)
AND dp.book_type_code = v_tax_book;
CURSOR check_trx(v_asset_id NUMBER, v_tax_book VARCHAR2, v_trx_date DATE) IS
SELECT 1 FROM fa_transaction_headers
WHERE asset_id = v_asset_id
AND book_type_code = v_tax_book
AND transaction_type_code = 'TAX'
AND transaction_date_entered = v_trx_date;
CURSOR retire_csr(v_asset_id NUMBER, v_tax_book VARCHAR2) IS
SELECT count(1)
FROM fa_transaction_headers
WHERE transaction_header_id =
(SELECT max(transaction_header_id)
FROM fa_transaction_headers
WHERE asset_id = v_asset_id
AND book_type_code = l_tax_book)
AND transaction_type_code = 'FULL RETIREMENT'
AND retire_count < 2;
l_retire_count NUMBER;
l_current_fiscal_year NUMBER;
CURSOR check_fiscal_csr(v_asset_id NUMBER, v_curr_fiscal_year NUMBER, v_tax_book VARCHAR2, v_given_year NUMBER) IS
SELECT 1 FROM DUAL
WHERE v_given_year IN (
SELECT DISTINCT fadp.fiscal_year
FROM fa_deprn_periods fadp, fa_deprn_summary fads, fa_books bk
WHERE fadp.book_type_code = v_tax_book
AND fads.asset_id = v_asset_id
AND fads.deprn_source_code = 'DEPRN'
AND fadp.period_counter = fads.period_counter
AND fadp.fiscal_year < v_curr_fiscal_year
AND bk.book_type_code = v_tax_book
AND bk.asset_id = v_asset_id
AND bk.date_ineffective IS NULL
AND bk.date_placed_in_service <= fadp.calendar_period_close_date);
----transaction_header_id
----book_type_code
----asset_id
----transaction_type_code = 'TAX'
----transaction_date_entered = 31-Oct-2008 for 2008
----date_effective = sysdate
----transaction_name = comments
----
PROCEDURE GET_DEPRN_SUM(p_asset_id IN NUMBER,
p_fiscal_year IN NUMBER,
p_tax_book IN VARCHAR2,
x_deprn_amount OUT NUMBER,
x_deprn_resv OUT NUMBER,
x_return_status OUT VARCHAR2)
IS
l_period_counter_adjusted NUMBER;
l_transaction_date_entered DATE;
l_book_type_code VARCHAR2(100);
l_fiscal_year NUMBER;
dummy_num number;
dummy_char varchar2(10);
dummy_bool boolean;
l_bonus_deprn_rsv number;
l_bonus_ytd_deprn number;
l_bonus_deprn_amount number;
l_ytd_deprn2 NUMBER;
l_ytd_deprn1 NUMBER;
l_ytd_deprn NUMBER;
l_prv_period_counter NUMBER;
period_check NUMBER;
l_asset_id NUMBER;
l_return_status VARCHAR2(1);
l_fiscal_ytd_deprn NUMBER;
CURSOR ast_csr IS
SELECT asset_id,
asset_number
FROM fa_additions_b fa
WHERE asset_id = p_asset_id;
CURSOR c1(v_fiscal_year NUMBER, v_book_type_code VARCHAR2, v_asset_id NUMBER) IS
SELECT dp.period_counter,
dp.calendar_period_close_date
FROM fa_deprn_periods dp
WHERE dp.period_counter = (SELECT max(dp1.period_counter)
FROM fa_deprn_periods dp1, fa_deprn_summary ds1
WHERE dp1.fiscal_year = v_fiscal_year
and dp1.period_counter = ds1.period_counter
and dp1.book_type_code = v_book_type_code
and ds1.asset_id = v_asset_id
and ds1.book_type_code = v_book_type_code)
and dp.book_type_code = v_book_type_code;
BEGIN
l_return_status := 'S';
OPEN c1(p_fiscal_year, p_tax_book, p_asset_id);
FETCH c1 INTO l_period_counter_adjusted, l_transaction_date_entered;
IF c1%NOTFOUND THEN
l_return_status := 'E';
END IF;
CLOSE c1;
FND_FILE.PUT_LINE(FND_FILE.LOG,'DEPRN PROC ' || l_period_counter_adjusted || ' ' || l_transactioN_date_entered);
IF (l_return_status = 'S') THEN
fa_query_balances_pkg.query_balances(
X_asset_id => p_asset_id,
X_book => p_tax_book,
X_period_ctr => l_period_counter_adjusted,
X_dist_id => 0,
X_run_mode => 'ADJUSTED',
X_cost => dummy_num,
X_deprn_rsv => l_ytd_deprn2,
X_reval_rsv => dummy_num,
X_ytd_deprn => dummy_num,
X_ytd_reval_exp => dummy_num,
X_reval_deprn_exp => dummy_num,
X_deprn_exp => dummy_num,
X_reval_amo => dummy_num,
X_prod => dummy_num,
X_ytd_prod => dummy_num,
X_ltd_prod => dummy_num,
X_adj_cost => dummy_num,
X_reval_amo_basis => dummy_num,
X_bonus_rate => dummy_num,
X_deprn_source_code => dummy_char,
X_adjusted_flag => dummy_bool,
X_transaction_header_id => -1,
X_bonus_deprn_rsv => l_bonus_deprn_rsv,
X_bonus_ytd_deprn => l_bonus_ytd_deprn,
X_bonus_deprn_amount => l_bonus_deprn_amount);
FND_FILE.PUT_LINE(FND_FILE.LOG,'DEPRN2=>' || l_ytd_deprn2);
SELECT min(dp.period_counter) - 1
INTO l_prv_period_counter
FROM fa_deprn_periods dp
WHERE dp.fiscal_year = p_fiscal_year
AND dp.book_type_code =p_tax_book;
SELECT count(*) into period_check
FROM fa_deprn_periods
WHERE book_Type_code = p_tax_book
AND period_counter = l_prv_period_counter;
IF (period_check = 0) then
l_prv_period_counter := l_prv_period_counter + 1;
END IF;
fa_query_balances_pkg.query_balances(
X_asset_id => p_asset_id,
X_book => p_tax_book,
X_period_ctr => l_prv_period_counter,
X_dist_id => 0,
X_run_mode => 'ADJUSTED',
X_cost => dummy_num,
X_deprn_rsv => l_ytd_deprn1,
X_reval_rsv => dummy_num,
X_ytd_deprn => dummy_num,
X_ytd_reval_exp => dummy_num,
X_reval_deprn_exp => dummy_num,
X_deprn_exp => dummy_num,
X_reval_amo => dummy_num,
X_prod => dummy_num,
X_ytd_prod => dummy_num,
X_ltd_prod => dummy_num,
X_adj_cost => dummy_num,
X_reval_amo_basis => dummy_num,
X_bonus_rate => dummy_num,
X_deprn_source_code => dummy_char,
X_adjusted_flag => dummy_bool,
X_transaction_header_id => -1,
X_bonus_deprn_rsv => l_bonus_deprn_rsv,
X_bonus_ytd_deprn => l_bonus_ytd_deprn,
X_bonus_deprn_amount => l_bonus_deprn_amount);
FND_FILE.PUT_LINE(FND_FILE.LOG,'DEPRN1=>' || l_ytd_deprn1);
l_ytd_deprn := NVL(l_ytd_deprn2,0) - NVL(l_ytd_deprn1,0);
x_deprn_amount := NVL(l_ytd_deprn,0);
fa_query_balances_pkg.query_balances(X_asset_id => p_asset_id,
X_book => p_tax_book,
X_period_ctr => 0,
X_dist_id => 0,
X_run_mode => 'ADJUSTED',
X_cost => dummy_num,
X_deprn_rsv => x_deprn_resv,
X_reval_rsv => dummy_num,
X_ytd_deprn => dummy_num,
X_ytd_reval_exp => dummy_num,
X_reval_deprn_exp => dummy_num,
X_deprn_exp => dummy_num,
X_reval_amo => dummy_num,
X_prod => dummy_num,
X_ytd_prod => dummy_num,
X_ltd_prod => dummy_num,
X_adj_cost => dummy_num,
X_reval_amo_basis => dummy_num,
X_bonus_rate => dummy_num,
X_deprn_source_code => dummy_char,
X_adjusted_flag => dummy_bool,
X_transaction_header_id => -1,
X_bonus_deprn_rsv => l_bonus_deprn_rsv,
X_bonus_ytd_deprn => l_bonus_ytd_deprn,
X_bonus_deprn_amount => l_bonus_deprn_amount);
END IF;
x_return_status := l_return_status;
END GET_DEPRN_SUM;
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_tax_reserve_temp
SET process_status = l_return_status,
error_message = p_error_msg
WHERE rowid = p_rowid;
COMMIT;
END update_temp_tbl;
BEGIN
OPEN tax_book_csr;
FETCH tax_book_csr INTO l_tax_book;
CLOSE tax_book_csr;
OPEN curr_fiscal_csr(l_tax_book);
FETCH curr_fiscal_csr INTO l_current_fiscal_year;
CLOSE curr_fiscal_csr;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Current Fiscal Year is ' || l_Current_Fiscal_year);
l_error_message := NULL;
l_error_message1 := NULL;
FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Asset Number' || CHR(9) ||
'Fiscal Year' || CHR(9) ||
'Deprn Amount' || CHR(9) ||
'Tax Book' || CHR(9) ||
'Return Status'|| CHR(9) ||
'Error Message' );
FOR main_rec IN main_csr
LOOP
FND_FILE.PUT_LINE(FND_FILE.LOG,'Processing the Asset Number=>' || main_rec.asset_number || ', ' || main_rec.fiscal_year
|| ', ' || main_rec.tax_book);
l_return_status := 'S';
OPEN asset_csr(main_rec.asset_number);
FETCH asset_csr INTO l_asset_id;
CLOSE asset_csr;
OPEN dtl_csr(l_asset_id, l_tax_book);
FETCH dtl_csr INTO l_cost,
l_adjusted_cost,
l_date_placed_in_service,
l_prorate_date,
l_deprn_start_date,
l_ceiling_name,
l_bonus_rule,
l_deprn_method_code,
l_recoverable_cost,
l_adjusted_rate,
l_life_in_months,
l_salvage_value,
l_depreciate_flag,
l_rate_adjustment_factor,
l_reval_amortization_basis,
l_production_capacity,
l_perd_fully_reserved,
l_current_units,
l_asset_category_id,
l_asset_type;
CLOSE dtl_csr;
l_tax_book := main_rec.tax_book;
l_old_deprn_amount := 0;
OPEN check_fiscal_csr(l_asset_id , l_current_fiscal_year, l_tax_book, main_rec.fiscal_year);
FETCH check_fiscal_csr INTO l_dummy;
IF (check_fiscal_csr%NOTFOUND) THEN
l_return_status := 'T';
l_error_message := 'No Data Exists for the Given Financial Year';
FND_FILE.PUT_LINE(FND_FILE.LOG,'No Data Exists for the Given Financial Year');
END IF;
CLOSE check_fiscal_csr;
IF (l_return_status = 'S') THEN
OPEN trx_date_csr(l_asset_id, main_rec.fiscal_year, l_tax_book);
FETCH trx_date_csr INTO l_trx_date;
CLOSE trx_Date_csr;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Transaction DAte for the Fiscal Year is ' || l_trx_date);
l_dummy := NULL;
OPEN check_trx(l_asset_id , l_tax_book , l_trx_date);
FETCH check_trx INTO l_dummy;
CLOSE check_trx;
IF (l_dummy IS NOT NULL) THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'This Asset is Already processed for the given financial Year');
l_return_status := 'D';
l_error_message := 'Asset Already Processed Previously';
END IF;
END IF;
IF (l_return_status = 'S') THEN
GET_DEPRN_SUM(p_asset_id => l_asset_id,
p_fiscal_year => main_rec.fiscal_year,
p_tax_book => l_tax_book,
x_deprn_amount => l_old_deprn_amount,
x_deprn_resv => l_deprn_resv,
x_return_status => l_return_status);
IF (l_return_status <> 'S') THEN
l_return_status := 'D';
l_error_message := 'You did not depreciate assets during this fiscal year. Please perform your depreciation adjustment in a later fiscal year.';
FND_FILE.PUT_LINE(FND_FILE.LOG,'You did not depreciate assets during this fiscal year. Please perform your depreciation adjustment in a later fiscal year.');
ELSE
FND_FILE.PUT_LINE(FND_FILE.LOG,'Old Depreciation Amount is ' || l_old_deprn_amount);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Deprn Reserve is ' || l_deprn_resv);
IF ABS(main_rec.deprn_amount - l_old_deprn_amount + l_deprn_resv) > ABS(l_recoverable_cost) THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Depreciation Cannot Exceeed the Cost');
l_Error_message := 'Depreciation Cannot Exceeed the Cost';
l_return_status := 'C';
END IF;
END IF;
END IF;
IF (l_return_status = 'S') THEN
IF (l_old_deprn_amount = main_rec.deprn_amount) THEN
FND_FILE.PUT_LINE(FND_FILE.LOG, 'The Old Depreciation Amount ' || l_old_deprn_amount || ' is Same as the New Amount. Not Proceeding Further' );
l_Error_message := 'The Old Depreciation Amount ' || l_old_deprn_amount || ' is Same as the New Amount. Not Proceeding Further';
l_return_status := 'P';
ELSE
FND_FILE.PUT_LINE(FND_FILE.LOG, 'The Old Depreciation Amount ' || l_old_deprn_amount || ' is Different then the New Amount. Proceeding Further');
l_Error_message := NULL;
l_return_status := 'S';
END IF;
END IF;
IF (l_return_status = 'S') THEN
OPEN retire_csr(l_asset_id, l_tax_book);
FETCH retire_csr INTO l_retire_count;
CLOSE retire_csr;
IF (l_retire_count > 0) THEN
l_return_status := 'R';
FND_FILE.PUT_LINE(FND_FILE.LOG,'You cannot perform a Reserve Adjustment on a fully retired asset.');
l_Error_message := 'You cannot perform a Reserve Adjustment on a fully retired asset.';
ELSE
FND_FILE.PUT_LINE(FND_FILE.LOG,'Asset is Not Retired, Proceeding Further');
l_error_message := NULL;
END IF;
END IF;
IF (l_return_status = 'S') THEN
SELECT fa_transaction_headers_s.nextval
INTO l_transaction_header_id
FROM dual;
OPEN trx_date_csr(l_asset_id, main_rec.fiscal_year, l_tax_book);
FETCH trx_date_csr INTO l_trx_date;
CLOSE trx_date_csr;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Inserting Transaction in FA Transaction Header');
FA_TRANSACTION_HEADERS_PKG.insert_row(X_ROWID => l_rowid,
X_TRANSACTION_HEADER_ID => l_transaction_header_id,
X_BOOK_TYPE_CODE => l_tax_book,
X_ASSET_ID => l_asset_id,
X_TRANSACTION_TYPE_CODE => 'TAX',
X_TRANSACTION_DATE_ENTERED => l_trx_date,
X_DATE_EFFECTIVE => sysdate,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => fnd_global.user_id,
X_RETURN_STATUS => x_return_status,
X_CALLING_FN => 'CUSTOM');
IF (x_return_status) THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Transaction Created Successfully');
l_return_status := 'S';
l_error_message := NULL;
ELSE
FND_FILE.PUT_LINE(FND_FILE.LOG,'Could not Create Transaction ');
l_error_message := 'Could not Create Transaction ';
l_return_status := 'E';
END IF;
IF (l_return_status = 'S') THEN
l_faxcat := FA_TRX_APPROVAL_PKG.faxcat(
X_book => 'NFC TAX BOOK',
X_asset_id => l_asset_id,
X_trx_type => 'TAX',
X_trx_date => sysdate,
X_init_message_flag => 'NO');
IF (l_faxcat) THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'FaxCat Success');
ELSE
FND_FILE.PUT_LINE(FND_FILE.LOG,'FaxCat Error');
END IF;
h_status := fa_txrsv_pkg.faxtxa (X_ASSET_NUMBER => l_asset_number,
X_ASSET_ID => l_asset_id,
X_CURRENT_UNITS => l_current_units ,
X_ASSET_CATEGORY_ID => l_asset_category_id ,
X_ASSET_TYPE => l_asset_type ,
X_NEW_COST => l_cost ,
X_SALVAGE_VALUE => l_salvage_value ,
X_NEW_RECOVERABLE_COST => l_recoverable_cost ,
X_RECOVERABLE_COST => l_recoverable_cost ,
X_ADJUSTED_RATE => l_adjusted_rate ,
X_CEILING_NAME => l_ceiling_name ,
X_BONUS_RULE => l_bonus_rule ,
X_DEPRN_METHOD_CODE => l_deprn_method_code,
X_LIFE_IN_MONTHS => l_life_in_months ,
X_DATE_PLACED_IN_SERVICE => l_date_placed_in_service ,
X_PRORATE_DATE => l_prorate_date ,
X_DEPRN_START_DATE => l_deprn_start_date ,
X_DEPRECIATE_FLAG => l_depreciate_flag ,
X_PRODUCTION_CAPACITY => l_production_capacity,
X_BOOK_TYPE_CODE => l_tax_book,
X_TRANSACTION_HEADER_ID => l_transaction_header_id ,
X_RATE_ADJUSTMENT_FACTOR => l_rate_adjustment_factor ,
X_REVAL_AMORTIZATION_BASIS => l_reval_amortization_basis ,
X_OLD_DEPRN_RSV => l_old_deprn_amount,
X_NEW_DEPRN_RSV => main_rec.deprn_amount,
X_FISCAL_YEAR => main_rec.fiscal_year,
X_PERIOD_COUNTER_RSV => l_perd_fully_reserved ,
X_SYSDATE_VAL => sysdate );
IF (h_status) THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'faxtxa proc is Success');
l_return_status := 'S';
ELSE
FND_FILE.PUT_LINE(FND_FILE.LOG,'faxtxa proc is Error');
l_error_message := 'faxtxa proc is Error';
l_return_status := 'E';
END IF;
ELSE
FND_FILE.PUT_LINE(FND_FILE.LOG,'Transaction was not successful, not proceeding further');
END IF;
END IF;
IF (l_return_status = 'E') 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
l_error_message := l_error_message || '|' || l_error_tbl(i);
END LOOP;
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.fiscal_year
|| CHR(9) || main_rec.deprn_amount
|| CHR(9) || main_rec.tax_book
|| CHR(9) || l_return_status
|| CHR(9) || l_error_message);
END LOOP; ---of Assets
END XXX_TAX_RESERVE_ADJUST;
retcode OUT VARCHAR2,
p_asset_number IN VARCHAR2,
p_fiscal_year IN NUMBER,
p_tax_book IN VARCHAR2)
AS
/* This Script will create Tax Reserve Adjustments for assets. The assets and their amounts can be stored in a temporary table
whose structure is as follows;
CREATE TABLE XXX_tax_reserve_temp
(asset_number VARCHAR2(50),
fiscal_Year NUMBER,
deprn_amount NUMBER,
tax_book VARCHAR2(50),
error_message VARCHAR2(4000),
process_status VARCHAR2(1));
*/
l_return_status varchar2(5);
x_return_status BOOLEAN;
l_faxcat BOOLEAN;
l_dummy 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_fin_rec_adj 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_transaction_header_id number(15);
l_new_cost NUMBER;
l_cost NUMBER;
l_adjusted_cost NUMBER;
l_date_placed_in_service DATE;
l_prorate_date DATE;
l_deprn_start_date DATE;
l_ceiling_name VARCHAR2(30);
l_bonus_rule VARCHAR2(30);
l_deprn_method_code VARCHAR2(12);
l_recoverable_cost NUMBER;
l_adjusted_rate NUMBER;
l_life_in_months NUMBER(4);
l_salvage_value NUMBER;
l_depreciate_flag VARCHAR2(3);
l_rate_adjustment_factor NUMBER;
l_reval_amortization_basis NUMBER;
l_production_capacity NUMBER;
l_perd_fully_reserved NUMBER(38);
l_current_units NUMBER;
l_asset_category_id NUMBER(15);
l_asset_type VARCHAR2(11);
l_rowid VARCHAR2(100);
l_tax_book VARCHAR2(50);
l_trx_date DATE;
l_fiscal_year NUMBER ;
l_old_deprn_amount NUMBER;
retire_count number:=0;
h_status BOOLEAN := TRUE;
CURSOR main_csr IS
SELECT rowid frowid,
asset_number,
fiscal_year,
deprn_amount,
tax_book
FROM XXX_tax_reserve_temp
WHERE process_status = 'N'
AND asset_number = NVL(p_asset_number, asset_number)
AND fiscal_year = NVL(p_fiscal_year, fiscal_year)
AND tax_book = NVL(p_tax_book, tax_book);
CURSOR asset_csr(v_asset_number VARCHAR2) IS
SELECT asset_id
FROM fa_additions_b
WHERE asset_number = v_asset_number;
l_asset_id NUMBER(15);
l_asset_number VARCHAR2(15);
l_header_seq_id NUMBER;
l_deprn_resv NUMBER;
l_error_tbl okl_Accounting_util.error_message_type;
l_error_message VARCHAR2(2000);
l_error_message1 VARCHAR2(2000);
CURSOR tax_book_csr IS
SELECT book_type_code
FROM fa_book_controls
WHERE set_of_books_id = okl_accounting_util.get_set_of_books_id
AND book_class = 'TAX';
CURSOR curr_fiscal_csr(v_tax_book VARCHAR2) IS
SELECT fadp.fiscal_year
FROM fa_deprn_periods fadp
WHERE fadp.book_type_code = v_tax_book
AND fadp.period_close_date is null;
CURSOR dtl_csr(v_asset_id VARCHAR2, v_tax_book VARCHAR2) IS
SELECT fab.cost,
fab.adjusted_cost,
fab.date_placed_in_service,
fab.prorate_date,
fab.deprn_start_date,
fab.ceiling_name,
fab.bonus_rule,
fab.deprn_method_code, fab.recoverable_cost,
fab.adjusted_rate, fab.life_in_months, fab.salvage_value,
fab.depreciate_flag, fab.rate_adjustment_factor,
nvl(fab.reval_amortization_basis,0),
nvl(fab.production_capacity,0),
nvl(fab.period_counter_fully_reserved,0),
fa.current_units,
fa.asset_category_id,
fa.asset_type
FROM fa_books fab,
fa_additions_b fa
where fab.asset_id = fa.asset_id
AND fa.asset_id = v_asset_id
AND fab.book_type_code = v_tax_book
AND fab.date_ineffective is null;
CURSOR trx_date_csr(v_asset_id NUMBER, v_fiscal_year NUMBER, v_tax_book VARCHAR2) IS
SELECT dp.calendar_period_close_date
FROM apps.fa_deprn_periods dp
WHERE dp.period_counter =
(SELECT max(dp1.period_counter)
FROM fa_deprn_periods dp1, fa_deprn_summary ds1
WHERE dp1.fiscal_year = v_fiscal_year
and dp1.period_counter = ds1.period_counter
and dp1.book_type_code = v_tax_book
AND ds1.asset_id = v_asset_id
AND ds1.book_type_code = v_tax_book)
AND dp.book_type_code = v_tax_book;
CURSOR check_trx(v_asset_id NUMBER, v_tax_book VARCHAR2, v_trx_date DATE) IS
SELECT 1 FROM fa_transaction_headers
WHERE asset_id = v_asset_id
AND book_type_code = v_tax_book
AND transaction_type_code = 'TAX'
AND transaction_date_entered = v_trx_date;
CURSOR retire_csr(v_asset_id NUMBER, v_tax_book VARCHAR2) IS
SELECT count(1)
FROM fa_transaction_headers
WHERE transaction_header_id =
(SELECT max(transaction_header_id)
FROM fa_transaction_headers
WHERE asset_id = v_asset_id
AND book_type_code = l_tax_book)
AND transaction_type_code = 'FULL RETIREMENT'
AND retire_count < 2;
l_retire_count NUMBER;
l_current_fiscal_year NUMBER;
CURSOR check_fiscal_csr(v_asset_id NUMBER, v_curr_fiscal_year NUMBER, v_tax_book VARCHAR2, v_given_year NUMBER) IS
SELECT 1 FROM DUAL
WHERE v_given_year IN (
SELECT DISTINCT fadp.fiscal_year
FROM fa_deprn_periods fadp, fa_deprn_summary fads, fa_books bk
WHERE fadp.book_type_code = v_tax_book
AND fads.asset_id = v_asset_id
AND fads.deprn_source_code = 'DEPRN'
AND fadp.period_counter = fads.period_counter
AND fadp.fiscal_year < v_curr_fiscal_year
AND bk.book_type_code = v_tax_book
AND bk.asset_id = v_asset_id
AND bk.date_ineffective IS NULL
AND bk.date_placed_in_service <= fadp.calendar_period_close_date);
----transaction_header_id
----book_type_code
----asset_id
----transaction_type_code = 'TAX'
----transaction_date_entered = 31-Oct-2008 for 2008
----date_effective = sysdate
----transaction_name = comments
----
PROCEDURE GET_DEPRN_SUM(p_asset_id IN NUMBER,
p_fiscal_year IN NUMBER,
p_tax_book IN VARCHAR2,
x_deprn_amount OUT NUMBER,
x_deprn_resv OUT NUMBER,
x_return_status OUT VARCHAR2)
IS
l_period_counter_adjusted NUMBER;
l_transaction_date_entered DATE;
l_book_type_code VARCHAR2(100);
l_fiscal_year NUMBER;
dummy_num number;
dummy_char varchar2(10);
dummy_bool boolean;
l_bonus_deprn_rsv number;
l_bonus_ytd_deprn number;
l_bonus_deprn_amount number;
l_ytd_deprn2 NUMBER;
l_ytd_deprn1 NUMBER;
l_ytd_deprn NUMBER;
l_prv_period_counter NUMBER;
period_check NUMBER;
l_asset_id NUMBER;
l_return_status VARCHAR2(1);
l_fiscal_ytd_deprn NUMBER;
CURSOR ast_csr IS
SELECT asset_id,
asset_number
FROM fa_additions_b fa
WHERE asset_id = p_asset_id;
CURSOR c1(v_fiscal_year NUMBER, v_book_type_code VARCHAR2, v_asset_id NUMBER) IS
SELECT dp.period_counter,
dp.calendar_period_close_date
FROM fa_deprn_periods dp
WHERE dp.period_counter = (SELECT max(dp1.period_counter)
FROM fa_deprn_periods dp1, fa_deprn_summary ds1
WHERE dp1.fiscal_year = v_fiscal_year
and dp1.period_counter = ds1.period_counter
and dp1.book_type_code = v_book_type_code
and ds1.asset_id = v_asset_id
and ds1.book_type_code = v_book_type_code)
and dp.book_type_code = v_book_type_code;
BEGIN
l_return_status := 'S';
OPEN c1(p_fiscal_year, p_tax_book, p_asset_id);
FETCH c1 INTO l_period_counter_adjusted, l_transaction_date_entered;
IF c1%NOTFOUND THEN
l_return_status := 'E';
END IF;
CLOSE c1;
FND_FILE.PUT_LINE(FND_FILE.LOG,'DEPRN PROC ' || l_period_counter_adjusted || ' ' || l_transactioN_date_entered);
IF (l_return_status = 'S') THEN
fa_query_balances_pkg.query_balances(
X_asset_id => p_asset_id,
X_book => p_tax_book,
X_period_ctr => l_period_counter_adjusted,
X_dist_id => 0,
X_run_mode => 'ADJUSTED',
X_cost => dummy_num,
X_deprn_rsv => l_ytd_deprn2,
X_reval_rsv => dummy_num,
X_ytd_deprn => dummy_num,
X_ytd_reval_exp => dummy_num,
X_reval_deprn_exp => dummy_num,
X_deprn_exp => dummy_num,
X_reval_amo => dummy_num,
X_prod => dummy_num,
X_ytd_prod => dummy_num,
X_ltd_prod => dummy_num,
X_adj_cost => dummy_num,
X_reval_amo_basis => dummy_num,
X_bonus_rate => dummy_num,
X_deprn_source_code => dummy_char,
X_adjusted_flag => dummy_bool,
X_transaction_header_id => -1,
X_bonus_deprn_rsv => l_bonus_deprn_rsv,
X_bonus_ytd_deprn => l_bonus_ytd_deprn,
X_bonus_deprn_amount => l_bonus_deprn_amount);
FND_FILE.PUT_LINE(FND_FILE.LOG,'DEPRN2=>' || l_ytd_deprn2);
SELECT min(dp.period_counter) - 1
INTO l_prv_period_counter
FROM fa_deprn_periods dp
WHERE dp.fiscal_year = p_fiscal_year
AND dp.book_type_code =p_tax_book;
SELECT count(*) into period_check
FROM fa_deprn_periods
WHERE book_Type_code = p_tax_book
AND period_counter = l_prv_period_counter;
IF (period_check = 0) then
l_prv_period_counter := l_prv_period_counter + 1;
END IF;
fa_query_balances_pkg.query_balances(
X_asset_id => p_asset_id,
X_book => p_tax_book,
X_period_ctr => l_prv_period_counter,
X_dist_id => 0,
X_run_mode => 'ADJUSTED',
X_cost => dummy_num,
X_deprn_rsv => l_ytd_deprn1,
X_reval_rsv => dummy_num,
X_ytd_deprn => dummy_num,
X_ytd_reval_exp => dummy_num,
X_reval_deprn_exp => dummy_num,
X_deprn_exp => dummy_num,
X_reval_amo => dummy_num,
X_prod => dummy_num,
X_ytd_prod => dummy_num,
X_ltd_prod => dummy_num,
X_adj_cost => dummy_num,
X_reval_amo_basis => dummy_num,
X_bonus_rate => dummy_num,
X_deprn_source_code => dummy_char,
X_adjusted_flag => dummy_bool,
X_transaction_header_id => -1,
X_bonus_deprn_rsv => l_bonus_deprn_rsv,
X_bonus_ytd_deprn => l_bonus_ytd_deprn,
X_bonus_deprn_amount => l_bonus_deprn_amount);
FND_FILE.PUT_LINE(FND_FILE.LOG,'DEPRN1=>' || l_ytd_deprn1);
l_ytd_deprn := NVL(l_ytd_deprn2,0) - NVL(l_ytd_deprn1,0);
x_deprn_amount := NVL(l_ytd_deprn,0);
fa_query_balances_pkg.query_balances(X_asset_id => p_asset_id,
X_book => p_tax_book,
X_period_ctr => 0,
X_dist_id => 0,
X_run_mode => 'ADJUSTED',
X_cost => dummy_num,
X_deprn_rsv => x_deprn_resv,
X_reval_rsv => dummy_num,
X_ytd_deprn => dummy_num,
X_ytd_reval_exp => dummy_num,
X_reval_deprn_exp => dummy_num,
X_deprn_exp => dummy_num,
X_reval_amo => dummy_num,
X_prod => dummy_num,
X_ytd_prod => dummy_num,
X_ltd_prod => dummy_num,
X_adj_cost => dummy_num,
X_reval_amo_basis => dummy_num,
X_bonus_rate => dummy_num,
X_deprn_source_code => dummy_char,
X_adjusted_flag => dummy_bool,
X_transaction_header_id => -1,
X_bonus_deprn_rsv => l_bonus_deprn_rsv,
X_bonus_ytd_deprn => l_bonus_ytd_deprn,
X_bonus_deprn_amount => l_bonus_deprn_amount);
END IF;
x_return_status := l_return_status;
END GET_DEPRN_SUM;
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_tax_reserve_temp
SET process_status = l_return_status,
error_message = p_error_msg
WHERE rowid = p_rowid;
COMMIT;
END update_temp_tbl;
BEGIN
OPEN tax_book_csr;
FETCH tax_book_csr INTO l_tax_book;
CLOSE tax_book_csr;
OPEN curr_fiscal_csr(l_tax_book);
FETCH curr_fiscal_csr INTO l_current_fiscal_year;
CLOSE curr_fiscal_csr;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Current Fiscal Year is ' || l_Current_Fiscal_year);
l_error_message := NULL;
l_error_message1 := NULL;
FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Asset Number' || CHR(9) ||
'Fiscal Year' || CHR(9) ||
'Deprn Amount' || CHR(9) ||
'Tax Book' || CHR(9) ||
'Return Status'|| CHR(9) ||
'Error Message' );
FOR main_rec IN main_csr
LOOP
FND_FILE.PUT_LINE(FND_FILE.LOG,'Processing the Asset Number=>' || main_rec.asset_number || ', ' || main_rec.fiscal_year
|| ', ' || main_rec.tax_book);
l_return_status := 'S';
OPEN asset_csr(main_rec.asset_number);
FETCH asset_csr INTO l_asset_id;
CLOSE asset_csr;
OPEN dtl_csr(l_asset_id, l_tax_book);
FETCH dtl_csr INTO l_cost,
l_adjusted_cost,
l_date_placed_in_service,
l_prorate_date,
l_deprn_start_date,
l_ceiling_name,
l_bonus_rule,
l_deprn_method_code,
l_recoverable_cost,
l_adjusted_rate,
l_life_in_months,
l_salvage_value,
l_depreciate_flag,
l_rate_adjustment_factor,
l_reval_amortization_basis,
l_production_capacity,
l_perd_fully_reserved,
l_current_units,
l_asset_category_id,
l_asset_type;
CLOSE dtl_csr;
l_tax_book := main_rec.tax_book;
l_old_deprn_amount := 0;
OPEN check_fiscal_csr(l_asset_id , l_current_fiscal_year, l_tax_book, main_rec.fiscal_year);
FETCH check_fiscal_csr INTO l_dummy;
IF (check_fiscal_csr%NOTFOUND) THEN
l_return_status := 'T';
l_error_message := 'No Data Exists for the Given Financial Year';
FND_FILE.PUT_LINE(FND_FILE.LOG,'No Data Exists for the Given Financial Year');
END IF;
CLOSE check_fiscal_csr;
IF (l_return_status = 'S') THEN
OPEN trx_date_csr(l_asset_id, main_rec.fiscal_year, l_tax_book);
FETCH trx_date_csr INTO l_trx_date;
CLOSE trx_Date_csr;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Transaction DAte for the Fiscal Year is ' || l_trx_date);
l_dummy := NULL;
OPEN check_trx(l_asset_id , l_tax_book , l_trx_date);
FETCH check_trx INTO l_dummy;
CLOSE check_trx;
IF (l_dummy IS NOT NULL) THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'This Asset is Already processed for the given financial Year');
l_return_status := 'D';
l_error_message := 'Asset Already Processed Previously';
END IF;
END IF;
IF (l_return_status = 'S') THEN
GET_DEPRN_SUM(p_asset_id => l_asset_id,
p_fiscal_year => main_rec.fiscal_year,
p_tax_book => l_tax_book,
x_deprn_amount => l_old_deprn_amount,
x_deprn_resv => l_deprn_resv,
x_return_status => l_return_status);
IF (l_return_status <> 'S') THEN
l_return_status := 'D';
l_error_message := 'You did not depreciate assets during this fiscal year. Please perform your depreciation adjustment in a later fiscal year.';
FND_FILE.PUT_LINE(FND_FILE.LOG,'You did not depreciate assets during this fiscal year. Please perform your depreciation adjustment in a later fiscal year.');
ELSE
FND_FILE.PUT_LINE(FND_FILE.LOG,'Old Depreciation Amount is ' || l_old_deprn_amount);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Deprn Reserve is ' || l_deprn_resv);
IF ABS(main_rec.deprn_amount - l_old_deprn_amount + l_deprn_resv) > ABS(l_recoverable_cost) THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Depreciation Cannot Exceeed the Cost');
l_Error_message := 'Depreciation Cannot Exceeed the Cost';
l_return_status := 'C';
END IF;
END IF;
END IF;
IF (l_return_status = 'S') THEN
IF (l_old_deprn_amount = main_rec.deprn_amount) THEN
FND_FILE.PUT_LINE(FND_FILE.LOG, 'The Old Depreciation Amount ' || l_old_deprn_amount || ' is Same as the New Amount. Not Proceeding Further' );
l_Error_message := 'The Old Depreciation Amount ' || l_old_deprn_amount || ' is Same as the New Amount. Not Proceeding Further';
l_return_status := 'P';
ELSE
FND_FILE.PUT_LINE(FND_FILE.LOG, 'The Old Depreciation Amount ' || l_old_deprn_amount || ' is Different then the New Amount. Proceeding Further');
l_Error_message := NULL;
l_return_status := 'S';
END IF;
END IF;
IF (l_return_status = 'S') THEN
OPEN retire_csr(l_asset_id, l_tax_book);
FETCH retire_csr INTO l_retire_count;
CLOSE retire_csr;
IF (l_retire_count > 0) THEN
l_return_status := 'R';
FND_FILE.PUT_LINE(FND_FILE.LOG,'You cannot perform a Reserve Adjustment on a fully retired asset.');
l_Error_message := 'You cannot perform a Reserve Adjustment on a fully retired asset.';
ELSE
FND_FILE.PUT_LINE(FND_FILE.LOG,'Asset is Not Retired, Proceeding Further');
l_error_message := NULL;
END IF;
END IF;
IF (l_return_status = 'S') THEN
SELECT fa_transaction_headers_s.nextval
INTO l_transaction_header_id
FROM dual;
OPEN trx_date_csr(l_asset_id, main_rec.fiscal_year, l_tax_book);
FETCH trx_date_csr INTO l_trx_date;
CLOSE trx_date_csr;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Inserting Transaction in FA Transaction Header');
FA_TRANSACTION_HEADERS_PKG.insert_row(X_ROWID => l_rowid,
X_TRANSACTION_HEADER_ID => l_transaction_header_id,
X_BOOK_TYPE_CODE => l_tax_book,
X_ASSET_ID => l_asset_id,
X_TRANSACTION_TYPE_CODE => 'TAX',
X_TRANSACTION_DATE_ENTERED => l_trx_date,
X_DATE_EFFECTIVE => sysdate,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => fnd_global.user_id,
X_RETURN_STATUS => x_return_status,
X_CALLING_FN => 'CUSTOM');
IF (x_return_status) THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Transaction Created Successfully');
l_return_status := 'S';
l_error_message := NULL;
ELSE
FND_FILE.PUT_LINE(FND_FILE.LOG,'Could not Create Transaction ');
l_error_message := 'Could not Create Transaction ';
l_return_status := 'E';
END IF;
IF (l_return_status = 'S') THEN
l_faxcat := FA_TRX_APPROVAL_PKG.faxcat(
X_book => 'NFC TAX BOOK',
X_asset_id => l_asset_id,
X_trx_type => 'TAX',
X_trx_date => sysdate,
X_init_message_flag => 'NO');
IF (l_faxcat) THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'FaxCat Success');
ELSE
FND_FILE.PUT_LINE(FND_FILE.LOG,'FaxCat Error');
END IF;
h_status := fa_txrsv_pkg.faxtxa (X_ASSET_NUMBER => l_asset_number,
X_ASSET_ID => l_asset_id,
X_CURRENT_UNITS => l_current_units ,
X_ASSET_CATEGORY_ID => l_asset_category_id ,
X_ASSET_TYPE => l_asset_type ,
X_NEW_COST => l_cost ,
X_SALVAGE_VALUE => l_salvage_value ,
X_NEW_RECOVERABLE_COST => l_recoverable_cost ,
X_RECOVERABLE_COST => l_recoverable_cost ,
X_ADJUSTED_RATE => l_adjusted_rate ,
X_CEILING_NAME => l_ceiling_name ,
X_BONUS_RULE => l_bonus_rule ,
X_DEPRN_METHOD_CODE => l_deprn_method_code,
X_LIFE_IN_MONTHS => l_life_in_months ,
X_DATE_PLACED_IN_SERVICE => l_date_placed_in_service ,
X_PRORATE_DATE => l_prorate_date ,
X_DEPRN_START_DATE => l_deprn_start_date ,
X_DEPRECIATE_FLAG => l_depreciate_flag ,
X_PRODUCTION_CAPACITY => l_production_capacity,
X_BOOK_TYPE_CODE => l_tax_book,
X_TRANSACTION_HEADER_ID => l_transaction_header_id ,
X_RATE_ADJUSTMENT_FACTOR => l_rate_adjustment_factor ,
X_REVAL_AMORTIZATION_BASIS => l_reval_amortization_basis ,
X_OLD_DEPRN_RSV => l_old_deprn_amount,
X_NEW_DEPRN_RSV => main_rec.deprn_amount,
X_FISCAL_YEAR => main_rec.fiscal_year,
X_PERIOD_COUNTER_RSV => l_perd_fully_reserved ,
X_SYSDATE_VAL => sysdate );
IF (h_status) THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'faxtxa proc is Success');
l_return_status := 'S';
ELSE
FND_FILE.PUT_LINE(FND_FILE.LOG,'faxtxa proc is Error');
l_error_message := 'faxtxa proc is Error';
l_return_status := 'E';
END IF;
ELSE
FND_FILE.PUT_LINE(FND_FILE.LOG,'Transaction was not successful, not proceeding further');
END IF;
END IF;
IF (l_return_status = 'E') 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
l_error_message := l_error_message || '|' || l_error_tbl(i);
END LOOP;
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.fiscal_year
|| CHR(9) || main_rec.deprn_amount
|| CHR(9) || main_rec.tax_book
|| CHR(9) || l_return_status
|| CHR(9) || l_error_message);
END LOOP; ---of Assets
END XXX_TAX_RESERVE_ADJUST;
Subscribe to:
Posts (Atom)