[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select b.callable_flag
, decode(sign(nvl(b.last_callable_time, SYSDATE) - SYSDATE), 0, 0, 1, 1, 0)
, a.callback_flag
, decode(sign(nvl(NEXT_CALL_TIME, SYSDATE) - SYSDATE), 0, 0, 1, 1, 0)
, a.record_out_flag
, c.status_code
, count(*)
from iec_g_return_entries a
, iec_g_mktg_item_Cc_tzs b
, iec_g_list_subsets c
where a.list_header_id = L_LIST_ID
and a.list_header_id = c.list_header_id
and a.itm_cc_Tz_id = b.itm_cc_tz_id
and a.do_not_use_Flag = 'N'
group by b.callable_flag
, decode(sign(nvl(b.last_callable_time, SYSDATE) - SYSDATE), 0, 0, 1, 1, 0)
, a.callback_flag
, decode(sign(nvl(NEXT_CALL_TIME, SYSDATE) - SYSDATE), 0, 0, 1, 1, 0)
, a.record_out_flag
, c.status_code;
EXECUTE IMMEDIATE 'select unique SCHEDULE_ID ' ||
' from IEC_G_EXECUTING_LISTS_V ' ||
' where SCHEDULE_ID = :1 '
INTO l_schedule_id
USING P_SCHEDULE_ID;
PROCEDURE UPDATE_SUBSET_RT_INFO
(P_CAMPAIGN_ID IN NUMBER
,P_LIST_HEADER_ID IN NUMBER
,P_SUBSET_ID IN NUMBER
,P_QUANTUM IN NUMBER
,P_QUOTA IN NUMBER
,P_QUOTA_RESET IN DATE
,P_USE_FLAG IN VARCHAR2
,X_RESULT IN OUT NOCOPY VARCHAR2
)
AS
PRAGMA AUTONOMOUS_TRANSACTION;
EXECUTE IMMEDIATE 'update iec_g_subset_rt_info ' ||
'set working_quantum = :1 ' ||
', use_flag = :2 ' ||
', working_quota = :3 ' ||
', quota_reset_time = :4 ' ||
', last_update_date = SYSDATE ' ||
'where list_subset_id = :5 '
USING P_QUANTUM
, P_USE_FLAG
, P_QUOTA
, P_QUOTA_RESET
, P_SUBSET_ID;
END UPDATE_SUBSET_RT_INFO;
UPDATE IEC_G_RETURN_ENTRIES
SET RECORD_OUT_FLAG = 'Y'
, CHECKOUT_ACTION_ID = P_SERVER_ID
, RECORD_RELEASE_TIME = SYSDATE
, LAST_UPDATE_DATE = SYSDATE
WHERE RETURNS_ID = P_RETURNS_ID_TAB(j);
select
d.list_entry_id,
d.returns_id,
c.priority
from iec_g_return_entries d
, iec_g_list_subsets c
where itm_cc_tz_id in
( select itm_cc_tz_id
from iec_g_mktg_item_cc_tzs
where subset_id in
( select a.list_Subset_id
from iec_g_list_Subsets a
, iec_g_subset_rt_info b
where a.list_header_id = L_LIST_ID
and a.list_subset_id = b.list_subset_id
and b.working_quota > 0
and b.status_code = 'ACTIVE')
and nvl(callable_flag, 'Y') <> 'N'
and last_callable_time > sysdate)
and nvl( callback_flag, 'N') = 'C'
and nvl( contact_point_index, 0) > 0
and nvl( record_out_flag, 'N') = 'N'
and nvl( do_not_use_flag, 'N') = 'N'
and pulled_subset_id is null
and sysdate > NEXT_CALL_TIME
and d.subset_id = c.list_subset_id
order by c.priority;
select
d.list_entry_id,
d.returns_id,
c.priority
from iec_g_return_entries d
, iec_g_list_subsets c
where itm_cc_tz_id in
( select itm_cc_tz_id
from iec_g_mktg_item_cc_tzs
where subset_id in
( select a.list_Subset_id
from iec_g_list_Subsets a
, iec_g_subset_rt_info b
where a.list_header_id = L_LIST_ID
and a.list_subset_id = b.list_subset_id
and b.working_quota > 0
and b.status_code = 'ACTIVE')
and nvl(callable_flag, 'Y') <> 'N'
and last_callable_time > sysdate)
and nvl( callback_flag, 'N') = 'Y'
and nvl( contact_point_index, 0) > 0
and nvl( record_out_flag, 'N') = 'N'
and nvl( do_not_use_flag, 'N') = 'N'
and pulled_subset_id is null
and sysdate > NEXT_CALL_TIME
and d.subset_id = c.list_subset_id
order by c.priority;
select /*+ index ( iec_g_return_entries, iec_g_return_entries_n8 ) */ list_entry_id,
returns_id
from iec_g_return_entries
where itm_cc_tz_id in
( select itm_cc_tz_id
from iec_g_mktg_item_cc_tzs
where subset_id = L_SUBSET_ID
and nvl(callable_flag, 'Y') <> 'N'
and last_callable_time > sysdate)
and nvl( callback_flag, 'N') = 'N'
and nvl( contact_point_index, 0) > 0
and nvl( record_out_flag, 'N') = 'N'
and nvl( do_not_use_flag, 'N') = 'N'
and pulled_subset_id is null
order by record_release_time asc;
l_subset_updated_tbl FLAG_COLLECTION;
SELECT a.list_subset_id
, a.priority
, a.release_strategy
, b.working_quantum
, b.working_quota
, a.quota
, a.quantum
, b.quota_reset_time
, b.use_flag
, a.quota_reset
BULK COLLECT INTO l_subset_id_tbl
, l_subset_priority_tbl
, l_release_strategy_tbl
, l_working_quantum_tbl
, l_working_quota_tbl
, l_quota_tbl
, l_quantum_tbl
, l_quota_reset_time_tbl
, l_use_flag_tbl
, l_quota_reset_tbl
FROM iec_g_list_subsets a
, iec_g_subset_rt_info b
WHERE a.list_header_id = P_LIST_HEADER_ID
AND a.list_subset_id = b.list_subset_id
AND b.valid_flag = 'Y'
AND b.callable_flag = 'Y'
AND b.STATUS_CODE = 'ACTIVE'
ORDER BY a.priority, a.list_subset_id;
l_subset_updated_tbl(j) := 'Y';
l_reg_returns_id_tab.DELETE;
IF l_subset_updated_tbl(j) = 'Y'
THEN
UPDATE_SUBSET_RT_INFO( P_CAMPAIGN_ID => P_CAMPAIGN_ID
, P_LIST_HEADER_ID => P_LIST_HEADER_ID
, P_SUBSET_ID => l_subset_id_tbl(j)
, P_QUANTUM => l_working_quantum_tbl(j)
, P_QUOTA => l_working_quota_tbl(j)
, P_QUOTA_RESET => l_quota_reset_time_tbl(j)
, P_USE_FLAG => l_use_flag_tbl(j)
, X_RESULT => X_RETURN_CODE);