SELECT B.PROFIT_CTR,

B.CALMONTH,

B.ZICZS0006,BALANCE,

B.PROFIT_CTR,

SUM(B.ZIFXAV015),

SUM(B.ZIFXAC016),C.GL_ACCOUNT,

SUM(A.ZIFKAV004_E_P1) AS ZIFKAV004_E_P1 -- 期末库存标签价金额_P1

FROM

(SELECT 

ZICZS0000,CALMONTH,

SUM(ZIFKAV004_E_P1) AS ZIFKAV004_E_P1 -- 期末库存标签价金额_P1

FROM (

SELECT CALMONTH,

ZICZS0000 AS ZICZS0000, -- 部门

BQJJE_TO AS ZIFKAV004_E_P1   -- 期末库存标签价金额_P1

FROM "_SYS_BIC"."CB.AL.YW/ZAL_STOCK_BQPRICE"

(PLACEHOLDER."$$ZIP_STDATE$$"=> '${user_date_from}', -- 时段开始日期

 PLACEHOLDER."$$ZIP_ENDDATE$$"=>'${user_date_to}')   -- 时段结束日期

WHERE ZICZA0001 NOT IN ('Z005','Z006','Z008','Z010')      -- 排除虚拟商品等

  AND ZICZA0003 NOT IN ('1110')        -- 排除以旧换新旧饰

  AND ZICZA0004 IN ('D01','D02','D03','D04','D05','D06','D07','D08','D09')

) GROUP BY ZICZS0000,CALMONTH) A  

RIGHT JOIN "_SYS_BIC"."CB.AL.YW/ZAL_SALES_DETAIL"  B

ON CONCAT('P',A.ZICZS0000)=B.PROFIT_CTR

AND A.CALMONTH=B.CALMONTH

LEFT JOIN "_SYS_BIC"."CB.BL.YW/ZBL_ZDEFI002" C

ON B.PROFIT_CTR = C.PROFIT_CTR  

AND A.CALMONTH = B.CALMONTH

WHERE FISCPER NOT LIKE '%000'

${user_profit_center}

${user_comp_code}

GROUP BY B.PROFIT_CTR,

B.CALMONTH,B.ZIFXAV015,

B.ZICZS0006,BALANCE,

B.PROFIT_CTR,

B.ZIFXAC016,

C.GL_ACCOUNT

ORDER BY B.PROFIT_CTR,B.CALMONTH