Creating Project Tasks with pa_project_pub - Issue when parent task created in form vs. create with API

The Oracle Projects public APIs pa_project_pub.create_project and pa_project_pub.update_project have the ability to, among other things, load multiple tasks in a single API call, including tasks with parent-child relationships.  For example, you can create 10 tasks, each with 5 sub-tasks, and those sub-tasks might have 3 sub-sub-tasks, etc.  When loading multiple tasks in a single API call, the 'task_id' of the tasks are not yet generated, so in order to allow the creation of parent-child relationships, the projects API has a mandatory parameter called 'pm_task_reference'.  When you want to create a task as the child of another task, you can pass the pm_task_reference of the parent into a second parameter called 'pm_PARENT_task_reference' for the child.  This works well.

However, when a task is entered through the forms, the pm_task_reference is not populated, so you cannot use that value in the pm_PARENT_task_reference parameter to create a child using the API.  In this case, you must use the another parameter, 'pa_PARENT_task_id', which is the task_id of the parent task.  In this case, since the parent task has previously been created; the task_id exists and can be used.  

As it turns out, the task_id (passed to the pa_PARENT_task_id parameter) can be used for both parent tasks created through the API and for parent tasks created through the forms as long as the parent task already exist.  So if you are adding sub-tasks to existing tasks, you should use the pa_PARENT_task_id.

This is explained in the Oracle Note: PA_PROJECT_PUB.Add_task Errors - Parent Tasks Must Be Specified Before The Child Task (Doc ID 1108470.1)

As an FYI, tasks can also be created and updated using the create_task, add_task, load_task, update_task, delete_task procedures - these use scalar parameters rather than the table-types used in create_project and update_project.  

Finally, Oracle has released a Physical Inventory API: INV_PHY_INV_PUB

At long last, EBS customers can enter physical inventory counts and adjustment approvals through a new public API.

Phyiscal Inventory counts and approvals are one of the most tedious processes an organization can undertake.  After spending countless hours counting what feels like and endless inventory, someone (or multiple people) must enter those counts into Oracle.  On top of that, someone has to approve the adjustments (where the physical inventory count is above or below the system value * the tolerance).

Trying to enter tag counts in the Oracle forms is an exercise in futility, as it form is way to inefficient to process even a small inventory, let alone the large inventories that must EBS customers will have.

Finally, after all these years, Oracle has released the API: INV_PHY_INV_PUB which can be used to programmatically perform tag counts and approvals.  This is available in version 12.2.5 and later (sorry to those folks on earlier versions).

Here are the details:

1) Enter Tag Counts: 

inv_phy_inv_pub.update_tags

This API allows you to enter your tag counts.  Typical input parameters are:

Org:                   organization_id

Physical Inv Name:     physical_inventory_id

Tag Number:            adjustment_id

Item Number:           inventory_item_id

Subinventory:          subinventory

Tag UOM:               tag_uom

Count Qty:             count_quantity

Action:                action (default to U, for update)

2) Approve Adjustments:

inv_phy_inv_pub.approve_adjustment

This API allows you to approve adjustments.  Here the key is to pass the

Org:                   organization_id
Physical Inv Name:     physical_inventory_id
Tag Number:            adjustment_id
Approval Status:       approval_status
Approved by:           approved_by_employee_id

These two procedures work well and can make processing your physical inventory much easier.  Interestingly, there is also a Create_Tags procedure but Oracle says that the tags must be created using the concurrent process in the Oracle Forms.  For more information, please read Oracle Support document: 

How To Use The Physical Inventory API In 12.2.5 (Doc ID 2161869.1)

You can call these APIs directly or you can perform your Physical Inventory Counts and Approvals in API Wizard

API Wizard has prebuilt Loaders for Physical Inventory Counts and Adjustment Approvals and its Excel-based interface makes it incredibly easy to process huge numbers of records at one time.  You can purchase and deploy API Wizard in 15 minutes and start entering your counts and adjustment approvals faster than it would take you (or your IT team) to get up to speed on this API.  Plus, API Wizard's battle-tested, sophisticated interface makes data entry and update a breeze!  Here are some screenshots of the Tag Count and Adjustment Approval Loaders.

API Wizard Physical Inventory Count Loader

API Wizard Physical Inventory Adjustment Approval Loader:

