This discussion is archived
9 Replies Latest reply: Jun 29, 2012 5:55 AM by Sandeep Gandhi, Consultant RSS

Error creating project by PA_PROJECT_PUB.create_project API

Amit Singh Sisodia Newbie
Currently Being Moderated
Hi All,
I have written the below code to create a project:
SET SERVEROUTPUT ON;
DECLARE

i NUMBER;

----API standard parameters---

l_responsibility_id NUMBER;
l_user_id NUMBER;
l_msg_count NUMBER;
l_msg_data VARCHAR2(2000);
l_return_status VARCHAR2(1);
l_api_version_number NUMBER := 1.0;
l_commit VARCHAR2(1) := 'F';
l_init_msg_list VARCHAR2(1) := 'F';
l_workflow_status VARCHAR2(1) := 'Y';
l_pm_product_code VARCHAR2(10);
l_msg_index_out NUMBER;
l_data VARCHAR2(2000);

api_error EXCEPTION;
-----------COMPOSITE DATA TYPES-----------------

l_project_in PA_PROJECT_PUB.PROJECT_IN_REC_TYPE;
l_project_out PA_PROJECT_PUB.project_out_rec_type;
l_key_members PA_PROJECT_PUB.project_role_tbl_type;
l_class_categories PA_PROJECT_PUB.class_category_tbl_type;
l_task_in PA_PROJECT_PUB.task_in_tbl_type;
l_tasks_out PA_PROJECT_PUB.task_out_tbl_type;
l_task_in_rec PA_PROJECT_PUB.task_in_rec_type;
l_person_id NUMBER;
l_project_role_type VARCHAR2(20);

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

BEGIN
--Fnd_global.apps_initialize(user_id,resp_id, resp_appl_id);
Fnd_global.apps_initialize(50254,58309,275);

-------calling global parameters---

pa_interface_utils_pub.set_global_info
(
p_api_version_number =>1.0,
p_responsibility_id =>58309,
p_user_id =>50254,
p_msg_count =>l_msg_count,
p_msg_data =>l_msg_data,
p_return_status =>l_return_status
);

dbms_output.put_line ('Set Global status ->' || l_return_status);
----PRODUCT RELATED DATA-----------
-----PASSING VALUES TO THE COMPOSITE DATA TYPE(PROJECT_IN_REC_TYPE, FIND IN TRM AMGTRM)-------

l_pm_product_code := 'XXCONST';
--l_project_in.pa_project_id := '99999';
l_project_in.pm_project_reference := 'Create_Project_Test' ;
l_project_in.project_name := 'Create_Project_Test';
l_project_in.created_from_project_id := '143074' ;
l_project_in.carrying_out_organization_id := '';--'3616';
l_project_in.public_sector_flag := '';--'N';
l_project_in.project_status_code := '';--'UNAPPROVED';
l_project_in.description := 'Create Project API Test ';
l_project_in.start_date := ''; --sysdate;
l_project_in.completion_date := ''; --sysdate + 10;
l_project_in.distribution_rule := ''; --'EVENT/EVENT';
l_project_in.customer_id := ''; --'1201';
l_project_in.project_relationship_code := 'PRIMARY'; -- from PA_PROJECT_CUSTOMERS


---KEY MEMBERS (PROJECT_ROLE_TBL_TYPE)---
-- can be defaulted from the Template, but not from a Project

-- i:=1;
--
-- l_person_id :=34;
-- l_project_role_type :='PROJECT MANAGER';
-- l_key_members(i).person_id :=61;
-- l_key_members(i).project_role_type :='PROJECT MANAGER';

--CLASS CATEGORIES DATA (CLASS_CATEGORY_TBL_TYPE)
-- l_class_categories(1).class_category := 'Product'; --value from template
-- l_class_categories(1).class_code := 'Not Specified';


---------------------TASKS DATA-------------------------


