Job Creation Sample Script

DECLARE
op_job_id NUMBER;
op_object_version_number NUMBER;
x_job_definition_id NUMBER;
op_name VARCHAR2(1000);
lv_job_group_id per_job_groups.job_group_id%TYPE;
BEGIN
---------------------------------------------------
-->> JOB GROUP ID <<--
---------------------------------------------------
SELECT job_group_id
INTO lv_job_group_id
FROM per_job_groups
WHERE displayed_name LIKE 'Setup Business Group';

hr_job_api.CREATE_JOB

(p_business_group_id => '0',p_date_from => SYSDATE,
p_job_group_id => lv_job_group_id,p_segment1 => 'NEW_JOB' --JOB_TITLE
,p_job_id => op_job_id,p_object_version_number => op_object_version_number,
p_job_definition_id => x_job_definition_id,
p_name => op_name);

dbms_output.PUT_LINE('OUTPUT JOB NAME CREATED : '
op_name);
EXCEPTION
WHEN OTHERS THEN
dbms_output.PUT_LINE('Error Message Is: '
SQLERRM);
END;
/

Create Employee API Sample Script

DECLARE
ip_p_person_id NUMBER;
ip_p_assignment_id NUMBER;
ip_p_per_object_version_number NUMBER;
ip_p_asg_object_version_number NUMBER;
ip_p_per_effective_start_date DATE;
ip_p_per_effective_end_date DATE;
ip_p_full_name VARCHAR2 (1000);
ip_p_per_comment_id NUMBER;
ip_p_assignment_sequence NUMBER;
ip_p_assignment_number VARCHAR2 (1000);
ip_p_name_combination_warning BOOLEAN;
ip_p_assign_payroll_warning BOOLEAN;
ip_p_orig_hire_warning BOOLEAN;
ip_employee_number VARCHAR2 (1000);
BEGIN
ip_employee_number := 'Test-102';
hr_employee_api.create_employee
(p_hire_date => SYSDATE
,p_business_group_id => 0
,p_last_name => 'TEST_API1'
,p_sex => 'M'
,p_employee_number => ip_employee_number
,p_attribute1 => '123456789'
,p_global_person_id => NULL
,p_party_id => NULL
,p_person_id => ip_p_person_id
,p_assignment_id => ip_p_assignment_id
,p_per_object_version_number => ip_p_per_object_version_number
,p_asg_object_version_number => ip_p_asg_object_version_number
,p_per_effective_start_date => ip_p_per_effective_start_date
,p_per_effective_end_date => ip_p_per_effective_end_date
,p_full_name => ip_p_full_name
,p_per_comment_id => ip_p_per_comment_id
,p_assignment_sequence => ip_p_assignment_sequence
,p_assignment_number => ip_p_assignment_number
,p_name_combination_warning => ip_p_name_combination_warning
,p_assign_payroll_warning => ip_p_assign_payroll_warning
,p_orig_hire_warning => ip_p_orig_hire_warning
);
DBMS_OUTPUT.put_line (SQLERRM);
COMMIT;
END;

Form Securing Attributes

