APPS.OKC_REP_EXP_NTF_PVT SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 54

    SELECT
      contract_id,
      contract_number,
      contract_version_num,
      DECODE(SIGN(MONTHS_BETWEEN(contract_expiration_date, trunc(sysdate))),
        1, 'N', 'Y') AS expired_flag,
      notify_contact_role_id
    FROM  okc_rep_contracts_all
    WHERE contract_expiration_date is not null
    AND   contract_status_code = 'SIGNED'
    AND   expire_ntf_flag = 'Y'
    AND   contract_expiration_date <= trunc(sysdate) + expire_ntf_period
    AND   wf_exp_ntf_item_key IS NULL
      UNION ALL
    SELECT
      v.contract_id,
      v.contract_number,
      v.contract_version_num,
      DECODE(SIGN(MONTHS_BETWEEN(v.contract_expiration_date, trunc(sysdate))),
        1, 'N', 'Y') AS expired_flag,
      v.notify_contact_role_id
    FROM  okc_rep_contract_vers v ,okc_rep_contracts_all v2
    WHERE v.contract_status_code = 'SIGNED'
    AND   v2.contract_id = v.contract_id
    AND   v2.contract_status_code  NOT IN('SIGNED','TERMINATED','APPROVED')
    AND   v.expire_ntf_flag = 'Y'
    AND   v.contract_expiration_date <= trunc(sysdate) + v.expire_ntf_period
    AND   v.wf_exp_ntf_item_key IS NULL
    AND   v.contract_version_num = (
      SELECT  DISTINCT MAX(v1.contract_version_num)
      OVER (PARTITION BY v1.contract_id)
      FROM  okc_rep_contract_vers v1
      WHERE  v1.contract_id = v.contract_id)
      AND NOT EXISTS(
        SELECT  1
        FROM    okc_rep_contracts_all c1
        WHERE   c1.contract_id = v.contract_id
        AND     c1.contract_status_code = 'SIGNED'
        AND     c1.expire_ntf_flag = 'Y'
        AND     c1.contract_expiration_date <= TRUNC(SYSDATE) + c1.expire_ntf_period
        AND     c1.wf_exp_ntf_item_key IS NULL
      );