Oracle HRMS- Person, Assignment , Job , Location Conversion Using Api

Workday Integration Process

Description

This blog is used to Creating new Person, Assignment, Job creation, Location creation, and Rehiring Person. This blog includes the updating of Person, Job, and assignment with email notification option the corresponding API will create and update the information Person and Job.

-- This query retrieves the phone_id to create and update phone

SELECT phone_id, object_version_number

  FROM per_phones

 WHERE phone_type = i_ph_type

   AND parent_table = 'PER_ALL_PEOPLE_F'

   AND parent_id = i_person_id

   AND (date_to IS NULL

        OR

        i_eff_date BETWEEN date_from AND NVL (TRUNC (date_to), 

        hr_general.end_of_time)

       );

            

-- This API is used to create the phone details based on the parameters.

      hr_phone_api.create_phone

        (

             p_validate              => FALSE

            ,p_date_from             => i_hire_date

            ,p_phone_type            => l_phone_type

            ,p_phone_number          => i_work_phone

            ,p_parent_id             => i_person_id

            ,p_parent_table          => 'PER_ALL_PEOPLE_F'

            ,p_effective_date        => i_hire_date

            ,p_object_version_number => l_ovn

            ,p_phone_id              => l_phone_id

        );

-- This API is used to update the phone details based on the parameters.

        hr_phone_api.update_phone

        (

            p_phone_id              => l_phone_id -- Phone id from per_phones

            ,p_phone_type           => l_phone_type -- Phone type 'W1'

            ,p_phone_number         => i_work_phone -- Input parameter

            ,p_object_version_number=> l_ovn

            ,p_validate             => FALSE

            ,p_effective_date       => i_hire_date

        );

                    

       

-- This API is used to create employee based on the parameters.

   

   

Hr_Employee_Api.CREATE_EMPLOYEE

(

    p_business_group_id           => g_business_group_id,

    p_person_id                   => v_person_id,

    p_assignment_id               => v_assignment_id,

    p_per_object_version_number   => v_per_object_version_number,

    p_asg_object_version_number   => v_asg_object_version_number,

    p_per_effective_start_date    => v_per_effective_start_date,

--Out Parameter

    p_per_effective_end_date      => v_per_effective_end_date,

    p_full_name                   => v_full_name,

    p_person_type_id              => g_person_type_id,

    p_per_comment_id              => v_per_comment_id,

    p_assignment_sequence         => v_assignment_sequence,

    p_assignment_number           => v_assignment_number,

    p_name_combination_warning    => v_name_combination_warning,

    p_assign_payroll_warning      => v_assign_payroll_warning,

    p_orig_hire_warning           => v_orig_hire_warning,--HEADER DETAILS

    p_last_name                   => l_get_all_per_inserts.per_last_name,

    p_first_name                  => l_get_all_per_inserts.per_first_name,

    p_sex                         => l_get_all_per_inserts.per_gender,

    p_middle_names                => l_get_all_per_inserts.per_middle_names,

    p_employee_number             =>

    l_get_all_per_inserts.per_employee_number,

      --OFFICE DETAILS TAB

    p_email_address               => l_get_all_per_inserts.per_email_address,

    p_hire_date                   =>  

   l_get_all_per_inserts.per_startdate_hiredate

)      

-- This API is used to update person based on the parameters.

Hr_Person_Api.UPDATE_PERSON

(

    p_validate                   => FALSE,

    p_effective_date             =>

l_get_all_per_update.per_effective_start_date,

    p_datetrack_update_mode      => v_datetrack_update_mode,

    p_person_id                  => l_get_all_per_update.person_id,

    p_object_version_number      => l_obj_ver_num,

    p_last_name                  => l_get_all_per_update.per_last_name,

    p_first_name                 => l_get_all_per_update.per_first_name,

    p_sex                        => l_get_all_per_update.per_gender,

    p_middle_names               => l_get_all_per_update.per_middle_names,

    p_employee_number            => l_get_all_per_update.per_employee_number,

    p_email_address              => l_get_all_per_update.per_email_address,

    p_effective_start_date       => v_o_effective_start_date,

    p_effective_end_date         => v_o_effective_end_date,

    p_full_name                  => v_o_full_name,

    p_comment_id                 => v_o_p_comment_id,

    p_name_combination_warning   => v_o_name_combination_warning,

    p_assign_payroll_warning     => v_o_assign_payroll_warning,

    p_orig_hire_warning          => v_o_orig_hire_warning

);

