Fa_interface

CREATE TABLE XX_ASSETMARVELL_TX_CONV
(
SNO NUMBER,
ASSET_NO VARCHAR2(2000 BYTE),
DATE_CAPITAL DATE,
ORIGINAL_COST VARCHAR2(200 BYTE),
ACC_DEP VARCHAR2(200 BYTE),
YTD_DEP VARCHAR2(200 BYTE),
NET_BOOK_VALUE VARCHAR2(200 BYTE),
SALVAGE_VALUE VARCHAR2(200 BYTE),
DEP_METHOD VARCHAR2(200 BYTE),
YEARS_OF_LIFE VARCHAR2(200 BYTE),
PRORATE_CONVENTION VARCHAR2(200 BYTE),
FA_BOOKS VARCHAR2(15 BYTE),
PROCESS_FLAG VARCHAR2(1 BYTE),
ERROR_MESSAGE VARCHAR2(4000 BYTE),
ATTRIBUTE1 VARCHAR2(200 BYTE),
ATTRIBUTE2 VARCHAR2(200 BYTE)
)
GRANT ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT, UPDATE ON XX_ASSETMARVELL_TX_CONV TO APPS WITH GRANT OPTION;
/
GRANT ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT, UPDATE ON XX_ASSETMARVELL_TX_CONV TO PUBLIC;
/
CREATE SYNONYM APPS.XX_ASSETMARVELL_TX_CONV FOR MVL_LNT.XX_ASSETMARVELL_TX_CONV;
/
CREATE PUBLIC SYNONYM XX_ASSETMARVELL_TX_CONV FOR MVL_LNT.XX_ASSETMARVELL_TX_CONV;
/
***************************
Step-2
------

options (skip=2)
LOAD DATA
INFILE "/bsa1/prod/prodappl/xxgt/11.5.0/bin/Asset_Marvell_TX_CA_ACE.txt"
INFILE "/bsa1/prod/prodappl/xxgt/11.5.0/bin/Asset_Marvell_TX_CA_AMT.txt"
INFILE "/bsa1/prod/prodappl/xxgt/11.5.0/bin/Asset_Marvell_TX_CA_REG.txt"
INFILE "/bsa1/prod/prodappl/xxgt/11.5.0/bin/Asset_Marvell_TX_FED_ACE.txt"
INFILE "/bsa1/prod/prodappl/xxgt/11.5.0/bin/Asset_Marvell_TX_FED_AMT.txt"
INFILE "/bsa1/prod/prodappl/xxgt/11.5.0/bin/Asset_Marvell_TX_FED_REG.txt"
INFILE "/bsa1/prod/prodappl/xxgt/11.5.0/bin/Asset_Marvell_TX_OTH_STT.txt"
APPEND
INTO TABLE MVL_LNT.XX_ASSETMARVELL_TX_CONV
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
( SNO ,
ASSET_NO ,
DATE_CAPITAL "TO_DATE(:DATE_CAPITAL)" ,
ORIGINAL_COST "TO_NUMBER(:ORIGINAL_COST,'999999999.99')" ,
ACC_DEP"TO_NUMBER(:ACC_DEP,'999999999.99')" ,
YTD_DEP ,
NET_BOOK_VALUE ,
SALVAGE_VALUE ,
DEP_METHOD ,
YEARS_OF_LIFE ,
PRORATE_CONVENTION,
FA_BOOKS ,
PROCESS_FLAG ,
ERROR_MESSAGE ,
ATTRIBUTE1 ,
ATTRIBUTE2
)
*************************************
Step-3
--------

CONN &username/&password

CREATE OR REPLACE package MVL_LNT.XX_ASSETMARVELL_TX_CONV_PKG
as
procedure ASSETMARVELL_TX_CONV ( errbuf out varchar2,
retcode out varchar2,
P_FA_BOOKS VARCHAR2);
END XX_ASSETMARVELL_TX_CONV_PKG;
/

CREATE OR REPLACE package body MVL_LNT.XX_ASSETMARVELL_TX_CONV_PKG
as
procedure ASSETMARVELL_TX_CONV (errbuf out varchar2,
retcode out varchar2,
P_FA_BOOKS VARCHAR2)
is
cursor XX_ASSETMARVELL_TX_CONV_CURSOR (P_FA_BOOKS VARCHAR2)
is
select * from XX_ASSETMARVELL_TX_CONV WHERE substr(FA_BOOKS,1,14)=P_FA_BOOKS
and asset_no is not null ;
BEGIN
----------------------------------------------
DELETE from XX_ASSETMARVELL_TX_CONV I WHERE substr(I.FA_BOOKS,1,14)=P_FA_BOOKS
and I.asset_no is not null
and rowid>(select min(rowid)from XX_ASSETMARVELL_TX_CONV A
WHERE A.ASSET_NO=I.ASSET_NO AND A.FA_BOOKS=I.FA_BOOKS);
----------------------------------------
FOR I in XX_ASSETMARVELL_TX_CONV_CURSOR(P_FA_BOOKS)
LOOP

insert into apps.fa_tax_interface(
ASSET_NUMBER
,COST
,YTD_DEPRN
,SALVAGE_VALUE
,posting_status
,LIFE_IN_MONTHS
,DEPRN_METHOD_CODE
,BOOK_TYPE_CODE
,DATE_PLACED_IN_SERVICE
,PRORATE_CONVENTION_CODE
,ADJUSTED_RATE
,AMORTIZE_NBV_FLAG
,DEPRN_RESERVE
)
VALUES( I.ASSET_NO
,I.ORIGINAL_COST
,nvl(decode(instr(I.YTD_DEP,'-'),0,to_number(I.YTD_DEP)),0)
,nvl(decode(instr(I.SALVAGE_VALUE,'-'),0,to_number(I.SALVAGE_VALUE)),0)
,'POST'
,I.YEARS_OF_LIFE*12
,SUBSTR(I.DEP_METHOD,1,12)
,SUBSTR(I.FA_BOOKS,1,14)
,I.DATE_CAPITAL
,UPPER(I.PRORATE_CONVENTION)
,I.ACC_DEP
,'Y'
,I.ACC_DEP);
-- fnd_file.put_line(FND_FILE.log,I.ASSET_NO);

begin
dbms_output.put_line ('ASSET NO' || I.ASSET_NO ||' - '|| I.FA_BOOKS);
fnd_file.put_line (FND_FILE.log,'ASSET NO' || I.ASSET_NO ||' - '|| I.FA_BOOKS);
EXCEPTION
when others then
dbms_output.put_line('Error IN TABLE' ||sqlerrm);
end;
END LOOP;
COMMIT;
End ASSETMARVELL_TX_CONV;
END XX_ASSETMARVELL_TX_CONV_PKG;
/
CREATE SYNONYM XX_ASSETMARVELL_TX_CONV_PKG FOR MVL_LNT.XX_ASSETMARVELL_TX_CONV_PKG;
GRANT SYNONYM XX_ASSETMARVELL_TX_CONV_PKG FOR MVL_LNT.XX_ASSETMARVELL_TX_CONV_PKG
*********************