| /*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后面应该怎么写呢 |
