金蝶云星空查询SQL

税率、仓库、部门、员工、供应商、客户、物料类别、物料查询、BOM查询、

辅助资料：工程项目、辅助资料：产品服务、科目、核算维度、科目余额、

未转采购订单的采购申请、采购订单未完结的：待接收的、

暂估应付： 未开票

--税率select rat.FNUMBER,rat.FTAXRATE, ratl.FNAME from T_BD_TAXRATE ratinner join T_BD_TAXRATE_L ratlon rat.FID=ratl.FID and ratl.FLOCALEID=2052;--仓库select stkl.FNAME,stk.FNUMBER,stk.FSTOCKPROPERTY, org.FNAME   from t_bd_stock stkinner join T_BD_STOCK_L stklon stk.FSTOCKID=stkl.FSTOCKIDinner join T_ORG_ORGANIZATIONS_L orgon org.FORGID=stk.FUSEORGIDand org.FLOCALEID=2052order by org.FNAME ,stk.FNUMBER ;--部门select   dpt.FNUMBER,dptl.FNAME ,org.FNAME ,dpt.FISDETAILDPTfrom T_BD_DEPARTMENT_L dptl inner join T_BD_DEPARTMENT dpt on dpt.FDEPTID=dptl.FDEPTIDand dptl.FLOCALEID=2052inner join T_ORG_ORGANIZATIONS_L orgon  org.FORGID=dpt.FUSEORGID and org.FLOCALEID=2052where dpt.FISDETAILDPT =1and org.FNAME='所托（杭州）汽车智能设备有限公司';--员工select emp.FNUMBER,empl.FNAMEfrom T_HR_EMPINFO empinner join T_HR_EMPINFO_L emplon emp.FID=empl.FIDand empl.FLOCALEID=2052where emp.FNUMBER='0080'order by empl.FNAME--供应商select sup.FNUMBER,supl.FNAME,supbas.FADDRESS,supfin.FTAXREGISTERCODE as "纳税识别号",rat.FTAXRATE as "税率",ratl.fname as "税率1",( select term.FNAME  from T_BD_PAYMENTCONDITION_L term where term.FID =supfin.FPAYCONDITION) as "付款条件"from T_BD_SUPPLIER  supinner join T_BD_SUPPLIER_L suplon sup.FSUPPLIERID=supl.FSUPPLIERIDand supl.FLOCALEID=2052inner join T_BD_SUPPLIERBASE supbason supbas.FSUPPLIERID=sup.FSUPPLIERIDinner join T_BD_SUPPLIERFINANCE supfinon supfin.FSUPPLIERID=sup.FSUPPLIERIDleft join T_BD_TAXRATE raton supfin.FTAXRATEID=rat.FIDinner join T_BD_TAXRATE_L ratlon rat.FID=ratl.FID and ratl.FLOCALEID=2052where supl.FLOCALEID=2052--and sup.FNUMBER='10130086'and sup.FUSEORGID=100105;--客户select cus.FNUMBER,cusl.FNAME,org.FNAME,len(cus.FNUMBER)from T_BD_CUSTOMER   cusinner join T_BD_CUSTOMER_L cuslon cus.FCUSTID=cusl.FCUSTIDand cusl.FLOCALEID=2052and cusl.FLOCALEID=2052inner join T_ORG_ORGANIZATIONS_L orgon  org.FORGID=cus.FUSEORGID and org.FLOCALEID=2052where 1=1--and sup.FNUMBER='10130086'and len(cus.FNUMBER)<>8and cus.FUSEORGID=100105--单位select uom.FNUMBER,uoml.FNAME  from T_BD_UNIT_L uoml inner join T_BD_UNIT uomon uom.FUNITID=uoml.FUNITID and  uoml.FLOCALEID=2052--物料类别select  itemg.FNUMBER,itemgl.FNAME ,(select t1.FNAME from T_BD_MATERIALGROUP_L t1 where t1.fid=itemglv.FGROUP1 )+'_'+ (select t1.FNAME from T_BD_MATERIALGROUP_L t1 where t1.fid=itemglv.FGROUP2 )+'_'+itemgl.FNAME from T_BD_MATERIALGROUP itemginner join T_BD_MATERIALGROUP_L itemglon itemg.fid=itemgl.FIDand itemgl.FLOCALEID=2052inner join T_BD_MATERIALGROUPLEVEL itemglvon itemg.FID=itemglv.FIDwhere itemglv.FLevel=3--物料查询select	case when itembas.FERPCLSID =1 then '外购' when itembas.FERPCLSID =2 then '自制' when itembas.FERPCLSID =3 then '委外' when itembas.FERPCLSID =4 then '特征' when itembas.FERPCLSID =5 then '虚拟' when itembas.FERPCLSID =6 then '服务' when itembas.FERPCLSID =7 then '一次性'when itembas.FERPCLSID =9 then '配置'when itembas.FERPCLSID =10 then '资产'when itembas.FERPCLSID =11 then '费用'when itembas.FERPCLSID =12 then '模型'when itembas.FERPCLSID =13 then '产品系列' end as "物料属性",ORG.FNAME,ITEM.FNUMBER as "物料编码",ITEMCAL.FNUMBER as "物料类别",  ITEML.FNAME as "物料名称",UOM.FNAME AS "单位",'有效' as "使用状态",'' as "用户物料类型",ITEML.FSPECIFICATION as "规格", item.F_PBQO_Text2 as "材质",item.FMnemonicCode as "助记码",ITEM.F_PBQO_TEXT3 AS "图号",item.FOldNumber as "旧物料编码",'' as "试装物料编码",'' as "正偏标" ,'' as "高度", '' as "波特率",'' as "刻度",'' as "主件类型",'' as "产品系列", '' as "产品名称",'' as "产品服务类型",item.F_PBQO_Text as "制造商", item.F_PBQO_Text1 as "制造商料号",ITEMSTK.FIsKFPeriod AS "是否启用保质期", ITEMSTK.FEXPPERIOD as "保质期",ITEMSTK.FIsBatchManage AS "是否启用批号",ITEMSTK.FIsSNManage AS "是否启用序列号"from T_BD_MATERIAL item inner join T_BD_MATERIAL_L itemlon item.FMATERIALID=iteml.FMATERIALIDAND ITEML.FLOCALEID=2052INNER JOIN T_ORG_ORGANIZATIONS_L ORGON ORG.FORGID=ITEM.FUSEORGIDAND ORG.FLOCALEID=2052INNER JOIN T_BD_MATERIALSTOCK ITEMSTKON ITEMSTK.FMATERIALID=ITEM.FMATERIALIDAND ITEMSTK.FUSEORGID=ITEM.FUSEORGIDINNER JOIN  T_BD_UNIT_L UOM ON UOM.FUNITID=ITEMSTK.FStoreUnitIDAND UOM.FLOCALEID=2052INNER JOIN T_BD_MATERIALGROUPLEVEL ITEMCALON ITEMCAL.FID=ITEM.FMATERIALGROUP inner join T_BD_MATERIALBASE itembason itembas.FMATERIALID=item.FMATERIALIDand itembas.FUSEORGID=item.FUSEORGIDwhere 1=1
--	and item.FNUMBER='03.07.ST.0160'--and (ITEMSTK.FIsKFPeriod+ITEMSTK.FIsBatchManage+ITEMSTK.FIsSNManage)=0AND ORG.FNAME='所托（杭州）汽车智能设备有限公司'--BOM查询select orgl.FNAME,item.FNUMBER,bom.FNUMBER,chid.FSEQ,item1.FNUMBER,chid.FNUMERATOR/chid.FDENOMINATOR qty,chid.FEFFECTDATE,chid.FEXPIREDATE  from T_ENG_BOM bom inner join T_ENG_BOMCHILD chidon bom.FID=chid.FID inner join T_BD_MATERIAL itemon item.FMATERIALID=bom.FMATERIALIDand item.FUSEORGID=bom.FUSEORGIDinner join T_ORG_ORGANIZATIONS_L orglon bom.FUSEORGID=orgl.FORGIDand orgl.FLOCALEID=2052inner join T_BD_MATERIAL item1on item1.FMATERIALID=chid.FMATERIALIDand item1.FUSEORGID=bom.FUSEORGIDwhere 1=1--item.FNUMBER='00.02.A2.0636'and bom.FFORBIDSTATUS='A'and bom.FDOCUMENTSTATUS='C'and bom.FAPPROVERID is not nullorder by bom.FID,chid.FSEQ;--辅助资料：工程项目
select base3.FNUMBER, base3l.FDATAVALUE  from T_BAS_ASSISTANTDATAENTRY base3
inner join T_BAS_ASSISTANTDATAENTRY_L base3l
on base3.FENTRYID=base3l.FENTRYID
and base3l.FLOCALEID=2052
and  base3.FID='643791db7c76db'--辅助资料：产品服务
select base3.FNUMBER, base3l.FDATAVALUE  from T_BAS_ASSISTANTDATAENTRY base3
inner join T_BAS_ASSISTANTDATAENTRY_L base3l
on base3.FENTRYID=base3l.FENTRYID
and base3l.FLOCALEID=2052
and  base3.FID='658fd6ef578485'--科目select t3.FNUMBER FCreateOrgId,
t3l.FNAME,
t.FNUMBER,
t1.FNAME,
t4.FNAME FDetailID,
t4.FFLEXNUMBER FDetailNo,
t.FISCONTACT as "往来科目",
t.FIsCash as "现金科目",
t.FISCASHFLOW as "现金等价物",
t.FISSHOWJOURNAL as "出日记账",
t.FDC as "余额方向",
t.FISDETAIL as "明细科目",
t.FIsAllocate as "期末调汇",
t.FUNMANUAL as "不允许手工录入"
From T_BD_ACCOUNT t
inner join T_BD_ACCOUNT_L t1
on t.FACCTID = t1.FACCTID
and t1.FLOCALEID = 2052
inner join T_ORG_ORGANIZATIONS t3
on t.FUSEORGID = t3.FORGID
inner join T_ORG_ORGANIZATIONS_L t3l
on t3l.FORGID=t3.FORGID
and t3l.FLOCALEID=2052
left join (select FACCTID, FFLEXNUMBER, FNAME
From T_BD_ACCOUNTFLEXENTRY TA
left join (SELECT t0.FID, FFLEXNUMBER, t0_L.FNAME
FROM T_BD_FLEXITEMPROPERTY t0
LEFT OUTER JOIN T_BD_FLEXITEMPROPERTY_L t0_L
ON (t0.FID = t0_L.FID AND t0_L.FLocaleId = 2052)) TAd
on TA.FFlEXITEMPROPERTYID = TAd.FID) T4
on t.FACCTID = t4.FACCTID
where t.FISDETAIL=1--核算维度
select* from T_BD_FLEXITEMPROPERTY 
--科目余额SELECT 
B.FACCOUNTID,
B.FDETAILID,
AB.FNUMBER         账簿编码,ABL.FNAME          账簿名称,B.FYEAR            年,B.FPERIOD          期,A.FNUMBER          科目编码,AL.FNAME           科目名称,CL.FNAME           币别,ORG.FNUMBER         组织机构编码,ORGL.FNAME           组织机构名称,S.FNUMBER          供应商编码,SL.FNAME           供应商名称,CUST.FNUMBER       客户编码,CUSTL.FNAME        客户名称,EMP.FNUMBER        员工编码,EMPL.FNAME         员工名称,DEPT.FNUMBER       部门编码,DEPTL.FNAME        部门名称,ITEM.FNUMBER       物料编码,ITEML.FNAME        物料名称,BASE.FNUMBER       项目编码,BASEL.FDATAVALUE   项目名称,BASE1.FNUMBER       产品服务编码,BASE1L.FDATAVALUE   产品服务名称,BASE2.FNUMBER       as "往来单位(股东)编码",BASE2L.FDATAVALUE   as "往来单位(股东)名称",BASE3.FNUMBER       税率编码,BASE3L.FDATAVALUE   税率名称,B.FBEGINBALANCEFOR 期初原币,B.FBEGINBALANCE    期初本位币,B.FDEBITFOR        借方原币,B.FDEBIT           借方本位币,B.FCREDITFOR       贷方原币,B.FCREDIT          贷方本位币,B.FYTDDEBITFOR     本年累计借方原币,B.FYTDDEBIT        本年累计借方本位币,B.FYTDCREDITFOR    本年累计贷方原币,B.FYTDCREDIT       本年累计贷方本位币,B.FENDBALANCEFOR   期末原币,B.FENDBALANCE      期末本位币FROM T_GL_BALANCE BJOIN T_BD_ACCOUNTBOOK ABON AB.FBOOKID = B.FACCOUNTBOOKIDLEFT JOIN T_BD_ACCOUNTBOOK_L ABLON ABL.FBOOKID = B.FACCOUNTBOOKIDAND ABL.FLOCALEID = 2052JOIN T_BD_ACCOUNT AON A.FACCTID = B.FACCOUNTIDLEFT JOIN T_BD_ACCOUNT_L ALON AL.FACCTID = B.FACCOUNTIDAND AL.FLOCALEID = 2052JOIN T_BD_CURRENCY CON C.FCURRENCYID = B.FCURRENCYIDLEFT JOIN T_BD_CURRENCY_L CLON CL.FCURRENCYID = B.FCURRENCYIDAND CL.FLOCALEID = 2052JOIN T_BD_FLEXITEMDETAILV DVON DV.FID = B.FDETAILID --核算维度组合IDLEFT JOIN T_BD_SUPPLIER SON S.FSUPPLIERID = DV.FFLEX4 --供应商维度，注意关联用到的表名及两边的关联字段LEFT JOIN T_BD_SUPPLIER_L SLON SL.FSUPPLIERID = S.FSUPPLIERIDAND SL.FLOCALEID = 2052LEFT JOIN T_HR_EMPINFO EMPON EMP.FID=DV.FFLEX7LEFT JOIN T_HR_EMPINFO_L EMPLON EMP.FID=EMPL.FIDAND EMPL.FLOCALEID=2052LEFT JOIN T_BD_DEPARTMENT DEPTON DEPT.FDEPTID=DV.FFLEX5LEFT JOIN T_BD_DEPARTMENT_L DEPTLON DEPT.FDEPTID=DEPTL.FDEPTIDAND DEPTL.FLOCALEID=2052LEFT JOIN T_BD_CUSTOMER custon cust.FCUSTID=DV.FFLEX6LEFT JOIN T_BD_CUSTOMER_L CUSTLON CUSTL.FCUSTID=CUST.FCUSTIDAND CUSTL.FLOCALEID=2052LEFT JOIN T_BD_MATERIAL ITEMON ITEM.FMATERIALID=DV.FFLEX8LEFT JOIN T_BD_MATERIAL_L ITEMLON ITEM.FMATERIALID=ITEML.FMATERIALIDAND ITEML.FLOCALEID=2052LEFT JOIN T_ORG_ORGANIZATIONS ORGON ORG.FORGID=DV.FFLEX11LEFT JOIN T_ORG_ORGANIZATIONS_L ORGLON ORGL.FORGID=ORG.FORGIDAND ORGL.FLOCALEID=2052LEFT JOIN T_BAS_ASSISTANTDATAENTRY baseON base.FENTRYID=DV.FF100002
left join T_BAS_ASSISTANTDATAENTRY_L basel
on base.FENTRYID=basel.FENTRYID
and basel.FLOCALEID=2052
LEFT JOIN T_BAS_ASSISTANTDATAENTRY base1ON base1.FENTRYID=DV.FF100003
left join T_BAS_ASSISTANTDATAENTRY_L base1l
on base1.FENTRYID=base1l.FENTRYID
and base1l.FLOCALEID=2052
LEFT JOIN T_BAS_ASSISTANTDATAENTRY base2ON base2.FENTRYID=DV.FF100004
left join T_BAS_ASSISTANTDATAENTRY_L base2l
on base2.FENTRYID=base2l.FENTRYID
and base2l.FLOCALEID=2052
LEFT JOIN T_BAS_ASSISTANTDATAENTRY base3ON base3.FENTRYID=DV.FF100005
left join T_BAS_ASSISTANTDATAENTRY_L base3l
on base3.FENTRYID=base3l.FENTRYID
and base3l.FLOCALEID=2052WHERE 1=1--AND AB.FNUMBER ='009' --'006'AND A.FNUMBER = '2204.01.02.01'AND CL.FNAME = '人民币'--AND b.FYEAR=2022--AND b.FPERIOD=5AND BASE3.FNUMBER <> ''-- AND BASE.FNUMBER='RD000009999'ORDER BY AB.FNUMBER,A.FNUMBER,CL.FNAME --未转采购订单的采购申请select org.FNAME,req.FBILLNO 单号,reql.FSEQ 行号,(select per.FNAMEfrom t_bd_staff stafinner join T_BD_PERSON_L peron per.FPERSONID = staf.FPERSONIDwhere staf.FSTAFFID = req.FAPPLICANTID) 申请人,(select dept.FNAMEfrom T_BD_DEPARTMENT_L DEPTWHERE DEPT.FDEPTID = req.FAPPLICATIONDEPTIDAND dept.FLOCALEID = 2052) 部门名称,(select dept.FNUMBERfrom T_BD_DEPARTMENT DEPTWHERE DEPT.FDEPTID = req.FAPPLICATIONDEPTID) 编码编码,org1.FNAME 收料组织,item.FNUMBER,iteml.FNAME 物料名称,iteml.FSPECIFICATION 规格,uom.FNAME,reql.FREQQTY as 采购申请数量,reql.FAPPROVEQTY - isnull((select sum(isnull(pol.FBASEUNITQTY, 0))from T_PUR_POORDERENTRY_LK polwhere pol.FSTABLENAME = 'T_PUR_ReqEntry'and pol.FSID = reql.FENTRYID),0) 剩余数量,reql.FTAXPRICE / (1 + isnull(reql.FTAXRATE, 0) / 100) 不含稅单价,reql.FTAXPRICE 含税单价,reql.FTAXRATE 税率,reql.FREQAMOUNT 含税金额,reql.FARRIVALDATE 到货日期from T_PUR_REQUISITION reqinner join T_PUR_REQENTRY reqlon req.FID = reql.FIDinner join T_BD_MATERIAL itemon reql.FMATERIALID = item.FMATERIALIDinner join T_BD_MATERIAL_L itemlon reql.FMATERIALID = iteml.FMATERIALIDAND ITEML.FLOCALEID = 2052inner join T_PUR_REQENTRY_S reqson reqs.FENTRYID = reql.FENTRYIDINNER JOIN T_ORG_ORGANIZATIONS_L ORGON ORG.FORGID = reqs.FPURCHASEORGIDAND ORG.FLOCALEID = 2052INNER JOIN T_ORG_ORGANIZATIONS_L ORG1ON ORG1.FORGID = reqs.FRECEIVEORGIDAND ORG1.FLOCALEID = 2052INNER JOIN T_BD_UNIT_L UOMON UOM.FUNITID = reql.FUNITIDAND UOM.FLOCALEID = 2052where req.FAPPROVEDATE is not nulland req.FCLOSESTATUS <> 'B'and reql.FAPPROVEQTY - isnull((select sum(isnull(pol.FBASEUNITQTY, 0))from T_PUR_POORDERENTRY_LK polwhere pol.FSTABLENAME = 'T_PUR_ReqEntry'and pol.FSID = reql.FENTRYID),0) > 0and reql.FMRPCLOSESTATUS <> 'B'and req.FBILLNO = '1005PR240820001'order by req.FBILLNO, reql.FSEQ;--采购订单未完结的：待接收的
select org.FNAME 采购组织,po.FBILLNO 单据编码,(select personl.FNAMEfrom V_BD_BUYER buyinner join t_bd_staff stfon stf.FSTAFFID = buy.FSTAFFIDinner join T_BD_PERSON_l personlon personl.FPERSONID = stf.FPERSONIDand personl.FLOCALEID = 2052where buy.fid = po.FPURCHASERID) 采购员,vend.FNUMBER 供应商编码,vendl.FNAME 供应商名称,po.F_URUL_Text1 订单编码,po.F_URUL_Text2 合同编码,org1.FNAME 收货组织,pol.FSEQ 行号,item.FNUMBER,iteml.FNAME 物料名称,iteml.FSPECIFICATION 规格,uom.FNAME,pol.FQTY 采购数量,,isnull((select sum(isnull(lk.FBASEUNITQTY, 0))from T_PUR_RECEIVEENTRY_LK lkwhere lk.FSTABLENAME = 'T_PUR_POORDERENTRY'and lk.FSID = pol.FENTRYID),0) - isnull((select sum(mrbl.FRMREALQTY)from T_PUR_MRBENTRY mrblinner join T_PUR_MRBENTRY_LK mlkon mlk.FSTABLENAME = 'T_STK_INSTOCKENTRY'and mlk.FENTRYID = mrbl.FENTRYIDand mrbl.FPOORDERENTRYID = pol.FENTRYID),0) 已接收数量,/*退货数量：(select sum(mrbl.FRMREALQTY)from T_PUR_MRBENTRY mrblinner join T_PUR_MRBENTRY_LK mlkon mlk.FSTABLENAME = 'T_STK_INSTOCKENTRY'and mlk.FENTRYID = mrbl.FENTRYIDand mrbl.FPOORDERENTRYID = pol.FENTRYID)  */pol.FQTY -(isnull((select sum(isnull(lk.FBASEUNITQTY, 0))from T_PUR_RECEIVEENTRY_LK lkwhere lk.FSTABLENAME = 'T_PUR_POORDERENTRY'and lk.FSID = pol.FENTRYID),0) - isnull((select sum(mrbl.FRMREALQTY)from T_PUR_MRBENTRY mrblinner join T_PUR_MRBENTRY_LK mlkon mlk.FSTABLENAME = 'T_STK_INSTOCKENTRY'and mlk.FENTRYID = mrbl.FENTRYIDand mrbl.FPOORDERENTRYID = pol.FENTRYID),0)) 未接收数量,polf.FPRICE 不含税单价,polf.FTAXPRICE 含税单价,polf.FTAXRATE 税率from T_PUR_POORDER poinner join T_PUR_POORDERENTRY polon po.FID = pol.FIDinner join T_PUR_POORDERENTRY_F polfon polf.FENTRYID = pol.FENTRYIDinner join T_BD_MATERIAL itemon pol.FMATERIALID = item.FMATERIALIDinner join T_BD_MATERIAL_L itemlon pol.FMATERIALID = iteml.FMATERIALIDAND ITEML.FLOCALEID = 2052INNER JOIN T_BD_UNIT_L UOMON UOM.FUNITID = pol.FUNITIDAND UOM.FLOCALEID = 2052INNER JOIN T_ORG_ORGANIZATIONS_L ORGON ORG.FORGID = po.FPURCHASEORGIDAND ORG.FLOCALEID = 2052inner join T_BD_SUPPLIER vendon vend.FSUPPLIERID = po.FSUPPLIERIDinner join T_BD_SUPPLIER_L vendlon vendl.FSUPPLIERID = vend.FSUPPLIERIDand vendl.FLOCALEID = 2052inner join T_PUR_POENTRYDELIPLAN pllaon plla.FENTRYID = pol.FENTRYIDinner join T_PUR_POORDERENTRY_D poldon pold.FENTRYID = pol.FENTRYIDinner JOIN T_ORG_ORGANIZATIONS_L ORG1ON ORG1.FORGID = pold.FRECEIVEORGIDAND ORG1.FLOCALEID = 2052where 1 = 1and po.FAPPROVEDATE is not nulland po.FCLOSESTATUS <> 'B' --B：单据头关闭and pol.FMRPCLOSESTATUS <> 'B' --B: 行业务关闭and po.FCANCELDATE is nulland po.FBUSINESSTYPE <> 'WW'
--and po.FBILLNO='1002-1002-2103-01'order by po.FBILLNO, pol.FSEQ;--暂估应付： 未开票selectorg.FNAME 公司,ap.FBILLNO 暂估应付单,po.FBILLNO 采购订单,(select personl.FNAMEfrom V_BD_BUYER buyinner join t_bd_staff stfon stf.FSTAFFID = buy.FSTAFFIDinner join T_BD_PERSON_l personlon personl.FPERSONID = stf.FPERSONIDand personl.FLOCALEID = 2052where buy.fid = po.FPURCHASERID) 采购员,vend.FNUMBER 供应商编码,vendl.FNAME 供应商名称,po.F_URUL_Text1 订单编码1,po.F_URUL_Text2 合同编码,cry.FCODE 币别,ap.FDATE 日期,pol.FSEQ 订单行号,apl.FSEQ 暂估应付行号,item.FNUMBER,iteml.FNAME 物料名称,iteml.FSPECIFICATION 规格,uom.FNAME,apl.FPRICEQTY 数量,apl.FTAXPRICE 含税单价,apl.FENTRYTAXRATE 税率,apl.FTAXAMOUNTFOR 税额,apl.FNOTAXAMOUNTFOR 不含税金额,apl.FTAXAMOUNT 含税金额,apl.FBUYIVQTY 开票数量,apl.FPAYMENTAMOUNT 已结算金额,apl.FNOINVOICEQTY 未开票核销数量,apl.FNOINVOICEAMOUNT 未开票核销金额,pol.FQTY 采购订单数量,isnull((select sum(isnull(lk.FBASEUNITQTY, 0))from T_PUR_RECEIVEENTRY_LK lkwhere lk.FSTABLENAME = 'T_PUR_POORDERENTRY'and lk.FSID = pol.FENTRYID),0) - isnull((select sum(mrbl.FRMREALQTY)from T_PUR_MRBENTRY mrblinner join T_PUR_MRBENTRY_LK mlkon mlk.FSTABLENAME = 'T_STK_INSTOCKENTRY'and mlk.FENTRYID = mrbl.FENTRYIDand mrbl.FPOORDERENTRYID = pol.FENTRYID),0) 已接收数量,po.FBUSINESSTYPE 业务类型from T_AP_PAYABLE apinner join T_AP_PAYABLEENTRY aplon ap.FID = apl.FIDinner join T_BD_MATERIAL itemon apl.FMATERIALID = item.FMATERIALIDinner join T_BD_MATERIAL_L itemlon apl.FMATERIALID = iteml.FMATERIALIDAND ITEML.FLOCALEID = 2052INNER JOIN T_BD_UNIT_L UOMON UOM.FUNITID = apl.FPRICEUNITIDAND UOM.FLOCALEID = 2052INNER JOIN T_ORG_ORGANIZATIONS_L ORGON ORG.FORGID = ap.FSETTLEORGIDAND ORG.FLOCALEID = 2052inner join T_BD_SUPPLIER vendon vend.FSUPPLIERID = ap.FSUPPLIERIDinner join T_BD_SUPPLIER_L vendlon vendl.FSUPPLIERID = ap.FSUPPLIERIDand vendl.FLOCALEID = 2052inner join T_BD_CURRENCY cryon cry.FCURRENCYID = ap.FCURRENCYIDinner join T_PUR_POORDERENTRY polon pol.FENTRYID = apl.FOrderEntryIDinner join T_PUR_POORDER poon pol.FID = po.FIDwhere 1 = 1--and ap.FBILLNO='AP00011657'and ap.FAPPROVEDATE is not nulland ap.FCANCELDATE is nulland apl.FPRICEQTY <> apl.FBUYIVQTYand ap.FSetAccountType = 2 -- 立账类型：2应付暂估and FByVerify = 0  --FByVerify：生成方式 2：系统自动冲回暂估 1：转销应付自动生成 0：正常应付暂估order by ap.FBILLNO,apl.FSEQ;