796 lines
28 KiB
Transact-SQL
796 lines
28 KiB
Transact-SQL
|
|
|
|
-- 检查存储过程是否存在，存在则删除 P202502080013
|
|
IF OBJECT_ID('dbo.[PR_YingShouSBU_yuyubo]', 'P') IS NOT NULL
|
|
BEGIN
|
|
DROP PROCEDURE dbo.PR_YingShouSBU_yuyubo;
|
|
PRINT '存储过程 dbo.[PR_YingShouSBU_yuyubo] 已删除';
|
|
END
|
|
GO
|
|
|
|
-- 创建新的存储过程
|
|
CREATE PROCEDURE dbo.PR_YingShouSBU_yuyubo @BeginDATE DATETIME='2000-01-01'
|
|
AS
|
|
BEGIN
|
|
UPDATE STATISTICS T_SAL_ORDERENTRY_F;
|
|
UPDATE STATISTICS T_SAL_ORDER;
|
|
EXEC usp_RefreshViews 'ZZV_SalOrder';
|
|
--EXEC usp_RefreshViews 'V_CN_SALORDERANDRECEIVE';
|
|
DELETE [ZZZ_HeXiaoBook] WHERE [ShouKuanFDate]>=@BeginDATE
|
|
OR ShouKuanType>0
|
|
|
|
DECLARE @FDATE DATETIME,@FBILLNO NVARCHAR(200),@ShouKuanFID INT,@FSALEORGID INT ,@F_contractnumber NVARCHAR(2000),@fid2 INT,@fid3 INT,@amount DECIMAL(18,2),@hexiao DECIMAL(18,2)
|
|
,@remark NVARCHAR(200);
|
|
-- 1. 声明游标
|
|
DECLARE cursor_name2 CURSOR FOR
|
|
SELECT FDATE,FBILLNO,FID,FSALEORGID,F_contractnumber,FREMARK FROM T_AR_RECEIVEBILL
|
|
WHERE FDOCUMENTSTATUS='C' AND FDATE>=@BeginDATE
|
|
ORDER BY FDATE
|
|
|
|
OPEN cursor_name2;
|
|
FETCH NEXT FROM cursor_name2
|
|
INTO @FDATE,
|
|
@FBILLNO,
|
|
@ShouKuanFID,
|
|
@FSALEORGID,
|
|
@F_contractnumber,@remark
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
SELECT @hexiao=0;
|
|
---销售的自动核销记录单--
|
|
SELECT @fid2=0,@fid3=0;
|
|
|
|
DECLARE cursor_name33 CURSOR FOR
|
|
SELECT FID FROM dbo.T_AutoWrireRecordEntry2 WHERE FBILLNO3=@FBILLNO
|
|
|
|
OPEN cursor_name33;
|
|
FETCH NEXT FROM cursor_name33
|
|
INTO @fid2
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
SELECT @fid3=0;
|
|
IF (@fid2 > 0)
|
|
BEGIN
|
|
SELECT @FDATE = ISNULL(F_YEWUDATE, FCREATEDATE),
|
|
@fid3 = FID
|
|
FROM T_AutoWrireRecord
|
|
WHERE FID = @fid2 AND FDOCUMENTSTATUS = 'C';
|
|
IF (@fid3 > 0)
|
|
BEGIN
|
|
SELECT @hexiao = 1;
|
|
IF ((SELECT COUNT(1)FROM T_AutoWrireRecordEntry2 WHERE FID = @fid2) < 2)
|
|
BEGIN
|
|
INSERT INTO [dbo].[ZZZ_HeXiaoBook]
|
|
(
|
|
[XiaoShouFid],
|
|
[XiaoShouBillNo],
|
|
[XiaoShouHeTong],
|
|
[ShouKuanFid],
|
|
[ShouKuanBillNo],
|
|
[ShouKuanFDate],
|
|
[BenCiHeXiao],
|
|
[FSaleOrgId],
|
|
Note,
|
|
NoteFid,
|
|
FExchangeRate,
|
|
FSettleCurrId,
|
|
FLOCALCURRID,
|
|
[ShouKuanType],
|
|
Remark
|
|
)
|
|
(SELECT a.FID,
|
|
a.FBILLNO,
|
|
a.F_CONTRACTNUMBER,
|
|
@ShouKuanFID,
|
|
@FBILLNO,
|
|
@FDATE,
|
|
b.FALLAMOUNT22,
|
|
@FSALEORGID,
|
|
'销售的自动核销记录单',
|
|
@fid2,
|
|
dd.FEXCHANGERATE,
|
|
FSETTLECURRID,
|
|
FLOCALCURRID,
|
|
0,
|
|
head.F_NOTE
|
|
FROM T_SAL_ORDER a
|
|
LEFT JOIN T_AutoWrireRecordEntry b
|
|
ON a.FBILLNO = b.FBILLNO2
|
|
LEFT JOIN T_AutoWrireRecordEntry2 c
|
|
ON b.FID = c.FID
|
|
LEFT JOIN T_SAL_ORDERFIN dd
|
|
ON a.FID = dd.FID
|
|
LEFT JOIN T_AutoWrireRecord head
|
|
ON b.FID = head.FID
|
|
WHERE b.FID = @fid2
|
|
AND c.FBILLNO3 = @FBILLNO);
|
|
END;
|
|
ELSE
|
|
BEGIN
|
|
INSERT INTO [dbo].[ZZZ_HeXiaoBook]
|
|
(
|
|
[XiaoShouFid],
|
|
[XiaoShouBillNo],
|
|
[XiaoShouHeTong],
|
|
[ShouKuanFid],
|
|
[ShouKuanBillNo],
|
|
[ShouKuanFDate],
|
|
[BenCiHeXiao],
|
|
[FSaleOrgId],
|
|
Note,
|
|
NoteFid,
|
|
FExchangeRate,
|
|
FSettleCurrId,
|
|
FLOCALCURRID,
|
|
[ShouKuanType],
|
|
Remark
|
|
)
|
|
(SELECT a.FID,
|
|
a.FBILLNO,
|
|
a.F_CONTRACTNUMBER,
|
|
@ShouKuanFID,
|
|
@FBILLNO,
|
|
@FDATE,
|
|
c.FALLAMOUNT33,
|
|
@FSALEORGID,
|
|
'销售的自动核销记录单',
|
|
@fid2,
|
|
dd.FEXCHANGERATE,
|
|
FSETTLECURRID,
|
|
FLOCALCURRID,
|
|
0,
|
|
head.F_NOTE
|
|
FROM T_SAL_ORDER a
|
|
LEFT JOIN T_AutoWrireRecordEntry b
|
|
ON a.FBILLNO = b.FBILLNO2
|
|
LEFT JOIN T_AutoWrireRecordEntry2 c
|
|
ON b.FID = c.FID
|
|
LEFT JOIN T_SAL_ORDERFIN dd
|
|
ON a.FID = dd.FID
|
|
LEFT JOIN T_AutoWrireRecord head
|
|
ON b.FID = head.FID
|
|
WHERE b.FID = @fid2
|
|
AND c.FBILLNO3 = @FBILLNO);
|
|
END;
|
|
END;
|
|
END;
|
|
-- 获取下一条记录
|
|
FETCH NEXT FROM cursor_name33
|
|
INTO @fid2;
|
|
END;
|
|
-- 4. 关闭游标
|
|
CLOSE cursor_name33;
|
|
-- 5. 释放游标资源
|
|
DEALLOCATE cursor_name33;
|
|
|
|
|
|
|
|
------发货通知单的核销记录单--------------
|
|
SELECT @fid2=0,@fid3=0,@F_contractnumber='',@amount=0;
|
|
|
|
DECLARE cursor_name44 CURSOR FOR
|
|
SELECT FID FROM dbo.VRYF_t_Cust_Entry100025 WHERE FBILLNO3=@FBILLNO
|
|
|
|
OPEN cursor_name44;
|
|
FETCH NEXT FROM cursor_name44
|
|
INTO @fid2
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
SELECT @fid3=0;
|
|
SELECT @FDATE=ISNULL(F_YeWuDate,FCREATEDATE),@fid3=FID FROM VRYF_t_Cust100015
|
|
WHERE FID=@fid2 AND FDOCUMENTSTATUS='C'
|
|
IF(@fid3>0)
|
|
BEGIN
|
|
SELECT @F_contractnumber=FCONTRACTNUMBER,@amount=FALLAMOUNT22 FROM VRYF_t_Cust_Entry100024 a WHERE a.FID=@fid2
|
|
SELECT @hexiao=@amount;
|
|
INSERT INTO [dbo].[ZZZ_HeXiaoBook]
|
|
([XiaoShouFid]
|
|
,[XiaoShouBillNo]
|
|
,[XiaoShouHeTong]
|
|
,[ShouKuanFid]
|
|
,[ShouKuanBillNo]
|
|
,[ShouKuanFDate]
|
|
,[BenCiHeXiao]
|
|
,[FSaleOrgId],Note,NoteFid,FExchangeRate,FSettleCurrId,FLOCALCURRID,[ShouKuanType],Remark)
|
|
( SELECT a.fid,a.FBILLNO,a.F_CONTRACTNUMBER,@ShouKuanFID,@FBILLNO,@FDATE,@amount,@FSALEORGID,'发货通知单的核销记录单',@fid2, dd.FExchangeRate,FSettleCurrId,FLOCALCURRID,0
|
|
,@remark FROM T_SAL_ORDER a
|
|
LEFT JOIN T_SAL_ORDERFIN dd ON a.FID=dd.FID
|
|
WHERE a.FSALEORGID=[FSaleOrgId] and a.F_CONTRACTNUMBER=@F_contractnumber)
|
|
END
|
|
FETCH NEXT FROM cursor_name44
|
|
INTO @fid2;
|
|
END;
|
|
-- 4. 关闭游标
|
|
CLOSE cursor_name44;
|
|
-- 5. 释放游标资源
|
|
DEALLOCATE cursor_name44;
|
|
-----应收核销记录表---------
|
|
SELECT @fid2=0,@fid3=0,@F_contractnumber='',@amount=0;
|
|
SELECT FVERIFYDATE,FBILLFORMID, FVERIFYSEQ,FSOURCETYPE,FCURWRITTENOFFAMOUNTFOR,FCURWRITTENOFFAMOUNT,FSRCBILLNO,b.FEXCHANGERATE,FCURTAXMATCHAMOUNT,FCURNOTAXMATCHAMOUNT
|
|
,FSRCBILLID ,b.FCURRENCYID,b.FLOCALCURRID,b.FORDERBILLNO
|
|
INTO #temp2
|
|
FROM T_AR_RECMacthLog a
|
|
LEFT JOIN dbo.T_AR_RECMACTHLOGENTRY b ON a.FID=b.FID
|
|
LEFT JOIN T_BAS_BILLTYPE c ON b.FSOURCETYPE=c.FBILLTYPEID
|
|
LEFT JOIN t_AR_RECEIVEBILL d ON d.FBILLNO=b.FSRCBILLNO
|
|
WHERE FVERIFYSEQ IN (SELECT FVERIFYSEQ FROM T_AR_RECMacthLog a
|
|
LEFT JOIN dbo.T_AR_RECMACTHLOGENTRY b ON a.FID=b.FID
|
|
WHERE b.FSRCBILLNO=@FBILLNO AND b.FSOURCETYPE='36cf265bd8c3452194ed9c83ec5e73d2' AND FVERIFYDATE>=@BeginDATE
|
|
) AND FVERIFYSEQ NOT IN (100135,100138,100042,100141,100139)
|
|
--SELECT * FROM #temp2
|
|
--SELECT @amount= SUM(FCURWRITTENOFFAMOUNT) FROM #temp2 WHERE FBILLFORMID !='AR_RECEIVEBILL'
|
|
SELECT @fid2=FSRCBILLID,@fid3=FVERIFYSEQ FROM #temp2 WHERE FBILLFORMID ='AR_receivable'
|
|
--SELECT @F_contractnumber= F_PAPERPRO FROM T_AR_receivable WHERE fid=@fid2
|
|
--PRINT '合同号：'+@F_contractnumber;
|
|
IF(@fid2>0)
|
|
BEGIN
|
|
SELECT @hexiao=1;
|
|
SELECT @FDATE=FVERIFYDATE FROM #temp2;
|
|
IF((SELECT COUNT(1)
|
|
FROM T_SAL_ORDER a
|
|
LEFT JOIN T_SAL_ORDERFIN dd ON a.FID=dd.FID
|
|
LEFT JOIN
|
|
(SELECT c.FSALEORGID,ak.FORDERNUMBER ,c.FID,SUM(ak.FALLAMOUNTFOR)/(SELECT SUM(FALLAMOUNTFOR) FROM T_AR_RECEIVABLEENTRY akk
|
|
where akk.FID=ak.FID) FALLAMOUNTFOR_D
|
|
FROM T_AR_receivable c
|
|
LEFT JOIN T_AR_RECEIVABLEENTRY ak ON c.FID=ak.FID
|
|
GROUP BY c.FSALEORGID,ak.FORDERNUMBER ,c.FID,ak.FID
|
|
) c
|
|
ON c.FORDERNUMBER=a.FBILLNO AND c.FSALEORGID=a.FSALEORGID AND a.FBILLNO !=''
|
|
LEFT JOIN #temp2 d ON d.FSRCBILLID=c.fid AND d.FBILLFORMID ='AR_receivable'
|
|
WHERE a.FSALEORGID=@FSALEORGID AND d.FBILLFORMID='AR_receivable'
|
|
)>0)
|
|
begin
|
|
INSERT INTO [dbo].[ZZZ_HeXiaoBook]
|
|
([XiaoShouFid]
|
|
,[XiaoShouBillNo]
|
|
,[XiaoShouHeTong]
|
|
,[ShouKuanFid]
|
|
,[ShouKuanBillNo]
|
|
,[ShouKuanFDate]
|
|
,[BenCiHeXiao]
|
|
,[FSaleOrgId],Note,NoteFid,FExchangeRate,FSettleCurrId,FLOCALCURRID,[ShouKuanType],Remark)
|
|
( SELECT a.fid,a.FBILLNO,a.F_CONTRACTNUMBER,@ShouKuanFID,@FBILLNO,d.FVERIFYDATE,FCURWRITTENOFFAMOUNTFOR*FALLAMOUNTFOR_D,@FSALEORGID,'应收核销记录表',d.FVERIFYSEQ
|
|
,dd.FEXCHANGERATE,dd.FSettleCurrId,dd.FLOCALCURRID,0 ,@remark
|
|
FROM T_SAL_ORDER a
|
|
LEFT JOIN T_SAL_ORDERFIN dd ON a.FID=dd.FID
|
|
LEFT JOIN
|
|
(SELECT c.FSALEORGID,ak.FORDERNUMBER ,c.FID,SUM(ak.FALLAMOUNTFOR)/(SELECT SUM(FALLAMOUNTFOR) FROM T_AR_RECEIVABLEENTRY akk
|
|
where akk.FID=ak.FID)
|
|
FALLAMOUNTFOR_D
|
|
FROM T_AR_receivable c
|
|
LEFT JOIN T_AR_RECEIVABLEENTRY ak ON c.FID=ak.FID
|
|
GROUP BY c.FSALEORGID,ak.FORDERNUMBER ,c.FID,ak.FID
|
|
) c
|
|
ON c.FORDERNUMBER=a.FBILLNO AND c.FSALEORGID=a.FSALEORGID AND a.FBILLNO !=''
|
|
LEFT JOIN #temp2 d ON d.FSRCBILLID=c.fid AND d.FBILLFORMID ='AR_receivable'
|
|
WHERE a.FSALEORGID=@FSALEORGID AND d.FBILLFORMID='AR_receivable' )
|
|
end
|
|
ELSE
|
|
begin
|
|
INSERT INTO [dbo].[ZZZ_HeXiaoBook]
|
|
([XiaoShouFid]
|
|
,[XiaoShouBillNo]
|
|
,[XiaoShouHeTong]
|
|
,[ShouKuanFid]
|
|
,[ShouKuanBillNo]
|
|
,[ShouKuanFDate]
|
|
,[BenCiHeXiao]
|
|
,[FSaleOrgId],Note,NoteFid,FExchangeRate,FSettleCurrId,FLOCALCURRID,[ShouKuanType],Remark)
|
|
( SELECT 0,'','',@ShouKuanFID,@FBILLNO,d.FVERIFYDATE,FCURWRITTENOFFAMOUNTFOR,@FSALEORGID,'应收核销记录表期初应收单',FVERIFYSEQ
|
|
,FEXCHANGERATE,FCURRENCYID,FLOCALCURRID,0 ,@remark
|
|
FROM #temp2 d
|
|
WHERE d.FBILLFORMID='AR_RECEIVEBILL' )
|
|
END
|
|
|
|
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
SELECT @fid2=FSRCBILLID,@fid3=FVERIFYSEQ,@hexiao=FCURWRITTENOFFAMOUNTFOR,@FDATE=FVERIFYDATE FROM #temp2 WHERE FSRCBILLNO=@FBILLNO AND FBILLFORMID='AR_RECEIVEBILL'
|
|
IF(@fid2>0)
|
|
BEGIN
|
|
INSERT INTO [dbo].[ZZZ_HeXiaoBook]
|
|
([XiaoShouFid]
|
|
,[XiaoShouBillNo]
|
|
,[XiaoShouHeTong]
|
|
,[ShouKuanFid]
|
|
,[ShouKuanBillNo]
|
|
,[ShouKuanFDate]
|
|
,[BenCiHeXiao]
|
|
,[FSaleOrgId],Note,NoteFid,FExchangeRate,FSettleCurrId,FLOCALCURRID,[ShouKuanType],Remark)
|
|
( SELECT 0,'','',@ShouKuanFID,@FBILLNO,@FDATE,@hexiao,@FSALEORGID,'特殊核销只有收款单',@fid3
|
|
,d.FEXCHANGERATE,FCURRENCYID,FMAINBOOKCURID,0 ,@remark
|
|
FROM T_AR_RECEIVEBILL d
|
|
WHERE d.FBILLNO=@FBILLNO )
|
|
END
|
|
end
|
|
|
|
DROP TABLE #temp2
|
|
--------------收款单来源明细--------
|
|
IF(@hexiao=0)
|
|
begin
|
|
SELECT @fid2=a.FID FROM T_AR_RECEIVEBILL a LEFT JOIN T_AR_RECEIVEBILLSRCENTRY b ON a.FID=b.FID
|
|
WHERE a.fid=@ShouKuanFID
|
|
IF(@fid2>0)
|
|
BEGIN
|
|
SELECT @hexiao=1;
|
|
INSERT INTO [dbo].[ZZZ_HeXiaoBook]
|
|
([XiaoShouFid]
|
|
,[XiaoShouBillNo]
|
|
,[XiaoShouHeTong]
|
|
,[ShouKuanFid]
|
|
,[ShouKuanBillNo]
|
|
,[ShouKuanFDate]
|
|
,[BenCiHeXiao]
|
|
,[FSaleOrgId],Note,NoteFid,FExchangeRate,FSettleCurrId,FLOCALCURRID,[ShouKuanType],Remark)
|
|
( SELECT aa.fid,aa.FBILLNO,aa.F_CONTRACTNUMBER,@ShouKuanFID,@FBILLNO,a.FDATE,b.FREALRECAMOUNT,@FSALEORGID,'收款单来源明细',@fid2
|
|
,dd.FEXCHANGERATE,FSettleCurrId,FLOCALCURRID,0 ,@remark
|
|
FROM T_AR_RECEIVEBILL a LEFT JOIN T_AR_RECEIVEBILLSRCENTRY b ON a.FID=b.FID
|
|
LEFT JOIN dbo.T_SAL_ORDER aa ON b.FSRCBILLID=aa.FID
|
|
LEFT JOIN T_SAL_ORDERFIN dd ON aa.FID=dd.FID
|
|
WHERE a.fid=@ShouKuanFID AND b.FREALRECAMOUNT <>0 )
|
|
END
|
|
end
|
|
--------历史核销记录单--------------
|
|
IF(@FSALEORGID=100302 or @FSALEORGID=100305)
|
|
begin
|
|
SELECT @hexiao=1;
|
|
INSERT INTO [dbo].[ZZZ_HeXiaoBook]
|
|
([XiaoShouFid]
|
|
,[XiaoShouBillNo]
|
|
,[XiaoShouHeTong]
|
|
,[ShouKuanFid]
|
|
,[ShouKuanBillNo]
|
|
,[ShouKuanFDate]
|
|
,[BenCiHeXiao]
|
|
,[FSaleOrgId],Note,NoteFid,FExchangeRate,FSettleCurrId,FLOCALCURRID,[ShouKuanType],Remark)
|
|
( SELECT a.fid,a.FBILLNO,a.F_CONTRACTNUMBER,@ShouKuanFID,@FBILLNO,c.F_Date,F_shareAmount,@FSALEORGID,'历史核销记录单',c.FEntryID ,dd.FEXCHANGERATE,FSettleCurrId,FLOCALCURRID,0 ,@remark
|
|
FROM T_SAL_ORDER a
|
|
RIGHT JOIN VRYF_t_Cust_Entry100021 c ON a.F_CONTRACTNUMBER=c.F_ORDERNO
|
|
LEFT JOIN T_SAL_ORDERFIN dd ON a.FID=dd.FID
|
|
WHERE (a.FSALEORGID in (100302,100305) OR a.fid IS NULL ) AND c.F_Receiptnumber=@FBILLNO AND c.F_ORDERNO NOT LIKE '%待%' )
|
|
END
|
|
|
|
-- 获取下一条记录
|
|
FETCH NEXT FROM cursor_name2
|
|
INTO @FDATE,
|
|
@FBILLNO,
|
|
@ShouKuanFID,
|
|
@FSALEORGID,
|
|
@F_contractnumber
|
|
,@remark;
|
|
END
|
|
|
|
-- 4. 关闭游标
|
|
CLOSE cursor_name2;
|
|
|
|
-- 5. 释放游标资源
|
|
DEALLOCATE cursor_name2;
|
|
|
|
INSERT INTO [dbo].[ZZZ_HeXiaoBook]
|
|
([XiaoShouFid]
|
|
,[XiaoShouBillNo]
|
|
,[XiaoShouHeTong]
|
|
,[ShouKuanFid]
|
|
,[ShouKuanBillNo]
|
|
,[ShouKuanFDate]
|
|
,[BenCiHeXiao]
|
|
,[FSaleOrgId],Note,NoteFid,FExchangeRate,FSettleCurrId,FLOCALCURRID,[ShouKuanType],Remark)
|
|
|
|
( SELECT a.fid AS [XiaoShouFid],a.FBILLNO,a.F_CONTRACTNUMBER,b.FEntryID,head.FBILLNO,head.F_YEWUDATE
|
|
,b.FALLAMOUNT22
|
|
,a.FSaleOrgId,'销售单与销售单调整',head.FID AS heahid,dd.FExchangeRate,FSettleCurrId,FLocalCurrId,2
|
|
,head.F_NOTE
|
|
FROM T_AutoWrireRecordEntry b
|
|
LEFT JOIN T_SAL_ORDER a ON a.FBILLNO=b.FBILLNO2
|
|
LEFT JOIN T_AutoWrireRecordEntry2 c ON b.FID=c.FID
|
|
LEFT JOIN T_SAL_ORDERFIN dd ON a.FID=dd.FID
|
|
LEFT JOIN T_AutoWrireRecord head ON b.FID=head.FID
|
|
WHERE head.FDOCUMENTSTATUS='C' AND c.FID IS NULL
|
|
)
|
|
|
|
|
|
|
|
|
|
--------以下是特殊核销----------
|
|
|
|
SELECT FVERIFYDATE,FBILLFORMID, FVERIFYSEQ,FSOURCETYPE,FCURWRITTENOFFAMOUNTFOR,FCURWRITTENOFFAMOUNT,FSRCBILLNO,b.FEXCHANGERATE,FCURTAXMATCHAMOUNT,FCURNOTAXMATCHAMOUNT ,FSRCBILLID,a.FMATCHMETHODID
|
|
,b.FORDERBILLNO
|
|
INTO #temp222
|
|
FROM T_AR_RECMacthLog a
|
|
LEFT JOIN dbo.T_AR_RECMACTHLOGENTRY b ON a.FID=b.FID
|
|
LEFT JOIN T_BAS_BILLTYPE c ON b.FSOURCETYPE=c.FBILLTYPEID
|
|
WHERE
|
|
--a.FMATCHMETHODID=30
|
|
-- AND
|
|
a.FVERIFYSEQ NOT IN
|
|
(SELECT DISTINCT FVERIFYSEQ from T_AR_RECMacthLog oo LEFT JOIN T_AR_RECMACTHLOGENTRY ll ON oo.FID=ll.FID
|
|
LEFT JOIN T_BAS_BILLTYPE c ON ll.FSOURCETYPE=c.FBILLTYPEID
|
|
WHERE FBILLFORMID='AR_RECEIVEBILL'
|
|
) AND FVERIFYSEQ NOT IN (100135,100138,100042,100140) AND FVERIFYSEQ NOT IN (100135,100138,100042,100141,100139)
|
|
ORDER BY a.FVERIFYSEQ;
|
|
|
|
INSERT INTO [dbo].[ZZZ_HeXiaoBook]
|
|
([XiaoShouFid]
|
|
,[XiaoShouBillNo]
|
|
,[XiaoShouHeTong]
|
|
,[ShouKuanFid]
|
|
,[ShouKuanBillNo]
|
|
,[ShouKuanFDate]
|
|
,[BenCiHeXiao]
|
|
,[FSaleOrgId],Note,NoteFid,FExchangeRate,FSettleCurrId,FLOCALCURRID,[ShouKuanType],Remark)
|
|
(
|
|
SELECT a.fid,a.FBILLNO,a.F_CONTRACTNUMBER,0,d.FSRCBILLNO,FVERIFYDATE,FCURWRITTENOFFAMOUNTFOR*FALLAMOUNTFOR_D,a.FSALEORGID,'特殊核销没有收款单',FVERIFYSEQ
|
|
,dd.FEXCHANGERATE,FSettleCurrId,FLOCALCURRID,1 ,''
|
|
FROM T_SAL_ORDER a
|
|
LEFT JOIN T_SAL_ORDERFIN dd ON a.FID=dd.FID
|
|
INNER JOIN
|
|
(SELECT c.FSALEORGID,ak.FORDERNUMBER ,c.FID,SUM(ak.FALLAMOUNTFOR)/(SELECT CASE WHEN SUM(FALLAMOUNTFOR)=0 THEN 100000000 ELSE SUM(FALLAMOUNTFOR) end FROM T_AR_RECEIVABLEENTRY akk
|
|
where akk.FID=ak.FID) FALLAMOUNTFOR_D
|
|
FROM T_AR_receivable c
|
|
LEFT JOIN T_AR_RECEIVABLEENTRY ak ON c.FID=ak.FID
|
|
GROUP BY c.FSALEORGID,ak.FORDERNUMBER ,c.FID,ak.FID
|
|
) c
|
|
ON c.FORDERNUMBER=a.FBILLNO AND c.FSALEORGID=a.FSALEORGID
|
|
INNER JOIN #temp222 d ON (d.FSRCBILLID=c.fid AND d.FBILLFORMID ='AR_receivable') OR (a.FBILLNO=d.FORDERBILLNO AND a.FBILLNO <> '' )
|
|
WHERE d.FBILLFORMID='AR_receivable' AND a.FDOCUMENTSTATUS='C'
|
|
);
|
|
DROP TABLE #temp222;
|
|
|
|
INSERT INTO [dbo].[ZZZ_HeXiaoBook]
|
|
([XiaoShouFid]
|
|
,[XiaoShouBillNo]
|
|
,[XiaoShouHeTong]
|
|
,[ShouKuanFid]
|
|
,[ShouKuanBillNo]
|
|
,[ShouKuanFDate]
|
|
,[BenCiHeXiao]
|
|
,[FSaleOrgId],Note,NoteFid,FExchangeRate,FSettleCurrId,FLOCALCURRID,[ShouKuanType],Remark)
|
|
( SELECT a.fid,a.FBILLNO,a.F_CONTRACTNUMBER,0,'',c.F_Date,F_shareAmount,a.FSALEORGID,'历史核销记录单没有收款单',c.FEntryID ,dd.FEXCHANGERATE,FSettleCurrId,FLOCALCURRID,2 ,c.F_NOTES
|
|
FROM T_SAL_ORDER a
|
|
INNER JOIN VRYF_t_Cust_Entry100021 c ON a.F_CONTRACTNUMBER=c.F_ORDERNO
|
|
LEFT JOIN T_SAL_ORDERFIN dd ON a.FID=dd.FID
|
|
WHERE (a.FSALEORGID in (100302,100305) OR a.fid IS NULL ) AND c.F_Receiptnumber='' AND c.F_ORDERNO NOT LIKE '%待%' )
|
|
|
|
UPDATE [ZZZ_HeXiaoBook] SET ZhangLing= ISNULL( ROUND( DATEDIFF(DAY,[到期时间],[ZZZ_HeXiaoBook].ShouKuanFDate) / 30,0) ,0 )
|
|
FROM (SELECT a.XiaoShouFid,a.XiaoShouBillNo,[XiaoShouHeTong],a.ShouKuanFDate,b.预计到期时间,b.到期时间 FROM [ZZZ_HeXiaoBook] a
|
|
LEFT JOIN (SELECT b.销售单主键ID,MAX(b.预计到期时间) AS 预计到期时间 ,max (b.到期时间) AS 到期时间 FROM [ZRP_YingShouMingXi_yuyubo] b GROUP BY 销售单主键ID) b
|
|
ON a.XiaoShouFid=b.销售单主键ID) b
|
|
WHERE [ZZZ_HeXiaoBook].XiaoShouFid=b.XiaoShouFid
|
|
|
|
UPDATE T_SAL_ORDER SET F_AMOUNT=0
|
|
WHERE FSALEORGID in (100302,100305)
|
|
|
|
UPDATE T_SAL_ORDER SET F_AMOUNT=b.BenCiHeXiao
|
|
FROM (SELECT SUM(BenCiHeXiao ) BenCiHeXiao,XiaoShouBillNo FROM dbo.ZZZ_HeXiaoBook WHERE [ShouKuanType]!=3
|
|
GROUP BY XiaoShouBillNo) b
|
|
WHERE T_SAL_ORDER.FBILLNO=b.XiaoShouBillNo AND FSALEORGID in (100302,100305) AND F_AMOUNT <> b.BenCiHeXiao AND T_SAL_ORDER.FBILLNO!=''
|
|
|
|
|
|
UPDATE T_AR_RECEIVEBILL SET F_AMOUNT=0
|
|
WHERE FSALEORGID in (100302,100305)
|
|
|
|
UPDATE T_AR_RECEIVEBILL SET F_AMOUNT=b.BenCiHeXiao
|
|
FROM (SELECT SUM(BenCiHeXiao ) BenCiHeXiao,ShouKuanBillNo FROM dbo.ZZZ_HeXiaoBook WHERE [ShouKuanType]!=3
|
|
GROUP BY ShouKuanBillNo) b
|
|
WHERE T_AR_RECEIVEBILL.FBILLNO=b.ShouKuanBillNo AND FSALEORGID in (100302,100305) AND F_AMOUNT <> b.BenCiHeXiao AND T_AR_RECEIVEBILL.FBILLNO!=''
|
|
|
|
|
|
|
|
UPDATE T_AR_RECEIVEBILL SET F_contractnumber=bb.hetong
|
|
FROM ( SELECT a.fid
|
|
,hetong=( SELECT STRING_AGG(XiaoShouHeTong, ',')
|
|
FROM (SELECT DISTINCT XiaoShouHeTong,ShouKuanBillNo FROM dbo.ZZZ_HeXiaoBook cc
|
|
WHERE ShouKuanBillNo!='' AND FSaleOrgId in (100302,100305) AND XiaoShouHeTong IS NOT NULL AND cc.ShouKuanBillNo=a.FBILLNO ) gg)
|
|
FROM T_AR_RECEIVEBILL a
|
|
WHERE a.FSALEORGID in (100302,100305) ) bb
|
|
WHERE FSALEORGID in (100302,100305) AND T_AR_RECEIVEBILL.fid=bb.fid AND bb.hetong IS NOT NULL;
|
|
|
|
PRINT '退款核销流水';
|
|
----------------退款核销流水----------- F_refundAmount F_TuiKuan_Amount
|
|
--SELECT a.fid,a.FBILLNO,b.FBILLNO2,b.FTYPE ,b.FALLAMOUNT22,c.FID,b.fid,c.FEntryID,b.FEntryID,b.FCONTRACTNUMBER
|
|
-- ,c.FBILLNO3,c.FCONTRACTNUMBER2 FROM T_RefundWrireRecord a
|
|
--LEFT JOIN T_RefundWrireRecordEntry b ON a.FID=b.FID
|
|
--LEFT JOIN T_RefundWrireRecordEntry2 c ON a.FID=c.FID
|
|
--WHERE a.FDOCUMENTSTATUS='C'
|
|
|
|
|
|
DECLARE @type1 NVARCHAR(50),
|
|
@tuiKuanBill NVARCHAR(50);
|
|
-- 1. 声明游标
|
|
DECLARE TuiKuanRecord CURSOR FOR
|
|
SELECT F_YEWUDATE,
|
|
FID,
|
|
F_NOTE
|
|
FROM T_RefundWrireRecord
|
|
WHERE FDOCUMENTSTATUS = 'C'
|
|
--AND F_YEWUDATE >= @BeginDATE
|
|
ORDER BY F_YEWUDATE;
|
|
|
|
OPEN TuiKuanRecord;
|
|
FETCH NEXT FROM TuiKuanRecord
|
|
INTO @FDATE,
|
|
@ShouKuanFID,
|
|
@remark;
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
SELECT @hexiao = 0;
|
|
---销售单或者收款单的退款记录--
|
|
SELECT @fid2 = 0,
|
|
@fid3 = 0;
|
|
|
|
DECLARE cursor_TuiKuanRecord1 CURSOR FOR
|
|
SELECT FTYPE,
|
|
FBILLNO2,
|
|
FALLAMOUNT22
|
|
FROM dbo.T_RefundWrireRecordEntry
|
|
WHERE FID = @ShouKuanFID;
|
|
|
|
OPEN cursor_TuiKuanRecord1;
|
|
FETCH NEXT FROM cursor_TuiKuanRecord1
|
|
INTO @type1,
|
|
@FBILLNO,
|
|
@hexiao;
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
SELECT @tuiKuanBill = FBILLNO3,
|
|
@fid2 = FID
|
|
FROM T_RefundWrireRecordEntry2
|
|
WHERE FID = @ShouKuanFID;
|
|
IF (@type1 = '销售订单')
|
|
BEGIN
|
|
IF (
|
|
(
|
|
SELECT COUNT(1)FROM T_RefundWrireRecordEntry2 WHERE FID = @ShouKuanFID
|
|
) < 2
|
|
)
|
|
BEGIN
|
|
INSERT INTO [dbo].[ZZZ_HeXiaoBook]
|
|
(
|
|
[XiaoShouFid],
|
|
[XiaoShouBillNo],
|
|
[XiaoShouHeTong],
|
|
[ShouKuanFid],
|
|
[ShouKuanBillNo],
|
|
[ShouKuanFDate],
|
|
[BenCiHeXiao],
|
|
[FSaleOrgId],
|
|
Note,
|
|
NoteFid,
|
|
FExchangeRate,
|
|
FSettleCurrId,
|
|
FLOCALCURRID,
|
|
[ShouKuanType],
|
|
Remark
|
|
)
|
|
(SELECT a.FID,
|
|
a.FBILLNO,
|
|
a.F_CONTRACTNUMBER,
|
|
@fid2,
|
|
@tuiKuanBill,
|
|
@FDATE,
|
|
@hexiao,
|
|
a.FSALEORGID,
|
|
'销售退款记录单1V多',
|
|
@ShouKuanFID,
|
|
dd.FEXCHANGERATE,
|
|
FSETTLECURRID,
|
|
FLOCALCURRID,
|
|
3,
|
|
@remark
|
|
FROM T_SAL_ORDER a
|
|
LEFT JOIN T_SAL_ORDERFIN dd
|
|
ON a.FID = dd.FID
|
|
WHERE a.FBILLNO = @FBILLNO);
|
|
END;
|
|
ELSE
|
|
BEGIN
|
|
INSERT INTO [dbo].[ZZZ_HeXiaoBook]
|
|
(
|
|
[XiaoShouFid],
|
|
[XiaoShouBillNo],
|
|
[XiaoShouHeTong],
|
|
[ShouKuanFid],
|
|
[ShouKuanBillNo],
|
|
[ShouKuanFDate],
|
|
[BenCiHeXiao],
|
|
[FSaleOrgId],
|
|
Note,
|
|
NoteFid,
|
|
FExchangeRate,
|
|
FSettleCurrId,
|
|
FLOCALCURRID,
|
|
[ShouKuanType],
|
|
Remark
|
|
)
|
|
(SELECT a.FID,
|
|
a.FBILLNO,
|
|
a.F_CONTRACTNUMBER,
|
|
e.FID,
|
|
e.FBILLNO,
|
|
@FDATE,
|
|
c.FALLAMOUNT33,
|
|
a.FSALEORGID,
|
|
'销售退款记录单多V1',
|
|
@ShouKuanFID,
|
|
dd.FEXCHANGERATE,
|
|
FSETTLECURRID,
|
|
FLOCALCURRID,
|
|
3,
|
|
head.F_NOTE
|
|
FROM T_SAL_ORDER a
|
|
LEFT JOIN T_RefundWrireRecordEntry b
|
|
ON a.FBILLNO = b.FBILLNO2
|
|
LEFT JOIN T_RefundWrireRecordEntry2 c
|
|
ON b.FID = c.FID
|
|
LEFT JOIN T_SAL_ORDERFIN dd
|
|
ON a.FID = dd.FID
|
|
LEFT JOIN T_RefundWrireRecord head
|
|
ON b.FID = head.FID
|
|
LEFT JOIN T_AR_REFUNDBILL e
|
|
ON c.FBILLNO3 = e.FBILLNO
|
|
WHERE b.FID = @ShouKuanFID);
|
|
END;
|
|
|
|
END;
|
|
ELSE IF (@type1 = '收款单')
|
|
BEGIN
|
|
IF (
|
|
(
|
|
SELECT COUNT(1)FROM T_RefundWrireRecordEntry2 WHERE FID = @ShouKuanFID
|
|
) < 2
|
|
)
|
|
BEGIN
|
|
INSERT INTO [dbo].[ZZZ_HeXiaoBook]
|
|
(
|
|
[XiaoShouFid],
|
|
[XiaoShouBillNo],
|
|
[XiaoShouHeTong],
|
|
[ShouKuanFid],
|
|
[ShouKuanBillNo],
|
|
[ShouKuanFDate],
|
|
[BenCiHeXiao],
|
|
[FSaleOrgId],
|
|
Note,
|
|
NoteFid,
|
|
FExchangeRate,
|
|
FSettleCurrId,
|
|
FLOCALCURRID,
|
|
[ShouKuanType],
|
|
Remark
|
|
)
|
|
(SELECT a.FID,
|
|
a.FBILLNO,
|
|
a.F_CONTRACTNUMBER,
|
|
@ShouKuanFID,
|
|
@tuiKuanBill,
|
|
@FDATE,
|
|
@hexiao,
|
|
a.FSALEORGID,
|
|
'收款退款记录单1V多',
|
|
@ShouKuanFID,
|
|
a.FEXCHANGERATE,
|
|
FCURRENCYID,
|
|
FMAINBOOKCURID,
|
|
4,
|
|
@remark
|
|
FROM T_AR_RECEIVEBILL a
|
|
WHERE a.FBILLNO = @FBILLNO);
|
|
END;
|
|
ELSE
|
|
BEGIN
|
|
INSERT INTO [dbo].[ZZZ_HeXiaoBook]
|
|
(
|
|
[XiaoShouFid],
|
|
[XiaoShouBillNo],
|
|
[XiaoShouHeTong],
|
|
[ShouKuanFid],
|
|
[ShouKuanBillNo],
|
|
[ShouKuanFDate],
|
|
[BenCiHeXiao],
|
|
[FSaleOrgId],
|
|
Note,
|
|
NoteFid,
|
|
FExchangeRate,
|
|
FSettleCurrId,
|
|
FLOCALCURRID,
|
|
[ShouKuanType],
|
|
Remark
|
|
)
|
|
(SELECT a.FID,
|
|
a.FBILLNO,
|
|
a.F_CONTRACTNUMBER,
|
|
e.FID,
|
|
e.FBILLNO,
|
|
@FDATE,
|
|
c.FALLAMOUNT33,
|
|
a.FSALEORGID,
|
|
'收款退款记录单多V1',
|
|
@ShouKuanFID,
|
|
a.FEXCHANGERATE,
|
|
a.FCURRENCYID,
|
|
a.FMAINBOOKCURID,
|
|
4,
|
|
head.F_NOTE
|
|
FROM T_AR_RECEIVEBILL a
|
|
LEFT JOIN T_RefundWrireRecordEntry b
|
|
ON a.FBILLNO = b.FBILLNO2
|
|
LEFT JOIN T_RefundWrireRecordEntry2 c
|
|
ON b.FID = c.FID
|
|
LEFT JOIN T_RefundWrireRecord head
|
|
ON b.FID = head.FID
|
|
LEFT JOIN T_AR_REFUNDBILL e
|
|
ON c.FBILLNO3 = e.FBILLNO
|
|
WHERE b.FID = @ShouKuanFID);
|
|
END;
|
|
|
|
END;
|
|
-- 获取下一条记录
|
|
FETCH NEXT FROM cursor_TuiKuanRecord1
|
|
INTO @type1,
|
|
@FBILLNO,
|
|
@hexiao;
|
|
END;
|
|
-- 4. 关闭游标
|
|
CLOSE cursor_TuiKuanRecord1;
|
|
-- 5. 释放游标资源
|
|
DEALLOCATE cursor_TuiKuanRecord1;
|
|
|
|
-- 获取下一条记录
|
|
FETCH NEXT FROM TuiKuanRecord
|
|
INTO @FDATE,
|
|
@ShouKuanFID,
|
|
@remark;
|
|
END;
|
|
-- 4. 关闭游标
|
|
CLOSE TuiKuanRecord;
|
|
-- 5. 释放游标资源
|
|
DEALLOCATE TuiKuanRecord;
|
|
|
|
INSERT INTO [dbo].[ZZZ_HeXiaoBook]
|
|
([XiaoShouFid]
|
|
,[XiaoShouBillNo]
|
|
,[XiaoShouHeTong]
|
|
,[ShouKuanFid]
|
|
,[ShouKuanBillNo]
|
|
,[ShouKuanFDate]
|
|
,[BenCiHeXiao]
|
|
,[FSaleOrgId],Note,NoteFid,FExchangeRate,FSettleCurrId,FLOCALCURRID,[ShouKuanType],Remark)
|
|
( SELECT 0,'','',bill.FID,bill.FBILLNO,FVERIFYDATE,FCURWRITTENOFFAMOUNTFOR*-1,FSALEORGID,'特殊核销只有退款款单',FVERIFYSEQ
|
|
,bill.FEXCHANGERATE,b.FCURRENCYID,b.FLOCALCURRID,4 ,FAbstract
|
|
FROM T_AR_RECMacthLog a
|
|
LEFT JOIN dbo.T_AR_RECMACTHLOGENTRY b ON a.FID=b.FID
|
|
LEFT JOIN T_BAS_BILLTYPE c ON b.FSOURCETYPE=c.FBILLTYPEID
|
|
INNER JOIN T_AR_REFUNDBILL bill ON FSRCBILLNO=bill.FBILLNO
|
|
WHERE FBILLFORMID='AR_REFUNDBILL' AND FVERIFYSEQ NOT IN (100135,100138,100042,100140) AND FVERIFYSEQ NOT IN (100135,100138,100042,100141,100139) )
|
|
|
|
--DELETE [ZZZ_HeXiaoBook] WHERE Note='特殊核销只有退款款单'
|
|
|
|
-----收款单已核销金额= 销售单核销-退款核销
|
|
UPDATE T_AR_RECEIVEBILL SET F_AMOUNT=F_AMOUNT+ISNULL(b.BenCiHeXiao,0),F_TUIKUAN_AMOUNT=ISNULL(b.BenCiHeXiao,0)
|
|
FROM (SELECT SUM(BenCiHeXiao*-1 ) BenCiHeXiao,XiaoShouBillNo FROM dbo.ZZZ_HeXiaoBook WHERE [ShouKuanType]=4
|
|
GROUP BY XiaoShouBillNo) b
|
|
WHERE T_AR_RECEIVEBILL.FBILLNO=b.XiaoShouBillNo AND FSALEORGID in (100302,100305) AND F_AMOUNT <> b.BenCiHeXiao AND T_AR_RECEIVEBILL.FBILLNO!=''
|
|
|
|
----销售单已核销金额=收款单核销+退款核销金额
|
|
UPDATE T_SAL_ORDER SET F_AMOUNT=F_AMOUNT+ISNULL(b.BenCiHeXiao,0),F_refundAmount=ISNULL(b.BenCiHeXiao,0)
|
|
FROM (SELECT SUM(BenCiHeXiao*1 ) BenCiHeXiao,XiaoShouBillNo FROM dbo.ZZZ_HeXiaoBook WHERE [ShouKuanType]=3
|
|
GROUP BY XiaoShouBillNo) b
|
|
WHERE T_SAL_ORDER.FBILLNO=b.XiaoShouBillNo AND FSALEORGID in (100302,100305) AND T_SAL_ORDER.FBILLNO!='';
|
|
|
|
---退款单核销金额=退款单核销金额
|
|
UPDATE T_AR_REFUNDBILL SET F_AMOUNT=ISNULL(b.BenCiHeXiao,0)
|
|
FROM (SELECT SUM(BenCiHeXiao *(-1)) BenCiHeXiao,ShouKuanBillNo FROM dbo.ZZZ_HeXiaoBook WHERE [ShouKuanType] IN (3,4)
|
|
GROUP BY ShouKuanBillNo) b
|
|
WHERE T_AR_REFUNDBILL.FBILLNO=b.ShouKuanBillNo AND FSALEORGID in (100302,100305) ;
|
|
|
|
--SELECT FVERIFYDATE,FBILLFORMID, FVERIFYSEQ,FSOURCETYPE,FCURWRITTENOFFAMOUNTFOR,FCURWRITTENOFFAMOUNT,FSRCBILLNO,b.FEXCHANGERATE,FCURTAXMATCHAMOUNT,FCURNOTAXMATCHAMOUNT ,FSRCBILLID,a.FMATCHMETHODID
|
|
--FROM T_AR_RECMacthLog a
|
|
--LEFT JOIN dbo.T_AR_RECMACTHLOGENTRY b ON a.FID=b.FID
|
|
--LEFT JOIN T_BAS_BILLTYPE c ON b.FSOURCETYPE=c.FBILLTYPEID
|
|
--WHERE a.FVERIFYSEQ =100135
|
|
|
|
END
|
|
|