API to Load Values into Value Sets


DECLARE
----------------------------Local Variables---------------------------
   l_enabled_flag             VARCHAR2 (2);
   l_summary_flag             VARCHAR2 (2);
   l_who_type                 FND_FLEX_LOADER_APIS.WHO_TYPE;
   l_user_id                  NUMBER                := FND_GLOBAL.USER_ID;
   l_login_id                 NUMBER                := FND_GLOBAL.LOGIN_ID;
   l_value_set_name           FND_FLEX_VALUE_SETS.FLEX_VALUE_SET_NAME%TYPE;
   l_value_set_value          FND_FLEX_VALUES.FLEX_VALUE%TYPE;
BEGIN

   l_value_set_name             :='VALUE_SET_NAME';
   l_value_set_value            :='VALUE_SET_VALUE';
   l_enabled_flag               := 'Y';
   l_summary_flag               := 'N';
   l_who_type.created_by        := l_user_id;
   l_who_type.creation_date     := SYSDATE;
   l_who_type.last_updated_by   := l_user_id;
   l_who_type.last_update_date  := SYSDATE;
   l_who_type.last_update_login := l_login_id;

     fnd_flex_loader_apis.up_value_set_value
                  (p_upload_phase               => 'BEGIN',
                   p_upload_mode                => NULL,
                   p_custom_mode                => 'FORCE',
                   p_flex_value_set_name        => l_value_set_name,
                   p_parent_flex_value_low      => NULL,
                   p_flex_value                 => l_value_set_value,
                   p_owner                      => NULL,
                   p_last_update_date           => TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS'),
                   p_enabled_flag               => l_enabled_flag,
                   p_summary_flag               => l_summary_flag,
                   p_start_date_active          => TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS'),
                   p_end_date_active            => NULL,
                   p_parent_flex_value_high     => NULL,
                   p_rollup_flex_value_set_name => NULL,
                   p_rollup_hierarchy_code      => NULL,
                   p_hierarchy_level            => NULL,
                   p_compiled_value_attributes  => NULL,
                   p_value_category             => 'VALUE_SET_NAME',
                   p_attribute1                 => '40912',
                   p_attribute2                 => NULL,
                   p_attribute3                 => NULL,
                   p_attribute4                 => NULL,
                   p_attribute5                 => NULL,
                   p_attribute6                 => NULL,
                   p_attribute7                 => NULL,
                   p_attribute8                 => NULL,
                   p_attribute9                 => NULL,
                   p_attribute10                => NULL,
                   p_attribute11                => NULL,
                   p_attribute12                => NULL,
                   p_attribute13                => NULL,
                   p_attribute14                => NULL,
                   p_attribute15                => NULL,
                   p_attribute16                => NULL,
                   p_attribute17                => NULL,
                   p_attribute18                => NULL,
                   p_attribute19                => NULL,
                   p_attribute20                => NULL,
                   p_attribute21                => NULL,
                   p_attribute22                => NULL,
                   p_attribute23                => NULL,
                   p_attribute24                => NULL,
                   p_attribute25                => NULL,
                   p_attribute26                => NULL,
                   p_attribute27                => NULL,
                   p_attribute28                => NULL,
                   p_attribute29                => NULL,
                   p_attribute30                => NULL,
                   p_attribute31                => NULL,
                   p_attribute32                => NULL,
                   p_attribute33                => NULL,
                   p_attribute34                => NULL,
                   p_attribute35                => NULL,
                   p_attribute36                => NULL,
                   p_attribute37                => NULL,
                   p_attribute38                => NULL,
                   p_attribute39                => NULL,
                   p_attribute40                => NULL,
                   p_attribute41                => NULL,
                   p_attribute42                => NULL,
                   p_attribute43                => NULL,
                   p_attribute44                => NULL,
                   p_attribute45                => NULL,
                   p_attribute46                => NULL,
                   p_attribute47                => NULL,
                   p_attribute48                => NULL,
                   p_attribute49                => NULL,
                   P_ATTRIBUTE50                => NULL,
                   p_flex_value_meaning         => l_value_set_value,
                   p_description                => NULL
                   );
      COMMIT;

   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT,PUT_LINE('Error is ' || SUBSTR (SQLERRM, 1, 1000));
   END;
Advertisements

Deriving Oracle GL Account Code Combination ID’s (CCID’s) through APIs


1] FND_FLEX_EXT.GET_COMBINATION_ID:

