用友U8+16.0存货档案查询sql语句

以下代码为自己根据工作需要所写，记录一下以免遗忘，通过实际验证。有需要的朋友可以根据实际情况修改使用，使用前请备份好数据库，数据无价！免责声明：以下代码仅用于交流学习，因使用以下代码造成数据丢失的概不负责！

select 
--以下为存货档案的“基本”页签内容：
a.cInvCCode as 存货大类编码,c.cInvCName as 存货大类名称,a.cInvCode as 存货编码,a.cInvName as 存货名称,a.cInvStd as 规格型号,a.cInvDefine4 as 材质,

--以下这段不需要时可以注释掉
/*a.cenglishname as 英文名,a.cinvaddcode as 存货代码,a.cinvmnemcode as 助记码,a.ccurrencyname as 通用名称,
a.cgroupcode as 计量单位组编码,d.cGroupName as 计量单位组名称,d.igrouptype as 计量单位组类别,a.ccomunitcode as 主计量单位编码,e.cComUnitName as 计量单位名称, a.cPUComUnitCode as 采购默认计量单位编码,
a.csacomunitcode as 销售默认计量单位编码,a.cproductunit as 生产计量单位,a.cstcomunitcode as 库存默认计量单位编码,a.ccacomunitcode as 成本默认计量单位编码,a.cshopunit as 零售计量单位,
a.cciqcode as 海关编码,f.cciqunitcode as 海关计量单位编码,b.fInvCIQExch as 海关单位换算率,a.cProduceNation as 生产国别,a.cEnterprise as 生产企业,a.cProduceAddress as 生产地点,a.cAddress as 产地,*/

--以下为存货档案的“基本”页签中的“存货属性”内容：
a.bSale as 是否内销,a.bPurchase as 是否采购,a.bSelf as 是否自制,a.bproxyforeign as 是否委外,a.bcomsume as 是否生产耗用,a.bservice as 是否应税劳务,

--以下这段不需要时可以注释掉
/*b.bimport as 是否进口,a.bexpsale as 是否外销,a.binventrust as 是否受托代销,a.bplaninv as 是否计划品,a.bproducing as 是否在制,
a.bPTOmodel as 是否PTO模型,a.batomodel as 是否ATO模型,a.binvmodel as 是否模型,b.binvasset as 是否资产,b.bPrjMat as 工程物料,a.bpiece as 计件,a.bsrvitem as 服务项目,a.bsrvfittings as 服务配件,
b.bsrvproduct as 服务产品,a.binvtype as 是否折扣,a.bservice as 应税劳务,b.bbondedinv as 是否保税品,a.baccessary as 是否成套件,a.bcheckitem as 是否选项类,b.bsuitretail as 是否适用零售,*/

--以下为存货档案的“控制”页签内容：
--以下这段不需要时可以注释掉
/*a.cpurpersoncode as 采购员,a.cinvdepcode as 生产部门,a.cPosition as 货位编码,a.cinvpersoncode as 计划员,*/

a.cdefwarehouse as 默认仓库,
a.isupplytype as 供应类型,--0领用，1入库倒冲，2工序倒冲，3虚拟件，4直接供应
b.idrawtype as 领料方式,--0直接领料，1申请领料
b.fbuyexcess as 请购超额上限,a.forderuplimit as 订货超额上限,b.fprjmatlimit as 采购数量上限,a.finexcess as 入库超额上限,b.fInvOutUpLimit as 发货允超上限,a.foutexcess as 出库超额上限,

