CREATE TABLE EMPLOYEE_BOND (EMP_CODE CHAR(10) NOT NULL,LINE_NO NUMBER(3) NOT NULL,BOND_PRD NUMBER(2),BOND_AMT NUMBER(14,3)) TABLESPACE ITM_ADM_D1;

ALTER TABLE EMPLOYEE_BOND ADD CONSTRAINT EMPLOYEE_BOND_X PRIMARY KEY (EMP_CODE, LINE_NO) USING INDEX TABLESPACE ITM_ADM_I1;


CREATE TABLE EMPLOYEE_BOND_DEF(TRAN_ID CHAR(10) NOT NULL,TRAN_DATE DATE,SITE_CODE CHAR(5) NOT NULL,EMP_CODE	CHAR(10) NOT NULL,CONFIRMED CHAR(1),CONF_DATE DATE,EMP_CODE__APRV CHAR(10),
CHG_USER CHAR(10) NOT NULL,CHG_TERM VARCHAR2(15) NOT NULL,CHG_DATE DATE NOT NULL) TABLESPACE  ITM_ADM_D1;

ALTER TABLE EMPLOYEE_BOND_DEF ADD CONSTRAINT EMPLOYEE_BOND_DEF_X PRIMARY KEY(TRAN_ID) USING INDEX TABLESPACE ITM_ADM_I1;

CREATE TABLE EMPLOYEE_BOND_DEF_DET(TRAN_ID CHAR(10)	NOT NULL,LINE_NO NUMBER(3) NOT NULL,BOND_AMT__CURR NUMBER(14,3),BOND_PRD__CURR NUMBER(2),BOND_AMT NUMBER(14,3),
BOND_PRD NUMBER(2)) TABLESPACE  ITM_ADM_D1;	

ALTER TABLE EMPLOYEE_BOND_DEF_DET ADD CONSTRAINT EMPLOYEE_BOND_DEF_DET_X PRIMARY KEY(TRAN_ID,LINE_NO) USING INDEX TABLESPACE ITM_ADM_I1;	 

ALTER TABLE EMPLOYEE_BOND_DEF_DET ADD CONSTRAINT FK_EMPLOYEE_BOND_DEF_DET FOREIGN KEY (TRAN_ID) REFERENCES EMPLOYEE_BOND_DEF(TRAN_ID) ON DELETE CASCADE;	


CREATE TABLE BOND_RECOVERY(TRAN_ID CHAR(10) NOT NULL,TRAN_DATE DATE,SITE_CODE	CHAR(5)	NOT NULL,EMP_CODE CHAR(10) NOT NULL,RELIEVE_DATE DATE,DATE_JOIN DATE,BOND_AMT NUMBER(14,3),
BOND_PRD NUMBER(2),RECOVERY_AMT__DUE NUMBER(14,3),RECOVERY_AMT__ACT	NUMBER(14,3),RECOVERY_AMT__DONE	NUMBER(14,3),ACCT_CODE__BOND	CHAR(10),CCTR_CODE__BOND	CHAR(4),
ANAL_CODE CHAR(5),CONFIRMED	CHAR(1),CONF_DATE DATE,EMP_CODE__APRV CHAR(10),CHG_USER	CHAR(10) NOT NULL,CHG_TERM VARCHAR2(15)	NOT NULL,CHG_DATE DATE NOT NULL) TABLESPACE  ITM_ADM_D1;
 
ALTER TABLE BOND_RECOVERY ADD CONSTRAINT BOND_RECOVERY_X PRIMARY KEY(TRAN_ID) USING INDEX TABLESPACE ITM_ADM_I1;	 
 
ALTER TABLE EMPLOYEE ADD BOND_AMT__RECO NUMBER(14,3);

-------------Bond Recovery----------------------------------------------

