yDdxWUWz

1
2
3CREATE PROCEDURE [crt].[GETPRODUCTS]
4@bi_ChannelId           BIGINT, -- The unique channel identifier (required).
5@bi_CatalogId           BIGINT, -- The catalog identifier (optional).
6@i_DataLevel            INT,    -- A value to control the richness of the products returned.
7@dt_ChannelDate         DATE,   -- The current date in the channel's time zone.
8@b_IsForwardLooking     BIT,    -- A value indicating whether to include products in future assortments.
9@b_SkipVariantExpansion BIT,    -- A value indicating whether product masters are to be expanded.
10@tvp_ProductIds         [crt].[RECORDIDTABLETYPE]   READONLY,
11@b_IsOnlineSearch       BIT     -- A value indicating whether the search is retrieving the product image blob
12AS
13BEGIN
14/*  // data level map for product:
15//
16// data level   |   content
17// =============|================================================================
18// identity     |   id, item id, invent dim
19// minimal      |   + behavior data: is (master, serialized, kit) flags, validity
20// standard     |   + first class properties, indexed properties
21// extended     |   + composition information
22// complete     |   + change tracking information
23// =============|================================================================
24*/
25SET NOCOUNT ON
26
27DECLARE @i_ReturnCode               INT;
28DECLARE @i_Error                    INT;
29
30DECLARE @tvp_ExpandedWorkingIdSet       [crt].[RECORDIDTABLETYPE]
31DECLARE @tvp_LookupIds                  [crt].[RECORDIDTABLETYPE]
32DECLARE @tvp_DummyId                    [crt].[RECORDIDTABLETYPE]
33DECLARE @tvp_DistinctProductVariantIds  [crt].[RECORDIDTABLETYPE]
34DECLARE @tvp_ProductIdsNormalized       [crt].[RECORDIDSTABLETYPE]
35DECLARE @tvp_AssortedProductIds         [crt].[PRODUCTLOOKUPIDTABLETYPE]
36DECLARE @tvp_CatalogAssortedProductIds  [crt].[PRODUCTLOOKUPIDTABLETYPE]
37
38DECLARE @ProductNameAttributeName           NVARCHAR(256) = 'ProductName'
39DECLARE @ProductDescriptionAttributeName    NVARCHAR(256) = 'Description'
40DECLARE @ProductImageAttributeName          NVARCHAR(256) = 'Image'
41
42-- Insert dummy record to satisfy API contract
43INSERT INTO @tvp_DummyId VALUES (0)
44
45-- initializes the return code; we'll return the number of data sets as the return code.
46SET @i_ReturnCode = 0;
47SET @bi_CatalogId = ISNULL(@bi_CatalogId, 0);
48
49-- Retrieve the data area for the current channel identifier.
50DECLARE @nvc_DataAreaId NVARCHAR(4);
51SELECT @nvc_DataAreaId = INVENTLOCATIONDATAAREAID FROM [ax].RETAILCHANNELTABLE rct WHERE rct.RECID = @bi_ChannelId
52
53-- The copy operation into @tvp_Ids below is needed for performance improvements, do not remove it.
54DECLARE @tvp_Ids crt.RECORDIDTABLETYPE
55INSERT INTO @tvp_Ids(RECID)
56SELECT tpi.RECID
57FROM @tvp_ProductIds tpi
58
59-- Normalize all input identifiers. That means standalone, product master and variant
60-- identifiers all return the parent product as the lookup identifer. This is necessary
61-- because the input product identifiers can be any type of product (standalone, product
62-- master, variant, kit, etc).
63INSERT @tvp_ProductIdsNormalized (RECID, PARENTRECID)
64SELECT DISTINCT
65ids.RECID AS 'PRODUCTID',
66COALESCE(pv2.PRODUCTMASTER, pv.PRODUCTMASTER, ids.RECID) AS 'LOOKUPID'
67FROM @tvp_Ids ids
68LEFT OUTER JOIN [ax].ECORESDISTINCTPRODUCTVARIANT pv ON [pv].RECID = [ids].RECID
69LEFT OUTER JOIN [ax].ECORESDISTINCTPRODUCTVARIANT pv2 ON [pv2].PRODUCTMASTER = [ids].RECID
70
71-- Retrieve the unique parent product identifiers.
72INSERT @tvp_LookupIds (RECID)
73SELECT DISTINCT PARENTRECID -- LOOKUPID
74FROM @tvp_ProductIdsNormalized
75
76-- Filter lookup identifiers by assortment and expand all product masters to their individually assorted variants if requested.
77INSERT @tvp_AssortedProductIds (LOOKUPID, PRODUCTID, ISREMOTE, VALIDFROM, VALIDTO)
78SELECT
79ap.PRODUCTID AS LOOKUPID,
80CASE
81WHEN ap.VARIANTID = 0 THEN ap.PRODUCTID
82ELSE ap.VARIANTID
83END AS PRODUCTID,
84ap.ISREMOTE,
85ap.VALIDFROM,
86ap.VALIDTO
87FROM [crt].GETASSORTEDPRODUCTS(@bi_ChannelId, @dt_ChannelDate, @b_IsForwardLooking, 0 /* @b_SkipVariantExpansion */, 1 /* @b_ProductIdsPassed */, @tvp_LookupIds) ap
88
89-- Build the expanded working set
90IF (@bi_CatalogId = 0)
91BEGIN
92INSERT @tvp_CatalogAssortedProductIds (LOOKUPID, PRODUCTID, ISREMOTE, VALIDFROM, VALIDTO)
93SELECT LOOKUPID, PRODUCTID, ISREMOTE, VALIDFROM, VALIDTO
94FROM @tvp_AssortedProductIds
95END
96ELSE
97BEGIN
98-- Filter out any products that are not in the specified catalog.
99WITH cte_CatalogProducts AS
100(
101SELECT
102ap.LOOKUPID,
103ap.PRODUCTID,
104ap.ISREMOTE,
105CASE
106WHEN ap.VALIDFROM > pc.VALIDFROM THEN ap.VALIDFROM
107ELSE pc.VALIDFROM
108END AS 'VALIDFROM',
109CASE
110WHEN pc.VALIDTO < ap.VALIDTO THEN pc.VALIDTO
111ELSE ap.VALIDTO
112END AS 'VALIDTO'
113FROM @tvp_AssortedProductIds ap
114INNER JOIN [ax].RETAILPUBCATALOGPRODUCT pcp ON pcp.PRODUCT = ap.LOOKUPID OR pcp.PRODUCT = ap.PRODUCTID
115INNER JOIN [ax].RETAILPUBCATALOG pc ON pc.ORIGIN = pcp.CATALOG
116INNER JOIN [crt].PUBCATALOGCHANNELVIEW ccv ON ccv.CATALOG = pcp.CATALOG
117WHERE ccv.CHANNEL = @bi_ChannelId AND ccv.CATALOG = @bi_CatalogId AND
118((@b_IsForwardLooking = 0 AND @dt_ChannelDate BETWEEN ap.VALIDFROM AND ap.VALIDTO) OR
119(@b_IsForwardLooking = 1 AND ap.VALIDTO >= @dt_ChannelDate))
120)
121INSERT @tvp_CatalogAssortedProductIds (LOOKUPID, PRODUCTID, ISREMOTE, VALIDFROM, VALIDTO)
122-- Retrieve the product masters that are either explicitly or implicitly part of the specified catalog.
123SELECT cp.LOOKUPID, cp.PRODUCTID, cp.ISREMOTE, cp.VALIDFROM, cp.VALIDTO
124FROM cte_CatalogProducts cp
125WHERE ((@b_IsForwardLooking = 0 AND @dt_ChannelDate BETWEEN cp.VALIDFROM AND cp.VALIDTO) OR
126(@b_IsForwardLooking = 1 AND cp.VALIDTO >= @dt_ChannelDate))
127UNION
128-- Retrieve the product variants that are either explicitly or implicitly part of the specified catalog.
129SELECT cp.LOOKUPID, cp.LOOKUPID, cp.ISREMOTE, cp.VALIDFROM, cp.VALIDTO
130FROM cte_CatalogProducts cp
131WHERE ((@b_IsForwardLooking = 0 AND @dt_ChannelDate BETWEEN cp.VALIDFROM AND cp.VALIDTO) OR
132(@b_IsForwardLooking = 1 AND cp.VALIDTO >= @dt_ChannelDate))
133END
134
135DECLARE @tbl_ProductIdentities TABLE (
136LOOKUPID BIGINT NOT NULL,
137PRODUCTID BIGINT NOT NULL,
138ISMASTER BIT NOT NULL,
139ISKIT BIT NOT NULL,
140ISREMOTE BIT NOT NULL,
141MASTERPRODUCT BIGINT NOT NULL,
142DISPLAYPRODUCTNUMBER NVARCHAR(70) NOT NULL,
143SEARCHNAME NVARCHAR(20) NULL,
144ITEMID NVARCHAR(20) NOT NULL,
145INVENTDIMID NVARCHAR(20) NOT NULL,
146OFFLINEIMAGE NVARCHAR(MAX) NULL,
147INVENTRECID BIGINT NULL,
148PRICE FLOAT NOT NULL
149)
150
151--MSPOS performance optimization -->
152DECLARE @tvp_CatalogAssortedProductIds_Filtered  [crt].[PRODUCTLOOKUPIDTABLETYPE]
153
154if (@b_SkipVariantExpansion = 1)
155BEGIN
156insert into @tvp_CatalogAssortedProductIds_Filtered
157select * from @tvp_CatalogAssortedProductIds
158WHERE PRODUCTID = LOOKUPID or PRODUCTID in (select recid from @tvp_ProductIds)
159END
160ELSE
161BEGIN
162insert into @tvp_CatalogAssortedProductIds_Filtered
163select * from @tvp_CatalogAssortedProductIds
164END
165--MSPOS performance optimization <--
166
167-- For the filtered identifiers, we can now build the identity for each product.
168INSERT INTO @tbl_ProductIdentities
169SELECT DISTINCT
170ap.LOOKUPID,
171ap.PRODUCTID,
172CASE
173WHEN (([pv].RECID IS NULL) AND ([pv2].RECID IS NOT NULL)) THEN 1
174ELSE 0
175END AS ISMASTER,
176CASE
177WHEN ([rk].RECID IS NULL) THEN 0
178ELSE 1
179END AS ISKIT,
180ap.ISREMOTE,
181CASE
182WHEN (([pv].RECID IS NULL) AND ([pv2].RECID IS NOT NULL)) THEN 0 -- IsMaster
183ELSE ap.LOOKUPID
184END AS MASTERPRODUCT,
185erp.DISPLAYPRODUCTNUMBER,
186erp.SEARCHNAME,
187COALESCE(it.ITEMID, idc.ITEMID) AS ITEMID,
188ISNULL(idc.INVENTDIMID, '') AS INVENTDIMID,
189NULL AS OFFLINEIMAGE,
190it.RECID AS INVENTRECID,
191--MSPOS performance optimization -->
192--orig: CASE [ap].PRODUCTID
193--orig: WHEN [ictap].PRODUCT THEN [ictap].AMOUNT_SUM
194--orig: ELSE [icbp].AMOUNT_SUM
195--orig: END AS PRICE
1960 as PRICE -- added
197--orig: FROM @tvp_CatalogAssortedProductIds ap
198from @tvp_CatalogAssortedProductIds_Filtered ap --added
199-- If b_SkipVariantExpansion was set to true and the provided product ID was a variant ID then we need to return just master (or standalone one) and the only
200-- variant whose IDs were passed, all other variants should not be returned.
201--orig: INNER JOIN @tvp_ProductIds ids ON (@b_SkipVariantExpansion = 0 OR (@b_SkipVariantExpansion = 1 AND ([ids].RECID = [ap].PRODUCTID) OR ([ap].PRODUCTID = [ap].LOOKUPID)))
202--MSPOS performance optimization <--
203INNER JOIN [ax].ECORESPRODUCT erp ON [erp].RECID = [ap].PRODUCTID
204LEFT OUTER JOIN [ax].INVENTTABLE it ON [it].PRODUCT = [ap].PRODUCTID AND [it].DATAAREAID = @nvc_DataAreaId
205LEFT OUTER JOIN [ax].INVENTDIMCOMBINATION idc ON [idc].DISTINCTPRODUCTVARIANT = [ap].PRODUCTID AND [idc].DATAAREAID = @nvc_DataAreaId
206LEFT OUTER JOIN [ax].ECORESDISTINCTPRODUCTVARIANT pv ON [pv].RECID = [ap].PRODUCTID
207LEFT OUTER JOIN [ax].ECORESDISTINCTPRODUCTVARIANT pv2 ON [pv2].PRODUCTMASTER = [ap].PRODUCTID
208LEFT OUTER JOIN [ax].RETAILKIT rk ON [rk].PRODUCTMASTER = [ap].PRODUCTID
209--MSPOS performance optimization -->
210--orig: LEFT OUTER JOIN [crt].[ITEMCHANNELTRADEAGREEMENTPRICEVIEW] ictap WITH (NOEXPAND) ON [ictap].PRODUCT = [ap].LOOKUPID AND @dt_ChannelDate BETWEEN [ictap].FROMDATE AND [ictap].TODATE AND [ictap].CHANNEL = @bi_ChannelId
211--orig: LEFT OUTER JOIN [crt].[ITEMCHANNELBASEPRICEVIEW] icbp WITH (NOEXPAND) ON [icbp].PRODUCT = [ap].LOOKUPID AND [icbp].CHANNEL = @bi_ChannelId
212ORDER BY
213--orig:    CASE [ap].PRODUCTID
214--orig:        WHEN [ictap].PRODUCT THEN [ictap].AMOUNT_SUM
215--orig:        ELSE [icbp].AMOUNT_SUM
216--orig: END
217[ap].[PRODUCTID] --added
218--MSPOS performance optimization <--
219
220-- Populate final set of product ids for which further data should be retrieved
221INSERT @tvp_ExpandedWorkingIdSet (RECID)
222SELECT DISTINCT [pids].PRODUCTID
223FROM @tbl_ProductIdentities pids
224
225--Update the OfflineImage column with image value, if any
226IF @b_IsOnlineSearch = 0
227BEGIN
228UPDATE pid
229SET pid.OFFLINEIMAGE = CAST(N'' AS XML).value('xs:base64Binary(xs:hexBinary(sql:column("[img].[MediumSize]")))', 'NVARCHAR(MAX)')
230FROM @tbl_ProductIdentities pid
231INNER JOIN [ax].ECORESPRODUCTIMAGE img
232ON pid.INVENTRECID = [img].REFRECORD AND [img].DEFAULTIMAGE = 1
233END
234
235-- retrieve product identities
236SELECT
237[piv].PRODUCTID AS 'RECID',
238[piv].LOOKUPID,
239[piv].ISMASTER,
240[piv].ISKIT,
241[piv].ISREMOTE,
242[piv].MASTERPRODUCT,
243[piv].ITEMID,
244[piv].INVENTDIMID,
245[piv].DISPLAYPRODUCTNUMBER,
246[piv].SEARCHNAME,
247[piv].OFFLINEIMAGE
248FROM @tbl_ProductIdentities piv
249ORDER BY [piv].LOOKUPID, /*MSPOS fix*/ [piv].ISMASTER DESC /*MSPOS fix*/, [piv].MASTERPRODUCT -- Ensure that the product master is always first in the group as CRT code depends on this.
250
251SET @i_ReturnCode = @i_ReturnCode + 1;
252
253-- retrieve the distinct product variant identifiers for each variant in our expanded set
254INSERT INTO @tvp_DistinctProductVariantIds (RECID)
255SELECT DISTINCT [piv].[PRODUCTID]
256FROM @tbl_ProductIdentities piv
257WHERE [piv].ISMASTER = 0 AND [piv].MASTERPRODUCT <> 0
258
259IF NOT EXISTS(SELECT NULL FROM @tvp_DistinctProductVariantIds)
260BEGIN
261SELECT
262NULL AS ITEMID,
263NULL AS VARIANTID,
264NULL AS INVENTDIMID,
265NULL AS DISTINCTPRODUCTVARIANT,
266NULL AS SIZEID,
267NULL AS COLORID,
268NULL AS STYLEID,
269NULL AS CONFIGID,
270NULL AS COLOR,
271NULL AS SIZE,
272NULL AS STYLE,
273NULL AS CONFIG
274END
275ELSE
276BEGIN
277-- retrieve the product variant identities
278SELECT
279[vv].ITEMID,
280[vv].VARIANTID,
281[vv].INVENTDIMID,
282[vv].DISTINCTPRODUCTVARIANT,
283[vv].SIZEID,
284[vv].COLORID,
285[vv].STYLEID,
286[vv].CONFIGID,
287[vv].COLOR,
288[vv].SIZE,
289[vv].STYLE,
290[vv].CONFIG
291FROM [crt].GETVARIANTS(@bi_ChannelId, @dt_ChannelDate, @tvp_DistinctProductVariantIds) vv
292END
293
294SET @i_ReturnCode = @i_ReturnCode + 1;
295IF (@i_DataLevel < 1)
296RETURN @i_ReturnCode;
297
298-- Retrieve the product rules
299SELECT
300[prv].PRODUCTID AS 'RECID',
301[prv].BLOCKEDONPOS,
302[prv].DATEBLOCKED,
303[prv].DATETOACTIVATEITEM,
304[prv].DATETOBEBLOCKED,
305[prv].KEYINGINPRICE,
306[prv].KEYINGINQTY,
307[prv].MUSTKEYINCOMMENT,
308[prv].QTYBECOMESNEGATIVE,
309[prv].SCALEITEM,
310[prv].ZEROPRICEVALID,
311[prv].ISSERIALIZED,
312[prv].ISACTIVEINSALESPROCESS,
313[prv].DEFAULTUNITOFMEASURE
314FROM [crt].PRODUCTRULESVIEW prv
315INNER JOIN @tvp_ExpandedWorkingIdSet ids ON [ids].RECID = [prv].PRODUCTID
316WHERE [prv].DATAAREAID = @nvc_DataAreaId
317
318SET @i_ReturnCode = @i_ReturnCode + 1;
319
320IF (@i_DataLevel < 2)
321BEGIN
322-- Extract attribute schema of the first-class(Name, Description, Image) attributes for this data level.
323SELECT
324[pamv].ATTRIBUTE,
325[pamv].KEYNAME,
326CASE [pamv].KEYNAME
327WHEN @ProductNameAttributeName THEN 5  /* Text */
328WHEN @ProductDescriptionAttributeName THEN 5  /* Text */
329WHEN @ProductImageAttributeName THEN 41  /* Image */
330END AS DATATYPE,
331[era].NAME,
332[erat].LANGUAGE,
333[erat].FRIENDLYNAME,
3340 AS PRODUCT,
3350 AS CATALOG,
3360 AS ATTRIBUTEGROUP,
3370 AS ATTRIBUTEGROUPTYPE
338FROM [ax].RETAILPUBPRODUCTATTRIBUTECHANNELMETADATA pamv
339INNER JOIN [ax].ECORESATTRIBUTE era ON [era].RECID = [pamv].ATTRIBUTE
340LEFT JOIN [ax].ECORESATTRIBUTETRANSLATION erat ON [erat].ATTRIBUTE = [pamv].ATTRIBUTE
341WHERE [pamv].HOSTCHANNEL = @bi_ChannelId
342AND ([pamv].KEYNAME = @ProductNameAttributeName
343OR [pamv].KEYNAME = @ProductDescriptionAttributeName
344OR [pamv].KEYNAME = @ProductImageAttributeName)
345
346SET @i_ReturnCode = @i_ReturnCode + 1;
347
348-- Retrieve product attribute values for the first-class(Name, Description, Image) attributes for this data level.
349SELECT
350[gpavv].ATTRIBUTE,
351[gpavv].VALUE,
352[gpavv].ISREFERENCE,
3530 AS PRODUCT,
3540 AS CATEGORY,
3550 AS CATALOG,
3560 AS DISTANCE,
3572 AS SOURCE,  /* DefaultProductAttributeGroupDefaultValue */
358NULL AS BOOLEANVALUE,
359NULL AS CURRENCYVALUE,
360NULL AS CURRENCYCODE,
361NULL AS DATETIMEVALUE,
362NULL AS DATETIMEVALUETZID,
363NULL AS FLOATVALUE,
364NULL AS FLOATUNITSYMBOL,
365NULL AS INTVALUE,
366NULL AS INTUNITSYMBOL,
367[ertv].TEXTVALUE,
368[tvt].LANGUAGE,
369[tvt].TRANSLATION
370FROM [crt].DEFAULTPRODUCTATTRIBUTEGROUPDEFAULTVALUEVIEW gpavv
371INNER JOIN [ax].ECORESTEXTVALUE ertv ON [ertv].RECID = [gpavv].VALUE
372LEFT JOIN [crt].GETTEXTVALUETRANSLATION(@bi_ChannelId) tvt ON [tvt].VALUE = [ertv].RECID
373WHERE [gpavv].CHANNEL = @bi_ChannelId
374AND
375(@bi_CatalogId = 0 OR EXISTS(
376SELECT 1
377FROM [ax].RETAILPUBCATALOG
378WHERE ORIGIN = @bi_CatalogId AND ENABLESNAPSHOT = 0
379))
380
381UNION ALL
382
383SELECT
384[rav].ATTRIBUTE,
3850 AS VALUE,
3861 AS ISREFERENCE,
387[ept].PRODUCT,
3880 AS CATEGORY,
3890 AS CATALOG,
3900 AS DISTANCE,
3915 AS SOURCE,  /* StandardProductAttributeValue */
392CONVERT(BIT, 0) AS BOOLEANVALUE,
393CONVERT(NUMERIC(32,16), 0) AS CURRENCYVALUE,
394'' AS CURRENCYCODE,
395CONVERT(DATETIME, 0) AS DATETIMEVALUE,
396CONVERT(INT, 0) AS DATETIMEVALUETZID,
397CONVERT(NUMERIC(32,16), 0) AS FLOATVALUE,
398'' AS FLOATUNITSYMBOL,
399CONVERT(INT, 0) AS INTVALUE,
400'' AS INTUNITSYMBOL,
401'' AS TEXTVALUE,
402[ept].LANGUAGEID AS LANGUAGE,
403CASE [rsa].STANDARDATTRIBUTEID
404WHEN 2 /* ProductName */ THEN [ept].NAME
405WHEN 4 /* Description */ THEN [ept].DESCRIPTION
406END AS TRANSLATION
407FROM @tvp_ExpandedWorkingIdSet AS ids
408INNER JOIN [ax].RETAILSTANDARDATTRIBUTE rsa ON [rsa].STANDARDATTRIBUTEID = 2 /* ProductName */ OR [rsa].STANDARDATTRIBUTEID = 4 /* Description */
409INNER JOIN [crt].REFERENCEATTRIBUTEVIEW rav ON [rav].REFERENCEDATTRIBUTE = [rsa].ATTRIBUTE
410INNER JOIN [ax].ECORESPRODUCTTRANSLATION ept ON [ept].PRODUCT = [ids].RECID
411INNER JOIN [crt].CHANNELLANGUAGESVIEW clv ON [clv].CHANNEL = @bi_ChannelId AND [clv].LANGUAGEID = [ept].LANGUAGEID
412
413SET @i_ReturnCode = @i_ReturnCode + 1;
414
415-- exit here if the data level is minimal
416RETURN @i_ReturnCode
417END
418ELSE BEGIN
419-- Retrieve the attribute schema.
420SELECT
421[pas].ATTRIBUTE,
422[pamv].KEYNAME,
423[pamv].DATATYPE AS DATATYPE,
424[era].NAME,
425[erat].LANGUAGE,
426[erat].FRIENDLYNAME,
427[pas].PRODUCT,
428[pas].[CATALOG],
429[pas].ATTRIBUTEGROUP,
430[pas].ATTRIBUTEGROUPTYPE
431FROM [crt].GETPRODUCTATTRIBUTESCHEMAS(@bi_ChannelId, @bi_CatalogId, @dt_ChannelDate, @tvp_ExpandedWorkingIdSet, @tvp_DummyId) AS pas
432INNER JOIN [crt].PRODUCTATTRIBUTEMETADATAVIEW pamv ON [pamv].ATTRIBUTE = [pas].ATTRIBUTE
433INNER JOIN [ax].ECORESATTRIBUTE era ON [era].RECID = [pas].ATTRIBUTE
434LEFT JOIN [ax].ECORESATTRIBUTETRANSLATION erat ON [erat].ATTRIBUTE = [pas].ATTRIBUTE
435WHERE [pamv].CHANNEL = @bi_ChannelId
436
437SET @i_ReturnCode = @i_ReturnCode + 1;
438
439DECLARE @nvc_Locale NVARCHAR(7)
440SET @nvc_Locale = 'en-us'
441
442IF object_id('ax.systemparameters', 'U') IS NOT NULL
443BEGIN
444IF (SELECT COUNT(*) FROM [ax].SYSTEMPARAMETERS WHERE [KEY] = 0) = 1
445BEGIN
446SET @nvc_Locale = (SELECT TOP 1 SYSTEMLANGUAGEID FROM [ax].SYSTEMPARAMETERS WHERE [KEY] = 0)
447END
448END
449
450-- Retrieve product attributes
451SELECT
452[pavfn].[ATTRIBUTE],
453[pavfn].[VALUE],
454[pavfn].[DISPLAYORDER],
455[pavfn].[ISREFERENCE],
456[pavfn].[PRODUCT],
457[pavfn].[CATEGORY],
458[pavfn].[CATALOG],
459[pavfn].[DISTANCE],
460[pavfn].[SOURCE],
461[pavfn].[BOOLEANVALUE],
462[pavfn].[CURRENCYVALUE],
463[pavfn].[CURRENCYCODE],
464[pavfn].[DATETIMEVALUE],
465[pavfn].[DATETIMEVALUETZID],
466[pavfn].[FLOATVALUE],
467[pavfn].[FLOATUNITSYMBOL],
468[pavfn].[INTVALUE],
469[pavfn].[INTUNITSYMBOL],
470[pavfn].[TEXTVALUE],
471CASE
472WHEN [pavfn].[TEXTVALUE] IS NOT NULL AND ([pavfn].[LANGUAGE] IS NULL OR [pavfn].[LANGUAGE] = '')
473THEN @nvc_Locale
474ELSE [pavfn].[LANGUAGE]
475END
476AS [LANGUAGE],
477CASE
478WHEN [pavfn].[TEXTVALUE] IS NOT NULL AND ([pavfn].[LANGUAGE] IS NULL OR [pavfn].[LANGUAGE] = '')
479THEN [pavfn].[TEXTVALUE]
480ELSE [pavfn].[TRANSLATION]
481END
482AS [TRANSLATION]
483FROM [crt].GETPRODUCTATTRIBUTEVALUES(@bi_ChannelId, @bi_CatalogId, @dt_ChannelDate, @b_IsForwardLooking, @tvp_ExpandedWorkingIdSet, @tvp_DummyId) AS pavfn
484INNER JOIN [crt].PRODUCTATTRIBUTEMETADATAVIEW pamv ON [pamv].ATTRIBUTE = [pavfn].[ATTRIBUTE]
485WHERE [pamv].CHANNEL = @bi_ChannelId AND [pamv].DATATYPE NOT IN (40, 41)  -- Exclude Video, Image
486
487UNION ALL
488
489-- This is to return images for all languages available for a channel as images cannot be translated in AX.
490SELECT
491[pavfn].[ATTRIBUTE],
492[pavfn].[VALUE],
493[pavfn].[DISPLAYORDER],
494[pavfn].[ISREFERENCE],
495[pavfn].[PRODUCT],
496[pavfn].[CATEGORY],
497[pavfn].[CATALOG],
498[pavfn].[DISTANCE],
499[pavfn].[SOURCE],
500[pavfn].[BOOLEANVALUE],
501[pavfn].[CURRENCYVALUE],
502[pavfn].[CURRENCYCODE],
503[pavfn].[DATETIMEVALUE],
504[pavfn].[DATETIMEVALUETZID],
505[pavfn].[FLOATVALUE],
506[pavfn].[FLOATUNITSYMBOL],
507[pavfn].[INTVALUE],
508[pavfn].[INTUNITSYMBOL],
509[pavfn].[TEXTVALUE],
510[clv].LANGUAGEID AS [LANGUAGE],
511
512[pavfn].[TRANSLATION]
513FROM [crt].GETPRODUCTATTRIBUTEVALUES(@bi_ChannelId, @bi_CatalogId, @dt_ChannelDate, @b_IsForwardLooking, @tvp_ExpandedWorkingIdSet, @tvp_DummyId) AS pavfn
514INNER JOIN [crt].PRODUCTATTRIBUTEMETADATAVIEW pamv ON [pamv].ATTRIBUTE = [pavfn].[ATTRIBUTE]
515INNER JOIN [crt].CHANNELLANGUAGESVIEW clv ON [clv].CHANNEL = @bi_ChannelId
516WHERE [pamv].CHANNEL = @bi_ChannelId AND [pamv].DATATYPE IN (40, 41)  -- Retrieve only Video, Image
517
518
519SET @i_ReturnCode = @i_ReturnCode + 1;
520END
521
522-- Retrieve active or all catalogs depending on the value of @bi_CatalogId.
523SELECT DISTINCT
524pcp.CATALOG,
525ap.PRODUCTID AS 'PRODUCT',
526CASE
527WHEN ap.VALIDFROM > pc.VALIDFROM THEN ap.VALIDFROM
528ELSE pc.VALIDFROM
529END AS 'VALIDFROM',
530CASE
531WHEN pc.VALIDTO < ap.VALIDTO THEN pc.VALIDTO
532ELSE ap.VALIDTO
533END AS 'VALIDTO'
534FROM @tvp_CatalogAssortedProductIds ap
535INNER JOIN [ax].RETAILPUBCATALOGPRODUCT pcp ON pcp.PRODUCT = ap.PRODUCTID OR pcp.PRODUCT = ap.LOOKUPID
536INNER JOIN [ax].RETAILPUBCATALOG pc ON pc.ORIGIN = pcp.CATALOG
537INNER JOIN [crt].PUBCATALOGCHANNELVIEW ccv ON ccv.CATALOG = pcp.CATALOG
538WHERE ccv.CHANNEL = @bi_ChannelId AND (ccv.CATALOG = @bi_CatalogId OR @bi_CatalogId = 0)
539AND pc.VALIDTO >= @dt_ChannelDate AND (@b_IsForwardLooking = 1 OR pc.VALIDFROM <= @dt_ChannelDate)
540
541UNION ALL
542
543-- Retrieve assortment dates when no catalogs have been requested.
544SELECT
5450 AS 'CATALOG',
546ap.PRODUCTID AS 'PRODUCT',
547ap.VALIDFROM,
548ap.VALIDTO
549FROM @tvp_CatalogAssortedProductIds ap
550WHERE @bi_CatalogId = 0 AND (@b_IsForwardLooking = 1 OR @dt_ChannelDate BETWEEN ap.VALIDFROM AND ap.VALIDTO)
551ORDER BY VALIDFROM DESC
552
553SET @i_ReturnCode = @i_ReturnCode + 1;
554
555-- Retrieve product categories
556SELECT DISTINCT
557categories.PRODUCT,
558categories.CATEGORY,
559categories.[CATALOG],
5600 AS ISPRIMARY
561FROM [crt].GETPRODUCTCATEGORIES(@bi_ChannelId, @bi_CatalogId, @dt_ChannelDate, @b_IsForwardLooking, 1 /* @b_ProductIdsPassed */, @tvp_ExpandedWorkingIdSet, 0 /* @b_CategoryIdsPassed */, @tvp_DummyId) categories
562
563SET @i_ReturnCode = @i_ReturnCode + 1;
564
565IF (@i_DataLevel < 3)
566RETURN @i_ReturnCode;
567
568-- Retrieve product relations
569SELECT
570[prfn].PRODUCT,
571[prfn].RELATEDPRODUCT,
572[prfn].PRODUCTRELATIONTYPE,
573[prfn].[CATALOG],
574[prfn].EXCLUSION
575FROM [crt].GETPRODUCTRELATIONS(@bi_ChannelId, @bi_CatalogId, @dt_ChannelDate, @tvp_LookupIds) AS prfn
576
577SET @i_ReturnCode = @i_ReturnCode + 1;
578
579RETURN @i_ReturnCode;
580END;