@[TOC](目录)
### 背景
2022年11月份版本，标准产品发布了采购订单付款执行视图查看功能：[https://wenku.my7c.com/link/s/lxX32](https://wenku.my7c.com/link/s/lxX32)。不少客户历史版本不愿意升级，也想实现该功能。由于该付款执行明细后台是一个视图表，原则上只要有了视图表就可以在历史版本进行二开，不外乎就是在BOS增加几个字段。
### 步骤
1. 新增页签
2. 新增单据体。为了之后升级不冲突，当前单据体的视图名称需要加客户的开发标识 ==（例如：V_SXIM_PURORDERPAYENTRY），表类型选择视图==
3. 新增字段
- 分别新增一个基础资料、三个金额、一个下拉列表、一个日期字段
- 第一个字段：关联单据（FORMID），==字段名取FORMID，标识和属性名不能叫FORMID，必须加开发商标识，避免升级后冲突==
- 第二个字段：关联单据编号（FRELATBILLNO），==字段名取FRELATBILLNO，标识和属性名不能叫FRELATBILLNO，必须加开发商标识，避免升级后冲突==
- 第三个字段：申请付款金额（FPAYAPPLYAMOUNT），==字段名取FPAYAPPLYAMOUNT，标识和属性名不能叫FPAYAPPLYAMOUNT，必须加开发商标识，避免升级后冲突==
- 第四个字段：付款金额（FPAYAMOUNT），==字段名取FPAYAMOUNT，标识和属性名不能叫FPAYAMOUNT，必须加开发商标识，避免升级后冲突==
- 第五个字段：退款金额（FREFUNDAMOUNT），==字段名取FREFUNDAMOUNT，标识和属性名不能叫FREFUNDAMOUNT，必须加开发商标识，避免升级后冲突==
- 第六个字段：核销记录单据（FTYPE），==字段名取FTYPE，标识和属性名不能叫FTYPE，必须加开发商标识，避免升级后冲突==
- 第七个字段：业务日期（FPAYDATE），==字段名取FPAYDATE，标识和属性名不能叫FPAYDATE，必须加开发商标识，避免升级后冲突==
### 视图SQL
1. 执行视图创建语句前先判断是否存在同名视图，如果有就删掉，保证视图创建语句可以重复执行
```SQL
IF EXISTS (SELECT 1 FROM (SELECT NAME AS TABLE_NAME, XTYPE AS TABLE_XTYPE FROM sysobjects WHERE XTYPE = 'U' OR XTYPE = 'V') AS KSQL_USERTABLES WHERE TABLE_NAME = 'V_SXIM_PURORDERPAYENTRY')
BEGIN
DROP VIEW V_FIN_PURORDERPAYENTRY
END
```
2. 视图创建语句
```SQL
CREATE VIEW V_SXIM_PURORDERPAYENTRY AS
select ROW_NUMBER() OVER (order by FID) AS fentryid, FORMID, FBILLID, FRELATBILLNO, FPURBILLNO, FID, FPAYAMOUNT, FPAYAPPLYAMOUNT, FISAYAPPLY, FTYPE, FREFUNDAMOUNT , FUNPAIDAMOUNT ,FPAYDATE from (
--新增付款单关联采购订单 已经审核
select 'AP_PAYBILL' AS FORMID,A.FID AS FBILLID, A.FBILLNO AS FRELATBILLNO,B.FPAYITEM AS FPURBILLNO,B.FPURCHASEORDERID AS FID, sum( B.FPAYTOTALAMOUNTFOR) AS FPAYAMOUNT,
0 AS FPAYAPPLYAMOUNT ,'0' AS FISAYAPPLY,'0' as FTYPE,0 as FREFUNDAMOUNT ,0 as FUNPAIDAMOUNT,A.FDATE AS FPAYDATE from T_AP_PAYBILL A inner join T_AP_PAYBILLENTRY B ON A.FID=B.FID
WHERE B.FPURPOSEID=20018 AND B.FPAYITEMTYPE='2' AND B.FPURCHASEORDERID>0 AND A.FDOCUMENTSTATUS='C' AND ISNULL( B.FPURCHASEORDERNO,' ')<>' '
group by A.FID,A.FBILLNO,B.FPAYITEM ,B.FPURCHASEORDERID ,A.FDATE
union all
--采购订单--付款申请单--付款单 已经审核
select 'AP_PAYBILL' AS FORMID,A.FID AS FBILLID,A.FBILLNO AS FRELATBILLNO,B.FPREPAYORDERNO AS FPURBILLNO,C.FID AS FID, sum( B.FREALPAYAMOUNT) AS FPAYAMOUNT ,
0 AS FPAYAPPLYAMOUNT ,'1' AS FISAYAPPLY ,'0' as FTYPE ,0 as FREFUNDAMOUNT ,0 as FUNPAIDAMOUNT,A.FDATE AS FPAYDATE from T_AP_PAYBILL A inner join T_AP_PAYBILLSRCENTRY B ON A.FID=B.FID
INNER JOIN T_PUR_POORDER C ON B.FPREPAYORDERNO=C.FBILLNO WHERE A.FDOCUMENTSTATUS='C' AND ISNULL( b.FPREPAYORDERNO,' ')<>' ' AND B.FPURORDERNO=b.FPREPAYORDERNO
group by A.FID,A.FBILLNO,B.FPREPAYORDERNO,C.FID ,A.FDATE
union all
--手工新增采购订单预付关联采购订单
select 'AP_PAYBILL' AS FORMID,A.FID AS FBILLID,A.FBILLNO AS FRELATBILLNO,D.FBILLNO AS FPURBILLNO ,C.FASSBILLID AS FID ,sum( C.FASSAMOUNTFOR) AS FPAYAMOUNT ,
0 AS FPAYAPPLYAMOUNT ,'0' AS FISAYAPPLY ,'0' as FTYPE,0 as FREFUNDAMOUNT ,0 as FUNPAIDAMOUNT ,A.FDATE AS FPAYDATE from T_AP_PAYBILL A inner join T_AP_PAYBILLENTRY B ON A.FID=B.FID
inner join T_AP_ASSPURCHASEORDER C ON B.FENTRYID=C.FENTRYID AND C.FASSBILLID>0
inner join T_PUR_POORDER D ON C.FASSBILLID=D.FID WHERE A.FDOCUMENTSTATUS='C'
group by A.FID,A.FBILLNO,D.FBILLNO ,C.FASSBILLID ,A.FDATE
union all
--应付付款金额 (1.应付单-付款申请单-付款单 2.应付单-付款单)
select 'AP_PAYBILL' AS FORMID,A.FID AS FBILLID,A.FBILLNO AS FRELATBILLNO,B.FPURORDERNO AS FPURBILLNO,C.FID AS FID, sum( B.FREALPAYAMOUNT) AS FPAYAMOUNT ,
0 AS FPAYAPPLYAMOUNT, CASE B.FSOURCETYPE WHEN 'CN_PAYAPPLY' then '1' ELSE '0' END AS FISAYAPPLY ,'0' as FTYPE ,0 as FREFUNDAMOUNT ,0 as FUNPAIDAMOUNT ,A.FDATE AS FPAYDATE from T_AP_PAYBILL A
inner join T_AP_PAYBILLSRCENTRY B ON A.FID=B.FID AND b.FPREPAYORDERNO=' ' AND ISNULL( B.FPURORDERNO,' ')<>' '
inner join T_PUR_POORDER C ON B.FPURORDERNO=C.FBILLNO WHERE A.FDOCUMENTSTATUS='C'
group by A.FID,A.FBILLNO,B.FPURORDERNO ,B.FSOURCETYPE,C.FID ,A.FDATE
union all
--累计申请金额
SELECT 'CN_PAYAPPLY' AS FORMID,A.FID AS FBILLID,A.FBILLNO AS FRELATBILLNO,B.FPURCHASEORDERNO AS FPURBILLNO,C.FID, 0 AS FPAYAMOUNT, sum(B.FAPPLYAMOUNTFOR) AS FPAYAPPLYAMOUNT,
'0' AS FISAYAPPLY ,'0' as FTYPE ,0 AS FREFUNDAMOUNT, sum(B.FUNPAIDAMOUNT) AS FUNPAIDAMOUNT ,A.FDATE AS FPAYDATE FROM T_CN_PAYAPPLY A INNER JOIN T_CN_PAYAPPLYENTRY B ON A.FID=B.FID
INNER JOIN T_PUR_POORDER C ON B.FPURCHASEORDERNO=C.FBILLNO WHERE A.FDOCUMENTSTATUS='C' AND ISNULL( B.FPURCHASEORDERNO,' ') <>' '
group by A.FID,A.FBILLNO,B.FPURCHASEORDERNO ,C.FID,A.FDATE
union all
--应付付款金额（非关联核销） '核销记录对方是付款单 取核销记录，应付单的本次核销金额求和。条件:核销为付款核销【不含暂估，不含发票】，核销方式非关联核销，本订单相关，对方为付款单
select 'AP_MatchRecord' AS FORMID, a.fid AS FBILLID,A.FBILLNO AS FRELATBILLNO, b.FPURCHASEORDERNO AS FPURBILLNO ,C.FID,sum(B.FCURWRITTENOFFAMOUNTFOR) AS FPAYAMOUNT ,
0 AS FPAYAPPLYAMOUNT ,'0' AS FISAYAPPLY ,'1' as FTYPE ,0 as FREFUNDAMOUNT ,0 as FUNPAIDAMOUNT ,NULL AS FPAYDATE from T_AP_PAYMatchLog A
INNER JOIN T_AP_PAYMatchLogENTRY B ON A.FID=B.FID
INNER JOIN T_PUR_POORDER C ON B.FPURCHASEORDERNO=C.FBILLNO
WHERE A.FISJOINMATCH='0' AND ISNULL( B.FPURCHASEORDERNO,' ') <>' ' and A.FMATCHMETHODID<>40 AND B.FSOURCEFROMID='AP_Payable'
group by A.FID,A.FBILLNO,B.FPURCHASEORDERNO ,C.FID,FDATE
union all
--付款单非关联核销金额
select 'AP_MatchRecord' AS FORMID, a.fid AS FBILLID,A.FBILLNO AS FRELATBILLNO, b.FPURCHASEORDERNO AS FPURBILLNO ,C.FID ,sum(B.FCURWRITTENOFFAMOUNTFOR) AS FPAYAMOUNT ,
0 AS FPAYAPPLYAMOUNT ,'0' AS FISAYAPPLY ,'2' as FTYPE,0 as FREFUNDAMOUNT ,0 as FUNPAIDAMOUNT,NULL AS FPAYDATE from T_AP_PAYMatchLog A
INNER JOIN T_AP_PAYMatchLogENTRY B ON A.FID=B.FID
INNER JOIN T_PUR_POORDER C ON B.FPURCHASEORDERNO=C.FBILLNO
WHERE A.FISJOINMATCH='0' AND ISNULL( B.FPURCHASEORDERNO,' ') <>' ' and A.FMATCHMETHODID<>40 AND B.FSOURCEFROMID='AP_PAYBILL'
group by A.FID,A.FBILLNO,B.FPURCHASEORDERNO ,C.FID ,FDATE
union all
--预付退款
select 'AP_REFUNDBILL' AS FORMID, A.FPAYADVANCEBILLID AS FBILLID,A.FPREPAYBILLNO AS FRELATBILLNO, B.FBILLNO AS FPURBILLNO ,B.FID, 0 AS FPAYAMOUNT ,
0 AS FPAYAPPLYAMOUNT ,'0' AS FISAYAPPLY ,'0' as FTYPE ,sum(A.FAMOUNT+A.FPPayJoinAmount) as FREFUNDAMOUNT,0 as FUNPAIDAMOUNT ,C.FDATE AS FPAYDATE from T_PUR_POORDERACTUALPAY a
INNER JOIN T_PUR_POORDER B ON B.FID=A.FPOORDERID INNER JOIN T_AP_REFUNDBILL C ON A.FPAYADVANCEBILLID=C.FID where A.FAMOUNT+A.FPPayJoinAmount<0 AND ISNULL(A.FPREPAYBILLNO,' ')<>' '
group by A.FPAYADVANCEBILLID,A.FPREPAYBILLNO,B.FBILLNO ,B.FID,C.FDATE
union all
--应付流程退款
select 'AP_REFUNDBILL' AS FORMID, b.FTARGETBILLID AS FBILLID,b.FTARGETBILLNO AS FRELATBILLNO, b.FPURCHASEORDERNO AS FPURBILLNO ,C.FID,0 AS FPAYAMOUNT ,
0 AS FPAYAPPLYAMOUNT ,'0' AS FISAYAPPLY ,'0' as FTYPE ,sum(B.FCURWRITTENOFFAMOUNTFOR) as FREFUNDAMOUNT ,0 as FUNPAIDAMOUNT,D.FDATE AS FPAYDATE from T_AP_PAYMatchLog A
INNER JOIN T_AP_PAYMatchLogENTRY B ON A.FID=B.FID
INNER JOIN T_PUR_POORDER C ON B.FPURCHASEORDERNO=C.FBILLNO
INNER JOIN T_AP_REFUNDBILL D ON b.FTARGETBILLID=D.FID
WHERE A.FISJOINMATCH='1' AND ISNULL( B.FPURCHASEORDERNO,' ') <>' ' and A.FMATCHMETHODID=10 AND B.FSOURCEFROMID='AP_Payable' and FTARGETFROMID='AP_REFUNDBILL'
and b.FPLANAMOUNTFOR>0
group by b.FTARGETBILLID,b.FTARGETBILLNO,B.FPURCHASEORDERNO ,C.FID,D.FDATE
) K;
```
### 效果
1. 采购订单全流程
2. 显示的付款执行明细数据
==说明：如果还需要增加其他字段显示或者其他付款业务流程单据数据展示，则需要按相同方式在BOS上增加字段，并且按自己的业务需要修改视图表V_SXIM_PURORDERPAYENTRY的SQL语句==。新增字段的二开功能可参考：[https://wenku.my7c.com/link/s/lx1V9](https://wenku.my7c.com/link/s/lx1V9)
**其他更多文章入口：**[https://wenku.my7c.com/link/s/lbRPP](https://wenku.my7c.com/link/s/lbRPP)
