Oracle Apps Gurus

Oracle Fusion: sample datafile .dat file to upload HR Person Record

2018-06-16T15:32:00.000-07:00

MERGE|Worker|HRC_SQLLOADER|00915NTYI_ID||2015/08/31|4712/12/31|2015/08/31|1958/07/27|00915NTYI|||HIRE|||00915NTYI_ID

MERGE|PersonLegislativeData|HRC_SQLLOADER|00915NTYI_LEGIS|2015/08/31|4712/12/31|00915NTYI_ID|US|M|M||00915NTYI_LEGIS

MERGE|PersonName|HRC_SQLLOADER|00915NTYI_NAME||2015/08/31|4712/12/31|US|00915NTYI_ID|00915NTYI|GLOBAL|AAatricia||LNicon||00915NTYI_NAME

MERGE|PersonAddress|HRC_SQLLOADER|00915NTYI_ADD|2015/08/31|4712/12/31|00915NTYI_ID|00915NTYI|HOME|2901 Jasmine Street||||||San Francisco||California||US|94123|||||00915NTYI_ADD

MERGE|WorkTerms|HRC_SQLLOADER|00915NTYI_AWT|2015/08/31|4712/12/31|1|Y|||||||||||US1 Business Unit||||Full-Time Regular||||||S|||||||||N|40|W|||||00915NTYI|2015/08/31|E||||||2015/08/31||||||||||||||||||||Full Time|Regular|ET|||00915NTYI_AWR|00915NTYI_ID|US1 Legal Entity|HIRE||||||||||00915NTYI_AWT

MERGE|Assignment|HRC_SQLLOADER|00915NTYI_ASS|2015/08/31|4712/12/31|HIRE|1|Y|||||E|US1 Business Unit||Y|Y||US1 Legal Entity||||00915NTYI_ID||N||00915NTYI_AWT|Full-Time Regular|W|S|40|||||||||||||||Prof06||||||JOB033||ComGlobOps||00915NTYI|2015/08/31|E||POS033|||2015/08/31||||||||||||||||||Full Time|Regular|||||||00915NTYI_ASS

Oracle Fusion: sample datafile .dat file to upload HR Job Family

2018-06-16T15:30:00.001-07:00

METADATA|JobFamily|SourceSystemOwner|SourceSystemId|JobFamilyId|EffectiveStartDate|EffectiveEndDate|JobFamilyName|ActionReasonCode|ActiveStatus
MERGE|JobFamily|HRC_SQLLOADER|600||1951/01/01|4712/12/31|AA-Pharmaceutical Development||A
MERGE|JobFamily|HRC_SQLLOADER|601||1951/01/01|4712/12/31|AA-Legal||A
MERGE|JobFamily|HRC_SQLLOADER|602||1951/01/01|4712/12/31|AA-Information Technology||A

After sucessfully loading the datafile navigate to My Workforce -> Workforce Structures to validate if all the data got loaded correctly

Oracle Fusion HCM Data Validator

2018-06-07T19:11:00.000-07:00

In order to validate the data before loading them to an Instance Oracle has provided a tool to validate the DAT file. More detailed information is available under the following metalink note

Data File Validator Tool for HCM Data Loader (Doc ID 2022617.1)

To validate the DAT files, provide the following and click Validate:

Configuration file –> Click Browse … and select the configuration file that contains the structure and validation definitions. You can use the configuration file delivered in the HDLDataFileValidationTool.zip. Alternatively, you can alter this configuration file to add your own validation.
Data file / Archive file –> Click Browse … and select the .dat file or compressed zip file you want to validate. You can choose to provide a single .dat file or a zip file containing one or more .dat files.

Once you click Validate, the status information appears directly within the utility. The tool also generates an HTML report once the validation is complete. The HTML report provides a summary of the results

Load HR Jobs in Oracle Fusion

2018-06-07T18:05:00.003-07:00

All the template files for every business object supported by HDL form can be downloaded from the system. The following metalink note can be used as a starting point for preparation of DAT files.

HCM Data Loader: Business Object Documentation (Doc ID 2020600.1)

The .dat file layout can be downloaded from the following navigation:

Select Navigator > Data Exchange to open the Dataload work area.

Click Initiate Data Load

If you are trying to upload HR JOBS then Search for JOB and then click on the Download option

The Job template will look something as the following:

To load a sample datafile update the .dat file with the following data.

