[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* This procedure is called by the concurrent program, will be used to delete
record from status table in order to purge publish history */
PROCEDURE Purge_Publish_History(err_buff IN OUT NOCOPY VARCHAR2,
ret_code OUT NOCOPY NUMBER,
p_batch_id IN NUMBER,
p_target_system_code IN VARCHAR2,
p_from_date IN VARCHAR2,
p_to_date IN VARCHAR2,
p_status_code IN VARCHAR2,
p_published_by IN NUMBER,
p_entity_type IN VARCHAR2)
IS
TYPE l_batch_table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
l_delete_batch_hdr VARCHAR2(1000) := NULL;
l_delete_batch_param VARCHAR2(1000) := NULL;
l_delete_entity_obj VARCHAR2(1000) := NULL;
l_delete_batch_status VARCHAR2(1000) := NULL;
l_delete_batch_system VARCHAR2(1000) := NULL;
l_delete_batch_system1 VARCHAR2(1000) := NULL;
l_delete_batch_status1 VARCHAR2(1000) := NULL;
l_delete_batch_status2 VARCHAR2(1000) := NULL;
l_delete_batch_status3 VARCHAR2(1000) := NULL;
l_batch_sql := ' SELECT DISTINCT hdr.Batch_id
FROM EGO_PUB_BAT_HDR_B hdr, EGO_PUB_BAT_STATUS_B status
WHERE hdr.batch_id= status.batch_id ';
l_batch_sql1 := ' select distinct Batch_id from ego_pub_bat_hdr_b where ';
l_batch_sql2 := ' select distinct BATCH_ID from EGO_PUB_BAT_STATUS_B where ';
l_batch_sql3 := ' select distinct BATCH_ID from EGO_PUB_BAT_SYSTEMS_B where ';
l_status_sys_seq := ' select SYSTEM_CODE from EGO_PUB_BAT_STATUS_B where ';
l_status_sel_sql := ' select count(*) from EGO_PUB_BAT_STATUS_B where BATCH_ID = :1 ';
l_system_sel_sql := ' select count(*) from EGO_PUB_BAT_SYSTEMS_B where BATCH_ID = :1 ';
l_delete_batch_hdr := 'Delete FROM ego_pub_bat_hdr_b WHERE batch_id = :1 ';
l_delete_batch_param := 'Delete FROM Ego_Pub_Bat_Params_B WHERE type = 1 and type_id = :1 '; --Type is 1 for Batch and 2 for System
l_delete_entity_obj := 'Delete FROM Ego_Pub_Bat_Ent_Objs_B WHERE batch_id = :1 ';
l_delete_batch_status := 'Delete FROM EGO_PUB_BAT_STATUS_B WHERE batch_id = :1 ';
l_delete_batch_status1 := 'Delete FROM EGO_PUB_BAT_STATUS_B WHERE batch_id = :1 and STATUS_CODE = :2 ';
l_delete_batch_system := 'Delete FROM EGO_PUB_BAT_SYSTEMS_B WHERE batch_id = :1 ';
l_delete_batch_system1 := 'Delete FROM EGO_PUB_BAT_SYSTEMS_B WHERE batch_id = :1 and SYSTEM_CODE = :2 ';
l_delete_batch_status2 := 'Delete FROM EGO_PUB_BAT_STATUS_B WHERE batch_id = :1 and SYSTEM_CODE = :2 and STATUS_CODE = :3 ';
l_delete_batch_status3 := 'Delete FROM EGO_PUB_BAT_STATUS_B WHERE batch_id = :1 and SYSTEM_CODE = :2 ';
' Processing data to delete record based on input ');
'No record has been deleted because all the input parameters contains null value. User have to enter value for atleast one of the input paramters to delete data');
' Date range is not provided to delete record. ');
/*Case when batch_id is passed as input parameter to delete publish history*/
IF (p_batch_id IS NOT NULL) THEN
fnd_file.put_line(fnd_file.Log,
' Control entrered into Batch ID varification block with : ' ||
p_batch_id);
/*Case when target_system_code is passed as input parameter to delete publish history*/
IF (p_target_system_code IS NOT NULL) THEN
fnd_file.put_line(fnd_file.Log,
' Control entrered into Target System varification block with : ' ||
p_target_system_code);
SELECT To_Char(To_Date(l_from_date, 'dd-mm-yy hh24:mi:ss'),
'dd-mon-yy hh24:mi:ss')
INTO l_pub_dt
FROM dual;
SELECT To_Char(To_Date(l_to_date, 'dd-mm-yy hh24:mi:ss'),
'dd-mon-yy hh24:mi:ss')
INTO l_pub_dt_to
FROM dual;
/*Case when publish status is passed as input parameter to delete publish history*/
IF (p_status_code IS NOT NULL) THEN
fnd_file.put_line(fnd_file.Log,
' Control entrered into status varification block with : ' ||
p_status_code);
/*Case when publisher is passed as input parameter to delete publish history*/
IF (p_published_by IS NOT NULL) THEN
fnd_file.put_line(fnd_file.Log,
' Control entrered into Publiched by varification block with : ' ||
p_published_by);
EXECUTE IMMEDIATE ' select count(*) from EGO_PUB_BAT_STATUS_B where BATCH_ID = ' ||
l_batch_id
INTO l_status_sel_count;
EXECUTE IMMEDIATE ' select count(*) from EGO_PUB_BAT_SYSTEMS_B where BATCH_ID = ' ||
l_batch_id
INTO l_system_sel_count;
EXECUTE IMMEDIATE l_delete_batch_system1
USING l_batch_id, l_sys_frm_status;
EXECUTE IMMEDIATE l_delete_batch_status2
USING l_batch_id, p_target_system_code, p_status_code;
EXECUTE IMMEDIATE l_delete_batch_status1
USING l_batch_id, p_status_code;
EXECUTE IMMEDIATE l_delete_batch_status3
USING l_batch_id, p_target_system_code;
EXECUTE IMMEDIATE l_delete_batch_system1
USING l_batch_id, p_target_system_code;
EXECUTE IMMEDIATE l_delete_batch_hdr
USING l_batch_id;
EXECUTE IMMEDIATE l_delete_batch_param
USING l_batch_id;
EXECUTE IMMEDIATE l_delete_entity_obj
USING l_batch_id;
EXECUTE IMMEDIATE l_delete_batch_status
USING l_batch_id;
EXECUTE IMMEDIATE l_delete_batch_system
USING l_batch_id;
EXECUTE IMMEDIATE ' select count(*) from EGO_PUB_BAT_STATUS_B where BATCH_ID = ' ||
l_batch_id
INTO l_status_sel_count;
EXECUTE IMMEDIATE ' select count(*) from EGO_PUB_BAT_SYSTEMS_B where BATCH_ID = ' ||
l_batch_id
INTO l_system_sel_count;
EXECUTE IMMEDIATE l_delete_batch_hdr
USING l_batch_id;
EXECUTE IMMEDIATE l_delete_batch_param
USING l_batch_id;
EXECUTE IMMEDIATE l_delete_entity_obj
USING l_batch_id;
'records are deleted based on the given parameters ');
'No records are deleted as the count of batch ID is 0');
'Records are not deleted due to runtime exception ' ||
SQLERRM);
l_update_batch_status VARCHAR2(2000) := NULL;
l_delete_batch_hdr VARCHAR2(1000) := NULL;
l_delete_batch_param VARCHAR2(1000) := NULL;
l_delete_entity_obj VARCHAR2(1000) := NULL;
l_delete_batch_status VARCHAR2(1000) := NULL;
l_delete_batch_system VARCHAR2(1000) := NULL;
l_delete_payload1 VARCHAR2(1000) := NULL;
l_delete_payload2 VARCHAR2(1000) := NULL;
l_delete_payload3 VARCHAR2(1000) := NULL;
l_delete_payload4 VARCHAR2(1000) := NULL;
l_delete_payload5 VARCHAR2(1000) := NULL;
l_delete_payload6 VARCHAR2(1000) := NULL;
l_delete_payload7 VARCHAR2(1000) := NULL;
l_batch_status_update_temp VARCHAR2(2000) := NULL;
l_batchcount_from_purgedays := 'select count(1) from ego_pub_bat_hdr_b where creation_date +' ||
p_purge_days || ' <= sysdate';
l_batch_from_purgedays := 'select batch_id from ego_pub_bat_hdr_b where creation_date +' ||
p_purge_days || ' <= sysdate';
l_set_is_purging := 'update EGO_PUB_BAT_HDR_B set Is_PURGING=''Y'' where Batch_id= :1';
l_getsessionid_batch := ' SELECT distinct session_id FROM EGO_PUB_WS_CONFIG where PARAMETER_NAME=''BATCHID'' AND NUMERIC_VALUE = :1';
l_getsessionid_list := ' SELECT distinct session_id FROM EGO_PUB_WS_CONFIG where PARAMETER_NAME!=''BATCHID'' AND creation_date +' ||
p_purge_days || ' <= sysdate';
l_getsessionid_batch_count := ' SELECT count(1) FROM EGO_PUB_WS_CONFIG where PARAMETER_NAME=''BATCHID'' AND NUMERIC_VALUE = :1 ';
l_getsessionid_list_count := ' SELECT count(1) FROM EGO_PUB_WS_CONFIG where PARAMETER_NAME!=''BATCHID'' AND creation_date +' ||
p_purge_days || ' <= sysdate';
l_batchid_from_batchname :='select batch_id from ego_pub_bat_hdr_b where batch_name= :1';
l_batchid_from_batchname_count :='select count(1) from ego_pub_bat_hdr_b where batch_name= :1';
l_batch_query_status := 'select status_code from EGO_PUB_BAT_STATUS_B where rownum=1';
l_update_batch_status := 'update EGO_PUB_BAT_STATUS_B set status_code= :1 where Batch_id= :2';
l_delete_batch_hdr := 'Delete FROM ego_pub_bat_hdr_b WHERE batch_id = :1 ';
l_delete_batch_param := 'Delete FROM Ego_Pub_Bat_Params_B WHERE type = 1 and type_id = :1 '; --Type is 1 for Batch and 2 for System
l_delete_entity_obj := 'Delete FROM Ego_Pub_Bat_Ent_Objs_B WHERE batch_id = :1 ';
l_delete_batch_status := 'Delete FROM EGO_PUB_BAT_STATUS_B WHERE batch_id = :1 ';
l_delete_batch_system := 'Delete FROM EGO_PUB_BAT_SYSTEMS_B WHERE batch_id = :1 ';
l_delete_payload1 := 'Delete FROM EGO_PUB_WS_ERRORS WHERE session_id = :1 ';
l_delete_payload2 := 'Delete FROM EGO_PUB_WS_FLAT_RECS WHERE session_id = :1 ';
l_delete_payload3 := 'Delete FROM EGO_PUB_WS_INPUT_IDENTIFIERS WHERE session_id = :1 ';
l_delete_payload4 := 'Delete FROM EGO_PUB_WS_OUTPUT WHERE session_id = :1 ';
l_delete_payload5 := 'Delete FROM EGO_PUB_WS_PARAMS WHERE session_id = :1 ';
l_delete_payload6 := 'Delete FROM EGO_ODI_WS_ENTITIES WHERE session_id = :1 ';
l_delete_payload7 := 'Delete FROM EGO_PUB_WS_CONFIG WHERE session_id = :1 ';
EXECUTE IMMEDIATE 'select count(1) from ego_pub_bat_hdr_b where batch_id=' ||
l_batch_id
INTO l_batch_count;
EXECUTE IMMEDIATE l_delete_batch_hdr
USING l_batch_id;
EXECUTE IMMEDIATE l_delete_batch_param
USING l_batch_id;
EXECUTE IMMEDIATE l_delete_entity_obj
USING l_batch_id;
EXECUTE IMMEDIATE l_delete_batch_status
USING l_batch_id;
EXECUTE IMMEDIATE l_delete_batch_system
USING l_batch_id;
EXECUTE IMMEDIATE l_delete_payload1
USING l_session_id;
EXECUTE IMMEDIATE l_delete_payload2
USING l_session_id;
EXECUTE IMMEDIATE l_delete_payload3
USING l_session_id;
EXECUTE IMMEDIATE l_delete_payload4
USING l_session_id;
EXECUTE IMMEDIATE l_delete_payload5
USING l_session_id;
EXECUTE IMMEDIATE l_delete_payload6
USING l_session_id;
EXECUTE IMMEDIATE l_delete_payload7
USING l_session_id;
EXECUTE IMMEDIATE l_delete_payload1
USING l_session_id;
EXECUTE IMMEDIATE l_delete_payload2
USING l_session_id;
EXECUTE IMMEDIATE l_delete_payload3
USING l_session_id;
EXECUTE IMMEDIATE l_delete_payload4
USING l_session_id;
EXECUTE IMMEDIATE l_delete_payload5
USING l_session_id;
EXECUTE IMMEDIATE l_delete_payload6
USING l_session_id;
EXECUTE IMMEDIATE l_delete_payload7
USING l_session_id;