projects apis
Post on 18-Jan-2016
131 Views
Preview:
DESCRIPTION
TRANSCRIPT
1 © 2009 Oracle Corporation – Proprietary and Confidential
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
© 2010 Oracle Corporation – Proprietary and Confidential
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.
© 2010 Oracle Corporation – Proprietary and Confidential
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
© 2010 Oracle Corporation – Proprietary and Confidential
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
© 2010 Oracle Corporation – Proprietary and Confidential
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
© 2010 Oracle Corporation – Proprietary and Confidential
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
© 2010 Oracle Corporation – Proprietary and Confidential
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
© 2010 Oracle Corporation – Proprietary and Confidential
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_msg_data => l_msg_data,
p_encoded => 'F',
p_data => l_data,
p_msg_count => l_msg_count,
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_msg_count => l_msg_count,
p_encoded => 'F',
p_msg_data => l_msg_data,
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
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.
32 © 2010 Oracle Corporation – Proprietary and Confidential
D E M O N S T R A T I O N
33
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)
35 © 2010 Oracle Corporation – Proprietary and Confidential
THANK YOU
top related