Useful Oracle Queries

1) Display the name of employees along with their annual salary (sal*12) the name of the employee earning highest annual salary should appear first?
Select ename, sal, sal*12 "Annual Salary" from EMP order by "Annual Salary" desc;
2) Display name, salary, Hra, pf, da, TotalSalary for each employee. The out put should be in the order of total salary, hra 15% of salary, DA 10% of salary .pf 5% salary Total Salary will be (salary+hra+da)-pf?
Select ename, sal SA, sal*0.15 HRA, sal*0.10 DA, sal*5/100 PF, sal+ (sal*0.15) + (sal*0.10) -(sal*.05) TOTALSALARY from emp ORDER BY TOTALSALARY DESC;
3) Display Department numbers and total number of employees working in each Department?
Select deptno, count (*) from emp group by deptno;
4) Display the various jobs and total number of employees working in each job group?
Select job, count (*) from emp group by job;
5) Display department numbers and Total Salary for each Department?
Select deptno, sum (sal) from emp group by deptno;
6) Display department numbers and Maximum Salary from each Department?
Select deptno, max (Sal) from emp group by deptno;
7) Display various jobs and Total Salary for each job?
Select job, sum (sal) from emp group by job;
8)Display each job along with min of salary being paid in each job group?
Select job, min (sal) from emp group by job;
9) Display the department Number with more than three employees in each department?
Select deptno, count (*) from emp group by deptno having count (*)>3;
10) Display various jobs along with total salary for each of the job where total salary is greater than 40000?
Select job, sum (sal) from emp group by job having sum (Sal)>40000;
11) Display the various jobs along with total number of employees in each job. The output should contain only those jobs with more than three employees?
Select job, count (*) from emp group by job having count (*)>3;
12) Display the name of employees who earn Highest Salary?
select ename, sal from emp where sal>=(select max(sal) from emp );
13) Display the employee Number and name for employee working as clerk and earning highest salary among the clerks?
select ename,empno from emp where sal=(select max(sal) from emp where job='CLERK') and job='CLERK' ;
14) Display the names of salesman who earns a salary more than the Highest Salary of the Clerk?
select ename,sal from emp where sal>(select max(sal) from emp where job='CLERK') AND job='SALESMAN';
15) Display the names of clerks who earn a salary more than the lowest Salary of any Salesman?
select ename,sal from emp where sal>(select min(sal) from emp where job='SALESMAN') and job='CLERK';
16) Display the names of employees who earn a salary more than that of jones or that of salary greater than that of scott?
select ename,sal from emp where sal>all(select sal from emp where ename='JONES' OR ename='SCOTT');
17) Display the names of employees who earn Highest salary in their respective departments?
select ename,sal,deptno from emp where sal in (select max(sal) from emp group by deptno);
18) Display the names of employees who earn Highest salaries in their respective job Groups?
select ename,job from emp where sal in (select max(sal) from emp group by job);
19)Display employee names who are working in Accounting department?
select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno and d.dname = 'ACCOUNTING';
20) Display the employee names who are Working in Chicago?
select e.ename,d.loc from emp e,dept d where e.deptno=d.deptno and d.loc='CHICAGO';
21) Display the job groups having Total Salary greater than the maximum salary for Managers?
select job ,sum(sal) from emp group by job having sum(sal) >(select max(sal) from emp where job='MANAGER');
22) Display the names of employees from department number 10 with salary greater than that of ANY employee working in other departments?
select ename,deptno from emp where sal>any(select min(sal) from emp where deptno!=10 group by deptno) and deptno=10 ;
23) Display the names of employees from department number 10 with salary greater than that of ALL employee working in other departments?
select ename,deptno from emp where sal>all(select max(sal) from emp where deptno!=10 group by deptno) and deptno=10 ;
24) Display the names of Employees in Upper Case?
select upper(ename) from emp;
25) Display the names of employees in Lower Case?
select Lower(ename) from emp;
26) Display the names of employees in Proper case?
select InitCap(ename)from emp;
27) Find the length of your name using Appropriate Function?
select lentgh('RAMA') from dual;
28) Display the length of all the employee names?
select length(ename) from emp;
29) Display the name of employee Concatinate with Employee Number?
select ename' 'empno from emp;
30) Use appropriate function and extract 3 characters starting from 2 characters from the following string 'Oracle' i.e., the out put should be ac?
select substr('Oracle',3,2) from dual;
31) Find the first occurance of character a from the following string Computer Maintenance Corporation?
select lstr('Computer Maintenance Corporation','a' ) from dual;
32) Replace every occurance of alphabet A with B in the string .Alliens (Use Translate function)
select translate('Alliens','A','B') from Dual;
33) Display the information from the employee table . where ever job Manager is found it should be displayed as Boss?
select ename ,replace(job,'MANAGER','BOSS') from emp;
34) Display empno,ename,deptno from emp table. Instead of display department numbers display the related department name(Use decode function)?
select empno,ename,deptno,Decode(deptno,10,'ACCOUNTING',20, 'RESEARCH' ,30 , 'SALES','OPERATIONS')DName from emp;
35) Display your Age in Days?
select sysdate-to_date('30-jul-1977') from dual;
36) Display your Age in Months?
select months_between(sysdate,to_date('30-jul-1977')) from dual;
37) Display current date as 15th August Friday Nineteen Nienty Seven?
select To_char(sysdate,'ddth Month Day year') from dual;
39) Scott has joined the company on 13th August ninteen ninety?
select empno,ename,to_char(Hiredate,'Day ddth Month year') from emp;
40) Find the nearest Saturday after Current date?
select next_day(sysdate,'Saturday') from dual;
41) Display the current time?
select To_Char(sysdate,'HH:MI:SS') from dual;
42) Display the date three months before the Current date?
select Add_months(sysdate,-3) from dual
43) Display the common jobs from department number 10 and 20?
select job from emp where job in (select job from emp where deptno=20) and deptno=10;
44) Display the jobs found in department 10 and 20 Eliminate duplicate jobs?
select Distinct job from emp where deptno in(10,20);
45) Display the jobs which are unique to department 10?
select job from emp where deptno=10;
46) Display the details of those employees who do not have any person working under him?
select empno,ename,job from emp where empno not in (select mgr from emp where mgr is not null );
47) Display the details of those employees who are in sales department and grade is 3?
select e.ename,d.dname,grade from emp e,dept d ,salgrade where e.deptno=d.deptno and dname='SALES' and grade=3
48) Display thoes who are not managers?
select ename from emp where job!='MANAGER';
49) Display those employees whose name contains not less than 4 characters?
Select ename from emp where length (ename)>=4
50) Display those department whose name start with"S” while location name ends with "K"?
Select e.ename, d.loc from emp e, dept d where d.loc like ('%K') and enamelike ('S%')
51) Display those employees whose manager name is Jones?
Select e.ename Superior, e1.ename Subordinate from emp e, e1 where e.empno = e1.mgr and e.ename='JONES'
52) Display those employees whose salary is more than 3000 after giving 20% increment?
Select ename, sal, (sal+(sal*0.20)) from emp where (sal+(sal*0.20))>3000;
53) Display all employees with their department names?
Select e.ename, d.dname from emp e, dept d where e.deptno=d.deptno
54) Display ename who are working in sales department?
Select e.ename, d.dname from emp e, dept d where e.deptno=d.deptno and d.dname='SALES'
55) Display employee name, dept name, salary, and commission for those sal in between 2000 to 5000 while location is Chicago?
Select e.ename, d.dname, e.sal, e.comm from emp e, dept d where e.deptno=d.deptno and sal between 2000 and 5000
56) Display those employees whose salary is greater than his manager’s salary?
Select e.ename, e.sal, e1.ename, e1.sal from emp e, e1 where e.mgr=e1.empno and e.sal>e1.sal
57) Display those employees who are working in the same dept where his manager is work?
Select e.ename, e.deptno, e1.ename, e1.deptno from emp e, e1 where e.mgr=e1.empno and e.deptno=e1.deptno
58) Display those employees who are not working under any Manager?
Select ename from emp where mgr is null;
59) Display the grade and employees name for the deptno 10 or 30 but grade is not 4 while joined the company before 31-DEC-82?
Select ename, grade, deptno, sal from emp, salgrade where (grade, Sal) in (select grade, Sal from salgrade, emp where sal between losal and hisal) and grade! =4 and deptno in (10,30) and hiredate<'31-Dec-82'
60) Update the salary of each employee by 10% increment that are not eligible for commission?
Update emp set sal= (sal+(sal*0.10)) where comm is null
61) Delete those employees who joined the company before 31-Dec-82 while their department Location is New York or Chicago?
Select e.ename, e.hiredate, d.loc from emp e, dept d where e.deptno=d.deptno and hiredate<'31-Dec-82' and d.loc in('NEW YORK','CHICAGO')
62) Display employee name, job, deptname, and loc for all who are working as manager?
Select e.ename, e.job, d.dname, d.loc from emp e, dept d where e.deptno=d.deptno
and e.empno in (select mgr from emp where mgr is not null)
63) Display those employees whose manager name is Jones and also display their manager name?
Select e.ename sub, e1.ename from emp e, emp e1 where e.mgr=e1.empno and e1.ename='JONES'
64) Display name and salary of ford if his salary is equal to hisal of his grade?
Select ename, grade, hisal, sal from emp, salgrade where ename='FORD' and sal=hisal;
OR
Select grade, sal, hisal from emp, salgrade where ename='FORD' and sal between losal and hisal;
OR
Select ename, sal, hisal, grade from emp, salgrade where ename='FORD' and (grade, Sal) in (select grade, hisal from salgrade,emp where sal between losal and hisal);
65) Display employee name, job, deptname, his manager name, his grade and make an under department wise?
Select e.ename sub, e1.ename sup, e.job, d.dname, grade from emp e1, salgrade, dept d where e.mgr=e1.empno and e.sal between losal and hisal and e.deptno=d.deptno group by d.deptno, e.ename, e1.ename, e.job, d.dname, grade
OR
Select e.ename sub, e1.ename sup, e.job, d.dname, grade from emp e, e1, salgrade, dept d where e.mgr=e1.empno and e.sal between losal and hisal and e.deptno=d.deptno
66) List out all the employee names, job, salary, grade and deptname for every one in a company except ‘CLERK’. Sort on salary display the highest salary?
Select e.ename, e.job, e.sal, d.dname, grade from emp e, salgrade, dept d where (e.deptno=d.deptno and e.sal between losal and hisal) order by e.sal desc
67) Display employee name, job and his manager. Display also employees who are with out managers?
Select e.ename, e1.ename, e.job, e.sal, d.dname from emp e, emp e1, dept d where e.mgr=e1.empno (+) and e.deptno=d.deptno
68) Display Top 5 employee of a Company?

69) Display the names of those employees who are getting the highest salary?
Select ename, sal from emp where sal in (select max (sal) from emp)
70) Display those employees whose salary is equal to average of maximum and minimum?
Select * from emp where sal=(select (max (sal)+min (sal))/2 from emp)
71) Select count of employees in each department where count >3?
Select count (*) from emp group by deptno having count (*)>3
72) Display dname where atleast three are working and display only deptname?
select d.dname from dept d, emp e where e.deptno=d.deptno group by d.dname having count(*)>3
73) Display name of those managers name whose salary is more than average salary of Company?
Select distinct e1.ename, e1.sal from emp e, e1, dept d where e.deptno=d.deptno and e.mgr=e1.empno and e1.sal> (select avg (sal) from emp)

