SQL

August 2, 2011

 

Views

A view is a logical entity. It is a SQL statement stored in the database in the system tablespace. It can used in much the same way as database table. When ever query is fired against it database taked the stored SQL statement and creates a table in memory and the temporary table.

As per my understanding and experience I have seen the use of VIEWS in two situtations:

  1. Hide complexity of a SQL statement
    Example: I need to design 10 report based on 3 tables. All reports are based on same set of data which drive from union between the 3 tables but different WHERE clause. Instead of writing the Union SQL in all 10 reports, I will simply create a view based on that SQL, and then use that view to create report with different WHERE clause.
  2. Security Purpose
    Example: I have table with different company data in it. I need to restrict access of user to his company data only. For this I can create a package with company variable in it (Set at the time of user choose the company) and then create view with WHERE clause as company equal to package company variable.

Materialized Views
<!–[if !supportLineBreakNewLine]–>
<!–[endif]–>

Materialized Views(also known as snapshots in prior releases) is a pre-computed table comprising aggregated or joined data from fact and possibly dimension tables. Also known as a summary or aggregate table.

It improves query performance by precalculating expensive join and aggregation operations on the database prior to execution and storing the results in the database. The query optimizer automatically recognizes when an existing materialized view can and should be used to satisfy a request. It then transparently rewrites the request to use the materialized view. Queries go directly to the materialized view and not to the underlying detail tables. In general, rewriting queries to use materialized views rather than detail tables improves response.

Conclusion:

While views are stored in the database as a SQL statement and are created as needed, a materialized view is created as a table, and physically stored in the database.

 

 

 

Understanding Function Based Indexes

First have a look at following query:

SELECT e_name, age, address
FROM employees
WHERE UPPER(e_name) = ‘RAJENDER SINGH’;

Now, One of the important instruction that I normally give to my team that when ever they are writing the SQL query, never ever use function in a WHERE CLAUSE of a SQL statement ( as above). Because doing so will guarantee that a normal index would not be used. However, if really necessary to overcome this setback use special type of Index called Function Based Indexes.

When using the Function Based Indexes, we index column on the product of a function (internal or user written) or an expressions rather then on its column value.

SQL for creating function index is as follows:
CREATE INDEX emp_upper_idx
ON employees(UPPER(e_name));

Where emp_upper_idx is name of the index, employees is a table name and UPPER(e_name)) is the function.

However there are few prerequisites we must make sure before we can use it in our schema because internally when ever this type of SQL query is passed and if there is Function Based Index to support it, the query is rewritten to allow the index to be used.

First, that our schema must be granted system privilege query rewrite.

Example as follows:

GRANT QUERY REWRITE TO user;

Secondly, following session or system variable must be set:

QUERY_REWRITE_ENABLED=TRUE
QUERY_REWRITE_INTEGRITY=TRUSTED

 

 

 

Deleting duplicate rows from a table

One of the old way in which I normally do this by using GROUP BY and HAVING keywords.

Example:

DELETE FROM EMP
WHERE ROWID NOT IN (SELECT RWID FROM ( SELECT COUNT(1), MAX(ROWID) RWID FROM EMP
GROUP BY empno
HAVING COUNT(1) > 1 ))
AND empno IN (SELECT empno FROM ( SELECT empno, COUNT(1) FROM EMP
GROUP BY empno
HAVING COUNT(1) > 1 ))
/

Thanks Sumit for pointing out the mistake.

Other way which we can perform same function is by using Oracle’s analytic functions ROW_NUMBER() as follows
DELETE FROM emp
WHERE ROWID IN (SELECT ROWID
FROM ( SELECT ROWID, ROW_NUMBER() OVER (PARTITION BY empno ORDER BY EMPNO) rn
FROM emp2)
WHERE rn > 1)
/

This function almost act like a ROWNUM pseudocolumn with only addition feature that it resets back to one for each partition that we define in out query (ROW_NUMBER() OVER (PARTITION BY empno ORDER BY EMPNO))

 

 

 

 

Time-series related query at Oracle Forum

Today I came across one interesting question at Oracle Forum

Dear experts!
Please help me with this kind of query!
Suppose that I have a table like the following to keep track of moving path of an item

location datetime

l1 2006/10/01
l1 2006/10/20
l1 2006/11/01
l2 2006/11/03
l2 2006/11/19
l1 2006/11/28
l1 2006/12/10

How can I figure which location the object used to be at and the duration it stayed there over time, so that the result of the query look like this:
location date_start date_end
l1 2006/10/01 2006/10/20
l2 2006/11/03 2006/11/19
l1 2006/11/28 2006/12/10

Thank you very much!

——————————————————————–

I though lot about it, and gave him following answer:

Ok first I created one table:

CREATE TABLE TEMP1234(
location VARCHAR2(6),
date_start DATE
);
ALTER SESSION SET NLS_DATE_FORMAT=’YYYY/MM/DD’;

INSERT into TEMP1234 values (’l1′, ‘2006/10/01′);
INSERT into TEMP1234 values (’l1′, ‘2006/10/20′);
INSERT into TEMP1234 values (’l1′, ‘2006/11/01′);
INSERT into TEMP1234 values (’l2′, ‘2006/11/03′);
INSERT into TEMP1234 values (’l2′, ‘2006/11/19′);
INSERT into TEMP1234 values (’l1′, ‘2006/11/28′);
INSERT into TEMP1234 values (’l1′, ‘2006/12/10′);

COMMIT;

Now once it is done, now issue following SQL.

SELECT location, date_start, (MAX(date_start) OVER ( order by date_start rows
between current row and 1 following)) as date_end
FROM TEMP1234
ORDER BY date_start
/

Result will be as follows:
LOCATI DATE_START DATE_END
—— ———- ———-
l1 2006/10/01 2006/10/20
l1 2006/10/20 2006/11/01
l1 2006/11/01 2006/11/03
l2 2006/11/03 2006/11/19
l2 2006/11/19 2006/11/28
l1 2006/11/28 2006/12/10
l1 2006/12/10 2006/12/10

7 rows selected.
I hope this will solve your problem.

Interesting SQL

Today I came across another interesting solution.

Situation was as follows:

There is PROJECTS table, with columns as follows (datatype never mind):
PERSON
ID_STATE

We need to select persons from this table having all the ID_STATE (2, 3 and 4)

I found solution given by Aketi Jyuuzou very interesting and simple!

SELECT person
FROM projects
GROUP BY person
HAVING MAX(case when ID_STATE = 2 then 1 else 0 end)
* MAX(case when ID_STATE = 3 then 1 else 0 end)
* MAX(case when ID_STATE = 4 then 1 else 0 end)= 1;

Which shows very good use of boolean algebra in having clause.

 

 

 

PL/SQL::New FOLLOWS Clause

Before Oracle 11g, the order in which triggers of same type fire was arbitrary without any underlying principle or logic.

But now with Oracle 11g, the sequence of trigger firing of same type on same table can be control through use of new FOLLOWS clause.

With this new FOLLOWS clause, one can specify after which other trigger of the same type the trigger should fire.

Example:

Let suppose we have a table called “item_ledger”.

and we have one trigger called “itemledger_trg” as follows:

CREATE OR REPLACE itemledger_trg
BEFORE INSERT
ON item_ledger
FOR EACH ROW
BEGIN
……….
END;

Now we want to create another trigger called “account_trg” which should fire after “itemledger_trg”.

In Oracle 11g we can do as follows:

CREATE OR REPLACE account_trg
BEFORE INSERT
ON item_ledger
FOR EACH ROW
FOLLOWS itemledger_trg
BEGIN
……….
END;

 

 

 

 

 

SQL Tip::Using OUTER JOIN with Filter

Hi Guys!

Today I came across very simple and very interesting fact!

First Assume as follows:

Table Structure
——————————-

SQL> desc emp;
Name Null? Type
—————————————– ——– ————-
EMP_CODE NUMBER
EMP_NAME VARCHAR2(100)

SQL> desc emp_dept;
Name Null? Type
—————————————– ——– ————-
EMP_CODE NUMBER
DEPT_CODE NUMBER
FROM_DATE DATE
TO_DATE DATE

Table Data
——————————-

SQL> select * from emp;

EMP_CODE EMP_NAME
———- —————
10000 Oracle Nerd
20000 Oracle Nerd2

SQL> select * from emp_dept;

EMP_CODE DEPT_CODE FROM_DATE TO_DATE
———- ———- ———- ———-
10000 10 12/12/2005 30/12/2006
10000 10 31/12/2006

Now I want to list out all the employee with their department, to do so I will shoot out query as follows:

SQL> SELECT emp.emp_code, emp.emp_name, emp_dept.dept_code
2 FROM emp, emp_dept
3 WHERE emp.emp_code = emp_dept.emp_code
4 AND TO_CHAR(from_date, ‘YYYYMM’) = ‘20070912′ OR to_date IS NULL)
6 /

EMP_CODE EMP_NAME DEPT_CODE
———- ————— ———-
10000 Oracle Nerd 10

But now when we see the result we see “Oracle Nerd2″ is not coming, so I changed my query and put outer join as follows:

SQL> SELECT emp.emp_code, emp.emp_name, emp_dept.dept_code
2 FROM emp, emp_dept
3 WHERE emp.emp_code = emp_dept.emp_code (+)
4 AND TO_CHAR(from_date, ‘YYYYMM’) = ‘20070912′ OR to_date IS NULL)
6 /

EMP_CODE EMP_NAME DEPT_CODE
———- ————— ———-
10000 Oracle Nerd 10

But still I cannot see “Oracle Nerd2″, then I changed my query as follows:

SQL> SELECT emp.emp_code, emp.emp_name, emp_dept.dept_code
2 FROM emp, ( SELECT emp_code, dept_code
3 FROM emp_dept
4 WHERE TO_CHAR(from_date, ‘YYYYMM’) = ‘20070912′ OR to_date IS NULL)) emp_dept
6 WHERE emp.emp_code = emp_dept.emp_code (+)
7 /

EMP_CODE EMP_NAME DEPT_CODE
———- ————— ———-
10000 Oracle Nerd 10
20000 Oracle Nerd2

Ahhh at last its ok now!

Now what has happened?

Actually Oracle was doing the join first and then filtering the query where as I need to filter data first to get the current department and then do the join!

Any way at last problem solved for my team with use of this inline view with outer join!

 

 

 

 

 

 

Updating a table from another table

Today I went through few post by Ask Tom, and stuck with this fantastic post

Lets first create following tables to show the concept

SQL> CREATE TABLE table_1 ( c_code NUMBER PRIMARY KEY, c_name VARCHAR2(40) );
Table created.

SQL> CREATE TABLE table_2 ( c_code NUMMBER PRIMARY KEY, c_name VARCHAR2(40) );
Table created.

Note:
We need a PRIMARY KEY or an UNIQUE KEY on source table atleast from where we will be getting the values because if this CONSTRAINT is not there then it will result in multiple rows which will create an ambigous situation.

SQL> INSERT INTO table_1 VALUES ( 1, ‘First Row’ );
SQL> INSERT INTO table_1 VALUES ( 2, ‘Nothing’ );
SQL> INSERT INTO table_2 VALUES ( 2, ‘Second Row’ );

SQL> UPDATE ( SELECT a.c_name a_col, b.c_name b_col
2 FROM table_1 a, table_2 b
3 WHERE a.c_code = b.c_code )
4 SET a_col = b_col
5 /

1 row updated.

SQL> select * from a
2 /

c_code c_name
————– ————————-
1 First Row
2 Second Row

Below are few more variance sql for same purpose, but SQL above gives best chance to optimizer in getting a good execution plan.

SQL> UPDATE table_1 a
2 SET c_name = ( SELECT c_name
3 FROM table_2 b
4 WHERE b.c_code = a.c_code)
5 WHERE EXISTS ( SELECT c_name
6 FROM table_2 b
7 WHERE b.c_code = a.c_code )
8 /

1 row updated.

SQL> UPDATE table_1 a
2 SET c_name = ( SELECT c_name
3 FROM table_2 b
4 WHERE b.c_code = a.c_code )
5 WHERE a.c_code IN ( SELECT c_code
6 FROM table_2 )
7 /

1 row updated.

Little bit funny piece of post!

Hi Guys,

Today I came across one little bit funny piece of post at one of the thread in Oracle Forum.

Member No 1 asked following doubts:

Hi all,
I have the table

List
flag_circ char(1)
impo number(11,3)

and I have to update flag_circ, if the number of decimal of the field impo, are 0 or 1

For example
Impo value
123,444 –> no update
123,44 –> no update
123,4 –> yes update
123 –> yes update

I’m trying those functions like round, mod, trunc, but don’t work!

Some suggestions?

Member No 2 answers:

