Executing sql from PL/SQL

This is a useful utility. It just uses the execute immediate command in PL/SQL but I've wrapped it up a bit for variable substitution, and overloaded the package call so you can use it with zero to five arguments (or more if you added to the code). Also a useful display error utility -

create or replace package iri_util as

procedure execute_sql(p_sql in varchar2,p_contflag in varchar2,p_arg1 in varchar2,
p_arg2 in varchar2,p_arg3 in varchar2,p_arg4 in varchar2,p_arg5 in varchar2);

procedure execute_sql(p_sql in varchar2,p_contflag in varchar2,p_arg1 in varchar2,
p_arg2 in varchar2,p_arg3 in varchar2,p_arg4 in varchar2);

procedure execute_sql(p_sql in varchar2,p_contflag in varchar2,p_arg1 in varchar2,
p_arg2 in varchar2,p_arg3 in varchar2);

procedure execute_sql(p_sql in varchar2,p_contflag in varchar2,p_arg1 in varchar2,
p_arg2 in varchar2);

procedure execute_sql(p_sql in varchar2,p_contflag in varchar2,p_arg1 in varchar2);

procedure execute_sql(p_sql in varchar2,p_contflag in varchar2);

procedure display_error(p_errmess in varchar2,p_errtype in varchar2,p_errcode in varchar2);

end iri_util;


create or replace package body iri_util is

procedure execute_sql(p_sql in varchar2,p_contflag in varchar2,p_arg1 in varchar2,p_arg2 in varchar2,p_arg3 in varchar2,p_arg4 in varchar2,p_arg5 in varchar2) is

v_sql varchar2(5000);

begin

v_sql := p_sql;
v_sql := replace(v_sql,'%1',p_arg1);
v_sql := replace(v_sql,'%2',p_arg2);
v_sql := replace(v_sql,'%3',p_arg3);
v_sql := replace(v_sql,'%4',p_arg4);
v_sql := replace(v_sql,'%5',p_arg5);

if p_contflag != 'T' then
execute immediate v_sql;
end if;

exception

when others then
if p_contflag in ('C','Y') then
null;
else
raise;
end if;

end;

procedure execute_sql(p_sql in varchar2,p_contflag in varchar2,p_arg1 in varchar2,p_arg2 in varchar2,p_arg3 in varchar2,p_arg4 in varchar2) is
begin

execute_sql(p_sql,p_contflag,p_arg1,p_arg2,p_arg3,p_arg4,null);

end;

procedure execute_sql(p_sql in varchar2,p_contflag in varchar2,p_arg1 in varchar2,p_arg2 in varchar2,p_arg3 in varchar2) is
begin

execute_sql(p_sql,p_contflag,p_arg1,p_arg2,p_arg3,null,null);

end;


procedure execute_sql(p_sql in varchar2,p_contflag in varchar2,p_arg1 in varchar2,p_arg2 in varchar2) is
begin

execute_sql(p_sql,p_contflag,p_arg1,p_arg2,null,null,null);

end;


procedure execute_sql(p_sql in varchar2,p_contflag in varchar2,p_arg1 in varchar2) is
begin

execute_sql(p_sql,p_contflag,p_arg1,null,null,null,null);

end;


procedure execute_sql(p_sql in varchar2,p_contflag in varchar2) is
begin

execute_sql(p_sql,p_contflag,null,null,null,null,null);

end;

procedure display_error(p_errmess in varchar2,p_errtype in varchar2,p_errcode in varchar2) is

begin

IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
FND_MESSAGE.Set_Name(' + appShortName +', p_errmess);
FND_MESSAGE.Set_Token(p_errtype, p_errcode, FALSE);
FND_MSG_PUB.ADD;
END IF;
RAISE FND_API.G_EXC_ERROR;

end;

end iri_util;

Revenue Recognition

Often Ignored, one of the most important aspects of AR, especially in a Sarbanes-Oxley environment. Most companies I've been to don't use RevRec effectively, mostly because they don't understand (i.e. haven't been properly informed) about the full range of functionality.

Latest functionality that actually makes RevRec very useful is Daily RevRec. That is, you can use a rule that allocates revenue by the number of days in a month, either for the entire life of the Revenue (Full Daily RevRec), or just for partial months at the start and end, with the full month revenue smoothed out (Partial RevRec).

One thing to note - you can't seem to use the Daily RevRec rules in OM. Investigating that now.

Oracle Alerts Plain Text

This one has bugged me for ages. I think that if you set your email preferences to be 'plain text' then any Alerts you send out (in Oracle Alerts) which are actioned by you, will be sent using this plain text format. That way you can get nicely formatted emails instead of all jumbled up.