[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT scs_code
FROM OKL_K_HEADERS_FULL_V
WHERE id = p_khr_id;
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRIPXB.pls call okl_trx_ar_invoices_pub.insert_trx_ar_invoices ');
okl_trx_ar_invoices_pub.insert_trx_ar_invoices(p_api_version,
p_init_msg_list,
l_return_status,
x_msg_count,
x_msg_data,
l_taiv_rec,
lx_taiv_rec);
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRIPXB.pls call okl_trx_ar_invoices_pub.insert_trx_ar_invoices ');
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRIPXB.pls call okl_txl_ar_inv_lns_pub.insert_txl_ar_inv_lns ');
okl_txl_ar_inv_lns_pub.insert_txl_ar_inv_lns(p_api_version,
p_init_msg_list,
l_return_status,
x_msg_count,
x_msg_data,
l_tilv_rec,
lx_tilv_rec);
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRIPXB.pls call okl_txl_ar_inv_lns_pub.insert_txl_ar_inv_lns ');
SELECT STE_CODE
FROM OKC_K_HEADERS_V KHR , OKC_STATUSES_B OST
WHERE KHR.ID = p_khr_id
AND KHR.STS_CODE = OST.CODE ;
PROCEDURE insert_ap_request(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_tap_id IN NUMBER,
p_credit_amount IN NUMBER,
p_credit_sty_id IN NUMBER,
p_khr_id IN NUMBER ,
p_kle_id IN NUMBER,
p_invoice_date IN DATE,
p_trx_id IN NUMBER,
p_vendor_site_id IN NUMBER ,
x_request_id OUT NOCOPY NUMBER
) IS
l_tplv_rec okl_tpl_pvt.tplv_rec_type ;
SELECT id
FROM okl_trx_types_tl
WHERE name = cp_name
AND language = cp_language;
SELECT
CURRENCY_CODE,
SET_OF_BOOKS_ID
FROM OKL_TRX_AP_INVOICES_B
WHERE ID = P_TAP_ID ;
SELECT CURRENCY_CODE --Bug:3825159
FROM OKC_K_HEADERS_B
WHERE ID = P_khr_ID ;
SELECT APPLICATION_ID
FROM FND_APPLICATION
WHERE APPLICATION_SHORT_NAME = 'OKL' ;
l_api_name CONSTANT VARCHAR2(30) := 'insert_ap_request';
SELECT DECODE(FND_GLOBAL.CONC_REQUEST_ID,-1,NULL,FND_GLOBAL.CONC_REQUEST_ID),
DECODE(FND_GLOBAL.PROG_APPL_ID,-1,NULL,FND_GLOBAL.PROG_APPL_ID),
DECODE(FND_GLOBAL.CONC_PROGRAM_ID,-1,NULL,FND_GLOBAL.CONC_PROGRAM_ID),
DECODE(FND_GLOBAL.CONC_REQUEST_ID,-1,NULL,SYSDATE),
mo_global.get_current_org_id() INTO l_tapv_rec.REQUEST_ID,
l_tapv_rec.PROGRAM_APPLICATION_ID,
l_tapv_rec.PROGRAM_ID,
l_tapv_rec.PROGRAM_UPDATE_DATE,
l_tapv_rec.ORG_ID FROM dual;
SELECT DECODE(FND_GLOBAL.CONC_REQUEST_ID,-1,NULL,FND_GLOBAL.CONC_REQUEST_ID),
DECODE(FND_GLOBAL.PROG_APPL_ID,-1,NULL,FND_GLOBAL.PROG_APPL_ID),
DECODE(FND_GLOBAL.CONC_PROGRAM_ID,-1,NULL,FND_GLOBAL.CONC_PROGRAM_ID),
DECODE(FND_GLOBAL.CONC_REQUEST_ID,-1,NULL,SYSDATE),
mo_global.get_current_org_id() INTO l_tplv_rec.REQUEST_ID,
l_tplv_rec.PROGRAM_APPLICATION_ID,
l_tplv_rec.PROGRAM_ID,
l_tplv_rec.PROGRAM_UPDATE_DATE,
l_tplv_rec.ORG_ID FROM dual;
END insert_ap_request;
PROCEDURE insert_ap_request(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_tap_id IN NUMBER,
p_credit_amount IN NUMBER,
p_credit_sty_id IN NUMBER,
p_khr_id IN NUMBER ,
p_kle_id IN NUMBER,
p_invoice_date IN DATE,
p_trx_id IN NUMBER
)
IS
l_tplv_rec okl_tpl_pvt.tplv_rec_type ;
SELECT PV.VENDOR_ID VENDOR_ID,PV.VENDOR_NAME VENDOR_NAME,CHR.AUTHORING_ORG_ID ORG_ID
FROM OKL_INS_POLICIES_B IPYB ,
OKC_K_HEADERS_B CHR,
PO_VENDORS PV
WHERE IPYB.KLE_ID = p_kle_id
AND CHR.ID = IPYB.KHR_ID
AND IPYB.ISU_ID = PV.VENDOR_ID;
SELECT IPOV.ID1 ID1
FROM OKX_VENDOR_SITES_V IPOV
WHERE IPOV.VENDOR_ID = p_vendor_id
AND IPOV.ORG_ID = p_org_id
AND IPOV.PAY_SITE_FLAG = 'Y';
SELECT id
FROM okl_trx_types_tl
WHERE name = cp_name
AND language = cp_language;
SELECT
CURRENCY_CODE,
SET_OF_BOOKS_ID
FROM OKL_TRX_AP_INVOICES_B
WHERE ID = P_TAP_ID ;
SELECT CURRENCY_CODE
FROM OKC_K_HEADERS_B -- Changed to table
WHERE ID = P_khr_ID ;
SELECT APPLICATION_ID
FROM FND_APPLICATION
WHERE APPLICATION_SHORT_NAME = 'OKL' ;
l_api_name CONSTANT VARCHAR2(30) := 'insert_ap_request';
SELECT DECODE(FND_GLOBAL.CONC_REQUEST_ID,-1,NULL,FND_GLOBAL.CONC_REQUEST_ID),
DECODE(FND_GLOBAL.PROG_APPL_ID,-1,NULL,FND_GLOBAL.PROG_APPL_ID),
DECODE(FND_GLOBAL.CONC_PROGRAM_ID,-1,NULL,FND_GLOBAL.CONC_PROGRAM_ID),
DECODE(FND_GLOBAL.CONC_REQUEST_ID,-1,NULL,SYSDATE),
mo_global.get_current_org_id() INTO l_tapv_rec.REQUEST_ID,
l_tapv_rec.PROGRAM_APPLICATION_ID,
l_tapv_rec.PROGRAM_ID,
l_tapv_rec.PROGRAM_UPDATE_DATE,
l_tapv_rec.ORG_ID FROM dual;
SELECT DECODE(FND_GLOBAL.CONC_REQUEST_ID,-1,NULL,FND_GLOBAL.CONC_REQUEST_ID),
DECODE(FND_GLOBAL.PROG_APPL_ID,-1,NULL,FND_GLOBAL.PROG_APPL_ID),
DECODE(FND_GLOBAL.CONC_PROGRAM_ID,-1,NULL,FND_GLOBAL.CONC_PROGRAM_ID),
DECODE(FND_GLOBAL.CONC_REQUEST_ID,-1,NULL,SYSDATE),
mo_global.get_current_org_id() INTO l_tplv_rec.REQUEST_ID,
l_tplv_rec.PROGRAM_APPLICATION_ID,
l_tplv_rec.PROGRAM_ID,
l_tplv_rec.PROGRAM_UPDATE_DATE,
l_tplv_rec.ORG_ID FROM dual;
END insert_ap_request;
SELECT id
FROM okl_trx_types_tl
WHERE name = cp_name
AND language = cp_language;
select ID
from OKL_STRM_TYPE_TL
where NAME = 'INSURANCE REFUND'
AND LANGUAGE = 'US';
SELECT SUM(STRE.AMOUNT)
FROM OKL_STRM_ELEMENTS STRE, OKL_STREAMS STR
WHERE STR.KHR_ID = P_IPYV_REC.KHR_ID
AND STR.KLE_ID = P_IPYV_REC.KLE_ID
AND STR.ID = STRE.STM_ID
AND STRE.DATE_BILLED IS NOT NULL;
PROCEDURE delete_policy(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_ipyv_rec IN ipyv_rec_type,
x_ipyv_rec OUT NOCOPY ipyv_rec_type
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'delete_policy';
SELECT IPYB.KHR_ID, IPYB.KLE_ID ,IPYB.OBJECT_VERSION_NUMBER, ISS_CODE, IPY_TYPE ,FACTOR_CODE
FROM OKL_INS_POLICIES_B IPYB
WHERE IPYB.ID = p_ipy_id;
OKC_API.set_message(G_APP_NAME, 'OKL_NO_DELETED' ); -- For Third party Error
Okl_Contract_Pub.update_contract_line
(
p_api_version => l_api_version ,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => l_return_status ,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data ,
p_clev_rec => l_clev_rec ,
p_klev_rec => l_klev_rec,
p_edit_mode =>'N' ,
x_clev_rec => lx_clev_rec,
x_klev_rec => lx_klev_rec
);
Okl_Contract_Pub.update_contract_line
(
p_api_version => l_api_version ,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => l_return_status ,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data ,
p_clev_rec => l_clev_rec ,
p_klev_rec => l_klev_rec,
p_edit_mode =>'N' ,
x_clev_rec => lx_clev_rec,
x_klev_rec => lx_klev_rec
);
OKL_CONTRACT_PUB.delete_contract_line(
p_api_version => l_api_version,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_line_id => l_ipyv_rec.kle_id );
l_ipyv_rec.iss_code := 'DELETED';
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRIPXB.pls call Okl_Ins_Policies_Pub.update_ins_policies ');
Okl_Ins_Policies_Pub.update_ins_policies(
p_api_version => p_api_version,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_ipyv_rec => l_ipyv_rec,
x_ipyv_rec => x_ipyv_rec
);
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRIPXB.pls call Okl_Ins_Policies_Pub.update_ins_policies ');
END delete_policy;
select SUM(lessor_premium) -- Smoduga fix fro bug 4238141
from OKL_INS_ASSETS OINB
where OINB.IPY_ID = p_ipyv_rec.ID
GROUP BY OINB.IPY_ID;
select ID
from OKL_STRM_TYPE_V
where code = p_stream_type;
SELECT SUM(STRE.AMOUNT)
FROM okl_strm_elements STRE, OKL_STREAMS STR
WHERE STR.ID = STRE.STM_ID
AND STR.STY_ID = l_stm_type_id
AND STRE.DATE_BILLED IS NOT NULL
AND STR.KHR_ID = p_ipyv_rec.KHR_ID
AND STR.KLE_ID = p_ipyv_rec.KLE_ID;
SELECT id
FROM okl_trx_types_tl
WHERE name = cp_name
AND language = cp_language;
SELECT ((INSURER_RATE * p_covered_amount )/100 )
FROM OKL_INS_POLICIES_B IPYB , OKL_INS_RATES INR
WHERE IPYB.ipt_id = inr.ipt_id AND
kle_id = p_ipyv_rec.KLE_ID and
khr_id = p_ipyv_rec.KHR_ID
AND IPYB.date_from between inr.date_FROM and DECODE(NVL(inr.date_TO,NULL),NULL,SYSDATE, inr.date_TO)
and IPYB.territory_code = inr.ic_id
AND IPYB.FACTOR_VALUE BETWEEN inr.FACTOR_RANGE_START AND inr.FACTOR_RANGE_END ;
SELECT start_date
FROM okc_k_headers_b
WHERE id = p_ipyv_rec.KHR_ID ;
insert_ap_request(p_api_version => l_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_tap_id => l_tra_id,
p_credit_amount => l_to_refund,
p_credit_sty_id => l_strm_type_id,
p_khr_id => p_ipyv_rec.khr_id,
p_kle_id => p_ipyv_rec.kle_id,
p_invoice_date => SYSDATE,
p_trx_id => l_trx_type_ID );
insert_ap_request(p_api_version => l_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_tap_id => l_tra_id,
p_credit_amount => l_to_refund,
p_credit_sty_id => l_strm_type_id,
p_khr_id => p_ipyv_rec.khr_id,
p_kle_id => p_ipyv_rec.kle_id,
p_invoice_date => SYSDATE,
p_trx_id => l_trx_type_ID );
SELECT STM.ID
FROM OKL_STREAMS STM
WHERE STM.STY_ID = l_recv_strm_id
AND STM.KLE_ID = p_contract_line
AND STM.KHR_ID = p_contract_id;
SELECT STM.ID
FROM OKL_STREAMS STM
WHERE STM.STY_ID = l_recv_strm_id
AND STM.KLE_ID = p_contract_line
AND STM.KHR_ID = p_contract_id
AND STM.PURPOSE_CODE IS NULL;
SELECT STM.ID
FROM OKL_STREAMS STM
WHERE STM.STY_ID = l_recv_strm_id
AND STM.KLE_ID = p_contract_line
AND STM.KHR_ID = p_contract_id
AND STM.PURPOSE_CODE ='REPORT';
select ID
from OKL_STRM_TYPE_TL
where NAME = ls_strm_type
AND LANGUAGE = 'US';
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRIPXB.pls call OKL_STREAMS_PUB.update_streams ');
OKL_STREAMS_PUB.update_streams(
p_api_version
,p_init_msg_list
,x_return_status
,x_msg_count
,x_msg_data
,p_stmv_rec
,x_stmv_rec
);
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRIPXB.pls call OKL_STREAMS_PUB.update_streams ');
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRIPXB.pls call OKL_STREAMS_PUB.update_streams ');
OKL_STREAMS_PUB.update_streams(
p_api_version
,p_init_msg_list
,x_return_status
,x_msg_count
,x_msg_data
,p_stmv_rec
,x_stmv_rec
);
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRIPXB.pls call OKL_STREAMS_PUB.update_streams ');
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRIPXB.pls call OKL_STREAMS_PUB.update_streams ');
OKL_STREAMS_PUB.update_streams(
p_api_version
,p_init_msg_list
,x_return_status
,x_msg_count
,x_msg_data
,p_stmv_rec
,x_stmv_rec
);
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRIPXB.pls call OKL_STREAMS_PUB.update_streams ');
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRIPXB.pls call OKL_STREAMS_PUB.update_streams ');
OKL_STREAMS_PUB.update_streams(
p_api_version
,p_init_msg_list
,x_return_status
,x_msg_count
,x_msg_data
,p_stmv_rec
,x_stmv_rec
);
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRIPXB.pls call OKL_STREAMS_PUB.update_streams ');
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRIPXB.pls call OKL_STREAMS_PUB.update_streams ');
OKL_STREAMS_PUB.update_streams(
p_api_version
,p_init_msg_list
,x_return_status
,x_msg_count
,x_msg_data
,p_stmv_rec
,x_stmv_rec
);
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRIPXB.pls call OKL_STREAMS_PUB.update_streams ');
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRIPXB.pls call OKL_STREAMS_PUB.update_streams ');
OKL_STREAMS_PUB.update_streams(
p_api_version
,p_init_msg_list
,x_return_status
,x_msg_count
,x_msg_data
,p_stmv_rec
,x_stmv_rec
);
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRIPXB.pls call OKL_STREAMS_PUB.update_streams ');
SELECT ID, IPY_TYPE, ISS_CODE
FROM OKL_INS_POLICIES_B
WHERE KHR_ID = p_contract_id
and ISS_CODE in ('ACTIVE','PENDING');
delete_policy(
p_api_version => l_api_version,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_ipyv_rec => l_ipyv_rec,
x_ipyv_rec => lx_ipyv_rec);
SELECT MAX(major_version)
FROM OKC_K_HEADERS_BH
WHERE id = cp_chr_id;
SELECT 'X'
FROM OKL_K_ASSETS_UV NEW_ASST
WHERE
NEW_ASST.KLE_ID not in
(SELECT INA.KLE_ID
FROM
OKL_INS_ASSETS INA,
OKL_INS_POLICIES_B IPY
WHERE
IPY.KHR_ID = cp_chr_id
AND IPY.ID = INA.IPY_ID
AND IPY.ISS_CODE IN ( 'PENDING', 'ACTIVE')
)
AND NEW_ASST.CONTRACT_ID = cp_chr_id
AND ROWNUM = 1;
SELECT 'X'
FROM OKL_INS_ASSETS INA,
OKC_K_LINES_B FINAC_CLE,
OKC_LINE_STYLES_B FINAC_LS ,
OKL_INS_POLICIES_B IPY
WHERE FINAC_CLE.ID = INA.KLE_ID
AND FINAC_LS.LTY_CODE = 'FREE_FORM1'
AND FINAC_CLE.LSE_ID = FINAC_LS.ID
AND FINAC_CLE.STS_CODE <> 'BOOKED'
AND IPY.KHR_ID = FINAC_CLE.chr_id
AND IPY.ID = INA.IPY_ID
AND FINAC_CLE.chr_id = cp_chr_id
AND IPY.ISS_CODE IN ( 'PENDING', 'ACTIVE')
AND ROWNUM = 1;
SELECT 'X'
FROM
OKC_K_LINES_B C_CLE,
OKC_K_ITEMS C_CIT,
OKC_K_ITEMS_H H_CIT,
OKC_LINE_STYLES_B C_LSE,
OKL_INS_POLICIES_B IPY,
OKL_INS_ASSETS INA
WHERE c_cle.dnz_chr_id = cp_chr_id
AND c_cle.id = c_cit.cle_id
AND c_cle.lse_id = c_lse.id
AND c_cit.id = h_cit.id
AND c_lse.lty_code = 'FIXED_ASSET'
AND c_cit.jtot_object1_code = 'OKX_ASSET'
AND h_cit.MAJOR_VERSION = cp_major_version
AND c_cit.number_of_items <> h_cit.number_of_items
AND INA.KLE_ID = c_cle.CLE_ID
AND IPY.KHR_ID = c_cle.dnz_chr_id
AND IPY.ID = INA.IPY_ID
AND IPY.ISS_CODE IN ( 'PENDING', 'ACTIVE')
AND ROWNUM = 1;
SELECT 'X'
FROM OKC_K_LINES_B C_CLE,
OKC_K_LINES_BH H_CLE,
OKC_LINE_STYLES_B C_LSE,
OKL_K_LINES c_kle,
OKL_K_LINES_H h_kle ,
OKL_INS_POLICIES_B IPY,
OKL_INS_ASSETS INA
WHERE c_cle.dnz_chr_id = cp_chr_id
AND c_cle.id = h_cle.id
AND c_cle.lse_id = c_lse.id
AND c_cle.id = c_kle.id
AND h_kle.id = c_kle.id
AND h_cle.major_version = cp_version
AND c_lse.lty_code = 'FIXED_ASSET'
AND c_cle.price_unit <> h_cle.price_unit
AND INA.KLE_ID = c_cle.CLE_ID
AND IPY.KHR_ID = c_cle.dnz_chr_id
AND IPY.ID = INA.IPY_ID
AND IPY.ISS_CODE IN ( 'PENDING', 'ACTIVE')
AND ROWNUM = 1;
SELECT c_chr.START_DATE c_start_date,h_chr.START_DATE h_start_date,
round (months_between(c_chr.end_date,c_chr.START_DATE)) c_term,
round (months_between(h_chr.end_date,h_chr.START_DATE)) h_term
FROM OKC_K_HEADERS_B C_CHR,
OKC_K_HEADERS_BH H_CHR
WHERE c_chr.id = h_chr.id
AND h_chr.major_version = cp_version
AND c_chr.id = cp_chr_id
AND ROWNUM = 1;
SELECT IPYB.KHR_ID, IPYB.KLE_ID ,IPYB.OBJECT_VERSION_NUMBER, IPYB.date_from,
IPYB.ipy_type,IPYB.factor_code,IPYB.IPF_CODE,IPYB.date_to,IPYB.premium,IPYB.COVERED_AMOUNT,IPYB.ISS_CODE
FROM OKL_INS_POLICIES_B IPYB
WHERE IPYB.ID = c_ipy_id;
SELECT COUNT(*)
FROM okl_strm_elements STRE, OKL_STREAMS STR
WHERE STR.ID = STRE.STM_ID
AND STR.STY_ID = c_sty_id
AND STRE.DATE_BILLED IS NOT NULL
AND STR.KHR_ID = c_contract_id
AND STR.KLE_ID = c_contract_line_id;
SELECT ID
FROM OKL_STRM_TYPE_TL
WHERE NAME = ls_stm_code
AND LANGUAGE = 'US';
SELECT START_DATE
FROM okc_k_headers_b
WHERE id = c_khr_id ;
SELECT SUM(lessor_premium)
FROM OKL_INS_ASSETS OINB
WHERE OINB.IPY_ID = p_ipyv_rec.ID
GROUP BY OINB.IPY_ID;
SELECT SUM(STRE.AMOUNT)
FROM okl_strm_elements STRE, OKL_STREAMS STR
WHERE STR.ID = STRE.STM_ID
AND STR.STY_ID = p_stm_type_id
AND STRE.DATE_BILLED IS NOT NULL
AND STR.KHR_ID = p_ipyv_rec.KHR_ID
AND STR.KLE_ID = p_ipyv_rec.KLE_ID;
SELECT id
FROM okl_trx_types_tl
WHERE name = cp_name
AND language = cp_language;
SELECT ((INSURER_RATE * p_covered_amount )/100 )
FROM OKL_INS_POLICIES_B IPYB , OKL_INS_RATES INR
WHERE IPYB.ipt_id = inr.ipt_id AND
kle_id = p_ipyv_rec.KLE_ID and
khr_id = p_ipyv_rec.KHR_ID
AND IPYB.date_from between inr.date_FROM and DECODE(NVL(inr.date_TO,NULL),NULL,SYSDATE, inr.date_TO)
and IPYB.territory_code = inr.ic_id
AND IPYB.FACTOR_VALUE BETWEEN inr.FACTOR_RANGE_START AND inr.FACTOR_RANGE_END ;
insert_ap_request(p_api_version => l_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_tap_id => l_tra_id,
p_credit_amount => l_to_refund,
p_credit_sty_id => l_Adj_strm_type_id,
p_khr_id => p_ipyv_rec.khr_id,
p_kle_id => p_ipyv_rec.kle_id,
p_invoice_date => SYSDATE,
p_trx_id => l_trx_type_ID );
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug call insert_ap_request');
insert_ap_request(p_api_version => l_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_tap_id => l_tra_id,
p_credit_amount => l_to_refund,
p_credit_sty_id => l_pay_strm_type_id,
p_khr_id => p_ipyv_rec.khr_id,
p_kle_id => p_ipyv_rec.kle_id,
p_invoice_date => SYSDATE,
p_trx_id => l_trx_type_id );
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug call insert_ap_request');
SELECT SUM(amount)
--FROM OKL_STREAMS STM,
FROM OKL_STREAMS_REP_V STM, -- MGAAP 7263041
OKL_STRM_ELEMENTS STEM
where STM.STY_ID = l_stream_type_id
AND STM.KLE_ID = l_kle_id
AND STM.KHR_ID = l_khr_id
AND STM.ID = STEM.STM_ID
AND STEM.DATE_BILLED IS NOT NULL ;
SELECT SUM(amount)
--FROM OKL_STREAMS STM , OKL_STRM_ELEMENTS STEM
FROM OKL_STREAMS_REP_V STM , OKL_STRM_ELEMENTS STEM --MGAAP 7263041
WHERE STM.STY_ID = l_stream_type_id
AND STM.KLE_ID = l_kle_id
AND STM.KHR_ID = l_khr_id
AND STM.ID = STEM.STM_ID
AND STEM.ACCRUED_YN = 'Y'
--AND STM.PURPOSE_CODE IS NULL;
SELECT id
FROM okl_trx_types_tl
WHERE name = cp_name
AND language = cp_language;
SELECT A.MULTI_GAAP_YN, B.REPORTING_PDT_ID
FROM OKL_K_HEADERS A,
OKL_PRODUCTS B
WHERE A.ID = p_khr_id
AND A.PDT_ID = B.ID;
SELECT currency_code
,currency_conversion_type
-- ,currency_conversion_rate
,currency_conversion_date
FROM okl_k_headers_full_v
WHERE id = p_ipyv_rec.khr_id ;
SELECT khr.pdt_id pdt_id
FROM okl_k_headers_v khr
WHERE khr.ID = p_khr_id;
select ct.object1_id1 id
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 = chrId;
select mo_global.get_current_org_id() l_fnd_profile
from dual;
SELECT SUM(amount)
--FROM OKL_STREAMS STM , OKL_STRM_ELEMENTS STEM
FROM OKL_STREAMS_REP_V STM , OKL_STRM_ELEMENTS STEM -- MGAAP
WHERE STM.STY_ID = l_stream_type_id
AND STM.KLE_ID = l_kle_id
AND STM.KHR_ID = l_khr_id
AND STM.ID = STEM.STM_ID
AND STEM.DATE_BILLED IS NOT NULL;
SELECT SUM(amount)
--FROM OKL_STREAMS STM , OKL_STRM_ELEMENTS STEM
FROM OKL_STREAMS_REP_V STM , OKL_STRM_ELEMENTS STEM -- MGAAP
WHERE STM.STY_ID = l_stream_type_id
AND STM.KLE_ID = l_kle_id
AND STM.KHR_ID = l_khr_id
AND STM.ID = STEM.STM_ID
AND STEM.ACCRUED_YN = 'Y'
--AND STM.PURPOSE_CODE IS NULL;
SELECT id
FROM okl_trx_types_tl
WHERE name = cp_name
AND language = cp_language;
SELECT currency_code
,currency_conversion_type
-- ,currency_conversion_rate
,currency_conversion_date
FROM okl_k_headers_full_v
WHERE id = p_ipyv_rec.khr_id ;
SELECT khr.pdt_id pdt_id,
khr.multi_gaap_yn mylti_gaap_yn, -- MGAAP
pdt.reporting_pdt_id reporting_pdt_id
FROM okl_k_headers_v khr,
okl_products pdt
WHERE khr.ID = p_khr_id
AND khr.PDT_ID = pdt.ID;
select ct.object1_id1 id
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 = chrId;
select mo_global.get_current_org_id() l_fnd_profile
from dual;
SELECT ID
FROM okl_trx_types_tl
WHERE NAME = 'Billing' AND LANGUAGE = 'US';
SELECT id
FROM okl_trx_types_tl
WHERE NAME = cp_name
AND LANGUAGE = cp_language;
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRIPXB.pls call okl_credit_memo_pub.insert_request ');
Okl_Contract_Pub.update_contract_line
(
p_api_version => l_api_version ,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => l_return_status ,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data ,
p_clev_rec => l_clev_rec ,
p_klev_rec => l_klev_rec,
p_edit_mode =>'N' ,
x_clev_rec => lx_clev_rec,
x_klev_rec => lx_klev_rec
);
Okl_Contract_Pub.update_contract_line
(
p_api_version => l_api_version ,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => l_return_status ,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data ,
p_clev_rec => l_clev_rec ,
p_edit_mode =>'N' ,
p_klev_rec => l_klev_rec,
x_clev_rec => lx_clev_rec,
x_klev_rec => lx_klev_rec );
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRIPXB.pls call Okl_Ins_Policies_Pub.update_ins_policies ');
Okl_Ins_Policies_Pub.update_ins_policies(
p_api_version => p_api_version,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_ipyv_rec => l_ipyv_rec,
x_ipyv_rec => x_ipyv_rec
);
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRIPXB.pls call Okl_Ins_Policies_Pub.update_ins_policies ');
SELECT ID, IPY_TYPE, ISS_CODE
FROM OKL_INS_POLICIES_B
WHERE KHR_ID = p_contract_id
and ISS_CODE IN ('ACTIVE','ACCEPTED','PENDING')
and IPY_TYPE = 'LEASE_POLICY'
AND DATE_TO > p_cancellation_date; -- bug 4056603
SELECT ipy_id
FROM OKL_INS_POLICIES_B
WHERE KHR_ID = p_contract_id
AND ID = p_quote_id;
SELECT OST.STE_CODE
FROM OKC_K_HEADERS_V KHR , OKC_STATUSES_B OST
WHERE KHR.ID = p_khr_id
AND KHR.STS_CODE = OST.CODE ;
select chr.start_date ,chr.end_date
from OKC_K_HEADERS_B chr ,
OKL_TRX_CONTRACTS TRX
where chr.ORIG_SYSTEM_ID1 = p_khr_id
and chr.ORIG_SYSTEM_SOURCE_CODE = 'OKL_REBOOK'
and chr.orig_system_id1 = trx.khr_id
and chr.id = trx.khr_id_new
and chr.sts_code <> 'ABANDONED'
and trx.tsu_code <> 'PROCESSED'
and trx.representation_type = 'PRIMARY'; -- MGAAP 7263041
select max(major_version) from okc_k_headers_bh where ID =p_khr_id ;
select start_date ,end_date
From okc_k_headers_bh
where ID = p_khr_id
And major_version = l_maj_ver_num;
SELECT
ID,
ADJUSTMENT,
CALCULATED_PREMIUM,
OBJECT_VERSION_NUMBER,
AGENCY_NUMBER,
SFWT_FLAG,
IPF_CODE,
INT_ID,
KHR_ID,
ISU_ID,
IPT_ID,
IPY_ID,
IPE_CODE,
CRX_CODE,
AGENCY_SITE_ID,
ISS_CODE,
KLE_ID,
AGENT_SITE_ID,
IPY_TYPE,
POLICY_NUMBER,
QUOTE_YN,
ENDORSEMENT,
INSURANCE_FACTOR,
FACTOR_CODE,
COVERED_AMOUNT,
ADJUSTED_BY_ID,
FACTOR_VALUE,
DATE_QUOTED,
SALES_REP_ID,
DATE_PROOF_REQUIRED,
DATE_QUOTE_EXPIRY,
DEDUCTIBLE,
PAYMENT_FREQUENCY,
DATE_PROOF_PROVIDED,
DATE_FROM,
NAME_OF_INSURED,
DATE_TO,
DESCRIPTION,
ON_FILE_YN,
PREMIUM,
COMMENTS,
ACTIVATION_DATE,
PRIVATE_LABEL_YN,
LESSOR_INSURED_YN,
LESSOR_PAYEE_YN,
CANCELLATION_DATE,
CANCELLATION_COMMENT,
AGENT_YN,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ORG_ID,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
TERRITORY_CODE
FROM Okl_Ins_Policies_V
WHERE okl_ins_policies_v.id = p_id;
l_ipyv_rec.PROGRAM_UPDATE_DATE,
l_ipyv_rec.CREATED_BY,
l_ipyv_rec.CREATION_DATE,
l_ipyv_rec.LAST_UPDATED_BY,
l_ipyv_rec.LAST_UPDATE_DATE,
l_ipyv_rec.LAST_UPDATE_LOGIN,
l_ipyv_rec.TERRITORY_CODE;
delete_policy(
p_api_version => l_api_version,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_ipyv_rec => l_delipyv_rec,
x_ipyv_rec => lx_ipyv_rec);
--SELECT 'X' INTO l_vld_cncl_dt FROM DUAL
--WHERE p_cancellation_date BETWEEN lx_ipyv_rec.date_from AND lx_ipyv_rec.date_to;
Select CLMB.ID,CLMB.claim_date
From okl_ins_claims_B CLMB,
OKL_INS_POLICIES_V IPYV
WHERE CLMB.ipy_id = IPYV.id
AND trunc(CLMB.claim_date) >= trunc(c_qte_eff_date)
AND CLMB.CSU_CODE <> 'SUBMITTED'
AND IPYV.ISS_CODE ='ACTIVE'
AND IPYV.IPY_TYPE ='LEASE_POLICY'
AND IPYV.khr_id = c_khr_id;
select contract_number
From okc_k_headers_b
where id = c_khr_id;
SELECT IPYB.KHR_ID, IPYB.KLE_ID ,IPYB.OBJECT_VERSION_NUMBER, IPYB.date_from, IPYB.ipy_type,IPYB.factor_code,
IPYB.COVERED_AMOUNT
FROM OKL_INS_POLICIES_B IPYB
WHERE IPYB.ID = p_ipy_id;
SELECT ID
FROM OKL_INS_POLICIES_B IPYB
WHERE IPYB.IPY_TYPE = 'THIRD_PARTY_POLICY'
AND l_cancellation_date BETWEEN IPYB.date_from and IPYB.date_to;
SELECT SUM(amount)
FROM OKL_STREAMS STM,
OKL_STRM_ELEMENTS STEM
where STM.STY_ID = l_stream_type_id
AND STM.KLE_ID = l_kle_id
AND STM.KHR_ID = l_khr_id
AND STM.ID = STEM.STM_ID
AND STEM.DATE_BILLED IS NOT NULL ;
SELECT SUM(amount)
FROM OKL_STREAMS STM , OKL_STRM_ELEMENTS STEM
WHERE STM.STY_ID = l_stream_type_id
AND STM.KLE_ID = l_kle_id
AND STM.KHR_ID = l_khr_id
AND STM.ID = STEM.STM_ID
AND STEM.DATE_BILLED IS NOT NULL;
SELECT SUM(amount)
FROM OKL_STREAMS STM , OKL_STRM_ELEMENTS STEM
WHERE STM.STY_ID = l_stream_type_id
AND STM.KLE_ID = l_kle_id
AND STM.KHR_ID = l_khr_id
AND STM.ID = STEM.STM_ID
AND STEM.ACCRUED_YN = 'Y'
AND STM.PURPOSE_CODE IS NULL;
SELECT SUM(amount)
FROM OKL_STREAMS STM , OKL_STRM_ELEMENTS STEM
WHERE STM.STY_ID = l_stream_type_id
AND STM.KLE_ID = l_kle_id
AND STM.KHR_ID = l_khr_id
AND STM.ID = STEM.STM_ID
AND STEM.ACCRUED_YN = 'Y'
AND STM.PURPOSE_CODE IS NULL;
SELECT id
FROM okl_trx_types_tl
WHERE name = cp_name
AND language = cp_language;
select ID
from OKL_STRM_TYPE_TL
where NAME = ls_stm_code
AND LANGUAGE = 'US';
SELECT currency_code
,currency_conversion_type
,currency_conversion_date
FROM okl_k_headers_full_v
WHERE id = p_ipyv_rec.khr_id ;
SELECT khr.pdt_id pdt_id
FROM okl_k_headers_v khr
WHERE khr.ID = p_khr_id;
select ct.object1_id1 id
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 = chrId;
select mo_global.get_current_org_id() l_fnd_profile
from dual;
SELECT max (stre.date_billed)
FROM okl_strm_elements STRE,
OKL_STREAMS STR
WHERE STR.ID = STRE.STM_ID
AND STR.STY_ID = c_sty_id
AND STRE.DATE_BILLED IS NOT NULL
AND STR.KHR_ID = c_khr_id
AND STR.KLE_ID = c_kle_id;
Okl_Contract_Pub.update_contract_line
(
p_api_version => l_api_version ,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => l_return_status ,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data ,
p_clev_rec => l_clev_rec ,
p_klev_rec => l_klev_rec,
p_edit_mode =>'N' ,
x_clev_rec => lx_clev_rec,
x_klev_rec => lx_klev_rec
);
Okl_Contract_Pub.update_contract_line
(
p_api_version => l_api_version ,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => l_return_status ,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data ,
p_clev_rec => l_clev_rec ,
p_edit_mode =>'N' ,
p_klev_rec => l_klev_rec,
x_clev_rec => lx_clev_rec,
x_klev_rec => lx_klev_rec );
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRIPXB.pls call Okl_Ins_Policies_Pub.update_ins_policies ');
Okl_Ins_Policies_Pub.update_ins_policies(
p_api_version => p_api_version,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_ipyv_rec => l_ipyv_rec,
x_ipyv_rec => x_ipyv_rec
);
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRIPXB.pls call Okl_Ins_Policies_Pub.update_ins_policies ');
SELECT KHR_ID , KLE_ID
FROM OKL_INS_POLICIES_B
WHERE ID = p_policy_id;
SELECT id
FROM okl_trx_types_tl
WHERE name = cp_name
AND language = cp_language;
SELECT id
FROM OKL_INS_POLICIES_B
WHERE KLE_ID = p_contract_line;
select ID
from OKL_STRM_TYPE_TL
where NAME = ls_stm_code
AND LANGUAGE = 'US';
select merge_reason_code
into l_merge_reason_code
from hz_merge_batch
where batch_id = p_batch_id;
UPDATE OKL_INS_POLICIES_B IPYB
SET IPYB.ISU_ID = p_to_fk_id
,IPYB.object_version_number = IPYB.object_version_number + 1
,IPYB.last_update_date = SYSDATE
,IPYB.last_updated_by = arp_standard.profile.user_id
,IPYB.last_update_login = arp_standard.profile.last_update_login
WHERE IPYB.ISU_ID = p_from_fk_id ;
arp_message.set_name('AR','AR_ROWS_UPDATED');
select merge_reason_code
into l_merge_reason_code
from hz_merge_batch
where batch_id = p_batch_id;
UPDATE OKL_INS_POLICIES_B IPYB
SET IPYB.AGENCY_SITE_ID = p_to_fk_id
,IPYB.object_version_number = IPYB.object_version_number + 1
,IPYB.last_update_date = SYSDATE
,IPYB.last_updated_by = arp_standard.profile.user_id
,IPYB.last_update_login = arp_standard.profile.last_update_login
WHERE IPYB.AGENCY_SITE_ID = p_from_fk_id ;
arp_message.set_name('AR','AR_ROWS_UPDATED');
select merge_reason_code
into l_merge_reason_code
from hz_merge_batch
where batch_id = p_batch_id;
UPDATE OKL_INS_POLICIES_B IPYB
SET IPYB.INT_ID = p_to_fk_id
,IPYB.object_version_number = IPYB.object_version_number + 1
,IPYB.last_update_date = SYSDATE
,IPYB.last_updated_by = arp_standard.profile.user_id
,IPYB.last_update_login = arp_standard.profile.last_update_login
WHERE IPYB.INT_ID = p_from_fk_id ;
arp_message.set_name('AR','AR_ROWS_UPDATED');
select merge_reason_code
into l_merge_reason_code
from hz_merge_batch
where batch_id = p_batch_id;
UPDATE OKL_INS_POLICIES_B IPYB
SET IPYB.AGENT_SITE_ID = p_to_fk_id
,IPYB.object_version_number = IPYB.object_version_number + 1
,IPYB.last_update_date = SYSDATE
,IPYB.last_updated_by = arp_standard.profile.user_id
,IPYB.last_update_login = arp_standard.profile.last_update_login
WHERE IPYB.AGENT_SITE_ID = p_from_fk_id ;
arp_message.set_name('AR','AR_ROWS_UPDATED');