Use this
declare
cursor c_1 is select rowid row_id from table where (round(number_field,0) = number
or round (number_field,1)= number;
begin
for f_1 in c_1 loop
update table(repplcae by table name here) set flag = ‘Y’ where rowid = f_1.row_id;
end;
/
I think this will work

Member No 3 answers to Member No 2:

It may work but it is horrible.

What would motivate you to declare a cursor and do in a loop what can easily be done with a single update statement?

A desire to write the slowest least efficient code possible?

If you have written code like this at work please do yourself and your employer a favor and get rid of the cursor loop.

There is from 9i onward almost no excuse for ever writing a cursor loop.

Now lets think about it, its not really funny but do tell us what not to do!

Any way my answer was

Hi,

Try this

UPDATE list
SET flag_circ = somthing you want
WHERE INSTR(impo,’.’,1,1) = 0
OR
LENGTH(SUBSTR(impo,INSTR(impo,’.’,1,1)+1,2)) = 1

My tests:
SQL> WITH data_Set AS
2 (
3 SELECT 1 row_no, 1221.4554 text_col FROM dual
4 UNION ALL
5 SELECT 2 row_no, 1221.4554 text_col FROM dual
6 UNION ALL
7 SELECT 3 row_no, 1234 text_col FROM dual
8 UNION ALL
9 SELECT 4 row_no, 2343.5 text_col FROM dual
10 )
11 SELECT row_no FROM data_set
12 WHERE INSTR(text_col,’.’,1,1) = 0
13 OR
14 LENGTH(SUBSTR(text_col,INSTR(text_col,’.’,1,1)+1,2)) = 1
15 /

ROW_NO
———-
3
4

Working Example of REGEXP_LIKE

Scenario From Oracle Forum

I have to select data from table which has the words both ‘help’ and ‘window’

eg: window.open(’help’) this type of data should be selected.

Both the words are mandatory.

Eg:window.open(’hai’) should not be selected.

This is not working
select * from employee_comment WHERE regexp_like(text,’window*help’) ;

Solution which I gave:

WITH data_Set AS
(
SELECT 1 row_no, ’shdhd dhdh window ddjfdh help’ text_col FROM dual
UNION ALL
SELECT 2 row_no, ’shdhd dhdh help window ddjfdh’ text_col FROM dual
UNION ALL
SELECT 3 row_no, ‘window.open(”hai”) ‘ text_col FROM dual
UNION ALL
SELECT 4 row_no, ‘window.open(”help”)’ text_col FROM dual
)
SELECT row_no FROM data_set
WHERE REGEXP_LIKE(text_col,’.*window.*help.*’)
/

Improved solution after feedback from Volder

WITH data_Set AS
(
SELECT 1 row_no, ’shdhd dhdh window ddjfdh help’ text_col FROM dual
UNION ALL
SELECT 2 row_no, ’shdhd dhdh help window ddjfdh’ text_col FROM dual
UNION ALL
SELECT 3 row_no, ‘window.open(”hai”) ‘ text_col FROM dual
UNION ALL
SELECT 4 row_no, ‘window.open(”help”)’ text_col FROM dual
)
SELECT row_no FROM data_set
WHERE REGEXP_LIKE(text_col,’.*window.*help.*|.*help.*window.*’)
/

Working Example of REGEXP_LIKE -2

Scenario From Oracle Forum

This function seems like a good idea for password validation, for example one number and 4-8 characters.

However, this does not work – any ideas?

select
1 from dual where
regexp_like(upper(’PA55WORD’),’^(?=.*\d).{4,8}$’)

Solution which I gave:

WITH data_Set AS
(
SELECT 1 row_no, ‘3434HERE’ text_col FROM dual
UNION ALL
SELECT 2 row_no, ‘EYRERY787′ text_col FROM dual
UNION ALL
SELECT 3 row_no, ‘DHFD67SDSD’ text_col FROM dual
UNION ALL
SELECT 4 row_no, ‘ERERT’ text_col FROM dual
UNION ALL
SELECT 4 row_no, ‘23232′ text_col FROM dual
)
SELECT row_no FROM data_set
WHERE REGEXP_LIKE(UPPER(text_col),’([[:alpha:]]+[[:digit:]]+[[:alpha:]]+)|([[:digit:]]+[[:alpha:]]+)|([[:alpha:]]+[[:digit:]]+)’)
/

Result as follows:
ROW_NO
———-
1
2
3

Deleting duplicate row in certain condition using Join!

Scenario From Oracle Forum

I have a transaction table consists of duplicate records I success to write a sql
which get the duplicate records but unable to write the delete statement to delete
this record.

The sql query is as follows:

Select invoice_no, slip_no, drug_code
from transaction_detail
having count(*)>1
group by invoice_no, slip_no, drug_code

If anyone knows please let me know the delete statement. Thanks in advance.

This is what I think

DELETE transaction_detail td1
WHERE EXISTS ( select ‘x’ FROM
(Select MIN(invoice_no) invoice_no, MIN(slip_no) slip_no, MIN(drug_code) drug_code, MIN(rowid) t_rowid
from transaction_detail
having count(*)>1
group by invoice_no, slip_no, drug_code) td2
WHERE td1.invoice_no = td2.invoice_no
AND td1.slip_no = td2.slip_no
AND td1.drug_code = td2.drug_code
AND td1.rowid td2.t_rowid);

Check out following url for more interesting sql regarding deleting of duplicates:

http://www.oraclebrains.com/?p=115

 

 

 

 

PL/SQL:: New Compound Trigger

The new Compound Trigger make it easier to handle a situation, where we want trigger fired at different point of time to share common data.

Before Compound trigger, to handle such situation we required the use of packages variable so that data can be shared. But this approach gives rise to many complications such as leaking of data from one transaction to another, fragmentation of logic, increase in number of packages and triggers and so on.

Package variable has a scope of session, which may lead to leaking of data from one transaction to another transaction so this need to be taken care manually. Apart from this, maintenance due to increase in number of triggers and packages becomes more complicated. Logical becomes very fragmented as it is spread among number of trigger and packages.

In words of Oracle Documentation

“To achieve the same effect with simple triggers, you had to model the common state with an ancillary package. This approach was both cumbersome to program and subject to memory leak when the triggering statement caused an error and the after-statement trigger did not fire”
The new compound trigger has a declaration section and a section for each of its timing points. All of these sections can access a common PL/SQL state. The common state is established when the triggering statement starts and is destroyed when the triggering statement completes, even when the triggering statement causes an error.

A compound trigger has a declaration section and must have at least one timing-point section.

The declaration section (the first section) declares variables and subprograms that timing-point sections can use. When the trigger fires, the declaration section executes before any timing-point sections execute. Variables and subprograms declared in this section have firing-statement duration.

A compound trigger defined on a table has one or more of the timing-point sections described as follows:

  • Before the triggering statement executes – BEFORE STATEMENT
  • After the triggering statement executes – AFTER STATEMENT
  • Before each row that the triggering statement affects – BEFORE EACH ROW
  • After each row that the triggering statement affects – AFTER EACH ROW

Compound Trigger Skelton code is as follows:

CREATE TRIGGER compound_trigger_jvitm
FOR UPDATE OF amount ON journal_voucher_items
COMPOUND TRIGGER

– Declaration Section
– Variables declared here have firing-statement duration.
Jv_type CONSTANT VARCHAR2(1):=’S’;

BEFORE STATEMENT IS
BEGIN

END BEFORE STATEMENT;

BEFORE EACH ROW IS
BEGIN

END BEFORE EACH ROW;

AFTER EACH ROW IS
BEGIN

END AFTER EACH ROW;
END compound_trigger_jvitm;
/

Few Exceptions:

  • The body of a compound trigger must be a compound trigger block.
  • A compound trigger must be a DML trigger.
  • A compound trigger defined on a view has an INSTEAD OF EACH ROW timing-point section, and no other timing-point section.
  • If the triggering statement affects no rows, and the compound trigger has neither a BEFORE STATEMENT section nor an AFTER STATEMENT section, the trigger never fires.
  • The firing order of compound triggers is not guaranteed. Their firing can be interleaved with the firing of simple triggers.
  • If compound triggers are ordered using the FOLLOWS option, and if the target of FOLLOWS does not contain the corresponding section as source code, the ordering is ignored.
  • :OLD, :NEW, and :PARENT cannot appear in the declaration section, the BEFORE STATEMENT section, or the AFTER STATEMENT section.
  • The declaration section cannot include PRAGMA AUTONOMOUS_TRANSACTION.
  • Only the BEFORE EACH ROW section can change the value of :NEW.

 

 

 

 

SQL Tip::UNION ALL Vs. UNION

The problem with UNION is that it required sorting operation to eliminate duplicate row sets which is added cost for executing a statement, while UNION ALL return all rows without any sorting or duplication checking.

If duplicate rows are not important, using UNION ALL can save cost in terms of expensive sorts, merge and filtering operation.

Example:

SELECT dept_code, expense_code, amount
FROM dept_expenses
WHERE location_code = ‘SA’
UNION
SELECT dept_code, expense_code, amount
FROM dept_expenses
WHERE location_code = ‘TM’

After running the explain plan for above statement it shows me:

PLAN_TABLE_OUTPUT
——————————————–
Plan hash value: 2107484601

——————————————–
| Id | Operation | Name
——————————————–
| 0 | SELECT STATEMENT |
| 1 | SORT UNIQUE |
| 2 | UNION-ALL |
|* 3 | TABLE ACCESS FULL| DEPT_EXPENSES
|* 4 | TABLE ACCESS FULL| DEPT_EXPENSES
——————————————–

SELECT dept_code, expense_code, amount
FROM dept_expenses
WHERE location_code = ‘SA’
UNION ALL
SELECT dept_code, expense_code, amount
FROM dept_expenses
WHERE location_code = ‘TM’

After running the explain plan for above statement it shows me:

PLAN_TABLE_OUTPUT
——————————————-
Plan hash value: 576826761

——————————————-
| Id | Operation | Name
——————————————-
| 0 | SELECT STATEMENT |
| 1 | UNION-ALL |
|* 2 | TABLE ACCESS FULL| DEPT_EXPENSES
|* 3 | TABLE ACCESS FULL| DEPT_EXPENSES
——————————————-

Understanding Oracle Release Number Format

While wandering in the web jungle I came across this note, which I decided to put online as I think it is sort of important info which every person working with Oracle technology should know and will be interested to know.

What exactly the significant of digit is in bold in each release?

Release 10.2.0.2.0

1st Digit: “10” is a major database release number.

2nd Digit: “2” is the database maintenance release number.

3rd Digit: “0” is the application server release number.

4th Digit: “2” identifies a release level specific to a component.

5th Digit: “0” identifies a platform specific release.

To check the Oracle version from the SQL*Plus prompt, issue following sql:

SELECT banner
FROM v$version
WHERE banner LIKE ‘Oracle%’;

Or

SELECT version
FROM product_component_version
WHERE product LIKE ‘Oracle%’;

 

 

 

 

 

 

Understanding Internal DATE Storage

First let’s understand what DUMP function do in Oracle.

The DUMP function shows the datatype, length in bytes and the actual value of each byte for column.

Now let’s do some hands-on:

SQL> create table dummy ( col1 DATE );

Table created.

SQL> INSERT INTO dummy
2 VALUES ( TO_DATE( ‘25/12/1980 12:30:05′,’DD/MM/YYYY HH24:MI:SS’ ) );

1 row created.

SQL> INSERT INTO dummy
2 VALUES ( TO_DATE( ‘25/12/2006 20:25:10′,’DD/MM/YYYY HH24:MI:SS’ ) );

1 row created.

SQL> select TO_CHAR(col1,’DD/MM/YYYY HH24:MI:SS’) col1, DUMP(col1) dumpcol1 FROM dummy

COL1

——————-

DUMPCOL1

——————————————————————————–

25/12/1980 12:30:05

Typ=12 Len=7: 119,180,12,25,13,31,6

25/12/2006 20:25:10

Typ=12 Len=7: 120,106,12,25,21,26,11

Now lets try to understand how Oracle is storing the DATE with above results.

Oracle’s stores DATE in total of 7 bytes. Each byte in it stores values for an element of the DATE as follows:

1st Byte: Stores the century value but before storing it add 100 to it.

2nd Byte: Stores the year and 100 is added to it before storing.

3rd Byte: Stores the Month.

4rth Byte: Stores the Day of the month.

5th Byte: Stores the hours but add 1 before storing it.

6th Byte: Stores the minutes but add 1 before storing it.

7th Byte: Stores the seconds but add 1 before storing it.

<!–[if !vml]–><!–[endif]–>

SQL::Life of a SQL statement in nutshell

Just trying to brush up few basic concept!

What happens when Oracle processes an SQL Statement?

Step 1: Oracle Create a Cursor
For every SQL, first a cursor is created (cursor creation can occur implicitly, or explicitly by declaring a cursor).

Step 2: Oracle Parse SQL Statement

A SQL statement is parsed only if an identical SQL statement does not exist in the library cache (shared pool-System Global Area). In this case, a new shared SQL area is allocated and the statement is parsed (hard parse), otherwise existing stored information in library cache is used (soft parse).

Hard Parsing is the process of

  •   Translating a SQL statement and verify the syntax.
  •   Checking data dictionary to check table and column definitions
  •   Acquiring parse locks on required objects so that their definitions do not change during the statement’s parsing
  •   Checking privileges to access referenced schema objects
  •   Determining the optimal execution plan for the statement
  •   Loading it into a shared SQL area
  •   For distributed statements, routing all or part of the statement to remote nodes that contain referenced data

Stage 3: Describe Results (FOR SELECT STATEMENT ONLY)

The describe phase is used to determine the characteristics (datatypes, lengths, and names) of a query’s result.

Stage 4: Defining Output (FOR SELECT STATEMENT ONLY)

Specify the location, size, and datatype of variables defined to receive each fetched value. Oracle performs datatype conversion if necessary.

Stage 5: Bind Any Variables

At this point, Oracle needs values for any variables listed in the statement;

for example, In SQL statement “SELECT * FROM EMP WHERE EMPNO = p_empno”, Oracle needs a value for p_empno.

This process is called binding variables. A program must specify the location (memory address) where the value can be found.

Stage 6: Execute the Statement

At last, Statement is executed.

Oracle checks if the data it needs for the query are already in the buffer cache. If not, it reads the data off the disk into the buffer cache.
The record(s) that are changed are locked i.e. in case of DELETE or UPDATE statement. Also, before and after images describing the changes are written to the redo log buffer and the rollback segments. The original block receives a pointer to the rollback segment. Then, the data is changed.

For some statements you can specify a number of executions to be performed. This is called array processing. Given n number of executions, the bind and define locations are assumed to be the beginning of an array of size n.

Stage 7: Parallelize the Statement

When using the parallel query option, Oracle can parallelize queries and certain DDL operations. Parallelization causes multiple query servers to perform the work of the query so that the query can complete faster.

Stage 8: Fetch Rows of a Query Result (FOR SELECT STATEMENT ONLY)

Rows are selected and ordered (if requested by the query), and each successive fetch retrieves another row of the result, until the last row has been fetched.

 

 

 

 

Generating same column of a table with ascending order and descending order Simple query

SELECT E1.EMPNO,E2.EMPNO FROM (SELECT ROWNUM r1,empno FROM emp ORDER BY empno DESC)e1,(SELECT ROWNUM r2,empno FROM emp ORDER BY empno ASC) e2 WHERE e1.r1 = e2.r2

 
So many time I am stuck with a situation that we need to write a query which should generate sequence number.

This solution never came to my mind as most of the time I resorted to pipeline function or some dummy table.

Now with the help of David’s solution it can be easily generated with simple sql as follows:

select level seq from dual connect by level <= <number till where you want to generate the sequence>

Example:

<!–[if !vml]–><!–[endif]–>

The simplicity of the solution really impressed me!

Thanks David for sharing it and hope that it will be useful to others!

Where Question was as follows:
Consider Test table have Numb (Number datatype) field. table values are given below.

Table Name: Test
NUMB
1
2
4
7
8
9
12 … This table have more than thousand records like that…..

How to retrive the missing numbers. I’m expecting the output 3,5,6,10,11……..
Could you please give the SQL for this task. Thanks!

One of the member David Grimberg gave following solution.


select level numb from dual connect by level <= (select max(numb) from test)
minus
select numb from test;

I was very impress with this sql because I recognize the worth of this sql to myself and many situation in which I am stuck many times.

So many time I am stuck with a situation that we need to write a query which should generate sequence number.

This solution never came to my mind as most of the time I resorted to pipeline function or some dummy table.

Now with the help of David’s solution it can be easily generated with simple sql as follows:

select level seq from dual connect by level <= <number till where you want to generate the sequence>

Example

SP2-0611: Error enabling STATISTICS report

Today when doing some research on “FAST DUAL”, I came across following error when I try to set autotrace on:

SQL> set autotrace on
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report
SQL>

Anyway I ignored the error and ran my sql.

The result was I could see the execution plan but not statistics as follows.

SQL> select sysdate from dual;

SYSDATE
———
17-DEC-07

Execution Plan
———————————————————-
Plan hash value: 1388734953

—————————————————————–
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
—————————————————————–
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
—————————————————————–

SQL>

I research about it and found out that my user don’t have PLUSTRACE role assign and I just need to assign the role to correct the error.

But when I tried to grant the role, I got another error as follows:

SQL> grant plustrace to scott;
grant plustrace to scott
*
ERROR at line 1:
ORA-01919: role ‘PLUSTRACE’ does not exist

After that I create the PLUSTRACE role by running the plustrce.sql at $ORACLE_HOME/sqlplus/admin as follows:

SQL> @’D:\oracle\product\11.1.0\db_1\sqlplus\admin\plustrce.sql’;
SQL>
SQL> drop role plustrace;
drop role plustrace
*
ERROR at line 1:
ORA-01919: role ‘PLUSTRACE’ does not exist

SQL> create role plustrace;

Role created.

SQL>
SQL> grant select on v_$sesstat to plustrace;

Grant succeeded.

SQL> grant select on v_$statname to plustrace;

Grant succeeded.

SQL> grant select on v_$mystat to plustrace;

Grant succeeded.

SQL> grant plustrace to dba with admin option;

Grant succeeded.

SQL>
SQL> set echo off
SQL>

Then granted the PLUSTRACE role.

SQL> grant plustrace to scott;

Grant succeeded.

SQL>

After granting I closed my old sqlplus window and open the new window and login as scott user.

when I ran my sql, the result was as expected that is explain plan with statistics as follows:

SQL> set autotrace on
SQL> select sysdate from dual;

SYSDATE
———
17-DEC-07

Execution Plan
———————————————————-
Plan hash value: 1388734953

—————————————————————–
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
—————————————————————–
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
—————————————————————–

Statistics
———————————————————-
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
422 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>

So had good end to a day!

Is the Oracle 11g smart enough to ignore the virtual column?

On 13th October 2007, I wrote about “Understanding Virtual Columns“.

Then Ray DeBruyn ask me a very good question, which I totally miss to ask myself when I was writing about it.

What happens if I use:
INSERT INTO myTable VALUES myTableRec
or
UPDATE myTable SET ROW = myTableRec

Assuming I declare myTableRec as myTable%ROWTYPE, is the new feature smart enough to ignore the virtual column?

To find out the answer, I did following:

SQL> create table virtual_col(
2 a number,
3 b number,
4 c number GENERATED ALWAYS AS (a+b) VIRTUAL,
5 d number);

Table created.

SQL> desc virtual_col;
Name Null? Type
—————————————– ——– —————————-

A NUMBER
B NUMBER
C NUMBER
D NUMBER

SQL> insert into virtual_col(1,2,3);
insert into virtual_col(1,2,3)
*
ERROR at line 1:
ORA-00928: missing SELECT keyword

SQL> insert into virtual_col values (1,2,3);
insert into virtual_col values (1,2,3)
*
ERROR at line 1:
ORA-00947: not enough values

SQL> insert into virtual_col values (1,2,3,4);
insert into virtual_col values (1,2,3,4)
*
ERROR at line 1:
ORA-54013: INSERT operation disallowed on virtual columns

SQL> insert into virtual_col(a,b,d) values (1,2,4);

1 row created.

SQL> declare
2 l_rec virtual_col%ROWTYPE;
3 begin
4 UPDATE virtual_col SET ROW = l_rec;
5 end;
6 /
declare
*
ERROR at line 1:
ORA-54017: UPDATE operation disallowed on virtual columns
ORA-06512: at line 4

To my surprise, I found out that oracle 11g is not smart enough to ignore the virtual columns and it gives error when we want to do DML operation rowwise.

We need to mention each columns and its value explicitly to do update or insert.

 

 

 

Going Backend of new feature of 11g related to Sequences

In this post I mention that in 11g we don’t need to use dual table to fetch next value of a sequence and using simple PL/SQL expression the next value can be fetched.

Any way thats the old story and lot of people have wrote about it.

But recently I got to know from Asif blog, what exactly is happening in background.

I follows what he did and confirms that he 100% right!

Created the new sequence

SQL> create sequence new11g_seq;

Sequence created.


Give a name to the trace file that will be generated

SQL> alter session set tracefile_identifier=’new11g_seq’;

Session altered.

Set the Event No and Level till I want to trace

SQL> alter session set events ‘10046 trace name context forever,level 12′;

Session altered.

Run one PL/SQL involving the new sequence as a PL/SQL expression

SQL> declare
2 l_no number;
3 begin
4 l_no := new11g_seq.nextval;
5 end;
6 /

PL/SQL procedure successfully completed.

Set the tracing off:

SQL> alter session set events ‘10046 trace name context off’;

Session altered.

Checked the destination of the trace file:

SQL> select value from v$diag_info where name = ‘Default Trace File’;

VALUE
————————————————————————-

d:\oracle\diag\rdbms\dcs\dcs\trace\dcs_ora_4084_new11g_seq.trc

Now I opened the given trace file and find some interesting fact in following lines:

=====================
PARSING IN CURSOR #3 len=59 dep=0 uid=5 oct=47 lid=5 tim=12331650243 hv=2968413633 ad=’1e1874a8′ sqlid=’b5v90tqsfwtf1′
declare
l_no number;
begin
l_no := new11g_seq.nextval;
end;
END OF STMT
PARSE #3:c=31250,e=81603,p=0,cr=2,cu=0,mis=1,r=0,dep=0,og=1,tim=12331650236
BINDS #3:
=====================
PARSING IN CURSOR #5 len=35 dep=1 uid=5 oct=3 lid=5 tim=12331651486 hv=3823849384 ad=’1e18717c’ sqlid=’9fhs7ymjyqmx8′
Select NEW11G_SEQ.NEXTVAL from dual
END OF STMT
PARSE #5:c=0,e=989,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,tim=12331651479
BINDS #5:
EXEC #5:c=0,e=81,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=12331651654
=====================

Wow!

It means even though oracle has made life easier for programmers as we don’t need to write whole SQL to fetch new sequence number, but from performance point of view and internal process of fetching the sequence nothing has changed

It still converts the PL/SQL expression to SQL and fetch the next value!

 

 

 

REMAINDER Function

I don’t why but I never came across this function, may be because I am so used to using MOD function to get the remainder of two number when one of them is divided by others, so never though of finding any other function doing same thing.

But yesterday I came across this function while surfing on the net and thought may be many of us may be missing it too.

So sharing for those person who have missed it like me…..

REMAINDER(n2,n1)

REMAINDER returns the remainder of n2 divided by n1 . This function takes as arguments any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. Oracle determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that datatype, and returns that datatype. The MOD function is similar to REMAINDER except that it uses FLOOR in its formula, whereas REMAINDER uses ROUND.

 

 

 

 

 

 

 

 

 

 

Creating Primary Key On Duplicate Values

Check out following:

SQL> select * from test;

COL1
———-
1
1
1
1
1

SQL> alter table test add constraint test_idx primary key(col1) disable;

Table altered.

SQL> create index test_idx on test(col1);

Index created.

SQL> alter table test enable novalidate constraint test_idx;

Table altered.

SQL> insert into test values(1);
insert into test values(1)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.TEST_IDX) violated

