The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT id
FROM okc_k_headers_v
WHERE authoring_org_id = cp_organization_id
AND scs_code = 'LEASE';
iex_open_interface_pub.insert_pending(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
p_object1_id1 => l_oinv_rec.khr_id,
p_object1_id2 => '#',
p_jtot_object1_code => 'OKX_LEASE',
p_action => IEX_OPI_PVT.ACTION_REPORT_CB,
p_status => IEX_OPI_PVT.STATUS_PENDING_ALL,
p_comments => OKC_API.G_MISS_CHAR,
p_ext_agncy_id => NULL,
p_review_date => NULL,
p_recall_date => NULL,
p_automatic_recall_flag => NULL,
p_review_before_recall_flag => NULL,
x_return_status => l_return_status,
x_msg_count => lx_msg_count,
x_msg_data => lx_msg_data);
PROCEDURE insert_pending_hst(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
p_iohv_rec IN iohv_rec_type,
x_iohv_rec OUT NOCOPY iohv_rec_type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) AS
l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
l_api_name CONSTANT VARCHAR2(30) := 'insert_pending_hst';
SELECT id
FROM iex_open_int_hst
WHERE jtot_object1_code = cp_jtot_object1_code
AND object1_id1 = cp_object1_id1
AND object1_id2 = cp_object1_id2
AND action = cp_action
AND status = cp_status;
iex_open_int_hst_pub.update_open_int_hst(p_api_version => l_api_version
,p_init_msg_list => p_init_msg_list
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_iohv_rec => l_iohv_rec
,x_iohv_rec => lx_iohv_rec);
iex_open_int_hst_pub.insert_open_int_hst(p_api_version => l_api_version
,p_init_msg_list => p_init_msg_list
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_iohv_rec => l_iohv_rec
,x_iohv_rec => lx_iohv_rec);
END insert_pending_hst;
iex_open_int_hst_pub.insert_open_int_hst(
p_api_version => l_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => lx_msg_count,
x_msg_data => lx_msg_data,
p_iohv_rec => lp_iohv_rec,
x_iohv_rec => lx_iohv_rec);
iex_open_int_hst_pub.update_open_int_hst(p_api_version => l_api_version
,p_init_msg_list => p_init_msg_list
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_iohv_rec => l_iohv_rec
,x_iohv_rec => lx_iohv_rec);
SELECT OIN.ID,
OIN.PARTY_ID,
OIN.PARTY_NAME,
OIN.PARTY_TYPE,
OIN.DATE_OF_BIRTH,
OIN.PLACE_OF_BIRTH,
OIN.PERSON_IDENTIFIER,
OIN.PERSON_IDEN_TYPE,
OIN.COUNTRY,
OIN.ADDRESS1,
OIN.ADDRESS2,
OIN.ADDRESS3,
OIN.ADDRESS4,
OIN.CITY,
OIN.POSTAL_CODE,
OIN.STATE,
OIN.PROVINCE,
OIN.COUNTY,
OIN.PO_BOX_NUMBER,
OIN.HOUSE_NUMBER,
OIN.STREET_SUFFIX,
OIN.APARTMENT_NUMBER,
OIN.STREET,
OIN.RURAL_ROUTE_NUMBER,
OIN.STREET_NUMBER,
OIN.BUILDING,
OIN.FLOOR,
OIN.SUITE,
OIN.ROOM,
OIN.POSTAL_PLUS4_CODE,
OIN.CAS_ID,
OIN.CASE_NUMBER,
OIN.KHR_ID,
OIN.CONTRACT_NUMBER,
OIN.CONTRACT_TYPE,
OIN.CONTRACT_STATUS,
OIN.ORIGINAL_AMOUNT,
OIN.START_DATE,
OIN.CLOSE_DATE,
OIN.TERM_DURATION,
OIN.MONTHLY_PAYMENT_AMOUNT,
OIN.LAST_PAYMENT_DATE,
OIN.DELINQUENCY_OCCURANCE_DATE,
OIN.PAST_DUE_AMOUNT,
OIN.REMAINING_AMOUNT,
OIN.CREDIT_INDICATOR,
OIN.NOTIFICATION_DATE,
OIN.CREDIT_BUREAU_REPORT_DATE,
OIN.CONTACT_ID,
OIN.CONTACT_NAME,
OIN.CONTACT_PHONE,
OIN.CONTACT_EMAIL,
OIN.OBJECT_VERSION_NUMBER,
OIN.ORG_ID,
OIN.REQUEST_ID,
OIN.PROGRAM_APPLICATION_ID,
OIN.PROGRAM_ID,
OIN.PROGRAM_UPDATE_DATE,
OIN.ATTRIBUTE_CATEGORY,
OIN.ATTRIBUTE1,
OIN.ATTRIBUTE2,
OIN.ATTRIBUTE3,
OIN.ATTRIBUTE4,
OIN.ATTRIBUTE5,
OIN.ATTRIBUTE6,
OIN.ATTRIBUTE7,
OIN.ATTRIBUTE8,
OIN.ATTRIBUTE9,
OIN.ATTRIBUTE10,
OIN.ATTRIBUTE11,
OIN.ATTRIBUTE12,
OIN.ATTRIBUTE13,
OIN.ATTRIBUTE14,
OIN.ATTRIBUTE15,
OIN.CREATED_BY,
OIN.CREATION_DATE,
OIN.LAST_UPDATED_BY,
OIN.LAST_UPDATE_DATE,
OIN.LAST_UPDATE_LOGIN,
IOH.ID,
IOH.OBJECT1_ID1,
IOH.OBJECT1_ID2,
IOH.JTOT_OBJECT1_CODE,
IOH.ACTION,
IOH.STATUS,
IOH.COMMENTS,
IOH.REQUEST_DATE,
IOH.PROCESS_DATE,
IOH.EXT_AGNCY_ID,
IOH.REVIEW_DATE,
IOH.RECALL_DATE
FROM Okl_Open_Int OIN
,Iex_Open_Int_Hst IOH
WHERE OIN.khr_id = TO_NUMBER(IOH.OBJECT1_ID1)
AND IOH.JTOT_OBJECT1_CODE = 'OKX_LEASE'
AND ((l_case_passed = Okc_Api.G_FALSE) OR
(l_case_passed = Okc_Api.G_TRUE AND OIN.case_number = cp_case_number))
AND OIN.org_id = cp_organization_id
AND ((IOH.STATUS = STATUS_PENDING_AUTO) OR
(IOH.STATUS = STATUS_PENDING_MANUAL) OR
(IOH.STATUS = STATUS_PENDING_ALL));
l_oinv_rec.program_update_date,
l_oinv_rec.attribute_category,
l_oinv_rec.attribute1,
l_oinv_rec.attribute2,
l_oinv_rec.attribute3,
l_oinv_rec.attribute4,
l_oinv_rec.attribute5,
l_oinv_rec.attribute6,
l_oinv_rec.attribute7,
l_oinv_rec.attribute8,
l_oinv_rec.attribute9,
l_oinv_rec.attribute10,
l_oinv_rec.attribute11,
l_oinv_rec.attribute12,
l_oinv_rec.attribute13,
l_oinv_rec.attribute14,
l_oinv_rec.attribute15,
l_oinv_rec.created_by,
l_oinv_rec.creation_date,
l_oinv_rec.last_updated_by,
l_oinv_rec.last_update_date,
l_oinv_rec.last_update_login,
l_iohv_rec.id,
l_iohv_rec.object1_id1,
l_iohv_rec.object1_id2,
l_iohv_rec.jtot_object1_code,
l_iohv_rec.action,
l_iohv_rec.status,
l_iohv_rec.comments,
l_iohv_rec.request_date,
l_iohv_rec.process_date,
l_iohv_rec.ext_agncy_id,
l_iohv_rec.review_date,
l_iohv_rec.recall_date;
SELECT oin.khr_id
,oin.org_id
,ioh.id
,ioh.object1_id1
,ioh.object1_id2
,ioh.jtot_object1_code
FROM okl_open_int oin
,iex_open_int_hst ioh
WHERE oin.id = cp_interface_id
AND oin.khr_id = TO_NUMBER(ioh.object1_id1)
AND ioh.jtot_object1_code = 'OKX_LEASE'
AND ioh.action = ACTION_REPORT_CB
AND ioh.status = STATUS_PROCESSED;
okl_open_int_pub.update_open_int(p_api_version => l_api_version
,p_init_msg_list => p_init_msg_list
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_oinv_rec => l_oinv_rec
,x_oinv_rec => lx_oinv_rec);
iex_open_int_hst_pub.insert_open_int_hst(p_api_version => l_api_version
,p_init_msg_list => p_init_msg_list
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_iohv_rec => l_iohv_rec
,x_iohv_rec => lx_iohv_rec);
okl_open_int_pub.update_open_int(p_api_version => l_api_version
,p_init_msg_list => p_init_msg_list
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_oinv_rec => l_oinv_rec
,x_oinv_rec => lx_oinv_rec);
iex_open_int_hst_pub.update_open_int_hst(p_api_version => l_api_version
,p_init_msg_list => p_init_msg_list
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_iohv_rec => l_iohv_rec
,x_iohv_rec => lx_iohv_rec);
SELECT oin.khr_id
,oin.org_id
,ioh.id
,ioh.object1_id1
,ioh.object1_id2
,ioh.jtot_object1_code
FROM okl_open_int oin
,iex_open_int_hst ioh
WHERE oin.id = cp_interface_id
AND oin.khr_id = TO_NUMBER(ioh.object1_id1)
AND ioh.jtot_object1_code = 'OKX_LEASE'
AND ioh.action = ACTION_TRANSFER_EXT_AGNCY
AND ioh.status = STATUS_PROCESSED;
okl_open_int_pub.update_open_int(p_api_version => l_api_version
,p_init_msg_list => p_init_msg_list
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_oinv_rec => l_oinv_rec
,x_oinv_rec => lx_oinv_rec);
iex_open_int_hst_pub.insert_open_int_hst(p_api_version => l_api_version
,p_init_msg_list => p_init_msg_list
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_iohv_rec => l_iohv_rec
,x_iohv_rec => lx_iohv_rec);
okl_open_int_pub.update_open_int(p_api_version => l_api_version
,p_init_msg_list => p_init_msg_list
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_oinv_rec => l_oinv_rec
,x_oinv_rec => lx_oinv_rec);
iex_open_int_hst_pub.update_open_int_hst(p_api_version => l_api_version
,p_init_msg_list => p_init_msg_list
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_iohv_rec => l_iohv_rec
,x_iohv_rec => lx_iohv_rec);
SELECT oin.khr_id
,oin.org_id
,oin.referral_number
,ioh.id
,ioh.object1_id1
,ioh.object1_id2
,ioh.jtot_object1_code
,ioh.ext_agncy_id
FROM okl_open_int oin
,iex_open_int_hst ioh
WHERE oin.id = cp_interface_id
AND oin.khr_id = TO_NUMBER(ioh.object1_id1)
AND ioh.jtot_object1_code = 'OKX_LEASE'
AND ioh.action = ACTION_TRANSFER_EXT_AGNCY
AND (ioh.status = STATUS_PROCESSED
OR ioh.status = STATUS_NOTIFIED);
okl_open_int_pub.update_open_int(p_api_version => l_api_version
,p_init_msg_list => p_init_msg_list
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_oinv_rec => l_oinv_rec
,x_oinv_rec => lx_oinv_rec);
iex_open_int_hst_pub.update_open_int_hst(p_api_version => l_api_version
,p_init_msg_list => p_init_msg_list
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_iohv_rec => l_iohv_rec
,x_iohv_rec => lx_iohv_rec);
iex_open_int_hst_pub.insert_open_int_hst(
p_api_version => l_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => lx_msg_count,
x_msg_data => lx_msg_data,
p_iohv_rec => lp_iohv_rec,
x_iohv_rec => lx_iohv_rec);
iex_open_interface_pub.insert_pending(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
p_object1_id1 => l_iohv_rec.object1_id1,
p_object1_id2 => l_iohv_rec.object1_id2,
p_jtot_object1_code => l_iohv_rec.jtot_object1_code,
p_action => ACTION_NOTIFY_EXT_AGNCY,
p_status => STATUS_PROCESSED,
p_comments => p_comments,
p_ext_agncy_id => l_iohv_rec.ext_agncy_id,
p_review_date => NULL,
p_recall_date => NULL,
p_automatic_recall_flag => NULL,
p_review_before_recall_flag => NULL,
x_return_status => l_return_status,
x_msg_count => lx_msg_count,
x_msg_data => lx_msg_data);
iex_open_int_hst_pub.update_open_int_hst(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => lx_msg_count,
x_msg_data => lx_msg_data,
p_iohv_rec => l_iohv_rec,
x_iohv_rec => lx_iohv_rec);
iex_open_int_hst_pub.update_open_int_hst(p_api_version => l_api_version
,p_init_msg_list => l_init_msg_list
,x_return_status => l_return_status
,x_msg_count => lx_msg_count
,x_msg_data => lx_msg_data
,p_iohv_rec => l_iohv_rec
,x_iohv_rec => lx_iohv_rec);
iex_open_int_hst_pub.insert_open_int_hst(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => lx_msg_count,
x_msg_data => lx_msg_data,
p_iohv_rec => lp_iohv_rec,
x_iohv_rec => lx_iohv_rec);
SELECT contract_number
,party_id
FROM OKL_OPEN_INT
WHERE khr_id = cp_contract_id;
SELECT TASK_TYPE_ID,
NAME
FROM JTF_TASK_TYPES_VL
WHERE NAME = 'Follow up action';
SELECT TASK.task_status_id,
TASK.name
FROM jtf_task_statuses_vl TASK
WHERE TRUNC(SYSDATE)
BETWEEN TRUNC(NVL(TASK.start_date_active, SYSDATE))
AND TRUNC(NVL(TASK.end_date_active, SYSDATE))
AND TASK.name = 'Open';
SELECT owner_resource_id
FROM iex_cases_all_b
WHERE cas_id = cp_case_id;
SELECT task_priority_id,
name
FROM jtf_task_priorities_vl
WHERE TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active, SYSDATE))
AND TRUNC(NVL(end_date_active, SYSDATE))
AND name = 'High';
SELECT OIN.ID,
OIN.KHR_ID,
OIN.CAS_ID,
OIN.PARTY_ID,
OIN.PARTY_NAME,
IOH.ID
FROM Okl_Open_Int OIN
,Iex_Open_Int_Hst IOH
WHERE OIN.khr_id = TO_NUMBER(IOH.object1_id1)
AND IOH.jtot_object1_code = 'OKX_LEASE'
AND (IOH.ACTION = ACTION_NOTIFY_CUST)
AND (IOH.STATUS = STATUS_PROCESSED)
AND ((l_case_passed = Okc_Api.G_FALSE) OR
(l_case_passed = Okc_Api.G_TRUE AND OIN.case_number = cp_case_number))
AND ((l_party_passed = Okc_Api.G_FALSE) OR
(l_party_passed = Okc_Api.G_TRUE AND OIN.party_id = cp_party_id))
AND (OIN.org_id = cp_organization_id);
SELECT OIN.ID,
OIN.KHR_ID,
OIN.CAS_ID,
IOH.ID,
IOH.OBJECT1_ID1,
IOH.OBJECT1_ID2,
IOH.JTOT_OBJECT1_CODE,
IOH.ACTION,
IOH.STATUS,
IOH.REQUEST_DATE,
IOH.PROCESS_DATE,
IOH.EXT_AGNCY_ID,
IOH.REVIEW_BEFORE_RECALL_FLAG
FROM Okl_Open_Int OIN
,Iex_Open_Int_Hst IOH
WHERE OIN.khr_id = TO_NUMBER(IOH.object1_id1)
AND IOH.jtot_object1_code = 'OKX_LEASE'
AND (IOH.ACTION = ACTION_TRANSFER_EXT_AGNCY)
AND (IOH.STATUS = STATUS_PROCESSED)
AND (TRUNC(IOH.REVIEW_DATE) = TRUNC(SYSDATE))
AND ((l_case_passed = Okc_Api.G_FALSE) OR
(l_case_passed = Okc_Api.G_TRUE AND OIN.case_number = cp_case_number))
AND ((l_ext_agncy_passed = Okc_Api.G_FALSE) OR
(l_ext_agncy_passed = Okc_Api.G_TRUE AND IOH.ext_agncy_id = cp_ext_agncy_id))
AND (OIN.org_id = cp_organization_id);
iex_open_int_hst_pub.update_open_int_hst(p_api_version => l_api_version
,p_init_msg_list => l_init_msg_list
,x_return_status => l_return_status
,x_msg_count => lx_msg_count
,x_msg_data => lx_msg_data
,p_iohv_rec => l_iohv_rec
,x_iohv_rec => lx_iohv_rec);
iex_open_int_hst_pub.insert_open_int_hst(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => lx_msg_count,
x_msg_data => lx_msg_data,
p_iohv_rec => lp_iohv_rec,
x_iohv_rec => lx_iohv_rec);
iex_open_int_hst_pub.update_open_int_hst(p_api_version => l_api_version
,p_init_msg_list => l_init_msg_list
,x_return_status => l_return_status
,x_msg_count => lx_msg_count
,x_msg_data => lx_msg_data
,p_iohv_rec => l_iohv_rec
,x_iohv_rec => lx_iohv_rec);
iex_open_interface_pub.insert_pending(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
p_object1_id1 => l_iohv_rec.object1_id1,
p_object1_id2 => l_iohv_rec.object1_id2,
p_jtot_object1_code => l_iohv_rec.jtot_object1_code,
p_action => IEX_OPI_PVT.ACTION_NOTIFY_EXT_AGNCY,
p_status => IEX_OPI_PVT.STATUS_PROCESSED,
p_comments => p_comments,
p_ext_agncy_id => NULL,
p_review_date => NULL,
p_recall_date => NULL,
p_automatic_recall_flag => NULL,
p_review_before_recall_flag => NULL,
x_return_status => l_return_status,
x_msg_count => lx_msg_count,
x_msg_data => lx_msg_data);
iex_open_int_hst_pub.update_open_int_hst(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => lx_msg_count,
x_msg_data => lx_msg_data,
p_iohv_rec => lp_iohv_rec,
x_iohv_rec => lx_iohv_rec);
iex_open_int_hst_pub.update_open_int_hst(p_api_version => l_api_version
,p_init_msg_list => l_init_msg_list
,x_return_status => l_return_status
,x_msg_count => lx_msg_count
,x_msg_data => lx_msg_data
,p_iohv_rec => l_iohv_rec
,x_iohv_rec => lx_iohv_rec);
iex_open_int_hst_pub.update_open_int_hst(p_api_version => l_api_version
,p_init_msg_list => l_init_msg_list
,x_return_status => l_return_status
,x_msg_count => lx_msg_count
,x_msg_data => lx_msg_data
,p_iohv_rec => l_iohv_rec
,x_iohv_rec => lx_iohv_rec);
SELECT OIN.ID,
OIN.KHR_ID,
IOH.ID,
IOH.EXT_AGNCY_ID
FROM Okl_Open_Int OIN
,Iex_Open_Int_Hst IOH
WHERE OIN.khr_id = TO_NUMBER(IOH.object1_id1)
AND IOH.jtot_object1_code = 'OKX_LEASE'
AND (IOH.ACTION = ACTION_NOTIFY_EXT_AGNCY)
AND (IOH.STATUS = STATUS_PROCESSED)
AND ((l_case_passed = Okc_Api.G_FALSE) OR
(l_case_passed = Okc_Api.G_TRUE AND OIN.case_number = cp_case_number))
AND ((l_ext_agncy_passed = Okc_Api.G_FALSE) OR
(l_ext_agncy_passed = Okc_Api.G_TRUE AND IOH.ext_agncy_id = cp_ext_agncy_id))
AND (OIN.org_id = cp_organization_id);
SELECT OIN.ID,
OIN.KHR_ID,
IOH.ID,
IOH.OBJECT1_ID1,
IOH.OBJECT1_ID2,
IOH.JTOT_OBJECT1_CODE,
IOH.EXT_AGNCY_ID
FROM Okl_Open_Int OIN
,Iex_Open_Int_Hst IOH
WHERE OIN.khr_id = TO_NUMBER(IOH.object1_id1)
AND IOH.jtot_object1_code = 'OKX_LEASE'
AND (IOH.ACTION = ACTION_TRANSFER_EXT_AGNCY)
AND (IOH.STATUS = STATUS_NOTIFIED OR IOH.STATUS = STATUS_PROCESSED)
AND (TRUNC(IOH.RECALL_DATE) = TRUNC(SYSDATE))
AND (NVL(IOH.AUTOMATIC_RECALL_FLAG,'N') = 'Y')
AND ((l_case_passed = Okc_Api.G_FALSE) OR
(l_case_passed = Okc_Api.G_TRUE AND OIN.case_number = cp_case_number))
AND ((l_ext_agncy_passed = Okc_Api.G_FALSE) OR
(l_ext_agncy_passed = Okc_Api.G_TRUE AND IOH.ext_agncy_id = cp_ext_agncy_id))
AND (OIN.org_id = cp_organization_id);
SELECT action
,status
FROM IEX_OPEN_INT_HST
WHERE id = cp_hst_id;
SELECT email_address
FROM hz_contact_points
WHERE owner_table_name = 'HZ_PARTIES'
AND owner_table_id = cp_party_id
AND contact_point_type = 'EMAIL'
AND primary_flag = 'Y'
AND status = 'A';
SELECT pvs.email_address
FROM iex_ext_agncy_b iea
,po_vendor_sites_all pvs
WHERE iea.external_agency_id = cp_ext_agncy_id
AND iea.vendor_site_id = pvs.vendor_site_id;
SELECT score_value
FROM IEX_SCORE_HISTORIES
WHERE score_object_id = cp_case_id
AND score_object_code = 'IEX_CASES'
AND score_id = cp_score_id
AND TRUNC(creation_date) = TRUNC(cp_creation_date)
ORDER BY creation_date DESC;
SELECT external_agency_id
FROM iex_ext_agncy_b
WHERE rank >= cp_score_value
AND SYSDATE BETWEEN effective_start_date AND nvl(effective_end_date, SYSDATE)
ORDER BY rank ASC;
SELECT ext_agncy_id
,status
,process_date
FROM iex_open_int_hst
WHERE object1_id1 = cp_object1_id1
AND object1_id2 = '#'
AND jtot_object1_code = 'OKX_LEASE'
AND action = ACTION_TRANSFER_EXT_AGNCY
AND (status = STATUS_PROCESSED
OR status = STATUS_RECALLED)
ORDER BY process_date DESC;
l_ext_agncy_tbl.delete(i);
SELECT score_value
FROM IEX_SCORE_HISTORIES
WHERE score_object_id = cp_case_id
AND score_object_code = 'IEX_CASES'
AND score_id = cp_score_id
AND TRUNC(creation_date) = TRUNC(cp_creation_date)
ORDER BY creation_date DESC;
SELECT score_value
FROM IEX_SCORE_HISTORIES
WHERE score_object_id = cp_case_id
AND score_object_code = 'IEX_CASES'
AND score_id = cp_score_id
AND TRUNC(creation_date) = TRUNC(cp_creation_date)
ORDER BY creation_date DESC;
SELECT del_status
FROM IEX_DEL_STATUSES
WHERE score_id = cp_score_id
AND cp_score_value BETWEEN score_value_low AND score_value_high;