DECLARE
--
-- Global Variables
--
l_object_name VARCHAR2(100) := NULL;
l_debug_flag VARCHAR2(10) := 'Y';
l_item_types_file_name VARCHAR2(240) :='user_file';
l_delimiter VARCHAR2(10) := '"|"';
--Object Name
l_item_types_ext VARCHAR2 (350) := 'Expenditure Item Types';
l_directory VARCHAR2(50):= 'TMP';
--
-- Declaring Local variables for getting query header based on Extract Type End
--
l_item_types_header fnd_new_messages.message_text%TYPE := NULL;
--
--Declaring Local variables for getting file handles
--
l_item_types_file utl_file.file_type;
l_log_file utl_file.file_type;
--
-- Declaring Local variables for getting query counts based on Extract Type End
--
l_exp_item_types_cnt NUMBER := 0;
l_bulk_collect_limit NUMBER := 10000;
l_item_type_start_date DATE;
l_item_type_end_date DATE;
l_exe_start_time DATE;
l_exe_end_time DATE;
l_item_type_time NUMBER := NULL;
l_directory_path dba_directories.directory_path%TYPE;
l_file_ext VARCHAR2 (1000) := TO_CHAR (SYSDATE, 'yyyymmddHHMISS');
l_log_name VARCHAR2 (1000) := 'logfile';
e_program_exit EXCEPTION;
--
-- Cursor to get expenditure item types
--
CURSOR cr_exp_item_types
IS
SELECT username user_name
,user_id user_id
from all_users;
TYPE xxpa_exp_item_types_tab IS TABLE OF cr_exp_item_types%ROWTYPE INDEX BY BINARY_INTEGER;
l_xxpa_exp_item_types_tab xxpa_exp_item_types_tab;
BEGIN
dbms_output.put_line('started');
l_exe_start_time := SYSDATE;
l_item_types_file_name := l_item_types_file_name||'_'||l_file_ext||'.dat';
l_log_name := l_log_name||'_'||l_file_ext||'.log';
l_log_file := utl_file.fopen (l_directory, l_log_name, 'W', 32767);
utl_file.put_line (l_log_file, ' ');
utl_file.put_line (l_log_file, ' ');
utl_file.put_line (l_log_file,
'---------------------------------'
|| l_object_name
|| '---------------------------------'
);
utl_file.put_line (l_log_file, ' ');
utl_file.put_line (l_log_file, ' ');
utl_file.put_line (l_log_file,
'Execution Start Time : '
||TO_CHAR (l_exe_start_time, 'YYYY-MM-DD HH:MI:SS')
);
utl_file.put_line (l_log_file,
'Log File Name : ' || l_log_name);
--
-- Get the directory path
--
BEGIN
SELECT directory_path
INTO l_directory_path
FROM dba_directories
WHERE directory_name = l_directory;
utl_file.put_line (l_log_file,
'Directory Path : ' || l_directory_path);
utl_file.put_line (l_log_file, ' ');
utl_file.put_line (l_log_file, ' ');
EXCEPTION
WHEN OTHERS
THEN
utl_file.put_line (l_log_file,
'Exception while fetching directory path -'
|| SQLERRM
|| '-'
|| SQLCODE
);
RAISE e_program_exit;
END;
--
-- End of getting directory path
--
utl_file.put_line (l_log_file, ' ');
--
-- Get the expenditure items types header
--
l_item_types_file := utl_file.fopen (l_directory, l_item_types_file_name, 'W', 32767);
BEGIN
SELECT '"USER_NAME"|"USER_ID"'
INTO l_item_types_header
FROM dual;
utl_file.put_line (l_item_types_file, l_item_types_header );
EXCEPTION
WHEN OTHERS
THEN
utl_file.put_line
(l_log_file,
'Exception while fetching header for expenditure item types -'
|| SQLERRM
|| '-'
|| SQLCODE
);
RAISE e_program_exit;
END;
--
-- End of fetching expenditure item types header
--
l_exp_item_types_cnt := 0;
l_item_type_start_date := SYSDATE;
IF cr_exp_item_types%ISOPEN
THEN
CLOSE cr_exp_item_types;
END IF;
OPEN cr_exp_item_types;
LOOP
FETCH cr_exp_item_types
BULK COLLECT INTO l_xxpa_exp_item_types_tab
LIMIT l_bulk_collect_limit;
FOR exp_item_types_idx IN 1..l_xxpa_exp_item_types_tab.COUNT
LOOP
BEGIN
l_exp_item_types_cnt := l_exp_item_types_cnt + 1;
utl_file.put_line(l_item_types_file,'"'
||l_xxpa_exp_item_types_tab(exp_item_types_idx).user_name
||l_delimiter
||l_xxpa_exp_item_types_tab(exp_item_types_idx).user_id
||'"');
EXCEPTION
WHEN OTHERS
THEN
utl_file.put_line (l_log_file,'Unexpected Error while extracting expediture
item types'
||SQLERRM
||dbms_utility.format_error_backtrace
);
RAISE e_program_exit;
END;
END LOOP; --FOR exp_item_types_idx IN 1..l_xxpa_exp_item_types_tab.COUNT
l_xxpa_exp_item_types_tab.DELETE;
EXIT WHEN cr_exp_item_types%NOTFOUND;
END LOOP; -- End of cr_exp_item_types
l_item_type_end_date := SYSDATE;
l_item_type_time := ROUND((l_item_type_end_date - l_item_type_start_date) * 24 * 3600 * 1000);
IF utl_file.is_open(l_item_types_file)
THEN
utl_file.fclose (l_item_types_file);
END IF;
l_exe_end_time := SYSDATE;
--
-- Generate the Log File
--
utl_file.put_line (l_log_file,
RPAD (NVL ('Data Element', ' '), 27, ' ')
|| RPAD (NVL ('File Name', ' '), 80, ' ')
|| RPAD (NVL ('Total Record Count', ' '), 23, ' ')
|| RPAD (NVL ('Extract Start Time', ' '), 30, ' ')
|| RPAD (NVL ('Extract End Time', ' '), 30, ' ')
|| 'Processing Time'
);
utl_file.put_line (l_log_file,' ');
utl_file.put_line (l_log_file,
RPAD (NVL (l_item_types_ext, ' '), 27, ' ')
|| RPAD (NVL (l_item_types_file_name, ' '), 80, ' ')
|| RPAD (NVL (TO_CHAR (l_exp_item_types_cnt),
' '),
23,
' '
)
|| RPAD (NVL (TO_CHAR (l_item_type_start_date,
'YYYY-MM-DD HH:MI:SS'
),
' '
),
30,
' '
)
|| RPAD (NVL (TO_CHAR (l_item_type_end_date,
'YYYY-MM-DD HH:MI:SS'
),
' '
),
30,
' '
)
|| l_item_type_time
);
utl_file.put_line (l_log_file,'' );
utl_file.put_line (l_log_file,
'Execution End Time : '
||TO_CHAR (l_exe_end_time, 'YYYY-MM-DD HH:MI:SS')
);
IF utl_file.is_open(l_log_file)
THEN
utl_file.fclose (l_log_file);
END IF;
--
-- End of log file generation
--
EXCEPTION
WHEN e_program_exit
THEN
utl_file.put_line
(l_log_file,
'Custom exception, please refer above in this file for details :'
||SQLERRM
||dbms_utility.format_error_backtrace
);
IF utl_file.is_open(l_log_file)
THEN
utl_file.fclose (l_log_file);
END IF;
WHEN OTHERS
THEN
utl_file.put_line (l_log_file,'Unexpected exception, details : '
|| SQLERRM
|| 'error code - '
|| SQLCODE
||dbms_utility.format_error_backtrace
);
IF utl_file.is_open(l_log_file)
THEN
utl_file.fclose (l_log_file);
END IF;
END;