SELECT A.CONTRACT_ID,

       A.CONTRACT_CODE,

       A.CUST_ID,

       A.PROJECT_NAME,

       A.EFF_DATE,

       A.EXP_DATE,

       A.RENEWAL_FLAG,

       A.SIGNATURE_DATE,

       A.DEMAND_COMPLETED_DATE,

       A.SALE_STAFF_ID,

       A.SALE_PROPORTION,

       A.SALE2_STAFF_ID,

       A.SALE2_PROPORTION,

       A.SALE_TYPE,

       A.SALE_CODE,

       A.STATE,

       A.STATE_DATE,

       A.CREATED_DATE,

       A.UPDATE_DATE,

       A.PARTY_TYPE,

       A.PARTY_CODE,

       A.COMMENTS,

       A.LINKMAN_ID,

       A.CHECK_PARTY_TYPE,

       A.CHECK_PARTY_CODE,

       A.CONTRACT_ORG_ID,

       A.CONTRACT_TERM,

       A.CANCEL_REASON,

       A.CHECK_FAILURE_REASON,

       FOR_COMM_QUERY.FORMAT_PRICE_VALUE_BY_CVBS(A.CHARGE, 0) CHARGE,

       B.SUBS_ID,

       B.SUBS_NAME,

       C.SALE_TYPE_NAME,

       E.ADDRESS,

       E.ADDRESS_ID,

       F.DISTRICT_NAME,

       G.STREET_NAME,

       H.ROAD_NAME

  FROM CVBS_CONTRACT     A,

       CVBS_SUBS         B,

       CVBS_SALE_TYPE    C,

       CVBS_SUBS_ADDRESS D,

       CVBS_ADDRESS      E,

       CVBS_DISTRICT     F,

       CVBS_STREET_INFO  G,

       CVBS_ROAD_INFO    H

 WHERE 1 = 1

   AND A.CONTRACT_ID = B.CONTRACT_ID

   AND A.SALE_TYPE = C.SALE_TYPE(+)

   AND A.PARENT_CONTRACT_ID IS NULL

   AND A.STATE IN ('E', 'G')

   AND B.SUBS_ID = D.SUBS_ID

   AND D.ADDRESS_ID = E.ADDRESS_ID

   AND D.STATE = 'A'

   AND D.ADDRESS_TYPE IN ('A', 'C')

   AND E.ROAD_ID = H.ROAD_ID(+)

   AND H.STREET_ID = G.STREET_ID(+)

   AND