pa_budget_pub.create_draft_budget - single record insert for revenue financial plans

DECLARE
   l_return_status                  VARCHAR2 (1);
   l_msg_count                      NUMBER;
   l_msg_data                       VARCHAR2 (2000);
   l_data                           VARCHAR2 (2000);
   l_msg_index_out                  NUMBER;
   ln_line_no                       NUMBER;
-- variables needed for the user_id and responsibility_id
   l_user_id                        NUMBER;
   l_responsibility_id              NUMBER;
-- variables needed for Oracle Project specific parameters
   l_pm_product_code                VARCHAR2 (10);
   l_pa_project_id                  NUMBER;
   l_pm_project_reference           VARCHAR2 (25);
   l_budget_type_code               VARCHAR2 (30);
   l_change_reason_code             VARCHAR2 (30);
   l_description                    VARCHAR2 (255);
   l_entry_method_code              VARCHAR2 (30);
   l_resource_list_id               NUMBER;
   l_resource_list_member_id        NUMBER;
   l_budget_lines_in                pa_budget_pub.budget_line_in_tbl_type;
   l_budget_lines_in_rec            pa_budget_pub.budget_line_in_rec_type;
   l_budget_lines_out               pa_budget_pub.budget_line_out_tbl_type;
   api_error                        EXCEPTION;
   v_version_type                   pa_budget_versions.version_type%TYPE
                                     := pa_interface_utils_pub.g_pa_miss_char;
   v_time_phased_code               pa_proj_fp_options.cost_time_phased_code%TYPE
                                     := pa_interface_utils_pub.g_pa_miss_char;
   v_fin_plan_level_code            pa_proj_fp_options.cost_fin_plan_level_code%TYPE
                                     := pa_interface_utils_pub.g_pa_miss_char;
   v_plan_in_multi_curr_flag        pa_proj_fp_options.plan_in_multi_curr_flag%TYPE
                                     := pa_interface_utils_pub.g_pa_miss_char;
   v_change_reason_code             pa_budget_versions.change_reason_code%TYPE
                                     := pa_interface_utils_pub.g_pa_miss_char;
   v_raw_cost_flag                  pa_fin_plan_amount_sets.raw_cost_flag%TYPE
                                     := pa_interface_utils_pub.g_pa_miss_char;
   v_burdened_cost_flag             pa_fin_plan_amount_sets.burdened_cost_flag%TYPE
                                     := pa_interface_utils_pub.g_pa_miss_char;
   v_revenue_flag                   pa_fin_plan_amount_sets.revenue_flag%TYPE
                                     := pa_interface_utils_pub.g_pa_miss_char;
   v_cost_qty_flag                  pa_fin_plan_amount_sets.cost_qty_flag%TYPE
                                     := pa_interface_utils_pub.g_pa_miss_char;
   v_revenue_qty_flag               pa_fin_plan_amount_sets.revenue_qty_flag%TYPE
                                     := pa_interface_utils_pub.g_pa_miss_char;
   v_all_qty_flag                   pa_fin_plan_amount_sets.all_qty_flag%TYPE
                                     := pa_interface_utils_pub.g_pa_miss_char;
   v_create_new_curr_working_flag   VARCHAR2 (1);
   v_replace_current_working_flag   VARCHAR2 (1);
   v_using_resource_lists_flag      VARCHAR2 (1);
   g_init_msg_list                  NUMBER;
BEGIN
   v_version_type := 'REVENUE';
--   v_time_phased_code := 'P';
   v_fin_plan_level_code := 'L';
   v_plan_in_multi_curr_flag := 'N';
   v_change_reason_code := NULL;
   v_raw_cost_flag := 'N';
   v_burdened_cost_flag := 'N';
   v_revenue_flag := 'Y';
   v_cost_qty_flag := 'N';
   v_revenue_qty_flag := 'Y';
   v_all_qty_flag := 'Y';
   v_create_new_curr_working_flag := 'Y';
   v_replace_current_working_flag := 'Y';
   v_using_resource_lists_flag := 'Y';
-- PRODUCT RELATED DATA
   l_pm_product_code := 'MSPROJECT';
-- BUDGET DATA
   l_budget_type_code := NULL;
   l_change_reason_code := NULL;
   l_description := 'New Description -> 2';
   l_entry_method_code := NULL;
   DBMS_OUTPUT.put_line (:p_resource_list_member_id);
   ln_line_no := NVL (ln_line_no, 0) + 1;
   l_pa_project_id := :p_project_id;
   l_pm_project_reference := :p_project_id;
   l_budget_lines_in_rec.pa_task_id := :p_task_id;
   l_budget_lines_in_rec.resource_list_member_id :=
                                                   :p_resource_list_member_id;
   l_budget_lines_in_rec.quantity := :p_hours_by_role;
   l_budget_lines_in_rec.revenue :=
                                 (:p_hours_by_role * :p_converted_pl_revenue
                                 );
   l_budget_lines_in (ln_line_no) := l_budget_lines_in_rec;

