create or replace procedure TRAIL_ELEMENT_CREATE_BATCH_TAB(p_object_type varchar2, p_run_id number, p_no_of_batch number)
IS
	err_msg varchar2(4000);
	proc_name varchar2(30);
	table_does_not_exist EXCEPTION;
    PRAGMA exception_init ( table_does_not_exist,-942 );
	PRAGMA exception_init ( invalid_identifier_exception, -904 );
BEGIN

	proc_name := $$plsql_unit;
	execution_log_writer (p_object_type, proc_name, p_run_id, 'S', null);

BEGIN
	EXECUTE IMMEDIATE 'truncate table STE_GTT_BNO';
EXCEPTION
	WHEN table_does_not_exist THEN
		NULL;
END;

BEGIN
	EXECUTE IMMEDIATE 'drop table STE_GTT_BNO';
EXCEPTION
	WHEN table_does_not_exist THEN
		NULL;
END;

BEGIN
	EXECUTE IMMEDIATE 'alter table stg_trail_element drop column BATCH_NO';
EXCEPTION
	WHEN invalid_identifier_exception THEN
		NULL;
END;

execute immediate '
CREATE TABLE STE_GTT_BNO
as
select ste.*, NTILE('||p_no_of_batch||') OVER (ORDER BY TRAIL_GRANITE_ID) AS BATCH_NO 
from STG_TRAIL_ELEMENT ste
';

execute immediate 'drop table STG_TRAIL_ELEMENT';

execute immediate 'alter table STE_GTT_BNO rename to STG_TRAIL_ELEMENT';

	execution_log_writer (p_object_type, proc_name, p_run_id, 'C', null);

-- exception
	-- when others then
		-- err_msg := dbms_utility.format_error_backtrace();
		-- execution_log_writer (p_object_type, proc_name, p_run_id, 'E', err_msg);

END;
/