[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 Log_StatusUpdateError
( p_method IN VARCHAR2
, p_sub_method IN VARCHAR2
, p_list_name IN VARCHAR2)
IS
BEGIN
IEC_OCS_LOG_PVT.Init_StatusUpdateErrorMsg
( p_list_name
, 'IEC_STATUS_PVT.UPDATE_LIST_STATUS'
, g_message
, g_encoded_message
);
END Log_StatusUpdateError;
SELECT object_type into l_check_view_table
FROM USER_OBJECTS
where OBJECT_NAME = p_source_type_view
and object_type = 'VIEW';
SELECT COLUMN_NAME
BULK COLLECT INTO l_columns
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME = p_source_type_view
AND OWNER = l_table_owner;
SELECT col.column_name
BULK COLLECT INTO l_columns
FROM ALL_TAB_COLUMNS col,
USER_SYNONYMS SYN
WHERE syn.synonym_name = p_source_type_view
AND col.owner = syn.table_owner
AND col.table_name = syn.table_name;
l_curr := 'NEWLY_UPDATED_FLAG';
'SELECT LIST_SOURCE_TYPE
FROM AMS_LIST_HEADERS_ALL
WHERE LIST_HEADER_ID = :list_id'
INTO l_source_type
USING IN p_list_id;
SELECT 'TRUNCATE TABLE ' || OWNER || '.' || TABLE_NAME SQLSTMT
FROM ALL_TABLES
WHERE TABLE_NAME = 'IEC_VAL_ENTRY_CACHE'
AND OWNER = table_owner;
delete from IEC_VAL_ENTRY_CACHE;
SELECT 'TRUNCATE TABLE ' || OWNER || '.' || TABLE_NAME SQLSTMT
FROM ALL_TABLES
WHERE TABLE_NAME IN ('IEC_TC_TZ_PAIRS_CACHE', 'IEC_TZ_MAPPING_CACHE', 'IEC_TZ_OFFSET_MAP_CACHE', 'IEC_VAL_ENTRY_CACHE')
AND OWNER = table_owner;
delete from IEC_TC_TZ_PAIRS_CACHE;
delete from IEC_TZ_MAPPING_CACHE;
delete from IEC_TZ_OFFSET_MAP_CACHE;
delete from IEC_VAL_ENTRY_CACHE;
SELECT OWNER || '.' || MVIEW_NAME MVIEW_LNAME
FROM ALL_MVIEWS
WHERE MVIEW_NAME IN ('IEC_O_VAL_DNU_S1_COUNTS_MV', 'IEC_O_VAL_DNU_S2_COUNTS_MV', 'IEC_O_VAL_DNU_S3_COUNTS_MV', 'IEC_O_VAL_DNU_S4_COUNTS_MV', 'IEC_O_VAL_DNU_S5_COUNTS_MV', 'IEC_O_VAL_DNU_S6_COUNTS_MV')
AND OWNER = mview_owner;
FOR cc_rec IN (SELECT DISTINCT B.PHONE_COUNTRY_CODE, B.AREA_CODE_LENGTH
FROM HZ_PHONE_COUNTRY_CODES B
WHERE B.AREA_CODE_LENGTH IS NOT NULL
AND (1, B.PHONE_COUNTRY_CODE) = (SELECT COUNT(*) COUNT, PHONE_COUNTRY_CODE
FROM (SELECT DISTINCT PHONE_COUNTRY_CODE, AREA_CODE_LENGTH
FROM HZ_PHONE_COUNTRY_CODES
WHERE AREA_CODE_LENGTH IS NOT NULL)
WHERE PHONE_COUNTRY_CODE = B.PHONE_COUNTRY_CODE GROUP BY PHONE_COUNTRY_CODE))
LOOP
g_ac_length_tbl(cc_rec.PHONE_COUNTRY_CODE) := cc_rec.AREA_CODE_LENGTH;
FOR cc_rec IN (SELECT DISTINCT B.PHONE_COUNTRY_CODE, (B.PHONE_LENGTH - B.AREA_CODE_LENGTH) PN_LENGTH
FROM HZ_PHONE_COUNTRY_CODES B
WHERE B.PHONE_LENGTH IS NOT NULL
AND (1, B.PHONE_COUNTRY_CODE) = (SELECT COUNT(*) COUNT, PHONE_COUNTRY_CODE
FROM (SELECT DISTINCT PHONE_COUNTRY_CODE, PHONE_LENGTH
FROM HZ_PHONE_COUNTRY_CODES
WHERE PHONE_LENGTH IS NOT NULL)
WHERE PHONE_COUNTRY_CODE = B.PHONE_COUNTRY_CODE GROUP BY PHONE_COUNTRY_CODE))
LOOP
g_pn_length_tbl(cc_rec.PHONE_COUNTRY_CODE) := cc_rec.PN_LENGTH;
FOR cc_rec IN (SELECT DISTINCT PHONE_COUNTRY_CODE, TERRITORY_CODE
FROM HZ_PHONE_COUNTRY_CODES A WHERE 1 = (SELECT COUNT(*)
FROM HZ_PHONE_COUNTRY_CODES
WHERE PHONE_COUNTRY_CODE = A.PHONE_COUNTRY_CODE))
LOOP
g_cc_tc_lookup_tbl(cc_rec.PHONE_COUNTRY_CODE) := cc_rec.TERRITORY_CODE;
FOR cc_rec IN (SELECT DISTINCT PHONE_COUNTRY_CODE
FROM HZ_PHONE_COUNTRY_CODES)
LOOP
g_cc_cc_lookup_tbl(cc_rec.PHONE_COUNTRY_CODE) := cc_rec.PHONE_COUNTRY_CODE;
FOR cc_rec IN (SELECT DISTINCT PHONE_COUNTRY_CODE, AREA_CODE, TERRITORY_CODE
FROM HZ_PHONE_AREA_CODES)
LOOP
g_cc_ac_tc_lookup_tbl(cc_rec.PHONE_COUNTRY_CODE || cc_rec.AREA_CODE) := cc_rec.TERRITORY_CODE;
FOR cc_rec IN (SELECT UPGRADE_TZ_ID FROM FND_TIMEZONES_VL) -- bug6449880
LOOP
-- g_tz_lookup_tbl(cc_rec.TIMEZONE_ID) := cc_rec.TIMEZONE_ID; //bug6449880
'SELECT VALIDATION_ACTION_BLOCK_ID
FROM IEC_G_LIST_RT_INFO
WHERE LIST_HEADER_ID = :list_id'
INTO l_rule_block_id
USING p_list_id;
'SELECT ACTION_ID
FROM IEC_O_ALG_ACTIONS
WHERE PARENT_BLOCK_ID = :rule_block_id
AND ACTION_CODE = :action_code'
INTO l_action_id
USING l_rule_block_id, l_action_code;
'SELECT ACTION_ID
FROM IEC_O_ALG_ACTIONS
WHERE PARENT_BLOCK_ID = :rule_block_id
AND ACTION_CODE = :action_code'
INTO l_action_id
USING l_rule_block_id, l_action_code;
'SELECT ACTION_ID
FROM IEC_O_ALG_ACTIONS
WHERE PARENT_BLOCK_ID = :rule_block_id
AND ACTION_CODE = :action_code'
INTO l_action_id
USING l_rule_block_id, l_action_code;
'SELECT ACTION_ID
FROM IEC_O_ALG_ACTIONS
WHERE PARENT_BLOCK_ID = :rule_block_id
AND ACTION_CODE = :action_code'
INTO l_action_id
USING l_rule_block_id, l_action_code;
'SELECT ACTION_ID
FROM IEC_O_ALG_ACTIONS
WHERE PARENT_BLOCK_ID = :rule_block_id
AND ACTION_CODE = :action_code'
INTO l_action_id
USING l_rule_block_id, l_action_code;
'SELECT ACTION_ID
FROM IEC_O_ALG_ACTIONS
WHERE PARENT_BLOCK_ID = :rule_block_id
AND ACTION_CODE = :action_code'
INTO l_action_id
USING l_rule_block_id, l_action_code;
SELECT B.DATA_VALUE BULK COLLECT INTO :data_value_col
FROM IEC_O_ALG_ACTIONS A, IEC_O_ALG_DATA B
WHERE A.ACTION_ID = B.OWNER_ID
AND PARENT_BLOCK_ID = :rule_block_id
AND ACTION_CODE = :action_code AND DATA_CODE = :data_code
AND ROWNUM = 1;
SELECT B.DATA_VALUE BULK COLLECT INTO :data_value_col
FROM IEC_O_ALG_ACTIONS A, IEC_O_ALG_DATA B
WHERE A.ACTION_ID = B.OWNER_ID
AND PARENT_BLOCK_ID = :rule_block_id
AND ACTION_CODE = :action_code AND DATA_CODE = :data_code
AND ROWNUM = 1;
'SELECT PHONE_COUNTRY_CODE
FROM HZ_PHONE_COUNTRY_CODES
WHERE TERRITORY_CODE = :territory_code'
INTO g_phone_country_code
USING g_territory_code;
SELECT B.DATA_VALUE BULK COLLECT INTO :data_value_col
FROM IEC_O_ALG_ACTIONS A, IEC_O_ALG_DATA B
WHERE A.ACTION_ID = B.OWNER_ID
AND PARENT_BLOCK_ID = :rule_block_id
AND ACTION_CODE = :action_code AND DATA_CODE = :data_code
AND ROWNUM = 1;
SELECT B.DATA_VALUE BULK COLLECT INTO :data_value_col
FROM IEC_O_ALG_ACTIONS A, IEC_O_ALG_DATA B
WHERE A.ACTION_ID = B.OWNER_ID
AND PARENT_BLOCK_ID = :rule_block_id
AND ACTION_CODE = :action_code AND DATA_CODE = :data_code
AND ROWNUM = 1;
'SELECT ACTION_ID
FROM IEC_O_ALG_ACTIONS
WHERE PARENT_BLOCK_ID = :rule_block_id
AND ACTION_CODE = :action_code'
INTO l_action_id
USING l_rule_block_id, l_action_code;
'SELECT ACTION_ID
FROM IEC_O_ALG_ACTIONS
WHERE PARENT_BLOCK_ID = :rule_block_id
AND ACTION_CODE = :action_code'
INTO l_action_id
USING l_rule_block_id, l_action_code;
SELECT B.DATA_CODE, B.DATA_VALUE BULK COLLECT INTO :data_code_col, :data_value_col
FROM IEC_O_ALG_ACTIONS A, IEC_O_ALG_DATA B
WHERE A.ACTION_ID = B.OWNER_ID
AND PARENT_BLOCK_ID = :rule_block_id
AND ACTION_CODE = :action_code
AND DATA_CODE IN (:data_code, ''TIMEZONE_VALUE'')
ORDER BY A.ACTION_ID, B.DATA_CODE;
'INSERT INTO IEC_TZ_OFFSET_MAP_CACHE (OFFSET, TIMEZONE_ID) VALUES (:timezone_offset, :timezone)'
USING l_data_value_col(l_count), l_data_value_col(l_count + 1);
INSERT INTO IEC_TZ_MAPPING_CACHE
(TERRITORY_CODE, AREA_CODE, POSTAL_CODE, TIMEZONE_ID)
SELECT DISTINCT TERRITORY_CODE, PHONE_AREA_CODE, POSTAL_CODE, TIMEZONE_ID
FROM IEC_G_TIMEZONE_MAPPINGS;
INSERT INTO IEC_TZ_MAPPING_CACHE
(TERRITORY_CODE, AREA_CODE, POSTAL_CODE, TIMEZONE_ID)
SELECT DISTINCT TERRITORY_CODE, PHONE_AREA_CODE, NULL, TIMEZONE_ID
FROM IEC_G_TIMEZONE_MAPPINGS;
PROCEDURE Update_ReportCounts
( p_campaign_id IN NUMBER
, p_schedule_id IN NUMBER
, p_list_id IN NUMBER
, p_subset_id_col IN SYSTEM.number_tbl_type
, p_sub_rec_loaded_incr_col IN SYSTEM.number_tbl_type)
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_col(i);
'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
, 0
, 0
, 0
, SYSDATE
, 1
, SYSDATE
, 1
, SYSDATE
, 0
, 0)'
USING p_campaign_id
, p_schedule_id
, p_list_id
, p_subset_id_col(i)
, p_sub_rec_loaded_incr_col(i);
'UPDATE IEC_G_REP_SUBSET_COUNTS
SET RECORD_LOADED = RECORD_LOADED + :records_loaded
, LAST_UPDATE_DATE = SYSDATE
WHERE SUBSET_ID = :subset_id'
USING p_sub_rec_loaded_incr_col(i)
, p_subset_id_col(i);
Log( 'Update_ReportCounts'
, 'MAIN'
, SQLERRM
);
END Update_ReportCounts;
PROCEDURE Update_Status
( p_list_id IN NUMBER
, p_status IN VARCHAR2)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
'UPDATE IEC_O_VALIDATION_STATUS
SET VALIDATED_ONCE_FLAG = ''Y''
, VALIDATION_END_TIME = :end_time
, LAST_UPDATE_DATE = SYSDATE
WHERE LIST_HEADER_ID = :list_id'
USING g_end_time, p_list_id;
Log('UPDATE_STATUS', 'DO_IEC_STAT_UPD_VALIDATED', SQLERRM);
'UPDATE IEC_O_VALIDATION_STATUS
SET SCHEDULED_EXECUTION_TIME = NULL
, USER_SCHEDULED_EXECUTION_TIME = NULL
, USER_TIMEZONE_ID = NULL
, VALIDATION_START_TIME = :start_time
, VALIDATION_END_TIME = NULL
, LAST_UPDATE_DATE = SYSDATE
WHERE LIST_HEADER_ID = :list_id'
USING g_start_time, p_list_id;
Log('UPDATE_STATUS', 'DO_IEC_STAT_UPD_VALIDATING', SQLERRM);
'UPDATE IEC_O_VALIDATION_STATUS
SET VALIDATION_END_TIME = SYSDATE
, LAST_UPDATE_DATE = SYSDATE
WHERE LIST_HEADER_ID = :list_id'
USING p_list_id;
Log('UPDATE_STATUS', 'DO_IEC_STAT_UPD_FAILED_VALIDATION', SQLERRM);
Iec_Status_Pvt.Update_List_Status(p_list_id, p_status);
Log_StatusUpdateError('Update_Status', 'DO_MKT_STATUS_UPDATE', Get_ListName(p_list_id));
Log('UPDATE_STATUS', 'MAIN', SQLERRM);
END Update_Status;
PROCEDURE Update_AmsListHeaderCounts
( p_list_id IN NUMBER
, p_rows_in_list_incr IN NUMBER
, p_rows_active_incr IN NUMBER
)
IS
l_api_version CONSTANT NUMBER := 1.0;
l_listheader_rec.last_update_date := FND_API.g_miss_date;
l_listheader_rec.last_updated_by := FND_API.g_miss_num;
l_listheader_rec.last_update_login := FND_API.g_miss_num;
l_listheader_rec.program_update_date := FND_API.g_miss_date;
l_listheader_rec.row_selection_type := FND_API.g_miss_char;
l_listheader_rec.main_random_nth_row_selection := FND_API.g_miss_num;
l_listheader_rec.main_random_pct_row_selection := FND_API.g_miss_num;
l_listheader_rec.ctrl_random_nth_row_selection := FND_API.g_miss_num;
l_listheader_rec.ctrl_random_pct_row_selection := FND_API.g_miss_num;
select nvl(no_of_rows_in_list,0), nvl(no_of_rows_active,0) into l_no_of_rows_in_list, l_no_of_rows_active from
AMS_LIST_HEADERS_ALL where list_header_id = p_list_id;
AMS_LISTHEADER_PUB.Update_ListHeader
( p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_listheader_rec => l_listheader_rec);
Log_msg('AMS_LISTHEADER_PUB.Update_ListHeader', l_msg_data);
'UPDATE AMS_LIST_HEADERS_ALL
SET NO_OF_ROWS_IN_LIST = NVL(NO_OF_ROWS_IN_LIST, 0) + :rows_incr
, NO_OF_ROWS_ACTIVE = NVL(NO_OF_ROWS_ACTIVE, 0) + :active_incr
WHERE LIST_HEADER_ID = :list_id'
USING IN p_rows_in_list_incr
, IN p_rows_active_incr
, IN p_list_id;
Log( 'Update_AmsListHeaderCounts'
, 'MAIN'
, SQLERRM
);
END Update_AmsListHeaderCounts;
PROCEDURE Update_MoveEntriesStatusCounts
( p_from_list_id IN NUMBER
, p_records_moved IN NUMBER
, p_records_updated IN NUMBER
)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
'UPDATE IEC_O_MOVE_RECORDS_STATUS
SET RECORDS_MOVED = NVL(RECORDS_MOVED, 0) + :records_moved
, RECORDS_UPDATED = NVL(RECORDS_UPDATED, 0) + :records_updated
, LAST_UPDATE_DATE = SYSDATE
WHERE FROM_LIST_HEADER_ID = :from_list_id'
USING p_records_moved, p_records_updated, p_from_list_id;
Log( 'Update_MoveEntriesStatusCounts'
, 'MAIN'
, SQLERRM
);
END Update_MoveEntriesStatusCounts;
PROCEDURE Update_MoveEntriesStatus
( p_from_list_id IN NUMBER
, p_to_list_id IN NUMBER
, p_status IN VARCHAR2
, p_api_initiated IN BOOLEAN)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
'SELECT COUNT(*)
FROM IEC_O_MOVE_RECORDS_STATUS
WHERE FROM_LIST_HEADER_ID = :from_list_id'
INTO l_count
USING p_from_list_id;
'SELECT IEC_O_MOVE_RECORDS_STATUS_S.NEXTVAL FROM DUAL'
INTO l_sequence;
'INSERT INTO IEC_O_MOVE_RECORDS_STATUS
( MOVE_RECORDS_STATUS_ID
, FROM_LIST_HEADER_ID
, TO_LIST_HEADER_ID
, MOVE_RECORDS_STATUS_CODE
, API_INITIATED_FLAG
, RECORDS_MOVED
, RECORDS_UPDATED
, CREATED_BY
, CREATION_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
, OBJECT_VERSION_NUMBER
)
VALUES
( :move_records_status_id
, :from_list_header_id
, :to_list_header_id
, ''PENDING_MOVE''
, :api_init_flag
, 0
, 0
, :user_id
, SYSDATE
, :user_id
, SYSDATE
, :login_id
, 0
)'
USING l_sequence
, p_from_list_id
, p_to_list_id
, l_api_init_flag
, l_user_id
, l_user_id
, l_login_id;
'UPDATE IEC_O_MOVE_RECORDS_STATUS
SET MOVE_RECORDS_STATUS_CODE = :status
, API_INITIATED_FLAG = NULL
, SCHEDULED_EXECUTION_TIME = NULL
, USER_SCHEDULED_EXECUTION_TIME = NULL
, USER_TIMEZONE_ID = NULL
, MOVE_RECORDS_END_TIME = SYSDATE
, LAST_UPDATE_DATE = SYSDATE
WHERE FROM_LIST_HEADER_ID = :from_list_id'
USING p_status, p_from_list_id;
'UPDATE IEC_O_MOVE_RECORDS_STATUS
SET MOVE_RECORDS_STATUS_CODE = :status
, TO_LIST_HEADER_ID = :to_list_id
, API_INITIATED_FLAG = :api_init_flag
, RECORDS_MOVED = 0
, RECORDS_UPDATED = 0
, SCHEDULED_EXECUTION_TIME = NULL
, USER_SCHEDULED_EXECUTION_TIME = NULL
, USER_TIMEZONE_ID = NULL
, MOVE_RECORDS_START_TIME = SYSDATE
, MOVE_RECORDS_END_TIME = NULL
, LAST_UPDATE_DATE = SYSDATE
WHERE FROM_LIST_HEADER_ID = :from_list_id'
USING p_status, p_to_list_id, l_api_init_flag, p_from_list_id;
'UPDATE IEC_O_MOVE_RECORDS_STATUS
SET MOVE_RECORDS_STATUS_CODE = :status
, API_INITIATED_FLAG = NULL
, SCHEDULED_EXECUTION_TIME = NULL
, USER_SCHEDULED_EXECUTION_TIME = NULL
, USER_TIMEZONE_ID = NULL
, MOVE_RECORDS_END_TIME = SYSDATE
, LAST_UPDATE_DATE = SYSDATE
WHERE FROM_LIST_HEADER_ID = :from_list_id'
USING p_status, p_from_list_id;
Log( 'Update_MoveEntriesStatus'
, 'MAIN'
, SQLERRM
);
END Update_MoveEntriesStatus;
PROCEDURE Update_PurgeStatus
( p_list_id IN NUMBER
, p_schedule_id IN NUMBER
, p_status IN VARCHAR2
, p_api_initiated IN BOOLEAN)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
'SELECT COUNT(*)
FROM IEC_G_LIST_RT_INFO
WHERE LIST_HEADER_ID = :list_id'
INTO l_count
USING p_list_id;
Log_ListRtInfoDNE('Update_PurgeStatus', 'CHECK_LIST_RT_INFO', Get_ScheduleName(p_schedule_id));
Iec_Status_Pvt.Update_List_Status(p_list_id, p_status, l_api_init_flag);
Log_StatusUpdateError('Update_PurgeStatus', 'DO_MKT_STATUS_UPDATE', Get_ListName(p_list_id));
Log('UPDATE_STATUS', 'MAIN', SQLERRM);
END Update_PurgeStatus;
PROCEDURE Update_AmsListEntries
( p_list_id IN NUMBER
, p_source_type_view IN VARCHAR2)
IS
-- UPDATE ALE
BEGIN
EXECUTE IMMEDIATE
'BEGIN
UPDATE ' || p_source_type_view || ' A' ||
' SET
( DO_NOT_USE_FLAG,
DO_NOT_USE_REASON,
NEWLY_UPDATED_FLAG,
CONTACT_POINT_ID_S1, TIME_ZONE_S1, PHONE_COUNTRY_CODE_S1, PHONE_AREA_CODE_S1, PHONE_NUMBER_S1, RAW_PHONE_NUMBER_S1, REASON_CODE_S1,
CONTACT_POINT_ID_S2, TIME_ZONE_S2, PHONE_COUNTRY_CODE_S2, PHONE_AREA_CODE_S2, PHONE_NUMBER_S2, RAW_PHONE_NUMBER_S2, REASON_CODE_S2,
CONTACT_POINT_ID_S3, TIME_ZONE_S3, PHONE_COUNTRY_CODE_S3, PHONE_AREA_CODE_S3, PHONE_NUMBER_S3, RAW_PHONE_NUMBER_S3, REASON_CODE_S3,
CONTACT_POINT_ID_S4, TIME_ZONE_S4, PHONE_COUNTRY_CODE_S4, PHONE_AREA_CODE_S4, PHONE_NUMBER_S4, RAW_PHONE_NUMBER_S4, REASON_CODE_S4,
CONTACT_POINT_ID_S5, TIME_ZONE_S5, PHONE_COUNTRY_CODE_S5, PHONE_AREA_CODE_S5, PHONE_NUMBER_S5, RAW_PHONE_NUMBER_S5, REASON_CODE_S5,
CONTACT_POINT_ID_S6, TIME_ZONE_S6, PHONE_COUNTRY_CODE_S6, PHONE_AREA_CODE_S6, PHONE_NUMBER_S6, RAW_PHONE_NUMBER_S6, REASON_CODE_S6
) =
( SELECT DO_NOT_USE_FLAG,
DO_NOT_USE_REASON,
''N'',
CONTACT_POINT_ID_S1, TIME_ZONE_S1, PHONE_COUNTRY_CODE_S1, PHONE_AREA_CODE_S1, PHONE_NUMBER_S1, RAW_PHONE_NUMBER_S1, MKTG_ITEM_CC_TZS_ID_S1,
CONTACT_POINT_ID_S2, TIME_ZONE_S2, PHONE_COUNTRY_CODE_S2, PHONE_AREA_CODE_S2, PHONE_NUMBER_S2, RAW_PHONE_NUMBER_S2, MKTG_ITEM_CC_TZS_ID_S2,
CONTACT_POINT_ID_S3, TIME_ZONE_S3, PHONE_COUNTRY_CODE_S3, PHONE_AREA_CODE_S3, PHONE_NUMBER_S3, RAW_PHONE_NUMBER_S3, MKTG_ITEM_CC_TZS_ID_S3,
CONTACT_POINT_ID_S4, TIME_ZONE_S4, PHONE_COUNTRY_CODE_S4, PHONE_AREA_CODE_S4, PHONE_NUMBER_S4, RAW_PHONE_NUMBER_S4, MKTG_ITEM_CC_TZS_ID_S4,
CONTACT_POINT_ID_S5, TIME_ZONE_S5, PHONE_COUNTRY_CODE_S5, PHONE_AREA_CODE_S5, PHONE_NUMBER_S5, RAW_PHONE_NUMBER_S5, MKTG_ITEM_CC_TZS_ID_S5,
CONTACT_POINT_ID_S6, TIME_ZONE_S6, PHONE_COUNTRY_CODE_S6, PHONE_AREA_CODE_S6, PHONE_NUMBER_S6, RAW_PHONE_NUMBER_S6, MKTG_ITEM_CC_TZS_ID_S6
FROM IEC_VAL_ENTRY_CACHE B WHERE A.LIST_ENTRY_ID = B.LIST_ENTRY_ID
)
WHERE LIST_HEADER_ID = :list_id AND LIST_ENTRY_ID IN (SELECT LIST_ENTRY_ID FROM IEC_VAL_ENTRY_CACHE);
Log_MissingSourceTypeColumns(p_list_id, p_source_type_view, Get_SourceType(p_list_id), 'UPDATE_AMS_LIST_ENTRIES', 'UPDATE_LIST_ENTRIES');
Log( 'Update_AmsListEntries'
, 'MAIN'
, SQLERRM
);
END Update_AmsListEntries;
PROCEDURE Update_ValidationReportDetails
(p_list_id IN NUMBER)
IS
l_user_id NUMBER;
'UPDATE IEC_O_VALIDATION_REPORT_DETS A
SET
( DO_NOT_USE_REASON_S1
, DO_NOT_USE_REASON_S2
, DO_NOT_USE_REASON_S3
, DO_NOT_USE_REASON_S4
, DO_NOT_USE_REASON_S5
, DO_NOT_USE_REASON_S6
) =
( SELECT
DO_NOT_USE_REASON_S1
, DO_NOT_USE_REASON_S2
, DO_NOT_USE_REASON_S3
, DO_NOT_USE_REASON_S4
, DO_NOT_USE_REASON_S5
, DO_NOT_USE_REASON_S6
FROM IEC_VAL_ENTRY_CACHE B WHERE A.LIST_ENTRY_ID = B.LIST_ENTRY_ID
)
, LAST_UPDATED_BY = :login_id
, LAST_UPDATE_DATE = SYSDATE
, LAST_UPDATE_LOGIN = :login_id
WHERE A.LIST_HEADER_ID = :list_id
AND A.LIST_ENTRY_ID IN (SELECT LIST_ENTRY_ID FROM IEC_VAL_ENTRY_CACHE)'
USING l_login_id, l_login_id, p_list_id;
Log( 'Update_ValidationReportDetails'
, 'UPDATE_EXISTING_ENTRIES'
, SQLERRM
);
'DELETE IEC_O_VALIDATION_REPORT_DETS
WHERE LIST_HEADER_ID = :list_id
AND LIST_ENTRY_ID IN (SELECT LIST_ENTRY_ID FROM IEC_VAL_ENTRY_CACHE)
AND DO_NOT_USE_REASON_S1 IS NULL
AND DO_NOT_USE_REASON_S2 IS NULL
AND DO_NOT_USE_REASON_S3 IS NULL
AND DO_NOT_USE_REASON_S4 IS NULL
AND DO_NOT_USE_REASON_S5 IS NULL
AND DO_NOT_USE_REASON_S6 IS NULL'
USING p_list_id;
Log( 'Update_ValidationReportDetails'
, 'DELETE_UNNECESSARY_ENTRIES'
, SQLERRM
);
'INSERT INTO IEC_O_VALIDATION_REPORT_DETS
( LIST_HEADER_ID
, LIST_ENTRY_ID
, DO_NOT_USE_REASON_S1
, DO_NOT_USE_REASON_S2
, DO_NOT_USE_REASON_S3
, DO_NOT_USE_REASON_S4
, DO_NOT_USE_REASON_S5
, DO_NOT_USE_REASON_S6
, CREATED_BY
, CREATION_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN )
SELECT :list_id
, LIST_ENTRY_ID
, DO_NOT_USE_REASON_S1
, DO_NOT_USE_REASON_S2
, DO_NOT_USE_REASON_S3
, DO_NOT_USE_REASON_S4
, DO_NOT_USE_REASON_S5
, DO_NOT_USE_REASON_S6
, :user_id
, sysdate
, :login_id
, sysdate
, :login_id
FROM IEC_VAL_ENTRY_CACHE
WHERE LIST_ENTRY_ID NOT IN (SELECT LIST_ENTRY_ID FROM IEC_O_VALIDATION_REPORT_DETS WHERE LIST_HEADER_ID = :list_id)
AND ( DO_NOT_USE_REASON_S1 IS NOT NULL
OR DO_NOT_USE_REASON_S2 IS NOT NULL
OR DO_NOT_USE_REASON_S3 IS NOT NULL
OR DO_NOT_USE_REASON_S4 IS NOT NULL
OR DO_NOT_USE_REASON_S5 IS NOT NULL
OR DO_NOT_USE_REASON_S6 IS NOT NULL
)'
USING p_list_id, l_user_id, l_login_id, l_login_id, p_list_id;
Log( 'Update_ValidationReportDetails'
, 'INSERTING_NEW_ENTRIES'
, SQLERRM
);
Log( 'Update_ValidationReportDetails'
, 'MAIN'
, SQLERRM
);
END Update_ValidationReportDetails;
PROCEDURE Update_CallableZones
(p_list_id IN NUMBER)
IS
l_cp_postfix SYSTEM.varchar_tbl_type := SYSTEM.varchar_tbl_type();
FOR subset_rec IN (SELECT SUBSET_ID SUBSET_ID
, CURR_CP_TERRITORY_CODE TERRITORY_CODE
, CURR_CP_REGION_ID REGION_ID
, CURR_CP_TIME_ZONE TIME_ZONE
, COUNT(*) RECORD_COUNT
FROM IEC_VAL_ENTRY_CACHE
WHERE DO_NOT_USE_FLAG = 'N'
GROUP BY SUBSET_ID
, CURR_CP_TERRITORY_CODE
, CURR_CP_REGION_ID
, CURR_CP_TIME_ZONE)
LOOP
l_tc_tz_pair_id := NULL;
'UPDATE IEC_TC_TZ_PAIRS_CACHE
SET RECORD_COUNT = NVL(RECORD_COUNT, 0) + :record_count_incr
WHERE SUBSET_ID = :subset_id
AND TERRITORY_CODE = :territory_code
AND TIMEZONE_ID = :timezone_id
AND REGION_ID IS NULL
AND CACHE_ONLY_FLAG <> ''O''
RETURNING TC_TZ_PAIR_ID INTO :tc_tz_pair_id'
USING subset_rec.RECORD_COUNT
, subset_rec.SUBSET_ID
, subset_rec.TERRITORY_CODE
, subset_rec.TIME_ZONE
, OUT l_tc_tz_pair_id;
'UPDATE IEC_TC_TZ_PAIRS_CACHE
SET RECORD_COUNT = NVL(RECORD_COUNT, 0) + :record_count_incr
WHERE SUBSET_ID = :subset_id
AND TERRITORY_CODE = :territory_code
AND TIMEZONE_ID = :timezone_id
AND REGION_ID = :region_id
AND CACHE_ONLY_FLAG <> ''O''
RETURNING TC_TZ_PAIR_ID INTO :tc_tz_pair_id'
USING subset_rec.RECORD_COUNT
, subset_rec.SUBSET_ID
, subset_rec.TERRITORY_CODE
, subset_rec.TIME_ZONE
, subset_rec.REGION_ID
, OUT l_tc_tz_pair_id;
'SELECT IEC_G_MKTG_ITEM_CC_TZS_S.NEXTVAL FROM DUAL'
INTO l_tc_tz_pair_id;
'INSERT INTO IEC_TC_TZ_PAIRS_CACHE
( SUBSET_ID
, TERRITORY_CODE
, REGION_ID
, TIMEZONE_ID
, TC_TZ_PAIR_ID
, RECORD_COUNT
, CACHE_ONLY_FLAG)
VALUES ( :subset_id
, :territory_code
, :region_id
, :timezone_id
, :tc_tz_pair_id
, :init_record_count
, ''Y'')'
USING subset_rec.SUBSET_ID
, subset_rec.TERRITORY_CODE
, subset_rec.REGION_ID
, subset_rec.TIME_ZONE
, l_tc_tz_pair_id
, subset_rec.RECORD_COUNT;
'SELECT DISTINCT SUBSET_ID SUBSET_ID
, TERRITORY_CODE' || l_cp_postfix(i) || ' TERRITORY_CODE
, REGION_ID' || l_cp_postfix(i) || ' REGION_ID
, TIME_ZONE' || l_cp_postfix(i) || ' TIME_ZONE
FROM IEC_VAL_ENTRY_CACHE
WHERE DO_NOT_USE_FLAG = ''N''
AND VALID_FLAG' || l_cp_postfix(i) || ' = ''Y''';
'SELECT TC_TZ_PAIR_ID
FROM IEC_TC_TZ_PAIRS_CACHE
WHERE SUBSET_ID = :subset_id
AND TERRITORY_CODE = :territory_code
AND TIMEZONE_ID = :timezone_id
AND REGION_ID IS NULL
AND CACHE_ONLY_FLAG <> ''O'''
INTO l_tc_tz_pair_id
USING l_subset_id
, l_territory_code
, l_timezone_id;
'SELECT TC_TZ_PAIR_ID
FROM IEC_TC_TZ_PAIRS_CACHE
WHERE SUBSET_ID = :subset_id
AND TERRITORY_CODE = :territory_code
AND TIMEZONE_ID = :timezone_id
AND REGION_ID = :region_id
AND CACHE_ONLY_FLAG <> ''O'''
INTO l_tc_tz_pair_id
USING l_subset_id
, l_territory_code
, l_timezone_id
, l_region_id;
'SELECT IEC_G_MKTG_ITEM_CC_TZS_S.NEXTVAL FROM DUAL'
INTO l_tc_tz_pair_id;
'INSERT INTO IEC_TC_TZ_PAIRS_CACHE
( SUBSET_ID
, TERRITORY_CODE
, REGION_ID
, TIMEZONE_ID
, TC_TZ_PAIR_ID
, RECORD_COUNT
, CACHE_ONLY_FLAG)
VALUES ( :subset_id
, :territory_code
, :region_id
, :timezone_id
, :tc_tz_pair_id
, :init_record_count
, ''Y'')'
USING l_subset_id
, l_territory_code
, l_region_id
, l_timezone_id
, l_tc_tz_pair_id
, 0;
'UPDATE IEC_VAL_ENTRY_CACHE A
SET A.MKTG_ITEM_CC_TZS_ID_S1 = (SELECT TC_TZ_PAIR_ID
FROM IEC_TC_TZ_PAIRS_CACHE
WHERE SUBSET_ID = A.SUBSET_ID
AND TERRITORY_CODE = A.TERRITORY_CODE_S1
AND TIMEZONE_ID = A.TIME_ZONE_S1
AND NVL(REGION_ID, -1) = NVL(A.REGION_ID_S1, -1)
AND A.VALID_FLAG_S1 = ''Y''
AND CACHE_ONLY_FLAG <> ''O'')
, A.MKTG_ITEM_CC_TZS_ID_S2 = (SELECT TC_TZ_PAIR_ID
FROM IEC_TC_TZ_PAIRS_CACHE
WHERE SUBSET_ID = A.SUBSET_ID
AND TERRITORY_CODE = A.TERRITORY_CODE_S2
AND TIMEZONE_ID = A.TIME_ZONE_S2
AND NVL(REGION_ID, -1) = NVL(A.REGION_ID_S2, -1)
AND A.VALID_FLAG_S2 = ''Y''
AND CACHE_ONLY_FLAG <> ''O'')
, A.MKTG_ITEM_CC_TZS_ID_S3 = (SELECT TC_TZ_PAIR_ID
FROM IEC_TC_TZ_PAIRS_CACHE
WHERE SUBSET_ID = A.SUBSET_ID
AND TERRITORY_CODE = A.TERRITORY_CODE_S3
AND TIMEZONE_ID = A.TIME_ZONE_S3
AND NVL(REGION_ID, -1) = NVL(A.REGION_ID_S3, -1)
AND A.VALID_FLAG_S3 = ''Y''
AND CACHE_ONLY_FLAG <> ''O'')
, A.MKTG_ITEM_CC_TZS_ID_S4 = (SELECT TC_TZ_PAIR_ID
FROM IEC_TC_TZ_PAIRS_CACHE
WHERE SUBSET_ID = A.SUBSET_ID
AND TERRITORY_CODE = A.TERRITORY_CODE_S4
AND TIMEZONE_ID = A.TIME_ZONE_S4
AND NVL(REGION_ID, -1) = NVL(A.REGION_ID_S4, -1)
AND A.VALID_FLAG_S4 = ''Y''
AND CACHE_ONLY_FLAG <> ''O'')
, A.MKTG_ITEM_CC_TZS_ID_S5 = (SELECT TC_TZ_PAIR_ID
FROM IEC_TC_TZ_PAIRS_CACHE
WHERE SUBSET_ID = A.SUBSET_ID
AND TERRITORY_CODE = A.TERRITORY_CODE_S5
AND TIMEZONE_ID = A.TIME_ZONE_S5
AND NVL(REGION_ID, -1) = NVL(A.REGION_ID_S5, -1)
AND A.VALID_FLAG_S5 = ''Y''
AND CACHE_ONLY_FLAG <> ''O'')
, A.MKTG_ITEM_CC_TZS_ID_S6 = (SELECT TC_TZ_PAIR_ID
FROM IEC_TC_TZ_PAIRS_CACHE
WHERE SUBSET_ID = A.SUBSET_ID
AND TERRITORY_CODE = A.TERRITORY_CODE_S6
AND TIMEZONE_ID = A.TIME_ZONE_S6
AND NVL(REGION_ID, -1) = NVL(A.REGION_ID_S6, -1)
AND A.VALID_FLAG_S6 = ''Y''
AND CACHE_ONLY_FLAG <> ''O'')
, A.CURR_CP_MKTG_ITEM_CC_TZS_ID = (SELECT TC_TZ_PAIR_ID
FROM IEC_TC_TZ_PAIRS_CACHE
WHERE SUBSET_ID = A.SUBSET_ID
AND TERRITORY_CODE = A.CURR_CP_TERRITORY_CODE
AND TIMEZONE_ID = A.CURR_CP_TIME_ZONE
AND NVL(REGION_ID, -1) = NVL(A.CURR_CP_REGION_ID, -1)
AND CACHE_ONLY_FLAG <> ''O'')
WHERE A.DO_NOT_USE_FLAG = ''N''';
Log( 'Update_CallableZones'
, 'MAIN'
, SQLERRM
);
END Update_CallableZones;
UPDATE IEC_G_REP_SUBSET_COUNTS
SET RECORD_LOADED = NVL(RECORD_LOADED, 0) + p_incr_amount_col(i)
WHERE SCHEDULE_ID = p_schedule_id
AND LIST_HEADER_ID = p_list_id
AND SUBSET_ID = p_subset_id_col(i);
UPDATE IEC_G_MKTG_ITEM_CC_TZS
SET RECORD_COUNT = NVL(RECORD_COUNT, 0) + p_incr_amount_col(i)
WHERE ITM_CC_TZ_ID = p_callable_zone_id_col(i);
UPDATE IEC_TC_TZ_PAIRS_CACHE
SET RECORD_COUNT = NVL(RECORD_COUNT, 0) + p_incr_amount_col(i)
WHERE TC_TZ_PAIR_ID = p_callable_zone_id_col(i);
'INSERT INTO IEC_TC_TZ_PAIRS_CACHE (SUBSET_ID, TERRITORY_CODE, REGION_ID, TIMEZONE_ID, TC_TZ_PAIR_ID, RECORD_COUNT, CACHE_ONLY_FLAG)
SELECT SUBSET_ID, TERRITORY_CODE, REGION_ID, TIMEZONE_ID, ITM_CC_TZ_ID, 0, ''N''
FROM IEC_G_MKTG_ITEM_CC_TZS
WHERE LIST_HEADER_ID = :list_id'
USING p_list_id;
'SELECT TERRITORY_CODE, REGION_ID, TIMEZONE_ID
FROM IEC_G_MKTG_ITEM_CC_TZS
WHERE ITM_CC_TZ_ID = :cc_tz_id'
INTO l_territory_code, l_region_id, l_time_zone_id
USING p_cc_tz_id;
'SELECT TERRITORY_CODE, REGION_ID, TIMEZONE_ID
FROM IEC_TC_TZ_PAIRS_CACHE
WHERE TC_TZ_PAIR_ID = :cc_tz_id'
INTO l_territory_code, l_region_id, l_time_zone_id
USING p_cc_tz_id;
PROCEDURE Delete_CallableZones
(p_list_id IN NUMBER)
IS
BEGIN
EXECUTE IMMEDIATE
'DELETE IEC_G_MKTG_ITEM_CC_TZS
WHERE LIST_HEADER_ID = :list_id'
USING p_list_id;
END Delete_CallableZones;
SELECT SUBSET_ID, TERRITORY_CODE, REGION_ID, TIMEZONE_ID, TC_TZ_PAIR_ID, RECORD_COUNT
BULK COLLECT INTO l_subset_id_col, l_territory_code_col, l_region_col, l_timezone_col, l_tc_tz_pair_id_col, l_record_count_col
FROM IEC_TC_TZ_PAIRS_CACHE
WHERE CACHE_ONLY_FLAG = 'Y';
INSERT INTO IEC_G_MKTG_ITEM_CC_TZS ( ITM_CC_TZ_ID
, SUBSET_ID
, LIST_HEADER_ID
, CAMPAIGN_SCHEDULE_ID
, TERRITORY_CODE
, REGION_ID
, TIMEZONE_ID
, LAST_CALLABLE_TIME
, CALLABLE_FLAG
, RECORD_COUNT
, OBJECT_VERSION_NUMBER
, LAST_UPDATE_DATE)
VALUES ( l_tc_tz_pair_id_col(I)
, l_subset_id_col(I)
, p_list_id
, p_campaign_schedule_id
, l_territory_code_col(I)
, l_region_col(I)
, l_timezone_col(I)
, NULL
, NULL
, l_record_count_col(I)
, 1
, SYSDATE);
SELECT TC_TZ_PAIR_ID, RECORD_COUNT
BULK COLLECT INTO l_tc_tz_pair_id_col, l_record_count_col
FROM IEC_TC_TZ_PAIRS_CACHE
WHERE CACHE_ONLY_FLAG = 'N';
UPDATE IEC_G_MKTG_ITEM_CC_TZS
SET RECORD_COUNT = RECORD_COUNT + l_record_count_col(I)
, LAST_UPDATE_DATE = SYSDATE
WHERE ITM_CC_TZ_ID = l_tc_tz_pair_id_col(I);
UPDATE IEC_TC_TZ_PAIRS_CACHE
SET CACHE_ONLY_FLAG = 'N'
WHERE CACHE_ONLY_FLAG = 'Y';
UPDATE IEC_TC_TZ_PAIRS_CACHE
SET RECORD_COUNT = 0;
PROCEDURE Update_IecReturnEntries
( p_list_id IN NUMBER
, p_campaign_schedule_id IN NUMBER
, p_campaign_id IN NUMBER
, p_source_type_view IN VARCHAR2)
IS
l_user_id NUMBER;
'UPDATE IEC_G_RETURN_ENTRIES A
SET ( A.SUBSET_ID
, A.DO_NOT_USE_FLAG
, A.DO_NOT_USE_REASON
, A.CONTACT_POINT_ID
, A.CONTACT_POINT_INDEX
, A.COUNTRY_CODE
, A.AREA_CODE
, A.PHONE_NUMBER
, A.RAW_PHONE_NUMBER
, A.TIME_ZONE
, A.ITM_CC_TZ_ID
, A.PHONE_LINE_TYPE
, A.CONTACT_POINT_PURPOSE )
=
( SELECT B.SUBSET_ID
, B.DO_NOT_USE_FLAG
, B.DO_NOT_USE_REASON
, B.CURR_CP_ID
, B.CURR_CP_INDEX
, B.CURR_CP_COUNTRY_CODE
, B.CURR_CP_AREA_CODE
, B.CURR_CP_PHONE_NUMBER
, B.CURR_CP_RAW_PHONE_NUMBER
, B.CURR_CP_TIME_ZONE
, B.CURR_CP_MKTG_ITEM_CC_TZS_ID
, B.CURR_CP_PHONE_LINE_TYPE
, B.CURR_CP_CONTACT_POINT_PURPOSE
FROM IEC_VAL_ENTRY_CACHE B
WHERE A.LIST_ENTRY_ID = B.LIST_ENTRY_ID)
, LAST_UPDATED_BY = :login_id
, LAST_UPDATE_DATE = SYSDATE
, LAST_UPDATE_LOGIN = :login_id
WHERE A.LIST_HEADER_ID = :list_id
AND A.LIST_ENTRY_ID IN (SELECT LIST_ENTRY_ID FROM IEC_VAL_ENTRY_CACHE)'
USING l_login_id, l_login_id, p_list_id;
Log( 'Update_IecReturnEntries'
, 'UPDATE_EXISTING_ENTRIES'
, SQLERRM
);
'DELETE IEC_G_RETURN_ENTRIES A
WHERE A.LIST_HEADER_ID = :list_id
AND A.LIST_ENTRY_ID IN
( SELECT LIST_ENTRY_ID
FROM IEC_VAL_ENTRY_CACHE
WHERE DO_NOT_USE_FLAG = ''Y''
AND DO_NOT_USE_REASON = ''4'')'
USING p_list_id;
Log( 'Update_IecReturnEntries'
, 'DELETE_INVALIDATED_ENTRIES'
, SQLERRM
);
'INSERT INTO IEC_G_RETURN_ENTRIES
( RETURNS_ID
, CAMPAIGN_ID
, CAMPAIGN_SCHEDULE_ID
, LIST_VIEW_NAME
, LIST_HEADER_ID
, LIST_ENTRY_ID
, SUBSET_ID
, OUTCOME_ID
, RESULT_ID
, REASON_ID
, DELIVER_IH_FLAG
, RECYCLE_FLAG
, DO_NOT_USE_FLAG
, CALLBACK_FLAG
, RECORD_OUT_FLAG
, RECORD_RELEASE_TIME
, CONTACT_POINT_ID
, CONTACT_POINT_INDEX
, COUNTRY_CODE
, AREA_CODE
, PHONE_NUMBER
, RAW_PHONE_NUMBER
, TIME_ZONE
, PHONE_LINE_TYPE
, CONTACT_POINT_PURPOSE
, ITM_CC_TZ_ID
, CREATED_BY
, CREATION_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN )
SELECT IEC_G_RETURN_ENTRIES_S.NEXTVAL
, :campaign_id
, :campaign_schedule_id
, :source_type_view
, :list_id
, LIST_ENTRY_ID
, SUBSET_ID
, -1
, -1
, -1
, ''N''
, ''N''
, ''N''
, ''N''
, ''N''
, SYSDATE
, CURR_CP_ID
, CURR_CP_INDEX
, CURR_CP_COUNTRY_CODE
, CURR_CP_AREA_CODE
, CURR_CP_PHONE_NUMBER
, CURR_CP_RAW_PHONE_NUMBER
, CURR_CP_TIME_ZONE
, CURR_CP_PHONE_LINE_TYPE
, CURR_CP_CONTACT_POINT_PURPOSE
, CURR_CP_MKTG_ITEM_CC_TZS_ID
, :user_id
, sysdate
, :login_id
, sysdate
, :login_id
FROM IEC_VAL_ENTRY_CACHE
WHERE DO_NOT_USE_FLAG = ''N''
AND LIST_ENTRY_ID NOT IN (SELECT LIST_ENTRY_ID FROM IEC_G_RETURN_ENTRIES WHERE LIST_HEADER_ID = :list_id)'
USING p_campaign_id, p_campaign_schedule_id, p_source_type_view, p_list_id, l_user_id, l_login_id, l_login_id, p_list_id;
Log( 'Update_IecReturnEntries'
, 'INSERTING_NEW_ENTRIES'
, SQLERRM
);
Log( 'Update_IecReturnEntries'
, 'MAIN'
, SQLERRM
);
END Update_IecReturnEntries;
'SELECT VALIDATED_ONCE_FLAG
FROM IEC_O_VALIDATION_STATUS
WHERE LIST_HEADER_ID = :list_id'
INTO l_validated_once
USING p_list_id;
FOR subset_rec IN (SELECT A.LIST_SUBSET_ID
, A.LOAD_PRIORITY
, NVL(B.DEFAULT_SUBSET_FLAG, 'N') SUBSET_FLAG
FROM IEC_G_SUBSET_RT_INFO A, IEC_G_LIST_SUBSETS B
WHERE A.LIST_SUBSET_ID = B.LIST_SUBSET_ID
AND B.LIST_HEADER_ID = p_list_id
AND A.STATUS_CODE <> 'DELETED'
ORDER BY NVL(B.DEFAULT_SUBSET_FLAG, 'N') ASC, A.LOAD_PRIORITY)
LOOP
l_subset_id := subset_rec.LIST_SUBSET_ID;
FOR subset_rec IN (SELECT LIST_SUBSET_ID
, LOAD_PRIORITY
, NVL(DEFAULT_SUBSET_FLAG, 'N') SUBSET_FLAG
FROM IEC_G_LIST_SUBSETS
WHERE LIST_HEADER_ID = p_list_id
AND STATUS_CODE <> 'DELETED'
ORDER BY NVL(DEFAULT_SUBSET_FLAG, 'N') ASC, LOAD_PRIORITY)
LOOP
l_subset_id := subset_rec.LIST_SUBSET_ID;
'UPDATE IEC_VAL_ENTRY_CACHE
SET SUBSET_ID = :subset_id
WHERE SUBSET_ID IS NULL
AND DO_NOT_USE_FLAG = ''N''
AND LIST_ENTRY_ID IN (SELECT LIST_ENTRY_ID FROM ' || p_subset_view_col(i) || ')'
USING p_subset_id_col(i);
'UPDATE IEC_VAL_ENTRY_CACHE
SET SUBSET_ID = :subset_id
WHERE SUBSET_ID IS NULL
AND DO_NOT_USE_FLAG = ''N'''
USING p_subset_id_col(p_subset_id_col.LAST);
'UPDATE IEC_VAL_ENTRY_CACHE
SET SUBSET_ID = :subset_id
WHERE SUBSET_ID IS NULL
AND DO_NOT_USE_FLAG = ''N'''
USING p_subset_id_col(p_subset_id_col.LAST);
'SELECT DISTINCT AREA_CODE_SIZE
FROM HZ_PHONE_FORMATS
WHERE TERRITORY_CODE = :territory_code
AND LENGTH(TRIM(TRANSLATE( UPPER(PHONE_FORMAT_STYLE)
, ''9012345678ABCDEFGHIJKLMNOPQRSTUVWXYZ-+/=()*&^%$#@!~`[]{}|\:;?><., ''
'SELECT DISTINCT A.AREA_CODE_SIZE
FROM HZ_PHONE_FORMATS A, HZ_PHONE_COUNTRY_CODES B
WHERE A.TERRITORY_CODE = B.TERRITORY_CODE AND B.PHONE_COUNTRY_CODE = :country_code
AND LENGTH(TRIM(TRANSLATE( UPPER(PHONE_FORMAT_STYLE)
, ''9012345678ABCDEFGHIJKLMNOPQRSTUVWXYZ-+/=()*&^%$#@!~`[]{}|\:;?><., ''
SELECT DISTINCT AREA_CODE BULK COLLECT INTO :area_code_col
FROM HZ_PHONE_AREA_CODES
WHERE PHONE_COUNTRY_CODE = :country_code
AND AREA_CODE LIKE CONCAT(SUBSTR(:phone_number, 1, 1), ''%'')
ORDER BY AREA_CODE;
'SELECT REGION_ID
FROM IEC_G_REGION_MAPPINGS
WHERE TERRITORY_CODE = :territory_code AND PHONE_AREA_CODE = :area_code'
INTO x_region
USING x_territory_code, p_area_code;
'SELECT DISTINCT TIMEZONE_ID
FROM IEC_TZ_MAPPING_CACHE
WHERE TERRITORY_CODE = :territory_code
AND (AREA_CODE = :area_code OR AREA_CODE IS NULL)'
INTO l_timezone_id
USING p_territory_code, p_area_code;
'SELECT DISTINCT TIMEZONE_ID
FROM IEC_TZ_MAPPING_CACHE
WHERE TERRITORY_CODE = :territory_code
AND AREA_CODE = :area_code
AND POSTAL_CODE = :postal_code'
INTO l_timezone_id
USING p_territory_code, p_area_code, p_postal_code;
'SELECT TIMEZONE_ID
FROM IEC_TZ_OFFSET_MAP_CACHE
WHERE OFFSET = :timezone
AND ROWNUM = 1'
INTO l_timezone_id
USING l_timezone;
'SELECT TIMEZONE_ID
FROM IEC_TZ_OFFSET_MAP_CACHE
WHERE UPPER(OFFSET) = :timezone
AND ROWNUM = 1'
INTO l_timezone_id
USING l_timezone;
'SELECT UPGRADE_TZ_ID
FROM FND_TIMEZONES_VL
WHERE UPPER(NAME) = :timezone_name
AND ROWNUM = 1'
INTO l_timezone_id
USING l_timezone;
DELETE IEC_G_RETURN_ENTRIES A
WHERE A.LIST_HEADER_ID = p_list_id
AND A.RETURNS_ID IN (SELECT * FROM TABLE(CAST(p_returns_id_col AS SYSTEM.NUMBER_TBL_TYPE)))
RETURNING A.RETURNS_ID, A.SUBSET_ID, A.LIST_ENTRY_ID, A.ITM_CC_TZ_ID, A.DO_NOT_USE_FLAG
BULK COLLECT INTO l_returns_id_col, l_subset_id_col, l_list_entry_id_col, l_callable_zone_id_col, l_do_not_use_col;
DELETE IEC_O_RCY_CALL_HISTORIES
WHERE RETURNS_ID = :returns_id(i);
DELETE IEC_O_VALIDATION_REPORT_DETS
WHERE LIST_HEADER_ID = :list_id
AND LIST_ENTRY_ID = :list_entry_id(i);
PROCEDURE Remove_DeletedRecords
( p_list_id IN NUMBER
, p_schedule_id IN NUMBER
)
IS
l_returns_id_col SYSTEM.number_tbl_type;
SELECT A.RETURNS_ID
BULK COLLECT INTO :returns_id_col
FROM IEC_G_RETURN_ENTRIES A
WHERE A.LIST_HEADER_ID = :list_id
AND NOT EXISTS (SELECT LIST_ENTRY_ID
FROM AMS_LIST_ENTRIES
WHERE A.LIST_HEADER_ID = LIST_HEADER_ID
AND A.LIST_ENTRY_ID = LIST_ENTRY_ID
AND ENABLED_FLAG = ''Y'');
'DELETE IEC_O_VALIDATION_REPORT_DETS
WHERE LIST_HEADER_ID = :list_id
AND DO_NOT_USE_REASON_S1 IS NOT NULL
AND DO_NOT_USE_REASON_S2 IS NOT NULL
AND DO_NOT_USE_REASON_S3 IS NOT NULL
AND DO_NOT_USE_REASON_S4 IS NOT NULL
AND DO_NOT_USE_REASON_S5 IS NOT NULL
AND DO_NOT_USE_REASON_S6 IS NOT NULL
AND LIST_ENTRY_ID NOT IN
(SELECT LIST_ENTRY_ID
FROM AMS_LIST_ENTRIES
WHERE LIST_HEADER_ID = :list_id
AND ENABLED_FLAG = ''Y'')'
USING IN p_list_id
, IN p_list_id;
Log( 'Remove_DeletedRecords'
, 'MAIN'
, SQLERRM
);
END Remove_DeletedRecords;
'SELECT A.STATUS_CODE
FROM IEC_G_AO_LISTS_V A
WHERE A.LIST_HEADER_ID = :list_id
AND LANGUAGE = USERENV(''LANG'')'
INTO l_status_code
USING l_list_id;
Update_PurgeStatus(l_list_id, p_schedule_id, 'PURGING', l_api_initiated);
SELECT A.RETURNS_ID, A.LIST_ENTRY_ID
BULK COLLECT INTO :returns_id_col, :list_entry_id_col
FROM IEC_G_RETURN_ENTRIES A
WHERE A.LIST_HEADER_ID = :list_id
AND A.DO_NOT_USE_FLAG = ''Y'';
UPDATE ' || Get_SourceTypeView(l_list_id) || '
SET REASON_CODE_S1 = NULL
, REASON_CODE_S2 = NULL
, REASON_CODE_S3 = NULL
, REASON_CODE_S4 = NULL
, REASON_CODE_S5 = NULL
, REASON_CODE_S6 = NULL
WHERE LIST_HEADER_ID = :list_id
AND LIST_ENTRY_ID IN (SELECT * FROM TABLE(CAST(:list_entry_id_col AS SYSTEM.NUMBER_TBL_TYPE)));
Update_PurgeStatus(l_list_id, p_schedule_id, l_status_code, l_api_initiated);
Update_PurgeStatus(l_list_id, p_schedule_id, l_status_code, l_api_initiated);
Update_PurgeStatus(l_list_id, p_schedule_id, l_status_code, l_api_initiated);
Update_PurgeStatus(l_list_id, p_schedule_id, l_status_code, l_api_initiated);
l_newly_updated_flag VARCHAR2(1);
l_callable_zone_id_col.DELETE;
l_callable_zone_incr_col.DELETE;
l_subset_rec_loaded_id_col.DELETE;
l_subset_rec_loaded_incr_col.DELETE;
FETCH p_list_entry_csr INTO l_list_entry_id, l_postal_code, l_do_not_use_flag, l_do_not_use_reason, l_newly_updated_flag, l_prev_subset_id, l_prev_callable_zone_id, l_curr_contact_point_idx,
l_contact_points(1).id, l_contact_points(1).time_zone, l_contact_points(1).phone_country_code, l_contact_points(1).phone_area_code, l_contact_points(1).phone_number,
l_contact_points(1).raw_phone_number, l_contact_points(1).cc_tz_id, l_contact_points(1).phone_line_type, l_contact_points(1).purpose,
l_contact_points(2).id, l_contact_points(2).time_zone, l_contact_points(2).phone_country_code, l_contact_points(2).phone_area_code, l_contact_points(2).phone_number,
l_contact_points(2).raw_phone_number, l_contact_points(2).cc_tz_id, l_contact_points(2).phone_line_type, l_contact_points(2).purpose,
l_contact_points(3).id, l_contact_points(3).time_zone, l_contact_points(3).phone_country_code, l_contact_points(3).phone_area_code, l_contact_points(3).phone_number,
l_contact_points(3).raw_phone_number, l_contact_points(3).cc_tz_id, l_contact_points(3).phone_line_type, l_contact_points(3).purpose,
l_contact_points(4).id, l_contact_points(4).time_zone, l_contact_points(4).phone_country_code, l_contact_points(4).phone_area_code, l_contact_points(4).phone_number,
l_contact_points(4).raw_phone_number, l_contact_points(4).cc_tz_id, l_contact_points(4).phone_line_type, l_contact_points(4).purpose,
l_contact_points(5).id, l_contact_points(5).time_zone, l_contact_points(5).phone_country_code, l_contact_points(5).phone_area_code, l_contact_points(5).phone_number,
l_contact_points(5).raw_phone_number, l_contact_points(5).cc_tz_id, l_contact_points(5).phone_line_type, l_contact_points(5).purpose,
l_contact_points(6).id, l_contact_points(6).time_zone, l_contact_points(6).phone_country_code, l_contact_points(6).phone_area_code, l_contact_points(6).phone_number,
l_contact_points(6).raw_phone_number, l_contact_points(6).cc_tz_id, l_contact_points(6).phone_line_type, l_contact_points(6).purpose;
IF l_newly_updated_flag = 'Y' THEN
FOR i IN 1..G_NUM_CONTACT_POINTS LOOP
l_contact_points(i).cc_tz_id := NULL;
INSERT INTO IEC_VAL_ENTRY_CACHE
( LIST_ENTRY_ID
, POSTAL_CODE
, DO_NOT_USE_FLAG
, DO_NOT_USE_REASON
, PREV_STATUS_CODE
, CURR_CP_INDEX
, CURR_CP_ID
, CURR_CP_TIME_ZONE
, CURR_CP_COUNTRY_CODE
, CURR_CP_AREA_CODE
, CURR_CP_PHONE_NUMBER
, CURR_CP_RAW_PHONE_NUMBER
, CURR_CP_TERRITORY_CODE
, CURR_CP_REGION_ID
, CURR_CP_MKTG_ITEM_CC_TZS_ID
, CURR_CP_PHONE_LINE_TYPE
, CURR_CP_CONTACT_POINT_PURPOSE
, CONTACT_POINT_ID_S1
, TIME_ZONE_S1
, PHONE_COUNTRY_CODE_S1
, PHONE_AREA_CODE_S1
, PHONE_NUMBER_S1
, RAW_PHONE_NUMBER_S1
, TERRITORY_CODE_S1
, REGION_ID_S1
, MKTG_ITEM_CC_TZS_ID_S1
, VALID_FLAG_S1
, DO_NOT_USE_REASON_S1
, PHONE_LINE_TYPE_S1
, CONTACT_POINT_PURPOSE_S1
, CONTACT_POINT_ID_S2
, TIME_ZONE_S2
, PHONE_COUNTRY_CODE_S2
, PHONE_AREA_CODE_S2
, PHONE_NUMBER_S2
, RAW_PHONE_NUMBER_S2
, TERRITORY_CODE_S2
, REGION_ID_S2
, MKTG_ITEM_CC_TZS_ID_S2
, VALID_FLAG_S2
, DO_NOT_USE_REASON_S2
, PHONE_LINE_TYPE_S2
, CONTACT_POINT_PURPOSE_S2
, CONTACT_POINT_ID_S3
, TIME_ZONE_S3
, PHONE_COUNTRY_CODE_S3
, PHONE_AREA_CODE_S3
, PHONE_NUMBER_S3
, RAW_PHONE_NUMBER_S3
, TERRITORY_CODE_S3
, REGION_ID_S3
, MKTG_ITEM_CC_TZS_ID_S3
, VALID_FLAG_S3
, DO_NOT_USE_REASON_S3
, PHONE_LINE_TYPE_S3
, CONTACT_POINT_PURPOSE_S3
, CONTACT_POINT_ID_S4
, TIME_ZONE_S4
, PHONE_COUNTRY_CODE_S4
, PHONE_AREA_CODE_S4
, PHONE_NUMBER_S4
, RAW_PHONE_NUMBER_S4
, TERRITORY_CODE_S4
, REGION_ID_S4
, MKTG_ITEM_CC_TZS_ID_S4
, VALID_FLAG_S4
, DO_NOT_USE_REASON_S4
, PHONE_LINE_TYPE_S4
, CONTACT_POINT_PURPOSE_S4
, CONTACT_POINT_ID_S5
, TIME_ZONE_S5
, PHONE_COUNTRY_CODE_S5
, PHONE_AREA_CODE_S5
, PHONE_NUMBER_S5
, RAW_PHONE_NUMBER_S5
, TERRITORY_CODE_S5
, REGION_ID_S5
, MKTG_ITEM_CC_TZS_ID_S5
, VALID_FLAG_S5
, DO_NOT_USE_REASON_S5
, PHONE_LINE_TYPE_S5
, CONTACT_POINT_PURPOSE_S5
, CONTACT_POINT_ID_S6
, TIME_ZONE_S6
, PHONE_COUNTRY_CODE_S6
, PHONE_AREA_CODE_S6
, PHONE_NUMBER_S6
, RAW_PHONE_NUMBER_S6
, TERRITORY_CODE_S6
, REGION_ID_S6
, MKTG_ITEM_CC_TZS_ID_S6
, VALID_FLAG_S6
, DO_NOT_USE_REASON_S6
, PHONE_LINE_TYPE_S6
, CONTACT_POINT_PURPOSE_S6
)
VALUES
( l_list_entry_id
, l_postal_code
, l_do_not_use_flag
, l_do_not_use_reason
, l_prev_record_status
, l_curr_contact_point_idx
, l_curr_contact_point.id
, l_curr_contact_point.time_zone
, l_curr_contact_point.phone_country_code
, l_curr_contact_point.phone_area_code
, l_curr_contact_point.phone_number
, l_curr_contact_point.raw_phone_number
, l_curr_contact_point.territory_code
, l_curr_contact_point.region_id
, l_curr_contact_point.cc_tz_id
, l_curr_contact_point.phone_line_type
, l_curr_contact_point.purpose
, l_contact_points(1).id
, l_contact_points(1).time_zone
, l_contact_points(1).phone_country_code
, l_contact_points(1).phone_area_code
, l_contact_points(1).phone_number
, l_contact_points(1).raw_phone_number
, l_contact_points(1).territory_code
, l_contact_points(1).region_id
, l_contact_points(1).cc_tz_id
, l_contact_points(1).valid_flag
, l_contact_points(1).dnu_reason
, l_contact_points(1).phone_line_type
, l_contact_points(1).purpose
, l_contact_points(2).id
, l_contact_points(2).time_zone
, l_contact_points(2).phone_country_code
, l_contact_points(2).phone_area_code
, l_contact_points(2).phone_number
, l_contact_points(2).raw_phone_number
, l_contact_points(2).territory_code
, l_contact_points(2).region_id
, l_contact_points(2).cc_tz_id
, l_contact_points(2).valid_flag
, l_contact_points(2).dnu_reason
, l_contact_points(2).phone_line_type
, l_contact_points(2).purpose
, l_contact_points(3).id
, l_contact_points(3).time_zone
, l_contact_points(3).phone_country_code
, l_contact_points(3).phone_area_code
, l_contact_points(3).phone_number
, l_contact_points(3).raw_phone_number
, l_contact_points(3).territory_code
, l_contact_points(3).region_id
, l_contact_points(3).cc_tz_id
, l_contact_points(3).valid_flag
, l_contact_points(3).dnu_reason
, l_contact_points(3).phone_line_type
, l_contact_points(3).purpose
, l_contact_points(4).id
, l_contact_points(4).time_zone
, l_contact_points(4).phone_country_code
, l_contact_points(4).phone_area_code
, l_contact_points(4).phone_number
, l_contact_points(4).raw_phone_number
, l_contact_points(4).territory_code
, l_contact_points(4).region_id
, l_contact_points(4).cc_tz_id
, l_contact_points(4).valid_flag
, l_contact_points(4).dnu_reason
, l_contact_points(4).phone_line_type
, l_contact_points(4).purpose
, l_contact_points(5).id
, l_contact_points(5).time_zone
, l_contact_points(5).phone_country_code
, l_contact_points(5).phone_area_code
, l_contact_points(5).phone_number
, l_contact_points(5).raw_phone_number
, l_contact_points(5).territory_code
, l_contact_points(5).region_id
, l_contact_points(5).cc_tz_id
, l_contact_points(5).valid_flag
, l_contact_points(5).dnu_reason
, l_contact_points(5).phone_line_type
, l_contact_points(5).purpose
, l_contact_points(6).id
, l_contact_points(6).time_zone
, l_contact_points(6).phone_country_code
, l_contact_points(6).phone_area_code
, l_contact_points(6).phone_number
, l_contact_points(6).raw_phone_number
, l_contact_points(6).territory_code
, l_contact_points(6).region_id
, l_contact_points(6).cc_tz_id
, l_contact_points(6).valid_flag
, l_contact_points(6).dnu_reason
, l_contact_points(6).phone_line_type
, l_contact_points(6).purpose
);
'SELECT COUNT(*)
FROM IEC_VAL_ENTRY_CACHE'
INTO l_entry_count;
Update_CallableZones(p_list_id);
Update_AmsListEntries(p_list_id, p_source_type_view);
Update_ValidationReportDetails(p_list_id);
Update_IecReturnEntries(p_list_id, p_campaign_schedule_id, p_campaign_id, p_source_type_view);
Update_ReportCounts( p_campaign_id
, p_campaign_schedule_id
, p_list_id
, l_subset_id_col
, l_subset_rec_loaded_col);
'SELECT B.CAMPAIGN_ID
FROM AMS_ACT_LISTS A, AMS_CAMPAIGN_SCHEDULES_B B
WHERE A.LIST_HEADER_ID = :list_id
AND A.LIST_USED_BY = ''CSCH''
AND A.LIST_ACT_TYPE = ''TARGET''
AND A.LIST_USED_BY_ID = B.SCHEDULE_ID'
INTO l_campaign_id
USING p_list_id;
'SELECT B.SOURCE_CODE
FROM AMS_ACT_LISTS A, AMS_CAMPAIGN_SCHEDULES_B B
WHERE A.LIST_USED_BY_ID = B.SCHEDULE_ID
AND A.LIST_HEADER_ID = :list_id'
INTO l_source_code
USING p_list_id;
SELECT COLUMN_NAME
BULK COLLECT INTO l_columns
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME = p_source_type_view
AND OWNER = l_table_owner;
l_curr := 'NEWLY_UPDATED_FLAG';
SELECT LIST_ENTRY_SOURCE_SYSTEM_TYPE, COUNT(*)
BULK COLLECT INTO l_source_type_col, l_source_type_count_col
FROM AMS_LIST_ENTRIES
WHERE LIST_HEADER_ID = p_list_id
GROUP BY LIST_ENTRY_SOURCE_SYSTEM_TYPE;
SELECT LIST_SOURCE_TYPE
INTO l_list_source_type
FROM AMS_LIST_HEADERS_ALL
WHERE LIST_HEADER_ID = p_list_id;
PROCEDURE Insert_ValidationHistoryRec
( p_list_id IN NUMBER
)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
INSERT INTO IEC_O_VALIDATION_HISTORY
( VALIDATION_HISTORY_ID
, LIST_HEADER_ID
, STATUS
, START_TIME
, END_TIME
, DESCRIPTION
, CREATED_BY
, CREATION_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
, OBJECT_VERSION_NUMBER
)
VALUES
( IEC_O_VALIDATION_HISTORY_S.NEXTVAL
, p_list_id
, g_status
, g_start_time
, g_end_time
, g_encoded_message
, l_user_id
, SYSDATE
, l_user_id
, SYSDATE
, l_login_id
, 1
);
Log( 'Insert_ValidationHistoryRec'
, 'MAIN'
, SQLERRM);
END Insert_ValidationHistoryRec;
Update_Status(p_list_id, 'VALIDATING');
'SELECT A.LIST_ENTRY_ID, A.POSTAL_CODE, A.DO_NOT_USE_FLAG, A.DO_NOT_USE_REASON, A.NEWLY_UPDATED_FLAG, B.SUBSET_ID, B.ITM_CC_TZ_ID, B.CONTACT_POINT_INDEX,
A.CONTACT_POINT_ID_S1, A.TIME_ZONE_S1, A.PHONE_COUNTRY_CODE_S1, A.PHONE_AREA_CODE_S1, A.PHONE_NUMBER_S1, A.RAW_PHONE_NUMBER_S1, A.REASON_CODE_S1, A.PHONE_LINE_TYPE_S1, NULL,
A.CONTACT_POINT_ID_S2, A.TIME_ZONE_S2, A.PHONE_COUNTRY_CODE_S2, A.PHONE_AREA_CODE_S2, A.PHONE_NUMBER_S2, A.RAW_PHONE_NUMBER_S2, A.REASON_CODE_S2, A.PHONE_LINE_TYPE_S2, NULL,
A.CONTACT_POINT_ID_S3, A.TIME_ZONE_S3, A.PHONE_COUNTRY_CODE_S3, A.PHONE_AREA_CODE_S3, A.PHONE_NUMBER_S3, A.RAW_PHONE_NUMBER_S3, A.REASON_CODE_S3, A.PHONE_LINE_TYPE_S3, NULL,
A.CONTACT_POINT_ID_S4, A.TIME_ZONE_S4, A.PHONE_COUNTRY_CODE_S4, A.PHONE_AREA_CODE_S4, A.PHONE_NUMBER_S4, A.RAW_PHONE_NUMBER_S4, A.REASON_CODE_S4, A.PHONE_LINE_TYPE_S4, NULL,
A.CONTACT_POINT_ID_S5, A.TIME_ZONE_S5, A.PHONE_COUNTRY_CODE_S5, A.PHONE_AREA_CODE_S5, A.PHONE_NUMBER_S5, A.RAW_PHONE_NUMBER_S5, A.REASON_CODE_S5, A.PHONE_LINE_TYPE_S5, NULL,
A.CONTACT_POINT_ID_S6, A.TIME_ZONE_S6, A.PHONE_COUNTRY_CODE_S6, A.PHONE_AREA_CODE_S6, A.PHONE_NUMBER_S6, A.RAW_PHONE_NUMBER_S6, A.REASON_CODE_S6, A.PHONE_LINE_TYPE_S6, NULL
FROM ' || l_source_type_view || ' A, IEC_G_RETURN_ENTRIES B
WHERE A.LIST_HEADER_ID = B.LIST_HEADER_ID(+)
AND A.LIST_ENTRY_ID = B.LIST_ENTRY_ID(+)
AND A.LIST_HEADER_ID = :list_id
AND A.ENABLED_FLAG = ''Y''
AND (((
((A.PHONE_NUMBER_S1 IS NOT NULL OR A.RAW_PHONE_NUMBER_S1 IS NOT NULL) AND A.REASON_CODE_S1 IS NULL)
OR
((A.PHONE_NUMBER_S2 IS NOT NULL OR A.RAW_PHONE_NUMBER_S2 IS NOT NULL) AND A.REASON_CODE_S2 IS NULL)
OR
((A.PHONE_NUMBER_S3 IS NOT NULL OR A.RAW_PHONE_NUMBER_S3 IS NOT NULL) AND A.REASON_CODE_S3 IS NULL)
OR
((A.PHONE_NUMBER_S4 IS NOT NULL OR A.RAW_PHONE_NUMBER_S4 IS NOT NULL) AND A.REASON_CODE_S4 IS NULL)
OR
((A.PHONE_NUMBER_S5 IS NOT NULL OR A.RAW_PHONE_NUMBER_S5 IS NOT NULL) AND A.REASON_CODE_S5 IS NULL)
OR
((A.PHONE_NUMBER_S6 IS NOT NULL OR A.RAW_PHONE_NUMBER_S6 IS NOT NULL) AND A.REASON_CODE_S6 IS NULL)
)
AND (A.DO_NOT_USE_FLAG = ''N'' OR A.DO_NOT_USE_FLAG IS NULL OR A.DO_NOT_USE_REASON = 4)
)
OR A.NEWLY_UPDATED_FLAG = ''Y''
)'
USING p_list_id;
Log_MissingSourceTypeColumns(p_list_id, l_source_type_view, Get_SourceType(p_list_id), 'VALIDATE_LIST', 'SELECT_ENTRIES_FOR_VALIDATION');
Remove_DeletedRecords(p_list_id, l_campaign_schedule_id);
Update_Status(p_list_id, 'VALIDATED');
/* SELECT (RECORD_VALID_ALL_CPS + RECORD_VALID_SOME_CPS)
, (RECORD_VALID_ALL_CPS + RECORD_VALID_SOME_CPS + RECORD_INVALID)
INTO l_valid_count
, l_total_count
FROM IEC_G_REP_LIST_DETAILS_V
WHERE LIST_HEADER_ID = p_list_id; */
SELECT COUNT(*) RECORDS_INVALID
into l_record_invalid
FROM IEC_O_VALIDATION_REPORT_DETS
WHERE ( DO_NOT_USE_REASON_S1 IS NOT NULL AND
DO_NOT_USE_REASON_S2 IS NOT NULL AND
DO_NOT_USE_REASON_S3 IS NOT NULL AND
DO_NOT_USE_REASON_S4 IS NOT NULL AND
DO_NOT_USE_REASON_S5 IS NOT NULL AND
DO_NOT_USE_REASON_S6 IS NOT NULL ) AND
LIST_HEADER_ID = p_list_id;
SELECT SUM(NVL(A.RECORD_LOADED, 0))
into l_valid_count
FROM IEC_G_REP_SUBSET_COUNTS A,
IEC_G_LIST_SUBSETS D
where A.SUBSET_ID = D.LIST_SUBSET_ID AND
D.LIST_HEADER_ID = p_list_id AND D.STATUS_CODE <> 'DELETED';
Insert_ValidationHistoryRec(p_list_id);
Update_Status(p_list_id, 'FAILED_VALIDATION');
Insert_ValidationHistoryRec(p_list_id);
Update_Status(p_list_id, 'FAILED_VALIDATION');
Insert_ValidationHistoryRec(p_list_id);
Update_Status(p_list_id, 'FAILED_VALIDATION');
Insert_ValidationHistoryRec(p_list_id);
SELECT SYSDATE, LAST_UPDATED_BY, SYSDATE, CREATED_BY, LAST_UPDATE_LOGIN, OBJECT_VERSION_NUMBER
, LIST_SELECT_ACTION_ID, ARC_LIST_SELECT_ACTION_FROM, LIST_SELECT_ACTION_FROM_NAME, SOURCE_CODE, ARC_LIST_USED_BY_SOURCE, SOURCE_CODE_FOR_ID
, PIN_CODE, LIST_ENTRY_SOURCE_SYSTEM_ID, LIST_ENTRY_SOURCE_SYSTEM_TYPE, VIEW_APPLICATION_ID, MANUALLY_ENTERED_FLAG
, MARKED_AS_DUPLICATE_FLAG, MARKED_AS_RANDOM_FLAG, PART_OF_CONTROL_GROUP_FLAG, EXCLUDE_IN_TRIGGERED_LIST_FLAG, ENABLED_FLAG, CELL_CODE, DEDUPE_KEY
, RANDOMLY_GENERATED_NUMBER, CAMPAIGN_ID, MEDIA_ID, CHANNEL_ID, CHANNEL_SCHEDULE_ID, EVENT_OFFER_ID, CUSTOMER_ID, MARKET_SEGMENT_ID
, PARTY_ID, PARENT_PARTY_ID, VENDOR_ID, TRANSFER_FLAG, TRANSFER_STATUS, LIST_SOURCE, DUPLICATE_MASTER_ENTRY_ID, MARKED_FLAG, LEAD_ID, LETTER_ID
, PICKING_HEADER_ID, BATCH_ID, SUFFIX, FIRST_NAME, LAST_NAME, CUSTOMER_NAME, TITLE, ADDRESS_LINE1, ADDRESS_LINE2, CITY, STATE, ZIPCODE, COUNTRY, FAX, PHONE, EMAIL_ADDRESS
, COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8, COL9, COL10, COL11, COL12, COL13, COL14, COL15, COL16, COL17, COL18
, COL19, COL20, COL21, COL22, COL23, COL24, COL25, COL26, COL27, COL28, COL29, COL30, COL31, COL32, COL33, COL34, COL35
, COL36, COL37, COL38, COL39, COL40, COL41, COL42, COL43, COL44, COL45, COL46, COL47, COL48, COL49, COL50, COL51, COL52
, COL53, COL54, COL55, COL56, COL57, COL58, COL59, COL60, COL61, COL62, COL63, COL64, COL65, COL66, COL67, COL68, COL69
, COL70, COL71, COL72, COL73, COL74, COL75, COL76, COL77, COL78, COL79, COL80, COL81, COL82, COL83, COL84, COL85, COL86
, COL87, COL88, COL89, COL90, COL91, COL92, COL93, COL94, COL95, COL96, COL97, COL98, COL99, COL100, COL101, COL102, COL103
, COL104, COL105, COL106, COL107, COL108, COL109, COL110, COL111, COL112, COL113, COL114, COL115, COL116, COL117, COL118, COL119
, COL120, COL121, COL122, COL123, COL124, COL125, COL126, COL127, COL128, COL129, COL130, COL131, COL132, COL133, COL134, COL135
, COL136, COL137, COL138, COL139, COL140, COL141, COL142, COL143, COL144, COL145, COL146, COL147, COL148, COL149, COL150, COL151
, COL152, COL153, COL154, COL155, COL156, COL157, COL158, COL159, COL160, COL161, COL162, COL163, COL164, COL165, COL166, COL167
, COL168, COL169, COL170, COL171, COL172, COL173, COL174, COL175, COL176, COL177, COL178, COL179, COL180, COL181, COL182, COL183
, COL184, COL185, COL186, COL187, COL188, COL189, COL190, COL191, COL192, COL193, COL194, COL195, COL196, COL197, COL198, COL199
, COL200, COL201, COL202, COL203, COL204, COL205, COL206, COL207, COL208, COL209, COL210, COL211, COL212, COL213, COL214, COL215
, COL216, COL217, COL218, COL219, COL220, COL221, COL222, COL223, COL224, COL225, COL226, COL227, COL228, COL229, COL230, COL231
, COL232, COL233, COL234, COL235, COL236, COL237, COL238, COL239, COL240, COL241, COL242, COL243, COL244, COL245, COL246, COL247
, COL248, COL249, COL250, IMP_SOURCE_LINE_ID, USAGE_RESTRICTION, COL251, COL252, COL253, COL254, COL255, COL256, COL257, COL258
, COL259, COL260, COL261, COL262, COL263, COL264, COL265, COL266, COL267, COL268, COL269, COL270, COL271, COL272, COL273, COL274
, COL275, COL276, COL277, COL278, COL279, COL280, COL281, COL282, COL283, COL284, COL285, COL286, COL287, COL288, COL289, COL290
, COL291, COL292, COL293, COL294, COL295, COL296, COL297, COL298, COL299, COL300 INTO
l_listentry_rec.LAST_UPDATE_DATE, l_listentry_rec.LAST_UPDATED_BY, l_listentry_rec.CREATION_DATE, l_listentry_rec.CREATED_BY, l_listentry_rec.LAST_UPDATE_LOGIN
, l_listentry_rec.OBJECT_VERSION_NUMBER, l_listentry_rec.LIST_SELECT_ACTION_ID, l_listentry_rec.ARC_LIST_SELECT_ACTION_FROM, l_listentry_rec.LIST_SELECT_ACTION_FROM_NAME
, l_listentry_rec.SOURCE_CODE, l_listentry_rec.ARC_LIST_USED_BY_SOURCE, l_listentry_rec.SOURCE_CODE_FOR_ID, l_listentry_rec.PIN_CODE
, l_listentry_rec.LIST_ENTRY_SOURCE_SYSTEM_ID, l_listentry_rec.LIST_ENTRY_SOURCE_SYSTEM_TYPE, l_listentry_rec.VIEW_APPLICATION_ID, l_listentry_rec.MANUALLY_ENTERED_FLAG
, l_listentry_rec.MARKED_AS_DUPLICATE_FLAG, l_listentry_rec.MARKED_AS_RANDOM_FLAG, l_listentry_rec.PART_OF_CONTROL_GROUP_FLAG,l_listentry_rec.EXCLUDE_IN_TRIGGERED_LIST_FLAG
, l_listentry_rec.ENABLED_FLAG, l_listentry_rec.CELL_CODE, l_listentry_rec.DEDUPE_KEY, l_listentry_rec.RANDOMLY_GENERATED_NUMBER, l_listentry_rec.CAMPAIGN_ID
, l_listentry_rec.MEDIA_ID, l_listentry_rec.CHANNEL_ID, l_listentry_rec.CHANNEL_SCHEDULE_ID, l_listentry_rec.EVENT_OFFER_ID, l_listentry_rec.CUSTOMER_ID
, l_listentry_rec.MARKET_SEGMENT_ID, l_listentry_rec.PARTY_ID, l_listentry_rec.PARENT_PARTY_ID, l_listentry_rec.VENDOR_ID, l_listentry_rec.TRANSFER_FLAG
, l_listentry_rec.TRANSFER_STATUS, l_listentry_rec.LIST_SOURCE, l_listentry_rec.DUPLICATE_MASTER_ENTRY_ID, l_listentry_rec.MARKED_FLAG, l_listentry_rec.LEAD_ID
, l_listentry_rec.LETTER_ID, l_listentry_rec.PICKING_HEADER_ID, l_listentry_rec.BATCH_ID, l_listentry_rec.SUFFIX, l_listentry_rec.FIRST_NAME, l_listentry_rec.LAST_NAME
, l_listentry_rec.CUSTOMER_NAME, l_listentry_rec.TITLE, l_listentry_rec.ADDRESS_LINE1, l_listentry_rec.ADDRESS_LINE2, l_listentry_rec.CITY, l_listentry_rec.STATE
, l_listentry_rec.ZIPCODE, l_listentry_rec.COUNTRY, l_listentry_rec.FAX, l_listentry_rec.PHONE, l_listentry_rec.EMAIL_ADDRESS
, l_listentry_rec.COL1, l_listentry_rec.COL2, l_listentry_rec.COL3, l_listentry_rec.COL4, l_listentry_rec.COL5, l_listentry_rec.COL6, l_listentry_rec.COL7
, l_listentry_rec.COL8,l_listentry_rec.COL9, l_listentry_rec.COL10, l_listentry_rec.COL11, l_listentry_rec.COL12, l_listentry_rec.COL13, l_listentry_rec.COL14
, l_listentry_rec.COL15, l_listentry_rec.COL16, l_listentry_rec.COL17, l_listentry_rec.COL18, l_listentry_rec.COL19, l_listentry_rec.COL20, l_listentry_rec.COL21
, l_listentry_rec.COL22, l_listentry_rec.COL23, l_listentry_rec.COL24, l_listentry_rec.COL25, l_listentry_rec.COL26, l_listentry_rec.COL27, l_listentry_rec.COL28
, l_listentry_rec.COL29, l_listentry_rec.COL30, l_listentry_rec.COL31, l_listentry_rec.COL32, l_listentry_rec.COL33, l_listentry_rec.COL34, l_listentry_rec.COL35
, l_listentry_rec.COL36, l_listentry_rec.COL37, l_listentry_rec.COL38, l_listentry_rec.COL39, l_listentry_rec.COL40, l_listentry_rec.COL41, l_listentry_rec.COL42
, l_listentry_rec.COL43, l_listentry_rec.COL44, l_listentry_rec.COL45, l_listentry_rec.COL46, l_listentry_rec.COL47, l_listentry_rec.COL48, l_listentry_rec.COL49
, l_listentry_rec.COL50, l_listentry_rec.COL51, l_listentry_rec.COL52, l_listentry_rec.COL53, l_listentry_rec.COL54, l_listentry_rec.COL55, l_listentry_rec.COL56
, l_listentry_rec.COL57, l_listentry_rec.COL58, l_listentry_rec.COL59, l_listentry_rec.COL60, l_listentry_rec.COL61, l_listentry_rec.COL62, l_listentry_rec.COL63
, l_listentry_rec.COL64, l_listentry_rec.COL65, l_listentry_rec.COL66, l_listentry_rec.COL67, l_listentry_rec.COL68, l_listentry_rec.COL69, l_listentry_rec.COL70
, l_listentry_rec.COL71, l_listentry_rec.COL72, l_listentry_rec.COL73, l_listentry_rec.COL74, l_listentry_rec.COL75, l_listentry_rec.COL76, l_listentry_rec.COL77
, l_listentry_rec.COL78, l_listentry_rec.COL79, l_listentry_rec.COL80, l_listentry_rec.COL81, l_listentry_rec.COL82, l_listentry_rec.COL83, l_listentry_rec.COL84
, l_listentry_rec.COL85, l_listentry_rec.COL86, l_listentry_rec.COL87, l_listentry_rec.COL88, l_listentry_rec.COL89, l_listentry_rec.COL90, l_listentry_rec.COL91
, l_listentry_rec.COL92, l_listentry_rec.COL93, l_listentry_rec.COL94, l_listentry_rec.COL95, l_listentry_rec.COL96, l_listentry_rec.COL97, l_listentry_rec.COL98
, l_listentry_rec.COL99, l_listentry_rec.COL100, l_listentry_rec.COL101, l_listentry_rec.COL102, l_listentry_rec.COL103, l_listentry_rec.COL104, l_listentry_rec.COL105
, l_listentry_rec.COL106, l_listentry_rec.COL107, l_listentry_rec.COL108, l_listentry_rec.COL109, l_listentry_rec.COL110, l_listentry_rec.COL111, l_listentry_rec.COL112
, l_listentry_rec.COL113, l_listentry_rec.COL114, l_listentry_rec.COL115, l_listentry_rec.COL116, l_listentry_rec.COL117, l_listentry_rec.COL118, l_listentry_rec.COL119
, l_listentry_rec.COL120, l_listentry_rec.COL121, l_listentry_rec.COL122, l_listentry_rec.COL123, l_listentry_rec.COL124, l_listentry_rec.COL125, l_listentry_rec.COL126
, l_listentry_rec.COL127, l_listentry_rec.COL128, l_listentry_rec.COL129, l_listentry_rec.COL130, l_listentry_rec.COL131, l_listentry_rec.COL132, l_listentry_rec.COL133
, l_listentry_rec.COL134, l_listentry_rec.COL135, l_listentry_rec.COL136, l_listentry_rec.COL137, l_listentry_rec.COL138, l_listentry_rec.COL139, l_listentry_rec.COL140
, l_listentry_rec.COL141, l_listentry_rec.COL142, l_listentry_rec.COL143, l_listentry_rec.COL144, l_listentry_rec.COL145, l_listentry_rec.COL146, l_listentry_rec.COL147
, l_listentry_rec.COL148, l_listentry_rec.COL149, l_listentry_rec.COL150, l_listentry_rec.COL151, l_listentry_rec.COL152, l_listentry_rec.COL153, l_listentry_rec.COL154
, l_listentry_rec.COL155, l_listentry_rec.COL156, l_listentry_rec.COL157, l_listentry_rec.COL158, l_listentry_rec.COL159, l_listentry_rec.COL160, l_listentry_rec.COL161
, l_listentry_rec.COL162, l_listentry_rec.COL163, l_listentry_rec.COL164, l_listentry_rec.COL165, l_listentry_rec.COL166, l_listentry_rec.COL167, l_listentry_rec.COL168
, l_listentry_rec.COL169, l_listentry_rec.COL170, l_listentry_rec.COL171, l_listentry_rec.COL172, l_listentry_rec.COL173, l_listentry_rec.COL174, l_listentry_rec.COL175
, l_listentry_rec.COL176, l_listentry_rec.COL177, l_listentry_rec.COL178, l_listentry_rec.COL179, l_listentry_rec.COL180, l_listentry_rec.COL181, l_listentry_rec.COL182
, l_listentry_rec.COL183, l_listentry_rec.COL184, l_listentry_rec.COL185, l_listentry_rec.COL186, l_listentry_rec.COL187, l_listentry_rec.COL188, l_listentry_rec.COL189
, l_listentry_rec.COL190, l_listentry_rec.COL191, l_listentry_rec.COL192, l_listentry_rec.COL193, l_listentry_rec.COL194, l_listentry_rec.COL195, l_listentry_rec.COL196
, l_listentry_rec.COL197, l_listentry_rec.COL198, l_listentry_rec.COL199, l_listentry_rec.COL200, l_listentry_rec.COL201, l_listentry_rec.COL202, l_listentry_rec.COL203
, l_listentry_rec.COL204, l_listentry_rec.COL205, l_listentry_rec.COL206, l_listentry_rec.COL207, l_listentry_rec.COL208, l_listentry_rec.COL209, l_listentry_rec.COL210
, l_listentry_rec.COL211, l_listentry_rec.COL212, l_listentry_rec.COL213, l_listentry_rec.COL214, l_listentry_rec.COL215, l_listentry_rec.COL216, l_listentry_rec.COL217
, l_listentry_rec.COL218, l_listentry_rec.COL219, l_listentry_rec.COL220, l_listentry_rec.COL221, l_listentry_rec.COL222, l_listentry_rec.COL223, l_listentry_rec.COL224
, l_listentry_rec.COL225, l_listentry_rec.COL226, l_listentry_rec.COL227, l_listentry_rec.COL228, l_listentry_rec.COL229, l_listentry_rec.COL230, l_listentry_rec.COL231
, l_listentry_rec.COL232, l_listentry_rec.COL233, l_listentry_rec.COL234, l_listentry_rec.COL235, l_listentry_rec.COL236, l_listentry_rec.COL237, l_listentry_rec.COL238
, l_listentry_rec.COL239, l_listentry_rec.COL240, l_listentry_rec.COL241, l_listentry_rec.COL242, l_listentry_rec.COL243, l_listentry_rec.COL244, l_listentry_rec.COL245
, l_listentry_rec.COL246, l_listentry_rec.COL247, l_listentry_rec.COL248, l_listentry_rec.COL249, l_listentry_rec.COL250, l_listentry_rec.IMP_SOURCE_LINE_ID
, l_listentry_rec.USAGE_RESTRICTION, l_listentry_rec.COL251, l_listentry_rec.COL252, l_listentry_rec.COL253, l_listentry_rec.COL254, l_listentry_rec.COL255, l_listentry_rec.COL256
, l_listentry_rec.COL257, l_listentry_rec.COL258, l_listentry_rec.COL259, l_listentry_rec.COL260, l_listentry_rec.COL261, l_listentry_rec.COL262, l_listentry_rec.COL263
, l_listentry_rec.COL264, l_listentry_rec.COL265, l_listentry_rec.COL266, l_listentry_rec.COL267, l_listentry_rec.COL268, l_listentry_rec.COL269, l_listentry_rec.COL270
, l_listentry_rec.COL271, l_listentry_rec.COL272, l_listentry_rec.COL273, l_listentry_rec.COL274, l_listentry_rec.COL275, l_listentry_rec.COL276, l_listentry_rec.COL277
, l_listentry_rec.COL278, l_listentry_rec.COL279, l_listentry_rec.COL280, l_listentry_rec.COL281, l_listentry_rec.COL282, l_listentry_rec.COL283, l_listentry_rec.COL284
, l_listentry_rec.COL285, l_listentry_rec.COL286, l_listentry_rec.COL287, l_listentry_rec.COL288, l_listentry_rec.COL289, l_listentry_rec.COL290, l_listentry_rec.COL291
, l_listentry_rec.COL292, l_listentry_rec.COL293, l_listentry_rec.COL294, l_listentry_rec.COL295, l_listentry_rec.COL296, l_listentry_rec.COL297, l_listentry_rec.COL298
, l_listentry_rec.COL299, l_listentry_rec.COL300
FROM AMS_LIST_ENTRIES
WHERE LIST_HEADER_ID = p_from_list_id_col(i) AND LIST_ENTRY_ID = p_from_list_entry_id_col(i);
'SELECT AMS_LIST_ENTRIES_S.NEXTVAL FROM DUAL'
INTO x_to_list_entry_id_col(i);
INSERT INTO AMS_LIST_ENTRIES
( LIST_ENTRY_ID, LIST_HEADER_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, OBJECT_VERSION_NUMBER
, LIST_SELECT_ACTION_ID, ARC_LIST_SELECT_ACTION_FROM, LIST_SELECT_ACTION_FROM_NAME, SOURCE_CODE, ARC_LIST_USED_BY_SOURCE, SOURCE_CODE_FOR_ID
, PIN_CODE, LIST_ENTRY_SOURCE_SYSTEM_ID, LIST_ENTRY_SOURCE_SYSTEM_TYPE, VIEW_APPLICATION_ID, MANUALLY_ENTERED_FLAG
, MARKED_AS_DUPLICATE_FLAG, MARKED_AS_RANDOM_FLAG, PART_OF_CONTROL_GROUP_FLAG, EXCLUDE_IN_TRIGGERED_LIST_FLAG, ENABLED_FLAG, CELL_CODE, DEDUPE_KEY
, RANDOMLY_GENERATED_NUMBER, CAMPAIGN_ID, MEDIA_ID, CHANNEL_ID, CHANNEL_SCHEDULE_ID, EVENT_OFFER_ID, CUSTOMER_ID, MARKET_SEGMENT_ID
, PARTY_ID, PARENT_PARTY_ID, VENDOR_ID, TRANSFER_FLAG, TRANSFER_STATUS, LIST_SOURCE, DUPLICATE_MASTER_ENTRY_ID, MARKED_FLAG, LEAD_ID, LETTER_ID
, PICKING_HEADER_ID, BATCH_ID, SUFFIX, FIRST_NAME, LAST_NAME, CUSTOMER_NAME, TITLE, ADDRESS_LINE1, ADDRESS_LINE2, CITY, STATE, ZIPCODE, COUNTRY, FAX, PHONE, EMAIL_ADDRESS
, COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8, COL9, COL10, COL11, COL12, COL13, COL14, COL15, COL16, COL17, COL18
, COL19, COL20, COL21, COL22, COL23, COL24, COL25, COL26, COL27, COL28, COL29, COL30, COL31, COL32, COL33, COL34, COL35
, COL36, COL37, COL38, COL39, COL40, COL41, COL42, COL43, COL44, COL45, COL46, COL47, COL48, COL49, COL50, COL51, COL52
, COL53, COL54, COL55, COL56, COL57, COL58, COL59, COL60, COL61, COL62, COL63, COL64, COL65, COL66, COL67, COL68, COL69
, COL70, COL71, COL72, COL73, COL74, COL75, COL76, COL77, COL78, COL79, COL80, COL81, COL82, COL83, COL84, COL85, COL86
, COL87, COL88, COL89, COL90, COL91, COL92, COL93, COL94, COL95, COL96, COL97, COL98, COL99, COL100, COL101, COL102, COL103
, COL104, COL105, COL106, COL107, COL108, COL109, COL110, COL111, COL112, COL113, COL114, COL115, COL116, COL117, COL118, COL119
, COL120, COL121, COL122, COL123, COL124, COL125, COL126, COL127, COL128, COL129, COL130, COL131, COL132, COL133, COL134, COL135
, COL136, COL137, COL138, COL139, COL140, COL141, COL142, COL143, COL144, COL145, COL146, COL147, COL148, COL149, COL150, COL151
, COL152, COL153, COL154, COL155, COL156, COL157, COL158, COL159, COL160, COL161, COL162, COL163, COL164, COL165, COL166, COL167
, COL168, COL169, COL170, COL171, COL172, COL173, COL174, COL175, COL176, COL177, COL178, COL179, COL180, COL181, COL182, COL183
, COL184, COL185, COL186, COL187, COL188, COL189, COL190, COL191, COL192, COL193, COL194, COL195, COL196, COL197, COL198, COL199
, COL200, COL201, COL202, COL203, COL204, COL205, COL206, COL207, COL208, COL209, COL210, COL211, COL212, COL213, COL214, COL215
, COL216, COL217, COL218, COL219, COL220, COL221, COL222, COL223, COL224, COL225, COL226, COL227, COL228, COL229, COL230, COL231
, COL232, COL233, COL234, COL235, COL236, COL237, COL238, COL239, COL240, COL241, COL242, COL243, COL244, COL245, COL246, COL247
, COL248, COL249, COL250, IMP_SOURCE_LINE_ID, USAGE_RESTRICTION, COL251, COL252, COL253, COL254, COL255, COL256, COL257, COL258
, COL259, COL260, COL261, COL262, COL263, COL264, COL265, COL266, COL267, COL268, COL269, COL270, COL271, COL272, COL273, COL274
, COL275, COL276, COL277, COL278, COL279, COL280, COL281, COL282, COL283, COL284, COL285, COL286, COL287, COL288, COL289, COL290
, COL291, COL292, COL293, COL294, COL295, COL296, COL297, COL298, COL299, COL300
)
(SELECT
:new_list_entry_id(i), :to_list_id, SYSDATE, LAST_UPDATED_BY, SYSDATE, CREATED_BY, LAST_UPDATE_LOGIN, OBJECT_VERSION_NUMBER
, LIST_SELECT_ACTION_ID, ARC_LIST_SELECT_ACTION_FROM, LIST_SELECT_ACTION_FROM_NAME, SOURCE_CODE, ARC_LIST_USED_BY_SOURCE, SOURCE_CODE_FOR_ID
, PIN_CODE, LIST_ENTRY_SOURCE_SYSTEM_ID, LIST_ENTRY_SOURCE_SYSTEM_TYPE, VIEW_APPLICATION_ID, MANUALLY_ENTERED_FLAG
, MARKED_AS_DUPLICATE_FLAG, MARKED_AS_RANDOM_FLAG, PART_OF_CONTROL_GROUP_FLAG, EXCLUDE_IN_TRIGGERED_LIST_FLAG, ENABLED_FLAG, CELL_CODE, DEDUPE_KEY
, RANDOMLY_GENERATED_NUMBER, CAMPAIGN_ID, MEDIA_ID, CHANNEL_ID, CHANNEL_SCHEDULE_ID, EVENT_OFFER_ID, CUSTOMER_ID, MARKET_SEGMENT_ID
, PARTY_ID, PARENT_PARTY_ID, VENDOR_ID, TRANSFER_FLAG, TRANSFER_STATUS, LIST_SOURCE, DUPLICATE_MASTER_ENTRY_ID, MARKED_FLAG, LEAD_ID, LETTER_ID
, PICKING_HEADER_ID, BATCH_ID, SUFFIX, FIRST_NAME, LAST_NAME, CUSTOMER_NAME, TITLE, ADDRESS_LINE1, ADDRESS_LINE2, CITY, STATE, ZIPCODE, COUNTRY, FAX, PHONE, EMAIL_ADDRESS
, COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8, COL9, COL10, COL11, COL12, COL13, COL14, COL15, COL16, COL17, COL18
, COL19, COL20, COL21, COL22, COL23, COL24, COL25, COL26, COL27, COL28, COL29, COL30, COL31, COL32, COL33, COL34, COL35
, COL36, COL37, COL38, COL39, COL40, COL41, COL42, COL43, COL44, COL45, COL46, COL47, COL48, COL49, COL50, COL51, COL52
, COL53, COL54, COL55, COL56, COL57, COL58, COL59, COL60, COL61, COL62, COL63, COL64, COL65, COL66, COL67, COL68, COL69
, COL70, COL71, COL72, COL73, COL74, COL75, COL76, COL77, COL78, COL79, COL80, COL81, COL82, COL83, COL84, COL85, COL86
, COL87, COL88, COL89, COL90, COL91, COL92, COL93, COL94, COL95, COL96, COL97, COL98, COL99, COL100, COL101, COL102, COL103
, COL104, COL105, COL106, COL107, COL108, COL109, COL110, COL111, COL112, COL113, COL114, COL115, COL116, COL117, COL118, COL119
, COL120, COL121, COL122, COL123, COL124, COL125, COL126, COL127, COL128, COL129, COL130, COL131, COL132, COL133, COL134, COL135
, COL136, COL137, COL138, COL139, COL140, COL141, COL142, COL143, COL144, COL145, COL146, COL147, COL148, COL149, COL150, COL151
, COL152, COL153, COL154, COL155, COL156, COL157, COL158, COL159, COL160, COL161, COL162, COL163, COL164, COL165, COL166, COL167
, COL168, COL169, COL170, COL171, COL172, COL173, COL174, COL175, COL176, COL177, COL178, COL179, COL180, COL181, COL182, COL183
, COL184, COL185, COL186, COL187, COL188, COL189, COL190, COL191, COL192, COL193, COL194, COL195, COL196, COL197, COL198, COL199
, COL200, COL201, COL202, COL203, COL204, COL205, COL206, COL207, COL208, COL209, COL210, COL211, COL212, COL213, COL214, COL215
, COL216, COL217, COL218, COL219, COL220, COL221, COL222, COL223, COL224, COL225, COL226, COL227, COL228, COL229, COL230, COL231
, COL232, COL233, COL234, COL235, COL236, COL237, COL238, COL239, COL240, COL241, COL242, COL243, COL244, COL245, COL246, COL247
, COL248, COL249, COL250, IMP_SOURCE_LINE_ID, USAGE_RESTRICTION, COL251, COL252, COL253, COL254, COL255, COL256, COL257, COL258
, COL259, COL260, COL261, COL262, COL263, COL264, COL265, COL266, COL267, COL268, COL269, COL270, COL271, COL272, COL273, COL274
, COL275, COL276, COL277, COL278, COL279, COL280, COL281, COL282, COL283, COL284, COL285, COL286, COL287, COL288, COL289, COL290
, COL291, COL292, COL293, COL294, COL295, COL296, COL297, COL298, COL299, COL300
FROM AMS_LIST_ENTRIES
WHERE LIST_HEADER_ID = :list_id_col(i) AND LIST_ENTRY_ID = :list_entry_id_col(i));
Update_AmsListHeaderCounts( p_to_list_id
, x_to_list_entry_id_col.COUNT
, x_to_list_entry_id_col.COUNT
);
SELECT RETURNS_ID, LIST_ENTRY_ID, LIST_HEADER_ID, ITM_CC_TZ_ID, DO_NOT_USE_FLAG
BULK COLLECT INTO :returns_id_col, :list_entry_id_col, :list_id_col, :callable_zone_id_col, :do_not_use_col
FROM IEC_G_RETURN_ENTRIES
WHERE RETURNS_ID IN (SELECT * FROM TABLE(CAST(:p_returns_id_col AS SYSTEM.NUMBER_TBL_TYPE)));
UPDATE IEC_G_RETURN_ENTRIES A
SET A.DO_NOT_USE_FLAG = ''Y''
, A.DO_NOT_USE_REASON = 5
WHERE A.RETURNS_ID = :returns_id(i);
SELECT A.RETURNS_ID
BULK COLLECT INTO :returns_id_col
FROM IEC_G_RETURN_ENTRIES A
WHERE A.RETURNS_ID IN (SELECT * FROM TABLE(CAST(:p_returns_id_col AS SYSTEM.NUMBER_TBL_TYPE)))
AND A.LIST_ENTRY_ID IN (SELECT LIST_ENTRY_ID
FROM AMS_LIST_ENTRIES
WHERE A.LIST_HEADER_ID = LIST_HEADER_ID
AND A.LIST_ENTRY_ID = LIST_ENTRY_ID
AND PARTY_ID NOT IN (SELECT PARTY_ID FROM AMS_LIST_ENTRIES WHERE LIST_HEADER_ID = :to_list_id));
'SELECT CALLBACK_FLAG
, NEXT_CALL_TIME
, CONTACT_POINT_INDEX
, DECODE(CONTACT_POINT_INDEX, 1, ''_S1''
, 2, ''_S2''
, 3, ''_S3''
, 4, ''_S4''
, 5, ''_S5''
, 6, ''_S6''
, ''_S1'')
FROM IEC_G_RETURN_ENTRIES
WHERE RETURNS_ID = :returns_id'
INTO l_callback_flag_col(i)
, l_next_call_time_col(i)
, l_cp_index_col(i)
, l_cp_postfix_col(i)
USING p_from_returns_id_col(i);
'UPDATE IEC_G_RETURN_ENTRIES
SET CALLBACK_FLAG = :callback_flag
, NEXT_CALL_TIME = :next_call_time
, ( CONTACT_POINT_ID
, ITM_CC_TZ_ID
, CONTACT_POINT_INDEX
, COUNTRY_CODE
, AREA_CODE
, PHONE_NUMBER
, RAW_PHONE_NUMBER
, TIME_ZONE ) =
(SELECT CONTACT_POINT_ID' || l_cp_postfix_col(i) || '
, MKTG_ITEM_CC_TZS_ID' || l_cp_postfix_col(i) || '
, :cp_index
, PHONE_COUNTRY_CODE' || l_cp_postfix_col(i) || '
, PHONE_AREA_CODE' || l_cp_postfix_col(i) || '
, PHONE_NUMBER' || l_cp_postfix_col(i) || '
, RAW_PHONE_NUMBER' || l_cp_postfix_col(i) || '
, TIME_ZONE' || l_cp_postfix_col(i) || '
FROM IEC_VAL_ENTRY_CACHE
WHERE LIST_ENTRY_ID = :list_entry_id)
WHERE LIST_HEADER_ID = :to_list_id AND LIST_ENTRY_ID = :list_entry_id
RETURNING RETURNS_ID INTO :returns_id'
USING IN l_callback_flag_col(i)
, IN l_next_call_time_col(i)
, IN l_cp_index_col(i)
, IN p_to_list_entry_id_col(i)
, IN p_to_list_id
, IN p_to_list_entry_id_col(i)
, OUT l_new_returns_id_col(i);
DELETE IEC_O_RCY_CALL_HISTORIES
WHERE RETURNS_ID = :new_returns_id(i);
UPDATE IEC_O_RCY_CALL_HISTORIES
SET RETURNS_ID = :new_returns_id(i)
WHERE RETURNS_ID = :old_returns_id(i);
'SELECT A.LIST_ENTRY_ID, A.POSTAL_CODE, A.DO_NOT_USE_FLAG, A.DO_NOT_USE_REASON, A.NEWLY_UPDATED_FLAG, B.SUBSET_ID, B.ITM_CC_TZ_ID, B.CONTACT_POINT_INDEX,
A.CONTACT_POINT_ID_S1, A.TIME_ZONE_S1, A.PHONE_COUNTRY_CODE_S1, A.PHONE_AREA_CODE_S1, A.PHONE_NUMBER_S1, A.RAW_PHONE_NUMBER_S1, A.REASON_CODE_S1, A.PHONE_LINE_TYPE_S1, NULL,
A.CONTACT_POINT_ID_S2, A.TIME_ZONE_S2, A.PHONE_COUNTRY_CODE_S2, A.PHONE_AREA_CODE_S2, A.PHONE_NUMBER_S2, A.RAW_PHONE_NUMBER_S2, A.REASON_CODE_S2, A.PHONE_LINE_TYPE_S2, NULL,
A.CONTACT_POINT_ID_S3, A.TIME_ZONE_S3, A.PHONE_COUNTRY_CODE_S3, A.PHONE_AREA_CODE_S3, A.PHONE_NUMBER_S3, A.RAW_PHONE_NUMBER_S3, A.REASON_CODE_S3, A.PHONE_LINE_TYPE_S3, NULL,
A.CONTACT_POINT_ID_S4, A.TIME_ZONE_S4, A.PHONE_COUNTRY_CODE_S4, A.PHONE_AREA_CODE_S4, A.PHONE_NUMBER_S4, A.RAW_PHONE_NUMBER_S4, A.REASON_CODE_S4, A.PHONE_LINE_TYPE_S4, NULL,
A.CONTACT_POINT_ID_S5, A.TIME_ZONE_S5, A.PHONE_COUNTRY_CODE_S5, A.PHONE_AREA_CODE_S5, A.PHONE_NUMBER_S5, A.RAW_PHONE_NUMBER_S5, A.REASON_CODE_S5, A.PHONE_LINE_TYPE_S5, NULL,
A.CONTACT_POINT_ID_S6, A.TIME_ZONE_S6, A.PHONE_COUNTRY_CODE_S6, A.PHONE_AREA_CODE_S6, A.PHONE_NUMBER_S6, A.RAW_PHONE_NUMBER_S6, A.REASON_CODE_S6, A.PHONE_LINE_TYPE_S6, NULL
FROM ' || l_source_type_view || ' A, IEC_G_RETURN_ENTRIES B
WHERE A.LIST_HEADER_ID = B.LIST_HEADER_ID(+)
AND A.LIST_ENTRY_ID = B.LIST_ENTRY_ID(+)
AND A.LIST_HEADER_ID = :list_id
AND A.LIST_ENTRY_ID IN (SELECT * FROM TABLE(CAST(:list_entry_id_col AS SYSTEM.NUMBER_TBL_TYPE)))'
USING p_list_id, p_list_entry_id_col;
Log_MissingSourceTypeColumns(p_list_id, l_source_type_view, Get_SourceType(p_list_id), 'LOAD_ENTRIES_PVT', 'SELECT_ENTRIES_FOR_LOAD');
PROCEDURE Update_DuplicateEntries
( p_from_list_id IN NUMBER
, p_to_list_id IN NUMBER
, p_mark_do_not_use IN BOOLEAN
, x_records_updated OUT NOCOPY NUMBER
)
IS
l_to_list_entry_id_col SYSTEM.number_tbl_type := SYSTEM.number_tbl_type(0);
SELECT B.list_entry_id, B.list_header_id, A.LAST_UPDATE_DATE, A.LAST_UPDATED_BY, A.CREATION_DATE, A.CREATED_BY, A.LAST_UPDATE_LOGIN, A.OBJECT_VERSION_NUMBER
, A.LIST_SELECT_ACTION_ID, A.ARC_LIST_SELECT_ACTION_FROM, A.LIST_SELECT_ACTION_FROM_NAME, A.SOURCE_CODE, A.ARC_LIST_USED_BY_SOURCE, A.SOURCE_CODE_FOR_ID
, A.PIN_CODE, A.LIST_ENTRY_SOURCE_SYSTEM_ID, A.LIST_ENTRY_SOURCE_SYSTEM_TYPE, A.VIEW_APPLICATION_ID, A.MANUALLY_ENTERED_FLAG
, A.MARKED_AS_DUPLICATE_FLAG, A.MARKED_AS_RANDOM_FLAG, A.PART_OF_CONTROL_GROUP_FLAG, A.EXCLUDE_IN_TRIGGERED_LIST_FLAG, A.ENABLED_FLAG, A.CELL_CODE, A.DEDUPE_KEY
, A.RANDOMLY_GENERATED_NUMBER, A.CAMPAIGN_ID, A.MEDIA_ID, A.CHANNEL_ID, A.CHANNEL_SCHEDULE_ID, A.EVENT_OFFER_ID, A.CUSTOMER_ID, A.MARKET_SEGMENT_ID
, A.PARTY_ID, A.PARENT_PARTY_ID, A.VENDOR_ID, A.TRANSFER_FLAG, A.TRANSFER_STATUS, A.LIST_SOURCE, A.DUPLICATE_MASTER_ENTRY_ID, A.MARKED_FLAG, A.LEAD_ID, A.LETTER_ID
, A.PICKING_HEADER_ID, A.BATCH_ID, A.SUFFIX, A.FIRST_NAME, A.LAST_NAME, A.CUSTOMER_NAME, A.TITLE, A.ADDRESS_LINE1, A.ADDRESS_LINE2, A.CITY, A.STATE, A.ZIPCODE
, A.COUNTRY, A.FAX, A.PHONE, A.EMAIL_ADDRESS
, A.COL1, A.COL2, A.COL3, A.COL4, A.COL5, A.COL6, A.COL7, A.COL8, A.COL9, A.COL10, A.COL11, A.COL12, A.COL13, A.COL14, A.COL15, A.COL16, A.COL17, A.COL18
, A.COL19, A.COL20, A.COL21, A.COL22, A.COL23, A.COL24, A.COL25, A.COL26, A.COL27, A.COL28, A.COL29, A.COL30, A.COL31, A.COL32, A.COL33, A.COL34, A.COL35
, A.COL36, A.COL37, A.COL38, A.COL39, A.COL40, A.COL41, A.COL42, A.COL43, A.COL44, A.COL45, A.COL46, A.COL47, A.COL48, A.COL49, A.COL50, A.COL51, A.COL52
, A.COL53, A.COL54, A.COL55, A.COL56, A.COL57, A.COL58, A.COL59, A.COL60, A.COL61, A.COL62, A.COL63, A.COL64, A.COL65, A.COL66, A.COL67, A.COL68, A.COL69
, A.COL70, A.COL71, A.COL72, A.COL73, A.COL74, A.COL75, A.COL76, A.COL77, A.COL78, A.COL79, A.COL80, A.COL81, A.COL82, A.COL83, A.COL84, A.COL85, A.COL86
, A.COL87, A.COL88, A.COL89, A.COL90, A.COL91, A.COL92, A.COL93, A.COL94, A.COL95, A.COL96, A.COL97, A.COL98, A.COL99, A.COL100, A.COL101, A.COL102, A.COL103
, A.COL104, A.COL105, A.COL106, A.COL107, A.COL108, A.COL109, A.COL110, A.COL111, A.COL112, A.COL113, A.COL114, A.COL115, A.COL116, A.COL117, A.COL118, A.COL119
, A.COL120, A.COL121, A.COL122, A.COL123, A.COL124, A.COL125, A.COL126, A.COL127, A.COL128, A.COL129, A.COL130, A.COL131, A.COL132, A.COL133, A.COL134, A.COL135
, A.COL136, A.COL137, A.COL138, A.COL139, A.COL140, A.COL141, A.COL142, A.COL143, A.COL144, A.COL145, A.COL146, A.COL147, A.COL148, A.COL149, A.COL150, A.COL151
, A.COL152, A.COL153, A.COL154, A.COL155, A.COL156, A.COL157, A.COL158, A.COL159, A.COL160, A.COL161, A.COL162, A.COL163, A.COL164, A.COL165, A.COL166, A.COL167
, A.COL168, A.COL169, A.COL170, A.COL171, A.COL172, A.COL173, A.COL174, A.COL175, A.COL176, A.COL177, A.COL178, A.COL179, A.COL180, A.COL181, A.COL182, A.COL183
, A.COL184, A.COL185, A.COL186, A.COL187, A.COL188, A.COL189, A.COL190, A.COL191, A.COL192, A.COL193, A.COL194, A.COL195, A.COL196, A.COL197, A.COL198, A.COL199
, A.COL200, A.COL201, A.COL202, A.COL203, A.COL204, A.COL205, A.COL206, A.COL207, A.COL208, A.COL209, A.COL210, A.COL211, A.COL212, A.COL213, A.COL214, A.COL215
, A.COL216, A.COL217, A.COL218, A.COL219, A.COL220, A.COL221, A.COL222, A.COL223, A.COL224, A.COL225, A.COL226, A.COL227, A.COL228, A.COL229, A.COL230, A.COL231
, A.COL232, A.COL233, A.COL234, A.COL235, A.COL236, A.COL237, A.COL238, A.COL239, A.COL240, A.COL241, A.COL242, A.COL243, A.COL244, A.COL245, A.COL246, A.COL247
, A.COL248, A.COL249, A.COL250, A.IMP_SOURCE_LINE_ID, A.USAGE_RESTRICTION, A.COL251, A.COL252, A.COL253, A.COL254, A.COL255, A.COL256, A.COL257, A.COL258
, A.COL259, A.COL260, A.COL261, A.COL262, A.COL263, A.COL264, A.COL265, A.COL266, A.COL267, A.COL268, A.COL269, A.COL270, A.COL271, A.COL272, A.COL273, A.COL274
, A.COL275, A.COL276, A.COL277, A.COL278, A.COL279, A.COL280, A.COL281, A.COL282, A.COL283, A.COL284, A.COL285, A.COL286, A.COL287, A.COL288, A.COL289, A.COL290
, A.COL291, A.COL292, A.COL293, A.COL294, A.COL295, A.COL296, A.COL297, A.COL298, A.COL299, A.COL300, A.NEWLY_UPDATED_FLAG
FROM AMS_LIST_ENTRIES A, AMS_LIST_ENTRIES B
WHERE A.LIST_HEADER_ID = p_from_list_id AND B.LIST_HEADER_ID = p_to_list_id AND A.PARTY_ID = B.PARTY_ID AND A.ENABLED_FLAG = 'Y' AND B.ENABLED_FLAG = 'Y';
l_listentry_rec.last_update_date := v_listentryrec.last_update_date;
l_listentry_rec.last_updated_by := v_listentryrec.last_updated_by;
l_listentry_rec.last_update_login := v_listentryrec.last_update_login;
l_listentry_rec.list_select_action_id := v_listentryrec.list_select_action_id;
l_listentry_rec.arc_list_select_action_from := v_listentryrec.arc_list_select_action_from;
l_listentry_rec.list_select_action_from_name := v_listentryrec.list_select_action_from_name;
AMS_LISTENTRY_PUB.update_listentry(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_entry_rec => l_listentry_rec);
Log_msg('AMS_LISTENTRY_PUB.update_listentry', l_msg_data);
UPDATE AMS_LIST_ENTRIES
SET NEWLY_UPDATED_FLAG = v_listentryrec.NEWLY_UPDATED_FLAG
WHERE LIST_ENTRY_ID = v_listentryrec.list_entry_id
AND LIST_HEADER_ID= v_listentryrec.list_header_id;
UPDATE AMS_LIST_ENTRIES A
SET ( LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, OBJECT_VERSION_NUMBER
, LIST_SELECT_ACTION_ID, ARC_LIST_SELECT_ACTION_FROM, LIST_SELECT_ACTION_FROM_NAME, SOURCE_CODE, ARC_LIST_USED_BY_SOURCE, SOURCE_CODE_FOR_ID
, PIN_CODE, LIST_ENTRY_SOURCE_SYSTEM_ID, LIST_ENTRY_SOURCE_SYSTEM_TYPE, VIEW_APPLICATION_ID, MANUALLY_ENTERED_FLAG
, MARKED_AS_DUPLICATE_FLAG, MARKED_AS_RANDOM_FLAG, PART_OF_CONTROL_GROUP_FLAG, EXCLUDE_IN_TRIGGERED_LIST_FLAG, ENABLED_FLAG, CELL_CODE, DEDUPE_KEY
, RANDOMLY_GENERATED_NUMBER, CAMPAIGN_ID, MEDIA_ID, CHANNEL_ID, CHANNEL_SCHEDULE_ID, EVENT_OFFER_ID, CUSTOMER_ID, MARKET_SEGMENT_ID
, PARTY_ID, PARENT_PARTY_ID, VENDOR_ID, TRANSFER_FLAG, TRANSFER_STATUS, LIST_SOURCE, DUPLICATE_MASTER_ENTRY_ID, MARKED_FLAG, LEAD_ID, LETTER_ID
, PICKING_HEADER_ID, BATCH_ID, SUFFIX, FIRST_NAME, LAST_NAME, CUSTOMER_NAME, TITLE, ADDRESS_LINE1, ADDRESS_LINE2, CITY, STATE, ZIPCODE, COUNTRY, FAX, PHONE, EMAIL_ADDRESS
, COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8, COL9, COL10, COL11, COL12, COL13, COL14, COL15, COL16, COL17, COL18
, COL19, COL20, COL21, COL22, COL23, COL24, COL25, COL26, COL27, COL28, COL29, COL30, COL31, COL32, COL33, COL34, COL35
, COL36, COL37, COL38, COL39, COL40, COL41, COL42, COL43, COL44, COL45, COL46, COL47, COL48, COL49, COL50, COL51, COL52
, COL53, COL54, COL55, COL56, COL57, COL58, COL59, COL60, COL61, COL62, COL63, COL64, COL65, COL66, COL67, COL68, COL69
, COL70, COL71, COL72, COL73, COL74, COL75, COL76, COL77, COL78, COL79, COL80, COL81, COL82, COL83, COL84, COL85, COL86
, COL87, COL88, COL89, COL90, COL91, COL92, COL93, COL94, COL95, COL96, COL97, COL98, COL99, COL100, COL101, COL102, COL103
, COL104, COL105, COL106, COL107, COL108, COL109, COL110, COL111, COL112, COL113, COL114, COL115, COL116, COL117, COL118, COL119
, COL120, COL121, COL122, COL123, COL124, COL125, COL126, COL127, COL128, COL129, COL130, COL131, COL132, COL133, COL134, COL135
, COL136, COL137, COL138, COL139, COL140, COL141, COL142, COL143, COL144, COL145, COL146, COL147, COL148, COL149, COL150, COL151
, COL152, COL153, COL154, COL155, COL156, COL157, COL158, COL159, COL160, COL161, COL162, COL163, COL164, COL165, COL166, COL167
, COL168, COL169, COL170, COL171, COL172, COL173, COL174, COL175, COL176, COL177, COL178, COL179, COL180, COL181, COL182, COL183
, COL184, COL185, COL186, COL187, COL188, COL189, COL190, COL191, COL192, COL193, COL194, COL195, COL196, COL197, COL198, COL199
, COL200, COL201, COL202, COL203, COL204, COL205, COL206, COL207, COL208, COL209, COL210, COL211, COL212, COL213, COL214, COL215
, COL216, COL217, COL218, COL219, COL220, COL221, COL222, COL223, COL224, COL225, COL226, COL227, COL228, COL229, COL230, COL231
, COL232, COL233, COL234, COL235, COL236, COL237, COL238, COL239, COL240, COL241, COL242, COL243, COL244, COL245, COL246, COL247
, COL248, COL249, COL250, IMP_SOURCE_LINE_ID, USAGE_RESTRICTION, COL251, COL252, COL253, COL254, COL255, COL256, COL257, COL258
, COL259, COL260, COL261, COL262, COL263, COL264, COL265, COL266, COL267, COL268, COL269, COL270, COL271, COL272, COL273, COL274
, COL275, COL276, COL277, COL278, COL279, COL280, COL281, COL282, COL283, COL284, COL285, COL286, COL287, COL288, COL289, COL290
, COL291, COL292, COL293, COL294, COL295, COL296, COL297, COL298, COL299, COL300, NEWLY_UPDATED_FLAG
) =
(SELECT
LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, OBJECT_VERSION_NUMBER
, LIST_SELECT_ACTION_ID, ARC_LIST_SELECT_ACTION_FROM, LIST_SELECT_ACTION_FROM_NAME, SOURCE_CODE, ARC_LIST_USED_BY_SOURCE, SOURCE_CODE_FOR_ID
, PIN_CODE, LIST_ENTRY_SOURCE_SYSTEM_ID, LIST_ENTRY_SOURCE_SYSTEM_TYPE, VIEW_APPLICATION_ID, MANUALLY_ENTERED_FLAG
, MARKED_AS_DUPLICATE_FLAG, MARKED_AS_RANDOM_FLAG, PART_OF_CONTROL_GROUP_FLAG, EXCLUDE_IN_TRIGGERED_LIST_FLAG, ENABLED_FLAG, CELL_CODE, DEDUPE_KEY
, RANDOMLY_GENERATED_NUMBER, CAMPAIGN_ID, MEDIA_ID, CHANNEL_ID, CHANNEL_SCHEDULE_ID, EVENT_OFFER_ID, CUSTOMER_ID, MARKET_SEGMENT_ID
, PARTY_ID, PARENT_PARTY_ID, VENDOR_ID, TRANSFER_FLAG, TRANSFER_STATUS, LIST_SOURCE, DUPLICATE_MASTER_ENTRY_ID, MARKED_FLAG, LEAD_ID, LETTER_ID
, PICKING_HEADER_ID, BATCH_ID, SUFFIX, FIRST_NAME, LAST_NAME, CUSTOMER_NAME, TITLE, ADDRESS_LINE1, ADDRESS_LINE2, CITY, STATE, ZIPCODE, COUNTRY, FAX, PHONE, EMAIL_ADDRESS
, COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8, COL9, COL10, COL11, COL12, COL13, COL14, COL15, COL16, COL17, COL18
, COL19, COL20, COL21, COL22, COL23, COL24, COL25, COL26, COL27, COL28, COL29, COL30, COL31, COL32, COL33, COL34, COL35
, COL36, COL37, COL38, COL39, COL40, COL41, COL42, COL43, COL44, COL45, COL46, COL47, COL48, COL49, COL50, COL51, COL52
, COL53, COL54, COL55, COL56, COL57, COL58, COL59, COL60, COL61, COL62, COL63, COL64, COL65, COL66, COL67, COL68, COL69
, COL70, COL71, COL72, COL73, COL74, COL75, COL76, COL77, COL78, COL79, COL80, COL81, COL82, COL83, COL84, COL85, COL86
, COL87, COL88, COL89, COL90, COL91, COL92, COL93, COL94, COL95, COL96, COL97, COL98, COL99, COL100, COL101, COL102, COL103
, COL104, COL105, COL106, COL107, COL108, COL109, COL110, COL111, COL112, COL113, COL114, COL115, COL116, COL117, COL118, COL119
, COL120, COL121, COL122, COL123, COL124, COL125, COL126, COL127, COL128, COL129, COL130, COL131, COL132, COL133, COL134, COL135
, COL136, COL137, COL138, COL139, COL140, COL141, COL142, COL143, COL144, COL145, COL146, COL147, COL148, COL149, COL150, COL151
, COL152, COL153, COL154, COL155, COL156, COL157, COL158, COL159, COL160, COL161, COL162, COL163, COL164, COL165, COL166, COL167
, COL168, COL169, COL170, COL171, COL172, COL173, COL174, COL175, COL176, COL177, COL178, COL179, COL180, COL181, COL182, COL183
, COL184, COL185, COL186, COL187, COL188, COL189, COL190, COL191, COL192, COL193, COL194, COL195, COL196, COL197, COL198, COL199
, COL200, COL201, COL202, COL203, COL204, COL205, COL206, COL207, COL208, COL209, COL210, COL211, COL212, COL213, COL214, COL215
, COL216, COL217, COL218, COL219, COL220, COL221, COL222, COL223, COL224, COL225, COL226, COL227, COL228, COL229, COL230, COL231
, COL232, COL233, COL234, COL235, COL236, COL237, COL238, COL239, COL240, COL241, COL242, COL243, COL244, COL245, COL246, COL247
, COL248, COL249, COL250, IMP_SOURCE_LINE_ID, USAGE_RESTRICTION, COL251, COL252, COL253, COL254, COL255, COL256, COL257, COL258
, COL259, COL260, COL261, COL262, COL263, COL264, COL265, COL266, COL267, COL268, COL269, COL270, COL271, COL272, COL273, COL274
, COL275, COL276, COL277, COL278, COL279, COL280, COL281, COL282, COL283, COL284, COL285, COL286, COL287, COL288, COL289, COL290
, COL291, COL292, COL293, COL294, COL295, COL296, COL297, COL298, COL299, COL300, NEWLY_UPDATED_FLAG
FROM AMS_LIST_ENTRIES
WHERE LIST_HEADER_ID = p_from_list_id AND PARTY_ID = A.PARTY_ID AND ENABLED_FLAG = 'Y')
WHERE LIST_HEADER_ID = p_to_list_id
AND ENABLED_FLAG = 'Y'
AND PARTY_ID IN (SELECT PARTY_ID
FROM AMS_LIST_ENTRIES
WHERE LIST_HEADER_ID = p_from_list_id
AND ENABLED_FLAG = 'Y')
RETURNING LIST_ENTRY_ID BULK COLLECT INTO l_to_list_entry_id_col;
l_to_list_entry_id_col.delete(l_num);
x_records_updated := l_to_list_entry_id_col.COUNT;
SELECT RETURNS_ID
BULK COLLECT INTO l_from_returns_id_col
FROM IEC_G_RETURN_ENTRIES
WHERE LIST_HEADER_ID = p_from_list_id
AND LIST_ENTRY_ID IN (SELECT LIST_ENTRY_ID
FROM AMS_LIST_ENTRIES
WHERE LIST_HEADER_ID = p_from_list_id
AND ENABLED_FLAG = 'Y'
AND PARTY_ID IN (SELECT PARTY_ID
FROM AMS_LIST_ENTRIES
WHERE LIST_HEADER_ID = p_to_list_id
AND ENABLED_FLAG = 'Y'));
Log( 'Update_DuplicateEntries'
, 'MAIN'
, SQLERRM
);
END Update_DuplicateEntries;
SELECT A.RETURNS_ID, A.LIST_HEADER_ID, A.LIST_ENTRY_ID, A.CAMPAIGN_SCHEDULE_ID, B.PARTY_ID
BULK COLLECT INTO l_from_returns_id_col, l_from_list_id_col, l_from_list_entry_id_col, l_from_schedule_id_col, l_party_id_col
FROM IEC_G_RETURN_ENTRIES A, AMS_LIST_ENTRIES B
WHERE A.LIST_HEADER_ID = B.LIST_HEADER_ID
AND A.LIST_ENTRY_ID = B.LIST_ENTRY_ID
AND A.RETURNS_ID IN (SELECT * FROM TABLE(CAST(p_from_returns_id_col AS SYSTEM.NUMBER_TBL_TYPE)))
AND B.PARTY_ID IN (SELECT PARTY_ID FROM AMS_LIST_ENTRIES WHERE LIST_HEADER_ID = p_to_list_id);
SELECT LIST_USED_BY_ID into l_schedule_id FROM AMS_ACT_LISTS
WHERE LIST_HEADER_ID = p_to_list_id AND LIST_ACT_TYPE = 'TARGET';
'UPDATE AMS_LIST_HEADERS_ALL
SET NO_OF_ROWS_DUPLICATES = NVL(NO_OF_ROWS_DUPLICATES, 0) + :rows_incr
WHERE LIST_HEADER_ID = :list_id'
USING IN l_list_duplicate_incr, IN p_to_list_id;
l_records_updated NUMBER;
SELECT B.RETURNS_ID
FROM IEC_G_RETURN_ENTRIES B
WHERE B.LIST_HEADER_ID = src_list_id
AND B.LIST_ENTRY_ID IN (SELECT LIST_ENTRY_ID
FROM AMS_LIST_ENTRIES
WHERE B.LIST_HEADER_ID = LIST_HEADER_ID
AND B.LIST_ENTRY_ID = LIST_ENTRY_ID
AND PARTY_ID NOT IN (SELECT PARTY_ID
FROM AMS_LIST_ENTRIES
WHERE LIST_HEADER_ID = dest_list_id));
'SELECT A.CALL_CENTER_READY_FLAG
, A.STATUS_CODE
, B.VALIDATED_ONCE_FLAG
FROM IEC_G_AO_LISTS_V A
, IEC_O_VALIDATION_STATUS B
WHERE A.LIST_HEADER_ID = B.LIST_HEADER_ID
AND A.LIST_HEADER_ID = :list_id
AND LANGUAGE = USERENV(''LANG'')'
INTO l_call_center_ready_flag
, l_status_code
, l_validated_once_flag
USING l_src_list_id;
'SELECT A.CALL_CENTER_READY_FLAG
, A.STATUS_CODE
, B.VALIDATED_ONCE_FLAG
FROM IEC_G_AO_LISTS_V A
, IEC_O_VALIDATION_STATUS B
WHERE A.LIST_HEADER_ID = B.LIST_HEADER_ID
AND A.LIST_HEADER_ID = :list_id
AND LANGUAGE = USERENV(''LANG'')'
INTO l_call_center_ready_flag
, l_status_code
, l_validated_once_flag
USING l_dest_list_id;
Update_DuplicateEntries
( l_src_list_id
, l_dest_list_id
, TRUE -- p_mark_do_not_use
, l_records_updated
);
l_records_updated NUMBER := 0;
SELECT B.RETURNS_ID, B.LIST_ENTRY_ID, B.LIST_HEADER_ID
FROM IEC_G_RETURN_ENTRIES B
WHERE B.LIST_HEADER_ID = from_list_id
AND B.LIST_ENTRY_ID IN (SELECT LIST_ENTRY_ID
FROM AMS_LIST_ENTRIES
WHERE B.LIST_HEADER_ID = LIST_HEADER_ID
AND B.LIST_ENTRY_ID = LIST_ENTRY_ID
AND PARTY_ID NOT IN (SELECT PARTY_ID
FROM AMS_LIST_ENTRIES
WHERE LIST_HEADER_ID = p_to_list_id));
'SELECT A.CALL_CENTER_READY_FLAG
, A.STATUS_CODE
, B.VALIDATED_ONCE_FLAG
FROM IEC_G_AO_LISTS_V A
, IEC_O_VALIDATION_STATUS B
WHERE A.LIST_HEADER_ID = B.LIST_HEADER_ID
AND A.LIST_HEADER_ID = :list_id
AND LANGUAGE = USERENV(''LANG'')'
INTO l_call_center_ready_flag
, l_status_code
, l_validated_once_flag
USING p_from_list_id;
'SELECT A.CALL_CENTER_READY_FLAG
, A.STATUS_CODE
, B.VALIDATED_ONCE_FLAG
FROM IEC_G_AO_LISTS_V A
, IEC_O_VALIDATION_STATUS B
WHERE A.LIST_HEADER_ID = B.LIST_HEADER_ID
AND A.LIST_HEADER_ID = :list_id
AND LANGUAGE = USERENV(''LANG'')'
INTO l_call_center_ready_flag
, l_status_code
, l_validated_once_flag
USING p_to_list_id;
Update_MoveEntriesStatus(p_from_list_id, p_to_list_id, 'MOVING', l_api_initiated);
Update_DuplicateEntries( P_from_list_id
, p_to_list_id
, FALSE
, l_records_updated);
Update_MoveEntriesStatusCounts(p_from_list_id, 0, l_records_updated);
Update_MoveEntriesStatusCounts(p_from_list_id, l_records_moved, 0);
Update_MoveEntriesStatus(p_from_list_id, p_to_list_id, 'MOVED', l_api_initiated);
Update_MoveEntriesStatus(p_from_list_id, p_to_list_id, 'FAILED_MOVE', l_api_initiated);
Update_MoveEntriesStatus(p_from_list_id, p_to_list_id, 'FAILED_MOVE', l_api_initiated);
Update_MoveEntriesStatus(p_from_list_id, p_to_list_id, 'FAILED_MOVE', l_api_initiated);
PROCEDURE Insert_AmsListEntries
( p_list_id IN NUMBER
, p_list_source_type IN VARCHAR2
, p_list_source_code IN VARCHAR2
, p_schedule_id IN NUMBER
, p_party_id IN NUMBER
, x_list_entry_id OUT NOCOPY NUMBER
)
IS
l_api_version CONSTANT NUMBER := 1.0;
l_listentry_rec.LAST_UPDATE_DATE := sysdate;
l_listentry_rec.LAST_UPDATED_BY := 1;
l_listentry_rec.LIST_SELECT_ACTION_ID := 0;
l_listentry_rec.ARC_LIST_SELECT_ACTION_FROM :='NONE';
'SELECT AMS_LIST_ENTRIES_S.NEXTVAL FROM DUAL'
INTO x_list_entry_id;
'INSERT INTO AMS_LIST_ENTRIES
( LIST_ENTRY_ID
, LIST_HEADER_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LIST_SELECT_ACTION_ID
, ARC_LIST_SELECT_ACTION_FROM
, SOURCE_CODE
, ARC_LIST_USED_BY_SOURCE
, SOURCE_CODE_FOR_ID
, PIN_CODE
, LIST_ENTRY_SOURCE_SYSTEM_ID
, LIST_ENTRY_SOURCE_SYSTEM_TYPE
, VIEW_APPLICATION_ID
, MANUALLY_ENTERED_FLAG
, MARKED_AS_DUPLICATE_FLAG
, MARKED_AS_RANDOM_FLAG
, PART_OF_CONTROL_GROUP_FLAG
, EXCLUDE_IN_TRIGGERED_LIST_FLAG
, ENABLED_FLAG
)
VALUES
( :list_entry_id
, :list_header_id
, SYSDATE
, 1
, SYSDATE
, 1
, 0
, ''NONE''
, :list_source_code
, ''CSCH''
, :schedule_id
, :list_entry_id
, :party_id
, :list_source_type
, 545
, ''N''
, ''N''
, ''N''
, ''N''
, ''N''
, ''Y''
)'
USING IN x_list_entry_id
, IN p_list_id
, IN p_list_source_code
, IN p_schedule_id
, IN x_list_entry_id
, IN p_party_id
, IN p_list_source_type;
Log( 'Insert_AmsListEntries'
, 'MAIN'
, SQLERRM
);
END Insert_AmsListEntries;
PROCEDURE Update_AmsListEntries
( p_list_id IN NUMBER
, p_list_entry_id IN NUMBER
, p_source_type_view IN VARCHAR2
, p_col_names IN SYSTEM.varchar_tbl_type
, p_col_values IN SYSTEM.varchar_tbl_type
)
IS
l_sql VARCHAR2(4000);
l_sql := 'UPDATE ' || p_source_type_view;
END Update_AmsListEntries;
'SELECT COUNT(*)
FROM AMS_LIST_ENTRIES
WHERE LIST_HEADER_ID = :list_id
AND PARTY_ID = :party_id'
INTO l_count
USING IN p_list_id, IN x_party_id;
Insert_AmsListEntries( p_list_id
, Get_SourceType(p_list_id)
, Get_ScheduleSourceCode(p_list_id)
, Get_ScheduleId(p_list_id)
, x_party_id
, x_list_entry_id
);
Update_AmsListEntries( p_list_id
, x_list_entry_id
, Get_SourceTypeView(p_list_id)
, p_column_name
, p_column_value
);
Update_AmsListHeaderCounts( p_list_id
, 1
, 1
);
'SELECT DO_NOT_USE_REASON_S1
FROM IEC_O_VALIDATION_REPORT_DETS
WHERE LIST_HEADER_ID = :list_id
AND LIST_ENTRY_ID = :list_entry_id'
INTO x_failure_code
USING IN p_list_id, IN p_list_entry_id;
PROCEDURE Update_CallbackTime
( p_list_id IN NUMBER
, p_list_entry_id IN NUMBER
, p_callback_time IN DATE
)
IS
l_callback_flag VARCHAR2(1);
'UPDATE IEC_G_RETURN_ENTRIES
SET CALLBACK_FLAG = ''Y''
, NEXT_CALL_TIME = :callback_time
WHERE LIST_HEADER_ID = :list_id
AND LIST_ENTRY_ID = :list_entry_id'
USING IN p_callback_time
, IN p_list_id
, IN p_list_entry_id;
Log( 'Update_CallbackTime'
, 'MAIN'
, SQLERRM
);
END Update_CallbackTime;
Update_CallbackTime(p_list_id, l_list_entry_id, p_callback_time);
SELECT DECODE( p_index
, 1, '_S1'
, 2, '_S2'
, 3, '_S3'
, 4, '_S4'
, 5, '_S5'
, 6, '_S6'
, NULL)
INTO l_postfix
FROM DUAL;
PROCEDURE Update_ContactPoint
( p_list_id IN NUMBER
, p_list_entry_id IN NUMBER
, p_party_id IN NUMBER
, p_contact_point_id IN NUMBER
, p_index IN NUMBER
, p_country_code IN VARCHAR2
, p_area_code IN VARCHAR2
, p_phone_number IN VARCHAR2
, p_time_zone IN NUMBER
, p_update_tca_flag IN VARCHAR2
)
IS
l_source_type_view VARCHAR2(32);
IF p_update_tca_flag = 'Y' THEN
l_contact_point_rec.contact_point_id := p_contact_point_id;
SELECT OBJECT_VERSION_NUMBER
INTO l_object_version_number
FROM HZ_CONTACT_POINTS
WHERE CONTACT_POINT_ID = p_contact_point_id;
HZ_CONTACT_POINT_V2PUB.update_phone_contact_point
( fnd_api.g_true -- init msg list
, l_contact_point_rec
, l_phone_rec
, l_object_version_number -- object version number
, l_return_status
, l_msg_count
, l_msg_data
);
Log( 'Update_ContactPoint'
, 'UPDATE_HZ_CONTACT_POINTS'
, l_error_msg
);
'UPDATE ' || l_source_type_view ||
' SET PHONE_COUNTRY_CODE' || l_postfix || ' = ' || p_country_code ||
' , PHONE_AREA_CODE' || l_postfix || ' = ' || p_area_code ||
' , PHONE_NUMBER' || l_postfix || ' = ' || p_phone_number ||
' , RAW_PHONE_NUMBER' || l_postfix || ' = NULL
, TIME_ZONE' || l_postfix || ' = ' || p_time_zone ||
'WHERE LIST_HEADER_ID = :list_id
AND LIST_ENTRY_ID = :list_entry_id
AND CONTACT_POINT_ID' || l_postfix || ' = ' || p_contact_point_id
USING IN p_list_id, IN p_list_entry_id;
Log_MissingSourceTypeColumns(p_list_id, l_source_type_view, Get_SourceType(p_list_id), 'Update_ContactPoint', 'UPDATE_AMS_LIST_ENTRIES');
Log( 'Update_ContactPoint'
, 'UPDATE_AMS_LIST_ENTRIES'
, SQLERRM
);
END Update_ContactPoint;
'SELECT COUNT(*)
FROM IEC_G_REP_SUBSET_COUNTS
WHERE LIST_HEADER_ID = :list_id'
INTO l_count
USING p_list_id;
UPDATE ' || p_source_type_v || ' A' || '
SET
REASON_CODE_S1 = (SELECT ITM_CC_TZ_ID FROM IEC_G_MKTG_ITEM_CC_TZS B WHERE B.TERRITORY_CODE = A.PHONE_COUNTRY_CODE_S1 AND B.TIMEZONE_ID = A.TIME_ZONE_S1 AND B.LIST_HEADER_ID = :1 AND B.REGION_ID IS NULL)
, REASON_CODE_S2 = (SELECT ITM_CC_TZ_ID FROM IEC_G_MKTG_ITEM_CC_TZS B WHERE B.TERRITORY_CODE = A.PHONE_COUNTRY_CODE_S2 AND B.TIMEZONE_ID = A.TIME_ZONE_S2 AND B.LIST_HEADER_ID = :2 AND B.REGION_ID IS NULL)
, REASON_CODE_S3 = (SELECT ITM_CC_TZ_ID FROM IEC_G_MKTG_ITEM_CC_TZS B WHERE B.TERRITORY_CODE = A.PHONE_COUNTRY_CODE_S3 AND B.TIMEZONE_ID = A.TIME_ZONE_S3 AND B.LIST_HEADER_ID = :3 AND B.REGION_ID IS NULL)
, REASON_CODE_S4 = (SELECT ITM_CC_TZ_ID FROM IEC_G_MKTG_ITEM_CC_TZS B WHERE B.TERRITORY_CODE = A.PHONE_COUNTRY_CODE_S4 AND B.TIMEZONE_ID = A.TIME_ZONE_S4 AND B.LIST_HEADER_ID = :4 AND B.REGION_ID IS NULL)
, REASON_CODE_S5 = (SELECT ITM_CC_TZ_ID FROM IEC_G_MKTG_ITEM_CC_TZS B WHERE B.TERRITORY_CODE = A.PHONE_COUNTRY_CODE_S5 AND B.TIMEZONE_ID = A.TIME_ZONE_S5 AND B.LIST_HEADER_ID = :5 AND B.REGION_ID IS NULL)
, REASON_CODE_S6 = (SELECT ITM_CC_TZ_ID FROM IEC_G_MKTG_ITEM_CC_TZS B WHERE B.TERRITORY_CODE = A.PHONE_COUNTRY_CODE_S6 AND B.TIMEZONE_ID = A.TIME_ZONE_S6 AND B.LIST_HEADER_ID = :6 AND B.REGION_ID IS NULL)
WHERE LIST_HEADER_ID = :list_header_id
AND TRANSLATE(UPPER(CURR_CP_COUNTRY_CODE), ''0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'', ''0123456789'') IS NULL
AND (DO_NOT_USE_FLAG = ''N'' OR (DO_NOT_USE_FLAG = ''Y'' AND DO_NOT_USE_REASON <> 4));
UPDATE ' || p_source_type_v || ' A' || '
SET
CURR_CP_COUNTRY_CODE = (SELECT PHONE_COUNTRY_CODE FROM HZ_PHONE_COUNTRY_CODES B WHERE B.TERRITORY_CODE = A.CURR_CP_COUNTRY_CODE)
, PHONE_COUNTRY_CODE_S1 = (SELECT PHONE_COUNTRY_CODE FROM HZ_PHONE_COUNTRY_CODES B WHERE B.TERRITORY_CODE = A.PHONE_COUNTRY_CODE_S1)
, PHONE_COUNTRY_CODE_S2 = (SELECT PHONE_COUNTRY_CODE FROM HZ_PHONE_COUNTRY_CODES B WHERE B.TERRITORY_CODE = A.PHONE_COUNTRY_CODE_S2)
, PHONE_COUNTRY_CODE_S3 = (SELECT PHONE_COUNTRY_CODE FROM HZ_PHONE_COUNTRY_CODES B WHERE B.TERRITORY_CODE = A.PHONE_COUNTRY_CODE_S3)
, PHONE_COUNTRY_CODE_S4 = (SELECT PHONE_COUNTRY_CODE FROM HZ_PHONE_COUNTRY_CODES B WHERE B.TERRITORY_CODE = A.PHONE_COUNTRY_CODE_S4)
, PHONE_COUNTRY_CODE_S5 = (SELECT PHONE_COUNTRY_CODE FROM HZ_PHONE_COUNTRY_CODES B WHERE B.TERRITORY_CODE = A.PHONE_COUNTRY_CODE_S5)
, PHONE_COUNTRY_CODE_S6 = (SELECT PHONE_COUNTRY_CODE FROM HZ_PHONE_COUNTRY_CODES B WHERE B.TERRITORY_CODE = A.PHONE_COUNTRY_CODE_S6)
WHERE LIST_HEADER_ID = :list_header_id
AND TRANSLATE(UPPER(CURR_CP_COUNTRY_CODE), ''0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'', ''0123456789'') IS NULL
AND (DO_NOT_USE_FLAG = ''N'' OR (DO_NOT_USE_FLAG = ''Y'' AND DO_NOT_USE_REASON <> 4));
'UPDATE IEC_G_LIST_SUBSETS
SET LOAD_PRIORITY = ROWNUM
WHERE LIST_HEADER_ID = :1
AND (DEFAULT_SUBSET_FLAG IS NULL OR DEFAULT_SUBSET_FLAG = ''N'')
AND LOAD_PRIORITY IS NULL'
USING p_list_id;
'INSERT INTO IEC_TC_TZ_PAIRS_CACHE (SUBSET_ID, TERRITORY_CODE, REGION_ID, TIMEZONE_ID, TC_TZ_PAIR_ID, RECORD_COUNT, CACHE_ONLY_FLAG)
SELECT SUBSET_ID, TERRITORY_CODE, REGION_ID, TIMEZONE_ID, ITM_CC_TZ_ID, RECORD_COUNT, ''O''
FROM IEC_G_MKTG_ITEM_CC_TZS
WHERE LIST_HEADER_ID = :list_id'
USING p_list_id;
'DELETE FROM IEC_G_MKTG_ITEM_CC_TZS
WHERE LIST_HEADER_ID = :list_id'
USING p_list_id;
'SELECT LIST_ENTRY_ID
, CALLBACK_FLAG
, NEXT_CALL_TIME
, DO_NOT_USE_FLAG
, DO_NOT_USE_REASON
, RECORD_OUT_FLAG
, RECORD_RELEASE_TIME
, CURR_CP_ID
, CURR_CP_INDEX
, CURR_CP_COUNTRY_CODE
, CURR_CP_AREA_CODE
, CURR_CP_PHONE_NUMBER
, CURR_CP_RAW_PHONE_NUMBER
, CURR_CP_TIME_ZONE_AUX
, REASON_CODE_S1
, REASON_CODE_S2
, REASON_CODE_S3
, REASON_CODE_S4
, REASON_CODE_S5
, REASON_CODE_S6
FROM ' || p_source_type_v || '
WHERE LIST_HEADER_ID = :list_id' || '
AND ( DO_NOT_USE_FLAG = ''N''
OR
(DO_NOT_USE_FLAG = ''Y'' AND DO_NOT_USE_REASON <> 4 AND (DO_NOT_USE_REASON < 400 OR DO_NOT_USE_REASON > 499))
)'
USING p_list_id;
INSERT INTO IEC_VAL_ENTRY_CACHE
( LIST_ENTRY_ID
, CALLBACK_FLAG
, NEXT_CALL_TIME
, DO_NOT_USE_FLAG
, PREV_STATUS_CODE
, DO_NOT_USE_REASON
, RECORD_OUT_FLAG
, RECORD_RELEASE_TIME
, CURR_CP_ID
, CURR_CP_INDEX
, CURR_CP_COUNTRY_CODE
, CURR_CP_AREA_CODE
, CURR_CP_PHONE_NUMBER
, CURR_CP_RAW_PHONE_NUMBER
, CURR_CP_TIME_ZONE
, CURR_CP_TERRITORY_CODE
, CURR_CP_REGION_ID
, TIME_ZONE_S1, TERRITORY_CODE_S1, REGION_ID_S1, VALID_FLAG_S1
, TIME_ZONE_S2, TERRITORY_CODE_S2, REGION_ID_S2, VALID_FLAG_S2
, TIME_ZONE_S3, TERRITORY_CODE_S3, REGION_ID_S3, VALID_FLAG_S3
, TIME_ZONE_S4, TERRITORY_CODE_S4, REGION_ID_S4, VALID_FLAG_S4
, TIME_ZONE_S5, TERRITORY_CODE_S5, REGION_ID_S5, VALID_FLAG_S5
, TIME_ZONE_S6, TERRITORY_CODE_S6, REGION_ID_S6, VALID_FLAG_S6
)
VALUES
( l_list_entry_id
, l_callback_flag
, l_next_call_time
, 'N' -- Default DO_NOT_USE_FLAG in temp table to 'N' for processing
, l_do_not_use_flag -- Store actual value of DO_NOT_USE_FLAG in PREV_STATUS_CODE column of temp table
, l_do_not_use_reason
, l_record_out_flag
, l_record_release_time
, l_cpc_id
, l_cpc_index
, l_cpc_cc
, l_cpc_ac
, l_cpc_pn
, l_cpc_rpn
, l_cpc_tz
, l_cpc_tc
, l_cpc_rc
, l_tz_s1, l_tc_s1, l_rc_s1, l_valid_flag_s1
, l_tz_s2, l_tc_s2, l_rc_s2, l_valid_flag_s2
, l_tz_s3, l_tc_s3, l_rc_s3, l_valid_flag_s3
, l_tz_s4, l_tc_s4, l_rc_s4, l_valid_flag_s4
, l_tz_s5, l_tc_s5, l_rc_s5, l_valid_flag_s5
, l_tz_s6, l_tc_s6, l_rc_s6, l_valid_flag_s6
);
'SELECT COUNT(*)
FROM IEC_VAL_ENTRY_CACHE'
INTO l_entry_count;
Update_CallableZones(p_list_id);
UPDATE ' || p_source_type_v || ' A' ||
' SET ( REASON_CODE_S1
, REASON_CODE_S2
, REASON_CODE_S3
, REASON_CODE_S4
, REASON_CODE_S5
, REASON_CODE_S6 )
=
( SELECT MKTG_ITEM_CC_TZS_ID_S1
, MKTG_ITEM_CC_TZS_ID_S2
, MKTG_ITEM_CC_TZS_ID_S3
, MKTG_ITEM_CC_TZS_ID_S4
, MKTG_ITEM_CC_TZS_ID_S5
, MKTG_ITEM_CC_TZS_ID_S6
FROM IEC_VAL_ENTRY_CACHE B WHERE A.LIST_ENTRY_ID = B.LIST_ENTRY_ID )
WHERE LIST_HEADER_ID = :list_id AND LIST_ENTRY_ID IN (SELECT LIST_ENTRY_ID FROM IEC_VAL_ENTRY_CACHE);
UPDATE IEC_G_RETURN_ENTRIES A
SET ( SUBSET_ID
, ITM_CC_TZ_ID
, CALLBACK_FLAG
, NEXT_CALL_TIME
, DO_NOT_USE_FLAG
, DO_NOT_USE_REASON
, RECORD_OUT_FLAG
, RECORD_RELEASE_TIME
, CONTACT_POINT_ID
, CONTACT_POINT_INDEX
, COUNTRY_CODE
, AREA_CODE
, PHONE_NUMBER
, RAW_PHONE_NUMBER
, TIME_ZONE )
=
( SELECT SUBSET_ID
, CURR_CP_MKTG_ITEM_CC_TZS_ID
, CALLBACK_FLAG
, NEXT_CALL_TIME
, PREV_STATUS_CODE -- ACTUAL DO_NOT_USE_FLAG VALUE FROM ALE
, DO_NOT_USE_REASON
, RECORD_OUT_FLAG
, RECORD_RELEASE_TIME
, CURR_CP_ID
, CURR_CP_INDEX
, CURR_CP_COUNTRY_CODE
, CURR_CP_AREA_CODE
, CURR_CP_PHONE_NUMBER
, CURR_CP_RAW_PHONE_NUMBER
, CURR_CP_TIME_ZONE
FROM IEC_VAL_ENTRY_CACHE B
WHERE A.LIST_ENTRY_ID = B.LIST_ENTRY_ID )
WHERE LIST_HEADER_ID = :list_id AND LIST_ENTRY_ID IN (SELECT LIST_ENTRY_ID FROM IEC_VAL_ENTRY_CACHE);
Update_ReportCounts( p_campaign_id
, p_schedule_id
, p_list_id
, l_subset_id_col
, l_subset_rec_loaded_col);
'SELECT COUNT(*)
FROM IEC_O_VALIDATION_STATUS
WHERE LIST_HEADER_ID = :list_id'
INTO l_count
USING p_list_id;
'INSERT INTO IEC_O_VALIDATION_STATUS
( VALIDATION_STATUS_ID
, LIST_HEADER_ID
, VALIDATED_ONCE_FLAG
, CREATED_BY
, CREATION_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, OBJECT_VERSION_NUMBER)
VALUES
( IEC_O_VALIDATION_STATUS_S.NEXTVAL
, :list_id
, ''Y''
, 1
, SYSDATE
, 1
, SYSDATE
, 0)'
USING p_list_id;
'UPDATE IEC_G_LIST_RT_INFO
SET EXECUTION_START_TIME = SYSDATE
, LAST_UPDATED_BY = 0
, LAST_UPDATE_DATE = SYSDATE
WHERE LIST_HEADER_ID IN (SELECT LIST_HEADER_ID FROM AMS_LIST_HEADERS_ALL WHERE LIST_HEADER_ID = :list_id AND STATUS_CODE = ''EXECUTING'')
AND EXECUTION_START_TIME IS NULL'
USING p_list_id;
Update_Status(p_list_id, 'VALIDATED');
PROCEDURE UPDATE_TZ_MAPPINGS
IS
L_COUNT NUMBER(9);
SELECT DISTINCT PHONE_AREA_CODE FROM IEC_G_TIMEZONE_MAPPINGS;
SELECT COUNT(*) INTO L_COUNT FROM
(SELECT DISTINCT TIMEZONE_ID FROM IEC_G_TIMEZONE_MAPPINGS WHERE PHONE_AREA_CODE = REC.PHONE_AREA_CODE);
SELECT DISTINCT TERRITORY_CODE INTO L_TERRITORY_CODE
FROM IEC_G_TIMEZONE_MAPPINGS
WHERE PHONE_AREA_CODE = REC.PHONE_AREA_CODE;
SELECT DISTINCT TIMEZONE_ID INTO L_TIMEZONE_ID
FROM IEC_G_TIMEZONE_MAPPINGS
WHERE PHONE_AREA_CODE = REC.PHONE_AREA_CODE;
DELETE IEC_G_TIMEZONE_MAPPINGS WHERE PHONE_AREA_CODE = REC.PHONE_AREA_CODE;
INSERT INTO IEC_G_TIMEZONE_MAPPINGS
(TIMEZONE_MAPPING_ID, TERRITORY_CODE, PHONE_AREA_CODE, POSTAL_CODE, TIMEZONE_ID, CREATED_BY,
CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, OBJECT_VERSION_NUMBER)
VALUES
(IEC_G_TIMEZONE_MAPPINGS_S.NEXTVAL, L_TERRITORY_CODE, REC.PHONE_AREA_CODE, NULL, L_TIMEZONE_ID, 0,
SYSDATE, 0, SYSDATE, 0, 1);
END UPDATE_TZ_MAPPINGS;