-- =============================================
-- Author:		Syed Mohsin
-- Create date: <8/11/2016>
-- Description:	<Extracts data for ApplicationLookup Entity>
-- Sample Call: EXEC [dbo].[ApplicationLookupExtract] 8 , 180

-- Updated By: Syed Mohsin
-- Updated Date: <8/23/2016>
-- Comments: Join conditions updated.

-- Updated By: Syed Mohsin
-- Updated Date: <9/7/2016>
-- Comments: Removed target and source store check from lookups.
-- =============================================

--CREATE Proc [dbo].[ApplicationLookupExtract]
--@StoreID int, @TargetStoreId int
--as
--BEGIN
--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
--select * from (

--  SELECT
--	 c.sto_pk sto,
--	  lt2.LookupTypeId,
--	  CAST(RTRIM(LTRIM(c.lc_Descript)) as nvarchar(50)) lc_Descript,
--	  c.lc_pk  as SourceId,
--	  1 as IsNew ,
--	  GetDate() as CreatedDate,
--	  GetDate() as UpdatedDate,
--	1 as CreatedBy, --Drop Column in package
--	u.UserId as UpdatedBy,
--	  0 as IsDeleted,
--	  ROW_NUMBER() OVER(PARTITION BY lt2.LookupTypeId,lc_Descript ORDER BY c.lc_pk DESC) AS RowNumber

--  FROM EXT_Lookup_B b INNER JOIN CEXT_LookupType lt ON lt.LookupTypeName = CASE [lb_type]
--		WHEN 'C' THEN 'Customer Characteristics'
--		WHEN 'G' THEN 'Gun Info Codes'
--		WHEN 'J' THEN 'Jewelry Info'
--		WHEN 'K' THEN 'Item'
--		WHEN 'O' THEN 'Item'
--		WHEN 'S' THEN 'Stone Info Codes'
--		WHEN 'T' THEN 'Tender Type'
--		END
--	INNER JOIN CEXT_LookupType lt2 ON lt2.LookupTypeName = CASE WHEN (b.lb_descript = 'SIZE') THEN 'Size / Length' ELSE b.lb_descript END  AND lt2.ParentLookupTypeId = lt.LookupTypeId --Updated condition SIZE -> Size / Length
--	INNER JOIN EXT_Lookup_C c ON b.lb_pk = c.LB_FK
--	INNER JOIN EXT_Lookup_N n ON n.lc_FK = c.lc_pk and n.sto_pk = @StoreID
--	LEFT JOIN CEXT_User u ON c.LastUpdatedUSR_ID = u.SourceId --Join condition updated from Inner to left
--	LEFT JOIN CEXT_LookupValue lv ON lt2.LookupTypeId = lv.LookupTypeId AND lv.Value = c.lc_Descript

--	LEFT JOIN EXT_cust cu on cu.CUS_EYESFK = c.lc_pk OR cu.CUS_HAIRFK = c.lc_pk  OR cu.CUS_RACEFK = c.lc_pk
--	LEFT JOIN EXT_items i on i.Color = c.lc_pk AND i.Sto_Pk = @StoreID
--	LEFT JOIN EXT_Detail_G g on g.Items_FK = i.ITEMS_PK and (g.Action_FK = c.lc_pk OR g.Barrel_FK = c.lc_pk OR g.Caliber_FK = c.lc_pk OR g.Finish_FK = c.lc_pk OR g.ImporterFK = c.lc_pk)
--	LEFT JOIN EXT_Detail_J j on j.Items_FK = i.ITEMS_PK AND (j.Gender_FK = c.lc_pk OR j.Karat_FK = c.lc_pk OR j.Metal_FK = c.lc_pk OR j.Sizelen_FK = c.lc_pk OR j.Style_FK = c.lc_pk)
--	LEFT JOIN EXT_stones st on st.JDT_FK = j.JDT_PK AND (st.COLOR_FK = c.lc_pk OR st.SHAPE_FK = c.lc_pk OR st.TRANSLUCFK = c.lc_pk OR st.TYPSTONEFK = c.lc_pk)
--  WHERE lv.LookupValueId IS NULL AND [lb_type] != 'A' AND [lb_type] != 'V' AND lc_pk!=1 AND
--  (n.Sto_PK = @StoreID OR (cu.Cus_id IS NOT NULL OR i.Items_ID IS NOT NULL OR g.GDT_id IS NOT NULL OR j.JDT_id IS NOT NULL OR st.STO_id IS NOT NULL))
--  ) q
--  WHERE q.RowNumber=1

