wuweihua
214 lines
15 KiB
SQL
214 lines
15 KiB
SQL
-- `pay_bill` 增加对方金额
|
|
ALTER TABLE `bms`.`pay_bill` ADD COLUMN `supplier_amount` decimal(18,2) NOT NULL DEFAULT 0.00 COMMENT '对方金额' AFTER `total_amount`;
|
|
|
|
-- `pay_bill` 增加对账差额
|
|
ALTER TABLE `bms`.`pay_bill` ADD COLUMN `diff_amount` decimal(18,2) NOT NULL DEFAULT 0.00 COMMENT '差额，对方减我方' AFTER `supplier_amount`;
|
|
|
|
-- `pay_bill` 增加已核销金额
|
|
ALTER TABLE `bms`.`pay_bill` ADD COLUMN `paid_amount` decimal(18,2) NOT NULL DEFAULT 0.00 COMMENT '已核销金额' AFTER `diff_amount`;
|
|
|
|
-- `pay_bill` 增加到期日
|
|
ALTER TABLE `bms`.`pay_bill` ADD COLUMN `due_date` date DEFAULT NULL COMMENT '到期日' AFTER `paid_amount`;
|
|
|
|
-- `pay_bill` 增加蓝字红字
|
|
ALTER TABLE `bms`.`pay_bill` ADD COLUMN `bill_kind` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'BLUE' COMMENT 'BLUE蓝字 RED红字' AFTER `status`;
|
|
|
|
-- `pay_bill` 增加原账单
|
|
ALTER TABLE `bms`.`pay_bill` ADD COLUMN `original_bill_id` bigint(20) DEFAULT NULL COMMENT '红字对应的原账单ID' AFTER `bill_kind`;
|
|
|
|
-- `pay_bill` 增加审核人
|
|
ALTER TABLE `bms`.`pay_bill` ADD COLUMN `confirm_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '审核人' AFTER `void_time`;
|
|
|
|
-- `pay_bill` 增加审核时间
|
|
ALTER TABLE `bms`.`pay_bill` ADD COLUMN `confirm_time` datetime DEFAULT NULL COMMENT '审核时间' AFTER `confirm_by`;
|
|
|
|
-- `pay_bill_line` 增加费用明细ID
|
|
ALTER TABLE `bms`.`pay_bill_line` ADD COLUMN `fee_id` bigint(20) DEFAULT NULL COMMENT '应付费用明细ID' AFTER `bill_id`;
|
|
|
|
-- `pay_bill_line` 增加费用来源
|
|
ALTER TABLE `bms`.`pay_bill_line` ADD COLUMN `fee_source` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'CALC计算 LADING提单分摊 MANUAL人工' AFTER `fee_name`;
|
|
|
|
-- `pay_bill_line` 增加费用阶段
|
|
ALTER TABLE `bms`.`pay_bill_line` ADD COLUMN `fee_level` varchar(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '费用阶段 1A 2B 3C 4D' AFTER `fee_source`;
|
|
|
|
-- `pay_bill_line` 增加对方金额
|
|
ALTER TABLE `bms`.`pay_bill_line` ADD COLUMN `supplier_amount` decimal(18,4) DEFAULT NULL COMMENT '对方金额' AFTER `amount`;
|
|
|
|
-- `pay_bill_line` 增加差额
|
|
ALTER TABLE `bms`.`pay_bill_line` ADD COLUMN `diff_amount` decimal(18,4) DEFAULT NULL COMMENT '差额，对方减我方' AFTER `supplier_amount`;
|
|
|
|
-- `pay_bill_line` 增加差异状态
|
|
ALTER TABLE `bms`.`pay_bill_line` ADD COLUMN `diff_status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'NONE' COMMENT 'NONE无差异 OPEN待处理 ACCEPT_OURS接受我方 ACCEPT_THEIRS接受对方' AFTER `diff_amount`;
|
|
|
|
-- `prc_pay_bill_fee` 增加对账单行占用
|
|
ALTER TABLE `bms`.`prc_pay_bill_fee` ADD COLUMN `statement_line_id` bigint(20) DEFAULT NULL COMMENT '已进入的应付账单行ID，空表示未出账' AFTER `is_deleted`;
|
|
|
|
-- `pay_bill_log` 增加业务类型
|
|
ALTER TABLE `bms`.`pay_bill_log` ADD COLUMN `biz_type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'BILL账单 PAYMENT付款 FILE凭证' AFTER `bill_id`;
|
|
|
|
-- `pay_bill_log` 增加业务单号
|
|
ALTER TABLE `bms`.`pay_bill_log` ADD COLUMN `biz_id` bigint(20) DEFAULT NULL COMMENT '业务单号' AFTER `biz_type`;
|
|
|
|
-- `pay_bill_log` 增加账单行
|
|
ALTER TABLE `bms`.`pay_bill_log` ADD COLUMN `line_id` bigint(20) DEFAULT NULL COMMENT '账单行ID' AFTER `biz_id`;
|
|
|
|
-- `pay_bill_log` 增加费用明细
|
|
ALTER TABLE `bms`.`pay_bill_log` ADD COLUMN `fee_id` bigint(20) DEFAULT NULL COMMENT '费用明细ID' AFTER `line_id`;
|
|
|
|
-- `pay_bill_log` 增加改前金额
|
|
ALTER TABLE `bms`.`pay_bill_log` ADD COLUMN `amount_before` decimal(18,4) DEFAULT NULL COMMENT '改前金额' AFTER `operation_desc`;
|
|
|
|
-- `pay_bill_log` 增加改后金额
|
|
ALTER TABLE `bms`.`pay_bill_log` ADD COLUMN `amount_after` decimal(18,4) DEFAULT NULL COMMENT '改后金额' AFTER `amount_before`;
|
|
|
|
-- 新建应付账单差异表
|
|
CREATE TABLE `bms`.`pay_bill_diff` (
|
|
`diff_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '差异ID',
|
|
`bill_id` bigint(20) NOT NULL COMMENT '应付账单ID',
|
|
`line_id` bigint(20) NOT NULL COMMENT '账单行ID',
|
|
`fee_id` bigint(20) DEFAULT NULL COMMENT '费用明细ID',
|
|
`our_amount` decimal(18,4) NOT NULL COMMENT '我方金额',
|
|
`supplier_amount` decimal(18,4) NOT NULL COMMENT '对方金额',
|
|
`diff_amount` decimal(18,4) NOT NULL COMMENT '差额，对方减我方',
|
|
`reason` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '差异原因',
|
|
`handle_type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'OPEN未处理 ACCEPT_OURS接受我方 ACCEPT_THEIRS接受对方',
|
|
`adjust_fee_id` bigint(20) DEFAULT NULL COMMENT '接受对方时新增的调整费用',
|
|
`create_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '创建者',
|
|
`create_time` datetime NOT NULL COMMENT '创建时间',
|
|
PRIMARY KEY (`diff_id`),
|
|
KEY `idx_pay_bill_diff_line` (`line_id`, `handle_type`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='应付账单差异';
|
|
|
|
-- 新建应付凭证表
|
|
CREATE TABLE `bms`.`pay_bill_file` (
|
|
`file_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '凭证ID',
|
|
`bill_id` bigint(20) NOT NULL COMMENT '应付账单ID',
|
|
`file_type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'INVOICE发票 STATEMENT对账单 DEBIT费用通知 OTHER其他',
|
|
`invoice_no` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '发票号',
|
|
`invoice_date` date DEFAULT NULL COMMENT '开票日',
|
|
`amount` decimal(18,2) DEFAULT NULL COMMENT '价税合计',
|
|
`tax_amount` decimal(18,2) DEFAULT NULL COMMENT '税额',
|
|
`file_name` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '文件名',
|
|
`file_url` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '文件地址',
|
|
`deleted` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '0' COMMENT '0正常 1删除',
|
|
`create_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '上传人',
|
|
`create_time` datetime NOT NULL COMMENT '上传时间',
|
|
PRIMARY KEY (`file_id`),
|
|
KEY `idx_pay_bill_file_bill` (`bill_id`, `deleted`),
|
|
KEY `idx_pay_bill_file_invoice` (`invoice_no`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='应付凭证';
|
|
|
|
-- 新建应付账单审核表
|
|
CREATE TABLE `bms`.`pay_bill_approval` (
|
|
`approval_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '审核ID',
|
|
`bill_id` bigint(20) NOT NULL COMMENT '应付账单ID',
|
|
`action` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'SUBMIT提交 APPROVE通过 REJECT驳回',
|
|
`opinion` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '意见',
|
|
`operator_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '操作人',
|
|
`create_time` datetime NOT NULL COMMENT '操作时间',
|
|
PRIMARY KEY (`approval_id`),
|
|
KEY `idx_pay_bill_approval_bill` (`bill_id`, `create_time`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='应付账单审核';
|
|
|
|
-- 新建应付付款表
|
|
CREATE TABLE `bms`.`pay_payment` (
|
|
`payment_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '付款单ID',
|
|
`payment_no` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '付款单号',
|
|
`supplier_id` bigint(20) NOT NULL COMMENT '供应商ID',
|
|
`supplier_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '供应商名称',
|
|
`currency` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '币种',
|
|
`pay_date` date NOT NULL COMMENT '付款日',
|
|
`pay_method` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'BANK银行转账 CASH现金 OTHER其他',
|
|
`bank_account` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '付款账户',
|
|
`amount` decimal(18,2) NOT NULL COMMENT '付款金额',
|
|
`bank_fee` decimal(18,2) NOT NULL DEFAULT 0.00 COMMENT '手续费',
|
|
`status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'DRAFT' COMMENT 'DRAFT草稿 PAID已付款 VOID已作废',
|
|
`remark` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '备注',
|
|
`create_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '创建者',
|
|
`create_time` datetime NOT NULL COMMENT '创建时间',
|
|
`confirm_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '确认人',
|
|
`confirm_time` datetime DEFAULT NULL COMMENT '确认时间',
|
|
PRIMARY KEY (`payment_id`),
|
|
UNIQUE KEY `uk_pay_payment_no` (`payment_no`),
|
|
KEY `idx_pay_payment_supplier` (`supplier_id`, `status`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='应付付款';
|
|
|
|
-- 新建应付付款核销表
|
|
CREATE TABLE `bms`.`pay_payment_alloc` (
|
|
`alloc_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '核销ID',
|
|
`payment_id` bigint(20) NOT NULL COMMENT '付款单ID',
|
|
`bill_id` bigint(20) NOT NULL COMMENT '应付账单ID',
|
|
`alloc_amount` decimal(18,2) NOT NULL COMMENT '本次核销金额',
|
|
PRIMARY KEY (`alloc_id`),
|
|
KEY `idx_pay_payment_alloc_pay` (`payment_id`),
|
|
KEY `idx_pay_payment_alloc_bill` (`bill_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='应付付款核销';
|
|
|
|
-- 账单管理下新增应付付款菜单
|
|
INSERT INTO `bms`.`sys_menu` (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `remark`)
|
|
SELECT '应付付款', parent.menu_id, 9, 'payPayment', 'price/billing/payPayment/index', 1, 0, 'C', '0', '0', 'price:payPayment:list', 'money', 'admin', sysdate(), '应付付款单，核销已确认的应付账单'
|
|
FROM `bms`.`sys_menu` parent
|
|
WHERE parent.menu_name = '账单管理' AND parent.menu_type = 'M'
|
|
ORDER BY parent.menu_id
|
|
LIMIT 1;
|
|
|
|
-- 应付付款查询按钮
|
|
INSERT INTO `bms`.`sys_menu` (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`)
|
|
SELECT '应付付款查询', m.menu_id, 1, '', '', 1, 0, 'F', '0', '0', 'price:payPayment:query', '#', 'admin', sysdate()
|
|
FROM `bms`.`sys_menu` m WHERE m.perms = 'price:payPayment:list' LIMIT 1;
|
|
|
|
-- 应付付款登记按钮
|
|
INSERT INTO `bms`.`sys_menu` (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`)
|
|
SELECT '应付付款登记', m.menu_id, 2, '', '', 1, 0, 'F', '0', '0', 'price:payPayment:add', '#', 'admin', sysdate()
|
|
FROM `bms`.`sys_menu` m WHERE m.perms = 'price:payPayment:list' LIMIT 1;
|
|
|
|
-- 应付付款确认按钮
|
|
INSERT INTO `bms`.`sys_menu` (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`)
|
|
SELECT '应付付款确认', m.menu_id, 3, '', '', 1, 0, 'F', '0', '0', 'price:payPayment:confirm', '#', 'admin', sysdate()
|
|
FROM `bms`.`sys_menu` m WHERE m.perms = 'price:payPayment:list' LIMIT 1;
|
|
|
|
-- 应付付款作废按钮
|
|
INSERT INTO `bms`.`sys_menu` (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`)
|
|
SELECT '应付付款作废', m.menu_id, 4, '', '', 1, 0, 'F', '0', '0', 'price:payPayment:void', '#', 'admin', sysdate()
|
|
FROM `bms`.`sys_menu` m WHERE m.perms = 'price:payPayment:list' LIMIT 1;
|
|
|
|
-- 账单管理下新增应付凭证菜单
|
|
INSERT INTO `bms`.`sys_menu` (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `remark`)
|
|
SELECT '应付凭证', parent.menu_id, 10, 'payVoucher', 'price/billing/payVoucher/index', 1, 0, 'C', '0', '0', 'price:payVoucher:list', 'documentation', 'admin', sysdate(), '应付发票和对账凭证'
|
|
FROM `bms`.`sys_menu` parent
|
|
WHERE parent.menu_name = '账单管理' AND parent.menu_type = 'M'
|
|
ORDER BY parent.menu_id
|
|
LIMIT 1;
|
|
|
|
-- 应付凭证查询按钮
|
|
INSERT INTO `bms`.`sys_menu` (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`)
|
|
SELECT '应付凭证查询', m.menu_id, 1, '', '', 1, 0, 'F', '0', '0', 'price:payVoucher:query', '#', 'admin', sysdate()
|
|
FROM `bms`.`sys_menu` m WHERE m.perms = 'price:payVoucher:list' LIMIT 1;
|
|
|
|
-- 应付凭证上传按钮
|
|
INSERT INTO `bms`.`sys_menu` (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`)
|
|
SELECT '应付凭证上传', m.menu_id, 2, '', '', 1, 0, 'F', '0', '0', 'price:payVoucher:add', '#', 'admin', sysdate()
|
|
FROM `bms`.`sys_menu` m WHERE m.perms = 'price:payVoucher:list' LIMIT 1;
|
|
|
|
-- 应付凭证删除按钮
|
|
INSERT INTO `bms`.`sys_menu` (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`)
|
|
SELECT '应付凭证删除', m.menu_id, 3, '', '', 1, 0, 'F', '0', '0', 'price:payVoucher:remove', '#', 'admin', sysdate()
|
|
FROM `bms`.`sys_menu` m WHERE m.perms = 'price:payVoucher:list' LIMIT 1;
|
|
|
|
-- 应付账单提交审核按钮
|
|
INSERT INTO `bms`.`sys_menu` (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`)
|
|
SELECT '应付账单提交', m.menu_id, 5, '', '', 1, 0, 'F', '0', '0', 'price:payBill:submit', '#', 'admin', sysdate()
|
|
FROM `bms`.`sys_menu` m WHERE m.perms = 'price:payBill:list' LIMIT 1;
|
|
|
|
-- 应付账单审核按钮
|
|
INSERT INTO `bms`.`sys_menu` (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`)
|
|
SELECT '应付账单审核', m.menu_id, 6, '', '', 1, 0, 'F', '0', '0', 'price:payBill:approve', '#', 'admin', sysdate()
|
|
FROM `bms`.`sys_menu` m WHERE m.perms = 'price:payBill:list' LIMIT 1;
|
|
|
|
-- 应付付款和应付凭证授给已有应付账单菜单的角色
|
|
INSERT INTO `bms`.`sys_role_menu` (`role_id`, `menu_id`)
|
|
SELECT rm.role_id, dst.menu_id
|
|
FROM `bms`.`sys_role_menu` rm
|
|
INNER JOIN `bms`.`sys_menu` src ON src.menu_id = rm.menu_id AND src.perms = 'price:payBill:list'
|
|
INNER JOIN `bms`.`sys_menu` dst ON dst.perms IN ('price:payPayment:list', 'price:payPayment:query', 'price:payPayment:add', 'price:payPayment:confirm', 'price:payPayment:void', 'price:payVoucher:list', 'price:payVoucher:query', 'price:payVoucher:add', 'price:payVoucher:remove', 'price:payBill:submit', 'price:payBill:approve');
|