问题描述：

1 问题重现：

采购价目表中物料没有定义税率，但是生成的采购订单中含有税率A001

查看客户那边是7.1.703，然后采购申请单下推过来的税率就会携带，新增的不会（这个本地已经测试重现）

现在客户那边有历史单据，单据上有税率，后台统一把税率清空，单据比较多

解决方案：

2 思路整理：

2.1 业务上沟通方便后台处理：产生了应收/应付单就比较麻烦，如果是当期的单据的话还是建议先删掉应收/应付单我这边统一后台修复上游单据

2.2 单据整理：税率需要更新的单据有四类：采购订单，采购入库单，单据类型为配送出库类型的销售出库单，和门店收货单

2.3 业务流程梳理：

1.采购申请单-采购订单（含带税率的价目表）-采购入库单-应付单（可能下推）

2.销售订单（含带税率的价目表）-销售出库单-应收单

3.采购订单（含带税率的价目表）-采购入库单-门店收货单-应付单

2.4 代码撰写思路：

通过下游LK表建议关联关系；通过关联将修改放在一套代码内，按照执行顺序来实现；从后往前，从明细到表头改；税率、税额清空，不含税单价/不含税总价 等于 含税单价/价税合计；建立财务表明细与价目表的一一对应关系

2.5 实际解决思路：

找出所有有问题的应付单，并进行标记，前台删除；标记有问题的入库单后修改并修改财务表表头和表体；最后修改上游订单的财务表表头和表体

2.6 亮点：

通过特殊字符标记修改后的单据，便于前台过滤：入库单的 * 号标注在明细，考虑到有两条记录备注不为空，统一采用在其后加上号的形式，过滤可通过明细.备注 包含 过滤出所有更改的入库单；

由于物料内码记录在明细表，但是价目表内码记录在财务明细表，而价目表的物料与单据物料无法直接一一对应，故通过F表作为关联表引用两次，DISTINCT去重（100w+ -> 1w+），创建对应关系临时表，并建立索引

----------------------------------------------

--1.backup

SELECT * INTO T_STK_INSTOCKENTRY_BAK181130 FROM T_STK_INSTOCKENTRY

SELECT * INTO T_STK_INSTOCKFIN_BAK181130 FROM T_STK_INSTOCKFIN

SELECT * INTO T_STK_INSTOCKENTRY_F_BAK181130 FROM T_STK_INSTOCKENTRY_F

SELECT * INTO T_PUR_POORDERFIN_BAK181130 FROM T_PUR_POORDERFIN

SELECT * INTO T_PUR_POORDERENTRY_F_BAK181130 FROM T_PUR_POORDERENTRY_F

--1.1 backup

SELECT * INTO T_STK_INSTOCKENTRY_BAK181130_1 FROM T_STK_INSTOCKENTRY

SELECT * INTO T_STK_INSTOCKFIN_BAK181130_1 FROM T_STK_INSTOCKFIN

SELECT * INTO T_STK_INSTOCKENTRY_F_BAK181130_1 FROM T_STK_INSTOCKENTRY_F

SELECT * INTO T_PUR_POORDERFIN_BAK181130_1 FROM T_PUR_POORDERFIN

SELECT * INTO T_PUR_POORDERENTRY_F_BAK181130_1 FROM T_PUR_POORDERENTRY_F

--2.create index

DROP TABLE T_PUR_POORDER_PRICELIST_LK;

SELECT  *

INTO    T_PUR_POORDER_PRICELIST_LK

FROM    ( SELECT DISTINCT

                    t1.FMATERIALID AS FMATERIALID

                  , FPRICELISTENTRY AS FPRICELISTENTRY

          FROM      ( SELECT    FMATERIALID

                              , FPRICELISTENTRY

                      FROM      T_PUR_POORDERENTRY PPOE

                      INNER JOIN T_PUR_POORDERENTRY_F PPOEF ON PPOEF.FENTRYID = PPOE.FENTRYID ) t1

          INNER JOIN ( SELECT   FMATERIALID

                              , PPLE.FID

                       FROM     T_PUR_PRICELISTENTRY PPLE

                       INNER JOIN T_PUR_POORDERENTRY_F PPOEF ON PPOEF.FPRICELISTENTRY = PPLE.FID ) t2 ON t1.FMATERIALID = t2.FMATERIALID

                                                              AND t1.FPRICELISTENTRY = t2.FID ) a;

