Untitled

mdm_downstream_product_view:
-- jsamdm_dev.mdm_downstream_product_view source

CREATE OR REPLACE
ALGORITHM = UNDEFINED VIEW `mdm_downstream_product_view` AS
select
    `mpv`.`RECORD_ID` AS `RECORD_ID`,
    `mpv`.`MDM_ID` AS `MDM_ID`,
    `mpv`.`SERIES_ID` AS `SERIES_ID`,
    `mpv`.`RELTIO_ID` AS `RELTIO_ID`,
    `mpv`.`COUNTRY_ID` AS `COUNTRY_ID`,
    `mpv`.`PRODUCT_NAME` AS `PRODUCT_NAME`,
    `mpv`.`GROUP_TYPE` AS `GROUP_TYPE`,
    `mpv`.`JANSSEN_MSTR_PRDCT_NM` AS `JANSSEN_MSTR_PRDCT_NM`,
    `mpv`.`PRODUCT_PHASE` AS `PRODUCT_PHASE`,
    `mpv`.`JNJ_FULL_COMPOUND_ID` AS `JNJ_FULL_COMPOUND_ID`,
    `mpv`.`GENERIC_NAME` AS `GENERIC_NAME`,
    `mpv`.`JNJ_FLAG` AS `JNJ_FLAG`,
    `mpv`.`TH_AREA` AS `TH_AREA`,
    `mpv`.`PRODUCT_STATUS` AS `PRODUCT_STATUS`,
    `mpv`.`REQUEST_STATUS_ID` AS `REQUEST_STATUS_ID`,
    `mpv`.`TA_SUB_TYPE` AS `TA_SUB_TYPE`,
    `mpv`.`CURRENT_RECORD` AS `CURRENT_RECORD`,
    `mpv`.`LAST_ACTIVE` AS `LAST_ACTIVE`,
    `mpv`.`IS_DELETED` AS `IS_DELETED`,
    `mpv`.`CREATED_DATE` AS `CREATED_DATE`,
    `mpv`.`CREATED_BY_ID` AS `CREATED_BY_ID`,
    `mpv`.`UPDATED_DATE` AS `UPDATED_DATE`,
    `mpv`.`UPDATED_BY_ID` AS `UPDATED_BY_ID`,
    `mpv`.`DELETE_STATE` AS `DELETE_STATE`,
    `mpv`.`TA_NAME` AS `TA_NAME`,
    `mpv`.`COUNTRY_NAME` AS `COUNTRY_NAME`,
    `mpv`.`REGION_ID` AS `REGION_ID`,
    `mpv`.`REGION_NAME` AS `REGION_NAME`,
    `mpv`.`REQUEST_STATUS` AS `REQUEST_STATUS`,
    `mpv`.`RECORD_ID_2` AS `RECORD_ID_2`,
    `mpv`.`COMARKETING_PARTNER` AS `COMARKETING_PARTNER`,
    `mpv`.`MARKETED_BY` AS `MARKETED_BY`,
    `mpv`.`JJ_OPERATING_COMPANY` AS `JJ_OPERATING_COMPANY`,
    `mpv`.`AESPQCS_BE_REPORTED` AS `AESPQCS_BE_REPORTED`,
    `mpv`.`BLACKBOX_WARNING` AS `BLACKBOX_WARNING`,
    `mpv`.`PI_LINK` AS `PI_LINK`,
    `mpv`.`FIELD_TEAM_BE_SUBMITTING_MIRS_ON_THIS_PRODUCT` AS `FIELD_TEAM_BE_SUBMITTING_MIRS_ON_THIS_PRODUCT`,
    `mpv`.`COMMENTS` AS `COMMENTS`,
    `mpv`.`IS_IT_TRADEMARK_OR_REGISTERED` AS `IS_IT_TRADEMARK_OR_REGISTERED`,
    `mpv`.`BRAND_STRENGTH` AS `BRAND_STRENGTH`,
    `mpv`.`INDICATION` AS `INDICATION`,
    `mpv`.`FDA_APPROVED` AS `FDA_APPROVED`,
    `mpv`.`QUADRANT` AS `QUADRANT`,
    `mpv`.`WEBSITE_PRODUCT_NAME` AS `WEBSITE_PRODUCT_NAME`,
    `mpv`.`ANALYTICS_DISPLAY_NAME` AS `ANALYTICS_DISPLAY_NAME`,
    `mdrv`.`MDM_ID_2` AS `MDM_ID_2`,
    `mdrv`.`DWN_7` AS `DWN_7`,
    `mdrv`.`DWN_8` AS `DWN_8`,
    if((`mdp`.`RECORD_ID` is null),
    'No',
    'Yes') AS `PUBLISHED`
from
    ((`mdm_product_view` `mpv`
left join `mdm_downstream_record_view` `mdrv` on
    ((`mpv`.`MDM_ID` = `mdrv`.`MDM_ID_2`)))
left join (
    select
        `mdm_downstream_publish`.`RECORD_ID` AS `RECORD_ID`
    from
        `mdm_downstream_publish`
    group by
        `mdm_downstream_publish`.`RECORD_ID`) `mdp` on
    ((`mpv`.`RECORD_ID` = `mdp`.`RECORD_ID`)))
where
    ((`mpv`.`LAST_ACTIVE` = 1)
        and (`mpv`.`IS_DELETED` = 0));
mdm_downstream_record_view:
-- jsamdm_dev.mdm_downstream_record_view source

CREATE OR REPLACE
ALGORITHM = UNDEFINED VIEW `mdm_downstream_record_view` AS
select
    `mdp`.`MDM_ID` AS `MDM_ID_2`,
    max(if((`mdp`.`DWN_STRM_ID` = '7'), 'Y', NULL)) AS `DWN_7`,
    max(if((`mdp`.`DWN_STRM_ID` = '8'), 'Y', NULL)) AS `DWN_8`
from
    `mdm_downstream_product` `mdp`
group by
    `mdp`.`MDM_ID`;