CREATE TABLE `pmr_mst_tcustomercategory` ( 

  `category_gid` varchar(64) NOT NULL, 

  `category_code` varchar(32) DEFAULT NULL, 

  `category_name` 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 (`category_gid`) 

); 

 

INSERT INTO `mootek`.`pmr_mst_tcustomercategory` (`category_gid`, `category_code`, `category_name`) VALUES ('CUS001', 'MP001', 'Manufacturing Process'); 

 

UPDATE `adm_mst_tmoduleangular` SET `sref` = '/ims/ImsMstLocation' WHERE (`module_gid` = 'IMSMSTLOC'); 

alter table smr_trn_treceivequotation add column  `tax_amount4` double(13,2) NOT NULL DEFAULT '0.00'; 
alter table acp_trn_tinvoice modify column shipping_address longtext default null;
 alter table acp_trn_tinvoice modify column termsandconditions longtext default null;


ALTER TABLE acp_trn_tinvoice MODIFY COLUMN termsandconditions longtext;  
 

 
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 ('B0EC2024280001','OUCA','Outlet Campaign','4','0','N','N','N','N','','N','','','','2023','N',NULL,NULL,NULL,NULL);
 

 
alter table pmr_mst_tproduct add column expiry_days varchar(24) default null;


 
alter table pmr_trn_tpurchasereturndtl modify column updated_date date default null;
 

 alter table ims_trn_tmaterialissued Add Column location_gid varchar(64) default null;

 
 
CREATE VIEW `pbl_trn_tinvoiceaddsum_new_v` AS (select `x`.`vendor_gid` AS `vendor_gid`,`x`.`purchaseorder_gid` AS `purchaseorder_gid`,`x`.`created_by` AS `created_by`,`x`.`purchaseorder_status` AS `purchaseorder_status`,`x`.`branch_name` AS `branch_name`,`x`.`vendor_contact_person` AS `vendor_contact_person`,`x`.`costcenter_name` AS `costcenter_name`,format(`x`.`total_amount`,2) AS `total_amount`,format(`x`.`invoice_amount`,2) AS `invoice_amount`,format((`x`.`total_amount` - `x`.`invoice_amount`),2) AS `outstanding_amount`,date_format(`x`.`purchaseorder_date`,'%d-%M-%y') AS `purchaseorder_date`,`x`.`purchaseorder_from` AS `purchaseorder_from`,`x`.`vendor_companyname` AS `vendor_companyname`,`x`.`grn_gid` AS `grn_gid` from (select `a`.`vendor_gid` AS `vendor_gid`,`a`.`purchaseorder_gid` AS `purchaseorder_gid`,`a`.`created_by` AS `created_by`,`a`.`purchaseorder_status` AS `purchaseorder_status`,`k`.`costcenter_name` AS `costcenter_name`,`c`.`branch_name` AS `branch_name`,`a`.`vendor_contact_person` AS `vendor_contact_person`,`a`.`total_amount` AS `total_amount`,(case when (sum(`e`.`invoice_amount`) is not null) then (select sum(`b`.`invoice_amount`) AS `amt` from `acp_trn_tinvoice` `b` where (`b`.`invoice_gid` = `h`.`invoice_gid`) group by `h`.`invoice_gid`) else '0.00' end) AS `invoice_amount`,`a`.`purchaseorder_date` AS `purchaseorder_date`,`a`.`purchaseorder_from` AS `purchaseorder_from`,`b`.`vendor_companyname` AS `vendor_companyname`,concat(`b`.`vendor_code`,'/',`b`.`vendor_companyname`) AS `vendor`,`d`.`grn_gid` AS `grn_gid` from ((((((`pmr_trn_tpurchaseorder` `a` left join `acp_trn_tpo2invoice` `h` on((`h`.`purchaseorder_gid` = `a`.`purchaseorder_gid`))) left join `acp_trn_tinvoice` `e` on((`e`.`invoice_gid` = `h`.`invoice_gid`))) left join `acp_mst_tvendor` `b` on((`a`.`vendor_gid` = `b`.`vendor_gid`))) left join `hrm_mst_tbranch` `c` on((`a`.`branch_gid` = `c`.`branch_gid`))) left join `pmr_trn_tgrn` `d` on((`d`.`purchaseorder_gid` = `a`.`purchaseorder_gid`))) left join `pmr_mst_tcostcenter` `k` on((`k`.`costcenter_gid` = `a`.`costcenter_gid`))) where (((`a`.`invoice_flag` = 'IV Pending') or (`a`.`invoice_flag` = 'Invoice Raised Partial')) and (`d`.`grn_flag` = 'Invoice Pending')) group by `a`.`purchaseorder_gid` union all select distinct `a`.`customer_gid` AS `vendor_gid`,`a`.`serviceorder_gid` AS `purchaseorder_gid`,`a`.`created_by` AS `created_by`,`a`.`so_status` AS `purchaseorder_status`,`d`.`costcenter_name` AS `costcenter_name`,`c`.`branch_name` AS `branch_name`,`b`.`contactperson_name` AS `vendor_contact_person`,`a`.`grand_total` AS `total_amount`,`a`.`invoice_amount` AS `invoice_amount`,`a`.`serviceorder_date` AS `purchaseorder_date`,'Service' AS `purchaseorder_from`,`b`.`vendor_companyname` AS `vendor`,concat(`b`.`vendor_code`,'/',`b`.`vendor_companyname`) AS `vendor_companyname`,'grn_gid' AS `grn_gid` from (((`pbl_trn_tserviceorder` `a` left join `acp_mst_tvendor` `b` on((`a`.`customer_gid` = `b`.`vendor_gid`))) left join `hrm_mst_tbranch` `c` on((`a`.`branch_gid` = `c`.`branch_gid`))) left join `pmr_mst_tcostcenter` `d` on((`d`.`costcenter_gid` = `a`.`costcenter_gid`))) where ((`a`.`invoice_amount` < `a`.`grand_total`) and (`a`.`approval_flag` = 'Y') and (`a`.`so_status` not in ('SO Approval Pending','SO Amended','Cancelled')))) `x` order by `x`.`purchaseorder_date` desc);
 

 
alter table pmr_trn_tpurchasereturndtl modify column updated_date date default null;
 
