3,491
社区成员
发帖
与我相关
我的任务
分享
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 F.DISTRICT_ID = G.DISTRICT_ID
AND G.STREET_ID = H.STREET_ID
--AND A.CONTRACT_CODE = 'd' 或者 AND upper(A.CONTRACT_CODE) = 'd'
AND EXISTS
(SELECT R.SUBS_ID
FROM (SELECT DISTINCT T.SUBS_ID
FROM IB_SUB_COMPANY_CONTRACT_SUBS T
WHERE 1 = 1
AND T.STATE = 'A'
) R
WHERE B.SUBS_ID = R.SUBS_ID)
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 F.DISTRICT_ID = G.DISTRICT_ID
AND G.STREET_ID = H.STREET_ID
--AND A.CONTRACT_CODE = 'd' 或者 AND upper(A.CONTRACT_CODE) = 'd'
AND EXISTS
(SELECT R.SUBS_ID
FROM (SELECT DISTINCT T.SUBS_ID
FROM IB_SUB_COMPANY_CONTRACT_SUBS T
WHERE 1 = 1
AND T.STATE = 'A'
) R
WHERE B.SUBS_ID = R.SUBS_ID)