CMK_APPL_PARTNERS_SETUP

Details

Schema: FUSION
Object owner: CMK
Object type: VIEW

Query

SQL_Statement
WITH OPT_IN_ENABLED AS ( SELECT 1 FROM DUAL WHERE RCS_INTERFACE.CONVERT_IS_FEATURE_ENABLED_BOOLEAN_TO_CHAR('COMBO','ORA_CMK_B2B_DOCUMENT_SETUP') = 'Y' ), DOCUMENT_ENABLED AS ( SELECT 1 FROM CMK_DOC_PROPERTY_VALUES DPV WHERE DPV.DOC_PROPERTY_ID IN ( SELECT DP.DOC_PROPERTY_ID FROM CMK_DOC_PROPERTIES DP WHERE DP.DOCUMENT_ID = 40076 AND DP.PROPERTY_CODE = 'ORA_ENABLED_FLAG' ) AND DPV.VALUE = 'Y' ), DEFAULT_CONN_ENABLED AS ( SELECT 1 FROM CMK_DECISION_TABLE_DETAILS DTD JOIN CMK_DECISION_TABLE_HEADERS DTH ON DTD.HEADER_ID = DTH.HEADER_ID WHERE DTH.CONTEXT1 = 40076 AND DTH.CONTEXT2 = 'DEFAULT' AND DTD.STATUS = 'ORA_CMK_CONN_ENABLED' ) SELECT DISTINCT DTD.HEADER_ID AS APPL_PARTNER_ID, 'Supplier Site' AS PARTNER_KEY_TYPE, JSON_VALUE(DTD.DATA, '$.SupplierId') AS PARTNER_KEY1, JSON_VALUE(DTD.DATA, '$.SupplierSiteId') AS PARTNER_KEY2, JSON_VALUE(DTD.DATA, '$.SupplierName') AS PARTNER_KEY3, JSON_VALUE(DTD.DATA, '$.SupplierNumber') AS PARTNER_KEY4, JSON_VALUE(DTD.DATA, '$.SupplierSiteName') AS PARTNER_KEY5, CPP.APPL_PARTNER_CODE AS PROC_ACTION_CODE, NULL AS EXTERNAL_PARTNER_ID, CEPM.PROVIDER_ID AS SERVICE_PROVIDER_ID, CEPM.DOCUMENT_ID AS DOCUMENT_ID, CEPM.PARTNER_MESSAGE_ID AS PARTNER_MESSAGE_ID, CEPM.PARTNER_MESSAGE_NAME AS PARTNER_MESSAGE_NAME, DTD.DETAIL_ID AS APPL_PARTNER_DOC_ID, DTD.STATUS AS APPL_PARTNER_DOC_STATUS, 'Y' AS PRIMARY_RECIPIENT_FLAG, CDM.DELIVERY_METHOD_ID AS DELIVERY_METHOD_ID, CDM.DELIVERY_METHOD_TYPE AS DELIVERY_METHOD_TYPE, CDM.RECIPIENT_EMAIL_ID AS RECIPIENT_EMAIL_ID, CEPMC.OVERRIDE_STATUS AS OVERRIDE_STATUS FROM CMK_DECISION_TABLE_DETAILS DTD JOIN CMK_DECISION_TABLE_HEADERS DTH ON DTD.HEADER_ID = DTH.HEADER_ID JOIN CMK_EXTERNAL_PTNR_MSGS_B CEPM ON JSON_VALUE(DTD.DATA, '$.ConnectionId') = CEPM.PARTNER_MESSAGE_ID JOIN CMK_EXT_PTNR_MSGS_CUST CEPMC ON CEPM.PARTNER_MESSAGE_ID = CEPMC.PARTNER_MESSAGE_CUST_ID JOIN CMK_DELIVERY_METHODS CDM ON CEPMC.DELIVERY_METHOD_ID = CDM.DELIVERY_METHOD_ID LEFT JOIN CMK_PARTNER_PROPERTIES CPP ON CPP.PARTNER_KEY1 = JSON_VALUE(DTD.DATA, '$.SupplierId') AND CPP.PARTNER_KEY2 = JSON_VALUE(DTD.DATA, '$.SupplierSiteId') WHERE DTH.CONTEXT1 = 40076 AND DTH.CONTEXT2 = 'SUPPLIER_SITE' AND EXISTS (SELECT 1 FROM OPT_IN_ENABLED) AND EXISTS (SELECT 1 FROM DOCUMENT_ENABLED) UNION ALL SELECT DTD.HEADER_ID AS APPL_PARTNER_ID, 'Default' AS PARTNER_KEY_TYPE, TO_CHAR(SUPPLIERPEO.VENDOR_ID) AS PARTNER_KEY1, TO_CHAR(SUPPLIERSITEDPEO.VENDOR_SITE_ID) AS PARTNER_KEY2, PARTYPEO.PARTY_NAME AS PARTNER_KEY3, SUPPLIERPEO.SEGMENT1 AS PARTNER_KEY4, SUPPLIERSITEDPEO.VENDOR_SITE_CODE AS PARTNER_KEY5, NULL AS PROC_ACTION_CODE, NULL AS EXTERNAL_PARTNER_ID, CEPM.PROVIDER_ID AS SERVICE_PROVIDER_ID, CEPM.DOCUMENT_ID AS DOCUMENT_ID, CEPM.PARTNER_MESSAGE_ID AS PARTNER_MESSAGE_ID, CEPM.PARTNER_MESSAGE_NAME AS PARTNER_MESSAGE_NAME, DTD.DETAIL_ID AS APPL_PARTNER_DOC_ID, DTD.STATUS AS APPL_PARTNER_DOC_STATUS, 'Y' AS PRIMARY_RECIPIENT_FLAG, CDM.DELIVERY_METHOD_ID AS DELIVERY_METHOD_ID, CDM.DELIVERY_METHOD_TYPE AS DELIVERY_METHOD_TYPE, CDM.RECIPIENT_EMAIL_ID AS RECIPIENT_EMAIL_ID, CEPMC.OVERRIDE_STATUS AS OVERRIDE_STATUS FROM HZ_PARTIES PARTYPEO, POZ_SUPPLIERS SUPPLIERPEO, HZ_PARTY_SITES PARTYSITEPEO, POZ_SUPPLIER_SITES_ALL_M SUPPLIERSITEDPEO, FUN_BU_USAGES_V BUSINESSUNITUSAGEPEO,CMK_DECISION_TABLE_DETAILS DTD JOIN CMK_DECISION_TABLE_HEADERS DTH ON DTD.HEADER_ID = DTH.HEADER_ID JOIN CMK_EXTERNAL_PTNR_MSGS_B CEPM ON JSON_VALUE(DTD.DATA, '$.ConnectionId') = CEPM.PARTNER_MESSAGE_ID JOIN CMK_EXT_PTNR_MSGS_CUST CEPMC ON CEPM.PARTNER_MESSAGE_ID = CEPMC.PARTNER_MESSAGE_CUST_ID JOIN CMK_DELIVERY_METHODS CDM ON CEPMC.DELIVERY_METHOD_ID = CDM.DELIVERY_METHOD_ID WHERE DTH.CONTEXT1 = 40076 AND DTH.CONTEXT2 = 'DEFAULT' AND PARTYPEO.PARTY_ID = SUPPLIERPEO.PARTY_ID AND PARTYPEO.PARTY_ID = PARTYSITEPEO.PARTY_ID AND SUPPLIERPEO.VENDOR_ID = SUPPLIERSITEDPEO.VENDOR_ID AND SUPPLIERSITEDPEO.PARTY_SITE_ID = PARTYSITEPEO.PARTY_SITE_ID AND SUPPLIERSITEDPEO.PRC_BU_ID = BUSINESSUNITUSAGEPEO.BUSINESS_UNIT_ID AND SUPPLIERSITEDPEO.B2B_COMM_METHOD_CODE = 'CMK' AND PARTYPEO.PARTY_TYPE = 'ORGANIZATION' AND BUSINESSUNITUSAGEPEO.STATUS = 'A' AND BUSINESSUNITUSAGEPEO.MODULE_KEY = 'PROCUREMENT_BF' AND ( SUPPLIERPEO.VENDOR_ID, SUPPLIERSITEDPEO.VENDOR_SITE_ID ) NOT IN ( SELECT DISTINCT JSON_VALUE(DTD.DATA, '$.SupplierId') AS VENDOR_ID, JSON_VALUE(DTD.DATA, '$.SupplierSiteId') AS VENDOR_SITE_ID FROM CMK_DECISION_TABLE_DETAILS DTD, CMK_DECISION_TABLE_HEADERS DTH WHERE DTD.HEADER_ID = DTH.HEADER_ID AND DTH.CONTEXT1 = 40076 AND DTH.CONTEXT2 = 'SUPPLIER_SITE' ) AND EXISTS (SELECT 1 FROM DEFAULT_CONN_ENABLED) AND EXISTS (SELECT 1 FROM OPT_IN_ENABLED) AND EXISTS (SELECT 1 FROM DOCUMENT_ENABLED) UNION ALL SELECT CMK_APPLICATION_PARTNERS.APPL_PARTNER_ID, CMK_APPLICATION_PARTNERS.PARTNER_KEY_TYPE, CMK_APPLICATION_PARTNERS.PARTNER_KEY1, CMK_APPLICATION_PARTNERS.PARTNER_KEY2, CMK_APPLICATION_PARTNERS.PARTNER_KEY3, CMK_APPLICATION_PARTNERS.PARTNER_KEY4, CMK_APPLICATION_PARTNERS.PARTNER_KEY5, CMK_APPLICATION_PARTNERS.PROC_ACTION_CODE, CMK_APPLICATION_PARTNERS.EXTERNAL_PARTNER_ID, CMK_APPLICATION_PARTNERS.SERVICE_PROVIDER_ID, CMK_APPL_PARTNER_DOCS.DOCUMENT_ID, CMK_EXTERNAL_PTNR_MSGS_B.PARTNER_MESSAGE_ID, CMK_EXTERNAL_PTNR_MSGS_B.PARTNER_MESSAGE_NAME, CMK_APPL_PARTNER_DOCS.APPL_PARTNER_DOC_ID, CMK_APPL_PARTNER_DOCS.APPL_PARTNER_DOC_STATUS, CMK_APPL_PARTNER_DOCS.PRIMARY_RECIPIENT_FLAG, CMK_DELIVERY_METHODS.DELIVERY_METHOD_ID, CMK_DELIVERY_METHODS.DELIVERY_METHOD_TYPE, CMK_DELIVERY_METHODS.RECIPIENT_EMAIL_ID, CMK_EXT_PTNR_MSGS_CUST.OVERRIDE_STATUS FROM CMK_APPLICATION_PARTNERS JOIN CMK_APPL_PARTNER_DOCS ON CMK_APPLICATION_PARTNERS.APPL_PARTNER_ID = CMK_APPL_PARTNER_DOCS.APPL_PARTNER_ID JOIN CMK_EXTERNAL_PTNR_MSGS_B ON CMK_APPL_PARTNER_DOCS.DOCUMENT_ID = CMK_EXTERNAL_PTNR_MSGS_B.DOCUMENT_ID JOIN CMK_EXT_PTNR_MSGS_CUST ON CMK_EXTERNAL_PTNR_MSGS_B.PARTNER_MESSAGE_ID = CMK_EXT_PTNR_MSGS_CUST.PARTNER_MESSAGE_CUST_ID JOIN CMK_DELIVERY_METHODS ON CMK_EXT_PTNR_MSGS_CUST.DELIVERY_METHOD_ID = CMK_DELIVERY_METHODS.DELIVERY_METHOD_ID WHERE NOT EXISTS (SELECT 1 FROM OPT_IN_ENABLED) AND ( CMK_APPLICATION_PARTNERS.EXTERNAL_PARTNER_ID = CMK_EXTERNAL_PTNR_MSGS_B.EXTERNAL_PARTNER_ID OR ( CMK_APPLICATION_PARTNERS.SERVICE_PROVIDER_ID = CMK_EXTERNAL_PTNR_MSGS_B.SERVICE_PROVIDER_ID AND CMK_EXTERNAL_PTNR_MSGS_B.EXTERNAL_PARTNER_ID IS NULL AND NOT EXISTS ( SELECT 1 FROM CMK_EXTERNAL_PTNR_MSGS_B CMKEXTERNALPTNRMSGS WHERE CMKEXTERNALPTNRMSGS.EXTERNAL_PARTNER_ID = CMK_APPLICATION_PARTNERS.EXTERNAL_PARTNER_ID AND CMKEXTERNALPTNRMSGS.DOCUMENT_ID = CMK_EXTERNAL_PTNR_MSGS_B.DOCUMENT_ID ) ) )

