Canalizaciones de índice de productos de Ingest

1. Cargando el documento de producto
2. Insertar las propiedades de nivel de producto en sus elementos
3. Desplegar las propiedades de nivel de elemento en su padre
4. Crear jerarquía de catálogo y rutas de navegación para cada categoría de ventas

1. Crear un documento de producto
2. Aplicar anulación de nivel de sitio
3. Asociar suscripciones
4. Asociar relación de producto padre e hijo
5. Aplicar relación de conjunto de productos para autorización de contrato
6. Generar asociación de comercialización
7. Asociación de adjuntos de producto e imágenes angulares
8. Creación de jerarquía de catálogo para navegación
9. Asociar atributos
10. Propagar atributos de nivel de elemento a sus padres
11. Insertar las propiedades de nivel de producto en sus elementos
12. Asociación de atributos descriptivos secundarios
13. Asociar adjuntos secundarios

​SELECT C.CATENTRY_ID, C.MEMBER_ID, C.CATENTTYPE_ID, C.PARTNUMBER, C.MARKFORDELETE, C.BUYABLE, C.STATE,
	   COALESCE(D.LANGUAGE_ID, L.LANGUAGE_ID) LANGUAGE_ID,
           COALESCE(C.MFPARTNUMBER, VP.MFPARTNUMBER) MFPARTNUMBER,
           COALESCE(C.MFNAME,VP.MFNAME) MFNAME,
           COALESCE(C.STARTDATE,VP.STARTDATE) STARTDATE,
           COALESCE(C.ENDDATE,VP.ENDDATE) ENDDATE,
           COALESCE(D.NAME, PD.NAME) NAME,
           COALESCE(D.SHORTDESCRIPTION, PD.SHORTDESCRIPTION) SHORTDESCRIPTION,
           COALESCE(D.KEYWORD, PD.KEYWORD) KEYWORD,
           D.THUMBNAIL, D.FULLIMAGE, D.PUBLISHED, 
	   R.CATALOG_ID, L.LOCALENAME, S.STOREENT_ID, PD.THUMBNAIL PARENT_THUMBNAIL, PD.FULLIMAGE PARENT_FULLIMAGE
     FROM CATGPENREL R, LANGUAGE L, STORECAT S, CATENTRY C
     ${TI_DELTA_JOIN_QUERY}
     LEFT OUTER JOIN CATENTDESC D ON (D.CATENTRY_ID=C.CATENTRY_ID AND D.LANGUAGE_ID = ${param.langId})
     LEFT OUTER JOIN CATENTREL PV ON (C.CATENTRY_ID = PV.CATENTRY_ID_CHILD AND PV.CATRELTYPE_ID IN ('PRODUCT_VARIANT', 'PRODUCT_ITEM')
                                                                                  AND PV.CATENTRY_ID_CHILD <> PV.CATENTRY_ID_PARENT)
     LEFT OUTER JOIN CATENTRY VP ON (VP.CATENTRY_ID = PV.CATENTRY_ID_PARENT)
     LEFT OUTER JOIN CATENTDESC PD ON (PV.CATENTRY_ID_PARENT=PD.CATENTRY_ID AND PD.LANGUAGE_ID = ${param.langId})
     WHERE R.CATALOG_ID = ${param.catalogId}
           AND R.CATALOG_ID IN (SELECT CATALOG_ID FROM STORECAT WHERE STOREENT_ID IN
               (SELECT RELATEDSTORE_ID FROM STOREREL WHERE STATE = 1 AND STRELTYP_ID = -4 AND STORE_ID = ${param.storeId}))
           AND R.CATENTRY_ID = C.CATENTRY_ID AND C.MARKFORDELETE = 0
           AND S.CATALOG_ID = R.CATALOG_ID AND L.LANGUAGE_ID = ${param.langId} ${extCatentryAndSQL}
         ORDER BY CATENTRY_ID
            OFFSET ${param.offset} ROWS FETCH NEXT ${param.pageSize} ROWS ONLY      

SELECT C.CATENTRY_ID, O.LANGUAGE_ID, O.NAME, O.SHORTDESCRIPTION, O.KEYWORD, O.FULLIMAGE, O.THUMBNAIL, L.LOCALENAME
           FROM CATENTRY C ${TI_DELTA_JOIN_QUERY}, CATENTDESCOVR O, LANGUAGE L
           WHERE C.CATENTRY_ID = O.CATENTRY_ID AND O.LANGUAGE_ID = ${param.langId} AND L.LANGUAGE_ID = O.LANGUAGE_ID AND C.MARKFORDELETE = 0
                   AND C.CATENTRY_ID IN 
                  (SELECT R.CATENTRY_ID FROM CATGPENREL R
    	              WHERE R.CATALOG_ID = ${param.catalogId} AND R.CATALOG_ID IN (SELECT CATALOG_ID FROM STORECAT WHERE STOREENT_ID IN
                       (SELECT RELATEDSTORE_ID FROM STOREREL WHERE STATE = 1 AND STRELTYP_ID = -4 AND STORE_ID = ${param.storeId}))) ${extCatentryAndSQL}	 

SELECT C.CATENTRY_ID, S.DISALLOW_REC_ORDER, T.SUBSCPTYPE_ID
		FROM CATENTRY C ${TI_DELTA_JOIN_QUERY}, CATENTSUBS S, SUBSCPTYPE T
		WHERE S.SUBSCPTYPE_ID = T.SUBSCPTYPE_ID AND C.CATENTRY_ID = S.CATENTRY_ID AND C.MARKFORDELETE = 0
		    AND C.CATENTRY_ID IN 
		  (SELECT R.CATENTRY_ID FROM CATGPENREL R
		   WHERE R.CATALOG_ID = ${param.catalogId} AND R.CATALOG_ID IN (SELECT CATALOG_ID FROM STORECAT WHERE STOREENT_ID IN
		         (SELECT RELATEDSTORE_ID FROM STOREREL WHERE STATE = 1 AND STRELTYP_ID = -4 AND STORE_ID = ${param.storeId}))) ${extCatentryAndSQL}​     

 SELECT L.CATENTRY_ID_PARENT,
  	     LISTAGG(L.CATENTRY_ID_CHILD, ', ') WITHIN GROUP (ORDER BY L.CATENTRY_ID_CHILD) CATENTRY_ID_CHILD,
	     LISTAGG(TO_CHAR(L.SEQUENCE), ', ') WITHIN GROUP (ORDER BY L.CATENTRY_ID_CHILD) SEQUENCE,
	     LISTAGG(L.CATRELTYPE_ID, '###') WITHIN GROUP (ORDER BY L.CATENTRY_ID_CHILD) CATRELTYPE_ID,
	     LISTAGG(R.CATALOG_ID, '###') WITHIN GROUP (ORDER BY L.CATENTRY_ID_CHILD) CATALOG_ID,
	     NULL AS COMPONENT_ID,
	     NULL AS QUANTITY
	FROM CATENTREL L, CATGPENREL R, CATENTRY C
	${TI_DELTA_JOIN_QUERY}
   WHERE R.CATALOG_ID IN
         (SELECT CATALOG_ID FROM STORECAT
           WHERE STOREENT_ID IN
	             (SELECT RELATEDSTORE_ID FROM STOREREL WHERE STATE = 1 AND STRELTYP_ID = -4 AND STORE_ID = ${param.storeId}))
	 AND R.CATENTRY_ID = C.CATENTRY_ID AND C.MARKFORDELETE = 0 AND C.CATENTRY_ID = L.CATENTRY_ID_PARENT
	 AND C.CATENTTYPE_ID NOT IN ('ItemBean') ${extCatentryAndSQL}
	 AND L.CATRELTYPE_ID NOT IN ('BUNDLE_COMPONENT', 'PACKAGE_COMPONENT')
	 AND L.CATENTRY_ID_CHILD IN (SELECT CATENTRY_ID FROM CATENTRY CE Where CE.CATENTRY_ID = L.CATENTRY_ID_CHILD AND CE.MARKFORDELETE = 0)
   GROUP BY L.CATENTRY_ID_PARENT
   UNION
  SELECT CATENTRY_ID_PARENT,
         LISTAGG(KITCOMP.CATENTRY_ID_CHILD,', ') WITHIN GROUP (ORDER BY KITCOMP.CATENTRY_ID_CHILD) CATENTRY_ID_CHILD,
         LISTAGG(TO_CHAR(KITCOMP.SEQUENCE),', ') WITHIN GROUP (ORDER BY KITCOMP.CATENTRY_ID_CHILD) SEQUENCE,
         LISTAGG(KITCOMP.CATRELTYPE_ID, '###') WITHIN GROUP (ORDER BY KITCOMP.CATENTRY_ID_CHILD) CATRELTYPE_ID,
         LISTAGG(KITCOMP.CATALOG_ID, '###') WITHIN GROUP (ORDER BY KITCOMP.CATENTRY_ID_CHILD) CATALOG_ID,
         LISTAGG(KITCOMP.COMPONENT_ID, ', ') WITHIN GROUP (ORDER BY KITCOMP.CATENTRY_ID_CHILD) COMPONENT_ID,
         LISTAGG(TO_CHAR(KITCOMP.QUANTITY), ', ') WITHIN GROUP (ORDER BY KITCOMP.CATENTRY_ID_CHILD) QUANTITY
    FROM (SELECT L.CATENTRY_ID_PARENT, L.CATENTRY_ID_CHILD, L.SEQUENCE, R.CATALOG_ID, CATENTRY_ID_CHILD COMPONENT_ID, QUANTITY, L.CATRELTYPE_ID
            FROM CATENTREL L, CATGPENREL R, CATENTRY C
			${TI_DELTA_JOIN_QUERY}
           WHERE R.CATALOG_ID IN (SELECT CATALOG_ID FROM STORECAT
                                   WHERE STOREENT_ID IN
                                         (SELECT RELATEDSTORE_ID FROM STOREREL
                                           WHERE STATE = 1 AND STRELTYP_ID = -4 AND STORE_ID = ${param.storeId}))
	         AND R.CATENTRY_ID = C.CATENTRY_ID AND C.MARKFORDELETE = 0 AND C.CATENTRY_ID = L.CATENTRY_ID_PARENT
	         AND L.CATENTRY_ID_CHILD IN (SELECT CATENTRY_ID FROM CATENTRY CE Where CE.CATENTRY_ID = L.CATENTRY_ID_CHILD AND CE.MARKFORDELETE = 0)
		     AND CATRELTYPE_ID in ('BUNDLE_COMPONENT','PACKAGE_COMPONENT') ${extCatentryAndSQL}) KITCOMP
   GROUP BY KITCOMP.CATENTRY_ID_PARENT
  OFFSET ${param.offset} ROWS FETCH NEXT ${param.pageSize} ROWS ONLY             

SELECT C.CATENTRY_ID, R.PRODUCTSET_ID
	  FROM CATENTRY C, PRSETCEREL R
	 WHERE C.CATENTRY_ID = R.CATENTRY_ID AND C.MARKFORDELETE = 0
	   AND C.CATENTRY_ID IN
	       (SELECT G.CATENTRY_ID FROM CATGPENREL G
	         WHERE G.CATALOG_ID = ${param.catalogId} AND G.CATALOG_ID IN
	               (SELECT CATALOG_ID FROM STORECAT WHERE STOREENT_ID IN
	               ​(SELECT RELATEDSTORE_ID FROM STOREREL WHERE STATE = 1 AND STRELTYP_ID = -4 AND STORE_ID = ${param.storeId}))) 
	 ORDER BY R.CATENTRY_ID	

