AiDOPWarehouse/server/Admin.NET.Web.Entry/UpdateScripts/1.0.339.sql

-- ============================================================
-- 1.0.339.sql
-- S5 搴撳瓨鍙ｅ緞鏀舵暃 C1锛欴omain脳绉熸埛鏄犲皠 / 搴撲綅鐧藉悕鍗?/ 鏍囧噯灞?/ 瀹炰綋淇
-- 鍙噸澶嶆墽琛岋紱涓嶆敼宸叉墽琛岀殑 1.0.300/304/332/335/336銆?-- ============================================================

CREATE TABLE IF NOT EXISTS `ado_source_domain_tenant_map` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `source_code` varchar(64) NOT NULL,
  `domain` varchar(32) NOT NULL,
  `tenant_id` bigint NOT NULL,
  `status` tinyint NOT NULL DEFAULT 1,
  `remark` varchar(500) DEFAULT NULL,
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_source_domain` (`source_code`,`domain`),
  KEY `idx_domain_tenant` (`tenant_id`,`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  COMMENT='澶栭儴鏁版嵁婧怐omain鍒拌处濂楃鎴风殑鏄惧紡鏄犲皠';

INSERT INTO `ado_source_domain_tenant_map`
  (`source_code`,`domain`,`tenant_id`,`status`,`remark`)
VALUES
  ('DOPDEMORQ_SQLSERVER','8010',797403760988229,1,'165 WMS dopdemorq 鈫?AIDOP 绉熸埛')
ON DUPLICATE KEY UPDATE
  `tenant_id`=VALUES(`tenant_id`),`status`=VALUES(`status`),
  `remark`=VALUES(`remark`),`update_time`=NOW();

CREATE TABLE IF NOT EXISTS `ado_inventory_location_scope` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint NOT NULL,
  `source_code` varchar(64) NOT NULL,
  `domain` varchar(32) NOT NULL,
  `location` varchar(32) NOT NULL,
  `scope_type` varchar(32) NOT NULL DEFAULT 'AVAILABLE',
  `status` tinyint NOT NULL DEFAULT 1,
  `remark` varchar(500) DEFAULT NULL,
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_inventory_location_scope`
    (`tenant_id`,`source_code`,`domain`,`location`,`scope_type`),
  KEY `idx_inventory_scope_enabled` (`tenant_id`,`domain`,`scope_type`,`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  COMMENT='搴撳瓨鍙敤閲忕瓑涓氬姟鍙ｅ緞鐨勫簱浣嶇櫧鍚嶅崟';

INSERT INTO `ado_inventory_location_scope`
  (`tenant_id`,`source_code`,`domain`,`location`,`scope_type`,`status`,`remark`)
