projects apis

2

Safe Harbor Statement

© 2010 Oracle Corporation – Proprietary and Confidential

The following is intended to outline our general

product direction. It is intended for information

purposes only, and may not be incorporated into

any contract. It is not a commitment to deliver any

material, code, or functionality, and should not be

relied upon in making purchasing decision. The

development, release, and timing of any features

or functionality described for Oracle’s products

remains at the sole discretion of Oracle.

3

<Insert Picture Here>

Project API’s

Glade Slaugh

Principal Technical Support Engineer

4

AGENDA

• Overview of API Basics

• Setup Steps for Using APIs

• Foundation APIs

• Costing APIs

• Billing APIs

• Project Management APIs

• New 12.1.x APIs

• Troubleshooting APIs

• API Demo

• Q & A


5

Overview of API Basics

•The Oracle Projects Application Programming

Interfaces (APIs) enable you to integrate Oracle

Projects with third-party systems to build a complete

management tool. You can combine the functionality

of your preferred system with the features of Oracle

Projects, and then safely share data and exchange

information.

•The APIs include more than 150 application

programming interfaces.


6

APIs do the following

• Perform real-time or batch sharing of data between

your system and Oracle Projects, thereby eliminating

duplicate data entry

• Share business rules and workflow from one system

to the other

• Share setup, project planning, resource planning,

budgeting, actuals, and progress data


7

Where Information Originates

• Project templates with Quick Entry (overridable) fields

• Resources

• Organizations

• Calendars (both GL and PA periods)

• Estimate to Complete (planned for a future release)

• Actuals: cost amounts (raw and burdened),

commitments (raw and burdened), quantities,

revenue, PA or GL period, inception-to-date, period-

to-date


8

Information That Originates In An

External System

• Projects and tasks of the work breakdown structure

(WBS)

• Budgets: Types, Time-Phased, Amounts, Quantities,

Baseline

• Schedules and schedule changes

• Task parent reassignment

• Percent complete: project level, WBS (any level)

• Earned value progress reporting: Budgeted Cost of

Work Scheduled, Budgeted Cost of Work Performed,

Actual Cost of Work Performed, Budget at Completion


9

Setup Steps for Using APIs

1. Create a Database Role

2. Create an Oracle Applications User

3. Create a Database User

4. Set Up Your Product in Oracle Projects

10

Basic Steps In Your API Calling Script

1. Declare Section

2. Set Global Info – responsibility and user ids

3. Setup data to be passed to the API –

variable/record assignments

4. Call the pa_project_pub.init_project function.

5. Call the API passing the correct parameters

6. Handle any exceptions that might be returned


11

Project Foundation

APIs Views

• PA_CUSTOMERS_LOV_V

• PA_CLASS_CATEGORIES_LOV_V

• PA_DISCOUNT_CODES_LOV_V

• PA_DISTRIBUTION_RULES_LOV_V

• PA_EMPLOYEE_SCHEDULES_LOV

_V

• PA_JOB_SCHEDULES_LOV_V

• PA_INVOICE_SCHEDULES_LOV_V

• PA_KEY_MEMBERS_LOV_V

• PA_ORG_NL_SCHDL_LOV_V

• PA_ORGANIZATIONS_LOV_V

• PA_OVERRIDE_FIELDS_V

• PA_OVERRIDE_FIELD_VALUES_V

• PA_PROJECT_STATUS_LOV_V

• PA_PROJECTS_AMG_V

• PA_REVENUE_SCHEDULES_LOV_

V

• PA_SELECT_TEMPLATE_V


12

Project Foundation

APIs Procedures

Composite Data type Procedures

• CREATE_PROJECT

• DELETE_PROJECT

• UPDATE_PROJECT

• INIT_PROJECT

• CHECK_CHANGE_PROJECT_ORG_OK

• CHECK_DELETE_PROJECT_OK

• CHECK_UNIQUE_PROJECT_REFERENCE

Load Execute Fetch Procedures

• EXECUTE_CREATE_PROJECT

• EXECUTE_UPDATE_PROJECT

• LOAD_CLASS_CATEGORY

• LOAD_KEY_MEMBER

• LOAD_ORG_ROLE

• LOAD_PROJECT