l_task_in_rec.pm_task_reference := '1';
l_task_in_rec.pa_task_number := '1';
l_task_in_rec.task_name := 'Construction';
l_task_in_rec.pm_parent_task_reference := '' ;
l_task_in_rec.task_description := 'Plant function';
-- Assign the top task to the table.
l_task_in(1) := l_task_in_rec;

-- Assign values for the sub task
l_task_in_rec.pm_task_reference := '1.1';
l_task_in_rec.pa_task_number := '1.1';
l_task_in_rec.task_name := 'Brick laying';
l_task_in_rec.pm_parent_task_reference := '1' ;
l_task_in_rec.task_description := 'Plant building';
-- Assign the subtask to the task table.
l_task_in(2) := l_task_in_rec;

dbms_output.put_line('Before Create Project');


-------------------INITIALIZE PROJECT-------------------;

PA_PROJECT_PUB.init_project;

--------------------CREATING PROJECT--------------------

PA_PROJECT_PUB.create_project(
p_api_version_number => 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_status,
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_task_in,
p_tasks_out => l_tasks_out
);


dbms_output.put_line('Task out number = ' ||l_tasks_out.count);


FOR I in 1..l_tasks_out.count LOOP

dbms_output.put_line('Task Id = ' ||l_tasks_out(i).pa_task_id);
dbms_output.put_line('Task Ref = ' ||l_tasks_out(i).pm_task_reference);
dbms_output.put_line('Return Status = ' ||l_tasks_out(i).return_status);

END LOOP;


IF l_return_status != 'S' THEN

RAISE api_error;

END IF;

--check the returned records in the task out structure


dbms_output.put_line ('The new project id ->' || l_project_out.pa_project_id);
dbms_output.put_line ('The new project number is ->' || l_project_out.pa_project_number);
commit;

---------------Exception handle---------------------

-- Get the error message that were returned if it did not complete sucessfully ---

EXCEPTION

WHEN api_error THEN
dbms_output.put_line('An error occured duing project creation');

IF (l_msg_count > 0 ) THEN

FOR i IN 1..l_msg_count LOOP

apps.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 ->'||l_data);
dbms_output.put_line('Error message l_msg_data ->'||l_msg_data);


END LOOP;

END IF;

WHEN OTHERS THEN
dbms_output.put_line('An error occured duing conversion, SQLCODE ->'|| SQLERRM);

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;

END IF;

END;

-------------------------------------------------------------------------------------
Executing the above code results in following error:

anonymous block completed
Set Global status ->S
Before Create Project
Task out number = 0
An error occured duing project creation
Error message l_data ->
Error message l_msg_data ->PA PA_FUNC_SECURITY_ENFORCED_AMG
-------------------------------------------------------------------------------------

Please let me know where exactly am I doing wrong in this?