SELECT M.CATENTRY_ID_FROM,
		   LISTAGG(M.MASSOCTYPE_ID, '###') WITHIN GROUP (ORDER BY M.RANK) TYPE,
		   LISTAGG(TO_CHAR(COALESCE(M.RANK, 0)), ', ') WITHIN GROUP (ORDER BY M.RANK) RANK,
		   LISTAGG(TO_CHAR(COALESCE(M.QUANTITY, 0)), ', ') WITHIN GROUP (ORDER BY M.RANK) QUANTITY,
		   LISTAGG(M.CATENTRY_ID_TO, ', ') WITHIN GROUP (ORDER BY M.RANK) CATENTRY_ID_TO
	  FROM MASSOCCECE M ${TI_DELTA_JOIN_QUERY_MASSOCCECE}
	 WHERE M.STORE_ID IN (SELECT RELATEDSTORE_ID FROM STOREREL WHERE STATE = 1 AND STRELTYP_ID = -4 AND STORE_ID = ${param.storeId}) ${extCatentryIdFromAndSQL}
	 GROUP BY M.CATENTRY_ID_FROM

SELECT CE.CATENTRY_ID,
	       LISTAGG(ATCHAST.ATCHAST_ID, ', ') WITHIN GROUP (ORDER BY ATCHAST.ATCHAST_ID) ATCHAST_ID,
	       LISTAGG(ATCHTGT.IDENTIFIER, '###') WITHIN GROUP (ORDER BY ATCHAST.ATCHAST_ID) IDENTIFIER,
               LISTAGG(COALESCE(NULLIF(ATCHRELDSC.NAME,''), ' '),  '###') WITHIN GROUP (ORDER BY ATCHAST.ATCHAST_ID) NAME,
               LISTAGG(COALESCE(NULLIF(ATCHRELDSC.SHORTDESCRIPTION,''), ' '), '###') WITHIN GROUP (ORDER BY ATCHAST.ATCHAST_ID) SHORTDESCRIPTION,
	       LISTAGG(COALESCE(ATCHAST.ATCHASTPATH, ' '),  '###') WITHIN GROUP (ORDER BY ATCHAST.ATCHAST_ID) ATCHASTPATH,
               LISTAGG(COALESCE(NULLIF(ATCHAST.DIRECTORYPATH,''), ' '),  '###') WITHIN GROUP (ORDER BY ATCHAST.ATCHAST_ID) DIRECTORYPATH,
               LISTAGG(COALESCE(NULLIF(ATCHAST.MIMETYPE,''), ' '),  '###') WITHIN GROUP (ORDER BY ATCHAST.ATCHAST_ID) MIMETYPE,
	       LISTAGG(ATCHASTLG.LANGUAGE_ID, ', ') WITHIN GROUP (ORDER BY ATCHAST.ATCHAST_ID) LANGUAGE_ID,
	       LISTAGG(COALESCE(ATCHRLUS.IMAGE, ' '), '###') WITHIN GROUP (ORDER BY ATCHAST.ATCHAST_ID) IMAGE,
	       LISTAGG(COALESCE(ATCHRLUS.IDENTIFIER, ' '), '###') WITHIN GROUP (ORDER BY ATCHAST.ATCHAST_ID) RULENAME,
               LISTAGG(TO_CHAR(ATCHREL.SEQUENCE), ', ') WITHIN GROUP (ORDER BY ATCHAST.ATCHAST_ID) SEQUENCE
	  FROM CATENTRY CE
		   ${TI_DELTA_JOIN_QUERY_ATTCH}
	  	   JOIN ATCHREL ON (ATCHREL.BIGINTOBJECT_ID = CE.CATENTRY_ID)
		   JOIN ATCHOBJTYP ON (ATCHREL.ATCHOBJTYP_ID = ATCHOBJTYP.ATCHOBJTYP_ID AND ATCHOBJTYP.IDENTIFIER = 'CATENTRY')
		   LEFT JOIN ATCHTGT ON (ATCHREL.ATCHTGT_ID = ATCHTGT.ATCHTGT_ID )
		   JOIN ATCHAST ON (ATCHAST.ATCHTGT_ID = ATCHTGT.ATCHTGT_ID)
		   LEFT JOIN ATCHASTLG ON (ATCHASTLG.ATCHAST_ID = ATCHAST.ATCHAST_ID) 
		   LEFT JOIN ATCHRELDSC ON (ATCHRELDSC.ATCHREL_ID = ATCHREL.ATCHREL_ID AND ATCHRELDSC.LANGUAGE_ID= ${param.langId})
		   JOIN ATCHRLUS ON (ATCHREL.ATCHRLUS_ID = ATCHRLUS.ATCHRLUS_ID)
	 WHERE (ATCHASTLG.ATCHASTLG_ID IS NULL OR ATCHASTLG.LANGUAGE_ID=${param.langId})
	   AND CE.CATENTRY_ID IN (SELECT C.CATENTRY_ID FROM CATGPENREL R, CATENTRY C
	                           WHERE R.CATALOG_ID = ${param.catalogId} AND R.CATALOG_ID IN
	                                 (SELECT CATALOG_ID FROM STORECAT WHERE STOREENT_ID IN
	                                 (SELECT RELATEDSTORE_ID FROM STOREREL
	                                   WHERE STATE = 1 AND STRELTYP_ID = -4 AND STORE_ID = ${param.storeId}))
	                             AND R.CATENTRY_ID = C.CATENTRY_ID AND C.MARKFORDELETE = 0 AND C.CATENTTYPE_ID <> 'ItemBean') ${extCatentryAndSQL1a}
	 GROUP BY CE.CATENTRY_ID	 
	       

SELECT C.CATENTRY_ID,
                   LISTAGG(R.CATGROUP_ID, '###') WITHIN GROUP (ORDER BY R.CATGROUP_ID) CATGROUP_ID,
	           LISTAGG(TO_CHAR(R.SEQUENCE), '###') WITHIN GROUP (ORDER BY R.CATGROUP_ID) SEQUENCE
          FROM CATGPENREL R, STORECAT S, CATENTRY C
			${TI_DELTA_JOIN_QUERY}
         WHERE R.CATALOG_ID = ${param.catalogId}
           AND R.CATALOG_ID IN (SELECT CATALOG_ID FROM STORECAT WHERE STOREENT_ID IN
               (SELECT RELATEDSTORE_ID FROM STOREREL WHERE STATE = 1 AND STRELTYP_ID = -4 AND STORE_ID = ${param.storeId}))
           AND R.CATENTRY_ID = C.CATENTRY_ID AND S.CATALOG_ID = R.CATALOG_ID ${extCatentryAndSQL}
         GROUP BY C.CATENTRY_ID ORDER BY C.CATENTRY_ID
              OFFSET ${param.offset} ROWS FETCH NEXT ${param.pageSize} ROWS ONLY  
			
		

• Empieza con la ejecución de la siguiente consulta de Elasticsearch con el índice de Categoría:​

  {
    "stored_fields": [
      "id*",
      "category.*",
      "name.*",
      "path"
    ],
    "size": ${es.pageSize},
    "query": {
      "bool": {
        "must_not": {
          "exists": {
            "field": "category.parent"
          }
        },
        "filter": [
          {
            "term": {
              "id.store": "${param.storeId}"
            }
          },
          {
            "term": {
              "id.catalog": "${param.catalogId}"
            }
          },
          {
            "term": {
              "id.language": "${param.langId}"
            }
          }
        ]
      }
    }
  }​​

• El conjunto de resultados inicial de la consulta de búsqueda anterior devolverá todas las categorías de nivel superior del catálogo especificado. Utilizando todas las categorías hijo de este conjunto de categorías de nivel superior para generar otra consulta de Elasticsearch para buscar las categorías hijo del siguiente nivel del catálogo de ventas actual:

  {
      "stored_fields": [
          "id*",
          "category.*",
          "name.*"​​,
           
      ],
      "size": 10000,
      "query": {
          "bool": {
              "must": {
                  "match_all": {}
              },
              "filter": [
                  {
                      "terms": {
                          "category.parent": [
                              "10020", ...
                          ]
                      }
                  },
                  {
                      "term": {
                          "id.store": "1"
                      }
                  },
                  {
                      "term": {
                          "id.catalog": "10502"
                      }
                  },
                  {
                      "term": {
                          "id.language": "-1"
                      }
                  }
              ]
          }
      }
  }​​

• El conjunto de resultados inicial de la consulta de búsqueda anterior se enviará de vuelta al flujo de datos en NiFi para su reejecución.​

