DECLARE @FROM_DATE DATETIME,@TO_DATE DATETIME,@PINPAI VARCHAR(32),@SX VARCHAR(32),@YR VARCHAR(32),@JJ VARCHAR(32),@XB VARCHAR(32),@MDBH VARCHAR(32),@PROD_CLS VARCHAR(32)
SET @FROM_DATE =:FROM_DATE
SET @TO_DATE =:TO_DATE
SET @MDBH=:MDBH SET @PINPAI=:PINPAI
SET @SX=:SX SET @YR=:YR SET @JJ=:JJ SET @XB=:XB SET @PROD_CLS=:PROD_CLS
--库存表
SELECT UG.UG_NAME , SYS_UNIT.UNIT_NAME , EX.PROD_CLS_NUM ,EX.PROD_NAME ,
SUM(STK.STK_ON_HAND)AS STK , SUM(STK.STK_ON_HAND * ISNULL(COST.UNIT_COST,0)) AS COST
INTO #STK
FROM WAREH_STK STK
LEFT JOIN SYS_UNIT ON STK.WAREH_ID = SYS_UNIT.UNIT_ID
INNER JOIN SYS_UG_DTL UD ON STK.WAREH_ID = UD.MBR_ID
INNER JOIN SYS_UG UG ON UD.UG_ID = UG.UG_ID AND UG_TYPE = 'MG' AND UNIT_TYPE = 'SH'
LEFT JOIN PROD_EX EX ON STK.PROD_ID = EX.PROD_ID
LEFT JOIN WAREH W ON W.WAREH_ID = STK.WAREH_ID
LEFT JOIN STK_COST COST ON COST.PROD_CLS_ID = EX.PROD_CLS_ID AND W.COST_GRP_ID =COST.COST_GRP_ID AND COST.UNIT_ID = 1
LEFT JOIN SYS_CODE_DTL BRAND ON BRAND.CODE = EX.BRAND AND BRAND.CODE_TYPE = 'BRAND'
LEFT JOIN SYS_CODE_DTL SCD2 ON SCD2.CODE=EX.PROD_PROP AND SCD2.CODE_TYPE='PROD_PROP'
LEFT JOIN SYS_CODE_DTL SCD3 ON SCD3.CODE=EX.SEASON AND SCD3.CODE_TYPE='SEASON'
LEFT JOIN SYS_CODE_DTL SCD6 ON SCD6.CODE=EX.GENDER AND SCD6.CODE_TYPE='GENDER'
WHERE SYS_UNIT.UNIT_NUM LIKE @MDBH AND EX.PROD_CLS_NUM LIKE @PROD_CLS AND STK.STK_ON_HAND <> 0
AND (EX.YEAR_VAL LIKE @YR OR EX.YEAR_VAL IS NULL) AND (BRAND.CODE LIKE @PINPAI OR BRAND.CODE IS NULL)
AND (SCD2.CODE LIKE @SX OR SCD2.CODE IS NULL )AND (SCD3.CODE LIKE @JJ OR SCD3.CODE IS NULL )
AND (SCD6.CODE LIKE @XB OR SCD6.CODE IS NULL)
GROUP BY UG.UG_NAME,SYS_UNIT.UNIT_NAME , EX.PROD_CLS_NUM ,EX.PROD_NAME
--===================================================================================================================
--零售表
SELECT UG.UG_NAME , SYS_UNIT.UNIT_NAME , EX.PROD_CLS_NUM ,EX.PROD_NAME ,
SUM(RLB_DTL.SELL_QTY) AS QTY, SUM(RLB_DTL.SELL_VAL) AS VAL,SUM(RLB_DTL.SELL_QTY * ISNULL(COST.UNIT_COST,0)) AS COST
INTO #SELL
FROM RLB
LEFT JOIN RLB_DTL ON RLB.SHOP_ID = RLB_DTL.SHOP_ID AND RLB.RLB_NUM = RLB_DTL.RLB_NUM AND RLB.UNIT_ID =1
LEFT JOIN SYS_UNIT ON RLB.SHOP_ID = SYS_UNIT.UNIT_ID
INNER JOIN SYS_UG_DTL UD ON RLB.SHOP_ID = UD.MBR_ID
INNER JOIN SYS_UG UG ON UD.UG_ID = UG.UG_ID AND UG_TYPE = 'MG' AND UNIT_TYPE = 'SH'
LEFT JOIN PROD_EX EX ON RLB_DTL.PROD_ID = EX.PROD_ID
LEFT JOIN WAREH W ON W.WAREH_ID = RLB.SHOP_ID
LEFT JOIN STK_COST COST ON COST.PROD_CLS_ID = EX.PROD_CLS_ID AND W.COST_GRP_ID =COST.COST_GRP_ID AND COST.UNIT_ID = 1
LEFT JOIN SYS_CODE_DTL BRAND ON BRAND.CODE = EX.BRAND AND BRAND.CODE_TYPE = 'BRAND'
LEFT JOIN SYS_CODE_DTL SCD2 ON SCD2.CODE=EX.PROD_PROP AND SCD2.CODE_TYPE='PROD_PROP'
LEFT JOIN SYS_CODE_DTL SCD3 ON SCD3.CODE=EX.SEASON AND SCD3.CODE_TYPE='SEASON'
LEFT JOIN SYS_CODE_DTL SCD6 ON SCD6.CODE=EX.GENDER AND SCD6.CODE_TYPE='GENDER'
WHERE SYS_UNIT.UNIT_NUM LIKE @MDBH AND EX.PROD_CLS_NUM LIKE @PROD_CLS AND
RLB.DOC_DATE BETWEEN @FROM_DATE AND @TO_DATE
AND (EX.YEAR_VAL LIKE @YR OR EX.YEAR_VAL IS NULL)
AND (BRAND.CODE LIKE @PINPAI OR BRAND.CODE IS NULL)
AND (SCD2.CODE LIKE @SX OR SCD2.CODE IS NULL )AND (SCD3.CODE LIKE @JJ OR SCD3.CODE IS NULL )
AND (SCD6.CODE LIKE @XB OR SCD6.CODE IS NULL)
GROUP BY UG.UG_NAME , SYS_UNIT.UNIT_NAME , EX.PROD_CLS_NUM ,EX.PROD_NAME
--======================================================================================================================
SELECT A.UG_NAME,A.UNIT_NAME,A.PROD_CLS_NUM,A.PROD_NAME,BRAND.DESCRIPTION AS BRAND,SCD1.DESCRIPTION AS D_STYLE,SCD2.DESCRIPTION AS PROD_PROP,
PS.DESCRIPTION AS PS,EX.YEAR_VAL AS YR,SCD3.DESCRIPTION AS SEASON,SCD4.DESCRIPTION AS M_STYLE,SCD5.DESCRIPTION AS SUB_STYLE,SCD6.DESCRIPTION AS GENDER,
EX.SALES_DATE ,U.UNIT_NAME AS U_NAME ,EX.STD_UNIT_PRICE,
ROUND((CASE WHEN (CASE WHEN F.UNIT_PRICE*F.DISC_RATE IS NULL THEN FF.UNIT_PRICE*FF.DISC_RATE ELSE F.UNIT_PRICE*F.DISC_RATE END)
IS NULL THEN EX.STD_UNIT_PRICE ELSE (CASE WHEN F.UNIT_PRICE*F.DISC_RATE IS NULL THEN FF.UNIT_PRICE*FF.DISC_RATE ELSE F.UNIT_PRICE*F.DISC_RATE END) END),2,0)AS RT_PRICE,
ROUND((CASE WHEN (CASE WHEN F.UNIT_PRICE*F.DISC_RATE IS NULL THEN FF.UNIT_PRICE*FF.DISC_RATE ELSE F.UNIT_PRICE*F.DISC_RATE END)
IS NULL THEN EX.STD_UNIT_PRICE ELSE (CASE WHEN F.UNIT_PRICE*F.DISC_RATE IS NULL THEN FF.UNIT_PRICE*FF.DISC_RATE ELSE F.UNIT_PRICE*F.DISC_RATE END) END)/EX.STD_UNIT_PRICE,2,0) AS ZL,
A.QTY,A.VAL,A.S_COST,A.STK,A.COST
FROM
(SELECT ISNULL(STK.UG_NAME ,SELL.UG_NAME )AS UG_NAME,ISNULL(STK.UNIT_NAME ,SELL.UNIT_NAME )AS UNIT_NAME,
ISNULL(STK.PROD_CLS_NUM ,SELL.PROD_CLS_NUM )AS PROD_CLS_NUM, ISNULL(STK.PROD_NAME ,SELL.PROD_NAME )AS PROD_NAME,
ISNULL(SELL.QTY,0)AS QTY, ISNULL(SELL.VAL,0)AS VAL,ISNULL(SELL.COST,0)AS S_COST,ISNULL(STK.STK,0)AS STK, ISNULL(STK.COST,0)AS COST
FROM #STK STK
FULL JOIN #SELL SELL ON SELL.UNIT_NAME=STK.UNIT_NAME AND SELL.PROD_CLS_NUM =STK.PROD_CLS_NUM)A
LEFT JOIN PROD_CLS EX ON EX.PROD_CLS_NUM = A.PROD_CLS_NUM
LEFT JOIN SYS_CODE_DTL BRAND ON BRAND.CODE = EX.BRAND AND BRAND.CODE_TYPE = 'BRAND'
LEFT JOIN SYS_CODE_DTL SCD1 ON SCD1.CODE=EX.DSN_STYLE AND SCD1.CODE_TYPE='DSN_STYLE'
LEFT JOIN PROD_SORT PS ON PS.PROD_SORT_ID=EX.PROD_SORT_ID
LEFT JOIN SYS_CODE_DTL SCD2 ON SCD2.CODE = PS.PROD_PROP AND SCD2.CODE_TYPE='PROD_PROP'
LEFT JOIN SYS_CODE_DTL SCD3 ON SCD3.CODE=EX.SEASON AND SCD3.CODE_TYPE='SEASON'
LEFT JOIN SYS_CODE_DTL SCD4 ON SCD4.CODE=EX.MAIN_STYLE AND SCD4.CODE_TYPE='MAIN_STYLE'
LEFT JOIN SYS_CODE_DTL SCD5 ON SCD5.CODE=EX.SUB_STYLE AND SCD5.CODE_TYPE='SUB_STYLE'
LEFT JOIN SYS_CODE_DTL SCD6 ON SCD6.CODE=EX.GENDER AND SCD6.CODE_TYPE='GENDER'
LEFT JOIN SYS_UNIT U ON U.UNIT_ID = EX.DFLT_VENDER_ID
LEFT JOIN SYS_UNIT C ON C.UNIT_NAME = A.UNIT_NAME
LEFT JOIN PRL_DTL F ON F.UNIT_ID = C.UNIT_ID
AND F.PROD_CLS_ID = EX.PROD_CLS_ID AND F.PRL_TYPE='RT' AND(F.EXP_DATE>GETDATE() OR F.EXP_DATE IS NULL)
LEFT JOIN PRL_DTL FF ON FF.UNIT_ID = 1 AND
FF.PROD_CLS_ID = EX.PROD_CLS_ID AND FF.PRL_TYPE='RT' AND(FF.EXP_DATE>GETDATE() OR FF.EXP_DATE IS NULL)
DROP TABLE #STK,#SELL