30/07/2024 -Karthikeyan

Opening Balance Table
--------------------------
alter table acc_trn_topeningbalance add column subgroup_account_gid varchar(64) DEFAULT NULL after account_gid;
alter table acc_trn_topeningbalance add column group_account_gid varchar(64) DEFAULT NULL after subgroup_account_gid;

20/08/2024 - Venkat

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('FMSEXSUN', 'FMSEXPMAN', 'FMSEXSUN', '1461', 'ems_finance/acc_rpt_bs.aspx', '3', NULL, ' Sundry Expenses ', '1', '../images/ico_stock_report_RS.png', 'SUNDRY EXPENSES', NULL, 'Sundry Expenses', NULL, NULL, NULL, 'N', 'N', NULL, NULL, NULL, NULL, 'Y', '/finance/AccTrnSundryexpenseSummary', 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(
'SSQM2403311399', 'SUEP', 'Sudry Expenses', '4', '0', 'Y', 'N', 'N', 'Y', 'Y', 'N', 'VCX', '/', '001', '2024', 'N', NULL, NULL, NULL, NULL, 'Y', 'Y', 'Y', 'Y');

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(
'SSQM2403311400', 'SUED', 'Sudry ExpensesDtl', '4', '0', 'Y', 'N', 'N', 'Y', 'Y', 'N', 'VCX', '/', '001', '2024', 'N', NULL, NULL, NULL, NULL, 'Y', 'Y', 'Y', 'Y');

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(
'SSQM2403311401', 'SUDE', 'Sudry ExpensesDtl', '4', '0', 'Y', 'N', 'N', 'Y', 'Y', 'N', 'VCX', '/', '001', '2024', 'N', NULL, NULL, NULL, NULL, 'Y', 'Y', 'Y', 'Y');


CREATE TABLE `acc_trn_tsundryexpenses` (
  `expense_gid` varchar(64) NOT NULL,
  `expense_date` date DEFAULT NULL,
  `vendor_gid` varchar(64) DEFAULT NULL,
  `vendor_contact_person` varchar(45) DEFAULT NULL,
  `vendor_address` text,
  `expense_remarks` longtext,
  `expense_amount` double(13,2) DEFAULT NULL,
  `expense_status` varchar(45) DEFAULT NULL,
  `user_gid` varchar(64) DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  `updated_by` varchar(64) DEFAULT NULL,
  `updated_date` datetime DEFAULT NULL,
  `expense_reference` text,
  `payment_amount` double DEFAULT '0',
  `payment_date` date DEFAULT NULL,
  `payment_term` int(4) DEFAULT '0',
  `initialexpense_amount` double DEFAULT '0',
  `systemgenerated_amount` double DEFAULT '0',
  `expense_flag` varchar(45) DEFAULT 'Payment Pending',
  `taxfiling_flag` varchar(45) DEFAULT 'N',
  `payment_flag` varchar(45) DEFAULT 'PY Pending',
  `expense_refno` varchar(45) DEFAULT NULL,
  `freightcharges_amount` double DEFAULT '0',
  `additionalcharges_amount` double DEFAULT '0',
  `discount_amount` double DEFAULT '0',
  `total_amount` double DEFAULT '0',
  `approved_by` varchar(16) DEFAULT NULL,
  `approved_date` datetime DEFAULT NULL,
  `reject_reason` text,
  `shortfall_amount` double DEFAULT '0',
  `advance_amount` double DEFAULT '0',
  `expense_from` varchar(32) DEFAULT NULL,
  `mail_Status` varchar(45) DEFAULT 'Mail Not Send',
  `tin_number` varchar(45) DEFAULT NULL,
  `cst_number` varchar(45) DEFAULT NULL,
  `frieghtcharges_amount_L` double DEFAULT '0',
  `additionalcharges_amount_L` double DEFAULT '0',
  `discount_amount_L` double DEFAULT '0',
  `total_amount_L` double DEFAULT '0',
  `currency_code` varchar(45) DEFAULT NULL,
  `exchange_rate` varchar(45) DEFAULT NULL,
  `freightcharges` double DEFAULT '0',
  `buybackorscrap` double DEFAULT '0',
  `debit_note` double(13,2) DEFAULT '0.00',
  `debit_date` datetime DEFAULT NULL,
  `priority` char(1) DEFAULT 'N',
  `priority_remarks` longtext,
  `expense_type` varchar(45) DEFAULT 'Purchase',
  `extraadditional_code` varchar(64) DEFAULT NULL,
  `extradiscount_code` varchar(64) DEFAULT NULL,
  `extraadditional_amount` double DEFAULT '0',
  `extradiscount_amount` double DEFAULT '0',
  `extraadditional_amount_L` double DEFAULT '0',
  `extradiscount_amount_L` double DEFAULT '0',
  `expense_total` double(13,2) DEFAULT '0.00',
  `raised_amount` double(13,2) DEFAULT '0.00',
  `outstanding_amount` double(13,2) DEFAULT NULL,
  `outstanding_amount_l` double(13,2) DEFAULT NULL,
  `agreement_gid` varchar(45) DEFAULT NULL,
  `tax_amount` varchar(45) DEFAULT NULL,
  `tax_name` varchar(45) DEFAULT NULL,
  `tax_percentage` varchar(45) DEFAULT NULL,
  `tax_gid` varchar(45) DEFAULT NULL,
  `branch_gid` varchar(64) DEFAULT NULL,
  `order_total` double DEFAULT '0',
  `received_amount` varchar(64) DEFAULT NULL,
  `received_year` varchar(64) DEFAULT NULL,
  `vendorexpenseref_no` varchar(64) DEFAULT NULL,
  `created_by` varchar(64) DEFAULT NULL,
  `round_off` double DEFAULT '0',
  `costcenter_gid` varchar(64) DEFAULT NULL,
  `ingst` char(2) NOT NULL DEFAULT 'N',
  `taxable_amount` double NOT NULL DEFAULT '0',
  `nontaxable_amount` double NOT NULL DEFAULT '0',
  `packing_charges` double DEFAULT '0',
  `insurance_charges` double DEFAULT '0',
  `invref_no` varchar(45) DEFAULT NULL,
  `fin_year` varchar(45) DEFAULT NULL,
  `entity_gid` varchar(45) DEFAULT NULL,
  `accountsref_no` varchar(45) DEFAULT NULL,
  `expense_document` varchar(256) DEFAULT NULL,
  `purchaseorder_gid` varchar(45) DEFAULT NULL,
  `department_name` varchar(64) DEFAULT NULL,
  `branch_name` varchar(64) DEFAULT NULL,
  `tds_gid` varchar(64) DEFAULT NULL,
  `tds_percentage` varchar(45) DEFAULT NULL,
  `tds` double(13,2) DEFAULT '0.00',
  `taxsegment_gid` varchar(64) DEFAULT NULL,
  `taxsegmenttax_gid` varchar(64) DEFAULT NULL,
  `freight_terms` varchar(64) DEFAULT NULL,
  `purchase_type` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`expense_gid`),
  KEY `ind_vendorgid` (`vendor_gid`),
  KEY `ind_usergid` (`user_gid`),
  KEY `id_entity_gid` (`entity_gid`),
  KEY `id_branch_gid` (`branch_gid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



CREATE TABLE `acc_trn_tsundryexpensesdtl` (
  `expensedtl_gid` varchar(64) NOT NULL,
  `expense_gid` varchar(64) NOT NULL,
  `product_gid` varchar(64) DEFAULT NULL,
  `qty_expense` double DEFAULT '0',
  `product_price` double(13,2) DEFAULT NULL,
  `discount_percentage` double DEFAULT '0',
  `discount_amount` double DEFAULT '0',
  `tax_percentage` double DEFAULT '0',
  `tax_amount` double DEFAULT '0',
  `excise_percentage` double DEFAULT '0',
  `excise_amount` double DEFAULT '0',
  `product_totalprice` double(13,2) DEFAULT '0.00',
  `product_total` double(13,2) DEFAULT NULL,
  `uom_gid` varchar(64) DEFAULT NULL,
  `expense_from` varchar(32) DEFAULT NULL,
  `tax_percentage2` double DEFAULT '0',
  `tax_amount2` double DEFAULT '0',
  `tax_percentage3` double DEFAULT '0',
  `tax_amount3` double DEFAULT '0',
  `tax_name` varchar(45) DEFAULT NULL,
  `tax_name2` varchar(45) DEFAULT NULL,
  `tax_name3` varchar(45) DEFAULT NULL,
  `display_field` text,
  `product_remarks` text,
  `product_price_L` double DEFAULT '0',
  `discount_amount_L` double DEFAULT '0',
  `tax_amount1_L` double DEFAULT '0',
  `tax_amount2_L` double DEFAULT '0',
  `tax_amount3_L` double DEFAULT '0',
  `tax1_gid` varchar(45) DEFAULT NULL,
  `tax2_gid` varchar(45) DEFAULT NULL,
  `tax3_gid` varchar(45) DEFAULT NULL,
  `productgroup_code` varchar(256) DEFAULT NULL,
  `productgroup_name` varchar(2048) DEFAULT NULL,
  `product_code` varchar(256) DEFAULT NULL,
  `product_name` varchar(2048) DEFAULT NULL,
  `productuom_code` varchar(256) DEFAULT NULL,
  `productuom_name` varchar(256) DEFAULT NULL,
  `vendor_refnodate` varchar(128) DEFAULT NULL,
  `producttype_gid` varchar(64) DEFAULT NULL,
  `created_by` varchar(64) DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  `updated_by` varchar(64) DEFAULT NULL,
  `updated_date` datetime DEFAULT NULL,
  `expensedtlref_no` varchar(45) DEFAULT NULL,
  `asset_flag` varchar(45) DEFAULT NULL,
  `itc` varchar(45) DEFAULT NULL,
  `expense_document` varchar(256) DEFAULT NULL,
  `tds_gid` varchar(64) DEFAULT NULL,
  `tds_amount` double(13,2) DEFAULT '0.00',
  `assetpart` varchar(255) DEFAULT NULL,
  `taxsegment_gid` varchar(64) DEFAULT NULL,
  `taxsegmenttax_gid` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`expensedtl_gid`),
  KEY `ind_expensegid` (`expense_gid`),
  KEY `ind_productgid` (`product_gid`),
  KEY `ind_uomgid` (`uom_gid`),
  KEY `id_tax1gid` (`tax1_gid`),
  KEY `id_tax2gid` (`tax2_gid`),
  KEY `id_tax3gid` (`tax3_gid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


alter table acc_trn_tsundryexpenses add column account_gid varchar(64) default null;
alter table acc_trn_tsundryexpenses add column currency_gid varchar(64) default null;
alter table acc_trn_tsundryexpenses add column currency_code varchar(64) default null;
alter table acc_trn_tsundryexpenses add column exchange_rate varchar(64) default null;
alter table acc_trn_tsundryexpenses add column due_date varchar(64) default null;
alter table acc_trn_tsundryexpenses add column address2 varchar(64) default null;

alter table acc_trn_tsundryexpensesdtl add column account_gid varchar(64) default null;
alter table acc_trn_tsundryexpensesdtl add column account_name varchar(64) default null;
alter table acc_trn_tsundryexpensesdtl add column accountgroup_gid varchar(64) default null;
alter table acc_trn_tsundryexpensesdtl add column accountgroup_name varchar(64) default null;

alter table acc_tmp_tsundryexpensesdtl add column account_gid varchar(64) default null;
alter table acc_tmp_tsundryexpensesdtl add column account_name varchar(64) default null;
alter table acc_tmp_tsundryexpensesdtl add column accountgroup_gid varchar(64) default null;
alter table acc_tmp_tsundryexpensesdtl add column tmpexpensedtl_gid varchar(64) default null;


CREATE TABLE `acc_tmp_tsundryexpensesdtl` (
  `expensedtl_gid` varchar(64) NOT NULL,
  `expense_gid` varchar(64) NOT NULL,
  `product_gid` varchar(64) DEFAULT NULL,
  `qty_expense` double DEFAULT '0',
  `product_price` double(13,2) DEFAULT NULL,
  `discount_percentage` double DEFAULT '0',
  `discount_amount` double DEFAULT '0',
  `tax_percentage` double DEFAULT '0',
  `tax_amount` double DEFAULT '0',
  `excise_percentage` double DEFAULT '0',
  `excise_amount` double DEFAULT '0',
  `product_totalprice` double(13,2) DEFAULT '0.00',
  `product_total` double(13,2) DEFAULT NULL,
  `uom_gid` varchar(64) DEFAULT NULL,
  `expense_from` varchar(32) DEFAULT NULL,
  `tax_percentage2` double DEFAULT '0',
  `tax_amount2` double DEFAULT '0',
  `tax_percentage3` double DEFAULT '0',
  `tax_amount3` double DEFAULT '0',
  `tax_name` varchar(45) DEFAULT NULL,
  `tax_name2` varchar(45) DEFAULT NULL,
  `tax_name3` varchar(45) DEFAULT NULL,
  `display_field` text,
  `product_remarks` text,
  `product_price_L` double DEFAULT '0',
  `discount_amount_L` double DEFAULT '0',
  `tax_amount1_L` double DEFAULT '0',
  `tax_amount2_L` double DEFAULT '0',
  `tax_amount3_L` double DEFAULT '0',
  `tax1_gid` varchar(45) DEFAULT NULL,
  `tax2_gid` varchar(45) DEFAULT NULL,
  `tax3_gid` varchar(45) DEFAULT NULL,
  `productgroup_code` varchar(256) DEFAULT NULL,
  `productgroup_name` varchar(2048) DEFAULT NULL,
  `product_code` varchar(256) DEFAULT NULL,
  `product_name` varchar(2048) DEFAULT NULL,
  `productuom_code` varchar(256) DEFAULT NULL,
  `productuom_name` varchar(256) DEFAULT NULL,
  `vendor_refnodate` varchar(128) DEFAULT NULL,
  `producttype_gid` varchar(64) DEFAULT NULL,
  `created_by` varchar(64) DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  `updated_by` varchar(64) DEFAULT NULL,
  `updated_date` datetime DEFAULT NULL,
  `expensedtlref_no` varchar(45) DEFAULT NULL,
  `asset_flag` varchar(45) DEFAULT NULL,
  `itc` varchar(45) DEFAULT NULL,
  `expense_document` varchar(256) DEFAULT NULL,
  `tds_gid` varchar(64) DEFAULT NULL,
  `tds_amount` double(13,2) DEFAULT '0.00',
  `assetpart` varchar(255) DEFAULT NULL,
  `taxsegment_gid` varchar(64) DEFAULT NULL,
  `taxsegmenttax_gid` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`expensedtl_gid`),
  KEY `ind_expensegid` (`expense_gid`),
  KEY `ind_productgid` (`product_gid`),
  KEY `ind_uomgid` (`uom_gid`),
  KEY `id_tax1gid` (`tax1_gid`),
  KEY `id_tax2gid` (`tax2_gid`),
  KEY `id_tax3gid` (`tax3_gid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

05/09/2024-Komathi(Menu fin regulation)

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('FMSMSTJON', 'FMSMST', 'FMSMSTJON', '1421', '', '3', NULL,
 'Journal Regulation', '1', '../images/ico_department_RS.gif', 'MASTER', '', 'Journal Regulation', '', 
 '', '', 'N', 'N', NULL, NULL, NULL, NULL, 'Y', '/finance/AccTrnFinanceRegulation', NULL, NULL, NULL, NULL);
CREATE TABLE acc_trn_topeningbalance (
opening_balance_gid varchar(64) not null,
entity_gid varchar(64) default null,
financial_year varchar(124) default null,
balance_type varchar(12) default null,
account_gid varchar(64) default null,
opening_balance double(24,2) default '0.00',
account_ref_no varchar(45) default null,
created_by varchar(64) default null,
created_date datetime DEFAULT NULL,
updated_by varchar(64) default null,
updated_date datetime DEFAULT NULL,
primary key (opening_balance_gid),
KEY `ind_opening_balance_gid` (opening_balance_gid),
KEY `ind_account_gid` (`account_gid`),
KEY `ind_financial_year` (`financial_year`));

ALTER TABLE acc_trn_topeningbalance ADD COLUMN subgroup_account_gid varchar(64) DEFAULT NULL after account_gid;
ALTER TABLE acc_trn_topeningbalance ADD COLUMN group_account_gid varchar(64) DEFAULT NULL after subgroup_account_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`, `finyear`, `carry_forward`, `product_flag`, `section_flag`, `block_flag`, `unit_flag`) VALUES ('OBLC202407090515', 'OBLC', 'Opening Balance', '4', '0', 'N', 'N', 'N', 'N', 'N', 'N', '2024', 'N', 'Y', 'Y', 'Y', 'Y');
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`, `finyear`, `carry_forward`, `product_flag`, `section_flag`, `block_flag`, `unit_flag`) VALUES ('OBLC202407100422', 'OBLC', 'Opening Balance', '4', '0', 'N', 'N', 'N', 'N', 'N', 'N', '2023', 'N', 'Y', 'Y', 'Y', 'Y');
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`, `finyear`, `carry_forward`, `product_flag`, `section_flag`, `block_flag`, `unit_flag`) VALUES ('OBLC202407100423', 'OBLC', 'Opening Balance', '4', '0', 'N', 'N', 'N', 'N', 'N', 'N', '2022', 'N', 'Y', 'Y', 'Y', '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`) VALUES ('FMSBALSMT','FMSRPT','FMSBALSMT',1458,'ems_finance/finance_transaction.aspx#',3,NULL,'Balancesheet Statement','1','../images/Settings.gif','REPORT',NULL,'Balancesheet Statement',NULL,NULL,NULL,'N','N',NULL,NULL,NULL,NULL,'Y','/finance/AccRptBalancesheetstatement',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 ('FMSINEXSMT','FMSRPT','FMSINEXSMT',1459,'ems_finance/finance_transaction.aspx#',3,NULL,'Income-Expense Statement','1','../images/Settings.gif','REPORT',NULL,'Income-Expense Statement',NULL,NULL,NULL,'N','N',NULL,NULL,NULL,NULL,'Y','/finance/AccRptIncomeandexpensestatement',NULL,NULL,NULL);

UPDATE `adm_mst_tmoduleangular` SET `module_name` = 'Balancesheet', `breadcrumb_name` = 'Balancesheet' WHERE (`module_gid` = 'FMSBALSMT');
UPDATE `adm_mst_tmoduleangular` SET `module_name` = 'Income-Expense', `breadcrumb_name` = 'Income-Expense' WHERE (`module_gid` = 'FMSINEXSMT');

DELETE FROM `adm_mst_tmoduleangular` WHERE (`module_gid` = 'FMSMSTCOA');