VALUES
  (797403760988229,'DOPDEMORQ_SQLSERVER','8010','1001','AVAILABLE',1,'鐜伴噰璐彛寰?),
  (797403760988229,'DOPDEMORQ_SQLSERVER','8010','5007','AVAILABLE',1,'鐜伴噰璐彛寰?),
  (797403760988229,'DOPDEMORQ_SQLSERVER','8010','5008','AVAILABLE',1,'鐜伴噰璐彛寰?),
  (797403760988229,'DOPDEMORQ_SQLSERVER','8010','8000','AVAILABLE',1,'鐜伴噰璐彛寰?),
  (797403760988229,'DOPDEMORQ_SQLSERVER','8010','8001','AVAILABLE',1,'鐜伴噰璐彛寰?)
ON DUPLICATE KEY UPDATE
  `status`=VALUES(`status`),`remark`=VALUES(`remark`),`update_time`=NOW();

CREATE TABLE IF NOT EXISTS `mdp_std_inventory` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint NOT NULL,
  `source_system` varchar(64) NOT NULL,
  `domain` varchar(32) NOT NULL,
  `location` varchar(32) NOT NULL,
  `lot_serial` varchar(120) NOT NULL DEFAULT '',
  `item_num` varchar(80) NOT NULL,
  `dimension1` varchar(32) NOT NULL DEFAULT '',
  `dimension2` varchar(32) NOT NULL DEFAULT '',
  `refs` varchar(32) NOT NULL DEFAULT '',
  `site` varchar(32) NOT NULL DEFAULT '',
  `inv_status` varchar(32) DEFAULT NULL,
  `qty_on_hand` decimal(18,5) NOT NULL DEFAULT 0,
  `qty_unrestricted` decimal(18,5) NOT NULL DEFAULT 0,
  `qty_inspection` decimal(18,5) NOT NULL DEFAULT 0,
  `qty_frozen` decimal(18,5) NOT NULL DEFAULT 0,
  `qty_available` decimal(18,5) NOT NULL DEFAULT 0 COMMENT '鍥哄畾绛変簬qty_unrestricted',
  `src_rec_id` varchar(100) NOT NULL,
  `source_update_time` datetime(3) DEFAULT NULL,
  `as_of` datetime(3) NOT NULL,
  `sync_batch_id` varchar(100) NOT NULL,
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_std_inventory_biz`
    (`tenant_id`,`source_system`,`domain`,`location`,`lot_serial`,`item_num`,
     `dimension1`,`dimension2`,`refs`,`site`),
  KEY `idx_std_inventory_item_lot`
    (`tenant_id`,`domain`,`item_num`,`lot_serial`),
  KEY `idx_std_inventory_location`
    (`tenant_id`,`domain`,`location`,`item_num`),
  KEY `idx_std_inventory_asof` (`tenant_id`,`as_of`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  COMMENT='165 LocationDetail鏍囧噯搴撳瓨浣欓锛圓i-DOP鍞竴鍐疯妯″瀷锛?;

CREATE TABLE IF NOT EXISTS `mdp_std_inv_trans` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint NOT NULL,
  `source_system` varchar(64) NOT NULL,
  `domain` varchar(32) NOT NULL,
  `src_rec_id` varchar(100) NOT NULL,
  `trans_type` varchar(32) DEFAULT NULL,
  `item_num` varchar(80) DEFAULT NULL,
  `lot_serial` varchar(120) DEFAULT NULL,
  `location` varchar(32) DEFAULT NULL,
  `dimension1` varchar(32) NOT NULL DEFAULT '',
  `dimension2` varchar(32) NOT NULL DEFAULT '',
  `refs` varchar(32) DEFAULT NULL,
  `site` varchar(32) DEFAULT NULL,
  `qty_change` decimal(18,5) NOT NULL DEFAULT 0,
  `begin_balance` decimal(18,5) NOT NULL DEFAULT 0,
  `end_balance` decimal(18,5) NOT NULL DEFAULT 0,
  `eff_date` datetime(3) DEFAULT NULL,
  `trans_time` datetime(3) DEFAULT NULL,
  `ord_nbr` varchar(120) DEFAULT NULL,
  `work_ord` varchar(80) DEFAULT NULL,
  `shipper_num` varchar(120) DEFAULT NULL,
  `ship_type` varchar(16) DEFAULT NULL,
  `reason` varchar(120) DEFAULT NULL,
  `remark` varchar(500) DEFAULT NULL,
  `create_user` varchar(80) DEFAULT NULL,
  `history_from` datetime(3) NOT NULL COMMENT '鏈妯″瀷鎵胯瑕嗙洊鐨勫巻鍙蹭笅鐣?,
  `as_of` datetime(3) NOT NULL,
  `sync_batch_id` varchar(100) NOT NULL,
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_std_inv_trans_src`
    (`tenant_id`,`source_system`,`src_rec_id`),
  KEY `idx_std_inv_trans_item_lot`
    (`tenant_id`,`domain`,`item_num`,`lot_serial`,`trans_time`),
  KEY `idx_std_inv_trans_location`
    (`tenant_id`,`domain`,`location`,`trans_time`),
  KEY `idx_std_inv_trans_type`
    (`tenant_id`,`domain`,`trans_type`,`trans_time`),
  KEY `idx_std_inv_trans_workord`
    (`tenant_id`,`work_ord`,`trans_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  COMMENT='165 InvTransHist杩?2涓湀鏍囧噯娴佹按';

UPDATE mdp_entity
SET biz_key_expr='Domain,Location,LotSerial,ItemNum,Dimension1,Dimension2,Refs,Site',
    batch_size=5000,
    sync_mode='INCR',
    incr_column='UpdateTime',
    status=1,
    update_time=NOW()
WHERE entity_code='S5_LOCATION_DETAIL_SQLSERVER';

UPDATE mdp_entity
SET biz_key_expr='RecID',
    batch_size=2000,
    sync_mode='INCR',
    incr_column='CreateTime',
    status=1,
    update_time=NOW()
WHERE entity_code='S5_INV_TRANS_HIST_SQLSERVER';

-- 鏈换鍔′笉鍐嶆妸 InvMaster 寤烘垚绗簩濂椾綑棰濇潵婧愩€?UPDATE mdp_entity
SET status=0,
    remark=CONCAT(IFNULL(remark,''),' | INV-CONVERGENCE: balance derives from LocationDetail'),
    update_time=NOW()
WHERE entity_code='S5_INV_MASTER_SQLSERVER';