委外入库单据无法核销的处理方法

1、查询无法核销的单据数据

SELECT  t1.FArapStatus ,--单据核销状态(Y(1):完全核销,P(2):部分核销;N(0):未核销)
        t1.FBillNo ,
        t1.FEntryID ,
        k1.FSInterID ,
        k1.FSEntryID ,
        SUM(k1.FQty) AS FQty ,--核销数量
        SUM(k1.FAmount) AS FAmount  --核销金额
FROM    ICClientVer k1 --委外核销表
        LEFT JOIN ( SELECT  v1.FInterID ,
                            v1.FBillNo ,
                            u1.FEntryID ,
                            ( CASE u1.FCheckStatus
                                WHEN 1 THEN 'Y'
                                WHEN 2 THEN 'P'
                                ELSE 'N'
                              END ) AS FArapStatus--单据核销状态
                    FROM    ICStockBill v1
                            RIGHT JOIN dbo.ICStockBillEntry u1 ON v1.FInterID = u1.FInterID
                  ) t1 ON ( k1.FSInterID = t1.FInterID
                            AND k1.FSEntryID = t1.FEntryID
                          )
WHERE   t1.FInterID IN ( SELECT FInterID
                         FROM   dbo.ICStockBill
                         WHERE  FBillNo LIKE '%000120%' )
GROUP BY t1.FArapStatus ,
        t1.FBillNo ,
        t1.FEntryID ,
        k1.FSInterID ,
        k1.FSEntryID
ORDER BY k1.FSInterID ,
        k1.FSEntryID

2、处理

--1、删除委外核销表（ICClientVer）中对应的记录

--1.1查询：

SELECT  k1.FSInterID ,

        k1.FSEntryID ,

        ( k1.FQty ) AS FQty ,--核销数量

        ( k1.FAmount ) AS FAmount  --核销金额

FROM    ICClientVer k1 --委外核销表

        LEFT JOIN ( SELECT  v1.FInterID ,

                            v1.FBillNo ,

                            u1.FEntryID ,

                            ( CASE u1.FCheckStatus

                                WHEN 1 THEN 'Y'

                                WHEN 2 THEN 'P'

                                ELSE 'N'

                              END ) AS FArapStatus--单据核销状态

                    FROM    ICStockBill v1

                            RIGHT JOIN dbo.ICStockBillEntry u1 ON v1.FInterID = u1.FInterID

                  ) t1 ON ( k1.FSInterID = t1.FInterID

                            AND k1.FSEntryID = t1.FEntryID

                          )

WHERE   t1.FInterID IN ( SELECT FInterID

                         FROM   dbo.ICStockBill

                         WHERE  FBillNo LIKE '%000120%' );

      

--1.2、删除

DELETE  FROM dbo.ICClientVer

WHERE   FSInterID = 256081

        AND FSEntryID = 1;

--2、更新出入库单据体（ICStockBillEntry）中的核销标志（FCheckStatus:0,未核销；1，已核销,2，部分核销）

--2.1查询      

SELECT  FCheckStatus

FROM    dbo.ICStockBillEntry

WHERE   FInterID IN ( SELECT    FInterID

                      FROM      dbo.ICStockBill

                      WHERE     FBillNo LIKE '%JOUT000120%' );

--2.2更新数据

UPDATE  dbo.ICStockBillEntry

SET     FCheckStatus = 0

WHERE   FInterID IN ( SELECT    FInterID

                      FROM      dbo.ICStockBill

                      WHERE     FBillNo LIKE '%JOUT000120%' )

        AND FCheckStatus = 2;