Today’s post is about forms personalization. Forms personalization is a way to customize Oracle Applications without requiring a broad knowledge on programming languages. Although, if you want to perform more complex customizations you might require some PL/SQL skills.
I will try to explain with a very simple example the basic functionality of forms personalization and on the way I will review on how to call a stored procedure within the personalization and how to catch messages from the procedure and print them on screen (you’ll be surprised on how useful can this messages be)
As usual, here is the list of what we need, and how we will do it.
We need:
1.- Oracle Applications (obviously)
2.- The Utilities and Diagnostics profile enabled at the user level (you should be careful with this profile, since it enables the examine function for Oracle forms)
3.- A bit of knowledge on Forms Personalization. If you are new to this you can review some information here and here
And this other document contains more detailed information on the topic.
4.- SQL and PL/SQL skills will be very helpful when developing more complex personalizations. If you feel like learning PL/SQL you can download the Oracle PL/SQL bible here
How we will do it:
1.- Clearly define what your personalization needs to accomplish. This can be as simple as setting a field as required or inactivating a field, and as complex as inserting records into an interface table, calling public APIs to perform transactions, launching Oracle preseeded or customized Oracle Workflows.
2.- From Oracle EBS, identify what objects and events you need to use and set up the personalization.
3.- If you are calling a procedure, you might need to catch a message as a result. The easiest way to do this is to create a table and insert the results on this table. This way you will just need to perform a simple SQL statement to get the message.
4.- Incorporate security to your personalization if needed.
5.- Test your personalization.
Ok so let’s start.
First thing to do is to setup the Utilities and Diagnostics profile at the user level as Yes. TO do this log into System Administrator responsibility—Profiles. Now query for Utilities:Diagnostics at the user level (be careful with this you should NEVER set it up at the site level since you will grant access to every user which can lead to very harmful results).
Once this profile has been set up log into the Oracle EBS form that you which to customize. Go to Help – Diagnostics – Custom Code --- Personalize.
This will make the Forms Personalization window to prompt.
Now for the purpose of this example we will enable a menu in the form. This menu will call a procedure in the database. This procedure will perform some actions (this actions can be anything that you want) and return a message to the user. I have put all the figures of this post in a file that you can access here
First let’s create the menu. Forms Personalization works with pre-defined events and objects. WHEN-NEW-FORM-INSTANCE event as you can guess is called every time the form is loaded. This event is useful for things that need to be done once (such as adding menus and initialize variables. If you are not sure when an event is called, you can set forms to show all events by going to Help—Diagnostics—Custom Code—Show Custom Events. This will launch a message for every event that is executed.
What we will do is first create a personalization. Input a sequence and a description and set the event to be WHEN-NEW-FORM-INSTANCE. Input any condition for this personalization if you need to, leave blank if none applies. On the bottom you can set the security for this personalization. You can use industry, responsibility or user. I will set this personalization to perform just for one responsibility. Check Fig1.
Now we need to set an action for this personalization, go to the Actions tab. Input a sequence and a description for the action. Set type as Menu. On the right set a Menu entry. Be sure that you don’t override one of the existing menus. All the entries that start with menu can be found under the Actions for menu. All the ones that start with Special can be found under the Tools form Menu. For this example we will use the Tools menu. Set up menu entry as Special 15. Set the menu label as ‘Call Procedure’.
You can also set the menu to be enabled only on a certain form block. Save your work. Check Fig2.
Now go to the condition tab again. Create a new sequence and a description for the personalization. Set the description as ‘Call Procedure’. On trigger event now set the menu we just created (Special 15). Go to the actions tab.
Set a sequence for the action and choose Builtin type. Now on the right under Builtin type set Execute a procedure. In argument we need to input the call to the procedure, this can be a little tricky, so review this example carefully:
='declare v_field_value VARCHAR2(500);begin scheme.procedure('''||${item.tolines_blk.header_id.value}||''',
'''||${item.tolines_blk.line_id.value}||''','''||fnd_global.user_id||''','''||fnd_global.org_id||''');
end'
Basically what we are doing is to call the procedure and passing the necessary arguments. The procedur in the example uses four input parameters. I have put two types of arguments, ones are items within the form (item.tolines_blk.header_id.value) and the other ones are sql statements (fnd_global.user_id). You can also pass constant values to the procedure.
Your procedure structure should be like the following:
Create procedure (argument1 argument type
….)
…Validations
…Save message in a variable
…Perform actions if validations passed
…Save message in custom table
End
In order to show the output message to the user, what we do is to save it into a table and the just query it up. You can use a table that has the following structure
Field Type Description
USER_ID Number Stores the user_id for the message
MESSAGE VARCHAR2(1000) Stores the output message
STATUS Varchar2(1) Status flag for the message
You can use the following sentence to create the table:
create table table_name (user_id number, message varchar2(1000), status varchar2(1));
After you created the table, remember to grant access to the user apps so the table can be accessed from Oracle EBS.
grant all on table_name to apps
On your procedure you should be cleaning the table for the specific user_id just before inserting the new message, this way you will only have one message for every user at any time. You should do something like this at the end of your procedure for every execution:
DELETE FROM table_name where user_id = p_user_id
INSERT INTO table_name VALUES (p_user_id,p_message,p_estado);
Now save your work. Check Fig4
Create a new personalization. Set a sequence and as description set ‘OUTPUT MESSAGE’.
Input responsibility security.
You can set up a condition for this personalization, for instance since we are saving a status in the message table so we can send no message if the status is successful and we can send a message if status is unsuccessful.
Now go to the actions tab and set the action type as message.
Select Show as message type and under the message text set the following:
=(select message from my_log_table where user_id = (select fnd_global.user_id from dual))
This will bring up the message stored in the message table.
Save your work.
Now close the form and reopen it. This will make the personalization to take effect.
Go to the Tools menu and launch the procedure. Check Fig 5 and 6.
And that’s pretty much of it.
I didn’t put a sample procedure since this should be developed based on your needs. Bottom line here is to know that we are able to launch a procedure within Oracle Personalization without the need to modify Oracle Forms.
Diego
sábado, 16 de mayo de 2009
domingo, 19 de abril de 2009
Email Purchasing Documents to Suppliers
Continuing with the emailing functionality, today’s tip is to automatically email reports from Oracle. Something like the PO Output for Communication report.
For instance have you ever wonder on how to send quotations to different suppliers automatically within Oracle EBS. Well, there are several ways to achieve this but I like this option since it allows you to customize almost anything you need and it can be incorporated into any custom report. What we are doing here is to setup a report that given a user parameter will automatically email the output. So here is a brief explanation o what we need to do it and how to do it:
We need:
1.- Oracle Reports v 6i or above.
2.- Oracle XML Publisher version 5.6.3 or above.
3.- A custom request for quotation (RFQ) report that groups by quotation-supplier and that is using an XML Publisher template.
4.- Some technical skills (as usual).
If you are new to XML Publisher, this tool is used as a standalone reporting tool. But when using it within Oracle EBS it is combined with Oracle Reports to provide fancy output formats (including PDF and EXCEL file types). You can setup templates as a Word Document, for this you need to install XML Publisher Desktop on your PC or laptop. XML Publisher came to solve the output formats lack of flexibility of Oracle Reports and it has some other enhancements as the one we will describe on this post. For a complete review on XML Publisher review the Oracle® XML Publisher Administration and Developer's Guide Release 11i Part No. E05321-013.
In general here is how we achieve it:
1.- We will use XML Publisher bursting feature. To enable this feature we need to setup the report so it can invoke the bursting program automatically.
2.- We will need to setup a control file within XML Publisher, this will tell the bursting program how to split the report and how it should be sending it
3.- We setup a new parameter in the report called P_BURST, which will allow the user to either burst or not at run time.
We will take for granted that the RFQ report has been developed already and that it uses an XML Publisher template as an output.
First, we will need to edit the custom report.
Open the rdf file in Oracle Reports. Go to Tools—Object Navigator.
Add the following user parameters:
P_BURST – type character, length 5
P_CONC_REQUEST_ID – type number, length 10
Now under the Object Navigator, go to the before report trigger and add the following line:
srw.user_exit('FND SRWINIT');
This will make Oracle Reports to save the id of the request in the variable P_CONC_REQUEST_ID
Now under the Object Navigator, go to the after report trigger and add the following lines:
req_id number;
begin
if :P_BURST = 'Y' then
req_id := apps.fnd_request.submit_request('XDO','XDOBURSTREP','',
'',FALSE,:P_CONC_REQUEST_ID,
'Y',chr(0),'','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','');
if req_id = 0 then
srw.message(100,'Bursting program failed’);
end if;
end if;
srw.user_exit('FND SRWEXIT');
return (TRUE);
Basically what this code does, is to call the bursting engine if the parameter P_BURST is received as 'Y'
This is everything that we will need to update in the report. Compile and upload the report to the server.
Next, create the control file. A control file is a set of XML instructions for the bursting program. Your control file should look like this (replace all the [] for <> I replaced them since it was being caught as XML in the post). You can use any text editor to create the control file:
2. [xapi:requestset xmlns:xapi="http://xmlns.oracle.com/oxp/xapi" type="bursting"]
3. [xapi:request select="/GPIN_POXRCOT2/LIST_G_DEFAULT_EFFECTIVE_DATE/G_DEFAULT_EFFECTIVE_DATE"]
4. [xapi:delivery]
5. [xapi:email server="localhost" port="25" from="${EMAIL_BUYER}"]
6. [xapi:message id="${PO_HEADER_ID}" to="${E_MAIL}" cc="${EMAIL_BUYER}" attachment="true" subject=" Solicitud de cotizacion"]
7. Dear Supplier,
8. Attached to this email you will find a request for quotation for you to review and answer.
9. Kind Regards
10. Purchasing Department
11. [/xapi:message]
12. [/xapi:email]
13. [/xapi:delivery]
14. [xapi:document output = "Cotizacion ${FOLIO} ${NOMBRE}" output-type="pdf" delivery="${PO_HEADER_ID}"]
15. [xapi:template type="rtf" locale = "esa-MX" location="/ppesci/applmgr/CUSTOM/xbol/11.5.0/templates/gpin_poxrcotms.rtf" filter =""]
16. [/xapi:template]
17. [/xapi:document]
18. [/xapi:request]
19. [/xapi:requestset]
This two lines are needed on every control file and they set up the character set and the api that will be used
This line tells the bursting engine how it should split the ouputs. For this example below are the groups within Oracle Reports data model and they should be splitted by supplier
The following lines tell the bursting engine that it should deliver an email and the email server and port to be used (this might change from one server to another, you should check this with your system administrator). You also set the from, to, cc, attachment and subject parameters for the email.You can use fields from the report here and call them with the syntax ${NAME}. For this example in the report we obtain the supplier email ${E_MAIL} and the buyer email ${E_MAIL_BUYER} and use them to dynamically set the from and to email addresses.
--------------------------------------------
Input the email message for the supplier
---------------------------------------------
And last, set up the file name that the attachment will have. You will also need to upload the template file (rtf file) t o an accessible location in the server. For this example we will be emailing a PDF file.
Once you have created the control file go to the XML Publisher Administrator responsibility.
Log into Home – Data Definitions
Query the data definition for the RFQ report
Click on the bursting control file button and upload the control file you just created.
Apply changes.
XML Publisher will validate that you have uploaded a valid XML File, be sure to check all the closing tags if you receive an error message.
Now we have successfully setup the report and the template for bursting.
We now log into the System Administrator responsibility and go to Applications – Define Programs. (The menu access may vary from one responsibility to another)
Query the RFQ concurrent program.
Go to the parameters window
Add a new parameter called P_BURST with a value set of YES_NO. The value set should pass the values Y and N to the report.
Save your work.
Now try to execute the report and set the bursting parameter to N.
The report should end normally.
Execute it again and now set the bursting parameter to Y.
Once the request finishes you will see a new request being executed (this is a bursting engine). Verify that the bursting engine ends normally. You should now have a new mail on the emails obtained in the report. Review the log file to check if there something wrong. Usually if emails are not being set is due to a missing email address (either supplier or buyer)
Now you have successfully configured the bursting functionality. Purchasing users (specially the really lazy ones) will love this feature since. This can be setup to any custom report, so that means that it can be used for RFQs as well as purchase orders.
Diego
For instance have you ever wonder on how to send quotations to different suppliers automatically within Oracle EBS. Well, there are several ways to achieve this but I like this option since it allows you to customize almost anything you need and it can be incorporated into any custom report. What we are doing here is to setup a report that given a user parameter will automatically email the output. So here is a brief explanation o what we need to do it and how to do it:
We need:
1.- Oracle Reports v 6i or above.
2.- Oracle XML Publisher version 5.6.3 or above.
3.- A custom request for quotation (RFQ) report that groups by quotation-supplier and that is using an XML Publisher template.
4.- Some technical skills (as usual).
If you are new to XML Publisher, this tool is used as a standalone reporting tool. But when using it within Oracle EBS it is combined with Oracle Reports to provide fancy output formats (including PDF and EXCEL file types). You can setup templates as a Word Document, for this you need to install XML Publisher Desktop on your PC or laptop. XML Publisher came to solve the output formats lack of flexibility of Oracle Reports and it has some other enhancements as the one we will describe on this post. For a complete review on XML Publisher review the Oracle® XML Publisher Administration and Developer's Guide Release 11i Part No. E05321-013.
In general here is how we achieve it:
1.- We will use XML Publisher bursting feature. To enable this feature we need to setup the report so it can invoke the bursting program automatically.
2.- We will need to setup a control file within XML Publisher, this will tell the bursting program how to split the report and how it should be sending it
3.- We setup a new parameter in the report called P_BURST, which will allow the user to either burst or not at run time.
We will take for granted that the RFQ report has been developed already and that it uses an XML Publisher template as an output.
First, we will need to edit the custom report.
Open the rdf file in Oracle Reports. Go to Tools—Object Navigator.
Add the following user parameters:
P_BURST – type character, length 5
P_CONC_REQUEST_ID – type number, length 10
Now under the Object Navigator, go to the before report trigger and add the following line:
srw.user_exit('FND SRWINIT');
This will make Oracle Reports to save the id of the request in the variable P_CONC_REQUEST_ID
Now under the Object Navigator, go to the after report trigger and add the following lines:
req_id number;
begin
if :P_BURST = 'Y' then
req_id := apps.fnd_request.submit_request('XDO','XDOBURSTREP','',
'',FALSE,:P_CONC_REQUEST_ID,
'Y',chr(0),'','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','');
if req_id = 0 then
srw.message(100,'Bursting program failed’);
end if;
end if;
srw.user_exit('FND SRWEXIT');
return (TRUE);
Basically what this code does, is to call the bursting engine if the parameter P_BURST is received as 'Y'
This is everything that we will need to update in the report. Compile and upload the report to the server.
Next, create the control file. A control file is a set of XML instructions for the bursting program. Your control file should look like this (replace all the [] for <> I replaced them since it was being caught as XML in the post). You can use any text editor to create the control file:
1. [?xml version="1.0" encoding="UTF-8"?]
2. [xapi:requestset xmlns:xapi="http://xmlns.oracle.com/oxp/xapi" type="bursting"]
3. [xapi:request select="/GPIN_POXRCOT2/LIST_G_DEFAULT_EFFECTIVE_DATE/G_DEFAULT_EFFECTIVE_DATE"]
4. [xapi:delivery]
5. [xapi:email server="localhost" port="25" from="${EMAIL_BUYER}"]
6. [xapi:message id="${PO_HEADER_ID}" to="${E_MAIL}" cc="${EMAIL_BUYER}" attachment="true" subject=" Solicitud de cotizacion"]
7. Dear Supplier,
8. Attached to this email you will find a request for quotation for you to review and answer.
9. Kind Regards
10. Purchasing Department
11. [/xapi:message]
12. [/xapi:email]
13. [/xapi:delivery]
14. [xapi:document output = "Cotizacion ${FOLIO} ${NOMBRE}" output-type="pdf" delivery="${PO_HEADER_ID}"]
15. [xapi:template type="rtf" locale = "esa-MX" location="/ppesci/applmgr/CUSTOM/xbol/11.5.0/templates/gpin_poxrcotms.rtf" filter =""]
16. [/xapi:template]
17. [/xapi:document]
18. [/xapi:request]
19. [/xapi:requestset]
This two lines are needed on every control file and they set up the character set and the api that will be used
1. [?xml version="1.0" encoding="UTF-8"?] 2. [xapi:requestset xmlns:xapi="http://xmlns.oracle.com/oxp/xapi" type="bursting"]
This line tells the bursting engine how it should split the ouputs. For this example below are the groups within Oracle Reports data model and they should be splitted by supplier
3.. [xapi:request select="/GPIN_POXRCOT2/LIST_G_DEFAULT_EFFECTIVE_DATE/G_DEFAULT_EFFECTIVE_DATE"]
The following lines tell the bursting engine that it should deliver an email and the email server and port to be used (this might change from one server to another, you should check this with your system administrator). You also set the from, to, cc, attachment and subject parameters for the email.You can use fields from the report here and call them with the syntax ${NAME}. For this example in the report we obtain the supplier email ${E_MAIL} and the buyer email ${E_MAIL_BUYER} and use them to dynamically set the from and to email addresses.
4. [xapi:delivery] 5. [xapi:email server="localhost" port="25" from="${CF_EMAIL_ELABORO}"] 6. [xapi:message id="${PO_HEADER_ID}" to="${E_MAIL}" cc="${CF_EMAIL_ELABORO}" attachment="true" subject=" Solicitud de cotizacion"]
--------------------------------------------
Input the email message for the supplier
---------------------------------------------
And last, set up the file name that the attachment will have. You will also need to upload the template file (rtf file) t o an accessible location in the server. For this example we will be emailing a PDF file.
14. [xapi:document output = "Cotizacion ${FOLIO} ${NOMBRE}" output-type="pdf" delivery="${PO_HEADER_ID}"] 15. [xapi:template type="rtf" locale = "esa-MX" location="/ppesci/applmgr/CUSTOM/xbol/11.5.0/templates/gpin_poxrcotms.rtf" filter =""]
Once you have created the control file go to the XML Publisher Administrator responsibility.
Log into Home – Data Definitions
Query the data definition for the RFQ report
Click on the bursting control file button and upload the control file you just created.
Apply changes.
XML Publisher will validate that you have uploaded a valid XML File, be sure to check all the closing tags if you receive an error message.
Now we have successfully setup the report and the template for bursting.
We now log into the System Administrator responsibility and go to Applications – Define Programs. (The menu access may vary from one responsibility to another)
Query the RFQ concurrent program.
Go to the parameters window
Add a new parameter called P_BURST with a value set of YES_NO. The value set should pass the values Y and N to the report.
Save your work.
Now try to execute the report and set the bursting parameter to N.
The report should end normally.
Execute it again and now set the bursting parameter to Y.
Once the request finishes you will see a new request being executed (this is a bursting engine). Verify that the bursting engine ends normally. You should now have a new mail on the emails obtained in the report. Review the log file to check if there something wrong. Usually if emails are not being set is due to a missing email address (either supplier or buyer)
Now you have successfully configured the bursting functionality. Purchasing users (specially the really lazy ones) will love this feature since. This can be setup to any custom report, so that means that it can be used for RFQs as well as purchase orders.
Diego
viernes, 17 de abril de 2009
Email alert for approved Purchasing Requisitions
Today's tip is for those implementers who are dealing with users who expect a little more than what the Oracle EBS does..you know, users who want the system to do things like:
* Notifications on what work they shall be doing
* Warnings when you mismatch 1,000.00 for 10,000.00
* Email the office's restaurant menu daily
* Weather analysis for the weekend on Cancún
You know, just simple things.
But hey, don't get me wrong, users might get a little too enthusiastic on what Oracle EBS does, but part of the problem comes with our pre-sales and sales buddies. Sometimes they get a little too excited when explaining all the ERP features to the users, but well what can we do this is how it works.
Anyway, after that subtle comment on over-enthusiastic users and our non-technical buddies on sales, Have you ever wonder how can you notify to buyers when a purchasing document has been approved? Simple right, just tell the user to log into his/her notifications within Oracle EBS and done. Well not quite, this tip is for users that are a little more reluctant to the whole ERP thing. Users who prefer this kind of notifications on email.
This can be achieved by using Oracle Alerts. Oracle Alerts is a EBS module that allow users to configure actions that trigger on predefined events. Most common type of alerts are email and notifications.
This functionality is quite simple to implement:
First, you will need Oracle Purchasing installed and running (of course).
Second, you need Oracle Alerts up and running.
Third, verify that Workflow Mailer is up and running (this is how email notifications are sent)
Fourth, you will need some technical skills for this, if you are one of those consultants that claim to be only functional please go to SAP, if you are on Oracle you should have some technical skills.
Later versions of Oracle Alerts use Workflow Mailer to perform email notifications, but you should verify which version are you on, since the sendmail command was used on previous releases.
For this example we will be notifiyng the buyer when a purchase requisition is been approved. Here is how is done:
1.- Configure an Alert
Go to Alert Manager responsibility
Log into Define form
Type a name and a description for the alert
Set it for type Event
Under Application choose Purchasing and under table choose PO_REQUISITION_HEADERS_ALL
Under the SQL box type the following statement:
Just some points to notice on the SQL:
* For this example we have hard-coded the buyer's email depending on the org_id. This can be set dynamic since there a buyer field in the requisition but the users will have to type the buyer for every line in the requisition. Once you got the buyer you can get the email for that user.
* The :rowid variable restricts the statement only to the current record (the one that is being updated, inserted)
* The fields preceded by (&) within the into clause, are the fields passed as variables to the alert. This fields can be used later in the message or email.
Validate the SQL statement and confirm that there are no errors.
Now go to the Actions b utton and create a new action called SEND_MAIL (this can be any name you want)
Set the action level to Summary
Now go to the Action Details button and set the action type as Message. You will see a kind of email form. Under the TO field type &mail_comprador which is the variab
le name on the SQL statement for the buyer's email. Set a Subject for the email
Enter the text that you wan t the buyer to see. In this text you can include any variable from the SQL statement (such as requisition number, preparer, etc)
Close the window and now go to Action Sets and type an action set name.
Go to Action Set Details and unde r the Members tab verify that the action is set correctly
Close the window and now go to the Alert Details and verify that you have the correct operating units for the alert to work on.
Now you have succesfully created the alert. Now try approving a requisition that meets the criteria in your alert. Afterwards run the Workflow Background Process concurrent program (emails won't be send before this concurrent is executed) and wait for the email. It shouldn't take more that a couple minutes to arrive.
Hope it helps.
Diego
* Notifications on what work they shall be doing
* Warnings when you mismatch 1,000.00 for 10,000.00
* Email the office's restaurant menu daily
* Weather analysis for the weekend on Cancún
You know, just simple things.
But hey, don't get me wrong, users might get a little too enthusiastic on what Oracle EBS does, but part of the problem comes with our pre-sales and sales buddies. Sometimes they get a little too excited when explaining all the ERP features to the users, but well what can we do this is how it works.
Anyway, after that subtle comment on over-enthusiastic users and our non-technical buddies on sales, Have you ever wonder how can you notify to buyers when a purchasing document has been approved? Simple right, just tell the user to log into his/her notifications within Oracle EBS and done. Well not quite, this tip is for users that are a little more reluctant to the whole ERP thing. Users who prefer this kind of notifications on email.
This can be achieved by using Oracle Alerts. Oracle Alerts is a EBS module that allow users to configure actions that trigger on predefined events. Most common type of alerts are email and notifications.
This functionality is quite simple to implement:
First, you will need Oracle Purchasing installed and running (of course).
Second, you need Oracle Alerts up and running.
Third, verify that Workflow Mailer is up and running (this is how email notifications are sent)
Fourth, you will need some technical skills for this, if you are one of those consultants that claim to be only functional please go to SAP, if you are on Oracle you should have some technical skills.
Later versions of Oracle Alerts use Workflow Mailer to perform email notifications, but you should verify which version are you on, since the sendmail command was used on previous releases.
For this example we will be notifiyng the buyer when a purchase requisition is been approved. Here is how is done:
1.- Configure an Alert
Go to Alert Manager responsibility
Log into Define form
Type a name and a description for the alert
Set it for type Event
Under Application choose Purchasing and under table choose PO_REQUISITION_HEADERS_ALL
Under the SQL box type the following statement:
SELECT prh.segment1 num_req, prh.description,
prh.creation_date fecha_creacion, prh.approved_date fecha_aprob,
prh.preparer_id id_usuario, pap.full_name solicitante, decode(prh.org_id,119,'XXX@XXX.com,120,XX@XXX.com')
INTO &num_req, &descr, &fec_creacion, &fec_aprob,
&id_usuario, &solicitante, &mail_comprador
FROM po_requisition_headers_all prh,
po_requisition_lines_all prl,
per_all_people_f pap
WHERE prh.requisition_header_id = prl.requisition_header_id
AND approved_date IS NOT NULL
AND pap.person_id = prh.preparer_id
AND NVL (prh.cancel_flag, 'N') = 'N'
AND prh.authorization_status = 'APPROVED'
AND prh.org_id in (119,120)
AND prh.rowid = :rowid
Just some points to notice on the SQL:
* For this example we have hard-coded the buyer's email depending on the org_id. This can be set dynamic since there a buyer field in the requisition but the users will have to type the buyer for every line in the requisition. Once you got the buyer you can get the email for that user.
* The :rowid variable restricts the statement only to the current record (the one that is being updated, inserted)
* The fields preceded by (&) within the into clause, are the fields passed as variables to the alert. This fields can be used later in the message or email.
Validate the SQL statement and confirm that there are no errors.
Now go to the Actions b utton and create a new action called SEND_MAIL (this can be any name you want)
Set the action level to Summary
Now go to the Action Details button and set the action type as Message. You will see a kind of email form. Under the TO field type &mail_comprador which is the variab
le name on the SQL statement for the buyer's email. Set a Subject for the email
Enter the text that you wan t the buyer to see. In this text you can include any variable from the SQL statement (such as requisition number, preparer, etc)
Close the window and now go to Action Sets and type an action set name.
Go to Action Set Details and unde r the Members tab verify that the action is set correctly
Close the window and now go to the Alert Details and verify that you have the correct operating units for the alert to work on.
Now you have succesfully created the alert. Now try approving a requisition that meets the criteria in your alert. Afterwards run the Workflow Background Process concurrent program (emails won't be send before this concurrent is executed) and wait for the email. It shouldn't take more that a couple minutes to arrive.
Hope it helps.
Diego
miércoles, 15 de abril de 2009
Welcome Post
Welcome,
If you have arrived here you are for sure either an Oracle consultant or an Oracle user, hence you are one of the thousands that have to deal with this ERP on a daily basis. We often receive negative comments about ERPs in general, so what this blog will be focus on is to provide some simple tips (maybe a few will be a little more complex) on how to make life easier when using Oracle EBS.
I have to say that even tough many solutions posted here are highly technical I’m a Functional Consultant for the Supply Chain modules. Due to the lack of technical consultants and a slight technical background I have, I got my hands really dirty in the technical stuff. If you are a functional consultant that doesn’t like to get involved into the technical well you are free to stay out of it, but from my short experience a functional-technical consultant can work much faster to solve problems than a pure functional.
The tips and solutions that I will be posting here are some of the stuff developed within the few years I have been working on Oracle EBS. I will be posting stuff on several modules such as Inventory, Purchasing, Order Management and also on BI Publisher, Oracle Alerts, Reports and so on.
Feel free to comment on any of the posts and also I you think you have an interesting solution and want me to post it here we can work on that too.
If you have arrived here you are for sure either an Oracle consultant or an Oracle user, hence you are one of the thousands that have to deal with this ERP on a daily basis. We often receive negative comments about ERPs in general, so what this blog will be focus on is to provide some simple tips (maybe a few will be a little more complex) on how to make life easier when using Oracle EBS.
I have to say that even tough many solutions posted here are highly technical I’m a Functional Consultant for the Supply Chain modules. Due to the lack of technical consultants and a slight technical background I have, I got my hands really dirty in the technical stuff. If you are a functional consultant that doesn’t like to get involved into the technical well you are free to stay out of it, but from my short experience a functional-technical consultant can work much faster to solve problems than a pure functional.
The tips and solutions that I will be posting here are some of the stuff developed within the few years I have been working on Oracle EBS. I will be posting stuff on several modules such as Inventory, Purchasing, Order Management and also on BI Publisher, Oracle Alerts, Reports and so on.
Feel free to comment on any of the posts and also I you think you have an interesting solution and want me to post it here we can work on that too.
Suscribirse a:
Entradas (Atom)
