Oracle Apps KT

Tuesday, May 17, 2022

Delete fnd_user in oracle apps r12

This is not recommended nor supported.

Use as last option, after testing.

DECLARE
BEGIN
FOR X IN (select * from fnd_user where user_name in ('FIA_10179'))
LOOP
DELETE FROM fnd_user where user_name=X.user_name;
fnd_function_security_cache.delete_user(X.user_id);
END LOOP;

END;

Monday, April 18, 2022

Oracle EBS R12 Update Employee Details

DECLARE

--In Variables for Update Employee API

l_employee_number per_all_people_f.employee_number%TYPE;-- := 'XX1635';

l_object_version_number per_all_people_f.object_version_number%TYPE;

l_person_id per_all_people_f.person_id%TYPE;

l_dt_ud_mode VARCHAR2 (200) := 'CORRECTION';

l_effective_date DATE := trunc ( SYSDATE);

--l_marital_status per_all_people_f.marital_status%TYPE := :p_marital_status;

--l_suffix per_all_people_f.marital_status%TYPE := :p_suffix;

-- Out Variables for Update Employee API

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

o_effective_start_date DATE;

o_effective_end_date DATE;

o_full_name per_all_people_f.full_name%TYPE;

o_comment_id per_all_people_f.comment_id%TYPE;

o_name_combination_warning BOOLEAN;

o_assign_payroll_warning BOOLEAN;

o_orig_hire_warning BOOLEAN;

cursor c1 is

SELECT PPX.PERSON_ID, EMP_NUMBER , OTHERS1 FROM XXFUJ_UPLOAD_TICKET XXFUJ, PER_PEOPLE_X PPX

WHERE

EMPLOYEE_NUMBER = EMP_NUMBER

--AND EMP_NUMBER IN ( 'xx1635')-- and person_id is not null;

;

BEGIN

-- Get person_id and object_version_number for employee_number

for i in c1 loop

BEGIN

SELECT person_id, object_version_number, employee_number

INTO l_person_id, l_object_version_number,l_employee_number

FROM per_all_people_f

WHERE employee_number = i.emp_number

AND sysdate BETWEEN effective_start_date AND effective_end_date

AND business_group_id = 81;

EXCEPTION

WHEN OTHERS THEN

NULL;

END;

-- Issue a savepoint.

SAVEPOINT update_person;

hr_person_api.update_person ( -- Input Data Elements

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

p_effective_date => l_effective_date

,p_datetrack_update_mode => l_dt_ud_mode

,p_person_id => l_person_id

,p_per_information3 => i.OTHERS1

--,p_marital_status => l_marital_status

--,p_suffix => l_suffix

, -- Output Data Elements

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

p_employee_number => l_employee_number

,p_object_version_number => l_object_version_number

,p_effective_start_date => o_effective_start_date

,p_effective_end_date => o_effective_end_date

,p_full_name => o_full_name

,p_comment_id => o_comment_id

,p_name_combination_warning => o_name_combination_warning

,p_assign_payroll_warning => o_assign_payroll_warning

,p_orig_hire_warning => o_orig_hire_warning);

COMMIT;

dbms_output.put_line ('Employee # ' || l_employee_number || ' has been updated successfully!');

end loop;

EXCEPTION

WHEN OTHERS THEN

ROLLBACK TO update_person;

dbms_output.put_line (sqlerrm);

END;

FND_USER_PKG.UPDATEUSER

Oracle EBS R12 Create new SIT (uploading SIT for employee)

DECLARE

CURSOR get_details

SELECT TICKET_CATEGORY,TICKET_TYPE,DURATION,BALANCE, PPX.PERSON_ID, EMP_NUMBER FROM XXFUJ_UPLOAD_TICKET XXFUJ, PER_PEOPLE_X PPX

WHERE

EMPLOYEE_NUMBER = EMP_NUMBER

--AND EMP_NUMBER IN ( 'xx1635')-- and person_id is not null;