Understanding DETERMINISTIC Functions or DETERMINISTIC Clause

When working with virtual columns recently I came across the following error:

ORA-30553: The function is not deterministic
Cause: The function on which the index is defined is not deterministic
Action: If the function is deterministic, mark it DETERMINISTIC. If it is not deterministic (it depends on package state, database state, current time, or anything other than the function inputs) then do not create the index. The values returned by a deterministic function should not change even when the function is rewritten or recompiled.

So I though of writing about DETERMINISTIC Functions as I find it very important piece of information when working with function-based indexes, virtual columns or materialized views.

When creating functions (User Defined Functions), we can use DETERMINISTIC keyword or clause to indicate that the function will always return the same output or value for any given set of input argument values any point of time.

We must specify this keyword or clause if you intend to call the function in the function-based index expression, virtual column expression or from the query of a materialized view that is marked REFRESH FAST or ENABLE QUERY REWRITE.

Otherwise it will generate following error:

ORA-30553: The function is not deterministic
Cause: The function on which the index is defined is not deterministic
Action: If the function is deterministic, mark it DETERMINISTIC. If it is not deterministic (it depends on package state, database state, current time, or anything other than the function inputs) then do not create the index. The values returned by a deterministic function should not change even when the function is rewritten or recompiled.

When Oracle Database encounters a deterministic function in one of these contexts, it attempts to use previously calculated results when possible rather than re-executing the function.

Notes from Oracle Doc:
“Do not specify this clause to define a function that uses package variables or that accesses the database in any way that might affect the return result of the function. The results of doing so will not be captured if Oracle Database chooses not to reexecute the function”

Important Note:
If you subsequently change the semantics of the function, then you must manually rebuild all dependent function-based indexes and materialized views.

 

 

 

 

 

 

 

 

 

 

 

 

 

ANSI date literal in Oracle

Lets first create a table as follows:

SQL> CREATE TABLE datetest(
2 datecol DATE);

Table created.

In normal way when entering the date through a literal value we make sure that it matching our NLS_DATE_FORMAT

SQL> ALTER SESSION SET NLS_DATE_FORMAT=’DD/MM/YYYY’;

Session altered.

SQL> INSERT INTO datetest VALUES (’06/12/2006′);

1 row created.

or we use TO_DATE to convert out literal value to date first

SQL> INSERT INTO datetest
VALUES (TO_DATE(’20061206′,’YYYYMMDD’));

1 row created.

Now today I came across another way by using “ANSI date literal”

ANSI – American National Standards Institute

SQL> INSERT INTO datetest VALUES( DATE ‘2006-12-06′);

1 row created.

It contains no time element and is formatted exactly as follows:

DATE ‘YYYY-MM-DD’

 


Inventory

August 2, 2011

What is Inventory

In simple terms it is a stock of items that your business is selling in order to make a living. Almost every non-IT related business has some physical items to sell and hence Inventory is at the heart of almost any business and is central to the planning process.

The considerations in setting it up are at least as complex as those for any other single application. Commonly, Inventory will be installed during the implementation of Order Entry, Purchasing, Manufacturing, or Supply Chain Management but it is an extremely complex module in its own right with several important setup considerations.

Inventory System: 
The simplest function of an Inventory system is to keep track of your items i.e. which item came in and which item went out of inventory and when the item count reaches below a certain threshold value then alert the responsible person to replenish it soon. So how does items go in and out of the warehouse? Let say your company manufactures office chairs. When a customer orders 5 chairs and when you ship them to the customer the count of the item goes down and when you manufacture them internally and when the finished chair is ready and moved to the warehouse the item count increases. An item can be internally consumed too.

An inventory system provides physical management of a company’s stock until it is either used up or sold. It also performs a logical, cataloging function. This stock is referred to by its item number  (usually item number is the segment1 column of mtl_system_items) and has many attributes that affect the transactions that the modules can process against the item.

Every other module within Oracle Applications that needs to name types of objects looks to the Inventory Item Master for details regarding an item. These objects include products ordered through Oracle Order Entry, items invoiced through Oracle Receivables, items purchased through Oracle Purchasing, items paid for through Oracle Payables, items maintained through Oracle Service, and items charged against projects in Oracle Projects.

Every module in the Manufacturing suite (Engineering, Bills of Material, Work in Process,  Master Production Schedule/Material Requirements Planning, and Cost Management) deals with items named in Inventory.

Inventory’s can also track intangibles like magazine subscriptions. The company can sell these items along with physical objects. Assemblies that are never stocked can also be defined in Inventory. A phantom item, for example, represents an intermediate step in manufacture, an assembly that is never stocked in inventory because it is used immediately in a higher-level assembly.

Oracle Inventory’s on-hand balance is increased when an item is bought or made. Inventory relinquishes control and decreases its balance when items are sold or put to their final use within the company. Pads of paper in the stock room are inventory; the same pad of paper on somebody’s desk is not-as far as the company is concerned, it is already used. A new desk in the warehouse belongs to inventory; a desk in somebody’s office is no longer in inventory. Many major purchases, such as plant and equipment, never have an on-hand balance because they are put to their final use
as soon as they are bought.

Inventory Tables:
MTL_CATEGORIES_B 

MTL_CATEGORIES_B is the code combinations table for item categories. Items are grouped into categories with in the context of a category set to provide flexible grouping schemes. The item category is a key flex field with a flex code of MCAT. 

The flexfield structure identifier is also stored in this table.MTL_CATEGORY_SETS_B
MTL_CATEGORY_SETS_B contains the entity definition for category sets. A category set is a categorization scheme for a group of items.Items may be assigned to different categories in different category sets to represent the different groupings of items used for different purposes.
 
An item may be assigned to only one category within a category set, however. STRUCTURE_ID identifies the flexfield structure associated with the category set. Only categories with the same flexfield structure may be grouped into a category set. 

CONTROL_LEVEL defines whether the category set is controlled at the item or the item/organization level. When an item is assigned to an item level category set within the item master organization, the category set assignment is propagated to all other organizations to which the item is assigned.

VALIDATE_FLAG defines whether a list of valid categories is used to validate category usage within the set. Validated category sets will not allow item assignment to the category set in categories that are not in a predefined list of valid categories. Category Sets now support multilingual category set name and description.
MLS is implemented with a pair of tables: MTL_CATEGORY_SETS_B and MTL_CATEGORY_SETS_TL.
 
MTL_ITEM_CATEGORIES: 
MTL_ITEM_CATEGORIES stores the item assignments to categories within a category set. For each category assignment, this table stores the item, the category set, and the category. Items may be assigned to multiple categories and category sets but may be assigned to only one category in a given category set. This table may be populated through the Master Items and Organization Items windows. It can also be populated by performing item assignments when a category set is defined. It is also populated when an item is transferred from engineering to manufacturing.

MTL_ITEM_SUB_INVENTORIES 

MTL_ITEM_SUB_INVENTORIES maintains a listing of subinventories assigned to an inventory or engineering item. These sub inventories make up the list of valid subinventories when transacting this specific item and the user has specified (in the master window) that the item must use subinventories restricted to a pre–defined list.
Oracle Inventory Interfaces
• Customer Item Interface
• Open Item Interface

 Open Replenishment Interface
• Open Transaction Interface
 Cycle Count Open Interface
• Reservations Open Interface 
• Move Orders Open Interface

§  Item import (Item conversion)

    The Item Interface lets you import items into Oracle Inventory. 

 

Pre-requisites:

 

  1. Creating an Organization
  2. Code Combinations
  3. Templates
  4. Defining Item Status Codes
  5. Defining Item Types
     
 
 Interface tables:          
 
  1.                      MTL_SYSTEM_ITEMS_INTERFACE
  2.                      MTL_ITEM_REVISIONS_INTERFACE (If importing revisions)
  3.                      MTL_ITEM_CATEGORIES_INTERFACE (If importing categories)
  4.                      MTL_INTERFACE_ERRORS (View errors after import)
    
Concurrent Program : =>                      Item import
 
In the item import parameters form, for the parameter ‘set process id’, specify the ‘set process id’ value given in the mtl_item_categories_interface table. The parameter ‘Create or Update’ can have any value. Through the import process, we can only create item category assignment(s). Updating or Deletion of item category assignment is not supported.
 
 
Base Tables:             
  1. MTL_SYSTEM_ITEMS_B              
  2. MTL_ITEM_REVISIONS_B
  3. MTL_CATEGORIES_B
  4. MTL_CATEGORY_SETS_B
  5. MTL_ITEM_STATUS          
  6. MTL_ITEM_TEMPLATES
 
 
 
 

Validations:    
      
  1.            Check for valid part_id/segment of the source table.
  2.            Validate part_id/segment1 for master org.
  3.            Validate and translate template id of the source table.
  4.            Check for valid item type.
  5.            Check for valid template id. (Attributes are already set for items, default attributes for that template      i.e., purchasable, stockable, etc )
  6.            Check for valid item status.
  7.            Validate primary uom of the source table.
  8.            Validate attribute values.
  9.            Validate other UOMs of the source table.
  10.            Check for unique item type. Discard the item, if part has non-unique item type.
  11.            Check for description, inv_um uniqueness
  12.            Validate organization id.
  13.            Load master records and category records only if all
  14.            Load child record if no error found.
 
 
 
 
Some important columns that need to populated in the interface tables:
 
MTL_SYSTEM_ITEMS_INTERFACE:
 
PROCESS_FLAG = 1
 
(1= Pending,
2= Assign Complete,
3= Assign/Validation Failed,
4= Validation succeeded; Import failed,
5 = Import in Process,
7 = Import succeeded)
 
 
TRANSACTION_TYPE = ‘CREATE’, ‘UPDATE’
SET_PROCESS_ID = 1
ORGANIZATION_ID
DESCRIPTION
ITEM_NUMBER and/or SEGMENT (n)
MATERIAL_COST
REVISION
TEMPLATE_ID
SUMMARY_FLAG
ENABLED_FLAG
PURCHASING_ITEM_FLAG
SALES_ACCOUNT (defaulted from  MTL_PARAMETERS.SALES_ACCOUNT)
COST_OF_SALES_ACCOUNT (defaulted from MTL_PARAMETERS.COST_OF_SALES_ACCOUNT)
 
 
 
MTL_ITEM_CATEGORIES_INTERFACE:
 
INVENTORY_ITEM_ID or ITEM_NUMBER.
 
ORGANIZATION_ID or ORGANIZATION_CODE or both.
 
TRANSACTION_TYPE = ‘CREATE’ (‘UPDATE’ or ‘DELETE’ is not possible through Item Import).
 
CATEGORY_SET_ID or CATEGORY_SET_NAME or both.
 
CATEGORY_ID or CATEGORY_NAME or both.
 
PROCESS_FLAG = 1
 
SET_PROCESS_ID (The item and category interface records should have the same set_process_id, if
you are importing item and category assignment together)
 
 
 
MTL_ITEM_REVISIONS_INTERFACE:
 
