[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
ID,
OBJECT_VERSION_NUMBER,
PTL_ID,
PQY_ID,
FROM_DATE,
TO_DATE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
FROM Okl_Pdt_Pqys_V
WHERE okl_pdt_pqys_v.id = p_id;
l_pdqv_rec.LAST_UPDATED_BY,
l_pdqv_rec.LAST_UPDATE_DATE,
l_pdqv_rec.LAST_UPDATE_LOGIN;
SELECT FROM_DATE,
TO_DATE
FROM Okl_pdt_templates_V ptlv
WHERE ptlv.id = p_ptl_id;
SELECT '1' FROM okl_pdt_templates_v ptlv,
okl_products pdtv,
okl_k_headers_v khdr
WHERE ptlv.id = p_ptl_id AND
ptlv.id = pdtv.ptl_id AND
pdtv.id = khdr.pdt_id;
SELECT '1'
FROM Okl_pdt_templates_V ptl
WHERE ptl.ID = p_ptl_id
AND NVL(ptl.TO_DATE, p_date) < p_date;
SELECT '1'
FROM Okl_Pdt_Qualitys_V pqy
WHERE pqy.ID = p_pqy_id
AND ((pqy.FROM_DATE > p_from_date OR
p_from_date > NVL(pqy.TO_DATE,p_from_date)) OR
NVL(pqy.TO_DATE, p_to_date) < p_to_date);
SELECT '1'
FROM okl_pdt_pqys_v
WHERE ptl_id = p_ptl_id
AND pqy_id = p_pqy_id
AND id <> NVL(p_pdqv_rec.id,-9999);
SELECT NAME
FROM okl_pdt_qualitys_v
WHERE id = cp_pqy_id
AND name IN ('LEASE','INVESTOR','TAXOWNER','REVENUE_RECOGNITION_METHOD','INTEREST_CALCULATION_BASIS');
l_invalid_selection_1 NUMBER(4):=0;
l_invalid_selection_2 NUMBER(4):=0;
SELECT COUNT(pqy.id)
INTO l_invalid_selection_1
FROM OKL_PDT_PQYS_V pdq,
OKL_PDT_QUALITYS_V pqy
WHERE pdq.PTL_ID = p_pdqv_rec.ptl_id
AND pdq.PQY_ID = pqy.ID
AND pqy.name = 'INVESTOR';
SELECT COUNT(pqy.id)
INTO l_invalid_selection_1
FROM OKL_PDT_PQYS_V pdq,
OKL_PDT_QUALITYS_V pqy
WHERE pdq.PTL_ID = p_pdqv_rec.ptl_id
AND pdq.PQY_ID = pqy.ID
AND pqy.name IN ('LEASE','TAXOWNER','REVENUE_RECOGNITION_METHOD','INTEREST_CALCULATION_BASIS');
SELECT COUNT(pqy.id)
INTO l_invalid_selection_1
FROM OKL_PDT_PQYS_V pdq,
OKL_PDT_QUALITYS_V pqy
WHERE pdq.PTL_ID = p_pdqv_rec.ptl_id
AND pdq.PQY_ID = pqy.ID
AND pqy.name = 'INVESTOR';
SELECT COUNT(pqy.id)
INTO l_invalid_selection_1
FROM OKL_PDT_PQYS_V pdq,
OKL_PDT_QUALITYS_V pqy
WHERE pdq.PTL_ID = p_pdqv_rec.ptl_id
AND pdq.PQY_ID = pqy.ID
AND pqy.name = 'LEASE';
SELECT COUNT(pqy.id)
INTO l_invalid_selection_2
FROM OKL_PDT_PQYS_V pdq,
OKL_PDT_QUALITYS_V pqy
WHERE pdq.PTL_ID = p_pdqv_rec.ptl_id
AND pdq.PQY_ID = pqy.ID
AND pqy.name = 'INVESTOR';
SELECT COUNT(pqy.id)
INTO l_invalid_selection_2
FROM OKL_PDT_PQYS_V pdq,
OKL_PDT_QUALITYS_V pqy
WHERE pdq.PTL_ID = p_pdqv_rec.ptl_id
AND pdq.PQY_ID = pqy.ID
AND pqy.name = 'TAXOWNER';
IF l_invalid_selection_1 > 0 THEN
Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
p_msg_name => G_LEASE_SEC_MISMATCH);
IF l_invalid_selection_2 > 0 THEN
Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
p_msg_name => G_TAXOWN_SEC_MISMATCH);
SELECT '1'
FROM okl_pdt_qualitys_v
WHERE okl_pdt_qualitys_v.id = p_id;
SELECT id
FROM okl_pdt_qualitys_v pqy
WHERE pqy.name = cp_name;
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRSDQB.pls call okl_pdt_pqys_pub.insert_pdt_pqys ');
okl_pdt_pqys_pub.insert_pdt_pqys(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_pdqv_rec => l_pdqv_rec,
x_pdqv_rec => l_out_pdqv_rec);
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRSDQB.pls call okl_pdt_pqys_pub.insert_pdt_pqys ');
PROCEDURE insert_dqualitys(p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_ptlv_rec IN ptlv_rec_type,
p_pdqv_rec IN pdqv_rec_type,
x_pdqv_rec OUT NOCOPY pdqv_rec_type
) IS
CURSOR choose_qualitys_csr(cp_pqy_id okl_pdt_qualitys_v.id%TYPE
) IS
SELECT name
FROM okl_pdt_qualitys_v pqy
WHERE pqy.ID = cp_pqy_id;
SELECT name
FROM okl_pdt_qualitys_v pqy
WHERE pqy.NAME <> P_choosen_quality
AND pqy.NAME IN ('LEASE','TAXOWNER','REVENUE_RECOGNITION_METHOD','INTEREST_CALCULATION_BASIS');
l_api_name CONSTANT VARCHAR2(30) := 'insert_dqualitys';
/* public api to insert dqualitys */
-- Start of wraper code generated automatically by Debug code generator for Okl_Pdt_Pqys_Pub.insert_pdt_pqys
IF(L_DEBUG_ENABLED='Y') THEN
L_LEVEL_PROCEDURE :=FND_LOG.LEVEL_PROCEDURE;
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRSDQB.pls call Okl_Pdt_Pqys_Pub.insert_pdt_pqys ');
Okl_Pdt_Pqys_Pub.insert_pdt_pqys(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_pdqv_rec => l_pdqv_rec,
x_pdqv_rec => x_pdqv_rec);
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRSDQB.pls call Okl_Pdt_Pqys_Pub.insert_pdt_pqys ');
SELECT COUNT(pqy.id)
INTO l_dependent_quality_cnt
FROM okl_pdt_pqys_v pdq,
okl_pdt_qualitys_v pqy
WHERE pdq.ptl_id = l_pdqv_rec.ptl_id
AND pdq.pqy_id = pqy.id
AND pqy.name = 'TAXOWNER';
SELECT COUNT(pqy.id)
INTO l_dependent_quality_cnt
FROM okl_pdt_pqys_v pdq,
okl_pdt_qualitys_v pqy
WHERE pdq.ptl_id = l_pdqv_rec.ptl_id
AND pdq.pqy_id = pqy.id
AND pqy.name = 'LEASE';
SELECT COUNT(pqy.id)
INTO l_dependent_quality_cnt
FROM okl_pdt_pqys_v pdq,
okl_pdt_qualitys_v pqy
WHERE pdq.ptl_id = l_pdqv_rec.ptl_id
AND pdq.pqy_id = pqy.id
AND pqy.name = 'LEASE';
SELECT COUNT(pqy.id)
INTO l_dependent_quality_cnt
FROM okl_pdt_pqys_v pdq,
okl_pdt_qualitys_v pqy
WHERE pdq.ptl_id = l_pdqv_rec.ptl_id
AND pdq.pqy_id = pqy.id
AND pqy.name = 'LEASE';
END insert_dqualitys;
PROCEDURE insert_dqualitys(p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_ptlv_rec IN ptlv_rec_type,
p_pdqv_tbl IN pdqv_tbl_type,
x_pdqv_tbl OUT NOCOPY pdqv_tbl_type
) IS
l_api_name CONSTANT VARCHAR2(30) := 'insert_dqualitys';
select 1 from okl_pdt_qualitys pdtq ,okl_pdt_pqys pdt
where pdtq.id = pdt.pqy_id and pdt.ptl_id=p_pdqv_tbl(i).ptl_id
and pdtq.name IN ('LEASE','TAXOWNER','REVENUE_RECOGNITION_METHOD','INTEREST_CALCULATION_BASIS');
CURSOR get_dependent_qualitys_csr--- added by rajnisku for bug 6398092 get the dependent qualitites not selected by the user
IS
SELECT name
FROM okl_pdt_qualitys_v pqy
WHERE pqy.NAME not in ( select pdtq.name from okl_pdt_qualitys pdtq ,okl_pdt_pqys pdt
where pdtq.id = pdt.pqy_id and pdt.ptl_id=p_pdqv_tbl(i).ptl_id)
AND pqy.NAME IN ('LEASE','TAXOWNER','REVENUE_RECOGNITION_METHOD','INTEREST_CALCULATION_BASIS');
insert_dqualitys(
p_api_version => p_api_version,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_ptlv_rec => p_ptlv_rec,
p_pdqv_rec => p_pdqv_tbl(i),
x_pdqv_rec => x_pdqv_tbl(i));
END insert_dqualitys;
PROCEDURE delete_pdt_pqys(
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_ptlv_rec IN ptlv_rec_type
,p_pdqv_rec IN pdqv_rec_type) IS
i PLS_INTEGER :=0;
SELECT pqvv.id
FROM okl_pdt_pqy_vals_v pqvv
WHERE pqvv.pdq_id = p_pdqv_rec.id;
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRSDQB.pls call Okl_Pqy_Values_Pub.delete_pqy_values ');
Okl_Pqy_Values_Pub.delete_pqy_values(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_pqvv_tbl => l_del_pqvv_tbl);
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRSDQB.pls call Okl_Pqy_Values_Pub.delete_pqy_values ');
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRSDQB.pls call Okl_Pdt_Pqys_Pub.delete_pdt_pqys ');
Okl_Pdt_Pqys_Pub.delete_pdt_pqys(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_pdqv_rec => p_pdqv_rec);
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRSDQB.pls call Okl_Pdt_Pqys_Pub.delete_pdt_pqys ');
END delete_pdt_pqys;
PROCEDURE delete_dqualitys( 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_ptlv_rec IN ptlv_rec_type
,p_pdqv_tbl IN pdqv_tbl_type ) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'delete_dqualitys';
CURSOR delete_dep_quality_csr(P_PDQ_ID NUMBER,P_PTL_ID NUMBER) IS
Select pdq.Id
From okl_pdt_pqys pdq,
okl_pdt_qualitys pqy
Where pdq.Id <> P_PDQ_ID
and pdq.pqy_id = pqy.id
and pqy.name in ('LEASE','TAXOWNER','REVENUE_RECOGNITION_METHOD','INTEREST_CALCULATION_BASIS')
and pdq.ptl_id = P_PTL_ID;
procedure insert_id(id in number,
l_pdqv_tbl IN OUT NOCOPY pdqv_tbl_type) Is
l PLS_INTEGER := 0;
Select count(*) into id_count
From okl_pdt_pqys pdq,
okl_pdt_qualitys pqy
Where pdq.Id = p_pdqv_tbl(j).Id
and pdq.pqy_id = pqy.id
and pqy.name in ('LEASE','TAXOWNER','REVENUE_RECOGNITION_METHOD','INTEREST_CALCULATION_BASIS')
and pdq.ptl_id = p_pdqv_tbl(j).ptl_id;
/* Select pdq.Id into other_id
From okl_pdt_pqys pdq,
okl_pdt_qualitys pqy
Where pdq.Id <> p_pdqv_tbl(j).Id
and pdq.pqy_id = pqy.id
and pqy.name in ('LEASE','TAXOWNER','REVENUE RECOGNITION METHOD','INTEREST_CALCULATION_BASIS')
and pdq.ptl_id = p_pdqv_tbl(j).ptl_id;*/
FOR delete_dep_quality_rec in delete_dep_quality_csr(P_PDQ_ID => p_pdqv_tbl(j).Id,
P_PTL_ID => p_pdqv_tbl(j).ptl_id)
LOOP
l_other_id := delete_dep_quality_rec.id;
insert_id(l_other_id,l_pdqv_tbl);
/* check if the product asked to delete is used by contracts if yes halt the process*/
Check_Constraints(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_pdqv_rec => l_pdqv_tbl(i),
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_valid => l_valid);
delete_pdt_pqys(
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_ptlv_rec => l_ptlv_rec
,p_pdqv_rec => l_pdqv_tbl(i)
);
END delete_dqualitys;