While migration to R12 with Noetix is very easy and relatively painless in some areas the significant changes in Inventory between the two versions make it practically impossible for Noetix views to handle the migration "invisibly" (as they tend to do in other areas like Account Payable).
This is especially true if your organisation, like mine, is taking this opportunity to do a bit of restructuring the system so that, for example, rather than using stock being in a particular warehouse to mean that it's a retained sample achieving the same thing using statuses and getting rid of the warehouse.
Here is a list of R11 and R12 Noetix View Templates with "suggested" replacements. It's not a complete list, but it is the list we have been working of for all our reports;
View Label >>>> Recommended Replacement
GMP_Forecast_Details >>>> INV_Forecasts
GMI_Unallocated_Inventory >>>> INV_Unallocated_Inventory view
GMI_Onhand_Inv_By_Lot >>>> INV_Item_Onhand_By_Lot*
GMI_Onhand_Inv_By_Location >>>> INV_Onhand_Quantities*
GMI_Month_End_Inventory >>>> INV_Period_Close_Details*
GMI_Item_Master >>>> INV_Items and INV_Item_Inventory_Attributes
GMI_Inv_Transactions >>>> INV_Transactions or INV_Transaction_Details
GMF_Update_Subledger >>>> GMF_SLA_Cost_Subledger
GMF_Cost_Warehouse_Assoc >>>> GMF_Cost_Organization_Assc
GMF_Order_Details_Base >>>> GMF_SLA_Cost_Subledger
*- The migration from R11 Process Manufacturing Inventory to R12 Common Inventory for us has been less than smooth and has not been helped by a few, dare I say it, "obvious" issues that Noetix should have addressed prior to us attempting the upgrade.
The most obvious issue was that in R12 there was no view that would tell us the lot number, the location, and the onhand quantity. The best we could do is two out of three. I just cannot imagine any organisation out there not wanting to see all three on the same report. This led to us having to make significant customisations in order for the migration path to actually be a migration path.
For example looking at the view template INV_Item_Onhand_By_Lot we have added the following customisation columns (with some additional information where appropriate);
Days_Since_First_Transaction
ITEM (Table Alias= ITEM, Column expression=ITEM) << Flexfield
Item_Type (Table Alias= ITEM, Column expression=ITEM_TYPE)
Item_Type_Code (Table Alias= ITEM, Column expression=ITEM_TYPE)
LOCT (Table Alias= LOCT, Column expression=LOCT) << Flexfield
Material_Status (Table Alias= MMST, Column expression=DESCRIPTION)
Material_Status_Code (Table Alias= MMST, Column expression=STATUS_CODE)
Organization_Code (Table Alias= MPARM, Column expression=ORGANIZATION_CODE)
Here is our list of customisation to the GMI_Onhand_Inv_By_Lot view;
Batch_Id
Company_Code (Table Alias= XMAP, Column expression=COMPANY_CODE)
Company_Name (Table Alias= XMAP, Column expression=COMPANY_NAME)
Days_Since_First_Transaction
Inventory_Class (Table Alias= ITEM, Column expression=INV_CLASS)
Item_Cost_Class (Table Alias= ITEM, Column expression=ITEMCOST_CLASS)
Item_Id (Table Alias= ITEM, Column expression=Item_Id)
Lots_MFD_Flag (Table Alias= LOTS, Column expression=DECODE(LOTS.DELETE_MARK,0,'N',1,'Y'))
Qc_Hold_Reason_Code (Table Alias= LOINV, Column expression=QCHOLD_RES_CODE)
I've highlighted the location flexfield which if you look at the Noetix columns for the original view seems to directly map to the INVENTORY_LOCATION_CODE. There seems to be no mapping for this column down the migration path we were recommended to take.
I'm currently going through the process of sorting out our customisations (something that I'm sure will greatly relieve Noetix Support!) and I'll be publishing as much as I possibly can of the work we've done (including the difference between Lot Status and Material_Status which I'm sure will intrigue and worry people).
Showing posts with label Release 11. Show all posts
Showing posts with label Release 11. Show all posts
Wednesday, April 25, 2012
Wednesday, May 14, 2008
Oracle EBS: Automating Changes of Username
As as typically the case within any organisation that has been around for a while the rules behind generating a user name will change. In our case the change was from initials (ARP01 - Andy Pellew) to a sort of initial/surname combination (APE06 - yes, one day I'll get together with apes 1-5 and we'll take over the world).
Oracle will allow you to update a single users user name as everything is stored against the USER_ID (from the FND_USER table under APPLSYS) in the database.
The package below (XXXX_APPUSERMANAGER) allows you to specify an old user name and a new user name and does the switch for you (after validating that it should let you). The source code for the package is below;
And here is the required code for the package body;
create or replace package body XXXX_APPUSERMANAGER is
c_NOTFOUND constant number := -998;
c_BLANK constant number := -999;
/* ********** ********** ********** ********** ********** --
-- PRIVATE Routines (available only to this package) --
-- ********** ********** ********** ********** ********** */
function private_GetUser(p_Username in varchar2) return number as
cursor c_GetUser is
select u.user_id
from applsys.fnd_user u
where upper(u.user_name) = upper(p_UserName);
v_UserId applsys.fnd_user.user_id%type;
begin
open c_GetUser;
fetch c_GetUser
into v_UserId;
close c_GetUser;
if v_UserId is null then
if length(p_UserName) = 0 then
v_UserId := c_BLANK;
else
v_UserId := c_NOTFOUND;
end if;
end if;
return v_UserId;
end private_GetUser;
function private_emailSubjectTag return varchar2 as
v_Database v$database.name%TYPE;
begin
select distinct name into v_Database from v$database;
return '[User Admin - ' || nvl(v_Database, 'Null') || ']';
end private_emailSubjectTag;
function private_passwordGenerator return varchar2 as
-- This is not massively secure, but it's secure enough (providing you don't have external users!)
c_ALPHA constant varchar2(255) := '1DFC84XSW3ERTGBZA52KI9JM6LOPYH70N';
v_Position number := mod(to_number(to_char(sysdate, 'SSSSS')),
length(c_ALPHA));
v_Length number := mod(to_number(to_char(sysdate, 'SSSSS')), 4) + 6;
begin
return substr(c_ALPHA || c_ALPHA, v_Position, v_Length);
end;
/* ********** ********** ********** ********** ********** --
-- PUBLIC Routines (available globally) --
-- ********** ********** ********** ********** ********** */
procedure RenameUsers(p_ReturnMessage OUT VARCHAR2,
p_ReturnCode OUT NUMBER,
p_OldUserName IN VARCHAR2 default '',
p_NewUserName IN VARCHAR2 default '') as
pragma autonomous_transaction;
v_OldUserId number;
v_NewUserId number;
v_RowCount number;
v_Password varchar2(255);
v_EmailAddress applsys.fnd_user.email_address%TYPE;
v_URL varchar2(255) := 'http://%HOST%:8000/OA_HTML/AppsLocalLogin.jsp?cancelUrl=/OA_HTML/AppsLocalLogin.jsp&langCode=US&username=' ||
upper(p_NewUserName);
v_Host v$instance.HOST_NAME%TYPE;
procedure LogMessage(p_Text in varchar2) as
begin
apps.fnd_file.put_line(apps.fnd_file.log, p_Text);
end;
begin
LogMessage('START');
v_OldUserId := private_GetUser(p_OldUserName);
v_NewUserId := private_GetUser(p_NewUserName);
LogMessage('Renaming ' || p_OldUserName || '(' || to_char(v_OldUserId) ||
') to ' || p_NewUserName || '(' || to_char(v_NewUserId) || ')');
-- If the user details are blank raise an error
if (v_OldUserId = c_BLANK) or (Length(trim(p_NewUserName)) = 0) then
raise_application_error(-20005,
'ERROR: You cannot rename from/to a blank username!');
end if;
-- If the old user is not found then raise an error
if v_OldUserId = c_NOTFOUND then
raise_application_error(-20005,
'ERROR: The user "' || p_OldUsername ||
'" does not exist');
end if;
-- If the old user is not found then raise an error
if upper(p_OldUserName) = upper(p_NewUserName) then
raise_application_error(-20005,
'ERROR: Old and new usernames are the same!');
end if;
-- Get the email address, if there is no email address raise an error
select email_address
into v_EmailAddress
from applsys.fnd_user u
where u.user_id = v_OldUserId;
if v_EmailAddress is null then
raise_application_error(-20005,
'ERROR: Unable to update user "' ||
upper(p_OldUserName) ||
'" because they do not have a valid email address');
end if;
-- If the old user *is* found then raise an error
if v_NewUserId <> c_NOTFOUND then
raise_application_error(-20005,
'ERROR: The user "' || p_NewUserName ||
'" already exists');
end if;
-- Perform the rename
LogMessage('Updating user record (ID=' || to_char(v_OldUserId) || ')');
update applsys.fnd_user u
set u.user_name = upper(p_NewUserName)
where u.user_id = v_OldUserId;
v_RowCount := SQL%ROWCOUNT;
-- If anything other than a single record has been updated raise an error
if v_RowCount <> 1 then
raise_application_error(-20005,
'ERROR: Updating the user record has failed (' ||
to_char(v_RowCount) || ')');
end if;
-- Reset the users password. This is necessary because the encryption on the account includes
-- the Username in the key (so when we change the username we make it impossible for the user
-- to login - hence the resetting of the users password to a known value).
LogMessage('Initialising Oracle Application Suite (required for Password Change)');
execute immediate 'alter session set NLS_LANGUAGE = AMERICAN'; -- oddly we need these lines
execute immediate 'alter session set NLS_TERRITORY = AMERICA';
apps.fnd_global.apps_initialize(0, 20420, 1); -- switch to SYSADMIN under apps.
v_Password := private_passwordGenerator; -- this is a pretty poor generator, but is almost certainly secure enough for us.
-- Get the hostname (necessary to customise the URLs).
select lower(host_name) into v_Host from v$instance where rownum = 1;
LogMessage('Changing the users password');
fnd_user_pkg.UpdateUser(x_user_name => upper(p_NewUserName),
x_owner => 'SEED',
x_unencrypted_password => v_Password,
x_password_lifespan_days => '',
x_password_lifespan_accesses => '',
x_password_accesses_left => '');
-- Notify the user their account name and password have changed
XXXX_emailmanager.addEmail(p_From => v_EmailAddress,
p_To => v_EmailAddress,
p_Subject => private_emailSubjectTag ||
' User/Password Change Notification',
p_Body => '
Your username (which was "' ||
upper(p_OldUserName) ||
'") has been changed to "' ||
upper(p_NewUserName) ||
'". Your password has also been reset to "' ||
v_Password ||
'" (without the quotes).
Please click the Oracle link below to logon and change your password:
' ||
Replace(v_URL, '%HOST%', v_Host) ||
'
If you have any problems accessing the system please contact the IT Helpdesk.
');
XXXX_emailmanager.processMails;
commit;
p_ReturnMessage := 'OK';
p_ReturnCode := 0;
LogMessage('END');
exception
when others then
begin
p_ReturnMessage := '(' || TO_CHAR(SQLCODE) || ') ' || SQLERRM;
p_ReturnCode := 1;
logMessage('999 ERROR: (' || TO_CHAR(SQLCODE) || '): ' || SQLERRM);
XXXX_emailmanager.addEmail(p_From => 'errors@company.com',
p_To => 'errors@company.com',
p_Subject => private_emailSubjectTag ||
' Error',
p_Body => '999 ERROR: (' ||
TO_CHAR(SQLCODE) || '): ' ||
SQLERRM ||
'Please raise this issue with the helpdesk.');
XXXX_emailmanager.processMails;
rollback;
end;
end RenameUsers;
end XXXX_APPUSERMANAGER;
Now the bad news; this won't work (yes, I know that's a bit of a biggie). For copyright reasons several routines are missing from the package, the routine that sends the notification email to the user letting them know that their user name and password have been changed. In the packages these routines are referred to as:
XXXX_emailmanager.addEmail, and
XXXX_emailmanager.processMails
The former allows you to add an e-mail to a queue, the latter processes all emails in the queue (rather than waiting for an automated process to pick the new email up).
These are custom packages that sit on top of two packages from oracle; BASE64ENCODER and EMAILER. Unfortunately the links I have for these packages are no longer working but as this is pretty standard functionality that most people would want to do it shouldn't be too hard to find a replacement (or the packages themselves - try google).
One day it's my goal to write an installation script to get everything installed nicely, but that goal is not going to be met today (and as I'm updating this post 3 years later and I still haven't done it it's looking like "never" is the window of time this work will be dropping into!).
Oracle will allow you to update a single users user name as everything is stored against the USER_ID (from the FND_USER table under APPLSYS) in the database.
The package below (XXXX_APPUSERMANAGER) allows you to specify an old user name and a new user name and does the switch for you (after validating that it should let you). The source code for the package is below;
create or replace package XXXX_APPUSERMANAGER is
procedure RenameUsers(p_ReturnMessage OUT VARCHAR2,
p_ReturnCode OUT NUMBER,
p_OldUserName IN VARCHAR2 default '',
p_NewUserName IN VARCHAR2 default '');
end XXXX_APPUSERMANAGER;
procedure RenameUsers(p_ReturnMessage OUT VARCHAR2,
p_ReturnCode OUT NUMBER,
p_OldUserName IN VARCHAR2 default '',
p_NewUserName IN VARCHAR2 default '');
end XXXX_APPUSERMANAGER;
And here is the required code for the package body;
create or replace package body XXXX_APPUSERMANAGER is
c_NOTFOUND constant number := -998;
c_BLANK constant number := -999;
/* ********** ********** ********** ********** ********** --
-- PRIVATE Routines (available only to this package) --
-- ********** ********** ********** ********** ********** */
function private_GetUser(p_Username in varchar2) return number as
cursor c_GetUser is
select u.user_id
from applsys.fnd_user u
where upper(u.user_name) = upper(p_UserName);
v_UserId applsys.fnd_user.user_id%type;
begin
open c_GetUser;
fetch c_GetUser
into v_UserId;
close c_GetUser;
if v_UserId is null then
if length(p_UserName) = 0 then
v_UserId := c_BLANK;
else
v_UserId := c_NOTFOUND;
end if;
end if;
return v_UserId;
end private_GetUser;
function private_emailSubjectTag return varchar2 as
v_Database v$database.name%TYPE;
begin
select distinct name into v_Database from v$database;
return '[User Admin - ' || nvl(v_Database, 'Null') || ']';
end private_emailSubjectTag;
function private_passwordGenerator return varchar2 as
-- This is not massively secure, but it's secure enough (providing you don't have external users!)
c_ALPHA constant varchar2(255) := '1DFC84XSW3ERTGBZA52KI9JM6LOPYH70N';
v_Position number := mod(to_number(to_char(sysdate, 'SSSSS')),
length(c_ALPHA));
v_Length number := mod(to_number(to_char(sysdate, 'SSSSS')), 4) + 6;
begin
return substr(c_ALPHA || c_ALPHA, v_Position, v_Length);
end;
/* ********** ********** ********** ********** ********** --
-- PUBLIC Routines (available globally) --
-- ********** ********** ********** ********** ********** */
procedure RenameUsers(p_ReturnMessage OUT VARCHAR2,
p_ReturnCode OUT NUMBER,
p_OldUserName IN VARCHAR2 default '',
p_NewUserName IN VARCHAR2 default '') as
pragma autonomous_transaction;
v_OldUserId number;
v_NewUserId number;
v_RowCount number;
v_Password varchar2(255);
v_EmailAddress applsys.fnd_user.email_address%TYPE;
v_URL varchar2(255) := 'http://%HOST%:8000/OA_HTML/AppsLocalLogin.jsp?cancelUrl=/OA_HTML/AppsLocalLogin.jsp&langCode=US&username=' ||
upper(p_NewUserName);
v_Host v$instance.HOST_NAME%TYPE;
procedure LogMessage(p_Text in varchar2) as
begin
apps.fnd_file.put_line(apps.fnd_file.log, p_Text);
end;
begin
LogMessage('START');
v_OldUserId := private_GetUser(p_OldUserName);
v_NewUserId := private_GetUser(p_NewUserName);
LogMessage('Renaming ' || p_OldUserName || '(' || to_char(v_OldUserId) ||
') to ' || p_NewUserName || '(' || to_char(v_NewUserId) || ')');
-- If the user details are blank raise an error
if (v_OldUserId = c_BLANK) or (Length(trim(p_NewUserName)) = 0) then
raise_application_error(-20005,
'ERROR: You cannot rename from/to a blank username!');
end if;
-- If the old user is not found then raise an error
if v_OldUserId = c_NOTFOUND then
raise_application_error(-20005,
'ERROR: The user "' || p_OldUsername ||
'" does not exist');
end if;
-- If the old user is not found then raise an error
if upper(p_OldUserName) = upper(p_NewUserName) then
raise_application_error(-20005,
'ERROR: Old and new usernames are the same!');
end if;
-- Get the email address, if there is no email address raise an error
select email_address
into v_EmailAddress
from applsys.fnd_user u
where u.user_id = v_OldUserId;
if v_EmailAddress is null then
raise_application_error(-20005,
'ERROR: Unable to update user "' ||
upper(p_OldUserName) ||
'" because they do not have a valid email address');
end if;
-- If the old user *is* found then raise an error
if v_NewUserId <> c_NOTFOUND then
raise_application_error(-20005,
'ERROR: The user "' || p_NewUserName ||
'" already exists');
end if;
-- Perform the rename
LogMessage('Updating user record (ID=' || to_char(v_OldUserId) || ')');
update applsys.fnd_user u
set u.user_name = upper(p_NewUserName)
where u.user_id = v_OldUserId;
v_RowCount := SQL%ROWCOUNT;
-- If anything other than a single record has been updated raise an error
if v_RowCount <> 1 then
raise_application_error(-20005,
'ERROR: Updating the user record has failed (' ||
to_char(v_RowCount) || ')');
end if;
-- Reset the users password. This is necessary because the encryption on the account includes
-- the Username in the key (so when we change the username we make it impossible for the user
-- to login - hence the resetting of the users password to a known value).
LogMessage('Initialising Oracle Application Suite (required for Password Change)');
execute immediate 'alter session set NLS_LANGUAGE = AMERICAN'; -- oddly we need these lines
execute immediate 'alter session set NLS_TERRITORY = AMERICA';
apps.fnd_global.apps_initialize(0, 20420, 1); -- switch to SYSADMIN under apps.
v_Password := private_passwordGenerator; -- this is a pretty poor generator, but is almost certainly secure enough for us.
-- Get the hostname (necessary to customise the URLs).
select lower(host_name) into v_Host from v$instance where rownum = 1;
LogMessage('Changing the users password');
fnd_user_pkg.UpdateUser(x_user_name => upper(p_NewUserName),
x_owner => 'SEED',
x_unencrypted_password => v_Password,
x_password_lifespan_days => '',
x_password_lifespan_accesses => '',
x_password_accesses_left => '');
-- Notify the user their account name and password have changed
XXXX_emailmanager.addEmail(p_From => v_EmailAddress,
p_To => v_EmailAddress,
p_Subject => private_emailSubjectTag ||
' User/Password Change Notification',
p_Body => '
Your username (which was "' ||
upper(p_OldUserName) ||
'") has been changed to "' ||
upper(p_NewUserName) ||
'". Your password has also been reset to "' ||
v_Password ||
'" (without the quotes).
Please click the Oracle link below to logon and change your password:
' ||
Replace(v_URL, '%HOST%', v_Host) ||
'
If you have any problems accessing the system please contact the IT Helpdesk.
');
XXXX_emailmanager.processMails;
commit;
p_ReturnMessage := 'OK';
p_ReturnCode := 0;
LogMessage('END');
exception
when others then
begin
p_ReturnMessage := '(' || TO_CHAR(SQLCODE) || ') ' || SQLERRM;
p_ReturnCode := 1;
logMessage('999 ERROR: (' || TO_CHAR(SQLCODE) || '): ' || SQLERRM);
XXXX_emailmanager.addEmail(p_From => 'errors@company.com',
p_To => 'errors@company.com',
p_Subject => private_emailSubjectTag ||
' Error',
p_Body => '999 ERROR: (' ||
TO_CHAR(SQLCODE) || '): ' ||
SQLERRM ||
'Please raise this issue with the helpdesk.');
XXXX_emailmanager.processMails;
rollback;
end;
end RenameUsers;
end XXXX_APPUSERMANAGER;
Now the bad news; this won't work (yes, I know that's a bit of a biggie). For copyright reasons several routines are missing from the package, the routine that sends the notification email to the user letting them know that their user name and password have been changed. In the packages these routines are referred to as:
XXXX_emailmanager.addEmail, and
XXXX_emailmanager.processMails
The former allows you to add an e-mail to a queue, the latter processes all emails in the queue (rather than waiting for an automated process to pick the new email up).
These are custom packages that sit on top of two packages from oracle; BASE64ENCODER and EMAILER. Unfortunately the links I have for these packages are no longer working but as this is pretty standard functionality that most people would want to do it shouldn't be too hard to find a replacement (or the packages themselves - try google).
One day it's my goal to write an installation script to get everything installed nicely, but that goal is not going to be met today (and as I'm updating this post 3 years later and I still haven't done it it's looking like "never" is the window of time this work will be dropping into!).
Wednesday, April 16, 2008
Oracle EBS: Tidying Up Partially Paid Expenses (in Internet Expenses)
As I may have mentioned previously I'm nearing the end of an implementation of Oracle Internet Expenses for a medium-sized pharmaceutical company in the UK. One of the issues that came up is the interesting way expenses are handled in the current (pre-OIE) Accounts Payable world.
At the moment, once a month, the company receives a statement from Barclaycard which lists the total amount outstanding on each of the company credit cards for all staff in Head Office and across the UK in the Sales Force. Because the credit cards are "company pay" and the payment itself is handled outside of AP (i.e. Barclaycard isn't setup as a vendor/supplier in Oracle) one of the AP staff sits down and enters each card total as a "prepayment" in AP against that individuals supplier record. This takes a couple of days.
The individual then accounts for their expenses in the usual way (it's an Excel spreadsheet in case you're wondering), returns all the details to Finance and then the AP staff enter the expenses on the system in the usual way. Because the prepayments have been entered the employee will only actually be paid for any expenses they incurred that haven't been paid for on their company Barclaycard.
There are plenty of loop holes (not to mention lots of reports checking to make sure that loop-holes we know about aren't being abused) and the entire process is very time consuming.
The reason I mention the way the system is being used is that it causes a problem with Internet Expenses (although it's pretty unique ... who else does it this way?). The problem is that in AP the expense is recorded as being "partially paid". This means that when the user logs into Internet Expenses the expense will still appear in their "active" list at the top (and they have no way of clearing it).
After working with Oracle they suggested a fairly simple piece of SQL that would be "fix" the open expenses;
This marks the expense as "paid" from the AP side. It's almost certainly best to make sure that you have a list of the affected invoices before you make any changes (just in case);
It's a very specific circumstance, maybe it will be useful to others!
At the moment, once a month, the company receives a statement from Barclaycard which lists the total amount outstanding on each of the company credit cards for all staff in Head Office and across the UK in the Sales Force. Because the credit cards are "company pay" and the payment itself is handled outside of AP (i.e. Barclaycard isn't setup as a vendor/supplier in Oracle) one of the AP staff sits down and enters each card total as a "prepayment" in AP against that individuals supplier record. This takes a couple of days.
The individual then accounts for their expenses in the usual way (it's an Excel spreadsheet in case you're wondering), returns all the details to Finance and then the AP staff enter the expenses on the system in the usual way. Because the prepayments have been entered the employee will only actually be paid for any expenses they incurred that haven't been paid for on their company Barclaycard.
There are plenty of loop holes (not to mention lots of reports checking to make sure that loop-holes we know about aren't being abused) and the entire process is very time consuming.
The reason I mention the way the system is being used is that it causes a problem with Internet Expenses (although it's pretty unique ... who else does it this way?). The problem is that in AP the expense is recorded as being "partially paid". This means that when the user logs into Internet Expenses the expense will still appear in their "active" list at the top (and they have no way of clearing it).
After working with Oracle they suggested a fairly simple piece of SQL that would be "fix" the open expenses;
update ap_invoices_all i set payment_status_flag = 'Y' where i.payment_status_flag <> 'Y' and i.source = 'XpenseXpress' and i.invoice_type_lookup_code = 'EXPENSE REPORT' and payment_status_flag = 'P';
This marks the expense as "paid" from the AP side. It's almost certainly best to make sure that you have a list of the affected invoices before you make any changes (just in case);
select i.invoice_id, i.invoice_num
from ap_invoices_all i
where i.payment_status_flag <> 'Y'
and i.source = 'XpenseXpress'
and i.invoice_type_lookup_code = 'EXPENSE REPORT'
and payment_status_flag = 'P';
It's a very specific circumstance, maybe it will be useful to others!
Wednesday, December 5, 2007
Oracle EBS: Scripting the Creation of Event-based Oracle Alerts in Oracle e-Business Suite
This was possibly one of the hardest things I've ever had to do in a long time. Not because it's technically challenging, but because there was just not enough information out there. If you search for Oracle Alerts you can see that using the FND_LOAD package there are a couple of example of how to migrate alerts from one server to another ... But not really anything on scripting their creation from scratch. You know you're in trouble when a Google search for some of the API's you're using returns no results. Naturally Oracles Metalink provided less than nothing, although someone called Margaret helped a great deal in relation to the SR I raised during this work!
Ok, a bit of background first. The aim here is to, using a PL/SQL script, create an Event-based Oracle Alert on a custom table within the Oracle database. The reasoning behind this is that I work in the Pharmaceutical industry and we use Oracle Manufacturing. Needless to say when you're dealing with drugs you can't have "random" changes being made to your database. Our environment is very tightly controlled.
At the moment we have a production server and five test/ development servers. We actually have an excellent cloning process that means if we need another copy from production it takes about a day to do.
The purpose behind this work was to enable us to write a script to implement an Oracle Alert that we could develop on our development instance, then run on our test instance and confirm it works before finally running it against production.
At the moment we are implementing Oracle Internet Expenses and are on our third refresh of our development server. so there will also be some time saving should we go onto our fourth or even fifth refresh!
Without this script we would have had to go in and make manual changes in the Oracle UI. This introduces human error and is very time consuming.
History lesson over, let's make a start.
First off let's assume we have the table CCL (stands for Credit Card Loader if anyone cares) which is created using the following script:
In order to register this table with Oracle applications (we're going to register it as part of payables - the same as Internet Expenses) use this script to perform the registration. The script uses Oracles AD_DD package (REGISTER_TABLE, REGISTER_COLUMN) to make APPS aware of the new table.
The key things for you to change to get this to work for you are both variables at the top of the file. v_TableName is the name of the table you wish to add, and v_AppShortName is the short name for the application (look in fnd_application_tl to get the application ID for your application, and then lookup the short code in fnd_application - Payables is SQLAP).
Whilst the script does remove existing registered columns if you change the delete a column from the table and then re-run the script you'll find yourself at the mercy of whatever it is Oracle does with the orphaned record ... Maybe delete? Maybe leave ... who knows? I for one have not tested that!
To keep this as simple as possible at this stage you need to create a concurrent request. It doesn't matter what it is just that you call it "CCL" and make sure it takes no parameters - you can do anything you like. Me personally I'd create one that writes a random record into a log table of some sort. Anyway, that part is down to you.
Next we use the ALR_ALERTS_PKG package to create an alert;
As you can see the huge bulk of values inserted are null, these are used mostly for periodic alerts rather than event-driven alerts. The values passed in are pretty self-explanatory - if you are lost for some of these values then you can always read this whole document, create the alert manually in Oracle (as the Alert Manager responsibility) and then look in the corresponding table and see what the values should be. Oracle have stuck with a fairly clear naming convention for these packages; ALR_ALERTS_PKG will manage data in the ALR_ALERTS table.
NB: The most likely causes of errors running this code are due to the table application short name and/or table being incorrect.
As far as I'm aware these next steps can be carried out in any order (but I have only tested them in the order they're listed here).
Setting up Alert Installations
This uses the following API;
This is very important, it took me quite a while to work out why my alerts weren't firing and it ended up being due to not having added the APPS user as an Alert Installation. In the example code above where it says "&XXXX" you need to specify the group name for the APPS login.
Now this ONLY work if you have the APPS environment initialised for the login. You can do this by executing the command;
You'll need to specify your own values for user, responsibility id and responsibility application id. if everything else looks ok (and you can see the Check Event Alert Concurrent Request starting but not your request) then this is likely the cause of your problem.
Next use the ALR_ACTIONS_PKG package to create an action. This will be to run the concurrent request we configured earlier.
As you can see all the options are here, again it might be easier if you created your alert in the database and then queried the tables directly to find the necessary values for all these API calls. the key things to note are that action type "C" is Concurrent Request and action level type "D" is Detail (as you would see in the GUI).
Next you need to create an action set for your action. This is done using the ALR_ACTION_SETS_PKG package;
Now you have an alert, an action and an action set you can configure the outputs for all three together using these three API calls. Note that in the SQL for the alert I made use of a PROCESSID output value, these calls correctly register this with the system.
Next now the outputs are configured we can setup the action set members;
Now this is the alert setup and ready to go - you can go into the Oracle GUI and check and everything should be there. Unfortunately it's possible for some of these calls to "silently" fail so it's necessary to perform this basic validation.
The final thing that's missing is the system-generated trigger on the CCL table that will fire off the alert. This API uses a different format than all the others so you need to put together a block of code;
If you query the database using PL/SQL Developer, TOAD, etc then you'll see the trigger is now available on the table.
A quick test;
If you log in as a System Administrator and look at concurrent requests you should see the CCL (Check Event Alert) start up and then a few second later your concurrent request should start to run.
Should you fancy looking at my script it's available here.
Hope this saves you the 2 days it took me to get this working!
NOTE: You may freely take my scripts and do whatever you want with them, so long as you don't try and pin anything they do (or don't do) on me.
Ok, a bit of background first. The aim here is to, using a PL/SQL script, create an Event-based Oracle Alert on a custom table within the Oracle database. The reasoning behind this is that I work in the Pharmaceutical industry and we use Oracle Manufacturing. Needless to say when you're dealing with drugs you can't have "random" changes being made to your database. Our environment is very tightly controlled.
At the moment we have a production server and five test/ development servers. We actually have an excellent cloning process that means if we need another copy from production it takes about a day to do.
The purpose behind this work was to enable us to write a script to implement an Oracle Alert that we could develop on our development instance, then run on our test instance and confirm it works before finally running it against production.
At the moment we are implementing Oracle Internet Expenses and are on our third refresh of our development server. so there will also be some time saving should we go onto our fourth or even fifth refresh!
Without this script we would have had to go in and make manual changes in the Oracle UI. This introduces human error and is very time consuming.
History lesson over, let's make a start.
First off let's assume we have the table CCL (stands for Credit Card Loader if anyone cares) which is created using the following script:
create table CCL ( PROCESS_ID NUMBER not null, LINE_ID NUMBER not null, TEXT VARCHAR2(2048) not null, VALID_BOO VARCHAR2(1) default 'T' );This receives a credit card transaction in a Barclaycard feed (it's part of our i-Expenses implementation) - each line in the file is inserted into this table. A trigger populates the VALID_BOO with either T or F depending on the data in TEXT.
In order to register this table with Oracle applications (we're going to register it as part of payables - the same as Internet Expenses) use this script to perform the registration. The script uses Oracles AD_DD package (REGISTER_TABLE, REGISTER_COLUMN) to make APPS aware of the new table.
The key things for you to change to get this to work for you are both variables at the top of the file. v_TableName is the name of the table you wish to add, and v_AppShortName is the short name for the application (look in fnd_application_tl to get the application ID for your application, and then lookup the short code in fnd_application - Payables is SQLAP).
Whilst the script does remove existing registered columns if you change the delete a column from the table and then re-run the script you'll find yourself at the mercy of whatever it is Oracle does with the orphaned record ... Maybe delete? Maybe leave ... who knows? I for one have not tested that!
To keep this as simple as possible at this stage you need to create a concurrent request. It doesn't matter what it is just that you call it "CCL" and make sure it takes no parameters - you can do anything you like. Me personally I'd create one that writes a random record into a log table of some sort. Anyway, that part is down to you.
Next we use the ALR_ALERTS_PKG package to create an alert;
alr_alerts_pkg.load_row(x_application_short_name => 'SQLAP',
x_alert_name => 'CCL_NEW',
x_owner => null,
x_alert_condition_type => 'E',
x_enabled_flag => 'Y',
x_start_date_active => sysdate,
x_end_date_active => null,
x_table_application_short_name => 'SQLAP',
x_description => 'Starts the second credit card loading process after a successful load',
x_frequency_type => 'O',
x_weekly_check_day => null,
x_monthly_check_day_num => null,
x_days_between_checks => null,
x_check_begin_date => null,
x_date_last_checked => null,
x_insert_flag => 'Y',
x_update_flag => 'N',
x_delete_flag => null,
x_maintain_history_days => 0,
x_check_time => null,
x_check_start_time => null,
x_check_end_time => null,
x_seconds_between_checks => null,
x_check_once_daily_flag => null,
x_sql_statement_text => 'select process_id into &PROCESSID from ccl where text like ''TRLR%'' and rowid = :ROWID',
x_one_time_only_flag => null,
x_table_name => 'CCL',
x_last_update_date => null,
x_custom_mode => null);
Now this calls the LOAD_ROW package (rather than INSERT_ROW) because if the row already exists this will update it.As you can see the huge bulk of values inserted are null, these are used mostly for periodic alerts rather than event-driven alerts. The values passed in are pretty self-explanatory - if you are lost for some of these values then you can always read this whole document, create the alert manually in Oracle (as the Alert Manager responsibility) and then look in the corresponding table and see what the values should be. Oracle have stuck with a fairly clear naming convention for these packages; ALR_ALERTS_PKG will manage data in the ALR_ALERTS table.
NB: The most likely causes of errors running this code are due to the table application short name and/or table being incorrect.
As far as I'm aware these next steps can be carried out in any order (but I have only tested them in the order they're listed here).
Setting up Alert Installations
This uses the following API;
alr_alert_installations_pkg.load_row(x_application_short_name => 'SQLAP',
x_alert_name => 'CCL_NEW',
x_oracle_username => 'APPS',
x_data_group_name => '&XXXX',
x_owner => null,
x_enabled_flag => 'Y',
x_last_update_date => null,
x_custom_mode => null);
This is very important, it took me quite a while to work out why my alerts weren't firing and it ended up being due to not having added the APPS user as an Alert Installation. In the example code above where it says "&XXXX" you need to specify the group name for the APPS login.
Now this ONLY work if you have the APPS environment initialised for the login. You can do this by executing the command;
APPS.FND_GLOBAL.APPS_INITIALIZE(user_id => 102,
resp_id => 2304,
resp_appl_id => 17);
You'll need to specify your own values for user, responsibility id and responsibility application id. if everything else looks ok (and you can see the Check Event Alert Concurrent Request starting but not your request) then this is likely the cause of your problem.
Next use the ALR_ACTIONS_PKG package to create an action. This will be to run the concurrent request we configured earlier.
alr_actions_pkg.load_row(x_application_short_name => 'SQLAP',
x_alert_name => 'CCL_NEW',
x_action_name => 'CCL_CONC_REQ',
x_action_end_date_active => null,
x_owner => null,
x_action_type => 'C',
x_enabled_flag => 'Y',
x_description => 'Run a concurrent request',
x_action_level_type => 'D',
x_date_last_executed => null,
x_file_name => null,
x_argument_string => null,
x_program_application_name => 'SQLAP',
x_concurrent_program_name => 'CCL',
x_list_application_name => null,
x_list_name => null,
x_to_recipients => null,
x_cc_recipients => null,
x_bcc_recipients => null,
x_print_recipients => null,
x_printer => null,
x_subject => null,
x_reply_to => null,
x_response_set_name => null,
x_follow_up_after_days => null,
x_column_wrap_flag => 'Y',
x_maximum_summary_message => null,
x_body => null,
x_version_number => 1,
x_last_update_date => null,
x_custom_mode => null);
As you can see all the options are here, again it might be easier if you created your alert in the database and then queried the tables directly to find the necessary values for all these API calls. the key things to note are that action type "C" is Concurrent Request and action level type "D" is Detail (as you would see in the GUI).
Next you need to create an action set for your action. This is done using the ALR_ACTION_SETS_PKG package;
alr_action_sets_pkg.load_row(x_application_short_name => 'SQLAP',
x_alert_name => 'CCL_NEW',
x_name => 'CCL_REQSET',
x_owner => null,
x_end_date_active => null,
x_enabled_flag => 'Y',
x_recipients_view_only_flag => 'N',
x_description => 'Request set for CCL_NEW',
x_suppress_flag => 'N',
x_suppress_days => null,
x_sequence => 1,
x_last_update_date => null,
x_custom_mode => null);
Now you have an alert, an action and an action set you can configure the outputs for all three together using these three API calls. Note that in the SQL for the alert I made use of a PROCESSID output value, these calls correctly register this with the system.
alr_action_outputs_pkg.load_row(x_application_short_name => 'SQLAP',
x_alert_name => 'CCL_NEW',
x_action_name => 'CCL_CONC_REQ',
x_action_end_date_active => null,
x_action_out_name => 'PROCESSID',
x_owner => null,
x_critical_flag => 'N',
x_end_date_active => null,
x_last_update_date => null,
x_custom_mode => null);
alr_alert_outputs_pkg.load_row(x_application_short_name => 'SQLAP',
x_alert_name => 'CCL_NEW',
x_name => 'PROCESSID',
x_owner => null,
x_sequence => 1,
x_enabled_flag => 'Y',
x_start_date_active => sysdate,
x_end_date_active => null,
x_title => 'PROCESSID',
x_detail_max_len => null,
x_summary_max_len => null,
x_default_suppress_flag => 'Y',
x_format_mask => null,
x_last_update_date => null,
x_custom_mode => null);
alr_action_set_outputs_pkg.load_row(x_application_short_name => 'SQLAP',
x_alert_name => 'CCL_NEW',
x_name => 'CCL_REQSET',
x_action_set_output_name => 'PROCESSID',
x_owner => null,
x_sequence => 1,
x_suppress_flag => 'Y',
x_last_update_date => null,
x_custom_mode => null);
Next now the outputs are configured we can setup the action set members;
alr_action_set_members_pkg.load_row(x_application_short_name => 'SQLAP',
x_alert_name => 'CCL_NEW',
x_name => 'CCL_REQSET',
x_owner => null,
x_action_name => 'CCL_CONC_REQ',
x_group_name => null,
x_group_type => null,
x_sequence => 1,
x_end_date_active => null,
x_enabled_flag => 'Y',
x_summary_threshold => null,
x_abort_flag => 'A',
x_error_action_sequence => null,
x_last_update_date => null,
x_custom_mode => null);
Now this is the alert setup and ready to go - you can go into the Oracle GUI and check and everything should be there. Unfortunately it's possible for some of these calls to "silently" fail so it's necessary to perform this basic validation.
The final thing that's missing is the system-generated trigger on the CCL table that will fire off the alert. This API uses a different format than all the others so you need to put together a block of code;
declare
cursor c_AlertDetails is
select a.application_id,
a.alert_id,
a.table_application_id,
a.table_id
from applsys.alr_alerts a
where a.alert_name = 'CCL_NEW';
begin
dbms_output.put_line('Creating a tigger on CCL for the alert (on insert) ...');
for v_Alert in c_AlertDetails loop
alr_dbtrigger.create_event_db_trigger(appl_id => v_Alert.application_id,
alr_id => v_Alert.alert_id,
tbl_applid => v_Alert.table_application_id,
tbl_name => 'CCL',
oid => null,
insert_flag => 'Y',
update_flag => 'N',
delete_flag => 'N',
is_enable => 'N');
end loop;
dbms_output.put_line('... Done');
end;
If you query the database using PL/SQL Developer, TOAD, etc then you'll see the trigger is now available on the table.
A quick test;
begin
-- Test statements here
APPS.FND_GLOBAL.APPS_INITIALIZE(user_id => 102,
resp_id => 2304,
resp_appl_id => 17);
delete from ccl where process_id = 41 and line_id = 3804;
insert into ccl (process_id, line_id,text)
values (41, 3804,
'TRLR{');
commit;
end;
If you log in as a System Administrator and look at concurrent requests you should see the CCL (Check Event Alert) start up and then a few second later your concurrent request should start to run.
Should you fancy looking at my script it's available here.
Hope this saves you the 2 days it took me to get this working!
NOTE: You may freely take my scripts and do whatever you want with them, so long as you don't try and pin anything they do (or don't do) on me.
Monday, December 3, 2007
Oracle EBS: How to Create Oracle Concurrent Requests using PL/SQL Scripts (Using FND_PROGRAM)
This was driving me mad. At the moment we tend to configure the Oracle e-Business Suite through the standard Java interface. The problem is that with big changes (like the i-Expenses implementation I'm currently working on) are first done on a development server, then on a test server, and finally on a live server. Each time the change has to be repeated and validated. This takes an age.
It turns out that Oracle has a perfectly serviceable API for this, the FND_PROGRAM package.
Running the script below setup a concurrent program, a request, and configured it so that it could only be running once at any given time:
This script sets up a executable using FND_PROGRAM.EXECUTABLE, then uses FND_PROGRAM.REGISTER to setup a new concurrent request, and finally FND_PROGRAM.INCOMPATIBILITY to tell the system that only one copy of the concurrent request should be run at any given time.
The begin ... end blocks at the start are just there to delete any data already configured from previous runs of the script (useful in a test environment). The reason for putting them in exception blocks is that the first time you run you would be passing in invalid parameters (the program short code) because it doesn't exist yet.
In case you were wondering this was a concurrent request that would check to see if the user had any new credit card transactions following a load and send them a nice e-mail reminding them to fill in an expense claim (via Oracle i-Expenses).
It turns out that Oracle has a perfectly serviceable API for this, the FND_PROGRAM package.
Running the script below setup a concurrent program, a request, and configured it so that it could only be running once at any given time:
-- Setting up Notify Users of New Credit Card Transactions
begin
fnd_program.delete_incompatibility(program_short_name => 'NUNCCT',
application => 'Payables',
inc_prog_short_name => 'NUNCCT',
inc_prog_application => 'Payables');
exception
when others then
null;
end;
begin
fnd_program.delete_program(program_short_name => 'NUNCCT',
application => 'Payables');
exception
when others then
null;
end;
begin
fnd_program.delete_executable(executable_short_name => 'NUNCCT',
application => 'Payables');
exception
when others then
null;
end;
fnd_program.executable(executable => 'Notify Users of New Credit Card Transactions',
application => 'Payables',
short_name => 'NUNCCT',
description => 'Notify Users of New Credit Card Transactions',
execution_method => 'PL/SQL Stored Procedure',
execution_file_name => 'APPS.CCL_MANAGER.NotifyUsersOfNewTransactions');
fnd_program.register(program => 'Notify Users of New Credit Card Transactions',
application => 'Payables',
enabled => 'Y',
short_name => 'NUNCCT',
description => 'Notify Users of New Credit Card Transactions',
executable_short_name => 'NUNCCT',
executable_application => 'Payables',
print => 'N',
use_in_srs => 'Y');
fnd_program.incompatibility(program_short_name => 'NUNCCT',
application => 'Payables',
inc_prog_short_name => 'NUNCCT',
inc_prog_application => 'Payables',
scope => 'Set',
inc_type => 'G');
commit;
This script sets up a executable using FND_PROGRAM.EXECUTABLE, then uses FND_PROGRAM.REGISTER to setup a new concurrent request, and finally FND_PROGRAM.INCOMPATIBILITY to tell the system that only one copy of the concurrent request should be run at any given time.
The begin ... end blocks at the start are just there to delete any data already configured from previous runs of the script (useful in a test environment). The reason for putting them in exception blocks is that the first time you run you would be passing in invalid parameters (the program short code) because it doesn't exist yet.
In case you were wondering this was a concurrent request that would check to see if the user had any new credit card transactions following a load and send them a nice e-mail reminding them to fill in an expense claim (via Oracle i-Expenses).
Subscribe to:
Posts (Atom)