-- GET GLOBAL INFO
   SELECT user_id, responsibility_id
     INTO l_user_id, l_responsibility_id
     FROM pa_user_resp_v
    WHERE user_name = :p_user_name
      AND responsibility_name = :p_responsibility_name;

   pa_interface_utils_pub.set_global_info
                                  (p_api_version_number      => 1.0,
                                   p_responsibility_id       => l_responsibility_id,
                                   p_user_id                 => l_user_id,
                                   p_msg_count               => l_msg_count,
                                   p_msg_data                => l_msg_data,
                                   p_return_status           => l_return_status
                                  );
   pa_budget_pub.init_budget;
   pa_budget_pub.create_draft_budget
            (p_api_version_number                => 1.0,
             p_msg_count                         => l_msg_count,
             p_commit                            => 'T',
             p_init_msg_list                     => g_init_msg_list,
             p_msg_data                          => l_msg_data,
             p_return_status                     => l_return_status,
             p_pm_product_code                   => 'PA',
             p_pa_project_id                     => l_pa_project_id,
             p_budget_type_code                  => NULL,
             p_entry_method_code                 => NULL,
             --
             p_fin_plan_type_id                  => :p_financial_plan_id,
             p_fin_plan_type_name                => :p_financial_plan_name,
             --v_fin_plan_type_name,
             p_version_type                      => v_version_type,
             p_time_phased_code                  => v_time_phased_code,
             p_resource_list_id                  => l_resource_list_id,
             p_fin_plan_level_code               => v_fin_plan_level_code,
             p_plan_in_multi_curr_flag           => v_plan_in_multi_curr_flag,
             p_budget_version_name               => 'Working',
             --v_budget_version_name,
             p_description                       => 'Working',
             p_change_reason_code                => v_change_reason_code,
             p_raw_cost_flag                     => v_raw_cost_flag,
             p_burdened_cost_flag                => v_burdened_cost_flag,
             p_revenue_flag                      => v_revenue_flag,
             p_cost_qty_flag                     => v_cost_qty_flag,
             p_revenue_qty_flag                  => v_revenue_qty_flag,
             p_all_qty_flag                      => 'N',
             p_create_new_curr_working_flag      => v_create_new_curr_working_flag,
             p_replace_current_working_flag      => v_replace_current_working_flag,
             p_using_resource_lists_flag         => v_using_resource_lists_flag,
             p_budget_lines_in                   => l_budget_lines_in,
             p_budget_lines_out                  => l_budget_lines_out
            );

   IF l_return_status != 'S'
   THEN
      DBMS_OUTPUT.put_line ('ERROR WHILE CREATING BUDGET ');
      RAISE api_error;
   ELSE
      DBMS_OUTPUT.put_line ('SUCCESS ');
      COMMIT;
   END IF;

   FOR i IN 1 .. l_budget_lines_out.COUNT
   LOOP
      DBMS_OUTPUT.put_line ('create draft budget was successful for line '
                            || i
                           );
      DBMS_OUTPUT.put_line (   'Return Code = '
                            || l_budget_lines_out (i).return_status
                           );
   END LOOP;

-- CLEAR_BUDGET
   pa_budget_pub.clear_budget;

   IF l_return_status != 'S'
   THEN
      DBMS_OUTPUT.put_line ('ERROR WHILE CLEAR_BUDGET');
      RAISE api_error;
   END IF;
-- HANDLE EXCEPTIONS
EXCEPTION
   WHEN api_error
   THEN
      FOR i IN 1 .. l_msg_count
      LOOP
         pa_interface_utils_pub.get_messages
                                          (p_msg_data           => l_msg_data,
                                           p_data               => l_data,
                                           p_msg_count          => l_msg_count,
                                           p_msg_index_out      => l_msg_index_out
                                          );
         DBMS_OUTPUT.put_line ('error msg ' || l_data);
         DBMS_OUTPUT.put_line ('error msg222 ' || l_msg_data);
         ROLLBACK;
      END LOOP;
   WHEN OTHERS
   THEN
      FOR i IN 1 .. l_msg_count
      LOOP
         pa_interface_utils_pub.get_messages
                                          (p_msg_data           => l_msg_data,
                                           p_data               => l_data,
                                           p_msg_count          => l_msg_count,
                                           p_msg_index_out      => l_msg_index_out
                                          );
         DBMS_OUTPUT.put_line ('error msg ' || l_data);
         DBMS_OUTPUT.put_line ('error msg111 ' || l_msg_data);
         ROLLBACK;
      END LOOP;
END;
/