insert into itm2menu 
(
APPLICATION,
LEVEL_1,
LEVEL_2,
LEVEL_3 ,
LEVEL_4,
LEVEL_5,
WIN_NAME,
DESCR,
MENU_PATH,
CHG_DATE,
CHG_TERM,
CHG_USER 
)
values
(
'ADM',
9,
0,
0,
0,
0,
'-',
'P.Bond Recovery',
'ADM.9.0.0.0.0',
sysdate,
'Dadaso',
'BASE'
);
----------------------------------------------
insert into itm2menu (
APPLICATION,
LEVEL_1,
LEVEL_2,
LEVEL_3 ,
LEVEL_4,
LEVEL_5,
WIN_NAME,
DESCR ,
MENU_PATH ,
CHG_DATE,
CHG_TERM,
CHG_USER  
)
values(
'ADM',
9,
1,
0,
0,
0,
'w_employee_bond_def',
'Bond Definition',
'ADM.9.1.0.0.0',
sysdate,
'Dadaso',
'BASE'
);
--------------------------------------
insert into obj_actions (
OBJ_NAME,
LINE_NO,
IMAGE,
DESCRIPTION,
SERVICE_CODE,
INTERACTIVE,
RIGHTS_CHAR,
TITLE,
FORM_NO,
ACTION_TYPE,
CHG_DATE,
CHG_TERM,
CHG_USER
)
values (
'employee_bond_def',
1,
'add.gif',
'Adding new',
'a',
'a',
'A',
'Add',
'1',
'S',
sysdate,
'BASE',
'Dadaso'
);
------------------------------------------------------
insert into obj_forms (WIN_NAME,TITLE,
OBJ_BROW_NAME,FORM_NO)
values('w_employee_bond_def','Bond Def Header',
'd_employee_bond_def','1');
--------------------------------------------------------

insert into transetup(TRAN_WINDOW,SAVE_FLAG,VAL_FLAG,KEY_FLAG,KEY_STRING,CHG_DATE,CHG_USER,CHG_TERM,EDI_OPTION,CONFIRM_COL,CONFIRM_VAL,REPEAT_ADD_DET,LEDG_POST_CONF,
CHG_DATE_ON_CONF,TRAN_ID_COL,REF_SER,CONFIRM_MODE,WORKFLOW_OPT,TABLE_NAME,APPLICATION,TABLE_DESC,VIEW_MODE,CHILDDATA_ARGOPT,EDIT_OPT,PERIOD_OPTION)
values('w_employee_bond_def','2','2','A','seq10',sysdate,'Dadaso','BASE','0','confirmed','Y','N','N','N','TRAN_ID',
'BNDDEF','0','0','employee_bond_def','ADM','Employee Bond Defination','F','2','0','M');


------------------------------------------------------
insert into obj_forms (
WIN_NAME,TITLE,OBJ_BROW_NAME,FORM_NO)
values('w_employee_bond_def','Bond Def Detail','d_employee_bond_def','2');



--------------------------------BOND Recorvery----------------------------------------------------------
--------------------------------------------------------------------------------------------------------
insert into itm2menu (APPLICATION,
LEVEL_1,LEVEL_2,LEVEL_3 ,LEVEL_4,LEVEL_5,WIN_NAME,DESCR ,MENU_PATH ,CHG_DATE,CHG_TERM,CHG_USER )
values('ADM',9,2,0,0,0,'w_bond_recovery','Bond Recovery','ADM.9.2.0.0.0',sysdate,'Dadaso','BASE');

---------------------------------------------------------
insert into obj_actions (OBJ_NAME,LINE_NO,IMAGE,DESCRIPTION,SERVICE_CODE,INTERACTIVE,RIGHTS_CHAR,TITLE,FORM_NO,ACTION_TYPE,CHG_DATE,CHG_TERM,CHG_USER)
values ('bond_recovery',1,'add.gif','Adding new','a','a','A','Add','1','S',sysdate,'BASE','Dadaso');

-------------------------------------------------------------------------------------------------------------

insert into obj_forms (WIN_NAME,TITLE,OBJ_BROW_NAME,FORM_NO)
values('w_bond_recovery','Bond Recovery Header','d_bond_recovery','1');

--------------------------------Bond recovery window---------------------------------------------
insert into transetup (TRAN_WINDOW,SAVE_FLAG,VAL_FLAG,KEY_FLAG,KEY_STRING,CHG_DATE,CHG_USER,CHG_TERM,EDI_OPTION,CONFIRM_COL,CONFIRM_VAL,REPEAT_ADD_DET,LEDG_POST_CONF,
CHG_DATE_ON_CONF,TRAN_ID_COL,REF_SER,CONFIRM_MODE,WORKFLOW_OPT,TABLE_NAME,APPLICATION,TABLE_DESC,VIEW_MODE,CHILDDATA_ARGOPT,AUDIT_TRAIL_OPT,EDIT_OPT,PERIOD_OPTION)
values ('w_bond_recovery','2','2','A','seq10',sysdate,'Dadaso','BASE','0','confirmed','Y','N','N','N','TRAN_ID','BNDREC','0','0','bond_recovery','ADM','Bond Recovery',
'F','2','0','0','M');


---------------------------edit button--------------------------------------------------------------------