74) Display those managers name whose salary is more than average salary of his employees?
Select distinct e1.ename, e1.sal from emp e, e1, dept d where e.deptno=d.deptno and e.mgr=e1.empno and e1.sal>any (select avg (sal) from emp group by deptno)
75) Display employee name, sal, comm and net pay for those employees whose net pay is greater than or equal to any other employee salary of the company?
Select ename, sal, NVL (comm, 0), sal+NVL (comm, 0) from emp where sal+NVL (comm, 0) >any (select e.sal from emp e)
76) Display those employees whose salary is less than his manager but more than salary of other managers?
Select e.ename sub, e.sal from emp e, e1, dept d where e.deptno=d.deptno and e.mgr=e1.empno and e.salany (select e2.sal from emp e2, e, dept d1 where e.mgr=e2.empno and d1.deptno=e.deptno)
77) Display all employees’ names with total sal of company with each employee name?

78) Find the last 5(least) employees of company?

79) Find out the number of employees whose salary is greater than their managers salary?
Select e.ename, e.sal, e1.ename, e1.sal from emp e, e1, dept d where e.deptno=d.deptno and e.mgr=e1.empno and e.sal>e1.sal
80) Display the manager who are not working under president but they are working under any other manager?
Select e2.ename from emp e1, emp e2, emp e3 where e1.mgr=e2.empno and e2.mgr=e3.empno and e3.job! ='PRESIDENT';
81) Delete those department where no employee working?
Delete from emp where empno is null;
82) Delete those records from emp table whose deptno not available in dept table?
Delete from emp e where e.deptno not in (select deptno from dept)
83) Display those enames whose salary is out of grade available in salgrade table?
Select empno, sal from emp where sal<(select min (LOSAL) from salgrade) OR sal>(select max (hisal) from salgrade)
84) Display employee name, sal, comm and whose net pay is greater than any other in the company?
Select ename, sal, comm, sal+comm from emp where sal+comm>any (select sal+comm from emp)
85) Display name of those employees who are going to retire 31-Dec-99 if maximum job period is 30 years?
Select empno, hiredate, sysdate, to_char (sysdate,'yyyy') - to_char (hiredate,'yyyy') from emp where to_char (sysdate,'yyyy') - to_char (hiredate,'yyyy')=30
86) Display those employees whose salary is odd value?
Select ename, sal from emp where mod (sal, 2)! =0
87) Display those employees whose salary contains atleast 3 digits?
Select ename, sal from emp where length (sal)=3

88) Display those employees who joined in the company in the month of Dec?
Select empno, ename from emp where trim (to_char (hiredate,'Mon'))=trim ('DEC')
89) Display those employees whose name contains A?
Select ename from emp where ename like ('%A%')
90) Display those employees whose deptno is available in salary?
Select ename, sal from emp where deptno in (select distinct sal from emp);
91) Display those employees whose first 2 characters from hiredate - last 2 characters sal?
Select empno, hiredate, sal from emp where trim (substr (hiredate, 1,2))=trim (substr (sal, -2,2));
OR
Select hiredate, sal from emp where to_Char (hiredate,'dd')=trim (substr (sal, -2,2))
92) Display those employees whose 10% of salary is equal to the year joining?
Select ename, sal, 0.10*sal from emp where 0.10*sal=trim (to_char (hiredate,'yy'))
93) Display those employees who are working in sales or research?
Select e.ename from emp e, dept d where e.deptno=d.deptno and d.dname in ('SALES','RESEARCH');
94) Display the grade of Jones?
Select ename, grade from emp, salgrade where (grade, Sal) =(select grade, Sal from salgrade, emp where sal between losal and hisal and ename='JONES')
95) Display those employees who joined the company before 15th of the month?
select ename ,hiredate from emp where hiredate<'15-Jul-02' and hiredate >='01-jul-02';
96) Display those employees who has joined before 15th of the month?
select ename ,hiredate from emp where hiredate<'15-Jul-02'
97) Delete those records where no of employees in particular department is less than 3?
delete from emp where deptno in (select deptno from emp group by deptno having count(*) <3
98) Delete those employeewho joined the company 10 years back from today?
delete from emp where empno in (select empno from emp where to_char(sysdate,'yyyy')- to_char(hiredate,'yyyy')>=10)
99) Display the deptname the number of characters of which is equal to no of employee in any other department?

100) Display the deptname where no employee is working?
select deptno from emp where empno is null;
101) Display those employees who are working as manager?
select e2.ename from emp e1,e2 where e1.mgr=e2.empno and e2.empno is not null
102) Count th number of employees who are working as managers (Using set opetrator)?
select d.dname from dept d where length(d.dname) in (select count(*) from emp e where e.deptno!=d.deptno group by e.deptno)
103) Display the name of the dept those employees who joined the company on the same date?
select a.ename,b.ename from emp a,emp b where a.hiredate=b.hiredate and a.empno!=b.empno
104) Display those employees whose grade is equal to any number of sal but not equal to first number of sal?
select ename,sal,grade ,substr(sal,grade,1) from emp,salgrade where grade!= substr (sal,1,1) and grade = substr(sal,grade,1) and sal between losal and hisal
105) Count the no of employees working as manager using set operation?
Select count(empno) from emp where empno in (select a.empno from emp a intersect select b.mgr from emp b)
106) Display the name of employees who joined the company on the same date?
select a.ename,b.ename from emp a,emp b where a.hiredate=b.hiredate and a.empno!=b.empno;
107) Display the manager who is having maximum number of employees working under him?
select e2.ename,count(*) from emp e1,e2 where e1.mgr=e2.empno group by e2.ename Having count(*)=(select max(count(*)) from emp e1,e2 where e1.mgr=e2.empno group by e2.ename)
108) List out the employee name and salary increased by 15% and express as whole number of Dollars?
select ename,sal,lpad(translate(sal,sal,((sal +(sal*0.15))/50)),5,'$') from emp
147) Produce the output of the emptable "EMPLOYEE_AND JOB" for ename and job ?
Ans: select ename"EMPLOYEE_AND",job"JOB" FROM EMP;
148) List of employees with hiredate in the format of 'June 4 1988'?
Ans: select ename,to_char(hiredate,'Month dd yyyy') from emp;
149) print list of employees displaying 'Just salary' if more than 1500 if exactly 1500 display 'on taget' if less than 1500 display below 1500?
Ans: select ename,sal,
(
case when sal < 1500 then
'Below_Target'
when sal=1500 then
'On_Target'
when sal > 1500 then
'Above_Target'
else
'kkkkk'
end
)
from emp
150) Which query to calculate the length of time any employee has been with the company
Ans: select hiredate, to_char (hiredate,' HH:MI:SS') FROM emp
151) Given a string of the format 'nn/nn' . Verify that the first and last 2 characters are numbers. And that the middle character is '/' Print the expressions 'Yes' IF valid ‘NO’ of not valid. Use the following values to test your solution'12/54', 01/1a, '99/98'?
Ans:

152) Employes hire on OR Before 15th of any month are paid on the last friday of that month those hired after 15th are paid the last friday of th following month .print a list of employees .their hiredate and first pay date sort those who se salary contains first digit of their deptno?
Ans: select ename,hiredate, LAST_DAY ( next_day(hiredate,'Friday')),
(
case when to_char(hiredate,'dd') <=('15') then
LAST_DAY ( next_day(hiredate,'Friday'))
when to_char(hiredate,'dd')>('15') then
LAST_DAY( next_day(add_months(hiredate,1),'Friday'))
end
)
from emp

153) Display those managers who are getting less than his employees salary?
Ans: select a.empno, a.ename, a.sal, b.sal, b.empno, b.ename from emp a, emp b where a.mgr=b.empno and a.sal>b.sal

154) Print the details of employees who are subordinates to BLAKE?
Ans: select a.empno,a.ename ,b.ename from emp a, emp b where a.mgr=b.empno
and b.ename='BLAKE'

Oracle Manufacturing Inventory

Oracle Manufacturing Inventory
Oracle Manufacturing Inventory
Contents
MANUFACTURING CONCEPTS
Flow of any manufacturing compan
Manufacturing Strategies
Make-to-stock
Make-to-order
Assemble-to-order
Engineer-to-order
Materials Management
Activities
Modules
Relation with major modules
Inventory
Functions of inventory
Inventory Flexfields
Account Aliases
Item catalogs
Item categories
Sales order
Stock locators
System items (item flexfield
ITEM definition
Item attributes
Main attribute group
Inventory Attribute
Bill of Material attribute group
Asset management attribute group
Costing attribute group
Purchasing attribute group
Receiving attribute group
Physical attribute group
General planning attributes
Order Management attribute group
Invoicing attribute group
Item templates
Category set & category
Unit of Item setup
Item Inventory control
item catalog
Item Transaction
Sub-Inventory
Inter-organization
ABC Analysis & cycle Counting
Setup
Organization parameters
Default inventory parameters
Costing Information
Other accounts
Inter-Organization Transfer Accounts
Inventory Planning and replenishment
Performing Recorder-Point planning
Performing min-max planning
Subinventory replishment planning
Creating & executing Kanban cards
Major Tables
Additional terms in inventory
Interfaces
Open Item Interface
Customer Item and Customer Item Cross-Reference Open interface
Reports In inventory
Inactive items report
Item categories report
Item definition detail
Item definition summary report
Item template listing
Item statuses
ABC descending value report

Manufacturing concepts
As we are dealing with inventory and purchasing we will go through the basic manufacturing concepts.
Any manufacturing unit will have a basic flow of materials. That will include right from the raw material processing to finished goods.

Flow of any manufacturing company:
1) Raw material flow into manufacturing company. 2) Raw material gets processed according to requirement. 3) Finally finished goods are distributed to end consumers through
physical distribution system.
For this different companies use different manufacturing strategies, according to their business need and their requirement and they are,

Manufacturing Strategies
Make- to-stock
In this delivery time is equal to shipment time, generally you will maintain stock.
Make- to-order
In make-to-order delivery time is manufacturing + assembly time + shipment time.
Assemble- to- order
In this strategy assembly time and shipment time contributes to delivery time.
Engineer- to- order-
In this special category order processing starts from design, manufacturing, assembly and shipment.

Materials Management
Any strategy company implements, there comes material management, for following benefits
1) For planning and controlling materials flow 2) Maximize use of firm’s resources 3) Provide required level of customer service
For this material management we will control these activities

Activities
Production Planning: The company will plan production according requirement and according to that the status of inventory is checked. Implementation of planning & control: Production planning should be implemented and control through proper resources, mainly involved purchasing. Inventory management: As per planning inventory should be managed, that vary from raw material to finished goods.

Modules
Different modules which are interrelated with Inventory & Purchasing.
• MPC – Manufacturing Planning & Control • MPS – Master Production Schedule • MRP – Materials Required Planning • BOM – Bill Of Material Relation with major modules
Production Planning
Master production schedule
Engg.
BOM
MRP
Purchasing
Inventory
Production control
Production planning is done by keeping long term view, while on that basis master production schedule is created. MRP will take feedback BOM, Engg. & other modules. Purchasing will work according to MRP for which status will provide by Inventory. So now we can visualize the position of purchasing & inventory in whole manufacturing organization.
Other Manufacturing modules which are inter-related are,
• Oracle BOM ( bill of material)
• Oracle Engineering
• Oracle Product Configurator
• Oracle MRP
• WIP( Work In Process)
• Cost management
• Oracle quality Inventory
In Inventory all major functions are related with items, all functionality is provided by keeping item as center. Now we will discuss various aspects of inventory and flow of Inventory.
Inventory consists of, mainly
1. Raw material 2. Work In Process Inventory 3. Finished Goods Functions of inventory
1. Maintaining stock 2. Planning 3. Forecasting 4. Physical control of inventory 5. Receipt 6. Issues We will discuss these functions as regards with oracle applications.

Inventory Flexfields
The flexfields which are used in inventory are:
• Account aliases • Item catalogs • Item categories • Sales orders • Stock locators • System items Depending on your setup inventory can use other modules flexfields like Accounting (General Ledger) & Sales tax location & territory from Accounts Receivables