CREATE INDEX T_PUR_POORDER_PRICELIST_LK_IDX ON T_PUR_POORDER_PRICELIST_LK(FMATERIALID, FPRICELISTENTRY);

--3.REPAIR

SELECT DISTINCT 

--PPLE.FTAXRATE 价目表税率,

        OOL.FNAME 采购组织

      , PPO.FBILLNO 订单编号

      , PPO.FDATE 订单日期

      , SIS.FBILLNO 入库单编号

      , SIS.FDATE 入库单日期

      , SISE.FNOTE 入库单备注 

--SISF.* 

--UPDATE SISE SET SISE.FNOTE = SISE.FNOTE + '#'

--UPDATE SISF SET SISF.FBILLALLAMOUNT = 0, SISF.FBILLALLAMOUNT_LC = 0, FBILLAMOUNT = SISF.FBILLALLAMOUNT, FBILLAMOUNT_LC = SISF.FBILLALLAMOUNT_LC

--UPDATE SISEF SET FTAXRATE = 0, FPRICE = SISEF.FTAXPRICE, FAMOUNT = SISEF.FALLAMOUNT, FAMOUNT_LC = SISEF.FALLAMOUNT_LC, FTAXAMOUNT = 0, FTAXAMOUNT_LC = 0

--UPDATE PPOF SET PPOF.FBILLALLAMOUNT = 0, PPOF.FBILLALLAMOUNT_LC = 0, FBILLAMOUNT = PPOF.FBILLALLAMOUNT, FBILLAMOUNT_LC = PPOF.FBILLALLAMOUNT_LC

--UPDATE PPOEF SET FTAXRATE = 0, FPRICE = PPOEF.FTAXPRICE, FAMOUNT = PPOEF.FALLAMOUNT, FAMOUNT_LC = PPOEF.FALLAMOUNT_LC, FTAXAMOUNT = 0, FTAXAMOUNT_LC = 0

FROM    T_STK_INSTOCKENTRY_LK SISEK

INNER JOIN T_PUR_POORDERENTRY_LK PPOEK ON PPOEK.FENTRYID = SISEK.FSID

        --INNER JOIN T_PUR_REQENTRY_LK PREK ON PREK.FENTRYID = PPOEK.FSID --三个LK表内联，保证是由申请单->订单->入库单

INNER JOIN T_STK_INSTOCKENTRY_F SISEF ON SISEF.FENTRYID = SISEK.FENTRYID

INNER JOIN T_STK_INSTOCKENTRY SISE ON SISE.FENTRYID = SISEK.FENTRYID

INNER JOIN T_PUR_POORDERENTRY_F PPOEF ON PPOEF.FENTRYID = PPOEK.FENTRYID

INNER JOIN T_PUR_POORDERENTRY PPOE ON PPOE.FENTRYID = PPOEF.FENTRYID 

--INNER JOIN T_PUR_POORDER_PRICELIST_LK PPLEK ON PPLEK.FPRICELISTENTRY = PPOEF.FPRICELISTENTRY AND PPLEK.FMATERIALID = PPOE.FMATERIALID

--INNER JOIN T_PUR_PRICELISTENTRY PPLE ON PPLE.FID = PPLEK.FPRICELISTENTRY AND PPLE.FMATERIALID = PPLEK.FMATERIALID

INNER JOIN T_PUR_POORDER PPO ON PPO.FID = PPOE.FID

INNER JOIN T_STK_INSTOCK SIS ON SIS.FID = SISE.FID

INNER JOIN T_ORG_ORGANIZATIONS_L OOL ON PPO.FPURCHASEORGID = OOL.FORGID

INNER JOIN T_STK_INSTOCKFIN SISF ON SISF.FID = SIS.FID

INNER JOIN T_PUR_POORDERFIN PPOF ON PPOF.FID = PPO.FID

