API to update and assign Project Roles in an Oracle Project

Oracle has provided a seeded package called PA_PROJECT_PARTIES_PUB to create, update or delete a project party (or Key member) in an oracle project. From front end, the navigation is Project Billing Super User (or related responsibility) > Projects > Find Projects > Open > Options > Key Members. The records in the form are displayed through a view (PA_PROJECT_PLAYERS) and the base table is PA_PROJECT_PARTIES.

PA_PROJECT_PARTIES_PUB.UPDATE_PROJECT_PARTY:

/* Formatted on 3/27/2017 2:43:17 PM (QP5 v5.114.809.3010) */
DECLARE
   l_project_id           PA_PROJECT_PARTIES.PROJECT_ID%TYPE := NULL;
   l_project_role         VARCHAR2 (240) := NULL;
   l_resource_name        PER_ALL_PEOPLE_F.FULL_NAME%TYPE := NULL;
   l_start_date_active    DATE := NULL;
   l_end_date_active      DATE := NULL;
   l_project_role_id      pa_project_role_types.PROJECT_ROLE_ID%TYPE := NULL;
   l_project_role_type    pa_project_role_types.PROJECT_ROLE_TYPE%TYPE := NULL;
   l_resource_source_id   PA_PROJECT_PARTIES.RESOURCE_SOURCE_ID%TYPE := NULL;
   l_project_party_id     PA_PROJECT_PARTIES.PROJECT_PARTY_ID%TYPE := NULL;
   l_object_id            PA_PROJECT_PARTIES.OBJECT_ID%TYPE := NULL;
   l_resource_id          PA_PROJECT_PARTIES.RESOURCE_ID%TYPE := NULL;
   l_record_version_number pa_project_parties.record_version_number%TYPE
         := NULL ;
   l_project_end_date     DATE;
   l_return_status        VARCHAR2 (20) := NULL;
   l_assignment_id        NUMBER := NULL;
   l_wf_type              VARCHAR2 (240) := NULL;
   l_wf_item_type         VARCHAR2 (240) := NULL;
   l_wf_process           VARCHAR2 (240) := NULL;
   l_msg_count            NUMBER := NULL;
   l_msg_data             VARCHAR2 (240) := NULL;
BEGIN
   ---Input Parameters----
   l_project_id := '7033';
   l_project_role := 'Project Manager';
   l_resource_name := 'Koch, Dibyajyoti';
   l_start_date_active := '24-NOV-2011';
   l_end_date_active := '24-NOV-2012';

   SELECT   PROJECT_ROLE_ID, PROJECT_ROLE_TYPE
     INTO   l_project_role_id, l_project_role_type
     FROM   PA_PROJECT_ROLE_TYPES
    WHERE   UPPER (MEANING) = UPPER (l_project_role);

   SELECT   DISTINCT PERSON_ID
     INTO   l_resource_source_id
     FROM   PER_ALL_PEOPLE_F
    WHERE   UPPER (FULL_NAME) = UPPER (l_resource_name);

   SELECT   PROJECT_PARTY_ID,
            OBJECT_ID,
            RESOURCE_ID,
            RECORD_VERSION_NUMBER
     INTO   l_project_party_id,
            l_object_id,
            l_resource_id,
            l_record_version_number
     FROM   PA_PROJECT_PARTIES
    WHERE       PROJECT_ID = l_project_id
            AND PROJECT_ROLE_ID = l_project_role_id
            AND RESOURCE_SOURCE_ID = l_resource_source_id;

   l_project_end_date :=
      pa_project_dates_utils.get_project_finish_date (l_project_id);

   PA_PROJECT_PARTIES_PUB.UPDATE_PROJECT_PARTY (
      P_API_VERSION                    => 1.0,
      P_INIT_MSG_LIST                  => FND_API.G_TRUE,
      P_COMMIT                         => FND_API.G_FALSE,
      P_VALIDATE_ONLY                  => FND_API.G_FALSE,
      P_VALIDATION_LEVEL               => FND_API.G_VALID_LEVEL_FULL,
      P_DEBUG_MODE                     => 'N',
      P_OBJECT_ID                      => l_object_id,
      P_OBJECT_TYPE                    => 'PA_PROJECTS',
      P_PROJECT_ROLE_ID                => l_project_role_id,
      P_PROJECT_ROLE_TYPE              => l_project_role_type,
      P_RESOURCE_TYPE_ID               => 101,                      --EMPLOYEE
      P_RESOURCE_SOURCE_ID             => l_resource_source_id,
      P_RESOURCE_NAME                  => l_resource_name,
      P_RESOURCE_ID                    => l_resource_id,
      P_START_DATE_ACTIVE              => l_start_date_active,
      P_SCHEDULED_FLAG                 => 'N',
      P_RECORD_VERSION_NUMBER          => l_record_version_number,
      P_CALLING_MODULE                 => FND_API.G_MISS_CHAR,
      P_PROJECT_ID                     => l_project_id,
      P_PROJECT_END_DATE               => l_project_end_date,
      P_PROJECT_PARTY_ID               => l_project_party_id,
      P_ASSIGNMENT_ID                  => NULL,
      P_ASSIGN_RECORD_VERSION_NUMBER   => l_record_version_number + 1,
      P_MGR_VALIDATION_TYPE            => 'FORM',
      P_END_DATE_ACTIVE                => l_end_date_active,
      X_ASSIGNMENT_ID                  => l_assignment_id,
      X_WF_TYPE                        => l_wf_type,
      X_WF_ITEM_TYPE                   => l_wf_item_type,
      X_WF_PROCESS                     => l_wf_process,
      X_RETURN_STATUS                  => l_return_status,
      X_MSG_COUNT                      => l_msg_count,
      x_msg_data                       => l_msg_data
   );
   COMMIT;
   DBMS_OUTPUT.PUT_LINE ('Status:' || l_return_status);
   DBMS_OUTPUT.PUT_LINE ('Message:' || l_msg_data);
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE ('Try Again!!');
END;