METADATA|Job|SourceSystemOwner|SourceSystemId|JobId|EffectiveStartDate|EffectiveEndDate|SetId|SetCode|Name|JobCode|ActionReasonCode|JobFamilyId|JobFamilyName|ActiveStatus|ApprovalAuthority
MERGE|Job|HRC_SQLLOADER|300||1951/01/01|4712/12/31||COMMON|AA-Principal Scientist|AA_10||||A|4
MERGE|Job|HRC_SQLLOADER|301||1951/01/01|4712/12/31||COMMON|AA-Sr Principal Scientist|AA_11||||A|5

Save the file as Job.dat and zip the file with Job.zip

Now that the file has been created. Use the Import and Load Data option under HCM Data Loader

Click on the Import File -> Import Local File.
Browse the zip file from local machine and hit the Submit button

After submitting the file, you can use the refresh button to see the progress. If there are no errors in the datafile it number of records will show under the Complete column. For any errors the records will be shown under the Failed column and the error messages will be prompted on the Messages column.

After importing the data navigate to My Workforce -> Workforce Structures

Under Manage Jobs search for the jobs that were uploaded using the .dat file

HCM Data Loader

2018-06-06T19:26:00.004-07:00

HCM Data Loader is a tool that can be used to load business objects for most Oracle Fusion HCM products. Apart from creating records the tool can be used to update/Correct or end-date employee records. The tool is a typical data interface which has GUI form to load, validate and monitor progress for loading data. The datafile that is used will be in .dat file format.

The HCM Dataloader uses multi-threading approach to load data. This will be very useful to load bulk data with minimizing any potential performance issues.

Steps to process are as follows:

Create and place the .zip file which contains .dat files.
Submit a request to HCM Data Loader to import and load the .zip file.
HCM Data Loader decompresses the .zip file and imports individual data lines to its stage tables, validates the data
Any errors from the import and load phases is available for review
Errors can be corrected from the import and load phases.

You repeat this process from step 2 until all of the data is successfully loaded. Any errors that occur during the import phase are reported on the HCM Data Loader interface.

NOTE:

1. To load data using HCM Dataloder use Human Capital Management Integration Specialist job role or privileges to access HCM Data Loader is required for loading data using HCM Data Loader.

Select Navigator -> Setup and Maintenance to open the Setup and Maintenance work area.

In the Setup and Maintenance work area, select the Configure HCM Data Loader in the HCM Data Load functional area.

Create Implementation Users and Assign Job Roles in Oracle HCM Cloud

2018-06-06T18:32:00.000-07:00

• Login to an existing user with ADMIN rights
• Select Navigator -> Setup and Maintenance to open the Setup and Maintenance work area.

In the Setup and Maintenance work area, select the Create Implementation Users task in the Initial Users functional area.
• Click on ‘Initial Users’
• Choose ‘All Tasks’ from Show
• Under Task Click ‘Create Implementation Users’

Click Add User Account

Fill in the required fields and save
Follow these steps to assign job roles to the implementation user:
• Click Add Role
• In the Add Role Membership dialog box, search for the desired job role
• In the search results, select the role and click Add Role Membership
• Click OK to close the Confirmation dialog box
• Repeat from step 2 to add other job roles to the user
• Click Save and Close

NOTE: If you are using demo.oracle.come for trying to create an user then use CURTIS.FEITTY user to login and create a new user

pa_budget_pub.create_draft_budget - single record insert for revenue financial plans

2017-10-10T18:15:00.000-07:00

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

2017-10-10T18:11:00.001-07:00

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

2017-10-10T18:06:00.002-07:00

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

2017-10-10T18:03:00.003-07:00

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;

SQL to convert GMT to EST with Daylight Savings

2015-07-28T10:54:00.000-07:00

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

Supplier Remittance Advice

2015-04-29T12:45:00.000-07:00

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.

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

2015-02-25T08:24:00.003-08:00

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

XLS Mime type in Oracle

2014-07-31T08:15:00.000-07:00

Ensure that the ‘XLS’ in the output type of the concurrent program is not already been created by navigating using the following:

System Administrator -> Concurrent -> Program -> Define
Click on the drop down list of concurrent program output and ‘XLS’ format should not appear.

If entry doesn't exist then run the following insert statements:

