GOC Retail

Pricing in Y2 has 4 major components

1. Price Lists
2. Application Periods
3. Stores
4. Items (Styles/SKUs)

1. Price Lists

The first point for pricing is to look at the store. The store can have a Tax Including or a Tax Excluding price assigned. In most cases only 1 field will be populated, usualy the Tax Incl Selling price. For regions like the US a tax excl price is added, because of the complex nature of the US tax configuration.

2. Application Periods

A price list can then have different application periods. An application period is a new list which can store prices, these application lists can have dates assigned, and even promotional codes applied. An application period can be assigned to any price list.

3. Stores

Pricing within each price list application period can be applied to all stored (allocated to that price list) or to individual stores. This allows you to manage a special in a single store without needing to setup a new price list. 

This however can complicate management as it is an another area within pricing to check when making changes.

4. Items (Styles/SKUs)

Pricing is applied on either a Style or a SKU level, and often a combination of both. A SKU is a dimensioned item under a Style. There is no options or configurations required for this, it is controlled automatically when loading in pricing. 

Every style will have a price, but SKUs do not need a price. If  a price is loaded at SKU level then this price is applied to the Style as well. 

This allows for a Style to have a price, but a specific dimension to be priced differently, e.g. when running out a colour within a style. 

SQL To get Price

There is a webservice for getting the price, or Y2 can be used directly, but sometimes an SQL extract is needed for reporting or other purposes. 

The SQL is configured in the following way:

1. Select is done from Article, this guarantees that if a SKU exists then it will yield a line, even if no price exists
2. First left join is done to the store. This is for 2 reasons, the first is if Store based pricing is used the correct price will be returned, the second is to get the price list for that store
3. The next join is to TARIFMODE. This is a list of all price lists - here the join is done to either the tax incl or tax excl price list on the store. There is no checking of Application periods as all periods should be returned
4. The next join is to retrieve the actual prices. This will ensure that any item which has a price in that price list is returned.

The WHERE clause is important and has the following

1. First where portion is for identifying the products. This will return in some circumstances multiple results. This is so that a price for a SKU is returned as well as the price for the Style (to be used if no SKU pricing exists)
2. The next is the Dates, the start and finish dates of a price is considered because this will determine if the price is in effect or not. Thus any query must have a date to pull 'active prices'
3. The last section of where is just to pull active prices. 

The ORDER BY is critical here, as this will ensure that the most recent price is used, the store pricing is prioritized over general pricing and that the SKU pricing is prioritized over Style pricing.

Below is a sample SQL. This SQL can be simplified if being used within different queries - e.g. if the GA_ARTICLE is known then all the barcode logic can be eliminated.

select GA_CODEARTICLE, GA_ARTICLE, GA_CODEBARRE, GA_LIBELLE, ET_LIBELLE,
ET_TYPETARIFVTEHT AS TAXEXCLLIST, ET_TYPETARIFVTETTC AS TAXINCLLIST, GFM_PERTARIF,GF_DATEDEBUT,GF_DATEFIN,
GF_PRIXUNITAIRE, GF_DEVISE, GF_DEPOT, GA_STATUTART
FROM ARTICLE
LEFT JOIN ETABLISS ON ET_ETABLISSEMENT="212" 
LEFT JOIN TARIFMODE ON (GFM_TYPETARIF=ET_TYPETARIFVTEHT OR GFM_TYPETARIF=ET_TYPETARIFVTETTC)
LEFT JOIN TARIF ON GA_ARTICLE=GF_ARTICLE AND GF_TARFMODE=GFM_TARFMODE AND (GF_DEPOT=ET_ETABLISSEMENT OR GF_DEPOT='' OR GF_DEPOT IS NULL)
WHERE 
  ( 
    ( GA_ARTICLE IN 
       (SELECT GA_ARTICLE FROM ARTICLE WHERE 
           ((GA_CODEARTICLE LIKE "%7325701619560%" AND (GA_STATUTART="GEN" OR GA_STATUTART="UNI")) OR 
           (GA_CODEBARRE LIKE "%7325701619560%" AND GA_STATUTART="DIM"))
       )
    ) OR
    ( GA_CODEARTICLE IN 
       (SELECT GA_CODEARTICLE FROM ARTICLE WHERE (GA_CODEBARRE LIKE "%7325701619560%" AND GA_STATUTART="DIM"))    
       AND GA_STATUTART IN ("GEN", "UNI")
    ) 
  )
  AND GF_DATEDEBUT <= "20200128" AND GF_DATEFIN >= "20200128"
  AND GF_BORNEINF <= 0 AND GF_BORNESUP >= 0 AND GF_FERME = "-"
ORDER BY GF_DATEDEBUT DESC, GF_DEPOT DESC, GA_STATUTART ASC

Below is an SQL to get only a single active price.

select TOP 1 (GF_PRIXUNITAIRE)
FROM ARTICLE
LEFT JOIN ETABLISS ON ET_ETABLISSEMENT="212" 
LEFT JOIN TARIFMODE ON (GFM_TYPETARIF=ET_TYPETARIFVTEHT OR GFM_TYPETARIF=ET_TYPETARIFVTETTC)
LEFT JOIN TARIF ON GA_ARTICLE=GF_ARTICLE AND GF_TARFMODE=GFM_TARFMODE AND (GF_DEPOT=ET_ETABLISSEMENT OR GF_DEPOT='' OR GF_DEPOT IS NULL)
WHERE 
  (
    ( GA_ARTICLE IN 
       (SELECT GA_ARTICLE FROM ARTICLE WHERE 
           ((GA_CODEARTICLE LIKE "%7325701619560%" AND (GA_STATUTART="GEN" OR GA_STATUTART="UNI")) OR 
           (GA_CODEBARRE LIKE "%7325701619560%" AND GA_STATUTART="DIM"))
       )
    ) OR
    ( GA_CODEARTICLE IN 
       (SELECT GA_CODEARTICLE FROM ARTICLE WHERE (GA_CODEBARRE LIKE "%7325701619560%" AND GA_STATUTART="DIM"))    
       AND GA_STATUTART IN ("GEN", "UNI")
    ) 
  )
  AND GF_DATEDEBUT <= "20200128" AND GF_DATEFIN >= "20200128"
  AND GF_BORNEINF <= 0 AND GF_BORNESUP >= 0 AND GF_FERME = "-"
ORDER BY GF_DATEDEBUT DESC, GF_DEPOT DESC, GA_STATUTART ASC