Account Aliases
Table name : MTL_GENERIC_DISPOSITIONS
An account alias is an easily recognized name or label representing a general ledger account number. You can view, report, and reserve against an account alias. During a transaction, you can use the account alias instead of an account number to refer to the account.

Item catalogs
Table name: MTL_ITEM_CATALOG_GROUPS Unique ID column: ITEM_CATALOG_GROUP_ID
If you make entries for your items in a standard industry catalog or want to group your items according to certain descriptive elements, you need to configure your Item Catalog Group Flexfield.
Even if you do not use item cataloging, you must enable at least one segment and compile this flexfields before you can define items.
These flexfields supports only one structure and dynamic inserts is not allowed.
Item categories
Table name: MTL_CATEGORIES Unique ID Column: CATEGORY_ID
You must define & configure your item categories flexfields before you can start defining items since all items must be assigned to categories. You can define multiple structures fro different category groups. So that you can associate these structures with categories & category sets.

Sales order
Table name: MTL_SALES_ORDERS Unique ID Column: SALES_ORDER_ID Through this flexfield inventory will identify sales order transactions of OM with
inventory. This sales order flexfield should be defined as order number, order type and order source so that each transaction will be unique in inventory.
Stock locators
Table name: MTL_ITEM_LOCATIONS Unique ID Column: INVENTORY_LOCATION_ID
If you keep track of specific locators such as aisle, row, bin indicators for your items, you need to configure your Stock Locators Flexfield and implement locator control in your organization.
You can use stock locators field to capture more information about stock locators in inventory. If you do not have oracle inventory installed any none of items have locator control then it is not necessary to define this flexfield.
And this flexfield supports only one structure.
System items (item flexfield)
Table name: MTL_SYSTEM_ITEMS And unique id column is INVENTORY_ITEM_ID
You can use item flexfield for recording and reporting your item information, and you must define and configure your item flexfield before you can start defining items.
You must plan how Oracle Inventory represents your company’s inventory sites and business units. This includes defining organizations, locations, subinventories, and locators depending on your company structure.
Inventory is consisting of inventory – subinventory -locators as per your organization setup.
ITEM is defined in inventory that is generally first in master organization, so that later it can be assign to multiple organizations. Also item definition can be uploaded by item upload open interface.
ITEM definition
You define items in one organization. To distinguish it from others, we call it the Item Master organization. Other organizations (child organizations) refer to the Item Master for item definition. After you define an item in the Item Master, you can assign it to any number of other organizations.
Item attributes
• inventory attributes like lot control/ serial control
• Purchasing and costing information.
• bill of material/ item category attributes
• Physical attributes like weight, volume
• Receiving attributes
• General planning attributes like min-max qty, order qty.
• Status of item Generally we will group the item attributes in these groups
Main Inventory Bill of material Asset management Costing Purchasing Receiving Physical attributes General planning MPS/MRP planning Lead times Work In Process Order Management Invoicing Service
Now we will discuss main attributes from major group.
Main attribute group
Primary Unit of Measure-
This is the stocking and selling unit of measure. Any necessary conversions are based on this unit of measure. This attribute is not updatable. The default primary unit of measure for new items is defined using the INV: Default Primary Unit of Measure profile option.
User Item Type-
Oracle provides several types by default at installation. These types correspond to the item templates also provided. Select one of these values, or one you defined with the Item Type window.
Different item types are ATO model/ Finished good/ Freight/ Inventory Type etc.
Item Status-
Item status codes set or default the values for attributes under status control. User–defined status codes control certain item attributes designated as status attributes. Item status can be active/engineer/inactive. The default item status for new items is defined using the INV:Default Item Status profile option.

Inventory Attribute Group
Inventory Item-Indicate whether to stock and transact this item in Oracle Inventory.
Stockable-Indicate whether to stock this item in Inventory. You can set this attribute only when you turn on the Inventory Item option.
Transactable – Indicate whether to allow Inventory transactions. You can set this attribute only when you turn on the Stockable option.
Reservable-Indicate whether you can create material reservations. You can reserve an item only when you have sufficient inventory.
Other attribute in this group are Lot control, serial control, locator control.

Bill of Material attribute group
BOM allowed-Allows you to define a bill of material for an item, or to assign the item as a component on a bill.
BOM item type-This attribute is controlled at the Master level only. Select a type to control bill functionality. You must enter a value here if BOM Allowed is turned on.
Asset management attribute group
These are the attributes you have to give for asset management.
Asset item type-This attribute identifies the asset item as an Asset Group, Asset Activity, or Rebuildable item.
Activity type-This is relevant when the Asset Item Type is set to Asset Activity. This indicates the type of maintenance for this asset activity.
Costing attribute group
You have to set these attributes while defining or updating items.
Costing enabled-
Indicate whether to report, value, and account for any item costs. Inventory asset value-Indicate whether to value an item as an asset in inventory. Turning this option off indicates an expense item.
Cost of goods sold account-
This attribute is controlled at the Organization level only. Enter a general ledger account to use as a source for the Cost of Goods Sold Account. The default cost of goods sold account is set when you define organization parameters.

Purchasing attribute group
Purchased-
Indicate whether to purchase and receive an item. Turning this option on allows you to set the Purchasable attribute. Purchasable-Indicate whether to order an item on a purchase order. You can set this only when Purchased is turned on.
Receipt required-
Indicate whether you must receive an item before you can pay the invoice. Leave this field blank if you want Inventory to use the value defined in the Purchasing Options window for transactions involving this item.
i.e. three way invoice matching Inspection required-
Indicate whether to inspect an item upon receipt from the supplier, before paying the corresponding invoice. Leave this field blank if you want Inventory to use the
value defined in the Purchasing Options window for transactions involving this item.
Encumbrance account-
This attribute is controlled at the Organization level only. Enter the default encumbrance account Oracle Purchasing uses when an item is received. If the item encumbrance account does not exist, Oracle Purchasing uses the sub inventory account.
Expense account-
This attribute is controlled at the Organization level only. Enter the default inventory account for expense items. This attribute is used only when Inventory Asset Value is turned off. Oracle Purchasing debits this account when you receive an item into inventory only if the item is being expensed. If you receive into an expense subinventory, Oracle Purchasing uses the expense account you assigned to the subinventory first; if you do not define the account here, Oracle Purchasing uses the expense account assigned to the item.
Receiving attribute group
In this group major attributes are receipt date controls, valid transactions and over-receipt quantity control.

Physical attribute group
The main attributes like weight, volume, dimension are shown below,

General planning attributes
Inventory planning method-Here you can select option for organizational level planning. Like not planned, min-max or recorder point.
For min-max quantity, order quantity and source. For source you can give inventory, subinventory or supplier.

Order Management attribute group
Customer ordered-Indicate whether to allow an item to be ordered by external customers. You can add any customer orderable items to price lists in Oracle Order Management.
Shippable-Indicate whether to ship an item to a customer. Shippable items are released by Oracle Shipping Execution’s Pick Release program, creating confirmable shipping lines, and are printed on the pick slip.
Default shipping organization-Enter the Oracle Shipping Execution primary shipping organization.
Picking rule-Enter the picking rule that defines the order in which subinventories, locators, lots, and revisions are picked.

Invoicing attribute group
Invoicable item-Indicate whether to include an item on an Oracle Receivables invoice.
Accounting rule-Enter an accounting rule to identify special revenue recognition rules for an item, such as recognizing revenue over time.
Invoicing rule-Enter an invoicing rule to determine the period in which you send an invoice when you recognize revenue over time (using accounting rules).
Sales account-This attribute is controlled at the Organization level only. Enter the general ledger account Oracle Receivables uses to record revenue when you bill the customer. If Auto-Accounting is based on items, accounting entries are created at that time.
Item templates
There are two types of templates. Predefined or custom template.
Predefined templates are planning item, purchased, outside processing item, lease item. So that once you assign any template, to new item then all default characters are assign to new item.

Category set & category
Categories are logical grouping of items that have similar characterizes, and a category set is a distinct category grouping scheme and consist of categories. Different category set accordingly to company need and reporting structure.
Use: For forecasting & summarizing history of that category. To keep easy track on that category.
Define category sets

Define categories

Specify default category sets
Assign categories to an item
Tables are related as, for item categories MTL_ITEM_CATEGORIES
MTL_SYSTEM_ITEMS_B
MTL_CATEGORY_SET_VALID_CATS
MTL_CATEGORY_SETS_B
MTL_CATEGORIES_B
Unit of Measure
Oracle Applications products provide powerful methods for defining and manipulating units of measure. You can easily convert between the units you define. This allows you to manufacture, order, or receive items in any unit of measure.
Table is related as:
MTL_UNITS_OF_MEASURE_TL
MTL_UOM_CONVERSIONS
MTL_SYSTEM_ITEMS_B
Item setup
The main tables that are involved in item master set up are with their relation,
MTL_SYSTEM_ITEMS_B
MTL_ITEM_STATUS
MTL_ITEM_TEMPLATES
MTL_PARAMETERS
MTL_ITEM_ATTRIBUTES
Item relationship
You can define item relationship between items. This allows you to search items through these relationships and for maintaining also.
• Item cross reference Use the Customer Item Cross References window to define and update cross references between your inventory items and the customer item numbers defined in the Customer Items Summary/Detail windows.
MTL_ITEM_TEMPL_ATTRIBUTES
• Substitute items In this relationship you can replace the outdated item, still keeping track on it.
• Related items You can relate different items according to your need.
• Manufacturer part no. You can define manufacturer part numbers for items. You can use this information for reporting purposes; and in catalog searches for particular items.

Inventory control
• Locator control You will track item as locator wise, it is a common method of inventory control.
• Revision Control With different revision of items according to your need, revision control can be Implemented.
• Lot Control You will define lot of your items so that you can track that item according to their lot numbers.
• Serial no. Control For expensive or for which you want tight control you will give serial numbers to those items. Item catalog
Used to partition items that share some common characteristics.
Item Transaction
WIP Suppliers INV Inv customers
Inventory can interact or can have transactions with suppliers, customers, work in process and with other sub inventories.
A transaction type is the combination of a transaction source type and a transaction action. It is used to classify a particular transaction for reporting and querying purposes. Oracle Inventory also uses transaction types to identify certain transactions
For example, transaction type is purchase order receipt which is a combination of transaction action i.e. receipt into stores and transaction source type is purchase type.
Major transactions are,

Sub-Inventory transfer
Each subinventory transfer is carried out as two separate transactions, creating two records in MTL_MATERIAL_TRANSACTIONS. The first record represents an issue from the source subinventory and the second record is a receipt from the destination subinventory.

Inter- organization transfer
Use the Inter–Organization Shipping Network window to define accounting information and the relationships that exist between shipping and destination organizations. You can specify whether an organization is a shipping organization, a destination organization, or both.
In these transactions you will need bills, shipping bills and other accounting details.

ABC Analysis & cycle counting
ABC analysis is a method of determining relative value of items in your Inventory sites.
Automatically scheduled or manually scheduled cycle counts You can define your own ABC group like A class, B class, C class.
Now we will go through some of the set up steps, which you will need before setting up inventory.
Setup
• Define item flexfields
• Define item categories flexfields
• Define catalog group flexfields
• Define UOM
• Define subinventories.
• Define locators
• Define organization parameters.
• Define intercompany relationship.
• Define picking rules. Some of the parameters we have to set defaults like item status as active, default primary unit of measure as Each or requisition approval status as approved or unapproved.
Organization parameters
You can define and update default inventory and costing parameters for your current organization in the following areas:
Default inventory parameters Costing information Revision, lot, serial parameters ATP, Pick, Item-sourcing parameters Inter-Organization Information Other account Parameters Define warehouse parameters
Default inventory parameters
Provide organization code and item master organization
In the Move Order Timeout Period field, enter the number of days a move order requisition can wait for approval. For locator control option: Inventory transactions within this organization do not require locator information. Pre-specified only: Inventory transactions within this organization require a valid, predefined locator for each item. Dynamic entry allowed: Inventory transactions within this organization require a locator for each item. You can choose a valid, predefined locator, or define a locator dynamically at the time of transaction. Determined at subinventory level: Inventory transactions use locator control information that you define at the subinventory level.
Costing Information