PA_PROJECT_PARTIES_PUB.CREATE_PROJECT_PARTY:

/* Formatted on 3/27/2017 2:44:48 PM (QP5 v5.114.809.3010) */
DECLARE
   l_project_id           PA_PROJECT_PARTIES.PROJECT_ID%TYPE := NULL;
   l_project_role         VARCHAR2 (240) := NULL;
   l_resource_name        PER_ALL_PEOPLE_F.FULL_NAME%TYPE := NULL;
   l_start_date_active    DATE := NULL;
   l_end_date_active      DATE := NULL;
   l_project_role_id      pa_project_role_types.PROJECT_ROLE_ID%TYPE := NULL;
   l_project_role_type    pa_project_role_types.PROJECT_ROLE_TYPE%TYPE := NULL;
   l_resource_source_id   PA_PROJECT_PARTIES.RESOURCE_SOURCE_ID%TYPE := NULL;
   l_project_party_id     PA_PROJECT_PARTIES.PROJECT_PARTY_ID%TYPE := NULL;
   l_object_id            PA_PROJECT_PARTIES.OBJECT_ID%TYPE := NULL;
   l_resource_id          PA_PROJECT_PARTIES.RESOURCE_ID%TYPE := NULL;
   l_record_version_number pa_project_parties.record_version_number%TYPE
         := NULL ;
   l_project_end_date     DATE;
   l_return_status        VARCHAR2 (20) := NULL;
   l_assignment_id        NUMBER := NULL;
   l_wf_type              VARCHAR2 (240) := NULL;
   l_wf_item_type         VARCHAR2 (240) := NULL;
   l_wf_process           VARCHAR2 (240) := NULL;
   l_msg_count            NUMBER := NULL;
   l_msg_data             VARCHAR2 (240) := NULL;
BEGIN
   ---Input Parameters----
   l_project_id := '7033';
   l_project_role := 'Project Accountant';
   l_resource_name := 'Koch, Dibyajyoti';
   l_start_date_active := '24-NOV-2011';
   l_end_date_active := '24-NOV-2012';

   SELECT   PROJECT_ROLE_ID, PROJECT_ROLE_TYPE
     INTO   l_project_role_id, l_project_role_type
     FROM   PA_PROJECT_ROLE_TYPES
    WHERE   UPPER (MEANING) = UPPER (l_project_role);

   SELECT   DISTINCT PERSON_ID
     INTO   l_resource_source_id
     FROM   PER_ALL_PEOPLE_F
    WHERE   UPPER (FULL_NAME) = UPPER (l_resource_name);

   l_project_end_date :=
      pa_project_dates_utils.get_project_finish_date (l_project_id);

   PA_PROJECT_PARTIES_PUB.CREATE_PROJECT_PARTY (
      P_API_VERSION           => 1.0,
      P_INIT_MSG_LIST         => FND_API.G_TRUE,
      P_COMMIT                => FND_API.G_FALSE,
      P_VALIDATE_ONLY         => FND_API.G_FALSE,
      P_VALIDATION_LEVEL      => FND_API.G_VALID_LEVEL_FULL,
      P_DEBUG_MODE            => 'N',
      P_OBJECT_ID             => l_project_id,
      P_OBJECT_TYPE           => 'PA_PROJECTS',
      P_PROJECT_ROLE_ID       => l_project_role_id,
      P_PROJECT_ROLE_TYPE     => l_project_role_type,
      P_RESOURCE_TYPE_ID      => 101,                               --EMPLOYEE
      P_RESOURCE_SOURCE_ID    => l_resource_source_id,
      P_RESOURCE_NAME         => l_resource_name,
      P_START_DATE_ACTIVE     => l_start_date_active,
      P_SCHEDULED_FLAG        => 'N',
      P_CALLING_MODULE        => NULL,
      P_PROJECT_ID            => l_project_id,
      P_PROJECT_END_DATE      => l_project_end_date,
      P_MGR_VALIDATION_TYPE   => 'FORM',
      P_END_DATE_ACTIVE       => l_end_date_active,
      X_PROJECT_PARTY_ID      => l_project_party_id,
      X_RESOURCE_ID           => l_resource_id,
      X_ASSIGNMENT_ID         => l_assignment_id,
      X_WF_TYPE               => l_wf_type,
      X_WF_ITEM_TYPE          => l_wf_item_type,
      X_WF_PROCESS            => l_wf_process,
      X_RETURN_STATUS         => l_return_status,
      X_MSG_COUNT             => l_msg_count,
      X_MSG_DATA              => l_msg_data
   );
   COMMIT;
   DBMS_OUTPUT.PUT_LINE ('Status:' || l_return_status);
   DBMS_OUTPUT.PUT_LINE ('Message:' || l_msg_data);
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE ('Try Again!!');
END;  

Project & Change Order Approval Action History in oracle projcts query