This API Finds combination_id for given set of key flexfield segment values. Segment values must be input in segments(1) – segments(n_segments) in the order displayed.

It also creates a new combination if it is valid and the flexfield allows dynamic inserts and the combination does not already exist. It commit the transaction soon after calling this function since if a combination is created it will prevent other users creating similar combinations on any flexfield until a commit is issued.

It performs all checks on values including security and cross-validation. Value security rules will be checked for the current user identified in the FND_GLOBAL package.

Generally pass in SYSDATE for validation date. If validation date is null, this function considers expired values valid and checks all cross-validation rules even if they are outdated.

This function returns TRUE if combination valid or FALSE and sets error message using FND_MESSAGE utility on error or if invalid. If this function returns FALSE, use GET_MESSAGE to get the text of the error message in the language of the database, or GET_ENCODED_MESSAGE to get the error message in a language-independent encoded format.

The Combination_id output may be NULL if combination is invalid.

Example: (Tested in R12.1.3)

SET serveroutput ON;
DECLARE
  l_application_short_name VARCHAR2(240);
  l_key_flex_code          VARCHAR2(240);
  l_structure_num          NUMBER;
  l_validation_date        DATE;
  n_segments               NUMBER;
  SEGMENTS                 APPS.FND_FLEX_EXT.SEGMENTARRAY;
  l_combination_id         NUMBER;
  l_data_set               NUMBER;
  l_return                 BOOLEAN;
  l_message                VARCHAR2(240);
BEGIN
  l_application_short_name := 'SQLGL';
  l_key_flex_code          := 'GL#';

  SELECT id_flex_num
  INTO l_structure_num
  FROM apps.fnd_id_flex_structures
  WHERE ID_FLEX_CODE        = 'GL#'
  AND ID_FLEX_STRUCTURE_CODE=<ACCOUNTING_FLEXFIELD>;

  l_validation_date        := SYSDATE;
  n_segments               := 6;
  segments(1)              := '00101';
  segments(2)              := '28506';
  segments(3)              := '00000';
  segments(4)              := '09063';
  segments(5)              := '00000';
  segments(6)              := '00000';
  l_data_set               := NULL;

  l_return := FND_FLEX_EXT.GET_COMBINATION_ID(
                    application_short_name => l_application_short_name,
                    key_flex_code          => l_key_flex_code,
                    structure_number       => l_structure_num,
                    validation_date        => l_validation_date,
                    n_segments             => n_segments,
                    segments               => segments,
                    combination_id         => l_combination_id,
                    data_set               => l_data_set
                    );
  l_message:= FND_FLEX_EXT.GET_MESSAGE;

  IF l_return THEN
    DBMS_OUTPUT.PUT_LINE('l_Return = TRUE');
    DBMS_OUTPUT.PUT_LINE('COMBINATION_ID = ' || l_combination_id);
  ELSE
    DBMS_OUTPUT.PUT_LINE('Error: '||l_message);
  END IF;
END;

2] FND_FLEX_EXT.get_ccid:

This API gets combination id for the specified key flexfield segments.It is identical to get_combination_id() except this function takes segment values in a string concatenated by the segment  delimiter for this flexfield, and returns a positive combination id if valid or 0 on error.

3] FND_FLEX_KEYVAL.VALIDATE_SEGS:

These key flexfields server validations API are a low level interface to key flexfields validation.  They are designed to allow access to all the flexfields functionality, and to allow the user to get only the information they need in return.  Because of their generality, these functions are more difficult to use than those in the FND_FLEX_EXT package.  Oracle strongly suggests using the functions in FND_FLEX_EXT package if at all possible.

This function finds combination from given segment values.  Segments are passed in as a concatenated string in increasing order of segment_number (display order).

Various Operations that can be performed are:

  • ‘FIND_COMBINATION’ – Combination must already exist.
  • ‘CREATE_COMBINATION’ – Combination is created if doesn’t exist.
  • ‘CREATE_COMB_NO_AT’ – same as create_combination but does not use an autonomous transaction.
  • ‘CHECK_COMBINATION’ – Checks if combination valid, doesn’t create.
  • ‘DEFAULT_COMBINATION’ – Returns minimal default combination.
  • ‘CHECK_SEGMENTS’ – Validates segments individually.

If validation date is NULL checks all cross-validation rules. It returns TRUE if combination valid or FALSE and sets error message on server if invalid. Use the default values if you do not want any special functionality.

