Rajesh Apps Blog: Project Events API with prerequisites

Hi,

HAPPY NEW YEAR 2012...

This post is going to explain you about the Project Events Loading using API.

API Name: pa_event_pub.create_event

How to create Events in Oracle Apps?

Navigation: Oracle Project User Responsibility -- Billing -- Events -- Project (Enter Project Name) -- New

Event Type, Organization, Description, Currency, Bill Amount and Revenue Amount are mandatory fields.

How to create Events using API?

Most of the steps are common as compared to the normal data loading process using APIs. But there are few mandatory steps to be noted before loading the project events data.

Those are

Initialize the apps using mo_global or fnd_global, If the package/procedure is calling from sql
Make sure you use the same responsibility from the back end which you used while successfully creating project from front end.

But if you have to use custom responsibility,
then make sure you have the following sub-functions attached to the menu which is attached to your custom responsibility

These are standard sub-functions attached to the Project responsibility.

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

Until unless the above functions are attached to the responsibility we can not create events using API. We may get the return_status as ‘E’ (which refers to the error in API).

Event Loader program needs to be run separately for each and every operating unit. The events related to the respective operating unit’s data are only uploaded.
Enable Operating Unit LOV (List of Values) while submitting the concurrent program. [Explained in the post “Setup to be done to support Multi-Org Views” – Published on 22^nd November, 2011]
If you enable/use descriptive flex field while creating the event, then you should pass the DFF name and attribute values for the API.

Ex: I have used two attributes in the ‘Events’ DFF.

So p_desc_flex_name := 'PA_EVENTS_DESC_FLEX'; -- Check it in Sample code

To get the above value, Click on the DFF, get the title of the DFF and execute the following query:

select DESCRIPTIVE_FLEXFIELD_NAME from FND_DESCRIPTIVE_FLEXS_TL where title = 'Events';

Sample Code:

/*********************************************************************

-- CUSTOM PROCEDURE TO CREATE PROJECT EVENTS

***********************************************************************/

PROCEDURE create_events (

errbuf OUT NOCOPY VARCHAR2,

retcode OUT NOCOPY NUMBER

)

CURSOR cur(p_org_id IN NUMBER)

SELECT sped.*

FROM son_project_events_data sped

WHERE sped.attribute4 IS NULL

and sped.org_id = p_org_id

ORDER BY sped.sno;

l_cur_rec son_project_events_data%rowtype;

l_error VARCHAR2 (300);

l_msg_count NUMBER;

l_msg_data VARCHAR2 (2000);

l_return_status VARCHAR2 (10);

l_data VARCHAR2 (2000);

l_msg_index_out NUMBER;

l_event_in_tbl pa_event_pub.event_in_tbl_type;

l_event_out_tbl pa_event_pub.event_out_tbl_type;

ln_line_no NUMBER := 0;

l_ref_no NUMBER;

l_rowcount NUMBER;

l_index NUMBER := 0;

l_reference VARCHAR2 (100);

l_event_id NUMBER;

l_status VARCHAR2 (100);

l_event_type pa_event_types.event_type%TYPE;

l_project_number pa_projects_all.segment1%TYPE;

l_org_name hr_all_organization_units.NAME%TYPE;

l_currency fnd_currencies.currency_code%TYPE;

l_task_number pa_tasks.task_number%TYPE:= NULL;

l_validation_flag VARCHAR2 (1);

l_validation_errors NUMBER := 0;

l_cursor_rowcount NUMBER;

ln_user_id NUMBER := fnd_profile.VALUE ('USER_ID');

ln_user_name fnd_user.user_name%TYPE;

ln_responsibility_id NUMBER := fnd_profile.VALUE ('RESP_ID');

ln_responsibility_name fnd_responsibility_tl.RESPONSIBILITY_NAME%TYPE;

ln_resp_appl_id NUMBER := fnd_profile.VALUE ('RESP_APPL_ID');

ln_org_id NUMBER := NVL (mo_global.get_current_org_id, fnd_profile.VALUE ('ORG_ID'));

ln_org_name hr_operating_units.name%TYPE;

l_project_currency_code fnd_currencies.currency_code%TYPE;

BEGIN

-- This is not required when you are registering the code as concurrent program

fnd_global.apps_initialize (ln_user_id,

ln_responsibility_id,

ln_resp_appl_id

);

begin

select user_name

into ln_user_name

from fnd_user

where user_id = ln_user_id;

select responsibility_name

into ln_responsibility_name

from fnd_responsibility_tl

where responsibility_id = ln_responsibility_id;

select name

into ln_org_name

from hr_operating_units

where organization_id = ln_org_id;

