WITH RELATED_STORES ( STORE_ID ) AS (
SELECT RELATEDSTORE_ID
FROM STOREREL
WHERE STATE = 1
AND STRELTYP_ID = -4
AND STORE_ID = ${param.storeId} ),
CATALOG_CATENTRY ( CATENTRY_ID ) AS (
SELECT G.CATENTRY_ID
FROM CATGPENREL G,
CATENTRY E,
STORECAT SC,
RELATED_STORES RS
WHERE G.CATENTRY_ID = E.CATENTRY_ID
AND G.CATALOG_ID = ${param.catalogId}
AND E.MARKFORDELETE = 0
AND G.CATALOG_ID = SC.CATALOG_ID
AND RS.STORE_ID = SC.STOREENT_ID )
SELECT CER.CATENTRY_ID_CHILD CATENTRY_ID,
LISTAGG(TO_CHAR(P.PRICE),', ') WITHIN GROUP (ORDER BY FILTER.USAGE DESC) PRICE,
LISTAGG(P.CURRENCY,'###') CURRENCY,
LISTAGG(FILTER.USAGE,', ') USAGE
FROM OFFER O,
OFFERPRICE P,
( SELECT A.CATENTRY_ID_PARENT CATENTRY_ID_PARENT,
A.CATENTRY_ID_CHILD CATENTRY_ID_CHILD
FROM CATENTREL A,
CATALOG_CATENTRY B,
CATENTRY C
WHERE A.CATENTRY_ID_PARENT = B.CATENTRY_ID
AND C.CATENTRY_ID = A.CATENTRY_ID_CHILD
AND C.MARKFORDELETE = 0
AND A.CATRELTYPE_ID IN ('PRODUCT_VARIANT')
UNION
SELECT A.CATENTRY_ID CATENTRY_ID_PARENT,
A.CATENTRY_ID CATENTRY_ID_CHILD
FROM CATALOG_CATENTRY A ) CER,
CATENTRY C,
( SELECT MAX(O.PRECEDENCE) MAX_PRECEDENCE,
O.TRADEPOSCN_ID,
O.CATENTRY_ID,
TS.STTPCUSG_ID USAGE
FROM OFFER O,
TRADEPOSCN T,
STORECENT S,
STORETPC TS,
RELATED_STORES RS
WHERE T.TRADEPOSCN_ID = TS.TRADEPOSCN_ID
AND S.STOREENT_ID = TS.STOREENT_ID
AND S.STOREENT_ID = RS.STORE_ID
AND O.TRADEPOSCN_ID = T.TRADEPOSCN_ID
AND O.CATENTRY_ID = S.CATENTRY_ID
AND (O.STARTDATE IS NULL OR CURRENT_TIMESTAMP > O.STARTDATE) AND (O.ENDDATE IS NULL OR O.ENDDATE > CURRENT_TIMESTAMP)
AND O.PUBLISHED = 1
AND (O.MINIMUMQUANTITY IN (1,0) OR O.MINIMUMQUANTITY IS NULL)
GROUP BY O.TRADEPOSCN_ID, O.CATENTRY_ID, TS.STTPCUSG_ID ) FILTER
WHERE O.TRADEPOSCN_ID = FILTER.TRADEPOSCN_ID
AND O.CATENTRY_ID = FILTER.CATENTRY_ID
AND O.PRECEDENCE = FILTER.MAX_PRECEDENCE
AND (O.STARTDATE IS NULL OR CURRENT_TIMESTAMP > O.STARTDATE)
AND (O.ENDDATE IS NULL OR O.ENDDATE > CURRENT_TIMESTAMP)
AND O.PUBLISHED = 1
AND O.OFFER_ID = P.OFFER_ID
AND C.CATENTRY_ID = CER.CATENTRY_ID_CHILD ${extCatentryAndSQL}
AND C.CATENTTYPE_ID <> 'BundleBean'
AND O.CATENTRY_ID = CER.CATENTRY_ID_PARENT
AND ( ( CER.CATENTRY_ID_PARENT = CER.CATENTRY_ID_CHILD )
OR NOT EXISTS
( SELECT 1
FROM OFFER,
TRADEPOSCN,
STORECENT,
CATGRPTPC
WHERE OFFER.CATENTRY_ID = CER.CATENTRY_ID_CHILD
AND OFFER.PUBLISHED = 1
AND OFFER.TRADEPOSCN_ID = TRADEPOSCN.TRADEPOSCN_ID
AND OFFER.CATENTRY_ID = STORECENT.CATENTRY_ID
AND CATGRPTPC.STORE_ID = STORECENT.STOREENT_ID
AND TRADEPOSCN.TRADEPOSCN_ID = CATGRPTPC.TRADEPOSCN_ID ) )
GROUP BY CER.CATENTRY_ID_CHILD
ORDER BY CATENTRY_ID
LIMIT ${param.offset}, ${param.pageSize}
A continuación, el conjunto de resultados se pasa al procesador FindPricesFromDatabase para su transformación, utilizando la siguiente tabla para correlacionar el campo de la base de datos devuelto por el SQL anterior con un campo de índice en el índice Producto:
| Campo de índice Nombre |
Tipo de campo de índice |
Descripción: |
| Propiedades |
| prices/list/<currency>/price |
float |
Lista los precios en la moneda proporcionada; se correlaciona con la tabla OFFERPRICE |
| prices/offer/<currency>/price |
float |
Precio de contrato o precio de oferta estándar en la moneda proporcionada; se correlaciona con la tabla OFFERPRICE |