[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT qte.id,
qte.qst_code,
qte.qtp_code,
qte.quote_number,
qte.khr_id,
qte.partial_yn,
qte.early_termination_yn,
tql.qlt_code
FROM okl_trx_quotes_b qte,
okl_txl_quote_lines_b tql
WHERE tql.id = p_line_id
AND tql.qte_id = qte.id;
OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'calling OKL_TRX_QUOTES_PUB.update_trx_quotes');
OKL_TRX_QUOTES_PUB.update_trx_quotes (
p_api_version => l_api_version,
p_init_msg_list => OKL_API.G_FALSE,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_qtev_rec => lp_quot_rec,
x_qtev_rec => lx_quot_rec,
x_return_status => l_return_status);
OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'calling OKL_TRX_QUOTES_PUB.update_trx_quotes, return status: ' || l_return_status);
SELECT qte.id,
qte.qst_code,
qte.qtp_code,
qte.quote_number,
qte.khr_id,
qte.partial_yn,
qte.early_termination_yn,
tql.qlt_code,
tql.kle_id
FROM okl_trx_quotes_b qte,
okl_txl_quote_lines_b tql
WHERE tql.id = p_line_id
AND tql.qte_id = qte.id;
SELECT kle.name asset_number
FROM okc_k_lines_v kle
WHERE kle.id = p_line_id;
SELECT oal.asset_id asset_id,
oal.asset_number asset_number,
fab.book_type_code book_type_code
FROM OKX_ASSET_LINES_V oal,
FA_BOOKS fab
WHERE oal.parent_line_id = p_kle_id -- fin id
AND oal.asset_id = fab.asset_id
AND fab.date_ineffective IS NULL
AND fab.transaction_header_id_out IS NULL;
SELECT max(th.transaction_date_entered) transaction_date_entered
FROM FA_TRANSACTION_HEADERS th
WHERE th.asset_id = p_asset_id
AND th.book_type_code = p_book;
SELECT calendar_period_close_date INTO p_calendar_period_close_date FROM fa_deprn_periods WHERE book_type_code = get_fa_dtls_rec.book_type_code AND period_close_date is null;
SELECT csi.serial_number,
cle_ib.id
FROM csi_item_instances csi,
okc_k_items cim_ib,
okc_line_styles_b lse_ib,
okc_k_lines_b cle_ib,
okc_line_styles_b lse_inst,
okc_k_lines_b cle_inst,
okc_line_styles_b lse_fin,
okc_k_lines_b cle_fin,
okc_k_headers_b khr -- RMUNJULU 24-JAN-03 2759726 Added
WHERE cle_fin.cle_id is null
-- AND cle_fin.sts_code = 'BOOKED' -- RMUNJULU 24-JAN-03 2759726 Removed
AND cle_fin.chr_id = khr.id -- RMUNJULU 24-JAN-03 2759726 Added
AND cle_fin.sts_code = khr.sts_code -- RMUNJULU 24-JAN-03 2759726 Added
AND cle_fin.chr_id = cle_fin.dnz_chr_id
AND lse_fin.id = cle_fin.lse_id
AND lse_fin.lty_code = 'FREE_FORM1'
AND cle_inst.cle_id = cle_fin.id
AND cle_inst.dnz_chr_id = cle_fin.dnz_chr_id
AND cle_inst.lse_id = lse_inst.id
AND lse_inst.lty_code = 'FREE_FORM2'
AND cle_ib.cle_id = cle_inst.id
AND cle_ib.dnz_chr_id = cle_fin.dnz_chr_id
AND cle_ib.lse_id = lse_ib.id
AND lse_ib.lty_code = 'INST_ITEM'
AND cim_ib.cle_id = cle_ib.id
AND cim_ib.dnz_chr_id = cle_ib.dnz_chr_id
AND cim_ib.object1_id1 = csi.instance_id
AND cim_ib.object1_id2 = '#'
AND cim_ib.jtot_object1_code = 'OKX_IB_ITEM'
AND cle_fin.id = p_asset_line;
SELECT lse.lty_code,
cle.sts_code,
chr.contract_number
FROM okc_k_lines_b cle,
okc_k_headers_b chr,
okc_line_styles_b lse
WHERE cle.id = p_asset_line
AND cle.lse_id = lse.id
AND cle.dnz_chr_id = chr.id;
SELECT OKHV.id,
OTQV.accepted_yn,
OTQV.date_effective_from,
OTQV.date_effective_to,
OTQV.quote_number,
OKHV.contract_number,
OTQV.id qte_id,
OTQV.qtp_code,
OTQV.qst_code, -- RMUNJULU -- 12-DEC-02 Bug # 2484327 added
OTQV.currency_conversion_date, -- RMUNJULU 30-DEC-02 2699412 added
OTQV.currency_code, -- RMUNJULU 30-DEC-02 2699412 added
OTQV.currency_conversion_code, -- RMUNJULU 30-DEC-02 2699412 added
OTQV.partial_yn, -- RMUNJULU 3061751 Added
TRUNC(OTQV.creation_date), -- rmunjulu EDAT Added
OTQV.perdiem_amount, -- rmunjulu LOANS_ENHANCEMENT
OTQV.REPO_QUOTE_INDICATOR_YN -- Bug 6674730
FROM OKL_TRX_QUOTES_V OTQV,
OKL_K_HEADERS_FULL_V OKHV
WHERE OTQV.id = p_id
AND OTQV.khr_id = OKHV.id;
SELECT contract_number
FROM OKL_K_HEADERS_FULL_V K
WHERE K.id = p_khr_id;
SELECT KLE.id kle_id,
KLE.name asset_name,
TQL.id tql_id,
TQL.quote_quantity quote_quantity,
TQL.asset_quantity asset_quantity
FROM OKL_TXL_QUOTE_LINES_V TQL,
OKC_K_LINES_V KLE
WHERE TQL.qte_id = p_qte_id
AND TQL.qlt_code = 'AMCFIA'
AND TQL.kle_id = KLE.id;
SELECT COUNT(TXD.id) ib_lines_count
FROM OKL_TXD_QUOTE_LINE_DTLS TXD
WHERE TXD.tql_id = p_tql_id;
SELECT SEL.id
FROM OKL_STREAMS_V STM,
OKL_STRM_ELEMENTS_V SEL,
OKC_K_HEADERS_V KHR,
OKL_STRM_TYPE_B STY
WHERE KHR.id = p_khr_id
AND KHR.id = STM.khr_id
AND STM.id = SEL.stm_id
AND STM.say_code = 'CURR'
AND STM.active_yn = 'Y'
AND SEL.date_billed IS NULL
AND STM.sty_id = STY.id
AND NVL(STY.billable_yn,'N') = 'Y'
AND TRUNC(SEL.stream_element_date) <= TRUNC(p_eff_from_date)
AND SEL.amount > 0
AND ROWNUM < 2;
SELECT 'Y'
FROM OKC_K_HEADERS_B CHR
WHERE id = p_chr_id
AND EXISTS (
SELECT 1
FROM OKL_STRM_TYPE_B sty,
OKL_STREAMS stm,
OKL_STRM_ELEMENTS ste,
OKL_PROD_STRM_TYPES_V psty,
OKL_K_HEADERS khr
WHERE stm.khr_id = chr.id
AND khr.id = stm.khr_id
AND stm.say_code = 'CURR'
AND stm.active_yn = 'Y'
AND stm.sty_id = sty.id
AND sty.id = psty.sty_id
AND psty.pdt_id = khr.pdt_id
AND psty.accrual_yn = 'Y'
AND stm.id = ste.stm_id
AND ste.stream_element_date <= p_accrue_till_date
AND ste.amount <> 0
AND ste.accrued_yn IS NULL);
SELECT trn.id id,
fnd.meaning,
fnd.description,
TRUNC(trn.creation_date) transaction_date
FROM OKL_TRX_CONTRACTS trn,
FND_LOOKUPS fnd
WHERE trn.khr_id = p_khr_id
--rkuttiya added for 12.1.1 Multi GAAP
AND trn.representation_type = 'PRIMARY'
--
AND trunc(trn.creation_date) >= trunc(p_date)
AND fnd.lookup_type = 'OKL_TCN_TYPE'
AND fnd.lookup_code = trn.tcn_type
AND trn.tcn_type IN ('TMT', -- Termination
'ALT', -- Asset Termination
'EVG' , -- Evergreen --akrangan bug 5354501 fix added 'EVG'
'RVC', -- Reverse
'SPLC', -- Split contract
'TAA', -- Transfer and Assumption
'TRBK', -- Rebook
'PPD' -- Principal Paydown
)
AND trn.tmt_status_code NOT IN ('CANCELED'); -- status --akrangan changed for sla tmt_status_code cr
SELECT trn.id id,
fnd.meaning,
fnd.description,
TRUNC(trn.creation_date) transaction_date
FROM OKL_TRX_CONTRACTS trn,
FND_LOOKUPS fnd
WHERE trn.khr_id = p_khr_id
--rkuttiya added for 12.1.1 Multi GAAP
AND trn.representation_type = 'PRIMARY'
--
AND trunc(trn.creation_date) >= trunc(p_date)
AND fnd.lookup_type = 'OKL_TCN_TYPE'
AND fnd.lookup_code = trn.tcn_type
AND trn.tcn_type IN ('TMT', -- Termination
'ALT', -- Asset Termination
'EVG' , -- Evergreen --akrangan bug 5354501 fix added 'EVG'
'RVC', -- Reverse
'SPLC', -- Split contract
'TAA', -- Transfer and Assumption
'TRBK', -- Rebook
'PPD' -- Principal Paydown
)
AND trn.tmt_status_code NOT IN ('CANCELED','PROCESSED'); -- status --akrangan changed for sla tmt_status_code cr
SELECT tas.id,
fnd.meaning,
fnd.description,
TRUNC(tas.creation_date) transaction_date
FROM OKL_TRX_ASSETS tas,
OKL_TXL_ASSETS_V tal,
FND_LOOKUPS fnd
WHERE tas.id = tal.tas_id
AND tal.dnz_khr_id = p_khr_id
AND trunc(tas.creation_date) >= trunc(p_date)
AND fnd.lookup_type = 'OKL_TRANS_HEADER_TYPE'
AND fnd.lookup_code = tas.tas_type
AND tas.tas_type IN ('ALI') -- Split Asset Transaction
AND tas.tsu_code NOT IN ('CANCELED'); -- status
SELECT tas.id,
fnd.meaning,
fnd.description,
TRUNC(tas.creation_date) transaction_date
FROM OKL_TRX_ASSETS tas,
OKL_TXL_ASSETS_V tal,
FND_LOOKUPS fnd
WHERE tas.id = tal.tas_id
AND tal.dnz_khr_id = p_khr_id
AND trunc(tas.creation_date) >= trunc(p_date)
AND fnd.lookup_type = 'OKL_TRANS_HEADER_TYPE'
AND fnd.lookup_code = tas.tas_type
AND tas.tas_type IN ('ALI') -- Split Asset Transaction
AND tas.tsu_code NOT IN ('CANCELED','PROCESSED'); -- status
SELECT NVL(upper(CANCEL_QUOTES_YN), 'N') CANCEL_QUOTES
FROM OKL_SYSTEM_PARAMS;
SELECT CHR.contract_number
FROM OKC_K_HEADERS_B CHR
WHERE CHR.id = p_service_id;
SELECT NVL(CHK_ACCRUAL_PREVIOUS_MNTH_YN,'N')
FROM OKL_SYSTEM_PARAMS;
SELECT ARS_CODE
FROM OKL_ASSET_RETURNS_B
WHERE kle_id = p_line_id
AND ARS_CODE = 'REPOSSESSED';
SELECT sts_code,
org_id
FROM Okc_K_Headers_b
WHERE id = l_khr_id;
SELECT P.user_concurrent_program_name,
R.request_id request_id
FROM Fnd_Concurrent_Requests R,
Fnd_Concurrent_Programs_VL P
WHERE R.Concurrent_Program_Id = P.Concurrent_program_ID
AND R.Program_Application_ID= P.Application_ID
AND P.concurrent_program_name IN ( -- Following Concurrent Programs
'OKLAGNCALC' --Generate Accruals Master - Streams
,'OKLAGNCALCW' -- Generate Accruals
,'OKL_STREAM_BILLING' -- Process Billable Streams
,'OKL_STREAM_BILLING_MASTER' -- Master Program -- Process Billable Streams
)
AND R.org_id = l_org_id -- check if billing or accruals running for the same org
AND R.phase_code = 'R'; -- Concurrent Program with Phase = 'Running'
SELECT SYSDATE INTO db_sysdate FROM DUAL;
p_control_flag => 'TRMNT_QUOTE_UPDATE',
x_contract_status => lx_contract_status);
OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'calling OKL_TRX_QUOTES_PUB.update_trx_quotes');
OKL_TRX_QUOTES_PUB.update_trx_quotes(
p_api_version => p_api_version,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_qtev_rec => lp_canceled_qtev_rec,
x_qtev_rec => lx_canceled_qtev_rec);
OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'called OKL_TRX_QUOTES_PUB.update_trx_quotes , return status: ' || l_return_status);
-- Please update the Asset Return status to Repossessed for all
-- assets on contract number CONTRACT_NUMBER.
OKL_API.set_message( p_app_name => OKL_API.G_APP_NAME,
p_msg_name => 'OKL_AM_ASSET_NOT_REPO',
p_token1 => 'CONTRACT_NUMBER',
p_token1_value => l_contract_number);
p_source => 'UPDATE',
p_trn_date => l_date_eff_from);
OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'calling OKL_TRX_QUOTES_PUB.update_trx_quotes');
OKL_TRX_QUOTES_PUB.update_trx_quotes(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_qtev_rec => lp_term_rec,
x_qtev_rec => lx_term_rec);
OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'called OKL_TRX_QUOTES_PUB.update_trx_quotes , return status: ' || l_return_status);
SELECT qte.accepted_yn
FROM okl_trx_quotes_v qte
WHERE qte.id = p_qte_id;
SELECT QTE.qst_code
FROM OKL_TRX_QUOTES_V QTE
WHERE QTE.id = p_qte_id;
lv_select_yn VARCHAR2(3);
ln_select_count NUMBER := 0;
SELECT tql.asset_quantity,
tql.quote_quantity,
cle.name,
cle.chr_id -- RMUNJULU 24-JAN-03 2759726 Added
FROM OKL_TXL_QUOTE_LINES_B tql,
OKC_K_LINES_V cle,
OKC_LINE_STYLES_B lse
WHERE tql.id = P_tql_id
AND tql.qlt_code = 'AMCFIA'
AND tql.kle_id = cle.id
--AND cle.sts_code = 'BOOKED'
AND cle.lse_id = lse.id
AND lse.lty_code = G_FIN_LINE_LTY_CODE
AND lse.lse_type = G_TLS_TYPE
AND lse.lse_parent_id IS NULL;
SELECT 1
FROM DUAL
WHERE EXISTS (SELECT '1'
FROM OKC_K_HEADERS_B chr
WHERE chr.id = p_dnz_chr_id
AND scs_code = G_LEASE_SCS_CODE);
SELECT 1
FROM DUAL
WHERE EXISTS (SELECT '1'
FROM OKC_K_LINES_B cle,
OKC_LINE_STYLES_B lse
WHERE cle.id = p_ib_id
AND cle.dnz_chr_id = p_dnz_chr_id
AND cle.lse_id = lse.id
AND lse.lty_code = 'INST_ITEM');
SELECT quote_number
FROM OKL_TRX_QUOTES_B qte
WHERE qte.id = p_qte_id;
OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_qld_tbl(' || i || ').select_yn: ' || p_qld_tbl(i).select_yn);
p_control_flag => 'TRMNT_QUOTE_UPDATE',
x_contract_status => lx_contract_status);
IF upper(nvl(l_qld_tbl(i).select_yn,'N')) NOT in ('Y','N') THEN
OKL_API.set_message(p_app_name => G_APP_NAME,
p_msg_name => G_INVALID_VALUE,
p_token1 => G_COL_NAME_TOKEN,
p_token1_value => 'SELECT_YN');
l_qld_tbl(i).select_yn := upper(nvl(l_qld_tbl(i).select_yn,'N'));
IF l_qld_tbl(i).select_yn = 'Y' THEN
ln_select_count := ln_select_count + 1;
IF ln_select_count <> ln_qte_qty THEN
--AND ln_asset_qty <> ln_qte_qty THEN
-- Asset ASSET_NUMBER is serialized. Quote quantity QUOTE_QUANTITY
-- does not match the number of selected asset units ASSET_UNITS.
OKL_API.set_message(p_app_name => G_APP_NAME,
p_msg_name => G_QTE_QTY_SRL_CNT_ERR,
p_token1 => 'ASSET_NUMBER',
p_token1_value => lv_asset_number,
p_token2 => 'QUOTE_QUANTITY',
p_token2_value => ln_qte_qty,
p_token3 => 'ASSET_UNITS',
p_token3_value => ln_select_count); -- RMUNJULU 2743604
OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_qld_tbl(' || i || ').select_yn: ' || p_qld_tbl(i).select_yn);
IF l_qld_tbl(i).select_yn = 'Y' AND
(l_qld_tbl(i).tqd_id IS NULL OR
l_qld_tbl(i).tqd_id = OKL_API.G_MISS_NUM) THEN
l_tqdv_rec.number_of_units := l_qld_tbl(i).instance_quantity;
ELSIF l_qld_tbl(i).select_yn = 'N' AND
(l_qld_tbl(i).tqd_id IS NOT NULL OR
l_qld_tbl(i).tqd_id <> OKL_API.G_MISS_NUM) THEN
l_tqdv_rec.id := l_qld_tbl(i).tqd_id;
OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'calling OKL_TXD_QTE_LN_DTLS_PUB.delete_txd_qte_ln_dtls');
OKL_TXD_QTE_LN_DTLS_PUB.delete_txd_qte_ln_dtls(
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_tqdv_rec => l_tqdv_rec);
OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'called OKL_TXD_QTE_LN_DTLS_PUB.delete_txd_qte_ln_dtls , return status: ' || x_return_status);
SELECT TQL.kle_id kle_id
FROM OKL_TXL_QUOTE_LINES_V TQL
WHERE TQL.qte_id = p_qte_id
AND TQL.qlt_code = 'AMCFIA';
SELECT QTE.khr_id khr_id
FROM OKL_TRX_QUOTES_V QTE
WHERE QTE.id = p_qte_id;
SELECT try.id
FROM okl_trx_types_v try
WHERE try.name = p_trx_name;
select max(line_number) from OKL_TXL_QUOTE_LINES_B
where qte_id = p_qte_id;
SELECT SYSDATE INTO l_sys_date FROM DUAL;
OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'calling OKL_TXL_QUOTE_LINES_PUB.insert_txl_quote_lines');
OKL_TXL_QUOTE_LINES_PUB.insert_txl_quote_lines(
p_api_version => p_api_version,
p_init_msg_list => G_FALSE,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_tqlv_rec => lp_tqlv_rec,
x_tqlv_rec => lx_tqlv_rec);
OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'called OKL_TXL_QUOTE_LINES_PUB.insert_txl_quote_lines , return status: ' || l_return_status);
PROCEDURE update_quote_line(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_tqlv_rec IN tqlv_rec_type,
x_tqlv_rec OUT NOCOPY tqlv_rec_type) IS
-- RMUNJULU 16-JAN-03 2754574 Added cursor to get quote status
-- Get the quote details
CURSOR get_quote_dtls_csr ( p_tql_id IN NUMBER) IS
SELECT QTE.qst_code,
QTE.id qte_id, -- rmunjulu Sales_Tax_Enhancement
TQL.amount -- rmunjulu Sales_Tax_Enhancement
FROM OKL_TRX_QUOTES_V QTE,
OKL_TXL_QUOTE_LINES_V TQL
WHERE TQL.id = p_tql_id
AND TQL.qte_id = QTE.id;
l_api_name VARCHAR2(30) := 'update_quote_line';
SELECT try.id
FROM okl_trx_types_v try
WHERE try.name = p_trx_name;
l_module_name VARCHAR2(500) := G_MODULE_NAME || 'update_quote_line';
lp_tqlv_rec.program_update_date := null;
OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'calling OKL_TXL_QUOTE_LINES_PUB.update_txl_quote_lines');
OKL_TXL_QUOTE_LINES_PUB.update_txl_quote_lines(
p_api_version => p_api_version,
p_init_msg_list => G_FALSE,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_tqlv_rec => lp_tqlv_rec,
x_tqlv_rec => lx_tqlv_rec);
OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'called OKL_TXL_QUOTE_LINES_PUB.update_txl_quote_lines , return status: ' || l_return_status);
END update_quote_line ;
PROCEDURE update_quote_line(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_tqlv_tbl IN tqlv_tbl_type,
x_tqlv_tbl OUT NOCOPY tqlv_tbl_type) IS
l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
l_api_name VARCHAR2(30) := 'update_quote_line';
l_module_name VARCHAR2(500) := G_MODULE_NAME || 'update_quote_line';
update_quote_line(
p_api_version => p_api_version,
p_init_msg_list => G_FALSE,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_tqlv_rec => lp_tqlv_tbl(i),
x_tqlv_rec => lx_tqlv_tbl(i));
OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'called update_quote_line , return status: ' || l_return_status);
END update_quote_line ;
SELECT TQD.id
FROM OKL_TXD_QUOTE_LINE_DTLS TQD
WHERE TQD.tql_id = p_tql_id;
SELECT QTE.QUOTE_NUMBER QUOTE_NUMBER,
QTE.QST_CODE QST_CODE,
QTE.QTP_CODE QTP_CODE,
TQL.ID TQL_ID,
NULL TQD_ID,
TQL.ASSET_QUANTITY ASSET_QUANTITY,
TQL.QUOTE_QUANTITY QUOTE_QUANTITY,
CLE_IB.ID IB_LINE_ID,
CLE_FIN.ID FIN_LINE_ID,
CLE_FIN.DNZ_CHR_ID DNZ_CHR_ID,
CSI.SERIAL_NUMBER SERIAL_NUMBER,
CSI.QUANTITY INSTANCE_QUANTITY,
CSI.INSTANCE_NUMBER INSTANCE_NUMBER,
CLET_FIN.NAME ASSET_NUMBER,
CLET_FIN.ITEM_DESCRIPTION ASSET_DESCRIPTION,
SUBSTR(ARP_ADDR_LABEL_PKG.FORMAT_ADDRESS(NULL,
HL.ADDRESS1,
HL.ADDRESS2,
HL.ADDRESS3,
HL.ADDRESS4,
HL.CITY,
HL.COUNTY,
HL.STATE,
HL.PROVINCE,
HL.POSTAL_CODE,
NULL,
HL.COUNTRY,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
'N',
'N',
80,1,1),1,80) LOCATION_DESCRIPTION,
QTE.ID QTE_ID
FROM HZ_LOCATIONS HL,
HZ_PARTY_SITES HPS,
HZ_PARTY_SITE_USES HPSU,
CSI_ITEM_INSTANCES CSI,
OKC_K_ITEMS CIM_IB,
OKC_LINE_STYLES_B LSE_IB,
OKC_K_LINES_B CLE_IB,
OKC_LINE_STYLES_B LSE_INST,
OKC_K_LINES_B CLE_INST,
OKC_LINE_STYLES_B LSE_FIN,
OKL_TXL_QUOTE_LINES_B TQL,
OKL_TRX_QUOTES_B QTE,
OKC_K_LINES_TL CLET_FIN,
OKC_K_LINES_B CLE_FIN
WHERE CLE_FIN.CLE_ID IS NULL
AND CLE_FIN.CHR_ID = CLE_FIN.DNZ_CHR_ID
AND CLE_FIN.ID = CLET_FIN.ID
AND CLET_FIN.LANGUAGE = USERENV('LANG')
AND CLE_FIN.ID = TQL.KLE_ID
AND TQL.QLT_CODE = 'AMCFIA'
AND LSE_FIN.ID = CLE_FIN.LSE_ID
AND LSE_FIN.LTY_CODE = 'FREE_FORM1'
AND CLE_INST.CLE_ID = CLE_FIN.ID
AND CLE_INST.LSE_ID = LSE_INST.ID
AND LSE_INST.LTY_CODE = 'FREE_FORM2'
AND CLE_IB.CLE_ID = CLE_INST.ID
AND CLE_IB.LSE_ID = LSE_IB.ID
AND LSE_IB.LTY_CODE = 'INST_ITEM'
AND CIM_IB.CLE_ID = CLE_IB.ID
AND CIM_IB.OBJECT1_ID1 = CSI.INSTANCE_ID
AND CIM_IB.OBJECT1_ID2 = '#'
AND CIM_IB.JTOT_OBJECT1_CODE = 'OKX_IB_ITEM'
AND CSI.INSTALL_LOCATION_ID = HPSU.PARTY_SITE_ID
AND HPSU.SITE_USE_TYPE = 'INSTALL_AT'
AND HPSU.PARTY_SITE_ID = HPS.PARTY_SITE_ID
AND HPS.LOCATION_ID = HL.LOCATION_ID
AND TQL.QTE_ID = QTE.ID
AND TQL.ID = p_tql_id;
SELECT QTE.QUOTE_NUMBER QUOTE_NUMBER,
QTE.QST_CODE QST_CODE,
QTE.QTP_CODE QTP_CODE,
TQL.ID TQL_ID,
TQD.ID TQD_ID,
TQL.ASSET_QUANTITY ASSET_QUANTITY,
TQL.QUOTE_QUANTITY QUOTE_QUANTITY,
CLE_IB.ID IB_LINE_ID,
CLE_FIN.ID FIN_LINE_ID,
CLE_FIN.DNZ_CHR_ID DNZ_CHR_ID,
CSI.SERIAL_NUMBER SERIAL_NUMBER,
CSI.QUANTITY INSTANCE_QUANTITY,
CSI.INSTANCE_NUMBER INSTANCE_NUMBER,
CLET_FIN.NAME ASSET_NUMBER,
CLET_FIN.ITEM_DESCRIPTION ASSET_DESCRIPTION,
SUBSTR(ARP_ADDR_LABEL_PKG.FORMAT_ADDRESS(NULL,
HL.ADDRESS1,
HL.ADDRESS2,
HL.ADDRESS3,
HL.ADDRESS4,
HL.CITY,
HL.COUNTY,
HL.STATE,
HL.PROVINCE,
HL.POSTAL_CODE,
NULL,
HL.COUNTRY,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
'N',
'N',
80,1,1),1,80) LOCATION_DESCRIPTION,
QTE.ID QTE_ID
FROM HZ_LOCATIONS HL,
HZ_PARTY_SITES HPS,
HZ_PARTY_SITE_USES HPSU,
CSI_ITEM_INSTANCES CSI,
OKC_K_ITEMS CIM_IB,
OKC_LINE_STYLES_B LSE_IB,
OKC_K_LINES_B CLE_IB,
OKC_LINE_STYLES_B LSE_INST,
OKC_K_LINES_B CLE_INST,
OKC_LINE_STYLES_B LSE_FIN,
OKL_TXL_QUOTE_LINES_B TQL,
OKL_TRX_QUOTES_B QTE,
OKC_K_LINES_TL CLET_FIN,
OKC_K_LINES_B CLE_FIN,
OKL_TXD_QUOTE_LINE_DTLS TQD
WHERE CLE_FIN.CLE_ID IS NULL
AND CLE_FIN.CHR_ID = CLE_FIN.DNZ_CHR_ID
AND CLE_FIN.ID = CLET_FIN.ID
AND CLET_FIN.LANGUAGE = USERENV('LANG')
AND CLE_FIN.ID = TQL.KLE_ID
AND TQL.QLT_CODE = 'AMCFIA'
AND LSE_FIN.ID = CLE_FIN.LSE_ID
AND LSE_FIN.LTY_CODE = 'FREE_FORM1'
AND CLE_INST.CLE_ID = CLE_FIN.ID
AND CLE_INST.LSE_ID = LSE_INST.ID
AND LSE_INST.LTY_CODE = 'FREE_FORM2'
AND CLE_IB.CLE_ID = CLE_INST.ID
AND CLE_IB.LSE_ID = LSE_IB.ID
AND LSE_IB.LTY_CODE = 'INST_ITEM'
AND CIM_IB.CLE_ID = CLE_IB.ID
AND CIM_IB.OBJECT1_ID1 = CSI.INSTANCE_ID
AND CIM_IB.OBJECT1_ID2 = '#'
AND CIM_IB.JTOT_OBJECT1_CODE = 'OKX_IB_ITEM'
AND CSI.INSTALL_LOCATION_ID = HPSU.PARTY_SITE_ID
AND HPSU.SITE_USE_TYPE = 'INSTALL_AT'
AND HPSU.PARTY_SITE_ID = HPS.PARTY_SITE_ID
AND HPS.LOCATION_ID = HL.LOCATION_ID
AND TQL.QTE_ID = QTE.ID
AND TQD.TQL_ID = TQL.ID
AND TQD.KLE_ID = CLE_IB.ID
AND TQL.ID = p_tql_id;
PROCEDURE delete_quote_line(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_tqlv_rec IN tqlv_rec_type) IS
-- Get the quote details
CURSOR get_quote_dtls_csr ( p_tql_id IN NUMBER) IS
SELECT QTE.qst_code,
QTE.id qte_id -- rmunjulu Sales_Tax_Enhancement
FROM OKL_TRX_QUOTES_V QTE,
OKL_TXL_QUOTE_LINES_V TQL
WHERE TQL.id = p_tql_id
AND TQL.qte_id = QTE.id;
l_api_name VARCHAR2(30) := 'delete_quote_line';
l_module_name VARCHAR2(500) := G_MODULE_NAME || 'delete_quote_line';
OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'calling OKL_TXL_QUOTE_LINES_PUB.delete_txl_quote_lines');
OKL_TXL_QUOTE_LINES_PUB.delete_txl_quote_lines(
p_api_version => p_api_version,
p_init_msg_list => G_FALSE,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_tqlv_rec => lp_tqlv_rec);
OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'called OKL_TXL_QUOTE_LINES_PUB.delete_txl_quote_lines , return status: ' || l_return_status);
END delete_quote_line ;
PROCEDURE delete_quote_line(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_tqlv_tbl IN tqlv_tbl_type) IS
l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
l_api_name VARCHAR2(30) := 'delete_quote_line';
l_module_name VARCHAR2(500) := G_MODULE_NAME || 'delete_quote_line';
delete_quote_line(
p_api_version => p_api_version,
p_init_msg_list => G_FALSE,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_tqlv_rec => lp_tqlv_tbl(i));
OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'called delete_quote_line , return status: ' || l_return_status);
END delete_quote_line ;