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
 Loop

Author  Topic 

maya_zakry
Constraint Violating Yak Guru

379 Posts
Posted - 2007-03-13 : 02:32:14
hello,
i have this SP to tally up my inventory tables.. im finding a way to loop through my table tblitemdetail to get necessary parameter to be insert into my other SP (SP_StkAdj_tbl_alignmt) that should accept this params (from the itemdetail) :-
@ItemID ='',
@ClientID='',
@CustomLotNo ='',
@UDF1=NULL,
@UDF2=NULL,
@UDF3 =NULL,
@UDF4 =NULL,
@UDF5=NULL,
@UDF6 =NULL,
@UDF7 =NULL,
@UDF8 =NULL,
@UDF9 =NULL,
@UDF10 =NULL,
@StockID ='0950-4388',
@RecvOwn ='OWN',
@ConsignorID ='JAB1MY'
EG:i will GROUP BY my itemdetail so it will give me the x records of data with :-

SELECT ItemID, CustomLotNo, Ownership, ConsignorID, RecvUDF1, RecvUDF2, RecvUDF3, ownerstatus
FROM tblItemDetail
GROUP BY ItemID, CustomLotNo, Ownership, ConsignorID, RecvUDF1, RecvUDF2, RecvUDF3,ownerstatus
ORDER BY ItemID

with the result then, i need to insert the param into the SP:SP_StkAdj_tbl_alignmt so that it perform the calculation.

so i guess this will need some looping from the result set i get from the group by and some Sp calling from main Sp

~~~Focus on problem, not solution~~~

maya_zakry
Constraint Violating Yak Guru

379 Posts
Posted - 2007-03-13 : 08:21:52
okay.. seems like hard to understand.. to simplify, can we for loop in SP?
if i select * from tbl1, how do i loop through this result set to perform some calculation?

thanks

~~~Focus on problem, not solution~~~
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2007-03-13 : 08:23:48
you can use cursor or while loop to do it

KH
Go to Top of Page

maya_zakry
Constraint Violating Yak Guru

379 Posts
Posted - 2007-03-13 : 20:46:50
hi..
sorry but i've never use cursor and while loop..
plus, i couldnt imagine how to represent the result set(i mean the records that i need to loop).. could u explain or maybe some eg? plizz.. :)

~~~Focus on problem, not solution~~~
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2007-03-13 : 20:53:01
1. create a temp table structure identical to the result of your 1st SP
2. insert into #temptable ( . . . ) exec sp1
3. use cursor to select from the #temptable and loop for all records.
For each record read, call the 2nd SP and pass in the necessary value.

KH
Go to Top of Page

maya_zakry
Constraint Violating Yak Guru

379 Posts
Posted - 2007-03-13 : 20:57:50
okay.. ive search from books online... and trying to apply this sample code into my situation.. but i guess need some help, coz i couldnt understand many part of this code...

USE PalmsDEV

GO
-- Declare and open a global cursor.
DECLARE abc CURSOR STATIC FOR
SELECT ItemID, CustomLotNo, Ownership, ConsignorID, RecvUDF1, RecvUDF2, RecvUDF3, ownerstatus
FROM tblItemDetail
where itemid='flower'
GROUP BY ItemID, CustomLotNo, Ownership, ConsignorID, RecvUDF1, RecvUDF2, RecvUDF3,ownerstatus
ORDER BY ItemID

OPEN abc

-- Declare a cursor variable to hold the cursor output variable
-- from sp_describe_cursor.
DECLARE @Report CURSOR

-- Execute sp_describe_cursor into the cursor variable.
EXEC master.dbo.sp_describe_cursor @cursor_return = @Report OUTPUT,
@cursor_source = N'global', @cursor_identity = N'abc'

-- Fetch all the rows from the sp_describe_cursor output cursor.
FETCH NEXT from @Report
WHILE (@@FETCH_STATUS <> -1)
BEGIN
FETCH NEXT from @Report
--- i guess here i should do my calcualtaion but im not sure how..
--- im also not so sure where sp_describe_cursor come, and maybe i dont understand this eg at all
END

-- Close and deallocate the cursor from sp_describe_cursor.
CLOSE @Report
DEALLOCATE @Report
GO

-- Close and deallocate the original cursor.
CLOSE abc
DEALLOCATE abc
GO

~~~Focus on problem, not solution~~~
Go to Top of Page

maya_zakry
Constraint Violating Yak Guru

379 Posts
Posted - 2007-03-13 : 20:58:56
oh.. khtan.. we post msg at the same time.. couldnt catch ur last post..

~~~Focus on problem, not solution~~~
Go to Top of Page

maya_zakry
Constraint Violating Yak Guru

379 Posts
Posted - 2007-03-13 : 21:01:27
okay, from ur last post, im not clear how to declare a cursor, (point number 3) :-
3. use cursor to select from the #temptable and loop for all records.


~~~Focus on problem, not solution~~~
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2007-03-13 : 21:05:40
see http://www.sqlteam.com/item.asp?ItemID=553

KH
Go to Top of Page

maya_zakry
Constraint Violating Yak Guru

379 Posts
Posted - 2007-03-13 : 21:15:34
tq! good article..

~~~Focus on problem, not solution~~~
Go to Top of Page

maya_zakry
Constraint Violating Yak Guru

379 Posts
Posted - 2007-03-13 : 21:24:10
yeayyy... It works!! a very useful thing ive learned today.. save time, normally i did the looping in my codebehind.. with this cursor it save my time

~~~Focus on problem, not solution~~~
Go to Top of Page

maya_zakry
Constraint Violating Yak Guru

379 Posts
Posted - 2007-03-13 : 22:14:14
help..!
something is wrong.. when i created a SP to do this cursor thing.. it seems like it didnt exec the child SP in the fetching loop.. but when i tried to just print the dataset, it works.. i dunno what is wrong.. coz ive tried my child SP, there's nothing wrong with it.. checkk out my cursor thing SP:-

CREATE PROCEDURE SP_TallyUpInventory
(
@empty varchar(3)
)
AS
BEGIN

DECLARE @ItemID varchar(100), @CustomLotNo varchar(50), @Ownership varchar(10), @ConsignorID varchar(10),
@RecvUDF1 varchar(50), @RecvUDF2 varchar(50),@RecvUDF3 varchar(50),@Ownerstatus varchar(3)

DECLARE c1 CURSOR FOR
SELECT ItemID, CustomLotNo, Ownership, ConsignorID, RecvUDF1, RecvUDF2, RecvUDF3, ownerstatus
FROM tblItemDetail
GROUP BY ItemID, CustomLotNo, Ownership, ConsignorID, RecvUDF1, RecvUDF2, RecvUDF3,ownerstatus
ORDER BY ItemID

OPEN c1

FETCH NEXT FROM c1
INTO @ItemID, @CustomLotNo, @Ownership, @ConsignorID, @RecvUDF1, @RecvUDF2,@RecvUDF3,@Ownerstatus

WHILE @@FETCH_STATUS = 0
BEGIN

EXEC SP_StkAdj_tbl_alignmt
@ItemID =@ItemID,
@ClientID=@Ownership,
@CustomLotNo =@CustomLotNo,
@UDF1=@RecvUDF1,
@UDF2=@RecvUDF2,
@UDF3 =@RecvUDF3,
@UDF4 =NULL,
@UDF5=NULL,
@UDF6 =NULL,
@UDF7 =NULL,
@UDF8 =NULL,
@UDF9 =NULL,
@UDF10 =NULL,
@StockID =@ItemID,
@RecvOwn =@Ownerstatus,
@ConsignorID =@ConsignorID
RETURN

FETCH NEXT FROM c1
INTO @ItemID, @CustomLotNo, @Ownership, @ConsignorID, @RecvUDF1, @RecvUDF2,@RecvUDF3,@Ownerstatus
END

CLOSE c1
DEALLOCATE c1

End
GO


~~~Focus on problem, not solution~~~
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2007-03-13 : 22:24:22
what's the RETURN for ? remove it

KH
Go to Top of Page

maya_zakry
Constraint Violating Yak Guru

