select distinct contractorDet.contractor from ContractorDetail contractorDet where contractorDet.status.description=? and contractorDet.status.moduletype=? and (upper(contractorDet.contractor.code) like ? or upper(contractorDet.contractor.name) like ?) order by contractorDet.contractor.code,contractorDet.contractor.name

 
SELECT DISTINCT contractor1_.ID       AS ID139_,
  contractor1_.CREATEDBY              AS CREATEDBY139_,
  contractor1_.MODIFIEDBY             AS MODIFIEDBY139_,
  contractor1_.CREATEDDATE            AS CREATEDD4_?_,
  contractor1_.MODIFIEDDATE           AS MODIFIED5_?_,
  contractor1_.CODE                   AS CODE139_,
  contractor1_.NAME                   AS NAME139_,
  contractor1_.CORRESPONDENCE_ADDRESS AS CORRESPO8_?_,
  contractor1_.PAYMENT_ADDRESS        AS PAYMENT9_?_,
  contractor1_.CONTACT_PERSON         AS CONTACT10_?_,
  contractor1_.EMAIL                  AS EMAIL139_,
  contractor1_.NARRATION              AS NARRATION139_,
  contractor1_.PAN_NUMBER             AS PAN13_?_,
  contractor1_.TIN_NUMBER             AS TIN14_?_,
  contractor1_.BANK_ID                AS BANK15_?_,
  contractor1_.IFSC_CODE              AS IFSC16_?_,
  contractor1_.BANK_ACCOUNT           AS BANK17_?_,
  contractor1_.PWD_APPROVAL_CODE      AS PWD18_?_
FROM EGW_CONTRACTOR_DETAIL contractor0_,
  EGW_CONTRACTOR contractor1_,
  EGW_STATUS egwstatus2_,
  EGW_CONTRACTOR contractor4_
WHERE contractor0_.CONTRACTOR_ID=contractor1_.ID
AND contractor0_.STATUS_ID      =egwstatus2_.ID
AND contractor0_.CONTRACTOR_ID  =contractor4_.ID
AND egwstatus2_.DESCRIPTION     ='Active'
AND egwstatus2_.MODULETYPE      ='Contractor'
AND (upper(contractor4_.CODE) LIKE '%100%'
OR upper(contractor4_.NAME) LIKE '%100%')
ORDER BY contractor4_.CODE,
  contractor4_.NAME;

 
SELECT DISTINCT contractor1_.ID     AS ID270_,
    contractor1_.CREATEDBY              AS CREATEDBY270_,
    contractor1_.MODIFIEDBY             AS MODIFIEDBY270_,
    contractor1_.CREATEDDATE            AS CREATEDD4_270_,
    contractor1_.MODIFIEDDATE           AS MODIFIED5_270_,
    contractor1_.CODE                   AS CODE270_,
    contractor1_.NAME                   AS NAME270_,
    contractor1_.CORRESPONDENCE_ADDRESS AS CORRESPO8_270_,
    contractor1_.PAYMENT_ADDRESS        AS PAYMENT9_270_,
    contractor1_.CONTACT_PERSON         AS CONTACT10_270_,
    contractor1_.EMAIL                  AS EMAIL270_,
    contractor1_.NARRATION              AS NARRATION270_,
    contractor1_.PAN_NUMBER             AS PAN13_270_,
    contractor1_.TIN_NUMBER             AS TIN14_270_,
    contractor1_.BANK_ID                AS BANK15_270_,
    contractor1_.IFSC_CODE              AS IFSC16_270_,
    contractor1_.BANK_ACCOUNT           AS BANK17_270_,
    contractor1_.PWD_APPROVAL_CODE      AS PWD18_270_
  FROM EGW_CONTRACTOR_DETAIL contractor0_,
    EGW_CONTRACTOR contractor1_,
    EGW_STATUS egwstatus2_
  WHERE contractor0_.CONTRACTOR_ID=contractor1_.ID
  AND contractor0_.STATUS_ID      =egwstatus2_.ID
  AND egwstatus2_.DESCRIPTION     ='Active'
  AND egwstatus2_.MODULETYPE      ='Contractor'
  AND (upper(contractor1_.CODE) LIKE '%100%'
  OR upper(contractor1_.NAME) LIKE'%100%')
  ORDER BY contractor1_.CODE,
    contractor1_.NAME  ;