[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_FORM_RECORD_DELETED CONSTANT VARCHAR2(30) := OKL_API.G_FORM_RECORD_DELETED;
G_RECORD_LOGICALLY_DELETED CONSTANT VARCHAR2(30) := OKL_API.G_RECORD_LOGICALLY_DELETED;
G_UPDATE_MODE VARCHAR2(30) := 'UPDATE';
G_DELETE_MODE VARCHAR2(30) := 'DELETE';
G_WF_EVT_CR_LN_UPDATED CONSTANT VARCHAR2(50) := 'oracle.apps.okl.la.credit_line.updated';
select 1
from okl_checklists clist
where clist.checklist_purpose_code = 'CHECKLIST_TEMPLATE_GROUP'
and clist.id = p_ckl_id
;
select cld.todo_item_code,
cld.function_id,
clh.checklist_type
from OKL_CHECKLIST_DTLS_ALL CLD, OKL_CHECKLISTS CLH
where cld.ckl_id = clh.id
and exists (select 1
from okl_checklists chlidren
where chlidren.id = cld.ckl_id
and chlidren.ckl_id = p_ckl_id)
--end modified abhsaxen for performance SQLID 20562590
;
select ckd.TODO_ITEM_CODE,
--start: 06-May-2005 cklee okl.h Lease App IA Authoring |
ckd.FUNCTION_ID,
ckd.CHECKLIST_TYPE
--from okl_checklist_details ckd
from okl_checklist_details_uv ckd
--end: 06-May-2005 cklee okl.h Lease App IA Authoring |
where ckd.ckl_id = p_ckl_id;
PROCEDURE update_credit_chklst_tpl(
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_rulv_rec IN rulv_rec_type
,x_rulv_rec OUT NOCOPY rulv_rec_type
)
is
l_api_name CONSTANT VARCHAR2(30) := 'update_credit_chklst_tpl';
select 1
from okl_checklists clist
where clist.checklist_purpose_code = 'CHECKLIST_TEMPLATE_GROUP'
and clist.id = p_ckl_id
;
select cld.todo_item_code,
cld.function_id,
clh.checklist_type
from OKL_CHECKLIST_DTLS_ALL CLD, OKL_CHECKLISTS CLH
where cld.ckl_id = clh.id
and exists (select 1
from okl_checklists chlidren
where chlidren.id = cld.ckl_id
and chlidren.ckl_id = p_ckl_id)
--end modified abhsaxen for performance SQLID 20562606
;
select ckd.TODO_ITEM_CODE,
--start: 06-May-2005 cklee okl.h Lease App IA Authoring |
ckd.FUNCTION_ID,
ckd.CHECKLIST_TYPE
--from okl_checklist_details ckd
from okl_checklist_details_uv ckd
--end: 06-May-2005 cklee okl.h Lease App IA Authoring |
where ckd.ckl_id = p_ckl_id
;
select rule.id
from okc_rules_b rule
where rule.RULE_INFORMATION_CATEGORY = p_rule_category
and rule.RGP_ID = p_rgp_id
;
select rule.RULE_INFORMATION1,
rule.RULE_INFORMATION2
from okc_rules_b rule
where rule.id = p_rule_id
;
select hdr.ID
from okl_crd_fund_chklst_tpl_hdr_uv hdr
where hdr.khr_id = p_chr_id
;
SAVEPOINT update_credit_chklst_tpl;
3. update rule if changes
-- credit line checklist
3.1.1 get the source of the checklist template lists from OKL link
3.1.2 delete associated checklist
3.2.1 get the source of the checklist template lists from NEW link
3.2.2 create rules based on #3. cursor
-- instance of funding checklist template
3.3.1 get the source of the checklist template lists from OKL link
3.3.2 delete associated checklist
3.4.1 get the source of the checklist template lists from NEW link
3.4.2 create rules based on #3. cursor
-------------------------------------------------------------
*/
-------------------------------------------------------------
--1. create rule group if rule group doesn't exists
-- (handle existing credit line contract or credit line doesn't have list)
-- call create_credit_chklst_tpl()
-------------------------------------------------------------
IF ( lp_rulv_rec.RGP_ID is null OR lp_rulv_rec.RGP_ID = OKC_API.G_MISS_NUM ) THEN
-- rule group FK
lpcrt_rgpv_rec.DNZ_CHR_ID := lp_rulv_rec.DNZ_CHR_ID;
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 => ldel_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 => ldel_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 => ldel_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 => lpcrt3_rulv_rec,
x_rulv_rec => lxcrt3_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);
end update_credit_chklst_tpl;
select 1
from okc_rule_groups_b rgp
where rgp.dnz_chr_id = p_chr_id
and rgp.RGD_CODE = G_CREDIT_CHKLST_TPL
;
select 1
from OKC_RULES_B RULT
where rult.DNZ_CHR_ID = p_chr_id and
nvl(rult.RULE_INFORMATION5, 'NEW') <> 'ACTIVE'and
rult.rule_information_category = 'LACCLD'
--end modified abhsaxen for performance SQLID 20562641
;
select chk.status,
chk.effective_to
from okl_crd_fund_chklst_tpl_hdr_uv chk
where chk.khr_id = p_chr_id
;
select 1
from okc_rules_b rult
where rult.rule_information_category = G_CREDIT_CHKLST_TPL_RULE2--'LACCLD'
and rult.dnz_chr_id = p_chr_id
and rult.RULE_INFORMATION2 = 'Y'
and (rult.RULE_INFORMATION3 <> 'Y' or rult.RULE_INFORMATION3 is null)
;
select 1
from okc_k_headers_b k
where k.id = p_chr_id
and k.sts_code = 'ACTIVE'
;
select rule.rule_information1,
rule.rule_information2
from okc_rules_b rule
where rule.dnz_chr_id = p_chr_id
and rule.rule_information_category = G_CREDIT_CHKLST_TPL_RULE1
;
select 1
from OKL_K_LINES kln,
OKC_K_LINES_B cln
where kln.id = cln.id
and cln.dnz_chr_id = p_contract_id
and kln.amount < 0
;
select 1
from OKL_K_LINES kln,
OKC_K_LINES_B cln
where kln.id = cln.id
and cln.dnz_chr_id = p_contract_id
and kln.amount = 0
;
select 'X'
from OKL_K_LINES kln,
OKC_K_LINES_B cln
where kln.id = cln.id
and cln.dnz_chr_id = p_contract_id
and kln.CREDIT_NATURE = 'NEW'
;
select cln.dnz_chr_id
from OKL_K_LINES kln,
OKC_K_LINES_B cln
where kln.id = cln.id
and cln.dnz_chr_id = p_contract_id
and kln.CREDIT_NATURE = 'NEW'
group by cln.dnz_chr_id
having count(1) > 1
;
SELECT 1
FROM okc_k_headers_b chr,
okc_k_lines_b cln
WHERE chr.id = cln.dnz_chr_id
AND chr.start_date IS NOT NULL
AND trunc(cln.start_date) < trunc(chr.start_date)
AND chr.id = p_chr_id
;
SELECT 1
FROM okc_k_headers_b chr,
okc_k_lines_b cln
WHERE chr.id = cln.dnz_chr_id
AND chr.end_date IS NOT NULL
AND trunc(cln.start_date) > trunc(chr.end_date)
AND chr.id = p_chr_id
;
ELSIF (p_mode = 'DELETE') THEN
l_msg_name := 'OKL_LLA_CREDIT_LIMIT_CHECK2';
ELSIF (p_mode = 'UPDATE') THEN
l_msg_name := 'OKL_LLA_CREDIT_LIMIT_CHECK3';
select 'X'
from OKL_K_LINES_FULL_V a
where a.dnz_chr_id = p_contract_id
and a.CREDIT_NATURE = 'NEW'
;
SELECT k.start_date,
k.end_date
FROM okc_k_headers_b k
WHERE k.id = p_chr_id
;
SELECT 'X'
FROM okc_k_headers_b k
WHERE k.contract_number = p_credit_number
-- bug fixed for creditline contarct sub-class
-- and k.scs_code = 'CREDITLINE_CONTRACT'
;
SELECT 'X'
FROM okc_k_headers_b k
WHERE k.contract_number = p_credit_number
AND k.id <> p_id -- except itself
;
select min(start_date)
from OKC_K_LINES_B a
where a.dnz_chr_id = p_contract_id
;
select max(start_date)
from OKC_K_LINES_B a
where a.dnz_chr_id = p_contract_id
;
SELECT k.start_date
FROM okc_k_headers_b k
WHERE k.id = p_chr_id
;
select khr.currency_code
from okc_k_headers_b khr
where khr.id = p_chrv_rec.id
;
select nvl(sum(nvl(a.amount,0)),0)
from OKL_K_LINES_FULL_V a
where a.dnz_chr_id = p_chrv_rec.id
;
select rule.rule_information1,
rule.rule_information2
from okc_rules_b rule
where rule.dnz_chr_id = p_chr_id
and rule.rule_information_category = G_CREDIT_CHKLST_TPL_RULE1
;
select 1
from okc_rules_b rult
where rult.rule_information_category = G_CREDIT_CHKLST_TPL_RULE2--'LACCLD'
and rult.dnz_chr_id = p_chr_id
and rult.RULE_INFORMATION5 = 'ACTIVE'
and exists (select null
from okc_rules_b rult1
where rult1.dnz_chr_id = rult.dnz_chr_id
and rult1.dnz_chr_id = p_chr_id -- impove performance
and rult1.rule_information_category = G_CREDIT_CHKLST_TPL_RULE1--'LACCLT'
and rult1.RULE_INFORMATION1 <> p_crd_chklst_id) -- id changes
;
select 1
from okc_rules_b rult
where rult.rule_information_category = G_CREDIT_CHKLST_TPL_RULE2--'LACCLD'
and rult.dnz_chr_id = p_chr_id
and rult.RULE_INFORMATION2 = 'Y' -- dirty bit check
and exists (select null
from okc_k_headers_b khr
where khr.id = rult.dnz_chr_id
and khr.id = p_chr_id -- impove performance
and khr.sts_code = 'NEW')
and exists (select null
from okc_rules_b rult1
where rult1.dnz_chr_id = rult.dnz_chr_id
and rult1.dnz_chr_id = p_chr_id -- impove performance
and rult1.rule_information_category = G_CREDIT_CHKLST_TPL_RULE1--'LACCLT'
and rult1.RULE_INFORMATION1 <> p_crd_chklst_id) -- id changes
;
select rule.rule_information1,
rule.rule_information2
from okc_rules_b rule
where rule.dnz_chr_id = p_chr_id
and rule.rule_information_category = G_CREDIT_CHKLST_TPL_RULE1
;
select 1
from okc_rules_b rult
where rult.rule_information_category = G_CREDIT_CHKLST_TPL_RULE4--'LACLFM'
and rult.dnz_chr_id = p_chr_id
and rult.RULE_INFORMATION3 = 'ACTIVE'
and exists (select null
from okc_rules_b rult1
where rult1.dnz_chr_id = rult.dnz_chr_id
and rult1.dnz_chr_id = p_chr_id -- impove performance
and rult1.rule_information_category = G_CREDIT_CHKLST_TPL_RULE1--'LACCLT'
and rult1.RULE_INFORMATION2 <> p_fund_chklst_id) -- id changes
;
select 1
from okc_rules_b rult
where rult.rule_information_category = G_CREDIT_CHKLST_TPL_RULE3--'LACLFD'
and rult.dnz_chr_id = p_chr_id
and rult.RULE_INFORMATION2 = 'Y' -- dirty bit check
and exists (select null
from okc_k_headers_b khr
where khr.id = rult.dnz_chr_id
and khr.id = p_chr_id -- impove performance
and khr.sts_code = 'NEW')
and exists (select null
from okc_rules_b rult1
where rult1.dnz_chr_id = rult.dnz_chr_id
and rult1.dnz_chr_id = p_chr_id -- impove performance
and rult1.rule_information_category = G_CREDIT_CHKLST_TPL_RULE1--'LACCLT'
and rult1.RULE_INFORMATION2 <> p_fund_chklst_id) -- id changes
;
select 'X'
from okx_parties_v a
where TRIM(a.name) = TRIM(p_customer_name) -- vsgandhi: Bug 13996614
;
select a.id1
from okx_customer_accounts_v a
where a.id1 = p_cust_acct_id
;
select a.id1
from okx_customer_accounts_v a
where a.description = p_cust_acct_number
;
select sts_code
from okc_k_headers_b
where id = p_chr_id
;
IF (upper(p_mode) <> 'DELETE') THEN
l_return_status := validate_start_date(p_clev_rec,p_klev_rec);
PROCEDURE update_credit_header(
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_restricted_update IN VARCHAR2,
-- funding checklist enhancement
p_chklst_tpl_rgp_id IN NUMBER, -- LACCLH
p_chklst_tpl_rule_id IN NUMBER, -- LACCLT
p_credit_ckl_id IN NUMBER,
p_funding_ckl_id IN NUMBER,
-- funding checklist enhancement
p_chrv_rec IN okl_okc_migration_pvt.chrv_rec_type,
p_khrv_rec IN khrv_rec_type,
x_chrv_rec OUT NOCOPY okl_okc_migration_pvt.chrv_rec_type,
x_khrv_rec OUT NOCOPY khrv_rec_type)
is
-- vthiruva Code change to enable Business Event START
CURSOR c_old_sts_code(p_chr_id okc_k_headers_b.id%TYPE) IS
SELECT sts_code
FROM okc_k_headers_b
WHERE id = p_chr_id;
l_api_name CONSTANT VARCHAR2(30) := 'update_credit_header_pub';
select chrb.cust_acct_id cust_acct_id
from OKC_K_HEADERS_B CHRB
where CHRB.id = p_chr_id;
SAVEPOINT update_credit_header_pub;
update_checklist_function(
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_contract_id => lp_chrv_rec.id);
lp_chrv_rec.sts_code := 'ACTIVE'; -- update to Active directly w/o WF implementation
OKL_CONTRACT_PUB.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_chrv_rec => lp_chrv_rec,
p_khrv_rec => lp_khrv_rec,
x_chrv_rec => x_chrv_rec,
x_khrv_rec => x_khrv_rec);
update_credit_chklst_tpl(
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);
p_event_name => G_WF_EVT_CR_LN_UPDATED);
update_checklist_function(
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_contract_id => lp_chrv_rec.id);
ROLLBACK TO update_credit_header_pub;
ROLLBACK TO update_credit_header_pub;
ROLLBACK TO update_credit_header_pub;
select lse.id
from okc_line_styles_b lse,
okc_subclass_top_line sctl
where lse.lty_code = 'FREE_FORM'
and sctl.lse_id = lse.id
and sctl.scs_code = 'CREDITLINE_CONTRACT';
FUNCTION is_updated(p_old_val IN VARCHAR2,
p_new_val IN VARCHAR2) RETURN BOOLEAN IS
l_return_val BOOLEAN;
END is_updated;
FUNCTION is_updated(p_old_val IN NUMBER,
p_new_val IN NUMBER) RETURN BOOLEAN IS
l_return_val BOOLEAN;
END is_updated;
FUNCTION is_updated(p_old_val IN DATE,
p_new_val IN DATE) RETURN BOOLEAN IS
l_return_val BOOLEAN;
END is_updated;
FUNCTION is_credit_limit_updated(p_clev_rec IN clev_rec_type,
p_klev_rec IN klev_rec_type) RETURN BOOLEAN IS
-- cursor to fetch the okc_k_lines fields which are updatable by user
CURSOR c_clev_csr IS
SELECT ITEM_DESCRIPTION,
START_DATE
FROM OKC_K_LINES_V
WHERE ID = p_clev_rec.ID;
SELECT AMOUNT,
CREDIT_NATURE
FROM OKL_K_LINES_V
WHERE ID = p_clev_rec.ID;
IF((is_updated(l_clev_csr_rec.ITEM_DESCRIPTION, p_clev_rec.ITEM_DESCRIPTION)) OR
(is_updated(l_clev_csr_rec.START_DATE, p_clev_rec.START_DATE))) THEN
l_return_val := TRUE;
IF((is_updated(l_klev_csr_rec.AMOUNT, p_klev_rec.AMOUNT)) OR
(is_updated(l_klev_csr_rec.CREDIT_NATURE, p_klev_rec.CREDIT_NATURE))) THEN
l_return_val := TRUE;
END is_credit_limit_updated;
PROCEDURE update_credit_limit(
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_clev_tbl IN okl_okc_migration_pvt.clev_tbl_type,
p_clev_tbl IN clev_tbl_type,
p_klev_tbl IN klev_tbl_type,
-- x_clev_tbl OUT NOCOPY okl_okc_migration_pvt.clev_tbl_type,
x_clev_tbl OUT NOCOPY clev_tbl_type,
x_klev_tbl OUT NOCOPY klev_tbl_type)
is
l_api_name CONSTANT VARCHAR2(30) := 'update_credit_limit_pub';
SAVEPOINT update_credit_limit_pub;
p_mode => G_UPDATE_MODE,
p_clev_tbl => lp_clev_tbl,
p_klev_tbl => p_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_tbl => lp_clev_tbl,
p_klev_tbl => p_klev_tbl,
x_clev_tbl => lx_clev_tbl,
x_klev_tbl => x_klev_tbl);
IF(is_credit_limit_updated(p_clev_tbl(i),p_klev_tbl(i)))THEN
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_tbl(i),
p_klev_rec => p_klev_tbl(i),
x_clev_rec => lx_clev_tbl(i),
x_klev_rec => x_klev_tbl(i));
,p_mode => 'UPDATE');
ROLLBACK TO update_credit_limit_pub;
ROLLBACK TO update_credit_limit_pub;
ROLLBACK TO update_credit_limit_pub;
PROCEDURE delete_credit_limit(
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_clev_tbl IN okl_okc_migration_pvt.clev_tbl_type,
p_clev_tbl IN clev_tbl_type,
p_klev_tbl IN klev_tbl_type)
is
l_api_name CONSTANT VARCHAR2(30) := 'delete_credit_limit_pub';
SAVEPOINT delete_credit_limit_pub;
p_mode => G_DELETE_MODE,
p_clev_tbl => lp_clev_tbl,
p_klev_tbl => p_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_clev_tbl => lp_clev_tbl,
p_klev_tbl => p_klev_tbl);
,p_mode => 'DELETE');
ROLLBACK TO delete_credit_limit_pub;
ROLLBACK TO delete_credit_limit_pub;
ROLLBACK TO delete_credit_limit_pub;
SELECT
chrb.contract_number contract_number,
chrb.start_date start_date,
chrb.end_date end_date,
chrb.currency_code currency_code,
chrb.sts_code sts_code,
chrb.cust_acct_id cust_acct_id,
chrt.description description,
khr.currency_conversion_type currency_conversion_type,
khr.currency_conversion_rate currency_conversion_rate,
khr.currency_conversion_date currency_conversion_date,
khr.revolving_credit_yn revolving_credit_yn,
cpl.id party_roles_id,
cpl.object1_id1 customer_id1,
cpl.object1_id2 customer_id2,
cpl.jtot_object1_code customer_jtot_object_code,
party.name customer_name,
rul.rule_information1 creditline_ckl_id,
rul.rule_information2 funding_ckl_id,
rul.rgp_id chklst_tpl_rgp_id,
rul.id chklst_tpl_rule_id,
CA.ACCOUNT_NUMBER cust_acct_number
FROM
OKC_K_HEADERS_B CHRB,
OKC_K_HEADERS_TL CHRT,
OKL_K_HEADERS KHR,
HZ_CUST_ACCOUNTS CA,
OKC_RULES_B RUL,
OKX_PARTIES_V PARTY,
OKC_K_PARTY_ROLES_B CPL
WHERE chrb.id = chrt.id
AND chrt.language = USERENV('LANG')
AND chrb.id = khr.id
AND chrb.scs_code = 'CREDITLINE_CONTRACT'
AND CA.CUST_ACCOUNT_ID(+) = chrb.cust_acct_id
AND rul.rule_information_category(+) = 'LACCLT'
AND rul.dnz_chr_id(+) = chrb.id
AND party.id1 = cpl.object1_id1
AND party.id2 = cpl.object1_id2
AND cpl.rle_code = 'LESSEE'
AND cpl.chr_id = chrb.id
AND cpl.DNZ_CHR_ID = cpl.chr_id
AND CHRB.ID = p_chr_id
--end modified abhsaxen for performance SQLID 20562820
;
select
okc.id,
okc.dnz_chr_id,
okc.item_description,
okc.start_date,
okl.credit_nature,
okl.amount
from OKC_K_LINES_V okc,
OKL_K_LINES okl
where okc.id = okl.id
and okc.dnz_chr_id = p_chr_id;
Select access_level
from OKC_ROLE_SOURCES
where rle_code = p_rle_code
and buy_or_sell = 'S';
lp_chrv_rec.deleted_yn := 'N';
select nvl(sum(a.amount),0)
from OKL_K_LINES_FULL_V a
where a.dnz_chr_id = p_contract_id
and a.CREDIT_NATURE = 'NEW'
and nvl(a.start_date,sysdate) <= sysdate
;
select nvl(sum(a.amount),0)
from OKL_K_LINES_FULL_V a
where a.dnz_chr_id = p_contract_id
and a.CREDIT_NATURE = 'ADD'
and nvl(a.start_date,sysdate) <= sysdate
;
select nvl(sum(a.amount),0)
from OKL_K_LINES_FULL_V a
where a.dnz_chr_id = p_contract_id
and a.CREDIT_NATURE = 'REDUCE'
and nvl(a.start_date,sysdate) <= sysdate
;
select CHECKLIST_NUMBER
from okl_checklists ckl
where ckl.id = to_number(p_ckl_id)
;
select DECODE(p_attr, 'RULE_INFORMATION1', to_number(rule.rule_information1),
'RULE_INFORMATION2', to_number(rule.rule_information2))
from okc_rules_b rule
where rule.dnz_chr_id = p_chr_id
and rule.rule_information_category = G_CREDIT_CHKLST_TPL_RULE1
;
select rule.id,
rule.rgp_id,
rule.rule_information1,
rule.rule_information2
from okc_rules_b rule
where rule.dnz_chr_id = p_chr_id
and rule.rule_information_category = G_CREDIT_CHKLST_TPL_RULE1
;
select a.ID
from OKC_K_HEADERS_B a,
okc_Governances_v g
where a.id = g.chr_id_referred
and a.sts_code = 'ACTIVE'
and g.dnz_chr_id = p_contract_id
and a.scs_code = 'CREDITLINE_CONTRACT'
;
select a.ID
from OKC_K_HEADERS_B a,
okc_Governances_v g
where a.id = g.chr_id_referred
and a.sts_code = 'ACTIVE'
and a.scs_code = 'CREDITLINE_CONTRACT'
and g.dnz_chr_id = (select a1.ID -- MLA chrid
from OKC_K_HEADERS_B a1,
okc_Governances_v g1
where a1.id = g1.chr_id_referred
and g1.dnz_chr_id = p_contract_id
and a1.scs_code = 'MASTER_LEASE')
;
PROCEDURE update_credit_line_status(
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,
x_status_code OUT NOCOPY VARCHAR2,
p_status_code IN VARCHAR2,
p_credit_line_id IN NUMBER)
is
l_api_name CONSTANT VARCHAR2(30) := 'update_credit_line_status';
SAVEPOINT update_credit_line_status;
update_credit_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_chrv_rec => lp_chrv_rec,
p_khrv_rec => lp_khrv_rec,
x_chrv_rec => lx_chrv_rec,
x_khrv_rec => lx_khrv_rec
);
ROLLBACK TO update_credit_line_status;
ROLLBACK TO update_credit_line_status;
ROLLBACK TO update_credit_line_status;
end update_credit_line_status;
PROCEDURE update_checklist_function(
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_contract_id IN NUMBER
) is
l_api_name CONSTANT VARCHAR2(30) := 'update_checklist_function';
SELECT
rult.ID,
fun.source function_source
FROM OKC_RULES_B RULT,
OKL_DATA_SRC_FNCTNS_b FUN
WHERE rult.rule_information_category = 'LACCLD' and
rult.RULE_INFORMATION9 = fun.ID and
rult.DNZ_CHR_ID = p_contract_id
--end modified abhsaxen for performance SQLID 20562912
;
SAVEPOINT update_checklist_function;
okl_credit_checklist_pvt.update_credit_chklst(
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_tbl => lp_rulv_tbl,
x_rulv_tbl => lx_rulv_tbl);
ROLLBACK TO update_checklist_function;
ROLLBACK TO update_checklist_function;
ROLLBACK TO update_checklist_function;
end update_checklist_function;
SELECT
chrb.contract_number contract_number,
chrb.start_date start_date,
chrb.end_date end_date,
chrb.currency_code currency_code,
chrb.sts_code sts_code,
chrb.cust_acct_id cust_acct_id,
chrt.description description,
khr.currency_conversion_type currency_conversion_type,
khr.currency_conversion_rate currency_conversion_rate,
khr.currency_conversion_date currency_conversion_date,
khr.revolving_credit_yn revolving_credit_yn,
cpl.id party_roles_id,
cpl.object1_id1 customer_id1,
cpl.object1_id2 customer_id2,
cpl.jtot_object1_code customer_jtot_object_code,
party.name customer_name,
rul.rule_information1 creditline_ckl_id,
rul.rule_information2 funding_ckl_id,
rul.rgp_id chklst_tpl_rgp_id,
rul.id chklst_tpl_rule_id,
CA.ACCOUNT_NUMBER cust_acct_number
FROM
OKC_K_HEADERS_B CHRB,
OKC_K_HEADERS_TL CHRT,
OKL_K_HEADERS KHR,
HZ_CUST_ACCOUNTS CA,
OKC_RULES_B RUL,
OKX_PARTIES_V PARTY,
OKC_K_PARTY_ROLES_B CPL
WHERE chrb.id = chrt.id
AND chrt.language = USERENV('LANG')
AND chrb.id = khr.id
AND chrb.scs_code = 'CREDITLINE_CONTRACT'
AND CA.CUST_ACCOUNT_ID(+) = chrb.cust_acct_id
AND rul.rule_information_category(+) = 'LACCLT'
AND rul.dnz_chr_id(+) = chrb.id
AND party.id1 = cpl.object1_id1
AND party.id2 = cpl.object1_id2
AND cpl.rle_code = 'LESSEE'
AND cpl.chr_id = chrb.id
AND cpl.DNZ_CHR_ID = cpl.chr_id
AND CHRB.ID = p_chr_id;
update_credit_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 => 'F',
-- funding checklist enhancement
p_chklst_tpl_rgp_id => l_credit_rec.CHKLST_TPL_RGP_ID,-- LACCLH
p_chklst_tpl_rule_id => l_credit_rec.CHKLST_TPL_RULE_ID,-- LACCLT
p_credit_ckl_id => l_credit_rec.CREDITLINE_CKL_ID,
p_funding_ckl_id => l_credit_rec.FUNDING_CKL_ID,
-- funding checklist enhancement
p_chrv_rec => lp_chrv_rec,
p_khrv_rec => lp_khrv_rec,
x_chrv_rec => lx_chrv_rec,
x_khrv_rec => lx_khrv_rec
);