-- This query is used to retrieve the period service id and final process

   Date for person final process.

SELECT period_of_service_id, object_version_number,

       last_standard_process_date, final_process_date

  INTO l_period_of_service_id, l_object_version_number,

       l_last_standard_process_date, l_final_process_date

  FROM per_periods_of_service pps

 WHERE person_id = l_get_all_per_rehire.person_id

   AND pps.date_start = (

                            SELECT MAX (date_start)

                            FROM per_periods_of_service pps2

                            WHERE pps2.person_id = pps.person_id

                        );     

                           

-- This API is used to perform the final process of employee.

                                

Hr_Ex_Employee_Api.final_process_emp

(

    p_validate                     => g_validate,

    p_period_of_service_id         => l_period_of_service_id,

    p_object_version_number        => l_object_version_number,

    p_final_process_date           => l_final_process_date,

    p_org_now_no_manager_warning   => l_supervisor_warning,

    p_asg_future_changes_warning   => l_asg_future_changes_warning,

    p_entries_changed_warning      => l_entries_changed_warning

);                                          

                                           

 

-- This API is used to re-hire the employee’s.

                                 

   

Hr_Employee_Api.re_hire_ex_employee

(

    p_validate                    => FALSE,

    p_hire_date                   =>

   l_get_all_per_rehire.per_startdate_hiredate,

    p_person_id                   => l_get_all_per_rehire.person_id,

    p_per_object_version_number   =>

    l_get_all_per_rehire.object_version_number,

    p_person_type_id              => l_person_type_id,

    p_rehire_reason               => 'REHIRED',

    p_assignment_id               => v_out_assignment_id,

    p_asg_object_version_number   => v_asg_object_version_number,

    p_per_effective_start_date    => v_per_effective_start_date,

    p_per_effective_end_date      => v_per_effective_end_date,

    p_assignment_sequence         => v_asg_sequence,

    p_assignment_number           => v_asg_number,

    p_assign_payroll_warning      => v_asg_payroll_warning

);

   

-- This API is used to process the employee termination.

Hr_Ex_Employee_Api.actual_termination_emp

(

  p_validate                     => l_validate,

  p_effective_date               => NVL (l_actual_termination_date, l_date),

  p_period_of_service_id         => l_period_of_service_id,

  p_object_version_number        => l_object_version_number,

  p_actual_termination_date      => NVL( l_actual_termination_date, l_date ),

  p_last_standard_process_date   => NVL ( l_last_std_process_date, l_date ),

  p_leaving_reason               => NULL,

  p_last_std_process_date_out    => l_last_std_process_date_out,

  p_supervisor_warning           => l_supervisor_warning,

  p_event_warning                => l_event_warning,

  p_interview_warning            => l_interview_warning,

  p_review_warning               => l_review_warning,

  p_recruiter_warning            => l_recruiter_warning,

  p_asg_future_changes_warning   => l_asg_future_changes_warning,

  p_entries_changed_warning      => l_entries_changed_warning,

  p_pay_proposal_warning         => l_pay_proposal_warning,

  p_dod_warning                  => l_dod_warning

);

-- This API is used to update the employee assignment.

Hr_Assignment_Api.update_emp_asg

