[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_WF_EVT_FUN_REQ_UPDATED CONSTANT VARCHAR2(50) := 'oracle.apps.okl.la.funding_request.updated';
select khr.ID KHR_ID,
khr.AUTHORING_ORG_ID ORG_ID,
khr.CURRENCY_CODE,
tap.ID TAP_ID,
tap.VENDOR_INVOICE_NUMBER,
tap.IPVS_ID,
tap.PAYMENT_METHOD_CODE,
-SUM(NVL(OKL_FUNDING_PVT.get_partial_subsidy_amount(tpl.KLE_ID, tpl.AMOUNT),0)) SUBSIDY_TOT_AMT
from okc_k_headers_b khr,
okl_trx_ap_invoices_b tap,
okl_txl_ap_inv_lns_all_b tpl
where khr.id = tpl.khr_id
and tap.id = tpl.tap_id
and tap.id = p_fund_id
and tap.funding_type_code = 'ASSET'
and OKL_FUNDING_PVT.get_partial_subsidy_amount(tpl.kle_id, tpl.amount) > 0
group by khr.ID,
khr.AUTHORING_ORG_ID,
khr.CURRENCY_CODE,
tap.VENDOR_INVOICE_NUMBER,
tap.ID,
tap.IPVS_ID,
tap.PAYMENT_METHOD_CODE
;
select ast.KLE_ID,
ast.AMOUNT
from OKL_TXL_AP_INV_LNS_B ast
where ast.TAP_ID = p_fund_id;
OKL_TXL_AP_INV_LNS_PUB.INSERT_TXL_AP_INV_LNS(
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_tplv_rec => l_tplv_rec,
x_tplv_rec => x_tplv_rec);
select rule.rule_information2
from okc_rules_b rule
where rule.dnz_chr_id = p_credit_id
and rule.rule_information_category = G_CREDIT_CHKLST_TPL_RULE1
;
select rgp.id
from okc_rule_groups_b rgp
where rgp.dnz_chr_id = p_chr_id
and rgp.RGD_CODE = G_FUNDING_CHKLST_TPL
;
select
tpl.TODO_ITEM_CODE,
nvl(tpl.MANDATORY_FLAG, 'N'),
tpl.NOTE,
-- start: 23-May-2005 cklee okl.h Lease App IA Authoring |
-- commented out tpl.STATUS
tpl.FUNCTION_ID,
tpl.CHECKLIST_TYPE
-- end: 23-May-2005 cklee okl.h Lease App IA Authoring |
from okl_crd_fund_checklists_tpl_uv tpl
where tpl.khr_id = p_credit_id
;
select chr.ORIG_SYSTEM_ID1
from okc_k_headers_b chr
where ORIG_SYSTEM_SOURCE_CODE = G_OKL_LEASE_APP
and chr.id = p_chr_id
;
select
chk.TODO_ITEM_CODE,
NVL(chk.MANDATORY_FLAG, 'N') MANDATORY_FLAG,
chk.USER_NOTE,
chk.FUNCTION_ID,
chk.INST_CHECKLIST_TYPE
from OKL_CHECKLIST_DETAILS chk
--START:| 21-Dec-2005 cklee -- Fixed bug#4880288 -- 4908242
,okl_checklists hdr
where chk.ckl_id = hdr.id
and hdr.CHECKLIST_OBJ_ID = p_lease_app_id
--where chk.DNZ_CHECKLIST_OBJ_ID = p_lease_app_id
--END:| 21-Dec-2005 cklee -- Fixed bug#4880288 -- 4908242
and chk.INST_CHECKLIST_TYPE = 'FUNDING'
;
select 1
from OKL_CHECKLIST_DETAILS chk
--START:| 21-Dec-2005 cklee -- Fixed bug#4880288 -- 4908242
,okl_checklists hdr
where chk.ckl_id = hdr.id
and hdr.CHECKLIST_OBJ_ID = p_lease_app_id
--where chk.DNZ_CHECKLIST_OBJ_ID = p_lease_app_id
--END:| 21-Dec-2005 cklee -- Fixed bug#4880288 -- 4908242
and chk.INST_CHECKLIST_TYPE = 'FUNDING'
;
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 = p_MLA_id
;
select fr.id
from OKL_TRX_AP_INVOICES_B fr
,okl_txl_ap_inv_lns_all_b b
where fr.id = b.tap_id
and b.khr_id = p_chr_id
and fr.trx_status_code = 'ENTERED'
;
SELECT rult.ID
FROM okc_rules_b rult
WHERE rult.rule_information_category = 'LAFCLD'
and rult.dnz_chr_id = p_chr_id
;
select 1
from OKL_TRX_AP_INVOICES_B fr
,okl_txl_ap_inv_lns_all_b b
where fr.id = b.tap_id
and b.khr_id = p_chr_id
and fr.trx_status_code in ('APPROVED','PROCESSED')
;
select 1
from okc_k_headers_b chr
where ORIG_SYSTEM_SOURCE_CODE = G_OKL_LEASE_APP
and chr.id = p_chr_id
;
5. Delete all associated list by chr_id
6. Loop for funding requests
call create_funding_chklst_tpl(p_chr_id, p_fund_req_id,p_creditline_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);
ROLLBACK TO UPDATE_FUNDING_LINES_PVT;
ROLLBACK TO UPDATE_FUNDING_LINES_PVT;
ROLLBACK TO UPDATE_FUNDING_LINES_PVT;
select b.khr_id,
a.funding_type_code,
a.ipvs_id,
--start:| 21-May-2007 cklee OKLR12B Accounting CR |
a.amount,
a.date_invoiced,
--end:| 21-May-2007 cklee OKLR12B Accounting CR |
a.org_id
from okl_trx_ap_invoices_b a
,okl_txl_ap_inv_lns_all_b b
where a.id = b.tap_id
and a.id = p_fund_id;
SELECT pdt_id,
--Bug# 4622198
scs_code
FROM okl_k_headers_full_v chr
where chr.id = p_chr_id
;
SELECT id
FROM okl_trx_types_tl
WHERE name = p_trx_name
AND language = 'US'
;
SELECT id
FROM OKL_STRM_TYPE_V
where name = p_name
;
select ct.object1_id1
from okc_contacts ct,
okc_contact_sources csrc,
okc_k_party_roles_b pty,
okc_k_headers_b chr
where ct.cpl_id = pty.id
and ct.cro_code = csrc.cro_code
and ct.jtot_object1_code = csrc.jtot_object_code
and ct.dnz_chr_id = chr.id
and pty.rle_code = csrc.rle_code
and csrc.cro_code = 'SALESPERSON'
and csrc.rle_code = 'LESSOR'
and csrc.buy_or_sell = chr.buy_or_sell
and pty.dnz_chr_id = chr.id
and pty.chr_id = chr.id
and chr.id = p_chr_id;
select cust_trx_type_id
from ra_cust_trx_types
where name = 'Invoice-OKL';
select object1_id1 cust_acct_site_id
from okc_rules_b rul
where rul.rule_information_category = 'BTO'
and exists (select '1'
from okc_rule_groups_b rgp
where rgp.id = rul.rgp_id
and rgp.rgd_code = 'LABILL'
and rgp.chr_id = rul.dnz_chr_id
and rgp.chr_id = p_chr_id );
select bill_to_site_use_id cust_acct_site_id
from okc_k_headers_b chr
where chr.id = p_chr_id;
select id, sty_id
from okl_txl_ap_inv_lns_b txl
where txl.tap_id = p_fund_id;
SELECT khr.currency_code
,chr.currency_conversion_type
,chr.currency_conversion_rate
,chr.currency_conversion_date
FROM okl_k_headers chr,
okc_k_headers_b khr
WHERE chr.id = khr.id
AND khr.id = p_khr_id;
SELECT nvl(tl.amount,0)
FROM okl_txl_ap_inv_lns_b tl
WHERE tl.id = p_fund_line_id;
SELECT tpl.id,
tpl.sty_id,
tpl.amount
FROM okl_txl_ap_inv_lns_all_b tpl
WHERE tpl.tap_id = p_fund_id;
select chr.sts_code,
khr.deal_type
from OKL_K_HEADERS khr,
OKC_K_HEADERS_B chr
where khr.id = chr.id
and khr.id = p_contract_id
;
select 1
from okc_statuses_b ste,
okc_k_headers_b chr
where ste.code = chr.sts_code
and ste.ste_code in ('ENTERED', 'ACTIVE','SIGNED')
and chr.id = p_contract_id
;
select nvl(sum(a.amount),0)
from okl_trx_ap_invoices_b a
,okl_txl_ap_inv_lns_all_b b
where a.id = b.tap_id
and a.funding_type_code = 'PREFUNDING'
and a.trx_status_code in ('APPROVED', 'PROCESSED')
and a.amount < 0 -- adjustments
and b.khr_id = p_contract_id;
select nvl(sum(a.amount),0)
from okl_trx_ap_invoices_b a
,okl_txl_ap_inv_lns_all_b b
where a.id = b.tap_id
and a.funding_type_code = 'PREFUNDING'
and a.trx_status_code in ('APPROVED', 'PROCESSED')
and a.amount < 0 -- adjustments
and b.khr_id = p_contract_id
and a.ipvs_id = p_vendor_site_id;
select 1 from OKL_K_HEADERS khr
where khr.id = p_contract_id
and khr.deal_type = 'LOAN-REVOLVING';
select 1 from OKL_K_HEADERS khr
where khr.id = p_contract_id
and khr.deal_type = 'LOAN-REVOLVING';
select nvl(sum(b.amount),0)
from okl_trx_ap_invoices_b a,
okl_txl_ap_inv_lns_all_b b
where a.id = b.tap_id
and a.trx_status_code in ('APPROVED', 'PROCESSED')
and a.funding_type_code ='ASSET'
and b.amount > 0 --?
and b.khr_id = p_contract_id
;
select nvl(sum(b.amount),0)
from okl_trx_ap_invoices_b a,
okl_txl_ap_inv_lns_all_b b
where a.id = b.tap_id
and a.trx_status_code in ('APPROVED', 'PROCESSED')
and a.funding_type_code ='ASSET'
and b.amount > 0 --?
and b.khr_id = p_contract_id
and a.ipvs_id = p_vendor_site_id
;
select nvl(sum(b.amount),0)
from okl_trx_ap_invoices_b a,
okl_txl_ap_inv_lns_b b
where a.id = b.tap_id
and a.trx_status_code in ('APPROVED', 'PROCESSED')
and a.funding_type_code ='EXPENSE'
and b.amount > 0 --?
and b.khr_id = p_contract_id
and exists (select null
from okx_vendor_sites_v vs
where vs.id1 = a.ipvs_id
and vs.id1 = p_vendor_site_id)
;
select nvl(sum(b.amount),0)
from okl_trx_ap_invoices_b a,
okl_txl_ap_inv_lns_b b
where a.id = b.tap_id
and a.trx_status_code in ('APPROVED', 'PROCESSED')
and a.funding_type_code ='EXPENSE'
and b.amount > 0 --?
and b.khr_id = p_contract_id;
select vendor_id from okx_vendor_sites_v
where id1 = to_char(p_vendor_site_id)
;
select nvl(cle.AMOUNT,0),
cle.id,
nvl(cle.start_date,k.start_date)
from OKL_K_LINES_FULL_V cle,
okc_k_party_roles_b cpl,
okc_line_styles_b ls,
okc_k_headers_b k
where k.id = cle.dnz_chr_id
and cle.dnz_chr_id = p_contract_id
and cle.lse_id = ls.id
and ls.lty_code = p_rle_code
and cle.id = cpl.cle_id
and cpl.dnz_chr_id = p_contract_id
and cpl.chr_id is null
and cpl.rle_code = 'OKL_VENDOR'
and cpl.object1_id1 = to_char(p_vendor_id)
and cpl.object1_id2 = '#'
-- Pass through check
/* and not exists (select null
from okc_rule_groups_v crg,
okc_rules_v cr
where crg.dnz_chr_id = p_contract_id
and crg.cle_id = cle.id -- line id for rle_code
and crg.id = cr.rgp_id
and crg.rgd_code = 'LAPSTH') */
and not exists (select null
from okl_party_payment_hdr phr
where phr.dnz_chr_id = p_contract_id
and phr.cle_id = cle.id)
;
SELECT SUM(NVL(kle_cov_asset.capital_amount,0)) amount
FROM okc_k_lines_b cleb_cov_asset,
okl_k_lines_full_v kle_cov_asset,
okc_k_items cim_cov_asset,
okc_k_lines_b cleb_fin,
okc_statuses_b sts,
okl_k_lines kle
WHERE kle_cov_asset.id = cleb_cov_asset.id
AND cim_cov_asset.cle_id = cleb_cov_asset.id
AND cim_cov_asset.dnz_chr_id = cleb_cov_asset.dnz_chr_id
AND cim_cov_asset.jtot_object1_code = 'OKX_COVASST'
AND cleb_fin.id = cim_cov_asset.object1_id1
AND CLEB_FIN.STS_CODE = STS.CODE
AND STS.STE_CODE <> 'CANCELLED'
and cleb_cov_asset.dnz_chr_id = p_contract_id
and kle.id = cim_cov_asset.object1_id1
--and nvl(kle.re_lease_yn,'N') <> 'Y' -- ndani - 26-Mar-2013 : Bug# 16493240
and kle_cov_asset.cle_id = p_cle_id;
/*select ceil(decode(cr.object1_id1, 'A', months_between(sysdate, nvl(cle.start_date, k.start_date))/12
, 'M', months_between(sysdate, nvl(cle.start_date, k.start_date))
, 'Q', months_between(sysdate, nvl(cle.start_date, k.start_date))/3
, 'S', months_between(sysdate, nvl(cle.start_date, k.start_date))/6
, months_between(sysdate, nvl(cle.start_date, k.start_date))))*/
select ceil(decode(cr.object1_id1, 'A', months_between(trunc(p_due_date), nvl(cle.start_date, k.start_date))/12
, 'M', months_between(trunc(p_due_date), nvl(cle.start_date, k.start_date))
, 'Q', months_between(trunc(p_due_date), nvl(cle.start_date, k.start_date))/3
, 'S', months_between(trunc(p_due_date), nvl(cle.start_date, k.start_date))/6
, months_between(trunc(p_due_date), nvl(cle.start_date, k.start_date))))
--cklee end 10/3/2007 bug: 6128765
from okc_rule_groups_v crg,
okc_rules_v cr,
OKL_K_LINES_FULL_V cle,
okc_k_headers_b k
where crg.dnz_chr_id = p_contract_id
and cle.dnz_chr_id = k.id
and crg.id = cr.rgp_id
and crg.rgd_code = 'LAFEXP'
and crg.cle_id = cle.id
and cr.RULE_INFORMATION_CATEGORY = 'LAFREQ'
and cle.id = p_cle_id
;
select to_number(nvl(cr.RULE_INFORMATION1,'0'))
,to_number(nvl(cr.RULE_INFORMATION2,'0'))
from okc_rule_groups_v crg,
okc_rules_v cr
where crg.dnz_chr_id = p_contract_id
and crg.id = cr.rgp_id
and crg.rgd_code = 'LAFEXP'
and cr.RULE_INFORMATION_CATEGORY = 'LAFEXP'
and crg.cle_id = p_cle_id
;
select nvl(cle.AMOUNT,0),
cle.id,
nvl(cle.start_date,k.start_date)
from OKL_K_LINES_FULL_V cle,
okc_k_party_roles_b cpl,
okc_line_styles_b ls,
okc_k_headers_b k
where k.id = cle.dnz_chr_id
and cle.dnz_chr_id = p_contract_id
and cle.lse_id = ls.id
and ls.lty_code = p_rle_code
and cle.id = cpl.cle_id
and cpl.dnz_chr_id = p_contract_id
and cpl.chr_id is null
and cpl.rle_code = 'OKL_VENDOR'
--and cpl.object1_id1 = to_char(p_vendor_id)
--and cpl.object1_id2 = '#'
-- Pass through check
/*
and not exists (select null
from okc_rule_groups_v crg,
okc_rules_v cr
where crg.dnz_chr_id = p_contract_id
and crg.cle_id = cle.id -- line id for rle_code
and crg.id = cr.rgp_id
and crg.rgd_code = 'LAPSTH') */
and not exists (select null
from okl_party_payment_hdr phr
where phr.dnz_chr_id = p_contract_id
and phr.cle_id = cle.id);
SELECT SUM(NVL(kle_cov_asset.capital_amount,0)) amount
FROM okc_k_lines_b cleb_cov_asset,
okl_k_lines_full_v kle_cov_asset,
okc_k_items cim_cov_asset,
okc_k_lines_b cleb_fin,
okc_statuses_b sts,
okl_k_lines kle
WHERE kle_cov_asset.id = cleb_cov_asset.id
AND cim_cov_asset.cle_id = cleb_cov_asset.id
AND cim_cov_asset.dnz_chr_id = cleb_cov_asset.dnz_chr_id
AND cim_cov_asset.jtot_object1_code = 'OKX_COVASST'
AND cleb_fin.id = cim_cov_asset.object1_id1
AND CLEB_FIN.STS_CODE = STS.CODE
AND STS.STE_CODE <> 'CANCELLED'
and cleb_cov_asset.dnz_chr_id = p_contract_id
and kle.id = cim_cov_asset.object1_id1
--and nvl(kle.re_lease_yn,'N') <> 'Y' -- ndani - 26-Mar-2013 : Bug# 16493240
and kle_cov_asset.cle_id = p_cle_id;
/*select ceil(decode(cr.object1_id1, 'A', months_between(sysdate, nvl(cle.start_date, k.start_date))/12
, 'M', months_between(sysdate, nvl(cle.start_date, k.start_date))
, 'Q', months_between(sysdate, nvl(cle.start_date, k.start_date))/3
, 'S', months_between(sysdate, nvl(cle.start_date, k.start_date))/6
, months_between(sysdate, nvl(cle.start_date, k.start_date))))*/
select ceil(decode(cr.object1_id1, 'A', months_between(trunc(p_due_date), nvl(cle.start_date, k.start_date))/12
, 'M', months_between(trunc(p_due_date), nvl(cle.start_date, k.start_date))
, 'Q', months_between(trunc(p_due_date), nvl(cle.start_date, k.start_date))/3
, 'S', months_between(trunc(p_due_date), nvl(cle.start_date, k.start_date))/6
, months_between(trunc(p_due_date), nvl(cle.start_date, k.start_date))))
--cklee end 10/3/2007 bug: 6128765
from okc_rule_groups_v crg,
okc_rules_v cr,
OKL_K_LINES_FULL_V cle,
okc_k_headers_b k
where crg.dnz_chr_id = p_contract_id
and cle.dnz_chr_id = k.id
and crg.id = cr.rgp_id
and crg.rgd_code = 'LAFEXP'
and crg.cle_id = cle.id
and cr.RULE_INFORMATION_CATEGORY = 'LAFREQ'
and cle.id = p_cle_id
;
select to_number(nvl(cr.RULE_INFORMATION1,'0'))
,to_number(nvl(cr.RULE_INFORMATION2,'0'))
from okc_rule_groups_v crg,
okc_rules_v cr
where crg.dnz_chr_id = p_contract_id
and crg.id = cr.rgp_id
and crg.rgd_code = 'LAFEXP'
and cr.RULE_INFORMATION_CATEGORY = 'LAFEXP'
and crg.cle_id = p_cle_id
;
SELECT nvl(rul.rule_information1, 'N') FROM
okc_rules_b rul, okc_rule_groups_b rgp
WHERE rul.rule_information_category='LARLES'
AND rgp.id = rul.rgp_id
AND rgp.rgd_code = 'LARLES'
AND rgp.dnz_chr_id= p_contract_id;
/*SELECT nvl(kle.re_lease_yn,'N')
FROM OKC_K_HEADERS_B chr
,okc_k_lines_b cle
,okl_k_lines kle
WHERE chr.id = cle.chr_id
and chr.id = cle.dnz_chr_id
and cle.id = kle.id
and chr.id = p_contract_id;
select tpl.status
from okl_crd_fund_checklists_tpl_uv tpl
where tpl.khr_id = p_credit_id
;
select 1
from okl_crd_fund_chklst_tpl_hdr_uv chk
where TRUNC(chk.effective_to) < TRUNC(sysdate)
and chk.khr_id = p_credit_id
;
select 1
from okl_funding_checklists_uv chk
where fund_req_id = TO_CHAR(p_req_id) -- cklee: 11/04/2004
;
select 1
from okc_rules_b rult
where rult.rule_information_category = G_FUNDING_CHKLST_TPL_RULE1--'LAFCLD'
and rult.dnz_chr_id = p_chr_id
and rult.object1_id1 = p_fund_req_id
and rult.object1_id2 = '#'
and rult.RULE_INFORMATION2 = 'Y'
and (rult.RULE_INFORMATION3 <> 'Y' or rult.RULE_INFORMATION3 is null)
;
select 1
from okc_k_headers_b chr
where ORIG_SYSTEM_SOURCE_CODE = G_OKL_LEASE_APP
and chr.id = p_chr_id
;
SELECT
nvl(a.ASSET_NUMBER,'X') ASSET_NUMBER, a.CHR_ID, a.CLE_ID, b.ipvs_id
FROM okl_assets_lov_uv a,
okl_trx_ap_invoices_b b
WHERE a.chr_id = b.khr_id
and b.id = p_fund_id;
SELECT
nvl(a.ASSET_NUMBER,'X')
FROM okl_assets_lov_uv a,
--START:| 13-Apr-2006 cklee -- Fixed bug#5160342 |
okl_trx_ap_invoices_b b,
OKL_TXL_AP_INV_LNS_V c
WHERE a.chr_id = c.khr_id
and b.id = c.TAP_ID
and a.cle_id = c.kle_id
--END:| 13-Apr-2006 cklee -- Fixed bug#5160342 |
and b.id = p_fund_id
and OKL_FUNDING_PVT.get_contract_line_amt(a.CHR_ID, a.CLE_ID, b.ipvs_id) > 0
and OKL_FUNDING_PVT.get_contract_line_funded_amt(a.CHR_ID, a.CLE_ID) >
OKL_FUNDING_PVT.get_contract_line_amt(a.CHR_ID, a.CLE_ID, b.ipvs_id); */
select nvl(sum(b.amount),0)
from okl_trx_ap_invoices_b a,
okl_txl_ap_inv_lns_b b
where a.id = b.tap_id
and b.tap_id = p_fund_id
and a.trx_status_code IN ('ENTERED','SUBMITTED')
;
select nvl(sum(b.amount),0)
from okl_trx_ap_invoices_b a,
okl_txl_ap_inv_lns_all_b b
where a.id = b.tap_id
and a.trx_status_code in ('APPROVED', 'PROCESSED')
and a.funding_type_code ='ASSET'
and b.amount > 0 --?
and b.khr_id = p_contract_id;
select nvl(sum(b.amount),0)
from okl_trx_ap_invoices_b a,
okl_txl_ap_inv_lns_all_b b
where a.id = b.tap_id
and a.trx_status_code in ('APPROVED', 'PROCESSED')
and a.funding_type_code ='EXPENSE'
and b.amount > 0 --?
and b.khr_id = p_contract_id
and exists (select null
from okx_vendor_sites_v vs
where vs.id1 = a.ipvs_id
and vs.id1 = p_vendor_site_id)
;
select count(1)
from OKC_K_HEADERS_B a
where id = p_contract_id
and sts_code = 'BOOKED'
;
select 1 from OKL_K_HEADERS khr
where khr.id = p_contract_id
and khr.deal_type = 'LOAN-REVOLVING';
select 1 from OKL_K_HEADERS khr
where khr.id = p_contract_id
and khr.deal_type = 'LOAN-REVOLVING';
select vs.vendor_id
from ap_supplier_sites vs
where vs.vendor_site_id = p_vendor_site_id
;
select a.application_id
from FND_APPLICATION a
where APPLICATION_SHORT_NAME = 'OKL'
;
select to_number(a.set_of_books_id)
from HR_OPERATING_UNITS a
where ORGANIZATION_ID = p_org_id
;
select a.TERMS_ID, a.PAY_GROUP_LOOKUP_CODE
from PO_VENDOR_SITES_ALL a
where vendor_site_id = p_vendor_site_id
;
select ste.meaning
from OKC_STATUSES_V ste,
okc_k_headers_b chr
where ste.code = chr.sts_code
and chr.id = p_chr_id
;
select a.DEAL_TYPE
from OKL_K_HEADERS a
where a.id = p_contract_id
;
select khr.REVOLVING_CREDIT_YN,
NVL(chr.END_DATE, SYSDATE)
from okl_k_headers khr,
okc_k_headers_b chr
where khr.id = chr.id
and chr.id = p_credit_id
;
select 1
from OKL_K_HEADERS khr
where khr.id = p_khr_id
and khr.deal_type = 'LOAN-REVOLVING';
select 1
from okl_fund_vendor_sites_uv vs
where vs.dnz_chr_id = p_khr_id;
select nvl(khr.PREFUNDING_ELIGIBLE_YN, 'N')
from OKL_K_HEADERS khr
where khr.id = p_contract_id
;
select trx_status_code
from OKL_TRX_AP_INVOICES_B
where id = p_req_id
;
select 1 from OKL_K_HEADERS khr
where khr.id = p_contract_id
and khr.deal_type = 'LOAN-REVOLVING';
select 1
from okl_trx_ap_invoices_b a
,okl_txl_ap_inv_lns_all_b b
where a.id = b.tap_id
and a.vendor_invoice_number = a.invoice_number
and b.khr_id = p_contract_id;
select lok.meaning
from fnd_lookups lok
where lok.lookup_type = 'OKL_FUNDING_TYPE'
and lok.lookup_code = p_funding_type_code
;
SELECT 'X'
FROM OKL_TRX_AP_INVOICES_B
WHERE id = p_tap_id
AND funding_type_code = 'ASSET'
;
SELECT 'X'
FROM OKL_TRX_AP_INVOICES_B
WHERE id = p_tap_id
AND funding_type_code = 'ASSET'
;
SELECT 'X'
FROM OKL_TRX_AP_INVOICES_B
WHERE id = p_tap_id
AND funding_type_code in ('SUPPLIER_RETENTION', 'EXPENSE')
;
select invoice_type
from okl_trx_ap_invoices_b
where id = p_tap_id;
select vs.vendor_id
from PO_VENDOR_SITES_ALL VS
where vs.vendor_site_id = p_vendor_site_id;
select chr.AUTHORING_ORG_ID
from okc_k_headers_b chr
where chr.id = p_khr_id;
SELECT id
FROM OKL_STRM_TYPE_V
where name = p_name
;
select tap.funding_type_code , tpl.khr_id
from okl_trx_ap_invoices_b tap
,okl_txl_ap_inv_lns_all_b tpl
where tap.id = p_tap_id
and tap.id = tpl.tap_id;*/
select tap.funding_type_code /*, tpl.khr_id*/
from okl_trx_ap_invoices_b tap
/*,okl_txl_ap_inv_lns_all_b tpl*/
where tap.id = p_tap_id
/*and tap.id = tpl.tap_id*/;
SELECT h.id,
h.funding_type_code,
h.VENDOR_INVOICE_NUMBER,
h.PAY_GROUP_LOOKUP_CODE,
h.NETTABLE_YN,
h.INVOICE_TYPE
FROM OKL_TRX_AP_INVOICES_B h
WHERE h.id = p_id
;
OKL_TRX_AP_INVOICES_PUB.UPDATE_TRX_AP_INVOICES(
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_tapv_rec => l_tapv_rec,
x_tapv_rec => x_tapv_rec);
select TRYB.ID
FROM
OKL_TRX_TYPES_B TRYB,
OKL_TRX_TYPES_TL TRYT
WHERE
TRYB.ID = TRYT.ID and
TRYT.LANGUAGE = 'US' and
TRYT.NAME = p_try_name; -- cklee 05/04/2004
SELECT chr.authoring_org_id
FROM okc_k_headers_b chr
WHERE chr.id = p_chr_id;
SELECT contract_number
FROM okc_k_headers_b
WHERE id = p_ctr_id1;
OKL_TRX_AP_INVOICES_PUB.INSERT_TRX_AP_INVOICES(
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_tapv_rec => l_tapv_rec,
x_tapv_rec => x_tapv_rec);
OKL_TXL_AP_INV_LNS_PUB.INSERT_TXL_AP_INV_LNS(
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_tplv_rec => l_tplv_rec,
x_tplv_rec => x_tplv_rec);
PROCEDURE update_funding_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_tapv_rec IN tapv_rec_type
,x_tapv_rec OUT NOCOPY tapv_rec_type
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_FUNDING_HEADER';
SELECT 1
FROM OKC_RULES_B RULT
WHERE rult.rule_information_category = 'LAFCLD'
and rult.OBJECT1_ID1 = p_fund_id ;
Select id funding_line_id
from OKL_TXL_AP_INV_LNS_B
Where tap_id = p_fund_id;
SAVEPOINT UPDATE_FUNDING_HEADER_PVT;
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_fund_req_id => l_tapv_rec.id);
OKL_TRX_AP_INVOICES_PUB.UPDATE_TRX_AP_INVOICES(
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_tapv_rec => l_tapv_rec,
x_tapv_rec => x_tapv_rec);
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_fund_req_id => l_tapv_rec.id);
l_event_name := G_WF_EVT_FUN_REQ_UPDATED;
ROLLBACK TO UPDATE_FUNDING_HEADER_PVT;
ROLLBACK TO UPDATE_FUNDING_HEADER_PVT;
ROLLBACK TO UPDATE_FUNDING_HEADER_PVT;
END update_funding_header;
SELECT CURRENCY_CODE
FROM OKL_TRX_AP_INVOICES_B
WHERE ID = cp_tap_id;
SELECT KHR_ID
FROM OKL_TRX_AP_INVOICES_B
WHERE ID = p_tap_id;
SELECT MAX(LINE_NUMBER)
FROM OKL_TXL_AP_INV_LNS_B
WHERE TAP_ID = P_TAP_ID;
OKL_TXL_AP_INV_LNS_PUB.INSERT_TXL_AP_INV_LNS(
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_tplv_tbl => l_tplv_tbl,
x_tplv_tbl => x_tplv_tbl);
SELECT a.cle_id cle_id,
a.chr_id chr_id,
a.asset_number kle_num,
a.description kle_name,
okl_funding_pvt.get_contract_line_amt(
a.chr_id,
a.cle_id,
p_vendor_site_id
) kle_amt
FROM okl_assets_lov_uv a,
okc_k_party_roles_b cpl,
okc_k_lines_b LN,
okx_vendor_sites_v sites
WHERE a.chr_id = p_khr_id
AND cpl.rle_code = 'OKL_VENDOR'
AND cpl.chr_id IS NULL
AND cpl.dnz_chr_id = a.chr_id
AND cpl.object1_id1 = TO_CHAR(sites.vendor_id)
AND sites.id1 = p_vendor_site_id
AND cpl.object1_id2 = '#'
AND cpl.cle_id = LN.ID
AND LN.cle_id = a.cle_id;
SELECT AUTHORING_ORG_ID
FROM OKC_K_HEADERS_ALL_B
WHERE ID = p_khr_id;
PROCEDURE update_funding_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_tplv_tbl IN tplv_tbl_type
,x_tplv_tbl OUT NOCOPY tplv_tbl_type
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_FUNDING_LINES';
select sty_id
from okl_txl_ap_inv_lns_b
where id = p_tpl_id
;
SELECT CURRENCY_CODE
FROM OKL_TRX_AP_INVOICES_B
WHERE ID = cp_tap_id;
SELECT KHR_ID
FROM OKL_TRX_AP_INVOICES_B
WHERE ID = p_tap_id;
SAVEPOINT UPDATE_FUNDING_LINES_PVT;
OKL_TXL_AP_INV_LNS_PUB.UPDATE_TXL_AP_INV_LNS(
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_tplv_tbl => l_tplv_tbl,
x_tplv_tbl => x_tplv_tbl);
ROLLBACK TO UPDATE_FUNDING_LINES_PVT;
ROLLBACK TO UPDATE_FUNDING_LINES_PVT;
ROLLBACK TO UPDATE_FUNDING_LINES_PVT;
END update_funding_lines;
select a.org_id
, a.currency_code
from okl_trx_ap_invoices_b a
where a.id = p_fund_id
and a.funding_type_code = 'ASSET'
;
select a.cle_id
,a.KLE_AMT
from okl_fund_assets_lov_uv a
where a.FUND_ID = p_fund_id
and a.KLE_AMT > 0
and NOT EXISTS
(select 1
from okl_txl_ap_inv_lns_b b
where a.FUND_ID = b.tap_id
and a.cle_id = b.kle_id)
;*/
--SELECT cle_id, KLE_AMT
--FROM
--(
select a.cle_id,
(select OKL_FUNDING_PVT.get_contract_line_amt(a.CHR_ID, a.CLE_ID, b.ipvs_id) from dual) KLE_AMT
from OKL_ASSETS_LOV_UV A,
OKL_TRX_AP_INVOICES_B b
WHERE a.chr_id = b.khr_id
AND b.ID = p_fund_id
and NOT EXISTS
(select 1
from okl_txl_ap_inv_lns_b c
where b.ID = c.tap_id
and a.cle_id = c.kle_id)
-- )
--WHERE KLE_AMT > 0
;
SELECT KHR_ID
FROM OKL_TRX_AP_INVOICES_B
WHERE ID = p_tap_id;
OKL_TXL_AP_INV_LNS_PUB.INSERT_TXL_AP_INV_LNS(
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_tplv_tbl => l_tplv_tbl,
x_tplv_tbl => x_tplv_tbl);
SELECT h.PAY_GROUP_LOOKUP_CODE,
h.NETTABLE_YN
FROM OKL_TRX_AP_INVOICES_B h
WHERE h.id = p_id
;
select a.AUTHORING_ORG_ID,
a.CURRENCY_CODE,
a.CONTRACT_NUMBER
from okc_k_headers_b a
where a.id = p_contract_id
;
select a.ipvs_id,
nvl(sum(OKL_FUNDING_PVT.get_contract_line_funded_amt(a.id,a.funding_type_code)),0)
from okl_trx_ap_invoices_b a
,okl_txl_ap_inv_lns_all_b b
where a.id = b.tap_id
and b.khr_id = p_contract_id
and a.trx_status_code in ('APPROVED', 'PROCESSED')
and a.funding_type_code IS NOT NULL -- cklee 09-24-03
and a.funding_type_code <> 'SUPPLIER_RETENTION'
group by a.ipvs_id
;
select a.name
from OKX_VENDOR_SITES_V a
where a.id1 = p_ipvs_id
;
Select id funding_line_id
from OKL_TXL_AP_INV_LNS_B
Where tap_id = p_fund_id;
update_funding_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_tapv_rec => l_tapv_rec,
x_tapv_rec => x_tapv_rec);
SELECT vendor_id
FROM okx_vendor_sites_v
WHERE id1 = TO_CHAR(p_vendor_site_id);
SELECT NVL(SUM(NVL(cle.price_unit, 0) * NVL(cim.number_of_items, 0)), 0)
FROM okc_k_items cim,
okc_k_party_roles_b cpl,
okc_k_lines_b cle,
--Bug# 15992711 : avsingh - New-Re-leased Asset Enhancement
okl_k_lines kle_top
--End Bug# Bug# 15992711 : New Re-leased Asset Enhancement
WHERE
--for model lines of vendor
(
cim.cle_id = cle.ID
AND cim.dnz_chr_id = p_khr_id
AND cpl.cle_id = cle.ID
AND cpl.dnz_chr_id = p_khr_id
AND cpl.chr_id IS NULL
AND cpl.rle_code = 'OKL_VENDOR'
AND cpl.object1_id1 = TO_CHAR(p_vendor_id)
AND cpl.object1_id2 = '#'
AND EXISTS(SELECT NULL
FROM okc_line_styles_b model_lse
WHERE model_lse.ID = cle.lse_id AND model_lse.lty_code = 'ITEM' AND cle.cle_id = p_kle_id)
--Bug# 13809564
AND cle.sts_code <> 'ABANDONED'
--Bug# 15992711 : avsingh - New-Re-leased Asset Enhancement
--Re-lease lines should not be included in total fundable calculation
AND kle_top.id = p_kle_id
AND nvl(kle_top.re_lease_yn,'N') = 'N'
--End Bug# Bug# 15992711 : New Re-leased Asset Enhancement
); -- end of model lines
SELECT NVL(SUM(NVL(cle.price_unit, 0) * NVL(cim.number_of_items, 0)), 0)
FROM okc_k_items cim,
okc_k_party_roles_b cpl,
okc_k_lines_b cle,
--Bug# 15992711 : avsingh - New-Re-leased Asset Enhancement
okl_k_lines kle_top
--End Bug# Bug# 15992711 : New Re-leased Asset Enhancement
WHERE
--for add on lines: don't care the vendor association, but care if it has vendor association!
(
cim.cle_id = cle.ID
AND cim.dnz_chr_id = p_khr_id
AND cpl.cle_id = cle.ID
AND cpl.dnz_chr_id = p_khr_id
AND cpl.chr_id IS NULL
AND cpl.rle_code = 'OKL_VENDOR'
-- start: okl.h: cklee
AND cpl.object1_id1 = TO_CHAR(p_vendor_id)
AND cpl.object1_id2 = '#'
-- end: okl.h: cklee
AND EXISTS(SELECT NULL
FROM okc_line_styles_b adon_lse
WHERE adon_lse.ID = cle.lse_id AND adon_lse.lty_code = 'ADD_ITEM'
-- ADD_ITEM has one parent ITEM only
AND EXISTS(SELECT NULL
FROM okc_k_lines_b mdl_parent
WHERE mdl_parent.ID = cle.cle_id AND mdl_parent.cle_id = p_kle_id))
--Bug# 13809564
AND cle.sts_code <> 'ABANDONED'
--Bug# 15992711 : avsingh - New-Re-leased Asset Enhancement
--Re-lease lines should not be included in total fundable calculation
AND kle_top.id = p_kle_id
AND nvl(kle_top.re_lease_yn,'N') = 'N'
--End Bug# Bug# 15992711 : New Re-leased Asset Enhancement
); -- end of add on lines
SELECT NVL(SUM(NVL(cle.price_unit, 0) * NVL(cim.number_of_items, 0)), 0)
FROM okc_k_items cim,
okc_k_party_roles_b cpl,
okc_k_lines_b cle,
--Bug# 15992711 : avsingh - New-Re-leased Asset Enhancement
okl_k_lines kle_top
--End Bug# Bug# 15992711 : New Re-leased Asset Enhancement
WHERE
--for model lines of vendor
(
cim.cle_id = cle.ID
AND cim.dnz_chr_id = p_khr_id
AND cpl.cle_id = cle.ID
AND cpl.dnz_chr_id = p_khr_id
AND cpl.chr_id IS NULL
AND cpl.rle_code = 'OKL_VENDOR'
--and cpl.object1_id1 = to_char(p_vendor_id)
--and cpl.object1_id2 = '#'
AND EXISTS(SELECT NULL
FROM okc_line_styles_b model_lse
WHERE model_lse.ID = cle.lse_id AND model_lse.lty_code = 'ITEM' AND cle.cle_id = p_kle_id)
--Bug# 13809564
AND cle.sts_code <> 'ABANDONED'
--Bug# 15992711 : avsingh - New-Re-leased Asset Enhancement
--Re-lease lines should not be included in total fundable calculation
AND kle_top.id = p_kle_id
AND nvl(kle_top.re_lease_yn,'N') = 'N'
--End Bug# Bug# 15992711 : New Re-leased Asset Enhancement
) -- end of model lines
OR
--for add on lines: don't care the vendor association, but care if it has vendor association!
(
cim.cle_id = cle.ID
AND cim.dnz_chr_id = p_khr_id
AND cpl.cle_id = cle.ID
AND cpl.dnz_chr_id = p_khr_id
AND cpl.chr_id IS NULL
AND cpl.rle_code = 'OKL_VENDOR'
--and cpl.object1_id1 = to_char(p_vendor_id)
--and cpl.object1_id2 = '#'
AND EXISTS(SELECT NULL
FROM okc_line_styles_b adon_lse
WHERE adon_lse.ID = cle.lse_id AND adon_lse.lty_code = 'ADD_ITEM'
-- ADD_ITEM has one parent ITEM only
AND EXISTS(SELECT NULL
FROM okc_k_lines_b mdl_parent
WHERE mdl_parent.ID = cle.cle_id AND mdl_parent.cle_id = p_kle_id))
--Bug# 13809564
AND cle.sts_code <> 'ABANDONED'
--Bug# 15992711 : avsingh - New-Re-leased Asset Enhancement
--Re-lease lines should not be included in total fundable calculation
AND kle_top.id = p_kle_id
AND nvl(kle_top.re_lease_yn,'N') = 'N'
--End Bug# Bug# 15992711 : New Re-leased Asset Enhancement
); -- end of add on lines
SELECT NVL(SUM(NVL(cle.price_unit, 0) * NVL(cim.number_of_items, 0)), 0)
FROM okc_k_items cim,
okc_k_party_roles_b cpl,
okc_k_lines_b cle,
--Bug# 15992711 : avsingh - New-Released asset ER
okl_k_lines kle_top
--End Bug# 15992711 : New-Released asset ER
WHERE (
cim.cle_id = cle.ID
AND cim.dnz_chr_id = p_khr_id
AND cpl.cle_id = cle.ID
AND cpl.dnz_chr_id = p_khr_id
AND cpl.chr_id IS NULL
AND cpl.rle_code = 'OKL_VENDOR'
AND EXISTS(SELECT NULL
FROM okc_line_styles_b model_lse
WHERE model_lse.ID = cle.lse_id
AND model_lse.lty_code = 'ITEM')
--Bug# 13809564
AND cle.sts_code <> 'ABANDONED'
--Bug# 15992711 : avsingh - New-Released asset ER
AND kle_top.id = cle.cle_id
AND nvl(kle_top.re_lease_yn,'N') = 'N'
--End Bug# 15992711 : New-Released asset ER
)
OR (
cim.cle_id = cle.ID
AND cim.dnz_chr_id = p_khr_id
AND cpl.cle_id = cle.ID
AND cpl.dnz_chr_id = p_khr_id
AND cpl.chr_id IS NULL
AND cpl.rle_code = 'OKL_VENDOR'
AND EXISTS(SELECT NULL
FROM okc_line_styles_b adon_lse
WHERE adon_lse.ID = cle.lse_id
AND adon_lse.lty_code = 'ADD_ITEM'
AND EXISTS(SELECT NULL
FROM okc_k_lines_b mdl_parent
WHERE mdl_parent.ID = cle.cle_id
--Bug# 15992711 : avsingh - New-Released asset ER
AND EXISTS(SELECT NULL
FROM okl_k_lines kle_top1
WHERE kle_top1.id = mdl_parent.cle_id
AND nvl(kle_top1.re_lease_yn,'N') = 'N')))
--End Bug# 15992711 : New-Released asset ER
--Bug# 13809564
AND cle.sts_code <> 'ABANDONED'
);
SELECT down_payment_receiver_code
FROM okl_k_lines
WHERE ID = p_kle_id;
SELECT kle.cle_id kle_id,
kle_k.down_payment_receiver_code downpymnt_recvr
FROM okl_assets_lov_uv kle,
okl_k_lines kle_k
WHERE kle.cle_id = kle_k.ID
AND kle.chr_id = p_khr_id;
SELECT kle_k.id kle_id,
kle_k.down_payment_receiver_code downpymnt_recvr
FROM okl_k_lines kle_k,
okc_k_lines_b kle
where kle_k.id = kle.id
and kle.dnz_chr_id = p_khr_id
--Bug# 15992711 : avsingh - New-Released asset ER
and nvl(kle_k.re_lease_yn,'N') = 'N' -- re lease flag check
--End Bug# 15992711 : New-Released asset ER
--Bug# 13809564
AND kle.sts_code <> 'ABANDONED'
-- only asset lines associated with Lease Vendor (Supplier Invoice)
and exists (
SELECT 1
FROM okc_k_party_roles_b cpl,
okc_k_lines_b cle,
okc_line_styles_b model_lse
WHERE cpl.rle_code = 'OKL_VENDOR'
AND cpl.chr_id IS NULL
and model_lse.ID = cle.lse_id
and model_lse.lty_code = 'ITEM'
and cle.cle_id = kle_k.id -- link to FREE_FORM1 (top line)
AND cpl.object1_id2 = '#'
AND cpl.cle_id = cle.ID); -- link to ITEM
SELECT SUM(tl.amount)
FROM okl_trx_ap_invoices_b th,
okl_txl_ap_inv_lns_all_b tl
WHERE th.id = tl.tap_id
AND tl.khr_id = p_khr_id
AND tl.kle_id = p_kle_id
-- fixed bug 3007875
AND th.TRX_STATUS_CODE NOT IN ('CANCELED', 'ERROR', 'REJECTED');
SELECT SUM(tl.amount)
FROM okl_trx_ap_invoices_b th,
okl_txl_ap_inv_lns_all_b tl
WHERE th.id = tl.tap_id
AND tl.khr_id = p_khr_id
AND tl.kle_id = p_kle_id
AND th.funding_type_code = p_funding_type_code
-- fixed bug 3007875
AND th.TRX_STATUS_CODE NOT IN ('CANCELED', 'ERROR', 'REJECTED');
SELECT nvl(SUM(tl.amount),0)
FROM okl_trx_ap_invoices_b th,
okl_txl_ap_inv_lns_b tl
WHERE tl.tap_id = th.id
AND th.id = p_fund_id
-- no need for this function. this is used for display at UI site only
-- AND th.TRX_STATUS_CODE NOT IN ('CANCELED', 'ERROR', 'REJECTED')
;
SELECT nvl(th.amount,0)
FROM okl_trx_ap_invoices_b th
WHERE th.id = p_fund_id
-- no need for this function. this is used for display at UI site only
-- AND th.TRX_STATUS_CODE NOT IN ('CANCELED', 'ERROR', 'REJECTED')
;
SELECT 'X'
FROM okl_trx_ap_invoices_b th,
okl_txl_ap_inv_lns_all_b tl,
okc_k_headers_b chr,
PO_VENDOR_SITES_ALL VS
WHERE th.id = tl.tap_id
AND tl.khr_id = chr.id
AND th.ipvs_id = vs.vendor_site_id
AND th.vendor_invoice_number = p_fund_number
AND chr.AUTHORING_ORG_ID = p_org_id
AND VS.vendor_id = p_vendor_id;
SELECT 'X'
FROM okl_txl_ap_inv_lns_b t
WHERE t.tap_id = p_fund_id
AND t.kle_id = p_kle_id
;
SELECT 'X'
FROM okl_txl_ap_inv_lns_b t
WHERE t.tap_id = p_fund_id
AND t.kle_id = p_kle_id
AND t.id <> p_fund_line_id -- except itself
;
select nvl(sum(a.amount),0)
from okl_trx_ap_invoices_b a
,okl_txl_ap_inv_lns_all_b b
where a.id = b.tap_id
and a.funding_type_code = 'PREFUNDING'
and a.trx_status_code in ('APPROVED', 'PROCESSED')
and b.khr_id = p_contract_id
-- positive only
and a.amount > 0
;
select nvl(sum(a.amount),0)
from okl_trx_ap_invoices_b a
,okl_txl_ap_inv_lns_all_b b
where a.id = b.tap_id
and a.funding_type_code = 'PREFUNDING'
and a.trx_status_code in ('APPROVED', 'PROCESSED')
and b.khr_id = p_contract_id
and a.ipvs_id = p_vendor_site_id
-- positive only
and a.amount > 0
;
select nvl(sum(b.amount),0)
from okl_trx_ap_invoices_b a,
okl_txl_ap_inv_lns_all_b b
where a.id = b.tap_id
and a.trx_status_code in ('APPROVED', 'PROCESSED')
and a.funding_type_code IN ('ASSET','EXPENSE', G_ASSET_SUBSIDY) -- cklee 11.5.10 subsidy
and b.khr_id = p_contract_id
UNION
select nvl(sum(a.amount),0)
from okl_trx_ap_invoices_b a
,okl_txl_ap_inv_lns_all_b b
where a.id = b.tap_id
and a.funding_type_code in ('PREFUNDING', 'BORROWER_PAYMENT') -- fixed bug# 2604862
and a.trx_status_code in ('APPROVED', 'PROCESSED')
and b.khr_id = p_contract_id
;
select nvl(sum(b.amount),0)
from okl_trx_ap_invoices_b a,
okl_txl_ap_inv_lns_all_b b
where a.id = b.tap_id
and b.khr_id = p_contract_id
and a.trx_status_code in ('APPROVED', 'PROCESSED')
and a.funding_type_code = 'SUPPLIER_RETENTION';
select nvl(sum(a.amount),0)
from okl_trx_ap_invoices_b a
,okl_txl_ap_inv_lns_all_b b
where a.id = b.tap_id
and b.khr_id = p_contract_id
and a.trx_status_code in ('APPROVED', 'PROCESSED')
and a.funding_type_code = 'BORROWER_PAYMENT';
select nvl(sum(a.amount),0)
from okl_trx_ap_invoices_b a
,okl_txl_ap_inv_lns_all_b b
where a.id = b.tap_id
and a.khr_id = p_contract_id
and a.trx_status_code in ('APPROVED', 'PROCESSED')
and a.funding_type_code = G_ASSET_SUBSIDY;
select nvl(sum(subln.amount),0)
from okl_trx_ap_invoices_b sub,
okl_txl_ap_inv_lns_all_b subln
where sub.id = subln.tap_id
and subln.khr_id = p_contract_id
and subln.kle_id = p_contract_line_id -- fixed asset ID
and sub.trx_status_code in ('APPROVED', 'PROCESSED')
and sub.funding_type_code = G_ASSET_SUBSIDY;
select vendor_id from okx_vendor_sites_v
where id1 = to_char(p_vendor_site_id)
;
select nvl(sum(subln.amount),0)
from okl_trx_ap_invoices_b sub,
okl_txl_ap_inv_lns_b subln
where sub.id = subln.tap_id
and sub.trx_status_code in ('APPROVED', 'PROCESSED')
and sub.funding_type_code = G_MANUAL_DISB
and subln.khr_id = p_contract_id;
select nvl(cle.AMOUNT,0),
cle.id,
nvl(cle.start_date,k.start_date)
from OKL_K_LINES_FULL_V cle,
okc_k_party_roles_b cpl,
okc_line_styles_b ls,
okc_k_headers_b k
where k.id = cle.dnz_chr_id
and cle.dnz_chr_id = p_contract_id
and cle.lse_id = ls.id
and ls.lty_code = p_rle_code
and cle.id = cpl.cle_id
and cpl.dnz_chr_id = p_contract_id
and cpl.chr_id is null
and cpl.rle_code = 'OKL_VENDOR'
and cle.id = p_fee_line_id
--and cpl.object1_id1 = to_char(p_vendor_id)
--and cpl.object1_id2 = '#'
-- Pass through check
/*
and not exists (select null
from okc_rule_groups_v crg,
okc_rules_v cr
where crg.dnz_chr_id = p_contract_id
and crg.cle_id = cle.id -- line id for rle_code
and crg.id = cr.rgp_id
and crg.rgd_code = 'LAPSTH') */
and not exists (select null
from okl_party_payment_hdr phr
where phr.dnz_chr_id = p_contract_id
and phr.cle_id = cle.id)
;
select ceil(decode(cr.object1_id1, 'A', months_between(p_effective_date, nvl(cle.start_date, k.start_date))/12
, 'M', months_between(p_effective_date, nvl(cle.start_date, k.start_date))
, 'Q', months_between(p_effective_date, nvl(cle.start_date, k.start_date))/3
, 'S', months_between(p_effective_date, nvl(cle.start_date, k.start_date))/6
, months_between(p_effective_date, nvl(cle.start_date, k.start_date))))
from okc_rule_groups_v crg,
okc_rules_v cr,
OKL_K_LINES_FULL_V cle,
okc_k_headers_b k
where crg.dnz_chr_id = p_contract_id
and cle.dnz_chr_id = k.id
and crg.id = cr.rgp_id
and crg.rgd_code = 'LAFEXP'
and crg.cle_id = cle.id
and cr.RULE_INFORMATION_CATEGORY = 'LAFREQ'
and cle.id = p_cle_id
;
select to_number(nvl(cr.RULE_INFORMATION1,'0'))
,to_number(nvl(cr.RULE_INFORMATION2,'0'))
from okc_rule_groups_v crg,
okc_rules_v cr
where crg.dnz_chr_id = p_contract_id
and crg.id = cr.rgp_id
and crg.rgd_code = 'LAFEXP'
and cr.RULE_INFORMATION_CATEGORY = 'LAFEXP'
and crg.cle_id = p_cle_id
;
select rule.rule_information2
from okc_rules_b rule
where rule.dnz_chr_id = p_credit_id
and rule.rule_information_category = G_CREDIT_CHKLST_TPL_RULE1
;
select chr.ORIG_SYSTEM_ID1
from okc_k_headers_b chr
where ORIG_SYSTEM_SOURCE_CODE = G_OKL_LEASE_APP
and chr.id = p_chr_id
;
select 1
from OKL_CHECKLIST_DETAILS chk
,okl_checklists hdr
where chk.ckl_id = hdr.id
and hdr.CHECKLIST_OBJ_ID = p_lease_app_id
and chk.INST_CHECKLIST_TYPE = 'FUNDING'
;
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_fund_req_id IN NUMBER
) is
l_api_name CONSTANT VARCHAR2(30) := 'update_checklist_function';
SELECT rult.ID, rult.DNZ_CHR_ID khr_id,
fun.source function_source
FROM OKC_RULES_B RULT,
OKL_DATA_SRC_FNCTNS_B FUN
WHERE rult.rule_information_category = 'LAFCLD'
and rult.object1_id1 = p_fund_req_id
and rult.RULE_INFORMATION9 = fun.Id ;
SAVEPOINT update_checklist_function;
okl_funding_checklist_pvt.update_funding_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 NVL(SUM(NVL(cle.price_unit, 0) * NVL(cim.number_of_items, 0)), 0)
FROM okc_k_items cim,
okc_k_lines_b cle,
okl_k_lines kle_top
WHERE (
cim.cle_id = cle.ID
AND cim.dnz_chr_id = p_khr_id
AND cim.jtot_object1_code = 'OKX_ASSET'
AND cle.sts_code <> 'ABANDONED'
AND kle_top.id = cle.cle_id
AND nvl(kle_top.re_lease_yn,'N') = 'Y'
);
SELECT NVL(SUM(NVL(cle.price_unit, 0) * NVL(cim.number_of_items, 0)), 0)
FROM okc_k_items cim,
okc_k_party_roles_b cpl,
okc_k_lines_b cle,
--Bug# 15992711 : avsingh - New-Released asset ER
okl_k_lines kle_top
--End Bug# 15992711 : New-Released asset ER
WHERE (
cim.cle_id = cle.ID
AND cim.dnz_chr_id = p_khr_id
AND cpl.cle_id = cle.ID
AND cpl.dnz_chr_id = p_khr_id
AND cpl.chr_id IS NULL
AND cpl.rle_code = 'OKL_VENDOR'
AND EXISTS(SELECT NULL
FROM okc_line_styles_b model_lse
WHERE model_lse.ID = cle.lse_id
AND model_lse.lty_code = 'ITEM')
--Bug# 13809564
AND cle.sts_code <> 'ABANDONED'
--Bug# 15992711 : avsingh - New-Released asset ER
AND kle_top.id = cle.cle_id
AND nvl(kle_top.re_lease_yn,'N') = 'Y'
--End Bug# 15992711 : New-Released asset ER
)
OR (
cim.cle_id = cle.ID
AND cim.dnz_chr_id = p_khr_id
AND cpl.cle_id = cle.ID
AND cpl.dnz_chr_id = p_khr_id
AND cpl.chr_id IS NULL
AND cpl.rle_code = 'OKL_VENDOR'
AND EXISTS(SELECT NULL
FROM okc_line_styles_b adon_lse
WHERE adon_lse.ID = cle.lse_id
AND adon_lse.lty_code = 'ADD_ITEM'
AND EXISTS(SELECT NULL
FROM okc_k_lines_b mdl_parent
WHERE mdl_parent.ID = cle.cle_id
--Bug# 15992711 : avsingh - New-Released asset ER
AND EXISTS(SELECT NULL
FROM okl_k_lines kle_top1
WHERE kle_top1.id = mdl_parent.cle_id
AND nvl(kle_top1.re_lease_yn,'N') = 'Y')))
--End Bug# 15992711 : New-Released asset ER
--Bug# 13809564
AND cle.sts_code <> 'ABANDONED'
);
SELECT down_payment_receiver_code
FROM okl_k_lines
WHERE ID = p_kle_id;
SELECT kle_k.id kle_id,
kle_k.down_payment_receiver_code downpymnt_recvr
FROM okl_k_lines kle_k,
okc_k_lines_b kle
where kle_k.id = kle.id
and kle.dnz_chr_id = p_khr_id
--Bug# 15992711 : avsingh - New-Released asset ER
and nvl(kle_k.re_lease_yn,'N') = 'Y' -- re lease flag check
--End Bug# 15992711 : New-Released asset ER
--Bug# 13809564
AND kle.sts_code <> 'ABANDONED';