• CLEAR_PROJECT

13

Standard API Parameters

• p_ api_version_number Always 1.0. This may change future versions

of the APIs.

• p_commit if T = The API issues the commit to the database

• p_init_msg_list Set this parameter to T (True) if you want to initialize the global

message table. Default = F (False)

• p_msg_count Holds the number of messages in the global message table.

• p_msg_data Holds the message code, if the API returned only one

error/warning message. Otherwise, the column is left

blank.

• p_return_status The return status of the APIs. Valid values are: S (the API completed successfully), E (business rule violation error), and U (Unexpected error, such as

an Oracle error)

• p_pm_product_code Defined in under setup->Application Programming Interfaces->Source Products

14

API Call Example

--CREATE_PROJECT

pa_project_pub.create_project(

l_api_version_number,

p_commit => l_commit,

p_init_msg_list => l_init_msg_list,

p_msg_count => l_msg_count,

p_msg_data => l_msg_data,

p_return_status => l_return_status,

p_workflow_started => l_workflow_started,

p_pm_product_code => l_pm_product_code,

p_project_in => l_project_in,

p_project_out => l_project_out,

p_key_members => l_key_members,

p_class_categories => l_class_categories,

p_tasks_in => l_tasks_in,

p_tasks_out => l_tasks_out);

15

API Types and Parameters

• Composite data types – pl/sql tables

• Fields in the pl/sql tables are individually set in record

structures

• Record structures are assigned to a pl/sql table

• The pl/sql table is passed as a single parameter

• Load-Execute-Fetch (LEF) – Individual fields

• Each variable assigned individually

• Parameters are passed first to the Load API

• Execute API is then called after the parameters are loaded

16

Differences Between Methods

• Load-Execute-Fetch

• Used for languages cannot pass structures (pl/sql tables) to

pl/sql (e.g. java)

• Composite datatype

• Used for languages that can pass structures (pl/sql tables) to

pl/sql (e.g. Pro-C, pl/sql)

17

Error Handling Example

• Make call to the pa_interface_utils.get_messages API for each message

EXCEPTION

When API_ERROR then

if l_msg_count >= 1

then

for i in 1..l_msg_count loop

pa_interface_utils_pub.get_messages(


p_encoded => 'F',

p_data => l_data,


p_msg_index_out => l_msg_index_out);

dbms_output.put_line('error message: ' || l_data);

end loop;

rollback;

end if;

When OTHERS then

if l_msg_count >= 1

then

for i in 1..l_msg_count loop

pa_interface_utils_pub.get_messages(


p_encoded => 'F',


p_data => l_data,

p_msg_index_out => l_msg_index_out);

dbms_output.put_line('error message: ' || l_data);

end loop;

rollback;

end if;

END;

18

Project Costing

Asset Views

• PA_PROJECT_ASSET_TYPE_LOV_V

• PA_ASSET_BOOKS_LOV_V

• PA_PARENT_ASSET_LOV_V

• PA_RET_TARGET_ASSET_LOV_V

• PA_PROJECT_ASSETS_AMG_V

19

Project Costing

Asset Procedure/Functions

Composite data type

procedure/functions

• ADD_PROJECT_ASSET

• UPDATE_PROJECT_ASSET

• DELETE_PROJECT_ASSET

• ADD_ASSET_ASSIGNMENT

• ADD_ASSET_ASSIGNMENT

Load-Execute-Fetch

procedure/function calls

• LOAD_PROJECT_ASSET

• LOAD_ASSET_ASSIGNMEN

T

• EXECUTE_ADD_PROJECT_

ASSET

• CONVERT_PM_ASSTREF_T

O_ID

• FETCH_PROJECT_ASSET_I

D

20

Project Costing APIs Cost Plus Application Programming

Interface (API)

• Get Burden Amount

• The cost plus application programming interface procedure is

PA_COST_PLUS.GET_BURDEN_AMOUNT.

• You can view descriptions of all of the parameters for this

procedure in the Oracle Integration Repository.

21

Project Billing

Agreement and Funding Views

• PA_AGREEMENT_TYPE_LOV_V

• PA_TERMS_LOV_V

• PA_OWNED_BY_LOV_V

• PA_CUSTOMERS_LOV_V

