[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_FORM_RECORD_DELETED CONSTANT VARCHAR2(200) := OKL_API.G_FORM_RECORD_DELETED;
G_RECORD_LOGICALLY_DELETED CONSTANT VARCHAR2(200) := OKL_API.G_RECORD_LOGICALLY_DELETED;
SELECT '!'
FROM dual
where exists (SELECT 1
FROM OKC_LINE_STYLES_B lse,
OKC_K_LINES_B cle,
okc_statuses_b sts
WHERE lse.id = cle.lse_id
AND lse.lty_code = 'USAGE'
AND cle.dnz_chr_id = p_chr_id
AND sts.code =cle.sts_code
AND sts.ste_code NOT IN ('EXPIRED','HOLD','CANCELLED','TERMINATED')
);
SELECT tsu_code,
complete_transfer_yn,
date_transaction_occurred
FROM okl_trx_contracts
where id = p_source_trx_id;
--Purpose : Update/Delete usage line on Release contract
--
--
-- - used internally
--Modification History :
--09-Jan-2008 rirawat Created : Bug 6657564
------------------------------------------------------------------------------
PROCEDURE adjust_usage_lines(p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_chr_id IN NUMBER,
p_release_date IN DATE) IS
l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
SELECT cle.id usage_line_id
FROM okc_line_styles_b lse,
okc_k_lines_b cle,
okc_statuses_v okcsts
WHERE cle.dnz_chr_id = p_chr_id
AND cle.lse_id = lse.id
AND lse.lse_parent_id is null
AND lse.lty_code ='USAGE'
and okcsts.code = cle.sts_code
AND okcsts.ste_code NOT IN ('EXPIRED','HOLD','CANCELLED','TERMINATED')
AND NOT EXISTS (
SELECT 1
FROM okc_k_lines_b line,
okc_line_styles_b lse,
okc_statuses_b sts
WHERE line.dnz_chr_id = p_chr_id
AND line.cle_id = cle.id
AND line.lse_id = lse.id
AND lse.lty_code ='LINK_USAGE_ASSET'
AND sts.code = line.sts_code
AND sts.ste_code NOT IN ('EXPIRED','HOLD','CANCELLED','TERMINATED')
);
SELECT cle.id usage_line_id
FROM okc_line_styles_b lse,
okc_k_lines_b cle,okc_statuses_v okcsts
WHERE cle.dnz_chr_id = p_chr_id
AND cle.lse_id = lse.id
AND lse.lse_parent_id is null
AND lse.lty_code ='USAGE'
and okcsts.code = cle.sts_code
AND okcsts.ste_code NOT IN ('EXPIRED','HOLD','CANCELLED','TERMINATED');
SELECT line.id
FROM okc_k_lines_b line,
okc_line_styles_b lse,
okc_statuses_b okcsts
WHERE line.dnz_chr_id = p_chr_id
AND line.cle_id = p_usage_line_id
AND line.lse_id = lse.id
AND lse.lty_code ='LINK_USAGE_ASSET'
AND okcsts.code = line.sts_code
AND okcsts.ste_code NOT IN ('EXPIRED','HOLD','CANCELLED','TERMINATED');
OKL_CONTRACT_PUB.delete_contract_line(
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_line_id => non_linked_usage_rec.usage_line_id
);
--update the start date of the usage and linked asset lines with the
-- release date
FOR usage_rec IN usage_csr (p_chr_id)
LOOP
lp_clev_rec := lp_clev_temp_rec;
OKL_CONTRACT_PUB.update_contract_line(
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_clev_rec => lp_clev_rec,
p_klev_rec => lp_klev_rec,
x_clev_rec => lx_clev_rec,
x_klev_rec => lx_klev_rec
);
OKL_CONTRACT_PUB.update_contract_line(
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_clev_rec => lp_clev_rec,
p_klev_rec => lp_klev_rec,
x_clev_rec => lx_clev_rec,
x_klev_rec => lx_klev_rec
);
SELECT ID,
OBJECT_VERSION_NUMBER,
ICA_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
TAS_TYPE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
TSU_CODE,
TRY_ID,
DATE_TRANS_OCCURRED,
TRANS_NUMBER,
COMMENTS,
REQ_ASSET_ID,
TOTAL_MATCH_AMOUNT
FROM OKL_TRX_ASSETS
WHERE id = p_tas_id;
x_trxv_rec.LAST_UPDATED_BY,
x_trxv_rec.LAST_UPDATE_DATE,
x_trxv_rec.LAST_UPDATE_LOGIN,
x_trxv_rec.TSU_CODE,
x_trxv_rec.TRY_ID,
x_trxv_rec.DATE_TRANS_OCCURRED,
x_trxv_rec.TRANS_NUMBER,
x_trxv_rec.COMMENTS,
x_trxv_rec.REQ_ASSET_ID,
x_trxv_rec.TOTAL_MATCH_AMOUNT;
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 CIM.ID,
CIM.OBJECT_VERSION_NUMBER,
CIM.CLE_ID,
CIM.CHR_ID,
CIM.CLE_ID_FOR,
CIM.DNZ_CHR_ID,
CIM.OBJECT1_ID1,
CIM.OBJECT1_ID2,
CIM.JTOT_OBJECT1_CODE,
CIM.UOM_CODE,
CIM.EXCEPTION_YN,
CIM.NUMBER_OF_ITEMS,
CIM.UPG_ORIG_SYSTEM_REF,
CIM.UPG_ORIG_SYSTEM_REF_ID,
CIM.PRICED_ITEM_YN,
CIM.CREATED_BY,
CIM.CREATION_DATE,
CIM.LAST_UPDATED_BY,
CIM.LAST_UPDATE_DATE,
CIM.LAST_UPDATE_LOGIN
FROM okc_k_items_v cim
WHERE cim.dnz_chr_id = p_dnz_chr_id
AND cim.cle_id = p_cle_id;
x_cimv_rec.LAST_UPDATED_BY,
x_cimv_rec.LAST_UPDATE_DATE,
x_cimv_rec.LAST_UPDATE_LOGIN;
select period_name,
calendar_period_open_date,
calendar_period_close_date
from fa_deprn_periods
where book_type_code = p_book_type_code
and period_close_date is null;
PROCEDURE l_update_contract_header(p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_restricted_update IN VARCHAR2 DEFAULT 'F',
p_chrv_rec IN chrv_rec_type,
p_khrv_rec IN khrv_rec_type,
x_chrv_rec OUT NOCOPY chrv_rec_type,
x_khrv_rec OUT NOCOPY khrv_rec_type) IS
l_api_name CONSTANT VARCHAR2(30) := 'L_UPDATE_CONTRACT_HEADER';
SELECT cle.id
From okc_k_lines_b cle,
okc_statuses_b sts
where cle.dnz_chr_id = cle.chr_id
and cle.chr_id = p_chr_id
and sts.code = cle.sts_code;
SELECT cle.id,
cle.start_date,
cle.end_date
From okc_k_lines_b cle
connect by prior cle.id = cle.cle_id
start with cle.id = p_cle_id
and exists (select 1
from okc_statuses_b sts
where sts.code = cle.sts_code);
okl_okc_migration_pvt.update_contract_line(
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_clev_rec => l_clev_rec,
x_clev_rec => lx_clev_rec);
OKL_OKC_MIGRATION_PVT.update_contract_header(
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_restricted_update => p_restricted_update,
p_chrv_rec => p_chrv_rec,
x_chrv_rec => x_chrv_rec);
OKL_KHR_PVT.Update_Row(
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_khrv_rec => p_khrv_rec,
x_khrv_rec => x_khrv_rec);
END l_update_contract_header;
SELECT cle.id top_line
FROM okc_subclass_top_line stl,
okc_line_styles_b lse,
okc_k_lines_b cle
WHERE cle.dnz_chr_id = p_dnz_chr_id
AND cle.lse_id = lse.id
AND lse.lty_code = G_FIN_LINE_LTY_CODE
AND lse.lse_parent_id is null
AND lse.lse_type = G_TLS_TYPE
AND lse.id = stl.lse_Id
AND stl.scs_code = 'LEASE';
select rul.id rul_id,
rgp.id rgp_id,
rul.rule_information1
from okc_rule_groups_b rgp,
okc_rules_b rul
where rgp.id = rul.rgp_id
and rgp.rgd_code = 'LARLES'
and rul.rule_information_category = 'LARLES'
and rgp.dnz_chr_id = p_chr_id
and rgp.chr_id = p_chr_id
and rul.dnz_chr_id = p_chr_id;
l_update_contract_header(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_restricted_update => OKL_API.G_FALSE,
p_chrv_rec => l_chrv_rec,
p_khrv_rec => l_khrv_rec,
x_chrv_rec => x_new_chrv_rec,
x_khrv_rec => x_new_khrv_rec);
OKL_RULE_PUB.update_rule(
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_rulv_rec => lp_larles_rulv_rec,
x_rulv_rec => lx_larles_rulv_rec);
SELECT cle.id top_line
FROM okc_subclass_top_line stl,
okc_line_styles_b lse,
okc_k_lines_b cle
WHERE cle.dnz_chr_id = p_dnz_chr_id
AND cle.lse_id = lse.id
AND lse.lty_code = G_FIN_LINE_LTY_CODE
AND lse.lse_parent_id is null
AND lse.lse_type = G_TLS_TYPE
AND lse.id = stl.lse_Id
AND stl.scs_code = 'LEASE';
SELECT av.asset_number asset_number
FROM okc_subclass_top_line stl,
okc_line_styles_b lse2,
okc_line_styles_b lse1,
okx_assets_v av,
okc_k_items cim,
okc_k_lines_b cle
WHERE cle.dnz_chr_id = p_dnz_chr_id
AND cle.id = cim.cle_id
AND cim.dnz_chr_id = cle.dnz_chr_id
AND cim.object1_id1 = av.id1
AND cim.object1_id2 = av.id2
AND cle.lse_id = lse1.id
AND lse1.lty_code = G_FA_LINE_LTY_CODE
AND lse1.lse_type = G_SLS_TYPE
AND lse1.lse_parent_id = lse2.id
AND lse2.lty_code = G_FIN_LINE_LTY_CODE
AND lse2.id = stl.lse_Id
AND stl.scs_code = 'LEASE';
SELECT 1
FROM DUAL
WHERE EXISTS (SELECT '1'
FROM OKL_ASSET_RETURNS_B
WHERE kle_id = p_cle_id
AND ars_code = 'RE_LEASE');
SELECT 1
FROM dual
WHERE EXISTS (SELECT '1'
FROM okx_asset_lines_v
WHERE asset_number = p_asset_number
AND line_status not in ('EXPIRED','TERMINATED','ABANDONED'));
SELECT currency_code,
authoring_org_id
FROM okc_k_headers_b
WHERE id = p_chr_id;
SELECT id
FROM okl_trx_types_tl
WHERE language = 'US'
AND name = p_trx_type_name;
SELECT contract_number
FROM OKC_K_HEADERS_B
WHERE id = p_ctr_id1;
SELECT party_rel_id2_new,
trx_number
FROM okl_trx_contracts
WHERE id = p_taa_trx_id;
SELECT bill_to_site_id,
cust_acct_id,
bank_acct_id,
invoice_format_id,
payment_mthd_id,
mla_id,
credit_line_id,
--Bug# 4191851
insurance_yn,
lease_policy_yn,
ipy_type,
policy_number,
covered_amt,
deductible_amt,
effective_to_date,
effective_from_date,
proof_provided_date,
proof_required_date,
lessor_insured_yn,
lessor_payee_yn,
int_id,
isu_id,
agency_site_id,
agent_site_id,
territory_code
FROM okl_taa_request_details_b
WHERE tcn_id = p_taa_trx_id;
SELECT cle.id,
cle.name,
tcl.source_column_1,
tcl.source_value_1,
tcl.source_column_2,
tcl.source_value_2,
tcl.source_column_3,
tcl.source_value_3
FROM okc_k_lines_v cle,
okc_line_styles_b lse,
okl_txl_cntrct_lns tcl
WHERE cle.chr_id = p_chr_id
AND cle.dnz_chr_id = p_chr_id
AND lse.lty_code = 'FREE_FORM1'
AND cle.lse_id = lse.id
AND tcl.tcn_id = p_taa_trx_id
AND tcl.kle_id = cle.id
AND tcl.before_transfer_yn = 'N';
SELECT 'Y'
FROM hz_parties prt
WHERE prt.party_id = p_party_id
AND prt.party_type IN ('PERSON','ORGANIZATION');
SELECT 'Y'
FROM hz_cust_accounts cas
WHERE cas.party_id = p_party_id
AND cas.cust_account_id = p_cust_acc_id;
SELECT 'Y'
FROM okx_cust_site_uses_v site_use,
hz_cust_acct_sites_all site,
okc_k_headers_b chr
WHERE chr.id = p_chr_id
AND site.cust_acct_site_id = site_use.cust_acct_site_id
AND site_use.party_id = p_party_id
AND site_use.id1 = p_bill_to_site_id
AND site_use.cust_account_id = p_cust_acc_id
AND site_use.site_use_code = 'BILL_TO'
AND site_use.b_status = 'A'
AND site.status = 'A'
AND site_use.org_id = chr.authoring_org_id
AND site.org_id = chr.authoring_org_id;
SELECT 'Y'
FROM okx_rcpt_method_accounts_v rma,
okc_k_headers_b chr
WHERE chr.id = p_chr_id
AND rma.id1 = p_bank_acc_id
AND rma.customer_id = p_cust_acc_id
--Commented and added by jjuneja for Bug #14211467
--AND rma.org_id = chr.authoring_org_id
AND NVL(rma.org_id,chr.authoring_org_id) = chr.authoring_org_id
--End of changes by jjuneja
AND TRUNC(SYSDATE) between NVL(rma.start_date_active, TRUNC(SYSDATE))
AND NVL(rma.end_date_active,TRUNC(SYSDATE));
SELECT 'Y'
FROM okx_receipt_methods_v
WHERE id1 = p_payment_mthd_id
AND customer_id = p_cust_acc_id
--Bug 8325912 AND site_use_id = p_bill_to_site_id
AND TRUNC(SYSDATE) between NVL(start_date_active, TRUNC(SYSDATE))
AND NVL(end_date_active,TRUNC(SYSDATE));
SELECT 'Y'
FROM okl_invoice_formats_v
WHERE id = p_invoice_format_id
AND TRUNC(SYSDATE) between NVL(start_date, TRUNC(SYSDATE))
AND NVL(end_date,TRUNC(SYSDATE));
SELECT 'Y'
FROM okc_k_headers_b chr
WHERE chr.id = p_mla_id
AND chr.scs_code = 'MASTER_LEASE'
AND chr.sts_code = 'ACTIVE'
AND chr.template_yn = 'N'
AND chr.buy_or_sell = 'S';
SELECT 'Y'
FROM okl_k_hdr_crdtln_uv crd,
okc_k_headers_b chr
WHERE crd.id = p_credit_line_id
AND chr.id = crd.id
AND crd.end_date >= p_release_date
AND crd.cust_object1_id1 = p_cust_id
AND crd.currency_code = p_currency_code
AND chr.cust_acct_id = p_cust_acct_id;
SELECT 'Y'
FROM okx_party_site_uses_v
WHERE id1 = p_install_site_id
AND site_use_type = 'INSTALL_AT'
AND party_id = p_cust_id
AND status = 'A';
SELECT 'Y'
FROM okx_ast_locs_v
WHERE id1= p_location_id
AND NVL(enabled_flag,'Y') = 'Y'
AND TRUNC(SYSDATE) BETWEEN NVL(start_date_active, TRUNC(SYSDATE))
AND NVL(end_date_active, TRUNC(SYSDATE));
SELECT chr.start_date,
chr.end_date,
chr.contract_number,
khr.deal_type,
sts_code,
chr.cust_acct_id,
--Bug# 4173345
chr.orig_system_source_code,
--Bug# 4631549
chr.currency_code
FROM okc_k_headers_b chr,
okl_k_headers khr
WHERE chr.id = p_chr_id
AND chr.id = khr.id;
SELECT 'Y'
FROM okl_trx_contracts tcn
WHERE tcn.khr_id = p_chr_id
AND tcn.tsu_code IN ('ENTERED','WORKING','WAITING','SUBMITTED')
--rkuttiya added for 12.1.1 Multi GAAP
AND tcn.representation_type = 'PRIMARY'
--
AND (tcn.tcn_type IN ('TRBK','RVS','SPLC'));
SELECT tsu_code,
complete_transfer_yn,
trx_number
FROM okl_trx_contracts
where id = p_source_trx_id;
SELECT fin_ast_cle.id,
fin_ast_cle.name
FROM okc_k_lines_v fin_ast_cle,
okc_k_headers_b chr,
okc_line_styles_b fin_ast_lse,
okl_trx_quotes_b qte,
okl_txl_quote_lines_b tql
WHERE chr.id = p_orig_chr_id
AND fin_ast_cle.chr_id = chr.id
AND fin_ast_cle.dnz_chr_id = chr.id
AND fin_ast_cle.sts_code = chr.sts_code
AND fin_ast_cle.lse_id = fin_ast_lse.id
AND fin_ast_lse.lty_code = 'FREE_FORM1'
AND tql.kle_id = fin_ast_cle.id
AND tql.qte_id = qte.id
AND tql.qlt_code = 'AMCFIA'
AND NVL(qte.accepted_yn,'N') = 'Y'
AND NVL(qte.consolidated_yn,'N') = 'N';
SELECT fin_ast_cle.id,
fin_ast_cle.name
FROM okl_txl_cntrct_lns tcl,
okc_k_lines_v fin_ast_cle,
okl_trx_quotes_b qte,
okl_txl_quote_lines_b tql
WHERE tcl.tcn_id = p_source_trx_id
AND tcl.before_transfer_yn = 'N'
AND fin_ast_cle.chr_id = p_orig_chr_id
AND fin_ast_cle.dnz_chr_id = p_orig_chr_id
AND fin_ast_cle.id = tcl.kle_id
AND tql.kle_id = fin_ast_cle.id
AND tql.qte_id = qte.id
AND tql.qlt_code = 'AMCFIA'
AND NVL(qte.accepted_yn,'N') = 'Y'
AND NVL(qte.consolidated_yn,'N') = 'N';
SELECT fin_ast_cle.id,
fin_ast_cle.name
FROM okc_k_lines_v fin_ast_cle,
okc_k_headers_b chr,
okc_line_styles_b fin_ast_lse,
okl_trx_assets ota,
okl_txl_assets_b otl
WHERE chr.id = p_orig_chr_id
AND fin_ast_cle.chr_id = chr.id
AND fin_ast_cle.dnz_chr_id = chr.id
AND fin_ast_cle.sts_code = chr.sts_code
AND fin_ast_cle.lse_id = fin_ast_lse.id
AND fin_ast_lse.lty_code = 'FREE_FORM1'
AND otl.kle_id = fin_ast_cle.id
AND ota.tas_type IN ('LLT','LLP','NLP')
AND ota.id = otl.tas_id
AND otl.tal_type = 'OAS'
AND ota.tsu_code = 'PROCESSED';
SELECT fin_ast_cle.id,
fin_ast_cle.name
FROM okl_txl_cntrct_lns tcl,
okc_k_lines_v fin_ast_cle,
okl_trx_assets ota,
okl_txl_assets_b otl
WHERE tcl.tcn_id = p_source_trx_id
AND tcl.before_transfer_yn = 'N'
AND fin_ast_cle.chr_id = p_orig_chr_id
AND fin_ast_cle.dnz_chr_id = p_orig_chr_id
AND fin_ast_cle.id = tcl.kle_id
AND otl.kle_id = fin_ast_cle.id
AND ota.tas_type IN ('LLT','LLP','NLP')
AND ota.id = otl.tas_id
AND otl.tal_type = 'OAS'
AND ota.tsu_code = 'PROCESSED';
SELECT 'Y'
FROM okc_k_rel_objs_v
WHERE chr_id = p_chr_id
AND jtot_object1_code = 'OKL_SERVICE'
-- AND rty_code IN ('OKLUBB','OKLSRV')
AND rty_code = 'OKLSRV';
SELECT max(receipt_date)
FROM okl_ext_csh_rcpts_b
WHERE customer_number = p_cust_acct_number;*/
SELECT max(date_accepted)
FROM okl_trx_quotes_b
WHERE khr_id = p_contract_id
AND QST_CODE = 'ACCEPTED';
SELECT max(date_invoiced)
FROM okl_trx_ar_invoices_b tar,
okl_trx_types_b typ
WHERE tar.khr_id = p_contract_id
AND tar.amount<0
AND tar.try_id = typ.id
AND typ.aep_code = 'CREDIT_MEMO';
Select kle.expected_Asset_Cost,
cleb.orig_system_id1 orig_cle_id,
chrb.id new_chr_id,
cleb.id new_cle_id
From
okc_k_lines_b cleb,
okl_k_lines kle,
okc_k_headers_b chrb
where kle.id = cleb.id
and cleb.dnz_chr_id = chrb.id
and cleb.lse_id = 33 --financial asset line
and cleb.sts_code = 'APPROVED'
and cleb.orig_system_id1 is NOT NULL
and chrb.orig_system_id1 = p_chr_id
and chrb.orig_system_source_code = 'OKL_RELEASE'
and chrb.sts_code = 'APPROVED';
select fb.asset_id,
fb.book_type_code
from fa_books fb,
fa_book_controls fbc,
okc_k_items cim,
okc_k_lines_b cleb_fa
where fb.asset_id = cim.object1_id1
and fb.book_type_code = fbc.book_type_code
and fbc.book_class = 'CORPORATE'
and fb.transaction_header_id_out is NULL
and cim.jtot_object1_code = 'OKX_ASSET'
and cim.object1_id2 = '#'
and cim.dnz_chr_id = cleb_fa.dnz_chr_id
and cim.cle_id = cleb_fa.id
and cleb_fa.cle_id = p_cle_id
and cleb_fa.dnz_chr_id = p_chr_id
and cleb_fa.lse_id = 42; -- fixed asset
select cleb_model.id model_cle_id,
cim_model.number_of_items
from okc_k_lines_b cleb_model,
okc_k_items cim_model
where cim_model.cle_id = cleb_model.id
and cim_model.dnz_chr_id = p_chr_id
and cleb_model.cle_id = p_cle_id
and cleb_model.dnz_chr_id = p_chr_id
and cleb_model.lse_id = 34; --model line
select asset_number
from fa_additions_b
where asset_id = p_Asset_id;
select currency_code
from okc_k_headers_b
where id = p_chr_id;
select '!' from dual
where exists (
select 1
from okc_k_items UITEM ,
OKC_K_LINES_B USAGE,
OKC_K_LINES_B USUB_LINE,
okc_k_items LINK_ITEM,
okc_k_lines_b top_line,
okl_txl_cntrct_lns tcl
where UITEM.dnz_chr_id=tcl.khr_id
and UITEM.JTOT_OBJECT1_CODE = 'OKL_USAGE'
and UITEM.OBJECT1_ID2='#'
and USAGE.id=UITEM.cle_id
and USUB_LINE.cle_id=USAGE.id
and USUB_LINE.lse_id=(select id from okc_line_styles_v where lty_code = 'LINK_USAGE_ASSET')
and LINK_ITEM.cle_id=USUB_LINE.id
and LINK_ITEM.OBJECT1_ID1=top_line.id
and LINK_ITEM.OBJECT1_ID2='#'
and top_line.lse_id=(select id from okc_line_styles_v where lty_code = 'FREE_FORM1')
and top_line.id = tcl.kle_id
and tcl.tcn_id = p_source_trx_id
AND tcl.before_transfer_yn = 'N'
);
SELECT oks.date_terminated,sts.ste_code
FROM okc_k_rel_objs_v krelobj ,
okc_k_headers_b oks,
okc_statuses_b sts
WHERE krelobj.JTOT_OBJECT1_CODE = 'OKL_SERVICE'
AND krelobj.RTY_CODE = 'OKLUBB'
and krelobj.OBJECT1_ID1 = oks.id
and krelobj.OBJECT1_ID2='#'
and krelobj.CHR_ID = p_orig_chr_id --original contract id
and sts.code=oks.sts_code;
SELECT INVOICE_DATE FROM( SELECT MAX(HD.DATE_CONSOLIDATED) INVOICE_DATE
FROM AR_PAYMENT_SCHEDULES_ALL PS,
OKL_CNSLD_AR_STRMS_B ST,
OKL_CNSLD_AR_LINES_B LN,
OKL_CNSLD_AR_HDRS_B HD,
OKC_K_HEADERS_B CN
WHERE PS.CLASS = 'INV'
AND ST.RECEIVABLES_INVOICE_ID = PS.CUSTOMER_TRX_ID
AND LN.ID = ST.LLN_ID
AND HD.ID = LN.CNR_ID
AND CN.ID = ST.KHR_ID
AND PS.AMOUNT_DUE_REMAINING < PS.AMOUNT_DUE_ORIGINAL
AND CN.ID = p_khr_id
AND HD.ORG_ID = CN.AUTHORING_ORG_ID
UNION
SELECT max(ractrx.trx_date) INVOICE_DATE
FROM ra_customer_trx_all ractrx,
ra_customer_trx_lines_all ractrl,
ar_payment_schedules_all ps,
okc_k_headers_b chr
WHERE chr.id = p_khr_id
AND chr.contract_number = ractrl.interface_line_attribute6
AND ractrx.customer_trx_id = ractrl.customer_trx_id
AND ractrl.line_type = 'LINE'
AND ractrl.interface_line_attribute1 IS NULL -- Assume 1 as cnsld inv
AND ractrl.amount_due_remaining < ractrl.amount_due_original
AND ps.customer_trx_id = ractrx.customer_trx_id
AND ps.class = 'INV')
WHERE INVOICE_DATE IS NOT NULL;
OKC_CONTRACT_PUB.update_contract_line
(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_clev_rec => l_model_clev_rec,
x_clev_rec => lx_model_clev_rec
);
OKL_CONTRACT_PUB.update_contract_line
(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_clev_rec => l_finast_clev_rec,
p_klev_rec => l_finast_klev_rec,
p_edit_mode => 'N',
x_clev_rec => lx_finast_clev_rec,
x_klev_rec => lx_finast_klev_rec);
OKL_CONTRACT_PUB.update_contract_line
(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_clev_rec => l_finast_clev_rec2,
p_klev_rec => l_finast_klev_rec2,
p_edit_mode => 'N',
x_clev_rec => lx_finast_clev_rec2,
x_klev_rec => lx_finast_klev_rec2);
p_msg_name => 'OKL_LA_UPDATE_ASSET');
SELECT chr.start_date,
chr.end_date,
chr.contract_number,
khr.deal_type,
sts_code,
--Bug# 5005869
chr.currency_code
FROM okc_k_headers_b chr,
okl_k_headers khr
WHERE chr.id = p_chr_id
AND chr.id = khr.id;
SELECT crl.rule_information1
FROM okc_rule_groups_b crg,
okc_rules_b crl
WHERE crl.rgp_id = crg.id
AND crg.rgd_code = p_rgd_code
AND crl.rule_information_category = p_rul_cat
AND crg.dnz_chr_id = p_chr_id;
SELECT 'Y'
FROM okc_k_lines_b cle,
okc_line_styles_b lse
WHERE cle.chr_id = p_chr_id
AND cle.dnz_chr_id = p_chr_id
AND lse.id = cle.lse_id
AND lse.lty_code = 'USAGE'
AND rownum = 1;
SELECT 'Y'
FROM okl_trx_contracts tcn
WHERE tcn.khr_id = p_chr_id
AND tcn.tsu_code IN ('ENTERED','WORKING','WAITING','SUBMITTED')
--rkuttiya added for 12.1.1 Multi GAAP
AND tcn.representation_type = 'PRIMARY'
AND (tcn.tcn_type IN ('TRBK','RVS','SPLC')
--
OR (tcn.tcn_type = 'MAE' AND tcn.try_id IN (SELECT try.id
FROM okl_trx_types_tl try
WHERE try.name = 'Release'
AND try.language= 'US')));
SELECT fin_ast_cle.id,
fin_ast_cle.name
FROM okc_k_lines_v fin_ast_cle,
okc_k_headers_b chr,
okc_line_styles_b fin_ast_lse
WHERE chr.id = p_orig_chr_id
AND fin_ast_cle.chr_id = chr.id
AND fin_ast_cle.dnz_chr_id = chr.id
AND fin_ast_cle.sts_code = chr.sts_code
AND fin_ast_cle.lse_id = fin_ast_lse.id
AND fin_ast_lse.lty_code = 'FREE_FORM1';
SELECT fin_ast_cle.id,
fin_ast_cle.name
FROM okl_txl_cntrct_lns tcl,
okc_k_lines_v fin_ast_cle
WHERE tcl.tcn_id = p_source_trx_id
AND tcl.before_transfer_yn = 'N'
AND fin_ast_cle.chr_id = p_orig_chr_id
AND fin_ast_cle.dnz_chr_id = p_orig_chr_id
AND fin_ast_cle.id = tcl.kle_id;
SELECT FND_DATE.canonical_to_date(sll.rule_information2) start_date,
DECODE(sll.rule_information7, NULL,
(ADD_MONTHS(FND_DATE.canonical_to_date(sll.rule_information2),
NVL(TO_NUMBER(sll.rule_information3),1) *
DECODE(sll.object1_id1, 'M',1,'Q',3,'S',6,'A',12)) - 1),
FND_DATE.canonical_to_date(sll.rule_information2) +
TO_NUMBER(sll.rule_information7) - 1) end_date
FROM okc_rules_b sll,
okc_rules_b slh,
okc_rule_groups_b rgp,
okl_strm_type_b sty
WHERE rgp.dnz_chr_id = p_chr_id
AND rgp.cle_id = p_cle_id
AND rgp.rgd_code = 'LALEVL'
AND slh.rgp_id = rgp.id
AND slh.rule_information_category = 'LASLH'
AND sll.object2_id1 = slh.id
AND sll.rule_information_category = 'LASLL'
AND sll.rgp_id = rgp.id
AND sty.id = TO_NUMBER(slh.object1_id1)
AND sty.stream_type_purpose = 'RENT';
SELECT FND_DATE.canonical_to_date(sll.rule_information2) start_date,
DECODE(sll.object1_id1, 'M', 1, 'Q', 3, 'S', 6, 'A', 12)factor,
TO_NUMBER(sll.rule_information3) periods,
DECODE(sll.rule_information7, NULL,
(ADD_MONTHS(FND_DATE.canonical_to_date(sll.rule_information2),
NVL(TO_NUMBER(sll.rule_information3),1) *
DECODE(sll.object1_id1, 'M',1,'Q',3,'S',6,'A',12)) - 1),
FND_DATE.canonical_to_date(sll.rule_information2) +
TO_NUMBER(sll.rule_information7) - 1) end_date,
sll.rule_information10 arrears_yn,
TO_NUMBER(sll.rule_information6) amount,
TO_NUMBER(sll.rule_information8) stub_amount,
sll.rule_information5,
sll.rgp_id,
sll.object1_id1,
sll.object1_id2,
sll.jtot_object1_code,
sll.object2_id1,
sll.object2_id2,
sll.jtot_object2_code,
sll.id sll_id,
sty.stream_type_purpose
FROM okc_rules_b sll,
okc_rules_b slh,
okc_rule_groups_b rgp,
okl_strm_type_b sty
WHERE rgp.dnz_chr_id = p_chr_id
AND rgp.rgd_code = 'LALEVL'
AND slh.rgp_id = rgp.id
AND slh.rule_information_category = 'LASLH'
AND slh.object1_id1 = sty.id
AND sll.object2_id1 = slh.id
AND sll.rule_information_category = 'LASLL'
AND sll.rgp_id = rgp.id
AND FND_DATE.canonical_to_date(sll.rule_information2) < p_release_date;
SELECT slh.id slh_id
FROM okc_rules_b slh
WHERE slh.dnz_chr_id = p_chr_id
AND slh.rule_information_category = 'LASLH'
AND NOT EXISTS (SELECT NULL FROM okc_rules_b sll
WHERE sll.object2_id1 = slh.id
AND sll.rule_information_category = 'LASLL'
AND sll.dnz_chr_id = slh.dnz_chr_id);
SELECT rgp.id rgp_id
FROM okc_rule_groups_b rgp
WHERE rgp.dnz_chr_id = p_chr_id
AND rgp.rgd_code = 'LALEVL'
AND NOT EXISTS (SELECT NULL FROM okc_rules_b slh
WHERE slh.rgp_id = rgp.id
AND slh.rule_information_category = 'LASLH');
okl_rule_pub.delete_rule(
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_rulv_rec => l_rulv_rec
);
OKL_RULE_PUB.update_rule(
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_rulv_rec => l_rulv_rec,
x_rulv_rec => lx_rulv_rec);
OKL_RULE_PUB.delete_rule(
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_rulv_rec => l_rulv_rec
);
OKL_RULE_PUB.update_rule(
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_rulv_rec => l_rulv_rec,
x_rulv_rec => lx_rulv_rec);
okl_rule_pub.delete_rule(
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_rulv_rec => l_rulv_rec
);
okl_rule_pub.delete_rule_group(
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_rgpv_rec => l_rgpv_rec
);
SELECT stm.id
FROM okl_streams stm
WHERE stm.khr_id = p_chr_id
AND stm.kle_id = p_cle_id
AND stm.sty_id = p_sty_id
AND stm.active_yn = 'Y'
AND stm.say_code = 'CURR';
SELECT stream_element_date,
amount
FROM okl_strm_elements_v sel
WHERE sel.stm_id = p_stm_id
AND stream_element_date < p_release_date
ORDER BY stream_element_date DESC;
SELECT cle.id,
cle.start_date,
cle.end_date,
kle.amount,
kle.initial_direct_cost,
kle.fee_type,
cle.chr_id,
cle.dnz_chr_id,
cle.cle_id,
sts.ste_code,
cle.orig_system_id1
FROM okc_k_lines_b cle,
okl_k_lines kle,
okc_line_styles_b lse,
okc_statuses_b sts
WHERE cle.dnz_chr_id = p_chr_id
AND cle.chr_id = p_chr_id
AND kle.id = cle.id
AND cle.lse_id = lse.id
AND lse.lty_code = 'FEE'
AND cle.sts_code = sts.code;
SELECT sts.ste_code
FROM okc_k_lines_b cle,
okc_statuses_b sts
WHERE cle.id = p_cle_id
AND cle.sts_code = sts.code;
SELECT DECODE(rul_lafreq.object1_id1, 'M', 1, 'Q', 3, 'S', 6, 'A', 12)factor,
TO_NUMBER(rul_lafexp.rule_information1) periods,
TO_NUMBER(rul_lafexp.rule_information2) amount,
rul_lafexp.id rul_lafexp_id,
rul_lafreq.id rul_lafreq_id,
rgp.id rgp_id
FROM okc_rules_b rul_lafexp,
okc_rules_b rul_lafreq,
okc_rule_groups_b rgp
WHERE rgp.dnz_chr_id = p_chr_id
AND rgp.cle_id = p_cle_id
AND rgp.rgd_code = 'LAFEXP'
AND rul_lafreq.rgp_id = rgp.id
AND rul_lafreq.rule_information_category = 'LAFREQ'
AND rul_lafexp.rgp_id = rgp.id
AND rul_lafexp.rule_information_category = 'LAFEXP';
SELECT cle.id,
NVL(kle.capital_amount,0) capital_amount,
NVL(kle.amount,0) amount,
cle.chr_id,
cle.dnz_chr_id,
cle.cle_id,
--avsingh
cle.orig_system_id1
FROM okc_k_lines_b cle,
okl_k_lines kle
WHERE cle.cle_id = p_cle_id
AND cle.dnz_chr_id = p_chr_id
AND cle.id = kle.id;
SELECT 'Y' pymt_exists,
rgp.id
FROM okc_rule_groups_b rgp
WHERE rgp.cle_id = p_cle_id
AND rgp.dnz_chr_id = p_chr_id
AND rgp.rgd_code = 'LALEVL';
SELECT fee_cim.id,
fee_cim.object1_id1
FROM okc_k_items fee_cim
WHERE fee_cim.cle_id = p_cle_id
AND fee_cim.dnz_chr_id = p_chr_id
AND fee_cim.jtot_object1_code = 'OKL_STRMTYP';
SELECT crl.object1_id1,
crl.rule_information3,
crl.rule_information6,
crl.rule_information8
FROM okc_rules_b crl
WHERE crl.rgp_id = p_rgp_id
AND crl.dnz_chr_id = p_chr_id
AND crl.rule_information_category = p_rul_cat;
SELECT stm.id
FROM okl_streams stm
WHERE stm.khr_id = p_chr_id
AND stm.kle_id = p_cle_id
AND stm.sty_id = p_sty_id
AND stm.active_yn = 'Y'
AND stm.say_code = 'CURR';
SELECT SUM(amount) amount
FROM okl_strm_elements_v sel
WHERE sel.stm_id = p_stm_id
AND stream_element_date >= p_release_date;
SELECT stream_element_date,
amount
FROM okl_strm_elements_v sel
WHERE sel.stm_id = p_stm_id
AND stream_element_date < p_release_date
ORDER BY stream_element_date DESC;
SELECT currency_code
FROM okc_k_headers_b
WHERE id = p_chr_id;
SELECT fee_type
FROM okl_k_lines
WHERE id = p_line_id;
OKL_CONTRACT_PUB.delete_contract_line(
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_line_id => contract_fee_rec.id
);
OKL_CONTRACT_PUB.delete_contract_line(
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_line_id => contract_fee_rec.id
);
OKL_CONTRACT_PUB.update_contract_line(
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_clev_rec => lp_clev_rec,
p_klev_rec => lp_klev_rec,
x_clev_rec => lx_clev_rec,
x_klev_rec => lx_klev_rec
);
OKL_CONTRACT_PUB.update_contract_line(
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_clev_rec => lp_clev_rec,
p_klev_rec => lp_klev_rec,
x_clev_rec => lx_clev_rec,
x_klev_rec => lx_klev_rec
);
OKL_CONTRACT_PUB.delete_contract_line(
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_line_id => contract_fee_rec.id
);
OKL_CONTRACT_PUB.update_contract_line(
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_clev_rec => lp_clev_rec,
p_klev_rec => lp_klev_rec,
x_clev_rec => lx_clev_rec,
x_klev_rec => lx_klev_rec
);
OKL_CONTRACT_PUB.update_contract_line(
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_clev_tbl => lp_sub_clev_tbl,
p_klev_tbl => lp_sub_klev_tbl,
x_clev_tbl => lx_sub_clev_tbl,
x_klev_tbl => lx_sub_klev_tbl
);
OKL_CONTRACT_PUB.delete_contract_line(
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_line_id => contract_fee_rec.id
);
OKL_CONTRACT_PUB.update_contract_line(
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_clev_tbl => lp_sub_clev_tbl,
p_klev_tbl => lp_sub_klev_tbl,
x_clev_tbl => lx_sub_clev_tbl,
x_klev_tbl => lx_sub_klev_tbl
);
OKL_CONTRACT_PUB.update_contract_line(
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_clev_rec => lp_clev_rec,
p_klev_rec => lp_klev_rec,
x_clev_rec => lx_clev_rec,
x_klev_rec => lx_klev_rec
);
OKL_CONTRACT_PUB.delete_contract_line(
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_line_id => contract_fee_rec.id
);
OKL_CONTRACT_PUB.update_contract_line(
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_clev_tbl => lp_sub_clev_tbl,
p_klev_tbl => lp_sub_klev_tbl,
x_clev_tbl => lx_sub_clev_tbl,
x_klev_tbl => lx_sub_klev_tbl
);
OKL_CONTRACT_PUB.update_contract_line(
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_clev_rec => lp_clev_rec,
p_klev_rec => lp_klev_rec,
x_clev_rec => lx_clev_rec,
x_klev_rec => lx_klev_rec
);
OKL_RULE_PUB.update_rule(
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_rulv_rec => l_rulv_rec,
x_rulv_rec => lx_rulv_rec);
OKL_CONTRACT_PUB.update_contract_line(
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_clev_rec => lp_clev_rec,
p_klev_rec => lp_klev_rec,
x_clev_rec => lx_clev_rec,
x_klev_rec => lx_klev_rec
);
OKL_CONTRACT_PUB.delete_contract_line(
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_line_id => contract_fee_rec.id
);
lp_sub_clev_tbl.DELETE;
lp_sub_klev_tbl.DELETE;
OKL_CONTRACT_PUB.update_contract_line(
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_clev_tbl => lp_sub_clev_tbl,
p_klev_tbl => lp_sub_klev_tbl,
x_clev_tbl => lx_sub_clev_tbl,
x_klev_tbl => lx_sub_klev_tbl
);
OKL_CONTRACT_PUB.update_contract_line(
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_clev_rec => lp_clev_rec,
p_klev_rec => lp_klev_rec,
x_clev_rec => lx_clev_rec,
x_klev_rec => lx_klev_rec
);
OKL_RULE_PUB.update_rule(
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_rulv_rec => l_rulv_rec,
x_rulv_rec => lx_rulv_rec);
OKL_CONTRACT_PUB.delete_contract_line(
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_line_id => contract_fee_rec.id
);
OKL_OKC_MIGRATION_PVT.update_contract_item
(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_cimv_rec => lp_cimv_rec,
x_cimv_rec => lx_cimv_rec);
lp_sub_clev_tbl.DELETE;
lp_sub_klev_tbl.DELETE;
OKL_CONTRACT_PUB.update_contract_line(
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_clev_tbl => lp_sub_clev_tbl,
p_klev_tbl => lp_sub_klev_tbl,
x_clev_tbl => lx_sub_clev_tbl,
x_klev_tbl => lx_sub_klev_tbl
);
OKL_RULE_PUB.update_rule(
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_rulv_rec => l_rulv_rec,
x_rulv_rec => lx_rulv_rec);
OKL_CONTRACT_PUB.update_contract_line(
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_clev_rec => lp_clev_rec,
p_klev_rec => lp_klev_rec,
x_clev_rec => lx_clev_rec,
x_klev_rec => lx_klev_rec
);
OKL_RULE_PUB.delete_rule_group(
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_rgpv_rec => l_rgpv_rec
);
SELECT cle.id,
cle.start_date,
cle.end_date,
kle.amount,
cle.chr_id,
cle.dnz_chr_id,
cle.cle_id,
cle.orig_system_id1,
sts.ste_code
FROM okc_k_lines_b cle,
okl_k_lines kle,
okc_line_styles_b lse,
okc_statuses_b sts
WHERE cle.dnz_chr_id = p_chr_id
AND cle.chr_id = p_chr_id
AND kle.id = cle.id
AND cle.lse_id = lse.id
AND lse.lty_code = 'SOLD_SERVICE'
AND cle.sts_code = sts.code;
SELECT sts.ste_code
FROM okc_k_lines_b cle,
okc_statuses_b sts
WHERE cle.id = p_cle_id
AND cle.sts_code = sts.code;
SELECT DECODE(rul_lafreq.object1_id1, 'M', 1, 'Q', 3, 'S', 6, 'A', 12)factor,
TO_NUMBER(rul_lafexp.rule_information1) periods,
TO_NUMBER(rul_lafexp.rule_information2) amount,
rul_lafexp.id rul_lafexp_id,
rul_lafreq.id rul_lafreq_id,
rgp.id rgp_id
FROM okc_rules_b rul_lafexp,
okc_rules_b rul_lafreq,
okc_rule_groups_b rgp
WHERE rgp.dnz_chr_id = p_chr_id
AND rgp.cle_id = p_cle_id
AND rgp.rgd_code = 'LAFEXP'
AND rul_lafreq.rgp_id = rgp.id
AND rul_lafreq.rule_information_category = 'LAFREQ'
AND rul_lafexp.rgp_id = rgp.id
AND rul_lafexp.rule_information_category = 'LAFEXP';
SELECT cle.id,
NVL(kle.capital_amount,0) capital_amount,
cle.chr_id,
cle.dnz_chr_id,
cle.cle_id
FROM okc_k_lines_b cle,
okl_k_lines kle
WHERE cle.cle_id = p_cle_id
AND cle.dnz_chr_id = p_chr_id
AND cle.id = kle.id;
SELECT currency_code
FROM okc_k_headers_b
WHERE id = p_chr_id;
SELECT 'Y' pymt_exists
FROM okc_rule_groups_b rgp
WHERE rgp.cle_id = p_cle_id
AND rgp.dnz_chr_id = p_chr_id
AND rgp.rgd_code = 'LALEVL';
OKL_CONTRACT_PUB.delete_contract_line(
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_line_id => contract_service_rec.id
);
OKL_RULE_PUB.update_rule(
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_rulv_rec => l_rulv_rec,
x_rulv_rec => lx_rulv_rec);
OKL_CONTRACT_PUB.update_contract_line(
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_clev_rec => lp_clev_rec,
p_klev_rec => lp_klev_rec,
x_clev_rec => lx_clev_rec,
x_klev_rec => lx_klev_rec
);
OKL_RULE_PUB.update_rule(
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_rulv_rec => l_rulv_rec,
x_rulv_rec => lx_rulv_rec);
OKL_RULE_PUB.delete_rule_group(
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_rgpv_rec => l_rgpv_rec
);
OKL_CONTRACT_PUB.update_contract_line(
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_clev_rec => lp_clev_rec,
p_klev_rec => lp_klev_rec,
x_clev_rec => lx_clev_rec,
x_klev_rec => lx_klev_rec
);
OKL_CONTRACT_PUB.update_contract_line(
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_clev_tbl => lp_sub_clev_tbl,
p_klev_tbl => lp_sub_klev_tbl,
x_clev_tbl => lx_sub_clev_tbl,
x_klev_tbl => lx_sub_klev_tbl
);
OKL_CONTRACT_PUB.update_contract_line(
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_clev_rec => lp_clev_rec,
p_klev_rec => lp_klev_rec,
x_clev_rec => lx_clev_rec,
x_klev_rec => lx_klev_rec
);
OKL_RULE_PUB.update_rule(
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_rulv_rec => l_rulv_rec,
x_rulv_rec => lx_rulv_rec);
OKL_CONTRACT_PUB.update_contract_line(
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_clev_rec => lp_clev_rec,
p_klev_rec => lp_klev_rec,
x_clev_rec => lx_clev_rec,
x_klev_rec => lx_klev_rec
);
OKL_CONTRACT_PUB.update_contract_line(
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_clev_rec => lp_clev_rec,
p_klev_rec => lp_klev_rec,
x_clev_rec => lx_clev_rec,
x_klev_rec => lx_klev_rec
);
OKL_RULE_PUB.delete_rule_group(
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_rgpv_rec => l_rgpv_rec
);
SELECT cle.id,
kle.amount
FROM okc_k_lines_b cle,
okc_line_styles_b lse,
okl_k_lines kle
WHERE cle.chr_id = p_chr_id
AND cle.dnz_chr_id = p_chr_id
AND lse.lty_code IN ('FEE','SOLD_SERVICE')
AND cle.lse_id = lse.id
AND kle.id = cle.id
AND NVL(kle.fee_type,'XXXX') <> 'CAPITALIZED';
SELECT SUM(NVL(kle.capital_amount,kle.amount)) sum_amount
FROM okc_k_lines_b cle,
okl_k_lines kle
WHERE cle.cle_id = p_cle_id
AND cle.dnz_chr_id = p_chr_id
AND kle.id = cle.id;
SELECT TO_NUMBER(sll.rule_information6) amount,
TO_NUMBER(sll.rule_information8) stub_amount,
sll.id sll_id
FROM okc_rules_b sll,
okc_rule_groups_b rgp
WHERE rgp.dnz_chr_id = p_chr_id
AND rgp.cle_id = p_cle_id
AND rgp.rgd_code = 'LALEVL'
AND sll.rule_information_category = 'LASLL'
AND sll.rgp_id = rgp.id;
SELECT currency_code
FROM okc_k_headers_b
WHERE id = p_chr_id;
OKL_RULE_PUB.update_rule(
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_rulv_rec => l_rulv_rec,
x_rulv_rec => lx_rulv_rec);
select khr.deal_type deal_type,
rulb.rule_information1 tax_owner
from okl_k_headers khr,
okc_rules_b rulb
where rulb.dnz_chr_id = p_chr_id
and rulb.rule_information_category = 'LATOWN'
and khr.id = p_chr_id;
SELECT cle.id,
cle.start_date,
sts.ste_code,
cle.orig_system_id1
FROM okc_k_lines_b cle,
okc_line_styles_b lse,
okc_statuses_b sts
WHERE cle.dnz_chr_id = p_chr_id
AND cle.chr_id = p_chr_id
AND cle.lse_id = lse.id
AND lse.lty_code = 'FREE_FORM1'
AND cle.sts_code = sts.code;
SELECT sts.ste_code,
--Bug# 4670760
cle.dnz_chr_id
FROM okc_k_lines_b cle,
okc_statuses_b sts
WHERE cle.id = p_cle_id
AND cle.sts_code = sts.code;
SELECT id,
dnz_chr_id,
chr_id,
cle_id,
lse_id,
start_date
FROM okc_k_lines_b
CONNECT BY PRIOR id = cle_id
START WITH id = p_cle_id;
SELECT lty_code
FROM okc_line_styles_b
WHERE id = p_lse_id;
SELECT name asset_number
FROM okc_k_lines_v
WHERE id = p_cle_id;
SELECT fab.asset_id,
fab.book_type_code
FROM fa_additions fad,
fa_book_controls fbc,
fa_books fab
WHERE fad.asset_number = p_asset_number
AND fab.asset_id = fad.asset_id
AND fab.book_type_code = fbc.book_type_code
AND fab.transaction_header_id_out IS NULL
AND fbc.book_class = 'CORPORATE';
SELECT txlb.depreciation_cost,
txlb.current_units,
txlb.salvage_value,
txlb.percent_salvage_value,
txlb.id
FROM okl_txl_assets_b txlb,
okl_trx_assets trx,
okc_k_lines_b fa_cleb,
okc_line_styles_b fa_lseb
WHERE txlb.kle_id = fa_cleb.id
AND txlb.tal_type = 'CRL'
AND trx.id = txlb.tas_id
AND trx.tsu_code <> 'PROCESSED'
AND trx.tas_type = 'CRL'
AND fa_cleb.cle_id = p_cle_id
AND fa_cleb.dnz_chr_id = p_chr_id
AND fa_cleb.lse_id = fa_lseb.id
AND fa_lseb.lty_code = 'FIXED_ASSET';
SELECT model_cim.id model_cim_id
FROM okc_k_items model_cim
WHERE model_cim.cle_id = p_model_cle_id
AND model_cim.dnz_chr_id = p_chr_id;
SELECT txdb.tax_book,
txdb.id
FROM okl_txd_assets_b txdb
WHERE txdb.tal_id = p_tal_id;
SELECT currency_code
FROM okc_k_headers_b
WHERE id = p_chr_id;
OKL_CONTRACT_PUB.delete_contract_line(
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_line_id => contract_fin_asset_rec.id
);
OKL_TXL_ASSETS_PUB.update_txl_asset_def(
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_tlpv_rec => l_talv_rec,
x_tlpv_rec => lx_talv_rec);
OKL_TXD_ASSETS_PUB.UPDATE_TXD_ASSET_DEF
(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_adpv_rec => l_txdv_rec,
x_adpv_rec => lx_txdv_rec);
OKL_CONTRACT_PUB.delete_contract_line(
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_line_id => cle_rec.id
);
OKL_CONTRACT_PUB.update_contract_line
(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_clev_rec => lp_clev_rec,
p_klev_rec => lp_klev_rec,
x_clev_rec => lx_clev_rec,
x_klev_rec => lx_klev_rec);
OKL_CONTRACT_PUB.update_contract_line
(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_clev_rec => lp_clev_rec,
p_klev_rec => lp_klev_rec,
x_clev_rec => lx_clev_rec,
x_klev_rec => lx_klev_rec);
OKL_OKC_MIGRATION_PVT.update_contract_item
(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_cimv_rec => lp_cimv_rec,
x_cimv_rec => lx_cimv_rec);
OKL_CONTRACT_PUB.update_contract_line(
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_clev_rec => lp_clev_rec,
p_klev_rec => lp_klev_rec,
x_clev_rec => lx_clev_rec,
x_klev_rec => lx_klev_rec
);
PROCEDURE update_taa_request_info(p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_chr_id IN NUMBER,
p_taa_trx_id IN NUMBER,
p_org_id IN NUMBER) IS
l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_TAA_REQUEST_INFO';
SELECT party_rel_id2_new
FROM okl_trx_contracts
WHERE id = p_taa_trx_id;
SELECT bill_to_site_id,
cust_acct_id,
bank_acct_id,
invoice_format_id,
payment_mthd_id,
mla_id,
credit_line_id,
--Bug# 4191851
insurance_yn,
lease_policy_yn,
ipy_type,
policy_number,
covered_amt,
deductible_amt,
effective_to_date,
effective_from_date,
proof_provided_date,
proof_required_date,
lessor_insured_yn,
lessor_payee_yn,
int_id,
isu_id,
agency_site_id,
agent_site_id,
territory_code
FROM okl_taa_request_details_b
WHERE tcn_id = p_taa_trx_id;
SELECT id
FROM okc_k_party_roles_b
WHERE chr_id = p_chr_id
AND rle_code = 'LESSEE';
select rul.id rul_id,
rgp.id rgp_id
from okc_rule_groups_b rgp,
okc_rules_b rul
where rgp.id = rul.rgp_id
and rgp.rgd_code = p_rgd_code
and rul.rule_information_category = p_rul_cat
and rgp.dnz_chr_id = p_chr_id
and rgp.chr_id = p_chr_id
and rul.dnz_chr_id = p_chr_id;
SELECT name
FROM okl_invoice_formats_v
WHERE ID = p_invoice_format_id;
SELECT id
FROM okc_governances gve
WHERE chr_id = p_chr_id
AND dnz_chr_id = p_chr_id
AND EXISTS (SELECT 1
FROM okc_k_headers_b chr
WHERE chr.id = gve.chr_id_referred
AND chr.scs_code = p_scs_code);
SELECT cle.id,
tcl.source_column_1,
tcl.source_value_1,
tcl.source_column_2,
tcl.source_value_2,
tcl.source_column_3,
tcl.source_value_3
FROM okc_k_lines_b cle,
okc_line_styles_b lse,
okl_txl_cntrct_lns tcl
WHERE cle.chr_id = p_chr_id
AND cle.dnz_chr_id = p_chr_id
AND lse.lty_code = 'FREE_FORM1'
AND cle.lse_id = lse.id
AND tcl.tcn_id = p_taa_trx_id
AND tcl.kle_id = cle.orig_system_id1
AND tcl.before_transfer_yn = 'N';
SELECT txlb.id
FROM okl_txl_assets_b txlb,
okl_trx_assets trx,
okc_k_lines_b fa_cleb,
okc_line_styles_b fa_lseb
WHERE txlb.kle_id = fa_cleb.id
AND txlb.tal_type = 'CRL'
AND trx.id = txlb.tas_id
AND trx.tsu_code <> 'PROCESSED'
AND trx.tas_type = 'CRL'
AND fa_cleb.cle_id = p_cle_id
AND fa_cleb.dnz_chr_id = p_chr_id
AND fa_cleb.lse_id = fa_lseb.id
AND fa_lseb.lty_code = 'FIXED_ASSET';
SELECT iti.id,
iti.object_id1_new,
iti.object_id2_new,
iti.jtot_object_code_new
FROM okl_trx_assets trx,
okl_txl_itm_insts iti,
okc_k_lines_b cleb_ib,
okc_line_styles_b lseb_ib,
okc_k_lines_b cleb_inst,
okc_line_styles_b lseb_inst
WHERE trx.id = iti.tas_id
AND trx.tsu_code <> 'PROCESSED'
AND trx.tas_type = 'CRL'
AND iti.kle_id = cleb_ib.id
AND iti.tal_type = 'CRL'
AND cleb_ib.cle_id = cleb_inst.id
AND cleb_ib.dnz_chr_id = cleb_inst.dnz_chr_id
AND cleb_ib.lse_id = lseb_ib.id
AND lseb_ib.lty_code = 'INST_ITEM'
AND cleb_inst.cle_id = p_cle_id
AND cleb_inst.dnz_chr_id = p_chr_id
AND cleb_inst.lse_id = lseb_inst.id
AND lseb_inst.lty_code = 'FREE_FORM2';
OKL_OKC_MIGRATION_PVT.update_k_party_role
(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_cplv_rec => lp_cplv_rec,
x_cplv_rec => lx_cplv_rec);
OKL_K_PARTY_ROLES_PVT.update_k_party_role
(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_cplv_rec => lp_cplv_rec,
x_cplv_rec => lx_cplv_rec,
p_kplv_rec => lp_kplv_rec,
x_kplv_rec => lx_kplv_rec);
OKL_OKC_MIGRATION_PVT.update_contract_header(
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_restricted_update => OKL_API.G_FALSE,
p_chrv_rec => l_chrv_rec,
x_chrv_rec => lx_chrv_rec);
OKL_RULE_PUB.update_rule(
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_rulv_rec => lp_rulv_rec,
x_rulv_rec => lx_rulv_rec);
OKL_RULE_PUB.update_rule(
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_rulv_rec => lp_rulv_rec,
x_rulv_rec => lx_rulv_rec);
OKL_RULE_PUB.update_rule(
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_rulv_rec => lp_rulv_rec,
x_rulv_rec => lx_rulv_rec);
OKL_OKC_MIGRATION_PVT.update_governance
(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_gvev_rec => lp_gvev_rec,
x_gvev_rec => lx_gvev_rec);
OKL_OKC_MIGRATION_PVT.update_governance
(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_gvev_rec => lp_gvev_rec,
x_gvev_rec => lx_gvev_rec);
l_itiv_ib_tbl.DELETE;
OKL_TXL_ITM_INSTS_PUB.update_txl_itm_insts
(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_iipv_tbl => l_itiv_ib_tbl,
x_iipv_tbl => lx_itiv_ib_tbl);
OKL_TXL_ASSETS_PUB.update_txl_asset_def(
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_tlpv_rec => l_talv_rec,
x_tlpv_rec => lx_talv_rec);
OKL_CONTRACT_PUB.update_contract_line(
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_clev_rec => lp_clev_rec,
p_klev_rec => lp_klev_rec,
x_clev_rec => lx_clev_rec,
x_klev_rec => lx_klev_rec
);
END update_taa_request_info;
SELECT chr.id,
chr.contract_number,
chr.end_date,
st.ste_code sts_code,
chr.authoring_org_id
FROM OKC_K_HEADERS_B chr,
okc_statuses_b st
WHERE chr.id = p_chr_id
and st.code = chr.sts_code;
SELECT new_contract_number
FROM okl_taa_request_details_b
WHERE tcn_id = p_source_trx_id;
SELECT cle.id
FROM okc_k_lines_b cle,
okc_line_styles_b lse
WHERE cle.chr_id = p_new_chr_id
AND cle.dnz_chr_id = p_new_chr_id
AND lse.lty_code = 'FREE_FORM1'
AND cle.lse_id = lse.id
AND NOT EXISTS (SELECT 1
FROM okl_txl_cntrct_lns tcl
WHERE tcl.tcn_id = p_source_trx_id
AND tcl.kle_id = cle.orig_system_id1
AND tcl.before_transfer_yn = 'N');
SELECT cle.id
FROM okc_k_lines_b cle,
okc_line_styles_b lse
WHERE cle.chr_id = p_new_chr_id
AND cle.dnz_chr_id = p_new_chr_id
AND lse.lty_code IN ('FEE','SOLD_SERVICE')
AND cle.lse_id = lse.id
AND NOT EXISTS (SELECT 1
FROM okc_k_lines_b sub_cle
WHERE sub_cle.cle_id = cle.id);
SELECT tsu_code,
complete_transfer_yn,
--Bug# 4198413
date_transaction_occurred
FROM okl_trx_contracts
where id = p_source_trx_id;
select cleb.id
from okc_k_lines_b cleb
where cleb.chr_id = p_chr_id
and cleb.dnz_chr_id = p_chr_id
and cleb.lse_id = 33; --financial asset line id
SELECT okl_rbk_seq.NEXTVAL
INTO l_seq_no
FROM DUAL;
OKL_CONTRACT_PUB.delete_contract_line(
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_line_id => taa_lines_rec.id
);
OKL_CONTRACT_PUB.delete_contract_line(
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_line_id => taa_fee_serv_rec.id
);
update_taa_request_info
(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_chr_id => x_new_chrv_rec.id,
p_taa_trx_id => p_source_trx_id,
p_org_id => ln_old_authoring_org_id);
OKL_CONTRACT_PUB.update_contract_line
(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_clev_rec => l_fin_clev_rec,
p_klev_rec => l_fin_klev_rec,
x_clev_rec => lx_fin_clev_rec,
x_klev_rec => lx_fin_klev_rec);
SELECT chr_new.orig_system_source_code,
chr_new.orig_system_id1,
chr_old.contract_number,
--Bug# 4072796
chr_new.contract_number
FROM okc_k_headers_b chr_new,
okc_k_headers_b chr_old
WHERE chr_new.id = p_chr_id
AND chr_old.id = chr_new.orig_system_id1;
SELECT cle.orig_system_id1 orig_cle_fa,
cle.id id
FROM OKC_K_LINES_B cle,
OKC_LINE_STYLES_B lse
WHERE cle.dnz_chr_id = p_chr_id
AND cle.lse_id = lse.id
AND lse.lty_code = 'FIXED_ASSET';
SELECT cle.orig_system_id1 orig_cle_ib,
cle.id id
FROM OKC_K_LINES_B cle,
OKC_LINE_STYLES_B lse
WHERE cle.dnz_chr_id = p_chr_id
AND cle.lse_id = lse.id
AND lse.lty_code = 'INST_ITEM';
SELECT object1_id1,
object1_id2
FROM okc_k_items
WHERE cle_id = p_orig_cle_id
AND dnz_chr_Id = p_orig_chr_id;
SELECT cle.cle_id finasst_id,
cim.object1_id1 asset_id,
cle_orig.cle_id orig_finasst_id,
asr.id asset_return_id
FROM OKL_ASSET_RETURNS_B asr,
OKC_K_LINES_B cle_orig,
OKC_LINE_STYLES_B lse_orig,
OKC_K_ITEMS cim_orig,
OKC_K_ITEMS cim,
OKC_K_LINES_B cle,
OKC_LINE_STYLES_B lse,
OKC_STATUSES_B sts,
OKL_TXL_ASSETS_B txl
WHERE asr.kle_id = cle_orig.cle_id
AND asr.ars_code = 'RELEASE_IN_PROCESS'
AND cim.object1_id1 = cim_orig.object1_id1
AND cim.object1_id2 = cim_orig.object1_id2
AND cim.jtot_object1_code = cim_orig.jtot_object1_code
AND cim.id <> cim_orig.id
AND cle_orig.id = cim_orig.cle_id
AND cle_orig.dnz_chr_id = cim_orig.dnz_chr_id
AND cle_orig.lse_id = lse_orig.id
AND lse_orig.lty_code = 'FIXED_ASSET'
AND cim.cle_id = cle.id
AND cim.dnz_chr_id = cle.dnz_chr_id
AND cle.id = txl.kle_id
AND cle.dnz_chr_id = p_rel_chr_id
AND cle.lse_id = lse.id
AND lse.lty_code = 'FIXED_ASSET'
AND cle.sts_code = sts.code
AND sts.ste_code not in ('EXPIRED','TERMINATED','CANCELLED')
AND txl.tal_type = 'CRL';
SELECT tcn.id,
tcn.source_trx_id ,
--Bug 6657564- Added
RBR_CODE
--Bug 6657564 - End
FROM okl_trx_contracts tcn,
okl_trx_types_tl try
WHERE tcn.khr_id_old = p_orig_chr_id
AND tcn.khr_id_new = p_new_chr_id
AND tcn_type = 'MAE'
AND tcn.tsu_code <> 'PROCESSED'
AND tcn.try_id = try.id
--rkuttiya added for 12.1.1 Multi GAAP
AND tcn.representation_type = 'PRIMARY'
--
AND try.name = 'Release'
AND try.language= 'US';
SELECT id,
tcn_id
FROM okl_taa_request_details_b
WHERE tcn_id = p_source_trx_id;
OKL_OKC_MIGRATION_PVT.update_contract_item(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_cimv_rec => l_cimv_rec,
x_cimv_rec => lx_cimv_rec);
OKL_OKC_MIGRATION_PVT.update_contract_item(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_cimv_rec => l_cimv_rec,
x_cimv_rec => lx_cimv_rec);
okl_asset_returns_pub.update_asset_returns(
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_artv_rec => l_artv_rec
,x_artv_rec => lx_artv_rec);
OKL_TRANSFER_ASSUMPTION_PVT.update_full_tna_creditline(
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_chr_id => p_chr_id);
OKL_CONTRACT_STATUS_PUB.update_contract_status(
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_khr_status => 'BOOKED',
p_chr_id => p_chr_id);
OKL_TRX_CONTRACTS_PUB.update_trx_contracts
(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_tcnv_rec => l_tcnv_rec,
x_tcnv_rec => l_out_tcnv_rec
);
OKL_TAA_PVT.update_row
(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_taav_rec => l_taav_rec,
x_taav_rec => l_out_taav_rec);
OKL_TRX_CONTRACTS_PUB.update_trx_contracts
(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_tcnv_rec => l_tcnv_rec,
x_tcnv_rec => l_out_tcnv_rec
);