Operation Details
Description: Reads the performance measure against the services delivered by 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(paymentAmt)
INTO
  :count 
FROM
  
  ( 
  SELECT
    SUM(InstructionLineItem.amount) AS paymentAmt 
  FROM
    CaseDecisionFinancialComp,
    CaseDecisionObjective,
    InstructionLineItem 
  WHERE CaseDecisionFinancialComp.caseDecisionObjectiveID = CaseDecisionObjective.caseDecisionObjectiveID 
    AND InstructionLineItem.financialCompID = CaseDecisionFinancialComp.financialCompID 
    AND CaseDecisionObjective.relatedReference IN 
    ( 
    SELECT
      CAST(ServiceInvoiceLineItem.serviceInvoiceLineItemID AS CHAR(20)) 
    FROM
      ServiceInvoiceLineItem,
      ProviderOffering,
      ServiceOffering ,
	 ContractVersion,
	 ContractPOLink
    WHERE  ContractVersion.contractVersionID = :resourceID 
      AND ContractVersion.contractVersionID   = ContractPOLink.contractVersionID
      AND ProviderOffering.providerOfferingID = ContractPOLink.providerOfferingID
      AND ProviderOffering.serviceOfferingID = :serviceOfferingID
      AND ProviderOffering.serviceOfferingID = ServiceOffering.serviceOfferingID 
      AND ServiceInvoiceLineItem.serviceID = ServiceOffering.serviceOfferingID 
	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 )
    ) 
    AND InstructionLineItem.instructionLineItemType = :siliInstructionLineItemType 
    AND InstructionLineItem.instructLineItemCategory = :instructLineItemCategory 
    AND InstructionLineItem.statusCode = :statusCode 
  

  UNION ALL
    
  SELECT
    SUM(InstructionLineItem.amount) AS paymentAmt 
  FROM
    CaseDecisionFinancialComp,
    CaseDecisionObjective,
    InstructionLineItem 
  WHERE CaseDecisionFinancialComp.caseDecisionObjectiveID = CaseDecisionObjective.caseDecisionObjectiveID 
    AND InstructionLineItem.financialCompID = CaseDecisionFinancialComp.financialCompID 
    AND CaseDecisionObjective.relatedReference IN 
    ( 
    SELECT
      CAST(ProviderRosterLineItem.providerRosterLineItemID AS CHAR(20)) 
    FROM
      ProviderRosterLineItem,
      RosterLineItem,
      Roster ,
     ProviderOffering,
      ContractVersion,
	ContractPOLink
    WHERE  ContractVersion.contractVersionID = :resourceID 
      AND ContractVersion.contractVersionID = ContractPOLink.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 (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)
    ) 
    AND InstructionLineItem.instructionLineItemType = :rosterInstructionLineItemType 
    AND InstructionLineItem.instructLineItemCategory = :instructLineItemCategory 
    AND InstructionLineItem.statusCode = :statusCode 
  
  UNION ALL
    
  SELECT
    SUM(InstructionLineItem.amount) AS paymentAmt 
  FROM
    CaseDecisionFinancialComp,
    CaseDecisionObjective,
    InstructionLineItem 
  WHERE CaseDecisionFinancialComp.caseDecisionObjectiveID = CaseDecisionObjective.caseDecisionObjectiveID 
    AND InstructionLineItem.financialCompID = CaseDecisionFinancialComp.financialCompID 
    AND CaseDecisionObjective.relatedReference IN 
    ( 
    SELECT
      CAST(Placement.placementID AS CHAR(20)) 
    FROM
      Placement,
      ProviderOffering,
      ServiceOffering ,
	  ContractVersion,
	  ContractPOLink
    WHERE ContractVersion.contractVersionID = :resourceID 
      AND ContractVersion.contractVersionID = ContractPOLink.contractVersionID
      AND ProviderOffering.providerOfferingID = ContractPOLink.providerOfferingID
      AND ProviderOffering.serviceOfferingID = :serviceOfferingID
	  AND Placement.providerOfferingID = ProviderOffering.providerOfferingID
      AND Placement.recordStatus = :recordStatus 
      AND ProviderOffering.serviceOfferingID = ServiceOffering.serviceOfferingID 
      AND ServiceOffering.placementPmtInd = :placementPmtInd 
	
	 
    ) 
    AND InstructionLineItem.instructionLineItemType = :placementInstructionLineItemType 
    AND InstructionLineItem.instructLineItemCategory = :instructLineItemCategory 
    AND InstructionLineItem.statusCode = :statusCode 
  ) InstructionLineItem