CoreApiHtml.sql -3<-- (INVItemCt115h.sql ) Note: 223702.1

CoreApiHtml.sql

-- Function Name: Display_Table

-- Usage:

-- a_number := Display_Table('Table Name', 'Heading', 'Where Clause', 'Order By', 'Long Flag');

-- Parameters:

-- Table Name - Any Valid Table or View

-- Heading - Text String to for heading the output

-- Where Clause - Where clause to apply to the table dump

-- Order By - Order By clause to apply to the table dump

-- Long Flag - 'Y' or 'N' - If set to 'N' then this will not output any LONG columns

-- Output:

-- Displays the output of the 'select * from table' as an HTML table.

-- Returns:

-- Number of rows displayed.

-- Examples:

-- declare

-- num_rows number;

-- begin

-- num_rows := Display_Table('AR_SYSTEM_PARAMETERS_ALL', 'AR Parameters', 'Where Org_id != -3113'

-- , 'order by org_id, set_of_books_id', 'N');

-- end;

function Display_Table (p_table_name varchar2,

p_table_alias varchar2,

p_where_clause varchar2,

p_order_by_clause varchar2defaultnull,

p_display_longs varchar2default'Y')returnnumberis

begin

return(Display_SQL ('select * from '||

replace(upper(p_table_name),'V_$','V$')||chr(10)|| p_where_clause ||

chr(10)||nvl(p_order_by_clause,'order by 1')

,nvl(p_table_alias, p_table_name)

, p_display_longs));

end Display_Table;

-- Function Name: Get_DB_Apps_Version

-- Usage:

-- a_varchar := Get_DB_Apps_Version;

-- Returns:

-- The version of applications from fnd_product_groups

-- Also sets the variable g_appl_version to '10.7','11.0', or '11.5'

-- as appropriate.

-- Examples:

-- declare

-- apps_ver varchar2(20);

-- begin

-- apps_ver := Get_DB_Apps_Version;

-- end;

function Get_DB_Apps_Version returnvarchar2is

l_appsver fnd_product_groups.release_name%type:=null;

begin

select release_name into l_appsver from fnd_product_groups;

g_appl_version :=substr(l_appsver,1,4);

return(l_appsver);

end;

-- Procedure Name: Init_Block

-- Usage:

-- Init_Block;

-- Parameters:

-- None

-- Output:

-- No output. Simple initializes the output CLOB for use in

-- multi-block scripts.

-- Examples:

-- begin

-- Init_Block;

-- end;

procedure Init_Block is

l_instance_name varchar2(16):=null;

l_host_name varchar2(64):=null;

l_version varchar2(17):=null;

begin

DBMS_LOB.CREATETEMPORARY(:g_hold_output,TRUE,DBMS_LOB.SESSION);

line_out('-->');

Insert_Style_Sheet;

line_out('</head><body>');

end Init_Block;

-- Procedure Name: End_Block

-- Usage:

-- End_Block;

-- Output:

-- No output. Adds the closing body and html tags to end a pl/sql

-- block without calling Show_Footer (for use in multi-block scripts).

-- Examples:

-- begin

-- End_Block;

-- end;

procedure End_Block is

begin

line_out('</body></html>');

end End_Block;

-- Procedure Name: Show_Header

-- Usage:

-- Show_Header('Note Number', 'Title');

-- Parameters:

-- Note Number - Any Valid Metalink Note Number

-- Title - Text string to go beside the note link

-- Output:

-- Displays Standard Header Information

-- Examples:

-- begin

-- Show_Header('139684.1', 'Oracle Applications Current Patchsets Comparison to applptch.txt');

-- end;

procedure Show_Header(p_note varchar2, p_title varchar2)is

l_instance_name varchar2(16):=null;

l_host_name varchar2(64):=null;

l_version varchar2(17):=null;

begin

DBMS_LOB.CREATETEMPORARY(:g_hold_output,TRUE,DBMS_LOB.SESSION);

select instance_name

, host_name

,version

into l_instance_name

, l_host_name

, l_version

from v$instance;

line_out('-->');

Insert_Style_Sheet;

line_out('</head><body>');

