API to Update Task Information in Oracle Projects

Here is one API to update Task Information in Oracle Projects. Here I have used the API to update the Task Manager Information.

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;

API to add Classification to an Oracle Project

The below API can be used to add a Classification to an Oracle Project.

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;

Query to find Project Manager Info of an Oracle Project

The below query can be useful when you are working in Oracle Projects and you need to find out the Project Manager Name and Email Address for a particular project.

 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;

Oracle also has provided a seeded package called PA_PROJECT_PARTIES_UTILS that have couple of good functions that can be used to get Project Parties information.

Few Queries for Oracle Projects

Project Basic Info:

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;

Project Parties (Project Key Members):

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)));

Approved Cost Budget Version Detail:

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')
  );

Budget Planning Element Information:

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 (+);

Change Order Basic Information:

select pci.ci_id,
       pci.summary Change_Order_Name,
       pci.ci_number Change_Order_Number,
       pci.description,
       pci.status_code,
       pcim.description
from pa_control_items pci,
pa_ci_impacts pcim
where project_id=:p_project_id
and pci.ci_id=:p_change_order_id
and pci.ci_id=pcim.ci_id
and pcim.impact_type_code='FINPLAN';

Financial Plan CI Impact:

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);

Project & Change Order Approval Action History:

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
  );

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:

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:

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;

Oracle Project Foundation APIs

This article gives a brief description of the APIs that you can use to integrate project data from an external system with Oracle Projects. The procedures discussed below are located in the public API package PA_PROJECT_PUB.

 

Project Procedures:

1] CREATE_PROJECT

CREATE_PROJECT is a PL/SQL procedure that creates a project in Oracle Projects using a template or an existing project.

The Parameters:

Name	Description
P_API_VERSION_NUMBER	API standard version number
P_COMMIT	API standard (default = ‘F’) indicates if transaction will be committed
P_INIT_MSG_LIST	API standard (default = ‘F’) indicates if message stack will be initialized
P_MSG_COUNT	API standard count of error messages
P_MSG_DATA	API standard error message
P_RETURN_STATUS	API standard Return of the API success/failure/unexpected error)
P_WORKFLOW_STARTED	Shows if a workflow has been started (Y or N)
P_PM_PRODUCT_CODE	Identifier of the external systems from which the project was imported
P_PROJECT_IN	Input project details
P_PROJECT_OUT	Output project details
P_KEY_MEMBERS	The identification code for the role that the members have on the project
P_CLASS_CATEGORIES	Identification code for the categories by which the project is classified
P_TASKS_IN	Input task details of the project
P_TASKS_OUT	Output task details of the project
P_ORG_ROLES	Identifier for organization roles for project
P_STRUCTURE_IN	Identifier of structure data
P_EXT_ATTR_TBL_IN	Identifier of external attributes

2] DELETE_PROJECT

DELETE_PROJECT is a PL/SQL procedure used to delete a project and its tasks from Oracle Projects.

The Parameters:

Name	Description
P_API_VERSION_NUMBER	API standard version number
P_COMMIT	API standard (default = ‘F’) indicates if transaction will be committed
P_INIT_MSG_LIST	API standard (default = ‘F’) indicates if message stack will be initialized
P_MSG_COUNT	API standard count of error messages
P_MSG_DATA	API standard error message
P_RETURN_STATUS	API standard Return of the API success/failure/unexpected error)
P_PM_PRODUCT_CODE	Identifier of the external systems from which the project was imported
P_PM_PROJECT_REFERENCE	The reference code that uniquely identifies the project in the external system
P_PA_PROJECT_ID	The reference code that uniquely identifies the project in Oracle Projects

3] UPDATE_PROJECT

UPDATE_PROJECT is a PL/SQL procedure that updates project and task information from your external system to Oracle Projects to reflect changes you have made in the external system. UPDATE_PROJECT uses composite datatypes.

The Parameters:

