To create a tar file.

login into unix(putty):

Enter the IP Address,Provide Username/Password
To go to defined schema cmd: sudo su -  schema name

pwd command used to shw the path,
cd .. command used to caom back from specified path,
ls command used to list files
to compile a file ./filename.extension

Creating a tar file:

Place the specified sql,sh files in bin(sh),sql(sql) if particular top folders.

sql file contains the views code to be created,sh file contains the script path to execute.

now from the particular path create tar file using command,

tar -cvf  /xxhr/per/file.sql 

/xxhr/per says the location of tar files from where it is taking,can add sequence of file paths too.

While compiling special characters may appear to remove

shift ;%s/.$=>:%s/.$


:wq

(save and quit)



quit ctrl z

Simple Folders

Simple Folders : Is nothing but creation of the Business area by using single


table.



For ex we required UserID

Username

Creation_date



we can all these columns from single table so we can go for creation of Simple folder





Process:

=========



1)Open the Admin Edition

2)Create the business group by creating the Simple Folder.(Select table Name)

3)Goto Desktop Edition and open the business area whatever we have created.

4)select the Columns whatever we would like to display.

5)Create the WorkBook and save the Workbook either in the Database or in the File.

ORACLE PURCHASING

Creating Purchase Orders

Oracle Purchasing provides four types:

Standard purchase order

Contract purchase agreement

Blanket purchase agreement

Planned purchase order



Standard Purchase Order Elements need:

One time purchases


Know details of purchase:

Specific goods or service

Known pricing

Quantity

Delivery schedule

Internal charges

Short-term agreement

Need to encumber the purchase immediately
 
 
When to Use a Contract Purchase Agreement:
 
Contract Purchase Agreement:


Details of goods or services to be purchased are not known

Terms and conditions are specified with your supplier

You reference contract purchase agreements directly on standard purchase order lines


When to Use a Blanket Purchase Agreement:

Long-term agreement:


Effective date and expiration date

Details of goods or services are known:

Pricing

Dates of delivery are not known

Actual purchases occur when a blanket release is issued

When to Use Planned Purchase Orders:

Planned Purchase Order


Dates of delivery tentatively scheduled

Details of services or goods are known

Actual purchases occur when a scheduled release is issued

HRMS FUNCTIONAL AN INTRO

What is Oracle Human Resources?

 
Oracle Human Resources (HR) is a proactive management solution that helps control costs while developing and supporting an effective workforce. Among the many features of Oracle HR is the ability to:

 

Manage the entire recruitment cycle.

• Design organizational models that match current and future business strategies and objectives.
• Perform position management by defining and recording required skills, competencies, experience and qualifications for positions, jobs and organizations .
• Perform career management functions relating to the definition of competencies, assessments, suitability matching, graphical ranking and succession planning.
• Administer and maintain benefits plans, coverage levels and contribution allocations.
• Manage salary proposals and approve these by component.
• Use spreadsheets to export compensation and benefit details for comparison with external survey figures

 Oracle Human Resources provides the shortest route to fast, smart human resource management.

 

What is Oracle Payroll?

  

Oracle Payroll is a high–performance, rule based payroll management system designed to keep pace with changing enterprises and workforce needs.Payroll managers require a solution to address unique requirements and offer complex calculations without losing the benefits of a standard supported package. Oracle Payroll offers that capability via a unique,data driven approach that enables the definition and management of diverse payroll requirements.

 

Among its many capabilities, Oracle Payroll delivers the power to:

• Process many payrolls quickly and easily in a single day
• Define comprehensive personal payment methods
• Quickly create complex calculation rules such as union overtime without programming
• Efficiently check, double check and reconcile payrolls
• Make retroactive adjustments to past earnings or deductions
• Examine employee payment histories at any time
• Track and monitor employee costs via online access to payroll data
• Disburse in multiple currencies
• Transfer payroll information to the general ledger and to other accounting systems, including project costing systems
• Report on payroll results to the tax office and company executives
• Maintain full security and integrity of payroll information,including historical information
• Enable access to information when required for inquiries and responses to pay queries

 

Oracle Payroll enables fast, flexible and accurate payroll processing from time capture to ledger costing.

 

What is Oracle Advanced Benefits?

 

In addition to the powerful compensation and benefit functionality included in Oracle Human Resources, Oracle also offers Oracle Advanced Benefits. Oracle Advanced Benefits enables the setup and administration of a complete benefits offering for enterprises managing their own benefits administration.

 

Oracle Advanced Benefits delivers the following key functions:

 

Pre and post–enrollment communications

 

Web and interactive voice response (IVR) enrollment for cafeteria and exceptions

 

Default and mass enrollments

 

Enrollment process monitoring

 

Life event management (for example, new hires, transfers,relocations or age changes)

 

Web–based what–if eligibility analysis

 

Flexibility spending account claims processing and reporting

 

Oracle Advanced Benefits provides a total compensation framework,setting the stage for exciting and new compensation solutions.

 

What is Oracle Self–Service Human Resources (SSHR)

 

SSHR provides self–service human resource management for managers and employees. Using an intranet and a web browser employees and their managers now have easy to use and intuitive access to personal
data and career management functionality.Oracle Workflow is used extensively in SSHR. SSHR uses Workflow to  manage the flow of information between employees and management.The workflow engine is used for business process transactions and can route decision making through approval chains. For example, an employee may apply for a job using the Apply for a Job function and through a management approvals process be informed and accepted into a job.

 

The workflow engine is also used to modify and configure much of SSHR.Using SSHR you can:

 

• Manage careers:This includes appraising employee’s competencies, matching a person to a job or position by competence and planning succession.

• Perform web based recruitment using ’Candidate Offers’ Candidate offers enables you to perform web based recruitment. Managers can seek approval for an appointment then advise jobapplicants, by letter, that they have been successful. This functionis offered with its own responsibilities.racle Self–Service

Purchase Order Flow

Create Requisition:


When the company/person wants to purchase any item a Requisition is created

Step 2: RFQ (Request For Quotation)

Once Requisition is approved then the company/person will Request for Quotation (RFQ) specifying the required item, quantity.... etc and send them to the vendors who can supply the item



Step 3: Quotation



Once the vendor receives the RFQ then he will prepare the Quotation and send it back to the company/person



Step 4: Purchase Order (PO)



After receiving different quotations from different vendors company/person will go through the quotations and approves the quotation that is best suited/reliable for the company/person.

Based on the approved quotation Purchase Order is generated to the vendor. Now the vendor will supply the item.



Step 5: Receipts



Once the Item is shipped the vendor will raise the Receipt to the company/person.



Step 6: Invoice



Based on the receipt Invoice is generated. For this invoice the company/person will make Payments.

OM Base Tables

This is available in metalink 

Order Management Tables.

Entered

oe_order_headers_all 1 record created in header tableoe_order_lines_all Lines for particular records
oe_price_adjustments When discount gets applied
oe_order_price_attribs If line has price attributes then populated
oe_order_holds_all If any hold applied for order like credit check etc.

Booked
oe_order_headers_all Booked_flag=Y Order booked.wsh_delivery_details Released_status Ready to release

Pick Released
wsh_delivery_details Released_status=Y Released to Warehouse (Line has been released to Inventory for processing)
wsh_picking_batches After batch is created for pick release.
mtl_reservations This is only soft reservations. No physical movement of stock

Full Transaction
mtl_material_transactions No records in mtl_material_transactionsmtl_txn_request_headers
mtl_txn_request_lines
wsh_delivery_details Released to warehouse.wsh_new_deliveries if Auto-Create is Yes then data populated.wsh_delivery_assignments deliveries get assigned

Pick Confirmed
wsh_delivery_details Released_status=Y Hard Reservations. Picked the stock. Physical movement of stock

Ship Confirmed
wsh_delivery_details Released_status=C Y To C:Shipped ;Delivery Note get printed Delivery assigned to trip stopquantity will be decreased from stagedmtl_material_transactions On the ship confirm form, check Ship all boxwsh_new_deliveries If Defer Interface is checked I.e its deferred then OM & inventory not updated. If Defer Interface is not checked.: Shipped
oe_order_lines_all Shipped_quantity get populated.wsh_delivery_legs 1 leg is called as 1 trip.1 Pickup & drop up stop for each trip.
oe_order_headers_all If all the lines get shipped then only flag N

