/*dialect*/
with ck as (
select f.FNAME_L2 as FCostCenter,d.fnumber as FCostObjectNumber
,d.fname_l2 as FCostObjectName
,e.FNUMBER as FItemNumber,e.fname_l2 AS FItemName,g.FNAME_L2 as FUnitName
,sum(b.FQTY) as FBCQTY
,sum(b.CFLOSSQTY) as CFLOSSQTY
,ROUND(sum(b.FACTUALCOST)/sum(b.FQTY),2) FUnitActualCost
,sum(b.FACTUALCOST) as FACTUALCOST
,b.fmaterialid as fmaterialid
from T_IM_MaterialReqBill a --领料出库
inner join T_IM_MaterialReqBillEntry b on a.fid=b.FParentID --领料出库单表体
inner join T_ORG_BaseUnit c on a.FStorageOrgUnitID=c.fid --组织
left join T_BD_CostObject d on b.FCOSTOBJECTID=d.fid --成本对象
inner join T_BD_Material e on b.FMaterialID=e.FID --物料
inner join T_ORG_CostCenter f on b.FCOSTCENTERORGUNITID=f.fid --成本中心
inner join T_BD_MeasureUnit g on e.FBaseUnit=g.fid --计量单位
where to_char(a.FBIZDATE,'YYYY-MM-DD')>='@FBegDate'
and to_char(a.FBIZDATE,'YYYY-MM-DD')<='@FEndDate'
and (c.FID='KWYAAAAAAAvM567U')
and (f.FNAME_L2='@FDepment' or ? is null)
and a.FBaseStatus in ('4','7') --状态：审核、关闭
group by f.FNAME_L2,d.fnumber,d.fname_l2,e.FNUMBER,e.fname_l2,g.FNAME_L2,b.fmaterialid)
,rk as(
select c.FNumber as FCostObjectNumber,c.FNAME_L2 as FCostObjectName,d.fname_l2 as FCostUnitName,b.fmaterialid as fmaterialid
,f.fname_l2 as FCostCenter,sum(b.fqty) as fCPQty,e.fid
from T_IM_ManufactureRecBill a --产品入库
inner join T_IM_ManufactureRecBillentry b on a.fid=b.FParentID
left join T_BD_Material c on b.FMaterialID=c.fid --物料
left join T_BD_MeasureUnit d on c.FWEIGHTUNIT=d.fid --计量单位
left join T_ORG_BaseUnit e on a.FSTORAGEORGUNITID=e.fid --组织
inner join T_ORG_CostCenter f on b.FCOSTCENTERORGUNITID=f.fid --成本中心
where to_char(a.FBIZDATE,'YYYY-MM-DD')>='@FBegDate'
and to_char(a.FBIZDATE,'YYYY-MM-DD')<='@FEndDate'
and a.FTransactionTypeID='KWYAAAACS2awCNyn' --事务类型
and (e.FID='KWYAAAAAAAvM567U')
and (f.FNAME_L2='@FDepment' or ? is null)
and a.FBaseStatus in ('4','7') --状态：审核、关闭
group by c.fnumber,c.FNAME_L2,d.fname_l2,f.fname_l2,e.fid,b.fmaterialid)
select rk.FCostCenter,rk.FCostObjectNumber,rk.FCostObjectName,rk.FCostUnitName,rk.fcpqty
,ck.FItemNumber,ck.FItemName,ck.FUnitName,ck.FBCQTY,ck.CFLOSSQTY,floor(b1.FConsumeFixQty/b.FProductQty * rk.fcpqty) rateqty,
ck.FUnitActualCost,ck.FACTUALCOST
from rk
full outer join ck on ck.FCostCenter=rk.FCostCenter and rk.FCostObjectNumber=ck.FCostObjectNumber
left join t_MM_bom b on rk.fmaterialid = b.fmaterialid
left join T_MM_BomEntry b1 on b.fid = b1.fparentid and ck.FMaterialID=b1.FMaterialID
order by rk.FCostCenter,ck.FCostObjectNumber
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
with ck as (
select b.FQty as 销售数量 ,b.FPrice as 销售价格,b.FAmount as 销售金额,c.FQty as 发货数量,c.FPrice as 发货价格,
c.FAmount as 发货金额 ,d.Fname_L2 as 组织,f.fname_l2 as 产品名称
from T_SD_SaleOrder a
inner join T_SD_SaleOrderEntry b on a.fid=b.FParentID
inner join T_SD_PostRequisitionEntry c on b.fid=c.FSaleOrderEntryID
left join T_ORG_CtrlUnit d on a.FControlUnitID=d.fid --组织
inner join T_BD_Material e on b.FMaterialID=e.FID --物料
left join T_BD_CostObject f on f.FStdProductIDID=e.fid --成本中心
）
,rk as（
select j.FQty as 销退数量 ,j.FPrice as 销退价格,j.FAmount as 销退金额,g.Fname_L2 as 组织,x.fname_l2 as 产品名称
from T_SD_SaleReturns h
inner join T_SD_SaleReturnsEntry j on h.fid=j.FParentID
left join T_ORG_CtrlUnit g on h.FControlUnitID=g.fid --组织
inner join T_BD_Material z on j.FMaterialID=z.FID --物料
left join T_BD_CostObject x on x.FStdProductIDID=z.fid --成本中心
)
,tk as (
select b.FQuantity as 应收数量,b.FPrice as 应收单价,b.FAmount as 应收金额,g.Fname_L2 as 组织,x.fname_l2 as 产品名称
from T_AR_OtherBill h
inner join T_AR_OtherBillEntry b on h.fid=b.FParentID
left join T_ORG_CtrlUnit g on h.FControlUnitID=g.fid --组织
inner join T_BD_Material z on b.FMaterialID=z.FID --物料
left join T_BD_CostObject x on x.FStdProductIDID=z.fid --成本中心
)
,yk as (
select b.FQuantity as 应退数量,b.FPrice as 应退单价,b.FAmount as 应退金额,g.Fname_L2 as 组织,x.fname_l2 as 产品名称
from T_AR_OtherBill h
inner join T_AR_OtherBillEntry b on h.FSourceBillId=b.FSourceBillId
left join T_ORG_CtrlUnit g on h.FControlUnitID=g.fid --组织
inner join T_BD_Material z on b.FMaterialID=z.FID --物料
left join T_BD_CostObject x on x.FStdProductIDID=z.fid --成本中心
）
，uk as (
select b.FQty as 销库数量,b.FSalePrice as 销库单价,b.FNonTaxAmount as 销库金额,g.Fname_L2 as 组织,x.fname_l2 as 产品名称
from T_IM_SaleIssueBill h
inner join T_IM_SaleIssueEntry b on h.fid=b.FParentID
left join T_ORG_CtrlUnit g on h.FControlUnitID=g.fid --组织
inner join T_BD_Material z on b.FMaterialID=z.FID --物料
left join T_BD_CostObject x on x.FStdProductIDID=z.fid --成本中心
）
，ok as (
select b.FQty as 销退库数量,b.FPrice as 销退库单价,b.FAmount as 销退库金额,g.Fname_L2 as 组织,x.fname_l2 as 产品名称
from T_SD_SaleReturns h
inner join T_SD_SaleReturnsEntry b on h.fid=b.FParentID
left join T_ORG_CtrlUnit g on h.FControlUnitID=g.fid --组织
inner join T_BD_Material z on b.FMaterialID=z.FID --物料
left join T_BD_CostObject x on x.FStdProductIDID=z.fid --成本中心
)
,fk as (
select b.FQty as 物销退数量,b.FPrice as 物销退单价,b.FAmount as 物销退金额,g.Fname_L2 as 组织,x.fname_l2 as 产品名称
from T_IM_StockTransferBill h
inner join T_IM_StockTransferBillEntry b on h.fid=b.FParentID
left join T_ORG_CtrlUnit g on h.FControlUnitID=g.fid --组织
inner join T_BD_Material z on b.FMaterialID=z.FID --物料
left join T_BD_CostObject x on x.FStdProductIDID=z.fid --成本中心
)
select rk.销退数量,rk.销退价格,rk.FCostObjectName,rk.销退金额,rk.成本中心
,ck.销售数量,ck.销售价格,ck.销售金额,ck.发货数量,ck.发货价格,ck.发货金额,
yk.应退数量，yk.应退单价,yk.应退金额,uk.销库数量,uk.销库单价,uk. 销库金额,
ok.销退库数量,ok.销退库单价,ok.销退库金额,fk.物销退数量,fk.物销退单价,fk.物销退金额
from rk
full outer join ck on ck.FCostCenter=rk.FCostCenter and rk.FCostObjectNumber=ck.FCostObjectNumber
left join t_MM_bom b on rk.fmaterialid = b.fmaterialid
left join T_MM_BomEntry b1 on b.fid = b1.fparentid and ck.FMaterialID=b1.FMaterialID
最后整合查询的时候 From后面应该怎么写呢