Example: (Tested in R12.1.3)

SET serveroutput ON;
DECLARE
  l_segment1   GL_CODE_COMBINATIONS.SEGMENT1%TYPE;
  l_segment2   GL_CODE_COMBINATIONS.SEGMENT2%TYPE;
  l_segment3   GL_CODE_COMBINATIONS.SEGMENT3%TYPE;
  l_segment4   GL_CODE_COMBINATIONS.SEGMENT4%TYPE;
  l_segment5   GL_CODE_COMBINATIONS.SEGMENT5%TYPE;
  l_segment6   GL_CODE_COMBINATIONS.SEGMENT6%TYPE;
  l_valid_combination BOOLEAN;
  l_cr_combination    BOOLEAN;
  l_ccid       GL_CODE_COMBINATIONS_KFV.code_combination_id%TYPE;
  l_structure_num FND_ID_FLEX_STRUCTURES.ID_FLEX_NUM%TYPE;
  l_conc_segs GL_CODE_COMBINATIONS_KFV.CONCATENATED_SEGMENTS%TYPE;
  p_error_msg1                 VARCHAR2(240);
  p_error_msg2                 VARCHAR2(240);
BEGIN
  l_segment1  := '00101';
  l_segment2  := '28506';
  l_segment3  := '00000';
  l_segment4  := '14302';
  l_segment5  := '00455';
  l_segment6  := '00000';
  l_conc_segs := l_segment1||'.'||l_segment2||'.'||l_segment3||'.'||l_segment4||'.'||l_segment5||'.'||l_segment6 ;
  BEGIN
    SELECT id_flex_num
      INTO l_structure_num
      FROM apps.fnd_id_flex_structures
     WHERE id_flex_code        = 'GL#'
       AND id_flex_structure_code='EPC_GL_ACCOUNTING_FLEXFIELD';
  EXCEPTION
  WHEN OTHERS THEN
    l_structure_num:=NULL;
  END;
  ---------------Check if CCID exits with the above Concatenated Segments---------------
  BEGIN
    SELECT code_combination_id
      INTO l_ccid
      FROM apps.gl_code_combinations_kfv
     WHERE concatenated_segments = l_conc_segs;
  EXCEPTION
  WHEN OTHERS THEN
    l_ccid:=NULL;
  END;
  IF l_ccid IS NOT NULL THEN
    ------------------------The CCID is Available----------------------
    DBMS_OUTPUT.PUT_LINE('COMBINATION_ID= ' ||l_ccid);
  ELSE
  DBMS_OUTPUT.PUT_LINE('This is a New Combination. Validation Starts....');
    ------------Validate the New Combination--------------------------
    l_valid_combination := APPS.FND_FLEX_KEYVAL.VALIDATE_SEGS
                          (
                          operation => 'CHECK_COMBINATION',
                          appl_short_name => 'SQLGL',
                          key_flex_code => 'GL#',
                          structure_number => L_STRUCTURE_NUM,
                          concat_segments => L_CONC_SEGS
                          );
    p_error_msg1 := FND_FLEX_KEYVAL.ERROR_MESSAGE;

    IF l_valid_combination then

      DBMS_OUTPUT.PUT_LINE('Validation Successful! Creating the Combination...');
      -------------------Create the New CCID--------------------------

      L_CR_COMBINATION := APPS.FND_FLEX_KEYVAL.VALIDATE_SEGS
                          (
                          operation => 'CREATE_COMBINATION',
                          appl_short_name => 'SQLGL',
                          key_flex_code => 'GL#',
                          structure_number => L_STRUCTURE_NUM,
                          concat_segments => L_CONC_SEGS );
          p_error_msg2 := FND_FLEX_KEYVAL.ERROR_MESSAGE;

      IF l_cr_combination THEN
        -------------------Fetch the New CCID--------------------------
        SELECT code_combination_id
          INTO l_ccid
          FROM apps.gl_code_combinations_kfv
        WHERE concatenated_segments = l_conc_segs;
        DBMS_OUTPUT.PUT_LINE('NEW COMBINATION_ID = ' || l_ccid);
      ELSE
        -------------Error in creating a combination-----------------
        DBMS_OUTPUT.PUT_LINE('Error in creating the combination: '||p_error_msg2);
      END IF;
    ELSE
      --------The segments in the account string are not defined in gl value set----------
      DBMS_OUTPUT.PUT_LINE('Error in validating the combination: '||p_error_msg1);
    END IF;
  END IF;