SQL_Statement

WITH OPT_IN_ENABLED AS (

SELECT 1 FROM DUAL WHERE RCS_INTERFACE.CONVERT_IS_FEATURE_ENABLED_BOOLEAN_TO_CHAR('COMBO','ORA_CMK_B2B_DOCUMENT_SETUP') = 'Y'

),

DOCUMENT_ENABLED AS (

SELECT 1

FROM CMK_DOC_PROPERTY_VALUES DPV

WHERE DPV.DOC_PROPERTY_ID IN (

SELECT DP.DOC_PROPERTY_ID

FROM CMK_DOC_PROPERTIES DP

WHERE DP.DOCUMENT_ID = 40076

AND DP.PROPERTY_CODE = 'ORA_ENABLED_FLAG'

)

AND DPV.VALUE = 'Y'

),

DEFAULT_CONN_ENABLED AS (

SELECT 1

FROM CMK_DECISION_TABLE_DETAILS DTD

JOIN CMK_DECISION_TABLE_HEADERS DTH

ON DTD.HEADER_ID = DTH.HEADER_ID

WHERE DTH.CONTEXT1 = 40076

AND DTH.CONTEXT2 = 'DEFAULT'

AND DTD.STATUS = 'ORA_CMK_CONN_ENABLED'

)

