-----用友U9-SQL查询语句汇总---------

组织---
select * from (select a.ID,a.Code,b.Name from Base_Organization a
inner join Base_Organization_Trl b on a.ID=b.ID
where a.Code not in ('VPOrg','CPOrg',2001,2002) and a.Effective_IsEffective=1) a
---部门---
select * from (select a.ID,a.Code,b.Name from CBO_Department a
inner join CBO_Department_Trl b on a.ID=b.ID
where a.Org in (1001706090000058) and a.Effective_IsEffective=1) a
---业务员---
select * from (select a.ID,a.Code,b.Name from CBO_Operators a
inner join CBO_Operators_Trl b on a.ID=b.ID
where a.Effective_IsEffective=1 and a.Org in (1001706090000058) ) a
---存储地点----
--select * from CBO_Wh
select b.Code,a.Name,c.Name as Name1 from CBO_Wh_Trl a
inner join CBO_Wh b on a.ID=b.ID
inner join CBO_Operators_Trl c on b.Manager=c.ID
where b.Org=1001706090000058 and b.Effective_IsEffective=1
and b.Code not in ('K100101','K100102','K100103','K100201','K100104','K200801','K20601','K200700','K305002','K200802','K100202','K200400')
--料品信息-料号-名称-规格---------办公用品--------
select ID,Code,Name,SPECS from CBO_ItemMaster where Org=1001706090000058 and Effective_IsEffective=1 and Code like '9903%'
---资产卡片信息---
--select ID, * from FA_AssetCard_Trl
--select AssetCard ,* from FA_AssetTag

select a.ID,a.Code,a.Style,c.AssetName,d.CreateDate as 建卡日期,d.AccumulateUsedPeriods as 已使用年限月,
d.OriginalValue as 原值,d.NetValue as 净值,d.AccumulateDepreciation as 累积折旧 from FA_AssetTag a
inner join FA_AssetCard b on b.ID=a.AssetCard
inner join FA_AssetCard_Trl c on c.ID=b.ID
inner join FA_AssetCardAccountInformation d on b.DocNo=d.AssetCardCode

where b.Org=1001706090000058 and d.AssetCardCode='KP-2017040002' order by a.ID Desc
---供应商信息--开户行信息
--select TJOA,* from CBO_Supplier_Trl
select * from (select a.ID,a.Code,b.Name,c.Code as code1,d.Name as name1,a.DescFlexField_PrivateDescSeg1,a.DescFlexField_PrivateDescSeg2 from CBO_Supplier a
full join CBO_Supplier_Trl b on a.ID=b.ID
full join CBO_BankAccount c on a.ID=c.Supplier
full join CBO_BankAccount_Trl d on c.ID=d.ID
where a.Org=1001706090000058 and a.Effective_IsEffective=1) c
---供应商信息--开户行信息---联系人---联系人电话--立账条件-到期日
select * from (select a.ID,a.Code,b.Name,c.Code as code1,d.Name as name1,a.DescFlexField_PrivateDescSeg1,a.DescFlexField_PrivateDescSeg2,
e.Name as Name2,f.MaturityDateFirm_OffsetDays
from CBO_Supplier a
inner join CBO_Supplier_Trl b on a.ID=b.ID
inner join CBO_BankAccount c on a.ID=c.Supplier
inner join CBO_BankAccount_Trl d on c.ID=d.ID
inner join CBO_APConfirmTerm_Trl e on e.ID=a.APConfirmTerm--立账条件
inner join CBO_InstalmentTerm f on f.APConfirmTerm=e.ID--到期日
where a.Org=1001706090000058 and a.Effective_IsEffective=1) G

---供应商分类信息----
select * from (select a.ID,a.Code,b.Name,c.Code as code1,d.Name as name1 from CBO_Supplier a
inner join CBO_Supplier_Trl b on a.ID=b.ID
inner join CBO_BankAccount c on a.ID=c.Supplier
inner join CBO_BankAccount_Trl d on c.ID=d.ID
where a.Org=1001706090000058 and a.Effective_IsEffective=1) c
---客户信息--银行--联系人---地址
select * from (select a.ID,a.Code,b.Name,c.Code as code1,d.Name as name1,a.DescFlexField_PrivateDescSeg1,a.DescFlexField_PrivateDescSeg2 from CBO_Customer a
inner join CBO_Customer_Trl b on a.ID=b.ID
inner join CBO_CustomerBankAccount c on a.ID=c.Customer
inner join CBO_CustomerBankAccount_Trl d on c.ID=d.ID
where a.Org=1001706090000058 and a.Effective_IsEffective=1) c
---物料信息--编码-料号-名称-MPN-规格-描述-单位---
select a.ID,a.Code,a.Name,a.DescFlexField_PrivateDescSeg1,a.SPECS,b.Name,c.Description as 描述 from CBO_ItemMaster a
inner join Base_UOM_Trl b on a.InventorySecondUOM=b.ID
inner join CBO_ItemMaster_Trl c on a.ID=c.ID
where Org=1001706090000058 and Effective_IsEffective=1