CREATE TABLE `smr_smm_gmail_service` (
   `s_no` int(8) NOT NULL AUTO_INCREMENT,
   `client_id` longtext,
   `client_secret` longtext,
   `refresh_token` longtext,
   `gmail_address` varchar(1024) DEFAULT NULL,
   `default_template` longtext,
   `gmail_status` varchar(1) DEFAULT 'Y',
   `created_date` datetime DEFAULT NULL,
   `created_by` varchar(64) DEFAULT NULL,
   `updated_date` datetime DEFAULT NULL,
   `updated_by` varchar(64) DEFAULT NULL,
   PRIMARY KEY (`s_no`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;


CREATE TABLE `smr_trn_gmailattachment` (
   `gmail_gid` varchar(32) NOT NULL,
   `from_mailaddress` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
   `to_mailaddress` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
   `mail_subject` longtext,
   `mail_body` longtext,
   `created_by` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
   `created_date` datetime DEFAULT NULL,
   `sent_flag` varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT 'N',
   `direction` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT 'outgoing',
   `template_gid` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
   `read_flag` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT 'N',
   `file_gid` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
   `document_name` text CHARACTER SET utf8 default NULL,
   `document_path` varchar(512) DEFAULT NULL,
   PRIMARY KEY (`gmail_gid`),
   KEY `gmail_gid` (`gmail_gid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


alter table adm_mst_tcompany add column accounts_email varchar(256) default null;


alter table rbl_tmp_tinvoicedtl add column hsn_code varchar(64) default null;
alter table rbl_tmp_tinvoicedtl add column hsn_description varchar(64) default null;
alter table rbl_trn_tinvoicedtl add column hsn_code varchar(64) default null;
alter table rbl_trn_tinvoicedtl add column hsn_description varchar(64) default null;
alter table rbl_tmp_tinvoicedtl add column productgroup_name varchar(64) default null;
alter table rbl_trn_tinvoicedtl add column productgroup_name varchar(64) default null;
alter table rbl_trn_tinvoicedtl add column employee_gid varchar(64) default null;
alter table rbl_trn_tinvoicedtl add column selling_price varchar(64) default null;
alter table rbl_trn_tinvoicedtl add column sl_no varchar(64) default null;
alter table rbl_trn_tinvoice add column bill_email varchar(64) default null;
alter table adm_mst_tcompany add column accounts_email varchar(64) default null;
alter table adm_mst_tcompany add column accounts_password varchar(64) default null;
alter table smr_trn_tsalesorder add column po_number varchar(64) default null;
alter table rbl_trn_Tinvoice add column po_number varchar(64) default null;
alter table pmr_mst_tproduct add column hsn_code varchar(64) default null;
alter table pmr_mst_tproduct add column hsn_description varchar(64) default null;

UPDATE `vcidex`.`adm_mst_tmoduleangular` SET `sref` = '/smr/SmrTrnInvoiceSummary' WHERE (`module_gid` = 'SMRINMINV');



 INSERT INTO `adm_mst_tmodule` (`module_gid`,`module_gid_parent`,`module_code`,`display_order`,`module_link`,`menu_level`,`max_menulevel`,`module_name`,`status`,`image_url`,`group_type`,`modulemanager_gid`,`breadcrumb_name`,`approval_flag`,`approval_tablename`,`approval_type`,`approval_limit`,`module_flag`,`created_by`,`created_date`,`updated_by`,`updated_date`,`lw_flag`,`sref`,`icon`,`icon_name`)  

VALUES 

('SMRRWLRNL', 'SMRRWL', 'SMRRWLRNL', '1705', 'ems_crm/crm_trn_Invoicerenewals.aspx', '3', NULL, 'Renewal Management', '1', '', 'RENEWAL', '', 'Renewal Management', '', '', '', 'N', 'N', NULL, NULL, NULL, NULL, 'N', NULL, NULL, NULL); 

  

 
 
 
 
 
 

CREATE TABLE `crm_trn_trenewalverifyorder` ( 

  `renewalverifyorder_gid` varchar(64) NOT NULL, 

  `invoice_date` varchar(128) DEFAULT NULL, 

  `invoice_no` varchar(128) DEFAULT NULL, 

  `customer_gid` varchar(128) DEFAULT NULL, 

  `customer_name` text, 

  `customer_contact_person` varchar(512) DEFAULT NULL, 

  `customer_email` varchar(128) DEFAULT NULL, 

 `customer_mobile` varchar(128) DEFAULT NULL, 

  `produt_code` varchar(128) DEFAULT NULL, 

  `produt_name` varchar(128) DEFAULT NULL, 

  `product_quantity` varchar(64) DEFAULT NULL, 

  `customer_address` text, 

  `created_by` varchar(16) DEFAULT NULL, 

  `created_date` datetime DEFAULT NULL, 

  `updated_by` varchar(64) DEFAULT NULL, 

  PRIMARY KEY (`renewalverifyorder_gid`) 

) ; 

 

CREATE TABLE `crm_trn_trenewalverifyorderdtl`( 

  `renewalverifyorderdtl_gid` varchar(64) NOT NULL, 

  `renewalverifyorder_gid` varchar(64) DEFAULT NULL, 

  `installation_date` datetime DEFAULT NULL, 

  `IEMI_No` varchar(64) DEFAULT NULL, 

  `SIM_No` varchar(64) DEFAULT NULL, 

  `VEHICLE_No` varchar(64) DEFAULT NULL, 

  `User_name` varchar(64) DEFAULT NULL, 

  `created_by` varchar(64) DEFAULT NULL, 

  `created_date` datetime DEFAULT NULL, 

`renewal_status` varchar(32) DEFAULT NULL, 

  PRIMARY KEY (`renewalverifyorderdtl_gid`) 

) ; 
 

INSERT INTO `adm_mst_tsequence` (`sequence_gid`,`sequence_code`,`sequence_name`,`sequence_format`,`sequence_curval`,`sequence_flag`,`branch_flag`,`department_flag`,`year_flag`,`month_flag`,`location_flag`,`company_code`,`delimeter`,`runningno_prefix`,`finyear`,`carry_forward`,`created_by`,`created_date`,`updated_by`,`updated_date`)  

VALUES ('SSQM2403317593','INVA','RENEWAL DETAIL','4','1','N','N','N','N','N','N','','','','2024','N',NULL,NULL,NULL,NULL); 

 

 

 
 

 INSERT INTO `adm_mst_tsequence` (`sequence_gid`,`sequence_code`,`sequence_name`,`sequence_format`,`sequence_curval`,`sequence_flag`,`branch_flag`,`department_flag`,`year_flag`,`month_flag`,`location_flag`,`company_code`,`delimeter`,`runningno_prefix`,`finyear`,`carry_forward`,`created_by`,`created_date`,`updated_by`,`updated_date`)  

VALUES ('SSQM24033175947','INVRO','RENEWAL DETAIL','4','1','N','N','N','N','N','N','','','','2024','N',NULL,NULL,NULL,NULL); 

 
 
 

INSERT INTO `adm_mst_terror` (`error_gid`,`error_code`,`error_message`,`error_type`,`created_by`,`created_date`,`updated_by`,`updated_date`)  

VALUES ('SERM202406020084','CRM_SUC_026','Installation Verify successfully !','SUCCESS',NULL,NULL,NULL,NULL); 

 

INSERT INTO `adm_mst_tsequence` (`sequence_gid`,`sequence_code`,`sequence_name`,`sequence_format`,`sequence_curval`,`sequence_flag`,`branch_flag`,`department_flag`,`year_flag`,`month_flag`,`location_flag`,`company_code`,`delimeter`,`runningno_prefix`,`finyear`,`carry_forward`,`created_by`,`created_date`,`updated_by`,`updated_date`,`product_flag`,`section_flag`,`block_flag`,`unit_flag`) VALUES ('SSQM24033175945','INVR','RENEWAL DETAIL','4','12','N','N','N','N','N','N','','','','2024','N',NULL,NULL,NULL,NULL,'N','N','N','N'); 
 
 

INSERT INTO `adm_mst_terror` (`error_gid`,`error_code`,`error_message`,`error_type`,`created_by`,`created_date`,`updated_by`,`updated_date`)  

VALUES ('SERM202406020082','CRM_SUC_027','SIM Blocked successfully ','SUCCESS',NULL,NULL,NULL,NULL); 

INSERT INTO `adm_mst_terror` (`error_gid`,`error_code`,`error_message`,`error_type`,`created_by`,`created_date`,`updated_by`,`updated_date`)  

VALUES ('SERM202406020085','CRM_SUC_028','Renewal Updated Successfully','SUCCESS',NULL,NULL,NULL,NULL); 

 

 
 

 alter table crm_trn_trenewalverifyorderdtl add column `renewal_date` datetime DEFAULT NULL; 

 alter table crm_trn_trenewalverifyorderdtl add column `renewalremarks` text; 

alter table  crm_trn_trenewalverifyorderdtl add column `renewal_status` varchar(32) DEFAULT NULL; 

  

 
 
 

INSERT INTO `adm_mst_terror` (`error_gid`,`error_code`,`error_message`,`error_type`,`created_by`,`created_date`,`updated_by`,`updated_date`)  

VALUES ('SERM202406020086','CRM_SUC_030','Renewal Updated successfully !','SUCCESS',NULL,NULL,NULL,NULL); 
 
 

 

INSERT INTO `adm_mst_tmodule` (`module_gid`,`module_gid_parent`,`module_code`,`display_order`,`module_link`,`menu_level`,`max_menulevel`,`module_name`,`status`,`image_url`,`group_type`,`modulemanager_gid`,`breadcrumb_name`,`approval_flag`,`approval_tablename`,`approval_type`,`approval_limit`,`module_flag`,`created_by`,`created_date`,`updated_by`,`updated_date`,`lw_flag`,`sref`,`icon`,`icon_name`) VALUES ('SMRREWRMPT','SMRREW','SMRREWRMPT',1031,'ems_crm/crm_rpt_renewalmanagemnetreport.aspx',3,NULL,'Renewal Management Report','1','','REPORT','','Renewal Managment Report','','','','N','N',NULL,NULL,NULL,NULL,'N',NULL,NULL,NULL); 

  

 
 

INSERT INTO `adm_mst_tmodule` (`module_gid`,`module_gid_parent`,`module_code`,`display_order`,`module_link`,`menu_level`,`max_menulevel`,`module_name`,`status`,`image_url`,`group_type`,`modulemanager_gid`,`breadcrumb_name`,`approval_flag`,`approval_tablename`,`approval_type`,`approval_limit`,`module_flag`,`created_by`,`created_date`,`updated_by`,`updated_date`,`lw_flag`,`sref`,`icon`,`icon_name`)  

VALUES 

('SMRRWLRWLS', 'SMRRWL', 'SMRRWLRWLS', '1706', 'ems_crm/crm_trn_renewals.aspx', '3', NULL, 'Renewals', '1', '', 'RENEWAL', '', 'Renewals', '', '', '', 'N', 'N', NULL, NULL, NULL, NULL, 'N', NULL, NULL, NULL); 




INSERT INTO `adm_mst_tmoduleangular` (`module_gid`,`module_gid_parent`,`module_code`,`display_order`,`module_link`,`menu_level`,`max_menulevel`,`module_name`,`status`,`image_url`,`group_type`,`modulemanager_gid`,`breadcrumb_name`,`approval_flag`,`approval_tablename`,`approval_type`,`approval_limit`,`module_flag`,`created_by`,`created_date`,`updated_by`,`updated_date`,`lw_flag`,`sref`,`icon`,`shopify_flag`,`icon_name`) VALUES ('SMRRWL','SMR','SMRRWL',1015,'ems_crm/crm_transaction.aspx#',2,NULL,'Renewal','1','../images/ico_qc_dg.gif','','','Renewal','','','','N','Y',NULL,NULL,NULL,NULL,'Y','/smr/SmrDashboard',NULL,NULL,NULL); 

 

INSERT INTO `adm_mst_tmoduleangular` (`module_gid`,`module_gid_parent`,`module_code`,`display_order`,`module_link`,`menu_level`,`max_menulevel`,`module_name`,`status`,`image_url`,`group_type`,`modulemanager_gid`,`breadcrumb_name`,`approval_flag`,`approval_tablename`,`approval_type`,`approval_limit`,`module_flag`,`created_by`,`created_date`,`updated_by`,`updated_date`,`lw_flag`,`sref`,`icon`,`shopify_flag`,`icon_name`) VALUES ('SMRRWLRLM','SMRRWL','SMRRWLRLM',1703,'ems_crm/crm_trn_renewalmanagersummary.aspx',3,NULL,'Renewal Manager','1','../images/module_icon_RS.gif','RENEWAL','','Renewal Manager','','','','N','N',NULL,NULL,NULL,NULL,'Y','/smr/SmrTrnRenewalmanagersummary',NULL,NULL,NULL); 

 

INSERT INTO `adm_mst_tmoduleangular` (`module_gid`,`module_gid_parent`,`module_code`,`display_order`,`module_link`,`menu_level`,`max_menulevel`,`module_name`,`status`,`image_url`,`group_type`,`modulemanager_gid`,`breadcrumb_name`,`approval_flag`,`approval_tablename`,`approval_type`,`approval_limit`,`module_flag`,`created_by`,`created_date`,`updated_by`,`updated_date`,`lw_flag`,`sref`,`icon`,`shopify_flag`,`icon_name`) VALUES ('SMRRWLRMT','SMRRWL','SMRRWLRMT',1701,'ems_crm/crm_trn_renewaldashboardtab.aspx',3,NULL,'Renewal ','1','../images/ico_manager_Approval_RS.gif','RENEWAL','','Renewal ','','','','N','N',NULL,NULL,NULL,NULL,'Y','/smr/SmrTrnRenevalsummary',NULL,NULL,NULL); 

 

INSERT INTO `adm_mst_tmoduleangular` (`module_gid`,`module_gid_parent`,`module_code`,`display_order`,`module_link`,`menu_level`,`max_menulevel`,`module_name`,`status`,`image_url`,`group_type`,`modulemanager_gid`,`breadcrumb_name`,`approval_flag`,`approval_tablename`,`approval_type`,`approval_limit`,`module_flag`,`created_by`,`created_date`,`updated_by`,`updated_date`,`lw_flag`,`sref`,`icon`,`shopify_flag`,`icon_name`) VALUES ('SMRRWLRTM','SMRRWL','SMRRWLRTM',1702,'ems_crm/crm_mst_renewalteamsummary.aspx',3,NULL,'Renewal Team','1','../images/ico_department_RS.gif','RENEWAL MANAGEMENT','','Renewal Team','','','','N','N',NULL,NULL,NULL,NULL,'Y','/smr/SmrTrnRenewalteamsummary',NULL,NULL,NULL); 

 

 

 alter table crm_trn_trenewalteam add column campaign_prefix varchar(1000) DEFAULT NULL; 

alter table crm_trn_trenewalteam add column campaign_mailid varchar(64) DEFAULT NULL; 

 

INSERT INTO `adm_mst_tmoduleangular` (`module_gid`,`module_gid_parent`,`module_code`,`display_order`,`module_link`,`menu_level`,`max_menulevel`,`module_name`,`status`,`image_url`,`group_type`,`modulemanager_gid`,`breadcrumb_name`,`approval_flag`,`approval_tablename`,`approval_type`,`approval_limit`,`module_flag`,`created_by`,`created_date`,`updated_by`,`updated_date`,`lw_flag`,`sref`,`icon`,`shopify_flag`,`icon_name`) VALUES ('SMRRWLINV','SMRRWL','SMRRWLINV',1704,'ems_crm/crm_mst_renewalinvoice.aspx',3,NULL,'My Renewal','1','../images/ico_department_RS.gif','RENEWAL MANAGEMENT','','My Renewal','','','','N','N',NULL,NULL,NULL,NULL,'Y','/smr/SmrTrnRenewalInvoice',NULL,NULL,NULL);  
 
 
 
alter table smr_trn_tsalesorder add column leadbank_gid varchar(64) default null; 
 
alter table smr_trn_tsalesorderdtl add column product_status varchar(64) default null; 
 
alter table smr_trn_tsalesorderdtl add column productrequireddate_remarks longtext; 

 

alter table smr_trn_tsalesorder add column `raiserenewal_flag` char(2) DEFAULT 'N'; 

 

INSERT INTO `adm_mst_tmoduleangular` (`module_gid`,`module_gid_parent`,`module_code`,`display_order`,`module_link`,`menu_level`,`max_menulevel`,`module_name`,`status`,`image_url`,`group_type`,`modulemanager_gid`,`breadcrumb_name`,`approval_flag`,`approval_tablename`,`approval_type`,`approval_limit`,`module_flag`,`created_by`,`created_date`,`updated_by`,`updated_date`,`lw_flag`,`sref`,`icon`,`shopify_flag`,`icon_name`) VALUES ('SMRRCPRR','SMRRCP','SMRRCPRR',1115,'ems_crm/crm_mst_renewalinvoice.aspx',3,NULL,'Renewal Report','1','../images/ico_department_RS.gif','REPORT','','Renewal Report','','','','N','N',NULL,NULL,NULL,NULL,'Y','/smr/SmrRptRenewalreport',NULL,NULL,NULL);  

 

 

 

Alter table crm_trn_trenewal2campaign modify column updated_by varchar(64) DEFAULT NULL; 

Alter table crm_trn_trenewal2campaign modify column updated_date date DEFAULT NULL; 

Alter table crm_trn_trenewal2campaign add column  renewal_gid varchar(64) default null; 

 alter table crm_mst_tcustomer add column  `category_gid` varchar(64) DEFAULT NULL; 

alter table crm_mst_tcustomer add column  `category_name` varchar(64) DEFAULT NULL; 

alter table crm_mst_tcustomer add column  `subcategory_name` varchar(64) DEFAULT NULL; 

 20/02024-Maxson

alter table crm_trn_trenewal add column renewal_flag varchar(264) default null;
alter table crm_trn_trenewal add column frequency_term varchar(64) default null;
alter table smr_trn_tsalesorder add column renewal_flag varchar(264) default null;

 alter table crm_trn_trenewal add column updated_date datetime DEFAULT NULL;

alter table crm_trn_trenewal add column agreement_gid varchar(64) default null;
 
 
alter table crm_trn_tagreement add column bill_to varchar(264) default null;
alter table crm_trn_tagreement add column tax_gid varchar(45) DEFAULT NULL;
alter table crm_trn_tagreement add column tax_name varchar(45) DEFAULT NULL;
alter table crm_trn_tagreement add column gst_amount double DEFAULT '0';
alter table crm_trn_tagreement add column total_price double(13,2) DEFAULT NULL;
alter table crm_trn_tagreement add column tax_amount double DEFAULT '0';
 
 
alter table crm_trn_tagreementdtl add column taxsegment_gid varchar(64) DEFAULT NULL;
alter table crm_trn_tagreementdtl add column taxsegmenttax_gid varchar(64) DEFAULT NULL;
alter table crm_trn_tagreementdtl add column `type` varchar(45) DEFAULT NULL;
 


INSERT INTO `adm_mst_tmoduleangular` (`module_gid`,`module_gid_parent`,`module_code`,`display_order`,`module_link`,`menu_level`,`max_menulevel`,`module_name`,`status`,`image_url`,`group_type`,`modulemanager_gid`,`breadcrumb_name`,`approval_flag`,`approval_tablename`,`approval_type`,`approval_limit`,`module_flag`,`created_by`,`created_date`,`updated_by`,`updated_date`,`lw_flag`,`sref`,`icon`,`shopify_flag`,`icon_name`) VALUES ('SMRRWL','SMR','SMRRWL',1015,'ems_crm/crm_transaction.aspx#',2,NULL,'Renewal','1','../images/ico_qc_dg.gif','','','Renewal','','','','N','Y',NULL,NULL,NULL,NULL,'Y','/smr/SmrDashboard',NULL,NULL,NULL);
 
INSERT INTO `adm_mst_tmoduleangular` (`module_gid`,`module_gid_parent`,`module_code`,`display_order`,`module_link`,`menu_level`,`max_menulevel`,`module_name`,`status`,`image_url`,`group_type`,`modulemanager_gid`,`breadcrumb_name`,`approval_flag`,`approval_tablename`,`approval_type`,`approval_limit`,`module_flag`,`created_by`,`created_date`,`updated_by`,`updated_date`,`lw_flag`,`sref`,`icon`,`shopify_flag`,`icon_name`) VALUES ('SMRRWLRLM','SMRRWL','SMRRWLRLM',1703,'ems_crm/crm_trn_renewalmanagersummary.aspx',3,NULL,'Renewal Manager','1','../images/module_icon_RS.gif','RENEWAL','','Renewal Manager','','','','N','N',NULL,NULL,NULL,NULL,'Y','/smr/SmrTrnRenewalmanagersummary',NULL,NULL,NULL);
 
INSERT INTO `adm_mst_tmoduleangular` (`module_gid`,`module_gid_parent`,`module_code`,`display_order`,`module_link`,`menu_level`,`max_menulevel`,`module_name`,`status`,`image_url`,`group_type`,`modulemanager_gid`,`breadcrumb_name`,`approval_flag`,`approval_tablename`,`approval_type`,`approval_limit`,`module_flag`,`created_by`,`created_date`,`updated_by`,`updated_date`,`lw_flag`,`sref`,`icon`,`shopify_flag`,`icon_name`) VALUES ('SMRRWLRMT','SMRRWL','SMRRWLRMT',1701,'ems_crm/crm_trn_renewaldashboardtab.aspx',3,NULL,'Renewal ','1','../images/ico_manager_Approval_RS.gif','RENEWAL','','Renewal ','','','','N','N',NULL,NULL,NULL,NULL,'Y','/smr/SmrTrnRenevalsummary',NULL,NULL,NULL);
 
INSERT INTO `adm_mst_tmoduleangular` (`module_gid`,`module_gid_parent`,`module_code`,`display_order`,`module_link`,`menu_level`,`max_menulevel`,`module_name`,`status`,`image_url`,`group_type`,`modulemanager_gid`,`breadcrumb_name`,`approval_flag`,`approval_tablename`,`approval_type`,`approval_limit`,`module_flag`,`created_by`,`created_date`,`updated_by`,`updated_date`,`lw_flag`,`sref`,`icon`,`shopify_flag`,`icon_name`) VALUES ('SMRRWLRTM','SMRRWL','SMRRWLRTM',1702,'ems_crm/crm_mst_renewalteamsummary.aspx',3,NULL,'Renewal Team','1','../images/ico_department_RS.gif','RENEWAL MANAGEMENT','','Renewal Team','','','','N','N',NULL,NULL,NULL,NULL,'Y','/smr/SmrTrnRenewalteamsummary',NULL,NULL,NULL);
 
 
alter table crm_trn_trenewalteam add column campaign_prefix varchar(1000) DEFAULT NULL;
alter table crm_trn_trenewalteam add column campaign_mailid varchar(64) DEFAULT NULL;
 
INSERT INTO `adm_mst_tmoduleangular` (`module_gid`,`module_gid_parent`,`module_code`,`display_order`,`module_link`,`menu_level`,`max_menulevel`,`module_name`,`status`,`image_url`,`group_type`,`modulemanager_gid`,`breadcrumb_name`,`approval_flag`,`approval_tablename`,`approval_type`,`approval_limit`,`module_flag`,`created_by`,`created_date`,`updated_by`,`updated_date`,`lw_flag`,`sref`,`icon`,`shopify_flag`,`icon_name`) VALUES ('SMRRWLINV','SMRRWL','SMRRWLINV',1704,'ems_crm/crm_mst_renewalinvoice.aspx',3,NULL,'My Renewal','1','../images/ico_department_RS.gif','RENEWAL MANAGEMENT','','My Renewal','','','','N','N',NULL,NULL,NULL,NULL,'Y','/smr/SmrTrnRenewalInvoice',NULL,NULL,NULL); 

alter table smr_trn_tsalesorder add column leadbank_gid varchar(64) default null;
alter table smr_trn_tsalesorderdtl add column product_status varchar(64) default null;
alter table smr_trn_tsalesorderdtl add column productrequireddate_remarks longtext;
 
alter table smr_trn_tsalesorder add column `raiserenewal_flag` char(2) DEFAULT 'N';
 
INSERT INTO `adm_mst_tmoduleangular` (`module_gid`,`module_gid_parent`,`module_code`,`display_order`,`module_link`,`menu_level`,`max_menulevel`,`module_name`,`status`,`image_url`,`group_type`,`modulemanager_gid`,`breadcrumb_name`,`approval_flag`,`approval_tablename`,`approval_type`,`approval_limit`,`module_flag`,`created_by`,`created_date`,`updated_by`,`updated_date`,`lw_flag`,`sref`,`icon`,`shopify_flag`,`icon_name`) VALUES ('SMRRCPRR','SMRRCP','SMRRCPRR',1115,'ems_crm/crm_mst_renewalinvoice.aspx',3,NULL,'Renewal Report','1','../images/ico_department_RS.gif','REPORT','','Renewal Report','','','','N','N',NULL,NULL,NULL,NULL,'Y','/smr/SmrRptRenewalreport',NULL,NULL,NULL);
 
 
 
Alter table crm_trn_trenewal2campaign modify column updated_by varchar(64) DEFAULT NULL;
Alter table crm_trn_trenewal2campaign modify column updated_date date DEFAULT NULL;
Alter table crm_trn_trenewal2campaign add column renewal_gid varchar(64) default null;

 
29/08/2024-Maxson

alter table crm_trn_trenewal add column renewal_flag varchar(264) default null;
alter table crm_trn_trenewal add column frequency_term varchar(64) default null;
alter table smr_trn_tsalesorder add column renewal_flag varchar(264) default null;

 alter table crm_trn_trenewal add column updated_date datetime DEFAULT NULL;

alter table crm_trn_trenewal add column agreement_gid varchar(64) default null;
 
 
alter table crm_trn_tagreement add column bill_to varchar(264) default null;
alter table crm_trn_tagreement add column tax_gid varchar(45) DEFAULT NULL;
alter table crm_trn_tagreement add column tax_name varchar(45) DEFAULT NULL;
alter table crm_trn_tagreement add column gst_amount double DEFAULT '0';
alter table crm_trn_tagreement add column total_price double(13,2) DEFAULT NULL;
alter table crm_trn_tagreement add column tax_amount double DEFAULT '0';
 
 
alter table crm_trn_tagreementdtl add column taxsegment_gid varchar(64) DEFAULT NULL;
alter table crm_trn_tagreementdtl add column taxsegmenttax_gid varchar(64) DEFAULT NULL;
alter table crm_trn_tagreementdtl add column `type` varchar(45) DEFAULT NULL;
 


INSERT INTO `adm_mst_tmoduleangular` (`module_gid`,`module_gid_parent`,`module_code`,`display_order`,`module_link`,`menu_level`,`max_menulevel`,`module_name`,`status`,`image_url`,`group_type`,`modulemanager_gid`,`breadcrumb_name`,`approval_flag`,`approval_tablename`,`approval_type`,`approval_limit`,`module_flag`,`created_by`,`created_date`,`updated_by`,`updated_date`,`lw_flag`,`sref`,`icon`,`shopify_flag`,`icon_name`) VALUES ('SMRRWL','SMR','SMRRWL',1015,'ems_crm/crm_transaction.aspx#',2,NULL,'Renewal','1','../images/ico_qc_dg.gif','','','Renewal','','','','N','Y',NULL,NULL,NULL,NULL,'Y','/smr/SmrDashboard',NULL,NULL,NULL);
 
INSERT INTO `adm_mst_tmoduleangular` (`module_gid`,`module_gid_parent`,`module_code`,`display_order`,`module_link`,`menu_level`,`max_menulevel`,`module_name`,`status`,`image_url`,`group_type`,`modulemanager_gid`,`breadcrumb_name`,`approval_flag`,`approval_tablename`,`approval_type`,`approval_limit`,`module_flag`,`created_by`,`created_date`,`updated_by`,`updated_date`,`lw_flag`,`sref`,`icon`,`shopify_flag`,`icon_name`) VALUES ('SMRRWLRLM','SMRRWL','SMRRWLRLM',1703,'ems_crm/crm_trn_renewalmanagersummary.aspx',3,NULL,'Renewal Manager','1','../images/module_icon_RS.gif','RENEWAL','','Renewal Manager','','','','N','N',NULL,NULL,NULL,NULL,'Y','/smr/SmrTrnRenewalmanagersummary',NULL,NULL,NULL);
 
INSERT INTO `adm_mst_tmoduleangular` (`module_gid`,`module_gid_parent`,`module_code`,`display_order`,`module_link`,`menu_level`,`max_menulevel`,`module_name`,`status`,`image_url`,`group_type`,`modulemanager_gid`,`breadcrumb_name`,`approval_flag`,`approval_tablename`,`approval_type`,`approval_limit`,`module_flag`,`created_by`,`created_date`,`updated_by`,`updated_date`,`lw_flag`,`sref`,`icon`,`shopify_flag`,`icon_name`) VALUES ('SMRRWLRMT','SMRRWL','SMRRWLRMT',1701,'ems_crm/crm_trn_renewaldashboardtab.aspx',3,NULL,'Renewal ','1','../images/ico_manager_Approval_RS.gif','RENEWAL','','Renewal ','','','','N','N',NULL,NULL,NULL,NULL,'Y','/smr/SmrTrnRenevalsummary',NULL,NULL,NULL);
 
INSERT INTO `adm_mst_tmoduleangular` (`module_gid`,`module_gid_parent`,`module_code`,`display_order`,`module_link`,`menu_level`,`max_menulevel`,`module_name`,`status`,`image_url`,`group_type`,`modulemanager_gid`,`breadcrumb_name`,`approval_flag`,`approval_tablename`,`approval_type`,`approval_limit`,`module_flag`,`created_by`,`created_date`,`updated_by`,`updated_date`,`lw_flag`,`sref`,`icon`,`shopify_flag`,`icon_name`) VALUES ('SMRRWLRTM','SMRRWL','SMRRWLRTM',1702,'ems_crm/crm_mst_renewalteamsummary.aspx',3,NULL,'Renewal Team','1','../images/ico_department_RS.gif','RENEWAL MANAGEMENT','','Renewal Team','','','','N','N',NULL,NULL,NULL,NULL,'Y','/smr/SmrTrnRenewalteamsummary',NULL,NULL,NULL);
 
 
alter table crm_trn_trenewalteam add column campaign_prefix varchar(1000) DEFAULT NULL;
alter table crm_trn_trenewalteam add column campaign_mailid varchar(64) DEFAULT NULL;
 
INSERT INTO `adm_mst_tmoduleangular` (`module_gid`,`module_gid_parent`,`module_code`,`display_order`,`module_link`,`menu_level`,`max_menulevel`,`module_name`,`status`,`image_url`,`group_type`,`modulemanager_gid`,`breadcrumb_name`,`approval_flag`,`approval_tablename`,`approval_type`,`approval_limit`,`module_flag`,`created_by`,`created_date`,`updated_by`,`updated_date`,`lw_flag`,`sref`,`icon`,`shopify_flag`,`icon_name`) VALUES ('SMRRWLINV','SMRRWL','SMRRWLINV',1704,'ems_crm/crm_mst_renewalinvoice.aspx',3,NULL,'My Renewal','1','../images/ico_department_RS.gif','RENEWAL MANAGEMENT','','My Renewal','','','','N','N',NULL,NULL,NULL,NULL,'Y','/smr/SmrTrnRenewalInvoice',NULL,NULL,NULL); 

alter table smr_trn_tsalesorder add column leadbank_gid varchar(64) default null;
alter table smr_trn_tsalesorderdtl add column product_status varchar(64) default null;
alter table smr_trn_tsalesorderdtl add column productrequireddate_remarks longtext;
 
alter table smr_trn_tsalesorder add column `raiserenewal_flag` char(2) DEFAULT 'N';
 
INSERT INTO `adm_mst_tmoduleangular` (`module_gid`,`module_gid_parent`,`module_code`,`display_order`,`module_link`,`menu_level`,`max_menulevel`,`module_name`,`status`,`image_url`,`group_type`,`modulemanager_gid`,`breadcrumb_name`,`approval_flag`,`approval_tablename`,`approval_type`,`approval_limit`,`module_flag`,`created_by`,`created_date`,`updated_by`,`updated_date`,`lw_flag`,`sref`,`icon`,`shopify_flag`,`icon_name`) VALUES ('SMRRCPRR','SMRRCP','SMRRCPRR',1115,'ems_crm/crm_mst_renewalinvoice.aspx',3,NULL,'Renewal Report','1','../images/ico_department_RS.gif','REPORT','','Renewal Report','','','','N','N',NULL,NULL,NULL,NULL,'Y','/smr/SmrRptRenewalreport',NULL,NULL,NULL);
 
 
 
Alter table crm_trn_trenewal2campaign modify column updated_by varchar(64) DEFAULT NULL;
Alter table crm_trn_trenewal2campaign modify column updated_date date DEFAULT NULL;
Alter table crm_trn_trenewal2campaign add column renewal_gid varchar(64) default null;



/*
-- Query: select * from adm_mst_tmoduleangular where module_name='Receipt Approval'
-- Date: 2024-08-17 15:56
*/
INSERT INTO `adm_mst_tmoduleangular` (`module_gid`,`module_gid_parent`,`module_code`,`display_order`,
`module_link`,`menu_level`,`max_menulevel`,`module_name`,`status`,`image_url`,`group_type`,
`modulemanager_gid`,`breadcrumb_name`,`approval_flag`,`approval_tablename`,`approval_type`,
`approval_limit`,`module_flag`,`created_by`,`created_date`,
`updated_by`,`updated_date`,`lw_flag`,`sref`,`icon`,`shopify_flag`,`icon_name`,`k_sref`)
 VALUES ('RBLPAYPYA','SMRINM','RBLPAYPYA',962,'ems_rbl/rbl_mst_paymentapprove.aspx'
 ,3,3,'Receipt Approval','1','../images/ico_payment_approval_RS.png','FINANCE','',
 'Receipt Approval','T','','','N','Y',NULL,NULL,NULL,NULL,'Y','/smr/SmrTrnReceiptapproval'
 ,NULL,NULL,NULL,NULL);


INSERT INTO `adm_mst_tsequence` (`sequence_gid`,`sequence_code`,`sequence_name`,`sequence_format`,`sequence_curval`,`sequence_flag`,`branch_flag`,`department_flag`,`year_flag`,
`month_flag`,`location_flag`,`company_code`,`delimeter`,`runningno_prefix`,`finyear`,`carry_forward`,`created_by`,`created_date`,`updated_by`,`updated_date`,
`product_flag`,`section_flag`,`block_flag`,`unit_flag`) VALUES ('SSQM1703324630','DG22','GLCode Tax','4','2208','N','N','N','N','','N','','','','2024','N',NULL,NULL,NULL,NULL,'Y','Y','Y','Y');




alter table smr_trn_tsalesorder add column file_path varchar(64) default null;

alter table acp_trn_torder add column file_path varchar(64) default null;





INSERT INTO `vcidex`.`adm_mst_tmoduleangular` (`module_gid`, `module_gid_parent`, `module_code`, `display_order`, `module_link`, `menu_level`, `module_name`, `status`, `group_type`, `breadcrumb_name`, `approval_flag`, `approval_limit`, `module_flag`, `lw_flag`, `sref`) VALUES ('SMRRPTRR', 'SMRRCP', 'SMRRPTRR', '1116', ' ', '3', 'Receipt Report', '1', 'Report', 'Receipt Report', '', 'N', 'N', 'Y', '/smr/SmrRptReceiptreport');
INSERT INTO `vcidex`.`adm_mst_tmoduleangular` (`module_gid`, `module_gid_parent`, `module_code`, `display_order`, `module_link`, `menu_level`, `module_name`, `status`, `group_type`, `breadcrumb_name`, `approval_limit`, `module_flag`, `lw_flag`, `sref`) VALUES ('SMRRPTCR', 'SMRRCP', 'SMRRPTCR', '985', ' ', '3', 'Customer Report', '1', 'Report', 'Customer Report', 'N', 'N', 'Y', '/smr/SmrRptCustomerreport');



UPDATE `vcidex`.`adm_mst_tmoduleangular` SET `display_order` = '1111' WHERE (`module_gid` = 'SMRRCPSSR');
UPDATE `vcidex`.`adm_mst_tmoduleangular` SET `display_order` = '1114' WHERE (`module_gid` = 'SMRRCPORT');

UPDATE `vcidex`.`adm_mst_tmoduleangular` SET `display_order` = '1113', `module_name` = 'Sales Order Report' WHERE (`module_gid` = 'SMRRCPORT');
UPDATE `vcidex`.`adm_mst_tmoduleangular` SET `display_order` = '1114' WHERE (`module_gid` = 'SMRRCPOVE');

UPDATE `vcidex`.`adm_mst_tmoduleangular` SET `display_order` = '1390' WHERE (`module_gid` = 'SMRRCPRR');
UPDATE `vcidex`.`adm_mst_tmoduleangular` SET `display_order` = '1115' WHERE (`module_gid` = 'SMRRPTSIR');

UPDATE `vcidex`.`adm_mst_tmoduleangular` SET `display_order` = '983' WHERE (`module_gid` = 'SMRRCPRR');
UPDATE `vcidex`.`adm_mst_tmoduleangular` SET `display_order` = '1390' WHERE (`module_gid` = 'SMRRPTAGR');


UPDATE `vcidex`.`adm_mst_tmoduleangular` SET `display_order` = '1118' WHERE (`module_gid` = 'SMRRPTAGR');
UPDATE `vcidex`.`adm_mst_tmoduleangular` SET `display_order` = '1117' WHERE (`module_gid` = 'SMRRPTOTR');
UPDATE `vcidex`.`adm_mst_tmoduleangular` SET `display_order` = '1119' WHERE (`module_gid` = 'SMRRPTCR');
UPDATE `vcidex`.`adm_mst_tmoduleangular` SET `display_order` = '1120' WHERE (`module_gid` = 'SMRRCPRR');



alter table adm_mst_tcompany add column mintsoft_flag varchar(64);
alter table pmr_mst_tproduct add column mintsoftproduct_id varchar(256);
alter table pmr_trn_tpurchaseorder add column mintsoftasn_id varchar(256);


CREATE TABLE `crm_smm_tmintsoftsuppliers` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `supplier_id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
   `supplier_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `contact_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `contact_number` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `address_line1` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `address_line2` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `address_line3` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `town` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `county` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `postcode` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `country_id` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `country` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `contact_email` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `is_active` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `code` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `currency_id` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `currency` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `client_id` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `created_by` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `created_date` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci





CREATE TABLE `crm_smm_tmintsoftcourierservice` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `courierservice_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `courierservicetype_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `name` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
   `trackingurl` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `activeb` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `created_by` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `created_date` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8




INSERT INTO `adm_mst_tsequence` (`sequence_gid`,`sequence_code`,`sequence_name`,`sequence_format`,`sequence_curval`,`sequence_flag`,`branch_flag`,`department_flag`,`year_flag`,
`month_flag`,`location_flag`,`company_code`,`delimeter`,`runningno_prefix`,`finyear`,`carry_forward`,`created_by`,`created_date`,`updated_by`,`updated_date`,
`product_flag`,`section_flag`,`block_flag`,`unit_flag`) VALUES ('SSQM1703324630','DG22','GLCode Tax','4','2208','N','N','N','N','','N','','','','2024','N',NULL,NULL,NULL,NULL,'Y','Y','Y','Y');
 
03/09/2024-Hariharan(sales and purchase)
alter table acp_mst_ttaxsegment add column reference_type varchar(64) default null;
alter table acp_mst_ttaxsegment add column accountgroup_gid varchar(64) default null;
alter table acp_mst_ttaxsegment add column account_gid varchar(64) default null;
alter table acp_mst_ttaxsegment add column account_name varchar(64) default null;
alter table acp_mst_ttax add column reference_type varchar(64) default null;
set sql_safe_updates=0;
update acp_mst_ttaxsegment set reference_type='Customer';
update acp_mst_Ttax set reference_type='Customer';

Maxson-11/09/2024

for test mintsoft
UPDATE `boba_tea`.`smr_trn_tminsoftconfig` SET `api_key` = 'a4d4b5f3-8779-44b0-8362-e49e541744d0', `client_id` = '8' WHERE (`Id` = '1');

UPDATE `boba_tea`.`smr_trn_tminsoftconfig` SET `api_key` = 'a4d4b5f3-8779-44b0-8362-e49e541744d0', `client_id` = '8' WHERE (`Id` = '2');

 
Alter table smr_trn_tminsoftconfig add column client_id varchar(50) default null;
 
alter table smr_tmp_tsalesorderdtl add column enquirydtl_gid varchar(64) default null;

alter table smr_tmp_tsalesorderdtl add column enquiry_gid varchar(64) default null;
  alter table smr_tmp_treceivequotationdtl add column enquirydtl_gid varchar(256) default null
 
for live 
client_id  = 9
 
alter table smr_trn_tdelivery_order add column tracker_url varchar(500) default null;






INSERT INTO `adm_mst_tmoduleangular` (`module_gid`,`module_gid_parent`,`module_code`,`display_order`,`module_link`,`menu_level`,`max_menulevel`,`module_name`,`status`,`image_url`,`group_type`,`modulemanager_gid`,`breadcrumb_name`,`approval_flag`,`approval_tablename`,`approval_type`,`approval_limit`,`module_flag`,`created_by`,`created_date`,`updated_by`,`updated_date`,`lw_flag`,`sref`,`icon`,`shopify_flag`,`icon_name`,`k_sref`) VALUES ('PMRTRNPAM','PMRTRN','PMRTRNPAM',1007,'ems_pmr/pmr_trn_operationpurchaseorderapprovalsummary.aspx',3,NULL,'Agreement','1','../images/ico_report_grn_RS.png','Agreement','','Agreement','Y','','Sequence','Y','N',NULL,NULL,NULL,NULL,'Y','/pmr/PmrTrnPurchaseagreement',NULL,NULL,NULL,NULL);

alter table pbl_trn_tagreement add column `shipping_address` longtext;
alter table pbl_trn_tagreement add column `netamount` varchar(64) DEFAULT NULL;
alter table pbl_trn_tagreement add column `addon_amount` double DEFAULT '0';
alter table pbl_trn_tagreement add column `freightcharges` double DEFAULT '0';
alter table pbl_trn_tagreement add column `discount_amount` double DEFAULT '0';
alter table pbl_trn_tagreement add column `tax_gid` varchar(64) DEFAULT NULL;
alter table pbl_trn_tagreement add column `tax_percentage` double DEFAULT '0';
alter table pbl_trn_tagreement add column `tax_amount` double DEFAULT '0';
alter table pbl_trn_tagreement add column`roundoff` double DEFAULT '0';
alter table pbl_trn_tagreement add column `taxsegment_gid` varchar(64) DEFAULT NULL;
 

alter table pmr_trn_trenewal add column  `agreement_gid` varchar(64) DEFAULT NULL;
alter table pmr_trn_trenewal add column `renewal_type` varchar(64) DEFAULT NULL;

alter table pbl_trn_tagreementdtl add column `product_code` varchar(64) DEFAULT NULL;
alter table pbl_trn_tagreementdtl modify column `product_name` varchar(1000) DEFAULT NULL;
alter table pbl_trn_tagreementdtl add column `productuom_name` varchar(256) DEFAULT NULL;
alter table pbl_trn_tagreementdtl add column `producttype_gid` varchar(256) DEFAULT NULL;
alter table pbl_trn_tagreementdtl add column `producttotal_amount` varchar(64) DEFAULT NULL;
alter table pbl_trn_tagreementdtl add column `qty_ordered` double DEFAULT '0';
alter table pbl_trn_tagreementdtl add column `display_field_name` longtext;
alter table pbl_trn_tagreementdtl add column `tax_amount1_L` double DEFAULT '0';
alter table pbl_trn_tagreementdtl add column `taxsegmenttax_gid` longtext;
alter table pbl_trn_tagreementdtl add column `taxsegment_gid` varchar(64) DEFAULT NULL;
CREATE TABLE `pmr_trn_trenewal` (
`renewal_gid` varchar(64) NOT NULL,
`renewal_date` datetime DEFAULT NULL,
`renewal_description` text,
`created_by` varchar(16) DEFAULT NULL,
`created_date` datetime DEFAULT NULL,
`vendor_gid` varchar(64) DEFAULT NULL,
`renewal_acknowleged` char(1) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT 'N',
`purchaseorder_gid` varchar(64) DEFAULT NULL,
`reference_gid` varchar(64) DEFAULT NULL,
`so_raised` varchar(2) DEFAULT '0',
`assigned` char(1) DEFAULT 'N',
`assigned_by` varchar(45) DEFAULT NULL,
`renewal_status` varchar(64) DEFAULT 'Open',
`followup_date` varchar(45) DEFAULT NULL,
`renewal_to` varchar(45) DEFAULT NULL,
`renewal_type` varchar(45) DEFAULT NULL,
`renewal_flag` varchar(264) DEFAULT NULL,
`frequency_term` varchar(64) DEFAULT NULL,
`updated_date` datetime DEFAULT NULL,
`agreement_gid` varchar(64) DEFAULT NULL,
PRIMARY KEY (`renewal_gid`)
) ;
alter table pmr_trn_tpurchaseorder add column `renewal_flag` char(2) DEFAULT 'N';
alter table pbl_trn_tagreement add column `mode_despatch`varchar(64) DEFAULT NULL;

alter table pbl_trn_tagreement add column `packing_charges` double DEFAULT '0';
alter table pbl_trn_tagreement add column `insurance_charges` double DEFAULT '0';

17/09/2024--Hariharan

ALTER TABLE adm_mst_tcompany ADD COLUMN einvoice_flag  varchar(16) default'N';
set sql_safe_updates=0;

update adm_mst_Tcompany set einvoice_flag='Y';
insert into adm_mst_tmoduleangular(module_gid, module_gid_parent, module_code, display_order, module_link, menu_level, max_menulevel, module_name, status, image_url, group_type, modulemanager_gid, breadcrumb_name, approval_flag, approval_tablename, approval_type, approval_limit, module_flag, created_by, created_date, updated_by, updated_date, lw_flag, sref, icon, shopify_flag, icon_name, k_sref)
values(
'SMRPAYCNS', 'SMRINM', 'SMRPAYCNS', '3014', 'ems_rbl/rbl_trn_creditnotesummary.aspx', '3', '3', 'Credit Note', '1', '../../images/ico_emp_details.gif', 'FINANCE', '', 'Credit Note', '', '', '', 'N', 'N', NULL, NULL, NULL, NULL, 'N', NULL, NULL, NULL, NULL, NULL);
UPDATE `vcidex`.`adm_mst_tmoduleangular` SET `lw_flag` = 'Y', `sref` = '/smr/SmrTrnCreditNoteSummary' WHERE (`module_gid` = 'SMRPAYCNS');

CREATE TABLE `rbl_trn_teinvoiceconfig` (
  `Id` bigint(20) NOT NULL AUTO_INCREMENT,
  `einvoiceAutenticationURL` varchar(256) DEFAULT NULL,
  `einvoiceIRNGenerate` varchar(500) DEFAULT NULL,
  `gspappid` varchar(500) DEFAULT NULL,
  `gspappsecret` varchar(500) DEFAULT NULL,
  `einvoiceuser_name` varchar(500) DEFAULT NULL,
  `einvoicepwd` varchar(500) DEFAULT NULL,
  `einvoicegstin` varchar(500) DEFAULT NULL,
  `einvoice_Auth` varchar(500) DEFAULT NULL,
  `generateQRURL` varchar(500) DEFAULT NULL,
  `cancleIRN` varchar(500) DEFAULT NULL,
  `updated_by` varchar(64) DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  `updated_date` datetime DEFAULT NULL,
  `created_by` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`Id`)
);
 
alter table rbl_trn_teinvoiceconfig add column einvoice_flag varchar(64) default null;
 
 INSERT INTO `vcidex`.`rbl_trn_teinvoiceconfig` (`Id`, `einvoiceAutenticationURL`, `einvoiceIRNGenerate`, `gspappid`, `gspappsecret`, `einvoiceuser_name`, `einvoicepwd`, `einvoicegstin`, `generateQRURL`, `cancleIRN`) VALUES ('1', '
https://gsp.adaequare.com/gsp/authenticate?grant_type=token\'
,
https://gsp.adaequare.com/test/enriched/ei/api/invoice'
, '0386E490581647B791B663BECC63D576', '0E1FC6D4G7E41G4A93GAB72G5E01E9CD32ED', 'adqgsptnusr1', 'Gsp@1234', '33AMBPG7773M014', '
https://gsp.adaequare.com/test/enriched/ei/others/qr/image'
, '
https://gsp.adaequare.com/test/enriched/ei/api/invoice/cancel"');
UPDATE `vcidex`.`rbl_trn_teinvoiceconfig` SET `einvoice_flag` = 'Y' WHERE (`Id` = '1');

19/09/2024--Hariharan

ALTER TABLE adm_mst_tcompany ADD COLUMN einvoice_flag  varchar(16) default'N';
set sql_safe_updates=0;

update adm_mst_Tcompany set einvoice_flag='Y';

insert into adm_mst_tmoduleangular(module_gid, module_gid_parent, module_code, display_order, module_link, menu_level, max_menulevel, module_name, status, image_url, group_type, modulemanager_gid, breadcrumb_name, approval_flag, approval_tablename, approval_type, approval_limit, module_flag, created_by, created_date, updated_by, updated_date, lw_flag, sref, icon, shopify_flag, icon_name, k_sref)
values(
'SMRPAYCNS', 'SMRINM', 'SMRPAYCNS', '3014', 'ems_rbl/rbl_trn_creditnotesummary.aspx', '3', '3', 'Credit Note', '1', '../../images/ico_emp_details.gif', 'FINANCE', '', 'Credit Note', '', '', '', 'N', 'N', NULL, NULL, NULL, NULL, 'N', NULL, NULL, NULL, NULL, NULL);
UPDATE `vcidex`.`adm_mst_tmoduleangular` SET `lw_flag` = 'Y', `sref` = '/smr/SmrTrnCreditNoteSummary' WHERE (`module_gid` = 'SMRPAYCNS');

CREATE TABLE `rbl_trn_teinvoiceconfig` (
  `Id` bigint(20) NOT NULL AUTO_INCREMENT,
  `einvoiceAutenticationURL` varchar(256) DEFAULT NULL,
  `einvoiceIRNGenerate` varchar(500) DEFAULT NULL,
  `gspappid` varchar(500) DEFAULT NULL,
  `gspappsecret` varchar(500) DEFAULT NULL,
  `einvoiceuser_name` varchar(500) DEFAULT NULL,
  `einvoicepwd` varchar(500) DEFAULT NULL,
  `einvoicegstin` varchar(500) DEFAULT NULL,
  `einvoice_Auth` varchar(500) DEFAULT NULL,
  `generateQRURL` varchar(500) DEFAULT NULL,
  `cancleIRN` varchar(500) DEFAULT NULL,
  `updated_by` varchar(64) DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  `updated_date` datetime DEFAULT NULL,
  `created_by` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`Id`)
);
 
alter table rbl_trn_teinvoiceconfig add column einvoice_flag varchar(64) default null;
 
 INSERT INTO `vcidex`.`rbl_trn_teinvoiceconfig` (`Id`, `einvoiceAutenticationURL`, `einvoiceIRNGenerate`, `gspappid`, `gspappsecret`, `einvoiceuser_name`, `einvoicepwd`, `einvoicegstin`, `generateQRURL`, `cancleIRN`) VALUES ('1', '
https://gsp.adaequare.com/gsp/authenticate?grant_type=token\'
,
https://gsp.adaequare.com/test/enriched/ei/api/invoice'
, '0386E490581647B791B663BECC63D576', '0E1FC6D4G7E41G4A93GAB72G5E01E9CD32ED', 'adqgsptnusr1', 'Gsp@1234', '33AMBPG7773M014', '
https://gsp.adaequare.com/test/enriched/ei/others/qr/image'
, '
https://gsp.adaequare.com/test/enriched/ei/api/invoice/cancel"');
UPDATE `vcidex`.`rbl_trn_teinvoiceconfig` SET `einvoice_flag` = 'Y' WHERE (`Id` = '1');


24/09/2024--Hariharan

  CREATE TABLE `crm_smm_tmintsoftcourierservice` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `courierservice_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `courierservicetype_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `name` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
   `trackingurl` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `activeb` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `created_by` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `created_date` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8;
 CREATE TABLE `crm_smm_tmintsoftcourierservicetype` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `courierservicetype_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
   `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
   `active` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
   `created_by` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `created_date` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
 CREATE TABLE `crm_smm_tmintsoftorderchannel` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `channel_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `name` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
   `description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
   `active` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `logo` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `client_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `created_by` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `created_date` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=87 DEFAULT CHARSET=utf8;
 CREATE TABLE `crm_smm_tmintsoftorderstatuses` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `orderstatus_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `name` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
   `externalname` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
   `created_by` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `created_date` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=70 DEFAULT CHARSET=utf8;
 CREATE TABLE `crm_smm_tmintsoftasnstatuses` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `asnstatus_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `asnstatus_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `colour` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `text_colour` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `created_date` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `created_by` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;
 CREATE TABLE `crm_smm_tmintsoftasngoodsintypes` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `goodsintypes_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `goodsintypes_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;
 CREATE TABLE `crm_smm_tmintsoftclientdetails` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `client_id` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `shortname` text COLLATE utf8mb4_unicode_ci,
   `client_name` text COLLATE utf8mb4_unicode_ci,
   `client_code` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `brandname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `contactname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `contactnumber` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `addressline1` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `addressline2` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `addressline3` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `town` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `county` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `postcode` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `ppinumber` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `vatnumber` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `eorinumber` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `vatexempt` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `nireorinumber` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `iossnumber` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `countryid` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `contactemail` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `packaginginstructions` text COLLATE utf8mb4_unicode_ci,
   `currencyid` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `active` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `onstop` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `accountingintegrationtype` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `customerregistrationnumber` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `created_by` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `created_date` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
 CREATE TABLE `crm_smm_tmintsoftcountries` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `country_id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
   `country_name` varchar(1020) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `code` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `code3` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `created_by` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `created_date` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=254 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
 CREATE TABLE `crm_smm_tmintsoftcurrencies` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `currency_id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
   `currency_name` varchar(1020) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `currency_code` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `symbol` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `created_by` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `created_date` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
 alter table adm_mst_tcompany add column mintsoft_flag varchar(64);
alter table pmr_mst_tproduct add column mintsoftproduct_id varchar(256);
alter table acp_mst_tvendor add column mintsoft_countryid varchar(256);
alter table acp_mst_tvendor add column mintsoft_currencyid varchar(256);
alter table adm_mst_tcountry add column mintsoft_countryid varchar(256);
alter table adm_mst_tcountry add column  mintsoft_code varchar(256);
alter table adm_mst_tcountry add column  mintsoft_code3 varchar(256);
alter table crm_trn_tcurrencyexchange add column mintsoft_currencyid varchar(256);
alter table smr_trn_tsalesorder add column mintsoftinvoice_flag varchar(64) default 'N';
alter table crm_trn_tcurrencyexchange add column  mintsoft_currencyname varchar(256);
CREATE TABLE `smr_trn_tminsoftconfig` (
  `Id` BIGINT auto_increment NOT NULL,
  `base_url` VARCHAR(256) DEFAULT NULL,
  `api_key` VARCHAR(500) DEFAULT NULL,
  PRIMARY KEY (`Id`)
);

alter table smr_trn_tminsoftconfig add column mintsoft_flag varchar(64) default 'Y';
alter table smr_trn_tminsoftconfig add column updated_date datetime default null;
alter table smr_trn_tminsoftconfig add column created_date datetime default null;
alter table smr_trn_tminsoftconfig add column updated_by varchar(64) default null;
alter table smr_trn_tminsoftconfig add column created_by varchar(64) default null;
alter table acp_mst_tvendor add column supplier_id varchar(64);
alter table pmr_trn_tgrn add column mintsoftasn_id varchar(64);
alter table smr_tmp_tsalesorderdtl add column enquirydtl_gid varchar(64) default null;
alter table smr_tmp_tsalesorderdtl add column enquiry_gid varchar(64) default null;
alter table smr_tmp_treceivequotationdtl add column enquirydtl_gid varchar(256) default null;
set sql_safe_updates=0;
UPDATE `media`.`acp_mst_ttax` SET `tax_name` = 'IGST 18%' WHERE (`tax_gid` = 'STXM2407013');
UPDATE `media`.`smr_trn_treceivequotationdtl` SET `tax_name` = 'IGST 18%' WHERE (`quotationdtl_gid` = 'VQDT24082968');

alter table acp_mst_ttaxsegment add column reference_type varchar(64) default null;
alter table acp_mst_ttaxsegment add column accountgroup_gid varchar(64) default null;
alter table acp_mst_ttaxsegment add column account_gid varchar(64) default null;
alter table acp_mst_ttaxsegment add column account_name varchar(64) default null;
alter table acp_mst_ttax add column reference_type varchar(64) default null;
set sql_safe_updates=0;
update acp_mst_ttaxsegment set reference_type='Customer';
update acp_mst_Ttax set reference_type='Customer';

alter table acp_trn_torder add column file_path varchar(64) default null;
 
alter table smr_trn_tsalesorder add column file_path varchar(64) default null;

alter table smr_trn_tsalesorder add column file_name varchar(64) default null;


alter table acp_mst_ttaxsegment add column reference_type varchar(64) default null;

alter table acp_mst_ttaxsegment add column accountgroup_gid varchar(64) default null;

alter table acp_mst_ttaxsegment add column account_gid varchar(64) default null;

alter table acp_mst_ttaxsegment add column account_name varchar(64) default null;

alter table acp_mst_ttax add column reference_type varchar(64) default null;

set sql_safe_updates=0;

update acp_mst_ttaxsegment set reference_type='Customer';

update acp_mst_Ttax set reference_type='Customer';
update adm_mst_tvendor set taxsegment_gid=null;
alter table rbl_trn_Tinvoice modify column payment_days varchar(64) default null;
alter table rbl_trn_Tinvoice modify column payment_term varchar(64) default null;
alter table smr_trn_tsalesorder modify column payment_days varchar(64) default null;
alter table smr_trn_tsalesorder modify column delivery_days varchar(64) default null;
alter table acp_trn_torder modify column payment_days varchar(64) default null;
alter table acp_trn_torder modify column delivery_days varchar(64) default null;
 
 24/09/2024--Maria Maxson

INSERT INTO `adm_mst_tmoduleangular` (`module_gid`,`module_gid_parent`,`module_code`,
`display_order`,`module_link`,`menu_level`,`max_menulevel`,`module_name`,`status`,
`image_url`,`group_type`,`modulemanager_gid`,`breadcrumb_name`,`approval_flag`,
`approval_tablename`,`approval_type`,`approval_limit`,`module_flag`,`created_by`,
`created_date`,`updated_by`,`updated_date`,`lw_flag`,`sref`,`icon`,`shopify_flag`,
`icon_name`,`k_sref`) VALUES ('SMRPRT','SMR','SMRPRT',3013,'ems_crm/crm_transaction.aspx#'
,2,NULL,'Product Report','1','../images/Reports.gif','','','Product Report'
,'','','','N','N',NULL,NULL,NULL,NULL,'Y',NULL,NULL,NULL,NULL,NULL);



INSERT INTO `adm_mst_tmoduleangular` (`module_gid`,`module_gid_parent`,`module_code`,
`display_order`,`module_link`,`menu_level`,`max_menulevel`,`module_name`,`status`,
`image_url`,`group_type`,`modulemanager_gid`,`breadcrumb_name`,`approval_flag`,
`approval_tablename`,`approval_type`,`approval_limit`,`module_flag`,`created_by`,
`created_date`,`updated_by`,`updated_date`,`lw_flag`,`sref`,`icon`,`shopify_flag`,
`icon_name`,`k_sref`) VALUES ('SMRPDTPGR','SMRPRT','SMRPDTPGR',1601,
'ems_crm/crm_rpt_salesorderproductgroupreport.aspx',3,NULL,'Groupwise Report',
'1','','Product Report','','Product Groupwise Report','','','Sequence','N','N',NULL,NULL,NULL,NULL,'Y',
'/crm/CrmRptProductgroupreport',NULL,NULL,NULL,NULL);



INSERT INTO `adm_mst_tmoduleangular` (`module_gid`,`module_gid_parent`,`module_code`,
`display_order`,`module_link`,`menu_level`,`max_menulevel`,`module_name`,`status`,
`image_url`,`group_type`,`modulemanager_gid`,`breadcrumb_name`,`approval_flag`,
`approval_tablename`,`approval_type`,`approval_limit`,`module_flag`,`created_by`,
`created_date`,`updated_by`,`updated_date`,`lw_flag`,`sref`,`icon`,`shopify_flag`,
`icon_name`,`k_sref`) VALUES ('SMRPDTPSR','SMRPRT','SMRPDTPSR',1602,
'ems_crm/crm_rpt_salesorderproductgroupreport.aspx',3,NULL,'Selling Report',
'1','','Product Report','','Product Selling Report','','','Sequence','N','N',NULL,NULL,NULL,NULL,'Y',
'/smr/smrrptproductsellingreport',NULL,NULL,NULL,NULL);



INSERT INTO `adm_mst_tmoduleangular` (`module_gid`,`module_gid_parent`,`module_code`,
`display_order`,`module_link`,`menu_level`,`max_menulevel`,`module_name`,`status`,
`image_url`,`group_type`,`modulemanager_gid`,`breadcrumb_name`,`approval_flag`,
`approval_tablename`,`approval_type`,`approval_limit`,`module_flag`,`created_by`,
`created_date`,`updated_by`,`updated_date`,`lw_flag`,`sref`,`icon`,`shopify_flag`,
`icon_name`,`k_sref`) VALUES ('SMRPDTPCR','SMRPRT','SMRPDTPCR',1603,
'ems_crm/crm_rpt_salesorderproductgroupreport.aspx',3,NULL,'Consumption Report',
'1','','Product Report','','Consumption Report','','','Sequence','N','N',NULL,NULL,NULL,NULL,'Y',
'/smr/smrrptproductconsumptionreports',NULL,NULL,NULL,NULL);


UPDATE `boba_tea`.`adm_mst_tmoduleangular` SET `sref` = '/smr/smrrptproductgroupreport' WHERE (`module_gid` = 'SMRPDTPGR');

/*
-- Query: select * from rbl_trn_teinvoiceconfig
-- Date: 2024-09-20 18:54
*/
INSERT INTO `rbl_trn_teinvoiceconfig` (`Id`,`einvoiceAutenticationURL`,`einvoiceIRNGenerate`,`gspappid`,`gspappsecret`,`einvoiceuser_name`,`einvoicepwd`,`einvoicegstin`,`einvoice_Auth`,`generateQRURL`,`cancleIRN`,`updated_by`,`created_date`,`updated_date`,`created_by`,`einvoice_flag`) VALUES (1,'https://gsp.adaequare.com/gsp/authenticate?grant_type=token','https://gsp.adaequare.com/test/enriched/ei/api/invoice','0386E490581647B791B663BECC63D576','0E1FC6D4G7E41G4A93GAB72G5E01E9CD32ED','adqgsptnusr1','Gsp@1234','33AMBPG7773M014',NULL,'https://gsp.adaequare.com/test/enriched/ei/others/qr/image','https://gsp.adaequare.com/test/enriched/ei/api/invoice/cancel',NULL,NULL,NULL,NULL,'Y');

 25/09/2024--Hariharan

drop procedure smr_trn_invoicesummary;
DELIMITER $$
CREATE  PROCEDURE `smr_trn_invoicesummary`( in lscurrency varchar(64))
BEGIN
	 select distinct a.invoice_gid,x.company_code, s.salesorder_status, a.irn,x.einvoice_flag,case when a.invoice_reference like '%AREF%' then j.agreement_referencenumber else  
                 s.so_referenceno1  end as so_referencenumber,
                 a.invoice_refno,
	CASE 
    WHEN a.irncancel_date IS NOT NULL THEN 'IRN Cancelled' 
    WHEN a.creditnote_status = 'Y' THEN 'Credit Noted'
    ELSE a.invoice_status
END AS status,
                 a.mail_status,a.customer_gid,a.invoice_date,a.invoice_reference,a.additionalcharges_amount,a.discount_amount,  
                 CASE when a.payment_flag <> 'PY Pending' then a.payment_flag else a.invoice_flag end as 'overall_status', a.mail_status,
                 a.payment_flag,  format(a.initialinvoice_amount,2) as initialinvoice_amount,s.salesorder_gid,a.invoice_status,a.invoice_flag,  
                 case when a.irn is not null then 'IRN Generated' else 'IRN Pending' end as 'irn_status',case when a.irncancel_date is not null then 'IRN Cancelled' else '' end as 'irncancel_status',
                 format(a.invoice_amount,2) as invoice_amount,c.customer_code,
                 case when a.customer_contactnumber is null then concat(a.customer_contactperson,' / ',a.customer_contactnumber)  
                 else  concat(a.customer_contactperson,' / ',a.customer_contactnumber, if(m.email='',' ',concat(' / ',m.email))) end as customer_contactperson,  
                 case when a.currency_code = lscurrency then concat(c.customer_id,' / ',c.customer_name)  when a.currency_code is null then concat(c.customer_id,' / ', c.customer_name)  
                 when a.currency_code is not null and a.currency_code <> lscurrency then concat(c.customer_name,' / ',h.country) end as customer_name,a.currency_code,  
                 a.customer_contactnumber  as mobile,a.invoice_from,i.directorder_gid,a.progressive_invoice  
                 from rbl_trn_tinvoice a  
                 left join rbl_trn_tinvoicedtl b on a.invoice_gid = b.invoice_gid  
                 left join crm_mst_tcustomer c on a.customer_gid = c.customer_gid  
                 left join crm_mst_tcustomercontact  m  on a.customer_gid = m.customer_gid  
                 left join crm_trn_tcurrencyexchange h on a.currency_code = h.currency_code  
                  left join adm_mst_tcompany x on 1=1
                 left join smr_trn_tsalesorder s on a.invoice_reference = s.salesorder_gid  
                 left join crm_trn_tagreement j on j.agreement_gid = a.invoice_reference  
                 left join smr_trn_tdeliveryorder i on s.salesorder_gid=i.salesorder_gid  
                 where a.invoice_type<>'Opening Invoice' and a.invoice_status<>'Invoice Cancelled'  group by a.invoice_gid order by date(a.invoice_date) desc,a.invoice_date asc, a.invoice_gid desc;
END$$
DELIMITER ;
DELIMITER $$
CREATE PROCEDURE smr_trn_tshopifyordertoinvoice()
BEGIN
    SELECT * FROM smr_trn_tsalesorder WHERE 
fulfillment_status = 'fulfilled' AND salesorder_date >= CURDATE() - INTERVAL 1 month ;
END$$
DELIMITER ;

CREATE TABLE `rbl_trn_teinvoiceconfig` (
  `Id` bigint(20) NOT NULL AUTO_INCREMENT,
  `einvoiceAutenticationURL` varchar(256) DEFAULT NULL,
  `einvoiceIRNGenerate` varchar(500) DEFAULT NULL,
  `gspappid` varchar(500) DEFAULT NULL,
  `gspappsecret` varchar(500) DEFAULT NULL,
  `einvoiceuser_name` varchar(500) DEFAULT NULL,
  `einvoicepwd` varchar(500) DEFAULT NULL,
  `einvoicegstin` varchar(500) DEFAULT NULL,
  `einvoice_Auth` varchar(500) DEFAULT NULL,
  `generateQRURL` varchar(500) DEFAULT NULL,
  `cancleIRN` varchar(500) DEFAULT NULL,
  `updated_by` varchar(64) DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  `updated_date` datetime DEFAULT NULL,
  `created_by` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`Id`)
);
 
alter table rbl_trn_teinvoiceconfig add column einvoice_flag varchar(64) default null;

DELIMITER $$
CREATE PROCEDURE adm_mst_spSequenceGid(IN pModule_Code VARCHAR(1024))
BEGIN
    DECLARE lsfinyear VARCHAR(500);
    DECLARE lsSeqNo INT;
    DECLARE lsTempGid VARCHAR(100); 
    DECLARE lsSeqflag VARCHAR(1) DEFAULT '';
    DECLARE lsSequenceCurval VARCHAR(45) DEFAULT '';
    DECLARE lsSequenceFormat VARCHAR(45) DEFAULT '';
    START TRANSACTION;
    SELECT YEAR(fyear_start) INTO lsfinyear
    FROM adm_mst_tyearendactivities
    WHERE CURDATE() BETWEEN fyear_start AND fyear_end;
    SET lsfinyear = IF(lsfinyear IS NULL OR lsfinyear = '',
                       (SELECT YEAR(fyear_start) 
                        FROM adm_mst_tyearendactivities
                        ORDER BY finyear_gid DESC
                        LIMIT 1),
                       lsfinyear);
    SELECT sequence_flag, sequence_curval, sequence_format 
    INTO lsSeqflag, lsSequenceCurval, lsSequenceFormat
    FROM adm_mst_tsequence
    WHERE sequence_code = pModule_Code AND finyear = lsfinyear
    LIMIT 1;
    SET lsSeqNo = lsSequenceCurval + 1;
    SET lsTempGid = CASE
        WHEN lsSeqflag = 'Y' THEN
            CONCAT(pModule_Code, LPAD(lsSeqNo, lsSequenceFormat, '0'))
        WHEN lsSeqflag = 'N' THEN
            CONCAT(pModule_Code, DATE_FORMAT(CURDATE(), '%Y%m%d'), lsSeqNo)
        ELSE
            'E'
    END;
    UPDATE adm_mst_tsequence
    SET sequence_curval = lsSeqNo
    WHERE sequence_code = pModule_Code AND finyear = lsfinyear;
    COMMIT;
    SELECT lsTempGid;
END$$
DELIMITER ;

01/10/2024--Hariharan

alter table rbl_trn_tinvoice modify column creditnote_irn varchar(256) default null;
alter table rbl_trn_tinvoice add column creditnote_irn varchar(64) default null;

04/10/2024--Sridhar

alter table smr_trn_tsalesreturn add column `return_type` varchar(64) DEFAULT NULL; 
alter table smr_trn_tsalesreturn add column `remarks` varchar(250) DEFAULT NULL;