金蝶SQL二次开发

1、二次开发报表项目美合源二次开发之生产指令单目的：在计件工资管理系统中，为成本预算人员提供直接报表。避免从工序定额计件入库单导出后,再使用EXCEL做繁琐的筛选和计算处理 。实现：通过K3系统的查询分析器，编写直接SQL数据报表-生产指令单。内容：select a . frp 序号，a.fnumber 物料长代码，a.fname 规格物料名称，a.f_108 用量，a.f_102 工令号，a.f_110 流水线编号，-substring(b.fsourcebillno,1,6)订单号,b.fentryselfa0153订单号，b.fentryselfb0166工段，(b. fqty / a. f

2、_108 )订单套数，b . fprice 单价，b.fqty 总数量，b.fentryselfa9738作业人员，a . fomortize完工数量,b.fentryselfb0167完工日期，a . fomortize完工数量,b.fentryselfa9738作业人员，b.fentryselfb0167完工日期FROM t_icitem aRIGHT joinicstockb川entry bON a . fitemid =b. fitemidWHERE fnumber between '*ItemNo*'AND '#ItemNo#'效果图：通过仓储管理系统

3、一查询分析一一查询分析工具一生产指令单图（一）输入物料长代码，如有多个物料长代码，按照物料表的先后顺序写入图（二）生产指令单详细表内容：生产工艺和流水线编号目的：在下达生产指令单的时候，解决K3系统不能对生产实际需要的工艺安排进行流水线标号识别,避免工艺组同成本预算组之间的多次沟通。实现：通过对工令号的维护，在维护工令号时候，针对物料所需要的工段对应的流水线编号进行维护。内容：基础资料一物料，添加流水线编号字段。工序定额计件入库单添加流水线编号。效果图：新增或者修改时候对流水线编号进行选择。鹏目博性书费呼仁I4本第-1韧ifl麦柯I计M附，审计先科I K« I县改蝴I迎阳口出料IS1

4、片I :密码 I（一）输入工序编码时候自动带出工段和流水线编号采购申请单时序薄目的：MRP生成的采购申请单，需要打印给相关部门。而系统中导出的报表需要再次处理。通过BOS采购申请单时序薄，可直接导出打印。实现：通过BOS）勺直接数据报表，对采购申请单单据体的内容筛选，提炼出需要的数据。内容：效果图selectT2.fsourcebillno 订单号，TI.Fname物料名称，TI.Fmodel规格型号，Tl.fNUmber物料代码,T2.fentryselfp0127工令号，t5.fname 单位，t2.fcommitqty数量,t2.ffetchtime 到货日期，T2.fentryself

5、p0128 备注From t_icitem T1Right join porequestentry T2On T2.Fitemid =T1.FitemidRight joint_measureunit t5Ont5.fmeasureunitid =t2.funitidRight joinporequest t3On t2.finterid=t3.finteridLeft join t_user t4Ont4.fuserid =t3.fbillerid'ANDWheret2.fsourcebillno between ' *t2.fsourcebillno *'and &

6、#39; #t2.fsourcebillno#t4.fname ='BillerName'采购系统一查询分析器图（一）点击采购申请单时序薄图（二）输入订单号码一点击确定图（三）点击文件一引出内部数据|卜已；购广民*慎欧KJ#庭 I巅后中 附存鼻F* <XI"5婚s启I-fll =c|i*(E)X*I2> Si=l)M的呼判专自第小fl的匚皿钳米购上将篁枭妹负5：向 打印出H俎IT '的咨订1_ 1出内一腰第®止1HhT片马.niulmm周量近拈_. -1H a;如夙格血弓工H单位SLjIBIB。目中R由;小F1EIAE由“I”)1 M D