INVENTORY_ITEM_ID or ITEM_NUMBER (Must match the
 
ORGANIZATION_ID or ORGANIZATION_CODE or both
 
REVISION
 
CHANGE_NOTICE
 
ECN_INITIATION_DATE
 
IMPLEMENTATION_DATE
 
IMPLEMENTED_SERIAL_NUMBER
 
EFFECTIVITY_DATE
 
ATTRIBUTE_CATEGORY
 
ATTRIBUTEn
 
REVISED_ITEM_SEQUENCE_ID
 
DESCRIPTION
 
PROCESS_FLAG = 1
 
TRANSACTION_TYPE = ‘CREATE’
 
SET_PROCESS_ID = 1
 
Each row in the mtl_item_revisions_interface table must have the REVISION and
 
EFFECTIVITY_DATE in alphabetical (ASCII sort) and chronological order.

§  Inventory On-hand quantity Interface



This interface lets you import the on hand inventory into Oracle.
    
 
Interface tables:
 
MTL_TRANSACTIONS_INTERFACE
 
MTL_MTL_TRANSACTION_LOTS_INTERFACE (If the item is Lot Controlled)
 
MTL_SERIAL_NUMBERS_INTERFACE (If the item is Serial Controlled)
Concurrent Program:

Launch the Transaction Manager through Interface Manager or explicitly call the API – INV_TXN_MANAGER_PUB.PROCESS_TRANSACTIONS () to launch a dedicated transaction worker to process them.
 
The Transaction Manager picks up the rows to process based on the LOCK_FLAG, TRANSACTION_MODE, and PROCESS_FLAG.
 
Only records with TRANSACTION_MODE of 3, LOCK_FLAG of ’2′, and PROCESS_FLAG of ’1′ will be picked up by the Transaction Manager and assigned to a Transaction Worker.
 
If a record fails to process completely, then PROCESS_FLAG will be set to ’3′ and ERROR_CODE and ERROR_EXPLANATION will be populated with the cause for the error.
 
Base Tables:
 
MTL_ON_HAND_QUANTITIES
 
MTL_LOT_NUMBERS
 
MTL_SERIAL_NUMBERS
 
Validations:
 
Validate organization_id
 
Check if item is assigned to organization
 
Validate disposition_id
 
Check if the item for the org is lot controlled before inserting into the Lots interface table.
 
Check if the item for the org is serial controlled before inserting into Serial interface table.
 
Check if inventory already exists for that item in that org and for a lot.
 
Validate organization_id, organization_code.
 
Validate inventory item id.
 
Transaction period must be open.
 
Some important columns that need to be populated in the interface tables:
 
MTL_TRANSACTIONS_INTERFACE:
 
TRANSACTION_SOURCE_NAME (ANY USER DEFINED VALUE),
 
TRANSACTION_HEADER_ID (MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL)
 
TRANSACTION_INTERFACE_ID (MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL –
 
If item is lot or serial controlled, use this field to link to mtl_transactions_interface otherwise leave it as NULL),
TRANSACTION_DATE,
 
TRANSACTION_TYPE_ID,
 
PROCESS_FLAG
(
         1 = Yet to be processed,
         2 = Processed,
         3= Error
)
 
TRANSACTION_MODE
 (
2 = Concurrent – to launch a dedicated transaction worker to explicitly process a set of transactions,
3 = Background – will be picked up by transaction manager polling process and assigned to
        transaction worker. These will not be picked up until the transaction manager is running
)
 
SOURCE_CODE,
 
SOURCE_HEADER_ID,
 
SOURCE_LINE_ID (Details about the source like Order Entry etc for tracking purposes)
 
TRANSACTION_SOURCE_ID
 
Source Type
Foreign Key Reference
Account
GL_CODE_COMBINATIONS.CODE_COMBINATION_ID
Account Alias
MTL_GENERIC_DISPOSITIONS.DISPOSITION_ID
Job or schedule
WIP_ENTITIES.WIP_ENTITY_ID
Sales Order
MTL_SALES_ORDERS.SALES_ORDER_ID
 
 
ITEM_SEGMENT1 TO 20,
 
TRANSACTION_QTY,
 
TRANSACTION_UOM,
 
SUBINVENTORY_CODE,
 
ORGANIZATION_ID,
 
LOC_SEGMENT1 TO 20.
 
 
MTL_TRANSACTION_LOTS_INTERFACE:
 
TRANSACTION_INTERFACE_ID,
 
LOT_NUMBER,
 
LOT_EXPIRATION_DATE,
 
TRANSACTION_QUANTITY,
 
SERIAL_TRANSACTION_TEMP_ID (This is required for items under both lot and serial control to identify child records in mtl_serial_numbers_interface)
 
 
MTL_SERIAL_NUMBERS_INTERFACE:
 
TRANSACTION_INTERFACE_ID
FM_SERIAL_NUMBER,
 
TO_SERIAL_NUMBER,
 
VENDOR_SERIAL_NUMBER
 
R12 INVENTORY
 
 
Define Key Flexfields.
  1. System Items
  2. Item Categories
Setup Locations
  1. Define Locations
Setup Calendar
  1. Define Calendar
Units of Measure
  1. Define Units of Measure Classes
  2. Define Units of Measure
  3. Define Units of Measure Conversion
Inventory Organizations
  1. Define Organizations
  2. Define Organization Parameters
  3. Define Subinventories
  4. Define Stock Locators
Item Attributes
  1. Define Item Attribute Controls
Categories
  1. Define Category Codes
  2. Define Category Sets
  3. Define Default Category Sets
Status Codes
  1. Define Status Codes
Transactions
  1. Define Transaction Source Types
  2. Define Transaction Types
Profile Options
  1. Define Profile Options
 
RCV_TRANSACTIONS

 
It stores historical information about receiving transactions that you have performed. When you enter a receiving transaction and the receiving transaction processor processes your transaction, the transaction is recorded in this table. Once a row has been inserted into this table, it will never be updated.
When you correct a transaction, the net transaction quantity is maintained in RCV_SUPPLY. The original transaction quantity does not get updated. You can only delete rows from this table using the Purge feature of Oracle Purchasing.
 

RCV_SHIPMENT_HEADERS and RCV_SHIPMENT_LINES

 
When a Ship Confirm is processed, one record is inserted in rcv_shipment_headers and one record is inserted in rcv_shipment_lines for each of the Sales Order Lines. 
The rcv_shipment_lines are linked to the one rcv_shipment_header record by shipment_header_id.
 
When a Shipment Line is received, the Receipt Number is populated in the rcv_shipment_headers record that was created for that Shipment.
Since only one rcv_shipment_headers record is created for each Ship Confirm process and the Receipt Number is also on rcv_shipment_headers record, there can only be one Receipt Number for a specific Shipment. 
 
For example:
 
1. Ship Confirm Sales Order Lines 1, 2, 3, 4, 5
a. The following records are created:
  • One rcv_shipment_header record
  • Five rcv_shipment_lines records

1.    What is item import? How is it done?
A)    The process of converting inventory items from another inventory system, migrating assembly and component items from a legacy manufacturing system, converting purchase items from a custom purchasing system and importing new items from a product data management package into Oracle Inventory. This import mechanism is achieved through a concurrent program called Open Item Interface.

                              Custom programs are executed prior to item interface and this gets data from the external systems into the interface tables such as MTL_SYSTEM_ITEMS _INTERFACE and MTL_ITEM_REVISIONS_INTERFACE. Item Interface program is then run which actually imports the items and revision information from the above mentioned interface face tables into the base tables such as MTL_SYSTEM_ITEMS_B, MTL_ITEM_REVISIONS. Item Interface assigns defaults and validates the data to ensure data integrity before feeding data into base tables.

2. What are Interface and Base Tables?

Interface Tables      :

MTL_SYSTEM_ITEMS_INTERFACE
MTL_ITEM_REVISIONS_INTERFACE (Oracle Defaults)
MTL_ITEM_CATEGORIES_INTERFACE (Oracle has Provided certain default categories, if not specified)
MTL_INTERFACE_ERRORS

Base Tables                   :

MTL_SYSTEM_ITEMS_B (Segment 1 stores Model # of Item)
MTL_ITEM_ATTRIBUTES,
MTL_ITEM_CATEGORIES,    
MTL_ITEM_LOCATIONS,   
MTL_ITEM_REVISIONS
MTL_ITEM_STATUS (Status Active/Engineer etc)
MTL_ITEM_SUB_INVENTORIES

3.  What are Item Attributes?
A)    Attributes are the specific characteristics associated to every item, namely order cost, item status, revision control, COGS account etc.

4.    What are Templates?
Templates are the defined set of attributes that can be used over and over to create similar items. Templates initial definition of items easier. Oracle has provided certain predefined templates such as (ATO MODEL, ATO OPTION CLASS, and FINISHED GOOD etc). Templates can also be User defined .

5)   What are Status Codes?
A)  Statuses are used to provide default values to certain item attributes to control the functionality of an item. Statuses typically default 8 item attributes namely,           BOM allowed, build in WIP, Customer orders enabled, internal orders enabled, invoice enabled, transactable, purchasable, stockable.
      Different status types include Active, Inactive, Engineer, obsolete, Phase-out, Prototype, OPM.

6.  What are Categories and Category Sets?
A) Category is a code used to group items with similar characteristics such as plastics, metals or glass items etc.

     A subset of categories grouped together is termed as a Category set. Typical category sets include purchasing, materials, costing and planning.

8.   What are Lot Numbers and Serial Numbers?
A)  Lot number is a number that identifies a specific batch of items.

     Serial Number is a number assigned to each unit of an item and used to track the item.

 9.  What are Locators?
A) A locator is a physical area with in the sub inventory where you store material such as a row, aisle, shelf, or a bin etc.

10.    What is a Sub Inventory?
It is a subdivision of an organization representing a physical area or a logical  grouping of items such as store room or a receiving dock.

11.      What are the flexfields in Inventory module?
A)  Item Key Flexfield and Category Key Flexfield.

12. While importing items from the legacy system through items interface what profile options do u set.
There are two profile options that we need to check, before running the Item Import. They are

i) PRIMARY_UNIT_OF_MEASURE from INV: Define Primary Unit of Measure

ii) INVENTORY_ITEM_STATUS_CODE from INV: Define Item Status


ORACLE APPS TABLES

July 31, 2011

Receivables (TCA Tables)

 

HZ_PARTIES

PARTY_ID, PARTY_NUMBER
PARTY_NAME
PARTY_TYPE
VALIDATED_FLAG
ORIG_SYSTEM_REFERENCE, SIC_CODE
HQ_BRANCH_IND
CUSTOMER_KEY
TAX_REFERENCE
JGZZ_FISCAL_CODE, DUNS_NUMBER
TAX_NAME

HZ_CUST_ACCOUNTS

CUST_ACCOUNT_ID, PARTY_ID
ACCOUNT_NUMBER
ORIG_SYSTEM_REFERENCE , STATUS
CUSTOMER_TYPE
CUSTOMER_CLASS_CODE, PRIMARY_SALESREP_ID, SALES_CHANNEL_CODE,ORDER_TYPE_ID
PRICE_LIST_ID
SUBCATEGORY_CODE, TAX_CODE
FOB_POINT
FREIGHT_TERM
SHIP_PARTIAL
SHIP_VIA
WAREHOUSE_ID
PAYMENT_TERM_ID
TAX_HEADER_LEVEL_FLAG,  TAX_ROUNDING_RULE
CURRENT_BALANCE
ACCOUNT_ACTIVATION_DATE
CREDIT_CLASSIFICATION_CODE

HZ_PARTY_SITES

PARTY_SITE_ID
PARTY_ID
LOCATION_ID
PARTY_SITE_NUMBER
ORIG_SYSTEM_REFERENCE
START_DATE_ACTIVE
REGION ,  MAILSTOP
CUSTOMER_KEY_OSM
IDENTIFYING_ADDRESS_FLAG 

HZ_LOCATIONS

LOCATION_ID
ORIG_SYSTEM_REFERENCE, COUNTRY
ADDRESS1
ADDRESS2
ADDRESS3
ADDRESS4
CITY
POSTAL_CODE
STATE
PROVINCE
COUNTY

HZ_CUST_ACCT_RELATE_ALL

RELATED_CUST_ACCOUNT_ID
CUST_ACCOUNT_ID
RELATIONSHIP_TYPE
COMMENTS
CUSTOMER_RECIPROCAL_FLAG
STATUS
ORG_ID
BILL_TO_FLAG
SHIP_TO_FLAG
OBJECT_VERSION_NUMBER
CREATED_BY_MODULE
APPLICATION_ID
PAYMENT_METHOD_LOOKUP_CODE

 

HZ_CUST_ACCT_SITES_ALL

CUST_ACCT_SITE_ID
CUST_ACCOUNT_ID
PARTY_SITE_ID
ORIG_SYSTEM_REFERENCE, STATUS
ORG_ID
BILL_TO_FLAG
MARKET_FLAG

HZ_CUST_SITE_USES_ALL

SITE_USE_ID
CUST_ACCT_SITE_ID
SITE_USE_CODE
PRIMARY_FLAG
STATUS
CONTACT_ID
BILL_TO_SITE_USE_ID
ORIG_SYSTEM_REFERENCE
SIC_CODE
PAYMENT_TERM_ID
GSA_INDICATOR
SHIP_PARTIAL
SHIP_VIA
FOB_POINT
ORDER_TYPE_ID
PRICE_LIST_ID
FREIGHT_TERM
WAREHOUSE_ID
TERRITORY_ID
TAX_CODE, LOCATION

 

HZ_ORGANIZATION_PROFILES

ORGANIZATION_PROFILE_ID
PARTY_ID
ORGANIZATION_NAME
DUNS_NUMBER
ENQUIRY_DUNS
CEO_NAME
CEO_TITLE
PRINCIPAL_NAME
PRINCIPAL_TITLE
LEGAL_STATUS
CONTROL_YR
EMPLOYEES_TOTAL
HQ_BRANCH_IND
BRANCH_FLAG
OOB_IND
LINE_OF_BUSINESS

 

HZ_CONTACT_POINTS

CONTACT_POINT_ID, CONTACT_POINT_TYPE
eg.. EMAIL, WEB, PHONE, STATUS, OWNER_TABLE_NAME eg.. HZ_PARTIES ,HZ_PARTY_SITES,OWNER_TABLE_ID ie.. ID of the above Table, PRIMARY_FLAG
ORIG_SYSTEM_REFERENCE
EDI_TRANSACTION_HANDLING
EDI_ID_NUMBER
EDI_PAYMENT_METHOD
EDI_PAYMENT_FORMAT
EDI_REMITTANCE_METHOD
EDI_REMITTANCE_INSTRUCTION
EDI_TP_HEADER_ID
EDI_ECE_TP_LOCATION_CODE
EMAIL_FORMAT
EMAIL_ADDRESS

HZ_ORG_CONTACT_ROLES

ORG_CONTACT_ROLE_ID
ORG_CONTACT_ID
ROLE_TYPE
CREATED_BY
ROLE_LEVEL
PRIMARY_FLAG
CREATION_DATE
ORIG_SYSTEM_REFERENCE
PRIMARY_CONTACT_PER_ROLE_TYPE
STATUS
OBJECT_VERSION_NUMBER
CREATED_BY_MODULE
APPLICATION_ID

HZ_CUST_PROFILE_CLASSES

PROFILE_CLASS_ID
NAME
STATUS
COLLECTOR_ID
CREDIT_ANALYST_ID
CREDIT_CHECKING
TOLERANCE
DISCOUNT_TERMS
DUNNING_LETTERS
INTEREST_CHARGES
PREF_FUNCTIONAL_CURRENCY
STATEMENTS
CREDIT_BALANCE_STATEMENTS
DESCRIPTION
REVIEW_CYCLE_DAYS
OUTSIDE_REPORTING
STANDARD_TERMS
OVERRIDE_TERMS
DUNNING_LETTER_SET_ID

 HZ_ORG_CONTACTS

ORG_CONTACT_ID
PARTY_SITE_ID
PARTY_RELATIONSHIP_ID
TITLE
JOB_TITLE
MAIL_STOP
CONTACT_KEY
COMMENTS
CONTACT_NUMBER
DEPARTMENT_CODE
DEPARTMENT
DECISION_MAKER_FLAG
JOB_TITLE_CODE
MANAGED_BY
REFERENCE_USE_FLAG
RANK
ORIG_SYSTEM_REFERENCE
NATIVE_LANGUAGE
OTHER_LANGUAGE_1
OTHER_LANGUAGE_2
MAILING_ADDRESS_ID
MATCH_GROUP_ID
STATUS
OBJECT_VERSION_NUMBER
CREATED_BY_MODULE
APPLICATION_ID

HZ_RELATIONSHIPS

RELATIONSHIP_ID
RELATIONSHIP_TYPE
SUBJECT_ID
SUBJECT_TYPE
SUBJECT_TABLE_NAME
OBJECT_ID
OBJECT_TYPE
OBJECT_TABLE_NAME
PARTY_ID
RELATIONSHIP_CODE
DIRECTIONAL_FLAG
COMMENTS
START_DATE
END_DATE
STATUS
CONTENT_SOURCE_TYPE
OBJECT_VERSION_NUMBER
CREATED_BY_MODULE
APPLICATION_ID
DIRECTION_CODE
PERCENTAGE_OWNERSHIP
ACTUAL_CONTENT_SOURCE

 HZ_CUST_PROFILE_AMTS

CUST_ACCT_PROFILE_AMT_ID
CUST_ACCOUNT_PROFILE_ID
CURRENCY_CODE
TRX_CREDIT_LIMIT
OVERALL_CREDIT_LIMIT
MIN_DUNNING_AMOUNT
MIN_DUNNING_INVOICE_AMOUNT
MAX_INTEREST_CHARGE
MIN_STATEMENT_AMOUNT
AUTO_REC_MIN_RECEIPT_AMOUNT
INTEREST_RATE
MIN_FC_BALANCE_AMOUNT
MIN_FC_INVOICE_AMOUNT
CUST_ACCOUNT_ID
SITE_USE_ID
EXPIRATION_DATE
OBJECT_VERSION_NUMBER

HZ_CUSTOMER_PROFILES

CUST_ACCOUNT_PROFILE_ID
CUST_ACCOUNT_ID
SITE_USE_ID
PROFILE_CLASS_ID
STATUS
COLLECTOR_ID
CREDIT_ANALYST_ID
CREDIT_CHECKING
NEXT_CREDIT_REVIEW_DATE
TOLERANCE
DISCOUNT_TERMS
DUNNING_LETTERS
INTEREST_CHARGES
PREF_FUNCTIONAL_CURRENCY
SEND_STATEMENTS
CREDIT_BALANCE_STATEMENTS
CREDIT_HOLD
CREDIT_RATING
RISK_CODE
STANDARD_TERMS
OVERRIDE_TERMS
DUNNING_LETTER_SET_ID
INTEREST_PERIOD_DAYS
PAYMENT_GRACE_DAYS
DISCOUNT_GRACE_DAYS
STATEMENT_CYCLE_ID 

RA_SALESREPS

SALESREP_ID
SALES_CREDIT_TYPE_ID
NAME
SALESREP_NUMBER
STATUS
START_DATE_ACTIVE
END_DATE_ACTIVE
SET_OF_BOOKS_ID
ORG_ID
EMAIL_ADDRESS
ASSIGNED_TO_USER_ID
COST_CENTER
CHARGE_TO_COST_CENTER , PERSON_ID
TYPE
COMMISSIONABLE_FLAG

AR_LOCATION_COMBINATIONS

LOCATION_ID
LOCATION_STRUCTURE_ID
ENABLED_FLAG
LOCATION_ID_SEGMENT_1
LOCATION_ID_SEGMENT_2
LOCATION_ID_SEGMENT_3
LOCATION_ID_SEGMENT_4
LOCATION_ID_SEGMENT_5
LOCATION_ID_SEGMENT_6
LOCATION_ID_SEGMENT_7
LOCATION_ID_SEGMENT_8
LOCATION_ID_SEGMENT_9
LOCATION_ID_SEGMENT_10

AR_LOCATION_VALUES