SELECT CEA.CATENTRY_ID,
            LISTAGG(CEA.USAGE, ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) USAGE,
            LISTAGG(AD.LANGUAGE_ID, ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) LANGUAGE_ID,
            LISTAGG(A.STOREENT_ID, ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) STOREENT_ID,
            LISTAGG(A.ATTRTYPE_ID, '###') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) ATTRTYPE_ID,
            LISTAGG(CEA.ATTR_ID, ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) ATTR_ID,
            LISTAGG(TO_CHAR(COALESCE(CEA.SEQUENCE, 0)), ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) ATTR_SEQUENCE,
            LISTAGG(A.IDENTIFIER, '###') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) ATTR_IDENTIFIER, 
            LISTAGG(COALESCE(A.DISPLAYABLE, 0), ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) DISPLAYABLE, 
            LISTAGG(COALESCE(A.SEARCHABLE, 0), ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) SEARCHABLE, 
            LISTAGG(COALESCE(A.COMPARABLE, 0), ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) COMPARABLE, 
            LISTAGG(COALESCE(A.FACETABLE, 0), ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) FACETABLE,
            LISTAGG(COALESCE(A.MERCHANDISABLE, 0), ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) MERCHANDISABLE, 
            LISTAGG(COALESCE(A.SWATCHABLE, 0), ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) SWATCHABLE,
            LISTAGG(COALESCE(A.STOREDISPLAY, 0), ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) STOREDISPLAY,
            LISTAGG(COALESCE(AD.NAME, ADF.NAME), '###') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) ATTR_NAME, 
            LISTAGG(COALESCE(COALESCE(AD.GROUPNAME, ADF.GROUPNAME), ' '), '###') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) GROUPNAME, 
            LISTAGG(V.IDENTIFIER, '###') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) ATTRVAL_IDENTIFIER,
            LISTAGG(COALESCE(COALESCE(VD.STRINGVALUE, VDF.STRINGVALUE), ' ' ), '###') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) STRINGVALUE, 
            LISTAGG(COALESCE(TO_CHAR(COALESCE(VD.INTEGERVALUE, VDF.INTEGERVALUE)), ' '), '###') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) INTEGERVALUE, 
            LISTAGG(COALESCE(TO_CHAR(COALESCE(VD.FLOATVALUE, VDF.FLOATVALUE)), ' '), '###') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) FLOATVALUE, 
            LISTAGG(COALESCE(TO_CHAR(COALESCE(VD.SEQUENCE, VDF.SEQUENCE)), '0'), ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) ATTRVAL_SEQUENCE, 
            LISTAGG(COALESCE(COALESCE(VD.QTYUNIT_ID, VDF.QTYUNIT_ID), ' '), '###') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) QTYUNIT_ID,
            LISTAGG(COALESCE(NULLIF(COALESCE(VD.IMAGE1, VDF.IMAGE1), ''), ' '), '###') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) IMAGE1,
	    LISTAGG(COALESCE(NULLIF(COALESCE(VD.IMAGE2, VDF.IMAGE2), ''), ' '), '###') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) IMAGE2,
            LISTAGG(CEA.ATTRVAL_ID, ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) ATTRVAL_ID,
            LISTAGG(COALESCE(COALESCE(QD.DESCRIPTION, QDF.DESCRIPTION), ' '), '###') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) QTY_DESCRIPTION
       FROM (SELECT CER.CATENTRY_ID_CHILD CATENTRY_ID, CEATTR.SEQUENCE, CEATTR.ATTR_ID, CEATTR.ATTRVAL_ID, CEATTR.USAGE
               FROM CATENTRYATTR CEATTR,
                    (SELECT A.CATENTRY_ID_PARENT CATENTRY_ID_PARENT,A.CATENTRY_ID_CHILD CATENTRY_ID_CHILD 
                       FROM CATENTREL A,
                            (SELECT G.CATENTRY_ID
                               FROM CATGPENREL G,CATENTRY E 
                              WHERE G.CATENTRY_ID = E.CATENTRY_ID AND G.CATALOG_ID = ${param.catalogId} AND E.MARKFORDELETE = 0 
                                AND G.CATALOG_ID IN ( SELECT CATALOG_ID FROM STORECAT
                                                       WHERE STOREENT_ID IN (SELECT RELATEDSTORE_ID FROM STOREREL WHERE STATE = 1 AND STRELTYP_ID = -4 AND STORE_ID = ${param.storeId} )) 
                             ) 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 (SELECT G.CATENTRY_ID FROM CATGPENREL G,CATENTRY E 
                              WHERE G.CATENTRY_ID = E.CATENTRY_ID AND G.CATALOG_ID = ${param.catalogId} AND E.MARKFORDELETE = 0 
                                AND G.CATALOG_ID IN (SELECT CATALOG_ID FROM STORECAT
                                                      WHERE STOREENT_ID IN (SELECT RELATEDSTORE_ID FROM STOREREL WHERE STATE = 1 AND STRELTYP_ID = -4 AND STORE_ID = ${param.storeId} )) 
                             ) A 
                    ) CER
              WHERE CEATTR.CATENTRY_ID = CER.CATENTRY_ID_PARENT AND CEATTR.ATTRVAL_ID <> 0
                AND (CER.CATENTRY_ID_PARENT = CER.CATENTRY_ID_CHILD OR 
                    (SELECT COUNT(*) FROM CATENTRYATTR WHERE CATENTRYATTR.CATENTRY_ID = CER.CATENTRY_ID_CHILD AND CEATTR.ATTR_ID = CATENTRYATTR.ATTR_ID) = 0) ) CEA
              INNER JOIN ATTR A ON (CEA.ATTR_ID = A.ATTR_ID)
              INNER JOIN ATTRVAL V ON (CEA.ATTRVAL_ID = V.ATTRVAL_ID)
			   ${TI_DELTA_JOIN_QUERY_ATTR}	
               LEFT OUTER JOIN ATTRDESC AD ON (A.ATTR_ID = AD.ATTR_ID AND AD.LANGUAGE_ID = ${param.langId})
               LEFT OUTER JOIN ATTRDESC ADF ON (A.ATTR_ID = ADF.ATTR_ID AND ADF.LANGUAGE_ID = ${default.language})
               LEFT OUTER JOIN ATTRVALDESC VD ON (V.ATTRVAL_ID = VD.ATTRVAL_ID AND VD.LANGUAGE_ID = ${param.langId})
               LEFT OUTER JOIN ATTRVALDESC VDF ON (V.ATTRVAL_ID = VDF.ATTRVAL_ID AND VDF.LANGUAGE_ID = ${default.language})
               LEFT OUTER JOIN QTYUNITDSC QD ON (VD.QTYUNIT_ID = QD.QTYUNIT_ID AND QD.LANGUAGE_ID = ${param.langId})
               LEFT OUTER JOIN QTYUNITDSC QDF ON (VD.QTYUNIT_ID = QDF.QTYUNIT_ID AND QDF.LANGUAGE_ID = ${default.language}) ${extCatentryWhereSQL}
       GROUP BY CEA.CATENTRY_ID
       ORDER BY CEA.CATENTRY_ID
  OFFSET ${param.offset} ROWS FETCH NEXT ${param.pageSize} ROWS ONLY
     
	         

         SELECT CEA.CATENTRY_ID_PARENT CATENTRY_ID,
	        LISTAGG(CEA.USAGE, ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) USAGE,
	        LISTAGG(CAST(AD.LANGUAGE_ID AS VARCHAR(32000)), ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) LANGUAGE_ID,
	        LISTAGG(CAST(A.STOREENT_ID AS VARCHAR(32000)), ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) STOREENT_ID,
	        LISTAGG(CAST(A.ATTRTYPE_ID AS VARCHAR(32000)), '###') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) ATTRTYPE_ID,
	        LISTAGG(CAST(CEA.ATTR_ID AS VARCHAR(32000)), ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) ATTR_ID,
	        LISTAGG(CAST(CEA.SEQUENCE AS VARCHAR(32000)), ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) ATTR_SEQUENCE,
	        LISTAGG(CAST(A.IDENTIFIER AS VARCHAR(32000)), '###') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) ATTR_IDENTIFIER,
	        LISTAGG(COALESCE(A.DISPLAYABLE, 0), ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) DISPLAYABLE,
	        LISTAGG(COALESCE(A.SEARCHABLE, 0), ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) SEARCHABLE,
	        LISTAGG(COALESCE(A.COMPARABLE, 0), ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) COMPARABLE,
	        LISTAGG(COALESCE(A.FACETABLE, 0), ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) FACETABLE,
	        LISTAGG(COALESCE(A.MERCHANDISABLE, 0), ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) MERCHANDISABLE,
	        LISTAGG(COALESCE(A.SWATCHABLE, 0), ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) SWATCHABLE,
               LISTAGG(COALESCE(A.STOREDISPLAY, 0), ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) STOREDISPLAY,
	        LISTAGG(CAST(COALESCE(AD.NAME, ADF.NAME) AS VARCHAR(32000)), '###') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) ATTR_NAME,
	        LISTAGG(COALESCE(CAST(COALESCE(AD.GROUPNAME, ADF.GROUPNAME) AS VARCHAR(32000)), ' '), '###') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) GROUPNAME,
	        LISTAGG(CAST(V.IDENTIFIER AS VARCHAR(32000)), '###') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) ATTRVAL_IDENTIFIER,
	        LISTAGG(COALESCE(CAST(COALESCE(VD.STRINGVALUE, VDF.STRINGVALUE) AS VARCHAR(32000)), ' '), '###') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) STRINGVALUE,
	        LISTAGG(COALESCE(CAST(TO_CHAR(COALESCE(VD.INTEGERVALUE, VDF.INTEGERVALUE)) AS VARCHAR(32000)), ' '), '###') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) INTEGERVALUE,
	        LISTAGG(COALESCE(CAST(TO_CHAR(COALESCE(VD.FLOATVALUE, VDF.FLOATVALUE)) AS VARCHAR(32000)), ' '), '###') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) FLOATVALUE,
	        LISTAGG(COALESCE(CAST(COALESCE(VD.SEQUENCE, VDF.SEQUENCE) AS VARCHAR(32000)), '0'),', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) ATTRVAL_SEQUENCE,
	        LISTAGG(COALESCE(CAST(COALESCE(VD.QTYUNIT_ID, VDF.QTYUNIT_ID) AS VARCHAR(32000)), ' '), '###') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) QTYUNIT_ID,
	        LISTAGG(COALESCE(CAST(NULLIF(COALESCE(VD.IMAGE1, VDF.IMAGE1),'') AS VARCHAR(32000)), ' '), '###') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) IMAGE1,
		LISTAGG(COALESCE(CAST(NULLIF(COALESCE(VD.IMAGE2, VDF.IMAGE2),'') AS VARCHAR(32000)), ' '), '###') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) IMAGE2,
	        LISTAGG(CAST(CEA.ATTRVAL_ID AS VARCHAR(32000)), ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) ATTRVAL_ID,
	        LISTAGG(COALESCE(CAST(COALESCE(QD.DESCRIPTION, QDF.DESCRIPTION) AS VARCHAR(32000)), ' '), '###') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) QTY_DESCRIPTION
	    FROM (SELECT DISTINCT CER.CATENTRY_ID_PARENT, CEATTR.SEQUENCE, CEATTR.ATTR_ID, CEATTR.ATTRVAL_ID, CEATTR.USAGE
	           FROM CATENTRYATTR CEATTR,
	                (SELECT A.CATENTRY_ID_PARENT, A.CATENTRY_ID_CHILD
	                   FROM CATENTREL A,
	                        (SELECT G.CATENTRY_ID
	                           FROM CATGPENREL G,CATENTRY E
	                          WHERE G.CATENTRY_ID = E.CATENTRY_ID AND G.CATALOG_ID = ${param.catalogId} AND E.MARKFORDELETE = 0
	                            AND G.CATALOG_ID IN ( SELECT CATALOG_ID FROM STORECAT
	                                                   WHERE STOREENT_ID IN (SELECT RELATEDSTORE_ID FROM STOREREL WHERE STATE = 1 AND STRELTYP_ID = -4 AND STORE_ID = ${param.storeId} ))
	                         ) 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_ITEM')
	                ) CER
	          WHERE CEATTR.CATENTRY_ID = CER.CATENTRY_ID_CHILD AND CEATTR.ATTRVAL_ID <> 0 AND CEATTR.USAGE = 1) CEA
	          INNER JOIN ATTR A ON (CEA.ATTR_ID = A.ATTR_ID)
	          INNER JOIN ATTRVAL V ON (CEA.ATTRVAL_ID = V.ATTRVAL_ID)
			   ${TI_DELTA_JOIN_QUERY_ROLLUP_ATTR}
	           LEFT OUTER JOIN ATTRDESC AD ON (A.ATTR_ID = AD.ATTR_ID AND AD.LANGUAGE_ID = ${param.langId})
               LEFT OUTER JOIN ATTRDESC ADF ON (A.ATTR_ID = ADF.ATTR_ID AND ADF.LANGUAGE_ID = ${default.language})
	           LEFT OUTER JOIN ATTRVALDESC VD ON (V.ATTRVAL_ID = VD.ATTRVAL_ID AND VD.LANGUAGE_ID = ${param.langId})
               LEFT OUTER JOIN ATTRVALDESC VDF ON (V.ATTRVAL_ID = VDF.ATTRVAL_ID AND VDF.LANGUAGE_ID = ${default.language})
	           LEFT OUTER JOIN QTYUNITDSC QD ON (VD.QTYUNIT_ID = QD.QTYUNIT_ID AND QD.LANGUAGE_ID = ${param.langId})
               LEFT OUTER JOIN QTYUNITDSC QDF ON (VD.QTYUNIT_ID = QDF.QTYUNIT_ID AND QDF.LANGUAGE_ID = ${default.language}) ${extCatentryIdWhereParentOrChild}
	  GROUP BY CEA.CATENTRY_ID_PARENT
      ORDER BY CEA.CATENTRY_ID_PARENT
      LIMIT ${param.offset}, ${param.pageSize}​​

