CREATE OR REPLACE VIEW BOOMIACCESS_USER.DATAHUB_ROBP_COMPANY_LVL1
(
    ID,
    LATEST_UPDATE,
    JSON_OBJ
)
BEQUEATH DEFINER
AS
    SELECT company.ID,
           (SELECT MAX (UPDATEDON)
              FROM (SELECT MAX (UPDATEDON)     AS UPDATEDON
                      FROM DATAHUB_COMPANY_RELATIONS_LEVEL1
                     WHERE DATAHUB_COMPANY_RELATIONS_LEVEL1.COMPANYID =
                           company.ID
                    UNION ALL
                    SELECT MAX (UPDATEDON)     AS UPDATEDON
                      FROM DATAHUB_COMPANY_EXTERNALRATINGS_LEVEL1
                     WHERE DATAHUB_COMPANY_EXTERNALRATINGS_LEVEL1.COMPANYID =
                           company.ID
                    UNION ALL
                    SELECT MAX (UPDATEDON)     AS UPDATEDON
                      FROM DATAHUB_COMPANY_EXTERNALREFERENCES_LEVEL1
                     WHERE DATAHUB_COMPANY_EXTERNALREFERENCES_LEVEL1.COMPANYID =
                           company.ID
                    UNION ALL
                    SELECT MAX (UPDATEDON)     AS UPDATEDON
                       FROM  BPARTNER.DATAHUB_COMPANY_NAMES_LEVEL1
                    WHERE DATAHUB_COMPANY_NAMES_LEVEL1.COMPANYID =
                            company.ID
                    UNION ALL
                    SELECT MAX (UPDATEDON)     AS UPDATEDON
                      FROM DATAHUB_COMPANY_EXTERNALREFERENCES_LEVEL1
                     WHERE DATAHUB_COMPANY_EXTERNALREFERENCES_LEVEL1.COMPANYID =
                           company.ID
                    UNION ALL
                    (SELECT GREATEST (MAX (addr.UPDATEDON),
                                      MAX (comms.UPDATEDON))    AS UPDATEDON
                       FROM DATAHUB_COMPANY_ADDRESSES_LEVEL1          addr,
                            DATAHUB_COMPANY_TELECOMUNICATIONS_LEVEL1  comms
                      WHERE     addr.COMPANYID = company.ID
                            AND addr.ID = comms.ADRESSID)
                    UNION ALL
                    SELECT MAX (UPDATEDON)     AS UPDATEDON
                      FROM DATAHUB_COMPANY_BASIC_LEVEL1
                     WHERE DATAHUB_COMPANY_BASIC_LEVEL1.ID = company.ID))
               LATEST_UPDATE,
           json_object (
               'ID' VALUE "ID",
               'NAME' VALUE "NAME",
               'COUNTRYCODE' VALUE COUNTRYCODE,
               'GROUPID' VALUE GROUPID,
               'COMPANYTYPEID' VALUE COMPANYTYPEID,
               'COMPANYTYPE' VALUE COMPANYTYPE,
               'ISACTIVE' VALUE
                   DECODE (UPPER (ISACTIVE),
                           'Y', 'true',
                           'N', 'false',
                           NULL),
               'COMPANYNACECODE' VALUE COMPANYNACECODE,
               'COMPANYNACE' VALUE COMPANYNACE,
               'COMPANYESACODE' VALUE COMPANYESACODE,
               'COMPANYESA' VALUE COMPANYESA,
               'UPDATEDON' VALUE UPDATEDON,
               'ISSIP' VALUE
                   DECODE (UPPER (ISSIP),  'Y', 'true',  'N', 'false',  NULL),
               'COMPANYLEGALFORM' VALUE COMPANYLEGALFORM,
               'ISICT' VALUE
                   DECODE (UPPER (ISICT),  'Y', 'true',  'N', 'false',  NULL),
                'NAMES' VALUE
                (
                    SELECT
                    JSON_ARRAYAGG
                    (
                        json_object
                        (
                            'ID' VALUE "ID",
                            'NAME' VALUE "NAME",
                            'NAMELINE2' VALUE NAMELINE2,
                            'TYPEID' VALUE TYPEID,
                            'NAMETYPE' VALUE NAMETYPE,
                            'NAMERANK' VALUE NAMERANK,
                            'NAMECOMMENT' VALUE NAMECOMMENT,
                            'UPDATEDON' VALUE UPDATEDON
                        )
                    ORDER BY UPDATEDON DESC)
                    FROM
                        BPARTNER.DATAHUB_COMPANY_NAMES_LEVEL1 names
                    WHERE
                        names.COMPANYID = company.ID
                ),
               'RELATIONS' VALUE
                   (SELECT JSON_ARRAYAGG (
                               json_object (
                                   'ID' VALUE "ID",
                                   'RELATIONSHIPTYPEID' VALUE
                                       RELATIONSHIPTYPEID,
                                   'RELATIONSHIPTYPE' VALUE RELATIONSHIPTYPE,
                                   'ENTITYID' VALUE ENTITYID,
                                   'ENTITYNAME' VALUE ENTITYNAME,
                                   'ENTITYTYPE' VALUE ENTITYTYPE,
                                   'ISMAIN' VALUE
                                       DECODE (UPPER (ISMAIN),
                                               'Y', 'true',
                                               'N', 'false',
                                               NULL),
                                   'UPDATEDON' VALUE UPDATEDON)
                               RETURNING CLOB)
                      FROM BPARTNER.DATAHUB_COMPANY_RELATIONS_LEVEL1 rels
                     WHERE rels.COMPANYID = COMPANY.ID),
               'EXTERNALRATINGS' VALUE
                   (SELECT JSON_ARRAYAGG (
                               json_object (
                                   'ID' VALUE ID,
                                   'TYPEID' VALUE TYPEID,
                                   'TYPECODE' VALUE TYPECODE,
                                   'TYPENAME' VALUE TYPENAME,
                                   'RATINGDATE' VALUE RATINGDATE,
                                   'VALUE' VALUE "VALUE",
                                   'RATINGCOMMENT' VALUE RATINGCOMMENT,
                                   'OUTLOOK' VALUE OUTLOOK,
                                   'UPDATEDON' VALUE UPDATEDON,
                                   'ISACTIVE' VALUE
                                       DECODE (UPPER (ISACTIVE),
                                               'Y', 'true',
                                               'N', 'false',
                                               NULL))
                               RETURNING CLOB)
                      FROM BPARTNER.DATAHUB_COMPANY_EXTERNALRATINGS_LEVEL1
                           ratings
                     WHERE ratings.COMPANYID = COMPANY.ID),
               'EXTERNALREFERENCES' VALUE
                   (SELECT JSON_ARRAYAGG (
                               json_object (
                                   'ID' VALUE "ID",
                                   'EXTERNALREFERENCETYPEID' VALUE
                                       EXTERNALREFERENCETYPEID,
                                   'EXTERNALREFERENCETYPECODE' VALUE
                                       EXTERNALREFERENCETYPECODE,
                                   'EXTERNALREFERENCETYPENAME' VALUE
                                       EXTERNALREFERENCETYPENAME,
                                   'EXTERNALREFERENCEVALUE' VALUE
                                       EXTERNALREFERENCEVALUE,
                                   'EXTERNALREFERENCECOMMENT' VALUE
                                       EXTERNALREFERENCECOMMENT,
                                   'UPDATEDON' VALUE UPDATEDON)
                               RETURNING CLOB)
                      FROM BPARTNER.DATAHUB_COMPANY_EXTERNALREFERENCES_LEVEL1
                           erefs
                     WHERE erefs.COMPANYID = COMPANY.ID),
               'Addresses' VALUE
                   (SELECT JSON_ARRAYAGG (
                               json_object (
                                   'ID' VALUE ID,
                                   'ISPRIMARY' VALUE
                                       DECODE (UPPER (ISPRIMARY),
                                               'Y', 'true',
                                               'N', 'false',
                                               NULL),
                                   'ISACTIVE' VALUE
                                       DECODE (UPPER (ISACTIVE),
                                               'Y', 'true',
                                               'N', 'false',
                                               NULL),
                                   'TYPEID' VALUE TYPEID,
                                   'TYPECODE' VALUE TYPECODE,
                                   'TYPENAME' VALUE TYPENAME,
                                   'LINE1' VALUE LINE1,
                                   'LINE2' VALUE LINE2,
                                   'LINE3' VALUE LINE3,
                                   'LINE4' VALUE LINE4,
                                   'LINE5' VALUE LINE5,
                                   'POSTALCODE' VALUE POSTALCODE,
                                   'CITYNAME' VALUE CITYNAME,
                                   'COUNTRYCODE' VALUE COUNTRYCODE,
                                   'COUNTRYNAME' VALUE COUNTRYNAME,
                                   'STATEPROVINCEID' VALUE STATEPROVINCEID,
                                   'STATEPROVINCECODE' VALUE
                                       STATEPROVINCECODE,
                                   'STATEPROVINCENAME' VALUE
                                       STATEPROVINCENAME,
                                   'UPDATEDON' VALUE UPDATEDON,
                                   'TELECOMMUNICATIONS' VALUE
                                       (SELECT JSON_ARRAYAGG (
                                                   json_object (
                                                       'ID' VALUE "ID",
                                                       'TELECOMUNICATION_NO' VALUE
                                                           "TELECOMUNICATION_NO",
                                                       'COMUNICATIONID' VALUE
                                                           (COMUNICATIONID),
                                                       'COMUNICATIONCODE' VALUE
                                                           (COMUNICATIONCODE),
                                                       'COMUNICATIONNAME' VALUE
                                                           COMUNICATIONNAME,
                                                       'UPDATEDON' VALUE
                                                           (UPDATEDON)
                                                       RETURNING CLOB)
                                                   RETURNING CLOB)
                                          FROM DATAHUB_COMPANY_TELECOMUNICATIONS_LEVEL1
                                               comms
                                         WHERE addr.ID = comms.ADRESSID)
                                   RETURNING CLOB)
                               RETURNING CLOB)
                      FROM DATAHUB_COMPANY_ADDRESSES_LEVEL1 addr
                     WHERE addr.COMPANYID = COMPANY.ID)
               RETURNING CLOB)
               AS json_obj
      FROM BPARTNER.DATAHUB_COMPANY_BASIC_LEVEL1 company;

GRANT SELECT ON BOOMIACCESS_USER.DATAHUB_ROBP_COMPANY_LVL1 TO BOOMI_USER;