exception when others then

fnd_file.put_line(fnd_file.LOG,'Error while getting profile option values-User Name/Responsibility Name/Operating Unit for the Org ID: '||ln_org_id);

end;

fnd_file.put_line(fnd_file.LOG,'Logged in User: '||ln_user_name);

fnd_file.put_line(fnd_file.LOG,'Responsibility Name: '||ln_responsibility_name);

fnd_file.put_line(fnd_file.LOG,'Appl ID: '||ln_resp_appl_id);

fnd_file.put_line(fnd_file.LOG,'Operating Unit: '||ln_org_name);

fnd_file.put_line(fnd_file.LOG,'Org ID: '||ln_org_id);

-- This is not required when you are registering the code as concurrent program

mo_global.set_policy_context ('S',ln_org_id);

commit;

OPEN cur(ln_org_id);

LOOP

fetch cur into l_cur_rec;

-- fnd_file.put_line(fnd_file.LOG,'Number of rows fetched: '||cur%rowcount);

IF cur%rowcount = 0 THEN

-- l_cursor_rowcount := 0;

fnd_file.put_line

(fnd_file.LOG,'There are no records to fetch hence events have not created');

RETURN;

END IF;

exit when cur%notfound;

SELECT son_proj_event_ref_s.NEXTVAL

INTO l_ref_no

FROM DUAL;

------------------------ Event Type Validation --------------------------------------------------------------

IF l_cur_rec.event_type IS NOT NULL

THEN

BEGIN

SELECT event_type

INTO l_event_type

FROM pa_event_types

WHERE event_type = l_cur_rec.event_type

AND TRUNC (SYSDATE) BETWEEN start_date_active

AND NVL (end_date_active, SYSDATE);

l_validation_flag := 'Y';

EXCEPTION

WHEN NO_DATA_FOUND

THEN

fnd_file.put_line(fnd_file.LOG,

'Event type validation is failed for the Event Type: '

|| l_cur_rec.event_type||' for the project number: '||l_cur_rec.project_number||' and the SNo: '||l_cur_rec.sno

);

l_validation_flag := 'N';

l_validation_errors := l_validation_errors + 1;

WHEN OTHERS

THEN

fnd_file.put_line

(fnd_file.LOG,

'Other error occurred while validating Event Type: '

|| l_cur_rec.event_type||' for the project number: '||l_cur_rec.project_number||' and the SNo: '||l_cur_rec.sno

);

l_validation_flag := 'N';

l_validation_errors := l_validation_errors + 1;

END;

ELSE

fnd_file.put_line (fnd_file.LOG,

'Event Type is NULL in the line: ' || l_cur_rec.sno

);

l_validation_flag := 'N';

l_validation_errors := l_validation_errors + 1;

END IF;

------------------------ Project Number Validation -------------------------------------------------------------

IF l_cur_rec.project_number IS NOT NULL

THEN

BEGIN

SELECT segment1,project_currency_code

INTO l_project_number,l_project_currency_code

FROM pa_projects_all

WHERE segment1 = l_cur_rec.project_number

and org_id = ln_org_id

and project_status_code in ('APPROVED','1002')

AND TRUNC (SYSDATE) BETWEEN start_date

AND NVL (completion_date, SYSDATE);

l_validation_flag := 'Y';

EXCEPTION

WHEN NO_DATA_FOUND

THEN

fnd_file.put_line

(fnd_file.LOG,

'Validation failed for the Project Number: '

|| l_cur_rec.project_number

);

l_validation_flag := 'N';

l_validation_errors := l_validation_errors + 1;

WHEN OTHERS

THEN

fnd_file.put_line

(fnd_file.LOG,

'Other error occured while validating Project Number: '

|| l_cur_rec.project_number

);

l_validation_flag := 'N';

l_validation_errors := l_validation_errors + 1;

END;

ELSE

fnd_file.put_line (fnd_file.LOG,

'Project Number is NULL in the line: ' || l_cur_rec.sno

);

l_validation_flag := 'N';

l_validation_errors := l_validation_errors + 1;

END IF;

------------------------ Organixation Name Validation ------------------------------------------------------------

IF l_cur_rec.org_name IS NOT NULL

THEN

BEGIN

SELECT NAME

INTO l_org_name

FROM hr_all_organization_units

WHERE TRUNC (SYSDATE) BETWEEN date_from AND NVL (date_to,

SYSDATE

)

AND NAME = l_cur_rec.org_name;

l_validation_flag := 'Y';

EXCEPTION

WHEN NO_DATA_FOUND

THEN

fnd_file.put_line

