我业务员有传值进去 但是却提示没有治疗
ALTER PROCEDURE [dbo].[DFQ_AUA_CWCX_CUSQK] @CUS VARCHAR(20),--客户
@YWY VARCHAR(20),--业务员
@TYPE VARCHAR(20),--客户类别
@JE_F VARCHAR(10), --金额
@JE_T VARCHAR(10), --金额
@ZD VARCHAR(1),
@ASH VARCHAR(1),
@CHWC VARCHAR(1), --出货完成
@CE VARCHAR(1), --目前结欠超过信用额度
@TEMPTABLE VARCHAR(50) --临时表
AS DECLARE @TEMPSQL VARCHAR(7999),@WHERESQL VARCHAR(7999),@OJZNO VARCHAR(20),@OJZNO1 VARCHAR(100),@OJZDT VARCHAR(100),@OJZDTQ VARCHAR(100)
BEGIN
DECLARE @SCF_JZFS VARCHAR(8000)
SELECT @SCF_JZFS=SCF_JZFS FROM SCF_FMS
SET @TEMPSQL='CREATE TABLE '+@TEMPTABLE+' (客户代码 VARCHAR(100),客户简称 VARCHAR(20),客户类别 varchar(20),业务员 VARCHAR(20),上期结转 FLOAT,本期发生$数量 FLOAT,本期发生$金额 FLOAT,
本期折让$LOSS折让 FLOAT,本期折让$收款折让 FLOAT,本期折让$单价折让 FLOAT,本期折让$尾数折让 FLOAT DEFAULT 0,其它应收 FLOAT DEFAULT(0),税额 FLOAT DEFAULT(0),
实收金额 FLOAT,目前结欠 FLOAT,已出货未结账金额 FLOAT,已出货金额 FLOAT,已交接金额 FLOAT,已结算金额 FLOAT,总欠金额 FLOAT,已出货未结账金额1 Float,信用额度 Float)'
EXEC(@TEMPSQL)
SELECT TOP 1 @OJZNO=ACD_code,@OJZDT=CONVERT(VARCHAR(10),ACD_EDTE,120) FROM ACD WHERE STATE='2' AND ID='I' ORDER BY CONVERT(VARCHAR(10),ACD_EDTE,120) DESC
SELECT TOP 1 @OJZDT=CONVERT(VARCHAR(10),ACD_EDTE,120) FROM ACD WHERE STATE='2' AND ID='I' and acd_no=@ojzno
SELECT TOP 1 @OJZDTQ=CONVERT(VARCHAR(10),ACD_EDTE,120)FROM ACD WHERE STATE='2' AND ID='I' AND ISNULL(ACD_CODE,'')='0' ORDER BY CONVERT(VARCHAR(10),ACD_EDTE,120) DESC
SET @WHERESQL='AND 1=1'
IF @CUS<>''
SET @WHERESQL=' AND AUA_CUS='''+@CUS+''' '
SELECT TOP 1 @OJZNO1=ACD_NO,@OJZDT=CONVERT(VARCHAR(10),ACD_EDTE,120) FROM ACD WHERE STATE='2' AND ID='I' ORDER BY CONVERT(VARCHAR(10),ACD_EDTE,120) DESC
SET @TEMPSQL='INSERT INTO '+@TEMPTABLE+' (客户代码,上期结转) SELECT AUA_CUS,Round(ISNULL(SUM(AUA_MAY),0),2) FROM AUA with(nolock) WHERE ID=''I'' and aua_no='''+@OJZNO1+''' '+@WHERESQL+''
IF @CUS<>''
SET @TEMPSQL=@TEMPSQL+' AND AUA_CUS='''+@CUS+''' '
SET @TEMPSQL=@TEMPSQL+' GROUP BY AUA_CUS'
EXEC(@TEMPSQL)
IF ISNULL(@OJZDT,'')='' SET @OJZDT=''
IF ISNULL(@OJZNO,'')='' SET @OJZNO=''
SET @WHERESQL='AND 1=1'
IF @CUS<>''
SET @WHERESQL=' AND ITH_CUS='''+@CUS+''' '
SET @TEMPSQL='INSERT INTO '+@TEMPTABLE+' (客户代码,已出货未结账金额)
SELECT ITH_CUS,SUM(CASE WHEN ITH_TRT IN (''GOO'',''GFO'',''AOO'',''FLC'') THEN ITH_TRM WHEN ITH_TRT IN (''GQI'',''ZLT'') THEN -(ITH_TRM) ELSE 0 END) FROM ITH with(nolock),ECL with(nolock) WHERE ITH_ONO=ECL_LORD AND ITH_ORO=ECL_LINE '+@WHERESQL+' AND ITH.STATE=''2'' AND ISNULL(ITH_ANO,'''')='''' AND ISNULL(ITH_CUS,'''') NOT IN (SELECT 客户代码 FROM '+@TEMPTABLE+')'
IF @CHWC<>'' --AND ITH_TRD>'''+@OJZDTQ+'''
SET @TEMPSQL=@TEMPSQL+' AND ECL_QSHP>=ECL_QORD'
SET @TEMPSQL=@TEMPSQL+' GROUP BY ITH_CUS'
EXEC(@TEMPSQL)
IF @CUS<>'' --已出货在期初之前也算
SET @WHERESQL=' AND ITH_CUS='''+@CUS+''' '
SET @TEMPSQL='INSERT INTO '+@TEMPTABLE+' (客户代码,已出货未结账金额1)
SELECT ITH_CUS,SUM(CASE WHEN ITH_TRT IN (''GOO'',''GFO'',''AOO'',''FLC'') THEN ITH_TRM WHEN ITH_TRT IN (''GQI'',''ZLT'') THEN -(ITH_TRM) ELSE 0 END) FROM ITH with(nolock),ECL with(nolock) WHERE ITH_ONO=ECL_LORD AND ITH_ORO=ECL_LINE '+@WHERESQL+' AND ITH.STATE=''2'' AND ISNULL(ITH_ANO,'''')='''' AND ISNULL(ITH_CUS,'''') NOT IN (SELECT 客户代码 FROM '+@TEMPTABLE+')'
IF @CHWC<>''
SET @TEMPSQL=@TEMPSQL+' AND ECL_QSHP>=ECL_QORD'
SET @TEMPSQL=@TEMPSQL+' GROUP BY ITH_CUS'
EXEC(@TEMPSQL)
SET @WHERESQL='AND 1=1'
IF @CUS<>''
SET @WHERESQL=' AND ACC_CUST='''+@CUS+''' '
SET @TEMPSQL='INSERT INTO '+@TEMPTABLE+' (客户代码)
SELECT DISTINCT ACC_CUST FROM ACI with(nolock) WHERE ACC_DAT>'''+@OJZDT+''' '+@WHERESQL+' AND ISNULL(ACC_CUST,'''') NOT IN (SELECT 客户代码 FROM '+@TEMPTABLE+')'
EXEC(@TEMPSQL)
SET @WHERESQL='AND 1=1'
IF @CUS<>''
SET @WHERESQL=' AND AAG_CUS='''+@CUS+''' '
SET @TEMPSQL='INSERT INTO '+@TEMPTABLE+' (客户代码)
SELECT DISTINCT AAG_CUS FROM AAG with(nolock) WHERE AAG_DAT>'''+@OJZDT+''' '+@WHERESQL+' AND ISNULL(AAG_CUS,'''') NOT IN (SELECT 客户代码 FROM '+@TEMPTABLE+')'
EXEC(@TEMPSQL)
SET @WHERESQL='AND 1=1'
IF @CUS<>''
SET @WHERESQL=' AND ECM_CUST='''+@CUS+''' '
SET @TEMPSQL='INSERT INTO '+@TEMPTABLE+' (客户代码)
SELECT DISTINCT ECM_CUST FROM ECM with(nolock) WHERE ECM_DAT>'''+@OJZDT+''' '+@WHERESQL+' AND ISNULL(ECM_CUST,'''') NOT IN (SELECT 客户代码 FROM '+@TEMPTABLE+')'
EXEC(@TEMPSQL)
IF @SCF_JZFS='B'
BEGIN
SET @TEMPSQL='UPDATE '+@TEMPTABLE+' SET 本期发生$数量= ISNULL((SELECT SUM(case when ith_trt=''GOO'' then ITH_TRQ else -ITH_TRQ end) FROM ITH with(nolock) WHERE ITH_CUS=客户代码 and CONVERT(CHAR(10),ITH_TRD,120)>'''+CONVERT(CHAR(10),@OJZDT,120)+''' AND ITH.STATE=''2'' AND (ITH_TRT=''GOO'' OR ITH_TRT=''GQI'') ),0)'
SELECT 'xx20',@TEMPSQL
EXEC(@TEMPSQL)
SET @TEMPSQL='UPDATE '+@TEMPTABLE+' SET 本期发生$金额= ISNULL((SELECT SUM(CASE WHEN ITH_TRT=''GOO'' THEN Round(ITH_TRM,2) else Round(-ITH_TRM,2) END) FROM ITH with(nolock) WHERE ITH_CUS=客户代码 AND CONVERT(CHAR(10),ITH_TRD,120)>'''+CONVERT(CHAR(10),@OJZDT,120)+''' AND ITH.STATE=''2'' AND (ITH_TRT=''GOO'' OR ITH_TRT=''GQI'')),0)'
EXEC(@TEMPSQL)
END
else
begin
SET @TEMPSQL='UPDATE '+@TEMPTABLE+' SET 本期发生$数量=ISNULL((SELECT SUM(Round(ACC_QTY,2)) FROM ACI with(nolock) WHERE ACC_DAT>'''+@OJZDT+''' AND ACC_CUST=客户代码 And IsNull(ACC_COD,'''')<>''QT''),0)'--ACI.STATE=''2'' AND
EXEC(@TEMPSQL)
--把金额ACC_AMO 转成应收金额ACC_YFJE
SET @TEMPSQL='UPDATE '+@TEMPTABLE+' SET 本期发生$金额=ISNULL((SELECT SUM(Round(IsNUll(ACC_YFJE,0),2)) FROM ACI with(nolock) WHERE ACC_DAT>'''+@OJZDT+''' AND ACC_CUST=客户代码 And IsNull(ACC_COD,'''')<>''QT''),0)'--ACI.STATE=''2'' AND
SELECT @TEMPSQL
EXEC(@TEMPSQL)
end
SET @TEMPSQL='UPDATE '+@TEMPTABLE+' SET 本期折让$单价折让=ISNULL((SELECT IsNULL(SUM(Round(AAG_BPN,2)),0) FROM AAG with(nolock) WHERE AAG_DAT>'''+@OJZDT+''' AND AAG_CUS=客户代码),0)'
EXEC(@TEMPSQL)
SET @TEMPSQL='UPDATE '+@TEMPTABLE+' SET 本期折让$LOSS折让=ISNULL((SELECT ISNulL(SUM(Round(ACC_ZRJE,2)),0) FROM ACI with(nolock) WHERE ACC_DAT>'''+@OJZDT+''' AND ACC_CUST=客户代码),0)'--ACI.STATE=''2'' AND
EXEC(@TEMPSQL)
SET @TEMPSQL='UPDATE '+@TEMPTABLE+' SET 本期折让$LOSS折让=isnull(本期折让$LOSS折让,0)+ISNULL((SELECT IsNULL(SUM(Round(AAG_BAN,2)),0) FROM AAG with(nolock) WHERE AAG_DAT>'''+@OJZDT+''' AND AAG_CUS=客户代码),0)'
EXEC(@TEMPSQL)
SET @TEMPSQL='UPDATE '+@TEMPTABLE+' SET 本期折让$LOSS折让=isnull(本期折让$LOSS折让,0)+ISNULL((SELECT IsNULL(SUM(Round(AAG_LOG,2)),0) FROM AAG with(nolock) WHERE AAG_DAT>'''+@OJZDT+''' AND AAG_CUS=客户代码),0)'
EXEC(@TEMPSQL)
SET @TEMPSQL='UPDATE '+@TEMPTABLE+' SET 本期折让$LOSS折让=isnull(本期折让$LOSS折让,0)+ISNULL((SELECT IsNULL(SUM(Round(AAG_BPN,2)),0) FROM AAG with(nolock) WHERE AAG_DAT>'''+@OJZDT+''' AND AAG_CUS=客户代码),0)'
EXEC(@TEMPSQL)
SET @TEMPSQL='UPDATE '+@TEMPTABLE+' SET 本期折让$收款折让=ISNULL((SELECT ISNULL(SUM(Round(ECM_ZRJE,2)),0) AS SUMTRM FROM ECM with(nolock) WHERE ECM_TRT=''I'' AND STATE=''2'' AND ECM_CUST=客户代码 AND ECM_DAT>'''+@OJZDT+'''),0)'
EXEC(@TEMPSQL)
SET @TEMPSQL='UPDATE '+@TEMPTABLE+' SET 实收金额=ISNULL((SELECT ISnULL(SUM(Round(ECM_TRP,2)),0) AS SUMTRM FROM ECM with(nolock) WHERE ECM_TRT=''I'' AND STATE=''2'' AND ECM_CUST=客户代码 AND ECM_DAT>'''+@OJZDT+'''),0)'
EXEC(@TEMPSQL)
SET @TEMPSQL='UPDATE '+@TEMPTABLE+' SET 税额=ISNULL((SELECT ISNULL(SUM(ISNULL(Round(SPX_TRM,2),0)),0) FROM SPXF with(nolock) WHERE SPX_CUS=客户代码 AND SPX_TRD>'''+@OJZDTQ+''' AND STATE=''2'' ),0)'
EXEC(@TEMPSQL)
SET @TEMPSQL='UPDATE '+@TEMPTABLE+' SET 税额=isnull(税额,0)+ISNULL((SELECT SUM(Round(IsNUll(ACC_AMO,0),2)) FROM ACI with(nolock) WHERE ACC_DAT>'''+@OJZDT+''' AND ACC_CUST=客户代码 And IsNull(ACC_COD,'''')=''QT'' AND
ACC_KM2 NOT IN (Select ACO_NO from ACO where ACO_USE<>0 and ACO_STY<>''0'' AND
(ACO_DES=''货款'' or ACO_DES=''鞋底''))),0)'
EXEC(@TEMPSQL)
SET @TEMPSQL='UPDATE '+@TEMPTABLE+' SET 目前结欠=ISNULL(Round(上期结转,2),0)+ISNULL(Round(本期发生$金额,2),0)+ISNULL(ROund(税额,2),0)-ISNULL(Round(实收金额,2),0)-ISNULL(Round(本期折让$单价折让,2),0)-ISNULL(Round(本期折让$尾数折让,2),0)-ISNULL(Round(本期折让$LOSS折让,2),0)-ISNULL(Round(本期折让$收款折让,2),0)'----ISNULL(Round(本期折让$LOSS折让,2),0) -ISNULL(Round(本期折让$收款折让,2)
EXEC(@TEMPSQL)
IF @CHWC<>''
BEGIN
SET @TEMPSQL='UPDATE '+@TEMPTABLE+' SET 已出货未结账金额=ISNULL((SELECT SUM(CASE WHEN ITH_TRT IN (''GOO'',''GFO'',''AOO'',''FLC'') THEN ITH_TRM WHEN ITH_TRT IN (''GQI'',''ZLT'') THEN -(ITH_TRM) ELSE 0 END) FROM ITH with(nolock),ECL with(nolock) WHERE ITH_ONO=ECL_LORD AND ITH_ORO=ECL_LINE AND ECL_QSHP>=ECL_QORD AND ITH_LINE=客户代码 AND ITH_TRD>'''+@OJZDTQ+''' AND ITH.STATE=''2'' AND ISNULL(ITH_ANO,'''')=''''),0)'
EXEC(@TEMPSQL)
SET @TEMPSQL='UPDATE '+@TEMPTABLE+' SET 已出货未结账金额1=ISNULL((SELECT SUM(CASE WHEN ITH_TRT IN (''GOO'',''GFO'',''AOO'',''FLC'') THEN ITH_TRM WHEN ITH_TRT IN (''GQI'',''ZLT'') THEN -(ITH_TRM) ELSE 0 END) FROM ITH with(nolock),ECL with(nolock) WHERE ITH_ONO=ECL_LORD AND ITH_ORO=ECL_LINE AND ECL_QSHP>=ECL_QORD AND ITH_LINE=客户代码 AND ITH.STATE=''2'' AND ISNULL(ITH_ANO,'''')=''''),0)'
EXEC(@TEMPSQL)
END
ELSE
BEGIN
SET @TEMPSQL='UPDATE '+@TEMPTABLE+' SET 已出货未结账金额=ISNULL((SELECT SUM(CASE WHEN ITH_TRT IN (''GOO'',''GFO'',''AOO'',''FLC'') THEN ITH_TRM WHEN ITH_TRT IN (''GQI'',''ZLT'') THEN -(ITH_TRM) ELSE 0 END) FROM ITH with(nolock),ECL with(nolock) WHERE ITH_ONO=ECL_LORD AND ITH_ORO=ECL_LINE AND ITH_LINE=客户代码 AND ITH_TRD>'''+@OJZDTQ+''' AND ITH.STATE=''2'' AND ISNULL(ITH_ANO,'''')=''''),0)'
EXEC(@TEMPSQL)
SET @TEMPSQL='UPDATE '+@TEMPTABLE+' SET 已出货未结账金额1=ISNULL((SELECT SUM(CASE WHEN ITH_TRT IN (''GOO'',''GFO'',''AOO'',''FLC'') THEN ITH_TRM WHEN ITH_TRT IN (''GQI'',''ZLT'') THEN -(ITH_TRM) ELSE 0 END) FROM ITH with(nolock),ECL with(nolock) WHERE ITH_ONO=ECL_LORD AND ITH_ORO=ECL_LINE AND ITH_LINE=客户代码 AND ITH.STATE=''2'' AND ISNULL(ITH_ANO,'''')=''''),0)'
EXEC(@TEMPSQL)
END
SET @TEMPSQL='UPDATE '+@TEMPTABLE+' SET 已出货金额=ISNULL((SELECT SUM(CASE WHEN ITH_TRT IN (''GOO'',''GFO'',''AOO'',''FLC'') THEN ITH_TRM WHEN ITH_TRT IN (''GQI'',''ZLT'') THEN -(ITH_TRM) ELSE 0 END) FROM ITH with(nolock),ECL with(nolock) WHERE ITH_ONO=ECL_LORD AND ITH_ORO=ECL_LINE AND ITH_LINE=客户代码 AND ITH_TRD>'''+@OJZDT+''' AND ITH.STATE=''2''),0)'
EXEC(@TEMPSQL)
SET @TEMPSQL='UPDATE '+@TEMPTABLE+' SET 已交接金额=ISNULL((SELECT SUM(CASE WHEN ITH_TRT IN (''GOO'',''GFO'',''AOO'',''FLC'') THEN ITH_TRM WHEN ITH_TRT IN (''GQI'',''ZLT'') THEN -(ITH_TRM) ELSE 0 END) FROM ITH with(nolock),ECL with(nolock) WHERE ITH_ONO=ECL_LORD AND ITH_ORO=ECL_LINE AND ITH_LINE=客户代码 AND ITH_TRD>'''+@OJZDT+''' and Isnull(ITH_YWJJR,'''')<>'''' AND ITH.STATE=''2''),0)'
EXEC(@TEMPSQL)
SET @TEMPSQL='UPDATE '+@TEMPTABLE+' SET 已结算金额=ISNULL((SELECT Sum(ACC_AMO) FROM ACI WHERE ACC_CUST=客户代码 And ACC_COD=''HK'' And ACC_DAT>'''+@OJZDT+'''),0)'
EXEC(@TEMPSQL)
SET @TEMPSQL='UPDATE '+@TEMPTABLE+' SET 客户简称=CUS_NAMEO FROM CUS with(nolock) WHERE CUS_NO=客户代码'
EXEC(@TEMPSQL)
SET @TEMPSQL='UPDATE '+@TEMPTABLE+' SET 业务员=MEP_NAME FROM CUS with(nolock),MEP with(nolock) WHERE CUS_NO=客户代码 AND CUS_MAN=MEP_NO'
EXEC(@TEMPSQL)
SET @TEMPSQL='UPDATE '+@TEMPTABLE+' SET 客户类别=CUS_TYPE FROM CUS with(nolock) WHERE CUS_NO=客户代码'
EXEC(@TEMPSQL)
SET @TEMPSQL='DELETE '+@TEMPTABLE+' WHERE ISNULL(客户代码,'''')='''''
EXEC(@TEMPSQL)
IF @TYPE<>''
BEGIN
SET @TEMPSQL='DELETE '+@TEMPTABLE+' WHERE ISNULL(客户类别,'''')<>'''+@TYPE+''''
EXEC(@TEMPSQL)
END
IF @YWY<>''
BEGIN
SET @TEMPSQL='DELETE '+@TEMPTABLE+' WHERE ISNULL(业务员,'''')<>'''+@YWY+''''
EXEC(@TEMPSQL)
END
--IF @SCF_JZFS='A'--@ZD<>''
-- BEGIN
-- SET @TEMPSQL='DELETE '+@TEMPTABLE+' WHERE ISNULL(上期结转,0)=0 AND ISNULL(本期发生$数量,0)=0 AND ISNULL(本期发生$金额,0)=0 AND ISNULL(本期折让$LOSS折让,0)=0 AND ISNULL(本期折让$单价折让,0)=0 AND
-- ISNULL(本期折让$尾数折让,0)=0 AND ISNULL(实收金额,0)=0 AND ISNULL(目前结欠,0)=0'
-- EXEC(@TEMPSQL)
-- END
IF @JE_F<>''
BEGIN
SET @TEMPSQL='DELETE '+@TEMPTABLE+' WHERE ISNULL(目前结欠,0)<'''+@JE_F+''''
EXEC(@TEMPSQL)
END
IF @JE_T<>''
BEGIN
SET @TEMPSQL='DELETE '+@TEMPTABLE+' WHERE ISNULL(目前结欠,0)>'''+@JE_T+''''
EXEC(@TEMPSQL)
END
SET @TEMPSQL='DELETE '+@TEMPTABLE+' WHERE ISNULL(上期结转,0)=0 AND ISNULL(本期发生$数量,0)=0 AND ISNULL(本期发生$金额,0)=0 AND ISNULL(本期折让$LOSS折让,0)=0 AND ISNULL(本期折让$单价折让,0)=0 AND
ISNULL(已出货未结账金额,0)=0 AND ISNULL(本期折让$尾数折让,0)=0 AND ISNULL(实收金额,0)=0 AND ISNULL(目前结欠,0)=0 AND ISNULL(税额,0)=0'
EXEC(@TEMPSQL)
if exists(select * from CPD where CPD_CODE='TC')
begin
SET @TEMPSQL='UPDATE '+@TEMPTABLE+' SET 目前结欠=round(目前结欠,0),已出货未结账金额=round(已出货未结账金额,0)'
EXEC(@TEMPSQL)
end
else
begin
SET @TEMPSQL='UPDATE '+@TEMPTABLE+' SET 目前结欠=str(目前结欠,10,2)'
EXEC(@TEMPSQL)
end
SET @TEMPSQL='UPDATE '+@TEMPTABLE+' SET 总欠金额=IsNULL(目前结欠,0)+IsNULL(已出货未结账金额,0)'
EXEC(@TEMPSQL)
---目前结欠为0的不要显示出来
set @tempsql='delete from '+@temptable+' where isnull(总欠金额,0)=0'
exec(@tempsql)
set @tempsql='update '+@temptable+' set 信用额度=isnull(CUS_CRMON,0) from cus where cus_no=客户代码 '
exec(@tempsql)
set @tempsql='update '+@temptable+' set 信用额度=''999999999999'' WHERE ISNULL(信用额度,0)=0'
exec(@tempsql)
IF IsNull(@CE,'')<>''
begin
set @tempsql='delete from '+@temptable+' where isnull(目前结欠,0)<=isnull(信用额度,0)'
exec(@tempsql)
End
END
