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

pa_budget_pub.create_draft_finplan - Single Record Script

DECLARE
   v_commit                         VARCHAR2 (1)                       := 'F';
   v_init_msg_list                  VARCHAR2 (1);
   v_data                           VARCHAR2 (2000);
   v_msg_index_out                  VARCHAR2 (30);
   v_pm_product_code                pa_budget_versions.pm_product_code%TYPE
                                                               := 'MSPROJECT';
   v_pm_finplan_reference           pa_budget_versions.pm_budget_reference%TYPE;
   v_pm_project_reference           pa_projects_all.pm_project_reference%TYPE
                                                        := 'API TEST PROJECT';
   v_pa_project_id                  pa_budget_versions.project_id%TYPE
                                                                    := 117829;
   v_fin_plan_type_id               pa_budget_versions.fin_plan_type_id%TYPE;
   v_fin_plan_type_name             pa_fin_plan_types_vl.NAME%TYPE
                                                    := 'Approved Cost Budget';
   v_version_type                   pa_budget_versions.version_type%TYPE
                                                                    := 'COST';
   v_time_phased_code               VARCHAR2 (1)                       := 'P';
   v_resource_list_name             pa_resource_lists.NAME%TYPE;
   v_resource_list_id               pa_budget_versions.resource_list_id%TYPE;
   v_fin_plan_level_code            VARCHAR2 (1)                       := 'L';
   v_budget_version_name            pa_budget_versions.version_name%TYPE
                                                                       := '1';
   v_description                    pa_budget_versions.description%TYPE
                                                    := 'Approved Cost Budget';
   v_change_reason_code             pa_budget_versions.change_reason_code%TYPE;
   v_raw_cost_flag                  pa_fin_plan_amount_sets.raw_cost_flag%TYPE
                                                                       := 'Y';
   v_burdened_cost_flag             pa_fin_plan_amount_sets.burdened_cost_flag%TYPE
                                                                       := 'Y';
   v_cost_qty_flag                  pa_fin_plan_amount_sets.cost_qty_flag%TYPE
                                                                       := 'Y';
   v_create_new_curr_working_flag   VARCHAR2 (1)                       := 'Y';
   v_finplan_trans_tab              pa_budget_pub.finplan_trans_tab;
   v_replace_current_working_flag   VARCHAR2 (1);
   v_using_resource_lists_flag      VARCHAR2 (1);
   v_finplan_version_id             NUMBER;
   v_return_status                  VARCHAR2 (1);
   v_msg_count                      NUMBER;
   v_msg_data                       VARCHAR2 (2000);
   l_user_id                        NUMBER                           := 51711;
   l_responsibility_id              NUMBER                           := 22593;
   l_org_id                         NUMBER                             := 159;
   api_error                        EXCEPTION;