Name	Description
P_API_VERSION_NUMBER	API standard version number
P_COMMIT	API standard (default = ‘F’) indicates if transaction will be committed
P_INIT_MSG_LIST	API standard (default = ‘F’) indicates if message stack will be initialized
P_MSG_COUNT	API standard count of error messages
P_MSG_DATA	API standard error message
P_RETURN_STATUS	API standard Return of the API success/failure/unexpected error)
P_WORKFLOW_STARTED	Shows if a workflow has been started (Y or N)
P_PM_PRODUCT_CODE	Identifier of the external systems from which the project was imported
P_PROJECT_IN	Input project details
P_PROJECT_OUT	Output project details
P_KEY_MEMBERS	The identification code for the role that the members have on the project
P_CLASS_CATEGORIES	Identification code for the categories by which the project is classified
P_TASKS_IN	Input task details of the project
P_TASKS_OUT	Output task details of the project
P_ORG_ROLES	Identifier for organization roles for project
P_STRUCTURE_IN	Identifier of structure data
P_PASS_ENTIRE_STRUCTURE	Flag indicating whether to pass entire structure
P_EXT_ATTR_TBL_IN	Identifier of external attributes.

Load-Execute-Fetch Procedures:

The following is the list of API’s for Load-Execute-Fetch and should be executed in the order of sequence.

• INIT_PROJECT
• LOAD_PROJECT
• LOAD_TASK
• LOAD_CLASS_CATEGORY
• LOAD_KEY_MEMBER
• EXECUTE_CREATE_PROJECT/EXECUTE_UPDATE_PROJECT
• FETCH_TASK
• CLEAR_PROJECT

Check Procedures:

CHECK_DELETE_PROJECT_OK	This API is used to determine if you can delete a project.
CHECK_CHANGE_PROJECT_ORG_OK	This API is used to determine if you can change the CARRYING_OUT_ORGANIZATION_ID field for a particular project or task.
CHECK_CHANGE_PARENT_OK	This API is used to determine if you can move a task from one parent task to another.
CHECK_UNIQUE_PROJECT_REFERENCE	This API is used to determine if a new or changed project reference(PM_PROJECT_REFERENCE) is unique
CHECK_ADD_SUBTASK_OK	This API is used to determine if a subtask can be added to a parent task.
CHECK_DELETE_TASK_OK	This API is used to determine if you can delete a task.
CHECK_TASK_NUMBER_CHANGE_OK	This API is used to determine if you can change a tasks number.
CHECK_UNIQUE_TASK_NUMBER	This API is used to determine if a new or changed task number is unique within a project.
CHECK_UNIQUE_TASK_REFERENCE	This API is used to determine if a new or changed task reference (PM_TASK_REFERENCE) is unique

You can refer Oracle Projects APIs, Client Extensions, and Open Interfaces for the record and table types used. Go to Oracle Integration Repository for more details in the above procedures.

Thanks..Have a nice day!

Oracle Projects Migration/ Data Conversion

During my work on a projects conversion, I came accross this very nice document. This document is very informative and so sharing with you all. Happy reading!

Main Article:

In this article I will be explaining the general steps involved in any Conversion/Data Migration of Oracle Projects module.

At the end of this article, you would have learned:

• Stages in Oracle Projects Conversion.
• How to setup the Oracle Projects module for the conversion/Data Migration.
• Options for the Load (flat file, csv, or direct Loads).
• Oracle Projects AMG APIs needed to perform the Conversion.
• Testing the Conversion Process.
• Verifying the Conversion Process.

Scenario:

Company ‘XYZ’ is using a Project Management and Accounting Software for years long. The Management has decided to move from their existing system to Oracle Projects module because of its vast functionality and integration with other financial modules.

How to deal with it?

Now the question arises: What data to migrate from the legacy system to Oracle Projects?. Well, it depends upon the type of projects.

If the Projects are used for Internal Administration and tracking of costs, you may want to migrate the existing projects, tasks(the work break down structure), Cost Budgets, Cost (Timecards, Employee Expenses, Miscellaneous Expenses) etc.

If the Projects are used for billing the Clients for the work done (Typical Contract Projects), then you may want to Revenue, Agreements (Contracts), Revenue budgets and Invoices in addition to the above data.

Once the decision is made to which data to migrate, then the next step would be setting up the Oracle Projects for the conversion purpose, which we will see in detail sooner. Once the System has been setup, the technical elements (programs, concurrent processes etc) have to be created in order to migrate the data from Legacy System to Oracle Projects.