22

Project Billing

Procedures and Functions

• CREATE_AGREEMENT

• DELETE_AGREEMENT

• UPDATE_AGREEMENT

• CREATE_BASELINE_BUD

GET

• ADD_FUNDING

• DELETE_FUNDING

• UPDATE_FUNDING

• INIT_AGREEMENT

• LOAD_AGREEMENT

• LOAD_FUNDING

• EXECUTE_CREATE_AGREEME

NT

• EXECUTE_UPDATE_AGREEME

NT

• FETCH_FUNDING

• CLEAR_AGREEMENT

• CHECK_DELETE_AGREEMENT_OK • CHECK_ADD_FUNDING_OK

• CHECK_DELETE_FUNDING_OK • CHECK_UPDATE_FUNDING_OK

23

Project Billing

Event APIs

• CREATE_EVENT

• DELETE_EVENT

• UPDATE_EVENT

• INIT_EVENT

• CHECK_DELETE_EVENT_

OK

• LOAD_EVENT

• EXECUTE_CREATE_EVE

NT

• EXECUTE_UPDATE_EVENT

• FETCH_EVENT

• CLEAR_EVENT

24

Project Management

Project Deliverables API Views

• PA_DELIVERABLES_AMG_V

• PA_DLVR_ACTIONS_AMG_V

• PA_DELIVERABLE_TYPES_AMG_V

• PA_DELIVERABLE_STATUSES_AMG_V

• PA_ACTION_FUNCTIONS_AMG_V

25

Project Management

Deliverable API Procedures

• CREATE_DELIVERABLE

• CREATE_DELIVERABLE_ACTION

• UPDATE_DELIVERABLE

• UPDATE_DELIVERABLE_ACTION

• DELETE_DELIVERABLES

• DELETE_DELIVERABLE_ACTIONS

• ASSOCIATE_DLV_TO_TASK • ASSOCIATE_DLV_TO_TASK_ASS

IGN

• DELETE_DLV_TO_TASK_ASSCN

• DELETE_DLV_TO_TASK_ASSIGN

• LOAD_DELIVERABLE

• LOAD_DELIVERABLES

• LOAD_ACTION

• LOAD_ACTIONS

26

Project Management

Budget API Views

• PA_BASE_BUDGET_BY_GL_PERIOD_V

• PA_BASE_BUDGET_BY_PA_PERIOD_V

• PA_BUDGET_CHANGE_REASON_V

• PA_BUDGET_ENTRY_METHODS_V

• PA_BUDGET_STATUS_CODES_V

• PA_BUDGET_TYPES_V

• PA_ORIG_BUDGET_BY_GL_PERIOD_V

• PA_ORIG_BUDGET_BY_PA_PERIOD_V

• PA_FINPLAN_TYPES_V

• PA_BASE_FINPLAN_BY_GL_PERIOD_V

• PA_BASE_FINPLAN_BY_PA_PERIOD_V

• PA_ORIG_ FINPLAN_BY_GL_PERIOD_V

• PA_ORIG_ FINPLAN_BY_PA_PERIOD_V

27

Project Management

Budget API Procedures

• ADD_BUDGET_LINE

• BASELINE_BUDGET

• CALCULATE_AMOUNTS

• CREATE_DRAFT_BUDGET

• CREATE_DRAFT _FINPLAN

• DELETE_BUDGET_LINE

• DELETE_DRAFT_BUDGET

• GET_PROJECT_ID

• SET_PROJECT_ID

• UPDATE_BUDGET

• UPDATE_BUDGET_LINE

• CLEAR_BUDGET

• CLEAR_CALCULATE_AMOUNTS

• EXECUTE_CALCULATE_AMOUNTS

• EXECUTE_CREATE_DRAFT_BUDGET

• EXECUTE_CREATE_DRAFT_FINPLAN

• EXECUTE_UPDATE_BUDGET

• FETCH_BUDGET_LINE

• FETCH_CALCULATE_AMOUNTS

• INIT_BUDGET

• INIT_CALCULATE_AMOUNTS

• LOAD_BUDGET_LINE

• LOAD_RESOURCE_INFO

28

New 12.1 API Views

