[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
);