insert into obj_actions (OBJ_NAME,LINE_NO,IMAGE,DESCRIPTION,SERVICE_CODE,INTERACTIVE,RIGHTS_CHAR,TITLE,FORM_NO,ACTION_TYPE,CHG_DATE,CHG_TERM,CHG_USER)
values ('bond_recovery',2,'edit.gif','Editing Existing Record','a','a','E','Edit','1','S',sysdate,'BASE','Dadaso');

------------------------------------------------------------------
insert into obj_actions (OBJ_NAME,LINE_NO,IMAGE,DESCRIPTION,SERVICE_CODE,INTERACTIVE,RIGHTS_CHAR,TITLE,FORM_NO,ACTION_TYPE,CHG_DATE,CHG_TERM,CHG_USER)
values ('employee_bond_def',2,'edit.gif','Editing Existing Record','a','a','E','Add','1','S',sysdate,'BASE','Dadaso');

----------------Validation employee_bond_def-----------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------

INSERT INTO SYSTEM_EVENTS   (
	obj_name ,event_code ,event_context ,service_code ,method_rule ,chg_date ,chg_user ,chg_term ,
	result_handle ,overwrite_core ,	comp_type ,comp_name ,comm_format )
 VALUES (
	'employee_bond_def','pre_validate','1','prv_default_ejb',NULL,
	sysdate,
	'Dadaso','BASE','1','0','EJB','EmpBondDefinition',NULL);
	
INSERT INTO SYSTEM_EVENTS (
	obj_name ,event_code ,event_context ,service_code ,method_rule ,chg_date ,chg_user ,chg_term ,
	result_handle ,overwrite_core ,	comp_type ,comp_name ,comm_format )
 VALUES (
	'employee_bond_def','post_item_change','1','poic_default_ejb',NULL,
	sysdate,
	'Dadaso','Dadaso','1','0','EJB','EmpBondDefinition',NULL);
	
insert into messages
(msg_no,msg_str,msg_descr,msg_type,chg_date,chg_user,chg_term) 
values 
('VNULEMPC','Employee code is empty','Please enter Employee code','E',sysdate,'Dadaso','BASE');

insert into messages
(msg_no,msg_str,msg_descr,msg_type,chg_date,chg_user,chg_term) 
values 
('VMEMPCD1','Employee Not found ...','This employee does not exist in master','E',sysdate,'Dadaso','BASE');

insert into messages
(msg_no,msg_str,msg_descr,msg_type,chg_date,chg_user,chg_term) 
values 
('VBAMTNOTF','Employee not found','Record not found in Employee bond table','E',sysdate,'Dadaso','BASE');
-----------------------------------------------

insert into messages
(msg_no,msg_str,msg_descr,msg_type,chg_date,chg_user,chg_term) 
values 
('VMSITENULL','Site code is empty','Please enter Site code','E',sysdate,'Dadaso','BASE');

insert into messages
(msg_no,msg_str,msg_descr,msg_type,chg_date,chg_user,chg_term) 
values 
('VMSITENVAL','Site code Not found ...','This Site code does not exist in master','E',sysdate,'Dadaso','BASE');

insert into messages
(msg_no,msg_str,msg_descr,msg_type,chg_date,chg_user,chg_term) 
values 
('VMBAMTNULL','Bond amount is empty','Please enter Bond amount','E',sysdate,'Dadaso','BASE');

insert into messages
(msg_no,msg_str,msg_descr,msg_type,chg_date,chg_user,chg_term) 
values 
('VMBPRDNULL','Bond period is empty','Please enter Bond period','E',sysdate,'Dadaso','BASE');



insert into obj_itemchange (obj_name,form_no,field_name,mandatory)
values('employee_bond_def','1','site_code',null);

insert into obj_itemchange (obj_name,form_no,field_name,mandatory)
values('employee_bond_def','1','emp_code',null);

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




	
---------------------------Validation query for Bond Recvery-----------------------------

INSERT INTO SYSTEM_EVENTS   (
	obj_name ,event_code ,event_context ,service_code ,method_rule ,chg_date ,chg_user ,chg_term ,
	result_handle ,overwrite_core ,	comp_type ,comp_name ,comm_format )
 VALUES (
	'bond_recovery','pre_validate','1','prv_default_ejb',NULL,
	sysdate,
	'Dadaso','BASE','1','0','EJB','BondRecovery',NULL);
	