SELECT DISTINCT

DTD.HEADER_ID AS APPL_PARTNER_ID,

'Supplier Site' AS PARTNER_KEY_TYPE,

JSON_VALUE(DTD.DATA, '$.SupplierId') AS PARTNER_KEY1,

JSON_VALUE(DTD.DATA, '$.SupplierSiteId') AS PARTNER_KEY2,

JSON_VALUE(DTD.DATA, '$.SupplierName') AS PARTNER_KEY3,

JSON_VALUE(DTD.DATA, '$.SupplierNumber') AS PARTNER_KEY4,

JSON_VALUE(DTD.DATA, '$.SupplierSiteName') AS PARTNER_KEY5,

CPP.APPL_PARTNER_CODE AS PROC_ACTION_CODE,

NULL AS EXTERNAL_PARTNER_ID,

CEPM.PROVIDER_ID AS SERVICE_PROVIDER_ID,

CEPM.DOCUMENT_ID AS DOCUMENT_ID,

CEPM.PARTNER_MESSAGE_ID AS PARTNER_MESSAGE_ID,

CEPM.PARTNER_MESSAGE_NAME AS PARTNER_MESSAGE_NAME,

DTD.DETAIL_ID AS APPL_PARTNER_DOC_ID,

DTD.STATUS AS APPL_PARTNER_DOC_STATUS,