line_out('<table cellspacing=0 summary="Header Table to start off Script"><tr>');

line_out('<th class=rowh align=LEFT id="note" nowrap>Note</th>');

line_out('<td align=left headers="note" nowrap><a href=http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id='|| p_note ||' target=new>'|| p_note ||'</a><a href=http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id='|| p_note ||'target=new></a>'|| p_title ||'</td>');

line_out('</tr><tr>');

line_out('<th class=rowh align=left id="machine" nowrap>Machine</th>');

line_out('<td align=left headers="machine" nowrap>'|| l_host_name ||'</td>');

line_out('</tr><tr>');

line_out('<th class=rowh align=left id="date" nowrap>Date Run</th>');

line_out('<td align=left headers="date" nowrap>'||to_char(sysdate,'DD-MON-YYYY HH24:MI')||'</td>');

line_out('</tr><tr>');

line_out('<th class=rowh align=left id="info" nowrap>Oracle Info</th>');

line_out('<td align=left headers="info" nowrap>SID: '|| l_instance_name ||' Version: '|| l_version ||'</td>');

line_out('</tr><tr>');

line_out('<th class=rowh align=left id="appver" nowrap>Apps Version</th>');

line_out('<td align=left headers="appver" nowrap>'|| Get_DB_Apps_Version ||'</td>');

line_out('</tr></table> ');

end Show_Header;

-- Procedure Name: Show_Footer

-- Usage:

-- Show_Footer('Script Name','Header');

-- Output:

-- Displays Standard Footer

-- Examples:

-- begin

-- Show_Footer('AR Setup Script',

-- '$Header: ARTrxInfo.sql 1.0 01/12/11 12:33:24 support $');

-- end;

procedure Show_Footer(p_script_name varchar2, p_header varchar2)is

begin

Tab0Print(' Please provide <a href="'||

'mailto:support-diagnostics_ww@oracle.com?Subject=Standalone '||

'Diagnostic feedback for '|| p_script_name ||'&AMPER.BODY='||

p_script_name ||' - '|| p_header ||'">feedback</a> regarding '||

'the usefulness of this test and/or tool.');

Tab0Print('We appreciate your feedback, however, there will be no '||

'replies to feedback emails.');

Tab0Print('For support issues, please log an iTar (Service Request).');

line_out('</body></html>');

end Show_Footer;

-- Procedure Name: Show_Link

-- Usage:

-- Show_Link('Note #');

-- Output:

-- Displays A link to a Metalink Note

-- Examples:

-- begin

-- Show_Link('139684.1');

-- end;

procedure Show_Link(p_note varchar2)is

begin

line_out('Click to see Note: <a href=http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=' || p_note ||'>'|| p_note ||'</a>');

end Show_Link;

-- Procedure Name: Show_Link

-- Usage:

-- Show_Link('URL', 'Name');

-- Output:

-- Displays A link to a URL using the Name Parameter

-- Examples:

-- begin

-- Show_Link('http://metalink.us.oracle.com', 'Metalink');

-- end;

procedure Show_Link(p_link varchar2, p_link_name varchar2)is

begin

line_out('<a href=' || p_link ||'>'|| p_link_name ||'</a>');

end Show_Link;

-- Procedure Name: Send_Email

-- Usage:

-- Send_Email('Sender', 'Recipient', 'Subject', 'Message', 'SMTP Host');

-- Output:

-- Sends E-mail - No screen output.

-- Examples:

-- begin

-- Send_Email ('somebody@oracle.com','tosomebody@oracle.com','this is a subject', 'this is a body','gmsmtp01.oraclecorp.com');

-- end;

procedure Send_Email ( p_sender varchar2

, p_recipient varchar2

, p_subject varchar2

, p_message varchar2

, p_mailhost varchar2)is

l_mail_conn utl_smtp.connection;

begin

l_mail_conn := utl_smtp.open_connection(p_mailhost,25);

utl_smtp.helo(l_mail_conn, p_mailhost);

utl_smtp.mail(l_mail_conn, p_sender);

utl_smtp.rcpt(l_mail_conn, p_recipient);

utl_smtp.open_data(l_mail_conn);

if p_subject isnotnullthen

