[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT REPLACE(REPLACE(p_amount_in,SUBSTR(value,2,1)),SUBSTR(value,1,1),'.')
-- select replace(replace(p_amount_in,substr(',.',2,1)),substr(',.',1,1),'.')
INTO l_amount_out
FROM v$nls_parameters
WHERE parameter = 'NLS_NUMERIC_CHARACTERS';
SELECT MEANING
FROM FND_LOOKUPS FND
WHERE FND.LOOKUP_TYPE = p_lookup_type
AND FND.LOOKUP_CODE = p_lookup_code;
--cursor to check if the contract is selected for Mass Rebook
CURSOR l_chk_mass_rbk_csr
IS
SELECT '1'
FROM okc_k_headers_b chrb,
okl_trx_contracts ktrx
WHERE chrb.ID = p_chr_id
AND ktrx.khr_id = chrb.id
AND ktrx.tsu_code = 'ENTERED'
AND ktrx.rbr_code IS NOT NULL
AND ktrx.tcn_type = 'TRBK'
--rkuttiya added for 12.1.1 Multi GAAP
AND ktrx.representation_type = 'PRIMARY'
--
AND EXISTS (SELECT '1'
FROM okl_rbk_selected_contract rbk_khr
WHERE rbk_khr.khr_id = chrb.id
AND rbk_khr.status <> 'PROCESSED');
SELECT '1'
FROM okc_k_headers_b chrb,
okl_trx_contracts ktrx
WHERE ktrx.khr_id_new = chrb.id
AND ktrx.tsu_code = 'ENTERED'
AND ktrx.rbr_code IS NOT NULL
AND ktrx.tcn_type = 'TRBK'
--rkuttiya added for 12.1.1 Multi GAAP
AND ktrx.representation_type = 'PRIMARY'
--
AND chrb.id = p_chr_id
AND chrb.orig_system_source_codE = 'OKL_REBOOK';
SELECT '1'
FROM okc_k_headers_b chrb
WHERE chrb.id = p_chr_id
AND NVL(chrb.orig_system_source_code,'XXXX') = 'OKL_RELEASE';
SELECT '1'
FROM okc_k_headers_b chrb
WHERE NVL(chrb.orig_system_source_code,'XXXX') <> 'OKL_RELEASE'
AND chrb.ID = p_chr_id
AND EXISTS (SELECT '1'
FROM okc_rules_b rul
WHERE rul.dnz_chr_id = chrb.id
AND rul.rule_information_category = 'LARLES'
AND NVL(rule_information1,'N') = 'Y');
SELECT scs_code
FROM okc_k_headers_b
WHERE id = p_chr_id;
PROCEDURE check_line_update_allowed(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_cle_id IN NUMBER) IS
l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
l_api_name CONSTANT VARCHAR2(30) := 'CHECK_LINE_UPDATE_ALLOWED';
SELECT cle.sts_code,
cle.dnz_chr_id
FROM okc_k_lines_b cle
WHERE cle.id = p_cle_id;
p_msg_name => 'OKL_LA_RBK_TER_LINE_UPDATE');
END check_line_update_allowed;
SELECT STS_CODE
FROM okc_k_headers_all_b
WHERE ID = p_khr_id;
SELECT 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) DESCRIPTION
FROM HZ_LOCATIONS HL,
CSI_ITEM_INSTANCES CSI,
OKC_K_ITEMS CIM
WHERE CIM.CLE_ID = (SELECT A.ID
FROM OKC_K_LINES_V A,
OKC_LINE_STYLES_B B
WHERE CLE_ID = (SELECT A.ID
FROM OKC_K_LINES_V A,
OKC_LINE_STYLES_B B
WHERE CLE_ID = p_kle_id
AND A.LSE_ID = B.ID
AND A.dnz_chr_id = p_khr_id
AND B.LTY_CODE = 'FREE_FORM2')
AND A.LSE_ID = B.ID
AND A.dnz_chr_id = p_khr_id
AND B.LTY_CODE = 'INST_ITEM')
AND CIM.DNZ_CHR_ID = p_khr_id
AND CIM.OBJECT1_ID1 = CSI.INSTANCE_ID
AND CIM.OBJECT1_ID2 = '#'
AND CIM.JTOT_OBJECT1_CODE = 'OKX_IB_ITEM'
AND CSI.INSTALL_LOCATION_ID = HL.LOCATION_ID
AND CSI.INSTALL_LOCATION_TYPE_CODE = 'HZ_LOCATIONS'
UNION
SELECT 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) DESCRIPTION
FROM HZ_LOCATIONS HL,
HZ_PARTY_SITES HPS,
CSI_ITEM_INSTANCES CSI,
OKC_K_ITEMS CIM
WHERE CIM.CLE_ID = (SELECT A.ID
FROM OKC_K_LINES_V A,
OKC_LINE_STYLES_B B
WHERE CLE_ID = (SELECT A.ID
FROM OKC_K_LINES_V A,
OKC_LINE_STYLES_B B
WHERE CLE_ID = p_kle_id
AND A.LSE_ID = B.ID
AND A.dnz_chr_id = p_khr_id
AND B.LTY_CODE = 'FREE_FORM2')
AND A.LSE_ID = B.ID
AND A.dnz_chr_id = p_khr_id
AND B.LTY_CODE = 'INST_ITEM')
AND CIM.DNZ_CHR_ID = p_khr_id
AND CIM.OBJECT1_ID1 = CSI.INSTANCE_ID
AND CIM.OBJECT1_ID2 = '#'
AND CIM.JTOT_OBJECT1_CODE = 'OKX_IB_ITEM'
AND CSI.INSTALL_LOCATION_ID = HPS.PARTY_SITE_ID
AND HPS.LOCATION_ID = HL.LOCATION_ID
AND CSI.INSTALL_LOCATION_TYPE_CODE = 'HZ_PARTY_SITES';
SELECT B.DESCRIPTION
FROM OKX_PARTY_SITE_USES_V B
WHERE B.ID1 = (SELECT A.OBJECT_ID1_NEW
FROM OKL_TXL_ITM_INSTS_V A
WHERE A.KLE_ID = (SELECT A.ID
FROM OKC_K_LINES_V A,
OKC_LINE_STYLES_B B
WHERE CLE_ID = (SELECT A.ID
FROM OKC_K_LINES_V A,
OKC_LINE_STYLES_B B
WHERE CLE_ID = p_kle_id
AND A.LSE_ID = B.ID
AND A.dnz_chr_id = p_khr_id
AND B.LTY_CODE = 'FREE_FORM2')
AND A.LSE_ID = B.ID
AND A.dnz_chr_id = p_khr_id
AND B.LTY_CODE = 'INST_ITEM'))
AND B.ID2 = '#';
SELECT STS_CODE
FROM okc_k_headers_all_b
WHERE ID = p_khr_id;
SELECT psu.party_site_use_id
FROM HZ_LOCATIONS HL,
CSI_ITEM_INSTANCES CSI,
OKC_K_ITEMS CIM,
hz_party_site_uses psu,
hz_party_sites hps
WHERE CIM.CLE_ID in (SELECT A.ID
FROM OKC_K_LINES_V A,
OKC_LINE_STYLES_B B
WHERE CLE_ID in (SELECT A.ID
FROM OKC_K_LINES_V A,
OKC_LINE_STYLES_B B
WHERE CLE_ID = p_kle_id
AND A.LSE_ID = B.ID
AND A.dnz_chr_id = p_khr_id
AND B.LTY_CODE = 'FREE_FORM2')
AND A.LSE_ID = B.ID
AND A.dnz_chr_id = p_khr_id
AND B.LTY_CODE = 'INST_ITEM')
AND CIM.DNZ_CHR_ID = p_khr_id
AND CIM.OBJECT1_ID1 = CSI.INSTANCE_ID
AND CIM.OBJECT1_ID2 = '#'
AND CIM.JTOT_OBJECT1_CODE = 'OKX_IB_ITEM'
AND CSI.INSTALL_LOCATION_ID = HL.LOCATION_ID
AND CSI.INSTALL_LOCATION_TYPE_CODE = 'HZ_LOCATIONS'
AND psu.site_use_type ='INSTALL_AT'
AND psu.party_site_id = hps.party_site_id
AND hps.location_id = hl.location_id
UNION
SELECT psu.party_site_use_id
FROM HZ_LOCATIONS HL,
HZ_PARTY_SITES HPS,
HZ_PARTY_SITE_USES PSU,
CSI_ITEM_INSTANCES CSI,
OKC_K_ITEMS CIM
WHERE CIM.CLE_ID in (SELECT A.ID
FROM OKC_K_LINES_V A,
OKC_LINE_STYLES_B B
WHERE CLE_ID in (SELECT A.ID
FROM OKC_K_LINES_V A,
OKC_LINE_STYLES_B B
WHERE CLE_ID = p_kle_id
AND A.LSE_ID = B.ID
AND A.dnz_chr_id = p_khr_id
AND B.LTY_CODE = 'FREE_FORM2')
AND A.LSE_ID = B.ID
AND A.dnz_chr_id = p_khr_id
AND B.LTY_CODE = 'INST_ITEM')
AND CIM.DNZ_CHR_ID = p_khr_id
AND CIM.OBJECT1_ID1 = CSI.INSTANCE_ID
AND CIM.OBJECT1_ID2 = '#'
AND CIM.JTOT_OBJECT1_CODE = 'OKX_IB_ITEM'
AND CSI.INSTALL_LOCATION_ID = HPS.PARTY_SITE_ID
AND HPS.LOCATION_ID = HL.LOCATION_ID
AND CSI.INSTALL_LOCATION_TYPE_CODE = 'HZ_PARTY_SITES'
AND psu.party_site_id = hps.party_site_id
AND psu.site_use_type = 'INSTALL_AT';
SELECT A.OBJECT_ID1_NEW
FROM OKL_TXL_ITM_INSTS_V A
WHERE A.KLE_ID IN (SELECT A.ID
FROM OKC_K_LINES_V A,
OKC_LINE_STYLES_B B
WHERE CLE_ID IN (SELECT A.ID
FROM OKC_K_LINES_V A,
OKC_LINE_STYLES_B B
WHERE CLE_ID = p_kle_id
AND A.LSE_ID = B.ID
AND A.dnz_chr_id = p_khr_id
AND B.LTY_CODE = 'FREE_FORM2')
AND A.LSE_ID = B.ID
AND A.dnz_chr_id = p_khr_id
AND B.LTY_CODE = 'INST_ITEM');
SELECT STS_CODE
FROM okc_k_headers_all_b
WHERE ID = p_khr_id;
SELECT FAV.ASSET_NUMBER ASSETNUMBER
FROM OKC_K_LINES_V CLE_FIN
, OKC_LINE_STYLES_B LSE_FIN
, OKC_K_LINES_B CLE_FA
, OKC_LINE_STYLES_B LSE_FA
, OKC_K_ITEMS CIM_FA
, FA_ADDITIONS_B FAV
WHERE CLE_FIN.CLE_ID IS NULL
AND CLE_FIN.id = p_kle_id
AND CLE_FIN.DNZ_CHR_ID = p_khr_id
AND LSE_FIN.ID = CLE_FIN.LSE_ID
AND LSE_FIN.LTY_CODE = 'FREE_FORM1'
AND CLE_FA.CLE_ID = CLE_FIN.ID
AND CLE_FA.LSE_ID = LSE_FA.ID
AND LSE_FA.LTY_CODE = 'FIXED_ASSET'
AND CIM_FA.CLE_ID = CLE_FA.ID
AND CIM_FA.OBJECT1_ID1 = FAV.ASSET_ID
AND CIM_FA.OBJECT1_ID2 = '#' ;
select hzps.party_site_id
,hzps.location_id
,aps.vendor_id
,hzps.party_site_number
,hzl.address1
,hzl.city
,hzl.state
,hzl.postal_code
,hzl.country
,hzl.county
,hzl.address_style
,hzl.province
from
hz_party_sites hzps
,hz_parties hz
,ap_suppliers aps
,hz_locations hzl
where
hzps.party_id = hz.party_id
and hzps.IDENTIFYING_ADDRESS_FLAG = 'Y'
and hz.party_id = aps.party_id
and hz.party_id = p_party_id
and hzps.location_id = hzl.location_id;
select
hzps.location_id
,hzps.party_site_number
,aps.vendor_id
,hzl.address1
,hzl.city
,hzl.state
,hzl.postal_code
,hzl.country
,hzl.county
,hzl.address_style
,hzl.province
from
hz_party_sites hzps
,ap_suppliers aps
,hz_locations hzl
where
hzps.party_id = aps.party_id
and party_site_id = p_party_site_id
and hzps.location_id = hzl.location_id;
POS_VENDOR_PUB_PKG.Update_Vendor_Site
(
p_vendor_site_rec => l_vendor_site_rec_upd,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
SELECT MAX(ktrx.transaction_date)
FROM okc_k_headers_b chrb,
okl_trx_contracts ktrx
WHERE chrb.id = p_chr_id
AND ktrx.khr_id = chrb.id
AND ktrx.tsu_code = 'PROCESSED'
AND ktrx.tcn_type IN ('TRBK','SPA','BKG','REL')
AND ktrx.representation_type = 'PRIMARY';
procedure update_external_id (p_chr_id in number,
x_return_status OUT NOCOPY VARCHAR2) IS
cursor get_line_details_csr(p_chr_id in number) IS
SELECT KLE.ID ID
FROM OKL_K_LINES KLE, OKC_K_LINES_B CLE
WHERE CLE.DNZ_CHR_ID = p_chr_id
AND CLE.ID = KLE.ID
AND CLE.LSE_ID in (33, 52, 53, 70) -- 48
AND cle.sts_code NOT IN ('HOLD', 'EXPIRED', 'CANCELLED','ABANDONED', 'TERMINATED')
AND kle.orig_contract_line_id is null;
SELECT vDtls.ID
FROM okl_party_payment_hdr vHdr,
okl_party_payment_dtls vDtls,
okc_k_lines_b cle,
okl_k_lines kle
WHERE vDtls.payment_hdr_id = vHdr.id
AND vHdr.CLE_ID = cle.id
AND vHdr.DNZ_CHR_ID = p_chr_id
AND vHdr.PASSTHRU_TERM = 'BASE'
AND vHdr.DNZ_CHR_ID = cle.dnz_chr_id
AND cle.lse_id = 52
AND cle.id = kle.id
AND cle.sts_code NOT IN ('HOLD', 'EXPIRED', 'CANCELLED','ABANDONED', 'TERMINATED')
and kle.fee_type = 'PASSTHROUGH'
and vDtls.orig_contract_line_id is null;
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_EXTERNAL_ID';
update okl_k_lines
set orig_contract_line_id = t_extr_id_tbl(i)
where id = t_extr_id_tbl(i)
and orig_contract_line_id is null;
t_extr_id_tbl.delete;
update okl_party_payment_dtls
set orig_contract_line_id = t_extr_id_tbl(i)
where id = t_extr_id_tbl(i)
and orig_contract_line_id is null;
t_extr_id_tbl.delete;
END update_external_id;
procedure update_external_id (p_chr_id in number,
x_return_status OUT NOCOPY VARCHAR2) IS
cursor get_line_details_csr(p_chr_id in number) IS
SELECT KLE.ID ID
FROM OKL_K_LINES KLE, OKC_K_LINES_B CLE
WHERE CLE.DNZ_CHR_ID = p_chr_id
AND CLE.ID = KLE.ID
AND CLE.LSE_ID in (33, 52, 53, 70) -- 48
AND cle.sts_code NOT IN ('HOLD', 'EXPIRED', 'CANCELLED','ABANDONED', 'TERMINATED')
AND kle.orig_contract_line_id is null;
SELECT kle.id, kle.orig_contract_line_id
FROM OKL_K_LINES KLE, OKC_K_LINES_B CLE
WHERE CLE.DNZ_CHR_ID = p_orig_chr_id
AND CLE.ID = KLE.ID
AND CLE.LSE_ID in (33, 52, 53, 70) -- 48
AND cle.sts_code NOT IN ('HOLD', 'EXPIRED', 'CANCELLED','ABANDONED', 'TERMINATED');
SELECT vDtls.ID
FROM okl_party_payment_hdr vHdr,
okl_party_payment_dtls vDtls,
okc_k_lines_b cle,
okl_k_lines kle,
okc_k_headers_all_b chr
WHERE vDtls.payment_hdr_id = vHdr.id
AND vHdr.CLE_ID = cle.id
AND vHdr.DNZ_CHR_ID = p_chr_id
AND vHdr.PASSTHRU_TERM = 'BASE'
AND vHdr.DNZ_CHR_ID = cle.dnz_chr_id
AND cle.lse_id = 52
AND cle.id = kle.id
AND cle.sts_code NOT IN ('HOLD', 'EXPIRED', 'CANCELLED','ABANDONED', 'TERMINATED')
and kle.fee_type = 'PASSTHROUGH'
and vDtls.orig_contract_line_id is null;
SELECT vDtls.orig_contract_line_id, vDtls.vendor_id, cle.id
FROM okl_party_payment_hdr vHdr,
okl_party_payment_dtls vDtls,
okc_k_lines_b cle,
okl_k_lines kle
WHERE vDtls.payment_hdr_id = vHdr.id
AND vHdr.CLE_ID = cle.id
AND vHdr.PASSTHRU_TERM = 'BASE'
AND vHdr.DNZ_CHR_ID = cle.dnz_chr_id
AND cle.lse_id = 52
AND cle.id = kle.id
and kle.fee_type = 'PASSTHROUGH'
AND cle.sts_code NOT IN ('HOLD', 'EXPIRED', 'CANCELLED','ABANDONED', 'TERMINATED')
AND vHdr.DNZ_CHR_ID = p_orig_chr_id;
SELECT 'Y' online_rebook_in_progress,
orig_chr.id
FROM okc_k_headers_all_b rbk_chr,
okc_k_headers_all_b orig_chr,
okl_trx_contracts_all trx
WHERE rbk_chr.id = p_khr_id
AND rbk_chr.orig_system_source_code = 'OKL_REBOOK'
AND trx.khr_id_new = rbk_chr.id
AND trx.tsu_code = 'ENTERED'
AND trx.tcn_type = 'TRBK'
AND rbk_chr.orig_system_id1 = orig_chr.id;
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_EXTERNAL_ID';
update okl_k_lines
set orig_contract_line_id = t_extr_id_tbl(i)
where id = t_extr_id_tbl(i)
and orig_contract_line_id is null;
t_extr_id_tbl.delete;
update okl_k_lines a
set orig_contract_line_id = t_extr_id_tbl(i)
where id in (select id FROM okc_k_lines_b b
where orig_system_id1 = t_orig_cle_id_tbl(i)
and dnz_chr_id = p_chr_id)
and orig_contract_line_id is null;
t_extr_id_tbl.delete;
t_orig_cle_id_tbl.delete;
update okl_k_lines
set orig_contract_line_id = t_extr_id_tbl(i)
where id = t_extr_id_tbl(i)
and orig_contract_line_id is null;
t_extr_id_tbl.delete;
update okl_party_payment_dtls
set orig_contract_line_id = t_extr_id_tbl(i)
where id = t_extr_id_tbl(i)
and orig_contract_line_id is null;
update okl_party_payment_dtls a
set orig_contract_line_id = t_extr_id_tbl(i)
where id in (
SELECT vDtls.id
FROM okl_party_payment_hdr vHdr,
okl_party_payment_dtls vDtls,
okc_k_lines_b cle
WHERE vDtls.payment_hdr_id = vHdr.id
AND vHdr.CLE_ID = cle.id
AND vHdr.PASSTHRU_TERM = 'BASE'
AND vHdr.DNZ_CHR_ID = cle.dnz_chr_id
AND cle.lse_id = 52
AND vHdr.DNZ_CHR_ID = p_chr_id
and cle.orig_system_id1 = t_orig_cle_id_tbl(i)
and vdtls.vendor_id = t_vendor_id_tbl(i)
and orig_contract_line_id is null);
t_extr_id_tbl.delete;
t_orig_cle_id_tbl.delete;
t_orig_chr_id_tbl.delete;
t_vendor_id_tbl.delete;
update okl_party_payment_dtls
set orig_contract_line_id = t_extr_id_tbl(i)
where id = t_extr_id_tbl(i)
and orig_contract_line_id is null;
t_extr_id_tbl.delete;
t_extr_id_tbl.delete;
t_orig_cle_id_tbl.delete;
t_orig_chr_id_tbl.delete;
t_vendor_id_tbl.delete;
END update_external_id;
SELECT 'Y' chr_upgraded_yn
FROM okl_stream_trx_data
WHERE orig_khr_id = p_chr_id
AND last_trx_state = 'Y';
SELECT amort_inc_adj_rev_dt_yn
FROM okl_sys_acct_opts;
SELECT contract_number
FROM okc_k_headers_all_b
WHERE id = p_chr_id;
SELECT 'Y' chr_upgraded_yn
FROM okl_stream_trx_data
WHERE khr_id = p_rbk_chr_id
AND transaction_state is not null;