Autoinvoice
wsh_delivery_details Released_status=I Need to run workflow background process.
ra_interface_lines_all Data will be populated after wkfw process.
ra_customer_trx_all After running Autoinvoice Master Program forra_customer_trx_lines_all specific batch transaction tables get populated

Price Detailsqp_list_headers_b To Get Item Price Details.qp_list_lines

Items On Hand Qty
mtl_onhand_quantities TO check On Hand Qty Items.

Payment Terms
ra_terms Payment terms

AutoMatic Numbering System
ar_system_parametes_all you can chk Automactic Numbering is enabled/disabled.

Customer Information
hz_parties Get Customer information include name,contacts,Address and Phonehz_party_sites
hz_locations
hz_cust_accounts
hz_cust_account_sites_all
hz_cust_site_uses_all
ra_customers

Document Sequence
fnd_document_sequences Document Sequence Numbersfnd_doc_sequence_categories
fnd_doc_sequence_assignments

Default rules for Price List
oe_def_attr_def_rules Price List Default Rulesoe_def_attr_condns
ak_object_attributes

End User Details
csi_t_party_details To capture End user Details

Sales Credit Sales Credit Information(How much credit can get)oe_sales_credits

Attaching Documents
fnd_attached_documents Attched Documents and Text information
fnd_documents_tl
fnd_documents_short_text

Blanket Sales Order
oe_blanket_headers_all Blanket Sales Order Information.
oe_blanket_lines_all

Processing Constraints
oe_pc_assignments Sales order Shipment schedule Processing Constratins
oe_pc_exclusions

Sales Order Holds
oe_hold_definitions Order Hold and Managing Details.
oe_hold_authorizations
oe_hold_sources_all
oe_order_holds_all

Hold Relaese
oe_hold_releases_all Hold released Sales Order.

Credit Chk Details
oe_credit_check_rules To get the Credit Check Againt Customer.

Cancel Ordersoe_order_lines_all Cancel Order Details.

Few KFF

KEY FLEXFIELDS




1) Oracle General Ledger:

Accounting Flexfield

2) Oracle Assets:

Category Flexfield

Asset key Flexfield

Location Flexfield

3) Oracle Receivables:

Territory Flexfield

Sales Tax Flexfield

4) Oracle Inventory:

Item Catalog Flexfield

Item Category Flexfield

System Items Flexfield

Stock Locators Flexfield

Sales Order Flexfield

Service Item Flexfield

Account Aliases Flexfield



5) Oracle Human Resources:



Competence Flexfield

Item contexts Key Flexfield

CAGR Flexfield

Soft Coded Key Flexfield

Position Flexfield

Personal Analysis Flexfield

Job Flexfield

Grade Flexfield

People Group Flexfield

Interface Tables

GL INTERFACE TABLES


TNAME TABTYPE

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

GL_BUDGET_INTERFACE TABLE

GL_DAILY_RATES_INTERFACE TABLE

GL_IEA_INTERFACE TABLE

GL_INTERFACE TABLE

GL_INTERFACE_CONTROL TABLE

GL_INTERFACE_HISTORY TABLE



AP INTERFACE TABLES

TNAME TABTYPE

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

AP_INTERFACE_CONTROLS TABLE

AP_INTERFACE_REJECTIONS TABLE

AP_INVOICES_INTERFACE TABLE

AP_INVOICE_LINES_INTERFACE TABLE

AR INTERFACE TABLES

TNAME TABTYPE

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

AR_PAYMENTS_INTERFACE_ALL TABLE

AR_TAX_INTERFACE TABLE

HZ_DQM_SYNC_INTERFACE TABLE

HZ_PARTY_INTERFACE TABLE

HZ_PARTY_INTERFACE_ERRORS TABLE

RA_CUSTOMERS_INTERFACE_ALL TABLE

RA_INTERFACE_DISTRIBUTIONS_ALL TABLE

RA_INTERFACE_ERRORS_ALL TABLE

RA_INTERFACE_LINES_ALL TABLE

RA_INTERFACE_SALESCREDITS_ALL TABLE



FA INTERFACE TABLES



TNAME TABTYPE

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

FA_BUDGET_INTERFACE TABLE

FA_INV_INTERFACE TABLE

FA_PRODUCTION_INTERFACE TABLE

FA_TAX_INTERFACE TABLE


INVENTORY INTERFACE TABLES


TNAME TABTYPE

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

MTL_CC_ENTRIES_INTERFACE TABLE

MTL_CC_INTERFACE_ERRORS TABLE

MTL_CI_INTERFACE TABLE

MTL_CI_XREFS_INTERFACE TABLE

MTL_COPY_ORG_INTERFACE TABLE

MTL_CROSS_REFERENCES_INTERFACE TABLE

MTL_DEMAND_INTERFACE TABLE

MTL_DESC_ELEM_VAL_INTERFACE TABLE

MTL_EAM_ASSET_NUM_INTERFACE TABLE

MTL_EAM_ATTR_VAL_INTERFACE TABLE

MTL_INTERFACE_ERRORS TABLE



TNAME TABTYPE

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

MTL_INTERFACE_PROC_CONTROLS TABLE

MTL_ITEM_CATEGORIES_INTERFACE TABLE

MTL_ITEM_CHILD_INFO_INTERFACE TABLE

MTL_ITEM_REVISIONS_INTERFACE TABLE

MTL_ITEM_SUB_INVS_INTERFACE TABLE

MTL_OBJECT_GENEALOGY_INTERFACE TABLE

MTL_RELATED_ITEMS_INTERFACE TABLE

MTL_RESERVATIONS_INTERFACE TABLE

MTL_RTG_ITEM_REVS_INTERFACE TABLE

MTL_SECONDARY_LOCS_INTERFACE TABLE

MTL_SERIAL_NUMBERS_INTERFACE TABLE



TNAME TABTYPE

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

MTL_SO_RMA_INTERFACE TABLE

MTL_SYSTEM_ITEMS_INTERFACE TABLE

MTL_TRANSACTIONS_INTERFACE TABLE

MTL_TRANSACTION_LOTS_INTERFACE TABLE

MTL_TXN_COST_DET_INTERFACE TABLE



PO INTERFACE TABLES





TNAME TABTYPE

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

PO_DISTRIBUTIONS_INTERFACE TABLE

PO_HEADERS_INTERFACE TABLE

PO_INTERFACE_ERRORS TABLE

PO_LINES_INTERFACE TABLE

PO_REQUISITIONS_INTERFACE_ALL TABLE

PO_REQ_DIST_INTERFACE_ALL TABLE

PO_RESCHEDULE_INTERFACE TABLE

RCV_HEADERS_INTERFACE TABLE

RCV_LOTS_INTERFACE TABLE

RCV_SERIALS_INTERFACE TABLE

RCV_TRANSACTIONS_INTERFACE TABLE



BOM INTERFACE TABLES



TNAME TABTYPE

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

BOM_BILL_OF_MTLS_INTERFACE TABLE

BOM_INTERFACE_DELETE_GROUPS TABLE

BOM_INVENTORY_COMPS_INTERFACE TABLE

BOM_OP_RESOURCES_INTERFACE TABLE

BOM_OP_ROUTINGS_INTERFACE TABLE

BOM_OP_SEQUENCES_INTERFACE TABLE

BOM_REF_DESGS_INTERFACE TABLE

BOM_SUB_COMPS_INTERFACE TABLE

CST_COMP_SNAP_INTERFACE TABLE

CST_INTERFACE_ERRORS TABLE

CST_ITEM_COSTS_INTERFACE TABLE

CST_ITEM_CST_DTLS_INTERFACE TABLE

CST_PC_COST_DET_INTERFACE TABLE

CST_PC_ITEM_COST_INTERFACE TABLE



WIP INTERFACE TABLES



TNAME TABTYPE

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

WIP_COST_TXN_INTERFACE TABLE

WIP_INTERFACE_ERRORS TABLE

WIP_JOB_DTLS_INTERFACE TABLE

WIP_JOB_SCHEDULE_INTERFACE TABLE

WIP_MOVE_TXN_INTERFACE TABLE

WIP_SCHEDULING_INTERFACE TABLE

WIP_TXN_INTERFACE_ERRORS TABLE



ORDER MANAGEMENT INTERFACE TABLES



TNAME TABTYPE

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

SO_CONFIGURATIONS_INTERFACE TABLE

SO_HEADERS_INTERFACE_ALL TABLE

SO_HEADER_ATTRIBUTES_INTERFACE TABLE

SO_LINES_INTERFACE_ALL TABLE

SO_LINE_ATTRIBUTES_INTERFACE TABLE

SO_LINE_DETAILS_INTERFACE TABLE

SO_PRICE_ADJUSTMENTS_INTERFACE TABLE

SO_SALES_CREDITS_INTERFACE TABLE

SO_SERVICE_DETAILS_INTERFACE TABLE

WSH_DELIVERIES_INTERFACE TABLE

WSH_FREIGHT_CHARGES_INTERFACE TABLE

WSH_PACKED_CONTAINER_INTERFACE TABLE

SQL Tunning

• 
  
  Tuning is a search for lost time
• 
  
  You need to identify where you are losing time and why. Then you can do something about it.
• 
  
  It nearly always comes down to a poorly performing SQL statement. The question is which statement and why.
•  An execution plan is a list of steps that Oracle will follow in order to execute a SQL statement. Each step is one of a finite number of basic operations known to the database server. Even the most complex SQL statement can be broken down into a series of basic operations.
• EXPLAIN PLAN is a statement that allows you to have Oracle generate the execution plan for any SQL statement without actually executing it. You will be able to examine the execution plan by querying the plan table.
• A plan table holds execution plans generated by the EXPLAIN PLAN statement.
• The typical name for a plan table is plan_table, but you may use any name you wish.
• Create the plan table by running utlxplan.sql, located in $ORACLE_HOME/rdbms/admin.

EXPLAIN PLAN


[SET STATEMENT_ID = ]

[INTO ]

FOR

;


******************************************
The autotrace feature in SQL*Plus


SET AUTOTRACE OFF ON TRACEONLY [EXPLAIN] [STATISTICS]


At the instance level:


sql_trace = true

timed_statistics = true (optional)

In your own session:

ALTER SESSION SET sql_trace = TRUE;

ALTER SESSION SET timed_statistics = TRUE; (optional)

In another session:

SYS.dbms_system.set_sql_trace_in_session

(, , TRUE)

Invoke TKPROF from the operating system prompt like this:




tkprof

\

[explain=] \

[sys=n] [insert=] \

[record=] [sort=]
 
  Examples:


1. tkprof ora_11598.trc /tmp/myfilename sys=no

2. tkprof ora_11598.trc /tmp/myfilename explain=ap/ap

3. tkprof ora_23532.trc myfilename sort=execpu (if timed_statistics=true)

How to get the values of Bind Variables:


Dump the event 10046 using,

In Init.ora set,

event = ‘10046 trace name context forever, level 4’;

OR

SQL> oradebug setospid (taken from V$PROCESS)

OR

SQL> oradebug event 10046 trace name context forever, level 4

TKProf does not know what data type bind variables. It assumes VARCHAR2. This may cause EXPLAIN PLAN to appear as though an index is not being used when in fact it is.

Relations in Form

A relation is a Form Builder object that handles the relationship between two associated blocks. You can create a relation either:


• Implicitly with a master-detail form module
• Explicitly in the Object Navigator

Implicitly creation of relation

Once you create a master-detail form module, the Data Block Wizard automatically creates a form object that handles the relationship between two associated data blocks. This object is called a relation.

• The new relation object is created under the master data block node in the Object Navigator with default properties.
• The relation is given the following default name:MasterDataBlock_DetailDataBlock, for example ORDER_ITEM
• Triggers and program units are generated to maintain coordination between the two data blocks.

Creating a Relation Manually

1 Select the master block entry in the Object Navigator.
2 Click the Create icon. The New Relation window is displayed.
3 Specify the name of the relation.
4 Specify the name of the master block.
5 Specify the name of the detail block.
6 Choose your master delete property.
7 Choose your coordination property.
8 Specify the join condition.
9 Click OK.

Forms Basics 2

Environmental variables


FORMS60_PATH
REPORTS60_PATH
GRAPHICS60_PATH
UI_ICON
ORACLE_PATH : if no specific path is specified oracle searches in this path

Running forms from command line


IFRUN60 fmxfilename username/password@database
multiple-document interface (MDI) parent window
single-document interface (SDI) window

Modes of Operation


Enter Query mode
Normal mode

Displaying Errors

Help—>Display Error.

Types of Blocks

Data block
Control block

Data blocks: It is associated with a specific database table (or view), a stored procedure, a FROM clause query, or transactional triggers.

Control Blocks: It is not associated with a database, and its items do not relate to any columns within any database table. Its items are called control items. For example, you can create many buttons in your module to initiate certain actions and to logically group these buttons in a control block.


Producing Text Files and Documentation

Select File—>Administration—>Convert.

RELATIONS

A relation is a Form Builder object that handles the relationship between two associated blocks. You can create a relation either:

• Implicitly with a master-detail form module
• Explicitly in the Object Navigator



Using Variables in Form Builder


• PL/SQL Variables (variables using in trigger blocks)
• Form Builder Variables

Form Builder Variables Syntax of Variables

Item (data base columns) :block_name.Item_name

Global Variables :GLOBAL.variable_name

System Variables :SYSTEM.variable_name

Parameters :PARAMETER.name

Initializing Global Variables

Default_Values(‘US’,’GLOBAL.city’);

To remove Global Variables

ERASE

Form Triggers

Trigger components


1. Trigger Type
2. Trigger Code
3. Trigger Scope

Trigger Type

On-

Key-
When-
Pre-
Post-

Trigger Code

PL/SQL code
Statement
User Subprograms
Built-in Subprograms

Trigger Scope

Form Level

Block Level
Item Level

Types of triggers and how the sequence of firing in text item:


Triggers can be classified as Key Triggers, Mouse Triggers, Navigational Triggers.

Key Triggers: Key Triggers are fired as a result of Key action.

e.g. Key-next-field, Key-up, Key-Down

Mouse Triggers: Mouse Triggers are fired as a result of the mouse navigation.

e.g. When-mouse-button-pressed, when-mouse-double clicked, etc

Navigational Triggers: These Triggers are fired as a result of Navigation.

E.g. Post-Text-item, Pre-text-item.

We also have event triggers like when–new-form-instance and when-new-block-instance.

We cannot call restricted procedures like go_to(‘my_block.first_item’) in the Navigational triggers But can use them in the Key-next-item.

The Difference between Key-next and Post-Text is an very important question. The key-next is fired as a result of the key action while the post text is fired as a result of the mouse movement. Key next will not fire unless there is a key event.

The sequence of firing in a text item are as follows:

a) pre - text
b) when new item
c) key-next
d) when validate
e) post text

Sample PL/SQL Code For Basics- 3

Package

create or replace package globle_constant is

mile2km number:=1.6093;
km2mile number:=.6214;
yard2m number:=.9144;
m2yard number:=1.0936;
end globle_constant;--exec dbms_output.put_line('20mile='20*globle_constant.mile2km 'km')

/
create or replace package comm_package AUTHID CURRENT_USER is
g_comm number :=0.10; --initialized to 0.10
procedure reset_comm (p_comm in number);
end comm_package;

/
create or replace package body comm_package is
function validate_comm (p_comm in number)return boolean is
v_max number;
begin
select max(commission_pct) into v_max from employees;
if p_comm>v_max then return (FALSE);
else return (TRUE);
end if;
end validate_comm;
procedure reset_comm(p_comm in number) is
begin
if validate_comm(p_comm) then g_comm :=p_comm;--reset the global variable
else
raise_application_error (-20210,'Invalid Commission');
end if;
dbms_output.put_line (g_comm);
end reset_comm;
end comm_package;
/

create or replace procedure add_job(p_id varchar2,p_title varchar2) is
v_id varchar2(10);
v_title varchar2(100);
begin
insert into jobs(job_id, job_title)
values(p_id,p_title);
select job_id,job_title into v_id,v_title from jobs where job_id=p_id;
dbms_output.put_line ('Inserted Data is 'v_idv_title);
end add_job;
/
create or replace procedure del_job(p_id varchar2) is
begin
delete from jobs where job_id=p_id;
if sql%found then
dbms_output.put_line ('Delete Completed');
end if;
exception
when no_data_found then
dbms_output.put_line ('No Such Department Avail');
end del_job;
/




Record
declare

type emp_record_type is record(employee_id employees.employee_id%type,
last_name employees.last_name%type,
job_id employees.job_id%type,
salary employees.salary%type);
emp_record emp_record_type;
begin
select employee_id, last_name, job_id, salary
into emp_record from employees where employee_id='&ID';
dbms_output.put_line (emp_record.last_name','emp_record.job_id);
end;
 /
create or replace procedure upd_job(p_id varchar2,p_title varchar2) is

v_id varchar2(10);
v_title varchar2(100);
begin
update jobs set job_id=p_id,job_title=p_title where job_id=p_id;
select job_id,job_title into v_id,v_title from jobs where job_id=p_id;
dbms_output.put_line ('Updated Data is 'v_idv_title);
end upd_job;
/


Sub Procedure

create or replace procedure prostore(p_id employees.employee_id%type) is
procedure prosuins is
begin
insert into messages values (user,sysdate);
end prosuins;
begin
update employees set salary=salary*1.10 where employee_id=p_id;
prosuins;
end prostore;

/

Sample PL/SQL Code For Basics- 2

Instead of  Trigger:

----create table 1


create table new_emps as select employee_id,last_name,salary,department_id,email,job_id,hire_date from employees;

-----create table 2

create table new_depts as select d.department_id,d.department_name,d.location_id,sum(e.salary) tot_dept_sal from departments d,employees e where d.department_id=e.employee_id group by d.department_id,d.department_name,d.location_id

-----create view

create view emp_detail as select e.employee_id,e.last_name,e.salary,e.department_id,e.email,e.j
d.department_name,d.location_id from employees e,departments d where e.department_id=d.department_id

-----create trigger

create or replace trigger new_emp_dept
instead of insert or update or delete on emp_detail for each row

begin

if inserting then insert into new_emps values(:new.employee_id,:new.last_name,:new.salary,:new.department_id,:new.email,:new.job_id,sy update new_depts set tot_dept_sal=tot_dept_sal+:new.salary where department_id=:new.department_id;
elsif deleting then delete from new_emps where employee_id=:old.employee_id;
update new_depts set tot_dept_sal=tot_dept_sal-:old.salary
where department_id=:old.department_id;
elsif updating ('salary') then
update new_emps set salary=:new.salary where department_id=:old.department_id;
update new_depts set tot_dept_sal=tot_dept_sal+(:new.salary-:old.salary) where department_id=:old.department_id;

elsif updating ('department_id') then
update new_emps set department_id=:new.department_id where employee_id=:old.employee_id;
end if;
end new_emp_dept;

/
Trigger Statement Level

create or replace trigger trigsar

before insert on employees
begin
if (to_char(sysdate,'DY') in ('SAT','SUN')) or(to_char(sysdate,'HH24:MI') not between '09:00' and '18:00') then raise_application_error (-20500,'You Can''t Insert in EMP during work Hrs...');
end if;
end trigsar;

Loop

declare

v_value number;
v_counter number:=1;
begin
loop
if v_counter=6 or v_counter=8 then null;
else
insert into messages(result)
values(v_counter);
v_counter:=v_counter+1;
end if;
exit when v_counter>10;
end loop;
end;

/
Variable Scope

<>

declare
v_sal number(7,2) :=60000;
v_comm number(7,2) :=v_sal*.20;
v_message varchar(250) := ' eligible for commission';

begin
<>
declare
v_sal number(7,2) :=50000;
v_comm number(7,2) :=0;
v_total number(7,2) :=v_sal+v_comm;
begin
v_message := 'Cleark Not'
v_message;
v_comm :=outer.v_sal*.30;
dbms_output.put_line (V_message);
dbms_output.put_line (v_comm);
end;
end;

/
UTL File:
 
create or replace procedure sal_status (p_filedir in varchar2,p_filename in varchar2)


is
v_filehandle utl_file.file_type;
cursor emp_info is
select last_name,salary,department_id from employees order by department_id;
v_newdept employees.department%type;
v_olddept employees.department%type :=0;

begin

v_filehandle :=utl_file.fopen(p_filedir,p_filename,'w');
utl_file.putf(v_filehandle,'SALARY REPORT: GENERATED ON %S\N',SYSDATE);
utl_file.put_line(v_filehandle);
for v_emp_rec in emp_info loop
v_newdept :=v_emp_rec.department_id;
if v_newdept<>v_olddept then
utl_file.putf(v_filehandle,'Department: %s\n',v_emp_rec.department_id);
end if;
utl_file.putf(v_filehandle,'Employee:%s earns:%s\n',v_emp_rec.last_name,v_emp_rec.salary);
v_olddept:=v_newdept;
end loop;
utl_file.put_line(v_filehandle,'****** End of Report******');
utl_file.fclose(v_filehandle);
exception
when utl_file.invalid_filehandle then
raise_application_error (-20001,'Invalid File');
when utl_file.write_error then
raise_application_error (-20002,'Unable to write');
end sal_status;
/

Sample PL/SQL Code For Basics- 1

Sample PL/SQL Code For Basics

declare

v_depno departments.department_id%type;
v_location departments.location_id%type;
begin
select department_id, location_id into v_depno, v_location from departments where department_id=&depno;
dbms_output.put_line ('The Deptno is' ( v_depno));
dbms_output.put_line (v_location);
end;
/

Cursor

declare

v_id employees.employee_id%type;
v_name employees.last_name%type;
cursor emp_cursor is select employee_id,last_name from employees;
begin
open emp_cursor;
for i in 1..10 loop
fetch emp_cursor into v_id,v_name;
dbms_output.put_line (to_char(v_id)' 'v_name);
end loop;
close emp_cursor;
end;
/

Cursor Parameter

DECLARE
CURSOR emp_cursor
(p_deptno NUMBER, p_job VARCHAR2) IS SELECT employee_id, last_name FROM employees WHERE department_id = p_deptno AND job_id = p_job;
i emp_cursor%rowtype;
begin
open emp_cursor('&Depno','&Jobno');
fetch emp_cursor into i;
dbms_output.put_line (i.employee_id' 'i.last_name);
close emp_cursor;
end;
/

Exceptions:

/* Prdefined Exception*/


DECLARE
v_num number;
begin
select result into v_num from messages;
exception
when no_data_found then insert into messages values(100);
end;
/

*********

/* Non-Prdefined Exception*/

DECLARE

err_num NUMBER;
err_msg VARCHAR2(100);
emp_remaining exception;
pragma exception_init (emp_remaining, -2292);

BEGIN
delete from employees where department_id=&deptno;
EXCEPTION
when emp_remaining then
dbms_output.put_line ('Cannot Remove dept' to_char(&deptno) 'Employee Exist');
WHEN others THEN err_num := SQLCODE;
err_msg := SUBSTR(SQLERRM, 1, 100);
INSERT INTO error VALUES (err_num, err_msg);
END;
/

***********

/* User defined Exception*/

DECLARE
e_exp exception;
begin
update employees set employee_id=&id where employee_id=&ent;
IF sql%notfound then raise e_exp;
end if;
exception
when e_exp then dbms_output.put_line ('Employee not exist');
end;
/

Index By Table

declare

type emp_table_type is table of employees%rowtype
index by binary_integer;
emp_table emp_table_type;
v_count number(3):=104;
begin
for i in 100..v_count
loop
select * into emp_table(i) from employees where employee_id=i;
end loop;
for i in emp_table.first..emp_table.last
loop
dbms_output.put_line (emp_table(i).last_name);
end loop;
end;

/

Data Pump Example Code

Data Pump Used for data upload in HRMS

It has Meta Mapper,Interface Tables,PL/SQL Routines,Data Pump Engine

Meta Mapper Creates the procedure Needed To map to HR API Parameters.Check the example
1) HR_PUMP_UTILS.CREATE_BATCH_HEADER
2) HRDPP_CREATE_EMPLOYEE.INSERT_BATCH_LINES
3) HRDPP_UPDATE_EMP_ASG_CRITERIA.INSERT_BATCH_LINES

***Using API***
1) HR_EMPLOYEE_API.CREATE_EMPLOYEE
2) HR_ASSIGNMENT_API.UPDATE_EMP_ASG_CRITERIA
3) HR_PUMP_UTILS.CREATE_BATCH_HEADER
4) HRDPP_CREATE_EMPLOYEE.INSERT_BATCH_LINES
5) HRDPP_UPDATE_EMP_ASG_CRITERIA.INSERT_BATCH_LINES


Declare
L_assignment_number varchar2 (20);
l_emp_no varchar2(20);
l_batch_id number;
l_pers_user_key varchar2(100);/* while update needed for exisiting assigment,use employee id and name as identifier(unique combination) as key*/
l_asg_user_key varchar2(100);
begin
l_batch_id := hr_pump_utils.create_batch_header('S1 Batch Header','MY Company');
l_pers_user_key := 'Wise' '25-feb-1978' ' : PER USER KEY';
l_asg_user_key := 'Wise' feb-1978' ' : ASG USER KEY';
hrdpp_create_employee.insert_batch_lines(
p_batch_id => l_batch_id
,p_person_user_key => l_pers_user_key
,p_assignment_user_key => l_asg_user_key
,p_hire_date => trunc(sysdate)
,p_employee_number => l_emp_no
,p_last_name => 'WISE'
,p_sex => 'M'
,p_date_of_birth => '25-feb-1978');
commit;
end;

declare
l_organization_name varchar2(20);
l_language_code varchar2(20);
l_batch_id number;
l_asg_user_key varchar2(100);
L_SPECIAL_CEILING_ID NUMBER;
L_P_CON_SEG_USER_NAME VARCHAR2(50);
L_PEOPLE_GROUP_ID NUMBER;
begin

l_batch_id := hr_pump_utils.create_batch_header('Assign Batch Header','My Company');
l_asg_user_key := 'Wise' '25-feb-1978' ' : ASG USER KEY';
L_SPECIAL_CEILING_ID := HR_API.G_NUMBER;
hrdpp_update_emp_asg_criteria.insert_batch_lines
(p_batch_id => l_batch_id
,P_EFFECTIVE_DATE =>trunc(sysdate)
,P_DATETRACK_UPDATE_MODE =>'CORRECTION'
,P_PEOPLE_GROUP_ID => L_PEOPLE_GROUP_ID
,P_SPECIAL_CEILING_STEP_ID => L_SPECIAL_CEILING_ID
,P_ASSIGNMENT_USER_KEY => l_asg_user_key
,P_CON_SEG_USER_NAME => L_P_CON_SEG_USER_NAME);

commit;
end;

declare

l_assignment_number varchar2(20);
l_emp_no varchar2(20);
l_batch_id number;
l_pers_user_key varchar2(100);
l_asg_user_key varchar2(100);
l_organization_name varchar2(20);
l_language_code varchar2(20);
L_SPECIAL_CEILING_ID NUMBER;
L_P_CON_SEG_USER_NAME VARCHAR2 (50);
L_PEOPLE_GROUP_ID NUMBER;
begin
l_batch_id := hr_pump_utils.create_batch_header('S1 Batch Header','My Company');
l_pers_user_key := 'Aise' '26-feb-1978' ' : PER USER KEY';
l_asg_user_key := 'Aise' '26-feb-1978' ' : ASG USER KEY';
hrdpp_create_employee.insert_batch_lines(
p_batch_id => l_batch_id
,p_person_user_key => l_pers_user_key
,p_assignment_user_key => l_asg_user_key
,p_hire_date => trunc(sysdate)
,p_employee_number => l_emp_no
,p_last_name => 'Aise'
,p_sex => 'M'
,p_date_of_birth => '26-feb-1978');
L_SPECIAL_CEILING_ID := HR_API.G_NUMBER;
hrdpp_update_emp_asg_criteria.insert_batch_lines(
p_batch_id => l_batCH_id
,P_EFFECTIVE_DATE =>trunc(sysdate)
,P_DATETRACK_UPDATE_MODE =>'CORRECTION'
,P_PEOPLE_GROUP_ID => L_PEOPLE_GROUP_ID
,P_SPECIAL_CEILING_STEP_ID => L_SPECIAL_CEILING_ID
,P_ASSIGNMENT_USER_KEY => l_asg_user_key
,P_CON_SEG_USER_NAME => L_P_CON_SEG_USER_NAME);

Commit;
End;
Declare

l_assignment_number varchar2(20);
l_emp_no varchar2(20);
l_batch_id number;
l_pers_user_key varchar2(100);
l_asg_user_key varchar2(100);
l_organization_name varchar2(20);
l_language_code varchar2(20);
L_SPECIAL_CEILING_ID NUMBER;
L_P_CON_SEG_USER_NAME VARCHAR2 (50);
L_PEOPLE_GROUP_ID NUMBER;

begin
l_batch_id := hr_pump_utils.create_batch_header('S1 Batch Header','My Company');
l_pers_user_key := 'Bise' '20-feb-1978' ' : PER USER KEY';
l_asg_user_key := 'Bise' '20-feb-1978' ' : ASG USER KEY';
hrdpp_create_employee.insert_batch_lines(p_batch_id => l_batch_id
,p_person_user_key => l_pers_user_key,
p_assignment_user_key => l_asg_user_key
,p_hire_date => trunc(sysdate)
,p_employee_number => l_emp_no
,p_last_name => 'Bise'
,p_sex => 'M'
,p_date_of_birth => '20-feb-1978');
L_SPECIAL_CEILING_ID := HR_API.G_NUMBER;
hrdpp_update_emp_asg_criteria.insert_batch_lines(
p_batch_id => l_batCH_id
,P_EFFECTIVE_DATE =>trunc(sysdate)
,P_DATETRACK_UPDATE_MODE =>'CORRECTION'
,P_PEOPLE_GROUP_ID => L_PEOPLE_GROUP_ID
,P_SPECIAL_CEILING_STEP_ID => L_SPECIAL_CEILING_ID
,P_ASSIGNMENT_USER_KEY => l_asg_user_key
,P_PAY_BASIS_NAME => 'Exempt'
,P_CON_SEG_USER_NAME => L_P_CON_SEG_USER_NAME);
commit;
end;
declare
l_assignment_number varchar2(20);
l_emp_no varchar2(20);
l_batch_id number;
l_pers_user_key varchar2(100);
l_asg_user_key varchar2(100);
l_organization_name varchar2(20);
l_language_code varchar2(20);
L_SPECIAL_CEILING_ID NUMBER;
L_P_CON_SEG_USER_NAME VARCHAR2(50);
L_PEOPLE_GROUP_ID NUMBER;

begin

l_batch_id := hr_pump_utils.create_batch_header('S1 Batch Header','My Company');
l_pers_user_key := 'Bise' '20-feb-1978' ' : PER USER KEY';
l_asg_user_key := 'Bise' '20-feb-1978' ' : ASG USER KEY';

hrdpp_create_employee.insert_batch_lines(
p_batch_id => l_batch_id
,p_person_user_key => l_pers_user_key
,p_assignment_user_key => l_asg_user_key
,p_hire_date => trunc(sysdate)
,p_employee_number => l_emp_no
,p_last_name => 'Bise'
,p_sex => 'M'
,p_date_of_birth => '20-feb-1978');

L_SPECIAL_CEILING_ID := HR_API.G_NUMBER;

hrdpp_update_emp_asg_criteria.insert_batch_lines(
p_batch_id => l_batCH_id
,P_EFFECTIVE_DATE =>trunc(sysdate)
,P_DATETRACK_UPDATE_MODE =>'CORRECTION'
,P_PEOPLE_GROUP_ID => L_PEOPLE_GROUP_ID
,P_SPECIAL_CEILING_STEP_ID => L_SPECIAL_CEILING_ID
,P_ASSIGNMENT_USER_KEY => l_asg_user_key
,P_JOB_NAME => 'HR009.HR CONSULTANT'
,P_PAY_BASIS_NAME => 'Exempt'
,P_CON_SEG_USER_NAME => L_P_CON_SEG_USER_NAME);

commit;

end;


declare

l_assignment_number varchar2(20);
l_emp_no varchar2(20);
l_batch_id number;
l_pers_user_key varchar2(100);
l_asg_user_key varchar2(100);
l_organization_name varchar2(20);
l_language_code varchar2(20);
L_SPECIAL_CEILING_ID NUMBER;
L_P_CON_SEG_USER_NAME VARCHAR2(50);
L_PEOPLE_GROUP_ID NUMBER;
begin

l_batch_id := hr_pump_utils.create_batch_header('S1 Batch Header','My Company');
l_pers_user_key := 'Sise' '2-feb-1978' ' : PER USER KEY';
l_asg_user_key := 'Sise' '2-feb-1978' ' : ASG USER KEY';

hrdpp_create_employee.insert_batch_lines(

p_batch_id => l_batch_id
,p_person_user_key => l_pers_user_key
,p_assignment_user_key => l_asg_user_key
,p_hire_date => trunc(sysdate)
,p_employee_number => l_emp_no
,p_last_name => 'Sise'
,p_sex => 'M'
,p_date_of_birth => '2-feb-1978');

L_SPECIAL_CEILING_ID := HR_API.G_NUMBER;

hrdpp_update_emp_asg_criteria.insert_batch_lines(

p_batch_id => l_batCH_id
,P_EFFECTIVE_DATE =>trunc(sysdate)
,P_DATETRACK_UPDATE_MODE =>'CORRECTION'
,P_PEOPLE_GROUP_ID => L_PEOPLE_GROUP_ID
,P_SPECIAL_CEILING_STEP_ID => L_SPECIAL_CEILING_ID
,P_ASSIGNMENT_USER_KEY => l_asg_user_key
,P_ORGANIZATION_NAME => 'My Company'
,P_JOB_NAME => 'AA900.Administrative Assistant'
,P_POSITION_NAME => 'AA910.EXECUTIVE ASSISTANT TO CEO'
,P_PAY_BASIS_NAME => 'Exempt'
,P_CON_SEG_USER_NAME => L_P_CON_SEG_USER_NAME);

commit;

end;

CUSTOM Package

The CUSTOM package contains the following functions and procedure:


 CUSTOM.ZOOM_AVAILABLE

 CUSTOM.STYLE

 CUSTOM.EVENT

CUSTOM.ZOOM_AVAILABLE

Summary: function custom.zoom_available return BOOLEAN;

Description:

If Zoom is available for this block, then return TRUE; otherwise return FALSE. Always test for the form and block name. Refer to the SYSTEM variables for form name and block name in your code and branch accordingly. The module name of your form must match the form file name. By default this routine must return FALSE.

Example:

The following example enables Zooms in the following places:

Form: FNDSCAUS, Block USER and
Form: FNDCPMCP, Block PROCESS
FUNCTION zoom_available RETURN BOOLEAN IS
form_name VARCHAR2(30) := NAME_IN(’system.current_form’);
block_name VARCHAR2(30) := NAME_IN(’system.cursor_block’);
BEGIN
IF (form_name = ’FNDSCAUS’ AND block_name = ’USER’) OR
(form_name = ’FNDCPMCP’ AND block_name = ’PROCESS’)THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END zoom_available;

CUSTOM.STYLE


Summary: function custom.style(event_name varchar2) return integer;

Description:

This function allows you to determine the execution style for a product–specific event if custom execution styles are supported for that product–specific event (many product–specific events do not support custom execution styles).

You can choose to have your code execute before, after, or in place of the code provided in Oracle Applications. See the User’s Guide for your Oracle Applications product for a list of events that are available through this interface. Note that some product–specific events may not support all execution styles. CUSTOM.STYLE does not affect generic form events or Zoom. Any event that returns a style other than custom.standard must have corresponding code in custom.event which will be executed at the time specified.


The following package variables should be used as return values:

 custom.before
 custom.after
 custom.override
 custom.standard

By default this routine must return custom.standard (which means that there is no custom execution style code).

Attention:

Oracle Corporation reserves the right to pass additional values for event_name to this routine, so all code must be written to branch on the specific event_name passed.

Example:

The following example sets up the MY_PRICING_EVENT event to have the Override execution style.

Begin

if event_name = ’MY_PRICING_EVENT’ then
return custom.override;
else
return custom.standard;
end if;
end style;

CUSTOM.EVENT

Summary: procedure custom.event(event_name varchar2);

Description

This procedure allows you to execute your code at specific events. Always test for event name, then for form and block name within that event. Refer to the SYSTEM variables for form name and block name in your code and branch accordingly. The module name of your form must match the form file name. By default, this routine must perform ”null;”.

Attention:

Oracle Corporation reserves the right to pass additional values for event_name to this routine, so all code must be written to branch on the specific event_name passed.

Example:

The following example contains logic for a Zoom, a product–specific event, and a generic form event. The Zoom event opens a new session of a form and passes parameter values to the new session. The parameters already exist in the form being opened, and the form function has already been defined and added to the menu (without a prompt, so it does not appear in the Navigator).

procedure event(event_name varchar2) is
form_name varchar2(30) := name_in(’system.current_form’);
block_name varchar2(30) := name_in(’system.cursor_block’);
param_to_pass1 varchar2(255);
param_to_pass2 varchar2(255);
begin
if (event_name = ’ZOOM’) then
if (form_name = ’DEMXXEOR’ and block_name = ’ORDERS’) then
/* The Zoom event opens a new session of a form and
passes parameter values to the new session. The
parameters already exist in the form being opened:*/
param_to_pass1 := name_in(’ORDERS.order_id’);
param_to_pass2 := name_in(’ORDERS.customer_name’);
fnd_function.execute(FUNCTION_NAME=>’DEM_DEMXXEOR’,
OPEN_FLAG=>’Y’,
SESSION_FLAG=>’Y’,
OTHER_PARAMS=>’ORDER_ID=”’param_to_pass1’” CUSTOMER_NAME=”’param_to_pass2’”’);

/* all the extra single and double quotes account for any spaces that might be in the passed values */

end if;
elsif (event_name = ’MY_PRICING_EVENT’) then
/*For the product–specific event MY_PRICING_EVENT, call a custom pricing routine */
get_custom_pricing(’ORDERS.item_id’, ’ORDERS.price’);
elsif (event_name = ’WHEN–VALIDATE–RECORD’) then
if (form_name = ’APXVENDR’ and block_name = ’VENDOR’) then

/* In the WHEN–VALIDATE–RECORD event, force the value of a Vendor Name field to be in uppercase letters */

copy(upper(name_in(’VENDOR.NAME’)), ’VENDOR.NAME’);
end if;
else
null;
end if;
end event;
end custom;

Always use FND_FUNCTION.EXECUTE to open a new session of a form. Do not use CALL_FORM or OPEN_FORM. The form function must already be defined with Oracle Application Object Library and added to the menu (without a prompt, if you do not want it to appear in the Navigator).

When to Use the CUSTOM Library

When to Use the CUSTOM Library


There are several main cases for which you can code logic using the CUSTOM library. Each of these cases must be coded differently.

1. Zoom

The addition of user–invoked logic on a per–block basis. A Zoom typically consists of opening another form and (optionally) passing parameter values to the opened form through the Zoom logic.

2. Logic for generic events

Augment Oracle Applications logic for certain generic form events such as WHEN–NEW–FORM–INSTANCE or WHEN–VALIDATE–RECORD.

3. Logic for product–specific events

Augment or replace Oracle Applications logic for certain product–specific events that enforce business rules.

4. Custom entries for the Special menu

Add entries to the Special menu for Oracle Applications forms, such as an entry that opens a custom form.

5. Setting visual attributes

Use the CUSTOM library to change the visual attributes of Oracle Applications fields at runtime. Use the Oracle Forms built–in SET_VA_PROPERTY to set the properties of the CUSTOM1–CUSTOM5 visual attributes, and then use APP_ITEM_PROPERTY2.SET_PROPERTY to apply the visual attribute to an item at runtime.





To code Zooms into the CUSTOM library:

• Add a branch to the CUSTOM.ZOOM_AVAILABLE function that specifies the form and block where you want a user to be able to invoke Zoom.

• Add a branch to the CUSTOM.EVENT procedure for the ZOOM event. Inside that branch, specify the form and block where you want a user to be able to invoke Zoom. Add the logic you want to occur when the user invokes Zoom.

Supporting Multiple Zoom Events for a Block

As of Release 11i, Oracle Applications provides a referenced list of values (LOV) and corresponding referenced parameter for Zooms in all forms built using the TEMPLATE form (including custom forms). They are the following:

• List of values: APPCORE_ZOOM.

• Parameter: APPCORE_ZOOM_VALUE Use the LOV and parameter to provide users with an LOV where you have more than one Zoom from a particular block.

To code the Zoom LOV into the CUSTOM library:

In the CUSTOM library (within your ZOOM event code):

• Create a record group and populate it with names and values of available Zooms for the block.

• Attach the record group to the APPCORE_ZOOM LOV.

• Call show_lov to display the LOV to the user.

• If user picks a Zoom, the value is returned into the APPCORE_ZOOM_VALUE parameter in the form.

• Retrieve the parameter value and branch your Zoom code accordingly.

Custom.PLL

Customizing Oracle Applications with the CUSTOM Library


The CUSTOM library allows extension of Oracle Applications without modification of Oracle Applications code.
You can use the CUSTOM library for customizations such as Zoom (such as moving to another form and querying up specific records), enforcing business rules (for example, vendor name must be in uppercase letters), and disabling fields that do not apply for your site.

You write code in the CUSTOM library, within the procedure shells that are provided. All logic must branch based on the form and block for which you want it to run. Oracle Applications sends events to the CUSTOM library. Your custom code can take effect based on these events.

Attention:
The CUSTOM library is provided for the exclusive use of Oracle Applications customers. The Oracle Applications products do not supply any predefined logic in the CUSTOM library other than the procedure shells described here.

Writing Code for the CUSTOM Library

 The CUSTOM library is an Oracle Forms PL/SQL library. It allows you to take full advantage of all the capabilities of Oracle Forms Developer, and integrate your code directly with Oracle Applications without making changes to Oracle Applications code. The as–shipped CUSTOM library is located in the AU_TOP/resource directory (or platform equivalent).

 After you write code in the CUSTOM procedures, compile and generate the library using Oracle Forms. Then place this library into $AU_TOP/resource directory (or platform equivalent). Subsequent invocations of Oracle Applications will then run this new code.

 The specification of the CUSTOM package in the CUSTOM library cannot be changed in any way. You may add your own packages to the CUSTOM library, but any packages you add to this library must be sequenced after the CUSTOM package.

Coding Considerations and Restrictions

Be aware of the open form environment in which Oracle Applications operate. Also, each running form has its own database connection.

The following considerations and restrictions apply to the CUSTOM library and any libraries you attach to CUSTOM:

• You cannot use any SQL in the library. However, you can use a record group to issue SELECT statements, and you can use calls to stored procedures for any other DML operations such as updates, inserts, or deletes.

• Oracle Forms global variables in your code are visible to all running forms.

Attaching Other Libraries to the CUSTOM Library

You may attach other libraries to the CUSTOM library. However, you cannot attach the APPCORE library to CUSTOM because it would cause a recursion problem (because CUSTOM is attached to APPCORE).

As of Oracle Applications Release 11i, you may attach the APPCORE2 library to CUSTOM. The APPCORE2 library duplicates most APPCORE routines with the following packages:

• APP_ITEM_PROPERTY2
• APP_DATE2
• APP_SPECIAL2

These packages contain the same routines as the corresponding APPCORE packages.

Within the CUSTOM library, you are free to write almost any code supported by Oracle Forms Developer.

Attention:

To invoke another form, use the function security routines in the FND_FUNCTION package. Do not use the CALL_FORM built–in since the Oracle Applications libraries do not support it.

Events Passed to the CUSTOM Library:

The CUSTOM library receives two different kinds of events, generic and product–specific. Generic events are common to all the forms in Oracle Applications. These events are:

• WHEN–FORM–NAVIGATE
• WHEN–NEW–FORM–INSTANCE
• WHEN–NEW–BLOCK–INSTANCE
• WHEN–NEW–RECORD–INSTANCE
• WHEN–NEW–ITEM–INSTANCE
• WHEN–VALIDATE–RECORD
• SPECIALn (where n is a number between 1 and 45)
• ZOOM
• EXPORT
• KEY–Fn (where n is a number between 1 and 8)

Valueset $Flex$

1. Create executable

Here the application name (“Application Object Library”) is the application (FND TOP) where the report (Execution file name: “CHUMMA.rdf”) resides.

2. Create validation set

While creating this value set “myset2” the set is a list of values from the CHUMMA table. But the parameter value where we are going to use this value set is depended on the previous parameter value. If first parameter value is 5 then this list of value should show only after 5. For that we should write a condition while creating this value set. The condition is

Here “:$FLEX$” is the keyword. “A” is the previous parameter name. We can also use “myset1” (parameter’s value set name). But while using the “myset1” it won’t depend on our desired parameter. Instead of the last parameter where value set (myset1) is used.

3. Create program

4. Run the request

A’s value set is myset1 C’s value set is myset1

B’s value set is myset1 with the following condition

A’s value set is myset1 C’s value set is myset1

B’s value set is myset1 with the following condition

Reports passing parameter

Create a manual data model with a query…select * from emp where sal between :low_sal and high_sal………

 
do proper layout model….
 

Give values

Go to sys admin in apps Register the concurrent executable and program

While Definig Concurrent Program,Go to Parameters Tab

copy Both of  token name from the object navigator…(Reports).Save it in as 2 different token id in parameter tab

save it

Go to Application Developer to create valuesets

Now while submiting reports these valuesets can be used.

Report Triggers

Trigger types in Report:


1. Report Trigger.
2. Data Model Trigger.
3. Layout Trigger.

Report Trigger:

This Enable you to use PL/SQL functions in the Reports during execution and formatting.

You can

1) Customize the Formatting of your report.

2) Initialize Certain Tasks Before Running Report.

3) Access The Database.

4) Create Dynamic WHERE Clause.

Using Report Triggers.

Types:

Types Fires

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

1. BEFORE PARAMETER FORM.
Before Runtime Parameter form is displayed.

2. AFTER PARAMETER FORM.
After Parameter form is displayed.

3. BEFORE REPORT.
After Queries are parsed before records are fetched.

4. BETWEEN PAGES.
For every Pages except first page.

5. AFTER REPORT.
After exiting Live Previewer.


Creating Dynamic Where Clause:

We Can Create Dynamic Where Clause Depending Upon the Value Entered by The User in the Runtime.

Here two possibilities are there

1. Bind Parameter is Not Null

2. Bind Parameter is Null

Example:-

1. Query:- SELECT * FROM EMP &PWHERE;

2. Create one User Parameter P_DNO. Set Data type to NUMBER and Width to 20.

3. Write Following Code in AFTER PARAMETER FORM Trigger.

FUNCTION AFTERPFORM RETURN BOOLEN IS
BEGIN
IF : P_DNO IS NULL THEN
:PWHERE := ‘’;
ELSE
:PWHERE := ‘WHERE DEPTNO=:P_DNO’;
END IF;
END;

Data Model Trigger:

It Consists Of

• PL/SQL Group Filter Triggers.
• Parameter Validation Triggers.

Using PL/SQL Group Filter:

Can be used for

• Restricting the Records in a Specific Group, depending on other processing in the Report.

• Performing PL/SQL Processing for each Record in a Report.

• Restrict The Value Of Lower Group in the Hierarchy.


Example:-

1. Query:-SELECT * FROM EMP;

2. Set Property Palette of G_EMP as Given Below:

Group Filter : PL/SQL Filter.

3. Write Following Query in Group Filter Program Unit Editor

FUNCTION G_EMPGROUPFILTER RETURN BOOLEAN IS
BEGIN
IF: DEPTNO= 10 THEN
RETURN (TRUE);
ELSE
RETURN (FALSE);
END IF;
END;


Validating a Parameter Value:

You Can Validate both System and User Parameters Using Validation Trigger Property.

Example:-

Query: - SELECT * FROM EMP WHERE DEPTNO=: P_DNO;

Select Validation Trigger Property in the Property Palette of User Parameter P_DNO and Write Following Code:

FUNCTION P_DNOVALIDTRIGGER RETURN BOOLEAN IS
BEGIN
IF : P_DNO IN (10,20,30) THEN
RETURN (TRUE);
ELSE
RETURN (FALSE);
END IF;
END;


Layout Model Trigger:

It Consists Of Format Triggers.

Used For:

1. Suppressing Entire Layout for a Query.

2. Suppressing Individual Records.


Example:-

1. Query:- SELECT * FROM EMP E, DEPT D WHERE E.DEPTNO(+)=D.DEPTNO;

2. Create a Summary Column to count the number of Members in a Particular Dept.

3. In the Layout Model, Create a Piece of Boiler Plate Text ‘NO EMPLOYEES’.

4. Write The Format Trigger on Column Heading:

FUNCTION RETURN BOOLEAN IS
BEGIN
IF :CS_1=0 THEN
RETURN (FALSE);
ELSE
RETURN (TRUE);
END IF;
END;

5. Write the Format Trigger on Boiler Plate Text Created:

FUNCTION RETURN BOOLEAN IS
BEGIN
IF :CS_1=0 THEN
RETURN (TRUE);
ELSE
RETURN (FALSE);
END IF;
END;

6. Compile the Trigger Codes and Run The Report.

Attaching a new Form

Login to SQL PLUS and then check for any table that is used in the application. If not then create a new table as required. In this case, we take the EMP table.

a. First Describe the table using the command DESC.

b. Then GRANT ALL Privileges to User APPS.

c. Connect as APPS in SQL *PLUS.

d. Create a Synonym EMP For this User.

e. Commit and exit SQL.

Now, we have a table EMP for the User APPS from the Schema SCOTT.
Now exit the SQL PLUS editor.
The next Step in the process is to create a TOP Directory.


TOP Directory is referred to a directory, which resides in the directory <$>:/ORACLE/PRODAPPL/

Where $ is the root directory where Oracle APPS is installed.

This Path is in itself called as ‘APPLTOP’.

In this case, we create a new top folder in the APPLTOP Directory. We name the folder as EMP_TOP.
The new folder looks like this :

Kindly note the path for the location of this folder, Its D:\Oracle\Prodappl

Now the next step is to create the Sub-Folders in our Top folder.

The following folders are required to be created inside the top folder.



These are Listed as below:


EMP_TOP\11.5.0\
Admin
Bin
Def
Forms -> US
Graphs
Help
HTML
Java
LIB
Log
MDS
MESG
OUT
Patch
Reports -> US
SQL
XML

The next step in this process is to register the created EMP_TOP Folder with APPS.

For this, Open the Environment File PROD.CMD (in Windows) or PROD.ENV (in LINUX).

The location of this file is in the APPL_TOP Folder.

To open it, Right Click on it and then Hit EDIT.

The File opens in a Notepad.

Once the file opens, search for the text “APPL_TOP”, the text will appear as below:


REM

REM APPL_TOP is the top-level directory for Oracle Applications.

REM

set APPL_TOP=D:\oracle\prodappl


In this file, just below the above text, we need to ‘SET’ our ‘TOP Folder’.

The Text to be added looks like this :



 Set EMP_TOP= D:\Oracle\Prodappl\EMP_TOP\11.5.0



Now the modified text in the Environment File looks like this:


REM

REM APPL_TOP is the top-level directory for Oracle Applications.

REM

set APPL_TOP=D:\oracle\prodappl

set EMP_TOP= D:\Oracle\Prodappl\EMP_TOP\11.5.0

________________________________________________________________________

Now Save and Close this File.

The next step is to Source this PROD.CMD file.

Sourcing: It is the process of running the ‘.CMD’ file in DOS Mode.

For doing this, get into command prompt and then go to

D:\Oracle\Prodappl\

Once in this screen, type as Prod.cmd. This will source the Prod.cmd file and bring into effect the changes that we made in it. It will set the EMP_TOP folder as the application top folder for our form.

To check whether the emp_top is set as application top folder, type in the command prompt as

CD %emp_top% and then hit enter.

This will take to the Emp_top Folder that was set in the Prod.cmd environment variable file. The screen for this looks as shown:

The next step is to register the EMP_TOP directory in our Registry.

Caution: => This step is needed only if the Operating System is Windows.

=> This step is not required for Linux Operating Systems.

 Always take a Backup of registry before modifying it, as it is the most

sensitive part of the Operating System. Any alteration may damage the entire

operating system



For doing this, Open the registry editor and then browse to

HKLM\Software\Oracle\Application\11.5.0\Context_name(Prod_dev210)

On the right hand side, we have to create a new string value by right clicking on it and then naming it as ‘EMP_TOP ‘ To that new string, we have to specify the value of ‘Emp_TOP’. The screen for doing this looks as below:

Once done, just click ‘OK’ to the screen

so this completes the modification of the registry and also the registration of our EMP_TOP folder with the windows Operating System.

Now close the registry editor.



The next step is to copy some of the required forms from a Source to our Emp_TOP folder.

We copy the APSTAND.fmb and Template.fmb files from D:\Oracle\prodappl\AU\11.5.0\forms\US and then place it in our folder at D:\Oracle\prodappl\EMP_TOP\11.5.0\forms\US

This step is supposed to be done every time we register our application top folder.



The next step is to copy all the *.pll files from the resource folder to another folder, but this is a One time process and does not require to be done every time we register our Application top folder.

Source: D:\Oracle\prodappl\AU\11.5.0\Resource

Destination: D:\Oracle\Prodora\8.0.6\forms60

Once copied, it marks the completion of this step.
The next step in the process is to create a form from the template that was copied on to our Application Top folder.


For doing this, open the Forms Builder and select the option of ‘Build a form based on a Template’.

The screen looks as below:

When the window opens, select the template and then open the Object Navigator.

It looks as shown below:

Save the form as Test form as soon as it opens.

Now follow the steps in the order as listed below:

 Delete the Child items pertaining to Object Groups.

 Delete the child items of Canvas.

 Delete the child items from Data Block.

Now, Open the Data Block and then use the Data Block Wizard to create a normal Form with the ‘EMP’ table.

In the Pre-Form Trigger, Change the ‘Template_form’ to Our form, that is, ‘test_form’ and also the ‘FND’ to our Top folder, that is, ‘EMP_TOP’ folder.

Save and Close it.

Now, navigate to Program Units and under that locate App_Custom(Package Body). In that select the First If Condition Block and then change every instance of ‘wnd=’ To “wnd=’BLOCKNAME’”

The actual code is as below:
if (wnd = '') then


app_window.close_first_window;

elsif (wnd = '') then

--defer relations

--close related windows

null;

elsif (wnd = '') then

--defer relations

--close related windows

null;

end if;

________________________________________________________________________



Now the modified code looks as shown below:



________________________________________________________________________

if (wnd = 'BLOCKNAME') then

app_window.close_first_window;

elsif (wnd = 'BLOCKNAME') then

--defer relations

--close related windows

null;

elsif (wnd = 'BLOCKNAME') then

--defer relations

--close related windows

null;

end if;

________________________________________________________________________



Now, Compile and then close the window.



Now, compile the form and then close it.

P.S. Do not run the form, it will give errors.