二开付款执行明细单据体

栏目:云星空知识作者:金蝶来源:金蝶云社区发布:2024-09-23浏览:1

二开付款执行明细单据体

@[TOC](目录) ### 背景 2022年11月份版本,标准产品发布了采购订单付款执行视图查看功能:[https://vip.kingdee.com/link/s/lxX32](https://vip.kingdee.com/link/s/lxX32)。不少客户历史版本不愿意升级,也想实现该功能。由于该付款执行明细后台是一个视图表,原则上只要有了视图表就可以在历史版本进行二开,不外乎就是在BOS增加几个字段。 ### 步骤 1. 新增页签 ![1、添加页签.webp](/download/010043680aab17fc49ba9315eb5127f94040.webp) 2. 新增单据体。为了之后升级不冲突,当前单据体的视图名称需要加客户的开发标识 ==(例如:V_SXIM_PURORDERPAYENTRY),表类型选择视图== ![2、新增单据体.webp](/download/0100bf557fb1431c4b7ba1e916fb1d0f0c59.webp) 3. 新增字段 - 分别新增一个基础资料、三个金额、一个下拉列表、一个日期字段 ![image.webp](/download/0100ba11a3026a794f3799f7c0f1fc3d6893.webp) - 第一个字段:关联单据(FORMID),==字段名取FORMID,标识和属性名不能叫FORMID,必须加开发商标识,避免升级后冲突== ![image.webp](/download/01008fff114561b2499a8fb08119c020aada.webp) - 第二个字段:关联单据编号(FRELATBILLNO),==字段名取FRELATBILLNO,标识和属性名不能叫FRELATBILLNO,必须加开发商标识,避免升级后冲突== ![image.webp](/download/010032d33372aa084c798130c51a110bd7d6.webp) - 第三个字段:申请付款金额(FPAYAPPLYAMOUNT),==字段名取FPAYAPPLYAMOUNT,标识和属性名不能叫FPAYAPPLYAMOUNT,必须加开发商标识,避免升级后冲突== - 第四个字段:付款金额(FPAYAMOUNT),==字段名取FPAYAMOUNT,标识和属性名不能叫FPAYAMOUNT,必须加开发商标识,避免升级后冲突== ![image.webp](/download/0100cdcfd36917684e7aa76259ba6a5754c9.webp) - 第五个字段:退款金额(FREFUNDAMOUNT),==字段名取FREFUNDAMOUNT,标识和属性名不能叫FREFUNDAMOUNT,必须加开发商标识,避免升级后冲突== ![image.webp](/download/0100e14632f8fa7a4cdabd549efc056d42e4.webp) - 第六个字段:核销记录单据(FTYPE),==字段名取FTYPE,标识和属性名不能叫FTYPE,必须加开发商标识,避免升级后冲突== ![image.webp](/download/0100787748841d1a4ab7a5446894815b451b.webp) - 第七个字段:业务日期(FPAYDATE),==字段名取FPAYDATE,标识和属性名不能叫FPAYDATE,必须加开发商标识,避免升级后冲突== ![image.webp](/download/01002dde791b20e54a79bdf31e27d5ca9c2d.webp) ### 视图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. 采购订单全流程 ![image.webp](/download/0100ff455270ec304ec598e1fc5b5c09ecb8.webp) 2. 显示的付款执行明细数据 ![image.webp](/download/01004f00a698422a4b3381efe3b3f9f89813.webp) ==说明:如果还需要增加其他字段显示或者其他付款业务流程单据数据展示,则需要按相同方式在BOS上增加字段,并且按自己的业务需要修改视图表V_SXIM_PURORDERPAYENTRY的SQL语句==。新增字段的二开功能可参考:[https://vip.kingdee.com/link/s/lx1V9](https://vip.kingdee.com/link/s/lx1V9) **其他更多文章入口:**[https://vip.kingdee.com/link/s/lbRPP](https://vip.kingdee.com/link/s/lbRPP)

二开付款执行明细单据体

@[TOC](目录)### 背景2022年11月份版本,标准产品发布了采购订单付款执行视图查看功能:[https://vip.kingdee.com/link/s/lxX32](https://...
点击下载文档
确认删除?
回到顶部
客服QQ
  • 客服QQ点击这里给我发消息