Data Conversion Process into Oracle EBS

The following steps are involved in the conversion process as mentioned below in the diagram

·         Data cleansing process will be completed in three phases.

·         Phase 1: Manual cleansing in legacy system

·         Phase 2 : Data correction, functional mapping and transformation during extraction

·         Phase 3:Reviewing the extracted data and manual correction

·         Data Extract sign off

·         Data load in Oracle

·         Functional unit test and data validation in Oracle applications

·         Data owner sign off

A variety of factors need to be taken into account while making a selection of tool/approach for data conversion.  The factors to be considered are:

·         Volume of data

·         Existence of Oracle open interface table for data

·         Existence of Oracle APIs that support the data to be converted

·         Complexity of the data fields and data relationships within Oracle

Conversion programs should be designed to use to cross reference through AOL features (e.g. Lookups) wherever necessary for transformation of data from legacy values to Oracle values.  Any change in the mapping should not impact the conversion code. 

Oracle OAB Enrollments Conversion Steps

Oracle Standard and Advanced Benefits Conversion Steps:-

 

Pre - Conversion Steps

 

The following pre and post conversion steps apply to all methods discussed in the introduction. You may add more steps to customize to you implementation. These steps will help you prepare for your conversion processing. Some of these items were included in the implementation strategy and planning but are restated here with additional steps to give you a comprehensive picture of the conversion process. Case studies will follow in each section that describe different variations on the conversion process

1.     Choose implementation strategy i.e.) new elections or conversion

2.     Analyze Enrollment Information and Map Source to Target. Determine current benefits. Analyze content and format of source enrollment data. - Identify the meaning and format of existing person enrollment data to be converted and map it to HR OAB data.  Information to consider includes person elections, covered dependents, and beneficiaries.

3.     Analyze Employees and contacts – For the HR conversion of people – if one is needed determine for each employee or former employee to be converted, at what stage the person is in the employment process.  Determine which person contacts need to be added.  Determine if any contacts became employees.  This will determine which HR APIs will be used and the order in which they will be used to create the people being converted in the appropriate employment or contact status and appropriate person types.  Examples:

 

Person Situation	Employment Status
Current Employees (New Hire)	Active Employee
Rehire	Active Employee
Paid LOA	Employee on Paid Leave of Absence
Paid LOA	Employee on Short Term Disability
Unpaid LOA	Employee on Unpaid Leave of Absence
Unpaid LOA	Employee on Long Term Disability
Termination	Terminated Employee
Termination	Terminated with Severance
Termination	Terminated Layoff
Termination	Terminated due to Gross Misconduct
Death	Deceased Employee
Return from LOA	Active Employee
Termination	Terminated Retiree
Employee Spouse	Contact
Employee Child	Contact
Surviving Spouse	Surviving Spouse
Disabled Surviving Spouse	Surviving Spouse
Surviving Child	Surviving Dependent
Former Spouse	Former Spouse
Child of a Divorced Parent	Former Dependent

Note:  If converting contacts, i.e. populating the PER_CONTACT_RELATIONSHIPS table, be certain to populate the following columns: Personal_flag on table – Must always populate this column if intend to designate this contact as a dependent or beneficiary.

Rltd_per_rsds_w_dsgntr_flag or provide an address for the contact.

4.     Gather counts from current enrollments to get total enrollments in programs, plans, plan types, and options. Also record benefit amounts and any other pertinent enrollment data. Prepare a spreadsheet for reference during and after conversion.

5.     Plan processing times and batches related to your total enrolled population. If you run other applications you may need to schedule your batches with the DBA. Running sample or test conversions will help you estimate time to process.

6.     Analyze People with Life Events in Progress - Conversion data is a snapshot of data as of a specific date, whereas people and their benefits may be in transition.  It is necessary to identify which people have a Life Event in progress, e.g. people such as New Hires, Newly Married, and Transfers, who are allowed to make changes to their benefits as of the Conversion Date.  These people may need special treatment or processing i.e.)OSB customers will process these people manually by either having the person make elections before or after the conversion date