Stages in Oracle Projects Conversion

1.     The First Stage is to obtain the data from the Legacy System which needs to be migrated to Oracle Projects.

2.     The Second Stage will be most crucial step in the process which is to massage the data according to the Oracle Projects Conversion Interface (Programs built using AMG APIs). This Step is indeed time consuming, manual labor intensive to massage and rectify the errors etc. But completing this step successfully pays dividends in the consecutive processes / Stages.

3.     The Third Stage is uploading the data obtained from legacy systems into the Staging Area (Staging Tables created to hold the data temporarily till it gets migrated into Oracle Projects). Once the data is uploaded to the Staging tables, the programs built for migration (We will see how to build these programs in detail) will validate the Staging Table data to confirm that it is in compliance with the Projects Conversion Program (The AMG APIs used in the programs indeed needs data in certain format, also the data should be validated against the Oracle Projects Setup. For instance, when migrating the cost or hours from legacy to Projects, we might need to validate if the expenditure type is already setup in Oracle Projects, if the expenditure type is not setup, the program/APIs will throw an error. So it is always better to capture these kind of scenarios in the Validation Step of the Migration.

The Second Stage and Third Stages are repetitive until you get the valid data from the legacy system which can be migrated into Oracle Projects without any errors or issues.

The Fourth Stage is the actual migration process which will migrate the data from the Staging Tables to the Oracle Projects Base tables. Once this step is done, the projects, tasks and other data are available in Oracle Projects for use.

Before going through the stages, we will look at some of the basic setups that need to be done in Oracle Projects.

Oracle Projects Setup For Conversion

Product Code:

The Product Code needs to be setup in Oracle Projects in the AMG Gateway – Source Products Form in the Oracle Projects Implementation Super User Responsibility. This setup is mandatory since this product code needs to be passed when using the Oracle Projects AMG APIs

Project Types and Project Templates:

The project types and project templates for conversion projects need to be setup up. This is a mandatory setup since while migrating projects we need to tell the APIs which project template/type the projects use.

For Contract Projects, setup the Contract Project Type Template. For administrative or internal projects, setup the Indirect Project Type templates.

If you are migrating Cost and Revenue Budgets, then the Plan Types need to be attached to the templates in order to create the budgets for the migrated projects.

Implementation Option Setup:

Project Numbering: This implementation option is by default set to ‘Automatic’ which means when creating projects in Oracle Projects, the project number is automatically derived and users are not required to provide any project numbers. This option is best suitable when creating projects in Oracle Projects. But when migrating the projects from the third party systems, there is an option to migrate the projects with the same project number as in the legacy system. This is not mandatory but is recommended since it will be easy to refer back the projects in the source system using the project numbers.

In order to pass the project number to the Migration program, this implementation option needs to be setup to ‘Manual’. Once the migration is done, this setup can be reverted back to ‘Automatic’.

Setup Transaction Source:

The Transaction Source needs to be setup in Oracle Projects in the Transaction Sources form in Oracle Projects Implementation Super User Responsibility. This is a mandatory setup for the Costs/hours migration from the legacy system to Oracle Projects. We need to tell the migration API’s what the source system is and how the data is handled when it is imported to Oracle Projects.

Setup Expenditure Types:

Expenditure Types are needed to categorize the cost/hours when it is imported to Oracle Projects. This is a mandatory setup for Cost/hours migration. We need to tell the system which expenditure type the cost/hour belongs to.

Setup Employee Cost Rates:

Setting up cost rates for employees is not mandatory. But if you need to cost the hours that are migrated in the system, the labor cost distribution process in Oracle Projects do need the rates setup in order to calculate the costs.

But if you are migrating the costs directly from the legacy instead of hours then this step is not needed. But ideally the cost rates are required in a general production scenario wherein the employees/contractors enter their timecards.

You can setup job rate schedule, employee level rate schedule or employee level overrides. Alternatively, the costing client extension can be setup to calculate the cost according to the business scenario.

Refer to the Oracle Projects User Guide for how to setup the employee cost rates.

First Stage: Obtain Data from Legacy System

The first stage deals with obtaining the data from the legacy system in the desired format. The data can be obtained in the form of flat text file or comma separated file csv, tab delimited file or file with any delimiters. Generally tab delimited files are recommended since comma separated files behave strange when there is a comma in the data itself.

If there is a database link created between the Source Legacy database and the Oracle Projects Database then the data can be obtained directly using the select statements against the Source DB from within the Oracle Projects DB. But this method is not preferred as it is more performance intensive when it comes to selecting large data over the network.

For Projects Migration, generally 2 files are obtained. One file for Projects Data and the other file for Tasks Data.

For Transaction Migration, single file is enough with all the cost/hours data.

For Cost/Revenue Budget migration, single file is enough with all the Budgets Data.

Create SQL Loader concurrent program which will upload the obtained data into the Oracle Staging Tables.

Also it is always the best practice to create a control table in the Staging area, which will control the data migration. For example your control table might look like the one below:

Parameter Type	Parameter	Parameter Value
Template	Contract	Contract_Template
Template	Indirect	Indirect_Template
Expenditure Type	Hours	Labor
Expenditure Type	Expenses	Employee_Expense
Transaction	Transaction Source	Legacy1
Product Code	Product code	LEGACY1
Project	Publish Workplan	Yes
Project	Baseline Workplan	Yes
Cost Budget	Baseline	Yes
Revenue Budget	Baseline	Yes

This control table is looked upon by the migration program. So whenever there is a change in the templates, expenditure types it is easy to change this control table instead of the code. So the advise is never hard code any values in the code, always handle it using the control table.

Also it will be better to have a form based on this table, so that this table data can be changed from the front end.

Second Stage & Third Stage: Validate and Format the Data

I am coupling the second and third stage because both are interdependent. Validating data is very important and it prevents some of the time consuming tasks in actual migration such as trouble shooting the errors due to the invalid data.

Below are some of the key validations that need to be done before doing the actual migration.

Projects/Tasks Migration:

Though the projects and tasks are in different staging tables, the migration of projects/tasks is doing using a single program. We can always migrate projects and tasks separately, but the issue is with the performance when adding task by task to each project. So it always better to create projects and tasks together because of the bulk loading of tasks.

Project/Task – Setup Validations: 

• Validate the Product code is setup.
• Validate if the required Project Templates are setup.
• Validate if the Project Numbering is set to ‘Manual’ for creating projects with the   predefined project numbers.

Project/Task Data Validations:

• Validate if the project name is unique. Project with the same name should not exist in Oracle Projects.
• Validate if the project number is unique. Project with the same number should not exist in Oracle Projects.
• Validate if the project long name is unique. Project with the same long name should not exist in Oracle.
• Validate the project reference(this field is mandatory in the projects file, it can be the projects identifier of the source project or project number of the source project, but it has to be unique in the source system as well. This field needs to be populated in all the converted projects in order to track back and identify the project in the source system)
• Project name and project number should be 30 chars in length. Project long name should be 240 chars in length. Project Description should be 250 chars in length. Project description is not a mandatory field when creating project.
• Check if the project has a project manager and the project manager is active in Oracle HR and has an assignment and a Job assigned. Also the project manager has to be active from the project start date, else you cannot create a project with that project manager.
• In case of contract projects, check if the customer of the project is a valid customer defined and with a valid Bill To site assigned.

Apart from the above necessary validations, you may have to validate the additional data such as Projects DFF Data you may want to populate with your custom field values. For example you may want to populate the Project cost center value in the Segment1 of the Project DFF. In such case you have to validate if the cost center value is a valid value for that Segment1 (sometimes you may have attached an LOV to that segment1, so in that case, the cost center has to be validated against that LOV Values).

For tasks, values for task types, work type, task manager has to be validated. Task types and work types have to be defined in Oracle Projects before the task with those values are migrated, else the task will not be created.

Cost/hours validation

Setup Validations:

• Validate if the Transaction source is setup.
• Validate if the Expenditure type is setup.

Data Validations:

• Check if the hours value is greater than zero.
• Check if the employee number is valid in HR and is active on the timecard date.
• If the transaction source is setup as costed, then the cost has provided while migrating the transactions. If the transaction source is setup as accounted, then the code combination ids need to be provided when migrating transactions.

Apart from the above validations, you may want to validate the additional DFF segments that you are going to populate for that expenditure item.

Budgets Validation

Setup Validations:

• Validate the project template has the required financial plan type attached. Financial plans are the project management versions of the Budget types in the Forms applications.
• Budget amount has to be greater than zero.
• There is no need to create revenue budgets if the ‘Baseline funding without budget’ option is checked at the project or project type level. Whenever the funding is created for the contract project and is baselined, the revenue budget is automatically created and baselined. If that option is not checked, it is necessary that a revenue budget with the same amount as the funding amount needs to be created and baselined in order to baseline the funding.

Data Validations:

The cost budget for the project can be from the source system’s budgeting system. If there is no budgeting in the source system, a cost budget with the total cost of the project can be created in Oracle Projects.

For revenue budgets, it has to be equal to the funding amount of the project. If there is no funding amount in the source system, the sum of the revenue amount can be the funding amount and it is the revenue budget amount as well.

Agreements and Funding Validation

Data Validations:

• Agreement type should be valid.
• Agreement Amount should be greater than zero.
• Hard Limits can be setup according to business rules. If the hard limits are setup for revenue and invoice then the revenue and invoice has to be within the funding limits for that project.
• Funding amount has to be within the Agreement amount.
• If the funding at the top task level, then the ‘Customer at top task’ has to be enabled and the customer should have been assigned at the top task.
• Funding amount should be same as the Revenue budget amount which in general will be same as the total revenue amount for that project. If there are no hard limits then the revenue or invoice can exceed the funding amounts.

Records which fail the above validations have to be rectified before doing the actual migration.

Revenue and Invoice Validations

Data Validations:

• Project / Task should already been converted to Oracle.
• Event amount should be non zero.
• For revenue event revenue amount should be populated.
• For invoice event invoice amount should be populated.

Generally for a project, the total revenue is obtained from the source system and is created as a revenue event for that project. The total invoiced amount is calculated per project and an invoice event is created for each project.

Once these events are created successfully in the system, the Generate Draft Revenue process and Generate Draft Invoice process needs to be run so that the desired revenue and invoices are generated.

The revenue and invoice automatic approval and release client extensions can be used to automatically release the revenue when it is generated and approve/release invoices respectively.

If the revenue amounts are already interfaced to General Ledger (GL) through a different interface, then uncheck the ‘Interface Revenue to GL’ option in the implementation options and run the ‘Interface Revenue to GL’ process in Oracle projects. This will turn the flags in the revenue records as accepted in GL, though it is not interfaced. Once this is done, revert back the implementation option back to its original state.

If the invoice amounts are already interfaced to Accounts Receivables (AR) by different means, it is not desired to interface the projects invoices to AR again since it will double the invoice amount in AR. In this case, we do not have an implementation option like we had for Revenue. So a script can be created to update the Invoice’s flag to Accepted State. Alternatively the generated projects invoices can be interfaced to AR, tied back to Oracle and then the invoices can be deleted in AR.

Stage 4: Actual Migration

Once the data is validated, the program for conversion is executed to migrate the data into oracle projects base tables. There might be still errors due to AMG APIs which has to analyzed and resolved. But the chances of such AMG API issues are just below 10% in any migration (based on my experience in Oracle Projects Conversion).

Below is a table with Conversion and which AMG APIs are used for that conversion:

Conversion	AMG APIs
Projects/Tasks Conversion	PA_PROJECT_PUB.CREATE_PROJECT
Budgets Conversion	PA_BUDGET_PU B.CREATE_DRAFT_BUDGET, PA_BUDGET_PUB.BASELINE_BUDGET
Agreements	PA_AGREEMENT_PUB.CREATE_AGREEMENT
Funding	PA_AGREEMENT_PUB.ADD_FUNDING
Revenue/Invoice	PA_EVENT_PUB.CREATE_BILLING_EVENT
User Defined Attributes (UDA)	PA_PROJECT_PUB.LOAD_EXTENSIBLE_ATTRIBUTE

For Transactions (cost/hours) migration, there is no APIs to create the expenditures in Oracle. The pa_transaction_interface_all table needs to be populated with the migration data and once it is populated, the PRC: Transaction Import process with the Transaction source as parameter needs to be run in Oracle Projects. All invalid records need to be rectified in order to migrate all the transactions.

The rejected records can be found in the same interface table with the transfer_status_code as ‘R’.

Conversion Tips:

1.     Make sure the templates are defined properly and exactly the way it is needed. Once the projects are created using the templates and the template was wrongly defined, then it takes ages to rectify the converted projects.

2.     Create the conversion program to operate in two modes: Validate, Run. A concurrent process with a parameter called mode accepting Validate/Run can be created. So the same concurrent program can be used to validate as well as run the actual migration.

3.     It is a good practice to have source Project id / Project Number as parameter to the projects conversion program. This will allow us to test the conversion for a single project and validate the data for that project.

4.     The validation process can write the invalid records to the output file. So once validation process completes, the output will have all the invalid records which needs to be rectified.

5.     Create a separate concurrent program to know the status of the already running migration process. If you want to know where the migration process is in terms of the number of records migrated, number of records rejected etc. If the volume of the migration data is huge, then it is likely possible that the conversion programs may run for hours. So in these scenarios this concurrent program can be helpful in finding the status of that migration process.

6.     For Transactions migration, the custom program written to populate the interface table can kick off the PRC: Transaction Import process and wait for its completion. Once the transaction import completes, the custom process can print the invalid records from the interface table to the output file.

7.     There are APIs to publish and baseline the workplans created as a part of projects migration. But these APIs need to be used with care. There are lot of performance issues and bugs when using these APIs.

Original Post

Author: Sathish Raju

Website: http://www.projectsaccounting.com

Key Tables in Oracle Projects

Here is a brief description of the key tables in Oracle Projects.

Table	Description
PA_PROJECTS_ALL	It stores the highest units of work defined in Oracle Projects.
PA_PROJECT_ASSETS_ALL	It contains assets information defined for capital projects.
PA_PROJECT_ASSIGNMENTS	It stores details of all Assignments for a project.
PA_PROJECT_CLASSES	It contains the class codes of class categories that are used to classify projects.
PA_PROJECT_ROLE_TYPES	Implementation-defined responsibilities or positions assigned to employees on projects are stored here.
PA_PROJECT_STATUSES	It stores valid project status codes.
PA_PROJECT_TYPES_ALL	It stores implementation-defined project classifications that supply default information and drive some project processing.
PA_TASKS	It contains user-defined subdivisions of project work.
PA_TASK_TYPES	It stores implementation-defined classifications of task.
PA_TRANSACTION_INTERFACE_ALL	It is an interface table to import transactions from external sources into Oracle Projects.
PA_TRANSACTION_SOURCES	It stores implementation-defined sources of imported transactions originating in an external system.
PA_IMPLEMENTATIONS_ALL	It contains information about the configuration of an Oracle Projects installation.
PA_ACTION_SETS	It stores action set templates as well as action sets belonging to an object, such as projects, requirements, etc.
PA_ACTION_SET_LINES	It stores action set lines that belong to an action set or an action set template.
PA_ACTION_SET_TYPES	It stores attributes of action set types.
PA_AGREEMENTS_ALL	It has customer contracts that serve as the basis for work authorization.
PA_AGREEMENT_TYPES	Implementation-defined classifications of customer agreements.
PA_BILL_RATES_ALL	Information about bill rates and markups of standard bill rate schedules.
PA_BUDGETS	It stores budgets information.
PA_BUDGET_LINES	It stores detail lines of project and task budgets.
PA_BUDGET_TYPES	It contains implementation-defined classifications of types of budgets used for different business purposes.
PA_CLASS_CATEGORIES	It stores implementation-defined categories for classifying projects.
PA_CLASS_CODES	It stores implementation-defined values within class categories that can be used to classify projects.
PA_EVENTS	It stores entries assigned to tasks that generate revenue and/or billing but are not directly related to expenditure items.
PA_EVENT_TYPES	It stores implementation-defined classifications of events.
PA_EXPENDITURES_ALL	Groups of expenditure items incurred by employees or organizations for an expenditure period.
PA_EXPENDITURE_CATEGORIES	Implementation-defined groupings of expenditure types by type of cost.
PA_EXPENDITURE_ITEMS_ALL	It contains the smallest units of expenditure charged to projects and tasks.
PA_EXPENDITURE_TYPES	Implementation-defined classifications of expenditures charged to projects and tasks.
PA_PERIODS_ALL	Implementation-defined periods against which project performance is measured.
PA_RBS_DENORM	This table stores normalized resource breakdown structure information.
PA_RBS_ELEMENTS	This table stores the RBS element information and the parent-child relationship.
PA_RESOURCES	It contains resources used in budgeting and project summary amounts.
PA_ROLE_LISTS	It stores lists of roles defined with the system.
PA_SCHEDULES	It displays the schedule details for requirements and assignments. It also displays calendar schedules.

 

Oracle Project Suite: An Introduction

The oracle project suite is a complete enterprise level project management solution. They provide a robust flexible approach to define and manage enterprise level projects and the people, schedules, deliverables, and finances associated with them.

Generally, enterprise project management involves the collection and coordination of corporate resources (such as people, money, and hard assets) to accomplish a predefined scope of work in a scheduled time frame and budget. Here the Oracle Projects application suite comes with a variety of features to accomplish such work.

It enables project managers to effectively oversee their projects, assess progress against predetermined milestones and budgets, staff their projects with appropriate talent, and quickly generate a wide variety of reports. It also helps virtual and globally distributed project teams to efficiently communicate, collaborate, and complete tasks in time. Oracle Projects also gives corporate executives the ability to quickly see how projects are performing across the enterprise.

Oracle Projects suite is designed to integrate with many other Oracle application suites (including Oracle HRMS, Oracle Financials, and Oracle SCM) to provide an efficient information flow between modules, facilitate a global sharing of enterprise resources, and provide robust intercompany accounting.

Below are the products that shipped with Oracle Projects Suite:

1] Oracle Project Costing