alter table pmr_trn_tpurchaseorder add column expected_date varchar(64) default null;
 
alter table pmr_trn_tpurchasereturndtl modify column updated_date date default null;

alter table pmr_trn_tpurchaserequisitiondtl add column requested_by varchar(64) default null; 
 
UPDATE `adm_mst_tmoduleangular` SET `sref` = '/payable/PblTrnInvoiceApproval' WHERE (`module_gid` = 'PMRINVRNINA');
 
UPDATE `adm_mst_tmoduleangular` SET `display_order` = '1104' WHERE (`module_gid` = 'IMSTRNISM');
 
UPDATE `adm_mst_tmoduleangular` SET `display_order` = '1105' WHERE (`module_gid` = 'IMSTRNMTR');


INSERT INTO `pmr_mst_tpurchaseconfig` (`id`,`charges`,`flag`,`updated_by`,`updated_date`) VALUES (21,'Contract_po','Y','E1','2024-07-20 17:14:29');

alter table acp_trn_tinvoice modify column shipping_address longtext default null;
 alter table acp_trn_tinvoice modify column termsandconditions longtext default null;
alter table acp_trn_tinvoicedtl add column taxsegmenttax_gid varchar(64) default null;
alter table pmr_mst_tproduct add column tax_gid1 varchar(64) default null;
 
alter table ims_trn_tstock add column financial_year varchar(64) default null;
alter table ims_trn_topeningstockedit add column financial_year varchar(64) default null;


-- ----------------------------------------------------------------------------------------------------------------------------------------------------------S
-- 29-07-2024  PO invoice script  

alter table pmr_tmp_tpurchaseorder add column productgroup_name varchar(264) default null; 
alter table acp_trn_tinvoice add column payment_days varchar(264) default null; 
 alter table acp_trn_tinvoice modify payment_term varchar(64) default null; 
alter table acp_trn_tinvoicedtl add column taxseg_taxname1 varchar(64) default null; 
 alter table acp_trn_tinvoicedtl add column taxseg_taxpercent1 varchar(64) default null; 
 alter table acp_trn_tinvoicedtl add column taxseg_taxamount1 varchar(64) default null; 
   alter table acp_trn_tinvoicedtl add column taxseg_taxname2 varchar(64) default null; 
 alter table acp_trn_tinvoicedtl add column taxseg_taxpercent2 varchar(64) default null; 
 alter table acp_trn_tinvoicedtl add column taxseg_taxamount2 varchar(64) default null; 
    alter table acp_trn_tinvoicedtl add column taxseg_taxname3 varchar(64) default null; 
 alter table acp_trn_tinvoicedtl add column taxseg_taxpercent3 varchar(64) default null; 
 alter table acp_trn_tinvoicedtl add column taxseg_taxamount3 varchar(64) default null;   
