<--------22/04/2024------> { path: 'SmrMstTaxsegment', component:SmrMstTaxsegmentComponent }, { path: 'SmrMstMapTaxSegment/:product_gid', component:SmrMstMaptaxsegment2productComponent }, { path: 'CustomerAssignDualList', component:CustomerAssignDualListComponent}, CREATE TABLE `acp_mst_ttaxsegment` ( `taxsegment_gid` VARCHAR(64) NOT NULL AUTO_INCREMENT, `taxsegment_name` varchar(64) DEFAULT NULL, `taxsegment_code` varchar(64) DEFAULT NULL, `taxsegment_description` varchar(1024) DEFAULT NULL, `active_flag` varchar(1) 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 (`taxsegment_gid`) ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1; 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('SSQM240305142001', 'TXSG', 'Tax Segment', '4', '0', '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('SSQM240305142003', 'TXSG', 'Tax Segment', '4', '0', 'N', 'N', 'N', 'N', '', 'N', '', '', '', '2023', 'N', 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) values('SMRMSTTXS', 'SMRMST', 'SMRMSTTXS', '1203', '', '3', NULL, 'Tax Segment', '1', '../images/ico_report_quotation_RS.png', 'SALES', '', 'Tax Segment', '', '', '', 'N', 'N', NULL, NULL, NULL, NULL, 'Y', '/smr/SmrMstTaxsegment', NULL); CREATE TABLE `acp_mst_ttaxsegment2tax` ( `taxsegment2tax_gid` varchar(64) NOT NULL, `taxsegment_gid` varchar(64) DEFAULT NULL, `tax_gid` varchar(64) DEFAULT NULL, `tax_name` varchar(1024) DEFAULT NULL, `tax_percentage` DECIMAL(10, 2) 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 (`taxsegment2tax_gid`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; 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('SSQM240305142002', 'TS2T', 'Tax Segment 2 Tax', '4', '0', 'N', 'N', 'N', 'N', '', 'N', '', '', '', '2023', '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('SSQM240305142004', 'TS2T', 'Tax Segment 2 Tax', '4', '0', 'N', 'N', 'N', 'N', '', 'N', '', '', '', '2024', 'N', NULL, NULL, NULL, NULL); CREATE TABLE `acp_mst_ttaxsegment2product` ( `taxsegment2product_gid` varchar(64) NOT NULL, `taxsegment_gid` varchar(64) DEFAULT NULL, `tax_gid` varchar(64) DEFAULT NULL, `tax_name` varchar(1024) DEFAULT NULL, `tax_percentage` DECIMAL(10, 2) DEFAULT NULL, `product_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, PRIMARY KEY (`taxsegment2product_gid`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; 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('SSQM240308142002', 'TS2P', 'Tax Segment 2 Product', '4', '0', 'N', 'N', 'N', 'N', '', 'N', '', '', '', '2023', '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('SSQM240308142004', 'TS2P', 'Tax Segment 2 Product', '4', '0', 'N', 'N', 'N', 'N', '', 'N', '', '', '', '2024', 'N', NULL, NULL, NULL, NULL); ALTER TABLE acp_mst_ttaxsegment2product ADD COLUMN tax_amount DECIMAL(10, 2); ALTER TABLE crm_mst_tcustomer ADD COLUMN taxsegment_gid varchar(64); ALTER TABLE smr_tmp_treceivequotationdtl ADD COLUMN taxsegment_gid VARCHAR(64), ADD COLUMN taxsegmenttax_gid longtext; ALTER TABLE smr_trn_treceivequotationdtl ADD COLUMN taxsegment_gid VARCHAR(64), ADD COLUMN taxsegmenttax_gid longtext; ALTER TABLE acp_mst_tvendorregister ADD COLUMN taxsegment_gid VARCHAR(64); ALTER TABLE acp_mst_tvendor ADD COLUMN taxsegment_gid VARCHAR(64); CREATE TABLE `acp_mst_ttaxsegment2customer` ( `taxsegment2customer_gid` varchar(64) NOT NULL, `taxsegment_gid` varchar(64) DEFAULT NULL, `taxsegment_name` varchar(1024) DEFAULT NULL, `customer_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, PRIMARY KEY (`taxsegment2customer_gid`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; 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('SSQM2404142001', 'TS2C', 'taxsegment2customer_gid', '4', '0', 'N', 'N', 'N', 'N', '', 'N', '', '', '', '2023', 'N', NULL, NULL, NULL, NULL); CREATE TABLE `acp_mst_ttaxsegment2vendor` ( `taxsegment2vendor_gid` varchar(64) NOT NULL, `taxsegment_gid` varchar(64) DEFAULT NULL, `taxsegment_name` varchar(1024) DEFAULT NULL, `vendor_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, PRIMARY KEY (`taxsegment2vendor_gid`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; 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('SSQM2404142001', 'TS2V', 'taxsegment2vendor_gid', '4', '0', 'N', 'N', 'N', 'N', '', 'N', '', '', '', '2023', 'N', NULL, NULL, NULL, NULL); <--------25/04/2024--------> Drop Procedure if exists `smr_mst_spGetUnmappedProducts`; DELIMITER $$ CREATE PROCEDURE `smr_mst_spGetUnmappedProducts`(in taxsegment_id varchar(200), tax_id varchar(200)) BEGIN SELECT a.product_name, a.product_code, a.cost_price, a.mrp_price, a.product_gid, c.productgroup_gid, c.productgroup_name, c.productgroup_code, a.product_price, d.productuom_name FROM pmr_mst_tproduct a LEFT JOIN pmr_mst_tproductgroup c ON a.productgroup_gid = c.productgroup_gid LEFT JOIN pmr_mst_tproductuom d ON d.productuom_gid = a.productuom_gid LEFT JOIN acp_mst_ttaxsegment2product b ON a.product_gid = b.product_gid WHERE b.product_gid IS NULL OR NOT EXISTS ( SELECT 1 FROM acp_mst_ttaxsegment2product WHERE product_gid = a.product_gid AND taxsegment_gid = taxsegment_id AND tax_gid = tax_id ); END$$ DELIMITER ; <------29/04/2024----> Drop Procedure if exists `smr_mst_spGetUnmappedProducts`; DELIMITER $$ CREATE PROCEDURE `smr_mst_spGetUnmappedProducts`(in taxsegment_id varchar(200), tax_id varchar(200)) BEGIN SELECT a.product_name, a.product_code, a.cost_price, a.mrp_price, a.product_gid, c.productgroup_gid, c.productgroup_name, c.productgroup_code, a.product_price, d.productuom_name FROM pmr_mst_tproduct a LEFT JOIN pmr_mst_tproductgroup c ON a.productgroup_gid = c.productgroup_gid LEFT JOIN pmr_mst_tproductuom d ON d.productuom_gid = a.productuom_gid LEFT JOIN acp_mst_ttaxsegment2product b ON a.product_gid = b.product_gid WHERE b.product_gid IS NULL OR NOT EXISTS ( SELECT 1 FROM acp_mst_ttaxsegment2product WHERE product_gid = a.product_gid AND taxsegment_gid = taxsegment_id AND tax_gid = tax_id ); END$$ DELIMITER ; 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) values('PMRMSTTXS', 'PMRMST', 'PMRMSTTXS', '1016', '', '3', NULL, 'Tax Segment', '1', '../images/ico_report_quotation_RS.png', 'PURCHASE', '', 'Tax Segment', '', '', '', 'N', 'N', NULL, NULL, NULL, NULL, 'Y', '/smr/SmrMstTaxsegment', NULL); ALTER TABLE pmr_tmp_tpurchaseorder ADD COLUMN taxsegment_gid VARCHAR(64), ADD COLUMN taxsegmenttax_gid longtext; ALTER TABLE pmr_trn_tpurchaseorder ADD COLUMN taxsegment_gid VARCHAR(64), ADD COLUMN taxsegmenttax_gid longtext; <-------29/04/2024-------> Drop Procedure if exists `smr_mst_spGetUnmappedProducts`; DELIMITER $$ CREATE PROCEDURE `smr_mst_spGetUnmappedProducts`(in taxsegment_id varchar(200), tax_id varchar(200)) BEGIN SELECT a.product_name, a.product_code, a.cost_price, a.mrp_price, a.product_gid, c.productgroup_gid, c.productgroup_name, c.productgroup_code, a.product_price, d.productuom_name FROM pmr_mst_tproduct a LEFT JOIN pmr_mst_tproductgroup c ON a.productgroup_gid = c.productgroup_gid LEFT JOIN pmr_mst_tproductuom d ON d.productuom_gid = a.productuom_gid LEFT JOIN acp_mst_ttaxsegment2product b ON a.product_gid = b.product_gid GROUP BY a.product_name, a.product_code, a.cost_price, a.mrp_price, a.product_gid, c.productgroup_gid, c.productgroup_name, c.productgroup_code, a.product_price, d.productuom_name HAVING (NOT EXISTS ( SELECT 1 FROM acp_mst_ttaxsegment2product WHERE product_gid = a.product_gid AND taxsegment_gid = taxsegment_id AND tax_gid = tax_id )) ORDER BY a.product_gid; END$$ DELIMITER ; <---------15/05/2024----------> ALTER TABLE rbl_trn_tinvoice ADD COLUMN taxsegment_gid VARCHAR(64); <---------15/05/2024----------> ALTER TABLE pmr_trn_tpurchaseorderdtl ADD COLUMN taxsegment_gid VARCHAR(64), ADD COLUMN taxsegmenttax_gid VARCHAR(64); <----------16-05-2024 ---------> ALTER TABLE rbl_tmp_tinvoicedtl ADD COLUMN taxseg_taxgid1 VARCHAR(64), ADD COLUMN taxseg_taxgid2 VARCHAR(64), ADD COLUMN taxseg_taxgid3 VARCHAR(64), ADD COLUMN taxseg_taxname1 VARCHAR(64), ADD COLUMN taxseg_taxname2 VARCHAR(64), ADD COLUMN taxseg_taxname3 VARCHAR(64), ADD COLUMN taxseg_taxpercent1 VARCHAR(64), ADD COLUMN taxseg_taxpercent2 VARCHAR(64), ADD COLUMN taxseg_taxpercent3 VARCHAR(64), ADD COLUMN taxseg_taxamount1 VARCHAR(64), ADD COLUMN taxseg_taxamount2 VARCHAR(64), ADD COLUMN taxseg_taxamount3 VARCHAR(64); ALTER TABLE rbl_tmp_tinvoicedtl ADD COLUMN taxseg_taxtotal VARCHAR(64); ALTER TABLE rbl_tmp_tinvoicedtl ADD COLUMN taxsegment_gid VARCHAR(64); ALTER TABLE rbl_tmp_tinvoicedtl ADD COLUMN taxsegmenttax_gid VARCHAR(64); ALTER TABLE rbl_trn_tinvoicedtl ADD COLUMN taxseg_taxgid1 VARCHAR(64), ADD COLUMN taxseg_taxgid2 VARCHAR(64), ADD COLUMN taxseg_taxgid3 VARCHAR(64), ADD COLUMN taxseg_taxname1 VARCHAR(64), ADD COLUMN taxseg_taxname2 VARCHAR(64), ADD COLUMN taxseg_taxname3 VARCHAR(64), ADD COLUMN taxseg_taxpercent1 VARCHAR(64), ADD COLUMN taxseg_taxpercent2 VARCHAR(64), ADD COLUMN taxseg_taxpercent3 VARCHAR(64), ADD COLUMN taxseg_taxamount1 VARCHAR(64), ADD COLUMN taxseg_taxamount2 VARCHAR(64), ADD COLUMN taxseg_taxamount3 VARCHAR(64); ALTER TABLE rbl_trn_tinvoicedtl ADD COLUMN taxseg_taxtotal VARCHAR(64); ALTER TABLE rbl_trn_tinvoicedtl ADD COLUMN taxsegmenttax_gid VARCHAR(64); ALTER TABLE rbl_trn_tinvoice ADD COLUMN taxsegment_gid VARCHAR(64); ALTER TABLE rbl_trn_tinvoice ADD COLUMN taxsegmenttax_gid VARCHAR(64); <---------09-05-2024---------> ALTER TABLE acp_mst_ttax ADD COLUMN tax_prefix VARCHAR(64) after tax_gid; <----------10/05/2024---------> ALTER TABLE pmr_tmp_tpurchaseorder ADD COLUMN taxseg_taxgid1 VARCHAR(64), ADD COLUMN taxseg_taxgid2 VARCHAR(64), ADD COLUMN taxseg_taxgid3 VARCHAR(64), ADD COLUMN taxseg_taxname1 VARCHAR(64), ADD COLUMN taxseg_taxname2 VARCHAR(64), ADD COLUMN taxseg_taxname3 VARCHAR(64), ADD COLUMN taxseg_taxpercent1 VARCHAR(64), ADD COLUMN taxseg_taxpercent2 VARCHAR(64), ADD COLUMN taxseg_taxpercent3 VARCHAR(64), ADD COLUMN taxseg_taxamount1 VARCHAR(64), ADD COLUMN taxseg_taxamount2 VARCHAR(64), ADD COLUMN taxseg_taxamount3 VARCHAR(64); ALTER TABLE pmr_tmp_tpurchaseorder ADD COLUMN taxseg_taxtotal VARCHAR(64); ALTER TABLE pmr_trn_tpurchaseorderdtl ADD COLUMN taxseg_taxgid1 VARCHAR(64), ADD COLUMN taxseg_taxgid2 VARCHAR(64), ADD COLUMN taxseg_taxgid3 VARCHAR(64), ADD COLUMN taxseg_taxname1 VARCHAR(64), ADD COLUMN taxseg_taxname2 VARCHAR(64), ADD COLUMN taxseg_taxname3 VARCHAR(64), ADD COLUMN taxseg_taxpercent1 VARCHAR(64), ADD COLUMN taxseg_taxpercent2 VARCHAR(64), ADD COLUMN taxseg_taxpercent3 VARCHAR(64), ADD COLUMN taxseg_taxamount1 VARCHAR(64), ADD COLUMN taxseg_taxamount2 VARCHAR(64), ADD COLUMN taxseg_taxamount3 VARCHAR(64); ALTER TABLE pmr_trn_tpurchaseorderdtl ADD COLUMN taxseg_taxtotal VARCHAR(64); ALTER TABLE smr_tmp_treceivequotationdtl ADD COLUMN taxseg_taxgid1 VARCHAR(64), ADD COLUMN taxseg_taxgid2 VARCHAR(64), ADD COLUMN taxseg_taxgid3 VARCHAR(64), ADD COLUMN taxseg_taxname1 VARCHAR(64), ADD COLUMN taxseg_taxname2 VARCHAR(64), ADD COLUMN taxseg_taxname3 VARCHAR(64), ADD COLUMN taxseg_taxpercent1 VARCHAR(64), ADD COLUMN taxseg_taxpercent2 VARCHAR(64), ADD COLUMN taxseg_taxpercent3 VARCHAR(64), ADD COLUMN taxseg_taxamount1 VARCHAR(64), ADD COLUMN taxseg_taxamount2 VARCHAR(64), ADD COLUMN taxseg_taxamount3 VARCHAR(64); ALTER TABLE smr_tmp_treceivequotationdtl ADD COLUMN taxseg_taxtotal VARCHAR(64); ALTER TABLE smr_trn_treceivequotation ADD COLUMN taxsegment_gid VARCHAR(64); ALTER TABLE smr_trn_treceivequotationdtl ADD COLUMN taxseg_taxgid1 VARCHAR(64), ADD COLUMN taxseg_taxgid2 VARCHAR(64), ADD COLUMN taxseg_taxgid3 VARCHAR(64), ADD COLUMN taxseg_taxname1 VARCHAR(64), ADD COLUMN taxseg_taxname2 VARCHAR(64), ADD COLUMN taxseg_taxname3 VARCHAR(64), ADD COLUMN taxseg_taxpercent1 VARCHAR(64), ADD COLUMN taxseg_taxpercent2 VARCHAR(64), ADD COLUMN taxseg_taxpercent3 VARCHAR(64), ADD COLUMN taxseg_taxamount1 VARCHAR(64), ADD COLUMN taxseg_taxamount2 VARCHAR(64), ADD COLUMN taxseg_taxamount3 VARCHAR(64); ALTER TABLE smr_trn_treceivequotationdtl ADD COLUMN taxseg_taxtotal VARCHAR(64);