​
	  {
  "stored_fields": [
    "id.*",
    "identifier.*",
    "relationship.*",
    "attributes.*",
    "natural.descriptive.*",
    "attachments.*",
    "images.*",
    "manufacturer.*"​
​
  ],
  "size": "100",
  "_source": false,
  "query": {
    "bool": {
      "must": {
        "match_all": {}
      },
      "filter": [
        {
          "bool": {
            "should": [
              {"term": {"relationship.name": "product"}},
              {"term": {"relationship.name": "variant"}}
             ]
          }
        },
        {
          "term": {
            "id.store": "${param.storeId}"
          }
        },
        {
          "term": {
            "id.catalog": "${param.catalogId}"
          }
        },
        {
          "term": {
            "id.language": "${param.langId}"
          }
        }     
        ${extCatentryES}
	${extDataloadES}​
      ]
    }
  }
}

• Los campos de respuesta - ​attribute.* y natural.descriptive.*, se correlacionan utilizando la tabla anterior en el paso 9.
• Los campos de respuesta - attachments.* e images.* , se correlacionan utilizando la tabla anterior del paso 7.
• El campo de respuesta - manufacture.* se correlaciona utilizando la tabla anterior en el paso 1.

SELECT CEA.CATENTRY_ID, 
            LISTAGG(CEA.USAGE, ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) USAGE,
            LISTAGG(AD.LANGUAGE_ID, ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) LANGUAGE_ID,
            LISTAGG(A.STOREENT_ID, ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) STOREENT_ID,
            LISTAGG(A.ATTRTYPE_ID, '###') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) ATTRTYPE_ID,
            LISTAGG(CEA.ATTR_ID, ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) ATTR_ID,
            LISTAGG(TO_CHAR(COALESCE(CEA.SEQUENCE, 0)), ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) ATTR_SEQUENCE,
            LISTAGG(A.IDENTIFIER, '###') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) ATTR_IDENTIFIER, 
            LISTAGG(COALESCE(A.DISPLAYABLE, 0), ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) DISPLAYABLE, 
            LISTAGG(COALESCE(A.SEARCHABLE, 0), ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) SEARCHABLE, 
            LISTAGG(COALESCE(A.COMPARABLE, 0), ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) COMPARABLE, 
            LISTAGG(COALESCE(A.FACETABLE, 0), ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) FACETABLE,
            LISTAGG(COALESCE(A.MERCHANDISABLE, 0), ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) MERCHANDISABLE, 
            LISTAGG(COALESCE(A.SWATCHABLE, 0), ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) SWATCHABLE,
            LISTAGG(COALESCE(A.STOREDISPLAY, 0), ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) STOREDISPLAY,
            LISTAGG(COALESCE(AD.NAME, ADF.NAME), '###') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) ATTR_NAME, 
            LISTAGG(COALESCE(COALESCE(AD.GROUPNAME, ADF.GROUPNAME), ' '), '###') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) GROUPNAME, 
            LISTAGG(V.IDENTIFIER, '###') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) ATTRVAL_IDENTIFIER,
            LISTAGG(COALESCE(COALESCE(VD.STRINGVALUE, VDF.STRINGVALUE), ' ' ), '###') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) STRINGVALUE, 
            LISTAGG(COALESCE(TO_CHAR(COALESCE(VD.INTEGERVALUE, VDF.INTEGERVALUE)), ' '), '###') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) INTEGERVALUE, 
            LISTAGG(COALESCE(TO_CHAR(COALESCE(VD.FLOATVALUE, VDF.FLOATVALUE)), ' '), '###') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) FLOATVALUE, 
            LISTAGG(COALESCE(TO_CHAR(COALESCE(VD.SEQUENCE, VDF.SEQUENCE)), '0'), ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) ATTRVAL_SEQUENCE, 
            LISTAGG(COALESCE(COALESCE(VD.QTYUNIT_ID, VDF.QTYUNIT_ID), ' '), '###') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) QTYUNIT_ID,
            LISTAGG(COALESCE(NULLIF(COALESCE(VD.IMAGE1, VDF.IMAGE1), ''), ' '), '###') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) IMAGE1,
	    LISTAGG(COALESCE(NULLIF(COALESCE(VD.IMAGE2, VDF.IMAGE2), ''), ' '), '###') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) IMAGE2,
            LISTAGG(CEA.ATTRVAL_ID, ', ') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) ATTRVAL_ID,
            LISTAGG(COALESCE(COALESCE(QD.DESCRIPTION, QDF.DESCRIPTION), ' '), '###') WITHIN GROUP (ORDER BY CEA.ATTRVAL_ID) QTY_DESCRIPTION
         FROM (SELECT CER.CATENTRY_ID_CHILD CATENTRY_ID, CEATTR.SEQUENCE, CEATTR.ATTR_ID, CEATTR.ATTRVAL_ID, CEATTR.USAGE
               FROM CATENTRYATTR CEATTR, CATENTREL CATREL,
                    (SELECT A.CATENTRY_ID CATENTRY_ID_PARENT,A.CATENTRY_ID CATENTRY_ID_CHILD 
                       FROM (SELECT G.CATENTRY_ID FROM CATGPENREL G,CATENTRY E 
                              WHERE G.CATENTRY_ID = E.CATENTRY_ID AND G.CATALOG_ID = ${param.catalogId} AND E.MARKFORDELETE = 0 
                                AND G.CATALOG_ID IN (SELECT CATALOG_ID FROM STORECAT
                                                      WHERE STOREENT_ID IN (SELECT RELATEDSTORE_ID FROM STOREREL WHERE STATE = 1 AND STRELTYP_ID = -4 AND STORE_ID = ${param.storeId} )) 
                             ) A 
                    ) CER
              WHERE CEATTR.CATENTRY_ID = CER.CATENTRY_ID_PARENT AND CEATTR.ATTRVAL_ID <> 0 
              AND CATREL.CATENTRY_ID_CHILD = CEATTR.CATENTRY_ID AND CATREL.CATRELTYPE_ID = 'PRODUCT_ITEM' AND CEATTR.USAGE = 2
              AND (CER.CATENTRY_ID_PARENT = CER.CATENTRY_ID_CHILD OR 
                    (SELECT COUNT(*) FROM CATENTRYATTR WHERE CATENTRYATTR.CATENTRY_ID = CER.CATENTRY_ID_CHILD AND CEATTR.ATTR_ID = CATENTRYATTR.ATTR_ID) = 0)) CEA
              INNER JOIN ATTR A ON (CEA.ATTR_ID = A.ATTR_ID)
              INNER JOIN ATTRVAL V ON (CEA.ATTRVAL_ID = V.ATTRVAL_ID)
			   ${TI_DELTA_JOIN_QUERY_ATTR}	
               LEFT OUTER JOIN ATTRDESC AD ON (A.ATTR_ID = AD.ATTR_ID AND AD.LANGUAGE_ID = ${param.langId})
               LEFT OUTER JOIN ATTRDESC ADF ON (A.ATTR_ID = ADF.ATTR_ID AND ADF.LANGUAGE_ID = ${default.language})
               LEFT OUTER JOIN ATTRVALDESC VD ON (V.ATTRVAL_ID = VD.ATTRVAL_ID AND VD.LANGUAGE_ID = ${param.langId})
               LEFT OUTER JOIN ATTRVALDESC VDF ON (V.ATTRVAL_ID = VDF.ATTRVAL_ID AND VDF.LANGUAGE_ID = ${default.language})
               LEFT OUTER JOIN QTYUNITDSC QD ON (VD.QTYUNIT_ID = QD.QTYUNIT_ID AND QD.LANGUAGE_ID = ${param.langId})
               LEFT OUTER JOIN QTYUNITDSC QDF ON (VD.QTYUNIT_ID = QDF.QTYUNIT_ID AND QDF.LANGUAGE_ID = ${default.language}) ${extCatentryWhereSQL}  
       GROUP BY CEA.CATENTRY_ID
       ORDER BY CEA.CATENTRY_ID
  OFFSET ${param.offset} ROWS FETCH NEXT ${param.pageSize} ROWS ONLY      
​

SELECT CE.CATENTRY_ID,
		 LISTAGG(ATCHAST.ATCHAST_ID, ', ') WITHIN GROUP (ORDER BY ATCHAST.ATCHAST_ID) ATCHAST_ID,
	         LISTAGG(ATCHTGT.IDENTIFIER, '###') WITHIN GROUP (ORDER BY ATCHAST.ATCHAST_ID) IDENTIFIER,
                 LISTAGG(COALESCE(NULLIF(ATCHRELDSC.NAME,''), ' '),  '###') WITHIN GROUP (ORDER BY ATCHAST.ATCHAST_ID) NAME,
                 LISTAGG(COALESCE(NULLIF(ATCHRELDSC.SHORTDESCRIPTION,''), ' '), '###') WITHIN GROUP (ORDER BY ATCHAST.ATCHAST_ID) SHORTDESCRIPTION,
		 LISTAGG(COALESCE(ATCHAST.ATCHASTPATH, ' '),  '###') WITHIN GROUP (ORDER BY ATCHAST.ATCHAST_ID) ATCHASTPATH,
                 LISTAGG(COALESCE(NULLIF(ATCHAST.DIRECTORYPATH,''), ' '),  '###') WITHIN GROUP (ORDER BY ATCHAST.ATCHAST_ID) DIRECTORYPATH,
                 LISTAGG(COALESCE(NULLIF(ATCHAST.MIMETYPE,''), ' '),  '###') WITHIN GROUP (ORDER BY ATCHAST.ATCHAST_ID) MIMETYPE,
		 LISTAGG(ATCHASTLG.LANGUAGE_ID, ', ') WITHIN GROUP (ORDER BY ATCHAST.ATCHAST_ID) LANGUAGE_ID,
		 LISTAGG(COALESCE(ATCHRLUS.IMAGE, ' '), '###') WITHIN GROUP (ORDER BY ATCHAST.ATCHAST_ID) IMAGE,
		 LISTAGG(COALESCE(ATCHRLUS.IDENTIFIER, ' '), '###') WITHIN GROUP (ORDER BY ATCHAST.ATCHAST_ID) RULENAME,
		 LISTAGG(TO_CHAR(ATCHREL.SEQUENCE), ', ') WITHIN GROUP (ORDER BY ATCHAST.ATCHAST_ID) SEQUENCE
	  FROM CATENTRY CE
		   ${TI_DELTA_JOIN_QUERY_ATTCH}
	  	   JOIN ATCHREL ON (ATCHREL.BIGINTOBJECT_ID = CE.CATENTRY_ID)
		   JOIN ATCHOBJTYP ON (ATCHREL.ATCHOBJTYP_ID = ATCHOBJTYP.ATCHOBJTYP_ID AND ATCHOBJTYP.IDENTIFIER = 'CATENTRY')
		   LEFT JOIN ATCHTGT ON (ATCHREL.ATCHTGT_ID = ATCHTGT.ATCHTGT_ID )
		   JOIN ATCHAST ON (ATCHAST.ATCHTGT_ID = ATCHTGT.ATCHTGT_ID)
		   LEFT JOIN ATCHASTLG ON (ATCHASTLG.ATCHAST_ID = ATCHAST.ATCHAST_ID) 
		   LEFT JOIN ATCHRELDSC ON (ATCHRELDSC.ATCHREL_ID = ATCHREL.ATCHREL_ID AND ATCHRELDSC.LANGUAGE_ID= ${param.langId})
		   JOIN ATCHRLUS ON (ATCHREL.ATCHRLUS_ID = ATCHRLUS.ATCHRLUS_ID)
	 WHERE (ATCHASTLG.ATCHASTLG_ID IS NULL OR ATCHASTLG.LANGUAGE_ID=${param.langId})
	   AND CE.CATENTRY_ID IN (SELECT C.CATENTRY_ID FROM CATGPENREL R, CATENTRY C
	                           WHERE R.CATALOG_ID = ${param.catalogId} AND R.CATALOG_ID IN
	                                 (SELECT CATALOG_ID FROM STORECAT WHERE STOREENT_ID IN
	                                 (SELECT RELATEDSTORE_ID FROM STOREREL
	                                   WHERE STATE = 1 AND STRELTYP_ID = -4 AND STORE_ID = ${param.storeId}))
	                             AND R.CATENTRY_ID = C.CATENTRY_ID AND C.MARKFORDELETE = 0 AND C.CATENTTYPE_ID = 'ItemBean') ${extCatentryAndSQL1a}
	 GROUP BY CE.CATENTRY_ID