alter table acp_trn_tinvoice add column purchase_type varchar(64) default null; 
 alter table pmr_trn_tpurchaseorder add column purchase_type varchar(64) default null; 
alter table smr_trn_tsalesorder add column sales_type varchar(64) default null; 
alter table rbl_trn_tinvoice add column sales_type varchar(64) default null; 
ALTER TABLE acp_tmp_tinvoice MODIFY display_field LONGTEXT; 
ALTER TABLE pmr_mst_tproduct MODIFY product_desc LONGTEXT; 
ALTER TABLE pmr_trn_tpurchaserequisition MODIFY purchaserequisition_remarks LONGTEXT; 
ALTER TABLE pmr_trn_tpurchaseorder MODIFY po_covernote LONGTEXT; 
ALTER TABLE acp_trn_tinvoicedtl MODIFY display_field LONGTEXT; 
 ALTER TABLE acp_trn_tinvoicedtl MODIFY product_remarks LONGTEXT; 
ALTER TABLE acp_trn_tpaymentdtl MODIFY invoice_remarks LONGTEXT; 
alter table pmr_mst_tproduct add column tax_gid1 varchar(64) default null; 
alter table ims_trn_tstock add column financial_year varchar(64) default null; 
alter table ims_trn_topeningstockedit add column financial_year 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`,`k_sref`) VALUES ('SMRRCPSALD','SMRRCP','SMRRCPSALD',3014,'',3,NULL,'Sales Legder','1','','Legder',NULL,'Sales Legder',NULL,NULL,NULL,'N','N',NULL,NULL,NULL,NULL,'Y','//smr/SmrTrnSalesLedger',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 ('PMRRPTPMRLD','PMRRPT','PMRRPTPMRLD',1002,'',3,NULL,'Purchase Legder','1','','Legder',NULL,'Purchase Legder',NULL,NULL,NULL,'N','N',NULL,NULL,NULL,NULL,'Y','/pmr/PmrTrnPurchaseLedger',NULL,NULL,NULL,NULL); 
UPDATE `adm_mst_tmoduleangular` SET `sref` = '/pmr/PmrRptPurchaseorderReport' WHERE (`module_gid` = 'PMRRPTPOR'); 



 


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 ('PMRRPTAGE', 'PMRRPT', 'PMRRPTAGE', '1388', 'ems_payable/pbl_rpt_ageingreport.aspx', '3', NULL, 'Ageing Report', '1', '', 'REPORT', '', 'Ageing Report', '', '', '', 'N', 'Y', NULL, NULL, NULL, NULL, 'Y','/payable/PblRptAgeingReport', 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('PMRRPTOSR', 'PMRRPT', 'PMRRPTOSR', '1008', 'ems_payable/pbl_rpt_outstandingamountreport.aspx', '3', NULL, 'Outstanding Amount Report', '1', '', 'REPORT', '', 'Outstanding Amount Report', '', '', '', 'N', 'Y', NULL, NULL, NULL, NULL, 'Y', '/pmr/PmrRptOutstandingamountreport', NULL, NULL, NULL, NULL

);
 

alter table acp_tmp_tinvoice modify column product_name longtext;

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 ('PMRMSTPT','PMRMST','PMRMSTPT',1013,'ems_pmr/pmr_mst_Purchasetype.aspx',3,NULL,'Purchase Type','1','../images/ico_pettycash_stmt_RS.png','PURCHASE','','Purchase Type','','','','N','N',NULL,NULL,NULL,NULL,'Y','/pmr/PmrMstPurchasetype',NULL,NULL,NULL);

19/09/2024--Praveen S

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 pbl_trn_tagreement add column`roundoff` double DEFAULT '0';

 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 add column `product_name` varchar(256) 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';

alter table acp_trn_tinvoice add column renewal_gid 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`,`k_sref`) 
VALUES ('PMRRPTAGR','PMRRPT','PMRRPTAGR',1008,'ems_pmr/pmr_rpt_agreementrreport.aspx',3,NULL,' Agreement Report','1',
'../images/ico_report_po_RS.png','PURCHASE','','Agreement Report','','','','N','N',NULL,NULL,NULL,NULL,'Y',
'/pmr/PmrRptAgreementreport',NULL,NULL,NULL,NULL);