---库存展望明细计算
ALTER PROCEDURE [dbo].[P_Quick_StockExpectationDetail]
@zTime DATETIME,
@cInvCode NVARCHAR(500)
AS
DECLARE @count INT;
SET @count = 1;
--SET @cInvCode = N'FB01010092';
--SET @zTime = '2024-04-21';
IF EXISTS
(
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'Temp1')
AND type IN ( N'U' )
)
DROP TABLE Temp1;
IF EXISTS
(
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'Temp2')
AND type IN ( N'U' )
)
DROP TABLE Temp2;
IF EXISTS
(
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'TempKCView_SZNK2105157_0_Temp1')
AND type IN ( N'U' )
)
DROP TABLE TempKCView_SZNK2105157_0_Temp1;
IF EXISTS
(
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'TempKCView_SZNK2105157_0_Temp2')
AND type IN ( N'U' )
)
DROP TABLE TempKCView_SZNK2105157_0_Temp2;
IF EXISTS
(
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'TempKCView_SZNK2105157_0_Temp3')
AND type IN ( N'U' )
)
DROP TABLE TempKCView_SZNK2105157_0_Temp3;
CREATE TABLE TempKCView_SZNK2105157_0_Temp1
(
djh NVARCHAR(30),
ddate DATETIME,
cVouchType NVARCHAR(20),
cwhcode NVARCHAR(20),
cInvCode NVARCHAR(60),
cmemo NVARCHAR(150),
iqty FLOAT
DEFAULT 0,
inqty FLOAT
DEFAULT 0,
outqty FLOAT
DEFAULT 0,
cFree1 NVARCHAR(20),
cFree2 NVARCHAR(20),
cFree3 NVARCHAR(20),
cFree4 NVARCHAR(20),
cFree5 NVARCHAR(20),
cFree6 NVARCHAR(20),
cFree7 NVARCHAR(20),
cFree8 NVARCHAR(20),
cFree9 NVARCHAR(20),
cFree10 NVARCHAR(20),
isotype SMALLINT
DEFAULT 0,
isodid NVARCHAR(40)
DEFAULT N''
);
INSERT INTO TempKCView_SZNK2105157_0_Temp1
SELECT TransVouch.cTVCode,
TransVouch.dTVDate,
N'调拨单',
RdRecord.cWhCode,
TransVouchs.cInvCode,
N'调拨在途量',
0,
CASE
WHEN ISNULL(RdRecord.cHandler, N'') = N'' THEN
RdRecords.iQuantity
ELSE
0
END,
0,
TransVouchs.cFree1,
TransVouchs.cFree2,
TransVouchs.cFree3,
TransVouchs.cFree4,
TransVouchs.cFree5,
TransVouchs.cFree6,
TransVouchs.cFree7,
TransVouchs.cFree8,
TransVouchs.cFree9,
TransVouchs.cFree10,
ISNULL(TransVouchs.iSSoType, 0),
ISNULL(TransVouchs.iSSodid, N'')
FROM rdrecords08 RdRecords
INNER JOIN RdRecord08 RdRecord
ON RdRecords.ID = RdRecord.ID
INNER JOIN TransVouch
INNER JOIN TransVouchs
ON TransVouch.ID = TransVouchs.ID
ON RdRecords.iTrIds = TransVouchs.autoID
INNER JOIN Inventory
ON TransVouchs.cInvCode = Inventory.cInvCode
INNER JOIN Warehouse
ON RdRecord.cWhCode = Warehouse.cWhCode
WHERE bInAvailCalcu = 1
AND ISNULL(TransVouch.cVerifyPerson, N'') <> N''
AND RdRecord.bRdFlag <> 0
AND RdRecord.cBusType = N'调拨入库'
AND RdRecord.dDate <= @zTime
AND (TransVouchs.cInvCode = @cInvCode)
AND (1 = 1);
INSERT INTO TempKCView_SZNK2105157_0_Temp1
SELECT TransVouch.cTVCode,
TransVouch.dTVDate,
N'调拨单',
RdRecord.cWhCode,
TransVouchs.cInvCode,
N'调拨在途量',
0,
CASE
WHEN ISNULL(RdRecord.cHandler, N'') = N'' THEN
TransVouchs.iTVQuantity
ELSE
0
END,
0,
TransVouchs.cFree1,
TransVouchs.cFree2,
TransVouchs.cFree3,
TransVouchs.cFree4,
TransVouchs.cFree5,
TransVouchs.cFree6,
TransVouchs.cFree7,
TransVouchs.cFree8,
TransVouchs.cFree9,
TransVouchs.cFree10,
ISNULL(TransVouchs.iSSoType, 0),
ISNULL(TransVouchs.iSSodid, N'')
FROM rdrecords09 RdRecords
INNER JOIN RdRecord09 RdRecord
ON RdRecords.ID = RdRecord.ID
INNER JOIN TransVouch
INNER JOIN TransVouchs
ON TransVouch.ID = TransVouchs.ID
ON RdRecords.iTrIds = TransVouchs.autoID
INNER JOIN Inventory
ON TransVouchs.cInvCode = Inventory.cInvCode
INNER JOIN Warehouse
ON RdRecord.cWhCode = Warehouse.cWhCode
WHERE bInAvailCalcu = 1
AND ISNULL(TransVouch.cVerifyPerson, N'') <> N''
AND RdRecord.bRdFlag = 0
AND RdRecord.cBusType = N'调拨出库'
AND RdRecord.dDate <= @zTime
AND RdRecords.iQuantity < 0
AND (TransVouchs.cInvCode = @cInvCode)
AND (1 = 1);
INSERT INTO TempKCView_SZNK2105157_0_Temp1
SELECT TransVouch.cTVCode,
TransVouch.dTVDate,
N'调拨单',
TransVouch.cIWhCode,
TransVouchs.cInvCode,
N'调拨在途量',
0,
TransVouchs.iTVQuantity,
0,
TransVouchs.cFree1,
TransVouchs.cFree2,
TransVouchs.cFree3,
TransVouchs.cFree4,
TransVouchs.cFree5,
TransVouchs.cFree6,
TransVouchs.cFree7,
TransVouchs.cFree8,
TransVouchs.cFree9,
TransVouchs.cFree10,
ISNULL(TransVouchs.iSSoType, 0),
ISNULL(TransVouchs.iSSodid, N'')
FROM TransVouch
INNER JOIN TransVouchs
ON TransVouch.ID = TransVouchs.ID
INNER JOIN Inventory
ON TransVouchs.cInvCode = Inventory.cInvCode
INNER JOIN Warehouse
ON TransVouch.cIWhCode = Warehouse.cWhCode
WHERE bInAvailCalcu = 1
AND ISNULL(TransVouch.cVerifyPerson, N'') = N''
AND TransVouch.dTVDate <= @zTime
AND (TransVouchs.cInvCode = @cInvCode)
AND (1 = 1);
INSERT INTO TempKCView_SZNK2105157_0_Temp1
SELECT PU_AppVouch.cCode,
PU_AppVouch.dDate AS dDate,
N'采购请购单',
N'' AS cwhcode,
PU_AppVouchs.cInvCode,
N'已请购量',
0,
CASE
WHEN (PU_AppVouchs.fQuantity - ISNULL(PU_AppVouchs.iReceivedQTY, 0) + ISNULL(PO_Podetails.iQuantity, 0)) < 0 THEN
0
ELSE
(PU_AppVouchs.fQuantity - ISNULL(PU_AppVouchs.iReceivedQTY, 0) + ISNULL(PO_Podetails.iQuantity, 0))
END,
0,
PU_AppVouchs.cFree1,
PU_AppVouchs.cFree2,
PU_AppVouchs.cFree3,
PU_AppVouchs.cFree4,
PU_AppVouchs.cFree5,
PU_AppVouchs.cFree6,
PU_AppVouchs.cFree7,
PU_AppVouchs.cFree8,
PU_AppVouchs.cFree9,
PU_AppVouchs.cFree10,
ISNULL(PU_AppVouchs.SoType, 0),
ISNULL(PU_AppVouchs.SoDId, N'')
FROM PU_AppVouch
INNER JOIN PU_AppVouchs
ON PU_AppVouch.ID = PU_AppVouchs.ID
LEFT JOIN
(
SELECT cInvCode,
iAppIds,
SUM(iQuantity) AS iQuantity
FROM PO_Podetails
INNER JOIN PO_Pomain
ON PO_Podetails.POID = PO_Pomain.POID
WHERE ISNULL(PO_Pomain.cVerifier, N'') = N''
GROUP BY cInvCode,
iAppIds
) PO_Podetails
ON PU_AppVouchs.AutoID = PO_Podetails.iAppIds
INNER JOIN Inventory
ON PU_AppVouchs.cInvCode = Inventory.cInvCode
INNER JOIN ComputationUnit
ON Inventory.cComUnitCode = ComputationUnit.cComunitCode
LEFT JOIN Warehouse
ON Inventory.cDefWareHouse = Warehouse.cWhCode
WHERE ISNULL(PU_AppVouch.cVerifier, N'') <> N''
AND ISNULL(PU_AppVouchs.cbcloser, N'') = N''
AND PU_AppVouch.dDate <= @zTime
AND
(
ISNULL(Warehouse.bInAvailCalcu, 0) = 1
OR ISNULL(Inventory.cDefWareHouse, N'') = N''
)
AND (PU_AppVouchs.cInvCode = @cInvCode)
AND (1 = 1);
INSERT INTO TempKCView_SZNK2105157_0_Temp1
SELECT PO_Pomain.cPOID,
(CASE
WHEN ISNULL(PO_Podetails.dArriveDate, N'') = N'' THEN
PO_Pomain.dPODate
ELSE
PO_Podetails.dArriveDate
END
) AS dDate,
N'采购订单',
N'' AS cwhcode,
PO_Podetails.cInvCode,
N'订单在途量',
0,
CASE
WHEN (PO_Podetails.iQuantity - ISNULL(iArrQTY, 0) - ISNULL(iReceivedQTY, 0)) < 0 THEN
0
ELSE
(PO_Podetails.iQuantity - ISNULL(iArrQTY, 0) - ISNULL(iReceivedQTY, 0))
END AS inqty,
0,
PO_Podetails.cFree1,
PO_Podetails.cFree2,
PO_Podetails.cFree3,
PO_Podetails.cFree4,
PO_Podetails.cFree5,
PO_Podetails.cFree6,
PO_Podetails.cFree7,
PO_Podetails.cFree8,
PO_Podetails.cFree9,
PO_Podetails.cFree10,
ISNULL(PO_Podetails.SoType, 0),
ISNULL(PO_Podetails.SoDId, N'')
FROM PO_Pomain
INNER JOIN PO_Podetails
ON PO_Pomain.POID = PO_Podetails.POID
INNER JOIN Inventory
ON PO_Podetails.cInvCode = Inventory.cInvCode
WHERE (ISNULL(cVerifier, N'') <> N'')
AND ISNULL(cbCloser, N'') = N''
AND ISNULL(PO_Pomain.cBusType, N'') <> N'直运采购'
AND (CASE
WHEN ISNULL(PO_Podetails.dArriveDate, N'') = N'' THEN
PO_Pomain.dPODate
ELSE
PO_Podetails.dArriveDate
END
) <= @zTime
AND (PO_Podetails.cInvCode = @cInvCode)
AND (1 = 1);
INSERT INTO TempKCView_SZNK2105157_0_Temp1
SELECT v_im_orderforkcview.ccode,
v_im_orderforkcview.ddate,
N'进口订单',
N'' AS cwhcode,
v_im_orderforkcview.cinvcode,
N'订单在途量',
0,
v_im_orderforkcview.fquantity AS inqty,
0,
v_im_orderforkcview.cfree1,
v_im_orderforkcview.cfree2,
v_im_orderforkcview.cfree3,
v_im_orderforkcview.cfree4,
v_im_orderforkcview.cfree5,
v_im_orderforkcview.cfree6,
v_im_orderforkcview.cfree7,
v_im_orderforkcview.cfree8,
v_im_orderforkcview.cfree9,
v_im_orderforkcview.cfree10,
ISNULL(v_im_orderforkcview.sotypeid, 0),
ISNULL(v_im_orderforkcview.sodid, N'')
FROM v_im_orderforkcview
INNER JOIN Inventory
ON v_im_orderforkcview.cinvcode = Inventory.cInvCode
WHERE v_im_orderforkcview.ddate <= @zTime
AND (v_im_orderforkcview.cinvcode = @cInvCode)
AND (1 = 1)
AND v_im_orderforkcview.iimbustype <> N'30';
INSERT INTO TempKCView_SZNK2105157_0_Temp1
SELECT PU_ArrivalVouch.cCode,
PU_ArrivalVouch.dDate,
N'采购到货单',
ISNULL(PU_ArrivalVouchs.cWhCode, N''),
PU_ArrivalVouchs.cInvCode,
N'到货/在检量',
0,
CASE
WHEN iBillType = 0 THEN
(CASE
WHEN PU_ArrivalVouchs.iQuantity - ISNULL(PU_ArrivalVouchs.fValidInQuan, 0)
- ISNULL(PU_ArrivalVouchs.fInValidInQuan, 0) - ISNULL(PU_ArrivalVouchs.fDTQuantity, 0)
- ISNULL(PU_ArrivalVouchs.fRefuseQuantity, 0) >= 0 THEN
PU_ArrivalVouchs.iQuantity - ISNULL(PU_ArrivalVouchs.fValidInQuan, 0)
- ISNULL(PU_ArrivalVouchs.fInValidInQuan, 0) - ISNULL(PU_ArrivalVouchs.fDTQuantity, 0)
- ISNULL(PU_ArrivalVouchs.fRefuseQuantity, 0)
ELSE
0
END
)
ELSE
(CASE
WHEN PU_ArrivalVouchs.iQuantity - ISNULL(PU_ArrivalVouchs.fValidInQuan, 0)
- ISNULL(PU_ArrivalVouchs.fInValidInQuan, 0) - ISNULL(PU_ArrivalVouchs.fDTQuantity, 0)
- ISNULL(PU_ArrivalVouchs.fRefuseQuantity, 0) <= 0 THEN
PU_ArrivalVouchs.iQuantity - ISNULL(PU_ArrivalVouchs.fValidInQuan, 0)
- ISNULL(PU_ArrivalVouchs.fInValidInQuan, 0) - ISNULL(PU_ArrivalVouchs.fDTQuantity, 0)
- ISNULL(PU_ArrivalVouchs.fRefuseQuantity, 0)
ELSE
0
END
)
END AS inqty,
0,
PU_ArrivalVouchs.cFree1,
PU_ArrivalVouchs.cFree2,
PU_ArrivalVouchs.cFree3,
PU_ArrivalVouchs.cFree4,
PU_ArrivalVouchs.cFree5,
PU_ArrivalVouchs.cFree6,
PU_ArrivalVouchs.cFree7,
PU_ArrivalVouchs.cFree8,
PU_ArrivalVouchs.cFree9,
PU_ArrivalVouchs.cFree10,
ISNULL(PU_ArrivalVouchs.SoType, 0),
ISNULL(PU_ArrivalVouchs.SoDId, N'')
FROM PU_ArrivalVouch
INNER JOIN PU_ArrivalVouchs
ON PU_ArrivalVouch.ID = PU_ArrivalVouchs.ID
INNER JOIN Inventory
ON PU_ArrivalVouchs.cInvCode = Inventory.cInvCode
LEFT JOIN Warehouse
ON PU_ArrivalVouchs.cWhCode = Warehouse.cWhCode
WHERE (
bInAvailCalcu = 1
OR ISNULL(PU_ArrivalVouchs.cWhCode, N'') = N''
)
AND ISNULL(PU_ArrivalVouch.iBillType, 0) <> 2
AND dDate <= @zTime
AND ISNULL(PU_ArrivalVouchs.cbcloser, N'') = N''
AND (PU_ArrivalVouchs.cInvCode = @cInvCode)
AND (1 = 1);
INSERT INTO TempKCView_SZNK2105157_0_Temp1
SELECT N'' AS ccode,
v_EX_WaitInForStock.ddate,
N'出口退货单',
ISNULL(v_EX_WaitInForStock.cWhCode, N''),
v_EX_WaitInForStock.cInvCode,
N'到货/在检量',
0,
ABS(fInQuantity) AS inqty,
0,
v_EX_WaitInForStock.cFree1,
v_EX_WaitInForStock.cFree2,
v_EX_WaitInForStock.cFree3,
v_EX_WaitInForStock.cFree4,
v_EX_WaitInForStock.cFree5,
v_EX_WaitInForStock.cFree6,
v_EX_WaitInForStock.cFree7,
v_EX_WaitInForStock.cFree8,
v_EX_WaitInForStock.cFree9,
v_EX_WaitInForStock.cFree10,
ISNULL(v_EX_WaitInForStock.sotype, 0),
ISNULL(v_EX_WaitInForStock.sodid, N'')
FROM v_EX_WaitInForStock
INNER JOIN Inventory
ON v_EX_WaitInForStock.cInvCode = Inventory.cInvCode
LEFT JOIN Warehouse W
ON v_EX_WaitInForStock.cWhCode = W.cWhCode
WHERE (
bInAvailCalcu = 1
OR ISNULL(v_EX_WaitInForStock.cWhCode, N'') = N''
)
AND CONVERT(NVARCHAR(10), v_EX_WaitInForStock.ddate, 121) <= @zTime
AND (v_EX_WaitInForStock.cInvCode = @cInvCode)
AND (1 = 1);
INSERT INTO TempKCView_SZNK2105157_0_Temp1
SELECT RdRecord.cCode,
RdRecord.dDate,
CASE
WHEN RdRecord.cVouchType = N'01' THEN
N'01'
WHEN RdRecord.cVouchType = N'10' THEN
N'10'
ELSE
N'08'
END,
RdRecord.cWhCode,
RdRecords.cInvCode,
N'到货/在检量',
0,
iQuantity AS inqty,
0,
RdRecords.cFree1,
RdRecords.cFree2,
RdRecords.cFree3,
RdRecords.cFree4,
RdRecords.cFree5,
RdRecords.cFree6,
RdRecords.cFree7,
RdRecords.cFree8,
RdRecords.cFree9,
RdRecords.cFree10,
ISNULL(RdRecords.isotype, 0),
ISNULL(RdRecords.isodid, N'')
FROM rdrecord10 RdRecord
INNER JOIN rdrecords10 RdRecords
ON RdRecord.ID = RdRecords.ID
INNER JOIN Inventory
ON RdRecords.cInvCode = Inventory.cInvCode
LEFT JOIN Warehouse
ON RdRecord.cWhCode = Warehouse.cWhCode
WHERE bInAvailCalcu = 1
AND dDate <= @zTime
AND dDate >= N'2018-01-01'
AND ISNULL(RdRecord.cHandler, N'') = N''
AND cSource <> N'调拨'
AND RdRecord.cBusType <> N'假退料'
AND (RdRecords.cInvCode = @cInvCode)
AND (1 = 1);
INSERT INTO TempKCView_SZNK2105157_0_Temp1
SELECT RdRecord.cCode,
RdRecord.dDate,
CASE
WHEN RdRecord.cVouchType = N'01' THEN
N'01'
WHEN RdRecord.cVouchType = N'10' THEN
N'10'
ELSE
N'08'
END,
RdRecord.cWhCode,
RdRecords.cInvCode,
N'到货/在检量',
0,
iQuantity AS inqty,
0,
RdRecords.cFree1,
RdRecords.cFree2,
RdRecords.cFree3,
RdRecords.cFree4,
RdRecords.cFree5,
RdRecords.cFree6,
RdRecords.cFree7,
RdRecords.cFree8,
RdRecords.cFree9,
RdRecords.cFree10,
ISNULL(RdRecords.isotype, 0),
ISNULL(RdRecords.isodid, N'')
FROM RdRecord01 RdRecord
INNER JOIN rdrecords01 RdRecords
ON RdRecord.ID = RdRecords.ID
INNER JOIN Inventory
ON RdRecords.cInvCode = Inventory.cInvCode
LEFT JOIN Warehouse
ON RdRecord.cWhCode = Warehouse.cWhCode
WHERE bInAvailCalcu = 1
AND dDate <= @zTime
AND dDate >= N'2018-01-01'
AND ISNULL(RdRecord.cHandler, N'') = N''
AND cSource <> N'调拨'
AND RdRecord.cBusType <> N'假退料'
AND (RdRecords.cInvCode = @cInvCode)
AND (1 = 1);
INSERT INTO TempKCView_SZNK2105157_0_Temp1
SELECT RdRecord.cCode,
RdRecord.dDate,
CASE
WHEN RdRecord.cVouchType = N'01' THEN
N'01'
WHEN RdRecord.cVouchType = N'10' THEN
N'10'
ELSE
N'08'
END,
RdRecord.cWhCode,
RdRecords.cInvCode,
N'到货/在检量',
0,
iQuantity AS inqty,
0,
RdRecords.cFree1,
RdRecords.cFree2,
RdRecords.cFree3,
RdRecords.cFree4,
RdRecords.cFree5,
RdRecords.cFree6,
RdRecords.cFree7,
RdRecords.cFree8,
RdRecords.cFree9,
RdRecords.cFree10,
ISNULL(RdRecords.isotype, 0),
ISNULL(RdRecords.isodid, N'')
FROM RdRecord08 RdRecord
INNER JOIN rdrecords08 RdRecords
ON RdRecord.ID = RdRecords.ID
INNER JOIN Inventory
ON RdRecords.cInvCode = Inventory.cInvCode
LEFT JOIN Warehouse
ON RdRecord.cWhCode = Warehouse.cWhCode
WHERE bInAvailCalcu = 1
AND dDate <= @zTime
AND dDate >= N'2018-01-01'
AND ISNULL(RdRecord.cHandler, N'') = N''
AND cSource <> N'调拨'
AND RdRecord.cBusType <> N'假退料'
AND (RdRecords.cInvCode = @cInvCode)
AND (1 = 1);
INSERT INTO TempKCView_SZNK2105157_0_Temp1
SELECT RdRecord.cCode,
RdRecord.dDate,
CASE
WHEN RdRecord.cVouchType = N'32' THEN
N'32'
WHEN RdRecord.cVouchType = N'11' THEN
N'11'
ELSE
N'09'
END,
RdRecord.cWhCode,
RdRecords.cInvCode,
N'到货/在检量',
0,
ABS(iQuantity) AS inqty,
0 AS outqty,
RdRecords.cFree1,
RdRecords.cFree2,
RdRecords.cFree3,
RdRecords.cFree4,
RdRecords.cFree5,
RdRecords.cFree6,
RdRecords.cFree7,
RdRecords.cFree8,
RdRecords.cFree9,
RdRecords.cFree10,
ISNULL(RdRecords.isotype, 0),
ISNULL(RdRecords.isodid, N'')
FROM RdRecord09 RdRecord
INNER JOIN rdrecords09 RdRecords
ON RdRecord.ID = RdRecords.ID
INNER JOIN Inventory
ON RdRecords.cInvCode = Inventory.cInvCode
LEFT JOIN Warehouse
ON RdRecord.cWhCode = Warehouse.cWhCode
WHERE bInAvailCalcu = 1
AND dDate <= @zTime
AND dDate >= N'2018-01-01'
AND ISNULL(RdRecord.cHandler, N'') = N''
AND RdRecords.iQuantity < 0
AND cSource <> N'调拨'
AND RdRecord.cBusType <> N'假退料'
AND (RdRecords.cInvCode = @cInvCode)
AND (1 = 1);
INSERT INTO TempKCView_SZNK2105157_0_Temp1
SELECT RdRecord.cCode,
RdRecord.dDate,
CASE
WHEN RdRecord.cVouchType = N'32' THEN
N'32'
WHEN RdRecord.cVouchType = N'11' THEN
N'11'
ELSE
N'09'
END,
RdRecord.cWhCode,
RdRecords.cInvCode,
N'到货/在检量',
0,
ABS(iQuantity) AS inqty,
0 AS outqty,
RdRecords.cFree1,
RdRecords.cFree2,
RdRecords.cFree3,
RdRecords.cFree4,
RdRecords.cFree5,
RdRecords.cFree6,
RdRecords.cFree7,
RdRecords.cFree8,
RdRecords.cFree9,
RdRecords.cFree10,
ISNULL(RdRecords.isotype, 0),
ISNULL(RdRecords.isodid, N'')
FROM rdrecord11 RdRecord
INNER JOIN rdrecords11 RdRecords
ON RdRecord.ID = RdRecords.ID
INNER JOIN Inventory
ON RdRecords.cInvCode = Inventory.cInvCode
LEFT JOIN Warehouse
ON RdRecord.cWhCode = Warehouse.cWhCode
WHERE bInAvailCalcu = 1
AND dDate <= @zTime
AND dDate >= N'2018-01-01'
AND ISNULL(RdRecord.cHandler, N'') = N''
AND RdRecords.iQuantity < 0
AND cSource <> N'调拨'
AND RdRecord.cBusType <> N'假退料'
AND (RdRecords.cInvCode = @cInvCode)
AND (1 = 1);
INSERT INTO TempKCView_SZNK2105157_0_Temp1
SELECT RdRecord.cCode,
RdRecord.dDate,
CASE
WHEN RdRecord.cVouchType = N'32' THEN
N'32'
WHEN RdRecord.cVouchType = N'11' THEN
N'11'
ELSE
N'09'
END,
RdRecord.cWhCode,
RdRecords.cInvCode,
N'到货/在检量',
0,
ABS(iQuantity) AS inqty,
0 AS outqty,
RdRecords.cFree1,
RdRecords.cFree2,
RdRecords.cFree3,
RdRecords.cFree4,
RdRecords.cFree5,
RdRecords.cFree6,
RdRecords.cFree7,
RdRecords.cFree8,
RdRecords.cFree9,
RdRecords.cFree10,
ISNULL(RdRecords.isotype, 0),
ISNULL(RdRecords.isodid, N'')
FROM rdrecord32 RdRecord
INNER JOIN rdrecords32 RdRecords
ON RdRecord.ID = RdRecords.ID
INNER JOIN Inventory
ON RdRecords.cInvCode = Inventory.cInvCode
LEFT JOIN Warehouse
ON RdRecord.cWhCode = Warehouse.cWhCode
WHERE bInAvailCalcu = 1
AND dDate <= @zTime
AND dDate >= N'2018-01-01'
AND ISNULL(RdRecord.cHandler, N'') = N''
AND RdRecords.iQuantity < 0
AND cSource <> N'调拨'
AND RdRecord.cBusType <> N'假退料'
AND (RdRecords.cInvCode = @cInvCode)
AND (1 = 1);
INSERT INTO TempKCView_SZNK2105157_0_Temp1
SELECT ScrapVouch.cSpCode,
ScrapVouch.dDate,
N'45',
ScrapVouch.cInWhcode,
ScrapVouchs.cInvCode,
N'到货/在检量',
0,
ABS(iQuantity),
0,
cFree1,
cFree2,
cFree3,
cFree4,
cFree5,
cFree6,
cFree7,
cFree8,
cFree9,
cFree10,
ISNULL(ScrapVouchs.iSoType, 0),
ISNULL(ScrapVouchs.iSodid, N'')
FROM ScrapVouch
INNER JOIN ScrapVouchs
ON ScrapVouch.Id = ScrapVouchs.ID
INNER JOIN Inventory
ON ScrapVouchs.cInvCode = Inventory.cInvCode
INNER JOIN Warehouse W
ON ScrapVouch.cInWhcode = W.cWhCode
WHERE bInAvailCalcu = 1
AND dDate <= @zTime
AND dDate >= N'2018-01-01'
AND ISNULL(ScrapVouch.cVerifyPerson, N'') = N''
AND ScrapVouchs.iQuantity > 0
AND cVouchType = N'45'
AND
(
cSource = N'入库验收单'
OR cSource = N'退货验收单'
)
AND (ScrapVouchs.cInvCode = @cInvCode)
AND (1 = 1);
INSERT INTO TempKCView_SZNK2105157_0_Temp1
SELECT DispatchList.cDLCode,
DispatchList.dDate,
N'销售发货单',
DispatchLists.cWhCode,
DispatchLists.cInvCode,
N'到货/在检量',
0,
CASE
WHEN (ABS(iQuantity) - ABS(ISNULL(fOutQuantity, 0))) < 0 THEN
0
ELSE
ABS(iQuantity) - ABS(ISNULL(fOutQuantity, 0))
END,
0,
DispatchLists.cFree1,
DispatchLists.cFree2,
DispatchLists.cFree3,
DispatchLists.cFree4,
DispatchLists.cFree5,
DispatchLists.cFree6,
DispatchLists.cFree7,
DispatchLists.cFree8,
DispatchLists.cFree9,
DispatchLists.cFree10,
ISNULL(DispatchLists.idemandtype, 0),
ISNULL(DispatchLists.cdemandid, N'')
FROM DispatchList
INNER JOIN DispatchLists
ON DispatchList.DLID = DispatchLists.DLID
INNER JOIN Inventory
ON DispatchLists.cInvCode = Inventory.cInvCode
INNER JOIN Warehouse
ON DispatchLists.cWhCode = Warehouse.cWhCode
WHERE ISNULL(DispatchLists.bSettleAll, 0) = 0
AND ISNULL(iQuantity, 0) < 0
AND Inventory.bService <> 1
AND ISNULL(DispatchLists.iTB, 0) <> 1
AND
(
DispatchList.dDate <= @zTime
OR ISNULL(bIsSTQc, 0) = 1
)
AND DispatchList.dDate >= N'2018-01-01'
AND (DispatchLists.cInvCode = @cInvCode)
AND (1 = 1);
INSERT INTO TempKCView_SZNK2105157_0_Temp1
SELECT SaleBillVouch.cSBVCode,
dDate,
N'销售发票',
SaleBillVouchs.cWhCode,
SaleBillVouchs.cInvCode,
N'到货/在检量',
0,
ABS(iQuantity),
0,
SaleBillVouchs.cFree1,
SaleBillVouchs.cFree2,
SaleBillVouchs.cFree3,
SaleBillVouchs.cFree4,
SaleBillVouchs.cFree5,
SaleBillVouchs.cFree6,
SaleBillVouchs.cFree7,
SaleBillVouchs.cFree8,
SaleBillVouchs.cFree9,
SaleBillVouchs.cFree10,
ISNULL(SaleBillVouchs.idemandtype, 0),
ISNULL(SaleBillVouchs.cdemandid, N'')
FROM SaleBillVouch
INNER JOIN SaleBillVouchs
ON SaleBillVouch.SBVID = SaleBillVouchs.SBVID
INNER JOIN Inventory
ON SaleBillVouchs.cInvCode = Inventory.cInvCode
INNER JOIN Warehouse
ON SaleBillVouchs.cWhCode = Warehouse.cWhCode
WHERE ISNULL(iQuantity, 0) < 0
AND ISNULL(iDisp, 0) = 0
AND Inventory.bService <> 1
AND SaleBillVouchs.iTB <> 1
AND ISNULL(SaleBillVouch.cChecker, N'') = N''
AND ISNULL(SaleBillVouch.cInvalider, N'') = N''
AND ISNULL(SaleBillVouch.cBusType, N'') <> N'直运销售'
AND ddate >= N'2018-01-01'
AND ddate <= @zTime
AND (SaleBillVouchs.cInvCode = @cInvCode)
AND (1 = 1);
INSERT INTO TempKCView_SZNK2105157_0_Temp1
(
djh,
ddate,
cVouchType,
cwhcode,
cInvCode,
cFree1,
cFree2,
cFree3,
cFree4,
cFree5,
cFree6,
cFree7,
cFree8,
cFree9,
cFree10,
isotype,
isodid,
cmemo,
inqty
) /*Qty：生产订单数量，DeclaredQty：累计报检数量， QualifiedInQty：累计入库数量*/
SELECT R.MoCode,
R.DueDate,
N'生产订单',
R.WhCode,
R.InvCode,
R.Free1,
R.Free2,
R.Free3,
R.Free4,
R.Free5,
R.Free6,
R.Free7,
R.Free8,
R.Free9,
R.Free10,
ISNULL(R.SoType, 0),
ISNULL(R.SoDId, N''),
N'生产订单量',
CASE
WHEN CASE R.ByProductFlag
WHEN 1 THEN
R.Qty
ELSE
R.MrpQty
END - ISNULL(R.QualifiedInQty, 0) >= 0 THEN
CASE R.ByProductFlag
WHEN 1 THEN
R.Qty
ELSE
R.MrpQty
END - ISNULL(R.QualifiedInQty, 0)
ELSE
0
END AS SCDD
FROM v_mom_orderdetail R
INNER JOIN Inventory I
ON R.InvCode = I.cInvCode
LEFT JOIN Warehouse w
ON w.cWhCode = R.WhCode
WHERE (
bInAvailCalcu = 1
OR ISNULL(R.WhCode, N'') = N''
)
AND R.Status = 3 /*不用考虑是否关闭*/--生产订单状态--1,未审核--2,锁定--3,审核
AND R.DueDate <= @zTime /*DueDate 完工日期*/
AND (R.InvCode = @cInvCode)
AND (1 = 1);
INSERT INTO TempKCView_SZNK2105157_0_Temp1
(
djh,
ddate,
cVouchType,
cwhcode,
cInvCode,
cFree1,
cFree2,
cFree3,
cFree4,
cFree5,
cFree6,
cFree7,
cFree8,
cFree9,
cFree10,
isotype,
isodid,
cmemo,
outqty
)
SELECT R.MoCode,
Rs.Demdate,
N'生产订单子件',
Rs.WhCode,
Rs.InvCode,
Rs.Free1,
Rs.Free2,
Rs.Free3,
Rs.Free4,
Rs.Free5,
Rs.Free6,
Rs.Free7,
Rs.Free8,
Rs.Free9,
Rs.Free10,
ISNULL(Rs.SoType, 0),
ISNULL(Rs.SoDId, N''), /*Qty:数量，IssQty：已领量*/
N'生产未领量',
(CASE
WHEN Rs.Qty - ISNULL(Rs.IssQty, 0) + ISNULL(mom_replenishapplydtl.issueqty, 0)
+ ISNULL(mom_replenishapplydtl.CmplQty, 0)
- (CASE
WHEN ISNULL(mom_replenishapplydtl.withdrawqty, 0) > ISNULL(mom_replenishapplydtl.applyqty, 0) THEN
ISNULL(mom_replenishapplydtl.applyqty, 0)
ELSE
ISNULL(mom_replenishapplydtl.withdrawqty, 0)
END
) >= 0 THEN
Rs.Qty - ISNULL(Rs.IssQty, 0) + ISNULL(mom_replenishapplydtl.issueqty, 0)
+ ISNULL(mom_replenishapplydtl.CmplQty, 0)
- (CASE
WHEN ISNULL(mom_replenishapplydtl.withdrawqty, 0) > ISNULL(mom_replenishapplydtl.applyqty, 0) THEN
ISNULL(mom_replenishapplydtl.applyqty, 0)
ELSE
ISNULL(mom_replenishapplydtl.withdrawqty, 0)
END
)
ELSE
0
END
)
FROM v_mom_moallocate_st Rs
INNER JOIN v_mom_orderdetail R
ON R.MoDId = Rs.MoDId
INNER JOIN Inventory I
ON Rs.InvCode = I.cInvCode
LEFT JOIN
(
SELECT mom_replenishapplydtl.MoAllocateId,
SUM( CASE
WHEN ISNULL(mom_replenishapply.CloseUser, N'') = N'' THEN
mom_replenishapplydtl.issueqty
ELSE
0
END
) AS issueqty,
SUM( CASE
WHEN ISNULL(mom_replenishapply.CloseUser, N'') = N'' THEN
mom_replenishapplydtl.CmplQty
ELSE
0
END
) AS CmplQty,
SUM( CASE
WHEN ISNULL(mom_replenishapply.CloseUser, N'') = N'' THEN
mom_replenishapplydtl.applyqty
ELSE
0
END
) AS applyqty,
SUM(WithdrawQty) AS withdrawqty
FROM mom_replenishapplydtl
LEFT JOIN mom_replenishapply
ON mom_replenishapply.ApplyId = mom_replenishapplydtl.ApplyId
WHERE mom_replenishapply.Status = 3
GROUP BY mom_replenishapplydtl.MoAllocateId
) mom_replenishapplydtl
ON mom_replenishapplydtl.MoAllocateId = Rs.AllocateId
LEFT JOIN Warehouse W
ON Rs.WhCode = W.cWhCode
WHERE (
bInAvailCalcu = 1
OR ISNULL(Rs.WhCode, N'') = N''
)
AND R.Status = 3 /*不用考虑是否关闭and Rs.ByproductFlag = 0 不用考虑*/
AND R.ByProductFlag = 0
AND Rs.ByproductFlag = 0
AND Rs.Demdate <= @zTime
AND (Rs.InvCode = @cInvCode)
AND (1 = 1);
INSERT INTO TempKCView_SZNK2105157_0_Temp1
(
djh,
ddate,
cVouchType,
cwhcode,
cInvCode,
cFree1,
cFree2,
cFree3,
cFree4,
cFree5,
cFree6,
cFree7,
cFree8,
cFree9,
cFree10,
isotype,
isodid,
cmemo,
outqty
)
SELECT R.DocCode,
Rs.StartDemDate,
N'补料申请单',
Rs.WhCode,
Rs.InvCode,
Rs.Free1,
Rs.Free2,
Rs.Free3,
Rs.Free4,
Rs.Free5,
Rs.Free6,
Rs.Free7,
Rs.Free8,
Rs.Free9,
Rs.Free10,
ISNULL(Rs.SoType, 0),
ISNULL(Rs.SoDId, N''), /*Qty:数量，IssQty：已领量*/
N'生产未领量',
(CASE
WHEN mom_replenishapplydtl.ApplyQty - ISNULL(mom_replenishapplydtl.CmplQty, 0)
- ISNULL(mom_replenishapplydtl.IssueQty, 0) >= 0 THEN
mom_replenishapplydtl.ApplyQty - ISNULL(mom_replenishapplydtl.CmplQty, 0)
- ISNULL(mom_replenishapplydtl.IssueQty, 0)
ELSE
0
END
) AS SCWL
FROM mom_replenishapplydtl
INNER JOIN mom_replenishapply R
ON R.ApplyId = mom_replenishapplydtl.ApplyId
LEFT OUTER JOIN mom_orderdetail
ON mom_replenishapplydtl.MoDId = mom_orderdetail.MoDId
LEFT OUTER JOIN mom_moallocate Rs
ON Rs.AllocateId = mom_replenishapplydtl.MoAllocateId
INNER JOIN Inventory
ON Rs.InvCode = Inventory.cInvCode
LEFT JOIN Warehouse W
ON Rs.WhCode = W.cWhCode
WHERE (
bInAvailCalcu = 1
OR ISNULL(Rs.WhCode, N'') = N''
)
AND R.Status = 3
AND ISNULL(R.CloseUser, N'') = N''
AND Rs.StartDemDate <= @zTime
AND (Rs.InvCode = @cInvCode)
AND (1 = 1);
INSERT INTO TempKCView_SZNK2105157_0_Temp1
(
djh,
ddate,
cVouchType,
cwhcode,
cInvCode,
cFree1,
cFree2,
cFree3,
cFree4,
cFree5,
cFree6,
cFree7,
cFree8,
cFree9,
cFree10,
isotype,
isodid,
cmemo,
inqty
)
SELECT R.cCode,
ISNULL(Rs.dArriveDate, R.dDate) AS ddate,
N'委外订单',
N'',
Rs.cInvCode,
cFree1,
cFree2,
cFree3,
cFree4,
cFree5,
cFree6,
cFree7,
cFree8,
cFree9,
cFree10,
ISNULL(Rs.SOType, 0),
ISNULL(Rs.SODID, N''),
N'委外订单量',
CASE
WHEN Rs.iQuantity - ISNULL(Rs.iReceivedQTY, 0) - ISNULL(Rs.iArrQTY, 0) >= 0 THEN
Rs.iQuantity - ISNULL(Rs.iReceivedQTY, 0) - ISNULL(Rs.iArrQTY, 0)
ELSE
0
END AS WWDD
FROM OM_MODetails Rs
INNER JOIN OM_MOMain R
ON R.MOID = Rs.MOID
INNER JOIN Inventory I
ON Rs.cInvCode = I.cInvCode
WHERE 1 = 1
AND
(
ISNULL(R.cVerifier, N'') <> N''
OR ISNULL(Rs.iSourceMOCode, N'') <> N''
OR ISNULL(R.cLocker, N'') <> N''
)
AND
(
ISNULL(R.cChanger, N'') = N''
OR ISNULL(R.cChangeVerifier, N'') <> N''
)
AND ISNULL(Rs.cbCloser, N'') = N''
AND ISNULL(Rs.dArriveDate, R.dDate) <= @zTime
AND (Rs.cInvCode = @cInvCode)
AND (1 = 1);
INSERT INTO TempKCView_SZNK2105157_0_Temp1
(
djh,
ddate,
cVouchType,
cwhcode,
cInvCode,
cFree1,
cFree2,
cFree3,
cFree4,
cFree5,
cFree6,
cFree7,
cFree8,
cFree9,
cFree10,
isotype,
isodid,
cmemo,
outqty
)
SELECT R.cCode,
ISNULL(Rss.dRequiredDate, R.dDate),
N'委外订单子件',
Rss.cWhCode,
Rss.cInvCode,
Rss.cFree1,
Rss.cFree2,
Rss.cFree3,
Rss.cFree4,
Rss.cFree5,
Rss.cFree6,
Rss.cFree7,
Rss.cFree8,
Rss.cFree9,
Rss.cFree10,
ISNULL(Rss.sotype, 0),
ISNULL(Rss.sodid, N''),
N'委外未领量',
CASE
WHEN Rss.iQuantity - ISNULL(Rss.iSendQTY, 0) >= 0 THEN
Rss.iQuantity - ISNULL(Rss.iSendQTY, 0)
ELSE
0
END
FROM OM_MOMain R
INNER JOIN OM_MODetails Rs
ON R.MOID = Rs.MOID
INNER JOIN OM_MOMaterials Rss
ON Rs.MODetailsID = Rss.MoDetailsID
INNER JOIN Inventory I
ON Rss.cInvCode = I.cInvCode
LEFT JOIN Warehouse W
ON Rss.cWhCode = W.cWhCode
WHERE (
ISNULL(R.cVerifier, N'') <> N''
OR ISNULL(Rs.iSourceMOCode, N'') <> N''
OR ISNULL(R.cLocker, N'') <> N''
)
AND
(
ISNULL(R.cChanger, N'') = N''
OR ISNULL(R.cChangeVerifier, N'') <> N''
)
AND ISNULL(Rs.cbCloser, N'') = N''
AND
(
ISNULL(Rss.cWhCode, N'') = N''
OR W.bInAvailCalcu = 1
)
AND ISNULL(Rss.dRequiredDate, R.dDate) <= @zTime
AND ISNULL(Rss.iProductType, 0) = 0
AND (Rss.cInvCode = @cInvCode)
AND (1 = 1);
INSERT INTO TempKCView_SZNK2105157_0_Temp1
SELECT SO_SOMain.cSOCode,
CASE
WHEN ISNULL(SO_SODetails.dPreDate, N'') = N'' THEN
SO_SOMain.dDate
ELSE
SO_SODetails.dPreDate
END AS ddate,
N'销售订单',
N'' AS cwhcode,
SO_SODetails.cInvCode,
N'销售订单量',
0,
0 AS inqty,
CASE
WHEN (iQuantity - ISNULL(iFHQuantity, 0)) < 0 THEN
0
ELSE
(iQuantity - ISNULL(iFHQuantity, 0))
END AS outqty,
SO_SODetails.cFree1,
SO_SODetails.cFree2,
SO_SODetails.cFree3,
SO_SODetails.cFree4,
SO_SODetails.cFree5,
SO_SODetails.cFree6,
SO_SODetails.cFree7,
SO_SODetails.cFree8,
SO_SODetails.cFree9,
SO_SODetails.cFree10,
ISNULL(SO_SODetails.idemandtype, 0),
(CASE
WHEN ISNULL(SO_SODetails.idemandtype, 0) = 1 THEN
CONVERT(NVARCHAR(40), ISNULL(SO_SODetails.iSOsID, N''))
WHEN ISNULL(SO_SODetails.idemandtype, 0) = 4 THEN
(CASE
WHEN ISNULL(SO_SODetails.cdemandcode, N'') = N'' THEN
N'Systemdefault'
ELSE
ISNULL(SO_SODetails.cdemandcode, N'')
END
)
WHEN ISNULL(SO_SODetails.idemandtype, 0) = 5 THEN
ISNULL(SO_SODetails.cSOCode, N'')
ELSE
N''
END
)
FROM SO_SOMain
INNER JOIN SO_SODetails
ON SO_SOMain.ID = SO_SODetails.ID
INNER JOIN Inventory
ON SO_SODetails.cInvCode = Inventory.cInvCode
WHERE ISNULL(SO_SOMain.cVerifier, N'') <> N''
AND ISNULL(SO_SODetails.cSCloser, N'') = N''
AND ISNULL(SO_SOMain.cBusType, N'') <> N'直运销售'
AND ISNULL(SO_SODetails.cParentCode, N'') = N''
AND (CASE
WHEN ISNULL(SO_SODetails.dPreDate, N'') = N'' THEN
SO_SOMain.dDate
ELSE
SO_SODetails.dPreDate
END
) <= @zTime
AND (SO_SODetails.cInvCode = @cInvCode)
AND (1 = 1);
INSERT INTO TempKCView_SZNK2105157_0_Temp1
SELECT v_ex_order_inuseFotSt.ccode,
CONVERT(NVARCHAR(10), v_ex_order_inuseFotSt.dexpdate, 121),
N'出口订单',
N'' AS cwhcode,
v_ex_order_inuseFotSt.cinvcode,
N'销售订单量',
0,
0 AS inqty,
ABS(fQuantity) AS outqty,
v_ex_order_inuseFotSt.cfree1,
v_ex_order_inuseFotSt.cfree2,
v_ex_order_inuseFotSt.cfree3,
v_ex_order_inuseFotSt.cfree4,
v_ex_order_inuseFotSt.cfree5,
v_ex_order_inuseFotSt.cfree6,
v_ex_order_inuseFotSt.cfree7,
v_ex_order_inuseFotSt.cfree8,
v_ex_order_inuseFotSt.cfree9,
v_ex_order_inuseFotSt.cfree10,
ISNULL(v_ex_order_inuseFotSt.idemandtype, 0),
(CASE
WHEN ISNULL(v_ex_order_inuseFotSt.idemandtype, 0) = 3 THEN
CONVERT(NVARCHAR(40), ISNULL(v_ex_order_inuseFotSt.autoid, N''))
WHEN ISNULL(v_ex_order_inuseFotSt.idemandtype, 0) = 4 THEN
ISNULL(v_ex_order_inuseFotSt.cdemandcode, N'')
WHEN ISNULL(v_ex_order_inuseFotSt.idemandtype, 0) = 6 THEN
ISNULL(v_ex_order_inuseFotSt.ccode, N'')
ELSE
N''
END
)
FROM v_ex_order_inuseFotSt
INNER JOIN Inventory
ON v_ex_order_inuseFotSt.cinvcode = Inventory.cInvCode
AND CONVERT(NVARCHAR(10), v_ex_order_inuseFotSt.dexpdate, 121) <= @zTime
AND (v_ex_order_inuseFotSt.cinvcode = @cInvCode)
AND (1 = 1);
INSERT INTO TempKCView_SZNK2105157_0_Temp1
SELECT DispatchList.cDLCode,
DispatchList.dDate,
N'销售发货单',
DispatchLists.cWhCode,
DispatchLists.cInvCode,
N'待发货量',
0,
0 AS inqty,
CASE
WHEN (iQuantity - ISNULL(fOutQuantity, 0)) < 0 THEN
0
ELSE
(iQuantity - ISNULL(fOutQuantity, 0))
END,
DispatchLists.cFree1,
DispatchLists.cFree2,
DispatchLists.cFree3,
DispatchLists.cFree4,
DispatchLists.cFree5,
DispatchLists.cFree6,
DispatchLists.cFree7,
DispatchLists.cFree8,
DispatchLists.cFree9,
DispatchLists.cFree10,
ISNULL(DispatchLists.idemandtype, 0),
ISNULL(DispatchLists.cdemandid, N'')
FROM DispatchList
INNER JOIN DispatchLists
ON DispatchList.DLID = DispatchLists.DLID
INNER JOIN Inventory
ON DispatchLists.cInvCode = Inventory.cInvCode
INNER JOIN Warehouse
ON DispatchLists.cWhCode = Warehouse.cWhCode
WHERE bInAvailCalcu = 1
AND ISNULL(DispatchLists.bSettleAll, 0) = 0
AND ISNULL(iQuantity, 0) > 0
AND Inventory.bService <> 1
AND ISNULL(DispatchLists.iTB, 0) <> 1
AND dDate <= @zTime
AND dDate >= N'2018-01-01'
AND
(
DispatchLists.bIsSTQc = 1
OR
(
ISNULL(DispatchList.bFirst, 0) = 0
AND ISNULL(DispatchList.bIAFirst, 0) = 0
)
)
AND (DispatchLists.cInvCode = @cInvCode)
AND (1 = 1);
INSERT INTO TempKCView_SZNK2105157_0_Temp1
SELECT v_EX_WaitOutForStock.ccode AS ccode,
v_EX_WaitOutForStock.ddate,
N'出口销货单',
v_EX_WaitOutForStock.cWhCode,
v_EX_WaitOutForStock.cInvCode,
N'待发货量',
0,
0,
v_EX_WaitOutForStock.fOutQuantity,
v_EX_WaitOutForStock.cFree1,
v_EX_WaitOutForStock.cFree2,
v_EX_WaitOutForStock.cFree3,
v_EX_WaitOutForStock.cFree4,
v_EX_WaitOutForStock.cFree5,
v_EX_WaitOutForStock.cFree6,
v_EX_WaitOutForStock.cFree7,
v_EX_WaitOutForStock.cFree8,
v_EX_WaitOutForStock.cFree9,
v_EX_WaitOutForStock.cFree10,
ISNULL(v_EX_WaitOutForStock.sotype, 0),
ISNULL(v_EX_WaitOutForStock.sodid, N'')
FROM v_ST_WaitOutForkcview v_EX_WaitOutForStock
INNER JOIN Inventory
ON v_EX_WaitOutForStock.cInvCode = Inventory.cInvCode
LEFT JOIN Warehouse W
ON v_EX_WaitOutForStock.cWhCode = W.cWhCode
WHERE (
bInAvailCalcu = 1
OR ISNULL(v_EX_WaitOutForStock.cWhCode, N'') = N''
)
AND CONVERT(NVARCHAR(10), v_EX_WaitOutForStock.ddate, 121) <= @zTime
AND (v_EX_WaitOutForStock.cInvCode = @cInvCode)
AND (1 = 1);
INSERT INTO TempKCView_SZNK2105157_0_Temp1
SELECT RdRecord.cCode,
RdRecord.dDate,
CASE
WHEN RdRecord.cVouchType = N'32' THEN
N'32'
WHEN RdRecord.cVouchType = N'11' THEN
N'11'
ELSE
N'09'
END,
RdRecord.cWhCode,
RdRecords.cInvCode,
N'待发货量',
0,
0,
iQuantity,
RdRecords.cFree1,
RdRecords.cFree2,
RdRecords.cFree3,
RdRecords.cFree4,
RdRecords.cFree5,
RdRecords.cFree6,
RdRecords.cFree7,
RdRecords.cFree8,
RdRecords.cFree9,
RdRecords.cFree10,
ISNULL(RdRecords.isotype, 0),
ISNULL(RdRecords.isodid, N'')
FROM RdRecord09 RdRecord
INNER JOIN rdrecords09 RdRecords
ON RdRecord.ID = RdRecords.ID
INNER JOIN Inventory
ON RdRecords.cInvCode = Inventory.cInvCode
LEFT JOIN Warehouse
ON RdRecord.cWhCode = Warehouse.cWhCode
WHERE bInAvailCalcu = 1
AND dDate <= @zTime
AND dDate >= N'2018-01-01'
AND ISNULL(RdRecord.cHandler, N'') = N''
AND RdRecords.iQuantity > 0
AND cSource <> N'调拨'
AND RdRecord.cBusType <> N'假退料'
AND (RdRecords.cInvCode = @cInvCode)
AND (1 = 1);
INSERT INTO TempKCView_SZNK2105157_0_Temp1
SELECT RdRecord.cCode,
RdRecord.dDate,
CASE
WHEN RdRecord.cVouchType = N'32' THEN
N'32'
WHEN RdRecord.cVouchType = N'11' THEN
N'11'
ELSE
N'09'
END,
RdRecord.cWhCode,
RdRecords.cInvCode,
N'待发货量',
0,
0,
iQuantity,
RdRecords.cFree1,
RdRecords.cFree2,
RdRecords.cFree3,
RdRecords.cFree4,
RdRecords.cFree5,
RdRecords.cFree6,
RdRecords.cFree7,
RdRecords.cFree8,
RdRecords.cFree9,
RdRecords.cFree10,
ISNULL(RdRecords.isotype, 0),
ISNULL(RdRecords.isodid, N'')
FROM rdrecord11 RdRecord
INNER JOIN rdrecords11 RdRecords
ON RdRecord.ID = RdRecords.ID
INNER JOIN Inventory
ON RdRecords.cInvCode = Inventory.cInvCode
LEFT JOIN Warehouse
ON RdRecord.cWhCode = Warehouse.cWhCode
WHERE bInAvailCalcu = 1
AND dDate <= @zTime
AND dDate >= N'2018-01-01'
AND ISNULL(RdRecord.cHandler, N'') = N''
AND RdRecords.iQuantity > 0
AND cSource <> N'调拨'
AND RdRecord.cBusType <> N'假退料'
AND (RdRecords.cInvCode = @cInvCode)
AND (1 = 1);
INSERT INTO TempKCView_SZNK2105157_0_Temp1
SELECT RdRecord.cCode,
RdRecord.dDate,
CASE
WHEN RdRecord.cVouchType = N'32' THEN
N'32'
WHEN RdRecord.cVouchType = N'11' THEN
N'11'
ELSE
N'09'
END,
RdRecord.cWhCode,
RdRecords.cInvCode,
N'待发货量',
0,
0,
iQuantity,
RdRecords.cFree1,
RdRecords.cFree2,
RdRecords.cFree3,
RdRecords.cFree4,
RdRecords.cFree5,
RdRecords.cFree6,
RdRecords.cFree7,
RdRecords.cFree8,
RdRecords.cFree9,
RdRecords.cFree10,
ISNULL(RdRecords.isotype, 0),
ISNULL(RdRecords.isodid, N'')
FROM rdrecord32 RdRecord
INNER JOIN rdrecords32 RdRecords
ON RdRecord.ID = RdRecords.ID
INNER JOIN Inventory
ON RdRecords.cInvCode = Inventory.cInvCode
LEFT JOIN Warehouse
ON RdRecord.cWhCode = Warehouse.cWhCode
WHERE bInAvailCalcu = 1
AND dDate <= @zTime
AND dDate >= N'2018-01-01'
AND ISNULL(RdRecord.cHandler, N'') = N''
AND RdRecords.iQuantity > 0
AND cSource <> N'调拨'
AND RdRecord.cBusType <> N'假退料'
AND (RdRecords.cInvCode = @cInvCode)
AND (1 = 1);
INSERT INTO TempKCView_SZNK2105157_0_Temp1
SELECT SaleBillVouch.cSBVCode,
dDate,
N'销售发票',
SaleBillVouchs.cWhCode,
SaleBillVouchs.cInvCode,
N'待发货量',
0,
0,
iQuantity AS DFHL,
SaleBillVouchs.cFree1,
SaleBillVouchs.cFree2,
SaleBillVouchs.cFree3,
SaleBillVouchs.cFree4,
SaleBillVouchs.cFree5,
SaleBillVouchs.cFree6,
SaleBillVouchs.cFree7,
SaleBillVouchs.cFree8,
SaleBillVouchs.cFree9,
SaleBillVouchs.cFree10,
ISNULL(SaleBillVouchs.idemandtype, 0),
ISNULL(SaleBillVouchs.cdemandid, N'')
FROM SaleBillVouch
INNER JOIN SaleBillVouchs
ON SaleBillVouch.SBVID = SaleBillVouchs.SBVID
INNER JOIN Inventory
ON SaleBillVouchs.cInvCode = Inventory.cInvCode
INNER JOIN Warehouse
ON SaleBillVouchs.cWhCode = Warehouse.cWhCode
WHERE bInAvailCalcu = 1
AND ISNULL(iQuantity, 0) > 0
AND ISNULL(iDisp, 0) <> 1
AND ISNULL(cBusType, N'') <> N'委托'
AND ISNULL(Inventory.bInvType, 0) <> 1
AND Inventory.bService <> 1
AND SaleBillVouchs.iTB <> 1
AND ISNULL(SaleBillVouch.cChecker, N'') = N''
AND (SaleBillVouch.cDLCode IS NULL)
AND ISNULL(SaleBillVouch.cInvalider, N'') = N''
AND ISNULL(SaleBillVouch.cBusType, N'') <> N'直运销售'
AND ddate >= N'2018-01-01'
AND ddate <= @zTime
AND (SaleBillVouchs.cInvCode = @cInvCode)
AND (1 = 1);
INSERT INTO TempKCView_SZNK2105157_0_Temp1
SELECT TransVouch.cTVCode,
RdRecord.dDate,
N'12',
TransVouch.cOWhCode,
TransVouchs.cInvCode,
N'调拨待发量',
0,
0,
CASE
WHEN ISNULL(RdRecord.cHandler, N'') = N'' THEN
TransVouchs.iTVQuantity
ELSE
0
END AS DBDF,
TransVouchs.cFree1,
TransVouchs.cFree2,
TransVouchs.cFree3,
TransVouchs.cFree4,
TransVouchs.cFree5,
TransVouchs.cFree6,
TransVouchs.cFree7,
TransVouchs.cFree8,
TransVouchs.cFree9,
TransVouchs.cFree10,
ISNULL(TransVouchs.iDSoType, 0),
ISNULL(TransVouchs.iDSodid, N'')
FROM rdrecords09 RdRecords
INNER JOIN RdRecord09 RdRecord
ON RdRecords.ID = RdRecord.ID
INNER JOIN TransVouch
INNER JOIN TransVouchs
ON TransVouch.ID = TransVouchs.ID
ON RdRecords.iTrIds = TransVouchs.autoID
INNER JOIN Inventory
ON TransVouchs.cInvCode = Inventory.cInvCode
INNER JOIN Warehouse
ON RdRecord.cWhCode = Warehouse.cWhCode
WHERE bInAvailCalcu = 1
AND ISNULL(TransVouch.cVerifyPerson, N'') <> N''
AND RdRecord.bRdFlag <> 1
AND RdRecord.cBusType = N'调拨出库'
AND RdRecord.dDate <= @zTime
AND (TransVouchs.cInvCode = @cInvCode)
AND (1 = 1)
AND RdRecords.iQuantity > 0;
INSERT INTO TempKCView_SZNK2105157_0_Temp1
SELECT TransVouch.cTVCode,
TransVouch.dTVDate,
N'12',
TransVouch.cOWhCode,
TransVouchs.cInvCode,
N'调拨待发量',
0,
0,
TransVouchs.iTVQuantity AS DBDF,
TransVouchs.cFree1,
TransVouchs.cFree2,
TransVouchs.cFree3,
TransVouchs.cFree4,
TransVouchs.cFree5,
TransVouchs.cFree6,
TransVouchs.cFree7,
TransVouchs.cFree8,
TransVouchs.cFree9,
TransVouchs.cFree10,
ISNULL(TransVouchs.iDSoType, 0),
ISNULL(TransVouchs.iDSodid, N'')
FROM TransVouch
INNER JOIN TransVouchs
ON TransVouch.ID = TransVouchs.ID
INNER JOIN Inventory
ON TransVouchs.cInvCode = Inventory.cInvCode
INNER JOIN Warehouse
ON TransVouch.cOWhCode = Warehouse.cWhCode
WHERE bInAvailCalcu = 1
AND ISNULL(TransVouch.cVerifyPerson, N'') = N''
AND TransVouch.dTVDate <= @zTime
AND (TransVouchs.cInvCode = @cInvCode)
AND (1 = 1);
INSERT INTO TempKCView_SZNK2105157_0_Temp1
SELECT MatchVouch.cVouchCode,
MatchVouch.PlanDate,
N'限额领料',
MatchVouchs.cWhCode,
MatchVouchs.cInvCode,
N'备料计划量',
0,
0,
CASE
WHEN (iPlanQuantity - ISNULL(iSumActiQuantity, 0)) < 0 THEN
0
ELSE
(iPlanQuantity - ISNULL(iSumActiQuantity, 0))
END AS BLJH,
MatchVouchs.cFree1,
MatchVouchs.cFree2,
MatchVouchs.cFree3,
MatchVouchs.cFree4,
MatchVouchs.cFree5,
MatchVouchs.cFree6,
MatchVouchs.cFree7,
MatchVouchs.cFree8,
MatchVouchs.cFree9,
MatchVouchs.cFree10,
0,
N''
FROM MatchVouch
INNER JOIN MatchVouchs
ON MatchVouch.ID = MatchVouchs.ID
INNER JOIN Inventory
ON MatchVouchs.cInvCode = Inventory.cInvCode
INNER JOIN Warehouse
ON MatchVouchs.cWhCode = Warehouse.cWhCode
WHERE Warehouse.bInAvailCalcu = 1
AND ISNULL(MatchVouch.cCloser, N'') = N''
AND CONVERT(NVARCHAR(10), MatchVouch.PlanDate, 121) <= @zTime
AND (MatchVouchs.cInvCode = @cInvCode)
AND (1 = 1);
INSERT INTO TempKCView_SZNK2105157_0_Temp1
SELECT MaterialAppVouch.cCode,
ISNULL(MaterialAppVouchs.dDueDate, MaterialAppVouch.dDate),
N'领料申请单',
MaterialAppVouchs.cWhCode,
MaterialAppVouchs.cInvCode,
N'备料计划量',
0,
0,
(CASE
WHEN (iQuantity - ISNULL(fOutQuantity, 0)) < 0 THEN
0
ELSE
(iQuantity - ISNULL(fOutQuantity, 0))
END
) AS BLJH,
MaterialAppVouchs.cFree1,
MaterialAppVouchs.cFree2,
MaterialAppVouchs.cFree3,
MaterialAppVouchs.cFree4,
MaterialAppVouchs.cFree5,
MaterialAppVouchs.cFree6,
MaterialAppVouchs.cFree7,
MaterialAppVouchs.cFree8,
MaterialAppVouchs.cFree9,
MaterialAppVouchs.cFree10,
0,
N''
FROM MaterialAppVouch
INNER JOIN MaterialAppVouchs
ON MaterialAppVouch.ID = MaterialAppVouchs.ID
INNER JOIN Inventory
ON MaterialAppVouchs.cInvCode = Inventory.cInvCode
LEFT JOIN Warehouse
ON MaterialAppVouchs.cWhCode = Warehouse.cWhCode
WHERE (
Warehouse.bInAvailCalcu = 1
OR ISNULL(MaterialAppVouchs.cWhCode, N'') = N''
)
AND ISNULL(MaterialAppVouch.cCloser, N'') = N''
AND ISNULL(MaterialAppVouch.cHandler, N'') <> N''
AND CONVERT(NVARCHAR(10), ISNULL(MaterialAppVouchs.dDueDate, MaterialAppVouch.dDate), 121) <= @zTime
AND ISNULL(MaterialAppVouch.cSource, N'') = N''
AND ISNULL(MaterialAppVouchs.cBCloser, N'') = N''
AND (MaterialAppVouchs.cInvCode = @cInvCode)
AND (1 = 1);
INSERT INTO TempKCView_SZNK2105157_0_Temp1
(
djh,
ddate,
cVouchType,
cwhcode,
cInvCode,
cmemo,
iqty,
inqty,
outqty,
cFree1,
cFree2,
cFree3,
cFree4,
cFree5,
cFree6,
cFree7,
cFree8,
cFree9,
cFree10,
isotype,
isodid
)
SELECT N'',
NULL,
N'',
ISNULL(T.cWhCode, N''),
T.cInvCode,
N'现存量',
SUM(iQuantity + ISNULL(ipeqty, 0)),
0,
0,
ISNULL(T.cFree1, N''),
ISNULL(T.cFree2, N''),
ISNULL(T.cFree3, N''),
ISNULL(T.cFree4, N''),
ISNULL(T.cFree5, N''),
ISNULL(T.cFree6, N''),
ISNULL(T.cFree7, N''),
ISNULL(T.cFree8, N''),
ISNULL(T.cFree9, N''),
ISNULL(T.cFree10, N''),
ISNULL(T.iSoType, 0),
ISNULL(T.iSodid, N'')
FROM CurrentStock T
INNER JOIN Inventory
ON T.cInvCode = Inventory.cInvCode
INNER JOIN Warehouse W
ON T.cWhCode = W.cWhCode
WHERE W.bInAvailCalcu = 1
AND (T.cInvCode = @cInvCode)
AND (1 = 1)
GROUP BY T.cInvCode,
ISNULL(T.cWhCode, N''),
ISNULL(T.cFree1, N''),
ISNULL(T.cFree2, N''),
ISNULL(T.cFree3, N''),
ISNULL(T.cFree4, N''),
ISNULL(T.cFree5, N''),
ISNULL(T.cFree6, N''),
ISNULL(T.cFree7, N''),
ISNULL(T.cFree8, N''),
ISNULL(T.cFree9, N''),
ISNULL(T.cFree10, N''),
ISNULL(T.iSoType, 0),
ISNULL(T.iSodid, N'')
HAVING ABS(CONVERT(DECIMAL(20, 6), SUM(iQuantity + ISNULL(ipeqty, 0)))) > 0
ORDER BY T.cInvCode,
ISNULL(T.cFree1, N''),
ISNULL(T.cFree2, N''),
ISNULL(T.cFree3, N''),
ISNULL(T.cFree4, N''),
ISNULL(T.cFree5, N''),
ISNULL(T.cFree6, N''),
ISNULL(T.cFree7, N''),
ISNULL(T.cFree8, N''),
ISNULL(T.cFree9, N''),
ISNULL(T.cFree10, N''),
ISNULL(T.iSoType, 0),
ISNULL(T.iSodid, N'');
INSERT INTO TempKCView_SZNK2105157_0_Temp1
(
djh,
ddate,
cVouchType,
cwhcode,
cInvCode,
cmemo,
iqty,
inqty,
outqty,
cFree1,
cFree2,
cFree3,
cFree4,
cFree5,
cFree6,
cFree7,
cFree8,
cFree9,
cFree10,
isotype,
isodid
)
SELECT N'',
NULL,
N'',
ISNULL(T.cWhCode, N''),
T.cInvCode,
N'冻结量',
SUM( CASE
WHEN ISNULL(T.bStopFlag, 0) = 1 THEN
ISNULL(iQuantity, 0)
ELSE
ISNULL(fStopQuantity, 0)
END
),
0,
0,
ISNULL(T.cFree1, N''),
ISNULL(T.cFree2, N''),
ISNULL(T.cFree3, N''),
ISNULL(T.cFree4, N''),
ISNULL(T.cFree5, N''),
ISNULL(T.cFree6, N''),
ISNULL(T.cFree7, N''),
ISNULL(T.cFree8, N''),
ISNULL(T.cFree9, N''),
ISNULL(T.cFree10, N''),
ISNULL(T.iSoType, 0),
ISNULL(T.iSodid, N'')
FROM CurrentStock T
INNER JOIN Inventory
ON T.cInvCode = Inventory.cInvCode
INNER JOIN Warehouse W
ON T.cWhCode = W.cWhCode
WHERE (
(
ISNULL(bStopFlag, 0) = 1
AND ISNULL(iQuantity, 0) > 00000001
)
OR ISNULL(fStopQuantity, 0) > 0.00000001
)
AND W.bInAvailCalcu = 1
AND (T.cInvCode = @cInvCode)
AND (1 = 1)
GROUP BY T.cInvCode,
ISNULL(T.cWhCode, N''),
ISNULL(T.cFree1, N''),
ISNULL(T.cFree2, N''),
ISNULL(T.cFree3, N''),
ISNULL(T.cFree4, N''),
ISNULL(T.cFree5, N''),
ISNULL(T.cFree6, N''),
ISNULL(T.cFree7, N''),
ISNULL(T.cFree8, N''),
ISNULL(T.cFree9, N''),
ISNULL(T.cFree10, N''),
ISNULL(T.iSoType, 0),
ISNULL(T.iSodid, N'')
HAVING ABS(CONVERT(DECIMAL(20, 6),
SUM( CASE
WHEN ISNULL(T.bStopFlag, 0) = 1 THEN
ISNULL(iQuantity, 0)
ELSE
ISNULL(fStopQuantity, 0)
END
)
)
) > 0
ORDER BY T.cInvCode,
ISNULL(T.cFree1, N''),
ISNULL(T.cFree2, N''),
ISNULL(T.cFree3, N''),
ISNULL(T.cFree4, N''),
ISNULL(T.cFree5, N''),
ISNULL(T.cFree6, N''),
ISNULL(T.cFree7, N''),
ISNULL(T.cFree8, N''),
ISNULL(T.cFree9, N''),
ISNULL(T.cFree10, N''),
ISNULL(T.iSoType, 0),
ISNULL(T.iSodid, N'');
SELECT A.djh,
ISNULL(CONVERT(NVARCHAR(10), A.ddate, 121), N'') AS ddate,
A.cVouchType,
A.cwhcode,
ISNULL(h.cWhName, N'') AS cwhname,
A.cInvCode,
inv.cInvName,
inv.cInvStd,
com.cComUnitName,
A.cFree1,
A.cFree2,
A.cFree3,
A.cFree4,
A.cFree5,
A.cFree6,
A.cFree7,
A.cFree8,
A.cFree9,
A.cFree10,
A.cmemo,
CONVERT(DECIMAL(38, 2), A.iqty) AS iqty,
CONVERT(DECIMAL(38, 2), A.inqty) AS inqty,
CONVERT(DECIMAL(38, 2), A.outqty) AS outqty,
cInvDefine1,
cInvDefine2,
cInvDefine3,
cInvDefine4,
cInvDefine5,
cInvDefine6,
cInvDefine7,
cInvDefine8,
cInvDefine9,
cInvDefine10,
cInvDefine11,
cInvDefine12,
cInvDefine13,
cInvDefine14,
cInvDefine15,
cInvDefine16,
v_aa_enum.EnumName AS isotypename,
(CASE
WHEN inv.cSRPolicy = N'LP'
AND ISNULL(inv.bSpecialOrder, 0) = 1 THEN
A.isotype
ELSE
0
END
) AS isotype,
(CASE
WHEN inv.cSRPolicy = N'LP'
AND ISNULL(inv.bSpecialOrder, 0) = 1 THEN
(CASE
WHEN A.isotype = 1 THEN
SO_SODetails.cSOCode
WHEN A.isotype = 3 THEN
ex_order.ccode
WHEN A.isotype = 0 THEN
N''
ELSE
A.isodid
END
)
ELSE
N''
END
) AS csocode,
(CASE
WHEN inv.cSRPolicy = N'LP'
AND ISNULL(inv.bSpecialOrder, 0) = 1 THEN
(CASE
WHEN A.isotype = 1 THEN
CONVERT(VARCHAR(10), SO_SODetails.iRowNo)
WHEN A.isotype = 3 THEN
CONVERT(VARCHAR(10), ex_orderdetail.irowno)
ELSE
N''
END
)
ELSE
N''
END
) AS isoseq
INTO TempKCView_SZNK2105157_0_Temp2
FROM TempKCView_SZNK2105157_0_Temp1 A
INNER JOIN Inventory inv
ON A.cInvCode = inv.cInvCode
LEFT JOIN SO_SODetails
ON A.isodid = CONVERT(NVARCHAR(40), SO_SODetails.iSOsID)
AND A.isotype = 1
LEFT JOIN ex_orderdetail
ON A.isodid = CONVERT(NVARCHAR(40), ex_orderdetail.autoid)
AND A.isotype = 3
LEFT JOIN ex_order
ON ex_orderdetail.id = ex_order.id
LEFT JOIN ComputationUnit com
ON com.cComunitCode = inv.cComUnitCode
LEFT JOIN Warehouse h
ON h.cWhCode = A.cwhcode
LEFT JOIN v_aa_enum
ON v_aa_enum.EnumCode = (CASE
WHEN inv.cSRPolicy = N'LP'
AND ISNULL(inv.bSpecialOrder, 0) = 1 THEN
A.isotype
ELSE
0
END
)
AND v_aa_enum.EnumType = N'ST.Sotype';
SELECT A.djh,
A.ddate,
(CASE
WHEN ISNULL(v.EnumName, N'') <> N'' THEN
v.EnumName
WHEN ISNULL(v1.EnumName, N'') <> N'' THEN
ISNULL(v1.EnumName, N'')
ELSE
A.cVouchType
END
) AS cvouchname,
A.cwhcode,
A.cwhname,
A.cInvCode,
A.cInvName,
A.cInvStd,
A.cComUnitName,
A.cFree1,
A.cFree2,
A.cFree3,
A.cFree4,
A.cFree5,
A.cFree6,
A.cFree7,
A.cFree8,
A.cFree9,
A.cFree10,
A.cmemo,
ISNULL(A.iqty, 0) AS iqty,
ISNULL(A.inqty, 0) AS inqty,
ISNULL(A.outqty, 0) AS outqty,
cInvDefine1,
cInvDefine2,
cInvDefine3,
cInvDefine4,
cInvDefine5,
cInvDefine6,
cInvDefine7,
cInvDefine8,
cInvDefine9,
cInvDefine10,
cInvDefine11,
cInvDefine12,
cInvDefine13,
cInvDefine14,
cInvDefine15,
cInvDefine16
INTO TempKCView_SZNK2105157_0_Temp3
FROM TempKCView_SZNK2105157_0_Temp2 A
LEFT JOIN v_aa_enum v
ON v.EnumCode = A.cVouchType
AND v.EnumType = N'ST.VouchSource'
LEFT JOIN v_aa_enum v1
ON v1.EnumCode = A.cVouchType
AND v1.EnumType = N'ST.VouchType'
WHERE (1 = 1)
AND
(
A.isotypename = N'无'
AND A.csocode = N''
)
AND
(
ISNULL(A.iqty, 0) <> 0
OR ISNULL(A.inqty, 0) <> 0
OR ISNULL(A.outqty, 0) <> 0
);
SELECT *
INTO #Temp1
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY cInvCode ORDER BY ddate) AS SeriesId,
djh,
ddate,
cvouchname,
cwhcode,
cwhname,
cInvCode,
cInvName,
cInvStd,
cComUnitName,
cFree1,
cFree2,
cFree3,
cFree4,
cFree5,
cFree6,
cFree7,
cFree8,
cFree9,
cFree10,
cmemo,
LTRIM(STR(SUM(ISNULL(iqty, 0)), 30, 2)) AS iqty,
LTRIM(STR(SUM(ISNULL(inqty, 0)), 30, 2)) AS inqty,
LTRIM(STR(SUM(ISNULL(outqty, 0)), 30, 2)) AS outqty,
-- CAST(LTRIM(STR(SUM(ISNULL(iqty, 0)), 30, 2)) AS DECIMAL(18,2)) AS iqty,
--CAST(LTRIM(STR(SUM(ISNULL(inqty, 0)), 30, 2)) AS DECIMAL(18,2)) AS inqty,
--CAST(LTRIM(STR(SUM(ISNULL(outqty, 0)), 30, 2)) AS DECIMAL(18,2)) AS outqty,
--SUM(ISNULL(iqty, 0)) AS iqty,
-- SUM(ISNULL(inqty, 0)) AS inqty,
-- SUM(ISNULL(outqty, 0)) AS outqty,
cInvDefine1,
cInvDefine2,
cInvDefine3,
cInvDefine4,
cInvDefine5,
cInvDefine6,
cInvDefine7,
cInvDefine8,
cInvDefine9,
cInvDefine10,
cInvDefine11,
cInvDefine12,
cInvDefine13,
cInvDefine14,
cInvDefine15,
cInvDefine16
FROM TempKCView_SZNK2105157_0_Temp3
GROUP BY ddate,
djh,
cvouchname,
cwhcode,
cwhname,
cInvCode,
cInvName,
cInvStd,
cComUnitName,
cFree1,
cFree2,
cFree3,
cFree4,
cFree5,
cFree6,
cFree7,
cFree8,
cFree9,
cFree10,
cmemo,
cInvDefine1,
cInvDefine2,
cInvDefine3,
cInvDefine4,
cInvDefine5,
cInvDefine6,
cInvDefine7,
cInvDefine8,
cInvDefine9,
cInvDefine10,
cInvDefine11,
cInvDefine12,
cInvDefine13,
cInvDefine14,
cInvDefine15,
cInvDefine16
) A;
CREATE TABLE #Temp2
(
SeriesId INT,
djh [NVARCHAR](50) NULL,
ddate [DATETIME] NULL,
cvouchname [NVARCHAR](50) NULL,
cwhcode [NVARCHAR](50) NULL,
cwhname [NVARCHAR](50) NULL,
cInvCode [NVARCHAR](50) NULL,
cInvName [NVARCHAR](50) NULL,
cInvStd [NVARCHAR](800) NULL,
cComUnitName [NVARCHAR](50) NULL,
cmemo [NVARCHAR](50) NULL,
iqty [DECIMAL](18, 2),
inqty [DECIMAL](18, 2),
outqty [DECIMAL](18, 2),
baQty [DECIMAL](18, 2)
);
WHILE @count <= (SELECT COUNT(*) FROM #Temp1)
---将展望明细中第一行的现存量+预计入库量-预计出库量=结存量，第二行中结存量=现存量+预计入库量-预计出库量+第一行结存量，第三行同第二行
BEGIN
INSERT INTO #Temp2
SELECT SeriesId,
djh,
CONVERT(VARCHAR(100), ddate, 23) AS ddate,
cvouchname,
cwhcode,
cwhname,
cInvCode,
cInvName,
cInvStd,
cComUnitName,
cmemo,
--ISNULL(iqty,0),
--ISNULL(inqty,0),
--ISNULL(outqty,0),
CAST(iqty AS DECIMAL(18, 2)) AS iqty,
CAST(inqty AS DECIMAL(18, 2)) AS inqty,
CAST(outqty AS DECIMAL(18, 2)) AS outqty,
(CASE SeriesId
WHEN 1 THEN
CAST(iqty AS DECIMAL(18, 2)) + CAST(inqty AS DECIMAL(18, 2)) - CAST(outqty AS DECIMAL(18, 2))
ELSE
(CAST(iqty AS DECIMAL(18, 2)) + CAST(inqty AS DECIMAL(18, 2)) - CAST(outqty AS DECIMAL(18, 2)) +
(
SELECT #Temp2.baQty FROM #Temp2 WHERE SeriesId = @count - 1
)
)
END
) AS baQty
FROM #Temp1
WHERE SeriesId = @count;
SET @count = @count + 1;
END;
SELECT SeriesId,
djh,
(CASE CONVERT(VARCHAR(100), ddate, 23)
WHEN '1900-01-01' THEN
''
ELSE
CONVERT(VARCHAR(100), ddate, 23)
END
) AS ddate,
cvouchname,
cwhcode,
cwhname,
cInvCode,
cInvName,
cInvStd,
cComUnitName,
cmemo,
iqty,
inqty,
outqty,
baQty
FROM #Temp2;
--drop table #Temp1
--drop table #Temp2
--drop table TempKCView_SZNK2105157_0_Temp1
--drop table TempKCView_SZNK2105157_0_Temp2
--drop table TempKCView_SZNK2105157_0_Temp3
扫一扫
1493
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
