[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_sql_stmt := 'SELECT ''1'' ' ||
'FROM ' || p_table ||
' WHERE NAME = ' || '''' || p_name || '''' ||
' AND NVL(TO_DATE, ' ||
'''' || Okl_Api.G_MISS_DATE || '''' || ') > ' ||
'''' || p_to_date || '''';
SELECT
ID,
OBJECT_VERSION_NUMBER,
NAME,
VERSION,
NVL(DESCRIPTION,Okl_Api.G_MISS_CHAR) DESCRIPTION,
FROM_DATE,
NVL(TO_DATE,Okl_Api.G_MISS_DATE) TO_DATE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
NVL(LAST_UPDATE_LOGIN, Okl_Api.G_MISS_NUM) LAST_UPDATE_LOGIN
FROM Okl_Pdt_Templates_V
WHERE okl_pdt_templates_v.id = p_id;
l_ptlv_rec.LAST_UPDATED_BY,
l_ptlv_rec.LAST_UPDATE_DATE,
l_ptlv_rec.LAST_UPDATE_LOGIN;
l_sql_stmt := 'SELECT ''1'' ' ||
'FROM ' || p_table ||
' WHERE NAME = ' || '''' || p_name || '''' ||
' AND ID <> ' || p_id ||
' AND ( ' || '''' || p_from_date || '''' ||
' BETWEEN FROM_DATE AND ' ||
' NVL(TO_DATE, ' || '''' || Okl_Api.G_MISS_DATE || '''' || ') OR ' ||
'''' || p_to_date || '''' ||
' BETWEEN FROM_DATE AND ' ||
' NVL(TO_DATE, ' || '''' || Okl_Api.G_MISS_DATE || '''' || ')) ' ||
'UNION ALL ' ||
'SELECT ''2'' ' ||
'FROM ' || p_table ||
' WHERE NAME = ' || '''' || p_name || '''' ||
' AND ID <> ' || p_id ||
' AND ' || '''' || p_from_date || '''' ||
' <= FROM_DATE ' ||
'AND ' || '''' || p_to_date || '''' ||
' >= NVL(TO_DATE, ' || '''' || Okl_Api.G_MISS_DATE || '''' || ') ';
SELECT '1'
FROM Okl_Products_V pdt
WHERE pdt.PTL_ID = p_ptl_id
AND (pdt.FROM_DATE < p_from_date OR
NVL(pdt.TO_DATE, pdt.FROM_DATE) > p_to_date);
SELECT '1'
FROM Okl_Ptq_Values_V ptv,
Okl_Ptl_Ptq_Vals_V pmv
WHERE pmv.PTL_ID = p_ptl_id
AND ptv.ID = pmv.PTV_ID
AND ((ptv.FROM_DATE > p_from_date OR
p_from_date > NVL(ptv.TO_DATE,p_from_date)) OR
NVL(ptv.TO_DATE, p_to_date) < p_to_date)
UNION ALL
SELECT '2'
FROM Okl_Pdt_Pqys_V pdq,
Okl_Pdt_Qualitys_V pqy
WHERE pdq.PTL_ID = p_ptl_id
AND pqy.ID = pdq.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);
PROCEDURE check_updates (
p_upd_ptlv_rec IN ptlv_rec_type,
p_db_ptlv_rec IN ptlv_rec_type,
p_ptlv_rec IN ptlv_rec_type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_upd_ptlv_rec ptlv_rec_type;
END check_updates;
SELECT ID,
PTQ_ID,
PTV_ID,
FROM_DATE,
TO_DATE
FROM Okl_Ptl_Ptq_Vals_V pmv
WHERE pmv.PTL_ID = p_ptl_id;
IF p_flag = G_UPDATE THEN
l_pmvv_tbl(l_count).ID := okl_pmv_rec.ID;
SELECT ID,
PQY_ID,
FROM_DATE,
TO_DATE
FROM Okl_Pdt_Pqys_V pdq
WHERE pdq.PTL_ID = p_ptl_id;
IF p_flag = G_UPDATE THEN
l_pdqv_tbl(l_count).ID := okl_pdq_rec.ID;
PROCEDURE copy_update_constraints (p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
p_upd_ptlv_rec IN ptlv_rec_type,
p_db_ptlv_rec IN ptlv_rec_type,
p_ptlv_rec IN ptlv_rec_type,
p_flag IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_upd_ptlv_rec ptlv_rec_type; /* input copy */
IF p_flag = G_UPDATE THEN
Okl_Ptq_Values_Pub.update_ptq_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_pmvv_tbl => l_pmvv_tbl,
x_pmvv_tbl => l_out_pmvv_tbl);
Okl_Ptq_Values_Pub.insert_ptq_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_pmvv_tbl => l_pmvv_tbl,
x_pmvv_tbl => l_out_pmvv_tbl);
IF p_flag = G_UPDATE THEN
Okl_Pdt_Pqys_Pub.update_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_tbl => l_pdqv_tbl,
x_pdqv_tbl => l_out_pdqv_tbl);
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_tbl => l_pdqv_tbl,
x_pdqv_tbl => l_out_pdqv_tbl);
END copy_update_constraints;
PROCEDURE insert_pdttemplates(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,
x_ptlv_rec OUT NOCOPY ptlv_rec_type
) IS
CURSOR c1(p_name okl_pdt_templates_v.name%TYPE,
p_version okl_pdt_templates_v.version%TYPE) IS
SELECT '1'
FROM okl_pdt_templates_v
WHERE name = p_name;
l_api_name CONSTANT VARCHAR2(30) := 'insert_pdttemplates';
/* public api to insert pdttemplates */
Okl_Pdt_Templates_Pub.insert_pdt_templates(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_ptlv_rec => l_ptlv_rec,
x_ptlv_rec => x_ptlv_rec);
END insert_pdttemplates;
PROCEDURE update_pdttemplates(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,
x_ptlv_rec OUT NOCOPY ptlv_rec_type
) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'update_pdttemplates';
/* public api to update product templates */
Okl_Pdt_Templates_Pub.update_pdt_templates(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_ptlv_rec => l_upd_ptlv_rec,
x_ptlv_rec => x_ptlv_rec);
check_updates(p_upd_ptlv_rec => l_upd_ptlv_rec,
p_db_ptlv_rec => l_db_ptlv_rec,
p_ptlv_rec => l_ptlv_rec,
x_return_status => l_return_status,
x_msg_data => x_msg_data);
/* public api to update product templates */
Okl_Pdt_Templates_Pub.update_pdt_templates(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_ptlv_rec => l_upd_ptlv_rec,
x_ptlv_rec => x_ptlv_rec);
/* update constraints */
/* copy_update_constraints(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_upd_ptlv_rec => l_upd_ptlv_rec,
p_db_ptlv_rec => l_db_ptlv_rec,
p_ptlv_rec => l_ptlv_rec,
p_flag => G_UPDATE,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
END update_pdttemplates;