utl_smtp.write_data(l_mail_conn,'Subject: '|| p_subject || utl_tcp.CRLF);

endif;

utl_smtp.write_data(l_mail_conn, utl_tcp.CRLF || p_mailhost);

utl_smtp.close_data(l_mail_conn);

utl_smtp.quit(l_mail_conn);

exception

whenothersthen

utl_smtp.quit(l_mail_conn);

ErrorPrint(' Error in Sending Mail'||sqlerrm);

end Send_Email;

-- Function Name: Get_Package_Version

-- Usage:

-- a_varchar := Get_Package_Version ('Object Type', 'Schema', 'Package Name');

-- Returns:

-- The version of the package or spec or null if the package does not

-- exist

-- Examples:

-- declare

-- spec_ver varchar2(20);

-- body_ver varchar2(20);

-- begin

-- spec_ver := Get_Package_Version('PACKAGE','APPS','ARH_ADDR_PKG');

-- body_ver := Get_Package_Version('PACKAGE BODY','APPS','ARH_ADDR_PKG');

-- end;

function Get_Package_Version (p_type varchar2, p_schema varchar2, p_package varchar2)returnvarchar2is

hold_version varchar2(50);

begin

selectsubstr(text,instr(text,'$Header')+10,40)

into hold_version

from all_source

wherename= p_package

andtype= p_type

and owner = p_schema

and text like'%$Header%'

andrownum=1;

hold_version :=substr(hold_version,instr(hold_version,' ')+1,50);

hold_version :=substr(hold_version,1,instr(hold_version,' ')-1);

return(hold_version);

exception

whenno_data_foundthen

ErrorPrint(p_type||' '||p_package||' owned by '||p_schema||

' does not exist');

ActionErrorPrint('Verify that this object is valid for your version of '||

'applications and that the owner indicated is correct');

return(null);

whenothersthen

ErrorPrint(sqlerrm||' occured in Get_Package_Version');

ActionErrorPrint('Please provide this information to your support '||

'representative');

raise;

end Get_Package_Version;

-- Function Name: Get_Package_Spec

-- Usage:

-- a_varchar := Get_Package_Spec('Package Name');

-- Returns:

-- The version of the package specification in the APPS schema

-- Examples:

-- declare

-- spec_ver varchar2(20);

-- begin

-- spec_ver := Get_Package_Spec('ARH_ADDR_PKG');

-- end;

function Get_Package_Spec(p_package varchar2)returnvarchar2is

begin

return Get_Package_Version('PACKAGE','APPS',p_package);

end Get_Package_Spec;

-- Function Name: Get_Package_Body

-- Usage:

-- a_varchar := Get_Package_Body('Package Name');

-- Returns:

-- The version of the package body in the APPS schema

-- Examples:

-- declare

-- body_ver varchar2(20);

-- begin

-- body_ver := Get_Package_Body('ARH_ADDR_PKG');

-- end;

function Get_Package_Body(p_package varchar2)returnvarchar2is

begin

return Get_Package_Version('PACKAGE BODY','APPS',p_package);

end Get_Package_Body;

-- Procedure Name: Display_Profiles

-- Usage:

-- Display_Profiles(application id, 'profile short name');

-- Output:

-- Displays all Profile settings for the application or profile

-- in an HTML table

-- Examples:

-- begin

-- Display_Profiles(222,null);

-- Display_Profiles(null, 'AR_ALLOW_OVERAPPLICATION_IN_LOCKBOX');

-- end;

procedure Display_Profiles (p_application_id varchar2

, p_short_name varchar2defaultnull)is

begin