You have to define costing organization and costing method.
Valuation Accounts:
You choose a default valuation account when you define organization parameters. Under standard costing, these accounts are defaulted when you define subinventories and can be overridden.
• Material An asset account that tracks material cost. For average costing, this account holds your inventory and in transit values. Once you perform transactions, you cannot change this account.
• Material Overhead An asset account that tracks material overhead cost.
• Resource An asset account that tracks resource cost.
• Overhead An asset account that tracks resource and outside processing overheads.
• Outside processing An asset account that tracks outside processing cost.
• Expense The expense account used when tracking a non-asset item. Other accounts
• Sales The profit and loss (income statement) account that tracks the default revenue account.
• Cost of Goods Sold The profit and loss (income statement) account that tracks the default cost of goods sold account.
• Purchase Price Variance The variance account used to record differences between purchase order price and standard cost. This account is not used with the average cost method.
• Inventory A/P Accrual The liability account that represents all inventory purchase order receipts not matched in Accounts Payable, such as the uninvoiced receipts account.
• Invoice Price Variance The variance account used to record differences between purchase order price and invoice price. This account is used by Accounts Payable to record invoice price variance.
• Encumbrance An expense account used to recognize the reservation of funds when a purchase order is approved.
• Project Clearance Account When performing miscellaneous issues to capital projects, the project clearance account is used to post the distributions.
• Average Cost Variance Under average costing with negative quantity balances, this account represents the inventory valuation error caused by issuing your inventory before your receipts. Inter-Organization Transfer Accounts
You define default inter–organization transfer accounts in the Organization Parameters window. These accounts are defaulted when you set up shipping information in the Inter–Organization Shipping
Networks window.
• Transfer Credit The default general ledger account used to collect transfer charges when this organization is the shipping organization. This is usually an expense account. • Purchase Price Variance The default general ledger account used to collect the purchase price variance for inter–organization receipts into standard cost organizations. This is usually an expense account. • Payable The default general ledger account used as an inter–organization clearing account when this organization is the receiving organization. This is usually a liability account. • Receivable The default general ledger account used as an inter–organization clearing account when this organization is the shipping organization. This is usually an asset account. • In-transit Inventory The default general ledger account used to hold in-transit inventory value. This is usually an asset account. For average cost organizations, this account is the default material account. Other information you have to give is,
Revision, Serial and lot control information.
ATP, Pick, Item sourcing rules.
Inventory Planning and replenishment:
Oracle Inventory lets you manage your inventory levels using any combination of the system’s planning and replenishment features, including min–max planning, reorder point planning, kanban replenishment, and a replenishment system for generating orders.
Performing Recorder-Point planning
In this planning method you will take minimum order quantity and time to fulfill the order to decide the level of order. So record point is safety stock + forecast demand during lead time
Performing min-max planning
In this simple method you will decide maximum and minimum level of stock for that item, so that at min level order can be placed and the qty of that item can be full at max level.
Subinventory replishment planning
For some items you will have transactions with other subinventory so that optimum stock can be maintained.
Creating & executing Kanban cards
In this you will use kanban method by deciding some items as kanban cards.
And through requisition interface i.e. after running requisition import, Purchase requisition gets created that turns out to purchase orders. Then through purchase order u can receipt material in inventory.
And that is how purchasing comes into picture.
Before that we will take look at main tables in inventory modules like
Major Tables
• MTL_SYSTEM_ITEMS_B: Base table for item definition.
• MTL_PARAMETERS : This table is consist of ORGANIZATION_ID, PICKING_RULE_ID, RULE_ID for ATP
• MTL_ITEM_CATEGORY_ SETS
• MTL_ITEM_CATEGORIES : These tables will store information about different category sets and categories defined.
• MTL_ITEM _SUB_INVENTORIES
• MTL_ITEM_LOCATIONS
• MTL_SYSTEM_ITEMS_INTERFACE
• MTL_SUPPLY
• MTL_ONHAND_QUANTITIES
• MTL_RESERVATIONS : These table stores information about reserved quantity and on hand quantity with available quantity for picking rules.
• MTL_SALES_ORDERS : Order Management will interact with inventory for order details through this table.
• MTL_ITEM_FLEXFIELDS
• MTL_ITEM_TEMPLATES
• MTL_ITEM_ATTRIBUTES
• MTL_ITEM_REVISIONS
• MTL_MATERIAL TRANSACTION
• MTL_TRANSACTIONS_REASONS Additional terms in inventory
• Accounting close cycle: General ledger transfer Inventory accounting periods

• Move orders: Move orders are requests for the movement of material within a single organization. They allow planners and facility managers to request the movement of material within a warehouse or facility for purposes like replenishment, material storage relocations and quality handling.
Move orders are restricted to transaction within tan organization
& if you are transferring material between organization, you must internal
requisitions.
Three types of move orders:
Move order requisition Replenishment move orders Pick wave move orders
Interfaces
Open Item Interface
You can import items from any source into Oracle Inventory and Oracle Engineering using the Item Interface. With this interface, you can convert inventory items from another inventory system, migrate assembly and component items from a legacy manufacturing system, convert purchased items from a custom purchasing system, and import new items from a Product Data Management package. The Item Interface validates your data, insuring that your imported items contain the same item detail as items you enter manually in the Master Item window.
The Item Interface reads data from three tables for importing items and item details.
MTL_SYSTEMS_ITEM_INTERFACE This table is used for your new item numbers and all item attributes. This is the main item interface table, and may be the only table you choose to use.
MTL_ITEM_REVISIONS_INTERFACE If you are importing revision details for your new items, you can use this table. Which is used only for revision information, and is not required.
MTL_ITEM_CATEGORIES_INTERFACE To import item category assignments, the table is used to store data about item assignments to category sets, and categories to be imported into the Oracle Inventory MTL_ITEM_CATEGORIES table.
MTL_INTERFACE_ERRORS is used for error tracking of all items that the Item Interface fails.
Before you use the Item Interface, you must write and run a custom program that extracts item information from your source system and inserts the records into the MTL_SYSTEM_ITEM_INTERFACE table. And if revision & category details are there upload into respective tables. After you load item, revision, and item category assignment records into these interface tables, you run the Item Interface to import the data. The Item Interface assigns defaults, validates data you include, and then imports the new items.
Customer Item and Customer Item Cross-Reference Open Interface
A number of manufacturing industries are characterized by a multi-tiered, just-intime supply chain structure. Today’s manufacturing environment requires a close working relationship between customers and suppliers along the entire supply chain. Suppliers must be able to react quickly to their customers’ often changing requirements. By cross-referencing customer items with their own inventory items, suppliers can achieve faster order processing and shipments by allowing customers to place orders using customer item numbers.
You can import customer items and customer item cross-references from any legacy system into oracle inventory.
In customer item interface you import customer items into inventory. For each customer item you must define related information such as customer and item definition level.
MTL_CI_INTERFACE
Using this table data is transferred MTL_CUSTOMER_ITEMS table.
In customer item cross reference, you can import cross-references between customer items and exiting inventory items into your master organization. You can create multiple cross-references between customer items and one Oracle Inventory item. You can also create multiple cross-references between Oracle Inventory items and one customer item.
MTL_CI_XREFS_INTERFACE Using this interface table data is uploaded into base table, MTL_CUSTOMER_ITEM_XREFS.
Reports In inventory
Oracle Inventory provides you with a wide variety of reports, intended for different users of the product.
We will have overlook on major reports that you will come across.
Inactive items report
You can use this report to print items that have not had an inventory transaction since the date you specify. You can use this report to isolate items that have become inactive and verify their status.
The parameters are display and break on sub inventory, category set, inactive since i.e. date from which no transactions were occurred for items.
Item categories report
You will use this report to list items and their associated categories. Report can develop output as per category set and for given item range.
Item definition detail
Use the Item Definition Detail report to view comprehensive information for items. Use this report to verify items have been classified in conformance with decisions you have made regarding how the parts are to be planned, coasted, and located.
The report prints the item definition for the organization in which you submit the report.
You can view item attributes as per the category and also depending on your choice which attributes you want to print. Like general item attributes, bill of material item attributes, costing item attributes, inventory item attributes or physical item attributes.
Item definition summary report
Use the Item Definition Summary report to print a limited amount of information about items, such as description, status, and cost.
Item template listing
Use the Item Template Listing to review the template definitions. A template is a way to define a set of attribute values.
Reports parameters are all template flag or item template.
Item statuses report
Use the Item Statuses Report to view the item statuses. For example, You can use the report to review all pending statuses by making the effective date and the report date the same. You can have options like categories from-to, items from –to, and status effective date. And only for particular status.
ABC descending value report
Use the ABC Descending Value Report to view the results of an ABC compile. The report is sorted by descending value or quantity, depending on the compile criterion. Use this report to evaluate the break points for assigning your ABC classes to items.
You can give cumulative display criteria i.e. cumulative by value of cumulative by quantity.

Order Management Stuff

1)     What are the Base Tables and Interface Tables for Order Management?
Interface Tables : OE_HEADERS_IFACE_ALL, OE_LINES_IFACE_ALL
OE_PRICE_ADJS_IFACE_ALL, OE_ACTIONS_IFACE_ALL
OE_CREDITS_IFACE_ALL (Order holds like credit check holds etc)
Base Tables : OE_ORDER_HEADERS_ALL: Order Header Information
OE_ORDER_LINES_ALL: Items Information
OE_PRICE_ADJUSTMENTS: Discounts Information
OE_SALES_CREDITS: Sales Representative Credits.
Shipping Tables :WSH_NEW_DELIVERIES, WSH_DELIVERY_DETAILS, WSH_DELIVERY_ASSIGNMENTS, WSH_DELIVERIES.

2)    What is Order Import and What are the Setup's involved in Order Import?
A) Order Import is an open interface that consists of open interface tables and a set of API’s. It imports New, updated, or changed sales orders from other applications such as Legacy systems. Order Import features include validations, Defaulting, Processing Constraints checks, Applying and releasing of order holds, scheduling of shipments, then ultimately inserting, updating or deleting orders from the OM base tables. Order management checks all the data during the import process to ensure its validity with OM. Valid Transactions are then converted into orders with lines, reservations ,price adjustments, and sales credits in the OM base tables.

B) Setups:
•    Setup every aspect of order management that we want to use with imported orders, including customers, pricing, items, and bills.
•    Define and enable the order import sources using the order import source window.

3)    Explain the Order Cycle?
i)    Enter the Sales Order
ii)    Book the Sales Order(SO will not be processed until booked(Inventory confirmation))
iii)    Release sales order(Pickslip Report is generated and Deliveries are created) (Deliveries – details about the delivery. Belongs to shipping module (wsh_deliveries, wsh_new_deliveries, wsh_delivery_assignments etc) they explain how many items are being shipped and such details.
iv)    Transaction Move Order (creates reservations determines the source and transfers the inventory into the staging areas)
v)    Launch Pick Release
vi)    Ship Confirm (Shipping Documents(Pickslip report, Performa Invoice, Shipping Lables))
vii)    Auto invoice and closed