INSERT INTO fnd_lookup_values
(lookup_type, LANGUAGE, lookup_code, meaning, description,
enabled_flag, start_date_active, end_date_active, created_by,
creation_date, last_updated_by, last_update_login,
last_update_date, source_lang, security_group_id,
view_application_id, territory_code, attribute_category,
attribute1, attribute2, attribute3, attribute4, attribute5,
attribute6, attribute7, attribute8, attribute9, attribute10,
attribute11, attribute12, attribute13, attribute14, attribute15,
tag, leaf_node
)
VALUES ('CP_OUTPUT_FILE_TYPE', 'US', 'XLS', 'XLS', 'Excel Output',
'Y', NULL, NULL, 1,
TO_DATE ('31-MAY-02', 'DD-MON-RR'), 0, 0,
TO_DATE ('29-AUG-05', 'DD-MON-RR'), 'US', 0,
0, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL
);

INSERT INTO fnd_mime_types_tl
(file_format_code, mime_type, LANGUAGE, source_lang, created_by,
creation_date, last_updated_by,
last_update_date, last_update_login, description,
allow_client_encoding
)
VALUES ('XLS', 'application/vnd.ms-excel', 'US', 'US', 0,
TO_DATE ('22-DEC-13', 'DD-MON-RR'), 0,
TO_DATE ('22-DEC-13', 'DD-MON-RR'), 0, 'Microsoft Excel',
'Y'
);

COMMIT ;

Sql to convert Rows to a single column seperated with comma (,)

2014-07-31T08:08:00.000-07:00

Following SQL shows how to convert multiple row output of a column and print all the output in a single column and values separated with a comma (,).

For real time example we took data on GL Journal Lines:

   SELECT RTRIM (XMLAGG (XMLELEMENT (e, segment1 || ',')).EXTRACT
                                                                   ('//text()'),
                       ','
                      ) segment1
           INTO l_company
           FROM (SELECT DISTINCT segment1
                            FROM gl_je_lines gjl,
                                 gl_code_combinations_kfv gcc
                           WHERE 1 = 1
                             AND gjl.code_combination_id =
                                                       gcc.code_combination_id
                             AND gjl.je_header_id = :l_je_header_id);

Word Wrap in Excel based Oracle Reports

2014-07-31T08:00:00.002-07:00

Use the following oracle function to use the word wrap functionality in Oracle excel based reports.

Create or Replace FUNCTION word_wrap (
      p_textin         IN   VARCHAR2 DEFAULT NULL,
      p_rmargin      IN   INTEGER DEFAULT 78,
      p_pad         IN   VARCHAR2 DEFAULT ' ',
      p_worddelim IN   VARCHAR2 DEFAULT ' ',
      p_just             IN   VARCHAR2 DEFAULT 'LEFT'
   )
      RETURN VARCHAR2
/*=======================================================================
OBJECT NAME: Word_wrap
OBJECT TYPE: PUBLIC FUNCTION
DESCRIPTION: PROCEDURE USED TO WRAP THE STRING
PARAMETERS :
==========
NAME              TYPE     DESCRIPTION
----------------- -------- ---------------------------------------------
P_TEXTIN          IN      TEXT TO FORMAT - UP TO 32K
P_RMARGIN      IN      WRAP AT WHAT CCOL POSITION
P_PAD                IN      PAD THE TEXT AFTER WRAPPING WITH WHAT DEFAULT TO SPACE
P_WORDDELIM IN      WORDS DELIMEETED BY WHAT? DEFAULTS TO SPACE
P_JUST               IN      JUSTIFY HOW? LEFT,RIGHT,CENTER

RETURNS: L_TEXTOUT (WRAPPED TEXT)
=====================================================================*/
   IS
      l_textout      VARCHAR2 (32000);
      l_line         VARCHAR2 (32000);
      l_subline      VARCHAR2 (32000);
      l_sublinelen   INTEGER;
      l_nl           CHAR (1)         := CHR (10);
      l_sp           CHAR (1)         := CHR (32);
      l_textlen      INTEGER          := LENGTH (p_textin);
      l_just         CHAR (1)         := UPPER (SUBSTR (p_just, 1, 1));
      l_toffset      INTEGER          := 1;
      l_doffset      INTEGER          := 0;
      l_pad          VARCHAR2 (32000) := LPAD (l_sp, p_rmargin, l_sp);
   BEGIN
      LOOP
         EXIT WHEN l_toffset > l_textlen;
         l_line := SUBSTR (p_textin || l_pad, l_toffset, p_rmargin);
         l_doffset :=
            LEAST (REPLACE (TO_CHAR (INSTR (l_line, p_worddelim, -1), '00000'),
                            '00000',
                            '99999'
                           ),
                   REPLACE (TO_CHAR (INSTR (l_line, l_nl, -1), '00000'),
                            '00000',
                            '99999'
                           )
                  );
         l_subline := LTRIM (RTRIM (SUBSTR (l_line, 1, l_doffset - 1)));
         l_sublinelen := LENGTH (l_subline);
         l_toffset := l_toffset + l_doffset;