EXCEPTION
WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE(SQLCODE||' '||SQLERRM);
END;

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;

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;

Utility APIs for Concurrent Processing


FND_CONCURRENT.GET_REQUEST_STATUS

This API Returns the Status of a concurrent request. It also returns the completion text if the request is already completed. The return type is Boolean (Returns TRUE on successful retrieval of the information, FALSE otherwise).

  function get_request_status(request_id     IN OUT NOCOPY number,
		              appl_shortname IN varchar2 default NULL,
		              program        IN varchar2 default NULL,
		              phase      OUT NOCOPY varchar2,
		              status     OUT NOCOPY varchar2,
		              dev_phase  OUT NOCOPY varchar2,
		              dev_status OUT NOCOPY varchar2,
		              message    OUT NOCOPY varchar2) return boolean;

The parameters are:

  • REQUEST_ID: Request ID of the program to be checked.
  • APPL_SHORTNAME: Short name of the application associated with the program. The default is NULL.
  • PROGRAM: Short name of the concurrent program. The default is NULL.
  • PHASE: Request phase.
  • STATUS: Request status.
  • DEV_PHASE: Request phase as a string constant.
  • DEV_STATUS: Request status as a string constant.
  • MESSAGE: Request completion message.

FND_CONCURRENT.WAIT_FOR_REQUEST

This API waits for the request completion, then returns the request phase/status and completion message to the caller. It goes to sleep between checks for the request completion. The return type is Boolean (Returns TRUE on successful retrieval of the information, FALSE otherwise).

  function wait_for_request(request_id IN number default NULL,
		  interval   IN  number default 60,
		  max_wait   IN  number default 0,
		  phase      OUT NOCOPY varchar2,
		  status     OUT NOCOPY varchar2,
		  dev_phase  OUT NOCOPY varchar2,
		  dev_status OUT NOCOPY varchar2,
		  message    OUT NOCOPY varchar2) return  boolean;

The parameters are:

  • REQUEST_ID: Request ID of the request to wait on. The default is NULL.
  • INTERVAL: Number of seconds to wait between checks. The default is 60 seconds.
  • MAX_WAIT: Maximum number of seconds to wait for the request completion. The default is 00 seconds.
  • PHASE: User-friendly Request phase.
  • STATUS: User-friendly Request status.
  • DEV_PHASE: Request phase as a constant string.
  • DEV_STATUS: Request status as a constant string.
  • MESSAGE: Request completion message.
There are few other useful apis too.
  • FND_CONCURRENT.SET_COMPLETION_STATUS: Called from a concurrent request to set its completion status and message.
  • FND_CONCURRENT.GET_REQUEST_PRINT_OPTIONS: Returns the print options for a concurrent request.
  • FND_CONCURRENT.GET_SUB_REQUESTS: Get all sub-requests for a given request id. For each sub-request it provides request_id, phase,status, developer phase , developer status and completion text.
  • FND_CONCURRENT.Cancel_Request: It cancels a given Concurrent Request.

API to Assign Item to an Organization in Oracle Inventory


EGO_ITEM_PUB package provides functionality for maintaining items, item revisions, etc. We can use ASSIGN_ITEM_TO_ORG procedure to assign one item to an organization.

The procedure definition is:

  PROCEDURE Assign_Item_To_Org(
      p_api_version             IN      NUMBER
     ,p_init_msg_list           IN      VARCHAR2        DEFAULT  G_FALSE
     ,p_commit                  IN      VARCHAR2        DEFAULT  G_FALSE
     ,p_Inventory_Item_Id       IN      NUMBER          DEFAULT  G_MISS_NUM
     ,p_Item_Number             IN      VARCHAR2        DEFAULT  G_MISS_CHAR
     ,p_Organization_Id         IN      NUMBER          DEFAULT  G_MISS_NUM
     ,p_Organization_Code       IN      VARCHAR2        DEFAULT  G_MISS_CHAR
     ,p_Primary_Uom_Code        IN      VARCHAR2        DEFAULT  G_MISS_CHAR
     ,x_return_status           OUT NOCOPY  VARCHAR2
     ,x_msg_count               OUT NOCOPY  NUMBER);

