Untitled

CREATE DEFINER=`jsa_dev_admin`@`%` PROCEDURE `jsamdm_qa`.`productViewSP`()
BEGIN
  CREATE OR REPLACE VIEW mdm_field_view AS SELECT mpf.RECORD_ID as RECORD_ID_2, mfd.FIELD_NAME, mpf.FIELD_VALUE FROM mdm_field_details mfd LEFT JOIN mdm_product_field mpf on mfd.FIELD_ID = mpf.FIELD_ID WHERE mfd.IS_DYNAMIC = 'Y' AND mfd.IS_ACTIVE = 'Y' AND mfd.IS_DELETED = '0';

  SET SESSION group_concat_max_len = 10000;
  SELECT
    GROUP_CONCAT(
      CONCAT(
        ' MAX(IF(FIELD_NAME = ''',
        tbl.FIELD_NAME,
        ''', FIELD_VALUE, NULL)) AS ',
        tbl.FIELD_NAME
      )
    ) INTO @PivotQuery
  FROM  
  (SELECT FIELD_NAME FROM mdm_field_view GROUP BY FIELD_NAME) tbl;
  
  SET @PivotQuery = CONCAT('CREATE OR REPLACE VIEW mdm_product_field_view AS SELECT RECORD_ID_2 ', IF(@PivotQuery IS NULL, '',CONCAT(',',@PivotQuery)), ' FROM mdm_field_view as pf WHERE RECORD_ID_2 IS NOT NULL GROUP BY RECORD_ID_2');
  PREPARE statement FROM @PivotQuery;
  EXECUTE statement;
  
  CREATE OR REPLACE VIEW mdm_product_view AS 
    SELECT mp.*,IF(mp.IS_DELETED>0, "Soft-Deleted", "In-Force") as DELETE_STATE, mt.TA_NAME, mc.COUNTRY_NAME, mr.REGION_ID, mr.REGION_NAME, ms.STATUS_DESC as REQUEST_STATUS, mpfv.*  
    FROM mdm_product AS mp 
    INNER JOIN mdm_status AS ms ON mp.REQUEST_STATUS_ID = ms.STATUS_ID 
    LEFT JOIN mdm_product_field_view AS mpfv ON mp.RECORD_ID = mpfv.RECORD_ID_2 
    LEFT JOIN mdm_countries AS mc ON mc.COUNTRY_ID = mp.COUNTRY_ID
    LEFT JOIN mdm_region_countries AS mrc ON mrc.COUNTRY_ID = mc.COUNTRY_ID
    LEFT JOIN mdm_regions AS mr ON mr.REGION_ID = mrc.REGION_ID
    LEFT JOIN mdm_therapeutic AS mt ON mt.TA_ID = mp.TH_AREA AND mt.STATUS_ID = 1 
    WHERE (LAST_ACTIVE > 0 OR CURRENT_RECORD = 1 OR IS_DELETED = 1) AND IS_DELETED != 2;

  CREATE OR REPLACE VIEW mdm_product_full_view AS 
    SELECT mp.*,IF(mp.IS_DELETED>0, "Soft-Deleted", "In-Force") as DELETE_STATE, mt.TA_NAME, mc.COUNTRY_NAME, mr.REGION_ID, mr.REGION_NAME, ms.STATUS_DESC as REQUEST_STATUS, mpfv.*  
    FROM mdm_product AS mp 
    INNER JOIN mdm_status AS ms ON mp.REQUEST_STATUS_ID = ms.STATUS_ID 
    LEFT JOIN mdm_product_field_view AS mpfv ON mp.RECORD_ID = mpfv.RECORD_ID_2 
    LEFT JOIN mdm_countries AS mc ON mc.COUNTRY_ID = mp.COUNTRY_ID
    LEFT JOIN mdm_region_countries AS mrc ON mrc.COUNTRY_ID = mc.COUNTRY_ID
    LEFT JOIN mdm_regions AS mr ON mr.REGION_ID = mrc.REGION_ID
    LEFT JOIN mdm_therapeutic AS mt ON mt.TA_ID = mp.TH_AREA AND mt.STATUS_ID = 1 
    WHERE IS_DELETED != 2;
  COMMIT;
  CALL productDownstreamViewSP();
END