上周接单金额按销售组车加工

select xmdcdocno as 订单单号,xmdcseq as 项次,xmdasite as 接单据点,xmdadocdt as 订单日期,xmda004 as 客户编号,pmaal004 as 简称,
xmdc001 as 料号,imaal003 as 品名,imaal004 as 规格,xmdc006 as 单位,xmdc007 as 订单数量,xmdc010 as 计价单位,xmdc011 as 订单计价数量,xmdc015 as 单价,chjj as 出货计价数量,xmdc016 as 税种,(case when xmdc017>0 then xmdc017/100 else xmdc017 end)as 税率,xmda015 as 币种,xmda003 as 部门,ooefl003 as 部门名称,(case when pmaa294 is null then ooefl004 else pmaa294 end)as 所属据点,
(case when xmdc045 in ('1','5') then'1:一般' when xmdc045='2'then '2:正常结案' when xmdc045='3' then '3:长结' when xmdc045='4' then '4:短结' when xmdc045='5'then '5:留置'else'有错'end)as 行状态,
xmda016 as 订单汇率,ooao005 as 人名币汇率,glaa001 as 接单据点本位币,年月,jg as 人民币未税金额 from 
(select xmdcdocno,xmdcseq,xmdadocdt,xmda004,xmdasite,xmdc001,xmdadocno,to_char(xmdadocdt,'YYYYMM')as 年月,xmda003,xmdc006,xmdc007,xmdc010,xmdc011,xmdc015,xmc016,xmdc017,xmda015,xmda016,ooao005,glaa001,xmdc045,(case when xmdc045 in('1','5')AND glaa001='RMB' then(COALESCE(xmdc046,0)*(case when glaa001='RMB'
AND ooao005  is NOT null THEN COALESCE(ooao005,0)ELSE COALESCE(xmda016,0)END))
WHEN xmdc045 in('1','5')AND glaa001='HKD'then(COALESCE(xmdc046,0)*(case when glaa001='HKD'
AND ooao005 is NOT null THEN COALESCE(ooao005,0)ELSE COALESCE(xmda016,0)END))
WHEN xmdc045 in ('1','5')AND glaa001='USD' then(COALESCE(xmdc046,0)*(case when glaa001='USD' AND ooao005 is NOT null
THEN COALESCE(ooao005,0)ELSE COALESCE(xmda016,0)END)) 
WHEN xmdc045 in('1','5')AND glaa001='EUR'then (COALESCE(xmdc046,0)*(case when glaa001='EUR' AND ooao005 is NOT null THEN COALESCE(ooao005,0)ELSE COALESCE(xmdc016,0)END))
--指定结案，根据已交数量*单价计算金额 round((apba014/l_oldsy*l_newsy),4)
WHEN xmdc045 not in('1','5')AND glaa001='RMB'AND xmdc016 LIKE'P%'THEN 
(select SUM(xmdl022*xmdl024/(xmdl026/100+1)*(case when glaa001='USD' AND ooao005 is NOT null THEN COALESCE(ooao005,0)ELSE COALESCE(xmda016,0)END))from xmdl_t left join xmdk_t on xmdlent=xmdkent and xmdldocno=xmdkdocno
where xmdlent=xmdcent and xmdl003=xmdcdocno and xmdl004=xmdcseq and xmdkstus='S' and xmdk000='1')
when xmdc045 not in('1','5') AND glaa001<>'EUR' AND xmdc016 LIKE'P%'THEN 
(select SUM(xmdl022*xmdl024/(xmdl026/100+1)*(case when glaa001='EUR' AND ooao005 is NOT null THEN COALESCE(ooao005,0)ELSE COALESCE(xmda016,0)END))from xmdl_t left join xmdk_t on xmdlent=xmdkent and xmdldocno=xmdkdocno
where xmdlent=xmdcent and xmdl003=xmdcdocno and xmdl004=xmdcseq and xmdkstus='S' and xmdk000='1')
 when xmdc045 not in('1','5') AND glaa001<>'JPY' AND xmdc016 LIKE'P%'THEN (select SUM(xmdl022*xmdl024/(xmdl026/100+1)*(case when glaa001='JPY' AND ooao005 is NOT null THEN COALESCE(ooao005,0)ELSE COALESCE(xmda016,0) END))from xmdl_t left join xmdk_t on xmdlent=xmdkent and xmdldocno=xmdkdocno 
 where xmdlent=xmdcent and xmdl003=xmdcdocno and xmdl004=xmdcseq and xmdkstus='S' and xmdk000='1')
 when xmdc045 not in ('1','5')AND glaa001='RMB' AND xmdc016 LIKE'W%' THEN (select SUM(xmdl022*xmdl024*(case when glaa001='RMB' AND ooao005 is NOT null THEN COALESCE(ooao005,0)ELSE COALESCE(xmda016,0)END)) from xmdl_t left join xmdk_t on xmdlent=xmdkent and xmdldocno=xmdkdocno where xmdlent=xmdcent and xmdl003=xmdcdocno and xmdl004=xmdcseq and xmdkstus='S' and xmdk000='1')
 when xmdlent=xmdcent and xmdl003=xmdcdocno and xmdl004=xmdcseq and xmdkstus='S' and xmdk000='1')
 when xmdc045 not in ('1','5')AND glaa001='USD' AND xmdc016 LIKE'W%'THEN (select SUM(xmdl022*xmdl024*(case when glaa001='USD' AND ooao005 is NOT null THEN COALESCE(ooao005,0)ELSE COALESCE(xmda016,0)END))from xmdl_t left join xmdk_t on xmdlent=xmdkent and xmdldocno=xmdkdocno where xmdlent=xmdcent and xmdl003=xmdcdocno and xmdl004=xmdcseq and xmdkstus='S' and xmdk000='1')
 when xmdc045 not in ('1','5') AND glaa001='USD' AND xmdc016 LIKE 'W%'THEN COALESCE(ooao005,0)ELSE COALESCE(xmda016,0)END)) from xmdl_t left join xmdk_t on xmdlent=xmdkent and xmdldocno=xmdkdocno where xmdlent=xmdcent and xmdl003=xmdcdocno and xmdl004=xmdcseq and xmdkstus='S' and xmdk000='1')
 when xmdc045 not in('1','5')AND glaa001='EUR'AND xmdc016 LIKE'W%'THEN (select SUM(xmdl022*xmdl024*(case when glaa001='EUR'AND ooao005 is NOT null THEN COALESCE(ooao005,0)ELSE COALESCE(xmda016,0)END)) from xmdl_t left join xmdk_t on xmdlent=xmdkent and xmdldocno=xmdkdocno where xmdlent=xmdcent and xmdl003=xmdcdocno and xmdl004=xmdcseq and xmdkstus='S' and xmdk000='1')
 when xmdc045 not in('1','5')AND glaa001<>'JPY'AND xmdc016 LIKE 'W%'THEN (select SUM(xmdl022*xmdl024*(case when glaa001='JPY' AND ooao005 is NOT null THEN COALESCE(ooao005,0)ELSE COALESCE(xmda016,0)END))from xmdl_t left join xmdk_t on xmdlent=xmdkent and xmdldocno=xmdkdocno where xmdlent=xmdcent and xmdl003=xmdcdocno and xmdl004=xmdcseq and xmdkstus='S' and xmdk000='1')
 ELSE 0 end) as jg,(select SUM(xmdl022) from xmdl_t left join xmdk_t on xmdlent=xmdkent and xmdldocno=xmdkdocno where xmdlent=xmdcent and xmdl003=xmdcdocno and xmdl004=xmdcseq and xmdkstus='S' and xmdk000='1')as chjj
 from xmda_t left join xmdc_t on xmdadocno=xmdcdocno and xmdaent=xmdcent LEFT JOIN imaa_t on imaaent=xmdcent and imaa001=xmdc001
 LEFT JOIN ooao_t on ooaoent=xmdaent and ooao004=to_char(xmdadocdt,'yyyymm')and ooao002=xmda015 and ooao001='01'and ooao003='RMB'
 LEFT JOIN glaa_t on glaaent=xmdaent and xmdasite=glaald LEFT JOIN pmaa_t ON pmaaent='100' AND xmda004=pmaa001 where xmdastus IN('Y','C')AND xmdaent=100 and (imaa134 not IN('SZ01','SZ03')or imaa134 is null) AND xmda021 not in('XTEMP')--不包括内部订单
 and pmaa093 not in('03')--废料回收即是供应商又是客户
 and xmdasite in ('SZ') and xmda005<>'5'--排除预先
 and xmdadocdt>=TRUNC(NEXT_DAY(SYSDATE-8,1)-6)AND xmdadocdt<TRUNC(NEXT_DAY(SYSDATE-8,1)+1)--查询上周
 ) c left join ooefl_t on xmda003=ooefl001 and ooefl002='zh_CN'
     left join pmaal_t ON xmda004=pmaal001 and pmaal002='zh_CN' 
     LEFT JOIN pmaa_t ON pmaaent='100'AND xmda004=pmaa001
     left join imaal_t on xmdc001=imaal001 and imaal002='zh_CN'
     ORDER BY xmdadocdt,xmdadocno,xmdcseq