
/*************************************************
Explanation of CFAMX-2634, second error:
	
	File Contains the following row:
		184774|032745|032745|W VA TAX STAMPS 5 GAL||0|2018/06/11 00:00:00|4712/12/31 00:00:00|N|N|N||Y|
	Error when importing:
		Cannot insert duplicate key row in object 'dbo.tbVendorItem' with unique index 'VendorIDVendorCode'. The duplicate key value is (11, 032745).

**************************************************/

-- The first three values from the import file
DECLARE @VendorCodeFromFile varchar(50) = '184774'
DECLARE @ItemCodeFromFile varchar(50) = '032745'
DECLARE @VendorItemCodeFromFile varchar(50) = '032745'

-- 1) Find the Item Id in our database by Item Code from the file
	DECLARE @ItemIdByItemCode BIGINT = (select itemid from tbItem where ItemCode like @ItemCodeFromFile)

-- 2) Find the Vendor Id in our database by Vendor Code from the file
	DECLARE @VendorId BIGINT = (select VendorId from tbVendor where theircustomercode like @VendorCodeFromFile)
	
-- 3) Then, look for a VendorItem with the Vendor and Item Ids that we just found, along with the VendorItemCode from the file
	SELECT * from tbVendorItem where VendorID = @VendorId and VendorCode = @VendorItemCodeFromFile and ItemId = @ItemIdByItemCode

-- 4) We don't find it (the Step 3 query returns nothing), so we know we're going to do an insert, NOT an update, into tbVendorItem

-- 5) But, we have a conflict because there's already a VendorItem with that VendorId and VendorCode, so the Insert throws an error
	Select VendorID, VendorCode, ItemID, VendorDescription from tbVendorItem where vendorid = @VendorId and vendorcode like @VendorItemCodeFromFile

-- 6) Wait, what is this second item that's already in tbVendorItem?
	DECLARE @ItemIdByVendorCode BIGINT = (select ItemID from tbVendorItem where vendorid = @VendorId and vendorcode like @VendorItemCodeFromFile)
	SELECT * FROM tbItem where ItemID = @ItemIdByVendorCode	

-- 7) And what is the first item we found, using the item code from the file?
	SELECT * FROM tbItem where ItemID = @ItemIdByItemCode
	
-- So we have two pre-existing items. One matches the Item Code from the file. The other matches the VendorCode and VendorItemCode from the file.
-- What should happen in this case? It's like the import file is giving us contradictory information.
