1、查询单据附件的关联关系ID及附件ID;此处关联了费用管理系统的相关表单,如果需要增加,按照格式扩展即可
SELECT
T.FNUMBER AS 单据编号,
T.FID AS 单据ID,
B.FNAME_L2 AS 附件名称,
B.FID AS 附件ID ,
A.FID AS 关联关系ID
FROM
(
(SELECT FID,FNUMBER FROM T_BC_OtherExpenseBill )UNION --费用申请单
(SELECT FID,FNUMBER FROM T_BC_DailyLoanBill )UNION --借款单
(SELECT FID,FNUMBER FROM T_BC_BizAccountOutBill )UNION --对公报销单
(SELECT FID,FNUMBER FROM T_BC_EvectionReqBill )UNION --出差申请单
(SELECT FID,FNUMBER FROM T_BC_EvectionLoanBill )UNION --出差借款单
(SELECT FID,FNUMBER FROM T_BC_TravelAccountBill )UNION --差旅费报销单
(SELECT FID,FNUMBER FROM T_IM_MakeInvoice )UNION --开票单
(SELECT FID,FNUMBER FROM T_AP_PayRequestBill ) --付款申请单
) AS T
INNER JOIN T_BAS_BoAttchAsso A ON A.FBOID = T.FID
INNER JOIN T_BAS_ATTACHMENT B ON B.FID = A.FATTACHMENTID
WHERE T.FNUMBER in ('CLFBXD-202101-001071','FKD-202101-001450','BXD202101021110')
查询结果如下:
2、删除关联关系
DELETE FROM T_BAS_BoAttchAsso where FID in( '关联关系表fid')
3、删除附件
DELETE FROM T_BAS_ATTACHMENT where FID in ('附件ID')
涉及删除动作,谨慎操作!