April 8th, 2011 Matt
Just a quick post in response to a question on the OTN forums today – if I have a role, how do I know whether it’s valid or not?
The quickest way to do it (I think!) would be just to check the end date – if the end date is set, and we’re past that point in time, then the role is invalid. BUT – what if there is more to it than that? What if Oracle modifies the product so that the end date field isn’t the only thing that needs to be tested?
The way that I recommend doing the check (and I’ve recently written some code around this very subject!) is to use the standard WF_DIRECTORY API to fetch me the information about the role. If the role is invalid, then there won’t be any data returned; if the rolw is valid, then you get data back.
And here’s the code:
DECLARE
l_display_name WF_ROLES.display_name%TYPE;
l_email_address WF_ROLES.email_address%TYPE;
l_notification_pref WF_ROLES.notification_preference%TYPE;
l_language WF_ROLES.language%TYPE;
l_territory WF_ROLES.territory%TYPE;
l_module VARCHAR2(100);
l_role WF_ROLES.name%TYPE := '&role';
BEGIN
WF_DIRECTORY.getRoleInfo ( role => l_role
, display_name => l_display_name
, email_address => l_email_address
, notification_preference => l_notification_pref
, language => l_language
, territory => l_territory );
IF l_display_name IS NULL THEN
dbms_output.put_line('Role is invalid');
ELSE
dbms_output.put_line('Role is valid');
END IF;
END;
/
It can be downloaded here.
Posted in Oracle, Technical | No Comments »
February 7th, 2011 Matt
It should be a fairly simple task – count the number of rows in a table. I recently had the “pleasure” of discussing this with a colleague who was adamant that the best way to count the number of rows in a table was to use something explicit, e.g. COUNT(1) or “even better” COUNT(ROWID).
The basis for his thinking seemed to be OCP Introduction to Oracle 9i: SQL Exam Guide which apparently says something like
Do not use count(*) to determine the number of rows in a table. Use count(1) or count(ROWID) instead. These options are faster because they bypass some unnecessary operations in Oracle’s SQL pocessing mechanism.
Now, I’ve been told both things in the past – I was always taught to use COUNT(*), but some people that I’ve worked with have always used COUNT(0) or similar. So, as with all these kinds of questions, I turned to Tom Kyte for more information…
Looking at a number of questions and answers on the AskTom site, this seems to have been done to death, so I’ll just post a link to what appears to be a definitive couple of answers: here and here.
As ever, there are a number of neat points made in the threads, but for those that don’t want to read through them all, here’s my concise summary:
- COUNT(1) is optimised to COUNT(*) in the database.
- COUNT(*) is meaningful (it shows we are counting everything), whereas COUNT(rowid) means nothing.
- COUNT(*) is the right way to go.
- This remains true for any version of the database greater than 8.
- This remains true regardless of the column types in the table
- Matt was right.
Posted in Oracle, Technical | No Comments »
February 1st, 2011 Matt
Within Oracle Email Center, there is the facility to associate an incoming email to either a new or existing service request. When you are manually creating the association between the two, the standard Email Center screens (via Email Center Agent Console) allow the user to create new notes against the service request. By default, however, the list of available notes types probably doesn’t match the ones that you really want the agents to use.
I was working recently with a client on an upgrade from 11.5.9 to Release 12, which also throws up an anomaly – there are some notes types defined in an AOL lookup where the application ID is set to CRM Foundation. This means that the list of notes types available now includes some which you cannot query in any other form in the eBusiness Suite.
So, firstly, we need to make those obsolete, incorrect notes types visible to the lookup screen, so that at least we are aware that there are additional notes types that are in the system – if we can’t find them, we can’t exclude them!
The way that this needs to be done is documented in Metalink note 813677.1. The note instructs you to delete the notes from the system, but I am always somewhat wary of doing this, in case at some stage they were available and have been used. Before doing anything with the values, I would recommend that they are backed up into a custom table, just in case we need to revert them at any stage:
CREATE TABLE xx_obsolete_lookups_backup AS
SELECT *
FROM FND_LOOKUP_VALUES
WHERE lookup_type = 'JTF_NOTE_TYPE'
AND view_application_id != 0
/
Instead of removing them completely, run the following SQL which makes them visible but are clearly obsolete:
UPDATE FND_LOOKUP_VALUES
SET view_application_id = 0
, meaning = 'OBSOLETE - '||meaning
WHERE lookup_type = 'JTF_NOTE_TYPE'
AND view_application_id != 0
/
Now, if you navigate to the AOL lookups for the JTF_NOTE_TYPE lookup, the list is now at least complete. Ideally, you would then just be able to end-date or disable the lookups, but those fields are protected against update. So, the next step is to create an exclusion list of notes types that we aren’t interested in.
Using the CRM Administrator responsibility, navigate to Task and Escalation Manager > Setup > Object Meta-data and create a new Object Type which will store the list of notes that we aren’t interested in.
For example, I use a name “Unwanted Notes Bin” and code “XX_UNWANTED_NOTES”. Set the Application to “CRM Foundation” and (this is the important bit), on the Usages tab, ensure that there is a record for object user is “NOTES”, as per the following example:
At this point, depending on how many notes types you want / need to exclude, we hit the really dull part of the configuration – map the notes you want to exclude to the new object type. Again using the CRM Administrator responsibility, navigate to Notes Setup > Source and Note Type Mapping. Create new records for each note type you wish to exclude – the Source Object should be the name of the object type you have just created, and then include the note type you wish to exclude. The screenshot below shows my example exclusion list:
If you are doing an upgrade and had to run the SQL at the start of this post, ensure that you add the notes which begin OBSOLETE to the list as well.
Once you have completed the list and saved it, when you next try to add a note to a service request from an email, the list of note types should be what you want to see. The two screens below show how the list now changes from this:
to this:
The list of note types available is now governed in two ways. Firstly, all note types which exist in the custom object type we created here are now excluded. Secondly, all note types which have been mapped to the Service Request object type will be enabled. If there are conflicts between the two of these, then the second takes priority over the first.
Posted in Functional, Oracle | No Comments »
January 24th, 2011 Matt
When trying to grant access to HR Extra Information Types to different responsibilities, one of the limitations in HR is that the only responsibilities which appear in the standard form are ones which are related to HR. For example, querying “Service” in the Information Type Security form (PERWSITS) returns no rows because HR can’t see that responsibility:
So, if you are working on a system where you need to grant access to HR information types to responsibilities which are not within the HR area (e.g. CRM-based responsibilities), then there is no obvious way to do it. In this kind of situation, there may well tend to be over-use of responsibilities since an HR and CRM responsibility is now needed to provide the access.
The way to reduce this duplication is to add the seeded HR security menu to the seeded Service menu as follows. Using the System Administrator responsibility, navigate to Application > Menu and query the menu CSX_CUSTOMER_SUPPORT. Add a new entry for the submenu UK HRMS SECURITY:
Once you have saved the new menu, navigate to the Service responsibility and you will have access to the form to enable you to map access to the information types:
Now when you open the form, you can easily map the required access. The screenshot below shows adding different information types to the seeded Service responsibility:
This little tip comes courtesy of Davyd Parry.
Posted in Functional, Oracle | No Comments »
December 16th, 2010 Matt
Back in September, I wrote a post which had some SQL to provide a list of Workflow timeout values which can be used in profile option values.
The code sample below was something that Gary Derrick put together which does a similar task, albeit more simplified than the version I produced in the original post:
SQL="SELECT a.meaning,
a.v
INTO :visible_option_value,
:profile_option_value
FROM
(SELECT DECODE(LEVEL - 1,0,'No Timeout'
, DECODE(LEAST(LEVEL - 1,59),LEVEL - 1,TO_CHAR(LEVEL - 1)
||' Minute'
||DECODE(LEVEL - 1,1,'','s')
, DECODE(LEAST(LEVEL - 1,1439),LEVEL - 1,TO_CHAR((LEVEL - 1)/60)
||' Hour'
||DECODE(LEVEL-1,60,'','s')
, TO_CHAR((LEVEL-1)/1440)
||' Day'
||DECODE(LEVEL - 1,1440,'','s')))) meaning
, LEVEL - 1 v
FROM dual
CONNECT BY LEVEL <= 7200
) a
WHERE a.v IN (0, 1,2, 60, 120, 1440, 2880)"
COLUMN="Meaning(15)"
HEADING="Timeout(15)"
The code generates a list of values based on the number of minutes you specify in the WHERE clause. In this example, the list of values would be no timeout (0), 1 Minute(1), 2 Minutes (2), 1 Hour (60), 2 Hours (120), 1 Day (1440), 2 Days (2880) – if you want different values, then change the number of minutes to include in the query.
The value of the profile option can then be retrieved directly by the Workflow process without any need to manipulate it – it’s ready to use in the code, but is also human-friendly in terms of what the LOV displays.
Hope you find it useful – thanks Gary!
Posted in Functional, Oracle, Technical | No Comments »
October 4th, 2010 Matt
Another code snippet from my archive.
On a number of projects, we have implemented approvals for service requests based on the HR supervisor hierarchy, and occasionally during testing the approval notification ends up in an unexpected place. If you are using AME, then you can run the live test based on the service request ID to find out who AME expects to notify, but if you are using an SR workflow then this becomes a little more complex.
The code sample below takes the SR number as the start point, and from the primary contact walks up the HR supervisor hierarchy until there are no more supervisors found.
Enjoy!
SET SERVEROUTPUT ON SIZE 1000000
DEFINE sr_number = &1
DECLARE
--
CURSOR c_per_info ( cp_party_id IN NUMBER ) IS
SELECT full_name
FROM apps.per_people_x
WHERE person_id = cp_party_id;
--
CURSOR c_get_manager ( cp_party_id IN NUMBER ) IS
SELECT paaf.supervisor_id
FROM apps.per_all_assignments_f paaf
JOIN apps.per_assignment_status_types past ON
( paaf.assignment_status_type_id = past.assignment_status_type_id )
WHERE paaf.person_id = cp_party_id
AND paaf.effective_start_date <= SYSDATE
AND NVL(paaf.effective_end_date, SYSDATE) >= SYSDATE
AND paaf.primary_flag = 'Y';
--
CURSOR c_party_from_sr_number ( cp_sr_number IN NUMBER ) IS
SELECT party_id
FROM apps.cs_hz_sr_contact_points
WHERE incident_id = ( SELECT incident_id
FROM apps.cs_incidents_all_b
WHERE incident_number = cp_sr_number )
AND primary_flag = 'Y';
--
v_start PLS_INTEGER;
v_curr_person per_people_x.full_name%TYPE;
v_next_person per_people_x.full_name%TYPE;
v_check BOOLEAN;
--
BEGIN
--
v_check := TRUE;
--
OPEN c_party_from_sr_number ( cp_sr_number => &sr_number );
FETCH c_party_from_sr_number INTO v_start;
CLOSE c_party_from_sr_number;
--
IF v_start IS NULL THEN
DBMS_OUTPUT.PUT_LINE('No SR found for SR number &sr_number');
v_check := FALSE;
END IF;
--
IF v_check THEN
WHILE v_start IS NOT NULL LOOP
OPEN c_per_info ( cp_party_id => v_start );
FETCH c_per_info INTO v_curr_person;
CLOSE c_per_info;
--
OPEN c_get_manager ( cp_party_id => v_start );
FETCH c_get_manager INTO v_start;
CLOSE c_get_manager;
--
OPEN c_per_info ( cp_party_id => v_start );
FETCH c_per_info INTO v_next_person;
CLOSE c_per_info;
--
IF v_start IS NULL THEN
DBMS_OUTPUT.PUT_LINE ( 'Current person '
||v_curr_person
||' has no supervisor' );
ELSE
DBMS_OUTPUT.PUT_LINE('Current person '
||v_curr_person
||' is supervised by '
||v_start
||' / '||v_next_person );
END IF;
END LOOP;
END IF;
END;
/
Posted in Functional, Oracle, Technical | 1 Comment »
September 28th, 2010 Matt
I’m currently migrating my code archives from one hard drive to my new QNAP network attached storage device, and found a whole load of useful SQL scripts that have been sitting around waiting for me to do something with them. Amongst them was this one….
Many Workflow processes require a timeout which is dynamic, and the way that I typically recommend my clients to do this is to define a profile option to store the value, and then reference this in the code. Unfortunately, quite a few Oracle environments that I have seen do this but provide no validation on the profile, and no clear indication of what the value means – setting the profile to 1 might mean one second, one hour, one day…
So, here’s some quick example code of SQL validation that can be put on a profile option to validate against a list of values of timeouts in days, up to 10 days:
SQL="SELECT decode ( timeout, 0, 'No timeout', meaning ) meaning
, timeout
into :visible_option_value, :profile_option_value
FROM ( SELECT TO_CHAR(level - 1)||' days' meaning
, level - 1 timeout
FROM dual
CONNECT BY LEVEL <= 11 )"
COLUMN="Meaning(15)"
HEADING=""Timeout Days"(15)"
And here’s a longer version, which provides a list of timeouts in hours from no timeout to 10 days:
SQL="SELECT DECODE ( timeout_period, 0,'No timeout',meaning) meaning
, timeout_period
into :visible_option_value, :profile_option_value
FROM ( SELECT CASE
WHEN (level-1) < 24 THEN
CASE WHEN (level-1) = 1 THEN '1 hour'
ELSE
TO_CHAR(level-1)||' hours'
END
ELSE
CASE WHEN MOD((level-1),24) = 0 THEN
CASE WHEN level = 25 THEN '1 day'
ELSE
TO_CHAR(TRUNC((level-1)/24))||' days'
END
ELSE
CASE WHEN TRUNC((level-1)/24) = 1 THEN
CASE WHEN MOD((level-1),24) = 1
THEN '1 day and 1 hour'
ELSE
'1 day and '||MOD((level-1),24)||' hours'
END
ELSE
CASE WHEN MOD((level-1),24) = 1
THEN TO_CHAR(TRUNC((level-1)/24))
||' days and 1 hour'
ELSE
TO_CHAR(TRUNC((level-1)/24))
||' days and '
||MOD((level-1),24)||' hours'
END
END
END
END meaning
, (level-1) / 24 timeout_period
FROM dual
CONNECT BY LEVEL <= 241 )"
COLUMN="Meaning(15)"
HEADING=""Timeout Period"(15)"
Enjoy! The SQL file for the above examples can be found here.
Posted in Functional, Oracle, Technical | 1 Comment »
September 26th, 2010 Matt
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 account is now removed from Email Center.
Posted in Functional, Oracle, Technical | No Comments »
March 3rd, 2010 Matt
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.
Posted in Oracle, Technical | 4 Comments »
February 22nd, 2010 Matt
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:
Posted in Functional, Oracle | 1 Comment »