LOCATION_SEGMENT_ID
LOCATION_STRUCTURE_ID
LOCATION_SEGMENT_QUALIFIER
LOCATION_SEGMENT_VALUE
LOCATION_SEGMENT_DESCRIPTION
PARENT_SEGMENT_ID
LOCATION_SEGMENT_USER_VALUE
TAX_ACCOUNT_CCID
ORG_ID
INTERIM_TAX_CCID
ADJ_CCID
EDISC_CCID
UNEDISC_CCID

AR_LOCATION_RATES

LOCATION_RATE_ID
LOCATION_SEGMENT_ID
TAX_RATE
OVERRIDE_STRUCTURE_ID
FROM_POSTAL_CODE
TO_POSTAL_CODE
START_DATE
END_DATE
REQUEST_ID
PROGRAM_APPLICATION_ID
PROGRAM_ID
PROGRAM_UPDATE_DATE
LAST_UPDATE_LOGIN
OVERRIDE_RATE1~10

AR_SALES_TAX

SALES_TAX_ID
LOCATION_ID
TAX_RATE
LOCATION1_RATE
LOCATION2_RATE
LOCATION3_RATE, ATTRIBUTE_CATEGORY
ATTRIBUTE1~10
RATE_CONTEXT
ENABLED_FLAG
START_DATE
END_DATE
FROM_POSTAL_CODE
TO_POSTAL_CODE
TAX_ACCOUNT    

AP_INVOICES_ALL                => INVOICE PAYMENT

AP_PAYMENT_SCHEDULES_ALL => INVOICE PAYMENT

AP_INV_SELECTION_CRITERIA_ALL => INSTRUCTIONS AND BATCHES

AP_INVOICE_PAYMENTS_ALL => INVOICE PAYMENT

AP_PAYMENT_DISTRIBUTIONS_ALL

AP_BANK_ACCOUNTS => SUPPLIER AND CUSTOMER BANK ACCOUNTS INFORMATION

AP_BANK_ACCT_USES_ALL     => SUPPLIER AND CUSTOMER BANK ACCOUNTS INFORMATION

AP_BANK_BRANCHES ,AP_BANK_ACCOUNTS => BANK DETAILS IRRESPECTIVE OF SUPPLIER OR INTERNAL BANKS

AP_CHECKS_ALL        => PAYMENTS

AP_CHECK_STOCKS_ALL => PAYMENT DOCUMENTS

AP_CHECK_FORMATS

 

 

AP_SELECTED_INVOICES_ALL

AP_SELECTED_INVOICES_ALL is a temporary table that stores information about invoices selected for payment in a payment batch.Your Oracle Payables application inserts into this table after you initiate a payment batch. There will be one row for each invoice that Payables selects for payment

in the current payment batch. When you build payments in a payment batch, your Oracle Payables application uses information in this table to create rows in AP_SELECTED_INVOICE_CHECKS. Information from this table appears in the Modify Payment Batch window.


AP_SELECTED_INVOICE_CHECKS_ALL

AP_SELECTED_INVOICE_CHECKS_ALL is a temporary table that stores payment information during a payment batch. Your Oracle Payables application inserts into this table when you build payments in a payment batch. There will be one row for each payment issued during the current payment batch.

When you confirm a payment batch, your Oracle Payables application inserts these payments into AP_CHECKS_ALL and creates a payment file.

Within a payment batch, SELECTED_CHECK_ID in this table joins with PRINT_SELECTED_CHECK_ID and PAY_SELECTED_CHECK_ID in AP_SELECTED_INVOICES

to associate a selected invoice with its payment.

 

 

R12 Payables New Tables:

