销售订单
SELECT *
FROM ( SELECT *
FROM dbo.T_SAL_ORDER A
INNER JOIN dbo.T_SAL_ORDERENTRY B
ON B.FID = A.FID
INNER JOIN dbo.T_SAL_ORDERENTRY_F C
ON C.FENTRYID = B.FENTRYID
INNER JOIN dbo.T_SAL_ORDERENTRY_R D
ON D.FENTRYID = B.FENTRYID
WHERE A.FDOCUMENTSTATUS = 'C') SAL;
物料
SELECT *
FROM ( SELECT a.FMATERIALID '物料ID',
a.FNUMBER '物料编码',
b.FNAME '物料名称',
b.FSPECIFICATION '规格型号',
c.FNUMBER '分组编码',
d.FNAME '分组名称'
FROM dbo.T_BD_MATERIAL a
INNER JOIN dbo.T_BD_MATERIAL_L b
ON b.FMATERIALID = a.FMATERIALID
INNER JOIN T_BD_MATERIALGROUP c
ON a.FMATERIALGROUP = c.FID
INNER JOIN dbo.T_BD_MATERIALGROUP_L d
ON c.FID = d.FID) MAT;
客户
SELECT a.FCUSTID '客户ID',
a.FNUMBER '客户编码',
b.FNAME '客户名称'
FROM dbo.T_BD_CUSTOMER a
INNER JOIN dbo.T_BD_CUSTOMER_L b
ON b.FCUSTID = a.FCUSTID;
客户分组级次
SELECT A.FCUSTID '客户ID',
D.FLevel '级次',
GROUP1.分组名称 '一级分组名称',
GROUP2.分组名称 '二级分组名称',
GROUP3.分组名称 '三级分组名称',
GROUP4.分组名称 '四级分组名称',
GROUP5.分组名称 '五级分组名称',
GROUP6.分组名称 '六级分组名称',
GROUP7.分组名称 '七级分组名称'
FROM dbo.T_BD_CUSTOMER A
INNER JOIN T_BD_CUSTOMERGROUP B
ON A.FPRIMARYGROUP = B.FID
INNER JOIN dbo.T_BD_CUSTOMERGROUP_L C
ON B.FID = C.FID
INNER JOIN T_BD_CUSTOMERGROUPLEVEL D
ON B.FID = D.FID
LEFT JOIN
(SELECT A.FID '分组ID',B.FNAME '分组名称' FROM T_BD_CUSTOMERGROUP A
INNER JOIN dbo.T_BD_CUSTOMERGROUP_L B
ON A.FID = B.FID) GROUP1 ON D.FGROUP1 = GROUP1.分组ID
LEFT JOIN
(SELECT A.FID '分组ID',B.FNAME '分组名称' FROM T_BD_CUSTOMERGROUP A
INNER JOIN dbo.T_BD_CUSTOMERGROUP_L B
ON A.FID = B.FID) GROUP2 ON D.FGROUP2 = GROUP2.分组ID
LEFT JOIN
(SELECT A.FID '分组ID',B.FNAME '分组名称' FROM T_BD_CUSTOMERGROUP A
INNER JOIN dbo.T_BD_CUSTOMERGROUP_L B
ON A.FID = B.FID) GROUP3 ON D.FGROUP3 = GROUP3.分组ID
LEFT JOIN
(SELECT A.FID '分组ID',B.FNAME '分组名称' FROM T_BD_CUSTOMERGROUP A
INNER JOIN dbo.T_BD_CUSTOMERGROUP_L B
ON A.FID = B.FID) GROUP4 ON D.FGROUP4 = GROUP4.分组ID
LEFT JOIN
(SELECT A.FID '分组ID',B.FNAME '分组名称' FROM T_BD_CUSTOMERGROUP A
INNER JOIN dbo.T_BD_CUSTOMERGROUP_L B
ON A.FID = B.FID) GROUP5 ON D.FGROUP5 = GROUP5.分组ID
LEFT JOIN
(SELECT A.FID '分组ID',B.FNAME '分组名称' FROM T_BD_CUSTOMERGROUP A
INNER JOIN dbo.T_BD_CUSTOMERGROUP_L B
ON A.FID = B.FID) GROUP6 ON D.FGROUP6 = GROUP6.分组ID
LEFT JOIN
(SELECT A.FID '分组ID',B.FNAME '分组名称' FROM T_BD_CUSTOMERGROUP A
INNER JOIN dbo.T_BD_CUSTOMERGROUP_L B
ON A.FID = B.FID) GROUP7 ON D.FGROUP7 = GROUP7.分组ID
WHERE A.FNUMBER = '01.01.01.12.01';
供应商
SELECT a.FSUPPLIERID '供应商ID',
a.FNUMBER '供应商编码',
b.FNAME '供应商名称'
FROM dbo.T_BD_SUPPLIER a
INNER JOIN dbo.T_BD_SUPPLIER_L b
ON b.FSUPPLIERID = a.FSUPPLIERID;
组织
SELECT a.FORGID '组织ID',
a.FNUMBER '组织编码',
b.FNAME '组织名称'
FROM dbo.T_ORG_ORGANIZATIONS a
INNER JOIN T_ORG_ORGANIZATIONS_L b
ON b.FORGID = a.FORGID;
部门
SELECT a.FDEPTID '部门ID',
a.FNUMBER '部门编码',
b.FNAME '部门名称'
FROM dbo.T_BD_DEPARTMENT a
INNER JOIN dbo.T_BD_DEPARTMENT_L b
ON a.FDEPTID = b.FDEPTID;
仓库
SELECT a.FSTOCKID '仓库ID',
a.FNUMBER '仓库编码',
b.FNAME '仓库名称'
FROM dbo.T_BD_STOCK a
INNER JOIN dbo.T_BD_STOCK_L b
ON b.FSTOCKID = a.FSTOCKID;
销售出库单
SELECT * FROM dbo.T_SAL_OUTSTOCK A INNER JOIN dbo.T_SAL_OUTSTOCKENTRY B ON B.FID = A.FID
仓库和仓位取值关系
SELECT B.FSTOCKLOCID,
B.*
FROM dbo.T_SAL_OUTSTOCK A
INNER JOIN dbo.T_SAL_OUTSTOCKENTRY B
ON B.FID = A.FID
WHERE A.FBILLNO = 'XSCKD000054'; --102345
SELECT FSTOCKID
FROM dbo.T_BD_STOCK
WHERE FNUMBER = 'CK01LQ'; --根据仓库编码查询仓库信息
SELECT FENTRYID
FROM dbo.T_BD_STOCKFLEXITEM
WHERE FSTOCKID = '128344'; --仓库和仓位值集表，通过仓库ID查询出仓位值的FENTRYID
SELECT *
FROM T_BD_STOCKFLEXDETAIL
WHERE FENTRYID = '100013'; --根据上表查询出来的FENTRYID查询出仓位FFLEXENTRYID这个值
SELECT loc.FID AS '仓位ID'
FROM T_BAS_FLEXVALUESDETAIL loc
LEFT JOIN T_BAS_FLEXVALUESENTRY locCol1
ON (loc.FF100015 = locCol1.FENTRYID)
LEFT JOIN T_BAS_FLEXVALUESENTRY_L locCol1L
ON locCol1.FENTRYID = locCol1L.FENTRYID
WHERE locCol1.FENTRYID = '100146'; --根据上表的FFLEXENTRYID值查询出仓位的ID，单据上的仓位字段存的就是这个值
--===================================================================================================================
SELECT a.仓库ID,
a.仓库名称,
d.仓位ID,
d.仓位名称,
c.无值内码,
c.内码
FROM ( SELECT a.FSTOCKID '仓库ID',
b.FNAME '仓库名称'
FROM dbo.T_BD_STOCK a
INNER JOIN dbo.T_BD_STOCK_L b
ON a.FSTOCKID = b.FSTOCKID) a
LEFT JOIN ( SELECT FENTRYID '仓位值表FENTRYID',
FSTOCKID '仓库ID'
FROM dbo.T_BD_STOCKFLEXITEM) b
ON b.仓库ID = a.仓库ID
LEFT JOIN ( SELECT FDETAILID '内码',
FENTRYID '仓位值表FENTRYID',
FFLEXENTRYID '仓位表FENTRYID',
F_T_IIDE '无值内码'
FROM T_BD_STOCKFLEXDETAIL) c
ON c.仓位值表FENTRYID = b.仓位值表FENTRYID
LEFT JOIN ( SELECT loc.FID AS '仓位ID',
locCol1L.FNAME '仓位名称',
locCol1.FENTRYID '仓位表FENTRYID'
FROM T_BAS_FLEXVALUESDETAIL loc
LEFT JOIN T_BAS_FLEXVALUESENTRY locCol1
ON (loc.FF100015 = locCol1.FENTRYID)
LEFT JOIN T_BAS_FLEXVALUESENTRY_L locCol1L
ON locCol1.FENTRYID = locCol1L.FENTRYID) d
ON d.仓位表FENTRYID = c.仓位表FENTRYID
WHERE d.仓位ID = '102345';
即时库存
SELECT TI.FBASEQTY '即时库存'
FROM T_STK_INVENTORY TI
LEFT JOIN T_ORG_ORGANIZATIONS_L OL
ON TI.FSTOCKORGID = OL.FORGID
AND OL.FLOCALEID = 2052 --组织名称表
INNER JOIN T_BD_MATERIAL M
ON M.FMASTERID = TI.FMATERIALID
AND ( M.FUSEORGID = TI.FSTOCKORGID
OR EXISTS ( SELECT 1
FROM T_META_BASEDATATYPE BT
WHERE BT.FBASEDATATYPEID = 'BD_MATERIAL'
AND BT.FSTRATEGYTYPE = 1)) --物料类型 1==外购
INNER JOIN T_BD_MATERIALBASE AS m2
ON M.FMATERIALID = m2.FMATERIALID
LEFT JOIN T_BD_MATERIAL_L ML
ON ( M.FMATERIALID = ML.FMATERIALID
AND ML.FLOCALEID = 2052)
INNER JOIN T_BD_MATERIALSTOCK TMS
ON M.FMATERIALID = TMS.FMATERIALID
INNER JOIN T_BD_MATERIALPRODUCE MP
ON M.FMATERIALID = MP.FMATERIALID
LEFT JOIN T_ENG_BOM TB
ON ( TB.FMASTERID = TI.FBOMID
AND ( TB.FUSEORGID = TI.FSTOCKORGID
OR EXISTS ( SELECT 1
FROM T_META_BASEDATATYPE BT
WHERE ( BT.FBASEDATATYPEID = 'ENG_BOM'
AND BT.FSTRATEGYTYPE = 1))))
------库存状态
LEFT JOIN T_BD_STOCKSTATUS_L TSSL
ON ( TI.FSTOCKSTATUSID = TSSL.FSTOCKSTATUSID
AND TSSL.FLOCALEID = 2052)
LEFT JOIN T_BD_LOTMASTER TL
ON ( ( TI.FLOT = TL.FMASTERID
AND TI.FSTOCKORGID = TL.FUSEORGID)
AND TL.FBIZTYPE = '1')
LEFT JOIN T_BD_UNIT TUS
ON TMS.FSTOREUNITID = TUS.FUNITID
LEFT JOIN T_BD_UNIT_L TUL0
ON ( TMS.FSTOREUNITID = TUL0.FUNITID
AND TUL0.FLOCALEID = 2052)
INNER JOIN T_BD_STOCK TSK
ON ( TSK.FMASTERID = TI.FSTOCKID
AND ( TSK.FUSEORGID = TI.FSTOCKORGID
OR EXISTS ( SELECT 1
FROM T_META_BASEDATATYPE BT
WHERE ( BT.FBASEDATATYPEID = 'BD_STOCK'
AND BT.FSTRATEGYTYPE = 1))))
LEFT JOIN T_BD_STOCK_L TSL
ON ( TSL.FSTOCKID = TI.FSTOCKID
AND TSL.FLOCALEID = 2052)
LEFT JOIN T_BD_STOCKGROUP TSKG
ON TSK.FGROUP = TSKG.FID
LEFT JOIN T_BD_STOCKGROUP_L TSKGL
ON TSKG.FID = TSKGL.FID
LEFT JOIN T_BD_UNIT_L TUL1
ON ( TI.FBASEUNITID = TUL1.FUNITID
AND TUL1.FLOCALEID = 2052)
LEFT JOIN T_BD_UNIT TUE
ON TMS.FAUXUNITID = TUE.FUNITID
LEFT JOIN T_BD_UNIT_L TUL2
ON ( TI.FSECUNITID = TUL2.FUNITID
AND TUL2.FLOCALEID = 2052)
LEFT JOIN V_ITEMCLASS_OWNER VO
ON ( ( VO.fmasterid = TI.FOWNERID
AND VO.fformid = TI.FOWNERTYPEID)
AND ( VO.fuseorgid = TI.FSTOCKORGID
OR VO.fuseorgid = 0
OR EXISTS ( SELECT 1
FROM T_META_BASEDATATYPE BT
WHERE ( BT.FBASEDATATYPEID = VO.fformid
AND BT.FSTRATEGYTYPE = 1))))
LEFT JOIN V_ITEMCLASS_OWNER_L VO_L
ON ( VO.fitemid = VO_L.fitemid
AND VO_L.FLOCALEID = 2052)
LEFT JOIN V_ITEMCLASS_KEEPER VK
ON ( ( VK.fmasterid = TI.FKEEPERID
AND VK.fformid = TI.FKEEPERTYPEID)
AND ( VK.fuseorgid = TI.FSTOCKORGID
OR VK.fuseorgid = 0
OR EXISTS ( SELECT 1
FROM T_META_BASEDATATYPE BT
WHERE ( BT.FBASEDATATYPEID = VK.fformid
AND BT.FSTRATEGYTYPE = 1))))
LEFT JOIN V_ITEMCLASS_KEEPER_L VK_L
ON ( VK.fitemid = VK_L.fitemid
AND VK_L.FLOCALEID = 2052);
查询账套信息
SELECT A.FDATACENTERID '账套ID',
A.FNUMBER '数据中心代码',
A.FDATABASENAME '数据库实体名',
A.FVISION '账套版本',
B.FNAME '数据中心名称'
FROM dbo.T_BAS_DATACENTER A
INNER JOIN T_BAS_DATACENTER_L B
ON A.FDATACENTERID = B.FDATACENTERID;
辅助资料
SELECT t3.FDATAVALUE AS 资料名称,
t2.FENTRYID
FROM T_BAS_ASSISTANTDATA t0
INNER JOIN T_BAS_ASSISTANTDATA_L t1
ON (
t0.FID = t1.FID
AND t1.FLOCALEID = 2052
)
INNER JOIN T_BAS_ASSISTANTDATAENTRY t2
ON (t0.FID = t2.FID)
INNER JOIN T_BAS_ASSISTANTDATAENTRY_L t3
ON (
t2.FENTRYID = t3.FENTRYID
AND t3.FLOCALEID = 2052
);
赞 6
打赏推荐阅读