Tuesday, October 10, 2017

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;

Tuesday, July 28, 2015

SQL to convert GMT to EST with Daylight Savings

SELECT to_date(
           to_char(
             to_timestamp_tz(
               to_char(to_timestamp('2015-11-01 06:00:00','yyyy-mm-dd hh24:mi:ss'),'YYYYMMDDHH24:MI:SS')||' '||'GMT'
                            ,'YYYYMMDDHH24:MI:SS TZR') at time zone 'America/New_York'
                   ,'YYYYMMDDHH24:MI:SS')
                 ,'YYYYMMDDHH24:MI:SS') datelight_GMT_EST
  FROM DUAL

Wednesday, April 29, 2015

Supplier Remittance Advice

For remittance advice Oracle fetches the email address from 'iby_external_payees_all' table.

When testing any changes to the remittance advice make sure to update the email before running the concurrent program.

SELECT   aps.segment1 "Vendor Number"
        ,aps.vendor_name "Vendor Name"
        ,iepa.remit_advice_email "Remittance Advice Email"
  FROM  ap_suppliers aps
       ,iby_external_payees_all iepa
 WHERE  iepa.payee_party_id = aps.party_id
   AND  aps.vendor_name= '<>';


You can also see the same at Payment Details -> Supplier / Supplier Site Level. Navigate to “Separate Remittance Advice Delivery” Tab.

Wednesday, February 25, 2015

Update Workflow Administrator Role in Oracle Applications 11i/R12 from backend

For looking at workflow details that are owned by other users or status diagram in Oracle Applications use the following update statement to update.

By default (in 11i & R12) this role is set to user sysadmin (In old versions 11.5.8 or prior, it used to set to *)

UPDATE wf_resources
   SET text = ’ * ’
 WHERE NAME = ’wf_admin_role’;