DeveloperBarn

SELECT  TP.PartnerCode AS 'Component Number',
		M.Name AS 'Component Name',
		TC.RelatedReference AS 'Delivery Note Reference',
		TC.RelatedReferenceDate AS 'Delivery Date',
		LVLProductCode.LookupValue AS 'ProductCode', 
	    LVLProductDescr.LookupValue AS 'Description', 
	    LVLPackSize.LookupValue AS 'Pack Size', 
		Case When dbo.Get_GLCategoryCode(@vnMemberID,TC.RecipientID,LVLProductCode.LookupValue) IS NOT NULL
			 THEN dbo.Get_GLCategoryCode(@vnMemberID,TC.RecipientID,LVLProductCode.LookupValue) 
		Else
			GM.GLCode
		End AS 'Default GL Category Code',
	    --GM2.GLCodeDescription AS 'Default GL Category Description'
		--GM.GLCodeDescription AS 'Default GL Category Description'
		--TPExInfo.ValueChar AS 'Default GL Category Description',
		GM.GLCode AS 'Selected GL Category Code',
		GM.GLCodeDescription AS 'Selected GL Category Description'
FROM ThreadContent TC
INNER JOIN bp_directory..allchildren(@vnMemberID) AS AC ON AC.MemberID=TC.SenderID
INNER JOIN bp_directory..member AS M on M.MemberID = TC.SenderID 
INNER JOIN TradingPartner AS TP ON TP.MemberID=TC.SenderID AND TP.ProxyLOOKup = 1                
-- Get the product code
INNER JOIN LookupValueLine LVLProductCode ON LVLProductCode.MessageID = TC.MessageID
	                                       AND LVLProductCode.LookupID = @LOOKUPID_PRODUCTCODE                                
-- Get the description of the product
INNER JOIN LookupValueLine LVLProductDescr ON LVLProductDescr.MessageID = LVLProductCode.MessageID
	                                       AND LVLProductDescr.LookupID = @LOOKUPID_PRODUCTDESCRIPTION
	                                       AND LVLProductDescr.[LineNo] = LVLProductCode.[LineNo]	
-- Get the pack size of the product
INNER JOIN LookupValueLine LVLPackSize  ON LVLPackSize.MessageID = LVLProductCode.MessageID
	                                    AND LVLPackSize.LookupID = @LOOKUPID_PACKSIZE
	                                    AND LVLPackSize.[LineNo] = LVLProductCode.[LineNo]
-- Get the selected GL Category
INNER JOIN LookupValueLine LVLSelGLCat  ON LVLSelGLCat.MessageID = TC.MessageID
	                                    AND LVLSelGLCat.LookupID = 298553

INNER JOIN BP_Catalogue..GLCategory_Master GM ON GM.MemberID = @vnMemberID and GM.GLCode=LVLSelGLCat.LookupValue 

INNER JOIN TradingPartner_ExtraInfo TPExInfo  ON TPExInfo.MemberID = @vnMemberID
	                                          AND TPExInfo.PartnerID = TC.RecipientID
	                                          AND TPExInfo.ExtraInfoID = 46
WHERE TC.RelatedReferenceDate BETWEEN @vdtDeliveryStartDate AND @vdtDeliveryEndDate
	  AND TC.RelatedReferenceTypeID = 2
ORDER BY TP.PartnerCode,TC.RelatedReferenceDate,LVLProductCode.LookupValue

CREATE FUNCTION [dbo].[Get_GLCategoryCode] 
(
	@vnMemberID int,
	@vnSupplierID int,
	@vnProductCode int
)
RETURNS int
AS
BEGIN
	DECLARE @vnGLCategoryCode int

	Select @vnGLCategoryCode=GLCategoryCOde from BP_Catalogue..GLCategory_Product where
	MemberId=@vnMemberID and SupplierID=@vnSupplierID and ProductCode=@vnProductCode
	
	RETURN @vnGLCategoryCode
END