CREATE TABLE ASSET_TRANSFER_HDR (
TRAN_ID NOT NULL CHAR(10),                                                                                                                                                                                     
TRAN_DATE NOT NULL DATE,                                                                                                                                                                                          
XFR_TYPE NOT NULL CHAR(1),                                                                                                                                                                                       
SITE_CODE__FROM NOT NULL CHAR(5),                                                                                                                                                                                       
SITE_CODE__TO NOT NULL CHAR(5),                                                                                                                                                                                       
CONFIRMED  CHAR(1),                                                                                                                                                                                       
CONFIRM_DATE DATE,                                                                                                                                                                                          
EMP_CODE__APRV CHAR(10),                                                                                                                                                                                      
TRAN_ID__ISS CHAR(10),                                                                                                                                                                                      
LINK_TYPE NOT NULL CHAR(1),                                                                                                                                                                                       
CHG_DATE NOT NULL DATE ,                                                                                                                                                                                         
CHG_USER NOT NULL CHAR(10),
CHG_TERM NOT NULL CHAR(15) 

)  TABLESPACE ITM_FIN_D2;

ALTER TABLE ASSET_TRANSFER_HDR ADD CONSTRAINT ASSET_TRANSFER_HDR_X PRIMARY KEY (TRAN_ID) USING INDEX TABLESPACE ITM_FIN_D2;
ALTER TABLE ASSET_TRANSFER_HDR ADD CONSTRAINT FK_ASSET_TRANSFER_HDR_F FOREIGN KEY (SITE_CODE__FROM) REFERENCES SITE (SITE_CODE);
ALTER TABLE ASSET_TRANSFER_HDR ADD CONSTRAINT FK_ASSET_TRANSFER_HDR_T FOREIGN KEY (SITE_CODE__TO) REFERENCES SITE (SITE_CODE);
ALTER TABLE ASSET_TRANSFER_HDR ADD CONSTRAINT FK_ASSET_TRANSFER_HDR_E FOREIGN KEY (EMP_CODE__APRV) REFERENCES EMPLOYEE (EMP_CODE);


CREATE TABLE ASSET_TRANSFER_DET (

TRAN_ID                        NOT NULL CHAR(10),
LINE_NO                        NOT NULL NUMBER(3),
ASSET_CODE__FROM               NOT NULL CHAR(15),
ASSET_CODE__TO                          CHAR(15),
ITEM_SER__FROM                 NOT NULL CHAR(5),
ITEM_SER__TO                            CHAR(5),
GRP_CODE__FROM                 NOT NULL CHAR(5),
GRP_CODE__TO                            CHAR(5),
ALOC_CODE__FROM                         CHAR(5),
ALOC_CODE__TO                           CHAR(5),
CCTR_CODE__FROM                         CHAR(4),
CCTR_CODE__TO                           CHAR(4),
ASSET_CODE__PAR_FROM                    CHAR(15),
ASSET_CODE__PAR_TO                      CHAR(15),
DEPR_TYPE__FROM                         CHAR(1),
DEPR_TYPE__TO                           CHAR(1),
ASSET_TYPE                              CHAR(1),
TRAN_ID__CREATE                         CHAR(10),
TRAN_SER__CREATE                        CHAR(10),
BILL_NO                                 CHAR(15),
SUPP_CODE                               CHAR(10),
SUPP_NAME                               CHAR(40),
ITEM_CODE                               CHAR(10),
RCP_DATE                                DATE,                                                                                                                                                                                          
INST_DATE                               DATE, 
CURR_CODE                               CHAR(5),
EXCH_RATE                               NUMBER(14,6),
ORGINAL_VALUE                           NUMBER(14,3),
OTHER_CHGS                              NUMBER(14,3),
TAX_AMT                                 NUMBER(14,3),                                                                                                                                                                                 
TAX_RECO_AMT                            NUMBER(14,3),
DIFF_AMT__EXCH                          NUMBER(14,3),
EXCISE_AMT                              NUMBER(14,3),
COMM_CHGS                               NUMBER(14,3),
INSTALL_CHGS                            NUMBER(14,3),
OCTROI_CHGS                             NUMBER(14,3),                                                                                                                                                                                  
INSUR_CHGS                              NUMBER(14,3),
TOTAL_VALUE                             NUMBER(14,3),
REVALUE_AMT                             NUMBER(14,3),
REVALUE_DATE                            DATE,
USE_DATE                                DATE,
DEPR_PERC_1                             NUMBER(6,2),
DEPR_METHOD_1                           CHAR(1),
DEPR_AMT_1                              NUMBER(14,3),
WDV_AMT_1                               NUMBER(14,3),
DEPR_PERC_2                             NUMBER(6,2),
DEPR_METHOD_2                           CHAR(1),
DEPR_AMT_2                              NUMBER(14,3),
WDV_AMT_2                               NUMBER(14,3),
ASSET_CODE__LINK                        CHAR(15),
PROFIT_LOSS                             NUMBER(14,3),
TOTAL_VALUE__BC                         NUMBER(14,3),
ACCT_PRD__PROC                          CHAR(6),
PARENT__TRAN_ID                         CHAR(10),
VERIFIED_ON                             DATE,
EMP_CODE__VERIFY                        CHAR(10),
REMARKS                                 CHAR(60),
WDV_AMT_REVL_2                          NUMBER(14,3),
DEPR_AMT_REVL_2                         NUMBER(14,3),
PROC_DATE_1                             DATE,
PROC_DATE_2                             DATE,
)TABLESPACE ITM_FIN_D2;

