The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 1
FROM fnd_lookups lok
WHERE lok.lookup_type = 'OKL_CHECKLIST_STATUS_CODE'
AND lok.lookup_code = p_lookup_code
;
IF (p_mode = G_INSERT_MODE) THEN
-- column is required:
IF (p_clhv_rec.status_code IS NULL) OR
(p_clhv_rec.status_code = OKL_API.G_MISS_CHAR)
THEN
OKL_API.Set_Message(p_app_name => G_APP_NAME,
p_msg_name => G_REQUIRED_VALUE,
p_token1 => G_COL_NAME_TOKEN,
p_token1_value => 'OKL_CHECKLIST.STATUS_CODE');
/* ELSIF (p_mode = G_UPDATE_MODE) THEN
-- column is required:
IF (p_clhv_rec.status_code IS NULL)
THEN
OKL_API.Set_Message(p_app_name => G_APP_NAME,
p_msg_name => G_REQUIRED_VALUE,
p_token1 => G_COL_NAME_TOKEN,
p_token1_value => 'OKL_CHECKLIST.STATUS_CODE');
SELECT lok.MEANING
FROM fnd_lookups lok
WHERE lok.lookup_type = p_lookup_type
AND lok.lookup_code = p_lookup_code
;
SELECT clh.CHECKLIST_PURPOSE_CODE,
clh.STATUS_CODE,
clh.STATUS_CODE_MEANING
FROM okl_checklists_uv clh
WHERE clh.id = p_clh_id
;
IF (p_mode = G_INSERT_MODE) THEN
-- column is required:
IF (p_clhv_rec.CHECKLIST_PURPOSE_CODE IS NULL) OR
(p_clhv_rec.CHECKLIST_PURPOSE_CODE = OKL_API.G_MISS_CHAR)
THEN
OKL_API.Set_Message(p_app_name => G_APP_NAME,
p_msg_name => G_REQUIRED_VALUE,
p_token1 => G_COL_NAME_TOKEN,
p_token1_value => 'OKL_CHECKLIST.CHECKLIST_PURPOSE_CODE');
ELSIF (p_mode = G_UPDATE_MODE) THEN
---------------------------------------------------------------
-- User are not allowed to modify the purpose code
---------------------------------------------------------------
OPEN c_org_purpose(p_clhv_rec.id);
select clh.CHECKLIST_PURPOSE_CODE
from okl_checklists clh
where clh.id = p_clh_id
;
IF (p_mode = G_INSERT_MODE) THEN
-- column is required:
IF l_purpose_code = 'CHECKLIST_INSTANCE' AND
(p_clhv_rec.CHECKLIST_OBJ_ID IS NULL OR
p_clhv_rec.CHECKLIST_OBJ_ID = OKL_API.G_MISS_NUM)
THEN
OKL_API.Set_Message(p_app_name => G_APP_NAME,
p_msg_name => G_REQUIRED_VALUE,
p_token1 => G_COL_NAME_TOKEN,
p_token1_value => 'OKL_CHECKLIST.CHECKLIST_OBJ_ID');
/* ELSIF (p_mode = G_UPDATE_MODE) THEN
-- column is required:
IF l_purpose_code = 'CHECKLIST_INSTANCE' AND
p_clhv_rec.CHECKLIST_OBJ_ID IS NULL
THEN
OKL_API.Set_Message(p_app_name => G_APP_NAME,
p_msg_name => G_REQUIRED_VALUE,
p_token1 => G_COL_NAME_TOKEN,
p_token1_value => 'OKL_CHECKLIST.CHECKLIST_OBJ_ID');
SELECT 1
FROM fnd_lookups lok
WHERE lok.lookup_type = 'OKL_CHECKLIST_OBJ_TYPE_CODE'
AND lok.lookup_code = p_lookup_code
;
select clh.CHECKLIST_PURPOSE_CODE
from okl_checklists clh
where clh.id = p_clh_id
;
IF (p_mode = G_INSERT_MODE) THEN
-- column is required:
IF l_purpose_code = 'CHECKLIST_INSTANCE' AND
(p_clhv_rec.CHECKLIST_OBJ_TYPE_CODE IS NULL OR
p_clhv_rec.CHECKLIST_OBJ_TYPE_CODE = OKL_API.G_MISS_CHAR)
THEN
OKL_API.Set_Message(p_app_name => G_APP_NAME,
p_msg_name => G_REQUIRED_VALUE,
p_token1 => G_COL_NAME_TOKEN,
p_token1_value => 'OKL_CHECKLIST.CHECKLIST_OBJ_TYPE_CODE');
/* ELSIF (p_mode = G_UPDATE_MODE) THEN
-- column is required:
IF l_purpose_code = 'CHECKLIST_INSTANCE' AND
p_clhv_rec.CHECKLIST_OBJ_TYPE_CODE IS NULL
THEN
OKL_API.Set_Message(p_app_name => G_APP_NAME,
p_msg_name => G_REQUIRED_VALUE,
p_token1 => G_COL_NAME_TOKEN,
p_token1_value => 'OKL_CHECKLIST.CHECKLIST_OBJ_TYPE_CODE');
SELECT 1
FROM okl_checklists ckl
WHERE ckl.CHECKLIST_PURPOSE_CODE = 'CHECKLIST_TEMPLATE_GROUP'
-- AND ckl.STATUS_CODE = 'NEW'
AND ckl.id = p_id
;
IF (p_mode = G_INSERT_MODE) THEN
-- column is required:
IF (p_clhv_rec.checklist_number IS NULL) OR
(p_clhv_rec.checklist_number = OKL_API.G_MISS_CHAR)
THEN
OKL_API.Set_Message(p_app_name => G_APP_NAME,
p_msg_name => G_REQUIRED_VALUE,
p_token1 => G_COL_NAME_TOKEN,
p_token1_value => 'Checklist Name');
/* ELSIF (p_mode = G_UPDATE_MODE) THEN
-- column is required:
IF (p_clhv_rec.checklist_number IS NULL)
THEN
OKL_API.Set_Message(p_app_name => G_APP_NAME,
p_msg_name => G_REQUIRED_VALUE,
p_token1 => G_COL_NAME_TOKEN,
p_token1_value => 'Checklist Name');
SELECT lok.MEANING
FROM fnd_lookups lok
WHERE lok.lookup_type = p_lookup_type
AND lok.lookup_code = p_lookup_code
AND lok.enabled_flag = 'Y'
;
SELECT clh.CHECKLIST_TYPE,
clh.STATUS_CODE,
clh.STATUS_CODE_MEANING
FROM okl_checklists_uv clh
WHERE clh.id = p_clh_id
;
IF (p_mode = G_INSERT_MODE) THEN
-- column is required:
IF (p_clhv_rec.checklist_type IS NULL) OR
(p_clhv_rec.checklist_type = OKL_API.G_MISS_CHAR)
THEN
OKL_API.Set_Message(p_app_name => G_APP_NAME,
p_msg_name => G_REQUIRED_VALUE,
p_token1 => G_COL_NAME_TOKEN,
p_token1_value => 'Checklist Type');
IF (p_mode = G_INSERT_MODE) THEN
-- column is required:
IF (p_clhv_rec.end_date IS NULL) OR
(p_clhv_rec.end_date = OKL_API.G_MISS_DATE)
THEN
OKL_API.Set_Message(p_app_name => G_APP_NAME,
p_msg_name => G_REQUIRED_VALUE,
p_token1 => G_COL_NAME_TOKEN,
p_token1_value => 'Effective To');
/* ELSIF (p_mode = G_UPDATE_MODE) THEN
-- column is required:
IF (p_clhv_rec.end_date IS NULL)
THEN
OKL_API.Set_Message(p_app_name => G_APP_NAME,
p_msg_name => G_REQUIRED_VALUE,
p_token1 => G_COL_NAME_TOKEN,
p_token1_value => 'Effective To');
SELECT clh.CHECKLIST_PURPOSE_CODE
FROM okl_checklists clh
WHERE clh.id = p_clh_id
;
SELECT grp.START_DATE grp_START_DATE,
grp.END_DATE grp_END_DATE,
cld.START_DATE cld_START_DATE,
cld.END_DATE cld_END_DATE
FROM okl_checklists grp,
okl_checklists cld
WHERE grp.id = cld.ckl_id
AND grp.id = p_clh_id
;
SELECT grp.START_DATE grp_START_DATE,
grp.END_DATE grp_END_DATE,
cld.START_DATE cld_START_DATE,
cld.END_DATE cld_END_DATE
FROM okl_checklists grp,
okl_checklists cld
WHERE grp.id = cld.ckl_id
AND cld.id = p_clh_id
;
SELECT count(1)
FROM okl_checklists clh
WHERE UPPER(clh.checklist_number) = UPPER(p_checklist_number)
AND clh.checklist_type = p_checklist_type
;
is select 1
from okl_checklists clh
where clh.start_date is not null
and trunc(clh.start_date) > trunc(clh.end_date)
and clh.id = p_id
;
select cld.ckl_id
from okl_checklist_details cld
where cld.id = p_cld_id
;
SELECT 1
FROM okl_checklist_details cld,
okl_checklists clh
where clh.id = cld.ckl_id -- get clh.checklist_type
and clh.checklist_purpose_code = 'CHECKLIST_TEMPLATE'
and cld.ckl_id in (select lst.id
from okl_checklists lst,
okl_checklists prt
where lst.ckl_id = prt.ckl_id
and prt.id = p_clh_id)
GROUP BY cld.todo_item_code, cld.FUNCTION_ID, clh.checklist_type
HAVING count(1) > 1
;
SELECT 1
FROM fnd_lookups lok
WHERE lok.lookup_type = 'OKL_YES_NO'
AND lok.lookup_code = p_lookup_code
;
SELECT 1
FROM fnd_lookups lok
WHERE lok.lookup_type = 'OKL_YES_NO'
AND lok.lookup_code = p_lookup_code
;
SELECT 1
FROM fnd_lookups lok
WHERE lok.lookup_type = 'OKL_FUN_VALIDATE_RSTS'
AND lok.lookup_code = p_lookup_code
;
SELECT 1
FROM OKL_DATA_SRC_FNCTNS_V fun
WHERE fun.id = p_id
;
SELECT 1
FROM okl_checklists clh,
okl_checklist_details cld
WHERE clh.id = cld.ckl_id
AND cld.id = p_cld_id
AND clh.CHECKLIST_PURPOSE_CODE = 'CHECKLIST_TEMPLATE_GROUP'
;
SELECT 1
FROM fnd_lookups lok
WHERE lok.lookup_type = G_CHECKLIST_TYPE_LOOKUP_TYPE
AND lok.lookup_code = p_checklist_type
;
SELECT clh.checklist_purpose_code
FROM okl_checklists clh,
okl_checklist_details cld
WHERE clh.id = cld.ckl_id
AND cld.id = p_cld_id
;
IF (p_mode = G_INSERT_MODE) THEN
-- column is required:
IF l_purpose = 'CHECKLIST_INSTANCE' AND
(p_cldv_rec.inst_checklist_type IS NULL OR
p_cldv_rec.inst_checklist_type = OKL_API.G_MISS_CHAR)
THEN
OKL_API.Set_Message(p_app_name => G_APP_NAME,
p_msg_name => G_REQUIRED_VALUE,
p_token1 => G_COL_NAME_TOKEN,
p_token1_value => 'Checklist Type');
ELSIF (p_mode = G_UPDATE_MODE) THEN
-- column is required:
IF (l_purpose = 'CHECKLIST_INSTANCE' AND p_cldv_rec.inst_checklist_type IS NULL)
THEN
OKL_API.Set_Message(p_app_name => G_APP_NAME,
p_msg_name => G_REQUIRED_VALUE,
p_token1 => G_COL_NAME_TOKEN,
p_token1_value => 'Checklist Type');
SELECT 1
FROM fnd_lookups lok
WHERE lok.lookup_type = 'OKL_TODO_ITEMS'
AND lok.lookup_code = p_todo_item_code
;
IF (p_mode = G_INSERT_MODE) THEN
-- column is required:
IF (p_cldv_rec.todo_item_code IS NULL) OR
(p_cldv_rec.todo_item_code = OKL_API.G_MISS_CHAR)
THEN
OKL_API.Set_Message(p_app_name => G_APP_NAME,
p_msg_name => G_REQUIRED_VALUE,
p_token1 => G_COL_NAME_TOKEN,
p_token1_value => 'Item Code');
/* ELSIF (p_mode = G_UPDATE_MODE) THEN
-- column is required:
IF (p_cldv_rec.todo_item_code IS NULL)
THEN
OKL_API.Set_Message(p_app_name => G_APP_NAME,
p_msg_name => G_REQUIRED_VALUE,
p_token1 => G_COL_NAME_TOKEN,
p_token1_value => 'Item Code');
SELECT lok.START_DATE_ACTIVE,
lok.END_DATE_ACTIVE,
clh.START_DATE,
clh.END_DATE
FROM fnd_lookups lok,
okl_checklists clh
-- START: Apr 25, 2005 cklee: Modification for okl.h lease app enhancement
-- WHERE decode(clh.checklist_type, 'CREDITLINE', 'OKL_TODO_CREDIT_CHKLST'
-- , 'FUNDING_REQUEST', 'OKL_TODO_FUNDING_CHKLST')
-- = lok.lookup_type
-- END: Apr 25, 2005 cklee: Modification for okl.h lease app enhancement
WHERE lok.lookup_type = 'OKL_TODO_ITEMS'
AND lok.lookup_code = p_todo_item_code
AND clh.id = p_ckl_id
;
IF (p_mode = G_INSERT_MODE) THEN
-- column is required:
IF (p_cldv_rec.todo_item_code IS NULL) OR
(p_cldv_rec.todo_item_code = OKL_API.G_MISS_CHAR)
THEN
OKL_API.Set_Message(p_app_name => G_APP_NAME,
p_msg_name => G_REQUIRED_VALUE,
p_token1 => G_COL_NAME_TOKEN,
p_token1_value => 'Item Code');
ELSIF (p_mode = G_UPDATE_MODE) THEN
-- column is required:
IF (p_cldv_rec.todo_item_code IS NULL)
THEN
OKL_API.Set_Message(p_app_name => G_APP_NAME,
p_msg_name => G_REQUIRED_VALUE,
p_token1 => G_COL_NAME_TOKEN,
p_token1_value => 'Item Code');
SELECT clh.checklist_number, cld.todo_item_code
FROM fnd_lookups lok,
okl_checklist_details cld,
okl_checklists clh
WHERE lok.lookup_type = 'OKL_TODO_ITEMS'
AND cld.id = p_cld_id
AND lok.lookup_code = cld.todo_item_code
AND cld.ckl_id = clh.id
AND clh.checklist_purpose_code in ('CHECKLIST_TEMPLATE', 'CHECKLIST_INSTANCE')
AND NOT (TRUNC(NVL(lok.START_DATE_ACTIVE, SYSDATE))
BETWEEN TRUNC(NVL(clh.START_DATE, lok.START_DATE_ACTIVE)) AND
NVL(TRUNC(clh.END_DATE),lok.START_DATE_ACTIVE))
;
SELECT clh_grp.checklist_number, cld.todo_item_code
FROM fnd_lookups lok,
okl_checklist_details cld,
okl_checklists clh,
okl_checklists clh_grp
WHERE lok.lookup_type = 'OKL_TODO_ITEMS'
AND cld.id = p_cld_id
AND lok.lookup_code = cld.todo_item_code
AND cld.ckl_id = clh.id
AND clh.ckl_id = clh_grp.id
AND clh_grp.checklist_purpose_code = 'CHECKLIST_TEMPLATE_GROUP'
AND NOT (TRUNC(NVL(lok.START_DATE_ACTIVE, SYSDATE))
BETWEEN TRUNC(NVL(clh_grp.START_DATE, lok.START_DATE_ACTIVE)) AND
NVL(TRUNC(clh_grp.END_DATE),lok.START_DATE_ACTIVE))
;
select cld.id, cld.todo_item_code
from okl_checklist_details cld,
okl_checklists clh
where cld.ckl_id = clh.id
and cld.ckl_id = p_clh_id
and clh.checklist_purpose_code in ('CHECKLIST_TEMPLATE', 'CHECKLIST_INSTANCE')
union
select cld.id, cld.todo_item_code
from okl_checklist_details cld,
okl_checklists clh,
okl_checklists clh_grp
where cld.ckl_id = clh.id
and clh.ckl_id = clh_grp.id
and clh_grp.id = p_clh_id
and clh_grp.checklist_purpose_code = 'CHECKLIST_TEMPLATE_GROUP'
;
l_return_status := validate_item_effective_from(l_cldv_rec, G_UPDATE_MODE);
SELECT 1
FROM okl_checklist_details cld
WHERE cld.ckl_id = p_ckl_id
-- START: 06-Jan-2006 cklee -- Fixed for instance checklist item duplication check |
-- GROUP BY cld.todo_item_code, cld.function_id
GROUP BY cld.todo_item_code, cld.function_id, cld.INST_CHECKLIST_TYPE
-- END: 06-Jan-2006 cklee -- Fixed for instance checklist item duplication check |
HAVING count(1) > 1
;
SELECT 1
FROM fnd_lookups lok
WHERE lok.lookup_type = 'OKL_YES_NO'
AND lok.lookup_code = p_lookup_code;
l_return_status := validate_header_attributes(lp_clhv_rec, G_INSERT_MODE);
okl_clh_pvt.insert_row(
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_clhv_rec => lp_clhv_rec,
x_clhv_rec => x_clhv_rec);
l_return_status := validate_hdr_attr_aftimg(lp_clhv_rec, G_INSERT_MODE);
PROCEDURE update_checklist_hdr(
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_clhv_rec IN clhv_rec_type
,x_clhv_rec OUT NOCOPY clhv_rec_type
)
is
l_api_name CONSTANT VARCHAR2(30) := 'update_checklist_hdr';
SAVEPOINT update_checklist_hdr;
l_return_status := validate_header_attributes(lp_clhv_rec, G_UPDATE_MODE);
okl_clh_pvt.update_row(
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_clhv_rec => lp_clhv_rec,
x_clhv_rec => x_clhv_rec);
l_return_status := validate_hdr_attr_aftimg(lp_clhv_rec, G_UPDATE_MODE);
ROLLBACK TO update_checklist_hdr;
ROLLBACK TO update_checklist_hdr;
ROLLBACK TO update_checklist_hdr;
end update_checklist_hdr;
PROCEDURE delete_checklist_hdr(
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_clhv_rec IN clhv_rec_type
)
is
l_api_name CONSTANT VARCHAR2(30) := 'delete_checklist_hdr';
SAVEPOINT delete_checklist_hdr;
okl_clh_pvt.delete_row(
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_clhv_rec => lp_clhv_rec);
ROLLBACK TO delete_checklist_hdr;
ROLLBACK TO delete_checklist_hdr;
ROLLBACK TO delete_checklist_hdr;
end delete_checklist_hdr;
l_return_status := validate_line_attributes(lp_cldv_tbl(i),G_INSERT_MODE);
okl_cld_pvt.insert_row(
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_cldv_tbl => lp_cldv_tbl,
x_cldv_tbl => x_cldv_tbl);
l_return_status := validate_line_attr_aftimg(lp_cldv_tbl(i),G_INSERT_MODE);
PROCEDURE update_checklist_dtl(
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_cldv_tbl IN cldv_tbl_type
,x_cldv_tbl OUT NOCOPY cldv_tbl_type
)
is
l_api_name CONSTANT VARCHAR2(30) := 'update_checklist_dtl';
select h.CHECKLIST_OBJ_ID
from okl_checklists h,
okl_checklist_details d
where h.id = d.ckl_id
and d.id = p_dtl_id;
SAVEPOINT update_checklist_dtl;
l_return_status := validate_line_attributes(lp_cldv_tbl(i),G_UPDATE_MODE);
okl_cld_pvt.update_row(
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_cldv_tbl => lp_cldv_tbl,
x_cldv_tbl => x_cldv_tbl);
l_return_status := validate_line_attr_aftimg(lp_cldv_tbl(i),G_UPDATE_MODE);
ROLLBACK TO update_checklist_dtl;
ROLLBACK TO update_checklist_dtl;
ROLLBACK TO update_checklist_dtl;
end update_checklist_dtl;
PROCEDURE delete_checklist_dtl(
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_cldv_tbl IN cldv_tbl_type
)
is
l_api_name CONSTANT VARCHAR2(30) := 'delete_checklist_dtl';
SAVEPOINT delete_checklist_dtl;
okl_cld_pvt.delete_row(
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_cldv_tbl => lp_cldv_tbl);
ROLLBACK TO delete_checklist_dtl;
ROLLBACK TO delete_checklist_dtl;
ROLLBACK TO delete_checklist_dtl;
end delete_checklist_dtl;
select clh.status_code
from okl_checklists clh
where clh.id = p_clh_id;
select clh.id
from okl_checklists clh
where clh.ckl_id = p_clh_id;
update_checklist_hdr(
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_clhv_rec => lp_clhv_rec,
x_clhv_rec => lx_clhv_rec);
update_checklist_hdr(
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_clhv_rec => lp_clhv_rec,
x_clhv_rec => lx_clhv_rec);
select okl_inst_checklist_num_s.nextval
from dual;
PROCEDURE update_checklist_inst_hdr(
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_clhv_rec IN clhv_rec_type
,x_clhv_rec OUT NOCOPY clhv_rec_type
)
is
l_api_name CONSTANT VARCHAR2(30) := 'update_checklist_inst_hdr';
SAVEPOINT update_checklist_inst_hdr;
update_checklist_hdr(
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_clhv_rec => lp_clhv_rec,
x_clhv_rec => x_clhv_rec);
ROLLBACK TO update_checklist_inst_hdr;
ROLLBACK TO update_checklist_inst_hdr;
ROLLBACK TO update_checklist_inst_hdr;
end update_checklist_inst_hdr;
PROCEDURE delete_checklist_inst_hdr(
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_clhv_rec IN clhv_rec_type
)
is
l_api_name CONSTANT VARCHAR2(30) := 'delete_checklist_inst_hdr';
SAVEPOINT delete_checklist_inst_hdr;
delete_checklist_hdr(
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_clhv_rec => lp_clhv_rec);
ROLLBACK TO delete_checklist_inst_hdr;
ROLLBACK TO delete_checklist_inst_hdr;
ROLLBACK TO delete_checklist_inst_hdr;
end delete_checklist_inst_hdr;
select clh.CHECKLIST_OBJ_ID
from okl_checklists clh
where clh.id = p_clh_id;
PROCEDURE update_checklist_inst_dtl(
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_cldv_tbl IN cldv_tbl_type
,x_cldv_tbl OUT NOCOPY cldv_tbl_type
)
is
l_api_name CONSTANT VARCHAR2(30) := 'update_checklist_inst_dtl';
SAVEPOINT update_checklist_inst_dtl;
update_checklist_dtl(
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_cldv_tbl => lp_cldv_tbl,
x_cldv_tbl => x_cldv_tbl);
ROLLBACK TO update_checklist_inst_dtl;
ROLLBACK TO update_checklist_inst_dtl;
ROLLBACK TO update_checklist_inst_dtl;
end update_checklist_inst_dtl;
PROCEDURE delete_checklist_inst_dtl(
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_cldv_tbl IN cldv_tbl_type
)
is
l_api_name CONSTANT VARCHAR2(30) := 'delete_checklist_inst_dtl';
SAVEPOINT delete_checklist_inst_dtl;
delete_checklist_dtl(
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_cldv_tbl => lp_cldv_tbl);
ROLLBACK TO delete_checklist_inst_dtl;
ROLLBACK TO delete_checklist_inst_dtl;
ROLLBACK TO delete_checklist_inst_dtl;
end delete_checklist_inst_dtl;
select clh.status_code,
clh.checklist_purpose_code,
clh.ckl_id
from okl_checklists clh
where clh.id = p_clh_id
;
select 1
from okl_checklists clh
where clh.ckl_id = p_clh_id
;
select clh.id
from okl_checklists clh
where clh.ckl_id = p_clh_id
;
select 1
from okl_checklist_details cld
where cld.ckl_id = p_clh_id
;
select 1
from okl_checklist_details cld
where cld.ckl_id = p_clh_id
;
l_return_status := validate_dates_w_item(l_clhv_rec, G_UPDATE_MODE);
PROCEDURE update_checklist_function(
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_checklist_obj_id IN NUMBER
) is
l_api_name CONSTANT VARCHAR2(30) := 'update_checklist_function';
select cld.FUNCTION_SOURCE,
cld.ID
from okl_checklist_details_uv cld
--START:| 21-Dec-2005 cklee -- Fixed bug#4880288 -- 4908242
,okl_checklists hdr
-- where cld.DNZ_CHECKLIST_OBJ_ID = p_checklist_obj_id
where cld.ckl_id = hdr.id
and hdr.CHECKLIST_OBJ_ID = p_checklist_obj_id
--START:| 21-Dec-2005 cklee -- Fixed bug#4880288 -- 4908242
and cld.FUNCTION_ID IS NOT NULL
;
SAVEPOINT update_checklist_function;
okl_cld_pvt.update_row(
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_cldv_rec => lp_cldv_rec,
x_cldv_rec => lx_cldv_rec);
ROLLBACK TO update_checklist_function;
ROLLBACK TO update_checklist_function;
ROLLBACK TO update_checklist_function;
end update_checklist_function;
select chr.ORIG_SYSTEM_ID1
from okc_k_headers_b chr
where ORIG_SYSTEM_SOURCE_CODE = G_OKL_LEASE_APP
and chr.id = p_chr_id
;
select
chk.TODO_ITEM_CODE,
NVL(chk.MANDATORY_FLAG, 'N') MANDATORY_FLAG,
chk.USER_NOTE,
chk.FUNCTION_ID,
chk.INST_CHECKLIST_TYPE
from OKL_CHECKLIST_DETAILS chk
--START:| 21-Dec-2005 cklee -- Fixed bug#4880288 -- 4908242
,okl_checklists hdr
where chk.ckl_id = hdr.id
--where chk.DNZ_CHECKLIST_OBJ_ID = p_lease_app_id
and hdr.CHECKLIST_OBJ_ID = p_lease_app_id
--START:| 21-Dec-2005 cklee -- Fixed bug#4880288 -- 4908242
and chk.INST_CHECKLIST_TYPE = 'ACTIVATION'
;
select 1
from OKL_CHECKLIST_DETAILS chk
--START:| 21-Dec-2005 cklee -- Fixed bug#4880288 -- 4908242
,okl_checklists hdr
where chk.ckl_id = hdr.id
--where chk.DNZ_CHECKLIST_OBJ_ID = p_lease_app_id
and hdr.CHECKLIST_OBJ_ID = p_lease_app_id
--END:| 21-Dec-2005 cklee -- Fixed bug#4880288 -- 4908242
and chk.INST_CHECKLIST_TYPE = 'ACTIVATION'
;
l_return_status := validate_appeal_flag(p_cldv_tbl(i), G_UPDATE_MODE);
,'begin debug call OKL_CLD_PVT.UPDATE_ROW');
OKL_CLD_PVT.UPDATE_ROW(
p_api_version => p_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_cldv_tbl => p_cldv_tbl
,x_cldv_tbl => x_cldv_tbl);
,'end debug call OKL_CLD_PVT.UPDATE_ROW');
,L_MODULE || ' Result of OKL_CLD_PVT.UPDATE_ROW'
,'l_return_status ' || l_return_status);