K3與CRM接口文檔

1、精選優(yōu)質(zhì)文檔-傾情為你奉上K3與CRM接口文檔物料l K3存儲過程icitem_crm01 不設(shè)置輸入條件，直接輸出所有在使用狀態(tài)的物料l 輸出字段：物料代碼，名稱，規(guī)格型號，基本計量單位, 類別,系列l(wèi) 定時同步時間相隔一天,執(zhí)行時間：1：00分開始，定時作業(yè)crm01客戶l 中間庫MiddleDB表k3_Account觸發(fā)器account_inserted 新增或更新客戶資料CRM傳到中間庫表，輸出創(chuàng)建客戶資料到金蝶K3兩個表T_Item和t_Organizationl 輸出創(chuàng)建字段：客戶代碼，客戶名稱，聯(lián)系人，地址，狀態(tài)，區(qū)域，電話，移動，傳真，郵編，郵件地址，開戶銀行，銀行賬號，稅務(wù)登

2、記號，銷售模式，分管專員，業(yè)務(wù)員，海關(guān)注冊碼，保稅監(jiān)管類，國別地區(qū)，收款條件l 實時同步銷售訂單l K3存儲過程seorder_crm02 新增或更新銷售訂單，反審核，重審核l 輸出字段：合同等級，銷售范圍，銷售方式，訂單類型，客戶代碼，摘要，日期，銷售訂單編號，幣別，付款方式，匯率，銷售年份，批次，出貨省份，來源，區(qū)域，地市，審核人，審核日期，部門，員工工號，制單人，訂單IDl 反審核與重審核開發(fā)觸發(fā)器字段：當(dāng)前審核級別為0就同步對應(yīng)一個值到中間表，中間表字段：記錄狀態(tài)recordStatus（0:創(chuàng)建 1：更新 2：反審核 3:重審核）l 定時同步時間相隔一天，執(zhí)行時間：1：00分開始，定

3、時作業(yè)crm01銷售訂單明細(xì)表l K3存儲過程update_seorder_crm03 訂單變更，l 輸出字段：序號，物料代碼，單位，數(shù)量，單價（含稅），金額（含稅），稅率，建議交貨日期，合同編號，簽約時間，體系，辦事處，實際業(yè)務(wù)員，類別，訂單ID，系列l(wèi) 定時同步時間相隔一天，執(zhí)行時間：1：00分開始，定時作業(yè)crm01銷售出庫單l K3存儲過程icstockbill_crm04 新增或更新銷售出庫單；l 輸出字段：訂單類型，銷售業(yè)務(wù)類型，客戶代碼，具體地址，源單類型，送貨費，聯(lián)系人，摘要，聯(lián)系電話，銷售方式，日期(出庫日期)，出庫單編號，貨運單號，物流公司，發(fā)貨人，部門，員工工號，制單人，

4、審核，審核日期l 定時同步時間相隔一天，執(zhí)行時間：1：00分開始，定時作業(yè)crm01銷售出庫單明細(xì)表l K3存儲過程icstockbill_crm04 新增或更新銷售出庫單明細(xì)；l 輸出字段：序號，產(chǎn)品代碼，單位，應(yīng)發(fā)數(shù)量，實發(fā)數(shù)量，備注，銷售單價，銷售金額，合同編號，訂單編號，箱數(shù)，體系，辦事處，實際業(yè)務(wù)員，類別，系列l(wèi) 定時同步時間相隔一天，執(zhí)行時間：1：00分開始，定時作業(yè)crm01銷售發(fā)票l K3存儲過程ICSale_insert_crm05，新增或更新；l 輸出字段：客戶代碼，銷售方式，源單類型，結(jié)算方式，收款日期，開戶銀行，日期，納稅登記號，地址，往來科目，摘要，發(fā)票號碼，匯率類型

