Oracle EBS API to create a Phone Number (contacts) in R12 HZ_CONTACT_POINT_V2PUB.CREATE_PHONE_CONTACT_POINT

Oracle EBS API to create a Phone Number (contacts) in R12 HZ_CONTACT_POINT_V2PUB.CREATE_PHONE_CONTACT_POINT

This API is relatively easy to work with.  It is a public API wrapper on   HZ_CONTACT_POINT_V2PUB.CREATE_CONTACT_POINT, which allows you to load phones, emails, websites, edi info, etc.   The API  we are looking at here, HZ_CONTACT_POINT_V2PUB.CREATE_PHONE_CONTACT_POINT, only creates contact phones.

I'll start with a sample script here and then explain the tricky part of working with this API down below:

Sample script for: HZ_CONTACT_POINT_V2PUB.CREATE_PHONE_CONTACT_POINT

DECLARE
    p_contact_point_rec HZ_CONTACT_POINT_V2PUB.CONTACT_POINT_REC_TYPE;
    p_phone_rec         HZ_CONTACT_POINT_V2PUB.PHONE_REC_TYPE;
    x_return_status     VARCHAR2(2000);
    x_msg_count         NUMBER;
    x_msg_data          VARCHAR2(2000);
    x_contact_point_id  NUMBER;

BEGIN
    -- Initialize the environment
  
    /*
        You probably don't need these for this API (my testing didn't require
        them and there is no org_id field in the hz_contact_points table) but
        here they are just in case:
  
        mo_global.init('AR');

        fnd_global.apps_initialize ( user_id      => 1234
                                    ,resp_id      => 50559
                                    ,resp_appl_id => 222);

        mo_global.set_policy_context('S',204);

    */

    -- Mandatory parameters
    p_contact_point_rec.contact_point_type    := 'PHONE';
    p_contact_point_rec.owner_table_name      := 'HZ_PARTIES';
    p_contact_point_rec.owner_table_id        := 1148;
    p_contact_point_rec.primary_flag          := 'Y';
    p_contact_point_rec.contact_point_purpose := 'BUSINESS';
    p_contact_point_rec.created_by_module     := 'BO_API';

    -- Non Mandatory Parameters
    p_phone_rec.phone_area_code               :=  NULL;
    p_phone_rec.phone_country_code            := '1';
    p_phone_rec.phone_number                  := '212-555-1212';
    p_phone_rec.phone_line_type               := 'GEN';
    p_phone_rec.phone_extension               := '';  
  
    /*
        Notes:
      
        1) The 'owner_table_name' can be other tables like hz_party_sites
      
        2) The 'owner_table_id' is the primary key for the record in the table
           you want to assign this contact to.  In this example, this would be
           the party_id of a party in HZ_Parties
    */
  
    DBMS_OUTPUT.PUT_LINE('-----------');
    DBMS_OUTPUT.PUT_LINE('Calling API');

    HZ_CONTACT_POINT_V2PUB.CREATE_PHONE_CONTACT_POINT(
                 p_init_msg_list      => FND_API.G_TRUE,
                 p_contact_point_rec  => p_contact_point_rec,
                 p_phone_rec          => p_phone_rec,
                 x_contact_point_id   => x_contact_point_id,
                 x_return_status      => x_return_status,
                 x_msg_count          => x_msg_count,
                 x_msg_data           => x_msg_data
                 );


    IF x_return_status = fnd_api.g_ret_sts_success
      THEN
        DBMS_OUTPUT.PUT_LINE('Success!');
        DBMS_OUTPUT.PUT_LINE('x_contact_point_id: '||x_contact_point_id);

      ELSE
          
        DBMS_OUTPUT.put_line('Failure...   Msgs: ');
          
        IF x_msg_count = 1
          THEN
            DBMS_OUTPUT.put_line('1) ' || x_msg_data);
                  
         ELSE
                                      
          FOR i IN 1 .. x_msg_count
            LOOP
              x_msg_data := fnd_msg_pub.get( p_msg_index => i, p_encoded => 'F');
              dbms_output.put_line( i|| ') '|| x_msg_data);
            END LOOP;
                                 
        END IF;
                             
    END IF;
  
END;

Now the trick to this is that you have to understand the parameters owner_table_name and owner_table_id.  The owner_table_name is the table which has the data you want to associate with this contact phone.  Typical choices are HZ_Parties or HZ_Party_Sites.  The party_id parameter is the primary ID in that table, so if the owner_table_name = 'HZ_Parties then the owner_table_id is the party_id field in HZ_Parties.  If the owner_table_name = 'HZ_Party_Sites' then the  owner_table_id is the party_site_id field in HZ_Party_Sites.  It's not super-intuitive but that's the way the table that stores contact info (hz_contact_points) works.

