57: contract_version_num,
58: DECODE(SIGN(MONTHS_BETWEEN(contract_expiration_date, trunc(sysdate))),
59: 1, 'N', 'Y') AS expired_flag,
60: notify_contact_role_id
61: FROM okc_rep_contracts_all
62: WHERE contract_expiration_date is not null
63: AND contract_status_code = 'SIGNED'
64: AND expire_ntf_flag = 'Y'
65: AND contract_expiration_date <= trunc(sysdate) + expire_ntf_period
83: FROM okc_rep_contract_vers v1
84: WHERE v1.contract_id = v.contract_id)
85: AND NOT EXISTS(
86: SELECT 1
87: FROM okc_rep_contracts_all c1
88: WHERE c1.contract_id = v.contract_id
89: AND c1.contract_status_code = 'SIGNED'
90: AND c1.expire_ntf_flag = 'Y'
91: AND c1.contract_expiration_date <= TRUNC(SYSDATE) + c1.expire_ntf_period