Skip Headers

Oracle Advanced Collections Implementation Guide
Release 12.1
Part Number E13452-04
Go to Table of Contents
Contents
Go to previous page
Previous
Go to next page
Next

Oracle Lease and Finance Management Open Interface Tables

This appendix describes the Oracle Lease and Finance Management open interface tables.

This appendix covers the following topics:

Open Interface Tables

This appendix describes the Oracle Lease and Finance Management Open Interface tables used during the Report to Credit Bureau and Transfer to External Agency processes. Data is written to these open interface tables during these processes, and made available to the credit bureau or external agency.

Sections in this appendix include:

OKL_OPEN_INT Table

The OKL_OPEN_INT table provides contract information as shown in the following table.

Open Interface Table OKL_OPEN_INT
Column Name Null? Data Type Description
ID NOT NULL NUMBER Unique identifier
PARTY_ID NOT NULL NUMBER Customer ID
PARTY_NAME NOT NULL VARCHAR2(360) Customer name
PARTY_TYPE NOT NULL VARCHAR2(30) Customer type, for example, individual, organization
DATE_OF_BIRTH   DATE  
PLACE_OF_BIRTH   VARCHAR2(60)  
PERSON_IDENTIFIER   VARCHAR2(60) Person identifier, for example, Social Security Number
PERSON_IDEN_TYPE   VARCHAR2(30) For example, Social Security Number, tax ID, and so on
COUNTRY   VARCHAR2(60)  
ADDRESS1   VARCHAR2(240)  
ADDRESS2   VARCHAR2(240)  
ADDRESS3   VARCHAR2(240)  
ADDRESS4   VARCHAR2(240)  
CITY   VARCHAR2(60)  
POSTAL_CODE   VARCHAR2(60)  
STATE   VARCHAR2(60)  
PROVINCE   VARCHAR2(60)  
COUNTY   VARCHAR2(60)  
PO_BOX_NUMBER   VARCHAR2(50)  
HOUSE_NUMBER   VARCHAR2(50)  
STREET_SUFFIX   VARCHAR2(50)  
APARTMENT_NUMBER   VARCHAR2(50)  
STREET   VARCHAR2(50)  
RURAL_ROUTE_NUMBER   VARCHAR2(50)  
STREET_NUMBER   VARCHAR2(50)  
BUILDING   VARCHAR2(50)  
FLOOR   VARCHAR2(50)  
SUITE   VARCHAR2(50)  
ROOM   VARCHAR2(50)  
POSTAL_PLUS4_CODE   VARCHAR2(50)  
CAS_ID NOT NULL NUMBER Case identifier, foreign key to the EX_CASES_ALL_B table
CASE_NUMBER NOT NULL VARCHAR2(240) Case number (referenced from IEX_CASES_ALL_B)
KHR_ID NOT NULL NUMBER Contract ID
CONTRACT_NUMBER NOT NULL VARCHAR2(120) Contract number
CONTRACT_TYPE NOT NULL VARCHAR2(30) Contract type, for example, lease, loan
CONTRACT_STATUS NOT NULL VARCHAR2(30) Contract status
ORIGINAL_AMOUNT   NUMBER(14,3) Original amount on the contract
START_DATE   DATE Contract start date
CLOSE_DATE   DATE Contract close date
TERM_DURATION   NUMBER(10) Number of terms
MONTHLY_PAYMENT_AMOUNT   NUMBER(14,3)  
LAST_PAYMENT_DATE   DATE Date the last payment was made
DELINQUENCY_OCCURANCE_DATE   DATE Date on which the first missed payment was due
PAST_DUE_AMOUNT   NUMBER(14,3) Overdue amount
REMAINING_AMOUNT   NUMBER(14,3) Outstanding balance
CREDIT_INDICATOR   VARCHAR2(30) Qualifies remaining amount
NOTIFICATION_DATE   DATE Date on which notification of intent to report was sent to the customer to
CREDIT_BUREAU_REPORT_DATE   DATE Date on which the open interface record was accessed by the credit bureau
EXTERNAL_AGENCY_TRANSFER_DATE   DATE Date on which the open interface record was accessed by the external agency
EXTERNAL_AGENCY_RECALL_DATE   DATE Date on which the contract was recalled from the external agency
REFERRAL_NUMBER   NUMBER Number of times the case was transferred to an external agency
CONTACT_ID   NUMBER Contact ID of the collections agent to whom the case is assigned
CONTACT_NAME   VARCHAR2(360) Name of the collections agent to whom the case is assigned
CONTACT_PHONE   VARCHAR2(2000) Phone of the collections agent to whom the case is assigned
CONTACT_EMAIL   VARCHAR2(2000) E-mail address of the collections agent to whom the case is assigned

OKL_OPEN_INT_PRTY Table

The OKL_OPEN_INT_PRTY table provides guarantor information as shown in the following table.