WHERE   1 = 1

        --AND PPOEF.FPRICELISTENTRY > 0 --有价目表

        --AND PPLE.FTAXRATE > 0

        AND PPOEF.FTAXRATE > 0 --订单有税率

        --AND SISEF.FTAXRATE > 0 --入库单有税率

        --AND SIS.FID  IN ( SELECT FSBILLID

        --                     FROM   T_AP_PAYABLE_LK ) --未下推应付单部分

        AND SIS.FDATE > '2018-10-31'

--AND SIS.FBILLNO = 'CGRK104071811299241'

ORDER BY PPO.FDATE ASC;

----------------------------------------------------------------

--4.AP_SELECT

SELECT DISTINCT

        PPLE.FTAXRATE 价目表税率

      , OOL.FNAME 采购组织

      , PPO.FBILLNO 订单编号

      , PPO.FDATE 订单日期

      , SIS.FBILLNO 入库单编号

      , SIS.FDATE 入库单日期

      , AP.FBILLNO 应付单编号

      , AP.FDATE 应付单日期

      , AP.FREMARK 

--,SISF.*

--UPDATE AP SET AP.FREMARK = '*'

FROM    T_STK_INSTOCKENTRY_LK SISEK

INNER JOIN T_PUR_POORDERENTRY_LK PPOEK ON PPOEK.FENTRYID = SISEK.FSID

INNER JOIN T_PUR_REQENTRY_LK PREK ON PREK.FENTRYID = PPOEK.FSID --三个LK表内联，保证是由申请单->订单->入库单

INNER JOIN T_STK_INSTOCKENTRY_F SISEF ON SISEF.FENTRYID = SISEK.FENTRYID

INNER JOIN T_STK_INSTOCKENTRY SISE ON SISE.FENTRYID = SISEK.FENTRYID

INNER JOIN T_PUR_POORDERENTRY_F PPOEF ON PPOEF.FENTRYID = PPOEK.FENTRYID

INNER JOIN T_PUR_POORDERENTRY PPOE ON PPOE.FENTRYID = PPOEF.FENTRYID

INNER JOIN T_PUR_POORDER_PRICELIST_LK PPLEK ON PPLEK.FPRICELISTENTRY = PPOEF.FPRICELISTENTRY

                                               AND PPLEK.FMATERIALID = PPOE.FMATERIALID

INNER JOIN T_PUR_PRICELISTENTRY PPLE ON PPLE.FID = PPLEK.FPRICELISTENTRY

                                        AND PPLE.FMATERIALID = PPLEK.FMATERIALID

INNER JOIN T_PUR_POORDER PPO ON PPO.FID = PPOE.FID

INNER JOIN T_STK_INSTOCK SIS ON SIS.FID = SISE.FID

INNER JOIN T_ORG_ORGANIZATIONS_L OOL ON PPO.FPURCHASEORGID = OOL.FORGID

INNER JOIN T_STK_INSTOCKFIN SISF ON SISF.FID = SIS.FID

INNER JOIN T_PUR_POORDERFIN PPOF ON PPOF.FID = PPO.FID

INNER JOIN T_AP_PAYABLE_LK APK ON APK.FSID = SISE.FENTRYID

INNER JOIN T_AP_PAYABLEENTRY APE ON APE.FENTRYID = APK.FENTRYID

INNER JOIN T_AP_PAYABLE AP ON AP.FID = APE.FID

WHERE   1 = 1

        --AND PPOEF.FPRICELISTENTRY > 0 --有价目表

        --AND PPLE.FTAXRATE > 0

        AND PPOEF.FTAXRATE > 0 --订单有税率

        AND SISEF.FTAXRATE > 0 --入库单有税率

        --AND SIS.FID IN ( SELECT FSBILLID

        --                     FROM   T_AP_PAYABLE_LK ) --未下推应付单部分

        AND APE.FENTRYTAXRATE > 0

        AND SIS.FDATE > '2018-10-31'

--AND AP.FREMARK IS NOT NULL

ORDER BY PPLE.FTAXRATE

      , AP.FDATE

      , PPO.FDATE ASC;

-------------------------------------------------------------------------------------------