(

   p_validate                     => FALSE

  ,p_effective_date               => l_get_all_asgn.asg_effective_start_date

  ,p_datetrack_update_mode        => v_datetrack_update_mode

  ,p_assignment_id                => v_in_assignment_id

  ,p_object_version_number        => l_object_version_number

  ,p_supervisor_id                => v_supervisor_id

  ,p_ass_attribute_category       => Hr_Api.g_varchar2

  ,p_default_code_comb_id         => l_expense_account

  ,p_set_of_books_id              => l_set_of_books_id -- Out arguments

  ,p_soft_coding_keyflex_id       => l_soft_coding_keyflex_id     --out

  ,p_effective_start_date         => l_effective_start_date       --out

  ,p_effective_end_date           => l_effective_end_date         --out

  ,p_no_managers_warning          => l_no_mgr_warn                --out

  ,p_other_manager_warning        => l_other_mgr_warng            --out

  ,p_hourly_salaried_warning      => l_salaried_warn              --out

  ,p_gsp_post_process_warning     => l_gsp_post_proc_war          --out

  ,p_cagr_grade_def_id            => l_cagr_grade_def_id          --in out

  ,p_cagr_concatenated_segments   => l_cagr_concatenated_segments --out

  ,p_comment_id                   => l_comment_id               -- out number

  ,p_concatenated_segments        => l_concatenated_segments      --out

);   

-- This API is used to update the employee assignment criteria.

 

Hr_Assignment_Api.update_emp_asg_criteria

(

    p_effective_date                 => l_get_all_asgn.asg_effective_start_date,

    p_datetrack_update_mode          => 'CORRECTION',

    p_assignment_id                  => v_in_assignment_id,

    p_validate                       => FALSE,

    p_called_from_mass_update        => FALSE,

    p_job_id                         => l_job_id,

    p_location_id                    => l_location_id,-- in out

    p_object_version_number          => l_object_version_number,

    p_special_ceiling_step_id        => l1_special_ceiling,

    p_people_group_id                => l_people_group_id,

    p_soft_coding_keyflex_id         => l1_soft_coding_keyflex_id,-- out

    p_group_name                     => l1_group_name,

    p_effective_start_date           => l1_effective_start_date,

    p_effective_end_date             => l1_effective_end_date,

    p_org_now_no_manager_warning     => l1_org_no_mgr_warning,

    p_other_manager_warning          => l1_other_mgr_warning,

    p_spp_delete_warning             => l1_spp_delete_warning,

    p_entries_changed_warning        => l1_entries_changed_warning,

    p_tax_district_changed_warning   => l1_tax_dist_chng_warn,

    p_concatenated_segments          => l1_concatenated_segments,

    p_gsp_post_process_warning       => l1_gsp_post_warning

);

   

  

-- This API is used to create job.

Hr_Job_Api.CREATE_JOB

(

    p_business_group_id           => g_business_group_id

    ,p_date_from                  => '01-JAN-1900'

    ,p_job_group_id               => v_job_group_id

    ,p_segment1                   => SUBSTR(l_get_insert_jobs.job_name,1,60)

    -- ,p_concat_segments => l_get_insert_jobs.job_division||'.'||

 l_get_insert_jobs.job_discipline||'.'||l_get_insert_jobs.job_name

 --Hr_Api.g_varchar2

    ,p_job_information_category   => v_job_info_cat --in param

    ,p_attribute16                => l_get_insert_jobs.job_wd_profile_id

    ,p_approval_authority         => l_authority_level

    ,p_job_id                     => v_job_id            --OUT

    ,p_object_version_number      => v_obj_ver_num       --OUT

    ,p_job_definition_id          => v_job_definition_id --OUT

    ,p_name                       => v_name              --OUT

);  

 

 

-- This API is used to update job.

Hr_Job_Api.UPDATE_JOB

(

    p_validate                       => FALSE,

    p_job_id                         => l_get_upd_del_jobs.job_id,

    p_object_version_number          =>

l_get_upd_del_jobs.object_version_number,

    p_date_to                        => l_get_upd_del_jobs.job_date_to,

    p_job_information_category       =>

l_get_upd_del_jobs.job_information_category,

    p_segment1                       =>

SUBSTR (l_get_upd_del_jobs.job_name,1,60),

    p_concat_segments                =>

SUBSTR (l_get_upd_del_jobs.job_name,1, 60 ),

    p_attribute16                    => l_get_upd_del_jobs.job_wd_profile_id,

    p_approval_authority             => l_authority_level,

    p_language_code                  => Hr_Api.userenv_lang,

    p_job_definition_id              => v_job_definition_id,       -- out

    p_name                           => v_name,                    --out

    p_valid_grades_changed_warning   => v_valid_grd_ch_warning,    --out

    p_effective_date                 => l_get_upd_del_jobs.date_from

);

                     

