New Latin America site script
#
# Import the CMS Site configuration for the Latin America store
#
#
$storeUid=latam
$StoreName=Safety | Latin America
$defaultCurrency=USD
$lang=en
$defaultLanguage=en
$deliveryCountries=US
$languages=en
$Currency=MXP
$contentCatalog=latamContentCatalog
$contentCatalogName= Latin America Content Catalog
$productCatalog=latamProductCatalog
$productCatalogName= Latin America Product Catalog
$classificationCatalog=Classification
$solrFacetSearchConfiguration=Index
$contentCV=catalogVersion(CatalogVersion.catalog(Catalog.id[default=$contentCatalog]),CatalogVersion.version[default=Staged])[default=$contentCatalog:Staged]
#$contentCV=catalogVersion(CatalogVersion.catalog(Catalog.id[default=$contentCatalog]),CatalogVersion.version[default=Online])[default=$contentCatalog:Online]
# These define sets of components that can fit into similar slots
INSERT_UPDATE ContentCatalog ; id[unique=true] ; name[lang=$lang]
; $contentCatalog ; $contentCatalogName
$languages=en,es_CO,es,pt
INSERT_UPDATE CatalogVersion ; catalog(id)[unique=true] ; version[unique=true] ; active ; defaultCurrency(isocode) ; languages(isoCode) ; territories(isocode)
; $contentCatalog ; Staged ; false ; $defaultCurrency ; $languages ; $deliveryCountries
; $contentCatalog ; Online ; true ; $defaultCurrency ; $languages ; $deliveryCountries
$languages=en
INSERT_UPDATE Catalog ; id[unique=true] ; name[lang=$lang]
; $productCatalog ; $productCatalogName
INSERT_UPDATE CatalogVersion ; catalog(id)[unique=true] ; version[unique=true] ; active ; languages(isoCode) ; readPrincipals(uid) ; writePrincipals(uid) ; territories(isocode)
; $productCatalog ; Staged ; false ; $languages ; employeegroup ; productmanagergroup,productmanager ; $deliveryCountries
; $productCatalog ; Online ; true ; $languages ; employeegroup ; productmanagergroup,productmanager ; $deliveryCountries
$regionalGlobalProductCatalog=regionalGlobalProductCatalog
$regionalGlobalProductCatalogName=Regional Global Product Catalog
INSERT_UPDATE Catalog ; id[unique=true] ; name[lang=$lang]
; $regionalGlobalProductCatalog ; $regionalGlobalProductCatalogName
INSERT_UPDATE CatalogVersion ; catalog(id)[unique=true] ; version[unique=true] ; active ; languages(isoCode) ; readPrincipals(uid) ; writePrincipals(uid) ; territories(isocode)
; $regionalGlobalProductCatalog ; Staged ; false ; $languages ; employeegroup ; productmanagergroup,productmanager ; $deliveryCountries
# Base Store
INSERT_UPDATE BaseStore ; uid[unique=true] ; catalogs(id) ; name[lang=$lang] ; defaultCurrency(isocode) ; defaultLanguage(isoCode)
; $storeUid ; $productCatalog,$classificationCatalog ; LATAM ; $defaultCurrency ; $defaultLanguage
# Create CMS Site
INSERT_UPDATE CMSSite ; uid[unique=true] ; name[lang=$lang] ; locale[lang=$lang] ; globalRegion(code) ; siteCountry(isocode) ; companyCode ; stores(uid) ; contentCatalogs(id) ; defaultCatalog(id) ; defaultLanguage(isoCode) ; urlPatterns ; active ; previewURL ; regionalSite ; countryToSalesOrg[map-delimiter=|][keytovalue-delimiter=->] ;
; $storeUid ; $StoreName ; en ; NA ; MX ; MX03 ; $storeUid ; $contentCatalog ; $productCatalog ; $defaultLanguage ; (?i)^https?://[^/]+(/[^?]*)?\?(.*\&)?(site=latam)(|\&.*)$,https?://latam.* ; true ; /?site=latam ; true ; mx->MX03|ar->AR01|br->BR01|cl->CL01|co->CO01|pe->PE01 ;
# we need to add the website info in properties file website.latam.https=https://latam.local.safety.com
UPDATE CMSSite ; uid[unique=true] ; redirectURL ; active[default=false]
# ; $storeUid ; http://latam.local.safety.com/ ; true
; $storeUid ; https://latam.dev2.safety.com/ ; true
UPDATE CMSSite ; uid[unique=true] ; commerceEnabledFlag ; warrantyPointsEnabled ; creditCardEnabled ; siteAccessGroupList
; $storeUid ; true ; false ; false ; PE01,AR01,CL01,CO01,MX03,BR01
### Update Sites to assign Index
UPDATE CMSSite ; uid[unique=true] ; solrFacetSearchConfiguration(name)
; $storeUid ; $solrFacetSearchConfiguration
$lang=en
INSERT_UPDATE AsCategoryAwareSearchProfile ; code[unique=true] ; name[lang=$lang] ; indexType ; catalogVersion(catalog(id),version)[unique=true]
#LATAM
; defaultLATAM-category ; Default LATAM - Category ; ProductType ; latamProductCatalog:Staged
; defaultLATAM-category ; Default LATAM - Category ; ProductType ; latamProductCatalog:Online
$facetSearchConfigName=Index
$catalogVersions=catalogVersions(catalog(id),version);
## Update/append the supported country catalogs in the Index
UPDATE SolrFacetSearchConfig ; name[unique=true] ; $catalogVersions[mode=append]
; $facetSearchConfigName ; latamProductCatalog:Online
INSERT_UPDATE CountryRegionMapping ; code[unique=true] ; Region ; supportedCountries(uid) ; email ; defaultSalesOrg
### ; LA ; Latin America ; pe,ar,cl,mx,co ; ; MX03
; PE ; Peru ; pe ; ; PE01
; AR ; Argentina ; ar ; ; AR01
; MX ; Mexico ; mx ; ; MX03
; CO ; Columbia ; co ; ; CO01
; CL ; Chile ; cl ; ; CL01
; BR ; Brazil ; br ; ; BR01
### ; ZA ; South Africa ; za ; ;
### ; UK ; United Kingdom ; gb ; ;
### ; SE ; Sweden ; se ; ;
### ; PL ; Poland ; pl ; ;
### ; NL ; Netherlands ; nl ; ;
### ; IT ; Italy ; it ; ;
### ; IN ; India ; in ; ;
### ; FR ; France ; fr ; ;
### ; ES ; Spain ; es ; ;
### ; DE ; Germany ; de ; ;
### ; AU ; Australia ; au ; ;
### ; AE ; Middle East ; ae ; ;
### ; ROA ; Rest of Asia ; id,jp,my,sg,th ; ;
### ; NA ; North America ; ca,us ; ;
#Updating the starting page in site with new Homepage
UPDATE CMSSite ; uid[unique=true] ; startingPage(uid,$contentCV)
; $storeUid ; 2homepage
$productCatalog=latamProductCatalog
$catalogVersion=catalogversion(catalog(id[default=$productCatalog]),version[default='Staged'])[unique=true,default=$productCatalog:Staged]
#$catalogVersion=catalogversion(catalog(id[default=$productCatalog]),version[default='Online'])[unique=true,default=$productCatalog:Online]
$lang = en
#################### https://latam.local.safety.com/p/assembly/AVO1 #################
INSERT_UPDATE AssemblyViewObject ; uid[unique=true] ; title ; description ; imageURL ; htmlMap
; AVO1 ; Cairns® N5A New Yorker™ Leather Fire Helmet ; The Cairns® N5A New Yorker™ leather helmet is handcrafted and meets current OSHA requirements. Offers the high quality and appearance that generations of firefighters have trusted since 1836. Each New Yorker leather helmet is hand-crafted, shaped, stitched, and trimmed to last for years with minimum maintenance. ; http://s7d9.scene7.com/is/image/minesafetyappliances/3S-Basis-Plus-Assembly?scl=1 ; "<map name=""image_map"" id=""image_map"">
<area target="""" alt=""flamsen"" title=""flamsen"" href=""#nm2"" coords=""629,49,22"" shape=""circle"">
<area target="""" alt=""face shield"" title=""face shield"" href=""#nm3"" coords=""115,453,24"" shape=""circle"">
<area target="""" alt=""face shield bracket"" title=""face shield bracket"" href=""#nm4"" coords=""44,109,23"" shape=""circle"">
<area target="""" alt=""doohickey"" title=""doohickey"" href=""#nm5"" coords=""297,469,16"" shape=""circle"">
<area target="""" alt=""little plastic thing"" title=""little plastic thing"" href=""#nm6"" coords=""295,502,16"" shape=""circle"">
<area target="""" alt=""diaphragm of some sort"" title=""diaphragm of some sort"" href=""#nm7"" coords=""410,64,21"" shape=""circle"">
<area target="""" alt=""retainer ring"" title=""retainer ring"" href=""#nm8"" coords=""374,116,22"" shape=""circle"">
<area target="""" alt=""the bit that screws in"" title=""the bit that screws in"" href=""#nm9"" coords=""443,20,19"" shape=""circle"">
<area target="""" alt=""straps to hold it onto your head"" title=""straps to hold it onto your head"" href=""#nm10"" coords=""633,416,20"" shape=""circle"">
<area target="""" alt=""buckle"" title=""buckle"" href=""#nm11"" coords=""420,427,22"" shape=""circle"">
</map>"
INSERT_UPDATE AssemblyViewRow ; uid[unique=true] ; imageRefNo ; sortNo ; partNo ; assemblyViewObject(uid)
; AVO1-AVR1 ; 2 ; 2 ; 50004-13 ; AVO1
; AVO1-AVR2 ; 3 ; 3 ; 50004-9 ; AVO1
; AVO1-AVR3 ; 4 ; 4 ; 50008-9 ; AVO1
; AVO1-AVR4 ; 5 ; 5 ; 10175107 ; AVO1
; AVO1-AVR5 ; 6 ; 6 ; 10175108 ; AVO1
; AVO1-AVR6 ; 7 ; 7 ; 10175109 ; AVO1
; AVO1-AVR7 ; 8 ; 8 ; LT01P-0002 ; AVO1
; AVO1-AVR8 ; 9 ; 9 ; LT01P-0003 ; AVO1
; AVO1-AVR9 ; 10 ; 10 ; L59RP ; AVO1
; AVO1-AVR10 ; 11 ; 11 ; L59RP-0001 ; AVO1
##UPDATE product ; $catalogVersion ; code[unique=true] ; assemblyViewObject(uid)
## ; ; 000140006700002050 ; AVO1
UPDATE CMSSite ; uid[unique=true] ; theme(code) ; affiliateCode
; $storeUid ; ; MX03
UPDATE CMSSite ; uid[unique=true] ; sapDefaultLanguage(isocode)
; $storeUid ; $defaultLanguage
INSERT_UPDATE Employee ; UID[unique=true] ; description ; name ; groups(uid) ; sessionLanguage(isocode) ; sessionCurrency(isocode) ; hmcLoginDisabled ; loginDisabled ;
; global2latamuser ; Global 2 Latin America User ; Global 2 Latin America User ; globalsyncgroup ; en ; USD ; true ; true ;
INSERT_UPDATE Employee ; UID[unique=true] ; description ; name ; groups(uid) ; sessionLanguage(isocode) ; sessionCurrency(isocode) ; password
; contentsyncuser ; Content User ; Content User ; contentsyncgroup ; en ; USD ; N5TK2OVL>b9|K)i
INSERT_UPDATE SearchRestriction ; code[unique=true] ; name[lang=en] ; principal(UID) ; restrictedType(code) ; active ; generate ; query
; global2latam ; Global 2 Latin America ; global2latamuser ; Product ; true ; true ; (({varianttype} is not null ) or ({varianttype} is null and ({code} IN ( {{select {product.code} FROM {Product AS product JOIN Product2SalesOrg AS pso ON {pso.source}={product.PK} JOIN SalesOrg AS so ON {so.PK}={pso.target} and {so.code} IN ('PE01','CL01','AR01','CO01','MX03','BR01') } }} ) )))
$regionalGlobalProductCatalog=regionalGlobalProductCatalog
$latamproductCatalog=latamProductCatalog
$sourceProductCV=sourceVersion(catalog(id[default=$regionalGlobalProductCatalog]),version[default='Staged'])[unique=true,default='$regionalGlobalProductCatalog:Staged']
$targetProductCV=targetVersion(catalog(id[default=$latamproductCatalog]),version[default='Staged'])[unique=true,default='$latamproductCatalog:Staged']
INSERT_UPDATE CatalogVersionSyncJob ; code[unique=true] ; sessionuser(UID) ; sessionlanguage(isocode) ; sessioncurrency(isocode) ; $sourceProductCV ; $targetProductCV
; syncRegionalGlobalproduct2latam:Staged->Staged ; global2latamuser ; en ; USD ; ;
$sourceProductCV=sourceVersion(catalog(id[default=$productCatalog]),version[default='Staged'])[unique=true,default='$productCatalog:Staged']
$targetProductCV=targetVersion(catalog(id[default=$productCatalog]),version[default='Online'])[unique=true,default='$productCatalog:Online']
INSERT_UPDATE CatalogVersionSyncJob ; code[unique=true] ; $sourceProductCV ; $targetProductCV ;
; sync latamproductCatalog:Staged->Online ; ; ;
$globalProductCatalog=globalProductCatalog
$regionalGlobalProductCatalog=regionalGlobalProductCatalog
$sourceProductCV=sourceVersion(catalog(id[default=$globalProductCatalog]),version[default='Staged'])[unique=true,default='$globalProductCatalog:Staged']
$targetProductCV=targetVersion(catalog(id[default=$regionalGlobalProductCatalog]),version[default='Staged'])[unique=true,default='$regionalGlobalProductCatalog:Staged']
INSERT_UPDATE CatalogVersionSyncJob ; code[unique=true] ; sessionuser(UID) ; sessionlanguage(isocode) ; sessioncurrency(isocode) ; $sourceProductCV ; $targetProductCV
; syncglobalBaseproduct2RegionalGlobal:Staged->Staged ; syncglobalBaseproduct2RegionalGlobaluser ; en ; USD ; ;
INSERT_UPDATE Employee ; UID[unique=true] ; description ; name ; groups(uid) ; sessionLanguage(isocode) ; sessionCurrency(isocode) ; hmcLoginDisabled ; loginDisabled ;
; syncglobalBaseproduct2RegionalGlobaluser ; Global 2 Regional Global Base product User ; Global 2 Regional Global Base product User ; globalsyncgroup ; en ; USD ; true ; true ;
INSERT_UPDATE SearchRestriction ; code[unique=true] ; name[lang=en] ; principal(UID) ; restrictedType(code) ; active ; generate ; query
; syncglobalBaseproduct2RegionalGlobal ; Global 2 Regional Global Base product ; syncglobalBaseproduct2RegionalGlobaluser ; Product ; true ; true ; ({varianttype} is not null )
$threads=4
$contentCatalog=latamContentCatalog
$sourceContentCV=sourceVersion(catalog(id[default=$contentCatalog]),version[default='Staged'])[unique=true,default='$contentCatalog:Staged']
$targetContentCV=targetVersion(catalog(id[default=$contentCatalog]),version[default='Online'])[unique=true,default='$contentCatalog:Online']
$globalContentCatalog=globalContentCatalog
INSERT_UPDATE CatalogVersionSyncJob ; code[unique=true] ; $sourceContentCV ; $targetContentCV ; nodeID[default=3] ; createNewItems ; removeMissingItems ; rootTypes(code)[mode=append]
; sync latamContentCatalog:Staged->Online ; ; ; ; true ; true ; CMSItem,CMSRelation
$sourceContentCV=sourceVersion(catalog(id[default=$globalContentCatalog]),version[default='Staged'])[unique=true,default='$globalContentCatalog:Staged']
$targetContentCV=targetVersion(catalog(id[default=$contentCatalog]),version[default='Staged'])[unique=true,default='$contentCatalog:Staged']
INSERT_UPDATE CatalogVersionSyncJob ; code[unique=true] ; sessionuser(UID) ; sessionlanguage(isocode) ; sessioncurrency(isocode) ; $sourceContentCV ; $targetContentCV ;
; syncGlobalContent2latam:Staged->Staged ; contentsyncuser ; en ; USD ; ;
UPDATE CatalogVersionSyncJob ; code[unique=true] ; syncPrincipals(UID) ; removeMissingItems[default=false] ; maxThreads[default=$threads] ; maxSchedulerThreads[default=$threads]
; syncGlobalContent2latam:Staged->Staged ; contentsyncuser ; true ; ;
INSERT_UPDATE JobSearchRestriction ; code[unique=true] ; type(code) ; job(code) ; query
; globalContent2latam ; CMSItem ; syncGlobalContent2latam\:Staged->Staged ; ({display} = 1)
$threads=4
$contentCatalog=latamContentCatalog
$sourceContentCV=sourceVersion(catalog(id[default=$contentCatalog]),version[default='Online'])[unique=true,default='$contentCatalog:Online']
$targetContentCV=targetVersion(catalog(id[default=$contentCatalog]),version[default='Staged'])[unique=true,default='$contentCatalog:Staged']
INSERT_UPDATE CatalogVersionSyncJob ; code[unique=true] ; $sourceContentCV ; $targetContentCV ; nodeID[default=3] ; createNewItems ; removeMissingItems ; rootTypes(code)[mode=append]
; sync latamContentCatalog:Online->Staged ; ; ; ; true ; true ; CMSItem,CMSRelation
$productCatalog=latamProductCatalog
$catalogVersion=catalogversion(catalog(id[default=$productCatalog]),version[default='Staged'])[unique=true,default=$productCatalog:Staged]
#$catalogVersion=catalogversion(catalog(id[default=$productCatalog]),version[default='Online'])[unique=true,default=$productCatalog:Online]
$supercategories=supercategories(code, $catalogVersion)
$approved=approvalstatus(code)[default='approved']
#$baseProduct=baseProduct(code, catalogVersion(catalog(id[default='$productCatalog']),version[default='Staged']))
#$variantType=varianttype(code)[default='VariantProduct']
###UPDATE Product[batchmode=true] ; itemtype(code)[unique=true] ; $catalogVersion ; forceDisplay[default=1] ; $supercategories ; $approved
### ; Product ; ; 1 ; 101 ;
###UPDATE Product[batchmode=true] ; itemtype(code)[unique=true] ; $catalogVersion ; $approved ; quickOrderOnly[default=0]
### ; VariantProduct ; ; ;
INSERT_UPDATE UpdateCatalogCronJob ; code[unique=true] ; job(code) ; active[default=false] ; sendEmail ; emailNotificationTemplate(code) ; emailAddress ; sessionUser(uid) ; sessionLanguage(isocode) ; sessionCurrency(isocode) ; ftpHost ; ftpUsername ; ftpPassword ; ftpSourceDir ; ftpArchiveDir ; remoteFileName
; regionalDeltaProductLoad ; regionalDeltaProductLoad ; true ; false ; UpdateCatalogNotificationTemplate ; DBritt@liveareacx.com ; admin ; en ; USD ; 10.6.4.36 ; minesafety ; ftppass ; /Dev/toacquity ; /Dev/toacquity/archive ; MM
INSERT_UPDATE ServicelayerJob ; code[unique=true] ; springId[unique=true]
; regionalDeltaProductLoad ; RegionalupdateCatalogJobPerformable
INSERT_UPDATE Trigger ; cronJob(code)[unique=true] ; second ; minute ; hour ; day ; month ; year ; relative ; active[default=false] ; maxAcceptableDelay
#Trigger at 04:00 am every day
; regionalDeltaProductLoad ; 0 ; 0 ; 4 ; -1 ; -1 ; -1 ; false ; false ; -1
###$productCatalog=latamProductCatalog
###$catalogVersion=catalogversion(catalog(id[default=$productCatalog]),version[default='Staged'])[unique=true,default=$productCatalog:Staged]
###INSERT_UPDATE SalesOrg ; code[unique=true] ; products(code,$catalogVersion)
### ; PE01
### ; CL01
### ; AR01
### ; CO01
### ; MX03
### ; BR01
###$productCatalog=latamProductCatalog
###$catalogVersion=catalogversion(catalog(id[default=$productCatalog]),version[default='Staged'])[unique=true,default=$productCatalog:Staged]
###$baseProduct=baseProduct(code, catalogVersion(catalog(id[default='$productCatalog']),version[default='Staged']))
###UPDATE VariantProduct ; code[unique=true] ; $catalogVersion ; $baseProduct ; salesOrgs(code)
### ; AR01_50004-13 ; ; 000140006700002050 ; MX03,CO01,AR01
###UPDATE VariantProduct ; code[unique=true] ; $catalogVersion ; $baseProduct ; salesOrgs(code)[mode=append]
### ; AR01_50004-13 ; ; 000140006700002050 ; CL01
###
###UPDATE VariantProduct ; code[unique=true] ; $catalogVersion ; $baseProduct ; salesOrgs(code)
### ; CO01_10022021 ; ; 000060007200001007 ; MX03,CO01
###
###UPDATE VariantProduct ; code[unique=true] ; $catalogVersion ; $baseProduct ; salesOrgs(code)[mode=append]
### ; CO01_10022021 ; ; 000060007200001007 ; PE01,CL01,MX03,CO01,AR01
###UPDATE VariantProduct ; code[unique=true] ; $catalogVersion ; $baseProduct ; salesOrgs(code)[mode=append]
### ; MX03_815175 ; ; 000100001000001221 ; CL01
###
### Query to look for the product based on product code and sales org.
### select {product.PK} FROM {Product AS product JOIN Product2SalesOrg AS pso ON {pso.source}={product.PK} JOIN SalesOrg AS so ON {so.PK}={pso.target} and {product.code}='815175' and {so.code}='PE01'}
###
### Query to get the sales org available for a single product
### select {so.code} FROM {Product AS product JOIN Product2SalesOrg AS pso ON {pso.source}={product.PK} JOIN SalesOrg AS so ON {so.PK}={pso.target} and {product.code}='815175'}
###
### Query to get product code and PK based on only sales org.
### select {product.code},{product.PK} FROM {Product AS product JOIN Product2SalesOrg AS pso ON {pso.source}={product.PK} JOIN SalesOrg AS so ON {so.PK}={pso.target} and {so.code}='PE01'}
###
### Query to see the site affiliateCode and sapDefaultLanguage
### select {uid},{affiliateCode},{sapDefaultLanguage},{Language.isoCode} from {CMSSite},{Language} where {sapDefaultLanguage}={Language.PK}
###
### -------------------------------------- PDP product check query based on product code and SalesOrg input -------------------------------------- ###
### FlexiQuery : select {product.PK} FROM {VariantProduct AS product JOIN Product2SalesOrg AS pso ON {pso.source}={product.PK} JOIN SalesOrg AS so ON {so.PK}={pso.target} and lower({product.code})=lower(?code) and {so.code}=?salesOrgCode and {product.activeFlag}=1 and lower({product.catalogVersion})=lower(?catalogVersion) }
### Parameters : {catalogVersion=CatalogVersionModel (8796355297881@1), code=710332, salesOrgCode=MX03}
### FlexiQuery with params : select {product.PK} FROM {VariantProduct AS product JOIN Product2SalesOrg AS pso ON {pso.source}={product.PK} JOIN SalesOrg AS so ON {so.PK}={pso.target} and lower({product.code})=lower('710332') and {so.code}='MX03' and {product.activeFlag}=1 and lower({product.catalogVersion})=lower('8796355297881') }
### HAC SQL equivalent : SELECT item_t0.PK FROM _products item_t0 JOIN _product2salesorg item_t1 ON item_t1.SourcePK = item_t0.PK JOIN _salesorg item_t2 ON item_t2.PK = item_t1.TargetPK AND item_t2.p_code ='MX03' AND lower( item_t0.Code )=lower('710332') AND item_t0.p_activeflag =1 AND lower( item_t0.p_catalogversion )=lower('8796355297881') WHERE ((item_t0.TypePkString=? AND item_t1.TypePkString=? AND item_t2.TypePkString=? ))
### Tranlated SQL Query : SELECT item_t0.PK FROM _products item_t0 JOIN _product2salesorg item_t1 ON item_t1.SourcePK = item_t0.PK JOIN _salesorg item_t2 ON item_t2.PK = item_t1.TargetPK AND item_t2.p_code =? AND lower( item_t0.Code )=lower(?) AND item_t0.p_activeflag =1 AND lower( item_t0.p_catalogversion )=lower(?) WHERE ((item_t0.TypePkString=? AND (( ( item_t0.TypePkString in (SELECT item_t3.PK FROM _composedtypes item_t3 WHERE ( item_t3.InternalCode = 'VariantProduct') AND (item_t3.TypePkString=? )) )or ( item_t0.p_productconfiguratorurl is not null) OR ( EXISTS (SELECT item_t4.PK FROM _products item_t4 WHERE ( item_t4.p_baseproduct = item_t0.PK AND item_t4.p_activeflag = 1) AND (item_t4.TypePkString=? AND (( item_t4.p_onlinedate IS NULL OR item_t4.p_onlinedate <= ?) AND ( item_t4.p_offlinedate IS NULL OR item_t4.p_offlinedate >= ?))AND (( EXISTS (SELECT item_t5.PK FROM _products item_t5 WHERE ( item_t4.p_baseproduct = item_t5.PK and item_t5.p_activeflag = 1) AND (item_t5.TypePkString IN (?,?,?,?,?,?,?,?,?,?,?,?,?) AND ((( item_t5.p_onlinedate IS NULL OR item_t5.p_onlinedate <= ?) AND ( item_t5.p_offlinedate IS NULL OR item_t5.p_offlinedate >= ?)) AND (( item_t5.p_activeflag = 1)) AND ( item_t5.p_catalogversion IN (?,?,?)) AND ( item_t5.p_approvalstatus = 8796096954459 ) AND (item_t5.TypePkString IN ( ?,?,?,?,?,?,?,?,?,?,?,? ) OR (( item_t5.p_activeflag = 1)))) )) ))AND (( item_t4.p_activeflag = 1))AND (( item_t4.p_activeflag = 1))AND ( item_t4.p_catalogversion IN (?,?,?))AND ( item_t4.p_approvalstatus = 8796096954459 ))) ) ))AND (( item_t0.p_onlinedate IS NULL OR item_t0.p_onlinedate <= ?) AND ( item_t0.p_offlinedate IS NULL OR item_t0.p_offlinedate >= ?))AND (( EXISTS (SELECT item_t6.PK FROM _products item_t6 WHERE ( item_t0.p_baseproduct = item_t6.PK and item_t6.p_activeflag = 1) AND (item_t6.TypePkString IN (?,?,?,?,?,?,?,?,?,?,?,?,?) AND ((( ( item_t6.TypePkString in (SELECT item_t7.PK FROM _composedtypes item_t7 WHERE ( item_t7.InternalCode = 'VariantProduct') AND (item_t7.TypePkString=? )) )or ( item_t6.p_productconfiguratorurl is not null) OR ( EXISTS (SELECT item_t8.PK FROM _products item_t8 WHERE ( item_t8.p_baseproduct = item_t6.PK AND item_t8.p_activeflag = 1) AND (item_t8.TypePkString=? AND (( item_t8.p_onlinedate IS NULL OR item_t8.p_onlinedate <= ?) AND ( item_t8.p_offlinedate IS NULL OR item_t8.p_offlinedate >= ?))AND (( item_t8.p_activeflag = 1))AND (( item_t8.p_activeflag = 1))AND ( item_t8.p_catalogversion IN (?,?,?))AND ( item_t8.p_approvalstatus = 8796096954459 ))) ) )) AND (( item_t6.p_onlinedate IS NULL OR item_t6.p_onlinedate <= ?) AND ( item_t6.p_offlinedate IS NULL OR item_t6.p_offlinedate >= ?)) AND (( item_t6.p_activeflag = 1)) AND ( item_t6.p_catalogversion IN (?,?,?)) AND ( item_t6.p_approvalstatus = 8796096954459 ) AND (item_t6.TypePkString IN ( ?,?,?,?,?,?,?,?,?,?,?,? ) OR (( item_t6.p_activeflag = 1)))) )) ))AND (( item_t0.p_activeflag = 1))AND (( item_t0.p_activeflag = 1))AND ( item_t0.p_catalogversion IN (?,?,?))AND ( item_t0.p_approvalstatus = 8796096954459 ) AND item_t1.TypePkString=? AND item_t2.TypePkString=? ))
### Parameters : [MX03, 710332, CatalogVersionModel (8796355297881@1), 8796130607186, 8796100853842, 8796130607186, Fri Jun 11 00:00:00 EDT 2021, Fri Jun 11 00:00:00 EDT 2021, 8796131491922, 8796289925202, 8796430401618, 8796100919378, 8796131524690, 8796388720722, 8796158689362, 8796158656594, 8796158722130, 8796130607186, 8796158623826, 8796158591058, 8796094857298, Fri Jun 11 00:00:00 EDT 2021, Fri Jun 11 00:00:00 EDT 2021, PropertyValue:8796355232345, PropertyValue:8796093383257, PropertyValue:8796355297881, 8796131491922, 8796289925202, 8796430401618, 8796100919378, 8796131524690, 8796388720722, 8796158689362, 8796158656594, 8796158722130, 8796158623826, 8796158591058, 8796094857298, PropertyValue:8796355232345, PropertyValue:8796093383257, PropertyValue:8796355297881, Fri Jun 11 00:00:00 EDT 2021, Fri Jun 11 00:00:00 EDT 2021, 8796131491922, 8796289925202, 8796430401618, 8796100919378, 8796131524690, 8796388720722, 8796158689362, 8796158656594, 8796158722130, 8796130607186, 8796158623826, 8796158591058, 8796094857298, 8796100853842, 8796130607186, Fri Jun 11 00:00:00 EDT 2021, Fri Jun 11 00:00:00 EDT 2021, PropertyValue:8796355232345, PropertyValue:8796093383257, PropertyValue:8796355297881, Fri Jun 11 00:00:00 EDT 2021, Fri Jun 11 00:00:00 EDT 2021, PropertyValue:8796355232345, PropertyValue:8796093383257, PropertyValue:8796355297881, 8796131491922, 8796289925202, 8796430401618, 8796100919378, 8796131524690, 8796388720722, 8796158689362, 8796158656594, 8796158722130, 8796158623826, 8796158591058, 8796094857298, PropertyValue:8796355232345, PropertyValue:8796093383257, PropertyValue:8796355297881, 8796437446738, 8796437381202]
### -------------------------------------- Query to get all the SalesOrgs by passing product code ,this is mostly used during indexing process -------------------------------------- ###
### FlexiQuery : select {so.PK} FROM {VariantProduct AS product JOIN Product2SalesOrg AS pso ON {pso.source}={product.PK} JOIN SalesOrg AS so ON {so.PK}={pso.target} and lower({product.code})=lower(?code) and {product.activeFlag}=1 and lower({product.catalogVersion})=lower(?catalogVersion) }
### Parameters : {catalogVersion=CatalogVersionModel (8796355297881@1), code=50004-13}
### FlexiQuery with params : select {so.PK} FROM {VariantProduct AS product JOIN Product2SalesOrg AS pso ON {pso.source}={product.PK} JOIN SalesOrg AS so ON {so.PK}={pso.target} and lower({product.code})=lower('50004-13') and {product.activeFlag}=1 and lower({product.catalogVersion})=lower('8796355297881') }
### HAC SQL equivalent : SELECT item_t2.PK FROM _products item_t0 JOIN _product2salesorg item_t1 ON item_t1.SourcePK = item_t0.PK JOIN _salesorg item_t2 ON item_t2.PK = item_t1.TargetPK AND lower( item_t0.Code )=lower('50004-13') AND item_t0.p_activeflag =1 AND lower( item_t0.p_catalogversion )=lower('8796355297881') WHERE ((item_t0.TypePkString=? AND item_t1.TypePkString=? AND item_t2.TypePkString=? ))
### Tranlated SQL Query : SELECT item_t2.PK FROM _products item_t0 JOIN _product2salesorg item_t1 ON item_t1.SourcePK = item_t0.PK JOIN _salesorg item_t2 ON item_t2.PK = item_t1.TargetPK AND lower( item_t0.Code )=lower(?) AND item_t0.p_activeflag =1 AND lower( item_t0.p_catalogversion )=lower(?) WHERE ((item_t0.TypePkString=? AND item_t1.TypePkString=? AND item_t2.TypePkString=? ))
### Parameters : [50004-13, CatalogVersionModel (8796355297881@1), 8796130607186, 8796437446738, 8796437381202]
### -------------- Query to get all the base products in a catalog --------- ###
### select * from {product} where {varianttype} is not null and lower({product.catalogVersion})=lower('8796355297881')
### -------------- Query to get all the variant products in a catalog --------- ###
### select * from {product} where {varianttype} is null and lower({product.catalogVersion})=lower('8796355297881')
### -------------- Query to get all the affiliateCode from CMSSite --------- ###
### select {affiliateCode} from {CMSSite}
###------ Groovy script to delete the products from a catalog by catalogversion Id (ex:8796355265113) -----------------
###import de.hybris.platform.servicelayer.search.FlexibleSearchQuery;
###
###int count = 100;
###String query = "select {PK} from {product} WHERE {catalogversion} =8796355265113"
###FlexibleSearchQuery flexiQuery = new FlexibleSearchQuery(query);
###flexiQuery.setCount(count);
###
###flexibleSearchService.search(flexiQuery).result.each {
### modelService.remove(it)
###}
### These salesorg updates are only required in the local env and they are upto date in higher env.
###$storeUid=pe
###UPDATE CMSSite ; uid[unique=true] ; affiliateCode
### ; $storeUid ; PE01
###$storeUid=ar
###UPDATE CMSSite ; uid[unique=true] ; affiliateCode
### ; $storeUid ; AR01
###$storeUid=cl
###UPDATE CMSSite ; uid[unique=true] ; affiliateCode
### ; $storeUid ; CL01
###$storeUid=co
###UPDATE CMSSite ; uid[unique=true] ; affiliateCode
### ; $storeUid ; CO01
###$storeUid=mx
###UPDATE CMSSite ; uid[unique=true] ; affiliateCode
### ; $storeUid ; MX03
### Base product codes which can be tested and verified in Latin America site as part of the feed MM file that was supplied.
### 000060007200001007
### 000060001300001000
### 000080001800003010
### 000080001800001002
### 000370007000001010
### 000370007000001030
### flexiblesearch against CMSItem and then find the duplicates.
### select {UID},{name},count(1) from {CMSItem} where {CatalogVersion} = 8796355232345 group by {UID},{name} having count(1) > 1
### select {UID},{name},count(1) from {CMSItem} where {CatalogVersion} = 8796453536345 group by {UID},{name} having count(1) > 1
### select {UID},{name},count(1) from {CMSItem} where {CatalogVersion} = 8796453503577 group by {UID},{name} having count(1) > 1
Comments
Post a Comment