The parameters are:

  • P_API_VERSION – A decimal number indicating major and minor revisions to the API. Pass 1.0 unless otherwise indicated in the API parameter list.
  • P_INIT_MSG_LIST – A one-character flag indicating whether to initialize the FND_MSG_PUB package’s message stack at the beginning of API processing (and thus remove any messages that may exist on the stack from prior processing in the same session). Valid values are FND_API.G_TRUE and FND_API.G_FALSE.
  • P_COMMIT – A one-character flag indicating whether to commit work at the end of API processing. Valid values are FND_API.G_TRUE and FND_API.G_FALSE.
  • P_INVENTORY_ITEM_ID – Inventory Item Id of the Item
  • P_ITEM_NUMBER – Segment1 of the Item
  • P_ORGANIZATION_ID – Organization Id of the Organization to whom Item must be assigned
  • P_ORGANIZATION_CODE – 3 character Organization Code of the Organization to whom Item must be assigned
  • P_PRIMARY_UOM_CODE – Primary Unit of Measure of the item.
  • X_RETURN_STATUS – A one-character code indicating whether any errors occurred during processing (in which case error messages will be present on the FND_MSG_PUB package’s message stack). Valid values are FND_API.G_RET_STS_SUCCESS, FND_API.G_RET_STS_ERROR, and FND_API.G_RET_STS_UNEXP_ERROR.
  • X_MSG_COUNT – An integer indicating the number of messages on the FND_MSG_PUB package’s message stack at the end of API processing.

Sample Code: (Tested in R12.1.3)

DECLARE
        g_user_id             fnd_user.user_id%TYPE :=NULL;
        l_appl_id             fnd_application.application_id%TYPE;
        l_resp_id             fnd_responsibility_tl.responsibility_id%TYPE;
        l_api_version		  NUMBER := 1.0;
        l_init_msg_list       VARCHAR2(2) := fnd_api.g_false;
        l_commit		      VARCHAR2(2) := FND_API.G_FALSE;
        x_message_list        error_handler.error_tbl_type;
        x_return_status		  VARCHAR2(2);
        x_msg_count		      NUMBER := 0;
BEGIN
        SELECT fa.application_id
          INTO l_appl_id
          FROM fnd_application fa
         WHERE fa.application_short_name = 'INV';

        SELECT fr.responsibility_id
          INTO l_resp_id
          FROM fnd_application fa, fnd_responsibility_tl fr
         WHERE fa.application_short_name = 'INV'
           AND fa.application_id = fr.application_id
           AND UPPER (fr.responsibility_name) = 'INVENTORY';

        fnd_global.apps_initialize (g_user_id, l_resp_id, l_appl_id);

        EGO_ITEM_PUB.ASSIGN_ITEM_TO_ORG(
                   P_API_VERSION          => l_api_version
                ,  P_INIT_MSG_LIST        => l_init_msg_list
                ,  P_COMMIT               => l_commit
                ,  P_INVENTORY_ITEM_ID    => 1003
                ,  p_item_number          => 000000000001035
                ,  p_organization_id      => 11047
                ,  P_ORGANIZATION_CODE    => 'DXN'
                ,  P_PRIMARY_UOM_CODE     => 'EA'
                ,  X_RETURN_STATUS        => x_return_status
                ,  X_MSG_COUNT            => x_msg_count
            );
        DBMS_OUTPUT.PUT_LINE('Status: '||x_return_status);
        IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
          DBMS_OUTPUT.PUT_LINE('Error Messages :');
          Error_Handler.GET_MESSAGE_LIST(x_message_list=>x_message_list);
            FOR j IN 1..x_message_list.COUNT LOOP
              DBMS_OUTPUT.PUT_LINE(x_message_list(j).message_text);
            END LOOP;
        END IF;
EXCEPTION
        WHEN OTHERS THEN
          dbms_output.put_line('Exception Occured :');
          DBMS_OUTPUT.PUT_LINE(SQLCODE ||':'||SQLERRM);
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!

Item Category Creation APIs


There are few APIs in INV_ITEM_CATEGORY_PUB package related to item category. This article will follow a category flexfield structure. Please refer the below post for more detail.

How to Create Category and Category Set in Oracle Inventory?

INV_ITEM_CATEGORY_PUB.Create_Category