In summary, it's a great thing for the EBS community that Oracle has released a public API for inventory counts and adjustment approvals.  If you want to build your own solution, good luck!  If you want to use API Wizard for your inventory counts and adjustment approvals, contact us at info@api-wizard.com.

How API Wizard benefits increase in Year 2

New customers generally have specific pain points that they want to eliminate using API Wizard.  Those pain points may be in one specific area, like AP Invoices, there may be several across a functional area, like a set of manufacturing processes, or there may be usage across multiple functional areas.  Whatever the case is, most new API Wizard customers have a clear understanding of the problem(s) they wish to solve.  After purchasing API Wizard, those processes are quickly addressed and the old and tedious Oracle forms are set aside in favor of the newer and more efficient API Wizard solution.  Generally that's the win needed to have a very favorable ROI on the API wizard purchase.

But what happens after a customer has been using API Wizard for a year?  The first thing that generally happens is that API Wizard becomes second nature to the user(s) of those initial processes and they look to replace their other, tedious Oracle processes with an additional API Wizard process.  For instance, an inventory manager using API Wizard to create new items and update existing items may want to start using it for Item Category maintenance, or BOM creation and update, or reporting on on-hand quantity.  With API Wizard's user-based pricing model, there is no additional cost when users do more with API Wizard, so that's a big win for the customer.  The next thing that frequently happens is that co-workers of the API Wizard users start to recognize that their own processes could be optimized using API Wizard and this generally is the driver behind purchasing additional API Wizard licenses and getting more benefit out of the tool.  Once an organization has seen success in its first few processes, the business case for adding additional processes becomes self-evident.  The third thing that happens is that the organizational knowledge and expertise in API Wizard develops to the point where they can leverage API Wizard not only for standard EBS processes but also to manage their custom processes as well.  In almost all cases, an API Wizard data entry/update solution for a custom process has a huge return on investment.  We see these a lot with custom tables and APIs, even those where an Apex form has been previously developed for daily usage.

Organizations with strong internal capabilities often do nearly all of this on their own.  Organizations with limited internal capabilities or very little bandwidth often ask the API Wizard team or third-party providers to help them add additional processes.  

As time goes on and more people in the organization become aware of how API Wizard can streamline EBS and custom processes, usage grows, and the organization improves processes and saves time and money.  


To learn more about API Wizard, visit us at:

www.api-wizard.com

End of Life for Oracle EBS?

The reports of EBS' demise have been greatly exaggerated!

As Oracle has been pushing all things cloud, it has left EBS customers to wonder whether the 'end of life' has arrived for Oracle EBS.  There's been a lot of confusion but the reality is that Oracle EBS is not in it's 'end of life'.  In fact, here are three recent updates from Oracle:

1) Oracle announces EBS 12.2.7 (9/8/2017)

www.oracle.com/us/products/applications/ebs-ga-2017-09-08-3876665.pdf

This is the latest/greatest release of EBS, announced just last week.

2) Oracle Roadmap shows support for R12 until at least 2030

(from Oracle.com)

3) EBS R13 is in the works

Cliff Godwin, Oracle Senior VP, Oracle EBS Development, announced at the OAUG Collaborate (April, 2017), that version R13 will be released in 2019/2020. 

Why the confusion?

Oracle has been pushing Cloud and their salespeople have been overly aggressive with their position of EBS' future, in hopes of signing new cloud contracts.  This appears to be how much of the confusion started - and then it spread, as rumors do!

Bottom Line:

Oracle has made it clear that neither their support for, nor their investment in, Oracle EBS is going away.  While everything 'Cloud' may be the marketing push for new revenue, the reality is that EBS support revenue makes up an important part of Oracle's overall annual revenue and they won't risk losing those customers by forcing them to switch to a cloud application or anything else.  

So for all you Oracle EBS customers out there, rest easy!  Oh, and if you want to get your EBS work done faster and easier, take a look at API Wizard (www.api-wizard.com).

Vendor Site trx failed: Inconsistent data entered as compared to TCA records.

Vendor Site trx failed: Inconsistent data entered as compared to TCA records.

Working with supplier site API ap_vendors_pub_pkg.create_vendor_site (also pos_vendor_pub_pkg.create_vendor_site) and ran into the following error:

Vendor Site trx failed: Inconsistent data entered as compared to TCA records.  For details, refer to the Concurrent Program log.  Invalid Payee Contrxt values.

Resolution:

