维富友ERP配货发出情况统计表

USE FY_LL082523GO/****** Object:  StoredProcedure [dbo].[p_yhj_ShotTimrationofPercentOrder]    Script Date: 2017-8-27 21:08:36 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[p_yhj_ShotTimrationofPercentOrder]AS    BEGIN        DECLARE @ls_xsphdh NVARCHAR(30)= '';        DECLARE @ls_xtwpdm NVARCHAR(30)= '';        DECLARE @ls_xtwldm NVARCHAR(30)= '';        DECLARE @ls_xsphdhTemp NVARCHAR(30)= '';DECLARE @ls_xsphdhlast NVARCHAR(30)= '';        DECLARE @ls_phsl INT= 0;        DECLARE @ls_phsl_ori INT= 0;        DECLARE @ls_phrows INT= 0;        DECLARE @ls_i_fistdh INT= 0;        DECLARE @ls_xsphdh1 NVARCHAR(30)= '';        DECLARE @ls_xtwpdm1 NVARCHAR(30)= '';        DECLARE @ls_xtwldm1 NVARCHAR(30)= '';        DECLARE @ls_xsphdhTemp1 NVARCHAR(30)= '';        DECLARE @ls_phsl1 INT= 0;DECLARE @ls_phcount INT= 0;        DECLARE @ls_return_i INT= 0;DECLARE @ls_courrentIntOfPh INT= 0;        DECLARE @ls_return_i1 INT= 0;        DECLARE @ls_int_temp INT= 0;        DECLARE @ls_int_temp1 INT= 0;        DECLARE @ls_int_slOfPerPhd INT= 0;        DECLARE @ls_int_FirstRunInCursor INT= 0;         DECLARE cursor_phdj CURSOR        FOR            SELECT  xst14.xsphdh ,                    xst15.xtwpdm ,                    xst15.xtwldm ,                    CAST(SUM(xst15.xsphsl) AS INT) sl            FROM    xst14 ,                    xst15            WHERE   xst14.xsczhm = xst15.xsczhm                    AND xslrrq > '2017-08-16'                    AND xst14.xtwldm IN ( SELECT    xtwldm                                          FROM      xtm14                                          WHERE     xtgsdm = '00'                                                    AND xtkhlx IN ( '4', '5' )                                                    AND xtwlzt = 'H' )                     --AND LTRIM(RTRIM(xst14.xsphdh)) IN ('CZPH1708000394')  --,'CZPH1708000507'            GROUP BY xst14.xsphdh ,                    xst15.xtwpdm ,                    xst15.xtwldm            ORDER BY xst14.xsphdh ,                    xst15.xtwpdm ,                    xst15.xtwldm               SET @ls_i_fistdh = 0;                  --SELECT * FROM dbo.xst14        OPEN cursor_phdj         FETCH cursor_phdj INTO @ls_xsphdh, @ls_xtwpdm, @ls_xtwldm, @ls_phsl; --IF @ls_xsphdhTemp<>@ls_xsphdh--SET @ls_xsphdhTemp=@ls_xsphdh     --IF ( @ls_int_FirstRunInCursor <> 0 )    --    BEGIN    --        SELECT  @ls_int_slOfPerPhd = CAST(SUM(xst15.xsphsl) AS INT)    --        FROM    xst14 ,    --                xst15    --        WHERE   xst14.xsczhm = xst15.xsczhm    --                AND xsshrq > '2016-08-16'    --                AND xst14.xtwldm IN ( SELECT    xtwldm    --                                      FROM      xtm14    --                                      WHERE     xtgsdm = '00'    --                                                AND xtkhlx IN ( '4', '5' )    --                                                AND xtwlzt = 'H' )    --                AND xsphdh = @ls_xsphdh    --    END        WHILE ( @@fetch_status = 0 )            BEGIN                              IF @ls_i_fistdh=0                 BEGIN SET @ls_i_fistdh = 1 set @ls_xsphdhTemp = @ls_xsphdh SET @ls_xsphdhlast=@ls_xsphdh; endSELECT @ls_phcount=ISNULL(COUNT(*),0)  --当前配货单共有多少行，用于当前配货单 最后一行后，输出文字作准备。--@ls_phcount=            FROM    xst14,xst15            WHERE   xst14.xsczhm = xst15.xsczhm                                    AND xst14.xsphdh =@ls_xsphdh --'CZDB1708000394'  --@ls_xsphdh --SELECT * FROM xst14 WHERE  xst14.xsphdh ='CZDB1708000394'  --SELECT * FROM xst15 WHERE xsczhm='001708003921'                                 SELECT  @ls_return_i = ISNULL(SUM(AA.kcczsl),0)  --给某店的 条码 两位维度判断有多少出库。                FROM     cz1436N.FY_ERP_NEW_125.dbo.kct03 AA                WHERE   AA.kcczlx = '140'                        AND AA.kcckdm = '20'                        AND AA.kcczrq > '2017-08-16'                        AND AA.xtwldm IN ( SELECT  xtwldm                                        FROM    xtm14                                        WHERE   xtgsdm = '00'                                                AND xtkhlx IN ( '4', '5' )                                                AND xtwlzt = 'H' )                        AND LTRIM(RTRIM(AA.xtwldm)) = LTRIM(RTRIM(@ls_xtwldm))                        AND LTRIM(RTRIM(AA.xtwpdm)) = LTRIM(RTRIM(@ls_xtwpdm))                   SET @ls_phrows = @ls_phrows + 1; --当前配货单 计算到第几行了                SET @ls_phsl_ori = @ls_phsl_ori + @ls_phsl;                IF @ls_return_i > 0                    BEGIN                        SET @ls_int_temp = @ls_int_temp + @ls_return_i                                            END                FETCH cursor_phdj INTO @ls_xsphdh, @ls_xtwpdm, @ls_xtwldm,@ls_phsl;                                     IF (@ls_xsphdhTemp <> @ls_xsphdh and @ls_i_fistdh<>0 OR @ls_phrows=@ls_phcount)                    BEGIN                        SET @ls_xsphdhTemp = @ls_xsphdh                         PRINT '配货单号:' + CAST(@ls_xsphdhlast AS NVARCHAR(30))                            + '行数:' + CAST(@ls_phrows AS NVARCHAR(4))                            + '需求数量：' + CAST(@ls_phsl_ori AS NVARCHAR(4))                            + '实际配发数量：' + CAST(@ls_int_temp AS NVARCHAR(4))                        SET @ls_phsl_ori = 0;                        SET @ls_int_temp = 0;                        SET @ls_i_fistdh = 0;SET @ls_phrows= 0;SET @ls_courrentIntOfPh=@ls_courrentIntOfPh+1;                    END                               END         CLOSE cursor_phdj         DEALLOCATE cursor_phdj    END--USE FY_LL082523--EXEC p_yhj_ShotTimrationofPercentOrder