/* Formatted on 3/27/2017 2:42:17 PM (QP5 v5.114.809.3010) */
SELECT   ROWNUM,
         ACTION_DATE,
         ACTION,
         from_user,
         from_role,
         to_user,
         to_role,
         Details,
         SEQUENCE,
         NOTIFICATION_ID,
         ACTION_TYPE
  FROM   (  SELECT   ACTION_DATE,
                     ACTION,
                     from_user,
                     from_role,
                     to_user,
                     to_role,
                     Details,
                     SEQUENCE,
                     NOTIFICATION_ID,
                     ACTION_TYPE
              FROM   (SELECT   c.comment_date DATE1,
                               TO_CHAR (c.comment_date,
                                        'DD-MON-RRRR HH24:MI:SS')
                                  action_date,
                               c.action action,
                               c.from_user from_user,
                               c.from_role from_role,
                               c.to_user to_user,
                               c.to_role to_role,
                               c.user_comment Details,
                               C.SEQUENCE SEQUENCE,
                               C.NOTIFICATION_ID NOTIFICATION_ID,
                               C.ACTION_TYPE ACTION_TYPE
                        FROM   WF_NOTIFICATIONS WFN,
                               pa_wf_processes pa,
                               wf_item_activity_statuses wfitems,
                               wf_comments c
                       WHERE       wfitems.notification_id = wfn.GROUP_ID
                               AND wfitems.NOTIFICATION_ID = C.NOTIFICATION_ID
                               AND pa.item_type = wfitems.item_type
                               AND pa.item_key = wfitems.item_key
                               AND pa.entity_key1 = :p_project_id
                               AND pa.item_type IN ('PAPROWF', 'PAWFCISC')
                      UNION ALL
                      SELECT   c.comment_date DATE1,
                               TO_CHAR (c.comment_date,
                                        'DD-MON-RRRR HH24:MI:SS')
                                  action_date,
                               c.action action,
                               c.from_user from_user,
                               c.from_role from_role,
                               c.to_user to_user,
                               c.to_role to_role,
                               c.user_comment Details,
                               C.SEQUENCE SEQUENCE,
                               C.NOTIFICATION_ID NOTIFICATION_ID,
                               C.ACTION_TYPE ACTION_TYPE
                        FROM   WF_NOTIFICATIONS WFN,
                               pa_wf_processes pa,
                               WF_ITEM_ACTIVITY_STATUSES_H wfitems,
                               wf_comments c
                       WHERE       wfitems.notification_id = wfn.GROUP_ID
                               AND wfitems.NOTIFICATION_ID = C.NOTIFICATION_ID
                               AND pa.item_type = wfitems.item_type
                               AND pa.item_key = wfitems.item_key
                               AND pa.entity_key1 = :p_project_id
                               AND pa.item_type IN ('PAPROWF', 'PAWFCISC'))
          ORDER BY   DATE1 DESC, notification_id, sequence);

Financial Plan CI Impact in oracle projects query

/* Formatted on 3/27/2017 2:41:06 PM (QP5 v5.114.809.3010) */
SELECT   pbv.ci_id,
         NVL (pe.name, p.name) task_name,
         rlm.alias planning_resource_name,
         (NVL (rac.total_projfunc_burdened_cost, 0) + NVL (NULL, 0))
            total_cost,
         DECODE (pfpt.plan_class_code,
                 'FORECAST', TO_NUMBER (NULL),
                 rac.total_txn_raw_cost)
            AS raw_cost_txn_cur,
         ROUND (
            DECODE (pra.rate_based_flag,
                    'Y', rac.txn_raw_cost_rate_override,
                    TO_NUMBER (NULL)),
            5
         )
            AS raw_cost_rate_override,
         DECODE (pfpt.plan_class_code,
                 'FORECAST', TO_NUMBER (NULL),
                 rac.total_projfunc_raw_cost)
            AS raw_cost_proj_func_cur,
         ROUND (
            DECODE (pra.rate_based_flag,
                    'Y', rac.txn_average_burden_cost_rate,
                    TO_NUMBER (NULL)),
            5
         )
            AS avg_burd_cost_rate,
         ROUND (
            DECODE (pra.rate_based_flag,
                    'Y', rac.txn_burden_cost_rate_override,
                    TO_NUMBER (NULL)),
            5
         )
            AS burd_cost_rate_override,
         DECODE (pfpt.plan_class_code,
                 'FORECAST', TO_NUMBER (NULL),
                 rac.total_projfunc_burdened_cost)
            AS burd_cost_proj_func_cur,
         DECODE (pfpt.plan_class_code,
                 'FORECAST', TO_NUMBER (NULL),
                 rac.total_txn_burdened_cost)
            AS burd_cost_txn_cur,
            NVL (pe.element_number, p.segment1)
         || '('
         || NVL (pe.name, p.name)
         || ')'
            task_name_num,
         NVL (ppe.element_version_id, 0) element_version_id
  FROM   pa_resource_assignments pra,
         pa_proj_elements pe,
         pa_resource_list_members rlm,
         pa_resource_asgn_curr rac,
         pa_budget_versions pbv,
         pa_fin_plan_types_b pfpt,
         pa_proj_fp_options po,
         pa_proj_element_versions ppe,
         pa_control_items ci,
         pa_projects_all p
 WHERE       pra.resource_assignment_id = rac.resource_assignment_id
         AND pra.resource_list_member_id = rlm.resource_list_member_id
         AND p.project_id = :p_project_id
         AND pra.project_id = p.project_id
         AND pbv.budget_version_id = rac.budget_version_id
         AND pbv.budget_version_id = pra.budget_version_id
         AND pbv.project_id = pra.project_id
         AND pbv.ci_id = ci.ci_id
         AND pbv.ci_id = :p_change_order_id
         AND pbv.fin_plan_type_id = pfpt.fin_plan_type_id
         AND pbv.budget_version_id = po.fin_plan_version_id
         AND po.fin_plan_option_level_code = 'PLAN_VERSION'
         AND po.fin_plan_version_id = pbv.budget_version_id
         AND pra.task_id = pe.proj_element_id(+)
         AND ppe.parent_structure_version_id(+) =
               pa_project_structure_utils.get_fin_struc_ver_id (
                  pe.project_id
               )
         AND pe.proj_element_id = ppe.proj_element_id(+)
         AND (   rac.total_quantity IS NOT NULL
              OR rac.txn_burden_cost_rate_override IS NOT NULL
              OR rac.total_txn_burdened_cost IS NOT NULL
              OR rac.txn_bill_rate_override IS NOT NULL
              OR rac.total_txn_revenue IS NOT NULL
              OR rac.txn_raw_cost_rate_override IS NOT NULL
              OR rac.total_txn_raw_cost IS NOT NULL);

Oracle projects Budget Planning Element Information query