--以下这段不需要时可以注释掉
/*a.breceiptbydt as 来料须依据检验结果入库,b.binbyprocheck as 产品须依据检验结果入库,a.isafenum as 安全库存,a.itopsum as 最高库存,a.ilowsum as 最低库存,a.binvoverstock as 是否呆滞积压,
a.ioverstock as 积压标准,b.imaterialscycle as 用料周期,a.cinvabc as ABC分类,a.idrawbatch as 领料批量,a.fminsplit as 最小分割量,a.bkccutmantissa as 领料是否切除尾数,a.ifrequency as 盘点周期,
a.dlastdate as 上次盘点日期,a.iwastage as 合理损耗率,a.cfrequency as 盘点周期单位,g.idays as 每第几天,a.binvbatch as 是否批次管理,a.btrack as 是否出库跟踪入库,b.binvrohs as ROHS物料,
a.bmngoldpart as 管理旧件,a.ioldpartmngrule as 旧件管理规则,a.binvquality as 是否保质期管理,a.imassdate as 保质期天数,a.cmassunit as 保质期单位,b.iexpiratdatecalcu as 有效期推算方式,
b.iwarrantyperiod as 保修期限,b.iwarrantyunit as 保修期单位,a.iwarndays as 保质期预警天数,a.bbarcode as 是否条形码管理,a.cbarcode as 对应条形码,a.bserial as 是否有序列号管理,
b.bpuquota as 参与配额,b.inearrejectdays as 失效期临近拒收天数,a.bsolitude as 单独存放,a.bcheckbsatp as 检查售前ATP,a.cinvprojectcode as 售前ATP方案,*/

--以下为存货档案的“价格成本”页签内容：
a.cvaluetype as 计价方式,--计划价法，全月平均法，移动平均法，先进先出法，个别计价法，后进先出法

--以下这段不需要时可以注释掉
/*a.bcheckbatch as 批次核算,a.iinvrcost as 计划价或售价,a.iimptaxrate as 进项税率,a.iExpTaxRate as 销项税率,a.iinvsprice as 参考成本,a.iinvncost as 最新成本,a.iinvscost as 参考售价,a.iinvlscost as 最低售价,
a.iinvmpcost as 最高进价,a.iinvsalecost as 零售单价,a.iexpsalerate as 销售加成率,a.fexpensesexch as 费用率,b.fmaterialcost as 标准材料费用,b.fcurllaborcost as 本阶标准人工费用,
b.fcurlvarmanucost as 本阶标准变动制造费用,b.fcurlfixmanucost as 本阶标准固定制造费用,b.fcurlomcost as 本阶标准委外加工费,b.fnextllaborcost as 前阶标准人工费用,
b.fnextlvarmanucost as 前阶标准变动制造费用,b.fnextlfixmanucost as 前阶标准固定制造费用,b.fnextlomcost as 前阶标准委外加工费,b.bsckeyprojections as 投产推算关键子件,*/

--以下为存货档案“计划”页签内容：
a.iplandefault as 计划默认属性,--1自制，2委外，3采购
a.bbommain as 允许BOM母件,a.bBomSub as 允许BOM子件,a.bProductBill as 允许生产订单,b.bInvKeyPart as 是否关键物料,a.bintotalcost as 成本相关,
a.cPlanMethod as 计划方法,--R，N，L。R表示此存货要列入MRP/MPS计算的对象，编制MPS/MRP计划；
--N表示该存货及其以下子件都不计算需求，不列入MRP/MPS展开。如量少价低、可随时取得的物料，可采用再订购点或其它方式计划其供应。
--L表示该存货采用LRP计划方法。

a.cSRPolicy as 供需政策,--注意！！！对应存货在'现存量'表中有记录则不允许'LP、PE'转换。
--PE（Period）：表示期间供应法。
--LP（Lot Pegging）：表示批量供应法，按各时间的净需求分别各自供应。所有净需求都不合并，按销售订单不同各自生成计划订单。此方式可使供需对应关系明朗化，库存较低，但供应批量可能偏低，未达经济规模。

