Operation Details
Description: Reads the performance measure against clients served for the service authorizations for all the provider offerings of the contract andoverlapping with contract period. @deprecated since 7.0.0.0, This functionality forms part of a feature that is no longer relevant to the product strategy and will not be replaced.
Sterotype: nsmulti
SQL 




SELECT
  serviceAuthorizationID,
  SUM(noOfClients)
INTO
  :resourceID,
  :count 
FROM
  
  ( 
  SELECT
    ServiceAuthorization.serviceAuthorizationID,
    count(distinct(ServiceAuthorization.concernRoleID)) AS noOfClients 
  FROM
    ServiceAuthorizationLineItem,
    ServiceAuthorization,
    ServiceOffering,
    ProviderOffering,
    ContractVersion,
    ContractPOLink
  WHERE ContractVersion.contractVersionID = :resourceID
    AND ContractVersion.contractVersionID = ContractPOLink.contractVersionID
     AND ProviderOffering.providerOfferingID = ContractPOLink.providerOfferingID
    AND ProviderOffering.serviceOfferingID = ServiceOffering.serviceOfferingID 
    AND ServiceOffering.serviceOfferingID = ServiceAuthorizationLineItem.serviceID 
    AND ServiceAuthorizationLineItem.recordStatus = :recordStatus 
    AND ServiceAuthorizationLineItem.serviceAuthorizationID = ServiceAuthorization.serviceAuthorizationID 
    AND ServiceAuthorization.recordStatus = :recordStatus 
    AND (
		ServiceAuthorizationLineItem.fromDate BETWEEN ContractVersion.startDate
 			 AND    ContractVersion.endDate
	  OR ServiceAuthorizationLineItem.toDate BETWEEN ContractVersion.startDate
			 AND ContractVersion.endDate
	  OR ContractVersion.startDate BETWEEN ServiceAuthorizationLineItem.fromDate
			 AND   ServiceAuthorizationLineItem.toDate
	  OR ContractVersion.endDate  BETWEEN ServiceAuthorizationLineItem.fromDate
			 AND       ServiceAuthorizationLineItem.toDate
)

  GROUP
    BY ServiceAuthorization.serviceAuthorizationID 
  
  UNION ALL
    
  SELECT
    ServiceAuthorization.serviceAuthorizationID,
    count(distinct(ServiceAuthorizationClient.concernRoleID)) AS noOfClients 
  FROM
    ServiceAuthorizationLineItem,
    ServiceAuthorization,
    ServiceAuthorizationClient,
    ServiceOffering,
    ProviderOffering,
    ContractVersion,
    ContractPOLink    
  WHERE ContractVersion.contractVersionID = :resourceID
    AND ContractVersion.contractVersionID = ContractPOLink.contractVersionID
    AND ProviderOffering.providerOfferingID = ContractPOLink.providerOfferingID
    AND ProviderOffering.serviceOfferingID = ServiceOffering.serviceOfferingID 
    AND ServiceOffering.serviceOfferingID = ServiceAuthorizationLineItem.serviceID 
    AND ServiceAuthorizationLineItem.recordStatus = :recordStatus 
    AND ServiceAuthorizationLineItem.serviceAuthorizationID = ServiceAuthorization.serviceAuthorizationID 
    AND ServiceAuthorization.serviceAuthorizationID = ServiceAuthorizationClient.serviceAuthorizationID 
    AND ServiceAuthorization.recordStatus = :recordStatus 
AND
 (
          ServiceAuthorizationLineItem.fromDate BETWEEN ContractVersion.startDate 
                        AND ContractVersion.endDate
	  OR ServiceAuthorizationLineItem.toDate BETWEEN ContractVersion.startDate
                       AND ContractVersion.endDate
	  OR ContractVersion.startDate BETWEEN ServiceAuthorizationLineItem.fromDate
                     AND  ServiceAuthorizationLineItem.toDate
	  OR ContractVersion.endDate  BETWEEN ServiceAuthorizationLineItem.fromDate 
                  AND   ServiceAuthorizationLineItem.toDate 
)

  GROUP
    BY ServiceAuthorization.serviceAuthorizationID 
  ) ServiceAuthorization 
GROUP
  BY serviceAuthorizationID