{
   "CATENTRY_ID":10031,
   "MEMBER_ID":7000000000000001001,
   "CATENTTYPE_ID":"ProductBean     ",
   "PARTNUMBER":"AuroraWMDRS-33",
   "MARKFORDELETE":0,
   "BUYABLE":1,
   "STATE":"1",
   "LANGUAGE_ID":-1,
   "MFPARTNUMBER":null,
   "MFNAME":"Luigi Valenti",
   "STARTDATE":null,
   "ENDDATE":null,
   "NAME":"Luigi Valenti Mid Length Cocktail Dress                                                                                         ",
   "SHORTDESCRIPTION":"Sleeveless cocktail dress with lace work and satin strip at the waist",
   "KEYWORD":null,
   "THUMBNAIL":"images/catalog/apparel/women/wcl000_dresses/200x310/wcl000_0037_a_pink.jpg",
   "FULLIMAGE":"images/catalog/apparel/women/wcl000_dresses/646x1000/wcl000_0037_a_pink.jpg",
   "PUBLISHED":1,
   "CATALOG_ID":10001,
   "CATGROUP_ID":10001,
   "SEQUENCE":31.0,
   "LOCALENAME":"en_US           ",
   "STOREENT_ID":10501
}

{ "update": { "_id": "1--1-10001-10031", "_index": ".auth.1.product.202006160325", "retry_on_conflict": 5, "_source": false } }
{
  "doc": {
    "buyable": true,
    "identifier": {
      "specification": "product",
      "language": "en_US",
      "sku": {
        "normalized": "AuroraWMDRS-33",
        "raw": "AuroraWMDRS-33"
      },
      "pn": {
        "normalized": "AuroraWMDRS-33",
        "raw": "AuroraWMDRS-33"
      }
    },
    "displayable": true,
    "description": {
      "text_en_US": "Sleeveless cocktail dress with lace work and satin strip at the waist",
      "raw": "Sleeveless cocktail dress with lace work and satin strip at the waist",
      "text": "Sleeveless cocktail dress with lace work and satin strip at the waist"
    },
    "type": "product",
    "url": {
      "image": "images/catalog/apparel/women/wcl000_dresses/646x1000/wcl000_0037_a_pink.jpg",
      "thumbnail": "images/catalog/apparel/women/wcl000_dresses/200x310/wcl000_0037_a_pink.jpg"
    },
    "__meta": {
      "created": "2020-08-04T01:16:42.012Z",
      "modified": "2020-08-04T01:16:42.034Z",
      "version": {
        "min": 0,
        "max": 0
      }
    },
    "manufacturer": {
      "normalized": "Luigi Valenti",
      "raw": "Luigi Valenti",
      "text": "Luigi Valenti"
    },
    "name": {
      "text_en_US": "Luigi Valenti Mid Length Cocktail Dress",
      "normalized": "Luigi Valenti Mid Length Cocktail Dress",
      "raw": "Luigi Valenti Mid Length Cocktail Dress",
      "text": "Luigi Valenti Mid Length Cocktail Dress"
    },
    "id": {
      "catalog": "10001",
      "member": "7000000000000001001",
      "catentry": "10031",
      "language": "-1",
      "store": [
        "10501",
        "1"
      ]
    },
    "state": true,
    "relationship": {
      "name": "product"
    }
  },
  "doc_as_upsert": true
}

{
  "CATENTRY_ID": 10352,
  "LANGUAGE_ID": -1,
  "NAME": "Luigi Valenti Strapless Sundress - Override                                                                                         ",
  "SHORTDESCRIPTION": "Strapless sundress with smoked top and gored skirt - Override",
  "KEYWORD": null,
  "FULLIMAGE": images/catalog/apparel/women/wcl000_dresses/646x1000/wcl000_0011_a_blue.jpg,
  "THUMBNAIL": images/catalog/apparel/women/wcl000_dresses/200x310/wcl000_0011_a_blue.jpg​
}

{ "update": { "_id": "1--1-10001-10352", "_index": ".auth.1.product.202006160325", "retry_on_conflict": 5, "_source": false } }

{
	"doc": {
		"name": {
			"override": {
				"text_en_US": "Luigi Valenti Strapless Sundress - Override",
				"normalized": "Luigi Valenti Strapless Sundress - Override",
				"raw": "Luigi Valenti Strapless Sundress - Override",
				"text": "Luigi Valenti Strapless Sundress - Override"
			}
		},
		"description": {
			"override": {
				"text_en_US": "Strapless sundress with smoked top and gored skirt - Override",
				"raw": "Strapless sundress with smoked top and gored skirt - Override",
				"text": "Strapless sundress with smoked top and gored skirt - Override"
			}
		},
		"url": {
			"override": {
				"image": "images/catalog/apparel/women/wcl000_dresses/646x1000/wcl000_0011_a_blue.jpg",
				"thumbnail": "images/catalog/apparel/women/wcl000_dresses/200x310/wcl000_0011_a_blue.jpg"
			}
		},
		"__meta": {
			"modified": "2020-08-04T01:28:42.405Z"
		}
	}
}    

{
  "CATENTRY_ID": 10378,
  "DISALLOW_REC_ORDER": 1,
  "SUBSCPTYPE_ID": "NONE                            "
}

{ "update": { "_id": "1--1-10001-10378", "_index": ".auth.1.product.202006160325", "retry_on_conflict": 5, "_source": false } }
{
  "doc": {
    "subscription": {
      "recurring": false,
      "type": "NONE"
    },
    "__meta": {
      "modified": "2020-08-04T01:38:24.368Z"
    }
  }
}

{
  "CATENTRY_ID_PARENT": 12361,
  "CATENTRY_ID_CHILD": "12362, 12363, 12364, 12365, 12366, 3074457345616681168, 3074457345616681169",
  "SEQUENCE": "1, 2, 3, 4, 5, 1, 2",
  "CATALOG_ID": "10001###10502###10001###10502###10001###10502###10001",
  "CATRELTYPE_ID": "PRODUCT_ITEM###PRODUCT_ITEM###PRODUCT_ITEM###PRODUCT_ITEM###PRODUCT_ITEM###PRODUCT_VARIANT###PRODUCT_VARIANT",  
  "COMPONENT_ID": null,
  "QUANTITY": null
}

{ "update": { "_id": "1--1-10001-12361", "_index": ".auth.1.product.202006160325", "retry_on_conflict": 5, "_source": false } }
{
  "doc": {
    "relationship": {
      "item": {
        "sequence": [
          1,
          2,
          3,
          4,
          5
        ],
        "id": [
          "12362",
          "12363",
          "12364",
          "12365",
          "12366"
        ]
      },
      "variant": {
        "sequence": [
          1,
          2
        ],
        "id": [
          "3074457345616681168",
          "3074457345616681169"
        ]
      }
    },
    "__meta": {
      "modified": "2020-08-04T01:45:28.668Z"
    }
  }
}

{
  "CATENTRY_ID": 13040,
  "PRODUCTSET_ID": 10001
}

{ "update": { "_id": "1--1-10001-13040", "_index": ".auth.1.product.202006160325", "retry_on_conflict": 5, "_source": false } }
{
  "doc": {
    "relationship": {
      "set": "10001"
    },
    "__meta": {
      "modified": "2020-08-04T01:54:15.517Z"
    }
  }
}

{
  "CATENTRY_ID_FROM": 10001,
  "CATENTRY_ID_TO": "10003, 10009, 10011",
  "RANK": "1, 2, 3",
  "TYPE": "ACCESSORY###ACCESSORY###ACCESSORY",
  "QUANTITY": "10, 2, 5",
}

{ "update": { "_id": "1--1-10001-10001", "_index": ".auth.1.product.202006160325", "retry_on_conflict": 5, "_source": false } }
{
  "doc": {
    "associations": [
      {
        "quantity": 10,
        "rank": 1,
        "id": "10003",
        "type": "ACCESSORY"
      },
      {
        "quantity": 2,
        "rank": 2,
        "id": "10009",
        "type": "ACCESSORY"
      },
      {
        "quantity": 5,
        "rank": 3,
        "id": "10011",
        "type": "ACCESSORY"
      }
    ],
    "__meta": {
      "modified": "2020-08-04T02:03:01.436Z"
    }
  }
}

{
  "CATENTRY_ID": 10350,
  "ATCHAST_ID": "2231, 2233, 2232, 2234",
  "IDENTIFIER": "Dress Angle 1 20201 thumbnail image###Dress Angle 1 20201 full size image###Dress Angle 2 20201 thumbnail image###Dress Angle 2 20201 full size image",
  "NAME": "Luigi Valenti Strapless Sundress Angle 1###Luigi Valenti Strapless Sundress Angle 1###Luigi Valenti Strapless Sundress Angle 2###Luigi Valenti Strapless Sundress Angle 2",
  "SHORTDESCRIPTION": "Luigi Valenti Strapless Sundress Angle 1###Luigi Valenti Strapless Sundress Angle 1###Luigi Valenti Strapless Sundress Angle 2###Luigi Valenti Strapless Sundress Angle 2",
  "ATCHASTPATH": "images/catalog/apparel/women/wcl000_dresses/200x310/wcl000_0011_a_blue.jpg###images/catalog/apparel/women/wcl000_dresses/646x1000/wcl000_0011_a_blue.jpg###images/catalog/apparel/women/wcl000_dresses/200x310/wcl000_0011_b_blue.jpg###images/catalog/apparel/women/wcl000_dresses/646x1000/wcl000_0011_b_blue.jpg",
  "DIRECTORYPATH": "images/catalog/apparel/women/###images/catalog/apparel/women/###images/catalog/apparel/women/###images/catalog/apparel/women/",
  "MIMETYPE": "image/jpg###image/jpg###image/jpg###image/jpg",
  "LANGUAGE_ID": null,
  "IMAGE": " ### ### ### ",
  "RULENAME": "ANGLEIMAGES_THUMBNAIL###ANGLEIMAGES_FULLIMAGE###ANGLEIMAGES_THUMBNAIL###ANGLEIMAGES_FULLIMAGE",
  "SEQUENCE": "1, 1, 2, 2"
}

{
  "CATENTRY_ID": 10001,
  "LANGUAGE_ID": -1,
  "NAME": "Hermitage Fit and Flare Dress-Override                                                                                          ",
  "SHORTDESCRIPTION": "Jewel-toned cocktail dress with fitted bodice and gently flared skirt-Override",
  "KEYWORD": null,
  "FULLIMAGE": null,
  "THUMBNAIL": null,
  "LOCALENAME": "en_US"
}