4)    Explain the Order to Cash Flow?
•    Enter the Sales Order
•    Book the Sales Order(SO will not be processed until booked(Inventory confirmation))
•    Release sales order(Pickslip Report is generated and Deliveries are created) (Deliveries – details about the delivery. Belongs to shipping module (wsh_deliveries, wsh_new_deliveries, wsh_delivery_assignments etc) they explain how many items are being shipped and such details.
•    Transaction Move Order (Selects the serial number of the product which has to be moved/ shipped)
•    Launch Pick Release
•    Ship Confirm (Shipping Documents(Pickslip report, Performa Invoice, Shipping Lables))
•    AutoInvoice (Creation of Invoice in Accounts Receivable Module)
•    Autolockbox ( Appling Receipts to Invoices In AR)
•    Transfer to General Ledger ( Populates GL interface tables)
•    Journal Import ( Populates GL base tables)
•    Posting (Account Balances Updated).

5)    What are the Process Constraints?
A. Process Constraints prevent users from adding updating, deleting, splitting lines and canceling order or return information beyond certain points in the order cycle. Oracle has provided certain process constraints which prevent data integrity violations.

Process constraints are defined for entities and attributes. Entities include regions on the sales order window such as order, line, order price adjustments, line price adjustments, order sales credits and line sales credits. Attributes include individual fields (of a particular entity) such as warehouse, shit to location, or agreement.


6)    What are Validation Templates?
A) Validation Templates are used to define the validation conditions in process constraints. A validation template names a condition and defines the semantic of how to validate that condition. These are used in processing constraints framework to specify the constraining conditions for a given constraint. These conditions are based on
•    Where the entity is in its work flow.
•    The state of attributes on an entity.
•    Any other validation condition that cannot be modeled using the above condition.

7)    What are different types of Holds?
1 GSA(General Services Administration) Violation Hold(Ensures that specific customers always get better pricing for example Govt. Customers)

2 Credit Checking Hold( Used for credit checking feature Ex: Credit Limit)

3 Configurator Validation Hold ( Cause: If we invalidate a configuration after booking)


8)    What is Document Sequence?
A) Document sequence is defined to automatically generate numbers for your orders or returns as you enter them. Single / multiple document sequences can be defined for different order types.

Document sequences can be defined as three types Automatic (Does not ensure that the numbers are contiguous), Gapless (Ensures that the numbering is contiguous), Manual Numbering. Order Management validates that the number specified is unique for order type.



9)    What are Defaulting Rules?
A) A defaulting rule is a value that OM automatically places in an order field of the sales order window. Defaulting rules reduce the amount of information one must enter. A defaulting rule is a collection of defaulting sources for objects and their attributes.
It involves the following steps
1 Defaulting Conditions - Conditions for Defaulting
2 Sequence – Priority for search
3 Source – Entity ,Attribute, Value
4 Defaulting source/Value

10)    When an order cannot be cancelled?
A) An order cannot be cancelled if,
1 It has been closed
2 It has already been cancelled
3 A work order is open for an ATO line
4 Any part of the line has been shipped or invoiced
5 Any return line has been returned or credited.

11)    When an order cannot be deleted?
A) You cannot delete an order line until there is a need for recording reason.

12)    What is order type?
A) An order type is the classification of order. It controls the order work flow activity, order number sequence, credit check point and transaction type. Order Type is associated to a work flow process which drives the processing of the order.

13)    What are primary and secondary price lists?
A) Every order is associated to a price list as each item on the order ought to have a price. A price list is contains basic list information and one or more pricing lines, pricing attributes, qualifiers, and secondary price lists. The price list that is primarily associated to an order is termed as Primary price list.

The pricing engine uses a Secondary Price list if it cannot determine the price of the item ordered in the Primary price list.

14)    What is pick slip? Types?
A) It is an internal shipping document that pickers use to locate items to ship for an order.
1 Standard Pick Slip – Each order will have its own pick slip with in each picking batch.
2 Consolidated Pickslip – Pick slip will have all the orders released in the each picking batch.

15)    What is packing slip?
A) It is an external shipping document that accompanies the shipment itemizing the contents of the shipment.

16)    What are picking rules?
A) Picking rules define the sources and prioritization of sub inventories, lots, revisions and locators when the item is pick released by order management. They are user defined set of rules to define the priorities order management must use when picking items from finished goods inventory to ship to a customer.
17)    Where do you find the order status column?
A) In the base tables, Order Status is maintained both at the header and line level. The field that maintains the Order status is FLOW_STATUS_CODE. This field is available in both the OE_ORDER_HEADERS_ALL and OE_ORDER_LINES_ALL.

18)    When the order import program is run it validates and the errors occurred can be seen in?
A) Responsibility: Order Management Super User
Navigation: Order, Returns > Import Orders > Corrections

BI Publisher Integration with Oracle Apps

Before BI Publisher was released as a Product that was integrated with Oracle Applications, Oracle Reports 6i was the most widely used Tool for generating Report outputs and print documents like PO’s, Invoices, statements etc…
Complex Report layouts often took longer to create and test using the Oracle Reports Tool. With the launch of BI Publisher (originally XML Publisher), Oracle has made a breakthrough with the ease and simplicity by which report outputs can be generated.
To be able to use BI Publisher with Oracle Apps, Patches need to be applied which are available for download from metalink.
This article provides an insight into the components involved in creating an end to end BI Publisher solution for Oracle Apps Concurrent Programs.
BI Publisher makes use of XML data files and templates to generate a PDF output.
XML Data file: The XML Data file can be generated in multiple ways from Oracle.
One way would be to set the option on the Concurrent Program Definition to generate an XML file. Another way would be to use plsql code to generate the XML. This XML file then becomes the input for the XML Publisher Concurrent Programs which processes it with the associated template to produce the output.
Pseudo XML data file







10000 ABC.
000 ABC PRODUCT.
1
USD

XML Publisher templates: These templates can be any of MS Word (.rtf )RTF, Adobe Acrobat(.pdf)PDF, XML, eText, MS-Excel

PDF and RTF templates Creation
PDF templates:-
PDF templates require the Adobe Standard Software. An already existing .pdf file can be used to prepare a template for XML Publisher. In most cases there is little or no amount of work involved in designing the layout, as the form fields are placed on a predesigned available blank layout. It is important to note that no matter what the version of the Adobe Acrobat Standard software you use to create the templates, all templates must be converted to Acrobat PDF Version 1.4. As of date only PDF templates Version 1.4 are supported with XML Publisher. This can be achieved by Navigating to AdvancedàPDF Optimizer. Note the current version and use the option Make compatible with: Acrobat 5.0 and later.
No logic of any kind can be used on PDF templates, which make it rigid and pushes back the task of including complex logic in the source generating the XML Data. However it is recommended to use PDF templates in standard Forms (Government or other eg W2), where the template for the form is subject to very little or no change at all.
It is therefore imperative to analyze business requirements thoroughly and decide on the usability of rtf or pdf templates at the outset of the implementation.
Creation of PDF Templates:
To create a pdf template for BI Publisher, form fields need to be added.
These form fields contain XML tags that assign XML data values to the fields at runtime.
To add form fields, open a .pdf file in Acrobat standard.
Navigate ToolsàFormsàText field tool.
Draw the text form field on the pdf where you want the data to appear. Double click on the form field and add the XML tag to the Name field in the Properties box. Note that the Name added should be available in the XML Data tree file.eg: If POH_PO_NUM is an XML tag that holds the Purchase Order Number in XML Data file, you will need to add POH_PO_NUM to the Name field in the Properties for that form field on the PDF template. If you wish to use names that are different from the XML file, a mapping feature is available in the Template Definition to map the PDF template form field names with the XML tag names.
Below is a screenshot of a PDF template. You can set the properties for the data that will be displayed by this form field. Some of the properties you can set are the font type, font size, multi-line…

RTF templates:-
RTF templates are easier to use and support complex logic. These templates can be created with any Version of MS Word. The template file should be of .rtf format.
Designing a complex template structurally can be a little more tedious as compared to PDF templates, as MS Word is primarily not a design tool and most rtf template designs would have to be created from scratch.RTF templates can be used where
–there are many groups and subgroups with data,
–conditional formatting of form fields is required
–conditional display of data is required
–spooling multiple pages with different header/footer.
The above is a list off of the many more uses of the rtf template.
Creation of RTF Templates.
To create a rtf template for XML Publisher, you need to first design the template placing tables, nested tables, assigning the header/footer, setting the type landscape/portrait, setting the margins…Once the layout design is ready form fields need to be added.
In MS Word 2008 version Navigate to Developer, the form fields can be found under the Control option.
These form fields contain XML tags that assign XML data values to the fields at runtime.
Draw the text form field on the rtf template. Double click on the form field to add the XML tag. You can enter in the XML tag name or any name in the Default Text field if you want to mask the actual XML name or if you want to give the xml tag a more meaningful name.
Click on Add help text to enter the XML tag in the format , transaction_date being an xml tag in the input XML data file.
Note that this is also the area for adding any kind of logic to be applied on the data group.
However there is a limit on the number of characters that can be added to this area.
If the logic you intend to add has too many characters, the Help Key can be used.
Below is a screenshot of an RTF template. You can set the properties for the data directly from the MS word toolbar –Home which has Text font, size, colour etc…
Some of the properties you can set are the font type, font size, multi-line…All you need to do is select the form field and set the properties from the toolbar.
Note: As of now MS word does not allow to create form fields in the header or footer section of the .rtf template. The alternative is to write the XML tag the way it is in the header and footer as can be seen with .The grey areas are the form fields on the template.

XML Publisher Concurrent Programs in Oracle APPS (Ebusiness suite):
The following programs are an out of the box solution provided for XML Publisher reports. These programs make a call to java class files to integrate the XML data file and the templates to produce an output. Based on the requirements, you may use either one of the following programs
1) Program: XML Report Publisher
Short Name: XDOREPPB
This program can be used when no delivery channel like email, fax,… is required. Using this program enables the user to view the output at will from the View Output tab of the concurrent Program that runs the Oracle report. The user can also print the output by setting the print options while submitting the Concurrent Program.
2) Program: XML Publisher Report Bursting Program
Short Name: XDOBURSTREP
This program is used when there are multiple delivery channels involved, for eg: the output document needs to be printed emailed as well as faxed to the requestor/vendor…
Or when there is a range of data to be printed. In this case Bursting reuses the same template to generate a new file for each data set in the XML file.
To implement bursting, a control file is created. This control file is an XML file that contains delivery channel information like email server, print server, email address, no of copies, template, printer name….
The values can be hard coded in the file but it is best to assign these values dynamically at run time. The bursting control file reads these values from the XML data files through variables. The bursting program creates the output based on the template and the xml data file and delivers it to the channels specified.
———————————————————————————————————————
XML Publisher Setups
1) Use an existing Concurrent Program like PO Print or Invoice Print or create a new one based on your requirement.
Ensure the following are set on the Concurrent Program Definition
System AdministratoràConcurrentàProgramàDefine
Query your concurrent program. If the executable is an Oracle Report,
In the output section, set the following
Format = XML
Style= PDF Publisher
2) XML Publisher Administrator Responsibility- Create Data and Template Definitions
1. Create a new data definition
XML Publisher AdministratoràData Definition
Name: Enter a name for the data definition.
Code: Enter the short_name of the concurrent program which generates the XML file.
The short_name associates XML Publisher setups with the Oracle Reports concurrent Program. The Code for the Data definition should always be the same as the short_name of the concurrent program for XML Publisher reporting to work.
Application: Enter the same application that the Oracle Report concurrent Program uses.
Click on apply to put this Data Definition into effect.
You can alternatively add the following files to the Data Definition based on your setup.
XML Schema: A .xsd file needs to be uploaded when using PDF templates where the custom form field names need to be mapped with the XML tag names.
Data Template: A sample data xml file can be uploaded to this field.
Preview Data: Adding a sample .xml data file enables to preview the output from the template Definition.
Bursting Control file:The .xml control file has to be uploaded to this field when using bursting of the XML data file for generating outputs. The XDOBURSTREP program uses this file to process the output.
Sample bursting control file (Skip this if not using Bursting)
Note: If using bursting, to use the out of the box XML Publisher Report Bursting Program,
You can use the FND_REQUEST.SUBMIT_REQUEST in the After Report trigger in Oracle Reports or in your plsql code depending on your source file which generates the xml file to automate the complete process.
1. Create a new Template Definition
XML Publisher AdministratoràTemplate Definition
Name: Enter a name for the template definition
Code: Enter a short name for the template definition, preferably but not mandatory, the same used for the Data Definition code.
Application: Enter the Application the same as the Data Definition and Oracle Report Concurrent Program.
Data Definition: Select the Data Definition created in the above step.
Type: Enter the template type, RTF if it is an MS word template, PDF if it is a PDF template.
File: Upload the template file to be used for generating the output.
Language: Enter the language as English
Territory: Enter the territory as United States
Apply the changes to activate the Template definition.
1. Set Administration Properties
XML Publisher AdministratoràAdministration
Under Properties, Expand the General tab, specify a Temporary Directory Path from your file system. If this is not set, the XDOBURSTREP Program will error
3) Printer setup:-If Pasta printing is not enabled in your organization
System administratoràInstallàPrinter-àRegister
Query the printers- check the Type field for the Printers.
If Printer Type= ‘–PASTA Universal Printer Type’, for all your printers then you are set to use Pasta Printing and no further steps would be necessary.
If your printer type is for eg: HPLJ4SI you may proceed to follow the steps below
Add PDF Publisher style with PASTA_PDF as a driver for printer type
———————————————————————————————————————
Once the above setups are done, Navigate to the responsibility.
Submit the request.
When it completes click on View output to see the pdf generated by XML Publisher.
If using Bursting, the output will get emailed/printed/faxed based on the values provided for the bursting control file at run time.