The issue was a fairly simple one - I was creating a vendor site address in the US and didn't pass a value for State, so to resolve this all I needed to do was pass a valid US state.

Leave it to Oracle to provide the most cryptic message possible!  I did some more research into this error and found that additional messaging is passed to the FND Debug logfile, where you see a message such as:

Begin of getAddrValStatus procedure
     Map status is E. Check for address validation level.
     Address validation level is ERROR.
     Please enter valid address elements : STATE
     516- Calling create_gnr with map status E.

Of course, when you are calling an API, you'd rather get the message in real time as part of the output parameters.  I guess that's asking too much?

In my research, I also find some interesting suggestions, like turning off address validation:

As System Administrator, navigate to Profile >System, query where application = Payables and profile = HZ: Address Validation Level%, and set the value to 'no validation').  

That's probably not the right approach in most cases but I guess if you're really stuck, it's an option!

WIP Discrete Jobs and wip_job_schedule_interface / Allow Explosion and Scheduling

WIP Discrete Jobs and wip_job_schedule_interface / Allow Explosion and Scheduling

One of our customers was working with a process to create and update WIP Discrete Jobs via the wip_job_schedule_interface.  While this work was being performed via API Wizard (the great Oracle EBS tool which lets you use Excel to perform data entry, updating, and reporting), this blog is relevant to both those using API Wizard and those working directly with the interface table wip_job_schedule_interface.

The issue the customer was having was that they would often change routing information in WIP Operations and on the Job, sometimes modifying and sometimes removing resource requirements.  When they would update existing Discrete Jobs using wip_job_schedule_interface, both the WIP Operations and the Discrete Job's resource requirements would be defaulted back to that of the item Routing.  

It turns out that all that was needed to resolve this was to set the 'ALLOW_EXPLOSION' field to 'N' (for no!).  Once that was done, the WIP Operation and Discrete Job's resource requirements were not changed.

So far, so good.

There was an issue though.  Once the allow_explosion parameter was set to 'N', the records were failing with a scheduling error "Invalid value for scheduling method".  Here there were two issues; first they needed to change the scheduling method to manual, required when allow_explosion is set to 'N'.  Once that was done, the next error was "You must enter both dates for nonstandard jobs without a routing reference".  They were already passing the first unit dates (first_unit_start_date, first_unit_completion_date) but also had to pass the last_unit dates (last_unit_start_date, last_unit_completion_date).  With those final changes, everything worked as desired!  However, it must be noted that the sacrifice made here is that the schedules must be calculated manually rather than using the interface's routing-based schedule option.  This was handled through some logic in a wrapper to ensure the dates were populated correctly.  

A quick note for completion's sake:

In addition to wip_job_schedule_interface, this process also uses wip_job_dtls_interface for loading components, operation sequences, and resources.

Addendum:

One of our customers who wanted to use the Routing-Based Scheduling option without having the Job operations and resources overwritten by the BOM Routing default found that they could use the private API:

wip_infinite_scheduler_pvt.schedule

They say it works great.  Being a private API, not all organizations will use it but since its scope is fairly limited (to the scheduling of an existing job), it appears to be fairly low risk in the scheme of things.

So that throws another option into the mix!

Controlling Accounting when Creating Receipt via AR_RECEIPT_API_PUB.CREATE_CASH

Controlling Accounting when Creating Receipt via AR_RECEIPT_API_PUB.CREATE_CASH

A prospect asked this question during an API Wizard Demo:

"Can we control the GL Accounting for a receipt created via API as we can by choosing Receipt Class when entering a Receipt Batch in Oracle forms?"

Answer:

Yes. In Oracle, a Receipt Class is comprised of some basic information, the most important of which is the 'Receipt Method'. It is the Receipt Method that actually controls the GL accounting, not the Receipt Class (but since a Receipt Method is associated with a Receipt Class and the user selects the Receipt Class when creating a batch in Oracle, it is more familiar). Receipt Methods can be associated with one or more Bank/Branch/Account combinations.

The key GL accounts which can be defaulted for a Receipt Method include: Cash, Bank Charges, Unapplied Receipts, Unidentified Receipts, On-Account Receipts, Unearned Discounts, Earned Discounts, and Claim Investigations.

The public API AR_RECEIPT_API_PUB.CREATE_CASH allows you to pass the Receipt Method as well as Bank info, providing the same control of GL Accounts as when entering a batch using a Receipt Class.

