CoreApiHtml.sql -1<-- (INVItemCt115h.sql ) Note: 223702.1
CoreApiHtml.sql
set termout off
prompt<html lang="en,us"><head><!--
REM $Header: CoreApiHtml.sql 1.21 06-DEC-2002 support $
REM +=========================================================================+
REM | Copyright (c) 2001 Oracle Corporation Belmont, California, USA |
REM | All rights reserved. |
REM +=========================================================================+
REM ##########################################################################
REM PURPOSE: PLSQL API
REM PROGRAM: CoreApiHtml.sql
REM NOTE: No Note to Associate
REM CONFIG: support.cfg, Requires files to source (.env) and Passwords.
REM USAGE: API only, included in others as: @CoreApiHtml
REM FRAMEWORK: SDF OHS
REM PRODUCT_VER: 11.5.X
REM PRODUCT_CODE: 510
REM SUBCOMPONENT: FND
REM NODE: WEB
REM PARAMS: NONE
REM
REM ##########################################################################
REM ##########################################################################
REM Change History
REM 05-JUN-2002 ALUMPE Modified Display_SQL so that headers print only
REM if rows are returned
REM 02-JUL-2002 ALUMPE 1) Added Column_Exists API
REM 2) Added Show_Invalids API
REM 3) Added Feedback option and Number of rows option
REM To Run_SQL and Display_SQL API's
REM 4) Modified Get_DB_Patch_List to give appropriate
REM feedback if no rows retrieved
REM 5) Added CheckFinPeriod API
REM 6) Added CheckKeyFlexfield API
REM 7) Fixed font inconsistencies with Tab0Print
REM 05-AUG-2002 ALUMPE Added Init_Block and End_Block API's to enable
REM multi-block script without reprinting header info
REM 14-AUG-2002 ALUMPE Added NoticePrint API.
REM 30-AUG-2002 ALUMPE Modified Display_profiles to use the _VL views
REM rather than the _TL tables with a language condition
REM as userenv('LANG') = 'US' but language = 'AMERICAN'
REM on some 10.7 instances
REM 07-NOV-2002 ALUMPE Modified Display_SQL/Run_SQL to allow for indenting
REM of the table output.
REM 06-DEC-2002 ALUMPE Modified Display_SQL to convert chr(10) to html
REM <BR> in text values
REM ##########################################################################
REM Procedures/Functions Implemented
REM ActionErrorPrint()
REM ActionPrint()
REM ActionWarningPrint()
REM Begin_Pre()
REM BRPrint()
REM CheckFinPeriod()
REM CheckKeyFlexfield()
REM CheckProfile()
REM Column_Exists()
REM Compare_Pkg_Version()
REM Display_Profiles()
REM Display_SQL()
REM Display_Table()
REM End_Pre()
REM ErrorPrint()
REM Get_DB_Apps_Version()
REM Get_DB_Patch_List()
REM Get_Package_Body()
REM Get_Package_Spec()
REM Get_Package_Version()
REM Get_Profile_Option()
REM Get_RDBMS_Header()
REM Insert_HTML()
REM Insert_Style_Sheet()
REM NoticePrint()
REM Run_SQL()
REM SectionPrint()
REM Send_Email()
REM Set_Client()
REM Set_Org()
REM Show_Header()
REM Show_Invalids()
REM Show_Link()
REM Show_Table()
REM Tab0Print()
REM Tab1Print()
REM Tab2Print()
REM Tab3Print()
REM WarningPrint()
REM
REM Procedures/Functions Removed
REM BlackPrint() - 508 Compatibility
REM BluePrint() - 508 Compatibility
REM GreenPrint() - 508 Compatibility
REM Plain_SectionPrint() - Same as ActionPrint()
REM RedPrint() - 508 Compatibility
REM Steps_Results() - Standards Modification
REM SuccessPrint() - Same as Tab1Print()
REM
REM Functions to possibly implement
REM Compare_SQL_Data()
REM Execute_URL()
REM Validate_Apps_User()
REM
set serveroutput onsize1000000
set feedback off
set verify off
set echo off
setlong2000000000
set linesize 32767
set longchunksize 32767
set pagesize 0
settimingoff
set trimout on
set trimspool on
set heading off
set autoprint on
undefine nbsp
define nbsp =''
column lnbsp new_value nbsp noprint
selectchr(38)||'nbsp' lnbsp from dual;
undefine p_id
define p_id =''
column lp_id new_value p_id noprint
selectchr(38)||'p_id' lp_id from dual;
undefine lt
define lt =''
column llt new_value lt noprint
selectchr(38)||'lt' llt from dual;
undefine gt
define gt =''
column lgt new_value gt noprint
selectchr(38)||'gt' lgt from dual;
undefine AMPER
define AMPER =''
column lAMPER new_value AMPER noprint
selectchr(38) lAMPER from dual;
undefine T_VARCHAR2
undefine T_ROWID
undefine T_NUMBER
undefine T_LONG
undefine T_DATE
undefine T_CHAR
undefine T_CLOB
variable g_hold_output clob;
declare
counter number :=0;
failures number :=0;
g_curr_loc number :=1;
/* Global variables set by Set_Client which can be used throughout a script */
g_user_id number;
g_resp_id number;
g_appl_id number;
g_org_id number;
/* Global variable set by Set_Client or Get_DB_Apps_Version which can
be referenced in scripts which branch based on applications release */
g_appl_version varchar2(10);
type V2T istableofvarchar2(32767);
procedure line_out (text varchar2)is
l_ptext varchar2(32767);
l_hold_num number;
begin
l_hold_num :=mod(g_curr_loc,32767);
if l_hold_num +length(text)>32759then
l_ptext :='<!--'||rpad('*',32761-l_hold_num,'*')||'-->';
dbms_lob.write(:g_hold_output,length(l_ptext), g_curr_loc, l_ptext);
g_curr_loc := g_curr_loc +length(l_ptext);
endif;
dbms_lob.write(:g_hold_output,length(text)+1, g_curr_loc, text ||chr(10));
g_curr_loc := g_curr_loc +length(text)+1;
end line_out;
-- Procedure Name: Insert_Style_Sheet
--
-- Usage:
-- Insert_Style_Sheet;
--
-- Output:
-- Inserts a Style Sheet into the output
--
-- Comments:
-- This is not normally needed as the style sheet is automatically
-- inserted with the header.
--
procedure Insert_Style_Sheet is
begin
line_out('<STYLE TYPE="text/css">');
line_out('<!--');
line_out('.ind1 {margin-left: .25in}');
line_out('.ind2 {margin-left: .50in}');
line_out('.ind3 {margin-left: .75in}');
line_out('.tab0 {font-size: 10pt; font-weight: normal}');
line_out('.tab1 {text-indent: .25in; font-size: 10pt; font-weight: normal}');
line_out('.tab2 {text-indent: .5in; font-size: 10pt; font-weight: normal}');
line_out('.tab3 {text-indent: .75in; font-size: 10pt; font-weight: normal}');
line_out('.error {color: #cc0000; font-size: 10pt; font-weight: normal}');
line_out('.errorbold {font-weight: bold; color: #cc0000; font-size: 10pt}');
line_out('.warning {font-weight: normal; color: #336699; font-size: 10pt}');
line_out('.warningbold {font-weight: bold; color: #336699; font-size: 10pt}');
line_out('.notice {font-weight: normal; font-style: italic; color: #663366; font-size: 10pt}');
line_out('.noticebold {font-weight: bold; font-style: italic; color: #663366; font-size: 10pt}');
line_out('.section {font-weight: normal; font-size: 10pt}');
line_out('.sectionbold {font-weight: bold; font-size: 10pt}');
line_out('.BigPrint {font-weight: bold; font-size: 14pt}');
line_out('.SmallPrint {font-weight: normal; font-size: 8pt}');
line_out('.BigError {color: #cc0000; font-size: 12pt; font-weight: bold}');
line_out('body {background-color: white; font: normal 12pt Ariel;}');
line_out('table {background-color: #000000 color:#000000; font-size: 10pt; font-weight: bold; line-height:1.5; padding:2px; text-align:left}');
line_out('h1, h2, h3, h4 {color: #00000}');
line_out('h3 {font-size: 16pt}');
line_out('td {background-color: #f7f7e7; color: #000000; font-weight: normal; font-size: 9pt; border-style: solid; border-width: 1; border-color: #CCCC99; white-space: nowrap}');
line_out('tr {background-color: #f7f7e7; color: #000000; font-weight: normal; font-size: 9pt; white-space: nowrap}');
line_out('th {background-color: #CCCC99; color: #336699; height: 20; border-style: solid; border-width: 1; border-left-color: #f7f7e7; border-right-color: #f7f7e7; border-top-width: 0; border-bottom-width: 0; white-space: nowrap}');
line_out('th.rowh {background-color: #CCCC99; color: #336699; height: 20; border-style: solid; border-width: 1; border-top-color: #f7f7e7; border-bottom-color: #f7f7e7; border-left-width: 0; border-right-width: 0; white-space: nowrap}');
line_out('-->');
line_out('</style>');
end;
-- Procedure Name: Insert_HTML
--
-- Usage:
-- Insert_HMTL('String');
--
-- Parameters:
-- String - Any text string
--
-- Output:
-- Displays the text string
--
-- Examples:
-- begin
-- Insert_HTML('<em>This can be any text you want.</em>');
-- end;
--
-- Notes:
-- Usage of this procedure may make the script not compatible with
-- standards or 508. Please avoid if possible.
--
procedure Insert_HTML(p_text varchar2)is
begin
line_out(p_text);
end Insert_HTML;
-- Procedure Name: ActionErrorPrint
--
-- Usage:
-- ActionErrorPrint('String');
--
-- Parameters:
-- String - Any text string
--
-- Output:
-- Displays the text string with the word ACTION - prior to the string
--
-- Examples:
-- begin
-- ActionErrorPrint('Run Gather Schema Statistics');
-- end;
--
procedure ActionErrorPrint(p_text varchar2)is
begin
line_out('<span class="errorbold">ACTION - </span><span class="error">' || p_text ||'</span><br>');
end ActionErrorPrint;
-- Procedure Name: ActionPrint
--
-- Usage:
-- ActionPrint('String');
--
-- Parameters:
-- String - Any text string
--
-- Output:
-- Displays the text string
--
-- Examples:
-- begin
-- ActionPrint('Run Gather Schema Statistics');
-- end;
--
procedure ActionPrint(p_text varchar2)is
begin
line_out(p_text ||'<br>');
end ActionPrint;
-- Procedure Name: ActionWarningPrint
--
-- Usage:
-- ActionWarningPrint('String');
--
-- Parameters:
-- String - Any text string
--
-- Output:
-- Displays the text string in warning format
--
-- Examples:
-- begin
-- ActionWarningPrint('Run Gather Schema Statistics');
-- end;
--
procedure ActionWarningPrint(p_text varchar2)is
begin
line_out('<span class="warningbold">ACTION - </span><span class="warning">' || p_text ||'</span><br>');
end ActionWarningPrint;
-- Procedure Name: WarningPrint
--
-- Usage:
-- WarningPrint('String');
--
-- Parameters:
-- String - Any text string
--
-- Output:
-- Displays the text string in warning format
--
-- Examples:
-- begin
-- WarningPrint('Statistics are not up to date');
-- end;
--
procedure WarningPrint(p_text varchar2)is
begin
line_out('<span class="warningbold">WARNING - </span><span class="warning">' || p_text ||'</span><br>');
end WarningPrint;
-- Procedure Name: ActionErrorLink
--
-- Usage:
-- ActionErrorLink('Pre_String','Note_Number','Post_String');
-- ActionErrorLink('Pre_String','URL','Link_Text', 'Post_String');
--
-- Parameters:
-- Pre_String - Text to appear prior to the link
-- Note_Number - Number of a metalink note to link to
-- URL - Any valid URL
-- Link_Text - Text for the link to URL
-- Post_String - Text to appear after the link
--
-- Output:
-- Displays the pre-link string, the link (as specified either by the
-- note number or by the URL and link text), and the post-link string
-- all in the format of an Error Action
--
-- Examples:
-- begin
-- ActionErrorLink('For clarification see note', 112233.1,
-- 'which provides more information on the subject');
-- ActionErrorLink('For clarification see the',
-- 'http://someurl.us.com/somepage.html','Development Homepage',
-- 'which provides more information on the subject');
-- end;
--
procedure ActionErrorLink(p_txt1 varchar2
, p_note varchar2
, p_txt2 varchar2)is
begin
line_out('<span class="errorbold">ACTION - </span><span class="error">'
|| p_txt1
||' <a href="http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id='
|| p_note
||'">'
|| p_note
||'</a> '
|| p_txt2
||'</span><br>');
end ActionErrorLink;
procedure ActionErrorLink(p_txt1 varchar2
, p_url varchar2
, p_link_txt varchar2
, p_txt2 varchar2)is
begin
line_out('<span class="errorbold">ACTION - </span><span class="error">'
|| p_txt1
||' <a href="'
|| p_url
||'">'
|| p_link_txt
||'</a> '
|| p_txt2
||'</span><br>');
end ActionErrorLink;
-- Procedure Name: ActionWarningLink
--
-- Usage:
-- ActionWarningLink('Pre_String','Note_Number','Post_String');
-- ActionWarningLink('Pre_String','URL','Link_Text', 'Post_String');
--
-- Parameters:
-- Pre_String - Text to appear prior to the link
-- Note_Number - Number of a metalink note to link to
-- URL - Any valid URL
-- Link_Text - Text for the link to URL
-- Post_String - Text to appear after the link
--
-- Output:
-- Displays the pre-link string, the link (as specified either by the
-- note number or by the URL and link text), and the post-link string
-- all in the format of a Warning Action
--
-- Examples:
-- begin
-- ActionWarningLink('For clarification see note', 112233.1,
-- 'which provides more information on the subject');
-- ActionWarningLink('For clarification see the',
-- 'http://someurl.us.com/somepage.html','Development Homepage',
-- 'which provides more information on the subject');
-- end;
--
procedure ActionWarningLink(p_txt1 varchar2
, p_note varchar2
, p_txt2 varchar2)is
begin
line_out('<span class="warningbold">ACTION - </span><span class="warning">'
|| p_txt1
||' <a href="http://metalink.oracle.com/metalink/plsql/'
||'ml2_documents.showDocument?p_database_id=NOT&p_id='
|| p_note
||'">'
|| p_note
||'</a> '
|| p_txt2
||'</span><br>');
end ActionWarningLink;
procedure ActionWarningLink(p_txt1 varchar2
, p_url varchar2
, p_link_txt varchar2
, p_txt2 varchar2)is
begin
line_out('<span class="warningbold">ACTION - </span><spanclass="warning">'
|| p_txt1
||' <a href="'
|| p_url
||'">'
|| p_link_txt
||'</a> '
|| p_txt2
||'</span><br>');
end ActionWarningLink;
-- Procedure Name: ErrorPrint
--
-- Usage:
-- ErrorPrint('String');
--
-- Parameters:
-- String - Any text string
--
-- Output:
-- Displays the text string
--
-- Examples:
-- begin
-- ErrorPrint('Statistics have not been run');
-- end;
--
procedure ErrorPrint(p_text varchar2)is
begin
line_out('<span class="errorbold">ERROR - </span><span class="error">' || p_text ||'</span><br>');
end ErrorPrint;
-- Procedure Name: NoticePrint
--
-- Usage:
-- NoticePrint('String');
--
-- Parameters:
-- String - Any text string
--
-- Output:
-- Displays the text string in italics preceded by the word 'ATTENTION - '
--
-- Examples:
-- begin
-- NoticePrint('Be alert to this information');
-- end;
--
procedure NoticePrint (p_text varchar2)is
begin
line_out('<span class="noticebold">ATTENTION - </span>'||
'<span class="notice">'||p_text||'</span><br>');
end NoticePrint;
-- Procedure Name: SectionPrint
--
-- Usage:
-- SectionPrint('String');
--
-- Parameters:
-- String - Any text string
--
-- Output:
-- Displays the text string in bold print
--
-- Examples:
-- begin
-- SectionPrint('Checking OE Parameters');
-- end;
--
procedure SectionPrint (p_text varchar2)is
begin
line_out('<br><span class="sectionbold">'|| p_text ||'</span><br>');
end SectionPrint;
-- Procedure Name: Tab0Print
--
-- Usage:
-- Tab0Print('String');
--
-- Parameters:
-- String - Any text string
--
-- Output:
-- Displays the text string with no indentation
--
-- Examples:
-- begin
-- Tab0Print('Layer 0');
-- end;
--
procedure Tab0Print (p_text varchar2)is
begin
line_out('<div class="tab0">'|| p_text ||'</div>');
end Tab0Print;
-- Procedure Name: Tab1Print
--
-- Usage:
-- Tab1Print('String');
--
-- Parameters:
-- String - Any text string
--
-- Output:
-- Displays the text string indented .25 inch
--
-- Examples:
-- begin
-- Tab1Print('Layer 1');
-- end;
--
procedure Tab1Print (p_text varchar2)is
begin
line_out('<div class="tab1">'|| p_text ||'</div>');
end Tab1Print;
-- Procedure Name: Tab2Print
--
-- Usage:
-- Tab2Print('String');
--
-- Parameters:
-- String - Any text string
--
-- Output:
-- Displays the text string indented .50 inch
--
-- Examples:
-- begin
-- Tab2Print('Layer 2');
-- end;
--
procedure Tab2Print (p_text varchar2)is
begin
line_out('<div class="tab2">'|| p_text ||'</div>');
end Tab2Print;
-- Procedure Name: Tab3Print
--
-- Usage:
-- Tab3Print('String');
--
-- Parameters:
-- String - Any text string
--
-- Output:
-- Displays the text string indented .75 inch
--
-- Examples:
-- begin
-- Tab3Print('Layer 3');
-- end;
--
procedure Tab3Print (p_text varchar2)is
begin
line_out('<div class="tab3">'|| p_text ||'</div>');
end Tab3Print;
-- Procedure Name: BRPrint
--
-- Usage:
-- BRPrint;
--
-- Output:
-- Displays a blank Line
--
-- Examples:
-- begin
-- Tab3Print('Layer 3');
-- BRPrint;
-- Tab3Print('Layer 4');
-- end;
--
procedure BRPrint is
begin
line_out('<br>');
end BRPrint;
-- Procedure Name: CheckFinPeriod
--
-- Usage:
-- CheckFinPeriod('Set of Books ID','Application ID');
--
-- Paramteters:
-- Set of Books ID - ID for the set of books
-- Application ID - ID of the application whose periods are being checked
--
-- Output:
-- List the number of defined and open periods. Indicate the latest
-- open period. Produce warnings if no periods are open or if the
-- current date is not in an open period.
--
-- Examples:
-- CheckFinPeriod(62, 222); -- Check open periods for AR SOB 62
-- CheckFinPeriod(202, 201); -- Check open periods for PO SOB 202
--
procedure checkFinPeriod (p_sobid NUMBER, p_appid NUMBER)IS
l_appname VARCHAR2(50):=NULL;
l_period_name VARCHAR2(50);
l_user_period_type VARCHAR2(50);
l_start_date DATE;
l_end_date DATE;
l_sysdate DATE;
l_sysopen VARCHAR2(1);
CURSOR C1 IS
select a.name sobname,
count(b.period_name) total_periods,
count(decode(b.closing_status,'O',b.PERIOD_NAME,null)) open_periods,
a.accounted_period_type period_type
from gl_sets_of_books a,
gl_period_statuses b
where a.set_of_books_id = b.set_of_books_id (+)
and b.application_id = p_appId
and a.set_of_books_id = p_sobId
and b.period_type =a.accounted_period_type
groupbya.name,a.accounted_period_type;
c1_rec c1%rowtype;
no_rows exception;
BEGIN
select application_name
into l_appname
from fnd_application_vl
where application_id = p_appid ;
open c1;
fetch c1 into c1_rec;
IF c1%notfoundTHEN
raise no_rows;
ENDIF;
select user_period_type into l_user_period_type
from gl_period_types
where period_type = c1_rec.period_type;
Tab1Print('Set of books '|| c1_rec.sobname ||' for application '
|| l_appname ||' has '||to_char(c1_rec.total_periods)
||' periods defined and '||to_char(c1_rec.open_periods)
||' periods open for period type '|| l_user_period_type);
IF c1_rec.total_periods =0THEN
WarningPrint('There are no periods defined for this Set of books');
ActionWarningPrint('There must be periods defined for this set of books');
ENDIF;
IF c1_rec.open_periods =0THEN
WarningPrint('There are no open periods defined for this Set of books');
ActionWArningprint('Please consider opening a period for this '||
'application and set of books');
ELSE
BEGIN
SELECT period_name, start_date, end_date,sysdate
INTO l_period_name, l_start_date, l_end_date, l_sysdate
FROM gl_period_statuses
WHERE adjustment_period_flag ='N'
AND period_type = c1_rec.period_type
AND start_date =(
SELECTMAX(start_date)
FROM gl_period_statuses
WHERE closing_status ='O'
AND adjustment_period_flag ='N'
AND period_type = c1_rec.period_type
AND application_id = p_appId
AND set_of_books_id = p_sobId )
AND closing_status ='O'
AND application_id = p_appId
AND set_of_books_id = p_sobId;
/* check if sysdate is in the latest open period*/
l_sysopen :='N';
IF l_sysdate >= l_start_date AND l_sysdate <= l_end_date THEN
l_sysOpen :='Y';
ENDIF;
Tab1Print('Latest open period is '|| l_period_name
||' with a start date of '||to_char(l_start_date)
||' and an end date of '||to_char(l_end_date));
IF l_sysopen ='Y'THEN
Tab2Print('Current date '||to_char(l_sysdate)
||' is in the latest open period');
ELSE
BEGIN
SELECT period_name, start_date, end_date,sysdate
INTO l_period_name, l_start_date, l_end_date, l_sysdate
FROM gl_period_statuses
WHERE adjustment_period_flag ='N'
AND period_type = c1_rec.period_type
AND sysdatebetween start_date and end_date
AND closing_status ='O'
AND application_id = p_appId
AND set_of_books_id = p_sobId;
Tab2Print('Current date '||to_char(sysDate)
||' is in the open period '|| l_period_name
||' with a start date of '||to_char(l_start_date)
||' and an end date of '||to_char(l_end_date));
EXCEPTIONWHENNO_DATA_FOUNDTHEN
WarningPrint('Current date '||to_char(l_sysdate)
||' is not in an open period');
ActionwarningPrint('Please consider opening the current period');
END;
ENDIF;
EXCEPTIONWHENNO_DATA_FOUNDTHEN
/* not really possible to fall in this exception as we already
checked that there were open periods */
WarningPrint('There are no open periods defined for this Set of books');
ActionWArningprint('Please consider opening a period for this '||
'application and set of books');
END;
ENDIF;
EXCEPTION
WHEN NO_ROWS THEN
WarningPrint('There are no accounting periods defined in '||
'gl_period_statuses for this application and set of books');
ActionWArningprint('If required, define the accounting calendar for this '||
'application and set of books');
WHENNO_DATA_FOUNDTHEN
ErrorPrint('Invalid Application id passed to checkFinPeriod');
ActionErrorPrint('Application id '||to_char(p_appid)
||' is not valid on this system');
WHENOTHERSTHEN
ErrorPrint(sqlerrm||' occurred in CheckFinPeriod');
ActionErrorPrint('Report this error to your support representative');
END checkFinPeriod;
-- Function Name: CheckKeyFlexfield
-- Procedure Name: CheckKeyFlexfield
--
-- Usage:
-- CheckKeyFlexfield('Key Flex Code','Flex Structure ID','Print Header');
--
-- Parameters:
-- Key Flex Code - The code of the Key Flexfield to be displayed. For
-- example, for the Accounting Flexfield use 'GL#'.
-- Flex Structure ID - The id_flex_num of the specific structure
-- of the key flexfield whose details are to be
-- displayed. If null, print details of all
-- structures. (default NULL)
-- Print Header - A boolean (true or false) indicating whether the output
-- should print a heading before outputting the details
-- of the key flexfield. (default TRUE)
-- Returns:
-- If value has been provided for the Flex Structure ID, the function
-- will returns an array of character strings with the following structure
-- 1 name of the flexfield
-- 2 enabled flag
-- 3 frozen flag
-- 4 dynamic instert flag
-- 5 cross validation allowed flag
-- 6 number of enabled segments defined
-- 7 number of enabled segments with value sets
-- 8 Y if any segment has security otherwise N
-- If no value is passed to the parameter the function will return an
-- array will null values.:w
--
-- Output:
-- Displays key information about the flexfield, its structure, and the
-- individual flexfield segments that make it up.
--
-- Examples:
-- declare
-- flexarray V2T;
-- begin
-- CheckKeyFlexfield('GL#', 50577, true);
-- CheckKeyFlexfield('MSTK', null, false);
-- flexarray := CheckKeyFlexfield('GL#', 12345, false);
-- end;
--
Function CheckKeyFlexfield(p_flex_code invarchar2
, p_flex_num innumberdefaultnull
, p_print_heading inbooleandefaulttrue)
return V2T is
l_ret_array V2T := V2T(null,null,null,null,null,null,null,null);
l_no_value_sets integer:=0;
l_any_sec_enabled varchar2(1):='N';
l_sec_enabled varchar2(1):='N';
l_flex_name fnd_id_flexs.id_flex_name%type;
l_counter integer:=0;
l_counter2 integer:=0;
l_num_segs integer:=0;
l_num_segs_vs integer:=0;
l_rule_count integer:=0;
l_rule_assign_count integer:=0;
l_value_set_str varchar2(400);
leave_api exception;
cursor get_structs (p_f_code varchar2, p_f_num number)is
select id_flex_num flex_str_num,
id_flex_structure_name flex_str_name,
to_char(last_update_date,'DD-MON-YYYY HH24:MI:SS') last_updated,
cross_segment_validation_flag cross_val,
dynamic_inserts_allowed_flag dyn_insert,
enabled_flag enabled,
freeze_flex_definition_flag frozen
from fnd_id_flex_structures_vl
where id_flex_code = p_f_code
and enabled_flag ='Y'
and id_flex_num =nvl(p_f_num,id_flex_num);
cursor get_segments (p_f_code varchar2, p_f_num number)is
select s.application_column_name col_name,
s.segment_name seg_name,
s.segment_num seg_num,
s.enabled_flag enabled,
s.required_flag required,
s.display_flag displayed,
s.flex_value_set_id value_set_id,
vs.flex_value_set_name value_set_name,
DECODE(vs.validation_type,
'I','Independent','N','None', 'D','Dependent',
'U','Special', 'P','Pair', 'F','Table',
'X','Translatable Independent', 'Y','Translatable Dependent',
vs.validation_type) validation_type,
s.security_enabled_flag seg_security,
nvl(vs.security_enabled_flag,'N') value_set_security
from fnd_id_flex_segments_vl s, fnd_flex_value_sets vs
where s.flex_value_set_id = vs.flex_value_set_id (+)
and s.id_flex_code = p_f_code
and s.id_flex_num = p_f_num
orderby s.segment_num ;
cursor get_qualifiers(p_f_code varchar2, p_f_num number, p_col_name varchar2)is
select segment_prompt
from fnd_segment_attribute_values sav,
fnd_segment_attribute_types sat
where sav.attribute_value ='Y'
and sav.segment_attribute_type <>'GL_GLOBAL'
and sav.application_id = sat.application_id
and sav.id_flex_code = sat.id_flex_code
and sav.segment_attribute_type = sat.segment_attribute_type
and sav.id_flex_code = p_f_code
and sav.id_flex_num = p_f_num
and sav.application_column_name = p_col_name;
begin
begin
select id_flex_name into l_flex_name
from fnd_id_flexs
where id_flex_code = p_flex_code;
exceptionwhenno_data_foundthen
WarningPrint('ID Flex Code passed '||p_flex_code||' is not valid on this '||
'system');
ActionWarningPrint('ID Flex Code '||p_flex_code||' will not be tested');
end;
BRPrint;
if p_flex_num isnullthen
if(p_print_heading)then
SectionPrint('Details of Key flexfield: '||l_flex_name);
else
Tab0Print('Key flexfield: '||l_flex_name);
endif;
else
l_ret_array(1):= l_flex_name;
if(p_print_heading)then
SectionPrint('Details of Key flexfield: '||l_flex_name
||' with id_flex_num '||to_char(p_flex_num));
else
Tab0Print('Key flexfield: '||l_flex_name||' with id_flex_num '
||to_char(p_flex_num));
endif;
endif;
l_counter :=0;
for str in get_structs(p_flex_code, p_flex_num)loop
l_counter := l_counter +1;
if p_flex_num isnotnullthen
l_ret_array(2):= str.enabled;
l_ret_array(3):= str.frozen;
l_ret_array(4):= str.dyn_insert;
l_ret_array(5):= str.cross_val;
endif;
if l_counter >1then
BRPrint;
endif;
Tab1Print('Structure '||str.flex_str_name||' (ID='||
to_char(str.flex_str_num)||')');
Tab1Print('Enabled Flag = '||str.enabled||', Frozen = '||str.frozen
||', Dynamic Inserts = '||str.dyn_insert||', Cross Validation Allowed = '
||str.cross_val||', Last Updated '||str.last_updated);
l_counter2 :=0;
l_num_segs :=0;
l_num_segs_vs :=0;
for seg in get_segments(p_flex_code, str.flex_str_num)loop
if l_counter2 =0then
Tab1Print('Segment Details for '||str.flex_str_name);
BRPrint;
endif;
l_counter2 := l_counter2 +1;
if(p_flex_num isnotnull)then
if seg.enabled ='Y'then
l_num_segs := l_num_segs +1;
if(seg.value_set_id isnotnull)then
l_num_segs_vs := l_num_segs_vs +1;
endif;
endif;
endif;
l_sec_enabled :='N';
if(seg.seg_security ='Y'and seg.value_set_security in('Y','H'))then
l_any_sec_enabled :='Y';
l_sec_enabled :='Y';
endif;
if(seg.value_set_id isnotnull)then
l_value_set_str :=', Value Set = '||seg.value_set_name||
', Value Set Type = '||seg.validation_type;
else
l_value_set_str :=' with no value set assigned';
endif;
Tab2Print('Segment Name = '||seg.seg_name);
Tab2Print('Enabled = '||seg.enabled||', Displayed = '||
seg.displayed||l_value_set_str);
for qual in get_qualifiers(p_flex_code,str.flex_str_num,seg.col_name)loop
Tab3Print('Qualifier '||qual.segment_prompt||' is assigned to '||
'segment '|| seg.seg_name);
endloop;
if l_sec_enabled ='Y'then
selectcount(*)into l_rule_count
from fnd_flex_value_rules_vl
where flex_value_set_id = seg.value_set_id;
selectcount(*)into l_rule_assign_count
from fnd_flex_value_rules_vl r,
fnd_flex_value_rule_usages ru
where r.flex_value_rule_id = ru.flex_value_rule_id
and r.flex_value_set_id = seg.value_set_id;
Tab3Print('Security is enabled for this segment and value set with '||
to_char(l_rule_count)||' rules defined and '||
to_char(l_rule_assign_count)||' rule assignments');
endif;
endloop;
if(p_flex_num isnotnull)then
l_ret_array(6):=to_char(l_num_segs);
l_ret_array(7):=to_char(l_num_segs_vs);
l_ret_array(8):= l_any_sec_enabled;
endif;
if l_counter2 =0then
ErrorPrint('There are no segments defined for this structure');
ActionErrorPrint('Please enable or define at least one segment for '||
str.flex_str_name);
endif;
endloop;
if l_counter =0then
if p_flex_num isnullthen
ErrorPrint('There are no Key Flexfields enabled for '|| p_flex_code);
ActionErrorPrint('Please enable or define a Key Flexfield for '||
p_flex_code);
else
ErrorPrint('The requested flexfield structure (ID_FLEX_NUM='||
to_char(p_flex_num)||') is inactive or does not exist');
ActionErrorPrint('Verify that the flexfield structure is defined '||
'and enabled for Key Flexfield '||p_flex_code);
endif;
endif;
return l_ret_array;
exception
when leave_api then
return l_ret_array;
end CheckKeyFlexfield;
procedure CheckKeyFlexfield(p_flex_code invarchar2
, p_flex_num innumberdefaultnull
, p_print_heading inbooleandefaulttrue) is
dummy_v2t V2T;
begin
dummy_v2t := CheckKeyFlexfield(p_flex_code, p_flex_num, p_print_heading);
end CheckKeyFlexfield;
-- Function Name: CheckProfile
--
-- Usage:
-- CheckProfile('Profile Name', UserID, ResponsibilityID,
-- ApplicationID, 'Default Value', Indent Level, Align At);
--
-- Parameters:
-- Profile Name - System name of the profile option being checked
-- UserID - The identifier of that applications user for which the
-- profile option is to be checked.
-- ResponsibilityID - The identifier of the responsibility for which
-- the profile option is to be checked
-- ApplicationID - The identifier of the application for which the profile
-- option is to be checked
-- Default Value - The value that will be used as a default if the profile
-- option is not set by the users (Default=NULL)
-- Indent Level - Number of tabs (0,1,2,3) that output should be indented
-- (Default=0)
-- Align At - Not used in HTML API's. Only present for consistency
-- with text API's where this will place the '=' at the
-- indicated character position from the beginning of the
-- text.
--
-- Returns:
-- If called as a function the return value will be either --
-- 1 the value of the profile option if set
-- 2 'DOESNOTEXIST' if the profile option does not exist
-- 3 'DISABLED' if the profile option has been end dated
-- 4 null if the profile option is not set
--
-- Output:
-- If the profile is set, displays its current setting. If not set and
-- a default value exists, displays a warning indicating that the default
-- value will be used and indicating the value of the default. If not set
-- and no default value is supplied, displays an error indicating that
-- the profile option should be set. Output will be indented according
-- to the Indent Level parameter supplied.
--
-- If the profile option does not exist or is disabled there is no
-- output.
--
-- Examples:
-- declare
-- profile_val fnd_profile_option_values.profile_option_value%type;
-- begin
-- profile_val := CheckProfile('PA_SELECTIVE_FLEX_SEG',g_user_id,
-- g_resp_id, g_appl_id, null, 1);
-- end;
--
function CheckProfile(p_prof_name invarchar2
, p_user_id innumber
, p_resp_id innumber
, p_appl_id innumber
, p_default invarchar2defaultnull
, p_indent inintegerdefault0
, p_align_at inintegerdefault0)
returnvarchar2is
l_user_prof_name fnd_profile_options_tl.user_profile_option_name%type;
l_prof_value fnd_profile_option_values.profile_option_value%type;
l_start_date date;
l_end_date date;
l_opt_defined boolean;
l_output_txt varchar2(500);
begin
begin
select user_profile_option_name,
nvl(start_date_active,sysdate-1),
nvl(end_date_active,sysdate+1)
into l_user_prof_name, l_start_date, l_end_date
from fnd_profile_options_vl
where profile_option_name = p_prof_name;
exception
whenno_data_foundthen
l_prof_value :='DOESNOTEXIST';
return(l_prof_value);
whenothersthen
ErrorPrint(sqlerrm||' occured while getting profile option '||
'information');
ActionErrorPrint('Report the above information to your support '||
'representative');
return(null);
end;
if((sysdate< l_start_date)or(sysdate> l_end_date))then
l_prof_value :='DISABLED';
return(l_prof_value);
endif;
fnd_profile.get_specific(p_prof_name, p_user_id, p_resp_id, p_appl_id,
l_prof_value, l_opt_defined);
ifnot l_opt_defined then
l_prof_value :=null;
endif;
if l_prof_value isnullthen
if p_default isnullthen
ErrorPrint(l_user_prof_name ||' profile option is not set');
ActionErrorPrint('Please set the profile option according to '||
'the user manual');
return(l_prof_value);
else
WarningPrint(l_user_prof_name ||' profile option is not set '||
'and will default to '|| p_default);
ActionWarningPrint('Please set the profile option according to '||
'the user manual if you do not want to use the default');
return(l_prof_value);
endif;
else
l_output_txt := l_user_prof_name ||' = "'|| l_prof_value||'"';
if p_indent =1then
Tab1Print(l_output_txt);
elsif p_indent =2then
Tab2Print(l_output_txt);
elsif p_indent =3then
Tab3Print(l_output_txt);
else
Tab0Print(l_output_txt);
endif;
return(l_prof_value);
endif;
exceptionwhenothersthen
ErrorPrint(sqlerrm||' occured in CheckProfile');
ActionErrorPrint('Please report this error to your support representative');
end CheckProfile;
-- Procedure Name: CheckProfile
--
-- Usage:
-- CheckProfile('Profile Name', UserID, ResponsibilityID,
-- ApplicationID, 'Default Value', Indent Level, Align At);
--
-- Parameters:
-- Profile Name - System name of the profile option being checked
-- UserID - The identifier of that applications user for which the
-- profile option is to be checked.
-- ResponsibilityID - The identifier of the responsibility for which
-- the profile option is to be checked
-- ApplicationID - The identifier of the application for which the profile
-- option is to be checked
-- Default Value - The value that will be used as a default if the profile
-- option is not set by the users (Default=NULL)
-- Indent Level - Number of tabs (0,1,2,3) that output should be indented
-- (Default=0)
-- Align At - Not used in HTML API's. Only present for consistency
-- with text API's where this will place the '=' at the
-- indicated character position from the beginning of the
-- text.
--
-- Output:
-- Same as the function CheckProfile
--
-- Examples:
-- CheckProfile('PA_DEBUG_MODE',g_user_id, g_resp_id, g_appl_id);
-- CheckProfile('PA_DEBUG_MODE',g_user_id, g_resp_id, g_appl_id,'Y',2);
--
procedure CheckProfile(p_prof_name invarchar2
, p_user_id innumber
, p_resp_id innumber
, p_appl_id innumber
, p_default invarchar2defaultnull
, p_indent inintegerdefault0
, p_align_at inintegerdefault0)is
l_dummy_prof_value fnd_profile_option_values.profile_option_value%type;
begin
l_dummy_prof_value := CheckProfile(p_prof_name, p_user_id, p_resp_id,
p_appl_id, p_default, p_indent, p_align_at);
end CheckProfile;
-- Function Name: Column_Exists
--
-- Usage:
-- Column_Exists('Table Name','Column Name');
--
-- Paramteters:
-- Table Name - Table in which to check for the column
-- Column Name - Column to check
--
-- Returns:
-- 'Y' if the column exists in the table, 'N' if not.
--
-- Example:
-- declare
-- sqltxt varchar2(1000);
-- begin
-- if Column_Exists('PA_IMPLEMENTATIONS_ALL','UTIL_SUM_FLAG') = 'Y' then ;
-- sqltxt := sqltxt||' and i.util_sum_flag is not null';
-- end if;
-- end;
--
function Column_Exists(p_tab invarchar, p_col invarchar)returnvarchar2is
l_counter integer:=0;
begin
selectcount(*)into l_counter
from all_tab_columns
where table_name =upper(p_tab)
and column_name =upper(p_col);
if l_counter >0then
return('Y');
else
return('N');
endif;
exceptionwhenothersthen
ErrorPrint(sqlerrm||' occured in Column_Exists');
ActionErrorPrint('Report this information to your support analyst');
raise;
end Column_Exists;
-- Procedure Name: Begin_Pre
--
-- Usage:
-- Begin_Pre;
--
-- Output:
-- Allows the following output to be preformatted
--
-- Examples:
-- begin
-- Begin_Pre;
-- end;
--
procedure Begin_Pre is
begin
line_out('<pre>');
end Begin_Pre;
-- Procedure Name: End_Pre
--
-- Usage:
-- End_Pre;
--
-- Output:
-- Closes the Begin_Pre procedure
--
-- Examples:
-- begin
-- End_Pre;
-- end;
--
procedure End_Pre is
begin
line_out('</pre>');
end End_Pre;
procedure Show_Table(p_type varchar2, p_values V2T, p_caption varchar2defaultnull, p_options V2T defaultnull)is
l_hold_option varchar2(500);
begin
ifupper(p_type)in('START','TABLE')then
if p_caption isnullthen
line_out('<table cellspacing=0>');
else
line_out('<br><table cellspacing=0 summary="'|| p_caption ||'">');
endif;
endif;
ifupper(p_type)in('TABLE','ROW','HEADER')then
line_out('<tr>');
for i in1..p_values.COUNTloop
if p_options isnotnullthen
l_hold_option :=' '|| p_options(i);
endif;
if p_values(i)=' '
or p_values(i)isnullthen
ifupper(p_type)='HEADER'then
line_out('<th id='|| i ||'> </th>');
else
line_out('<td headers='|| i ||'> </td>');
endif;
else
ifupper(p_type)='HEADER'then
line_out('<th'|| l_hold_option ||' id='|| i ||'>'|| p_values(i)||'</th>');
else
line_out('<td'|| l_hold_option ||' headers='|| i ||'>'|| p_values(i)||'</td>');
endif;
endif;
endloop;
line_out('</tr>');
endif;
ifupper(p_type)in('TABLE','END')then
line_out('</TABLE>');
endif;
end Show_Table;
procedure Show_Table(p_values V2T)is
begin
Show_Table('TABLE',p_values);
end Show_Table;
procedure Show_Table(p_type varchar2)is
begin
Show_Table(p_type,null);
end Show_Table;
procedure Show_Table_Row(p_values V2T, p_options V2T defaultnull)is
begin
Show_Table('ROW', p_values,null, p_options);
end Show_Table_Row;
procedure Show_Table_Header(p_values V2T, p_options V2T defaultnull)is
begin
Show_Table('HEADER', p_values,null, p_options);
end Show_Table_Header;
procedure Start_Table (p_caption varchar2defaultnull)is
begin
Show_Table('START',null, p_caption);
end Start_Table;
procedure End_Table is
begin
Show_Table('END',null);
end End_Table;