INSERT INTO SYSTEM_EVENTS (
	obj_name ,event_code ,event_context ,service_code ,method_rule ,chg_date ,chg_user ,chg_term ,
	result_handle ,overwrite_core ,	comp_type ,comp_name ,comm_format )
 VALUES (
	'bond_recovery','post_item_change','1','poic_default_ejb',NULL,
	sysdate,
	'Dadaso','Dadaso','1','0','EJB','BondRecovery',NULL);
	
insert into obj_itemchange (obj_name,form_no,field_name,mandatory)
values('bond_recovery','1','site_code',null);

insert into obj_itemchange (obj_name,form_no,field_name,mandatory)
values('bond_recovery','1','emp_code',null);

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

insert into obj_itemchange (obj_name,form_no,field_name,mandatory)
values('bond_recovery','1','acct_code_bond',null);

insert into obj_itemchange (obj_name,form_no,field_name,mandatory)
values('bond_recovery','1','cctr_code_bond',null); 
---------------------------------------------------------------------------------------------------------------Think...
insert into obj_itemchange (obj_name,form_no,field_name,mandatory)
values('bond_recovery','1','DATE_JOIN',null);
insert into obj_itemchange (obj_name,form_no,field_name,mandatory)
values('bond_recovery','1','RELIEVE_DATE ',null);


-----------------------------------Default Button-------------------------
insert into obj_actions (OBJ_NAME,LINE_NO,IMAGE,DESCRIPTION,SERVICE_HANDLER,SERVICE_CODE,INTERACTIVE,RIGHTS_CHAR,TITLE,FORM_NO,ACTION_TYPE,CHG_DATE,CHG_TERM,CHG_USER)
values ('employee_bond_def',7,'default.gif','Action Button for Default Data',3,'empbond_def','a','R','Default','2','U',sysdate,'BASE','Dadaso');

-----------Creating new service code------------------------------------------------

INSERT INTO SYSTEM_EVENTS   (
	obj_name ,event_code ,event_context ,service_code ,method_rule ,chg_date ,chg_user ,chg_term ,
	result_handle ,overwrite_core ,	comp_type ,comp_name ,comm_format )
 VALUES (
	'employee_bond_def','empbond_def','1','empBond_default',NULL,
	sysdate,
	'Dadaso','BASE','1','0','EJB','EmpBondDefault',NULL);

