I recently had to remove some email accounts from Oracle Email Center, because the environment had been cloned from one instance to another and referenced obsolete accounts. An easy task, one might think – if they have never worked with EMC before… Oracle produce some guidance on how to clear the accounts out, but I’ve never had a complete list of instructions before, so thought I’d document it here. I’m sure that there are ways to remove the accounts purely functionally, but if there is, I’ve not found it – the instructions here include a mix of functional changes and direct table updates, so as ever you use it at your own risk.
Step 1 – Ensure that there are no Agents assigned to the email account
Using the Email Center Administrator responsibility, navigate to the agents association page (path Email Center Administration > Administration > Email Accounts > Associations > Agents). Choose the “Assign Agents to Accounts” drop-down, then choose the email account that you want to remove and click the Go button. Remove the agents from the account(s) if you can.
If there are emails in the agent’s inbox, you will need to log in as that person, navigate to their in box and remove the emails that are sitting there. Even if you do this, though, you may well find that you still can’t remove the agent from the account – so we’ll do a direct table update to ensure that there aren’t any emails there!
Identify the email account ID that you are looking for by running the following SQL:
SELECT email_account_id
FROM iem_mstemail_accounts
Where email_address = '<enter mailbox address here>'
/
Then identify the agent by running:
SELECT agent_id
FROM iem_agents
WHERE email_account_id = <email_ account id from above SQL>
/
Before deleting the records, I would recommend creating a backup table at this stage, e.g.
CREATE TABLE xx_iem_agents_backup AS SELECT * FROM iem_agents;
Then force the removal of the agent from the account by running the following SQL:
DELETE FROM iem_agents
WHERE email_account_id = <email account id>
AND agent_id = <the associated agent id>;
COMMIT;
So, whether you have reached this point from functional configuration or deleting the records in the Email Center tables, you should now have no agents associated with the account that you want to delete.
Step 2 – Remove the rules from the accounts
This is the one step where you should be able to do this functionally. Navigate to the rules association page (Email Center Administration > Administration > Email Accounts > Associations > Processing Rules), and choose the account that you are deleting. Make sure that there are no rules associated with the account – if there are, then expand the entry and remove them by clicking on the bin icon.
Step 3 – Empty the folders
Check whether Email Center is looking at the folders for that account – navigate to Email Center Administration >Monitoring > Download Processor and see whether there are any emails that the system is looking at.
If there are any messages on the server, then you should delete them if they are no longer needed – to do this, I open the account in my email client and remove the messages from the inbox, Oracle Processed and Oracle Retry folders. Once they are empty, the Download Processor should show that there are no messages there.
Step 4 – Make the account inactive and delete it
Theoretically, this is the last step and should be straightforward. To do this functionally, navigate to Email Center Administration > Administration > Email Accounts > Summary and find the account that you are trying to delete. Edit the configuration and change it from Active to Inactive, then return to the summary screen. If all is well, then the bin icon will be enabled and you can now delete the account. If not, then we are back into the world of SQL updates to remove the account information…
Firstly, check whether there are emails being processed for the mailbox by running the following SQL:
SELECT email_account_id
, message_id
FROM iem_rt_proc_emails
WHERE email_account_id = ( SELECT email_account_id
FROM iem_mstemail_accounts
WHERE email_address = '<enter mailbox address here>' );
If there are no rows returned, then identify the email account ID by running the subquery above:
SELECT email_account_id
FROM iem_mstemail_accounts
WHERE email_address = '<enter mailbox address here>';
Make a note of the account ID before moving onto the next section.
If there were any records returned, then delete them from the table (again, I recommend making a backup of the data first!):
CREATE TABLE xx_iem_rt_proc_emails_backup AS SELECT * FROM iem_rt_proc_emails;
DELETE FROM iem_rt_proc_emails
WHERE email_account_id = <id you made a note of> ;
COMMIT;
Next, check for any messages that are in the pre-processing stage:
SELECT email_account_id
, message_id
FROM iem_rt_preproc_emails
WHERE email_account_id = <id you made a note of> ;
If no rows are returned do nothing, but if one or more rows are returned, then do the following:
CREATE TABLE xx_iem_rt_preproc_emails_backup AS SELECT * FROM iem_rt_preproc_emails;
DELETE FROM iem_rt_preproc_emails
WHERE email_account_id = <id you made a note of> ;
COMMIT;
That should be all that you need to do, and the accounts should now be able to be deleted. Navigate to Email Center Administration > Administration > Email Accounts > Summary and check whether the bin icon for the email account is now enabled. If it is, then delete the account from the front-end. If not, then there is one final “force” that you can do from the back end:
UPDATE iem_mstemail_accounts
SET deleted_flag = 'Y'
WHERE deleted_flag = 'N'
AND email_account_id = <id you made a note of> ;
The hard drive crashed last week and had to be binned, so I replaced it with an old one I had kicking about (which may not last too long, either!). I downloaded Linux Mint 9 from the official site and installed it from the DVD.
And the sound worked fine with no need to do anything else!
Top work from the Mint guys – saved me from hacking about again to get sound working
Since the Foehn & Hirsch laptop was a bit of a fiasco, I decided to re-use an old laptop of mine that I’ve had kicking around for a while. I bought an Acer TravelMate 3000 about four years ago now, because it was the best spec that I could find. I can’t remember the spec off the top of my head, but I remember the hard drive being over 100GB (huge in those days) and I’ve since doubled the memory to 2GB to make it last a bit longer. It’s been running Windows XP Professional since I got it, and that’s been fine, but as I said in the original post about the F&H laptop, my wife was so taken with my Linux Mint laptop, so I decided to rebuild it from scratch.
Firstly, I downloaded the 32-bit version of Linux Mint 8 from their website, as the version I used to build this laptop was the 64-bit and the old laptop doesn’t support 64-bit architecture. I burned the image to disc using Brasero, stuck it in the external DVD drive that came with the laptop, and booted it up.
As I’ve found on every system that I’ve built with Linux (Mint or Ubuntu) apart from the Foehn & Hirsch one, there were no problems – or certainly no obvious ones. The screen resolution was fine, the wireless network connected immediately (including the light on the front of the laptop working correctly), everything I checked was fine – so I reformatted the hard drive and replaced XP with Mint.
I then went off to work (I was away for a couple of days), and the girls told me that there was no sound working – the children want to watch childrens programs on iPlayer, play on CBeebies and watch YouTube and the like. I thought that it would be something really obvious like the speaker just being muted, but there was just nothing working. So, when I got home, I thought “this will be straight forward and take a couple of seconds…”
After about half an hour, I managed to track down the problem – the modem seemed to be conflicting with the soundcard (I think). Here’s how I found the source of the problem and resolved it.
Quick update – they found that the laptop was faulty when it arrived back and have refunded the cost of the laptop. I have raised a new customer support note because eBuyer have kept hold of the £9.99 shipping costs for sending the faulty unit out to me, which I don’t believe they should have done.
So far, it has cost £9.99 not to get a working laptop from eBuyer, so the complaint goes on.
Last week, my house was broken into and one of our laptops was stolen. Not too much of a big deal, since there wasn’t much data on there (thank goodness for Cloud computing!), and once the insurance assessor visited, I went out and bought a new machine. Since this was for my wife, I left her using my Toshiba Portege M800 which runs Linux Mint 8 x64, just to see what she thought of it and whether she could move from Vista to Linux.
After a few days of it, she was mightily impressed – there are some nice features of Mint that she liked over Vista, so asked if the replacement could be one that ran Linux. No problems – might be a bit cheaper, after all! – so I hunted down a Foehn & Hirsch laptop on eBuyer.com which was cheap, and came with no OS. Here’s the detailed spec:
Processor
Intel T3000 Celeron Dual Core Processor 1.8Ghz,
1MB Cache
SIS M672 + 968 Graphics Chipset
Memory
4GB DDR2 667MHz
2 x SO DIMM
Expandable to 4GB
Hard Drive
320GB SATA 2.5″
Optical Drive
DVD+/-RW Supermulti
Software
Operating System: No Operating System
Display
15.6” HD TFT
Resolution: 1366 x 768
Graphics
SiS M672 Integrated
Shared Memory Architecture 256MB
High Preference 3D/2D graphic Accelerator
Microsoft DirectX 9.0 Compatible
Super Vertex Shader 2.0 and Pixel Shader 2.0
Audio
High Definition Audio
Compliant With Microsoft UAA
Direct Sound 3D
2 x Built in Speaker
Built in Mic
Input Devices
Multi Language Keyboard
Touchpad with Scroll zone
Networking
56K Fax Modem
Built in 10/100MB Base-TX Ethernet LAN
WLAN: 802.11 b/g
Power Supply
Full Range AC Adapter 65W
AC in 100~240V, 50~60Hz
DC Out 19V, 3.42A / 18.5V, 3.5A
6 Cell Lithium Ion Battery
up to 2.5 hour battery life
Dimensions
Width 374mm
Depth 256mm
Height 25~37mm
Weight 2.7kg
Interfaces
3 x USB 2.0 ports
1 x VGA port
1 x Headphone port
1 x Microphone port
1 x S/PDIF output port
1 x RJ-11 port
1 x RJ45 LAN port
1 x DC-in jack
Expansion
7 in 1 Card Reader (MMC / RSMMC / SD / Mini SD / MS / MS Pro / MS Duo)
1 x Express Card Slot
Warranty / Miscellaneous
1 Year Manufacturer Warranty
1.3mp Webcam
That should be fine, I thought – keep her in laptops for a while, good spec and should be fine for Linux. There was nothing anywhere that I could find that said otherwise, apart from a line that said “Please note that this Laptops does not have an Operating System installed, Ebuyer recommends Genuine Microsoft Software”
I’ve just been asked a question which seems to fit the classic Tom Kyte “my car won’t start” scenario – a few pieces of information (which are of relevance to the asker, who has been looking at it for a while) but there is nothing there that provides any information about what the problem actually is.
Here’s a video from ZD Net Australia – it’s an interview with Tom about how developers should ask for help:
I recently had to install SQL Developer on my Linux box at home to communicate with my R12.1.1 instance. I could have used the version that I have installed in my W2K virtual machine (I need some kind of Windows installed to use Workflow Builder, so have a VM running it), but sometimes I just need to access the database without wanting or needing to have a VM running which uses 1GB of RAM. In this post, I will detail the steps that I took to get it working.
Firstly, download the right version from Oracle here. I downloaded the file “sqldeveloper-2.1.1.64.39-no-jre.zip”
Once the program has downloaded, unzip the file into a temporary location. I unzipped mine into /home/Downloads where it created a new subdirectory called “sqldeveloper” for me.
Move the sqldeveloper folder into /opt (you may need root privileges to do this, so either do this as root or use sudo to move the folder).
Navigate to /opt/sqldeveloper and change the privileges on the sqldeveloper.sh file using chmod:
sudo chmod 755 sqldeveloper.sh
Create a new file called “sqldeveloper” in /usr/local/bin/sqldeveloper which contains the following line:
/opt/sqldeveloper/sqldeveloper.sh
Change the permissions on this new file using chmod again:
sudo chmod 755 /usr/local/bin/sqldeveloper
Finally, you can run the program just by typing “sqldeveloper” at the command prompt window. Run this now from a terminal window, and you will be prompted to confirm the location of the JDK:
Oracle SQL Developer
Copyright (c) 1997, 2009, Oracle and/or its affiliates.All rights reserved.
Type the full pathname of a J2SE installation (or Ctrl-C to quit), the path
will be stored in ~/.sqldeveloper/jdk
In my instance, the JVM is located at /usr/lib/jvm/java-6-sun so enter that path and SQL Developer should start. Because the path to ~/.sqldeveloper does not exist yet (it will be created the first time the program runs), you will see errors. Close SQL Developer down and run the command again – you will be prompted to enter the path a second time, which will then be saved to ~/.sqldeveloper/jdk. If you ever change the location of the JVM, you will need to modify this file or SQL Developer will fail.
The last thing that I did was to create a new shortcut in my Ubuntu menu – if you are doing this, then the icon file can be found at /opt/sqldeveloper/icon.png. Now when I click on the icon, SQL Developer starts fine:
Update 01-FEB-2011 @ 0822: Just a quick update to say that although I’ve not tested this on many Linux distros, I’ve moved away from Ubuntu to Linux Mint with no problems.
One of the most recent search terms that I’ve seen for my blog here was “AME dynamic approval group example” – so in this post, I’m going to go through the steps required to create a dynamic approval group and provide an example. This example continues on from part four, since we will be replacing the static approval group with a simple dynamic one.
Using the “Approvals Management Business Analyst” responsibility, in the “Approval Process Setup” panel on the right hand side of the dashboard, choose the “iRecruitment Vacancy Approval” transaction type again:
Click on the “Approver Groups” link, and we will modify the existing group from static to dynamic. Click on the “Update” icon for the Approver Group we created earlier, and then change the usage type from Static to Dynamic. As soon as the change is made, you will be shown a warning, indicating that the static approvers will be removed from the group:
Since we are going to be redefining the group completely, this is fine, so click on the “Yes” button and you will be returned to the group definition with no static approvers shown.
As a simple example, my approver group requires approval from two people – the user MATT and the HR person Tim Cleary. The SQL code required to retrieve the two records in the same query is
SELECT 'user_id:'||user_id
FROM fnd_user
WHERE user_name = 'MATT'
UNION ALL
SELECT 'person_id:'||person_id
FROM per_all_people_f papf
WHERE papf.full_name = 'Cleary, Mr. Timothy'
In the SQL above, we need to tell AME exactly what kind of approver we are retrieving – by prefixing the user record with “user_id:” and prefixing the person record with “person_id:”, AME then knows where the records are being retrieved from. Enter the SQL into the Query box, and then hit the Apply button. You can also use the Validate button, which checks that the query is structurally sound, but does not seem to check much more than that (for example, if you prefix the user record with “usssssser_id:”, it is still classed as a valid query).
That’s all there is to it – if we now go and create a new vacancy (using the same BJOSPEH user that we did in the last post), when we review the vacancy, we see that AME has added the two approvers to the bottom of the approval list:
In the last blog post, I detailed the functional configuration required to setup a new user with AME administration privileges. Now that the user is ready, in this post I will be going through a quick example of how AME can be used within iRecruitment.
In this example, we will implement the following approval rules for creating a new job vacancy within iRecruitment:
When a vacancy is created, the vacancy must be approved by the supervisor of the person creating the vacancy.
Once the supervisor has approved the vacancy, it must then be sent to a different user for final approval.
In my example, I have picked two random users within Vision – BJOSEPH (Joseph, Brian) and his supervisor, RJAMES (James, Robert).
The first rule (that the vacancy must be approved by the supervisor) is standard behaviour within iRecruitment, so there is nothing that we need to do with that. So – what do we need to do to meet the second requirement?
Firstly, we’ll create a new static AME Approval Group and add the Recruiting Administrator to that group. An Approval Group can return either a static or a dynamic list of users. Once we have created the Approval Group, we will then add the Group to the AME Rule that is used by the standard Vacancy Creation process.
Before we start, run through the standard Vacancy Creation process and see who the notification is sent to. Log in as the first user to create the vacancy – in my example, I will use the BJOSEPH user. Using the “iRecruitment Recruiter – VC” responsibility, navigate to the iRecruitment Home page, and click on the “New Vacancy” link in the sidebar:
In the “New Vacancy” window, enter a name for the vacancy and then click on the “Review” button. At this stage, we could enter more information about the vacancy, but since this is just a quick test to see the default functionality, there is nothing additional required here. The bottom of the vacancy summary shows the default approver that has been found – the supervisor of the person creating the vacancy:
As I said above – the default functionality meets our first requirement, so all we need to do is to add the additional requirements. Firstly, we need to find out what AME Transaction Type is used by the Vacancy Creation process. To do this, we need to examine the function for “Irc Vacancy Details – Create”, as shown in the screenshot below:
Navigate to the “Form” tab and open the Parameters to see the parameters which are passed to the form:
The parameter that we are interested in is “pAMETranType=IRCVACAPPROVAL”. Now that we know the name of the internal name for the transaction type (or Transaction Type Key), switch responsibility to “Approvals Management Administrator” – I will switch user completely to the AMEADMIN user we created in the last post. Navigate to the Admin Dashboard and query the IRCVACAPPROVAL Transaction Type Key:
Now that we know the name of the Transaction Type (iRecruitment Vacancy Approval), switch responsibility to “Approvals Management Business Analyst” and enter the Transaction Type in the right hand side in Step 1 of the “Approvals Setup Process”:
If you click on the “Rules” link, you can view the existing approval rules in place. Click on the link and then locate the “iRecruitment Create Vacancy” rule in the list of rules. When you open the rule, you are presented with the default rule:
Return to the dashboard, and we’ll create our custom Approval Group to add to the Rule. Under “Step 2” of the Process Setup, there is a link to “Approver Groups”. Click on the link to navigate to setup a new Approver Group, and then click on the “Create” button. The screenshot below shows the values that I have entered for my Approver Group:
The “Approver Type” can be an Applications User (FND User), a group of people (Nested Group) or an HR person (HR People). Multiple users can be added to the approval hierarchy here – I have only added one person, a user called MATT who will act as the final approver for vacancies. Click on the “Apply” button and then return to the dashboard to add this new Approval Group to the rule.
As before, click on the “Rules” link on the right hand side of the screen and locate the rule for iRecruitment Create Vacancy. Click on the update icon to open the rule details. At the bottom of the page, you will see the default action type, which seeks approval from the supervisor of the person creating the vacancy. Click on the Add Action button to add our Approval Group to the rule, as shown below:
If the “approval-group chain of authority” option is not available, this is because it has not been enabled. Click on the “Setup” tab and choose the “Action Types” submenu. You will be presented with a list of suitable action types – typically only the default “Supervisory level” one will be enabled. Click on the “Use Existing Action Type” button and navigate through the list of available types until you see the “approval-group chain of authority” type. Tick the box next to the action type and then click on the Continue button, then click on the Finish button. Now return to the rule and add the Approval Group as above.
Once you have added the action type, click on the Apply button. That’s all there is to it – now let’s check it again by logging out and creating a new vacancy. The final screenshot below shows that the new vacancy will be sent to MATT for approval as well as the originators supervisor:
By default, the “Approvals Management Administrator” and “Approvals Management Business Analyst” responsibilities do not have any functions associated with them, because their access is restricted by Role Based Access Control (RBAC). In this section, I will detail the steps needed to add functions so that we can get into using AME.
Firstly, we’ll create a new user for this demonstration, and assign them the two responsibilities, thus:
If I now log into the eBusiness Suite as that user and try to switch to either of those responsibilities, the system indicates that there are no functions available for the user (see screenshot below). As I said above, this is because role based access control ensures that only users with the right roles as well as responsibilities can access the functions.
So, now we need to modify the user to give them the right roles to administer AME properly. As a system administrator, open the “User Management” responsibility, as shown below.
If you do not have any functions assigned to the responsibility, then you will need to use the SYSADMIN user and assign the “Security Administrator” role to your user. Select the “Users” option to continue.
Search for the user that you want to add the functions to (in my case, the AMEADMIN user I created earlier), open the user details and then click on the “Assign Roles” button. You are then presented with a search window, where you should search for all Roles beginning “Approval”:
You are then returned to the user screen, where you should complete a justification for granting the roles to the user – if you don’t then the screen will prevent you from continuing. Once you have entered the justification, you should then hit the Apply button.
Log out as this user, and when you log in as the AME user, you now have functions associated with the two responsibilities:
Now that we have granted the roles to the user, they can access the functions correctly. However, RBAC means that when we open the Dashboard, the data is still protected and cannot be viewed:
Again, we need to grant some RBAC roles to allow the user to access the different transaction types. The main principle behind this is to enable organizations to split the Transaction Type maintenance between different roles – for example to allow HR Super Users to maintain only the HR rules.
Using a different user, navigate to the Functional Administrator responsibility and click on the “Create Grant” button to create the new grant. In this example, we will create a specific grant for the AMEADMIN user, however in the real world it is more likely that you will create the grant for a group of users. The Object is set to “AME Transaction Types”, thus:
In the second step, accept the default data context type of “All Rows” and continue to the third step. Define the “Set” to be “AME Calling Applications” as shown in the following screenshot:
Finally, review the settings that you have entered and click on the “Finish” button. In order to ensure that the changes are replicated, now flush the cache by clicking on the “Core Services” tab then choose the “Caching Framework” sub-menu. Choose “Global Configuration” from the sidebar and then click on the “Clear All Cache” button. You will be presented with a warning message (see below) which you can ignore and click on the “Yes” button.
Log out of this user and log back in as the AMEADMIN user, and navigate to one of the AME responsibilities – you are now given a list of the AME Transaction Types that are configured in the system:
In the next blog post, I’ll be providing a practical example of customizing AME within iRecruitment.
