The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT request_status_code,
object1_id1,
jtot_object1_code
FROM okl_trx_requests
where id=a_id;
SELECT sis_code
FROM okl_stream_interfaces
WHERE ID=a_id;
select c.amount
from
okl_streams a,
okl_strm_type_b b,
okl_strm_elements c
where
a.sty_id=b.id
and b.stream_type_purpose ='RENT'
and nvl(b.start_date,sysdate) <= sysdate
and nvl(b.end_date,sysdate) >= sysdate
and c.stm_id=a.id
and a.say_code='CURR'
and a.active_yn = 'Y'
--multigaap changes
AND a.PURPOSE_CODE IS NULL
--end multigaap changes
and to_char(c.stream_element_date,'MM-YYYY')=to_char(sysdate,'MM-YYYY')
and khr_id = a_khr_id;
SELECT start_Date,end_date
FROM
okc_k_headers_b
where id = a_khr_id;
SELECT term_Duration,after_tax_yield
FROM
okl_k_headers
where id = a_khr_id;
SELECT ID,
OBJECT_VERSION_NUMBER,
SFWT_FLAG,
CHR_ID_RESPONSE,
CHR_ID_AWARD,
INV_ORGANIZATION_ID,
STS_CODE,
QCL_ID,
SCS_CODE,
CONTRACT_NUMBER,
CURRENCY_CODE,
CONTRACT_NUMBER_MODIFIER,
ARCHIVED_YN,
DELETED_YN,
CUST_PO_NUMBER_REQ_YN,
PRE_PAY_REQ_YN,
CUST_PO_NUMBER,
SHORT_DESCRIPTION,
COMMENTS,
DESCRIPTION,
DPAS_RATING,
COGNOMEN,
TEMPLATE_YN,
TEMPLATE_USED,
DATE_APPROVED,
DATETIME_CANCELLED,
AUTO_RENEW_DAYS,
DATE_ISSUED,
DATETIME_RESPONDED,
NON_RESPONSE_REASON,
NON_RESPONSE_EXPLAIN,
RFP_TYPE,
CHR_TYPE,
KEEP_ON_MAIL_LIST,
SET_ASIDE_REASON,
SET_ASIDE_PERCENT,
RESPONSE_COPIES_REQ,
DATE_CLOSE_PROJECTED,
DATETIME_PROPOSED,
DATE_SIGNED,
DATE_TERMINATED,
DATE_RENEWED,
TRN_CODE,
START_DATE,
END_DATE,
AUTHORING_ORG_ID,
BUY_OR_SELL,
ISSUE_OR_RECEIVE,
ESTIMATED_AMOUNT,
ESTIMATED_AMOUNT_RENEWED,
CURRENCY_CODE_RENEWED,
UPG_ORIG_SYSTEM_REF,
UPG_ORIG_SYSTEM_REF_ID,
APPLICATION_ID,
ORIG_SYSTEM_SOURCE_CODE,
ORIG_SYSTEM_ID1,
ORIG_SYSTEM_REFERENCE1,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
FROM okc_k_headers_v chrv
WHERE chrv.id = p_id;
x_chrv_rec.DELETED_YN,
x_chrv_rec.CUST_PO_NUMBER_REQ_YN,
x_chrv_rec.PRE_PAY_REQ_YN,
x_chrv_rec.CUST_PO_NUMBER,
x_chrv_rec.SHORT_DESCRIPTION,
x_chrv_rec.COMMENTS,
x_chrv_rec.DESCRIPTION,
x_chrv_rec.DPAS_RATING,
x_chrv_rec.COGNOMEN,
x_chrv_rec.TEMPLATE_YN,
x_chrv_rec.TEMPLATE_USED,
x_chrv_rec.DATE_APPROVED,
x_chrv_rec.DATETIME_CANCELLED,
x_chrv_rec.AUTO_RENEW_DAYS,
x_chrv_rec.DATE_ISSUED,
x_chrv_rec.DATETIME_RESPONDED,
x_chrv_rec.NON_RESPONSE_REASON,
x_chrv_rec.NON_RESPONSE_EXPLAIN,
x_chrv_rec.RFP_TYPE,
x_chrv_rec.CHR_TYPE,
x_chrv_rec.KEEP_ON_MAIL_LIST,
x_chrv_rec.SET_ASIDE_REASON,
x_chrv_rec.SET_ASIDE_PERCENT,
x_chrv_rec.RESPONSE_COPIES_REQ,
x_chrv_rec.DATE_CLOSE_PROJECTED,
x_chrv_rec.DATETIME_PROPOSED,
x_chrv_rec.DATE_SIGNED,
x_chrv_rec.DATE_TERMINATED,
x_chrv_rec.DATE_RENEWED,
x_chrv_rec.TRN_CODE,
x_chrv_rec.START_DATE,
x_chrv_rec.END_DATE,
x_chrv_rec.AUTHORING_ORG_ID,
x_chrv_rec.BUY_OR_SELL,
x_chrv_rec.ISSUE_OR_RECEIVE,
x_chrv_rec.ESTIMATED_AMOUNT,
x_chrv_rec.ESTIMATED_AMOUNT_RENEWED,
x_chrv_rec.CURRENCY_CODE_RENEWED,
x_chrv_rec.UPG_ORIG_SYSTEM_REF,
x_chrv_rec.UPG_ORIG_SYSTEM_REF_ID,
x_chrv_rec.APPLICATION_ID,
x_chrv_rec.ORIG_SYSTEM_SOURCE_CODE,
x_chrv_rec.ORIG_SYSTEM_ID1,
x_chrv_rec.ORIG_SYSTEM_REFERENCE1,
x_chrv_rec.ATTRIBUTE_CATEGORY,
x_chrv_rec.ATTRIBUTE1,
x_chrv_rec.ATTRIBUTE2,
x_chrv_rec.ATTRIBUTE3,
x_chrv_rec.ATTRIBUTE4,
x_chrv_rec.ATTRIBUTE5,
x_chrv_rec.ATTRIBUTE6,
x_chrv_rec.ATTRIBUTE7,
x_chrv_rec.ATTRIBUTE8,
x_chrv_rec.ATTRIBUTE9,
x_chrv_rec.ATTRIBUTE10,
x_chrv_rec.ATTRIBUTE11,
x_chrv_rec.ATTRIBUTE12,
x_chrv_rec.ATTRIBUTE13,
x_chrv_rec.ATTRIBUTE14,
x_chrv_rec.ATTRIBUTE15,
x_chrv_rec.CREATED_BY,
x_chrv_rec.CREATION_DATE,
x_chrv_rec.LAST_UPDATED_BY,
x_chrv_rec.LAST_UPDATE_DATE,
x_chrv_rec.LAST_UPDATE_LOGIN;
SELECT
ID,
OBJECT_VERSION_NUMBER,
ISG_ID,
KHR_ID,
PDT_ID,
AMD_CODE,
DATE_FIRST_ACTIVITY,
GENERATE_ACCRUAL_YN,
GENERATE_ACCRUAL_OVERRIDE_YN,
DATE_REFINANCED,
CREDIT_ACT_YN,
TERM_DURATION,
CONVERTED_ACCOUNT_YN,
DATE_CONVERSION_EFFECTIVE,
SYNDICATABLE_YN,
SALESTYPE_YN,
DATE_DEAL_TRANSFERRED,
DATETIME_PROPOSAL_EFFECTIVE,
DATETIME_PROPOSAL_INEFFECTIVE,
DATE_PROPOSAL_ACCEPTED,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
PRE_TAX_YIELD,
AFTER_TAX_YIELD,
IMPLICIT_INTEREST_RATE,
IMPLICIT_NON_IDC_INTEREST_RATE,
TARGET_PRE_TAX_YIELD,
TARGET_AFTER_TAX_YIELD,
TARGET_IMPLICIT_INTEREST_RATE,
TARGET_IMPLICIT_NONIDC_INTRATE,
DATE_LAST_INTERIM_INTEREST_CAL,
DEAL_TYPE,
PRE_TAX_IRR,
AFTER_TAX_IRR,
EXPECTED_DELIVERY_DATE,
ACCEPTED_DATE,
PREFUNDING_ELIGIBLE_YN,
REVOLVING_CREDIT_YN
FROM OKL_K_HEADERS_V
WHERE OKL_K_HEADERS_V.id = p_id;
l_khrv_rec.LAST_UPDATED_BY,
l_khrv_rec.LAST_UPDATE_DATE,
l_khrv_rec.LAST_UPDATE_LOGIN,
l_khrv_rec.PRE_TAX_YIELD,
l_khrv_rec.AFTER_TAX_YIELD,
l_khrv_rec.IMPLICIT_INTEREST_RATE,
l_khrv_rec.IMPLICIT_NON_IDC_INTEREST_RATE,
l_khrv_rec.TARGET_PRE_TAX_YIELD,
l_khrv_rec.TARGET_AFTER_TAX_YIELD,
l_khrv_rec.TARGET_IMPLICIT_INTEREST_RATE,
l_khrv_rec.TARGET_IMPLICIT_NONIDC_INTRATE,
l_khrv_rec.DATE_LAST_INTERIM_INTEREST_CAL,
l_khrv_rec.DEAL_TYPE,
l_khrv_rec.PRE_TAX_IRR,
l_khrv_rec.AFTER_TAX_IRR,
l_khrv_rec.EXPECTED_DELIVERY_DATE,
l_khrv_rec.ACCEPTED_DATE,
l_khrv_rec.PREFUNDING_ELIGIBLE_YN,
l_khrv_rec.REVOLVING_CREDIT_YN
;
SELECT contract_number
FROM okc_k_headers_v
WHERE id = p_chr_id;
SELECT okl_rbk_seq.nextval
INTO l_seq_no
FROM DUAL;
okl_contract_pub.update_contract_header(
p_api_version => 1.0,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_restricted_update => OKC_API.G_FALSE,
p_chrv_rec => l_chrv_rec,
p_khrv_rec => l_khrv_rec,
x_chrv_rec => x_chrv_rec,
x_khrv_rec => x_khrv_rec
);
PROCEDURE update_hdr_info(
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_working_copy_chr_id IN OKC_K_HEADERS_V.ID%TYPE,
p_start_date IN OKL_K_HEADERS_FULL_V.START_DATE%TYPE,
p_end_date IN OKL_K_HEADERS_FULL_V.END_DATE%TYPE,
p_term_duration IN OKL_K_HEADERS_FULL_V.TERM_DURATION%TYPE
)
IS
l_khrv_rec khrv_rec_type;
update_failed EXCEPTION;
RAISE update_failed;
RAISE update_failed;
okl_contract_pub.update_contract_header(
p_api_version => 1.0,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_restricted_update => OKC_API.G_FALSE,
p_chrv_rec => l_chrv_rec,
p_khrv_rec => l_khrv_rec,
x_chrv_rec => x_chrv_rec,
x_khrv_rec => x_khrv_rec
);
RAISE update_failed;
WHEN update_failed THEN
x_return_status := OKC_API.G_RET_STS_ERROR;
END update_hdr_info;
PROCEDURE update_residual_value(
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_chr_id IN NUMBER,
p_reduce_residual_ptg_by IN NUMBER
) IS
l_api_name VARCHAR2(35) := 'update_residual_value';
SELECT residual_value
FROM okl_k_lines_full_v
WHERE id = p_top_line_id;
SELECT line.id
FROM okl_k_lines_full_v line,
okc_line_styles_v style
WHERE dnz_chr_id = c_chr_id
AND style.lty_code = 'FREE_FORM1'
AND line.lse_id = style.id;
okl_contract_pub.update_contract_line(
p_api_version => 1.0,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_clev_tbl => l_clev_tbl,
p_klev_tbl => l_klev_tbl,
x_clev_tbl => x_clev_tbl,
x_klev_tbl => x_klev_tbl
);
END update_residual_value;
SELECT OBJECT_VERSION_NUMBER, REQUEST_STATUS_CODE
FROM OKL_TRX_REQUESTS
WHERE ID = p_req_id;
-- Call the public API for insertion here.
okl_trx_requests_pub.insert_trx_requests(
p_api_version => p_api_version,
p_init_msg_list =>p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_trqv_tbl => p_trqv_tbl,
x_trqv_tbl => x_trqv_tbl);
--Call update here.
okl_trx_requests_pub.update_trx_requests(
p_api_version => p_api_version,
p_init_msg_list =>p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_trqv_tbl => l_upd_trqv_tbl,
x_trqv_tbl => x_trqv_tbl);
-- Update the Hdr info
update_hdr_info( x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_working_copy_chr_id => p_chr_id,
p_start_date => l_current_lease_attribs(1).start_date,
p_end_date => p_trqv_tbl(1).end_date,
p_term_duration => l_new_term );
--Update the Residual Values
update_residual_value(
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_chr_id => p_chr_id,
p_reduce_residual_ptg_by => l_reduce_residual_ptg_by
);
--Delete the values that are already in the rents table and
--repopulate the values.
l_rents_tbl.delete;
--Now update the Status and the Transaction id in the CS Request Table.
IF l_request_status_code <> 'COMPLETE' THEN
l_trqv_tbl(1).request_status_code := 'PRICING';
okl_trx_requests_pub.update_trx_requests(
p_api_version => p_api_version,
p_init_msg_list =>p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_trqv_tbl => l_trqv_tbl,
x_trqv_tbl => x_trqv_tbl);
PROCEDURE update_lrnw_request(
p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,p_trqv_rec IN okl_trx_requests_pub.trqv_rec_type
,x_trqv_rec OUT NOCOPY okl_trx_requests_pub.trqv_rec_type)
AS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_LRNW_REQUEST';
SELECT object_Version_number
FROM okl_trx_requests
WHERE id=a_id;
-- the update API.
--The status should be present in the FND_LOOKUP.
IF (l_trqv_rec.request_status_code = 'PROCESS_COMPLETE' ) THEN
l_trqv_rec.request_status_code := 'COMPLETE';
okl_trx_requests_pub.update_trx_requests(
p_api_version => p_api_version,
p_init_msg_list =>p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_trqv_rec => l_trqv_rec,
x_trqv_rec => x_trqv_rec);
END update_lrnw_request;