30 lines
2.3 KiB
SQL
30 lines
2.3 KiB
SQL
-- 2026-10-29：采购驳回照片返工及客户下单预估冻结；仅在Lastleg所属库执行。
|
|
SET @ddl = (SELECT IF(EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='ll_overseas_procurement_order' AND column_name='customer_rejection_reason'), 'SELECT 1', 'ALTER TABLE ll_overseas_procurement_order ADD COLUMN customer_rejection_reason VARCHAR(1000) NULL COMMENT ''客户采购结果驳回原因'''));
|
|
PREPARE stmt FROM @ddl;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
SET @ddl = (SELECT IF(EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='ll_overseas_procurement_order' AND column_name='rejected_photo_ids'), 'SELECT 1', 'ALTER TABLE ll_overseas_procurement_order ADD COLUMN rejected_photo_ids TEXT NULL COMMENT ''本轮客户驳回的收货照片主键快照'''));
|
|
PREPARE stmt FROM @ddl;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
CREATE TABLE IF NOT EXISTS ll_order_budget_reservation (
|
|
id BIGINT NOT NULL COMMENT '预算冻结记录主键',
|
|
customer_id VARCHAR(64) NOT NULL COMMENT '客户隔离标识',
|
|
kind VARCHAR(32) NOT NULL COMMENT '客户下单业务类型',
|
|
request_key VARCHAR(128) NOT NULL COMMENT '下单幂等键',
|
|
order_no VARCHAR(64) NULL COMMENT '成功创建的业务单号',
|
|
original_amount DECIMAL(18,2) NOT NULL COMMENT '客户预估原币金额',
|
|
currency VARCHAR(3) NOT NULL COMMENT '预估金额币种',
|
|
amount_cny DECIMAL(18,2) NOT NULL COMMENT '按下单月份汇率折算的人民币冻结金额',
|
|
status VARCHAR(32) NOT NULL COMMENT '资金冻结或释放回执状态',
|
|
error_message VARCHAR(1000) NULL COMMENT '资金失败原因',
|
|
command_event_id VARCHAR(64) NULL COMMENT '当前资金指令事件标识',
|
|
command_version BIGINT NOT NULL DEFAULT 0 COMMENT '资金指令单调版本',
|
|
release_requested TINYINT NOT NULL DEFAULT 0 COMMENT '是否已请求释放预占',
|
|
created_at DATETIME(3) NOT NULL COMMENT '首次预占时间',
|
|
updated_at DATETIME(3) NOT NULL COMMENT '最近资金状态更新时间',
|
|
PRIMARY KEY(id), UNIQUE KEY uk_order_budget_request(customer_id,kind,request_key),
|
|
KEY idx_order_budget_release(status,release_requested,created_at),
|
|
KEY idx_order_budget_order(customer_id,kind,order_no)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='海外客户下单预估资金冻结投影';
|