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'

AIM Documents and its relevant Information.

Hi All,
Download the AIM templates from the following link:
http://www.bryanthompsononline.com/oracle/2008/05/06/download-oracle-aim-applications-implementation-methodology-software/
Different Phases:
======================================
Business Process Architecture (BP)

• BP.010 Define Business and Process Strategy
• BP.020 Catalog and Analyze Potential Changes
• BP.030 Determine Data Gathering Requirements
• BP.040 Develop Current Process Model
• BP.050 Review Leading Practices
• BP.060 Develop High-Level Process Vision
• BP.070 Develop High-Level Process Design
• BP.080 Develop Future Process Model
• BP.090 Document Business Procedure

======================================
Business Requirements Definition (RD)

• RD.010 Identify Current Financial and Operating Structure
• RD.020 Conduct Current Business Baseline
• RD.030 Establish Process and Mapping Summary
• RD.040 Gather Business Volumes and Metrics
• RD.050 Gather Business Requirements
• RD.060 Determine Audit and Control Requirements
• RD.070 Identify Business Availability Requirements
• RD.080 Identify Reporting and Information Access Requirements

======================================
Business Requirements Mapping

• BR.010 Analyze High-Level Gaps
• BR.020 Prepare mapping environment
• BR.030 Map Business requirements
• BR.040 Map Business Data
• BR.050 Conduct Integration Fit Analysis
• BR.060 Create Information Model
• BR.070 Create Reporting Fit Analysis
• BR.080 Test Business Solutions
• BR.090 Confirm Integrated Business Solutions
• BR.100 Define Applications Setup
• BR.110 Define security Profiles

======================================
Application and Technical Architecture (TA)

• TA.010 Define Architecture Requirements and Strategy
• TA.020 Identify Current Technical Architecture
• TA.030 Develop Preliminary Conceptual Architecture
• TA.040 Define Application Architecture
• TA.050 Define System Availability Strategy
• TA.060 Define Reporting and Information Access Strategy
• TA.070 Revise Conceptual Architecture
• TA.080 Define Application Security Architecture
• TA.090 Define Application and Database Server Architecture
• TA.100 Define and Propose Architecture Subsystems
• TA.110 Define System Capacity Plan
• TA.120 Define Platform and Network Architecture
• TA.130 Define Application Deployment Plan
• TA.140 Assess Performance Risks
• TA.150 Define System Management Procedures

======================================
Module Design and Build (MD)

• MD.010 Define Application Extension Strategy
• MD.020 Define and estimate application extensions
• MD.030 Define design standards
• MD.040 Define Build Standards
• MD.050 Create Application extensions functional design
• MD.060 Design Database extensions
• MD.070 Create Application extensions technical design
• MD.080 Review functional and Technical designs
• MD.090 Prepare Development environment
• MD.100 Create Database extensions
• MD.110 Create Application extension modules
• MD.120 Create Installation routines

======================================
Data Conversion (CV)

• CV.010 Define data conversion requirements and strategy
• CV.020 Define Conversion standards
• CV.030 Prepare conversion environment
• CV.040 Perform conversion data mapping
• CV.050 Define manual conversion procedures
• CV.060 Design conversion programs
• CV.070 Prepare conversion test plans
• CV.080 Develop conversion programs
• CV.090 Perform conversion unit tests
• CV.100 Perform conversion business objects
• CV.110 Perform conversion validation tests
• CV.120 Install conversion programs
• CV.130 Convert and verify data

======================================

Documentation (DO)

• DO.010 Define documentation requirements and strategy
• DO.020 Define Documentation standards and procedures
• DO.030 Prepare glossary
• DO.040 Prepare documentation environment
• DO.050 Produce documentation prototypes and templates
• DO.060 Publish user reference manual
• DO.070 Publish user guide
• DO.080 Publish technical reference manual
• DO.090 Publish system management guide

======================================
Business System Testing (TE)

• TE.010 Define testing requirements and strategy
• TE.020 Develop unit test script
• TE.030 Develop link test script
• TE.040 Develop system test script
• TE.050 Develop systems integration test script
• TE.060 Prepare testing environments
• TE.070 Perform unit test
• TE.080 Perform link test
• TE.090 perform installation test
• TE.100 Prepare key users for testing
• TE.110 Perform system test
• TE.120 Perform systems integration test
• TE.130 Perform Acceptance test

======================================

PERFORMACE TESTING(PT)

• PT.010 - Define Performance Testing Strategy
• PT.020 - Identify Performance Test Scenarios
• PT.030 - Identify Performance Test Transaction
• PT.040 - Create Performance Test Scripts
• PT.050 - Design Performance Test Transaction Programs
• PT.060 - Design Performance Test Data
• PT.070 - Design Test Database Load Programs
• PT.080 - Create Performance Test Transaction Programs
• PT.090 - Create Test Database Load Programs
• PT.100 - Construct Performance Test Database
• PT.110 - Prepare Performance Test Environment
• PT.120 - Execute Performance Test

======================================
Adoption and Learning (AP)

• AP.010 - Define Executive Project Strategy
• AP.020 - Conduct Initial Project Team Orientation
• AP.030 - Develop Project Team Learning Plan
• AP.040 - Prepare Project Team Learning Environment
• AP.050 - Conduct Project Team Learning Events
• AP.060 - Develop Business Unit Managers Readiness Plan
• AP.070 - Develop Project Readiness Roadmap
• AP.080 - Develop and Execute Communication Campaign
• AP.090 - Develop Managers' Readiness Plan
• AP.100 - Identify Business Process Impact on Organization
• AP.110 - Align Human Performance SupportSystems
• AP.120 - Align Information Technology Groups
• AP.130 - Conduct User Learning Needs Analysis
• AP.140 - Develop User Learning Plan
• AP.150 - Develop User Learningware
• AP.160 - Prepare User Learning Environment
• AP.170 - Conduct User Learning Events
• AP.180 - Conduct Effectiveness Assessment

======================================

Production Migration (PM)

• PM.010 - Define Transition Strategy
• PM.020 - Design Production Support Infrastructure
• PM.030 - Develop Transition and Contingency Plan
• PM.040 - Prepare Production Environment
• PM.050 - Set Up Applications
• PM.060 - Implement Production Support Infrastructure
• PM.070 - Verify Production Readiness
• PM.080 - Begin Production
• PM.090 - Measure System Performance
• PM.100 - Maintain System
• PM.110 - Refine Production System
• PM.120 - Decommission Former Systems
• PM.130 - Propose Future Business Direction
• PM.140 - Propose Future Technical Direction