/* Formatted on 3/27/2017 2:39:29 PM (QP5 v5.114.809.3010) */
SELECT   NVL (pe.name, p.name) AS task_name,
         rlm.alias AS planning_resource,
         ra.TOTAL_PLAN_RAW_COST
  FROM   pa_budget_versions bv,
         pa_fin_plan_types_b pt,
         pa_proj_fp_options po,
         pa_resource_assignments ra,
         pa_resource_list_members rlm,
         pa_projects_all p,
         pa_proj_elements pe,
         pa_proj_element_versions pev
 WHERE   bv.budget_version_id =
            (SELECT   budget_version_id
               FROM   pa_budget_versions
              WHERE   project_id = :p_project_id
                      AND fin_plan_type_id =
                            (SELECT   fin_plan_type_id
                               FROM   pa_fin_plan_types_vl
                              WHERE   migrated_frm_bdgt_typ_code = 'AC'
                                      AND UPPER (NAME) =
                                            UPPER ('Approved Cost Budget'))
                      AND budget_status_code = 'B'
                      AND current_flag = 'Y')
         AND bv.project_id = p.project_id
         AND bv.fin_plan_type_id = pt.fin_plan_type_id
         AND bv.budget_version_id = po.fin_plan_version_id
         AND po.fin_plan_option_level_code = 'PLAN_VERSION'
         AND po.fin_plan_version_id = bv.budget_version_id
         AND bv.budget_version_id = ra.budget_version_id
         AND ra.resource_list_member_id = rlm.resource_list_member_id
         AND ra.task_id = pev.proj_element_id(+)
         AND pev.parent_structure_version_id(+) =
               pa_planning_element_utils.get_fin_struct_id (
                  ra.project_id,
                  ra.budget_version_id
               )
         AND pev.proj_element_id = pe.proj_element_id(+);

Approved Cost Budget Version Detail in oracle projects

/* Formatted on 3/27/2017 2:37:59 PM (QP5 v5.114.809.3010) */
SELECT   po.project_id,
         bv.version_name,
         pt.name AS plan_type_name,
         bv.description,
         po.fin_plan_preference_code,
         bv.budget_version_id,
         bv.record_version_number,
         bv.budget_status_code,
         bv.raw_cost Row_Cost_Total
  FROM   pa_proj_fp_options po,
         pa_fin_plan_types_vl pt,
         pa_budget_versions bv
 WHERE   bv.project_id = :p_project_id
         AND bv.budget_version_id =
               (SELECT   budget_version_id
                  FROM   pa_budget_versions
                 WHERE   project_id = :p_project_id
                         AND fin_plan_type_id =
                               (SELECT   fin_plan_type_id
                                  FROM   pa_fin_plan_types_vl
                                 WHERE   migrated_frm_bdgt_typ_code = 'AC'
                                         AND UPPER (NAME) =
                                               UPPER ('Approved Cost Budget'))
                         AND budget_status_code = 'B'
                         AND CURRENT_FLAG = 'Y')
         AND bv.budget_version_id = po.fin_plan_version_id
         AND bv.ci_id IS NULL
         AND po.fin_plan_option_level_code = 'PLAN_VERSION'
         AND po.fin_plan_type_id = pt.fin_plan_type_id
         AND pt.fin_plan_type_id =
               (SELECT   fin_plan_type_id
                  FROM   pa_fin_plan_types_vl
                 WHERE   migrated_frm_bdgt_typ_code = 'AC'
                         AND UPPER (name) = UPPER ('Approved Cost Budget'));

Project Parties (Project Key Members) query in R12

