Untitled

-- jsamdm_dev.mdm_product_view source

CREATE OR REPLACE
ALGORITHM = UNDEFINED VIEW `mdm_product_view` AS
select
    `mp`.`RECORD_ID` AS `RECORD_ID`,
    `mp`.`MDM_ID` AS `MDM_ID`,
    `mp`.`SERIES_ID` AS `SERIES_ID`,
    `mp`.`RELTIO_ID` AS `RELTIO_ID`,
    `mp`.`COUNTRY_ID` AS `COUNTRY_ID`,
    `mp`.`PRODUCT_NAME` AS `PRODUCT_NAME`,
    `mp`.`GROUP_TYPE` AS `GROUP_TYPE`,
    `mp`.`JANSSEN_MSTR_PRDCT_NM` AS `JANSSEN_MSTR_PRDCT_NM`,
    `mp`.`PRODUCT_PHASE` AS `PRODUCT_PHASE`,
    `mp`.`JNJ_FULL_COMPOUND_ID` AS `JNJ_FULL_COMPOUND_ID`,
    `mp`.`GENERIC_NAME` AS `GENERIC_NAME`,
    `mp`.`JNJ_FLAG` AS `JNJ_FLAG`,
    `mp`.`TH_AREA` AS `TH_AREA`,
    `mp`.`PRODUCT_STATUS` AS `PRODUCT_STATUS`,
    `mp`.`REQUEST_STATUS_ID` AS `REQUEST_STATUS_ID`,
    `mp`.`TA_SUB_TYPE` AS `TA_SUB_TYPE`,
    `mp`.`CURRENT_RECORD` AS `CURRENT_RECORD`,
    `mp`.`LAST_ACTIVE` AS `LAST_ACTIVE`,
    `mp`.`IS_DELETED` AS `IS_DELETED`,
    `mp`.`CREATED_DATE` AS `CREATED_DATE`,
    `mp`.`CREATED_BY_ID` AS `CREATED_BY_ID`,
    `mp`.`UPDATED_DATE` AS `UPDATED_DATE`,
    `mp`.`UPDATED_BY_ID` AS `UPDATED_BY_ID`,
    if((`mp`.`IS_DELETED` > 0),
    'Soft-Deleted',
    'In-Force') AS `DELETE_STATE`,
    `mt`.`TA_NAME` AS `TA_NAME`,
    `mc`.`COUNTRY_NAME` AS `COUNTRY_NAME`,
    `mr`.`REGION_ID` AS `REGION_ID`,
    `mr`.`REGION_NAME` AS `REGION_NAME`,
    `ms`.`STATUS_DESC` AS `REQUEST_STATUS`,
    `mpfv`.`RECORD_ID_2` AS `RECORD_ID_2`,
    `mpfv`.`COMARKETING_PARTNER` AS `COMARKETING_PARTNER`,
    `mpfv`.`MARKETED_BY` AS `MARKETED_BY`,
    `mpfv`.`JJ_OPERATING_COMPANY` AS `JJ_OPERATING_COMPANY`,
    `mpfv`.`AESPQCS_BE_REPORTED` AS `AESPQCS_BE_REPORTED`,
    `mpfv`.`BLACKBOX_WARNING` AS `BLACKBOX_WARNING`,
    `mpfv`.`PI_LINK` AS `PI_LINK`,
    `mpfv`.`FIELD_TEAM_BE_SUBMITTING_MIRS_ON_THIS_PRODUCT` AS `FIELD_TEAM_BE_SUBMITTING_MIRS_ON_THIS_PRODUCT`,
    `mpfv`.`COMMENTS` AS `COMMENTS`,
    `mpfv`.`IS_IT_TRADEMARK_OR_REGISTERED` AS `IS_IT_TRADEMARK_OR_REGISTERED`,
    `mpfv`.`BRAND_STRENGTH` AS `BRAND_STRENGTH`,
    `mpfv`.`INDICATION` AS `INDICATION`,
    `mpfv`.`FDA_APPROVED` AS `FDA_APPROVED`,
    `mpfv`.`QUADRANT` AS `QUADRANT`,
    `mpfv`.`WEBSITE_PRODUCT_NAME` AS `WEBSITE_PRODUCT_NAME`,
    `mpfv`.`ANALYTICS_DISPLAY_NAME` AS `ANALYTICS_DISPLAY_NAME`
from
    ((((((`mdm_product` `mp`
join `mdm_status` `ms` on
    ((`mp`.`REQUEST_STATUS_ID` = `ms`.`STATUS_ID`)))
left join `mdm_product_field_view` `mpfv` on
    ((`mp`.`RECORD_ID` = `mpfv`.`RECORD_ID_2`)))
left join `mdm_countries` `mc` on
    ((`mc`.`COUNTRY_ID` = `mp`.`COUNTRY_ID`)))
left join `mdm_region_countries` `mrc` on
    ((`mrc`.`COUNTRY_ID` = `mc`.`COUNTRY_ID`)))
left join `mdm_regions` `mr` on
    ((`mr`.`REGION_ID` = `mrc`.`REGION_ID`)))
left join `mdm_therapeutic` `mt` on
    (((`mt`.`TA_ID` = `mp`.`TH_AREA`)
        and (`mt`.`STATUS_ID` = 1))))
where
    (((`mp`.`LAST_ACTIVE` > 0)
        or (`mp`.`CURRENT_RECORD` = 1)
            or (`mp`.`IS_DELETED` = 1))
        and (`mp`.`IS_DELETED` <> 2));