Table Names Usage
CE_BANK_ACCOUNTS Internal Bank  Accounts
CE_PAYMENT_DOCUMENTS Payment  Documents to be used for Printed type Payments
IBY_EXTERNAL_PAYEES_ALL Payee (Trading  Partner Info for paying invoices
IBY_PMT_INSTR_USES_ALL Used for Remit  to Bank Accounts
AP_INVOICE_LINES_ALL Invoice Lines
AP_ALLOCATION_RULES Invoice Lines
AP_INVOICE_LINES_ALL Invoice Lines
AP_ALLOCATION_RULES Invoice Lines
AP_ALLOCATION_RULE_LINES Invoice Lines
AP_ALLOCATION_RULE_LINES_GT Global Temp  Table for Invoice Lines – Allocations
AP_SELF_ASSESSED_TAX_DIST_ALL E-Business Tax
AP_PRODUCT_REGISTRATIONS Invoice Lines
AP_PRODUCT_SETUP Multi Org  Access Control
AP_TOLERANCE_TEMPLATES Support of  complex PO Contracts
AP_NEGOTIATION_HIST Invoice  Requests
AP_NEGOTIATION_HIST Holds  Resolution Workflow

 

Changed Tables

Table Name Feature Area Brief Description of
Change
AP_INVOICES_ALL Invoice Lines,
eBusiness Tax, Payments, SLA, Complex Work
POs…
Numerous  columns were added to this table due to almost all the projects done for R12, for list of columns added please refer to the TDD of the respective projects.
AP_INVOICE_DISTRIBUTIONS_ALL Invoice Lines,
eBusiness Tax…
Number of columns were added to this table due to almost all the projects done for R12, for list of columns added please refer to the TDD of the respective projects.
AP_INV_APRVL_HIST_ALL Holds  resolution workflow Hold_ID column  was added.
AP_CHECKS_ALL Payables  Payments Data Some new columns are added to AP_CHECKS_ALL table for maintaining real time synchronization with Oracle Payments and Cash Managements. Some existing
columns from the same tables have been made obsolete.
AP_HOLDS_ALL Holds  Resolution Workflow New Columns  Added were: Hold_id and wf_status.
AP_HOLD_CODES Holds  Resolution Workflow New Columns  Added were: hold_instruction, wait_before_notify_days, reminder_days
AP_INV_APRVL_HIST_ALL Holds
Resolution and Invoice Approval Workflow
This will be  used to record history for both the Invoice level Approvals and Invoice Line  level approvals. New Columns Added were:line_number, hold_id and history_type
AP_APINV_APPROVERS Invoice
Approval Workflow
New Columns  Added were:child_process_item_key, child_process_item_type, item_type and
item_key.

 

 

Obsolete Tables

Table Name Feature Area Replaced By
AP_BANK_BRANCHES Bank/Bank Branches CE_BANK_BRANCHES_V
AP_BANK_ACCOUNTS_ALL Bank Accounts
including Internal and External
CE_BANK_USES_OU_V/IBY_EXT_BANK_ACCOUNTS_V
AP_BANK_ACCOUNTS_USES_ALL Remit to Bank
Account Uses
AP_CHECK_STOCKS_ALL Payments  Documents CE_PAYMENT_DOCUMENTS
AP_CHECK_FORMATS Payments  Format
AP_TAX_RECVRY_RULES_ALL EBusiness Tax Replaced by relevant EBTax tables/views.
AP_TAX_RECVRY_RATES_ALL EBusiness Tax Replaced by relevant EBTax tables/views.
AP_CHRG_ALLOCATIONS_ALL Invoice Lines
- Allocations
This feature is replaced by distributions itself, distributions itself represent the
allocation of charges.
AP_TAX_DERIVATIONS EBusiness Tax Replaced by relevant EBTax tables/views.
AP_LINE_APRVL_HIST_ALL Invoice  Approval Workflow AP_INV_APRVL_HIST_ALL

 

Purchasing Tables: 

PO_VENDORS

VENDOR_ID
VENDOR_NAME
SUMMARY_FLAG
ENABLED_FLAG
EMPLOYEE_ID
VENDOR_TYPE_LOOKUP_CODE
CUSTOMER_NUM
ONE_TIME_FLAG
PARENT_VENDOR_ID
MIN_ORDER_AMOUNT
SHIP_TO_LOCATION_ID
BILL_TO_LOCATION_ID
SHIP_VIA_LOOKUP_CODE
FREIGHT_TERMS_LOOKUP_CODE
FOB_LOOKUP_CODE
TERMS_ID
SET_OF_BOOKS_ID
CREDIT_STATUS_LOOKUP_CODE
CREDIT_LIMIT
ALWAYS_TAKE_DISC_FLAG
PAY_DATE_BASIS_LOOKUP_CODE
PAY_GROUP_LOOKUP_CODE
PAYMENT_PRIORITY
INVOICE_CURRENCY_CODE
PAYMENT_CURRENCY_CODE
INVOICE_AMOUNT_LIMIT
EXCHANGE_DATE_LOOKUP_CODE
HOLD_ALL_PAYMENTS_FLAG
HOLD_FUTURE_PAYMENTS_FLAG
HOLD_REASON

PO_VENDOR_SITES_ALL   

VENDOR_SITE_ID
VENDOR_ID
VENDOR_SITE_CODE
PURCHASING_SITE_FLAG
RFQ_ONLY_SITE_FLAG
PAY_SITE_FLAG
ATTENTION_AR_FLAG
ADDRESS_LINE1
ADDRESS_LINE2
ADDRESS_LINE3
CITY
STATE
ZIP
PROVINCE
COUNTRY
AREA_CODE
PHONE
CUSTOMER_NUM
SHIP_TO_LOCATION_ID
BILL_TO_LOCATION_ID
SHIP_VIA_LOOKUP_CODE
FREIGHT_TERMS_LOOKUP_CODE
FOB_LOOKUP_CODE
INACTIVE_DATE
FAX
FAX_AREA_CODE
TELEX
PAYMENT_METHOD_LOOKUP_CODE

PO_VENDOR_CONTACTS

VENDOR_CONTACT_ID
VENDOR_SITE_ID
INACTIVE_DATE
FIRST_NAME
MIDDLE_NAME
LAST_NAME
PREFIX
TITLE
MAIL_STOP
AREA_CODE
PHONE
DEPARTMENT
EMAIL_ADDRESS
URL
ALT_AREA_CODE
ALT_PHONE
FAX_AREA_CODE
FAX

PO_REQUISITION_HEADERS_ALL

REQUISITION_HEADER_ID
PREPARER_ID
SEGMENT1
SUMMARY_FLAG
ENABLED_FLAG
START_DATE_ACTIVE
END_DATE_ACTIVE
DESCRIPTION
AUTHORIZATION_STATUS
NOTE_TO_AUTHORIZER
TYPE_LOOKUP_CODE
TRANSFERRED_TO_OE_FLAG
ON_LINE_FLAG
PRELIMINARY_RESEARCH_FLAG
RESEARCH_COMPLETE_FLAG
PREPARER_FINISHED_FLAG
PREPARER_FINISHED_DATE
AGENT_RETURN_FLAG
AGENT_RETURN_NOTE
CANCEL_FLAG

PO_REQUISITION_LINES_ALL

REQUISITION_LINE_ID
REQUISITION_HEADER_ID
LINE_NUM
LINE_TYPE_ID
CATEGORY_ID
ITEM_DESCRIPTION
UNIT_MEAS_LOOKUP_CODE
UNIT_PRICE
QUANTITY
DELIVER_TO_LOCATION_ID
TO_PERSON_ID
SOURCE_TYPE_CODE
ITEM_ID
ITEM_REVISION
QUANTITY_DELIVERED
SUGGESTED_BUYER_ID
ENCUMBERED_FLAG
RFQ_REQUIRED_FLAG
NEED_BY_DATE
LINE_LOCATION_ID

PO_REQ_DISTRIBUTIONS_ALL

DISTRIBUTION_ID
REQUISITION_LINE_ID
SET_OF_BOOKS_ID
CODE_COMBINATION_ID
REQ_LINE_AMOUNT
REQ_LINE_QUANTITY
ENCUMBERED_FLAG
GL_ENCUMBERED_DATE
GL_ENCUMBERED_PERIOD_NAME

PO_HEADERS_ALL

PO_HEADER_ID
TYPE_LOOKUP_CODE
SEGMENT1
VENDOR_ORDER_NUM
SUMMARY_FLAG
ENABLED_FLAG
START_DATE_ACTIVE
END_DATE_ACTIVE
VENDOR_ID
VENDOR_SITE_ID
VENDOR_CONTACT_ID
SHIP_TO_LOCATION_ID
BILL_TO_LOCATION_ID
AGENT_ID
TERMS_ID
SHIP_VIA_LOOKUP_CODE
FOB_LOOKUP_CODE

PO_LINES_ALL

PO_LINE_ID
PO_HEADER_ID
LINE_TYPE_ID
LINE_NUM
ITEM_ID
ITEM_REVISION
CATEGORY_ID
ITEM_DESCRIPTION
UNIT_MEAS_LOOKUP_CODE, QUANTITY_COMMITTED , COMMITTED_AMOUNT
ALLOW_PRICE_OVERRIDE_FLAG
NOT_TO_EXCEED_PRICE
LIST_PRICE_PER_UNIT
UNIT_PRICE
QUANTITY

PO_DISTRIBUTIONS_ALL

PO_DISTRIBUTION_ID
PO_HEADER_ID
PO_LINE_ID
LINE_LOCATION_ID
SET_OF_BOOKS_ID
CODE_COMBINATION_ID
QUANTITY_ORDERED
PO_RELEASE_ID
QUANTITY_DELIVERED,DESTINATION_TYPE_CODE, DESTINATION_ORGANIZATION_ID, DESTINATION_SUBINVENTORY

PO_LINE_LOCATIONS_ALL

LINE_LOCATION_ID
PO_HEADER_ID
PO_LINE_ID
QUANTITY
SHIP_TO_LOCATION_ID
NEED_BY_DATE
PROMISED_DATE
QUANTITY_RECEIVED
QUANTITY_ACCEPTED
QUANTITY_REJECTED
QUANTITY_BILLED
QUANTITY_CANCELLED
UNIT_MEAS_LOOKUP_CODE
PO_RELEASE_ID

PO_RELEASES_ALL

PO_RELEASE_ID
PO_HEADER_ID
RELEASE_NUM
AGENT_ID
RELEASE_DATE
REVISION_NUM
HOLD_DATE,HOLD_B,Y, ACCEPTANC_REQUIRED_FLAG ,APPROVED_FLAG , APPROVED_DATE
PRINT_COUNT
PRINTED_DATE
ACCEPTANCE_DUE_DATE

RCV_SHIPMENT_HEADERS

SHIPMENT_HEADER_ID
RECEIPT_SOURCE_CODE
VENDOR_ID
VENDOR_SITE_ID
ORGANIZATION_ID
SHIPMENT_NUM
RECEIPT_NUM
SHIP_TO_LOCATION_ID
BILL_OF_LADING
PACKING_SLIP
SHIPPED_DATE
FREIGHT_CARRIER_CODE
EXPECTED_RECEIPT_DATE
EMPLOYEE_ID
NUM_OF_CONTAINERS
WAYBILL_AIRBILL_NUM
COMMENTS

RCV_SHIPMENT_LINES

SHIPMENT_LINE_ID
SHIPMENT_HEADER_ID
LINE_NUM
CATEGORY_ID
QUANTITY_SHIPPED
QUANTITY_RECEIVED
UNIT_OF_MEASURE
ITEM_DESCRIPTION
ITEM_ID
ITEM_REVISION
VENDOR_ITEM_NUM
VENDOR_LOT_NUM
UOM_CONVERSION_RATE
SHIPMENT_LINE_STATUS_CODE
SOURCE_DOCUMENT_CODE
PO_HEADER_ID
PO_RELEASE_ID
PO_LINE_ID
PO_LINE_LOCATION_ID
PO_DISTRIBUTION_ID
REQUISITION_LINE_ID
REQ_DISTRIBUTION_ID

RCV_TRANSACTIONS

TRANSACTION_ID
TRANSACTION_TYPE
TRANSACTION_DATE
QUANTITY
UNIT_OF_MEASURE
SHIPMENT_HEADER_ID
SHIPMENT_LINE_ID
USER_ENTERED_FLAG
INTERFACE_SOURCE_CODE
INTERFACE_SOURCE_LINE_ID
INV_TRANSACTION_ID
SOURCE_DOCUMENT_CODE
DESTINATION_TYPE_CODE
PRIMARY_QUANTITY
PRIMARY_UNIT_OF_MEASURE
UOM_CODE
EMPLOYEE_ID
PARENT_TRANSACTION_ID
PO_HEADER_ID
PO_RELEASE_ID
PO_LINE_ID
PO_LINE_LOCATION_ID
PO_DISTRIBUTION_ID
PO_REVISION_NUM
REQUISITION_LINE_ID

 

HR Tables:

HR_ORGANIZATION_INFORMATION

ORG_INFORMATION_ID
ORGANIZATION_ID
ORG_INFORMATION_CONTEXT
ORG_INFORMATION1 ~ 20 

HR_LEGAL_ENTITIES

ORGANIZATION_ID
BUSINESS_GROUP_ID
NAME
DATE_FROM
DATE_TO
SET_OF_BOOKS_ID
VAT_REGISTRATION_NUMBER

 

HR_ALL_ORGANIZATION_UNITS

ORGANIZATION_ID
NAME
BUSINESS_GROUP_ID
LOCATION_ID
DATE_FROM
INTERNAL_EXTERNAL_FLAG
INTERNAL_ADDRESS_LINE
TYPE 

HR_LOCATIONS_ALL

LOCATION_ID
LOCATION_CODE
ADDRESS_LINE_1
ADDRESS_LINE_2
ADDRESS_LINE_3
COUNTRY
DESCRIPTION
INVENTORY_ORGANIZATION_ID
OFFICE_SITE_FLAG
RECEIVING_SITE_FLAG
SHIP_TO_SITE_FLAG
BILL_TO_SITE_FLAG, SHIP_TO_LOCATION_ID
POSTAL_CODE
STYLE
DESIGNATED_RECEIVER_ID
IN_ORGANIZATION_FLAG
INACTIVE_DATE 

HR_OPERATING_UNITS

ORGANIZATION_ID
BUSINESS_GROUP_ID
NAME
DATE_FROM
DATE_TO
LEGAL_ENTITY_ID
SET_OF_BOOKS_ID 

ORG_ORGANIZATION_DEFINITIONS

ORGANIZATION_ID
BUSINESS_GROUP_ID
USER_DEFINITION_ENABLE_DATE
DISABLE_DATE
ORGANIZATION_CODE
ORGANIZATION_NAME
SET_OF_BOOKS_ID
CHART_OF_ACCOUNTS_ID
INVENTORY_ENABLED_FLAG
OPERATING_UNIT
LEGAL_ENTITY

                            Inventory Tables:

MTL_PARAMETERS

ORGANIZATION_ID
ORGANIZATION_CODE
MASTER_ORGANIZATION_ID
CALENDAR_CODE
DEFAULT_ATP_RULE_ID
DEFAULT_PICKING_RULE_ID
DEFAULT_LOCATOR_ORDER_VALUE
DEFAULT_SUBINV_ORDER_VALUE
NEGATIVE_INV_RECEIPT_CODE
STOCK_LOCATOR_CONTROL_CODE
MATL_INTERORG_TRANSFER_CODE
INTERORG_TRNSFR_CHARGE_PERCENT
SOURCE_ORGANIZATION_ID
SOURCE_SUBINVENTORY
SOURCE_TYPE
SERIAL_NUMBER_TYPE
AUTO_SERIAL_ALPHA_PREFIX
START_AUTO_SERIAL_NUMBER
AUTO_LOT_ALPHA_PREFIX
LOT_NUMBER_UNIQUENESS

MTL_SECONDARY_INVENTORIES

SECONDARY_INVENTORY_NAME
ORGANIZATION_ID
DESCRIPTION
SUBINVENTORY_TYPE
ASSET_INVENTORY
QUANTITY_TRACKED
INVENTORY_ATP_CODE
AVAILABILITY_TYPE
RESERVABLE_TYPE
LOCATOR_TYPE
PICKING_ORDER
DROPPING_ORDER
LOCATION_ID
STATUS_ID
DEFAULT_LOC_STATUS_ID
LPN_CONTROLLED_FLAG
PICK_METHODOLOGY
CARTONIZATION_FLAG
PREPROCESSING_LEAD_TIME
PROCESSING_LEAD_TIME
POSTPROCESSING_LEAD_TIME
SOURCE_TYPE
SOURCE_SUBINVENTORY
SOURCE_ORGANIZATION_ID
DEFAULT_COST_GROUP_ID
DEFAULT_COUNT_TYPE_CODE 

MTL_ITEM_SUB_INVENTORIES 

INVENTORY_ITEM_ID
ORGANIZATION_ID
SECONDARY_INVENTORY
PRIMARY_SUBINVENTORY_FLAG
PICKING_ORDER
MIN_MINMAX_QUANTITY
MAX_MINMAX_QUANTITY
INVENTORY_PLANNING_CODE
FIXED_LOT_MULTIPLE
MINIMUM_ORDER_QUANTITY
MAXIMUM_ORDER_QUANTITY
SOURCE_TYPE
SOURCE_ORGANIZATION_ID
SOURCE_SUBINVENTORY
ENCUMBRANCE_ACCOUNT
PREPROCESSING_LEAD_TIME
PROCESSING_LEAD_TIME
POSTPROCESSING_LEAD_TIME 

MTL_ITEM_LOCATIONS

INVENTORY_LOCATION_ID
ORGANIZATION_ID
SUBINVENTORY_CODE
DESCRIPTION
PHYSICAL_LOCATION_ID
PICK_UOM_CODE
DIMENSION_UOM_CODE
LENGTH
WIDTH
HEIGHT
LOCATOR_STATUS
STATUS_ID
INVENTORY_LOCATION_TYPE

FND Tables

 

FND_FLEX_VALUE_NORM_HIERARCHY

It stores information about multi level value hierarchies for independent and dependent value sets. Each row includes a value set name, a parent value, a flag to distinguish a child value from a parent value (RANGE_ATTRIBUTE), a low value for the range of child values, and a high value for the range of child values. Oracle Application Object Library uses this information to support multilevel hierarchy values.

The below tables are used to store the descriptive flexfield definition

 FND_DESCRIPTIVE_FLEXS

It stores setup information about descriptive flexfields. Each row includes the name of the table that contains the descriptive flexfield columns, the name and title of the flexfield, the identifier of the application with which the flexfield is registered, whether the flexfield is currently frozen, whether this is a protected descriptive flexfield, the name of the structure defining column for the flexfield (CONTEXT_COLUMN_NAME), and other information about how the flexfield is defined. You need one row for each descriptive flexfield in each application. Oracle Application Object Library uses this information to generate a compiled definition for a descriptive flexfield.

FND_DESCR_FLEX_COLUMN_USAGES

It stores the correspondences between application table columns and the descriptive flexfield segments. Each row includes an application identifier, the name of a descriptive flexfield, and a column name. The context field value, also known as the structure name, is in DESCRIPTIVE_FLEX_CONTEXT_CODE.

Each row also includes the segment name (END_USER_COLUMN_NAME), the display information about the segment such as prompts, display size, type of default value, whether the segment is required or not, whether the segment is part of a high, low segment pair, whether security is enabled for the segment, whether to display the segment or not, and the value set the segment uses. You need one row for each segment for each context value (structure), including global data element segments, for each descriptive flexfield of each application. Oracle Application Object Library uses this information to create a compiled descriptive flexfield definition to store in the FND_COMPILED_DESCRIPTIVE_FLEXS table

FND_CONCURRENT_PROGRAMS 

It stores information about concurrent programs. Each row includes a name and description of the concurrent program. Each row also includes the execution methods for the program (EXECUTION_METHOD_CODE), the argument method (ARGUMENT_METHOD_CODE), and whether the program is constrained (QUEUE_METHOD_CODE).

If the program is a special concurrent program that controls the concurrent managers, QUEUE_CONTROL_FLAG is set to Y. Each row also includes flags that indicate whether the program is enabled and defined as run alone, as well as values that specify the print style the concurrent manager should use to print program output, if any.

There are also values that identify the executable associated with the concurrent program and the application with which the executable is defined, and flags that specify whether the concurrent program is a parent of a report set, whether to save the output file, and whether a print style is required.

Information such as printer name and number of rows and columns on each page of the output file for the concurrent program is also included in the table. You need one row for each concurrent program in each application. Oracle Application Object Library uses this information to run concurrent programs FND_CONCURRENT_PROCESSES

It stores information about concurrent managers. Each row includes values that identify the ORACLE process, the operating system process, and the concurrent manager (QUEUE_APPLICATION_ID and CONCURRENT_QUEUE_ID). You need one row for each instance of a running concurrent manager (each process), as well as one row for the Internal Concurrent Manager.

Oracle Application Object Library uses this table to keep a history of concurrent managers. You should never update this table manually. You can use the Purge Concurrent Request and/or Managers Data program to delete history information periodically.

FND_EXECUTABLES 

It stores information about concurrent program executables. Each row includes an application identifier, an executable identifier, an executable name, and the name of the physical executable file as it resides on the operating system. The execution method associated with the executable identifies the tool that is needed to run the executable.

A subroutine name is only applicable to immediate concurrent programs and spawned concurrent programs that can be run either as spawned or immediate concurrent program. You need one row for each executable that you are going to submit as a concurrent program. Oracle Application Object Library uses this information to process concurrent requests

FND_FLEX_VALUE_SETS 

It stores information about the value sets used by both key and descriptive flexfields. Each row includes the application identifier, the name and description of the value set, the validation type of value set (F for Table, I for Independent, D for Dependent, N for None, P for Pair, U for Special), the data format type,the maximum and minimum values and precision for number format type value set.

Each row also contains flags that determine what size values can be in this value set, and whether flexfield value security and LOVs LongList feature are enabled for this value set.

NUMERIC_MODE_ENABLED_FLAG indicates whether Oracle Application Object Library should right–justify and zero–fill values that contain only the characters 0 through 9; it does not indicate that values in this value set are of type NUMBER. MAXIMUM_VALUE and MINIMUM_VALUE together do range checks on values.

If the value set is a dependent value set, PARENT_FLEX_VALUE_SET_ID identifies the independent value set the current dependent value set depends upon.Also if the value set is a dependent value set, DEPENDANT_DEFAULT_VALUE and DEPENDANT_DEFAULT_MEANING contain the default value and description that Oracle Application Object Library should automatically create in the dependent value set whenever you create a new value in the independent value set it depends upon. You need one row for each value set you have for your flexfields.

Oracle Application Object Library uses this information to assign groups of valid values to flexfield segments 

FND_FLEX_VALUE_HIERARCHIES 

It stores information about child value ranges for key flexfield segment values. Each row includes an identification of the parent value the range belongs to, as well as the low and high values that make up the range of child values.
FLEX_VALUE_SET_ID identifies the value set to which the parent value belongs. You need one row for each range of child values (you can have more than one row for each parent value). Oracle Application Object Library provides this information for applications reporting purposes.

Attribute columns on this inventory table are used as additional information columns known as Descriptive flexfields. The reason they do not have any specific column name is because each implementation of Oracle Apps can customize as to what is stored in this DFF.

To find out what is stored in these attribute columns…

1. find out the different dff defined on this table, how?

Easy way is to just query the fnd_descriptive_flexs_vl view and in the base_table column provide the desired table you wish to query for. Once I find run the query I get the below table that lists all the defined DFF on this table

Flex Title Code base
Items MTL_SYSTEM_ITEMS MTL_SYSTEM_ITEMS_B
Item Order Attributes BOM_ITEM_ORDER_ATTRIBUTES MTL_SYSTEM_ITEMS_B
Item Shipping Attributes BOM_ITEM_SHIPPING_ATTRIBUTES MTL_SYSTEM_ITEMS_B
JG_MTL_SYSTEM_ITEMS JG_MTL_SYSTEM_ITEMS MTL_SYSTEM_ITEMS_B
Master Items EGO_MASTER_ITEMS MTL_SYSTEM_ITEMS_B

2. Once you know the DFF title, go to

System Administrator -> Application -> FlexField -> Descriptive-> Segments

Query the form and enter the Flex title value from the above table and enter in the title field of this form.
Click on the Segments and there you see all the End user column names and the attribute associations.
But there is another easier way to find out the attribute and dff column names associations.
You can query the view fnd_descr_flex_col_usage_vl as demonstrated in the below query.

SELECT
dfc.end_user_column_name user_column_name,
dfc.column_seq_num column_sequence,
dfc.application_column_name table_column_name,
dfc.flex_value_set_id,
dff.application_table_name base_table,
dff.descriptive_flexfield_name flex_code,
dff.title flex_name
FROM fnd_descr_flex_col_usage_vl dfc,
fnd_descriptive_flexs_vl dff
WHERE dff.descriptive_flexfield_name = dfc.descriptive_flexfield_name
and dff.descriptive_flexfield_name =

I get the below list of columns and attributes

User Column Name Column Sequence Table Column Name
Drop Shipment 10 ATTRIBUTE10
Country of Origin 10 ATTRIBUTE1
Invoice UOM 15 ATTRIBUTE15
Harmonized Tarriff Code 20 ATTRIBUTE2
Business Class (y/n) 30 ATTRIBUTE3
Mac_ID Required 40 ATTRIBUTE4
Parent Item for DP 50 ATTRIBUTE14
ECCN 60 ATTRIBUTE9
CCATS 70 ATTRIBUTE12
Version 80 ATTRIBUTE11
Legacy Part Number 90 ATTRIBUTE6

Multi Org Structure in Oracle Apps

July 29, 2011

Oracle APPS Multi-Org Structure

We all hear a lot about the multi-org structure of oracle apps and there is a lot of jargon thrown around like OU and IO and legal entity etc etc. It can get pretty confusing as a beginner. Ok so here is an article that tries to explain the multi-org structure in Oracle Apps. Basically the different entities in multi-org are:

  • Business Group (BG)
  • Sets of Books (SOB)
  • Legal entities (LE)
  • Operating units (OU)
  • Inventory organizations (IO)

Business Group

The Business Group is an Organization that represents the consolidated enterprise, a major division, or an operation company and has no accounting impact. The Business Group partitions Human Resources information and the Purchasing Approval Hierarchy. At least one Business Group would be required per country that an enterprise operates in since HRMS data would have different legislation in different countries.

If you request a list of employees (in any module) you will see only those employees in the Business Group of which your Operating Unit is a part. Multiple Legal Entities can relate to a single Business Group. You must have at least one Business Group. For a new installation, Oracle Applications provides a default business group, Setup Business Group. You can define additional business groups as required for your enterprise.

Sets of Books

A set of books (SOB) is a financial reporting entity that shares the three Cs: a particular chart of accounts (accounting flexfield structure), functional currency, and financial accounting calendar. The SOB concept is similar in a Multi-Org environment. Basically the Set of Books separate the accountings for a legal Entity. A company which operates in separate cities or separate line of businesses may separate their accounting transactions across units through separate Set of Books. General Ledger module secures transaction information (journal entries, balances) by set of books. When you use General Ledger, you choose a responsibility that specifies a set of books. You then see information only for that set of books. Besides GL, Fixed Assets (FA) is also at the SOB level.

You create sets of books using the Set of Books window in General Ledger. You define all other types of organizations using the Organizations window.

As evident from the diagram above, a Business Group can have one or more set of Books.

Legal entities

A legal entity represents a legal company for which you prepare fiscal or tax reports. You assign tax identifiers and other legal entity information to these types of organizations. Separate Legal Entities may share same set of Books.

Operating units

An operating unit represents an organization that uses any Oracle subledger application, for example, Order Management, Payables. It may be a sales office, a division, or a department. An operating unit is associated with a legal entity. Information is secured by operating unit for these applications. Each user sees information only for their operating unit. Responsibilities are linked to a specific operating unit by the MO: Operating Unit profile option.

Inventory organizations

An inventory organization represents an organization for which you track inventory transactions and balances, and manufactures or distributes products. Examples include manufacturing plants, warehouses, distribution centers, and sales offices. The following products and functions secure information by inventory organization: Inventory, Bills of Material, Engineering, Work in Process, Master Scheduling/MRP, Capacity, and purchasing receiving functions. To run any of these products or functions, you must choose an organization that is classified as an inventory organization.

With the Multi-Org, multiple sets of books can use the same “global” item master organization, since the item master organization is used for item definition and not item accounting information. All accounting related attributes in the Item Master are controlled at the item or organization level.

  1. How is CLIENT_INFO being replaced in R12?

Lets take an example.

In pre Release 12, you would have had following methodology for PO_HEADERS_ALL

a. A table is created in PO Schema, named PO_HEADERS_ALL

b. A synonym named PO_HEADERS_ALL is created in APPS schema, referring to PO.PO_HEADERS_ALL

c. Create a view PO_HEADERS in APPS schema, as “select * from po_headers_all where org_id=client_info”

But now in R12, following will happen

a. A table is created in PO Schema, named PO_HEADERS_ALL

b. A synonym named PO_HEADERS_ALL is created in APPS schema, referring to PO.PO_HEADERS_ALL

c. Another synonym named PO_HEADERS is created in APPS, referring to PO_HEADERS_ALL

d. A Row Level security is applied to PO_HEADERS, using package function MO_GLOBAL.ORG_SECURITY.

This can be double-checked by running SQL select * from all_policies where object_name=’PO_HEADERS’

e. The effect of this policy is that,whenever you access PO_HEADERS, Oracle RLS will dynamically append WHERE

CLAUSE similar to below

SELECT * FROM PO_HEADERS WHERE EXISTS (SELECT 1 FROM mo_glob_org_access_tmp oa WHERE

oa.organization_id = org_id)

  1. Does this mean, if I create a new custom table, I will have to apply RLS [ Row Level Security ] against Custom table too?

Yes indeed, if it contains data partitioned by ORG_ID. All you need to do in such case is to assign package function

MO_GLOBAL.ORG_SECURITY to that table/synonym/view.

  1. Will the Multi Org Row Level security be applied against the table or the synonym or the view?

In theory, RLS can be applied against any of the above objects. However in practice, you will apply RLS against Objects in APPS Schema. This means, you will most probably apply RLS on Synonyms. Basically, the Multi Org Views are now replaced by RLS Secured Synonyms. Hence no code change is required where the pre-R12 Multi-Org secured view was being accessed. The responsibility of securing data as per ORG_ID now lies with RLS [also known as VPD - VirtualPrivate Database].

  1. I have made changes to my Multi Org Security Profile, by attaching a new Org Hierarchy. Do i need to run any process?

Just like we do in HRMS, it is advised that any changes to Security Profiles must be followed by running “Security List Maintenance”

  1. What is MO_GLOBAL.INIT

Purpose of mo_global.init :-

It will check if new Multi Org Security Profile is set, to decide if new Security Profile method will be used.

If the new MO security profile is set, then mo_global.init inserts one record, for each Organization in Org Hierarchy, in

table mo_glob_org_access_tmp

When & from where is mo_global.init called ?

This package procedure will be called as soon as you login or as soon as you  responsibility. Just like FND_GLOBAL.INITIALIZE is called. It is safe to assume that Oracle will invoke MO_GLOBAL.INIT after FND_GLOBAL.INITIALIZE

  1. Is mo_glob_org_access_tmp table a global temporary table?

Yes, it is. Hence after Multi Org is initialised for your session, your session will have X number of records in table mo_glob_org_access_tmp. X is the number of organizations assigned to MO Security profile .

  1. What is the purpose of MO_GLOBAL.ORG_SECURITY?

The purpose of Row-Level-Security is to hide certain data[based on some conditions]. RLS does so by appending awhere clause to the secured object.

            MO_GLOBAL.ORG_SECURITY is a function that returns a predicate for the WHERE CLAUSE. The where clause will be appended to Table/Synonym/View for which Multi Org Row Level security is enabled

  1. What is the purpose of MO_GLOBAL.SET_POLICY_CONTEXT ?

This procedure has two parameters

p_access_mode

Pass a value “S” in case you want your current session to work against Single ORG_ID

Pass a value of “M” in case you want your current session to work against multiple ORG_ID’s

p_org_id

Only applicable if p_access_mode is passed value of “S”

  1. In SQL*Plus, I wish to set my session to work against a specific Org [one single org]. How do I do that in R12

SQL>> exec MO_GLOBAL.SET_POLICY_CONTEXT(‘S’,101);

In the above case, ORG_ID 101 will be assigned as current org for your session.

Internally, following code in blue will be executed by Oracle when you set your context to single Org,

dbms_session.set_context(‘multi_org2′, ‘current_org_id’, 101);

**** If the current database session is initialised for Single Org[as in above step], then Where clause appended to object by Row-Level-Security will be

WHERE org_id = sys_context(‘multi_org2′,’current_org_id’)

  1. Why will I as a Apps Techie ever use MO_GLOBAL.SET_POLICY_CONTEXT ?

Lets say you wish to call an API to create invoices in ORG_ID 101. In case the API does not have a parameter for Org_id, you can do the below.

a. exec MO_GLOBAL.SET_POLICY_CONTEXT(‘S’,101)

b. Call the Invoice API, which will internally read the ORG_ID from MO Current Context.

From SQL*Plus, I wish to simulate login to a specific responsibility. How do I do this?

a. Call FND_GLOBAL.INITIALIZE

This will set your responsibility id, user_id etc

b. call MO_GLOBAL.INIT

This will read the MO profile option values for your responsibility/user, and will initialize the Multi Org Access.


RAXINV

July 27, 2011

 RAXINV

How to resolve problems with RAXINV.rdf; Invoice Print Program; [ID 402458.1]

Q1: Is a customized RAXINV supported ?

Q2: What is the only known cause of failures in a RAXINV that has been working ok for some time ?

Solution

A1: No. A customized RAXINV.RDF is not supported by Oracle Support.

A2: The truth is that RAXINV.rdf is almost impossible to use without customization.Sometimes this is as small as adding the customer name to the invoice print program.These customizations render the RAXINV.rdf are not supported Oracle Support.You need to contact the responsible department or consulting office who did the customizations.

What often happens is that the report is either contaminated in the customization process or that the “old” version of the RAXINV.rdf upon which the customizations are built are no longer with the other system updates in the interim. This is the inherent risk of customization in general. However, this is seen quite often in support.

Also, the RAXINV.rdf may have only been opened to be “looked at” in Reports Developer.Unfortunately, the process of opening the report is enough to damage the code.In any case the resolution is the same.

Restore the RAXINV.rdf to vanilla.The only reasonable way to do this and take care of all the compiling and linking, etc. is to apply the latest RAXINV.rdf patch. This is true even if the existing version is at the latest levels. In a case like this, the existing RAXINV.rdf should be renamed to an archiving name such as RAXINVo.rdfo or the like. Then the patch can be applied and will take care of all the necessary steps to restore the RAXINV.rdf to vanilla.

Of course, any customizations will have to be repeated after testing the new vanilla version. These customizations are not a support function, so the customer or a third party consultant such as Oracle Consulting will have to provide these customizations on the new vanilla code.Again, this is the only reasonable approach to resolving RAXINV.rdf problems.Having to re-do the customizations when the base vanilla code needs to be upgraded or is no longer compatible with other upgrades is a hazard of doing customizations unfortunately.

FAQ: Printing Documents: Invoices, Statements and Dunning Letters [ID 145189.1]

CONTENTS

1.      How do you suppress the Credit Memo verbage:

2.      How do you remove the ATTN: Accounts Payable from your invoices?

3.      How do you print invoices in portrait mode?

4.      How do you print attachments to invoices?

5.      How do you supress the header page on Invoices?

6.      How do you print invoices from Oracle Projects?

7.      How do you reprint statements?

8.      How do you suppress Remit-To Address from printing on Dunning Letters?

9.      How do you prevent a Dunning Letter being created for a customer?

10.    How do you print statements by Customer Profile?

11.     How do you run Statements from the Command Line?

 

 

 

QUESTIONS & ANSWERS

1.            How do you suppress the Credit Memo verbage:

                Credit Memo Confirmation

                This is not a request for payment

                AND

                100% of Credit Memo Applied to Invoice XXXXX

Answer

There is no functionality within the application to turn this off.

2.            How do you remove the ATTN: Accounts Payable from your invoices?

Answer

Assign a bill-to contact on the invoices, and the contact will appear.

3.            How do you print invoices in portrait mode?

Answer

Currently, you cannot select portrait mode to print the invoices.  You would have to use the Invoice Print Views.

4.            How do you print attachments to invoices?

Currently, there is no funtionality in the application to do this.

5.            How do you supress the header page on Invoices?

Answer

1. Use responsibility Application Developer 

2. Concurrent>Program>Define 

3. Query up short name RAXINV%

4. Click on paramaters, arrow down to ‘Number of Alignment Pages’     and change the value to zero.  Save.  You cannot suppress the batch summary

6.            How do you print invoices from Oracle Projects?

Answer

You can print an invoice from Oracle Projects Invoice Review or run a management report. However, if you print from Oracle Projects your invoice will be missing the tax and freight.   Tax and Freight are only printed from the Oracle Receivables module.

7.      How do you reprint statements?

Answer

Statements can be reprinted in rel 11 by clicking Special->Reprint

8.      How do you suppress Remit-To Address from printing on Dunning Letters?

Answer

This is designed functionality.  Enhancement Request 341653 received no votes from the User Groups in the 2000 voting cycle

9.      How do you prevent a Dunning Letter being created for a customer?

Answer

The profile option (Profile: Send Letters) must be turned off for every Bill-to usage address defined for the customer, as well as at the customer header level. If there is any active Bill-to entry and the profile option is still enabled, the customer may be dunned.

10.     How do you print statements by Customer Profile?

Answer

———

You can define a statement cycle that is unique to each profile, then print statements by indicating that particular statement cycle.

Example :

Profile 1 defined to use statement cycle Month-Prof1

Profile 2 defined to use statement cycle Month-Prof2

Profile 3 defined to use statement cycle Month-Prof3

During Statement Cycle printing, you can specify Month-Prof1, this will then print only all those customers with profile 1.

11.     How do you run Statements from the Command Line?

Answer

Receivables does not support running ARXSGP from CONCSUB.

ARXSGP is not supported to run from the command line.

Statements are customer facing documents, and as such they are MLS-enabled, which means that a French speaking customer will receive  his statement in French, a Spanish speaking customer will receive his statement is Spanish, etc…

The whole MLS-logic is controlled when it is run from the Application. They cannot get this same effect if they ran the statements from the command line.

Attempting to run Statements via CONCSUB is not in the Oracle  Receivables User Guide Documentation is a customized way to run the statement and is not supported.

Xml Report Output Prints * Instead Of The Correct Value [ID 370280.1]

INVOICE PRINT PRINTING LINES OVER TOTALS IF LINES >= 22 [ID 1073329.6]

Problem Description:

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

When you are in Oracle Receivables and you are printing an invoice with > 22 line items, the subtotals on the front page are printed on invoice lines.

Problem Explanation:

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

The invoice print program prints subtotals on the pages if the invoice exceeds 1 page. When you print an invoice with > 22 line items, the subtotals are overlapping with invoice lines.

 

RAXINV 22 Line items subtotal overflow overlap

Solution Description:

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

You have to change style to invoice print. Customer had Landscape (instead of Invoice Print) in style. Landscape is defined as 66 rows, and this was causing the issue.

  Solution Explanation:

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

In System Administrator Responsibility  

Navigate:concurrent/programs/define   

query enter   

Raxinv% in short name   

query run   

arrow down to RAXINV_SEL   

Rows = 45   

Style = Invoice Print   

using Sysadmin Responsibility:   

install printer style   

query up style name = INVOICE   

(user style = invoice print)   

Rows = 60   

Columns = 132   

Orientation = Landscape.

Translated_description Not Getting Populated Into Interface Tables [ID 304219.1]

RAXINV.rdf (invoice print program) is not printing the translated description of the items for the invoices created from Order Mangement.

Cause : The translated_description column in ra_interface_lines_all table is not being populated by Order Management..

See also Note:304344.1 for further details on workflow limitations

Cause

Currently there is an Internal Enhancement Request 3830379 logged for this issue.

Solution

Monitor this functionality in future releases.

References

NOTE:304344.1 – Invoice Line Item, The Discription Language Is Not Correct

Invoice Line Item, The Discription Language Is Not Correct [ID 304344.1]

Invoice shows Item Description in wrong language.

Sales Orders form shows Item description in Dutch while Invoice shows Description in English.

Sales Order has been entered in NLS language e.g. Dutch with Item Description in Dutch

and ra_interface_lines.all.description is being populated with Item Description in base language English.

Expected behaviour is that Item Description on Invoice is in same language as Sales Orders has been entered in.

Cause

Problem is because of the technical limitation with Workflow engine;The engine processes the eligible order lines based on the engine’s session language rather than the language on which the order line

was created/updated.

Solution

1. Using WF builder, query the existing OEOL workflow definition,

‘Line Flow – Generic’.

2. Copy this line flow definition to say ‘Line Flow – Generic, custom’.

3. Open up this new definition and you would see a transitions like this:

Ship Line -> Fulfill (deferred) -> Fulfill line

4. Delete the Fulfill-Deferred activity.

5. Now create a new transition between Ship Line and Fulfill Line,

so that new definition looks like

Ship Line -> Fulfill line

 

6. Save this new definition.

The main intention here is to make the lines not wait at ‘Fulfill Line’, but immediately go past ‘Fulfill line’ after ‘Ship Line’ is completed.

7. Create a new transaction type and use this new line flow definition for your Order lines.

(Please also consult ‘WF builder user manual’ and user guides on creating application extensions for further instructions on modifying/customizing the workflow.)

HOW TO VIEW REPORT QUERIES, PL/SQL LIBRARIES AND PACKAGES [ID 1076318.6]

You need to see the queries that a report uses as well as the triggers, PL/SQL packages and libraries that the report uses.If you want to see all the report queries, triggers, PL/SQL libraries and packages for a report we have a Tool available.

Solution Description:

You can use Oracle Reports Designer by running the command r25desm from the command line.  This tool can be used to debug, view report triggers and packages for most Oracle reports.

 Solution Description:

From the command line, execute the command:  r25desm  

 The following is an example that will show all of the queries for

RAXINV.rdf (Print Invoices) report.

In UNIX:

cd $AR_TOP/srw

type r25desm

 —> a small window will appear

Click on report folder icon on the top left side of the window.

 —> another small form will appear

Choose reports and queries and then click ok.

Select your report (RAXINV.rdf) from the list and click OK.

 —> it will bring up Object Navigator

In this window, you will see options like Data Model, Report Triggers,

Libraries, Built in Packages etc.

To look at specific report queries do the following:

    Click on Data Model

    Click Queries

       This will show all of the select statements in RAXINV.rdf.

When printing invoices for German customers, in the Bill To and Ship To concatenated address,  Country is missing and Empty line is present between line 2 and line 3.

Cause

This has been identified and fixed through unpublished bug 8899028 which resolves two issues for German addresses:

 

1) Address4 needs to be concatenated for the bill to address