7.     Determine the date when the conversion takes place. From this date forward you will use OAB or OSB to administer benefits.

8.     If OAB you must disable the benefits life event triggers before conversion process begins.  A “Disable Life Event Data Triggers” step is described below.  Note that even if no data is attached to the life events that are defined, turning off the triggers will save time in populating the data and later when running the participation processes.  Set the pay_action_parameters to include data_migrator_mode = P to turn off the life event triggers during the conversion process. Navigation:  Processes and Reports > Maintain Process Parameters. The value is then used to check whether the potential life events need to be created or not.

9.      Setup an Administrative life event if OAB on the Program Enrollment Requirements > Timing > Scheduled tab or unrestricted event if OSB. The assigned life event date must be set to the date you want the conversion to take place. Close enrollment date should be When Enrollment Period Ends and the plan year must coincide with your assigned life event date.

10.  Verify that NO default enrollment requirements are defined for the Administrative life or life event being used. This means you will need to make sure no default enrollment requirements are specified. In this conversion you are explicitly choosing what the participant will be enrolled in from the electable choices created by the life event.

11.  Make sure all Person, Assignment, and Contact data is accurately stored in the appropriate HR tables. The participation process can only evaluate date stored in PER tables within Oracle HRMS.

12.  Run the conversion manually on a test sample to ensure all dates and amounts are correct. If all is correct you can proceed to the steps in the appropriate sections.

 

 Post - Conversion Steps

1.     Compare your counts from original enrollments to track down any issues

2.     Close the administrative Life event

3.     Manage Life Events in Progress - Post Conversion. Create Potential Life Events OAB customers who decide to delay processing these peoples’ life events will need to manually create the potential life event for these people; because the change detected that caused the life event happened before conversion.  For example, Tomy moved from Boston to San Francisco on 15-June-2000 and as a result is experiencing a "Relocation" life event which allows him to change medical plans any time between 15-June-2011 and 15-July-2011.  As of the conversion date, 1-July-2011, Tomy is already living in San Francisco.  The customer needs to create a "potential" life event of Relocation for Tomy, so that when processing is done in OAB, Tomy’s Relocation will started and Tomy will be allowed to make new elections as applicable and as determined according to the plan design setup.

a.     To manually create potential life events for persons who had a life event in progress as of the conversion date, use the Person Life Event professional interface form, Potential Life Events tab.  Select the Name of the applicable Life Event.  Enter the Notified Date and Occurred on Date.  Enter the Status as Unprocessed and enter the Unprocessed Date.  Each of these dates is the same as the date the Life Event Occurred.  The customer may want to set the Notified Date to one day after the conversion date and the Enrollment Period Start Date codes such that the person will have more time to make choices.  These potential events will then be evaluated in the subsequent run of the Participation Process.

4.     Turn Life event triggers back on

How to Apply Custom PL/SQL Based Security with Oracle Report Manager

Following example will give an insight to setup Custom PL/SQL based security with Oracle Report Manager to distribute reports based on "user to value" security function based on the user location(derived from hr locations based on person assignment). A PL/SQL package is required as a custom security to restrict the reporting information only to the user's(employee) location based on location segment in Accounting Combination.

Following is the package to derive individual person location, the parameters are :

 1. Location Code of individual
 2. Oracle Application user_id of individual


CREATE OR REPLACE PACKAGE BODY xx_custom_security AS
--Create  mandatory procedure for Report Manager Custom Security to individual person location

   PROCEDURE accessallowed (
      p_securedvalue    IN       VARCHAR2
     ,p_accessallowed   OUT      NUMBER
   ) IS

-- Define Local Variables
      l_query    VARCHAR2 (3000);
      l_dummy    NUMBER;
      l_cursor   NUMBER;
      l_count    NUMBER          DEFAULT 0;
      l_loc      VARCHAR2 (10)   DEFAULT '0';
      l_result   NUMBER;
   BEGIN
--Initialize variables
      l_loc := '0';
      l_query := '';
      l_cursor := '';
 