DECLARE
l_category_rec    INV_ITEM_CATEGORY_PUB.CATEGORY_REC_TYPE;
l_return_status   VARCHAR2(80);
l_error_code      NUMBER;
l_msg_count       NUMBER;
l_msg_data        VARCHAR2(80);
l_out_category_id NUMBER;
BEGIN
  l_category_rec.segment1 := 'RED';

  SELECT f.ID_FLEX_NUM
    INTO l_category_rec.structure_id
    FROM FND_ID_FLEX_STRUCTURES f
   WHERE f.ID_FLEX_STRUCTURE_CODE = 'INV_COLORS';

  l_category_rec.description := 'Red';

  INV_ITEM_CATEGORY_PUB.Create_Category
          (
          p_api_version   => 1.0,
          p_init_msg_list => FND_API.G_FALSE,
          p_commit        => FND_API.G_TRUE,
          x_return_status => l_return_status,
          x_errorcode     => l_error_code,
          x_msg_count     => l_msg_count,
          x_msg_data      => l_msg_data,
          p_category_rec  => l_category_rec,
          x_category_id   => l_out_category_id
          );
  IF l_return_status = fnd_api.g_ret_sts_success THEN
    COMMIT;
    DBMS_OUTPUT.put_line ('Creation of Item Category is Successful : '||l_out_category_id);
  ELSE
    DBMS_OUTPUT.put_line ('Creation of Item Category Failed with the error :'||l_error_code);
    ROLLBACK;
  END IF;
END ;

INV_ITEM_CATEGORY_PUB. Delete_Category

DECLARE
l_return_status VARCHAR2(80);
l_error_code    NUMBER;
l_msg_count     NUMBER;
l_msg_data      VARCHAR2(80);
l_category_id   NUMBER;
BEGIN
  SELECT mcb.CATEGORY_ID
    INTO l_category_id
    FROM mtl_categories_b mcb
   WHERE mcb.SEGMENT1='RED'
     AND mcb.STRUCTURE_ID =
        (SELECT mcs_b.STRUCTURE_ID
           FROM mtl_category_sets_b mcs_b
          WHERE mcs_b.CATEGORY_SET_ID =
               (SELECT mcs_tl.CATEGORY_SET_ID
                  FROM mtl_category_sets_tl mcs_tl
                 WHERE CATEGORY_SET_NAME ='INV_COLORS_SET'
                 )
        );

    INV_ITEM_CATEGORY_PUB.Delete_Category
          (
          p_api_version     => 1.0,
          p_init_msg_list   => FND_API.G_FALSE,
          p_commit          => FND_API.G_TRUE,
          x_return_status   => l_return_status,
          x_errorcode       => l_error_code,
          x_msg_count       => l_msg_count,
          x_msg_data        => l_msg_data,
          p_category_id     => l_category_id);

  IF l_return_status = fnd_api.g_ret_sts_success THEN
    COMMIT;
    DBMS_OUTPUT.put_line ('Deletion of Item Category is Successful : '||l_category_id);
  ELSE
    DBMS_OUTPUT.put_line ('Deletion of Item Category Failed with the error :'||l_error_code);
    ROLLBACK;
  END IF;
END ;

INV_ITEM_CATEGORY_PUB.Update_Category_Description
Updates the category description.

DECLARE
         l_return_status VARCHAR2(80);
         l_error_code    NUMBER;
         l_msg_count     NUMBER;
         l_msg_data      VARCHAR2(80);
         l_category_id   NUMBER;
         l_description   VARCHAR2(80);
BEGIN
      select mcb.CATEGORY_ID into l_category_id
        from mtl_categories_b mcb
       where mcb.SEGMENT1='BLACK'
         and mcb.STRUCTURE_ID = (select mcs_b.STRUCTURE_ID
             from mtl_category_sets_b mcs_b
             where mcs_b.CATEGORY_SET_ID = (select mcs_tl.CATEGORY_SET_ID
                 from mtl_category_sets_tl mcs_tl
                 where CATEGORY_SET_NAME ='INV_COLORS_SET'));

      l_description := 'new black color';

     INV_ITEM_CATEGORY_PUB.Update_Category_Description (
       p_api_version     => 1.0,
       p_init_msg_list   => FND_API.G_FALSE,
       p_commit          => FND_API.G_TRUE,
       x_return_status   => l_return_status,
       x_errorcode       => l_error_code,
       x_msg_count       => l_msg_count,
       x_msg_data        => l_msg_data,
       p_category_id     => l_category_id,
       p_description     => l_description);

  IF l_return_status = fnd_api.g_ret_sts_success THEN
    COMMIT;
    DBMS_OUTPUT.put_line ('Update of Item Category Description is Successful : '||l_category_id);
  ELSE
    DBMS_OUTPUT.put_line ('Update of Item Category Description Failed with the error :'||l_error_code);
    ROLLBACK;
  END IF;