Edited by: 932550 on Jun 19, 2012 3:07 AM
  • 1. re
    Amit Singh Sisodia Newbie
    Currently Being Moderated
    What could be the possible solution to this?
  • 2. Re: re
    Sandeep Gandhi, Consultant Guru
    Currently Being Moderated
    See Create_Project API Receive Error PA_FUNC_SECURITY_ENFORCED_AMG [ID 204432.1]     

    Getting Error Pa_func_security_enforced When Trying To Use Pa_events_pub Api [ID 555542.1]     
         
    Sandeep Gandhi
  • 3. Re: Error creating project with CREATE_PROJECT API
    Amit Singh Sisodia Newbie
    Currently Being Moderated
    Hi,
    Thanks Sandeep for the reply.
    I been through that log on metalink. It is not much helpful. I have checked with the user and responsibility.
    ------------------------------------
    Still I get this error:
    Set Global status ->S
    Task out number = 0
    Error message l_data ->Function Security determines access to this function. You are not authorized to perform this function.
    Error message l_msg_data ->PA PA_FUNC_SECURITY_ENFORCED_AMG.
    -----------------------------------

    I am able to create task from front end apps side... with same responsibility and user ... then why is this happening?

    I need some help.

    Edited by: 932550 on Jun 20, 2012 2:38 AM

    Edited by: 932550 on Jun 20, 2012 2:38 AM
  • 4. Re: Error creating project with CREATE_PROJECT API
    Sandeep Gandhi, Consultant Guru
    Currently Being Moderated
    You are not populating person_id. Check if it is required to be populated.
    You are using a from project id. Make sure you use the same project (as a template) when creating a new project manually on the screen.

    Other than that, you may have to raise an SR.

    Sandeep Gandhi
  • 5. Re: Error creating project with CREATE_PROJECT API
    Amit Singh Sisodia Newbie
    Currently Being Moderated
    Hi,
    Thanks for your reply Sandeep sir.
    Person Id is not mandatory to pass to that API.
    I have even checked with the project templates as you said.
  • 6. Re: Error creating project with CREATE_PROJECT API
    Amit Singh Sisodia Newbie
    Currently Being Moderated
    Thanks Sandeep. My program is creating projects successfully now. I had issues with user_id and responsibility_id. The user which i used didn't had all privileges.
    Thanks again.
  • 7. Re: Error creating project with CREATE_PROJECT API
    Sandeep Gandhi, Consultant Guru
    Currently Being Moderated
    I am curious. You had said earlier "I am able to create task from front end apps side... ."
    Was it with a different responsibility /user?

    Sandeep Gandhi
  • 8. Re: Error creating project with CREATE_PROJECT API
    Amit Singh Sisodia Newbie
    Currently Being Moderated
    Hi Sandeep,
    No it was with different responsibility... i have changed both user id and responsibility id for my new code... i used supervisor user id... I still dont understand why i was able to create projects on frontend with with one userid and responsibility... n couldnt create with same using api....

    can you please help me 1 more time. I need to assign project specific tasks (which i will be taking from staging table) other than the default task which are assigned during project creation. How do I proceed with this within same package. I am attaching the code of my package below...

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    CREATE OR REPLACE PACKAGE body xxpa_proj_conv_pkg as
    PROCEDURE xxpa_create_project_proc(O_ERRBUF OUT VARCHAR2,O_RETCODE OUT VARCHAR2)
    is

    variables need to derive global parameters
    v_responsibility_id NUMBER; --- PA Supervisor responsibility id
    v_user_id NUMBER;
    deriving global parameters-


    -- Variables needed for API standard parameters
    v_api_version_number NUMBER := 1.0;
    v_commit VARCHAR2(1) := 'F';
    v_return_status VARCHAR2(1);
    v_init_msg_list VARCHAR2(1) := 'F';
    v_msg_count NUMBER;
    v_msg_index_out NUMBER;
    v_msg_data VARCHAR2(2000);
    v_data VARCHAR2(2000);
    v_workflow_started VARCHAR2(1) := 'Y';
    v_pm_product_code VARCHAR2(10);


    ---variables for catching errors---
    v_error_flag number:=0;

    -- Predefined Composite data types
    v_project_in PA_PROJECT_PUB.PROJECT_IN_REC_TYPE;
    v_project_out PA_PROJECT_PUB.PROJECT_OUT_REC_TYPE;
    v_key_members PA_PROJECT_PUB.PROJECT_ROLE_TBL_TYPE;
    v_class_categories PA_PROJECT_PUB.CLASS_CATEGORY_TBL_TYPE;
    v_tasks_in_rec PA_PROJECT_PUB.TASK_IN_REC_TYPE;
    v_tasks_in PA_PROJECT_PUB.TASK_IN_TBL_TYPE;
    v_tasks_out_rec PA_PROJECT_PUB.TASK_OUT_REC_TYPE;
    v_tasks_out PA_PROJECT_PUB.TASK_OUT_TBL_TYPE;

    v_CREATED_FROM_PROJECT_ID varchar2(20);
    v_CARRYING_OUT_ORGANIZATION_ID varchar2(20);
    v_person_id NUMBER;
    v_project_role_type VARCHAR2(20);

    API_ERROR EXCEPTION;

    v_a NUMBER;


    cursor for project in data
    CURSOR cur_project_in_data IS SELECT * FROM XXPA_PROJECT_IN_STG;
    cursor for task data
    CURSOR cur_task_in_data IS SELECT * FROM XXPA_TASK_IN_STG;

    ------------------------Cursors used for validations----------------------------------
    cursor for product code used for validation

    cursor cprc is select distinct PROJECT_RELATIONSHIP_CODE from PA_PROJECT_CUSTOMERS;
    cursor for distribution rule-
    cursor cdr is select DISTRIBUTION_RULE from PA_DISTRIBUTION_RULES;
    cursor for project status code
    cursor cpsc is SELECT PROJECT_STATUS_CODE, PROJECT_STATUS_NAME FROM PA_PROJECT_STATUSES WHERE STATUS_TYPE = 'PROJECT';
    cursor for template/created from project id
    cursor ccpid is select project_id from pa_projects where template_flag='Y';
    --------------------------------------------------------------------------------------

    BEGIN

    select user_id, responsibility_id into v_user_id, v_responsibility_id
    from PA_USER_RESP_V
    where user_name like 'amit_kumar%'
    and responsibility_name like'PA SupervisorS';

    -- --Fnd_global.apps_initialize(user_id,resp_id, resp_appl_id);
    -- Fnd_global.apps_initialize(v_user_id,v_responsibility_id,275);
    --
    -- -------calling global parameters---
    --
    pa_interface_utils_pub.set_global_info
    (
    p_api_version_number =>v_api_version_number,
    p_responsibility_id =>v_responsibility_id,
    p_user_id =>v_user_id,
    p_msg_count =>v_msg_count,
    p_msg_data =>v_msg_data,
    p_return_status =>v_return_status
    );
    --
    dbms_output.put_line ('Set Global status ->' || v_return_status);


    ----Cursor for PRODUCT RELATED DATA-----------
    FOR REC IN cur_project_in_data LOOP

    -----PASSING VALUES TO THE COMPOSITE DATA TYPE(PROJECT_IN_REC_TYPE)-------

    ----retrieving product code-----
    select lookup_code into v_pm_product_code
    from pa_lookups
    where lookup_type = 'PM_PRODUCT_CODE'
    and meaning = 'Oracle Project Manufacturing';

    -----retrieving and validating created from project id----
    BEGIN
    select project_id
    into v_CREATED_FROM_PROJECT_ID
    from pa_projects_all
    where name=rec.created_from_project_name;
    EXCEPTION
    when others then
    O_Retcode := '1';
    O_Errbuf :='Incorrent CREATED_FROM_PROJECT_NAME';
    Fnd_File.Put_Line (Fnd_File.LOG, O_Errbuf);
    UPDATE XXPA.XXPA_PROJECT_IN_STG
    SET ERROR_FLAG ='1' ,last_updation_date='sysdate' where created_from_project_name = rec.CREATED_FROM_PROJECT_NAME;
    END;

    -----retrieving & validating carrying out organization id-----
    BEGIN
    select distinct(CARRYING_OUT_ORGANIZATION_ID)
    into v_CARRYING_OUT_ORGANIZATION_ID
    from pa_projects_prm_v
    where CARRYING_OUT_ORGANIZATION_NAME=rec.carrying_out_organization_name;
    EXCEPTION
    when others then
    O_Retcode := '1';
    O_Errbuf :='Incorrent Carrying Out Organization name';
    Fnd_File.Put_Line (Fnd_File.LOG, O_Errbuf);
    UPDATE XXPA.XXPA_PROJECT_IN_STG
    SET ERROR_FLAG ='1' ,last_updation_date='sysdate' where carrying_out_organization_name = rec.carrying_out_organization_name;
    END ;

    v_project_in.pm_project_reference := rec.segment1;
    v_project_in.project_name := rec.PROJECT_NAME;
    v_project_in.created_from_project_id := v_CREATED_FROM_PROJECT_ID;
    v_project_in.carrying_out_organization_id := v_CARRYING_OUT_ORGANIZATION_ID;
    v_project_in.project_status_code := rec.PROJECT_STATUS_CODE;
    v_project_in.description := rec.PROJECT_DESCRIPTION;
    v_project_in.start_date := rec.PROJECT_START_DATE;
    v_project_in.completion_date := rec.PROJECT_COMPLETION_DATE;
    v_project_in.distribution_rule := rec.DISTRIBUTION_RULE;
    v_project_in.project_relationship_code := rec.PROJECT_RELATIONSHIP_CODE;

    -------------------------Validation of incoming project data--------------------------------
    v_error_flag := 1;
    project relationship code validation
    BEGIN
    for prc in cprc
    loop
    if (rec.PROJECT_RELATIONSHIP_CODE=prc.PROJECT_RELATIONSHIP_CODE) or (rec.PROJECT_RELATIONSHIP_CODE is null)--can be overridden from template
    then
    v_error_flag :=0;
    else null;
    end if;
    end loop;
    END;

    project distribution rule validation
    BEGIN
    for dr in cdr
    loop
    if (rec.DISTRIBUTION_RULE=dr.DISTRIBUTION_RULE) or (rec.DISTRIBUTION_RULE is null) null since the value can be taken from template too
    then
    v_error_flag :=0;
    else null;
    end if;
    end loop;
    END;

    project status code validation
    BEGIN
    for sc in cpsc
    loop
    if (rec.PROJECT_STATUS_CODE=sc.PROJECT_STATUS_CODE) or (rec.PROJECT_STATUS_CODE is null) null since the value can be taken from template too
    then
    v_error_flag :=0;
    else null;
    end if;
    end loop;
    END;
    dbms_output.put_line ('Error at PROJECT_STATUS_CODE>' ||v_error_flag);

    validation logic for project start date
    BEGIN
    if TRUNC(rec.PROJECT_START_DATE) >= TRUNC(rec.PROJECT_COMPLETION_DATE)
    THEN
    v_error_flag := 1;
    O_Retcode := '1';
    O_Errbuf :='Project start date cannnot be greater than completion date';
    Fnd_File.Put_Line (Fnd_File.LOG, O_Errbuf);
    END IF;
    END;
    validation logic for project completion date
    BEGIN
    if (TRUNC(rec.PROJECT_COMPLETION_DATE)<=TRUNC(rec.PROJECT_START_DATE))
    then
    if ( rec.PROJECT_STATUS_CODE='CLOSED' and rec.PROJECT_COMPLETION_DATE>sysdate)
    THEN
    v_error_flag := 1;
    O_Retcode := '1';
    O_Errbuf :='completion date cannot be greater than sysdate for closed projects';
    Fnd_File.Put_Line (Fnd_File.LOG, O_Errbuf);
    END IF;
    v_error_flag := 1;
    O_Retcode := '1';
    O_Errbuf :='Project closed date cannnot be less than start date';
    end if;
    END;

    --------Update staging table for the error records--------
    BEGIN
    if v_error_flag =1
    then
    O_Retcode := '1';
    O_Errbuf :='Incorrent project relationship code';
    Fnd_File.Put_Line (Fnd_File.LOG, O_Errbuf);
    UPDATE XXPA.XXPA_PROJECT_IN_STG
    SET ERROR_FLAG ='1' ,last_updation_date='sysdate' where PROJECT_RELATIONSHIP_CODE = rec.PROJECT_RELATIONSHIP_CODE;
    end if;
    END;
    -----------------------End of validation of incoming project data----------------------------------
    ---------------Project Task DATA-----------------
    v_a:=0;
    FOR tsk IN cur_task_in_data LOOP

    v_tasks_in_rec.pm_task_reference :=tsk.task_reference ;
    v_tasks_in_rec.task_name :=tsk.task_name;
    v_tasks_in_rec.pm_parent_task_reference :=tsk.parent_task_reference ;
    v_tasks_in_rec.task_start_date :=tsk.task_start_date ;
    v_tasks_in_rec.task_completion_date :=tsk.task_completion_date ;
    v_tasks_in(v_a) := v_tasks_in_rec;
    v_a:=v_a+1;
    end loop;
    ---------------end of task details------------------
    --INIT_CREATE_PROJECT
    pa_project_pub.init_project;

    ---------------------CREATE_PROJECT--------------------------
    pa_project_pub.create_project(
    p_api_version_number=> v_api_version_number,
    p_commit => v_commit,
    p_init_msg_list => v_init_msg_list,
    p_msg_count => v_msg_count,
    p_msg_data => v_msg_data,
    p_return_status => v_return_status,
    p_workflow_started => v_workflow_started,
    p_pm_product_code => v_pm_product_code,
    p_project_in => v_project_in,
    p_project_out => v_project_out,
    p_key_members => v_key_members,
    p_class_categories => v_class_categories,
    p_tasks_in => v_tasks_in,
    p_tasks_out => v_tasks_out);


    if v_return_status = 'S'
    then
    UPDATE XXPA.XXPA_PROJECT_IN_STG
    SET INTERFACE_STATUS ='Success' where segment1 = v_project_out.pa_project_number; ---P->pending & S-> Success
    dbms_output.put_line('New Project Id: ' || v_project_out.pa_project_id);
    dbms_output.put_line('New Project Number: ' || v_project_out.pa_project_number);
    else
    UPDATE XXPA.XXPA_PROJECT_IN_STG
    SET INTERFACE_STATUS ='Pending' where segment1 = v_project_out.pa_project_number;
    raise API_ERROR;
    end if;



    END LOOP;

    Commit;
    ---------------------------------------------------------------
    ------Handling Exception--------



    EXCEPTION

    WHEN api_error THEN
    dbms_output.put_line('An error occured during project creation');

    IF (v_msg_count > 0 ) THEN

    FOR i IN 1..v_msg_count LOOP

    apps.PA_INTERFACE_UTILS_PUB.get_messages(
    p_msg_count => v_msg_count,
    p_encoded => 'F',
    p_msg_index => i,
    p_msg_data => v_msg_data,
    p_data => v_data,
    p_msg_index_out => v_msg_index_out);

    dbms_output.put_line('Error message v_data ->'||v_data);
    dbms_output.put_line('Error message v_msg_data ->'||v_msg_data);
    dbms_output.put_line('Error message v_msg_index_out ->'||v_msg_index_out);
    dbms_output.put_line('Error message p_msg_index ->'||i);
    APPS.fnd_file.put_line(APPS.FND_FILE.LOG,v_data);



    END LOOP;

    END IF;

    WHEN OTHERS THEN
    dbms_output.put_line('An error occured during conversion, SQLCODE ->'|| SQLERRM);

    IF (v_msg_count >=1 ) THEN

    FOR i IN 1..v_msg_count LOOP

    PA_INTERFACE_UTILS_PUB.get_messages(
    p_msg_count => v_msg_count,
    p_msg_index => i,
    p_encoded => 'F',
    p_msg_data => v_msg_data,
    p_data => v_data,
    p_msg_index_out => v_msg_index_out);

    dbms_output.put_line('Error message ->'||v_data);
    APPS.fnd_file.put_line(APPS.FND_FILE.LOG,v_data);
    END LOOP;

    END IF;

    end; --end procedure

    END xxpa_proj_conv_pkg;
    ------------------------------------------------------------------------
    Please tell me how to assign project specific task with the newly created projects???

    Also please tell me how to assign multiple * Project_Relationship_Code * (ex: END CLIENT, GENERAL CONTRACTOR, PRIMARY) for a particular project during project creation?

    Edited by: Amit Singh Sisodia on Jun 29, 2012 4:26 AM
  • 9. Re: Error creating project by PA_PROJECT_PUB.create_project API
    Sandeep Gandhi, Consultant Guru
    Currently Being Moderated
    All Project APIs are at http://docs.oracle.com/cd/B40089_10/current/acrobat/120pjapi.pdf
    Look at add_task at page 3-69

    Sandeep Gandhi

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points