5、，幣別，匯率，部門，員工工號，記賬，開票人，審核人，審核日期l 定時同步時間相隔一天，執(zhí)行時間：1：10分開始，定時作業(yè)crm02發(fā)票明細(xì)l K3存儲過程ICSale_insert_crm05，新增或更新；l 輸出字段：序號，產(chǎn)品代碼，單位，數(shù)量，單價（含稅），金額，稅率，訂單編號，發(fā)票ID，出庫單號，合同編號l 定時同步時間相隔一天，執(zhí)行時間：1：10分開始，定時作業(yè)crm02收款單l K3存儲過程t_RP_NewReceiveBill_insert_crm06，新增或更新l 輸出字段：收款類型，收款銀行，收款日期，付款單位，結(jié)算方式，幣種，備注（合同號），審核人，制單人，金額，日期，收款單

6、IDl 定時同步時間相隔一天，執(zhí)行時間：1：10分開始，定時作業(yè)crm02收款單明細(xì)l K3存儲過程t_RP_NewReceiveBill_insert_crm06，新增或更新l 輸出字段：序號，產(chǎn)品代碼，單位，數(shù)量，單價（含稅），金額，收款單ID，合同編號l 定時同步時間相隔一天，執(zhí)行時間：1：10分開始，定時作業(yè)crm02核銷日志l K3存儲過程t_rp_newcheckinfo_insert_crm07，新增或更新l 特殊業(yè)務(wù)場景：存在跨月核銷以及跨月反核銷，存儲過程語句將在執(zhí)行判斷全表與中間表數(shù)據(jù)匹配，如果中間表數(shù)據(jù)存在，不插入；不存在的數(shù)據(jù)進(jìn)行插入。l 輸出字段：核銷日期，關(guān)聯(lián)ID，

7、核銷類型，核銷金額，核銷序號，核銷日志ID，核銷人，記錄類型，狀態(tài)，記錄狀態(tài)l 定時同步時間相隔一個月，執(zhí)行時間每個月第二個周日零晨：2：00分開始，定時作業(yè)crm04中間數(shù)據(jù)庫MiddleDB中間表l 物料 k3_Materiall 客戶 k3_Accountl 銷售訂單k3_orderl 訂單明細(xì)k3_Orderdetaill 銷售出庫k3_Deliveryl 出庫明細(xì)k3_DeliveryDetaill 銷售發(fā)票k3_Invoicel 發(fā)票明細(xì)k3_InvoiceDetaill 收款單k3_Cashl 收款明細(xì)k3_CashDetaill 核銷日志k3_VerifilogK3與CRM開發(fā)

8、源代碼創(chuàng)建、更新、審核l 物料-創(chuàng)建insert into middledb.dbo.k3_materialselect a.FNumber as productCode,a.FName as name,a.FModel as model,b.FName as unit,d.FName as series,c.FName as type,0,0,1, a.Fitemid as materialID,getdate()from t_ICItem a join t_MeasureUnit bon a.FStoreUnitID=b.FMeasureUnitID left join t_SubMess

9、age c on a.F_112=c.FInterID and c.ftypeid=10013left join t_SubMessage d on a.F_113=d.FInterID and d.ftypeid=10014left join t_BaseProperty e on a.FitemID = e.FitemIDleft join t_item f on f.FitemID = a.Fitemid where f.FNumber like '1.%'or f.FNumber like '2.%' andf.FchkuserID is not nul

