[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Update_Schedule_Status
( p_schedule_id IN NUMBER
, p_status IN NUMBER
, p_user_id IN NUMBER
)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_SCHEDULE_STATUS';
l_schedule_rec.last_update_date := sysdate;
l_schedule_rec.last_updated_by := p_user_id;
SELECT OBJECT_VERSION_NUMBER
INTO l_schedule_rec.object_version_number
FROM AMS_CAMPAIGN_SCHEDULES_B
WHERE SCHEDULE_ID = p_schedule_id;
AMS_CAMP_SCHEDULE_PUB.Update_Camp_Schedule
( p_api_version_number => l_api_version,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_code,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data ,
p_schedule_rec => l_schedule_rec,
x_object_version_number => l_object_version_number
);
Log_AmsStatusError('UPDATE_SCHEDULE_STATUS', 'UPDATE_SCHEDULE', p_schedule_id, p_status);
END Update_Schedule_Status;
PROCEDURE Update_List_Status
( p_list_id IN NUMBER
, p_status IN VARCHAR2
, p_api_init_flag IN VARCHAR2
)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_LIST_STATUS';
UPDATE IEC_G_LIST_RT_INFO
SET EXECUTION_START_TIME = SYSDATE
, LAST_UPDATED_BY = NVL(FND_GLOBAL.conc_login_id, -1)
, LAST_UPDATE_DATE = SYSDATE
WHERE LIST_HEADER_ID = p_list_id
AND EXECUTION_START_TIME IS NULL;
, 'PRE_PROCESSING.UPDATE_EXECUTION_START_TIME'
, SQLERRM );
UPDATE IEC_G_LIST_RT_INFO
SET STATUS_CODE = p_status
, API_INITIATED_FLAG = l_api_init_flag
, LAST_UPDATED_BY = NVL(FND_GLOBAL.conc_login_id, -1)
, LAST_UPDATE_DATE = SYSDATE
WHERE LIST_HEADER_ID = p_list_id;
, 'UPDATE_AO_STATUS'
, SQLERRM );
SELECT USER_STATUS_ID
INTO l_curr_mkt_status
FROM AMS_LIST_HEADERS_ALL
WHERE LIST_HEADER_ID = p_list_id;
AMS_LISTHEADER_PVT.Update_ListHeader
( p_api_version => l_api_version,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_code,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data ,
p_listheader_rec => l_list_rec
);
, 'UPDATE_AMS_LIST_STATUS'
, l_msg_data );
END Update_List_Status;
PROCEDURE Update_List_Status
( p_list_id IN NUMBER
, p_status IN VARCHAR2
)
IS
BEGIN
Update_List_Status(p_list_id, p_status, 'N');
END Update_List_Status;
FOR list_rec IN (SELECT LIST_HEADER_ID
FROM IEC_O_LISTS_TO_STOP_V)
LOOP
BEGIN
Stop_ListExecution(list_rec.LIST_HEADER_ID);
EXECUTE IMMEDIATE 'DELETE FROM IEC_O_RCY_CALL_HISTORIES WHERE RETURNS_ID IN ' ||
'(SELECT RETURNS_ID FROM IEC_G_RETURN_ENTRIES WHERE LIST_HEADER_ID = :1)'
USING p_list_id;
, 'DELETE_CALL_HISTORY.LIST_' || p_list_id
, SQLERRM );
EXECUTE IMMEDIATE 'DELETE FROM IEC_O_TRANSITION_PHONES WHERE LIST_ID = :1'
USING p_list_id;
, 'DELETE_TRANSITION_PHONES.LIST_' || p_list_id
, SQLERRM );
EXECUTE IMMEDIATE 'DELETE FROM IEC_O_TRANSITION_SUBSETS WHERE LIST_ID = :1'
USING p_list_id;
, 'DELETE_TRANSITION_SUBSETS.LIST_' || p_list_id
, SQLERRM );
EXECUTE IMMEDIATE 'DELETE FROM IEC_O_TRANSITION_SUBSETS WHERE LIST_ID = :1'
USING p_list_id;
, 'DELETE_TRANSITION_SUBSETS.LIST_' || p_list_id
, SQLERRM );
EXECUTE IMMEDIATE 'DELETE FROM IEC_G_REP_SUBSET_COUNTS WHERE LIST_HEADER_ID = :1'
USING p_list_id;
, 'DELETE_SUBSET_COUNTS.LIST_' || p_list_id
, SQLERRM );
EXECUTE IMMEDIATE 'DELETE FROM IEC_G_MKTG_ITEM_CC_TZS WHERE LIST_HEADER_ID = :1'
USING p_list_id;
, 'DELETE_CALLABLE_ZONES.LIST_' || p_list_id
, SQLERRM );
EXECUTE IMMEDIATE 'DELETE FROM IEC_O_VALIDATION_HISTORY WHERE LIST_HEADER_ID = :1'
USING p_list_id;
, 'DELETE_VALIDATION_HISTORY.LIST_' || p_list_id
, SQLERRM );
EXECUTE IMMEDIATE 'DELETE FROM IEC_O_VALIDATION_REPORT_DETS WHERE LIST_HEADER_ID = :1'
USING p_list_id;
, 'DELETE_VALIDATION_REPORT_DETAILS.LIST_' || p_list_id
, SQLERRM );
EXECUTE IMMEDIATE 'DELETE FROM IEC_O_VALIDATION_STATUS WHERE LIST_HEADER_ID = :1'
USING p_list_id;
, 'DELETE_VALIDATION_STATUS.LIST_' || p_list_id
, SQLERRM );
EXECUTE IMMEDIATE 'DELETE FROM IEC_G_RETURN_ENTRIES WHERE LIST_HEADER_ID = :1'
USING p_list_id;
, 'DELETE_RETURN_ENTRIES.LIST_' || p_list_id
, SQLERRM );
Update_List_Status(p_list_id, 'DELETED');
UPDATE_LIST_STATUS( p_list_id
, 'ACTIVE');
SELECT DISTINCT B.SERVER_ID
BULK COLLECT INTO l_stopped_server_id_col
FROM IEC_G_RETURN_ENTRIES A, IEO_SVR_RT_INFO B
WHERE A.CHECKOUT_ACTION_ID = B.SERVER_ID
AND A.LIST_HEADER_ID = p_list_id
AND A.RECORD_OUT_FLAG = 'Y'
AND B.STATUS = 1;
UPDATE IEC_G_RETURN_ENTRIES
SET RECORD_OUT_FLAG = 'N'
, CHECKOUT_ACTION_ID = NULL
, LAST_UPDATE_DATE = SYSDATE
WHERE LIST_HEADER_ID = p_list_id
AND CHECKOUT_ACTION_ID = l_stopped_server_id_col(i);
SELECT COUNT(*)
INTO l_records_out
FROM IEC_G_RETURN_ENTRIES
WHERE LIST_HEADER_ID = p_list_id
AND RECORD_OUT_FLAG = 'Y';
UPDATE_LIST_STATUS( p_list_id
, 'INACTIVE');
UPDATE_LIST_STATUS( p_list_id
, 'STOPPING');
'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_List_Status(l_list_id, 'STOPPING', 'Y');
SELECT DISTINCT B.SERVER_ID
BULK COLLECT INTO l_stopped_server_id_col
FROM IEC_G_RETURN_ENTRIES A, IEO_SVR_RT_INFO B
WHERE A.CHECKOUT_ACTION_ID = B.SERVER_ID
AND A.LIST_HEADER_ID = l_list_id
AND A.RECORD_OUT_FLAG = 'Y'
AND B.STATUS = 1;
UPDATE IEC_G_RETURN_ENTRIES
SET RECORD_OUT_FLAG = 'N'
, CHECKOUT_ACTION_ID = NULL
, LAST_UPDATE_DATE = SYSDATE
WHERE LIST_HEADER_ID = l_list_id
AND CHECKOUT_ACTION_ID = l_stopped_server_id_col(i);
SELECT COUNT(*)
INTO l_records_out
FROM IEC_G_RETURN_ENTRIES
WHERE LIST_HEADER_ID = l_list_id
AND RECORD_OUT_FLAG = 'Y';
UPDATE_LIST_STATUS( l_list_id
, 'INACTIVE'
, NULL);
UPDATE_LIST_STATUS( l_list_id
, 'ACTIVE'
, NULL);
SELECT USER_STATUS_ID
INTO l_user_status_id
FROM AMS_USER_STATUSES_B
WHERE SYSTEM_STATUS_TYPE = 'AMS_CAMPAIGN_SCHEDULE_STATUS'
AND SYSTEM_STATUS_CODE = 'ON_HOLD'
AND ROWNUM = 1;
UPDATE_SCHEDULE_STATUS( p_schedule_id
, l_user_status_id
, p_user_id
);