--------------------------------------------------------
--  DDL for View NLCORP_MCP_CUSTOMER_CONSOLIDATED_VW
--------------------------------------------------------

  CREATE OR REPLACE FORCE EDITIONABLE VIEW "OTHQ_OWN"."NLCORP_MCP_CUSTOMER_CONSOLIDATED_VW" ("PAR_ACCOUNT", "CUSTOMER_INTERNAL_ID", "CUSTOMER_NAME", "ACTIVE_CUSTOMER", "METADATA_DOWNLOADED_TIMESTAMP_UTC", "ZOOMINFO_INDUSTRY", "PRIVATE_EQUITY_OR_VENTURE_CAPITAL_FIRM", "ASSOCIATIONS_BUYING_GROUPS", "ZOOMINFO_SUBINDUSTRY", "NAICS") DEFAULT COLLATION "USING_NLS_COMP"  AS 
  SELECT 
    CASE 
        WHEN l.CUSTOMER_INTERNAL_ID IS NULL 
         AND r.CUSTOMER_INTERNAL_ID IS NOT NULL THEN r.PAR_NAME
        ELSE l.PAR_ACCOUNT
    END AS PAR_ACCOUNT,
    CASE 
        WHEN l.CUSTOMER_INTERNAL_ID IS NULL 
         AND r.CUSTOMER_INTERNAL_ID IS NOT NULL THEN r.CUSTOMER_INTERNAL_ID
        ELSE l.CUSTOMER_INTERNAL_ID
    END AS CUSTOMER_INTERNAL_ID,
    CASE 
        WHEN l.CUSTOMER_INTERNAL_ID IS NULL 
         AND r.CUSTOMER_INTERNAL_ID IS NOT NULL THEN r.CUSTOMER_NAME
        ELSE l.NAME
    END AS CUSTOMER_NAME,
    CASE 
        WHEN l.CUSTOMER_INTERNAL_ID IS NULL 
         AND r.CUSTOMER_INTERNAL_ID IS NOT NULL THEN 'No'
        ELSE 'Yes' 
    END AS ACTIVE_CUSTOMER,
    COALESCE(LEAST(l.METADATA_DOWNLOADED_TIMESTAMP_UTC, r.METADATA_DOWNLOADED_TIMESTAMP_UTC), l.METADATA_DOWNLOADED_TIMESTAMP_UTC, r.METADATA_DOWNLOADED_TIMESTAMP_UTC) AS METADATA_DOWNLOADED_TIMESTAMP_UTC,
    l.ZOOMINFO_INDUSTRY,
    l.PRIVATE_EQUITY_OR_VENTURE_CAPITAL_FIRM,
    l.ASSOCIATIONS_BUYING_GROUPS,
    l.ZOOMINFO_SUBINDUSTRY,
    l.NAICS
FROM SRC_NLCORP_CUSTOMER_MCP_TB l
FULL OUTER JOIN SRC_NLCORP_PRODUCTION_ACCOUNTS_MAPPING_TB r
    ON l.CUSTOMER_INTERNAL_ID = r.CUSTOMER_INTERNAL_ID
   AND l.PAR_ACCOUNT_INTERNAL_ID = r.PAR_INTERNAL_ID
;