-- By 

-- Eswaramoorthi M

Customer PO Conversion

Customer PO Conversion

Description

            This blog is used to create a Customer PO conversion. This blog will invoke the API OE_BLANKET_PUB.process_blanket and it processes the header and line level information with validations.

-- This query is used to retrieves the customer account related information’s

SELECT   hca.account_number, hcasa.cust_account_id, hcasa.attribute4 custpo

FROM hz_cust_acct_sites_all hcasa, hz_cust_accounts hca

WHERE 1 = 1

AND hca.cust_account_id = hcasa.cust_account_id

AND hcasa.attribute_category = 'ARCUS'

AND hca.account_number = NVL (p_cust_acct_i, hca.account_number)

AND hcasa.attribute4 IS NOT NULL

AND EXISTS

    (

    SELECT DISTINCT purchase_order

    FROM ra_customer_trx_all rct,

    ra_cust_trx_types_all rctta

    WHERE 1 = 1

    AND rct.cust_trx_type_id = rctta.cust_trx_type_id

    AND UPPER (LTRIM (RTRIM (rctta.description))) LIKE '%OSDP%INV%'

    AND rct.complete_flag = 'Y'

    AND rct.purchase_order IS NOT NULL

    AND TRUNC (rct.trx_date) BETWEEN TO_DATE('04-01-2015','MM-DD-YYYY')

    AND TO_DATE('04-30-2015','MM-DD-YYYY')

    AND rct.purchase_order = hcasa.attribute4

    )

AND hcasa.status = 'A'

AND hca.status = 'A'

AND NOT EXISTS

    (

    SELECT cust_po_number

    FROM ont.oe_blanket_headers_all obha

    WHERE cust_po_number = hcasa.attribute4

    AND obha.sold_to_org_id = hcasa.cust_account_id

    )

GROUP BY

    hca.account_number, hcasa.cust_account_id, hcasa.attribute4;

-- This query is used to retrieves the customer and site related

   Information’s

SELECT hps.party_site_number, hp.party_name, hca.account_number,

       hca.status cust_account_status, hcasa.status cust_site_status,

       hcasa.attribute4 custpo, hcsua.site_use_code, hcsua.site_use_id

  FROM hz_parties hp,

       hz_cust_accounts hca,

       hz_locations hl,

       hz_party_sites hps,

       hz_cust_acct_sites_all hcasa,

       hz_cust_site_uses_all hcsua

 WHERE 1 = 1

   AND hp.party_id = hca.party_id

   AND hp.party_id = hps.party_id

   AND hl.location_id = hps.location_id

   AND hca.cust_account_id = hcasa.cust_account_id

   AND hps.party_site_id = hcasa.party_site_id

   AND hcasa.cust_acct_site_id = hcsua.cust_acct_site_id

   AND hcsua.site_use_code = 'SHIP_TO'

   AND hcasa.attribute_category = 'ARCUS'

   AND hcasa.attribute4 IS NOT NULL

   AND hcasa.cust_account_id = c_cust_acct_id_i     -- Parameter 1

   AND hcasa.attribute4 = c_custpo_i                -- Parameter 2

   AND hcasa.status = 'A'

   AND hcsua.status = 'A'

   AND hca.status = 'A';

-- This API is used to process the customer po with validations

oe_msg_pub.initialize;

oe_blanket_pub.process_blanket

(

    p_org_id                  => n_organization_id,

    p_operating_unit          => NULL,

    p_api_version_number      => 1.0,

    x_return_status           => x_return_status,

    x_msg_count               => x_msg_count,

    x_msg_data                => x_msg_data,

    p_header_rec              => l_hdr_rec,

 -- sold_to_org_id, order_type_id, context, start_date_active, cust_po_number

    p_header_val_rec          => l_hdr_val_rec,

 -- oe_blanket_pub.g_miss_header_val_rec

    p_line_tbl                => l_line_tbl,

 -- oe_blanket_pub.g_miss_blanket_line_rec

    p_line_val_tbl            => l_line_val_tbl,

 -- oe_blanket_pub.g_miss_blanket_line_val_rec

    p_control_rec             => l_control_rec,

    x_header_rec              => x_header_rec,

    x_line_tbl                => x_line_tbl

);

