EAS库存台账报表SQL

SELECT

"ALLINVENTORY".FID AS "ID",
"ALLINVENTORY".FStorageOrgUnitID AS "STORAGEORGUNIT.ID", ---------库存组织ID
"STORAGEORGUNIT".FNumber AS "STORAGEORGUNIT.NUMBER", ------------库存组织编码
"STORAGEORGUNIT".FName_l2 AS "STORAGEORGUNIT.NAME", -------------库存组织名称
"DEPOT".FNumber AS "DEPOT.NUMBER", ------------------------------仓库编码
"DEPOT".FName_l2 AS "DEPOT.NAME", --------------------------------仓库名称

"GOODS".FNumber AS "GOODS.NUMBER", --------------------------------商品编码
"GOODS".FName_l2 AS "GOODS.NAME", --------------------------------商品名称
"GOODS".FDefBarcode AS "GOODS.DEFBARCODE", --------------------------------商品条码
"GOODSGROUP".FNumber AS "GOODSGROUP.NUMBER", --------------------------------类别编码
"GOODSGROUP".FName_l2 AS "GOODSGROUP.NAME", --------------------------------类别名称

"BASEUNIT".FName_l2 AS "BASEUNIT.NAME", ------------------------------------基本计量单位
"GOODS".FModel AS "GOODS.MODEL", -----------------------------------规格型号
nvl("ALLINVENTORY".FBaseQty,0) AS "BASEQTY", -----------------------------------库存数量
nvl("RetailEntry".FBaseQty,0) as "JSBASEQTY",------------------------------------即时销售数量
(nvl("ALLINVENTORY".FBaseQty,0)-nvl("RetailEntry".FBaseQty,0)) as "JSKCBASEQTY",---------------即时库存数量
nvl("GoodsSales".Fsaleprice,0.00) as "SJPrice"

 

FROM T_RT_AllInventory  "ALLINVENTORY"

LEFT OUTER JOIN T_ORG_Storage  "STORAGEORGUNIT"
ON "ALLINVENTORY".FStorageOrgUnitID = "STORAGEORGUNIT".FID

 

LEFT OUTER JOIN T_IM_STORESTATE  "STORESTATE"
ON "ALLINVENTORY".FStoreStateID = "STORESTATE".FID

LEFT OUTER JOIN T_RT_Goods  "GOODS"
ON "ALLINVENTORY".FGoodsID = "GOODS".FID

LEFT OUTER JOIN T_BD_MeasureUnit  "BASEUNIT"
ON "ALLINVENTORY".FBaseUnitID = "BASEUNIT".FID

LEFT OUTER JOIN T_RT_Depot  "DEPOT"
ON "ALLINVENTORY".FDepotID = "DEPOT".FID

-------zyw
LEFT OUTER JOIN T_LS_RetailEntry "RetailEntry"
on "ALLINVENTORY".FDepotID="RetailEntry".FDepotID and "ALLINVENTORY".FGoodsID="RetailEntry".FGoodsID -------仓库，商品一致的条件下去销售数量

left OUTER join T_LS_Retail "Retail"
on "RetailEntry".FParentID="Retail".fid and "Retail".FStatus=0-----未日结的销售数量

LEFT OUTER JOIN T_ORG_UnitRelation  "RELATIONA"
ON "ALLINVENTORY".FStorageOrgUnitID = "RELATIONA".FFromUnitID

LEFT OUTER JOIN T_ORG_TypeRelation  "TYPERELATIONA"
ON "RELATIONA".FTypeRelationID = "TYPERELATIONA".FID

LEFT OUTER JOIN T_RT_GoodsSales "GoodsSales"
on "GoodsSales".FGoodsID="ALLINVENTORY".FGoodsID and "GoodsSales".FSaleOrgID="RELATIONA".FTOUNITID--------商品相同，库存委托销售组织相同

LEFT OUTER JOIN T_ORG_Sale "OrgSales"
on "RELATIONA".FTOUNITID="OrgSales".fid and "OrgSales".fid="GoodsSales".FSaleOrgID

 
-------zyw

LEFT OUTER JOIN T_RT_GoodsGroup  "GOODSGROUP"
ON "ALLINVENTORY".FGoodsGroupID = "GOODSGROUP".FID

 

where "TYPERELATIONA".FFromType = 4 and "TYPERELATIONA".FToType = 2

 

ORDER BY
"STORAGEORGUNIT.NUMBER" ASC,
"DEPOT.NUMBER" ASC,
"GOODS.NUMBER" ASC

/*-----------------------------------------------------------------
/*select FBeginTime,FEndTime from T_LS_Retail

1代表已日结
0代表未日结*/

 

select FGoodsGroupID from T_RT_AllInventory

 

/*sql.append("SELECT                                                                                          
sql.append("DISTINCT STORAGEORGUNIT.FID AS ID,                                                            
sql.append("STORAGEORGUNIT.Fnumber /r/n");
sql.append("FROM T_ORG_Storage AS STORAGEORGUNIT 

                                                         
sql.append("INNER JOIN T_ORG_UnitRelation AS RELATIONA                                                    
sql.append("ON STORAGEORGUNIT.FID = RELATIONA.FFromUnitID   

                                             
sql.append("LEFT OUTER JOIN T_ORG_TypeRelation AS TYPERELATIONA                                           
sql.append("ON RELATIONA.FTypeRelationID = TYPERELATIONA.FID                                              
sql.append("WHERE STORAGEORGUNIT.FIsBizUnit = 1 AND TYPERELATIONA.FFromType = 4 AND  /r/n");
sql.append("TYPERELATIONA.FToType = 2 and RELATIONA.FTOUNITID = '").append(saleOrgUnitId).append
sql.append("order by STORAGEORGUNIT.Fnumber desc");

*/
select * from T_ORG_UnitRelation

select * from T_ORG_Storage
----------------------------------------------------------

select "SaleOrgUnit".fname_l2 from T_ORG_Sale  "SaleOrgUnit"

inner join T_ORG_UnitRelation "RELATIONA"
on "SaleOrgUnit".FID="RELATIONA".Ftounitid and "RELATIONA".Ftounitid='LptYeuxPQM6fKZmdmZtRMsznrtQ='

LEFT OUTER JOIN T_ORG_TypeRelation  "TYPERELATIONA"
ON "RELATIONA".FTypeRelationID = "TYPERELATIONA".FID

where "SaleOrgUnit".FIsBizUnit = 1 ------1代表实体组织
AND "TYPERELATIONA".FFromType = 4 -------委托组织为库存组织
AND "TYPERELATIONA".FToType = 2----------被委托组织为销售组织

select Fsaleprice,"OrgSale".fname_l2 from T_RT_GoodsSales "GoodsSales"
left outer join T_ORG_Sale "OrgSale" on "OrgSale".fid="GoodsSales".FSaleOrgID

select "GoodsSales".Fsaleprice
from T_RT_GoodsSales "GoodsSales"
left outer join T_ORG_Sale "OrgSale" on "OrgSale".fid="GoodsSales".FSaleOrgID

 

 

 

*/