2) The concatenated address should not get truncated.

This does not resolve the issue of the blank line for line number 3. There is an existing Enhancement Request bug 3462242 open for that issue.The cause of the problem is that the invoice which should be printed has is closed.

Amount_due_remaining = 0, that’s why it’s not returning a  row for the select.

The Invoice Print Select program is most likely being run with Open Invoices Only parameter = ‘Yes’ (that’s the default). In order to print closed invoices also run the program with this parameter set to ‘No’.

Raxinv: Why Are Invoices Not Printing From Invoice Print ?; [ID 559896.1]

Check to be sure the customer does not have a trading partner defined for EDI Invoice.

If the customer is set up to print in EDI (Electronic Data Interchange)

then the invoices are sent electronically by EDI.

These are hence ineligible for printing physical invoices (RAXINV) to avoid duplicate billing.

Invoice Print Selected Invoices (RAXINV) shows Subtotal with shipping and Tax on One Invoice Only [ID 296167.1]

Oracle Receivables – Version: 11.0 to 11.5.9

Information in this document applies to any platform.

REPORT:RAXINVPR – Invoice Print Preview Report

REPORT:RAXINV_NEW – Invoice Print New Invoices

REPORT:RAXINV_SEL – Invoice Print Selected Invoices

Goal

On Specific Invoices the following is showing at the bottom: 

Invoice  SUBTOTAL                      Shipping                                        Tax

Invoice TOTAL  

You would like to know how to remove this and determine why is it only showing on some invoices and not others.

Solution

This is Functionality based on the Payment Terms setup applied to the Transaction

Setup->transactions->Payment terms.

Query up the Payment Term which corresponds to the invoice.

In the Installment Option You will see “Include tax and freight in first installment” as the setup

This is what causes the first line Only to display tax and freight.

RAXINV: How To Change Invoice Print Default Value for Parameter [ID 270462.1]

Credit memos created against invoices do not print for Invoice Print because the “Open Invoices Only” parameter defaults to ‘Yes’.

How do you change the parameter to automatically default to ‘No’ in the Submissions form instead of ‘Yes’?

Cause

Credit transactions created against invoices are considered “closed” and will not print is Open Invoice Only parameter is set to ‘Yes’. This is the seeded Default value in the submissions forms

Receivables Users Guide 11.5 in the Print Invoices sections states “Yes” is default value for Open Invoices Only parameter.

Fix

Do the following to change the default value of parameter “Open Invoices Only”

1. Under System Administrator Responsibility

Navigate to Concurrent > Program > Define

2. Query the following Short Name: %RAXINV%

 

Select following individually:

RAXINV_NEW

RAXINV_SEL

3. Click ‘Parameters’ button

Put the Cursor on “Open Invoices Only” parameter

4. Click on the “Default Field”.

You will see the following value: select meaning from fnd_lookups where lookup_type = ‘YES_NO’

and lookup_code = ‘Y’

change this to … lookup_code = ‘N’

5. Save.

Navigate to Print Document > Invoice Print

Select Print New Invoices or Select Invoices, the “Open Invoices Only” parameter should default to ‘No”

Attention:

=======

It’s possible that the default changes back to Yes after a patch has been applied or an upgrade has been done.This is because the seeded value (standard) is lookup_code = ‘Y’

 

What are Flexible Invoice Print Views and How are They Used? [ID 68149.1]

 This document is intended to provide you with information on AR Invoice  Print Views and how they are used to avoid unnecessary customization   of the invoice print process.

 DESCRIPTION

———–

Invoice Print Views are just that, a set of 2 views (one for header, one  for lines).  These views help to coordinate the customization of your  invoices, keeping the amount of development work needed to a bare minimum.  We have taken the most difficult part and masked that behind these easy-to-use views.

 Here are the details:

The data that is reflected on the printed invoice comes from a variety of  tables in the database.  Historically, a developer, consultant, or  technical resource at the customer site had to farm the data from all of these various tables to produce the customized invoice.  Now, with invoice print views, all the data elements necessary to customize an invoice are coordinated through two views, one for header and one for lines.  The process of customizing invoices is likely to still require technical/development assistance (i.e., the end user is not likely to be able to set up these views).  But, we have made the process infinitely easier by providing one go to point where the customizer can get the data.

The Invoice Print Views are version independent.  No changes to the customized invoice are needed when upgrading since the customized invoice is utilizing the views which will not change as opposed to the base tables  which may change from version to version.  Additionally, the Invoice Print  Views can be used with virtually any reporting tool (Reports 3.0/6.0,  Discoverer, Crystal Reports, etc.).

Although the views were planned for R11, we were not able to complete the  coding within the R11 timeframe.  Consequently, the views are considered      a R11.5 feature that has been back ported to 11.0.  The feature will be made available in the 11.03 mini pack.  The readme file for 11.03 will      contain some information about the print views.  Additional information  will be made available through MetaLink at that time. The user documentation will be updated in the next documentation release

(example:, R11.5 Users’ Guide).  The documentation on the Invoice Print  Views will be listed in the user’s guide as an appendix.

NOTE – ADDITIONAL INFORMATION

There are actually 8 views.   Amoung which are the 2 main views that were mentioned originally, for header and lines.   The other 6 views are for adjustments and other supporting invoice information such as tax, commitments, and summary totals.  The reason there are so many views is that the customer may not be interested in everything to do with all of this supporting  information.  By putting this information into different views we lessened the complexity and saved on performance.

Invoice Print Views

(Available in 11.0.3)

Use the Receivables Transaction Printing Views with Oracle Applications or any

third party SQL-based system to collect Receivables transaction information and print it in a format you define.  The Transaction Printing Views cannot be accessed from  any Receivables window or program.  Your system administrator or Oracle consultant must write custom SQL scripts to extract the transaction data that you need from the views.  You can then use Oracle Reports 2.5 or later  (or a similar SQL-based report generator) to format and print the data according to your needs.While the Transaction Printing Views and the Receivables Print Invoice program  are somewhat similar, you use each differently and for different purposes.  The Receivables Print Invoices program prints selected transactions based on a series of runtime parameters that you specify, such as transaction class, transaction type, or a range of transaction numbers.  Transaction Printing Views select all Receivables transaction information from the database.  Your system administrator or Oracle consultant extracts the transaction information needed from the views by entering parameters in the WHERE clause for each SQL statement.  A list of valid parameters is included with each view.

Note:  The Transaction Printing Views also provide several functions to extract complex data that cannot be accessed using SQL select statements.  These functions are for select purposes only and cannot update the database due to pragma restrictions, compiler directives that indicate which kinds of SQL  statements can be used in a PL/SQL function.  These restrictions are declared  in the package  specification for each function.  The Pragma Restrict-Reference is located in the package header with the specification for the function.

New Modules

The following modules create the view structure for the transaction printing procedure.  You must run these modules in the order in which they appear, because dependencies exist between some of the files.

ARTPSQS.pls is the package specification for view functions

ARTPSQBS.pls is the package body for view functions

artpinvv.sql is the other views creation script

artphdrv.sql is the header view creation script

artpadjv.sql is the adjustment view creation script

artplinv.sql is the lines view creation script

 

New Views

=========

Receivables provides the following Transaction Printing Views which can be used

to select transaction data:

 

AR_INVOICE_HEADER_V is the main view.  It retrieves the  header information of  the report.  It has two parts, one for adjustments and one for  nonadjustments.

AR_INVOICE_ADJ_V retrieves the details for an adjustment.

AR_INVOICE_LINES_V retrieves the line items of each transaction retrieved by   AR_INVOICE_HEADER_V.

AR_INVOICE_TAX_SUMMARY_V retrieves tax summary information for a transaction.

AR_INVOICE_COMMITMENT_INFO_V retrieves commitment  information for a   transaction.

AR_INVOICE_TOTALS_V retrieves the total amounts for all lines and associated charges for a transaction (for example, lines, freight, and tax).

AR_INVOICE_INSTALLMENTS_V retrieves installment information for transactions  with multiple installments.

AR_INVOICE_COUNT_TERMS_V retrieves the number of terms for a transaction    (that is, transactions assigned to split payment terms).

(Remember that for multi org, you need to set the org_id before running any sqlplus against the tables.

From sqlplus, please type the following, filling in your org_id:

Exec  dbms_application_info.set_client_info(’100′);  — 100 is the org_id

select count(*) from ar_invoice_header_v;

It should return rows.)

Credit Memos Do Not Print [ID 162268.1]

fix:

When printing credit memos, the ‘Open Invoices Only’ parameter on the  submission window must be set to ‘No’.

Invoice Print Selected Invoices Completes With Warning [ID 160269.1]

The cause of the problem is that the invoice which should be printed has is closed.

Amount_due_remaining = 0, that’s why it’s not returning a  row for the select.

The Invoice Print Select program is most likely being run with Open Invoices Only parameter = ‘Yes’ (that’s the default).

In order to print closed invoices also run the program with this parameter set to ‘No’.

fix:

If this is a closed invoice, or an open invoice with a zero balance, then the parameter Open Invoices Only must be set to No in order to print the zero balance invoices.

Invoice Print Program Includes Consolidated Billing Customers [ID 335951.1]

The Invoice Print Program includes invoices that were included on a Consolidated Billing Invoice

Cause

The application is working as designed. 

Solution

There is nothing to prevent invoices from being printed if they were included in a consolidated billing invoice.  Enhancement Request 660690 was logged to request this functionality.

 

One possible workaround is to set the Printing Option for the Transaction Type to ‘Do Not Print’.  But if this transaction type includes invoices that are to be printed via RAXINV and invoices include in ARXCBI, this will not work.

How To Print Tax Information On An Invoice [ID 244952.1]

How tax prints on invoices is setup through the customer profile settings.Receivables prints tax on invoices and debit memos depending upon the value entered for the Tax Printing option assigned to a customer¿s profile class.

See: Defining Customer Profile Classes in the Accounts Receivable User Guide

If a Tax Printing option is not designated in the customer’s profile class at site or header level, Receivables uses the value entered in the System Options window.

 

Following are Receivables tax printing options:

European Tax Format:

Prints tax information in the following format: Tax rates printed as the last column of invoice lines, freight items printed last, and the taxable amount with the summarized tax codes printed at the end of the invoice.

Itemize by Line:

Itemize tax information for each invoice line. Receivables displays this information after each invoice line.

Itemize and Summarize: Display both itemized and recap tax information.

Summarize by Tax Code:

Display all tax information under the heading ¿Tax Summary by Tax Code.¿ If there are two tax lines with the same tax code, rate, exemption, and precedence number, Receivables will group them together.

Summarize By Tax Name:

Displays all tax information under the heading ¿Tax Summary by Tax Name.¿ If there are two tax lines with the same tax name, rate, exemption, and precedence number, Receivables will  group them together.

Total Tax Only:

Displays only the total tax amount at the bottom of the document.

 

RAXINV_SEL: ORA-01009 MISSING PARAMETER PRINTING > 1 INVOICE [ID 1080128.6]

RAXINV_SEL report errors on printing more than one invoice with ORA-1009.

Report Invoice Print Selected Invoices (RAXINV), errors with the following:

Error, missing mandatory parameter is reported on the log file , and Ora-01009 (if printing more than one invoice).

If run from the command line, two additional error messages are received:

     msg-00103:  oracle error in call to after parameter form trigger-101502; 

     rep-1419:  “after form: plsql program aborted”

(The report will work with single transaction numbers, for example with a transaction number low and high, the report will print one invoice).

Problem Explanation

When trying to print a range of invoices, the log file returns the following:

missing mandatory parameter on the log file, but it is possible to print one invoice at a time.

This problem has occured in the Oracle Applications 10.7.16.1 SC with DEV2K 1.6.1 (Oracle Reports 2.5.7.5.0) and with DEV2K 1.3.2.

Solution Description

1. This is fixed in SQLMGR 2.5.5.13.  Please contact the Tools group to determine the appropriate patchset to apply for specific system configuration because the patches are platform specific.  (i.e. operating system, Dev2K version, reports version, application version).

 2. Apply the latest version of raxinv.rdf.

3. Run adrelink on the ar25run : adrelink force=y ranlib=y “ar25run”.

4. Bounce the concurrent manager and the database ( ** VERY IMPORTANT **) because changes will not take effect until this is done.

Invoice And Credit Memo Printing Process

Overview
Oracle Receivables provides several ways in which you may print Transactions. The same print programs may be used to print all of the different type of Transactions, including Invoices, Credit Memos, Debit Memos, Deposits, Guarantees, Chargebacks, or Pro Forma Invoices. Before an Invoice may be printed, it must be in the Completed state. If you go to print an Invoice and the Invoice number is not available in the List of Values, you should first verify to see if the Invoice has been Completed.

A set up decision which must be made is whether or not you allow changes to printed Invoices. In the Trans and Customers alternate region of the System Options form (Setup > System > System Options), you may opt to check the box to the field labeled Allow Change to Printed Invoices. If the box is checked, then you have the option of Incomplete an Invoice after it has been printed, making changes to it, and then resubmitting it to the print program to reflect the changes. If the box is not checked, then you will not be allowed to make changes to an Invoice once it has been printed.

Pre-Requisite

* Transactions (such as Invoices and Credit Memos) are eligible for printing.

* Batches of Invoices exist for the Print Batch of Invoices program.

 

Process Flowchart
(For Clear View of the Image double click on it)

 

 

Description of Process Steps

Step 1.0 Preview Invoices to be Printed (Optional)
Oracle Receivables provides the option to preview the output of the Invoice print program. This would come in especially handy if the Allow Change to Printed Invoices System Option was checked. This way, you can view the printed output of an Invoice, and still be able to make changes to the Invoice before actually running the print program.

Step 2.0 Print New Invoices
This version of the Invoice print program will select all transactions in the Completed state, which have never previously been printed. Using the Transaction Class/Type parameter, you may choose to print just the new Invoices, or just the new Credit Memos, etc.

Note: Oracle Receivables defines a New Invoice as one which has never before been printed. This does not include Invoice(s) which have been previously printed, then subsequently changed, and requiring reprinting. The Print New program will not pick up these transactions since they have been previously printed. In this case, you would have to run the Invoice Print Selected Invoices program.

Step 3.0 Print Selected Invoices
This version of the Invoice print program will select only those transactions which are specified in the parameters of the print program for printing. You must select at least one transaction number when running this program.

Step 4.0 Print an Invoice Batch
When Oracle imports transactions into Receivables, be it from Order Management or an external source, it creates batches of those transactions. These batches may be viewed in either the Transactions > Batches or the Transactions > Batches Summary form, each of which provides drill down capability to the individual transactions in a batch. The Invoice Print Batch of Invoices program allows you to print all of the transactions from a specific batch or from a range of batches.

Step 5.0 Print Invoice Adjustments
Use the Print Adjustments report to prepare a document to deliver to customers which identifies adjustments made to previous transactions.

Step 6.0 Print Transaction Register
After printing invoices, you may print the Transaction Register (Reports > Accounting) to review a summary of those transactions.

 

 


About us

July 24, 2011
Founded in 2002

Purchasing

July 15, 2011

Q: What is Blanket PO?


A: A Blanket PO is created when you know the detail of the goods or services you plan to buy from a specific supplier in a period, but you do not know the detail of your delivery schedules.


Q: What are different types of PO’s ?

A:

  1. Standard
  2. Blanket
  3. Contract
  4. Planned

Q: What is Contract PO?

 A: Contract PO is created when you agree with your suppliers on specific terms and conditions without indicating the goods and services that you will be purchasing.

Q: How does workflow determines whom to send the PO for approval?

A: It Depends on the setup whether it is position based or supervisor based.

Q: Can you create PO in iProcurement ?

A: No

Q: Give me some PO tables

A: 

  1. PO_HEADERS_ALL, 
  2. PO_LINES_ALL,
  3. PO_LINE_LOCATIONS_ALL,
  4. PO_DISTRIBUTIONS_ALL

Q: Tell me about PO cycle?

A: Create PO

Submit it for Approval

Receive Goods against the PO (when order is delivered by the vendor)

Close the PO after the invoice is created in AP and teh payments have been made to the vendor.

Q: Explain Approval Heirarchies in PO.

A: Approval hierarchies let you automatically route documents for approval. There are two kinds of approval hierarchies in Purchasing: position hierarchy and employee/supervisor relationships.

If an employee/supervisor relationship is used, the approval routing structures are defined as you enter employees using the Enter Person window. In this case, positions are not required to be setup.

If you choose to use position hierarchies, you must set up positions. Even though the position hierarchies require more initial effort to set up, they are easy to maintain and allow you to define approval routing structures that remain stable regardless of how frequently individual employees leave your organization or relocate within it.


How do we enable tracing for the document managers?

This can be done by setting profile option “Initialization SQL Statement – Custom” against your username before reproducing the issue. The value of this profile will be set so as to enable trace using event 10046, level 12.

What is the role of Document Manager in Oracle Purchasing?

To receive pipeline signal from the application on made request. Document Manager Functionality comes within Oracle Universal Content Management (UCM) which allows an Organizations/firm to effectively and efficiently capture, secure, share and distribute digital and paper-based documents and reports.

The main benefits of using the Document Manager are Organization will: Save money and improve operational efficiencies by streamlining communications, Automating routine tasks and Lowering costs related to the printing, shipping, and storage of business documents.

Manageable as it can easily collaborate and share documents of all types and allow users to quickly and securely access and manage them via a Web browser

Hot-pluggable which means can provide out-of-the-box support for Oracle and third party repositories, security, and enterprise applications

What are the two mechanisms by which to trace the document manager? Explain them

1. Set the debugging on by using profile option:

First Set profile option “Concurrent: Debug Flags” to TCTM1. This profile should only generate debugs when set at Site level as Document Manager can run in a different session. Secondly Bounce the Document Manager. Then Retry the Workflow to generate debugs. After generating debugs reset profile option “Concurrent: Debug Flags” to blank. At last check debug information in table fnd_concurrent_debug_info

2. This can also be done by setting profile option “Initialization SQL Statement – Custom” against your username before reproducing the issue. The value of this profile will be set so as to enable trace using event 10046, level 12.

What are the key benefits of forms personalization over custom.pll?

Many users can develop form personalization at a given time.

By using form personalization it becomes fairly easy to enable and disable forms personalization.

Hide/disable fields or buttons functionality is removed.

Provides more visibility on customizations to the screen

What are different types of invoices and what is a recurring invoice?

Types of Invoice:

Standard Invoice, CreditMemo Invoice, DebitMemo Invoice,

Expense Invoice, Recurring Invoice With-hold tax Quick Match and Pre-Payment Invoice.

Recurring Invoice: As the name suggests the invoice that occurs at regular interval of time is known as the Recurring Invoice.

Q: What functions you do from iProcurement?

A: Create Requisition, Receive the goods.

Q: What is difference between positional hierarchy and supervisor hierarchy?

A: If an employee/supervisor relationship is used, the approval routing structures are defined as you enter employees using the Enter Person window. In this case, positions are not required to be setup.

If you choose to use position hierarchies, you must set up positions. Even though the position hierarchies require more initial effort to set up, they are easy to maintain and allow you to define approval routing structures that remain stable regardless of how frequently individual employees leave your organization or relocate within it.

Q: What is the difference between purchasing module and iProcurement module?

A: Iprocurement is a self service application with a web shopping interface. WE can only create and manage requisitions and receipts.

Purchasing module is form based and also lets you create PO and many other functions are possible other than requisitions and receiving.

Q: What is dollar limit and how can you change it?

A: An approval group is defined which is nothing but a set of authorization rules comprised of include/exclude and amount limit criteria for the following Object Types: Document Total, Account Range, Item Range, Item Category Range, and Location that are required to approve a PO.

You can always change the rules by doing the below:

Navigation:

Purchasing Responsibility > Setup > Approvals > Approval Groups

Query the Approval group and change teh rules accordingly.

Q: What is Planned PO?

A: A Planned PO is a long–term agreement committing to buy items or services from a single source. You must specify tentative delivery schedules and all details for goods or services that you want to buy, including charge account, quantities, and estimated cost.

Q: What is backdated Receipt?

A: Creating a Receipt with “Receipt Date” less than sysdate or today’s date is referred to as ‘Backdated Receipt”.

Q: Is it possible to create a backdated receipt with a receipt date falling in closed GL period?

A: No. The receipt date has to be with in open GL period. 

Q: What is Receipt Date Tolerance?

A: The buffer time during which receipts can be created with out warning/error prior or later to receipt due date.

Q: How do we set the Receipt Tolerance?

A: Receipt Tolerance can be set in three different places.

1. Master Item Form (Item Level)

2. setup, organization form (Organization Level)

3. Purchase Order, Receiving controls. (shipment level)



Follow

Get every new post delivered to your Inbox.