DECLARE
v_return_status VARCHAR2 (1);
v_msg_count NUMBER;
v_msg_data VARCHAR2 (240);
exception_tag_user_id NUMBER;
api_unexpectedly_failed EXCEPTION;
BEGIN
FOR x IN (SELECT fu.user_id web_user_id,
fu.user_name,
'ICX_HR_PERSON_ID' attribute_code,
178 attribute_application_id,
fu.employee_id number_value,
-1 created_by,
SYSDATE creation_date,
-1 last_updated_by,
SYSDATE last_update_date,
-1 last_update_login
FROM fnd_user fu
WHERE fu.employee_id IS NOT NULL
AND fu.creation_date > SYSDATE - 220
AND NOT EXISTS ( SELECT 'x' FROM ak_web_user_sec_attr_values awusav
WHERE awusav.web_user_id = fu.user_id
AND awusav.attribute_application_id = 178
AND awusav.attribute_code = 'ICX_HR_PERSON_ID')
UNION
SELECT fu.user_id web_user_id,
fu.user_name,
'TO_PERSON_ID' attribute_code,
178 attribute_application_id,
fu.employee_id number_value,
-1 created_by, SYSDATE creation_date,
-1 last_updated_by,
SYSDATE last_update_date,
-1 last_update_login
FROM fnd_user fu
WHERE fu.employee_id IS NOT NULL
AND fu.creation_date > SYSDATE - 220
AND NOT EXISTS ( SELECT 'x' FROM ak_web_user_sec_attr_values awusav
WHERE awusav.web_user_id = fu.user_id
AND awusav.attribute_application_id = 178
AND awusav.attribute_code = 'TO_PERSON_ID'))
LOOP
icx_user_sec_attr_pub.create_user_sec_attr
(p_api_version_number => 1,
p_web_user_id => x.web_user_id,
p_attribute_code => x.attribute_code,
p_attribute_appl_id => x.attribute_application_id,
p_varchar2_value => fnd_api.g_false,
p_date_value => fnd_api.g_miss_date,
p_number_value => x.number_value,
p_created_by => x.created_by,
p_creation_date => x.creation_date,
p_last_updated_by => x.last_updated_by,
p_last_update_date => x.last_update_date,
p_last_update_login => x.last_update_login,
p_return_status => v_return_status,
p_msg_count => v_msg_count,
p_msg_data => v_msg_data );
IF v_return_status = fnd_api.g_ret_sts_unexp_error
THEN
exception_tag_user_id := x.web_user_id;
RAISE api_unexpectedly_failed;
ELSIF v_msg_count = 1
THEN-- retcode := 1;
DBMS_OUTPUT.put_line ('');
DBMS_OUTPUT.put_line ('*** Failed *** User ID ' x.web_user_id);
DBMS_OUTPUT.put_line ('. Message ' v_msg_count '. ' v_msg_data);
ELSIF v_msg_count > 1
THEN
-- retcode := 1;
DBMS_OUTPUT.put_line ('');
DBMS_OUTPUT.put_line ('*** Failed *** User ID ' x.web_user_id);
FOR y IN 0 .. v_msg_count
LOOP v_msg_data := fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);
IF v_msg_data IS NULL
THEN EXIT;
END IF;
DBMS_OUTPUT.put_line ('. Message ' y '. ' v_msg_data);
END LOOP;
END IF;
DBMS_OUTPUT.put_line (' Added Sec Attrib to ' x.web_user_id ' ' x.user_name ' ' x.attribute_code); END LOOP;
EXCEPTION
WHEN api_unexpectedly_failed
THEN
DBMS_OUTPUT.put_line ('API Unexpectedly Failed on User_id record ' (exception_tag_user_id)); DBMS_OUTPUT.put_line ('API Failed with ' SQLERRM);
DBMS_OUTPUT.put_line ('API Failed with ' SQLCODE);
DBMS_OUTPUT.put_line ('ALL API Transactions should / have been Rolled Back'); DBMS_OUTPUT.put_line ('*** REPORT TO THE DBA ***');
DBMS_OUTPUT.put_line ('*** REPORT TO THE DBA ***');
-- retcode := 2;
DBMS_OUTPUT.put_line ('.');
ROLLBACK;
WHEN OTHERS THEN
DBMS_OUTPUT.put_line ('API Unexpectedly and passed to OTHERS Handler');
DBMS_OUTPUT.put_line ('Failed on receipt record ' (exception_tag_user_id));
DBMS_OUTPUT.put_line ('API Failed with ' SQLERRM);
DBMS_OUTPUT.put_line ('API Failed with ' SQLCODE);
DBMS_OUTPUT.put_line ('ALL API Transactions should / have been Rolled Back'); DBMS_OUTPUT.put_line ('*** REPORT TO THE DBA ***');
DBMS_OUTPUT.put_line ('*** REPORT TO THE DBA ***');
--retcode := 2;
DBMS_OUTPUT.put_line ('.');
ROLLBACK;
END;/

