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.overflowInd = :overflowInd
    AND CaseDecisionObjective.relatedReference IN 
    ( 
    SELECT
     CAST(ServiceInvoiceLineItem.serviceInvoiceLineItemID AS CHAR(20))
    FROM
      Provider,
      ServiceInvoiceLineItem,
      ProviderOffering,
      ServiceOffering,
      ConcernRole
      WHERE Provider.providerConcernRoleID = :resourceID
      AND Provider.providerConcernRoleID =ConcernRole.ConcernRoleID 
      AND  ProviderOffering.providerConcernRoleID=Provider.providerConcernRoleID
      AND ProviderOffering.serviceOfferingID = ServiceOffering.serviceOfferingID 
      AND ServiceInvoiceLineItem.providerReferenceNo=ConcernRole.primaryAlternateID
      AND ServiceInvoiceLineItem.serviceID = ServiceOffering.serviceOfferingID 
    ) 
    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.overflowInd = :overflowInd
    AND CaseDecisionObjective.relatedReference IN 
    ( 
    SELECT
     CAST(ContractVersion.contractVersionID AS CHAR(20))
    FROM
      ContractVersion,
      ContractPOLink,
      FlatRateContract 
    WHERE ContractVersion.concernRoleID = :resourceID 
      AND ContractVersion.contractType = :contractType 
      AND ContractVersion.status = :contractStatus 
      AND FlatRateContract.contractVersionID = ContractVersion.contractVersionID 
    ) 
    AND InstructionLineItem.instructionLineItemType = :frContractInstructionLineItemType 
    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.overflowInd =:overflowInd
    AND CaseDecisionObjective.relatedReference IN 
    ( 
    SELECT
    CAST(ProviderRosterLineItem.providerRosterLineItemID AS CHAR(20))
    FROM
      ProviderRosterLineItem,
      RosterLineItem,
      Roster 
    WHERE Roster.concernRoleID = :resourceID 
      AND Roster.rosterID = RosterLineItem.rosterID 
      AND RosterLineItem.rosterLineItemID = ProviderRosterLineItem.rosterLineItemID 
    ) 
    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.overflowInd =:overflowInd
    AND CaseDecisionObjective.relatedReference IN 
    ( 
    SELECT
      CAST(Placement.placementID AS CHAR(20))
    FROM
      Placement,
      ProviderOffering,
      ServiceOffering 
    WHERE ProviderOffering.providerConcernRoleID = :resourceID 
      AND ProviderOffering.providerOfferingID = Placement.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