SELECT A.CATENTRY_ID_PARENT,
         LISTAGG(A.FFMCENTER_ID, ', ') WITHIN GROUP (ORDER BY A.FFMCENTER_ID) FFMCENTER_ID,
		LISTAGG(TO_CHAR(A.STATUS), ', ') WITHIN GROUP (ORDER BY A.FFMCENTER_ID) STATUS
    FROM (SELECT M.CATENTRY_ID_PARENT, M.FFMCENTER_ID, M.STATUS
		    FROM ((SELECT L.CATENTRY_ID_PARENT, L.CATENTRY_ID_CHILD, I.STLOC_ID AS FFMCENTER_ID,  I.INVENTORYSTATUS AS STATUS
					FROM CATENTREL L, CATGPENREL R, CATENTRY C, INVAVL I
				    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.CATENTRY_ID = L.CATENTRY_ID_PARENT
					  AND C.CATENTTYPE_ID NOT IN ('ItemBean')
					  AND L.CATRELTYPE_ID NOT IN ('BUNDLE_COMPONENT', 'PACKAGE_COMPONENT')
					  AND L.CATENTRY_ID_CHILD = I.CATENTRY_ID AND I.STLOC_ID IN (SELECT STLOC_ID FROM STLOC WHERE storeent_id=${param.storeId})
					  AND I.INVENTORYSTATUS IN ('AVL')
					  UNION
					  SELECT L.CATENTRY_ID_PARENT, L.CATENTRY_ID_CHILD, I.STORE_ID AS FFMCENTER_ID,  I.INVENTORYSTATUS AS STATUS
					FROM CATENTREL L, CATGPENREL R, CATENTRY C, INVAVL I
				    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.CATENTRY_ID = L.CATENTRY_ID_PARENT
					  AND C.CATENTTYPE_ID NOT IN ('ItemBean')
					  AND L.CATRELTYPE_ID NOT IN ('BUNDLE_COMPONENT', 'PACKAGE_COMPONENT')
					  AND L.CATENTRY_ID_CHILD = I.CATENTRY_ID AND I.STLOC_ID is NULL AND I.store_id = ${param.storeId}
					  AND I.INVENTORYSTATUS IN ('AVL')
					  )
				    UNION
				   SELECT CATENTRY_ID_PARENT, KITCOMP.CATENTRY_ID_CHILD, KITCOMP.FFMCENTER_ID, KITCOMP.STATUS
				     FROM (SELECT L.CATENTRY_ID_PARENT, L.CATENTRY_ID_CHILD, I.STLOC_ID AS FFMCENTER_ID, I.INVENTORYSTATUS AS STATUS
				            FROM CATENTREL L, CATGPENREL R, CATENTRY C, INVAVL I
				           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.CATENTRY_ID = L.CATENTRY_ID_PARENT
						     AND CATRELTYPE_ID in ('BUNDLE_COMPONENT','PACKAGE_COMPONENT')
						     AND L.CATENTRY_ID_CHILD = I.CATENTRY_ID AND I.STLOC_ID IN (SELECT STLOC_ID FROM STLOC WHERE storeent_id=${param.storeId})
							 AND I.INVENTORYSTATUS IN ('AVL')
							 UNION
							 SELECT L.CATENTRY_ID_PARENT, L.CATENTRY_ID_CHILD, I.STORE_ID AS FFMCENTER_ID, I.INVENTORYSTATUS AS STATUS
				            FROM CATENTREL L, CATGPENREL R, CATENTRY C, INVAVL I
				           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.CATENTRY_ID = L.CATENTRY_ID_PARENT
						     AND CATRELTYPE_ID in ('BUNDLE_COMPONENT','PACKAGE_COMPONENT')
						     AND L.CATENTRY_ID_CHILD = I.CATENTRY_ID AND I.STLOC_ID is NULL AND I.store_id = ${param.storeId}
							 AND I.INVENTORYSTATUS IN ('AVL')) KITCOMP) M
		   GROUP BY M.CATENTRY_ID_PARENT, M.FFMCENTER_ID, M.STATUS) A
   GROUP BY A.CATENTRY_ID_PARENT
   ORDER BY A.CATENTRY_ID_PARENT