October 27th, 2008 Matt
This is a quick follow up to a comment posted on the original thread – I had a discussion with some people about the post, and was asked the following (hence the new post): “Can we use wma.name = ‘RESULT’ instead of wma.subtype=’RESPOND’ as each message that requires a RESPONSE action will have RESULT attribute?”. Here’s my response:
Not every message that requires a response will have a RESULT attribute, though. The RESULT attribute only applies to notifications which have conditional branching based on a result applied to them.
If you have no result type, but have respond attributes, then the notification still requires a response but does not have a result.
Have a look at this test flow for an example. Launching the flow and then selecting from the WF_NOTIFICATION_ATTRIBUTES table returns one row for the attribute IA1 – there is no RESULT attribute since the notification does not have a result.
Here’s the output:
**** Notifications
NID Context Status
----- --------------- --------
40899 NEW_TYP4:1:1692 OPEN
SELECT notification_id, NAME
FROM wf_notification_attributes
WHERE notification_id = 40899
/
NOTIFICATION_ID NAME
--------------- ----
40899 IA1
So, although the notification requires a response, there is no RESULT attribute, and it’s certainly not uncommon to have notifications and messages defined in this way – if the requirement is to capture a response, but not to branch conditionally depending on the result, this is the way to build the Workflow.
Matt
Posted in Oracle, Technical | No Comments »
October 24th, 2008 Matt
A thread on Metalink some time back asked how to tell whether a notification requires a response or not, and I’ve just found some notes that I took on the subject at the time. [For those without Metalink access, the exact question is "How do I know from the backend what notifications require action and what notifications and just FYI"].
The first thing I thought about the question was “what does it actually ask?” There are two possible things that the person posing the question might be after. Either they want to know what MESSAGES are defined in the system, and do they require a response or not; or they are looking at NOTIFICATIONS that have been sent to users to see whether they require a response. Accordingly, there are two different answers, since the source of the data is completely different.
Let’s take the first possible questions – what messages are there in the system, and do they require a response of not? The key thing in either of the situations is whether the notification has any attributes which require a response or not – in which case the table that needs to be accessed is WF_MESSAGE_ATTRIBUTES which stores all of this information. In order to make the information more understandable, I would also extend the query to look at the WF_ITEM_TYPES_TL and WF_MESSAGES_TL tables, which provide the display name for each of the internal names (message and item type), which makes it much easier to understand which message and item type you are dealing with.
Firstly, retrieve all the messages from the system which have a message attribute which is does not have a type of RESPOND, but which also do not have any RESPOND attributes. This takes care of any messages which have multiple attributes, and at least one of them is a response attribute. Secondly, we need to include any of the messages which have RESPOND attributes, and union the result sets together. I have written the two SELECT statements as a sub-query in my example, so that it is then much easier to sort by whatever order you require. Here’s the code:
SELECT *
FROM ( SELECT DISTINCT 'FYI' action
, wfi.display_name item_type
, wfma.message_type item_type_internal
, wfmt.display_name message_name
, wfma.message_name message_internal
FROM wf_message_attributes wfma
, wf_item_types_tl wfi
, wf_messages_tl wfmt
WHERE wfma.SUBTYPE != 'RESPOND'
AND wfma.message_type = wfi.name
AND wfma.message_type = wfmt.type
AND wfma.message_name = wfmt.name
AND (wfma.message_type, wfma.message_name) NOT IN ( SELECT DISTINCT wfma2.message_type
, wfma2.message_name
FROM wf_message_attributes wfma2
WHERE wfma2.SUBTYPE = 'RESPOND'
)
UNION
SELECT DISTINCT 'Response Required'
, wfi.display_name item_type
, wfma.message_type item_type_internal
, wfmt.display_name message_name
, wfma.message_name message_internal
FROM wf_message_attributes wfma
, wf_item_types_tl wfi
, wf_messages_tl wfmt
WHERE wfma.SUBTYPE = 'RESPOND'
AND wfma.message_type = wfi.name
AND wfma.message_type = wfmt.type
AND wfma.message_name = wfmt.name
) messages
ORDER BY item_type_internal, message_internal
The second question requires us to expand the query a bit further – we now need to include the notifications that are open in the system, so include WF_NOTIFICATIONS in the query as well. Apart from the join between this table and the others, the only other change that I have made to the query is to include the notification_id in the query as well. Here’s the code for the second query:
SELECT *
FROM ( SELECT DISTINCT 'FYI' action
, wfi.display_name item_type
, wfn.message_type item_type_internal
, wfmt.display_name message_name
, wfn.message_name message_internal
, wfn.notification_id
FROM wf_notifications wfn
, wf_message_attributes wfma
, wf_item_types_tl wfi
, wf_messages_tl wfmt
WHERE wfn.message_type = wfma.message_type
AND wfn.message_name = wfma.message_name
AND wfma.message_type = wfi.name
AND wfma.SUBTYPE != 'RESPOND'
AND wfma.message_type = wfmt.type
AND wfma.message_name = wfmt.name
AND (wfn.message_type, wfn.message_name) NOT IN ( SELECT DISTINCT wfn2.message_type
, wfn2.message_name
FROM wf_notifications wfn2
, wf_message_attributes wfma2
WHERE wfn2.message_type = wfma2.message_type
AND wfn2.message_name = wfma2.message_name
AND wfma2.SUBTYPE = 'RESPOND' )
UNION
SELECT DISTINCT 'Response Required'
, wfi.display_name item_type
, wfn.message_type item_type_internal
, wfmt.display_name message_name
, wfn.message_name message_internal
, wfn.notification_id
FROM wf_notifications wfn
, wf_message_attributes wfma
, wf_item_types_tl wfi
, wf_messages_tl wfmt
WHERE wfn.message_type = wfma.message_type
AND wfn.message_name = wfma.message_name
AND wfma.message_type = wfi.name
AND wfma.SUBTYPE = 'RESPOND'
AND wfma.message_type = wfmt.type
AND wfma.message_name = wfmt.name
) notifications
ORDER BY item_type_internal
Hopefully that all answers the question – how do I know what notifications require a response and which do not? It also answers the unwitting question – how do I know what messages require a response and which do not?
As ever, comments more than welcome!
Posted in Oracle, Technical | No Comments »
October 17th, 2008 Matt
Recently, there was a question asked on Metalink about how to update a large number of user preferences in one easy step. The original question was to see how to turn off the email preference for users, in an environment that had been cloned from production.
There are a couple of ways that you could do this, but my preferred way is through the WF_DIRECTORY APIs – I’m currently working with TCA a lot, and so the importance of using the supplied APIs is really being hammered home!
I’ve attached a script which loops through the records in the WF_USERS table, and updates the notification preference to ‘QUERY’ for all the records it finds – if you want to change different preference settings, then you just plug them into the script. Although the API is designed for use with ad-hoc roles, there is no reason why this does not work with standard roles. I tested it this morning on eBS release 11.5.10.2 on a 10g database running on Linux, and it worked fine for me.
Enjoy!
SQL script
Posted in Oracle, Technical | No Comments »
October 11th, 2008 Matt
I’ve just hit (for the nth time) the limit in DBMS_OUTPUT.PUT_LINE that you can only have 1000000 characters in the output. I know that that sounds a lot, but if you’re debugging complex workflows (with lots written out to the screen), and processing multiple flows in the same transaction, then this can easily be blown. I know that in 10g, you can have an unlimited DBMS_OUTPUT buffer, but not everyone is using that version, or even using SQL*Plus.
So I “googled” (yes, it’s a verb!) the error that I was getting thrown, and (as ever) found this thread on AskTom.
Tom has provided a new PL/SQL package which replicates pretty much what DBMS_OUTPUT does by storing it in a PL/SQL table, and then has a view on top of it to retrieve (and delete) the data from the table. You can then just select from the view to get the output back.
I’ve copied the code into the attached SQL file, just for ease:
DBMS_OUTPUT alternative
Enjoy!
Matt
Posted in Oracle, Technical | No Comments »
October 1st, 2008 Matt
I’ve just been asked to come up with a quick script for our DBAs to let them know who has been using the system recently. They want something that they can use to pick up the recent eBS users and their email addresses, so that they can let them know when the system is about to be bounced or shut down.
So, here’s the SQL:
SELECT DISTINCT ppf.full_name
, fu.user_name
, NVL(ppf.email_address,fu.email_address) email_address
FROM per_people_f ppf
, fnd_user fu
, fnd_logins fl
WHERE fl.start_time > SYSDATE - 2
AND fu.user_id = fl.user_id
AND ppf.person_id(+) = fu.employee_id
AND fu.user_name NOT IN ('SYSADMIN', 'GUEST')
/
Note, it will only pick up recent eBS users – it doesn’t look at database users who are logged in, so if there is other access to the system then you need to be aware that this isn’t going to be a definitive list. Good starter for ten though.
Posted in Oracle, Technical | No Comments »