Interfaces and Conversions in Oracle Applications

Overview:
Oracle provides flexible and flexible tools in the form of Interface programs to import the master and transactional data like Customers, Invoices, and Sales Orders etc from external systems into Oracle Applications. This article briefs you about some of the major interface programs.
Conversion/Interface Strategy:
1.Data Mapping: During the data mapping process, list of all the data sets and data elements that will need to be moved into the Oracle tables as part of conversion are identified. Data mapping tables are prepared as part of this activity that show what are the data elements that are needed by the target system to meet the business requirements and from where they will be extracted in the old system.

2.Download Programs: After the conversion data mapping is complete, download programs are developed that are used to extract the identified conversion data elements from the current systems in the form of an ASCII flat file. The structure of the flat file must match the structure of the Oracle standard interface tables. These flat files generated may be in text form or a comma or space delimited, variable or fixed format data file.

3.Upload Program: Once the data has been extracted to a flat file, it is then moved to the target file system and the data from the file is loaded into user defined staging tables in the target database using SQL Loader or UTL_FILE utilities. Then programs are written and run which validate the data in the staging tables and insert the same into the Oracle provided standard Interface tables.

4.Interface Program: Once the interface tables are populated, the respective interface program (each data element interface has a specific interface program to run) is submitted. The interface programs validate the data, derive and assign the default values and ultimately populate the production base tables.

Interface/Conversion examples with details:
The below list of interfaces/conversions are covered in this section. Details like pre-requisites required, interface tables, interface program, base tables, validations that need to be performed after inserting the details into the interface tables and required columns that need to be populated in the interface table are discussed for each interface.
1.Order Import Interface (Sales Order Conversion)
2.Item import (Item conversion)
3.Inventory On-hand quantity Interface
4.Customer conversion
5.Auto Invoice Interface
6.AR Receipts
7.Lockbox Interface
8.AP Invoices
9.Vendor
10.Purchase Orders
11.Requisition
12.Receiving
13.Journal import
14.Budget import
15.Daily Conversion Rates

1.Order Import Interface (Sales Order Conversion): Order Import enables you to import Sales Orders into Oracle Applications instead of manually entering them.

Pre-requisites:
Order Type
Line Type
Items
Customers
Ship Method/ Freight Carrier
Sales Person
Sales Territories
Customer Order Holds
Sub Inventory/ Locations
On hand Quantity

Interface tables:
OE_HEADERS_IFACE_ALL
OE_LINES_IFACE_ALL
OE_ACTIONS_IFACE_ALL
OE_ORDER_CUST_IFACE_ALL
OE_PRICE_ADJS_IFACE_ALL
OE_PRICE_ATTS_IFACE_ALL

Base tables:
OE_ORDER_HEADERS_ALL
OE_ORDER_LINES_ALL
Pricing tables: QP_PRICING_ATTRIBUTES

Concurrent Program: Order Import

Validations:
•Check for sold_to_org_id. If does not exist, create new customer by calling create_new_cust_info API.
•Check for sales_rep_id. Should exist for a booked order.
•Ordered_date should exist (header level)
•Delivery_lead_time should exist (line level)
•Earliest_acceptable_date should exist.
•Freight_terms should exist
Notes:
During import of orders, shipping tables are not populated.
If importing customers together with the order, OE_ORDER_CUST_IFACE_ALL has to be populated and the base tables are HZ_PARTIES, HZ_LOCATIONS.

Orders can be categorized based on their status:
1. Entered orders
2. Booked orders
3. Closed orders

Order Import API OE_ORDER_PUB.GET_ORDER and PROCESS_ORDER can also be used to import orders.

Some important columns that need to populated in the interface tables:
OE_HEADERS_IFACE_ALL:
•ORIG_SYS_DOCUMENT_REF
•ORDER_SOURCE
•CONVERSION_RATE
•ORG_ID
•ORDER_TYPE_ID
•PRICE_LIST
•SOLD_FROM_ORG_ID
•SOLD_TO_ORG_ID
•SHIP_TO_ORG_ID
•SHIP_FROM_ORG_ID
•CUSTOMER_NAME
•INVOICE_TO_ORG_ID
•OPERATION_CODE
OE_LINES_IFACE_ALL
•ORDER_SOURCE_ID
•ORIG_SYS_DOCUMENT_REF
•ORIG_SYS_LINE_REF
•ORIG_SYS_SHIPMENT_REF
•INVENTORY_ITEM_ID
•LINK_TO_LINE_REF
•REQUEST_DATE
•DELIVERY_LEAD_TIME
•DELIVERY_ID
•ORDERED_QUANTITY
•ORDER_QUANTITY_UOM
•SHIPPING_QUANTITY
•PRICING_QUANTITY
•PRICING_QUANTITY_UOM
•SOLD_FROM_ORG_ID
•SOLD_TO_ORG_ID
•INVOICE_TO_ ORG_ID
•SHIP_TO_ORG_ID
•PRICE_LIST_ID
•PAYMENT_TERM_ID

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

2.Item import (Item conversion): The Item Interface lets you import items into Oracle Inventory.

Pre-requisites:
•Creating an Organization
•Code Combinations
•Templates
•Defining Item Status Codes
•Defining Item Types

Interface tables:
•MTL_SYSTEM_ITEMS_INTERFACE
•MTL_ITEM_REVISIONS_INTERFACE (If importing revisions)
•MTL_ITEM_CATEGORIES_INTERFACE (If importing categories)
•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:
•MTL_SYSTEM_ITEMS_B
•MTL_ITEM_REVISIONS_B
•MTL_CATEGORIES_B
•MTL_CATEGORY_SETS_B
•MTL_ITEM_STATUS
•MTL_ITEM_TEMPLATES

Validations:
•Check for valid item type.
•Check for valid part_id/segment of the source table.
•Validate part_id/segment1 for master org.
•Validate and translate template id of the source table.
•Check for valid template id. (Attributes are already set for items, default attributes for that template, i.e., purchasable, stockable, etc )
•Check for valid item status.
•Validate primary uom of the source table.
•Validate attribute values.
•Validate other UOMs of the source table.
•Check for unique item type. Discard the item, if part has non-unique item type.
•Check for description, inv_um uniqueness
•Validate organization id.
•Load master records and category records only if all
•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.

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

3.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)
•MTLL_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

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

4.Customer conversion: Customer Interface helps you create customers in Oracle Applications.

Interface tables:
RA_CUSTOMERS_INTERFACE_ALL
RA_CUSTOMER_PROFILES_INT_ALL
RA_CONTACT_PHONES_INT_ALL
RA_CUSTOMER_BANKS_INT_ALL
RA_CUST_PAY_METHOD_INT_ALL

Base tables:
RA_CUSTOMERS
RA_ADDRESSES_ALL
RA_CUSTOMER_RELATIONSHIPS_ALL
RA_SITE_USES_ALL

Concurrent program: Customer Interface

Validations:
Check if legacy values fetched are valid.
Check if customer address site is already created.
Check if customer site use is already created.
Check is customer header is already created.
Check whether the ship_to_site has associated bill_to_site
Check whether associated bill_to_site is created or not.
Profile amounts validation:
Validate cust_account_id, validate customer status.
Check if the location already exists in HZ_LOCATIONS. If does not exist, create new location.

Some important columns that need to be populated in the interface tables:
RA_CUSTOMERS_INTERFACE_ALL:
ORIG_SYSTEM_CUSTOMER_REF
SITE_USE_CODE
ORIG_SYSTEM_ADDRESS_REF
INSERT_UPDATE_FLAG (I = Insert, U = Update)
CUSTOMER_NAME
CUSTOMER_NUMBER
CUSTOMER_STATUS
PRIMARY_SITE_USE_FLAG
LOCATION
ADDRESS1
ADDRESS2
ADDRESS3
ADDRESS4
CITY
STATE
PROVINCE
COUNTY
POSTAL_CODE
COUNTRY
CUSTOMER_ATTRIBUTE1
CUSTOMER_ATTRIBUTE2
CUSTOMER_ATTRIBUTE3
CUSTOMER_ATTRIBUTE4
CUSTOMER_ATTRIBUTE5
LAST_UPDATED_BY
LAST_UPDATE_DATE
CREATED_BY
CREATION_DATE
ORG_ID
CUSTOMER_NAME_PHONETIC

RA_CUSTOMER_PROFILES_INT_ALL:
INSERT_UPDATE_FLAG
ORIG_SYSTEM_CUSTOMER_REF
ORIG_SYSTEM_ADDRESS_REF
CUSTOMER_PROFILE_CLASS_NAME
CREDIT_HOLD
LAST_UPDATED_BY
LAST_UPDATE_DATE
CREATION_DATE
CREATED_BY
ORG_ID

RA_CONTACT_PHONES_INT_ALL:
ORIG_SYSTEM_CONTACT_REF
ORIG_SYSTEM_TELEPHONE_REF
ORIG_SYSTEM_CUSTOMER_REF
ORIG_SYSTEM_ADDRESS_REF
INSERT_UPDATE_FLAG
CONTACT_FIRST_NAME
CONTACT_LAST_NAME
CONTACT_TITLE
CONTACT_JOB_TITLE
TELEPHONE
TELEPHONE_EXTENSION
TELEPHONE_TYPE
TELEPHONE_AREA_CODE
LAST_UPDATE_DATE
LAST_UPDATED_BY
LAST_UPDATE_LOGIN
CREATION_DATE
CREATED_BY
EMAIL_ADDRESS
ORG_ID
--Customer API
Trading Community Architecture (TCA) is an architecture concept designed to support complex trading communities. These APIs utilize the new TCA model, inserting directly to the HZ tables.
API Details:
1.Set the organization id
Exec dbms_application_info.set_client_info(‘204’);
2.Create a party and an account
HZ_CUST_ACCOUNT_V2PUB.CREATE_CUST_ACCOUNT()
HZ_CUST_ACCOUNT_V2PUB.CUST_ACCOUNT_REC_TYPE
HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE
HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE
3.Create a physical location
HZ_LOCATION_V2PUB.CREATE_LOCATION()
HZ_LOCATION_V2PUB.LOCATION_REC_TYPE
4.Create a party site using party_id you get from step 2 and location_id from step 3.
HZ_PARTY_SITE_V2PUB.CREATE_PARTY_SITE()
HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE
5.Create an account site using account_id you get from step 2 and party_site_id from step 4.
HZ_CUST_ACCOUNT_SITE_V2PUB.CREATE_CUST_ACCT_SITE()
HZ_CUST_ACCOUNT_SITE_V2PUB.CUST_ACCT_SITE_REC_TYPE
6.Create an account site use using cust_acct_site_id you get from step 5 ans site_use_code = ‘BILL_TO’.
HZ_CUST_ACCOUNT_SITE_V2PUB.CREATE_CUST_SITE_USE()
HZ_CUST_ACCOUNT_SITE_V2PUB.CUST_SITE_USE_REC_TYPE
HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE

Base table:
HZ_PARTIES
HZ_PARTY_SITES
HZ_LOCATIONS
HZ_CUST_ACCOUNTS
HZ_CUST_SITE_USES_ALL
HZ_CUST_ACCT_SITES_ALL
HZ_PARTY_SITE_USES
Validations:
Check if legacy values fetched are valid.
Check if customer address site is already created.
Check if customer site use is already created.
Check is customer header is already created.
Check whether the ship_to_site has associated bill_to_site
Check whether associated bill_to_site is created or not.
Profile amounts validation:
Validate cust_account_id, validate customer status.
Check if the location already exists in HZ_LOCATIONS. If does not exist, create new location.
For detailed explanation refer to the below article:
http://www.erpschools.com/Apps/oracle-applications/articles/financials/Receivables/Customer-TCA-Architecture-and-API/index.aspx

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

5.Auto Invoice interface: This interface is used to import Customer invoices, Credit memos, Debit memos and On Account credits.

Pre-requisites:
Set of Books
Code combinations
Items
Sales representatives
Customers
Sales Tax rate
Payment Terms
Transaction Types
Freight Carriers
FOB
Batch Sources
Accounting Rules

Interface tables:
RA_INTERFACE_LINES_ALL
RA_INTERFACE_SALESCREDITS
RA_INTERFACE_DISTRIBUTIONS
RA_INTERFACE_ERRORS (details about the failed records)

Base tables:
RA_BATCHES
RA_CUSTOMER_TRX_ALL
RA_CUSTOMER_TRX_LINES_ALL
AR_PAYMENT_SCHEDULES_ALL
RA_CUSTOMER_TRX_LINE_SALESREPS
RA_CUST_TRX_GL_DIST_ALL
RA_CUSTOMER_TRX_TYPES_ALL

Concurrent Program: Auto invoice master program

Validations:
Check for amount, batch source name, conversion rate, conversion type.
Validate orig_system_bill_customer_id, orig_system_bill_address_id,
Validate if the amount includes tax flag.

Some important columns that need to be populated in the interface tables:
RA_INTERFACE_LINES_ALL:
AGREEMENT_ID
COMMENTS
CONVERSION_DATE
CONVERSION_RATE
CONVERSION_TYPE
CREDIT_METHOD_FOR_ACCT_RULE
CREDIT_METHOD_FOR_INSTALLMENTS
CURRENCY_CODE
CUSTOMER_BANK_ACCOUNT_ID
CUST_TRX_TYPE_ID
DOCUMENT_NUMBER
DOCUMENT_NUMBER_SEQUENCE_ID
GL_DATE
HEADER_ATTRIBUTE1–15
HEADER_ATTRIBUTE_CATEGORY
INITIAL_CUSTOMER_TRX_ID
INTERNAL_NOTES
INVOICING_RULE_ID
ORIG_SYSTEM_BILL_ADDRESS_ID
ORIG_SYSTEM_BILL_CONTACT_ID
ORIG_SYSTEM_BILL_CUSTOMER_ID
ORIG_SYSTEM_SHIP_ADDRESS_ID
ORIG_SYSTEM_SHIP_CONTACT_ID
ORIG_SYSTEM_SHIP_CUSTOMER_ID
ORIG_SYSTEM_SOLD_CUSTOMER_ID
ORIG_SYSTEM_BATCH_NAME
PAYMENT_SERVER_ORDER_ID
PREVIOUS_CUSTOMER_TRX_ID
PRIMARY_SALESREP_ID
PRINTING_OPTION
PURCHASE_ORDER
PURCHASE_ORDER_DATE
PURCHASE_ORDER_REVISION
REASON_CODE
RECEIPT_METHOD_ID
RELATED_CUSTOMER_TRX_ID
SET_OF_BOOKS_ID
TERM_ID
TERRITORY_ID
TRX_DATE
TRX_NUMBER

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

6.Receipt API: To bring in Unapplied Receipts and Conversion Receipts for Open Debit items to reduce the balance to the original amount due.

Pre-requisites:
Set of Books
Code combinations
Items
Quick Codes
Sales representatives
Customers
Sales Tax rate

API:
AR_RECEIPT_API_PUB.CREATE_CASH
AR_RECEIPT_API_PUB.CREATE_AND_APPLY

Base tables:
AR_CASH_RECEIPTS

Validations:
Check the currency and the exchange rate type to assign the exchange rate.
Validate bill to the customer.
Get bill to site use id.
Get the customer trx id for this particular transaction number.
Get payment schedule date for the customer trx id.

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

7.Lockbox interface: AutoLockbox lets us automatically process receipts that are sent directly to the bank instead of manually feeding them in Oracle Receivables.

AutoLockbox is a three step process:
1.Import: During this step, Lockbox reads and formats the data from your bank file into interface table AR_PAYMENTS_INTERFACE_ALL using a SQL *Loader
script.
2.Validation: The validation program checks data in this interface table for compatibility with Receivables. Once validated, the data is transferred into QuickCash tables (AR_INTERIM_CASH_RECEIPTS_ALL and
AR_INTERIM_CASH_RCPT_LINES_ALL).
3.Post QuickCash: This step applies the receipts and updates your customer’s balances.

Pre-Requisites:
Banks
Receipt Class
Payment Method
Receipt Source
Lockbox
Transmission format
AutoCash Rule sets

Interface tables:
AR_PAYMENTS_INTERFACE_ALL (Import data from bank file)
AR_INTERIM_CASH_RECEIPTS_ALL
AR_INTERIM_CASH_RCPT_LINES_ALL (Validate data in interface table and place in quick cash tables)

Base Tables:
AR_CASH_RECEIPTS
AR_RECEIVABLES_APPLICATIONS
AR_ADJUSTMENTS
AR_DISTRIBUTIONS_ALL
AR_PAYMENT_SCHEDULES_ALL

Concurrent program: Lockbox
Validations:
Check for valid record type, transmission record id.
Validate sum of the payments within the transmission.
Identify the lockbox number (no given by a bank to identify a lockbox).

Some important columns that need to be populated in the interface tables:
AR_PAYMENTS_INTERFACE_ALL:
STATUS
RECORD_TYPE
LOCKBOX_NUMBER
BATCH_NAME
TRANSIT_ROUTING_NUMBER
ACCOUNT
CHECK_NUMBER
REMITTANCE_AMOUNT
DEPOSIT_DATE
ITEM_NUMBER
CURRENCY_CODE
DEPOSIT_TIME

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

8.AP invoice interface: This interface helps us to import vendor invoices into Oracle applications from external systems into Oracle Applications.

Pre-requisites:
Set of Books
Code combinations
Employees
Lookups

Interface tables:
AP_INVOICES_INTERFACE
AP_INVOICE_LINES_INTERFACE

Base tables:
AP_INVOICES_ALL – header information
AP_INVOICE_DISTRIBUTIONS_ALL – lines info

Concurrent program: Payables Open Interface Import
Validations:
Check for valid vendor
Check for Source, Location, org_id, currency_code’s validity
Check for valid vendor site code.
Check if record already exists in payables interface table.

Some important columns that need to be populated in the interface tables:
AP_INVOICES_INTERFACE:
INVOICE_ID
INVOICE_NUM
INVOICE_DATE
VENDOR_NUM
VENDOR_SITE_ID
INVOICE_AMOUNT
INVOICE_CURRENCY_CODE
EXCHANGE_RATE
EXCHANGE_RATE_TYPE
EXCHANGE_DATE
DESCRIPTION
SOURCE
PO_NUMBER
PAYMENT_METHOD_LOOKUP_CODE
PAY_GROUP_LOOKUP_CODE
ATTRIBUTE1 TO 15
ORG_ID

AP_INVOICE_LINES_INTERFACE:
INVOICE_ID
INVOICE_LINE_ID
LINE_TYPE_LOOKUP_CODE
AMOUNT
DESCRIPTION
TAX_CODE
PO_NUMBER
PO_LINE_NUMBER
PO_SHIPMENT_NUM
PO_DISTRIBUTION_NUM
PO_UNIT_OF_MEASURE
QUANTITY_INVOICED
DIST_CODE_CONCATENATED
DIST_CODE_COMBINATION_ID
ATTRIBUTE1
ATTRIBUTE2
ATTRIBUTE3
ATTRIBUTE4
ATTRIBUTE5
ORG_ID

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

9.Vendor conversion/interface: This interface is used to import suppliers, supplier sites and site contacts into Oracle applications.

Pre-requisites setup’s required:
Payment terms
Pay Groups
CCID
Supplier classifications
Bank Accounts
Employees (if employees have to set up as vendors)

Interface tables:
AP_SUPPLIERS_INT
AP_SUPPLIER_SITES_INT
AP_SUP_SITE_CONTACT_INT

Base Tables:
PO_VENDORS
PO_VENDOR_SITES_ALL
PO_VENDOR_CONTACTS

Interface programs:
Supplier Open Interface Import
Supplier Sites Open Interface Import
Supplier Site Contacts Open Interface Import

Validations:
Check if vendor already exists
Check if vendor site already exists
Check if site contact already exists
Check if term is defined.
Some important columns that need to be populated in the interface tables:

AP_SUPPLIERS_INT:
VENDOR_NUMBER, VENDOR_NAME, VENDOR_TYPE, STATE_REPORTABLE, FED_REPORTABLE, NUM_1099, TYPE_1099, PAY_GROUP_LOOKUP_CODE, VENDOR_ID is auto generated.

AP_SUPPLIER_SITES_INT:
VENDOR_SITE_ID, ORG_ID, VENDOR_SITE_CODE, INACTIVE_DATE, PAY_SITE, PURCHASING_SITE, SITE_PAYMENT_TERM, ADDRESS1, ADDRESS2.ADDRESS3, CITY, STATE, COUNTRY, ZIP, PH_NUM, FAX_NUMBER, TAX_REPORTING_SITE_FLAG.
AP_SUP_SITE_CONTACTS_INT:
VENDOR_ID, VENDOR_SITE_ID, FIRST_NAME, LAST_NAME, AREA_CODE, PHONE, EMAIL, ORG_ID

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

10.Purchase Order conversion: The Purchasing Document Open Interface concurrent program was replaced by two new concurrent programs Import Price Catalogs and Import Standard Purchase Orders. Import Price Catalogs concurrent program is used to import Catalog Quotations, Standard Quotations, and Blanket Purchase Agreements. Import Standard Purchase Orders concurrent program is used to import Unapproved or Approved Standard Purchase Orders.
Import Standard Purchase Orders

Pre-requisites:
Suppliers, sites and contacts
Buyers
Line Types
Items
PO
Charge account setup

Interface Tables:
PO_HEADERS_INTERFACE
PO_LINES_INTERFACE
PO_DISTRIBUTIONS_INTERFACE
PO_INTERFACE_ERRORS (Fallouts)

Interface Program:
Import Standard Purchase Orders.

Base Tables:
PO_HEADERS_ALL
PO_LINES_ALL
PO_DISTRIBUTIONS_ALL
PO_LINE_LOCATIONS_ALL

Validations:
Header:
Check if OU name is valid
Check if Supplier is valid
Check if Supplier site is valid
Check if buyer is valid
Check if Payment term is valid
Check if Bill to and ship to are valid
Check if FOB, freight terms are valid
Lines:
Check if Line_type, ship_to_org, item, uom, ship_to_location_id, requestor, charge_account, deliver_to_location are valid
General:
Check for duplicate records in interface tables
Check if the record already exists in base tables.