;

l_err_msg VARCHAR2 (500) := NULL;

l_id_flex_num NUMBER (9) := NULL;

l_analysis_criteria_id NUMBER (9) := NULL;

l_person_analysis_id NUMBER (9) := NULL;

l_pea_object_version_number NUMBER (9) := NULL;

BEGIN

--Initialize Session

fnd_global.

apps_initialize (user_id => 2605, -- XXXX_MIGRATION

resp_id => 50419, -- XXXX HRMS Manager

resp_appl_id => 800 -- Human Resouces

);

SELECT fifs.id_flex_num

INTO l_id_flex_num

FROM fnd_id_flex_structures fifs

WHERE id_flex_structure_code LIKE 'FUJ_XX_DET';

FOR fetch_details IN get_details

LOOP

l_err_msg := NULL;

l_analysis_criteria_id := NULL;

l_person_analysis_id := NULL;

l_pea_object_version_number := NULL;

BEGIN

hr_sit_api.

create_sit (

p_validate => FALSE,

p_person_id => fetch_details.person_id,

p_business_group_id => 81,

p_id_flex_num => l_id_flex_num,

p_effective_date => SYSDATE,

p_comments => NULL,

p_date_from => SYSDATE

--p_date_to => fetch_details.end_date,

,p_segment1 => fetch_details.TICKET_CATEGORY

,p_segment2 => fetch_details.TICKET_TYPE

,p_segment3 => fetch_details.DURATION

,p_segment4 => nvl(fetch_details.BALANCE,30)

,p_analysis_criteria_id => l_analysis_criteria_id,

p_person_analysis_id => l_person_analysis_id,

p_pea_object_version_number => l_pea_object_version_number);

COMMIT;

EXCEPTION

WHEN OTHERS

THEN

l_err_msg := SQLERRM;

DBMS_OUTPUT.put_line (' Exception: ' || l_err_msg);

COMMIT;

END;

END LOOP;

EXCEPTION

WHEN OTHERS

THEN

l_err_msg := SQLERRM;

DBMS_OUTPUT.put_line ('Main Exception: ' || l_err_msg);

END ;

Oracle EBS R12 Delete Employee

update apps.fnd_user set employee_id=null

WHERE employee_id IN

(SELECT DISTINCT person_id

FROM per_assignments_x

WHERE payroll_id = 149

and person_id <> 11694)

;

--2.

DECLARE

--— Input Variables

l_validate BOOLEAN := FALSE;

l_effective_date DATE := SYSDATE;

l_person_id NUMBER := 0;

l_perform_predel_validation BOOLEAN := FALSE;

--— Output Variables

l_person_org_manager_warning VARCHAR2 (2000);

BEGIN

--–Capture in Cursor

DECLARE

CURSOR res

SELECT person_id, assignment_number EMPLOYEE_number

FROM per_assignments_x where payroll_id = 199

--and person_id = 11694

;

--–Start Loop

BEGIN

FOR rc IN res

LOOP

BEGIN

--–API Update

--— Calling API HR_PERSON_API.DELETE_PERSON

hr_person_api.delete_person

(p_validate => l_validate,

p_effective_date => l_effective_date,

p_person_id => rc.person_id,

p_perform_predel_validation => l_perform_predel_validation,

p_person_org_manager_warning => l_person_org_manager_warning

);

dbms_output.put_line

( 'Employee deleted successfully. Person ID '

|| rc.person_id

|| ' Employee Number '

|| rc.employee_number

);

COMMIT;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line ( 'Unable to delete Employee : '

|| SQLCODE

|| ' '

|| SUBSTR (SQLERRM, 1, 100)

);

END;

END LOOP;

DBMS_OUTPUT.put_line ('Process Completed');

END;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line ('Error : ' || SQLERRM);

END;

ORACLE APPS R12 create Employee Address

TABLE: XX_CREATE_EMP_ADDRESS

DECLARE