ALTER TABLE ASSET_TRANSFER_DET ADD CONSTRAINT spl_sales_por_det_X PRIMARY KEY (TRAN_ID,LINE_NO) USING INDEX TABLESPACE ITM_FIN_D2;



insert into obj_forms (WIN_NAME,TITLE,
OBJ_BROW_NAME,OBJ_EDIT_NAME,FORM_NO)
values('w_asset_transfer','Detail',
'fixed_asset_transfer12','fixed_asset_transfer22','2');

update obj_forms set OBJ_BROW_NAME='fixed_asset_transfer11',OBJ_EDIT_NAME='fixed_asset_transfer21' where WIN_NAME='w_asset_transfer';

update system_events set service_code='asset_xfr', comp_name='FixedAssetTransfConf',comp_type ='EJB' where obj_name='asset_transfer' and 
event_code ='pre_confirm';

update system_events set comp_name='FixedAssetTransf',comp_type ='EJB' where obj_name='asset_transfer' and 
(event_code ='post_item_change' or event_code='pre_validate');

insert into system_event_services(SERVICE_CODE,SERVICE_DESCR,SERVICE_URI,SERVICE_PROVIDER,METHOD_NAME,RETURN_VALUE,RETURN_TYPE,CHG_DATE,CHG_USER,CHG_TERM)
values ('asset_xfr','Action for Asset Transfer confirmation','http://localhost:8080/axis/services/ActionHandlerService','Base Information','confirm','String',
'S',sysdate,'Base','Base');

	
insert into system_service_args(SERVICE_CODE,LINE_NO,ARG_NAME,ARG_MODE,DESCR,ARG_TYPE,CHG_DATE,CHG_USER,CHG_TERM,ARG_VALUE)
values ('asset_xfr',1,'COMPONENT_TYPE','I','Component Type','S',sysdate,'Base','Base','EJB');

insert into system_service_args(SERVICE_CODE,LINE_NO,ARG_NAME,ARG_MODE,DESCR,ARG_TYPE,CHG_DATE,CHG_USER,CHG_TERM,ARG_VALUE)
values ('asset_xfr',2,'COMPONENT_NAME','I','Component name','S',sysdate,'Base','Base','FixedAssetTransfConf');


insert into system_service_args(SERVICE_CODE,LINE_NO,ARG_NAME,ARG_TYPE,CHG_DATE,CHG_USER,CHG_TERM)
values ('asset_xfr',3,'<tran_id>','B.String',sysdate,'Base','Base');


insert into system_service_args(SERVICE_CODE,LINE_NO,ARG_NAME ,DESCR,ARG_TYPE,CHG_DATE, CHG_USER,CHG_TERM)                                       
values ('asset_xfr',4,'XTRA_PARAMS','params','S',sysdate,'Base','Base');

insert into system_service_args(SERVICE_CODE,LINE_NO,ARG_NAME ,DESCR,ARG_TYPE,CHG_DATE, CHG_USER,CHG_TERM)                                           
values ('asset_xfr',5,'FORCED_FLAG','flag','S',sysdate,'Base','Base');

update transetup set key_string ='_spsite_code__to,seq05' where tran_window='w_asset_transfer';

insert into obj_itemchange (obj_name,form_no,field_name,mandatory)
values('asset_transfer','1','itm_default',null); 

insert into obj_itemchange (obj_name,form_no,field_name,mandatory)
values('asset_transfer','1','site_code__from',null); 

update obj_itemchange set field_name='site_code__to' where obj_name='asset_transfer' and field_name='site_code__new';

insert into obj_itemchange (obj_name,form_no,field_name,mandatory)
values('asset_transfer','2','asset_code__from',null); 

delete from obj_itemchange where obj_name='asset_transfer' and (field_name='item_ser__new' or field_name='grp_code__new');

insert into obj_itemchange (obj_name,form_no,field_name,mandatory)
values('asset_transfer','2','item_ser__to',null);

