Oracle E-Business Suite:Applying NLS Patch - Adding Languages To EBS in R12 & R11 - EBS'e Türkçe Dil Desteği Eklemek

After a fresh install of Oracle Production EBS R12.2.4 VM Template on our servers we wanted to add language support on our application system. Therefore we followed this document :"R11i / R12 : Requesting Translation Synchronization Patches (Doc ID 252422.1)"


In that document, it pretty much says everything about languages in an EBS system. You can either add a new language, or update the current language with respect to the other english file versions, just change the language files with the refreshed translations.

The whole process goes like:

1-We should check out the prerequisites under this topic "Before Requesting Translation Synchronization Patch" .  There some patches that you need to apply. according the patch level. You can check out  the patch level with the following SQL

 select * from fnd_product_installations where patch_level like '%AD%';

If you want to see that a patch is installed or not you can use the following patch: 

select * from AD_APPLIED_PATCHES where patch_name='%';  

2- Then  we activate the new language by using Oracle Applications Manager (OAM) Licence Manager.

We first click on Licence Manager.

Then we activate the language that we want.

3- After the activating the language, we go on with the adadmin utiliy the application node.

You run "adadmin" then go on with the "Maintain multi-lingual tables" which is under the the "Maintain Applications Database Entities Menu"

4- Then we request for a new syncronization patch by running the following command and uploading the output to the  following address.

Command:

 perl $AD_TOP/bin/adgennls.pl 

Output:

 $APPL_TOP/admin/$TWO_TASK/out/adgennls.txt 

Upload Address:

https://updates.oracle.com/TransSync

5- Then you receive  a translation syncrhonization patch to apply. To apply the for R12.2.4 

5.1- Change directory to the patch directory

5.2- Then execute the following command 

"adop phase=prepare,apply,finalize,cutover,cleanup"

6- Last step would be to generate message files from the adadmin utility.

Referances:


1-R11i / R12 : Requesting Translation Synchronization Patches (Doc ID 252422.1)

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=176620445661286&id=252422.1&_afrWindowMode=0&_adf.ctrl-state=303mg8osv_25


2-Oracle E-Business Suite NLS Release Notes, Release 12.2 (Doc ID 1314621.1)

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=176932629461056&parent=DOCUMENT&sourceId=252422.1&id=1314621.1&_afrWindowMode=0&_adf.ctrl-state=303mg8osv_70


3-How To Troubleshoot NLS Forms Generation Errors When Applying An NLS Patch In R12 Or 11i (Doc ID 1537973.1)

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=273461662123937&id=1537973.1&_afrWindowMode=0&_adf.ctrl-state=1803s998f3_4


4- Oracle E-Business Suite Installation Guide: Using Rapid Install - Part Number E22950-18 - Finishing Tasks - Installing NLS Language Support

https://docs.oracle.com/cd/E26401_01/doc.122/e22950/T422699i4783.htm

5-  Using Lightweight MLS With Oracle E-Business Suite Release 12.1.3 and higher (Doc ID 1077709.1)

Oracle E-Business Suite: Forms Compilation Error - Form Derleme Hatası

In R12.1.3

Recently we have encountered errors such as these:


Compiling KEY-NXTBLK trigger on QUERY_FIND data block...
Compilation error on KEY-NXTBLK trigger on QUERY_FIND data block:
PL/SQL ERROR 49 at line 1, column 1
bad bind variable 'parameter.G_query_find'
PL/SQL ERROR 49 at line 1, column 1
bad bind variable 'parameter.G_query_find'
PL/SQL ERROR 49 at line 3, column 1
bad bind variable 'parameter.G_query_find'

It turned out that FORMS60_PATH was not set correctly. Whenever forms are compiled FORMS60_PATH is set to:

FORMS60_PATH=$FORMS60_PATH:$AU_TOP/forms/US:$AU_TOP/resource

In context file it was set as:

$AU_TOP/resource:$AU_TOP/resource/stub

After correcting this and compiling manually it worked. To make this permanent change the s_f60path to include $AU_TOP/forms/US:$AU_TOP/resource