lrec_person_details per_all_people_f%ROWTYPE;

ln_object_version_num NUMBER;

ln_address_id NUMBER;

ln_exists NUMBER;

lv_employee_num VARCHAR2(200);-- DEFAULT '123456';

v_api_error VARCHAR2(2000);

CURSOR C1 IS

SELECT *

--INTO lrec_person_details

FROM per_all_people_f papf

, XX_CREATE_EMP_ADDRESS XXFIA

WHERE current_employee_flag = 'Y'

AND 'FIA'||XXFIA.EMP_NUMBER = PAPF.EMPLOYEE_NUMBER

AND PAPF.EMPLOYEE_NUMBER <> 'XX1442'

AND SYSDATE BETWEEN effective_start_date AND effective_end_date;

BEGIN

FOR lrec_person_details IN C1 LOOP

--SELECT * FROM PER_ADDRESSES WHERE PERSON_ID = 47856;

BEGIN

HR_PERSON_ADDRESS_API.CREATE_PERSON_ADDRESS

(p_validate => FALSE,

p_effective_date => lrec_person_details.effective_start_date,

p_person_id => lrec_person_details.person_id,

p_primary_flag => 'Y',

p_style => 'AE',

p_date_from => lrec_person_details.effective_start_date,

--p_address_line1 => '11, TEST ADDRESS LINE',

--p_address_line2 => NULL,

p_address_line3 => lrec_person_details.ADDRES_LINE3,

p_country => 'AE',

--p_postal_code => '40400',

--p_region_1 => 'SELANGOR',

P_TOWN_OR_CITY => lrec_person_details.TOWN_OR_CITY,

p_address_id => ln_address_id,

p_object_version_number => ln_object_version_num

);

DBMS_OUTPUT.PUT_LINE('Address id: '||ln_address_id);

COMMIT;

UPDATE XX_CREATE_EMP_ADDRESS

SET STATUS = 'done', MESSAGE = NULL

WHERE 'FIA'||EMP_NUMBER = lrec_person_details.EMPLOYEE_NUMBER

;

EXCEPTION WHEN OTHERS THEN

v_api_error := sqlerrm;

UPDATE XX_CREATE_EMP_ADDRESS

SET STATUS = 'Error', Message = v_api_error

WHERE 'FIA'||EMP_NUMBER = lrec_person_details.EMPLOYEE_NUMBER

;

END;

END LOOP;

EXCEPTION WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(SQLERRM);

END;

API Apps R12 Document of Records

DECLARE

l_document_extra_info_id NUMBER := NULL;

l_object_version_number NUMBER := NULL;

CURSOR C1 IS

SELECT PPX.PERSON_ID,PPX.PER_INFORMATION18 NATIONALITY_CODE,

--fnd_conc_date.string_to_date(VALID_FROM)fnd_dt,

to_date(VALID_FROM,'mm/dd/yyyy')fnd_dt,

--FND_DATE.canonical_to_date(valid_from)fnd_dt,

XXFUJ.* FROM XXFUJ_DOC_RECORDS_P XXFUJ, PER_PEOPLE_X PPX

WHERE EMP_NUMBER = 'XX1085'

AND EMP_NUMBER = EMPLOYEE_NUMBER

AND PASSPORT_NUMBER IS NOT NULL

and PPX.PER_INFORMATION18 is not null

AND XXFUJ.EMP_NUMBER NOT IN

(

select PER.EMPLOYEE_NUMBER

FROM

hr_document_extra_info dei,

per_all_people_f per,

hr_document_types hdt

WHERE

per.person_id = dei.person_id

AND per.business_group_id = 81

--AND per.employee_number ='XX1085'

AND per.current_employee_flag = 'Y'

AND trunc(SYSDATE) BETWEEN per.effective_start_date AND per.effective_end_date

AND hdt.document_type_id = dei.document_type_id

AND DEI_INFORMATION_CATEGORY = 'Passport'

)

;

BEGIN

