[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select start_date, end_date
from okc_k_headers_b
where id = p_chr_id;
select start_date, end_date
from okc_k_lines_b
where id = p_cle_id;
SELECT global_timezone_name
from okx_timezones_v
where timezone_id = p_tze_id;
SELECT id, tze_id
FROM OKC_TIMEVALUES tgn
WHERE cnh_id = p_cnh_id
AND tve_type = 'TGN';
SELECT id, tve_type, duration, operator, before_after, datetime, tve_id_offset, uom_code, tze_id
FROM Okc_Timevalues
connect by tve_id_offset = prior id
start with tve_id_offset = p_id;
SELECT month, day, nth, day_of_week, hour, minute, second, tze_id
FROM OKC_TIMEVALUES
WHERE id = p_tve_id
and tve_type = 'TGD';
SELECT id, active_yn, duration, uom_code
FROM Okc_Span
connect by prior id = spn_id
start with ((spn_id is NULL) or (spn_id = OKC_API.G_MISS_NUM)) and
tve_id = p_id;
select nvl(ise.start_date,to_date('01010001','ddmmyyyy')) start_date,
nvl(ise.end_date,to_date('31124000','ddmmyyyy')) end_date,
tze_id
from okc_time_ia_startend_val_v ise
where ise.id = p_tve_id;
l_sql_string := 'select r.id, rg.chr_id, rg.cle_id, r.rule_information_category ' ||
'from okc_rules_b r, okc_rule_groups_b rg '||
'where r.dnz_chr_id = :p_chr_id ' ||
'and rg.id = r.rgp_id ' ||
'and r.rule_information_category in '|| l_list_of_rules ||
'and r.rule_information_category in '|| l_list_of_rules1 ;
'select id, tve_id_limited, tve_type, datetime, tze_id ' ||
'from okc_timevalues ' ||
'where ((tve_type in ( ''TGD'',''TAV''))'||
' or (tve_type = ''CYL'' and interval_yn = ''N'')) ' ||
'and id in '|| l_list_of_tve_id;
In order to overcome this problem, we are storing the start_date as 01010001 and we will update this to the
actual start date of the contract once the contract is signed/approved and this procedure is called.
The same can be said to be the reason for storing end_date as 31124000 where the end_date is entered as null
but the start date is an actual limiting date.
BETTER SOLUTION: Change datamodel to have the K effectivity pointing to a timevalue and then the limited
timevalue will also be pointing to the same start date as referenced by the contract start date.
*/
if l_start_date = to_date('01010001','ddmmyyyy') or
l_end_date = to_date('31124000','ddmmyyyy') Then
Get_K_Effectivity(
l_chr_id,
l_cle_id,
l_k_start_date,
l_k_end_date,
x_return_status);
OKC_TIME_pub.update_ia_startend(
p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
p_isev_ext_rec,
x_isev_ext_rec);
as a limiting date for time resolving timevalue and also update the contract header to reflect this value.
Otherwise reset the resolved until date of the contract header to NULL
*/
IF l_cle_id is NOT NULL and
l_cle_id <> OKC_API.G_MISS_NUM Then
null;
update okc_k_headers_b
set resolved_until = p_resolved_until_date
where id = l_chr_id;
update okc_k_headers_b
set resolved_until = NULL
where id = l_chr_id;
select nvl(ise.start_date,to_date('01010001','ddmmyyyy')) start_date,
nvl(ise.end_date,to_date('31124000','ddmmyyyy')) end_date,
tze_id
from okc_time_ia_startend_val_v ise
where ise.id = p_tve_id;
SELECT resolved_until from okc_k_headers_b h, okc_k_lines_b l
where h.id = l.chr_id
and l.id = p_cle_id;
l_sql_string := 'select r.id, r.rule_information_category ' ||
'from okc_rules_b r, okc_rule_groups_b rg '||
'where ((rg.dnz_chr_id = :p_chr_id and rg.cle_id IS NULL) or ' ||
' (rg.cle_id = :p_cle_id)) and ' ||
' r.rgp_id = rg.id ' ||
'and r.rule_information_category in '|| l_list_of_rules ||
'and r.rule_information_category in '|| l_list_of_rules1 ;
'select id, tve_id_limited, tve_type, datetime, tze_id ' ||
'from okc_timevalues ' ||
'where ((tve_type in ( ''TGD'',''TAV''))'||
' or (tve_type = ''CYL'' and interval_yn = ''N'')) ' ||
'and id in '|| l_list_of_tve_id;
update okc_k_headers_b
set resolved_until = l_resolved_until_date
where id = p_chr_id;
update okc_k_headers_b
set resolved_until = NULL
where id = p_chr_id;
SELECT resolved_until from okc_k_headers_b h
where h.id = p_chr_id;
NOTE: terminating header will not automatically delete resolved timevalues for lines.
Has to be called for terminating lines as well. p_chr_id and p_cle_id are mutually exclusive.
*/
x_return_status := OKC_API.G_RET_STS_SUCCESS;
l_sql_string := 'select r.id, r.rule_information_category ' ||
'from okc_rules_b r, okc_rule_groups_b rg '||
'where ((rg.dnz_chr_id = :p_chr_id and rg.cle_id IS NULL) or ' ||
' (rg.cle_id = :p_cle_id)) and ' ||
' r.rgp_id = rg.id ' ||
'and r.rule_information_category in '|| l_list_of_rules ||
'and r.rule_information_category in '|| l_list_of_rules1 ;
'select rtv.id rtv_id ' ||
'from okc_timevalues tve, okc_resolved_timevalues rtv ' ||
'where rtv.tve_id = tve.id ' ||
'and rtv.datetime >= :p_end_date ' ||
'and tve.id in '|| l_list_of_tve_id ;
OKC_TASK_PUB.DELETE_TASK(
p_api_version,
p_init_msg_list,
'F',
NULL,
l_rtv_id,
x_return_status,
x_msg_count,
x_msg_data);
OKC_TIME_PUB.DELETE_RESOLVED_TIMEVALUES(
p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
l_rtvv_rec);
update okc_k_headers_b
set resolved_until = NULL
where id = p_chr_id;
SELECT '1' from OKC_RESOLVED_TIMEVALUES
WHERE tve_id = p_tve_id AND
datetime <= p_date
UNION ALL
SELECT '1' from OKC_RESOLVED_TIMEVALUES rtv, JTF_TASKS_B t, JTF_TASK_TYPES_VL tt
WHERE SOURCE_OBJECT_ID = rtv.id AND
t.TASK_TYPE_ID = tt.TASK_TYPE_ID AND
tve_id = p_tve_id AND
tt.task_type_id = 23 AND
--tt.name = 'OKCSCHRULE' AND
ACTUAL_START_DATE <= p_date;
PROCEDURE Delete_Res_Time_N_Tasks(
p_tve_id IN NUMBER,
p_date IN DATE,
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2) IS
CURSOR rtv_csr(p_tve_id IN NUMBER, p_date IN DATE) is
select rtv.id rtv_id
from okc_timevalues tve, okc_resolved_timevalues rtv
where rtv.tve_id = tve.id
and rtv.datetime >= p_date
and tve.id = p_tve_id ;
OKC_TASK_PUB.DELETE_TASK(
p_api_version,
p_init_msg_list,
'F',
NULL,
l_rtv_rec.rtv_id,
x_return_status,
x_msg_count,
x_msg_data);
OKC_TIME_PUB.DELETE_RESOLVED_TIMEVALUES(
p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
l_rtvv_rec);
END Delete_Res_Time_N_Tasks;
select id, tve_type, datetime, tze_id
from okc_timevalues
where
id = p_tve_id;
SELECT contract_number, id, resolved_until from OKC_K_HEADERS_B
where resolved_until < add_months(sysdate, OKC_TIME_RES_PVT.PICKUP_UPTO_MONTHS)
and resolved_until >= sysdate;