{ "update": { "_id": "1--1-10001-10001", "_index": ".auth.1.product.202006160325", "retry_on_conflict": 5, "_source": false } }
{
	"doc": {
		"category": {
			"10502-1-3-10001": {
				"parent": "10001",
				"sequence": "1",
				"catalog": "10502",
				"path": {
					"name": [
						"Apparel",
						"Women",
						"Dresses"
					],
					"id": [
						"1",
						"3",
						"10001"
					]
				},
				"name": "Dresses"
			}
		},
		"path": {
			"10502": [
				"/1/3/10001"
			]
		}
	},
	"doc_as_upsert": true
}​

{
  "CATENTRY_ID" : 10579,
  "USAGE" : "1, 1",
  "LANGUAGE_ID" : "-1, -1",
  "STOREENT_ID" : "10501, 10501",
  "ATTRTYPE_ID" : "STRING          ###STRING          ",
  "ATTR_ID" : "7000000000000000001, 7000000000000000002",
  "DISPLAY_SEQUENCE" : "1, 1",
  "ATTR_IDENTIFIER" : "swatchSize###swatchcolor",
  "ATTR_SEQUENCE" : "0, 0",
  "DISPLAYABLE" : "1, 1",
  "SEARCHABLE" : "0, 0",
  "COMPARABLE" : "1, 1",
  "FACETABLE" : "0, 0",
  "MERCHANDISABLE" : "0, 0",
  "SWATCHABLE" : "0, 0",
  "ATTR_NAME" : "Available Sizes###Color",
  "GROUPNAME" : " ### ",
  "ATTRVAL_IDENTIFIER" : "S###Silver",
  "STRINGVALUE" : "S###Silver",
  "INTEGERVALUE" : " ### ",
  "FLOATVALUE" : " ### ",
  "ATTRVAL_SEQUENCE" : "1, 1",
  "QTYUNIT_ID" : "C62             ###C62             ",
  "IMAGE1" : "images/catalog/apparel/women/Swatches/swatch_size/size_s_enabled.png###images/catalog/apparel/women/Swatches/swatch_silver.png",
  "IMAGE2" : " ### ",
  "ATTRVAL_ID" : "7000000000000000001, 7000000000000000015",
  "QTY_DESCRIPTION" : "one###one"
}

{ "update": { "_id": "1--1-10001-10579", "_index": ".auth.1.product.202006160325", "retry_on_conflict": 5, "_source": false } }
{
  "doc": {
    "attributes": {
      "swatchcolor": {
        "identifier": "swatchcolor",
        "usage": "Defining",
        "displayable": true,
        "merchandisable": false,
        "searchable": false,
        "sequence": 0,
        "name": {
          "normalized": "Color",
          "raw": "Color",
          "text": "Color"
        },
        "facetable": false,
        "id": "7000000000000000002",
        "comparable": true,
        "value": [
          {
            "identifier": "Silver",
            "sequence": 1,
            "unit": {
              "identifier": "C62",
              "name": {
                "raw": "one"
              }
            },
            "normalized": "Silver",
            "raw": "Silver",
            "id": "7000000000000000015",
            "image1": "images/catalog/apparel/women/Swatches/swatch_silver.png"
          }
        ],
        "key": "swatchcolor",
        "swatchable": false
      },
      "swatchsize": {
        "identifier": "swatchSize",
        "usage": "Defining",
        "displayable": true,
        "merchandisable": false,
        "searchable": false,
        "sequence": 0,
        "name": {
          "normalized": "Available Sizes",
          "raw": "Available Sizes",
          "text": "Available Sizes"
        },
        "facetable": false,
        "id": "7000000000000000001",
        "comparable": true,
        "value": [
          {
            "identifier": "S",
            "sequence": 1,
            "unit": {
              "identifier": "C62",
              "name": {
                "raw": "one"
              }
            },
            "normalized": "S",
            "raw": "S",
            "id": "7000000000000000001",
            "image1": "images/catalog/apparel/women/Swatches/swatch_size/size_s_enabled.png"
          }
        ],
        "key": "swatchsize",
        "swatchable": false
      }
    },
    "__meta": {
      "modified": "2020-08-04T02:30:16.091Z"
    }
  }
}

{
  "CATENTRY_ID": 13296,
  "USAGE": "1, 1, 1, 1, 1, 1, 1",
  "LANGUAGE_ID": "-1, -1, -1, -1, -1, -1, -1",
  "STOREENT_ID": "10501, 10501, 10501, 10501, 10501, 10501, 10501",
  "ATTRTYPE_ID": "STRING          ###STRING          ###STRING          ###STRING          ###STRING          ###STRING          ###STRING          ",
  "ATTR_ID": "7000000000000000138, 7000000000000000142, 7000000000000000142, 7000000000000000142, 7000000000000000141, 7000000000000000141, 7000000000000000141",
  "DISPLAY_SEQUENCE": "0E0, 1.0E0, 1.0E0, 1.0E0, 2.0E0, 2.0E0, 2.0E0",
  "ATTR_IDENTIFIER": "hardware_fasteners_boltsThread size###hardware_fasteners_boltsMaterial###hardware_fasteners_boltsMaterial###hardware_fasteners_boltsMaterial###hardware_fasteners_boltsLength###hardware_fasteners_boltsLength###hardware_fasteners_boltsLength",
  "ATTR_SEQUENCE": "0E0, 0E0, 0E0, 0E0, 0E0, 0E0, 0E0",
  "DISPLAYABLE": "1, 1, 1, 1, 1, 1, 1",
  "SEARCHABLE": "0, 0, 0, 0, 0, 0, 0",
  "COMPARABLE": "1, 1, 1, 1, 1, 1, 1",
  "FACETABLE": "0, 0, 0, 0, 0, 0, 0",
  "MERCHANDISABLE": "0, 0, 0, 0, 0, 0, 0",
  "SWATCHABLE": "0, 0, 0, 0, 0, 0, 0",
  "ATTR_NAME": "Thread size###Material###Material###Material###Length###Length###Length",
  "GROUPNAME": " ### ### ### ### ### ### ",
  "ATTRVAL_IDENTIFIER": "3/4\"-10###Stainless steel###Alloy steel###Brass###2\"###2 1/4\"###3\"",
  "STRINGVALUE": "3/4\"-10###Stainless steel###Alloy steel###Brass###2\"###2 1/4\"###3\"",
  "INTEGERVALUE": " ### ### ### ### ### ### ",
  "FLOATVALUE": " ### ### ### ### ### ### ",
  "ATTRVAL_SEQUENCE": "0E0, 1.0E0, 1.0E0, 1.0E0, 2.0E0, 2.0E0, 2.0E0",
  "QTYUNIT_ID": "C62             ###C62             ###C62             ###C62             ###C62             ###C62             ###C62             ",
  "IMAGE1": " ### ### ### ### ### ### ",
  "IMAGE2": " ### ### ### ### ### ### ",
  "ATTRVAL_ID": "7000000000000002590, 7000000000000002612, 7000000000000002613, 7000000000000002614, 7000000000000002607, 7000000000000002608, 7000000000000002609",
  "QTY_DESCRIPTION": "one###one###one###one###one###one###one"
}   
      

{ "update": { "_id": "1--1-10001-13296", "_index": ".auth.1.product.202006160325", "retry_on_conflict": 5, "_source": false } }
{
  "doc": {
    "attributes": {
      "hardware_fasteners_boltslength": {
        "identifier": "hardware_fasteners_boltsLength",
        "usage": "Defining",
        "displayable": true,
        "merchandisable": false,
        "searchable": false,
        "sequence": 0,
        "name": {
          "normalized": "Length",
          "raw": "Length",
          "text": "Length"
        },
        "facetable": false,
        "id": "7000000000000000141",
        "comparable": true,
        "value": [
          {
            "identifier": "2\"",
            "sequence": 2,
            "unit": {
              "identifier": "C62",
              "name": {
                "raw": "one"
              }
            },
            "normalized": "2\"",
            "raw": "2\"",
            "id": "7000000000000002607"
          },
          {
            "identifier": "2 1/4\"",
            "sequence": 2,
            "unit": {
              "identifier": "C62",
              "name": {
                "raw": "one"
              }
            },
            "normalized": "2 1/4\"",
            "raw": "2 1/4\"",
            "id": "7000000000000002608"
          },
          {
            "identifier": "3\"",
            "sequence": 2,
            "unit": {
              "identifier": "C62",
              "name": {
                "raw": "one"
              }
            },
            "normalized": "3\"",
            "raw": "3\"",
            "id": "7000000000000002609"
          }
        ],
        "key": "hardware_fasteners_boltslength",
        "swatchable": false
      },
      "hardware_fasteners_boltsthreadsize": {
        "identifier": "hardware_fasteners_boltsThread size",
        "usage": "Defining",
        "displayable": true,
        "merchandisable": false,
        "searchable": false,
        "sequence": 0,
        "name": {
          "normalized": "Thread size",
          "raw": "Thread size",
          "text": "Thread size"
        },
        "facetable": false,
        "id": "7000000000000000138",
        "comparable": true,
        "value": [
          {
            "identifier": "3/4\"-10",
            "sequence": 0,
            "unit": {
              "identifier": "C62",
              "name": {
                "raw": "one"
              }
            },
            "normalized": "3/4\"-10",
            "raw": "3/4\"-10",
            "id": "7000000000000002590"
          }
        ],
        "key": "hardware_fasteners_boltsthreadsize",
        "swatchable": false
      },
      "hardware_fasteners_boltsmaterial": {
        "identifier": "hardware_fasteners_boltsMaterial",
        "usage": "Defining",
        "displayable": true,
        "merchandisable": false,
        "searchable": false,
        "sequence": 0,
        "name": {
          "normalized": "Material",
          "raw": "Material",
          "text": "Material"
        },
        "facetable": false,
        "id": "7000000000000000142",
        "comparable": true,
        "value": [
          {
            "identifier": "Stainless steel",
            "sequence": 1,
            "unit": {
              "identifier": "C62",
              "name": {
                "raw": "one"
              }
            },
            "normalized": "Stainless steel",
            "raw": "Stainless steel",
            "id": "7000000000000002612"
          },
          {
            "identifier": "Alloy steel",
            "sequence": 1,
            "unit": {
              "identifier": "C62",
              "name": {
                "raw": "one"
              }
            },
            "normalized": "Alloy steel",
            "raw": "Alloy steel",
            "id": "7000000000000002613"
          },
          {
            "identifier": "Brass",
            "sequence": 1,
            "unit": {
              "identifier": "C62",
              "name": {
                "raw": "one"
              }
            },
            "normalized": "Brass",
            "raw": "Brass",
            "id": "7000000000000002614"
          }
        ],
        "key": "hardware_fasteners_boltsmaterial",
        "swatchable": false
      }
    },
    "__meta": {
      "modified": "2020-08-04T02:55:52.644Z"
    }
  }
}