--以下这段不需要时可以注释掉
/*a.bmps as MPS件,a.bRePlan as 是否重复计划,a.bforeexpland as 预测展开,a.creplaceitem as 替换件,a.dreplacedate as 替换日期,b.ibomexpandunittype as BOM展开单位,a.bplaninv as 是否计划品,
a.fConvertRate as 转换因子,a.iinvtfid as 需求时栅,a.iplantfday as 计划时栅天数,a.ioverlapday as 重叠天数,b.isupplyperiodtype as 供应期间类型,a.isupplyday as 供应期间,b.iTimeBucketId as 时格代号,
b.iavailabilitydate as 可用日期,a.iInvAdvance as 固定提前期,a.iAdvanceDate as 累计提前期,a.iAlterAdvance as 变动提前期,a.iInvBatch as 固定供应量,a.fAlterBaseNum as 变动基数,
a.fSupplyMulti as 供应倍数,a.fMaxSupply as 最高供应量,a.fMinSupply as 最低供应量,a.fVagQuantity as 日均耗量,b.iAcceptEarlyDays as 允许提前天数,b.iAcceptDelayDays as 允许延后天数,
a.iPFBatchQty as 流转卡批量,a.bKCCutMantissa as 领料是否切除尾数,b.fRoundFactor as 舍入因子,a.iAllocatePrintDgt as 子件用料打印精度,b.iRequireTrackStyle as 需求跟踪方式,
b.bConsiderFreeStock as 是否考虑自由库存,a.bTrackSaleBill as 销售跟单,a.bBillUnite as 令单合并,b.iSurenessType as 安全库存方法,b.iDynamicSurenessType as 动态安全库存方法,
b.iBestrowSum as 覆盖天数,b.iPercentumSum as 百分比,b.iDateType as 期间类型,b.iDateSum as 期间数,a.bROP as ROP件,a.iROPMethod as 再订货点方法,a.iBatchRule as ROP批量规则,
a.iAssureProvideDays as 保证供应天数,a.fSubscribePoint as 再订货点,a.iCheckATP as 检查ATP,a.iInvATPId as ATP规则,*/

--以下为存货档案“其它”页签的内容：
--以下这段不需要时可以注释掉
/*a.cPackingType as 包装规格,a.cFile as 批准文号,a.cCheckOut as 合格证号,a.cLabel as 注册商标,a.cEnterNo as 入关证号,a.cLicence as 许可证号,a.cCommodity as 注册商品批件,a.cNotPatentName as 国际非专利名,
a.cQuality as 质量要求,a.cEngineerFigNo as 工程图号,a.cWGroupCode as 重量计量组,a.cWUnit as 重量单位,a.iInvWeight as 净重,a.fGrossW as 毛重,a.cVGroupCode as 体积计量组,a.cVUnit as 体积单位,
a.fLength as 长,a.fWidth as 宽,a.fHeight as 高,a.iVolume as 单位体积,*/

a.dSDate as 启用日期,a.dEDate as 停用日期,a.cCreatePerson as 建档人,b.dInvCreateDatetime as 建档日期,a.cModifyPerson as 变更人,a.dModifyDate as 变更日期,

--以下这段不需要时可以注释掉
/*a.iId as 所属权限组,b.cInvAppDocNo as 变更申请编号,*/

--以下为存货档案“自定义项”页签的内容：
a.cInvDefine1 as 货位,a.cInvDefine2 as 辅单位,a.cInvDefine3 as 转化率,a.cInvDefine4 as 材质,a.cInvDefine5 as 图号,a.cInvDefine7 as 其他,a.cInvDefine8 as 开票名称,a.cInvDefine9    as 是否存在BOM,
a.cInvDefine10 as 是否存在工艺路线    

from inventory a 
left join inventory_sub b on a.cinvcode = b.cinvsubcode
left join inventoryclass c on a.cinvccode = c.cinvccode
left join ComputationGroup d on a.cGroupCode = d.cGroupCode 
left join ComputationUnit e on a.ccomunitcode = e.ccomunitcode
left join ex_ciqinv f on a.cciqcode = f.cciqcode
left join Warehouse g on a.cdefwarehouse = g.cwhcode