store procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

store procedure

Author

Topic

karuppasamy
Starting Member

1 Post

Posted - 2011-05-26 : 08:59:23

Please explain this sp

USE [NUTWARE_CLIENT_SVWG]
GO
/****** Object: StoredProcedure [dbo].[spINV_RPT_LOT_SUM] Script Date: 05/26/2011 13:03:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[spINV_RPT_LOT_SUM]
@INV_LOT_ID AS INT,
@LOT_NO_VAR AS VARCHAR(15)
AS

--******************************************************************************************************************************************************************************************
SET NOCOUNT ON

--******************************************************************************************************************************************************************************************
SELECT
PROD_GRADE_CODE,
PROD_GRADE_DESC,
PROD_SIZE_CODE,
PROD_SIZE_DESC,
SUM(WGHT_HALVES) AS WGHT_HALV,
SUM(GKW) AS WGHT_GKW,
SUM(WGHT_NET) AS WGHT_NET
INTO #TMP_BASE
FROM vwINV_RPT_RECV_DETAIL
WHERE INV_LOT_ID = @INV_LOT_ID
GROUP BY
PROD_GRADE_CODE,
PROD_GRADE_DESC,
PROD_SIZE_CODE,
PROD_SIZE_DESC;
--SELECT * FROM #TMP_BASE

--******************************************************************************************************************************************************************************************
SELECT
PROD_GRADE_CODE,
PROD_GRADE_DESC,
PROD_SIZE_CODE,
PROD_SIZE_DESC,
SUM(WGHT_NET) AS WGHT_NET
INTO #TMP_BASE2
FROM vwINV_RPT_RECV_DETAIL
WHERE
DESTIN_CURR = @LOT_NO_VAR
GROUP BY
PROD_GRADE_CODE,
PROD_GRADE_DESC,
PROD_SIZE_CODE,
PROD_SIZE_DESC;
--SELECT * FROM #TMP_BASE2
--******************************************************************************************************************************************************************************************

--******************************************************************************************************************************************************************************************
DECLARE @WGHT_DUMPED AS INT
SET @WGHT_DUMPED = ISNULL((SELECT SUM(WGHT_NET) FROM #TMP_BASE2),0)
--PRINT @WGHT_DUMPED

DECLARE @TP_NET AS INT
DECLARE @TP_GKW AS INT
DECLARE @TP_VAR AS INT
DECLARE @TP_DELV AS DECIMAL(18,4)
SET @TP_NET = ISNULL((SELECT SUM(WGHT_NET) FROM #TMP_BASE WHERE PROD_SIZE_CODE IN ('ML', 'TP')),0)
SET @TP_GKW = ISNULL((SELECT SUM(WGHT_GKW) FROM #TMP_BASE WHERE PROD_SIZE_CODE IN ('ML', 'TP')),0)
SET @TP_VAR = @TP_NET - @TP_GKW

SET @TP_DELV =
ISNULL(CONVERT(DECIMAL(18,4),
(
CONVERT(DECIMAL(18,4), @TP_VAR) / CONVERT(DECIMAL(18,4), @WGHT_DUMPED)
)
)
,0)

--******************************************************************************************************************************************************************************************
--******************************************************************************************************************************************************************************************
--******************************************************************************************************************************************************************************************

--******************************************************************************************************************************************************************************************
SELECT DISTINCT
INV_LOT_ID,
LOT_NO_VAR,
TAG_ID
INTO #TMP1Z
FROM vwINV_TAG_LOT1
WHERE
DESTIN_CURR = @LOT_NO_VAR;

--******************************************************************************************************************************************************************************************
SELECT
SUM(TAG_NET_WGHT) AS TAG_NET_WGHT,
PERCENT_EDIBLE
INTO #TMP_LOT1
FROM vwBASE_INV_LOT_DFA2
WHERE TAG_ID IN (SELECT TAG_ID FROM #TMP1Z)
AND TYPE_CLASS <> 'CON_WLNT'
GROUP BY
PERCENT_EDIBLE;

--******************************************************************************************************************************************************************************************
SELECT
CASE WHEN PERCENT_EDIBLE IS NULL OR PERCENT_EDIBLE = 0
THEN 0
ELSE CONVERT(INT,(TAG_NET_WGHT * PERCENT_EDIBLE))
END WGHT_GKW_VAR,
TAG_NET_WGHT,
PERCENT_EDIBLE
INTO #TMP_FINAL1A
FROM #TMP_LOT1
GROUP BY
TAG_NET_WGHT,
PERCENT_EDIBLE;
--******************************************************************************************************************************************************************************************

--******************************************************************************************************************************************************************************************
SELECT
SUM(WGHT_GKW_VAR) AS WGHT_GKW_VAR,
SUM(TAG_NET_WGHT) AS TAG_NET_WGHT
INTO #TMP_FINAL1B
FROM #TMP_FINAL1A;
--******************************************************************************************************************************************************************************************

--******************************************************************************************************************************************************************************************
SELECT
WGHT_GKW_VAR,
TAG_NET_WGHT
INTO #TMP_FINAL1
FROM #TMP_FINAL1B;
--******************************************************************************************************************************************************************************************

--**********************************************************************************************
--CONSOLIDATED TAGS

--**********************************************************************************************
SELECT
LOT_NO_VAR
INTO #TMP_CON
FROM vwLIST_LOT_NO
WHERE INV_LOT_ID IN (SELECT INV_LOT_ID FROM #TMP1Z)
AND TYPE_CLASS = 'CON_WLNT'

--**********************************************************************************************
SELECT DISTINCT
INV_LOT_ID,
TAG_ID
INTO #TMP_DESTIN1
FROM LOG_INV_TAG
WHERE DESTIN_CURR IN (SELECT LOT_NO_VAR FROM #TMP_CON)
AND TAG_STATUS <> 'CANCEL';

--**********************************************************************************************
SELECT DISTINCT
SUM(TAG_NET_WGHT) AS TAG_NET_WGHT,
PERCENT_EDIBLE
INTO #TMP_CON2
FROM vwBASE_INV_LOT_DFA2
WHERE TAG_ID IN (SELECT TAG_ID FROM #TMP_DESTIN1)
GROUP BY
PERCENT_EDIBLE;

--**********************************************************************************************
SELECT DISTINCT
CASE WHEN PERCENT_EDIBLE IS NULL OR PERCENT_EDIBLE = 0
THEN 0
ELSE CONVERT(INT,(TAG_NET_WGHT * PERCENT_EDIBLE))
END WGHT_GKW_VAR,
TAG_NET_WGHT,
PERCENT_EDIBLE
INTO #TMP_FINAL2A
FROM #TMP_CON2;

--******************************************************************************************************************************************************************************************
SELECT DISTINCT
SUM(WGHT_GKW_VAR) AS WGHT_GKW_VAR,
SUM(TAG_NET_WGHT) AS TAG_NET_WGHT
INTO #TMP_FINAL2B
FROM #TMP_FINAL2A;

--******************************************************************************************************************************************************************************************
SELECT
WGHT_GKW_VAR,
TAG_NET_WGHT
INTO #TMP_FINAL2
FROM #TMP_FINAL2B;

--******************************************************************************************************************************************************************************************
SELECT
WGHT_GKW_VAR,
TAG_NET_WGHT
INTO #TMP1ZZ_XX
FROM
(
SELECT
WGHT_GKW_VAR,
TAG_NET_WGHT
FROM #TMP_FINAL1

UNION ALL

SELECT
WGHT_GKW_VAR,
TAG_NET_WGHT
FROM #TMP_FINAL2
) AS TEMP_UNION

--******************************************************************************************************************************************************************************************
SELECT
SUM(WGHT_GKW_VAR) AS WG,
SUM(TAG_NET_WGHT) AS WT
INTO #TMP_WGT
FROM #TMP1ZZ_XX;

--******************************************************************************************************************************************************************************************
--******************************************************************************************************************************************************************************************
DECLARE @RECV_ED_PER DECIMAL(18,4)

SELECT
CONVERT(DECIMAL(18,4),WG) / CONVERT(DECIMAL(18,4), WT) AS PERCENT_EDIBLE
INTO #TMP1ZZ
FROM #TMP_WGT;

SET @RECV_ED_PER = ISNULL((SELECT CONVERT(DECIMAL(18,4),PERCENT_EDIBLE) FROM #TMP1ZZ),0)
--******************************************************************************************************************************************************************************************
--******************************************************************************************************************************************************************************************

--******************************************************************************************************************************************************************************************
--******************************************************************************************************************************************************************************************
DECLARE @WGHT_NET INT

SELECT
SUM(WGHT_NET) AS WGHT_NET
INTO #TMP1
FROM #TMP_BASE
WHERE PROD_SIZE_CODE NOT IN ('MEAL','ML');

SET @WGHT_NET = ISNULL((SELECT SUM(WGHT_NET) FROM #TMP1),0)
--******************************************************************************************************************************************************************************************
--******************************************************************************************************************************************************************************************

--******************************************************************************************************************************************************************************************
DECLARE @WGHT_GKW INT

SELECT
SUM(WGHT_GKW) AS WGHT_GKW
INTO #TMP2
FROM #TMP_BASE
WHERE PROD_SIZE_CODE NOT IN ('MEAL','ML');

SET @WGHT_GKW = ISNULL((SELECT SUM(WGHT_GKW) FROM #TMP2),0)
--******************************************************************************************************************************************************************************************
--******************************************************************************************************************************************************************************************

--******************************************************************************************************************************************************************************************
DECLARE @WGHT_HLV_LP AS INT
DECLARE @WGHT_HLV_LH AS INT
DECLARE @WGHT_HLV_SH AS INT
SET @WGHT_HLV_LP = ISNULL((SELECT SUM(WGHT_HALV) FROM #TMP_BASE WHERE PROD_SIZE_CODE IN ('LP','LPC')),0)
SET @WGHT_HLV_SH = ISNULL((SELECT SUM(WGHT_HALV) FROM #TMP_BASE WHERE PROD_SIZE_CODE = 'SM HLV'),0)
SET @WGHT_HLV_LH = ISNULL((SELECT SUM(WGHT_HALV) FROM #TMP_BASE WHERE PROD_SIZE_CODE = 'LG HLV'),0)
--SELECT @WGHT_HLV_LP, @WGHT_HLV_SH, @WGHT_HLV_LH

--******************************************************************************************************************************************************************************************
DECLARE @WGHT_NET_LP AS INT
DECLARE @WGHT_NET_LH AS INT
DECLARE @WGHT_NET_SH AS INT
SET @WGHT_NET_LP = ISNULL((SELECT SUM(WGHT_NET) FROM #TMP_BASE WHERE PROD_SIZE_CODE IN ('LP','LPC')),0)
SET @WGHT_NET_SH = ISNULL((SELECT SUM(WGHT_NET) FROM #TMP_BASE WHERE PROD_SIZE_CODE = 'SM HLV'),0)
SET @WGHT_NET_LH = ISNULL((SELECT SUM(WGHT_NET) FROM #TMP_BASE WHERE PROD_SIZE_CODE = 'LG HLV'),0)
--SELECT @WGHT_NET_LP, @WGHT_NET_SH, @WGHT_NET_LH
--******************************************************************************************************************************************************************************************

--******************************************************************************************************************************************************************************************
DECLARE @PER_NET_LP AS DECIMAL(10,4)
DECLARE @PER_NET_LH AS DECIMAL(10,4)
DECLARE @PER_NET_SH AS DECIMAL(10,4)

IF @WGHT_NET_LP = 0
BEGIN
SET @PER_NET_LP = 0
END
ELSE
BEGIN
SET @PER_NET_LP =

ISNULL(

CONVERT(DECIMAL(10,4),
(
CONVERT(DECIMAL(10,4), @WGHT_HLV_LP) / CONVERT(DECIMAL(10,4),@WGHT_NET_LP)
)
),0)

END

IF @WGHT_NET_LH = 0
BEGIN
SET @PER_NET_LH = 0
END
ELSE
BEGIN
SET @PER_NET_LH = ISNULL(CONVERT(DECIMAL(10,4), ( CONVERT(DECIMAL(10,4), @WGHT_HLV_LH) / CONVERT(DECIMAL(10,4),@WGHT_NET_LH))),0)
END

IF @WGHT_NET_SH = 0
BEGIN
SET @PER_NET_SH = 0
END
ELSE
BEGIN
SET @PER_NET_SH = ISNULL(CONVERT(DECIMAL(10,4), ( CONVERT(DECIMAL(10,4), @WGHT_HLV_SH) / CONVERT(DECIMAL(10,4),@WGHT_NET_SH))),0)
END

--SELECT @PER_NET_LP, @PER_NET_SH, @PER_NET_LH
--******************************************************************************************************************************************************************************************

--******************************************************************************************************************************************************************************************
DECLARE @WGHT_ACCEPT INT
DECLARE @WGHT_REWORK INT
DECLARE @WGHT_HALV INT

DECLARE @WGHT_LP INT
DECLARE @WGHT_LH INT
DECLARE @WGHT_SH INT

DECLARE @WGHT_VAR INT
DECLARE @WGHT_ML INT

SET @WGHT_ACCEPT = ISNULL((SELECT SUM(WGHT_NET) FROM #TMP_BASE WHERE PROD_GRADE_CODE = 'ACCEPT' AND PROD_SIZE_CODE NOT IN ('MEAL','ML')) ,0)
SET @WGHT_REWORK = ISNULL((SELECT SUM(WGHT_NET) FROM #TMP_BASE WHERE PROD_GRADE_CODE IN ('REWORK', 'SH-Rework') AND PROD_SIZE_CODE NOT IN ('MEAL','ML')),0)
SET @WGHT_HALV = ISNULL((SELECT SUM(WGHT_HALV) FROM #TMP_BASE WHERE PROD_SIZE_CODE IN ('LP','LPC','SM HLV','LG HLV')),0)

SET @WGHT_LP = ISNULL((SELECT SUM(WGHT_GKW) FROM #TMP_BASE WHERE PROD_SIZE_CODE IN ('LP','LPC')),0)
SET @WGHT_SH = ISNULL((SELECT SUM(WGHT_GKW) FROM #TMP_BASE WHERE PROD_SIZE_CODE = 'SM HLV'),0)
SET @WGHT_LH = ISNULL((SELECT SUM(WGHT_GKW) FROM #TMP_BASE WHERE PROD_SIZE_CODE = 'LG HLV'),0)

SET @WGHT_ML = ISNULL((SELECT SUM(WGHT_NET) FROM #TMP_BASE WHERE PROD_SIZE_CODE IN ('MEAL','ML')),0)

SET @WGHT_VAR = @WGHT_LP + @WGHT_SH + @WGHT_LH

--SELECT @WGHT_VAR
--******************************************************************************************************************************************************************************************

--******************************************************************************************************************************************************************************************
DECLARE @LH_PER_GKW1 DECIMAL(18,4)
DECLARE @LH_PER_GKW2 DECIMAL(18,4)
DECLARE @SH_PER_GKW1 DECIMAL(18,4)
DECLARE @SH_PER_GKW2 DECIMAL(18,4)
DECLARE @LP_PER_GKW1 DECIMAL(18,4)
DECLARE @LP_PER_GKW2 DECIMAL(18,4)

SET @LP_PER_GKW1 =
ISNULL(CONVERT(DECIMAL(18,4),
(
CONVERT(DECIMAL(18,4), (1 - @PER_NET_LP)) * CONVERT(DECIMAL(18,4), @WGHT_LP)
)
)
,0)

SET @LP_PER_GKW2 =
ISNULL(CONVERT(DECIMAL(18,4),
(
CONVERT(DECIMAL(18,4), @PER_NET_LP) * CONVERT(DECIMAL(18,4), @WGHT_LP)
)
)
,0)

SET @LH_PER_GKW1 =
ISNULL(CONVERT(DECIMAL(18,4),
(
CONVERT(DECIMAL(18,4), (1 - @PER_NET_LH)) * CONVERT(DECIMAL(18,4), @WGHT_LH)
)
)
,0)

SET @LH_PER_GKW2 =
ISNULL(CONVERT(DECIMAL(18,4),
(
CONVERT(DECIMAL(18,4), @PER_NET_LH) * CONVERT(DECIMAL(18,4), @WGHT_LH)
)
)
,0)

SET @SH_PER_GKW1 =
ISNULL(CONVERT(DECIMAL(18,4),
(
CONVERT(DECIMAL(18,4), (1 - @PER_NET_SH)) * CONVERT(DECIMAL(18,4), @WGHT_SH)
)
)
,0)

SET @SH_PER_GKW2 =
ISNULL(CONVERT(DECIMAL(18,4),
(
CONVERT(DECIMAL(18,4), @PER_NET_SH) * CONVERT(DECIMAL(18,4), @WGHT_SH)
)
)
,0)

--SELECT @LH_PER_GKW1, @SH_PER_GKW1, @LP_PER_GKW1, @LH_PER_GKW2, @SH_PER_GKW2, @LP_PER_GKW2
--******************************************************************************************************************************************************************************************

--******************************************************************************************************************************************************************************************
DECLARE @TOTAL_SH_LH_LP_1 AS INT
DECLARE @TOTAL_SH_LH_LP_2 AS INT
DECLARE @TOTAL_SH_LH_LP_3 AS INT
SET @TOTAL_SH_LH_LP_1 = @SH_PER_GKW1 + @LH_PER_GKW1 + @LP_PER_GKW1
SET @TOTAL_SH_LH_LP_2 = @SH_PER_GKW2 + @LH_PER_GKW2 + @LP_PER_GKW2
SET @TOTAL_SH_LH_LP_3 = @TOTAL_SH_LH_LP_1 + @TOTAL_SH_LH_LP_2

DECLARE @TOTAL_SH_LH_LP_GKW1 AS DECIMAL(18,4)
DECLARE @TOTAL_SH_LH_LP_GKW2 AS DECIMAL(18,4)
SET @TOTAL_SH_LH_LP_GKW1 =
ISNULL(CONVERT(DECIMAL(18,4),
(
CONVERT(DECIMAL(18,4), @TOTAL_SH_LH_LP_1) / CONVERT(DECIMAL(18,4), @WGHT_GKW)
)
)
,0)

SET @TOTAL_SH_LH_LP_GKW2 =
ISNULL(CONVERT(DECIMAL(18,4),
(
CONVERT(DECIMAL(18,4), @TOTAL_SH_LH_LP_2) / CONVERT(DECIMAL(18,4), @TOTAL_SH_LH_LP_3)
)
)
,0)

--SELECT @TOTAL_SH_LH_LP_1, @TOTAL_SH_LH_LP_2, @TOTAL_SH_LH_LP_3, @TOTAL_SH_LH_LP_GKW1, @TOTAL_SH_LH_LP_GKW2
--******************************************************************************************************************************************************************************************

--******************************************************************************************************************************************************************************************
DECLARE @ACCEPT_PER DECIMAL (18,4)
DECLARE @REWORK_PER DECIMAL (18,4)
DECLARE @HALV_PER DECIMAL (18,4)
DECLARE @LP_PER DECIMAL (18,4)
DECLARE @ML_PER DECIMAL (18,4)

--******************************************************************************************************************************************************************************************
IF @WGHT_NET > 0
BEGIN
SET @ACCEPT_PER = CONVERT(DECIMAL(18,4),@WGHT_ACCEPT) / CONVERT(DECIMAL(18,4),@WGHT_NET)
END
ELSE
BEGIN
SET @ACCEPT_PER = 0
END

--******************************************************************************************************************************************************************************************
IF @WGHT_NET > 0
BEGIN
SET @REWORK_PER = CONVERT(DECIMAL(18,4),@WGHT_REWORK) / CONVERT(DECIMAL(18,4),@WGHT_NET)
END
ELSE
BEGIN
SET @REWORK_PER = 0
END

--******************************************************************************************************************************************************************************************
IF @WGHT_VAR > 0
BEGIN
SET @HALV_PER = @TOTAL_SH_LH_LP_GKW2
END
ELSE
BEGIN
SET @HALV_PER = 0
END

IF @WGHT_GKW > 0
BEGIN
SET @LP_PER = @TOTAL_SH_LH_LP_GKW1
END
ELSE
BEGIN
SET @LP_PER = 0
END

--******************************************************************************************************************************************************************************************
IF @WGHT_DUMPED > 0
BEGIN
SET @ML_PER = @TP_DELV
END
ELSE
BEGIN
SET @ML_PER = 0
END

--PRINT @RECV_ED_PER
--PRINT @WGHT_DUMPED
--PRINT @WGHT_GKW

--******************************************************************************************************************************************************************************************
DECLARE @PROCESS_YIELD AS DECIMAL(18,4)

--******************************************************************************************************************************************************************************************
IF @WGHT_DUMPED > 0
BEGIN
SET @PROCESS_YIELD = CONVERT(DECIMAL(18,4), @WGHT_GKW) / CONVERT(DECIMAL(18,4), @WGHT_DUMPED)
END
ELSE
BEGIN
SET @PROCESS_YIELD = 0
END

--******************************************************************************************************************************************************************************************
DECLARE @PROCESS_LOSS AS DECIMAL(18,4)
SET @PROCESS_LOSS = @RECV_ED_PER - @PROCESS_YIELD

--PRINT @PROCESS_YIELD-
--PRINT @PROCESS_LOSS

--******************************************************************************************************************************************************************************************
SELECT
@WGHT_GKW,
@WGHT_NET,
@ACCEPT_PER,
@REWORK_PER,
@HALV_PER,
@LP_PER,
@ML_PER,
@PROCESS_YIELD,
@PROCESS_LOSS

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-26 : 09:06:50

It's making a whole buch of temporary tables (#TMP1,#TMP2, etc.). Getting various values out of them and then calculating other values with them. After all is said is done these values
SELECT
@WGHT_GKW,
@WGHT_NET,
@ACCEPT_PER,
@REWORK_PER,
@HALV_PER,
@LP_PER,
@ML_PER,
@PROCESS_YIELD,
@PROCESS_LOSS

are being returned to the app that called the procedure.

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources