-- Restituisce le righe della tabella a pagine
--- B35864 - ...alcune modifiche illogiche
FUNCTION findAll
         ( p_codMarket            IN COMMERCIALMODEL_CONFIGURATOR.CODMARKET%TYPE DEFAULT NULL,
           p_codBrand             IN COMMERCIALMODEL_CONFIGURATOR.CODBRAND%TYPE DEFAULT NULL,
           p_codCommercialModel   IN COMMERCIALMODEL_CONFIGURATOR.CODCOMMERCIALMODEL%TYPE DEFAULT NULL,
           p_codVehicleType       IN COMMERCIALMODEL_CONFIGURATOR.CODVEHICLETYPE%TYPE DEFAULT NULL,
           p_codCommercialVersion IN COMMERCIALMODEL_CONFIGURATOR.CODCOMMERCIALVERSION%TYPE DEFAULT NULL,
           p_startRow             IN NUMBER DEFAULT 1,
           p_numRows              IN NUMBER DEFAULT 10 )
  RETURN ELINKTYPES.REF_COLLECTION IS
   myCursor ELINKTYPES.REF_COLLECTION;
   v_sqlStmt VARCHAR2(3000);
BEGIN
--- B33954 - Saranno emessi solo i records con il flag ISVISBLE valorizzato
--- Quelli dove ISVISIBLE e' nullo sono considerati storici ( Boh!!! )
   v_sqlStmt := '
SELECT  /* pkCommercialModel_Configurator.FindAll */
        SEL002.NROW#,
        SEL002.CODMARKET,
        SEL002.CODBRAND,
        SEL002.CODCOMMERCIALMODEL,
        SEL002.CODENAMEB2C,
        SEL002.ISVISIBLE,
        SEL002.ZIPOFFLINE,
        SEL002.SCOPE,
        SEL002.NOTE,
        SEL002.LASTUPDATE,
        SEL002.USERGID,
        SEL002.STOCKONLY,
        SEL002.STOCKONLY_PRODCAT,
        SEL002.CODCOMMERCIALVERSION,
        SEL002.CODVEHICLETYPE,
        SEL002.BROCHURE,
        SEL002.TOTALROWS
--- 2^ Select - Numerazione delle righe
   FROM ( SELECT ROWNUM AS NROW#,
                 SEL001.CODMARKET,
                 SEL001.CODBRAND,
                 SEL001.CODCOMMERCIALMODEL,
                 SEL001.CODENAMEB2C,
                 SEL001.ISVISIBLE,
                 SEL001.ZIPOFFLINE,
                 SEL001.SCOPE,
                 SEL001.NOTE,
                 SEL001.LASTUPDATE,
                 SEL001.USERGID,
                 SEL001.STOCKONLY,
                 SEL001.STOCKONLY_PRODCAT,
                 SEL001.CODCOMMERCIALVERSION,
                 SEL001.CODVEHICLETYPE,
                 SEL001.BROCHURE,
                 COUNT(*) OVER() AS TOTALROWS
--- 1^ Select - Order By
FROM ( SELECT CMC.CODMARKET,
              CMC.CODBRAND,
              CMC.CODCOMMERCIALMODEL,
              CMC.CODENAMEB2C,
              CMC.ISVISIBLE,
              CMC.ZIPOFFLINE,
              CMC.SCOPE,
              CMC.NOTE,
              CMC.LASTUPDATE,
              CMC.USERGID,
              CMC.STOCKONLY,
              CMC.STOCKONLY_PRODCAT,
              CMC.CODCOMMERCIALVERSION,
              CMC.CODVEHICLETYPE,
              CMC.BROCHURE
         FROM COMMERCIALMODEL_CONFIGURATOR CMC
        WHERE 1=1
          AND CMC.ISVISIBLE IS NOT NULL ';

--- Gestione parametri opzionali
   IF p_codMarket IS NOT NULL THEN
      v_sqlStmt := v_sqlStmt || ' AND CMC.CODMARKET = :p_codMarket ';
   ELSE
      v_sqlStmt := v_sqlStmt || ' AND :p_codMarket IS NULL ';
   END IF;
   IF p_codBrand IS NOT NULL THEN
      v_sqlStmt := v_sqlStmt || ' AND CMC.CODBRAND = :p_codBrand ';
   ELSE
      v_sqlStmt := v_sqlStmt || ' AND :p_codBrand IS NULL ';
   END IF;
   IF p_codCommercialModel IS NOT NULL THEN
      v_sqlStmt := v_sqlStmt || ' AND CMC.CODCOMMERCIALMODEL = :p_codCommercialModel ';
   ELSE
      v_sqlStmt := v_sqlStmt || ' AND :p_codCommercialModel IS NULL ';
   END IF;
   IF p_codCommercialVersion IS NOT NULL THEN
      v_sqlStmt := v_sqlStmt || ' AND CMC.CODCOMMERCIALVERSION = :p_codCommercialVersion ';
   ELSE
      v_sqlStmt := v_sqlStmt || ' AND :p_codCommercialVersion IS NULL ';
   END IF;
   IF p_codVehicleType IS NOT NULL THEN
      v_sqlStmt := v_sqlStmt || ' AND CMC.CODVEHICLETYPE = :p_codVehicleType ';
   ELSE
      v_sqlStmt := v_sqlStmt || ' AND :p_codVehicleType IS NULL ';
   END IF;

   v_sqlStmt := v_sqlStmt || ' ORDER BY CMC.CODMARKET,
                            CMC.CODBRAND,
                            CMC.CODCOMMERCIALMODEL,
                            CMC.CODCOMMERCIALVERSION
                   ) SEL001
          ) SEL002
    WHERE SEL002.NROW# >= NVL(:p_startRow,1)
      AND SEL002.NROW# < NVL(:p_startRow,1) + NVL(:p_numRows,10) ';

   OPEN myCursor FOR v_sqlStmt USING p_codMarket,
                                     p_codBrand,
                                     p_codCommercialModel,
                                     p_codCommercialVersion,
                                     p_codVehicleType,
                                     p_startRow,
                                     p_startRow,
                                     p_numRows;

   RETURN myCursor;

END findAll;