By the way, there are several other things you can do with ar_receipt_api_pub, including procedures: apply, apply_in_detail, unapply, process_payment, create_and_apply, create_payment_extension, reverse, apply_on_account, unapply_on_account, unapply_other_account, activity_application, activity_unapplication, create_misc, apply_open_receipt, unapply_open_receipt.  There's so much you can do with ar_receipt_api_pub.  Unfortunately, as many of you know, the one thing it can't do is create a receipt batch.  It's been on the Oracle enhancement list for a while, hopefully they get to it soon!

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

Purchase Order Import

Working with Purchase Order Import (Import Standard Purchase Orders - POXPOPDOI)

A prospective customer wants to use API Wizard to load purchase orders.  Oracle provides an open interface process, aka the PO import process, for loading POs.  My plan is to utilize this import process via API Wizard. So, the first thing I do is run a a couple of simple test transactions through the interface to see that I can create POs the way I want. And I'm off...

Error: PO_HEADER_ID There should be at least one line per document.

However, I was repeatedly vexed by the following error: "Error: PO_HEADER_ID There should be at least one line per document." which I see in the po_interface_errors table. 

As usual, Oracle makes things that should be seemingly easy way too difficult! This error only tells me that there should be one line per document but, as you could imagine, I already had multiple lines associated with the header... so what gives?  

First, a little background on the this process:

Oracle provides the following interface tables:

po_interface_headers
po_interface_lines
po_interface_distributions

These tables are linked with the field 'po_header_id' which should be generated using the sequence po_headers_interface_s.  This serves as the link between both tables.  There is also a field po_header_id.  The error actually references this column, which makes you think you need to have this value in both the header and lines table - but that's misleading - the interface actually populates this value during the import process and it becomes the header_id in the po_headers_all table once the PO is successfully imported.

Although I am not sure why the interface failed the first time I ran it, what ended up happening is that the lines associated with my header had been stamped by the interface with a process_code of 'ACCEPTED' and so the import process wasn't considering them when I tried to reimport the header.  Hence the import process complaining that there needed to be at least one line.

The bottom line is make sure you have the correct interface_header_id in the lines table and it pays to reset the process_code if you are trying to rerun (or potentially change to 'UPDATE').

As I was looking into this, I also came across someone who indicated that you can receive this same error message if you due to incorrectly setting the import parameter 'Create or Update Items' to 'Yes' for a one time item.  This is referenced in metalink document ID 862819.1.  The bottom line is you should keep this set to 'No' unless you are trying to create a one-time item (time for the Purchasing User Manual for more details).

PO Created but no confirmation in concurrent output

Once I got passed the 'at least one line per document' error, I was able to successfully create an 'incomplete PO' (I hadn't entered distribution info in my first pass).  Unfortunately, the concurrent request didn't generate any output - it would have been nice if it had told me my PO was created.  Anyway, the po_header_interface record was stamped with a process_code of 'INCOMPLETE' and when I looked in po_headers_all and po_lines_all, my PO was there (I also verified it through the front end forms).

SELECT *
FROM   po_headers_all
WHERE  po_header_id = 110309

The 'authorization_status' field was incomplete, so I know I have to bring in my distributions to finish this PO.  So I added it:

Adding the distribution record

You need at least one distribution record for each purchase order line and you must pass some basic information.  First, to associate the distribution with an PO header and line, you must pass the following fields (with the same values uses for the po_interface_headers and po_interface_lines) tables:

interface_header_id (from po_interface_headers)
interface_line_id (from po_interface_lines)

Use the suggested sequence to derive the interface_distribution_id (unless you are importing from another source and have a unique numbering convention):

Some other useful things to know:

1.  Check package po_pdoi_constants to find the valid values for key parameters such as 'Action', 'Document Type', 'Process Code', etc. (lots of interesting stuff here).

2.  Use the table po_interface_errors to see the errors for your transactions.  The following fields in this table are particularly useful:

Table_Name -> The table for which the error is reported (e.g. po_headers_interface)
Interface_header_id -> The header_id for the errored record (if available)
Interface_line_id -> The line_id for the errored record (if available)
Interface_distribution_id -> the distribution_id for the errored record (if available)
Column_name -> The column the error references
Error_message_name -> The error code
Error_message -> The user error message
Request_id -> The concurrent request id