#=======================================================================
# Writer : Lokanadham Thandlam
#=======================================================================
CREATE OR REPLACE PACKAGE APPS.XX_FA_IMPORT_PKG
IS
PROCEDURE XX_FA_IMPORT_PROC (ERRBUF OUT VARCHAR2, RETCODE OUT VARCHAR2);
END;
/
CREATE OR REPLACE PACKAGE BODY APPS.XX_FA_IMPORT_PKG
IS
PROCEDURE XX_FA_IMPORT_PROC (ERRBUF OUT VARCHAR2, RETCODE OUT VARCHAR2)
IS
L_CATEGORY_ID NUMBER;
L_EXP_CCID NUMBER;
L_LOCATION_ID NUMBER (15);
L_STATUS VARCHAR2 (50) := 'S';
L_ERROR_MESSAGE VARCHAR2 (240);
L_COUNT NUMBER := 0;
L_SALVAGE_VAL NUMBER;
L_COUNT1 NUMBER := 0;
L_LIFE_IN_MONTHS NUMBER;
L_ASSET_KEY_CCID NUMBER (15);
L_CATEGORY VARCHAR2 (100);
L_DEPRN_METHOD VARCHAR2 (100);
L_BASIC_RATE NUMBER;
L_ADJUSTED_RATE NUMBER;
L_ASSET_NUMBER VARCHAR2 (15);
L_CT NUMBER;
CURSOR C1
IS
SELECT A.ROWID, A.*
FROM XX_FA_STG A
WHERE A.STATUS = 'N';
BEGIN
FOR CREC IN C1
LOOP
L_STATUS := 'S';
L_COUNT := L_COUNT + 1;
-- ===================================================================
-- Checking for Duplicate Asset Number
-- ===================================================================
BEGIN
SELECT 1
INTO L_CT
FROM DUAL
WHERE NOT EXISTS
(SELECT asset_number
FROM fa_mass_Additions
WHERE asset_number = CREC.ASSET_NUMBER);
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'Asset Number : ' || CREC.ASSET_NUMBER || ' Is Unique');
EXCEPTION
WHEN NO_DATA_FOUND
THEN
L_STATUS := 'E';
L_ERROR_MESSAGE := 'Asset Number Is Alredy Exist';
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'Asset Number : '
|| CREC.ASSET_NUMBER
|| ' Is Alredy Exist');
WHEN OTHERS
THEN
L_STATUS := 'E';
L_ERROR_MESSAGE := 'Unhandled Exception';
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'Unhandled exception while checking for duplicate for Asset Number : '
|| CREC.ASSET_NUMBER);
END;
-- ===================================================================
-- Fetching the category id (major and minor category)
-- ===================================================================
BEGIN
SELECT CATEGORY_ID
INTO L_CATEGORY_ID
FROM FA_CATEGORIES_B
WHERE UPPER (TRIM (SEGMENT1)) =
UPPER (TRIM (CREC.MAJOR_CATEGORY))
AND UPPER (TRIM (SEGMENT2)) =
UPPER (TRIM (CREC.MINOR_CATEGORY))
AND ENABLED_FLAG = 'Y'
AND END_DATE_ACTIVE IS NULL;
FND_FILE.PUT_LINE (FND_FILE.LOG,
'Category ID is ' || L_CATEGORY_ID);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
L_STATUS := 'E';
L_ERROR_MESSAGE := 'Category ID Not Found';
FND_FILE.PUT_LINE (FND_FILE.LOG,
'No data found for category ID in EBS');
WHEN OTHERS
THEN
L_STATUS := 'E';
L_ERROR_MESSAGE := 'Unhandled Exception';
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'Unhandled exception while fetching the Category ID');
END;
-- =========================================================
-- Fetching the expense code combination id
-- =========================================================
-- BEGIN
-- SELECT CODE_COMBINATION_ID
-- INTO L_EXP_CCID
-- FROM GL_CODE_COMBINATIONS
-- WHERE CODE_COMBINATION_ID=trim(CREC.EXP_ACC_CCID);
-- EXCEPTION
-- WHEN NO_DATA_FOUND THEN
-- L_STATUS:='E';
-- L_ERROR_MESSAGE:='Expense CCID Not Found';
-- DBMS_OUTPUT.PUT_LINE('EXPENSE CODE COMBINATION ID NOT EXISTS FOR THIS COMBINATION');
-- WHEN OTHERS THEN
-- L_STATUS:='E';
-- L_ERROR_MESSAGE:='Unhandled Exception';
-- DBMS_OUTPUT.PUT_LINE('Internal Error Occured at Expense CCID');
-- END;
----------------------------------------------------------------------------
BEGIN
SELECT LOCATION_ID
INTO L_LOCATION_ID
FROM FA_LOCATIONS
WHERE 1 = 1
AND UPPER (SEGMENT1) = UPPER (TRIM (CREC.CITY))
AND UPPER (SEGMENT2) = UPPER (TRIM (CREC.AREA))
AND UPPER (SEGMENT3) = UPPER (TRIM (CREC.BUILDING))
AND ENABLED_FLAG = 'Y'
AND END_DATE_ACTIVE IS NULL;
FND_FILE.PUT_LINE (FND_FILE.LOG,
'Location ID is ' || L_LOCATION_ID);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
L_STATUS := 'E';
L_ERROR_MESSAGE := 'Location ID Not Found';
FND_FILE.PUT_LINE (FND_FILE.LOG,
'No data found for location ID in EBS');
WHEN OTHERS
THEN
L_STATUS := 'E';
L_ERROR_MESSAGE := 'Unhandled Exception';
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'Unhandled exception while fetching the Location ID');
END;
-- ======================================================================
-- Fetching Asset Key CCID
-- =======================================================================
BEGIN
SELECT CODE_COMBINATION_ID
INTO L_ASSET_KEY_CCID
FROM FA_ASSET_KEYWORDS
WHERE SEGMENT1 = CREC.ASSET_KEY
AND END_DATE_ACTIVE IS NULL
AND ENABLED_FLAG = 'Y';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
L_STATUS := 'E';
L_ERROR_MESSAGE := 'Asset Key CCID Not Found';
FND_FILE.PUT_LINE (FND_FILE.LOG,
'No data found for Asset Key CCID in EBS');
WHEN OTHERS
THEN
L_STATUS := 'E';
L_ERROR_MESSAGE := 'Unhandled Exception';
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'Unhandled exception while fetching the Asset Key CCID');
END;
BEGIN
SELECT DEPRN_EXPENSE_ACCOUNT_CCID
INTO L_EXP_CCID
FROM FA_CATEGORY_BOOKS
WHERE CATEGORY_ID = L_CATEGORY_ID
AND BOOK_TYPE_CODE = 'MALAWI BOOK';
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'DEPRN_EXPENSE_ACCOUNT_CCID is ' || L_EXP_CCID);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
L_STATUS := 'E';
L_ERROR_MESSAGE := 'No Data Found for Deprn Account';
FND_FILE.PUT_LINE (FND_FILE.LOG,
'No data found for Expense CCID in EBS');
WHEN OTHERS
THEN
L_STATUS := 'E';
L_ERROR_MESSAGE := 'Unhandled Exception';
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'Unhandled exception while fetching the Expense CCID');
END;
-- ======================================================================
-- DEPRN_METHOD_CODE VALIDATION BASIC_RATE, ADJUSTED_RATE FETCHING
-- =======================================================================
-- IF CREC.DEPRN_METHOD IS NOT NULL THEN
--
-- BEGIN
-- SELECT METHOD_CODE,LIFE_IN_MONTHS INTO L_DEPRN_METHOD,L_LIFE_IN_MONTHS
-- FROM FA_METHODS
-- WHERE trim(METHOD_CODE)=trim(CREC.DEPRN_METHOD);
-- EXCEPTION
-- WHEN NO_DATA_FOUND THEN
-- L_STATUS:='E';
-- L_ERROR_MESSAGE:='Deprn Method Not Defined';
-- FND_FILE.PUT_LINE(FND_FILE.LOG,'DEPRN_METHOD IS NOT DEFINED in EBS');
-- WHEN OTHERS THEN
-- L_STATUS:='E';
-- L_ERROR_MESSAGE:='Unhandled Exception';
-- FND_FILE.PUT_LINE(FND_FILE.LOG,'Unhandled exception while validating DEPRN_METHOD');
-- END;
-- END IF;
--
-- ======================================================================
-- BASIC_RATE, ADJUSTED_RATE FETCHING
-- =======================================================================
IF L_DEPRN_METHOD IS NOT NULL
THEN
BEGIN
SELECT DISTINCT BASIC_RATE,
ADJUSTED_RATE,
DEPRN_METHOD,
LIFE_IN_MONTHS
INTO L_BASIC_RATE,
L_ADJUSTED_RATE,
L_DEPRN_METHOD,
L_LIFE_IN_MONTHS
FROM FA_CATEGORY_BOOK_DEFAULTS
WHERE 1 = 1 --DEPRN_METHOD= L_DEPRN_METHOD
AND CATEGORY_ID = L_CATEGORY_ID;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
L_STATUS := 'E';
L_ERROR_MESSAGE :=
'Basic Rate and Adjust Rate is not defined in system';
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'BASIC_RATE, ADJUSTED_RATE IS NOT DEFINED in EBS');
WHEN OTHERS
THEN
L_STATUS := 'E';
L_ERROR_MESSAGE := 'Unhandled Exception';
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'Unhandled exception while validating DEPRN_METHOD');
END;
END IF;
--- =======================================================================
IF L_STATUS = 'S'
THEN
BEGIN
INSERT INTO FA_MASS_ADDITIONS (MASS_ADDITION_ID,
ASSET_NUMBER,
DESCRIPTION,
ASSET_CATEGORY_ID,
BOOK_TYPE_CODE,
DATE_PLACED_IN_SERVICE,
FIXED_ASSETS_COST,
PAYABLES_UNITS,
FIXED_ASSETS_UNITS,
LOCATION_ID,
CREATE_BATCH_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
POSTING_STATUS,
QUEUE_NAME,
PAYABLES_COST,
ASSET_TYPE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
DEPRECIATE_FLAG,
INVENTORIAL,
ASSET_KEY_CCID,
EXPENSE_CODE_COMBINATION_ID,
LIFE_IN_MONTHS,
DEPRN_METHOD_CODE,
BASIC_RATE,
ADJUSTED_RATE,
IN_USE_FLAG,
DEPRN_RESERVE,
YTD_DEPRN,
TAG_NUMBER)
VALUES (FA_MASS_ADDITIONS_S.NEXTVAL,
CREC.ASSET_NUMBER,
CREC.DESCRIPTION,
L_CATEGORY_ID,
'XXX BOOK', --Change the Book Name here--Lokanadham
CREC.DATE_PI_SERVICE,
CREC.ASSET_COST,
1, --CREC.NO_OF_UNITS,
1, --CREC.NO_OF_UNITS,
L_LOCATION_ID,
1,
SYSDATE,
Fnd_Global.USER_ID, --1110,
'POST',
'POST',
CREC.ASSET_COST,
CREC.ASSET_TYPE,
Fnd_Global.USER_ID, --1110,
SYSDATE,
1,
'YES',
'YES',
L_ASSET_KEY_CCID,
L_EXP_CCID,
CREC.LIFE_IN_MONTHS, --L_LIFE_IN_MONTHS,
L_DEPRN_METHOD,
L_BASIC_RATE,
L_ADJUSTED_RATE,
'YES',
CREC.DEPRN_RESERVE,
CREC.YTD_DEPRN,
TRIM (CREC.TAG_NUMBER) --CREC.IN_USE
);
COMMIT;
FND_FILE.PUT_LINE (FND_FILE.LOG,
L_COUNT || '. Record Inserted');
L_COUNT1 := L_COUNT1 + 1;
UPDATE XX_FA_STG
SET STATUS = 'P'
WHERE ROWID = CREC.ROWID;
EXCEPTION
WHEN OTHERS
THEN
FND_FILE.PUT_LINE (
FND_FILE.LOG,
L_COUNT
|| '. insert failed code: '
|| SQLCODE
|| ' Error message: '
|| SQLERRM);
END;
ELSE
FND_FILE.PUT_LINE (FND_FILE.LOG, L_COUNT || '.Error Occured');
UPDATE XX_FA_STG
SET STATUS = 'E', ERROR_MESSAGE = L_ERROR_MESSAGE
WHERE ROWID = CREC.ROWID;
END IF;
END LOOP;
FND_FILE.PUT_LINE (FND_FILE.LOG, 'OUT OF THE LOOP');
FND_FILE.PUT_LINE (FND_FILE.LOG,
'Total Records Inserted :' || L_COUNT1);
END;
END;
/