/* Formatted on 3/27/2017 2:37:02 PM (QP5 v5.114.809.3010) */
SELECT   *
  FROM   (SELECT   DISTINCT
                   PPP.PROJECT_ID project_id,
                   DECODE (PA.ASSIGNMENT_ID,
                           NULL, PPRT.MEANING,
                           PA.ASSIGNMENT_NAME)
                      project_role_meaning,
                   PPP.RESOURCE_SOURCE_ID resource_source_id,
                   PE.FULL_NAME resource_source_name,
                   PPP.PROJECT_ROLE_ID project_role_id,
                   PPRT.PROJECT_ROLE_TYPE project_role_type,
                   PPP.START_DATE_ACTIVE start_date_active,
                   ppp.end_date_active end_date_active,
                   pa_project_parties_utils.active_party (
                      ppp.start_date_active,
                      ppp.end_date_active
                   )
                      active,
                   'EMPLOYEE' party_type
            FROM   PA_PROJECT_PARTIES PPP,
                   PA_PROJECTS_ALL PPA,
                   PA_PROJECT_ROLE_TYPES PPRT,
                   PER_ALL_PEOPLE_F PE,
                   PA_PROJECT_ASSIGNMENTS PA,
                   fnd_user u,
                   (SELECT   pj.name job_name,
                             haou.organization_id org_id,
                             haou.name org_name,
                             paf.person_id,
                             paf.assignment_type
                      FROM   per_all_assignments_f paf,
                             per_jobs pj,
                             hr_all_organization_units haou
                     WHERE   TRUNC (SYSDATE) BETWEEN TRUNC(paf.effective_start_date)
                                                 AND  TRUNC(paf.effective_end_date)
                             AND paf.primary_flag = 'Y'
                             AND paf.organization_id = haou.organization_id
                             AND NVL (paf.job_id, -99) = pj.job_id(+)) prd
           WHERE       PPP.RESOURCE_TYPE_ID = 101
                   AND PPP.PROJECT_ID = PPA.PROJECT_ID
                   AND PPP.PROJECT_ROLE_ID = PPRT.PROJECT_ROLE_ID
                   AND PPP.RESOURCE_SOURCE_ID = PE.PERSON_ID
                   AND PE.EFFECTIVE_START_DATE =
                         (SELECT   MIN (PAPF.EFFECTIVE_START_DATE)
                            FROM   PER_ALL_PEOPLE_F PAPF
                           WHERE   PAPF.PERSON_ID = PE.PERSON_ID
                                   AND PAPF.EFFECTIVE_END_DATE >=
                                         TRUNC (SYSDATE))
                   AND PE.EFFECTIVE_END_DATE >= TRUNC (SYSDATE)
                   AND PPP.PROJECT_PARTY_ID = PA.PROJECT_PARTY_ID(+)
                   AND NVL (prd.assignment_type, '-99') IN
                            ('C',
                             DECODE (
                                DECODE (
                                   PE.CURRENT_EMPLOYEE_FLAG,
                                   'Y',
                                   'Y',
                                   DECODE (PE.CURRENT_NPW_FLAG,
                                           'Y', 'Y',
                                           'N')
                                ),
                                'Y',
                                'E',
                                'B'
                             ),
                             'E',
                             '-99')
                   AND ppp.resource_source_id = prd.person_id(+)
                   AND u.employee_id(+) = ppp.resource_source_id
                   AND ppp.object_type = 'PA_PROJECTS'
                   AND ppp.object_id = ppa.project_id
          UNION ALL
          SELECT   DISTINCT
                   ppp.project_id,
                   pprt.meaning,
                   ppp.resource_source_id,
                   hzp.party_name,
                   ppp.project_role_id,
                   pprt.project_role_type,
                   ppp.start_date_active,
                   ppp.end_date_active,
                   pa_project_parties_utils.active_party (
                      ppp.start_date_active,
                      ppp.end_date_active
                   ),
                   'PERSON'
            FROM   pa_project_parties ppp,
                   pa_projects_all ppa,
                   pa_project_role_types pprt,
                   hz_parties hzp,
                   hz_parties hzo,
                   hz_relationships hzr,
                   hz_contact_points hzcp,
                   fnd_user u
           WHERE       ppp.resource_type_id = 112
                   AND ppp.project_id = ppa.project_id
                   AND ppp.project_role_id = pprt.project_role_id
                   AND ppp.resource_source_id = hzp.party_id
                   AND hzp.party_type = 'PERSON'
                   AND hzo.party_type = 'ORGANIZATION'
                   AND hzr.relationship_code IN ('EMPLOYEE_OF', 'CONTACT_OF')
                   AND hzr.status = 'A'
                   AND hzr.subject_id = hzp.party_id
                   AND hzr.object_id = hzo.party_id
                   AND hzr.object_table_name = 'HZ_PARTIES'
                   AND hzr.directional_flag = 'F'
                   AND hzcp.owner_table_name(+) = 'HZ_PARTIES'
                   AND hzcp.owner_table_id(+) = hzp.party_id
                   AND hzcp.contact_point_type(+) = 'PHONE'
                   AND hzcp.phone_line_type(+) = 'GEN'
                   AND hzcp.primary_flag(+) = 'Y'
                   AND u.person_party_id(+) = ppp.resource_source_id
                   AND ppp.object_type = 'PA_PROJECTS'
                   AND ppp.object_id = ppa.project_id
          UNION ALL
          SELECT   DISTINCT
                   ppp.project_id,
                   pprt.meaning,
                   ppp.resource_source_id,
                   hzo.party_name,
                   ppp.project_role_id,
                   pprt.project_role_type,
                   ppp.start_date_active,
                   ppp.end_date_active,
                   pa_project_parties_utils.active_party (
                      ppp.start_date_active,
                      ppp.end_date_active
                   ),
                   'ORGANIZATION'
            FROM   pa_project_parties ppp,
                   pa_projects_all ppa,
                   pa_project_role_types_vl pprt,
                   hz_parties hzo,
                   hz_contact_points hzcp
           WHERE       ppp.resource_type_id = 112
                   AND ppp.project_id = ppa.project_id
                   AND ppp.project_role_id = pprt.project_role_id
                   AND ppp.resource_source_id = hzo.party_id
                   AND hzo.party_type = 'ORGANIZATION'
                   AND hzcp.owner_table_name(+) = 'HZ_PARTIES'
                   AND hzcp.owner_table_id(+) = hzo.party_id
                   AND hzcp.contact_point_type(+) = 'PHONE'
                   AND hzcp.phone_line_type(+) = 'GEN'
                   AND hzcp.primary_flag(+) = 'Y'
                   AND ppp.object_type = 'PA_PROJECTS'
                   AND ppp.object_id = ppa.project_id
          UNION ALL
          SELECT   ppc.project_id,
                   'Customer Person' meaning,
                   NULL,
                   ppc.customer_name,
                   NULL,
                   NULL,
                   NULL,
                   NULL,
                   DECODE (ppc.customer_status,
                           'A',
                           'Y',
                           'I',
                           'N'),
                   'ORGANIZATION' party_type
            FROM   pa_project_customers_v ppc,
                   hz_parties hzo,
                   hz_contact_points hzcp
           WHERE       hzcp.owner_table_name(+) = 'HZ_PARTIES'
                   AND hzcp.owner_table_id(+) = hzo.party_id
                   AND hzcp.contact_point_type(+) = 'PHONE'
                   AND hzcp.phone_line_type(+) = 'GEN'
                   AND hzcp.primary_flag(+) = 'Y'
                   AND ppc.party_type = 'PERSON'
                   AND ppc.party_id = hzo.party_id)
 WHERE   (project_id = :p_project_id AND party_type <> 'ORGANIZATION'
          AND TRUNC (SYSDATE) BETWEEN start_date_active
                                  AND  NVL (end_date_active, TRUNC (SYSDATE)));

Project Basic Info qurey in R12