-- Dynamic select statement  to derive LOCATION location
      l_query :=
       'SELECT COUNT(hl.location_code)
      FROM per_assignments_x paaf
         , per_people_x papf
         , hr_all_organization_units hou
         , fnd_user fu
         , hr_locations hl
         , fnd_id_flex_segments ffs
         , fnd_flex_values_vl ffv
         , per_jobs pj
     WHERE paaf.person_id = papf.person_id
       AND paaf.organization_id = hou.organization_id
       AND paaf.assignment_type = ''E''
       AND paaf.primary_flag = ''Y''
       AND hou.TYPE = ''LOCATION''
       AND papf.person_id = fu.employee_id
       AND paaf.location_id = hl.location_id
       AND ffs.id_flex_code = ''GL#''
       AND hl.attribute2 = ''LOCATION''
       AND pj.job_id = paaf.job_id
       AND hl.location_code= ffv.flex_value
       AND ffs.application_column_name = ''SEGMENT2''
       AND ffv.enabled_flag = ''Y''
       AND ffs.enabled_flag = ''Y''
       AND ffv.summary_flag = ''N''
       AND ffv.flex_value_set_id = ffs.flex_value_set_id
       AND ffs.id_flex_num =101 ';
      l_query :=    l_query
                 || ' AND hl.location_code = :1 AND  fu.user_id = :2';
      l_cursor := DBMS_SQL.open_cursor;
-- Parse the query
      DBMS_SQL.parse (l_cursor,l_query,DBMS_SQL.native);
-- Pass 'Location' Parameter to the select stmt
      DBMS_SQL.bind_variable (l_cursor,':1',SUBSTR (p_securedvalue, -7));
-- Pass ‘Oracle FND User’ Parameter to the select stmt
      DBMS_SQL.bind_variable (l_cursor,':2',fnd_global.user_id);
      DBMS_SQL.define_column (l_cursor,1,l_count);
     
      l_dummy := DBMS_SQL.EXECUTE (l_cursor);
      l_dummy := DBMS_SQL.fetch_rows (l_cursor);
     
      DBMS_SQL.COLUMN_VALUE (l_cursor,1,l_count);
      DBMS_SQL.close_cursor (l_cursor);

--  If there is a User has Location then Count>1 then the result = 1 else --  result = 0
      IF (l_count = 0) THEN
         l_result := 0;
      ELSE
        l_result := 1;
      END IF;

      DBMS_OUTPUT.put_line ('Result '|| l_result);
      p_accessallowed := l_result;
   EXCEPTION
      WHEN OTHERS THEN
         l_result := 0;
   END accessallowed;
END xx_custom_security;
------------------------------------------------------------------------------------------------------------------
Once the above package is created, the package name i.e., XX_CUSTOM_SECURITY should be passed as a parameter value to "Custom Security" parameter in REPORT MANAGER/Financial Report Submission – Security (Section) as follows:

 

Relation between AR tables and Projects, Project invoices

Following is the project invoices query with customer_name,project_number,
bill-to address,project revenue, ar invoice ,invoice_amount,payment amount etc.:-

SELECT       trx.trx_number, reve.gl_date,
                  hl.address1 || hl.address2 address,
                  hl.city, hl.state, hl.postal_code, hca.account_name,
                  paysch.amount_due_remaining, reve.draft_revenue_num,
                  proj.project_id project_id, draftinv.draft_invoice_num,
                  proj.segment1 project_number, draftinv.system_reference
FROM    ar_payment_schedules_all paysch,
            ra_customer_trx_all trx,
            pa_draft_invoices draftinv,
            pa_draft_invoice_items dinvitms,
            pa_cust_rev_dist_lines revedist,
            pa_draft_revenues reve,
            pa_projects proj,
            hz_cust_accounts hca,
            hz_cust_acct_sites_all hcas,
            hz_cust_site_uses_all hcsu,
            hz_party_sites hps,
            hz_locations hl