379 Posts
Posted - 2007-03-13 : 22:40:33
huh?? just that?.. uwawawaaa.. this is what i get when the code is cut n paste, what is return? huhuh.. i always use but dont even know what it is for.. :( ,

yep, removed.. waiting for the query analyzer run it.. now it's been 12 mins.. compared to just now it didnt run at all.. it only said ---> "The command(s) completed successfully."

~~~Focus on problem, not solution~~~
Go to Top of Page

maya_zakry
Constraint Violating Yak Guru

379 Posts
Posted - 2007-03-13 : 22:44:10
again, the speed problem..! vb will time out this process :(
Total time : 17 mins..huarghhh ngap ngap.. sleepy process

~~~Focus on problem, not solution~~~
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2007-03-14 : 13:07:36
show us what SP_StkAdj_tbl_alignmt does



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page

maya_zakry
Constraint Violating Yak Guru

379 Posts
Posted - 2007-03-15 : 02:22:45
okay, it's quite long.. u want me to paste here>? how do ppl paste the code in code font???

~~~Focus on problem, not solution~~~
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2007-03-15 : 02:25:35
Enclose your codes with [ code ] and [ /code ] without any spacing in between

KH
Go to Top of Page

maya_zakry
Constraint Violating Yak Guru

379 Posts
Posted - 2007-03-15 : 02:29:40
huh??? ok.. :P

~~~Focus on problem, not solution~~~
Go to Top of Page

maya_zakry
Constraint Violating Yak Guru

379 Posts
Posted - 2007-03-15 : 02:31:10
so this is my SP, i dunno if ppl have time to read it :(



CREATE PROCEDURE SP_StkAdj_tbl_alignmt
(
	
	@ItemID VARCHAR(50),
	@ClientID VARCHAR(20),
	@CustomLotNo VARCHAR(20),
	@UDF1 VARCHAR(50),
	@UDF2 VARCHAR(50),
	@UDF3 VARCHAR(50),
	@UDF4 VARCHAR(50),
	@UDF5 VARCHAR(50),
	@UDF6 VARCHAR(50),
	@UDF7 VARCHAR(50),
	@UDF8 VARCHAR(50),
	@UDF9 VARCHAR(50),
	@UDF10 VARCHAR(50),
	@StockID VARCHAR(50),
	@RecvOwn VARCHAR(3),
	@ConsignorID VARCHAR(20)
	
)
AS
BEGIN

--------------------------------------------OWNERSHIP---------------------------------
---------------------------------------------------------------------------------------------------
-- Flag (Used to hold the compulsary field, Y or N)

	DECLARE @Pick1 VARCHAR(1),@Pick2 VARCHAR(1),@Pick3 VARCHAR(1),@Pick4 VARCHAR(1),@Pick5 VARCHAR(1),
		@Pick6 VARCHAR(1),@Pick7 VARCHAR(1),@Pick8 VARCHAR(1),@Pick9 VARCHAR(1),@Pick10 VARCHAR(1),
		@PickCustomLot VARCHAR(1), @PickExpiryDate VARCHAR(1)

-- CustomLot
	SELECT  @PickCustomLot = CASE WHEN EXISTS (
				SELECT	p.PickType 
				FROM		tblItemPickFormat p
				INNER JOIN	tblItemCrossRef r ON r.ItemClientRef = p.ItemClientRef
				WHERE	r.ClientID = @ClientID AND r.ItemID = @ItemID AND p.PickType = 'Custom Lot No.'
			)
			THEN 'Y'
			ELSE 'N'
			END
	FROM tblClient
	WHERE ClientID = @ClientID

-- Expiry Date
	SELECT  @PickExpiryDate = CASE WHEN EXISTS (
				SELECT	p.PickType 
				FROM		tblItemPickFormat p
				INNER JOIN	tblItemCrossRef r ON r.ItemClientRef = p.ItemClientRef
				WHERE	r.ClientID = @ClientID AND r.ItemID = @ItemID AND p.PickType = 'Expiry Date'
			)
			THEN 'Y'
			ELSE 'N'
			END
	FROM tblClient
	WHERE ClientID = @ClientID

-- UDF1
	SELECT  @Pick1 = CASE WHEN EXISTS (
				SELECT	p.PickType 
				FROM		tblItemPickFormat p
				INNER JOIN	tblClient c ON c.ClientUDF1 = p.PickType 
				INNER JOIN	tblItemCrossRef r ON r.ClientID = c.ClientID AND r.ItemClientRef = p.ItemClientRef
				WHERE	c.ClientID = @ClientID AND r.ItemID = @ItemID
			)
			THEN 'Y'
			ELSE 'N'
			END
	FROM tblClient
	WHERE ClientID = @ClientID

-- UDF2
	SELECT  @Pick2 = CASE WHEN EXISTS (
				SELECT	p.PickType 
				FROM		tblItemPickFormat p
				INNER JOIN	tblClient c ON c.ClientUDF2 = p.PickType 
				INNER JOIN	tblItemCrossRef r ON r.ClientID = c.ClientID AND r.ItemClientRef = p.ItemClientRef
				WHERE	c.ClientID = @ClientID AND r.ItemID = @ItemID
			)
			THEN 'Y'
			ELSE 'N'
			END
	FROM tblClient
	WHERE ClientID = @ClientID

-- UDF3
	SELECT  @Pick3 = CASE WHEN EXISTS (
				SELECT	p.PickType 
				FROM		tblItemPickFormat p
				INNER JOIN	tblClient c ON c.ClientUDF3 = p.PickType 
				INNER JOIN	tblItemCrossRef r ON r.ClientID = c.ClientID AND r.ItemClientRef = p.ItemClientRef
				WHERE	c.ClientID = @ClientID AND r.ItemID = @ItemID
			)
			THEN 'Y'
			ELSE 'N'
			END
	FROM tblClient
	WHERE ClientID = @ClientID

-- UDF4
	SELECT  @Pick4 = CASE WHEN EXISTS (
				SELECT	p.PickType 
				FROM		tblItemPickFormat p
				INNER JOIN	tblClient c ON c.ClientUDF4 = p.PickType 
				INNER JOIN	tblItemCrossRef r ON r.ClientID = c.ClientID AND r.ItemClientRef = p.ItemClientRef
				WHERE	c.ClientID = @ClientID AND r.ItemID = @ItemID
			)
			THEN 'Y'
			ELSE 'N'
			END
	FROM tblClient
	WHERE ClientID = @ClientID

-- UDF5
	SELECT  @Pick5 = CASE WHEN EXISTS (
				SELECT	p.PickType 
				FROM		tblItemPickFormat p
				INNER JOIN	tblClient c ON c.ClientUDF5 = p.PickType 
				INNER JOIN	tblItemCrossRef r ON r.ClientID = c.ClientID AND r.ItemClientRef = p.ItemClientRef
				WHERE	c.ClientID = @ClientID AND r.ItemID = @ItemID
			)
			THEN 'Y'
			ELSE 'N'
			END
	FROM tblClient
	WHERE ClientID = @ClientID

-- UDF6
	SELECT  @Pick6 = CASE WHEN EXISTS (
				SELECT	p.PickType 
				FROM		tblItemPickFormat p
				INNER JOIN	tblClient c ON c.ClientUDF6 = p.PickType 
				INNER JOIN	tblItemCrossRef r ON r.ClientID = c.ClientID AND r.ItemClientRef = p.ItemClientRef
				WHERE	c.ClientID = @ClientID AND r.ItemID = @ItemID
			)
			THEN 'Y'
			ELSE 'N'
			END
	FROM tblClient
	WHERE ClientID = @ClientID

-- UDF7
	SELECT  @Pick7 = CASE WHEN EXISTS (
				SELECT	p.PickType 
				FROM		tblItemPickFormat p
				INNER JOIN	tblClient c ON c.ClientUDF7 = p.PickType 
				INNER JOIN	tblItemCrossRef r ON r.ClientID = c.ClientID AND r.ItemClientRef = p.ItemClientRef
				WHERE	c.ClientID = @ClientID AND r.ItemID = @ItemID
			)
			THEN 'Y'
			ELSE 'N'
			END
	FROM tblClient
	WHERE ClientID = @ClientID

-- UDF8
	SELECT  @Pick8 = CASE WHEN EXISTS (
				SELECT	p.PickType 
				FROM		tblItemPickFormat p
				INNER JOIN	tblClient c ON c.ClientUDF8 = p.PickType 
				INNER JOIN	tblItemCrossRef r ON r.ClientID = c.ClientID AND r.ItemClientRef = p.ItemClientRef
				WHERE	c.ClientID = @ClientID AND r.ItemID = @ItemID
			)
			THEN 'Y'
			ELSE 'N'
			END
	FROM tblClient
	WHERE ClientID = @ClientID

-- UDF9
	SELECT  @Pick9 = CASE WHEN EXISTS (
				SELECT	p.PickType 
				FROM		tblItemPickFormat p
				INNER JOIN	tblClient c ON c.ClientUDF9 = p.PickType 
				INNER JOIN	tblItemCrossRef r ON r.ClientID = c.ClientID AND r.ItemClientRef = p.ItemClientRef
				WHERE	c.ClientID = @ClientID AND r.ItemID = @ItemID
			)
			THEN 'Y'
			ELSE 'N'
			END
	FROM tblClient
	WHERE ClientID = @ClientID

-- UDF10
	SELECT  @Pick10 = CASE WHEN EXISTS (
				SELECT	p.PickType 
				FROM		tblItemPickFormat p
				INNER JOIN	tblClient c ON c.ClientUDF10 = p.PickType 
				INNER JOIN	tblItemCrossRef r ON r.ClientID = c.ClientID AND r.ItemClientRef = p.ItemClientRef
				WHERE	c.ClientID = @ClientID AND r.ItemID = @ItemID
			)
			THEN 'Y'
			ELSE 'N'
			END
	FROM tblClient
	WHERE ClientID = @ClientID

-------- After get the compulsary field, we get the value using the flag -----------------

	-- Variable to hold the UDF value
	DECLARE @TempRecvUDF1 varchar(50),@TempRecvUDF2 varchar(50),@TempRecvUDF3 varchar(50),@TempRecvUDF4 varchar(50),@TempRecvUDF5 varchar(50),
		@TempRecvUDF6 varchar(50),@TempRecvUDF7 varchar(50),@TempRecvUDF8 varchar(50),@TempRecvUDF9 varchar(50),@TempRecvUDF10 varchar(50)
-- RecvUDF1
	IF @Pick1  = 'Y'
		IF @UDF1 is NULL
			SET @TempRecvUDF1 = '0'
		ELSE SET @TempRecvUDF1 = @UDF1
	ELSE SET @TempRecvUDF1 = '0'
-- RecvUDF2
	IF @Pick2  = 'Y'
		IF @UDF2 is NULL
			SET @TempRecvUDF2 = '0'
		ELSE SET @TempRecvUDF2 = @UDF2
	ELSE SET @TempRecvUDF2 = '0'

-- RecvUDF3
	IF @Pick3  = 'Y'
		IF @UDF3 is NULL
			SET @TempRecvUDF3 = '0'
		ELSE SET @TempRecvUDF3 = @UDF3
	ELSE SET @TempRecvUDF3 = '0'
-- RecvUDF4
	IF @Pick4  = 'Y'
		IF @UDF4 is NULL
			SET @TempRecvUDF4 = '0'
		ELSE SET @TempRecvUDF4 = @UDF4
	ELSE SET @TempRecvUDF4 = '0'
-- RecvUDF5
	IF @Pick5  = 'Y'
		IF @UDF5 is NULL
			SET @TempRecvUDF5 = '0'
		ELSE SET @TempRecvUDF5 = @UDF5
	ELSE SET @TempRecvUDF5 = '0'
-- RecvUDF6
	IF @Pick6  = 'Y'
		IF @UDF6 is NULL
			SET @TempRecvUDF6 = '0'
		ELSE SET @TempRecvUDF6 = @UDF6
	ELSE SET @TempRecvUDF6 = '0'
-- RecvUDF7
	IF @Pick7  = 'Y'
		IF @UDF7 is NULL
			SET @TempRecvUDF7 = '0'
		ELSE SET @TempRecvUDF7 = @UDF7
	ELSE SET @TempRecvUDF7 = '0'
-- RecvUDF8
	IF @Pick8  = 'Y'
		IF @UDF8 is NULL
			SET @TempRecvUDF8 = '0'
		ELSE SET @TempRecvUDF8 = @UDF8
	ELSE SET @TempRecvUDF8 = '0'
-- RecvUDF9
	IF @Pick9  = 'Y'
		IF @UDF9 is NULL
			SET @TempRecvUDF9 = '0'
		ELSE SET @TempRecvUDF9 = @UDF9
	ELSE SET @TempRecvUDF9 = '0'
-- RecvUDF10
	IF @Pick10  = 'Y'
		IF @UDF10 is NULL
			SET @TempRecvUDF10 = '0'
		ELSE SET @TempRecvUDF10 = @UDF10
	ELSE SET @TempRecvUDF10 = '0'


--------------------------------------------CONSIGNORID---------------------------------
----------------------------------------------------------------------------------------
-- Flag (Used to hold the compulsary field, Y or N)

	DECLARE @cPick1 VARCHAR(1),@cPick2 VARCHAR(1),@cPick3 VARCHAR(1),@cPick4 VARCHAR(1),@cPick5 VARCHAR(1),
		@cPick6 VARCHAR(1),@cPick7 VARCHAR(1),@cPick8 VARCHAR(1),@cPick9 VARCHAR(1),@cPick10 VARCHAR(1),
		@cPickCustomLot VARCHAR(1), @cPickExpiryDate VARCHAR(1)

-- CustomLot
	SELECT  @cPickCustomLot = CASE WHEN EXISTS (
				SELECT	p.PickType 
				FROM		tblItemPickFormat p
				INNER JOIN	tblItemCrossRef r ON r.ItemClientRef = p.ItemClientRef
				WHERE	r.ClientID = @ConsignorID AND r.ItemID = @ItemID AND p.PickType = 'Custom Lot No.'
			)
			THEN 'Y'
			ELSE 'N'
			END
	FROM tblClient
	WHERE ClientID = @ConsignorID

-- Expiry Date
	SELECT  @cPickExpiryDate = CASE WHEN EXISTS (
				SELECT	p.PickType 
				FROM		tblItemPickFormat p
				INNER JOIN	tblItemCrossRef r ON r.ItemClientRef = p.ItemClientRef
				WHERE	r.ClientID = @ConsignorID AND r.ItemID = @ItemID AND p.PickType = 'Expiry Date'
			)
			THEN 'Y'
			ELSE 'N'
			END
	FROM tblClient
	WHERE ClientID = @ConsignorID

-- UDF1
	SELECT  @cPick1 = CASE WHEN EXISTS (
				SELECT	p.PickType 
				FROM		tblItemPickFormat p
				INNER JOIN	tblClient c ON c.ClientUDF1 = p.PickType 
				INNER JOIN	tblItemCrossRef r ON r.ClientID = c.ClientID AND r.ItemClientRef = p.ItemClientRef
				WHERE	c.ClientID = @ConsignorID AND r.ItemID = @ItemID
			)
			THEN 'Y'
			ELSE 'N'
			END
	FROM tblClient
	WHERE ClientID = @ConsignorID

-- UDF2
	SELECT  @cPick2 = CASE WHEN EXISTS (
				SELECT	p.PickType 
				FROM		tblItemPickFormat p
				INNER JOIN	tblClient c ON c.ClientUDF2 = p.PickType 
				INNER JOIN	tblItemCrossRef r ON r.ClientID = c.ClientID AND r.ItemClientRef = p.ItemClientRef
				WHERE	c.ClientID = @ConsignorID AND r.ItemID = @ItemID
			)
			THEN 'Y'
			ELSE 'N'
			END
	FROM tblClient
	WHERE ClientID = @ConsignorID

-- UDF3
	SELECT  @cPick3 = CASE WHEN EXISTS (
				SELECT	p.PickType 
				FROM		tblItemPickFormat p
				INNER JOIN	tblClient c ON c.ClientUDF3 = p.PickType 
				INNER JOIN	tblItemCrossRef r ON r.ClientID = c.ClientID AND r.ItemClientRef = p.ItemClientRef
				WHERE	c.ClientID = @ConsignorID AND r.ItemID = @ItemID
			)
			THEN 'Y'
			ELSE 'N'
			END
	FROM tblClient
	WHERE ClientID = @ConsignorID

-- UDF4
	SELECT  @cPick4 = CASE WHEN EXISTS (
				SELECT	p.PickType 
				FROM		tblItemPickFormat p
				INNER JOIN	tblClient c ON c.ClientUDF4 = p.PickType 
				INNER JOIN	tblItemCrossRef r ON r.ClientID = c.ClientID AND r.ItemClientRef = p.ItemClientRef
				WHERE	c.ClientID = @ConsignorID AND r.ItemID = @ItemID
			)
			THEN 'Y'
			ELSE 'N'
			END
	FROM tblClient
	WHERE ClientID = @ConsignorID

-- UDF5
	SELECT  @cPick5 = CASE WHEN EXISTS (
				SELECT	p.PickType 
				FROM		tblItemPickFormat p
				INNER JOIN	tblClient c ON c.ClientUDF5 = p.PickType 
				INNER JOIN	tblItemCrossRef r ON r.ClientID = c.ClientID AND r.ItemClientRef = p.ItemClientRef
				WHERE	c.ClientID = @ConsignorID AND r.ItemID = @ItemID
			)
			THEN 'Y'
			ELSE 'N'
			END
	FROM tblClient
	WHERE ClientID = @ConsignorID

-- UDF6
	SELECT  @cPick6 = CASE WHEN EXISTS (
				SELECT	p.PickType 
				FROM		tblItemPickFormat p
				INNER JOIN	tblClient c ON c.ClientUDF6 = p.PickType 
				INNER JOIN	tblItemCrossRef r ON r.ClientID = c.ClientID AND r.ItemClientRef = p.ItemClientRef
				WHERE	c.ClientID = @ConsignorID AND r.ItemID = @ItemID
			)
			THEN 'Y'
			ELSE 'N'
			END
	FROM tblClient
	WHERE ClientID = @ConsignorID

-- UDF7
	SELECT  @cPick7 = CASE WHEN EXISTS (
				SELECT	p.PickType 
				FROM		tblItemPickFormat p
				INNER JOIN	tblClient c ON c.ClientUDF7 = p.PickType 
				INNER JOIN	tblItemCrossRef r ON r.ClientID = c.ClientID AND r.ItemClientRef = p.ItemClientRef
				WHERE	c.ClientID = @ConsignorID AND r.ItemID = @ItemID
			)
			THEN 'Y'
			ELSE 'N'
			END
	FROM tblClient
	WHERE ClientID = @ConsignorID

-- UDF8
	SELECT  @cPick8 = CASE WHEN EXISTS (
				SELECT	p.PickType 
				FROM		tblItemPickFormat p
				INNER JOIN	tblClient c ON c.ClientUDF8 = p.PickType 
				INNER JOIN	tblItemCrossRef r ON r.ClientID = c.ClientID AND r.ItemClientRef = p.ItemClientRef
				WHERE	c.ClientID = @ConsignorID AND r.ItemID = @ItemID
			)
			THEN 'Y'
			ELSE 'N'
			END
	FROM tblClient
	WHERE ClientID = @ConsignorID

-- UDF9
	SELECT  @cPick9 = CASE WHEN EXISTS (
				SELECT	p.PickType 
				FROM		tblItemPickFormat p
				INNER JOIN	tblClient c ON c.ClientUDF9 = p.PickType 
				INNER JOIN	tblItemCrossRef r ON r.ClientID = c.ClientID AND r.ItemClientRef = p.ItemClientRef
				WHERE	c.ClientID = @ConsignorID AND r.ItemID = @ItemID
			)
			THEN 'Y'
			ELSE 'N'
			END
	FROM tblClient
	WHERE ClientID = @ConsignorID

-- UDF10
	SELECT  @cPick10 = CASE WHEN EXISTS (
				SELECT	p.PickType 
				FROM		tblItemPickFormat p
				INNER JOIN	tblClient c ON c.ClientUDF10 = p.PickType 
				INNER JOIN	tblItemCrossRef r ON r.ClientID = c.ClientID AND r.ItemClientRef = p.ItemClientRef
				WHERE	c.ClientID = @ConsignorID AND r.ItemID = @ItemID
			)
			THEN 'Y'
			ELSE 'N'
			END
	FROM tblClient
	WHERE ClientID = @ConsignorID

-------- After get the compulsary field, we get the value using the flag -----------------

-- Variable to hold the UDF value
DECLARE @cTempRecvUDF1 varchar(50),@cTempRecvUDF2 varchar(50),@cTempRecvUDF3 varchar(50),@cTempRecvUDF4 varchar(50),@cTempRecvUDF5 varchar(50),
	@cTempRecvUDF6 varchar(50),@cTempRecvUDF7 varchar(50),@cTempRecvUDF8 varchar(50),@cTempRecvUDF9 varchar(50),@cTempRecvUDF10 varchar(50)

-- RecvUDF1
	IF @cPick1  = 'Y'
		IF @UDF1 is NULL
			SET @cTempRecvUDF1 = '0'
		ELSE SET @cTempRecvUDF1 = @UDF1
	ELSE SET @cTempRecvUDF1 = '0'
-- RecvUDF2
	IF @cPick2  = 'Y'
		IF @UDF2 is NULL
			SET @cTempRecvUDF2 = '0'
		ELSE SET @cTempRecvUDF2 = @UDF2
	ELSE SET @cTempRecvUDF2 = '0'

-- RecvUDF3
	IF @cPick3  = 'Y'
		IF @UDF3 is NULL
			SET @cTempRecvUDF3 = '0'
		ELSE SET @cTempRecvUDF3 = @UDF3
	ELSE SET @cTempRecvUDF3 = '0'
-- RecvUDF4
	IF @cPick4  = 'Y'
		IF @UDF4 is NULL
			SET @cTempRecvUDF4 = '0'
		ELSE SET @cTempRecvUDF4 = @UDF4
	ELSE SET @cTempRecvUDF4 = '0'
-- RecvUDF5
	IF @cPick5  = 'Y'
		IF @UDF5 is NULL
			SET @cTempRecvUDF5 = '0'
		ELSE SET @cTempRecvUDF5 = @UDF5
	ELSE SET @cTempRecvUDF5 = '0'
-- RecvUDF6
	IF @cPick6  = 'Y'
		IF @UDF6 is NULL
			SET @cTempRecvUDF6 = '0'
		ELSE SET @cTempRecvUDF6 = @UDF6
	ELSE SET @cTempRecvUDF6 = '0'
-- RecvUDF7
	IF @cPick7  = 'Y'
		IF @UDF7 is NULL
			SET @cTempRecvUDF7 = '0'
		ELSE SET @cTempRecvUDF7 = @UDF7
	ELSE SET @cTempRecvUDF7 = '0'
-- RecvUDF8
	IF @cPick8  = 'Y'
		IF @UDF8 is NULL
			SET @cTempRecvUDF8 = '0'
		ELSE SET @cTempRecvUDF8 = @UDF8
	ELSE SET @cTempRecvUDF8 = '0'
-- RecvUDF9
	IF @cPick9  = 'Y'
		IF @UDF9 is NULL
			SET @cTempRecvUDF9 = '0'
		ELSE SET @cTempRecvUDF9 = @UDF9
	ELSE SET @cTempRecvUDF9 = '0'
-- RecvUDF10
	IF @cPick10  = 'Y'
		IF @UDF10 is NULL
			SET @cTempRecvUDF10 = '0'
		ELSE SET @cTempRecvUDF10 = @UDF10
	ELSE SET @cTempRecvUDF10 = '0'


-----------------------------------------------------------------------------------------------UPDATE tblINItemclientQty -------------------------------------------------------------------------------------------------------------------------------------------------------
--IN=InItemClientQty
--cr=Crossref
--c=Consignor

Declare @AVAQtyIN float, @QCQtyIN float, @BookQtyIN float, @HoldQtyIN float, @PickQtyIN float
Declare @VMIAVAQtyIN float, @VMIQCQtyIN float, @VMIBookQtyIN float, @VMIHoldQtyIN float, @VMIPickQtyIN float
Declare @cVMIAVAQtyIN float, @cVMIQCQtyIN float, @cVMIBookQtyIN float, @cVMIHoldQtyIN float, @cVMIPickQtyIN float

	----OWN : Ownership---------
	sELECT @AVAQtyIN=isnull(sum(d.OriginQty),0)-isnull(sum(d.PickQty),0)-isnull(sum(d.HoldQty),0)-isnull(sum(d.QCQty),0)-isnull(sum(d.BookQty),0)+isnull(sum(d.AdjQty),0),
	       @QCQtyIN=isnull(sum(d.QCQty),0),
	       @HoldQtyIN=isnull(sum(d.HoldQty),0),
	       @PickQtyIN=isnull(sum(d.PickQty),0),
	       @BookQtyIN=isnull(sum(d.BookQty),0)
	       
	FROM tblItemDetail d
	WHERE d.ItemID=@ItemID AND
	      d.Ownership=@ClientID AND
	      d.CustomLotNo=@CustomLotNo AND
		(nullif(@tempRecvudf1,'0') IS NULL OR d.recvudf1 = nullif(@tempRecvudf1,'0')) AND 
		(nullif(@tempRecvudf2,'0') IS NULL OR d.recvudf2 = nullif(@tempRecvudf2,'0')) AND 
		(nullif(@tempRecvudf3,'0') IS NULL OR d.recvudf3 = nullif(@tempRecvudf3,'0')) AND 
		(nullif(@tempRecvudf4,'0') IS NULL OR d.recvudf4 = nullif(@tempRecvudf4,'0')) AND 
		(nullif(@tempRecvudf5,'0') IS NULL OR d.recvudf5 = nullif(@tempRecvudf5,'0')) AND 
		(nullif(@tempRecvudf6,'0') IS NULL OR d.recvudf6 = nullif(@tempRecvudf6,'0')) AND 
		(nullif(@tempRecvudf7,'0') IS NULL OR d.recvudf7 = nullif(@tempRecvudf7,'0')) AND 
		(nullif(@tempRecvudf8,'0') IS NULL OR d.recvudf8 = nullif(@tempRecvudf8,'0')) AND 
		(nullif(@tempRecvudf9,'0') IS NULL OR d.recvudf9 = nullif(@tempRecvudf9,'0')) AND 
		(nullif(@tempRecvudf10,'0') IS NULL OR d.recvudf10 = nullif(@tempRecvudf10,'0')) 
	
	------VMI : Ownership------
	sELECT @VMIAVAQtyIN=isnull(sum(d.OriginQty),0)-isnull(sum(d.PickQty),0)-isnull(sum(d.HoldQty),0)-isnull(sum(d.QCQty),0)-isnull(sum(d.BookQty),0)+isnull(sum(d.AdjQty),0),
	       @VMIQCQtyIN=isnull(sum(d.QCQty),0),
	       @VMIHoldQtyIN=isnull(sum(d.HoldQty),0),
	       @VMIPickQtyIN=isnull(sum(d.PickQty),0),
	       @VMIBookQtyIN=isnull(sum(d.BookQty),0)
	       
	FROM tblItemDetail d
	WHERE d.ItemID=@ItemID AND	     
	      d.Ownership=@ClientID AND
	      d.CustomLotNo=@CustomLotNo AND
	      d.OwnerStatus='VMI' AND
		(nullif(@tempRecvudf1,'0') IS NULL OR d.recvudf1 = nullif(@tempRecvudf1,'0')) AND 
		(nullif(@tempRecvudf2,'0') IS NULL OR d.recvudf2 = nullif(@tempRecvudf2,'0'))  AND 
		(nullif(@tempRecvudf3,'0') IS NULL OR d.recvudf3 = nullif(@tempRecvudf3,'0')) AND 
		(nullif(@tempRecvudf4,'0') IS NULL OR d.recvudf4 = nullif(@tempRecvudf4,'0')) AND 
		(nullif(@tempRecvudf5,'0') IS NULL OR d.recvudf5 = nullif(@tempRecvudf5,'0')) AND 
		(nullif(@tempRecvudf6,'0') IS NULL OR d.recvudf6 = nullif(@tempRecvudf6,'0')) AND 
		(nullif(@tempRecvudf7,'0') IS NULL OR d.recvudf7 = nullif(@tempRecvudf7,'0')) AND 
		(nullif(@tempRecvudf8,'0') IS NULL OR d.recvudf8 = nullif(@tempRecvudf8,'0')) AND 
		(nullif(@tempRecvudf9,'0') IS NULL OR d.recvudf9 = nullif(@tempRecvudf9,'0')) AND 
		(nullif(@tempRecvudf10,'0') IS NULL OR d.recvudf10 = nullif(@tempRecvudf10,'0')) 
	
	------VMI : ConsignorID------
	sELECT @cVMIAVAQtyIN=isnull(sum(d.OriginQty),0)-isnull(sum(d.PickQty),0)-isnull(sum(d.HoldQty),0)-isnull(sum(d.QCQty),0)-isnull(sum(d.BookQty),0)+isnull(sum(d.AdjQty),0),
	       @cVMIQCQtyIN=isnull(sum(d.QCQty),0),
	       @cVMIHoldQtyIN=isnull(sum(d.HoldQty),0),
	       @cVMIPickQtyIN=isnull(sum(d.PickQty),0),
	       @cVMIBookQtyIN=isnull(sum(d.BookQty),0)
	       
	FROM tblItemDetail d
	WHERE d.ItemID=@ItemID AND	     
	      d.ConsignorID=@ConsignorID AND
	      d.CustomLotNo=@CustomLotNo AND
	      d.OwnerStatus='VMI' AND
		(nullif(@ctempRecvudf1,'0') IS NULL OR d.recvudf1 = nullif(@ctempRecvudf1,'0')) AND 
		(nullif(@ctempRecvudf2,'0') IS NULL OR d.recvudf2 = nullif(@ctempRecvudf2,'0'))  AND 
		(nullif(@ctempRecvudf3,'0') IS NULL OR d.recvudf3 = nullif(@ctempRecvudf3,'0')) AND 
		(nullif(@ctempRecvudf4,'0') IS NULL OR d.recvudf4 = nullif(@ctempRecvudf4,'0')) AND 
		(nullif(@ctempRecvudf5,'0') IS NULL OR d.recvudf5 = nullif(@ctempRecvudf5,'0')) AND 
		(nullif(@ctempRecvudf6,'0') IS NULL OR d.recvudf6 = nullif(@ctempRecvudf6,'0')) AND 
		(nullif(@ctempRecvudf7,'0') IS NULL OR d.recvudf7 = nullif(@ctempRecvudf7,'0')) AND 
		(nullif(@ctempRecvudf8,'0') IS NULL OR d.recvudf8 = nullif(@ctempRecvudf8,'0')) AND 
		(nullif(@ctempRecvudf9,'0') IS NULL OR d.recvudf9 = nullif(@ctempRecvudf9,'0')) AND 
		(nullif(@ctempRecvudf10,'0') IS NULL OR d.recvudf10 = nullif(@ctempRecvudf10,'0')) 
	

IF @recvOwn='OWN'
	BEGIN
	---updating the ownership 
	UPDATE tblINItemClientQty
	SET    AVAQTy=@AVAQtyIN,
	       QCQty= @QCQtyIN,
	       HoldQty=@HoldQtyIN,
	       PickQty=@PickQtyIN,
	       BookQty= @BookQtyIN
	WHERE ClientID=@ClientID AND ItemID=@ItemID AND 
	      CustomLotNo=@CustomLotNo AND StockID = @StockID AND	      
				 COALESCE(UDF1, '%') LIKE '%'+@tempRecvudf1+'%' AND
				 COALESCE(UDF2, '%') LIKE '%'+@tempRecvudf2+'%' AND
				 COALESCE(UDF3, '%') LIKE '%'+@tempRecvudf3+'%' AND
				 COALESCE(UDF4, '%') LIKE '%'+@tempRecvudf4+'%' AND
				 COALESCE(UDF5, '%') LIKE '%'+@tempRecvudf5+'%' AND
				 COALESCE(UDF6, '%') LIKE '%'+@tempRecvudf6+'%' AND
				 COALESCE(UDF7, '%') LIKE '%'+@tempRecvudf7+'%' AND
				 COALESCE(UDF8, '%') LIKE '%'+@tempRecvudf8+'%' AND
				 COALESCE(UDF9, '%') LIKE '%'+@tempRecvudf9+'%' AND
				 COALESCE(UDF10, '%') LIKE '%'+@tempRecvudf10+'%'
	END

ELSE IF @RecvOwn='VMI'
	BEGIN
	---Ownership---
	UPDATE tblINItemClientQty
	SET    VMIAVAQTy=@VMIAVAQtyIN,
	       VMIQCQty= @VMIQCQtyIN,
	       VMIHoldQty=@VMIHoldQtyIN,
	       VMIPickQty=@VMIPickQtyIN,
	       VMIBookQty= @VMIBookQtyIN
	WHERE ClientID=@ClientID AND ItemID=@ItemID AND 
	      CustomLotNo=@CustomLotNo AND StockID = @StockID AND	      
				 COALESCE(UDF1, '%') LIKE '%'+@tempRecvudf1+'%' AND
				 COALESCE(UDF2, '%') LIKE '%'+@tempRecvudf2+'%' AND
				 COALESCE(UDF3, '%') LIKE '%'+@tempRecvudf3+'%' AND
				 COALESCE(UDF4, '%') LIKE '%'+@tempRecvudf4+'%' AND
				 COALESCE(UDF5, '%') LIKE '%'+@tempRecvudf5+'%' AND
				 COALESCE(UDF6, '%') LIKE '%'+@tempRecvudf6+'%' AND
				 COALESCE(UDF7, '%') LIKE '%'+@tempRecvudf7+'%' AND
				 COALESCE(UDF8, '%') LIKE '%'+@tempRecvudf8+'%' AND
				 COALESCE(UDF9, '%') LIKE '%'+@tempRecvudf9+'%' AND
				 COALESCE(UDF10, '%') LIKE '%'+@tempRecvudf10+'%'
	---consignor---
	UPDATE tblINItemClientQty
	SET    VMIAVAQTy=@VMIAVAQtyIN,
	       VMIQCQty= @VMIQCQtyIN,
	       VMIHoldQty=@VMIHoldQtyIN,
	       VMIPickQty=@VMIPickQtyIN,
	       VMIBookQty= @VMIBookQtyIN
	WHERE ClientID=@ConsignorID AND ItemID=@ItemID AND 
	      CustomLotNo=@CustomLotNo AND StockID = @StockID AND	      
				 COALESCE(UDF1, '%') LIKE '%'+@ctempRecvudf1+'%' AND
				 COALESCE(UDF2, '%') LIKE '%'+@ctempRecvudf2+'%' AND
				 COALESCE(UDF3, '%') LIKE '%'+@ctempRecvudf3+'%' AND
				 COALESCE(UDF4, '%') LIKE '%'+@ctempRecvudf4+'%' AND
				 COALESCE(UDF5, '%') LIKE '%'+@ctempRecvudf5+'%' AND
				 COALESCE(UDF6, '%') LIKE '%'+@ctempRecvudf6+'%' AND
				 COALESCE(UDF7, '%') LIKE '%'+@ctempRecvudf7+'%' AND
				 COALESCE(UDF8, '%') LIKE '%'+@ctempRecvudf8+'%' AND
				 COALESCE(UDF9, '%') LIKE '%'+@ctempRecvudf9+'%' AND
				 COALESCE(UDF10, '%') LIKE '%'+@ctempRecvudf10+'%'
	END


---------------------------------------------------------------------------------------------------------------- updating tblItemCrossRef--------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Declare @AVAQtyCR float, @QCQtyCR float, @BookQtyCR float, @HoldQtyCR float, @PickQtyCR float
Declare @VMIAVAQtyCR float, @VMIQCQtyCR float, @VMIBookQtyCR float, @VMIHoldQtyCR float, @VMIPickQtyCR float
Declare @cVMIAVAQtyCR float, @cVMIQCQtyCR float, @cVMIBookQtyCR float, @cVMIHoldQtyCR float, @cVMIPickQtyCR float
	
	---OWN : ownership---
	sELECT @AVAQtyCR=isnull(sum(d.OriginQty),0)-isnull(sum(d.PickQty),0)-isnull(sum(d.HoldQty),0)-isnull(sum(d.QCQty),0)-isnull(sum(d.BookQty),0)+isnull(sum(d.AdjQty),0),
	       @QCQtyCR=isnull(sum(d.QCQty),0),
	       @HoldQtyCR=isnull(sum(d.HoldQty),0),
	       @PickQtyCR=isnull(sum(d.PickQty),0),
	       @BookQtyCR=isnull(sum(d.BookQty),0)
	       
	FROM tblItemDetail d
	WHERE d.ItemID=@ItemID AND
	      d.Ownership=@ClientID 

	------VMI : Ownership------
	sELECT @VMIAVAQtyCR=isnull(sum(d.OriginQty),0)-isnull(sum(d.PickQty),0)-isnull(sum(d.HoldQty),0)-isnull(sum(d.QCQty),0)-isnull(sum(d.BookQty),0)+isnull(sum(d.AdjQty),0),
	       @VMIQCQtyCR=isnull(sum(d.QCQty),0),
	       @VMIHoldQtyCR=isnull(sum(d.HoldQty),0),
	       @VMIPickQtyCR=isnull(sum(d.PickQty),0),
	       @VMIBookQtyCR=isnull(sum(d.BookQty),0)
	       
	FROM tblItemDetail d
	WHERE d.ItemID=@ItemID AND	     
	      d.Ownership=@ClientID AND
	      d.OwnerStatus='VMI' 

	------VMI : ConsignorID------
	sELECT @cVMIAVAQtyCR=isnull(sum(d.OriginQty),0)-isnull(sum(d.PickQty),0)-isnull(sum(d.HoldQty),0)-isnull(sum(d.QCQty),0)-isnull(sum(d.BookQty),0)+isnull(sum(d.AdjQty),0),
	       @cVMIQCQtyCR=isnull(sum(d.QCQty),0),
	       @cVMIHoldQtyCR=isnull(sum(d.HoldQty),0),
	       @cVMIPickQtyCR=isnull(sum(d.PickQty),0),
	       @cVMIBookQtyCR=isnull(sum(d.BookQty),0)
	       
	FROM tblItemDetail d
	WHERE d.ItemID=@ItemID AND	     
	      d.ConsignorID=@ConsignorID AND
	      d.OwnerStatus='VMI'

IF @recvOwn='OWN'
	BEGIN
	UPDATE tblItemcrossRef
	SET    AVAQTy=@AVAQtyCR,
	       QCQty= @QCQtyCR,
	       PNDQty=@HoldQtyCR + @PickQtyCR
	WHERE ClientID=@ClientID AND ItemID=@ItemID AND StockID = @StockID 
	END

ELSE IF @recvOwn='VMI'
	BEGIN
	--ownership--
	UPDATE tblItemcrossRef
	SET    VMIAVAQTy=@VMIAVAQtyCR,
	       VMIQCQty= @VMIQCQtyCR,
	       VMIPNDQty=@VMIHoldQtyCR + @VMIPickQtyCR
	WHERE ClientID=@ClientID AND ItemID=@ItemID AND StockID = @StockID 	
	
	--consignor--
	UPDATE tblItemcrossRef
	SET    VMIAVAQTy=@cVMIAVAQtyCR,
	       VMIQCQty= @cVMIQCQtyCR,
	       VMIPNDQty=@cVMIHoldQtyCR + @cVMIPickQtyCR
	WHERE ClientID=@ConsignorID AND ItemID=@ItemID AND StockID = @StockID	
	END

END
GO






~~~Focus on problem, not solution~~~
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-03-15 : 03:43:54
Try this and see if there is a speed difference
CREATE PROCEDURE SP_StkAdj_tbl_alignmt
(	
	@ItemID VARCHAR(50),
	@ClientID VARCHAR(20),
	@CustomLotNo VARCHAR(20),
	@UDF1 VARCHAR(50),
	@UDF2 VARCHAR(50),
	@UDF3 VARCHAR(50),
	@UDF4 VARCHAR(50),
	@UDF5 VARCHAR(50),
	@UDF6 VARCHAR(50),
	@UDF7 VARCHAR(50),
	@UDF8 VARCHAR(50),
	@UDF9 VARCHAR(50),
	@UDF10 VARCHAR(50),
	@StockID VARCHAR(50),
	@RecvOwn VARCHAR(3),
	@ConsignorID VARCHAR(20)	
)
AS

DECLARE	@PickCustomLot VARCHAR(1),
	@PickExpiryDate VARCHAR(1),
	@Pick1 VARCHAR(1),
	@Pick2 VARCHAR(1),
	@Pick3 VARCHAR(1),
	@Pick4 VARCHAR(1),
	@Pick5 VARCHAR(1),
	@Pick6 VARCHAR(1),
	@Pick7 VARCHAR(1),
	@Pick8 VARCHAR(1),
	@Pick9 VARCHAR(1),
	@Pick10 VARCHAR(1),
	@cPickCustomLot VARCHAR(1),
	@cPickExpiryDate VARCHAR(1),
	@cPick1 VARCHAR(1),
	@cPick2 VARCHAR(1),
	@cPick3 VARCHAR(1),
	@cPick4 VARCHAR(1),
	@cPick5 VARCHAR(1),
	@cPick6 VARCHAR(1),
	@cPick7 VARCHAR(1),
	@cPick8 VARCHAR(1),
	@cPick9 VARCHAR(1),
	@cPick10 VARCHAR(1)

SELECT		@PickCustomLot = MAX(CASE WHEN p.PickType = 'Custom Lot No.' AND r.ClientID = @ClientID THEN 'Y' ELSE 'N' END),
		@PickExpiryDate = MAX(CASE WHEN p.PickType = 'Expiry Date' AND r.ClientID = @ClientID THEN 'Y' ELSE 'N' END),
		@cPickCustomLot = MAX(CASE WHEN p.PickType = 'Custom Lot No.' AND r.ClientID = @ConsignorID THEN 'Y' ELSE 'N' END),
		@cPickExpiryDate = MAX(CASE WHEN p.PickType = 'Expiry Date' AND r.ClientID = @ConsignorID THEN 'Y' ELSE 'N' END)
FROM		tblItemPickFormat AS p
INNER JOIN	tblItemCrossRef AS r ON r.ItemClientRef = p.ItemClientRef
WHERE		r.ItemID = @ItemID

SELECT		@Pick1 = MAX(CASE WHEN UDF = 1 AND ClientID = @ClientID THEN 'Y' ELSE 'N' END),
		@Pick2 = MAX(CASE WHEN UDF = 2 AND ClientID = @ClientID THEN 'Y' ELSE 'N' END),
		@Pick3 = MAX(CASE WHEN UDF = 3 AND ClientID = @ClientID THEN 'Y' ELSE 'N' END),
		@Pick4 = MAX(CASE WHEN UDF = 4 AND ClientID = @ClientID THEN 'Y' ELSE 'N' END),
		@Pick5 = MAX(CASE WHEN UDF = 5 AND ClientID = @ClientID THEN 'Y' ELSE 'N' END),
		@Pick6 = MAX(CASE WHEN UDF = 6 AND ClientID = @ClientID THEN 'Y' ELSE 'N' END),
		@Pick7 = MAX(CASE WHEN UDF = 7 AND ClientID = @ClientID THEN 'Y' ELSE 'N' END),
		@Pick8 = MAX(CASE WHEN UDF = 8 AND ClientID = @ClientID THEN 'Y' ELSE 'N' END),
		@Pick9 = MAX(CASE WHEN UDF = 9 AND ClientID = @ClientID THEN 'Y' ELSE 'N' END),
		@Pick10 = MAX(CASE WHEN UDF = 10 AND ClientID = @ClientID THEN 'Y' ELSE 'N' END),
		@cPick1 = MAX(CASE WHEN UDF = 1 AND ClientID = @ConsignorID THEN 'Y' ELSE 'N' END),
		@cPick2 = MAX(CASE WHEN UDF = 2 AND ClientID = @ConsignorID THEN 'Y' ELSE 'N' END),
		@cPick3 = MAX(CASE WHEN UDF = 3 AND ClientID = @ConsignorID THEN 'Y' ELSE 'N' END),
		@cPick4 = MAX(CASE WHEN UDF = 4 AND ClientID = @ConsignorID THEN 'Y' ELSE 'N' END),
		@cPick5 = MAX(CASE WHEN UDF = 5 AND ClientID = @ConsignorID THEN 'Y' ELSE 'N' END),
		@cPick6 = MAX(CASE WHEN UDF = 6 AND ClientID = @ConsignorID THEN 'Y' ELSE 'N' END),
		@cPick7 = MAX(CASE WHEN UDF = 7 AND ClientID = @ConsignorID THEN 'Y' ELSE 'N' END),
		@cPick8 = MAX(CASE WHEN UDF = 8 AND ClientID = @ConsignorID THEN 'Y' ELSE 'N' END),
		@cPick9 = MAX(CASE WHEN UDF = 9 AND ClientID = @ConsignorID THEN 'Y' ELSE 'N' END),
		@cPick10 = MAX(CASE WHEN UDF = 10 AND ClientID = @ConsignorID THEN 'Y' ELSE 'N' END)
FROM		tblItemPickFormat AS p
INNER JOIN	(
			SELECT	1 AS UDF, ClientUDF1 AS PickType, ClientID FROM tblClient WHERE ClientID IN (@ClientID, @ConsignorID) UNION ALL
			SELECT	2, ClientUDF2, ClientID FROM tblClient WHERE ClientID IN (@ClientID, @ConsignorID) UNION ALL
			SELECT	3, ClientUDF3, ClientID FROM tblClient WHERE ClientID IN (@ClientID, @ConsignorID) UNION ALL
			SELECT	4, ClientUDF4, ClientID FROM tblClient WHERE ClientID IN (@ClientID, @ConsignorID) UNION ALL
			SELECT	5, ClientUDF5, ClientID FROM tblClient WHERE ClientID IN (@ClientID, @ConsignorID) UNION ALL
			SELECT	6, ClientUDF6, ClientID FROM tblClient WHERE ClientID IN (@ClientID, @ConsignorID) UNION ALL
			SELECT	7, ClientUDF7, ClientID FROM tblClient WHERE ClientID IN (@ClientID, @ConsignorID) UNION ALL
			SELECT	8, ClientUDF8, ClientID FROM tblClient WHERE ClientID IN (@ClientID, @ConsignorID) UNION ALL
			SELECT	9, ClientUDF9, ClientID FROM tblClient WHERE ClientID IN (@ClientID, @ConsignorID) UNION ALL
			SELECT	10, ClientUDF10, ClientID FROM tblClient WHERE ClientID IN (@ClientID, @ConsignorID)
		) AS c ON c.PickType = p.PickType 
INNER JOIN	tblItemCrossRef AS r ON r.ClientID = c.ClientID AND r.ItemClientRef = p.ItemClientRef
WHERE		r.ItemID = @ItemID

DECLARE	@TempRecvUDF1 VARCHAR(50),
	@TempRecvUDF2 VARCHAR(50),
	@TempRecvUDF3 VARCHAR(50),
	@TempRecvUDF4 VARCHAR(50),
	@TempRecvUDF5 VARCHAR(50),
	@TempRecvUDF6 VARCHAR(50),
	@TempRecvUDF7 VARCHAR(50),
	@TempRecvUDF8 VARCHAR(50),
	@TempRecvUDF9 VARCHAR(50),
	@TempRecvUDF10 VARCHAR(50),
	@cTempRecvUDF1 VARCHAR(50),
	@cTempRecvUDF2 VARCHAR(50),
	@cTempRecvUDF3 VARCHAR(50),
	@cTempRecvUDF4 VARCHAR(50),
	@cTempRecvUDF5 VARCHAR(50),
	@cTempRecvUDF6 VARCHAR(50),
	@cTempRecvUDF7 VARCHAR(50),
	@cTempRecvUDF8 VARCHAR(50),
	@cTempRecvUDF9 VARCHAR(50),
	@cTempRecvUDF10 VARCHAR(50)

SELECT	@TempRecvUDF1 = CASE WHEN @Pick1 = 'Y' THEN ISNULL(@UDF1, '0') ELSE '0' END,
	@TempRecvUDF2 = CASE WHEN @Pick2 = 'Y' THEN ISNULL(@UDF2, '0') ELSE '0' END,
	@TempRecvUDF3 = CASE WHEN @Pick3 = 'Y' THEN ISNULL(@UDF3, '0') ELSE '0' END,
	@TempRecvUDF4 = CASE WHEN @Pick4 = 'Y' THEN ISNULL(@UDF4, '0') ELSE '0' END,
	@TempRecvUDF5 = CASE WHEN @Pick5 = 'Y' THEN ISNULL(@UDF5, '0') ELSE '0' END,
	@TempRecvUDF6 = CASE WHEN @Pick6 = 'Y' THEN ISNULL(@UDF6, '0') ELSE '0' END,
	@TempRecvUDF7 = CASE WHEN @Pick7 = 'Y' THEN ISNULL(@UDF7, '0') ELSE '0' END,
	@TempRecvUDF8 = CASE WHEN @Pick8 = 'Y' THEN ISNULL(@UDF8, '0') ELSE '0' END,
	@TempRecvUDF9 = CASE WHEN @Pick9 = 'Y' THEN ISNULL(@UDF9, '0') ELSE '0' END,
	@TempRecvUDF10 = CASE WHEN @Pick10 = 'Y' THEN ISNULL(@UDF10, '0') ELSE '0' END,
	@cTempRecvUDF1 = CASE WHEN @cPick1 = 'Y' THEN ISNULL(@UDF1, '0') ELSE '0' END,
	@cTempRecvUDF2 = CASE WHEN @cPick2 = 'Y' THEN ISNULL(@UDF2, '0') ELSE '0' END,
	@cTempRecvUDF3 = CASE WHEN @cPick3 = 'Y' THEN ISNULL(@UDF3, '0') ELSE '0' END,
	@cTempRecvUDF4 = CASE WHEN @cPick4 = 'Y' THEN ISNULL(@UDF4, '0') ELSE '0' END,
	@cTempRecvUDF5 = CASE WHEN @cPick5 = 'Y' THEN ISNULL(@UDF5, '0') ELSE '0' END,
	@cTempRecvUDF6 = CASE WHEN @cPick6 = 'Y' THEN ISNULL(@UDF6, '0') ELSE '0' END,
	@cTempRecvUDF7 = CASE WHEN @cPick7 = 'Y' THEN ISNULL(@UDF7, '0') ELSE '0' END,
	@cTempRecvUDF8 = CASE WHEN @cPick8 = 'Y' THEN ISNULL(@UDF8, '0') ELSE '0' END,
	@cTempRecvUDF9 = CASE WHEN @cPick9 = 'Y' THEN ISNULL(@UDF9, '0') ELSE '0' END,
	@cTempRecvUDF10 = CASE WHEN @cPick10 = 'Y' THEN ISNULL(@UDF10, '0') ELSE '0' END

DECLARE	@AVAQtyIN FLOAT,
	@QCQtyIN FLOAT,
	@BookQtyIN FLOAT,
	@HoldQtyIN FLOAT,
	@PickQtyIN FLOAT,
	@VMIAVAQtyIN FLOAT,
	@VMIQCQtyIN FLOAT,
	@VMIBookQtyIN FLOAT,
	@VMIHoldQtyIN FLOAT,
	@VMIPickQtyIN FLOAT,
	@cVMIAVAQtyIN FLOAT
	@cVMIQCQtyIN FLOAT,
	@cVMIBookQtyIN FLOAT,
	@cVMIHoldQtyIN FLOAT,
	@cVMIPickQtyIN FLOAT

SELECT	@AVAQtyIN = ISNULL(SUM(OriginQty), 0) -
			ISNULL(SUM(PickQty), 0) -
			ISNULL(SUM(HoldQty), 0) -
			ISNULL(SUM(QCQty), 0) -
			ISNULL(SUM(BookQty), 0) +
			ISNULL(SUM(AdjQty), 0),
	@QCQtyIN = ISNULL(SUM(d.QCQty), 0),
	@HoldQtyIN = ISNULL(SUM(d.HoldQty), 0),
	@PickQtyIN = ISNULL(SUM(d.PickQty), 0),
	@BookQtyIN = ISNULL(SUM(d.BookQty), 0),
	@VMIAVAQtyIN = SUM(CASE WHEN OwnerStatus = 'VMI' THEN OriginQty ELSE 0 END) -
			SUM(CASE WHEN OwnerStatus = 'VMI' THEN PickQty ELSE 0 END) -
			SUM(CASE WHEN OwnerStatus = 'VMI' THEN HoldQty ELSE 0 END) -
			SUM(CASE WHEN OwnerStatus = 'VMI' THEN QCQty ELSE 0 END) -
			SUM(CASE WHEN OwnerStatus = 'VMI' THEN BookQty ELSE 0 END) +
			SUM(CASE WHEN OwnerStatus = 'VMI' THEN AdjQty ELSE 0 END),
	@VMIQCQtyIN = SUM(CASE WHEN OwnerStatus = 'VMI' THEN QCQty ELSE 0 END),
	@VMIHoldQtyIN = SUM(CASE WHEN OwnerStatus = 'VMI' THEN HoldQty ELSE 0 END),
	@VMIPickQtyIN = SUM(CASE WHEN OwnerStatus = 'VMI' THEN PickQty ELSE 0 END),
	@VMIBookQtyIN = SUM(CASE WHEN OwnerStatus = 'VMI' THEN BookQty ELSE 0 END),
	@cVMIAVAQtyIN =	SUM(CASE WHEN OwnerStatus = 'VMI' THEN OriginQty ELSE 0 END) -
			SUM(CASE WHEN OwnerStatus = 'VMI' THEN PickQty ELSE 0 END) -
			SUM(CASE WHEN OwnerStatus = 'VMI' THEN HoldQty ELSE 0 END) -
			SUM(CASE WHEN OwnerStatus = 'VMI' THEN QCQty ELSE 0 END) -
			SUM(CASE WHEN OwnerStatus = 'VMI' THEN BookQty ELSE 0 END) +
			SUM(CASE WHEN OwnerStatus = 'VMI' THEN AdjQty ELSE 0 END),
	@cVMIQCQtyIN = SUM(CASE WHEN OwnerStatus = 'VMI' THEN QCQty ELSE 0 END),
	@cVMIHoldQtyIN = SUM(CASE WHEN OwnerStatus = 'VMI' THEN HoldQty ELSE 0 END),
	@cVMIPickQtyIN = SUM(CASE WHEN OwnerStatus = 'VMI' THEN PickQty ELSE 0 END),
	@cVMIBookQtyIN = SUM(CASE WHEN OwnerStatus = 'VMI' THEN BookQty ELSE 0 END)
FROM	tblItemDetail
WHERE	ItemID = @ItemID
	AND Ownership = @ClientID
	AND CustomLotNo = @CustomLotNo
	AND @TempRecvUDF1 IN ('0', RecvUDF1)
	AND @TempRecvUDF2 IN ('0', RecvUDF2)
	AND @TempRecvUDF3 IN ('0', RecvUDF3)
	AND @TempRecvUDF4 IN ('0', RecvUDF4)
	AND @TempRecvUDF5 IN ('0', RecvUDF5)
	AND @TempRecvUDF6 IN ('0', RecvUDF6)
	AND @TempRecvUDF7 IN ('0', RecvUDF7)
	AND @TempRecvUDF8 IN ('0', RecvUDF8)
	AND @TempRecvUDF9 IN ('0', RecvUDF9)
	AND @TempRecvUDF10 IN ('0', RecvUDF10)

IF @RecvOwn = 'OWN'
	UPDATE	tblINItemClientQty
	SET	AVAQTy =@AVAQtyIN,
		QCQty = @QCQtyIN,
		HoldQty =@HoldQtyIN,
		PickQty =@PickQtyIN,
		BookQty = @BookQtyIN
	WHERE	ClientID = @ClientID
		AND ItemID = 
		AND CustomLotNo = @CustomLotNo
		AND StockID = @StockID
		AND COALESCE(UDF1, '%') LIKE '%' + @TempRecvUDF1 + '%'
		AND COALESCE(UDF2, '%') LIKE '%' + @TempRecvUDF2 + '%'
		AND COALESCE(UDF3, '%') LIKE '%' + @TempRecvUDF3 + '%'
		AND COALESCE(UDF4, '%') LIKE '%' + @TempRecvUDF4 + '%'
		AND COALESCE(UDF5, '%') LIKE '%' + @TempRecvUDF5 + '%'
		AND COALESCE(UDF6, '%') LIKE '%' + @TempRecvUDF6 + '%'
		AND COALESCE(UDF7, '%') LIKE '%' + @TempRecvUDF7 + '%'
		AND COALESCE(UDF8, '%') LIKE '%' + @TempRecvUDF8 + '%'
		AND COALESCE(UDF9, '%') LIKE '%' + @TempRecvUDF9 + '%'
		AND COALESCE(UDF10, '%') LIKE '%' + @TempRecvUDF10 + '%'

IF @RecvOwn = 'VMI'
	BEGIN
		UPDATE	tblINItemClientQty
		SET	VMIAVAQTy = @VMIAVAQtyIN,
			VMIQCQty = @VMIQCQtyIN,
			VMIHoldQty = @VMIHoldQtyIN,
			VMIPickQty = @VMIPickQtyIN,
			VMIBookQty = @VMIBookQtyIN
		WHERE	ClientID = @ClientID
			AND ItemID = @ItemID
			AND CustomLotNo = @CustomLotNo
			AND StockID = @StockID
			AND COALESCE(UDF1, '%') LIKE '%' + @TempRecvUDF1 + '%'
			AND COALESCE(UDF2, '%') LIKE '%' + @TempRecvUDF2 + '%'
			AND COALESCE(UDF3, '%') LIKE '%' + @TempRecvUDF3 + '%'
			AND COALESCE(UDF4, '%') LIKE '%' + @TempRecvUDF4 + '%'
			AND COALESCE(UDF5, '%') LIKE '%' + @TempRecvUDF5 + '%'
			AND COALESCE(UDF6, '%') LIKE '%' + @TempRecvUDF6 + '%'
			AND COALESCE(UDF7, '%') LIKE '%' + @TempRecvUDF7 + '%'
			AND COALESCE(UDF8, '%') LIKE '%' + @TempRecvUDF8 + '%'
			AND COALESCE(UDF9, '%') LIKE '%' + @TempRecvUDF9 + '%'
			AND COALESCE(UDF10, '%') LIKE '%' + @TempRecvUDF10 + '%'

		UPDATE	tblINItemClientQty
		SET	VMIAVAQTy = @VMIAVAQtyIN,
			VMIQCQty = @VMIQCQtyIN,
			VMIHoldQty = @VMIHoldQtyIN,
			VMIPickQty = @VMIPickQtyIN,
			VMIBookQty = @VMIBookQtyIN
		WHERE	ClientID = @ConsignorID
			AND ItemID = @ItemID
			AND CustomLotNo = @CustomLotNo
			AND StockID = @StockID
			AND COALESCE(UDF1, '%') LIKE '%' + @cTempRecvUDF1 + '%'
			AND COALESCE(UDF2, '%') LIKE '%' + @cTempRecvUDF2 + '%'
			AND COALESCE(UDF3, '%') LIKE '%' + @cTempRecvUDF3 + '%'
			AND COALESCE(UDF4, '%') LIKE '%' + @cTempRecvUDF4 + '%'
			AND COALESCE(UDF5, '%') LIKE '%' + @cTempRecvUDF5 + '%'
			AND COALESCE(UDF6, '%') LIKE '%' + @cTempRecvUDF6 + '%'
			AND COALESCE(UDF7, '%') LIKE '%' + @cTempRecvUDF7 + '%'
			AND COALESCE(UDF8, '%') LIKE '%' + @cTempRecvUDF8 + '%'
			AND COALESCE(UDF9, '%') LIKE '%' + @cTempRecvUDF9 + '%'
			AND COALESCE(UDF10, '%') LIKE '%' + @cTempRecvUDF10 + '%'
	END

DECLARE	@AVAQtyCR FLOAT,
	@QCQtyCR FLOAT,
	@BookQtyCR FLOAT,
	@HoldQtyCR FLOAT,
	@PickQtyCR FLOAT,
	@VMIAVAQtyCR FLOAT,
	@VMIQCQtyCR FLOAT,
	@VMIBookQtyCR FLOAT,
	@VMIHoldQtyCR FLOAT,
	@VMIPickQtyCR FLOAT,
	@cVMIAVAQtyCR FLOAT,
	@cVMIQCQtyCR FLOAT,
	@cVMIBookQtyCR FLOAT
	@cVMIHoldQtyCR FLOAT,
	@cVMIPickQtyCR FLOAT
	
SELECT	@AVAQtyCR = ISNULL(SUM(OriginQty), 0) -
			ISNULL(SUM(PickQty), 0) -
			ISNULL(SUM(HoldQty), 0) -
			ISNULL(SUM(QCQty), 0) -
			ISNULL(SUM(BookQty), 0) +
			ISNULL(SUM(AdjQty), 0),
	@QCQtyCR = ISNULL(SUM(QCQty), 0),
	@HoldQtyCR = ISNULL(SUM(HoldQty), 0),
	@PickQtyCR = ISNULL(SUM(PickQty), 0),
	@BookQtyCR = ISNULL(SUM(BookQty), 0),
	@VMIAVAQtyCR = SUM(CASE WHEN OwnerStatus = 'VMI' THEN OriginQty ELSE 0 END) -
			SUM(CASE WHEN OwnerStatus = 'VMI' THEN PickQty ELSE 0 END) -
			SUM(CASE WHEN OwnerStatus = 'VMI' THEN HoldQty ELSE 0 END) -
			SUM(CASE WHEN OwnerStatus = 'VMI' THEN QCQty ELSE 0 END) -
			SUM(CASE WHEN OwnerStatus = 'VMI' THEN BookQty ELSE 0 END) +
			SUM(CASE WHEN OwnerStatus = 'VMI' THEN AdjQty ELSE 0 END),
	@VMIQCQtyCR = SUM(CASE WHEN OwnerStatus = 'VMI' THEN QCQty ELSE 0 END),
	@VMIHoldQtyCR = SUM(CASE WHEN OwnerStatus = 'VMI' THEN HoldQty ELSE 0 END),
	@VMIPickQtyCR = SUM(CASE WHEN OwnerStatus = 'VMI' THEN PickQty ELSE 0 END),
	@VMIBookQtyCR = SUM(CASE WHEN OwnerStatus = 'VMI' THEN BookQty ELSE 0 END),
	@cVMIAVAQtyCR =	SUM(CASE WHEN OwnerStatus = 'VMI' THEN OriginQty ELSE 0 END) -
			SUM(CASE WHEN OwnerStatus = 'VMI' THEN PickQty ELSE 0 END) -
			SUM(CASE WHEN OwnerStatus = 'VMI' THEN HoldQty ELSE 0 END) -
			SUM(CASE WHEN OwnerStatus = 'VMI' THEN QCQty ELSE 0 END) -
			SUM(CASE WHEN OwnerStatus = 'VMI' THEN BookQty ELSE 0 END) +
			SUM(CASE WHEN OwnerStatus = 'VMI' THEN AdjQty ELSE 0 END),
	@cVMIQCQtyCR = SUM(CASE WHEN OwnerStatus = 'VMI' THEN QCQty ELSE 0 END),
	@cVMIHoldQtyCR = SUM(CASE WHEN OwnerStatus = 'VMI' THEN HoldQty ELSE 0 END),
	@cVMIPickQtyCR = SUM(CASE WHEN OwnerStatus = 'VMI' THEN PickQty ELSE 0 END),
	@cVMIBookQtyCR = SUM(CASE WHEN OwnerStatus = 'VMI' THEN BookQty ELSE 0 END)
FROM	tblItemDetail
WHERE	ItemID = @ItemID
	AND Ownership = @ClientID 

IF @RecvOwn = 'OWN'
	UPDATE	tblItemcrossRef
	SET	AVAQTy = @AVAQtyCR,
		QCQty = @QCQtyCR,
		PNDQty = @HoldQtyCR + @PickQtyCR
	WHERE	ClientID = @ClientID
		AND ItemID = @ItemID
		AND StockID = @StockID 

IF @recvOwn='VMI'
	BEGIN
		UPDATE	tblItemcrossRef
		SET	VMIAVAQTy = @VMIAVAQtyCR,
			VMIQCQty = @VMIQCQtyCR,
			VMIPNDQty = @VMIHoldQtyCR + @VMIPickQtyCR
		WHERE	ClientID = @ClientID
			AND ItemID = @ItemID
			AND StockID = @StockID 

		UPDATE	tblItemcrossRef
		SET	VMIAVAQTy = @cVMIAVAQtyCR,
			VMIQCQty = @cVMIQCQtyCR,
			VMIPNDQty = @cVMIHoldQtyCR + @cVMIPickQtyCR
		WHERE	ClientID = @ConsignorID
			AND ItemID = @ItemID
			AND StockID = @StockID
	END

Peter Larsson
Helsingborg, Sweden
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site