Knowing this, hopefully you will be able to use this API easily.

NLS_LANGUAGE what a pain... Oracle EBS issue

NLS_LANGUAGE what a pain... Oracle EBS issue

I have a customer who has a report that works great in the U.S. but when they rolled the report out to users in the U.K. the report returned no values.  At first blush, everything seemed the same and we expected that the report should run.

The only difference we could find was that the UK Oracle client was setup with an nls_language of 'ENGLISH' while in the US it was setup with an nls_language of 'AMERICAN' - but even so, we didn't see why that would make a difference, since none of the report parameter values would be any different using ENGLISH vs. AMERICAN.  I had originally though the issue might be the date but it turned out that both systems were using nls_date format.

The developer of the report started from scratch, adding each table and join condition one at a time until he found the one which resulted in the query returning no values.  It was an Oracle EBS table hr_operating_units.  Well, actually, it turned out that hr_operating_units is a view and not a table and when you look at the view code:

SELECT   o.business_group_id,
         o.organization_id,
         otl.name,
         o.date_from,
         o.date_to,
         o3.org_information5,
         o3.org_information3,
         o3.org_information2,
         o3.org_information6
FROM     hr_all_organization_units o,
         hr_all_organization_units_tl otl,
         hr_organization_information o2,
         hr_organization_information o3
WHERE    o.organization_id = o2.organization_id
     AND o.organization_id = o3.organization_id
     AND o2.org_information_context || '' = 'CLASS'
     AND o3.org_information_context = 'Operating Unit Information'
     AND o2.org_information1 = 'OPERATING_UNIT'
     AND o2.org_information2 = 'Y'
     AND o.organization_id = otl.organization_id
     AND otl.language = userenv ('LANG')

What you find is the very interesting last line... the view is filtering based on the user's session's language setting.  Set to 'ENGLISH' there are no matching records, set to 'AMERICAN' there are.  We confirmed this as follows:

ALTER SESSION SET NLS_LANGUAGE = AMERICAN;

SELECT COUNT(*) FROM hr_operating_units;
 

61 rows
 

ALTER SESSION SET NLS_LANGUAGE = ENGLISH;

SELECT COUNT(*) FROM hr_operating_units;
 

0 rows
 

I hadn't expected this and it took some methodical troubleshooting to resolve.  If you are getting different results running queries on different PCs, check the language settings and check the query - particularly if you are using views that you didn't create!!!

Hope that helps.

Where do I find info about Oracle EBS Public APIs?

Where do I find info about Oracle EBS Public APIs?

This is a question that I get multiple times each week in my role at API Wizard (a software product that leverages Oracle APIs).  

Oracle has make a huge investment, starting in Release 11 and continuing into each version of R12, in public APIs which allow you to perform nearly all of the same business processes that you can do with the Oracle forms.  You can create and update master data (customers, vendors, people, items, etc), transactional data (invoices, payments, orders, shipments, material transactions, etc), and configuration data (banks, value set values, etc).

Sounds great... and it is.  

But the question remains; how do you get a comprehensive list of available public APIs for Oracle EBS?

At a high-level, there are 4 main sources:

1) The Oracle Integration Repository
2) Oracle documenation
3) Metalink / My Oracle Support 
4) Web search

Let's break these down:

1) The Oracle Integration Repository

This is a resource that is part of every R12 system.  You access it using the seeded 'Integrated SOA Gateway' responsibility and choose the menu item 'Integration Repository'.  The Integration Repository contains about 70% of the Oracle APIs for whatever version of Oracle you're on, which doesn't sound great - but actually it's a huge accomplishment for Oracle as they have never had this information consolidated in one place before the integration repository. 

The Integration Repository provides a menu on the left broken out by module family and you can drill down to get to specific modules and then the public APIs or open interfaces available in those modules.  This is a great starting place -- but there are a few issues:

a) As mentioned above, if you don't find the API you're looking for here, it doesn't mean it doesn't exist, it just means it wasn't cataloged in the Integration Repository.
b) The Integration Repository in your environment will only list the APIs for the version of Oracle that you are running.  For example, if you are running 12.1.2 you'll see the APIs for version 12.1.2 but if you want to know if there is an API introduced in 12.1.3 (new APIs are added with each patchset), you can't determine that in your Integration Respository.
c) This is only available in R12.  The integration repository is not available in your R11 system.
d) The integration repository identifies the API and provides some information about the API parameters and a description of business process but it is not very detailed.

Oracle does maintain a version of the integration repository on their website at irep.oracle.com.  It used to contain R11 info and then was upgraded to R12 info but has been offline for the last few weeks.  It would be great if they would get that back online and provide the ability to search by version...  I am not holding my breath.