WHERE paysch.customer_trx_id = trx.customer_trx_id
     AND paysch.CLASS = 'INV'
     AND paysch.status = 'OP'
     AND trx.bill_to_customer_id = hca.cust_account_id
     AND trx.bill_to_site_use_id = hcsu.site_use_id
     AND hca.cust_account_id = hcas.cust_account_id
     AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id
     AND hps.party_site_id = hcas.party_site_id
     AND trx.interface_header_context = 'PROJECTS INVOICES'
     AND draftinv.system_reference = trx.customer_trx_id
     AND draftinv.draft_invoice_num = dinvitms.draft_invoice_num
     AND dinvitms.draft_invoice_num = revedist.draft_invoice_num
     AND dinvitms.line_num = revedist.draft_invoice_item_line_num
     AND revedist.draft_revenue_num = reve.draft_revenue_num
     AND hl.location_id = hps.location_id
     AND reve.transfer_status_code = 'A'
     AND draftinv.draft_invoice_num_credited IS NULL
     AND proj.project_id = reve.project_id
     AND proj.project_id = revedist.project_id
     AND proj.project_id = dinvitms.project_id
     AND proj.project_id = draftinv.project_id
     AND draftinv.project_id = dinvitms.project_id
     AND reve.project_id = draftinv.project_id
GROUP BY      trx.trx_number,
                     hca.account_name,
                     hl.address1 || hl.address2,
                     hl.city, hl.state,
                     hl.postal_code,
                     reve.gl_date,
                     paysch.amount_due_remaining,
                     reve.draft_revenue_num,
                     draftinv.draft_invoice_num,
                     proj.segment1,
                     draftinv.system_reference,
                     proj.project_id;

Order Management Processing Constraint steps

The following are the Setups done for processing constraints using API to validate Records in Sales Order Form:-

1)     In Setup of Order Management Responsibility, 

        /setup/rules/security/Validation  Templates, in Order Management Application,
        define a validation template under Entity: Order Line, with
        Template name : 
        Regulatory Hold Validation
        Validation Type: API.
       
        PL/SQL Package Name: <PL/SQL package name> and 

        PL/SQL Procedure : <PL/SQL procedure name>
       
2)    In Setup Order Management Responsibility, /setup/rules/security/Processing 

       Constraints, assign the above validation template
       Regulatory Hold Validation to Processing Constraints
       
       Under Application: Order Management, Entity: Order Line, Record Set: Line

Select statement to detail PO and PO approver details

Following is the query which details all the purchase orders approved by particular approver for a given date range:-

Parameters: P_START_DATE  -- PO Creation Date

                   P_END_DATE      -- PO Creation Date

                   P_EMP_NUM -- Approver Employee Number

