-- 检查存储过程是否存在，存在则删除 P202502080013 IF OBJECT_ID('dbo.[PR_YingShouMingXi_yuyubo]', 'P') IS NOT NULL BEGIN DROP PROCEDURE dbo.PR_YingShouMingXi_yuyubo; PRINT '存储过程 dbo.[PR_YingShouMingXi_yuyubo] 已删除'; END GO -- 创建新的存储过程 CREATE PROCEDURE dbo.PR_YingShouMingXi_yuyubo AS BEGIN -- 存储过程逻辑 -- P202505190009 TRUNCATE TABLE ZRP_YingShouMingXi_yuyubo; INSERT INTO ZRP_YingShouMingXi_yuyubo ([销售单主键ID],[销售员ID],[收款条件主键ID],[收款条件],[F_RecConditionId],[整单订单总额],[销售员],[分摊比例],[分摊金额],[SBU] ,[明细区域],[购货单位],[公司性质],[客户信用],[纸质合同号],[项目名称],[签订年份],[订单日期],[发货日期],[整单累计发货金额],[开票日期], [整单累计退货金额],[整单累计开票金额],[整单累计到款金额],[预计到期时间],[质保金],[质保金天数],[PMS日期时间],[项目进度],[项目调试时间], [项目验收时间],[质保金到期时间],[是否提前验收],[账龄月],[结算类别],[结算类型],[转专责小组时间],[是否超账龄转出],[超账龄是否申请归还大区], [反馈表商务人员引用],[反馈表引用分组],[取值销售],[是否坏账],[是否预发货],[是否集采项目],[合同类型],[是否已存在],[订单总额],[应收比例],[累计发货金额] ,[累计退货金额],[累计开票金额],[应收款合计],[未出货总额],[未到期金额],[到期时间],[到期金额],[实际已出货应收款合计],[项目阶段],[收款人],[累计到款金额], [1-6个月],[7-12个月],[13-18个月],[19个月以上],[1年以上],[账龄-阶段],[风险评估级别],[组织ID],[天数],[销售订单编号],[收款条件分摊金额],[收款条件分摊比例],[整单正数订单总额],[收款条件累计核销金额] ,[结算币别],[结算币别ID],[汇率],[客户信用额度],[客户编码],[累计到款金额(原币)],[应收款合计(原币)] ,[运杂费]) SELECT [销售单主键ID],[销售员ID],[收款条件主键ID],[收款条件],[F_RecConditionId],[整单订单总额],[销售员],[分摊比例],[分摊金额],[SBU] ,[明细区域],[购货单位],[公司性质],[客户信用],[纸质合同号],[项目名称],[签订年份],[订单日期],[发货日期],[整单累计发货金额],[开票日期], [整单累计退货金额],[整单累计开票金额],[整单累计到款金额],[预计到期时间],[质保金],[质保金天数],[PMS日期时间],[项目进度],[项目调试时间], [项目验收时间],[质保金到期时间],[是否提前验收],[账龄月],[结算类别],[结算类型],[转专责小组时间],[是否超账龄转出],[超账龄是否申请归还大区], [反馈表商务人员引用],[反馈表引用分组],[取值销售],[是否坏账],[是否预发货],[是否集采项目],[合同类型],[是否已存在],0 [订单总额],[应收比例], 0 [累计发货金额] ,0 [累计退货金额],0 [累计开票金额],0 [应收款合计],0 [未出货总额],0 [未到期金额],NULL [到期时间],0 [到期金额],0 [实际已出货应收款合计],NULL [项目阶段],NULL [收款人],0 [累计到款金额], NULL [1-6个月],NULL [7-12个月],NULL [13-18个月],NULL [19个月以上],NULL [1年以上],NULL [账龄-阶段],NULL [风险评估级别],[组织ID],[天数],[销售订单编号],[收款条件分摊金额],[收款条件分摊比例],[整单正数订单总额] ,[收款条件累计核销金额] ,[结算币别],[结算币别ID],[汇率],[客户信用额度],[客户编码],[累计到款金额(原币)],[应收款合计(原币)],[运杂费] FROM ( SELECT A.FID [销售单主键ID] ,ISNULL(O.fid,l.fid) [销售员ID] ,ISNULL(w.FENTRYID,0) [收款条件主键ID], ISNULL(w.FNAME,'') AS [收款条件], W.F_RecConditionId ,(SELECT SUM(FBillAllAmount) FROM T_SAL_ORDERFIN FF WITH(NOLOCK) WHERE FF.FID = A.FID ) AS [整单订单总额],w.FRECADVANCEAMOUNT [收款条件分摊金额],ISNULL(w.FRECADVANCERATE,100) [收款条件分摊比例] ,ISNULL(O.FNAME,L.FNAME) AS [销售员],CONVERT(DECIMAL(18,2),ISNULL(n.FSHARERATE,100)) AS [分摊比例] ,CONVERT(DECIMAL(18,6),fin.FBillAllAmount*ISNULL(n.FSHARERATE,100)*0.01 ) [分摊金额] ,ISNULL(ACO.区域,ACL.区域) AS [SBU] , ISNULL (ACO.[明细区域] , ACL.明细区域) AS [明细区域],c.FNAME AS [购货单位] ,ISNULL(K.FDATAVALUE,'') AS [公司性质] ,P.FDATAVALUE AS [客户信用],A.F_contractnumber AS [纸质合同号] , G.FNAME AS [项目名称] ,CONVERT( NVARCHAR(10), YEAR(A.FAPPROVEDATE)) AS [签订年份],CONVERT(VARCHAR(10), A.FAPPROVEDATE,23) AS [订单日期] --,[发货日期]=ISNULL((SELECT (CONVERT(NVARCHAR(10),MAX(SAL.FDATE),23)) AS '发货日期' FROM T_SAL_DELIVERYNOTICE SAL WITH(NOLOCK) --INNER JOIN T_SAL_DELIVERYNOTICEFIN B WITH(NOLOCK) ON SAL.FID =B.FID --WHERE SAL.FDOCUMENTSTATUS= 'C' AND SAL.F_PAPERCONTRACT = A.F_contractnumber),'') -- ,[发货日期]=ISNULL((SELECT STRING_AGG(CONVERT(NVARCHAR(10),SAL.FDATE,23)+'---'+FORMAT( B.FBillAllAmount_LC,'0.00')+'元',',') -- AS '发货日期' FROM T_SAL_DELIVERYNOTICE SAL WITH(NOLOCK) --INNER JOIN T_SAL_DELIVERYNOTICEFIN B WITH(NOLOCK) ON SAL.FID =B.FID --WHERE SAL.FDOCUMENTSTATUS= 'C' AND SAL.F_PAPERCONTRACT = A.F_contractnumber),'') ,[发货日期]=NULL ,[整单累计发货金额]=ISNULL( (SELECT SUM(B.FBillAllAmount) AS '累计发货金额' FROM T_SAL_DELIVERYNOTICE SAL WITH(NOLOCK) INNER JOIN T_SAL_DELIVERYNOTICEFIN B WITH(NOLOCK) ON SAL.FID =B.FID WHERE SAL.FDOCUMENTSTATUS= 'C' AND SAL.F_PAPERCONTRACT = A.F_contractnumber AND SAL.FSALEORGID=a.FSALEORGID),0) ,[开票日期]=NULL --,[开票日期]=ISNULL((CASE WHEN SUBSTRING(U.FNUMBER,1,1)= 'W' THEN T.开票日期 ELSE T.没有红色的开票日期 END ),'') --,[开票日期]=T.开票日期 ,[整单累计退货金额]=ISNULL(( SELECT SUM(B.FBillAllAmount) AS '累计退货金额' FROM T_SAL_RETURNSTOCK AA WITH(NOLOCK) INNER JOIN T_SAL_RETURNSTOCKFIN B WITH(NOLOCK) ON AA.FID =B.FID WHERE AA.FDOCUMENTSTATUS= 'C' AND aa.FSALEORGID = a.FSALEORGID AND AA.F_contractnumber LIKE CONCAT(A.F_contractnumber, '-%') AND AA.F_contractnumber NOT LIKE CONCAT(A.F_contractnumber, '-%-%') ),0) ,[整单累计开票金额] =AD.发票总金额 ,[整单累计到款金额]=ISNULL(a.F_Amount,0) ,[预计到期时间]= (SELECT MAX([预计到期时间]) AS [预计到期时间] FROM ( SELECT DISTINCT * FROM ( SELECT plan1.FID ,CASE WHEN X.FNAME LIKE '%预付款%' THEN CONVERT(NVARCHAR(20), AA.FAPPROVEDATE, 120) WHEN X.FNAME LIKE '%货到收款%' THEN DATEADD(DAY, plan1.F_DAY, CK.F_SFSIGNATURETIME) WHEN X.FNAME LIKE '%货到票到收款%' THEN DATEADD(DAY, plan1.F_DAY,FP.FAPPROVEDATE) WHEN X.FNAME LIKE '%款到发货%' THEN DATEADD(DAY, plan1.F_DAY, CK.F_SFSIGNATURETIME) WHEN X.FNAME LIKE '%质保金%' THEN DATEADD(DAY, plan1.F_DAY,AA.F_XMYSSJ) WHEN (X.FNAME LIKE '%调试完成%' OR X.FNAME LIKE '%项目验收%') THEN DATEADD(DAY,ISNULL(plan1.F_DAY,0), AA.F_XMYSSJ) -- 注意：销售订单【验收日期】：年后会加这个字段，由PMS系统传输过来 该字段未添加 --【调试完成、项目验收】：取销售订单【验收日期】日期为开始日期，加上销售订单明细有【天数#】判断预计到期日，再计算到期日，如果实际时间超过预计到期日则判断已到期，【到期日期】取预计到期日； WHEN AA.F_XMYSSJ IS NOT NULL THEN DATEADD(DAY,ISNULL(plan1.F_DAY,0), AA.F_XMYSSJ) ELSE CONVERT(NVARCHAR(20), AA.FAPPROVEDATE, 120) --ELSE '2029-09-01' END AS '预计到期时间' --销售订单-付款计划-PMS日期# ,plan1.F_RECCONDITIONID,X.FNAME, plan1.F_DAY,plan1.FENTRYID ,AA.FAPPROVEDATE,CK.F_SFSIGNATURETIME FROM T_SAL_ORDER AA WITH(NOLOCK) LEFT JOIN T_SAL_ORDERPLAN plan1 WITH(NOLOCK) ON plan1.FID=aa.FID LEFT JOIN T_BD_RecCondition_L X WITH(NOLOCK) ON X.FID = plan1.F_RecConditionId LEFT JOIN (SELECT MAX(F_SFSIGNATURETIME) F_SFSIGNATURETIME ,F_CONTRACTNUMBER,FSALEORGID FROM T_SAL_OUTSTOCK WITH(NOLOCK) WHERE F_CONTRACTNUMBER = a.F_CONTRACTNUMBER AND FSALEORGID = a.FSALEORGID GROUP BY F_CONTRACTNUMBER,FSALEORGID ) CK ON CK.F_CONTRACTNUMBER = aa.F_CONTRACTNUMBER AND CK.FSALEORGID = AA.FSALEORGID --销售出库单 LEFT JOIN (SELECT MAX(FAPPROVEDATE) FAPPROVEDATE,F_PAPERNUMBER,FSALEORGID FROM T_IV_SALESIC ff WITH(NOLOCK) WHERE ff.F_PAPERNUMBER = a.F_CONTRACTNUMBER AND ff.FSALEORGID = a.FSALEORGID GROUP BY F_PAPERNUMBER,FSALEORGID) FP ON FP.F_PAPERNUMBER = aa.F_CONTRACTNUMBER AND FP.FSALEORGID = AA.FSALEORGID WHERE aa.FID=a.FID AND plan1.FENTRYID=w.FENTRYID ) kk WHERE KK.fid=a.FID AND w.FENTRYID= KK.FENTRYID ) aa) ,[质保金]=W.FRECADVANCEAMOUNT ,[质保金天数]=W.F_DAY ,[PMS日期时间]=W.F_DAY ,a.F_XMJDD AS '项目进度' --取销售订单-销售进度 ,CONVERT(NVARCHAR(10),a.F_XMTSSJ,23) AS '项目调试时间' --取项目调试时间 ,CONVERT(NVARCHAR(10),a.F_XMYSSJ,23) AS '项目验收时间' --取项目验收时间 ,CONVERT(NVARCHAR(10),DATEADD(DAY,ISNULL(w.f_day,0) ,w.F_PMSDate),23) AS '质保金到期时间' ,[是否提前验收]=CASE WHEN a.F_YYS = 0 THEN '否' ELSE '是' END ,[账龄月]= 0 ----,CASE WHEN A.F_OrderType in ('仪表','YB') THEN '元器件' ELSE s.FCAPTION END AS '结算类别' --取销售订单-单据头-订单类别#，当值为仪表时，改成元器件，其他为系统 --,[结算类别]=(SELECT TOP 1 CASE WHEN s.FCAPTION = '仪表' THEN '元器件' ELSE s.FCAPTION END FROM T_META_FORMENUMITEM R WITH(NOLOCK) --LEFT JOIN T_META_FORMENUMITEM_L S WITH(NOLOCK) ON S.FENUMID = R.FENUMID AND S.FLOCALEID = 2052 -- WHERE r.FVALUE = A.F_OrderType ) ,'' 结算类别,'' 结算类型 --,(SELECT TOP 1 CASE WHEN S.FCAPTION = '仪表' AND a.F_XMYSSJ <> '' THEN '项目已做完，回部分款' -- WHEN S.FCAPTION = '仪表' AND a.F_XMYSSJ = '' THEN '项目未完工，欠款合同' -- WHEN S.FCAPTION <> '仪表' AND w.FID IS NOT NULL THEN '款到发货' -- WHEN S.FCAPTION <> '仪表' AND w.FID IS NOT NULL THEN '协议付款' -- ELSE '预收款+到货/调试款+质保金' END FROM T_META_FORMENUMITEM R --LEFT JOIN T_META_FORMENUMITEM_L S ON S.FENUMID = R.FENUMID AND S.FLOCALEID = 2052 -- WHERE r.FVALUE = A.F_OrderType) AS '结算类型' ,'' AS '转专责小组时间' ,'' AS '是否超账龄转出' ,'' AS '超账龄是否申请归还大区' --待定 ,'' AS '反馈表商务人员引用' --待定 ,'' AS '反馈表引用分组' --待定 ,'' AS '取值销售' --待定 ,CASE WHEN FIsBadDebt = 1 THEN '是' ELSE '否' END AS '是否坏账' --取销售订单上的是否坏账 ,CASE WHEN AB.F_PAPERCONTRACT IS NULL THEN '否' ELSE '是' END AS '是否预发货' --取销售订单上的是否预发货 ,CASE WHEN ISNULL(A.F_PURCHASINGPRO,'') = 'LH' THEN '龙湖' WHEN ISNULL(A.F_PURCHASINGPRO,'') = 'BL' THEN '保利地产' WHEN ISNULL(A.F_PURCHASINGPRO,'') = 'HR' THEN '华润' WHEN ISNULL(A.F_PURCHASINGPRO,'') = 'N' THEN '否' WHEN ISNULL(A.F_PURCHASINGPRO,'') = 'HEF' THEN '华润非集采' WHEN ISNULL(A.F_PURCHASINGPRO,'') = 'LD' THEN '绿地' ELSE '' END AS '是否集采项目' ,B.FDATAVALUE AS '合同类型' ,[是否已存在]=0 ,[应收比例]=w.FRECADVANCERATE ,A.FSALEORGID AS '组织ID' ,W.F_DAY AS '天数' ,a.FBILLNO [销售订单编号] ,[整单正数订单总额]=ISNULL((SELECT SUM(FAllAmount) FROM T_SAL_ORDERENTRY_F aa WHERE aa.FID=a.FID AND aa.FAllAmount>0),0) ,[收款条件累计核销金额]=w.F_MBBA_DECIMAL_UKY ,(SELECT ll.FNAME FROM dbo.T_BD_CURRENCY_L ll WITH(NOLOCK) where ll.FCURRENCYID=fin.FSettleCurrId ) AS [结算币别] ,fin.FSettleCurrId [结算币别ID] , fin.FExchangeRate [汇率] , (0) [客户信用额度] ,Cccc.FNUMBER AS [客户编码] ,0 [累计到款金额(原币)],0 [应收款合计(原币)] ,ISNULL(a.F_expenses,0) [运杂费] -----------------from 表-------- FROM T_SAL_ORDER A WITH(NOLOCK) LEFT JOIN ( SELECT DISTINCT x.FNAME +'--'+CONVERT(NVARCHAR(50), CONVERT(INT, w.F_DAY))+'天--'+CONVERT(NVARCHAR(50),CONVERT(DECIMAL(18,2), w.FRECADVANCERATE))+'%' AS FNAME ,w.FID,w.F_PMSDate ,W.FRECADVANCEAMOUNT,w.F_DAY ,w.F_MBBA_DECIMAL_UKY ,w.FENTRYID, W.F_RecConditionId ,w.FRECADVANCERATE ,x.FNAME AS fname2 FROM T_SAL_ORDERPLAN w WITH(NOLOCK) LEFT JOIN T_BD_RecCondition_L X ON X.FID = W.F_RecConditionId ) w ON w.FID=A.FID LEFT JOIN (SELECT a2.FENTRYID,a2.FDATAVALUE FROM T_BAS_ASSISTANTDATAENTRY a1 JOIN T_BAS_ASSISTANTDATAENTRY_L a2 ON a1.FENTRYID = a2.FENTRYID WHERE a1.FID = '670cc977263353') B ON A.F_ORDERCATEGORY = B.FENTRYID LEFT JOIN T_BD_CUSTOMER Cccc WITH(NOLOCK) ON Cccc.FCUSTID= A.FCUSTID LEFT JOIN T_BD_CUSTOMER_L C WITH(NOLOCK) ON C.FCUSTID= A.FCUSTID AND C.FLOCALEID= 2052 LEFT JOIN T_PerformanceSharingEntry TPS ON TPS.fid=a.FID LEFT JOIN V_BD_SALESMAN_L L WITH(NOLOCK) ON L.fid = A.FSALERID LEFT JOIN T_PerformanceSharing M WITH(NOLOCK) ON M.FSOURCEBILLNO = A.FBILLNO AND M.FDOCUMENTSTATUS = 'C' LEFT JOIN T_PerformanceSharingEntry N WITH(NOLOCK) ON N.FID=M.FID LEFT JOIN V_BD_SALESMAN_L O WITH(NOLOCK) ON O.fid= N.FSALEID LEFT JOIN (SELECT DISTINCT ACO22.名称,ACO22.明细区域,ACO22.区域 FROM ERPTOHR.HYHRV3.dbo.v_erp_empinfo ACO22 ) ACO ON ACO.名称=O.FNAME AND ISNULL(ACO.明细区域,'') != '' LEFT JOIN (SELECT DISTINCT ACL22.名称,ACL22.明细区域 ,ACL22.区域 FROM ERPTOHR.HYHRV3.dbo.v_erp_empinfo ACL22 ) ACL ON ACL.名称=L.FNAME AND ISNULL(ACL.明细区域,'') != '' LEFT JOIN T_SAL_ORDERFIN fin WITH(NOLOCK) ON a.FID=fin.FID LEFT JOIN T_BD_CUSTOMER I WITH(NOLOCK) ON I .FCUSTID = A.FCUSTID LEFT JOIN V_ASSISTANTDATA K WITH(NOLOCK) ON K.FENTRYID = i.FCompanyNature LEFT JOIN V_ASSISTANTDATA P WITH(NOLOCK) ON P.FENTRYID = I.F_CREDITCLASSIFICATION LEFT JOIN T_BAS_PREBDONE_L G WITH(NOLOCK) ON G.FID = A.F_projectname AND G.FLocaleID =2052 --LEFT JOIN --( -- SELECT F_Paper ,CONVERT(NVARCHAR(10), MAX(FDATE),23) AS '开票日期' -- ,CONVERT(NVARCHAR(10), MAX(FDATE),23) AS '没有红色的开票日期' ,A.FSALEORGID -- FROM t_AR_receivable A WITH(NOLOCK) -- INNER JOIN T_AR_RECEIVABLEENTRY B WITH(NOLOCK) ON A.FID = B.FID -- WHERE A.FDOCUMENTSTATUS = 'C' AND A.FCancelStatus = 'A' -- GROUP BY F_Paper ,A.FSALEORGID --) T ON T.F_PAPER = A.F_contractnumber AND t.FSALEORGID=a.FSALEORGID -- LEFT JOIN -- ( -- SELECT F_PaperNumber, FSALEORGID ,MAX(F_GeneralInvoice) AS '开票日期' FROM ( -- SELECT F_PaperNumber, t0.FSALEORGID, -- CASE WHEN F_ORDERCATEGORY ='67283ced82f38d' THEN FINVOICEDATE -- WHEN F_ORDERCATEGORY !='67283ced82f38d' AND F_GENERALINVOICE='Y' THEN NULL ELSE FINVOICEDATE END AS F_GeneralInvoice -- --FAFTERTOTALTAX --FROM T_IV_SALESIC t0 WITH(NOLOCK) --INNER JOIN T_SAL_ORDER aa1 WITH(NOLOCK) ON t0.F_PAPERNUMBER=aa1.F_contractnumber AND t0.FSETTLEORGID=aa1.FSALEORGID -- WHERE T0.FREDBLUE=0 --) tt --GROUP BY F_PaperNumber, FSALEORGID -- ) T ON T.F_PaperNumber = A.F_contractnumber AND T.FSALEORGID=a.FSALEORGID LEFT JOIN V_ASSISTANTDATA U WITH(NOLOCK) ON U.FENTRYID = A.F_Ordercategory LEFT JOIN ( SELECT F_PaperNumber, FSALEORGID ,SUM(FAFTERTOTALTAXFOR) AS '发票总金额' FROM ( SELECT F_PaperNumber, t0.FSALEORGID,t0.FCURRENCYID,fin.FSettleCurrId ,CASE WHEN F_ORDERCATEGORY ='67283ced82f38d' THEN FAFTERTOTALTAXFOR WHEN F_ORDERCATEGORY !='67283ced82f38d' AND F_GENERALINVOICE='Y' THEN 0 ELSE FAFTERTOTALTAXFOR END AS FAFTERTOTALTAXFOR --FAFTERTOTALTAX FROM T_IV_SALESIC t0 WITH(NOLOCK) INNER JOIN T_SAL_ORDER aa1 WITH(NOLOCK) ON t0.F_PAPERNUMBER=aa1.F_contractnumber AND t0.FSETTLEORGID=aa1.FSALEORGID INNER JOIN dbo.T_SAL_ORDERFIN fin ON aa1.FID=fin.FID ) tt GROUP BY F_PaperNumber, FSALEORGID ) AD ON AD.F_PaperNumber = A.F_contractnumber AND ad.FSALEORGID=a.FSALEORGID LEFT JOIN (SELECT distinct F_Papercontract,FSALEORGID FROM T_SAL_DELIVERYNOTICE WITH(NOLOCK) WHERE F_SFYFH ='是') AB ON AB.F_PAPERCONTRACT = A.F_contractnumber AND AB.FSALEORGID=a.FSaleOrgId WHERE ( ( A.FSALEORGID = 100302 -- AND ( -- (ACO.区域 != '碳索智控' OR ACO.区域 IS null ) --AND (ACL.区域 != '碳索智控' OR ACL.区域 IS null ) AND C.FNAME NOT IN ('派诺科技（香港）有限公司','武汉派诺科技发展有限公司','客户-样机使用' ,'珠海华夏云联技术有限公司','珠海兴诺能源技术有限公司','广东碳索智控科技有限公司','广东诺瓦数能科技有限公司') --AND L.FNAME NOT IN ('兴诺总','陈智勇') ) OR A.FSALEORGID != 100302) AND A.FDocumentStatus = 'C' --AND EXISTS(SELECT 1 FROM T_SAL_ORDERENTRY bb WHERE bb.fid=a.fid ) ) A --where [销售单主键ID]= 102802 --where [分摊比例]< 100 --where [预计到期时间] !='' WHERE [整单订单总额]<>0 AND SUBSTRING([纸质合同号],1,1) != 'Z' ORDER BY A.[销售单主键ID],[销售员ID],[收款条件主键ID]; UPDATE [ZRP_YingShouMingXi_yuyubo] SET [发货日期2]=ISNULL((SELECT STRING_AGG(CONVERT(NVARCHAR(10),SAL.FDATE,23) +'---'+FORMAT( ISNULL(B.FBillAllAmount_LC,0),'0.00')+'元' ,',') AS '发货日期' FROM T_SAL_DELIVERYNOTICE SAL WITH(NOLOCK) INNER JOIN T_SAL_DELIVERYNOTICEFIN B WITH(NOLOCK) ON SAL.FID =B.FID WHERE SAL.FDOCUMENTSTATUS= 'C' AND SAL.F_PAPERCONTRACT = [纸质合同号]),'') , [开票日期2]= ISNULL( ( SELECT STRING_AGG(CONVERT(NVARCHAR(10),kaipiaoriqi,23)+'---'+FORMAT( F_GeneralInvoice,'0.00')+'元',',')[开票日期2] FROM ( SELECT F_PaperNumber, t0.FSALEORGID, CASE WHEN F_ORDERCATEGORY ='67283ced82f38d' THEN FINVOICEDATE WHEN F_ORDERCATEGORY !='67283ced82f38d' AND F_GENERALINVOICE='Y' THEN NULL ELSE FINVOICEDATE END AS kaipiaoriqi ,CASE WHEN F_ORDERCATEGORY ='67283ced82f38d' THEN FAFTERTOTALTAX WHEN F_ORDERCATEGORY !='67283ced82f38d' AND F_GENERALINVOICE='Y' THEN 0 ELSE FAFTERTOTALTAX END AS F_GeneralInvoice --FAFTERTOTALTAX FROM T_IV_SALESIC t0 WITH(NOLOCK) INNER JOIN T_SAL_ORDER aa1 WITH(NOLOCK) ON t0.F_PAPERNUMBER=aa1.F_contractnumber AND t0.FSETTLEORGID=aa1.FSALEORGID WHERE T0.FREDBLUE=0 ) tt WHERE ISNULL(kaipiaoriqi,'')!='' AND F_PaperNumber=[纸质合同号] AND FSALEORGID=组织ID GROUP BY F_PaperNumber, FSALEORGID ),''); --【订单总额、累计发货金额、累计退货金额、累计开票金额、累计到款金额、应收款合计、未出货总额、实际已出货应收款合计、】的明细需要去重 -- 补充一点，这些字段还要按销售员分摊进行区分，比如订单金额100元，销售员A 30%，销售员B 70%，这些字段就会显示 A显示30元，B显示70元 update [ZRP_YingShouMingXi_yuyubo] set [订单总额]=[整单正数订单总额]*[分摊比例]*0.01 ,[累计发货金额]=[整单累计发货金额]*[分摊比例]*0.01 ,[累计退货金额]=[整单累计退货金额]*[分摊比例]*0.01 ,[累计开票金额]=[整单累计开票金额]*[分摊比例]*0.01 ,[累计到款金额]=[整单累计到款金额]*[分摊比例]*0.01 ,[收款条件分摊比例]=ISNULL([收款条件分摊比例],100) ,[运杂费]=[运杂费]*[分摊比例]*0.01*ISNULL([收款条件分摊比例],100)*0.01 --UPDATE [ZRP_YingShouMingXi_yuyubo] set [分摊金额]=[订单总额]; UPDATE [ZRP_YingShouMingXi_yuyubo] set [应收款合计]=CASE WHEN [整单正数订单总额]>0 then ABS([订单总额])-ABS(([整单累计退货金额]+[整单累计到款金额])*[分摊比例]*0.01) +[运杂费] ELSE 0 end ,[未出货总额]=CASE WHEN [整单正数订单总额]>0 then ABS([订单总额])-ABS([整单累计发货金额]*[分摊比例]*0.01) ELSE 0 end --,[到期时间]='2022-01-01' --,[到期时间]=CASE WHEN [预计到期时间] IS NOT NULL AND [预计到期时间] <= GETDATE() AND [预计到期时间] >'1990-01-01' AND [订单总额]>0 THEN [预计到期时间] ELSE NULL END --,[到期金额]=CASE WHEN [预计到期时间] IS NOT NULL AND [预计到期时间] <= GETDATE() AND [预计到期时间] >'1990-01-01' AND [订单总额]>0 THEN([整单累计发货金额]-[整单累计退货金额]-[整单累计到款金额])*[分摊比例]*0.01*ISNULL([收款条件分摊比例],100)*0.01 ELSE 0 END ,[实际已出货应收款合计]=CASE WHEN [整单正数订单总额] >0 THEN ([整单累计发货金额]-[整单累计退货金额]- [整单累计到款金额] )*[分摊比例]*0.01 ELSE 0 end ,[收款人]=CASE WHEN [账龄月]>12 THEN '转应收专责小组' ELSE [销售员] END ,[项目阶段]= CASE WHEN [项目验收时间]>='1990-01-01' AND [项目验收时间] IS NOT NULL THEN '验收完成' WHEN [项目调试时间]>='1990-01-01' AND [项目调试时间] IS NOT NULL THEN '调试完成' WHEN ISNULL([项目进度],'') <> '' THEN [项目进度] WHEN ISNULL([整单累计发货金额],0) = 0 THEN '未启动' WHEN ISNULL( [整单累计发货金额],0) = ISNULL([整单正数订单总额],0) THEN '发货完成' WHEN ISNULL([整单正数订单总额],0) > ISNULL( [整单累计发货金额],0) THEN '发货中' END ; update [ZRP_YingShouMingXi_yuyubo] set [到期时间]=CASE WHEN [预计到期时间] IS NOT NULL AND [预计到期时间] <= GETDATE() AND [预计到期时间] >'1990-01-01' AND [订单总额]>0 THEN [预计到期时间] ELSE NULL END ,[到期金额]=CASE WHEN [预计到期时间] IS NOT NULL AND [预计到期时间] <= GETDATE() AND [预计到期时间] >'1990-01-01' AND [订单总额]>0 THEN ([整单正数订单总额]-[整单累计退货金额])*[分摊比例]*0.01*[收款条件分摊比例]*0.01 - [收款条件累计核销金额]*[分摊比例]*0.01+[运杂费] ELSE 0 END; --WHERE [销售单主键ID]=110181; update [ZRP_YingShouMingXi_yuyubo] SET [订单日期]=CASE WHEN [订单日期] > '1990-01-01' THEN [订单日期] ELSE NULL END ,[发货日期]=CASE WHEN [发货日期] > '1990-01-01' THEN [发货日期] ELSE NULL END ,[开票日期]=CASE WHEN [开票日期] > '1990-01-01' THEN [开票日期] ELSE NULL END --,[预计到期时间]=CASE WHEN [预计到期时间] > '1990-01-01' AND [预计到期时间] >GETDATE() THEN [预计到期时间] ELSE NULL END ,[质保金到期时间]=CASE WHEN [质保金到期时间] > '1990-01-01' THEN [质保金到期时间] ELSE NULL END ,[转专责小组时间]=CASE WHEN [转专责小组时间] > '1990-01-01' THEN [转专责小组时间] ELSE NULL END , [账龄月]= ISNULL( ROUND( DATEDIFF(DAY,[到期时间],GETDATE()) / 30,0) ,0 ) UPDATE [ZRP_YingShouMingXi_yuyubo] SET [账龄-阶段]=CASE WHEN [账龄月]>=1 AND [账龄月]<=6 THEN '1-6个月' WHEN [账龄月]>=7 AND [账龄月]<=12 THEN '7-12个月' WHEN [账龄月]>=13 AND [账龄月]<=24 THEN '1-2年' WHEN [账龄月]>=25 AND [账龄月]<=36 THEN '2-3年' WHEN [账龄月]>=37 AND [账龄月]<=48 THEN '3-4年' WHEN [账龄月]>=49 THEN '5年以上' ELSE '' END --,[风险评估级别]=CASE WHEN [账龄月]>=1 AND [账龄月]<=6 THEN '正常' WHEN [账龄月]>=7 AND [账龄月]<=12 THEN '催款函' WHEN [账龄月]>=13 AND [账龄月]<=18 --THEN '律师' WHEN [账龄月]>=19 THEN '诉讼' ELSE '' END ,[1-6个月]=CASE WHEN [账龄月]>=1 AND [账龄月]<=6 THEN ([整单正数订单总额]-[整单累计退货金额])*[分摊比例]*0.01*[收款条件分摊比例]*0.01 - [收款条件累计核销金额]*[分摊比例]*0.01 +[运杂费] ELSE 0 END ,[7-12个月]=CASE WHEN [账龄月]>=7 AND [账龄月]<=12 THEN ([整单正数订单总额]-[整单累计退货金额])*[分摊比例]*0.01*[收款条件分摊比例]*0.01 - [收款条件累计核销金额]*[分摊比例]*0.01 +[运杂费] ELSE 0 END ,[1-2年]=CASE WHEN [账龄月]>=13 AND [账龄月]<=24 THEN ([整单正数订单总额]-[整单累计退货金额])*[分摊比例]*0.01*[收款条件分摊比例]*0.01 - [收款条件累计核销金额]*[分摊比例]*0.01+[运杂费] ELSE 0 END ,[2-3年]=CASE WHEN [账龄月]>=25 AND [账龄月]<=36 THEN ([整单正数订单总额]-[整单累计退货金额])*[分摊比例]*0.01*[收款条件分摊比例]*0.01 - [收款条件累计核销金额]*[分摊比例]*0.01 +[运杂费] ELSE 0 END ,[4-5年]=CASE WHEN [账龄月]>=37 AND [账龄月]<=60 THEN ([整单正数订单总额]-[整单累计退货金额])*[分摊比例]*0.01*[收款条件分摊比例]*0.01 - [收款条件累计核销金额]*[分摊比例]*0.01 +[运杂费] ELSE 0 END ,[5年以上]=CASE WHEN [账龄月]> 60 THEN (([整单正数订单总额]-[整单累计退货金额])*[收款条件分摊比例]*0.01 -[收款条件累计核销金额])*[分摊比例]*0.01 +[运杂费] ELSE 0 END update [ZRP_YingShouMingXi_yuyubo] SET [预计到期时间]=CASE WHEN [预计到期时间] > '1990-01-01' AND [预计到期时间] >GETDATE() THEN [预计到期时间] ELSE NULL END ,[未到期金额]=CASE WHEN (([预计到期时间] <'1990-01-01'OR ([预计到期时间] IS NULL AND [到期时间] IS null)) OR [预计到期时间] > GETDATE()) AND [订单总额]>0 THEN ([整单正数订单总额]-[整单累计退货金额])*[分摊比例]*0.01*[收款条件分摊比例]*0.01 - [收款条件累计核销金额]*[分摊比例]*0.01 +[运杂费] ELSE 0 END ,[是否超账龄转出]=CASE WHEN [账龄月] > 12 THEN '是' ELSE '否' END ; --, fin.FExchangeRate [汇率] -- , (0) [客户信用额度] -- ,Cccc.FNUMBER AS [客户编码] -- ,0 [累计到款金额(原币)],0 [应收款合计(原币)] [订单总额(原币)] -- ,ISNULL(a.F_expenses,0) [运杂费] update [ZRP_YingShouMingXi_yuyubo] SET [累计到款金额(原币)]=[累计到款金额],[应收款合计(原币)]=[应收款合计], [订单总额(原币)]= [订单总额],[累计退货金额(原币)]=[累计退货金额]; update [ZRP_YingShouMingXi_yuyubo] SET [累计到款金额]=[累计到款金额]*[汇率],[应收款合计]=[应收款合计]*[汇率], [订单总额]= [订单总额]*[汇率] ,累计发货金额=累计发货金额*[汇率],[累计退货金额]=[累计退货金额]*[汇率] ; -- 1.当字段“是否集采项目”为“华润”“龙湖”“绿地”时，则“客户信用”取“是否集团项目”字段。其余不变 --2.当字段“购货单位”为 --“联通（广东）产业互联网有限公司” --“联通系统集成有限公司广东省分公司” --“联通华盛通信有限公司甘肃分公司” --“中国联合网络通信有限公司”——此为左包含，所有地市公司都算入 --则“客户信用”填为“联通” UPDATE [ZRP_YingShouMingXi_yuyubo] SET [客户信用]=[是否集采项目] WHERE [是否集采项目] IN ('华润','龙湖','绿地') UPDATE [ZRP_YingShouMingXi_yuyubo] SET [客户信用]='联通' WHERE [购货单位] LIKE '联通%' OR [购货单位] LIKE '中国联合网络通信有限公司%' UPDATE [ZRP_YingShouMingXi_yuyubo] SET [客户信用额度]=bb.FCREDITAMOUNT FROM (SELECT FCreditAmount,b.FUseOrgId,b.FNUMBER FROM T_CRE_CUSTARCHIVESENTRY a LEFT JOIN T_BD_CUSTOMER b ON a.FObjectId=b.FCUSTID WHERE FCreditStatus='A' AND FRowAuditStatus='A') bb WHERE bb.FNUMBER=[客户编码] --SELECT TOP 100 [发货日期]=ISNULL((SELECT STRING_AGG(CONVERT(NVARCHAR(10),SAL.FDATE,23)+'---'+CONVERT(NVARCHAR(100),ROUND(B.FBillAllAmount_LC,2)),',')) AS '发货日期' FROM T_SAL_DELIVERYNOTICE SAL WITH(NOLOCK) --INNER JOIN T_SAL_DELIVERYNOTICEFIN B WITH(NOLOCK) ON SAL.FID =B.FID --WHERE SAL.FDOCUMENTSTATUS= 'C' AND SAL.F_PAPERCONTRACT = A.[纸质合同号]),'') ---- ,[整单累计发货金额]=ISNULL( (SELECT SUM(B.FBillAllAmount_LC) AS '累计发货金额' FROM T_SAL_DELIVERYNOTICE SAL WITH(NOLOCK) ----INNER JOIN T_SAL_DELIVERYNOTICEFIN B WITH(NOLOCK) ON SAL.FID =B.FID ----WHERE SAL.FDOCUMENTSTATUS= 'C' AND SAL.F_PAPERCONTRACT = A.F_contractnumber AND SAL.FSALEORGID=a.FSALEORGID),0) --FROM [ZRP_YingShouMingXi_yuyubo] A --update [ReceivablesDetailsTable] set [F_001]=b.[销售单主键ID],[F_002]=b.[销售员],[F_003]=b.[SBU],[F_004]=B.[明细区域],[F_005]=b.[购货单位],[F_006]=b.[公司性质] --,[F_007]=[客户信用],[F_008]=[纸质合同号],[F_009]=[项目名称],[F_010]=[订单总额],[F_011]=[签订年份],[F_012]=CONVERT(NVARCHAR(20),[订单日期],23),[F_013]=[收款条件],[F_014]=CONVERT(NVARCHAR(20),[发货日期],23) --,[F_015]=[应收比例],[F_016]=CONVERT(NVARCHAR(20),[开票日期],23),[F_017]=[累计发货金额],[F_018]=[累计退货金额],[F_019]=[累计开票金额],[F_020]=[累计到款金额],[F_021]=[应收款合计] --,[F_022]=[未出货总额],[F_023]=CONVERT(NVARCHAR(20),[预计到期时间],23),[F_024]=[未到期金额],[F_025]=[实际已出货应收款合计],[F_026]=CONVERT(NVARCHAR(20),[到期时间],23) ,[F_027]=[到期金额],[F_028]=[收款人] --,[F_029]=[项目阶段],[F_030]=[项目进度],[F_031]=[项目调试时间],[F_032]=[项目验收时间],[F_033]=[质保金天数],[F_034]=CONVERT(NVARCHAR(20),[质保金到期时间],23),[F_035]=[是否提前验收] --,[F_036]=[账龄月],[F_037]=[账龄-阶段],[F_038]=[1-6个月],[F_039]=[7-12个月],[F_040]=[13-18个月],[F_041]=[19个月以上],[F_042]=[1年以上],[F_043]=[风险评估级别] --,[F_044]=[结算类别],[F_045]=[结算类型],[F_046]=CONVERT(NVARCHAR(20),[转专责小组时间],23),[F_047]=[是否超账龄转出],[F_048]=[超账龄是否申请归还大区],[F_049]=[创建人] --,[F_050]=[反馈表引用分组],[F_051]=[取值销售],[F_052]=[是否坏账],[F_053]=[是否预发货] --,[F_059]=[合同类型],[F_000]=[组织ID],[F_062]=[是否集采项目],[F_063]=[天数],[EditTime]=GETDATE() --,[F_064]=[1-2年],[F_065]=[2-3年],[F_066]=[3-4年],[F_067]=[4-5年],[F_068]=[5年以上] --FROM [ZRP_YingShouMingXi_yuyubo] B --WHERE [ReceivablesDetailsTable].[销售员ID]=b.[销售员ID] AND [ReceivablesDetailsTable].[销售单主键ID]=B.[销售单主键ID] --AND [ReceivablesDetailsTable].[收款条件主键ID]=B.[收款条件主键ID] --UPDATE [ZRP_YingShouMingXi_yuyubo] SET [是否已存在]=1 --FROM [ReceivablesDetailsTable] --WHERE [ReceivablesDetailsTable].[销售员ID]=[ZRP_YingShouMingXi_yuyubo].[销售员ID] AND [ReceivablesDetailsTable].[销售单主键ID]=[ZRP_YingShouMingXi_yuyubo].[销售单主键ID] --AND [ReceivablesDetailsTable].[收款条件主键ID]=[ZRP_YingShouMingXi_yuyubo].[收款条件主键ID] --DELETE [ReceivablesDetailsTable] WHERE [销售单主键ID] IS NULL OR [销售单主键ID]=0 ; IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('ReceivablesDetailsTableBack') AND type = 'U') begin drop table ReceivablesDetailsTableBack; end SELECT [销售单主键ID],[销售员ID],[收款条件主键ID],[F_061],[F_054],[F_055],[F_056],[F_060],[F_057],[F_058] ,[F_048] INTO ReceivablesDetailsTableBack FROM ReceivablesDetailsTable WHERE ISNULL([F_061],'')!='' OR ISNULL([F_054],'')!='' OR ISNULL([F_055],0)!=0 OR ISNULL([F_056],'')!=''OR ISNULL([F_060],'')!='' OR ISNULL([F_057],'')!=''OR ISNULL([F_058],'')!='' TRUNCATE TABLE ReceivablesDetailsTable; INSERT INTO [ReceivablesDetailsTable] ( [F_001],[F_002],[F_003],[F_004],[F_005],[F_006],[F_007],[F_008],[F_009],[F_010],[F_011],[F_012],[F_013],[F_014],[F_015],[F_016],[F_017],[F_018],[F_019],[F_020] ,[F_021],[F_022],[F_023],[F_024],[F_025],[F_026],[F_027],[F_028],[F_029],[F_030],[F_031],[F_032],[F_033],[F_034],[F_035],[F_036],[F_037],[F_038],[F_039],[F_040], [F_041],[F_042] ,[F_043],[F_044],[F_045],[F_046],[F_047],[F_048],[F_049],[F_050],[F_051],[F_052],[F_053],[F_059],[F_000] ,[F_062],[F_063],[销售单主键ID],[销售员ID],[收款条件主键ID],[EditTime],[IfManualEdit] ,[F_064],[F_065],[F_066],[F_067],[F_068] ,[BiBie] ,[HuiLv] ,[DingDanZongE_YuanBi] ,[ZeRenRen] ,[XinYongEDu] ,[客户编码] ,[DaoKuanJinE_YuanBi] ,[YingShouHeJi_YuanBi] ,[YunZaFei],[LeiJiTuiHuoJinE_YuanBi]) SELECT [销售单主键ID],[销售员],[SBU],[明细区域],[购货单位],[公司性质],[客户信用] ,[纸质合同号],[项目名称],[订单总额],[签订年份],CONVERT(NVARCHAR(20),[订单日期],23),[收款条件] ,[发货日期2] --CONVERT(NVARCHAR(20), [发货日期2],23), ,[应收比例] --,CONVERT(NVARCHAR(20),[开票日期],23), ,[开票日期2] ,[累计发货金额],[累计退货金额],[累计开票金额],[累计到款金额],[应收款合计],[未出货总额],CONVERT(NVARCHAR(20),[预计到期时间],23) ,[未到期金额],[实际已出货应收款合计],CONVERT(NVARCHAR(20),[到期时间],23) ,[到期金额],[收款人],[项目阶段],[项目进度],[项目调试时间],[项目验收时间],[质保金天数],CONVERT(NVARCHAR(20),[质保金到期时间],23),[是否提前验收],[账龄月],[账龄-阶段],[1-6个月],[7-12个月],[13-18个月],[19个月以上] ,[1年以上],[风险评估级别],[结算类别],[结算类型],CONVERT(NVARCHAR(20),[转专责小组时间],23),[是否超账龄转出],[超账龄是否申请归还大区],[创建人],[反馈表引用分组],[取值销售],[是否坏账],[是否预发货] ,[合同类型] ,[组织ID],[是否集采项目],[天数] ,[销售单主键ID],[销售员ID],[收款条件主键ID],GETDATE(),0,[1-2年],[2-3年],[3-4年],[4-5年],[5年以上],[结算币别],[汇率],[订单总额(原币)],[责任人],[客户信用额度] ,[客户编码] ,[累计到款金额(原币)],[应收款合计(原币)],A.运杂费,[累计退货金额(原币)] FROM [ZRP_YingShouMingXi_yuyubo] A WHERE ([整单正数订单总额]-[累计退货金额(原币)])>[累计到款金额(原币)] ORDER BY A.[销售单主键ID],[销售员ID],[收款条件主键ID]; update ReceivablesDetailsTable set F_061=B.F_061, F_054=B.F_054 ,F_055=B.F_055,F_056=B.F_056 ,F_060=B.F_060,F_057=B.F_057,F_058=B.F_058 FROM ReceivablesDetailsTableBack B WHERE ReceivablesDetailsTable.销售单主键ID=B.销售单主键ID AND ReceivablesDetailsTable.销售员ID=B.销售员ID AND ReceivablesDetailsTable.收款条件主键ID=B.收款条件主键ID --DELETE [ReceivablesDetailsTable] --WHERE [销售单主键ID] IN (SELECT DISTINCT [销售单主键ID] FROM [ZRP_YingShouMingXi_yuyubo] WHERE [整单累计到款金额]=[整单订单总额]-[整单累计退货金额]) END GO --TRUNCATE TABLE dbo.ReceivablesDetailsTable; --EXEC PR_YingShouMingXi_yuyubo --SELECT TOP 10 * FROM ReceivablesDetailsTable --SELECT a.FID,b.FID,a.F_AMOUNT,b.F_MBBA_DECIMAL_UKY FROM T_SAL_ORDER a LEFT JOIN T_SAL_ORDERPLAN b --ON a.FID=b.FID WHERE a.F_AMOUNT