In R12.2.4 or in R12.2 we should be adding the following to the $FORMS_PATH variable in the context file which is in the $APPL_TOP/$SID_$HOSTNAME.env path  

$AU_TOP/forms/US:$AU_TOP/forms/TR 

After that forms can be compiled with the following command.

E.g.:

frmcmp_batch AKDFLOWB.fmb   apps/apps output_file=$AK_TOP/forms/TR/AKDFLOWB.fmx compile_all=special;


Referanslar:

Forms Compilation hatası
http://oracleappstechnology.blogspot.com.tr/2009/03/forms-compilation-errors.html

Oracle Fusion Middleware: Running RDA For Hyperion - Remote Diagnostic Agent(RDA) - Hyperion için RDA Çalıştırmak

We have written about the RDA before for OBIEE and EBS.

1- http://berkeoz1.blogspot.com.tr/2014/01/oracle-e-business-suite-rda-nedir.html
2- http://berkeoz1.blogspot.com.tr/2014/07/oracle-fusion-middleware-running-rda.html

Now we would like to specify the usage of rda for Hyperion. The need to that has arisen to detect problems in the Hyperion system. What we had used before was the diagnostic tool of Hyperion.
(http://berkeoz1.blogspot.com.tr/2014/08/oracle-hyperion-starting-and-stopping.html)
However we will be using rda as a complementary tool to our diagnostic efforts.

Where is RDA?

RDA is found in the Middleware Home. (For example:E:\Oracle\Middleware\oracle_common\rda)

We could do the setup which is giving the appropriate details for running rda by "rda.cmd -S".

To list all the profiles that we can run we could use:

rda.exe -L

With that command all the modules and previously defined profiles to analyze the system can be found.



An example of the command to run RDA with using just the HFM module is below:

rda.cmd -vSCRP HFM

An example of the command to run RDA with using profile for HFM is below:

rda.cmd -vSCRP -p Hyperion1112_HFM

In the end, we would have something like the directory structure below in which we run first the "RDA_index.htm" file

References:

1-How to Use Remote Diagnostic Agent (RDA) to Gather Diagnostic Information On Oracle Hyperion Financial Management (HFM) Servers to Help Resolve Service Requests Faster [Video] (Doc ID 1100612.1)

Oracle Fusion Middleware: Error Assigning Users To Application Role in OBIEE 11g - Kullanıcuların Uygulama Rolüne Atanırken Hata Alması

After installing OBIEE 11.1.1.7 on our system, we encountered with an error. This error was occuring although we had succesfully set our Active Directory. Our AD setup was working fine and we were logging in to system with the AD accounts.

Regarding the setup, you could check on how we have done it.
1-rovider - Active Directory Authentication to OBIEE 11g - Active Directory Configuration'ını OBIEE 11g İçin Yapmak

2-hentication to OBIEE 11g - 2 - Deleting Default Provider - Active Directory Configuration'ını OBIEE 11g İçin Yapm

The error that we have seen is

Java.lang.NullPointerException
#{viewScope.emas_pagemodel_security_EditAppRole.searchPrincipal}: java.lang.NullPointerException
     Hide Additional Trace Information

The error screen was like the picture below:

After that we have searched for the error, we have seen that there was a bug concerning this problem.

"Bug 16808088 : 11G JAVA.LANG.NULLPOINTEREXCEPTION ADDING USER TO ROLE AFTER UPGRADE TO 11.1.1.7"

The proposed solution is to set the virtualize parameter to false if it ise set. Virtualize parameter doesn't work with the username.attr which we have previously set in the security provider configuration settings.

In the end, we changed its value to false and we were able overcome this problem.

Reference:
1- https://community.oracle.com/thread/2530201?start=0&tstart=0

Oracle Fusion Middleware: EVALUATE_SUPPORT_LEVEL Error in Oracle OBIEE 11g

EVALUATE_SUPPORT_LEVEL errror appers when we open a report from a catalog or a dashboard. It has a quite simple solution.







In order to to solve this problem, we change evaluate support level parameter in the NQSConfig.ini file to the value given below:

EVALUATE_SUPPORT_LEVEL=2


After that, we could restart the BI server to activate the changes.

Oracle Fusion Middleware: Using Active Directory as a User Provider - Active Directory Authentication to OBIEE 11g - 2 - Deleting Default Provider - Active Directory Configuration'ını OBIEE 11g İçin Yapmak ve Default Provider'ı Kaldırmak

In our previous article, we explained how to add Active Directory as an user provider. Now we will delete the default authenticator in order to make the newly added Active Directory our only provider to have a tighter security configuration.

2.Deleting Default Provider

The reason why we put 2 at the beginning is that we are assuming that we have added the Active Directory just like we did on the arcticle before this.

2.1 Update the Credential Store Password

These configuration changes can be made from entreprise manager. The link should be like this: http://localhost:7001/em

Open Weblogic Domain / bifoundation_domain screen. Select Security / Credentials item under WebLogic Domain menu.

Find “system.user” under oracle.bi.system and click Edit.

Input the new user name and password. We have created OBIA_BISystemUser just for this purpose. We will use it later to add more privileges to our system user.

2.3. Security Provider Configuration

Click “WebLogic Domain” menu. Click “Security Provider Configuration” menu item under Security sub-menu.

We then add the following properties.

Property Name	Value
virtualize	false
user.login.attr	samaccountname
username.attr	samaccountname

2.4. Assign application roles to system users and groups

Select Business Intelligence / coreapplication from left panel. Click on Security / Application Roles item on Business Intelligence Instance menu.

Make the following assignments :

Role Name	Members
BISystem	OBIA_BISystemUser (User)
BIAdministrator	OBIA_Administrators (Group)
BIAuthor	OBIA_Administrators (Group)
BIConsumer	OBIA_Administrators (Group)

Note: The users and the groups are newly created. The creation process is explained on the first part of this arcticle.

2.5. Assign global roles to system users and groups:

Open Weblogic Admin Console. Go to “myrealm” in WebLogic Admin Console. Go to Roles and Policies tab. In “Realm Roles” sub tab, expand Global Roles / Roles.

Assign the roles to LDAP groups as seen in the following table.

Global Roles :	Assignment :
Admin	Group : OBIA_Administrators User : OBIA_BISystemUser
AdminChannelUsers	Group : OBIA_Administrators
AppTester	Group : OBIA_Administrators
CrossDomainConnector	Group : OBIA_Administrators
Deployer	Group : OBIA_Administrators
Monitor	Group : OBIA_Administrators
Operator	Group : OBIA_Administrators

2.6. Delete default provider:

Go to Providers tab under security realm “myrealm”. Click on Active Directory provider we created. Select REQUIRED in Control Flag dropdown field. Click Save.

2.7. Stop and start BI services:

Click “Stop BI Services” under “Oracle Business Intelligence” group in Start Menu. To proceed, we have to provide weblogic user and its password in the console.

Delete the two boot.properties files in the following locations :

%OBI_HOME%\user_projects\domains\bifoundation_domain\servers\AdminServer\security

%OBI_HOME%\user_projects\domains\bifoundation_domain\servers\bi_server1\security

Click “Start BI Services” under “Oracle Business Intelligence” group in Start Menu.

Enter the user and password of OBIA_Weblogic when prompted.

Oracle Fusion Middleware: Using Active Directory as a User Provider - Active Directory Authentication to OBIEE 11g - Active Directory Configuration'ını OBIEE 11g İçin Yapmak

In Oracle OBIEE 11g, we could use AD(Active Directory) as an LDAP source. Therefore we could use the users and their accounts which are defined in AD server. To do that, we have some configuration tasks to apply in Oracle Weblogic Server and Fusion Middleware Control which is  also known as Oracle .

1. Creating a New Provider

1.1. Create System Users & Groups in Active Director:First step is to create some of system users in AD. These are:

OBIA_Weblogic, OBIAOracleSystemUser, OBIA_BISystemUser

We also need to create 2 groups in AD. These are

OBIA_Administrators(Member:OBIA_Weblogic) and OBIAOracleSystemGroup(OBIAOracleSysUser)

The reason why we added prefixes like OBIA is to make them specific to our installation.

1.2 Backup of Configuration: 

The given config.xml file is where we %OBI_HOME%\user_projects\domains\bifoundation_domain\config\config.xml

In case we  make an error, we should have a backup of this file. If we ever have a problem with the opening of Oracle Weblogic, we could solve this problem by returning to the old configuration file.

1.3 Creation of Active Directory Provider:

Lets login to the Weblogic Admin Console: http://localhost:7001/console. Ports and links  may change according the installation you are making.

Click Security Realms on left panel. Click “myrealm” on Realms list. Go to Providers tab. Click Lock & Edit button on left panel. Click NEW. 

Specify the name for new authentication provider as you like.  Select “ActiveDirectoryAuthenticatior” from the TYPE drop-down list. Click OK. Click on the newly created Active Directory provider.

Select SUFFICIENT from Control Flag drop down list. Click SAVE. Then we fill the Provider specific properties. 

Connection / Host	Active Directory IP
Connection / Port	389
Connection / Principal	cn=obia_weblogic,cn=users,dc=gm,dc=,dc=com,dc=tr
Connection / Credential	Passw0rd
Connection / SSLEnabled	NOT SELECTED
Users / User Base DN	dc=gm,dc=,dc=com,dc=tr
Users / All Users Filter
Users / User From Name Filter	(&(samaccountname=%u)(objectclass=user))
Users / User Search Scope	subtree
Users / User Name Attribute	samaccountname
Users / User Object Class	user
Users / Use Retrieved User Name as Principal	SELECTED
Groups / Group Base DN	cn=users,dc=gm,dc=,dc=com,dc=tr
Groups / All Groups Filter
Groups / Group From Name Filter	(&(cn=%g)(objectclass=group))
Groups / Group Search Scope	subtree
Groups / Group Membership Searching	limited
Groups / Max Group Membership Search Level	1
Groups / Ignore Duplicate Membership	NOT SELECTED
Groups / Use Token Groups For Group Membership Lookup	NOT SELECTED
Static Groups / Static Group Name Attribute	cn
Static Groups / Static Group Object Class	group
Static Groups / Static Member DN Attribute	member
Static Groups / Static Group DNs from Member DN Filter	(&(member=%M)(objectclass=group))
Dynamic Groups / Dynamic Group Name Attribute
Dynamic Groups / Dynamic Group Object Class:
Dynamic Groups / Dynamic Member URL Attribute
Dynamic Groups / User Dynamic Group DN Attribute
General / Connection Pool Size	0
General / Connect Timeout	20
General / Connection Retry Limit	3
General / Parallel Connect Delay	0
General / Results Time Limit	0
General / Keep Alive Enabled	NOT SELECTED
General / Follow Referrals	SELECTED
General / Bind Anonymously On Referrals	NOT SELECTED
General / Propagate Cause For Login Exception	NOT SELECTED
General / Cache Enabled	SELECTED
General / Cache Size	32
General / Cache TTL	60
General / GUID Attribute	samaccountname

Click DefaultAuthenticator from the Provider list.

Select SUFFICIENT from Control Flag drop down list. Click SAVE.

Click Activate Changes button from Left Panel. Stop and start BI Services for these changes to take effect. 

1.4 Check If Integration Works

Login Weblogic admin console and go to Provider list. Click on Users and Groups tab.

See if users and groups from Active Directory are listed. 

1.5 Security Provider Configuration

Click “WebLogic Domain” menu in Enterprise Manager. Click “Security Provider Configuration” menu item under Security sub-menu.

Add the following property to the custom properties.

Property Name	Value
virtualize	true

Now we shoud be able to login to the environment with both our default authenticator and with the users from active directory users.

References:

1-http://www.peakindicators.com/media_pi/Knowledge/oracle%20bi%2011g%20-%20active%20directory%20authentication.pdf

2- TechNote: Configuring Oracle BI 11g and Weblogic for Single Sign-On using Kerberos-based Windows Authentication backed by Active Directory.
An Oracle White Paper
Updated October 2011

Oracle Hyperion: Starting and Stopping HFM 11.1.2.3

START:

EPM System Installer provides us a single start script under EPM_ORACLE_INSTANCE/bin which is called start.bat depending on the operating system.
To start the system, we start from foundation server, and run the start.bat under the "E:\Oracle\Middleware\user_projects\epmsystem1\bin". Then we pass on to the application server and run the start.bat which also under this or a similar path "E:\Oracle\Middleware\user_projects\epmsystem2\bin".

To check if we have succeeded on starting the application, we could run EPM System Diagnostics.

STOP:

Stoping procedure is quite similar to starting procedure. To stop we could start from the application server then stop the foundation server.


What is  EPM System Diagnostics?

Epm System Diagnostics is a tool that makes the essantial checks on a system and produces a report in which it shows its analysis. EPM System Diagnostics can be found on both of the servers(Applciation and Foundation). After the analysis, reports are created int the following path which may change according to the installation "E:\Oracle\Middleware\user_projects\epmsystem2\diagnostics\reports\"
Example:"E:\Oracle\Middleware\user_projects\epmsystem1\diagnostics\reports\instance_report_20140813_034231.html"

These reports can be used to for historical evalution. For example to estimate a time period where  we have last succeeded on running some module or when some module just crashed, we could always check these things.

System Diagnostics exe can be found in the  EPM_ORACLE_INSTANCE/bin

EXE's  and URL's 

Oracle Hyperion Shared Services Console: http://machine_name:Port/interop.  This is found on the Foundation Server. Example: http://machine_name:28080/interop

EPM Workspace: Http://machine:port/workspace. Workspace URL's can be also found on foundation services.
Example. http://machine_name:19000workspace

Oracle Hyperion: HFM Applications and Menu Item Are Not Displayed In Workspace - Error

Financial Management related menu items in Workspace can disappear from time to time if there happens to be any change in the configuration. The following ways of


1-File -> Preferences -> Consolidation
2-File -> Open -> Applications -> Consolidation
3-Navigate (the "wheel") -> Applications -> Consolidation
4-Navigate (the "wheel") -> Administer -> Consolidation Administration, plus the items "Users On Systems", "System Messages" and "Manage Servers and Applications".

When this happens, it is usually due to some kind of system configuration or parameter change.

How to solve this problem?

The way to solve this problem is running the Web Server Configuration Task from the Foundation Server.
This error was mostly seen in HFM 11.1.1.3 but it is also the correct way to solve it in HFM 11.1.2.3.

We first go to Foundation Server and run the configurator.

Then we configure all the financial management tasks.

After that we configure only "Configure Web Server" task.

Make sure that the HFM component is selected and shows the correct address and port; complete the task and Finish. Then in order , stop the Foundation Workspace Web Application service, stop and restart the Foundation Workspace Agent service. At last restart the Foundation Workspace Web Application service

References:

1-http://concentricsolutions.helpserve.com/Knowledgebase/Article/View/22/1/hfm-applications-and-menu-item-are-not-displayed-in-workspace

Oracle Hyperion: Logs in HFM 11.1.2.3

When we need Hyperion Logs, usually to send the information to SR or to find and search the error in the internet, we have to know where they are. So we will list the log file locations in case of an emergency.

1-E:\Oracle\Middleware\diag

Diagnostics directory under Middleware is where we can find the information needed when we run diagnostics tool.


2-E:\Oracle\Middleware\EPMSystem11R1\logs

What we have under this directory, is the logs belonging to application. For example, copy application log is found here. BIPlus logs are placed here. Whether we have done the right configuration or not, its logs are written under this path.

3-E:\Oracle\Middleware\logs

This path is more like the installation log file.

4-E:\Oracle\Middleware\user_projects\epmsystem2\diagnostics

Diagnostics paths under the domain home is the real location where the runtime logs are placed. We need to be aware of this directory's presence.


Note:Paths may change according to the directory installation.


How to view the system logs? 

There is actually two tools for this purpose given by Hyperion. In HFM 11.1.2.3, the application server of Hyperion, under
"E:\Oracle\Middleware\EPMSystem11R1\products\FinancialManagement\Consultant Utilities\" , our two are placed. One is the HFM Error Log Viewer to search for errors, second is to lookup for error which is seen on the HFM Error Log Viewer. Moreover, our research for solution becomes easier.

Oracle Hyperion: Copying HFM Application - Running HfmCopyApplication.exe - HfmCopyApplication Çalıştırmak

In Hyperion 11.1.2.3 CopyApplication.exe is found in the C:\Oracle\Middleware\EPMSystem11R1\products\FinancialManagement\Server . Under the "Server" directory you could find HfmCopyApplication.exe which is used to copy the applications from one HFM server to another or within the environment.

What do we need to run it?

We need 2 UDL's or just one according to the need. If we are using it to transport the application from one environment to another then we need 2.

These UDL's are used in order to show the database information. Because our database is on SQL server, we need to use SQL UDL's, one for source, one for destination.


Why is it used?

It is used mainly for synchronization between Production and Development environments. Therefore it is highly cruical for companies.

Other than migration, it can be used to:

1-Backup
2-Copy
3-Recover

Where is it found?

\EPMSystem11R1\products\FinancialManagement\Server 

Depending on how you install it, it can be found under D:\Hyperion\products\FinancialManagement\Server in HFM 11.1.1.3

Where are its logs?

D:\Hyperion\logs\hfm in HFM 11.1.1.3
E:\Oracle\Middleware\EPMSystem11R1\logs\hfm in HFM 11.1.2.3

The paths may change depending on the installation directories.

Note: If you used HfmCopyApplication to create a new HFM application (not to overwrite the content of an existing one) you need to register the application to Shared Services before being able to use it. This can be accomplished by using the Register Application function found in EPM Workspace.

References:

1-Copying HFM Applications: http://hyperionfinn.blogspot.com.tr/2012/02/copying-hfm-applications-on-command.html

2-EPM Utilities: http://hyperionnz.blogspot.com.tr/2012/06/epm-utilities-that-you-should-know.html

3-http://oraclehyperionstuff.blogspot.com.tr/2012/02/how-to-backup-hfm-application-for.html

Oracle Fusion Middleware: Changing The Default Values Of The Reports in OBIEE 11g - Raporların Sabit Değerlerinin Değiştirilmesi

We would now like to change the default values of  the reports that we export. The reason why we are changing these values is that we either want to modify default number of rowthat can be downloaded to the reports or to modify the number of row that is processed. There are some other parameters too that can be changed according to the need but the ones that we are focusing on are the ones explained above.

So in order to change the configuration, we lock and edit the configuration, then we set out to change the values. Proposed values can be seen in the picture. Then we activate the changes and restart the BI_Server components. In conclusion we would have our values changes, therefore our configuration is changed, and the way our reports produced would be more suitable for us.

Oracle Fusion Middleware: Increasing Numbers Of Row To Display/Export to Excel From Analysis in OBIEE 11g - Excel'e Export Alınırken Görüntülenen Satır Sayısının Arttırılması

When we try to export one report that we had in OBIEE, we encountered with a problem which said that we were exceeding the maximum limit of row numbers that could be seen in an Excel Report. Problem looked like the picture below:

The error code was EY692ZW9 (Maximum total number of cells exceeded. (Limit 50000)).

Therefore we looked for the ways to solve this problem. To overcome this difficulty we had to change some parameters in our instanceconfig.xml which was found in the following path:
C:\oracle\OracleBusinessIntelligence\instances\instance1\config\OracleBIPresentationServicesComponent\coreapplication_obips1\

After we have reached our destination file, we then changed the tab with the following lines.

 

  
     

75

25000

false
5000
2500
500000
5000
1920000



75

25000
2500
500000
5000
1920000




Briefly, we added the following parameters to both the tab and the pivot .

2500
500000
5000
1920000

In the end we able to extract all the rows we need in our report which needed 28000 lines instead of just 8000.

References:
1-http://oraclebisolutions.blogspot.com.tr/2013/01/increase-number-of-rows-to.html
2-https://community.oracle.com/thread/2341945?tstart=0
3-https://community.oracle.com/thread/2391197?tstart=0
4-http://shivabizint.wordpress.com/2011/08/26/export-more-than-6500-obiee-11g-records-to-excel/
5-https://community.oracle.com/thread/2473815?tstart=0
6-http://ruchi-obiee.blogspot.in/2012/05/to-limit-number-of-rows-in-pivot-table_07.html
7-http://docs.oracle.com/cd/E25178_01/bi.1111/e10541/answersconfigset.htm#CIHJDHGI

Oracle E-Business Suite: Cancelling or Closing Workflow Notification - Workflow Kapatma veya İptal Etme

There is a PL\SQL Script that can cancel or close workflow notifications through its API.   The script below has 2 parameters (recepient_role and close_type). The workflows can be closed or cansel with the script below according to its close type value.

 declare   
  -- declare Cursor   
  cursor get_notif is select * from wf_notifications where recipient_role='&UserName' and status='OPEN';  
  -- Local variables here  
  i integer      number(10):=1;  
  notification_type  varchar2(10):= '&nType';  
 begin  
  dbms_output.put_line('Executing Procedure');  
  -- open cursor to check open notifications which needs to be closed  
  for c_get_notif in get_notif  
   loop  
   begin  
   
   -- Check notificaiton type (close or cancel)  
   -- CLOSE - This will close information notifications only, all action required notification will not be close  
   -- CANCEL - This will cancel notification, action required notification such as Approve/Reject will be cancelled and cannot be rollback, users will not be able to take actiion on such notification later  
   if upper(notification_type)='CLOSE' then  
    wf_notification.Close(c_get_notif.notification_id, c_get_notif.recipient_role);  
    i:=i+1;  
   elsif upper(notification_type)='CANCEL' then  
    wf_notification.cancel(c_get_notif.notification_id, c_get_notif.recipient_role);  
    i:=i+1;  
   end if;  
     
   exception  
    when others then  
    null;  
   end;  
   end loop;  
     
   commit;  
   dbms_output.put_line(to_char(i)||' records effected');  
   dbms_output.put_line('Procedure Executed Successfully');  
 end;

Why This Is Important?

It is because if these workflows are not cleaned, they can cause some serious queue. That is to say it will prevent  the other mails from going out or in.

Reference:

1-http://maqappsfusion.blogspot.com.tr/2013/05/close-or-cancel-workflow-notification.html

Oracle Fusion Middleware: Oracle Business Intelligence 11g Start/Stop Services And Servers

In Oracle Business Intelligence 11g starting and stoping servers can be done from the shortcuts. The following image shows us from where we can set the shortcut.

Below is the output of how they look on the desktop.

After the installation of OBIEE, these should be set in order to have a more compact system. The executables that are called are located in the Oracle Instance Home(Example: C:\oracle\obiee\instance\instance1\bifoundation_domain\OracleBIApplication )


Starting and Stopping Servers One by One:

Sometimes it takes too long to stop and start all the services when all we have to do is to stop just one service. When this is the case we could always run cmds and then start and stop the servers one by one.
That is to say if there is a specific problem, we will be to act according to it.

Three Steps: There are 3 servers that we must start and stop if we had only installed Oracle BI. If we have Oracle BI Apps installed, then there must be an ODI server that should be deployed.

1-Starting Weblogic Server: To do that we first send a cmd file shortcut to our desktop.

Then we specifiy the location for which command line should start from, in the start in section just like below, by right clicking to command line and from there clicking on the properties
.

For weblogic server we could write in "Start in" topic :C:\oracle\obiee\user_projects\domains\bifoundation_domain\bin. Which shows that it is in the Domain home. After setting this and clicking Ok, we should be able to go directly into the directory we want.  Then we enter "startWebLogic.cmd"

2-Starting Managed Server(BI_Server1):

For BI Server, the configuration steps are exactly the same as the above. Bu for command wise, we enter startManagedWebLogic.cmd bi_server1

 3- Starting OPMN:

To start opmn, we will set the instance home for the directory to start in which is in this case C:\oracle\obiee}instances\instance1\bin\ . Then to start the OPMN we issue the "opmctl.bat startall"

