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,
'MALAWI BOOK',
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;
/
No comments:
Post a Comment