/* Formatted on 3/27/2017 2:35:45 PM (QP5 v5.114.809.3010) */
SELECT   proj.carrying_out_organization_name project_organization,
         proj.segment1 project_number,
         proj.name Project_Short_Name,
         proj.city,
         proj.region,
         proj.long_name project_long_name,
         proj.start_date transaction_start_date,
         proj.target_finish_date Target_Finish_Date,
         proj.project_type,
         proj.project_type_class_code,
         proj.completion_date,
         proj.public_sector_flag,
         proj.project_status_name,
         proj.project_status_code,
         proj.wf_status_code,
         proj.country_name,
         proj.country_code,
         proj.record_version_number,
         proj.target_start_date,
         proj.scheduled_start_date,
         proj.scheduled_finish_date,
         proj.actual_start_date,
         proj.actual_finish_date,
         opr.name operating_unit
  FROM   pa_projects_prm_v proj, hr_all_organization_units_vl opr
 WHERE   proj.org_id = opr.organization_id
         AND proj.project_id = :p_project_id;

Query to find Project Manager Info of an Oracle Project

/* Formatted on 3/27/2017 2:34:34 PM (QP5 v5.114.809.3010) */
SELECT   PE.FULL_NAME, PE.EMAIL_ADDRESS
  FROM   PA_PROJECT_PARTIES PPP,
         PA_PROJECTS_ALL PPA,
         PA_PROJECT_ROLE_TYPES PPRT,
         PER_ALL_PEOPLE_F PE,
         PA_PROJECT_ASSIGNMENTS PA,
         FND_USER U,
         (SELECT   PJ.NAME JOB_NAME,
                   HAOU.ORGANIZATION_ID ORG_ID,
                   HAOU.NAME ORG_NAME,
                   PAF.PERSON_ID,
                   PAF.ASSIGNMENT_TYPE
            FROM   PER_ALL_ASSIGNMENTS_F PAF,
                   PER_JOBS PJ,
                   HR_ALL_ORGANIZATION_UNITS HAOU
           WHERE   TRUNC (SYSDATE) BETWEEN TRUNC (PAF.EFFECTIVE_START_DATE)
                                       AND  TRUNC (PAF.EFFECTIVE_END_DATE)
                   AND PAF.PRIMARY_FLAG = 'Y'
                   AND PAF.ORGANIZATION_ID = HAOU.ORGANIZATION_ID
                   AND NVL (PAF.JOB_ID, -99) = PJ.JOB_ID(+)) PRD
 WHERE       PPA.PROJECT_ID = :P_PROJECT_ID
         AND DECODE (PA.ASSIGNMENT_ID,
                     NULL, PPRT.MEANING,
                     PA.ASSIGNMENT_NAME) = 'Project Manager'
         AND PPP.PROJECT_ID = PPA.PROJECT_ID
         AND PPP.PROJECT_ROLE_ID = PPRT.PROJECT_ROLE_ID
         AND PPP.RESOURCE_SOURCE_ID = PE.PERSON_ID
         AND TRUNC (SYSDATE) BETWEEN TRUNC (PPP.START_DATE_ACTIVE)
                                 AND  NVL (TRUNC (PPP.END_DATE_ACTIVE),
                                           SYSDATE)
         AND PE.EFFECTIVE_START_DATE =
               (SELECT   MIN (PAPF.EFFECTIVE_START_DATE)
                  FROM   PER_ALL_PEOPLE_F PAPF
                 WHERE   PAPF.PERSON_ID = PE.PERSON_ID
                         AND PAPF.EFFECTIVE_END_DATE >= TRUNC (SYSDATE))
         AND PE.EFFECTIVE_END_DATE >= TRUNC (SYSDATE)
         AND PPP.PROJECT_PARTY_ID = PA.PROJECT_PARTY_ID(+)
         AND NVL (PRD.ASSIGNMENT_TYPE, '-99') IN
                  ('C',
                   DECODE (
                      DECODE (PE.CURRENT_EMPLOYEE_FLAG,
                              'Y', 'Y',
                              DECODE (PE.CURRENT_NPW_FLAG, 'Y', 'Y', 'N')),
                      'Y',
                      'E',
                      'B'
                   ),
                   'E',
                   '-99')
         AND PPP.RESOURCE_SOURCE_ID = PRD.PERSON_ID(+)
         AND U.EMPLOYEE_ID(+) = PPP.RESOURCE_SOURCE_ID
         AND PPP.OBJECT_TYPE = 'PA_PROJECTS'
         AND ppp.object_id = ppa.project_id;

API to add Classification to an Oracle Project

/* Formatted on 3/27/2017 2:33:29 PM (QP5 v5.114.809.3010) */
DECLARE
   l_project_id       pa_projects_all.project_id%TYPE := NULL;
   l_class_category   pa_project_classes.class_category%TYPE := NULL;
   l_class_code       pa_project_classes.class_code%TYPE := NULL;
   l_return_status    VARCHAR2 (20);
   l_msg_count        NUMBER;
   l_msg_data         VARCHAR2 (240);
BEGIN
   L_PROJECT_ID := &P_PROJECT_ID;
   L_CLASS_CATEGORY := &P_CLASS_CATEGORY;
   l_class_code := &p_class_code;
   pa_projects_maint_pub.create_classifications (
      p_api_version          => 1.0,
      p_init_msg_list        => fnd_api.g_true,
      p_commit               => fnd_api.g_false,
      p_validate_only        => fnd_api.g_false,
      p_validation_level     => fnd_api.g_valid_level_full,
      p_calling_module       => 'SELF_SERVICE',
      p_debug_mode           => 'N',
      p_max_msg_count        => fnd_api.g_miss_num,
      p_object_id            => l_project_id,
      p_object_type          => 'PA_PROJECTS',
      p_class_category       => l_class_category,
      p_class_code           => l_class_code,
      p_code_percentage      => fnd_api.g_miss_num,
      p_attribute_category   => fnd_api.g_miss_char,
      p_attribute1           => fnd_api.g_miss_char,
      p_attribute2           => fnd_api.g_miss_char,
      p_attribute3           => fnd_api.g_miss_char,
      p_attribute4           => fnd_api.g_miss_char,
      p_attribute5           => fnd_api.g_miss_char,
      p_attribute6           => fnd_api.g_miss_char,
      p_attribute7           => fnd_api.g_miss_char,
      p_attribute8           => fnd_api.g_miss_char,
      p_attribute9           => fnd_api.g_miss_char,
      p_attribute10          => fnd_api.g_miss_char,
      p_attribute11          => fnd_api.g_miss_char,
      p_attribute12          => fnd_api.g_miss_char,
      p_attribute13          => fnd_api.g_miss_char,
      p_attribute14          => fnd_api.g_miss_char,
      p_attribute15          => fnd_api.g_miss_char,
      x_return_status        => l_return_status,
      x_msg_count            => l_msg_count,
      x_msg_data             => l_msg_data
   );
   COMMIT;

   DBMS_OUTPUT.put_line ('Status:' || l_return_status);
   DBMS_OUTPUT.put_line ('Message:' || l_msg_data);