'Y' AS PRIMARY_RECIPIENT_FLAG,

CDM.DELIVERY_METHOD_ID AS DELIVERY_METHOD_ID,

CDM.DELIVERY_METHOD_TYPE AS DELIVERY_METHOD_TYPE,

CDM.RECIPIENT_EMAIL_ID AS RECIPIENT_EMAIL_ID,

CEPMC.OVERRIDE_STATUS AS OVERRIDE_STATUS

FROM CMK_DECISION_TABLE_DETAILS DTD

JOIN CMK_DECISION_TABLE_HEADERS DTH

ON DTD.HEADER_ID = DTH.HEADER_ID

JOIN CMK_EXTERNAL_PTNR_MSGS_B CEPM

ON JSON_VALUE(DTD.DATA, '$.ConnectionId') = CEPM.PARTNER_MESSAGE_ID

JOIN CMK_EXT_PTNR_MSGS_CUST CEPMC

ON CEPM.PARTNER_MESSAGE_ID = CEPMC.PARTNER_MESSAGE_CUST_ID

JOIN CMK_DELIVERY_METHODS CDM

ON CEPMC.DELIVERY_METHOD_ID = CDM.DELIVERY_METHOD_ID

LEFT JOIN CMK_PARTNER_PROPERTIES CPP

ON CPP.PARTNER_KEY1 = JSON_VALUE(DTD.DATA, '$.SupplierId')

AND CPP.PARTNER_KEY2 = JSON_VALUE(DTD.DATA, '$.SupplierSiteId')

WHERE DTH.CONTEXT1 = 40076

AND DTH.CONTEXT2 = 'SUPPLIER_SITE'

AND EXISTS (SELECT 1 FROM OPT_IN_ENABLED)

AND EXISTS (SELECT 1 FROM DOCUMENT_ENABLED)

UNION ALL