{
  "_index": ".auth.1.product.202007021505",
  "_type": "_doc",
  "_id": "1--1-10001-11062",
  "_score": 1,
  "fields": {
    "id.catentry": [
      "11062"
    ],
    "attributes.shape.merchandisable": [
      false
    ],
    "attributes.material.displayable": [
      true
    ],
    "attributes.style.sequence": [
      0
    ],
    "attributes.womenskirtssize.value.unit.identifier": [
      "C62",
      "C62",
      "C62",
      "C62",
      "C62",
      "C62",
      "C62"
    ],
    "attributes.length.value.raw": [
      "Mini cut length"
    ],
    "attributes.occasion.name.normalized": [
      "occasion"
    ],
    "attributes.style.value.identifier": [
      "WCL003_0303"
    ],
    "attributes.material.value.sequence": [
      3
    ],
    "attributes.length.value.id": [
      "7000000000000000369"
    ],
    "attributes.style.value.sequence": [
      1
    ],
    "attributes.cleaning.value.raw": [
      "Machine wash"
    ],
    "id.store": [
      "10501",
      "1"
    ],
    "attributes.womenskirtscolor.value.identifier": [
      "Green"
    ],
    "attributes.cleaning.sequence": [
      11
    ],
    "attributes.length.usage": [
      "Descriptive"
    ],
    "attributes.womenskirtssize.usage": [
      "Defining"
    ],
    "attributes.womenskirtscolor.usage": [
      "Defining"
    ],
    "attributes.womenskirtscolor.name.normalized": [
      "color"
    ],
    "attributes.womenskirtssize.value.identifier": [
      "XS",
      "S",
      "M",
      "L",
      "XL",
      "XXL",
      "XXXL"
    ],
    "attributes.style.identifier": [
      "Style"
    ],
    "attributes.occasion.value.identifier": [
      "WCL003_0303"
    ],
    "attributes.womenskirtscolor.value.unit.identifier": [
      "C62"
    ],
    "attributes.style.merchandisable": [
      false
    ],
    "attributes.length.name.normalized": [
      "length"
    ],
    "attributes.cleaning.value.text": [
      "Machine wash"
    ],
    "attributes.length.value.sequence": [
      2
    ],
    "id.catalog": [
      "10001"
    ],
    "attributes.womenskirtssize.value.id": [
      "7000000000000000316",
      "7000000000000000317",
      "7000000000000000318",
      "7000000000000000319",
      "7000000000000000320",
      "7000000000000000321",
      "7000000000000000322"
    ],
    "attributes.length.key": [
      "length"
    ],
    "attributes.length.name.text": [
      "Length"
    ],
    "attributes.occasion.value.id": [
      "7000000000000000384"
    ],
    "attributes.material.searchable": [
      false
    ],
    "attributes.length.displayable": [
      true
    ],
    "attributes.womenskirtssize.name.text": [
      "Size"
    ],
    "attributes.shape.facetable": [
      false
    ],
    "attributes.material.usage": [
      "Descriptive"
    ],
    "attributes.shape.comparable": [
      true
    ],
    "attributes.cleaning.searchable": [
      true
    ],
    "attributes.womenskirtscolor.sequence": [
      0
    ],
    "attributes.womenskirtssize.searchable": [
      false
    ],
    "attributes.length.facetable": [
      false
    ],
    "attributes.style.value.normalized": [
      "pull on"
    ],
    "attributes.womenskirtscolor.value.normalized": [
      "green"
    ],
    "attributes.occasion.comparable": [
      true
    ],
    "id.language": [
      "-1"
    ],
    "attributes.style.comparable": [
      true
    ],
    "attributes.shape.displayable": [
      true
    ],
    "attributes.style.id": [
      "7000000000000000010"
    ],
    "attributes.shape.value.sequence": [
      0
    ],
    "attributes.length.name.raw": [
      "Length"
    ],
    "attributes.shape.identifier": [
      "Shape"
    ],
    "attributes.style.name.raw": [
      "Style"
    ],
    "attributes.cleaning.facetable": [
      true
    ],
    "attributes.womenskirtscolor.name.text": [
      "Color"
    ],
    "attributes.occasion.name.text": [
      "Occasion"
    ],
    "attributes.material.swatchable": [
      false
    ],
    "attributes.womenskirtssize.swatchable": [
      false
    ],
    "attributes.style.facetable": [
      false
    ],
    "attributes.length.value.normalized": [
      "mini cut length"
    ],
    "attributes.womenskirtssize.name.raw": [
      "Size"
    ],
    "attributes.occasion.name.raw": [
      "Occasion"
    ],
    "attributes.womenskirtscolor.value.raw": [
      "Green"
    ],
    "attributes.womenskirtssize.value.unit.name.raw": [
      "one",
      "one",
      "one",
      "one",
      "one",
      "one",
      "one"
    ],
    "attributes.length.value.identifier": [
      "WCL003_0303"
    ],
    "attributes.womenskirtssize.merchandisable": [
      false
    ],
    "attributes.cleaning.swatchable": [
      false
    ],
    "attributes.womenskirtscolor.comparable": [
      true
    ],
    "attributes.womenskirtssize.value.raw": [
      "XS",
      "S",
      "M",
      "L",
      "XL",
      "XXL",
      "XXXL"
    ],
    "attributes.occasion.value.sequence": [
      4
    ],
    "attributes.womenskirtscolor.displayable": [
      true
    ],
    "attributes.cleaning.usage": [
      "Descriptive"
    ],
    "attributes.material.sequence": [
      0
    ],
    "attributes.shape.sequence": [
      0
    ],
    "attributes.material.facetable": [
      false
    ],
    "attributes.material.key": [
      "material"
    ],
    "attributes.length.sequence": [
      0
    ],
    "attributes.womenskirtscolor.searchable": [
      false
    ],
    "attributes.cleaning.identifier": [
      "Cleaning"
    ],
    "attributes.womenskirtssize.value.normalized": [
      "xs",
      "s",
      "m",
      "l",
      "xl",
      "xxl",
      "xxxl"
    ],
    "attributes.womenskirtssize.displayable": [
      true
    ],
    "attributes.womenskirtscolor.swatchable": [
      false
    ],
    "attributes.cleaning.name.raw": [
      "Cleaning"
    ],
    "attributes.womenskirtscolor.merchandisable": [
      false
    ],
    "attributes.shape.searchable": [
      false
    ],
    "attributes.cleaning.comparable": [
      true
    ],
    "attributes.shape.usage": [
      "Descriptive"
    ],
    "attributes.cleaning.key": [
      "cleaning"
    ],
    "attributes.cleaning.value.id": [
      "7000000000000000324"
    ],
    "attributes.womenskirtssize.facetable": [
      false
    ],
    "attributes.womenskirtssize.identifier": [
      "WomenSkirtsSize"
    ],
    "attributes.cleaning.id": [
      "7000000000000000021"
    ],
    "attributes.cleaning.displayable": [
      true
    ],
    "attributes.style.value.id": [
      "7000000000000000356"
    ],
    "attributes.style.displayable": [
      true
    ],
    "attributes.length.comparable": [
      true
    ],
    "attributes.shape.name.normalized": [
      "shape"
    ],
    "attributes.length.id": [
      "7000000000000000025"
    ],
    "attributes.material.id": [
      "7000000000000000009"
    ],
    "attributes.womenskirtssize.name.normalized": [
      "size"
    ],
    "attributes.occasion.value.raw": [
      "Casual"
    ],
    "attributes.length.identifier": [
      "Length"
    ],
    "attributes.womenskirtscolor.id": [
      "7000000000000000023"
    ],
    "attributes.occasion.identifier": [
      "Occasion"
    ],
    "attributes.occasion.sequence": [
      0
    ],
    "relationship.item.id": [
      "11063",
      "11064"
    ],
    "relationship.variant.id": [
      "3074457345616681168",
      "3074457345616681169"
    ],
    "relationship.variant.sequence": [
      "100.00",
      "200.00"
    ],    
    "attributes.shape.name.raw": [
      "Shape"
    ],
    "attributes.style.key": [
      "style"
    ],
    "attributes.shape.key": [
      "shape"
    ],
    "attributes.style.searchable": [
      false
    ],
    "attributes.womenskirtscolor.value.sequence": [
      0
    ],
    "attributes.womenskirtssize.id": [
      "7000000000000000024"
    ],
    "attributes.length.searchable": [
      false
    ],
    "attributes.cleaning.value.sequence": [
      6
    ],
    "attributes.womenskirtscolor.key": [
      "womenskirtscolor"
    ],
    "attributes.material.identifier": [
      "Material"
    ],
    "relationship.item.sequence": [
      1,
      2
    ],
    "attributes.material.name.raw": [
      "Material"
    ],
    "attributes.womenskirtssize.comparable": [
      true
    ],
    "attributes.occasion.value.normalized": [
      "casual"
    ],
    "attributes.womenskirtssize.sequence": [
      0
    ],
    "attributes.shape.value.id": [
      "7000000000000000402"
    ],
    "relationship.name": [
      "product"
    ],
    "attributes.cleaning.name.normalized": [
      "cleaning"
    ],
    "attributes.womenskirtscolor.identifier": [
      "WomenSkirtsColor"
    ],
    "attributes.material.comparable": [
      true
    ],
    "attributes.occasion.displayable": [
      true
    ],
    "attributes.style.value.raw": [
      "Pull on"
    ],
    "attributes.occasion.id": [
      "7000000000000000011"
    ],
    "attributes.womenskirtscolor.value.id": [
      "7000000000000000311"
    ],
    "attributes.shape.value.identifier": [
      "WCL003_0303"
    ],
    "attributes.material.merchandisable": [
      false
    ],
    "attributes.style.usage": [
      "Descriptive"
    ],
    "attributes.shape.id": [
      "7000000000000000026"
    ],
    "attributes.occasion.searchable": [
      false
    ],
    "attributes.length.merchandisable": [
      false
    ],
    "attributes.occasion.merchandisable": [
      false
    ],
    "attributes.womenskirtscolor.facetable": [
      false
    ],
    "attributes.cleaning.merchandisable": [
      false
    ],
    "attributes.material.value.raw": [
      "Cotton"
    ],
    "attributes.womenskirtssize.value.sequence": [
      1,
      1,
      1,
      1,
      1,
      1,
      1
    ],
    "attributes.womenskirtscolor.value.unit.name.raw": [
      "one"
    ],
    "attributes.womenskirtscolor.name.raw": [
      "Color"
    ],
    "id.member": [
      "7000000000000001001"
    ],
    "attributes.occasion.key": [
      "occasion"
    ],
    "attributes.material.value.id": [
      "7000000000000000341"
    ],
    "attributes.style.name.normalized": [
      "style"
    ],
    "attributes.shape.swatchable": [
      false
    ],
    "attributes.shape.value.raw": [
      "Flocked"
    ],
    "attributes.womenskirtssize.key": [
      "womenskirtssize"
    ],
    "attributes.material.value.normalized": [
      "cotton"
    ],
    "attributes.material.name.text": [
      "Material"
    ],
    "attributes.occasion.usage": [
      "Descriptive"
    ],
    "attributes.cleaning.value.normalized": [
      "machine wash"
    ],
    "attributes.style.swatchable": [
      false
    ],
    "attributes.material.value.identifier": [
      "WCL003_0303"
    ],
    "attributes.cleaning.name.text": [
      "Cleaning"
    ],
    "attributes.shape.name.text": [
      "Shape"
    ],
    "attributes.shape.value.normalized": [
      "flocked"
    ],
    "attributes.occasion.facetable": [
      false
    ],
    "attributes.occasion.swatchable": [
      false
    ],
    "attributes.material.name.normalized": [
      "material"
    ],
    "attributes.style.name.text": [
      "Style"
    ],
    "attributes.cleaning.value.identifier": [
      "WCL003_0303"
    ],
    "attributes.length.swatchable": [
      false
    ]
  }
}

