Operation Details
Description: Reads the performance measure against units delivered for a service invoice line item and roster line item 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: ns
SQL 

SELECT
  SUM(unitsDelivered)
INTO
  :count 
FROM
  
  ( 
  SELECT
    SUM(ServiceInvoiceLineItem.noOfUnits) AS unitsDelivered 
  FROM
    ServiceInvoiceLineItem,
    ProviderOffering,
    ServiceOffering ,
   ContractVersion, 
   ContractPOLink
  WHERE ContractVersion.contractVersionID =  :resourceID
  AND  ContractPOLink.contractVersionID = ContractVersion.contractVersionID
  AND ProviderOffering.providerOfferingID = ContractPOLink.providerOfferingID
  AND ProviderOffering.serviceOfferingID = :serviceOfferingID 
  AND ProviderOffering.serviceOfferingID = ServiceOffering.serviceOfferingID 
  AND ServiceInvoiceLineItem.serviceID = ServiceOffering.serviceOfferingID 
  AND ServiceInvoiceLineItem.status = :siliStatus 
  AND (
ServiceInvoiceLineItem.ServiceDateFrom BETWEEN ContractVersion.startDate AND ContractVersion.endDate
	  OR ServiceInvoiceLineItem.serviceDateTo BETWEEN ContractVersion.startDate AND ContractVersion.endDate
	  OR ContractVersion.startDate BETWEEN ServiceInvoiceLineItem.ServiceDateFrom AND ServiceInvoiceLineItem.serviceDateTo
	  OR ContractVersion.endDate  BETWEEN ServiceInvoiceLineItem.ServiceDateFrom AND ServiceInvoiceLineItem.serviceDateTo
 )

  UNION ALL
    
  SELECT
    SUM(RosterLineItem.totalUnitsDelivered) AS unitsDelivered 
  FROM
    Roster,
    RosterLineItem,
    ProviderRosterLineItem ,
   ProviderOffering,
  ContractVersion, 
  ContractPOLink
  WHERE  ContractVersion.contractVersionID =  :resourceID
  AND  ContractPOLink.contractVersionID = ContractVersion.contractVersionID
   AND ProviderOffering.providerOfferingID = ContractPOLink.providerOfferingID
   AND ProviderOffering.serviceOfferingID = :serviceOfferingID 
   AND Roster.serviceID = ProviderOffering.providerOfferingID
    AND Roster.rosterID = RosterLineItem.rosterID 
    AND RosterLineItem.rosterLineItemID = ProviderRosterLineItem.rosterLineItemID 
    AND ProviderRosterLineItem.status = :prliStatus 
	AND
 (
       RosterLineItem.serviceFrom BETWEEN ContractVersion.startDate AND ContractVersion.endDate
	  OR RosterLineItem.serviceTo BETWEEN ContractVersion.startDate AND ContractVersion.endDate
	  OR ContractVersion.startDate BETWEEN RosterLineItem.serviceFrom AND RosterLineItem.serviceTo
	  OR ContractVersion.endDate  BETWEEN RosterLineItem.serviceFrom AND RosterLineItem.serviceTo
)
	
  ) LineItems