38am4SjQ

1
2-- =============================================
3-- Author:		Syed Mohsin
4-- Create date: <8/11/2016>
5-- Description:	<Extracts data for ApplicationLookup Entity>
6-- Sample Call: EXEC [dbo].[ApplicationLookupExtract] 8 , 180
7
8-- Updated By: Syed Mohsin
9-- Updated Date: <8/23/2016>
10-- Comments: Join conditions updated.
11
12-- Updated By: Syed Mohsin
13-- Updated Date: <9/7/2016>
14-- Comments: Removed target and source store check from lookups.
15-- =============================================
16
17--CREATE Proc [dbo].[ApplicationLookupExtract]
18--@StoreID int, @TargetStoreId int
19--as
20--BEGIN
21--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
22--select * from (
23
24--  SELECT
25--	 c.sto_pk sto,
26--	  lt2.LookupTypeId,
27--	  CAST(RTRIM(LTRIM(c.lc_Descript)) as nvarchar(50)) lc_Descript,
28--	  c.lc_pk  as SourceId,
29--	  1 as IsNew ,
30--	  GetDate() as CreatedDate,
31--	  GetDate() as UpdatedDate,
32--	1 as CreatedBy, --Drop Column in package
33--	u.UserId as UpdatedBy,
34--	  0 as IsDeleted,
35--	  ROW_NUMBER() OVER(PARTITION BY lt2.LookupTypeId,lc_Descript ORDER BY c.lc_pk DESC) AS RowNumber
36	  
37--  FROM EXT_Lookup_B b INNER JOIN CEXT_LookupType lt ON lt.LookupTypeName = CASE [lb_type] 
38--		WHEN 'C' THEN 'Customer Characteristics'
39--		WHEN 'G' THEN 'Gun Info Codes'
40--		WHEN 'J' THEN 'Jewelry Info'
41--		WHEN 'K' THEN 'Item'
42--		WHEN 'O' THEN 'Item'
43--		WHEN 'S' THEN 'Stone Info Codes'
44--		WHEN 'T' THEN 'Tender Type'
45--		END
46--	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 
47--	INNER JOIN EXT_Lookup_C c ON b.lb_pk = c.LB_FK
48--	INNER JOIN EXT_Lookup_N n ON n.lc_FK = c.lc_pk and n.sto_pk = @StoreID
49--	LEFT JOIN CEXT_User u ON c.LastUpdatedUSR_ID = u.SourceId --Join condition updated from Inner to left
50--	LEFT JOIN CEXT_LookupValue lv ON lt2.LookupTypeId = lv.LookupTypeId AND lv.Value = c.lc_Descript
51
52--	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
53--	LEFT JOIN EXT_items i on i.Color = c.lc_pk AND i.Sto_Pk = @StoreID
54--	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)
55--	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)
56--	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)
57--  WHERE lv.LookupValueId IS NULL AND [lb_type] != 'A' AND [lb_type] != 'V' AND lc_pk!=1 AND 
58--  (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)) 
59--  ) q 
60--  WHERE q.RowNumber=1
61
62--END
63--GO
64/****** Object:  StoredProcedure [dbo].[AuditItemsExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
65--SET ANSI_NULLS ON
66--GO
67--SET QUOTED_IDENTIFIER ON
68--GO
69
70-- =============================================
71-- Author:		Nadeem Akhtar
72-- Create date: <8/29/2016>
73-- Description:	<Extracts audit data for Inventory Items>
74-- Sample Call: EXEC [dbo].[AuditItemsExtract] 2, 133 , 5
75
76-- Updated By: Syed Mohsin
77-- Updated Date: <8/30/2016>
78-- Comments: Mapping corrected and Join conditions updated.
79
80-- Updated By: Farrukh Ijaz
81-- Updated Date: <9/16/2016>
82-- Comments: Drop Columned 2nd Select to get item version for inventory items where stone version exists but item version does not.
83-- =============================================
84
85--CREATE Proc [dbo].[AuditItemsExtract]
86--@StoreID int, @TargetStoreId int, @UtcTime int
87--as
88--BEGIN
89--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
90
91--DECLARE @LookuptypeBin int;
92--SET @LookuptypeBin=(SELECT LookupTypeId FROM CEXT_LookupType WHERE LookupTypeName='BIN')
93
94--DECLARE @LookuptypeBrand int;
95--SET @LookuptypeBrand = (SELECT LookupTypeId FROM CEXT_LookupType WHERE LookupTypeName='Brand')
96
97--select C.InventoryItemId as InventoryItemId,
98--       DATEDrop Column(HOUR,@UtcTime,A.LastUpdatedDate) as CreatedDate,
99--       u.UserId as CreatedBy,
100--       DATEDrop Column(HOUR,@UtcTime,A.LastUpdatedDate) as UpdatedDate,
101--       u.UserId as UpdatedBy,
102--       @TargetStoreId as StoreID,
103--       c.InventoryNumber as InventoryNumber,
104--       it.ItemTypeId as ItemTypeId,
105--       cast (LTRIM(RTRIM(MODELNUM))       as nvarchar(30)) as Model, 
106--       cast (LTRIM(RTRIM(SERIALNUM))      as nvarchar(50)) as SerialNumber,
107--       cast (LTRIM(RTRIM(OWNERNUM))       as     nvarchar(50)) as    OwnerNumber,
108--       itb.ItemTypeBrandId as ItemTypeBrandId, 
109--       lvcol.LookupValueId as ItemColorId, 
110--       cast (Amount as decimal(12,3) ) as              Cost,  
111--       cast (RESALEAMT      as decimal(12,3) )  as Resale,
112--       cast (LOWSLPRICE as decimal(12,3) )  as  [Min],
113--       cast (LTRIM(RTRIM(A.DESCRIPT)) as          nvarchar(500) ) as Comment,
114--       a.IsBulkItem  as     IsBulkItem,                                     
115--       NEWITEM       as     IsNewItem,
116--       cast(a.UPC    as nvarchar(14)) as UPC,
117--       cast ( MAXQUANT      as int) as    MaxQuantity,
118--       cast (REORDER as int)              as ReOrderLevel,
119--	   C.IsInventoryItem as IsInventoryItem,
120--      lv.LookupValueId as BinNumberId
121    
122--from EXT_Audit_Items A
123--       INNER JOIN CEXT_InventoryItem C
124--              on a.Items_ID =c.sourceid AND c.StoreId = @TargetStoreId
125--       INNER JOIN CEXT_ItemType it
126--              ON it.SourceId = 
127--                     CASE 
128--                           WHEN(LEVEL4_FK != 1) THEN LEVEL4_FK
129--                           WHEN (LEVEL3_FK != 1) THEN LEVEL3_FK
130--                           WHEN (LEVEL2_FK != 1) THEN LEVEL2_FK
131--                           ELSE LEVEL1_FK 
132--                     END
133--	left outer join CEXT_LookupValue lv 
134--		ON lv.LookupTypeId = @LookuptypeBin AND a.BIN = lv.Value AND lv.StoreId = @TargetStoreId
135
136--	left outer join CEXT_LookupValue lvcol
137--		ON a.Color != 1 AND lvcol.SourceId = a.Color AND a.BIN = lv.Value AND lvcol.StoreId = @TargetStoreId
138
139--	LEFT OUTER JOIN EXT_Level5 ll5
140--		ON ll5.lv5_PK = A.LEVEL5_FK
141
142--	LEFT OUTER JOIN CEXT_LookupValue lvb  
143--		ON lvb.LookupTypeId = @LookuptypeBrand AND lvb.Value = RTRIM(LTRIM(ll5.DESCRIPT)) AND lvb.StoreId = @TargetStoreId
144
145--     LEFT OUTER JOIN CEXT_ItemTypeBrand itb  
146--		ON itb.BrandId = lvb.LookupValueId
147
148--	LEFT OUTER JOIN CEXT_User u
149--		ON u.SourceId = a.LastUpdatedUSR_ID
150
151--UNION
152
153--SELECT 
154--	   C.InventoryItemId as InventoryItemId,
155--       C.CreatedDate as CreatedDate,
156--       1 as CreatedBy,
157--       C.UpdatedDate as UpdatedDate,
158--       u.UserId as UpdatedBy,
159--       @TargetStoreId as StoreID,
160--       c.InventoryNumber as InventoryNumber,
161--       it.ItemTypeId as ItemTypeId,
162--       cast (LTRIM(RTRIM(i.MODELNUM))       as nvarchar(30)) as Model, 
163--       cast (LTRIM(RTRIM(i.SERIALNUM))      as nvarchar(50)) as SerialNumber,
164--       cast (LTRIM(RTRIM(i.OWNERNUM))       as     nvarchar(50)) as    OwnerNumber,
165--       itb.ItemTypeBrandId as ItemTypeBrandId, 
166--       lvcol.LookupValueId as ItemColorId, 
167--       cast (i.Amount as decimal(12,3) ) as              Cost,  
168--       cast (i.RESALEAMT      as decimal(12,3) )  as Resale,
169--       cast (i.LOWSLPRICE as decimal(12,3) )  as  [Min],
170--       cast (LTRIM(RTRIM(i.DESCRIPT)) as          nvarchar(500) ) as Comment,
171--       i.IsBulkItem  as     IsBulkItem,                                     
172--       i.NEWITEM       as     IsNewItem,
173--       cast(i.UPC    as nvarchar(14)) as UPC,
174--       cast ( i.MAXQUANT      as int) as    MaxQuantity,
175--       cast (i.REORDER as int)              as ReOrderLevel,
176--	   C.IsInventoryItem as IsInventoryItem,
177--      lv.LookupValueId as BinNumberId
178
179--FROM EXT_items i
180--INNER JOIN EXT_Detail_J j ON i.ITEMS_PK = j.Items_FK AND i.Sto_Pk=j.Sto_FK
181--INNER JOIN EXT_stones s ON j.JDT_PK = s.JDT_FK AND j.Sto_FK=s.Sto_FK
182--LEFT JOIN EXT_Audit_Items ai ON i.Items_ID=ai.Items_ID
183--INNER JOIN CEXT_InventoryItem C
184--              on i.Items_ID =c.sourceid AND c.StoreId = @TargetStoreId
185--LEFT OUTER JOIN CEXT_User u
186--		ON u.SourceId = i.LastUpdatedUSR_ID
187--INNER JOIN CEXT_ItemType it
188--              ON it.SourceId = 
189--                     CASE 
190--                           WHEN(i.LEVEL4_FK != 1) THEN i.LEVEL4_FK
191--                           WHEN (i.LEVEL3_FK != 1) THEN i.LEVEL3_FK
192--                           WHEN (i.LEVEL2_FK != 1) THEN i.LEVEL2_FK
193--                           ELSE i.LEVEL1_FK 
194--                     END
195--left outer join CEXT_LookupValue lv 
196--		ON lv.LookupTypeId = @LookuptypeBin AND i.BIN = lv.Value AND lv.StoreId = @TargetStoreId
197--left outer join CEXT_LookupValue lvcol
198--		ON i.Color != 1 AND lvcol.SourceId = i.Color AND i.BIN = lv.Value AND lvcol.StoreId = @TargetStoreId
199
200--LEFT OUTER JOIN EXT_Level5 l5
201--	ON l5.lv5_PK = i.LEVEL5_FK
202
203--LEFT OUTER JOIN CEXT_LookupValue lvb  
204--		ON lvb.LookupTypeId = @LookuptypeBrand AND lvb.Value = RTRIM(LTRIM(l5.DESCRIPT))-- AND lvb.StoreId = @TargetStoreId
205--LEFT OUTER JOIN CEXT_ItemTypeBrand itb  
206--		ON itb.BrandId = lvb.LookupValueId
207
208--WHERE ai.AUD_Num IS NULL
209
210--END
211--GO
212/****** Object:  StoredProcedure [dbo].[AuditStonesExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
213--SET ANSI_NULLS ON
214--GO
215--SET QUOTED_IDENTIFIER ON
216--GO
217
218-- =============================================
219-- Author:		Nadeem Akhtar
220-- Create date: <8/29/2016>
221-- Description:	<Extracts audit data for Inventory Item Stones>
222-- Sample Call: EXEC [dbo].[AuditStonesExtract] 2, 133
223
224-- Updated By: Syed Mohsin
225-- Updated Date: <8/30/2016>
226-- Comments: Mapping corrected and Join conditions updated.
227
228-- Updated By: Farrukh Ijaz
229-- Updated Date: <9/16/2016>
230-- Comments: Drop Columned InventoryItemVersionId.
231-- =============================================
232
233--CREATE Proc [dbo].[AuditStonesExtract]
234--@SourceStoreId int, @TargetStoreId int
235--as
236--BEGIN
237--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
238
239--WITH InventoryItemVer AS  
240--(  
241--    SELECT ROW_NUMBER() OVER(PARTITION BY InventoryItemId ORDER BY CreatedDate DESC) as RowNumber,
242--	InventoryItemVersionId, InventoryItemId FROM CEXT_InventoryItemVersion 
243--) 
244
245--       SELECT
246--       iv.InventoryItemVersionId,
247--	   s.LastUpdatedDate as CreatedDate,
248--       1 as CreatedBy,
249--       s.LastUpdatedDate as UpdatedDate,
250--       u.UserId as UpdatedBy,
251--       @TargetStoreId as StoreID,
252--       st.InventoryItemStoneId as   InventoryItemStoneId,
253--	   st.InventoryItemId  as InventoryItemId,
254--       cast (s.NUMSTONE       as int) as    Quantity,
255--       lvtyp.LookupValueId    as     StoneTypeId,       
256--       lvshape.LookupValueId      as     StoneShapeId,     
257--       cast( s.CARAT as decimal(4,2) ) as   Carat, 
258--       lvcol.LookupValueId      as     StoneColorId, 
259--       cast(s.WEIGHT   as decimal(4,2))     as     [Weight],     
260--       cast(  s.LENGTH as     decimal(4,2)) [Length],     
261--       cast(s.WIDTH    as decimal(4,2))     as     Width,
262--       lvtran.LookupValueId    as     StoneClarityId                  
263                                         
264--from   EXT_AUDIT_Stones s
265--INNER JOIN CEXT_InventoryItemStone st
266--	ON s.STO_ID = st.SourceId
267--INNER JOIN EXT_stones t
268--	ON s.STO_ID = t.STO_id AND t.Sto_FK = @SourceStoreId
269--LEFT JOIN (SELECT * FROM InventoryItemVer WHERE RowNumber=1) iv 
270--	ON st.InventoryItemId = iv.InventoryItemId
271----Joins with LookupValue
272--LEFT JOIN CEXT_LookupValue lvtyp
273--ON lvtyp.StoreId = @TargetStoreId AND lvtyp.SourceId = s.TYPSTONEFK
274
275--LEFT JOIN CEXT_LookupValue lvshape
276--ON lvshape.StoreId = @TargetStoreId AND lvshape.SourceId = s.SHAPE_FK
277
278--LEFT JOIN CEXT_LookupValue lvcol
279--ON lvcol.StoreId = @TargetStoreId AND lvcol.SourceId = s.COLOR_FK
280
281--LEFT JOIN CEXT_LookupValue lvtran
282--ON lvtran.StoreId = @TargetStoreId AND lvtran.SourceId = s.TRANSLUCFK
283
284
285--LEFT OUTER JOIN CEXT_User u
286--		ON u.SourceId = s.LastUpdatedUSR_ID
287
288--END
289--GO
290/****** Object:  StoredProcedure [dbo].[BrandsExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
291--SET ANSI_NULLS ON
292--GO
293--SET QUOTED_IDENTIFIER ON
294--GO
295
296---- =============================================
297---- Author:		Syed Mohsin
298---- Create date: <8/15/2016>
299---- Description:	<Extracts data for Brands>
300---- Sample Call: EXEC [dbo].[BrandsExtract] 8, 171
301
302---- Updated By: Syed Mohsin
303---- Update date: <10/18/2016>
304---- Comments: Repeating brands issue resolved.
305---- =============================================
306
307--CREATE Proc [dbo].[BrandsExtract]
308--@SourceStoreID int, @TargetStoreId int 
309
310--AS
311
312--BEGIN
313--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
314--declare @lookupTypeID int
315
316--select @lookupTypeID= 
317--		 LookupTypeId from 
318--		CEXT_LookupType s 
319--		where LookupTypeName='Brand'
320--SELECT * FROM (	
321--select  
322	
323--	@lookupTypeID as  LookupTypeID,
324--	cast(RTRIM(LTRIM(s.DESCRIPT)) as nvarchar(50))	as	value,		
325--	GetDate() as	CreatedDate	,
326--	1 as CreatedBy, --Drop Column in package
327--	GetDate() as	UpdatedDate,	
328--	u.UserId as UpdatedBy,
329--	0 as	IsDeleted,
330--	@TargetStoreId  as StoreId,
331--	s.lv5_PK as SourceID,
332--	ROW_NUMBER() over (partition by RTRIM(LTRIM(s.DESCRIPT)) order by s.lv5_PK) as Num1
333
334
335-- from  EXT_Level5 s 
336--	left outer join CEXT_LookupValue t
337--		on RTRIM(LTRIM(s.DESCRIPT)) = t.Value
338--			and t.LookupTypeId=@lookupTypeID
339--	left outer join CEXT_User u
340--		on s.LastUpdatedUSR_ID = u.SourceId
341-- where Len(RTRIM(LTRIM(s.DESCRIPT))) >0
342--	and t.Value is null AND s.sto_pk = @SourceStoreID
343--)q where Num1 = 1
344--END
345--GO
346/****** Object:  StoredProcedure [dbo].[BrandsNCICExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
347--SET ANSI_NULLS ON
348--GO
349--SET QUOTED_IDENTIFIER ON
350--GO
351
352---- =============================================
353---- Author:		Syed Mohsin
354---- Create date: <8/11/2016>
355---- Description:	<Extracts data for LevelN Lookup values>
356---- Sample Call: EXEC [dbo].[BrandsNCICExtract] 1 , 389
357
358---- =============================================
359--CREATE Proc [dbo].[BrandsNCICExtract]
360--@SourceStoreId int , @TargetStoreId int
361
362--AS
363--BEGIN
364--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
365 
366--  SELECT lv.LookupValueId,
367--cast(n.NCIC_Code as nvarchar(50)) NCIC_Code ,
368--  cast(n.Local_Code as nvarchar(50)) Local_Code,
369--  getdate() as UpdateDate,
370--  getdate() as CreatedDate,
371--  u.UserId UpdatedBy,
372--  0 as IsDeleted,
373--  @TargetStoreId Storeid
374-- from CEXT_LookupValue lv
375--  INNER JOIN EXT_Level5 c ON lv.Value = LTRIM(RTRIM(c.DESCRIPT)) AND c.Sto_PK = @SourceStoreId
376--  INNER JOIN EXT_Level_N n ON n.Level_FK = c.lv5_pk AND n.sto_pk = @SourceStoreId
377--  LEFT JOIN CEXT_User u ON u.SourceId = n.LastUpdatedUSR_ID
378-- WHERE lv.LookupTypeId=8
379
380--END
381--GO
382/****** Object:  StoredProcedure [dbo].[CustomerPicExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
383--SET ANSI_NULLS ON
384--GO
385--SET QUOTED_IDENTIFIER ON
386--GO
387
388---- =============================================
389---- Author:		Syed Mohsin
390---- Create date: <8/17/2016>
391---- Description:	<Extracts data for Customer's Pictures>
392---- Sample Call: EXEC [dbo].[CustomerPicExtract]
393
394---- Updated By: Syed Mohsin
395---- Update date: <9/26/2016>
396---- Comments: Modified query by Drop Columning UNION for extracting driver license picture differently.
397
398---- Updated By: Syed Mohsin
399---- Update date: <12/23/2016>
400---- Comments: Drop Columned check for image 0x.
401---- =============================================
402
403--CREATE Proc [dbo].[CustomerPicExtract]
404--AS
405--BEGIN
406--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
407
408--DECLARE @CusPicSourceId int
409--SET @CusPicSourceId  = (select PictureSourceId FROM CEXT_PictureSource p
410--  WHERE p.PictureSourceName = 'Customer Picture')
411
412
413--DECLARE @DLPicSourceId int
414--SET @DLPicSourceId  = (select PictureSourceId FROM CEXT_PictureSource p
415--  WHERE p.PictureSourceName = 'CustomerIdPicture')
416
417--select cusPic_Img as ObjectImage,
418--cusPic_Img_Date as PictureTakenDate,
419--@CusPicSourceId as PictureSourceId,
420--c.CustomerId as CustomerId,
421--0 as IsDeleted,
422--getdate() as CreatedDate,
423--1 as CreatedBy ,-- Drop Column in package
424--getdate() as UpdatedDate,
425--c.UpdatedBy as UpdatedBy
426
427--from PEXT_custPics cp
428--inner join CEXT_Customer c 
429--	ON c.SourceId = cp.cus_id
430--where cusPic_Img IS NOT NULL and CONVERT(varchar(max),CONVERT(varbinary(max),cuspic_Img),1) != ''
431
432--UNION ALL
433
434--select cuspic_DLImg as ObjectImage,
435--cusPic_DLImg_Date as PictureTakenDate,
436--@DLPicSourceId as PictureSourceId,
437--c.CustomerId as CustomerId,
438--0 as IsDeleted,
439--getdate() as CreatedDate,
440--1 as CreatedBy ,-- Drop Column in package
441--getdate() as UpdatedDate,
442--c.UpdatedBy as UpdatedBy
443
444--from PEXT_custPics cp
445--inner join CEXT_Customer c 
446--	ON c.SourceId = cp.cus_id
447--where cuspic_DLImg IS NOT NULL and CONVERT(varchar(max),CONVERT(varbinary(max),cuspic_DLImg),1) != ''
448
449--END
450--GO
451/****** Object:  StoredProcedure [dbo].[CustomersExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
452--SET ANSI_NULLS ON
453--GO
454--SET QUOTED_IDENTIFIER ON
455--GO
456
457---- =============================================
458---- Author:		Nadeem Akhtar
459---- Create date: <8/3/2016>
460---- Description:	<Extracts data for Customers>
461---- Sample Call: EXEC [dbo].[CustomersExtract] 1
462
463---- Updated By: Syed Mohsin
464---- Update date: <8/5/2016>
465---- Comments: Corrected mapping for columns and Drop Columned join conditions to meet the design logic.
466----			 Drop Columned Storeid parameter for Source StoreId
467
468---- Updated By: Syed Mohsin
469---- Update date: <8/16/2016>
470---- Comments: Drop Columned join for StateIssueId. Revised mapping.
471
472---- Updated By: Syed Mohsin
473---- Update date: <9/1/2016>
474---- Comments: Removed store condition check.
475
476---- Updated By: Syed Mohsin
477---- Update date: <9/2/2016>
478---- Comments: Fixes made for iteration 1 response.
479
480---- Updated By: Syed Mohsin
481---- Update date: <9/22/2016>
482---- Comments: Removed cash customer from extract and applied trim on Customer name. Also Drop Columned lookupids for sex column
483
484---- Updated By: Syed Mohsin
485---- Update date: <10/17/2016>
486---- Comments: Applied fixes for Bug 6249
487
488---- Updated By: Syed Mohsin
489---- Update date: <11/3/2016>
490---- Comments: Applied fixes country mapping
491
492---- Updated By: Syed Mohsin
493---- Update date: <11/23/2016>
494---- Comments: Drop Columned column SSNLast4Digits
495
496---- Updated By: Syed Mohsin
497---- Update date: <11/23/2016>
498---- Comments: Join Drop Columned of state to get country
499---- =============================================
500
501--CREATE Proc [dbo].[CustomersExtract]
502--@StoreId int
503
504--AS
505--Begin
506--DECLARE @CustomerNumber int;
507--SET @CustomerNumber =
508--(SELECT MAX(CustomerNumber) FROM CEXT_Customer)
509
510--DECLARE @CustomerCharacteristics int;
511--SET @CustomerCharacteristics = (SELECT LookupTypeId from CEXT_LookupType where LookupTypeName = 'Customer Characteristics')
512
513--DECLARE @LookUpTypeHair int;
514--SET @LookUpTypeHair = (SELECT LookupTypeId from CEXT_LookupType where LookupTypeName = 'Hair' and ParentLookupTypeId = @CustomerCharacteristics)
515
516--DECLARE @LookUpTypeEye int;
517--SET @LookUpTypeEye = (SELECT LookupTypeId from CEXT_LookupType where LookupTypeName = 'Eyes' and ParentLookupTypeId = @CustomerCharacteristics)
518
519--DECLARE @LookUpTypeRace int;
520--SET @LookUpTypeRace = (SELECT LookupTypeId from CEXT_.LookupType where LookupTypeName = 'Race' and ParentLookupTypeId = @CustomerCharacteristics)
521
522--DECLARE @IdType int;
523--SET @IdType = (SELECT LookupTypeId from CEXT_LookupType where LookupTypeName = 'ID Type' and ParentLookupTypeId = @CustomerCharacteristics)
524
525--DECLARE @MaleId int;
526--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')
527
528
529--DECLARE @FemaleId int;
530--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')
531
532--DECLARE @IdRef int;
533--SET @IdRef = (SELECT LookupTypeId from CEXT_LookupType where LookupTypeName = 'Referred')
534
535
536--	SELECT 
537--		iif(Cus_Entered IS NULL,getdate(),Cus_Entered) as CreatedDate,
538--		1 as CreatedBy, --Drop Column in package
539--		u.UserId as UpdatedBy,
540--		GetDate() as UpdatedDate,
541--		Cus_Store as StoreID, --Drop Column in package (TargetStoreId)
542--		@CustomerNumber as CustomerNumber,--Increment in package
543--		cast (1 as varbinary(max)) as Barcode,
544--		cast(LTRIM(RTRIM(CUS_FNAME)) as nvarchar(100)) as FirstName,
545--		cast(LTRIM(RTRIM(CUS_MNAME)) as nvarchar(100)) as MiddleName,
546--		cast(LTRIM(RTRIM(s.CUS_LNAME)) as nvarchar(100)) as LastName,
547--		cast(LTRIM(RTRIM(CUS_Drop Column1)) as nvarchar(500))	as Drop ColumnressLine1,
548--		cast(LTRIM(RTRIM(CUS_Drop Column2)) as nvarchar(500))	as Drop ColumnressLine2,
549--		cast(LTRIM(RTRIM(CUS_CITY)) as nvarchar(50)) as City,
550--		cast(cst.Country as nvarchar(100)) as Country,
551		
552--		cast(LTRIM(RTRIM(CUS_STATE)) as nvarchar(50))	as State,
553--		cast(LTRIM(RTRIM(CUS_ZIP)) as nvarchar(10))		as ZipCode,
554--		iif(CUS_PHONE1 = '','',cast(LTRIM(RTRIM(CUS_AC1))+'-'+LTRIM(RTRIM(CUS_PHONE1)) as nvarchar(20))	) as Phone1,
555--		cast (LTRIM(RTRIM(REPLACE(CUS_SSNUM,'-','')))	as	nvarchar(400)) as SSN,
556--		lvtype1.LookupValueId	as			ID1TypeID, -- Iteration 1 response
557--		st.Id as ID1StateIssueID,
558--		CUS_ID1EXP	 as ID1ExpiryDate,
559--		cast(CUS_IDNUM1 as nvarchar(50)) as ID1Number,
560--		lvtype2.LookupValueId	as ID2TypeID, -- Iteration 1 response
561--		st2.Id	as ID2StateIssueID,
562--		CUS_ID2EXP		as ID2ExpiryDate,
563--		cast(LTRIM(RTRIM(CUS_IDNUM2))	as nvarchar(50))	as			ID2Number,
564--		cast(LTRIM(RTRIM(CUS_IDDrop Column1))	as nvarchar(500))	as			IDDrop ColumnressLine1,
565--		cast(LTRIM(RTRIM(CUS_IDDrop Column2)) as nvarchar(500)) as			IDDrop ColumnressLine2,
566--		cast(LTRIM(RTRIM(CUS_IDCITY)) as nvarchar(50)) as IDCity,
567--		cast(LTRIM(RTRIM(CUS_IDSTATE))	as nvarchar(10) ) as IDState,
568--		cast(idst.Country as nvarchar(100)) as IDCountry,
569--		cast(LTRIM(RTRIM(CUS_IDZIP))	as nvarchar(10)	) as			IDZipCode,
570--		cast (LTRIM(RTRIM(Cus_CellPhone)) as	nvarchar(20) ) as 				CellPhone,	
571--		cast(LTRIM(RTRIM(Cus_Email))	as nvarchar(100)) as 				Email,	
572--		cast(LTRIM(RTRIM(CUS_HEIGHT))	as nvarchar(50)) as				Height,
573--		CUS_WEIGHT	 as Weight,	
574--		lvhair.LookupValueId	 as Hair,-- Iteration 1 response
575--		lveye.LookupValueId	as Eyes,	-- Iteration 1 response
576--		lvrace.LookupValueId	as Race,	-- Iteration 1 response
577--		cast( CASE CUS_SEX
578--				WHEN 'F' then @FemaleId
579--				WHEN 'M' then @MaleId
580--				ELSE NULL
581--				END
582--		  as nvarchar(10))	as Sex,		
583--		CUS_BIRTHDate as BirthDate,	
584--		cast(LTRIM(RTRIM(CUS_BIRTHCITY)) as 	nvarchar(50)) as	BirthCity,
585--		cast(LTRIM(RTRIM(CUS_BIRTHSTATE))	as nvarchar(10)) as		BirthState,
586--		cast(LTRIM(RTRIM(CUS_COUNTRY)) as nvarchar(100)) as BirthCountry ,				
587--		cast(LTRIM(RTRIM(CUS_MARKS))	as nvarchar(100)	) as	Features,	
588--		cast(LTRIM(RTRIM(CUS_COMMENT))	as nvarchar(500)) as	Comments,
589--		cast (LTRIM(RTRIM(CUS_EMPLOYER))	as nvarchar(200)) as	EmployeeName,		
590--		cast(LTRIM(RTRIM(CUS_EMPAD1))	as nvarchar(500)) as		EmpDrop ColumnressLine1,	
591--		cast(LTRIM(RTRIM(CUS_EMPAD2))		as nvarchar(500)) as 	EmpDrop ColumnressLine2,
592--		cast(LTRIM(RTRIM(CUS_EMPCITY)) as nvarchar(50)	 ) as	EmpCity,		
593--		cast(LTRIM(RTRIM(CUS_EMPSTATE))	as nvarchar(10)	) as	EmpState,
594--		CAST(empst.Country as nvarchar(100)) as EmpCountry ,	
595--		cast(LTRIM(RTRIM(CUS_EMPZIP)) as	nvarchar(10))		as	EmpZipCode,	
596--		iif(CUS_AC2 = '','',cast(LTRIM(RTRIM(CUS_AC2))+'-'+LTRIM(RTRIM(CUS_EMPPHONE))	as nvarchar(20)) ) as		EmpPhone1,	
597--		Cus_Military	as	Military,		
598--		CUS_KNOWN	as		Known,
599--		iif(CUS_SPECIAL = 1,NULL,reflv.lookupValueId ) as Referred,
600--		cast(LTRIM(RTRIM(CUS_fflnum)) as nvarchar(20)) as FedFirearmNumber,
601--		Cus_FFLExpireDate as FedFirearmExpiryDate,
602--		cast(LTRIM(RTRIM(CUS_VEHIC1)) as nvarchar(100)) as VehicleMake,
603--		cast(LTRIM(RTRIM(CUS_VEHIC2)) as nvarchar(100)) as VehicleTagNumber,
604--		cast(LTRIM(RTRIM(CUS_VEHIC3)) as nvarchar(10)) as VehicleTagState, 	
605--		cast(LTRIM(RTRIM(Cus_TaxID))	as nvarchar(100)) as		TexId,
606--		cast(CUS_CREDIT	as decimal(12,3)) as		CustomerCredit,		
607--		--Cus_Preferred	as		PreferredRate	,	Update Later
608--		CUS_DELETE	as	IsDeleted,
609--		iif(CUS_DELETE = 1,0,1) as IsActive, -- Iteration 1 response
610--		Cus_AnnivDate	as		AnniversaryDate	,	
611--		Cus_Form8300Sent	as	Form8300Date,	
612--		cx.Cus_TxtMsgSent	as		LastTextDate	,	
613--		s.Cus_TxtMsg	as			IsSMSAllowed,
614--		cx.Cus_TxtMsgsendfinancials as IsSMSFinancials,						
615--		CUS_LOCKED	as			IsLocked	,
616--		cx.Cus_TxtMsgsendsales as IsSMSMaketing,
617--		iif(cx.Cus_TxtMsgDeclined IS NULL,0,cx.Cus_TxtMsgDeclined) as IsNeverSendSMS,	
618--		Cus_NoRemind	as		IsNeverSendRemindersLetter	,					
619--		cus_MLARate	as			IsMLARateApplied,-- not in actual source db
620--		Cus_PatriotAct as IsOFACSDNList,
621--		Cus_PatriotMsg as IsNeverDisplayOFACWarning,
622--		iif(LTRIM(RTRIM(REPLACE(CUS_SSNUM,'-',''))) = '',NULL,CAST(RIGHT(LTRIM(RTRIM(REPLACE(CUS_SSNUM,'-',''))),4) as nvarchar(10))) as SSNLastDigits,
623--		s.Cus_id	as SourceId		
624
625--		from EXT_cust s
626--		OUTER APPLY													----Drop Columned outer apply to compensate for an SBT switch issue in Classic (Devon Rhodes 26 April 2018)
627--		(
628--			SELECT TOP 1 * FROM EXT_custxtinfo CX				
629--			 WHERE S.CUS_ID = CX.Cus_id
630--		)CX
631--		--left join [PawnMaster_foxPro].dbo.custxtinfo cx
632--		--	on s.Cus_id = cx.Cus_id
633--		left outer join dbo.[state] st
634--			on s.CUS_ID1ISSUE = st.StateCode
635--		left outer join dbo.[state] st2
636--			on s.CUS_ID2ISSUE = st2.StateCode
637---- Iteration 1 response
638		
639--		left join EXT_Lookup_C ch
640--		on ch.lc_pk = s.CUS_HAIRFK
641
642--		left join CEXT_LookupValue lvhair
643--			on ch.lc_Descript = lvhair.Value and lvhair.LookupTypeId = @LookUpTypeHair
644
645--		left join EXT_Lookup_C ce
646--			on ce.lc_pk = s.CUS_EYESFK
647
648--		left join CEXT_LookupValue lveye
649--			on ce.lc_Descript = lveye.Value and lveye.LookupTypeId = @LookUpTypeEye
650
651--		left join EXT_Lookup_C cr
652--			on cr.lc_pk = s.CUS_RACEFK
653		
654--		left join CEXT_LookupValue lvrace
655--			on cr.lc_Descript = lvrace.Value and lvrace.LookupTypeId = @LookUpTypeRace
656
657--		left join  EXT_Lookup_C ct1
658--			on ct1.lc_pk = s.CUS_IDTYP1
659
660--		left join CEXT_LookupValue lvtype1
661--			on ct1.lc_Descript = lvtype1.Value and lvtype1.LookupTypeId = @IdType
662
663--		left join  EXT_Lookup_C ct2
664--			on ct2.lc_pk = s.CUS_IDTYP2
665
666--		left join CEXT_LookupValue lvtype2
667--			on ct2.lc_Descript = lvtype2.Value and lvtype2.LookupTypeId = @IdType
668
669--		left join EXT_Lookup_C ref
670--			on ref.lc_pk = s.CUS_SPECIAL and s.CUS_SPECIAL != 1
671
672--		left join CEXT_LookupValue reflv
673--			on ref.lc_Descript = reflv.Value and reflv.LookupTypeId = @IdRef
674
675--		left join dbo.[State] bst
676--			on bst.StateCode = s.CUS_BIRTHSTATE
677		
678--		left join dbo.[State] cst
679--			on cst.StateCode = s.CUS_STATE
680			
681--		left join dbo.[State] idst
682--			on idst.StateCode = s.CUS_IDSTATE
683			
684--		left join dbo.[State] empst
685--			on empst.StateCode = s.CUS_EMPSTATE
686
687--			left join CEXT_User u
688--			ON  s.LastUpdatedUSR_ID = u.SourceId
689
690--		WHERE LTRIM(RTRIM(s.CUS_LNAME)) != 'CASH CUSTOMER' and s.Cus_Store=@StoreId
691
692--END
693--GO
694/****** Object:  StoredProcedure [dbo].[FirearmRepairExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
695--SET ANSI_NULLS ON
696--GO
697--SET QUOTED_IDENTIFIER ON
698--GO
699
700---- =============================================
701---- Author:		Syed Mohsin
702---- Create date: <8/25/2016>
703---- Description:	<Extracts repair history for firearms >
704---- Sample Call: EXEC [dbo].[FirearmRepairExtract] 8
705---- =============================================
706
707--CREATE Proc [dbo].[FirearmRepairExtract]
708--@StoreId int
709--as
710--BEGIN
711--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
712--SELECT 
713--	it.InventoryItemId as InventoryItemId,
714--	iif(r.subStatus='NONE',0,1) as IsSentOut,
715--	CAST(r.SUBOUT as datetime) as SentOutDate,
716--	CAST(r.Quantity as int) as Quantity,
717--	v.VendorId,
718--	CAST(r.WORK1 as nvarchar(500)) as Comment,
719--	iif(r.subStatus='RETURNED',1,0) as IsReturned,
720--	CAST(r.SUBIN as datetime) as ReturnedDate ,
721--	CAST(r.Drop ColumnedCost as decimal(12,3)) as RepairCost,
722--	getdate() as CreatedDate,
723--	1 as CreatedBy, --Drop Column in package
724--	getdate() as UpdatedDate,
725--	1 as UpdatedBy
726	
727--FROM EXT_repair r
728
729--INNER JOIN EXT_items i
730--ON r.Items_PK = i.ITEMS_PK AND r.STO_PK = i.Sto_Pk
731
732--INNER JOIN EXT_Level1 l1
733--ON i.LEVEL1_FK = l1.lv1_pk AND l1.DESCRIPT = 'FIREARM'
734
735--INNER JOIN EXT_vend ven
736--on ven.VEN_PK = r.vend_fk 
737
738--INNER JOIN CEXT_Vendor v
739--ON v.SourceId = ven.VEN_id
740
741--INNER JOIN CEXT_InventoryItem it
742--ON it.SourceId = i.Items_ID
743
744--WHERE r.vend_fk != -1  AND r.STO_PK = @StoreId 
745
746--END
747
748--GO
749/****** Object:  StoredProcedure [dbo].[GunlogEntityExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
750--SET ANSI_NULLS ON
751--GO
752--SET QUOTED_IDENTIFIER ON
753GO
754
755-- =============================================
756-- Author:		Syed Mohsin
757-- Create date: <9/1/2016>
758-- Description:	<Extracts data for LevelN Lookup values>
759-- Sample Call: EXEC [dbo].[GunlogEntityExtract] 8 , 180, 5
760
761--Updated By: Syed Mohsin
762--Updated Date: <9/29/2016>
763--Comments: Drop Columned mapping for newly Drop Columned columns
764
765--Updated By: Syed Mohsin
766--Updated Date: <12/1/2016>
767--Comments: Drop Columned utc offset
768-- =============================================
769--CREATE Proc [dbo].[GunlogEntityExtract]
770--@SourceStoreId int , @TargetStoreId int, @UtcOffset int
771
772--AS
773--BEGIN
774--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
775 
776--DECLARE @CreatedDate datetime
777--SET @CreatedDate = (select dateDrop Column(HOUR,@UtcOffset,min(gt_date))from EXT_guntrans)
778
779--DECLARE @UpdatedDate datetime
780--SET @UpdatedDate = (select DATEDrop Column(HOUR,@UtcOffset,max(gt_date))from EXT_guntrans)
781
782--Select 
783--       ISNULL(@CreatedDate,DATEDrop Column(HOUR,@UtcOffset,GETDATE())) as CreatedDate,
784--       1 as CreatedBy,
785--       ISNULL(@UpdatedDate,DATEDrop Column(HOUR,@UtcOffset,GETDATE())) as UpdatedDate,
786--       u.UserId as UpdatedBy,
787--       @TargetStoreId as StoreID,
788
789--       gl.GunLogId as GunLogId,
790--       Cast ( BUYFNAME as nvarchar(100) ) as ReceiptFirstName,
791--       Cast ( BUYMNAME as nvarchar(100) ) as ReceiptMiddleName,
792--       Cast ( BUYLNAME as nvarchar(250) ) as ReceiptLastName,
793--       Cast ( BUYDrop Column1 as nvarchar(500) ) as ReceiptDrop ColumnressLine1,
794--       Cast ( BuyDrop Column2 as nvarchar(500) ) as ReceiptDrop ColumnressLine2,
795--       Cast ( BUYCITY as nvarchar(50) ) as ReceiptCity,
796--       Cast ( BUYSTATE as nvarchar(50) ) as ReceiptState,
797--	   Cast ( St.Country as nvarchar(100) ) as ReceiptCountry,
798--       Cast ( BUYZIP as nvarchar(10) ) as ReceiptZipCode,
799--       Cast ( BUYIDTYPE as nvarchar(150) ) as ReceiptIDType,
800--       Cast ( BUYIDNUM as nvarchar(50) ) as ReceiptNumber,
801--       Cast ( BUYDATE as datetime ) as ReceiptDate,
802--       Cast ( SOLDFNAME as nvarchar(100) ) as DispositionFirstName,
803--       Cast ( SOLDMNAME as nvarchar(100) ) as DispositionMiddleName,
804--       Cast ( SOLDLNAME as nvarchar(250) ) as DispositionLastName,
805--       Cast ( SOLDDrop Column1 as nvarchar(500) ) as DispositionDrop ColumnressLine1,
806--       Cast ( SOLDDrop Column2 as nvarchar(500) ) as DispositionDrop ColumnressLine2,
807--       Cast ( SOLDCITY as nvarchar(50) ) as DispositionCity,
808--       Cast ( SOLDSTATE as nvarchar(50) ) as DispositionState,
809--	   Cast ( st2.Country as nvarchar(100) ) as DispositionCountry,
810--       Cast ( SOLDZIP as nvarchar(10) ) as DispositionZipCode,
811--       Cast ( SOLDIDTYPE as nvarchar(150) ) as DispositionIDType,
812--       Cast ( SOLDIDNUM as nvarchar(50) ) as DispositionNumber,
813--       Cast ( SOLDDATE as datetime ) as DispositionDate,
814--	   CAST ( inv.InventoryItemId as nvarchar(50) ) as InvItemTypeId,
815--	   CAST ( inv.FirearmImporterId as nvarchar(50) ) as InvFirearmImporterId,
816--	   CAST ( inv.ItemTypeBrandId as nvarchar(50) ) as InvItemTypeBrandId,
817--	   CAST ( inv.FirearmCaliberGaugeId as nvarchar(50) ) as InvFirearmCaliberGaugeId,
818--	   CAST ( inv.FirearmActionId as nvarchar(50) ) as InvFirearmActionId,
819--	   CAST ( inv.FirearmCondition as nvarchar(50) ) as InvFirearmCondition,
820--	   CAST ( inv.Model as nvarchar(50) ) as InvModel,
821--	   CAST ( inv.SerialNumber as nvarchar(50) ) as InvSerialNumber
822	   
823	   
824
825--from EXT_gunlog g
826--       inner join CEXT_GunLog gl
827--              on g.GunLog_PK=gl.SourceID
828--		left join CEXT_User u
829--			on u.SourceId = LastUpdatedUSR_ID
830--		left join EXT_items i
831--			on i.INVNUM = g.INVNUM
832--		left join CEXT_InventoryItem inv
833--			on inv.SourceId = Items_ID
834--		left join dbo.State st
835--			on st.StateCode = g.BUYSTATE
836--		left join dbo.State st2
837--			on st2.StateCode = g.SOLDSTATE
838--		where g.STO_PK=@SourceStoreId
839--END
840--GO
841--/****** Object:  StoredProcedure [dbo].[GunlogExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
842--SET ANSI_NULLS ON
843--GO
844--SET QUOTED_IDENTIFIER ON
845--GO
846
847-- =============================================
848-- Author:		Syed Mohsin
849-- Create date: <8/15/2016>
850-- Description:	<Extracts data for gunlogs>
851-- Sample Call: EXEC [dbo].[GunlogExtract] 8 , 179 , 5
852
853--Modified date: <12/1/2016>
854--Modified by: Syed Mohsin
855--Comments: Drop Columned utc date offset
856-- =============================================
857
858--CREATE Proc [dbo].[GunlogExtract]
859--@SourceStoreId int, @TargetStoreId int, @UtcOffset int
860
861--AS
862
863--BEGIN
864--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
865--DECLARE @CreatedDate datetime
866--SET @CreatedDate = (select dateDrop Column(HOUR,@UtcOffset,min(gt_date))from EXT_guntrans)
867
868--DECLARE @UpdatedDate datetime
869--SET @UpdatedDate = (select DATEDrop Column(HOUR,@UtcOffset,max(gt_date))from EXT_guntrans)
870
871--SELECT * FROM (
872
873--Select ROW_NUMBER() over (partition by GunLog_PK order by gt_pk desc) as Num1,
874--       ISNULL(@CreatedDate,DATEDrop Column(HOUR,@UtcOffset,GETDATE())) as CreatedDate,
875--       1 as CreatedBy,
876--       ISNULL(@UpdatedDate,DATEDrop Column(HOUR,@UtcOffset,GETDATE())) as UpdatedDate,
877--       u.UserId as UpdatedBy,
878--       @TargetStoreId as StoreID,
879--       it.InventoryItemId as InventoryItemId,
880--       Cast(LTRIM(RTRIM(GunLogNum)) as nvarchar(50) ) as LogNumber,  
881              
882--       Cast ( PICKDATE as datetime ) as PickDate,
883--       iif(gt.gt_type = 'HOLD' and PICKDATE is null,1,0) as IsPlaceInHoldPeriod, -- todo revisit if gt_type='Hold' exists and PickDate in NULL
884--       Cast ( TRANSNUM as nvarchar(100) ) as ATFNumber,
885--       Cast ( NICSTN as nvarchar(250) ) as NICSNumber,
886--       Cast ( RTRIM(LTRIM(g.COMMENT1)) as nvarchar(250) ) as Comments1,
887--       Cast ( RTRIM(LTRIM(g.COMMENT2)) as nvarchar(250) ) as Comments2,
888--       Cast ( GunLog_PK as int ) as SourceId
889
890--from EXT_gunlog g
891--inner join EXT_guntrans gt
892--on gt.invnum = g.INVNUM
893--inner join CEXT_InventoryItem it
894--on it.sourceinventorynumber = g.INVNUM
895--left join CEXT_User u
896--on u.SourceId = g.LastUpdatedUSR_ID
897--where it.StoreId=@TargetStoreId and g.STO_PK = @SourceStoreId
898--)q where q.Num1 = 1
899
900--END
901--GO
902/****** Object:  StoredProcedure [dbo].[GunlogHistoryExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
903--SET ANSI_NULLS ON
904--GO
905--SET QUOTED_IDENTIFIER ON
906--GO
907
908---- =============================================
909---- Author:		Syed Mohsin
910---- Create date: <9/1/2016>
911---- Description:	<Extracts gun log history>
912---- Sample Call: EXEC [dbo].[GunlogHistoryExtract] 8 , 2 , 5
913---- =============================================
914--CREATE Proc [dbo].[GunlogHistoryExtract]
915--@SourceStoreId int , @TargetStoreId int, @UtcOffset int
916
917--AS
918--BEGIN
919--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
920
921--Select 
922--       dateDrop Column(HOUR,@UtcOffset,gt_date) as CreatedDate,
923--       1 as CreatedBy,
924--       dateDrop Column(HOUR,@UtcOffset,gt_date) as UpdatedDate,
925--       1 as UpdatedBy,
926--       @TargetStoreId as StoreID,
927
928--       it.InventoryItemId as InventoryItemId,
929--       gt.GunLogTypeId as GunLogTypeId,
930--       Cast ( comment1 as nvarchar(MAX) ) as Comment
931       
932
933       
934
935--from EXT_guntrans g 
936--	inner join CEXT_InventoryItem it
937--	on it.sourceinventorynumber = g.INVNUM
938--	inner join CEXT_GunLogType gt
939--		on gt.[Type] = CASE g.gt_type
940--						WHEN 'REPAIR RET' THEN 'Repair Return'
941--						WHEN 'REPAWN' THEN 'Pawn'
942--						WHEN 'SOLD' THEN 'Sale'
943--						WHEN 'VOID SALE' THEN 'Void'
944--						ELSE g.gt_type
945--						END 
946
947--	where g.sto_pk = @SourceStoreId and it.StoreId = @TargetStoreId
948--END
949--GO
950/****** Object:  StoredProcedure [dbo].[InsertMasterUser]    Script Date: 6/13/2018 9:39:35 AM ******/
951SET ANSI_NULLS ON
952GO
953SET QUOTED_IDENTIFIER ON
954GO
955
956CREATE Proc [dbo].[InsertMasterUser]
957@SourceStoreId int ,@TargetStoreId int , @CompanyId int, @Password nvarchar(150)
958
959AS
960
961BEGIN
962SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
963DECLARE @firstuser int
964SET @firstuser = (Select Min(UserId) from CEXT_User )
965
966INSERT INTO CEXT_User ([UserName]
967      ,[Password]
968      ,[CreatedDate]
969      ,[CreatedBy]
970      ,[FirstName]
971      ,[LastName]
972      ,[StartDate]
973      ,[EndDate]
974      ,[MaxTransactionAmountBuy]
975      ,[MaxTransactionAmountPawn]
976      ,[MaxTransactionAmountSale]
977      ,[SaleDiscount]
978      ,[StatusId]
979	  ,[IsDeleted]
980      ,[IsLoggedIn]
981      ,[CompanyId]
982      ,[StoreId]
983      ,[Comment]
984      ,[IsRequiredChangePassword]
985      ,[IsDefaultUser]
986      ,[CultureId]
987      ,[IsUserMigrated]
988	  ,SourceId) OUTPUT Inserted.UserId
989
990select  
991	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,
992	@Password as Password, --Drop Column in package
993	GetDate() as CreatedDate,
994	@firstuser as CreatedBy, -- Drop Column in package
995	cast( USR_FNAME  as nvarchar(100)) as FirstName,	
996	cast(USR_LNAME	as nvarchar(100)) as	LastName,		
997	USR_STARTDATE	as StartDate,	
998	USR_TERMINATE	as EndDate	,
999	CAST(iif(BuyLimit=999999.99,9999999999.99,BuyLimit) as decimal(12,2))		as MaxTransactionAmountBuy,
1000	CAST(iif(PawnLimit=999999.99,9999999999.99,PawnLimit) as decimal(12,2))		as MaxTransactionAmountPawn,
1001	CAST(iif(OptionLimit=999999.99,9999999999.99,OptionLimit) as decimal(12,2))		as MaxTransactionAmountSale,
1002	USR_DISCOUNT	as SaleDiscount,	
1003	0		as StatusId,
1004	0 as IsDeleted,
1005	0			as IsLoggedIn,
1006	@CompanyId  as CompanyId, 
1007	@TargetStoreId		as StoreId,		
1008	cast(USR_NOTES	as nvarchar(200))		as Comment,		
1009	1			as IsRequiredChangePassword,
1010	0			as IsDefaultUser,
1011	1			as CultureId,	
1012	1			as IsUserMigrated,
1013	USR_ID	as SourceId
1014
1015	from EXT_users s
1016		left outer join CEXT_User t
1017			on s.USR_LANID =t.UserName
1018		where USR_STORE = @SourceStoreId AND s.USR_LANID = '***'
1019
1020END
1021GO
1022/****** Object:  StoredProcedure [dbo].[InventoryItemExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
1023--SET ANSI_NULLS ON
1024--GO
1025--SET QUOTED_IDENTIFIER ON
1026--GO
1027
1028---- =============================================
1029---- Author:		Syed Mohsin
1030---- Create date: <8/22/2016>
1031---- Description:	<Extracts Inventory items info >
1032---- Sample Call: EXEC [dbo].[InventoryItemExtract] 1 , 371 , 5
1033
1034---- Modified By: Syed Mohsin
1035---- Modified date: <12/21/2016>
1036---- Comments: IsInventory checks for statuscodes Drop Columned
1037
1038---- Modified By: Syed Mohsin
1039---- Modified date: <01/19/2017>
1040---- Comments: From customer/vendor logic updated BUG# 7242
1041
1042---- Modified By: Syed Mohsin
1043---- Modified date: <01/23/2017>
1044---- Comments: IsDeleted Migration rule updated and TitleLoan PawnItems removed.
1045
1046---- Modified By: Syed Mohsin
1047---- Modified date: <02/14/2017>
1048---- Comments: From customer/vendor logic updated again. Firearm rule applies on all items rather just Pawn items
1049
1050---- Modified By: Syed Mohsin
1051---- Modified date: <02/21/2017>
1052---- Comments: From customer logic updated. If cus_fk does not have value then check for consignor
1053
1054---- Modified By: Syed Mohsin
1055---- Modified date: <02/22/2017>
1056---- Comments: From customer logic updated. Customer can only exist if FireArm item. Also if ticketnum = 0 then ignore Cus_Fk and use CONSIGNOR
1057
1058---- Modified By: Syed Mohsin
1059---- Modified date: <03/07/2017>
1060---- Comments: Drop Columned IsInventory check for Status B
1061---- =============================================
1062
1063--CREATE Proc [dbo].[InventoryItemExtract]
1064--@StoreID int , @TargetStoreId int, @UtcTime int
1065--as
1066--BEGIN
1067--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
1068
1069--DECLARE @SequenceNum int
1070--SET @SequenceNum = (SELECT ISNULL(MAX(SequenceNumber),0) FROM CEXT_SystemNumber WHERE PermissionKey = 'InventoryItem' AND StoreId = @TargetStoreId)
1071----Following Statements are to get LookupTypeIds for columns that needs to be looked up from LookupValue
1072
1073--DECLARE @lookupTypeIDItem int
1074--SET @lookupTypeIDItem = (SELECT LookupTypeId from CEXT_LookupType  
1075--							where LookupTypeName='Item')
1076
1077--DECLARE @lookupTypeIDJewel int
1078--SET @lookupTypeIDJewel = (SELECT LookupTypeId from CEXT_LookupType  
1079--							where LookupTypeName='Jewelry Info')
1080
1081--DECLARE @lookupTypeIDGun int
1082--SET @lookupTypeIDGun = (SELECT LookupTypeId from CEXT_LookupType  
1083--							where LookupTypeName='Gun Info Codes')
1084
1085--DECLARE @lookupTypeIDBin int
1086--SET @lookupTypeIDBin = (SELECT LookupTypeId from CEXT_LookupType  
1087--							where LookupTypeName='Bin')
1088
1089--DECLARE @lookupTypeIDBrand int
1090--	  SET @lookupTypeIDBrand = (SELECT LookupTypeId from CEXT_LookupType  
1091--							where LookupTypeName='Brand' );
1092
1093--DECLARE @lookupTypeIDColor int
1094--	  SET @lookupTypeIDColor = (SELECT LookupTypeId from CEXT_LookupType  
1095--							where LookupTypeName='Color' AND ParentLookupTypeId=@lookupTypeIDItem);
1096
1097--DECLARE @lookupTypeIDCondition int
1098--	  SET @lookupTypeIDCondition = (SELECT LookupTypeId from CEXT_LookupType  
1099--							where LookupTypeName='Condition' AND ParentLookupTypeId=@lookupTypeIDItem);
1100
1101--DECLARE @lookupTypeIDmetal int
1102--	  SET @lookupTypeIDmetal = (SELECT LookupTypeId from CEXT_LookupType  
1103--							where LookupTypeName='Metal' AND ParentLookupTypeId=@lookupTypeIDJewel);
1104
1105--DECLARE @lookupTypeIDkarat int
1106--	  SET @lookupTypeIDkarat = (SELECT LookupTypeId from CEXT_LookupType  
1107--							where LookupTypeName='Karat' AND ParentLookupTypeId=@lookupTypeIDJewel);
1108
1109--DECLARE @lookupTypeIDgender int
1110--	  SET @lookupTypeIDgender = (SELECT LookupTypeId from CEXT_LookupType  
1111--							where LookupTypeName='Gender' AND ParentLookupTypeId=@lookupTypeIDJewel);
1112
1113--DECLARE @lookupTypeIDstyle int
1114--	  SET @lookupTypeIDstyle = (SELECT LookupTypeId from CEXT_LookupType  
1115--							where LookupTypeName='Style' AND ParentLookupTypeId=@lookupTypeIDJewel);
1116
1117--DECLARE @lookupTypeIDsize int
1118--	  SET @lookupTypeIDsize = (SELECT LookupTypeId from CEXT_LookupType  
1119--							where LookupTypeName ='Size / Length' AND ParentLookupTypeId=@lookupTypeIDJewel);
1120
1121--DECLARE @lookupTypeIDaction int
1122--	  SET @lookupTypeIDaction = (SELECT LookupTypeId from CEXT_LookupType  
1123--							where LookupTypeName ='Action' AND ParentLookupTypeId=@lookupTypeIDGun);
1124
1125--DECLARE @lookupTypeIDfinish int
1126--	  SET @lookupTypeIDfinish = (SELECT LookupTypeId from CEXT_LookupType  
1127--							where LookupTypeName ='Finish' AND ParentLookupTypeId=@lookupTypeIDGun);
1128
1129--DECLARE @lookupTypeIDbarrel int
1130--	  SET @lookupTypeIDbarrel = (SELECT LookupTypeId from CEXT_LookupType  
1131--							where LookupTypeName ='Barrel' AND ParentLookupTypeId=@lookupTypeIDGun);
1132
1133--DECLARE @lookupTypeIDcaliber int
1134--	  SET @lookupTypeIDcaliber = (SELECT LookupTypeId from CEXT_LookupType  
1135--							where LookupTypeName ='Caliber' AND ParentLookupTypeId=@lookupTypeIDGun);
1136
1137--DECLARE @lookupTypeIDimporter int
1138--	  SET @lookupTypeIDimporter = (SELECT LookupTypeId from CEXT_LookupType  
1139--							where LookupTypeName ='Importer' AND ParentLookupTypeId=@lookupTypeIDGun);
1140
1141--DECLARE @StoreCode int
1142--	  SET @StoreCode = (select StoreCode from CEXT_Store where StoreId = @TargetStoreId);
1143
1144---- =============================================
1145
1146---- Extracting data from source
1147--	  SELECT CAST(@StoreCode as nvarchar(50)) as InventoryNumber, -- StoreCode+##### will also be used for barcode
1148--			@SequenceNum as SequenceNumber,--Used for Incremental Values
1149--			CAST(i.INVNUM as nvarchar(50)) as SourceInventoryNumber,
1150--			it.ItemTypeId,
1151--			itb.ItemTypeBrandId as ItemTypeBrandId,
1152--			CAST(RTRIM(LTRIM(i.MODELNUM)) as nvarchar(30)) as Model,
1153--			CAST(RTRIM(LTRIM(i.SERIALNUM)) as nvarchar(50)) as SerialNumber,
1154--			CAST(RTRIM(LTRIM(i.OWNERNUM)) as nvarchar(50)) as OwnerNumber,
1155--			CAST(i.RESALEAMT as decimal(12,3)) as Resale,
1156--			lvcon.LookupValueId as ItemConditionid ,
1157--			lvcol.LookupValueId as ItemColorid ,
1158--			CAST(i.LOWSLPRICE as decimal(12,3)) as [Min],
1159--			CAST(RTRIM(LTRIM(i.DESCRIPT)) as nvarchar(500))  as Comment,
1160--			lvmet.LookupValueId as MetalId,
1161--			lvkar.LookupValueId as  KaratId,
1162--			CAST(j.[Weight] as decimal(9,3)) as JewelryWeight, --Possible Data loss
1163--			lvwgt.LookupValueId as JewelryWeightUnitId ,
1164--			lvgen.LookupValueId as JewelryGenderId,
1165--			lvsty.LookupValueId as JewelryStyleId,
1166--			lvsize.LookupValueId as JewelrySizeLengthId,
1167--			lvact.LookupValueId as FirearmActionId,
1168--			lvfin.LookupValueId as FirearmFinishId,
1169--			lvbar.LookupValueId as FirearmBarrelsNumberId,
1170--			CAST(g.[Length] as nvarchar(20)) as FirearmLength,
1171--			lvcal.LookupValueId as FirearmCaliberGaugeId,
1172--			CAST(g.Condition as nvarchar(50)) as FirearmCondition,
1173--			lvimp.LookupValueId as FirearmImporterId,
1174--			st.StatusId as StatusId,
1175--			i.IsBulkItem,
1176--			iif(i.NEWITEM=0,0,1) as IsNewItem, -- Is Default value needed ?
1177--			i.SalesTaxEx as IsSalesTaxExempt,
1178--			en.EntityId as InventoryItemTypeSourceId,
1179--			iif(lvf.DESCRIPT='FIREARM',iif(c.CustomerId IS NULL, v.vendorId , c.customerid) ,v.vendorId) 
1180--			 as InventoryItemSourceId, --Logic updated as per new business rules
1181--			CAST(RTRIM(LTRIM(i.UPC)) as nvarchar(14)) as UPC,
1182--			CAST(i.MAXQUANT as int) as MaxQuantity, -- Possible data Loss
1183--			CAST(i.REORDER as int) as ReOrderLevel, -- Possible data Loss
1184--			iif(i.DELETEDATE IS NOT NULL AND i.status = 'D',1,0) as IsDeleted, --IsDeleted Logic Updated
1185--			DATEDrop Column(HOUR,@UtcTime,iif(i.DateItemEntered IS NULL,getdate(),i.DateItemEntered)) as CreatedDate,
1186--			1 as CreatedBy,-- Drop Column in package
1187--			DATEDrop Column(HOUR,@UtcTime,iif(i.DELETEDATE IS NULL,i.DateItemEntered,i.DELETEDATE)) as UpdatedDate,
1188--			u.UserId as UpdatedBy,
1189--			@TargetStoreId as StoreId,
1190--			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,
1191--			CAST(iif(i.DELETEDATE IS NOT NULL AND i.status = 'D',RTRIM(LTRIM(i.DELETECOM)),NULL) as nvarchar(250)) as ReasonForDelete, --IsDeleted Logic Updated
1192--			lvbin.LookupValueId as BinNumberId,
1193--			i.REPAWNED as IsReused,
1194--			--SplitFromItems_ID Update later in Package
1195--			iif(i.TICKETNUM > 0 AND i.[STATUS] = 'I',1,0) as IsForfeit,
1196--			i.ITEMS_ID as SourceId
1197
1198--  FROM EXT_items i --Main source table
1199  
1200--  LEFT JOIN EXT_Detail_G g --Source table for gun information
1201--  ON i.Sto_Pk = g.Sto_FK AND i.ITEMS_PK = g.Items_FK
1202  
1203--  LEFT JOIN EXT_Detail_J j --Source table for Jewelery information
1204--  ON i.Sto_Pk = j.Sto_FK AND i.ITEMS_PK = j.Items_FK
1205  
1206--  LEFT JOIN EXT_Level5 l5
1207--  ON l5.lv5_PK = i.LEVEL5_FK
1208
1209--    LEFT JOIN CEXT_LookupValue lv  -- IS THIS CONDITION RIGHT?
1210--  ON lv.LookupTypeId = @lookupTypeIDBrand AND lv.Value = LTRIM(RTRIM(l5.DESCRIPT)) --AND lv.StoreId = @TargetStoreId
1211
1212--  --Joins to get lookupValueId for columns that needs to be looked up
1213--  LEFT JOIN CEXT_LookupValue lvcon
1214--  ON i.Condition != 1 AND lvcon.LookupTypeId = @lookupTypeIDCondition AND lvcon.SourceId = i.Condition
1215
1216--  LEFT JOIN CEXT_LookupValue lvcol
1217--  ON i.Color != 1 AND lvcol.LookupTypeId = @lookupTypeIDColor AND lvcol.SourceId = i.Color
1218
1219--  LEFT JOIN CEXT_LookupValue lvmet
1220--  ON j.Metal_FK != 1 AND lvmet.LookupTypeId = @lookupTypeIDmetal AND lvmet.SourceId = j.Metal_FK
1221
1222--  LEFT JOIN CEXT_LookupValue lvkar
1223--  ON j.Karat_FK != 1 AND lvkar.LookupTypeId = @lookupTypeIDkarat AND lvkar.SourceId = j.Karat_FK 
1224
1225--  LEFT JOIN CEXT_LookupValue lvwgt
1226--  ON j.WgtUnit != '' AND lvwgt.Value = CASE WHEN (j.WgtUnit = 'G')
1227--											Then 'Grams'
1228--											WHEN (j.WgtUnit = 'O')
1229--											Then 'Ounces'
1230--											WHEN (j.WgtUnit = 'D')
1231--											Then 'DWT'
1232--										END
1233					
1234  
1235--  LEFT JOIN CEXT_LookupValue lvgen
1236--  ON j.Gender_FK != 1 AND lvgen.LookupTypeId = @lookupTypeIDgender AND lvgen.SourceId = j.Gender_FK
1237
1238--  LEFT JOIN CEXT_LookupValue lvsty
1239--  ON j.Style_FK != 1 AND lvsty.LookupTypeId = @lookupTypeIDstyle AND lvsty.SourceId = j.Style_FK
1240  
1241--  LEFT JOIN CEXT_LookupValue lvsize
1242--  ON j.Sizelen_FK != 1 AND lvsize.LookupTypeId = @lookupTypeIDsize AND lvsize.SourceId = j.Sizelen_FK
1243
1244--  LEFT JOIN CEXT_LookupValue lvact
1245--  ON g.Action_FK != 1 AND lvact.LookupTypeId = @lookupTypeIDaction AND lvact.SourceId = g.Action_FK
1246
1247--  LEFT JOIN CEXT_LookupValue lvfin
1248--  ON g.Finish_FK != 1 AND lvfin.LookupTypeId = @lookupTypeIDfinish AND lvfin.SourceId = g.Finish_FK 
1249
1250--  LEFT JOIN CEXT_LookupValue lvbar
1251--  ON g.Barrel_FK != 1 AND lvbar.LookupTypeId = @lookupTypeIDbarrel AND lvbar.SourceId = g.Barrel_FK 
1252
1253--  LEFT JOIN CEXT_LookupValue lvcal
1254--  ON g.Caliber_FK != 1 AND lvcal.LookupTypeId = @lookupTypeIDcaliber AND lvcal.SourceId = g.Caliber_FK 
1255
1256--  LEFT JOIN CEXT_LookupValue lvimp
1257--  ON g.ImporterFK != 1 AND lvimp.LookupTypeId = @lookupTypeIDimporter AND lvimp.SourceId = g.ImporterFK
1258
1259--  LEFT JOIN CEXT_LookupValue lvbin
1260--  ON lvbin.LookupTypeId = @lookupTypeIDBin AND lvbin.Value = i.BIN AND lvbin.StoreId = @TargetStoreId
1261--  -- =============================================
1262  
1263--  --To get the status translated values
1264--  LEFT JOIN common.SourceTargetKeyMapping map
1265--  ON map.SourceTable='ItemStatus' AND map.SourceKey = CASE i.[STATUS]
1266--														WHEN 'L' THEN 'I'
1267--														WHEN 'S' THEN 'I'
1268--														ELSE i.[STATUS]
1269--														END
1270
1271--  LEFT JOIN CEXT_Status st
1272--  ON map.TargetKey = st.StatusCode
1273
1274--  --Join from user table to get TargetUserId for UpdatedBy column
1275--  LEFT JOIN CEXT_User u
1276--  ON u.SourceId = i.LastUpdatedUSR_ID
1277
1278--  INNER JOIN CEXT_ItemType it 
1279--  ON it.SourceId = CASE WHEN(LEVEL4_FK != 1) THEN LEVEL4_FK
1280--WHEN (LEVEL3_FK != 1) THEN LEVEL3_FK
1281--WHEN (LEVEL2_FK != 1) THEN LEVEL2_FK
1282--ELSE LEVEL1_FK END
1283
1284
1285
1286
1287-- LEFT JOIN EXT_Level1 lvf
1288-- ON lvf.lv1_pk = i.LEVEL1_FK and lvf.sto_pk=@StoreID
1289
1290-- 	LEFT JOIN CEXT_ItemType it2
1291--	on it2.ItemTypeName = LTRIM(RTRIM(lvf.DESCRIPT)) and it2.ParentItemTypeId IS NULL
1292
1293--    LEFT JOIN CEXT_ItemTypeBrand itb  -- IS THIS CONDITION RIGHT?
1294--  ON itb.BrandId = lv.LookupValueId and it2.ItemTypeId = itb.ItemTypeId
1295
1296-- LEFT JOIN CEXT_Entity en 
1297-- 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' 
1298-- WHEN i.VENDOR != 0 AND i.VENDOR != -1 THEN 'Vendor'
1299-- ELSE '' END -- Condtion Updated as per new busines logics
1300
1301-- left join EXT_cust cu
1302-- on cu.Cus_PK = CASE WHEN i.CUS_FK != -1 AND i.CUS_FK != 0 AND i.TICKETNUM != 0 THEN i.CUS_FK
1303--WHEN (i.CUS_FK = -1 OR i.CUS_FK = 0) AND CONSIGNOR != 0 AND CONSIGNOR != -1 AND CONSIGNOR != 1 THEN CONSIGNOR  
1304--END -- Drop Columned consignor field in join if Cus_Fk does not have the value.
1305 
1306-- LEFT JOIN CEXT_Customer c
1307-- ON c.SourceId = cu.Cus_id 
1308
1309-- left join EXT_vend ve
1310-- on ve.VEN_PK = i.VENDOR
1311
1312-- LEFT JOIN CEXT_Vendor v
1313-- ON v.SourceId = ve.VEN_id
1314
1315
1316--LEFT JOIN EXT_pawn p --Join for TitleLoan Pawns
1317--ON p.PWN_id = i.PWN_id
1318
1319--WHERE i.Sto_Pk = @StoreId and (TitleLoan = 0 OR TitleLoan IS NULL) --Removing TitleLoan PawnItems
1320
1321--END
1322--GO
1323/****** Object:  StoredProcedure [dbo].[InventoryItemLotExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
1324--SET ANSI_NULLS ON
1325--GO
1326--SET QUOTED_IDENTIFIER ON
1327--GO
1328
1329---- =============================================
1330---- Author:		Syed Mohsin
1331---- Create date: <8/22/2016>
1332---- Description:	<Extracts Data for table InventoryItemLot >
1333---- Sample Call: EXEC [dbo].[InventoryItemLotExtract] 8 , 190 , 5
1334
1335---- Updated By: Syed Mohsin
1336---- Update date: <9/27/2016>
1337---- Comments: StatusId hardcoded 28 which means LotIn.
1338
1339---- Updated By: Syed Mohsin
1340---- Update date: <12/19/2016>
1341---- Comments: InventoryItemLotNumber's logic changed to simple increment.
1342---- =============================================
1343
1344--CREATE Proc [dbo].[InventoryItemLotExtract]
1345--@SourceStoreID int , @TargetStoreId int, @UtcTime int
1346--as
1347--BEGIN
1348
1349--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
1350
1351--DECLARE @LotNumber int
1352--SET @LotNumber = (SELECT ISNULL(max(SequenceNumber),0) FROM CEXT_SystemNumber where PermissionKey = 'InventoryItemlot' and StoreId=@TargetStoreId)
1353
1354--SELECT 
1355--	it.InventoryItemId,
1356--	28 as StatusId ,
1357--	CAST(@LotNumber + ROW_NUMBER() over (order by (select NULL)) as nvarchar(50)) as InventoryItemLotNumber, -- (Auto-Incremented)
1358--	CAST(i.OnHand as decimal(12,3)) as Quantity,
1359--	CAST(AMOUNT as decimal(12,3)) as Cost,
1360--	DATEDrop Column(HOUR,@UtcTime,iif(i.DateItemEntered IS NULL,getdate(),i.DateItemEntered)) as CreatedDate,
1361--	DATEDrop Column(HOUR,@UtcTime,iif(i.DateItemEntered IS NULL,getdate(),i.DateItemEntered)) as DateIn,
1362--	--DATEDrop Column(HOUR,@UtcTime,getdate()) as CreatedDate,
1363--	1 as CreatedBy, -- Drop Column in package
1364--	it.UpdatedDate as UpdatedDate,
1365--	it.UpdatedBy as UpdatedBy
1366
1367-- FROM EXT_items i
1368
1369--INNER JOIN CEXT_InventoryItem it
1370--	ON i.Items_ID = it.SourceId AND it.StoreId = @TargetStoreId
1371
1372--WHERE i.Sto_Pk = @SourceStoreId
1373
1374--END
1375--GO
1376/****** Object:  StoredProcedure [dbo].[InventoryItemLotOutScrapped]    Script Date: 6/13/2018 9:39:35 AM ******/
1377--SET ANSI_NULLS ON
1378--GO
1379--SET QUOTED_IDENTIFIER ON
1380--GO
1381
1382---- =============================================
1383---- Author:		Syed Mohsin
1384---- Create date: <3/10/2017>
1385---- Description:	<Extracts scrapped items for table InventoryItemLot for lotout >
1386---- Sample Call: EXEC [dbo].[InventoryItemLotOutScrapped] 1818
1387
1388---- Updated By: Syed Mohsin
1389---- Update date: <03/22/2017>
1390---- Comments: Jewelry items are not lotout
1391---- =============================================
1392
1393--CREATE Proc [dbo].[InventoryItemLotOutScrapped]
1394--@TargetStoreId int
1395--as
1396--BEGIN
1397
1398--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
1399
1400--DECLARE @LotNumber int;
1401--  SET @LotNumber=(SELECT MAX(CAST(InventoryItemLotNumber as int)) FROM CEXT_InventoryItemLot il INNER JOIN CEXT_InventoryItem it
1402--  ON it.InventoryItemId = il.InventoryItemId
1403-- where StoreId=@TargetStoreId)
1404
1405--DECLARE @JewelryTypeid int;
1406--SET @JewelryTypeid = ( SELECT ItemTypeId from CEXT_ItemType where ItemTypeName='JEWELRY' and ParentItemTypeId is null
1407--)
1408
1409
1410--select 
1411--SourceInventoryItemId as InventoryItemId,
1412--29 as StatusId,
1413--CAST(@LotNumber + ROW_NUMBER() over (order by (select NULL)) as nvarchar(50)) as InventoryItemLotNumber, -- (Auto-Incremented)
1414--CAST(Quantity*-1 as decimal(12,3)) as Quantity,
1415----[Quantity] * -1 as Quantity,
1416--Cost,
1417--DateIn,
1418--il.CreatedDate,
1419--il.CreatedBy
1420--from CEXT_InventoryItemLot il
1421--inner join CEXT_InventoryItem it on it.InventoryItemId = il.InventoryItemId and it.StoreId = @TargetStoreId
1422--inner join CEXT_InventoryItem its on its.InventoryItemId = il.SourceInventoryItemId
1423--left join CEXT_ItemType j4 on j4.ItemTypeId = its.ItemTypeId
1424--left join CEXT_itemtype j3 on j3.ItemTypeId = CASE WHEN j4.ParentItemTypeId is null THEN its.ItemTypeId ELSE j4.ParentItemTypeId END
1425--left join CEXT_itemtype j2 on j2.ItemTypeId = CASE WHEN j3.ParentItemTypeId is null THEN its.ItemTypeId ELSE j3.ParentItemTypeId END
1426--left join CEXT_itemtype j1 on j1.ItemTypeId = CASE WHEN j2.ParentItemTypeId is null THEN its.ItemTypeId ELSE j2.ParentItemTypeId END
1427--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
1428--and 
1429--its.ItemTypeId not in (select ItemTypeId from CoEXT_ItemType where ParentItemTypeId = @JewelryTypeid)
1430
1431--END
1432--GO
1433/****** Object:  StoredProcedure [dbo].[InventoryItemLotScrapped]    Script Date: 6/13/2018 9:39:35 AM ******/
1434--SET ANSI_NULLS ON
1435--GO
1436--SET QUOTED_IDENTIFIER ON
1437--GO
1438
1439---- =============================================
1440---- Author:		Syed Mohsin
1441---- Create date: <3/10/2017>
1442---- Description:	<Extracts scrapped items for table InventoryItemLot >
1443---- Sample Call: EXEC [dbo].[InventoryItemLotScrapped] 8 , 1818 , 5
1444---- =============================================
1445
1446--CREATE Proc [dbo].[InventoryItemLotScrapped]
1447--@SourceStoreID int , @TargetStoreId int, @UtcTime int
1448--as
1449--BEGIN
1450
1451--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
1452
1453--DECLARE @LotNumber int;
1454--  SET @LotNumber=(SELECT MAX(CAST(InventoryItemLotNumber as int)) FROM CEXT_InventoryItemLot il INNER JOIN CEXT_InventoryItem it
1455--  ON it.InventoryItemId = il.InventoryItemId
1456--  where StoreId=@TargetStoreId)
1457
1458--select 
1459--blkIt.InventoryItemId as InventoryItemId,
1460--30 as statusid,
1461--	CAST(@LotNumber + ROW_NUMBER() over (order by (select NULL)) as nvarchar(50)) as InventoryItemLotNumber, -- (Auto-Incremented)
1462--	CAST(i.Quantity as decimal(12,3)) as Quantity,
1463--	CAST(i.COST as decimal(12,3)) as Cost,
1464--	DATEDrop Column(HOUR,@UtcTime,iif(i.DateIn IS NULL,getdate(),i.DateIn)) as CreatedDate,
1465--	DATEDrop Column(HOUR,@UtcTime,iif(i.DateIn IS NULL,getdate(),i.DateIn)) as DateIn,
1466--	--DATEDrop Column(HOUR,@UtcTime,getdate()) as CreatedDate,
1467--	scrIt.CreatedBy as CreatedBy, -- Drop Column in package
1468--	scrIt.InventoryItemId as SourceInventoryItemId,
1469--	scrIt.InventoryItemId as SourceInventoryItemIdVersion
1470
1471-- from EXT_inv i
1472--inner join EXT_items it on it.INVNUM = i.scrapnum and i.STATUS='J'
1473--inner join CEXT_InventoryItem blkIt on blkIt.SourceId = i.items_id
1474--inner join CEXT_InventoryItem scrIt on scrIt.SourceId = it.Items_ID 
1475--where it.Sto_Pk=@SourceStoreID
1476
1477--END
1478--GO
1479/****** Object:  StoredProcedure [dbo].[InventoryPicturesExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
1480--SET ANSI_NULLS ON
1481--GO
1482--SET QUOTED_IDENTIFIER ON
1483--GO
1484
1485---- =============================================
1486---- Author:		Syed Mohsin
1487---- Create date: <8/23/2016>
1488---- Description:	<Extracts Stone Info for Inventory Items>
1489---- Sample Call: EXEC [dbo].[InventoryPicturesExtract] 2 , 133
1490---- =============================================
1491
1492--CREATE Proc [dbo].[InventoryPicturesExtract]
1493--@SourceStoreId int, @TargetStoreId int
1494--as
1495--BEGIN
1496
1497--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
1498
1499--SELECT
1500--InventoryItemId, 
1501--PictureTakenDate,
1502--ObjectImage,
1503--PictureSourceId
1504-- FROM
1505--(
1506--SELECT 
1507--	it.InventoryItemId as InventoryItemId,
1508--	itemPic_Img,
1509--	CAST(CAST(itemPic_img2 as varchar(8000)) as image) as itemPic_img2,
1510--	itemPic_Img_Date as PictureTakenDate,
1511--	ps.PictureSourceId as PictureSourceId
1512--FROM PEXT_itemPics ip
1513--INNER JOIN EXT_items i
1514--ON ip.itemPic_PK = i.itemPic_FK AND ip.sto_PK = i.Sto_Pk
1515
1516--INNER JOIN CEXT_InventoryItem it
1517--ON it.SourceId = i.Items_ID AND it.StoreId = @TargetStoreId
1518
1519--INNER JOIN CEXT_PictureSource ps
1520--ON ps.PictureSourceName = iif(i.PWN_id IS NULL, 'Item Picture','Inventory')
1521--WHERE ip.sto_PK = @SourceStoreId
1522--) a
1523--UNPIVOT(
1524--ObjectImage for ObjectValue IN (itemPic_Img,itemPic_img2)
1525--)unpiv
1526
1527--UNION ALL
1528
1529--select 
1530--	it.InventoryItemId as InventoryItemId,
1531--	ip.IMP_Img_Date as PictureTakenDate,
1532--	ip.IMP_Img as itemPic_Img,
1533--	ps.PictureSourceId as PictureSourceId
1534	
1535--from  PEXT_ItemMorePics ip
1536
1537--INNER JOIN EXT_items i
1538--ON i.Items_ID = ip.ITEMS_id AND i.Sto_Pk = @SourceStoreId
1539
1540--INNER JOIN CEXTInventoryItem it
1541--ON it.SourceId = ip.ITEMS_id AND it.StoreId = @TargetStoreId
1542
1543--INNER JOIN CEXT_PictureSource ps
1544--ON ps.PictureSourceName = iif(i.PWN_id IS NULL, 'Item Picture','Inventory')
1545
1546--END
1547--GO
1548/****** Object:  StoredProcedure [dbo].[InventoryStoneExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
1549--SET ANSI_NULLS ON
1550--GO
1551--SET QUOTED_IDENTIFIER ON
1552--GO
1553
1554---- =============================================
1555---- Author:		Syed Mohsin
1556---- Create date: <8/23/2016>
1557---- Description:	<Extracts Stone Info for Inventory Items>
1558---- Sample Call: EXEC [dbo].[InventoryStoneExtract] 8 , 182
1559---- =============================================
1560
1561--CREATE Proc [dbo].[InventoryStoneExtract]
1562--@SourceStoreId int, @TargetStoreId int
1563--as
1564--BEGIN
1565
1566--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
1567--DECLARE @StoneType int =(
1568--select LookupTypeId from CEXT_LookupType where LookupTypeName='Stone Info Codes'
1569--)
1570--DECLARE @StType int =(
1571--select LookupTypeId from CEXT_LookupType where LookupTypeName='Type' and ParentLookupTypeId=@StoneType 
1572--)
1573
1574--DECLARE @StShape int =(
1575--select LookupTypeId from CEXT_LookupType where LookupTypeName='Shape' and ParentLookupTypeId=@StoneType 
1576--)
1577
1578--DECLARE @StColor int =(
1579--select LookupTypeId from CEXT_LookupType where LookupTypeName='Color' and ParentLookupTypeId=@StoneType 
1580--)
1581
1582--DECLARE @StClarity int =(
1583--select LookupTypeId from CEXT_LookupType where LookupTypeName='Clarity' and ParentLookupTypeId=@StoneType 
1584--)
1585
1586--SELECT
1587-- 	Inv.InventoryItemId as InventoryItemId ,
1588--	CAST(NUMSTONE as int) as Quantity,
1589--	lvtyp.LookupValueId as StoneTypeId ,
1590--	lvshape.LookupValueId as StoneShapeId ,
1591--	CAST(CARAT as decimal(4,2)) as Carat,
1592--	lvcol.LookupValueId as StoneColorId ,
1593--	CAST(s.[WEIGHT] as decimal(4,2)) as [Weight],
1594--	CAST([LENGTH] as decimal(4,2)) as [Length],
1595--	CAST(WIDTH as decimal(4,2)) as [Width],
1596--	lvtran.LookupValueId as StoneClarityId ,
1597--	0 as IsDeleted,
1598--	getdate() as CreatedDate,
1599--	1 as CreatedBy,
1600--	getdate() as UpdatedDate,
1601--	u.UserId as UpdatedBy,
1602--	STO_id as SourceId
1603
1604--FROM EXT_stones s
1605
1606--INNER JOIN EXT_Detail_J j
1607--ON s.JDT_FK = j.JDT_PK AND j.Sto_FK = s.Sto_FK 
1608
1609----Join with items table to get item id
1610--INNER JOIN EXT_items i
1611--ON i.ITEMS_PK = j.Items_FK AND i.Sto_Pk = j.Sto_FK
1612
1613--INNER JOIN CEXT_InventoryItem inv
1614--ON inv.SourceId = i.Items_ID AND inv.StoreId = @TargetStoreId
1615
1616----Joins with LookupValue
1617--LEFT JOIN CEXT_LookupValue lvtyp
1618--ON lvtyp.SourceId = s.TYPSTONEFK and lvtyp.LookupTypeId=@StType
1619
1620--LEFT JOIN CEXT_LookupValue lvshape
1621--ON lvshape.SourceId = s.SHAPE_FK and lvtyp.LookupTypeId=@StShape
1622
1623--LEFT JOIN CEXT_LookupValue lvcol
1624--ON lvcol.SourceId = s.COLOR_FK and lvtyp.LookupTypeId=@StColor
1625
1626--LEFT JOIN CEXT_LookupValue lvtran
1627--ON lvtran.SourceId = s.TRANSLUCFK and lvtyp.LookupTypeId=@StClarity
1628
1629----Join target user table to get UserId for UpdateBy column
1630--LEFT JOIN CEXT_User u
1631--ON u.SourceId = s.LastUpdatedUSR_ID
1632
1633--END
1634--GO
1635/****** Object:  StoredProcedure [dbo].[ItemTypeBrandsExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
1636--SET ANSI_NULLS ON
1637--GO
1638--SET QUOTED_IDENTIFIER ON
1639--GO
1640
1641---- =============================================
1642---- Author:		Nadeem Akhtar
1643---- Create date: <8/12/2016>
1644---- Description:	<Extracts data for Brand Items>
1645---- Sample Call: EXEC [dbo].[ItemTypeBrandsExtract] 1 , 1831
1646
1647---- Updated By: Syed Mohsin
1648---- Update date: <8/16/2016>
1649---- Comments: Corrected mapping for columns and Drop Columned join conditions to meet the design logic.
1650----			 Drop Columned source and target store id as parameter.
1651
1652---- Updated By: Syed Mohsin
1653---- Update date: <8/25/2016>
1654---- Comments: Updated join from inner to left with User table.
1655---- =============================================
1656
1657--CREATE Proc [dbo].[ItemTypeBrandsExtract]
1658--@SourceStoreId int, @TargetStoreId int
1659
1660--AS
1661--BEGIN
1662
1663--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
1664
1665--	  DECLARE @lookupTypeID int
1666--	  SET @lookupTypeID = (SELECT LookupTypeId from CEXT_LookupType  
1667--							where LookupTypeName='Brand')
1668--	SELECT * FROM (
1669--	  SELECT 
1670--		  lv.LookupValueId,
1671--		  it.ItemTypeId,
1672--		  getdate() as CreatedDate,
1673--		  1 as CreatedBy, --Drop Column in package
1674--		  getdate() as UpdatedDate,
1675--		  u.UserId as UpdatedBy,
1676--		  itb.ItemTypeBrandId,
1677--		  ROW_NUMBER() over(partition by lv1_parent,LTRIM(RTRIM(l5.DESCRIPT)) order by lv5_PK) num1 
1678--	  from EXT_Level5 l5 
1679  
1680--	  INNER JOIN CEXT_LookupValue lv ON
1681--	   RTRIM(LTRIM(l5.DESCRIPT)) = lv.Value
1682--	   AND lv.LookupTypeId = @lookupTypeID 
1683--	   --AND lv.StoreId = @TargetStoreId
1684--	   AND l5.sto_pk = @SourceStoreId
1685  
1686--	  INNER JOIN EXT_Level1 l1 ON
1687--	  l1.sto_pk = @SourceStoreId
1688--	  AND l1.lv1_pk = l5.LV1_PARENT
1689
1690--	  INNER JOIN CEXT_ItemType it ON 
1691--	  --l5.LV1_PARENT = it.SourceId AND
1692--	  RTRIM(LTRIM(l1.DESCRIPT)) = it.ItemTypeName 
1693--	  AND it.ParentItemTypeId IS NULL
1694--	  --AND l5.sto_pk = @SourceStoreId
1695
1696--	  LEFT JOIN CEXT_ItemTypeBrand itb ON 
1697--	  itb.ItemTypeId = it.ItemTypeId 
1698--	  AND itb.BrandId = lv.LookupValueId
1699
1700--	  left JOIN CEXT_User u ON
1701--	  l5.LastUpdatedUSR_ID = u.SourceId 
1702--	  AND l5.sto_pk = @SourceStoreId
1703--	  AND u.StoreId = @TargetStoreId
1704
1705--	  WHERE itb.ItemTypeBrandId IS NULL
1706--	  )q where q.num1=1
1707--END
1708--GO
1709/****** Object:  StoredProcedure [dbo].[ItemTypeLevel1RecordsToInsert]    Script Date: 6/13/2018 9:39:35 AM ******/
1710--SET ANSI_NULLS ON
1711--GO
1712--SET QUOTED_IDENTIFIER ON
1713--GO
1714
1715---- =============================================
1716---- Author:		Nadeem Akhtar
1717---- Create date: <8/11/2016>
1718---- Description:	<Extracts data for Level1 Lookup values>
1719---- Sample Call: EXEC [dbo].[ItemTypeLevel1RecordsToInsert] 8
1720
1721---- Updated By: Syed Mohsin
1722---- Update date: <8/12/2016>
1723---- Comments: Corrected mapping for columns and Drop Columned join conditions to meet the design logic.
1724----			 Drop Columned Storeid parameter for Source StoreId
1725
1726---- Updated By: Syed Mohsin
1727---- Update date: <8/25/2016>
1728---- Comments: Updated join from inner to left with User table.
1729
1730---- Updated By: Syed Mohsin
1731---- Update date: <9/22/2016>
1732---- Comments: Drop Columned check to remove empty strings from extract.
1733
1734---- Updated By: Syed Mohsin
1735---- Update date: <7/20/2017>
1736---- Comments: Drop Columned check if item type does not have a levelN but exists in items table.
1737---- =============================================
1738
1739--CREATE proc [dbo].[ItemTypeLevel1RecordsToInsert]
1740--@StoreId int
1741--AS
1742
1743--BEGIN
1744--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
1745--DECLARE @FireArm int, @Jewelry int, @Other int
1746
1747--	select @FireArm=ItemCategoryId from CEXT_ItemType t 
1748--			where value = 'Firearm'
1749
1750--	select @Jewelry=ItemCategoryId from CEXT_ItemType t 		
1751--			where value = 'Jewelry'
1752	
1753--		select @Other=ItemCategoryId from CEXT_ItemType t 
1754--			where value = 'Other'
1755
1756
1757--Select 
1758--	Null as ParentItemTypeId,
1759--	ItemCategoryID=
1760--		case WHEN s.Descript LIKE '%Firearm%' THEN 
1761--			@FireArm
1762--		 WHEN s.Descript LIKE '%Jewelry %' THEN 
1763--			@Jewelry
1764--		ELSE
1765--			@Other
1766--		end,
1767--	ItemTypeName= cast(RTRIM(LTRIM(s.Descript)) as nvarchar(100)),
1768--	Value= cast(RTRIM(LTRIM(s.Descript)) as nvarchar(50)),
1769--	GetDate() as CreatedDate,
1770--	1 as CreatedBy, --Drop Column in package
1771--	GetDate() as UpdatedDate,
1772--	u.UserId as UpdatedBy,
1773--	0 as IsDeleted,
1774--	Lv1_pk as SourceID,
1775--	1 as Level,
1776--	Ln.Post2GunLog as IsPostToGunLog,
1777--	Ln.HandGun as IsHandGun,
1778--	Ln.HoldGunDays as HoldDays,
1779--	Ln.Min_Age as MinimumAge
1780
1781--	from EXT_Level1 s
1782	
1783--	left outer join CEXT_ItemType t
1784--		on RTRIM(LTRIM(s.Descript)) = t.ItemTypeName
1785--			and t.ParentItemTypeId is null
1786--	left outer join EXT_Level_N LN
1787--		on s.Lv1_PK= Ln.Level_FK
1788--	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
1789--	left join CEXT_User u ON s.LastUpdatedUSR_ID = u.SourceId 
1790--	where RTRIM(LTRIM(s.Descript)) != '' AND t.ItemTypeName is null AND s.sto_pk = @StoreId 
1791--	AND (LN.LVN_id IS NOT NULL OR q.lv1_ID IS NOT NULL) --Also migrate if missing in LevelN but used in inventory
1792
1793--	END
1794--GO
1795/****** Object:  StoredProcedure [dbo].[ItemTypeLevel1RepeatedRecordsToInsert]    Script Date: 6/13/2018 9:39:35 AM ******/
1796--SET ANSI_NULLS ON
1797--GO
1798--SET QUOTED_IDENTIFIER ON
1799--GO
1800
1801---- =============================================
1802---- Author:		Syed Mohsin
1803---- Create date: <8/04/2017>
1804---- Description:	<Extracts data for Level1 Repeating ItemTypes>
1805---- Sample Call: EXEC [dbo].[ItemTypeLevel1RepeatedRecordsToInsert] 1
1806
1807---- =============================================
1808
1809--CREATE proc [dbo].[ItemTypeLevel1RepeatedRecordsToInsert]
1810--@StoreId int
1811--AS
1812
1813--BEGIN
1814--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
1815--DECLARE @FireArm int, @Jewelry int, @Other int
1816
1817--	select @FireArm=ItemCategoryId from CEXT_ItemType t 
1818--			where value = 'Firearm'
1819
1820--	select @Jewelry=ItemCategoryId from CEXT_ItemType t 		
1821--			where value = 'Jewelry'
1822	
1823--		select @Other=ItemCategoryId from CEXT_ItemType t 
1824--			where value = 'Other'
1825
1826
1827--Select 
1828--	ROW_NUMBER() over (partition by RTRIM(LTRIM(s.Descript)) order by Lv1_pk) as NUM,
1829--	Null as ParentItemTypeId,
1830--	ItemCategoryID=
1831--		case WHEN s.Descript LIKE '%Firearm%' THEN 
1832--			@FireArm
1833--		 WHEN s.Descript LIKE '%Jewelry %' THEN 
1834--			@Jewelry
1835--		ELSE
1836--			@Other
1837--		end,
1838--	ItemTypeName= cast(RTRIM(LTRIM(s.Descript)) as nvarchar(100)),
1839--	Value= cast(RTRIM(LTRIM(s.Descript)) as nvarchar(50)),
1840--	GetDate() as CreatedDate,
1841--	1 as CreatedBy, --Drop Column in package
1842--	GetDate() as UpdatedDate,
1843--	u.UserId as UpdatedBy,
1844--	0 as IsDeleted,
1845--	Lv1_pk as SourceID,
1846--	1 as Level,
1847--	Ln.Post2GunLog as IsPostToGunLog,
1848--	Ln.HandGun as IsHandGun,
1849--	Ln.HoldGunDays as HoldDays,
1850--	Ln.Min_Age as MinimumAge
1851
1852--	from EXT_Level1 s
1853
1854--	INNER join CEXT_ItemType t
1855--		on RTRIM(LTRIM(s.Descript)) = t.ItemTypeName
1856--			and t.ParentItemTypeId is null and t.SourceId IS NOT NULL and t.SourceId != s.lv1_pk
1857--	left outer join EXT_Level_N LN
1858--		on s.Lv1_PK= Ln.Level_FK
1859--	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
1860--	left join CEXT_User u ON s.LastUpdatedUSR_ID = u.SourceId 
1861--	where RTRIM(LTRIM(s.Descript)) != ''  AND s.sto_pk = @StoreId 
1862--	AND (LN.LVN_id IS NOT NULL OR q.lv1_ID IS NOT NULL) --Also migrate if missing in LevelN but used in inventory
1863
1864--	END
1865--GO
1866/****** Object:  StoredProcedure [dbo].[ItemTypeLevel2RecordsToInsert]    Script Date: 6/13/2018 9:39:35 AM ******/
1867--SET ANSI_NULLS ON
1868--GO
1869--SET QUOTED_IDENTIFIER ON
1870--GO
1871
1872---- =============================================
1873---- Author:		Nadeem Akhtar
1874---- Create date: <8/11/2016>
1875---- Description:	<Extracts data for Level2 Lookup values>
1876---- Sample Call: EXEC [dbo].[ItemTypeLevel2RecordsToInsert] 8
1877
1878---- Updated By: Syed Mohsin
1879---- Update date: <8/12/2016>
1880---- Comments: Corrected mapping for columns and Drop Columned join conditions to meet the design logic.
1881----			 Drop Columned Storeid parameter for Source StoreId
1882
1883---- Updated By: Syed Mohsin
1884---- Update date: <8/25/2016>
1885---- Comments: Updated join from inner to left with User table.
1886
1887---- Updated By: Syed Mohsin
1888---- Update date: <9/22/2016>
1889---- Comments: Drop Columned check to remove empty string from extract.
1890
1891---- Updated By: Syed Mohsin
1892---- Update date: <7/20/2017>
1893---- Comments: Drop Columned check if item type does not have a levelN but exists in items table.
1894---- =============================================
1895
1896--CREATE proc [dbo].[ItemTypeLevel2RecordsToInsert]
1897--@StoreId int
1898--as
1899
1900--BEGIN
1901--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
1902
1903--	Select 
1904--		p.ItemTypeId as ParentItemTypeId,
1905--		p.ItemCategoryId as ItemCategoryId,
1906--		ItemTypeName= cast(RTRIM(LTRIM(s.Descript)) as nvarchar(100)),
1907--		Value= cast(RTRIM(LTRIM(s.Descript)) as nvarchar(50)),
1908--		GetDate() as CreatedDate,
1909--		1 as CreatedBy, --Drop Column in package
1910--		GetDate() as UpdatedDate,
1911--		u.UserId as UpdatedBy,
1912--		0 as IsDeleted,
1913--		Lv2_pk as SourceID
1914--		--2 as [Level]
1915
1916--		from EXT_Level2 s 
1917--			inner join CEXT_ItemType p
1918--				on s.lv1_Parent = p.sourceID
1919--					--and p.[level]=1
1920--			left outer join CEXT_ItemType t
1921--				on RTRIM(LTRIM(s.Descript)) = t.ItemTypeName and t.ParentItemTypeId = p.ItemTypeId
1922--					--and t.[level]=2
1923--			left outer join EXT_Level_N n
1924--				on n.Level_FK = s.lv2_pk
1925--			left outer join (SELECT DISTINCT lv2_ID from EXT_items where Sto_Pk=@StoreId)q 
1926--				on q.lv2_ID = s.lv2_ID --Drop Columned for missing itemtypes in LevelN
1927--			left join CEXT_User u
1928--				on s.LastUpdatedUSR_ID = u.SourceId
1929--		where RTRIM(LTRIM(s.Descript)) != '' AND t.ItemTypeName is null AND s.sto_pk = @StoreId
1930--				AND (n.LVN_id IS NOT NULL OR q.lv2_ID IS NOT NULL) --Also migrate if missing in LevelN but used in inventory
1931--END
1932--GO
1933/****** Object:  StoredProcedure [dbo].[ItemTypeLevel2RepeatedRecordsToInsert]    Script Date: 6/13/2018 9:39:35 AM ******/
1934--SET ANSI_NULLS ON
1935--GO
1936--SET QUOTED_IDENTIFIER ON
1937--GO
1938
1939---- =============================================
1940---- Author:		Syed Mohsin
1941---- Create date: <8/04/2017>
1942---- Description:	<Extracts data for Level2 repeating itemtypes>
1943---- Sample Call: EXEC [dbo].[ItemTypeLevel2RepeatedRecordsToInsert] 1
1944---- =============================================
1945
1946--CREATE proc [dbo].[ItemTypeLevel2RepeatedRecordsToInsert]
1947--@StoreId int
1948--as
1949
1950--BEGIN
1951--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
1952
1953--	Select
1954--	ROW_NUMBER() over (partition by RTRIM(LTRIM(s.Descript)) order by Lv2_pk) as Num, 
1955--		p.ItemTypeId as ParentItemTypeId,
1956--		p.ItemCategoryId as ItemCategoryId,
1957--		ItemTypeName= cast(RTRIM(LTRIM(s.Descript)) as nvarchar(100)),
1958--		Value= cast(RTRIM(LTRIM(s.Descript)) as nvarchar(50)),
1959--		GetDate() as CreatedDate,
1960--		1 as CreatedBy, --Drop Column in package
1961--		GetDate() as UpdatedDate,
1962--		u.UserId as UpdatedBy,
1963--		0 as IsDeleted,
1964--		Lv2_pk as SourceID
1965--		--2 as [Level]
1966
1967--		from EXT_Level2 s 
1968--			inner join CEXT_ItemType p
1969--				on s.lv1_Parent = p.sourceID
1970--					--and p.[level]=1
1971--			INNER join CEXT_ItemType t
1972--				on RTRIM(LTRIM(s.Descript)) = t.ItemTypeName and t.ParentItemTypeId = p.ItemTypeId
1973--				and t.SourceId IS NOT NULL and t.SourceId != s.lv2_pk
1974--					--and t.[level]=2
1975--			left outer join EXT_Level_N n
1976--				on n.Level_FK = s.lv2_pk
1977--			left outer join (SELECT DISTINCT lv2_ID from EXT_items where Sto_Pk=@StoreId)q 
1978--				on q.lv2_ID = s.lv2_ID --Drop Columned for missing itemtypes in LevelN
1979--			left join CEXT_User u
1980--				on s.LastUpdatedUSR_ID = u.SourceId
1981--		where RTRIM(LTRIM(s.Descript)) != ''  AND s.sto_pk = @StoreId
1982--				AND (n.LVN_id IS NOT NULL OR q.lv2_ID IS NOT NULL) --Also migrate if missing in LevelN but used in inventory
1983--END
1984--GO
1985/****** Object:  StoredProcedure [dbo].[ItemTypeLevel3RecordsToInsert]    Script Date: 6/13/2018 9:39:35 AM ******/
1986--SET ANSI_NULLS ON
1987--GO
1988--SET QUOTED_IDENTIFIER ON
1989--GO
1990
1991---- =============================================
1992---- Author:		Nadeem Akhtar
1993---- Create date: <8/11/2016>
1994---- Description:	<Extracts data for Level3 Lookup values>
1995---- Sample Call: EXEC [dbo].[ItemTypeLevel3RecordsToInsert] 1
1996
1997---- Updated By: Syed Mohsin
1998---- Update date: <8/12/2016>
1999---- Comments: Corrected mapping for columns and Drop Columned join conditions to meet the design logic.
2000----			 Drop Columned Storeid parameter for Source StoreId
2001
2002---- Updated By: Syed Mohsin
2003---- Update date: <8/25/2016>
2004---- Comments: Updated join from inner to left with User table.
2005
2006---- Updated By: Syed Mohsin
2007---- Update date: <9/22/2016>
2008---- Comments: Drop Columned check to remove empty string from extract.
2009---- =============================================
2010
2011--CREATE proc [dbo].[ItemTypeLevel3RecordsToInsert]
2012--@StoreId int
2013
2014--AS
2015
2016--BEGIN
2017--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
2018
2019--		Select 
2020--			p.ItemTypeId as ParentItemTypeId,
2021--			p.ItemCategoryId as ItemCategoryId,
2022--			ItemTypeName= cast(RTRIM(LTRIM(s.Descript)) as nvarchar(100)),
2023--			Value= cast(RTRIM(LTRIM(s.Descript)) as nvarchar(50)),
2024--			GetDate() as CreatedDate,
2025--			1 as CreatedBy, --Drop Column in package
2026--			GetDate() as UpdatedDate,
2027--			u.UserId as UpdatedBy,
2028--			0 as IsDeleted,
2029--			Lv3_pk as SourceID
2030
2031
2032--			from EXT_Level3 s 
2033--						--and t.level=3
2034--				inner join CEXT_ItemType p
2035--					on s.lv2_Parent = p.sourceID
2036--						--and p.level=2
2037--				left outer join CEXT_ItemType t
2038--					on RTRIM(LTRIM(s.Descript)) = t.ItemTypeName and t.ParentItemTypeId = p.ItemTypeId
2039--				left outer JOIN EXT_Level_N n
2040--					on n.Level_FK = s.lv3_pk
2041--				left outer join (SELECT DISTINCT lv3_ID from EXT_items where Sto_Pk=@StoreId)q 
2042--					on q.lv3_ID = s.lv3_ID --Drop Columned for missing itemtypes in LevelN
2043--				left join CEXT_User u
2044--					on s.LastUpdatedUSR_ID = u.SourceId
2045--			where RTRIM(LTRIM(s.Descript)) != '' AND t.ItemTypeName is null AND s.sto_pk = @StoreId
2046--			AND (n.LVN_id IS NOT NULL OR q.lv3_ID IS NOT NULL) --Also migrate if missing in LevelN but used in inventory
2047		
2048--END
2049--GO
2050/****** Object:  StoredProcedure [dbo].[ItemTypeLevel3RepeatedRecordsToInsert]    Script Date: 6/13/2018 9:39:35 AM ******/
2051--SET ANSI_NULLS ON
2052--GO
2053--SET QUOTED_IDENTIFIER ON
2054--GO
2055
2056---- =============================================
2057---- Author:		Syed Mohsin
2058---- Create date: <8/04/2017>
2059---- Description:	<Extracts data for Level3 Repeating ItemTypes>
2060---- Sample Call: EXEC [dbo].[ItemTypeLevel3RepeatedRecordsToInsert] 1
2061---- =============================================
2062
2063--CREATE proc [dbo].[ItemTypeLevel3RepeatedRecordsToInsert]
2064--@StoreId int
2065
2066--AS
2067
2068--BEGIN
2069--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
2070
2071--		Select 
2072--		ROW_NUMBER() over (partition by RTRIM(LTRIM(s.Descript)) order by Lv3_pk) as Num, 
2073--			p.ItemTypeId as ParentItemTypeId,
2074--			p.ItemCategoryId as ItemCategoryId,
2075--			ItemTypeName= cast(RTRIM(LTRIM(s.Descript)) as nvarchar(100)),
2076--			Value= cast(RTRIM(LTRIM(s.Descript)) as nvarchar(50)),
2077--			GetDate() as CreatedDate,
2078--			1 as CreatedBy, --Drop Column in package
2079--			GetDate() as UpdatedDate,
2080--			u.UserId as UpdatedBy,
2081--			0 as IsDeleted,
2082--			Lv3_pk as SourceID
2083
2084
2085--			from Level3 s 
2086--						--and t.level=3
2087--				inner join CEXT_ItemType p
2088--					on s.lv2_Parent = p.sourceID
2089--						--and p.level=2
2090--				INNER join CEXT_ItemType t
2091--					on RTRIM(LTRIM(s.Descript)) = t.ItemTypeName and t.ParentItemTypeId = p.ItemTypeId
2092--					and t.SourceId IS NOT NULL and t.SourceId != s.lv3_pk
2093--				left outer JOIN EXT_Level_N n
2094--					on n.Level_FK = s.lv3_pk
2095--				left outer join (SELECT DISTINCT lv3_ID from EXT_items where Sto_Pk=@StoreId)q 
2096--					on q.lv3_ID = s.lv3_ID --Drop Columned for missing itemtypes in LevelN
2097--				left join CEXT_User u
2098--					on s.LastUpdatedUSR_ID = u.SourceId
2099--			where RTRIM(LTRIM(s.Descript)) != ''  AND s.sto_pk = @StoreId
2100--			AND (n.LVN_id IS NOT NULL OR q.lv3_ID IS NOT NULL) --Also migrate if missing in LevelN but used in inventory
2101		
2102--END
2103--GO
2104/****** Object:  StoredProcedure [dbo].[ItemTypeLevel4RecordsToInsert]    Script Date: 6/13/2018 9:39:35 AM ******/
2105--SET ANSI_NULLS ON
2106--GO
2107--SET QUOTED_IDENTIFIER ON
2108--GO
2109
2110---- =============================================
2111---- Author:		Nadeem Akhtar
2112---- Create date: <8/11/2016>
2113---- Description:	<Extracts data for Level4 Lookup values>
2114---- Sample Call: EXEC [dbo].[ItemTypeLevel4RecordsToInsert] 8
2115
2116---- Updated By: Syed Mohsin
2117---- Update date: <8/12/2016>
2118---- Comments: Corrected mapping for columns and Drop Columned join conditions to meet the design logic.
2119----			 Drop Columned Storeid parameter for Source StoreId
2120
2121---- Updated By: Syed Mohsin
2122---- Update date: <8/25/2016>
2123---- Comments: Updated join from inner to left with User table.
2124
2125---- Updated By: Syed Mohsin
2126---- Update date: <9/22/2016>
2127---- Comments: Drop Columned check to remove empty string from extract.
2128---- =============================================
2129
2130--CREATE proc [dbo].[ItemTypeLevel4RecordsToInsert]
2131--@StoreId int
2132--AS
2133
2134--BEGIN
2135--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
2136
2137--Select 
2138--		p.ItemTypeId as ParentItemTypeId,
2139--		p.ItemCategoryId as ItemCategoryId,
2140--		ItemTypeName= cast(RTRIM(LTRIM(s.Descript)) as nvarchar(100)),
2141--		Value= cast(RTRIM(LTRIM(s.Descript)) as nvarchar(50)),
2142--		GetDate() as CreatedDate,
2143--		1 as CreatedBy, --Drop Column in package
2144--		GetDate() as UpdatedDate,
2145--		u.UserId as UpdatedBy,
2146--		0 as IsDeleted,
2147--		Lv4_pk as SourceID
2148
2149
2150--		from EXT_Level4 s 
2151
2152--					--and t.level=4
2153--			inner join CEXT_ItemType p
2154--				on s.lv3_Parent = p.sourceID
2155--					--and p.level=3 
2156--			left outer join CEXT_ItemType t
2157--				on RTRIM(LTRIM(s.Descript)) = t.ItemTypeName and t.ParentItemTypeId = p.ItemTypeId
2158--			left outer JOIN EXT_Level_N n
2159--				ON n.Level_FK = s.lv4_PK
2160--			left outer join (SELECT DISTINCT lv4_ID from EXT_items where Sto_Pk=@StoreId )q 
2161--					on q.lv4_ID = s.lv4_ID --Drop Columned for missing itemtypes in LevelN
2162--			left join CEXT_User u
2163--				on s.LastUpdatedUSR_ID = u.SourceId
2164--		where RTRIM(LTRIM(s.Descript)) != '' AND t.ItemTypeName is null AND s.sto_pk = @StoreId
2165--		AND (n.LVN_id IS NOT NULL OR q.lv4_ID IS NOT NULL) --Also migrate if missing in LevelN but used in inventory
2166		
2167--	END
2168--GO
2169/****** Object:  StoredProcedure [dbo].[ItemTypeLevel4RepeatedRecordsToInsert]    Script Date: 6/13/2018 9:39:35 AM ******/
2170--SET ANSI_NULLS ON
2171--GO
2172--SET QUOTED_IDENTIFIER ON
2173--GO
2174
2175---- =============================================
2176---- Author:		Syed Mohsin
2177---- Create date: <8/04/2017>
2178---- Description:	<Extracts data for Level4 repeated itemtypes>
2179---- Sample Call: EXEC [dbo].[ItemTypeLevel4RepeatedRecordsToInsert] 1
2180---- =============================================
2181
2182--CREATE proc [dbo].[ItemTypeLevel4RepeatedRecordsToInsert]
2183--@StoreId int
2184--AS
2185
2186--BEGIN
2187--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
2188
2189--Select 
2190--ROW_NUMBER() over (partition by RTRIM(LTRIM(s.Descript)) order by Lv4_pk) as Num,
2191--		p.ItemTypeId as ParentItemTypeId,
2192--		p.ItemCategoryId as ItemCategoryId,
2193--		ItemTypeName= cast(RTRIM(LTRIM(s.Descript)) as nvarchar(100)),
2194--		Value= cast(RTRIM(LTRIM(s.Descript)) as nvarchar(50)),
2195--		GetDate() as CreatedDate,
2196--		1 as CreatedBy, --Drop Column in package
2197--		GetDate() as UpdatedDate,
2198--		u.UserId as UpdatedBy,
2199--		0 as IsDeleted,
2200--		Lv4_pk as SourceID
2201
2202
2203--		from EXT_Level4 s 
2204
2205--					--and t.level=4
2206--			inner join CEXT_ItemType p
2207--				on s.lv3_Parent = p.sourceID
2208--					--and p.level=3 
2209--			INNER JOIN  CEXT_ItemType t
2210--				on RTRIM(LTRIM(s.Descript)) = t.ItemTypeName and t.ParentItemTypeId = p.ItemTypeId
2211--				and t.SourceId IS NOT NULL and t.SourceId != s.lv4_PK
2212--			left outer JOIN EXT_Level_N n
2213--				ON n.Level_FK = s.lv4_PK
2214--			left outer join (SELECT DISTINCT lv4_ID from EXT_items where Sto_Pk=@StoreId )q 
2215--					on q.lv4_ID = s.lv4_ID --Drop Columned for missing itemtypes in LevelN
2216--			left join CEXT_User u
2217--				on s.LastUpdatedUSR_ID = u.SourceId
2218--		where RTRIM(LTRIM(s.Descript)) != '' AND s.sto_pk = @StoreId
2219--		AND (n.LVN_id IS NOT NULL OR q.lv4_ID IS NOT NULL) --Also migrate if missing in LevelN but used in inventory
2220		
2221--	END
2222--GO
2223/****** Object:  StoredProcedure [dbo].[LookupCNExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
2224--SET ANSI_NULLS ON
2225--GO
2226--SET QUOTED_IDENTIFIER ON
2227--GO
2228
2229---- =============================================
2230---- Author:		Nadeem Akhtar
2231---- Create date: <8/11/2016>
2232---- Description:	<Extracts data for LevelN Lookup values>
2233---- Sample Call: EXEC [dbo].[LookupCNExtract] 8 , 2
2234
2235---- Updated By: Syed Mohsin
2236---- Update date: <8/12/2016>
2237---- Comments: Corrected mapping for columns and Drop Columned join conditions to meet the design logic.
2238----			 Drop Columned Storeid parameter for Source StoreId
2239
2240---- Updated By: Syed Mohsin
2241---- Update date: <9/7/2016>
2242---- Comments: All rows are insert rows. Removed update from package
2243---- =============================================
2244--CREATE Proc [dbo].[LookupCNExtract]
2245--@SourceStoreId int , @TargetStoreId int
2246
2247--AS
2248--BEGIN
2249--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
2250 
2251--  SELECT lv.LookupValueId,
2252--cast(LTRIM(RTRIM(n.NCIC_Code)) as nvarchar(50)) NCIC_Code ,
2253--  cast(LTRIM(RTRIM(n.Local_Code)) as nvarchar(50)) Local_Code,
2254--  getdate() as UpdateDate,
2255--  getdate() as CreatedDate,
2256--  u.UserId UpdatedBy,
2257--  0 as IsDeleted,
2258--  @TargetStoreId Storeid
2259-- from CEXT_LookupValue lv
2260--  INNER JOIN EXT_Lookup_C c ON lv.SourceId = c.lc_pk
2261--  INNER JOIN EXT_Lookup_N n ON n.lc_FK = c.lc_pk AND n.sto_pk = @SourceStoreId
2262--  LEFT JOIN CEXT_User u ON u.SourceId = n.LastUpdatedUSR_ID
2263
2264--END
2265--GO
2266/****** Object:  StoredProcedure [dbo].[OverrideExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
2267--SET ANSI_NULLS ON
2268--GO
2269--SET QUOTED_IDENTIFIER ON
2270--GO
2271
2272---- =============================================
2273---- Author:		<Farrukh Ijaz>
2274---- Create date: <09/05/2016>
2275---- Description:	<Extract all transaction overrides>
2276---- Sample Call: EXEC OverrideExtract 8
2277---- =============================================
2278--CREATE PROCEDURE [dbo].[OverrideExtract] 
2279--	-- Drop Column the parameters for the stored procedure here
2280--	@SourceStoreId as int
2281--AS
2282--BEGIN
2283--	-- SET NOCOUNT ON Drop Columned to prevent extra result sets from
2284--	-- interfering with SELECT statements.
2285--	SET NOCOUNT ON;
2286--	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
2287--    -- Insert statements for procedure here
2288--	Select o.*,
2289--		CASE WHEN (pt.TransactionId IS NOT NULL) THEN
2290--			pt.TransactionId
2291--		WHEN (st.TransactionId IS NOT NULL) THEN
2292--			st.TransactionId
2293--		END AS TransactionId,
2294--		CASE WHEN (TransactionType='P_LimO') THEN 1667
2295--			WHEN (TransactionType='S_DisO') THEN 1671
2296--			WHEN (TransactionType='S_DepO') THEN 1672
2297--			END AS OverrideTypeId,
2298--		overUsr.UserId AS ActionUserId,
2299--		Usr.UserId AS OverridenUserId
2300--	FROM(
2301--			SELECT 
2302--			CASE WHEN (Ovr_Desc='Override employee limit: Ticket#') Then 
2303--				'P_LimO'
2304--			WHEN (Ovr_Desc='Sales - Override discount: Ticket#') THEN
2305--				'S_DisO'
2306--			WHEN (Ovr_Desc='Layaway - Deposit less than recommended percentage') THEN
2307--				'S_DepO'
2308--			END as TransactionType,
2309--			Ticketnum,
2310--			CAST(Ovr_Desc as nvarchar) AS Ovr_Desc,
2311--			Ovr_UserID,
2312--			[User_ID],
2313--			Ovr_Date,
2314--			Sto_PK FROM EXT_Override
2315--			WHERE Ovr_Desc IN ('Override employee limit: Ticket#','Sales - Override discount: Ticket#','Layaway - Deposit less than recommended percentage') 
2316--			AND Sto_PK=@SourceStoreId
2317--		)o
2318--	LEFT JOIN CEXT_Transaction st ON o.Ticketnum=st.TicketNumber AND o.Sto_PK=st.StoreId AND o.TransactionType IN ('S_DisO', 'S_DepO')
2319--	LEFT JOIN CEXT_Transaction pt ON o.Ticketnum=pt.TicketNumber AND o.Sto_PK=pt.StoreId AND o.TransactionType='P_LimO'
2320--	LEFT JOIN CEXT_User overUsr ON o.Ovr_UserID = overUsr.SourceId
2321--	LEFT JOIN CEXT_User Usr ON o.[User_ID] = Usr.SourceId
2322
2323--END
2324--GO
2325/****** Object:  StoredProcedure [dbo].[PartialPaymentsExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
2326--SET ANSI_NULLS ON
2327--GO
2328--SET QUOTED_IDENTIFIER ON
2329--GO
2330
2331---- =============================================
2332---- Author:		<Syed Mohsin>
2333---- Create date: <08/01/2017>
2334---- Description:	<Extract TransactionPayments data from Acct table which are partial payments>
2335---- Sample Call: EXEC [dbo].[PartialPaymentsExtract] 1,399,5
2336---- =============================================
2337
2338--CREATE PROCEDURE [dbo].[PartialPaymentsExtract]
2339--	-- Drop Column the parameters for the stored procedure here
2340--	@SourceStoreId as int,@TargetStoreId as int , @UtcTime int
2341--AS
2342--BEGIN
2343--	-- SET NOCOUNT ON Drop Columned to prevent extra result sets from
2344--	-- interfering with SELECT statements.
2345--	SET NOCOUNT ON;
2346--	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
2347
2348--DECLARE @BatchId int
2349--SET @BatchId = (SELECT MAX(batchid) from CEXT_TransactionPayment)
2350
2351--SELECT 
2352--	[TYPE]
2353--	,'PAWN' AS PaymentTransType
2354--	,a.TICKETNUM as TicketNumber
2355--	,iif(LostTicket=0,a.AMOUNT,a.amount-LostTicket) as AMOUNT
2356--	,t.TransactionId
2357--	,1 as PaymentTypeId
2358--	,DATEDrop Column(HOUR,@UtcTime,a.DATEin) DATEin
2359--	,STATETAX+COUNTYTAX+LOCALTAX AS Tax
2360--	,DATEDrop Column(HOUR,@UtcTime,a.DATEin) AS CreatedDate
2361--	,u.UserId
2362--	,Acct_PK
2363--	,c.CustomerId
2364--	,lv1.LookupValueId AS TenderType1
2365--	,lv2.LookupValueId AS TenderType2
2366--	,TENDERAMT1
2367--	,TENDERAMT2
2368--	,Acct_PK AS SourceId
2369--	,DATEDrop Column(DAY,-p.NUMDAYS,DATEDrop Column(HOUR,@UtcTime,p.OrigChgDate)) as CHARGEDATE
2370--	, 0 as IsQueued
2371--	, @BatchId + ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as BatchId
2372--	,CAST('Partial' as NVARCHAR(500)) as VALUE
2373--	,CAST(a.RTsvc as decimal(12,3)) as ServiceFee
2374--	,CAST(a.RTint as decimal(12,3)) as InterestPercentage
2375--	,CAST(a.RTonetime as decimal(12,3)) as OneTimeFee
2376--	,CAST(a.RTper as decimal(12,3)) as FeePerPeriod
2377--	,CAST(a.Gun as decimal(12,3)) as FirearmFee
2378
2379-- FROM EXT_pawn p 
2380--inner join (
2381--select TICKETNUM  ,SUM(AMOUNT) [Total Amount] from EXT_acct a
2382--WHERE a.TYPE = 'PPP' and a.sto_pk = 1
2383--GROUP BY TICKETNUM 
2384--)q
2385--on q.TICKETNUM = p.TICKETNUM and q.[Total Amount] = p.FLOATAMT
2386
2387--inner join EXT_Acct a 
2388--on a.TICKETNUM = p.TICKETNUM and a.sto_pk = @SourceStoreId  and type='PPP'
2389
2390--inner join CEXT_transaction t 
2391--on t.TicketNumber = a.TICKETNUM and t.TransactionTypeId = 1
2392
2393--LEFT JOIN EXT_cust cst
2394--ON cst.Cus_PK = a.CUS_FK 
2395
2396--LEFT JOIN CEXT_Customer c
2397--ON c.SourceId = cst.Cus_id
2398
2399--LEFT JOIN EXT_users us
2400--on us.USR_PK = a.Usr_FK and us.USR_STORE = @SourceStoreId
2401--LEFT JOIN CEXT_User u
2402--on u.SourceId = us.USR_ID
2403
2404--LEFT JOIN EXT_Lookup_C tt1 ON a.TENDERTYP1 = tt1.lc_pk
2405--LEFT JOIN EXT_Lookup_C tt2 ON a.TENDERTYP2 = tt2.lc_pk
2406--LEFT JOIN CEXT_LookupValue lv1 ON tt1.lc_Descript = lv1.Value and tt1.lc_pk = lv1.SourceId
2407--LEFT JOIN CEXT_LookupValue lv2 ON tt2.lc_Descript = lv2.Value and tt2.lc_pk = lv2.SourceId
2408
2409--WHERE p.STORE_NO = @SourceStoreId and p.FLOATAMT > 0
2410
2411--END
2412--GO
2413/****** Object:  StoredProcedure [dbo].[PawnBuyTransactionExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
2414--SET ANSI_NULLS ON
2415--GO
2416--SET QUOTED_IDENTIFIER ON
2417--GO
2418
2419--CREATE Proc [dbo].[PawnBuyTransactionExtract]
2420--@SourceStoreId int, @TargetStoreId int, @UtcTime int
2421
2422--AS
2423
2424--BEGIN
2425--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
2426
2427--Declare @LastReminderDate datetime
2428--SET @LastReminderDate = 
2429--(select DATEDrop Column(HOUR,@UtcTime,CAST(REMINDER as datetime)) as reminder from EXT_sysinfo2 where STO_PK=@SourceStoreId)
2430
2431--select 
2432--	tr.TransactionTypeId,
2433--	@TargetStoreId as StoreId,
2434--	cst.CustomerId,
2435--	p.TICKETNUM as TicketNumber,
2436--	NUMDAYS as Duration,
2437--	CAST(SERVPERIOD as int) Period,
2438--	DATEDrop Column(HOUR,@UtcTime,CAST(DATEIN as datetime)) InDate,--UTC CONVERSION
2439--	DATEDrop Column(HOUR,@UtcTime,CAST(DATEOUT as datetime)) OutDate,--UTC CONVERSION	 
2440--	CAST(PawnAMT as decimal(16,3)) as Amount,
2441--	rt.RateTableId,
2442--	CAST(iif(TRANS = 'O',CONCAT(LTRIM(PAWNNOTE),'This was a Buy-Sell option to begin with') ,PAWNNOTE) as nvarchar(500)) as Note,
2443--	1 as IsActive,
2444--	0 as IsDeleted,
2445--	0 as IsQueued,
2446--	DATEDrop Column(HOUR,@UtcTime,CAST(p.STARTDATE as datetime)) as CreatedDate,--UTC CONVERSION
2447--	DATEDrop Column(HOUR,@UtcTime,CAST(TRANSDATE as datetime)) as UpdatedDate,--UTC CONVERSION
2448--	u2.UserId as CreatedBy,
2449--	u.UserId as UpdatedBy,
2450--	p.PWN_id as SourceId,
2451--	s.StatusId,
2452--	CAST(p.COMMENT as nvarchar(200)) as [Message],
2453--	@LastReminderDate as LastDateReminderLetterSent
2454--	--VoidDescription
2455--from EXT_pawn p
2456
2457--INNER JOIN CEXT_TransactionType tr
2458--	ON tr.TransactionTypeDesc = iif(p.TRANS='P' OR p.TRANS='O','Pawn','Buy')
2459
2460--INNER JOIN EXT_cust c
2461--	ON c.Cus_PK = p.CUS_FK
2462
2463--INNER JOIN CEXT_Customer cst
2464--	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))
2465--	and cst.BirthDate like LTRIM(RTRIM(c.CUS_BIRTHDate)))
2466
2467--LEFT JOIN CEXT_RateTable rt
2468--	ON rt.TableName = p.RateTable AND rt.StoreId = @TargetStoreId
2469
2470--LEFT JOIN CEXT_User u
2471--	ON p.LastUpdatedUSR_ID = u.SourceId
2472
2473--INNER JOIN Common.SourceTargetKeyMapping map
2474--	ON map.SourceKey = CASE WHEN p.[STATUS] = 'V' AND p.TRANS = 'P' THEN 'VP'
2475--							WHEN p.[STATUS] = 'V' AND p.TRANS = 'B' THEN 'VB'
2476--						ELSE p.[STATUS]
2477--						END
2478--	   AND map.SourceTable='TransactionCodes'
2479
2480--INNER JOIN CEXT_Status s
2481--ON s.StatusCode = map.TargetKey
2482
2483--LEFT JOIN EXT_users us 
2484--on us.USR_PK = p.usr_fk and p.STORE_NO=us.USR_STORE
2485
2486--LEFT JOIN CEXT_User u2
2487--on u2.SourceId = us.USR_ID
2488
2489--WHERE p.STORE_NO=@SourceStoreId and p.TitleLoan=0
2490
2491--END
2492--GO
2493/****** Object:  StoredProcedure [dbo].[PawnBuyTransactionItemExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
2494--SET ANSI_NULLS ON
2495--GO
2496--SET QUOTED_IDENTIFIER ON
2497--GO
2498
2499--CREATE Proc [dbo].[PawnBuyTransactionItemExtract]
2500--@SourceStoreId int, @TargetStoreId int, @UtcTime int
2501
2502--AS
2503
2504--BEGIN
2505--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
2506--select 
2507--	t.TransactionId,
2508--	st.StatusId, 
2509--	il.InventoryItemLotId,
2510--	--CAST(i.AMOUNT as decimal(16,3)) as Cost,
2511--	--CAST(i.Origonhand as decimal(16,3)) as Quantity,
2512--	CAST(LTRIM(RTRIM(i.DESCRIPT)) as nvarchar(500)) as Comment,
2513--	CAST(i.RESALEAMT as decimal(16,3)) as Resale,
2514--	it.JewelryWeightUnitId, 
2515--	t.CreatedDate as CreatedDate,
2516--	t.UpdatedDate as UpdatedDate,
2517--	1 as CreatedBy,
2518--	it.UpdatedBy, 
2519--	it.BinNumberId, 
2520--	CAST(GunChrg as decimal(16,3)) as GunProcessingFee 
2521
2522--from EXT_items i
2523
2524--INNER JOIN EXT_pawn p
2525--	ON p.PWN_id = i.PWN_id and p.TitleLoan=0 --Drop Columned logic to remove pawns with title loans
2526
2527--INNER JOIN CEXT_InventoryItem it
2528--	ON it.SourceId = i.Items_ID AND it.StoreId = @TargetStoreId
2529
2530--INNER JOIN CEXT_InventoryItemLot il
2531--	ON il.InventoryItemId = it.InventoryItemId
2532
2533--INNER JOIN CEXT_Transaction t
2534--	ON t.SourceId = p.PWN_id
2535
2536--INNER JOIN Common.SourceTargetKeyMapping map
2537--	on map.SourceTable='ItemStatus' AND map.SourceKey = i.[Status]
2538
2539--INNER JOIN CEXT_Status st
2540--	on st.StatusCode = CASE map.TargetKey WHEN 'S' THEN 'I' WHEN 'D' THEN 'DE' ELSE map.TargetKey END
2541
2542
2543--WHERE i.Sto_Pk=@SourceStoreId and it.SourceSplittedInventoryItemId is null and il.StatusId=28
2544
2545--END
2546--GO
2547/****** Object:  StoredProcedure [dbo].[PoliceHoldDetailsExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
2548--SET ANSI_NULLS ON
2549--GO
2550--SET QUOTED_IDENTIFIER ON
2551--GO
2552
2553---- =============================================
2554---- Author:		Syed Mohsin
2555---- Create date: <11/15/2016>
2556---- Description:	<Extracts police holds / confiscates >
2557---- Sample Call: EXEC [dbo].[PoliceHoldDetailsExtract] 8 , 5
2558---- =============================================
2559
2560--CREATE Proc [dbo].[PoliceHoldDetailsExtract]
2561--@StoreId int , @UtcTime int
2562--as
2563--BEGIN
2564--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
2565
2566--	SELECT
2567--		CAST(DATEDrop Column(HOUR,@UtcTime,[date]) as datetime) as StartedDate,
2568--		--NULL as ReleasedDate,
2569--		CAST(casenum as nvarchar(100)) as CaseNumber,
2570--		CAST(agency as nvarchar(500)) as Agency,
2571--		CAST(jurisdict as nvarchar(500)) as Jurisdiction,
2572--		CAST(agentfn as nvarchar(250)) as FirstName,
2573--		CAST(agentln as nvarchar(250)) as LastName,
2574--		CAST(agentmi as nvarchar(250)) as MiddleName,
2575--		CAST(badge as nvarchar(250)) as BadgeNumber,
2576--		CAST(ac1+'-'+phone1 as nvarchar(100)) as PhoneNumber,
2577--		CAST(ext1 as nvarchar(50)) as Extension,
2578--		CAST(hc.comment as nvarchar(1000)) as Comments,
2579--		CAST(DATEDrop Column(HOUR,@UtcTime,[date]) as datetime) as CreatedDate,
2580--		u.UserId as CreatedBy,
2581--		CAST(DATEDrop Column(HOUR,@UtcTime,iif(dateout IS NULL OR LTRIM(RTRIM([dateout])) = '',[date],dateout)) as datetime) as UpdatedDate,
2582--		u2.UserId as UpdatedBy,
2583--		HCN_id as SourceId
2584--		FROM EXT_holdcon hc
2585--		 LEFT JOIN EXT_users us
2586--		 on us.USR_PK = hc.emp_fk and hc.sto_pk = us.USR_STORE
2587--		 LEFT JOIN CEXT_User u
2588--		 on u.SourceId = us.USR_ID
2589--		 LEFT JOIN CEXT_User u2
2590--		 on u2.SourceId = hc.LastUpdatedUSR_ID
2591--		 LEFT JOIN EXT_items i 
2592--		 on i.Sto_Pk=@StoreId and i.INVNUM = hc.LookupKey and hc.dateout is null and i.STATUS = 'I'
2593--		WHERE hc.[sto_pk] = @StoreId --Source StoreId for which data is being converted
2594--		--AND [date] >= DATEDrop Column(YEAR,-2,getdate()) --Extracting only last two year's data
2595--		AND ([dateout] IS NULL OR LTRIM(RTRIM([dateout])) = '') --Extracting only those items which are not released
2596--		AND hc.ishold = 2 --All un-released confiscated items
2597--		AND i.ITEMS_PK is null --Removing the Incorrect Hold/Confiscate records
2598--	UNION
2599
2600--	SELECT
2601--		CAST(DATEDrop Column(HOUR,@UtcTime,[date]) as datetime) as StartedDate,
2602--		--NULL as ReleasedDate,
2603--		CAST(casenum as nvarchar(100)) as CaseNumber,
2604--		CAST(agency as nvarchar(500)) as Agency,
2605--		CAST(jurisdict as nvarchar(500)) as Jurisdiction,
2606--		CAST(agentfn as nvarchar(250)) as FirstName,
2607--		CAST(agentln as nvarchar(250)) as LastName,
2608--		CAST(agentmi as nvarchar(250)) as MiddleName,
2609--		CAST(badge as nvarchar(250)) as BadgeNumber,
2610--		CAST(ac1+'-'+phone1 as nvarchar(100)) as PhoneNumber,
2611--		CAST(ext1 as nvarchar(50)) as Extension,
2612--		CAST(hc.comment as nvarchar(1000)) as Comments,
2613--		CAST(DATEDrop Column(HOUR,@UtcTime,[date]) as datetime) as CreatedDate,
2614--		u.UserId as CreatedBy,
2615--		CAST(DATEDrop Column(HOUR,@UtcTime,iif(dateout IS NULL OR LTRIM(RTRIM([dateout])) = '',[date],dateout)) as datetime) as UpdatedDate,
2616--		u2.UserId as UpdatedBy,
2617--		HCN_id as SourceId
2618--		FROM EXT_holdcon hc
2619--		 LEFT JOIN EXT_users us
2620--		 on us.USR_PK = hc.emp_fk and hc.sto_pk = us.USR_STORE
2621--		 LEFT JOIN CEXT_User u
2622--		 on u.SourceId = us.USR_ID
2623--		 LEFT JOIN CEXT_User u2
2624--		 on u2.SourceId = hc.LastUpdatedUSR_ID
2625--		 		 LEFT JOIN EXT_items i 
2626--		 on i.Sto_Pk=@StoreId and i.INVNUM = hc.LookupKey and hc.dateout is null and i.STATUS = 'I'
2627--		WHERE hc.[sto_pk] = @StoreId --Source StoreId for which data is being converted
2628--		--AND [date] >= DATEDrop Column(YEAR,-2,getdate()) --Extracting only last two year's data
2629--		AND ([dateout] IS NULL OR LTRIM(RTRIM([dateout])) = '') --Extracting only those items which are not released
2630--		AND hc.ishold = 1 --All un-released items in hold
2631--		AND i.ITEMS_PK is null --Removing the Incorrect Hold/Confiscate records
2632--END
2633--GO
2634/****** Object:  StoredProcedure [dbo].[PoliceHoldItemsExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
2635--SET ANSI_NULLS ON
2636--GO
2637--SET QUOTED_IDENTIFIER ON
2638--GO
2639
2640---- =============================================
2641---- Author:		Syed Mohsin
2642---- Create date: <11/15/2016>
2643---- Description:	<Extracts police holds / confiscates >
2644---- Sample Call: EXEC [dbo].[PoliceHoldItemsExtract] 8 , 182
2645---- =============================================
2646
2647--CREATE Proc [dbo].[PoliceHoldItemsExtract]
2648--@SourceStoreId int , @TargetStoreId int
2649--as
2650--BEGIN
2651--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
2652	
2653--		SELECT 
2654--			pc.PoliceConfiscateCaseDetailId,
2655--			t.TransactionId,
2656--			TransactionItemId,
2657--			iif(isinv=1,it.InventoryItemId,NULL) as InventoryItemId,
2658--			iif(isinv=1,CAST(i.OnHand as decimal(12,3)),NULL) as Quantity,
2659--			iif(hc.ishold = 1,14,10) as StatusId,
2660--			@TargetStoreId as StoreId, --TargetStore
2661--			pc.CreatedDate,
2662--			pc.CreatedBy,
2663--			pc.UpdatedDate,
2664--			pc.UpdatedBy
2665	
2666--		FROM EXT_HoldConItems hci
2667
2668--		 INNER JOIN EXT_holdcon hc on hci.hc_fk = hc.hc_pk and hc.sto_pk = hci.sto_fk
2669--		 INNER JOIN CEXT_PoliceConfiscateDetail pc on pc.SourceId = hc.HCN_id
2670--		 LEFT JOIN EXT_items i on i.Sto_Pk=hci.sto_fk and i.ITEMS_PK = hci.items_fk
2671--		 LEFT JOIN CEXT_InventoryItem it on it.SourceId = i.Items_ID
2672--		 LEFT JOIN CEXT_InventoryItemLot il on il.InventoryItemId = it.InventoryItemId
2673--		 LEFT JOIN EXT_pawn p on p.STORE_NO = hci.sto_fk and p.TICKETNUM = iif(hc.isinv=2,hc.lookupkey,NULL)
2674--		 LEFT JOIN CEXT_Transaction t on t.SourceId = p.PWN_id
2675--		 LEFT JOIN CEXT_TransactionItem ti on ti.TransactionId = t.TransactionId and ti.InventoryItemLotId = il.InventoryItemLotId
2676--		 WHERE hci.sto_fk = @SourceStoreId
2677	
2678--END
2679--GO
2680/****** Object:  StoredProcedure [dbo].[PoliceHoldItemsLotExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
2681--SET ANSI_NULLS ON
2682--GO
2683--SET QUOTED_IDENTIFIER ON
2684--GO
2685
2686---- =============================================
2687---- Author:		Syed Mohsin
2688---- Create date: <11/15/2016>
2689---- Description:	<Extracts police holds / confiscates >
2690---- Sample Call: EXEC [dbo].[PoliceHoldItemsLotExtract] 8
2691---- =============================================
2692
2693--CREATE Proc [dbo].[PoliceHoldItemsLotExtract]
2694--@SourceStoreId int
2695--as
2696--BEGIN
2697--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
2698	
2699--SELECT
2700--	pci.PoliceConfiscateCaseDetailId,
2701--	pci.InventoryItemId,
2702--	pci.StatusId,
2703--	pci.Quantity*-1 as Quantity,
2704--	il.Cost,
2705--	il.DateIn,
2706--	il.SourceInventoryItemId,
2707--	il.SourceInventoryItemIdVersion,
2708--	pc.CreatedDate,
2709--	0 as CreatedBy
2710
2711--FROM ExT_holdcon hc
2712--INNER JOIN CEXT_PoliceConfiscateDetail pc on pc.SourceId = hc.HCN_id
2713--INNER JOIN CEXT_PoliceConfiscateItems pci on pci.PoliceConfiscateCaseDetailId = pc.PoliceConfiscateCaseDetailId
2714--INNER JOIN CEXT_InventoryItemLot il on il.InventoryItemId = pci.InventoryItemId and il.StatusId = 28 and SourceInventoryItemId is null
2715
2716--WHERE hc.sto_pk = @SourceStoreId
2717	
2718--END
2719--GO
2720/****** Object:  StoredProcedure [dbo].[PoliceHoldItemsLotInsert]    Script Date: 6/13/2018 9:39:35 AM ******/
2721--SET ANSI_NULLS ON
2722--GO
2723--SET QUOTED_IDENTIFIER ON
2724--GO
2725
2726---- =============================================
2727---- Author:		Syed Mohsin
2728---- Create date: <11/15/2016>
2729---- Description:	<Extracts police holds / confiscates >
2730---- Sample Call: EXEC [dbo].[PoliceHoldItemsLotInsert] 182
2731---- =============================================
2732
2733--CREATE Proc [dbo].[PoliceHoldItemsLotInsert]
2734--@TargetStoreId int
2735--as
2736--BEGIN
2737--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
2738	
2739--		SELECT	pc.InventoryItemId,
2740--				pc.StatusId,
2741--				pc.Quantity,
2742--				pc.Cost,
2743--				pc.DateIn,
2744--				pc.CreatedDate,
2745--				il.InventoryItemLotNumber,
2746--				il.CreatedBy,
2747--				il.UpdatedDate,
2748--				il.UpdatedBy
2749--FROM CEXT_PoliceConfiscateInventoryItemLot pc
2750--INNER JOIN CEXT_PoliceConfiscateItems pci on pci.PoliceConfiscateCaseDetailId = pc.PoliceConfiscateCaseDetailId
2751--INNER JOIN CEXT_InventoryItemLot il on pc.InventoryItemId = il.InventoryItemId
2752--WHERE pci.StoreId = @TargetStoreId
2753	
2754--END
2755--GO
2756/****** Object:  StoredProcedure [dbo].[RateTableExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
2757--SET ANSI_NULLS ON
2758--GO
2759--SET QUOTED_IDENTIFIER ON
2760--GO
2761
2762---- =============================================
2763---- Author:		Syed Mohsin
2764---- Create date: <8/18/2016>
2765---- Description:	<Extracts data for RateTables>
2766---- Sample Call: EXEC [dbo].[RateTableExtract] 8
2767
2768---- Updated By: Syed Mohsin
2769---- Update date: <9/5/2016>
2770---- Comments: Made fixes for iteration 1 response.
2771---- =============================================
2772
2773--CREATE Proc [dbo].[RateTableExtract]
2774--@StoreId int
2775--AS
2776--BEGIN
2777--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
2778--select * from (
2779--SELECT
2780--      1 as StoreId, -- Drop Column in package
2781--      ROW_NUMBER() OVER(PARTITION BY [DESCRIPT] ORDER BY UpdatedDate DESC) AS RowNumber, -- Fixed iteration 1 bug
2782--	  cast([DESCRIPT] as nvarchar(20)) as TableName,
2783--	  cast (iif(s.DEFAULTRAT IS NULL,0,1) as bit) as IsDefault,
2784--	  0 as IsDeleted,
2785--	  getdate() as CreatedDate,
2786--	  1 as CreatedBy, -- Drop Column in package
2787--	  getdate() as UpdatedDate,
2788--	  u.UserId as UpdatedBy
2789
2790--  FROM EXT_c_rates c
2791--  LEFT OUTER JOIN EXT_SysInfo2 s
2792--	ON c.DESCRIPT = s.DEFAULTRAT AND s.STO_PK = c.STO_PK
2793--  LEFT OUTER JOIN CEXT_User u
2794--	ON c.LastUpdatedUSR_ID = u.SourceId 
2795--	WHERE
2796--	c.sto_pk = @StoreId and c.SimpleInterest = 0
2797--	) s WHERE RowNumber = 1
2798--END
2799--GO
2800/****** Object:  StoredProcedure [dbo].[RateTablePeriodExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
2801--SET ANSI_NULLS ON
2802--GO
2803--SET QUOTED_IDENTIFIER ON
2804--GO
2805
2806---- =============================================
2807---- Author:		Syed Mohsin
2808---- Create date: <8/18/2016>
2809---- Description:	<Extracts data for RateTablePeriod>
2810---- Sample Call: EXEC [dbo].[RateTablePeriodExtract] 8
2811---- =============================================
2812
2813--CREATE Proc [dbo].[RateTablePeriodExtract]
2814--@StoreId int
2815--AS
2816--BEGIN
2817--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
2818--SELECT r.RateTableId as RateTableId
2819--	  ,cast([PERIOD] as int) as PeriodId
2820--      ,cast([HD] as decimal(12,2)) as UptoAmount
2821--      ,cast(iif([DP]='$',1,0) as bit) as CalculationType
2822--      ,cast([AP] as decimal(12,2)) as ServiceRate
2823--      ,cast([Interest]/100 as decimal(5,4)) as InterestPercentage
2824--	  ,0 as IsDeleted
2825--	  ,getdate() as CreatedDate
2826--	  ,1 as CreatedBy -- Drop Column in package
2827--	  ,getdate() as UpdatedDate
2828--	  ,u.UserId as UpdatedBy  
2829--	  ,cast([MINAMT] as decimal(12,2)) as Minimum
2830--      ,cast([ONECHRG] as decimal(12,2)) as OneTimeFee
2831--      ,cast([MONCHRG] as decimal(12,2)) as FeePerPeriod
2832
2833--  FROM EXT_c_rates c
2834--		inner join CEXT_RateTable r
2835--			on c.DESCRIPT = r.TableName
2836--		left join CEXT_User u
2837--			on c.LastUpdatedUSR_ID = u.SourceId
2838--	WHERE c.SimpleInterest=0 AND c.STO_PK = @StoreId
2839--END
2840--GO
2841/****** Object:  StoredProcedure [dbo].[RequiredFieldsExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
2842--SET ANSI_NULLS ON
2843--GO
2844--SET QUOTED_IDENTIFIER ON
2845--GO
2846
2847---- =============================================
2848---- Author:		Farrukh Ijaz
2849---- Create date: <8/17/2016>
2850---- Description:	<Extracts all system options in required format>
2851---- Sample Call: EXEC [dbo].[RequiredFieldsExtract] 8
2852---- =============================================
2853--CREATE PROCEDURE [dbo].[RequiredFieldsExtract] 
2854--	@SourceStoreId as int=0
2855--AS
2856--BEGIN
2857--	-- SET NOCOUNT ON Drop Columned to prevent extra result sets from
2858--	-- interfering with SELECT statements.
2859--	SET NOCOUNT ON;
2860--	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
2861
2862
2863--SELECT RF.EntityName, RF.SourceKey, RF.SourceValue, mapping.[TargetKey], reqFields.RequiredFieldId  FROM(
2864-----Select from CustReq
2865--SELECT 'CustReq' AS SourceTable, 'Customer' AS EntityName, SourceKey, SourceValue
2866--FROM 
2867--(SELECT CUS_CELLPHONE
2868--		,CUS_SSNUM
2869--		,CUS_BIRTHDate
2870--		,CUS_BIRTHCITY
2871--		,CUS_BIRTHSTATE
2872--		--,CUS_BIRTHCOUNTRY_NA
2873--		,CUS_HEIGHT
2874--		,CUS_WEIGHT
2875--		,CUS_HAIRFK
2876--		,CUS_EYESFK
2877--		,CUS_RACEFK
2878--		,CUS_SEX
2879--		,CUS_MARKS
2880--		,CUS_IDTYP1
2881--		,CUS_IDNUM1
2882--		,CUS_ID1EXP
2883--		,CUS_ID1ISSUE
2884--		,CUS_IDTYP2
2885--		,CUS_IDNUM2
2886--		,CUS_ID2EXP
2887--		,CUS_ID2ISSUE
2888--		,CUS_FNAME
2889--		,CUS_MNAME
2890--		,CUS_LNAME
2891--		,CUS_Drop Column1
2892--		,CUS_CITY
2893--		,CUS_STATE
2894--		,CUS_ZIP
2895--		,CUS_PHONE1
2896--		--,CUS_Drop ColumnITIONALINFO_NA
2897--		,CUS_SPECIAL
2898--FROM EXT_CustReq WHERE CUS_STORE=@SourceStoreId
2899--)CR
2900--unpivot
2901--(
2902--SourceValue for SourceKey IN
2903--(
2904--		CUS_CELLPHONE
2905--		,CUS_SSNUM
2906--		,CUS_BIRTHDate
2907--		,CUS_BIRTHCITY
2908--		,CUS_BIRTHSTATE
2909--		--,CUS_BIRTHCOUNTRY_NA
2910--		,CUS_HEIGHT
2911--		,CUS_WEIGHT
2912--		,CUS_HAIRFK
2913--		,CUS_EYESFK
2914--		,CUS_RACEFK
2915--		,CUS_SEX
2916--		,CUS_MARKS
2917--		,CUS_IDTYP1
2918--		,CUS_IDNUM1
2919--		,CUS_ID1EXP
2920--		,CUS_ID1ISSUE
2921--		,CUS_IDTYP2
2922--		,CUS_IDNUM2
2923--		,CUS_ID2EXP
2924--		,CUS_ID2ISSUE
2925--		,CUS_FNAME
2926--		,CUS_MNAME
2927--		,CUS_LNAME
2928--		,CUS_Drop Column1
2929--		,CUS_CITY
2930--		,CUS_STATE
2931--		,CUS_ZIP
2932--		,CUS_PHONE1
2933--		--,CUS_Drop ColumnITIONALINFO_NA
2934--		,CUS_SPECIAL
2935--)
2936--) unpiv
2937
2938--UNION
2939
2940-----Select from ITRQ_ItemReq
2941--SELECT 'ITRQ_ItemReq' AS SourceTable, 'Item' AS EntityName, SourceKey, SourceValue
2942--FROM 
2943--(SELECT level1_fk
2944--		,level2_fk
2945--		,level3_fk
2946--		,level4_fk
2947--		,level5_fk
2948--		,modelnum
2949--		,serialnum
2950--		,color
2951--		,condition_item
2952--		,ownernum
2953--		,bin
2954--		,amount
2955--		,resaleamt
2956--		,onhand
2957--		,descript
2958--		,metal_fk
2959--		,karat_fk
2960--		,[weight]
2961--		,gender_fk
2962--		,style_fk
2963--		,sizelen_fk
2964--		,action_fk
2965--		,finish_fk
2966--		,barrel_fk
2967--		,[length]
2968--		,caliber_fk
2969--		,condition
2970--		,importerfk
2971--		,numstone
2972--		,typstonefk
2973--		,shape_fk
2974--		,carat
2975--		,color_fk
2976--		,stone_weight
2977--		,stone_length
2978--		,stone_width
2979--		,translucfk
2980--FROM EXT_ITRQ_ItemReq  WHERE EXT_ITRQ_ItemReq.ITRQ_Store=@SourceStoreId
2981--)IR
2982--unpivot
2983--(
2984--SourceValue for SourceKey IN
2985--(
2986--		level1_fk
2987--		,level2_fk
2988--		,level3_fk
2989--		,level4_fk
2990--		,level5_fk
2991--		,modelnum
2992--		,serialnum
2993--		,color
2994--		,condition_item
2995--		,ownernum
2996--		,bin
2997--		,amount
2998--		,resaleamt
2999--		,onhand
3000--		,descript
3001--		,metal_fk
3002--		,karat_fk
3003--		,[weight]
3004--		,gender_fk
3005--		,style_fk
3006--		,sizelen_fk
3007--		,action_fk
3008--		,finish_fk
3009--		,barrel_fk
3010--		,[length]
3011--		,caliber_fk
3012--		,condition
3013--		,importerfk
3014--		,numstone
3015--		,typstonefk
3016--		,shape_fk
3017--		,carat
3018--		,color_fk
3019--		,stone_weight
3020--		,stone_length
3021--		,stone_width
3022--		,translucfk
3023--)
3024--) unpiv
3025--) RF
3026--INNER JOIN Common.[SourceTargetKeyMapping] mapping ON RF.SourceTable = mapping.SourceTable AND RF.SourceKey=mapping.SourceKey
3027--INNER JOIN CEXT_RequiredField reqFields ON mapping.TargetKey = reqFields.RequiredFieldName AND RF.EntityName= reqFields.EntityName
3028
3029--END
3030--GO
3031/****** Object:  StoredProcedure [dbo].[ReWriteExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
3032--SET ANSI_NULLS ON
3033--GO
3034--SET QUOTED_IDENTIFIER ON
3035--GO
3036
3037---- =============================================
3038---- Author:		Syed Mohsin
3039---- Create date: <11/22/2017>
3040---- Description:	<Extracts data for ReWrite history>
3041---- Sample Call: EXEC [dbo].[ReWriteExtract] 8, 171
3042---- =============================================
3043
3044--CREATE Proc [dbo].[ReWriteExtract]
3045--@SourceStoreID int, @TargetStoreId int 
3046
3047--AS
3048
3049--BEGIN
3050--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
3051
3052--SELECT 
3053--	tn.TransactionId,
3054--	iif(p.ORIGTICKET != p.TICKETNUM,t.TicketNumber,NULL) as OriginalTicketNumber,
3055--	tn.TicketNumber,
3056--	tn.CreatedDate as RewriteDate,
3057--	tn.CreatedDate,
3058--	tn.CreatedBy 
3059--from EXT_Pawn p 
3060
3061--LEFT JOIN CEXT_Transaction t on t.TicketNumber = p.ORIGTICKET and t.TransactionTypeId=1
3062
3063--INNER JOIN CEXT_Transaction tn on tn.TicketNumber = p.TICKETNUM and tn.TransactionTypeId=1
3064
3065
3066--END
3067--GO
3068/****** Object:  StoredProcedure [dbo].[SaleLayawayTransactionExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
3069--SET ANSI_NULLS ON
3070--GO
3071--SET QUOTED_IDENTIFIER ON
3072--GO
3073
3074---- =============================================
3075---- Author:		Syed Mohsin
3076---- Create date: <9/5/2016>
3077---- Description:	<Extracts transactions for sale / layaway >
3078---- Sample Call: EXEC [dbo].[SaleLayawayTransactionExtract] 8 , 5
3079---- =============================================
3080
3081--CREATE Proc [dbo].[SaleLayawayTransactionExtract]
3082--@StoreId int , @UtcTime int
3083--as
3084--BEGIN
3085--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
3086--Select  DATEDrop Column(HOUR,@UtcTime,Cast (DATEin As datetime)) as CreatedDate,
3087--		u2.UserId as CreatedBy, --Drop Column in package
3088--       DATEDrop Column(HOUR,@UtcTime,Cast (
3089--	   CASE 
3090--	   WHEN s.TRANS = 'S' and s.STATUS = 'S'
3091--	   THEN s.DATEin
3092--	   WHEN s.TRANS = 'S' and s.STATUS != 'S'
3093--	   THEN s.DateOut
3094--	   ELSE s.DateOut
3095--	   END
3096--	    As datetime)) as UpdatedDate,
3097--       u.userId as UpdatedBy,
3098--       1 as StoreID, --Drop Column in package 
3099--       c.CustomerId as CustomerId,
3100--	   tr.TransactionTypeId ,
3101--       Cast (TICKETNUM As int) as TicketNumber,
3102--       Cast (PERIOD As int) as Period,
3103--       DATEDrop Column(HOUR,@UtcTime,Cast (DATEin As datetime)) as InDate, --UTC CONVERSION
3104--       DATEDrop Column(HOUR,@UtcTime,Cast (DATEout As datetime)) as OutDate, --UTC CONVERSION
3105--       Cast (SaleAmt + s.tax  As decimal(16, 3)) as Amount,
3106--       Cast (s.NOTE As nvarchar(500)) as Note,
3107--      st.StatusId as StatusId,
3108
3109--	  	0 as IsQueued,
3110--       Cast (sld_Message As nvarchar(200)) as Message,
3111--       Cast (s.COMMENT As nvarchar(200)) as VoidDescription,
3112--	   s.GunProcFee as GunProcessingFee ,
3113--       Cast (s.SLD_id As nvarchar(50)) as SourceID
3114
3115--from EXT_Sold s 
3116--INNER JOIN EXT_cust cst
3117--	on cst.Cus_PK = s.CUS_FK
3118--INNER JOIN CEXT_Customer c
3119--      	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))
3120--	and c.BirthDate like LTRIM(RTRIM(cst.CUS_BIRTHDate)))
3121--LEFT JOIN CEXT_User u
3122--	on u.SourceId = s.LastUpdatedUSR_ID
3123--INNER JOIN CEXT_TransactionType tr
3124--	on tr.TransactionTypeDesc = CASE s.TRANS
3125--								WHEN 'S' THEN 'Sale'
3126--								WHEN 'C' THEN 'Sale'
3127--								WHEN 'L' THEN 'Layaway'
3128--								END
3129--INNER JOIN Common.SourceTargetKeyMapping map
3130--	on map.SourceTable='TransactionCodes' AND map.SourceKey = CASE 
3131--												WHEN s.[STATUS]='V' AND s.TRANS='L' THEN 'VLA'
3132--												WHEN s.[STATUS]='V' AND s.TRANS='S' THEN 'VS'
3133--												WHEN s.[STATUS]='R' AND s.TRANS='L' THEN 'VLA'
3134--												WHEN s.[STATUS]='R' AND s.TRANS='S' THEN 'VS'
3135--												ELSE s.STATUS
3136--												END
3137--INNER JOIN CEXT_Status st
3138--	on st.StatusCode = map.TargetKey
3139
3140--LEFT JOIN EXT_users us on us.USR_PK = s.USR_fk and us.USR_STORE = s.STO_PK
3141
3142--LEFT JOIN CEXT_User u2 on u2.SourceId = us.USR_ID
3143
3144
3145--WHERE s.STO_PK = @StoreId
3146
3147--END
3148--GO
3149/****** Object:  StoredProcedure [dbo].[SaleLayawayTransactionItemExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
3150--SET ANSI_NULLS ON
3151--GO
3152--SET QUOTED_IDENTIFIER ON
3153--GO
3154
3155---- =============================================
3156---- Author:		Syed Mohsin
3157---- Create date: <9/5/2016>
3158---- Description:	<Extracts transaction items for sale / layaway>
3159---- Sample Call: EXEC [dbo].[SaleLayawayTransactionItemExtract] 1 , 1831 , 5
3160
3161---- Updated By: Syed Mohsin
3162---- Update date: <9/22/2016>
3163---- Comments: Drop Columned check for non-inventory items.
3164
3165---- Updated By: Syed Mohsin
3166---- Update date: <1/5/2017>
3167---- Comments: Splitted items are removed because not part of TransactionItem.
3168
3169---- Updated By: Syed Mohsin
3170---- Update date: <4/3/2017>
3171---- Comments: Splitted items are Drop Columned becasue this is sale layaway transactions. InventoryItemLot join conditions updated. Repetition is removed.
3172---- =============================================
3173
3174--CREATE Proc [dbo].[SaleLayawayTransactionItemExtract]
3175--@StoreId int ,  @TargetStoreId int, @UtcTime int
3176--as
3177--BEGIN
3178--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
3179--DECLARE @lookupTypeIDBin int
3180--SET @lookupTypeIDBin = (SELECT LookupTypeId from CEXT_LookupType  
3181--							where LookupTypeName='Bin')
3182
3183
3184--DECLARE @StateTax DECIMAL(16,3)
3185--DECLARE @LocalTax DECIMAL(16,3)
3186--DECLARE @CountyTax DECIMAL(16,3)
3187
3188--SET @StateTax = (SELECT sso.SystemOptionValue FROM CEXT_.SystemOption so 
3189--INNER JOIN CEXT_StoreSystemOption sso ON so.SystemOptionId = sso.SystemOptionId
3190--WHERE so.SystemOptionKey = 'GENERAL_SALES_TAX_STATE' AND sso.StoreId = @TargetStoreId )
3191
3192--SET @LocalTax = (SELECT sso.SystemOptionValue FROM CEXT_SystemOption so 
3193--INNER JOIN CEXT_StoreSystemOption sso ON so.SystemOptionId = sso.SystemOptionId
3194--WHERE so.SystemOptionKey = 'GENERAL_SALES_TAX_LOCAL' AND sso.StoreId = @TargetStoreId )
3195
3196--SET @CountyTax = (SELECT sso.SystemOptionValue FROM CEXT_SystemOption so 
3197--INNER JOIN CEXT_StoreSystemOption sso ON so.SystemOptionId = sso.SystemOptionId
3198--WHERE so.SystemOptionKey = 'GENERAL_SALES_TAX_COUNTY' AND sso.StoreId = @TargetStoreId )
3199
3200
3201--Select  
3202--iif(s.RETURNSOLD IS NULL,tr.CreatedDate,DATEDrop Column(HOUR,@UtcTime,CAST(s.RETURNSOLD as datetime))) as CreatedDate,--UTC CONVERSION
3203--       1 as CreatedBy, -- Drop Column in package
3204--       iif(s.RETURNSOLD IS NULL,tr.UpdatedDate,DATEDrop Column(HOUR,@UtcTime,CAST(s.RETURNSOLD as datetime))) as UpdatedDate,--UTC CONVERSION
3205--       u.UserId as UpdatedBy,
3206--       1 as StoreID, -- Drop Column in package
3207--       --iif(tr.StatusId = 2 , CASE WHEN tr.transactiontypeid = 3 then 19 ELSE 2 END ,st.StatusId) StatusId,
3208--	   iif(tr.transactiontypeid = 5 and st.statusid = 19,2,st.statusid ) StatusId,
3209--	   tr.TransactionId,
3210--	   lot.InventoryItemLotId ,
3211--        Cast (s.AMOUNT As decimal(16, 3)) as Resale,
3212--		Cast (s.Cost As decimal(16, 3)) as Cost,
3213--       Cast (s.NUMBERSOLD As decimal(16, 3)) as Quantity, 
3214--       Cast (LTRIM(RTRIM(s.DESCRIPT)) As nvarchar(500)) as Comment,
3215--Cast   (
3216--                     iif(s.AMOUNT<i.RESALEAMT AND s.AMOUNT >= 0 ,
3217--                                  (i.RESALEAMT-s.AMOUNT)/i.RESALEAMT,
3218--                                  0
3219--                     )
3220--                     As decimal(5, 2)
3221--              ) as DiscountPercentage,
3222
3223--       Cast   (
3224--                     iif(s.TAXEXEMPT=1 and s.CountyTaxExempt=1,1,0)
3225--                     As bit
3226--                     ) as IsTaxExempt,
3227--       iif(s.items_pk <=0, Cast (s.DESCRIPT As nvarchar(500)),NULL) as NonInventoryItemDescription,
3228--       iif(s.items_pk <=0,Cast (s.INVNUM As nvarchar(50)),NULL) as NonInventoryNumber,
3229--       lv.LookupValueId as BinNumberId,
3230--	   iif(s.Taxexempt=0 and s.CountyTaxExempt=0,@StateTax,NULL ) as StateTax,
3231--	   iif(s.Taxexempt=0 and s.CountyTaxExempt=0,@CountyTax,NULL ) as CountyTax,
3232--	   iif(s.Taxexempt=0 and s.CountyTaxExempt=0,@LocalTax,NULL ) as LocalTax,
3233--	   iif(s.Taxexempt=0,iif(sld.TAX > 0,((s.NUMBERSOLD * s.Amount)/sld.Taxable)*sld.Tax,0),NULL ) as Tax,
3234--       Cast (i.RESALEAMT As decimal(16, 3)) as OrignalResale,
3235--	   NULL as GunProcessingFee,
3236--	   ROW_NUMBER() OVER (PARTITION BY s.TICKETNUM ORDER BY sitem_PK desc) as TransactionItemLinkedId
3237
3238--from EXT_sitems s
3239    
3240--	INNER JOIN Common.SourceTargetKeyMapping map
3241--		on map.SourceTable='ItemStatus' AND map.SourceKey = s.[Status]
3242	
3243--	INNER JOIN CEXT_Status st
3244--		on st.StatusCode = CASE map.TargetKey WHEN 'S' THEN 'SA' WHEN 'D' THEN 'DE' ELSE map.TargetKey END
3245	
3246--	inner join EXT_Sold sld
3247--		on sld.TICKETNUM = s.TICKETNUM AND sld.STO_PK = s.Sto_PK
3248	
3249--	INNER join CEXT_Transaction tr
3250--		on tr.SourceId = sld.SLD_id
3251		
3252--		left join CEXT_User u
3253--			on u.SourceId = s.LastUpdatedUSR_ID
3254	   
3255--	   left outer join CEXT_LookupValue lv
3256--              on LTRIM(RTRIM(s.BIN)) != '' and s.Bin =lv.Value AND lv.LookupTypeId = @lookupTypeIDBin
3257       
3258--	   left outer join EXT_items i
3259--              on s.Items_id=i.Items_id
3260	   
3261--	   left join CEXT_InventoryItem ii
3262--              on s.Items_ID IS NOT NULL and ii.SourceId=s.Items_ID
3263		
3264--		left join CEXT_InventoryItemLot lot
3265--			  on ii.InventoryItemId is not null and lot.SourceInventoryItemId IS NULL and lot.StatusId=28 and lot.InventoryItemId = ii.InventoryItemId
3266--		--left join PawnMaster_foxPro.dbo.Acct ac
3267--			  --on ac.sto_pk = @StoreId and ac.TICKETNUM = s.TICKETNUM and ac.TOWHOM='GUN PROCESSING FEE-S'
3268
3269
3270
3271
3272--where s.Sto_PK = @StoreId --and ii.SourceSplittedInventoryItemId IS NULL
3273
3274--END
3275--GO
3276/****** Object:  StoredProcedure [dbo].[ServiceFeePaymentsExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
3277--SET ANSI_NULLS ON
3278--GO
3279--SET QUOTED_IDENTIFIER ON
3280--GO
3281
3282---- =============================================
3283---- Author:		<Syed Mohsin>
3284---- Create date: <08/01/2017>
3285---- Description:	<Extract TransactionPayments data from Acct table on which are service fee paid>
3286---- Sample Call: EXEC [dbo].[ServiceFeePaymentsExtract] 1,399,5
3287---- =============================================
3288
3289--CREATE PROCEDURE [dbo].[ServiceFeePaymentsExtract]
3290--	-- Drop Column the parameters for the stored procedure here
3291--	@SourceStoreId as int,@TargetStoreId as int , @UtcTime int
3292--AS
3293--BEGIN
3294--	-- SET NOCOUNT ON Drop Columned to prevent extra result sets from
3295--	-- interfering with SELECT statements.
3296--	SET NOCOUNT ON;
3297--	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
3298
3299--DECLARE @BatchId int
3300--SET @BatchId = (SELECT MAX(batchid) from CEXT_TransactionPayment)
3301
3302--SELECT 
3303--	--ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as Num,
3304--	[TYPE]
3305--	,'PAWN' AS PaymentTransType
3306--	,a.TICKETNUM as TicketNumber
3307--	,iif(LostTicket=0,a.AMOUNT,a.amount-LostTicket) as AMOUNT
3308--	,t.TransactionId
3309--	,4 as PaymentTypeId
3310--	,DATEDrop Column(HOUR,@UtcTime,a.DATEin) DATEin
3311--	,STATETAX+COUNTYTAX+LOCALTAX AS Tax
3312--	,DATEDrop Column(HOUR,@UtcTime,a.DATEin) AS CreatedDate
3313--	,u.UserId
3314--	,Acct_PK
3315--	,c.CustomerId
3316--	,lv1.LookupValueId AS TenderType1
3317--	,lv2.LookupValueId AS TenderType2
3318--	,TENDERAMT1
3319--	,TENDERAMT2
3320--	,Acct_PK AS SourceId
3321--	,DATEDrop Column(DAY,-p.NUMDAYS,DATEDrop Column(HOUR,@UtcTime,p.OrigChgDate)) as CHARGEDATE
3322--	, 0 as IsQueued
3323--	, @BatchId + ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as BatchId
3324--	,CAST('Service Charges' as NVARCHAR(500)) as VALUE
3325--	,CAST(a.RTsvc as decimal(12,3)) as ServiceFee
3326--	,CAST(a.RTint as decimal(12,3)) as InterestPercentage
3327--	,CAST(a.RTonetime as decimal(12,3)) as OneTimeFee
3328--	,CAST(a.RTper as decimal(12,3)) as FeePerPeriod
3329--	,CAST(a.Gun as decimal(12,3)) as FirearmFee
3330--	,a.Cus_Credit as PaidCustomerCredit
3331
3332-- FROM EXT_Acct a
3333
3334--inner join EXT_Pawn p 
3335--on p.TICKETNUM = a.TICKETNUM
3336
3337--inner join CEXT_transaction t 
3338--on t.TicketNumber = a.TICKETNUM and t.TransactionTypeId = 1
3339
3340--LEFT JOIN EXT_cust cst
3341--ON cst.Cus_PK = a.CUS_FK 
3342
3343--LEFT JOIN CEXT_Customer c
3344--ON c.SourceId = cst.Cus_id
3345
3346--LEFT JOIN EXT_users us
3347--on us.USR_PK = a.Usr_FK and us.USR_STORE = @SourceStoreId
3348--LEFT JOIN CEXT_User u
3349--on u.SourceId = us.USR_ID
3350
3351--LEFT JOIN EXT_Lookup_C tt1 ON a.TENDERTYP1 = tt1.lc_pk
3352--LEFT JOIN EXT_Lookup_C tt2 ON a.TENDERTYP2 = tt2.lc_pk
3353--LEFT JOIN CEXT_LookupValue lv1 ON tt1.lc_Descript = lv1.Value and tt1.lc_pk = lv1.SourceId
3354--LEFT JOIN CEXT_LookupValue lv2 ON tt2.lc_Descript = lv2.Value and tt2.lc_pk = lv2.SourceId
3355
3356--where p.FLOATAMT = 0 and p.STORE_NO = @SourceStoreId and a.sto_pk = @SourceStoreId and t.StoreId = @TargetStoreId and TYPE = 'PPP'
3357
3358--END
3359--GO
3360/****** Object:  StoredProcedure [dbo].[Service-PartialPaymentsExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
3361--SET ANSI_NULLS ON
3362--GO
3363--SET QUOTED_IDENTIFIER ON
3364--GO
3365
3366---- =============================================
3367---- Author:		<Syed Mohsin>
3368---- Create date: <08/01/2017>
3369---- Description:	<Extract TransactionPayments data from Acct table which are partial payments and service charges paid>
3370---- Sample Call: EXEC [dbo].[Service-PartialPaymentsExtract] 1,399,5
3371---- =============================================
3372
3373--CREATE PROCEDURE [dbo].[Service-PartialPaymentsExtract]
3374--	-- Drop Column the parameters for the stored procedure here
3375--	@SourceStoreId as int,@TargetStoreId as int , @UtcTime int
3376--AS
3377--BEGIN
3378--	-- SET NOCOUNT ON Drop Columned to prevent extra result sets from
3379--	-- interfering with SELECT statements.
3380--	SET NOCOUNT ON;
3381--	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
3382
3383--DECLARE @BatchId int
3384--SET @BatchId = (SELECT MAX(batchid) from CEXT_TransactionPayment)
3385
3386--SELECT 
3387--	[TYPE]
3388--	,'PAWN' AS PaymentTransType
3389--	,a.TICKETNUM as TicketNumber
3390--	,iif(LostTicket=0,a.AMOUNT,a.amount-LostTicket) as AMOUNT
3391--	,t.TransactionId
3392--	,4 as PaymentTypeId
3393--	,DATEDrop Column(HOUR,@UtcTime,a.DATEin) DATEin
3394--	,STATETAX+COUNTYTAX+LOCALTAX AS Tax
3395--	,DATEDrop Column(HOUR,@UtcTime,a.DATEin) AS CreatedDate
3396--	,u.UserId
3397--	,Acct_PK
3398--	,c.CustomerId
3399--	,lv1.LookupValueId AS TenderType1
3400--	,lv2.LookupValueId AS TenderType2
3401--	,TENDERAMT1
3402--	,TENDERAMT2
3403--	,Acct_PK AS SourceId
3404--	,DATEDrop Column(DAY,-p.NUMDAYS,DATEDrop Column(HOUR,@UtcTime,p.OrigChgDate)) as CHARGEDATE
3405--	, 0 as IsQueued
3406--	, @BatchId + ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as BatchId
3407--	,CAST('Service Charges' as NVARCHAR(500)) as VALUE
3408--	,CAST(a.RTsvc as decimal(12,3)) as ServiceFee
3409--	,CAST(a.RTint as decimal(12,3)) as InterestPercentage
3410--	,CAST(a.RTonetime as decimal(12,3)) as OneTimeFee
3411--	,CAST(a.RTper as decimal(12,3)) as FeePerPeriod
3412--	,CAST(a.Gun as decimal(12,3)) as FirearmFee
3413--	,a.Cus_Credit as PaidCustomerCredit
3414-- FROM EXT_Pawn p 
3415--inner join (
3416--select TICKETNUM  ,SUM(AMOUNT) [Total Amount] from EXT_acct a
3417--WHERE a.TYPE = 'PPP' and a.sto_pk = 1
3418--GROUP BY TICKETNUM 
3419--)q
3420--on q.TICKETNUM = p.TICKETNUM and q.[Total Amount] > p.FLOATAMT
3421--inner join EXT_Acct a on a.TICKETNUM = p.TICKETNUM and a.sto_pk = 1
3422
3423--inner join CEXT_Transaction t 
3424--on t.TicketNumber = a.TICKETNUM and t.TransactionTypeId = 1
3425
3426--LEFT JOIN EXT_cust cst
3427--ON cst.Cus_PK = a.CUS_FK 
3428
3429--LEFT JOIN CEXT_Customer c
3430--ON c.SourceId = cst.Cus_id
3431
3432--LEFT JOIN EXT_users us
3433--on us.USR_PK = a.Usr_FK and us.USR_STORE = @SourceStoreId
3434--LEFT JOIN CEXT_User u
3435--on u.SourceId = us.USR_ID
3436
3437--LEFT JOIN EXT_Lookup_C tt1 ON a.TENDERTYP1 = tt1.lc_pk
3438--LEFT JOIN EXT_Lookup_C tt2 ON a.TENDERTYP2 = tt2.lc_pk
3439--LEFT JOIN CEXT_LookupValue lv1 ON tt1.lc_Descript = lv1.Value
3440--LEFT JOIN CEXT_LookupValue lv2 ON tt2.lc_Descript = lv2.Value
3441
3442--WHERE p.STORE_NO = 1 and p.FLOATAMT > 0
3443
3444--END
3445--GO
3446/****** Object:  StoredProcedure [dbo].[sp_Drop ColumnStagingColumns]    Script Date: 6/13/2018 9:39:35 AM ******/
3447--SET ANSI_NULLS ON
3448--GO
3449--SET QUOTED_IDENTIFIER ON
3450--GO
3451
3452---- =============================================
3453---- Author:		Syed Mohsin
3454---- Create date: <8/4/2016>
3455---- Description:	<This sp will Drop Column SourceId columns to tables>
3456---- Sample Call: EXEC [dbo].[sp_Drop ColumnStagingColumns]
3457---- =============================================
3458
3459--CREATE PROC [dbo].[sp_Drop ColumnStagingColumns]
3460	
3461	
3462--AS
3463--	BEGIN
3464--		exec sp_execute_remote
3465--		N'Company', -- This is the external data source name…
3466--		N'ALTER TABLE [ItemType] Drop Column [SourceId] nvarchar(50) NULL;
3467
3468--		ALTER TABLE [LookupValue] Drop Column [SourceId] nvarchar(50) NULL;
3469
3470--		ALTER TABLE [Customer] Drop Column [SourceId] nvarchar(50) NULL;
3471
3472--		ALTER TABLE [User] Drop Column [SourceId] nvarchar(50) NULL;
3473
3474--		ALTER TABLE [Vendor] Drop Column [SourceId] nvarchar(50) NULL;
3475		
3476--		ALTER TABLE [InventoryItem] Drop Column [SourceId] nvarchar(50) NULL;
3477		
3478--		ALTER TABLE [InventoryItemStone] Drop Column [SourceId] nvarchar(50) NULL;
3479		
3480--		ALTER TABLE [Transaction] Drop Column [SourceId] nvarchar(50) NULL; 
3481
3482--		ALTER TABLE [GunLog]  Drop Column [SourceId] nvarchar(50) NULL;	
3483		
3484--		ALTER TABLE [TransactionCheckoutHistory]  Drop Column [SourceId] nvarchar(50) NULL;
3485		
3486--		ALTER TABLE [PoliceConfiscateDetail]  Drop Column [SourceId] nvarchar(50) NULL;
3487
3488--		ALTER TABLE [TransactionVersion]  Drop Column [SourceId] nvarchar(50) NULL;'
3489--	END
3490--GO
3491/****** Object:  StoredProcedure [dbo].[sp_DropStagingColumns]    Script Date: 6/13/2018 9:39:35 AM ******/
3492--SET ANSI_NULLS ON
3493--GO
3494--SET QUOTED_IDENTIFIER ON
3495--GO
3496
3497---- =============================================
3498---- Author:		Syed Mohsin
3499---- Create date: <8/4/2016>
3500---- Description:	<This sp will drop SourceId columns from tables>
3501---- Sample Call: EXEC [dbo].[sp_DropStagingColumns] 
3502---- =============================================
3503
3504--CREATE PROC [dbo].[sp_DropStagingColumns]
3505	
3506	
3507--AS
3508--	BEGIN
3509--	exec sp_execute_remote
3510--	N'Company', -- This is the external data source name…
3511--	N'ALTER TABLE [ItemType] Drop Column [SourceId;
3512	
3513--	ALTER TABLE [LookupValue] Drop Column [SourceId];
3514	
3515--	ALTER TABLE [Customer] Drop Column [SourceId];
3516	
3517--	ALTER TABLE [User] Drop Column [SourceId];
3518	
3519--	ALTER TABLE [Vendor] Drop Column [SourceId];
3520	
3521--	ALTER TABLE [InventoryItem] Drop Column [SourceId];
3522	
3523--	ALTER TABLE [InventoryItemStone] Drop Column [SourceId];
3524	
3525--	ALTER TABLE [Transaction] Drop Column [SourceId]; 
3526	
3527--	ALTER TABLE [GunLog]  Drop Column [SourceId];	
3528	
3529--	ALTER TABLE [TransactionCheckoutHistory]  Drop Column [SourceId];
3530	
3531--	ALTER TABLE [PoliceConfiscateDetail]  Drop Column [SourceId];
3532	
3533--	ALTER TABLE [TransactionVersion]  Drop Column [SourceId];'
3534
3535--	END
3536--GO
3537/****** Object:  StoredProcedure [dbo].[StoreBinExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
3538--SET ANSI_NULLS ON
3539--GO
3540--SET QUOTED_IDENTIFIER ON
3541--GO
3542
3543---- =============================================
3544---- Author:		Nadeem Akhtar
3545---- Create date: <8/11/2016>
3546---- Description:	<Extracts Bin values >
3547---- Sample Call: EXEC [dbo].[StoreBinExtract] 8
3548
3549---- Updated By: Syed Mohsin
3550---- Update date: <8/12/2016>
3551---- Comments: Corrected mapping for columns and Drop Columned join conditions to meet the design logic.
3552----			 Drop Columned Storeid parameter for Source StoreId  
3553---- =============================================
3554
3555--CREATE Proc [dbo].[StoreBinExtract]
3556--@StoreID int
3557--as
3558--BEGIN
3559--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
3560--select distinct 
3561	
3562--	LookupTypeID =
3563--		( 
3564--		select LookupTypeId from 
3565--		CEXT_LookupType s 
3566--		where LookupTypeName='Bin'
3567--		),
3568
3569--	cast(BIN as nvarchar(50))	as	value	,		
3570--	GetDate() as	CreatedDate,
3571--	GetDate() as	UpdatedDate,
3572--	1 as CreatedBy, --Drop Column in package
3573--	1 as UpdatedBy, --Drop Column in package
3574--	0 as IsDeleted,
3575--	1  as StoreId --Drop Column in package	
3576
3577-- from
3578--(
3579--SELECT BIN FROM
3580-- EXT_items s WHERE Sto_Pk = @StoreID
3581-- Union All
3582-- SELECT BIN FROM 
3583-- EXT_sitems s WHERE Sto_PK = @StoreID
3584-- ) a
3585-- WHERE Len(Bin) >0
3586
3587--END
3588--GO
3589/****** Object:  StoredProcedure [dbo].[StoreItemTypeMissingRecordsToInsert]    Script Date: 6/13/2018 9:39:35 AM ******/
3590--SET ANSI_NULLS ON
3591--GO
3592--SET QUOTED_IDENTIFIER ON
3593--GO
3594
3595
3596
3597--CREATE proc [dbo].[StoreItemTypeMissingRecordsToInsert]
3598--@SourceStoreId int, @TargetStoreId int
3599--as
3600--BEGIN
3601--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
3602
3603--select DISTINCT l1.lv1_pk as SourceId,
3604--		0 as ParentId,
3605--		0 as IsDeleted,
3606--		0 as IsPostToGunLog,
3607--		0 as IsHandGun,
3608--		0 as HoldDays,
3609--		0 as MinimumAge,
3610--		CAST(LTRIM(RTRIM(l1.DESCRIPT)) as nvarchar(15)) as NCICCode,
3611--		CAST(LTRIM(RTRIM(l1.DESCRIPT)) as nvarchar(15)) as LocalCode,
3612--		@TargetStoreId as Storeid,
3613--		GETDATE() as CreatedDate,
3614--		it.ItemTypeId,
3615--		l1.sto_pk
3616--from EXT_Level1 l1
3617--inner join EXT_items i on i.lv1_ID = l1.lv1_ID
3618--left join EXT_Level_N ln on ln.Lv_ID = l1.lv1_ID
3619--inner join CEXT_itemtype it on it.sourceid = l1.lv1_pk
3620--where ln.LVN_id IS NULL and i.Sto_Pk = @SourceStoreId
3621
3622--UNION
3623
3624--select DISTINCT l2.lv2_pk as SourceId,
3625--		l2.lv1_Parent as ParentId,
3626--		0 as IsDeleted,
3627--		0 as IsPostToGunLog,
3628--		0 as IsHandGun,
3629--		0 as HoldDays,
3630--		0 as MinimumAge,
3631--		cast(LTRIM(RTRIM(l2.DESCRIPT)) as nvarchar(15)) as NCICCode,
3632--		CAST(LTRIM(RTRIM(l2.DESCRIPT)) as nvarchar(15)) as LocalCode,
3633--		@TargetStoreId as Storeid,
3634--		GETDATE() as CreatedDate,
3635--		it.ItemTypeId,
3636--		l2.sto_pk 
3637--from EXT_Level2 l2
3638--inner join EXT_items i on i.lv2_ID = l2.lv2_ID
3639--left join EXT_Level_N ln on ln.Lv_ID = l2.lv2_ID
3640--inner join cEXT_itemtype it on it.sourceid = l2.lv2_pk
3641--where ln.LVN_id IS NULL and i.Sto_Pk = @SourceStoreId
3642
3643--UNION
3644
3645--select DISTINCT l3.lv3_pk as SourceId,
3646--		l3.lv2_Parent as ParentId,
3647--		0 as IsDeleted,
3648--		0 as IsPostToGunLog,
3649--		0 as IsHandGun,
3650--		0 as HoldDays,
3651--		0 as MinimumAge,
3652--		CAST(LTRIM(RTRIM(l3.DESCRIPT)) as nvarchar(15)) as NCICCode,
3653--		CAST(LTRIM(RTRIM(l3.DESCRIPT)) as nvarchar(15)) as LocalCode,
3654--		@TargetStoreId as Storeid,
3655--		GETDATE() as CreatedDate,
3656--		it.ItemTypeId,
3657--		l3.sto_pk 
3658--from EXT_Level3 l3
3659--inner join EXT_items i on i.lv3_ID = l3.lv3_ID
3660--left join EXT_Level_N ln on ln.Lv_ID = l3.lv3_ID
3661--inner join cEXT_itemtype it on it.sourceid = l3.lv3_pk
3662--where ln.LVN_id IS NULL and i.Sto_Pk = @SourceStoreId
3663
3664--UNION
3665
3666--select DISTINCT l4.lv4_PK as SourceId,
3667--		l4.lv3_Parent as ParentId,
3668--		0 as IsDeleted,
3669--		0 as IsPostToGunLog,
3670--		0 as IsHandGun,
3671--		0 as HoldDays,
3672--		0 as MinimumAge,
3673--		CAST(LTRIM(RTRIM(l4.DESCRIPT)) as nvarchar(15)) as NCICCode,
3674--		CAST(LTRIM(RTRIM(l4.DESCRIPT)) as nvarchar(15)) as LocalCode,
3675--		@TargetStoreId as Storeid,
3676--		GETDATE() as CreatedDate,
3677--		it.ItemTypeId,
3678--		l4.sto_pk
3679--from EXT_Level4 l4
3680--inner join EXT_items i on i.lv4_ID = l4.lv4_ID
3681--left join EXT_Level_N ln on ln.Lv_ID = l4.lv4_ID
3682--inner join EXT_itemtype it on it.sourceid = l4.lv4_pk
3683--where ln.LVN_id IS NULL and i.Sto_Pk = @SourceStoreId	
3684
3685--END
3686--GO
3687/****** Object:  StoredProcedure [dbo].[StoreItemTypeRecordsToInsert]    Script Date: 6/13/2018 9:39:35 AM ******/
3688--SET ANSI_NULLS ON
3689--GO
3690--SET QUOTED_IDENTIFIER ON
3691--GO
3692
3693
3694
3695--CREATE proc [dbo].[StoreItemTypeRecordsToInsert]
3696--@SourceStoreId int, @TargetStoreId int
3697--as
3698--BEGIN
3699--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
3700--Select 
3701--	t.ItemTypeId as ItemTypeId,
3702--	0 as IsDeleted,
3703--	@TargetStoreId as StoreID,
3704--	cast(Local_Code as nvarchar(15)) as LocalCode,
3705--	cast(NCIC_Code as nvarchar(15)) as NCICCode,
3706--	GetDate() as CreatedDate,
3707--	1 as CreatedBy, --Drop Column in package
3708--	GetDate() as UpdatedDate,
3709--	u.UserId as UpdatedBy,
3710--	Post2GunLog as IsPostToGunLog,
3711--	HandGun as IsHandGun,
3712--	HoldGunDays as HoldDays,
3713--	Min_Age as MinimumAge
3714
3715--from EXT_Level_N as LN 
3716--	inner join CEXT_ItemType t
3717--		on Ln.Level_FK = t.SourceID
3718--	left outer join CEXT_User u
3719--		on LN.LastUpdatedUSR_ID = u.SourceId 
3720--	WHERE LN.sto_pk = @SourceStoreId	
3721--END
3722--GO
3723/****** Object:  StoredProcedure [dbo].[SystemOptionsExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
3724--SET ANSI_NULLS ON
3725--GO
3726--SET QUOTED_IDENTIFIER ON
3727--GO
3728
3729--CREATE PROCEDURE [dbo].[SystemOptionsExtract] 
3730--	@SourceStoreId as int=0
3731--AS
3732--BEGIN
3733--	-- SET NOCOUNT ON Drop Columned to prevent extra result sets from
3734--	-- interfering with SELECT statements.
3735--	SET NOCOUNT ON;
3736--	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
3737
3738----Clear Staging table
3739--TRUNCATE TABLE Stg_SysOptions
3740
3741---- Select from SysInfo1
3742--INSERT INTO Stg_SysOptions   
3743--SELECT 
3744--CASE WHEN (SystemOptionKey= 'ExtendLay') THEN
3745--	SystemOptionKey+'_'+SystemOptionValue
3746--ELSE
3747--	SystemOptionKey
3748--END AS SystemOptionKey, SystemOptionValue
3749--FROM 
3750--(SELECT CAST(AllGunsAmt as varchar(100)) AS AllGunsAmt
3751--	,CAST(backpaymnt as varchar(100)) AS backpaymnt
3752--	,CAST(BuyHoldDay as varchar(100)) AS BuyHoldDay
3753--	,CAST(CntWeekend as varchar(100)) AS CntWeekend
3754--	,CAST(CtTaxRate as varchar(100)) AS CtTaxRate
3755--	,CAST(EncodeCost as varchar(100)) AS EncodeCost
3756--	,CAST(EntPawnDat as varchar(100)) AS EntPawnDat
3757--	,CAST(ExtendLay as varchar(100)) AS ExtendLay
3758--	,CAST(FedGunNum as varchar(100)) AS FedGunNum
3759--	,CAST(FuturePay as varchar(100)) AS FuturePay
3760--	,CAST(Gracedays as varchar(100)) AS Gracedays
3761--	,CAST(GunAmt as varchar(100)) AS GunAmt
3762--	,CAST(GunLog as varchar(100)) AS GunLog
3763--	,CAST(layawayday as varchar(100)) AS layawayday
3764--	,CAST(LayawayPct as varchar(100)) AS LayawayPct
3765--	,CAST(LayLabel as varchar(100)) AS LayLabel
3766--	,CAST(LcTaxRate as varchar(100)) AS LcTaxRate
3767--	,CAST(Losttikamt as varchar(100)) AS Losttikamt
3768--	,CAST(Losttikchg as varchar(100)) AS Losttikchg
3769--	,CAST(MinAge as varchar(100)) AS MinAge
3770--	,CAST(NonInvCost as varchar(100)) AS NonInvCost
3771--	,CAST(OverGunSal as varchar(100)) AS OverGunSal
3772--	,CAST(PartPay as varchar(100)) AS PartPay
3773--	,CAST(PawnDays as varchar(100)) AS PawnDays
3774--	,CAST(PawnRecpt as varchar(100)) AS PawnRecpt
3775--	,CAST(PoliceANum as varchar(100)) AS PoliceANum
3776--	,CAST(PoliceDept as varchar(100)) AS PoliceDept
3777--	,CAST(PricTagPaw as varchar(100)) AS PricTagPaw
3778--	,CAST(PrinLower as varchar(100)) AS PrinLower
3779--	,CAST(PrnSaleRec as varchar(100)) AS PrnSaleRec
3780--	,CAST(ProRepStrt as varchar(100)) AS ProRepStrt
3781--	,CAST(ReprntTick as varchar(100)) AS ReprntTick
3782--	,CAST(StTaxRate as varchar(100)) AS StTaxRate
3783--	,CAST(SamPawnPur as varchar(100)) AS SamPawnPur
3784--	,CAST(PERIOD as varchar(100)) AS Period
3785--FROM EXT_SysInfo1 WHERE STO_PK=@SourceStoreId
3786--)si1
3787--unpivot
3788--(
3789--SystemOptionValue for SystemOptionKey IN
3790--(
3791--	AllGunsAmt
3792--	,backpaymnt
3793--	,BuyHoldDay
3794--	,CntWeekend
3795--	,CtTaxRate
3796--	,EncodeCost
3797--	,EntPawnDat
3798--	,ExtendLay
3799--	,FedGunNum
3800--	,FuturePay
3801--	,Gracedays
3802--	,GunAmt
3803--	,GunLog
3804--	,layawayday
3805--	,LayawayPct
3806--	,LayLabel
3807--	,LcTaxRate
3808--	,Losttikamt
3809--	,Losttikchg
3810--	,MinAge
3811--	,NonInvCost
3812--	,OverGunSal
3813--	,PartPay
3814--	,PawnDays
3815--	,PawnRecpt
3816--	,PoliceANum
3817--	,PoliceDept
3818--	,PricTagPaw
3819--	,PrinLower
3820--	,PrnSaleRec
3821--	,ProRepStrt
3822--	,ReprntTick
3823--	,StTaxRate
3824--	,SamPawnPur
3825--	,Period
3826--)
3827--) unpiv;
3828
3829---- Select from SysInfo2
3830--INSERT INTO Stg_SysOptions
3831--SELECT 
3832--CASE WHEN (SystemOptionKey= 'DepositTax') THEN
3833--	SystemOptionKey+'_'+SystemOptionValue
3834--ELSE
3835--	SystemOptionKey
3836--END AS SystemOptionKey,
3837
3838--CASE WHEN (SystemOptionKey= 'DepositTax')
3839--THEN 'True'
3840--ELSE SystemOptionValue
3841--END AS SystemOptionValue
3842
3843--FROM 
3844--(SELECT CAST(HandGunPwn as varchar(100)) AS HandGunPwn
3845--,CAST(HandGunDPW as varchar(100)) AS HandGunDPW
3846--,CAST(CustCred as varchar(100)) AS CustCred
3847--,CAST(GoldWeight as varchar(100)) AS GoldWeight
3848--,CAST(GoldWtSale as varchar(100)) AS GoldWtSale
3849--,CAST(PoliceAmt as varchar(100)) AS PoliceAmt
3850--,CAST(PoliceEmployer as varchar(100)) AS PoliceEmployer
3851--,CAST(RingLabel as varchar(100)) AS RingLabel
3852--,CAST(EatTax as varchar(100)) AS EatTax
3853--,CAST(DepositTax as varchar(100)) AS DepositTax
3854--,CAST(monthchrg as varchar(100)) AS monthchrg
3855--,CAST(PawnDaysG as varchar(100)) AS PawnDaysG
3856--,CAST(PawnDaysJ as varchar(100)) AS PawnDaysJ
3857--,CAST(BuyHoldG as varchar(100)) AS BuyHoldG
3858--,CAST(BuyHoldJ as varchar(100)) AS BuyHoldJ
3859--,CAST(PullGrace as varchar(100)) AS PullGrace
3860--,CAST(PawnBuyNum as varchar(100)) AS PawnBuyNum
3861--,CAST(PawnBuyDat as varchar(100)) AS PawnBuyDat
3862--,CAST(ScrapOther as varchar(100)) AS ScrapOther
3863--,CAST(PayRepawn as varchar(100)) AS PayRepawn
3864--,CAST(NewTickNum as varchar(100)) AS NewTickNum
3865--,CAST(PawRecBlan as varchar(100)) AS PawRecBlan
3866--,CAST(Prorate as varchar(100)) AS Prorate
3867--,CAST(ProUpToDay as varchar(100)) AS ProUpToDay
3868--,CAST(TexasMinPr as varchar(100)) AS TexasMinPr
3869--,CAST(ProratSChg as varchar(100)) AS ProratSChg
3870--,CAST(ProStart as varchar(100)) AS ProStart
3871--,CAST(RateOnePer as varchar(100)) AS RateOnePer
3872--,CAST(RateOnePro as varchar(100)) AS RateOnePro
3873--,CAST(RateFeePer as varchar(100)) AS RateFeePer
3874--,CAST(RateFeePro as varchar(100)) AS RateFeePro
3875--,CAST(SaleEnCost as varchar(100)) AS SaleEnCost
3876--,CAST(EncodeCent as varchar(100)) AS EncodeCent
3877--,CAST(EncodeDorp as varchar(100)) AS EncodeDorp
3878--,CAST(EncodeRept as varchar(100)) AS EncodeRept
3879--,CAST(FirearmFeeRedeemOnly as varchar(100)) AS FirearmFeeRedeemOnly
3880--FROM EXT_SysInfo2 WHERE STO_PK=@SourceStoreId
3881--)si1
3882--unpivot
3883--	(SystemOptionValue for SystemOptionKey IN
3884--		(HandGunPwn
3885--		,HandGunDPW
3886--		,CustCred
3887--		,GoldWeight
3888--		,GoldWtSale
3889--		,PoliceAmt
3890--		,PoliceEmployer
3891--		,RingLabel
3892--		,EatTax
3893--		,DepositTax
3894--		,monthchrg
3895--		,PawnDaysG
3896--		,PawnDaysJ
3897--		,BuyHoldG
3898--		,BuyHoldJ
3899--		,PullGrace
3900--		,PawnBuyNum
3901--		,PawnBuyDat
3902--		,ScrapOther
3903--		,PayRepawn
3904--		,NewTickNum
3905--		,PawRecBlan
3906--		,Prorate
3907--		,ProUpToDay
3908--		,TexasMinPr
3909--		,ProratSChg
3910--		,ProStart
3911--		,RateOnePer
3912--		,RateOnePro
3913--		,RateFeePer
3914--		,RateFeePro
3915--		,SaleEnCost
3916--		,EncodeCent
3917--		,EncodeDorp
3918--		,EncodeRept
3919--		,FirearmFeeRedeemOnly)
3920--	) unpiv;
3921
3922
3923---- Select from SysInfo4
3924--INSERT INTO Stg_SysOptions
3925--SELECT 
3926--CASE WHEN (SystemOptionKey IN ('JewLabelType','EncCostMinInvLbl','InvLabelDate','PLblDateIO','LayCred')) THEN
3927--	SystemOptionKey+'_'+SystemOptionValue
3928--ELSE
3929--	SystemOptionKey
3930--END AS SystemOptionKey, SystemOptionValue
3931--FROM 
3932--	(SELECT CAST(Auto_4473 as varchar(100)) AS Auto_4473
3933--		,CAST(GunHoldLbl as varchar(100)) AS GunHoldLbl
3934--		,CAST(JewLabelType as varchar(100)) AS JewLabelType
3935--		,CAST(PrtPettyCashReceipt as varchar(100)) AS PrtPettyCashReceipt
3936--		,CAST(EncCostMinInvLbl as varchar(100)) AS EncCostMinInvLbl
3937--		,CAST(InvLabelDate as varchar(100)) AS InvLabelDate
3938--		,CAST(InvLabelYear2 as varchar(100)) AS InvLabelYear2
3939--		,CAST(APRFirearm as varchar(100)) AS APRFirearm
3940--		,CAST(GunFeeAmt as varchar(100)) AS GunFeeAmt
3941--		,CAST(RetSvcChg_FL as varchar(100)) AS RetSvcChg_FL
3942--		,CAST(SerialNumberCheck as varchar(100)) AS SerialNumberCheck
3943--		,CAST(ScrapJewelry as varchar(100)) AS ScrapJewelry
3944--		,CAST(SplitJewelry as varchar(100)) AS SplitJewelry
3945--		,CAST(SplitOther as varchar(100)) AS SplitOther
3946--		,CAST(chgcustonpay as varchar(100)) AS chgcustonpay
3947--		,CAST(PawnPayDays as varchar(100)) AS PawnPayDays
3948--		,CAST(ResetOTFonNewTic as varchar(100)) AS ResetOTFonNewTic
3949--		,CAST(TXMemorandum as varchar(100)) AS TXMemorandum
3950--		,CAST(PawnPayTag as varchar(100)) AS PawnPayTag
3951--		,CAST(PrtLgTicketNum as varchar(100)) AS PrtLgTicketNum
3952--		,CAST(PLblDateIO as varchar(100)) AS PLblDateIO
3953--		,CAST(LostTickRcpt as varchar(100)) AS LostTickRcpt
3954--		,CAST(LostTickPaymentRcpt as varchar(100)) AS LostTickPaymentRcpt
3955--		,CAST(PawnPayBlank as varchar(100)) AS PawnPayBlank
3956--		,CAST(AskPawnRcpt as varchar(100)) AS AskPawnRcpt
3957--		,CAST(EncCostPawnBuyLbl as varchar(100)) AS EncCostPawnBuyLbl
3958--		,CAST(ProRt as varchar(100)) AS ProRt
3959--		,CAST(TexasDailyProrate as varchar(100)) AS TexasDailyProrate
3960--		,CAST(LayDayExtn as varchar(100)) AS LayDayExtn
3961--		,CAST(LayCred as varchar(100)) AS LayCred
3962--		,CAST(Form8300Amount as varchar(100)) as Form8300Amount
3963--		,CAST(GunFeeEachPeriod as varchar(100)) as GunFeeEachPeriod
3964--	FROM EXT_SysInfo4 WHERE STO_PK=@SourceStoreId
3965--	)si1
3966--		unpivot
3967--			(SystemOptionValue for SystemOptionKey IN
3968--				(Auto_4473
3969--				,GunHoldLbl
3970--				,JewLabelType
3971--				,PrtPettyCashReceipt
3972--				,EncCostMinInvLbl
3973--				,InvLabelDate
3974--				,InvLabelYear2
3975--				,APRFirearm
3976--				,GunFeeAmt
3977--				,RetSvcChg_FL
3978--				,SerialNumberCheck
3979--				,ScrapJewelry
3980--				,SplitJewelry
3981--				,SplitOther
3982--				,chgcustonpay
3983--				,PawnPayDays
3984--				,ResetOTFonNewTic
3985--				,TXMemorandum
3986--				,PawnPayTag
3987--				,PrtLgTicketNum
3988--				,PLblDateIO
3989--				,LostTickRcpt
3990--				,LostTickPaymentRcpt
3991--				,PawnPayBlank
3992--				,AskPawnRcpt
3993--				,EncCostPawnBuyLbl
3994--				,ProRt
3995--				,TexasDailyProrate
3996--				,LayDayExtn
3997--				,LayCred
3998--				,Form8300Amount
3999--				,GunFeeEachPeriod)
4000--			) unpiv;
4001
4002---- Select from Sy5_sysinfo5 This is an exceptional case for SYS_LabelPull mapping
4003--INSERT INTO Stg_SysOptions
4004--SELECT 
4005--	RTrim(Sys5.sy5_Lookup) + '_' + CAST(Convert(decimal(1,0), (LTrim(Sys5.sy5_value))) as varchar) AS SystemOptionKey, 
4006--Sys5.sy5_value as SystemOptionValue 
4007--FROM EXT_sy5_SysInfo5 Sys5 WHERE Sys5.sy5_Lookup='SYS_LabelPull' AND Sys5.sto_pk=@SourceStoreId
4008
4009---- Select from SS_PAWN
4010--INSERT INTO Stg_SysOptions
4011--SELECT SystemOptionKey, SystemOptionValue
4012--FROM 
4013--	(SELECT CAST(TicketNum as varchar(100)) AS TicketNum		
4014--	FROM EXT_SS_PAWN WHERE STO_PK=@SourceStoreId
4015--	)si1
4016--		unpivot
4017--			(SystemOptionValue for SystemOptionKey IN
4018--				(TicketNum)
4019--			) unpiv;
4020
4021---- Select from SS_SALE
4022--INSERT INTO Stg_SysOptions
4023--SELECT SystemOptionKey, SystemOptionValue
4024--FROM 
4025--	(SELECT CAST(ReceiptNum as varchar(100)) AS ReceiptNum		
4026--	FROM EXT_SS_SALE WHERE STO_PK=@SourceStoreId
4027--	)si1
4028--		unpivot
4029--			(SystemOptionValue for SystemOptionKey IN
4030--				(ReceiptNum)
4031--			) unpiv;
4032
4033---- Select from SS_INVENTNEW
4034--INSERT INTO Stg_SysOptions
4035--SELECT SystemOptionKey, SystemOptionValue
4036--FROM 
4037--	(SELECT CAST(InvNumNew as varchar(100)) AS InvNumNew		
4038--	FROM EXT_SS_INVENTNEW WHERE STO_PK=@SourceStoreId
4039--	)si1
4040--		unpivot
4041--			(SystemOptionValue for SystemOptionKey IN
4042--				(InvNumNew)
4043--			) unpiv;
4044
4045---- Select from SS_PURCHAS
4046--INSERT INTO Stg_SysOptions
4047--SELECT SystemOptionKey, SystemOptionValue
4048--FROM 
4049--	(SELECT CAST(PurchasNum as varchar(100)) AS PurchasNum		
4050--	FROM EXT_SS_PURCHAS WHERE STO_PK=@SourceStoreId
4051--	)si1
4052--		unpivot
4053--			(SystemOptionValue for SystemOptionKey IN
4054--				(PurchasNum)
4055--			) unpiv;
4056
4057
4058---- Select from Contract
4059--INSERT INTO Stg_SysOptions
4060--SELECT SystemOptionKey, SystemOptionValue
4061--FROM 
4062--	(SELECT CAST(EXT_Contract.SalesMsg as varchar(100)) AS SalesMsg,
4063--			CAST(EXT_Contract.LayawayMsg as varchar(100)) AS LayawayMsg
4064--	FROM EXT_Contract  WHERE EXT_Contract.sto_FK=@SourceStoreId
4065--	)si1
4066--		unpivot
4067--			(SystemOptionValue for SystemOptionKey IN
4068--				(SalesMsg,
4069--				LayawayMsg)
4070--			) unpiv;
4071
4072
4073
4074---- Select from Sy5_sysinfo5
4075--SELECT Lkp.SourceKey, Lkp.TargetKey, so.ControlTypeId,
4076-- CASE WHEN so.ControlTypeId in (5,6) and LTrim(RTrim(Sys5.sy5_value)) = '0' THEN 'False'
4077--	  WHEN so.ControlTypeId in (5,6) and LTrim(RTrim(Sys5.sy5_value)) != '0' and lkp.TargetKey !='PAWN_OTHER_DAILY_SVCCHARG_TO_PRORATE' THEN 'True'
4078--	  WHEN so.ControlTypeId = 11 and LTrim(RTrim(Sys5.sy5_value)) = 'G' THEN '206'
4079--	  WHEN so.ControlTypeId = 11 and LTrim(RTrim(Sys5.sy5_value)) = 'O' THEN '207'
4080--	  WHEN so.ControlTypeId = 11 and LTrim(RTrim(Sys5.sy5_value)) = 'D' THEN '205'
4081--	  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))
4082--	  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'
4083--	  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'
4084--	 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'
4085--	   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'
4086--	 --WHEN lkp.TargetKey LIKE 'GENERAL_SALES_TAX_COLLECT_LAYAWAY_AT_END' AND LTrim(RTrim(Sys5.sy5_value)) = '0' THEN 'False' 
4087--	  --WHEN lkp.TargetKey LIKE 'GENERAL_SALES_TAX_COLLECT_LAYAWAY_AT_END' AND LTrim(RTrim(Sys5.sy5_value)) != '0' THEN 'True'
4088--	 WHEN lkp.TargetKey LIKE 'PAWN_OTHER_DAILY_MLA_RATE' AND LTrim(RTrim(Sys5.sy5_value)) = '0' THEN 'False' 
4089--	  WHEN lkp.TargetKey LIKE 'PAWN_OTHER_DAILY_MLA_RATE' AND LTrim(RTrim(Sys5.sy5_value)) != '0' THEN 'True' 
4090--	  ELSE LTrim(RTrim(Sys5.sy5_value))
4091--	  END
4092-- as SystemOptionValue, so.SystemOptionId
4093--FROM [Common].[SourceTargetKeyMapping] Lkp 
4094--INNER JOIN EXT_sy5_SysInfo5 Sys5 ON Lkp.[SourceKey] = Sys5.sy5_Lookup AND Sys5.sto_pk=@SourceStoreId
4095--INNER JOIN CEXT_SystemOption so ON Lkp.TargetKey = so.SystemOptionKey
4096--INNER JOIN cEXT_ControlType ct ON so.ControlTypeId = ct.ControlTypeId
4097--WHERE Lkp.SourceTable = 'sy5_SysInfo5' 
4098--UNION
4099----Final select for mapping
4100--SELECT opt.SystemOptionKey AS SourceKey, Lkp.TargetKey, so.ControlTypeId,
4101-- CASE WHEN so.ControlTypeId in (5,6) and LTrim(RTrim(opt.SystemOptionValue)) = '0' THEN 'False'
4102--	  WHEN so.ControlTypeId in (5,6) and LTrim(RTrim(opt.SystemOptionValue)) != '0' THEN 'True'
4103--	  WHEN so.ControlTypeId = 11 and LTrim(RTrim(opt.SystemOptionValue)) = 'G' THEN '206'
4104--	  WHEN so.ControlTypeId = 11 and LTrim(RTrim(opt.SystemOptionValue)) = 'O' THEN '207'
4105--	  WHEN so.ControlTypeId = 11 and LTrim(RTrim(opt.SystemOptionValue)) = 'D' THEN '205'
4106--	  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))
4107--	  	  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'
4108--	  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'
4109--	  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'
4110--	   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'
4111--	 -- WHEN lkp.TargetKey LIKE 'GENERAL_SALES_TAX_COLLECT_LAYAWAY_AT_END' AND LTrim(RTrim(opt.SystemOptionValue)) = '0' THEN 'False' 
4112--	 -- WHEN lkp.TargetKey LIKE 'GENERAL_SALES_TAX_COLLECT_LAYAWAY_AT_END' AND LTrim(RTrim(opt.SystemOptionValue)) != '0' THEN 'True'
4113--	  ELSE LTrim(RTrim(opt.SystemOptionValue))
4114--	  END
4115-- AS SystemOptionValue, so.SystemOptionId 
4116--FROM Stg_SysOptions opt
4117--INNER JOIN Common.[SourceTargetKeyMapping] Lkp ON opt.SystemOptionKey = Lkp.[SourceKey]
4118--INNER JOIN CEXT_SystemOption so ON Lkp.TargetKey = so.SystemOptionKey
4119--INNER JOIN CEXT_ControlType ct ON so.ControlTypeId = ct.ControlTypeId
4120
4121--END
4122--GO
4123/****** Object:  StoredProcedure [dbo].[SystemOptionsUpdate]    Script Date: 6/13/2018 9:39:35 AM ******/
4124SET ANSI_NULLS ON
4125GO
4126SET QUOTED_IDENTIFIER ON
4127GO
4128
4129
4130-- =============================================
4131-- Author:		Farrukh Ijaz
4132-- Create date: <9/01/2016>
4133-- Description:	<Update system option values in target store> 
4134-- =============================================
4135CREATE PROCEDURE [dbo].[SystemOptionsUpdate] 
4136	-- Drop Column the parameters for the stored procedure here
4137	@SystemOptionId as int
4138	,@StoreId as int
4139	,@SystemOptionValue varchar(2000)
4140	,@UpdatedDate as datetime
4141	,@UpdatedBy as int
4142
4143AS
4144BEGIN
4145
4146    -- Insert statements for procedure here
4147	UPDATE Company.dbo.StoreSystemOption SET 
4148		SystemOptionValue = @SystemOptionValue,
4149		UpdatedBy = @UpdatedBy,
4150		UpdatedDate = @UpdatedDate
4151	WHERE SystemOptionId = @SystemOptionId
4152	AND StoreId = @StoreId
4153	--PRINT 1
4154END
4155GO
4156/****** Object:  StoredProcedure [dbo].[TenderExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
4157--SET ANSI_NULLS ON
4158--GO
4159--SET QUOTED_IDENTIFIER ON
4160--GO
4161
4162---- =============================================
4163---- Author:		Syed Mohsin
4164---- Create date: <10/25/2016>
4165---- Description:	<Extracts data for ApplicationLookup Tender Types only>
4166---- Sample Call: EXEC [dbo].[TenderExtract] 8 , 180
4167---- =============================================
4168
4169--CREATE Proc [dbo].[TenderExtract]
4170--@StoreID int, @TargetStoreId int
4171--as
4172--BEGIN
4173--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
4174
4175--DECLARE @LookupTypeId int
4176--SET @LookupTypeId = (SELECT LookupTypeId FROM CEXT_LookupType where LookupTypeName = 'Tender Type')
4177
4178--  SELECT
4179--	 c.sto_pk sto,
4180--	  @LookupTypeId as LookupTypeId,
4181--	  CAST(RTRIM(LTRIM(c.lc_Descript)) as nvarchar(50)) lc_Descript,
4182--	  c.lc_pk  as SourceId,
4183--	  1 as IsNew,
4184--	  GetDate() as CreatedDate,
4185--	  GetDate() as UpdatedDate,
4186--	1 as CreatedBy, --Drop Column in package
4187--	u.UserId as UpdatedBy,
4188--	  0 as IsDeleted
4189	
4190--	FROM EXT_Lookup_C  c
4191--inner join EXT_Lookup_B b on b.lb_pk = c.LB_FK and b.lb_type='T'
4192--inner join EXT_Lookup_N n on n.lc_FK = c.lc_pk and n.sto_pk = @StoreID
4193--LEFT JOIN CEXT_User u on u.SourceId = c.LastUpdatedUSR_ID
4194--LEFT JOIN CEXT_LookupValue lv on lv.Value = c.lc_Descript 
4195--where n.Sto_PK = @StoreID and lv.LookupValueId IS NULL
4196
4197--END
4198--GO
4199/****** Object:  StoredProcedure [dbo].[TransactionCheckoutBatchExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
4200--SET ANSI_NULLS ON
4201--GO
4202--SET QUOTED_IDENTIFIER ON
4203--GO
4204
4205---- =============================================
4206---- Author:		<Farrukh Ijaz>
4207---- Create date: <09/12/2016>
4208---- Description:	<Extract Checkout Batch data from Acct table>
4209---- Sample Call: EXEC [dbo].[TransactionCheckoutBatchExtract] 8, 5
4210---- =============================================
4211--CREATE PROCEDURE [dbo].[TransactionCheckoutBatchExtract]
4212--	-- Drop Column the parameters for the stored procedure here
4213--	@SourceStoreId as int, @UtcTime int
4214--AS
4215--BEGIN
4216--	-- SET NOCOUNT ON Drop Columned to prevent extra result sets from
4217--	-- interfering with SELECT statements.
4218--	SET NOCOUNT ON;
4219--	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
4220
4221--SELECT 
4222--	tp.TransactionId
4223--	,tch.TransactionCheckoutHistoryID
4224--	,DATEADD(HOUR,@UtcTime,DATEin)AS CreatedDate
4225--	,tch.CreatedBy AS CreatedBy
4226--	,tp.TransactionPaymentId
4227--FROM EXT_Acct a
4228--INNER JOIN CEXT_TransactionPayment tp ON tp.PaymentTypeId not in (23,14) and a.Acct_PK=tp.SourceId
4229--INNER JOIN CEXT_TransactionCheckoutHistory tch ON a.Acct_PK = tch.SourceId
4230--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')
4231
4232--AND a.sto_pk=@SourceStoreId
4233
4234
4235--END
4236--GO
4237/****** Object:  StoredProcedure [dbo].[TransactionCheckoutHistoryExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
4238--SET ANSI_NULLS ON
4239--GO
4240--SET QUOTED_IDENTIFIER ON
4241--GO
4242
4243---- =============================================
4244---- Author:		<Farrukh Ijaz>
4245---- Create date: <09/12/2016>
4246---- Description:	<Extract Checkout History data from Acct table>
4247---- Sample Call: EXEC [dbo].[TransactionCheckoutHistoryExtract] 8, 5
4248---- =============================================
4249--CREATE PROCEDURE [dbo].[TransactionCheckoutHistoryExtract]
4250--	-- Drop Column the parameters for the stored procedure here
4251--	@SourceStoreId as int, @UtcTime int
4252--AS
4253--BEGIN
4254--	-- SET NOCOUNT ON Drop Columned to prevent extra result sets from
4255--	-- interfering with SELECT statements.
4256--	SET NOCOUNT ON;
4257--	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
4258
4259--DECLARE @tender int
4260--SET @tender = (select LookupTypeId from CEXT_LookupType where LookupTypeName='Tender Type')
4261
4262--SELECT 
4263--	TENDERTYP1
4264--	--,tt1.lc_Descript
4265--	,lv1.LookupValueId AS TenderTypeId1
4266--	,TENDERTYP2
4267--	--,tt2.lc_Descript
4268--	,lv2.LookupValueId AS TenderTypeId2
4269--	,TENDERAMT1
4270--	,TENDERAMT2
4271--	,a.AMOUNT+STATETAX+COUNTYTAX+LOCALTAX as PaymentAmount
4272--	,Cus_Credit as CustomerCredit 
4273--	,TENDCHANGE
4274--	,DATEADD(HOUR,@UtcTime,DATEin) AS CreatedDate
4275--	,u.UserId AS CreatedBy
4276--	,Acct_PK AS SourceId
4277--FROM EXT_Acct a
4278----LEFT JOIN Company.dbo.[User] u ON a.CUS_FK=u.SourceId
4279--LEFT JOIN EXT_users us
4280--on us.USR_PK = a.Usr_FK and us.USR_STORE = @SourceStoreId
4281--LEFT JOIN CEXT_User u
4282--on u.SourceId = us.USR_ID
4283
4284
4285--LEFT JOIN EXT_Lookup_C tt1 ON a.TENDERTYP1 = tt1.lc_pk
4286--LEFT JOIN EXT_Lookup_C tt2 ON a.TENDERTYP2 = tt2.lc_pk	 
4287--LEFT JOIN CEXT_LookupValue lv1 ON tt1.lc_Descript = lv1.Value and lv1.LookupTypeId = @tender
4288--LEFT JOIN CEXT_LookupValue lv2 ON tt2.lc_Descript = lv2.Value and lv2.LookupTypeId = @tender
4289--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')
4290
4291--AND a.sto_pk=@SourceStoreId
4292
4293
4294--END
4295--GO
4296/****** Object:  StoredProcedure [dbo].[TransactionCheckoutPaymentHistoryExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
4297--SET ANSI_NULLS ON
4298--GO
4299--SET QUOTED_IDENTIFIER ON
4300--GO
4301
4302---- =============================================
4303---- Author:		<Farrukh Ijaz>
4304---- Create date: <09/12/2016>
4305---- Description:	<Extract TransactionCheckoutPaymentHistory data from Acct table>
4306---- Sample Call: EXEC [dbo].[TransactionCheckoutPaymentHistoryExtract] 8
4307---- =============================================
4308--CREATE PROCEDURE [dbo].[TransactionCheckoutPaymentHistoryExtract]
4309--	-- Drop Column the parameters for the stored procedure here
4310--	@SourceStoreId as int, @UtcTime int
4311--AS
4312--BEGIN
4313--	-- SET NOCOUNT ON Drop Columned to prevent extra result sets from
4314--	-- interfering with SELECT statements.
4315--	SET NOCOUNT ON;
4316--	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
4317
4318--SELECT 
4319--	t.TransactionID	
4320--	,t.TransactionTypeId
4321--	,tp.TransactionPaymentId
4322--	,tcb.TransactionCheckoutBatchID
4323--	,tch.TransactionCheckoutHistoryID
4324--	,1 AS BatchId
4325--	,DATEADD(HOUR,@UtcTime,tch.CreatedDate) AS CreatedDate
4326--	,tch.CreatedBy
4327--FROM EXT_Acct a
4328--LEFT JOIN CEXT_TransactionPayment tp ON a.Acct_PK= tp.SourceId
4329--INNER JOIN CEXT_Transaction t on 
4330--t.TicketNumber = a.TICKETNUM
4331--AND ( 
4332--		(t.TransactionTypeId in (1,2) and  (a.TYPE like 'P%' OR a.TYPE like 'B%')) 
4333--		OR 
4334--		(t.TransactionTypeId in (3,5) and a.TYPE like 'S%')
4335--	)
4336--INNER JOIN CEXT_TransactionCheckoutHistory tch ON tp.PaymentTypeId not in (23,14) and a.Acct_PK = tch.SourceId
4337--INNER JOIN CEXT_TransactionCheckoutBatch tcb ON tch.TransactionCheckoutHistoryID = tcb.TransactionCreditHistoryID
4338--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')
4339
4340
4341--AND a.sto_pk=@SourceStoreId
4342
4343
4344--END
4345--GO
4346/****** Object:  StoredProcedure [dbo].[TransactionItemVersionExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
4347--SET ANSI_NULLS ON
4348--GO
4349--SET QUOTED_IDENTIFIER ON
4350--GO
4351
4352---- =============================================
4353---- Author:		<Syed Mohsin>
4354---- Create date: <09/26/2016>
4355---- Description:	<Extract TransactionItemVersions by extracting data from source>
4356---- Sample Call: EXEC [dbo].[TransactionItemVersionExtract] 371
4357---- =============================================
4358--CREATE PROCEDURE [dbo].[TransactionItemVersionExtract]
4359--	-- Drop Column the parameters for the stored procedure here
4360--	@TargetStoreId as int
4361--AS
4362--BEGIN
4363--	-- SET NOCOUNT ON Drop Columned to prevent extra result sets from
4364--	-- interfering with SELECT statements.
4365--	SET NOCOUNT ON;
4366--	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
4367
4368----	DECLARE @myTable as TABLE
4369----(
4370----	InventoryItemVersionId  int,
4371----	InventoryItemId int
4372----)
4373----INSERT INTO @myTable
4374----select MAX(InventoryItemVersionId), InventoryItemId from Company.dbo.InventoryItemVersion group by InventoryItemId
4375
4376--SELECT *,iif(Num1 = 1,Stat, 
4377--CASE trStat
4378--WHEN  23 THEN 1
4379--WHEN  26 THEN 6
4380--WHEN  22 THEN 7
4381--WHEN  5 THEN 1
4382--WHEN  4 THEN 4
4383--WHEN  21 THEN 6
4384--WHEN  45 THEN 7
4385--WHEN  15 THEN 15
4386--WHEN  22 THEN 22
4387--WHEN  2 THEN CASE transactiontypeid WHEN 3 THEN 19 WHEN 5 THEN 2 END
4388--ELSE stat
4389--END
4390
4391--) StatusId
4392
4393--FROM (
4394--select tv.TransactionVersionId,
4395--			--iv.InventoryItemVersionId,
4396--			iif(tr.TransactionTypeId IN (1,2),NULL,s.Quantity) as OrigQuantity,
4397--			s.TransactionItemId,
4398--			s.TransactionId,
4399--			s.statusid as Stat,
4400--			s.InventoryItemLotId,
4401--			s.Cost,
4402--			s.Quantity,
4403--			s.Comment,
4404--			s.DiscountPercentage,
4405--			s.IsTaxExempt,
4406--			s.Resale,
4407--			s.NonInventoryItemDescription,
4408--			s.NonInventoryNumber,
4409--			tv.CreatedDate,
4410--			tv.CreatedBy,
4411--			s.JewelryWeightUnitId,
4412--			s.BinNumberId,
4413--			s.Tax,
4414--			s.GunProcessingFee,
4415--			tr.statusid as TrStat,
4416--			tr.transactiontypeid,
4417--			ROW_NUMBER() over(partition by tv.TransactionId order by tv.transactionversionid desc)as Num1 
4418--			from CEXT_TransactionItem s
4419--			inner join CEXT_Transaction tr on tr.TransactionId = s.TransactionId
4420--			left join  CEXT_TransactionVersion tv on tv.TransactionId = s.TransactionId
4421--			left join CEXT_InventoryItemLot il on il.InventoryItemLotId = s.InventoryItemLotId
4422--			--left join @myTable iv on iv.InventoryItemId = il.InventoryItemId
4423--			WHERE tr.StoreId=@TargetStoreId
4424--			)q
4425
4426--END
4427--GO
4428/****** Object:  StoredProcedure [dbo].[TransactionPawnBuyVersionExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
4429--SET ANSI_NULLS ON
4430--GO
4431--SET QUOTED_IDENTIFIER ON
4432--GO
4433
4434---- =============================================
4435---- Author:		<Syed Mohsin>
4436---- Create date: <09/21/2017>
4437---- Description:	<Extract History Transaction Versions for Pawns converted to buy>
4438---- Sample Call: EXEC [dbo].[TransactionPawnBuyVersionExtract] 182 , 1 , 5
4439---- =============================================
4440--CREATE PROCEDURE [dbo].[TransactionPawnBuyVersionExtract]
4441--	-- Drop Column the parameters for the stored procedure here
4442--	@TargetStoreId as int, @SourceStoreId as int, @UtcOffset as int
4443--AS
4444--BEGIN
4445--	-- SET NOCOUNT ON Drop Columned to prevent extra result sets from
4446--	-- interfering with SELECT statements.
4447--	SET NOCOUNT ON;
4448--	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
4449
4450--SELECT   t.TransactionId ,
4451--        1 TransactionTypeId,
4452--        t.StoreId ,
4453--        t.CustomerId ,
4454--        t.TicketNumber ,
4455--        t.Duration ,
4456--        t.Period ,
4457--        DATEADD(HOUR,@UtcOffset, CONVERT(DATETIME, a.DATEin)) InDate,
4458--        t.OutDate ,
4459--        a.AMOUNT * -1 as Amount,
4460--        t.RateTableId ,
4461--        t.Note ,
4462--        t.Message ,
4463--        CASE WHEN a.TYPE='P' THEN 23 WHEN a.TYPE = 'PV' THEN 26 END as StatusId,
4464--        0 IsActive,
4465--        0 IsDeleted,
4466--        0 IsQueued,
4467--        DATEADD(HOUR,@UtcOffset, CONVERT(DATETIME, a.DATEin)) CreatedDate,
4468--        t.CreatedBy
4469--FROM    EXT_Acct a
4470--       INNER JOIN CEXT_Transaction t ON t.TicketNumber = a.TICKETNUM
4471--                                     AND t.TransactionTypeId = 2
4472       
4473--WHERE   TYPE = 'P' OR TYPE = 'PV' AND StoreId=@TargetStoreId
4474--        AND TICKETNUM IN ( SELECT   TICKETNUM
4475--                           FROM     EXT_Acct
4476--                           WHERE    TYPE = 'B' and sto_pk=@SourceStoreId)
4477
4478--ORDER BY a.Acct_PK ASC
4479
4480--END
4481--GO
4482/****** Object:  StoredProcedure [dbo].[TransactionPaymentBuyExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
4483--SET ANSI_NULLS ON
4484--GO
4485--SET QUOTED_IDENTIFIER ON
4486--GO
4487
4488---- =============================================
4489---- Author:		<Syed Mohsin>
4490---- Create date: <09/14/2017>
4491---- Description:	<Extract TransactionPayment data for pawns converted into buys>
4492---- Sample Call: EXEC [dbo].[TransactionPaymentBuyExtract] 1 , 5
4493
4494---- =============================================
4495--CREATE PROCEDURE [dbo].[TransactionPaymentBuyExtract]
4496--	-- Drop Column the parameters for the stored procedure here
4497--	@SourceStoreId as int, @UtcTime int
4498--AS
4499--BEGIN
4500--	-- SET NOCOUNT ON Drop Columned to prevent extra result sets from
4501--	-- interfering with SELECT statements.
4502--	SET NOCOUNT ON;
4503--	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
4504
4505
4506--DECLARE @BatchId int
4507--SET @BatchId = (SELECT ISNULL(MAX(batchid),0) from CEXT_TransactionPayment)
4508
4509--SELECT * FROM (
4510--SELECT *,row_number() over(partition by SourceId,ticketnum,TransactionId order by TransactionId desc) as Num2 FROM (
4511--SELECT 
4512--	row_number() over(partition by a.sto_pk, a.[TYPE], a.TICKETNUM order by Acct_PK) as NUM	
4513--	,[TYPE]
4514--	,CASE WHEN [TYPE] LIKE 'P%' THEN
4515--		 'PAWN'
4516--	WHEN [TYPE] LIKE 'SL%' THEN
4517--		 'LAYAWAY'
4518--	ELSE '' END AS PaymentTransType
4519--	,a.TICKETNUM
4520--	,a.amount-LostTicket-RTsvc as AMOUNT
4521--	,t.TransactionId
4522--	,iif(a.TYPE='PPP',4,TargetPaymentType) as PaymentTypeId
4523--	,DATEADD(HOUR,@UtcTime,a.DATEin) DATEin
4524--	,STATETAX+COUNTYTAX+LOCALTAX AS Tax
4525--	,DATEADD(HOUR,@UtcTime,a.DATEin) AS CreatedDate
4526--	,u.UserId
4527--	,Acct_PK
4528--	,c.CustomerId
4529--	--,TENDERTYP1
4530--	--,tt1.lc_Descript AS lc_Descript1
4531--	,lv1.LookupValueId AS TenderType1
4532--	--,TENDERTYP2
4533--	--,tt2.lc_Descript AS lc_Descript2
4534--	,lv2.LookupValueId AS TenderType2
4535--	,TENDERAMT1
4536--	,TENDERAMT2
4537--	,Acct_PK AS SourceId
4538--	,DATEADD(DAY,-p.NUMDAYS,DATEADD(HOUR,@UtcTime,p.CHARGEDATE)) as CHARGEDATE
4539--	, @BatchId + ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as BatchId
4540--	,[TargetStatusId] as VALUE
4541--	,CAST(a.RTsvc as decimal(12,3)) as ServiceFee
4542--	,CAST(a.RTint as decimal(12,3)) as InterestPercentage
4543--	,CAST(a.RTonetime as decimal(12,3)) as OneTimeFee
4544--	,CAST(a.RTper as decimal(12,3)) as FeePerPeriod
4545--	,CAST(a.Gun as decimal(12,3)) as FirearmFee
4546--	,a.Cus_Credit as PaidCustomerCredit
4547--FROM EXT_Acct a
4548----LEFT JOIN Company.dbo.Customer c ON a.CUS_FK=c.SourceId
4549----LEFT JOIN Company.dbo.[User] u ON a.CUS_FK=u.SourceId
4550
4551--INNER JOIN [PaymentTypeMapping] tm ON 
4552--tm.[SourcePaymentType] = CASE a.[TYPE]  WHEN 'PPP' THEN 'PH' ELSE a.[TYPE] END
4553
4554--INNER JOIN CEXT_Transaction t ON a.TICKETNUM = t.TicketNumber AND 
4555--	(CASE WHEN a.[TYPE] LIKE 'P%' THEN 2 END) = t.TransactionTypeId
4556
4557--LEFT JOIN EXT_cust cst
4558--ON cst.Cus_PK = a.CUS_FK 
4559--LEFT JOIN CEXT_Customer c
4560--ON c.SourceId = cst.Cus_id
4561
4562--LEFT JOIN EXT_users us
4563--on us.USR_PK = a.Usr_FK and us.USR_STORE = @SourceStoreId
4564--LEFT JOIN CEXT_User u
4565--on u.SourceId = us.USR_ID
4566
4567--LEFT JOIN EXT_Lookup_C tt1 ON a.TENDERTYP1 = tt1.lc_pk
4568--LEFT JOIN EXT_Lookup_C tt2 ON a.TENDERTYP2 = tt2.lc_pk
4569--LEFT JOIN CEXT_.LookupValue lv1 ON tt1.lc_Descript = lv1.Value and tt1.lc_pk = lv1.SourceId
4570--LEFT JOIN CEXT_.LookupValue lv2 ON tt2.lc_Descript = lv2.Value and tt2.lc_pk = lv2.SourceId
4571--LEFT JOIN EXT_Pawn p ON a.sto_pk = p.STORE_NO AND a.TICKETNUM = p.TICKETNUM
4572
4573		
4574--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')
4575--AND a.sto_pk=@SourceStoreId -- and a.LostTicket =0
4576----OR ([TYPE] = 'P' AND a.AMOUNT<0))
4577
4578--) payments
4579--WHERE [Type]!='P' OR ([TYPE]='P' AND NUM>1)
4580--) tr
4581--WHERE num2 = 1
4582
4583--END
4584--GO
4585/****** Object:  StoredProcedure [dbo].[TransactionPaymentExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
4586--SET ANSI_NULLS ON
4587--GO
4588--SET QUOTED_IDENTIFIER ON
4589--GO
4590
4591---- =============================================
4592---- Author:		<Farrukh Ijaz>
4593---- Create date: <09/12/2016>
4594---- Description:	<Extract TransactionPayment data from Acct table>
4595---- Sample Call: EXEC [dbo].[TransactionPaymentExtract] 1 , 5
4596
4597---- Modified by: Syed Mohsin
4598---- Modifed Date: <12/06/2016>
4599---- Comments: Drop Columned mapping for newly Drop Columned columns
4600
4601---- Modified by: Syed Mohsin
4602---- Modifed Date: <03/08/2017>
4603---- Comments: IF source payment type = PPP and amount = pawn.float amount then it is a partial payment.
4604
4605---- Modified by: Syed Mohsin
4606---- Modifed Date: <04/05/2017>
4607---- Comments: Transaction and Status join is INNER. Moved to top
4608---- =============================================
4609--CREATE PROCEDURE [dbo].[TransactionPaymentExtract]
4610--	-- Drop Column the parameters for the stored procedure here
4611--	@SourceStoreId as int, @UtcTime int
4612--AS
4613--BEGIN
4614--	-- SET NOCOUNT ON Drop Columned to prevent extra result sets from
4615--	-- interfering with SELECT statements.
4616--	SET NOCOUNT ON;
4617--	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
4618
4619--	DECLARE @Tender int = (
4620--select LookupTypeId from CEXT_LookupType where LookupTypeName='Tender Type')
4621
4622--DECLARE @BatchId int
4623--SET @BatchId = (SELECT ISNULL(MAX(batchid),0) from CEXT_TransactionPayment)
4624
4625--SELECT * FROM (
4626--SELECT *,row_number() over(partition by SourceId,ticketnum,TransactionId order by TransactionId desc) as Num2 FROM (
4627--SELECT 
4628--	row_number() over(partition by a.sto_pk, a.[TYPE], a.TICKETNUM order by Acct_PK) as NUM
4629	
4630--	,[TYPE]
4631--	,CASE WHEN [TYPE] LIKE 'P%' THEN
4632--		 'PAWN'
4633--	WHEN [TYPE] LIKE 'SL%' THEN
4634--		 'LAYAWAY'
4635--	ELSE '' END AS PaymentTransType
4636--	,a.TICKETNUM
4637--	,iif(LostTicket=0,a.AMOUNT,a.amount-LostTicket) as AMOUNT
4638--	,t.TransactionId
4639--	,iif(a.TYPE='PPP' AND p.floatamt > 0 and p.floatamt = a.amount,1,TargetPaymentType) as PaymentTypeId
4640--	,DATEADD(HOUR,@UtcTime,a.DATEin) DATEin
4641--	,STATETAX+COUNTYTAX+LOCALTAX AS Tax
4642--	,DATEADD(HOUR,@UtcTime,a.DATEin) AS CreatedDate
4643--	,u.UserId
4644--	,Acct_PK
4645--	,c.CustomerId
4646--	--,TENDERTYP1
4647--	--,tt1.lc_Descript AS lc_Descript1
4648--	,lv1.LookupValueId AS TenderType1
4649--	--,TENDERTYP2
4650--	--,tt2.lc_Descript AS lc_Descript2
4651--	,lv2.LookupValueId AS TenderType2
4652--	,TENDERAMT1
4653--	,TENDERAMT2
4654--	,Acct_PK AS SourceId
4655--	,DATEADD(DAY,-p.NUMDAYS,DATEADD(HOUR,@UtcTime,p.OrigChgDate)) as CHARGEDATE
4656--	, @BatchId + ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as BatchId
4657--	,[TargetStatusId] as VALUE
4658--	,CAST(a.RTsvc as decimal(12,3)) as ServiceFee
4659--	,CAST(a.RTint as decimal(12,3)) as InterestPercentage
4660--	,CAST(a.RTonetime as decimal(12,3)) as OneTimeFee
4661--	,CAST(a.RTper as decimal(12,3)) as FeePerPeriod
4662--	,CAST(a.Gun as decimal(12,3)) as FirearmFee
4663--	,iif(a.TYPE like 'P%',CAST(a.OneTime as decimal(16,4)),NULL) as SysOptOneTimeFee
4664--	,CAST(CASE TargetPaymentType
4665--	WHEN 8 THEN 'Principal Lower'
4666--	WHEN 9 THEN 'Redemption'
4667--	ELSE NULL
4668--	END as nvarchar(50)) AS ValueOf14,
4669--	a.Cus_Credit as PaidCustomerCredit
4670--FROM EXT_Acct a
4671----LEFT JOIN Company.dbo.Customer c ON a.CUS_FK=c.SourceId
4672----LEFT JOIN Company.dbo.[User] u ON a.CUS_FK=u.SourceId
4673
4674--INNER JOIN PaymentTypeMapping tm ON a.[TYPE] = tm.[SourcePaymentType]
4675
4676--INNER JOIN CEXT_Transaction t ON a.TICKETNUM = t.TicketNumber AND 
4677--	(CASE WHEN a.[TYPE] LIKE 'P%' THEN 1 WHEN [TYPE] LIKE 'SL%' THEN 5 END) = t.TransactionTypeId
4678
4679--LEFT JOIN EXT_cust cst
4680--ON cst.Cus_PK = a.CUS_FK 
4681--LEFT JOIN CEXT_Customer c
4682--ON c.SourceId = cst.Cus_id
4683
4684--LEFT JOIN EXT_users us
4685--on us.USR_PK = a.Usr_FK and us.USR_STORE = @SourceStoreId
4686--LEFT JOIN CEXT_User u
4687--on u.SourceId = us.USR_ID
4688
4689--LEFT JOIN EXT_Lookup_C tt1 ON a.TENDERTYP1 = tt1.lc_pk
4690--LEFT JOIN EXT_Lookup_C tt2 ON a.TENDERTYP2 = tt2.lc_pk
4691--LEFT JOIN CEXT_LookupValue lv1 ON tt1.lc_Descript = lv1.Value and lv1.lookuptypeid = @Tender
4692--LEFT JOIN CEXT_.LookupValue lv2 ON tt2.lc_Descript = lv2.Value and lv1.lookuptypeid = @Tender
4693--LEFT JOIN EXT_Pawn p ON a.sto_pk = p.STORE_NO AND a.TICKETNUM = p.TICKETNUM
4694
4695		
4696--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')
4697--AND a.sto_pk=@SourceStoreId -- and a.LostTicket =0
4698----OR ([TYPE] = 'P' AND a.AMOUNT<0))
4699
4700--) payments
4701--WHERE [Type]!='P' OR ([TYPE]='P' AND NUM>1)
4702--) tr
4703--WHERE num2 = 1
4704
4705
4706--END
4707--GO
4708/****** Object:  StoredProcedure [dbo].[TransactionPaymentMissingExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
4709--SET ANSI_NULLS ON
4710--GO
4711--SET QUOTED_IDENTIFIER ON
4712--GO
4713
4714---- =============================================
4715---- Author:		<Syed Mohsin>
4716---- Create date: <11/09/2017>
4717---- Description:	<Drop Column payments if total paid does not match>
4718---- Sample Call: EXEC [dbo].[TransactionPaymentMissingExtract] 182,1
4719---- =============================================
4720--CREATE PROCEDURE [dbo].[TransactionPaymentMissingExtract]
4721--	-- Drop Column the parameters for the stored procedure here
4722--	@TargetStoreId as int, @SourceStoreId as int
4723--AS
4724--BEGIN
4725--	-- SET NOCOUNT ON Drop Columned to prevent extra result sets from
4726--	-- interfering with SELECT statements.
4727--	SET NOCOUNT ON;
4728--	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
4729
4730--DECLARE @Interval int
4731--SET @Interval = (SELECT SystemOptionValue from CEXT_.SystemOption so inner join CEXT_StoreSystemOption sso on so.SystemOptionId = sso.SystemOptionId
4732--WHERE SystemOptionKey like 'PAWNBUY_DURATION_SVC_CHG_PRD' and sso.StoreId=@TargetStoreId)
4733
4734--DECLARE @Days int
4735--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
4736--WHERE SystemOptionKey like 'PAWNBUY_DURATION_SVC_CHG_IS_MONTHLY' and sso.StoreId=@TargetStoreId)
4737
4738--DECLARE @Batchid int
4739--SET @Batchid =(SELECT ISNULL(MAX(batchid),0) from CEXT_TransactionPayment )
4740
4741--select q.transactionId,
4742--23 as PaymentTypeId,
4743--PAIDAMT-col1 as Amount,
4744-- DATEADD(DAY,-@Days,t.outdate) PeriodDate,
4745-- DATEADD(DAY,-@Days,t.outdate) PaymentDate,
4746-- DATEADD(DAY,-@Days,t.outdate) CreatedDate,
4747-- t.CreatedBy,
4748-- ROW_NUMBER() over(order by t.transactionid)+@Batchid as BatchId,
4749-- t.CustomerId,
4750-- t.outdate CurrentOutDate,
4751-- t.SourceId as SourceId
4752
4753--	from (
4754--select sum(ISNULL(tp.amount,0)) col1,t.TicketNumber,t.transactionid from CEXT_Transaction t 
4755--left join CEXT_TransactionPayment tp on t.TransactionId=tp.TransactionId and tp.PaymentTypeId in (1,4,7,8,9)
4756--where t.TransactionTypeId=1 and t.StoreId=@TargetStoreId
4757--group by t.ticketnumber,t.TransactionId
4758----order by t.ticketnumber
4759--)q 
4760--inner join EXT_Pawn p on p.TICKETNUM = q.ticketnumber
4761--inner join CEXT_transaction t on t.transactionid = q.transactionid
4762--where col1 <> PAIDAMT and t.StoreId=@TargetStoreId and p.STORE_NO=@SourceStoreId
4763
4764
4765--END
4766--GO
4767/****** Object:  StoredProcedure [dbo].[TransactionPaymentPartialPaymentsExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
4768--SET ANSI_NULLS ON
4769--GO
4770--SET QUOTED_IDENTIFIER ON
4771--GO
4772
4773---- =============================================
4774---- Author:		<Syed Mohsin>
4775---- Create date: <03/08/2017>
4776---- Description:	<Extract partial payments from pawn.floatamt>
4777---- Sample Call: EXEC [dbo].[TransactionPaymentPartialPaymentsExtract] 8 
4778---- =============================================
4779--CREATE PROCEDURE [dbo].[TransactionPaymentPartialPaymentsExtract]
4780--	-- Drop Column the parameters for the stored procedure here
4781--	@SourceStoreId as int
4782--AS
4783--BEGIN
4784--	-- SET NOCOUNT ON Drop Columned to prevent extra result sets from
4785--	-- interfering with SELECT statements.
4786--	SET NOCOUNT ON;
4787--	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
4788
4789--select * FROM (
4790--  select tp.TransactionId,
4791--  1 as PaymentTypeId,
4792--  p.floatamt as Amount,
4793--  tp.PeriodDate,
4794--  tp.PaymentDate,
4795--  tp.Tax,
4796--  tp.AssociatedTransactionPaymentId,
4797--  tp.IsQueued,
4798--  tp.BatchId,
4799--  tp.Value,
4800--  tp.CreatedDate,
4801--  tp.CreatedBy,
4802--  tp.UpdatedDate,
4803--  tp.UpdatedBy,
4804--  tp.CustomerId,
4805--  tp.CurrentOutDate,
4806--  tp.TenderType1,
4807--  tp.TenderType2,
4808--  tp.TenderType1Amount,
4809--  tp.TenderType2Amount,
4810--  tp.SourceId,
4811--  tp.ServiceFee,
4812--  tp.InterestPercentage,
4813--  tp.OneTimeFee,
4814--  tp.FeePerPeriod,
4815--  tp.FirearmFee,
4816--  row_number() over(partition by p.pwn_id order by a.datein desc) as NUM
4817--   from CEXT_Transactionpayment tp
4818--  inner join EXT_Acct a on a.Acct_PK = tp.SourceId and a.sto_pk = @SourceStoreId and a.TYPE='PPP'
4819--  inner join CEXT_Transaction t on t.TransactionId = tp.TransactionId
4820--  inner join EXT_Pawn p on p.pwn_id = t.SourceId and floatamt >0
4821--  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)
4822--  )q where q.NUM = 1
4823
4824
4825--END
4826--GO
4827/****** Object:  StoredProcedure [dbo].[TransactionSettingExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
4828--SET ANSI_NULLS ON
4829--GO
4830--SET QUOTED_IDENTIFIER ON
4831--GO
4832
4833---- =============================================
4834---- Author:		Syed Mohsin
4835---- Create date: <8/2/2016>
4836---- Description:	<Extracts Transaction XML settings >
4837---- Sample Call: EXEC [dbo].[TransactionSettingExtract] 3429
4838---- =============================================
4839
4840--CREATE Proc [dbo].[TransactionSettingExtract]
4841--@TargetStoreId int
4842--as
4843--BEGIN
4844--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
4845
4846--DECLARE @key1 int;
4847--DECLARE @key2 int;
4848--DECLARE @key3 int;
4849--DECLARE @key4 int;
4850
4851--DECLARE @value1 varchar(200);
4852--DECLARE @value2 varchar(200);
4853--DECLARE @value4 varchar(200);
4854 
4855--SELECT  @key1 = so.SystemOptionId, @value1 = ss.SystemOptionValue
4856--  FROM CEXT_SystemOption so
4857--  INNER JOIN CEXT_StoreSystemOption ss
4858--	ON ss.SystemOptionId = so.SystemOptionId AND ss.StoreId = @TargetStoreId
4859--  where SystemOptionKey='PAWNBUY_DURATION_SVC_CHG_PRD' 
4860
4861--SELECT  @key2 = so.SystemOptionId, @value2 = ss.SystemOptionValue
4862--  FROM CEXT_SystemOption so
4863--  INNER JOIN CEXT_StoreSystemOption ss
4864--	ON ss.SystemOptionId = so.SystemOptionId AND ss.StoreId = @TargetStoreId
4865--  WHERE SystemOptionKey='PAWNBUY_DURATION_SVC_CHG_IS_MONTHLY'
4866
4867--  SELECT  @key3 = so.SystemOptionId
4868--  FROM CEXT_SystemOption so
4869--  INNER JOIN CEXT_StoreSystemOption ss
4870--	ON ss.SystemOptionId = so.SystemOptionId AND ss.StoreId = @TargetStoreId
4871--  WHERE SystemOptionKey='PAWNBUY_OTHER_FEE_ONE_TIME_FEE'
4872
4873--    SELECT  @key4 = so.SystemOptionId
4874--  FROM CEXT_SystemOption so
4875--  INNER JOIN CEXT_StoreSystemOption ss
4876--	ON ss.SystemOptionId = so.SystemOptionId AND ss.StoreId = @TargetStoreId
4877--  WHERE SystemOptionKey='PAWNBUY_OTHER_FEE_REMINDER_FEE'
4878
4879--  SELECT transactionid,CAST ('<TransactionSettings>
4880--					<Setting Key="'+CAST(@key1 as nvarchar)+'" 
4881--					Name="PAWNBUY_DURATION_SVC_CHG_PRD" Value="'+CAST(p.SERVPERIOD as nvarchar)+'"/>
4882--					<Setting Key="'+CAST(@key2 as nvarchar)+'" 
4883--					Name="PAWNBUY_DURATION_SVC_CHG_IS_MONTHLY" Value="'+CAST(iif(p.MONTHLYCHG=0,'False','True') as nvarchar)+'"/>
4884--					<Setting Key="'+CAST(@key3 as nvarchar)+'" 
4885--					Name="PAWNBUY_OTHER_FEE_ONE_TIME_FEE" Value="'+CAST(p.StorageChrg as nvarchar)+'"/>
4886--					<Setting Key="'+CAST(@key4 as nvarchar)+'" 
4887--					Name="PAWNBUY_OTHER_FEE_REMINDER_FEE" Value="'+CAST(0.00 as nvarchar)+'"/>
4888--					</TransactionSettings>' as nvarchar(max)) as XMLSetting from CEXT_Transaction t
4889--  INNER JOIN EXT_Pawn p  on p.PWN_id = t.SourceId 
4890--  WHERE t.SourceId IS NOT NULL 
4891--  AND t.StoreId=@TargetStoreId
4892--  AND t.TransactionTypeId = 1
4893
4894--END
4895--GO
4896/****** Object:  StoredProcedure [dbo].[TransactionVersionExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
4897SET ANSI_NULLS ON
4898GO
4899SET QUOTED_IDENTIFIER ON
4900GO
4901
4902-- =============================================
4903-- Author:		<Syed Mohsin>
4904-- Create date: <04/24/2017>
4905-- Description:	<Extract Transaction Versions by extracting data from source>
4906-- Sample Call: EXEC [dbo].[TransactionVersionExtract] 182
4907-- =============================================
4908CREATE PROCEDURE [dbo].[TransactionVersionExtract]
4909	-- Drop Column the parameters for the stored procedure here
4910	@TargetStoreId as int
4911AS
4912BEGIN
4913	-- SET NOCOUNT ON Drop Columned to prevent extra result sets from
4914	-- interfering with SELECT statements.
4915	SET NOCOUNT ON;
4916	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
4917SELECT t.[TransactionId]
4918      ,t.[TransactionTypeId]
4919      ,t.[StoreId]
4920      ,t.[CustomerId]
4921      ,t.[TicketNumber]
4922      ,t.[Duration]
4923      ,t.[Period]
4924      ,t.[InDate]
4925      ,t.[OutDate]
4926      ,t.[Amount]
4927      ,t.[RateTableId]
4928      ,t.[Note]
4929      ,t.[IsActive]
4930      ,t.[IsDeleted]
4931      ,t.[IsQueued]
4932      ,t.[CreatedDate]
4933      ,t.[CreatedBy]
4934      ,t.[UpdatedDate]
4935      ,t.[UpdatedBy]
4936      ,t.[StatusId]
4937      ,t.[Message]
4938      ,t.[VoidDescription]
4939      ,t.[IsEatTax] FROM (
4940SELECT t.[TransactionId]
4941      ,t.[TransactionTypeId]
4942      ,t.[StoreId]
4943      ,t.[CustomerId]
4944      ,t.[TicketNumber]
4945      ,t.[Duration]
4946      ,t.[Period]
4947      ,t.[InDate]
4948      ,t.[OutDate]
4949      ,t.[Amount]
4950      ,t.[RateTableId]
4951      ,t.[Note]
4952      ,t.[IsActive]
4953      ,t.[IsDeleted]
4954      ,t.[IsQueued]
4955      ,t.[CreatedDate]
4956      ,t.[CreatedBy]
4957      ,t.[UpdatedDate]
4958      ,t.[UpdatedBy]
4959      ,t.[StatusId]
4960      ,t.[Message]
4961      ,t.[VoidDescription]
4962      ,t.[IsEatTax],
4963	  ROW_NUMBER() over(partition by t.TransactionId order by TransactionversionId desc) rn
4964  FROM [Company].[dbo].[Transaction] t
4965  LEFT JOIN Company.dbo.TransactionVersion tv on tv.TransactionId = t.TransactionId
4966  WHERE t.StoreId = @TargetStoreId and (t.TransactionTypeId != 2 OR tv.transactionid is null )
4967  )t
4968  WHERE t.rn=1
4969END
4970GO
4971/****** Object:  StoredProcedure [dbo].[TransactionVersionHistoryExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
4972SET ANSI_NULLS ON
4973GO
4974SET QUOTED_IDENTIFIER ON
4975GO
4976
4977-- =============================================
4978-- Author:		<Syed Mohsin>
4979-- Create date: <09/21/2017>
4980-- Description:	<Extract History Transaction Versions for All Transactions>
4981-- Sample Call: EXEC [dbo].[TransactionVersionHistoryExtract] 1406 , 1 , 5
4982-- =============================================
4983CREATE PROCEDURE [dbo].[TransactionVersionHistoryExtract]
4984	-- Drop Column the parameters for the stored procedure here
4985	@TargetStoreId as int, @SourceStoreId as int, @UtcOffset as int
4986AS
4987BEGIN
4988	-- SET NOCOUNT ON Drop Columned to prevent extra result sets from
4989	-- interfering with SELECT statements.
4990	SET NOCOUNT ON;
4991	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
4992SELECT *,
4993iif(NUM2 = 1,NewOutDate,DATEDrop Column(HOUR,5, CONVERT(DATETIME, OldOutDate))) as OutDate
4994 FROM (
4995SELECT *,row_number() over(partition by TicketNumber order by SourceId desc) as NUM2 FROM (
4996Select 
4997		row_number() over(partition by a.sto_pk, a.[TYPE], a.TICKETNUM order by Acct_PK) as NUM,
4998		t.TransactionId ,
4999        CASE 
5000		WHEN TYPE like 'P%' THEN 1
5001		WHEN TYPE LIKE 'B%' THEN 2
5002		WHEN TYPE LIKE 'SL%' THEN 5
5003		ELSE 3
5004		END
5005		 as TransactionTypeId,
5006        t.StoreId ,
5007        cst.CustomerId ,
5008        t.TicketNumber ,
5009       CAST (t.Period as int) Period ,
5010	    t.Duration ,
5011        iif(t.TransactionTypeId in (1,2),t.InDate,DATEDrop Column(HOUR,@UtcOffset, CONVERT(DATETIME, a.DATEin))) as InDate,
5012        CAST(t.OutDate as datetime) as NewOutDate,
5013        iif (a.AMOUNT < 0 ,a.AMOUNT  * -1,a.Amount) as Amount,
5014        t.RateTableId ,
5015        t.Note ,
5016		m.TargetKey as StatusId,
5017        DATEDrop Column(HOUR,@UtcOffset, CONVERT(DATETIME, a.DATEin)) as CreatedDate,
5018        t.CreatedBy,
5019		a.Acct_PK SourceId,
5020		a.TYPE,
5021		CASE 
5022			WHEN a.TYPE like 'P%' OR a.TYPE like 'B%' 
5023			THEN CAST(p.OrigDateOut  as DATETIME)
5024			ELSE CAST( s.PrevDate as DATETIME)
5025			END 
5026		AS OldOutDate, 
5027		t.StatusId as TStat,
5028		t.Amount as TAmount
5029		from EXT_Acct a
5030		
5031
5032INNER JOIN Company.dbo.[Transaction] t on 
5033t.TicketNumber = a.TICKETNUM
5034AND ( 
5035		(t.TransactionTypeId in (1,2) and  (a.TYPE like 'P%' OR a.TYPE like 'B%')) 
5036		OR 
5037		(t.TransactionTypeId in (3,5) and a.TYPE like 'S%')
5038	)
5039LEFT JOIN EXT_Pawn p
5040on p.TICKETNUM = a.TICKETNUM 
5041AND a.sto_pk = p.STORE_NO 
5042AND (a.TYPE like 'P%' OR a.TYPE like 'B%')
5043
5044LEFT JOIN EXT_Sold s
5045ON s.TICKETNUM = a.TICKETNUM 
5046AND s.STO_PK = a.sto_pk
5047AND a.TYPE like 'S%'
5048
5049INNER JOIN pawnmaster_foxpro.dbo.cust c
5050ON c.Cus_PK = a.CUS_FK
5051
5052INNER JOIN Company.dbo.Customer cst
5053	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))
5054	and cst.BirthDate like LTRIM(RTRIM(c.CUS_BIRTHDate)))
5055
5056INNER JOIN Common.SourceTargetKeyMapping m
5057on m.SourceTable = 'Acct' and m.SourceKey = CASE WHEN a.TYPE IN ( 'PV','PPU','PD') and a.Amount < 0 THEN 'P' 
5058												WHEN a.TYPE = 'SLD' and a.Amount > 0 THEN 'SL' 
5059												WHEN a.TYPE = 'SSV' and s.STATUS = 'S' THEN 'SS'
5060											ELSE a.TYPE END
5061WHERE a.sto_pk = @SourceStoreId and t.StoreId = @TargetStoreId
5062)payments
5063WHERE [Type]!='P' OR ([TYPE]='P' AND NUM = 1)
5064)p
5065WHERE 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)
5066
5067UNION
5068SELECT * FROM (
5069SELECT 
5070		ROW_NUMBER() over(partition by p.pwn_id order by i.items_pk) num,
5071		t.TransactionId ,
5072        t.TransactionTypeId,
5073        t.StoreId ,
5074        t.CustomerId ,
5075        t.TicketNumber ,
5076		 CAST (t.Period as int) Period ,
5077        t.Duration ,
5078        DATEDrop Column(HOUR,@UtcOffset, CONVERT(DATETIME, inv.DATEin)) as  InDate,
5079       CAST(p.OrigDateOut as DATETIME) as NewOutDate,
5080	    t.Amount as Amount,
5081        t.RateTableId ,
5082        t.Note ,
5083		iif(inv.QUANTITY < 0,4,45) as StatusId,
5084        DATEDrop Column(HOUR,@UtcOffset, CONVERT(DATETIME, inv.DATEin)) as CreatedDate,
5085        us.UserId as CreatedBy,
5086		inv.Inv_PK SourceId,
5087		'' as TYPE,
5088		CAST(p.OrigDateOut as DATETIME) as OldOutDate,
5089		0 as NUM2,
5090		DATEDrop Column(HOUR,@UtcOffset, CONVERT(DATETIME, p.OrigDateOut)) as OutDate,
5091		1 as TStat,
5092		1 as TAmount
5093		 
5094
5095FROM pawnmaster_foxpro.dbo.inv inv
5096inner join EXT_items i on i.INVNUM = inv.INVNUM
5097inner join EXT_Pawn p on p.PWN_id = i.PWN_id
5098inner join Company.dbo.[Transaction] t on t.TicketNumber = p.TICKETNUM and t.TransactionTypeId=2
5099inner join pawnmaster_foxpro.dbo.users u on u.USR_PK = inv.USR_fk
5100inner join Company.dbo.[User] us on us.SourceId = u.USR_ID
5101where inv.Sto_PK = @SourceStoreId and t.StoreId=@TargetStoreId and inv.STATUS='B'
5102)q where q.num = 1
5103
5104ORDER BY CreatedDate ASC
5105
5106END
5107
5108
5109GO
5110/****** Object:  StoredProcedure [dbo].[UsersExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
5111SET ANSI_NULLS ON
5112GO
5113SET QUOTED_IDENTIFIER ON
5114GO
5115
5116CREATE Proc [dbo].[UsersExtract]
5117@StoreID int
5118
5119AS
5120
5121BEGIN
5122SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
5123select  
5124	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,
5125	cast ( '1xxx!' as nvarchar(150)) as Password, --Drop Column in package
5126	Null as BiometricTemplate,
5127	NULL as BiometricSample,
5128	GetDate() as CreatedDate,
5129	1 as CreatedBy, -- Drop Column in package
5130	cast( LTRIM(RTRIM(USR_FNAME))  as nvarchar(100)) as FirstName,
5131	cast(LTRIM(RTRIM(USR_MNAME))  as nvarchar(100))	as	MiddleName,		
5132	cast(LTRIM(RTRIM(USR_LNAME))	as nvarchar(100)) as	LastName,
5133	cast(LTRIM(RTRIM(USR_Drop Column1))	as nvarchar(255)) as	Drop ColumnressLine1	,		
5134	cast(LTRIM(RTRIM(USR_Drop Column2))	as nvarchar(255))		as	Drop ColumnressLine2	,		
5135	cast(LTRIM(RTRIM(USR_CITY))	as nvarchar(50))	as	City,		
5136	cast(LTRIM(RTRIM(USR_STATE))	as nvarchar(50))	as State,		
5137	cast(LTRIM(RTRIM(USR_ZIP))	as nvarchar(10))	as ZipCode,
5138	cast( USR_AC1+'-'+USR_PHONE1	as nvarchar(20)) as Phone,	
5139	cast(USR_SSNUM	as nvarchar(400)) as SSN,	
5140	USR_BIRTHDATE	as BirthDate,
5141	USR_STARTDATE	as StartDate,	
5142	USR_TERMINATE	as EndDate	,
5143	CAST(iif(BuyLimit=999999.99,9999999999.99,BuyLimit) as decimal(12,2))		as MaxTransactionAmountBuy, -- Iteration 1 response fix
5144	CAST(iif(PawnLimit=999999.99,9999999999.99,PawnLimit) as decimal(12,2))		as MaxTransactionAmountPawn, -- Iteration 1 response fix	
5145	CAST(iif(OptionLimit=999999.99,9999999999.99,OptionLimit) as decimal(12,2))		as MaxTransactionAmountSale, -- Iteration 1 response fix
5146	USR_DISCOUNT	as SaleDiscount,	
5147	USR_ACTIVE		as StatusId,	
5148	0			as IsLoggedIn,
5149	1  as CompanyId, --Drop Column in package
5150	USR_STORE		as StoreId,		
5151	cast(USR_NOTES	as nvarchar(200))		as Comment,		
5152	1			as IsRequiredChangePassword,
5153	0			as IsDefaultUser,
5154	1			as CultureId,
5155	0			as RowVersion,		
5156	1			as IsUserMigrated,
5157	1 as DefaultStoreId,
5158	USR_ID	as SourceId
5159
5160	
5161	from [PawnMaster_foxPro].dbo.users s (nolock)
5162		left outer join [Company].dbo.[User] t
5163			on LTRIM(RTRIM(s.USR_LANID)) =t.UserName
5164		where USR_STORE = @StoreID AND s.USR_LANID != '***'
5165
5166END
5167GO
5168/****** Object:  StoredProcedure [dbo].[VendorExtract]    Script Date: 6/13/2018 9:39:35 AM ******/
5169SET ANSI_NULLS ON
5170GO
5171SET QUOTED_IDENTIFIER ON
5172GO
5173
5174-- =============================================
5175-- Author:		Nadeem Akhtar
5176-- Create date: <8/2/2016>
5177-- Description:	<Extracts Store Vendors >
5178-- Sample Call: EXEC [dbo].[UsersExtract] 8
5179
5180-- Updated By: Syed Mohsin
5181-- Update date: <8/3/2016>
5182-- Comments: Corrected mapping for columns and Drop Columned join conditions to meet the design logic.
5183--			 Drop Columned Storeid parameter for Source StoreId  
5184
5185-- Updated By: Syed Mohsin
5186-- Update date: <8/25/2016>
5187-- Comments: Updated join from inner to left with User table.
5188
5189-- Updated By: Syed Mohsin
5190-- Update date: <9/1/2016>
5191-- Comments: Removed store check and updated condition to check for unique vendors.
5192
5193-- Updated By: Syed Mohsin
5194-- Update date: <9/2/2016>
5195-- Comments: Made fixes for iteration 1 response.
5196
5197-- Updated By: Syed Mohsin
5198-- Update date: <9/22/2016>
5199-- Comments: Drop Columned country in extract by joining with state
5200-- =============================================
5201
5202CREATE Proc [dbo].[VendorExtract]
5203@StoreID int
5204as
5205BEGIN
5206SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
5207select 
5208cast(cast(VEN_STORE as nvarchar(10))+'_' as nvarchar(50)) as VendorCode, -- Drop Column in package
5209cast(RTRIM(LTRIM(VEN_COMPANY)) as nvarchar(250)) as VendorName,
5210cast(VEN_STORE as int) as StoreId, --Replace with targetStoreId in package
5211cast(RTRIM(LTRIM(VEN_STATE)) as nvarchar(50)) as State,
5212cast(st.Country as nvarchar(50)) as Country,
5213cast(VEN_CONTACT as nvarchar(150)) as Contact,
5214cast(RTRIM(LTRIM(VEN_Drop Column1)) as nvarchar(255)) as Drop ColumnressLine1,
5215cast(RTRIM(LTRIM(VEN_Drop Column2)) as nvarchar(255)) as Drop ColumnressLine2,
5216cast(RTRIM(LTRIM(VEN_CITY)) as nvarchar(50)) as City,
5217cast(VEN_ZIP as nvarchar(10)) as ZipCode,
5218cast(VEN_AC1+'-'+VEN_PHONE as nvarchar(20)) as Phone,
5219cast(VEN_AC2+''+VEN_FAX as nvarchar(20)) as Fax,
5220cast(RTRIM(LTRIM(s.Comments)) as nvarchar(100)) as Comments, -- Changed after iteration 1 response
5221cast(Sells_FireArms as bit) as SellsFireArms,
5222cast(VEN_FFLNUM as nvarchar(50)) as FFLNumber,
5223cast(VEN_FFLEXPIREDATE as datetime) as FFLExpire,
52240 as IsDeleted,
5225getdate() as CreatedDate,
52261 as CreatedBy, --Drop Column in package
5227getdate() as UpdatedDate,
5228u.UserId as UpdatedBy,
5229VEN_id	as SourceId
5230from [PawnMaster_foxPro].dbo.vend s
5231left outer join [Company].dbo.Vendor t
5232				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
5233left join [Company].dbo.[User] u
5234				on s.LastUpdatedUSR_ID = u.SourceId
5235left join dbo.[State] st on RTRIM(LTRIM(s.VEN_STATE)) = st.StateCode
5236WHERE t.VendorName is null
5237END
5238
5239GO