| /*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 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---销售订单b 发货通知单c---- select * from T_SD_SaleOrder; select * from T_SD_SaleOrderEntry; select b.FQty,b.FPrice,b.FAmount,c.FQty,c.FPrice,c.FAmount from T_SD_SaleOrder a left join T_SD_SaleOrderEntry b on a.fid=b.FParentID left join T_SD_PostRequisitionEntry c on b.fid=c.FSaleOrderEntryID; -----销售退货单---- select * from T_SD_SaleReturnsEntry; select b.FQty,b.FPrice,b.FAmount from T_SD_SaleReturns a left join T_SD_SaleReturnsEntry b on a.fid=b.FParentID ----应收单-------- select FQuantity from T_AR_OtherBillEntry; select b.FQuantity,b.FPrice,b.FAmount from T_AR_OtherBill a left join T_AR_OtherBillEntry b on a.fid=b.FParentID; ----应收单退货-------- select FQuantity from T_AR_OtherBillEntry; select b.FQuantity,b.FPrice,b.FAmount from T_AR_OtherBill a inner join T_AR_OtherBillEntry b on a.FSourceBillId =b.FSourceBillId; -----销售出库单---- select * from T_IM_SaleIssueEntry; select b.FQty,b.FSalePrice,b.FNonTaxAmount from T_IM_SaleIssueBill a left join T_IM_SaleIssueEntry b on a.fid=b.FParentID -----股份公司销售退库单--- select * from T_SD_SaleReturnsEntry; select b.FQty,b.FPrice,b.FAmount from T_SD_SaleReturns a left join T_SD_SaleReturnsEntry b on a.fid=b.FParentID -------物管处销售退库单（退货申请下的调拨单）------- select * from T_IM_StockTransferBillEntry; select b.FQty,b.FPrice,b.FAmount from T_IM_StockTransferBill a left join T_IM_StockTransferBillEntry b on a.FSourceBillId =b.FSourceBillId; 谁能帮我把下面的按上面的拼起来呀 实在是不会拼 |
