AME Debug Scripts
Purpose:
This AME setup output allows support and development to analyze your approval setup.
Requirement:
Internal ID (application_id)
AME assigns a internal ID (also known as AME application ID) for each transaction type. The utility script generates the setup report based on this ID. The following details on how to identify the correct application ID for your Transaction Type.
Run the following SQL from SQLPLUS under ‘apps’ account.
Select decode(
to_char(nvl(fnd_profile.value('AME_INSTALLATION_LEVEL')
,'Pre-AME.B'))
,'Pre-AME.B','Pre-AME.B'
,'1','Pre-AME.B'
,'1.1','Pre-AME.B'
,'2','Post-AME.B'
,'Post-AME.B')
From Dual;
If the value returned 'Pre-AME.B' then do the following
1. Login to AME Application using ‘AME Application Administrator’ responsibility.
2. Select ‘Admin’ tab
3. Choose "Application Administration" and continue.
4. Choose "Maintain Transaction Types" and continue.
5. Click on your transaction type.
6. AME-Internal ID of the chosen transaction type will be displayed on "Edit Transaction Type" page.
If the value returned by step is 'Post-AME.B' then do the following
1. Login to AME Application using "Approvals Management Administrator" responsibility.
2. Search for your transaction type
3. Click on the transaction type
4. AME-Internal ID of the chosen transaction type will be displayed.
Or you may use the script below to obtain the ID
select fnd.application_name
,aca.application_name
,aca.transaction_type_id
from fnd_application_vl fnd
,ame_calling_apps aca
where aca.fnd_application_id = fnd.application_id
and aca.application_id = to_number(:p_application_id)
and sysdate between aca.start_date and nvl(aca.end_date - (1/86400),sysdate);
Note, there are over 200 possible application names, of which iRecruitment has over 4 different
choices. The query option should only be used if you know the application_name.
Select decode(to_char(
nvl(fnd_profile.value('AME_INSTALLATION_LEVEL')
,'Pre-AME.B'))
,'Pre-11.5.10','Pre-11.5.10'
,'Post-11.5.10')
From Dual;
If the value returned by step is ‘Pre-11.5.10', then run the script 1
If the value returned by step is ‘Post-11.5.10', then run the script 2
The utility script should be run in an SQLPLUS session using ‘apps’ account. The AME-internalID identified in Step 1 should be applied as a parameter to the script as specified below.
Usage : sqlplus apps/<pwd>@dbname @file_name AME-InternalID
Example : sqlplus apps/apps@hrprodenv @get_ame_setup -200
Note: The output file should write the directory in which the script was executed from.
Script1:
--usage example: sqlplus @apps/apps/juldbgqa @file_name application_id
SET serveroutput on size 1000000
SET feedback off
spool Report_AppId&1..htm
DECLARE
TYPE rule_id_table IS TABLE OF ame_rules.rule_id%TYPE
INDEX BY BINARY_INTEGER;
TYPE rule_desc_table IS TABLE OF ame_rules.description%TYPE
INDEX BY BINARY_INTEGER;
TYPE action_id_table IS TABLE OF ame_actions.action_id%TYPE
INDEX BY BINARY_INTEGER;
TYPE action_desc_table IS TABLE OF ame_actions.description%TYPE
INDEX BY BINARY_INTEGER;
TYPE action_type_name_table IS TABLE OF ame_action_types.NAME%TYPE
INDEX BY BINARY_INTEGER;
TYPE action_parameter_table IS TABLE OF ame_actions.parameter%TYPE
INDEX BY BINARY_INTEGER;
TYPE action_type_id_table IS TABLE OF ame_action_types.action_type_id%TYPE
INDEX BY BINARY_INTEGER;
TYPE condn_id_table IS TABLE OF ame_conditions.condition_id%TYPE
INDEX BY BINARY_INTEGER;
TYPE condn_type_table IS TABLE OF VARCHAR2 (25)
INDEX BY BINARY_INTEGER;
TYPE attr_name_table IS TABLE OF ame_attributes.NAME%TYPE
INDEX BY BINARY_INTEGER;
TYPE attr_type_table IS TABLE OF ame_attributes.attribute_type%TYPE
INDEX BY BINARY_INTEGER;
TYPE attr_value_table IS TABLE OF ame_attribute_usages.query_string%TYPE
INDEX BY BINARY_INTEGER;
TYPE attr_is_line_item_table IS TABLE OF ame_attributes.line_item%TYPE
INDEX BY BINARY_INTEGER;
TYPE rule_type_table IS TABLE OF varchar2(30)
INDEX BY BINARY_INTEGER;
TYPE rule_type_num_table is table of number
index by binary_integer;
TYPE is_static_table IS TABLE OF ame_attribute_usages.is_static%TYPE
INDEX BY BINARY_INTEGER;
TYPE approval_group_id_table IS TABLE OF ame_approval_groups.approval_group_id%TYPE
INDEX BY BINARY_INTEGER;
TYPE approval_group_name_table IS TABLE OF ame_approval_groups.name%TYPE
INDEX BY BINARY_INTEGER;
TYPE approval_group_par_name_table IS TABLE OF ame_approval_group_items.parameter_name%TYPE
INDEX BY BINARY_INTEGER;
TYPE approval_group_par_table IS TABLE OF ame_approval_group_items.parameter%TYPE
INDEX BY BINARY_INTEGER;
TYPE config_vars_name_table IS TABLE OF ame_config_vars.variable_name%TYPE
INDEX BY BINARY_INTEGER;
TYPE config_vars_value_table IS TABLE OF ame_config_vars.variable_value%TYPE
INDEX BY BINARY_INTEGER;
TYPE rule_usages_start_date IS TABLE OF ame_rule_usages.start_date%TYPE
INDEX BY BINARY_INTEGER;
TYPE rule_usages_lifespan IS TABLE OF varchar2(100)
INDEX BY BINARY_INTEGER;
TYPE rule_usages_priority IS TABLE OF ame_rule_usages.priority%TYPE
INDEX BY BINARY_INTEGER;
CURSOR c_rule (app_id NUMBER) IS
SELECT amerl.rule_id, amerl.description,
DECODE(
amerl.rule_type,
1,'List Creation Rules',
2,'List Creation Exception Rules',
3,'List Modification Rules',
4,'Substitution Rules',
5,'Pre List Rules',
6,'Post List Rules',
'Undefined Rules'
),amerl.rule_type
FROM ame_rules amerl
WHERE amerl.rule_id IN (SELECT rule_id
FROM ame_rule_usages
WHERE item_id = app_id
AND
SYSDATE <= NVL((end_date-(1/86400)),SYSDATE)
)
and sysdate <= nvl((amerl.end_date-(1/86400)),sysdate)
order by amerl.rule_type;
CURSOR c_rule_usages (appid NUMBER,ruleid NUMBER) IS
SELECT ameru.start_date,
DECODE(ameru.end_date,null,start_date || ' Onward',start_date||' to '|| (ameru.end_date -(1/86400))),
ameru.priority
FROM ame_rule_usages ameru
WHERE ameru.item_id = appid
AND ameru.rule_id = ruleid
AND sysdate <= NVL(ameru.end_date-(1/86400),SYSDATE);
CURSOR c_action (ruleid number) is
select ameact.action_id, act.name, ameact.description
from ame_actions ameact, ame_action_types act
where act.action_type_id = ameact.action_type_id
and ameact.action_id in (select action_id
from ame_rules
where rule_id = ruleid
and sysdate <=nvl (end_date-(1/86400),sysdate)
)
and sysdate <= nvl (act.end_date-(1/86400),sysdate)
and sysdate <= nvl (ameact.end_date-(1/86400),sysdate);
CURSOR c_action_types is
select act.action_type_id, act.name
from ame_action_types act
where act.action_type_id <> -1
and sysdate between act.start_date and nvl(act.end_date-(1/86400),sysdate);
CURSOR c_action_type_attr_details (act_typ_id number) is
select ameattr.name, ameattr.attribute_type
from ame_attributes ameattr
where ameattr.attribute_id in (select attribute_id
from ame_mandatory_attributes
where action_type_id = act_typ_id
and sysdate between start_date and
nvl(end_date-(1/86400),sysdate)
)
and sysdate between ameattr.start_date AND NVL(ameattr.end_date-(1/86400),SYSDATE)
ORDER BY ameattr.NAME;
CURSOR c_action_type_action_details (act_typ_id NUMBER) IS
SELECT ameact.action_id, ameact.description,
ameact.parameter
FROM ame_actions ameact
WHERE ameact.action_type_id = act_typ_id
AND SYSDATE BETWEEN ameact.start_date AND NVL(ameact.end_date-(1/86400),SYSDATE)
ORDER BY ameact.action_id;
CURSOR c_condition (ruleid NUMBER) IS
SELECT amecon.condition_id,
DECODE(amecon.condition_type,'auth',' ','pre','Exception : ','post', 'List Modification : ')
FROM ame_conditions amecon
WHERE amecon.condition_id IN (SELECT condition_id
FROM ame_condition_usages
WHERE rule_id = ruleid
AND SYSDATE <=NVL (end_date-(1/86400),SYSDATE)
)
AND SYSDATE <=NVL (amecon.end_date-(1/86400),SYSDATE);
CURSOR c_attr_details_mandatory_item (app_id NUMBER) IS
SELECT ameattr.NAME attribute_name, ameattr.attribute_type,ameatusg.query_string,
ameatusg.is_static
FROM ame_attributes ameattr, ame_attribute_usages ameatusg
WHERE ameattr.attribute_id IN (SELECT attribute_id
FROM ame_mandatory_attributes amemattr
WHERE action_type_id = -1
AND SYSDATE BETWEEN amemattr.start_date AND
NVL (amemattr.end_date-(1/86400),SYSDATE)
)
AND ameattr.attribute_id = ameatusg.attribute_id
AND ameatusg.application_id = app_id
AND SYSDATE BETWEEN ameattr.start_date AND NVL(ameattr.end_date-(1/86400),SYSDATE)
AND SYSDATE BETWEEN ameatusg.start_date AND NVL(ameatusg.end_date-(1/86400),SYSDATE)
ORDER BY ameattr.line_item, ameattr.NAME;
CURSOR c_attr_details_header_item (app_id NUMBER) IS
SELECT ameattr.NAME attribute_name, ameattr.attribute_type, ameatusg.query_string,
ameatusg.is_static
FROM ame_attributes ameattr, ame_attribute_usages ameatusg
WHERE ameattr.attribute_id NOT IN (SELECT attribute_id
FROM ame_mandatory_attributes amemattr
WHERE action_type_id = -1
AND SYSDATE BETWEEN amemattr.start_date AND
NVL(amemattr.end_date-(1/86400),SYSDATE)
)
AND ameattr.attribute_id = ameatusg.attribute_id
AND ameatusg.application_id = app_id
AND (ameattr.line_item IS NULL OR ameattr.line_item = 'N')
AND SYSDATE BETWEEN ameattr.start_date AND NVL(ameattr.end_date-(1/86400),SYSDATE)
AND SYSDATE BETWEEN ameatusg.start_date AND NVL((ameatusg.end_date-(1/86400)),SYSDATE)
ORDER BY ameattr.line_item, ameattr.NAME;
CURSOR c_attr_details_line_item (app_id NUMBER) IS
SELECT ameattr.NAME attribute_name, ameattr.attribute_type,ameatusg.query_string,
ameatusg.is_static
FROM ame_attributes ameattr, ame_attribute_usages ameatusg
WHERE ameattr.attribute_id NOT IN (SELECT attribute_id
FROM ame_mandatory_attributes amemattr
WHERE action_type_id = -1
AND SYSDATE BETWEEN amemattr.start_date AND
NVL(amemattr.end_date-(1/86400),SYSDATE)
)
AND ameattr.attribute_id = ameatusg.attribute_id
AND ameatusg.application_id = app_id
AND ameattr.line_item = 'Y'
AND SYSDATE BETWEEN ameattr.start_date AND NVL(ameattr.end_date-(1/86400),SYSDATE)
AND SYSDATE BETWEEN ameatusg.start_date AND NVL(ameatusg.end_date-(1/86400),SYSDATE)
ORDER BY ameattr.line_item, ameattr.NAME;
CURSOR c_approval_groups IS
SELECT ameapg.approval_group_id,ameapg.name,ameapg.query_string,
ameapg.is_static
FROM ame_approval_groups ameapg
WHERE SYSDATE BETWEEN ameapg.start_date AND
NVL(ameapg.end_date-(1/86400),SYSDATE)
ORDER BY ameapg.is_static,ameapg.approval_group_id;
CURSOR c_approval_group_items (approval_group NUMBER) IS
SELECT ameapgi.approval_group_item_id, ameapgi.parameter_name,
ameapgi.parameter, ameapgi.order_number
FROM ame_approval_group_items ameapgi
WHERE ameapgi.approval_group_id = approval_group
AND SYSDATE BETWEEN ameapgi.start_date AND
NVL(ameapgi.end_date-(1/86400),SYSDATE)
ORDER BY ameapgi.order_number;
CURSOR c_config_vars (app_id NUMBER) IS
SELECT amecv.variable_name,amecv.variable_value
FROM ame_config_vars amecv
WHERE amecv.application_id = app_id
AND sysdate BETWEEN amecv.start_date AND
NVL(amecv.end_date-(1/86400),sysdate);
CURSOR c_config_default_vars IS
SELECT amecv.variable_name,amecv.variable_value
FROM ame_config_vars amecv
WHERE amecv.application_id is null
AND sysdate BETWEEN amecv.start_date AND
NVL(amecv.end_date-(1/86400),sysdate);
tx_typ_id VARCHAR2 (50);
txname VARCHAR2 (240);
prodname VARCHAR2 (240);
appid NUMBER;
ruleid ame_rules.rule_id%TYPE := 0;
s_no NUMBER;
color_code NUMBER := 1;
color_tag VARCHAR2 (7);
line_item_query VARCHAR2 (4000);
attribute_typ VARCHAR2 (1);
is_static_details is_static_table;
attr_name_details attr_name_table;
attr_type_details attr_type_table;
attr_values attr_value_table;
PROCEDURE printline (line VARCHAR2) IS
is_exist BOOLEAN := TRUE;
printstr VARCHAR2 (255);
idx NUMBER := 1;
BEGIN
WHILE is_exist LOOP
printstr := SUBSTR (line, idx, 255);
IF (LENGTH(printstr) IS NULL OR LENGTH (printstr) = 0) THEN
is_exist := FALSE;
ELSE
idx := idx + 255;
DBMS_OUTPUT.put_line (printstr);
END IF;
END LOOP;
END printline;
FUNCTION get_priority (appid NUMBER,ruletype NUMBER) RETURN boolean is
v_appid number;
v_ruletype number;
v_variable_value varchar2(200);
v_left_position number;
v_right_position number;
v_position_value varchar2(20);
begin
v_appid:=appid;
v_ruletype := ruletype;
begin
select x.variable_value into v_variable_value
from ame_config_vars x
where x.application_id=appid and
x.variable_name = 'rulePriorityModes' and
sysdate between x.start_date and nvl(x.end_date-(1/86400),sysdate);
exception
when no_data_found then
select x.variable_value into v_variable_value
from ame_config_vars x
where x.application_id is null and
x.variable_name = 'rulePriorityModes' and
sysdate between x.start_date and nvl(x.end_date-(1/86400),sysdate);
end;
v_variable_value := ':'||v_variable_value||':';
v_left_position := instr(v_variable_value,':',1,v_ruletype);
v_right_position := instr(v_variable_value,':',1,v_ruletype+1);
v_position_value := substr(v_variable_value,v_left_position+1,v_right_position-v_left_position-1);
if v_position_value = 'disabled' then
return false;
else
return true;
end if;
end get_priority;
procedure print_attributes (attribute_title varchar2,attr_name_details attr_name_table,attr_type_details attr_type_table,
is_static_details is_static_table,attr_values attr_value_table) is
begin
IF attr_name_details.COUNT > 0 THEN
printline ('<a name="'||attribute_title||'"></a><br>');
printline ('<a href="#top">Top</a>');
printline ('<TABLE width="100%" BORDER ="1" cellspacing=0><TR bgcolor="#000099"><TD colspan="4"><B><FONT COLOR="#00F2FA" FACE="arial,helvetica">'||attribute_title||' Attributes'||' :</FONT></B></TD></TR>');
printline ('<BR><TR bgcolor="#00F2FA"><TD>Attribute Name</TD><TD>Attribute Type</TD><TD>Static</TD><TD>Attribute Value</TD></TR>');
FOR attr_idx IN 1 .. attr_name_details.COUNT LOOP
printline ('<TR><TD width=20%>' || attr_name_details (attr_idx) || '</TD><TD>' || attr_type_details (attr_idx));
printline ('</TD><TD>');
printline(is_static_details (attr_idx));
printline ('</TD><TD>');
printline (NVL (TRIM (attr_values (attr_idx)), '.'));
printline ('</TD></TR>');
END LOOP;
printline ('</TABLE>');
END IF;
end print_attributes;
procedure print_rules_actions is
rule_details rule_id_table;
rule_desc_details rule_desc_table;
rule_type_details rule_type_table;
rule_type_num_details rule_type_num_table;
action_details action_id_table;
action_desc_details action_desc_table;
action_type_details action_type_name_table;
condn_details condn_id_table;
condn_type_details condn_type_table;
rule_usages_start_det rule_usages_start_date;
rule_usages_lifespan_det rule_usages_lifespan;
rule_usages_priority_det rule_usages_priority;
v_status boolean;
rule_type_state varchar2(30);
begin
printline ('<TABLE width="100%" BORDER ="1" cellspacing=0><TR bgcolor="#000099"><TD align=center><B><FONT COLOR="#00F2FA" FACE="arial,helvetica">Rule Details</FONT></B></TD></TR></TABLE>');
OPEN c_rule (appid);
FETCH c_rule BULK COLLECT INTO rule_details, rule_desc_details,rule_type_details,rule_type_num_details;
CLOSE c_rule;
rule_type_state:='initialized';
FOR idx IN 1 .. rule_details.COUNT LOOP
if rule_type_state <> rule_type_details (idx) then
printline ('<a name="Rule_Details'||rule_type_num_details (idx) || '"></a><br>');
rule_type_state := rule_type_details (idx);
printline ('<a href="#top">Top</a>');
printline (
'<TABLE width="100%" BORDER ="1" cellspacing=0><TR bgcolor="#000099"><TD align=left><B><FONT COLOR="#00F2FA" FACE="arial,helvetica">' || rule_type_state ||'</FONT></B></TD></TR></TABLE>'
);
end if;
printline ('<TABLE width="100%" BORDER ="1" cellspacing=0>');
printline ('<TR BGCOLOR="#00F2FA"><TD width=27%><B>Rule ID :' || rule_details (idx) || '</B></TD>');
printline ('<TD><B>Rule Description : ' || rule_desc_details (idx) || '</B></TD>');
printline ('</TR><TABLE width="100%" BORDER ="1" cellspacing=0>' || '<TR><TD width="12%" > </TD>');
printline ('<TD BGCOLOR="#cccc66" colspan="2"><B>' || 'Conditions :</B></TD></TR>');
OPEN c_condition (rule_details (idx));
FETCH c_condition BULK COLLECT INTO condn_details, condn_type_details;
CLOSE c_condition;
FOR con_idx IN 1 .. condn_details.COUNT LOOP
printline ('<TR><TD width="12%"> </TD><TD>');
printline (condn_details (con_idx) || '</TD><TD>');
printline (condn_type_details (con_idx) || ame_condition_pkg.getdescription (condn_details (con_idx)));
printline ('</TD></TR>');
END LOOP;
printline ('<TR><TD width="12%" > </TD><TD BGCOLOR="#cccc66" colspan="2"><B>' || 'Actions :</B></TD>');
printline('</TR>');
OPEN c_action (rule_details (idx));
FETCH c_action BULK COLLECT INTO action_details, action_type_details,action_desc_details;
CLOSE c_action;
FOR act_idx IN 1 .. action_details.COUNT LOOP
printline ('<TR><TD width="12%"> </TD><TD>');
printline (action_details (act_idx) || '</TD><TD>');
printline (action_type_details (act_idx) || ' : ' || action_desc_details (act_idx) || '</TD></TR>');
END LOOP;
--rule usages
OPEN c_rule_usages (appid,rule_details (idx));
FETCH c_rule_usages BULK COLLECT INTO rule_usages_start_det, rule_usages_lifespan_det,
rule_usages_priority_det;
CLOSE c_rule_usages;
printline ('<TR><TD width="12%" > </TD><TD BGCOLOR="#cccc66" colspan="2"><B>' || 'Usages :</B></TD>');
printline('<TR ><TD width="12%"></TD><TD bgcolor="#cccc66">Lifespan</TD><TD bgcolor="#cccc66">Priority</TD></TR>');
FOR usage_idx IN 1 .. rule_usages_start_det.COUNT LOOP
printline ('<TR><TD width="12%"> </TD><TD>');
if rule_usages_start_det (usage_idx) > sysdate then
printline('**' || rule_usages_lifespan_det (usage_idx) ||'</TD><TD>');
else
printline (rule_usages_lifespan_det (usage_idx) || '</TD><TD>');
end if;
--print priority if not disabled
v_status := get_priority(appId,rule_type_num_details(idx));
if v_status = true then
printline ( rule_usages_priority_det (usage_idx)|| '</TD></TR>');
else
printline ( 'disabled' || '</TD></TR>');
end if;
END LOOP;
printline ('</TABLE>');
END LOOP; --end for(227)
end print_rules_actions;
procedure print_approval_groups is
appr_grp_id_det approval_group_id_table;
appr_grp_query_det attr_value_table;
appr_grp_static_det is_static_table;
appr_grp_name_det approval_group_name_table;
appr_grp_item_id_det approval_group_id_table;
appr_par_nam_det approval_group_par_name_table;
appr_par_det approval_group_par_table;
appr_order_number approval_group_id_table;
v_approval_group_name varchar2(50);
begin
--approvers details
OPEN c_approval_groups;
FETCH c_approval_groups BULK COLLECT INTO appr_grp_id_det, appr_grp_name_det,
appr_grp_query_det,appr_grp_static_det;
CLOSE c_approval_groups;
printline ('<a name="Approval_Groups"></a><br>');
printline ('<a href="#top">Top</a>');
printline('<TABLE width="100%" BORDER ="1" cellspacing=0>');
printline('<TR bgcolor="#000099"><TD colspan="4" align="center"><B><FONT COLOR="#00F2FA" FACE="arial,helvetica">Approval Groups</FONT></B></TD></TR>');
FOR idx in 1..appr_grp_id_det.COUNT LOOP
printline('<TR bordercolor=blue><TD colspan="4" align="left">'||'<B>Group Id:</B>'||appr_grp_id_det (idx)||'<BR>');
printline('<B>Name:</B>'||appr_grp_name_det (idx)||'<BR>');
printline('<B>Static:</B>'||appr_grp_static_det (idx)||'<BR>');
if appr_grp_static_det (idx) = 'N' then
printline('<B>Query String:</B>'||appr_grp_query_det (idx)||'<BR>');
printline('</TD></TR>');
else
OPEN c_approval_group_items (appr_grp_id_det (idx));
FETCH c_approval_group_items BULK COLLECT INTO
appr_grp_item_id_det ,appr_par_nam_det,
appr_par_det,appr_order_number;
CLOSE c_approval_group_items;
if appr_grp_item_id_det.COUNT <> 0 then
printline('<TABLE width="100%" BORDER ="1" cellspacing=0>');
printline('<TR BGCOLOR="#cccc66"><TD><B>Order Number</B></TD><TD><B>Item Id</B></TD><TD><B>Parameter Type</B></TD><TD><B>Parameter Value</B></TD></TR>');
FOR idy in 1..appr_grp_item_id_det.COUNT LOOP
printline('<TR><TD>');
printline(appr_order_number (idy));
printline('</TD><TD>');
printline(appr_grp_item_id_det (idy));
printline('</TD><TD>');
printline(appr_par_nam_det (idy));
printline('</TD><TD>');
if appr_par_nam_det (idy) = 'OAM_group_id' then
select name into v_approval_group_name
from ame_approval_groups
where approval_group_id = appr_par_det (idy)
and sysdate between start_date and
nvl(end_date - (1/86400),sysdate);
printline(v_approval_group_name);
else
printline(appr_par_det (idy));
end if;
printline('</TD></TR>');
END LOOP;
printline('</TABLE>');
else
printline('<B>Members:</B> None');
end if;
printline('</TD></TR>');
end if;
END LOOP;
printline ('</TABLE>');
end print_approval_groups;
procedure print_configurations is
config_name_det config_vars_name_table;
config_value_det config_vars_value_table;
begin
--configuration variables for application
printline ('<a name="Config_Variables"></a><br>');
printline ('<a href="#top">Top</a>');
printline('<TABLE width="100%" BORDER ="1" cellspacing=0>');
printline('<TR bgcolor="#000099"><TD colspan="2" align="center"><B><FONT COLOR="#00F2FA" FACE="arial,helvetica">Configuration Variables</FONT></B></TD></TR>');
printline('<TR bgcolor="#00F2FA"><TD colspan="2" align="left"><B><FONT COLOR="#000099" FACE="arial,helvetica">Transaction Type Level</FONT></B></TD></TR>');
OPEN c_config_vars (appId);
FETCH c_config_vars BULK COLLECT INTO config_name_det,config_value_det;
CLOSE c_config_vars;
printline('<TR bgcolor="#cccc66"><TD>Variable Name</TD><TD>Variable Value</TD></TR>');
FOR idx in 1..config_name_det.COUNT LOOP
printline('<TR><TD>');
printline(config_name_det (idx));
printline('</TD><TD>');
printline(config_value_det (idx));
printline('</TD></TR>');
END LOOP;
printline('<TR bgcolor="#00F2FA"><TD colspan="2" align="left"><B><FONT COLOR="#000099" FACE="arial,helvetica">Default</FONT></B></TD></TR>');
printline('<TR bgcolor="#cccc66"><TD>Variable Name</TD><TD>Variable Value</TD></TR>');
OPEN c_config_default_vars;
FETCH c_config_default_vars BULK COLLECT INTO config_name_det,config_value_det;
CLOSE c_config_default_vars;
FOR idx in 1..config_name_det.COUNT LOOP
printline('<TR><TD>');
printline(config_name_det (idx));
printline('</TD><TD>');
printline(config_value_det (idx));
printline('</TD></TR>');
END LOOP;
printline ('</TABLE>');
end print_configurations;
procedure print_action_types is
action_details action_id_table;
action_desc_details action_desc_table;
action_type_details action_type_name_table;
action_param_details action_parameter_table;
act_typ_id_details action_type_id_table;
attr_name_details attr_name_table;
attr_type_details attr_type_table;
begin
--action_types
OPEN c_action_types;
FETCH c_action_types BULK COLLECT INTO act_typ_id_details, action_type_details;
CLOSE c_action_types;
IF act_typ_id_details.COUNT > 0 THEN
printline ('<BR><TABLE width="100%" BORDER ="1" cellspacing=0><TR bgcolor="#000099"><TD colspan="3" align="center"><B><FONT COLOR="#00F2FA" FACE="arial,helvetica">Action Types</FONT></B></TD></TR></table>');
printline ('<a name="Action_Types"></a>');
printline ('<a href="#top">Top</a><br>');
--give links to different action types
printline ('<a href ="#Action_Types">Action Types</a>');
printline ('<ul>');
FOR act_typ_idx in 1..act_typ_id_details.count loop
printline ('<li><a href = "#actiontype'||act_typ_id_details(act_typ_idx)||'">'||action_type_details(act_typ_idx)||'</a>');
end loop;
printline ('</ul>');
FOR act_typ_idx IN 1 .. act_typ_id_details.COUNT LOOP
printline ('<BR><a href="#Action_Types">Back to Action Types</a>');
printline ('<a name="actiontype'||act_typ_id_details (act_typ_idx) ||'"></a>');
printline ('<TABLE width="100%" BORDER ="1" cellspacing=0>');
printline ('<BR><TR BGCOLOR="#00F2FA"><TD width=27%><B>Action Type ID :' || act_typ_id_details (act_typ_idx) || '</B></TD>');
printline ('<TD colspan="2"><B>Action Type Description : ' || action_type_details (act_typ_idx) || '</B></TD>');
printline ('</TR>');
OPEN c_action_type_attr_details (act_typ_id_details (act_typ_idx));
FETCH c_action_type_attr_details BULK COLLECT INTO attr_name_details, attr_type_details;
CLOSE c_action_type_attr_details;
IF attr_name_details.COUNT > 0 THEN
printline ('<TR>');
printline ('<TD BGCOLOR="#cccc66" colspan="3"><B>' || 'Required Attributes :</B></TD>');
printline ('</TR>');
FOR attr_idx IN 1 .. attr_name_details.COUNT LOOP
printline ('<TR>');
printline ('<TD>' || attr_name_details (attr_idx) || '</TD>');
printline ('<TD colspan="2">' || attr_type_details (attr_idx) || '</TD>');
printline ('</TR>');
END LOOP;
END IF;
OPEN c_action_type_action_details (act_typ_id_details (act_typ_idx));
FETCH c_action_type_action_details BULK COLLECT INTO action_details, action_desc_details,action_param_details;
CLOSE c_action_type_action_details;
printline ('<TR BGCOLOR="#cccc66" >');
printline ('<TD ><B>' || 'Action Id</B></TD>');
printline('<TD><B>Action Description</B></TD>');
printline('<TD><B>Parameter</B></TD>');
printline ('</TR>');
IF action_details.COUNT > 0 THEN
FOR act_idx IN 1 .. action_details.COUNT LOOP
printline ('<TR>');
printline ('<TD>' || action_details (act_idx) || '</TD>');
printline ('<TD>' || action_desc_details (act_idx) || '</TD>');
printline('<TD>' || action_param_details (act_idx) || '</TD>');
printline ('</TR>');
END LOOP;
ELSE
printline ('<TR>');
printline ('<TD colspan="2" align="center">[none]</TD>');
printline ('</TR>');
END IF;
printline ('</TABLE>');
END LOOP;
END IF;
end print_action_types;
--main part of code
BEGIN
printline ('<!--');
appId := to_number(&1);
printline ('-->');
SELECT ameapp.application_name, fndapp.application_name,ameapp.application_id, ameapp.line_item_id_query,ameapp.transaction_type_id
INTO txname, prodname, appid, line_item_query, tx_typ_id
FROM ame_calling_apps ameapp, fnd_application_vl fndapp
WHERE ameapp.application_id = appId
AND fndapp.application_id = ameapp.fnd_application_id
AND SYSDATE BETWEEN ameapp.start_date AND NVL((ameapp.end_date-(1/86400)),SYSDATE);
printline ('<HTML>');
printline ('<head>');
printline ('<TITLE>AME Report</TITLE>');
printline ('<style type="text/css">');
printline ('a');
printline ('{');
printline ('color: blue;');
printline ('text-decoration: none;');
printline ('background: transparent;');
printline ('}');
printline ('a:hover');
printline ('{');
printline ('background: #ccddcc;');
printline ('text-decoration: none;');
printline ('color: #662222;');
printline ('}');
printline ('</style>');
printline ('</head>');
printline ('<BODY font="Arial">');
printline ('<p align=center><B><font size=+1>AME Setup Details for Transaction Type : ' || txname || '</font></B></p>');
printline ('<TABLE width="100%" BORDER ="1" cellspacing=0 BGCOLOR="#339999"><TR>');
printline ('<TD><B>Product Name : </B></TD><TD>' || prodname || '</TD></TR>');
printline ('<TR><TD><B>Transaction Type ID : </B></TD><TD>' || tx_typ_id || '</TD></TR>');
if line_item_query is not null then
printline ('<TR><TD><B>Line Item Query : </B></TD><TD>');
printline ( TRIM (line_item_query) ||'</TD><BR></TR>');
end if;
printline ('</TABLE><BR>');
--links
printline ('<a name="top"></a>');
printline ('<a href="#Rule_Details1"><B>Rule Details</B></a><br>');
printline ('<ul>');
printline ('<li><a href="#Rule_Details1"><B>List Creation Rules</B></a>');
printline ('<li><a href="#Rule_Details2"><B>List Creation Exception Rules</B></a>');
printline ('<li><a href="#Rule_Details3"><B>List Modification Rules</B></a>');
printline ('<li><a href="#Rule_Details4"><B>Substitution Rules</B></a>');
printline ('<li><a href="#Rule_Details5"><B>Pre List Rules</B></a>');
printline ('<li><a href="#Rule_Details6"><B>Post List Rules</B></a>');
printline ('</ul>');
printline ('<a href="#Mandatory"><B>Mandatory Attributes</B></a><br>');
printline ('<a href="#Header"><B>Header Attributes</B></a><br>');
if line_item_query is not null then
printline ('<a href="#Line"><B>Line Item Attributes</B></a><br>');
end if;
printline ('<a href="#Action_Types"><B>Action Types</B></a><br>');
printline ('<a href="#Approval_Groups"><B>Approval Groups</B></a><br>');
printline ('<a href="#Config_Variables"><B>Configuration Variables</B></a><br>');
--end of links code
--print rules and associated conditions and actions
print_rules_actions();
--mandatory_item
OPEN c_attr_details_mandatory_item (appid);
FETCH c_attr_details_mandatory_item BULK COLLECT
INTO attr_name_details,attr_type_details,attr_values,is_static_details;
CLOSE c_attr_details_mandatory_item;
print_attributes('Mandatory',attr_name_details,attr_type_details,is_static_details,attr_values);
--header_item
OPEN c_attr_details_header_item (appid);
FETCH c_attr_details_header_item BULK COLLECT INTO attr_name_details,attr_type_details,attr_values,
is_static_details;
CLOSE c_attr_details_header_item;
print_attributes('Header',attr_name_details,attr_type_details,is_static_details,attr_values);
--line_item
OPEN c_attr_details_line_item (appid);
FETCH c_attr_details_line_item BULK COLLECT INTO attr_name_details,attr_type_details,attr_values,
is_static_details;
CLOSE c_attr_details_line_item;
print_attributes('Line',attr_name_details,attr_type_details,is_static_details,attr_values);
--print action_types along with mandatory attributes and action ids of each action type
print_action_types();
--print approval groups and their members
print_approval_groups();
--print application and default configuration variables
print_configurations();
printline ('</BODY>');
printline ('</HTML>');
EXCEPTION
WHEN NO_DATA_FOUND THEN
printline ('No Data Found for the input.');
END;
/
SPOOL off;
EXIT;
Script2:
--usage example: sqlplus @apps/apps/juldbgqa @getPost11510AMESetup.sql -735
set serveroutput on size 1000000
set feedback off
set echo off
set termout off
spool Report_AppId&1..htm
declare
--+
applicationId number;
itemClassIdList ame_util.idList;
itemClassNamesList ame_util.stringList;
applicationFound boolean;
--+
type invalidApprRec is record(
roleName varchar2(200),
origsystem varchar2(75),
source varchar2(1000)
);
type invApprList is table of invalidApprRec index by binary_integer;
ame_invalappr invApprList;
--+
--+
procedure printLine (line varchar2) is
isExist boolean := true;
str varchar2 (255);
idx number := 1;
begin
while isExist loop
str := substr (line, idx, 255);
if (length(str) is null or length (str) = 0) then
isExist := false;
else
idx := idx + 255;
dbms_output.put_line(str);
end if;
end loop;
end printLine;
--+
procedure startHtml as
begin
printLine('<html>');
printLine('<head>');
printLine('<title>AME Report</title>');
printLine('<style type="text/css">');
printLine('.OraTableRowHeader');
printLine('{');
printLine(' font-family:Arial, Helvetica, Geneva, sans-serif;');
printLine(' font-size:10pt;');
printLine(' font-weight:bold;');
printLine(' text-align:left;');
printLine(' background-color:#cccc99;');
printLine(' color:#336699;');
printLine(' text-indent:1');
printLine('}');
printLine('tr');
printLine('{');
printLine(' font-family:Arial, Helvetica, Geneva, sans-serif;');
printLine(' font-size:10pt;');
printLine(' background-color:#f7f7e7;');
printLine(' color:#000000;');
printLine('}');
printLine('A:link');
printLine('{');
printLine(' font-family:Arial, Helvetica, Geneva, sans-serif;');
printLine(' font-size:10pt;');
printLine(' color:#663300');
printLine('}');
printLine('A:active');
printLine('{');
printLine(' font-family:Arial, Helvetica, Geneva, sans-serif;');
printLine(' font-size:10pt;');
printLine(' color:#ff6600');
printLine('}');
printLine('A:visited');
printLine('{');
printLine(' font-family:Arial, Helvetica, Geneva, sans-serif;');
printLine(' font-size:10pt;');
printLine(' color:#996633');
printLine('}');
printLine('</style>');
printLine('</head>');
printLine('<body bgcolor="#ffffff">');
end startHtml;
--+
procedure closeHtml as
begin
printLine('</body>');
printLine('</html>');
end closeHtml;
--+
procedure startTable as
begin
--printLine('<br>');
printLine('<table width="100%" border="1" cellspacing="0" >');
end startTable;
--+
procedure startTable2 as
begin
printLine('<br>');
printLine('<table width="100%" border="1" cellspacing="0" >');
end startTable2;
--+
procedure closeTable is
begin
printLine('</table>');
end closeTable;
--+
procedure startTableRow(styleIn in varchar2 default null) as
begin
if styleIn is not null then
printLine('<tr class='||styleIn||'>');
else
printLine('<tr>');
end if;
end startTableRow;
--+
procedure closeTableRow as
begin
printLine('</tr>');
end closeTableRow;
--+
procedure lineBreak as
begin
printLine('<br>');
end lineBreak;
--+
procedure anchor(nameIn in varchar2) as
begin
printLine('<a name='||nameIn||'></a><br>');
end anchor;
--+
procedure printTableCell(contentIn in varchar2
,colSpanIn in integer default null) as
printString varchar2(5000);
content ame_util.longestStringType;
contentLength integer;
begin
if colSpanIn is null then
printString := '<td>';
else
printString := '<td colspan="'||colspanIn||'">';
end if;
content := trim(contentIn);
contentLength := lengthb(content);
if contentLength = 0 then
content := '&'||'nbsp;';
end if;
printLine(printString||content||'</td>');
end printTableCell;
--+
--+
--+
procedure createLinktoTop as
begin
printLine('<p align=right><a href="#top">Top</a></p>');
end createLinktoTop;
--+
procedure createLink(link1In in varchar2
,link1DescIn in varchar2
,link2In in varchar2 default null
,link2DescIn in varchar2 default null) as
begin
printLine('<p align=right>');
printLine('<a href="#'||link1In||'">'||link1DescIn||'</a>&'||'nbsp;');
if link2In is not null then
printLine('<a href="#'||link2In||'">'||link2DescIn||'</a>&'||'nbsp;');
end if;
printLine('<a href="#top">Top</a></p>');
end createLink;
--+
--+
procedure createHtmlLinks as
begin
printLine('<ul>');
printLine('<li><a href="#icu">Item Classes</a>');
printLine('<li><a href="#man">Mandatory Attributes</a>');
printLine('<li><a href="#atr">Attributes</a>');
printLine('<li><a href="#othercond">Conditions</a>');
printLine('<li><a href="#rules">Rules</a>');
printLine('<li><a href="#act">Action Types</a>');
printLine('<li><a href="#TransAgr">Approval Groups</a>');
printLine('<li><a href="#cfg">Configuration Variables</a>');
printLine('<li><a href="#appr">Approver Type Details</a>');
printLine('<li><a href="#invAppr">Invalid Approvers</a>');
printLine('<li><a href="#inval">Invalid AME Objects </a>');
printLine('<li><a href="#AMEver">AME Patch Details</a>');
printLine('<li><a href="#FPver">Family Pack Version</a>');
printLine('<li><a href="#APPVer">Application Release Version</a>');
printLine('<li><a href="#file">AME Key File Version</a>');
printLine('</ul>');
end createHtmlLinks;
--+
--+
--+
procedure printTrasactionTypeInfo(applicationIdIn in number
,transactionTypeFound out boolean) as
fndApplicationName ame_util.stringType;
applicationName ame_util.stringType;
transactionTypeId ame_util.stringType;
cursor getTxTypeInfo is
select fnd.application_name
,aca.application_name
,aca.transaction_type_id
from fnd_application_vl fnd
,ame_calling_apps aca
where aca.fnd_application_id = fnd.application_id
and aca.application_id = applicationIdIn
and sysdate between aca.start_date and nvl(aca.end_date - (1/86400),sysdate);
begin
startTable;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'AME Setup Details');
closeTableRow;
closeTable;
open getTxTypeInfo;
fetch getTxTypeInfo into
fndApplicationName
,applicationName
,transactionTypeId;
if getTxTypeInfo%notfound then
transactionTypeFound := false;
startTable2;
startTableRow;
printTableCell(contentIn => 'applicationId '||applicationIdIn ||' Not Found');
closeTableRow;
close getTxTypeInfo;
return;
else
transactionTypeFound := true;
end if;
close getTxTypeInfo;
startTable2;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'Product Name');
printTableCell(contentIn => fndApplicationName);
closeTableRow;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'Transaction Type Name');
printTableCell(contentIn => applicationName);
closeTableRow;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'TransactionType ID');
printTableCell(contentIn => transactionTypeId);
closeTableRow;
closeTable;
end printTrasactionTypeInfo;
--+
--+
--+
procedure printItemClassUsages(applicationIdIn in number
,itemClassNamesOut out nocopy ame_util.stringList
,itemClassIdsOut out nocopy ame_util.idList)as
itemClassQueryList ame_util.longestStringList;
itemClassONList ame_util.idList;
itemClassPMList ame_util.stringList;
itemClassSLMList ame_util.stringList;
cursor itemClassUsageCursor(applicationIdIn in number)is
select ic.item_class_id
,ic.name
,icu.item_id_query
,icu.item_class_order_number
,decode(icu.item_class_par_mode
,'S'
,'Serial'
,'P'
,'Parallel'
,icu.item_class_par_mode)
,decode(icu.item_class_sublist_mode
,'S'
,'Serial'
,'P'
,'Parallel'
,'R'
,'pre-approvers first, then authority and post-approvers'
,'A'
,'pre-approvers and authority approvers first, then post-approvers'
,icu.item_class_sublist_mode)
from ame_item_classes ic
,ame_item_class_usages icu
where ic.item_class_id = icu.item_class_id
and icu.application_id = applicationIdIn
and sysdate between ic.start_date and nvl(ic.end_date - (1/86400),sysdate)
and sysdate between icu.start_date and nvl(icu.end_date - (1/86400),sysdate)
order by icu.item_class_order_number;
begin
open itemClassUsageCursor(applicationIdIn => applicationIdIn);
fetch itemClassUsageCursor bulk collect
into
itemClassIdsOut
,itemClassNamesOut
,itemClassQueryList
,itemClassONList
,itemClassPMList
,itemClassSLMList;
close itemClassUsageCursor;
anchor('icu');
startTable;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'Item Classes'
,colSpanIn => 6);
closeTableRow;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'ID');
printTableCell(contentIn => 'Name');
printTableCell(contentIn => 'QueryString');
printTableCell(contentIn => 'Order Number');
printTableCell(contentIn => 'Parallelisation Mode');
printTableCell(contentIn => 'Sub List Mode');
closeTableRow;
for i in 1 .. itemClassIdsOut.count loop
startTableRow;
printTableCell(contentIn => itemClassIdsOut(i));
printTableCell(contentIn => itemClassNamesOut(i));
printTableCell(contentIn => itemClassQueryList(i));
printTableCell(contentIn => itemClassONList(i));
printTableCell(contentIn => itemClassPMList(i));
printTableCell(contentIn => itemClassSLMList(i));
closeTableRow;
end loop;
closeTable;
end printItemClassUsages;
--+
--+
--+
procedure printAttributesByIC(applicationIdIn in number
,itemClassIdIn in number
,itemClassNameIn in varchar2 ) as
attributeIdList ame_util.idList;
attributeNamesList ame_util.stringList;
attributeQList ame_util.longestStringList;
attributeDescList ame_util.stringList;
attributeStaticList ame_util.charList;
attrUseCountList ame_util.stringList;
cursor attributeUsageCursor(applicationIdIn in number)is
select atr.attribute_id
,atr.name
,atu.query_string
,atr.description
,atu.is_static
,atu.use_count
from ame_attributes atr
,ame_attribute_usages atu
where atr.attribute_id = atu.attribute_id
and atu.application_id = applicationIdIn
and atr.item_class_id = itemClassIdIn
and sysdate between atr.start_date and nvl(atr.end_date - (1/86400),sysdate)
and sysdate between atu.start_date and nvl(atu.end_date - (1/86400),sysdate)
and atr.attribute_id not in
(select attribute_id
from ame_mandatory_attributes man
where man.action_type_id = -1
and sysdate between man.start_date and nvl(man.end_date - (1/86400),sysdate)
)
order by atr.name;
begin
open attributeUsageCursor(applicationIdIn => applicationIdIn);
fetch attributeUsageCursor bulk collect
into
attributeIdList
,attributeNamesList
,attributeQList
,attributeDescList
,attributeStaticList
,attrUseCountList;
close attributeUsageCursor;
anchor('atr');
startTable;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => initcap(itemClassNameIn) ||' Attributes'
,colspanIn => 5);
closeTableRow;
closeTable;
startTable;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'ID');
printTableCell(contentIn => 'Name');
printTableCell(contentIn => 'QueryString');
printTableCell(contentIn => 'Description');
printTableCell(contentIn => 'is Static');
printTableCell(contentIn => 'Use Count');
closeTableRow;
for i in 1 .. attributeIdList.count loop
startTableRow;
printTableCell(contentIn => attributeIdList(i));
printTableCell(contentIn => attributeNamesList(i));
printTableCell(contentIn => attributeQList(i));
printTableCell(contentIn => attributeDescList(i));
printTableCell(contentIn => attributeStaticList(i));
printTableCell(contentIn => attrUseCountList(i));
closeTableRow;
end loop;
closeTable;
createLinktoTop;
end printAttributesByIC;
--+
--+
--+
procedure printMandatoryAttributes(applicationIdIn in number) as
attributeIdList ame_util.idList;
attributeNamesList ame_util.stringList;
attributeQList ame_util.longestStringList;
attributeDescList ame_util.stringList;
attributeStaticList ame_util.charList;
cursor attributeUsageCursor(applicationIdIn in number)is
select atr.attribute_id
,atr.name
,atu.query_string
,atr.description
,atu.is_static
from ame_attributes atr
,ame_attribute_usages atu
where atr.attribute_id = atu.attribute_id
and atu.application_id = applicationIdIn
and sysdate between atr.start_date and nvl(atr.end_date - (1/86400),sysdate)
and sysdate between atu.start_date and nvl(atu.end_date - (1/86400),sysdate)
and atr.attribute_id in
(select attribute_id
from ame_mandatory_attributes man
where man.action_type_id = -1
and sysdate between man.start_date and nvl(man.end_date - (1/86400),sysdate)
)
order by atr.name;
begin
open attributeUsageCursor(applicationIdIn => applicationIdIn);
fetch attributeUsageCursor bulk collect
into
attributeIdList
,attributeNamesList
,attributeQList
,attributeDescList
,attributeStaticList;
close attributeUsageCursor;
anchor('man');
startTable;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'Mandatory attributes'
,colspanIn => 5);
closeTableRow;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'ID');
printTableCell(contentIn => 'Name');
printTableCell(contentIn => 'QueryString');
printTableCell(contentIn => 'Description');
printTableCell(contentIn => 'is Static');
closeTableRow;
for i in 1 .. attributeIdList.count loop
startTableRow;
printTableCell(contentIn => attributeIdList(i));
printTableCell(contentIn => attributeNamesList(i));
printTableCell(contentIn => attributeQList(i));
printTableCell(contentIn => attributeDescList(i));
printTableCell(contentIn => attributeStaticList(i));
closeTableRow;
end loop;
closeTable;
createLinktoTop;
end printMandatoryAttributes;
--+
--+
--+
/* function getActionDescription(actionIdIn in number)return varchar2 as
begin
return ame_action_pkg.getDescription(actionIdIn);
exception
when others then
return to_char(actionIdIn);
end getActionDescription; */
function checkDuplicate(expressionIn in varchar2) return boolean is
inval_index number;
begin
inval_index := ame_invalappr.count;
for i in 1..inval_index loop
if trim(expressionIn)=trim(ame_invalappr(i).source)then
return true;
end if;
end loop;
return false;
end ;
function getActionDescription(actionIdIn in number)return varchar2 as
actionTypeId number;
tempActionDescr varchar2(800);
beforeColon varchar2(800);
afterColon varchar2(800);
wf_role_name varchar2(200);
inval_index integer;
begin
tempActionDescr := ame_action_pkg.getDescription(actionIdIn);
actionTypeId := ame_action_pkg.getActionTypeIdById(actionIdIn =>actionIdIn);
if ame_action_pkg.getActionTypeName(actionTypeId) =ame_util.substitutionTypeName then
wf_role_name := ame_action_pkg.getParameter(actionIdIn);
if ame_approver_type_pkg.validateApprover(wf_role_name) then
null;
else
if checkDuplicate('actionId:'||actionIdIn)<> true then
inval_index := ame_invalappr.count;
ame_invalappr(inval_index+1).roleName := wf_role_name;
ame_invalappr(inval_index+1).source := 'actionId:'||actionIdIn;
end if;
end if;
end if;
return tempActionDescr;
exception
when others then
return to_char(actionIdIn);
end getActionDescription;
--+
--+
--+
function getConditionDescription(conditionIdIn in number)return varchar2 as
begin
return ame_condition_pkg.getDescription(conditionIdIn);
exception
when others then
return to_char(conditionIdIn);
end getConditionDescription;
--+
--+
--+
procedure printRules(applicationIdIn in number
,itemClassIdsIn in ame_util.idList
,itemClassNamesIn in ame_util.stringList) as
activeRuleIdList ame_util.idList;
activeRuleDescList ame_util.stringList;
conditionIdList ame_util.idList;
conditionTypeList ame_util.stringList;
actionIdList ame_util.idList;
startDateList ame_util.stringList;
endDateList ame_util.stringList;
priorityList ame_util.stringList;
categoryList ame_util.stringList;
ruleTypeDescList ame_util.stringList;
currentDesc ame_util.stringType;
currentLink ame_util.stringType;
rule_keyList ame_util.stringList;
cursor activRuleCursor(applicationIdIn in number
,itemClassIdIn in number
,ruleTypeIn in number) is
select rul.rule_id
,rul.description
,rul.rule_key
from ame_rules rul
,ame_rule_usages rlu
where rul.rule_type = ruleTypeIn
and ((ruleTypeIn = 3 or ruleTypeIn = 4) or (rul.item_class_id = itemClassIdIn))
and rul.rule_id = rlu.rule_id
and rlu.item_id = applicationIdIn
and (sysdate between rul.start_date and nvl(rul.end_date - (1/86400),sysdate)
or
rul.start_date > sysdate and (rul.end_date is null or rul.end_date > rul.start_date)
)
and (sysdate between rlu.start_date and nvl(rlu.end_date - (1/86400),sysdate)
or
rlu.start_date > sysdate and (rlu.end_date is null or rlu.end_date > rlu.start_date)
);
cursor conditionUsageCursor(ruleIdIn in number) is
select acu.condition_id
,decode(ac.condition_type,'auth',' ','pre','Exception : ','post', 'List-Modification : ')
from ame_condition_usages acu
,ame_conditions ac
where acu.rule_id = ruleIdIn
and ac.condition_id = acu.condition_id
and sysdate between ac.start_date and nvl(ac.end_date - (1/86400),sysdate)
and (sysdate between acu.start_date and nvl(acu.end_date - (1/86400),sysdate)
or
acu.start_date > sysdate and (acu.end_date is null or acu.end_date > acu.start_date)
);
cursor actionUsageCursor(ruleIdIn in number) is
select act.action_id
from ame_action_usages acu
,ame_actions act
where acu.rule_id = ruleIdIn
and act.action_id = acu.action_id
and sysdate between act.start_date and nvl(act.end_date - (1/86400),sysdate)
and (sysdate between acu.start_date and nvl(acu.end_date - (1/86400),sysdate)
or
acu.start_date > sysdate and (acu.end_date is null or acu.end_date > acu.start_date)
);
cursor ruleUsageCursor(applicationIdIn in number
,ruleIdIn in number) is
select rlu.start_date
,decode(rlu.end_date
,null
,rlu.start_date || ' Onward'
,rlu.start_date || ' to ' || rlu.end_date)
,nvl(to_char(rlu.priority),'disabled')
,decode(rlu.approver_category,'A','Action','F','FYI',rlu.approver_category)
from ame_rules rul
,ame_rule_usages rlu
where rul.rule_id = ruleIdIn
and rul.rule_id = rlu.rule_id
and rlu.item_id = applicationIdIn
and (sysdate between rul.start_date and nvl(rul.end_date - (1/86400),sysdate)
or
rul.start_date > sysdate and (rul.end_date is null or rul.end_date > rul.start_date)
)
and (sysdate between rlu.start_date and nvl(rlu.end_date - (1/86400),sysdate)
or
rlu.start_date > sysdate and (rlu.end_date is null or rlu.end_date > rlu.start_date)
)
order by rlu.start_date;
begin
ruleTypeDescList(1) := 'Combination';
ruleTypeDescList(2) := 'List Creation';
ruleTypeDescList(3) := 'List Creation Exception';
ruleTypeDescList(4) := 'List Modification';
ruleTypeDescList(5) := 'Substitution';
ruleTypeDescList(6) := 'Pre-list';
ruleTypeDescList(7) := 'Post-list';
ruleTypeDescList(8) := 'Production';
anchor('rules');
for i in 0 .. 7 loop
for j in 1 .. itemClassIdsIn.count loop
open activRuleCursor(applicationIdIn => applicationIdIn
,ruleTypeIn => i
,itemClassIdIn => itemClassIdsIn(j));
fetch activRuleCursor bulk collect
into
activeRuleIdList
,activeRuleDescList
,rule_keyList;
close activRuleCursor;
--
if activeRuleIdList.count > 0 then
currentDesc := initcap(itemClassNamesIn(j))
|| ' '
|| ruleTypeDescList(i+1)
|| ' Rules';
currentLink := to_char(i)||'_'||lower(substr(itemClassNamesIn(j),1,3))||'rules';
anchor(currentLink);
startTable;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => currentDesc
,colSpanIn => 6);
closeTableRow;
closeTable;
lineBreak;
for x in 1 .. activeRuleIdList.count loop
-- print rule id and desc
startTable;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'Rule ID : '|| activeRuleIdList(x)
,colspanIn => 1);
printTableCell(contentIn => 'Rule Key : '|| rule_keyList(x)
,colspanIn => 2);
printTableCell(contentIn => 'Description : '|| activeRuleDescList(x)
,colspanIn => 5);
closeTableRow;
open conditionUsageCursor(ruleIdIn => activeRuleIdList(x));
fetch conditionUsageCursor bulk collect
into
conditionIdList
,conditionTypeList;
close conditionUsageCursor;
-- print cond details
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'Conditions :'
,colspanIn => 7);
closeTableRow;
for k in 1 .. conditionIdList.count loop
startTableRow;
printTableCell(contentIn => conditionIdList(k)
,colSpanIn => 2);
printTableCell(contentIn => conditionTypeList(k)
||getConditionDescription(conditionIdList(k))
,colSpanIn => 5);
closeTableRow;
end loop;
if conditionIdList.count = 0 then
startTableRow;
printTableCell(contentIn => 'No Conditions'
,colSpanIn => 7);
closeTableRow;
end if;
open actionUsageCursor(ruleIdIn => activeRuleIdList(x));
fetch actionUsageCursor bulk collect
into
actionIdList;
close actionUsageCursor;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'Actions :'
,colspanIn => 7);
closeTableRow;
for k in 1 .. actionIdList.count loop
startTableRow;
printTableCell(contentIn => actionIdList(k)
,colSpanIn => 2);
printTableCell(contentIn => getActionDescription(actionIdList(k))
,colSpanIn => 5);
closeTableRow;
end loop;
open ruleUsageCursor(applicationIdIn => applicationIdIn
,ruleIdIn => activeRuleIdList(x));
fetch ruleUsageCursor bulk collect
into
startDateList
,endDateList
,priorityList
,categoryList;
close ruleUsageCursor;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'Usages'
,colSpanIn => 7);
closeTableRow;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'Lifespan'
,colSpanIn => 2);
printTableCell(contentIn => 'Priority'
,colSpanIn => 3);
printTableCell(contentIn => 'Category'
,colSpanIn => 2);
closeTableRow;
for k in 1 .. startDateList.count loop
startTableRow;
printTableCell(contentIn => endDateList(k)
,colSpanIn => 2);
printTableCell(contentIn => priorityList(k)
,colSpanIn => 3);
printTableCell(contentIn => categoryList(k)
,colSpanIn => 2);
closeTableRow;
end loop;
closeTable;
createLink(link1In => currentLink
,link1DescIn => currentDesc
,link2In => 'rules'
,link2DescIn => 'Rules');
conditionIdList.delete;
conditionTypeList.delete;
actionIdList.delete;
startDateList.delete;
endDateList.delete;
priorityList.delete;
categoryList.delete;
end loop; -- loop for each rule
end if; --
end loop; -- loop for each item class
end loop; -- rule type loop
end printRules;
--+
--+
--+
procedure printActionTypes(applicationIdIn in number) as
actionTypeIdList ame_util.idList;
actionTypeNameList ame_util.stringList;
actionTypeDDList ame_util.charList;
actIdList ame_util.charList;
actionIdList ame_util.idList;
actionDescList ame_util.stringList;
actionParam1List ame_util.longStringList;
actionParam2List ame_util.longStringList;
attributeNameList ame_util.stringList;
attributeTypeList ame_util.stringList;
orderNumberList ame_util.idList;
VRList ame_util.stringList;
COAOMList ame_util.stringList;
ruleTypeList ame_util.idList;
currentRuleType integer;
tempActionDescr varchar2(300);
cursor actionTypeCursor is
select aty.action_type_id
,aty.name
,aty.dynamic_description
from ame_action_types aty
where sysdate between aty.start_date and nvl(aty.end_date - (1/86400),sysdate)
and aty.action_type_id in
(select action_type_id
from ame_action_type_config
where application_id = applicationIdIn
and sysdate between start_date
and nvl(end_date-(1/86400),end_date));
--and rownum < 5;
cursor actionCursor(actionTypeIdIn in number) is
select act.action_id
,act.parameter
,act.parameter_two
,act.description
from ame_actions act
where act.action_type_id = actionTypeIdIn
and sysdate between act.start_date and nvl(act.end_date - (1/86400),sysdate)
and act.created_by not in (1,120);
cursor reqAttributeCursor(actionTypeIdIn in number) is
select atr.name
,atr.attribute_type
from ame_attributes atr
where sysdate between atr.start_date and nvl(atr.end_date - (1/86400),sysdate)
and atr.attribute_id in
(select attribute_id
from ame_mandatory_attributes man
where man.action_type_id = actionTypeIdIn
and sysdate between man.start_date and nvl(man.end_date - (1/86400),sysdate)
);
cursor substActionCursor(actionTypeIdIn in number) is
select act.action_id
,act.parameter
,act.parameter_two
,act.description
from ame_actions act
where act.action_type_id = actionTypeIdIn
and sysdate between act.start_date and nvl(act.end_date - (1/86400),sysdate)
and action_id in (
select acu.action_id
from ame_rule_usages aru
,ame_action_usages acu
where aru.item_id = applicationIdIn
and acu.rule_id = aru.rule_id
and sysdate between aru.start_date and
nvl(aru.end_date-(1/86400),sysdate)
and sysdate between acu.start_date and
nvl(aru.end_date-(1/86400),sysdate));
cursor actionTypeCfgCursor(applicationIdIn in number) is
select acf.order_number
,decode(acf.voting_regime
,'S'
,'Serial'
,'C'
,'Consensus'
,'F'
,'First Responder Wins'
,acf.voting_regime)
,decode(acf.chain_ordering_mode
,'S'
,'Serial'
,'P'
,'Parallel'
,acf.chain_ordering_mode)
,aty.action_type_id
,aty.name
,decode(atu.rule_type,2,1,atu.rule_type)
from ame_action_type_config acf
,ame_action_types aty
,ame_action_type_usages atu
where acf.application_id = applicationIdIn
and aty.action_type_id = acf.action_type_id
and aty.action_type_id = atu.action_type_id
and sysdate between aty.start_date and nvl(aty.end_date - (1/86400),sysdate)
and sysdate between atu.start_date and nvl(atu.end_date - (1/86400),sysdate)
and sysdate between acf.start_date and nvl(acf.end_date - (1/86400),sysdate)
order by atu.rule_type,acf.order_number;
begin
anchor('act');
startTable;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'Action Types');
closeTableRow;
closeTable;
lineBreak;
open actionTypeCfgCursor(applicationIdIn => applicationIdIn);
fetch actionTypeCfgCursor bulk collect
into
orderNumberList
,VRList
,COAOMList
,actionTypeIdList
,actionTypeNameList
,ruleTypeList;
close actionTypeCfgCursor;
--+
startTable2;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => ' Action Types being used in current Transaction Type'
,colspanIn => 5);
closeTableRow;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'ID');
printTableCell(contentIn => 'Name');
printTableCell(contentIn => 'Order Number');
printTableCell(contentIn => 'Voting Regime');
printTableCell(contentIn => 'COA Ordering Mode');
closeTableRow;
currentRuleType := ruleTypeList(1);
for i in 1 .. actionTypeIdList.count loop
/*if currentRuleType <> ruleTypeList(i) then
startTableRow;
printTableCell(contentIn => ' - '
,colSpanIn => 5);
closeTableRow;
end if;*/
startTableRow;
printTableCell(contentIn => actionTypeIdList(i));
printTableCell(contentIn => actionTypeNameList(i));
printTableCell(contentIn => orderNumberList(i));
printTableCell(contentIn => VRList(i));
printTableCell(contentIn => COAOMList(i));
closeTableRow;
currentRuleType := ruleTypeList(i);
end loop;
closeTable;
--+
actIdList.delete;
actionTypeNameList.delete;
actionTypeDDList.delete;
--+
open actionTypeCursor;
fetch actionTypeCursor bulk collect
into
actionTypeIdList
,actionTypeNameList
,actionTypeDDList;
close actionTypeCursor;
for i in 1 .. actionTypeIdList.count loop
startTable2;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'Action Type ID :'|| actionTypeIdList(i));
printTableCell(contentIn => 'Action Type Description :'|| actionTypeNameList(i)
,colspanIn => 2);
closeTableRow;
--+
open reqAttributeCursor(actionTypeIdIn => actionTypeIdList(i));
fetch reqAttributeCursor bulk collect
into
attributeNameList
,attributeTypeList;
close reqAttributeCursor;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'Required Attributes '
,colspanIn => 3);
closeTableRow;
--+
for j in 1 .. attributeTypeList.count loop
startTableRow;
printTableCell(contentIn => attributeNameList(j)
,colspanIn => 2);
printTableCell(contentIn => attributeTypeList(j));
end loop;
--+
open actionCursor(actionTypeIdIn => actionTypeIdList(i));
fetch actionCursor bulk collect
into
actionIdList
,actionParam1List
,actionParam2List
,actionDescList;
close actionCursor;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'Action ID');
printTableCell(contentIn => 'Action Description');
printTableCell(contentIn => 'Parameter');
closeTableRow;
for j in 1 .. actionIdList.count loop
if ame_action_pkg.getActionTypeName(actionTypeIdList(i)) =ame_util.substitutionTypeName then
tempActionDescr := getActionDescription(actionIdIn =>actionIdList(j));
end if;
startTableRow;
printTableCell(contentIn => actionIdList(j));
if tempActionDescr is not null then
printTableCell(contentIn => tempActionDescr);
else
printTableCell(contentIn => actionDescList(j));
end if;
tempActionDescr :=null;
--printTableCell(contentIn => actionDescList(j));
printTableCell(contentIn => actionParam1List(j));
closeTableRow;
end loop;
--+
if actionIdList.count = 0 then
startTableRow;
printTableCell(contentIn => 'No Actions'
,colspanIn => 2);
closeTableRow;
end if;
--+
actionIdList.delete;
actionDescList.delete;
actionParam1List.delete;
actionParam2List.delete;
attributeNameList.delete;
attributeTypeList.delete;
closeTable;
end loop;
--+
--+
createLinktoTop;
--+
actionTypeIdList.delete;
actionTypeNameList.delete;
orderNumberList.delete;
VRList.delete;
COAOMList.delete;
end printActionTypes;
--+
--+
--+
function getGroupName(approvalGroupIdIn in number) return varchar2 is
groupName ame_util.stringType;
begin
select agr.name
into groupName
from ame_approval_groups agr
where agr.approval_group_id = approvalGroupIdIn
and sysdate between agr.start_date and nvl(agr.end_date - (1/86400),sysdate);
return groupName;
exception
when no_data_found then
return approvalGroupIdIn;
end getGroupName;
--+
--+
--+
--+
--+
--------------------------------------------------------------------------------------------
procedure printConditionDetails(applicationIdIn in number) is
cursor getConditionid is
select aco.condition_id
,aco.condition_key
from ame_attribute_usages atu
,ame_conditions aco
where atu.application_id = applicationIdIn
and aco.attribute_id = atu.attribute_id
and sysdate between atu.start_date and
nvl(atu.end_date-(1/86400),sysdate)
and sysdate between aco.start_date and
nvl(aco.end_date-(1/86400),sysdate)
order by condition_id;
condList ame_util.idList;
valuList ame_util.longestStringList;
stringCon varchar2(30000);
conddition_key ame_util.stringList;
begin
open getConditionid;
fetch getConditionid bulk collect into condList,conddition_key;
close getConditionid;
anchor('othercond');
startTable;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'AME conditions'
,colSpanIn => 4);
closeTableRow;
closeTable;
lineBreak;
startTable;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'Regular Conditions'
,colSpanIn => 4);
closeTableRow;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'Condition Key');
printTableCell(contentIn => 'Condition Id');
printTableCell(contentIn => 'Attribute Name');
printTableCell(contentIn => 'Description');
closeTableRow;
for i in 1 .. condList.count loop
if ame_condition_pkg.isStringAttributeType(conditionIdIn =>condList(i)) then
ame_condition_pkg.getStringValueList(conditionIdIn => condList(i),
stringValueListOut => valuList);
for j in 1..valuList.count loop
if stringCon is not null then
stringCon := stringCon || ','||valuList(j);
else
stringCon := valuList(j);
end if;
end loop;
startTableRow;
printTableCell(contentIn => conddition_key(i));
printTableCell(contentIn => condList(i));
printTableCell(contentIn => ame_condition_pkg.getAttributeName(condList(i)));
printTableCell(contentIn => stringCon);
closeTableRow;
else
startTableRow;
printTableCell(contentIn => conddition_key(i));
printTableCell(contentIn => condList(i));
printTableCell(contentIn => ame_condition_pkg.getAttributeName(condList(i)));
printTableCell(contentIn => ame_condition_pkg.getDescription(condList(i)));
closeTableRow;
end if;
end loop;
closeTable;
createLinktoTop;
end printConditionDetails;
procedure printLMConditions(applicationIdIn in number) is
cursor getLMconditions is
select condition_id
,parameter_one
,parameter_two
,condition_key
from ame_conditions
where condition_type ='post'
and sysdate between start_date and nvl(end_date-(1/86400),sysdate);
cursor getruleLMConditions is
select condition_id
from ame_condition_usages
where rule_id in (select rule_id
from ame_rule_usages
where item_id =applicationIdIn
and sysdate between start_date
and nvl(end_date-(1/86400),sysdate));
conditionIdList ame_util.idList;
param_oneList ame_util.stringList;
param_twoList ame_util.stringList;
lmConitiondKey ame_util.stringList;
conUsageList ame_util.idList;
condUsed boolean :=false;
inval_index integer;
begin
open getLMconditions;
fetch getLMconditions bulk collect into conditionIdList,param_oneList,param_twoList,lmConitiondKey;
close getLMconditions;
open getruleLMConditions;
fetch getruleLMConditions bulk collect into conUsageList;
close getruleLMConditions;
anchor('lcm');
startTable;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'List Modification conditions ');
closeTableRow;
startTable;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'Condition Key');
printTableCell(contentIn => 'ConditionId');
printTableCell(contentIn => 'Type');
printTableCell(contentIn => 'Approver');
printTableCell(contentIn => 'Used in current Transaction Type');
closeTableRow;
for i in 1..conditionIdList.count loop
condUsed := false;
for j in 1..conUsageList.count loop
if conditionIdList(i)= conUsageList(j) then
condUsed := true;
end if;
exit when condUsed = true;
end loop;
startTableRow;
printTableCell(contentIn => lmConitiondKey(i));
printTableCell(contentIn => conditionIdList(i));
printTableCell(contentIn => param_oneList(i));
if ame_approver_type_pkg.validateApprover(param_twoList(i)) then
printTableCell(contentIn => param_twoList(i));
elsif(condUsed) then
printTableCell(contentIn => 'Invalid:'||param_twoList(i));
inval_index := ame_invalappr.count;
ame_invalappr(inval_index+1).roleName :=param_twoList(i);
ame_invalappr(inval_index+1).source :='COND:'||conditionIdList(i);
else
printTableCell(contentIn => 'Invalid:'||param_twoList(i));
end if;
if condUsed then
printTableCell(contentIn => 'Y');
else
printTableCell(contentIn => 'N');
end if;
closeTableRow;
end loop;
closeTable;
end printLMConditions;
procedure printApprDetails is
cursor getApprDetails is
select apt.approver_type_id
,apt.orig_system
,fnd_lookups.description
from ame_approver_types apt
,fnd_lookups
where lookup_code = apt.orig_system
and lookup_type = 'FND_WF_ORIG_SYSTEMS'
and sysdate between apt.start_date
and nvl(apt.end_date-(1/86400),sysdate)
order by approver_type_id;
apprTypeId ame_util.idList;
origsystem ame_util.stringList;
apprDescr ame_util.stringList;
begin
open getApprDetails;
fetch getApprDetails bulk collect
into
apprTypeId
,origsystem
,apprDescr;
close getApprDetails;
--+
anchor('appr');
startTable;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'Approver Type Details'
,colSpanIn => 4);
closeTableRow;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'Approver Type Id');
printTableCell(contentIn => 'Orig System');
printTableCell(contentIn => 'Description');
closeTableRow;
for i in 1 .. apprTypeId.count loop
startTableRow;
printTableCell(contentIn => apprTypeId(i));
printTableCell(contentIn => origsystem(i));
printTableCell(contentIn => apprDescr(i));
closeTableRow;
end loop;
closeTable;
createLinktoTop;
end printApprDetails;
procedure printAMEInvalids is
cursor getInvalids is
select object_name
,object_type
from all_objects
where object_name like 'AME%'
and status = 'INVALID';
objectName ame_util.stringList;
objectType ame_util.stringList;
begin
open getInvalids;
fetch getInvalids bulk collect
into
objectName
,objectType;
close getInvalids;
anchor('inval');
startTable;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'AME Invalid object'
,colSpanIn => 6);
closeTableRow;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'Object Name');
printTableCell(contentIn => 'Object Type');
closeTableRow;
for i in 1 .. objectName.count loop
startTableRow;
printTableCell(contentIn => objectName(i));
printTableCell(contentIn => objectType(i));
closeTableRow;
end loop;
closeTable;
createLinktoTop;
end printAMEInvalids;
procedure printReleaseVersions is
cursor getAMEVersions is
select decode(bug_number
,'2614213','2614213'
,'2863619','2863619'
,'3858763','AME 11.5.10'
,'3962268','AME.A'
,'4433707','AME.A RUP'
,'4428060','AME.B'
,'4873179','AME.B RUP1'
,'5708576','AME.B.RUP2'
,'NONE') AME_LEVEL
,bug_number
,creation_date
from ad_bugs
where bug_number in ('2614213'
,'2863619'
,'3858763'
,'3962268'
,'4433707'
,'4428060'
,'4873179'
,'5708576')
order by bug_number;
cursor getFamilyPack is
select decode (bug_number
,'3127777','FP.I'
,'3333633','FP.J'
,'3500000','FP.K'
,'5055050','FP.K.RUP'
,'NONE') AME_LEVEL
,bug_number
,creation_date
from ad_bugs
where bug_number in (
'3127777'
,'3333633'
,'3500000'
,'5055050'
)
order by bug_number;
cursor getAppsRelease is
select decode (bug_number
,'2669606','Oracle Applications Release 11.5.9'
,'3140000','Oracle Applications Release 11.5.10'
,'3640000','11.5.10 CU1'
,'3480000','11.5.10 CU2'
,'NONE') AME_LEVEL
,bug_number
,creation_date
from ad_bugs
where bug_number in (
'2669606'
,'3140000'
,'3640000'
,'3480000'
)
order by bug_number;
PatchList ame_util.StringList;
ReleaseName ame_util.stringList;
dateList ame_util.dateList;
begin
open getAMEVersions;
fetch getAMEVersions bulk collect
into
ReleaseName
,PatchList
,dateList;
close getAMEVersions;
anchor('AMEver');
startTable;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'AME minipack Level'
,colSpanIn => 6);
closeTableRow;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'AME minipack');
printTableCell(contentIn => 'Patch Number');
printTableCell(contentIn => 'Patch Date');
closeTableRow;
for i in 1 .. PatchList.count loop
startTableRow;
printTableCell(contentIn => ReleaseName(i));
printTableCell(contentIn => PatchList(i));
printTableCell(contentIn => to_char(dateList(i),'dd/mm/yyyy:hh:mm:ss'));
closeTableRow;
end loop;
closeTable;
createLinktoTop;
ReleaseName.delete;
PatchList.delete;
dateList.delete;
open getFamilyPack;
fetch getFamilyPack bulk collect
into
ReleaseName
,PatchList
,dateList;
close getFamilyPack;
anchor('FPver');
startTable;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'HRMS Family Pack Level'
,colSpanIn => 6);
closeTableRow;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'Family Pack');
printTableCell(contentIn => 'Patch Number');
printTableCell(contentIn => 'Patch Date');
closeTableRow;
for i in 1 .. PatchList.count loop
startTableRow;
printTableCell(contentIn => ReleaseName(i));
printTableCell(contentIn => PatchList(i));
printTableCell(contentIn => to_char(dateList(i),'dd/mm/yyyy:hh:mm:ss'));
closeTableRow;
end loop;
closeTable;
createLinktoTop;
ReleaseName.delete;
PatchList.delete;
dateList.delete;
open getAppsRelease;
fetch getAppsRelease bulk collect
into
ReleaseName
,PatchList
,dateList;
close getAppsRelease;
anchor('APPVer');
startTable;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'Application release Level'
,colSpanIn => 6);
closeTableRow;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'Apps release');
printTableCell(contentIn => 'Patch Number');
printTableCell(contentIn => 'Patch Date');
closeTableRow;
for i in 1 .. PatchList.count loop
startTableRow;
printTableCell(contentIn => ReleaseName(i));
printTableCell(contentIn => PatchList(i));
printTableCell(contentIn => to_char(dateList(i),'dd/mm/yyyy:hh:mm:ss'));
closeTableRow;
end loop;
closeTable;
createLinktoTop;
end printReleaseVersions;
procedure splithdrLine(hdrLineIn in varchar2
,fileNameOut out varchar
,varsionOut out varchar2) is
pos1 integer;
pos2 integer;
pos3 integer;
result varchar2(50);
begin
pos2:= instrb(hdrLineIn,'pkb');
if pos2 = 0 then
pos2:= instrb(hdrLineIn,'pkh');
end if;
pos1 := instrb(hdrLineIn,':', 1);
result := trim(substr(hdrLineIn,pos1+2,pos2-1));
pos3 := instrb(result,' ');
fileNameOut := substr(result,1,pos3);
result := trim(substr(result,pos3+1,length(result)));
pos2 := instrb(result,' ');
if pos2 <> 0 then
varsionOut := substr(result,1,pos2);
else
varsionOut := result;
end if;
end splithdrLine;
procedure printKeyAMEFileVersion is
cursor getKeyFileVersion is
select name
,text
,type
from dba_source
where name in ('AME_ENGINE'
,'AME_UTIL'
,'AME_API'
,'AME_API2'
,'AME_TEST_UI'
,'AME_TEST_PKG'
,'AME_APPROVER_TYPE_PKG')
and line = 2
order by name;
pkgName ame_util.stringList;
obj_typeList ame_util.stringList;
VersionTxt ame_util.stringList;
file_name varchar2(50);
version varchar2(50);
begin
open getKeyFileVersion;
fetch getKeyFileVersion bulk collect
into
pkgName
,VersionTxt
,obj_typeList;
close getKeyFileVersion;
anchor('file');
startTable;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'Key AME File Version'
,colSpanIn => 6);
closeTableRow;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'Package Name');
printTableCell(contentIn => 'Package Type');
printTableCell(contentIn => 'File Name');
printTableCell(contentIn => 'Version');
closeTableRow;
for i in 1 .. pkgName.count loop
startTableRow;
splithdrLine(hdrLineIn => trim(VersionTxt(i))
,fileNameOut => file_name
,varsionOut => version);
printTableCell(contentIn => pkgName(i));
printTableCell(contentIn => obj_typeList(i));
printTableCell(contentIn =>trim(file_name) );
printTableCell(contentIn =>trim(version) );
closeTableRow;
end loop;
closeTable;
createLinktoTop;
end printKeyAMEFileVersion;
procedure printTransApprovalGroups(applicationIdIn in number) as
groupIdList ame_util.idList;
groupNameList ame_util.stringList;
groupIsStaticList ame_util.charList;
groupDescList ame_util.stringList;
groupQueryList ame_util.longestStringList;
groupItemIdList ame_util.idList;
groupParamTypeList ame_util.stringList;
groupParamsList ame_util.longStringList;
orderNumberList ame_util.idList;
ONList ame_util.idList;
VRList ame_util.stringList;
inval_index integer;
cursor approvalGroupCursor is
select agr.approval_group_id
,agr.name
,agr.query_string
,agr.description
,agr.is_static
from ame_approval_groups agr
where sysdate between agr.start_date and nvl(agr.end_date - (1/86400),sysdate)
and agr.approval_group_id in (select approval_group_id
from ame_approval_group_config
where sysdate between start_date and nvl(end_date-(1/86400),sysdate)
and application_id = applicationIdIn
);
cursor approvalGroupItemCursor(approvalGroupIdIn in number) is
select agi.approval_group_item_id
,decode(agi.parameter_name
,'OAM_group_id'
,'AME Group'
,'wf_roles_name'
,'WF Role'
,agi.parameter_name)
,agi.parameter
,agi.order_number
from ame_approval_group_items agi
where agi.approval_group_id = approvalGroupIdIn
and sysdate between agi.start_date and nvl(agi.end_date - (1/86400),sysdate);
cursor approvalGroupCfgCursor(applicationIdIn in number ) is
select agf.order_number
,decode(agf.voting_regime
,'S'
,'Serial'
,'C'
,'Consensus'
,'F'
,'First Responder Wins'
,'O'
,'Order Number'
,agf.voting_regime)
,agr.approval_group_id
,agr.name
from ame_approval_group_config agf
,ame_approval_groups agr
where agf.application_id = applicationIdIn
and agr.approval_group_id = agf.approval_group_id
and sysdate between agr.start_date and nvl(agr.end_date - (1/86400),sysdate)
and sysdate between agf.start_date and nvl(agf.end_date - (1/86400),sysdate)
order by agf.order_number;
begin
anchor('TransAgr');
startTable;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'Approval Groups');
closeTableRow;
closeTable;
open approvalGroupCfgCursor(applicationIdIn => applicationIdIn);
fetch approvalGroupCfgCursor bulk collect
into
ONList
,VRList
,groupIdList
,groupNameList;
close approvalGroupCfgCursor;
--+
startTable2;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => ' Approval Groups - Parallelization Details'
,colspanIn => 4);
closeTableRow;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'ID');
printTableCell(contentIn => 'Name');
printTableCell(contentIn => 'Order Number');
printTableCell(contentIn => 'Voting Regime');
closeTableRow;
for i in 1 .. groupIdList.count loop
startTableRow;
printTableCell(contentIn => groupIdList(i));
printTableCell(contentIn => groupNameList(i));
printTableCell(contentIn => ONList(i));
printTableCell(contentIn => VRList(i));
closeTableRow;
end loop;
closeTable;
groupIdList.delete;
groupNameList.delete;
groupQueryList.delete;
groupDescList.delete;
open approvalGroupCursor;
fetch approvalGroupCursor bulk collect
into
groupIdList
,groupNameList
,groupQueryList
,groupDescList
,groupIsStaticList;
close approvalGroupCursor;
for i in 1 .. groupIdList.count loop
--+
startTable2;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'Approval Group ID');
printTableCell(contentIn => groupIdList(i));
printTableCell(contentIn => 'Name');
printTableCell(contentIn => groupNameList(i));
closeTableRow;
--+
startTableRow;
printTableCell(contentIn => 'Description'
,colspanIn => 1);
printTableCell(contentIn => groupDescList(i)
,colspanIn => 3);
closeTableRow;
--+
startTableRow;
printTableCell(contentIn => 'Query String'
,colspanIn => 1);
printTableCell(contentIn => groupQueryList(i)
,colspanIn => 3);
closeTableRow;
--+
startTableRow;
printTableCell(contentIn => 'Static'
,colspanIn => 1);
printTableCell(contentIn => groupIsStaticList(i)
,colspanIn => 3);
closeTableRow;
--+
if groupIsStaticList(i) = 'Y' then
open approvalGroupItemCursor(approvalGroupIdIn => groupIdList(i));
fetch approvalGroupItemCursor bulk collect
into
groupItemIdList
,groupParamTypeList
,groupParamsList
,orderNumberList;
close approvalGroupItemCursor;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'Item ID');
printTableCell(contentIn => 'Order Number');
printTableCell(contentIn => 'Parameter Type');
printTableCell(contentIn => 'Parameter');
closeTableRow;
for i in 1 .. groupItemIdList.count loop
startTableRow;
printTableCell(contentIn => groupItemIdList(i));
printTableCell(contentIn => orderNumberList(i));
printTableCell(contentIn => groupParamTypeList(i));
if groupParamTypeList(i) = 'AME Group' then
printTableCell(contentIn => getGroupName(groupParamsList(i)));
else
begin
if ame_approver_type_pkg.validateApprover(groupParamsList(i)) then
printTableCell(contentIn => groupParamsList(i));
else
inval_index := ame_invalappr.count;
ame_invalappr(inval_index+1).roleName := groupParamsList(i);
ame_invalappr(inval_index+1).source := 'APG:'||groupIdList(i);
printTableCell(contentIn =>'Invalid:'||groupParamsList(i));
end if;
exception
when others then
null;
end;
end if;
closeTableRow;
end loop;
if groupItemIdList.count = 0 then
startTableRow;
printTableCell(contentIn => 'No items.'
,colSpanIn => 4);
closeTableRow;
end if;
--+
groupItemIdList.delete;
groupParamTypeList.delete;
orderNumberList.delete;
groupParamsList.delete;
orderNumberList.delete;
closeTable;
--+
else
closeTable;
end if;
end loop;
closeTable;
createLinktoTop;
--+
ONList.delete;
VRList.delete;
end printTransApprovalGroups;
--+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
function isexist(approverList in ame_util.stringList
,nameIn in varchar2) return boolean as
begin
for i in 1..approverList.count loop
if approverList(i)= nameIn then
return true;
end if;
end loop;
return false;
end isexist;
procedure approverSource(approverNameIn in varchar2
,objectOut out ame_util.stringList
,objectIdType out ame_util.stringList
,objectIdOut out ame_util.stringList
,descrOut out ame_util.stringList) is
appr_source varchar2(300);
before_colon varchar2(50);
after_colon varchar2(50);
total_count integer;
loc_count integer;
tempDesc varchar2(30);
descr varchar2(100);
begin
total_count := ame_invalappr.count;
for i in 1..total_count loop
if approverNameIn = ame_invalappr(i).roleName then
appr_source := ame_invalappr(i).source;
before_colon := substr(appr_source,1,instrb(appr_source,':')-1);
after_colon := substr(appr_source,instrb(appr_source,':')+1,length(appr_source));
loc_count := objectIdOut.count;
loc_count := loc_count+1;
if before_colon ='actionId' then
objectOut(loc_count) := ame_action_pkg.getActionTypeNameByActionId(to_number(after_colon))||' Action';
objectIdType(loc_count) := 'Action Id';
objectIdOut(loc_count) := after_colon;
descrOut(loc_count) := ame_action_pkg.getDescription(actionIdIn =>to_number(after_colon));
elsif before_colon ='APG' then
objectOut(loc_count) := 'Approval group';
objectIdType(loc_count) := 'Approval Group Id';
objectIdOut(loc_count) := after_colon;
descrOut(loc_count) := ame_approval_group_pkg.getName(approvalGroupIdIn => to_number(after_colon));
elsif before_colon ='ADM' then
objectOut(loc_count) := 'Configuration variable';
objectIdType(loc_count) := 'Configuration variable Name';
objectIdOut(loc_count) := 'Admin Approver';
descrOut(loc_count) := 'invalid : '||approverNameIn;
elsif before_colon ='COND' then
descr := trim(ame_condition_pkg.getConditionType(to_number(after_colon)));
if trim(descr) ='post' then
tempDesc := 'List Modification';
end if;
objectOut(loc_count) := 'Conditions:'||tempDesc;
objectIdType(loc_count) := 'Condition Id';
objectIdOut(loc_count) := after_colon;
descrOut(loc_count) := ame_condition_pkg.getDescription(conditionIdIn =>to_number(after_colon));
end if;
end if;
loc_count:=0;
end loop;
end approverSource;
procedure processInvalAppr is
wNameList ame_util.stringList;
objectList ame_util.stringList;
objectIdTypeList ame_util.stringList;
objectIdList ame_util.stringList;
descrList ame_util.stringList;
wNameListIndex number;
begin
for i in 1..ame_invalappr.count loop
if isexist(approverList => wNameList
,nameIn => ame_invalappr(i).roleName) <> true then
wNameListIndex := wNameList.count;
wNameList(wNameListIndex+1) := ame_invalappr(i).roleName;
end if;
end loop;
anchor('invAppr');
startTable;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'List of Invalid Approvers existing in current AME Setup');
closeTable;
if wNameList.count = 0 then
return;
end if;
for i in 1..wNameList.count loop
lineBreak;
startTable;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'WF_ROLES.NAME :'||wNameList(i));
closeTableRow;
closeTable;
objectList.delete;
objectIdTypeList.delete;
objectIdList.delete;
descrList.delete;
approverSource(approverNameIn => wNameList(i)
,objectOut => objectList
,objectIdType => objectIdTypeList
,objectIdOut => objectIdList
,descrOut => descrList);
startTable;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'Object');
printTableCell(contentIn => 'Object Key');
printTableCell(contentIn => 'Object Id');
printTableCell(contentIn => 'Object Description');
closeTableRow;
for j in 1..objectIdList.count loop
startTableRow;
printTableCell(contentIn => objectList(j));
printTableCell(contentIn => objectIdTypeList(j));
printTableCell(contentIn => objectIdList(j));
printTableCell(contentIn => descrList(j));
closeTableRow;
end loop;
closeTable;
end loop;
end processInvalAppr;
--+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--------------------------------------------------------------------------------------------
--+
procedure printConfigVariables(applicationIdIn in number) as
variableNameList ame_util.stringList;
variableValueList ame_util.longStringList;
inval_index integer;
cursor configVarCursor is
select cfg.variable_name
,cfg.variable_value
from ame_config_vars cfg
where cfg.application_id = applicationIdIn
and sysdate between cfg.start_date and nvl(cfg.end_date - (1/86400),sysdate);
cursor configVarCursor2 is
select cfg.variable_name
,cfg.variable_value
from ame_config_vars cfg
where cfg.application_id = 0
and sysdate between cfg.start_date and nvl(cfg.end_date - (1/86400),sysdate);
begin
open configVarCursor;
fetch configVarCursor bulk collect
into
variableNameList
,variableValueList;
close configVarCursor;
anchor('cfg');
startTable;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'Configuration Variables ');
closeTableRow;
closeTable;
--+
startTable2;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'Transaction Type Level'
,colspanIn => 3);
closeTableRow;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'Variable Name');
printTableCell(contentIn => 'Value'
,colspanIn => 2);
closeTableRow;
for i in 1 .. variableNameList.count loop
startTableRow;
if variableNameList(i) = 'adminApprover' then
if ame_approver_type_pkg.validateApprover(variableValueList(i)) then
printTableCell(contentIn => variableNameList(i));
printTableCell(contentIn => variableValueList(i)
,colspanIn => 2);
else
inval_index := ame_invalappr.count;
ame_invalappr(inval_index+1).roleName :=variableValueList(i);
ame_invalappr(inval_index+1).source :='ADM:CONFIG';
printTableCell(contentIn => variableNameList(i));
printTableCell(contentIn => 'Invalid:'||variableValueList(i)
,colspanIn => 2);
end if;
else
printTableCell(contentIn => variableNameList(i));
printTableCell(contentIn => variableValueList(i)
,colspanIn => 2);
end if;
closeTableRow;
end loop;
closeTable;
variableNameList.delete;
variableValueList.delete;
--+
--+
open configVarCursor2;
fetch configVarCursor2 bulk collect
into
variableNameList
,variableValueList;
close configVarCursor2;
--+
startTable2;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'Default'
,colspanIn => 3);
closeTableRow;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'Variable Name');
printTableCell(contentIn => 'Value'
,colspanIn => 2);
closeTableRow;
for i in 1 .. variableNameList.count loop
startTableRow;
if variableNameList(i) = 'adminApprover' then
if ame_approver_type_pkg.validateApprover(variableValueList(i)) then
printTableCell(contentIn => variableNameList(i));
printTableCell(contentIn => variableValueList(i)
,colspanIn => 2);
else
inval_index := ame_invalappr.count;
ame_invalappr(inval_index+1).roleName :=variableValueList(i);
ame_invalappr(inval_index+1).source :='ADM:CONFIG';
printTableCell(contentIn => variableNameList(i));
printTableCell(contentIn => 'Invalid:'||variableValueList(i)
,colspanIn => 2);
end if;
else
printTableCell(contentIn => variableNameList(i));
printTableCell(contentIn => variableValueList(i)
,colspanIn => 2);
end if;
closeTableRow;
end loop;
closeTable;
createLinktoTop;
end printConfigVariables;
--+
--+ main part of code
--+
BEGIN
printline ('<!--');
applicationId := to_number(&1);
printline ('-->');
startHtml;
anchor('top');
printTrasactionTypeInfo(applicationIdIn => applicationId
,transactionTypeFound => applicationFound);
if applicationFound then
createHtmlLinks;
printItemClassUsages(applicationIdIn => applicationId
,itemClassIdsOut => itemClassIdList
,itemClassNamesOut => itemClassNamesList);
printMandatoryAttributes(applicationIdIn => applicationId);
for i in 1 .. itemClassIdList.count loop
printAttributesByIC(applicationIdIn => applicationId
,itemClassIdIn => itemClassIdList(i)
,itemClassNameIn => itemClassNamesList(i));
end loop;
printConditionDetails(applicationIdIn => applicationId);
printLMConditions(applicationIdIn =>applicationId);
printRules(applicationIdIn => applicationId
,itemClassIdsIn => itemClassIdList
,itemClassNamesIn => itemClassNamesList);
printActionTypes (applicationIdIn => applicationId);
printTransApprovalGroups(applicationIdIn => applicationId);
printConfigVariables(applicationIdIn => applicationId);
printApprDetails;
processInvalAppr;
printAMEInvalids;
printReleaseVersions;
printKeyAMEFileVersion;
end if;
closeHtml;
exception
when no_data_found then
printLine ('no data found for the input.');
end;
/
spool off;
exit;
Purpose:
This AME setup output allows support and development to analyze your approval setup.
Requirement:
Internal ID (application_id)
Purpose of Utility Script
This reporting utility script will allow a snapshot to be taken of the AME set-up for a specified transaction type (ie Setup Report for the transaction Type).
It will report on the following
- AME Setup Data for a Transaction Type
- Invalid Approvers in the setup
- Invalid AME Objects
- AME Patch Level
- AME Key Files Versions
The output of this utility script is an HTML document.
Steps to generate the Setup Report
There are 2 steps involved in generating the setup report. The first step we need to identify the AME’s internal application id for the transaction type in question. The second step is the actually running the script which generates the setup report.
Step 1 : Identify the AME Internal ID for the Transaction Type.AME assigns a internal ID (also known as AME application ID) for each transaction type. The utility script generates the setup report based on this ID. The following details on how to identify the correct application ID for your Transaction Type.
Run the following SQL from SQLPLUS under ‘apps’ account.
Select decode(
to_char(nvl(fnd_profile.value('AME_INSTALLATION_LEVEL')
,'Pre-AME.B'))
,'Pre-AME.B','Pre-AME.B'
,'1','Pre-AME.B'
,'1.1','Pre-AME.B'
,'2','Post-AME.B'
,'Post-AME.B')
From Dual;
If the value returned 'Pre-AME.B' then do the following
1. Login to AME Application using ‘AME Application Administrator’ responsibility.
2. Select ‘Admin’ tab
3. Choose "Application Administration" and continue.
4. Choose "Maintain Transaction Types" and continue.
5. Click on your transaction type.
6. AME-Internal ID of the chosen transaction type will be displayed on "Edit Transaction Type" page.
If the value returned by step is 'Post-AME.B' then do the following
1. Login to AME Application using "Approvals Management Administrator" responsibility.
2. Search for your transaction type
3. Click on the transaction type
4. AME-Internal ID of the chosen transaction type will be displayed.
Or you may use the script below to obtain the ID
select fnd.application_name
,aca.application_name
,aca.transaction_type_id
from fnd_application_vl fnd
,ame_calling_apps aca
where aca.fnd_application_id = fnd.application_id
and aca.application_id = to_number(:p_application_id)
and sysdate between aca.start_date and nvl(aca.end_date - (1/86400),sysdate);
Note, there are over 200 possible application names, of which iRecruitment has over 4 different
choices. The query option should only be used if you know the application_name.
Note: Please note that the AME Internal ID (Application ID) is usually a negative integer.
Make a note of the AME Internal ID
Step 2 : Running the script
Run the following SQL from SQLPLUS under ‘apps’ account.Select decode(to_char(
nvl(fnd_profile.value('AME_INSTALLATION_LEVEL')
,'Pre-AME.B'))
,'Pre-11.5.10','Pre-11.5.10'
,'Post-11.5.10')
From Dual;
If the value returned by step is ‘Pre-11.5.10', then run the script 1
The utility script should be run in an SQLPLUS session using ‘apps’ account. The AME-internalID identified in Step 1 should be applied as a parameter to the script as specified below.
Usage : sqlplus apps/<pwd>@dbname @file_name AME-InternalID
Example : sqlplus apps/apps@hrprodenv @get_ame_setup -200
Note: The output file should write the directory in which the script was executed from.
Script1:
--usage example: sqlplus @apps/apps/juldbgqa @file_name application_id
SET serveroutput on size 1000000
SET feedback off
spool Report_AppId&1..htm
DECLARE
TYPE rule_id_table IS TABLE OF ame_rules.rule_id%TYPE
INDEX BY BINARY_INTEGER;
TYPE rule_desc_table IS TABLE OF ame_rules.description%TYPE
INDEX BY BINARY_INTEGER;
TYPE action_id_table IS TABLE OF ame_actions.action_id%TYPE
INDEX BY BINARY_INTEGER;
TYPE action_desc_table IS TABLE OF ame_actions.description%TYPE
INDEX BY BINARY_INTEGER;
TYPE action_type_name_table IS TABLE OF ame_action_types.NAME%TYPE
INDEX BY BINARY_INTEGER;
TYPE action_parameter_table IS TABLE OF ame_actions.parameter%TYPE
INDEX BY BINARY_INTEGER;
TYPE action_type_id_table IS TABLE OF ame_action_types.action_type_id%TYPE
INDEX BY BINARY_INTEGER;
TYPE condn_id_table IS TABLE OF ame_conditions.condition_id%TYPE
INDEX BY BINARY_INTEGER;
TYPE condn_type_table IS TABLE OF VARCHAR2 (25)
INDEX BY BINARY_INTEGER;
TYPE attr_name_table IS TABLE OF ame_attributes.NAME%TYPE
INDEX BY BINARY_INTEGER;
TYPE attr_type_table IS TABLE OF ame_attributes.attribute_type%TYPE
INDEX BY BINARY_INTEGER;
TYPE attr_value_table IS TABLE OF ame_attribute_usages.query_string%TYPE
INDEX BY BINARY_INTEGER;
TYPE attr_is_line_item_table IS TABLE OF ame_attributes.line_item%TYPE
INDEX BY BINARY_INTEGER;
TYPE rule_type_table IS TABLE OF varchar2(30)
INDEX BY BINARY_INTEGER;
TYPE rule_type_num_table is table of number
index by binary_integer;
TYPE is_static_table IS TABLE OF ame_attribute_usages.is_static%TYPE
INDEX BY BINARY_INTEGER;
TYPE approval_group_id_table IS TABLE OF ame_approval_groups.approval_group_id%TYPE
INDEX BY BINARY_INTEGER;
TYPE approval_group_name_table IS TABLE OF ame_approval_groups.name%TYPE
INDEX BY BINARY_INTEGER;
TYPE approval_group_par_name_table IS TABLE OF ame_approval_group_items.parameter_name%TYPE
INDEX BY BINARY_INTEGER;
TYPE approval_group_par_table IS TABLE OF ame_approval_group_items.parameter%TYPE
INDEX BY BINARY_INTEGER;
TYPE config_vars_name_table IS TABLE OF ame_config_vars.variable_name%TYPE
INDEX BY BINARY_INTEGER;
TYPE config_vars_value_table IS TABLE OF ame_config_vars.variable_value%TYPE
INDEX BY BINARY_INTEGER;
TYPE rule_usages_start_date IS TABLE OF ame_rule_usages.start_date%TYPE
INDEX BY BINARY_INTEGER;
TYPE rule_usages_lifespan IS TABLE OF varchar2(100)
INDEX BY BINARY_INTEGER;
TYPE rule_usages_priority IS TABLE OF ame_rule_usages.priority%TYPE
INDEX BY BINARY_INTEGER;
CURSOR c_rule (app_id NUMBER) IS
SELECT amerl.rule_id, amerl.description,
DECODE(
amerl.rule_type,
1,'List Creation Rules',
2,'List Creation Exception Rules',
3,'List Modification Rules',
4,'Substitution Rules',
5,'Pre List Rules',
6,'Post List Rules',
'Undefined Rules'
),amerl.rule_type
FROM ame_rules amerl
WHERE amerl.rule_id IN (SELECT rule_id
FROM ame_rule_usages
WHERE item_id = app_id
AND
SYSDATE <= NVL((end_date-(1/86400)),SYSDATE)
)
and sysdate <= nvl((amerl.end_date-(1/86400)),sysdate)
order by amerl.rule_type;
CURSOR c_rule_usages (appid NUMBER,ruleid NUMBER) IS
SELECT ameru.start_date,
DECODE(ameru.end_date,null,start_date || ' Onward',start_date||' to '|| (ameru.end_date -(1/86400))),
ameru.priority
FROM ame_rule_usages ameru
WHERE ameru.item_id = appid
AND ameru.rule_id = ruleid
AND sysdate <= NVL(ameru.end_date-(1/86400),SYSDATE);
CURSOR c_action (ruleid number) is
select ameact.action_id, act.name, ameact.description
from ame_actions ameact, ame_action_types act
where act.action_type_id = ameact.action_type_id
and ameact.action_id in (select action_id
from ame_rules
where rule_id = ruleid
and sysdate <=nvl (end_date-(1/86400),sysdate)
)
and sysdate <= nvl (act.end_date-(1/86400),sysdate)
and sysdate <= nvl (ameact.end_date-(1/86400),sysdate);
CURSOR c_action_types is
select act.action_type_id, act.name
from ame_action_types act
where act.action_type_id <> -1
and sysdate between act.start_date and nvl(act.end_date-(1/86400),sysdate);
CURSOR c_action_type_attr_details (act_typ_id number) is
select ameattr.name, ameattr.attribute_type
from ame_attributes ameattr
where ameattr.attribute_id in (select attribute_id
from ame_mandatory_attributes
where action_type_id = act_typ_id
and sysdate between start_date and
nvl(end_date-(1/86400),sysdate)
)
and sysdate between ameattr.start_date AND NVL(ameattr.end_date-(1/86400),SYSDATE)
ORDER BY ameattr.NAME;
CURSOR c_action_type_action_details (act_typ_id NUMBER) IS
SELECT ameact.action_id, ameact.description,
ameact.parameter
FROM ame_actions ameact
WHERE ameact.action_type_id = act_typ_id
AND SYSDATE BETWEEN ameact.start_date AND NVL(ameact.end_date-(1/86400),SYSDATE)
ORDER BY ameact.action_id;
CURSOR c_condition (ruleid NUMBER) IS
SELECT amecon.condition_id,
DECODE(amecon.condition_type,'auth',' ','pre','Exception : ','post', 'List Modification : ')
FROM ame_conditions amecon
WHERE amecon.condition_id IN (SELECT condition_id
FROM ame_condition_usages
WHERE rule_id = ruleid
AND SYSDATE <=NVL (end_date-(1/86400),SYSDATE)
)
AND SYSDATE <=NVL (amecon.end_date-(1/86400),SYSDATE);
CURSOR c_attr_details_mandatory_item (app_id NUMBER) IS
SELECT ameattr.NAME attribute_name, ameattr.attribute_type,ameatusg.query_string,
ameatusg.is_static
FROM ame_attributes ameattr, ame_attribute_usages ameatusg
WHERE ameattr.attribute_id IN (SELECT attribute_id
FROM ame_mandatory_attributes amemattr
WHERE action_type_id = -1
AND SYSDATE BETWEEN amemattr.start_date AND
NVL (amemattr.end_date-(1/86400),SYSDATE)
)
AND ameattr.attribute_id = ameatusg.attribute_id
AND ameatusg.application_id = app_id
AND SYSDATE BETWEEN ameattr.start_date AND NVL(ameattr.end_date-(1/86400),SYSDATE)
AND SYSDATE BETWEEN ameatusg.start_date AND NVL(ameatusg.end_date-(1/86400),SYSDATE)
ORDER BY ameattr.line_item, ameattr.NAME;
CURSOR c_attr_details_header_item (app_id NUMBER) IS
SELECT ameattr.NAME attribute_name, ameattr.attribute_type, ameatusg.query_string,
ameatusg.is_static
FROM ame_attributes ameattr, ame_attribute_usages ameatusg
WHERE ameattr.attribute_id NOT IN (SELECT attribute_id
FROM ame_mandatory_attributes amemattr
WHERE action_type_id = -1
AND SYSDATE BETWEEN amemattr.start_date AND
NVL(amemattr.end_date-(1/86400),SYSDATE)
)
AND ameattr.attribute_id = ameatusg.attribute_id
AND ameatusg.application_id = app_id
AND (ameattr.line_item IS NULL OR ameattr.line_item = 'N')
AND SYSDATE BETWEEN ameattr.start_date AND NVL(ameattr.end_date-(1/86400),SYSDATE)
AND SYSDATE BETWEEN ameatusg.start_date AND NVL((ameatusg.end_date-(1/86400)),SYSDATE)
ORDER BY ameattr.line_item, ameattr.NAME;
CURSOR c_attr_details_line_item (app_id NUMBER) IS
SELECT ameattr.NAME attribute_name, ameattr.attribute_type,ameatusg.query_string,
ameatusg.is_static
FROM ame_attributes ameattr, ame_attribute_usages ameatusg
WHERE ameattr.attribute_id NOT IN (SELECT attribute_id
FROM ame_mandatory_attributes amemattr
WHERE action_type_id = -1
AND SYSDATE BETWEEN amemattr.start_date AND
NVL(amemattr.end_date-(1/86400),SYSDATE)
)
AND ameattr.attribute_id = ameatusg.attribute_id
AND ameatusg.application_id = app_id
AND ameattr.line_item = 'Y'
AND SYSDATE BETWEEN ameattr.start_date AND NVL(ameattr.end_date-(1/86400),SYSDATE)
AND SYSDATE BETWEEN ameatusg.start_date AND NVL(ameatusg.end_date-(1/86400),SYSDATE)
ORDER BY ameattr.line_item, ameattr.NAME;
CURSOR c_approval_groups IS
SELECT ameapg.approval_group_id,ameapg.name,ameapg.query_string,
ameapg.is_static
FROM ame_approval_groups ameapg
WHERE SYSDATE BETWEEN ameapg.start_date AND
NVL(ameapg.end_date-(1/86400),SYSDATE)
ORDER BY ameapg.is_static,ameapg.approval_group_id;
CURSOR c_approval_group_items (approval_group NUMBER) IS
SELECT ameapgi.approval_group_item_id, ameapgi.parameter_name,
ameapgi.parameter, ameapgi.order_number
FROM ame_approval_group_items ameapgi
WHERE ameapgi.approval_group_id = approval_group
AND SYSDATE BETWEEN ameapgi.start_date AND
NVL(ameapgi.end_date-(1/86400),SYSDATE)
ORDER BY ameapgi.order_number;
CURSOR c_config_vars (app_id NUMBER) IS
SELECT amecv.variable_name,amecv.variable_value
FROM ame_config_vars amecv
WHERE amecv.application_id = app_id
AND sysdate BETWEEN amecv.start_date AND
NVL(amecv.end_date-(1/86400),sysdate);
CURSOR c_config_default_vars IS
SELECT amecv.variable_name,amecv.variable_value
FROM ame_config_vars amecv
WHERE amecv.application_id is null
AND sysdate BETWEEN amecv.start_date AND
NVL(amecv.end_date-(1/86400),sysdate);
tx_typ_id VARCHAR2 (50);
txname VARCHAR2 (240);
prodname VARCHAR2 (240);
appid NUMBER;
ruleid ame_rules.rule_id%TYPE := 0;
s_no NUMBER;
color_code NUMBER := 1;
color_tag VARCHAR2 (7);
line_item_query VARCHAR2 (4000);
attribute_typ VARCHAR2 (1);
is_static_details is_static_table;
attr_name_details attr_name_table;
attr_type_details attr_type_table;
attr_values attr_value_table;
PROCEDURE printline (line VARCHAR2) IS
is_exist BOOLEAN := TRUE;
printstr VARCHAR2 (255);
idx NUMBER := 1;
BEGIN
WHILE is_exist LOOP
printstr := SUBSTR (line, idx, 255);
IF (LENGTH(printstr) IS NULL OR LENGTH (printstr) = 0) THEN
is_exist := FALSE;
ELSE
idx := idx + 255;
DBMS_OUTPUT.put_line (printstr);
END IF;
END LOOP;
END printline;
FUNCTION get_priority (appid NUMBER,ruletype NUMBER) RETURN boolean is
v_appid number;
v_ruletype number;
v_variable_value varchar2(200);
v_left_position number;
v_right_position number;
v_position_value varchar2(20);
begin
v_appid:=appid;
v_ruletype := ruletype;
begin
select x.variable_value into v_variable_value
from ame_config_vars x
where x.application_id=appid and
x.variable_name = 'rulePriorityModes' and
sysdate between x.start_date and nvl(x.end_date-(1/86400),sysdate);
exception
when no_data_found then
select x.variable_value into v_variable_value
from ame_config_vars x
where x.application_id is null and
x.variable_name = 'rulePriorityModes' and
sysdate between x.start_date and nvl(x.end_date-(1/86400),sysdate);
end;
v_variable_value := ':'||v_variable_value||':';
v_left_position := instr(v_variable_value,':',1,v_ruletype);
v_right_position := instr(v_variable_value,':',1,v_ruletype+1);
v_position_value := substr(v_variable_value,v_left_position+1,v_right_position-v_left_position-1);
if v_position_value = 'disabled' then
return false;
else
return true;
end if;
end get_priority;
procedure print_attributes (attribute_title varchar2,attr_name_details attr_name_table,attr_type_details attr_type_table,
is_static_details is_static_table,attr_values attr_value_table) is
begin
IF attr_name_details.COUNT > 0 THEN
printline ('<a name="'||attribute_title||'"></a><br>');
printline ('<a href="#top">Top</a>');
printline ('<TABLE width="100%" BORDER ="1" cellspacing=0><TR bgcolor="#000099"><TD colspan="4"><B><FONT COLOR="#00F2FA" FACE="arial,helvetica">'||attribute_title||' Attributes'||' :</FONT></B></TD></TR>');
printline ('<BR><TR bgcolor="#00F2FA"><TD>Attribute Name</TD><TD>Attribute Type</TD><TD>Static</TD><TD>Attribute Value</TD></TR>');
FOR attr_idx IN 1 .. attr_name_details.COUNT LOOP
printline ('<TR><TD width=20%>' || attr_name_details (attr_idx) || '</TD><TD>' || attr_type_details (attr_idx));
printline ('</TD><TD>');
printline(is_static_details (attr_idx));
printline ('</TD><TD>');
printline (NVL (TRIM (attr_values (attr_idx)), '.'));
printline ('</TD></TR>');
END LOOP;
printline ('</TABLE>');
END IF;
end print_attributes;
procedure print_rules_actions is
rule_details rule_id_table;
rule_desc_details rule_desc_table;
rule_type_details rule_type_table;
rule_type_num_details rule_type_num_table;
action_details action_id_table;
action_desc_details action_desc_table;
action_type_details action_type_name_table;
condn_details condn_id_table;
condn_type_details condn_type_table;
rule_usages_start_det rule_usages_start_date;
rule_usages_lifespan_det rule_usages_lifespan;
rule_usages_priority_det rule_usages_priority;
v_status boolean;
rule_type_state varchar2(30);
begin
printline ('<TABLE width="100%" BORDER ="1" cellspacing=0><TR bgcolor="#000099"><TD align=center><B><FONT COLOR="#00F2FA" FACE="arial,helvetica">Rule Details</FONT></B></TD></TR></TABLE>');
OPEN c_rule (appid);
FETCH c_rule BULK COLLECT INTO rule_details, rule_desc_details,rule_type_details,rule_type_num_details;
CLOSE c_rule;
rule_type_state:='initialized';
FOR idx IN 1 .. rule_details.COUNT LOOP
if rule_type_state <> rule_type_details (idx) then
printline ('<a name="Rule_Details'||rule_type_num_details (idx) || '"></a><br>');
rule_type_state := rule_type_details (idx);
printline ('<a href="#top">Top</a>');
printline (
'<TABLE width="100%" BORDER ="1" cellspacing=0><TR bgcolor="#000099"><TD align=left><B><FONT COLOR="#00F2FA" FACE="arial,helvetica">' || rule_type_state ||'</FONT></B></TD></TR></TABLE>'
);
end if;
printline ('<TABLE width="100%" BORDER ="1" cellspacing=0>');
printline ('<TR BGCOLOR="#00F2FA"><TD width=27%><B>Rule ID :' || rule_details (idx) || '</B></TD>');
printline ('<TD><B>Rule Description : ' || rule_desc_details (idx) || '</B></TD>');
printline ('</TR><TABLE width="100%" BORDER ="1" cellspacing=0>' || '<TR><TD width="12%" > </TD>');
printline ('<TD BGCOLOR="#cccc66" colspan="2"><B>' || 'Conditions :</B></TD></TR>');
OPEN c_condition (rule_details (idx));
FETCH c_condition BULK COLLECT INTO condn_details, condn_type_details;
CLOSE c_condition;
FOR con_idx IN 1 .. condn_details.COUNT LOOP
printline ('<TR><TD width="12%"> </TD><TD>');
printline (condn_details (con_idx) || '</TD><TD>');
printline (condn_type_details (con_idx) || ame_condition_pkg.getdescription (condn_details (con_idx)));
printline ('</TD></TR>');
END LOOP;
printline ('<TR><TD width="12%" > </TD><TD BGCOLOR="#cccc66" colspan="2"><B>' || 'Actions :</B></TD>');
printline('</TR>');
OPEN c_action (rule_details (idx));
FETCH c_action BULK COLLECT INTO action_details, action_type_details,action_desc_details;
CLOSE c_action;
FOR act_idx IN 1 .. action_details.COUNT LOOP
printline ('<TR><TD width="12%"> </TD><TD>');
printline (action_details (act_idx) || '</TD><TD>');
printline (action_type_details (act_idx) || ' : ' || action_desc_details (act_idx) || '</TD></TR>');
END LOOP;
--rule usages
OPEN c_rule_usages (appid,rule_details (idx));
FETCH c_rule_usages BULK COLLECT INTO rule_usages_start_det, rule_usages_lifespan_det,
rule_usages_priority_det;
CLOSE c_rule_usages;
printline ('<TR><TD width="12%" > </TD><TD BGCOLOR="#cccc66" colspan="2"><B>' || 'Usages :</B></TD>');
printline('<TR ><TD width="12%"></TD><TD bgcolor="#cccc66">Lifespan</TD><TD bgcolor="#cccc66">Priority</TD></TR>');
FOR usage_idx IN 1 .. rule_usages_start_det.COUNT LOOP
printline ('<TR><TD width="12%"> </TD><TD>');
if rule_usages_start_det (usage_idx) > sysdate then
printline('**' || rule_usages_lifespan_det (usage_idx) ||'</TD><TD>');
else
printline (rule_usages_lifespan_det (usage_idx) || '</TD><TD>');
end if;
--print priority if not disabled
v_status := get_priority(appId,rule_type_num_details(idx));
if v_status = true then
printline ( rule_usages_priority_det (usage_idx)|| '</TD></TR>');
else
printline ( 'disabled' || '</TD></TR>');
end if;
END LOOP;
printline ('</TABLE>');
END LOOP; --end for(227)
end print_rules_actions;
procedure print_approval_groups is
appr_grp_id_det approval_group_id_table;
appr_grp_query_det attr_value_table;
appr_grp_static_det is_static_table;
appr_grp_name_det approval_group_name_table;
appr_grp_item_id_det approval_group_id_table;
appr_par_nam_det approval_group_par_name_table;
appr_par_det approval_group_par_table;
appr_order_number approval_group_id_table;
v_approval_group_name varchar2(50);
begin
--approvers details
OPEN c_approval_groups;
FETCH c_approval_groups BULK COLLECT INTO appr_grp_id_det, appr_grp_name_det,
appr_grp_query_det,appr_grp_static_det;
CLOSE c_approval_groups;
printline ('<a name="Approval_Groups"></a><br>');
printline ('<a href="#top">Top</a>');
printline('<TABLE width="100%" BORDER ="1" cellspacing=0>');
printline('<TR bgcolor="#000099"><TD colspan="4" align="center"><B><FONT COLOR="#00F2FA" FACE="arial,helvetica">Approval Groups</FONT></B></TD></TR>');
FOR idx in 1..appr_grp_id_det.COUNT LOOP
printline('<TR bordercolor=blue><TD colspan="4" align="left">'||'<B>Group Id:</B>'||appr_grp_id_det (idx)||'<BR>');
printline('<B>Name:</B>'||appr_grp_name_det (idx)||'<BR>');
printline('<B>Static:</B>'||appr_grp_static_det (idx)||'<BR>');
if appr_grp_static_det (idx) = 'N' then
printline('<B>Query String:</B>'||appr_grp_query_det (idx)||'<BR>');
printline('</TD></TR>');
else
OPEN c_approval_group_items (appr_grp_id_det (idx));
FETCH c_approval_group_items BULK COLLECT INTO
appr_grp_item_id_det ,appr_par_nam_det,
appr_par_det,appr_order_number;
CLOSE c_approval_group_items;
if appr_grp_item_id_det.COUNT <> 0 then
printline('<TABLE width="100%" BORDER ="1" cellspacing=0>');
printline('<TR BGCOLOR="#cccc66"><TD><B>Order Number</B></TD><TD><B>Item Id</B></TD><TD><B>Parameter Type</B></TD><TD><B>Parameter Value</B></TD></TR>');
FOR idy in 1..appr_grp_item_id_det.COUNT LOOP
printline('<TR><TD>');
printline(appr_order_number (idy));
printline('</TD><TD>');
printline(appr_grp_item_id_det (idy));
printline('</TD><TD>');
printline(appr_par_nam_det (idy));
printline('</TD><TD>');
if appr_par_nam_det (idy) = 'OAM_group_id' then
select name into v_approval_group_name
from ame_approval_groups
where approval_group_id = appr_par_det (idy)
and sysdate between start_date and
nvl(end_date - (1/86400),sysdate);
printline(v_approval_group_name);
else
printline(appr_par_det (idy));
end if;
printline('</TD></TR>');
END LOOP;
printline('</TABLE>');
else
printline('<B>Members:</B> None');
end if;
printline('</TD></TR>');
end if;
END LOOP;
printline ('</TABLE>');
end print_approval_groups;
procedure print_configurations is
config_name_det config_vars_name_table;
config_value_det config_vars_value_table;
begin
--configuration variables for application
printline ('<a name="Config_Variables"></a><br>');
printline ('<a href="#top">Top</a>');
printline('<TABLE width="100%" BORDER ="1" cellspacing=0>');
printline('<TR bgcolor="#000099"><TD colspan="2" align="center"><B><FONT COLOR="#00F2FA" FACE="arial,helvetica">Configuration Variables</FONT></B></TD></TR>');
printline('<TR bgcolor="#00F2FA"><TD colspan="2" align="left"><B><FONT COLOR="#000099" FACE="arial,helvetica">Transaction Type Level</FONT></B></TD></TR>');
OPEN c_config_vars (appId);
FETCH c_config_vars BULK COLLECT INTO config_name_det,config_value_det;
CLOSE c_config_vars;
printline('<TR bgcolor="#cccc66"><TD>Variable Name</TD><TD>Variable Value</TD></TR>');
FOR idx in 1..config_name_det.COUNT LOOP
printline('<TR><TD>');
printline(config_name_det (idx));
printline('</TD><TD>');
printline(config_value_det (idx));
printline('</TD></TR>');
END LOOP;
printline('<TR bgcolor="#00F2FA"><TD colspan="2" align="left"><B><FONT COLOR="#000099" FACE="arial,helvetica">Default</FONT></B></TD></TR>');
printline('<TR bgcolor="#cccc66"><TD>Variable Name</TD><TD>Variable Value</TD></TR>');
OPEN c_config_default_vars;
FETCH c_config_default_vars BULK COLLECT INTO config_name_det,config_value_det;
CLOSE c_config_default_vars;
FOR idx in 1..config_name_det.COUNT LOOP
printline('<TR><TD>');
printline(config_name_det (idx));
printline('</TD><TD>');
printline(config_value_det (idx));
printline('</TD></TR>');
END LOOP;
printline ('</TABLE>');
end print_configurations;
procedure print_action_types is
action_details action_id_table;
action_desc_details action_desc_table;
action_type_details action_type_name_table;
action_param_details action_parameter_table;
act_typ_id_details action_type_id_table;
attr_name_details attr_name_table;
attr_type_details attr_type_table;
begin
--action_types
OPEN c_action_types;
FETCH c_action_types BULK COLLECT INTO act_typ_id_details, action_type_details;
CLOSE c_action_types;
IF act_typ_id_details.COUNT > 0 THEN
printline ('<BR><TABLE width="100%" BORDER ="1" cellspacing=0><TR bgcolor="#000099"><TD colspan="3" align="center"><B><FONT COLOR="#00F2FA" FACE="arial,helvetica">Action Types</FONT></B></TD></TR></table>');
printline ('<a name="Action_Types"></a>');
printline ('<a href="#top">Top</a><br>');
--give links to different action types
printline ('<a href ="#Action_Types">Action Types</a>');
printline ('<ul>');
FOR act_typ_idx in 1..act_typ_id_details.count loop
printline ('<li><a href = "#actiontype'||act_typ_id_details(act_typ_idx)||'">'||action_type_details(act_typ_idx)||'</a>');
end loop;
printline ('</ul>');
FOR act_typ_idx IN 1 .. act_typ_id_details.COUNT LOOP
printline ('<BR><a href="#Action_Types">Back to Action Types</a>');
printline ('<a name="actiontype'||act_typ_id_details (act_typ_idx) ||'"></a>');
printline ('<TABLE width="100%" BORDER ="1" cellspacing=0>');
printline ('<BR><TR BGCOLOR="#00F2FA"><TD width=27%><B>Action Type ID :' || act_typ_id_details (act_typ_idx) || '</B></TD>');
printline ('<TD colspan="2"><B>Action Type Description : ' || action_type_details (act_typ_idx) || '</B></TD>');
printline ('</TR>');
OPEN c_action_type_attr_details (act_typ_id_details (act_typ_idx));
FETCH c_action_type_attr_details BULK COLLECT INTO attr_name_details, attr_type_details;
CLOSE c_action_type_attr_details;
IF attr_name_details.COUNT > 0 THEN
printline ('<TR>');
printline ('<TD BGCOLOR="#cccc66" colspan="3"><B>' || 'Required Attributes :</B></TD>');
printline ('</TR>');
FOR attr_idx IN 1 .. attr_name_details.COUNT LOOP
printline ('<TR>');
printline ('<TD>' || attr_name_details (attr_idx) || '</TD>');
printline ('<TD colspan="2">' || attr_type_details (attr_idx) || '</TD>');
printline ('</TR>');
END LOOP;
END IF;
OPEN c_action_type_action_details (act_typ_id_details (act_typ_idx));
FETCH c_action_type_action_details BULK COLLECT INTO action_details, action_desc_details,action_param_details;
CLOSE c_action_type_action_details;
printline ('<TR BGCOLOR="#cccc66" >');
printline ('<TD ><B>' || 'Action Id</B></TD>');
printline('<TD><B>Action Description</B></TD>');
printline('<TD><B>Parameter</B></TD>');
printline ('</TR>');
IF action_details.COUNT > 0 THEN
FOR act_idx IN 1 .. action_details.COUNT LOOP
printline ('<TR>');
printline ('<TD>' || action_details (act_idx) || '</TD>');
printline ('<TD>' || action_desc_details (act_idx) || '</TD>');
printline('<TD>' || action_param_details (act_idx) || '</TD>');
printline ('</TR>');
END LOOP;
ELSE
printline ('<TR>');
printline ('<TD colspan="2" align="center">[none]</TD>');
printline ('</TR>');
END IF;
printline ('</TABLE>');
END LOOP;
END IF;
end print_action_types;
--main part of code
BEGIN
printline ('<!--');
appId := to_number(&1);
printline ('-->');
SELECT ameapp.application_name, fndapp.application_name,ameapp.application_id, ameapp.line_item_id_query,ameapp.transaction_type_id
INTO txname, prodname, appid, line_item_query, tx_typ_id
FROM ame_calling_apps ameapp, fnd_application_vl fndapp
WHERE ameapp.application_id = appId
AND fndapp.application_id = ameapp.fnd_application_id
AND SYSDATE BETWEEN ameapp.start_date AND NVL((ameapp.end_date-(1/86400)),SYSDATE);
printline ('<HTML>');
printline ('<head>');
printline ('<TITLE>AME Report</TITLE>');
printline ('<style type="text/css">');
printline ('a');
printline ('{');
printline ('color: blue;');
printline ('text-decoration: none;');
printline ('background: transparent;');
printline ('}');
printline ('a:hover');
printline ('{');
printline ('background: #ccddcc;');
printline ('text-decoration: none;');
printline ('color: #662222;');
printline ('}');
printline ('</style>');
printline ('</head>');
printline ('<BODY font="Arial">');
printline ('<p align=center><B><font size=+1>AME Setup Details for Transaction Type : ' || txname || '</font></B></p>');
printline ('<TABLE width="100%" BORDER ="1" cellspacing=0 BGCOLOR="#339999"><TR>');
printline ('<TD><B>Product Name : </B></TD><TD>' || prodname || '</TD></TR>');
printline ('<TR><TD><B>Transaction Type ID : </B></TD><TD>' || tx_typ_id || '</TD></TR>');
if line_item_query is not null then
printline ('<TR><TD><B>Line Item Query : </B></TD><TD>');
printline ( TRIM (line_item_query) ||'</TD><BR></TR>');
end if;
printline ('</TABLE><BR>');
--links
printline ('<a name="top"></a>');
printline ('<a href="#Rule_Details1"><B>Rule Details</B></a><br>');
printline ('<ul>');
printline ('<li><a href="#Rule_Details1"><B>List Creation Rules</B></a>');
printline ('<li><a href="#Rule_Details2"><B>List Creation Exception Rules</B></a>');
printline ('<li><a href="#Rule_Details3"><B>List Modification Rules</B></a>');
printline ('<li><a href="#Rule_Details4"><B>Substitution Rules</B></a>');
printline ('<li><a href="#Rule_Details5"><B>Pre List Rules</B></a>');
printline ('<li><a href="#Rule_Details6"><B>Post List Rules</B></a>');
printline ('</ul>');
printline ('<a href="#Mandatory"><B>Mandatory Attributes</B></a><br>');
printline ('<a href="#Header"><B>Header Attributes</B></a><br>');
if line_item_query is not null then
printline ('<a href="#Line"><B>Line Item Attributes</B></a><br>');
end if;
printline ('<a href="#Action_Types"><B>Action Types</B></a><br>');
printline ('<a href="#Approval_Groups"><B>Approval Groups</B></a><br>');
printline ('<a href="#Config_Variables"><B>Configuration Variables</B></a><br>');
--end of links code
--print rules and associated conditions and actions
print_rules_actions();
--mandatory_item
OPEN c_attr_details_mandatory_item (appid);
FETCH c_attr_details_mandatory_item BULK COLLECT
INTO attr_name_details,attr_type_details,attr_values,is_static_details;
CLOSE c_attr_details_mandatory_item;
print_attributes('Mandatory',attr_name_details,attr_type_details,is_static_details,attr_values);
--header_item
OPEN c_attr_details_header_item (appid);
FETCH c_attr_details_header_item BULK COLLECT INTO attr_name_details,attr_type_details,attr_values,
is_static_details;
CLOSE c_attr_details_header_item;
print_attributes('Header',attr_name_details,attr_type_details,is_static_details,attr_values);
--line_item
OPEN c_attr_details_line_item (appid);
FETCH c_attr_details_line_item BULK COLLECT INTO attr_name_details,attr_type_details,attr_values,
is_static_details;
CLOSE c_attr_details_line_item;
print_attributes('Line',attr_name_details,attr_type_details,is_static_details,attr_values);
--print action_types along with mandatory attributes and action ids of each action type
print_action_types();
--print approval groups and their members
print_approval_groups();
--print application and default configuration variables
print_configurations();
printline ('</BODY>');
printline ('</HTML>');
EXCEPTION
WHEN NO_DATA_FOUND THEN
printline ('No Data Found for the input.');
END;
/
SPOOL off;
EXIT;
Script2:
--usage example: sqlplus @apps/apps/juldbgqa @getPost11510AMESetup.sql -735
set serveroutput on size 1000000
set feedback off
set echo off
set termout off
spool Report_AppId&1..htm
declare
--+
applicationId number;
itemClassIdList ame_util.idList;
itemClassNamesList ame_util.stringList;
applicationFound boolean;
--+
type invalidApprRec is record(
roleName varchar2(200),
origsystem varchar2(75),
source varchar2(1000)
);
type invApprList is table of invalidApprRec index by binary_integer;
ame_invalappr invApprList;
--+
--+
procedure printLine (line varchar2) is
isExist boolean := true;
str varchar2 (255);
idx number := 1;
begin
while isExist loop
str := substr (line, idx, 255);
if (length(str) is null or length (str) = 0) then
isExist := false;
else
idx := idx + 255;
dbms_output.put_line(str);
end if;
end loop;
end printLine;
--+
procedure startHtml as
begin
printLine('<html>');
printLine('<head>');
printLine('<title>AME Report</title>');
printLine('<style type="text/css">');
printLine('.OraTableRowHeader');
printLine('{');
printLine(' font-family:Arial, Helvetica, Geneva, sans-serif;');
printLine(' font-size:10pt;');
printLine(' font-weight:bold;');
printLine(' text-align:left;');
printLine(' background-color:#cccc99;');
printLine(' color:#336699;');
printLine(' text-indent:1');
printLine('}');
printLine('tr');
printLine('{');
printLine(' font-family:Arial, Helvetica, Geneva, sans-serif;');
printLine(' font-size:10pt;');
printLine(' background-color:#f7f7e7;');
printLine(' color:#000000;');
printLine('}');
printLine('A:link');
printLine('{');
printLine(' font-family:Arial, Helvetica, Geneva, sans-serif;');
printLine(' font-size:10pt;');
printLine(' color:#663300');
printLine('}');
printLine('A:active');
printLine('{');
printLine(' font-family:Arial, Helvetica, Geneva, sans-serif;');
printLine(' font-size:10pt;');
printLine(' color:#ff6600');
printLine('}');
printLine('A:visited');
printLine('{');
printLine(' font-family:Arial, Helvetica, Geneva, sans-serif;');
printLine(' font-size:10pt;');
printLine(' color:#996633');
printLine('}');
printLine('</style>');
printLine('</head>');
printLine('<body bgcolor="#ffffff">');
end startHtml;
--+
procedure closeHtml as
begin
printLine('</body>');
printLine('</html>');
end closeHtml;
--+
procedure startTable as
begin
--printLine('<br>');
printLine('<table width="100%" border="1" cellspacing="0" >');
end startTable;
--+
procedure startTable2 as
begin
printLine('<br>');
printLine('<table width="100%" border="1" cellspacing="0" >');
end startTable2;
--+
procedure closeTable is
begin
printLine('</table>');
end closeTable;
--+
procedure startTableRow(styleIn in varchar2 default null) as
begin
if styleIn is not null then
printLine('<tr class='||styleIn||'>');
else
printLine('<tr>');
end if;
end startTableRow;
--+
procedure closeTableRow as
begin
printLine('</tr>');
end closeTableRow;
--+
procedure lineBreak as
begin
printLine('<br>');
end lineBreak;
--+
procedure anchor(nameIn in varchar2) as
begin
printLine('<a name='||nameIn||'></a><br>');
end anchor;
--+
procedure printTableCell(contentIn in varchar2
,colSpanIn in integer default null) as
printString varchar2(5000);
content ame_util.longestStringType;
contentLength integer;
begin
if colSpanIn is null then
printString := '<td>';
else
printString := '<td colspan="'||colspanIn||'">';
end if;
content := trim(contentIn);
contentLength := lengthb(content);
if contentLength = 0 then
content := '&'||'nbsp;';
end if;
printLine(printString||content||'</td>');
end printTableCell;
--+
--+
--+
procedure createLinktoTop as
begin
printLine('<p align=right><a href="#top">Top</a></p>');
end createLinktoTop;
--+
procedure createLink(link1In in varchar2
,link1DescIn in varchar2
,link2In in varchar2 default null
,link2DescIn in varchar2 default null) as
begin
printLine('<p align=right>');
printLine('<a href="#'||link1In||'">'||link1DescIn||'</a>&'||'nbsp;');
if link2In is not null then
printLine('<a href="#'||link2In||'">'||link2DescIn||'</a>&'||'nbsp;');
end if;
printLine('<a href="#top">Top</a></p>');
end createLink;
--+
--+
procedure createHtmlLinks as
begin
printLine('<ul>');
printLine('<li><a href="#icu">Item Classes</a>');
printLine('<li><a href="#man">Mandatory Attributes</a>');
printLine('<li><a href="#atr">Attributes</a>');
printLine('<li><a href="#othercond">Conditions</a>');
printLine('<li><a href="#rules">Rules</a>');
printLine('<li><a href="#act">Action Types</a>');
printLine('<li><a href="#TransAgr">Approval Groups</a>');
printLine('<li><a href="#cfg">Configuration Variables</a>');
printLine('<li><a href="#appr">Approver Type Details</a>');
printLine('<li><a href="#invAppr">Invalid Approvers</a>');
printLine('<li><a href="#inval">Invalid AME Objects </a>');
printLine('<li><a href="#AMEver">AME Patch Details</a>');
printLine('<li><a href="#FPver">Family Pack Version</a>');
printLine('<li><a href="#APPVer">Application Release Version</a>');
printLine('<li><a href="#file">AME Key File Version</a>');
printLine('</ul>');
end createHtmlLinks;
--+
--+
--+
procedure printTrasactionTypeInfo(applicationIdIn in number
,transactionTypeFound out boolean) as
fndApplicationName ame_util.stringType;
applicationName ame_util.stringType;
transactionTypeId ame_util.stringType;
cursor getTxTypeInfo is
select fnd.application_name
,aca.application_name
,aca.transaction_type_id
from fnd_application_vl fnd
,ame_calling_apps aca
where aca.fnd_application_id = fnd.application_id
and aca.application_id = applicationIdIn
and sysdate between aca.start_date and nvl(aca.end_date - (1/86400),sysdate);
begin
startTable;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'AME Setup Details');
closeTableRow;
closeTable;
open getTxTypeInfo;
fetch getTxTypeInfo into
fndApplicationName
,applicationName
,transactionTypeId;
if getTxTypeInfo%notfound then
transactionTypeFound := false;
startTable2;
startTableRow;
printTableCell(contentIn => 'applicationId '||applicationIdIn ||' Not Found');
closeTableRow;
close getTxTypeInfo;
return;
else
transactionTypeFound := true;
end if;
close getTxTypeInfo;
startTable2;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'Product Name');
printTableCell(contentIn => fndApplicationName);
closeTableRow;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'Transaction Type Name');
printTableCell(contentIn => applicationName);
closeTableRow;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'TransactionType ID');
printTableCell(contentIn => transactionTypeId);
closeTableRow;
closeTable;
end printTrasactionTypeInfo;
--+
--+
--+
procedure printItemClassUsages(applicationIdIn in number
,itemClassNamesOut out nocopy ame_util.stringList
,itemClassIdsOut out nocopy ame_util.idList)as
itemClassQueryList ame_util.longestStringList;
itemClassONList ame_util.idList;
itemClassPMList ame_util.stringList;
itemClassSLMList ame_util.stringList;
cursor itemClassUsageCursor(applicationIdIn in number)is
select ic.item_class_id
,ic.name
,icu.item_id_query
,icu.item_class_order_number
,decode(icu.item_class_par_mode
,'S'
,'Serial'
,'P'
,'Parallel'
,icu.item_class_par_mode)
,decode(icu.item_class_sublist_mode
,'S'
,'Serial'
,'P'
,'Parallel'
,'R'
,'pre-approvers first, then authority and post-approvers'
,'A'
,'pre-approvers and authority approvers first, then post-approvers'
,icu.item_class_sublist_mode)
from ame_item_classes ic
,ame_item_class_usages icu
where ic.item_class_id = icu.item_class_id
and icu.application_id = applicationIdIn
and sysdate between ic.start_date and nvl(ic.end_date - (1/86400),sysdate)
and sysdate between icu.start_date and nvl(icu.end_date - (1/86400),sysdate)
order by icu.item_class_order_number;
begin
open itemClassUsageCursor(applicationIdIn => applicationIdIn);
fetch itemClassUsageCursor bulk collect
into
itemClassIdsOut
,itemClassNamesOut
,itemClassQueryList
,itemClassONList
,itemClassPMList
,itemClassSLMList;
close itemClassUsageCursor;
anchor('icu');
startTable;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'Item Classes'
,colSpanIn => 6);
closeTableRow;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'ID');
printTableCell(contentIn => 'Name');
printTableCell(contentIn => 'QueryString');
printTableCell(contentIn => 'Order Number');
printTableCell(contentIn => 'Parallelisation Mode');
printTableCell(contentIn => 'Sub List Mode');
closeTableRow;
for i in 1 .. itemClassIdsOut.count loop
startTableRow;
printTableCell(contentIn => itemClassIdsOut(i));
printTableCell(contentIn => itemClassNamesOut(i));
printTableCell(contentIn => itemClassQueryList(i));
printTableCell(contentIn => itemClassONList(i));
printTableCell(contentIn => itemClassPMList(i));
printTableCell(contentIn => itemClassSLMList(i));
closeTableRow;
end loop;
closeTable;
end printItemClassUsages;
--+
--+
--+
procedure printAttributesByIC(applicationIdIn in number
,itemClassIdIn in number
,itemClassNameIn in varchar2 ) as
attributeIdList ame_util.idList;
attributeNamesList ame_util.stringList;
attributeQList ame_util.longestStringList;
attributeDescList ame_util.stringList;
attributeStaticList ame_util.charList;
attrUseCountList ame_util.stringList;
cursor attributeUsageCursor(applicationIdIn in number)is
select atr.attribute_id
,atr.name
,atu.query_string
,atr.description
,atu.is_static
,atu.use_count
from ame_attributes atr
,ame_attribute_usages atu
where atr.attribute_id = atu.attribute_id
and atu.application_id = applicationIdIn
and atr.item_class_id = itemClassIdIn
and sysdate between atr.start_date and nvl(atr.end_date - (1/86400),sysdate)
and sysdate between atu.start_date and nvl(atu.end_date - (1/86400),sysdate)
and atr.attribute_id not in
(select attribute_id
from ame_mandatory_attributes man
where man.action_type_id = -1
and sysdate between man.start_date and nvl(man.end_date - (1/86400),sysdate)
)
order by atr.name;
begin
open attributeUsageCursor(applicationIdIn => applicationIdIn);
fetch attributeUsageCursor bulk collect
into
attributeIdList
,attributeNamesList
,attributeQList
,attributeDescList
,attributeStaticList
,attrUseCountList;
close attributeUsageCursor;
anchor('atr');
startTable;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => initcap(itemClassNameIn) ||' Attributes'
,colspanIn => 5);
closeTableRow;
closeTable;
startTable;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'ID');
printTableCell(contentIn => 'Name');
printTableCell(contentIn => 'QueryString');
printTableCell(contentIn => 'Description');
printTableCell(contentIn => 'is Static');
printTableCell(contentIn => 'Use Count');
closeTableRow;
for i in 1 .. attributeIdList.count loop
startTableRow;
printTableCell(contentIn => attributeIdList(i));
printTableCell(contentIn => attributeNamesList(i));
printTableCell(contentIn => attributeQList(i));
printTableCell(contentIn => attributeDescList(i));
printTableCell(contentIn => attributeStaticList(i));
printTableCell(contentIn => attrUseCountList(i));
closeTableRow;
end loop;
closeTable;
createLinktoTop;
end printAttributesByIC;
--+
--+
--+
procedure printMandatoryAttributes(applicationIdIn in number) as
attributeIdList ame_util.idList;
attributeNamesList ame_util.stringList;
attributeQList ame_util.longestStringList;
attributeDescList ame_util.stringList;
attributeStaticList ame_util.charList;
cursor attributeUsageCursor(applicationIdIn in number)is
select atr.attribute_id
,atr.name
,atu.query_string
,atr.description
,atu.is_static
from ame_attributes atr
,ame_attribute_usages atu
where atr.attribute_id = atu.attribute_id
and atu.application_id = applicationIdIn
and sysdate between atr.start_date and nvl(atr.end_date - (1/86400),sysdate)
and sysdate between atu.start_date and nvl(atu.end_date - (1/86400),sysdate)
and atr.attribute_id in
(select attribute_id
from ame_mandatory_attributes man
where man.action_type_id = -1
and sysdate between man.start_date and nvl(man.end_date - (1/86400),sysdate)
)
order by atr.name;
begin
open attributeUsageCursor(applicationIdIn => applicationIdIn);
fetch attributeUsageCursor bulk collect
into
attributeIdList
,attributeNamesList
,attributeQList
,attributeDescList
,attributeStaticList;
close attributeUsageCursor;
anchor('man');
startTable;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'Mandatory attributes'
,colspanIn => 5);
closeTableRow;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'ID');
printTableCell(contentIn => 'Name');
printTableCell(contentIn => 'QueryString');
printTableCell(contentIn => 'Description');
printTableCell(contentIn => 'is Static');
closeTableRow;
for i in 1 .. attributeIdList.count loop
startTableRow;
printTableCell(contentIn => attributeIdList(i));
printTableCell(contentIn => attributeNamesList(i));
printTableCell(contentIn => attributeQList(i));
printTableCell(contentIn => attributeDescList(i));
printTableCell(contentIn => attributeStaticList(i));
closeTableRow;
end loop;
closeTable;
createLinktoTop;
end printMandatoryAttributes;
--+
--+
--+
/* function getActionDescription(actionIdIn in number)return varchar2 as
begin
return ame_action_pkg.getDescription(actionIdIn);
exception
when others then
return to_char(actionIdIn);
end getActionDescription; */
function checkDuplicate(expressionIn in varchar2) return boolean is
inval_index number;
begin
inval_index := ame_invalappr.count;
for i in 1..inval_index loop
if trim(expressionIn)=trim(ame_invalappr(i).source)then
return true;
end if;
end loop;
return false;
end ;
function getActionDescription(actionIdIn in number)return varchar2 as
actionTypeId number;
tempActionDescr varchar2(800);
beforeColon varchar2(800);
afterColon varchar2(800);
wf_role_name varchar2(200);
inval_index integer;
begin
tempActionDescr := ame_action_pkg.getDescription(actionIdIn);
actionTypeId := ame_action_pkg.getActionTypeIdById(actionIdIn =>actionIdIn);
if ame_action_pkg.getActionTypeName(actionTypeId) =ame_util.substitutionTypeName then
wf_role_name := ame_action_pkg.getParameter(actionIdIn);
if ame_approver_type_pkg.validateApprover(wf_role_name) then
null;
else
if checkDuplicate('actionId:'||actionIdIn)<> true then
inval_index := ame_invalappr.count;
ame_invalappr(inval_index+1).roleName := wf_role_name;
ame_invalappr(inval_index+1).source := 'actionId:'||actionIdIn;
end if;
end if;
end if;
return tempActionDescr;
exception
when others then
return to_char(actionIdIn);
end getActionDescription;
--+
--+
--+
function getConditionDescription(conditionIdIn in number)return varchar2 as
begin
return ame_condition_pkg.getDescription(conditionIdIn);
exception
when others then
return to_char(conditionIdIn);
end getConditionDescription;
--+
--+
--+
procedure printRules(applicationIdIn in number
,itemClassIdsIn in ame_util.idList
,itemClassNamesIn in ame_util.stringList) as
activeRuleIdList ame_util.idList;
activeRuleDescList ame_util.stringList;
conditionIdList ame_util.idList;
conditionTypeList ame_util.stringList;
actionIdList ame_util.idList;
startDateList ame_util.stringList;
endDateList ame_util.stringList;
priorityList ame_util.stringList;
categoryList ame_util.stringList;
ruleTypeDescList ame_util.stringList;
currentDesc ame_util.stringType;
currentLink ame_util.stringType;
rule_keyList ame_util.stringList;
cursor activRuleCursor(applicationIdIn in number
,itemClassIdIn in number
,ruleTypeIn in number) is
select rul.rule_id
,rul.description
,rul.rule_key
from ame_rules rul
,ame_rule_usages rlu
where rul.rule_type = ruleTypeIn
and ((ruleTypeIn = 3 or ruleTypeIn = 4) or (rul.item_class_id = itemClassIdIn))
and rul.rule_id = rlu.rule_id
and rlu.item_id = applicationIdIn
and (sysdate between rul.start_date and nvl(rul.end_date - (1/86400),sysdate)
or
rul.start_date > sysdate and (rul.end_date is null or rul.end_date > rul.start_date)
)
and (sysdate between rlu.start_date and nvl(rlu.end_date - (1/86400),sysdate)
or
rlu.start_date > sysdate and (rlu.end_date is null or rlu.end_date > rlu.start_date)
);
cursor conditionUsageCursor(ruleIdIn in number) is
select acu.condition_id
,decode(ac.condition_type,'auth',' ','pre','Exception : ','post', 'List-Modification : ')
from ame_condition_usages acu
,ame_conditions ac
where acu.rule_id = ruleIdIn
and ac.condition_id = acu.condition_id
and sysdate between ac.start_date and nvl(ac.end_date - (1/86400),sysdate)
and (sysdate between acu.start_date and nvl(acu.end_date - (1/86400),sysdate)
or
acu.start_date > sysdate and (acu.end_date is null or acu.end_date > acu.start_date)
);
cursor actionUsageCursor(ruleIdIn in number) is
select act.action_id
from ame_action_usages acu
,ame_actions act
where acu.rule_id = ruleIdIn
and act.action_id = acu.action_id
and sysdate between act.start_date and nvl(act.end_date - (1/86400),sysdate)
and (sysdate between acu.start_date and nvl(acu.end_date - (1/86400),sysdate)
or
acu.start_date > sysdate and (acu.end_date is null or acu.end_date > acu.start_date)
);
cursor ruleUsageCursor(applicationIdIn in number
,ruleIdIn in number) is
select rlu.start_date
,decode(rlu.end_date
,null
,rlu.start_date || ' Onward'
,rlu.start_date || ' to ' || rlu.end_date)
,nvl(to_char(rlu.priority),'disabled')
,decode(rlu.approver_category,'A','Action','F','FYI',rlu.approver_category)
from ame_rules rul
,ame_rule_usages rlu
where rul.rule_id = ruleIdIn
and rul.rule_id = rlu.rule_id
and rlu.item_id = applicationIdIn
and (sysdate between rul.start_date and nvl(rul.end_date - (1/86400),sysdate)
or
rul.start_date > sysdate and (rul.end_date is null or rul.end_date > rul.start_date)
)
and (sysdate between rlu.start_date and nvl(rlu.end_date - (1/86400),sysdate)
or
rlu.start_date > sysdate and (rlu.end_date is null or rlu.end_date > rlu.start_date)
)
order by rlu.start_date;
begin
ruleTypeDescList(1) := 'Combination';
ruleTypeDescList(2) := 'List Creation';
ruleTypeDescList(3) := 'List Creation Exception';
ruleTypeDescList(4) := 'List Modification';
ruleTypeDescList(5) := 'Substitution';
ruleTypeDescList(6) := 'Pre-list';
ruleTypeDescList(7) := 'Post-list';
ruleTypeDescList(8) := 'Production';
anchor('rules');
for i in 0 .. 7 loop
for j in 1 .. itemClassIdsIn.count loop
open activRuleCursor(applicationIdIn => applicationIdIn
,ruleTypeIn => i
,itemClassIdIn => itemClassIdsIn(j));
fetch activRuleCursor bulk collect
into
activeRuleIdList
,activeRuleDescList
,rule_keyList;
close activRuleCursor;
--
if activeRuleIdList.count > 0 then
currentDesc := initcap(itemClassNamesIn(j))
|| ' '
|| ruleTypeDescList(i+1)
|| ' Rules';
currentLink := to_char(i)||'_'||lower(substr(itemClassNamesIn(j),1,3))||'rules';
anchor(currentLink);
startTable;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => currentDesc
,colSpanIn => 6);
closeTableRow;
closeTable;
lineBreak;
for x in 1 .. activeRuleIdList.count loop
-- print rule id and desc
startTable;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'Rule ID : '|| activeRuleIdList(x)
,colspanIn => 1);
printTableCell(contentIn => 'Rule Key : '|| rule_keyList(x)
,colspanIn => 2);
printTableCell(contentIn => 'Description : '|| activeRuleDescList(x)
,colspanIn => 5);
closeTableRow;
open conditionUsageCursor(ruleIdIn => activeRuleIdList(x));
fetch conditionUsageCursor bulk collect
into
conditionIdList
,conditionTypeList;
close conditionUsageCursor;
-- print cond details
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'Conditions :'
,colspanIn => 7);
closeTableRow;
for k in 1 .. conditionIdList.count loop
startTableRow;
printTableCell(contentIn => conditionIdList(k)
,colSpanIn => 2);
printTableCell(contentIn => conditionTypeList(k)
||getConditionDescription(conditionIdList(k))
,colSpanIn => 5);
closeTableRow;
end loop;
if conditionIdList.count = 0 then
startTableRow;
printTableCell(contentIn => 'No Conditions'
,colSpanIn => 7);
closeTableRow;
end if;
open actionUsageCursor(ruleIdIn => activeRuleIdList(x));
fetch actionUsageCursor bulk collect
into
actionIdList;
close actionUsageCursor;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'Actions :'
,colspanIn => 7);
closeTableRow;
for k in 1 .. actionIdList.count loop
startTableRow;
printTableCell(contentIn => actionIdList(k)
,colSpanIn => 2);
printTableCell(contentIn => getActionDescription(actionIdList(k))
,colSpanIn => 5);
closeTableRow;
end loop;
open ruleUsageCursor(applicationIdIn => applicationIdIn
,ruleIdIn => activeRuleIdList(x));
fetch ruleUsageCursor bulk collect
into
startDateList
,endDateList
,priorityList
,categoryList;
close ruleUsageCursor;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'Usages'
,colSpanIn => 7);
closeTableRow;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'Lifespan'
,colSpanIn => 2);
printTableCell(contentIn => 'Priority'
,colSpanIn => 3);
printTableCell(contentIn => 'Category'
,colSpanIn => 2);
closeTableRow;
for k in 1 .. startDateList.count loop
startTableRow;
printTableCell(contentIn => endDateList(k)
,colSpanIn => 2);
printTableCell(contentIn => priorityList(k)
,colSpanIn => 3);
printTableCell(contentIn => categoryList(k)
,colSpanIn => 2);
closeTableRow;
end loop;
closeTable;
createLink(link1In => currentLink
,link1DescIn => currentDesc
,link2In => 'rules'
,link2DescIn => 'Rules');
conditionIdList.delete;
conditionTypeList.delete;
actionIdList.delete;
startDateList.delete;
endDateList.delete;
priorityList.delete;
categoryList.delete;
end loop; -- loop for each rule
end if; --
end loop; -- loop for each item class
end loop; -- rule type loop
end printRules;
--+
--+
--+
procedure printActionTypes(applicationIdIn in number) as
actionTypeIdList ame_util.idList;
actionTypeNameList ame_util.stringList;
actionTypeDDList ame_util.charList;
actIdList ame_util.charList;
actionIdList ame_util.idList;
actionDescList ame_util.stringList;
actionParam1List ame_util.longStringList;
actionParam2List ame_util.longStringList;
attributeNameList ame_util.stringList;
attributeTypeList ame_util.stringList;
orderNumberList ame_util.idList;
VRList ame_util.stringList;
COAOMList ame_util.stringList;
ruleTypeList ame_util.idList;
currentRuleType integer;
tempActionDescr varchar2(300);
cursor actionTypeCursor is
select aty.action_type_id
,aty.name
,aty.dynamic_description
from ame_action_types aty
where sysdate between aty.start_date and nvl(aty.end_date - (1/86400),sysdate)
and aty.action_type_id in
(select action_type_id
from ame_action_type_config
where application_id = applicationIdIn
and sysdate between start_date
and nvl(end_date-(1/86400),end_date));
--and rownum < 5;
cursor actionCursor(actionTypeIdIn in number) is
select act.action_id
,act.parameter
,act.parameter_two
,act.description
from ame_actions act
where act.action_type_id = actionTypeIdIn
and sysdate between act.start_date and nvl(act.end_date - (1/86400),sysdate)
and act.created_by not in (1,120);
cursor reqAttributeCursor(actionTypeIdIn in number) is
select atr.name
,atr.attribute_type
from ame_attributes atr
where sysdate between atr.start_date and nvl(atr.end_date - (1/86400),sysdate)
and atr.attribute_id in
(select attribute_id
from ame_mandatory_attributes man
where man.action_type_id = actionTypeIdIn
and sysdate between man.start_date and nvl(man.end_date - (1/86400),sysdate)
);
cursor substActionCursor(actionTypeIdIn in number) is
select act.action_id
,act.parameter
,act.parameter_two
,act.description
from ame_actions act
where act.action_type_id = actionTypeIdIn
and sysdate between act.start_date and nvl(act.end_date - (1/86400),sysdate)
and action_id in (
select acu.action_id
from ame_rule_usages aru
,ame_action_usages acu
where aru.item_id = applicationIdIn
and acu.rule_id = aru.rule_id
and sysdate between aru.start_date and
nvl(aru.end_date-(1/86400),sysdate)
and sysdate between acu.start_date and
nvl(aru.end_date-(1/86400),sysdate));
cursor actionTypeCfgCursor(applicationIdIn in number) is
select acf.order_number
,decode(acf.voting_regime
,'S'
,'Serial'
,'C'
,'Consensus'
,'F'
,'First Responder Wins'
,acf.voting_regime)
,decode(acf.chain_ordering_mode
,'S'
,'Serial'
,'P'
,'Parallel'
,acf.chain_ordering_mode)
,aty.action_type_id
,aty.name
,decode(atu.rule_type,2,1,atu.rule_type)
from ame_action_type_config acf
,ame_action_types aty
,ame_action_type_usages atu
where acf.application_id = applicationIdIn
and aty.action_type_id = acf.action_type_id
and aty.action_type_id = atu.action_type_id
and sysdate between aty.start_date and nvl(aty.end_date - (1/86400),sysdate)
and sysdate between atu.start_date and nvl(atu.end_date - (1/86400),sysdate)
and sysdate between acf.start_date and nvl(acf.end_date - (1/86400),sysdate)
order by atu.rule_type,acf.order_number;
begin
anchor('act');
startTable;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'Action Types');
closeTableRow;
closeTable;
lineBreak;
open actionTypeCfgCursor(applicationIdIn => applicationIdIn);
fetch actionTypeCfgCursor bulk collect
into
orderNumberList
,VRList
,COAOMList
,actionTypeIdList
,actionTypeNameList
,ruleTypeList;
close actionTypeCfgCursor;
--+
startTable2;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => ' Action Types being used in current Transaction Type'
,colspanIn => 5);
closeTableRow;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'ID');
printTableCell(contentIn => 'Name');
printTableCell(contentIn => 'Order Number');
printTableCell(contentIn => 'Voting Regime');
printTableCell(contentIn => 'COA Ordering Mode');
closeTableRow;
currentRuleType := ruleTypeList(1);
for i in 1 .. actionTypeIdList.count loop
/*if currentRuleType <> ruleTypeList(i) then
startTableRow;
printTableCell(contentIn => ' - '
,colSpanIn => 5);
closeTableRow;
end if;*/
startTableRow;
printTableCell(contentIn => actionTypeIdList(i));
printTableCell(contentIn => actionTypeNameList(i));
printTableCell(contentIn => orderNumberList(i));
printTableCell(contentIn => VRList(i));
printTableCell(contentIn => COAOMList(i));
closeTableRow;
currentRuleType := ruleTypeList(i);
end loop;
closeTable;
--+
actIdList.delete;
actionTypeNameList.delete;
actionTypeDDList.delete;
--+
open actionTypeCursor;
fetch actionTypeCursor bulk collect
into
actionTypeIdList
,actionTypeNameList
,actionTypeDDList;
close actionTypeCursor;
for i in 1 .. actionTypeIdList.count loop
startTable2;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'Action Type ID :'|| actionTypeIdList(i));
printTableCell(contentIn => 'Action Type Description :'|| actionTypeNameList(i)
,colspanIn => 2);
closeTableRow;
--+
open reqAttributeCursor(actionTypeIdIn => actionTypeIdList(i));
fetch reqAttributeCursor bulk collect
into
attributeNameList
,attributeTypeList;
close reqAttributeCursor;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'Required Attributes '
,colspanIn => 3);
closeTableRow;
--+
for j in 1 .. attributeTypeList.count loop
startTableRow;
printTableCell(contentIn => attributeNameList(j)
,colspanIn => 2);
printTableCell(contentIn => attributeTypeList(j));
end loop;
--+
open actionCursor(actionTypeIdIn => actionTypeIdList(i));
fetch actionCursor bulk collect
into
actionIdList
,actionParam1List
,actionParam2List
,actionDescList;
close actionCursor;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'Action ID');
printTableCell(contentIn => 'Action Description');
printTableCell(contentIn => 'Parameter');
closeTableRow;
for j in 1 .. actionIdList.count loop
if ame_action_pkg.getActionTypeName(actionTypeIdList(i)) =ame_util.substitutionTypeName then
tempActionDescr := getActionDescription(actionIdIn =>actionIdList(j));
end if;
startTableRow;
printTableCell(contentIn => actionIdList(j));
if tempActionDescr is not null then
printTableCell(contentIn => tempActionDescr);
else
printTableCell(contentIn => actionDescList(j));
end if;
tempActionDescr :=null;
--printTableCell(contentIn => actionDescList(j));
printTableCell(contentIn => actionParam1List(j));
closeTableRow;
end loop;
--+
if actionIdList.count = 0 then
startTableRow;
printTableCell(contentIn => 'No Actions'
,colspanIn => 2);
closeTableRow;
end if;
--+
actionIdList.delete;
actionDescList.delete;
actionParam1List.delete;
actionParam2List.delete;
attributeNameList.delete;
attributeTypeList.delete;
closeTable;
end loop;
--+
--+
createLinktoTop;
--+
actionTypeIdList.delete;
actionTypeNameList.delete;
orderNumberList.delete;
VRList.delete;
COAOMList.delete;
end printActionTypes;
--+
--+
--+
function getGroupName(approvalGroupIdIn in number) return varchar2 is
groupName ame_util.stringType;
begin
select agr.name
into groupName
from ame_approval_groups agr
where agr.approval_group_id = approvalGroupIdIn
and sysdate between agr.start_date and nvl(agr.end_date - (1/86400),sysdate);
return groupName;
exception
when no_data_found then
return approvalGroupIdIn;
end getGroupName;
--+
--+
--+
--+
--+
--------------------------------------------------------------------------------------------
procedure printConditionDetails(applicationIdIn in number) is
cursor getConditionid is
select aco.condition_id
,aco.condition_key
from ame_attribute_usages atu
,ame_conditions aco
where atu.application_id = applicationIdIn
and aco.attribute_id = atu.attribute_id
and sysdate between atu.start_date and
nvl(atu.end_date-(1/86400),sysdate)
and sysdate between aco.start_date and
nvl(aco.end_date-(1/86400),sysdate)
order by condition_id;
condList ame_util.idList;
valuList ame_util.longestStringList;
stringCon varchar2(30000);
conddition_key ame_util.stringList;
begin
open getConditionid;
fetch getConditionid bulk collect into condList,conddition_key;
close getConditionid;
anchor('othercond');
startTable;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'AME conditions'
,colSpanIn => 4);
closeTableRow;
closeTable;
lineBreak;
startTable;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'Regular Conditions'
,colSpanIn => 4);
closeTableRow;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'Condition Key');
printTableCell(contentIn => 'Condition Id');
printTableCell(contentIn => 'Attribute Name');
printTableCell(contentIn => 'Description');
closeTableRow;
for i in 1 .. condList.count loop
if ame_condition_pkg.isStringAttributeType(conditionIdIn =>condList(i)) then
ame_condition_pkg.getStringValueList(conditionIdIn => condList(i),
stringValueListOut => valuList);
for j in 1..valuList.count loop
if stringCon is not null then
stringCon := stringCon || ','||valuList(j);
else
stringCon := valuList(j);
end if;
end loop;
startTableRow;
printTableCell(contentIn => conddition_key(i));
printTableCell(contentIn => condList(i));
printTableCell(contentIn => ame_condition_pkg.getAttributeName(condList(i)));
printTableCell(contentIn => stringCon);
closeTableRow;
else
startTableRow;
printTableCell(contentIn => conddition_key(i));
printTableCell(contentIn => condList(i));
printTableCell(contentIn => ame_condition_pkg.getAttributeName(condList(i)));
printTableCell(contentIn => ame_condition_pkg.getDescription(condList(i)));
closeTableRow;
end if;
end loop;
closeTable;
createLinktoTop;
end printConditionDetails;
procedure printLMConditions(applicationIdIn in number) is
cursor getLMconditions is
select condition_id
,parameter_one
,parameter_two
,condition_key
from ame_conditions
where condition_type ='post'
and sysdate between start_date and nvl(end_date-(1/86400),sysdate);
cursor getruleLMConditions is
select condition_id
from ame_condition_usages
where rule_id in (select rule_id
from ame_rule_usages
where item_id =applicationIdIn
and sysdate between start_date
and nvl(end_date-(1/86400),sysdate));
conditionIdList ame_util.idList;
param_oneList ame_util.stringList;
param_twoList ame_util.stringList;
lmConitiondKey ame_util.stringList;
conUsageList ame_util.idList;
condUsed boolean :=false;
inval_index integer;
begin
open getLMconditions;
fetch getLMconditions bulk collect into conditionIdList,param_oneList,param_twoList,lmConitiondKey;
close getLMconditions;
open getruleLMConditions;
fetch getruleLMConditions bulk collect into conUsageList;
close getruleLMConditions;
anchor('lcm');
startTable;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'List Modification conditions ');
closeTableRow;
startTable;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'Condition Key');
printTableCell(contentIn => 'ConditionId');
printTableCell(contentIn => 'Type');
printTableCell(contentIn => 'Approver');
printTableCell(contentIn => 'Used in current Transaction Type');
closeTableRow;
for i in 1..conditionIdList.count loop
condUsed := false;
for j in 1..conUsageList.count loop
if conditionIdList(i)= conUsageList(j) then
condUsed := true;
end if;
exit when condUsed = true;
end loop;
startTableRow;
printTableCell(contentIn => lmConitiondKey(i));
printTableCell(contentIn => conditionIdList(i));
printTableCell(contentIn => param_oneList(i));
if ame_approver_type_pkg.validateApprover(param_twoList(i)) then
printTableCell(contentIn => param_twoList(i));
elsif(condUsed) then
printTableCell(contentIn => 'Invalid:'||param_twoList(i));
inval_index := ame_invalappr.count;
ame_invalappr(inval_index+1).roleName :=param_twoList(i);
ame_invalappr(inval_index+1).source :='COND:'||conditionIdList(i);
else
printTableCell(contentIn => 'Invalid:'||param_twoList(i));
end if;
if condUsed then
printTableCell(contentIn => 'Y');
else
printTableCell(contentIn => 'N');
end if;
closeTableRow;
end loop;
closeTable;
end printLMConditions;
procedure printApprDetails is
cursor getApprDetails is
select apt.approver_type_id
,apt.orig_system
,fnd_lookups.description
from ame_approver_types apt
,fnd_lookups
where lookup_code = apt.orig_system
and lookup_type = 'FND_WF_ORIG_SYSTEMS'
and sysdate between apt.start_date
and nvl(apt.end_date-(1/86400),sysdate)
order by approver_type_id;
apprTypeId ame_util.idList;
origsystem ame_util.stringList;
apprDescr ame_util.stringList;
begin
open getApprDetails;
fetch getApprDetails bulk collect
into
apprTypeId
,origsystem
,apprDescr;
close getApprDetails;
--+
anchor('appr');
startTable;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'Approver Type Details'
,colSpanIn => 4);
closeTableRow;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'Approver Type Id');
printTableCell(contentIn => 'Orig System');
printTableCell(contentIn => 'Description');
closeTableRow;
for i in 1 .. apprTypeId.count loop
startTableRow;
printTableCell(contentIn => apprTypeId(i));
printTableCell(contentIn => origsystem(i));
printTableCell(contentIn => apprDescr(i));
closeTableRow;
end loop;
closeTable;
createLinktoTop;
end printApprDetails;
procedure printAMEInvalids is
cursor getInvalids is
select object_name
,object_type
from all_objects
where object_name like 'AME%'
and status = 'INVALID';
objectName ame_util.stringList;
objectType ame_util.stringList;
begin
open getInvalids;
fetch getInvalids bulk collect
into
objectName
,objectType;
close getInvalids;
anchor('inval');
startTable;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'AME Invalid object'
,colSpanIn => 6);
closeTableRow;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'Object Name');
printTableCell(contentIn => 'Object Type');
closeTableRow;
for i in 1 .. objectName.count loop
startTableRow;
printTableCell(contentIn => objectName(i));
printTableCell(contentIn => objectType(i));
closeTableRow;
end loop;
closeTable;
createLinktoTop;
end printAMEInvalids;
procedure printReleaseVersions is
cursor getAMEVersions is
select decode(bug_number
,'2614213','2614213'
,'2863619','2863619'
,'3858763','AME 11.5.10'
,'3962268','AME.A'
,'4433707','AME.A RUP'
,'4428060','AME.B'
,'4873179','AME.B RUP1'
,'5708576','AME.B.RUP2'
,'NONE') AME_LEVEL
,bug_number
,creation_date
from ad_bugs
where bug_number in ('2614213'
,'2863619'
,'3858763'
,'3962268'
,'4433707'
,'4428060'
,'4873179'
,'5708576')
order by bug_number;
cursor getFamilyPack is
select decode (bug_number
,'3127777','FP.I'
,'3333633','FP.J'
,'3500000','FP.K'
,'5055050','FP.K.RUP'
,'NONE') AME_LEVEL
,bug_number
,creation_date
from ad_bugs
where bug_number in (
'3127777'
,'3333633'
,'3500000'
,'5055050'
)
order by bug_number;
cursor getAppsRelease is
select decode (bug_number
,'2669606','Oracle Applications Release 11.5.9'
,'3140000','Oracle Applications Release 11.5.10'
,'3640000','11.5.10 CU1'
,'3480000','11.5.10 CU2'
,'NONE') AME_LEVEL
,bug_number
,creation_date
from ad_bugs
where bug_number in (
'2669606'
,'3140000'
,'3640000'
,'3480000'
)
order by bug_number;
PatchList ame_util.StringList;
ReleaseName ame_util.stringList;
dateList ame_util.dateList;
begin
open getAMEVersions;
fetch getAMEVersions bulk collect
into
ReleaseName
,PatchList
,dateList;
close getAMEVersions;
anchor('AMEver');
startTable;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'AME minipack Level'
,colSpanIn => 6);
closeTableRow;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'AME minipack');
printTableCell(contentIn => 'Patch Number');
printTableCell(contentIn => 'Patch Date');
closeTableRow;
for i in 1 .. PatchList.count loop
startTableRow;
printTableCell(contentIn => ReleaseName(i));
printTableCell(contentIn => PatchList(i));
printTableCell(contentIn => to_char(dateList(i),'dd/mm/yyyy:hh:mm:ss'));
closeTableRow;
end loop;
closeTable;
createLinktoTop;
ReleaseName.delete;
PatchList.delete;
dateList.delete;
open getFamilyPack;
fetch getFamilyPack bulk collect
into
ReleaseName
,PatchList
,dateList;
close getFamilyPack;
anchor('FPver');
startTable;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'HRMS Family Pack Level'
,colSpanIn => 6);
closeTableRow;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'Family Pack');
printTableCell(contentIn => 'Patch Number');
printTableCell(contentIn => 'Patch Date');
closeTableRow;
for i in 1 .. PatchList.count loop
startTableRow;
printTableCell(contentIn => ReleaseName(i));
printTableCell(contentIn => PatchList(i));
printTableCell(contentIn => to_char(dateList(i),'dd/mm/yyyy:hh:mm:ss'));
closeTableRow;
end loop;
closeTable;
createLinktoTop;
ReleaseName.delete;
PatchList.delete;
dateList.delete;
open getAppsRelease;
fetch getAppsRelease bulk collect
into
ReleaseName
,PatchList
,dateList;
close getAppsRelease;
anchor('APPVer');
startTable;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'Application release Level'
,colSpanIn => 6);
closeTableRow;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'Apps release');
printTableCell(contentIn => 'Patch Number');
printTableCell(contentIn => 'Patch Date');
closeTableRow;
for i in 1 .. PatchList.count loop
startTableRow;
printTableCell(contentIn => ReleaseName(i));
printTableCell(contentIn => PatchList(i));
printTableCell(contentIn => to_char(dateList(i),'dd/mm/yyyy:hh:mm:ss'));
closeTableRow;
end loop;
closeTable;
createLinktoTop;
end printReleaseVersions;
procedure splithdrLine(hdrLineIn in varchar2
,fileNameOut out varchar
,varsionOut out varchar2) is
pos1 integer;
pos2 integer;
pos3 integer;
result varchar2(50);
begin
pos2:= instrb(hdrLineIn,'pkb');
if pos2 = 0 then
pos2:= instrb(hdrLineIn,'pkh');
end if;
pos1 := instrb(hdrLineIn,':', 1);
result := trim(substr(hdrLineIn,pos1+2,pos2-1));
pos3 := instrb(result,' ');
fileNameOut := substr(result,1,pos3);
result := trim(substr(result,pos3+1,length(result)));
pos2 := instrb(result,' ');
if pos2 <> 0 then
varsionOut := substr(result,1,pos2);
else
varsionOut := result;
end if;
end splithdrLine;
procedure printKeyAMEFileVersion is
cursor getKeyFileVersion is
select name
,text
,type
from dba_source
where name in ('AME_ENGINE'
,'AME_UTIL'
,'AME_API'
,'AME_API2'
,'AME_TEST_UI'
,'AME_TEST_PKG'
,'AME_APPROVER_TYPE_PKG')
and line = 2
order by name;
pkgName ame_util.stringList;
obj_typeList ame_util.stringList;
VersionTxt ame_util.stringList;
file_name varchar2(50);
version varchar2(50);
begin
open getKeyFileVersion;
fetch getKeyFileVersion bulk collect
into
pkgName
,VersionTxt
,obj_typeList;
close getKeyFileVersion;
anchor('file');
startTable;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'Key AME File Version'
,colSpanIn => 6);
closeTableRow;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'Package Name');
printTableCell(contentIn => 'Package Type');
printTableCell(contentIn => 'File Name');
printTableCell(contentIn => 'Version');
closeTableRow;
for i in 1 .. pkgName.count loop
startTableRow;
splithdrLine(hdrLineIn => trim(VersionTxt(i))
,fileNameOut => file_name
,varsionOut => version);
printTableCell(contentIn => pkgName(i));
printTableCell(contentIn => obj_typeList(i));
printTableCell(contentIn =>trim(file_name) );
printTableCell(contentIn =>trim(version) );
closeTableRow;
end loop;
closeTable;
createLinktoTop;
end printKeyAMEFileVersion;
procedure printTransApprovalGroups(applicationIdIn in number) as
groupIdList ame_util.idList;
groupNameList ame_util.stringList;
groupIsStaticList ame_util.charList;
groupDescList ame_util.stringList;
groupQueryList ame_util.longestStringList;
groupItemIdList ame_util.idList;
groupParamTypeList ame_util.stringList;
groupParamsList ame_util.longStringList;
orderNumberList ame_util.idList;
ONList ame_util.idList;
VRList ame_util.stringList;
inval_index integer;
cursor approvalGroupCursor is
select agr.approval_group_id
,agr.name
,agr.query_string
,agr.description
,agr.is_static
from ame_approval_groups agr
where sysdate between agr.start_date and nvl(agr.end_date - (1/86400),sysdate)
and agr.approval_group_id in (select approval_group_id
from ame_approval_group_config
where sysdate between start_date and nvl(end_date-(1/86400),sysdate)
and application_id = applicationIdIn
);
cursor approvalGroupItemCursor(approvalGroupIdIn in number) is
select agi.approval_group_item_id
,decode(agi.parameter_name
,'OAM_group_id'
,'AME Group'
,'wf_roles_name'
,'WF Role'
,agi.parameter_name)
,agi.parameter
,agi.order_number
from ame_approval_group_items agi
where agi.approval_group_id = approvalGroupIdIn
and sysdate between agi.start_date and nvl(agi.end_date - (1/86400),sysdate);
cursor approvalGroupCfgCursor(applicationIdIn in number ) is
select agf.order_number
,decode(agf.voting_regime
,'S'
,'Serial'
,'C'
,'Consensus'
,'F'
,'First Responder Wins'
,'O'
,'Order Number'
,agf.voting_regime)
,agr.approval_group_id
,agr.name
from ame_approval_group_config agf
,ame_approval_groups agr
where agf.application_id = applicationIdIn
and agr.approval_group_id = agf.approval_group_id
and sysdate between agr.start_date and nvl(agr.end_date - (1/86400),sysdate)
and sysdate between agf.start_date and nvl(agf.end_date - (1/86400),sysdate)
order by agf.order_number;
begin
anchor('TransAgr');
startTable;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'Approval Groups');
closeTableRow;
closeTable;
open approvalGroupCfgCursor(applicationIdIn => applicationIdIn);
fetch approvalGroupCfgCursor bulk collect
into
ONList
,VRList
,groupIdList
,groupNameList;
close approvalGroupCfgCursor;
--+
startTable2;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => ' Approval Groups - Parallelization Details'
,colspanIn => 4);
closeTableRow;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'ID');
printTableCell(contentIn => 'Name');
printTableCell(contentIn => 'Order Number');
printTableCell(contentIn => 'Voting Regime');
closeTableRow;
for i in 1 .. groupIdList.count loop
startTableRow;
printTableCell(contentIn => groupIdList(i));
printTableCell(contentIn => groupNameList(i));
printTableCell(contentIn => ONList(i));
printTableCell(contentIn => VRList(i));
closeTableRow;
end loop;
closeTable;
groupIdList.delete;
groupNameList.delete;
groupQueryList.delete;
groupDescList.delete;
open approvalGroupCursor;
fetch approvalGroupCursor bulk collect
into
groupIdList
,groupNameList
,groupQueryList
,groupDescList
,groupIsStaticList;
close approvalGroupCursor;
for i in 1 .. groupIdList.count loop
--+
startTable2;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'Approval Group ID');
printTableCell(contentIn => groupIdList(i));
printTableCell(contentIn => 'Name');
printTableCell(contentIn => groupNameList(i));
closeTableRow;
--+
startTableRow;
printTableCell(contentIn => 'Description'
,colspanIn => 1);
printTableCell(contentIn => groupDescList(i)
,colspanIn => 3);
closeTableRow;
--+
startTableRow;
printTableCell(contentIn => 'Query String'
,colspanIn => 1);
printTableCell(contentIn => groupQueryList(i)
,colspanIn => 3);
closeTableRow;
--+
startTableRow;
printTableCell(contentIn => 'Static'
,colspanIn => 1);
printTableCell(contentIn => groupIsStaticList(i)
,colspanIn => 3);
closeTableRow;
--+
if groupIsStaticList(i) = 'Y' then
open approvalGroupItemCursor(approvalGroupIdIn => groupIdList(i));
fetch approvalGroupItemCursor bulk collect
into
groupItemIdList
,groupParamTypeList
,groupParamsList
,orderNumberList;
close approvalGroupItemCursor;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'Item ID');
printTableCell(contentIn => 'Order Number');
printTableCell(contentIn => 'Parameter Type');
printTableCell(contentIn => 'Parameter');
closeTableRow;
for i in 1 .. groupItemIdList.count loop
startTableRow;
printTableCell(contentIn => groupItemIdList(i));
printTableCell(contentIn => orderNumberList(i));
printTableCell(contentIn => groupParamTypeList(i));
if groupParamTypeList(i) = 'AME Group' then
printTableCell(contentIn => getGroupName(groupParamsList(i)));
else
begin
if ame_approver_type_pkg.validateApprover(groupParamsList(i)) then
printTableCell(contentIn => groupParamsList(i));
else
inval_index := ame_invalappr.count;
ame_invalappr(inval_index+1).roleName := groupParamsList(i);
ame_invalappr(inval_index+1).source := 'APG:'||groupIdList(i);
printTableCell(contentIn =>'Invalid:'||groupParamsList(i));
end if;
exception
when others then
null;
end;
end if;
closeTableRow;
end loop;
if groupItemIdList.count = 0 then
startTableRow;
printTableCell(contentIn => 'No items.'
,colSpanIn => 4);
closeTableRow;
end if;
--+
groupItemIdList.delete;
groupParamTypeList.delete;
orderNumberList.delete;
groupParamsList.delete;
orderNumberList.delete;
closeTable;
--+
else
closeTable;
end if;
end loop;
closeTable;
createLinktoTop;
--+
ONList.delete;
VRList.delete;
end printTransApprovalGroups;
--+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
function isexist(approverList in ame_util.stringList
,nameIn in varchar2) return boolean as
begin
for i in 1..approverList.count loop
if approverList(i)= nameIn then
return true;
end if;
end loop;
return false;
end isexist;
procedure approverSource(approverNameIn in varchar2
,objectOut out ame_util.stringList
,objectIdType out ame_util.stringList
,objectIdOut out ame_util.stringList
,descrOut out ame_util.stringList) is
appr_source varchar2(300);
before_colon varchar2(50);
after_colon varchar2(50);
total_count integer;
loc_count integer;
tempDesc varchar2(30);
descr varchar2(100);
begin
total_count := ame_invalappr.count;
for i in 1..total_count loop
if approverNameIn = ame_invalappr(i).roleName then
appr_source := ame_invalappr(i).source;
before_colon := substr(appr_source,1,instrb(appr_source,':')-1);
after_colon := substr(appr_source,instrb(appr_source,':')+1,length(appr_source));
loc_count := objectIdOut.count;
loc_count := loc_count+1;
if before_colon ='actionId' then
objectOut(loc_count) := ame_action_pkg.getActionTypeNameByActionId(to_number(after_colon))||' Action';
objectIdType(loc_count) := 'Action Id';
objectIdOut(loc_count) := after_colon;
descrOut(loc_count) := ame_action_pkg.getDescription(actionIdIn =>to_number(after_colon));
elsif before_colon ='APG' then
objectOut(loc_count) := 'Approval group';
objectIdType(loc_count) := 'Approval Group Id';
objectIdOut(loc_count) := after_colon;
descrOut(loc_count) := ame_approval_group_pkg.getName(approvalGroupIdIn => to_number(after_colon));
elsif before_colon ='ADM' then
objectOut(loc_count) := 'Configuration variable';
objectIdType(loc_count) := 'Configuration variable Name';
objectIdOut(loc_count) := 'Admin Approver';
descrOut(loc_count) := 'invalid : '||approverNameIn;
elsif before_colon ='COND' then
descr := trim(ame_condition_pkg.getConditionType(to_number(after_colon)));
if trim(descr) ='post' then
tempDesc := 'List Modification';
end if;
objectOut(loc_count) := 'Conditions:'||tempDesc;
objectIdType(loc_count) := 'Condition Id';
objectIdOut(loc_count) := after_colon;
descrOut(loc_count) := ame_condition_pkg.getDescription(conditionIdIn =>to_number(after_colon));
end if;
end if;
loc_count:=0;
end loop;
end approverSource;
procedure processInvalAppr is
wNameList ame_util.stringList;
objectList ame_util.stringList;
objectIdTypeList ame_util.stringList;
objectIdList ame_util.stringList;
descrList ame_util.stringList;
wNameListIndex number;
begin
for i in 1..ame_invalappr.count loop
if isexist(approverList => wNameList
,nameIn => ame_invalappr(i).roleName) <> true then
wNameListIndex := wNameList.count;
wNameList(wNameListIndex+1) := ame_invalappr(i).roleName;
end if;
end loop;
anchor('invAppr');
startTable;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'List of Invalid Approvers existing in current AME Setup');
closeTable;
if wNameList.count = 0 then
return;
end if;
for i in 1..wNameList.count loop
lineBreak;
startTable;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'WF_ROLES.NAME :'||wNameList(i));
closeTableRow;
closeTable;
objectList.delete;
objectIdTypeList.delete;
objectIdList.delete;
descrList.delete;
approverSource(approverNameIn => wNameList(i)
,objectOut => objectList
,objectIdType => objectIdTypeList
,objectIdOut => objectIdList
,descrOut => descrList);
startTable;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'Object');
printTableCell(contentIn => 'Object Key');
printTableCell(contentIn => 'Object Id');
printTableCell(contentIn => 'Object Description');
closeTableRow;
for j in 1..objectIdList.count loop
startTableRow;
printTableCell(contentIn => objectList(j));
printTableCell(contentIn => objectIdTypeList(j));
printTableCell(contentIn => objectIdList(j));
printTableCell(contentIn => descrList(j));
closeTableRow;
end loop;
closeTable;
end loop;
end processInvalAppr;
--+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--------------------------------------------------------------------------------------------
--+
procedure printConfigVariables(applicationIdIn in number) as
variableNameList ame_util.stringList;
variableValueList ame_util.longStringList;
inval_index integer;
cursor configVarCursor is
select cfg.variable_name
,cfg.variable_value
from ame_config_vars cfg
where cfg.application_id = applicationIdIn
and sysdate between cfg.start_date and nvl(cfg.end_date - (1/86400),sysdate);
cursor configVarCursor2 is
select cfg.variable_name
,cfg.variable_value
from ame_config_vars cfg
where cfg.application_id = 0
and sysdate between cfg.start_date and nvl(cfg.end_date - (1/86400),sysdate);
begin
open configVarCursor;
fetch configVarCursor bulk collect
into
variableNameList
,variableValueList;
close configVarCursor;
anchor('cfg');
startTable;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'Configuration Variables ');
closeTableRow;
closeTable;
--+
startTable2;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'Transaction Type Level'
,colspanIn => 3);
closeTableRow;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'Variable Name');
printTableCell(contentIn => 'Value'
,colspanIn => 2);
closeTableRow;
for i in 1 .. variableNameList.count loop
startTableRow;
if variableNameList(i) = 'adminApprover' then
if ame_approver_type_pkg.validateApprover(variableValueList(i)) then
printTableCell(contentIn => variableNameList(i));
printTableCell(contentIn => variableValueList(i)
,colspanIn => 2);
else
inval_index := ame_invalappr.count;
ame_invalappr(inval_index+1).roleName :=variableValueList(i);
ame_invalappr(inval_index+1).source :='ADM:CONFIG';
printTableCell(contentIn => variableNameList(i));
printTableCell(contentIn => 'Invalid:'||variableValueList(i)
,colspanIn => 2);
end if;
else
printTableCell(contentIn => variableNameList(i));
printTableCell(contentIn => variableValueList(i)
,colspanIn => 2);
end if;
closeTableRow;
end loop;
closeTable;
variableNameList.delete;
variableValueList.delete;
--+
--+
open configVarCursor2;
fetch configVarCursor2 bulk collect
into
variableNameList
,variableValueList;
close configVarCursor2;
--+
startTable2;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'Default'
,colspanIn => 3);
closeTableRow;
startTableRow(styleIn => 'OraTableRowHeader');
printTableCell(contentIn => 'Variable Name');
printTableCell(contentIn => 'Value'
,colspanIn => 2);
closeTableRow;
for i in 1 .. variableNameList.count loop
startTableRow;
if variableNameList(i) = 'adminApprover' then
if ame_approver_type_pkg.validateApprover(variableValueList(i)) then
printTableCell(contentIn => variableNameList(i));
printTableCell(contentIn => variableValueList(i)
,colspanIn => 2);
else
inval_index := ame_invalappr.count;
ame_invalappr(inval_index+1).roleName :=variableValueList(i);
ame_invalappr(inval_index+1).source :='ADM:CONFIG';
printTableCell(contentIn => variableNameList(i));
printTableCell(contentIn => 'Invalid:'||variableValueList(i)
,colspanIn => 2);
end if;
else
printTableCell(contentIn => variableNameList(i));
printTableCell(contentIn => variableValueList(i)
,colspanIn => 2);
end if;
closeTableRow;
end loop;
closeTable;
createLinktoTop;
end printConfigVariables;
--+
--+ main part of code
--+
BEGIN
printline ('<!--');
applicationId := to_number(&1);
printline ('-->');
startHtml;
anchor('top');
printTrasactionTypeInfo(applicationIdIn => applicationId
,transactionTypeFound => applicationFound);
if applicationFound then
createHtmlLinks;
printItemClassUsages(applicationIdIn => applicationId
,itemClassIdsOut => itemClassIdList
,itemClassNamesOut => itemClassNamesList);
printMandatoryAttributes(applicationIdIn => applicationId);
for i in 1 .. itemClassIdList.count loop
printAttributesByIC(applicationIdIn => applicationId
,itemClassIdIn => itemClassIdList(i)
,itemClassNameIn => itemClassNamesList(i));
end loop;
printConditionDetails(applicationIdIn => applicationId);
printLMConditions(applicationIdIn =>applicationId);
printRules(applicationIdIn => applicationId
,itemClassIdsIn => itemClassIdList
,itemClassNamesIn => itemClassNamesList);
printActionTypes (applicationIdIn => applicationId);
printTransApprovalGroups(applicationIdIn => applicationId);
printConfigVariables(applicationIdIn => applicationId);
printApprDetails;
processInvalAppr;
printAMEInvalids;
printReleaseVersions;
printKeyAMEFileVersion;
end if;
closeHtml;
exception
when no_data_found then
printLine ('no data found for the input.');
end;
/
spool off;
exit;
Comments
Post a Comment