[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT okl_wf_item_s.nextval
FROM DUAL;
l_parameter_list.DELETE;
SELECT okl_wf_item_s.nextval
FROM DUAL;
l_parameter_list.DELETE;
SELECT kle.id asset_id
FROM OKC_K_LINES_B kle,
OKC_LINE_STYLES_B LSEB,
OKL_TRX_QUOTES_B qte
WHERE kle.chr_id = qte.khr_id
AND kle.STS_CODE <> 'ABANDONED'
AND KLE.LSE_ID = LSEB.ID
AND LSEB.LTY_CODE = 'FREE_FORM1'
AND qte.id = p_qte_id;
SELECT kle.id kle_id
FROM OKL_AM_ASSET_LINES_UV kle
WHERE kle.qte_id = p_qte_id;
SELECT COUNT(KLE.id) CONTRACT_ASSETS
FROM OKC_K_LINES_B kle,
OKC_LINE_STYLES_B LSEB,
OKL_TRX_QUOTES_B qte
WHERE kle.chr_id = qte.khr_id
AND kle.STS_CODE <> 'ABANDONED'
AND KLE.LSE_ID = LSEB.ID
AND LSEB.LTY_CODE = 'FREE_FORM1'
AND qte.id = p_qte_id;
SELECT COUNT(KLE.id) QUOTE_ASSETS
FROM OKL_AM_ASSET_LINES_UV kle
WHERE kle.qte_id = p_qte_id;
SELECT K.id,
K.contract_number,
Q.last_updated_by,
Q.created_by
FROM OKL_TRX_QUOTES_V Q,
OKL_K_HEADERS_FULL_V K
WHERE Q.khr_id = K.id
AND Q.id = p_quote_id;
SELECT TO_CHAR(sysdate, 'MM-DD-YYYY') system_date,
TRQ.quote_number quote_number,
TRQ.date_effective_to effective_to,
QTE.amount quote_total,
OKL_AM_UTIL_PVT.get_lookup_meaning('OKL_QUOTE_TYPE',TRQ.qtp_code,'N') quote_type,
TRQ.creation_date quote_creation_date,
OKL_AM_UTIL_PVT.get_lookup_meaning('OKL_QUOTE_REASON',TRQ.qrs_code,'N') quote_reason,
OKL_AM_UTIL_PVT.get_chr_currency(TRQ.khr_id) currency,
TRQ.comments comments,
KHR.contract_number contract_number,
TRQ.last_updated_by last_updated_by
FROM OKC_K_HEADERS_B KHR,
OKL_TRX_QUOTES_V TRQ,
(SELECT SUM(NVL(amount,0)) amount, qte_id FROM OKL_TXL_QUOTE_LINES_V GROUP BY qte_id) QTE
WHERE KHR.id = TRQ.khr_id
AND QTE.qte_id = TRQ.id
AND TRQ.id = p_id;
l_updated_by NUMBER;
p_user_id => l_pre_proceeds_rec.last_updated_by,
x_name => l_requestor,
x_description => l_description);
avalue => l_pre_proceeds_rec.last_updated_by);
SELECT okhv.contract_number contract_number,
clet.item_description asset_description,
clet.name asset_number,
oalv.serial_number serial_number,
oalv.model_number model_number
FROM okc_k_lines_b cleb,
okc_k_lines_tl clet,
okx_asset_lines_v oalv,
okc_k_headers_all_b okhv,
okc_line_styles_b lsev,
okl_trx_quotes_all_b trq
WHERE cleb.id = oalv.parent_line_id(+)
AND cleb.chr_id = okhv.id
AND cleb.lse_id = lsev.id
AND lsev.lty_code = 'FREE_FORM1'
AND cleb.chr_id = trq.khr_id
AND cleb.id = clet.id
AND clet.LANGUAGE = userenv('LANG')
AND cleb.sts_code <> 'ABANDONED'
AND trq.id = p_qte_id;
SELECT otqv.quote_number quote_number,
clet.item_description asset_description,
clet.name asset_number,
oalv.serial_number serial_number,
oalv.model_number model_number
FROM okl_trx_quotes_b otqv,
okl_txl_qte_lines_all_b tql,
okc_k_lines_b cleb,
okc_k_lines_tl clet,
okx_asset_lines_v oalv,
okc_line_styles_b lsev
WHERE otqv.id = p_qte_id
AND tql.kle_id = cleb.id
AND otqv.id = tql.qte_id
AND tql.qlt_code = 'AMCFIA'
AND cleb.id = oalv.parent_line_id(+)
AND cleb.lse_id = lsev.id
AND lsev.lty_code = 'FREE_FORM1'
AND cleb.id = clet.id
AND clet.LANGUAGE = userenv('LANG')
AND cleb.sts_code <> 'ABANDONED';
SELECT TO_CHAR(sysdate, 'MM-DD-YYYY') system_date,
TRQ.quote_number quote_number,
TRQ.date_effective_to effective_to,
QTE.amount quote_total,
OKL_AM_UTIL_PVT.get_lookup_meaning('OKL_QUOTE_TYPE',TRQ.qtp_code,'N') quote_type,
TRQ.creation_date quote_creation_date,
OKL_AM_UTIL_PVT.get_lookup_meaning('OKL_QUOTE_REASON',TRQ.qrs_code,'N') quote_reason,
OKL_AM_UTIL_PVT.get_chr_currency(TRQ.khr_id) currency,
KHR.contract_number contract_number,
TRQ.comments comments,
TRQ.last_updated_by last_updated_by
FROM OKC_K_HEADERS_B KHR,
OKL_TRX_QUOTES_V TRQ,
(SELECT SUM(NVL(amount,0)) amount, qte_id FROM OKL_TXL_QUOTE_LINES_V GROUP BY qte_id) QTE
WHERE KHR.id = TRQ.khr_id
AND QTE.qte_id = TRQ.id
AND TRQ.id = p_id;
SELECT TO_CHAR(SYSDATE, 'DD-MON-RRRR') SYSTEM_DATE,
OTQ.QUOTE_NUMBER QUOTE_NUMBER,
TO_CHAR(OTQ.DATE_EFFECTIVE_TO, 'DD-MON-RRRR') EFFECTIVE_TO,
OKL_ACCOUNTING_UTIL.format_amount(SUM(NVL(OTL.AMOUNT,0)),OKL_AM_UTIL_PVT.get_chr_currency(OTQ.KHR_ID))||' '||OKL_AM_UTIL_PVT.get_chr_currency(OTQ.KHR_ID) QUOTE_TOTAL,
OKL_AM_UTIL_PVT.get_lookup_meaning('OKL_QUOTE_REASON',OTQ.qrs_code,'N')QUOTE_REASON ,
OKL_AM_UTIL_PVT.get_lookup_meaning('OKL_QUOTE_TYPE',OTQ.qtp_code,'N') QUOTE_TYPE,
TO_CHAR(OTQ.CREATION_DATE, 'DD-MON-RRRR') QUOTE_CREATION_DATE,
KHR.CONTRACT_NUMBER CONTRACT_NUMBER,
OTQ.comments COMMENTS
FROM OKL_TRX_QUOTES_V OTQ,
OKL_TXL_QUOTE_LINES_B OTL,
OKC_K_HEADERS_V KHR
WHERE OTQ.ID = p_qte_id
AND OTQ.ID = OTL.QTE_ID
AND KHR.ID = OTQ.KHR_ID
GROUP BY TO_CHAR(SYSDATE, 'DD-MON-RRRR'),
OTQ.QUOTE_NUMBER,
OTQ.DATE_EFFECTIVE_TO,
OTQ.QST_CODE,
OTQ.CREATION_DATE,
OTQ.QRS_CODE,
KHR.CONTRACT_NUMBER,
OTQ.KHR_ID,
OTQ.QTP_CODE,
OTQ.COMMENTS;
SELECT qtp_code,
qrs_code
FROM OKL_TRX_QUOTES_V
WHERE id = p_qte_id;
SELECT SYSDATE INTO l_termination_date FROM DUAL;
l_term_rec.p_control_flag := 'TRMNT_QUOTE_UPDATE';
SELECT COUNT(OKLV.id)
FROM OKC_K_LINES_V OKLV,
OKC_LINE_STYLES_V OLSV
WHERE OKLV.chr_id = p_khr_id
AND OKLV.lse_id = OLSV.id
AND OLSV.lty_code = 'SOLD_SERVICE'
AND OKLV.end_date > SYSDATE;
SELECT OKLB.id line_id,
OKPB.object1_id1 party_id
FROM OKC_K_PARTY_ROLES_B OKPB,
OKC_K_LINES_B OKLB,
OKC_LINE_STYLES_B OLSB,
FND_LOOKUPS FNDV
WHERE OKLB.chr_id = p_khr_id
AND OKLB.id > NVL(p_current_rec_id,0)
AND OKPB.dnz_chr_id = OKLB.dnz_chr_id
AND OKPB.cle_id = OKLB.id
AND OKLB.lse_id = OLSB.id
AND OLSB.lty_code = 'SOLD_SERVICE'
AND OKLB.end_date > SYSDATE
AND FNDV.lookup_type = 'OKC_ROLE'
AND OKPB.RLE_CODE = FNDV.lookup_code
ORDER BY OKLB.id ASC;
SELECT hzp.email_address email
FROM hz_parties hzp
WHERE hzp.party_id = p_recipient_id;
SELECT nvl(ppf.email_address , fu.email_address) email
FROM fnd_user fu,
per_people_f ppf
WHERE fu.employee_id = ppf.person_id (+)
AND fu.user_id = c_agent_id;
SELECT a.LAST_UPDATED_BY LAST_UPDATED_BY
FROM OKL_TXL_QUOTE_LINES_V a
WHERE a.qte_id = to_number(p_qte_id);
select party_id
from ap_suppliers
where vendor_id = p_vendor_id;
SELECT khr_id, qtp_code
FROM OKL_TRX_QUOTES_B
WHERE id = c_id;
SELECT qp.party_object1_id1 recipient_id,
qp.party_jtot_object1_code recipient_code,
qp.id party_id,
qp.email_address email_id
FROM okl_quote_parties qp
WHERE qp.qte_id= p_qte_id
AND qp.id> NVL(p_current_party_id, 0)
AND qp.qpt_code LIKE 'RECIPIENT%'
ORDER BY qp.id ASC;
SELECT a.last_updated_by last_update_by
FROM okl_txl_quote_lines_v a
WHERE a.qte_id = p_quote_id;
SELECT nvl(ppf.email_address , fu.email_address) email
FROM fnd_user fu,
per_people_f ppf
WHERE fu.employee_id = ppf.person_id (+)
AND fu.user_id = p_requestor_id;
SELECT OKLV.id cle_id
FROM OKC_K_LINES_V OKLV,
OKC_LINE_STYLES_V OLSV
WHERE OKLV.chr_id = p_khr_id
AND OKLV.id > NVL(p_current_asset_id , 0)
AND OKLV.lse_id = OLSV.id
AND OLSV.lty_code = 'FREE_FORM1'
ORDER BY OKLV.id ASC;
SELECT hzp.email_address email
FROM hz_parties hzp
WHERE hzp.party_id = to_number(p_recipient_id);--ansethur 26-jul-2007 XMLP added to_number
SELECT nvl(ppf.email_address , fu.email_address) email
FROM fnd_user fu,
per_people_f ppf
WHERE fu.employee_id = ppf.person_id (+)
AND fu.user_id = c_agent_id;
SELECT a.LAST_UPDATED_BY LAST_UPDATED_BY
FROM OKL_TXL_QUOTE_LINES_V a
WHERE a.qte_id = p_qte_id;
SELECT K.id,
K.contract_number,
Q.last_updated_by
FROM OKL_K_HEADERS_FULL_V K,
OKL_TRX_QUOTES_V Q
WHERE K.id = Q.khr_id
AND Q.id = p_quote_id;
SELECT SUM(amount) total_amount,
kle_id kle_id
FROM OKL_TXL_QUOTE_LINES_V
WHERE qte_id = p_qte_id
AND qlt_code <> 'AMCTAX'
GROUP BY kle_id;
SELECT nvl(amount,0) amount, kle_id -- SECHAWLA 11-MAR-03 : nvl the amount
FROM OKL_TXL_QUOTE_LINES_V
WHERE qte_id = p_qte_id
AND qlt_code = 'AMBSPR' ;
SELECT DISTINCT kle_id
FROM OKL_TXL_QUOTE_LINES_V
WHERE qte_id = p_qte_id;
SELECT art_id
FROM OKL_TRX_QUOTES_B
WHERE id = cp_qte_id;
SELECT legal_entity_id
FROM OKL_ASSET_RETURNS_ALL_B
WHERE id = cp_art_id;
PROCEDURE update_asset_return_status(
itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funcmode IN VARCHAR2,
resultout OUT NOCOPY VARCHAR2) IS
-- Cursor to get the sum of the amounts of all quote lines for the quote ( for a asset)
CURSOR l_return_csr ( p_qte_id IN NUMBER) IS
SELECT QLT.kle_id kle_id,
RET.id ret_id
FROM OKL_ASSET_RETURNS_V ret,
OKL_TXL_QUOTE_LINES_V qlt
WHERE RET.kle_id = QLT.kle_id
AND QLT.qte_id = p_qte_id
AND QLT.qlt_code <> 'AMCTAX'
GROUP BY QLT.kle_id, RET.id;
L_MODULE_NAME VARCHAR2(500) := G_MODULE_NAME||'update_asset_return_status';
OKL_ASSET_RETURNS_PUB.update_asset_returns (
p_api_version => l_api_version,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => lx_msg_count,
x_msg_data => lx_msg_data,
p_artv_rec => lp_artv_rec,
x_artv_rec => lx_artv_rec);
'after call to OKL_ASSET_RETURNS_PUB.update_asset_returns :'||l_return_status);
WF_CORE.context('OKL_AM_QUOTES_WF' , 'update_asset_return_status',
itemtype, itemkey, actid, funcmode);
WF_CORE.context('OKL_AM_QUOTES_WF' , 'update_asset_return_status',
itemtype, itemkey, actid, funcmode);
END update_asset_return_status;
SELECT tai.id
FROM OKL_TRX_AR_INVOICES_V tai
WHERE tai.qte_id = p_qte_id;
SELECT SUM(amount)
FROM OKL_TXL_QUOTE_LINES_V
WHERE qte_id = p_qte_id;
SELECT qlt.kle_id kle_id
FROM okl_txl_quote_lines_b qlt,
fnd_lookups flo
WHERE qlt.qte_id = p_qte_id
AND qlt.amount NOT IN (OKL_API.G_MISS_NUM, 0)
AND flo.lookup_type = 'OKL_QUOTE_LINE_TYPE'
AND flo.lookup_code = qlt.qlt_code
AND qlt.qlt_code NOT IN (
'AMCFIA', -- Used to save quote assets, not amounts
'AMCTAX', -- Estimated tax, AR will recalculate tax
'AMYOUB') -- Outstanding balances are already billed
AND ROWNUM = 1;
SELECT qlt.kle_id kle_id
FROM okl_txl_quote_lines_b qlt,
fnd_lookups flo
WHERE qlt.qte_id = p_qte_id
AND qlt.amount NOT IN (OKL_API.G_MISS_NUM, 0)
AND flo.lookup_type = 'OKL_QUOTE_LINE_TYPE'
AND flo.lookup_code = qlt.qlt_code
AND qlt.qlt_code NOT IN (
'AMCFIA', -- Used to save quote assets, not amounts
'AMCTAX', -- Estimated tax, AR will recalculate tax
'AMYOUB', -- Outstanding balances are already billed
'BILL_ADJST') -- Estimated Billing Adjustment
AND ROWNUM = 1;
SELECT nvl(qte.partial_yn,'N') partial_yn
,qte.khr_id khr_id -- gboomina added for bug#5265083
FROM okl_trx_quotes_v qte
WHERE qte.id = p_qte_id;
PROCEDURE update_quote_status(
itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funcmode IN VARCHAR2,
resultout OUT NOCOPY VARCHAR2) IS
l_transaction_id VARCHAR2(2000);
SELECT tmt_status_code
FROM okl_trx_contracts trx
WHERE trx.qte_id = p_qte_id;
L_MODULE_NAME VARCHAR2(500) := G_MODULE_NAME||'update_quote_status';
OKL_TRX_QUOTES_PUB.update_trx_quotes (
p_api_version => l_api_version,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => lx_msg_count,
x_msg_data => lx_msg_data,
p_qtev_rec => lp_qtev_rec,
x_qtev_rec => lx_qtev_rec);
'after call to OKL_TRX_QUOTES_PUB.update_trx_quotes :'||l_return_status);
WF_CORE.context('OKL_AM_QUOTES_WF' , 'update_quote_status',
itemtype, itemkey, actid, funcmode);
WF_CORE.context('OKL_AM_QUOTES_WF' , 'update_quote_status',
itemtype, itemkey, actid, funcmode);
END update_quote_status;
SELECT a.LAST_UPDATED_BY LAST_UPDATED_BY,
a.QTE_ID QTE_ID,
b.PO_PARTY_ID1 QP_PARTY_ID,
decode(b.po_party_object, 'OKX_OPERUNIT', 'O', 'OKX_PARTY', 'P', 'OKX_VENDOR', 'V') party_type
FROM OKL_TXL_QUOTE_LINES_V a,
OKL_AM_QUOTE_PARTIES_UV b
WHERE a.qte_id = to_number(p_qte_id)
-- and b.qp_role_code = 'RECIPIENT'
and b.quote_id = a.qte_id;
SELECT hzp.email_address email
FROM hz_parties hzp
WHERE hzp.party_id = p_recipient_id;
SELECT nvl(ppf.email_address , fu.email_address) email
FROM fnd_user fu,
per_people_f ppf
WHERE fu.employee_id = ppf.person_id (+)
AND fu.user_id = c_agent_id;
, p_user_id => l_quote_rec.last_updated_by
, x_name => l_user_name
, x_description => l_name);
IF l_quote_rec.last_updated_by IS NOT NULL THEN
wf_engine.SetItemAttrNumber ( itemtype=> itemtype,
itemkey => itemkey,
aname => 'CREATED_BY',
avalue => l_quote_rec.last_updated_by);
OPEN c_agent_csr(l_quote_rec.last_updated_by);
SELECT to_char(sysdate, 'DD-MON-RRRR') system_date,
OTQ.quote_number quote_number,
to_char(OTQ.date_effective_to, 'DD-MON-RRRR') effective_to,
okl_accounting_util.format_amount(SUM(NVL(OTL.AMOUNT,0)),okl_am_util_pvt.get_chr_currency(OTQ.KHR_ID))||' '||okl_am_util_pvt.get_chr_currency(OTQ.KHR_ID) QUOTE_TOTAL,
OKL_AM_UTIL_PVT.get_lookup_meaning('OKL_QUOTE_REASON',OTQ.qrs_code,'N')QUOTE_REASON ,
OKL_AM_UTIL_PVT.get_lookup_meaning('OKL_QUOTE_TYPE',OTQ.qtp_code,'N') QUOTE_TYPE,
to_char(OTQ.creation_date, 'DD-MON-RRRR') quote_creation_date,
nvl2 (qp.cpl_id, okl_am_util_pvt.get_jtf_object_name (pr.jtot_object1_code, pr.object1_id1, pr.object1_id2), okl_am_util_pvt.get_jtf_object_name (qp.party_jtot_object1_code, qp.party_object1_id1,qp.party_object1_id2)) recipient_name,
to_char(qp.date_sent, 'DD-MON-RRRR') recipient_date,
AD.CONTRACT_NUMBER CONTRACT_NUMBER,
OTQ.COMMENTS COMMENTS,
OTQ.LAST_UPDATED_BY LAST_UPDATED_BY,
OTQ.KHR_ID KHR_ID
FROM OKL_TRX_QUOTES_V OTQ,
OKL_TXL_QUOTE_LINES_B OTL,
OKL_QUOTE_PARTIES QP,
OKC_K_PARTY_ROLES_V PR,
OKC_K_HEADERS_ALL_B AD
WHERE OTQ.ID = c_id
AND pr.id (+) = qp.cpl_id
AND ad.org_id = otq.org_id
AND OTQ.ID = OTL.QTE_ID
AND AD.ID = OTQ.KHR_ID
AND OTQ.ID = qp.qte_id
-- AND OTQ.PARTIAL_YN = 'Y'
AND qp.qpt_code = 'RECIPIENT'
AND otl.org_id = otq.org_id
GROUP BY to_char(sysdate, 'MM-DD-YYYY'),
OTQ.quote_number,
OTQ.date_effective_to,
OTQ.QST_CODE,
OTQ.creation_date,
OTQ.qrs_code,
OTQ.qtp_code,
nvl2 (qp.cpl_id, okl_am_util_pvt.get_jtf_object_name (pr.jtot_object1_code, pr.object1_id1, pr.object1_id2), okl_am_util_pvt.get_jtf_object_name (qp.party_jtot_object1_code, qp.party_object1_id1,qp.party_object1_id2)),
qp.date_sent,
AD.CONTRACT_NUMBER,
OTQ.COMMENTS,
OTQ.LAST_UPDATED_BY,
OTQ.KHR_ID;
SELECT CLEV.ITEM_DESCRIPTION ASSET_DESCRIPTION ,
OKHV.CONTRACT_NUMBER CONTRACT_NUMBER ,
CLEV.NAME ASSET_NUMBER ,
OALV.SERIAL_NUMBER SERIAL_NUMBER,
OALV.MODEL_NUMBER MODEL_NUMBER
FROM OKL_K_LINES_FULL_V CLEV,
OKX_ASSET_LINES_V OALV,
OKC_K_HEADERS_V OKHV,
OKC_LINE_STYLES_V LSEV,
FA_CATEGORIES_VL FAC ,
OKL_AM_ASSET_LINES_UV AL
WHERE CLEV.ID = OALV.PARENT_LINE_ID
AND CLEV.CHR_ID = OKHV.ID
AND CLEV.LSE_ID = LSEV.ID
AND LSEV.LTY_CODE = 'FREE_FORM1'
AND AL.ID = CLEV.ID
AND FAC.CATEGORY_ID (+) = OALV.DEPRECIATION_CATEGORY
AND AL.QTE_ID = c_qte_id;
l_updated_by NUMBER;
l_updated_by := l_quote_rec.last_updated_by;
okl_qte_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_qtev_rec => p_qtev_rec,
x_qtev_rec => x_qtev_rec);
'after call to okl_qte_pvt.update_row :'||x_return_status);
SELECT TO_CHAR(SYSDATE, 'DD-MON-RRRR') SYSTEM_DATE,
OTQ.QUOTE_NUMBER QUOTE_NUMBER,
to_char(OTQ.DATE_EFFECTIVE_TO, 'DD-MON-RRRR') EFFECTIVE_TO,
okl_accounting_util.format_amount(SUM(NVL(OTL.AMOUNT,0)),okl_am_util_pvt.get_chr_currency(OTQ.KHR_ID))||' '||okl_am_util_pvt.get_chr_currency(OTQ.KHR_ID) QUOTE_TOTAL,
OKL_AM_UTIL_PVT.get_lookup_meaning('OKL_QUOTE_REASON',OTQ.qrs_code,'N')QUOTE_REASON ,
OKL_AM_UTIL_PVT.get_lookup_meaning('OKL_QUOTE_TYPE',OTQ.qtp_code,'N') QUOTE_TYPE,
to_char(OTQ.CREATION_DATE, 'DD-MON-RRRR') QUOTE_CREATION_DATE,
AD.CONTRACT_NUMBER CONTRACT_NUMBER,
OTQ.COMMENTS COMMENTS,
OTQ.LAST_UPDATED_BY LAST_UPDATED_BY,
OTQ.KHR_ID KHR_ID
,OTQ.gain_loss gain_loss -- Added for bug 12802662
FROM OKL_TRX_QUOTES_V OTQ,
OKL_TXL_QUOTE_LINES_B OTL,
OKC_K_HEADERS_V AD
WHERE OTQ.ID = c_id
AND OTQ.ID = OTL.QTE_ID
AND AD.ID = OTQ.KHR_ID
GROUP BY TO_CHAR(SYSDATE, 'DD-MON-RRRR'),
OTQ.QUOTE_NUMBER,
OTQ.DATE_EFFECTIVE_TO,
OTQ.QST_CODE,
OTQ.CREATION_DATE,
OTQ.QRS_CODE,
AD.CONTRACT_NUMBER,
OTQ.COMMENTS,
OTQ.LAST_UPDATED_BY, OTQ.KHR_ID, OTQ.qtp_code
,OTQ.gain_loss; -- Added for bug 12802662
SELECT a.last_updated_by, a.quote_number, count(*) recs
FROM OKL_TRX_QUOTES_B a,
OKL_QUOTE_PARTIES b
WHERE a.id = p_qte_id
and a.qst_code in ('DRAFTED', 'REJECTED')
and b.qte_id (+) = a.id
GROUP BY a.last_updated_by, a.quote_number;
l_last_updated_by NUMBER;
SELECT a.LAST_UPDATED_BY LAST_UPDATED_BY,
a.QTE_ID QTE_ID,
b.PO_PARTY_ID1 QP_PARTY_ID,
decode(b.po_party_object, 'OKX_OPERUNIT', 'O', 'OKX_PARTY', 'P', 'OKX_VENDOR', 'V') party_type
FROM OKL_TXL_QUOTE_LINES_V a,
OKL_AM_QUOTE_PARTIES_UV b
WHERE a.qte_id = to_number(p_qte_id)
-- and b.qp_role_code = 'RECIPIENT'
and b.quote_id = a.qte_id;
SELECT hzp.email_address email
FROM hz_parties hzp
WHERE hzp.party_id = p_recipient_id;
SELECT nvl(ppf.email_address , fu.email_address) email
FROM fnd_user fu,
per_people_f ppf
WHERE fu.employee_id = ppf.person_id (+)
AND fu.user_id = c_agent_id;
okl_qte_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_qtev_rec => p_qtev_rec,
x_qtev_rec => x_qtev_rec);
'after call to okl_qte_pvt.update_row :'||x_return_status);
FETCH get_quote_csr INTO l_last_updated_by, l_quote_number, l_current_party;
, p_user_id => l_last_updated_by
, x_name => l_user_name
, x_description => l_name);
IF l_last_updated_by IS NOT NULL AND l_user_name IS NOT NULL THEN
wf_engine.SetItemAttrText ( itemtype=> itemtype,
itemkey => itemkey,
aname => 'REQUESTER',
avalue => l_user_name);
avalue => l_last_updated_by);
okl_qte_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_qtev_rec => p_qtev_rec,
x_qtev_rec => x_qtev_rec);
'after call to okl_qte_pvt.update_row :'||x_return_status);
OPEN c_agent_csr(l_quote_rec.last_updated_by);
SELECT TO_CHAR(SYSDATE, 'DD-MON-RRRR') SYSTEM_DATE,
OTQ.QUOTE_NUMBER QUOTE_NUMBER,
to_char(OTQ.DATE_EFFECTIVE_TO, 'DD-MON-RRRR') EFFECTIVE_TO,
okl_accounting_util.format_amount(SUM(NVL(OTL.AMOUNT,0)),okl_am_util_pvt.get_chr_currency(OTQ.KHR_ID))||' '||okl_am_util_pvt.get_chr_currency(OTQ.KHR_ID) QUOTE_TOTAL,
OKL_AM_UTIL_PVT.get_lookup_meaning('OKL_QUOTE_REASON',OTQ.qrs_code,'N')QUOTE_REASON ,
OKL_AM_UTIL_PVT.get_lookup_meaning('OKL_QUOTE_TYPE',OTQ.qtp_code,'N') QUOTE_TYPE,
to_char(OTQ.CREATION_DATE, 'DD-MON-RRRR') QUOTE_CREATION_DATE,
AD.CONTRACT_NUMBER CONTRACT_NUMBER,
OTQ.COMMENTS COMMENTS,
OTQ.LAST_UPDATED_BY LAST_UPDATED_BY,
OTQ.KHR_ID KHR_ID
FROM OKL_TRX_QUOTES_V OTQ,
OKL_TXL_QUOTE_LINES_B OTL,
OKC_K_HEADERS_V AD
WHERE OTQ.ID = c_id
AND OTQ.ID = OTL.QTE_ID
AND AD.ID = OTQ.KHR_ID
GROUP BY TO_CHAR(SYSDATE, 'DD-MON-RRRR'),
OTQ.QUOTE_NUMBER,
OTQ.DATE_EFFECTIVE_TO,
OTQ.QST_CODE,
OTQ.CREATION_DATE,
OTQ.QRS_CODE,
AD.CONTRACT_NUMBER,
OTQ.COMMENTS,
OTQ.LAST_UPDATED_BY, OTQ.KHR_ID, OTQ.qtp_code;
SELECT partial_yn, khr_id, qtp_code
FROM OKL_TRX_QUOTES_B
WHERE id = c_id;
SELECT TRX.ID,
TRX.QUOTE_NUMBER,
TRX.KHR_ID,
TRX.QTP_CODE,
KHR.CONTRACT_NUMBER
FROM OKL_TRX_QUOTES_B TRX,
OKC_K_HEADERS_B KHR
WHERE TRX.id = p_qte_id
AND TRX.KHR_ID = KHR.ID;
SELECT khr_id, qtp_code,last_updated_by
FROM OKL_TRX_QUOTES_B
WHERE id = c_id;
SELECT *
FROM OKL_TXL_QUOTE_LINES_B
WHERE qte_id =c_id;
l_last_updated_by NUMBER;
FETCH c_qte_csr INTO l_khr_id, l_qtp_code,l_last_updated_by;
, p_user_id => l_last_updated_by
, x_name => l_user_name
, x_description => l_name);
SELECT max(QP_DELAY_DAYS) DELAY_DAYS
FROM OKL_AM_QUOTE_PARTIES_UV
WHERE quote_id = c_qte_id
AND qp_role_code = 'ADVANCE_NOTICE';
SELECT *
FROM OKL_TXL_QUOTE_LINES_B
WHERE qte_id =c_id;
SELECT trx.QUOTE_NUMBER
, khr.short_description contract_name
, khr.contract_number
, trx.last_updated_by
, trx.date_requested
, decode(trx.QTP_CODE, 'TER_PURCHASE', 'Y', 'N') PURCHASE_ASSET
, nvl(early_termination_yn, 'N') EOT
, decode(nvl(partial_yn, 'N'), 'N', 'Y', 'N') COMPLETE_CONTRACT
FROM OKL_TRX_QUOTES_V trx,
OKC_K_HEADERS_V khr
WHERE trx.id = p_qte_id
and trx.QST_CODE = 'DRAFTED'
and trx.khr_id = khr.id;
, p_user_id => r_qte_details.last_updated_by
, x_name => l_user_name
, x_description => l_name);
SELECT a.last_updated_by, a.quote_number
FROM OKL_TRX_QUOTES_B a,
okl_quote_parties b
WHERE a.id = p_qte_id
and a.qst_code in ('ACCEPTED')
and b.qte_id (+) = a.id;
l_last_updated_by NUMBER;
FETCH get_quote_csr INTO l_last_updated_by, l_quote_number;
, p_user_id => l_last_updated_by
, x_name => l_user_name
, x_description => l_name);
IF l_last_updated_by IS NOT NULL AND l_user_name IS NOT NULL THEN
wf_engine.SetItemAttrText ( itemtype=> itemtype,
itemkey => itemkey,
aname => 'REQUESTER',
avalue => l_user_name);
okl_qte_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_qtev_rec => p_qtev_rec,
x_qtev_rec => x_qtev_rec);
'after call to okl_qte_pvt.update_row :'||x_return_status);
select count(*)
from WF_USER_ROLES WUR
where WUR.ROLE_NAME = p_value;
SELECT trx.QUOTE_NUMBER
, khr.short_description contract_name
, khr.contract_number
, trx.last_updated_by
, trx.date_requested
, decode(trx.QTP_CODE, 'TER_PURCHASE', 'Y', 'N') PURCHASE_ASSET
, nvl(early_termination_yn, 'N') EOT
, decode(nvl(partial_yn, 'N'), 'N', 'Y', 'N') COMPLETE_CONTRACT
, kle.ITEM_DESCRIPTION ASSET_NUMBER
, txl.ASSET_QUANTITY ASSET_QUANTITY
, txl.QUOTE_QUANTITY QUOTE_QUANTITY
FROM OKL_TRX_QUOTES_V trx,
OKL_TXL_QUOTE_LINES_V txl,
OKC_K_HEADERS_V khr,
OKC_K_LINES_V kle
WHERE trx.id = p_qte_id
and txl.qte_id (+) = trx.id
and trx.QST_CODE = 'DRAFTED'
and trx.khr_id = khr.id
and txl.kle_id = kle.id;
, p_user_id => r_qte_details.last_updated_by
, x_name => l_user_name
, x_description => l_name);
PROCEDURE update_partial_quote( itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funcmode IN VARCHAR2,
resultout OUT NOCOPY VARCHAR2 ) AS
l_approved VARCHAR2(1);
L_MODULE_NAME VARCHAR2(500) := G_MODULE_NAME||'update_partial_quote';
wf_core.context('OKL_AM_QUOTES_WF' , 'update_partial_quote', itemtype, itemkey, actid, funcmode);
END update_partial_quote;
PROCEDURE update_gain_loss_quote( itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funcmode IN VARCHAR2,
resultout OUT NOCOPY VARCHAR2 ) AS
l_approved VARCHAR2(1);
L_MODULE_NAME VARCHAR2(500) := G_MODULE_NAME||'update_gain_loss_quote';
wf_core.context('OKL_AM_QUOTES_WF' , 'update_gain_loss_quote', itemtype, itemkey, actid, funcmode);
END update_gain_loss_quote;
SELECT qte.date_effective_from,
qte.date_accepted
FROM OKL_TRX_QUOTES_B qte
WHERE qte.id = p_qte_id;
PROCEDURE update_quote_drafted(itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funcmode IN VARCHAR2,
resultout OUT NOCOPY VARCHAR2) AS
l_transaction_id VARCHAR2(2000);
L_MODULE_NAME VARCHAR2(500) := G_MODULE_NAME||'update_quote_drafted';
OKL_TRX_QUOTES_PUB.update_trx_quotes (
p_api_version => l_api_version,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => lx_msg_count,
x_msg_data => lx_msg_data,
p_qtev_rec => lp_qtev_rec,
x_qtev_rec => lx_qtev_rec);
'after call to OKL_TRX_QUOTES_PUB.update_trx_quotes :'||l_return_status);
WF_CORE.context('OKL_AM_QUOTES_WF' , 'update_quote_status',
itemtype, itemkey, actid, funcmode);
WF_CORE.context('OKL_AM_QUOTES_WF' , 'update_quote_status',
itemtype, itemkey, actid, funcmode);
END update_quote_drafted;
SELECT khr_id, qtp_code,last_updated_by
FROM OKL_TRX_QUOTES_B
WHERE id = c_id;
SELECT sum(amount) amount
FROM OKL_TXL_QUOTE_LINES_B
WHERE qte_id =c_id
AND qlt_code NOT IN ('AMCFIA','AMYOUB','BILL_ADJST');
l_last_updated_by NUMBER;
FETCH c_qte_csr INTO l_khr_id, l_qtp_code,l_last_updated_by;
, p_user_id => l_last_updated_by
, x_name => l_user_name
, x_description => l_name);
SELECT TRX.ID,
TRX.QUOTE_NUMBER,
TRX.KHR_ID,
TRX.QTP_CODE,
KHR.CONTRACT_NUMBER
FROM OKL_TRX_QUOTES_B TRX,
OKC_K_HEADERS_B KHR
WHERE TRX.id = p_qte_id
AND TRX.KHR_ID = KHR.ID;
SELECT nvl(SUM(amount),0) amount
FROM OKL_TXL_QUOTE_LINES_B TQL
WHERE TQL.qte_id = p_qte_id
AND TQL.qlt_code NOT IN ('AMCFIA','AMYOUB','BILL_ADJST');
SELECT repo_quote_indicator_yn
FROM OKL_TRX_QUOTES_B
WHERE id = p_qte_id;
SELECT kle_id, DATE_EFFECTIVE_FROM, CURRENCY_CODE -- 6736148
FROM OKL_AM_ASSET_QUOTES_UV
WHERE id = p_qte_id;
SELECT COUNT(kle_id)
FROM OKL_ASSET_RETURNS_B
WHERE kle_id = p_line_id
AND ARS_CODE <> 'CANCELLED';
SELECT khr_id
FROM OKL_TRX_QUOTES_B
WHERE ID = p_qte_id;
SELECT B.NAME
FROM OKL_SYSTEM_PARAMS_ALL A,
OKL_FORMULAE_B B
WHERE A.ORG_ID = p_org_id
AND A.FORMULA_ID = B.ID (+);