BEGIN
   v_msg_count := NULL;
   v_msg_data := NULL;
   DBMS_OUTPUT.put_line ('Setting global variables');

   SELECT fin_plan_type_id
     INTO v_fin_plan_type_id
     FROM pa_fin_plan_types_tl
    WHERE LANGUAGE = 'US' AND NAME = 'Approved Cost Budget';

   SELECT resource_list_id
     INTO v_resource_list_id
     FROM pabv_resource_lists
    WHERE NAME = 'Forecast Roles';

   -- Projects Responsibility
   DBMS_OUTPUT.put_line ('Setting global info');
   pa_interface_utils_pub.set_global_info
                                  (p_api_version_number      => 1,
                                   p_responsibility_id       => l_responsibility_id,
                                   p_user_id                 => l_user_id,
                                   p_operating_unit_id       => l_org_id,
                                   p_calling_mode            => 'AMG',
                                   p_msg_count               => v_msg_count,
                                   p_msg_data                => v_msg_data,
                                   p_return_status           => v_return_status
                                  );
   mo_global.set_policy_context ('S', l_org_id);
   DBMS_OUTPUT.put_line ('Calling init budget');
   pa_budget_pub.init_budget;
   v_finplan_trans_tab (1).pm_product_code := 'MSPROJECT';
   --'PA';
   v_finplan_trans_tab (1).task_id := 6468200;
   v_finplan_trans_tab (1).pm_task_reference := 646820011;
   v_finplan_trans_tab (1).pm_res_asgmt_reference := NULL;
   v_finplan_trans_tab (1).currency_code := 'GBP';
   v_finplan_trans_tab (1).unit_of_measure_code := 'HOURS';
   v_finplan_trans_tab (1).start_date :=
                                        TO_DATE ('01-MAR-2016', 'DD-MON-YYYY');
   v_finplan_trans_tab (1).end_date := TO_DATE ('31-DEC-2016', 'DD-MON-YYYY');
   v_finplan_trans_tab (1).cost_code := NULL;
   v_finplan_trans_tab (1).cbs_element_id := NULL;
   v_finplan_trans_tab (1).quantity := 10;
   v_finplan_trans_tab (1).raw_cost := 5000;
   v_finplan_trans_tab (1).burdened_cost := 5000;
   ---v_finplan_trans_tab(1).revenue:=NULL;
   v_finplan_trans_tab (1).resource_list_member_id := 168012;
   v_finplan_trans_tab (1).attribute_category := NULL;
   v_finplan_trans_tab (1).attribute1 := NULL;
   v_finplan_trans_tab (1).attribute1 := NULL;
   v_finplan_trans_tab (1).attribute2 := NULL;
   v_finplan_trans_tab (1).attribute3 := NULL;
   v_finplan_trans_tab (1).attribute4 := NULL;
   v_finplan_trans_tab (1).attribute5 := NULL;
   v_finplan_trans_tab (1).attribute6 := NULL;
   v_finplan_trans_tab (1).attribute7 := NULL;
   v_finplan_trans_tab (1).attribute8 := NULL;
   v_finplan_trans_tab (1).attribute9 := NULL;
   v_finplan_trans_tab (1).attribute10 := NULL;
   v_finplan_trans_tab (1).attribute11 := NULL;
   v_finplan_trans_tab (1).attribute12 := NULL;
   v_finplan_trans_tab (1).attribute13 := NULL;
   v_finplan_trans_tab (1).attribute14 := NULL;
   v_finplan_trans_tab (1).attribute15 := NULL;
   v_finplan_trans_tab (1).attribute16 := NULL;
   v_finplan_trans_tab (1).attribute17 := NULL;
   v_finplan_trans_tab (1).attribute18 := NULL;
   v_finplan_trans_tab (1).attribute19 := NULL;
   v_finplan_trans_tab (1).attribute20 := NULL;
   v_finplan_trans_tab (1).attribute21 := NULL;
   v_finplan_trans_tab (1).attribute22 := NULL;
   v_finplan_trans_tab (1).attribute23 := NULL;
   v_finplan_trans_tab (1).attribute24 := NULL;
   v_finplan_trans_tab (1).attribute25 := NULL;
   v_finplan_trans_tab (1).attribute26 := NULL;
   v_finplan_trans_tab (1).attribute27 := NULL;
   v_finplan_trans_tab (1).attribute28 := NULL;
   v_finplan_trans_tab (1).attribute29 := NULL;
   v_finplan_trans_tab (1).attribute30 := NULL;
   DBMS_OUTPUT.put_line ('Calling create draft finplan');
   pa_budget_pub.create_draft_finplan
            (p_api_version_number                => 1.0,
             p_commit                            => v_commit,
             p_init_msg_list                     => v_init_msg_list,
             p_pm_product_code                   => v_pm_product_code,
             p_pm_finplan_reference              => v_pm_finplan_reference,
             p_pm_project_reference              => v_pm_project_reference,
             p_pa_project_id                     => v_pa_project_id,
             p_fin_plan_type_id                  => v_fin_plan_type_id,
             p_fin_plan_type_name                => v_fin_plan_type_name,
             p_version_type                      => v_version_type,
             p_time_phased_code                  => v_time_phased_code,
             p_resource_list_name                => v_resource_list_name,
             p_resource_list_id                  => v_resource_list_id,
             p_fin_plan_level_code               => v_fin_plan_level_code,
             p_budget_version_name               => v_budget_version_name,
             p_description                       => v_description,
             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_cost_qty_flag                     => v_cost_qty_flag,
             p_create_new_curr_working_flag      => v_create_new_curr_working_flag,
             p_using_resource_lists_flag         => v_using_resource_lists_flag,
             p_finplan_trans_tab                 => v_finplan_trans_tab,
             x_finplan_version_id                => v_finplan_version_id,
             x_return_status                     => v_return_status,
             x_msg_count                         => v_msg_count,
             x_msg_data                          => v_msg_data
            );

   IF v_return_status != 'S'
   THEN
      DBMS_OUTPUT.put_line ('Create fin plan ended with errors');
      RAISE api_error;
   ELSE
      DBMS_OUTPUT.put_line ('Create fin plan called successfully');
   END IF;

   DBMS_OUTPUT.put_line ('Calling clear budget');
   pa_budget_pub.clear_budget;