Collections

A collection is nothing more than an array. The most basic collection is a single dimension array.A collection can be loosely defined as a group of ordered elements, all of the same type, that allows programmatic access to its elements through an index.

The Varray is short for Variable Array.A Varray stores elements of the same type in the order in which they are added.The number of elements in a Varray must be known at the time of its declaration.In other words, a Varray has a fixed lower and upper bounds.

Method - Action It Performs
COUNT Returns number of elements in the Collection
EXISTS Returns Boolean
true if element at specified index exists; otherwise, false
EXTEND Increases size of Collection by 1 or number specified, ie.EXTEND(n)
**Cannot use with Associative Array
FIRST Navigates to the first element in the Collection
LAST Navigates to the last element
PRIOR Navigates to the previous element
NEXT Navigates to the next element
TRIM Removes the last element, or the last n elements if a number is specified,
ie. TRIM(n) **Cannot use with Associative Array
DELETE Removes all elements of a Collection, or the nth element, if a parameter is specified
===============================================
Simple table type example
===============================================
DECLARE
v is table of varchar2(10);
v1 v := v();
BEGIN
v1.extend;
v1(1):='Hari';
v1.extend;
v1(2):='Santosh';
v1.extend;
v1(3):='Chandra';
v1.extend;
v1(4):='Jeevan';
for i in v1.first..v1.last
loop
dbms_output.put_line(v1(i));
end loop;
end;
/
============================================================
BULK COLLECT
============================================================
declare
type v is table of emp_bkp%rowtype;
v1 v;
begin
select *
bulk collect into v1
from emp;
forall i in v1.first..v1.last
insert into emp_bkp values v1(i);
end;
============================================================
RECORD and TABLE TYPE
=============================================================
DECLARE
TYPE ssn_rec IS RECORD(ssn NUMBER,name VARCHAR2(30),age NUMBER);
TYPE ssn_tbl1 IS TABLE OF ssn_rec INDEX BY BINARY_INTEGER;
ssn_tbl ssn_tbl1;
BEGIN
ssn_tbl(1).ssn:=223344;
ssn_tbl(1).name:='Hari';
ssn_tbl(1).age:=30;
ssn_tbl(2).ssn:=223345;
ssn_tbl(2).name:='Jeevan';
ssn_tbl(2).age:=31;
ssn_tbl(3).ssn:=223346;
ssn_tbl(3).name:='Ashok';
ssn_tbl(3).age:=29;

FOR i IN ssn_tbl.first .. ssn_tbl.last
LOOP
dbms_output.put_line('Name of the person is'ssn_tbl(i).name);
INSERT INTO test_tab VALUES(ssn_tbl(i).ssn,ssn_tbl(i).name,ssn_tbl(i).age);
END LOOP;
END;
OTHER Queries:
=============
CREATE TABLE test_tab(ssn1 NUMBER,name VARCHAR2(30),age NUMBER)
SELECT * FROM test_tab
DELETE test_tab

Query to Check Database Locks and Kill the locked sessions

Below query lists all the objects that have been locked:
SELECT s.SID
,s.serial#
,SUBSTR (s.module, 1, 20) module
,s.status
,lo.os_user_name
,o.object_name
,l.lmode
,l.TYPE lock_type
FROM v$lock l, v$session s, all_objects o, v$locked_object lo
WHERE l.SID = s.SID
AND s.SID = lo.session_id
AND lo.object_id = o.object_id
--AND o.object_name LIKE 'TTWIPJOBCLNUP_NEW'
--AND o.object_type = 'PACKAGE'
--AND o.owner = 'IGS'
ORDER BY o.object_name

Use the below given Command to KILL the particular Session:
--ALTER SYSTEM KILL SESSION 'SID,SERIAL#'
ALTER SYSTEM KILL SESSION '1946,3897'