报销退回解绑购买记录修复.sql
1.45 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
-- 历史数据修复：已退回报销申请仍挂靠的购买记录，解绑并重置为「未审批」
-- 使申请人无需逐条点「更新」即可在物品购买中重新选用
-- 执行前请先备份相关表
-- 1) 预览：将被解绑的购买记录
SELECT
pr.F_Id AS purchaseId,
pr.F_ApplicationId AS applicationId,
pr.F_ApproveStatus AS purchaseStatus,
app.F_ApprovalStatus AS applicationStatus,
app.F_ApplicationUserName AS applicant,
pr.F_ReimbursementCategoryName AS itemName,
pr.F_Amount AS amount
FROM lq_purchase_records pr
INNER JOIN lq_reimbursement_application app ON app.F_Id = pr.F_ApplicationId
WHERE (app.F_ApprovalStatus = '已退回' OR app.F_ApproveStatus = '已退回')
AND pr.F_ApplicationId IS NOT NULL
AND pr.F_ApplicationId <> '';
-- 2) 解绑购买记录并重置状态
UPDATE lq_purchase_records pr
INNER JOIN lq_reimbursement_application app ON app.F_Id = pr.F_ApplicationId
SET
pr.F_ApplicationId = NULL,
pr.F_ApproveStatus = '未审批',
pr.F_ApproveTime = NULL,
pr.F_ApproveUser = NULL
WHERE (app.F_ApprovalStatus = '已退回' OR app.F_ApproveStatus = '已退回')
AND pr.F_ApplicationId IS NOT NULL
AND pr.F_ApplicationId <> '';
-- 3) 清空已退回申请单上的购买关联串（明细已解绑）
UPDATE lq_reimbursement_application
SET F_PurchaseRecordsId = NULL
WHERE (F_ApprovalStatus = '已退回' OR F_ApproveStatus = '已退回')
AND F_PurchaseRecordsId IS NOT NULL
AND F_PurchaseRecordsId <> '';