Service Startup Order of the OBIEE 11g:

1- Weblogic
2- BI_Server
3- OPMN

Service Stop Order of the OBIEE 11g:

1- OPMN
2- BI_Server

3- Weblogic

Oracle Fusion Middleware: How to find Oracle Weblogic Server Version? - Oracle Weblogic Server Versiyonu Nasıl Bulınur?

To find out  the WebLogic Server Version, we may go to  registry.xml in MIDDLEWARE_HOME
We open registry.xml and search for “component name=”WebLogic Server”” version=”10.3.5.0″ (variable version next to this will tell you weblogic version.

For example ours was 10.3.5.0.

Oracle E-Business Suite: Concurrent Requests Performance History - Concurrent Request'lerle ilgili İstatistik Performans Bilgisi

Query About Performance Statistics of Concurrent Requests:

This query gives us the count of  daily run concurrents. In addition to that their daily running duration, average running hours and pending hours are also shown. That's how we could diagnose if there had been wrong with the system. In case there was a problem, we could easily detect and investigate the problem on that day or period.

 SELECT TO_CHAR(TRUNC(ACTUAL_START_DATE),'DD-MON-YY DY') STARTDATE,  
 COUNT(*) COUNT, ROUND(SUM(ACTUAL_COMPLETION_DATE - ACTUAL_START_DATE) * 24, 2) RUNNING_HOURS,  
 ROUND(AVG(ACTUAL_COMPLETION_DATE - ACTUAL_START_DATE) * 24, 2) AVG_RUNNING_HOURS,  
 ROUND(SUM(ACTUAL_START_DATE - REQUESTED_START_DATE) * 24, 2) PENDING_HOURS,  
 ROUND(AVG(ACTUAL_START_DATE - REQUESTED_START_DATE) * 24, 2) AVG_PENDING_HOURS  
 FROM APPLSYS.FND_CONCURRENT_PROGRAMS P,APPLSYS.FND_CONCURRENT_REQUESTS R  
 WHERE R.PROGRAM_APPLICATION_ID = P.APPLICATION_ID  
 AND R.CONCURRENT_PROGRAM_ID = P.CONCURRENT_PROGRAM_ID  
 AND R.STATUS_CODE IN ('C','G')  
 AND TRUNC(ACTUAL_COMPLETION_DATE) > TRUNC(SYSDATE-6)  
 AND TO_CHAR(TRUNC(ACTUAL_START_DATE),'DD-MON-YY DY') IS NOT NULL  
 GROUP BY TRUNC(ACTUAL_START_DATE)   
 ORDER BY TRUNC(ACTUAL_START_DATE) ASC;

Reference:

1-http://dbavandana.blogspot.com.tr/2013/06/concurrent-manager-queries.html

Oracle E-Business Suite: Concurrent Programs Under Queue - Sırada Bekleyen İşlemler, Concurrentlar

Concurrent Programs Under Queue:

The query given below can show us the queries that are on hold, waiting to be executed. There could be a condition on the number of concurrents waiting and in the end if this number is too much, a mail can be sent to the administrator to check if something is wrong.

select user_CONCURRENT_PROGRAM_NAME "PROGRAM NAME",concurrent_queue_name "QUEUE NAME", priority,decode(phase_code,'P','Pending') "PHASE",   
 decode(status_code,'A','Waiting','B','Resuming','C','Normal','D','Cancelled','E','Error','F',  
 'Scheduled','G','Warning','H','On Hold','I','Normal','M','No Manager','Q','Standby','R','Normal','S',  
 'Suspended','T','Terminating','U','Disabled','W','Paused','X','Terminated','Z','Waiting') "   
 NAME", status_code,count(*) from   
 fnd_concurrent_worker_requests   
 where phase_code='P' and hold_flag!='Y'   
 and requested_start_date<=sysdate  
 and concurrent_queue_name<> 'FNDCRM'  
 and concurrent_queue_name<> 'GEMSPS'  
 group by   
 user_CONCURRENT_PROGRAM_NAME,  
 concurrent_queue_name,priority,phase_code,status_code  
 order by count(*) desc;  

Referans:

1- http://dbavandana.blogspot.com.tr/2013/06/concurrent-manager-queries.html