insert into obj_itemchange (obj_name,form_no,field_name,mandatory)
values('asset_transfer','2','grp_code__to',null);


insert into messages
(msg_no,msg_str,msg_descr,msg_type,chg_date,chg_user,chg_term) 
values 
('VTITEMSER1','Item Series Not Found!','Item series you have entered is not found in the item series master.','E',sysdate,'BASE','BASE');

insert into messages
(msg_no,msg_str,msg_descr,msg_type,chg_date,chg_user,chg_term) 
values 
('VTPGRPCD1 ','Invalid Group Code!','Group Code entered is not found in Group Master.','E',sysdate,'BASE','BASE');

insert into messages
(msg_no,msg_str,msg_descr,msg_type,chg_date,chg_user,chg_term) 
values 
('VTALOC','Asset location is incorrect','The asset location is not valid.','E',sysdate,'BASE','BASE');

insert into messages
(msg_no,msg_str,msg_descr,msg_type,chg_date,chg_user,chg_term) 
values 
('VTCCTRCDPL','Invalid Cost Centre Code','Cost Centre not defined in master.','E',sysdate,'BASE','BASE');

insert into messages
(msg_no,msg_str,msg_descr,msg_type,chg_date,chg_user,chg_term) 
values 
('VTDEPRTP','Invalid Cost Centre Code','Please select Depr Type','E',sysdate,'BASE','BASE');


insert into messages
(msg_no,msg_str,msg_descr,msg_type,chg_date,chg_user,chg_term) 
values 
('VTASSETCE','Old Asset Code is empty','Old Asset Code can not be blank','E',sysdate,'BASE','BASE');

insert into messages
(msg_no,msg_str,msg_descr,msg_type,chg_date,chg_user,chg_term) 
values 
('VTASSETCD','Invalid Old Asset Code','Entered Old Asset Code is not defined in asset register master','E',sysdate,'BASE','BASE');

insert into messages
(msg_no,msg_str,msg_descr,msg_type,chg_date,chg_user,chg_term) 
values 
('VTASETNND','New Asset Code is empty','New Asset Code can not be blank','E',sysdate,'BASE','BASE');

insert into messages
(msg_no,msg_str,msg_descr,msg_type,chg_date,chg_user,chg_term) 
values 
('VTASETNND','Invalid New Asset Code','Entered New Asset Code is not defined in asset register master','E',sysdate,'BASE','BASE');


insert into messages
(msg_no,msg_str,msg_descr,msg_type,chg_date,chg_user,chg_term) 
values 
('VTSITEFNN','Mandatory field','Site Code From cannot be blank.Please enter Site Code From','E',sysdate,'BASE','BASE');

insert into messages
(msg_no,msg_str,msg_descr,msg_type,chg_date,chg_user,chg_term) 
values 
('VTSITEFND','Invalid Site Code From','Entered Site Code From is not defined in site master','E',sysdate,'BASE','BASE');

insert into messages
(msg_no,msg_str,msg_descr,msg_type,chg_date,chg_user,chg_term) 
values 
('VTSITETNN','Mandatory field','Site Code TO cannot be blank.Please enter Site Code TO','E',sysdate,'BASE','BASE');

insert into messages
(msg_no,msg_str,msg_descr,msg_type,chg_date,chg_user,chg_term) 
values 
('VTSITETND','Invalid Site Code TO','Entered Site Code TO is not defined in site master','E',sysdate,'BASE','BASE');

insert into messages 
(msg_no,msg_str,msg_descr,msg_type,chg_date,chg_user,chg_term) 
values 
('VTASSTSITE','Data mismatch','Entered old asset code and site code from must be same as per asset register master','E',sysdate,'BASE','BASE');

insert into messages
(msg_no,msg_str,msg_descr,msg_type,chg_date,chg_user,chg_term) 
values 
('VTLINKTN','Mandatory field','Please select Link type','E',sysdate,'BASE','BASE');

insert into messages
(msg_no,msg_str,msg_descr,msg_type,chg_date,chg_user,chg_term) 
values 
('VTTRNNC','Not confirmed','Selected Transaction not confirmed.Please check log','E',sysdate,'BASE','BASE');

insert into messages
(msg_no,msg_str,msg_descr,msg_type,chg_date,chg_user,chg_term) 
values 
('VTMCONFS','Trasaction confirmed','Selected Transaction confirmed successfully','P',sysdate,'BASE','BASE');

insert into messages
(msg_no,msg_str,msg_descr,msg_type,chg_date,chg_user,chg_term) 
values 
('VTACCTCD1','Account Not Found!','The account code you have entered is not found in the accounts master.','E',sysdate,'BASE','BASE');