10、l and f.FNumber not like '1.%' or f.FNumber not like '2.%'and DateDiff(dd,e.FCreateDate,getdate()=0l 物料-修改insert into middledb.dbo.k3_materialselect a.FNumber as productCode,a.FName as name,a.FModel as model,b.FName as unit,d.FName as series,c.FName as type,1,0,1,a.Fitemid,getdate()f

11、rom t_ICItem a join t_MeasureUnit bon a.FStoreUnitID=b.FMeasureUnitID left join t_SubMessage c on a.F_112=c.FInterID and c.ftypeid=10013left join t_SubMessage d on a.F_113=d.FInterID and d.ftypeid=10014left join t_BaseProperty e on a.FitemID = e.FitemIDleft join t_item f on f.FitemID = a.Fitemid whe

12、re DateDiff(dd,e.FLastmodDate,getdate()=0 and DATEDIFF(dd,e.FCreateDate,GETDATE() !=0 andf.FchkuserID is not nulll 客戶創(chuàng)建ALTER TRIGGER dbo.account_insertedon dbo.k3_Accountfor insertasbegininsert into AIS251.dbo.t_Organization(FItemID,FNumber,FName,FPaycondition) select (select max(FItemID)+1 from AIS

13、251.dbo.t_Item),inserted.accountNumber , ,inserted.gatheringCodition from MiddleDB.dbo.k3_Account as khzjbinner join inserted on inserted.accountid=khzjb.accountidand inserted.recordType=0;insert into AIS251.dbo.t_Item (FItemID,FNumber,FName,FItemClassID,FParentID,FDetail) select (selec

14、t max(FItemID)+1 from AIS251.dbo.t_Item), inserted.accountNumber,,1,46462,1 from MiddleDB.dbo.k3_Account as khzjbinner join inserted on inserted.accountid=khzjb.accountidand inserted.recordType=0;update AIS251.dbo.t_Organizationset FName=,FPaycondition=inserted.gatheringCod

15、itionfrom AIS251.dbo.t_Organization inner join inserted on inserted.accountNumber=AIS251.dbo.t_Organization.FNumberand inserted.recordType=1;update AIS251.dbo.t_Itemset FName=from AIS251.dbo.t_Item inner join inserted on inserted.accountNumber=AIS251.dbo.t_Item.FNumberand inserted.recor

16、dType=1;update MiddleDB.dbo.k3_Accountset statecode=1from MiddleDB.dbo.k3_Account khzjbinner join inserted on khzjb.accountid=inserted.accountid;Endl 訂單創(chuàng)建insert into middledb.dbo.k3_orderselect p.FName 合同等級,v.FName 購貨單位,l.FName 銷售范圍,k.FName 訂單類型,v.Fnumber 客戶代碼,Fexplanation 摘要,Fdate 日期,fbillno 銷售訂單編號

17、,f.FName 幣別,Fpaystyle 付款方式,a.FExchangeRate 匯率,i.FName 銷售年份,h.FName 批次,n.FName 出貨省份, j.FName 來源,m.FName 區(qū)域,FHeadselfs0174 市,b.FName 審核人,fcheckdate as 審核日期,c.FName 部門,d.FNumber 員工工號,e.FName 制單人,a.FInterID 訂單ID,0,0,1,getdate()from SEOrder aleft join t_user b on a.FCheckerID=b.FUserIDleft join t_Departm

18、ent c on a.FDeptID=c.FItemIDleft join t_Emp d on a.FEmpID=d.FItemIDleft join t_user e on a.FBillerID=e.FUserIDleft join t_Currency f on a.FCurrencyID=f.FCurrencyIDleft join T_SubMessage n on a.FHeadselfs0173=n.FInterIDleft join T_SubMessage m on a.FHeadselfs0169=m.FInterIDleft join T_SubMessage j on

19、 a.FHeadselfs0172=j.FInterIDleft join T_SubMessage h on a.FHeadselfs0171=h.FInterIDleft join T_SubMessage i on a.FHeadselfs0170=i.FInterIDleft join T_SubMessage k on a.Fsalestyle=k.FInterIDleft join T_SubMessage l on a.FAreaPS=l.FInterIDleft join t_Organization v on a.FCustID=v.FItemIDleft join t_Po

20、Degree p on a.FHeadselfs0167=p.FIDwhere a.Fstatus != 0 and a.FCancellation = 0and DateDiff(dd,Fcheckdate,getdate()= 0l 訂單-變更insert into middledb.dbo.k3_orderselect p.FName 合同等級,v.FName 購貨單位,l.FName 銷售范圍,k.FName 訂單類型,v.Fnumber 客戶代碼,Fexplanation 摘要,Fdate 日期,fbillno 銷售訂單編號,f.FName 幣別,Fpaystyle 付款方式,a.F

21、ExchangeRate 匯率,i.FName 銷售年份,h.FName 批次,n.FName 出貨省份, j.FName 來源,m.FName 區(qū)域,FHeadselfs0174 市,b.FName 審核人,fcheckdate as 審核日期,c.FName 部門,d.FNumber 員工工號,e.FName 制單人,a.FInterID 訂單ID,1,0,1,getdate()from SEOrder aleft join t_user b on a.FCheckerID=b.FUserIDleft join t_Department c on a.FDeptID=c.FItemIDle

22、ft join t_Emp d on a.FEmpID=d.FItemIDleft join t_user e on a.FBillerID=e.FUserIDleft join t_Currency f on a.FCurrencyID=f.FCurrencyIDleft join T_SubMessage n on a.FHeadselfs0173=n.FInterIDleft join T_SubMessage m on a.FHeadselfs0169=m.FInterIDleft join T_SubMessage j on a.FHeadselfs0172=j.FInterIDle

23、ft join T_SubMessage h on a.FHeadselfs0171=h.FInterIDleft join T_SubMessage i on a.FHeadselfs0170=i.FInterIDleft join T_SubMessage k on a.Fsalestyle=k.FInterIDleft join T_SubMessage l on a.FAreaPS=l.FInterIDleft join t_Organization v on a.FCustID=v.FItemIDleft join t_PoDegree p on a.FHeadselfs0167=p

24、.FID-FchangeDate as 變更日期where DateDiff(dd,a.FchangeDate,getdate()=0 and DateDiff(dd,a.FcheckDate,getdate() != 0and a.Fstatus != 0 and FCancellation=0-mingxiinsert into middledb.dbo.k3_orderdetailselect b.FEntryID 序號,b.FItemID 產(chǎn)品ID,c.Fname 單位,b.FAuxQty 數(shù)量,b.FAuxTaxPrice 含稅單價,b.FAllstdAmount 金額,b.FCES

25、S 稅率,b.FAdviceConsignDate 建議交貨日期,b.FEntrySelfS0162 合同編號,b.FEntrySelfS0168 簽約時間,d.FName 體系,e.FName 辦事處,f.FName 實際業(yè)務(wù)員,b.FEntrySelfS0178 類別,b.FInterID 訂單ID,b.FEntrySelfS0179 系列,b.Fdetailid 訂單明細(xì)ID,0,0,1,a.Fbillno,getdate()from SEOrderEntry b left join t_MeasureUnit c on b.FUnitID=c.FItemID left join SEO

26、rder a on a.finterid=b.finteridleft join t_Department d on b.FEntrySelfS0176=d.FItemIDleft join t_Department e on b.FEntrySelfS0174=e.FItemIDleft join t_Emp f on b.FEntrySelfS0175=f.FItemIDwhere a.Fstatus != 0 and a.FCancellation=0and DateDiff(dd,a.FchangeDate,getdate()=0 and DateDiff(dd,a.FcheckDat

27、e,getdate() != 0l 訂單明細(xì)-創(chuàng)建insert into middledb.dbo.k3_orderdetailselect b.FEntryID 序號,b.FItemID 產(chǎn)品ID,c.Fname 單位,b.FAuxQty 數(shù)量,b.FAuxTaxPrice 含稅單價,b.FAllstdAmount 金額,b.FCESS 稅率,b.FAdviceConsignDate 建議交貨日期,b.FEntrySelfS0162 合同編號,b.FEntrySelfS0168 簽約時間,d.FName 體系,e.FName 辦事處,f.FName 實際業(yè)務(wù)員,b.FEntrySelfS01

28、78 類別,b.FInterID 訂單ID,b.FEntrySelfS0179 系列,b.Fdetailid 訂單明細(xì)ID,0,0,1,a.Fbillno,getdate()from SEOrderEntry b left join t_MeasureUnit c on b.FUnitID=c.FItemID left join SEOrder a on a.finterid=b.finteridleft join t_Department d on b.FEntrySelfS0176=d.FItemIDleft join t_Department e on b.FEntrySelfS0174

29、=e.FItemIDleft join t_Emp f on b.FEntrySelfS0175=f.FItemIDwhere a.Fstatus != 0 and a.FCancellation=0and DateDiff(dd,a.fcheckdate,getdate() = 0l 出庫單-創(chuàng)建insert into middledb.dbo.k3_deliveryselect n.FName 訂單類型,m.FName 銷售業(yè)務(wù)類型,b.FNumber 客戶代碼,FHeadSelfB0162 具體地址,o.FName 源單類型,FHeadSelfB0172 送貨費,FHeadSelfB01

30、63 聯(lián)系人,Fexplanation 摘要,FHeadSelfB0164 聯(lián)系電話,c.FName 銷售方式,Fdate 出庫日期,fbillno 出庫單編號,d.FName 發(fā)貨人,f.FName 部門,e.FNumber 員工工號,g.FName 制單人,h.FName 審核人,FcheckDate 審核日期,a.FInterID 出庫單ID,0,0,1,getdate()from ICStockBill aleft join t_Organization b on a.FSupplyID=b.FItemIDleft join t_SubMessage c on a.Fsalestyle

31、=c.finteridleft join t_Emp d on a.FFmanagerID=d.FItemIDleft join t_Emp e on a.FEmpID=e.FItemIDleft join t_Department f on a.FDeptID=f.FItemIDleft join t_user g on a.FBillerID=g.FUserID left join t_user h on a.Fcheckerid=h.FUserIDleft join ICTransactionType o on a.FSelTranType=o.FIDleft join t_Item n

32、 on a.FHeadSelfB0169=n.FItemIDleft join t_SubMessage m on a.FMarketingStyle=m.FInterID -where a.FBillNo='XOUT'where a.FTranType=21 and a.FROB=1 and a.fstatus != 0 and a.FCancellation = 0 and a.FHeadSelfB0169 is not nulland DateDiff(dd,a.Fcheckdate,getdate() = 0l 出庫單明細(xì)insert into MiddleDB.dbo

33、.k3_deliverydetailselect a.FEntryID,a.FitemID materialID,i.Fname unit,a.FqtyMust qtyMust,a.Fqty qty,a.Fnote note,a.Fprice price, a.Famount allHookAmount,a.FEntryselfB0162 客戶合同編號,h.finterid orderNo,a.FEntryselfB0168 箱數(shù),j.FName 體系,k.FName 辦事處,l.FName 實際業(yè)務(wù)員,a.FEntryselfB0171 類別,a.FEntryselfB0172 系列,b.F

34、Interid,a.FdetailID,h.FDetailID,0,0,1,getdate()from icstockbillEntry aleft join ICStockBill b on a.FInterID = b.FInterIDleft join (select c.FentryID,d.FbillNO,c.FOrderEntryID,c.FOrderBillNo from SEOutStockEntry c left join SEoutStock d on c.finterid = d.finterid where d.fstatus != 0 and d.FCancellat

35、ion = 0 ) eon a.FSEOutbillNo = e.fbillno and a.FSEOutEntryID =e.FEntryIDleft join (select f.FDetailID,f.FEntryID,g.FBillNo,g.finterid from SEOrderEntry f left join SEOrder g on f.finterid = g.finterid where g.fstatus != 0 and g.FCancellation = 0 )hon e.FOrderBillNo = h.FBillNo and e.FOrderEntryID =

36、h.FEntryIDleft join t_MeasureUnit i on a.FUnitID = i.FItemIDleft join t_Department j on j.Fitemid = a.FEntryselfB0189left join t_Department k on k.Fitemid = a.FEntryselfB0187left join t_Emp l on l.Fitemid = a.FEntryselfB0188 where a.FSourcebillno != '' and b.ftrantype =21 and b.fstatus != 0

37、and b.FCancellation = 0 and DateDiff(dd,b.Fcheckdate,getdate() = 0l 發(fā)票創(chuàng)建insert into middledb.dbo.k3_invoiceselect h.FNumber 客戶代碼,b.FName 銷售方式,j.fname 源單類型,c.FName 結(jié)算方式,a.FSettleDate 收款日期,a.Fbank 開戶銀行,a.Fdate 日期,a.FTaxNum 納稅登記號,a.FAddress 地址,d.FName 往來科目,a.Fexplanation 摘要,a.FBillNo 發(fā)票號碼,k.Fname 匯率類型,

38、i.Fname 幣別,convert(varchar,a.FExchangeRate) 匯率,e.FName 部門,f.FNumber 業(yè)務(wù)員工號,g.FName 記賬,o.FName 開票人,g.FName 審核人,Fcheckdate 審核日期,a.FInterID 發(fā)票ID,0,0,1,getdate()from ICSale aleft join t_SubMessage b on a.FSaleStyle=b.Finteridleft join t_settle c on a.FSettleID=c.FItemIDleft join t_Account d on a.FCussent

39、AcctID=d.FAccountIDleft join t_Department e on a.FDeptID=e.FItemIDleft join t_Emp f on a.FEmpID=f.FItemID left join t_Emp o on a.Finvoicer=o.FItemIDleft join t_user g on a.FPosterID=g.FUserID and a.FCheckerID=g.FUserIDleft join t_organization h on a.FCustID = h.fitemidleft join t_Currency i on a.FCu

40、rrencyID=i.FCurrencyIDleft join ICTransactionType j on a.fseltrantype = j.Fidleft join t_ExchangeRate k on k.fid = a.FExchangeRateType where a.fstatus != 0 and a.FCancellation = 0 and DateDiff(dd,a.Fcheckdate,getdate() = 0l 發(fā)票明細(xì)insert into MiddleDB.dbo.k3_invoicedetailselectcs.FentryID 序號,cs.FItemID

41、 產(chǎn)品ID,c.Fname 單位,cs.FAuxQty 數(shù)量,cs.FAuxTaxPrice 含稅單價,cs.Famountincludetax 金額,cs.FTaxRate 稅率,cs.FOrderinterID 訂單ID,cs.FInterID 發(fā)票ID,cs.FdetailID 發(fā)票明細(xì)ID,cs.FSourceInterID 出庫單ID,cs.FContractBillNo 合同編號,st.fdetailid 出庫單明細(xì)ID,0,0,1,getdate()fromICSaleentry CSleft join ICStockBillEntry ST on cs.FSourceInter

42、ID = st.Finterid and cs.FSourceEntryID = st.Fentryidleft join icsale csa on csa.Finterid = cs.finteridleft join t_MeasureUnit c on cs.FUnitID=c.FItemID where csa.fstatus != 0 and csa.FCancellation = 0and DateDiff(dd,csa.Fcheckdate,getdate() = 0l 收款單創(chuàng)建insert into MiddleDB.dbo.k3_cashselect f.FName 收款

43、類型,FRPBank 收款銀行,e.FName 結(jié)算方式,d.FName 幣種,FContractNo 備注合同號,b.FName 審核人,c.FName 制單人,FAmount 金額,FDate 日期,FBillID 收款單ID,0,0,1,a.FNumber 收款單編號,getdate()fromt_RP_NewReceiveBill aleft join t_user b on a.Fchecker=b.FUserIDleft join t_user c on a.Fpreparer=c.FUserIDleft join t_Currency d on a.FCurrencyID=d.F

44、CurrencyIDleft join t_settle e on a.Fsettle=e.FItemIDleft join t_RP_SystemEnum f on a.Fbilltype=f.Fitemidwhere a.FRP=1 and a.fstatus != 0 and DateDiff(dd,a.Fcheckdate,getdate() =0l 收款單明細(xì)insert into MiddleDB.dbo.k3_cashdetail select Findex 序號,a.Fitemid 產(chǎn)品ID,FName 單位,FQuantity 數(shù)量,FTaxPrice 含稅單價,a.FSet

45、tleAmount 金額,a.FBillID 收款單ID,FEntryID 收款明細(xì)ID,a.FContractNo 合同編號,a.FEntryID_SRC 源單單號,0,0,1,getdate()fromt_rp_ARBillOfSH aleft join t_RP_NewReceiveBill b on a.FBillID=b.FBillIDleft join t_MeasureUnit c on a.FUnitID=c.FItemID -left join (select FinterID,Fbillno from ICSale where Fbillno != '' a

46、nd FCancellation = 0) d on a.Fbillno_src = d.Fbillno where b.FRP=1and b.fstatus != 0 and DateDiff(dd,b.Fcheckdate,getdate() =0l 核銷日志insert into middledb.dbo.k3_verifilogselect a.FInterID, a.Fid, b.Ftype, b.FNumber, a.FcheckAmount, u.Fname, a.FcheckDate, 0,0,1,getdate() from t_rp_newcheckinfo a join

47、t_rp_contact b on a.FContactID=b.FID left join t_item i on a.FCustomer=i.FItemID left join t_item ii on a.Fdepartment=ii.FItemID left join t_item iii on a.Femployee=iii.FItemID left join t_currency cur on a.FCurrencyID=cur.FCurrencyID left join t_User u on a.FChecker=u.FUserID where a.FRP=1 and a.FI

48、sBad=0 -and DateDiff(dd,a.FcheckDate,getdate() =0 and(a.FCheckManer=1 or (a.FCheckType>0 and a.FCheckManer=3)and FInterID not in (select checkLogID from middledb.dbo.k3_verifilog) union all select a.FInterID, a.Fid, b.Ftype, b.FNumber, a.FcheckAmount, u.Fname, a.FcheckDate, 0,0,1,getdate() from (

49、select FInterID,FID,FBillID,FType,FIsinit,FEntryID,FItemID,FUnitID,FContractNo,FOrderNo,sum(FCheckQuantity)FCheckQty,sum(FRemainQuantity)FRemainQty, sum(FCheckAmount)FCheckAmount,sum(FCheckAmountFor)FCheckAmountFor, sum(FRemainAmount)FRemainAmount,sum(FRemainAmountFor)FRemainAmountFor from t_rp_chec

50、kEntry group by FInterID,FID,FBillID,FType,FIsinit,FEntryID,FItemID,FUnitID,FContractNo,FOrderNo)c left join (select FInterID,FBillID,FType,FIsinit,FEntryID,Max(FRemainQuantity) FRemainQuantity,Max(FRemainAmount) FRemainAmount,Max(FRemainAmountFor) FRemainAmountFor from t_rp_checkEntry where FCheckA

51、mountFor<0 group by FInterID,FBillID,FType,FIsinit,FEntryID)x on c.FInterID=x.FInterID and c.FBillID=x.FBillID and c.FType=x.FType and c.FIsinit=x.FIsinit and c.FEntryID=x.FEntryID left join (select FInterID,FBillID,FType,FIsinit,FEntryID,Min(FRemainQuantity)FRemainQuantity,Min(FRemainAmount) FRe

52、mainAmount,Min(FRemainAmountFor) FRemainAmountFor from t_rp_checkEntry where FCheckAmountFor>=0 group by FInterID,FBillID,FType,FIsinit,FEntryID)y on c.FInterID=y.FInterID and c.FBillID=y.FBillID and c.FType=y.FType and c.FIsinit=y.FIsinit and c.FEntryID=y.FEntryID join t_rp_newcheckinfo a on c.F

53、InterID=a.FInterID join t_rp_contact b on a.FContactID=b.FID left join t_item i on a.FCustomer=i.FItemID left join t_item ii on a.Fdepartment=ii.FItemID left join t_item iii on a.Femployee=iii.FItemID left join t_currency cur on a.FCurrencyID=cur.FCurrencyID left join t_User u on a.FChecker=u.FUserI

54、D left join t_icItem p on c.FItemID=p.FItemID left join t_MeasureUnit q on c.FUnitID=q.FMeasureUnitID where a.FRP=1 and a.FIsBad=0 -and DateDiff(dd,a.FcheckDate,getdate() =0 and a.FCheckManer=2and a.FInterID not in (select checkLogID from middledb.dbo.k3_verifilog) union all select a.FInterID, a.Fid

55、, b.Ftype, b.FNumber, a.FcheckAmount, u.Fname, a.FcheckDate, 0,0,1,getdate() from t_rp_checkEntry c join t_rp_newcheckinfo a on c.FInterID=a.FInterID join t_rp_contact b on a.FContactID=b.FID left join t_item i on a.FCustomer=i.FItemID left join t_item ii on a.Fdepartment=ii.FItemID left join t_item iii on a.Femployee=iii.FItemID left join t_currency cur on a.FCurrencyID=cur.FCurrencyID left join t_User u on a.FChecker=u.FUserID left join t_icItem p on c.FItemID=p.FItemID left join t_MeasureUnit q on c.