Knowledge: Generic function and procedures

Purpose: This function will return the date from the input - no. of business days and from date

Modifications: 11/4/2003 xxxxxxxxxxxxxxxxxxxxxx
--------------------------------------------------------------------------------------------------------*/
FUNCTION calc_date_add_business_days(from_date date, no_of_business_days number) RETURN DATE IS

out_date date;
weekend_day varchar2(20);
temp_date date;
i number:= 0;

BEGIN
out_date := from_date;

loop exit when (i = no_of_business_days);
out_date := out_date +1;
weekend_day := to_char(out_date, 'D');
if (weekend_day not in (1,7)) then
i := i+1;
end if;

end loop;

return (out_date);
end;
--first parameter is input and second parameter number of days after
select calc_date_add_business_days(trunc(TO_DATE('27-JUN-2015')),1) from dual

2.) get age range

FUNCTION GET_AGE_RANGE(date_in IN date, /* e.g. birthday */
as_of_date_in IN date default sysdate, /* e.g. as of sysdate */
default_if_null IN varchar2 default null) RETURN VARCHAR2 IS
temp number;
ret_val varchar2(50) := null;
BEGIN
temp := floor((as_of_date_in - date_in)/365.25);
if temp between 0 and 18 then
ret_val := 'JRA';
elsif temp between 18 and 29 then
ret_val := '18-29';
elsif temp between 30 and 49 then
ret_val := '30-49';
elsif temp between 50 and 64 then
ret_val := '50-64';
elsif temp > 64 then
ret_val := '65+';
else
ret_val := default_if_null;
end if;
RETURN ret_val;
END get_age_range;
---------------------
select GET_AGE_RANGE(to_date('20-JUN-2015')) from dual
3.)
/* this function is needed for delimited files*/
function c_get(p_text varchar2,p_occur number, p_delimiter varchar2) return varchar2 is
v_out varchar2(32000) :=null;
begin
v_out:=substr(p_text,instr(p_text,p_delimiter,1,p_occur)+1,instr(p_text,p_delimiter,1,p_occur+1)-(instr(p_text,p_delimiter,1,p_occur)+1));
return (v_out);
end;

select dtc_sql_util.c_get('|subbu|srikanth|viswa|',1,'|') from dual
4.)
/**Returns only numbers from the given string**/
function return_numbers(p_text varchar2) return varchar2 is
v_length number:=length(p_text);
v_text varchar2(30000):=null;
v_char varchar2(10):=null;
begin
if (p_text is not null) then
for i in 1..v_length loop
v_char:=substr(p_text,i,1);
if (ascii(v_char) between 48 and 57) then
v_text:=v_text||v_char;
end if;
end loop;
end if;
return trim(v_text);
end;
5.)
/**Returns only alpha bytes(abcds) from the given string**/
function return_abcds(p_text varchar2) return varchar2 is
v_length number:=length(p_text);
v_text varchar2(30000):=null;
v_char varchar2(10):=null;
begin
if (p_text is not null) then
for i in 1..v_length loop
v_char:=substr(p_text,i,1);
if (ascii(v_char) between 65 and 90) or (ascii(v_char) between 97 and 122) then
v_text:=v_text||v_char;
end if;
end loop;
end if;
return trim(v_text);
end;
6)Routine to get next sequence no from the sequence
/*************************************************************
*
* Routine to return the next sequence value from any sequence
* that is passed to the routine.
*
*************************************************************/
FUNCTION get_nextval (in_sequence IN VARCHAR2)
RETURN NUMBER
IS
str VARCHAR2(100) := 'SELECT ' || in_sequence || '.' ||
'NEXTVAL next_seq FROM dual where rownum = 1' ;
cur INTEGER := DBMS_SQL.OPEN_CURSOR;
upd_rows INTEGER;
return_value INTEGER;
BEGIN
DBMS_SQL.PARSE(cur, str, DBMS_SQL.V7);
DBMS_SQL.DEFINE_COLUMN (cur, 1, return_value);
upd_rows := DBMS_SQL.EXECUTE_AND_FETCH(cur);
IF upd_rows = 1 THEN
DBMS_SQL.COLUMN_VALUE (cur, 1, return_value);
END IF;
DBMS_SQL.CLOSE_CURSOR (cur);
RETURN return_value;
EXCEPTION
WHEN OTHERS THEN
DBMS_SQL.CLOSE_CURSOR(cur);
RAISE_APPLICATION_ERROR(-20100,'DTC_UTIL.GET_NEXTVAL: Unable to get nextval for ' || in_sequence);
END get_nextval;
PROCEDURE ddl (ddl_string IN VARCHAR2, out_err_msg OUT VARCHAR2)
IS
cur INTEGER;
BEGIN
cur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cur, ddl_string, DBMS_SQL.V7);
DBMS_SQL.CLOSE_CURSOR (cur);
EXCEPTION
WHEN OTHERS THEN
out_err_msg := 'DTC_UTIL.DDL failed on: ' || ddl_string || ' SQL Error Message: ' ||sqlerrm;
--RAISE_APPLICATION_ERROR(-20101,'DTC_UTIL.DDL failed on: ' || ddl_string || ' SQL Error Message: ' ||sqlerrm);
END ddl;
/******************************************************************************
Returns the value of the column passed (good for post-query stuff)
******************************************************************************/
FUNCTION get_column_value ( column_name IN VARCHAR2
, table_name IN VARCHAR2
, pk_column IN VARCHAR2
, pk_value IN VARCHAR2
, err_text OUT VARCHAR2)
RETURN VARCHAR2 IS
sql_stmt VARCHAR2(2000);
cur_id INTEGER := DBMS_SQL.OPEN_CURSOR;
exe_err INTEGER;
col_value VARCHAR2(2000);