EXCEPTION
   WHEN api_error
   THEN
      FOR i IN 1 .. v_msg_count
      LOOP
         pa_interface_utils_pub.get_messages
                                          (p_encoded            => 'F',
                                           p_msg_index          => i,
                                           p_msg_data           => v_msg_data,
                                           p_data               => v_data,
                                           p_msg_count          => v_msg_count,
                                           p_msg_index_out      => v_msg_index_out
                                          );
         DBMS_OUTPUT.put_line ('An error occurred: ' || v_msg_data);
         DBMS_OUTPUT.put_line (fnd_msg_pub.get (p_msg_index      => i,
                                                p_encoded        => 'F'
                                               )
                              );
      END LOOP;
END;
/

FND_LOOKUP - Single Record Script

DECLARE
   ln_rowid1   VARCHAR2 (1000);
BEGIN
      fnd_lookup_values_pkg.insert_row
                          (x_rowid                    => ln_rowid1,
                           x_lookup_type              => :p_lookup_type,
                           x_security_group_id        => 0,
                           x_view_application_id      => :p_application_id,
                           x_lookup_code              => :p_lookup_code,
                           x_tag                      => null,
                           x_attribute_category       => null,
                           x_attribute1               => null,
                           x_attribute2               => null,
                           x_attribute3               => null,
                           x_attribute4               => null,
                           x_enabled_flag             => 'Y',
                           x_start_date_active        => TO_DATE
                                                               ('01-JAN-1950',
                                                                'DD-MON-YYYY'
                                                               ),
                           x_end_date_active          => NULL,
                           x_territory_code           => NULL,
                           x_attribute5               => NULL,
                           x_attribute6               => NULL,
                           x_attribute7               => NULL,
                           x_attribute8               => NULL,
                           x_attribute9               => NULL,
                           x_attribute10              => NULL,
                           x_attribute11              => NULL,
                           x_attribute12              => NULL,
                           x_attribute13              => NULL,
                           x_attribute14              => NULL,
                           x_attribute15              => NULL,
                           x_meaning                  => :p_meaning,
                           x_description              => :p_description,
                           x_creation_date            => SYSDATE,
                           x_created_by               => 0,
                           x_last_update_date         => SYSDATE,
                           x_last_updated_by          => 0,
                           x_last_update_login        => -1
                          );
      DBMS_OUTPUT.put_line (ln_rowid1);
      COMMIT;
END;

Project UDA - Single Record Script

DECLARE
   lc_return_status_gi             VARCHAR2 (15);
   lc_return_message_gi            VARCHAR2 (4000);
   l_debug_mode                    VARCHAR2 (200)                      := 'N';
   l_debug_level                   NUMBER                                := 0;
   l_failed_row_id_list            VARCHAR2 (200);
   x_errorcode                     NUMBER;
   l_init_msg_list                 VARCHAR2 (200)          := fnd_api.g_false;
   l_ext_attr_data_table           pa_project_pub.pa_ext_attr_table_type;
   g_api_version_number   CONSTANT NUMBER                              := 1.0;
   g_commit               CONSTANT VARCHAR2 (1)                        := 'T';
   x_msg_count                     NUMBER;
   x_msg_data                      VARCHAR2 (2000);
   x_return_status                 VARCHAR2 (2000);
BEGIN
 
   pa_project_pub.init_project;
   fnd_msg_pub.initialize;
   l_ext_attr_data_table (1).proj_element_id := NULL;
   l_ext_attr_data_table (1).attr_group_id := ;
   l_ext_attr_data_table (1).row_identifier := ;
   l_ext_attr_data_table (1).attr_int_name := '';
   l_ext_attr_data_table (1).attr_disp_value := '';
   pa_user_attr_pub.process_user_attrs_data
                              (p_api_version                  => g_api_version_number,
                               p_object_name                  => 'PA_PROJECTS',
                               p_ext_attr_data_table          => l_ext_attr_data_table,
                               p_project_id                   => :PROJECT_ID,
                               p_structure_type               => 'WORKPLAN',
                               p_debug_mode                   => l_debug_mode,
                               p_debug_level                  => l_debug_level,
                               p_init_msg_list                => l_init_msg_list,
                               p_log_errors                   => 'Y',
                               p_write_to_concurrent_log      => 'Y',
                               p_commit                       => g_commit,
                               x_failed_row_id_list           => l_failed_row_id_list,
                               x_return_status                => x_return_status,
                               x_errorcode                    => x_errorcode,
                               x_msg_count                    => x_msg_count,
                               x_msg_data                     => x_msg_data
                              );
   DBMS_OUTPUT.put_line (x_return_status);
   DBMS_OUTPUT.put_line (x_msg_data);
END;