create table spl_sales_por_hdr (
tran_id Char(10), 
tran_date Date NOT NULL,
invoice_id  Char(10) NOT NULL,
site_code Char(5) NOT NULL,
cust_code Char(10) NOT NULL,
curr_code Char(5),
exch_rate Number(14,6),
amount	number(14,3),
tran_id__drn char(10),
confirmed char(1),
conf_date date ,
emp_code__aprv char(10),
wf_status  char(1),
chg_date date NOT NULL,
chg_user char(10) NOT NULL,
chg_term char(15) NOT NULL
) TABLESPACE ITM_DIS_D1;

ALTER TABLE spl_sales_por_hdr ADD CONSTRAINT spl_sales_por_hdr_X PRIMARY KEY (TRAN_ID) USING INDEX TABLESPACE ITM_DIS_I1;

ALTER TABLE spl_sales_por_hdr ADD CONSTRAINT FK_spl_sales_por_hdr_I FOREIGN KEY (INVOICE_ID) REFERENCES invoice (INVOICE_ID);

ALTER TABLE spl_sales_por_hdr ADD CONSTRAINT FK_spl_sales_por_hdr_S FOREIGN KEY (SITE_CODE) REFERENCES site (SITE_CODE);

ALTER TABLE spl_sales_por_hdr ADD CONSTRAINT FK_spl_sales_por_hdr_C FOREIGN KEY (CUST_CODE) REFERENCES customer (CUST_CODE);

ALTER TABLE spl_sales_por_hdr ADD CONSTRAINT FK_spl_sales_por_hdr_U FOREIGN KEY (CURR_CODE) REFERENCES currency (CURR_CODE);

ALTER TABLE spl_sales_por_hdr ADD CONSTRAINT FK_spl_sales_por_hdr_E FOREIGN KEY (EMP_CODE__APRV) REFERENCES employee (EMP_CODE);

create table spl_sales_por_det (
tran_id Char(10) not null,
line_no Number(3) NOT NULL,
line_no__trace Number(3) NOT NULL,
item_code Char(10) NOT NULL,
loc_code Char(8),
lot_no Char(15),
lot_sl Char(15),
bill_no Varchar2(40),
bil_date Date,
rate__inv  number(15,4),
rate__resale number(15,4),
quantity__inv number(14,3),
quantity__resale number(14,3),
amount number(14,3)
) TABLESPACE ITM_DIS_D1;


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

ALTER TABLE spl_sales_por_det ADD CONSTRAINT FK_spl_sales_por_det FOREIGN KEY (TRAN_ID) REFERENCES spl_sales_por_hdr (TRAN_ID) ON DELETE CASCADE;
ALTER TABLE spl_sales_por_det ADD CONSTRAINT FK_spl_sales_por_det_I FOREIGN KEY (ITEM_CODE) REFERENCES item (ITEM_CODE) ;
ALTER TABLE spl_sales_por_det ADD CONSTRAINT FK_spl_sales_por_det_L FOREIGN KEY (LOC_CODE) REFERENCES location (LOC_CODE) ;