Run_SQL('Profile Options',

'select b.user_profile_option_name "Long Name"'

||' , a.profile_option_name "Short Name"'

||' , decode(to_char(c.level_id),''10001'',''Site'''

||' ,''10002'',''Application'''

||' ,''10003'',''Responsibility'''

||' ,''10004'',''User'''

||' ,''Unknown'') "Level"'

||' , decode(to_char(c.level_id),''10001'',''Site'''

||' ,''10002'',nvl(h.application_short_name,to_char(c.level_value))'

||' ,''10003'',nvl(g.responsibility_name,to_char(c.level_value))'

||' ,''10004'',nvl(e.user_name,to_char(c.level_value))'

||' ,''Unknown'') "Level Value"'

||' , c.PROFILE_OPTION_VALUE "Profile Value"'

||' , c.profile_option_id "Profile ID"'

||' , to_char(c.LAST_UPDATE_DATE,''DD-MON-YYYY HH24:MI'') '

||' "Updated Date"'

||' , nvl(d.user_name,to_char(c.last_updated_by)) "Updated By"'

||' from fnd_profile_options a'

||' , FND_PROFILE_OPTIONS_VL b'

||' , FND_PROFILE_OPTION_VALUES c'

||' , FND_USER d'

||' , FND_USER e'

||' , FND_RESPONSIBILITY_VL g'

||' , FND_APPLICATION h'

||' where a.application_id = nvl('||nvl(p_application_id,'null')

||' , a.application_id)'

||' and a.profile_option_name = nvl('''|| p_short_name

||''' , a.profile_option_name)'

||' and a.profile_option_name = b.profile_option_name'

||' and a.profile_option_id = c.profile_option_id'

||' and a.application_id = c.application_id'

||' and c.last_updated_by = d.user_id (+)'

||' and c.level_value = e.user_id (+)'

||' and c.level_value = g.responsibility_id (+)'

||' and c.level_value = h.application_id (+)'

||' order by b.user_profile_option_name, c.level_id, '

||' decode(to_char(c.level_id),''10001'',''Site'''

||' ,''10002'',nvl(h.application_short_name,to_char(c.level_value))'

||' ,''10003'',nvl(g.responsibility_name,to_char(c.level_value))'

||' ,''10004'',nvl(e.user_name,to_char(c.level_value))'

||' ,''Unknown'')');

end;

-- Procedure Name: Get_Profile_Option

-- Usage:

-- a_varchar := Get_Profile_Option('Short Name');

-- Parameters:

-- Short Name - The Short Name of the Profile Option

-- Returns:

-- The value of the profile option based on the user.

-- If Set_Client has not been run successfully then

-- it will return the site level.

-- Output:

-- None

-- Examples:

-- declare

-- prof_value varchar2(150);

-- begin

-- prof_value := Get_Profile_Option('AR_ALLOW_OVERAPPLICATION_IN_LOCKBOX')

-- end;

function Get_Profile_Option (p_profile_option varchar2)returnvarchar2is

begin

returnFND_PROFILE.VALUE(p_profile_option);

end;

-- Procedure Name: Set_Org

-- Usage:

-- Set_Org(org_id);

-- Parameters:

-- Org_ID - The id of the organization to set.

-- Output:

-- None

-- Examples:

-- begin

-- Set_Org(204);

-- end;

procedure Set_Org (p_org_id number)is

begin

fnd_client_info.set_org_context(p_org_id);

end Set_Org;

-- Procedure Name: Set_Client

-- Description:

-- Validates user_name, responsibility_id, and application_id parameters