{ "update": { "_id": "1--1-10001-11063", "_index": ".auth.1.product.202007021505", "retry_on_conflict": 5, "_source": false } }
{ "doc": {"attributes":{"occasion":{"identifier":"Occasion","usage":"Descriptive","displayable":true,"merchandisable":false,"searchable":false,"sequence":0,"name":{"normalized":"occasion","raw":"Occasion","text":"Occasion"},"facetable":false,"id":"7000000000000000011","value":{"identifier":"WCL003_0303","sequence":4,"normalized":"casual","raw":"Casual","id":"7000000000000000384"},"comparable":true,"key":"occasion","swatchable":false},"cleaning":{"identifier":"Cleaning","usage":"Descriptive","displayable":true,"merchandisable":false,"searchable":true,"sequence":11,"name":{"normalized":"cleaning","raw":"Cleaning","text":"Cleaning"},"facetable":true,"id":"7000000000000000021","value":{"sequence":6,"identifier":"WCL003_0303","normalized":"machine wash","raw":"Machine wash","text":"Machine wash","id":"7000000000000000324"},"comparable":true,"key":"cleaning","swatchable":false},"shape":{"identifier":"Shape","usage":"Descriptive","displayable":true,"merchandisable":false,"searchable":false,"sequence":0,"name":{"normalized":"shape","raw":"Shape","text":"Shape"},"facetable":false,"id":"7000000000000000026","comparable":true,"value":{"sequence":0,"identifier":"WCL003_0303","normalized":"flocked","raw":"Flocked","id":"7000000000000000402"},"key":"shape","swatchable":false},"material":{"identifier":"Material","usage":"Descriptive","displayable":true,"merchandisable":false,"searchable":false,"sequence":0,"name":{"normalized":"material","raw":"Material","text":"Material"},"facetable":false,"id":"7000000000000000009","value":{"sequence":3,"identifier":"WCL003_0303","normalized":"cotton","raw":"Cotton","id":"7000000000000000341"},"comparable":true,"key":"material","swatchable":false},"length":{"identifier":"Length","usage":"Descriptive","displayable":true,"merchandisable":false,"searchable":false,"sequence":0,"name":{"normalized":"length","raw":"Length","text":"Length"},"facetable":false,"id":"7000000000000000025","value":{"sequence":2,"identifier":"WCL003_0303","normalized":"mini cut length","raw":"Mini cut length","id":"7000000000000000369"},"comparable":true,"key":"length","swatchable":false},"style":{"identifier":"Style","usage":"Descriptive","displayable":true,"merchandisable":false,"searchable":false,"sequence":0,"name":{"normalized":"style","raw":"Style","text":"Style"},"facetable":false,"id":"7000000000000000010","value":{"identifier":"WCL003_0303","sequence":1,"normalized":"pull on","raw":"Pull on","id":"7000000000000000356"},"comparable":true,"key":"style","swatchable":false}},"relationship":{"product":{"sequence":[1.0],"id":["11062"]}},"__meta":{"modified":"2020-08-04T03:08:32.521Z"}} }
{ "update": { "_id": "1--1-10001-11064", "_index": ".auth.1.product.202007021505", "retry_on_conflict": 5, "_source": false } }
{ "doc": {"attributes":{"occasion":{"identifier":"Occasion","usage":"Descriptive","displayable":true,"merchandisable":false,"searchable":false,"sequence":0,"name":{"normalized":"occasion","raw":"Occasion","text":"Occasion"},"facetable":false,"id":"7000000000000000011","value":{"identifier":"WCL003_0303","sequence":4,"normalized":"casual","raw":"Casual","id":"7000000000000000384"},"comparable":true,"key":"occasion","swatchable":false},"cleaning":{"identifier":"Cleaning","usage":"Descriptive","displayable":true,"merchandisable":false,"searchable":true,"sequence":11,"name":{"normalized":"cleaning","raw":"Cleaning","text":"Cleaning"},"facetable":true,"id":"7000000000000000021","value":{"sequence":6,"identifier":"WCL003_0303","normalized":"machine wash","raw":"Machine wash","text":"Machine wash","id":"7000000000000000324"},"comparable":true,"key":"cleaning","swatchable":false},"shape":{"identifier":"Shape","usage":"Descriptive","displayable":true,"merchandisable":false,"searchable":false,"sequence":0,"name":{"normalized":"shape","raw":"Shape","text":"Shape"},"facetable":false,"id":"7000000000000000026","comparable":true,"value":{"sequence":0,"identifier":"WCL003_0303","normalized":"flocked","raw":"Flocked","id":"7000000000000000402"},"key":"shape","swatchable":false},"material":{"identifier":"Material","usage":"Descriptive","displayable":true,"merchandisable":false,"searchable":false,"sequence":0,"name":{"normalized":"material","raw":"Material","text":"Material"},"facetable":false,"id":"7000000000000000009","value":{"sequence":3,"identifier":"WCL003_0303","normalized":"cotton","raw":"Cotton","id":"7000000000000000341"},"comparable":true,"key":"material","swatchable":false},"length":{"identifier":"Length","usage":"Descriptive","displayable":true,"merchandisable":false,"searchable":false,"sequence":0,"name":{"normalized":"length","raw":"Length","text":"Length"},"facetable":false,"id":"7000000000000000025","value":{"sequence":2,"identifier":"WCL003_0303","normalized":"mini cut length","raw":"Mini cut length","id":"7000000000000000369"},"comparable":true,"key":"length","swatchable":false},"style":{"identifier":"Style","usage":"Descriptive","displayable":true,"merchandisable":false,"searchable":false,"sequence":0,"name":{"normalized":"style","raw":"Style","text":"Style"},"facetable":false,"id":"7000000000000000010","value":{"identifier":"WCL003_0303","sequence":1,"normalized":"pull on","raw":"Pull on","id":"7000000000000000356"},"comparable":true,"key":"style","swatchable":false}},"relationship":{"product":{"sequence":[2.0],"id":["11062"]}},"__meta":{"modified":"2020-08-04T03:08:33.071Z"}} }
{ "update": { "_id": "1--1-10001-3074457345616681168", "_index": ".auth.1.product.202007021505", "retry_on_conflict": 5, "_source": false } }
{ "doc": {"attributes":{"occasion":{"identifier":"Occasion","usage":"Descriptive","displayable":true,"merchandisable":false,"searchable":false,"sequence":0,"name":{"normalized":"occasion","raw":"Occasion","text":"Occasion"},"facetable":false,"id":"7000000000000000011","value":{"identifier":"WCL003_0303","sequence":4,"normalized":"casual","raw":"Casual","id":"7000000000000000384"},"comparable":true,"key":"occasion","swatchable":false},"cleaning":{"identifier":"Cleaning","usage":"Descriptive","displayable":true,"merchandisable":false,"searchable":true,"sequence":11,"name":{"normalized":"cleaning","raw":"Cleaning","text":"Cleaning"},"facetable":true,"id":"7000000000000000021","value":{"sequence":6,"identifier":"WCL003_0303","normalized":"machine wash","raw":"Machine wash","text":"Machine wash","id":"7000000000000000324"},"comparable":true,"key":"cleaning","swatchable":false},"shape":{"identifier":"Shape","usage":"Descriptive","displayable":true,"merchandisable":false,"searchable":false,"sequence":0,"name":{"normalized":"shape","raw":"Shape","text":"Shape"},"facetable":false,"id":"7000000000000000026","comparable":true,"value":{"sequence":0,"identifier":"WCL003_0303","normalized":"flocked","raw":"Flocked","id":"7000000000000000402"},"key":"shape","swatchable":false},"material":{"identifier":"Material","usage":"Descriptive","displayable":true,"merchandisable":false,"searchable":false,"sequence":0,"name":{"normalized":"material","raw":"Material","text":"Material"},"facetable":false,"id":"7000000000000000009","value":{"sequence":3,"identifier":"WCL003_0303","normalized":"cotton","raw":"Cotton","id":"7000000000000000341"},"comparable":true,"key":"material","swatchable":false},"length":{"identifier":"Length","usage":"Descriptive","displayable":true,"merchandisable":false,"searchable":false,"sequence":0,"name":{"normalized":"length","raw":"Length","text":"Length"},"facetable":false,"id":"7000000000000000025","value":{"sequence":2,"identifier":"WCL003_0303","normalized":"mini cut length","raw":"Mini cut length","id":"7000000000000000369"},"comparable":true,"key":"length","swatchable":false},"style":{"identifier":"Style","usage":"Descriptive","displayable":true,"merchandisable":false,"searchable":false,"sequence":0,"name":{"normalized":"style","raw":"Style","text":"Style"},"facetable":false,"id":"7000000000000000010","value":{"identifier":"WCL003_0303","sequence":1,"normalized":"pull on","raw":"Pull on","id":"7000000000000000356"},"comparable":true,"key":"style","swatchable":false}},"relationship":{"product":{"sequence":[100.0],"id":["11062"]}},"__meta":{"modified":"2020-08-04T03:08:33.073Z"}} }

{
  "CATENTRY_ID" : 10350,
  "USAGE" : "2",
  "LANGUAGE_ID" : "-1",
  "STOREENT_ID" : "10501",
  "ATTRTYPE_ID" : "STRING          ",
  "ATTR_ID" : "7000000000000000005",
  "DISPLAY_SEQUENCE" : "1",
  "ATTR_IDENTIFIER" : "material",
  "ATTR_SEQUENCE" : "0",
  "DISPLAYABLE" : "1",
  "SEARCHABLE" : "0",
  "STOREDISPLAY": "0",
  "COMPARABLE" : "1",
  "FACETABLE" : "0",
  "MERCHANDISABLE" : "0",
  "SWATCHABLE" : "0",
  "ATTR_NAME" : "Material",
  "GROUPNAME" : " ",
  "ATTRVAL_IDENTIFIER" : "Cotton blend",
  "STRINGVALUE" : "Cotton blend",
  "INTEGERVALUE" : " ",
  "FLOATVALUE" : " ",
  "ATTRVAL_SEQUENCE" : "23",
  "QTYUNIT_ID" : "C62             ",
  "IMAGE1" : " ",
  "IMAGE2" : " ",
  "ATTRVAL_ID" : "7000000000000000032",
  "QTY_DESCRIPTION" : "one"
}

{ "update": { "_id": "1--1-10001-10350", "_index": ".auth.1.product.202006160325", "retry_on_conflict": 5, "_source": false } }

{
	"doc": {
		"7000000000000000005": {
			"identifier": "material",
			"usage": "Descriptive",
			"displayable": true,
			"ribbon": false,
			"merchandisable": false,
			"searchable": false,
			"sequence": 1.0,
			"name": {
				"normalized": "material",
				"raw": "Material",
				"text": "Material"
			},
			"facetable": false,
			"id": "7000000000000000005",
			"value": {
				"sequence": 23.0,
				"identifier": "Cotton blend",
				"unit": {
					"identifier": "C62",
					"name": {
						"raw": "one"
					}
				},
				"normalized": "cotton blend",
				"raw": "Cotton blend",
				"id": "7000000000000000032"
			},
			"comparable": true,
			"key": "7000000000000000005",
			"swatchable": false
		}
	},
	"__meta": {
		"modified": "2020-08-04T02:55:52.644Z"
	}
}​