(fnd_file.LOG,

'Validation failed for the Organization: '

|| l_cur_rec.org_name||' for the project number: '||l_cur_rec.project_number||' and the SNo: '||l_cur_rec.sno

);

l_validation_flag := 'N';

l_validation_errors := l_validation_errors + 1;

WHEN OTHERS

THEN

fnd_file.put_line

(fnd_file.LOG,

'Other error occured while validating Organization: '

|| l_cur_rec.org_name||' for the project number: '||l_cur_rec.project_number||' and the SNo: '||l_cur_rec.sno

);

l_validation_flag := 'N';

l_validation_errors := l_validation_errors + 1;

END;

ELSE

fnd_file.put_line (fnd_file.LOG,

'Organization Name is NULL in the line: '

|| l_cur_rec.sno

);

l_validation_flag := 'N';

l_validation_errors := l_validation_errors + 1;

END IF;

------------------------- Currency Validation -------------------------------------------------------------

IF l_cur_rec.currency IS NOT NULL

THEN

-- Not validating billing currency with project functional currency because there may be cases

BEGIN

SELECT currency_code

INTO l_currency

FROM fnd_currencies

WHERE TRUNC (SYSDATE) BETWEEN NVL(start_date_active,SYSDATE-1)

AND NVL (end_date_active, SYSDATE)

AND currency_code = l_cur_rec.currency;

-- Commented because project functional currency need not be same as the project event billing currency

/*IF l_project_currency_code = l_cur_rec.currency THEN

l_validation_flag := 'Y';

ELSE

fnd_file.put_line

(fnd_file.LOG,

'Validation mismatch for the Currency: '

|| l_cur_rec.currency || ' with Project Currency: '||l_project_currency_code||' for the project: '||l_cur_rec.project_number||'in SNo: '||l_cur_rec.sno

);

l_validation_flag := 'N';

l_validation_errors := l_validation_errors + 1;

END IF;*/

EXCEPTION

WHEN NO_DATA_FOUND

THEN

fnd_file.put_line

(fnd_file.LOG,

'Validation failed for the Currency: '

|| l_cur_rec.currency||' for the project number: '||l_cur_rec.project_number||' and the SNo: '||l_cur_rec.sno

);

l_validation_flag := 'N';

l_validation_errors := l_validation_errors + 1;

WHEN OTHERS

THEN

fnd_file.put_line

(fnd_file.LOG,

'Other error occured while validating Currency: '

|| l_cur_rec.currency||' for the project number: '||l_cur_rec.project_number||' and the SNo: '||l_cur_rec.sno

);

l_validation_flag := 'N';

l_validation_errors := l_validation_errors + 1;

END;

ELSE

fnd_file.put_line (fnd_file.LOG,

'Currency is NULL in the line: ' || l_cur_rec.sno

);

l_validation_flag := 'N';

l_validation_errors := l_validation_errors + 1;

END IF;

------------------------Task Validation --------------------------------------------------------------

-- Commented because Task is not mandatory for creating project events.

-- Client is not using task while creating project events, so made it as null while initiating l_task_number

-- Enable this code depends on the requirement

/* IF l_cur_rec.task_number IS NOT NULL

THEN

BEGIN

SELECT task_number

INTO l_task_number

FROM pa_tasks

WHERE TRUNC (SYSDATE) BETWEEN start_date

AND NVL (completion_date, SYSDATE)

AND task_number = l_cur_rec.task_number;

l_validation_flag := 'Y';

EXCEPTION

WHEN NO_DATA_FOUND

THEN

fnd_file.put_line

(fnd_file.LOG,

'Validation failed for the Task Number: '

|| l_cur_rec.task_number

);

l_validation_flag := 'N';

l_validation_errors := l_validation_errors + 1;

WHEN OTHERS

THEN

fnd_file.put_line

(fnd_file.LOG,

'Other error occured while validating Task Number: '

|| l_cur_rec.task_number

);

l_validation_flag := 'N';

l_validation_errors := l_validation_errors + 1;

END;

ELSE

l_task_number := NULL;

END IF;*/

--------------------------------------------------------------------------------------

IF l_validation_flag = 'Y'

THEN

dbms_output.put_line('Task:'||l_task_number);

dbms_output.put_line('Event Type: '||l_event_type);

dbms_output.put_line('Completion date: '||l_cur_rec.event_date);

dbms_output.put_line('Project Number: '||l_project_number);

dbms_output.put_line('Org Name: '||l_org_name);

dbms_output.put_line('Currency: '||l_currency);

dbms_output.put_line('l_ref_no: '||l_ref_no);

ln_line_no := ln_line_no + 1;