• It is an integrated cost management solution for all projects and activities within an enterprise.
• Run across multiple currencies and organizations.
• It acts as a central repository of project plans and transactions, processes project costs, and creates corresponding accounting entries to satisfy corporate finance requirements.

2] Oracle Project Billing

• It enables enterprises to simplify customer invoicing, streamline corporate cash flow, and measure the profitability of contract projects.
• It provides features to review project invoices online, analyze project profitability and corporate impact of project work.

3] Oracle Project Resource Management

• It empowers an enterprise to make better use of their single most critical asset: their people.
• It enables efficient coordination of project resource needs, profitability, and organization utilization through the location and deployment of qualified resources to projects across the enterprise.
• It is integrated with Oracle HRMS to efficiently deploy human resources in various projects across enterprise.

4] Oracle Project Management

• It is used for integrated project planning, tracking & real-time project performance management.
• With Oracle Project Management, project managers can proactively plan and forecast their projects, manage change and performance in real-time, focus on desired project outcomes rather than data management, and make better decisions with less effort.

5] Oracle Project Collaboration

• It provides a secure and intuitive user interface through which a team member of a project can see his assigned tasks, issues, deliverables and other project related information.
• It has structured workspaces such as the Team Member Home page with the help of which, team members can work together more efficiently, make more effective decisions, and deliver superior results faster.

6] Oracle Daily Business Intelligence for Projects

• It is a comprehensive out-of-the-box reporting solution that delivers aggregate and detail information about the projects in an enterprise directly to the people who need it.
• Utilizing secure, role-based portals, it provides daily summaries of key metrics including revenue, cost, margin, bookings, backlog, and utilization.
• It also provides features like drill-down to detail information, cross-project reporting, reporting for multiple calendar types, displaying of information by different periods , comparison of current actual amounts to prior periods and reporting by enterprise and functional currency etc.

7] Oracle Project Portfolio Analysis

• It leverages the rich project management functionality of Oracle Projects to facilitate evaluation and collection of projects in a portfolio.
• It uses financial criteria, strategic goals, and information on available funds to help you evaluate, prioritize, and select the right projects to match your business objectives.
• It provides a full range of portfolio analysis reports, charts, and graphs.