SELECT DTD.HEADER_ID AS APPL_PARTNER_ID,

'Default' AS PARTNER_KEY_TYPE,

TO_CHAR(SUPPLIERPEO.VENDOR_ID) AS PARTNER_KEY1,

TO_CHAR(SUPPLIERSITEDPEO.VENDOR_SITE_ID) AS PARTNER_KEY2,

PARTYPEO.PARTY_NAME AS PARTNER_KEY3,

SUPPLIERPEO.SEGMENT1 AS PARTNER_KEY4,

SUPPLIERSITEDPEO.VENDOR_SITE_CODE AS PARTNER_KEY5,

NULL AS PROC_ACTION_CODE,

NULL AS EXTERNAL_PARTNER_ID,

CEPM.PROVIDER_ID AS SERVICE_PROVIDER_ID,

CEPM.DOCUMENT_ID AS DOCUMENT_ID,

CEPM.PARTNER_MESSAGE_ID AS PARTNER_MESSAGE_ID,

CEPM.PARTNER_MESSAGE_NAME AS PARTNER_MESSAGE_NAME,

DTD.DETAIL_ID AS APPL_PARTNER_DOC_ID,

DTD.STATUS AS APPL_PARTNER_DOC_STATUS,

'Y' AS PRIMARY_RECIPIENT_FLAG,

CDM.DELIVERY_METHOD_ID AS DELIVERY_METHOD_ID,

CDM.DELIVERY_METHOD_TYPE AS DELIVERY_METHOD_TYPE,

CDM.RECIPIENT_EMAIL_ID AS RECIPIENT_EMAIL_ID,

CEPMC.OVERRIDE_STATUS AS OVERRIDE_STATUS

FROM HZ_PARTIES PARTYPEO,

POZ_SUPPLIERS SUPPLIERPEO,

HZ_PARTY_SITES PARTYSITEPEO,

POZ_SUPPLIER_SITES_ALL_M SUPPLIERSITEDPEO,

FUN_BU_USAGES_V BUSINESSUNITUSAGEPEO,CMK_DECISION_TABLE_DETAILS DTD

JOIN CMK_DECISION_TABLE_HEADERS DTH

ON DTD.HEADER_ID = DTH.HEADER_ID

JOIN CMK_EXTERNAL_PTNR_MSGS_B CEPM

ON JSON_VALUE(DTD.DATA,

'$.ConnectionId') = CEPM.PARTNER_MESSAGE_ID

JOIN CMK_EXT_PTNR_MSGS_CUST CEPMC

ON CEPM.PARTNER_MESSAGE_ID = CEPMC.PARTNER_MESSAGE_CUST_ID

JOIN CMK_DELIVERY_METHODS CDM

ON CEPMC.DELIVERY_METHOD_ID = CDM.DELIVERY_METHOD_ID

WHERE DTH.CONTEXT1 = 40076

AND DTH.CONTEXT2 = 'DEFAULT'

AND PARTYPEO.PARTY_ID = SUPPLIERPEO.PARTY_ID

AND PARTYPEO.PARTY_ID = PARTYSITEPEO.PARTY_ID

AND SUPPLIERPEO.VENDOR_ID = SUPPLIERSITEDPEO.VENDOR_ID

AND SUPPLIERSITEDPEO.PARTY_SITE_ID = PARTYSITEPEO.PARTY_SITE_ID

AND SUPPLIERSITEDPEO.PRC_BU_ID = BUSINESSUNITUSAGEPEO.BUSINESS_UNIT_ID

AND SUPPLIERSITEDPEO.B2B_COMM_METHOD_CODE = 'CMK'

AND PARTYPEO.PARTY_TYPE = 'ORGANIZATION'

AND BUSINESSUNITUSAGEPEO.STATUS = 'A'

AND BUSINESSUNITUSAGEPEO.MODULE_KEY = 'PROCUREMENT_BF'

AND ( SUPPLIERPEO.VENDOR_ID,

SUPPLIERSITEDPEO.VENDOR_SITE_ID ) NOT IN (

SELECT DISTINCT JSON_VALUE(DTD.DATA,

'$.SupplierId') AS VENDOR_ID,

JSON_VALUE(DTD.DATA,

'$.SupplierSiteId') AS VENDOR_SITE_ID

FROM CMK_DECISION_TABLE_DETAILS DTD,

CMK_DECISION_TABLE_HEADERS DTH

WHERE DTD.HEADER_ID = DTH.HEADER_ID

AND DTH.CONTEXT1 = 40076

AND DTH.CONTEXT2 = 'SUPPLIER_SITE'

)