l_event_in_tbl (ln_line_no).p_pm_event_reference := l_ref_no;

l_event_in_tbl (ln_line_no).p_task_number := l_task_number;

l_event_in_tbl (ln_line_no).p_event_type := l_event_type;

l_event_in_tbl (ln_line_no).p_description := l_cur_rec.description;

l_event_in_tbl (ln_line_no).p_completion_date := l_cur_rec.event_date;

l_event_in_tbl (ln_line_no).p_desc_flex_name := 'PA_EVENTS_DESC_FLEX';

--l_event_in_tbl (ln_line_no).P_attribute_category := 'Events';

l_event_in_tbl (ln_line_no).p_attribute1 := l_cur_rec.attribute1;

l_event_in_tbl (ln_line_no).p_attribute2 := l_cur_rec.attribute2;

l_event_in_tbl (ln_line_no).p_project_number := l_project_number;

l_event_in_tbl (ln_line_no).p_organization_name := l_org_name; -- Project owning organization

l_event_in_tbl (ln_line_no).p_bill_trans_bill_amount := l_cur_rec.bill_amount;

l_event_in_tbl (ln_line_no).p_bill_trans_rev_amount := l_cur_rec.revenue_amount;

l_event_in_tbl (ln_line_no).p_bill_trans_currency_code := l_currency;

-- Project Functional currency

END IF;

END LOOP;

close cur;

-- IF l_cursor_rowcount <> 0 THEN

IF l_validation_errors = 0

THEN

fnd_file.put_line(fnd_file.LOG,'All the records are validated successfully');

fnd_file.put_line(fnd_file.LOG,'Calling API....');

pa_event_pub.init_event;

pa_event_pub.create_event (p_api_version_number => 1.0,

p_commit => fnd_api.g_false,

p_init_msg_list => fnd_api.g_true,

p_pm_product_code => 'DATAMIG', -- Data Migration

p_event_in_tbl => l_event_in_tbl,

p_event_out_tbl => l_event_out_tbl,

p_msg_count => l_msg_count,

p_msg_data => l_msg_data,

p_return_status => l_return_status

);

fnd_file.put_line (fnd_file.LOG,

'API message: ' || NVL(l_msg_data,'Nothing to display')

);

fnd_file.put_line (fnd_file.LOG,

'Return Status of API is: ' || l_return_status

);

DBMS_OUTPUT.put_line ('After API: ' || l_msg_data);

DBMS_OUTPUT.put_line ('Return Status: ' || l_return_status);

IF l_return_status = 'S'

THEN

UPDATE son_project_events_data

SET attribute4 = 'Event Created'

WHERE attribute4 IS NULL

and org_id = ln_org_id;

fnd_file.put_line (fnd_file.LOG,'Events created successfully and updated the custom table');

ELSE

UPDATE son_project_events_data

SET attribute4 = 'API Error'

WHERE attribute4 IS NULL

and org_id = ln_org_id;

fnd_file.put_line (fnd_file.LOG, 'Program ends up with Error');

DBMS_OUTPUT.put_line ('Program ends up with Error');

END IF; --l_return_status = 'S'

ELSE -- l_validation_errors =0

UPDATE son_project_events_data

SET attribute4 = 'Validation Failed'

WHERE attribute4 IS NULL

and org_id = ln_org_id;

fnd_file.put_line

(fnd_file.LOG,

'Events have not created because of validation failures'

);

END IF; -- l_validation_errors = 0

COMMIT;

-- ELSE --l_rowcount <> 0

-- fnd_file.put_line(fnd_file.LOG,'There are no records to fetch hence events have not created');

-- END IF; --l_rowcount <> 0

/* FOR i IN 1 .. l_msg_count

LOOP

apps.pa_interface_utils_pub.get_messages

(p_encoded => 'F',

p_msg_count => l_msg_count,

p_msg_data => l_msg_data,

p_data => l_data,

p_msg_index => i,

p_msg_index_out => l_msg_index_out

);

END LOOP;

fnd_file.put_line(fnd_file.LOG,'From pa_interface_utils_pub.get_messages: ' || l_data);

DBMS_OUTPUT.put_line ('From pa_interface_utils_pub.get_messages: ' || l_data); */

-- dbms_output.put_line('Before Commit');

EXCEPTION

WHEN OTHERS

THEN

l_error := SUBSTR (SQLERRM, 1, 200);

fnd_file.put_line (fnd_file.LOG,

'Other Error while creating event: ' || l_error

);

END create_events;

*********************************************************************************

Thanks,

Rajesh

Rajesh Apps Blog

Wednesday, 4 January 2012

Project Events API with prerequisites

4 comments: