zkxw/docs/sql/protocol_invoice_request.sql

-- docs/sql/protocol_invoice_request.sql
DROP TABLE IF EXISTS `biz_protocol_invoice_request_item`;
DROP TABLE IF EXISTS `biz_protocol_invoice_request`;

CREATE TABLE `biz_protocol_invoice_request` (
  `id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '主键ID',
  `collection_protocol_id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '采集协议单ID',
  `invoice_amount` decimal(18,2) NOT NULL COMMENT '开票合计金额',
  `invoice_title` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '发票抬头',
  `tax_no` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '税号',
  `company_address` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '单位地址',
  `company_phone` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '电话',
  `bank_name` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '开户银行',
  `bank_account` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '银行账号',
  `apply_remark` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '申请备注',
  `invoice_attachments` text COLLATE utf8mb4_unicode_ci COMMENT '财务发票附件逗号分隔URL',
  `finance_remark` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '财务备注',
  `status` varchar(8) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'dkp' COMMENT 'dkp待开票 ykp已开票',
  `finance_handle_time` datetime DEFAULT NULL COMMENT '财务处理时间',
  `tenant_no` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'zkxw' COMMENT '租户编号',
  `order_num` int(11) DEFAULT NULL COMMENT '排序号',
  `remark` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '备注',
  `create_by` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '创建人id',
  `create_dept_id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '创建人部门ID',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_by` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '更新人id',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `del_flag` tinyint(4) NOT NULL DEFAULT '0' COMMENT '删除标记',
  PRIMARY KEY (`id`),
  KEY `idx_inv_req_protocol` (`collection_protocol_id`,`del_flag`),
  KEY `idx_inv_req_status` (`tenant_no`,`status`,`del_flag`,`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='采集协议单开票申请主表';

CREATE TABLE `biz_protocol_invoice_request_item` (
  `id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '主键ID',
  `invoice_request_id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '开票申请主表ID',
  `payment_id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '缴费主表ID',
  `payment_detail_id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '缴费明细ID',
  `installment_no` int(11) DEFAULT NULL COMMENT '期数快照',
  `line_amount` decimal(18,2) NOT NULL COMMENT '该行开票金额',
  `tenant_no` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'zkxw' COMMENT '租户编号',
  `order_num` int(11) DEFAULT NULL COMMENT '排序号',
  `remark` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '备注',
  `create_by` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '创建人id',
  `create_dept_id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '创建人部门ID',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_by` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '更新人id',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `del_flag` tinyint(4) NOT NULL DEFAULT '0' COMMENT '删除标记',
  PRIMARY KEY (`id`),
  KEY `idx_inv_item_req` (`invoice_request_id`,`del_flag`),
  KEY `idx_inv_item_detail` (`payment_detail_id`,`del_flag`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='采集协议单开票申请明细';