-------------------------------------------System Entries---------------------------------------------


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(
'DIS',
4,
19,
10,
0,
0,
'w_proof_of_delivery',
'Proof Of Delivery',
'DIS.4.19.10.0.0',
sysdate,
'BASE',
'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 ('proof_of_delivery',1,'add.gif','Adding new','a','a','A','Add','1','S',sysdate,'BASE','BASE');

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 ('proof_of_delivery',7,'default.gif','Action Button for Default Data',3,'def_pod','a','R','Default','2','U',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 ('proof_of_delivery',2,'edit.gif','Editing Existing Record','a','a','E','Add','1','S',sysdate,'BASE','BASE');

insert into obj_forms (WIN_NAME,TITLE,
OBJ_BROW_NAME,OBJ_EDIT_NAME,FORM_NO)
values('w_proof_of_delivery','Header',
'd_spl_sales_por_hdr_brow','d_spl_sales_por_hdr_edit','1');

insert into obj_forms (WIN_NAME,TITLE,
OBJ_BROW_NAME,OBJ_EDIT_NAME,FORM_NO)
values('w_proof_of_delivery','Detail',
'd_spl_sales_por_det_brow','d_spl_sales_por_det_edit','2');


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_proof_of_delivery','2','2','A','seq10',sysdate,'BASE','BASE','0','confirmed','Y','Y','N','N','TRAN_ID',
'PODRL','0','0','spl_sales_por_hdr','DIS','','F','2','0','M');


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 ('proof_of_delivery','pre_validate','1','prv_default_ejb',NULL,
	sysdate,
	'BASE','BASE','1','0','EJB','ProofOfDelivery',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 ('proof_of_delivery','post_item_change','1','poic_default_ejb',NULL,
	sysdate,
	'BASE','BASE','1','0','EJB','ProofOfDelivery',NULL);
	
	
	
---------------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 ('proof_of_delivery',3,NULL,'Confirm','POD_conf','N','C','Confirm','1',
'2','U',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 (
	'proof_of_delivery','POD_conf','1','POD_confirm',NULL,
	sysdate,
	'Dadaso','BASE','1','0','EJB','ProofOfDeliveryConf',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 ('POD_confirm','Action To update POD confirmation','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 ('POD_confirm',1,'COMPONENT_TYPE','I','Component Type','S',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 ('POD_confirm',2,'COMPONENT_NAME','I','Component name','S',sysdate,'Dadaso','Base','ProofOfDeliveryConf');


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


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

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


----------------------Reject 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 ('proof_of_delivery',5,NULL,'Reject','POD_rej','N','C','Reject','1',
'2','U',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 (
	'proof_of_delivery','POD_rej','1','POD_reject',NULL,
	sysdate,
	'Dadaso','BASE','1','0','EJB','ProofOfDeliveryReject',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 ('POD_reject','Action To update POD status','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 ('POD_reject',1,'COMPONENT_TYPE','I','Component Type','S',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 ('POD_reject',2,'COMPONENT_NAME','I','Component name','S',sysdate,'Dadaso','Base','ProofOfDeliveryReject');


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


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

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

----------------Messages-------------------------------------

insert into messages
(msg_no,msg_str,msg_descr,msg_type,chg_date,chg_user,chg_term) 
values 
('VTINVIDNN','Invoice ID is empty','Invoice ID 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 
('VTINVIDNE','Invalid invoice ID ','Entered invoice code is not defined in Invoice master','E',sysdate,'BASE','BASE');



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

insert into messages
(msg_no,msg_str,msg_descr,msg_type,chg_date,chg_user,chg_term) 
values 
('VTALCONF','Already confirmed','Selected transaction already confirmed','E',sysdate,'BASE','BASE');


insert into messages
(msg_no,msg_str,msg_descr,msg_type,chg_date,chg_user,chg_term) 
values 
('VTINVNCON','Invalid invoice ID','Entered invoice Id not confirmed','E',sysdate,'BASE','BASE');

insert into messages
(msg_no,msg_str,msg_descr,msg_type,chg_date,chg_user,chg_term) 
values 
('VTNSUBCON','Transaction not confirm','Selected transaction not submitted.Please submit it before confirmation.','E',sysdate,'BASE','BASE');

insert into messages
(msg_no,msg_str,msg_descr,msg_type,chg_date,chg_user,chg_term) 
values 
('VTPODGRT','POD quantity error','POD quantity cannot be greater than invoice quantity','E',sysdate,'BASE','BASE');


insert into messages
(msg_no,msg_str,msg_descr,msg_type,chg_date,chg_user,chg_term) 
values 
('VTREJSUCF','Transaction rejected','Selected transaction rejected successfully','P',sysdate,'Dadaso','BASE');

insert into messages
(msg_no,msg_str,msg_descr,msg_type,chg_date,chg_user,chg_term) 
values 
('VTALREJ','Already rejected','Selected transaction already rejected','E',sysdate,'BASE','BASE');

insert into messages
(msg_no,msg_str,msg_descr,msg_type,chg_date,chg_user,chg_term) 
values 
('VTCONFCRJ','Confirmation error','Confirmed Transaction cannot be rejected ','E',sysdate,'BASE','BASE');

insert into messages
(msg_no,msg_str,msg_descr,msg_type,chg_date,chg_user,chg_term) 
values 
('VTRJCCONF','Rejection error','Rejected transaction cannot be confirmed','E',sysdate,'BASE','BASE');


insert into messages
(msg_no,msg_str,msg_descr,msg_type,chg_date,chg_user,chg_term) 
values 
('VTREGPRCN','Information missing','Regulated price information to calculate stockist margin percentage is not available','E',sysdate,'BASE','BASE');

insert into messages
(msg_no,msg_str,msg_descr,msg_type,chg_date,chg_user,chg_term) 
values 
('VTREJNOTA','Operation not allowed','Cannot reject transaction while creating Proof of Delivery','E',sysdate,'BASE','BASE');




-----------------Item Change------------------------------------

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

insert into obj_itemchange (obj_name,form_no,field_name,mandatory)
values('proof_of_delivery','2','quantity__resale','Y');


 
------------popnhelp------------------------

insert into pophelp(FIELD_NAME,MOD_NAME,SQL_STR,MSG_TITLE,WIDTH,HEIGHT,CHG_DATE,CHG_USER,CHG_TERM)
values(	'INVOICE_ID','W_PROOF_OF_DELIVERY','SELECT INVOICE_ID,TRAN_DATE,FIN_ENTITY,EFF_DATE,DESP_ID,SITE_CODE FROM INVOICE','Invoice ID',0,0,sysdate,'BASE','BASE');

---Default button system entries 

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 ('proof_of_delivery', 'def_pod', '1', 'act_pod', NULL, sysdate, 'Dadaso', 'BASE', '2', '0', 'EJB', 'ProofOfDeliveryDefault', NULL);


INSERT INTO system_event_services (
SERVICE_CODE, SERVICE_DESCR, SERVICE_URI, SERVICE_PROVIDER, METHOD_NAME, RETURN_VALUE, RETURN_TYPE, RETURN_DESCR, RETURN_XFRM, CHG_DATE, CHG_USER, CHG_TERM
) VALUES ('act_pod', 'Invoice items', 'http://localhost:9090/axis/services/ActionHandlerService', NULL, 'actionHandler', 'String', 'S', NULL, NULL, sysdate, 'Dadaso', 'System');


INSERT INTO system_service_args (
service_code, line_no, arg_name, arg_mode, descr, arg_type, arg_xfrm, chg_date, chg_user, chg_term, arg_value
) VALUES ('act_pod', 2, 'COMPONENT_TYPE', 'I', 'Component Type', 'C.String', NULL, sysdate, 'Dadaso', 'BASE', 'EJB');

INSERT INTO system_service_args (service_code, line_no, arg_name, arg_mode, descr, arg_type, arg_xfrm, chg_date, chg_user, chg_term, arg_value) VALUES ('act_pod', 3, 'COMPONENT_NAME', 'I', 'Component Name', 'C.String', NULL, sysdate, 'Dadaso', 'BASE', 'ProofOfDeliveryDefault');
INSERT INTO system_service_args (service_code, line_no, arg_name, arg_mode, descr, arg_type, arg_xfrm, chg_date, chg_user, chg_term, arg_value) VALUES ('act_pod', 4, 'ACTION_TYPE', 'I', 'Action Type', 'C.String', NULL, sysdate, 'Dadaso', 'BASE', 'Items');
INSERT INTO system_service_args (service_code, line_no, arg_name, arg_mode, descr, arg_type, arg_xfrm, chg_date, chg_user, chg_term, arg_value) VALUES ('act_pod', 5, 'XML_DATA_1', 'I', 'XML data of form no 1', 'S', NULL, sysdate, 'Dadaso', 'BASE', NULL);
INSERT INTO system_service_args (service_code, line_no, arg_name, arg_mode, descr, arg_type, arg_xfrm, chg_date, chg_user, chg_term, arg_value) VALUES ('act_pod', 6, 'OBJ_CONTEXT', 'I', 'Extra Arguments', 'S', NULL, sysdate, 'Dadaso', 'BASE', NULL);
INSERT INTO system_service_args (service_code, line_no, arg_name, arg_mode, descr, arg_type, arg_xfrm, chg_date, chg_user, chg_term, arg_value) VALUES ('act_pod', 7, 'XTRA_PARAMS', 'l', NULL, 'S', NULL, sysdate, 'Dadaso', 'BASE', NULL);



update transetup set edit_opt = '3' where tran_window ='w_proof_of_delivery';


INSERT INTO obj_actions (obj_name ,line_no,image,description,service_code,interactive,rights_char,title,form_no,service_handler,placement,action_type,
tran_type,chg_date,chg_term,chg_user )
 VALUES ('proof_of_delivery',5,'menu.bmp','View Audit Trail','audit_trail','1','X','View Audit','1','1','1','U',NULL,NULL,NULL,NULL);

 --31-july-2013
 
insert into messages
(msg_no,msg_str,msg_descr,msg_type,chg_date,chg_user,chg_term) 
values 
('VTINVIDAC','Operation not allowed','Proof Of Delivery already exist for selected invoice ID','E',sysdate,'BASE','BASE');


update transetup set edit_expr = 'or spl_sales_por_hdr.wf_status in(''R'')' where tran_window ='w_proof_of_delivery';

insert into gencodes (FLD_NAME,MOD_NAME,FLD_VALUE,DESCR,SH_DESCR,CHG_DATE,CHG_USER,CHG_TERM,ACTIVE,UDF_STR1,UDF_STR2,UDF_STR3) 
values ('REAS_CODE','W_MISC_DRCR_RCP_DR','PODDBTNOTE','POD DEBIT NOTE CHARGES ','PODCHRG',SYSDATE,'BASE','BASE','Y','3616','C421',null);


