@[TOC](目录)
### 背景
2022年11月份版本，标准产品发布了采购订单付款执行视图查看功能：[https://vip.kingdee.com/link/s/lxX32](https://vip.kingdee.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.FDOCUMENTSTAT
