DAAS | Discretization-Aware Architecture Search | Architecture library

WITH
cust AS
    (SELECT DISTINCT
            C.swCustomerId AS EnduserId,
            A.AssetID AS asset,
            suite.ctName AS Product
     FROM ReplicaCADS.dbo.TransactionHeader TH WITH (NOLOCK)
          INNER JOIN ReplicaCRMDB.dbo.SW_CUSTOMER C WITH (NOLOCK) ON C.swCustomerId = TH.EndUserCustomerId
          INNER JOIN ReplicaCADS.dbo.Asset A WITH (NOLOCK) ON TH.TransactionId = A.TransactionId
                                                          AND A.Status = 'Active'
          INNER JOIN ReplicaCADS.dbo.AssetComponent AC WITH (NOLOCK) ON AC.AssetId = A.AssetId
                                                                    AND AC.PrimaryFlag = 1
                                                                    AND AC.Status = 'Active'
          INNER JOIN ReplicaCADS.dbo.MaintenanceProgram MP WITH (NOLOCK) ON MP.AssetId = A.AssetId
                                                                        AND IsLatest = 1
                                                                        AND MP.Status = 'Active'
                                                                        AND MP.EndDate <> '2099-12-31 00:00:00.000'
                                                                        AND MP.MaintenanceType = 'Core'
          INNER JOIN ReplicaCRMDB.dbo.[ct_Product_Suite] suite WITH (NOLOCK) ON suite.ctSuiteID = A.ProductSuiteID
                                                                            AND suite.cTName LIKE 'DaaS'
          INNER JOIN Salesforce.[dbo].[Apttus__APTS_Agreement__c] agr ON agr.Vantive_Org_ID__c = C.ctOrgId
                                                                     AND Apttus__Status__c = 'Activated'
                                                                     AND Agreement_Type__c = 'Licensing'
                                                                     AND agr.Account_Geo__c LIKE 'APAC'
     WHERE NOT EXISTS (SELECT 1
                       FROM salesforce..Priority_Customer__c pr
                       WHERE pr.Account_Org_Id__c = C.CtOrgId)
       AND NOT EXISTS (SELECT 1
                       FROM ReplicaTransactionData..[Transaction] TN
                       WHERE TN.CustomerId = C.swCustomerId
                         AND Status = 'Pending'
                         AND QuoteType IS NULL)
       AND NOT EXISTS (SELECT 1
                       FROM [Salesforce]..Large_Customer__c LDC
                       WHERE LDC.Org_ID__c = C.ctOrgId)
       AND NOT EXISTS (SELECT 1
                       FROM [Salesforce].dbo.Account sac
                       WHERE sac.Org_ID__C = C.ctOrgId
                         AND High_Touch_Account__C = 'true')
       AND NOT EXISTS (SELECT 1
                       FROM salesforce..Priority_Customer__c pr
                       WHERE pr.Account_Org_Id__c = C.ctOrgId)
       AND NOT EXISTS (SELECT 1
                       FROM Salesforce..Asset_Maintenance_Program__c
                       WHERE frmAccount_Org_ID__c = C.ctOrgId
                         AND Maintenance_Type__c IN ('Advanced'))
       AND EXISTS (SELECT 1
                   FROM ReplicaCADS..AssetpricingData AP
                   WHERE AP.AssetId = A.AssetId))
SELECT TOP 1
       P.swLogin AS LoginId
FROM cust
     INNER JOIN ReplicaCRMDB.dbo.SW_PERSON P WITH (NOLOCK) ON P.swCustomerId = EnduserId
                                                          AND P.swStatus = 'Current'
                                                          AND SWLogin IS NOT NULL
                                                          AND P.ctLocale = 'en-US'
     INNER JOIN ReplicaCRMDB.dbo.CT_CONTACT_TYPE Contact WITH (NOLOCK) ON Contact.swContactId = P.swPersonId
     INNER JOIN ReplicaCRMDB.dbo.CT_MC_USERS MCUsers ON MCUsers.swPersonID = P.swPersonId
                                                    AND (MCUsers.ctPassword = '32CA9FC1A0F5B6330E3F4C8C1BBECDE9BEDB9573'
                                                      OR MCUsers.ctPassword = '')
ORDER BY NEWID();