-- If valid it initializes the session (which results in the operating

-- unit being set for the session as well. Also sets the global variables

-- g_user_id, g_resp_id, g_appl_id, and g_org_id which can then be used

-- throughout the script.

-- Usage:

-- Set_Client(UserName, Responsibility_ID);

-- Set_Client(UserName, Responsibility_ID, Application_ID);

-- Set_Client(UserName, Responsibility_ID, Application_ID, SecurityGrp_ID);

-- Parameters:

-- UserName - The Name of the Applications User

-- Responsibility_ID - Any Valid Responsibility ID

-- Application_ID - Any Valid Application ID (275=PA) If no value

-- provided, attempt to obtain from responsibility_id

-- SecurityGrp_ID - A valid security_group_id

-- Examples:

-- begin

-- Set_Client('JOEUSER',50719, 222);

-- end;

procedure Set_Client(p_user_name varchar2, p_resp_id number,

p_app_id number, p_sec_grp_id number)is

l_cursor integer;

l_num_rows integer;

l_user_name fnd_user.user_name%type;

l_user_id number;

l_app_id number;

l_counter integer;

l_appl_vers fnd_product_groups.release_name%type;

sqltxt varchar2(2000);

inv_user exception;

inv_resp exception;

inv_app exception;

no_app exception;

begin

l_user_name :=upper(p_user_name);

begin

select user_id into l_user_id

from fnd_user where user_name = l_user_name;

exception

whenothersthen

raise inv_user;

end;

l_appl_vers := get_db_apps_version;-- sets g_appl_version

if g_appl_version ='11.0'or g_appl_version ='10.7'then

sqltxt :='select rg.application_id '||

'from fnd_user_responsibility rg '||

'where rg.responsibility_id = '||to_char(p_resp_id)||' '||

'and rg.user_id = '||to_char(l_user_id);

elsif g_appl_version ='11.5'then

sqltxt :='select rg.responsibility_application_id '||

'from fnd_user_resp_groups rg '||

'where rg.responsibility_id = '||to_char(p_resp_id)||' '||

'and rg.user_id = '||to_char(l_user_id);

endif;

begin

l_cursor := dbms_sql.open_cursor;

dbms_sql.parse(l_cursor, sqltxt, dbms_sql.native);

dbms_sql.define_column(l_cursor,1, l_app_id);

l_num_rows := dbms_sql.execute_and_fetch(l_cursor,TRUE);

dbms_sql.column_value(l_cursor,1, l_app_id);

dbms_sql.close_cursor(l_cursor);

exception

whenno_data_foundthen

raise inv_resp;

whentoo_many_rowsthen

if p_app_id isnullthen

raise no_app;

else

dbms_sql.close_cursor(l_cursor);

l_cursor := dbms_sql.open_cursor;

dbms_sql.parse(l_cursor, sqltxt, dbms_sql.native);

dbms_sql.define_column(l_cursor,1, l_app_id);

l_num_rows := dbms_sql.execute(l_cursor);

while dbms_sql.fetch_rows(l_cursor)>0loop

dbms_sql.column_value(l_cursor,1, l_app_id);

if l_app_id = p_app_id then

exit;

endif;

endloop;

dbms_sql.close_cursor(l_cursor);

if l_app_id != p_app_id then

raise inv_app;

endif;

end;

l_cursor := dbms_sql.open_cursor;

if g_appl_version ='11.5'then

sqltxt :='begin '||

'fnd_global.apps_initialize(:user, :resp, '||

':appl, :secg); '||

'end; ';

dbms_sql.parse(l_cursor, sqltxt, dbms_sql.native);

dbms_sql.bind_variable(l_cursor,':user',l_user_id);

dbms_sql.bind_variable(l_cursor,':resp',p_resp_id);

dbms_sql.bind_variable(l_cursor,':appl',l_app_id);

dbms_sql.bind_variable(l_cursor,':secg',p_sec_grp_id);

else

sqltxt :='begin '||

'fnd_global.apps_initialize(:user,:resp,:appl); '||

'end; ';

dbms_sql.parse(l_cursor, sqltxt, dbms_sql.native);

dbms_sql.bind_variable(l_cursor,':user',l_user_id);

dbms_sql.bind_variable(l_cursor,':resp',p_resp_id);

dbms_sql.bind_variable(l_cursor,':appl',l_app_id);

endif;

l_num_rows := dbms_sql.execute(l_cursor);

g_user_id := l_user_id;

g_resp_id := p_resp_id;

g_appl_id := l_app_id;

g_org_id := Get_Profile_Option('ORG_ID');

exception

when inv_user then

ErrorPrint('Unable to initialize client due to invalid username: '||

l_user_name);

ActionErrorPrint('Set_Client has been passed an invalid username '||

'parameter. Please correct this parameter if possible, and if not, '||

'inform your support representative.');

raise;

when inv_resp then

ErrorPrint('Unable to initialize client due to invalid responsibility '||

'ID: '||to_char(p_resp_id));

ActionErrorPrint('Set_Client has been passed an invalid responsibility '||

'ID parameter. This responsibility_id either does not exist or has not '||

'been assigned to the user ('||l_user_name||'). Please correct these '||

'parameter values if possible, and if not inform your support '||

'representative.');

raise;

when inv_app then

ErrorPrint('Unable to initialize client due to invalid application ID: '||

to_char(p_app_id));

ActionErrorPrint('Set_Client has been passed an invalid application ID '||

'parameter. This application either does not exist or is not '||

'associated with the responsibility id ('||to_char(p_resp_id)||'). '||

'Please correct this parameter value if possible, and if not inform '||

'your support representative.');

raise;

when no_app then

ErrorPrint('Set_Client was unable to obtain an application ID to '||

'initialize client settings');

ActionErrorPrint('No application_id was supplied and Set_Client was '||

'unable to determine this from the responsibility because multiple '||

'responsibilities with the same responsibility_id have been assigned '||

'to this user ('||l_user_name||').');

raise;

whenothersthen

ErrorPrint(sqlerrm||' occured in Set_Client');

ActionErrorPrint('Please inform your support representative');

raise;

end Set_Client;

procedure Set_Client(p_user_name varchar2, p_resp_id number)is

begin

Set_Client(p_user_name, p_resp_id,null,null);

end Set_Client;

procedure Set_Client(p_user_name varchar2, p_resp_id number,

p_app_id number)is

begin

Set_Client(p_user_name, p_resp_id, p_app_id,null);

end Set_Client;

-- Procedure Name: Get_DB_Patch_List

-- Usage:

-- a_string := Get_DB_Patch_List('heading', 'short name', 'bug number', 'start date');

-- Parameters:

-- Heading = Title to go at the top of TABLE or TEXT outputs

-- Short Name = Limits to Bugs that match this expression for the Applications Production Short Name (LIKE)

-- Bug Number = Limits to bugs that match this expression (LIKE)

-- Start Date = Limits to Bugs created after this date

-- Output:

-- An HTML table of patches applied for the application since the date

-- indicated is displayed.

-- Examples:

-- begin

-- Get_DB_Patch_List(null, 'AD','%', '03-MAR-2002', 'SILENT');

-- end;

procedure Get_DB_Patch_List (p_heading varchar2default'AD_BUGS'

, p_app_short_name varchar2default'%'

, p_bug_number varchar2default'%'

, p_start_date datedefaultto_date('01-JAN-1960','DD-MON-YYYY')

, p_output_option varchar2default'TABLE') is

l_cursor integer;

l_sqltxt varchar2(5000);

l_list_out varchar2(32767);

l_hold_comma varchar2(2);

l_counter integer;

l_app_short_name varchar2(50);

l_bug_number varchar2(30);

l_creation_date date;

begin

selectcount(*)into l_counter

from all_tables

where table_name ='AD_BUGS';

if l_counter >0then

l_sqltxt :='select application_short_name'

||' , bug_number'

||' , creation_date'

||' from ad_bugs'

||' where upper(application_short_name) like '''

|| upper(p_app_short_name)

||''' and creation_date >= '''

|| nvl(to_char(p_start_date,'DD-MON-YYYY'),'01-JAN-1960')

||''' and bug_number like '''||p_bug_number||'''';

Run_SQL(p_heading, l_sqltxt);

else

WarningPrint('Table AD_BUGS does not exist');

ActionWarningPrint('Unable to retrieve a patch list from the database as this feature is not available on this version of the applications');

endif;

end Get_DB_Patch_List;

-- Function Name: Get_RDBMS_Header

-- Usage:

-- Get_RDBMS_Header;

-- Returns:

-- Version of the Database from v$version

-- Examples:

-- declare

-- RDBMS_Ver := v$version.banner%type;

-- begin

-- RDBMS_Ver := Get_RDBMS_Header;

-- end;

Function Get_RDBMS_Header returnvarchar2is

l_hold_name v$database.name%type;

l_DB_Ver v$version.banner%type;

begin

selectname

into l_hold_name

from v$database;

exception

whenothersthen

l_hold_name :='Not Available';

end;

begin

select banner

into l_DB_Ver

from v$version

where banner like'Oracle%';

exception

whenothersthen

l_DB_Ver :='Not Available';

end;

return(l_hold_name ||' - '|| l_DB_Ver);

end Get_RDBMS_Header;

-- Function Name: Compare_Pkg_Version

-- Usage:

-- Compare_Pkg_Version('package_name','obj_type','obj_owner', 'outversvar',

-- 'reference_version');

-- Compare_Pkg_Version('package_name','obj_type', 'outversvar',

-- 'reference_version');

-- Parameters:

-- package_name - Name of the package whose version is being checked

-- obj_type - Either BODY or SPEC to determine which piece to check

-- obj_owner - The owner of the package being checked. If null or

-- not supplied the default is APPS.

-- outversvar - A text out variable to hold the actual package version

-- of the package as returned from the database

-- reference_version - A string containing the version to which the

-- package version should be compared (in

-- format ###.##, ie, in a format convertible

-- to a number. As opposed to, for example,

-- 11.5.119, use 115.119.

-- Returns:

-- 'greater' if the version of the object is greater than the reference

-- 'less' if the version of the object is less than the reference

-- 'equal' if the version of the object is equal to the reference

-- 'null' if either the reference or db version is null

-- Examples:

-- declare

-- Comparison_Var varchar2(8);

-- Package_Version varchar2(10);

-- begin

-- Comparison_Var := Compare_Pkg_Version('PA_UTILS2','BODY','APPS',

-- Package_Version, '115.13');

-- Comparison_Var := Compare_Pkg_Version('PA_UTILS2','BODY',

-- Package_Version, '115.13');

-- end;

Function Compare_Pkg_Version(

package_name invarchar2,

object_type invarchar2,

object_owner invarchar2,

version_str inoutvarchar2,

compare_version invarchar2)

returnvarchar2is

vers_line varchar2(1000);

l_object_owner varchar2(250);

db_vers_key number;

in_vers_key number;

begin

l_object_owner := object_owner;

if l_object_owner isnullthen

l_object_owner :='APPS';

endif;

in_vers_key :=

to_number(substr(compare_version,instr(compare_version,'.')+1));

ifupper(object_type)='BODY'then

select text into vers_line

from dba_source

where name= package_name

and owner = l_object_owner

and text like'%$Header%'

and type='PACKAGE BODY';

else

select text into vers_line

from dba_source

where name= package_name

and owner = l_object_owner

and text like'%$Header%'

and type='PACKAGE';

endif;

vers_line :=substr(vers_line,instr(vers_line,'$Header:')+9);

vers_line :=ltrim(vers_line);

vers_line :=substr(vers_line,1,instr(vers_line,' ',1,2)-1);

vers_line :=substr(vers_line,instr(vers_line,' ')+1);

version_str := vers_line;

db_vers_key :=

to_number(substr(vers_line,instr(vers_line,'.')+1));

if db_vers_key < in_vers_key then

return('less');

elsif db_vers_key > in_vers_key then

return('greater');

elsif db_vers_key = in_vers_key then

return('equal');

elsif db_vers_key isnullor in_vers_key isnullthen

return('null');

endif;

exceptionwhenothersthen

ErrorPrint('Unable to verify package version for '||package_name||' ('||

object_type||') -- '||sqlerrm||' occured in Compare_Pkg_Version');

ActionErrorPrint('Contact your support representative and supply the '||

'above error information');

return('null');

end Compare_Pkg_Version;

Function Compare_Pkg_Version(

package_name invarchar2,

object_type invarchar2,

version_str inoutvarchar2,

compare_version invarchar2defaultnull)

returnvarchar2is

begin

return(compare_pkg_version(

package_name, object_type,null, version_str,compare_version));

end Compare_Pkg_Version;

-- Procedure Name: Show_Invalids

-- Usage:

-- Show_Invalids('start string', 'include errors', 'heading');

-- Parameters:

-- start string - An string indicating the beginning of object names to

-- be included. The underscore '_' character will be

-- escaped in this string so that it does not act as a

-- wild card character. For example, 'PA_' will not match

-- 'PAY' even though it normally would in SQL*Plus.

-- include errors - Y or N to indicate whether to search on and report

-- the errors from ALL_ERRORS for each of the invalid

-- objects found. (DEFAULT = N)

-- heading - An optional heading for the table. If null the heading will

-- be "Invalid Objects (Starting with 'XXX')" where XXX is

-- the start string parameter.

-- Output:

-- A listing of invalid objects whose name starts with the 'start string'.

-- For packages, procedures, and functions, file versions will be included,

-- and when requested, error messages associated with the object will

-- be reported.

-- Examples:

-- Show_Invalids('PA_','Y');

-- Show_Invalids('GL_');

Procedure Show_Invalids (p_start_string varchar2

, p_include_errors varchar2default'N'

, p_heading varchar2defaultnull)is

l_start_string varchar2(60);

l_errors varchar2(32767);

l_file_version varchar2(100);

l_heading varchar2(500);

l_first_row boolean:=true;

l_table_row V2T;

l_row_options V2T;

cursor get_invalids(c_start_string varchar2)is

select o.object_name, o.object_type, o.owner

from all_objects o

where o.status ='INVALID'

and o.object_name like c_start_string escape'~'

orderby o.object_name;

cursor get_file_version(

c_obj_name varchar2

, c_obj_type varchar2

, c_obj_owner varchar2)is

selectsubstr(substr(s.text,instr(s.text,'$Header')+9),1,

instr(substr(s.text,instr(s.text,'$Header')+9),' ',1,2)-1) file_vers

from all_source s

where name= c_obj_name

and type= c_obj_type

and owner = c_obj_owner

and text like'%$Header%';

cursor get_errors (

c_obj_name varchar2

, c_obj_type varchar2

, c_obj_owner varchar2)is

selectto_char(sequence)||') LINE: '||to_char(line)||' CHR: '||

to_char(position)||' '||text error_row

from all_errors

where name= c_obj_name

and type= c_obj_type

and owner = c_obj_owner;

begin

l_start_string :=upper(replace(p_start_string,'_','~_'))||'%';

if p_heading isnullthen

l_heading :='Invalid Objects (Starting with '''||p_start_string||''')';

else

l_heading := p_heading;

endif;

line_out(' '|| l_heading ||'');

for inv_rec in get_invalids(l_start_string)loop

if l_first_row then

Start_Table('Invalid Objects');

if p_include_errors ='Y'then

Show_Table_Header(V2T('Object Name','Object Type','Owner',

'File Version','Errors'));

else

Show_Table_Header(V2T('Object Name','Object Type','Owner',

'File Version'));

endif;

l_first_row :=false;

endif;

if inv_rec.object_type like'PACKAGE%'or

inv_rec.object_type in('PROCEDURE','FUNCTION')then

open get_file_version(inv_rec.object_name, inv_rec.object_type,

inv_rec.owner);

fetch get_file_version into l_file_version;

if get_file_version%notfoundthen

l_file_version :=null;

endif;

close get_file_version;

else

l_file_version :=null;

endif;

if p_include_errors ='Y'then

for err_rec in get_errors(inv_rec.object_name, inv_rec.object_type,

inv_rec.owner)loop

l_errors := l_errors||err_rec.error_row||' ';

endloop;

l_table_row := V2T(inv_rec.object_name, inv_rec.object_type,

inv_rec.owner, l_file_version, l_errors);

l_row_options := V2T(null,'nowrap',null,'nowrap','nowrap');

Show_Table_Row(l_table_row,l_row_options);

else

l_table_row := V2T(inv_rec.object_name, inv_rec.object_type,

inv_rec.owner, l_file_version);

Show_Table_Row(l_table_row);

endif;

endloop;

End_Table;

if l_first_row then

Insert_HTML(' No Rows Selected ');

endif;

exceptionwhenothersthen

ErrorPrint(sqlerrm||' occured in Show_Invalids');

ActionErrorPrint('Use the feedback link to report the above error to '||

'support');

end Show_Invalids;

posted @ 2013-06-07 10:49 全威儒阅读(323) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

ORACLE EBS

人法地地法天天法道道法自然

CoreApiHtml.sql -3<-- (INVItemCt115h.sql ) Note: 223702.1

公告

ORACLE EBS

人法地 地法天 天法道 道法自然

CoreApiHtml.sql -3<-- (INVItemCt115h.sql ) Note: 223702.1

公告

人法地地法天天法道道法自然