7、IO WL?ftIOT阿片M咂“LUU鞭淖鼻料2 U4 UIU 的壮Enfl幽 JlS-lfr-ZSFIMlJ-2盅暗Ffr+WI2SW04 任，4043235苴JWaIQ-Jfr-JSFIOTI5-3由讨不曜3 E TOI 399块W 2UI0-10-J5FltMlS-i铜也第耳0组MLfcMb品T”2 CrSJjOL.OSL?ffiSM柒演 2flifl-lG-2SFIWL3-E遇明相记看囱面工网上我6j ffi JML JHK由W ：fl|i-J0-2SntDLS-23Oh-(IB'：1D4 DID mi73Q5LUftIO JDI0-10-2SFiCMsjX五本央第M衍性，时空

8、:MML.EKTa0K-12十i(M SJIQ-i0-25HEIR“随氐图*M*5 5-J 01 Kfl COK(SEK牛M ZOIOB IO"?S”tug3 U4 UIU Ulf#1 怎 2til-ll-34FIOdLX：+«W*>5-«3.Q1.WXE潞:月-TMaiQ-JI-25fiwij-5皿：打117，帕卸L5-3./魏一卸/i-油4 M «K 道I3WS7rrX WI»- |-|FHlAILi-2liSSSTt H* 鼻仙目"T f &M L 曜3曲侬.於但b之n5笈hO-il喳FIML3-S熠蓄中芝1*3S

9、E-li'ZTE-J.-S'T-SZB5 (Ht WE.D,KH22”-Tsi aiia-u-iEniDL3-2227T中虹可加3LB-T/WIT-T-IJB3 H DD2 H377山上l430 ZDia-11-11!Fi£jCil>2脸巴中木用432tTM蜡L-处胃&岂巾工阻皿隐闰钓i1中NMIS-21ft尚甘做护的! WN ；（则纪W III UE73S7中IE0 SJIfl-JI-IEriffle避F?鞋事口堂LlXMLML 仲 40Lfl III U3Q1 刘 2tlll>l-l£PiDdiX鹏修坤梅归:LDft«L0&

10、#187;L?4»*：i3(H. jlL.DKKXSZ?中1加州lO-IITVriroiM皿制中野r"r-57715-1/4-3-1 .'11 W ros K75-Tr iuurn-irFltMLX侬四限maMTifgATJB3 LB.ffl2.Krreoe±s牛3a2AiJQ-li-l£FJMLS-2Nt击中驯抽!33LE-3j,B*2f-IffilJEEJBLJSSM464 >10-JI-IErims. 3-2傩求看第L 皿 LDDK4DM01 oa 111 EETZissza4izo ZDia-ii-iz比帕修理出LDO*LDO*?i

11、lHfl33JiL值m0C223中加划lail£nwk>z帽承11福4M 叫，ESMSn”。W WE D0版中M SDIKbll-IEFILULS-3HA申虾后7附比配gmQEn中葡 Zfil-1l-l£FIMlJ-2喑样 llEGliJLSMKMagOd 09.1iL.DdIB32STJ£Q WIO-JI-LEnwii-5二1H璀戏做11a.LWFl W*IWHQ4 W III 两口K7W-T】即加isilifFltflLS-i显阳通修炉，ei-ij'&KtJ-i/ua-r/fi2瓯侬.整的独UH i-吐PIML3-21H整坤法ML3M 匚

12、CM:(HJIIJE2L32MTw ana-ji-ie：1r IOJL3-2耶单埠餐炉由LHMLDMnDMC'3 oa in omc4BO ZDia-11-llFiiMlX陋，1|布站375-"的即T再侬!即工闻期.询OK-54十3d 2010-11-iErjD0L3 a工心描做IfLWfL：O"K"W(M III DEL43l_50 2DLO-31-IE1Ml=gMSJHi用工司| 1 年 IUI1 IHKT才拜郎小 出 e J 口图:S3* 事节华斤1神卬* T| ,EI3 1 JS1FEK|nnwj _d Ilcf4ec-.c|J®右增才

13、5：LE图（四）库存查询状态表查 询 条 件物料来源销售订单生产任务单物料物 料物 料规格默 认销售订单购 货销 售单出库生产任务工令生 产计 划计划生产计划完工代码代名型仓号单数位数单号号车数日期日期码称号库位量量间量销售订单生产任务单select b . fnumber物料代码,b . fname 物料名称，b . fmodel规格型号，b . fsecinv安全库存,c . fname 仓库名称，a . fbillno生产任务单号，substring ( b. fname ,1,5) 工令号, a . fplancommitdate计划开工时间，a . fqty计划数量，a . fpla

14、nfinishdate计划完工时间，d . fbillno销售订单号，h . fname 购货单位， f . fqty订货数量，-m.fname单位,f . fqtymust 假设出库数量from icmo aright join t_icitem b on a . fitemid =b. fitemidleft join t_stock c为什么是空值呢?on b . FDefaultLoc =c. fitemid -right join t_unitgroup m - -on a.funitid=m.funitgroupidright join seorder don d .finteri

15、d=a. forderinteridright join t_organization hon d . fcustid =h. fitemidrightjoin icstockbillentry fon b . fitemid =f. fitemid where b . fnumber like '%3.%select a . fnumber物料代码,a . fname 物料名称a . fmodel规格型号，a . fsecinv 安全库存from icmo bright join t_icitem aon a . fitemid =b. fitemid -找到物料代码，名称，规格，安

16、全库存。select a . fname 单位,* from t_unitgroup aleft join icmo bon a . funitgroupid =b. funitid - 得出单位。on d . fcustid =f. fitemid -订单号码，和购货单位select fcommitqty 出库数量,fqty 订货数量,finterid , * fromseorderentryorder by fitemidselect a . fitemid , a. fqty数量，a. fqtymust 出货数量,* fromicstockb川entry aright join t_ic

17、item bon b . fitemid =a. fitemid - 出货数量select B . fbillno销售订单号,* from icmo aright join seorder bon b . finterid =a. forderinterid-得出销售订单号select * from seorder盘点报表select t1.fnumber物料代码,t1.fname物料名称t1.fmodel规格型号，t3.fname仓库名称，t2.fqty结存数量t4.fname单位,t2.fbatchno备注 from icinventory t2right join t_icitem t1

18、on t1.fitemid=t2.fitemidright join t_stock t3on t3.fitemid=t2.fstockidright join t_measureunit t4on t1.funitgroupid=t4.funitgroupidwhere t1.fnumber between '*ItemNo*' AND '#ItemNo#' AND t2.fqty<>'0目的：每月盘点时候，能迅速通过物料找到库存不为0'的所有物料，直接打印或者导出实现：通过数据查询分析器，进行 SQL语句编写效果图：第一：点击仓库

19、管理-数据查tU分析-查询分析工具第二：选择打开，输入需要查找的物料代码上.叼刈E3主控台于功盅明的功忠一 1HUW - ffa1 KI 一 蒲见 一 - - MS4I 供题. 攀赞委畀加工暴均管理津港善理皿自存管理守为低鼻值量管理枷机收碘总若司立示版用益版打工望海机帷硒总司演示独修手 Jr fjaos 0.4K/5叫. . I底,四引力 MB1B口二"函明o皿叁或工口I因£13cl.Tn：xsura d中由|省二i研 一 | 口才 薨点击确定。需要查找的物料可自动生成,奈嫉 克怦帆JSff (£)谓的®*A中2 次；. 气漕般人命记岛-IJI X|专国

20、寻岛A W -口胤退出W利斯打印HIE字悻页面格度Effi 主提告疆启推,总楹点报表eA起如例*中网 1KJ=W加代码2判明代，班1古群现将型号6X君静给铐珏单位.at.铝孽45, |】传W自凯同5帕幽L 口 0t»l ffifl !=* J村曰管机枪5EB：件L 03 0005相板15* 5材网世机后的蹶LM.OmWW 4M 弓，闰电ItM的件I g HBIW 45# 42材资0机忸乳0昨I 03.100Bq靠假索阳禽机尼200件l C3 1003明聚包*50村耳信机用IDD幽1 03 1003刑!l! 45*50材朋心:机后m件L 口 1JU刊击号4国目机M第牛!则J8-位露岩史

21、材料宜机忸8%L. GL 15- P【三息足豺盟自拉布30金L UL. (n- =saSE?材到百机性R朝！t 01 100-刊兴总史材解包机后皿成L ciwemit-30片剧露行史材料也机忸3p HE1 4L 100-*£.50材。1 包力由：30自L鼻君史材科目机杷R条:71值上刊通总史材明包机后30 ffl1 ci i(n-T5i£tfi<fefilln第插L 口 1 100中告苣层材用性机忸CuttKVDDl ri loo-十二若急座WJtHiiJLfpCflstKmL Cl 300-ffla.BC-村第1 色机ftXUE口：7100-刊通总史材川色机恬Cg

22、国*00i.OLitio-，5言.&星期对仓机用CustKimf 04»K再O.(KK/5 &L则100任露忠卫材科也机匡CuxKVDDi »1 iw-Ati.ea豺第七抗布CustBimL LL.MM-中或白'材耳色机hCuxCVDD：01 100-气骷逆总史材弱也挑后Cg国网M>1 口 13-W髭总成材刷仓凯依d*L 口1 .100,要思用树均包机匡CuxKVDDi »l 100-hAti.SP材用也批片tt»0T府蝌凯腆砌*公司雷示tff m站雨打理海机跟碘公司il肃套IK B U二，开始|号3 F "图豺:

23、阿B即j1 i grPKfrj . .1"口工口| 口射F:sum 1H 中西二制 .| 亚 15：M相对BOM的财务数据稽查CREATE PROCEDURE dbo . A_ICBOM_temp AS BEGIN SET NOCOUNT ON deletefrom ZRSC_temp_BOM0deletefrom ZRSC_temp_BOM1-一级物料SELECT ICBomChild . FInterID , ICBomChild. FItemID asFSubItemID , ICBomChild . FQty ,( 100+isnull( ICBomChild . fentr

24、yselfz0142 , 0)as FSCRAP into #temp FROM ICBomChild , t_ICItem t2 WHERe ICBomChild . FItemID =t2 . FItemID CREATE INDEX #temp ON #Temp ( FinterID )-select count(*) from ICBom where FUseStatus = 1072 and FStatus = 1 and FItemID in ( select FSubItemID from #temp) declare count as int select count =cou

25、nt (*) from ICBom where FUseStatus = 1072 and FStatus =1 and FItemID in ( select FSubItemID from #temp ) -明细物料while count >0begininsert into ZRSC_temp_BOM0 select * from #temp where FSubItemID in ( select Fitemid from ICBom where FUseStatus = 1072 and FStatus = 1 )-select * from ZRSC_temp_BOM0 wh

26、ere fsubitemid=3704insert into #temp selectt1 . finterid , t3 . fitemid , t1 . Fqty * t3 . Fqty , t1 . FSCRAP*( 100 +isnull (t3 . fentr yselfz0142 ,0)/ 100 from #temp t1 , ICBOM t2 , ICBomChild t3where t1 . FSubItemID =t2 . FItemid and t2 . finterid =t3 . finterid and t2 . FUseStatus = 1072 and t2 .

27、 FStatus = 1 -select * from #temp where finterid=7891-delete from #temp where FSubItemid in (select FSubItemid fromZRSC_temp_BOM0 where fitemid=#temp.fsubitemid or finterid=#temp.finterid) delete from #temp where exists (select a. FSubItemid from ZRSC_temp_BOM0 a, icbom b where a. finterid =b. finte

28、rid and ( b. fitemid =#temp . fsubitemid or a. finterid =#temp . finterid ) and a . FSubItemid =#temp . FSubItemid )-alter by long for BOM多级展开成本不准(外发，印刷塑件等级以上的BOM)select count =count (*) from ICBom where FUseStatus = 1072 and FStatus=1 and FItemID in ( select FSubItemID from #temp ) endinsert into Z

29、RSC_temp_BOM1selecta .*, isnull ( b.fplanprice, 0) as fprice ,isnull(b. fplanprice,0)* a. fqty * a. FSCRAP/ 100 as Famount ,isnull(c. fprice , 0)*1.17 as fprice1,isnull(c. fprice , 0)*1.17 * a. fqty *a. FSCRAP/ 100 as Famount1isnull(d. fprice , 0)as fprice2 ,isnull(d. fprice , 0)*a. fqty * a. FSCRAP

30、/ 100 as Famount2 ,isnull(b. Fstandardcost,0) as fprice3 ,isnull ( b. Fstandardcost, 0)* a. fqty * a. FSCRAP/ 100 as Famount3from#temp a left jointicitem bon a . FSubItemID =b. fitemidleft join(select fitemid , sum (fbegbal )/ sum (fbegqty ) as Fprice from icbal where Fyear in( select Fvalue from t_

31、SystemProfile where FKEY='CurrentYear' and FCategory ='IC')and FPeriod in( select Fvalue from t_SystemProfilewhereFKEY = 'CurrentPeriod'and FCategory ='IC')group by fitemid having sum(fbegqty )<> 0) c on a . FSubItemID =c. fitemid left join(select fitemid , max

32、(fprice ) as fprice FROM t_SupplyEntry t1 where t1 . fused = 1 and t1 . fdisabledate >getdate () and not Exists( select *from t_SupplyEntry t2where t2 . fquotetime >t1 . fquotetimeand t2 . fused =1 and t2 . fdisabledate>getdate ()and t2 . fitemid=t1 . fitemid )groupby fitemid ) d on a . FSu

33、bItemID=d. fitemid-加权平均加要含税的单价update ZRSC_temp_BOM1 set Fprice1 =a. fprice *1.17 , Famount1= isnull (a. fprice , 0)* fqty *1.17 * FSCRAP/ 100 from ZRSC_temp_BOM1 ,(select t2 . fitemid , t2 . fprice from icstockb川 t1, icstockb川entry t2where t1 . finterid =t2 . finterid and t1 . ftrantype = 1 and t2 .

34、 fitemid <>36439 and Not Exists( Select * From icstockbill a1, icstockb川entry a2wherea1 . finterid =a2 . finterid and a2 . fitemid =t2 . fitemid and a1 . ftrantype =1 And a1 . fdate > t1 . fdate ) )awhere a . fitemid =ZRSC_temp_BOM1 . FSubItemID and Fprice1 =0update ZRSC_temp_BOM1 from ZRSC

35、_temp_BOM1setFprice1 =Fprice2,Famount1 =Famount2where Fprice1 =0 update ZRSC_temp_BOM1 ZRSC_temp_BOM1 wheresetFpriceFamountis not=fprice null*fqty * FSCRAP/100 fromend采购申请检验单create proc sp_lyh_hq_puricqcpjbtestfdates datetime , fdatee datetimefitemnumbers fsupnumbersvarchar ( 255 ), fitemnumbere var

36、char ( 255 ), varchar ( 255 ), fsupnumbere varchar ( 255 )asSET NOCOUNT ON-创建单据CREATE TABLE #QCData (Fdate datetimeFType INT ,FItemID INT ,FSupplyID INT ,FQCBillInterIDFPOInstockInterIDFPOInstockEntryIDINT ,-检验单的内码INT DEFAULT 0，一 申请单INT DEFAULT 0，一申请单分录FUnitIDINTDEFAULT 0 ,-加权平均没有时取最新报价FBSICQCQtyFSe

37、ndUpQtyFPOInstockQtyFNotPassCountDECIMAL ( 28, 10),DECIMAL ( 28, 10),DECIMAL ( 28 , 10 ) DEFAULT 0INT DEFAULT 0 一 不合格批次CREATE TABLE #Result (fdatedatetime ,FItemID INT ,FName NVARCHAR( 100 ),FSupplylD INT , FBSPOInStockQty DECIMAL (28, 10) DEFAULT 0FPOInStockQty DECIMAL ( 28 , 10 ) DEFAULT 0 ,FCUPOI

38、nStockQty DECIMAL (28, 10) DEFAULT 0FBSICQCQty DECIMAL ( 28 , 10) DEFAULT 0 ,FICQCQty DECIMAL ( 28 , 10) DEFAULT 0 ,FCUICQCQty DECIMAL ( 28 , 10) DEFAULT 0 ,FCheckCount INT DEFAULT 0 ,FNotPassCount INT DEFAULT 0 , FSumSort INT DEFAULT 100 ,FSort INT DEFAULT 1)INSERT INTO#QCData ( Fdate , FType , FIt

39、emID , FSupplyID , FQCBillInterID , FPOInstockInterID, FPOInstockEntryID , FBSICQCQty , FSendUpQty , FPOInstockQty , FNotPassCount )SELECT q. fdate , 0, q. FItemID , q. FSupplyID , q. FInterID , q. FInStockInterID , q. FSeria lID , ISNULL ( m FCoefficient , 1)* tp . FPassAuxQty , ISNULL ( m FCoefficient , 1)* tp . FSendUpAuxQty , 0, CASE t . FInspectionLevel WHEN353 一 抽检 THEN CASE q . FResult WHEN287 THEN 1 ELSE 0 END WHEN351 一 全检 THEN