END;

API to Update Task Information in Oracle Projects

/* Formatted on 3/27/2017 2:32:15 PM (QP5 v5.114.809.3010) */
DECLARE
   l_return_status            VARCHAR (10);
   l_msg_count                VARCHAR (240);
   l_MSG_DATA                 VARCHAR (240);
   l_rowid                    VARCHAR2 (240);
   task_record                PA_TASKS%ROWTYPE;
   task_struc_record          PA_PROJ_ELEMENTS%ROWTYPE;
   l_last_updated_by          NUMBER := FND_GLOBAL.USER_ID;
   l_last_update_date         DATE;
   l_last_update_login        NUMBER := FND_GLOBAL.LOGIN_ID;
   pt_task_name               PA_TASKS.TASK_NAME%TYPE;
   l_task_name                PA_PROJ_ELEMENTS.NAME%TYPE;
   l_task_name1               PA_PROJ_ELEMENTS.NAME%TYPE;
   l_project_id               PA_PROJECTS_ALL.PROJECT_ID%TYPE;
   l_task_id                  PA_TASKS.TASK_ID%TYPE;
   l_task_manager_person_id   PA_TASKS.TASK_MANAGER_PERSON_ID%TYPE;
   l_output                   VARCHAR2 (2000);
   l_msg_dummy                VARCHAR2 (2000);
   n                          NUMBER := 0;