-------------------------------------------------------------------------------------------
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 ('empBond_default','Action To Get Bond Detail','http://localhost:8080/axis/services/ActionHandlerService','Base Information','actionHandler','String','S',
sysdate,'Dadaso','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 ('empBond_default',1,'COMPONENT_TYPE','I','Component Type','C.String',sysdate,'Dadaso','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 ('empBond_default',2,'COMPONENT_NAME','I','Component name','C.String',sysdate,'Dadaso','Base','EmpBondDefault');


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 ('empBond_default',3,'ACTION_TYPE','I','Action Type','C.String',sysdate,'Dadaso','Base','Default');

insert into system_service_args(SERVICE_CODE,LINE_NO,ARG_NAME,ARG_MODE,DESCR,ARG_TYPE,CHG_DATE,CHG_USER,CHG_TERM)
values ('empBond_default',4,'XML_DATA_ALL','I','Action TyXML data of form no 1','S',sysdate,'Dadaso','Base');

insert into system_service_args(SERVICE_CODE,LINE_NO,ARG_NAME,ARG_MODE,DESCR,ARG_TYPE,CHG_DATE,CHG_USER,CHG_TERM)
values ('empBond_default',5,'OBJ_CONTEXT','I','Object context','S',sysdate,'Dadaso','Base');

insert into system_service_args(SERVICE_CODE,LINE_NO,ARG_NAME,ARG_MODE,DESCR,ARG_TYPE,CHG_DATE,CHG_USER,CHG_TERM)
values ('empBond_default',6,'XTRA_PARAMS','I','Extra Arguments','S',sysdate,'Dadaso','Base');



-----------------------------------Confirm button--------------------------------

insert into obj_actions (OBJ_NAME,LINE_NO,IMAGE,DESCRIPTION,SERVICE_CODE,INTERACTIVE,RIGHTS_CHAR,TITLE,FORM_NO,SERVICE_HANDLER,ACTION_TYPE,CHG_DATE,CHG_TERM,CHG_USER)
values ('employee_bond_def',5,'confirm.gif','Confirmation','empconfirm','N','C','Confirm','1','2','U',sysdate,'BASE','Dadaso');


-----Creating service code for confirm button----------

INSERT INTO SYSTEM_EVENTS   (
	obj_name ,event_code ,event_context ,service_code ,method_rule ,chg_date ,chg_user ,chg_term ,
	result_handle ,overwrite_core ,	comp_type ,comp_name ,comm_format )
 VALUES (
	'employee_bond_def','empconfirm','1','empbond_confirm',NULL,
	sysdate,
	'Dadaso','BASE','1','0','EJB','EmpBondDefConfirm',NULL);
	
	
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 ('empbond_confirm','Action To update Bond Detail','http://localhost:8080/axis/services/ActionHandlerService','Base Information','confirm','String',
'S',sysdate,'Dadaso','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 ('empbond_confirm',1,'COMPONENT_TYPE','I','Component Type','C.String',sysdate,'Dadaso','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 ('empbond_confirm',2,'COMPONENT_NAME','I','Component name','C.String',sysdate,'Dadaso','Base','EmpBondDefConfirm');



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

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

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


insert into messages
(msg_no,msg_str,msg_descr,msg_type,chg_date,chg_user,chg_term) 
values 
('VCONFSUC','Transaction Confirmed!','Selected Transaction confirmed.','P',sysdate,'Dadaso','BASE');



insert into messages
(msg_no,msg_str,msg_descr,msg_type,chg_date,chg_user,chg_term) 
values 
('VCONFUSUC','Not confirm','Selected Transaction not confirm.','E',sysdate,'Dadaso','BASE');

insert into messages
(msg_no,msg_str,msg_descr,msg_type,chg_date,chg_user,chg_term) 
values 
('AEBCONF','Already confirmed','This Transaction already confirmed','E',sysdate,'Dadaso','BASE');

insert into messages
(msg_no,msg_str,msg_descr,msg_type,chg_date,chg_user,chg_term) 
values 
('RECAMTNUL','Please enter correct amount','Actual amount can not be greater than due amount ','E',sysdate,'Dadaso','BASE');




insert into messages
(msg_no,msg_str,msg_descr,msg_type,chg_date,chg_user,chg_term) 
values 
('VBACTAMT','Bond actual amount is empty','Please enter Bond actual amount','E',sysdate,'Dadaso','BASE');

insert into messages
(msg_no,msg_str,msg_descr,msg_type,chg_date,chg_user,chg_term) 
values 
('VRECAMTGRT','Done Bond Recovery','Bond Recovery amount done for this employee','E',sysdate,'Dadaso','BASE');

insert into messages
(msg_no,msg_str,msg_descr,msg_type,chg_date,chg_user,chg_term) 
values 
('VBACTAMTGR','Bond actual amount is Greater','Bond actual amount can not be greater than recovery amount due','E',sysdate,'Dadaso','BASE');

insert into messages
(msg_no,msg_str,msg_descr,msg_type,chg_date,chg_user,chg_term) 
values 
('VACTCODNUL','Acct code is empty','Please enter Acct code','E',sysdate,'Dadaso','BASE'); 

insert into messages
(msg_no,msg_str,msg_descr,msg_type,chg_date,chg_user,chg_term) 
values 
('VACTNEXIST','Acct code not found','Acct code does not exist in accounts master','E',sysdate,'Dadaso','BASE');

insert into messages
(msg_no,msg_str,msg_descr,msg_type,chg_date,chg_user,chg_term) 
values 
('VANACODN','Cctr code is empty','Please enter Cctr code','E',sysdate,'Dadaso','BASE'); 

insert into messages
(msg_no,msg_str,msg_descr,msg_type,chg_date,chg_user,chg_term) 
values 
('VCCTNEXIST','Cctr code not found','Cctr code does not exist in costctr master','E',sysdate,'Dadaso','BASE');

insert into messages
(msg_no,msg_str,msg_descr,msg_type,chg_date,chg_user,chg_term) 
values 
('VANACODN','Analysis code is empty','Please enter Analysis code','E',sysdate,'Dadaso','BASE');

insert into messages
(msg_no,msg_str,msg_descr,msg_type,chg_date,chg_user,chg_term) 
values 
('VANALNEXT','Analysis code not found','Analysis code does not exist in Analysis master','E',sysdate,'Dadaso','BASE');


-------------------------Confirm Button Bond recovery--------------------

insert into obj_actions (OBJ_NAME,LINE_NO,IMAGE,DESCRIPTION,SERVICE_CODE,INTERACTIVE,RIGHTS_CHAR,TITLE,FORM_NO,SERVICE_HANDLER,ACTION_TYPE,CHG_DATE,CHG_TERM,CHG_USER)
values ('bond_recovery',5,'confirm.gif','Confirmation','serbondrecovery','N','C','Confirm','1','2','U',sysdate,'BASE','Dadaso');


--------------------Creating service code for confirm button----------

INSERT INTO SYSTEM_EVENTS   (
	obj_name ,event_code ,event_context ,service_code ,method_rule ,chg_date ,chg_user ,chg_term ,
	result_handle ,overwrite_core ,	comp_type ,comp_name ,comm_format )
 VALUES (
	'bond_recovery','serbondrecovery','1','bondrecovery_code',NULL,
	sysdate,
	'Dadaso','BASE','1','0','EJB','BondRecoveryConfirm',NULL);
	
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 ('bondrecovery_code','Action To update actual bond amt','http://localhost:8080/axis/services/ActionHandlerService','Base Information','confirm','String','S',
sysdate,'Dadaso','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 ('bondrecovery_code',1,'COMPONENT_TYPE','I','Component Type','C.String',sysdate,'Dadaso','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 ('bondrecovery_code',2,'COMPONENT_NAME','I','Component name','C.String',sysdate,'Dadaso','Base','BondRecoveryConfirm');

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

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

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


--------------------------------------------------------------------------



-----------------------pop help-------------------------------------------------------------------------------
insert into pophelp(FIELD_NAME,MOD_NAME,SQL_STR,MSG_TITLE,WIDTH,HEIGHT,CHG_DATE,CHG_USER,CHG_TERM)
values('EMP_CODE','W_EMPLOYEE_BOND_DEF','SELECT EMP_CODE,EMP_FNAME,EMP_MNAME,EMP_LNAME FROM EMPLOYEE','Employee Code',0,0,sysdate,'Dadaso','BASE');

insert into pophelp(FIELD_NAME,MOD_NAME,SQL_STR,MSG_TITLE,WIDTH,HEIGHT,CHG_DATE,CHG_USER,CHG_TERM)
values(	'SITE_CODE','W_EMPLOYEE_BOND_DEF','SELECT SITE_CODE,DESCR FROM SITE','Site Code',0,0,sysdate,'Dadaso','BASE');



insert into pophelp(FIELD_NAME,MOD_NAME,SQL_STR,MSG_TITLE,WIDTH,HEIGHT,CHG_DATE,CHG_USER,CHG_TERM)
values(	'SITE_CODE','W_BOND_RECOVERY','SELECT SITE_CODE,DESCR FROM SITE','Site Code',0,0,sysdate,'Dadaso','BASE');

insert into pophelp(FIELD_NAME,MOD_NAME,SQL_STR,MSG_TITLE,WIDTH,HEIGHT,CHG_DATE,CHG_USER,CHG_TERM)
values(	'ACCT_CODE__BOND','W_BOND_RECOVERY','SELECT ACCT_CODE,DESCR FROM ACCOUNTS','Acct Code',0,0,sysdate,'Dadaso','BASE');

insert into pophelp(FIELD_NAME,MOD_NAME,SQL_STR,MSG_TITLE,WIDTH,HEIGHT,CHG_DATE,CHG_USER,CHG_TERM)
values(	'CCTR_CODE__BOND','W_BOND_RECOVERY','SELECT CCTR_CODE, DESCR FROM COSTCTR','Cctr Code',0,0,sysdate,'Dadaso','BASE');

insert into pophelp(FIELD_NAME,MOD_NAME,SQL_STR,MSG_TITLE,WIDTH,HEIGHT,CHG_DATE,CHG_USER,CHG_TERM)
values('ANAL_CODE','W_BOND_RECOVERY','SELECT ANAL_CODE,SH_DESCR,DESCR FROM ANALYSIS','Cctr Code',0,0,sysdate,'Dadaso','BASE');


------------------------------------------Payparm--------------------------------------------------------------------------------------

insert into PAYRPARM (PRD_CODE,VAR_NAME,VAR_TYPE,VAR_VALUE,DESCR,VAR_SUBS,CHG_DATE,CHG_USER,CHG_TERM)    
values('999999','Bond_Cctr_Code','S','H450','Cctr_code for Bond Recovery',0,sysdate,'Dadaso','BASE') ;    

insert into PAYRPARM (PRD_CODE,VAR_NAME,VAR_TYPE,VAR_VALUE,DESCR,VAR_SUBS,CHG_DATE,CHG_USER,CHG_TERM)  
values
('999999','Bond_Anal_Code','S','E2777','Analysis for Bond Recovery',0,sysdate,'Dadaso','BASE');

-------------------- 22-Dec-2012----------------------

insert into PAYRPARM (PRD_CODE,VAR_NAME,VAR_TYPE,VAR_VALUE,DESCR,VAR_SUBS,CHG_DATE,CHG_USER,CHG_TERM)
values(	'999999','Bond_Acct_Code','S','2795','Acct_code for Bond Recovery',0,sysdate,'Dadaso','BASE') ; 


----------------------26 DEC 2012-------------------------

insert into pophelp(FIELD_NAME,MOD_NAME,SQL_STR,MSG_TITLE,WIDTH,HEIGHT,CHG_DATE,CHG_USER,CHG_TERM)
values('EMP_CODE','W_BOND_RECOVERY','SELECT EMP_CODE,EMP_FNAME,EMP_MNAME,EMP_LNAME FROM EMPLOYEE WHERE (DATE_JOIN  IS NOT NULL) AND (RELIEVE_DATE IS NOT NULL)','Employee Code',0,0,sysdate,'Dadaso','BASE');



 ---------------------------------------------------27-DEC-2012-----------------------------------------------------------------------------------
 -------------------------------------------------------------------------------------------------------------------------------------------------
 

insert into itm2menu (
APPLICATION,
LEVEL_1,
LEVEL_2,
LEVEL_3 ,
LEVEL_4,
LEVEL_5,
WIN_NAME,
DESCR ,
MENU_PATH ,
CHG_DATE,
CHG_TERM,
CHG_USER  
)
values(
'ADM',
9,
3,
0,
0,
0,
'w_employee_bond',
'Employee Bond',
'ADM.9.3.0.0.0',
sysdate,
'Dadaso',
'BASE'
);
------------------------------------------
insert into obj_actions (
OBJ_NAME,
LINE_NO,
IMAGE,
DESCRIPTION,
SERVICE_CODE,
INTERACTIVE,
RIGHTS_CHAR,
TITLE,
FORM_NO,
ACTION_TYPE,
CHG_DATE,
CHG_TERM,
CHG_USER
)
values (
'employee_bond',
1,
'add.gif',
'Adding new',
'a',
'a',
'A',
'Add',
'1',
'S',
sysdate,
'BASE',
'Dadaso'
);

-----------------------------------------------

insert into obj_forms (WIN_NAME,TITLE,OBJ_BROW_NAME,FORM_NO)
values('w_employee_bond','Bond Amt Detail','d_employee_bond','1');

insert into transetup(TRAN_WINDOW,SAVE_FLAG,VAL_FLAG,KEY_FLAG,KEY_STRING,CHG_DATE,CHG_USER,CHG_TERM,EDI_OPTION,CONFIRM_VAL,REPEAT_ADD_DET,LEDG_POST_CONF,
CHG_DATE_ON_CONF,TRAN_ID_COL,REF_SER,CONFIRM_MODE,WORKFLOW_OPT,TABLE_NAME,APPLICATION,TABLE_DESC,VIEW_MODE,CHILDDATA_ARGOPT,EDIT_OPT,PERIOD_OPTION)
values('w_employee_bond','2','2','M','seq10',sysdate,'Dadaso','BASE','0','Y','N','N','N','EMP_CODE',
'BNDDEF','0','0','employee_bond','ADM','Employee Bond Defination','F','2','0','M');
-------------------------------------------------------------------------------------------------------------------------------------------------

insert into obj_actions (OBJ_NAME,LINE_NO,IMAGE,DESCRIPTION,SERVICE_CODE,INTERACTIVE,RIGHTS_CHAR,TITLE,FORM_NO,ACTION_TYPE,CHG_DATE,CHG_TERM,CHG_USER)
values ('employee_bond',2,'edit.gif','Editing Existing Record','a','a','E','Edit','1','S',sysdate,'BASE','Dadaso');



INSERT INTO SYSTEM_EVENTS   (
	obj_name ,event_code ,event_context ,service_code ,method_rule ,chg_date ,chg_user ,chg_term ,
	result_handle ,overwrite_core ,	comp_type ,comp_name ,comm_format )
 VALUES ('employee_bond','pre_validate','1','prv_default_ejb',NULL,	sysdate,'Dadaso','BASE','1','0','EJB','EmpBondAmount',NULL);
	
	
INSERT INTO SYSTEM_EVENTS (	obj_name ,event_code ,event_context ,service_code ,method_rule ,chg_date ,chg_user ,chg_term ,
	result_handle ,overwrite_core ,	comp_type ,comp_name ,comm_format )
 VALUES ('employee_bond','post_item_change','1','poic_default_ejb',NULL,	sysdate,'Dadaso','Dadaso','1','0','EJB','EmpBondAmount',NULL);
 
 
 
 ---------------------------------------------------------------------------------
 
 insert into messages
(msg_no,msg_str,msg_descr,msg_type,chg_date,chg_user,chg_term)  
values 
('VNULBAMT','Bond amount is empty','Please enter Bond Amount','E',sysdate,'Dadaso','BASE');

insert into messages
(msg_no,msg_str,msg_descr,msg_type,chg_date,chg_user,chg_term) 
values 
('VNULBPRD','Bond period is empty','Please enter Bond period','E',sysdate,'Dadaso','BASE');

insert into messages
(msg_no,msg_str,msg_descr,msg_type,chg_date,chg_user,chg_term) 
values 
('VETLINE','Invalid emp_code and line_no','Employee code and line no already exist','E',sysdate,'Dadaso','BASE');

insert into messages
(msg_no,msg_str,msg_descr,msg_type,chg_date,chg_user,chg_term) 
values 
('VNULBLINE','LINE Number is empty','Please enter line number','E',sysdate,'Dadaso','BASE');

--------------------------pophelp-------------------------------------------------------


insert into obj_itemchange (obj_name,form_no,field_name,mandatory)
values('employee_bond','1','emp_code',null);

insert into pophelp(FIELD_NAME,MOD_NAME,SQL_STR,MSG_TITLE,WIDTH,HEIGHT,CHG_DATE,CHG_USER,CHG_TERM)
values('EMP_CODE','W_EMPLOYEE_BOND','SELECT EMP_CODE,EMP_FNAME,EMP_MNAME,EMP_LNAME FROM EMPLOYEE WHERE (DATE_JOIN  IS NOT NULL) AND (RELIEVE_DATE IS NOT NULL)','Employee Code',0,0,sysdate,'Dadaso','BASE');
 
 
-------------------Full and final sql-----------------------------


update system_events set COMP_TYPE='EJB' where  obj_name='fullfinal' and event_code='act_default';


ALTER TABLE FULLFINAL_DET ADD BOND_AMT NUMBER(14,3);

insert into payroll_projHDR(PRD_CODE,EMP_CODE,SITE_CODE__PAY,DEPT_CODE,PAY_DATE,BASIC_AMT,ALLOW_AMT ,DEDN_AMT,NET_AMT,CHG_DATE,CHG_USER,CHG_TERM )
values ('200203','E03654','SP801','CO01',sysdate,3000,0,0,0,sysdate,'BASE','BASE');

insert into payroll_projdet (PRD_CODE,EMP_CODE,AD_CODE,type,PAYABLE,BASE_AMT,SLAB_AMT,RESULT_AMT,POST_YN,CALC_AMT,RND_OFF,RND_TO,AMOUNT,SEQ_NO)
values ('200203','E03654','PLEN3','C','N',0,0,2000,'N',2000,'N',0,2000,'001');

insert into allwdedn (AD_CODE,SH_DESCR ,DESCR,TYPE ,RND_OFF,PROP_YN,PAYABLE,POST_YN,SLAB_OPTION,BAL_OPT,IT_PROJ,DROP_YN,COST_COMPANY,SPLIT_YN)
values('PLEN3','PLEN3','PLEN3','C','N','N','D','N','D','N','N','N','N','N');

insert into allwdedn (AD_CODE,SH_DESCR ,DESCR,TYPE ,RND_OFF,PROP_YN,PAYABLE,POST_YN,SLAB_OPTION,BAL_OPT,IT_PROJ,DROP_YN,COST_COMPANY,SPLIT_YN)
values('NOTR','Notice Rec','NOTICE RECOVERY','C','N','Y','D','Y','N','N','N','N','Y','N');


insert into grade_site_lvetbl (SITE_CODE,GRADE_CODE,LVE_TBLNO,CHG_DATE,CHG_USER,CHG_TERM)
values('SP801','114','LV002',sysdate,'BASE','BASE');


update obj_forms set cross_update_expr='[amount][net_amt][][((("Y".equals(confirmed) || "I".equals(confirmed)) ? 1 : 0)*(total_amt - it_amt)) + ((("Y".equals(confirmed) || "I".equals(confirmed)) ? 1 : 0)*(total_amt - it_amt)) *(total_amt + it_amt);]' where form_no='2' and win_name='w_fullfinal';

update obj_forms set expr_fld_info='[2..D.amount][2..D.net_amt][1.1.D.total_amt,1.1.D.it_amt,1.1.S.confirmed]' where form_no='2' and win_name='w_fullfinal';


UPDATE transetup set ref_ser='E-FNFS' where tran_window='w_fullfinal';










 








	
	
