Oracle Application Blog

API to Update Profile Value – SSO to Local

DECLARE L_SUCCESS BOOLEAN; CURSOR uid is SELECT user_name,user_id from fnd_user where user_name in (select user_name from apps.fnd_user where email_address in (select email_address from apps.per_all_people_f where 1=1 and employee_number in (‘215677…

Read More

API to Update Profile Value – Local to SSO

DECLARE L_SUCCESS BOOLEAN; CURSOR uid is SELECT user_name,user_id from fnd_user where user_name in (select user_name from apps.fnd_user where email_address in (select email_address from apps.per_all_people_f where 1=1 and employee_number in (‘215677’…

Read More

Script to delete the employee record using API in oracle

–Create Table create table papf_16082018 (person_id number, employee_number varchar2(10)); –Insert Data insert into papf_16082018 values (53687,’215677′); commit; update apps.fnd_user set employee_id=null where employee_id in                 (select distinct p.person_id from apps.per_all_people_f…

Read More

Export employee data with personal expenses in i-expense and the expense is not claimed

SELECT pap.full_name, pap.email_address, pap.employee_number,                 aeh.invoice_num, aeh.total total_amount,                 aeh.amt_due_employee personal_amount, aeh.expense_status_code,                 aeh.report_header_id, aeh.employee_id, aeh.org_id, aeh.ROWID,                 round((trunc(sysdate)-trunc(aia.creation_date)))days,aia.creation_date            FROM ap_expense_report_headers_all aeh, per_all_people_f pap,ap_invoices_all aia           WHERE aeh.employee_id =…

Read More

Employee data extract based on business group

SELECT      hou.name                business_unit,      papf1.first_name,      papf1.middle_names,      papf1.last_name,      papf1.full_name         emp_full_name,      pj.name                 employeee_job_desc,      pj.attribute16          wd_job,      papf1.employee_number   employee_number,      papf1.email_address     employee_email_hr,      fu.email_address        employee_email_fnd,      fu.user_name           …

Read More

Fusion HCM – Automating Payroll Batch Loader

Below are the Steps to Automate Payroll Batch Loader : Create an HCM Extract to deliver PBL file to UCM Content Server in required Format Create a BI Report which…

Read More

Fusion HCM – Steps to Create a Basic HCM Extract

Below Steps Shows How to Create an HCM Extract : An HCM Extract is created from the below Navigation : Navigator -> Data Exchange -> Tasks -> Manage Extract Definitions…

Read More

Fusion HCM – Getting HR Data in Fast Formula

To get HR Data in Fast Formula, 1. The Formula Type must be Payroll Access to HR2. U Must use Proper CHANGE_CONTEXTS to get required Data Below is a Sample…

Read More

Script to get PHONE NO with area code of the customer

SELECT    LISTAGG(                 cont_point.phone_area_code                          || DECODE (cont_point.contact_point_type,                                     ‘TLX’, cont_point.telex_number,                                     REGEXP_REPLACE (cont_point.phone_number,                                                     ‘[^[:alnum:]” ”]’,                                                     NULL                                                    )                                    ),’|’) WITHIN GROUP (ORDER BY cont_point.phone_number) into lv_phone_no…

Read More

Script to get EMAIL information of the customer

SELECT    LISTAGG( cont_point.EMAIL_ADDRESS,’|’) WITHIN GROUP (ORDER BY cont_point.EMAIL_ADDRESS) into lv_email_address                     FROM hz_contact_points cont_point,                          –hz_contact_points cont_point1,                          hz_cust_account_roles acct_role,                          hz_parties party,                          hz_parties rel_party,                          hz_relationships rel,                         …

Read More