[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select 'x'
from
OKC_K_PARTY_ROLES_B CPLB
where CPLB.CHR_ID = p_khr_id
and CPLB.DNZ_CHR_ID = p_khr_id
and CPLB.OBJECT1_ID1 = p_isu_id
and CPLB.JTOT_OBJECT1_CODE = 'OKX_PARTY'
and CPLB.RLE_CODE = 'EXTERNAL_PARTY';
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRINQB.pls call Okl_Ins_Policies_Pub.insert_ins_policies ');
Okl_Ins_Policies_Pub.insert_ins_policies(
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 => p_ipyv_rec,
x_ipyv_rec => x_ipyv_rec
);
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRINQB.pls call Okl_Ins_Policies_Pub.insert_ins_policies ');
SELECT 'x'
FROM OKL_INS_POLICIES_B
WHERE lease_application_id = p_lapp_id
AND IPY_TYPE = 'THIRD_PARTY_POLICY'
AND KHR_ID IS NULL -- not a contract yet
AND TRUNC(nvl(DATE_TO,SYSDATE)) > TRUNC(SYSDATE);
,'Begin Debug OKLRINQB.pls call Okl_Ins_Policies_Pub.insert_ins_policies');
Okl_Ins_Policies_Pub.insert_ins_policies(
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_ipyv_rec => p_ipyv_rec,
x_ipyv_rec => x_ipyv_rec);
,'End Debug OKLRINQB.pls call Okl_Ins_Policies_Pub.insert_ins_policies');
SELECT ORIG_SYSTEM_ID1
FROM OKC_K_HEADERS_B
WHERE id = p_lakhr_id;
SELECT ID
, ISU_ID
, OBJECT_VERSION_NUMBER
FROM OKL_INS_POLICIES_B
WHERE lease_application_id = c_lease_app_id
AND IPY_TYPE = 'THIRD_PARTY_POLICY'
AND TRUNC(nvl(DATE_TO,SYSDATE)) > TRUNC(SYSDATE);
select 'x'
from OKC_K_PARTY_ROLES_B CPLB
where CPLB.CHR_ID = p_khr_id
and CPLB.DNZ_CHR_ID = p_khr_id
and CPLB.OBJECT1_ID1 = p_isu_id
and CPLB.JTOT_OBJECT1_CODE = 'OKX_PARTY'
and CPLB.RLE_CODE = 'EXTERNAL_PARTY';
l_ipyv_rec.khr_id := p_lakhr_id; -- Update existing record with contract id
,'Begin Debug OKLRINQB.pls call Okl_Ins_Policies_Pub.update_ins_policies');
Okl_Ins_Policies_Pub.update_ins_policies(
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_ipyv_rec => l_ipyv_rec,
x_ipyv_rec => x_ipyv_rec);
,'End Debug OKLRINQB.pls call Okl_Ins_Policies_Pub.update_ins_policies');
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 ;
select OKL_SIF_SEQ.nextval INTO p_stmv_rec.transaction_number from dual;
select MULTI_GAAP_YN
from okl_k_headers
WHERE ID = l_ipyv_rec.khr_id ;
select PDT_ID
from okl_k_headers
WHERE ID = l_ipyv_rec.khr_id ;
select OKL_SIF_SEQ.nextval INTO p_stmv_rec.transaction_number from dual;
select OKL_SIF_SEQ.nextval INTO p_stmv_rec.transaction_number from dual;
select MULTI_GAAP_YN
from okl_k_headers
WHERE ID = p_khr_id ;
select PDT_ID
from okl_k_headers
WHERE ID = p_khr_id ;
select OKL_SIF_SEQ.nextval INTO p_stmv_rec.transaction_number from dual;
select OKL_SIF_SEQ.nextval INTO p_stmv_rec.transaction_number from dual;
SELECT 'x'
FROM OKL_K_LINES
WHERE OKL_K_LINES.ID = l_kle_id;
SELECT START_DATE,END_DATE
FROM OKC_K_HEADERS_V
WHERE OKC_K_HEADERS_V.ID = p_khr_id;
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 ;
SELECT 'x'
FROM OKL_K_HEADERS_V
WHERE OKL_K_HEADERS_V.ID = l_khr_id;
IF (x_khr_status = 'CANCELED' ) OR (x_khr_status = 'DELETED' ) OR (x_khr_status = 'EXPIRED' ) THEN
OKC_API.set_message(G_APP_NAME,'OKL_INS_K_NOT_ACTIVE' );
select OKL_SIF_SEQ.nextval INTO p_stmv_rec.transaction_number from dual;
SELECT id
FROM OKC_LINE_STYLES_b
WHERE LTY_CODE = 'INSURANCE' ;
SELECT CURRENCY_CODE
FROM OKC_K_HEADERS_B
WHERE ID = p_khr_id ;
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;
select 'x'
FROM OKL_INS_POLICIES_B IPYB
WHERE IPYB.IPY_TYPE = 'LEASE_POLICY'
AND IPYB.ISS_CODE IN ('ACCEPTED', 'PENDING')
AND IPYB.KHR_ID = p_khr_id
AND (p_date BETWEEN IPYB.DATE_FROM AND IPYB.DATE_TO
OR IPYB.DATE_FROM between p_date and p_to_date);
SELECT 'x'
FROM OKL_INS_POLICIES_B OIPB
WHERE OIPB.KHR_ID = p_khr_id AND
OIPB.IPY_TYPE = 'LEASE_POLICY' AND
OIPB.QUOTE_YN = 'N' AND
OIPB.ISS_CODE = 'ACTIVE' AND
(p_date BETWEEN OIPB.DATE_FROM AND OIPB.DATE_TO
OR OIPB.DATE_FROM BETWEEN p_date and p_to_date);
FUNCTION insert_policy_assets (
l_inqv_rec IN ipyv_rec_type,
policyid IN NUMBER
) RETURN VARCHAR2 IS
CURSOR okl_inav_pk_csr (p_id NUMBER) IS
SELECT
ID,
OBJECT_VERSION_NUMBER,
IPY_ID,
KLE_ID,
ASSET_PREMIUM,
LESSOR_PREMIUM,
CALCULATED_PREMIUM,
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
FROM OKL_INS_ASSETS
WHERE OKL_INS_ASSETS.IPY_ID = p_id;
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_inav_rec.REQUEST_ID,
l_inav_rec.PROGRAM_APPLICATION_ID,
l_inav_rec.PROGRAM_ID,
l_inav_rec.PROGRAM_UPDATE_DATE,
l_inav_rec.org_id FROM dual;
l_inav_rec.LAST_UPDATED_BY := l_okl_inav_pk_csr.LAST_UPDATED_BY ;
l_inav_rec.LAST_UPDATE_DATE := l_okl_inav_pk_csr.LAST_UPDATE_DATE ;
l_inav_rec.LAST_UPDATE_LOGIN := l_okl_inav_pk_csr.LAST_UPDATE_LOGIN ;
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRINQB.pls call Okl_Ins_Assets_Pub.insert_ins_assets ');
Okl_Ins_Assets_Pub.insert_ins_assets(
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_inav_rec => l_inav_rec,
x_inav_rec => lx_inav_rec
);
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRINQB.pls call Okl_Ins_Assets_Pub.insert_ins_assets ');
END insert_policy_assets;
SELECT POLICY_SYMBOL
FROM OKL_INS_PRODUCTS_B
WHERE id = l_ipyv_rec.ipt_id;
SELECT OKL_IPY_SEQ.NEXTVAL INTO l_seq 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_ipyv_rec.REQUEST_ID,
l_ipyv_rec.PROGRAM_APPLICATION_ID,
l_ipyv_rec.PROGRAM_ID,
l_ipyv_rec.PROGRAM_UPDATE_DATE,
l_ipyv_rec.org_id FROM dual;
l_ipyv_rec.last_updated_by := OKC_API.G_MISS_NUM;
l_ipyv_rec.last_update_date := OKC_API.G_MISS_DATE ;
l_ipyv_rec.last_update_login := OKC_API.G_MISS_NUM;
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRINQB.pls call Okl_Ins_Policies_Pub.insert_ins_policies ');
Okl_Ins_Policies_Pub.insert_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 => lx_ipyv_rec
);
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRINQB.pls call Okl_Ins_Policies_Pub.insert_ins_policies ');
-- Insert Assets
l_return_status := insert_policy_assets(l_inqv_rec,lx_ipyv_rec.ID );
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRINQB.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 => lx_ipyv_rec
);
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRINQB.pls call Okl_Ins_Policies_Pub.update_ins_policies ');
-- update quote
-- Put Policy Number in Quote record
l_inqv_rec.IPY_ID := lx_ipyv_rec.ID;
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRINQB.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_inqv_rec,
x_ipyv_rec => lx_inqv_rec );
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRINQB.pls call Okl_Ins_Policies_Pub.update_ins_policies ');
SELECT POLICY_SYMBOL , FACTOR_NAME ,FACTOR_CODE
FROM OKL_INS_PRODUCTS_V
WHERE id = product_id;
SELECT OKL_IPY_SEQ.NEXTVAL INTO l_seq FROM dual;
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRINQB.pls call Okl_Ins_Policies_Pub.insert_ins_policies ');
Okl_Ins_Policies_Pub.insert_ins_policies(
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
);
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRINQB.pls call Okl_Ins_Policies_Pub.insert_ins_policies ');
SELECT OKL_IPY_SEQ.NEXTVAL INTO l_seq FROM dual;
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRINQB.pls call Okl_Ins_Policies_Pub.insert_ins_policies ');
Okl_Ins_Policies_Pub.insert_ins_policies(
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
);
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRINQB.pls call Okl_Ins_Policies_Pub.insert_ins_policies ');
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRINQB.pls call Okl_Ins_Assets_Pub.insert_ins_assets ');
Okl_Ins_Assets_Pub.insert_ins_assets(
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_inav_tbl => l_inav_tbl,
x_inav_tbl => lx_inav_tbl
);
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRINQB.pls call Okl_Ins_Assets_Pub.insert_ins_assets ');
SELECT OTAT.DESCRIPTION ASSET_DESCRIPTION,
OTAB.current_units QUANTITY,
KLE_TOP.OEC OEC,
TL.NAME ASSET_CATEGORY,
OICC.IAC_CODE INSURANCE_CLASS_code,
KLE_TOP.ID KLE_ID,
OTAB.ASSET_NUMBER ASSET_NUMBER
FROM OKL_TXL_ASSETS_B OTAB,
OKL_TXL_ASSETS_TL OTAT,
OKX_ASST_CATGRS_V TL,
OKL_INS_CLASS_CATS OICC,
OKL_K_LINES KLE,
OKL_K_LINES KLE_TOP,
OKC_K_LINES_B CLE
WHERE KLE.ID = OTAB.KLE_ID
AND KLE.ID = CLE.ID
AND CLE.CLE_ID = KLE_TOP.ID
AND TL.CATEGORY_ID = OTAB.DEPRECIATION_ID
AND OTAB.ID = OTAT.ID
AND OICC.IAY_ID = OTAB.DEPRECIATION_ID
AND SYSDATE BETWEEN OICC.DATE_FROM AND NVL(OICC.DATE_TO, SYSDATE +1)
AND OTAT.LANGUAGE = USERENV ('LANG')
AND NOT EXISTS
(Select '1'
from okc_k_items cim
where cim.cle_id = otab.kle_id
AND cim.object1_id1 is not null)
AND CLE.STS_CODE NOT IN ( 'TERMINATED' , 'EXPIRED','ABANDONED' )
AND OTAB.DNZ_KHR_ID = p_k_id
UNION
SELECT OTAT.DESCRIPTION ASSET_DESCRIPTION,
OTAB.current_units QUANTITY,
KLE_TOP.OEC OEC,
TL.NAME ASSET_CATEGORY,
OICC.IAC_CODE INSURANCE_CLASS_code,
KLE_TOP.ID KLE_ID,
OTAB.ASSET_NUMBER ASSET_NUMBER
FROM OKL_TXL_ASSETS_B OTAB,
OKL_TXL_ASSETS_TL OTAT,
OKX_ASST_CATGRS_V TL,
OKL_INS_CLASS_CATS OICC,
OKL_K_LINES KLE ,
OKL_K_LINES KLE_TOP ,
OKC_K_LINES_B CLE ,
OKL_K_HEADERS KHR
WHERE KLE.ID = OTAB.KLE_ID
AND KLE.ID = CLE.ID
AND CLE.CLE_ID = KLE_TOP.ID
AND TL.CATEGORY_ID = KLE_TOP.ITEM_INSURANCE_CATEGORY
AND OTAB.ID = OTAT.ID
AND OICC.IAY_ID = KLE_TOP.ITEM_INSURANCE_CATEGORY
AND SYSDATE BETWEEN OICC.DATE_FROM AND NVL(OICC.DATE_TO, SYSDATE +1)
AND OTAT.LANGUAGE = USERENV('LANG')
AND NOT EXISTS
(Select '1'
from okc_k_items cim
where cim.cle_id = otab.kle_id
AND cim.object1_id1 is not null)
AND khr.id = CLE.DNZ_CHR_ID
AND khr.deal_type = 'LOAN'
AND CLE.STS_CODE NOT IN ( 'TERMINATED' , 'EXPIRED','ABANDONED','BOOKED' )
AND OTAB.DNZ_KHR_ID = p_k_id
UNION
SELECT OTL.ITEM_DESCRIPTION ASSET_DESCRIPTION,
MODEL.NUMBER_OF_ITEMS QUANTITY,
KLE.OEC OEC,
TL.NAME ASSET_CATEGORY,
OICC.IAC_CODE INSURANCE_CLASS_code,
KLE.ID KLE_ID,
FAD.ASSET_NUMBER ASSET_NUMBER
FROM OKL_K_LINES KLE ,
OKC_K_LINES_B CLE,
OKC_LINE_STYLES_B LS,
OKC_K_ITEMS CIM,
OKC_K_LINES_TL OTL,
OKC_K_LINES_B ITEM_CLE,
OKC_LINE_STYLES_B ITEM_LS,
OKC_K_ITEMS MODEL ,
OKX_ASST_CATGRS_V TL,
OKL_INS_CLASS_CATS OICC,
FA_ADDITIONS_B FAd,
OKC_K_LINES_B FINAC_CLE,
OKC_LINE_STYLES_B FINAC_LS
WHERE FINAC_LS.LTY_CODE = 'FREE_FORM1'
AND FINAC_CLE.LSE_ID = FINAC_LS.ID
AND FINAC_CLE.ID = KLE.ID
AND OICC.IAY_ID = FAD.ASSET_CATEGORY_ID
AND SYSDATE BETWEEN OICC.DATE_FROM AND NVL(OICC.DATE_TO, SYSDATE +1)
AND FAD.ASSET_ID = CIM.OBJECT1_ID1
AND CIM.OBJECT1_ID2 = '#'
AND TL.CATEGORY_ID = FAD.ASSET_CATEGORY_ID
AND MODEL.JTOT_OBJECT1_CODE = 'OKX_SYSITEM'
AND MODEL.DNZ_CHR_ID = ITEM_CLE.DNZ_CHR_ID
AND MODEL.cle_id = ITEM_CLE.ID
AND ITEM_LS.LTY_CODE = 'ITEM'
AND ITEM_LS.ID = ITEM_CLE.LSE_ID
AND ITEM_CLE.CLE_ID = FINAC_CLE.ID
AND OTL.ID = CLE.ID
AND OTL.LANGUAGE = USERENV('LANG')
AND CIM.DNZ_CHR_ID = CLE.DNZ_CHR_ID
AND CIM.CLE_ID = CLE.ID
AND CIM.JTOT_OBJECT1_CODE = 'OKX_ASSET'
AND LS.ID = CLE.LSE_ID
AND LS.LTY_CODE = 'FIXED_ASSET'
AND CLE.CLE_ID = FINAC_CLE.ID
AND FINAC_CLE.STS_CODE NOT IN ( 'TERMINATED' , 'EXPIRED','ABANDONED' )
AND CLE.DNZ_CHR_ID = p_k_id
UNION
SELECT OTL.ITEM_DESCRIPTION ASSET_DESCRIPTION,
MODEL.NUMBER_OF_ITEMS QUANTITY,
KLE.OEC OEC,
TL.NAME ASSET_CATEGORY,
OICC.IAC_CODE INSURANCE_CLASS_code,
KLE.ID KLE_ID,
FINAN_CLET.NAME ASSET_NUMBER
FROM OKL_K_LINES KLE,
OKX_ASST_CATGRS_V TL,
OKL_INS_CLASS_CATS OICC,
OKC_K_LINES_B CLE,
OKC_K_LINES_TL OTL,
OKC_LINE_STYLES_B LS,
OKC_K_LINES_B ITEM_CLE,
OKC_LINE_STYLES_B ITEM_LS,
OKC_K_ITEMS MODEL ,
OKL_K_HEADERS KHR ,
OKC_K_LINES_B FINAN_CLE ,
OKC_K_LINES_TL FINAN_CLET
WHERE MODEL.cle_id = ITEM_CLE.ID
AND MODEL.DNZ_CHR_ID = ITEM_CLE.DNZ_CHR_ID
AND ITEM_LS.LTY_CODE = 'ITEM'
AND ITEM_LS.ID = ITEM_CLE.LSE_ID
AND ITEM_CLE.CLE_ID = FINAN_CLE.ID
AND ITEM_CLE.DNZ_CHR_ID = FINAN_CLE.DNZ_CHR_ID
AND CLE.CLE_ID = FINAN_CLE.ID
AND CLE.DNZ_CHR_ID = FINAN_CLE.DNZ_CHR_ID
AND TL.CATEGORY_ID = kle.ITEM_INSURANCE_CATEGORY
AND OICC.IAY_ID = kle.ITEM_INSURANCE_CATEGORY
AND SYSDATE BETWEEN OICC.DATE_FROM AND NVL(OICC.DATE_TO, SYSDATE +1)
AND OTL.ID = CLE.ID
AND OTL.LANGUAGE = USERENV('LANG')
AND LS.ID = CLE.LSE_ID
AND LS.LTY_CODE = 'FIXED_ASSET'
AND FINAN_CLE.STS_CODE NOT IN ( 'TERMINATED' , 'EXPIRED','ABANDONED' )
AND FINAN_CLET.LANGUAGE = USERENV('LANG')
AND FINAN_CLET.ID = FINAN_CLE.ID
AND KLE.ID = FINAN_CLE.ID
AND FINAN_CLE.DNZ_CHR_ID = KHR.ID
AND FINAN_CLE.CHR_ID = KHR.ID
AND FINAN_CLE.CLE_ID is null
AND KHR.DEAL_TYPE = 'LOAN'
AND CLE.DNZ_CHR_ID = p_k_id;
SELECT 'x'
FROM OKL_INS_EXCLUSIONS_B
WHERE COUNTRY_ID = p_country_code
AND COLL_CODE = p_asset_category;
SELECT 'x'
FROM OKL_INS_EXCLUSIONS_B
WHERE COUNTRY_ID = p_country_code
AND COLL_CODE = p_asset_category
AND SIC_CODE = p_sic_code;
select 'x',OTAB.ASSET_NUMBER
from OKL_TXL_ASSETS_B OTAB,
OKL_K_LINES KLE,
OKC_K_LINES_B CLE,
OKC_LINE_STYLES_B LS
WHERE OTAB.DNZ_KHR_ID = p_khr_id AND
KLE.ID = OTAB.KLE_ID
AND CLE.ID = KLE.ID
AND LS.ID = CLE.LSE_ID
AND LS.LTY_CODE = 'FIXED_ASSET' -- Bug# 4102231
and CLE.STS_CODE NOT IN ( 'TERMINATED' , 'EXPIRED','ABANDONED' )
and OTAB.DEPRECIATION_ID IS NULL
UNION
SELECT 'x', FAD.ASSET_NUMBER
from
OKC_K_LINES_B CLE,
OKC_LINE_STYLES_B LS,
OKC_K_ITEMS CIM,
FA_ADDITIONS_B FAD
where
FAD.ASSET_CATEGORY_ID IS NULL
AND FAD.ASSET_ID = CIM.OBJECT1_ID1
AND CIM.DNZ_CHR_ID = CLE.DNZ_CHR_ID
AND CIM.JTOT_OBJECT1_CODE = 'OKX_ASSET'
AND CIM.OBJECT1_ID1 = '#'
AND CIM.CLE_ID = CLE.ID
AND LS.LTY_CODE = 'FIXED_ASSET'
AND LS.ID = CLE.LSE_ID
and CLE.DNZ_CHR_ID = p_khr_id
and CLE.STS_CODE NOT IN ( 'TERMINATED' , 'EXPIRED','ABANDONED' ) ;
SELECT SUM(KLE.CAPITAL_AMOUNT) --,SUM(KLE.OEC)
FROM OKC_K_LINES_B CLEB,OKL_K_LINES KLE
WHERE CLEB.ID = KLE.ID
AND CLEB.DNZ_CHR_ID = p_khr_id
AND CLEB.CLE_ID IS NULL
GROUP BY CLEB.DNZ_CHR_ID ;
SELECT MONTHS_BETWEEN(END_DATE,START_DATE), CONTRACT_NUMBER ----20-Jan-2005 Bug# 4056484 PAGARG removing rounding
FROM OKC_K_HEADERS_B
WHERE ID = p_khr_id ;
SELECT SUM(OEC)
FROM
(
SELECT OTAB.DNZ_KHR_ID CONTRACT_ID,
KLE_TOP.OEC OEC,
OTAB.ASSET_NUMBER ASSET_NUMBER
FROM OKL_TXL_ASSETS_B OTAB,
OKL_K_LINES KLE,
OKL_K_LINES KLE_TOP,
OKC_K_LINES_B CLE
WHERE KLE.ID = OTAB.KLE_ID
AND KLE.ID = CLE.ID
AND CLE.CLE_ID = KLE_TOP.ID
AND NOT EXISTS
(Select '1'
from okc_k_items cim
where cim.cle_id = otab.kle_id
AND cim.object1_id1 is not null)
AND CLE.STS_CODE NOT IN ( 'TERMINATED' , 'EXPIRED','ABANDONED' )
AND OTAB.DNZ_KHR_ID = p_khr_id
UNION
SELECT OTAB.DNZ_KHR_ID CONTRACT_ID,KLE_TOP.OEC OEC,
OTAB.ASSET_NUMBER ASSET_NUMBER
FROM OKL_TXL_ASSETS_B OTAB,
OKL_K_LINES KLE ,
OKL_K_LINES KLE_TOP ,
OKC_K_LINES_B CLE ,
OKL_K_HEADERS KHR
WHERE KLE.ID = OTAB.KLE_ID
AND KLE.ID = CLE.ID
AND CLE.CLE_ID = KLE_TOP.ID
AND NOT EXISTS
(Select '1'
from okc_k_items cim
where cim.cle_id = otab.kle_id
AND cim.object1_id1 is not null)
AND khr.id = CLE.DNZ_CHR_ID
AND khr.deal_type = 'LOAN'
AND CLE.STS_CODE NOT IN ( 'TERMINATED' , 'EXPIRED','ABANDONED','BOOKED' )
AND OTAB.DNZ_KHR_ID = p_khr_id
UNION
select CLE.DNZ_CHR_ID CONTRACT_ID,
KLE.OEC OEC,
FAD.ASSET_NUMBER ASSET_NUMBER
from OKL_K_LINES KLE ,
OKC_K_LINES_B CLE,
OKC_LINE_STYLES_B LS,
OKC_K_ITEMS CIM,
OKC_K_LINES_B ITEM_CLE,
OKC_LINE_STYLES_B ITEM_LS,
OKC_K_ITEMS MODEL ,
FA_ADDITIONS_B FAd,
OKC_K_LINES_B FINAC_CLE,
OKC_LINE_STYLES_B FINAC_LS
where FINAC_LS.LTY_CODE = 'FREE_FORM1'
AND FINAC_CLE.LSE_ID = FINAC_LS.ID
AND FINAC_CLE.ID = KLE.ID
AND FAD.ASSET_ID = CIM.OBJECT1_ID1
AND CIM.OBJECT1_ID2 = '#'
AND MODEL.JTOT_OBJECT1_CODE = 'OKX_SYSITEM'
AND MODEL.DNZ_CHR_ID = CLE.DNZ_CHR_ID
AND MODEL.cle_id = ITEM_CLE.ID
AND ITEM_LS.LTY_CODE = 'ITEM'
AND ITEM_LS.ID = ITEM_CLE.LSE_ID
AND ITEM_CLE.CLE_ID = FINAC_CLE.ID
AND CIM.DNZ_CHR_ID = CLE.DNZ_CHR_ID
AND CIM.CLE_ID = CLE.ID
AND CIM.JTOT_OBJECT1_CODE = 'OKX_ASSET'
AND LS.ID = CLE.LSE_ID
AND LS.LTY_CODE = 'FIXED_ASSET'
AND CLE.CLE_ID = FINAC_CLE.ID
AND FINAC_CLE.STS_CODE NOT IN ( 'TERMINATED' , 'EXPIRED','ABANDONED' )
AND CLE.DNZ_CHR_ID = p_khr_id
union
SELECT CLE.DNZ_CHR_ID CONTRACT_ID,
KLE.OEC OEC,
FINAN_CLET.NAME ASSET_NUMBER
FROM OKL_K_LINES KLE,
OKC_K_LINES_B CLE,
OKC_LINE_STYLES_B LS,
OKC_K_LINES_B ITEM_CLE,
OKC_LINE_STYLES_B ITEM_LS,
OKC_K_ITEMS MODEL ,
OKL_K_HEADERS KHR ,
OKC_K_LINES_B FINAN_CLE ,
OKC_K_LINES_TL FINAN_CLET
WHERE MODEL.cle_id = ITEM_CLE.ID
AND MODEL.DNZ_CHR_ID = ITEM_CLE.DNZ_CHR_ID
AND ITEM_LS.LTY_CODE = 'ITEM'
AND ITEM_LS.ID = ITEM_CLE.LSE_ID
AND ITEM_CLE.CLE_ID = FINAN_CLE.ID
AND ITEM_CLE.DNZ_CHR_ID = FINAN_CLE.DNZ_CHR_ID
AND CLE.CLE_ID = FINAN_CLE.ID
AND CLE.DNZ_CHR_ID = FINAN_CLE.DNZ_CHR_ID
AND LS.ID = CLE.LSE_ID
AND LS.LTY_CODE = 'FIXED_ASSET'
AND FINAN_CLE.STS_CODE NOT IN ( 'TERMINATED' , 'EXPIRED','ABANDONED' )
AND FINAN_CLET.LANGUAGE = USERENV('LANG')
AND FINAN_CLET.ID = FINAN_CLE.ID
AND KLE.ID = FINAN_CLE.ID
AND FINAN_CLE.DNZ_CHR_ID = KHR.ID
AND FINAN_CLE.CHR_ID = KHR.ID
AND FINAN_CLE.CLE_ID is null
AND KHR.DEAL_TYPE = 'LOAN'
AND CLE.DNZ_CHR_ID = p_khr_id)
GROUP BY CONTRACT_ID;
SELECT COUNT(*) --Bug:3825159
FROM OKL_INS_PRODUCTS_B IPTB,
MTL_SYSTEM_ITEMS_B_KFV MSIB
WHERE IPTB.IPD_ID =MSIB.INVENTORY_ITEM_ID
AND iptb.isu_id = p_isu_id
AND IPTB.IPT_TYPE = 'LEASE_PRODUCT'
AND p_total_oec BETWEEN IPTB.FACTOR_MIN AND IPTB.FACTOR_MAX
AND p_from_date BETWEEN IPTB.DATE_FROM AND DECODE(IPTB.DATE_TO,NULL,p_from_date,IPTB.DATE_TO)
AND MSIB.ORGANIZATION_ID = p_inv_org_id;
SELECT iptb.ID ,iptt.NAME --Bug:3825159
FROM OKL_INS_PRODUCTS_TL IPTT,
OKL_INS_PRODUCTS_B IPTB,
MTL_SYSTEM_ITEMS_B_KFV MSIB
WHERE IPTB.ID = IPTT.ID
AND IPTT.LANGUAGE = USERENV('LANG')
AND IPTB.IPD_ID = MSIB.INVENTORY_ITEM_ID
AND iptb.isu_id = p_isu_id
AND IPTB.IPT_TYPE = 'LEASE_PRODUCT'
AND p_total_oec BETWEEN IPTB.FACTOR_MIN AND IPTB.FACTOR_MAX
AND p_from_date BETWEEN IPTB.DATE_FROM AND DECODE(IPTB.DATE_TO,NULL,p_from_date,IPTB.DATE_TO)
AND MSIB.ORGANIZATION_ID =p_inv_org_id;
SELECT ((INSURED_RATE * p_oec )/100 ) * p_freq_factor,((INSURER_RATE * p_oec )/100 ) * p_freq_factor
FROM OKL_INS_RATES INR
WHERE INR.IPT_ID = p_ipt_id
AND INR.IAC_CODE = p_ins_class
AND INR.IC_ID = p_location_code
AND p_oec BETWEEN INR.FACTOR_RANGE_START AND INR.FACTOR_RANGE_END
AND p_from_date BETWEEN INR.DATE_FROM AND DECODE(INR.DATE_TO,NULL,p_from_date,INR.DATE_TO) ;
SELECT 'x'
FROM OKX_INS_PROVIDER_V
WHERE PARTY_ID = p_isu_id ;
SELECT currency_code , deal_type, AUTHORING_ORG_ID
FROM okl_k_headers_full_v
WHERE id = p_khr_id;
select 'x',CLE.NAME , cle.id
from OKL_K_LINES KLE,
OKC_K_LINES_V CLE, OKC_LINE_STYLES_B LS
WHERE CLE.ID = KLE.ID
and CLE.STS_CODE NOT IN ( 'TERMINATED' , 'EXPIRED','ABANDONED' )
and KLE.ITEM_INSURANCE_CATEGORY IS NULL
AND LS.LTY_CODE = 'FREE_FORM1'
AND LS.ID = CLE.LSE_ID
AND CLE.chr_id = p_khr_id ;
SELECT mtl.ASSET_CATEGORY_ID, MTL.DESCRIPTION
FROM
MTL_SYSTEM_ITEMS MTL,
OKC_K_items FA_ITEM ,
OKC_K_LINES_B MODEL,
OKC_LINE_STYLES_B LS_MODEL
WHERE
MTL.INVENTORY_ITEM_ID = FA_ITEM.object1_id1
AND MTL.ORGANIZATION_ID = FA_ITEM.object1_id2
AND FA_ITEM.JTOT_OBJECT1_CODE = 'OKX_SYSITEM'
AND MODEL.ID = FA_ITEM.cle_id
AND LS_MODEL.ID = MODEL.LSE_ID
AND LS_MODEL.LTY_CODE = 'ITEM'
and MODEL.cle_id =p_financial_line_id;
Okl_Contract_Pub.update_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_clev_rec => l_clev_rec ,
p_klev_rec => l_klev_rec,
x_clev_rec => lx_clev_rec,
x_klev_rec => lx_klev_rec );
SELECT MONTHS_BETWEEN(END_DATE,START_DATE), CONTRACT_NUMBER
FROM OKC_K_HEADERS_B
WHERE ID = p_khr_id ;
SELECT ((INSURED_RATE * p_covered_amount )/100 ) * p_freq_factor
FROM OKL_INS_RATES INR
WHERE INR.IPT_ID = p_ipt_id AND
p_fact_val BETWEEN INR.FACTOR_RANGE_START AND INR.FACTOR_RANGE_END AND
p_from_date BETWEEN INR.DATE_FROM AND DECODE(INR.DATE_TO,NULL,p_from_date,INR.DATE_TO)
AND IC_ID = p_territory_code;
SELECT 'x'
FROM OKX_INSURER_V
WHERE PARTY_ID = p_isu_id ;
SELECT currency_code, start_date
FROM okl_k_headers_full_v
WHERE id = p_khr_id;
SELECT premium,ipf_code,date_from,date_to,khr_id
FROM OKL_INS_POLICIES_B
WHERE ID = c_id;
SELECT CURRENCY_CODE
FROM OKC_K_HEADERS_B
WHERE ID = p_khr_id;
SELECT PRECISION
FROM fnd_currencies_vl
WHERE currency_code = p_currency_code
AND enabled_flag = 'Y'
AND TRUNC(NVL(start_date_active, SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(NVL(end_date_active, SYSDATE)) >= TRUNC(SYSDATE);
SELECT ID,
OBJECT_VERSION_NUMBER,
SGN_CODE,
SAY_CODE,
STY_ID,
KLE_ID,
KHR_ID,
ACTIVE_YN,
DATE_CURRENT,
DATE_WORKING,
DATE_HISTORY,
COMMENTS,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
PROGRAM_ID,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_UPDATE_DATE,
LAST_UPDATE_LOGIN
FROM Okl_Streams_V
WHERE okl_streams_v.khr_id = p_khr_id AND
okl_streams_v.kle_id = p_kle_id
AND okl_streams_v.sty_id = p_sty_id;
l_stmv_rec.LAST_UPDATED_BY,
l_stmv_rec.LAST_UPDATE_DATE,
l_stmv_rec.PROGRAM_ID,
l_stmv_rec.REQUEST_ID,
l_stmv_rec.PROGRAM_APPLICATION_ID,
l_stmv_rec.PROGRAM_UPDATE_DATE,
l_stmv_rec.LAST_UPDATE_LOGIN;
SELECT
ID,
OBJECT_VERSION_NUMBER,
KHR_ID,
KLE_ID,
ISS_CODE,
IPY_TYPE
FROM Okl_Ins_Policies_V
WHERE Okl_Ins_Policies_V.KHR_ID = p_id
AND ISS_CODE = 'ACCEPTED' ;
-- Start of wraper code generated automatically by Debug code generator for Okl_Ins_Policies_Pub.update_ins_policies
IF(IS_DEBUG_PROCEDURE_ON) THEN
BEGIN
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRINQB.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 => lx_ipyv_rec
);
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRINQB.pls call Okl_Ins_Policies_Pub.update_ins_policies ');
-- End of wraper code generated automatically by Debug code generator for Okl_Ins_Policies_Pub.update_ins_policies
IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
SELECT 'x'
FROM OKL_K_LINES
WHERE OKL_K_LINES.ID = p_kle_id;
SELECT IPY.ID
,IPY.POLICY_NUMBER
,OKHB.CONTRACT_NUMBER
FROM OKC_K_HEADERS_B OKHB
,OKL_INS_POLICIES_B IPY
,OKL_TRX_CONTRACTS CTRX
,OKC_STATUSES_B OSTS
WHERE OKHB.ID = IPY.KHR_ID
AND OKHB.ID = CTRX.KHR_ID
AND OKHB.STS_CODE = OSTS.CODE
AND OSTS.STE_CODE = 'ACTIVE'
AND IPY.ISS_CODE = 'PENDING'
AND CTRX.TCN_TYPE = 'BKG'
--rkuttiya added for 12.1.1 Multi GAAP
AND CTRX.REPRESENTATION_TYPE = 'PRIMARY'
--
AND (CTRX.DATE_TRANSACTION_OCCURRED + l_afterlease_criteria) < SYSDATE
UNION
SELECT IPY.ID
,IPY.POLICY_NUMBER
,OKHB.CONTRACT_NUMBER
FROM OKC_K_HEADERS_B OKHB
,OKL_INS_POLICIES_B IPY
,OKL_IN_RAMOUNT_BC_V KLRA -- Bug 5897792
WHERE KLRA.KLE_ID = IPY.KLE_ID
AND OKHB.ID = IPY.KHR_ID -- smoduga fix for 4383565
AND KLRA.AMOUNT_RECEIVED >= (IPY.PREMIUM * l_percentage_criteria);
SELECT contract_number INTO l_contract_number
from OKC_K_HEADERS_B
WHERE ID = p_ipyv_rec.khr_id;
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRINQB.pls call Okl_Streams_Pub.update_streams ');
Okl_Streams_Pub.update_streams (
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_stmv_rec => l_stmv_rec,
x_stmv_rec => x_stmv_rec);
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRINQB.pls call Okl_Streams_Pub.update_streams ');
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRINQB.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 => lx_ipyv_rec
);
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRINQB.pls call Okl_Ins_Policies_Pub.update_ins_policies ');
SELECT SUM(LESSOR_PREMIUM)
FROM OKL_INS_ASSETS INA
WHERE INA.IPY_ID = p_ipy_id ;
SELECT INSURER_RATE/ INSURED_RATE
FROM OKL_INS_RATES INR
WHERE INR.IPT_ID = p_ipt_id AND
p_fact_val BETWEEN INR.FACTOR_RANGE_START AND INR.FACTOR_RANGE_END AND
p_from_date BETWEEN INR.DATE_FROM AND DECODE(INR.DATE_TO,NULL,p_from_date + 1,INR.DATE_TO)
AND IC_ID = p_territory_code;
SELECT id
FROM okl_trx_types_tl
WHERE name = cp_name
AND language = cp_language;
select 'x'
from
OKC_K_PARTY_ROLES_B CPLB
where CPLB.CHR_ID = p_khr_id
and CPLB.DNZ_CHR_ID = p_khr_id
and CPLB.OBJECT1_ID1 = p_isu_id
and CPLB.JTOT_OBJECT1_CODE = 'OKX_VENDOR'
and CPLB.RLE_CODE = 'OKL_VENDOR';
select ID
from OKL_STRM_TYPE_TL
where NAME = 'INSURANCE EXPENSE'
AND LANGUAGE = 'US';
SELECT STM.ID , STM.active_yn
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 , STM.active_yn
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 SUM(OEC)
FROM
(
SELECT OTAB.DNZ_KHR_ID CONTRACT_ID,
KLE_TOP.OEC OEC,
OTAB.ASSET_NUMBER ASSET_NUMBER
FROM OKL_TXL_ASSETS_B OTAB,
OKL_K_LINES KLE,
OKL_K_LINES KLE_TOP,
OKC_K_LINES_B CLE
WHERE KLE.ID = OTAB.KLE_ID
AND KLE.ID = CLE.ID
AND CLE.CLE_ID = KLE_TOP.ID
AND NOT EXISTS
(Select '1'
from okc_k_items cim
where cim.cle_id = otab.kle_id
AND cim.object1_id1 is not null)
AND CLE.STS_CODE NOT IN ( 'TERMINATED' , 'EXPIRED','ABANDONED' )
AND OTAB.DNZ_KHR_ID = p_khr_id
UNION
SELECT OTAB.DNZ_KHR_ID CONTRACT_ID,KLE_TOP.OEC OEC,
OTAB.ASSET_NUMBER ASSET_NUMBER
FROM OKL_TXL_ASSETS_B OTAB,
OKL_K_LINES KLE ,
OKL_K_LINES KLE_TOP ,
OKC_K_LINES_B CLE ,
OKL_K_HEADERS KHR
WHERE KLE.ID = OTAB.KLE_ID
AND KLE.ID = CLE.ID
AND CLE.CLE_ID = KLE_TOP.ID
AND NOT EXISTS
(Select '1'
from okc_k_items cim
where cim.cle_id = otab.kle_id
AND cim.object1_id1 is not null)
AND khr.id = CLE.DNZ_CHR_ID
AND khr.deal_type = 'LOAN'
AND CLE.STS_CODE NOT IN ( 'TERMINATED' , 'EXPIRED','ABANDONED','BOOKED' )
AND OTAB.DNZ_KHR_ID = p_khr_id
UNION
select CLE.DNZ_CHR_ID CONTRACT_ID,
KLE.OEC OEC,
FAD.ASSET_NUMBER ASSET_NUMBER
from OKL_K_LINES KLE ,
OKC_K_LINES_B CLE,
OKC_LINE_STYLES_B LS,
OKC_K_ITEMS CIM,
OKC_K_LINES_B ITEM_CLE,
OKC_LINE_STYLES_B ITEM_LS,
OKC_K_ITEMS MODEL ,
FA_ADDITIONS_B FAd,
OKC_K_LINES_B FINAC_CLE,
OKC_LINE_STYLES_B FINAC_LS
where FINAC_LS.LTY_CODE = 'FREE_FORM1'
AND FINAC_CLE.LSE_ID = FINAC_LS.ID
AND FINAC_CLE.ID = KLE.ID
AND FAD.ASSET_ID = CIM.OBJECT1_ID1
AND CIM.OBJECT1_ID2 = '#'
AND MODEL.JTOT_OBJECT1_CODE = 'OKX_SYSITEM'
AND MODEL.DNZ_CHR_ID = CLE.DNZ_CHR_ID
AND MODEL.cle_id = ITEM_CLE.ID
AND ITEM_LS.LTY_CODE = 'ITEM'
AND ITEM_LS.ID = ITEM_CLE.LSE_ID
AND ITEM_CLE.CLE_ID = FINAC_CLE.ID
AND CIM.DNZ_CHR_ID = CLE.DNZ_CHR_ID
AND CIM.CLE_ID = CLE.ID
AND CIM.JTOT_OBJECT1_CODE = 'OKX_ASSET'
AND LS.ID = CLE.LSE_ID
AND LS.LTY_CODE = 'FIXED_ASSET'
AND CLE.CLE_ID = FINAC_CLE.ID
AND FINAC_CLE.STS_CODE NOT IN ( 'TERMINATED' , 'EXPIRED','ABANDONED' )
AND CLE.DNZ_CHR_ID = p_khr_id
union
SELECT CLE.DNZ_CHR_ID CONTRACT_ID,
KLE.OEC OEC,
FINAN_CLET.NAME ASSET_NUMBER
FROM OKL_K_LINES KLE,
OKC_K_LINES_B CLE,
OKC_LINE_STYLES_B LS,
OKC_K_LINES_B ITEM_CLE,
OKC_LINE_STYLES_B ITEM_LS,
OKC_K_ITEMS MODEL ,
OKL_K_HEADERS KHR ,
OKC_K_LINES_B FINAN_CLE ,
OKC_K_LINES_TL FINAN_CLET
WHERE MODEL.cle_id = ITEM_CLE.ID
AND MODEL.DNZ_CHR_ID = ITEM_CLE.DNZ_CHR_ID
AND ITEM_LS.LTY_CODE = 'ITEM'
AND ITEM_LS.ID = ITEM_CLE.LSE_ID
AND ITEM_CLE.CLE_ID = FINAN_CLE.ID
AND ITEM_CLE.DNZ_CHR_ID = FINAN_CLE.DNZ_CHR_ID
AND CLE.CLE_ID = FINAN_CLE.ID
AND CLE.DNZ_CHR_ID = FINAN_CLE.DNZ_CHR_ID
AND LS.ID = CLE.LSE_ID
AND LS.LTY_CODE = 'FIXED_ASSET'
AND FINAN_CLE.STS_CODE NOT IN ( 'TERMINATED' , 'EXPIRED','ABANDONED' )
AND FINAN_CLET.LANGUAGE = USERENV('LANG')
AND FINAN_CLET.ID = FINAN_CLE.ID
AND KLE.ID = FINAN_CLE.ID
AND FINAN_CLE.DNZ_CHR_ID = KHR.ID
AND FINAN_CLE.CHR_ID = KHR.ID
AND FINAN_CLE.CLE_ID is null
AND KHR.DEAL_TYPE = 'LOAN'
AND CLE.DNZ_CHR_ID = p_khr_id)
GROUP BY CONTRACT_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,
x_clev_rec => lx_clev_rec,
x_klev_rec => lx_klev_rec
);
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRINQB.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 => lx_ipyv_rec );
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRINQB.pls call Okl_Ins_Policies_Pub.update_ins_policies ');
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRINQB.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
,l_stmv_rec
,x_stmv_rec
);
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRINQB.pls call OKL_STREAMS_PUB.update_streams ');
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRINQB.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
,l_stmv_rec2
,x_stmv_rec
);
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRINQB.pls call OKL_STREAMS_PUB.update_streams ');
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRINQB.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
,l_stmv_rec3
,x_stmv_rec
);
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRINQB.pls call OKL_STREAMS_PUB.update_streams ');
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRINQB.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
,l_stmv_rec4
,x_stmv_rec
);
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRINQB.pls call OKL_STREAMS_PUB.update_streams ');
SELECT POLICY_SYMBOL
FROM OKL_INS_PRODUCTS_B
WHERE id = l_ipyv_rec.ipt_id;
SELECT OKL_IPY_SEQ.NEXTVAL INTO l_seq FROM dual;
l_ipyv_rec.PROGRAM_UPDATE_DATE := OKC_API.G_MISS_DATE ;
l_ipyv_rec.last_updated_by := OKC_API.G_MISS_NUM;
l_ipyv_rec.last_update_date := OKC_API.G_MISS_DATE ;
l_ipyv_rec.last_update_login := OKC_API.G_MISS_NUM;
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRINQB.pls call Okl_Ins_Policies_Pub.insert_ins_policies ');
Okl_Ins_Policies_Pub.insert_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 => lx_ipyv_rec
);
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRINQB.pls call Okl_Ins_Policies_Pub.insert_ins_policies ');
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRINQB.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_inqv_rec,
x_ipyv_rec => lx_inqv_rec
);
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRINQB.pls call Okl_Ins_Policies_Pub.update_ins_policies ');
select stream_type_purpose from okl_strm_type_b
where stream_type_purpose like 'INSURANCE%'
AND stream_type_purpose NOT IN
(select stl.primary_sty_purpose
from OKL_STRM_TMPT_LINES_UV stl,
okc_k_headers_b chr,
okl_k_headers oklchr
where stl.primary_sty_purpose like 'INSURANCE%'
and stl.primary_yn = 'Y'
and stl.pdt_id = oklchr.pdt_id
and (STL.START_DATE <= chr.start_date)
and (STL.END_DATE >= chr.start_date OR STL.END_DATE IS NULL)
and chr.id = p_contract_id
and chr.id = oklchr.id);
Select khr_id --Bug:3825159
from OKL_INS_POLICIES_B
where id = p_quote_id;
Select pdt_id
from okl_k_headers
where id = p_contract_id;
Select st_gen_tmpt_set_name
from OKL_STRM_TMPT_LINES_UV
where pdt_id = p_pdt_id
and rownum<2;
SELECT IPY_TYPE
FROM OKL_INS_POLICIES_V
WHERE OKL_INS_POLICIES_V.ID = quote_id;