--END
--GO
/****** Object:  StoredProcedure [dbo].[AuditItemsExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO

-- =============================================
-- Author:		Nadeem Akhtar
-- Create date: <8/29/2016>
-- Description:	<Extracts audit data for Inventory Items>
-- Sample Call: EXEC [dbo].[AuditItemsExtract] 2, 133 , 5

-- Updated By: Syed Mohsin
-- Updated Date: <8/30/2016>
-- Comments: Mapping corrected and Join conditions updated.

-- Updated By: Farrukh Ijaz
-- Updated Date: <9/16/2016>
-- Comments: Drop Columned 2nd Select to get item version for inventory items where stone version exists but item version does not.
-- =============================================

--CREATE Proc [dbo].[AuditItemsExtract]
--@StoreID int, @TargetStoreId int, @UtcTime int
--as
--BEGIN
--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

--DECLARE @LookuptypeBin int;
--SET @LookuptypeBin=(SELECT LookupTypeId FROM CEXT_LookupType WHERE LookupTypeName='BIN')

--DECLARE @LookuptypeBrand int;
--SET @LookuptypeBrand = (SELECT LookupTypeId FROM CEXT_LookupType WHERE LookupTypeName='Brand')

--select C.InventoryItemId as InventoryItemId,
--       DATEDrop Column(HOUR,@UtcTime,A.LastUpdatedDate) as CreatedDate,
--       u.UserId as CreatedBy,
--       DATEDrop Column(HOUR,@UtcTime,A.LastUpdatedDate) as UpdatedDate,
--       u.UserId as UpdatedBy,
--       @TargetStoreId as StoreID,
--       c.InventoryNumber as InventoryNumber,
--       it.ItemTypeId as ItemTypeId,
--       cast (LTRIM(RTRIM(MODELNUM))       as nvarchar(30)) as Model,
--       cast (LTRIM(RTRIM(SERIALNUM))      as nvarchar(50)) as SerialNumber,
--       cast (LTRIM(RTRIM(OWNERNUM))       as     nvarchar(50)) as    OwnerNumber,
--       itb.ItemTypeBrandId as ItemTypeBrandId,
--       lvcol.LookupValueId as ItemColorId,
--       cast (Amount as decimal(12,3) ) as              Cost,
--       cast (RESALEAMT      as decimal(12,3) )  as Resale,
--       cast (LOWSLPRICE as decimal(12,3) )  as  [Min],
--       cast (LTRIM(RTRIM(A.DESCRIPT)) as          nvarchar(500) ) as Comment,
--       a.IsBulkItem  as     IsBulkItem,
--       NEWITEM       as     IsNewItem,
--       cast(a.UPC    as nvarchar(14)) as UPC,
--       cast ( MAXQUANT      as int) as    MaxQuantity,
--       cast (REORDER as int)              as ReOrderLevel,
--	   C.IsInventoryItem as IsInventoryItem,
--      lv.LookupValueId as BinNumberId

--from EXT_Audit_Items A
--       INNER JOIN CEXT_InventoryItem C
--              on a.Items_ID =c.sourceid AND c.StoreId = @TargetStoreId
--       INNER JOIN CEXT_ItemType it
--              ON it.SourceId =
--                     CASE
--                           WHEN(LEVEL4_FK != 1) THEN LEVEL4_FK
--                           WHEN (LEVEL3_FK != 1) THEN LEVEL3_FK
--                           WHEN (LEVEL2_FK != 1) THEN LEVEL2_FK
--                           ELSE LEVEL1_FK
--                     END
--	left outer join CEXT_LookupValue lv
--		ON lv.LookupTypeId = @LookuptypeBin AND a.BIN = lv.Value AND lv.StoreId = @TargetStoreId

--	left outer join CEXT_LookupValue lvcol
--		ON a.Color != 1 AND lvcol.SourceId = a.Color AND a.BIN = lv.Value AND lvcol.StoreId = @TargetStoreId

--	LEFT OUTER JOIN EXT_Level5 ll5
--		ON ll5.lv5_PK = A.LEVEL5_FK

--	LEFT OUTER JOIN CEXT_LookupValue lvb
--		ON lvb.LookupTypeId = @LookuptypeBrand AND lvb.Value = RTRIM(LTRIM(ll5.DESCRIPT)) AND lvb.StoreId = @TargetStoreId

--     LEFT OUTER JOIN CEXT_ItemTypeBrand itb
--		ON itb.BrandId = lvb.LookupValueId

--	LEFT OUTER JOIN CEXT_User u
--		ON u.SourceId = a.LastUpdatedUSR_ID

--UNION

--SELECT
--	   C.InventoryItemId as InventoryItemId,
--       C.CreatedDate as CreatedDate,
--       1 as CreatedBy,
--       C.UpdatedDate as UpdatedDate,
--       u.UserId as UpdatedBy,
--       @TargetStoreId as StoreID,
--       c.InventoryNumber as InventoryNumber,
--       it.ItemTypeId as ItemTypeId,
--       cast (LTRIM(RTRIM(i.MODELNUM))       as nvarchar(30)) as Model,
--       cast (LTRIM(RTRIM(i.SERIALNUM))      as nvarchar(50)) as SerialNumber,
--       cast (LTRIM(RTRIM(i.OWNERNUM))       as     nvarchar(50)) as    OwnerNumber,
--       itb.ItemTypeBrandId as ItemTypeBrandId,
--       lvcol.LookupValueId as ItemColorId,
--       cast (i.Amount as decimal(12,3) ) as              Cost,
--       cast (i.RESALEAMT      as decimal(12,3) )  as Resale,
--       cast (i.LOWSLPRICE as decimal(12,3) )  as  [Min],
--       cast (LTRIM(RTRIM(i.DESCRIPT)) as          nvarchar(500) ) as Comment,
--       i.IsBulkItem  as     IsBulkItem,
--       i.NEWITEM       as     IsNewItem,
--       cast(i.UPC    as nvarchar(14)) as UPC,
--       cast ( i.MAXQUANT      as int) as    MaxQuantity,
--       cast (i.REORDER as int)              as ReOrderLevel,
--	   C.IsInventoryItem as IsInventoryItem,
--      lv.LookupValueId as BinNumberId

--FROM EXT_items i
--INNER JOIN EXT_Detail_J j ON i.ITEMS_PK = j.Items_FK AND i.Sto_Pk=j.Sto_FK
--INNER JOIN EXT_stones s ON j.JDT_PK = s.JDT_FK AND j.Sto_FK=s.Sto_FK
--LEFT JOIN EXT_Audit_Items ai ON i.Items_ID=ai.Items_ID
--INNER JOIN CEXT_InventoryItem C
--              on i.Items_ID =c.sourceid AND c.StoreId = @TargetStoreId
--LEFT OUTER JOIN CEXT_User u
--		ON u.SourceId = i.LastUpdatedUSR_ID
--INNER JOIN CEXT_ItemType it
--              ON it.SourceId =
--                     CASE
--                           WHEN(i.LEVEL4_FK != 1) THEN i.LEVEL4_FK
--                           WHEN (i.LEVEL3_FK != 1) THEN i.LEVEL3_FK
--                           WHEN (i.LEVEL2_FK != 1) THEN i.LEVEL2_FK
--                           ELSE i.LEVEL1_FK
--                     END
--left outer join CEXT_LookupValue lv
--		ON lv.LookupTypeId = @LookuptypeBin AND i.BIN = lv.Value AND lv.StoreId = @TargetStoreId
--left outer join CEXT_LookupValue lvcol
--		ON i.Color != 1 AND lvcol.SourceId = i.Color AND i.BIN = lv.Value AND lvcol.StoreId = @TargetStoreId

--LEFT OUTER JOIN EXT_Level5 l5
--	ON l5.lv5_PK = i.LEVEL5_FK

--LEFT OUTER JOIN CEXT_LookupValue lvb
--		ON lvb.LookupTypeId = @LookuptypeBrand AND lvb.Value = RTRIM(LTRIM(l5.DESCRIPT))-- AND lvb.StoreId = @TargetStoreId
--LEFT OUTER JOIN CEXT_ItemTypeBrand itb
--		ON itb.BrandId = lvb.LookupValueId

--WHERE ai.AUD_Num IS NULL

--END
--GO
/****** Object:  StoredProcedure [dbo].[AuditStonesExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO

-- =============================================
-- Author:		Nadeem Akhtar
-- Create date: <8/29/2016>
-- Description:	<Extracts audit data for Inventory Item Stones>
-- Sample Call: EXEC [dbo].[AuditStonesExtract] 2, 133

-- Updated By: Syed Mohsin
-- Updated Date: <8/30/2016>
-- Comments: Mapping corrected and Join conditions updated.

-- Updated By: Farrukh Ijaz
-- Updated Date: <9/16/2016>
-- Comments: Drop Columned InventoryItemVersionId.
-- =============================================

--CREATE Proc [dbo].[AuditStonesExtract]
--@SourceStoreId int, @TargetStoreId int
--as
--BEGIN
--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

--WITH InventoryItemVer AS
--(
--    SELECT ROW_NUMBER() OVER(PARTITION BY InventoryItemId ORDER BY CreatedDate DESC) as RowNumber,
--	InventoryItemVersionId, InventoryItemId FROM CEXT_InventoryItemVersion
--)

--       SELECT
--       iv.InventoryItemVersionId,
--	   s.LastUpdatedDate as CreatedDate,
--       1 as CreatedBy,
--       s.LastUpdatedDate as UpdatedDate,
--       u.UserId as UpdatedBy,
--       @TargetStoreId as StoreID,
--       st.InventoryItemStoneId as   InventoryItemStoneId,
--	   st.InventoryItemId  as InventoryItemId,
--       cast (s.NUMSTONE       as int) as    Quantity,
--       lvtyp.LookupValueId    as     StoneTypeId,
--       lvshape.LookupValueId      as     StoneShapeId,
--       cast( s.CARAT as decimal(4,2) ) as   Carat,
--       lvcol.LookupValueId      as     StoneColorId,
--       cast(s.WEIGHT   as decimal(4,2))     as     [Weight],
--       cast(  s.LENGTH as     decimal(4,2)) [Length],
--       cast(s.WIDTH    as decimal(4,2))     as     Width,
--       lvtran.LookupValueId    as     StoneClarityId

--from   EXT_AUDIT_Stones s
--INNER JOIN CEXT_InventoryItemStone st
--	ON s.STO_ID = st.SourceId
--INNER JOIN EXT_stones t
--	ON s.STO_ID = t.STO_id AND t.Sto_FK = @SourceStoreId
--LEFT JOIN (SELECT * FROM InventoryItemVer WHERE RowNumber=1) iv
--	ON st.InventoryItemId = iv.InventoryItemId
----Joins with LookupValue
--LEFT JOIN CEXT_LookupValue lvtyp
--ON lvtyp.StoreId = @TargetStoreId AND lvtyp.SourceId = s.TYPSTONEFK

--LEFT JOIN CEXT_LookupValue lvshape
--ON lvshape.StoreId = @TargetStoreId AND lvshape.SourceId = s.SHAPE_FK

--LEFT JOIN CEXT_LookupValue lvcol
--ON lvcol.StoreId = @TargetStoreId AND lvcol.SourceId = s.COLOR_FK

--LEFT JOIN CEXT_LookupValue lvtran
--ON lvtran.StoreId = @TargetStoreId AND lvtran.SourceId = s.TRANSLUCFK


--LEFT OUTER JOIN CEXT_User u
--		ON u.SourceId = s.LastUpdatedUSR_ID

--END
--GO
/****** Object:  StoredProcedure [dbo].[BrandsExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO

---- =============================================
---- Author:		Syed Mohsin
---- Create date: <8/15/2016>
---- Description:	<Extracts data for Brands>
---- Sample Call: EXEC [dbo].[BrandsExtract] 8, 171

---- Updated By: Syed Mohsin
---- Update date: <10/18/2016>
---- Comments: Repeating brands issue resolved.
---- =============================================

--CREATE Proc [dbo].[BrandsExtract]
--@SourceStoreID int, @TargetStoreId int

--AS

--BEGIN
--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
--declare @lookupTypeID int

--select @lookupTypeID=
--		 LookupTypeId from
--		CEXT_LookupType s
--		where LookupTypeName='Brand'
--SELECT * FROM (
--select

--	@lookupTypeID as  LookupTypeID,
--	cast(RTRIM(LTRIM(s.DESCRIPT)) as nvarchar(50))	as	value,
--	GetDate() as	CreatedDate	,
--	1 as CreatedBy, --Drop Column in package
--	GetDate() as	UpdatedDate,
--	u.UserId as UpdatedBy,
--	0 as	IsDeleted,
--	@TargetStoreId  as StoreId,
--	s.lv5_PK as SourceID,
--	ROW_NUMBER() over (partition by RTRIM(LTRIM(s.DESCRIPT)) order by s.lv5_PK) as Num1


-- from  EXT_Level5 s
--	left outer join CEXT_LookupValue t
--		on RTRIM(LTRIM(s.DESCRIPT)) = t.Value
--			and t.LookupTypeId=@lookupTypeID
--	left outer join CEXT_User u
--		on s.LastUpdatedUSR_ID = u.SourceId
-- where Len(RTRIM(LTRIM(s.DESCRIPT))) >0
--	and t.Value is null AND s.sto_pk = @SourceStoreID
--)q where Num1 = 1
--END
--GO
/****** Object:  StoredProcedure [dbo].[BrandsNCICExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO

---- =============================================
---- Author:		Syed Mohsin
---- Create date: <8/11/2016>
---- Description:	<Extracts data for LevelN Lookup values>
---- Sample Call: EXEC [dbo].[BrandsNCICExtract] 1 , 389

---- =============================================
--CREATE Proc [dbo].[BrandsNCICExtract]
--@SourceStoreId int , @TargetStoreId int

--AS
--BEGIN
--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

--  SELECT lv.LookupValueId,
--cast(n.NCIC_Code as nvarchar(50)) NCIC_Code ,
--  cast(n.Local_Code as nvarchar(50)) Local_Code,
--  getdate() as UpdateDate,
--  getdate() as CreatedDate,
--  u.UserId UpdatedBy,
--  0 as IsDeleted,
--  @TargetStoreId Storeid
-- from CEXT_LookupValue lv
--  INNER JOIN EXT_Level5 c ON lv.Value = LTRIM(RTRIM(c.DESCRIPT)) AND c.Sto_PK = @SourceStoreId
--  INNER JOIN EXT_Level_N n ON n.Level_FK = c.lv5_pk AND n.sto_pk = @SourceStoreId
--  LEFT JOIN CEXT_User u ON u.SourceId = n.LastUpdatedUSR_ID
-- WHERE lv.LookupTypeId=8

--END
--GO
/****** Object:  StoredProcedure [dbo].[CustomerPicExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO

---- =============================================
---- Author:		Syed Mohsin
---- Create date: <8/17/2016>
---- Description:	<Extracts data for Customer's Pictures>
---- Sample Call: EXEC [dbo].[CustomerPicExtract]

---- Updated By: Syed Mohsin
---- Update date: <9/26/2016>
---- Comments: Modified query by Drop Columning UNION for extracting driver license picture differently.

---- Updated By: Syed Mohsin
---- Update date: <12/23/2016>
---- Comments: Drop Columned check for image 0x.
---- =============================================

--CREATE Proc [dbo].[CustomerPicExtract]
--AS
--BEGIN
--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

--DECLARE @CusPicSourceId int
--SET @CusPicSourceId  = (select PictureSourceId FROM CEXT_PictureSource p
--  WHERE p.PictureSourceName = 'Customer Picture')


--DECLARE @DLPicSourceId int
--SET @DLPicSourceId  = (select PictureSourceId FROM CEXT_PictureSource p
--  WHERE p.PictureSourceName = 'CustomerIdPicture')

--select cusPic_Img as ObjectImage,
--cusPic_Img_Date as PictureTakenDate,
--@CusPicSourceId as PictureSourceId,
--c.CustomerId as CustomerId,
--0 as IsDeleted,
--getdate() as CreatedDate,
--1 as CreatedBy ,-- Drop Column in package
--getdate() as UpdatedDate,
--c.UpdatedBy as UpdatedBy

--from PEXT_custPics cp
--inner join CEXT_Customer c
--	ON c.SourceId = cp.cus_id
--where cusPic_Img IS NOT NULL and CONVERT(varchar(max),CONVERT(varbinary(max),cuspic_Img),1) != ''

--UNION ALL

--select cuspic_DLImg as ObjectImage,
--cusPic_DLImg_Date as PictureTakenDate,
--@DLPicSourceId as PictureSourceId,
--c.CustomerId as CustomerId,
--0 as IsDeleted,
--getdate() as CreatedDate,
--1 as CreatedBy ,-- Drop Column in package
--getdate() as UpdatedDate,
--c.UpdatedBy as UpdatedBy

--from PEXT_custPics cp
--inner join CEXT_Customer c
--	ON c.SourceId = cp.cus_id
--where cuspic_DLImg IS NOT NULL and CONVERT(varchar(max),CONVERT(varbinary(max),cuspic_DLImg),1) != ''

--END
--GO
/****** Object:  StoredProcedure [dbo].[CustomersExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO

---- =============================================
---- Author:		Nadeem Akhtar
---- Create date: <8/3/2016>
---- Description:	<Extracts data for Customers>
---- Sample Call: EXEC [dbo].[CustomersExtract] 1

---- Updated By: Syed Mohsin
---- Update date: <8/5/2016>
---- Comments: Corrected mapping for columns and Drop Columned join conditions to meet the design logic.
----			 Drop Columned Storeid parameter for Source StoreId

---- Updated By: Syed Mohsin
---- Update date: <8/16/2016>
---- Comments: Drop Columned join for StateIssueId. Revised mapping.

---- Updated By: Syed Mohsin
---- Update date: <9/1/2016>
---- Comments: Removed store condition check.

---- Updated By: Syed Mohsin
---- Update date: <9/2/2016>
---- Comments: Fixes made for iteration 1 response.

---- Updated By: Syed Mohsin
---- Update date: <9/22/2016>
---- Comments: Removed cash customer from extract and applied trim on Customer name. Also Drop Columned lookupids for sex column

---- Updated By: Syed Mohsin
---- Update date: <10/17/2016>
---- Comments: Applied fixes for Bug 6249

---- Updated By: Syed Mohsin
---- Update date: <11/3/2016>
---- Comments: Applied fixes country mapping

---- Updated By: Syed Mohsin
---- Update date: <11/23/2016>
---- Comments: Drop Columned column SSNLast4Digits

---- Updated By: Syed Mohsin
---- Update date: <11/23/2016>
---- Comments: Join Drop Columned of state to get country
---- =============================================

--CREATE Proc [dbo].[CustomersExtract]
--@StoreId int

--AS
--Begin
--DECLARE @CustomerNumber int;
--SET @CustomerNumber =
--(SELECT MAX(CustomerNumber) FROM CEXT_Customer)

--DECLARE @CustomerCharacteristics int;
--SET @CustomerCharacteristics = (SELECT LookupTypeId from CEXT_LookupType where LookupTypeName = 'Customer Characteristics')

--DECLARE @LookUpTypeHair int;
--SET @LookUpTypeHair = (SELECT LookupTypeId from CEXT_LookupType where LookupTypeName = 'Hair' and ParentLookupTypeId = @CustomerCharacteristics)

--DECLARE @LookUpTypeEye int;
--SET @LookUpTypeEye = (SELECT LookupTypeId from CEXT_LookupType where LookupTypeName = 'Eyes' and ParentLookupTypeId = @CustomerCharacteristics)

--DECLARE @LookUpTypeRace int;
--SET @LookUpTypeRace = (SELECT LookupTypeId from CEXT_.LookupType where LookupTypeName = 'Race' and ParentLookupTypeId = @CustomerCharacteristics)

--DECLARE @IdType int;
--SET @IdType = (SELECT LookupTypeId from CEXT_LookupType where LookupTypeName = 'ID Type' and ParentLookupTypeId = @CustomerCharacteristics)

--DECLARE @MaleId int;
--SET @MaleId = (select LookupValueId from cEXT_LookupValue lv inner join cEXT_LookupType lt on lv.LookupTypeId = lt.LookupTypeId and LookupTypeName = 'sex' and lv.Value = 'MALE')


--DECLARE @FemaleId int;
--SET @FemaleId = (select LookupValueId from cEXT_LookupValue lv inner join cEXT_LookupType lt on lv.LookupTypeId = lt.LookupTypeId and LookupTypeName = 'sex' and lv.Value = 'FEMALE')

--DECLARE @IdRef int;
--SET @IdRef = (SELECT LookupTypeId from CEXT_LookupType where LookupTypeName = 'Referred')


--	SELECT
--		iif(Cus_Entered IS NULL,getdate(),Cus_Entered) as CreatedDate,
--		1 as CreatedBy, --Drop Column in package
--		u.UserId as UpdatedBy,
--		GetDate() as UpdatedDate,
--		Cus_Store as StoreID, --Drop Column in package (TargetStoreId)
--		@CustomerNumber as CustomerNumber,--Increment in package
--		cast (1 as varbinary(max)) as Barcode,
--		cast(LTRIM(RTRIM(CUS_FNAME)) as nvarchar(100)) as FirstName,
--		cast(LTRIM(RTRIM(CUS_MNAME)) as nvarchar(100)) as MiddleName,
--		cast(LTRIM(RTRIM(s.CUS_LNAME)) as nvarchar(100)) as LastName,
--		cast(LTRIM(RTRIM(CUS_Drop Column1)) as nvarchar(500))	as Drop ColumnressLine1,
--		cast(LTRIM(RTRIM(CUS_Drop Column2)) as nvarchar(500))	as Drop ColumnressLine2,
--		cast(LTRIM(RTRIM(CUS_CITY)) as nvarchar(50)) as City,
--		cast(cst.Country as nvarchar(100)) as Country,

--		cast(LTRIM(RTRIM(CUS_STATE)) as nvarchar(50))	as State,
--		cast(LTRIM(RTRIM(CUS_ZIP)) as nvarchar(10))		as ZipCode,
--		iif(CUS_PHONE1 = '','',cast(LTRIM(RTRIM(CUS_AC1))+'-'+LTRIM(RTRIM(CUS_PHONE1)) as nvarchar(20))	) as Phone1,
--		cast (LTRIM(RTRIM(REPLACE(CUS_SSNUM,'-','')))	as	nvarchar(400)) as SSN,
--		lvtype1.LookupValueId	as			ID1TypeID, -- Iteration 1 response
--		st.Id as ID1StateIssueID,
--		CUS_ID1EXP	 as ID1ExpiryDate,
--		cast(CUS_IDNUM1 as nvarchar(50)) as ID1Number,
--		lvtype2.LookupValueId	as ID2TypeID, -- Iteration 1 response
--		st2.Id	as ID2StateIssueID,
--		CUS_ID2EXP		as ID2ExpiryDate,
--		cast(LTRIM(RTRIM(CUS_IDNUM2))	as nvarchar(50))	as			ID2Number,
--		cast(LTRIM(RTRIM(CUS_IDDrop Column1))	as nvarchar(500))	as			IDDrop ColumnressLine1,
--		cast(LTRIM(RTRIM(CUS_IDDrop Column2)) as nvarchar(500)) as			IDDrop ColumnressLine2,
--		cast(LTRIM(RTRIM(CUS_IDCITY)) as nvarchar(50)) as IDCity,
--		cast(LTRIM(RTRIM(CUS_IDSTATE))	as nvarchar(10) ) as IDState,
--		cast(idst.Country as nvarchar(100)) as IDCountry,
--		cast(LTRIM(RTRIM(CUS_IDZIP))	as nvarchar(10)	) as			IDZipCode,
--		cast (LTRIM(RTRIM(Cus_CellPhone)) as	nvarchar(20) ) as 				CellPhone,
--		cast(LTRIM(RTRIM(Cus_Email))	as nvarchar(100)) as 				Email,
--		cast(LTRIM(RTRIM(CUS_HEIGHT))	as nvarchar(50)) as				Height,
--		CUS_WEIGHT	 as Weight,
--		lvhair.LookupValueId	 as Hair,-- Iteration 1 response
--		lveye.LookupValueId	as Eyes,	-- Iteration 1 response
--		lvrace.LookupValueId	as Race,	-- Iteration 1 response
--		cast( CASE CUS_SEX
--				WHEN 'F' then @FemaleId
--				WHEN 'M' then @MaleId
--				ELSE NULL
--				END
--		  as nvarchar(10))	as Sex,
--		CUS_BIRTHDate as BirthDate,
--		cast(LTRIM(RTRIM(CUS_BIRTHCITY)) as 	nvarchar(50)) as	BirthCity,
--		cast(LTRIM(RTRIM(CUS_BIRTHSTATE))	as nvarchar(10)) as		BirthState,
--		cast(LTRIM(RTRIM(CUS_COUNTRY)) as nvarchar(100)) as BirthCountry ,
--		cast(LTRIM(RTRIM(CUS_MARKS))	as nvarchar(100)	) as	Features,
--		cast(LTRIM(RTRIM(CUS_COMMENT))	as nvarchar(500)) as	Comments,
--		cast (LTRIM(RTRIM(CUS_EMPLOYER))	as nvarchar(200)) as	EmployeeName,
--		cast(LTRIM(RTRIM(CUS_EMPAD1))	as nvarchar(500)) as		EmpDrop ColumnressLine1,
--		cast(LTRIM(RTRIM(CUS_EMPAD2))		as nvarchar(500)) as 	EmpDrop ColumnressLine2,
--		cast(LTRIM(RTRIM(CUS_EMPCITY)) as nvarchar(50)	 ) as	EmpCity,
--		cast(LTRIM(RTRIM(CUS_EMPSTATE))	as nvarchar(10)	) as	EmpState,
--		CAST(empst.Country as nvarchar(100)) as EmpCountry ,
--		cast(LTRIM(RTRIM(CUS_EMPZIP)) as	nvarchar(10))		as	EmpZipCode,
--		iif(CUS_AC2 = '','',cast(LTRIM(RTRIM(CUS_AC2))+'-'+LTRIM(RTRIM(CUS_EMPPHONE))	as nvarchar(20)) ) as		EmpPhone1,
--		Cus_Military	as	Military,
--		CUS_KNOWN	as		Known,
--		iif(CUS_SPECIAL = 1,NULL,reflv.lookupValueId ) as Referred,
--		cast(LTRIM(RTRIM(CUS_fflnum)) as nvarchar(20)) as FedFirearmNumber,
--		Cus_FFLExpireDate as FedFirearmExpiryDate,
--		cast(LTRIM(RTRIM(CUS_VEHIC1)) as nvarchar(100)) as VehicleMake,
--		cast(LTRIM(RTRIM(CUS_VEHIC2)) as nvarchar(100)) as VehicleTagNumber,
--		cast(LTRIM(RTRIM(CUS_VEHIC3)) as nvarchar(10)) as VehicleTagState,
--		cast(LTRIM(RTRIM(Cus_TaxID))	as nvarchar(100)) as		TexId,
--		cast(CUS_CREDIT	as decimal(12,3)) as		CustomerCredit,
--		--Cus_Preferred	as		PreferredRate	,	Update Later
--		CUS_DELETE	as	IsDeleted,
--		iif(CUS_DELETE = 1,0,1) as IsActive, -- Iteration 1 response
--		Cus_AnnivDate	as		AnniversaryDate	,
--		Cus_Form8300Sent	as	Form8300Date,
--		cx.Cus_TxtMsgSent	as		LastTextDate	,
--		s.Cus_TxtMsg	as			IsSMSAllowed,
--		cx.Cus_TxtMsgsendfinancials as IsSMSFinancials,
--		CUS_LOCKED	as			IsLocked	,
--		cx.Cus_TxtMsgsendsales as IsSMSMaketing,
--		iif(cx.Cus_TxtMsgDeclined IS NULL,0,cx.Cus_TxtMsgDeclined) as IsNeverSendSMS,
--		Cus_NoRemind	as		IsNeverSendRemindersLetter	,
--		cus_MLARate	as			IsMLARateApplied,-- not in actual source db
--		Cus_PatriotAct as IsOFACSDNList,
--		Cus_PatriotMsg as IsNeverDisplayOFACWarning,
--		iif(LTRIM(RTRIM(REPLACE(CUS_SSNUM,'-',''))) = '',NULL,CAST(RIGHT(LTRIM(RTRIM(REPLACE(CUS_SSNUM,'-',''))),4) as nvarchar(10))) as SSNLastDigits,
--		s.Cus_id	as SourceId

--		from EXT_cust s
--		OUTER APPLY													----Drop Columned outer apply to compensate for an SBT switch issue in Classic (Devon Rhodes 26 April 2018)
--		(
--			SELECT TOP 1 * FROM EXT_custxtinfo CX
--			 WHERE S.CUS_ID = CX.Cus_id
--		)CX
--		--left join [PawnMaster_foxPro].dbo.custxtinfo cx
--		--	on s.Cus_id = cx.Cus_id
--		left outer join dbo.[state] st
--			on s.CUS_ID1ISSUE = st.StateCode
--		left outer join dbo.[state] st2
--			on s.CUS_ID2ISSUE = st2.StateCode
---- Iteration 1 response

--		left join EXT_Lookup_C ch
--		on ch.lc_pk = s.CUS_HAIRFK

--		left join CEXT_LookupValue lvhair
--			on ch.lc_Descript = lvhair.Value and lvhair.LookupTypeId = @LookUpTypeHair

--		left join EXT_Lookup_C ce
--			on ce.lc_pk = s.CUS_EYESFK

--		left join CEXT_LookupValue lveye
--			on ce.lc_Descript = lveye.Value and lveye.LookupTypeId = @LookUpTypeEye

--		left join EXT_Lookup_C cr
--			on cr.lc_pk = s.CUS_RACEFK

--		left join CEXT_LookupValue lvrace
--			on cr.lc_Descript = lvrace.Value and lvrace.LookupTypeId = @LookUpTypeRace

--		left join  EXT_Lookup_C ct1
--			on ct1.lc_pk = s.CUS_IDTYP1

--		left join CEXT_LookupValue lvtype1
--			on ct1.lc_Descript = lvtype1.Value and lvtype1.LookupTypeId = @IdType

--		left join  EXT_Lookup_C ct2
--			on ct2.lc_pk = s.CUS_IDTYP2

--		left join CEXT_LookupValue lvtype2
--			on ct2.lc_Descript = lvtype2.Value and lvtype2.LookupTypeId = @IdType

--		left join EXT_Lookup_C ref
--			on ref.lc_pk = s.CUS_SPECIAL and s.CUS_SPECIAL != 1

--		left join CEXT_LookupValue reflv
--			on ref.lc_Descript = reflv.Value and reflv.LookupTypeId = @IdRef

--		left join dbo.[State] bst
--			on bst.StateCode = s.CUS_BIRTHSTATE

--		left join dbo.[State] cst
--			on cst.StateCode = s.CUS_STATE

--		left join dbo.[State] idst
--			on idst.StateCode = s.CUS_IDSTATE

--		left join dbo.[State] empst
--			on empst.StateCode = s.CUS_EMPSTATE

--			left join CEXT_User u
--			ON  s.LastUpdatedUSR_ID = u.SourceId

--		WHERE LTRIM(RTRIM(s.CUS_LNAME)) != 'CASH CUSTOMER' and s.Cus_Store=@StoreId

--END
--GO
/****** Object:  StoredProcedure [dbo].[FirearmRepairExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO

---- =============================================
---- Author:		Syed Mohsin
---- Create date: <8/25/2016>
---- Description:	<Extracts repair history for firearms >
---- Sample Call: EXEC [dbo].[FirearmRepairExtract] 8
---- =============================================

--CREATE Proc [dbo].[FirearmRepairExtract]
--@StoreId int
--as
--BEGIN
--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
--SELECT
--	it.InventoryItemId as InventoryItemId,
--	iif(r.subStatus='NONE',0,1) as IsSentOut,
--	CAST(r.SUBOUT as datetime) as SentOutDate,
--	CAST(r.Quantity as int) as Quantity,
--	v.VendorId,
--	CAST(r.WORK1 as nvarchar(500)) as Comment,
--	iif(r.subStatus='RETURNED',1,0) as IsReturned,
--	CAST(r.SUBIN as datetime) as ReturnedDate ,
--	CAST(r.Drop ColumnedCost as decimal(12,3)) as RepairCost,
--	getdate() as CreatedDate,
--	1 as CreatedBy, --Drop Column in package
--	getdate() as UpdatedDate,
--	1 as UpdatedBy

--FROM EXT_repair r

--INNER JOIN EXT_items i
--ON r.Items_PK = i.ITEMS_PK AND r.STO_PK = i.Sto_Pk

--INNER JOIN EXT_Level1 l1
--ON i.LEVEL1_FK = l1.lv1_pk AND l1.DESCRIPT = 'FIREARM'

--INNER JOIN EXT_vend ven
--on ven.VEN_PK = r.vend_fk

--INNER JOIN CEXT_Vendor v
--ON v.SourceId = ven.VEN_id

--INNER JOIN CEXT_InventoryItem it
--ON it.SourceId = i.Items_ID

--WHERE r.vend_fk != -1  AND r.STO_PK = @StoreId

--END

--GO
/****** Object:  StoredProcedure [dbo].[GunlogEntityExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Syed Mohsin
-- Create date: <9/1/2016>
-- Description:	<Extracts data for LevelN Lookup values>
-- Sample Call: EXEC [dbo].[GunlogEntityExtract] 8 , 180, 5

--Updated By: Syed Mohsin
--Updated Date: <9/29/2016>
--Comments: Drop Columned mapping for newly Drop Columned columns

--Updated By: Syed Mohsin
--Updated Date: <12/1/2016>
--Comments: Drop Columned utc offset
-- =============================================
--CREATE Proc [dbo].[GunlogEntityExtract]
--@SourceStoreId int , @TargetStoreId int, @UtcOffset int

--AS
--BEGIN
--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

--DECLARE @CreatedDate datetime
--SET @CreatedDate = (select dateDrop Column(HOUR,@UtcOffset,min(gt_date))from EXT_guntrans)

--DECLARE @UpdatedDate datetime
--SET @UpdatedDate = (select DATEDrop Column(HOUR,@UtcOffset,max(gt_date))from EXT_guntrans)

--Select
--       ISNULL(@CreatedDate,DATEDrop Column(HOUR,@UtcOffset,GETDATE())) as CreatedDate,
--       1 as CreatedBy,
--       ISNULL(@UpdatedDate,DATEDrop Column(HOUR,@UtcOffset,GETDATE())) as UpdatedDate,
--       u.UserId as UpdatedBy,
--       @TargetStoreId as StoreID,

--       gl.GunLogId as GunLogId,
--       Cast ( BUYFNAME as nvarchar(100) ) as ReceiptFirstName,
--       Cast ( BUYMNAME as nvarchar(100) ) as ReceiptMiddleName,
--       Cast ( BUYLNAME as nvarchar(250) ) as ReceiptLastName,
--       Cast ( BUYDrop Column1 as nvarchar(500) ) as ReceiptDrop ColumnressLine1,
--       Cast ( BuyDrop Column2 as nvarchar(500) ) as ReceiptDrop ColumnressLine2,
--       Cast ( BUYCITY as nvarchar(50) ) as ReceiptCity,
--       Cast ( BUYSTATE as nvarchar(50) ) as ReceiptState,
--	   Cast ( St.Country as nvarchar(100) ) as ReceiptCountry,
--       Cast ( BUYZIP as nvarchar(10) ) as ReceiptZipCode,
--       Cast ( BUYIDTYPE as nvarchar(150) ) as ReceiptIDType,
--       Cast ( BUYIDNUM as nvarchar(50) ) as ReceiptNumber,
--       Cast ( BUYDATE as datetime ) as ReceiptDate,
--       Cast ( SOLDFNAME as nvarchar(100) ) as DispositionFirstName,
--       Cast ( SOLDMNAME as nvarchar(100) ) as DispositionMiddleName,
--       Cast ( SOLDLNAME as nvarchar(250) ) as DispositionLastName,
--       Cast ( SOLDDrop Column1 as nvarchar(500) ) as DispositionDrop ColumnressLine1,
--       Cast ( SOLDDrop Column2 as nvarchar(500) ) as DispositionDrop ColumnressLine2,
--       Cast ( SOLDCITY as nvarchar(50) ) as DispositionCity,
--       Cast ( SOLDSTATE as nvarchar(50) ) as DispositionState,
--	   Cast ( st2.Country as nvarchar(100) ) as DispositionCountry,
--       Cast ( SOLDZIP as nvarchar(10) ) as DispositionZipCode,
--       Cast ( SOLDIDTYPE as nvarchar(150) ) as DispositionIDType,
--       Cast ( SOLDIDNUM as nvarchar(50) ) as DispositionNumber,
--       Cast ( SOLDDATE as datetime ) as DispositionDate,
--	   CAST ( inv.InventoryItemId as nvarchar(50) ) as InvItemTypeId,
--	   CAST ( inv.FirearmImporterId as nvarchar(50) ) as InvFirearmImporterId,
--	   CAST ( inv.ItemTypeBrandId as nvarchar(50) ) as InvItemTypeBrandId,
--	   CAST ( inv.FirearmCaliberGaugeId as nvarchar(50) ) as InvFirearmCaliberGaugeId,
--	   CAST ( inv.FirearmActionId as nvarchar(50) ) as InvFirearmActionId,
--	   CAST ( inv.FirearmCondition as nvarchar(50) ) as InvFirearmCondition,
--	   CAST ( inv.Model as nvarchar(50) ) as InvModel,
--	   CAST ( inv.SerialNumber as nvarchar(50) ) as InvSerialNumber



--from EXT_gunlog g
--       inner join CEXT_GunLog gl
--              on g.GunLog_PK=gl.SourceID
--		left join CEXT_User u
--			on u.SourceId = LastUpdatedUSR_ID
--		left join EXT_items i
--			on i.INVNUM = g.INVNUM
--		left join CEXT_InventoryItem inv
--			on inv.SourceId = Items_ID
--		left join dbo.State st
--			on st.StateCode = g.BUYSTATE
--		left join dbo.State st2
--			on st2.StateCode = g.SOLDSTATE
--		where g.STO_PK=@SourceStoreId
--END
--GO
--/****** Object:  StoredProcedure [dbo].[GunlogExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO

-- =============================================
-- Author:		Syed Mohsin
-- Create date: <8/15/2016>
-- Description:	<Extracts data for gunlogs>
-- Sample Call: EXEC [dbo].[GunlogExtract] 8 , 179 , 5

--Modified date: <12/1/2016>
--Modified by: Syed Mohsin
--Comments: Drop Columned utc date offset
-- =============================================

--CREATE Proc [dbo].[GunlogExtract]
--@SourceStoreId int, @TargetStoreId int, @UtcOffset int

--AS

--BEGIN
--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
--DECLARE @CreatedDate datetime
--SET @CreatedDate = (select dateDrop Column(HOUR,@UtcOffset,min(gt_date))from EXT_guntrans)

--DECLARE @UpdatedDate datetime
--SET @UpdatedDate = (select DATEDrop Column(HOUR,@UtcOffset,max(gt_date))from EXT_guntrans)

--SELECT * FROM (

--Select ROW_NUMBER() over (partition by GunLog_PK order by gt_pk desc) as Num1,
--       ISNULL(@CreatedDate,DATEDrop Column(HOUR,@UtcOffset,GETDATE())) as CreatedDate,
--       1 as CreatedBy,
--       ISNULL(@UpdatedDate,DATEDrop Column(HOUR,@UtcOffset,GETDATE())) as UpdatedDate,
--       u.UserId as UpdatedBy,
--       @TargetStoreId as StoreID,
--       it.InventoryItemId as InventoryItemId,
--       Cast(LTRIM(RTRIM(GunLogNum)) as nvarchar(50) ) as LogNumber,

--       Cast ( PICKDATE as datetime ) as PickDate,
--       iif(gt.gt_type = 'HOLD' and PICKDATE is null,1,0) as IsPlaceInHoldPeriod, -- todo revisit if gt_type='Hold' exists and PickDate in NULL
--       Cast ( TRANSNUM as nvarchar(100) ) as ATFNumber,
--       Cast ( NICSTN as nvarchar(250) ) as NICSNumber,
--       Cast ( RTRIM(LTRIM(g.COMMENT1)) as nvarchar(250) ) as Comments1,
--       Cast ( RTRIM(LTRIM(g.COMMENT2)) as nvarchar(250) ) as Comments2,
--       Cast ( GunLog_PK as int ) as SourceId

--from EXT_gunlog g
--inner join EXT_guntrans gt
--on gt.invnum = g.INVNUM
--inner join CEXT_InventoryItem it
--on it.sourceinventorynumber = g.INVNUM
--left join CEXT_User u
--on u.SourceId = g.LastUpdatedUSR_ID
--where it.StoreId=@TargetStoreId and g.STO_PK = @SourceStoreId
--)q where q.Num1 = 1

--END
--GO
/****** Object:  StoredProcedure [dbo].[GunlogHistoryExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO

---- =============================================
---- Author:		Syed Mohsin
---- Create date: <9/1/2016>
---- Description:	<Extracts gun log history>
---- Sample Call: EXEC [dbo].[GunlogHistoryExtract] 8 , 2 , 5
---- =============================================
--CREATE Proc [dbo].[GunlogHistoryExtract]
--@SourceStoreId int , @TargetStoreId int, @UtcOffset int

--AS
--BEGIN
--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

--Select
--       dateDrop Column(HOUR,@UtcOffset,gt_date) as CreatedDate,
--       1 as CreatedBy,
--       dateDrop Column(HOUR,@UtcOffset,gt_date) as UpdatedDate,
--       1 as UpdatedBy,
--       @TargetStoreId as StoreID,

--       it.InventoryItemId as InventoryItemId,
--       gt.GunLogTypeId as GunLogTypeId,
--       Cast ( comment1 as nvarchar(MAX) ) as Comment




--from EXT_guntrans g
--	inner join CEXT_InventoryItem it
--	on it.sourceinventorynumber = g.INVNUM
--	inner join CEXT_GunLogType gt
--		on gt.[Type] = CASE g.gt_type
--						WHEN 'REPAIR RET' THEN 'Repair Return'
--						WHEN 'REPAWN' THEN 'Pawn'
--						WHEN 'SOLD' THEN 'Sale'
--						WHEN 'VOID SALE' THEN 'Void'
--						ELSE g.gt_type
--						END

--	where g.sto_pk = @SourceStoreId and it.StoreId = @TargetStoreId
--END
--GO
/****** Object:  StoredProcedure [dbo].[InsertMasterUser]    Script Date: 6/13/2018 9:39:35 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE Proc [dbo].[InsertMasterUser]
@SourceStoreId int ,@TargetStoreId int , @CompanyId int, @Password nvarchar(150)

AS

BEGIN
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @firstuser int
SET @firstuser = (Select Min(UserId) from CEXT_User )

INSERT INTO CEXT_User ([UserName]
      ,[Password]
      ,[CreatedDate]
      ,[CreatedBy]
      ,[FirstName]
      ,[LastName]
      ,[StartDate]
      ,[EndDate]
      ,[MaxTransactionAmountBuy]
      ,[MaxTransactionAmountPawn]
      ,[MaxTransactionAmountSale]
      ,[SaleDiscount]
      ,[StatusId]
	  ,[IsDeleted]
      ,[IsLoggedIn]
      ,[CompanyId]
      ,[StoreId]
      ,[Comment]
      ,[IsRequiredChangePassword]
      ,[IsDefaultUser]
      ,[CultureId]
      ,[IsUserMigrated]
	  ,SourceId) OUTPUT Inserted.UserId

select
	cast (iif(s.USR_LANID =t.UserName, cast(s.USR_LANID as varchar(36)) +'_'+cast(s.[USR_STORE] as varchar(10)),s.usr_LANID) as nvarchar(50)) as username,
	@Password as Password, --Drop Column in package
	GetDate() as CreatedDate,
	@firstuser as CreatedBy, -- Drop Column in package
	cast( USR_FNAME  as nvarchar(100)) as FirstName,
	cast(USR_LNAME	as nvarchar(100)) as	LastName,
	USR_STARTDATE	as StartDate,
	USR_TERMINATE	as EndDate	,
	CAST(iif(BuyLimit=999999.99,9999999999.99,BuyLimit) as decimal(12,2))		as MaxTransactionAmountBuy,
	CAST(iif(PawnLimit=999999.99,9999999999.99,PawnLimit) as decimal(12,2))		as MaxTransactionAmountPawn,
	CAST(iif(OptionLimit=999999.99,9999999999.99,OptionLimit) as decimal(12,2))		as MaxTransactionAmountSale,
	USR_DISCOUNT	as SaleDiscount,
	0		as StatusId,
	0 as IsDeleted,
	0			as IsLoggedIn,
	@CompanyId  as CompanyId,
	@TargetStoreId		as StoreId,
	cast(USR_NOTES	as nvarchar(200))		as Comment,
	1			as IsRequiredChangePassword,
	0			as IsDefaultUser,
	1			as CultureId,
	1			as IsUserMigrated,
	USR_ID	as SourceId

	from EXT_users s
		left outer join CEXT_User t
			on s.USR_LANID =t.UserName
		where USR_STORE = @SourceStoreId AND s.USR_LANID = '***'

END
GO
/****** Object:  StoredProcedure [dbo].[InventoryItemExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO

---- =============================================
---- Author:		Syed Mohsin
---- Create date: <8/22/2016>
---- Description:	<Extracts Inventory items info >
---- Sample Call: EXEC [dbo].[InventoryItemExtract] 1 , 371 , 5

---- Modified By: Syed Mohsin
---- Modified date: <12/21/2016>
---- Comments: IsInventory checks for statuscodes Drop Columned

---- Modified By: Syed Mohsin
---- Modified date: <01/19/2017>
---- Comments: From customer/vendor logic updated BUG# 7242

---- Modified By: Syed Mohsin
---- Modified date: <01/23/2017>
---- Comments: IsDeleted Migration rule updated and TitleLoan PawnItems removed.

---- Modified By: Syed Mohsin
---- Modified date: <02/14/2017>
---- Comments: From customer/vendor logic updated again. Firearm rule applies on all items rather just Pawn items

---- Modified By: Syed Mohsin
---- Modified date: <02/21/2017>
---- Comments: From customer logic updated. If cus_fk does not have value then check for consignor

---- Modified By: Syed Mohsin
---- Modified date: <02/22/2017>
---- Comments: From customer logic updated. Customer can only exist if FireArm item. Also if ticketnum = 0 then ignore Cus_Fk and use CONSIGNOR

---- Modified By: Syed Mohsin
---- Modified date: <03/07/2017>
---- Comments: Drop Columned IsInventory check for Status B
---- =============================================

--CREATE Proc [dbo].[InventoryItemExtract]
--@StoreID int , @TargetStoreId int, @UtcTime int
--as
--BEGIN
--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

--DECLARE @SequenceNum int
--SET @SequenceNum = (SELECT ISNULL(MAX(SequenceNumber),0) FROM CEXT_SystemNumber WHERE PermissionKey = 'InventoryItem' AND StoreId = @TargetStoreId)
----Following Statements are to get LookupTypeIds for columns that needs to be looked up from LookupValue

--DECLARE @lookupTypeIDItem int
--SET @lookupTypeIDItem = (SELECT LookupTypeId from CEXT_LookupType
--							where LookupTypeName='Item')

--DECLARE @lookupTypeIDJewel int
--SET @lookupTypeIDJewel = (SELECT LookupTypeId from CEXT_LookupType
--							where LookupTypeName='Jewelry Info')

--DECLARE @lookupTypeIDGun int
--SET @lookupTypeIDGun = (SELECT LookupTypeId from CEXT_LookupType
--							where LookupTypeName='Gun Info Codes')

--DECLARE @lookupTypeIDBin int
--SET @lookupTypeIDBin = (SELECT LookupTypeId from CEXT_LookupType
--							where LookupTypeName='Bin')

--DECLARE @lookupTypeIDBrand int
--	  SET @lookupTypeIDBrand = (SELECT LookupTypeId from CEXT_LookupType
--							where LookupTypeName='Brand' );

--DECLARE @lookupTypeIDColor int
--	  SET @lookupTypeIDColor = (SELECT LookupTypeId from CEXT_LookupType
--							where LookupTypeName='Color' AND ParentLookupTypeId=@lookupTypeIDItem);

--DECLARE @lookupTypeIDCondition int
--	  SET @lookupTypeIDCondition = (SELECT LookupTypeId from CEXT_LookupType
--							where LookupTypeName='Condition' AND ParentLookupTypeId=@lookupTypeIDItem);

--DECLARE @lookupTypeIDmetal int
--	  SET @lookupTypeIDmetal = (SELECT LookupTypeId from CEXT_LookupType
--							where LookupTypeName='Metal' AND ParentLookupTypeId=@lookupTypeIDJewel);

--DECLARE @lookupTypeIDkarat int
--	  SET @lookupTypeIDkarat = (SELECT LookupTypeId from CEXT_LookupType
--							where LookupTypeName='Karat' AND ParentLookupTypeId=@lookupTypeIDJewel);

--DECLARE @lookupTypeIDgender int
--	  SET @lookupTypeIDgender = (SELECT LookupTypeId from CEXT_LookupType
--							where LookupTypeName='Gender' AND ParentLookupTypeId=@lookupTypeIDJewel);

--DECLARE @lookupTypeIDstyle int
--	  SET @lookupTypeIDstyle = (SELECT LookupTypeId from CEXT_LookupType
--							where LookupTypeName='Style' AND ParentLookupTypeId=@lookupTypeIDJewel);

--DECLARE @lookupTypeIDsize int
--	  SET @lookupTypeIDsize = (SELECT LookupTypeId from CEXT_LookupType
--							where LookupTypeName ='Size / Length' AND ParentLookupTypeId=@lookupTypeIDJewel);

--DECLARE @lookupTypeIDaction int
--	  SET @lookupTypeIDaction = (SELECT LookupTypeId from CEXT_LookupType
--							where LookupTypeName ='Action' AND ParentLookupTypeId=@lookupTypeIDGun);

--DECLARE @lookupTypeIDfinish int
--	  SET @lookupTypeIDfinish = (SELECT LookupTypeId from CEXT_LookupType
--							where LookupTypeName ='Finish' AND ParentLookupTypeId=@lookupTypeIDGun);

--DECLARE @lookupTypeIDbarrel int
--	  SET @lookupTypeIDbarrel = (SELECT LookupTypeId from CEXT_LookupType
--							where LookupTypeName ='Barrel' AND ParentLookupTypeId=@lookupTypeIDGun);

--DECLARE @lookupTypeIDcaliber int
--	  SET @lookupTypeIDcaliber = (SELECT LookupTypeId from CEXT_LookupType
--							where LookupTypeName ='Caliber' AND ParentLookupTypeId=@lookupTypeIDGun);

--DECLARE @lookupTypeIDimporter int
--	  SET @lookupTypeIDimporter = (SELECT LookupTypeId from CEXT_LookupType
--							where LookupTypeName ='Importer' AND ParentLookupTypeId=@lookupTypeIDGun);

--DECLARE @StoreCode int
--	  SET @StoreCode = (select StoreCode from CEXT_Store where StoreId = @TargetStoreId);

---- =============================================

---- Extracting data from source
--	  SELECT CAST(@StoreCode as nvarchar(50)) as InventoryNumber, -- StoreCode+##### will also be used for barcode
--			@SequenceNum as SequenceNumber,--Used for Incremental Values
--			CAST(i.INVNUM as nvarchar(50)) as SourceInventoryNumber,
--			it.ItemTypeId,
--			itb.ItemTypeBrandId as ItemTypeBrandId,
--			CAST(RTRIM(LTRIM(i.MODELNUM)) as nvarchar(30)) as Model,
--			CAST(RTRIM(LTRIM(i.SERIALNUM)) as nvarchar(50)) as SerialNumber,
--			CAST(RTRIM(LTRIM(i.OWNERNUM)) as nvarchar(50)) as OwnerNumber,
--			CAST(i.RESALEAMT as decimal(12,3)) as Resale,
--			lvcon.LookupValueId as ItemConditionid ,
--			lvcol.LookupValueId as ItemColorid ,
--			CAST(i.LOWSLPRICE as decimal(12,3)) as [Min],
--			CAST(RTRIM(LTRIM(i.DESCRIPT)) as nvarchar(500))  as Comment,
--			lvmet.LookupValueId as MetalId,
--			lvkar.LookupValueId as  KaratId,
--			CAST(j.[Weight] as decimal(9,3)) as JewelryWeight, --Possible Data loss
--			lvwgt.LookupValueId as JewelryWeightUnitId ,
--			lvgen.LookupValueId as JewelryGenderId,
--			lvsty.LookupValueId as JewelryStyleId,
--			lvsize.LookupValueId as JewelrySizeLengthId,
--			lvact.LookupValueId as FirearmActionId,
--			lvfin.LookupValueId as FirearmFinishId,
--			lvbar.LookupValueId as FirearmBarrelsNumberId,
--			CAST(g.[Length] as nvarchar(20)) as FirearmLength,
--			lvcal.LookupValueId as FirearmCaliberGaugeId,
--			CAST(g.Condition as nvarchar(50)) as FirearmCondition,
--			lvimp.LookupValueId as FirearmImporterId,
--			st.StatusId as StatusId,
--			i.IsBulkItem,
--			iif(i.NEWITEM=0,0,1) as IsNewItem, -- Is Default value needed ?
--			i.SalesTaxEx as IsSalesTaxExempt,
--			en.EntityId as InventoryItemTypeSourceId,
--			iif(lvf.DESCRIPT='FIREARM',iif(c.CustomerId IS NULL, v.vendorId , c.customerid) ,v.vendorId)
--			 as InventoryItemSourceId, --Logic updated as per new business rules
--			CAST(RTRIM(LTRIM(i.UPC)) as nvarchar(14)) as UPC,
--			CAST(i.MAXQUANT as int) as MaxQuantity, -- Possible data Loss
--			CAST(i.REORDER as int) as ReOrderLevel, -- Possible data Loss
--			iif(i.DELETEDATE IS NOT NULL AND i.status = 'D',1,0) as IsDeleted, --IsDeleted Logic Updated
--			DATEDrop Column(HOUR,@UtcTime,iif(i.DateItemEntered IS NULL,getdate(),i.DateItemEntered)) as CreatedDate,
--			1 as CreatedBy,-- Drop Column in package
--			DATEDrop Column(HOUR,@UtcTime,iif(i.DELETEDATE IS NULL,i.DateItemEntered,i.DELETEDATE)) as UpdatedDate,
--			u.UserId as UpdatedBy,
--			@TargetStoreId as StoreId,
--			iif(st.StatusCode = 'B' OR st.StatusCode = 'P' OR st.StatusCode = 'S' OR st.StatusCode = 'V' OR st.StatusCode = 'U' OR st.StatusCode = 'E' OR i.status = 'H',0,1 ) as IsInventoryItem,
--			CAST(iif(i.DELETEDATE IS NOT NULL AND i.status = 'D',RTRIM(LTRIM(i.DELETECOM)),NULL) as nvarchar(250)) as ReasonForDelete, --IsDeleted Logic Updated
--			lvbin.LookupValueId as BinNumberId,
--			i.REPAWNED as IsReused,
--			--SplitFromItems_ID Update later in Package
--			iif(i.TICKETNUM > 0 AND i.[STATUS] = 'I',1,0) as IsForfeit,
--			i.ITEMS_ID as SourceId

--  FROM EXT_items i --Main source table

--  LEFT JOIN EXT_Detail_G g --Source table for gun information
--  ON i.Sto_Pk = g.Sto_FK AND i.ITEMS_PK = g.Items_FK

--  LEFT JOIN EXT_Detail_J j --Source table for Jewelery information
--  ON i.Sto_Pk = j.Sto_FK AND i.ITEMS_PK = j.Items_FK

--  LEFT JOIN EXT_Level5 l5
--  ON l5.lv5_PK = i.LEVEL5_FK

--    LEFT JOIN CEXT_LookupValue lv  -- IS THIS CONDITION RIGHT?
--  ON lv.LookupTypeId = @lookupTypeIDBrand AND lv.Value = LTRIM(RTRIM(l5.DESCRIPT)) --AND lv.StoreId = @TargetStoreId

--  --Joins to get lookupValueId for columns that needs to be looked up
--  LEFT JOIN CEXT_LookupValue lvcon
--  ON i.Condition != 1 AND lvcon.LookupTypeId = @lookupTypeIDCondition AND lvcon.SourceId = i.Condition

--  LEFT JOIN CEXT_LookupValue lvcol
--  ON i.Color != 1 AND lvcol.LookupTypeId = @lookupTypeIDColor AND lvcol.SourceId = i.Color

--  LEFT JOIN CEXT_LookupValue lvmet
--  ON j.Metal_FK != 1 AND lvmet.LookupTypeId = @lookupTypeIDmetal AND lvmet.SourceId = j.Metal_FK

--  LEFT JOIN CEXT_LookupValue lvkar
--  ON j.Karat_FK != 1 AND lvkar.LookupTypeId = @lookupTypeIDkarat AND lvkar.SourceId = j.Karat_FK

--  LEFT JOIN CEXT_LookupValue lvwgt
--  ON j.WgtUnit != '' AND lvwgt.Value = CASE WHEN (j.WgtUnit = 'G')
--											Then 'Grams'
--											WHEN (j.WgtUnit = 'O')
--											Then 'Ounces'
--											WHEN (j.WgtUnit = 'D')
--											Then 'DWT'
--										END


--  LEFT JOIN CEXT_LookupValue lvgen
--  ON j.Gender_FK != 1 AND lvgen.LookupTypeId = @lookupTypeIDgender AND lvgen.SourceId = j.Gender_FK

--  LEFT JOIN CEXT_LookupValue lvsty
--  ON j.Style_FK != 1 AND lvsty.LookupTypeId = @lookupTypeIDstyle AND lvsty.SourceId = j.Style_FK

--  LEFT JOIN CEXT_LookupValue lvsize
--  ON j.Sizelen_FK != 1 AND lvsize.LookupTypeId = @lookupTypeIDsize AND lvsize.SourceId = j.Sizelen_FK

--  LEFT JOIN CEXT_LookupValue lvact
--  ON g.Action_FK != 1 AND lvact.LookupTypeId = @lookupTypeIDaction AND lvact.SourceId = g.Action_FK

--  LEFT JOIN CEXT_LookupValue lvfin
--  ON g.Finish_FK != 1 AND lvfin.LookupTypeId = @lookupTypeIDfinish AND lvfin.SourceId = g.Finish_FK

--  LEFT JOIN CEXT_LookupValue lvbar
--  ON g.Barrel_FK != 1 AND lvbar.LookupTypeId = @lookupTypeIDbarrel AND lvbar.SourceId = g.Barrel_FK

--  LEFT JOIN CEXT_LookupValue lvcal
--  ON g.Caliber_FK != 1 AND lvcal.LookupTypeId = @lookupTypeIDcaliber AND lvcal.SourceId = g.Caliber_FK

--  LEFT JOIN CEXT_LookupValue lvimp
--  ON g.ImporterFK != 1 AND lvimp.LookupTypeId = @lookupTypeIDimporter AND lvimp.SourceId = g.ImporterFK

--  LEFT JOIN CEXT_LookupValue lvbin
--  ON lvbin.LookupTypeId = @lookupTypeIDBin AND lvbin.Value = i.BIN AND lvbin.StoreId = @TargetStoreId
--  -- =============================================

--  --To get the status translated values
--  LEFT JOIN common.SourceTargetKeyMapping map
--  ON map.SourceTable='ItemStatus' AND map.SourceKey = CASE i.[STATUS]
--														WHEN 'L' THEN 'I'
--														WHEN 'S' THEN 'I'
--														ELSE i.[STATUS]
--														END

--  LEFT JOIN CEXT_Status st
--  ON map.TargetKey = st.StatusCode

--  --Join from user table to get TargetUserId for UpdatedBy column
--  LEFT JOIN CEXT_User u
--  ON u.SourceId = i.LastUpdatedUSR_ID

--  INNER JOIN CEXT_ItemType it
--  ON it.SourceId = CASE WHEN(LEVEL4_FK != 1) THEN LEVEL4_FK
--WHEN (LEVEL3_FK != 1) THEN LEVEL3_FK
--WHEN (LEVEL2_FK != 1) THEN LEVEL2_FK
--ELSE LEVEL1_FK END




-- LEFT JOIN EXT_Level1 lvf
-- ON lvf.lv1_pk = i.LEVEL1_FK and lvf.sto_pk=@StoreID

-- 	LEFT JOIN CEXT_ItemType it2
--	on it2.ItemTypeName = LTRIM(RTRIM(lvf.DESCRIPT)) and it2.ParentItemTypeId IS NULL

--    LEFT JOIN CEXT_ItemTypeBrand itb  -- IS THIS CONDITION RIGHT?
--  ON itb.BrandId = lv.LookupValueId and it2.ItemTypeId = itb.ItemTypeId

-- LEFT JOIN CEXT_Entity en
-- ON en.EntityName = CASE WHEN((i.CUS_FK != 0 AND i.CUS_FK != -1) OR (i.CONSIGNOR != 0 AND i.CONSIGNOR != -1 AND i.CONSIGNOR != 1)) AND lvf.DESCRIPT = 'FIREARM' THEN 'Customer'
-- WHEN i.VENDOR != 0 AND i.VENDOR != -1 THEN 'Vendor'
-- ELSE '' END -- Condtion Updated as per new busines logics

-- left join EXT_cust cu
-- on cu.Cus_PK = CASE WHEN i.CUS_FK != -1 AND i.CUS_FK != 0 AND i.TICKETNUM != 0 THEN i.CUS_FK
--WHEN (i.CUS_FK = -1 OR i.CUS_FK = 0) AND CONSIGNOR != 0 AND CONSIGNOR != -1 AND CONSIGNOR != 1 THEN CONSIGNOR
--END -- Drop Columned consignor field in join if Cus_Fk does not have the value.

-- LEFT JOIN CEXT_Customer c
-- ON c.SourceId = cu.Cus_id

-- left join EXT_vend ve
-- on ve.VEN_PK = i.VENDOR

-- LEFT JOIN CEXT_Vendor v
-- ON v.SourceId = ve.VEN_id


--LEFT JOIN EXT_pawn p --Join for TitleLoan Pawns
--ON p.PWN_id = i.PWN_id

--WHERE i.Sto_Pk = @StoreId and (TitleLoan = 0 OR TitleLoan IS NULL) --Removing TitleLoan PawnItems

--END
--GO
/****** Object:  StoredProcedure [dbo].[InventoryItemLotExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO

---- =============================================
---- Author:		Syed Mohsin
---- Create date: <8/22/2016>
---- Description:	<Extracts Data for table InventoryItemLot >
---- Sample Call: EXEC [dbo].[InventoryItemLotExtract] 8 , 190 , 5

---- Updated By: Syed Mohsin
---- Update date: <9/27/2016>
---- Comments: StatusId hardcoded 28 which means LotIn.

---- Updated By: Syed Mohsin
---- Update date: <12/19/2016>
---- Comments: InventoryItemLotNumber's logic changed to simple increment.
---- =============================================

--CREATE Proc [dbo].[InventoryItemLotExtract]
--@SourceStoreID int , @TargetStoreId int, @UtcTime int
--as
--BEGIN

--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

--DECLARE @LotNumber int
--SET @LotNumber = (SELECT ISNULL(max(SequenceNumber),0) FROM CEXT_SystemNumber where PermissionKey = 'InventoryItemlot' and StoreId=@TargetStoreId)

--SELECT
--	it.InventoryItemId,
--	28 as StatusId ,
--	CAST(@LotNumber + ROW_NUMBER() over (order by (select NULL)) as nvarchar(50)) as InventoryItemLotNumber, -- (Auto-Incremented)
--	CAST(i.OnHand as decimal(12,3)) as Quantity,
--	CAST(AMOUNT as decimal(12,3)) as Cost,
--	DATEDrop Column(HOUR,@UtcTime,iif(i.DateItemEntered IS NULL,getdate(),i.DateItemEntered)) as CreatedDate,
--	DATEDrop Column(HOUR,@UtcTime,iif(i.DateItemEntered IS NULL,getdate(),i.DateItemEntered)) as DateIn,
--	--DATEDrop Column(HOUR,@UtcTime,getdate()) as CreatedDate,
--	1 as CreatedBy, -- Drop Column in package
--	it.UpdatedDate as UpdatedDate,
--	it.UpdatedBy as UpdatedBy

-- FROM EXT_items i

--INNER JOIN CEXT_InventoryItem it
--	ON i.Items_ID = it.SourceId AND it.StoreId = @TargetStoreId

--WHERE i.Sto_Pk = @SourceStoreId

--END
--GO
/****** Object:  StoredProcedure [dbo].[InventoryItemLotOutScrapped]    Script Date: 6/13/2018 9:39:35 AM ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO

---- =============================================
---- Author:		Syed Mohsin
---- Create date: <3/10/2017>
---- Description:	<Extracts scrapped items for table InventoryItemLot for lotout >
---- Sample Call: EXEC [dbo].[InventoryItemLotOutScrapped] 1818

---- Updated By: Syed Mohsin
---- Update date: <03/22/2017>
---- Comments: Jewelry items are not lotout
---- =============================================

--CREATE Proc [dbo].[InventoryItemLotOutScrapped]
--@TargetStoreId int
--as
--BEGIN

--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

--DECLARE @LotNumber int;
--  SET @LotNumber=(SELECT MAX(CAST(InventoryItemLotNumber as int)) FROM CEXT_InventoryItemLot il INNER JOIN CEXT_InventoryItem it
--  ON it.InventoryItemId = il.InventoryItemId
-- where StoreId=@TargetStoreId)

--DECLARE @JewelryTypeid int;
--SET @JewelryTypeid = ( SELECT ItemTypeId from CEXT_ItemType where ItemTypeName='JEWELRY' and ParentItemTypeId is null
--)


--select
--SourceInventoryItemId as InventoryItemId,
--29 as StatusId,
--CAST(@LotNumber + ROW_NUMBER() over (order by (select NULL)) as nvarchar(50)) as InventoryItemLotNumber, -- (Auto-Incremented)
--CAST(Quantity*-1 as decimal(12,3)) as Quantity,
----[Quantity] * -1 as Quantity,
--Cost,
--DateIn,
--il.CreatedDate,
--il.CreatedBy
--from CEXT_InventoryItemLot il
--inner join CEXT_InventoryItem it on it.InventoryItemId = il.InventoryItemId and it.StoreId = @TargetStoreId
--inner join CEXT_InventoryItem its on its.InventoryItemId = il.SourceInventoryItemId
--left join CEXT_ItemType j4 on j4.ItemTypeId = its.ItemTypeId
--left join CEXT_itemtype j3 on j3.ItemTypeId = CASE WHEN j4.ParentItemTypeId is null THEN its.ItemTypeId ELSE j4.ParentItemTypeId END
--left join CEXT_itemtype j2 on j2.ItemTypeId = CASE WHEN j3.ParentItemTypeId is null THEN its.ItemTypeId ELSE j3.ParentItemTypeId END
--left join CEXT_itemtype j1 on j1.ItemTypeId = CASE WHEN j2.ParentItemTypeId is null THEN its.ItemTypeId ELSE j2.ParentItemTypeId END
--where SourceInventoryItemId is not null and j1.ItemTypeId != @JewelryTypeid and j2.ItemTypeId != @JewelryTypeid and j3.ItemTypeId != @JewelryTypeid and j4.ItemTypeId != @JewelryTypeid --and j1.ItemTypeId is null
--and
--its.ItemTypeId not in (select ItemTypeId from CoEXT_ItemType where ParentItemTypeId = @JewelryTypeid)

--END
--GO
/****** Object:  StoredProcedure [dbo].[InventoryItemLotScrapped]    Script Date: 6/13/2018 9:39:35 AM ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO

---- =============================================
---- Author:		Syed Mohsin
---- Create date: <3/10/2017>
---- Description:	<Extracts scrapped items for table InventoryItemLot >
---- Sample Call: EXEC [dbo].[InventoryItemLotScrapped] 8 , 1818 , 5
---- =============================================

--CREATE Proc [dbo].[InventoryItemLotScrapped]
--@SourceStoreID int , @TargetStoreId int, @UtcTime int
--as
--BEGIN

--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

--DECLARE @LotNumber int;
--  SET @LotNumber=(SELECT MAX(CAST(InventoryItemLotNumber as int)) FROM CEXT_InventoryItemLot il INNER JOIN CEXT_InventoryItem it
--  ON it.InventoryItemId = il.InventoryItemId
--  where StoreId=@TargetStoreId)

--select
--blkIt.InventoryItemId as InventoryItemId,
--30 as statusid,
--	CAST(@LotNumber + ROW_NUMBER() over (order by (select NULL)) as nvarchar(50)) as InventoryItemLotNumber, -- (Auto-Incremented)
--	CAST(i.Quantity as decimal(12,3)) as Quantity,
--	CAST(i.COST as decimal(12,3)) as Cost,
--	DATEDrop Column(HOUR,@UtcTime,iif(i.DateIn IS NULL,getdate(),i.DateIn)) as CreatedDate,
--	DATEDrop Column(HOUR,@UtcTime,iif(i.DateIn IS NULL,getdate(),i.DateIn)) as DateIn,
--	--DATEDrop Column(HOUR,@UtcTime,getdate()) as CreatedDate,
--	scrIt.CreatedBy as CreatedBy, -- Drop Column in package
--	scrIt.InventoryItemId as SourceInventoryItemId,
--	scrIt.InventoryItemId as SourceInventoryItemIdVersion

-- from EXT_inv i
--inner join EXT_items it on it.INVNUM = i.scrapnum and i.STATUS='J'
--inner join CEXT_InventoryItem blkIt on blkIt.SourceId = i.items_id
--inner join CEXT_InventoryItem scrIt on scrIt.SourceId = it.Items_ID
--where it.Sto_Pk=@SourceStoreID

--END
--GO
/****** Object:  StoredProcedure [dbo].[InventoryPicturesExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO

---- =============================================
---- Author:		Syed Mohsin
---- Create date: <8/23/2016>
---- Description:	<Extracts Stone Info for Inventory Items>
---- Sample Call: EXEC [dbo].[InventoryPicturesExtract] 2 , 133
---- =============================================

--CREATE Proc [dbo].[InventoryPicturesExtract]
--@SourceStoreId int, @TargetStoreId int
--as
--BEGIN

--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

--SELECT
--InventoryItemId,
--PictureTakenDate,
--ObjectImage,
--PictureSourceId
-- FROM
--(
--SELECT
--	it.InventoryItemId as InventoryItemId,
--	itemPic_Img,
--	CAST(CAST(itemPic_img2 as varchar(8000)) as image) as itemPic_img2,
--	itemPic_Img_Date as PictureTakenDate,
--	ps.PictureSourceId as PictureSourceId
--FROM PEXT_itemPics ip
--INNER JOIN EXT_items i
--ON ip.itemPic_PK = i.itemPic_FK AND ip.sto_PK = i.Sto_Pk

--INNER JOIN CEXT_InventoryItem it
--ON it.SourceId = i.Items_ID AND it.StoreId = @TargetStoreId

--INNER JOIN CEXT_PictureSource ps
--ON ps.PictureSourceName = iif(i.PWN_id IS NULL, 'Item Picture','Inventory')
--WHERE ip.sto_PK = @SourceStoreId
--) a
--UNPIVOT(
--ObjectImage for ObjectValue IN (itemPic_Img,itemPic_img2)
--)unpiv

--UNION ALL

--select
--	it.InventoryItemId as InventoryItemId,
--	ip.IMP_Img_Date as PictureTakenDate,
--	ip.IMP_Img as itemPic_Img,
--	ps.PictureSourceId as PictureSourceId

--from  PEXT_ItemMorePics ip

--INNER JOIN EXT_items i
--ON i.Items_ID = ip.ITEMS_id AND i.Sto_Pk = @SourceStoreId

--INNER JOIN CEXTInventoryItem it
--ON it.SourceId = ip.ITEMS_id AND it.StoreId = @TargetStoreId

--INNER JOIN CEXT_PictureSource ps
--ON ps.PictureSourceName = iif(i.PWN_id IS NULL, 'Item Picture','Inventory')

--END
--GO
/****** Object:  StoredProcedure [dbo].[InventoryStoneExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO

---- =============================================
---- Author:		Syed Mohsin
---- Create date: <8/23/2016>
---- Description:	<Extracts Stone Info for Inventory Items>
---- Sample Call: EXEC [dbo].[InventoryStoneExtract] 8 , 182
---- =============================================

--CREATE Proc [dbo].[InventoryStoneExtract]
--@SourceStoreId int, @TargetStoreId int
--as
--BEGIN

--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
--DECLARE @StoneType int =(
--select LookupTypeId from CEXT_LookupType where LookupTypeName='Stone Info Codes'
--)
--DECLARE @StType int =(
--select LookupTypeId from CEXT_LookupType where LookupTypeName='Type' and ParentLookupTypeId=@StoneType
--)

--DECLARE @StShape int =(
--select LookupTypeId from CEXT_LookupType where LookupTypeName='Shape' and ParentLookupTypeId=@StoneType
--)

--DECLARE @StColor int =(
--select LookupTypeId from CEXT_LookupType where LookupTypeName='Color' and ParentLookupTypeId=@StoneType
--)

--DECLARE @StClarity int =(
--select LookupTypeId from CEXT_LookupType where LookupTypeName='Clarity' and ParentLookupTypeId=@StoneType
--)

--SELECT
-- 	Inv.InventoryItemId as InventoryItemId ,
--	CAST(NUMSTONE as int) as Quantity,
--	lvtyp.LookupValueId as StoneTypeId ,
--	lvshape.LookupValueId as StoneShapeId ,
--	CAST(CARAT as decimal(4,2)) as Carat,
--	lvcol.LookupValueId as StoneColorId ,
--	CAST(s.[WEIGHT] as decimal(4,2)) as [Weight],
--	CAST([LENGTH] as decimal(4,2)) as [Length],
--	CAST(WIDTH as decimal(4,2)) as [Width],
--	lvtran.LookupValueId as StoneClarityId ,
--	0 as IsDeleted,
--	getdate() as CreatedDate,
--	1 as CreatedBy,
--	getdate() as UpdatedDate,
--	u.UserId as UpdatedBy,
--	STO_id as SourceId

--FROM EXT_stones s

--INNER JOIN EXT_Detail_J j
--ON s.JDT_FK = j.JDT_PK AND j.Sto_FK = s.Sto_FK

----Join with items table to get item id
--INNER JOIN EXT_items i
--ON i.ITEMS_PK = j.Items_FK AND i.Sto_Pk = j.Sto_FK

--INNER JOIN CEXT_InventoryItem inv
--ON inv.SourceId = i.Items_ID AND inv.StoreId = @TargetStoreId

----Joins with LookupValue
--LEFT JOIN CEXT_LookupValue lvtyp
--ON lvtyp.SourceId = s.TYPSTONEFK and lvtyp.LookupTypeId=@StType

--LEFT JOIN CEXT_LookupValue lvshape
--ON lvshape.SourceId = s.SHAPE_FK and lvtyp.LookupTypeId=@StShape

--LEFT JOIN CEXT_LookupValue lvcol
--ON lvcol.SourceId = s.COLOR_FK and lvtyp.LookupTypeId=@StColor

--LEFT JOIN CEXT_LookupValue lvtran
--ON lvtran.SourceId = s.TRANSLUCFK and lvtyp.LookupTypeId=@StClarity

----Join target user table to get UserId for UpdateBy column
--LEFT JOIN CEXT_User u
--ON u.SourceId = s.LastUpdatedUSR_ID

--END
--GO
/****** Object:  StoredProcedure [dbo].[ItemTypeBrandsExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO

---- =============================================
---- Author:		Nadeem Akhtar
---- Create date: <8/12/2016>
---- Description:	<Extracts data for Brand Items>
---- Sample Call: EXEC [dbo].[ItemTypeBrandsExtract] 1 , 1831

---- Updated By: Syed Mohsin
---- Update date: <8/16/2016>
---- Comments: Corrected mapping for columns and Drop Columned join conditions to meet the design logic.
----			 Drop Columned source and target store id as parameter.

---- Updated By: Syed Mohsin
---- Update date: <8/25/2016>
---- Comments: Updated join from inner to left with User table.
---- =============================================

--CREATE Proc [dbo].[ItemTypeBrandsExtract]
--@SourceStoreId int, @TargetStoreId int

--AS
--BEGIN

--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

--	  DECLARE @lookupTypeID int
--	  SET @lookupTypeID = (SELECT LookupTypeId from CEXT_LookupType
--							where LookupTypeName='Brand')
--	SELECT * FROM (
--	  SELECT
--		  lv.LookupValueId,
--		  it.ItemTypeId,
--		  getdate() as CreatedDate,
--		  1 as CreatedBy, --Drop Column in package
--		  getdate() as UpdatedDate,
--		  u.UserId as UpdatedBy,
--		  itb.ItemTypeBrandId,
--		  ROW_NUMBER() over(partition by lv1_parent,LTRIM(RTRIM(l5.DESCRIPT)) order by lv5_PK) num1
--	  from EXT_Level5 l5

--	  INNER JOIN CEXT_LookupValue lv ON
--	   RTRIM(LTRIM(l5.DESCRIPT)) = lv.Value
--	   AND lv.LookupTypeId = @lookupTypeID
--	   --AND lv.StoreId = @TargetStoreId
--	   AND l5.sto_pk = @SourceStoreId

--	  INNER JOIN EXT_Level1 l1 ON
--	  l1.sto_pk = @SourceStoreId
--	  AND l1.lv1_pk = l5.LV1_PARENT

--	  INNER JOIN CEXT_ItemType it ON
--	  --l5.LV1_PARENT = it.SourceId AND
--	  RTRIM(LTRIM(l1.DESCRIPT)) = it.ItemTypeName
--	  AND it.ParentItemTypeId IS NULL
--	  --AND l5.sto_pk = @SourceStoreId

--	  LEFT JOIN CEXT_ItemTypeBrand itb ON
--	  itb.ItemTypeId = it.ItemTypeId
--	  AND itb.BrandId = lv.LookupValueId

--	  left JOIN CEXT_User u ON
--	  l5.LastUpdatedUSR_ID = u.SourceId
--	  AND l5.sto_pk = @SourceStoreId
--	  AND u.StoreId = @TargetStoreId

--	  WHERE itb.ItemTypeBrandId IS NULL
--	  )q where q.num1=1
--END
--GO
/****** Object:  StoredProcedure [dbo].[ItemTypeLevel1RecordsToInsert]    Script Date: 6/13/2018 9:39:35 AM ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO

---- =============================================
---- Author:		Nadeem Akhtar
---- Create date: <8/11/2016>
---- Description:	<Extracts data for Level1 Lookup values>
---- Sample Call: EXEC [dbo].[ItemTypeLevel1RecordsToInsert] 8

---- Updated By: Syed Mohsin
---- Update date: <8/12/2016>
---- Comments: Corrected mapping for columns and Drop Columned join conditions to meet the design logic.
----			 Drop Columned Storeid parameter for Source StoreId

---- Updated By: Syed Mohsin
---- Update date: <8/25/2016>
---- Comments: Updated join from inner to left with User table.

---- Updated By: Syed Mohsin
---- Update date: <9/22/2016>
---- Comments: Drop Columned check to remove empty strings from extract.

---- Updated By: Syed Mohsin
---- Update date: <7/20/2017>
---- Comments: Drop Columned check if item type does not have a levelN but exists in items table.
---- =============================================

--CREATE proc [dbo].[ItemTypeLevel1RecordsToInsert]
--@StoreId int
--AS

--BEGIN
--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
--DECLARE @FireArm int, @Jewelry int, @Other int

--	select @FireArm=ItemCategoryId from CEXT_ItemType t
--			where value = 'Firearm'

--	select @Jewelry=ItemCategoryId from CEXT_ItemType t
--			where value = 'Jewelry'

--		select @Other=ItemCategoryId from CEXT_ItemType t
--			where value = 'Other'


--Select
--	Null as ParentItemTypeId,
--	ItemCategoryID=
--		case WHEN s.Descript LIKE '%Firearm%' THEN
--			@FireArm
--		 WHEN s.Descript LIKE '%Jewelry %' THEN
--			@Jewelry
--		ELSE
--			@Other
--		end,
--	ItemTypeName= cast(RTRIM(LTRIM(s.Descript)) as nvarchar(100)),
--	Value= cast(RTRIM(LTRIM(s.Descript)) as nvarchar(50)),
--	GetDate() as CreatedDate,
--	1 as CreatedBy, --Drop Column in package
--	GetDate() as UpdatedDate,
--	u.UserId as UpdatedBy,
--	0 as IsDeleted,
--	Lv1_pk as SourceID,
--	1 as Level,
--	Ln.Post2GunLog as IsPostToGunLog,
--	Ln.HandGun as IsHandGun,
--	Ln.HoldGunDays as HoldDays,
--	Ln.Min_Age as MinimumAge

--	from EXT_Level1 s

--	left outer join CEXT_ItemType t
--		on RTRIM(LTRIM(s.Descript)) = t.ItemTypeName
--			and t.ParentItemTypeId is null
--	left outer join EXT_Level_N LN
--		on s.Lv1_PK= Ln.Level_FK
--	left outer join (SELECT DISTINCT lv1_ID from EXT_items where Sto_Pk=@StoreId)q on q.lv1_ID = s.lv1_ID --Drop Columned for missing itemtypes in LevelN
--	left join CEXT_User u ON s.LastUpdatedUSR_ID = u.SourceId
--	where RTRIM(LTRIM(s.Descript)) != '' AND t.ItemTypeName is null AND s.sto_pk = @StoreId
--	AND (LN.LVN_id IS NOT NULL OR q.lv1_ID IS NOT NULL) --Also migrate if missing in LevelN but used in inventory

--	END
--GO
/****** Object:  StoredProcedure [dbo].[ItemTypeLevel1RepeatedRecordsToInsert]    Script Date: 6/13/2018 9:39:35 AM ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO

---- =============================================
---- Author:		Syed Mohsin
---- Create date: <8/04/2017>
---- Description:	<Extracts data for Level1 Repeating ItemTypes>
---- Sample Call: EXEC [dbo].[ItemTypeLevel1RepeatedRecordsToInsert] 1

---- =============================================

--CREATE proc [dbo].[ItemTypeLevel1RepeatedRecordsToInsert]
--@StoreId int
--AS

--BEGIN
--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
--DECLARE @FireArm int, @Jewelry int, @Other int

--	select @FireArm=ItemCategoryId from CEXT_ItemType t
--			where value = 'Firearm'

--	select @Jewelry=ItemCategoryId from CEXT_ItemType t
--			where value = 'Jewelry'

--		select @Other=ItemCategoryId from CEXT_ItemType t
--			where value = 'Other'


--Select
--	ROW_NUMBER() over (partition by RTRIM(LTRIM(s.Descript)) order by Lv1_pk) as NUM,
--	Null as ParentItemTypeId,
--	ItemCategoryID=
--		case WHEN s.Descript LIKE '%Firearm%' THEN
--			@FireArm
--		 WHEN s.Descript LIKE '%Jewelry %' THEN
--			@Jewelry
--		ELSE
--			@Other
--		end,
--	ItemTypeName= cast(RTRIM(LTRIM(s.Descript)) as nvarchar(100)),
--	Value= cast(RTRIM(LTRIM(s.Descript)) as nvarchar(50)),
--	GetDate() as CreatedDate,
--	1 as CreatedBy, --Drop Column in package
--	GetDate() as UpdatedDate,
--	u.UserId as UpdatedBy,
--	0 as IsDeleted,
--	Lv1_pk as SourceID,
--	1 as Level,
--	Ln.Post2GunLog as IsPostToGunLog,
--	Ln.HandGun as IsHandGun,
--	Ln.HoldGunDays as HoldDays,
--	Ln.Min_Age as MinimumAge

--	from EXT_Level1 s

--	INNER join CEXT_ItemType t
--		on RTRIM(LTRIM(s.Descript)) = t.ItemTypeName
--			and t.ParentItemTypeId is null and t.SourceId IS NOT NULL and t.SourceId != s.lv1_pk
--	left outer join EXT_Level_N LN
--		on s.Lv1_PK= Ln.Level_FK
--	left outer join (SELECT DISTINCT lv1_ID from EXT_items where Sto_Pk=@StoreId)q on q.lv1_ID = s.lv1_ID --Drop Columned for missing itemtypes in LevelN
--	left join CEXT_User u ON s.LastUpdatedUSR_ID = u.SourceId
--	where RTRIM(LTRIM(s.Descript)) != ''  AND s.sto_pk = @StoreId
--	AND (LN.LVN_id IS NOT NULL OR q.lv1_ID IS NOT NULL) --Also migrate if missing in LevelN but used in inventory

--	END
--GO
/****** Object:  StoredProcedure [dbo].[ItemTypeLevel2RecordsToInsert]    Script Date: 6/13/2018 9:39:35 AM ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO

---- =============================================
---- Author:		Nadeem Akhtar
---- Create date: <8/11/2016>
---- Description:	<Extracts data for Level2 Lookup values>
---- Sample Call: EXEC [dbo].[ItemTypeLevel2RecordsToInsert] 8

---- Updated By: Syed Mohsin
---- Update date: <8/12/2016>
---- Comments: Corrected mapping for columns and Drop Columned join conditions to meet the design logic.
----			 Drop Columned Storeid parameter for Source StoreId

---- Updated By: Syed Mohsin
---- Update date: <8/25/2016>
---- Comments: Updated join from inner to left with User table.

---- Updated By: Syed Mohsin
---- Update date: <9/22/2016>
---- Comments: Drop Columned check to remove empty string from extract.

---- Updated By: Syed Mohsin
---- Update date: <7/20/2017>
---- Comments: Drop Columned check if item type does not have a levelN but exists in items table.
---- =============================================

--CREATE proc [dbo].[ItemTypeLevel2RecordsToInsert]
--@StoreId int
--as

--BEGIN
--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

--	Select
--		p.ItemTypeId as ParentItemTypeId,
--		p.ItemCategoryId as ItemCategoryId,
--		ItemTypeName= cast(RTRIM(LTRIM(s.Descript)) as nvarchar(100)),
--		Value= cast(RTRIM(LTRIM(s.Descript)) as nvarchar(50)),
--		GetDate() as CreatedDate,
--		1 as CreatedBy, --Drop Column in package
--		GetDate() as UpdatedDate,
--		u.UserId as UpdatedBy,
--		0 as IsDeleted,
--		Lv2_pk as SourceID
--		--2 as [Level]

--		from EXT_Level2 s
--			inner join CEXT_ItemType p
--				on s.lv1_Parent = p.sourceID
--					--and p.[level]=1
--			left outer join CEXT_ItemType t
--				on RTRIM(LTRIM(s.Descript)) = t.ItemTypeName and t.ParentItemTypeId = p.ItemTypeId
--					--and t.[level]=2
--			left outer join EXT_Level_N n
--				on n.Level_FK = s.lv2_pk
--			left outer join (SELECT DISTINCT lv2_ID from EXT_items where Sto_Pk=@StoreId)q
--				on q.lv2_ID = s.lv2_ID --Drop Columned for missing itemtypes in LevelN
--			left join CEXT_User u
--				on s.LastUpdatedUSR_ID = u.SourceId
--		where RTRIM(LTRIM(s.Descript)) != '' AND t.ItemTypeName is null AND s.sto_pk = @StoreId
--				AND (n.LVN_id IS NOT NULL OR q.lv2_ID IS NOT NULL) --Also migrate if missing in LevelN but used in inventory
--END
--GO
/****** Object:  StoredProcedure [dbo].[ItemTypeLevel2RepeatedRecordsToInsert]    Script Date: 6/13/2018 9:39:35 AM ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO

---- =============================================
---- Author:		Syed Mohsin
---- Create date: <8/04/2017>
---- Description:	<Extracts data for Level2 repeating itemtypes>
---- Sample Call: EXEC [dbo].[ItemTypeLevel2RepeatedRecordsToInsert] 1
---- =============================================

--CREATE proc [dbo].[ItemTypeLevel2RepeatedRecordsToInsert]
--@StoreId int
--as

--BEGIN
--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

--	Select
--	ROW_NUMBER() over (partition by RTRIM(LTRIM(s.Descript)) order by Lv2_pk) as Num,
--		p.ItemTypeId as ParentItemTypeId,
--		p.ItemCategoryId as ItemCategoryId,
--		ItemTypeName= cast(RTRIM(LTRIM(s.Descript)) as nvarchar(100)),
--		Value= cast(RTRIM(LTRIM(s.Descript)) as nvarchar(50)),
--		GetDate() as CreatedDate,
--		1 as CreatedBy, --Drop Column in package
--		GetDate() as UpdatedDate,
--		u.UserId as UpdatedBy,
--		0 as IsDeleted,
--		Lv2_pk as SourceID
--		--2 as [Level]

--		from EXT_Level2 s
--			inner join CEXT_ItemType p
--				on s.lv1_Parent = p.sourceID
--					--and p.[level]=1
--			INNER join CEXT_ItemType t
--				on RTRIM(LTRIM(s.Descript)) = t.ItemTypeName and t.ParentItemTypeId = p.ItemTypeId
--				and t.SourceId IS NOT NULL and t.SourceId != s.lv2_pk
--					--and t.[level]=2
--			left outer join EXT_Level_N n
--				on n.Level_FK = s.lv2_pk
--			left outer join (SELECT DISTINCT lv2_ID from EXT_items where Sto_Pk=@StoreId)q
--				on q.lv2_ID = s.lv2_ID --Drop Columned for missing itemtypes in LevelN
--			left join CEXT_User u
--				on s.LastUpdatedUSR_ID = u.SourceId
--		where RTRIM(LTRIM(s.Descript)) != ''  AND s.sto_pk = @StoreId
--				AND (n.LVN_id IS NOT NULL OR q.lv2_ID IS NOT NULL) --Also migrate if missing in LevelN but used in inventory
--END
--GO
/****** Object:  StoredProcedure [dbo].[ItemTypeLevel3RecordsToInsert]    Script Date: 6/13/2018 9:39:35 AM ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO

---- =============================================
---- Author:		Nadeem Akhtar
---- Create date: <8/11/2016>
---- Description:	<Extracts data for Level3 Lookup values>
---- Sample Call: EXEC [dbo].[ItemTypeLevel3RecordsToInsert] 1

---- Updated By: Syed Mohsin
---- Update date: <8/12/2016>
---- Comments: Corrected mapping for columns and Drop Columned join conditions to meet the design logic.
----			 Drop Columned Storeid parameter for Source StoreId

---- Updated By: Syed Mohsin
---- Update date: <8/25/2016>
---- Comments: Updated join from inner to left with User table.

---- Updated By: Syed Mohsin
---- Update date: <9/22/2016>
---- Comments: Drop Columned check to remove empty string from extract.
---- =============================================

--CREATE proc [dbo].[ItemTypeLevel3RecordsToInsert]
--@StoreId int

--AS

--BEGIN
--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

--		Select
--			p.ItemTypeId as ParentItemTypeId,
--			p.ItemCategoryId as ItemCategoryId,
--			ItemTypeName= cast(RTRIM(LTRIM(s.Descript)) as nvarchar(100)),
--			Value= cast(RTRIM(LTRIM(s.Descript)) as nvarchar(50)),
--			GetDate() as CreatedDate,
--			1 as CreatedBy, --Drop Column in package
--			GetDate() as UpdatedDate,
--			u.UserId as UpdatedBy,
--			0 as IsDeleted,
--			Lv3_pk as SourceID


--			from EXT_Level3 s
--						--and t.level=3
--				inner join CEXT_ItemType p
--					on s.lv2_Parent = p.sourceID
--						--and p.level=2
--				left outer join CEXT_ItemType t
--					on RTRIM(LTRIM(s.Descript)) = t.ItemTypeName and t.ParentItemTypeId = p.ItemTypeId
--				left outer JOIN EXT_Level_N n
--					on n.Level_FK = s.lv3_pk
--				left outer join (SELECT DISTINCT lv3_ID from EXT_items where Sto_Pk=@StoreId)q
--					on q.lv3_ID = s.lv3_ID --Drop Columned for missing itemtypes in LevelN
--				left join CEXT_User u
--					on s.LastUpdatedUSR_ID = u.SourceId
--			where RTRIM(LTRIM(s.Descript)) != '' AND t.ItemTypeName is null AND s.sto_pk = @StoreId
--			AND (n.LVN_id IS NOT NULL OR q.lv3_ID IS NOT NULL) --Also migrate if missing in LevelN but used in inventory

--END
--GO
/****** Object:  StoredProcedure [dbo].[ItemTypeLevel3RepeatedRecordsToInsert]    Script Date: 6/13/2018 9:39:35 AM ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO

---- =============================================
---- Author:		Syed Mohsin
---- Create date: <8/04/2017>
---- Description:	<Extracts data for Level3 Repeating ItemTypes>
---- Sample Call: EXEC [dbo].[ItemTypeLevel3RepeatedRecordsToInsert] 1
---- =============================================

--CREATE proc [dbo].[ItemTypeLevel3RepeatedRecordsToInsert]
--@StoreId int

--AS

--BEGIN
--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

--		Select
--		ROW_NUMBER() over (partition by RTRIM(LTRIM(s.Descript)) order by Lv3_pk) as Num,
--			p.ItemTypeId as ParentItemTypeId,
--			p.ItemCategoryId as ItemCategoryId,
--			ItemTypeName= cast(RTRIM(LTRIM(s.Descript)) as nvarchar(100)),
--			Value= cast(RTRIM(LTRIM(s.Descript)) as nvarchar(50)),
--			GetDate() as CreatedDate,
--			1 as CreatedBy, --Drop Column in package
--			GetDate() as UpdatedDate,
--			u.UserId as UpdatedBy,
--			0 as IsDeleted,
--			Lv3_pk as SourceID


--			from Level3 s
--						--and t.level=3
--				inner join CEXT_ItemType p
--					on s.lv2_Parent = p.sourceID
--						--and p.level=2
--				INNER join CEXT_ItemType t
--					on RTRIM(LTRIM(s.Descript)) = t.ItemTypeName and t.ParentItemTypeId = p.ItemTypeId
--					and t.SourceId IS NOT NULL and t.SourceId != s.lv3_pk
--				left outer JOIN EXT_Level_N n
--					on n.Level_FK = s.lv3_pk
--				left outer join (SELECT DISTINCT lv3_ID from EXT_items where Sto_Pk=@StoreId)q
--					on q.lv3_ID = s.lv3_ID --Drop Columned for missing itemtypes in LevelN
--				left join CEXT_User u
--					on s.LastUpdatedUSR_ID = u.SourceId
--			where RTRIM(LTRIM(s.Descript)) != ''  AND s.sto_pk = @StoreId
--			AND (n.LVN_id IS NOT NULL OR q.lv3_ID IS NOT NULL) --Also migrate if missing in LevelN but used in inventory

--END
--GO
/****** Object:  StoredProcedure [dbo].[ItemTypeLevel4RecordsToInsert]    Script Date: 6/13/2018 9:39:35 AM ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO

---- =============================================
---- Author:		Nadeem Akhtar
---- Create date: <8/11/2016>
---- Description:	<Extracts data for Level4 Lookup values>
---- Sample Call: EXEC [dbo].[ItemTypeLevel4RecordsToInsert] 8

---- Updated By: Syed Mohsin
---- Update date: <8/12/2016>
---- Comments: Corrected mapping for columns and Drop Columned join conditions to meet the design logic.
----			 Drop Columned Storeid parameter for Source StoreId

---- Updated By: Syed Mohsin
---- Update date: <8/25/2016>
---- Comments: Updated join from inner to left with User table.

---- Updated By: Syed Mohsin
---- Update date: <9/22/2016>
---- Comments: Drop Columned check to remove empty string from extract.
---- =============================================

--CREATE proc [dbo].[ItemTypeLevel4RecordsToInsert]
--@StoreId int
--AS

--BEGIN
--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

--Select
--		p.ItemTypeId as ParentItemTypeId,
--		p.ItemCategoryId as ItemCategoryId,
--		ItemTypeName= cast(RTRIM(LTRIM(s.Descript)) as nvarchar(100)),
--		Value= cast(RTRIM(LTRIM(s.Descript)) as nvarchar(50)),
--		GetDate() as CreatedDate,
--		1 as CreatedBy, --Drop Column in package
--		GetDate() as UpdatedDate,
--		u.UserId as UpdatedBy,
--		0 as IsDeleted,
--		Lv4_pk as SourceID


--		from EXT_Level4 s

--					--and t.level=4
--			inner join CEXT_ItemType p
--				on s.lv3_Parent = p.sourceID
--					--and p.level=3
--			left outer join CEXT_ItemType t
--				on RTRIM(LTRIM(s.Descript)) = t.ItemTypeName and t.ParentItemTypeId = p.ItemTypeId
--			left outer JOIN EXT_Level_N n
--				ON n.Level_FK = s.lv4_PK
--			left outer join (SELECT DISTINCT lv4_ID from EXT_items where Sto_Pk=@StoreId )q
--					on q.lv4_ID = s.lv4_ID --Drop Columned for missing itemtypes in LevelN
--			left join CEXT_User u
--				on s.LastUpdatedUSR_ID = u.SourceId
--		where RTRIM(LTRIM(s.Descript)) != '' AND t.ItemTypeName is null AND s.sto_pk = @StoreId
--		AND (n.LVN_id IS NOT NULL OR q.lv4_ID IS NOT NULL) --Also migrate if missing in LevelN but used in inventory

--	END
--GO
/****** Object:  StoredProcedure [dbo].[ItemTypeLevel4RepeatedRecordsToInsert]    Script Date: 6/13/2018 9:39:35 AM ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO

---- =============================================
---- Author:		Syed Mohsin
---- Create date: <8/04/2017>
---- Description:	<Extracts data for Level4 repeated itemtypes>
---- Sample Call: EXEC [dbo].[ItemTypeLevel4RepeatedRecordsToInsert] 1
---- =============================================

--CREATE proc [dbo].[ItemTypeLevel4RepeatedRecordsToInsert]
--@StoreId int
--AS

--BEGIN
--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

--Select
--ROW_NUMBER() over (partition by RTRIM(LTRIM(s.Descript)) order by Lv4_pk) as Num,
--		p.ItemTypeId as ParentItemTypeId,
--		p.ItemCategoryId as ItemCategoryId,
--		ItemTypeName= cast(RTRIM(LTRIM(s.Descript)) as nvarchar(100)),
--		Value= cast(RTRIM(LTRIM(s.Descript)) as nvarchar(50)),
--		GetDate() as CreatedDate,
--		1 as CreatedBy, --Drop Column in package
--		GetDate() as UpdatedDate,
--		u.UserId as UpdatedBy,
--		0 as IsDeleted,
--		Lv4_pk as SourceID


--		from EXT_Level4 s

--					--and t.level=4
--			inner join CEXT_ItemType p
--				on s.lv3_Parent = p.sourceID
--					--and p.level=3
--			INNER JOIN  CEXT_ItemType t
--				on RTRIM(LTRIM(s.Descript)) = t.ItemTypeName and t.ParentItemTypeId = p.ItemTypeId
--				and t.SourceId IS NOT NULL and t.SourceId != s.lv4_PK
--			left outer JOIN EXT_Level_N n
--				ON n.Level_FK = s.lv4_PK
--			left outer join (SELECT DISTINCT lv4_ID from EXT_items where Sto_Pk=@StoreId )q
--					on q.lv4_ID = s.lv4_ID --Drop Columned for missing itemtypes in LevelN
--			left join CEXT_User u
--				on s.LastUpdatedUSR_ID = u.SourceId
--		where RTRIM(LTRIM(s.Descript)) != '' AND s.sto_pk = @StoreId
--		AND (n.LVN_id IS NOT NULL OR q.lv4_ID IS NOT NULL) --Also migrate if missing in LevelN but used in inventory

--	END
--GO
/****** Object:  StoredProcedure [dbo].[LookupCNExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO

---- =============================================
---- Author:		Nadeem Akhtar
---- Create date: <8/11/2016>
---- Description:	<Extracts data for LevelN Lookup values>
---- Sample Call: EXEC [dbo].[LookupCNExtract] 8 , 2

---- Updated By: Syed Mohsin
---- Update date: <8/12/2016>
---- Comments: Corrected mapping for columns and Drop Columned join conditions to meet the design logic.
----			 Drop Columned Storeid parameter for Source StoreId

---- Updated By: Syed Mohsin
---- Update date: <9/7/2016>
---- Comments: All rows are insert rows. Removed update from package
---- =============================================
--CREATE Proc [dbo].[LookupCNExtract]
--@SourceStoreId int , @TargetStoreId int

--AS
--BEGIN
--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

--  SELECT lv.LookupValueId,
--cast(LTRIM(RTRIM(n.NCIC_Code)) as nvarchar(50)) NCIC_Code ,
--  cast(LTRIM(RTRIM(n.Local_Code)) as nvarchar(50)) Local_Code,
--  getdate() as UpdateDate,
--  getdate() as CreatedDate,
--  u.UserId UpdatedBy,
--  0 as IsDeleted,
--  @TargetStoreId Storeid
-- from CEXT_LookupValue lv
--  INNER JOIN EXT_Lookup_C c ON lv.SourceId = c.lc_pk
--  INNER JOIN EXT_Lookup_N n ON n.lc_FK = c.lc_pk AND n.sto_pk = @SourceStoreId
--  LEFT JOIN CEXT_User u ON u.SourceId = n.LastUpdatedUSR_ID

--END
--GO
/****** Object:  StoredProcedure [dbo].[OverrideExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO

---- =============================================
---- Author:		<Farrukh Ijaz>
---- Create date: <09/05/2016>
---- Description:	<Extract all transaction overrides>
---- Sample Call: EXEC OverrideExtract 8
---- =============================================
--CREATE PROCEDURE [dbo].[OverrideExtract]
--	-- Drop Column the parameters for the stored procedure here
--	@SourceStoreId as int
--AS
--BEGIN
--	-- SET NOCOUNT ON Drop Columned to prevent extra result sets from
--	-- interfering with SELECT statements.
--	SET NOCOUNT ON;
--	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
--    -- Insert statements for procedure here
--	Select o.*,
--		CASE WHEN (pt.TransactionId IS NOT NULL) THEN
--			pt.TransactionId
--		WHEN (st.TransactionId IS NOT NULL) THEN
--			st.TransactionId
--		END AS TransactionId,
--		CASE WHEN (TransactionType='P_LimO') THEN 1667
--			WHEN (TransactionType='S_DisO') THEN 1671
--			WHEN (TransactionType='S_DepO') THEN 1672
--			END AS OverrideTypeId,
--		overUsr.UserId AS ActionUserId,
--		Usr.UserId AS OverridenUserId
--	FROM(
--			SELECT
--			CASE WHEN (Ovr_Desc='Override employee limit: Ticket#') Then
--				'P_LimO'
--			WHEN (Ovr_Desc='Sales - Override discount: Ticket#') THEN
--				'S_DisO'
--			WHEN (Ovr_Desc='Layaway - Deposit less than recommended percentage') THEN
--				'S_DepO'
--			END as TransactionType,
--			Ticketnum,
--			CAST(Ovr_Desc as nvarchar) AS Ovr_Desc,
--			Ovr_UserID,
--			[User_ID],
--			Ovr_Date,
--			Sto_PK FROM EXT_Override
--			WHERE Ovr_Desc IN ('Override employee limit: Ticket#','Sales - Override discount: Ticket#','Layaway - Deposit less than recommended percentage')
--			AND Sto_PK=@SourceStoreId
--		)o
--	LEFT JOIN CEXT_Transaction st ON o.Ticketnum=st.TicketNumber AND o.Sto_PK=st.StoreId AND o.TransactionType IN ('S_DisO', 'S_DepO')
--	LEFT JOIN CEXT_Transaction pt ON o.Ticketnum=pt.TicketNumber AND o.Sto_PK=pt.StoreId AND o.TransactionType='P_LimO'
--	LEFT JOIN CEXT_User overUsr ON o.Ovr_UserID = overUsr.SourceId
--	LEFT JOIN CEXT_User Usr ON o.[User_ID] = Usr.SourceId

--END
--GO
/****** Object:  StoredProcedure [dbo].[PartialPaymentsExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO

---- =============================================
---- Author:		<Syed Mohsin>
---- Create date: <08/01/2017>
---- Description:	<Extract TransactionPayments data from Acct table which are partial payments>
---- Sample Call: EXEC [dbo].[PartialPaymentsExtract] 1,399,5
---- =============================================

--CREATE PROCEDURE [dbo].[PartialPaymentsExtract]
--	-- Drop Column the parameters for the stored procedure here
--	@SourceStoreId as int,@TargetStoreId as int , @UtcTime int
--AS
--BEGIN
--	-- SET NOCOUNT ON Drop Columned to prevent extra result sets from
--	-- interfering with SELECT statements.
--	SET NOCOUNT ON;
--	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

--DECLARE @BatchId int
--SET @BatchId = (SELECT MAX(batchid) from CEXT_TransactionPayment)

--SELECT
--	[TYPE]
--	,'PAWN' AS PaymentTransType
--	,a.TICKETNUM as TicketNumber
--	,iif(LostTicket=0,a.AMOUNT,a.amount-LostTicket) as AMOUNT
--	,t.TransactionId
--	,1 as PaymentTypeId
--	,DATEDrop Column(HOUR,@UtcTime,a.DATEin) DATEin
--	,STATETAX+COUNTYTAX+LOCALTAX AS Tax
--	,DATEDrop Column(HOUR,@UtcTime,a.DATEin) AS CreatedDate
--	,u.UserId
--	,Acct_PK
--	,c.CustomerId
--	,lv1.LookupValueId AS TenderType1
--	,lv2.LookupValueId AS TenderType2
--	,TENDERAMT1
--	,TENDERAMT2
--	,Acct_PK AS SourceId
--	,DATEDrop Column(DAY,-p.NUMDAYS,DATEDrop Column(HOUR,@UtcTime,p.OrigChgDate)) as CHARGEDATE
--	, 0 as IsQueued
--	, @BatchId + ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as BatchId
--	,CAST('Partial' as NVARCHAR(500)) as VALUE
--	,CAST(a.RTsvc as decimal(12,3)) as ServiceFee
--	,CAST(a.RTint as decimal(12,3)) as InterestPercentage
--	,CAST(a.RTonetime as decimal(12,3)) as OneTimeFee
--	,CAST(a.RTper as decimal(12,3)) as FeePerPeriod
--	,CAST(a.Gun as decimal(12,3)) as FirearmFee

-- FROM EXT_pawn p
--inner join (
--select TICKETNUM  ,SUM(AMOUNT) [Total Amount] from EXT_acct a
--WHERE a.TYPE = 'PPP' and a.sto_pk = 1
--GROUP BY TICKETNUM
--)q
--on q.TICKETNUM = p.TICKETNUM and q.[Total Amount] = p.FLOATAMT

--inner join EXT_Acct a
--on a.TICKETNUM = p.TICKETNUM and a.sto_pk = @SourceStoreId  and type='PPP'

--inner join CEXT_transaction t
--on t.TicketNumber = a.TICKETNUM and t.TransactionTypeId = 1

--LEFT JOIN EXT_cust cst
--ON cst.Cus_PK = a.CUS_FK

--LEFT JOIN CEXT_Customer c
--ON c.SourceId = cst.Cus_id

--LEFT JOIN EXT_users us
--on us.USR_PK = a.Usr_FK and us.USR_STORE = @SourceStoreId
--LEFT JOIN CEXT_User u
--on u.SourceId = us.USR_ID

--LEFT JOIN EXT_Lookup_C tt1 ON a.TENDERTYP1 = tt1.lc_pk
--LEFT JOIN EXT_Lookup_C tt2 ON a.TENDERTYP2 = tt2.lc_pk
--LEFT JOIN CEXT_LookupValue lv1 ON tt1.lc_Descript = lv1.Value and tt1.lc_pk = lv1.SourceId
--LEFT JOIN CEXT_LookupValue lv2 ON tt2.lc_Descript = lv2.Value and tt2.lc_pk = lv2.SourceId

--WHERE p.STORE_NO = @SourceStoreId and p.FLOATAMT > 0

--END
--GO
/****** Object:  StoredProcedure [dbo].[PawnBuyTransactionExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO

--CREATE Proc [dbo].[PawnBuyTransactionExtract]
--@SourceStoreId int, @TargetStoreId int, @UtcTime int

--AS

--BEGIN
--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

--Declare @LastReminderDate datetime
--SET @LastReminderDate =
--(select DATEDrop Column(HOUR,@UtcTime,CAST(REMINDER as datetime)) as reminder from EXT_sysinfo2 where STO_PK=@SourceStoreId)

--select
--	tr.TransactionTypeId,
--	@TargetStoreId as StoreId,
--	cst.CustomerId,
--	p.TICKETNUM as TicketNumber,
--	NUMDAYS as Duration,
--	CAST(SERVPERIOD as int) Period,
--	DATEDrop Column(HOUR,@UtcTime,CAST(DATEIN as datetime)) InDate,--UTC CONVERSION
--	DATEDrop Column(HOUR,@UtcTime,CAST(DATEOUT as datetime)) OutDate,--UTC CONVERSION
--	CAST(PawnAMT as decimal(16,3)) as Amount,
--	rt.RateTableId,
--	CAST(iif(TRANS = 'O',CONCAT(LTRIM(PAWNNOTE),'This was a Buy-Sell option to begin with') ,PAWNNOTE) as nvarchar(500)) as Note,
--	1 as IsActive,
--	0 as IsDeleted,
--	0 as IsQueued,
--	DATEDrop Column(HOUR,@UtcTime,CAST(p.STARTDATE as datetime)) as CreatedDate,--UTC CONVERSION
--	DATEDrop Column(HOUR,@UtcTime,CAST(TRANSDATE as datetime)) as UpdatedDate,--UTC CONVERSION
--	u2.UserId as CreatedBy,
--	u.UserId as UpdatedBy,
--	p.PWN_id as SourceId,
--	s.StatusId,
--	CAST(p.COMMENT as nvarchar(200)) as [Message],
--	@LastReminderDate as LastDateReminderLetterSent
--	--VoidDescription
--from EXT_pawn p

--INNER JOIN CEXT_TransactionType tr
--	ON tr.TransactionTypeDesc = iif(p.TRANS='P' OR p.TRANS='O','Pawn','Buy')

--INNER JOIN EXT_cust c
--	ON c.Cus_PK = p.CUS_FK

--INNER JOIN CEXT_Customer cst
--	ON cst.sourceid = c.Cus_id OR( c.Cus_Store != @SourceStoreId and cst.FirstName like LTRIM(RTRIM(c.CUS_FNAME)) and cst.LastName like LTRIM(RTRIM(c.CUS_LNAME)) and cst.MiddleName like LTRIM(RTRIM(c.CUS_MNAME))
--	and cst.BirthDate like LTRIM(RTRIM(c.CUS_BIRTHDate)))

--LEFT JOIN CEXT_RateTable rt
--	ON rt.TableName = p.RateTable AND rt.StoreId = @TargetStoreId

--LEFT JOIN CEXT_User u
--	ON p.LastUpdatedUSR_ID = u.SourceId

--INNER JOIN Common.SourceTargetKeyMapping map
--	ON map.SourceKey = CASE WHEN p.[STATUS] = 'V' AND p.TRANS = 'P' THEN 'VP'
--							WHEN p.[STATUS] = 'V' AND p.TRANS = 'B' THEN 'VB'
--						ELSE p.[STATUS]
--						END
--	   AND map.SourceTable='TransactionCodes'

--INNER JOIN CEXT_Status s
--ON s.StatusCode = map.TargetKey

--LEFT JOIN EXT_users us
--on us.USR_PK = p.usr_fk and p.STORE_NO=us.USR_STORE

--LEFT JOIN CEXT_User u2
--on u2.SourceId = us.USR_ID

--WHERE p.STORE_NO=@SourceStoreId and p.TitleLoan=0

--END
--GO
/****** Object:  StoredProcedure [dbo].[PawnBuyTransactionItemExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO

--CREATE Proc [dbo].[PawnBuyTransactionItemExtract]
--@SourceStoreId int, @TargetStoreId int, @UtcTime int

--AS

--BEGIN
--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
--select
--	t.TransactionId,
--	st.StatusId,
--	il.InventoryItemLotId,
--	--CAST(i.AMOUNT as decimal(16,3)) as Cost,
--	--CAST(i.Origonhand as decimal(16,3)) as Quantity,
--	CAST(LTRIM(RTRIM(i.DESCRIPT)) as nvarchar(500)) as Comment,
--	CAST(i.RESALEAMT as decimal(16,3)) as Resale,
--	it.JewelryWeightUnitId,
--	t.CreatedDate as CreatedDate,
--	t.UpdatedDate as UpdatedDate,
--	1 as CreatedBy,
--	it.UpdatedBy,
--	it.BinNumberId,
--	CAST(GunChrg as decimal(16,3)) as GunProcessingFee

--from EXT_items i

--INNER JOIN EXT_pawn p
--	ON p.PWN_id = i.PWN_id and p.TitleLoan=0 --Drop Columned logic to remove pawns with title loans

--INNER JOIN CEXT_InventoryItem it
--	ON it.SourceId = i.Items_ID AND it.StoreId = @TargetStoreId

--INNER JOIN CEXT_InventoryItemLot il
--	ON il.InventoryItemId = it.InventoryItemId

--INNER JOIN CEXT_Transaction t
--	ON t.SourceId = p.PWN_id

--INNER JOIN Common.SourceTargetKeyMapping map
--	on map.SourceTable='ItemStatus' AND map.SourceKey = i.[Status]

--INNER JOIN CEXT_Status st
--	on st.StatusCode = CASE map.TargetKey WHEN 'S' THEN 'I' WHEN 'D' THEN 'DE' ELSE map.TargetKey END


--WHERE i.Sto_Pk=@SourceStoreId and it.SourceSplittedInventoryItemId is null and il.StatusId=28

--END
--GO
/****** Object:  StoredProcedure [dbo].[PoliceHoldDetailsExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO

---- =============================================
---- Author:		Syed Mohsin
---- Create date: <11/15/2016>
---- Description:	<Extracts police holds / confiscates >
---- Sample Call: EXEC [dbo].[PoliceHoldDetailsExtract] 8 , 5
---- =============================================

--CREATE Proc [dbo].[PoliceHoldDetailsExtract]
--@StoreId int , @UtcTime int
--as
--BEGIN
--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

--	SELECT
--		CAST(DATEDrop Column(HOUR,@UtcTime,[date]) as datetime) as StartedDate,
--		--NULL as ReleasedDate,
--		CAST(casenum as nvarchar(100)) as CaseNumber,
--		CAST(agency as nvarchar(500)) as Agency,
--		CAST(jurisdict as nvarchar(500)) as Jurisdiction,
--		CAST(agentfn as nvarchar(250)) as FirstName,
--		CAST(agentln as nvarchar(250)) as LastName,
--		CAST(agentmi as nvarchar(250)) as MiddleName,
--		CAST(badge as nvarchar(250)) as BadgeNumber,
--		CAST(ac1+'-'+phone1 as nvarchar(100)) as PhoneNumber,
--		CAST(ext1 as nvarchar(50)) as Extension,
--		CAST(hc.comment as nvarchar(1000)) as Comments,
--		CAST(DATEDrop Column(HOUR,@UtcTime,[date]) as datetime) as CreatedDate,
--		u.UserId as CreatedBy,
--		CAST(DATEDrop Column(HOUR,@UtcTime,iif(dateout IS NULL OR LTRIM(RTRIM([dateout])) = '',[date],dateout)) as datetime) as UpdatedDate,
--		u2.UserId as UpdatedBy,
--		HCN_id as SourceId
--		FROM EXT_holdcon hc
--		 LEFT JOIN EXT_users us
--		 on us.USR_PK = hc.emp_fk and hc.sto_pk = us.USR_STORE
--		 LEFT JOIN CEXT_User u
--		 on u.SourceId = us.USR_ID
--		 LEFT JOIN CEXT_User u2
--		 on u2.SourceId = hc.LastUpdatedUSR_ID
--		 LEFT JOIN EXT_items i
--		 on i.Sto_Pk=@StoreId and i.INVNUM = hc.LookupKey and hc.dateout is null and i.STATUS = 'I'
--		WHERE hc.[sto_pk] = @StoreId --Source StoreId for which data is being converted
--		--AND [date] >= DATEDrop Column(YEAR,-2,getdate()) --Extracting only last two year's data
--		AND ([dateout] IS NULL OR LTRIM(RTRIM([dateout])) = '') --Extracting only those items which are not released
--		AND hc.ishold = 2 --All un-released confiscated items
--		AND i.ITEMS_PK is null --Removing the Incorrect Hold/Confiscate records
--	UNION

--	SELECT
--		CAST(DATEDrop Column(HOUR,@UtcTime,[date]) as datetime) as StartedDate,
--		--NULL as ReleasedDate,
--		CAST(casenum as nvarchar(100)) as CaseNumber,
--		CAST(agency as nvarchar(500)) as Agency,
--		CAST(jurisdict as nvarchar(500)) as Jurisdiction,
--		CAST(agentfn as nvarchar(250)) as FirstName,
--		CAST(agentln as nvarchar(250)) as LastName,
--		CAST(agentmi as nvarchar(250)) as MiddleName,
--		CAST(badge as nvarchar(250)) as BadgeNumber,
--		CAST(ac1+'-'+phone1 as nvarchar(100)) as PhoneNumber,
--		CAST(ext1 as nvarchar(50)) as Extension,
--		CAST(hc.comment as nvarchar(1000)) as Comments,
--		CAST(DATEDrop Column(HOUR,@UtcTime,[date]) as datetime) as CreatedDate,
--		u.UserId as CreatedBy,
--		CAST(DATEDrop Column(HOUR,@UtcTime,iif(dateout IS NULL OR LTRIM(RTRIM([dateout])) = '',[date],dateout)) as datetime) as UpdatedDate,
--		u2.UserId as UpdatedBy,
--		HCN_id as SourceId
--		FROM EXT_holdcon hc
--		 LEFT JOIN EXT_users us
--		 on us.USR_PK = hc.emp_fk and hc.sto_pk = us.USR_STORE
--		 LEFT JOIN CEXT_User u
--		 on u.SourceId = us.USR_ID
--		 LEFT JOIN CEXT_User u2
--		 on u2.SourceId = hc.LastUpdatedUSR_ID
--		 		 LEFT JOIN EXT_items i
--		 on i.Sto_Pk=@StoreId and i.INVNUM = hc.LookupKey and hc.dateout is null and i.STATUS = 'I'
--		WHERE hc.[sto_pk] = @StoreId --Source StoreId for which data is being converted
--		--AND [date] >= DATEDrop Column(YEAR,-2,getdate()) --Extracting only last two year's data
--		AND ([dateout] IS NULL OR LTRIM(RTRIM([dateout])) = '') --Extracting only those items which are not released
--		AND hc.ishold = 1 --All un-released items in hold
--		AND i.ITEMS_PK is null --Removing the Incorrect Hold/Confiscate records
--END
--GO
/****** Object:  StoredProcedure [dbo].[PoliceHoldItemsExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO

---- =============================================
---- Author:		Syed Mohsin
---- Create date: <11/15/2016>
---- Description:	<Extracts police holds / confiscates >
---- Sample Call: EXEC [dbo].[PoliceHoldItemsExtract] 8 , 182
---- =============================================

--CREATE Proc [dbo].[PoliceHoldItemsExtract]
--@SourceStoreId int , @TargetStoreId int
--as
--BEGIN
--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

--		SELECT
--			pc.PoliceConfiscateCaseDetailId,
--			t.TransactionId,
--			TransactionItemId,
--			iif(isinv=1,it.InventoryItemId,NULL) as InventoryItemId,
--			iif(isinv=1,CAST(i.OnHand as decimal(12,3)),NULL) as Quantity,
--			iif(hc.ishold = 1,14,10) as StatusId,
--			@TargetStoreId as StoreId, --TargetStore
--			pc.CreatedDate,
--			pc.CreatedBy,
--			pc.UpdatedDate,
--			pc.UpdatedBy

--		FROM EXT_HoldConItems hci

--		 INNER JOIN EXT_holdcon hc on hci.hc_fk = hc.hc_pk and hc.sto_pk = hci.sto_fk
--		 INNER JOIN CEXT_PoliceConfiscateDetail pc on pc.SourceId = hc.HCN_id
--		 LEFT JOIN EXT_items i on i.Sto_Pk=hci.sto_fk and i.ITEMS_PK = hci.items_fk
--		 LEFT JOIN CEXT_InventoryItem it on it.SourceId = i.Items_ID
--		 LEFT JOIN CEXT_InventoryItemLot il on il.InventoryItemId = it.InventoryItemId
--		 LEFT JOIN EXT_pawn p on p.STORE_NO = hci.sto_fk and p.TICKETNUM = iif(hc.isinv=2,hc.lookupkey,NULL)
--		 LEFT JOIN CEXT_Transaction t on t.SourceId = p.PWN_id
--		 LEFT JOIN CEXT_TransactionItem ti on ti.TransactionId = t.TransactionId and ti.InventoryItemLotId = il.InventoryItemLotId
--		 WHERE hci.sto_fk = @SourceStoreId

--END
--GO
/****** Object:  StoredProcedure [dbo].[PoliceHoldItemsLotExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO

---- =============================================
---- Author:		Syed Mohsin
---- Create date: <11/15/2016>
---- Description:	<Extracts police holds / confiscates >
---- Sample Call: EXEC [dbo].[PoliceHoldItemsLotExtract] 8
---- =============================================

--CREATE Proc [dbo].[PoliceHoldItemsLotExtract]
--@SourceStoreId int
--as
--BEGIN
--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

--SELECT
--	pci.PoliceConfiscateCaseDetailId,
--	pci.InventoryItemId,
--	pci.StatusId,
--	pci.Quantity*-1 as Quantity,
--	il.Cost,
--	il.DateIn,
--	il.SourceInventoryItemId,
--	il.SourceInventoryItemIdVersion,
--	pc.CreatedDate,
--	0 as CreatedBy

--FROM ExT_holdcon hc
--INNER JOIN CEXT_PoliceConfiscateDetail pc on pc.SourceId = hc.HCN_id
--INNER JOIN CEXT_PoliceConfiscateItems pci on pci.PoliceConfiscateCaseDetailId = pc.PoliceConfiscateCaseDetailId
--INNER JOIN CEXT_InventoryItemLot il on il.InventoryItemId = pci.InventoryItemId and il.StatusId = 28 and SourceInventoryItemId is null

--WHERE hc.sto_pk = @SourceStoreId

--END
--GO
/****** Object:  StoredProcedure [dbo].[PoliceHoldItemsLotInsert]    Script Date: 6/13/2018 9:39:35 AM ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO

---- =============================================
---- Author:		Syed Mohsin
---- Create date: <11/15/2016>
---- Description:	<Extracts police holds / confiscates >
---- Sample Call: EXEC [dbo].[PoliceHoldItemsLotInsert] 182
---- =============================================

--CREATE Proc [dbo].[PoliceHoldItemsLotInsert]
--@TargetStoreId int
--as
--BEGIN
--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

--		SELECT	pc.InventoryItemId,
--				pc.StatusId,
--				pc.Quantity,
--				pc.Cost,
--				pc.DateIn,
--				pc.CreatedDate,
--				il.InventoryItemLotNumber,
--				il.CreatedBy,
--				il.UpdatedDate,
--				il.UpdatedBy
--FROM CEXT_PoliceConfiscateInventoryItemLot pc
--INNER JOIN CEXT_PoliceConfiscateItems pci on pci.PoliceConfiscateCaseDetailId = pc.PoliceConfiscateCaseDetailId
--INNER JOIN CEXT_InventoryItemLot il on pc.InventoryItemId = il.InventoryItemId
--WHERE pci.StoreId = @TargetStoreId

--END
--GO
/****** Object:  StoredProcedure [dbo].[RateTableExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO

---- =============================================
---- Author:		Syed Mohsin
---- Create date: <8/18/2016>
---- Description:	<Extracts data for RateTables>
---- Sample Call: EXEC [dbo].[RateTableExtract] 8

---- Updated By: Syed Mohsin
---- Update date: <9/5/2016>
---- Comments: Made fixes for iteration 1 response.
---- =============================================

--CREATE Proc [dbo].[RateTableExtract]
--@StoreId int
--AS
--BEGIN
--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
--select * from (
--SELECT
--      1 as StoreId, -- Drop Column in package
--      ROW_NUMBER() OVER(PARTITION BY [DESCRIPT] ORDER BY UpdatedDate DESC) AS RowNumber, -- Fixed iteration 1 bug
--	  cast([DESCRIPT] as nvarchar(20)) as TableName,
--	  cast (iif(s.DEFAULTRAT IS NULL,0,1) as bit) as IsDefault,
--	  0 as IsDeleted,
--	  getdate() as CreatedDate,
--	  1 as CreatedBy, -- Drop Column in package
--	  getdate() as UpdatedDate,
--	  u.UserId as UpdatedBy

--  FROM EXT_c_rates c
--  LEFT OUTER JOIN EXT_SysInfo2 s
--	ON c.DESCRIPT = s.DEFAULTRAT AND s.STO_PK = c.STO_PK
--  LEFT OUTER JOIN CEXT_User u
--	ON c.LastUpdatedUSR_ID = u.SourceId
--	WHERE
--	c.sto_pk = @StoreId and c.SimpleInterest = 0
--	) s WHERE RowNumber = 1
--END
--GO
/****** Object:  StoredProcedure [dbo].[RateTablePeriodExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO

---- =============================================
---- Author:		Syed Mohsin
---- Create date: <8/18/2016>
---- Description:	<Extracts data for RateTablePeriod>
---- Sample Call: EXEC [dbo].[RateTablePeriodExtract] 8
---- =============================================

--CREATE Proc [dbo].[RateTablePeriodExtract]
--@StoreId int
--AS
--BEGIN
--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
--SELECT r.RateTableId as RateTableId
--	  ,cast([PERIOD] as int) as PeriodId
--      ,cast([HD] as decimal(12,2)) as UptoAmount
--      ,cast(iif([DP]='$',1,0) as bit) as CalculationType
--      ,cast([AP] as decimal(12,2)) as ServiceRate
--      ,cast([Interest]/100 as decimal(5,4)) as InterestPercentage
--	  ,0 as IsDeleted
--	  ,getdate() as CreatedDate
--	  ,1 as CreatedBy -- Drop Column in package
--	  ,getdate() as UpdatedDate
--	  ,u.UserId as UpdatedBy
--	  ,cast([MINAMT] as decimal(12,2)) as Minimum
--      ,cast([ONECHRG] as decimal(12,2)) as OneTimeFee
--      ,cast([MONCHRG] as decimal(12,2)) as FeePerPeriod

--  FROM EXT_c_rates c
--		inner join CEXT_RateTable r
--			on c.DESCRIPT = r.TableName
--		left join CEXT_User u
--			on c.LastUpdatedUSR_ID = u.SourceId
--	WHERE c.SimpleInterest=0 AND c.STO_PK = @StoreId
--END
--GO
/****** Object:  StoredProcedure [dbo].[RequiredFieldsExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO

---- =============================================
---- Author:		Farrukh Ijaz
---- Create date: <8/17/2016>
---- Description:	<Extracts all system options in required format>
---- Sample Call: EXEC [dbo].[RequiredFieldsExtract] 8
---- =============================================
--CREATE PROCEDURE [dbo].[RequiredFieldsExtract]
--	@SourceStoreId as int=0
--AS
--BEGIN
--	-- SET NOCOUNT ON Drop Columned to prevent extra result sets from
--	-- interfering with SELECT statements.
--	SET NOCOUNT ON;
--	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;


--SELECT RF.EntityName, RF.SourceKey, RF.SourceValue, mapping.[TargetKey], reqFields.RequiredFieldId  FROM(
-----Select from CustReq
--SELECT 'CustReq' AS SourceTable, 'Customer' AS EntityName, SourceKey, SourceValue
--FROM
--(SELECT CUS_CELLPHONE
--		,CUS_SSNUM
--		,CUS_BIRTHDate
--		,CUS_BIRTHCITY
--		,CUS_BIRTHSTATE
--		--,CUS_BIRTHCOUNTRY_NA
--		,CUS_HEIGHT
--		,CUS_WEIGHT
--		,CUS_HAIRFK
--		,CUS_EYESFK
--		,CUS_RACEFK
--		,CUS_SEX
--		,CUS_MARKS
--		,CUS_IDTYP1
--		,CUS_IDNUM1
--		,CUS_ID1EXP
--		,CUS_ID1ISSUE
--		,CUS_IDTYP2
--		,CUS_IDNUM2
--		,CUS_ID2EXP
--		,CUS_ID2ISSUE
--		,CUS_FNAME
--		,CUS_MNAME
--		,CUS_LNAME
--		,CUS_Drop Column1
--		,CUS_CITY
--		,CUS_STATE
--		,CUS_ZIP
--		,CUS_PHONE1
--		--,CUS_Drop ColumnITIONALINFO_NA
--		,CUS_SPECIAL
--FROM EXT_CustReq WHERE CUS_STORE=@SourceStoreId
--)CR
--unpivot
--(
--SourceValue for SourceKey IN
--(
--		CUS_CELLPHONE
--		,CUS_SSNUM
--		,CUS_BIRTHDate
--		,CUS_BIRTHCITY
--		,CUS_BIRTHSTATE
--		--,CUS_BIRTHCOUNTRY_NA
--		,CUS_HEIGHT
--		,CUS_WEIGHT
--		,CUS_HAIRFK
--		,CUS_EYESFK
--		,CUS_RACEFK
--		,CUS_SEX
--		,CUS_MARKS
--		,CUS_IDTYP1
--		,CUS_IDNUM1
--		,CUS_ID1EXP
--		,CUS_ID1ISSUE
--		,CUS_IDTYP2
--		,CUS_IDNUM2
--		,CUS_ID2EXP
--		,CUS_ID2ISSUE
--		,CUS_FNAME
--		,CUS_MNAME
--		,CUS_LNAME
--		,CUS_Drop Column1
--		,CUS_CITY
--		,CUS_STATE
--		,CUS_ZIP
--		,CUS_PHONE1
--		--,CUS_Drop ColumnITIONALINFO_NA
--		,CUS_SPECIAL
--)
--) unpiv

--UNION

-----Select from ITRQ_ItemReq
--SELECT 'ITRQ_ItemReq' AS SourceTable, 'Item' AS EntityName, SourceKey, SourceValue
--FROM
--(SELECT level1_fk
--		,level2_fk
--		,level3_fk
--		,level4_fk
--		,level5_fk
--		,modelnum
--		,serialnum
--		,color
--		,condition_item
--		,ownernum
--		,bin
--		,amount
--		,resaleamt
--		,onhand
--		,descript
--		,metal_fk
--		,karat_fk
--		,[weight]
--		,gender_fk
--		,style_fk
--		,sizelen_fk
--		,action_fk
--		,finish_fk
--		,barrel_fk
--		,[length]
--		,caliber_fk
--		,condition
--		,importerfk
--		,numstone
--		,typstonefk
--		,shape_fk
--		,carat
--		,color_fk
--		,stone_weight
--		,stone_length
--		,stone_width
--		,translucfk
--FROM EXT_ITRQ_ItemReq  WHERE EXT_ITRQ_ItemReq.ITRQ_Store=@SourceStoreId
--)IR
--unpivot
--(
--SourceValue for SourceKey IN
--(
--		level1_fk
--		,level2_fk
--		,level3_fk
--		,level4_fk
--		,level5_fk
--		,modelnum
--		,serialnum
--		,color
--		,condition_item
--		,ownernum
--		,bin
--		,amount
--		,resaleamt
--		,onhand
--		,descript
--		,metal_fk
--		,karat_fk
--		,[weight]
--		,gender_fk
--		,style_fk
--		,sizelen_fk
--		,action_fk
--		,finish_fk
--		,barrel_fk
--		,[length]
--		,caliber_fk
--		,condition
--		,importerfk
--		,numstone
--		,typstonefk
--		,shape_fk
--		,carat
--		,color_fk
--		,stone_weight
--		,stone_length
--		,stone_width
--		,translucfk
--)
--) unpiv
--) RF
--INNER JOIN Common.[SourceTargetKeyMapping] mapping ON RF.SourceTable = mapping.SourceTable AND RF.SourceKey=mapping.SourceKey
--INNER JOIN CEXT_RequiredField reqFields ON mapping.TargetKey = reqFields.RequiredFieldName AND RF.EntityName= reqFields.EntityName

--END
--GO
/****** Object:  StoredProcedure [dbo].[ReWriteExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO

---- =============================================
---- Author:		Syed Mohsin
---- Create date: <11/22/2017>
---- Description:	<Extracts data for ReWrite history>
---- Sample Call: EXEC [dbo].[ReWriteExtract] 8, 171
---- =============================================

--CREATE Proc [dbo].[ReWriteExtract]
--@SourceStoreID int, @TargetStoreId int

--AS

--BEGIN
--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

--SELECT
--	tn.TransactionId,
--	iif(p.ORIGTICKET != p.TICKETNUM,t.TicketNumber,NULL) as OriginalTicketNumber,
--	tn.TicketNumber,
--	tn.CreatedDate as RewriteDate,
--	tn.CreatedDate,
--	tn.CreatedBy
--from EXT_Pawn p

--LEFT JOIN CEXT_Transaction t on t.TicketNumber = p.ORIGTICKET and t.TransactionTypeId=1

--INNER JOIN CEXT_Transaction tn on tn.TicketNumber = p.TICKETNUM and tn.TransactionTypeId=1


--END
--GO
/****** Object:  StoredProcedure [dbo].[SaleLayawayTransactionExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO

---- =============================================
---- Author:		Syed Mohsin
---- Create date: <9/5/2016>
---- Description:	<Extracts transactions for sale / layaway >
---- Sample Call: EXEC [dbo].[SaleLayawayTransactionExtract] 8 , 5
---- =============================================

--CREATE Proc [dbo].[SaleLayawayTransactionExtract]
--@StoreId int , @UtcTime int
--as
--BEGIN
--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
--Select  DATEDrop Column(HOUR,@UtcTime,Cast (DATEin As datetime)) as CreatedDate,
--		u2.UserId as CreatedBy, --Drop Column in package
--       DATEDrop Column(HOUR,@UtcTime,Cast (
--	   CASE
--	   WHEN s.TRANS = 'S' and s.STATUS = 'S'
--	   THEN s.DATEin
--	   WHEN s.TRANS = 'S' and s.STATUS != 'S'
--	   THEN s.DateOut
--	   ELSE s.DateOut
--	   END
--	    As datetime)) as UpdatedDate,
--       u.userId as UpdatedBy,
--       1 as StoreID, --Drop Column in package
--       c.CustomerId as CustomerId,
--	   tr.TransactionTypeId ,
--       Cast (TICKETNUM As int) as TicketNumber,
--       Cast (PERIOD As int) as Period,
--       DATEDrop Column(HOUR,@UtcTime,Cast (DATEin As datetime)) as InDate, --UTC CONVERSION
--       DATEDrop Column(HOUR,@UtcTime,Cast (DATEout As datetime)) as OutDate, --UTC CONVERSION
--       Cast (SaleAmt + s.tax  As decimal(16, 3)) as Amount,
--       Cast (s.NOTE As nvarchar(500)) as Note,
--      st.StatusId as StatusId,

--	  	0 as IsQueued,
--       Cast (sld_Message As nvarchar(200)) as Message,
--       Cast (s.COMMENT As nvarchar(200)) as VoidDescription,
--	   s.GunProcFee as GunProcessingFee ,
--       Cast (s.SLD_id As nvarchar(50)) as SourceID

--from EXT_Sold s
--INNER JOIN EXT_cust cst
--	on cst.Cus_PK = s.CUS_FK
--INNER JOIN CEXT_Customer c
--      	ON c.sourceid = cst.Cus_id OR(cst.Cus_Store != @StoreId and  c.FirstName like LTRIM(RTRIM(cst.CUS_FNAME)) and c.LastName like LTRIM(RTRIM(cst.CUS_LNAME)) and c.MiddleName like LTRIM(RTRIM(cst.CUS_MNAME))
--	and c.BirthDate like LTRIM(RTRIM(cst.CUS_BIRTHDate)))
--LEFT JOIN CEXT_User u
--	on u.SourceId = s.LastUpdatedUSR_ID
--INNER JOIN CEXT_TransactionType tr
--	on tr.TransactionTypeDesc = CASE s.TRANS
--								WHEN 'S' THEN 'Sale'
--								WHEN 'C' THEN 'Sale'
--								WHEN 'L' THEN 'Layaway'
--								END
--INNER JOIN Common.SourceTargetKeyMapping map
--	on map.SourceTable='TransactionCodes' AND map.SourceKey = CASE
--												WHEN s.[STATUS]='V' AND s.TRANS='L' THEN 'VLA'
--												WHEN s.[STATUS]='V' AND s.TRANS='S' THEN 'VS'
--												WHEN s.[STATUS]='R' AND s.TRANS='L' THEN 'VLA'
--												WHEN s.[STATUS]='R' AND s.TRANS='S' THEN 'VS'
--												ELSE s.STATUS
--												END
--INNER JOIN CEXT_Status st
--	on st.StatusCode = map.TargetKey

--LEFT JOIN EXT_users us on us.USR_PK = s.USR_fk and us.USR_STORE = s.STO_PK

--LEFT JOIN CEXT_User u2 on u2.SourceId = us.USR_ID


--WHERE s.STO_PK = @StoreId

--END
--GO
/****** Object:  StoredProcedure [dbo].[SaleLayawayTransactionItemExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO

---- =============================================
---- Author:		Syed Mohsin
---- Create date: <9/5/2016>
---- Description:	<Extracts transaction items for sale / layaway>
---- Sample Call: EXEC [dbo].[SaleLayawayTransactionItemExtract] 1 , 1831 , 5

---- Updated By: Syed Mohsin
---- Update date: <9/22/2016>
---- Comments: Drop Columned check for non-inventory items.

---- Updated By: Syed Mohsin
---- Update date: <1/5/2017>
---- Comments: Splitted items are removed because not part of TransactionItem.

---- Updated By: Syed Mohsin
---- Update date: <4/3/2017>
---- Comments: Splitted items are Drop Columned becasue this is sale layaway transactions. InventoryItemLot join conditions updated. Repetition is removed.
---- =============================================

--CREATE Proc [dbo].[SaleLayawayTransactionItemExtract]
--@StoreId int ,  @TargetStoreId int, @UtcTime int
--as
--BEGIN
--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
--DECLARE @lookupTypeIDBin int
--SET @lookupTypeIDBin = (SELECT LookupTypeId from CEXT_LookupType
--							where LookupTypeName='Bin')


--DECLARE @StateTax DECIMAL(16,3)
--DECLARE @LocalTax DECIMAL(16,3)
--DECLARE @CountyTax DECIMAL(16,3)

--SET @StateTax = (SELECT sso.SystemOptionValue FROM CEXT_.SystemOption so
--INNER JOIN CEXT_StoreSystemOption sso ON so.SystemOptionId = sso.SystemOptionId
--WHERE so.SystemOptionKey = 'GENERAL_SALES_TAX_STATE' AND sso.StoreId = @TargetStoreId )

--SET @LocalTax = (SELECT sso.SystemOptionValue FROM CEXT_SystemOption so
--INNER JOIN CEXT_StoreSystemOption sso ON so.SystemOptionId = sso.SystemOptionId
--WHERE so.SystemOptionKey = 'GENERAL_SALES_TAX_LOCAL' AND sso.StoreId = @TargetStoreId )

--SET @CountyTax = (SELECT sso.SystemOptionValue FROM CEXT_SystemOption so
--INNER JOIN CEXT_StoreSystemOption sso ON so.SystemOptionId = sso.SystemOptionId
--WHERE so.SystemOptionKey = 'GENERAL_SALES_TAX_COUNTY' AND sso.StoreId = @TargetStoreId )


--Select
--iif(s.RETURNSOLD IS NULL,tr.CreatedDate,DATEDrop Column(HOUR,@UtcTime,CAST(s.RETURNSOLD as datetime))) as CreatedDate,--UTC CONVERSION
--       1 as CreatedBy, -- Drop Column in package
--       iif(s.RETURNSOLD IS NULL,tr.UpdatedDate,DATEDrop Column(HOUR,@UtcTime,CAST(s.RETURNSOLD as datetime))) as UpdatedDate,--UTC CONVERSION
--       u.UserId as UpdatedBy,
--       1 as StoreID, -- Drop Column in package
--       --iif(tr.StatusId = 2 , CASE WHEN tr.transactiontypeid = 3 then 19 ELSE 2 END ,st.StatusId) StatusId,
--	   iif(tr.transactiontypeid = 5 and st.statusid = 19,2,st.statusid ) StatusId,
--	   tr.TransactionId,
--	   lot.InventoryItemLotId ,
--        Cast (s.AMOUNT As decimal(16, 3)) as Resale,
--		Cast (s.Cost As decimal(16, 3)) as Cost,
--       Cast (s.NUMBERSOLD As decimal(16, 3)) as Quantity,
--       Cast (LTRIM(RTRIM(s.DESCRIPT)) As nvarchar(500)) as Comment,
--Cast   (
--                     iif(s.AMOUNT<i.RESALEAMT AND s.AMOUNT >= 0 ,
--                                  (i.RESALEAMT-s.AMOUNT)/i.RESALEAMT,
--                                  0
--                     )
--                     As decimal(5, 2)
--              ) as DiscountPercentage,

--       Cast   (
--                     iif(s.TAXEXEMPT=1 and s.CountyTaxExempt=1,1,0)
--                     As bit
--                     ) as IsTaxExempt,
--       iif(s.items_pk <=0, Cast (s.DESCRIPT As nvarchar(500)),NULL) as NonInventoryItemDescription,
--       iif(s.items_pk <=0,Cast (s.INVNUM As nvarchar(50)),NULL) as NonInventoryNumber,
--       lv.LookupValueId as BinNumberId,
--	   iif(s.Taxexempt=0 and s.CountyTaxExempt=0,@StateTax,NULL ) as StateTax,
--	   iif(s.Taxexempt=0 and s.CountyTaxExempt=0,@CountyTax,NULL ) as CountyTax,
--	   iif(s.Taxexempt=0 and s.CountyTaxExempt=0,@LocalTax,NULL ) as LocalTax,
--	   iif(s.Taxexempt=0,iif(sld.TAX > 0,((s.NUMBERSOLD * s.Amount)/sld.Taxable)*sld.Tax,0),NULL ) as Tax,
--       Cast (i.RESALEAMT As decimal(16, 3)) as OrignalResale,
--	   NULL as GunProcessingFee,
--	   ROW_NUMBER() OVER (PARTITION BY s.TICKETNUM ORDER BY sitem_PK desc) as TransactionItemLinkedId

--from EXT_sitems s

--	INNER JOIN Common.SourceTargetKeyMapping map
--		on map.SourceTable='ItemStatus' AND map.SourceKey = s.[Status]

--	INNER JOIN CEXT_Status st
--		on st.StatusCode = CASE map.TargetKey WHEN 'S' THEN 'SA' WHEN 'D' THEN 'DE' ELSE map.TargetKey END

--	inner join EXT_Sold sld
--		on sld.TICKETNUM = s.TICKETNUM AND sld.STO_PK = s.Sto_PK

--	INNER join CEXT_Transaction tr
--		on tr.SourceId = sld.SLD_id

--		left join CEXT_User u
--			on u.SourceId = s.LastUpdatedUSR_ID

--	   left outer join CEXT_LookupValue lv
--              on LTRIM(RTRIM(s.BIN)) != '' and s.Bin =lv.Value AND lv.LookupTypeId = @lookupTypeIDBin

--	   left outer join EXT_items i
--              on s.Items_id=i.Items_id

--	   left join CEXT_InventoryItem ii
--              on s.Items_ID IS NOT NULL and ii.SourceId=s.Items_ID

--		left join CEXT_InventoryItemLot lot
--			  on ii.InventoryItemId is not null and lot.SourceInventoryItemId IS NULL and lot.StatusId=28 and lot.InventoryItemId = ii.InventoryItemId
--		--left join PawnMaster_foxPro.dbo.Acct ac
--			  --on ac.sto_pk = @StoreId and ac.TICKETNUM = s.TICKETNUM and ac.TOWHOM='GUN PROCESSING FEE-S'




--where s.Sto_PK = @StoreId --and ii.SourceSplittedInventoryItemId IS NULL

--END
--GO
/****** Object:  StoredProcedure [dbo].[ServiceFeePaymentsExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO

---- =============================================
---- Author:		<Syed Mohsin>
---- Create date: <08/01/2017>
---- Description:	<Extract TransactionPayments data from Acct table on which are service fee paid>
---- Sample Call: EXEC [dbo].[ServiceFeePaymentsExtract] 1,399,5
---- =============================================

--CREATE PROCEDURE [dbo].[ServiceFeePaymentsExtract]
--	-- Drop Column the parameters for the stored procedure here
--	@SourceStoreId as int,@TargetStoreId as int , @UtcTime int
--AS
--BEGIN
--	-- SET NOCOUNT ON Drop Columned to prevent extra result sets from
--	-- interfering with SELECT statements.
--	SET NOCOUNT ON;
--	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

--DECLARE @BatchId int
--SET @BatchId = (SELECT MAX(batchid) from CEXT_TransactionPayment)

--SELECT
--	--ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as Num,
--	[TYPE]
--	,'PAWN' AS PaymentTransType
--	,a.TICKETNUM as TicketNumber
--	,iif(LostTicket=0,a.AMOUNT,a.amount-LostTicket) as AMOUNT
--	,t.TransactionId
--	,4 as PaymentTypeId
--	,DATEDrop Column(HOUR,@UtcTime,a.DATEin) DATEin
--	,STATETAX+COUNTYTAX+LOCALTAX AS Tax
--	,DATEDrop Column(HOUR,@UtcTime,a.DATEin) AS CreatedDate
--	,u.UserId
--	,Acct_PK
--	,c.CustomerId
--	,lv1.LookupValueId AS TenderType1
--	,lv2.LookupValueId AS TenderType2
--	,TENDERAMT1
--	,TENDERAMT2
--	,Acct_PK AS SourceId
--	,DATEDrop Column(DAY,-p.NUMDAYS,DATEDrop Column(HOUR,@UtcTime,p.OrigChgDate)) as CHARGEDATE
--	, 0 as IsQueued
--	, @BatchId + ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as BatchId
--	,CAST('Service Charges' as NVARCHAR(500)) as VALUE
--	,CAST(a.RTsvc as decimal(12,3)) as ServiceFee
--	,CAST(a.RTint as decimal(12,3)) as InterestPercentage
--	,CAST(a.RTonetime as decimal(12,3)) as OneTimeFee
--	,CAST(a.RTper as decimal(12,3)) as FeePerPeriod
--	,CAST(a.Gun as decimal(12,3)) as FirearmFee
--	,a.Cus_Credit as PaidCustomerCredit

-- FROM EXT_Acct a

--inner join EXT_Pawn p
--on p.TICKETNUM = a.TICKETNUM

--inner join CEXT_transaction t
--on t.TicketNumber = a.TICKETNUM and t.TransactionTypeId = 1

--LEFT JOIN EXT_cust cst
--ON cst.Cus_PK = a.CUS_FK

--LEFT JOIN CEXT_Customer c
--ON c.SourceId = cst.Cus_id

--LEFT JOIN EXT_users us
--on us.USR_PK = a.Usr_FK and us.USR_STORE = @SourceStoreId
--LEFT JOIN CEXT_User u
--on u.SourceId = us.USR_ID

--LEFT JOIN EXT_Lookup_C tt1 ON a.TENDERTYP1 = tt1.lc_pk
--LEFT JOIN EXT_Lookup_C tt2 ON a.TENDERTYP2 = tt2.lc_pk
--LEFT JOIN CEXT_LookupValue lv1 ON tt1.lc_Descript = lv1.Value and tt1.lc_pk = lv1.SourceId
--LEFT JOIN CEXT_LookupValue lv2 ON tt2.lc_Descript = lv2.Value and tt2.lc_pk = lv2.SourceId

--where p.FLOATAMT = 0 and p.STORE_NO = @SourceStoreId and a.sto_pk = @SourceStoreId and t.StoreId = @TargetStoreId and TYPE = 'PPP'

--END
--GO
/****** Object:  StoredProcedure [dbo].[Service-PartialPaymentsExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO

---- =============================================
---- Author:		<Syed Mohsin>
---- Create date: <08/01/2017>
---- Description:	<Extract TransactionPayments data from Acct table which are partial payments and service charges paid>
---- Sample Call: EXEC [dbo].[Service-PartialPaymentsExtract] 1,399,5
---- =============================================

--CREATE PROCEDURE [dbo].[Service-PartialPaymentsExtract]
--	-- Drop Column the parameters for the stored procedure here
--	@SourceStoreId as int,@TargetStoreId as int , @UtcTime int
--AS
--BEGIN
--	-- SET NOCOUNT ON Drop Columned to prevent extra result sets from
--	-- interfering with SELECT statements.
--	SET NOCOUNT ON;
--	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

--DECLARE @BatchId int
--SET @BatchId = (SELECT MAX(batchid) from CEXT_TransactionPayment)

--SELECT
--	[TYPE]
--	,'PAWN' AS PaymentTransType
--	,a.TICKETNUM as TicketNumber
--	,iif(LostTicket=0,a.AMOUNT,a.amount-LostTicket) as AMOUNT
--	,t.TransactionId
--	,4 as PaymentTypeId
--	,DATEDrop Column(HOUR,@UtcTime,a.DATEin) DATEin
--	,STATETAX+COUNTYTAX+LOCALTAX AS Tax
--	,DATEDrop Column(HOUR,@UtcTime,a.DATEin) AS CreatedDate
--	,u.UserId
--	,Acct_PK
--	,c.CustomerId
--	,lv1.LookupValueId AS TenderType1
--	,lv2.LookupValueId AS TenderType2
--	,TENDERAMT1
--	,TENDERAMT2
--	,Acct_PK AS SourceId
--	,DATEDrop Column(DAY,-p.NUMDAYS,DATEDrop Column(HOUR,@UtcTime,p.OrigChgDate)) as CHARGEDATE
--	, 0 as IsQueued
--	, @BatchId + ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as BatchId
--	,CAST('Service Charges' as NVARCHAR(500)) as VALUE
--	,CAST(a.RTsvc as decimal(12,3)) as ServiceFee
--	,CAST(a.RTint as decimal(12,3)) as InterestPercentage
--	,CAST(a.RTonetime as decimal(12,3)) as OneTimeFee
--	,CAST(a.RTper as decimal(12,3)) as FeePerPeriod
--	,CAST(a.Gun as decimal(12,3)) as FirearmFee
--	,a.Cus_Credit as PaidCustomerCredit
-- FROM EXT_Pawn p
--inner join (
--select TICKETNUM  ,SUM(AMOUNT) [Total Amount] from EXT_acct a
--WHERE a.TYPE = 'PPP' and a.sto_pk = 1
--GROUP BY TICKETNUM
--)q
--on q.TICKETNUM = p.TICKETNUM and q.[Total Amount] > p.FLOATAMT
--inner join EXT_Acct a on a.TICKETNUM = p.TICKETNUM and a.sto_pk = 1

--inner join CEXT_Transaction t
--on t.TicketNumber = a.TICKETNUM and t.TransactionTypeId = 1

--LEFT JOIN EXT_cust cst
--ON cst.Cus_PK = a.CUS_FK

--LEFT JOIN CEXT_Customer c
--ON c.SourceId = cst.Cus_id

--LEFT JOIN EXT_users us
--on us.USR_PK = a.Usr_FK and us.USR_STORE = @SourceStoreId
--LEFT JOIN CEXT_User u
--on u.SourceId = us.USR_ID

--LEFT JOIN EXT_Lookup_C tt1 ON a.TENDERTYP1 = tt1.lc_pk
--LEFT JOIN EXT_Lookup_C tt2 ON a.TENDERTYP2 = tt2.lc_pk
--LEFT JOIN CEXT_LookupValue lv1 ON tt1.lc_Descript = lv1.Value
--LEFT JOIN CEXT_LookupValue lv2 ON tt2.lc_Descript = lv2.Value

--WHERE p.STORE_NO = 1 and p.FLOATAMT > 0

--END
--GO
/****** Object:  StoredProcedure [dbo].[sp_Drop ColumnStagingColumns]    Script Date: 6/13/2018 9:39:35 AM ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO

---- =============================================
---- Author:		Syed Mohsin
---- Create date: <8/4/2016>
---- Description:	<This sp will Drop Column SourceId columns to tables>
---- Sample Call: EXEC [dbo].[sp_Drop ColumnStagingColumns]
---- =============================================

--CREATE PROC [dbo].[sp_Drop ColumnStagingColumns]


--AS
--	BEGIN
--		exec sp_execute_remote
--		N'Company', -- This is the external data source name…
--		N'ALTER TABLE [ItemType] Drop Column [SourceId] nvarchar(50) NULL;

--		ALTER TABLE [LookupValue] Drop Column [SourceId] nvarchar(50) NULL;

--		ALTER TABLE [Customer] Drop Column [SourceId] nvarchar(50) NULL;

--		ALTER TABLE [User] Drop Column [SourceId] nvarchar(50) NULL;

--		ALTER TABLE [Vendor] Drop Column [SourceId] nvarchar(50) NULL;

--		ALTER TABLE [InventoryItem] Drop Column [SourceId] nvarchar(50) NULL;

--		ALTER TABLE [InventoryItemStone] Drop Column [SourceId] nvarchar(50) NULL;

--		ALTER TABLE [Transaction] Drop Column [SourceId] nvarchar(50) NULL;

--		ALTER TABLE [GunLog]  Drop Column [SourceId] nvarchar(50) NULL;

--		ALTER TABLE [TransactionCheckoutHistory]  Drop Column [SourceId] nvarchar(50) NULL;

--		ALTER TABLE [PoliceConfiscateDetail]  Drop Column [SourceId] nvarchar(50) NULL;

--		ALTER TABLE [TransactionVersion]  Drop Column [SourceId] nvarchar(50) NULL;'
--	END
--GO
/****** Object:  StoredProcedure [dbo].[sp_DropStagingColumns]    Script Date: 6/13/2018 9:39:35 AM ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO

---- =============================================
---- Author:		Syed Mohsin
---- Create date: <8/4/2016>
---- Description:	<This sp will drop SourceId columns from tables>
---- Sample Call: EXEC [dbo].[sp_DropStagingColumns]
---- =============================================

--CREATE PROC [dbo].[sp_DropStagingColumns]


--AS
--	BEGIN
--	exec sp_execute_remote
--	N'Company', -- This is the external data source name…
--	N'ALTER TABLE [ItemType] Drop Column [SourceId;

--	ALTER TABLE [LookupValue] Drop Column [SourceId];

--	ALTER TABLE [Customer] Drop Column [SourceId];

--	ALTER TABLE [User] Drop Column [SourceId];

--	ALTER TABLE [Vendor] Drop Column [SourceId];

--	ALTER TABLE [InventoryItem] Drop Column [SourceId];

--	ALTER TABLE [InventoryItemStone] Drop Column [SourceId];

--	ALTER TABLE [Transaction] Drop Column [SourceId];

--	ALTER TABLE [GunLog]  Drop Column [SourceId];

--	ALTER TABLE [TransactionCheckoutHistory]  Drop Column [SourceId];

--	ALTER TABLE [PoliceConfiscateDetail]  Drop Column [SourceId];

--	ALTER TABLE [TransactionVersion]  Drop Column [SourceId];'

--	END
--GO
/****** Object:  StoredProcedure [dbo].[StoreBinExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO

---- =============================================
---- Author:		Nadeem Akhtar
---- Create date: <8/11/2016>
---- Description:	<Extracts Bin values >
---- Sample Call: EXEC [dbo].[StoreBinExtract] 8

---- Updated By: Syed Mohsin
---- Update date: <8/12/2016>
---- Comments: Corrected mapping for columns and Drop Columned join conditions to meet the design logic.
----			 Drop Columned Storeid parameter for Source StoreId
---- =============================================

--CREATE Proc [dbo].[StoreBinExtract]
--@StoreID int
--as
--BEGIN
--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
--select distinct

--	LookupTypeID =
--		(
--		select LookupTypeId from
--		CEXT_LookupType s
--		where LookupTypeName='Bin'
--		),

--	cast(BIN as nvarchar(50))	as	value	,
--	GetDate() as	CreatedDate,
--	GetDate() as	UpdatedDate,
--	1 as CreatedBy, --Drop Column in package
--	1 as UpdatedBy, --Drop Column in package
--	0 as IsDeleted,
--	1  as StoreId --Drop Column in package

-- from
--(
--SELECT BIN FROM
-- EXT_items s WHERE Sto_Pk = @StoreID
-- Union All
-- SELECT BIN FROM
-- EXT_sitems s WHERE Sto_PK = @StoreID
-- ) a
-- WHERE Len(Bin) >0

--END
--GO
/****** Object:  StoredProcedure [dbo].[StoreItemTypeMissingRecordsToInsert]    Script Date: 6/13/2018 9:39:35 AM ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO



--CREATE proc [dbo].[StoreItemTypeMissingRecordsToInsert]
--@SourceStoreId int, @TargetStoreId int
--as
--BEGIN
--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

--select DISTINCT l1.lv1_pk as SourceId,
--		0 as ParentId,
--		0 as IsDeleted,
--		0 as IsPostToGunLog,
--		0 as IsHandGun,
--		0 as HoldDays,
--		0 as MinimumAge,
--		CAST(LTRIM(RTRIM(l1.DESCRIPT)) as nvarchar(15)) as NCICCode,
--		CAST(LTRIM(RTRIM(l1.DESCRIPT)) as nvarchar(15)) as LocalCode,
--		@TargetStoreId as Storeid,
--		GETDATE() as CreatedDate,
--		it.ItemTypeId,
--		l1.sto_pk
--from EXT_Level1 l1
--inner join EXT_items i on i.lv1_ID = l1.lv1_ID
--left join EXT_Level_N ln on ln.Lv_ID = l1.lv1_ID
--inner join CEXT_itemtype it on it.sourceid = l1.lv1_pk
--where ln.LVN_id IS NULL and i.Sto_Pk = @SourceStoreId

--UNION

--select DISTINCT l2.lv2_pk as SourceId,
--		l2.lv1_Parent as ParentId,
--		0 as IsDeleted,
--		0 as IsPostToGunLog,
--		0 as IsHandGun,
--		0 as HoldDays,
--		0 as MinimumAge,
--		cast(LTRIM(RTRIM(l2.DESCRIPT)) as nvarchar(15)) as NCICCode,
--		CAST(LTRIM(RTRIM(l2.DESCRIPT)) as nvarchar(15)) as LocalCode,
--		@TargetStoreId as Storeid,
--		GETDATE() as CreatedDate,
--		it.ItemTypeId,
--		l2.sto_pk
--from EXT_Level2 l2
--inner join EXT_items i on i.lv2_ID = l2.lv2_ID
--left join EXT_Level_N ln on ln.Lv_ID = l2.lv2_ID
--inner join cEXT_itemtype it on it.sourceid = l2.lv2_pk
--where ln.LVN_id IS NULL and i.Sto_Pk = @SourceStoreId

--UNION

--select DISTINCT l3.lv3_pk as SourceId,
--		l3.lv2_Parent as ParentId,
--		0 as IsDeleted,
--		0 as IsPostToGunLog,
--		0 as IsHandGun,
--		0 as HoldDays,
--		0 as MinimumAge,
--		CAST(LTRIM(RTRIM(l3.DESCRIPT)) as nvarchar(15)) as NCICCode,
--		CAST(LTRIM(RTRIM(l3.DESCRIPT)) as nvarchar(15)) as LocalCode,
--		@TargetStoreId as Storeid,
--		GETDATE() as CreatedDate,
--		it.ItemTypeId,
--		l3.sto_pk
--from EXT_Level3 l3
--inner join EXT_items i on i.lv3_ID = l3.lv3_ID
--left join EXT_Level_N ln on ln.Lv_ID = l3.lv3_ID
--inner join cEXT_itemtype it on it.sourceid = l3.lv3_pk
--where ln.LVN_id IS NULL and i.Sto_Pk = @SourceStoreId

--UNION

--select DISTINCT l4.lv4_PK as SourceId,
--		l4.lv3_Parent as ParentId,
--		0 as IsDeleted,
--		0 as IsPostToGunLog,
--		0 as IsHandGun,
--		0 as HoldDays,
--		0 as MinimumAge,
--		CAST(LTRIM(RTRIM(l4.DESCRIPT)) as nvarchar(15)) as NCICCode,
--		CAST(LTRIM(RTRIM(l4.DESCRIPT)) as nvarchar(15)) as LocalCode,
--		@TargetStoreId as Storeid,
--		GETDATE() as CreatedDate,
--		it.ItemTypeId,
--		l4.sto_pk
--from EXT_Level4 l4
--inner join EXT_items i on i.lv4_ID = l4.lv4_ID
--left join EXT_Level_N ln on ln.Lv_ID = l4.lv4_ID
--inner join EXT_itemtype it on it.sourceid = l4.lv4_pk
--where ln.LVN_id IS NULL and i.Sto_Pk = @SourceStoreId

--END
--GO
/****** Object:  StoredProcedure [dbo].[StoreItemTypeRecordsToInsert]    Script Date: 6/13/2018 9:39:35 AM ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO



--CREATE proc [dbo].[StoreItemTypeRecordsToInsert]
--@SourceStoreId int, @TargetStoreId int
--as
--BEGIN
--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
--Select
--	t.ItemTypeId as ItemTypeId,
--	0 as IsDeleted,
--	@TargetStoreId as StoreID,
--	cast(Local_Code as nvarchar(15)) as LocalCode,
--	cast(NCIC_Code as nvarchar(15)) as NCICCode,
--	GetDate() as CreatedDate,
--	1 as CreatedBy, --Drop Column in package
--	GetDate() as UpdatedDate,
--	u.UserId as UpdatedBy,
--	Post2GunLog as IsPostToGunLog,
--	HandGun as IsHandGun,
--	HoldGunDays as HoldDays,
--	Min_Age as MinimumAge

--from EXT_Level_N as LN
--	inner join CEXT_ItemType t
--		on Ln.Level_FK = t.SourceID
--	left outer join CEXT_User u
--		on LN.LastUpdatedUSR_ID = u.SourceId
--	WHERE LN.sto_pk = @SourceStoreId
--END
--GO
/****** Object:  StoredProcedure [dbo].[SystemOptionsExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO

--CREATE PROCEDURE [dbo].[SystemOptionsExtract]
--	@SourceStoreId as int=0
--AS
--BEGIN
--	-- SET NOCOUNT ON Drop Columned to prevent extra result sets from
--	-- interfering with SELECT statements.
--	SET NOCOUNT ON;
--	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

----Clear Staging table
--TRUNCATE TABLE Stg_SysOptions

---- Select from SysInfo1
--INSERT INTO Stg_SysOptions
--SELECT
--CASE WHEN (SystemOptionKey= 'ExtendLay') THEN
--	SystemOptionKey+'_'+SystemOptionValue
--ELSE
--	SystemOptionKey
--END AS SystemOptionKey, SystemOptionValue
--FROM
--(SELECT CAST(AllGunsAmt as varchar(100)) AS AllGunsAmt
--	,CAST(backpaymnt as varchar(100)) AS backpaymnt
--	,CAST(BuyHoldDay as varchar(100)) AS BuyHoldDay
--	,CAST(CntWeekend as varchar(100)) AS CntWeekend
--	,CAST(CtTaxRate as varchar(100)) AS CtTaxRate
--	,CAST(EncodeCost as varchar(100)) AS EncodeCost
--	,CAST(EntPawnDat as varchar(100)) AS EntPawnDat
--	,CAST(ExtendLay as varchar(100)) AS ExtendLay
--	,CAST(FedGunNum as varchar(100)) AS FedGunNum
--	,CAST(FuturePay as varchar(100)) AS FuturePay
--	,CAST(Gracedays as varchar(100)) AS Gracedays
--	,CAST(GunAmt as varchar(100)) AS GunAmt
--	,CAST(GunLog as varchar(100)) AS GunLog
--	,CAST(layawayday as varchar(100)) AS layawayday
--	,CAST(LayawayPct as varchar(100)) AS LayawayPct
--	,CAST(LayLabel as varchar(100)) AS LayLabel
--	,CAST(LcTaxRate as varchar(100)) AS LcTaxRate
--	,CAST(Losttikamt as varchar(100)) AS Losttikamt
--	,CAST(Losttikchg as varchar(100)) AS Losttikchg
--	,CAST(MinAge as varchar(100)) AS MinAge
--	,CAST(NonInvCost as varchar(100)) AS NonInvCost
--	,CAST(OverGunSal as varchar(100)) AS OverGunSal
--	,CAST(PartPay as varchar(100)) AS PartPay
--	,CAST(PawnDays as varchar(100)) AS PawnDays
--	,CAST(PawnRecpt as varchar(100)) AS PawnRecpt
--	,CAST(PoliceANum as varchar(100)) AS PoliceANum
--	,CAST(PoliceDept as varchar(100)) AS PoliceDept
--	,CAST(PricTagPaw as varchar(100)) AS PricTagPaw
--	,CAST(PrinLower as varchar(100)) AS PrinLower
--	,CAST(PrnSaleRec as varchar(100)) AS PrnSaleRec
--	,CAST(ProRepStrt as varchar(100)) AS ProRepStrt
--	,CAST(ReprntTick as varchar(100)) AS ReprntTick
--	,CAST(StTaxRate as varchar(100)) AS StTaxRate
--	,CAST(SamPawnPur as varchar(100)) AS SamPawnPur
--	,CAST(PERIOD as varchar(100)) AS Period
--FROM EXT_SysInfo1 WHERE STO_PK=@SourceStoreId
--)si1
--unpivot
--(
--SystemOptionValue for SystemOptionKey IN
--(
--	AllGunsAmt
--	,backpaymnt
--	,BuyHoldDay
--	,CntWeekend
--	,CtTaxRate
--	,EncodeCost
--	,EntPawnDat
--	,ExtendLay
--	,FedGunNum
--	,FuturePay
--	,Gracedays
--	,GunAmt
--	,GunLog
--	,layawayday
--	,LayawayPct
--	,LayLabel
--	,LcTaxRate
--	,Losttikamt
--	,Losttikchg
--	,MinAge
--	,NonInvCost
--	,OverGunSal
--	,PartPay
--	,PawnDays
--	,PawnRecpt
--	,PoliceANum
--	,PoliceDept
--	,PricTagPaw
--	,PrinLower
--	,PrnSaleRec
--	,ProRepStrt
--	,ReprntTick
--	,StTaxRate
--	,SamPawnPur
--	,Period
--)
--) unpiv;

---- Select from SysInfo2
--INSERT INTO Stg_SysOptions
--SELECT
--CASE WHEN (SystemOptionKey= 'DepositTax') THEN
--	SystemOptionKey+'_'+SystemOptionValue
--ELSE
--	SystemOptionKey
--END AS SystemOptionKey,

--CASE WHEN (SystemOptionKey= 'DepositTax')
--THEN 'True'
--ELSE SystemOptionValue
--END AS SystemOptionValue

--FROM
--(SELECT CAST(HandGunPwn as varchar(100)) AS HandGunPwn
--,CAST(HandGunDPW as varchar(100)) AS HandGunDPW
--,CAST(CustCred as varchar(100)) AS CustCred
--,CAST(GoldWeight as varchar(100)) AS GoldWeight
--,CAST(GoldWtSale as varchar(100)) AS GoldWtSale
--,CAST(PoliceAmt as varchar(100)) AS PoliceAmt
--,CAST(PoliceEmployer as varchar(100)) AS PoliceEmployer
--,CAST(RingLabel as varchar(100)) AS RingLabel
--,CAST(EatTax as varchar(100)) AS EatTax
--,CAST(DepositTax as varchar(100)) AS DepositTax
--,CAST(monthchrg as varchar(100)) AS monthchrg
--,CAST(PawnDaysG as varchar(100)) AS PawnDaysG
--,CAST(PawnDaysJ as varchar(100)) AS PawnDaysJ
--,CAST(BuyHoldG as varchar(100)) AS BuyHoldG
--,CAST(BuyHoldJ as varchar(100)) AS BuyHoldJ
--,CAST(PullGrace as varchar(100)) AS PullGrace
--,CAST(PawnBuyNum as varchar(100)) AS PawnBuyNum
--,CAST(PawnBuyDat as varchar(100)) AS PawnBuyDat
--,CAST(ScrapOther as varchar(100)) AS ScrapOther
--,CAST(PayRepawn as varchar(100)) AS PayRepawn
--,CAST(NewTickNum as varchar(100)) AS NewTickNum
--,CAST(PawRecBlan as varchar(100)) AS PawRecBlan
--,CAST(Prorate as varchar(100)) AS Prorate
--,CAST(ProUpToDay as varchar(100)) AS ProUpToDay
--,CAST(TexasMinPr as varchar(100)) AS TexasMinPr
--,CAST(ProratSChg as varchar(100)) AS ProratSChg
--,CAST(ProStart as varchar(100)) AS ProStart
--,CAST(RateOnePer as varchar(100)) AS RateOnePer
--,CAST(RateOnePro as varchar(100)) AS RateOnePro
--,CAST(RateFeePer as varchar(100)) AS RateFeePer
--,CAST(RateFeePro as varchar(100)) AS RateFeePro
--,CAST(SaleEnCost as varchar(100)) AS SaleEnCost
--,CAST(EncodeCent as varchar(100)) AS EncodeCent
--,CAST(EncodeDorp as varchar(100)) AS EncodeDorp
--,CAST(EncodeRept as varchar(100)) AS EncodeRept
--,CAST(FirearmFeeRedeemOnly as varchar(100)) AS FirearmFeeRedeemOnly
--FROM EXT_SysInfo2 WHERE STO_PK=@SourceStoreId
--)si1
--unpivot
--	(SystemOptionValue for SystemOptionKey IN
--		(HandGunPwn
--		,HandGunDPW
--		,CustCred
--		,GoldWeight
--		,GoldWtSale
--		,PoliceAmt
--		,PoliceEmployer
--		,RingLabel
--		,EatTax
--		,DepositTax
--		,monthchrg
--		,PawnDaysG
--		,PawnDaysJ
--		,BuyHoldG
--		,BuyHoldJ
--		,PullGrace
--		,PawnBuyNum
--		,PawnBuyDat
--		,ScrapOther
--		,PayRepawn
--		,NewTickNum
--		,PawRecBlan
--		,Prorate
--		,ProUpToDay
--		,TexasMinPr
--		,ProratSChg
--		,ProStart
--		,RateOnePer
--		,RateOnePro
--		,RateFeePer
--		,RateFeePro
--		,SaleEnCost
--		,EncodeCent
--		,EncodeDorp
--		,EncodeRept
--		,FirearmFeeRedeemOnly)
--	) unpiv;


---- Select from SysInfo4
--INSERT INTO Stg_SysOptions
--SELECT
--CASE WHEN (SystemOptionKey IN ('JewLabelType','EncCostMinInvLbl','InvLabelDate','PLblDateIO','LayCred')) THEN
--	SystemOptionKey+'_'+SystemOptionValue
--ELSE
--	SystemOptionKey
--END AS SystemOptionKey, SystemOptionValue
--FROM
--	(SELECT CAST(Auto_4473 as varchar(100)) AS Auto_4473
--		,CAST(GunHoldLbl as varchar(100)) AS GunHoldLbl
--		,CAST(JewLabelType as varchar(100)) AS JewLabelType
--		,CAST(PrtPettyCashReceipt as varchar(100)) AS PrtPettyCashReceipt
--		,CAST(EncCostMinInvLbl as varchar(100)) AS EncCostMinInvLbl
--		,CAST(InvLabelDate as varchar(100)) AS InvLabelDate
--		,CAST(InvLabelYear2 as varchar(100)) AS InvLabelYear2
--		,CAST(APRFirearm as varchar(100)) AS APRFirearm
--		,CAST(GunFeeAmt as varchar(100)) AS GunFeeAmt
--		,CAST(RetSvcChg_FL as varchar(100)) AS RetSvcChg_FL
--		,CAST(SerialNumberCheck as varchar(100)) AS SerialNumberCheck
--		,CAST(ScrapJewelry as varchar(100)) AS ScrapJewelry
--		,CAST(SplitJewelry as varchar(100)) AS SplitJewelry
--		,CAST(SplitOther as varchar(100)) AS SplitOther
--		,CAST(chgcustonpay as varchar(100)) AS chgcustonpay
--		,CAST(PawnPayDays as varchar(100)) AS PawnPayDays
--		,CAST(ResetOTFonNewTic as varchar(100)) AS ResetOTFonNewTic
--		,CAST(TXMemorandum as varchar(100)) AS TXMemorandum
--		,CAST(PawnPayTag as varchar(100)) AS PawnPayTag
--		,CAST(PrtLgTicketNum as varchar(100)) AS PrtLgTicketNum
--		,CAST(PLblDateIO as varchar(100)) AS PLblDateIO
--		,CAST(LostTickRcpt as varchar(100)) AS LostTickRcpt
--		,CAST(LostTickPaymentRcpt as varchar(100)) AS LostTickPaymentRcpt
--		,CAST(PawnPayBlank as varchar(100)) AS PawnPayBlank
--		,CAST(AskPawnRcpt as varchar(100)) AS AskPawnRcpt
--		,CAST(EncCostPawnBuyLbl as varchar(100)) AS EncCostPawnBuyLbl
--		,CAST(ProRt as varchar(100)) AS ProRt
--		,CAST(TexasDailyProrate as varchar(100)) AS TexasDailyProrate
--		,CAST(LayDayExtn as varchar(100)) AS LayDayExtn
--		,CAST(LayCred as varchar(100)) AS LayCred
--		,CAST(Form8300Amount as varchar(100)) as Form8300Amount
--		,CAST(GunFeeEachPeriod as varchar(100)) as GunFeeEachPeriod
--	FROM EXT_SysInfo4 WHERE STO_PK=@SourceStoreId
--	)si1
--		unpivot
--			(SystemOptionValue for SystemOptionKey IN
--				(Auto_4473
--				,GunHoldLbl
--				,JewLabelType
--				,PrtPettyCashReceipt
--				,EncCostMinInvLbl
--				,InvLabelDate
--				,InvLabelYear2
--				,APRFirearm
--				,GunFeeAmt
--				,RetSvcChg_FL
--				,SerialNumberCheck
--				,ScrapJewelry
--				,SplitJewelry
--				,SplitOther
--				,chgcustonpay
--				,PawnPayDays
--				,ResetOTFonNewTic
--				,TXMemorandum
--				,PawnPayTag
--				,PrtLgTicketNum
--				,PLblDateIO
--				,LostTickRcpt
--				,LostTickPaymentRcpt
--				,PawnPayBlank
--				,AskPawnRcpt
--				,EncCostPawnBuyLbl
--				,ProRt
--				,TexasDailyProrate
--				,LayDayExtn
--				,LayCred
--				,Form8300Amount
--				,GunFeeEachPeriod)
--			) unpiv;

---- Select from Sy5_sysinfo5 This is an exceptional case for SYS_LabelPull mapping
--INSERT INTO Stg_SysOptions
--SELECT
--	RTrim(Sys5.sy5_Lookup) + '_' + CAST(Convert(decimal(1,0), (LTrim(Sys5.sy5_value))) as varchar) AS SystemOptionKey,
--Sys5.sy5_value as SystemOptionValue
--FROM EXT_sy5_SysInfo5 Sys5 WHERE Sys5.sy5_Lookup='SYS_LabelPull' AND Sys5.sto_pk=@SourceStoreId

---- Select from SS_PAWN
--INSERT INTO Stg_SysOptions
--SELECT SystemOptionKey, SystemOptionValue
--FROM
--	(SELECT CAST(TicketNum as varchar(100)) AS TicketNum
--	FROM EXT_SS_PAWN WHERE STO_PK=@SourceStoreId
--	)si1
--		unpivot
--			(SystemOptionValue for SystemOptionKey IN
--				(TicketNum)
--			) unpiv;

---- Select from SS_SALE
--INSERT INTO Stg_SysOptions
--SELECT SystemOptionKey, SystemOptionValue
--FROM
--	(SELECT CAST(ReceiptNum as varchar(100)) AS ReceiptNum
--	FROM EXT_SS_SALE WHERE STO_PK=@SourceStoreId
--	)si1
--		unpivot
--			(SystemOptionValue for SystemOptionKey IN
--				(ReceiptNum)
--			) unpiv;

---- Select from SS_INVENTNEW
--INSERT INTO Stg_SysOptions
--SELECT SystemOptionKey, SystemOptionValue
--FROM
--	(SELECT CAST(InvNumNew as varchar(100)) AS InvNumNew
--	FROM EXT_SS_INVENTNEW WHERE STO_PK=@SourceStoreId
--	)si1
--		unpivot
--			(SystemOptionValue for SystemOptionKey IN
--				(InvNumNew)
--			) unpiv;

---- Select from SS_PURCHAS
--INSERT INTO Stg_SysOptions
--SELECT SystemOptionKey, SystemOptionValue
--FROM
--	(SELECT CAST(PurchasNum as varchar(100)) AS PurchasNum
--	FROM EXT_SS_PURCHAS WHERE STO_PK=@SourceStoreId
--	)si1
--		unpivot
--			(SystemOptionValue for SystemOptionKey IN
--				(PurchasNum)
--			) unpiv;


---- Select from Contract
--INSERT INTO Stg_SysOptions
--SELECT SystemOptionKey, SystemOptionValue
--FROM
--	(SELECT CAST(EXT_Contract.SalesMsg as varchar(100)) AS SalesMsg,
--			CAST(EXT_Contract.LayawayMsg as varchar(100)) AS LayawayMsg
--	FROM EXT_Contract  WHERE EXT_Contract.sto_FK=@SourceStoreId
--	)si1
--		unpivot
--			(SystemOptionValue for SystemOptionKey IN
--				(SalesMsg,
--				LayawayMsg)
--			) unpiv;



---- Select from Sy5_sysinfo5
--SELECT Lkp.SourceKey, Lkp.TargetKey, so.ControlTypeId,
-- CASE WHEN so.ControlTypeId in (5,6) and LTrim(RTrim(Sys5.sy5_value)) = '0' THEN 'False'
--	  WHEN so.ControlTypeId in (5,6) and LTrim(RTrim(Sys5.sy5_value)) != '0' and lkp.TargetKey !='PAWN_OTHER_DAILY_SVCCHARG_TO_PRORATE' THEN 'True'
--	  WHEN so.ControlTypeId = 11 and LTrim(RTrim(Sys5.sy5_value)) = 'G' THEN '206'
--	  WHEN so.ControlTypeId = 11 and LTrim(RTrim(Sys5.sy5_value)) = 'O' THEN '207'
--	  WHEN so.ControlTypeId = 11 and LTrim(RTrim(Sys5.sy5_value)) = 'D' THEN '205'
--	  WHEN (Lkp.TargetKey LIKE '%DAY%' OR Lkp.TargetKey LIKE '%MIN_AGE%') and ISNUMERIC(Sys5.sy5_value) = 1 THEN CAST(CAST(CONVERT(decimal,LTrim(RTrim(Sys5.sy5_value))) as int) as varchar(500))
--	  WHEN lkp.TargetKey LIKE '%GENERAL_PRINTING%' AND so.ControlTypeId = 4 and CAST(CAST(CONVERT(decimal,LTrim(RTrim(Sys5.sy5_value))) as int) as varchar(500)) = '0' THEN 'False'
--	  WHEN lkp.TargetKey LIKE '%GENERAL_PRINTING%' AND so.ControlTypeId = 4 and CAST(CAST(CONVERT(decimal,LTrim(RTrim(Sys5.sy5_value))) as int) as varchar(500)) != '0' THEN 'True'
--	 WHEN lkp.TargetKey LIKE 'GENERAL_NUMBERING_SAME_NUMBER_FOR_PAWN_AND_PURCHASE_TICKETS' AND so.ControlTypeId = 4 and LTrim(RTrim(Sys5.sy5_value)) = '0' THEN 'False'
--	   WHEN lkp.TargetKey LIKE 'GENERAL_NUMBERING_SAME_NUMBER_FOR_PAWN_AND_PURCHASE_TICKETS' AND so.ControlTypeId = 4 and LTrim(RTrim(Sys5.sy5_value)) = '1' THEN 'True'
--	 --WHEN lkp.TargetKey LIKE 'GENERAL_SALES_TAX_COLLECT_LAYAWAY_AT_END' AND LTrim(RTrim(Sys5.sy5_value)) = '0' THEN 'False'
--	  --WHEN lkp.TargetKey LIKE 'GENERAL_SALES_TAX_COLLECT_LAYAWAY_AT_END' AND LTrim(RTrim(Sys5.sy5_value)) != '0' THEN 'True'
--	 WHEN lkp.TargetKey LIKE 'PAWN_OTHER_DAILY_MLA_RATE' AND LTrim(RTrim(Sys5.sy5_value)) = '0' THEN 'False'
--	  WHEN lkp.TargetKey LIKE 'PAWN_OTHER_DAILY_MLA_RATE' AND LTrim(RTrim(Sys5.sy5_value)) != '0' THEN 'True'
--	  ELSE LTrim(RTrim(Sys5.sy5_value))
--	  END
-- as SystemOptionValue, so.SystemOptionId
--FROM [Common].[SourceTargetKeyMapping] Lkp
--INNER JOIN EXT_sy5_SysInfo5 Sys5 ON Lkp.[SourceKey] = Sys5.sy5_Lookup AND Sys5.sto_pk=@SourceStoreId
--INNER JOIN CEXT_SystemOption so ON Lkp.TargetKey = so.SystemOptionKey
--INNER JOIN cEXT_ControlType ct ON so.ControlTypeId = ct.ControlTypeId
--WHERE Lkp.SourceTable = 'sy5_SysInfo5'
--UNION
----Final select for mapping
--SELECT opt.SystemOptionKey AS SourceKey, Lkp.TargetKey, so.ControlTypeId,
-- CASE WHEN so.ControlTypeId in (5,6) and LTrim(RTrim(opt.SystemOptionValue)) = '0' THEN 'False'
--	  WHEN so.ControlTypeId in (5,6) and LTrim(RTrim(opt.SystemOptionValue)) != '0' THEN 'True'
--	  WHEN so.ControlTypeId = 11 and LTrim(RTrim(opt.SystemOptionValue)) = 'G' THEN '206'
--	  WHEN so.ControlTypeId = 11 and LTrim(RTrim(opt.SystemOptionValue)) = 'O' THEN '207'
--	  WHEN so.ControlTypeId = 11 and LTrim(RTrim(opt.SystemOptionValue)) = 'D' THEN '205'
--	  WHEN (Lkp.TargetKey LIKE '%DAY%' OR Lkp.TargetKey LIKE '%MIN_AGE%') and ISNUMERIC(opt.SystemOptionValue) = 1 THEN CAST(CAST(CONVERT(decimal,LTrim(RTrim(opt.SystemOptionValue))) as int) as varchar(500))
--	  	  WHEN lkp.TargetKey LIKE '%GENERAL_PRINTING%' AND so.ControlTypeId = 4 and CAST(CAST(CONVERT(decimal,LTrim(RTrim(opt.SystemOptionValue))) as int) as varchar(500)) = '0' THEN 'False'
--	  WHEN lkp.TargetKey LIKE '%GENERAL_PRINTING%' AND so.ControlTypeId = 4 and CAST(CAST(CONVERT(decimal,LTrim(RTrim(opt.SystemOptionValue))) as int) as varchar(500)) != '0' THEN 'True'
--	  WHEN lkp.TargetKey LIKE 'GENERAL_NUMBERING_SAME_NUMBER_FOR_PAWN_AND_PURCHASE_TICKETS' AND so.ControlTypeId = 4 and LTrim(RTrim(opt.SystemOptionValue)) = '0' THEN 'False'
--	   WHEN lkp.TargetKey LIKE 'GENERAL_NUMBERING_SAME_NUMBER_FOR_PAWN_AND_PURCHASE_TICKETS' AND so.ControlTypeId = 4 and LTrim(RTrim(opt.SystemOptionValue)) = '1' THEN 'True'
--	 -- WHEN lkp.TargetKey LIKE 'GENERAL_SALES_TAX_COLLECT_LAYAWAY_AT_END' AND LTrim(RTrim(opt.SystemOptionValue)) = '0' THEN 'False'
--	 -- WHEN lkp.TargetKey LIKE 'GENERAL_SALES_TAX_COLLECT_LAYAWAY_AT_END' AND LTrim(RTrim(opt.SystemOptionValue)) != '0' THEN 'True'
--	  ELSE LTrim(RTrim(opt.SystemOptionValue))
--	  END
-- AS SystemOptionValue, so.SystemOptionId
--FROM Stg_SysOptions opt
--INNER JOIN Common.[SourceTargetKeyMapping] Lkp ON opt.SystemOptionKey = Lkp.[SourceKey]
--INNER JOIN CEXT_SystemOption so ON Lkp.TargetKey = so.SystemOptionKey
--INNER JOIN CEXT_ControlType ct ON so.ControlTypeId = ct.ControlTypeId

--END
--GO
/****** Object:  StoredProcedure [dbo].[SystemOptionsUpdate]    Script Date: 6/13/2018 9:39:35 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		Farrukh Ijaz
-- Create date: <9/01/2016>
-- Description:	<Update system option values in target store>
-- =============================================
CREATE PROCEDURE [dbo].[SystemOptionsUpdate]
	-- Drop Column the parameters for the stored procedure here
	@SystemOptionId as int
	,@StoreId as int
	,@SystemOptionValue varchar(2000)
	,@UpdatedDate as datetime
	,@UpdatedBy as int

AS
BEGIN

    -- Insert statements for procedure here
	UPDATE Company.dbo.StoreSystemOption SET
		SystemOptionValue = @SystemOptionValue,
		UpdatedBy = @UpdatedBy,
		UpdatedDate = @UpdatedDate
	WHERE SystemOptionId = @SystemOptionId
	AND StoreId = @StoreId
	--PRINT 1
END
GO
/****** Object:  StoredProcedure [dbo].[TenderExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO

---- =============================================
---- Author:		Syed Mohsin
---- Create date: <10/25/2016>
---- Description:	<Extracts data for ApplicationLookup Tender Types only>
---- Sample Call: EXEC [dbo].[TenderExtract] 8 , 180
---- =============================================

--CREATE Proc [dbo].[TenderExtract]
--@StoreID int, @TargetStoreId int
--as
--BEGIN
--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

--DECLARE @LookupTypeId int
--SET @LookupTypeId = (SELECT LookupTypeId FROM CEXT_LookupType where LookupTypeName = 'Tender Type')

--  SELECT
--	 c.sto_pk sto,
--	  @LookupTypeId as LookupTypeId,
--	  CAST(RTRIM(LTRIM(c.lc_Descript)) as nvarchar(50)) lc_Descript,
--	  c.lc_pk  as SourceId,
--	  1 as IsNew,
--	  GetDate() as CreatedDate,
--	  GetDate() as UpdatedDate,
--	1 as CreatedBy, --Drop Column in package
--	u.UserId as UpdatedBy,
--	  0 as IsDeleted

--	FROM EXT_Lookup_C  c
--inner join EXT_Lookup_B b on b.lb_pk = c.LB_FK and b.lb_type='T'
--inner join EXT_Lookup_N n on n.lc_FK = c.lc_pk and n.sto_pk = @StoreID
--LEFT JOIN CEXT_User u on u.SourceId = c.LastUpdatedUSR_ID
--LEFT JOIN CEXT_LookupValue lv on lv.Value = c.lc_Descript
--where n.Sto_PK = @StoreID and lv.LookupValueId IS NULL

--END
--GO
/****** Object:  StoredProcedure [dbo].[TransactionCheckoutBatchExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO

---- =============================================
---- Author:		<Farrukh Ijaz>
---- Create date: <09/12/2016>
---- Description:	<Extract Checkout Batch data from Acct table>
---- Sample Call: EXEC [dbo].[TransactionCheckoutBatchExtract] 8, 5
---- =============================================
--CREATE PROCEDURE [dbo].[TransactionCheckoutBatchExtract]
--	-- Drop Column the parameters for the stored procedure here
--	@SourceStoreId as int, @UtcTime int
--AS
--BEGIN
--	-- SET NOCOUNT ON Drop Columned to prevent extra result sets from
--	-- interfering with SELECT statements.
--	SET NOCOUNT ON;
--	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

--SELECT
--	tp.TransactionId
--	,tch.TransactionCheckoutHistoryID
--	,DATEADD(HOUR,@UtcTime,DATEin)AS CreatedDate
--	,tch.CreatedBy AS CreatedBy
--	,tp.TransactionPaymentId
--FROM EXT_Acct a
--INNER JOIN CEXT_TransactionPayment tp ON tp.PaymentTypeId not in (23,14) and a.Acct_PK=tp.SourceId
--INNER JOIN CEXT_TransactionCheckoutHistory tch ON a.Acct_PK = tch.SourceId
--WHERE [TYPE] IN ('P','B','SS','SL','PC','PD','PH','PP','PPL','PPP','PPU','PRP','PRU','PU','PV','PW','PX','SL','SLD','SLP','SLR','SLS','SLU','SLV','SLX')

--AND a.sto_pk=@SourceStoreId


--END
--GO
/****** Object:  StoredProcedure [dbo].[TransactionCheckoutHistoryExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO

---- =============================================
---- Author:		<Farrukh Ijaz>
---- Create date: <09/12/2016>
---- Description:	<Extract Checkout History data from Acct table>
---- Sample Call: EXEC [dbo].[TransactionCheckoutHistoryExtract] 8, 5
---- =============================================
--CREATE PROCEDURE [dbo].[TransactionCheckoutHistoryExtract]
--	-- Drop Column the parameters for the stored procedure here
--	@SourceStoreId as int, @UtcTime int
--AS
--BEGIN
--	-- SET NOCOUNT ON Drop Columned to prevent extra result sets from
--	-- interfering with SELECT statements.
--	SET NOCOUNT ON;
--	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

--DECLARE @tender int
--SET @tender = (select LookupTypeId from CEXT_LookupType where LookupTypeName='Tender Type')

--SELECT
--	TENDERTYP1
--	--,tt1.lc_Descript
--	,lv1.LookupValueId AS TenderTypeId1
--	,TENDERTYP2
--	--,tt2.lc_Descript
--	,lv2.LookupValueId AS TenderTypeId2
--	,TENDERAMT1
--	,TENDERAMT2
--	,a.AMOUNT+STATETAX+COUNTYTAX+LOCALTAX as PaymentAmount
--	,Cus_Credit as CustomerCredit
--	,TENDCHANGE
--	,DATEADD(HOUR,@UtcTime,DATEin) AS CreatedDate
--	,u.UserId AS CreatedBy
--	,Acct_PK AS SourceId
--FROM EXT_Acct a
----LEFT JOIN Company.dbo.[User] u ON a.CUS_FK=u.SourceId
--LEFT JOIN EXT_users us
--on us.USR_PK = a.Usr_FK and us.USR_STORE = @SourceStoreId
--LEFT JOIN CEXT_User u
--on u.SourceId = us.USR_ID


--LEFT JOIN EXT_Lookup_C tt1 ON a.TENDERTYP1 = tt1.lc_pk
--LEFT JOIN EXT_Lookup_C tt2 ON a.TENDERTYP2 = tt2.lc_pk
--LEFT JOIN CEXT_LookupValue lv1 ON tt1.lc_Descript = lv1.Value and lv1.LookupTypeId = @tender
--LEFT JOIN CEXT_LookupValue lv2 ON tt2.lc_Descript = lv2.Value and lv2.LookupTypeId = @tender
--WHERE [TYPE] IN ('P','B','SS','SL','PC','PD','PH','PP','PPL','PPP','PPU','PRP','PRU','PU','PV','PW','PX','SL','SLD','SLP','SLR','SLS','SLU','SLV','SLX')

--AND a.sto_pk=@SourceStoreId


--END
--GO
/****** Object:  StoredProcedure [dbo].[TransactionCheckoutPaymentHistoryExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO

---- =============================================
---- Author:		<Farrukh Ijaz>
---- Create date: <09/12/2016>
---- Description:	<Extract TransactionCheckoutPaymentHistory data from Acct table>
---- Sample Call: EXEC [dbo].[TransactionCheckoutPaymentHistoryExtract] 8
---- =============================================
--CREATE PROCEDURE [dbo].[TransactionCheckoutPaymentHistoryExtract]
--	-- Drop Column the parameters for the stored procedure here
--	@SourceStoreId as int, @UtcTime int
--AS
--BEGIN
--	-- SET NOCOUNT ON Drop Columned to prevent extra result sets from
--	-- interfering with SELECT statements.
--	SET NOCOUNT ON;
--	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

--SELECT
--	t.TransactionID
--	,t.TransactionTypeId
--	,tp.TransactionPaymentId
--	,tcb.TransactionCheckoutBatchID
--	,tch.TransactionCheckoutHistoryID
--	,1 AS BatchId
--	,DATEADD(HOUR,@UtcTime,tch.CreatedDate) AS CreatedDate
--	,tch.CreatedBy
--FROM EXT_Acct a
--LEFT JOIN CEXT_TransactionPayment tp ON a.Acct_PK= tp.SourceId
--INNER JOIN CEXT_Transaction t on
--t.TicketNumber = a.TICKETNUM
--AND (
--		(t.TransactionTypeId in (1,2) and  (a.TYPE like 'P%' OR a.TYPE like 'B%'))
--		OR
--		(t.TransactionTypeId in (3,5) and a.TYPE like 'S%')
--	)
--INNER JOIN CEXT_TransactionCheckoutHistory tch ON tp.PaymentTypeId not in (23,14) and a.Acct_PK = tch.SourceId
--INNER JOIN CEXT_TransactionCheckoutBatch tcb ON tch.TransactionCheckoutHistoryID = tcb.TransactionCreditHistoryID
--WHERE [TYPE] IN ('P','B','SS','SL','PC','PD','PH','PP','PPL','PPP','PPU','PRP','PRU','PU','PV','PW','PX','SL','SLD','SLP','SLR','SLS','SLU','SLV','SLX')


--AND a.sto_pk=@SourceStoreId


--END
--GO
/****** Object:  StoredProcedure [dbo].[TransactionItemVersionExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO

---- =============================================
---- Author:		<Syed Mohsin>
---- Create date: <09/26/2016>
---- Description:	<Extract TransactionItemVersions by extracting data from source>
---- Sample Call: EXEC [dbo].[TransactionItemVersionExtract] 371
---- =============================================
--CREATE PROCEDURE [dbo].[TransactionItemVersionExtract]
--	-- Drop Column the parameters for the stored procedure here
--	@TargetStoreId as int
--AS
--BEGIN
--	-- SET NOCOUNT ON Drop Columned to prevent extra result sets from
--	-- interfering with SELECT statements.
--	SET NOCOUNT ON;
--	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

----	DECLARE @myTable as TABLE
----(
----	InventoryItemVersionId  int,
----	InventoryItemId int
----)
----INSERT INTO @myTable
----select MAX(InventoryItemVersionId), InventoryItemId from Company.dbo.InventoryItemVersion group by InventoryItemId

--SELECT *,iif(Num1 = 1,Stat,
--CASE trStat
--WHEN  23 THEN 1
--WHEN  26 THEN 6
--WHEN  22 THEN 7
--WHEN  5 THEN 1
--WHEN  4 THEN 4
--WHEN  21 THEN 6
--WHEN  45 THEN 7
--WHEN  15 THEN 15
--WHEN  22 THEN 22
--WHEN  2 THEN CASE transactiontypeid WHEN 3 THEN 19 WHEN 5 THEN 2 END
--ELSE stat
--END

--) StatusId

--FROM (
--select tv.TransactionVersionId,
--			--iv.InventoryItemVersionId,
--			iif(tr.TransactionTypeId IN (1,2),NULL,s.Quantity) as OrigQuantity,
--			s.TransactionItemId,
--			s.TransactionId,
--			s.statusid as Stat,
--			s.InventoryItemLotId,
--			s.Cost,
--			s.Quantity,
--			s.Comment,
--			s.DiscountPercentage,
--			s.IsTaxExempt,
--			s.Resale,
--			s.NonInventoryItemDescription,
--			s.NonInventoryNumber,
--			tv.CreatedDate,
--			tv.CreatedBy,
--			s.JewelryWeightUnitId,
--			s.BinNumberId,
--			s.Tax,
--			s.GunProcessingFee,
--			tr.statusid as TrStat,
--			tr.transactiontypeid,
--			ROW_NUMBER() over(partition by tv.TransactionId order by tv.transactionversionid desc)as Num1
--			from CEXT_TransactionItem s
--			inner join CEXT_Transaction tr on tr.TransactionId = s.TransactionId
--			left join  CEXT_TransactionVersion tv on tv.TransactionId = s.TransactionId
--			left join CEXT_InventoryItemLot il on il.InventoryItemLotId = s.InventoryItemLotId
--			--left join @myTable iv on iv.InventoryItemId = il.InventoryItemId
--			WHERE tr.StoreId=@TargetStoreId
--			)q

--END
--GO
/****** Object:  StoredProcedure [dbo].[TransactionPawnBuyVersionExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO

---- =============================================
---- Author:		<Syed Mohsin>
---- Create date: <09/21/2017>
---- Description:	<Extract History Transaction Versions for Pawns converted to buy>
---- Sample Call: EXEC [dbo].[TransactionPawnBuyVersionExtract] 182 , 1 , 5
---- =============================================
--CREATE PROCEDURE [dbo].[TransactionPawnBuyVersionExtract]
--	-- Drop Column the parameters for the stored procedure here
--	@TargetStoreId as int, @SourceStoreId as int, @UtcOffset as int
--AS
--BEGIN
--	-- SET NOCOUNT ON Drop Columned to prevent extra result sets from
--	-- interfering with SELECT statements.
--	SET NOCOUNT ON;
--	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

--SELECT   t.TransactionId ,
--        1 TransactionTypeId,
--        t.StoreId ,
--        t.CustomerId ,
--        t.TicketNumber ,
--        t.Duration ,
--        t.Period ,
--        DATEADD(HOUR,@UtcOffset, CONVERT(DATETIME, a.DATEin)) InDate,
--        t.OutDate ,
--        a.AMOUNT * -1 as Amount,
--        t.RateTableId ,
--        t.Note ,
--        t.Message ,
--        CASE WHEN a.TYPE='P' THEN 23 WHEN a.TYPE = 'PV' THEN 26 END as StatusId,
--        0 IsActive,
--        0 IsDeleted,
--        0 IsQueued,
--        DATEADD(HOUR,@UtcOffset, CONVERT(DATETIME, a.DATEin)) CreatedDate,
--        t.CreatedBy
--FROM    EXT_Acct a
--       INNER JOIN CEXT_Transaction t ON t.TicketNumber = a.TICKETNUM
--                                     AND t.TransactionTypeId = 2

--WHERE   TYPE = 'P' OR TYPE = 'PV' AND StoreId=@TargetStoreId
--        AND TICKETNUM IN ( SELECT   TICKETNUM
--                           FROM     EXT_Acct
--                           WHERE    TYPE = 'B' and sto_pk=@SourceStoreId)

--ORDER BY a.Acct_PK ASC

--END
--GO
/****** Object:  StoredProcedure [dbo].[TransactionPaymentBuyExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO

---- =============================================
---- Author:		<Syed Mohsin>
---- Create date: <09/14/2017>
---- Description:	<Extract TransactionPayment data for pawns converted into buys>
---- Sample Call: EXEC [dbo].[TransactionPaymentBuyExtract] 1 , 5

---- =============================================
--CREATE PROCEDURE [dbo].[TransactionPaymentBuyExtract]
--	-- Drop Column the parameters for the stored procedure here
--	@SourceStoreId as int, @UtcTime int
--AS
--BEGIN
--	-- SET NOCOUNT ON Drop Columned to prevent extra result sets from
--	-- interfering with SELECT statements.
--	SET NOCOUNT ON;
--	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;


--DECLARE @BatchId int
--SET @BatchId = (SELECT ISNULL(MAX(batchid),0) from CEXT_TransactionPayment)

--SELECT * FROM (
--SELECT *,row_number() over(partition by SourceId,ticketnum,TransactionId order by TransactionId desc) as Num2 FROM (
--SELECT
--	row_number() over(partition by a.sto_pk, a.[TYPE], a.TICKETNUM order by Acct_PK) as NUM
--	,[TYPE]
--	,CASE WHEN [TYPE] LIKE 'P%' THEN
--		 'PAWN'
--	WHEN [TYPE] LIKE 'SL%' THEN
--		 'LAYAWAY'
--	ELSE '' END AS PaymentTransType
--	,a.TICKETNUM
--	,a.amount-LostTicket-RTsvc as AMOUNT
--	,t.TransactionId
--	,iif(a.TYPE='PPP',4,TargetPaymentType) as PaymentTypeId
--	,DATEADD(HOUR,@UtcTime,a.DATEin) DATEin
--	,STATETAX+COUNTYTAX+LOCALTAX AS Tax
--	,DATEADD(HOUR,@UtcTime,a.DATEin) AS CreatedDate
--	,u.UserId
--	,Acct_PK
--	,c.CustomerId
--	--,TENDERTYP1
--	--,tt1.lc_Descript AS lc_Descript1
--	,lv1.LookupValueId AS TenderType1
--	--,TENDERTYP2
--	--,tt2.lc_Descript AS lc_Descript2
--	,lv2.LookupValueId AS TenderType2
--	,TENDERAMT1
--	,TENDERAMT2
--	,Acct_PK AS SourceId
--	,DATEADD(DAY,-p.NUMDAYS,DATEADD(HOUR,@UtcTime,p.CHARGEDATE)) as CHARGEDATE
--	, @BatchId + ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as BatchId
--	,[TargetStatusId] as VALUE
--	,CAST(a.RTsvc as decimal(12,3)) as ServiceFee
--	,CAST(a.RTint as decimal(12,3)) as InterestPercentage
--	,CAST(a.RTonetime as decimal(12,3)) as OneTimeFee
--	,CAST(a.RTper as decimal(12,3)) as FeePerPeriod
--	,CAST(a.Gun as decimal(12,3)) as FirearmFee
--	,a.Cus_Credit as PaidCustomerCredit
--FROM EXT_Acct a
----LEFT JOIN Company.dbo.Customer c ON a.CUS_FK=c.SourceId
----LEFT JOIN Company.dbo.[User] u ON a.CUS_FK=u.SourceId

--INNER JOIN [PaymentTypeMapping] tm ON
--tm.[SourcePaymentType] = CASE a.[TYPE]  WHEN 'PPP' THEN 'PH' ELSE a.[TYPE] END

--INNER JOIN CEXT_Transaction t ON a.TICKETNUM = t.TicketNumber AND
--	(CASE WHEN a.[TYPE] LIKE 'P%' THEN 2 END) = t.TransactionTypeId

--LEFT JOIN EXT_cust cst
--ON cst.Cus_PK = a.CUS_FK
--LEFT JOIN CEXT_Customer c
--ON c.SourceId = cst.Cus_id

--LEFT JOIN EXT_users us
--on us.USR_PK = a.Usr_FK and us.USR_STORE = @SourceStoreId
--LEFT JOIN CEXT_User u
--on u.SourceId = us.USR_ID

--LEFT JOIN EXT_Lookup_C tt1 ON a.TENDERTYP1 = tt1.lc_pk
--LEFT JOIN EXT_Lookup_C tt2 ON a.TENDERTYP2 = tt2.lc_pk
--LEFT JOIN CEXT_.LookupValue lv1 ON tt1.lc_Descript = lv1.Value and tt1.lc_pk = lv1.SourceId
--LEFT JOIN CEXT_.LookupValue lv2 ON tt2.lc_Descript = lv2.Value and tt2.lc_pk = lv2.SourceId
--LEFT JOIN EXT_Pawn p ON a.sto_pk = p.STORE_NO AND a.TICKETNUM = p.TICKETNUM


--WHERE [TYPE] IN ('P','PC','PD','PH','PP','PPL','PPP','PPU','PRP','PRU','PU','PV','PW','PX','SL','SLD','SLP','SLR','SLS','SLU','SLV','SLX')
--AND a.sto_pk=@SourceStoreId -- and a.LostTicket =0
----OR ([TYPE] = 'P' AND a.AMOUNT<0))

--) payments
--WHERE [Type]!='P' OR ([TYPE]='P' AND NUM>1)
--) tr
--WHERE num2 = 1

--END
--GO
/****** Object:  StoredProcedure [dbo].[TransactionPaymentExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO

---- =============================================
---- Author:		<Farrukh Ijaz>
---- Create date: <09/12/2016>
---- Description:	<Extract TransactionPayment data from Acct table>
---- Sample Call: EXEC [dbo].[TransactionPaymentExtract] 1 , 5

---- Modified by: Syed Mohsin
---- Modifed Date: <12/06/2016>
---- Comments: Drop Columned mapping for newly Drop Columned columns

---- Modified by: Syed Mohsin
---- Modifed Date: <03/08/2017>
---- Comments: IF source payment type = PPP and amount = pawn.float amount then it is a partial payment.

---- Modified by: Syed Mohsin
---- Modifed Date: <04/05/2017>
---- Comments: Transaction and Status join is INNER. Moved to top
---- =============================================
--CREATE PROCEDURE [dbo].[TransactionPaymentExtract]
--	-- Drop Column the parameters for the stored procedure here
--	@SourceStoreId as int, @UtcTime int
--AS
--BEGIN
--	-- SET NOCOUNT ON Drop Columned to prevent extra result sets from
--	-- interfering with SELECT statements.
--	SET NOCOUNT ON;
--	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

--	DECLARE @Tender int = (
--select LookupTypeId from CEXT_LookupType where LookupTypeName='Tender Type')

--DECLARE @BatchId int
--SET @BatchId = (SELECT ISNULL(MAX(batchid),0) from CEXT_TransactionPayment)

--SELECT * FROM (
--SELECT *,row_number() over(partition by SourceId,ticketnum,TransactionId order by TransactionId desc) as Num2 FROM (
--SELECT
--	row_number() over(partition by a.sto_pk, a.[TYPE], a.TICKETNUM order by Acct_PK) as NUM

--	,[TYPE]
--	,CASE WHEN [TYPE] LIKE 'P%' THEN
--		 'PAWN'
--	WHEN [TYPE] LIKE 'SL%' THEN
--		 'LAYAWAY'
--	ELSE '' END AS PaymentTransType
--	,a.TICKETNUM
--	,iif(LostTicket=0,a.AMOUNT,a.amount-LostTicket) as AMOUNT
--	,t.TransactionId
--	,iif(a.TYPE='PPP' AND p.floatamt > 0 and p.floatamt = a.amount,1,TargetPaymentType) as PaymentTypeId
--	,DATEADD(HOUR,@UtcTime,a.DATEin) DATEin
--	,STATETAX+COUNTYTAX+LOCALTAX AS Tax
--	,DATEADD(HOUR,@UtcTime,a.DATEin) AS CreatedDate
--	,u.UserId
--	,Acct_PK
--	,c.CustomerId
--	--,TENDERTYP1
--	--,tt1.lc_Descript AS lc_Descript1
--	,lv1.LookupValueId AS TenderType1
--	--,TENDERTYP2
--	--,tt2.lc_Descript AS lc_Descript2
--	,lv2.LookupValueId AS TenderType2
--	,TENDERAMT1
--	,TENDERAMT2
--	,Acct_PK AS SourceId
--	,DATEADD(DAY,-p.NUMDAYS,DATEADD(HOUR,@UtcTime,p.OrigChgDate)) as CHARGEDATE
--	, @BatchId + ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as BatchId
--	,[TargetStatusId] as VALUE
--	,CAST(a.RTsvc as decimal(12,3)) as ServiceFee
--	,CAST(a.RTint as decimal(12,3)) as InterestPercentage
--	,CAST(a.RTonetime as decimal(12,3)) as OneTimeFee
--	,CAST(a.RTper as decimal(12,3)) as FeePerPeriod
--	,CAST(a.Gun as decimal(12,3)) as FirearmFee
--	,iif(a.TYPE like 'P%',CAST(a.OneTime as decimal(16,4)),NULL) as SysOptOneTimeFee
--	,CAST(CASE TargetPaymentType
--	WHEN 8 THEN 'Principal Lower'
--	WHEN 9 THEN 'Redemption'
--	ELSE NULL
--	END as nvarchar(50)) AS ValueOf14,
--	a.Cus_Credit as PaidCustomerCredit
--FROM EXT_Acct a
----LEFT JOIN Company.dbo.Customer c ON a.CUS_FK=c.SourceId
----LEFT JOIN Company.dbo.[User] u ON a.CUS_FK=u.SourceId

--INNER JOIN PaymentTypeMapping tm ON a.[TYPE] = tm.[SourcePaymentType]

--INNER JOIN CEXT_Transaction t ON a.TICKETNUM = t.TicketNumber AND
--	(CASE WHEN a.[TYPE] LIKE 'P%' THEN 1 WHEN [TYPE] LIKE 'SL%' THEN 5 END) = t.TransactionTypeId

--LEFT JOIN EXT_cust cst
--ON cst.Cus_PK = a.CUS_FK
--LEFT JOIN CEXT_Customer c
--ON c.SourceId = cst.Cus_id

--LEFT JOIN EXT_users us
--on us.USR_PK = a.Usr_FK and us.USR_STORE = @SourceStoreId
--LEFT JOIN CEXT_User u
--on u.SourceId = us.USR_ID

--LEFT JOIN EXT_Lookup_C tt1 ON a.TENDERTYP1 = tt1.lc_pk
--LEFT JOIN EXT_Lookup_C tt2 ON a.TENDERTYP2 = tt2.lc_pk
--LEFT JOIN CEXT_LookupValue lv1 ON tt1.lc_Descript = lv1.Value and lv1.lookuptypeid = @Tender
--LEFT JOIN CEXT_.LookupValue lv2 ON tt2.lc_Descript = lv2.Value and lv1.lookuptypeid = @Tender
--LEFT JOIN EXT_Pawn p ON a.sto_pk = p.STORE_NO AND a.TICKETNUM = p.TICKETNUM


--WHERE [TYPE] IN ('P','PC','PD','PH','PP','PPL','PPP','PPU','PRP','PRU','PU','PV','PW','PX','SL','SLD','SLP','SLR','SLS','SLU','SLV','SLX')
--AND a.sto_pk=@SourceStoreId -- and a.LostTicket =0
----OR ([TYPE] = 'P' AND a.AMOUNT<0))

--) payments
--WHERE [Type]!='P' OR ([TYPE]='P' AND NUM>1)
--) tr
--WHERE num2 = 1


--END
--GO
/****** Object:  StoredProcedure [dbo].[TransactionPaymentMissingExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO

---- =============================================
---- Author:		<Syed Mohsin>
---- Create date: <11/09/2017>
---- Description:	<Drop Column payments if total paid does not match>
---- Sample Call: EXEC [dbo].[TransactionPaymentMissingExtract] 182,1
---- =============================================
--CREATE PROCEDURE [dbo].[TransactionPaymentMissingExtract]
--	-- Drop Column the parameters for the stored procedure here
--	@TargetStoreId as int, @SourceStoreId as int
--AS
--BEGIN
--	-- SET NOCOUNT ON Drop Columned to prevent extra result sets from
--	-- interfering with SELECT statements.
--	SET NOCOUNT ON;
--	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

--DECLARE @Interval int
--SET @Interval = (SELECT SystemOptionValue from CEXT_.SystemOption so inner join CEXT_StoreSystemOption sso on so.SystemOptionId = sso.SystemOptionId
--WHERE SystemOptionKey like 'PAWNBUY_DURATION_SVC_CHG_PRD' and sso.StoreId=@TargetStoreId)

--DECLARE @Days int
--SET @Days = (SELECT CASE SystemOptionValue WHEN 'False' Then @Interval ELSE @Interval * 30 END as Dayss from CEXT_SystemOption so inner join cEXT_StoreSystemOption sso on so.SystemOptionId = sso.SystemOptionId
--WHERE SystemOptionKey like 'PAWNBUY_DURATION_SVC_CHG_IS_MONTHLY' and sso.StoreId=@TargetStoreId)

--DECLARE @Batchid int
--SET @Batchid =(SELECT ISNULL(MAX(batchid),0) from CEXT_TransactionPayment )

--select q.transactionId,
--23 as PaymentTypeId,
--PAIDAMT-col1 as Amount,
-- DATEADD(DAY,-@Days,t.outdate) PeriodDate,
-- DATEADD(DAY,-@Days,t.outdate) PaymentDate,
-- DATEADD(DAY,-@Days,t.outdate) CreatedDate,
-- t.CreatedBy,
-- ROW_NUMBER() over(order by t.transactionid)+@Batchid as BatchId,
-- t.CustomerId,
-- t.outdate CurrentOutDate,
-- t.SourceId as SourceId

--	from (
--select sum(ISNULL(tp.amount,0)) col1,t.TicketNumber,t.transactionid from CEXT_Transaction t
--left join CEXT_TransactionPayment tp on t.TransactionId=tp.TransactionId and tp.PaymentTypeId in (1,4,7,8,9)
--where t.TransactionTypeId=1 and t.StoreId=@TargetStoreId
--group by t.ticketnumber,t.TransactionId
----order by t.ticketnumber
--)q
--inner join EXT_Pawn p on p.TICKETNUM = q.ticketnumber
--inner join CEXT_transaction t on t.transactionid = q.transactionid
--where col1 <> PAIDAMT and t.StoreId=@TargetStoreId and p.STORE_NO=@SourceStoreId


--END
--GO
/****** Object:  StoredProcedure [dbo].[TransactionPaymentPartialPaymentsExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO

---- =============================================
---- Author:		<Syed Mohsin>
---- Create date: <03/08/2017>
---- Description:	<Extract partial payments from pawn.floatamt>
---- Sample Call: EXEC [dbo].[TransactionPaymentPartialPaymentsExtract] 8
---- =============================================
--CREATE PROCEDURE [dbo].[TransactionPaymentPartialPaymentsExtract]
--	-- Drop Column the parameters for the stored procedure here
--	@SourceStoreId as int
--AS
--BEGIN
--	-- SET NOCOUNT ON Drop Columned to prevent extra result sets from
--	-- interfering with SELECT statements.
--	SET NOCOUNT ON;
--	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

--select * FROM (
--  select tp.TransactionId,
--  1 as PaymentTypeId,
--  p.floatamt as Amount,
--  tp.PeriodDate,
--  tp.PaymentDate,
--  tp.Tax,
--  tp.AssociatedTransactionPaymentId,
--  tp.IsQueued,
--  tp.BatchId,
--  tp.Value,
--  tp.CreatedDate,
--  tp.CreatedBy,
--  tp.UpdatedDate,
--  tp.UpdatedBy,
--  tp.CustomerId,
--  tp.CurrentOutDate,
--  tp.TenderType1,
--  tp.TenderType2,
--  tp.TenderType1Amount,
--  tp.TenderType2Amount,
--  tp.SourceId,
--  tp.ServiceFee,
--  tp.InterestPercentage,
--  tp.OneTimeFee,
--  tp.FeePerPeriod,
--  tp.FirearmFee,
--  row_number() over(partition by p.pwn_id order by a.datein desc) as NUM
--   from CEXT_Transactionpayment tp
--  inner join EXT_Acct a on a.Acct_PK = tp.SourceId and a.sto_pk = @SourceStoreId and a.TYPE='PPP'
--  inner join CEXT_Transaction t on t.TransactionId = tp.TransactionId
--  inner join EXT_Pawn p on p.pwn_id = t.SourceId and floatamt >0
--  WHERE t.TicketNumber not in (SELECT TicketNumber from CEXT_Transaction t inner join CEXT_TransactionPayment tp on tp.TransactionId = t.TransactionId and tp.PaymentTypeId=1)
--  )q where q.NUM = 1


--END
--GO
/****** Object:  StoredProcedure [dbo].[TransactionSettingExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO

---- =============================================
---- Author:		Syed Mohsin
---- Create date: <8/2/2016>
---- Description:	<Extracts Transaction XML settings >
---- Sample Call: EXEC [dbo].[TransactionSettingExtract] 3429
---- =============================================

--CREATE Proc [dbo].[TransactionSettingExtract]
--@TargetStoreId int
--as
--BEGIN
--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

--DECLARE @key1 int;
--DECLARE @key2 int;
--DECLARE @key3 int;
--DECLARE @key4 int;

--DECLARE @value1 varchar(200);
--DECLARE @value2 varchar(200);
--DECLARE @value4 varchar(200);

--SELECT  @key1 = so.SystemOptionId, @value1 = ss.SystemOptionValue
--  FROM CEXT_SystemOption so
--  INNER JOIN CEXT_StoreSystemOption ss
--	ON ss.SystemOptionId = so.SystemOptionId AND ss.StoreId = @TargetStoreId
--  where SystemOptionKey='PAWNBUY_DURATION_SVC_CHG_PRD'

--SELECT  @key2 = so.SystemOptionId, @value2 = ss.SystemOptionValue
--  FROM CEXT_SystemOption so
--  INNER JOIN CEXT_StoreSystemOption ss
--	ON ss.SystemOptionId = so.SystemOptionId AND ss.StoreId = @TargetStoreId
--  WHERE SystemOptionKey='PAWNBUY_DURATION_SVC_CHG_IS_MONTHLY'

--  SELECT  @key3 = so.SystemOptionId
--  FROM CEXT_SystemOption so
--  INNER JOIN CEXT_StoreSystemOption ss
--	ON ss.SystemOptionId = so.SystemOptionId AND ss.StoreId = @TargetStoreId
--  WHERE SystemOptionKey='PAWNBUY_OTHER_FEE_ONE_TIME_FEE'

--    SELECT  @key4 = so.SystemOptionId
--  FROM CEXT_SystemOption so
--  INNER JOIN CEXT_StoreSystemOption ss
--	ON ss.SystemOptionId = so.SystemOptionId AND ss.StoreId = @TargetStoreId
--  WHERE SystemOptionKey='PAWNBUY_OTHER_FEE_REMINDER_FEE'

--  SELECT transactionid,CAST ('<TransactionSettings>
--					<Setting Key="'+CAST(@key1 as nvarchar)+'"
--					Name="PAWNBUY_DURATION_SVC_CHG_PRD" Value="'+CAST(p.SERVPERIOD as nvarchar)+'"/>
--					<Setting Key="'+CAST(@key2 as nvarchar)+'"
--					Name="PAWNBUY_DURATION_SVC_CHG_IS_MONTHLY" Value="'+CAST(iif(p.MONTHLYCHG=0,'False','True') as nvarchar)+'"/>
--					<Setting Key="'+CAST(@key3 as nvarchar)+'"
--					Name="PAWNBUY_OTHER_FEE_ONE_TIME_FEE" Value="'+CAST(p.StorageChrg as nvarchar)+'"/>
--					<Setting Key="'+CAST(@key4 as nvarchar)+'"
--					Name="PAWNBUY_OTHER_FEE_REMINDER_FEE" Value="'+CAST(0.00 as nvarchar)+'"/>
--					</TransactionSettings>' as nvarchar(max)) as XMLSetting from CEXT_Transaction t
--  INNER JOIN EXT_Pawn p  on p.PWN_id = t.SourceId
--  WHERE t.SourceId IS NOT NULL
--  AND t.StoreId=@TargetStoreId
--  AND t.TransactionTypeId = 1

--END
--GO
/****** Object:  StoredProcedure [dbo].[TransactionVersionExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Syed Mohsin>
-- Create date: <04/24/2017>
-- Description:	<Extract Transaction Versions by extracting data from source>
-- Sample Call: EXEC [dbo].[TransactionVersionExtract] 182
-- =============================================
CREATE PROCEDURE [dbo].[TransactionVersionExtract]
	-- Drop Column the parameters for the stored procedure here
	@TargetStoreId as int
AS
BEGIN
	-- SET NOCOUNT ON Drop Columned to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT t.[TransactionId]
      ,t.[TransactionTypeId]
      ,t.[StoreId]
      ,t.[CustomerId]
      ,t.[TicketNumber]
      ,t.[Duration]
      ,t.[Period]
      ,t.[InDate]
      ,t.[OutDate]
      ,t.[Amount]
      ,t.[RateTableId]
      ,t.[Note]
      ,t.[IsActive]
      ,t.[IsDeleted]
      ,t.[IsQueued]
      ,t.[CreatedDate]
      ,t.[CreatedBy]
      ,t.[UpdatedDate]
      ,t.[UpdatedBy]
      ,t.[StatusId]
      ,t.[Message]
      ,t.[VoidDescription]
      ,t.[IsEatTax] FROM (
SELECT t.[TransactionId]
      ,t.[TransactionTypeId]
      ,t.[StoreId]
      ,t.[CustomerId]
      ,t.[TicketNumber]
      ,t.[Duration]
      ,t.[Period]
      ,t.[InDate]
      ,t.[OutDate]
      ,t.[Amount]
      ,t.[RateTableId]
      ,t.[Note]
      ,t.[IsActive]
      ,t.[IsDeleted]
      ,t.[IsQueued]
      ,t.[CreatedDate]
      ,t.[CreatedBy]
      ,t.[UpdatedDate]
      ,t.[UpdatedBy]
      ,t.[StatusId]
      ,t.[Message]
      ,t.[VoidDescription]
      ,t.[IsEatTax],
	  ROW_NUMBER() over(partition by t.TransactionId order by TransactionversionId desc) rn
  FROM [Company].[dbo].[Transaction] t
  LEFT JOIN Company.dbo.TransactionVersion tv on tv.TransactionId = t.TransactionId
  WHERE t.StoreId = @TargetStoreId and (t.TransactionTypeId != 2 OR tv.transactionid is null )
  )t
  WHERE t.rn=1
END
GO
/****** Object:  StoredProcedure [dbo].[TransactionVersionHistoryExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Syed Mohsin>
-- Create date: <09/21/2017>
-- Description:	<Extract History Transaction Versions for All Transactions>
-- Sample Call: EXEC [dbo].[TransactionVersionHistoryExtract] 1406 , 1 , 5
-- =============================================
CREATE PROCEDURE [dbo].[TransactionVersionHistoryExtract]
	-- Drop Column the parameters for the stored procedure here
	@TargetStoreId as int, @SourceStoreId as int, @UtcOffset as int
AS
BEGIN
	-- SET NOCOUNT ON Drop Columned to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT *,
iif(NUM2 = 1,NewOutDate,DATEDrop Column(HOUR,5, CONVERT(DATETIME, OldOutDate))) as OutDate
 FROM (
SELECT *,row_number() over(partition by TicketNumber order by SourceId desc) as NUM2 FROM (
Select
		row_number() over(partition by a.sto_pk, a.[TYPE], a.TICKETNUM order by Acct_PK) as NUM,
		t.TransactionId ,
        CASE
		WHEN TYPE like 'P%' THEN 1
		WHEN TYPE LIKE 'B%' THEN 2
		WHEN TYPE LIKE 'SL%' THEN 5
		ELSE 3
		END
		 as TransactionTypeId,
        t.StoreId ,
        cst.CustomerId ,
        t.TicketNumber ,
       CAST (t.Period as int) Period ,
	    t.Duration ,
        iif(t.TransactionTypeId in (1,2),t.InDate,DATEDrop Column(HOUR,@UtcOffset, CONVERT(DATETIME, a.DATEin))) as InDate,
        CAST(t.OutDate as datetime) as NewOutDate,
        iif (a.AMOUNT < 0 ,a.AMOUNT  * -1,a.Amount) as Amount,
        t.RateTableId ,
        t.Note ,
		m.TargetKey as StatusId,
        DATEDrop Column(HOUR,@UtcOffset, CONVERT(DATETIME, a.DATEin)) as CreatedDate,
        t.CreatedBy,
		a.Acct_PK SourceId,
		a.TYPE,
		CASE
			WHEN a.TYPE like 'P%' OR a.TYPE like 'B%'
			THEN CAST(p.OrigDateOut  as DATETIME)
			ELSE CAST( s.PrevDate as DATETIME)
			END
		AS OldOutDate,
		t.StatusId as TStat,
		t.Amount as TAmount
		from EXT_Acct a


INNER JOIN Company.dbo.[Transaction] t on
t.TicketNumber = a.TICKETNUM
AND (
		(t.TransactionTypeId in (1,2) and  (a.TYPE like 'P%' OR a.TYPE like 'B%'))
		OR
		(t.TransactionTypeId in (3,5) and a.TYPE like 'S%')
	)
LEFT JOIN EXT_Pawn p
on p.TICKETNUM = a.TICKETNUM
AND a.sto_pk = p.STORE_NO
AND (a.TYPE like 'P%' OR a.TYPE like 'B%')

LEFT JOIN EXT_Sold s
ON s.TICKETNUM = a.TICKETNUM
AND s.STO_PK = a.sto_pk
AND a.TYPE like 'S%'

INNER JOIN pawnmaster_foxpro.dbo.cust c
ON c.Cus_PK = a.CUS_FK

INNER JOIN Company.dbo.Customer cst
	ON cst.sourceid = c.Cus_id OR(c.Cus_Store != @SourceStoreId and  cst.FirstName like LTRIM(RTRIM(c.CUS_FNAME)) and cst.LastName like LTRIM(RTRIM(c.CUS_LNAME)) and cst.MiddleName like LTRIM(RTRIM(c.CUS_MNAME))
	and cst.BirthDate like LTRIM(RTRIM(c.CUS_BIRTHDate)))

INNER JOIN Common.SourceTargetKeyMapping m
on m.SourceTable = 'Acct' and m.SourceKey = CASE WHEN a.TYPE IN ( 'PV','PPU','PD') and a.Amount < 0 THEN 'P'
												WHEN a.TYPE = 'SLD' and a.Amount > 0 THEN 'SL'
												WHEN a.TYPE = 'SSV' and s.STATUS = 'S' THEN 'SS'
											ELSE a.TYPE END
WHERE a.sto_pk = @SourceStoreId and t.StoreId = @TargetStoreId
)payments
WHERE [Type]!='P' OR ([TYPE]='P' AND NUM = 1)
)p
WHERE NUM2 > 1 OR (TYPE like 'B%' OR (TYPE = 'P' AND (TStat =14 OR TStat=10)) OR (TYPE = 'P') and iif (AMOUNT < 0 ,AMOUNT  * -1,Amount) != TAmount)

UNION
SELECT * FROM (
SELECT
		ROW_NUMBER() over(partition by p.pwn_id order by i.items_pk) num,
		t.TransactionId ,
        t.TransactionTypeId,
        t.StoreId ,
        t.CustomerId ,
        t.TicketNumber ,
		 CAST (t.Period as int) Period ,
        t.Duration ,
        DATEDrop Column(HOUR,@UtcOffset, CONVERT(DATETIME, inv.DATEin)) as  InDate,
       CAST(p.OrigDateOut as DATETIME) as NewOutDate,
	    t.Amount as Amount,
        t.RateTableId ,
        t.Note ,
		iif(inv.QUANTITY < 0,4,45) as StatusId,
        DATEDrop Column(HOUR,@UtcOffset, CONVERT(DATETIME, inv.DATEin)) as CreatedDate,
        us.UserId as CreatedBy,
		inv.Inv_PK SourceId,
		'' as TYPE,
		CAST(p.OrigDateOut as DATETIME) as OldOutDate,
		0 as NUM2,
		DATEDrop Column(HOUR,@UtcOffset, CONVERT(DATETIME, p.OrigDateOut)) as OutDate,
		1 as TStat,
		1 as TAmount


FROM pawnmaster_foxpro.dbo.inv inv
inner join EXT_items i on i.INVNUM = inv.INVNUM
inner join EXT_Pawn p on p.PWN_id = i.PWN_id
inner join Company.dbo.[Transaction] t on t.TicketNumber = p.TICKETNUM and t.TransactionTypeId=2
inner join pawnmaster_foxpro.dbo.users u on u.USR_PK = inv.USR_fk
inner join Company.dbo.[User] us on us.SourceId = u.USR_ID
where inv.Sto_PK = @SourceStoreId and t.StoreId=@TargetStoreId and inv.STATUS='B'
)q where q.num = 1

ORDER BY CreatedDate ASC

END


GO
/****** Object:  StoredProcedure [dbo].[UsersExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE Proc [dbo].[UsersExtract]
@StoreID int

AS

BEGIN
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
select
	cast (iif(LTRIM(RTRIM(s.USR_LANID)) =t.UserName, cast(LTRIM(RTRIM(s.USR_LANID)) as varchar(36)) +'_'+cast(s.[USR_STORE] as varchar(10)),LTRIM(RTRIM(s.usr_LANID))) as nvarchar(50)) as username,
	cast ( '1xxx!' as nvarchar(150)) as Password, --Drop Column in package
	Null as BiometricTemplate,
	NULL as BiometricSample,
	GetDate() as CreatedDate,
	1 as CreatedBy, -- Drop Column in package
	cast( LTRIM(RTRIM(USR_FNAME))  as nvarchar(100)) as FirstName,
	cast(LTRIM(RTRIM(USR_MNAME))  as nvarchar(100))	as	MiddleName,
	cast(LTRIM(RTRIM(USR_LNAME))	as nvarchar(100)) as	LastName,
	cast(LTRIM(RTRIM(USR_Drop Column1))	as nvarchar(255)) as	Drop ColumnressLine1	,
	cast(LTRIM(RTRIM(USR_Drop Column2))	as nvarchar(255))		as	Drop ColumnressLine2	,
	cast(LTRIM(RTRIM(USR_CITY))	as nvarchar(50))	as	City,
	cast(LTRIM(RTRIM(USR_STATE))	as nvarchar(50))	as State,
	cast(LTRIM(RTRIM(USR_ZIP))	as nvarchar(10))	as ZipCode,
	cast( USR_AC1+'-'+USR_PHONE1	as nvarchar(20)) as Phone,
	cast(USR_SSNUM	as nvarchar(400)) as SSN,
	USR_BIRTHDATE	as BirthDate,
	USR_STARTDATE	as StartDate,
	USR_TERMINATE	as EndDate	,
	CAST(iif(BuyLimit=999999.99,9999999999.99,BuyLimit) as decimal(12,2))		as MaxTransactionAmountBuy, -- Iteration 1 response fix
	CAST(iif(PawnLimit=999999.99,9999999999.99,PawnLimit) as decimal(12,2))		as MaxTransactionAmountPawn, -- Iteration 1 response fix
	CAST(iif(OptionLimit=999999.99,9999999999.99,OptionLimit) as decimal(12,2))		as MaxTransactionAmountSale, -- Iteration 1 response fix
	USR_DISCOUNT	as SaleDiscount,
	USR_ACTIVE		as StatusId,
	0			as IsLoggedIn,
	1  as CompanyId, --Drop Column in package
	USR_STORE		as StoreId,
	cast(USR_NOTES	as nvarchar(200))		as Comment,
	1			as IsRequiredChangePassword,
	0			as IsDefaultUser,
	1			as CultureId,
	0			as RowVersion,
	1			as IsUserMigrated,
	1 as DefaultStoreId,
	USR_ID	as SourceId


	from [PawnMaster_foxPro].dbo.users s (nolock)
		left outer join [Company].dbo.[User] t
			on LTRIM(RTRIM(s.USR_LANID)) =t.UserName
		where USR_STORE = @StoreID AND s.USR_LANID != '***'

END
GO
/****** Object:  StoredProcedure [dbo].[VendorExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Nadeem Akhtar
-- Create date: <8/2/2016>
-- Description:	<Extracts Store Vendors >
-- Sample Call: EXEC [dbo].[UsersExtract] 8

-- Updated By: Syed Mohsin
-- Update date: <8/3/2016>
-- Comments: Corrected mapping for columns and Drop Columned join conditions to meet the design logic.
--			 Drop Columned Storeid parameter for Source StoreId

-- Updated By: Syed Mohsin
-- Update date: <8/25/2016>
-- Comments: Updated join from inner to left with User table.

-- Updated By: Syed Mohsin
-- Update date: <9/1/2016>
-- Comments: Removed store check and updated condition to check for unique vendors.

-- Updated By: Syed Mohsin
-- Update date: <9/2/2016>
-- Comments: Made fixes for iteration 1 response.

-- Updated By: Syed Mohsin
-- Update date: <9/22/2016>
-- Comments: Drop Columned country in extract by joining with state
-- =============================================

CREATE Proc [dbo].[VendorExtract]
@StoreID int
as
BEGIN
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
select
cast(cast(VEN_STORE as nvarchar(10))+'_' as nvarchar(50)) as VendorCode, -- Drop Column in package
cast(RTRIM(LTRIM(VEN_COMPANY)) as nvarchar(250)) as VendorName,
cast(VEN_STORE as int) as StoreId, --Replace with targetStoreId in package
cast(RTRIM(LTRIM(VEN_STATE)) as nvarchar(50)) as State,
cast(st.Country as nvarchar(50)) as Country,
cast(VEN_CONTACT as nvarchar(150)) as Contact,
cast(RTRIM(LTRIM(VEN_Drop Column1)) as nvarchar(255)) as Drop ColumnressLine1,
cast(RTRIM(LTRIM(VEN_Drop Column2)) as nvarchar(255)) as Drop ColumnressLine2,
cast(RTRIM(LTRIM(VEN_CITY)) as nvarchar(50)) as City,
cast(VEN_ZIP as nvarchar(10)) as ZipCode,
cast(VEN_AC1+'-'+VEN_PHONE as nvarchar(20)) as Phone,
cast(VEN_AC2+''+VEN_FAX as nvarchar(20)) as Fax,
cast(RTRIM(LTRIM(s.Comments)) as nvarchar(100)) as Comments, -- Changed after iteration 1 response
cast(Sells_FireArms as bit) as SellsFireArms,
cast(VEN_FFLNUM as nvarchar(50)) as FFLNumber,
cast(VEN_FFLEXPIREDATE as datetime) as FFLExpire,
0 as IsDeleted,
getdate() as CreatedDate,
1 as CreatedBy, --Drop Column in package
getdate() as UpdatedDate,
u.UserId as UpdatedBy,
VEN_id	as SourceId
from [PawnMaster_foxPro].dbo.vend s
left outer join [Company].dbo.Vendor t
				on RTRIM(LTRIM(s.VEN_COMPANY)) = t.VendorName AND RTRIM(LTRIM(s.VEN_Drop Column1)) = t.Drop ColumnressLine1 AND s.VEN_FFLNUM = t.FFLNumber --Needs to be confirmed
left join [Company].dbo.[User] u
				on s.LastUpdatedUSR_ID = u.SourceId
left join dbo.[State] st on RTRIM(LTRIM(s.VEN_STATE)) = st.StateCode
WHERE t.VendorName is null
END

GO