Some important columns that need to be populated in the interface tables:
PO_HEADERS_INTERFACE:
INTERFACE_HEADER_ID (PO_HEADERS_INTERFACE_S.NEXTVAL), BATCH_ID, ORG_ID, INTERFACE_SOURCE_CODE, ACTION (‘ORIGINAL’,’UPDATE’,’REPLACE’), GROUP_CODE, DOCUMENT_TYPE_CODE, PO_HEADER_ID (NULL), RELEASE_ID, RELEASE_NUM, CURRENCY_CODE, RATE, AGENT_NAME, VENDOR_ID, VENDOR_SITE_ID, SHIP_TO_LOCATION, BILL_TO_LOCATION, , PAYMENT_TERMS

PO_LINES_INTERFACE:
INTERFACE_LINE_ID, INTERFACE_HEADER_ID, LINE_NUM, SHIPMENT_NUM, ITEM, REQUISITION_LINE_ID, UOM, UNIT_PRICE, FREIGHT_TERMS, FOB

PO_DISTRIBUTIONS_INTERFACE:
INTERFACE_LINE_ID, INTERFACE_HEADER_ID, INTERFACE_DISTRIBUTION_ID, DISTRIBUTION_NUM, QUANTITY_ORDERED, QTY_DELIVERED, QTY_BILLED, QTY_CANCELLED, DELIVER_TO_LOCATION_ID, DELIVER_TO_PERSON_ID, SET_OF_BOOKS, CHARGE_ACCT, AMOUNT_BILLED.

Import Blanket Purchase Agreements:
Interface Tables:
PO_HEADERS_INTERFACE
PO_LINES_INTERFACE

Interface program: Import Price Catalogs

Base tables:
PO_HEADERS_ALL
PO_LINES_ALL
PO_LINE_LOCATIONS_ALL

Example:
Suppose you want to create a blanket with one line and two price breaks and the details for the price break are as below:
1) Quantity = 500, price = 10, effective date from '01-JAN-2006' to
'31-JUN-2006'
2) Quantity = 500, price = 11, effective date from '01-JUL-2006' to
'01-JAN-2007'
To create the above the BPA, you would create ONE record in PO_HEADERS_INTERFACE and THREE records in PO_LINES_INTERFACE
LINE1: It will have only the line information. LINE NUM would be 1.
LINE2: For the first Price Break details, LINE NUM will be the same as above i.e. 1. SHIPMENT_NUM would be 1 and SHIPMENT_TYPE would be ‘PRICE BREAK’
LINE3: For the second Price Break details, LINE NUM will be the same as above i.e. 1. SHIPMENT_NUM would be 2 and SHIPMENT_TYPE would be ‘PRICE BREAK’
All the line-level records above must have the same INTERFACE_HEADER_ID.
For detailed explanation refer to the below article:
http://www.erpschools.com/Apps/oracle-applications/articles/financials/Purchasing/Import-Blanket-Purchase-Agreements/index.aspx

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

11.Requisition import: You can automatically import requisitions into Oracle Applications using the Requisitions Open Interface

Pre-requisites:
Set of Books
Code combinations
Employees
Items
Define a Requisition Import Group-By method in the Options window.
Associate a customer with your deliver-to location using the Customer Addresses window for internally sourced requisitions.

Interface tables:
PO_REQUISITIONS_INTERFACE_ALL
PO_REQ_DIST_INTERFACE_ALL

Base tables:
PO_REQUISITIONS_HEADERS_ALL
PO_REQUISITION_LINES_ALL
PO_REQ_DISTRIBUTIONS_ALL

Concurrent program: REQUISITION IMPORT

Validations:
Check for interface transaction source code, requisition destination type.
Check for quantity ordered, authorization status type.

Some important columns that need to be populated in the interface tables:
PO_REQUISITIONS_INTERFACE_ALL:
INTERFACE_SOURCE_CODE (to identify the source of your Requisitions)
DESTINATION_TYPE_CODE
AUTHORIZATION_STATUS
PREPARER_ID or PREPARER_NAME
QUANTITY
CHARGE_ACCOUNT_ID or charge account segment values
DESTINATION_ORGANIZATION_ID or DESTINATION_ORGANIZATION_CODE
DELIVER_TO_LOCATION_ID or DELIVER_TO_LOCATION_CODE
DELIVER_TO_REQUESTOR_ID or DELIVER_TO_REQUESTOR_NAME
ORG_ID
ITEM_ID or item segment values (values if the SOURCE_TYPE_CODE or DESTINATION_TYPE_CODE is 'INVENTORY')
PO_REQ_DIST_INTERFACE_ALL:
CHARGE_ACCOUNT_ID or charge account segment values
DISTRIBUTION_NUMBER
DESTINATION_ORGANIZATION_ID
DESTINATION_TYPE_CODE
INTERFACE_SOURCE_CODE
ORG_ID
DIST_SEQUENCE_ID (if MULTI_DISTRIBUTIONS is set to Y)

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

12.PO Receipts Interface: The Receiving Open Interface is used for processing and validating receipt data that comes from sources other than the Receipts window in Purchasing.

Pre-requisites:
Set of Books
Code combinations
Employees
Items

Interface tables:
RCV_HEADERS_INTERFACE
RCV_TRANSACTIONS_INTERFACE
PO_INTERFACE_ERRORS

Concurrent program: RECEIVING OPEN INTERFACE

Base tables:
RCV_SHIPMENT_HEADERS
RCV_SHIPMENT_LINES
RCV_TRANSACTIONS

Validations:
Check that SHIPPED_DATE should not be later than today.
Check if vendor is valid.
If Invoice number is passed, check for its validity
Check if Item is valid

Some important columns that need to be populated in the interface tables:
RCV_HEADERS_INTERFACE:
HEADER_INTERFACE_ID
GROUP_ID
PROCESSING_STATUS_CODE
RECEIPT_SOURCE_CODE
TRANSACTION_TYPE
SHIPMENT_NUM
RECEIPT_NUM
VENDOR_NAME
SHIP_TO_ORGANIZATION_CODE
SHIPPED_DATE
INVOICE_NUM
INVOICE_DATE
TOTAL_INVOICE_AMOUNT
PAYMENT_TERMS_ID
EMPLOYEE_NAME
VALIDATION_FLAG (Indicates whether to validate a row or not, values ‘Y’, ‘N’)
RCV_TRANSACTIONS_INTERFACE:
INTERFACE_TRANSACTION_ID
GROUP_ID
TRANSACTION_TYPE (’SHIP’ for a standard shipment (an ASN or ASBN) or ’RECEIVE’ for a standard receipt)
TRANSACTION_DATE
PROCESSING_STATUS_CODE =’PENDING’
CATEGORY_ID
QUANTITY
UNIT_OF_MEASURE
ITEM_DESCRIPTION
ITEM_REVISION
EMPLOYEE_ID
AUTO_TRANSACT_CODE
SHIP_TO_LOCATION_ID
RECEIPT_SOURCE_CODE
TO_ORGANIZATION_CODE
SOURCE_DOCUMENT_CODE
PO_HEADER_ID
PO_RELEASE_ID
PO_LINE_ID
PO_LINE_LOCATION_ID
PO_DISTRIBUTION_ID
SUBINVENTORY
HEADER_INTERFACE_ID
DELIVER_TO_PERSON_NAME
DELIVER_TO_LOCATION_CODE
VALIDATION_FLAG
ITEM_NUM
VENDOR_ITEM_NUM
VENDOR_ID
VENDOR_SITE_ID
ITEM_ID
ITEM_DESCRIPTION
SHIP_TO_LOCATION_ID

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

13.GL Journal interface: This interface lets you import journals from other applications like Receivables, Payables etc to integrate the information with General Ledger.

Pre-requisites:
Set of Books
Flex field Value sets
Code Combinations
Currencies
Categories
Journal Sources

Interface tables: GL_INTERFACE

Base tables:
GL_JE_HEADERS
GL_JE_LINES
GL_JE_BACTHES

Concurrent Program: Journal Import
Journal Posting --- populates GL_BALANCES
Validations:
Validate SOB, journal source name, journal category name, actual flag
A – Actual amounts
B – Budget amounts
E – Encumbrance amount
If you enter E in the interface table, then enter appropriate encumbrance ID, if B enter budget id.
Check if accounting date or GL date based period name is valid (i.e., not closed).
Check if accounting date falls in open or future open period status.
Check chart of accounts id based on Sob id.
Check if code combination is valid and enabled.
Check if record already exists in GL interface table.
Check if already journal exists in GL application.

Some important columns that need to be populated in the interface tables:
GL_INTERFACE:
STATUS
SET_OF_BOOKS_ID
ACCOUNTING_DATE
CURRENCY_CODE
DATE_CREATED
CREATED_BY
ACTUAL_FLAG
USER_JE_CATEGORY_NAME
USER_JE_SOURCE_NAME
CURRENCY_CONVERSION_DATE
ENCUMBRANCE_TYPE_ID
BUDGET_VERSION_ID
USER_CURRENCY_CONVERSION_TYPE
CURRENCY_CONVERSION_RATE
SEGMENT1 to
ENTERED_DR
ENTERED_CR
ACCOUNTED_DR
ACCOUNTED_CR
TRANSACTION_DATE
PERIOD_NAME
JE_LINE_NUM
CHART_OF_ACCOUNTS_ID
FUNCTIONAL_CURRENCY_CODE
CODE_COMBINATION_ID
DATE_CREATED_IN_GL
GROUP_ID

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

14.GL budget interface: Budget interface lets you load budget data from external sources into Oracle Applications.

Pre-requisites:
Set of Books
Flex field Value sets
Code Combinations

Interface tables: GL_BUDGET_INTERFACE

Base tables:
GL_BUDGETS
GL_BUDGET_ASSIGNMENTS
GL_BUDGET_TYPES

Concurrent program: Budget Upload

Validations:
Check if CURRENCY_CODE is valid.
Check if SET_OF_BOOKS_ID is valid.
Check if BUDGET_ENTITY_NAME (budget organization) is valid.

Some important columns that need to be populated in the interface tables:
GL_BUDGET_INTERFACE:
BUDGET_NAME NOT
BUDGET_ENTITY_NAME
CURRENCY_CODE
FISCAL_YEAR
UPDATE_LOGIC_TYPE
BUDGET_ENTITY_ID
SET_OF_BOOKS_ID
CODE_COMBINATION_ID
BUDGET_VERSION_ID
PERIOD_TYPE
DR_FLAG
STATUS
ACCOUNT_TYPE
PERIOD1_AMOUNT through PERIOD60_AMOUNT
SEGMENT1 through SEGMENT30

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

15.GL daily conversion rates: This interface lets you load the rates automatically into General Ledger.

Pre-requisites:
Currencies
Conversion rate Types

Interface tables: GL_DAILY_RATES_INTERFACE
Base tables:
GL_DAILY_RATES
GL_DAILY_CONVERSION_TYPES

Concurrent Program: You do not need to run any import programs. The insert, update, or deletion of rates in GL_DAILY_RATES is done automatically by database triggers on the GL_DAILY_RATES_INTERFACE. All that is required is to develop program to populate the interface table with daily rates information.

Validations:
Check if FROM_CURRENCY and TO_CURRENCY are valid.
Check if USER_CONVERSION_TYPE is valid.

Some important columns that need to be populated in the interface tables:
GL_DAILY_RATES_INTERFACE:
FROM_CURRENCY
TO_CURRENCY
FROM_CONVERSION_DATE
TO_CONVERSION_DATE
USER_CONVERSION_TYPE
CONVERSION_RATE
MODE_FLAG (D= Delete, I = Insert, U = Update)
INVERSE_CONVERSION_RATE

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