END ;

Use following API for assigning a category to a category set. A category will be available in the list of valid categoies for a category set only if it is assigned to the category set. This is a required step if for categories enforce list is checked on.

INV_ITEM_CATEGORY_PUB.Create_Valid_Category

Create a record in mtl_category_set_valid_cats.

DECLARE
        l_return_status   VARCHAR2(80);
        l_error_code      NUMBER;
        l_msg_count       NUMBER;
        l_msg_data        VARCHAR2(80);
        l_category_set_id NUMBER;
        l_category_id     NUMBER;
BEGIN
       select mcs_tl.CATEGORY_SET_ID into l_category_set_id
         from mtl_category_sets_tl mcs_tl
        where mcs_tl.CATEGORY_SET_NAME ='INV_COLORS_SET';

       select mcb.CATEGORY_ID into l_category_id
         from mtl_categories_b mcb
        where mcb.SEGMENT1='RED'
          and mcb.STRUCTURE_ID = (select mcs_b.STRUCTURE_ID
              from mtl_category_sets_b mcs_b
              where mcs_b.CATEGORY_SET_ID = (select mcs_tl.CATEGORY_SET_ID
                    from mtl_category_sets_tl mcs_tl
                    where CATEGORY_SET_NAME ='INV_COLORS_SET'));

       INV_ITEM_CATEGORY_PUB.Create_Valid_Category (
             p_api_version        => 1.0,
             p_init_msg_list      => FND_API.G_FALSE,
             p_commit             => FND_API.G_TRUE,
             x_return_status      => l_return_status,
             x_errorcode          => l_error_code,
             x_msg_count          => l_msg_count,
             x_msg_data           => l_msg_data,
             p_category_set_id    => l_category_set_id,
             p_category_id        => l_category_id,
             p_parent_category_id => NULL );

  IF l_return_status = fnd_api.g_ret_sts_success THEN
    COMMIT;
    DBMS_OUTPUT.put_line ('Create Valid Category is Successful : '||l_category_id);
  ELSE
    DBMS_OUTPUT.put_line ('Create Valid Category Failed with the error :'||l_error_code);
    ROLLBACK;
  END IF;
END ;

INV_ITEM_CATEGORY_PUB.Delete_Valid_Category

Delete the record from mtl_category_set_valid_cats.

DECLARE
           l_return_status    VARCHAR2(80);
           l_error_code       NUMBER;
           l_msg_count        NUMBER;
           l_msg_data         VARCHAR2(80);
           l_category_set_id  NUMBER;
           l_category_id      NUMBER;
BEGIN
         select mcs_tl.CATEGORY_SET_ID into l_category_set_id
           from mtl_category_sets_tl mcs_tl
          where mcs_tl.CATEGORY_SET_NAME ='INV_COLORS_SET';

         select mcb.CATEGORY_ID into l_category_id
           from mtl_categories_b mcb
          where mcb.SEGMENT1='RED'
            and mcb.STRUCTURE_ID = (select mcs_b.STRUCTURE_ID
                from mtl_category_sets_b mcs_b
                where mcs_b.CATEGORY_SET_ID = (select mcs_tl.CATEGORY_SET_ID
                  from mtl_category_sets_tl mcs_tl
                  where CATEGORY_SET_NAME ='INV_COLORS_SET'));

      INV_ITEM_CATEGORY_PUB.Delete_Valid_Category (
            p_api_version      => 1.0,
            p_init_msg_list    => FND_API.G_FALSE,
            p_commit           => FND_API.G_TRUE,
            x_return_status    => l_return_status,
            x_errorcode        => l_error_code,
            x_msg_count        => l_msg_count,
            x_msg_data         => l_msg_data,
            p_category_set_id  => l_category_set_id,
            p_category_id      => l_category_id);

  IF l_return_status = fnd_api.g_ret_sts_success THEN
    COMMIT;
    DBMS_OUTPUT.put_line ('Delete Valid Category is Successful : '||l_category_id);
  ELSE
    DBMS_OUTPUT.put_line ('Delete Valid Category Failed with the error :'||l_error_code);
    ROLLBACK;
  END IF;