SELECT   ph.segment1 po_number, poah1.object_sub_type_code po_type,
         pv.segment1 supplier_number, pv.vendor_name supplier_name,
         pl.po_amount, ph.currency_code, poah1.action_date approved_date,
         poah1.employee_name approver_name,
         ppf.employee_number appr_emp_number,
         (SELECT ppf1.full_name
            FROM po_action_history poa1,
                 per_all_people_f ppf1
           WHERE TRUNC (SYSDATE) BETWEEN TRUNC (ppf1.effective_start_date)
                                     AND TRUNC (ppf1.effective_end_date)
             AND ppf1.person_id = poa1.employee_id
             AND poa1.action_code = 'SUBMIT'
             AND poa1.object_type_code = 'PO'
             AND poa1.object_id = poah1.object_id
             AND poa1.object_type_code = poah1.object_type_code
             AND poa1.sequence_num =
                    (SELECT MIN (sequence_num)
                       FROM po_action_history
                      WHERE object_type_code = poa1.object_type_code
                        AND object_id = poa1.object_id)) po_submitted_by,
         poah1.sequence_num
    FROM po_headers_all ph,
         (SELECT poah.ROWID row_id, poah.created_by, poah.action_code,
                 poah.action_date, poah.employee_id, poah.approval_path_id,
                 poah.note, poah.object_revision_num, poah.offline_code,
                 poah.last_update_login, poah.request_id,
                 poah.program_application_id, poah.program_id,
                 poah.program_update_date, poah.program_date, poah.object_id,
                 poah.object_type_code, poah.object_sub_type_code,
                 poah.sequence_num, poah.last_update_date,
                 poah.last_updated_by, poah.creation_date,
                 (SELECT DISTINCT full_name
                             FROM per_all_people_f
                            WHERE TRUNC (SYSDATE)
                                     BETWEEN TRUNC
                                               (effective_start_date
                                               )
                                         AND TRUNC (effective_end_date)
                              AND person_id = poah.employee_id) employee_name,
                 NVL (polc1.displayed_field, polc2.displayed_field),
                 polc1.security_group_id
            FROM po_action_history poah,
                 (SELECT lookup_code, lookup_type, last_update_date,
                         last_updated_by, last_update_login, creation_date,
                         created_by, meaning displayed_field, description,
                         end_date_active, enabled_flag,
                         TO_NUMBER (NULL) request_id,
                         TO_NUMBER (NULL) program_application_id,
                         TO_NUMBER (NULL) program_id,
                         TO_DATE (NULL) program_update_date,
                         attribute_category, attribute1, attribute2,
                         attribute3, attribute4, attribute5, attribute6,
                         attribute7, attribute8, attribute9, attribute10,
                         attribute11, attribute12, attribute13, attribute14,
                         attribute15, security_group_id
                    FROM fnd_lookup_values lv
                   WHERE LANGUAGE = USERENV ('LANG')
                     AND view_application_id = 201
                     AND security_group_id =
                            0 /*fnd_global.lookup_security_group (lv.lookup_type,
                                             lv.view_application_id
                                            )*/) polc1,
                 (SELECT lookup_code, lookup_type, last_update_date,
                         last_updated_by, last_update_login, creation_date,
                         created_by, meaning displayed_field, description,
                         end_date_active, enabled_flag,
                         TO_NUMBER (NULL) request_id,
                         TO_NUMBER (NULL) program_application_id,
                         TO_NUMBER (NULL) program_id,
                         TO_DATE (NULL) program_update_date,
                         attribute_category, attribute1, attribute2,
                        attribute3, attribute4, attribute5, attribute6,
                         attribute7, attribute8, attribute9, attribute10,
                         attribute11, attribute12, attribute13, attribute14,
                         attribute15, security_group_id
                    FROM fnd_lookup_values lv
                   WHERE LANGUAGE = USERENV ('LANG')
                     AND view_application_id = 201
                     AND security_group_id =
                            0 /*  fnd_global.lookup_security_group (lv.lookup_type,
                                           lv.view_application_id
                                          )*/) polc2
           WHERE polc1.lookup_code(+) = poah.action_code
            AND polc1.lookup_type(+) = 'APPROVER ACTIONS'
             AND polc2.lookup_code(+) = poah.action_code
             AND polc2.lookup_type(+) = 'CONTROL ACTIONS'
             AND poah.object_type_code = 'PO') poah1,
         per_all_people_f ppf,
         (SELECT   SUM (unit_price * quantity) po_amount, po_header_id
              FROM po_lines_all
          GROUP BY po_header_id) pl,
         po_vendors pv
   WHERE poah1.object_id = ph.po_header_id
     AND poah1.object_type_code = 'PO'
     AND poah1.action_code = 'APPROVE'
     AND pv.vendor_id = ph.vendor_id
     AND pl.po_header_id = ph.po_header_id
     AND NVL (ph.cancel_flag, 'N') = 'N'
     AND ppf.employee_number = :P_EMP_NUM
     AND TRUNC (SYSDATE) BETWEEN TRUNC (ppf.effective_start_date)
                             AND TRUNC (ppf.effective_end_date)
     AND ppf.person_id = poah1.employee_id
     AND TRUNC (ph.creation_date) BETWEEN TRUNC (TO_DATE (:P_START_DATE))
                                      AND TRUNC (TO_DATE (:P_END_DATE))
     AND poah1.sequence_num =
            (SELECT MAX (sequence_num)
               FROM po_action_history
              WHERE object_type_code = poah1.object_type_code
                AND object_id = poah1.object_id)
ORDER BY ph.segment1, pl.po_amount DESC;