Operation Details
Description: Reads the performance measure against clients served for the service authorizations for the provider offering of the contract and overlapping 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 = :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 = :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