AND EXISTS (SELECT 1 FROM DEFAULT_CONN_ENABLED)

AND EXISTS (SELECT 1 FROM OPT_IN_ENABLED)

AND EXISTS (SELECT 1 FROM DOCUMENT_ENABLED)

UNION ALL

SELECT

CMK_APPLICATION_PARTNERS.APPL_PARTNER_ID,

CMK_APPLICATION_PARTNERS.PARTNER_KEY_TYPE,

CMK_APPLICATION_PARTNERS.PARTNER_KEY1,

CMK_APPLICATION_PARTNERS.PARTNER_KEY2,

CMK_APPLICATION_PARTNERS.PARTNER_KEY3,

CMK_APPLICATION_PARTNERS.PARTNER_KEY4,

CMK_APPLICATION_PARTNERS.PARTNER_KEY5,

CMK_APPLICATION_PARTNERS.PROC_ACTION_CODE,

CMK_APPLICATION_PARTNERS.EXTERNAL_PARTNER_ID,

CMK_APPLICATION_PARTNERS.SERVICE_PROVIDER_ID,

CMK_APPL_PARTNER_DOCS.DOCUMENT_ID,

CMK_EXTERNAL_PTNR_MSGS_B.PARTNER_MESSAGE_ID,

CMK_EXTERNAL_PTNR_MSGS_B.PARTNER_MESSAGE_NAME,

CMK_APPL_PARTNER_DOCS.APPL_PARTNER_DOC_ID,

CMK_APPL_PARTNER_DOCS.APPL_PARTNER_DOC_STATUS,

CMK_APPL_PARTNER_DOCS.PRIMARY_RECIPIENT_FLAG,

CMK_DELIVERY_METHODS.DELIVERY_METHOD_ID,

CMK_DELIVERY_METHODS.DELIVERY_METHOD_TYPE,

CMK_DELIVERY_METHODS.RECIPIENT_EMAIL_ID,

CMK_EXT_PTNR_MSGS_CUST.OVERRIDE_STATUS

FROM CMK_APPLICATION_PARTNERS

JOIN CMK_APPL_PARTNER_DOCS

ON CMK_APPLICATION_PARTNERS.APPL_PARTNER_ID = CMK_APPL_PARTNER_DOCS.APPL_PARTNER_ID

JOIN CMK_EXTERNAL_PTNR_MSGS_B

ON CMK_APPL_PARTNER_DOCS.DOCUMENT_ID = CMK_EXTERNAL_PTNR_MSGS_B.DOCUMENT_ID

JOIN CMK_EXT_PTNR_MSGS_CUST

ON CMK_EXTERNAL_PTNR_MSGS_B.PARTNER_MESSAGE_ID = CMK_EXT_PTNR_MSGS_CUST.PARTNER_MESSAGE_CUST_ID

JOIN CMK_DELIVERY_METHODS

ON CMK_EXT_PTNR_MSGS_CUST.DELIVERY_METHOD_ID = CMK_DELIVERY_METHODS.DELIVERY_METHOD_ID

WHERE NOT EXISTS (SELECT 1 FROM OPT_IN_ENABLED)

AND (

CMK_APPLICATION_PARTNERS.EXTERNAL_PARTNER_ID = CMK_EXTERNAL_PTNR_MSGS_B.EXTERNAL_PARTNER_ID

OR (

CMK_APPLICATION_PARTNERS.SERVICE_PROVIDER_ID = CMK_EXTERNAL_PTNR_MSGS_B.SERVICE_PROVIDER_ID

AND CMK_EXTERNAL_PTNR_MSGS_B.EXTERNAL_PARTNER_ID IS NULL

AND NOT EXISTS (

SELECT 1

FROM CMK_EXTERNAL_PTNR_MSGS_B CMKEXTERNALPTNRMSGS

WHERE CMKEXTERNALPTNRMSGS.EXTERNAL_PARTNER_ID = CMK_APPLICATION_PARTNERS.EXTERNAL_PARTNER_ID

AND CMKEXTERNALPTNRMSGS.DOCUMENT_ID = CMK_EXTERNAL_PTNR_MSGS_B.DOCUMENT_ID

)