SELECT
  taxILI.instructLineItemID,
  taxILI.amount,
  taxILI.unprocessedAmount,
  taxILI.instructLineItemCategory,
  taxILI.instructionLineItemType,
  taxILI.financialCompID,
  taxILI.coverPeriodFrom,
  taxILI.coverPeriodTo,
  taxILI.effectiveDate,
  taxILI.creationDate,
  taxILI.statusCode,
  taxILI.caseID,
  taxILI.finInstructionID,
  taxILI.concernRoleID,
  taxILI.caseNomineeID,
  taxILI.primaryClientID,
  taxILI.deliveryMethodType,
  taxILI.creditDebitType,
  taxILI.currencyExchangeID,
  taxILI.currencyTypeCode,
  taxILI.adjustmentInd,
  taxILI.adjustmentFrequency,
  taxILI.nextAdjustmentDate,
  taxILI.instrumentGenInd,
  taxILI.fundID,
  taxILI.maximumAmount,
  taxILI.versionNo,
  taxILI.dueDate,
  taxILI.inRespectOfID
INTO
  :instructLineItemID,
  :amount,
  :unprocessedAmount,
  :instructLineItemCategory,
  :instructionLineItemType,
  :financialCompID,
  :coverPeriodFrom,
  :coverPeriodTo,
  :effectiveDate,
  :creationDate,
  :statusCode,
  :caseID,
  :finInstructionID,
  :concernRoleID,
  :caseNomineeID,
  :primaryClientID,
  :deliveryMethodType,
  :creditDebitType,
  :currencyExchangeID,
  :currencyTypeCode,
  :adjustmentInd,
  :adjustmentFrequency,
  :nextAdjustmentDate,
  :instrumentGenInd,
  :fundID,
  :maximumAmount,
  :versionNo,
  :dueDate,
  :inRespectOfID 
FROM
  InstructionLineItem nomineeILI,
  InstructionLineItem taxILI,
  InstructionLineItemRelation 
WHERE nomineeILI.caseNomineeID = :caseNomineeID 
  AND InstructionLineItemRelation.instructLineItemID = nomineeILI.instructLineItemID 
  AND taxILI.instructLineItemID = InstructionLineItemRelation.relatedLineItemID 
  AND taxILI.statusCode = :statusCode 
  AND 
  (
    taxILI.instructLineItemCategory = :instructLineItemCategoryTax 
    OR 
    (
      taxILI.instructLineItemCategory = :instructLineItemCategoryReversal 
      AND taxILI.concernRoleID IN 
      ( 
      SELECT
        DISTINCT(concernRoleID) 
      FROM
        InstructionLineItem 
      WHERE InstructionLineItem.instructLineItemCategory = :instructLineItemCategoryTax 
      ) 
    ) 
  ) 
ORDER BY taxILI.concernRoleID,
  taxILI.currencyTypeCode,
  taxILI.deliveryMethodType,
  taxILI.caseID