OKL Cancellation Confirmation for Auto Debit

  SELECT CHR.ID,
       CHR.CONTRACT_NUMBER,
       CHR.ORG_ID,
       CHR.STS_CODE CONTRACT_STATUS,
       CHR.START_DATE CONTRACT_EFFECTIVE_FROM,
       CHR.END_DATE CONTRACT_EFFECTIVE_TO,
       ROUND(MONTHS_BETWEEN(CHR.END_DATE,CHR.START_DATE)) CONTRACT_TERM,
       ORG.NAME ORG_NAME,
       ORG.ADDRESS_LINE_1 ORG_ADDRESS1,
       ORG.ADDRESS_LINE_2 ORG_ADDRESS2,
       ORG.REGION_1 ORG_REGION1,
       ORG.TELEPHONE_NUMBER_1 ORG_TELEPHONE1,
       ORG.TELEPHONE_NUMBER_2 ORG_TELEPHONE2,
       ORG.POSTAL_CODE,
       ORG.TOWN_OR_CITY, 
       SYSDATE SYSTEM_DATE,
       CUST.PARTY_NAME CUSTOMER,
       CUST.ADDRESS1 PARTY_ADDRESS1,
       CUST.ADDRESS2 PARTY_ADDRESS2,
       CUST.ADDRESS3 PARTY_ADDRESS3,
       CUST.ADDRESS4 PARTY_ADDRESS4,
       CUST.CITY PARTY_CITY,
       CUST.POSTAL_CODE PARTY_POSTAL_CODE,
       CUST.STATE PARTY_STATE,
       CUST.PROVINCE PARTY_PROVINCE,
       CUST.COUNTY PARTY_COUNTY,
       CUST.EMAIL_ADDRESS PARTY_EMAIL_ADDRESS,
       CNT.PARTY_NAME CONTACT_NAME,
       CNT.PERSON_ACADEMIC_TITLE CONTACT_TITLE,
       CNT.PRIMARY_PHONE_COUNTRY_CODE||' '||CNT.PRIMARY_PHONE_AREA_CODE||' '||
       CNT.PRIMARY_PHONE_NUMBER||' '||CNT.PRIMARY_PHONE_EXTENSION CONTACT_PRIMARY_PHONE
FROM OKC_K_HEADERS_ALL_B CHR,
     HR_ORGANIZATION_UNITS_V ORG,
     HZ_PARTIES CUST,
     HZ_CUST_ACCOUNTS CSTACC,
     HZ_PARTY_RELATIONSHIP_V REL,
     HZ_PARTIES CNT
WHERE CHR.ID = :P_CONTRACT_ID
AND CHR.ORG_ID = ORG.ORGANIZATION_ID
AND CSTACC.CUST_ACCOUNT_ID = CHR.CUST_ACCT_ID
AND CSTACC.PARTY_ID = CUST.PARTY_ID
AND CUST.PARTY_ID = REL.OBJECT_ID(+)
AND REL.RELATIONSHIP_TYPE_CODE = 'CONTACT_OF'
AND CNT.PARTY_ID(+) = REL.SUBJECT_ID
AND ROWNUM = 1