[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ORACLE_USERNAME
INTO l_schema_name
FROM FND_ORACLE_USERID
WHERE READ_ONLY_FLAG = 'U';
PROCEDURE UPDATE_SUBSET_COUNTS
( p_campaign_id IN NUMBER
, p_schedule_id IN NUMBER
, p_list_id IN NUMBER
, p_subset_id IN NUMBER
, p_rec_loaded IN NUMBER
, p_rec_called IN NUMBER)
IS
l_rec_count NUMBER;
'SELECT COUNT(*)
FROM IEC_G_REP_SUBSET_COUNTS
WHERE SUBSET_ID = :subset_id'
INTO l_rec_count
USING p_subset_id;
'INSERT INTO IEC_G_REP_SUBSET_COUNTS
( SUBSET_COUNT_ID
, CAMPAIGN_ID
, SCHEDULE_ID
, LIST_HEADER_ID
, SUBSET_ID
, RECORD_LOADED
, RECORD_CALLED_ONCE
, RECORD_CALLED_AND_REMOVED
, RECORD_CALLED_AND_REMOVED_COPY
, LAST_COPY_TIME
, CREATED_BY
, CREATION_DATE
, LAST_UPDATE_LOGIN
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, OBJECT_VERSION_NUMBER
)
VALUES
(IEC_G_REP_SUBSET_COUNTS_S.NEXTVAL
, :campaign_id
, :schedule_id
, :list_id
, :subset_id
, :records_loaded
, :records_called
, 0
, 0
, SYSDATE
, 1
, SYSDATE
, 1
, SYSDATE
, 0
, 0)'
USING p_campaign_id
, p_schedule_id
, p_list_id
, p_subset_id
, p_rec_loaded
, p_rec_called;
'UPDATE IEC_G_REP_SUBSET_COUNTS
SET RECORD_LOADED = NVL(RECORD_LOADED, 0) + :records_loaded
, RECORD_CALLED_ONCE = NVL(RECORD_CALLED_ONCE, 0) + :records_called
, LAST_UPDATE_DATE = SYSDATE
WHERE SUBSET_ID = :subset_id'
USING p_rec_loaded
, p_rec_called
, p_subset_id;
Log( 'UPDATE_SUBSET_COUNTS'
, 'MAIN.SUBSET_' || p_subset_id
, SQLERRM
);
END UPDATE_SUBSET_COUNTS;
l_create_as_str CONSTANT VARCHAR2(100) := ' AS SELECT LIST_ENTRY_ID FROM ';
SELECT 1
INTO l_ignore
FROM ALL_VIEWS
WHERE VIEW_NAME = UPPER(l_view_name)
AND OWNER = UPPER(l_view_owner);
FOR subset_rec IN (SELECT LIST_SUBSET_ID
FROM IEC_G_LIST_SUBSETS
WHERE LIST_HEADER_ID = P_TARGET_GROUP_ID
AND NVL(DEFAULT_SUBSET_FLAG, 'N') = 'N')
LOOP
DROP_SUBSET_VIEW( P_SOURCE_ID
, subset_rec.LIST_SUBSET_ID
, l_status_code);
EXECUTE IMMEDIATE ' SELECT STATUS ' ||
' FROM ALL_OBJECTS ' ||
' WHERE OWNER = :owner ' ||
' AND OBJECT_NAME = :b1 ' ||
' AND OBJECT_TYPE = ''VIEW'' '
INTO L_STATUS
USING l_view_owner
, L_VIEW_NAME;
SELECT NVL(RELEASE_STRATEGY, G_RELEASE_STRATEGY_DEFAULT)
, NVL(QUANTUM, G_QUANTUM_DEFAULT)
, NVL(QUOTA, G_QUOTA_DEFAULT)
, NVL(QUOTA_RESET, G_QUOTA_RESET_DEFAULT)
, STATUS_CODE
, LOAD_PRIORITY
INTO L_RELEASE_STRATEGY
, L_QUANTUM
, L_QUOTA
, L_QUOTA_RESET
, L_STATUS_CODE
, L_LOAD_PRIORITY
FROM IEC_G_LIST_SUBSETS
WHERE LIST_SUBSET_ID = P_SUBSET_ID;
INSERT INTO IEC_G_SUBSET_RT_INFO
( SUBSET_RT_INFO_ID
, LIST_SUBSET_ID
, WORKING_QUANTUM
, WORKING_QUOTA
, QUOTA_RESET_TIME
, CACHE_AMT_NEEDED
, VALID_FLAG
, USE_FLAG
, CALLABLE_FLAG
, TOTAL_CACHE_COUNT
, STATUS_CODE
, LOAD_PRIORITY
, CREATED_BY
, CREATION_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
)
VALUES
( IEC_G_SUBSET_RT_INFO_S.NEXTVAL
, P_SUBSET_ID
, L_QUANTUM
, L_QUOTA
, SYSDATE + L_QUOTA_RESET
, NULL
, 'Y'
, 'Y'
, 'Y'
, 0
, L_STATUS_CODE
, L_LOAD_PRIORITY
, L_USERID
, SYSDATE
, L_LOGIN_USERID
, SYSDATE
);
' UPDATE IEC_G_SUBSET_RT_INFO
SET STATUS_CODE = :1
, LOAD_PRIORITY = :2
, LAST_UPDATE_DATE = SYSDATE
WHERE LIST_SUBSET_ID = :3'
USING L_STATUS_CODE, L_LOAD_PRIORITY, P_SUBSET_ID;
END; -- end of block for inserting entry into IEC_G_SUBSET_RT_INFO table.
SELECT NVL(DEFAULT_SUBSET_FLAG, 'N')
INTO l_default_subset_Flag
FROM IEC_G_LIST_SUBSETS
WHERE LIST_HEADER_ID = P_TARGET_GROUP_ID
AND LIST_SUBSET_ID = P_SUBSET_ID;
select orig_subset_id
, new_subset_id
, do_not_use_flag
, orig_itm_cc_tz_id
, new_itm_cc_tz_id
, call_Attempts
, count(*) NUM_ENTRIES
from iec_o_transition_subsets
where (record_out_Flag = 'N' OR record_out_flag = 'R')
-- and new_subset_id <> orig_subset_id
and list_id = P_TARGET_GROUP_ID
group by orig_subset_id
, new_Subset_id
, do_not_use_flag
, orig_itm_cc_tz_id
, new_itm_cc_Tz_id
, call_Attempts
)
LOOP
----------------------------------------------------------------
-- Update the subset ids for the entries that have changed
-- subsets and are not checked out in bulk. These are only
-- available for updates by prefetching or validation and these
-- should not be allowed at the same time as the subset
-- transitioning for this schedule. I only updates these by time zones so I can
-- update one record at a time from the IEC_G_REP_SUBSET_COUNTS and
-- IEC_G_MKTG_ITEM_CC_TZS table to avoid deadlocks with validation
-- calendar and recycling.
----------------------------------------------------------------
TRACELOG('UPDATING RETURN_ENTRIES ');
EXECUTE IMMEDIATE 'UPDATE IEC_G_RETURN_ENTRIES A ' ||
'SET A.SUBSET_ID = :newSubset ' ||
', A.ITM_CC_TZ_ID = :newCallZone ' ||
', A.PULLED_SUBSET_ID = NULL ' ||
'WHERE A.LIST_ENTRY_ID IN (SELECT C.LIST_ENTRY_ID ' ||
'FROM IEC_O_TRANSITION_SUBSETS C ' ||
'WHERE C.LIST_ID = :listID ' ||
'AND C.NEW_SUBSET_ID = :newSubset ' ||
'AND C.ORIG_SUBSET_ID = :origSubset ' ||
'AND C.NEW_ITM_CC_TZ_ID = :newCall ' ||
'AND C.ORIG_ITM_CC_TZ_ID = :origCall ' ||
'AND C.CALL_ATTEMPTS = :callAttempts ' ||
'AND C.DO_NOT_USE_FLAG = :do_not_use_Flag ' ||
'AND (C.RECORD_OUT_FLAG = ''N'' OR C.RECORD_OUT_FLAG = ''R''))' ||
'AND A.LIST_HEADER_ID = :listID'
USING count_rec.new_subset_id
, count_rec.new_itm_cc_tz_id
, P_TARGET_GROUP_ID
, count_rec.new_subset_id
, count_rec.orig_subset_id
, count_rec.new_itm_cc_tz_id
, count_rec.orig_itm_cc_tz_id
, count_rec.call_Attempts
, count_rec.do_not_use_flag
, P_TARGET_GROUP_ID;
TRACELOG('UPDATED RETURN_ENTRIES ' || SQL%ROWCOUNT);
EXECUTE IMMEDIATE 'UPDATE ' || P_STYPE_VIEW_NAME || ' A ' ||
' SET A.REASON_CODE_S1 = ( SELECT /*+ index(B iec_o_transition_phones_u1) */ ITM_CC_TZ_ID ' ||
' FROM IEC_O_TRANSITION_PHONES B' ||
' WHERE B.PHONE_INDEX = 1 ' ||
' AND B.LIST_ENTRY_ID = A.LIST_ENTRY_ID ' ||
' AND B.LIST_ID = A.LIST_HEADER_ID ) ' ||
' , A.REASON_CODE_S2 = ( SELECT /*+ index(C iec_o_transition_phones_u1) */ C.ITM_CC_TZ_ID ' ||
' FROM IEC_O_TRANSITION_PHONES C' ||
' WHERE C.PHONE_INDEX = 2 ' ||
' AND C.LIST_ENTRY_ID = A.LIST_ENTRY_ID ' ||
' AND C.LIST_ID = A.LIST_HEADER_ID ) ' ||
' , A.REASON_CODE_S3 = ( SELECT /*+ index(D iec_o_transition_phones_u1) */ D.ITM_CC_TZ_ID ' ||
' FROM IEC_O_TRANSITION_PHONES D' ||
' WHERE D.PHONE_INDEX = 3 ' ||
' AND D.LIST_ENTRY_ID = A.LIST_ENTRY_ID ' ||
' AND D.LIST_ID = A.LIST_HEADER_ID ) ' ||
' , A.REASON_CODE_S4 = ( SELECT /*+ index(E iec_o_transition_phones_u1) */ E.ITM_CC_TZ_ID ' ||
' FROM IEC_O_TRANSITION_PHONES E' ||
' WHERE E.PHONE_INDEX = 4 ' ||
' AND E.LIST_ENTRY_ID = A.LIST_ENTRY_ID ' ||
' AND E.LIST_ID = A.LIST_HEADER_ID ) ' ||
' , A.REASON_CODE_S5 = ( SELECT /*+ index(F iec_o_transition_phones_u1) */ F.ITM_CC_TZ_ID ' ||
' FROM IEC_O_TRANSITION_PHONES F' ||
' WHERE F.PHONE_INDEX = 5 ' ||
' AND F.LIST_ENTRY_ID = A.LIST_ENTRY_ID ' ||
' AND F.LIST_ID = A.LIST_HEADER_ID ) ' ||
' , A.REASON_CODE_S6 = ( SELECT /*+ index(G iec_o_transition_phones_u1) */ G.ITM_CC_TZ_ID ' ||
' FROM IEC_O_TRANSITION_PHONES G' ||
' WHERE G.PHONE_INDEX = 6 ' ||
' AND G.LIST_ENTRY_ID = A.LIST_ENTRY_ID ' ||
' AND G.LIST_ID = A.LIST_HEADER_ID ) ' ||
'WHERE A.LIST_ENTRY_ID IN (SELECT H.LIST_ENTRY_ID ' ||
'FROM IEC_O_TRANSITION_SUBSETS H ' ||
'WHERE H.LIST_ID = :listID ' ||
'AND H.NEW_SUBSET_ID = :newSubset ' ||
'AND H.ORIG_SUBSET_ID = :origSubset ' ||
'AND H.NEW_ITM_CC_TZ_ID = :newCall ' ||
'AND H.ORIG_ITM_CC_TZ_ID = :origCall ' ||
'AND H.CALL_ATTEMPTS = :callAttempts ' ||
'AND H.DO_NOT_USE_FLAG = :do_not_use_Flag ' ||
'AND (H.RECORD_OUT_FLAG = ''N'' OR H.RECORD_OUT_FLAG = ''R''))' ||
'AND A.LIST_HEADER_ID = :listID'
USING P_TARGET_GROUP_ID
, count_rec.new_subset_id
, count_rec.orig_subset_id
, count_rec.new_itm_cc_tz_id
, count_rec.orig_itm_cc_tz_id
, count_rec.call_Attempts
, count_rec.do_not_use_flag
, P_TARGET_GROUP_ID;
TRACELOG('UPDATED VIEW ' || SQL%ROWCOUNT);
EXECUTE IMMEDIATE 'UPDATE IEC_G_MKTG_ITEM_CC_TZS ' ||
'SET RECORD_COUNT = RECORD_COUNT + :remainingCount ' ||
'WHERE ITM_CC_TZ_ID = :callZone'
USING count_rec.NUM_ENTRIES, count_rec.new_itm_cc_tz_id;
EXECUTE IMMEDIATE 'UPDATE IEC_G_MKTG_ITEM_CC_TZS ' ||
'SET RECORD_COUNT = RECORD_COUNT - :remainingCount ' ||
'WHERE ITM_CC_TZ_ID = :callZone'
USING count_rec.NUM_ENTRIES, count_rec.orig_itm_cc_tz_id;
UPDATE_SUBSET_COUNTS( P_CAMPAIGN_ID
, P_SCHEDULE_ID
, P_TARGET_GROUP_ID
, count_rec.new_subset_id
, count_rec.NUM_ENTRIES
, l_called_once_count
);
UPDATE_SUBSET_COUNTS( P_CAMPAIGN_ID
, P_SCHEDULE_ID
, P_TARGET_GROUP_ID
, count_rec.orig_subset_id
, (0 - count_rec.NUM_ENTRIES)
, (0 - l_called_once_count)
);
EXECUTE IMMEDIATE 'SELECT ITM_CC_TZ_ID FROM IEC_G_MKTG_ITEM_CC_TZS ' ||
' WHERE LIST_HEADER_ID = :listId AND SUBSET_ID = :subsetID ' ||
' AND (TERRITORY_CODE, TIMEZONE_ID, NVL(REGION_ID, -1)) = ' ||
' (SELECT TERRITORY_CODE, TIMEZONE_ID, NVL(REGION_ID, -1) ' ||
' FROM IEC_G_MKTG_ITEM_CC_TZS WHERE ITM_CC_TZ_ID = :xref_id)'
INTO X_NEW_XREF
USING P_TARGET_GROUP_ID, P_NEW_SUBSET_ID, P_ORIG_XREF;
EXECUTE IMMEDIATE 'INSERT INTO IEC_G_MKTG_ITEM_CC_TZS ' ||
' (ITM_CC_TZ_ID, LIST_HEADER_ID, CAMPAIGN_SCHEDULE_ID, TERRITORY_CODE, ' ||
' TIMEZONE_ID, LAST_CALLABLE_TIME, CALLABLE_FLAG, OBJECT_VERSION_NUMBER, ' ||
' SECURITY_GROUP_ID, LAST_UPDATE_DATE, RECORD_COUNT, REGION_ID, SUBSET_ID) '||
' select IEC_G_MKTG_ITEM_CC_TZS_S.NEXTVAL, :listID, :schedID ' ||
', a.territory_code, a.timezone_id, NULL, ''N'', 0, -1 ' ||
', SYSDATE, 0, a.region_id, :subsetId from ' ||
' (SELECT region_id, territory_code, timezone_id '||
' from iec_g_mktg_item_cc_tzs c where c.itm_cc_Tz_id = :xref_id) a ' ||
' RETURNING ITM_CC_TZ_ID '
INTO X_NEW_XREF
USING P_TARGET_GROUP_ID, P_SCHEDULE_ID, P_ORIG_XREF;
END; -- end insertingnew xref block.
select a.subset_id SUBSET_ID
, a.pulled_subset_id TRANSITION_SUBSET_ID
, a.list_entry_id LIST_ENTRY_ID
, a.record_out_flag RECORD_OUT_FLAG
, a.itm_cc_Tz_id ITM_CC_TZ_ID
, a.contact_point_index CONTACT_POINT_INDEX
, a.returns_id RETURNS_ID
, a.do_not_use_flag DO_NOT_USE_FLAG
, DECODE(NVL(SUM(B.CALL_ATTEMPT), 0), 0, 0, 1) CALLED_ONCE
from iec_g_return_entries a
, IEC_O_RCY_CALL_HISTORIES B
where a.LIST_HEADER_ID = P_TARGET_GROUP_ID
and a.pulled_subset_id IS NOT NULL
and a.returns_id = b.returns_id(+)
group by a.subset_id
, a.pulled_subset_id
, a.list_entry_id
, a.record_out_flag
, a.itm_cc_Tz_id
, a.contact_point_index
, a.returns_id
, a.do_not_use_flag
)
LOOP
----------------------------------------------------------------
-- If the entry is still checked out then simply update the
-- counter.
----------------------------------------------------------------
IF (entry_rec.record_out_flag = 'Y')
THEN
X_NUM_REMAINING := X_NUM_REMAINING + 1;
EXECUTE IMMEDIATE 'SELECT NVL(A.reason_code_S1, -1), NVL(A.reason_code_S2, -1), ' ||
' NVL(A.reason_code_S3, -1), NVL(A.reason_code_S4, -1), ' ||
' NVL(A.reason_code_S5, -1), NVL(A.reason_code_S6, -1) ' ||
' FROM ' || l_src_type_view_name || ' a ' ||
' WHERE A.LIST_HEADER_ID = :listID AND A.LIST_ENTRY_ID = :entryId'
INTO l_old_itm_xref_id1, l_old_itm_xref_id2, l_old_itm_xref_id3
, l_old_itm_xref_id4, l_old_itm_xref_id5, l_old_itm_xref_id6
USING P_TARGET_GROUP_ID, entry_rec.list_entry_id;
EXECUTE IMMEDIATE 'UPDATE IEC_G_RETURN_ENTRIES SET SUBSET_ID = :subsetId ' ||
', PULLED_SUBSET_ID = NULL, ITM_CC_TZ_ID = :xrefId ' ||
' WHERE RETURNS_ID = :returnsId'
USING entry_rec.TRANSITION_SUBSET_ID
, l_curr_itm_xref_id
, entry_rec.RETURNS_ID;
EXECUTE IMMEDIATE 'UPDATE ' || l_src_type_view_name || ' SET ' ||
' REASON_CODE_S1 = :zoneXref1, ' ||
' REASON_CODE_S2 = :zoneXref2, ' ||
' REASON_CODE_S3 = :zoneXref3, ' ||
' REASON_CODE_S4 = :zoneXref4, ' ||
' REASON_CODE_S5 = :zoneXref5, ' ||
' REASON_CODE_S6 = :zoneXref6 ' ||
' WHERE LIST_HEADER_ID = :listID AND LIST_ENTRY_ID = :entryID '
USING l_new_itm_xref_id1
, l_new_itm_xref_id2
, l_new_itm_xref_id3
, l_new_itm_xref_id4
, l_new_itm_xref_id5
, l_new_itm_xref_id6
, P_TARGET_GROUP_ID, entry_rec.LIST_ENTRY_ID;
EXECUTE IMMEDIATE 'UPDATE IEC_G_MKTG_ITEM_CC_TZS ' ||
' SET RECORD_COUNT = RECORD_COUNT + 1' ||
', LAST_UPDATE_DATE = SYSDATE ' ||
' WHERE ITM_CC_TZ_ID = :xrefId '
USING l_curr_itm_xref_id;
EXECUTE IMMEDIATE 'UPDATE IEC_G_MKTG_ITEM_CC_TZS ' ||
' SET RECORD_COUNT = RECORD_COUNT - 1' ||
', LAST_UPDATE_DATE = SYSDATE ' ||
' WHERE ITM_CC_TZ_ID = :xrefId '
USING entry_rec.ITM_CC_TZ_ID;
UPDATE_SUBSET_COUNTS( P_CAMPAIGN_ID
, P_SCHEDULE_ID
, P_TARGET_GROUP_ID
, entry_rec.TRANSITION_SUBSET_ID
, 1
, entry_rec.CALLED_ONCE
);
UPDATE_SUBSET_COUNTS( P_CAMPAIGN_ID
, P_SCHEDULE_ID
, P_TARGET_GROUP_ID
, entry_rec.SUBSET_ID
, (0 - 1)
, (0 - entry_rec.CALLED_ONCE)
);
EXECUTE IMMEDIATE 'SELECT LIST_SUBSET_ID FROM IEC_G_LIST_SUBSETS ' ||
'WHERE LIST_HEADER_ID = :listID AND DEFAULT_SUBSET_FLAG = ''Y'''
INTO X_DEFAULT_SUBSET_ID
USING P_TARGET_GROUP_ID;
EXECUTE IMMEDIATE 'UPDATE IEC_G_RETURN_ENTRIES A ' ||
'SET A.PULLED_SUBSET_ID = :newSubsetId ' ||
'WHERE A.RETURNS_ID IN (SELECT * FROM TABLE(CAST(:collection AS SYSTEM.NUMBER_TBL_TYPE))) ' ||
'AND A.RECORD_OUT_FLAG = ''N'' AND A.PULLED_SUBSET_ID IS NULL'
USING P_INTO_SUBSET
, l_return_tbl;
OPEN l_checked_entry_cursor FOR 'SELECT RETURNS_ID FROM IEC_G_RETURN_ENTRIES ' ||
'WHERE PULLED_SUBSET_ID IS NULL ' ||
'AND RETURNS_ID IN (SELECT * FROM TABLE(CAST(:collection AS SYSTEM.NUMBER_TBL_TYPE)))'
USING l_return_tbl;
EXECUTE IMMEDIATE 'UPDATE IEC_G_RETURN_ENTRIES A ' ||
'SET PULLED_SUBSET_ID = :newSubsetId ' ||
'WHERE RETURNS_ID = :returnsId ' ||
'AND PULLED_SUBSET_ID IS NULL'
USING P_INTO_SUBSET
, l_curr_returns_id;
EXECUTE IMMEDIATE 'DELETE FROM iec_o_transition_subsets ' ||
'WHERE list_id = :1 '
USING P_TARGET_GROUP_ID;
EXECUTE IMMEDIATE 'INSERT INTO IEC_O_TRANSITION_SUBSETS ' ||
'( LIST_ID ' ||
', LIST_ENTRY_ID ' ||
', ORIG_SUBSET_ID ' ||
', NEW_SUBSET_ID ' ||
', ORIG_ITM_CC_TZ_ID ' ||
', NEW_ITM_CC_TZ_ID ' ||
', DO_NOT_USE_FLAG ' ||
', RECORD_OUT_FLAG ' ||
', RETURNS_ID ' ||
', CALL_ATTEMPTS ' ||
') ' ||
'SELECT A.LIST_HEADER_ID, A.LIST_ENTRY_ID, A.SUBSET_ID, A.PULLED_SUBSET_ID ' ||
', A.ITM_CC_TZ_ID, NULL, A.DO_NOT_USE_FLAG, ''N'', A.RETURNS_ID, ' ||
' DECODE(NVL(SUM(B.CALL_ATTEMPT), 0), 0, 0, 1) ' ||
' FROM IEC_G_RETURN_ENTRIES A, IEC_O_RCY_CALL_HISTORIES B ' ||
' WHERE A.SUBSET_ID = :oldSubsetId ' ||
' AND A.RETURNS_ID = B.RETURNS_ID(+) ' ||
' AND A.PULLED_SUBSET_ID = :newSubsetId' ||
' AND A.RECORD_OUT_FLAG = ''N'' GROUP BY ' ||
' A.LIST_HEADER_ID, A.LIST_ENTRY_ID, A.SUBSET_ID, A.PULLED_SUBSET_ID ' ||
', A.ITM_CC_TZ_ID, NULL, A.DO_NOT_USE_FLAG, ''N'', A.RETURNS_ID'
USING P_FROM_SUBSET
, P_INTO_SUBSET;
EXECUTE IMMEDIATE 'DELETE FROM iec_o_transition_phones where list_id = :1'
USING P_TARGET_GROUP_ID;
INSERT INTO iec_o_transition_phones
( LIST_ID
, LIST_ENTRY_ID
, SUBSET_ID
, territory_code
, region_id
, timezone_id
, phone_index
)
VALUES
( P_TARGET_GROUP_ID
, l_phone_entry_tbl(j)
, l_phone_subset_tbl(j)
, l_territory_tbl(j)
, l_region_tbl(j)
, l_timezone_tbl(j)
, l_index_tbl(j));
l_phone_entry_tbl.DELETE;
l_phone_subset_tbl.DELETE;
l_territory_tbl.DELETE;
l_region_tbl.DELETE;
l_timezone_tbl.DELETE;
l_index_tbl.DELETE;
EXECUTE IMMEDIATE 'INSERT INTO IEC_G_MKTG_ITEM_CC_TZS ' ||
' (ITM_CC_TZ_ID, LIST_HEADER_ID, CAMPAIGN_SCHEDULE_ID, TERRITORY_CODE, ' ||
' TIMEZONE_ID, LAST_CALLABLE_TIME, CALLABLE_FLAG, OBJECT_VERSION_NUMBER, ' ||
' SECURITY_GROUP_ID, LAST_UPDATE_DATE, RECORD_COUNT, REGION_ID, SUBSET_ID) '||
' select IEC_G_MKTG_ITEM_CC_TZS_S.NEXTVAL, :listID, :schedID ' ||
', a.territory_code, a.timezone_id, NULL, ''N'', 0, -1 ' ||
', SYSDATE, 0, a.region_code, a.subset_id from ' ||
' (SELECT DISTINCT DECODE(region_id, -1, NULL, region_id) region_code, territory_code, subset_id, timezone_id '||
' from iec_o_transition_phones c where c.list_id = :listID ' ||
' and territory_code <> ''-1'' and timezone_id <> -1 ' ||
' and not exists (select null from iec_g_mktg_item_Cc_Tzs b where ' ||
' c.subset_id = b.subset_id and c.territory_code = b.territory_code ' ||
' and NVL(b.region_id, -1) = c.region_id and c.timezone_id = b.timezone_id ' ||
' and c.list_id = b.list_header_id) ) a'
USING P_TARGET_GROUP_ID
, P_SCHED_ID
, P_TARGET_GROUP_ID;
EXECUTE IMMEDIATE 'UPDATE IEC_O_TRANSITION_PHONES A SET A.ITM_CC_TZ_ID = ' ||
'( SELECT B.ITM_CC_TZ_ID FROM IEC_G_MKTG_ITEM_CC_TZS B ' ||
' WHERE B.LIST_HEADER_ID = A.LIST_ID AND B.SUBSET_ID = A.SUBSET_ID ' ||
' AND B.TERRITORY_CODE = A.TERRITORY_CODE AND B.TIMEZONE_ID = A.TIMEZONE_ID ' ||
' AND NVL(B.REGION_ID, -1) = A.REGION_ID) WHERE A.LIST_ID = :listID ' ||
' AND A.TERRITORY_CODE <> ''-1'' AND A.TIMEZONE_ID <> -1 '
USING P_TARGET_GROUP_ID;
EXECUTE IMMEDIATE 'UPDATE IEC_O_TRANSITION_SUBSETS A SET A.NEW_ITM_CC_TZ_ID = ' ||
' (SELECT D.ITM_CC_TZ_ID ' ||
' FROM IEC_G_RETURN_ENTRIES B, IEC_G_MKTG_ITEM_CC_TZS C, IEC_G_MKTG_ITEM_CC_TZS D ' ||
' WHERE A.LIST_ENTRY_ID = B.LIST_ENTRY_ID AND A.LIST_ID = B.LIST_HEADER_ID ' ||
' AND B.LIST_HEADER_ID = C.LIST_HEADER_ID AND B.ITM_CC_TZ_ID = C.ITM_CC_TZ_ID' ||
' AND D.TERRITORY_CODE = C.TERRITORY_CODE AND D.SUBSET_ID = A.NEW_SUBSET_ID AND ' ||
' D.TIMEZONE_ID = C.TIMEZONE_ID AND D.LIST_HEADER_ID = C.LIST_HEADER_ID ' ||
' AND NVL(D.REGION_ID, -1) = NVL(C.REGION_ID, -1)) ' ||
' WHERE A.LIST_ID = :list_ID' ||
' AND A.ORIG_SUBSET_ID <> A.NEW_SUBSET_ID'
USING P_TARGET_GROUP_ID;
l_return_tbl.DELETE;
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM IEC_G_RETURN_ENTRIES WHERE SUBSET_ID = :1 and PULLED_SUBSET_ID IS NOT NULL'
INTO X_NUM_PENDING
USING P_FROM_SUBSET;
l_return_tbl.DELETE;
EXECUTE IMMEDIATE 'SELECT NVL(RECORD_LOADED,0) ' ||
' FROM IEC_G_REP_SUBSET_COUNTS ' ||
' WHERE subset_id = :subsetId '
INTO l_subset_load_size
USING P_FROM_SUBSETS(1);
EXECUTE IMMEDIATE 'SELECT NVL(SUM(RECORD_COUNT),0) ' ||
' FROM IEC_G_MKTG_ITEM_CC_TZS ' ||
' WHERE subset_id = :subsetId '
INTO l_subset_left_size
USING P_FROM_SUBSETS(1);
EXECUTE IMMEDIATE 'SELECT NVL(SUM(RECORD_COUNT),0) ' ||
' FROM IEC_G_MKTG_ITEM_CC_TZS ' ||
' WHERE subset_id = :subsetId ' ||
' AND CALLABLE_FLAG = ''Y'' ' ||
' AND LAST_CALLABLE_TIME > SYSDATE '
INTO l_subset_callable_size
USING P_FROM_SUBSETS(1);
l_phone_sql := 'SELECT a.list_entry_id, h.new_subset_id ' ||
',1, NVL(b.TERRITORY_CODE, ''-1''), NVL(b.TIMEZONE_ID, -1), NVL(b.REGION_ID, -1) ' ||
',2, NVL(c.TERRITORY_CODE, ''-1''), NVL(c.TIMEZONE_ID, -1), NVL(c.REGION_ID, -1) ' ||
',3, NVL(d.TERRITORY_CODE, ''-1''), NVL(d.TIMEZONE_ID, -1), NVL(d.REGION_ID, -1) ' ||
',4, NVL(e.TERRITORY_CODE, ''-1''), NVL(e.TIMEZONE_ID, -1), NVL(e.REGION_ID, -1) ' ||
',5, NVL(f.TERRITORY_CODE, ''-1''), NVL(f.TIMEZONE_ID, -1), NVL(f.REGION_ID, -1) ' ||
',6, NVL(g.TERRITORY_CODE, ''-1''), NVL(g.TIMEZONE_ID, -1), NVL(g.REGION_ID, -1) ' ||
'from ' || l_src_type_view_name || ' a ' ||
', iec_g_mktg_item_cc_Tzs b ' ||
', iec_g_mktg_item_cc_Tzs c ' ||
', iec_g_mktg_item_cc_Tzs d ' ||
', iec_g_mktg_item_cc_Tzs e ' ||
', iec_g_mktg_item_cc_Tzs f ' ||
', iec_g_mktg_item_cc_Tzs g ' ||
', IEC_O_TRANSITION_SUBSETS h ' ||
'where h.list_id = :listID ' ||
'and h.list_id = a.list_header_id ' ||
'and h.list_entry_id = a.list_entry_id ' ||
'and a.reason_code_S1 = b.itm_cc_tz_id(+) ' ||
'and a.reason_code_S2 = c.itm_cc_tz_id(+) ' ||
'and a.reason_code_S3 = d.itm_cc_tz_id(+) ' ||
'and a.reason_code_S4 = e.itm_cc_tz_id(+) ' ||
'and a.reason_code_S5 = f.itm_cc_tz_id(+) ' ||
'and a.reason_code_S6 = g.itm_cc_tz_id(+)';
l_first_query_entry_sql := 'SELECT A.RETURNS_ID ' ||
' FROM IEC_G_RETURN_ENTRIES A ' ||
' WHERE A.SUBSET_ID = :subsetId ' ||
' AND A.PULLED_SUBSET_ID IS NULL ' ||
' AND A.ITM_CC_TZ_ID IN ' ||
' (SELECT ITM_CC_TZ_ID FROM ' ||
' IEC_G_MKTG_ITEM_CC_TZS B WHERE ' ||
' A.SUBSET_ID = B.SUBSET_ID ' ||
' AND B.CALLABLE_FLAG = ''Y'' ' ||
' AND B.LAST_CALLABLE_TIME > SYSDATE) ' ||
' AND A.LIST_ENTRY_ID IN (SELECT C.LIST_ENTRY_ID ' ||
' FROM ' || l_subset_view_name || ' C)';
l_first_query_entry_sql := 'SELECT A.RETURNS_ID ' ||
' FROM IEC_G_RETURN_ENTRIES A ' ||
' WHERE A.SUBSET_ID = :subsetId ' ||
' AND A.PULLED_SUBSET_ID IS NULL ' ||
' AND A.DO_NOT_USE_FLAG = ''N'' ' ||
' AND A.LIST_ENTRY_ID IN (SELECT C.LIST_ENTRY_ID ' ||
' FROM ' || l_subset_view_name || ' C)';
l_second_query_entry_sql := 'SELECT A.RETURNS_ID ' ||
' FROM IEC_G_RETURN_ENTRIES A ' ||
' WHERE A.SUBSET_ID = :subsetId ' ||
' AND A.PULLED_SUBSET_ID IS NULL ' ||
' AND A.LIST_ENTRY_ID IN (SELECT C.LIST_ENTRY_ID ' ||
' FROM ' || l_subset_view_name || ' C)';
l_first_query_entry_sql := 'SELECT A.RETURNS_ID ' ||
' FROM IEC_G_RETURN_ENTRIES A ' ||
' WHERE A.SUBSET_ID = :subsetId ' ||
' AND A.PULLED_SUBSET_ID IS NULL ' ||
' AND A.ITM_CC_TZ_ID IN ' ||
' (SELECT ITM_CC_TZ_ID FROM ' ||
' IEC_G_MKTG_ITEM_CC_TZS B WHERE ' ||
' A.SUBSET_ID = B.SUBSET_ID ' ||
' AND B.CALLABLE_FLAG = ''Y'' ' ||
' AND B.LAST_CALLABLE_TIME > SYSDATE)';
l_first_query_entry_sql := 'SELECT A.RETURNS_ID ' ||
' FROM IEC_G_RETURN_ENTRIES A ' ||
' WHERE A.SUBSET_ID = :subsetId ' ||
' AND A.PULLED_SUBSET_ID IS NULL ' ||
' AND A.DO_NOT_USE_FLAG = ''N'' ';
l_second_query_entry_sql := 'SELECT A.RETURNS_ID ' ||
' FROM IEC_G_RETURN_ENTRIES A ' ||
' WHERE A.SUBSET_ID = :subsetId ' ||
' AND A.PULLED_SUBSET_ID IS NULL ';