/******************************************************************************
CODE FOR JUSTIFICATION. BY DEFAULT THE JUSTIFICATION IS SET TO LEFT
*******************************************************************************/
         IF l_just = 'L'
         THEN
            l_textout :=
                  l_textout
               || l_subline
               || LPAD (p_pad, p_rmargin - l_sublinelen, p_pad)
               || l_nl;
         ELSIF l_just = 'R'
         THEN
            l_textout :=
                  l_textout
               || LPAD (p_pad, p_rmargin - l_sublinelen, p_pad)
               || l_subline
               || l_nl;
         ELSIF l_just = 'C'
         THEN
            l_textout :=
                  l_textout
               || LPAD (p_pad, (p_rmargin - l_sublinelen) / 2, p_pad)
               || l_subline
               || l_nl;
         END IF;
      END LOOP;

      RETURN l_textout;
   END word_wrap;

------------------------------------------------------------------------

Example:
select word_wrap ('Vendor Name', 10) from dual

Read Only DFF Segment in Oracle Forms

2014-07-31T07:53:00.001-07:00

Create a new value set with the details as shown in the screenshot.
Make sure the 'Validation Type' is 'Special'
Hit 'Edit Information'

Make sure to enter the following:

Event: Edit
Function: FND SQL "BEGIN NULL; END;"

Event: Validate
Function: FND SQL "BEGIN NULL; END;"

Assign the above value set to the DFF when you create the Descriptive Flexfield Segment.

Query to differenciate customer and suppliers data in hz_parties table in Oracle R12

2014-02-26T07:36:00.002-08:00

select * from (
select hp.party_number,hp.party_name,hp.status,decode(nvl(hpu.party_usage_code,hp.party_type),'ORGANIZATION','CUSTOMER',nvl(hpu.party_usage_code,hp.party_type)) party_type
from HZ_PARTY_USG_ASSIGNMENTS hpu,hz_parties hp
where hp.party_id = hpu.party_id(+))
order by party_type

Workflow Mailer Debugging Script for Debugging Emails issues

2013-07-18T07:13:00.000-07:00

http://apps2fusion.com/at/44-bpel-soa/328-workflow-mailer-debugging-script-for-debugging-emails-issues

SQL Query to find Parameters and Value Sets associated with a Concurrent Program

2013-06-17T18:09:00.002-07:00

SELECT
fcpl.user_concurrent_program_name "Concurrent Program Name",
fcp.concurrent_program_name "Short Name",
fdfcuv.column_seq_num "Column Seq Number",
fdfcuv.end_user_column_name "Parameter Name",
fdfcuv.form_left_prompt "Prompt",
fdfcuv.enabled_flag " Enabled Flag",
fdfcuv.required_flag "Required Flag",
fdfcuv.display_flag "Display Flag",
fdfcuv.flex_value_set_id "Value Set Id",
ffvs.flex_value_set_name "Value Set Name",
flv.meaning "Default Type",
fdfcuv.DEFAULT_VALUE "Default Value"
FROM
fnd_concurrent_programs fcp,
fnd_concurrent_programs_tl fcpl,
fnd_descr_flex_col_usage_vl fdfcuv,
fnd_flex_value_sets ffvs,
fnd_lookup_values flv
WHERE
fcp.concurrent_program_id = fcpl.concurrent_program_id
AND fcpl.user_concurrent_program_name like 'XX%'
AND fdfcuv.descriptive_flexfield_name = '$SRS$.'
|| fcp.concurrent_program_name
AND ffvs.flex_value_set_id = fdfcuv.flex_value_set_id
AND flv.lookup_type(+) = 'FLEX_DEFAULT_TYPE'
AND flv.lookup_code(+) = fdfcuv.default_type
AND fcpl.LANGUAGE = USERENV ('LANG')
AND flv.LANGUAGE(+) = USERENV ('LANG')
ORDER BY fdfcuv.column_seq_num;