2) Oracle documenation (docs.oracle.com)

Oracle generates tons of documentation.  Each module mimimally has a user guide, an implementation guide, and a technical reference manual.  Some modules also have documents focusing solely on APIs and open interfaces, others embed this information into their other documents, and some don't document them at all.

It can be tedious work going through Oracle's documentation but worth when you find the guide that you need.  For the APIs and open interfaces which are documented, Oracle often provides sample scripts and talks about important concepts like environment initialization.

3) Metalink / My Oracle Support (support.oracle.com)

These days, good searching skills are often worth more than concrete knowledge.  There's a lot of information on metalink both in Oracle's notes/documents but also in Oracle forums, all of which you can search in one place.  I've found many an API this way.  Sometimes I get better results on google (see next bullet point) but sometimes Metalink is best.  

One thing to note is that if you have done your due dilligence but can't identfy an public API or open interface, you can raise an SR with Oracle and ask them if one exists.  Not only can they help you find it but if there is not, then can tell you if anyone has made an enhancement request for that API and you can ask them to add you to the list of customers requesting it.  That's one factor in how Oracle prioritizes its development initiatives.  

4) Web search

Not surprisingly, sometimes you get better information doing a web seach on google, bing, etc.   Actually, I often start here because if there are results I find them very quickly.  There are many forum questions (is there an Oracle EBS API for .....?) and there are many good blogs which describe working with these APIS.

Oracle Projects API (PA_PROJECT_PUB) and Function Security

Oracle Projects API (PA_PROJECT_PUB) and Function Security

A customer wanted to work with project module APIs to perform standard project tasks (create and update projects, create and update project tasks, add key members to projects, etc).

Oracle provides a number of APIs within the pa_project_pub API for just that purpose, those include:

pa_project_pub.create_project
pa_project_pub.update_project
pa_project_pub.create_task
pa_project_pub.update_task  

These APIs are actually pretty straightforward and when I tested them in my system, they worked great.  When my customer ran these APIs, he received the following error:

PA_PA_FUNCTION_SECURITY_ENFORCED_--Your current responsibility does not have access to this function

We were running the API in API Wizard (an Excel software product which works dynamically with Oracle EBS APIs) but we also tested at the command line and received the same issue.  In both cases, we were running following initialization:

    fnd_global.apps_initialize (
        user_id         => 19616,
        resp_id         => 52896,
        resp_appl_id    => 275
        );

This responsibility (resp_id 52896) had the ability to create and update project tasks via the Oracle forms.  It took as a while to figure this out but the issue was that the customer had created a custom menu for projects that did not include the Activity Management Gateway functions, such as:

PA_PM_CREATE_PROJECT - Activity Management Gateway:Create Project
PA_PM_UPDATE_PROJECT - Activity Management Gateway:Update Project
PA_PM_ADD_TASK       - Activity Management Gateway:Add Task 
PA_PM_UPDATE_TASK    - Activity Management Gateway:Update Task

So we went to the Menus form (System Administrator > Application > Menu) and added them.  Viola!  Everything worked.

I verified that this is the same in R11 and R12.  

While our issue was due to custom responsibility with a custom menu, not all of the delivered responsibilities using the standard menus have access to these functions, for example the Project Manager Super User does have access by default while the Project Costing Super User does not.  So you can definitely run into this issue using a seeded responsibility.  The key is making sure that the functions listed above are available.

Another thing to note is that if you use a responsibility based on a menu that has these functions, you must also ensure that there none of these are part of a function exclusion for the target responsibility.

A last note, Projects is the only module I have seen so far where access to be able to use APIs is restricted by a function assigned (or in this case unassigned or excluded) to a menu.  If you've run into this in any other modules, please let me know!

You can use API Wizard to create and update your projects, tasks, and more!

API Wizard has pre-built Loaders for common projects activities, including creating and updating projects and tasks.  This ready-to-go solution will save you from investing time and development effort into a custom solution.  Here is are a couple of screenshots of API Wizard Project Loaders:

Create Full Project:  

This Loader allows you to create Projects, tasks, key members, structure details, and more all in one Excel worksheet:

Add or Update Project Tasks:

This Loader allows you to easily add or update project tasks.  This 'Task-Only' Loader is perfect for projects that have lots of tasks and you need an easy way to manage them all.

Project Assets and Assignments:


This Loader allows you to easily create your Project Asssets and Assignments.  

Project Budgets:

This Loader allows you to easily create your project budgets.  For those organizations that budget at the task or sub-task level this can be a time-consuming process.  With API Wizard, it's simple and efficient.

If you want to build your own Projects solution, good luck!  If you want to use API Wizard for your Project creation and maintenance, contact us at info@api-wizard.com

See more about what API Wizard can do at:

www.api-wizard.com