上周销售额

select 'ALL' as 据点,sum(xmdl018) as 销售数量,sum(未税金额1) as 未税金额 from(select xmdksite,xmdl018,xmdk016,ooao095,(case when xmdk000='6' then jg*-1 else jg end)as 未税金额1 from(select xmdksite,xmdldocno,xmdlseq,xmdk000,xmdk001,to_char(xmdk001,'mm')as xmdk001_1,
to_char(xmdk001,'yyyymm')as xmdk001_2,xmdk009,pmaal004,xmdl018,xmdl008,imaal003,imaal004,xmdk045,xmdk016,ooao005,glaa001,pmaa095,
(CASE --指定结案，根据已交数量*单价计算金额 round((apba014/l_oldsy*l_newsy),4)
when glaa001='RMB' AND xmdl025 LIKE 'P%'THEN
(xmdl022*xmdl024/(xmdl026/100+1)*(case when glaa001='RMB' AND ooao005 is NOT null THEN COALESCE(ooao005,0)ELSE COALESCE(xmdk017,0)END))
when glaa001='HKD' AND xmdl025 LIKE'P%'THEN
(xmdl022*xmdl024/(xmdl026/100+1)*(case when glaa001='HKD' AND ooao005 is NOT null THEN
COALESCE(ooao005,0)ELSE COALESCE(xmdk017,0)END))
when glaa001='USD' AND xmdl025 LIKE 'P%'THEN (xmdl022*xmdl024/(xmdl026/100+1)*(case when glaa001='USD' AND ooao005 is NOT null THEN
COALESCE(ooao005,0)ELSE COALESCE(xmdk017,0)END))
when glaa001='EUR'AND xmdl025 LIKE'P%'THEN (xmdl022*xmdl024/(xmdl026/100+1)*(case when glaa001='EUR'AND ooao005 is NOT null THEN 
COALESCE(ooao005,0)ELSE COALESCE(xmdk017,0)END))
when glaa001='JPY'AND xmdl025 LIKE'P%'THEN
(xmdl022*xmdl024/(xmdl026/100+1)*(case when glaa001='JPY' AND ooao005 is NOT null THEN
COALESCE(ooao005,0)ELSE COALESCE(xmdk017,0)END))
when glaa001='RMB' AND xmdl025 LIKE 'W%'THEN
(xmdl022*xmdl024*(case when glaa001='RMB' AND ooao005 is NOT null THEN COALESCE(ooao005,0)ELSE COALESCE(xmdk017,0)END))
when glaa001='HKD'AND xmdl025 LIKE'W%'THEN 
(xmdl022*xmdl024*(case when glaa001='HKD' AND ooao005 is NOT null THEN COALESCE(ooao005,0)ELSE COALESCE(xmdk017,0)END))
when glaa001='USD' AND xmdl025 LIKE'W%'THEN 
(xmdl022*xmdl024*(case when glaa001='USD' AND ooao005 is NOT null THEN COALESCE(ooao005,0)ELSE COALESCE(xmdk017,0)END))
when glaa001='EUR'AND xmdl025 LIKE'W%'THEN (xmdl022*xmdl024*(case when glaa001='EUR' AND ooao005 is NOT null THEN COALESCE(ooao005,0)ELSE COALESCE(xmdk017,0)END))
when glaa001='JPY'AND xmdl025 LIKE'W%'THEN (xmdl022*xmdl024*(case when glaa001='JPY' AND ooao005 is NOT null THEN COALESCE(ooao005,0)ELSE COALESCE(xmdk017,0)END))
ELSE 0 end )as jg

from xmdk_t left join xmdl_t on xmdlent=xmdkent AND xmdldocno=xmdkdocno left join imaal_t on imaalent=xmdlent and imaal001=xmdl008 and imaal002='zh_CN'
LEFT JOIN pmaal_t ON pmaalent=xmdkent AND pmaal001=xmdk009
left join pmaa_t ON pmaaent=xmdkent AND pmaa001=xmdk009
left join ooao_t on ooaoent=xmdkent and ooao004=to_char(xmdk001,'yyyymm')and ooao002=xmdk016 and ooao001='01' and ooao003='RMB'
LEFT JOIN glaa_t on glaaent=xmdkent and xmdksite=glaald WHERE xmdksite in('SU','TS','SZ','US','HK') and xmdk007 not in('00001','00010','T0159','T0160','T0164','XTEMP')--不包括内部订单
and pmaa093 not in('03')--废料回收即是供应商又是客户
and xmdk001>=TRUNC(NEXT_DAY(SYSDATE-8,1)-6)AND xmdk001<TRUNC(NEXT_DAY(SYSDATE-8,1)+1)--查询上周
AND xmdkstus='S' and xmdk000 IN('1','2','6')and xmdkent=100)
where jg>0)