BEGIN
sql_stmt := 'SELECT '||column_name||' '||
'FROM '||table_name||' '||
'WHERE '||pk_column||' = '''||pk_value||'''';
DBMS_SQL.PARSE(cur_id,sql_stmt,DBMS_SQL.V7);
DBMS_SQL.DEFINE_COLUMN(cur_id,1,col_value,100);
exe_err := DBMS_SQL.EXECUTE_AND_FETCH(cur_id,TRUE);
DBMS_SQL.COLUMN_VALUE(cur_id,1,col_value);
DBMS_SQL.CLOSE_CURSOR(cur_id);

RETURN col_value;
EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_SQL.CLOSE_CURSOR(cur_id);
err_text := 'Too Many Rows Selected';
RETURN 'TOO_MANY_ROWS';
WHEN NO_DATA_FOUND THEN
DBMS_SQL.CLOSE_CURSOR(cur_id);
RETURN NULL;
WHEN OTHERS THEN
DBMS_SQL.CLOSE_CURSOR(cur_id);
err_text := SQLERRM;
RETURN 'ERROR';
END get_column_value;

/******************************************************************************
Returns the date value of the column passed (good for post-query stuff)
******************************************************************************/
FUNCTION get_date_column_value ( column_name IN VARCHAR2
, table_name IN VARCHAR2
, pk_column IN VARCHAR2
, pk_value IN VARCHAR2
, err_text OUT VARCHAR2)
RETURN VARCHAR2 IS
sql_stmt VARCHAR2(2000);
cur_id INTEGER := DBMS_SQL.OPEN_CURSOR;
exe_err INTEGER;
col_value VARCHAR2(2000);

BEGIN
sql_stmt := 'SELECT to_char('||column_name||',''MM/DD/YYYY'') '||
'FROM '||table_name||' '||
'WHERE '||pk_column||' = '''||pk_value||'''';
DBMS_SQL.PARSE(cur_id,sql_stmt,DBMS_SQL.V7);
DBMS_SQL.DEFINE_COLUMN(cur_id,1,col_value,100);
exe_err := DBMS_SQL.EXECUTE_AND_FETCH(cur_id,TRUE);
DBMS_SQL.COLUMN_VALUE(cur_id,1,col_value);
DBMS_SQL.CLOSE_CURSOR(cur_id);

RETURN col_value;
EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_SQL.CLOSE_CURSOR(cur_id);
err_text := 'Too Many Rows Selected';
RETURN 'TOO_MANY_ROWS';
WHEN NO_DATA_FOUND THEN
DBMS_SQL.CLOSE_CURSOR(cur_id);
RETURN NULL;
WHEN OTHERS THEN
DBMS_SQL.CLOSE_CURSOR(cur_id);
err_text := SQLERRM;
RETURN 'ERROR';
END get_date_column_value;

/************************************************************************
*
* Return the roles for the user/program that are currently active.
*
************************************************************************/
/***** Commented out for DTC - Needs to be modified to support new user/role structure *****
FUNCTION get_user_roles(in_user_id IN cmn_user.user_id%TYPE DEFAULT 1,
in_program_id IN cmn_program.program_id%TYPE DEFAULT 1)
RETURN VARCHAR2
IS
CURSOR get_program_role IS
SELECT role
FROM cmn_user_prog_role
WHERE
program_id = in_program_id
AND user_id = in_user_id
AND sysdate between start_date and nvl(end_date,sysdate);
role_str VARCHAR2(1000);
ret_val VARCHAr2(1000);
BEGIN
FOR i IN get_program_role LOOP
role_str := ',' || i.role || role_str;
END LOOP;
-- strip off the leading comma from the role streing, if it is NULL then
-- return 'DTC_USER'.
ret_val := nvl(substr(role_str,2,1000),'DTC_USER');
RETURN ret_val;
END get_user_roles;
/******************************************************************************
This procedure sets the seq value to the value passed
******************************************************************************/
PROCEDURE set_seq_value( in_seq_name IN VARCHAR2
, in_value IN INTEGER) IS
sql_drop VARCHAR2(500);
sql_create VARCHAR2(500);
sql_stmt VARCHAR2(500) := 'SELECT '||in_seq_name||'.NEXTVAL FROM DUAL';
cur_id INTEGER;
exe_err INTEGER;
curr_val INTEGER;
BEGIN
/* First Drop The Sequence */
sql_drop := 'DROP SEQUENCE '||in_seq_name;
cur_id := DBMS_SQL.OPEN_CURSOR;
BEGIN
DBMS_SQL.PARSE(cur_id,sql_drop,DBMS_SQL.NATIVE);
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20169,'Could Not Drop Sequence '||
in_seq_name||', it may be an invalid sequence!');
END;
DBMS_SQL.CLOSE_CURSOR(cur_id);
sql_create := 'CREATE SEQUENCE '||in_seq_name||CHR(10)||
'INCREMENT BY 1'||CHR(10)||
'START WITH '||in_value|| CHR(10)||
'MINVALUE '||in_value ||CHR(10)||
'MAXVALUE 9999999999'||CHR(10)||
'NOCYCLE'||CHR(10)||
'CACHE 20'||CHR(10)||
'NOORDER';
/* Then re-create the sequence */
cur_id := DBMS_SQL.OPEN_CURSOR;
BEGIN
DBMS_SQL.PARSE(cur_id,sql_create,DBMS_SQL.NATIVE);
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20169,'Could Not Re-Create Sequence '||
in_seq_name||', it has been dropped so you must recreate it so it will work, Sorry!'||
'--'||SQLERRM);
END;
DBMS_SQL.CLOSE_CURSOR(cur_id);
EXCEPTION
WHEN OTHERS THEN
IF DBMS_SQL.IS_OPEN(cur_id) THEN
DBMS_SQL.CLOSE_CURSOR(cur_id);
END IF;
RAISE_APPLICATION_ERROR(-20569,'DTC_UTIL.SET_SEQ_VALUE Error--'||SQLERRM);
END set_seq_value;
/******************************************************************************
This function returns a format mask (99,999,999.99) based on the length and
decimal places passed
******************************************************************************/
FUNCTION gimme_a_mask( in_length IN INTEGER
, in_dec_places IN INTEGER
, do_commas IN BOOLEAN DEFAULT TRUE)
RETURN VARCHAR2 IS
curr_length INTEGER := in_length;
curr_decimal INTEGER := NVL(in_dec_places,0);
mask VARCHAR2(50);
num_of_commas INTEGER;
BEGIN
/* mask := RPAD('9',curr_length,'9'); */
mask := RPAD('9',10,'9');
IF do_commas THEN
mask := REPLACE(TO_CHAR(TO_NUMBER(mask),'9,999,999,999'),' ',NULL);
END IF;
IF curr_decimal > 0 THEN
mask := mask||'.'||RPAD('9',curr_decimal,'9');
END IF;
RETURN mask;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20187,'DTC_UTIL.GIMME_A_MASK Error--'||SQLERRM);
END gimme_a_mask;
/******************************************************************************
This function returns 'Y' if the value passed is a number or 'N' if it is not
******************************************************************************/
FUNCTION is_this_a_number( in_string IN VARCHAR2)
RETURN VARCHAR2 IS
test_number NUMBER;
BEGIN
test_number := TO_NUMBER(in_string);
RETURN 'Y';
EXCEPTION
WHEN VALUE_ERROR THEN
RETURN 'N';
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20152,
'DTC_UTIL.IS_THIS_A_NUMBER Error--Can Not Display SQLERRM, Sorry!');
END is_this_a_number;
/******************************************************************************
This function returns the given string to the DD-MON-YY format
******************************************************************************/
FUNCTION valid_date (in_date IN VARCHAR2,
in_format IN VARCHAR2 DEFAULT 'DD-MON-YY'
)
RETURN VARCHAR2
IS
ret_val VARCHAR2(50);
BEGIN
ret_val := TO_CHAR(TO_DATE(in_date, in_format), in_format);
RETURN ret_val;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END valid_date;
/******************************************************************************
This function returns the datatype of the table.column
******************************************************************************/
FUNCTION get_datatype(in_table IN VARCHAR2,
in_column IN VARCHAR2,
in_global IN VARCHAR2)
RETURN VARCHAR2
IS
CURSOR get_column_datatype IS
SELECT data_type
FROM user_tab_columns
WHERE table_name = in_table
AND column_name = in_column;
CURSOR get_global_datatype IS
SELECT datatype
FROM dtc_global_variable
WHERE variable_name = in_global;
ret_val VARCHAR2(30);
BEGIN
IF in_column IS NOT NULL THEN
OPEN get_column_datatype;
FETCH get_column_datatype INTO ret_val;
CLOSE get_column_datatype;
ELSE
OPEN get_global_datatype;
FETCH get_global_datatype INTO ret_val;
CLOSE get_global_datatype;
END IF;
RETURN ret_val;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20519,'dtc_util.get_datatype: Unable to find datatype.');
END get_datatype;
/******************************************************************************
This function returns the position of the column in an index
******************************************************************************/
FUNCTION get_ind_col_position(in_owner IN VARCHAR2,
in_table IN VARCHAR2,
in_column IN VARCHAR2,
in_dblink IN VARCHAR2 default null
)
RETURN INTEGER
IS
CURSOR get_ind_col IS
SELECT
column_position
FROM
sys.all_ind_columns
WHERE
TABLE_OWNER = in_owner
AND TABLE_NAME = in_table
AND COLUMN_NAME = in_column;

v_select_statement varchar2(1000);
CURSOR_HANDLE INTEGER;
EXECUTE_RESULTS INTEGER;

ret_val INTEGER;

BEGIN

if in_dblink is null then
OPEN get_ind_col;
FETCH get_ind_col INTO ret_val;
IF get_ind_col%NOTFOUND THEN
ret_val := 0;
END IF;
CLOSE get_ind_col;

else
v_select_statement := 'select column_position from sys.all_ind_columns@'||in_dblink||
' where table_owner = '''||in_owner||''''||
' and table_name = '''||in_table||''''||
' and column_name = '''||in_column||'''';

CURSOR_HANDLE := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(CURSOR_HANDLE,v_select_statement,DBMS_SQL.V7);
DBMS_SQL.DEFINE_COLUMN(CURSOR_HANDLE,1,ret_val);
EXECUTE_RESULTS := DBMS_SQL.EXECUTE(CURSOR_HANDLE);
IF DBMS_SQL.FETCH_ROWS(CURSOR_HANDLE) > 0 THEN
DBMS_SQL.COLUMN_VALUE(CURSOR_HANDLE,1,ret_val);
END IF;
DBMS_SQL.CLOSE_CURSOR(CURSOR_HANDLE);

end if;
RETURN ret_val;

EXCEPTION

WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20783,'dtc_util.get_ind_col_position ' || sqlerrm);

END get_ind_col_position;

/******************************************************************************
This function returns the number of pipe sessions running
******************************************************************************/
--/************************* Commented out for DTC ******************************
FUNCTION get_pipe_count
RETURN INTEGER
IS
CURSOR get_count IS
SELECT count(*)
FROM sys.dba_jobs_running
WHERE job < 0; -- all PAP j0obs run with job < 0
ret_val INTEGER;
BEGIN
OPEN get_count;
FETCH get_count INTO ret_val;
CLOSE get_count;
RETURN ret_val;
EXCEPTION
WHEN OTHERS THEN
RETURN 0;
END get_pipe_count;
--******************************************************************************/
PROCEDURE delimited_to_table ( in_string IN VARCHAR2
,in_delimiter IN VARCHAR2 DEFAULT '|'
,out_rec_count OUT INTEGER
,out_tab OUT dtc_global.vc_2000
)
IS
i integer;
p integer;
np integer;
BEGIN
if in_string is null then out_rec_count := 0; return; end if;
i := 0;
p := 0;
np := 0;
LOOP
np := INSTR(in_string,in_delimiter,p+1);
IF np = 0 THEN
-- IF i <> 0 THEN
i := i + 1;
out_tab(i) := substr(in_string,p+1);
-- END IF;
EXIT;
ELSE
i := i + 1;
out_tab(i) := substr(in_string,p+1,np-(p+1));
p := np;
END IF;
END LOOP;
out_rec_count := i;
END;
PROCEDURE table_to_delimited( in_tab IN dtc_global.vc_2000
, in_delimiter IN VARCHAR2 DEFAULT '|'
, out_rec_count OUT INTEGER
, out_string OUT VARCHAR2
)
IS
i integer;
j integer;
t_out_string varchar2(2000);
BEGIN
t_out_string := '';
i := in_tab.COUNT;
FOR J in 1..i
LOOP
IF j <> i THEN
t_out_string := t_out_string || in_tab(j) || in_delimiter;
ELSE
t_out_string := t_out_string || in_tab(j) ;
END IF;
END LOOP;
out_rec_count := i;
out_string := t_out_string;
END;

/******************************************************************************
This procedure writes a message to a pipe named 'HOST_' || user
The messages can be retrieved real-time by executing host_r on the host
host_r is below in the comments
******************************************************************************/
procedure host( cmd in varchar2 ) is
status number;
begin
dbms_pipe.pack_message( cmd );
status := dbms_pipe.send_message( 'HOST_' || UPPER(user) );
if ( status <> 0 ) then raise_application_error( -20001, 'Pipe error' );
end if;
end host;

/******************************************************************************
This section uses set_c_string and set_c_delimiter to declare a string and its
delimiter, then get_val (given an number) will return that argument in the
string
******************************************************************************/
FUNCTION get_val (arg NUMBER,
in_string VARCHAR2, -- DEFAULT c_string,
in_delimiter VARCHAR2 -- DEFAULT c_delimiter
) RETURN VARCHAR2 IS
val VARCHAR2(256) := SUBSTR(in_string,
ifelse(arg = 1,1,INSTR(in_string,in_delimiter,1,arg-1)+1),
ifelse(INSTR(in_string,in_delimiter,1,arg) = 0,1000000,INSTR(in_string,in_delimiter,1,arg))-ifelse(arg = 1,1,INSTR(in_string,in_delimiter,1,arg-1)+1));
BEGIN
IF INSTR(in_string,in_delimiter,1,arg-1) = 0 THEN
val := NULL;
END IF;
RETURN val;
END get_val;
PROCEDURE set_c_string (str VARCHAR2) IS
BEGIN
c_string := str;
END set_c_string;
PROCEDURE set_c_delimiter (del VARCHAR2) IS
BEGIN
c_delimiter := del;
END set_c_delimiter;

/******************************************************************************
This section creates a DECODE for pl/sql. Overloaded, evaluates boolean expression
and returns tval_in if true else fval_in
Also includes ex_dyn, a generic execute for dbms_sql
******************************************************************************/
FUNCTION ifelse (BOOL_IN IN BOOLEAN,
TVAL_IN IN BOOLEAN,
FVAL_IN IN BOOLEAN)
RETURN BOOLEAN IS
BEGIN
IF BOOL_IN THEN
RETURN TVAL_IN;
ELSE
RETURN FVAL_IN;
END IF;
END ifelse;
FUNCTION ifelse (BOOL_IN IN BOOLEAN,
TVAL_IN IN DATE,
FVAL_IN IN DATE)
RETURN DATE IS
BEGIN
IF BOOL_IN THEN
RETURN TVAL_IN;
ELSE
RETURN FVAL_IN;
END IF;
END ifelse;
FUNCTION ifelse (BOOL_IN IN BOOLEAN,
TVAL_IN IN VARCHAR2,
FVAL_IN IN VARCHAR2)
RETURN VARCHAR2 IS
BEGIN
IF BOOL_IN THEN
RETURN TVAL_IN;
ELSE
RETURN FVAL_IN;
END IF;
END ifelse;
FUNCTION ifelse (BOOL_IN IN BOOLEAN,
TVAL_IN IN NUMBER,
FVAL_IN IN NUMBER)
RETURN NUMBER IS
BEGIN
IF BOOL_IN THEN
RETURN TVAL_IN;
ELSE
RETURN FVAL_IN;
END IF;
END ifelse;
FUNCTION ex_dyn(sql_statement varchar2) RETURN VARCHAR2 IS
cursor_handle INTEGER;
curs_result INTEGER;
ret_column VARCHAR2(2000);
rows_fetched INTEGER;
BEGIN
cursor_handle := dbms_sql.open_cursor;
dbms_sql.parse(cursor_handle, sql_statement, dbms_sql.native);
dbms_sql.define_column(cursor_handle,1,ret_column,2000);
curs_result := dbms_sql.execute(cursor_handle);
rows_fetched := dbms_sql.fetch_rows(cursor_handle);
dbms_sql.column_value(cursor_handle,1,ret_column);
dbms_sql.close_cursor(cursor_handle);

RETURN ret_column;
EXCEPTION
WHEN others THEN
dbms_sql.close_cursor(cursor_handle);
RETURN null;
END ex_dyn;

/******************************************************************************
This section evaluates any range. yes_end_null and no_end_null determine
if the second value of the range can be null or not.
******************************************************************************/
PROCEDURE yes_end_null IS
BEGIN
end_null_ok := TRUE;
END yes_end_null ;
PROCEDURE no_end_null IS
BEGIN
end_null_ok := FALSE;
END no_end_null ;
FUNCTION C (begin_val DATE, end_val DATE) RETURN BOOLEAN IS
begin_null BOOLEAN := begin_val IS NULL;
end_null BOOLEAN := end_val IS NULL;
BEGIN
IF begin_null THEN
IF end_null THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
ELSIF end_null_ok THEN
IF end_null THEN
RETURN TRUE;
ELSIF TRUNC(begin_val) > TRUNC(end_val) THEN
RETURN FALSE;
ELSE
RETURN TRUE ;
END IF ;
ELSE
IF end_null OR TRUNC(begin_val) > TRUNC(end_val) THEN
RETURN FALSE ;
ELSE
RETURN TRUE ;
END IF ;
END IF;
END c;
FUNCTION C (begin_val NUMBER, end_val NUMBER) RETURN BOOLEAN IS
begin_null BOOLEAN := begin_val IS NULL;
end_null BOOLEAN := end_val IS NULL;
BEGIN
IF begin_null THEN
IF end_null THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
ELSIF end_null_ok THEN
IF end_null THEN
RETURN TRUE;
ELSIF begin_val > end_val THEN
RETURN FALSE;
ELSE
RETURN TRUE ;
END IF ;
ELSE
IF end_null OR begin_val > end_val THEN
RETURN FALSE ;
ELSE
RETURN TRUE ;
END IF ;
END IF;
END c;
FUNCTION C (begin_val VARCHAR2, end_val VARCHAR2) RETURN BOOLEAN IS
begin_null BOOLEAN := begin_val IS NULL;
end_null BOOLEAN := end_val IS NULL;
BEGIN
IF begin_null THEN
IF end_null THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
ELSIF end_null_ok THEN
IF end_null THEN
RETURN TRUE;
ELSIF begin_val > end_val THEN
RETURN FALSE;
ELSE
RETURN TRUE ;
END IF ;
ELSE
IF end_null OR begin_val > end_val THEN
RETURN FALSE ;
ELSE
RETURN TRUE ;
END IF ;
END IF;
END c;

/******************************************************************************
This function returns the number of delimiters passed until it finds the value
passed in the string passed if the value.
******************************************************************************/
FUNCTION get_occurance ( in_string IN VARCHAR2
, in_value IN VARCHAR2
, in_delimiter IN VARCHAR2)
RETURN NUMBER IS

new_string VARCHAR2(2000);
value_pos NUMBER;
counter NUMBER;

BEGIN

value_pos := INSTR(in_string,in_value);

IF value_pos = 0 THEN
RETURN NULL;
END IF;

new_string := SUBSTR(in_string,1,value_pos - 1);

counter := 0;

LOOP
counter := counter + 1;

EXIT WHEN new_string IS NULL;

value_pos := INSTR(new_string,in_delimiter);
IF value_pos = 0 THEN
new_string := NULL;
ELSE
new_string := SUBSTR(new_string,value_pos + 1);
END IF;

END LOOP;

RETURN counter;

END get_occurance;

FUNCTION over_punch (opunch varchar2)
return varchar2
IS
/***************************************************************
* HDS Information Systems
****************************************************************
*
* Name: OVER_PUNCH
*
* Author: Dave Robinson
*
* Purpose: Converts numbers that contain over punch
* characters into real numbers. Returns
* either the converted number or null.
*
* Mod History:
* Mapped from PAP into DTC by Wayne Xu on 2/10/1999
*
****************************************************************
***************************************************************/
cnvt_result varchar2(15);
begin
select decode(substr(opunch,1,1),'{','+','}','-',
decode(substr(opunch,-1,1),'{','+','}','-',
decode(sign(trunc(ascii(substr(upper(opunch),1,1))/74)),1,'-',
decode(sign(trunc(ascii(substr(upper(opunch),-1,1))/74)),1,'-','+'
))))||translate(upper(opunch),
'{}ABCDEFGHIJKLMNOPQRSTUVWXYZ',
'0012345678912345678900000000')
into cnvt_result
from dual;
if to_number(cnvt_result)>=-10
and to_number(cnvt_result) <=-1 then
cnvt_result:=null;
end if;
return cnvt_result;
exception
when others then
cnvt_result:=null;
return cnvt_result;
end over_punch;

/**************** Moved from dtc_global **************************/

/******************************************************************************
This procedure assigns the value passed to the global package variable
passed.
******************************************************************************/
PROCEDURE assign_global_value_char( in_var_name IN VARCHAR2
, in_value IN VARCHAR2)
IS

plsql_stmt VARCHAR2(1000) := 'BEGIN '||in_var_name||' := ';
exe_err INTEGER;
assign_cur_id INTEGER;

BEGIN

IF in_value IS NULL THEN
plsql_stmt := plsql_stmt||'NULL;';
ELSE
plsql_stmt := plsql_stmt||':char_value;';
END IF;

plsql_stmt := plsql_stmt||' END;';

assign_cur_id := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(assign_cur_id,plsql_stmt,DBMS_SQL.NATIVE);
IF in_value IS NOT NULL THEN
DBMS_SQL.BIND_VARIABLE(assign_cur_id,'char_value',in_value);
END IF;

exe_err := DBMS_SQL.EXECUTE(assign_cur_id);
DBMS_SQL.CLOSE_CURSOR(assign_cur_id);

EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20100,'Assign Global Value--'||SQLERRM);

END assign_global_value_char;

PROCEDURE assign_global_value( in_var_name IN VARCHAR2
, in_value IN NUMBER)
IS

plsql_stmt VARCHAR2(1000) := 'BEGIN '||in_var_name||' := ';
exe_err INTEGER;
assign_cur_id INTEGER;

BEGIN

IF in_value IS NULL THEN
plsql_stmt := plsql_stmt||'NULL;';
ELSE
plsql_stmt := plsql_stmt||TO_CHAR(in_value)||';';
END IF;

plsql_stmt := plsql_stmt||' END;';

assign_cur_id := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(assign_cur_id,plsql_stmt,DBMS_SQL.V7);

exe_err := DBMS_SQL.EXECUTE(assign_cur_id);
DBMS_SQL.CLOSE_CURSOR(assign_cur_id);

EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20100,'Assign Global Value--'||SQLERRM);

END assign_global_value;

PROCEDURE assign_global_value_date( in_var_name IN VARCHAR2
, in_value IN DATE)
IS

plsql_stmt VARCHAR2(1000) := 'BEGIN '||in_var_name||' := ';
exe_err INTEGER;
assign_cur_id INTEGER;

BEGIN

IF in_value IS NULL THEN
plsql_stmt := plsql_stmt||'NULL;';
ELSE
plsql_stmt := plsql_stmt||'TO_DATE('''||TO_CHAR(in_value,'MM/DD/YYYY')||''',''MM/DD/YYYY'');';
END IF;

plsql_stmt := plsql_stmt||' END;';

assign_cur_id := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(assign_cur_id,plsql_stmt,DBMS_SQL.V7);

exe_err := DBMS_SQL.EXECUTE(assign_cur_id);
DBMS_SQL.CLOSE_CURSOR(assign_cur_id);

EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20100,'Assign Global Value--'||SQLERRM);

END assign_global_value_date;

/******************************************************************************
This function returns the title of the current application title.
******************************************************************************/

/*********************** This is not used by DTC ******************************
FUNCTION get_application RETURN VARCHAR2 IS

app_record dtc_cur.app_cur%ROWTYPE;

BEGIN

OPEN dtc_cur.app_cur(dtc_global.application_id);
FETCH dtc_cur.app_cur INTO app_record;
IF dtc_cur.app_cur%NOTFOUND THEN
CLOSE dtc_cur.app_cur;
RETURN NULL;
END IF;
CLOSE dtc_cur.app_cur;

RETURN app_record.application_code;

END get_application;
******************************************************************************/

/******************************************************************************
This function returns the current organization name. (previously get_customer_name)
******************************************************************************/
FUNCTION get_organization_name RETURN VARCHAR2 IS

org_record dtc_cur.org_cur%ROWTYPE;

BEGIN

OPEN dtc_cur.org_cur(dtc_global.organization_id);
FETCH dtc_cur.org_cur INTO org_record;
IF dtc_cur.org_cur%NOTFOUND THEN
CLOSE dtc_cur.org_cur;
RETURN NULL;
END IF;
CLOSE dtc_cur.org_cur;

RETURN org_record.org_name;

END get_organization_name;

/******************************************************************************
This function returns the program name of the id passed
******************************************************************************/
FUNCTION get_program_name (in_org_prog_id IN dtc_org_prog.org_prog_id%TYPE DEFAULT NULL)
RETURN VARCHAR2 IS

prog_record dtc_cur.prog_cur%ROWTYPE;

BEGIN

OPEN dtc_cur.prog_cur(NVL(in_org_prog_id,dtc_global.org_prog_id));
FETCH dtc_cur.prog_cur INTO prog_record;
IF dtc_cur.prog_cur%NOTFOUND THEN
CLOSE dtc_cur.prog_cur;
RETURN NULL;
END IF;
CLOSE dtc_cur.prog_cur;

RETURN prog_record.program_desc;

END get_program_name;

/******************************************************************************
This function returns the campaign name of the id passed
******************************************************************************/
FUNCTION get_campaign_name (in_org_prog_cam_id IN dtc_org_prog_cam.org_prog_cam_id%TYPE DEFAULT NULL)
RETURN VARCHAR2 IS

cam_record dtc_cur.cam_cur%ROWTYPE;

BEGIN

OPEN dtc_cur.cam_cur(NVL(in_org_prog_cam_id,dtc_global.org_prog_cam_id));
FETCH dtc_cur.cam_cur INTO cam_record;
IF dtc_cur.cam_cur%NOTFOUND THEN
CLOSE dtc_cur.cam_cur;
RETURN NULL;
END IF;
CLOSE dtc_cur.cam_cur;

RETURN cam_record.campaign_desc;

END get_campaign_name;

/******************************************************************************
This function sets the DTC_GLOBAL.USER_ID and returns the username
******************************************************************************/

FUNCTION assign_user_id RETURN VARCHAR2 IS

return_username VARCHAR2(30);

CURSOR get_user IS
SELECT
APP_USER_ID
, USERNAME
FROM
DTC_APP_USER
WHERE
USERNAME = NVL(dtc_global.current_user,USER);

BEGIN
OPEN get_user;
FETCH get_user INTO
dtc_global.user_id
, return_username;
IF get_user%NOTFOUND THEN
dtc_global.user_id := 0;
return_username := 'UNKNOWN';
END IF;
CLOSE get_user;

RETURN return_username;

END assign_user_id;

/******************************************************************************
This function returns the form title of the module name passed.
******************************************************************************/

FUNCTION get_module_title( in_mod_name VARCHAR2) RETURN VARCHAR2 IS

mod_title VARCHAR2(50);

CURSOR get_title (in_name IN VARCHAR2) IS
SELECT MODULE_TITLE
FROM DTC_MODULE
WHERE
MODULE_NAME = in_name;

BEGIN

OPEN get_title(in_mod_name);
FETCH get_title INTO mod_title;
CLOSE get_title;

IF mod_title IS NULL THEN
OPEN get_title('UNKNOWN');
FETCH get_title INTO mod_title;
IF get_title%NOTFOUND THEN
mod_title := 'Module Title Unknown';
END IF;
CLOSE get_title;
END IF;

RETURN mod_title;

END get_module_title;

/******************************************************************************
This function returns the current date in character format.
******************************************************************************/
FUNCTION get_char_date RETURN VARCHAR2 IS

BEGIN
RETURN TO_CHAR(SYSDATE,'MM/DD/YYYY HH24:MI');
END get_char_date;

/******************************************************************************
This procedure gathers the entered/last updated infomation for the
table and rowid passed.
******************************************************************************/
PROCEDURE get_entry_info( in_table IN VARCHAR2
, in_rowid IN VARCHAR2
, out_created OUT NUMBER
, out_create_dt OUT DATE
, out_lst_up_by OUT NUMBER
, out_lst_up_dt OUT DATE) IS

cur_id INTEGER := DBMS_SQL.OPEN_CURSOR;
sql_stmt VARCHAR2(1000);
exe_err INTEGER;

created_user NUMBER;
create_dt DATE;
last_upt_by NUMBER;
last_upt_dt DATE;

BEGIN

sql_stmt := 'SELECT '||
' CREATED_BY'||
', CREATE_DATE'||
', LAST_UPDATED_BY'||
', LAST_UPDATE_DATE '||
'FROM '||in_table||' '||
'WHERE ROWID = '''||in_rowid||'''';

DBMS_SQL.PARSE(cur_id,sql_stmt,DBMS_SQL.V7);

DBMS_SQL.DEFINE_COLUMN(cur_id,1,created_user);
DBMS_SQL.DEFINE_COLUMN(cur_id,2,create_dt);
DBMS_SQL.DEFINE_COLUMN(cur_id,3,last_upt_by);
DBMS_SQL.DEFINE_COLUMN(cur_id,4,last_upt_dt);

exe_err := DBMS_SQL.EXECUTE_AND_FETCH(cur_id,FALSE);

DBMS_SQL.COLUMN_VALUE(cur_id,1,created_user);
DBMS_SQL.COLUMN_VALUE(cur_id,2,create_dt);
DBMS_SQL.COLUMN_VALUE(cur_id,3,last_upt_by);
DBMS_SQL.COLUMN_VALUE(cur_id,4,last_upt_dt);

DBMS_SQL.CLOSE_CURSOR(cur_id);

out_created := created_user;
out_create_dt := create_dt;
out_lst_up_by := last_upt_by;
out_lst_up_dt := last_upt_dt;

EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001,SQLERRM);

END get_entry_info;

/******************************************************************************
This function returns the value of the Global package variable passed.
******************************************************************************/
FUNCTION get_global_value(in_variable IN VARCHAR2)
RETURN VARCHAR2 IS
plsql_text VARCHAR2(500);
cur_id INTEGER := DBMS_SQL.OPEN_CURSOR;
exe_err INTEGER;
BEGIN
plsql_text := 'DECLARE m_char VARCHAR2(255); m_date DATE;' ||
'BEGIN m_char := TO_CHAR('|| in_variable || '); ' ||
'BEGIN m_date := TO_DATE('||in_variable||');' ||
'DTC_GLOBAL.GLOBAL_VAR_VALUE := TO_CHAR('||in_variable||',''MM/DD/YYYY''); ' ||
'EXCEPTION WHEN OTHERS THEN DTC_GLOBAL.GLOBAL_VAR_VALUE := '||in_variable||'; END; ';

plsql_text := plsql_text ||' END;';
DBMS_SQL.PARSE(cur_id,plsql_text,dbms_sql.v7);
exe_err := DBMS_SQL.EXECUTE(cur_id);
DBMS_SQL.CLOSE_CURSOR(cur_id);

return DTC_GLOBAL.GLOBAL_VAR_VALUE;
EXCEPTION
WHEN OTHERS THEN -- Was a Character Global
BEGIN
plsql_text := ' BEGIN DTC_GLOBAL.GLOBAL_VAR_VALUE := ' || in_variable || ';' ||
' END;';
DBMS_SQL.PARSE(cur_id,plsql_text,dbms_sql.v7);
exe_err := DBMS_SQL.EXECUTE(cur_id);
DBMS_SQL.CLOSE_CURSOR(cur_id);

RETURN DTC_GLOBAL.GLOBAL_VAR_VALUE;

EXCEPTION
WHEN OTHERS THEN
RETURN '**ERROR**' || SQLERRM;
END;
END get_global_value;

/******************************************************************************
This function returns the table and column name of the table_def_col_id passed
(used in order by in forms)
******************************************************************************/
FUNCTION get_table_column_name
(in_table_def_col_id IN dtc_table_def_col.table_def_col_id%TYPE DEFAULT NULL)
RETURN VARCHAR2 IS

return_table_column_name VARCHAR2(61);

CURSOR table_column_cur IS
SELECT table_name||'.'||column_name
FROM dtc_table_def,dtc_table_def_col
WHERE dtc_table_def.table_def_id = dtc_table_def_col.table_def_id
AND dtc_table_def_col.table_def_col_id = in_table_def_col_id;

BEGIN
OPEN table_column_cur;
FETCH table_column_cur INTO return_table_column_name;
IF table_column_cur%NOTFOUND THEN
return_table_column_name := NULL;
END IF;
CLOSE table_column_cur;

RETURN return_table_column_name;

END get_table_column_name;

/******************************************************************************
This function returns the organization*program*campaign name of the ids passed
(Used in order_by in forms)
******************************************************************************/
FUNCTION get_org_prog_cam_name
(in_organization_id IN dtc_organization.organization_id%TYPE DEFAULT NULL,
in_org_prog_id IN dtc_org_prog.org_prog_id%TYPE DEFAULT NULL,
in_org_prog_cam_id IN dtc_org_prog_cam.org_prog_cam_id%TYPE DEFAULT NULL)
RETURN VARCHAR2 IS

return_org_prog_cam_name VARCHAR2(555);
v_org_name dtc_organization.org_name%type;
v_program_desc dtc_org_prog.program_desc%type;
v_campaign_desc dtc_org_prog_cam.campaign_desc%type;

CURSOR organization_cur IS
select org_name
from dtc_organization
where organization_id = in_organization_id;

CURSOR org_prog_cur IS
select program_desc
from dtc_org_prog
where org_prog_id = in_org_prog_id;

CURSOR org_prog_cam_cur IS
select campaign_desc
from dtc_org_prog_cam
where org_prog_cam_id = in_org_prog_cam_id;

BEGIN

if in_organization_id is not null then
open organization_cur;
fetch organization_cur into v_org_name;
if organization_cur%NOTFOUND then
v_org_name := NULL;
end if;
return_org_prog_cam_name := v_org_name;
close organization_cur;

if in_org_prog_id is not null then
open org_prog_cur;
fetch org_prog_cur into v_program_desc;
if org_prog_cur%NOTFOUND then
v_program_desc := NULL;
else
return_org_prog_cam_name := return_org_prog_cam_name||'*'||v_program_desc;
end if;
close org_prog_cur;

if in_org_prog_cam_id is not null then
open org_prog_cam_cur;
fetch org_prog_cam_cur into v_campaign_desc;
if org_prog_cam_cur%NOTFOUND then
v_campaign_desc := NULL;
else
return_org_prog_cam_name := return_org_prog_cam_name||'*'||v_campaign_desc;
end if;
close org_prog_cam_cur;
end if;
end if;
else
return_org_prog_cam_name := NULL;
end if;

return return_org_prog_cam_name;

END get_org_prog_cam_name;

/******************************************************************************
This function returns the table title of the table_def_id passed
(used in order by in forms)
******************************************************************************/
FUNCTION get_table_title
(in_table_def_col_id IN dtc_table_def_col.table_def_col_id%TYPE DEFAULT NULL)
RETURN VARCHAR2 IS

return_table_title dtc_table_def.title%type;

CURSOR table_cur IS
SELECT t.title
FROM dtc_table_def t, dtc_table_def_col c
WHERE c.table_def_col_id = in_table_def_col_id
and t.table_def_id = c.table_def_id;

BEGIN
OPEN table_cur;
FETCH table_cur INTO return_table_title;
IF table_cur%NOTFOUND THEN
return_table_title := NULL;
END IF;
CLOSE table_cur;

RETURN return_table_title;

END get_table_title;

/******************************************************************************
This function calculates the business (working) days between the two days
It doesn't take into account the Holidays.
******************************************************************************/
FUNCTION calc_business_days(P_start_date date,
P_end_date date)
RETURN NUMBER IS

begin_date date;
thru_date date;
weekend number;
days number;
maxweekend number;
weekday number;

BEGIN
IF P_start_date is null or P_end_date is null THEN
return(null);
ELSIF TRUNC(P_start_date) > TRUNC(P_end_date) THEN
return( -1 );
ELSE
begin_date := P_start_date;
thru_date := P_end_date;
END IF;

days := TRUNC(thru_date) - TRUNC(begin_date);
IF days = 0 THEN
return(0);
ELSE
weekday := to_char(begin_date,'D');
days := days + 0;
IF mod(days,7) = 0 THEN
weekend := TRUNC(days / 7) * 2;
ELSE
MAXWEEKEND := (TRUNC(days / 7) + 1) * 2;
IF weekday = 1 THEN
weekend := maxweekend - 1;
ELSE
IF (weekday + days) = (TRUNC(days / 7) + 1) * 7 + 1 THEN
weekend := maxweekend - 1;
ELSE
IF (weekday + days) < (trunc(days / 7) + 1) * 7 + 1 THEN
weekend := maxweekend - 2;
ELSE
weekend := maxweekend;
END IF;
END IF;
END IF;
END IF;
RETURN ((days - weekend) * SIGN(TRUNC(P_end_date) - TRUNC(P_start_date)));
END IF;
EXCEPTION
WHEN OTHERS THEN
RETURN (0);
END; -- calc_business_days

/***************************************************************************
This function is used by the Job Queue screen DTCFM560 for getting the description
of the opc_item.
******************************************************************************/

FUNCTION dtc_opc_item_desc_sort(i_opc_item_id IN NUMBER) RETURN VARCHAR2
IS
opc_item_desc VARCHAR2(255);
BEGIN
DECLARE
item_desc dtc_opc_item.opc_item_desc%TYPE;
BEGIN
SELECT opc_item_desc
INTO item_desc
FROM dtc_opc_item
WHERE opc_item_id = i_opc_item_id
AND sysdate between start_date and nvl(end_date,sysdate);

RETURN item_desc;
EXCEPTION
WHEN OTHERS THEN NULL;
RETURN ('OPC ITEM DESCRIPTION NOT FOUND');
END;
END dtc_opc_item_desc_sort;
7)Conversion Cvs to xml code
SET SERVEROUTPUT OFF;
SET SERVEROUTPUT ON;
DECLARE
v_rec_count NUMBER := 0;

--File Variables
v_file_out UTL_FILE.FILE_TYPE;
v_file_in UTL_FILE.FILE_TYPE;
l_in_path varchar2(100) := '/u02/apps/dtc/incoming_data/lc_5050/';
l_out_path varchar2(100) := '/u02/apps/dtc/outgoing_data/lc_5050/';
v_input_buffer varchar2(4000);
v_issuedate varchar2(20);
v_char clob;
v_firstag varchar2(30):='<Document_Revision_Request>';
v_lasttag varchar2(30):='</Document_Revision_Request>';

BEGIN
dbms_output.put_line('First Script Start Time ' ||TO_CHAR(SYSDATE, 'MM/DD/YYYY HH24:MI:SS'));
dtc_global.org_prog_id := 5050;

v_file_in := UTL_FILE.FOPEN(l_in_path,'VOID_MHS_PHS_20150917.csv','R');
UTL_FILE.get_LINE(v_file_in,v_input_buffer);
IF utl_file.is_open(v_file_in) then
LOOP
begin

UTL_FILE.get_LINE(v_file_in,v_input_buffer);
v_rec_count := v_rec_count + 1;

v_issuedate :=REGEXP_SUBSTR(v_input_buffer, '[^,]+', 1, 5);

select to_clob(XMLAgg(XMLElement("Document_Revision_Request",
XMLElement("Document_Revision_ID",v_rec_count),
XMLElement("Payment_Code",'CHK'),
XMLElement("Original_Recipient_Number",REGEXP_SUBSTR(v_input_buffer, '[^,]+', 1, 2)),
XMLElement("Original_Document_Date",to_char(to_date(v_issuedate,'YYYYMMDD'),'MM/DD/YYYY')),
XMLElement("Original_Document_Number",REGEXP_SUBSTR(v_input_buffer, '[^,]+', 1, 3)),
XMLElement("Original_Document_Amount",REGEXP_SUBSTR(v_input_buffer, '[^,]+', 1, 4)),
XMLElement("Revised_Document_Status",'V'),
XMLElement("Document_Status_Revision_Reason_Code",'VR9'),
XMLElement("Document_Status_Revision_Description")
))) into v_char from dual;

IF v_char is not null then
IF v_rec_count=1 then
v_char:=v_firstag||v_char;
END IF;
END IF;

IF NOT utl_file.is_open(v_file_out) then
v_file_out := utl_file.fopen(l_out_path,'VOID_MHS_PHS_20150917.xml', 'W');
END IF;
UTL_FILE.PUT_LINE(v_file_out,v_char);
EXCEPTION

WHEN NO_DATA_FOUND THEN
IF ( UTL_FILE.is_open(v_file_in) ) THEN
v_char:=null;
v_char:=v_char||v_lasttag;
UTL_FILE.PUT_LINE(v_file_out,v_char);
UTL_FILE.fclose(v_file_in);
END IF;
IF ( UTL_FILE.is_open(v_file_out) ) THEN
UTL_FILE.fclose(v_file_out);
END IF;
EXIT;

end;
END LOOP;
END IF;
dbms_output.put_line('No of records converted:' ||v_rec_count);

UTL_FILE.FCLOSE(v_file_in);
UTL_FILE.FCLOSE(v_file_out);

dbms_output.put_line('First Script End Time ' ||TO_CHAR(SYSDATE, 'MM/DD/YYYY HH24:MI:SS'));
dbms_output.put_line('Second Script Start Time ' ||TO_CHAR(SYSDATE, 'MM/DD/YYYY HH24:MI:SS'));
v_rec_count:=0;

v_file_in := UTL_FILE.FOPEN(l_in_path,'VOID_MHS_FRS_20150917.csv','R');
UTL_FILE.get_LINE(v_file_in,v_input_buffer);
IF utl_file.is_open(v_file_in) then
LOOP
begin

UTL_FILE.get_LINE(v_file_in,v_input_buffer);
v_rec_count := v_rec_count + 1;

v_issuedate :=REGEXP_SUBSTR(v_input_buffer, '[^,]+', 1, 5);

select to_clob(XMLAgg(XMLElement("Document_Revision_Request",
XMLElement("Document_Revision_ID",v_rec_count),
XMLElement("Payment_Code",'CHK'),
XMLElement("Original_Recipient_Number",REGEXP_SUBSTR(v_input_buffer, '[^,]+', 1, 2)),
XMLElement("Original_Document_Date",to_char(to_date(v_issuedate,'YYYYMMDD'),'MM/DD/YYYY')),
XMLElement("Original_Document_Number",REGEXP_SUBSTR(v_input_buffer, '[^,]+', 1, 3)),
XMLElement("Original_Document_Amount",REGEXP_SUBSTR(v_input_buffer, '[^,]+', 1, 4)),
XMLElement("Revised_Document_Status",'V'),
XMLElement("Document_Status_Revision_Reason_Code",'VR9'),
XMLElement("Document_Status_Revision_Description")
))) into v_char from dual;

IF v_char is not null then
IF v_rec_count=1 then
v_char:=v_firstag||v_char;
END IF;
END IF;

IF NOT utl_file.is_open(v_file_out) then
v_file_out := utl_file.fopen(l_out_path,'VOID_MHS_FRS_20150917.xml', 'W');
END IF;
UTL_FILE.PUT_LINE(v_file_out,v_char);
EXCEPTION

WHEN NO_DATA_FOUND THEN
IF ( UTL_FILE.is_open(v_file_in) ) THEN
v_char:=null;
v_char:=v_char||v_lasttag;
UTL_FILE.PUT_LINE(v_file_out,v_char);
UTL_FILE.fclose(v_file_in);
END IF;
IF ( UTL_FILE.is_open(v_file_out) ) THEN
UTL_FILE.fclose(v_file_out);
END IF;
EXIT;

end;
END LOOP;
END IF;
dbms_output.put_line('No of records converted:' ||v_rec_count);

UTL_FILE.FCLOSE(v_file_in);
UTL_FILE.FCLOSE(v_file_out);

dbms_output.put_line('Second Script End Time ' ||TO_CHAR(SYSDATE, 'MM/DD/YYYY HH24:MI:SS'));
EXCEPTION
WHEN OTHERS THEN
IF ( UTL_FILE.is_open(v_file_in) ) THEN
UTL_FILE.fclose(v_file_in);
dbms_output.put_line( 'file_in'||SQLERRM);
END IF;
RAISE_APPLICATION_ERROR(-20202, 'Error: ' || SQLERRM);
dbms_output.put_line(SQLERRM);
END;

/

7)Write to txt file

SET SERVEROUTPUT OFF;
SET SERVEROUTPUT ON;
DECLARE
v_rec_count NUMBER := 0;
v_prog_recp_cnt NUMBER:=0;
v_terminate_cnt NUMBER:=0;
v_call_cnt NUMBER:=0;
v_class_cnt NUMBER:=0;
v_out_msg varchar2(4000);
v_term_evnt_exp EXCEPTION;

--File Variables
v_file UTL_FILE.FILE_TYPE;
l_dir_path varchar2(100) := '/u02/apps/dtc/outgoing_data/lc_5050/';

cursor cur_pat is
select opcr.org_prog_cam_recipient_id opcr_id,opcr.recipient_pk_id,
dtc_report_util.get_attr_usage_val(8466,opcr.org_prog_cam_recipient_id)coachid ,
email.comm_value email,
opcr.create_date create_date
from dtc_org_prog_cam_recipient opcr, dtc_recipient_comm_value email
where opcr.recipient_pk_id=email.recipient_pk_id
and opcr.org_prog_cam_id=7103
and opcr.end_date is null
and email.comm_type_id=76
and (email.comm_value like '%VOXIVA%' or email.comm_value like '%TEST%')
and email.end_date is null
;
CURSOR get_event_chk(in_opcr_id number)
IS
SELECT opcr_event_id
FROM dtc_opcr_event
WHERE org_prog_cam_recipient_id = in_opcr_id
AND end_date IS NULL
AND current_status <> 'CLOSED TERMINATED';
v_opcr_event NUMBER(10);
BEGIN
dbms_output.put_line('DML Script Start Time ' ||TO_CHAR(SYSDATE, 'MM/DD/YYYY HH24:MI:SS'));
dtc_global.org_prog_id := 5050;
FOR rec in cur_pat
LOOP
IF NOT utl_file.is_open(v_file) then
v_file := utl_file.fopen(l_dir_path,'dtc_pmo_5400_out_file.csv', 'W');
END IF;
v_rec_count := v_rec_count + 1;
--(1) PART Please create an output file with the following columns so we can send to the
IF v_rec_count=1 then
utl_file.put_line(v_file,'COACH ID'||','||'EMAIL'||','||'MEMBER CREATE DATE');
END IF;
utl_file.put_line(v_file,rec.coachid||','||rec.email||','||TO_CHAR(rec.create_date,'MM/DD/YYYY'));
--(2)PART For these recipients we need do the following:
--dbms_output.put_line(rec.opcr_id);
Update dtc_org_prog_cam_recipient
set end_date=trunc(sysdate)
where org_prog_cam_recipient_id=rec.opcr_id
and org_prog_cam_id=7103;
v_prog_recp_cnt:=v_prog_recp_cnt+sql%rowcount;
v_opcr_event:=null;
OPEN get_event_chk(rec.opcr_id);
LOOP
FETCH get_event_chk into v_opcr_event;
exit when get_event_chk%notfound;
dtc_dupe_process_pkg.terminate_event(v_opcr_event, v_out_msg, 'TERMINATED AS PER PMO-5400');
v_terminate_cnt:=v_terminate_cnt+1;
END LOOP;
CLOSE get_event_chk;
IF v_out_msg<>'0' THEN
RAISE v_term_evnt_exp;
END IF;
UPDATE dtc_opcret_call_attempt
SET end_date=sysdate
WHERE org_prog_cam_recipient_id=rec.opcr_id;

v_call_cnt:=v_call_cnt+sql%rowcount;

--Class Term and new class creation:
dtc_lc_5077_util_pkg.end_dt_cur_cls_and_ins_class(8283,rec.opcr_id);

v_class_cnt:=v_class_cnt+sql%rowcount;

END LOOP;
UTL_FILE.FCLOSE(v_file);
DBMS_OUTPUT.PUT_LINE('No of records Updated Prog_cam_recipient:'||v_prog_recp_cnt );
DBMS_OUTPUT.PUT_LINE('No of records Updated dtc_opcr_event:'||v_terminate_cnt );
DBMS_OUTPUT.PUT_LINE('No of records Updated dtc_opcret_call_attempt:'||v_call_cnt );
DBMS_OUTPUT.PUT_LINE('No of records Updated dtc_opcr_class:'||v_class_cnt );
dbms_output.put_line('DML Script End Time ' ||TO_CHAR(SYSDATE, 'MM/DD/YYYY HH24:MI:SS'));
commit;
EXCEPTION
WHEN v_term_evnt_exp THEN
RAISE_APPLICATION_ERROR(-20012,SQLERRM);
WHEN OTHERS THEN
IF ( UTL_FILE.is_open(v_file) ) THEN
UTL_FILE.fclose(v_file);
END IF;
RAISE_APPLICATION_ERROR(-20202, 'Error: ' || SQLERRM);
END;
/

Generating txt for to .csv files

declare
Procedure upd_card_type ( p_file_name IN VARCHAR2, p_card_type IN varchar2 ) IS
f1 UTL_FILE.FILE_TYPE;
f2 UTL_FILE.FILE_TYPE;
f3 UTL_FILE.FILE_TYPE;
v_file_name1 varchar2(100) ;
v_file_name2 varchar2(100) ;
v_file_name3 varchar2(100) ;
v_file_dir varchar2(300) := '/u02/apps/dtc/incoming_data/lc_5050/';
v_record varchar2(500);
v_rownum number := 0 ;
v_cnt number;
v_random_no dtc_opc_random_no.RANDOM_NO%type ;
begin
v_cnt:=0;
DBMS_OUTPUT.put_line ('DML Script Start Time ' || TO_CHAR (SYSDATE, 'MM/DD/YYYY HH24:MI:SS'));
v_file_name1 := p_file_name ;
v_file_name2 := replace(v_file_name1, '.txt', '_reject.csv');
v_file_name3 := replace(v_file_name1, '.txt', '_log.csv');
f1 := UTL_FILE.FOPEN(v_file_dir, v_file_name1 , 'r', 4000);
f2 := UTL_FILE.FOPEN(v_file_dir, v_file_name2 , 'w', 4000);
f3 := UTL_FILE.FOPEN(v_file_dir, v_file_name3 , 'w', 4000);
dtc_global.org_prog_id := 5050 ;
LOOP
BEGIN
v_record := null ;
utl_file.get_line(f1,v_record);

v_random_no := null ;
if substr(v_record, 1, 4) = 'TRLR' then
EXIT ;
END IF;
v_random_no := substr(v_record, 1, 9);

for rec IN(select count(1) cnt from dtc_opc_random_no
WHERE random_no =v_random_no
AND attribute01='IN OFFICE'
AND org_prog_cam_id=6967)
LOOP
v_cnt:=v_cnt+rec.cnt;
END LOOP;

if SQL%ROWCOUNT = 1 then
v_rownum := v_rownum + 1 ;
else
utl_file.put_line(f2, v_random_no || ','||p_card_type ||', Rows updated = '||SQL%ROWCOUNT);
end if;

if mod(v_rownum, 5000) = 0 then
commit;
utl_file.put_line(f3, 'Record processed = '||v_rownum );
end if;
EXCEPTION
WHEN no_data_found THEN
EXIT ;
END ;
END LOOP;
DBMS_OUTPUT.put_line('No Of records CNT:'|| v_cnt);
DBMS_OUTPUT.put_line('No Of Update records:'||v_rownum);
DBMS_OUTPUT.put_line('DML Script End Time ' || TO_CHAR (SYSDATE, 'MM/DD/YYYY HH24:MI:SS'));
utl_file.fclose(f1);
utl_file.fclose(f2);
utl_file.fclose(f3);
commit;
end;
begin
upd_card_type('IDs_sq9416_RESPIMAT6967LS_grp6967_cnt117335_typePATIENTSTARTERKIT_10152014.txt', 'PATIENT STARTER KIT' );
end ;
/

Knowledge

Tuesday, June 30, 2015

Generic function and procedures

No comments: