HOW TO CREATE, DROP, DISABLE, AND ENABLE A PRIMARY KEY

 how to create, drop, disable, and enable a primary key in Oracle with examples.

WHAT IS A PRIMARY KEY ?

In Oracle, a primary key is a single field or combination of fields that uniquely defines a record. None of the fields that are part of the primary key can contain a null value. A table can have only one primary key.

NOTE

In Oracle, a primary key can not contain more than 32 columns.
A primary key can be defined in either a CREATE TABLE statement or an ALTER TABLE statement.

CREATE PRIMARY KEY - USING CREATE TABLE STATEMENT

You can create a primary key in Oracle with the CREATE TABLE statement.

SYNTAX

The syntax to create a primary key using the CREATE TABLE statement in Oracle/PLSQL is:

CREATE TABLE table_name
(
  column1 datatype null/not null,
  column2 datatype null/not null,
  ...
  
  CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ... column_n)
);

EXAMPLE

Let's look at an example of how to create a primary key using the CREATE TABLE statement in Oracle:

CREATE TABLE supplier
(
  supplier_id numeric(10) not null,
  supplier_name varchar2(50) not null,
  contact_name varchar2(50),
  CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
);

In this example, we've created a primary key on the supplier table called supplier_pk. It consists of only one field - the supplier_id field.
We could also create a primary key with more than one field as in the example below:

CREATE TABLE supplier
(
  supplier_id numeric(10) not null,
  supplier_name varchar2(50) not null,
  contact_name varchar2(50),
  CONSTRAINT supplier_pk PRIMARY KEY (supplier_id, supplier_name)
);

CREATE PRIMARY KEY - USING ALTER TABLE STATEMENT

You can create a primary key in Oracle with the ALTER TABLE statement.

SYNTAX

The syntax to create a primary key using the ALTER TABLE statement in Oracle/PLSQL is:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ... column_n);

EXAMPLE

Let's look at an example of how to create a primary key using the ALTER TABLE statement in Oracle.

ALTER TABLE supplier
ADD CONSTRAINT supplier_pk PRIMARY KEY (supplier_id);

In this example, we've created a primary key on the existing supplier table called supplier_pk. It consists of the field called supplier_id.
We could also create a primary key with more than one field as in the example below:

ALTER TABLE supplier
ADD CONSTRAINT supplier_pk PRIMARY KEY (supplier_id, supplier_name);

DROP PRIMARY KEY

You can drop a primary key in Oracle using the ALTER TABLE statement.

SYNTAX

The syntax to drop a primary key using the ALTER TABLE statement in Oracle/PLSQL is:

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

EXAMPLE

Let's look at an example of how to drop a primary key using the ALTER TABLE statement in Oracle.

ALTER TABLE supplier
DROP CONSTRAINT supplier_pk;

In this example, we're dropping a primary key on the supplier table called supplier_pk.

DISABLE PRIMARY KEY

You can disable a primary key in Oracle using the ALTER TABLE statement.

SYNTAX

The syntax to disable a primary key using the ALTER TABLE statement in Oracle/PLSQL is:

ALTER TABLE table_name
DISABLE CONSTRAINT constraint_name;

EXAMPLE

Let's look at an example of how to disable a primary using the ALTER TABLE statement in Oracle.

ALTER TABLE supplier
DISABLE CONSTRAINT supplier_pk;

In this example, we're disabling a primary key on the supplier table called supplier_pk.

ENABLE PRIMARY KEY

You can enable a primary key in Oracle using the ALTER TABLE statement.

SYNTAX

The syntax to enable a primary key using the ALTER TABLE statement in Oracle/PLSQL is:

ALTER TABLE table_name
ENABLE CONSTRAINT constraint_name;

EXAMPLE

Let's look at an example of how to enable a primary key using the ALTER TABLE statement in Oracle.

ALTER TABLE supplier
ENABLE CONSTRAINT supplier_pk;

In this example, we're enabling a primary key on the supplier table called supplier_pk.

Query to Define Value Set for Customer

Query to Define Value Set for Customer

select RTRIM (RPAD (SUBSTRB (party_name, 1, 50), 36)) customer_name from hz_parties
where
party_type = 'ORGANIZATION'
and party_name <> '%'
and party_name not like 'cancel%'
Order by party_name

Sql Functions

Aggregate  functions

• Aggregate functions return a single result row based on groups of rows, rather than on single rows.
• Aggregate functions can appear in select lists and in ORDER BY and HAVING clauses.

They are commonly used with the GROUP BY clause in a SELECT statement, where Oracle divides the rows of a queried table or view into groups.
All aggregate functions except COUNT(*)and GROUPING ignore nulls.

These are Aggregate functions
SUM()
SELECT SUM(salary) as Sum_Sal FROM emp;
AVG()
SELECT AVG(salary) as Avg_Sal FROM emp;
Max()
MAX returns maximum value of expression.
SELECT Max(salary) as Max_Sal FROM emp;
Min()
Min returns minimum value of expression.
SELECT Min(salary) as Min_Sal FROM emp;
Count()
Count returns the number of rows in the query.
SELECT COUNT(*) as total_rec FROM emp;
Example of Aggregate Function using GROUP BY:
To return the minimum and maximum salaries for each department in the emp table,we can use:group by clause
SELECT department_id,MIN(salary),MAX(salary)
FROM emp
GROUP BY dept_id;
To return the minimum and maximum salaries for each department in the emp table where minimum salary should be more than 5000
SELECT department_id, MIN(salary), MAX (salary)
FROM emp
WHERE MIN(salary) > 5000
GROUP BY dept_id;

Character Functions

• It calculates the ASCII equivalent of the first character of the given input string.

ASCII(<Character>)
ascii('A')     would return 65
ascii('a')     would return 97
ascii('a8')     would return 97

CHR(<Character>)
Returns the character equivalent of the given integer.
Example
SELECT CHR(65), CHR(97) FROM dual;
Output : A a

CONCAT(<string1>,<string2>)
This function returns String2 appended to String1.
Example:
SELECT CONCAT('Fname', 'Lname') Emp_name FROM emp;

INITCAP(<String>)
This function returns String with the first character of each word in upper case and rest of all in lower case.
Example:
SELECT INITCAP('oracle tutorial') FROM Dual;
Output :  Oracle Tutorial

instr( string1, string2 [, start_position [,  nth_Appearance ] ] ):
string1 is the string to search.

string2 is the substring to search for in string1.

start_position is the position in string1 from where the search will start. This argument is optional. If not mentioned, it defaults to 1.
The first position in the string is 1. If the start_position is negative, the function counts backward direction

nth_appearance is the nth appearance of string2. This is optional. If not defined, it defaults to 1.

Example
SELECT INSTR('Character','r',1,1) POS1
, INSTR('Character','r',1,2) POS2
, INSTR('Character','a',-1,2) POS3
,INSTR('character','c',) POS4
FROM Dual;
pos1 pos2 pos3 pos4
  4          9          3        6

LENGTH(<Str>)
Returns length of a string
secect length('Sql Tutorial') as len from dual;
O/p len
     12
LOWER(<Str>)
This function returns a character string with all characters in lower case.

UPPER(<Str>)
This function returns a character string with all characters in upper case.

LPAD(<Str1>,<i>[,<Str2>])
This function returns the character string Str1 expanded in length to i characters, using Str2 to fill in space as needed on the left side of Str1.
Example
SELECT LPAD('Oracle',10,'.') lapd_doted from Dual, would return ....Oracle
SELECT  LPAD('RAM', 7)  lapd_exa  from Dual    would return    '    RAM'

RPAD(<Str1>,<i>[,<Str2>])
RPAD is same as LPAD but Str2 is padded at the right side

LTRIM(<Str1>[,<Str2>])
The LTRIM function removes characters from the left side of the character Srting, with all the leftmost characters that appear in another text expression removed.
This function returns Str1 without any leading character that appears in Str2.If  Str2 characters are leading character in Str1, then Str1 is returned unchanged.
Str2 defaults to a single space.
Example
Select LTRIM('datawarehousing','ing') trim1
, LTRIM('datawarehousing   ') trim2
, LTRIM('         datawarehousing') trim3
, LTRIM('datawarehousing','data') trim4 from dual
O/P trim1                                      trim2                                     trim3                                        trim4
       datawarehousing               datawarehousing               datawarehousing                warehousing

RTRIM(<Str1>[,<Str2>])
Same as LTRIM but the characters are trimmed from the right side

TRIM([[<Str1>]<Str2> FROM]<Str3>)

• If present Str1 can be one of the following literal: LEADING, TRAILING, BOTH.
• This function returns Str3 with all C1(leading trailing or both) occurrences of characters in Str2 removed.
• If any of Str1,Str2 or Str3 is Null, this function returns a Null.

Str1 defaults to BOTH, and Str2 defaults to a space character.
Example
SELECT TRIM(' Oracle   ') trim1, TRIM('Oracle ') trim2 FROM Dual;
Ans trim1             trim2
        Oracle          Oracle
It'll remove the space from both string.

REPLACE(<Str1>,<Str2>[,<Str3>]
This function returns Str1 with all occurrence of Str2 replaced with Str3
Example
SELECT REPLACE (‘Oracle’, ’Ora’, ’Arti’) replace_exa FROM Dual;
O/p replace_exa
       Atricle

Essential Numeric Functions
ABS()
Select Absolute value
SELECT ABS(-25) "Abs" FROM DUAL;

  Abs
-------
  15
ACOS ()
Select cos value
SELECT ACOS(.28)"Arc_Cosine" FROM DUAL;
ASIN ()
Select sin value
SELECT ASIN(.6)"Arc_Cosine" FROM DUAL;
ATAN()
Select tan value
SELECT ATAN(.6)"Arc_Cosine" FROM DUAL;
CEIL()
Returns the smallest integer greater than or equal to the order total of a specified orde
SELECT  CEIL(239.8) FROM Dual would return 240

FLOOR()
Returns the largest integer equal to or less than value.

SELECT FLOOR(15.65) "Floor" FROM DUAL;

 Floor
------
15

MOD()
Return modulus value
SELECT MOD(11,3) "Mod" FROM DUAL;

 Modulus
-------
   2

POWER()
SELECT POWER(3,2) "Power" FROM DUAL;

 power
-------
   9
ROUND (number)
SELECT ROUND(43.698,1) "Round" FROM DUAL;

     Round
----------
      43.7

TRUNC (number)
The TRUNC (number) function returns n1 truncated to n2 decimal places. If n2 is omitted, then n1 is truncated to 0 places. n2 can be negative to truncate (make zero) n2 digits left of the decimal point.
SELECT TRUNC(12.75,1) "Trunc" FROM DUAL;

  Trunc
----------
  12.75

SELECT TRUNC(12.75,-1) "Trunc" FROM DUAL;

  Trunc
------
  10
Date And Time Function

ADD_MONTHS(date,number_of_month)
SELECT SYSDATE, ADD_MONTHS(SYSDATE,2), ADD_MONTHS(SYSDATE,-2) FROM DUAL;
Result:
SYSDATE     ADD_MONTH ADD_MONTH
-------------    ------------------------------------------
10-Feb-13       10-Apr-13 10-Dec-13

EXTRACT(<type> FROM <date>)
'Type' can be YEAR, MONTH, DAY, HOUR, MIN, SECOND, TIME_ZONE_HOUR, TIME_ZONE_MINUTE, TIME_ZONE_REGION
SELECT SYSDATE, EXTRACT(YEAR FROM SYSDATE)YEAR, EXTRACT(DAY FROM SYSDATE)DAY , EXTRACT(TIMEZONE_HOUR FROM SYSTIMESTAMP) TZH FROM DUAL;
LAST_DAY(<date>)
Extract last day of month
Example:
SELECT SYSDATE, LAST_DAY(SYSDATE) END_OF_MONTH  FROM DUAL;
Result: SYSDATE      END_OF_MO
               ----------    ---------------
             10-Feb-13      28-Feb-13

NEXT_DAY(<date>,<day>)
SELECT NEXT_DAY('28-Feb-13','SUN') "FIRST MONDAY OF MARCH”  FROM DUAL;
O/P FIRST MONDAY OF MARCH
               03-Mar-13
ROUND (date[,<fmt>])
SELECT SYSDATE, ROUND(SYSDATE,'MM'), ROUND(SYSDATE,'YYYY') FROM DUAL;
Result:SYSDATE   ROUND(SYS   ROUND(SYS
                                         -----------       ---------------
                                10-FEB-13 01-MAR-13 01-JAN-13
TRUNC(date[,<fmt>])
SELECT SYSDATE, TRUNC(SYSDATE,'MM'), TRUNC(SYSDATE,'YYYY') FROM DUAL;
Result: SYSDATE   TRUNC(SYS    TRUNC(SYS
                    -------      - ------------              --------
                    10-FEB-13 01-FEB-13    01-JAN-13

Most frequently asked in interviews.

  These questions are the most frequently asked in interviews.

1. To fetch ALTERNATE records from a table. (EVEN NUMBERED)
   select * from emp where rowid in (select decode(mod(rownum,2),0,rowid, null) from emp);
2. To select ALTERNATE records from a table. (ODD NUMBERED)
   select * from emp where rowid in (select decode(mod(rownum,2),0,null ,rowid) from emp);
3. Find the 3rd MAX salary in the emp table.
   select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2 where e1.sal <= e2.sal);
4. Find the 3rd MIN salary in the emp table.
   select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2where e1.sal >= e2.sal);
5. Select FIRST n records from a table.
   select * from emp where rownum <= &n;
6. Select LAST n records from a table
   select * from emp minus select * from emp where rownum <= (select count(*) - &n from emp);
7. List dept no., Dept name for all the departments in which there are no employees in the department.
   select * from dept where deptno not in (select deptno from emp); 
   alternate solution:  select * from dept a where not exists (select * from emp b where a.deptno = b.deptno);
   altertnate solution:  select empno,ename,b.deptno,dname from emp a, dept b where a.deptno(+) = b.deptno and empno is null;
8. How to get 3 Max salaries ?
   select distinct sal from emp a where 3 >= (select count(distinct sal) from emp b where a.sal <= b.sal) order by a.sal desc;
9. How to get 3 Min salaries ?
   select distinct sal from emp a  where 3 >= (select count(distinct sal) from emp b  where a.sal >= b.sal);
10. How to get nth max salaries ?
    select distinct hiredate from emp a where &n =  (select count(distinct sal) from emp b where a.sal >= b.sal);
11. Select DISTINCT RECORDS from emp table.
    select * from emp a where  rowid = (select max(rowid) from emp b where  a.empno=b.empno);
12. How to delete duplicate rows in a table?
    delete from emp a where rowid != (select max(rowid) from emp b where  a.empno=b.empno);
13. Count of number of employees in  department  wise.
    select count(EMPNO), b.deptno, dname from emp a, dept b  where a.deptno(+)=b.deptno  group by b.deptno,dname;
14.  Suppose there is annual salary information provided by emp table. How to fetch monthly salary of each and every employee?
    select ename,sal/12 as monthlysal from emp;
    
15. Select all record from emp table where deptno =10 or 40.
    select * from emp where deptno=30 or deptno=10;
    
16. Select all record from emp table where deptno=30 and sal>1500.
    select * from emp where deptno=30 and sal>1500;
    
17. Select  all record  from emp where job not in SALESMAN  or CLERK.
    select * from emp where job not in ('SALESMAN','CLERK');
    
18. Select all record from emp where ename in 'BLAKE','SCOTT','KING'and'FORD'.
    select * from emp where ename in('JONES','BLAKE','SCOTT','KING','FORD');
    
19. Select all records where ename starts with ‘S’ and its lenth is 6 char.
    select * from emp where ename like'S____';
    
20. Select all records where ename may be any no of  character but it should end with ‘R’.
    select * from emp where ename like'%R';
    
21. Count  MGR and their salary in emp table.
    select count(MGR),count(sal) from emp;
    
22. In emp table add comm+sal as total sal  .
    select ename,(sal+nvl(comm,0)) as totalsal from emp;
    
23. Select  any salary <3000 from emp table. 
    select * from emp  where sal> any(select sal from emp where sal<3000);
    
24. Select  all salary <3000 from emp table. 
    select * from emp  where sal> all(select sal from emp where sal<3000);
    
25. Select all the employee  group by deptno and sal in descending order.
    select ename,deptno,sal from emp order by deptno,sal desc;
    
26. How can I create an empty table emp1 with same structure as emp?
    Create table emp1 as select * from emp where 1=2;
    
27. How to retrive record where sal between 1000 to 2000?
    Select * from emp where sal>=1000 And  sal<2000
    
28. Select all records where dept no of both emp and dept table matches.
    select * from emp where exists(select * from dept where emp.deptno=dept.deptno)
    
29. If there are two tables emp1 and emp2, and both have common record. How can I fetch all the recods but common records only once?
    (Select * from emp) Union (Select * from emp1)
    
30. How to fetch only common records from two tables emp and emp1?
    (Select * from emp) Intersect (Select * from emp1)
    
31.  How can I retrive all records of emp1 those should not present in emp2?
    (Select * from emp) Minus (Select * from emp1)
    
32. Count the totalsa  deptno wise where more than 2 employees exist.
    SELECT  deptno, sum(sal) As totalsal
    FROM emp
    GROUP BY deptno
    HAVING COUNT(empno) > 2
    
       
        

Order to Cash query

SELECT hp.party_name customer
      ,hca.account_number
      ,hp.party_number
      ,ooh.order_number
      ,ooh.flow_status_code Order_status
      ,rcta.trx_date Invoice_date
      ,rcta.trx_number Invoice_number
      ,rcta.status_trx Invoice_Status
      ,rcta.invoice_currency_code Invoice_currency
      ,rctla.line_number
      ,msi.segment1 Item_number
      ,rctla.description
      ,rctla.extended_amount line_amt
      ,arp.amount_due_original 
      ,arp.amount_due_remaining 
      ,rcta.org_id 
      ,rcta.customer_trx_id
  FROM RA_CUSTOMER_TRX_ALL RCTA,
       RA_CUSTOMER_TRX_LINES_ALL RCTLA,
       RA_CUST_TRX_TYPES_ALL rctt,
       AR_PAYMENT_SCHEDULES_ALL arp,
       HZ_CUST_ACCOUNTS_ALL HCA,
       HZ_PARTIES HP,
       MTL_SYSTEM_ITEMS_B msi, 
       OE_ORDER_LINES_ALL ool,
       OE_ORDER_HEADERS_ALL ooh
WHERE rcta.customer_trx_id = rctla.customer_trx_id
  AND msi.inventory_item_id = rctla.inventory_item_id
  AND msi.organization_id = 116
  AND rcta.cust_trx_type_id = rctt.cust_trx_type_id
  AND arp.customer_trx_id = rcta.customer_trx_id
  AND rctla.line_type =  'LINE'
  AND rcta.org_id = rctla.org_id
  AND rcta.bill_to_customer_id = hca.cust_account_id
  AND hca.party_id = hp.party_id
  AND rctla.interface_line_attribute6 = ool.line_id
  AND ool.header_id = ooh.header_id
--  AND ooh.order_number = '13904756'
--  AND hp.party_name = 'BANQUE MAGNETIQUE'
 ORDER BY hp.party_name,rcta.trx_number,rctla.line_number;

Invoice List for a Customer query

SELECT hp.party_name customer
      ,hca.account_number
      ,rcta.trx_date
      ,rcta.trx_number Invoice_number
      ,rcta.status_trx
      ,rcta.invoice_currency_code Invoice_currency
      ,rctla.line_number
      ,msi.segment1 Item_number
      ,rctla.description
      ,rctla.extended_amount line_amt
      ,rcta.org_id 
      ,rctt.cust_trx_type_id     
  FROM RA_CUSTOMER_TRX_ALL RCTA,
       RA_CUSTOMER_TRX_LINES_ALL RCTLA,
       RA_CUST_TRX_TYPES_ALL rctt,
       HZ_CUST_ACCOUNTS_ALL HCA,
       HZ_PARTIES HP,
       MTL_SYSTEM_ITEMS_B msi
WHERE rcta.customer_trx_id = rctla.customer_trx_id
  AND msi.inventory_item_id = rctla.inventory_item_id
  AND msi.organization_id = 116
  AND rcta.cust_trx_type_id = rctt.cust_trx_type_id
  AND rctt.TYPE = 'INV'
  AND rctla.line_type =  'LINE'
  AND rcta.org_id = rctla.org_id
  AND rcta.bill_to_customer_id = hca.cust_account_id
  AND hca.party_id = hp.party_id
  AND hp.party_name = 'A. C. Networks'
--  AND hca.account_number = '1581'
--  AND rcta.status_trx = 'OP'
  ORDER BY hp.party_name,rcta.trx_number,rctla.line_number;

Get List of responsibilities assigned to a user

select fu.user_name,
  frt.responsibility_name,
  furg.start_date,
  furg.end_date
from fnd_user fu ,
  fnd_user_resp_groups_direct furg ,
  fnd_responsibility_vl frt
where fu.user_id                 = furg.user_id
and frt.responsibility_id        = furg.responsibility_id
and frt.application_id           = furg.responsibility_application_id
and nvl(furg.end_date,sysdate+1) > sysdate
and nvl(frt.end_date,sysdate +1) > sysdate
and fu.user_name                   = :p_user_name
--and fu.user_name                   = :p_userid;
;

Trace details for a concurrent request along with the log and output file location details

SELECT 'Request id: '
  || request_id,
  'Trace id: '
  || oracle_process_id,
  'Trace Flag: '
  || req.enable_trace,
  'Trace Name: '
  || dest.VALUE
  || '/'
  || LOWER (dbnm.VALUE)
  || '_ora_'
  || oracle_process_id
  || '.trc',
  'Prog. Name: '
  || prog.user_concurrent_program_name,
  'File Name: '
  || execname.execution_file_name
  || execname.subroutine_name,
  'Status : '
  || DECODE (phase_code, 'R', 'Running')
  || '-'
  || DECODE (status_code, 'R', 'Normal'),
  'SID Serial: '
  || ses.sid
  || ','
  || ses.serial#,
  'Module : '
  || ses.module ,
  TO_CHAR(req.actual_start_date,'DD-MON-YYYY HH24:MI:SS') Request_start_date ,
  TO_CHAR(req.actual_completion_date,'DD-MON-YYYY HH24:MI:SS') Request_completion_date ,
  req.logfile_name ,
  req.outfile_name ,
  req.argument_text parameters_passed
FROM fnd_concurrent_requests req,
  v$session ses,
  v$process PROC,
  v$parameter dest,
  v$parameter dbnm,
  fnd_concurrent_programs_vl prog,
  fnd_executables execname
WHERE req.request_id           = :p_request_id
AND req.oracle_process_id      = proc.spid(+)
AND proc.addr                  = ses.paddr(+)
AND dest.NAME                  = 'user_dump_dest'
AND dbnm.NAME                  = 'db_name'
AND req.concurrent_program_id  = prog.concurrent_program_id
AND req.program_application_id = prog.application_id
AND prog.application_id        = execname.application_id
AND prog.executable_id         = execname.executable_id
;

List Concurrent Programs using a specific valueset in its parameter list

SELECT fcp.user_concurrent_program_name,
  fat.application_name,
  fdfc.column_seq_num Seq,
  fdfc.form_left_prompt "Parameter Name",
  fdfc.enabled_flag Active
FROM FND_DESCR_FLEX_COL_USAGE_VL fdfc ,
  fnd_flex_value_sets ffvs ,
  fnd_concurrent_programs_vl fcp ,
  fnd_application_tl fat
WHERE 1                             =1
AND ffvs.flex_value_set_id          = fdfc.flex_value_set_id
and fdfc.descriptive_flexfield_name = '$SRS$.'||fcp.concurrent_program_name
and fcp.application_id              = fat.application_id
and ffvs.flex_value_set_name        = :p_valuesetname -- Enter value set name
;

Form Personalization in Oracle Apps

Form Personalization in Oracle Apps

Form Personalization

Oracle has provided a simple and easy feature to implement the customer specific requirements without modifying the underlying forms code or CUSTOM library. Although CUSTOM library still can be used for forms customization to implement the complex business logic, the personalization feature provided in the latest release is easy, faster and requires minimum development effort.

If user needs to personalize any details in a form like changing field names, adding some validations, etc, then Form Personalization can provide the way out in few simple steps.

Please follow here to go through Steps with example

Steps:

Step – 1:

Go to the specific form where you need the changes

Then we need to note down the block and field name which is needed in next steps.

So keep the cursor on the required field and then Go to Help --> Diagnostics --> Examine

Note down the Block and Field value.

Step -2:

Now the step to do the personalization

Follow this navigation

Help à Diagnostics à Custom Code à Personalize

This will show the Form Personalization window

Here you need to do the customization as per the requirement.

As you can see in this form, there are mainly 4 sections

1.       Rule

2.       Condition

3.       Actions

4.       Context

Step – 3:

Rule:

This is the section where you need to create the rules how your requirement is going to be executed. This section contains 4 segments, Seq, description, Level and Enabled flag.

Seq : Need to give the Sequence how the rule will execute (a number)

Description: Write a sort description of the rule which you are going to create

Level: Select the level where you want the rule to execute

Enabled: This is the flag which will enable or disable the rule.

Example:

Step – 4:

Condition:

Now need to set the condition.

This is the section where you will define when the rule will be executed.

This section contains 4 segments, Trigger Event, Trigger Object, Condition and Processing mode.

Trigger Event: This specifies where the rule will be executed. There are mainly 5 types of event which decided the trigger of the rule.

Trigger Object:  This is the Object in the form which decides at what level the rule will be executed.

The values can be “<Form Name>”, ‘<Block Name>” or “<Item Name>”

Condition: Here mention any SQL statement to control the execution of the rule when the mentioned criterion is met.

Processing Mode: This is the mode where you want the rules to be executed.

Example:

Step – 5:

Actions:

This decides what will happen when the rule will execute.

This contains 2 sections.

Left sections contains Seq, Type, Description, Language, Enabled

Seq: Mention the Action sequence which this rule will execute

Type: This is of 4 types, Property, Message, Builtin, Menu.

This decides which type of action, the rule will perform.

According to this the right section will come and need to fill the detailed actions.

Description: Write a description on the action which this will perform.

Language: Select the language

Enabled: You can enable or disable the actions here.

Right Section is a dynamic section; it appears depending on the Type field value in Left section.

Action Types:

Property

The action type “Property” is used to set the properties of the objects. The various objects include

“Item, Window, and Block etc.”.  Actual object name defined in the form should be entered after selecting the object type.

Property Name is the property to be changed

Value is the new desired value for the property.

The current value of the property will be displayed when clicked on the “Get Value” button.

Example:

Object Type = “Item”

Target Object = “:ORDER.ORDER_NUMBER”

Property Name = “PROMPT_TEXT”

Value = “Claim Number”

The value is interpreted at runtime, so you can use SQL functions and operators. Any value started with “=” operator will be interpreted at runtime; otherwise the value is treated as is entered in the value field

Message:

The action type “Message” is used to display custom messages during runtime. Message Type and Description should be entered after selecting the action type as “Message”. The available message types are “Error, Warning, Hint, Question and Debug”. Depending on the response expected from the user, the appropriate message type should be selected.

Example:

Message Type = “Hint”

Message Text = “Please Follow the Date format DD-MON-YYYY”

Builtin:

The action type “Builtin” is used to execute the form and AOL API’s. Depending on the API type selected, the parameters should be entered.

Example:

Builtin Type = FND_UTILITIES.OPEN_URL

Argument = http://www.oracle.com

Menu:

The action type “Menu” is used to activate the available special menus on the Tools menu.

Oracle Applications provide 45 special menus under Tools menu which can be used by customers based on their requirements.

Select the SPECIAL menu which is not used by the form. Menu label is the prompt which appears to the users when Tools menu is invoked, block specifies the blocks for which the special menu should be activated and Icon name is the .ico file name.

A separator can be created above the activated special menu by selecting the “Render line before menu” checkbox.

Example:

Menu Entry = SPECIAL4

Menu Label = Additional Order Header Information

Enabled in Blocks(s) = ORDER

Icon Name = Flower.ico

Step – 6:

Context:

Context manages to whom the personalization should apply. This is similar to the concept of using profile options in Oracle Applications. The various levels are Site, Responsibility, Industry and User. During runtime, the values provided in the context are evaluated and personalization rules will be applied. Usage of context is very vital in implementing the personalization to prevent the inappropriate users accessing these customizations of the form.

Example:

Context = Responsibility

Value = Order Management Super User

Step by Step Analysis

Requirement: Don’t allow the user to use more than 1 qty in Order line Qty field during creation of a sales order.

Solution:

Step -1:

Go to the specific form where you need the changes

Here in this case this will be the Sales Orders form and then Line Items tab.

Then we need to note down the block and field name which is needed in next steps.

So keep the cursor on the required field and then Go to Help à Diagnostics à Examine

Note down the Block and Field value.

Step -2:

Help --> Diagnostics --> Custom Code --> Personalize

Step – 3:

Fill the Rule section

Step – 4:

Fill the Condition section

Step – 5:

Fill the Actions section

Step – 6:

Fill the context As you need.

Save this.

Log out of the application and login again, then in Sales Order line item tab enter Qty field value as 1 and try to save, you can see the message.

JOIN CONDITIONS USED IN ORACLE APPS(GL,AP,AR,INV ETC)

KEY JOINS

GL AND AP
GL_CODE_COMBINATIONS AP_INVOICES_ALL
code_combination_id = acct_pay_code_combination_id
GL_CODE_COMBINATIONS AP_INVOICES_DISTRIBUTIONS_ALL
code_combination_id = dist_code_combination_id
GL_SETS_OF_BOOKS AP_INVOICES_ALL
set_of_books_id = set_of_books_id

GL AND AR
GL_CODE_COMBINATIONS RA_CUST_TRX_LINE__GL_DIST_ALL
code_combination_id = code_combination_id

GL AND INV
GL_CODE_COMBINATIONS MTL_SYSTEM_ITEMS_B
code_combination_id = cost_of_sales_account


GL AND PO
GL_CODE_COMBINATIONS PO_DISTRIBUTIONS_ALL
code_combination_id = code_combination_id


PO AND AP
PO_DISTRIBUTIONS_ALL AP_INVOICE_DISTRIBUTIONS_ALL
Po_distribution_id = po_distribution_id

PO_VENDORS AP_INVOICES_ALL
vendor_id = vendor_id

PO AND SHIPMENTS
PO_HEADERS_ALL RCV_TRANSACTIONS
Po_header_id = po_header_id

PO_DISTRIBUTIONS_ALL RCV_TRANSACTIONS
Po_distribution_id = po_distribution_id

SHIPMENTS AND INVOICE
RCV_TRANSACTIONS AP_INVOICE_DISTRIBUTIONS_ALL
RCV_TRANSACTION_ID = RCV_TRANSACTION_ID


PO AND INV
PO_REQUISITION_LINES_ALL MTL_SYSTEM_ITEMS_B
item_id = inventory_item_id
org_id = organization_id

PO AND HRMS
PO_HEADERS_ALL HR_EMPLOYEES
Agent_id = employee_id

PO AND REQUISITION
PO_DISTRIBUTIONS_ALL PO_REQ_DISTRIBUTIONS_ALL
req_distribution_id = distribution_id


SHIPMENTS AND INV
RCV_TRANSACTIONS MTL_SYSTEM_ITEMS_B
Organization_id = organization_id

INV AND HRMS
MTL_SYSTEM_ITEMS_B HR_EMPLOYEES
buyer_id = employee_id

OM AND AR
OE_ORDER_HEADERS_ALL RA_CUSTOMER_TRX_LINES_ALL
TO_CHAR( Order_number) = interface_line_attribute1
OE_ORDER_LINES_ALL RA_CUSTOMER_TRX_LINES_ALL
TO_CHAR(Line_id) = interface_line_attribute6

OE_ORDER_LINES_ALL RA_CUSTOMER_TRX_LINES_ALL
reference_customer_trx_line_id = customer_trx_line_id

OM AND SHIPPING
OE_ORDER_HEADERS_ALL WSH_DELIVARY_DETAILS

HEADER_ID = SOURCE_HEADER_ID

OE_ORDER_HEADERS_ALL WSH_DELIVARY_DETAILS
LINE_ID = SOURCE_LINE_ID
AP AND AR (BANKS)
AR_CASH_RECEIPTS_ALL AP_BANK_ACCOUNTS
REMITTANCE_BANK_ACCOUNT_ID = ABA.BANK_ACCOUNT_ID
AP AND AR
HZ_PARTIES AP_INVOICES_ALL

PARTY_ID = PARTY_ID

OM AND CRM
OE_ORDER_LINES_ALL CSI_ITEM_INSTANCES(Install Base)

LINE_ID = LAST_OE_ORDER_LINE_ID
Table Name: Po_Requisition_Headers_All A
Column Names Table Name Column Name
A. REQUISITION_HEADER_ID PO_REQUISITION_LINES_ALL REQUISITION_HEADER_ID
A. TYPE_LOOKUP_CODE PO_DOCUMENT_TYPES DOCUMENT_SUBTYPE
A. PREPARER_ID PER_PEOPLE_F PERSON_ID
A. ORG_ID MTL_SYSTEM_ITEMS ORGANIZATION_ID
A. ORG_ID MTL_ORGANIZATIONS ORGANIZATION_ID
Table Name: Po_Requisition_Lines_All B
Column Names Table Name Column Name
B .REQUISITION_HEADER_ID PO_REQUISITION_HEADERS_ALL REQUISITION_HEADER_ID
B .REQUISITION_LINE_ID PO_REQ_DISTRIBUTIONS_ALL REQUISITION_LINE_ID
B .LINE_TYPE_ID PO_LINE_TYPES LINE_TYPE_ID
B .ITEM_ID MTL_SYSTEM_ITEMS INVENTORY_ITEM_ID
B .ORG_ID MTL_SYSTEM_ITEMS ORGANIZATION_ID
Table Name: Po_Requisition_Distributions_All C .
Column Names Table Name Column Name
C .REQUISITION_LINE_ID PO_REQUISITION_LINES_ALL REQUISITION_LINE_ID
C .DISTRIBUTION_ID PO_DISTRIBUTIONS_ALL REQ_DISTRIBUTION_ID
C .SET_OF_BOOKS_ID GL_SETS_OF_BOOKS SET_OF_BOOKS_ID
C .CODE_COMBINATION_ID GL_CODE-COMBINATIONS CODE_COMBINATION_ID
Table Name: Po_Distributions_All D .
Column Names Table Name Column Name
D .PO_LINE_ID PO_LINES PO_LINE_ID
D .REQ_DISTRIBUTION_ID PO_REQ_DISTRIBUTIONS_ALL DISTRIBUTION_ID
D .PO_DISTRIBUTION_ID AP_INVOICE_DISTRIBUTIONS_ALL PO_DISTRIBUTION_ID
Table Name: Po_Headers_All E .
Column Names Table Name Column Name
E .PO_HEADER_ID PO_LINES PO_HEADER_ID
E .PO_HEADER_ID RCV_SHIPMENT_LINES PO_HEADER_ID
E .VENDOR_ID PO_VENDORS VENDOR_ID
E .AGENT_ID PER_PEOPLE PERSON_ID
E .TYPE_LOOK_UP_CODE PO_DOCUMENT_TYPES DOCUMENT_SUBTYPE
Table Name: Po_Lines_All F.
Column Names Table Name Column Name
F.PO_HEADER_ID PO_HEADERS PO_HEADER_ID
F.PO_LINE_ID PO_DISTRIBUTIONS_ALL PO_LINE_ID
F.ITEM_ID MTL_SYSTEM_ITEMS ITEM_ID
Table Name: Rcv_Shipment_Lines G.
Column Names Table Name Column Name
G.PO_HEADER_ID PO_HEADERS PO_HEADER_ID
G.SHIPMENT_HEADER_ID RCV_SHIPMENT_HEADERS SHIPMENT_HEADER_ID
Table Name: Ap_Invoices_All H.
Column Names Table Name Column Name
H. INVOICE_ID AP_INVOICE_DISTRIBUTIONS_ALL INVOICE_ID
Table Name: Oe_Order_Headers_All I.
Column Names Table Name Column Name
I.HEADER_ID OE_ORDER_LINES HEADER_ID
I.SOURCE_HEADER_ID WISH_DELIVERY_DETAILS SOURCE_HEADER_ID
I.PRICE_LIST_ID QP_LIST_HEADERS_TL LIST_HEADER_ID
I.ORG_ID MTL_ORGANIZATIONS ORGANIZATION_ID
I.SALESREP_ID JTF_RS_SALESREPS SALESREP_ID
I.ORDER_TYPE_ID OE_TRANSACTION_TYPES TRANSACTION_TYPE_ID
I.ORDER_SOURCE_ID OE_ORDER_SOURCES ORDER_SOURCE_ID
I.ACCOUNTING_RULE_ID RA_RULES RULE_ID
I.PAYMENT_TERM_ID RA_TERMS TERM_ID
I.SOLD_TO_ORG_ID HZ_CUST_ACCOUNTS CUST_ACCOUNT_ID
I.SHIP_FROM_ORG_ID MTL_PARAMETERS ORGANIZATION_ID
I.SHIP_TO_ORG_ID HZ_CUST_SITE_USES_ALL SITE_USE_ID
Table Name: Oe_Order_Lines_All J.
Column Names Table Name Column Name
J.LINE_TYPE_ID OE_TRANSACTION_TYPES_TL TRANSACTION_TYPE_ID
J.INVOICING_RULE_ID RA_RULES RULE_ID
Table Name: Hz_Parties K.
Column Names Table Name Column Name
K.PATY_ID HZ_CUST_ACCOUNTS PATY_ID
K.CUST_ACCOUNT_ID OE_ORDER_LINES SOLD_TO_ORG_ID
Table Name: Hz_Party_Sites_All L.
Column Names Table Name Column Name
L.PATY_ID HZ_PARTIES PATY_ID
L. LOCATION_ID HZ_LOCATIONS LOCATION_ID
Table Name: Wsh_delivery_details M.
Column Names Table Name Column Name
M.SOURCE_HEADER_ID OE_ORDER_HEADERS SOURCE_HEADER_ID
M.DELIVERY_DETAIL_ID WSH_DELIVERY_ASSIGNMENTS DELIVERY_DETAIL_ID
M.DELIVERY_ID WSH_NEW_DELIVERIES DELIVERY_ID
M.INVENTORY_ITEM_ID MTL_SYSTEM_ITEMS INVENTORY_ITEM_ID
Table Name: RA_CUSTOMER_TRX_ALL N.
Column Names Table Name Column Name
N.CUSTOMER_TRX_ID AR_RECEIVABLE_APPLICATIONS_ALL APPLIED_CUSTOMER_TRX_ID
N.TERM_ID RA_TERMS TERM_ID
N.CUSTOMER_TRX_ID RA_CUST_TRX_LINE_GL_DIST CUSTOMER_TRX_ID
Table Name: AR_CASH_REC EIPTS_ALL O.
Column Names Table Name Column Name
O.CASH_RECEIPT_ID AR_RECEIVABLE_APPLICATIONS_ALL CASH_RECEIPT_ID
O.SET_OF_BOOKS_ID GL_SETS_OF_BOOKS SET_OF_BOOKS_ID

ORDER MANAGEMENT TABLES

Entered
oe_order_headers_all 1 record created in header table
oe_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_transactions
mtl_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 staged
mtl_material_transactions On the ship confirm form, check Ship all box
wsh_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 for
ra_customer_trx_lines_all specific batch transaction tables get populated
Price Details
qp_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 Phone
hz_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 Numbers
fnd_doc_sequence_categories
fnd_doc_sequence_assignments
Default rules for Price List
oe_def_attr_def_rules Price List Default Rules
oe_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 Orders
oe_order_lines_all Cancel Order Details.
Read more...

IMPORTANT APPS TABLES

PO Modules:
1. Po_requisition_headers_all -- Req. Header Info
2. Po_requisition_lines_all -- Req. Lines Info
3. Po_req_distributions_all -- Req.Distributions Info
4. PO_HEADERS_ALL -- PO Header Information
5. PO_LINES_AL -- PO Line Information
6. PO_LINE_LOCATIONS_ALL -- PO Line Shipment Info
7. PO_DISTRIBUTIONS_ALL -- PO Accounting Info
8. rcv_shipment_headers -- Receipt Header Info
9. rcv_shipment_lines -- Receipt Line Info
10. rcv_transactions -- Receipt Transactions
11. rcv_routing_headers -- Receipt Routing info
12. po_vendors -- Supplier Header
13. po_vendor_sites_all -- supplier site level info
14. po_vendor_contacts -- Supplier Contacts Info
15. hr_locations -- Supplier Physical address info

Order Management Tables
1. oe_order_headers_all -- Order Header info
2. oe_order_lines_all -- order line info
3. oe_transaction_types_tl -- Order Type
4. hz_cust_accounts -- Customer Info
5. hz_parties -- Party Info
6. hz_cust_site_uses_all -- Customer site info
7. hz_cust_acct_sites_all -- Customer acct
8. hz_party_sites -- customer site level info
9. hz_locations -- Customer Phsyical Location
10.ra_sales_reps_all -- Sales Reps
11.Qp_list_headers_tl -- Pricing info
12.wsh_delivery_detials -- Shipping DElivery details
13.wsh_delivery_assignments --shipping deliver assignments
14.wsh_new_deliveries -- Delivery Header info
15.oe_order_holds -- order hold info
16.oe_hold_sources_all -- hold source
17.oe_hold_releases_ll -- hold releases
18.oe_hold_definitions

General Ledger (GL)
1.gl_je_batches -- Journal Batches
2.gl_je_headers -- Journal Header
3.gl_je_lines -- Jounral Lines
4.gl_je_sources -- Journal Sources
5.gl_je_categories -- Journal Categories
6.gl_code_combinations -- Accounting Info
7.gl_balances -- Journal Balances
8.gl_sets_of_books -- Set of books info
Accounts Payables (AP)

1. ap_invoices_all --- Invoice Header info
2. ap_invoice_distributions_all -- Invoice Line info
3. ap_invoice_payments_All -- Invoice Payment Info
4. ap_payment_schedules_all
5. ap_checks_all -- Check Info
6. ap_banks
7. ap_bank_branches -- bank branches
8. ap_ae_headers_all -- accounting header info
9. ap_ae_lines_all -- accounting lines info
10.ap_Accounting_events_all -- accounting event info
11. po_vendors -- Supplier Header
12. po_vendor_sites_all -- supplier site level info
13. po_vendor_contacts -- Supplier Contacts Info
14. hr_locations -- Supplier Physicaal info

Accounts Receivables (AR)

1. ra_customer_trx_all -- transaction header info
2. ra_customer_trx_lines_all -- transaction lines
3. ra_cust_trx_line_gl_dist_all -- transaction acct info
4. ar_cash_receipts_all -- receipt info
5. ar_receivable_applications_all
6. hz_cust_accounts -- Customer Info
7. hz_parties -- Party Info
8. hz_cust_site_uses_all -- Customer site info
9. hz_cust_acct_sites_all -- Customer acct
10.hz_party_sites -- customer site level info
11.hz_locations -- Customer Phsyical Location
12.ra_cust_trx_types_all -- Transaction Types

General Ledger (GL)

1.gl_je_batches -- Journal Batches
2.gl_je_headers -- Journal Header
3.gl_je_lines -- Jounral Lines
4.gl_je_sources -- Journal Sources
5.gl_je_categories -- Journal Categories
6.gl_code_combinations -- Accounting Info
7.gl_balances -- Journal Balances
8.gl_sets_of_books -- Set of books info
9.gl_charts_of_accounts -- Chart of accounts info



Inventory (Inv)

1.mtl_system_items_b -- item info
2.mtl_onhand_quantities -- Item onhand qty
3.mtl_reservations -- Item Reservations
4.mtl_material_transactions -- Inventory Transaction
5.mtl_txn_request_headers -- Move Order Header Info
6.mtl_txn_request_lines -- Move order line info
7.mtl_item_locations -- Item Physical Locations
8.org_organization_definitions -- Organization info
9.mtl_categories_b -- Item Categories info
10.mtl_transaction_accounts -- Item Tranaction acct
11.mtl_txn_source_types -- Transaction Sources
12.mtl_secondary+inventories -- Subinventory info
Read more...