-- By

-- Eswaramoorthi M

Query for Approval groups

SELECT pcr.control_rule_id, pcga.control_group_name, pcr.object_code,
       pcr.rule_type_code, pcr.amount_limit,
       (   segment1_low
        || '.'
        || segment2_low
        || '.'
        || segment3_low
        || '.'
        || segment4_low
        || '.'
        || segment5_low
        || '.'
        || segment6_low
        || '.'
        || segment7_low
        || '.'
        || segment8_low
        || '.'
        || segment9_low
       ) low_value,
       (   segment1_high
        || '.'
        || segment2_high
        || '.'
        || segment3_high
        || '.'
        || segment4_high
        || '.'
        || segment5_high
        || '.'
        || segment6_high
        || '.'
        || segment7_high
        || '.'
        || segment8_high
        || '.'
        || segment9_high
       ) high_value
  FROM apps.po_control_rules pcr, apps.po_control_groups_all pcga
 WHERE pcga.control_group_id =  pcr.control_group_id
 AND pcr.control_group_id in (select distinct control_group_id from apps.po_position_controls_all where org_id = :ou_name)

Query for Approval Assignments

SELECT hou.NAME operating_unit, pj.NAME job, pcf.control_function_name,
       pcga.control_group_name,
       TO_CHAR (ppca.start_date, 'DD-MON-YY') start_date,
       TO_CHAR (ppca.end_date, 'DD-MON-YY') end_date
  FROM apps.po_position_controls_all ppca,
       apps.po_control_functions pcf,
       apps.hr_operating_units hou,
       apps.per_jobs_tl pj,
       apps.po_control_groups_all pcga
 WHERE ppca.control_function_id = pcf.control_function_id
   AND ppca.org_id = hou.organization_id
   AND ppca.job_id = pj.job_id
   AND pj.LANGUAGE = 'US'
   AND pj.source_lang = 'US'
   AND pcga.control_group_id = ppca.control_group_id
   AND ppca.org_id = :ou_name

How to rectify FUN_INTER_PAY_NOT_VALID error in create accounting

During month close an error with invoices/receipts/transactions   "FUN_INTER_PAY_NOT_VALID"  is a blocker for the close. When this error occurs for AR, follow below steps to identify and fix this issue.

Step 1: Execute below query and identify the transaction that is causing the issue.

SELECT xlt.source_id_int_1 ID, xlt.security_id_int_1 org_id, xe.event_id,

xe.event_type_code, xe.event_status_code, xe.process_status_code,

xae.encoded_msg

FROM apps.xla_events xe,

xla.xla_transaction_entities xlt,

apps.xla_accounting_errors xae

WHERE xe.entity_id = xlt.entity_id

AND xe.application_id = xlt.application_id

AND ENCODED_MSG like '%FUN%INTER%PAY%'

AND xlt.source_application_id = 222 -- Change Value to 200 for AP

--AND xlt.ledger_id = 12010
--AND xe.event_date between to_date('01-APR-2016','DD-MON-YYYY') and to_date('30-APR-2016','DD-MON-YYYY')

AND xe.process_status_code <> 'P'

AND xe.event_status_code <> 'P'

AND xe.event_id = xae.event_id

ORDER BY xe.event_date, xe.event_id;

Note: ID is the customer_trx_id if the event is invoice, ID is cash_receipt_id if the event is receipt.

 

Step 2: Query the transaction and find the GL strings that is being used. Make sure the code combination is active, and the individual values are active and enabled. 

 

Step3: Go to Accounting Setups > Accounting Options > SOB > Intercompany Accounts
Identify the Balancing segment value and replace the transaction balancing segment. Then make sure the GL string (replaced) and values are active and enabled.

Step4: Enable the code combination or values that are end dated/inactive. Now you will be able to account the transaction.

Step5:  Run Submit Accounting [ Or create Accounting in case of AP]. Make sure the create accounting program completes successfully. Also run the query mentioned in Step1 and make sure the records is not fetching.