During the runtime each p_parameter value will be replaced with the actual value selected by the user in the parameter.
How to... in Oracle EBS
Tuesday, October 8, 2024
Turn SQL into an EBS Excel report
During the runtime each p_parameter value will be replaced with the actual value selected by the user in the parameter.
Monday, September 16, 2024
Robust messaging system in EBS
Oracle EBS does not provide a straightforward and efficient tool for communicating important messages to users.
Currently, we have two main options for delivering actionable information to users:
- Create a concurrent request to generate a report for the user.
- Develop a workflow to send a message.
However, both methods are resource-intensive and add significant overhead to any project where they are implemented.
For the first option, a report must be developed, typically using Oracle Reports, followed by creating a BI Publisher template. This process requires a developer skilled in Oracle Reports and BI Publisher environments.
Workflows, on the other hand, are time-consuming to develop and test. Custom workflows, beyond the seeded ones, are rarely used for communication, which is unfortunate because workflow messages appear on the To-Do worklist that opens upon application login. This worklist contains all pending tasks and can serve as a checklist at the start of each workday.
Below, I outline a simple yet powerful API that enables you to transform any SQL query into a professionally formatted worklist message, offering a more efficient and user-friendly communication tool.
Here is a simple query:
SELECT Organization_id, organization_code org_code, calendar_code calendar, creation_date, last_update_date
FROM mtl_parameters
ORDER BY 2
instantly transformed into an Excel exportable worklist notification:
DECLARE
query VARCHAR2(4000) :=
'SELECT
Organization_id,
organization_code org_code,
Calendar_code calendar,
creation_date,
last_update_date
FROM
mtl_parameters
ORDER BY
2';
BEGIN
XX_UNIVERSAL_WORKFLOW.start_workflow (
p_query => query, -- Query that will retrieve the data for the report
p_performer => 'PBELTER', -- Recipient of the notification, application user name from fnd_user table
p_org_id => 81, -- Organization ID, mainly to select the corporate logo in the report, different per company
p_MESSAGE_TITLE => 'Warehouse listing', -- Notification subject
p_DOCUMENT_HEADING => 'List of Warehouses', -- Name of the report printed in the header
p_GENERIC_MSG_TOP => 'Here is the list of warehouses:<br>
It contains the code and the calendar<br>
But also a date when the warehouse was created<br>
And when it was updated', -- Introductory message that prints before all the data. Can be in HTML
p_GENERIC_MSG_BOTTOM => 'So now that we are done:<br>
ENJOY THE REST OF YOUR DAY!!' -- Summary message that prints after all the data
);
END;
To run this you need a very simple workflow with only 1 function that delivers the message:
&MESSAGE_TITLE in the subject and &MESSAGE in the HTML body
Tuesday, October 6, 2020
The system cannot determine the default tax rate
Have you encountered the below error when trying to cancel a Sales Order?
"Error: The system cannot determine the default tax rate for tax CITY and tax status STANDARD. Please contact your tax manager either to specify a default tax rate code for this tax status and date &DATE or to define appropriate rate determination rules. (TAX_DET_DATE=20-DEC-19)"
This is an extremely frustrating feature in EBS as the documentation does not give any reasonable clue why this is happening and besides, what do we care about the tax rate when the order is to be cancelled anyway?
I tried to find where the TAX_DET_DATE comes from and it seemed like this should be the TAX_DATE from OE_ORDER_LINES_ALL.
Well, it is not.
It is PROMISE_DATE from OE_ORDER_LINES_ALL.
This is beyond silly, but when I pointed this out to DEV they just shrugged and said that works as designed.
Anyway, change the Promise Date on your order lines to be today any you will be able to cancel the order.
Thursday, July 2, 2015
Calling Oracle forms documents form non EBS applications, i.e. OBIEE
- Identify the Oracle E-Business Suite Application page/function that you want to link to. Obtain the function_id of that page and identify the querystring parameters required by that page. This will have to be done by going through Oracle E-Business Suite documentation."
Even when a particular function cannot be placed on navigator, it may be still possible to get direct link to it, but it is harder to determine the right parameters. They can be determined by opening the form in Forms Developer and reading them from the triggers.
The Org items and Material Workbench are examples of this scenario.
'ORGANIZATION_ID='||organization_id||' INVENTORY_ITEM_ID='||inventory_item_id||' OWNING_QUERY_MODE=1 G_QUERY_FIND=FALSE')
from mtl_onhand_quantities
-- skip the organization_id if you want availability across orgs
--Material transactions for Sales Order Issue, for specific item that not older than 3 months and not newer than one month (Inventory). This is an interesting example as it involves dates
SELECT FND_RUN_FUNCTION.get_run_function_url (
fnd_function.get_function_id ('INV_INVTVTXN'),
401,
20634,
0,
'MODULE=CST FORM_USAGE_MODE=DSLA_DRILLDOWN INVTVTXN_GO_DETAIL=Y ORG_ID=102 INVTVTXN_INVENTORY_ITEM_ID='
|| inventory_item_id
|| ' INVTVTXN_TRXN_TYPE_ID=33 INVTVTXN_FROM_DATE='
|| SUBSTR (fnd_date.date_to_canonical (SYSDATE - 90),
1,
INSTR (fnd_date.date_to_canonical (SYSDATE - 90),
' ',
1,
1)
- 1)
|| ' INVTVTXN_TO_DATE='
|| SUBSTR (fnd_date.date_to_canonical (SYSDATE - 30),
1,
INSTR (fnd_date.date_to_canonical (SYSDATE - 30),
' ',
1,
1)
- 1))
FROM mtl_system_items_b
WHERE organization_id = 102 AND segment1 = 'XFR0150R240R'
fnd_function.get_function_id ('WIP_WIPDJMDF_DEFINE'),
706, 20560,
0,
'GID="'
|| wip_entity_id
|| '" ORG_ID="'
|| organization_id
|| '"')
FROM wip_discrete_jobs where trunc(creation_date)=trunc(sysdate)
A problem with calling Oracle from from an URL is that if you already have EBS open the URL may hijack the current session and hard close all the documents on the screen while keeping them locked.
To avoid that use this syntax when calling the URL from a third application:
Opening the URL with the -nonframemerging option in IE will force a new EBS window to open.
Sunday, May 17, 2015
Email override for concurrent request Delivery Options
Gone are the days where one had to build a new workflow and create a role to email a document to external party. With BI publisher the emails can be very fancy containing colors, graphics end everything else as long as MS Word and HTML can take it.
But adding this new great feature oracle broke something else. The Workflow Email Override Address does not apply to the Delivery Options and that means that one can send external emails form a cloned test instance. As you can imagine the consequences can be disastrous, i.e. we have a scheduled concurrent requests that email customers the AR invoices. Now if someone started testing AR invoices in the clone instance...
Some companies worked around the issue by blocking the email functionality on clone instances. That works but now one can't properly test the new workflow emails nor concurrent request emails. So there are 3 options:
- you can deploy new functionality related to workflow or concurrent request emailing but risk that critical documents such as invoices get emailed to customers from the cloned instance
- do not deploy any new functionality
- deploy in PROD but without complete test cycle
Surprisingly, Oracle DEV being the siloed organization they are, looked only at the Delivery Option side of the coin and recognized the need as an enhancement request #9308736. ERs are nice but they can take years to deploy, it is 2015 and and couple of ERs I created in 2002 and 2004 were just recently released.
If waiting 10 or 15 years is not an option for your organization here is how you can fix the issue:
- Create an email address where the string is equal to the SID of the test database and get access to the email box. It would have this format: TEST@yourcompanyname.com. Replace the values with proper SID and domain names
- Add this script to the cloning process. The script needs to be incorporated as integral part of the cloning process and executed after the SID has been already changed form prod to the test instance, but before concurrent manager is brought up:
-- after the NAME column has been set in V$DATABASE to the correct clone instance
-- but before concurrent managers get up
--This section sets the workflow email override address to
--be equal to the SID of the test instance @yourcompanyname.com
update fnd_svc_comp_param_vals set parameter_value=(select name from v$database)||'@'||'yourcompanyname.com'
where parameter_id =(select parameter_id from fnd_svc_comp_params_tl where display_name = 'Test Address' );
commit;
-- This section updates all the concurrent requestsincluding those that are scheduled or were being
-- executed when the instance was cloned
-- It updates the email address of every request to eb that of the workflow email override
update fnd_conc_pp_actions set argument3=
( select fscpv.parameter_value -- go to workflow override email address
from fnd_svc_comp_params_tl fscpt
,fnd_svc_comp_param_vals fscpv
where fscpt.display_name = 'Test Address'
and fscpt.parameter_id = fscpv.parameter_id),
argument4=( select fscpv.parameter_value -- go to workflow override email address
from fnd_svc_comp_params_tl fscpt
,fnd_svc_comp_param_vals fscpv
where fscpt.display_name = 'Test Address'
and fscpt.parameter_id = fscpv.parameter_id),
argument5=null,
argument6=null,
argument8=null,
argument9=null,
argument10=null
where ACTION_TYPE =7 ;
commit;
- Create trigger on the FND_CONC_PP_ACTIONS table to update any email specified by user during new request submission to the email override. This script needs to be executed at the same time as the previous one