END ;

The above scripts are tested in R12.1.3

Thanks….

How to Import Item Catalog Descriptive Element Values?


Oracle has provided the below two public APIs to import Item Catalog Descriptive Element Values.

  1. inv_item_catalog_elem_pub.process_item_catalog_grp_recs
  2.  inv_item_catalog_elem_pub.process_item_descr_elements

The first API, process_item_catalog_grp_recs, is used in batch mode to import element values for a SET of items. The second API, process_item_descr_elements, is used to import element values for a SINGLE item.

Using Procedure process_item_catalog_grp_recs:

1] Get the Item Number.

2] Get the item_catalog_group_id from mtl_item_catalog_groups.

3] Get the Descriptive Element Names (element_name) from the table mtl_descriptive_elements for the above item_catalog_group_id.

4] Insert the data into mtl_desc_elem_val_interface table.

INSERT INTO mtl_desc_elem_val_interface (
       item_number,
       element_name,
       element_value,
       element_sequence,
       process_flag,
       set_process_id )
VALUES (
       l_item_number, 	-- Derived in Step1
       l_element_name, 	-- Derived in Step3
       l_element_value,	-- Insert the Descriptive Element Value from data file
       10, 	            -- Insert the element sequence
       1,
       1
       );

5] Run loop to put all the data (element name-value pair) into the above interface table.

6] Run loop to put data for multiple items.

7] Finally run the below API.

inv_item_catalog_elem_pub.process_item_catalog_grp_recs
          (
          errbuf                 	=> l_errbuf,
          retcode                	=> l_retcode,
          p_rec_set_id           	=> 1,
          p_upload_rec_flag      	=> 1,
          p_delete_rec_flag      	=> 1,
          p_commit_flag          	=> 1,
          p_prog_appid           	=> NULL,
          p_prog_id              	=> NULL,
          p_request_id           	=> NULL,
          p_user_id              	=> NULL,
          p_login_id             	=> NULL
          );

The parameters are described below:

Parameter Type Meaning
errbuf Out It holds the error message, if any.
retcode Out It should be zero if there were no errors
p_rec_set_id In Used to group the rows, should be set to value of “set_process_id” in mtl_desc_elem_val_interface table
p_upload_rec_flag In Whether the rows in interface table are to be uploaded to database. default 1
p_delete_rec_flag In Whether the rows in interface table are to be deleted after they have been uploaded to database. default 1
p_commit_flag In Whether the uploaded rows need to be committed to the database. default 1
p_prog_appid In default null
p_prog_id In default null
p_request_id In default null
p_user_id In default null
p_login_id In default null

Using Procedure process_item_descr_elements:

Define necessary values programmatically in PL/SQL variables and tables and call this API. Do not populate the interface table.

The declaration of this API:

PROCEDURE Process_item_descr_elements
     (
        p_api_version        	   IN   NUMBER
     ,  p_init_msg_list      		IN   VARCHAR2
     ,  p_commit_flag        		IN   VARCHAR2
     ,  p_validation_level   		IN   NUMBER
     ,  p_inventory_item_id  		IN   NUMBER
     ,  p_item_number        		IN   VARCHAR2
     ,  p_item_desc_element_table 	IN  ITEM_DESC_ELEMENT_TABLE
     ,  x_generated_descr    		OUT NOCOPY VARCHAR2
     ,  x_return_status      		OUT NOCOPY VARCHAR2
     ,  x_msg_count          		OUT NOCOPY NUMBER
     ,  x_msg_data           		OUT NOCOPY VARCHAR2
     );

The parameters are:

Parameter Meaning
p_api_version The version of this API. Value=1.0
p_init_msg_list If set to true initially, messages generated internally by the API will be captured.  Value = fnd_api.g_TRUE, if messages need to be captured, else accept default
p_commit_flag Whether the uploaded rows need to be committed to the database. Value = fnd_api.g_TRUE, to commit else accept default.
p_validation_level Determines if item_number is to be converted to item_id. If item_id is specified, set Value = g_VALIDATE_NONE, Else take default.
p_inventory_item_id Item id of the item for which the element values need to be uploaded.
p_item_number Item number of the item.
p_item_desc_element_table PL/SQL table of records; each record will hold element name, value and description.
x_generated_descr Element values are concatenated to generate item description.
x_return_status The return status of the API.
x_msg_count Count of messages generated.
x_msg_data Holds the messages generated.