• PA_PROGRAM_LINKS_AMG_V • Displays the following columns:

• SUB_PROJECT_ID SUB_PROJECT_NAME

• PM_SUB_PROJECT_REFERENCE

• SUB_PROJECT_NUMBER

• SUB_STRUCTURE_VER_ID

• RELATIONSHIP_TYPE

• COMMENTS PARENT_PROJECT_ID

• PARENT_PROJECT_NAME

• PM_PARENT_PROJECT_REFERENCE

• PARENT_PROJECT_NUMBER

• PARENT_STRUCTURE_VER_ID

• PARENT_TASK_VERSION_ID

• PARENT_TASK_ID

• PARENT_TASK_NUMBER

• PARENT_TASK_NAME

• PM_PARENT_PM_TASK_REFERENCE

• OBJECT_RELATIONSHIP_ID

29

New 12.1 Procedures

• SETUP_PROJECT_AS_PROGRAM

• CREATE_PROGRAM_LINKS

• UPDATE_PROGRAM_LINK_COMMENTS

• DELETE_PROGRAM_LINK

30

Troubleshooting APIs

Steps will generally be specific to the error the customer is getting.

1.Search for the error in fnd_new_messages table then see if you can find the message name in the source code to determine what condition generates the exception

2.Request Debug log (see note 1340525.1) from the customer’s calling script

3.Get a trace file by asking the customer to put the following in their code right after the begin statement:

• DBMS_SUPPORT.START_TRACE( waits=>false, binds=>true )

4.Request small (1 to 2 pages) test case with hardcoded values that reproduces the issue

5.Make changes to the hardcoded values to work internally and see if the issue can be reproduced internally

https://support.us.oracle.com/oip/faces/secure/km/DocumentDisplay.jspx?id=1340525.1&h=Y

31

Questions to Ask to Troubleshoot

1. Upload a small test case sample code (1 to 2 pages) with hard coded values that reproduces the issue

calling the API's.

2. Upload any output and/or log information produced when your code is run. Make sure you upload all the

output, not just the error message, so I can tie output to any messaging you have in our code.

3. What is the API being used, and what is your version of the API package? Use the following SQL to

assist you and provide the output:

select text from dba_source where name in

('PA_PROJECT_PUB','PA_PROJECT_PVT') and text like

'%$Header%'

4. Does the error always occur or only occur under certain conditions? If only under certain conditions, what

are those conditions?

5. Use the following procedures to provide the fnd_log_messages:

a. Truncate applsys.fnd_log_messages table before running API process.

b. Add the following to the API program prior to running:

fnd_profile.put('AFLOG_ENABLED', 'Y');

fnd_profile.put('AFLOG_LEVEL', 1);

fnd_profile.put('AFLOG_MODULE','%');

fnd_profile.put('PA_DEBUG_MODE', 'Y');

fnd_log_repository.init();

c. Provide the output of the fnd_log_messages in Excel format with column headings.


D E M O N S T R A T I O N

34

References

• Note 62294.1 - The DBMS_SUPPORT Package

• Note 1340525.1 How Can You Create a PA Debug Log File for AMG APIs?

• Note 206391.1 - Sample Script For AMG API UPDATE_PROJECT Using Composite Data types

• Note 206389.1 - Sample Script to create a project using AMG with composite data types

• Note 215541.1 - Sample Script For Using The Create_Draft_Budget API

• Note 216202.1 - Sample Script For The BASELINE_BUDGET API Using Composite Data types

• Note 1305709.1 - Sample Script for AMG API Add_Task Using Composite Data types

• Note 1305777.1 - Sample Script for AMG API Create_Agreement using Composite Data types

• Oracle Projects APIs, Client Extensions, and Open Interfaces Reference (B25624-02)

https://support.us.oracle.com/oip/faces/secure/km/DocumentDisplay.jspx?id=62294.1

https://support.us.oracle.com/oip/faces/secure/km/DocumentDisplay.jspx?id=1340525.1&h=Y







http://download.oracle.com/docs/cd/B40089_10/current/acrobat/120pjapi.pdf


THANK YOU

projects apis

Documents

oracle corporation proprietary

features of oracle projects

x apis

oracle applications

sole discretion of oracle

apis api demo q

project level

project planning