BEGIN
   l_project_id := 590;
   l_task_id := 3355;
   l_task_manager_person_id := 136263;                      --Koch, Dibyajyoti

   BEGIN
      SELECT   *
        INTO   task_record
        FROM   pa_tasks
       WHERE   project_id = l_project_id AND task_id = l_task_id;
   EXCEPTION
      WHEN OTHERS
      THEN
         NULL;
   END;

   BEGIN
      SELECT   *
        INTO   task_struc_record
        FROM   pa_proj_elements
       WHERE   PROJECT_ID = l_project_id AND PROJ_ELEMENT_ID = l_task_id;
   EXCEPTION
      WHEN OTHERS
      THEN
         NULL;
   END;

   BEGIN
      SELECT   ROWID
        INTO   l_rowid
        FROM   pa_tasks
       WHERE   project_id = l_project_id AND task_id = l_task_id;
   EXCEPTION
      WHEN OTHERS
      THEN
         NULL;
   END;

   BEGIN
      SELECT   SYSDATE INTO l_last_update_date FROM DUAL;
   EXCEPTION
      WHEN OTHERS
      THEN
         NULL;
   END;

   BEGIN
      SELECT   task_name
        INTO   pt_task_name
        FROM   pa_tasks
       WHERE   task_id = l_task_id;

      SELECT   name
        INTO   l_task_name
        FROM   pa_proj_elements
       WHERE   proj_element_id = l_task_id;

      IF pt_task_name = l_task_name
      THEN
         l_task_name1 := l_task_name;
      ELSE
         l_task_name1 := pt_task_name;
      END IF;
   EXCEPTION
      WHEN OTHERS
      THEN
         NULL;
   END;

   PA_TASKS_PKG.UPDATE_ROW (
      X_ROWID                          => l_rowid,
      X_TASK_ID                        => task_record.task_id,
      X_PROJECT_ID                     => task_record.project_id,
      X_TASK_NUMBER                    => task_record.task_number,
      X_LAST_UPDATE_DATE               => l_last_update_date,  --Updated Value
      X_LAST_UPDATED_BY                => l_last_updated_by,   --Updated Value
      X_LAST_UPDATE_LOGIN              => l_last_update_login, --Updated Value
      X_Task_Name                      => task_record.task_name,
      X_Long_Task_Name                 => task_record.long_task_name,
      X_TOP_TASK_ID                    => task_record.top_task_id,
      X_WBS_LEVEL                      => task_record.wbs_level,
      X_READY_TO_BILL_FLAG             => task_record.ready_to_bill_flag,
      X_READY_TO_DISTRIBUTE_FLAG       => task_record.ready_to_distribute_flag,
      X_PARENT_TASK_ID                 => task_record.parent_task_id,
      X_DESCRIPTION                    => task_record.description,
      X_CARRYING_OUT_ORGANIZATION_ID   => task_record.carrying_out_organization_id,
      X_SERVICE_TYPE_CODE              => task_record.service_type_code,
      X_TASK_MANAGER_PERSON_ID         => l_task_manager_person_id, --Updated Value
      X_CHARGEABLE_FLAG                => task_record.chargeable_flag,
      X_BILLABLE_FLAG                  => task_record.billable_flag,
      X_LIMIT_TO_TXN_CONTROLS_FLAG     => task_record.limit_to_txn_controls_flag,
      X_START_DATE                     => task_record.start_date,
      X_COMPLETION_DATE                => task_record.completion_date,
      X_ADDRESS_ID                     => task_record.address_id,
      X_LABOR_BILL_RATE_ORG_ID         => task_record.labor_bill_rate_org_id,
      X_LABOR_STD_BILL_RATE_SCHDL      => task_record.labor_std_bill_rate_schdl,
      X_LABOR_SCHEDULE_FIXED_DATE      => task_record.labor_schedule_fixed_date,
      X_LABOR_SCHEDULE_DISCOUNT        => task_record.labor_schedule_discount,
      X_NON_LABOR_BILL_RATE_ORG_ID     => task_record.non_labor_bill_rate_org_id,
      X_NL_STD_BILL_RATE_SCHDL         => task_record.non_labor_std_bill_rate_schdl,
      X_NL_SCHEDULE_FIXED_DATE         => task_record.non_labor_schedule_fixed_date,
      X_NON_LABOR_SCHEDULE_DISCOUNT    => task_record.non_labor_schedule_discount,
      X_LABOR_COST_MULTIPLIER_NAME     => task_record.labor_cost_multiplier_name,
      X_ATTRIBUTE_CATEGORY             => task_record.attribute_category,
      X_ATTRIBUTE1                     => task_record.attribute1,
      X_ATTRIBUTE2                     => task_record.attribute2,
      X_ATTRIBUTE3                     => task_record.attribute3,
      X_ATTRIBUTE4                     => task_record.attribute4,
      X_ATTRIBUTE5                     => task_record.attribute5,
      X_ATTRIBUTE6                     => task_record.attribute6,
      X_ATTRIBUTE7                     => task_record.attribute7,
      X_ATTRIBUTE8                     => task_record.attribute8,
      X_ATTRIBUTE9                     => task_record.attribute9,
      X_ATTRIBUTE10                    => task_record.attribute10,
      X_COST_IND_RATE_SCH_ID           => task_record.cost_ind_rate_sch_id,
      X_REV_IND_RATE_SCH_ID            => task_record.rev_ind_rate_sch_id,
      X_INV_IND_RATE_SCH_ID            => task_record.inv_ind_rate_sch_id,
      X_COST_IND_SCH_FIXED_DATE        => task_record.cost_ind_sch_fixed_date,
      X_REV_IND_SCH_FIXED_DATE         => task_record.rev_ind_sch_fixed_date,
      X_INV_IND_SCH_FIXED_DATE         => task_record.inv_ind_sch_fixed_date,
      X_LABOR_SCH_TYPE                 => task_record.labor_sch_type,
      X_NON_LABOR_SCH_TYPE             => task_record.non_labor_sch_type,
      X_ALLOW_CROSS_CHARGE_FLAG        => task_record.allow_cross_charge_flag,
      X_PROJECT_RATE_DATE              => task_record.project_rate_date,
      X_PROJECT_RATE_TYPE              => task_record.project_rate_type,
      X_CC_PROCESS_LABOR_FLAG          => task_record.cc_process_labor_flag,
      X_LABOR_TP_SCHEDULE_ID           => task_record.labor_tp_schedule_id,
      X_LABOR_TP_FIXED_DATE            => task_record.labor_tp_fixed_date,
      X_CC_PROCESS_NL_FLAG             => task_record.cc_process_nl_flag,
      X_NL_TP_SCHEDULE_ID              => task_record.nl_tp_schedule_id,
      X_NL_TP_FIXED_DATE               => task_record.nl_tp_fixed_date,
      X_RECEIVE_PROJECT_INVOICE_FLAG   => task_record.receive_project_invoice_flag,
      X_WORK_TYPE_ID                   => task_record.work_type_id,
      X_JOB_BILL_RATE_SCHEDULE_ID      => task_record.job_bill_rate_schedule_id,
      X_emp_bill_rate_schedule_id      => task_record.emp_bill_rate_schedule_id,
      X_taskfunc_cost_rate_type        => task_record.taskfunc_cost_rate_type,
      X_taskfunc_cost_rate_date        => task_record.taskfunc_cost_rate_date,
      X_non_lab_std_bill_rt_sch_id     => task_record.non_lab_std_bill_rt_sch_id,
      X_labor_disc_reason_code         => task_record.labor_disc_reason_code,
      X_non_labor_disc_reason_code     => task_record.non_labor_disc_reason_code,
      x_retirement_cost_flag           => task_record.retirement_cost_flag,
      x_cint_eligible_flag             => task_record.cint_eligible_flag,
      X_CINT_STOP_DATE                 => task_record.cint_stop_date,
      X_GEN_ETC_SRC_CODE               => task_record.gen_etc_source_code
   );

   PA_PROJ_TASK_STRUC_PUB.UPDATE_TASK_STRUCTURE2 (
      p_calling_module                 => 'FORMS',
      p_task_id                        => task_record.task_id,
      p_task_number                    => task_record.task_number,
      p_task_name                      => l_task_name1,
      P_TASK_DESCRIPTION               => TASK_RECORD.DESCRIPTION,
      p_task_manager_id                => l_task_manager_person_id, --Updated Value
      p_carrying_out_organization_id   => task_record.carrying_out_organization_id,
      p_pm_product_code                => task_record.pm_product_code,
      p_pm_task_reference              => task_record.pm_task_reference,
      p_location_id                    => task_struc_record.location_id,
      p_ref_task_id                    => NULL,
      p_project_id                     => task_struc_record.project_id,
      x_msg_count                      => l_msg_count,
      x_msg_data                       => l_msg_data,
      x_return_status                  => l_return_status
   );
   COMMIT;

   IF l_return_status <> 'S'
   THEN
      FOR n IN 1 .. l_msg_count
      LOOP
         fnd_msg_pub.get (n,
                          fnd_api.g_false,
                          l_msg_data,
                          l_msg_dummy);
         l_output := (TO_CHAR (n) || ': ' || l_msg_data);
         DBMS_OUTPUT.put_line (
            'Error: API Error while updating the Task: ' || l_output
         );
         COMMIT;
      END LOOP;
   ELSE
      DBMS_OUTPUT.put_line ('Sucessfully Update the task');
      COMMIT;
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE ('Other Error in Project: ' || SQLERRM);
END;