Open Interface Table OKL_OPEN_INT_PRTY
Column Name Null? Data Type Description
ID NOT NULL NUMBER Unique identifier
KHR_ID NOT NULL NUMBER Contract ID
PARTY_ID NOT NULL NUMBER Guarantor ID
PARTY_NAME NOT NULL VARCHAR2(360) Guarantor name
COUNTRY   VARCHAR2(240)  
ADDRESS1   VARCHAR2(240)  
ADDRESS2   VARCHAR2(240)  
ADDRESS3   VARCHAR2(240)  
ADDRESS4   VARCHAR2(240)  
CITY   VARCHAR2(240)  
POSTAL_CODE   VARCHAR2(240)  
STATE   VARCHAR2(240)  
PROVINCE   VARCHAR2(240)  
COUNTY   VARCHAR2(240)  
PO_BOX_NUMBER   VARCHAR2(240)  
HOUSE_NUMBER   VARCHAR2(240)  
STREET_SUFFIX   VARCHAR2(240)  
APARTMENT_NUMBER   VARCHAR2(240)  
STREET   VARCHAR2(240)  
RURAL_ROUTE_NUMBER   VARCHAR2(240)  
STREET_NUMBER   VARCHAR2(240)  
BUILDING   VARCHAR2(240)  
FLOOR   VARCHAR2(240)  
SUITE   VARCHAR2(240)  
ROOM   VARCHAR2(240)  
POSTAL_PLUS4_CODE   VARCHAR2(240)  
PHONE_COUNTRY_CODE   VARCHAR2(10)  
PHONE_AREA_CODE   VARCHAR2(10)  
PHONE_NUMBER   VARCHAR2(40)  
PHONE_EXTENSION   VARCHAR2(20)  

OKL_OPEN_INT_ASST Table

The OKL_OPEN_INT_ASST table provides asset information as shown in the following table.

Open Interface Table OKL_OPEN_INT_ASST
Column Name Null? Data Type Description
ID NOT NULL NUMBER Unique identifier
KHR_ID NOT NULL NUMBER Contract ID
INSTANCE_NUMBER NOT NULL VARCHAR2(30) Instance number of the asset
ASSET_ID NOT NULL NUMBER(15) Asset ID
ASSET_NUMBER   VARCHAR2(240) Asset number
DESCRIPTION   VARCHAR2(240)  
ASSET_TYPE   VARCHAR2(240)  
MANUFACTURER_NAME   VARCHAR2(240)  
MODEL_NUMBER   VARCHAR2(240)  
SERIAL_NUMBER   VARCHAR2(240)  
TAG_NUMBER   VARCHAR2(240)  
ORIGINAL_COST   NUMBER  
QUANTITY   VARCHAR2(240)  
COUNTRY   VARCHAR2(240) Columns below store asset address information
ADDRESS1   VARCHAR2(240)  
ADDRESS2   VARCHAR2(240)  
ADDRESS3   VARCHAR2(240)  
ADDRESS4   VARCHAR2(240)  
CITY   VARCHAR2(240)  
POSTAL_CODE   VARCHAR2(240)  
STATE   VARCHAR2(240)  
PROVINCE   VARCHAR2(240)  
COUNTY   VARCHAR2(240)  
PO_BOX_NUMBER   VARCHAR2(240)  
HOUSE_NUMBER   VARCHAR2(240)  
STREET_SUFFIX   VARCHAR2(240)  
APARTMENT_NUMBER   VARCHAR2(240)  
STREET   VARCHAR2(240)  
RURAL_ROUTE_NUMBER   VARCHAR2(240)  
STREET_NUMBER   VARCHAR2(240)  
BUILDING   VARCHAR2(240)  
FLOOR   VARCHAR2(240)  
SUITE   VARCHAR2(240)  
ROOM   VARCHAR2(240)  
POSTAL_PLUS4_CODE   VARCHAR2(240)  

IEX_OPEN_INT_HST Table

The IEX_OPEN_INT_HST table provides open interface history information as show in the following table.

Open Interface Table IEX_OPEN_INT_HST
Column Name Null? Data Type Description
ID NOT NULL NUMBER Unique identifier
OBJECT1_ID1 NOT NULL VARCHAR2(40) Foreign key to JTF_OBJECTS_B table
OBJECT1_ID2   VARCHAR2(200) Foreign key to JTF_OBJECTS_B table
JTOT_OBJECT1_CODE NOT NULL VARCHAR2(30) Foreign key to JTF_OBJECTS_B table
ACTION NOT NULL VARCHAR2(240) Action performed on the open interface record, for example:
NOTIFY_CUST
REPORT_CB
TRANSFER_EXT_AGNCY
NOTIFY_EXT_AGNCY
STATUS NOT NULL VARCHAR2(240) Status of action performed on the open interface record, for example:
PENDING_AUTO
PENDING_MANUAL
PENDING_ALL
PROCESSED
COMPLETE
RECALLED
NOTIFIED
COLLECTED
COMMENTS   VARCHAR2(2000) Free form text entered by credit bureau & external agency
REQUEST_DATE NOT NULL DATE Date on which request was made
PROCESS_DATE   DATE Date on which request was processed
EXT_AGNCY_ID   NUMBER External agency ID, foreign key to the EX_EXT_AGNCY_B table
TRANSFER_DAYS   NUMBER(10) Number of days for which the contract is transferred to the external agency (note: REQUEST_DATE + TRANSFER_DAYS = the review date)
EXTEND_DAYS   NUMBER(10) This specifies the grace period after the external agency is notified of intent to recall
REVIEW_BEFORE_RECALL_FLAG   VARCHAR2(1) Specifies whether review is to be performed before recall