R12 Upgrade: 'Function not available to this responsibility' when accessing Custom Form

2012-09-18T11:12:00.001-07:00

1. Navigate to $ORA_CONFIG_HOME/10.1.2/forms/server/default.env
2. Create an entry for $CUSTOM_TOP directory.
3. Save the file and restart the middle tier services

Query to find Installed Applications in Oracle

2012-09-17T07:16:00.002-07:00

SELECT   fatl.application_name,
         SUBSTR (fa.application_short_name, 1, 8) product,
         SUBSTR (fpi.product_version, 1, 14) VERSION,
         SUBSTR (fpi.patch_level, 1, 11) patch_level, fpi.status,
         flv.meaning status_meaning
    FROM fnd_application fa,
         fnd_product_installations fpi,
         fnd_application_tl fatl,
         fnd_lookup_values flv
   WHERE fa.application_id = fpi.application_id
     AND fatl.application_id = fa.application_id
     AND UPPER (flv.lookup_type) = 'APP_INSTALL_STATUS'
     AND fpi.status = flv.lookup_code
     AND flv.LANGUAGE = 'US'
     AND flv.lookup_type = 'APP_INSTALL_STATUS'
     AND fatl.LANGUAGE = 'US'
ORDER BY fa.application_short_name

Script that queries the Menu structure

2012-09-12T14:12:00.000-07:00

SELECT LPAD (' ', 6 * (LEVEL - 1)) || menu_entry.entry_sequence SEQUENCE,
LPAD (' ', 6 * (LEVEL - 1)) || menu_entry.prompt prompt,
menu_entry.grant_flag grant_flag,
DECODE (menu_entry.sub_menu_id,
NULL, 'FUNCTION',
DECODE (menu_entry.function_id, NULL, 'SUBMENU', 'BOTH')
) TYPE,
menu2.user_menu_name, func2.user_function_name
FROM fnd_menu_entries_vl menu_entry,
fnd_menus_tl menu,
fnd_form_functions_tl func,
fnd_form_functions_tl func2,
fnd_menus_tl menu2
WHERE menu_entry.sub_menu_id = menu.menu_id(+)
AND menu_entry.function_id = func.function_id(+)
AND menu_entry.sub_menu_id = menu2.menu_id(+)
AND menu_entry.function_id = func2.function_id(+)
AND grant_flag = 'Y'
START WITH menu_entry.menu_id =
(SELECT menu_id
FROM fnd_menus_tl menu2
WHERE menu2.user_menu_name = :MENU_NAME)
CONNECT BY menu_entry.menu_id = PRIOR menu_entry.sub_menu_id
ORDER SIBLINGS BY menu_entry.entry_sequence

Query to find profile Option values using SQL

2011-12-02T07:52:00.001-08:00

SELECT po.profile_option_name "NAME", po.user_profile_option_name,
DECODE (TO_CHAR (pov.level_id),
'10001', 'SITE',
'10002', 'APP',
'10003', 'RESP',
'10005', 'SERVER',
'10006', 'ORG',
'10004', 'USER',
'???'
) "LEV",
DECODE (TO_CHAR (pov.level_id),
'10001', '',
'10002', app.application_short_name,
'10003', rsp.responsibility_key,
'10005', svr.node_name,
'10006', org.NAME,
'10004', usr.user_name,
'???'
) "CONTEXT",
pov.profile_option_value "VALUE"
FROM fnd_profile_options_vl po,
fnd_profile_option_values pov,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
WHERE 1 = 1
AND pov.application_id = po.application_id
AND pov.profile_option_id = po.profile_option_id
AND usr.user_id(+) = pov.level_value
AND rsp.application_id(+) = pov.level_value_application_id
AND rsp.responsibility_id(+) = pov.level_value
AND app.application_id(+) = pov.level_value
AND svr.node_id(+) = pov.level_value
AND org.organization_id(+) = pov.level_value
ORDER BY "NAME", pov.level_id, "VALUE";

Query to retrieve Discoverer business areas, users and privileges

2011-06-18T16:47:00.000-07:00

SELECT eu_username
FROM eul5_eul_users a, eul5_access_privs b, eul5_bas c
WHERE a.eu_id = b.ap_eu_id
AND b.gba_ba_id = c.ba_id
AND c.ba_name = --- BA_NAME

Thanks to Juntaletras for sharing the script.