FOR I IN C1 LOOP

hr_document_extra_info_api.create_doc_extra_info (

p_validate => FALSE,

p_person_id => I.PERSON_ID,

p_verified_by => 0,

p_document_type_id => 62,

p_date_from => TO_DATE(I.VALID_FROM,'mm/dd/yyyy'),--fnd_conc_date.string_to_date(I.VALID_FROM),

p_date_to => TO_DATE(I.VALID_TO,'mm/dd/yyyy'),--fnd_conc_date.string_to_date(I.VALID_TO),

p_document_number => '',

p_DEI_INFORMATION_CATEGORY => 'Passport',

p_DEI_INFORMATION1 => I.PASSPORT_NUMBER,

p_DEI_INFORMATION2 => 1,--NORMAL PASSPORT

p_DEI_INFORMATION3 => I.NATIONALITY_CODE,

p_DEI_INFORMATION6 => I.COUNTRY_ORIGIN,

p_DEI_INFORMATION7 => i.UNIFIED_NUMBER,

--p_DEI_INFORMATION10 => I.LABOUR_CARD,

---p_DEI_INFORMATION11 =>TO_CHAR(I.LABOUR_CARD_DATE,'YYYY/MM/DD HH24:MI:SS'),--fnd_conc_date.string_to_date(I.LABOUR_CARD_DATE),

p_document_extra_info_id => l_document_extra_info_id,

p_object_version_number => l_object_version_number

);

COMMIT;

DBMS_OUTPUT.put_line ('SUCCESS');

END LOOP;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line ('FAILURE ' || SQLERRM);

END;

Wednesday, March 30, 2022

create job api for Oracle Apps R12

DECLARE
lv_group_name VARCHAR2(200) := 'HR_51';
ln_business_group_id NUMBER;
ln_job_group_id NUMBER;
ln_job_id NUMBER;
ln_object_version_number NUMBER;
ln_job_definition_id NUMBER;
lv_job_name VARCHAR2(200);

CURSOR C1 IS

SELECT INITCAP(A)JOB_NAME
FROM TESTA
where UPPER(A) NOT IN (SELECT UPPER(NAME) FROM PER_JOBS)
;

BEGIN

-- get group id and business group id
BEGIN
SELECT business_group_id,
job_group_id
INTO ln_business_group_id,
ln_job_group_id
FROM per_job_groups
--WHERE displayed_name = lv_group_name;
where business_group_id = 81;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Unable to get the job group details.Error:'||SQLERRM);
RAISE;
END;
FOR I IN C1 LOOP
BEGIN
HR_JOB_API.CREATE_JOB
(p_validate => FALSE
,p_business_group_id => ln_business_group_id
,p_date_from => TO_DATE('01-JAN-1950')
,p_comments => 'Uploaded from API 31032022'
,p_date_to => NULL
,p_job_group_id => ln_job_group_id
,p_concat_segments => i.JOB_NAME
,p_language_code => 'US'
,p_job_id => ln_job_id
,p_object_version_number => ln_object_version_number
,p_job_definition_id => ln_job_definition_id
,p_name => lv_job_name
);

UPDATE TESTA SET B = 'DONE'
WHERE A = I.JOB_NAME;

DBMS_OUTPUT.PUT_LINE('JOB ID: '||ln_job_id);
DBMS_OUTPUT.PUT_LINE('OBJECT_VERSION_NUMBER: '||ln_object_version_number);
DBMS_OUTPUT.PUT_LINE('JOB_DEFINITION_ID: '||ln_job_definition_id);
DBMS_OUTPUT.PUT_LINE('JOB_NAME: '||lv_job_name);

EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Unable to create a job.Error:'||SQLERRM);
--RAISE;
UPDATE TESTA SET B = 'ERROR'
WHERE A = I.JOB_NAME;


END;

END LOOP;
END;

Search This Blog

Tuesday, May 17, 2022

Monday, April 18, 2022

Wednesday, March 30, 2022