PL-SQL Interview Questions

PL-SQL Interview Questions

1. Describe the difference between a procedure, function and anonymous pl/sql block.
Level: Low
Expected answer : Candidate should mention use of DECLARE statement, a function must
return a value while a procedure doesn?t have to.

2. What is a mutating table error and how can you get around it?
Level: Intermediate
Expected answer: This happens with triggers. It occurs because the trigger is trying
to update a row it is currently using. The usual fix involves either use of views
or temporary tables so the database is selecting from one while updating the other.

3. Describe the use of %ROWTYPE and %TYPE in PL/SQL
Level: Low
Expected answer: %ROWTYPE allows you to associate a variable with an entire table row.
The %TYPE associates a variable with a single column type.

4. What packages (if any) has Oracle provided for use by developers?
Expected answer: Oracle provides the DBMS_ series of packages. There are many
which developers should be aware of such as DBMS_SQL, DBMS_PIPE, DBMS_TRANSACTION,
DBMS_LOCK, DBMS_ALERT, DBMS_OUTPUT, DBMS_JOB, DBMS_UTILITY, DBMS_DDL, UTL_FILE. If
they can mention a few of these and describe how they used them, even better. If
they include the SQL routines provided by Oracle, great, but not really what
was asked.

5. Describe the use of PL/SQL tables
Expected answer: PL/SQL tables are scalar arrays that can be referenced by a
binary integer. They can be used to hold values for use in later queries
or calculations. In Oracle 8 they will be able to be of the %ROWTYPE designation,
or RECORD.

6. When is a declare statement needed ?
The DECLARE statement is used in PL/SQL anonymous blocks such as with stand alone, non-stored PL/SQL procedures. It must come first in a PL/SQL stand alone file if it is used.

7. In what order should a open/fetch/loop set of commands in a PL/SQL block be implemented if you use the NOTFOUND cursor variable in the exit when statement? Why?
Expected answer: OPEN then FETCH then LOOP followed by the exit when. If not specified in this order will result in the final return being done twice because of the way the %NOTFOUND is handled by PL/SQL.

8. What are SQLCODE and SQLERRM and why are they important for PL/SQL developers?
Expected answer: SQLCODE returns the value of the error number for the last error encountered. The SQLERRM returns the actual error message for the last error encountered. They can be used in exception handling to report, or, store in an error log table, the error that occurred in the code. These are especially useful for the WHEN OTHERS exception.

9. How can you find within a PL/SQL block, if a cursor is open?
Expected answer: Use the %ISOPEN cursor status variable.

10. How can you generate debugging output from PL/SQL?
Expected answer: Use the DBMS_OUTPUT package. Another possible method is to just use the SHOW ERROR command, but this only shows errors. The DBMS_OUTPUT package can be used to show intermediate results from loops and the status of variables as the procedure is executed. The new package UTL_FILE can
also be used.

11. What are the types of triggers?
Expected Answer: There are 12 types of triggers in PL/SQL that consist of
combinations of the BEFORE, AFTER, ROW, TABLE, INSERT, UPDATE, DELETE and
ALL key words:
BEFORE ALL ROW INSERT
AFTER ALL ROW INSERT
BEFORE INSERT
AFTER INSERT etc.

SQL / SQLPlus Interview Questions

SQL / SQLPlus Interview Questions

1. How can variables be passed to a SQL routine?
Expected answer: By use of the & symbol. For passing in variables the numbers
1-8 can be used (&1, &2,...,&8) to pass the values after the command into the
SQLPLUS session. To be prompted for a specific variable, place the ampersanded
variable in the code itself:
"select * from dba_tables where owner=&owner_name;" . Use of double
ampersands tells SQLPLUS to resubstitute the value for each subsequent
use of the variable, a single ampersand will cause a reprompt for the
value unless an ACCEPT statement is used to get the value from the user.

2. You want to include a carriage return/linefeed in your output from a SQL script, how can you do this?
Expected answer: The best method is to use the CHR() function (CHR(10) is a return/linefeed) and the concatenation function "". Another method, although it is hard to document and isn?t always portable is to use the return/linefeed as a part of a quoted string.

3. How can you call a PL/SQL procedure from SQL?
Expected answer: By use of the EXECUTE (short form EXEC) command.

4. How do you execute a host operating system command from within SQL?
Expected answer: By use of the exclamation point "!" (in UNIX and some other OS) or the HOST (HO) command.

5. You want to use SQL to build SQL, what is this called and give an example
Expected answer: This is called dynamic SQL. An example would be:
set lines 90 pages 0 termout off feedback off verify off
spool drop_all.sql
select ?drop user ?username? cascade;? from dba_users
where username not in ("SYS?,?SYSTEM?);
spool off
Essentially you are looking to see that they know to include a command (in this case DROP USER...CASCADE;) and that you need to concatenate using the ?? the values selected from the database.

6. What SQLPlus command is used to format output from a select?
Expected answer: This is best done with the COLUMN command.

7. You want to group the following set of select returns, what can you group on?
Max(sum_of_cost), min(sum_of_cost), count(item_no), item_no
Expected answer: The only column that can be grouped on is the "item_no" column, the rest have aggregate functions associated with them.

8. What special Oracle feature allows you to specify how the cost based system treats a SQL statement?
Level: Intermediate to high Expected answer: The COST based system allows the use of HINTs to control the optimizer path selection. If they can give some example hints such as FIRST ROWS, ALL ROWS, USING INDEX, STAR, even better.

9. You want to determine the location of identical rows in a table before attempting to place a unique index on the table, how can this be done?
Level: High Expected answer: Oracle tables always have one guaranteed unique column, the rowid column. If you use a min/max function against your rowid and then select against the proposed primary key you can squeeze out the rowids of the duplicate rows pretty quick. For example: select rowid from emp e where e.rowid > (select min(x.rowid) from emp x where x.emp_no = e.emp_no); In the situation where multiple columns make up the proposed key, they must all be used in the where clause.

10. What is a Cartesian product?
Expected answer: A Cartesian product is the result of an unrestricted join of two or more tables. The result set of a three table Cartesian product will have x * y * z number of rows where x, y, z correspond to the number of rows in each table involved in the join.

11. You are joining a local and a remote table, the network manager complains about the traffic involved, how can you reduce the network traffic?
Level: High Expected answer: Push the processing of the remote data to the remote instance by using a view to pre-select the information for the join. This will result in only the data required for the join being sent across.

12. What is the default ordering of an ORDER BY clause in a SELECT statement?
Expected answer: Ascending

13. What is tkprof and how is it used?
Level: Intermediate to high Expected answer: The tkprof tool is a tuning tool used to determine cpu and execution times for SQL statements. You use it by first setting timed_statistics to true in the initialization file and then turning on tracing for either the entire database via the sql_trace parameter or for the session using the ALTER SESSION command. Once the trace file is generated you run the tkprof tool against the trace file and then look at the output from the tkprof tool. This can also be used to generate explain plan output.

14. What is explain plan and how is it used?
Level: Intermediate to high Expected answer: The EXPLAIN PLAN command is a tool to tune SQL statements. To use it you must have an explain_table generated in the user you are running the explain plan for. This is created using the utlxplan.sql script. Once the explain plan table exists you run the explain plan command giving as its argument the SQL statement to be explained. The explain_plan table is then queried to see the execution plan of the statement. Explain plans can also be run using tkprof.

15. How do you set the number of lines on a page of output? The width?
Level: Low Expected answer: The SET command in SQLPLUS is used to control the number of lines generated per page and the width of those lines, for example SET PAGESIZE 60 LINESIZE 80 will generate reports that are 60 lines long with a line width of 80 characters. The PAGESIZE and LINESIZE options can be shortened to PAGES and LINES.

16. How do you prevent output from coming to the screen?
Level: Low
Expected answer: The SET option TERMOUT controls output to the screen. Setting TERMOUT OFF turns
off screen output. This option can be shortened to TERM.

17. How do you prevent Oracle from giving you informational messages during and after a SQL statement execution?
Level: Low Expected answer: The SET options FEEDBACK and VERIFY can be set to OFF.

18. How do you generate file output from SQL?
Answer: By use of the SPOOL command

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'

FIXED ASSETS

FIXED ASSETS

1. What is unplanned depreciation?
Answer: Unplanned depreciation is a feature used primarily to comply with special
depreciation accounting rules in Germany and the Netherlands. However, you
also can use this feature to handle unusual accounting situations in which
you need to adjust the net book value and accumulated depreciation amounts
for an asset without affecting its cost. Oracle Assets immediately updates
the YTD and LTD depreciation and the net book value of the asset. The
unplanned depreciation expense you enter must not exceed the current net
book value (Cost - Salvage Value - Accumulated Depreciation) of the asset.
2. Can depreciation be suspended for a specified period of time?
Answer: Depreciation can be suspended at any time by changing the depreciate flag
on the book form to NO. Note that the total depreciation to be taken over
the life of the asset (including that incurred in periods the flag was set
to NO) will still be taken over the original life assigned to the asset. If
the asset was added with the depreciate flag set to NO, missed depreciation
will be caught up in the period the flag is changed to YES. If the asset
was added with the depreciate flag set to YES and the flag was later changed
to NO, the missed depreciation will be caught up in the last period of the
asset's ORIGINAL life; suspending depreciation will not extend the period
over which the asset is depreciated.
The Depreciate flag can also be set at the category level.
If you set the depreciate flag at the asset level, this will override the category
depreciate flag which is the default.
If the intention is to never have the asset Depreciate then the flag Depreciate
flag should be set to 'No' for the life of the asset or the asset can be entered
into the system fully reserved.
3. Can depreciation expense be manually input to override the system?
Answer: Depreciation reserve adjustments can be made to a TAX book. From Release
10.7, with unplanned depreciation you may manually override the depreciation
amount taken in the Corporate book. The depreciation amount cannot be
greater than the net book value of the asset.
4. How does the Depreciate When Placed In Service flag on my prorate
convention affect the calculation and allocation of depreciation?
Answer: With the exception of the method type Calculated Straight Line,
depreciation for the year is calculated based on the prorate date which maps
to a prorate period and rate on the prorate calendar. This total amount is
then allocated back to the individual periods in the year. If this flag is
set to NO, the years depreciation will be spread over the periods beginning
with the prorate date. If the flag is set to YES, the years depreciation
will be spread over the periods beginning with the date placed in service.
Note that total depreciation for the year remains unchanged, only
depreciation per period will differ.
When the method type Calculated Straight Line is used, this flag has no
effect. Yearly depreciation will be calculated as recoverable cost/life,
and allocated beginning with the prorate date.
5. GAAP defines two types of changes; changes in estimates which are to be
handled prospectively and errors which are to be retroactively corrected.
What Oracle functionality addresses these?
Answer: Expense an adjustment for correction of an error, amortize the adjustment
for a change in estimate.
6. How do I set up Oracle Assets to charge a half-month's depreciation in the
first and last periods of the assets life?
Answer: You must do the following:
a. Set up a prorate CALENDAR with semi-monthly periods. So your prorate
calendar will have 24 periods per fiscal year.
Example:
Period 1: Jan 01 - Jan 15
Period 2: Jan 16 - Jan 31
Period 3: Feb 01 - Feb 15
Period 4: Feb 16 - Feb 28
Period 5: Mar 01 - Mar 15 ...

b. Set up a prorate CONVENTION that maps the appropriate dates to the
middle of the month.
Example:
Jan 01 - Jan 31 map to Jan 16
Feb 01 - Feb 28 map to Feb 16
Mar 01 - Mar 31 map to Mar 16 ...

c. Assign your book to the appropriate prorate CALENDAR in the Book
Controls form. (You will also probably want it to depreciate EVENLY).

d. Set the default prorate convention to the appropriate mid-month
convention in the Default Depreciation Rules zone of the Asset
Categories form. You can also specify the prorate CONVENTION in the
Books window during the Detail Additions process.

Now when the depreciation program processes an asset whose date placed in
service is Jan 10, it will use the prorate convention to map that date to a
PRORATE DATE of Jan 16, and it will use the prorate date to map to
PRORATE PERIOD #2 in your prorate calendar. Thus, if you are running
depreciation for January (note that your DEPRECIATION CALENDAR can still
be monthly), you will get half a month's worth of depreciation for January.

*** It is not enough to set the prorate convention to a mid-month convention -
*** you must also set the prorate CALENDAR to be semi-monthly.
7. What is the difference between the new What-If feature and the old
depreciation projections functionality?
Answer: Using What-If Analysis, you can model depreciation scenarios for any number
of future periods based on depreciation attributes different from what you
have currently set up for the asset. Hence the name: What If Analysis.

Using Depreciation Projection, you can project depreciation expense based
on the asset's current depreciation method, life, etc.

Additionally, What-If Analysis is very flexible in allowing you to select a
subset of assets for analysis. Selection criteria include Range of Asset
Numbers, Range of Dates Placed in Service, Asset Category etc. For
Depreciation Projections, you must specify a BOOK and the program selects
all active assets for that book.
8. When I run depreciation I get the following error:
"Error: function fafbgcc returned failure (called from fadoflx) Getting
account CCID"
How do I correct this problem?
Answer: Set the profile options FA:PRINT_DEBUG and FA:DEPRN SINGLE to YES and
rerun depreciation. Make note of the asset number and distribution id.
Depreciation tries to build a code combination id (CCID) for one of the
following:

Asset Cost Account/CIP Cost Acct (Current Period Asset Clearing
Account)/CIP Clearing Acct (Current Period Adds) Depreciation Expense
Account (Prior Period Additions)

Check whether Allow Dynamic Inserts is being allowed for the Accounting
Flexfield (AFF).
Navigation:
Setup -> Financials -> Flexfields -> Segments -> Key
Query for Oracle General Ledger - Accounting Flexfield
If the Allow Dynamic Inserts box is not checked, unfreeze the flexfield
definition, check the box, refreeze and Compile. If it is already checked,
that means the combination generated is not valid. To find out what
combination is being generated, do the following:

Find out which category the asset belongs to by querying for the asset in
the Asset Workbench (Navigation: Assets -> Asset Workbench). Then query for
the asset category/book combination in the Asset Categories form
(Navigation: Setup -> Asset System -> Asset Categories). Using the Help ->
Tools -> Examine function from the menu, get the following information:

Account segment value for Asset Cost/Cost Clearing accounts
(or CIP Cost/CIP Clearing accounts if asset is CIP)
Code combination id (CCID) associated with these accounts

From the Book Controls form, you need to get the Default CCID for the
book utilizing Help -> Tools -> Examine method
(N)Setup -> Asset System -> Book Controls.

From the Inquiry/Financial Information form, you need to get the
distribution CCID for the asset utilizing the same method
(N)Inquiry -> Financial Information -> Assignments form.

Once you have the parameters, in Release 11 run the script faxagtst.sql
to see what combination is getting built and why it is failing.
For details on using faxagtst.sql, see Note 1062849.6

In Release 10.7, you will need to perform a Flexbuilder Test in the
application.
(N)Setup -> Financials -> Flexfields -> Flexbuilder -> Test
9. What depreciation methods are supported within Oracle Assets?
Answer: You may choose from the following:
Straight-line
Declining balance
Sum-of-year's digits
Units of production
ACRS and MACRS
Flat rate
Diminishing value
Bonus depreciation
In Release 11i, you will also be able to create formula-based methods
for depreciation.
10. When should I run the depreciation program?
Answer: For Release 10.7 and 11:
You should run depreciation when you are ready to close your depreciation
period. Depreciation cannot be rolled back once run. Since the depreciation
program closes the period, you should make sure that you entered all your
transactions for the current period. If you forget to enter a transaction in
the current period, you can enter a retroactive addition, transfer, or
retirement transaction in the following period. Oracle Assets will not
calculate adjustments to depreciation until you run depreciation again.

For Release 11i:
You can now run Depreciation as many times as you would like without closing
the period. When you are ready to close the period, on your final
Depreciation run, you would check the Close Period button on the form.
You have the capability in 11i to rollback depreciation. So if you run
Depreciation and you do not like the results, you can rollback Depreciation,
make your changes, and submit Depreciation again. Once the final Depreciation
has been run and the period is closed, you cannot rollback Depreciation for
the period. If you are closing the last period for a fiscal year, you cannot
enter a retroactive retirement for a period after the end of the year.
11. How often can I run depreciation?
Answer: For Release 10.7 and 11:
You can run depreciation only once per depreciation period. When you run
depreciation and close the period, you cannot reopen that period. You must
run depreciation for each corporate and tax book; Oracle Assets does not run
depreciation automatically for a tax book when you run depreciation for the
associated corporate book. Run Mass Copy to update your tax book prior to
running depreciation for the tax book.

For Release 11i:
You can run Depreciation as many times as you like. When you are ready to
close the period, on your last Depreciation run, check the Close Period
box on the Run Depreciation form.
12. What happens if I run depreciation when there are retirements or
reinstatements pending?
Answer:
When you submit depreciation, the process automatically runs the Calc
Gain/Loss (FARET) program to calculate gains and losses for any pending
retirements. You also can run FARET independently in order to reduce
depreciation processing time.
13. What is the difference between depreciation projections and depreciation?
Answer: Depreciation projections use a completely separate set of modules than the
Depreciation program. Depreciation projections do not take into account
adjustments entered in the current period, so any new retirements, transfers,
or adjustments will not effect the projection. Projections simply take a
snapshot of the asset at the start date of the projection and project
depreciation expense based on that information.
14. What happens if depreciation encounters an error? How do I proceed?
Answer: For Release 10.7 and 11:
If the depreciation program encounters an error, the program will stop and
perform a rollback to the previous commit. The program automatically resets
the DEPRN_RUNNING_FLAG to NO. If the error is straight forward, such as
Out of rollback segments, you can try to correct the error and then resubmit
the depreciation program. If the error is more serious, such as an operating
system error, you should contact Support before taking any further actions.

For Release 11i:
If the Depreciation program encounters and error, it will continue to
process all of the assets. The errored assets will appear in your logfile
so that you can fix them and resubmit Depreciation. Depreciation will then
only process the corrected assets.
15. What can I do to reduce processing time for the depreciation program?
Answer: Run Calc Gain/Loss several times throughout the period (this can be run
as often as you want). Then, when you finally run depreciation, the
Calc Gain/Loss program will process only the remaining retirements or
reinstatements. Ensure that your tables are not fragmented. Ask your
database administrator (DBA) to check for fragmentation problems. If
fragmentation exists, have the DBA export and reimport the tables, or
recreate them.

For Release 11 and 11i:
In addition to running Calculate Gains and Losses throughout the period,
run the Generate Accounts program before running Depreciation (N)Other
-> Requests -> Run. This will create the new code combinations needed so
that when you run Depreciation, the Generate Accounts program will not
detect any new asset with code combinations that need to be built, which
will greatly enhance overall performance.

For 11i customers, if the concurrent program (FAGDA) does not appear in the LOV
using Standard Report Submission (SRS) form, then please see Note 124955.1.
16. How does the depreciation program handle the end of a fiscal year?
Answer: At the end of a fiscal year, the depreciation program runs a short module
to prepare Oracle Assets for the next fiscal year. This module runs
automatically during the depreciation program. The fiscal years program
runs if the current period is the last period in the fiscal year. This
occurs when the period number of the current period = NUMBER_PER_FISCAL_YEAR
in the table FA_CALENDAR_TYPES. The fiscal years program checks if there are
rows defined for the next fiscal year in FA_DEPRN_PERIODS, FA_FISCAL_YEAR,
FA_CALENDAR_PERIODS, and FA_CONVENTIONS. If rows do not already
exist, the fiscal years program creates them.

For Release 11i:
Because of changes for the formula-based depreciation methods, you are now
required to create your fiscal year and calendars for the current fiscal
year + 1. Otherwise, you will be unable to run depreciation successfully.
17. What is the process flow for running Depreciation in 11i?
Answer:There have been several changes made by development to make the Depreciation/Create
Journals Process go much smoother for 11i. To take advantage of these changes, you must be on minipack H (2115788) and stand alone Patch 2130639.
In 11i, you may run Depreciation without closing the period. This allows you to check Depreciation
and make any necessary adjustments before closing the period.
The process should be:
1. Run Depreciation (without closing the period)
2. Run Create Journals
3. Review reports in FA and GL to determine if everything look correct
If everything is correct,
- Resubmit Depreciation (closing the period)
- Create Journals does not need to be ran again
If corrections are necessary,
- Rollback Journals
- Rollback Depreciation
- Make corrections
- Repeat steps 1-3.
With the application of the new code mentioned above, if you try to rollback
Depreciation without rolling back Create Journals, Rollback Depreciation will
error telling you that you need to rollback Create Journals.
18. What is the difference between the 'B' row and the 'D' row in the
FA_DEPRN_DETAIL table?
Answer:The 'B' (Books) row is added to the FA_DEPRN_DETAIL table when the asset is added
to Oracle Assets. There will only be one Books row per distribution in the
FA_DEPRN_DETAIL table. The 'D' (Depreciation) rows are added when Depreciation
is ran. There will be one row for each period and distribution that
Depreciation is ran for.
19. What is the Depreciation Adjustment account used for?
Answer:When you use the functionality of Tax Reserve Adjustment for the prior fiscal
of a Tax book the Depreciation Adjustment Account is used.
20. How can I add assets to a closed period (after Depreciation has been ran)?
Answer:Many types of transactions within Oracle Assets can be entered with
retro-active effective dates - (please consult the User Guide to see
if this is possible for the transactions you wanted to enter).
For the period accidentally closed in Assets, you can accrue for the
financial impact of these transactions using manual GL journals.
Oracle Assets will then catch up any financial impact of the transactions
when you Create Journal Entries for the next period. You can then reverse
out your accruals.

OTC

Order cycle
1.customer sends details of order or sales dept brings order from customer.
2.Enter the order in Sales order (SO)
3.Book an SO
4.Check for ATP (Available to promise)
5. Check for CTP (Capable to Promise) and CTD (capable to deliver)
6.Schedule SOship date
7.Send SO Acknowledgement
8.Resolve SO Holds
9.Release Credit Holds (Go to SO actions tab and release hold or increase the credit amount in customers->>standard-->>profile:amount)
10.Pick material (Pick release material from Inventory)
11.Pick wave MO is generated by system and material is moved to staging
12.prepare material for shipment (Packing)
13.Load Material
14.Ship material (Send ship documentslike bill of lading,customer invoice,packing list,vehicle load sheet)
15.Generate invoice in AR AR-> setup-> Auto invoice and send it to cus.
16.customer followup (calling customer thruphone,Dunning letters etc)
17.Customer payment aganist the invoice and receive payment
18.Financial reconcilation (reconsile shipment and payment done by customer)
Order to cash life cycle contains following steps
Order Entry --> OrderBooked --> Pick release --> Ship confirm --> Auto invoice
--> invoice --> Receipt --> Bank Reconcilations

Steps in Order management
Enter the sales orderBook the sales order
Steps in Shipping exexution
release the sales order for picking
Ship confirm the sales order
Steps in Receivables
Run auto invoiceInvoiceBank Reconcilation

Technical point of o2c(order to cash) is as follows
first we setup the customer,tax category details,item details,pricing definitions,payment terms,freight terms and then only we can order the product.
for set up main base tables are
RA_CUSTOMERS,
RA_ADDRESSES,
RA_CUST_SITE_USES_ALL
then go for the order entry
here effected tables are
OE_ORDER_HEADERS_ALL,
OE_ORDER_LINES_ALL
flow_status_code for both tables will be 'ENTER'
then book the order
flow_status_code for both tables of
OE_ORDER_HEADERS_ALL,
OE_ORDER_LINES_ALLwill be 'Booked'
WSH_DELIVERY_DETAILS,
WSH_DELIVERY_ASSIGNMENTS will be going to effect.
the pick release
the flow_status_code for OE_ORDER_HEADERS_ALL will be 'BOOKED"
and flow_status_code for OE_ORDER_LINES_ALL will be 'awaiting shipping"
pick conform
flow_status_code for both tables of OE_ORDER_HEADERS_ALL,
OE_ORDER_LINES_ALL will be 'PICKED'
ship conform
flow_status_code for both tables of OE_ORDER_HEADERS_ALL,
OE_ORDER_LINES_ALL will be 'SHIPPED'
here in WSH_NEW_ASSIGNMENTS the status_code is 'CL' then the order details are conformed and otherwise it is 'Open' stage
here in WSH_DELIVERY_DETAILS the oe_interface_flag is 'Y' then the order is closed and if it is 'N' it is an error in the order line details and we need to assign line level order details
once the shipping is conformed invoice will be generated
the effected tables are RA_CUSTOMER_TRX_ALL,RA_CUSTOMER_TRX_LINES_ALL
this is O2C

API's

API's
FND_PROGRAM.EXECUTABLE ( )
FND_PROGRAM.DELETE_EXECUTABLE( )
FND_PROGRAM.REGISTER( )
FND_PROGRAM.DELETE_PROGRAM( )
FND_PROGRAM.PARAMETER( )
FND_PROGRAM.DELETE_PARAMETER( )
FND_PROGRAM.INCOMPATIBILITY( )
FND_PROGRAM.DELETE_INCOMPATIBILITY( )
FND_PROGRAM.REQUEST_GROUP( )
FND_PROGRAM.DELETE_GROUP( )
FND_PROGRAM.ADD_TO_GROUP( )
FND_PROGRAM.REMOVE_FROM_GROUP( )
FND_REQUEST.SUBMIT_REQUEST( )
FND_CONCURRENT.WAIT_FOR_REQUEST( )
FND_REQUEST.SET_PRINT_OPTIONS ( )
FND_GLOBAL.USER_IDFND_GLOBAL.APPS_INITIALIZE(user_id in number,resp_id in number,resp_appl_id in number);
FND_GLOBAL.LOGIN_ID
FND_GLOBAL.CONC_LOGIN_ID
FND_GLOBAL.PROG_APPL_ID
FND_GLOBAL.CONC_PROGRAM_ID
FND_GLOBAL.CONC_REQUEST_ID
FND_PROFILE.PUT(name,value)
FND_PROFILE.GET(name IN varchar2,value out varchar2)
API’S IN APPS
PO
PO_CUSTOM_PRICE_PUB
PO_DOCUMENT_CONTROL_PUB
PO_DOC_MANAGER_PUB
PO_WFDS_PUB
AP
AP_NOTES_PUB
AP_WEB_AUDIT_LIST_PUB
INV
INV_COST_GROUP_PUB
INV_ITEM_CATALOG_ELEM_PUB
INV_ITEM_CATEGORY_PUB
INV_ITEM_PUB
INV_ITEM_REVISION_PUB
INV_ITEM_STATUS_PUB
INV_LOT_API_PUB
INV_MATERIAL_STATUS_PUB
INV_MOVEMENT_STATISTICS_PUB
INV_MOVE_ORDER_PUB
INV_PICK_RELEASE_PUB
INV_PICK_WAVE_PICK_CONFIRM_PUB
INV_RESERVATION_PUB
INV_SERIAL_NUMBER_PUB
INV_SHIPPING_TRANSACTION_PUB

Concurrent Program Submission Through Backend

declare
l_request_id number(9) ;
begin
l_request_id := Fnd_Request.submit_request
( 'PO', -Concurrent Prog Application Name
'SQL-USERS', -Conccurrent Prog Short Name
'Users Data', -Concurrent Prog description '',
-start time '',
-sub request 1000,
-first parameter value 2000,
-second parameter value
'CREATION_DATE');-third parameter value
Commit;
if l_request_id = 0
then fnd_file.put_line(fnd_file.log,'Prograqm not sumitted Succesfully');
else
fnd_file.put_line(fnd_file.log,'Prograqm sumitted Succesfully Request ID ='l_request_id);
End If;
Exception
when others then
fnd_file.put_line(fnd_file.log,'Error occured during Porgram submission');
End ;

from Triggers
declare
l_request_id number(9) ;
begin
l_request_id := Fnd_Request.submit_request
( 'PO', -Concurrent Prog ApplciationName
'SQL-USERS', -Conccurrent Prog Short Name
'Users Data', -Concurrent Prog description '',
-start time '',
-sub request 1000,
-first parameter value 2000,
-second parameter value
'CREATION_DATE','','','','','','','','','','' '','','','','','','','','','' '','','','','','','','','','' '','','','','','','','','','' '','','','','','','','','','' '','','','','','','','','','' '','','','','','','','','','' '','','','','','','','','','' '','','','','','','','','','' '','','','','','','','','','' '','','','','','','','','','');
Commit;
if l_request_id = 0 then
fnd_file.put_line(fnd_file.log,'Prograqm not sumitted Succesfully');
else
fnd_file.put_line(fnd_file.log,'Prograqm sumitted Succesfully Request ID ='l_request_id);
End If;
Exception
when others then
fnd_file.put_line(fnd_file.log,'Error occured during Porgram submission');
End ;
fnd_global.apps_initialize(user_id, Resp_id, Resp_appl_id);
-To initialize the Application Environment by specifying the UserID and RespID system will verify the User Access details based on that it will submit the Program.
1)fnd_Program.executable
2)fnd_program.register
3)fnd_program.request_group
4)fnd_program.add_to_group - Add concurrent Pogram to the Group
5)fnd_program.parameter - To create parameters for concurrent Program
6)fnd_program.incompatibility - TO attach Incompatibility Programs List
7)fnd_program.delete_group - To delete the Request Group
Any Table ,Procedure,Package,view any database Object starting with "FND" then it is relatedfor AOL(Application Obejct Library) , AOL ObjectSchema Name :APPLSYS"fnd" is nothing but foundation

Requisition Interface

Requisition Interface - PO_REQUISITIONS_INTERFACE_ALL
-- insert data into Interface tables
INSERT INTO PO_REQUISITIONS_INTERFACE_ALL
(interface_source_code
,source_type_code
,requisition_type
,destination_type_code
,item_id
,item_description
,quantity
,authorization_status
,preparer_id
,autosource_flag
,req_number_segment1 *** see the note
,header_attribute13 ---xtra infomation
,line_attribute15 ---xtra infomation
,uom_code
,destination_organization_id
,destination_subinventory
,deliver_to_location_id
,deliver_to_requestor_id
,need_by_date
,gl_date
,charge_account_id
,accrual_account_id
,variance_account_id
,org_id
,suggested_vendor_id
,suggested_vendor_site_id
,unit_price
,creation_date
,created_by
,last_update_date
,last_updated_by
)
VALUES ('INV'
,'VENDOR'
,'PURCHASE'
,'INVENTORY'
,rec_get_lines_info.inventory_item_id
,rec_get_lines_info.item_desc
,rec_get_lines_info.ordered_quantity
,l_authorization_status --------'INCOMPLETE' or 'APPROVED'
,g_employee_id
,'P'
,l_req_segment1
,'ZZ' ---xtra infomation
,rec_get_lines_info.ship_to_org_id ---xtra infomation
,rec_get_lines_info.uom_code
,rec_get_lines_info.ship_from_org_id
,rec_get_lines_info.subinventory
,rec_get_lines_info.location_id
,get_requestor (fnd_global.user_id) --rec_get_lines_info.requestor
,rec_get_lines_info.schedule_ship_date
,SYSDATE
,rec_get_lines_info.charge_account
,rec_get_lines_info.ap_accrual_account
,rec_get_lines_info.invoice_price_var_account
,g_org_id
, rec_get_lines_info.vendor_id
, rec_get_lines_info.vendor_site_id
,rec_get_lines_info.list_price
,SYSDATE
,fnd_global.user_id
,SYSDATE
,fnd_global.user_id
);


Note:

Standard grouping rules provided by Oracle are by
Buyer
Category
Item
Location
Vendor or
ALL , these grouping rules can be over written by populating "req_number_segment1" .
When we use req_number_segment1 with ALL grouping option, requisiton will be grouped by req_number_segment1

Concurrent Program:

apps.fnd_request.submit_request (application => 'PO' --Application,
program => 'REQIMPORT' --Program,
argument1 => 'INV' --Interface Source code,
argument2 => '' --Batch ID,
argument3 => 'ALL'--Group By,
argument4 => ''--Last Req Number,
argument5 => ''--Multi Distributions,
argument6 => 'N' --Initiate Approval after ReqImport
);

Purchase Order Interface

Purchase Order Interface -- PO_HEADERS_INTERFACE
Interface Tables Used

1)PO_HEADERS_INTERFACE
2)PO_LINES_INTERFACE
3)PO_DISTRIBUTIONS_INTERFACE

INSERT INTO po_headers_interface
(interface_header_id
,batch_id
,action
,org_id
,document_type_code
,vendor_id
,vendor_site_code
,vendor_site_id
,vendor_doc_num
,currency_code
,agent_id
,terms_id
,approval_status
,ship_to_location_id
,effective_date
,creation_date
,created_by
,last_update_date
,last_updated_by
,attribute_category
,attribute9 --xtra information
,comments
)
VALUES (po_headers_interface_s.NEXTVAL
,po_headers_interface_s.CURRVAL
,'ORIGINAL'
,g_org_id
,'STANDARD'
,rec_get_header_info.vendor_id
,rec_get_header_info.vendor_site_code
,rec_get_header_info.vendor_site_id
,po_headers_interface_s.CURRVAL
,'USD'
,l_agent_id
,rec_get_header_info.terms_id
,'APPROVED'
,rec_get_header_info.deliver_to_location_id
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,fnd_profile.VALUE ('USER_ID')
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,fnd_profile.VALUE ('USER_ID')
,'DS Fields'
,rec_get_header_info.attribute9 --xtra information
,l_comments
)

INSERT INTO po_lines_interface
(interface_header_id
,interface_line_id
,requisition_line_id
,line_num
,shipment_num
,line_type_id
,item
,item_description
,category_id
,unit_of_measure
,quantity
,unit_price
,ship_to_organization_id
,line_location_id
,effective_date
,need_by_date
,creation_date
,created_by
,last_update_date
,last_updated_by
,taxable_flag
)
VALUES (po.po_headers_interface_s.CURRVAL
,po.po_lines_interface_s.NEXTVAL
,rec_get_line_info.requisition_line_id
,l_line_num
,1
,rec_get_line_info.line_type_id
,rec_get_line_info.segment1
,rec_get_line_info.item_description
,rec_get_line_info.category_id
,rec_get_line_info.primary_unit_of_measure
,rec_get_line_info.quantity
,rec_get_line_info.unit_price
,rec_get_line_info.destination_organization_id
, po_line_locations_s.NEXTVAL
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,TO_DATE (rec_get_line_info.need_by_date, 'DD-MON-RRRR')
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,fnd_profile.VALUE ('USER_ID')
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,fnd_profile.VALUE ('USER_ID')
,'Y'
);


INSERT INTO po.po_distributions_interface
(interface_header_id
,interface_line_id
,interface_distribution_id
,req_distribution_id
,org_id
,distribution_num
,quantity_ordered
,destination_organization_id
,destination_type_code
,destination_subinventory
, deliver_to_location_id
,set_of_books_id
,charge_account_id
,budget_account_id
,accrual_account_id
,variance_account_id
,deliver_to_person_id
,creation_date
,created_by
,last_update_date
,last_updated_by
)
VALUES (po.po_headers_interface_s.CURRVAL
,po.po_lines_interface_s.CURRVAL
,po.po_distributions_interface_s.NEXTVAL
,rec_get_distrib_info.distribution_id
,g_org_id
,rec_get_distrib_info.distribution_num
,rec_get_distrib_info.req_line_quantity
,rec_get_distrib_info.destination_organization_id
,rec_get_distrib_info.destination_type_code
,l_destination_subinventory
, rec_get_line_info.deliver_to_location_id
,rec_get_distrib_info.set_of_books_id
,rec_get_distrib_info.code_combination_id
,rec_get_distrib_info.budget_account_id
,l_new_accrual_account_id
,rec_get_distrib_info.variance_account_id
,rec_get_line_info.to_person_id
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,fnd_profile.VALUE ('USER_ID')
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,fnd_profile.VALUE ('USER_ID')
);

Concurrent Program:
apps.fnd_request.submit_request (application => 'PO' --Application,
program => 'POXPOPDOI'--Program,
argument1 => ''--Buyer ID,
argument2 => 'STANDARD'--Document Type,
argument3 => ''--Document Subtype,
argument4 => 'N'--Process Items Flag,
argument5 => 'N'--Create Sourcing rule,
argument6 => ''--Approval Status,
argument7 => ''--Release Generation Method,
argument8 => ''--NULL,
argument9 => g_org_id--Operating Unit ID,
argument10 => ''--Global Agreement
);

Calling PO Approval workflow to approve POs

SELECT TO_CHAR (po_wf_itemkey_s.NEXTVAL)
INTO v_wf_seq
FROM SYS.DUAL;

v_itemkey := TO_CHAR (l_header.po_header_id)
|| '-'
|| v_wf_seq;
po_reqapproval_init1.start_wf_process (itemtype => 'POAPPRV'
,itemkey => v_itemkey
,workflowprocess => 'POAPPRV_TOP'
,actionoriginatedfrom => 'PO_FORM'
,documentid => l_header.po_header_id
,documentnumber => l_header.segment1
,preparerid => l_header.agent_id
,documenttypecode => 'PO'
,documentsubtype => 'STANDARD'
,submitteraction => 'APPROVE'
,forwardtoid => NULL
,forwardfromid => l_header.agent_id
,defaultapprovalpathid => 10
,note => NULL
,printflag => 'N'
,faxflag => NULL
,faxnumber => NULL
);

Concatenating Multiple Rows into single row

Concatenating Multiple Rows into single row
SELECT customer_product_id,
SUBSTR(MAX(REPLACE(SYS_CONNECT_BY_PATH(incident_number, '/') ,
'/',' ,')),3) Concatenated_String
FROM (SELECT cia.incident_number,cia.customer_product_id
,ROW_NUMBER () OVER (PARTITION BY customer_product_id ORDER BY customer_product_id) row#
FROM cs_incidents_all cia
,csi_item_instances cii
WHERE 1 = 1
AND cia.customer_product_id = cii.instance_id
AND cii.serial_number = 'XXXXXX'
AND NOT EXISTS (
SELECT 1
FROM cs_incident_statuses
WHERE NAME IN ('Cancelled', 'Closed')
AND incident_subtype = 'INC'
AND incident_status_id = cia.incident_status_id))
START
WITH ROW#=1
CONNECT
BY PRIOR row# = row#-1 and prior customer_product_id = customer_product_id
GROUP
BY customer_product_id

Spliting One row into Multi rows

Spliting One row into Multi rows
select * from (
with t as (select 'A-INSTALL,6-FOLLOWUP' str from dual)
SELECT trim(REGEXP_SUBSTR (str,'[^,]+' ,1,level))
FROM t
connect by instr(str, ',', 1, level - 1) > 0
and connect_by_root str = str)

SQL Loader Case Studies

SQL Loader Case Studies
http://www.cs.umbc.edu/portal/help/oracle8/server.815/a67792/ch04.htm

Importing material cost associated to item.

Importing material cost associated to item.


First check which cost elements are defined for the organizaion.


select cost_type_id,cost_type,description
from CST_COST_TYPES;


Output looks like this.


cost_type_id cost_type description
------------ ------------ ------------------------------------
1 Frozen Frozen Standard Cost Type
2 Average Average Cost Type
3 Pending Pending Standard Cost Type
.....


To find the sub element name defined for your organization for a particular cost type please check bom resources table.


In our example we are checking for material sub element in organization M1 with organization_id = 207 for cost_code type 1.


select resource_code,description,cost_element_id,cost_code_type
from bom_resources where organization_id = 207
and cost_code_type= 1;


resource_code description cost_element_id cost_code_type
------------- ----------------- --------------- --------------
Material Material Sub element 1 1
Labor Labor Sub element 1 1
Expense Expense Sub element 1 1


In our scenario we want to create a new item in organization M1 along with its material cost (say $11) assuming the same item is already created in master organization V1.


insert into mtl_system_items_interface
(process_flag, set_process_id, transaction_type,organization_id,segment1,
description,template_id,MATERIAL_COST,MATERIAL_SUB_ELEM)
values
(1,1,'CREATE',207,'TESTCOSTMATERIAL', 'Testing Item Material Cost Import',
259,11,'Material');
commit;


Note : Using mtl_system_items_interface 'Frozen cost' also be converted


Similary we can use MATERIAL_SUB_ELEM_ID, MATERIAL_OH_RATE,MATERIAL_OH_SUB_ELEM and MATERIAL_OH_SUB_ELEM_ID colums.


To check the subelements you can go to Cost management responsibility for your organization and under Setup > Sub-Elements you will see the respective
subelements being defined.


Now run the item import program ie. Item > Import > Import Items.


In Process Set enter 1 as we had used 1 in the set_process_id column.


Now enter 1 in create or update items field and hit OK button.You can check view requests to check whether your request has completed successfully.


Now go to organization items and query on 'TESTCOSTMATERIAL' the item that we had populated and you should be able to see the item.


Go to Tools > Item Costs and then hit Open button from Item Cost Summary screen
and you should see the details. In out example we see a material cost of $11 for the item in cost type 'Frozen'(Standard Costing).


NOTE: Item import cannot be used to update item costs.


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
You have to populate
CST_ITEM_CST_DTLS_INTERFACE,
CST_RESOURCE_COSTS_INTERFACE,
CST_RES_OVERHEADS_INTERFACE and
CST_DEPT_OVERHEADS_INTERFACE accordingly.


The concurrent request can be launched by navigating to cost->cost massedits->Import Cost Information.


The parameters for the Cost Import concurrent program and their description is
as follows :


Import Cost Option : A LOV is provided from which the user can select one of the import options which may be either to import Only item costs , Only resource costs , Only overhead rates or all the cost information .


Option Table from which data is processed
---------- --------------------------------------------
Only item cost cst_item_cst_dtls_interface
Only resource costs cst_resource_costs_interface
Only overhead rates cst_res_overheads_interface ,
cst_dept_overheads_interface


All Cost Information From all the four interface tables


Mode to run this request : A LOV is provided with possible two values , 'Insert new cost' or 'Remove and replace cost'.


The 'Insert new cost' mode , is useful if you are importing large # of items and are not sure if that Item/Organization/Cost Type combination already exists in the production tables, if it does then the row in the interface table would be flaged as errored and not imported.This would prevent any accidental overwrite of already existing data.
With 'Remove and replace cost' mode all the previous cost information for this item, cost_type and organization combination will be deleted from the production tables and the new information will overwrite (replace) the already existing one.


Group Id Option : A LOV is provided from which the user can either select 'ALL' or 'Specific Group Id' . If the user wishes to submit multiple Cost Import process requests he can do so by submitting one request per group id. For doing so the data in the interface tables should be stamped with distinct group id value by using the NEXTVAL from the sequence generator CST_LISTS_S .The use of this sequence geneartor is a MUST for generating multiple groups or may lead to data corruption as these interface tables are used by other processes too.
If the user selects "ALL" from the list then a group ID generated by a sequence will replace the group ID in the interface tables (if any) and all the unprocessed rows from the four interface table (viz.cst_item_cst_dtls_interface , cst_resource_costs_interface ,cst_res_overheads_interface , cst_dept_overheads_interface ) will be processed in one run.


Cost type to import to : The user is provided with a LOV from which he needs to select the cost type in which he wishes to import the cost information. Even if the user has populated a cost type or cost type ID in the interface tables, it would be overwritten with the one that is selected here.


The cost types that the user can pick from is restricted to the multi-org,
updateable cost types.


Delete succesfull rows : This parameter decides whether the successfully processed rows should be deleted from the interface tables at the end of the run. If the user selects 'Yes' then all the successful rows be deleted, basically rows that do not have their error flag set to "E".


Importing directly into Frozen/Average cost type (i.e non updateable cost types)and merging of new cost with existing costs is not supported at this time and would still have to be processed by cost update routines. Also when importing the costs from the interface table ,material overhead defaults (if any) specified for an Organization/Category would not be respected.




Minimum columns in each table that the user needs to provide
1. CST_ITEM_CST_DTLS_INTERFACE
The columns that the user has to provide are
a. Inventory_item_id
b. organization_ID or organization_code.
c. resource_ID or resource_code for cost elements other than 1(material).
If we are importing cost into material cost element and default material
subelement has been specified on 'Define Organization parameters' form
then that would be respected unless the user overides it with a value
in this column.
d. usage_rate_or_amount
e. cost_element_ID or cost_element
f. Process Flag (must be set to 1)


We default values for based_on_rollup, shrinkage_rate, inventory_asset_flag,
lot_size from the row from CST_ITEM_COSTS for this item and the default cost
type(default cost type of the cost type specified to import the costs into). If
there is no such row already defined in CST_ITEM_COSTS, then, the values for
these 4 columns also need to be specified and they have to be the same for all
rows of this item, cost type and organization combination.


CST_RESOURCE_COSTS_INTERFACE
a. Resource_ID or resource_code
b. organization_ID or organization_code
c. resource_rate
d. Process_flag (must be set to 1)


CST_RES_OVERHEADS_INTERFACE
a. Resource_ID or resource_code
b. Overhead_ID or overhead
c. Organization_ID or organization_code
d. Process_flag(must be set to 1)


CST_DEPT_OVERHEADS_INTERFACE
a. Department_ID or department
b. Overhead_ID or overhead
c. Organization_ID
d. Rate_or_amount
e. Process_flag(must be set to 1)


The other columns will be defaulted.


CST_ITEM_COSTS_INTERFACE can be used to import all the costs
MATERIAL_COST, OUTSIDE PROCESSING COST, OVER HEAD COST and other costs

Article from Uday Somavarapu Blog

Ship To address and Bill to Address Qry

Ship To address and Bill to Address Qry

SELECT hp.party_name
FROM hz_parties hp
,hz_party_sites site
,hz_locations loc
,hz_party_site_uses uses
,fnd_territories_vl terr
,hz_cust_site_uses hcsua
,hz_cust_acct_sites hcasa
WHERE site.party_id = hp.party_id
AND site.location_id = loc.location_id
AND site.party_site_id = uses.party_site_id(+)
AND loc.country = terr.territory_code
AND hp.status = 'A'
AND site.status = 'A'
AND hcsua.status = 'A'
AND uses.site_use_type = 'SHIP_TO'
AND NVL (uses.status, 'A') = 'A'
AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
AND hcasa.party_site_id = site.party_site_id
AND hcsua.site_use_code = uses.site_use_type
AND hcsua.site_use_id = ol.ship_to_org_id

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

SELECT hp.party_name
FROM hz_parties hp
,hz_party_sites site
,hz_locations loc
,hz_party_site_uses uses
,fnd_territories_vl terr
,hz_cust_site_uses hcsua
,hz_cust_acct_sites hcasa
WHERE site.party_id = hp.party_id
AND site.location_id = loc.location_id
AND site.party_site_id = uses.party_site_id(+)
AND loc.country = terr.territory_code
AND hp.status = 'A'
AND site.status = 'A'
AND hcsua.status = 'A'
AND uses.site_use_type = 'BILL_TO'
AND NVL (uses.status, 'A') = 'A'
AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
AND hcasa.party_site_id = site.party_site_id
AND hcsua.site_use_code = uses.site_use_type
AND hcsua.site_use_id = ol.invoice_to_org_id

OTC (Order To Cash Techinical Overview )

Order To Cash Techinical Overview

Enter order --> Book Order --> Pick Release --> Pick Slip --> Confirm Shipment àGenerate Invoice --> Customer follow up --> Receipt --> [Final Reconciliation]


Steps in Cycle:


1. Order Entry

This is first stage, when the order is entered in the system; it creates a record in order headers and Order Lines table.


Enter header details: Once you enter details on the order header and save it or move it to lines, record goes to one table OE_ORDER_HEADERS_ALL
FLOW_STATUS_CODE = ENTERED,

BOOKED_FLAG = N),

Primary key=HEADER_ID

No record exists in any other table for this order till now.


Enter Line details for this order: Enter different item numbers, quantity and other details in line tab. When the record gets saved, it goes to one table. Order header details will be linked with line details by order HEADER_ID.

OE_ORDER_LINES_ALL

FLOW_STATUS_CODE = ENTERED,

BOOKED_FLAG = N,

OPEN_FLAG = Y

Primary key= LINE_ID

2. Order Booking

This is next stage, when Order is booked then the Flow status changed from Entered to Booked.At this stage, these below table get affected.


OE_ORDER_HEADERS_ALL (FLOW_STATUS_CODE as BOOKED, BOOKED_FLAG updated to Y)
OE_ORDER_LINES_ALL (FLOW_STATUS_CODE as AWAITING_SHIPPING, BOOKED_FLAGupdated Y)
WSH_DELIVERY_DETAILS (DELIVERY_DETAIL_ID is assigned here, RELEASED_STATUS ‘R’ ready to release, LINE_ID comes as SOURCE_LINE_ID)
WSH_DELIVERY_ASSIGNMENTS (DELIVERY_ASSIGNMENT_ID is assigned for DELIVERY_DETAIL_ID present in WSH_DELIVERY_DETAILS, DELIVERY_ID remains blank till this stage)

In shipping transaction form order status remains "Ready to Release".

At the same time, Demand interface program runs in background And insert into inventory tables MTL_DEMAND, here LINE_ID come as a reference in DEMAND_SOURCE_LINE

3. Reservation

This step is required for doing reservations SCHEDULE ORDER PROGRAM runs in the background and quantities are reserved. Once these programs get successfully get completed.


MTL_DEMAND andMTL_RESERVATIONS table get updated. LINE_ID gets updated in DEMAND_SOURCE_LINE_ID in both the tables.

4. Pick Release

Pick Release is the process of putting reservation on on-hand quantity available in the inventory and pick them for particular sales order.

Pick release can be done from 'Release Sales Order' form or 'Pick release SRS' program can be scheduled in background. In both of these cases all lines of the order gets pick released depending on the Picking rule used. If specific line/s needs to be pick release it can be done from 'Shipping Transaction form. For this case Pick Release is done from 'Release Sales Order' form with Pick Confirm=NO.
Once pick release is done these are the tables get affected:

· If step 3 is not done then MTL_RESERVATIONS gets updated now.

· WSH_NEW_DELIVERIES (one record gets inserted with SOURCE_HEADER_ID= order header ID, STATUS_CODE=OP =>open)

· WSH_DELIVERY_ASSIGNMENTS (DELIVERY_ID gets assigned which comes from WSH_NEW_DELIVERIES)

· WSH_DELIVERY_DETAILS (RELEASED_STATUS ‘S’ ‘submitted for release’)

· MTL_TXN_REQUEST_HEADERS

· MTL_TXN_REQUEST_LINES (LINE_ID goes as TXN_SOURCE_LINE_ID)

· (move order tables. Here request is generated to move item from Source (RM or FG) sub-inventory to staging sub-inventory)

· MTL_MATERIAL_TRANSACTIONS_TEMP (link to above tables through MOVE_ORDER_HEADER_ID/LINE_ID, this table holds the record temporally)

· MTL_SERIAL_NUMBERS_TEMP (if item is serial controlled at receipt then record goes in this table)

· MTL_SERIAL_NUMBERS (enter value in GROUP_MARK_ID )

*In shipping transaction form order status remains "Released to Warehouse" and all the material still remains in source sub-inventory. We need to do Move Order Transaction for this order. Till this no material transaction has been posted to MTL_MATERIAL_TRANSACTIONS


5. Pick Confirm/ Move Order Transaction

Items are transferred from source sub-inventory to staging Sub-inventory. Here material transaction occurs.

Order line status becomes 'Picked' on Sales Order and 'Staged/Pick Confirmed' on Shipping Transaction Form.


MTL_MATERIAL_TRANSACTIONS_TEMP (Record gets deleted from here and gets posted to MTL_MATERIAL_TRANSACTIONS)
OE_ORDER_LINES_ALL (FLOW_STATUS_CODE ‘PICKED’ )
MTL_MATERIAL_TRANSACTIONS (LINE_ID goes as TXN_SOURCE_LINE_ID)
MTL_TRANSACTION_ACCOUNTS
WSH_DELIVERY_DETAILS (RELEASED_STATUS becomes ‘Y’ => ‘Released’ )
WSH_DELIVERY_ASSIGNMENTS
MTL_ONHAND_QUANTITIES
MTL_SERIAL_NUMBERS_TEMP (record gets inserted after putting details for the item which are serial controlled at 'Sales order issue')
MTL_SERIAL_NUMBERS (record gets inserted after putting details for the item which are serial controlled at 'Sales order issue')

* This step can be eliminated if we set Pick Confirm=YES at the time of Pick Release

6. Ship Confirm

Here is the ship confirm interface program runs in background. Data removed from


WSH_NEW_DELIVERIES. The items on the delivery shipped to customer at this stage
OE_ORDER_LINES_ALL (FLOW_STATUS_CODE ‘shipped’)
WSH_DELIVERY_DETAILS (RELEASED_STATUS ‘C’ ‘Shipped’, SERIAL_NUMBER if quantity is ONE)
WSH_SERIAL_NUMBERS (records gets inserted with the DELIVERY_DETAIL_ID reference, only in case of shipped quantity is two or more)
MTL_TRANSACTION_INTERFACE
MTL_MATERIAL_TRANSACTIONS (linked through Transaction source header id)
MTL_TRANSACTION_ACCOUNTS
Data deleted from MTL_DEMAND, MTL_RESERVATIONS
Item deducted from MTL_ONHAND_QUANTITIES
MTL_SERIAL_NUMBERS_TEMP (records gets deleted from this table)
MTL_SERIAL_NUMBERS (Serial number stauts gets updated CURRENT_STATUS=4 , 'Issued out of store')

7. Enter Invoice

After shipping the order, order lines are eligible to transfer to RA_INTERFACE_LINES_ALL. Workflow background engine picks those records and post it to RA_INTERFACE_LINES_ALL. This is also called Receivable interface, this mean information moved to accounting area for invoicing details. Invoicing workflow activity transfers shipped item information to Oracle Receivables. At the same time records also goes in the table RA_INTERFACE_SALESCREDITS_ALL which hold details of sales credit for the particular order


RA_INTERFACE_LINES_ALL (interface table into which the data is transferred from order management) Then Autoinvoice program imports data from this table which get affected into this stage are receivables base table. At the same time records goes in
RA_CUSTOMER_TRX_ALL (CUST_TRX_ID is primary key to link it to TRX_LINES table and TRX_NUMBER is the invoice number)
RA_CUSTOMER_TRX_LINES_ALL (LINE_ATTRIBUTE_1 and LINE_ATTRIBUTE_6 are linked to order number and LINE_ID of the orders)

8. Complete Line

In this stage order line level table get updated with Flow status and open flag.


OE_ORDER_LINES_ALL (FLOW_STATUS_CODE ‘shipped’, OPEN_FLAG “N”)

9. Close Order

This is last step of Order Processing. In this stage only OE_ORDER_LINES_ALL table get updated. These are the table get affected in this step.

OE_ORDER_LINES_ALL (FLOW_STATUS_CODE ‘closed’, OPEN_FLAG “N”)

To import a routing with operations and resource

To import a routing with operations and resources, you need to populate the following tables:


BOM_OP_ROUTINGS_INTERFACE
BOM_OP_SEQUENCES_INTERFACE
BOM_OP_RESOURCES_INTERFACE


With these three tables, you can create routing header information and assign
operation and resource details.


PROCESS_FLAG
The column PROCESS_FLAG indicates the current state of processing for a row in the interface table. All inserted rows must have the PROCESS_FLAG set to 1.


1 - Pending
2 - Assigned Succeeded
3 - Assign/Validation Failed
4 - Validation Succeeded
7 - Import Succeeded
BOM_OP_ROUTINGS_INTERFACE TABLE.


a. REQUIRED COLUMNS FOR THE BOM_OP_ROUTINGS_INTERFACE TABLE.


You must always enter values for the following required columns when
you insert rows into the BOM_OP_ROUTINGS_INTERFACE table.


PROCESS_FLAG
ASSEMBLY_ITEM_ID
ORGANIZATION_ID
ROUTING_TYPE


If you are creating an alternate routing, you must also enter a value
in the ALTERNATE_ROUTING_DESIGNATOR column.


If the routing you import references a common routing, you must enter a
value in the COMMON_ASSEMBLY_ITEM_ID or the COMMON_ROUTING_SEQUENCE_ID
columns. Routings can only reference common routings that belong to the
same organization. If the routing does not reference a common routing,
the Bill and Routing Interface program defaults the value of the
ROUTING_SEQUENCE_ID for the COMMON_ROUTING_SEQUENCE_ID.


You can specify, in the ROUTING_TYPE column, whether the routing is a
manufacturing routing or an engineering routing. If you do not include
a value for this column, Oracle Bills of Material defaults a value
of 1 (manufacturing), and creates a manufacturing routing. To create
an engineering routing, you must enter a value of 2 (engineering) for
the ROUTING_TYPE column.


For each new row you insert into the BOM_OP_ROUTINGS_INTERFACE table,
you should set the PROCESS_FLAG to 1 (Pending).




b. DERIVED/DEFAULTED VALUES FOR BOM_OP_ROUTINGS_INTERFACE.


The Bill and Routing Interface program derives or defaults most of the
data required to create a manufacturing or engineering routing.
The Bill and Routing Interface program derives or defaults the columns
using the same logic as the Define Routing form or the Define
Engineering Routing form. When you populate a column in the interface
table, the program imports the row with the data you included and does
not default a value.


If you enter a value for the ASSEMBLY_ITEM_NUMBER or COMMON_ITEM_NUMBER
column, you must insert the system item flexfield separator between each
segment of your item number.


INSERT INTO bom_op_routing_interface
(process_flag
,assembly_item_id
,organization_id
,routing_type
,transaction_type
)
VALUES (1
,&inventory_item_id
,&organization_id
,1
,'insert'
);


BOM_OP_SEQUENCES_INTERFACE TABLE.


a. REQUIRED COLUMNS FOR THE BOM_OP_SEQUENCES_INTERFACE TABLE


Each imported record must have a value for the following columns:


PROCESS_FLAG
ROUTING_SEQUENCE_ID
OPERATION_SEQ_NUM
DEPARTMENT_ID
EFFECTIVITY_DATE


You must also specify a value in the ALTERNATE_ROUTING_DESIGNATOR column
if you assign an operation to an alternate routing and have not entered
a value for the ROUTING_SEQUENCE_ID column.


When you insert rows into BOM_OP_SEQUENCES_INTERFACE, you must set the
PROCESS_FLAG to 1 (Pending) for the Bill and Routing Interface program
to process the record.




b. DERIVED/DEFAULTED COLUMN VALUES FOR BOM_OP_SEQUENCES_INTERFACE.


The Bill and Routing Interface program derives or defaults most of the
data required to assign operations to a routing. You can optionally
include a value for derived or defaulted columns, as well as data for
any of the other columns in the interface. The interface program uses
the same logic to derive or default column values in the
BOM_OP_SEQUENCES_INTERFACE table as in the BOM_OP_ROUTINGS_INTERFACE
table. When you populate a column in the interface table, the program
imports the row with the data you included and does not default a
value. However, if you do not enter data in a derived or defaulted
column, the program automatically imports the row with the derived or
defaulted value.


BOM_OP_SEQUENCES_INTERFACE Derived or Defaulted Value
OPERATION_SEQUENCE_ID Sequence BOM_OPERATIONAL_SEQUENCES_S
ROUTING_SEQUENCE_ID From BOM_OP_ROUTINGS_INTERFACE or
BOM_OPERATIONAL_ROUTINGS
LAST_UPDATE_DATE System Date
LAST_UPDATE_BY Userid
CREATION_DATE System Date
CREATED_BY Userid
STANDARD_OPERATION_ID From OPERATION_CODE
DEPARTMENT_ID From DEPARTMENT_CODE
MINIMUM_TRANSFER_QUANTITY 0
COUNT_POINT_TYPE 1
EFFECTIVITY_DATE System Date
BACKFLUSH_FLAG 1
REQUEST_ID From FND_CONCURRENT_REQUESTS
ASSEMBLY_ITEM_ID From ASSEMBLY_ITEM_NUMBER
OPTION_DEPENDENT_FLAG 2
ORGANIZATION_ID From ORGANIZATION_CODE
RESOURCE_ID1 From RESOURCE_CODE1
RESOURCE_ID2 From RESOURCE_CODE2
RESOURCE_ID3 From RESOURCE_CODE3


INSERT INTO bom_op_sequences_interface
(process_flag
,assembly_item_id
,organization_id
,operation_seq_num
,department_id
,effectivity_date
,transaction_type
)
VALUES (1
,&assembly_item_id
,&organization_id
,&operation_seq_num
,&department_id
,&effectivity_date
,'insert'
);

BOM_OP_RESOURCES_INTERFACE TABLE.
a. REQUIRED COLUMNS FOR THE BOM_OP_RESOURCES_INTERFACE TABLE.


You must always enter values for the following required columns when
you insert rows into the BOM_OP_RESOURCES_INTERFACE table:


PROCESS_FLAG
RESOURCE_SEQ_NUM
RESOURCE_ID
OPERATION_SEQUENCE_ID


You must specify a value in the ALTERNATE_ROUTING_DESIGNATOR column if
you assign resources to an alternate routing and have not entered a
value for the ROUTING_SEQUENCE_ID or the OPERATION_SEQUENCE_ID column.


When you insert a row into the BOM_OP_RESOURCES_INTERFACE table, you
must set the PROCESS_FLAG to 1 (Pending) for the Bill and Routing
Interface program to process the record.




b. DERIVED/DEFAULTED COLUMN VALUES FOR BOM_OP_RESOURCES_INTERFACE.


The Bill and Routing Interface program derives or defaults most of the
data required to assign a resource to an operation. You can optionally
include a value for derived or defaulted columns, as well as data for
any of the other columns in the interface. The interface program uses
the same logic to derive or default column values in the
BOM_OP_RESOURCES_INTERFACE table as in the BOM_OP_ROUTINGS_INTERFACE
table and BOM_OP_SEQUENCES_INTERFACE tables. When you populate a
column in the interface table, the program imports the row with the
data you included and does not default a value. However, if you do not
enter data in a derived or defaulted column, the program automatically
imports the row with the derived or defaulted value.


BOM_OP_RESOURCES_INTERFACE Derived or Defaulted Value
OPERATION_SEQUENCE_ID Sequence BOM_OP_SEQUENCES_INTERFACE or
BOM_OPERATION_SEQUENCES_S
RESOURCE_ID From RESOURCE_CODE
ACTIVITY_ID From ACTIVITY
STATDARD_RATE_FLAG From BOM_RESOURCES.STANDARD_RATE_FLAG
ASSIGNED UNITS 1
USAGE_RATE_OR_AMOUNT 1
USAGE_RATE_OR_AMOUNT_INVERSE 1
BASIS_TYPE From BOM_RESOURCES.DEFAULT_BASIS
SCHEDULE_FLAG 2
LAST_UPDATE_DATE System Date
LAST_UPDATED_BY Userid
CREATION_DATE System Date
CREATED_BY Userid
AUTOCHARGE_TYPE From BOM_RESOURCES.AUTOCHARGE_TYPE
REQUEST_ID From FND_CONCURRENT_REQUESTS
ASSEMBLY_ITEM_ID From ASSEMBLY_ITEM_NUMBER
ORGANIZATION_ID From ORGANIZATION_CODE
ROUTING_SEQUENCE_ID From BOM_OP_ROUTINGS_INTERFACE or
BOM_OPERATIONAL_ROUTINGS
INSERT INTO bom_op_resources_interface
(process_flag
,resource_seq_num
,resource_id
,assembly_item_id
,organization_id
,operation_seq_num
,effectivity_date
,transaction_type
)
VALUES (1
,&resource_seq_num
,&resource_id
,&assembly_item_id
,&organization_id
,&operation_seq_num
,&effectivity_date
,'insert'
);

This Article from Uday Somavarapu BlogSpot

Accounts Payable Suppliers Show In Release 12 Receivables Customer

Following Oracle support note has the answers to some common questions..

FAQ: Why Do Accounts Payable Suppliers Show In Release 12 Receivables Customer Form? [ID 733801.1]

R12 AP Tax Tables

select * from ZX_TAXES_VL

select * from ZX_TAXES_TL

select * from ZX_TAXES_b

Items Master and Child Conflict Trouble shooting guide

Troubleshooting Guide for INV_IOI_MASTER_CHILD Errors in Item Import [ID 429924.1]

AP Invoices conversion process

The process for AP invoice program is as follows:

Excel Sheet -> Data file -> SQL Loader (Control file) -> Staging Tables -> Run Validation program -> Interface Tables -> Run the Payables Open Invoice Import Program -> Base Tables

Staging tables:
Create a custom staging table XX_AP_INVOICE_INTERFACE, XX_AP_INVOICE_LINES_INTERFACE which should be same as seeded interface table AP_INVOICES_INTERFACE and AP_INVOICE_LINES_INTERFACE.

Interface Tables:
1. AP_INVOICES_INTERFACE
2. AP_INVOICE_LINES_INTERFACE

Base Tables:
1. AP_INVOICES_ALL
2. AP_INVOICE_DISTRIBUTIONS_ALL
3. AP_PAYMENT_SCHEDULES_ALL

Process Flow:
-----------------
1. Excel sheet template is prepared which contains all the columns in AP_INVOICES_INTERFACE and AP_INVOICE_LINES table.
2. Convert the Excel Sheet which contains data to be interfaced given by the user into CSV file format.
3. Format the CSV file so that it contains only the relevant data separated with commas.
4. Rename the CSV into DAT file.
5. Prepare the Control File.
6. Run SQL Loader Script.
7. Check the Log file for any errors. In case of any errors rectify it and re-run the SQL Loader Script.
8. Run the PL/SQL Program to validate staging table data and load to Interface tables.
9. Run the Payables Open Invoice Import Program.
10. Check for the status in the Interface Table.
11. Query for the data in the front-end.

Script to Cancel AP Invoices

/* Formatted on 2010/08/09 20:15 (Formatter Plus v4.8.0) */
CREATE OR REPLACE PROCEDURE cancel_invoices (ip_operating_unit IN VARCHAR2)
AS
l_resp_id NUMBER;
l_appl_id NUMBER;
l_user_id NUMBER := apps.fnd_global.user_id;
l_org_id NUMBER := apps.fnd_global.org_id;
l_message_name VARCHAR2 (1000);
l_invoice_amount NUMBER;
l_base_amount NUMBER;
l_tax_amount NUMBER;
l_temp_cancelled_amount NUMBER;
l_cancelled_by VARCHAR2 (1000);
l_cancelled_amount NUMBER;
l_cancelled_date DATE;
l_last_update_date DATE;
l_original_prepayment_amount NUMBER;
l_pay_curr_invoice_amount NUMBER;
l_token VARCHAR2 (100);
l_boolean BOOLEAN;
err_msg VARCHAR2 (2000);

CURSOR invoice_cur
IS
SELECT aia.invoice_id, aia.last_updated_by, aia.last_update_login,
aia.gl_date, aia.invoice_num
FROM xx_ap_invoices_conv_stg a,
ap_invoices_all aia,
ap_invoice_lines_all aila
WHERE a.ls_inv_num = aia.invoice_num
AND a.ls_org_id = aia.org_id
AND aia.invoice_id = aila.invoice_id
AND aia.org_id = aila.org_id
AND aia.payment_status_flag = 'N'
AND NVL (aila.cancelled_flag, 'N') <> 'Y';
--- AND aia.invoice_num = '65431';
BEGIN
BEGIN
SELECT DISTINCT fr.responsibility_id, frx.application_id
INTO l_resp_id, l_appl_id
FROM apps.fnd_responsibility frx,
apps.fnd_responsibility_tl fr
WHERE fr.responsibility_id = frx.responsibility_id
AND UPPER (fr.responsibility_name) LIKE
UPPER (DECODE (ip_operating_unit,
'OU USA MA', 'Payables Manager',
'OU USA WI', 'OU USA WI_Payables Manager',
'OU Austria', 'OU AUSTRIA_Payables Manager',
'OU China', 'OU CHINA_Payables Manager'
)
);

DBMS_OUTPUT.put_line ('l_resp_id => ' || l_resp_id);
DBMS_OUTPUT.put_line ('l_appl_id => ' || l_appl_id);
EXCEPTION
WHEN OTHERS
THEN
err_msg :=
'Error Occured while Deriving responsibility id' || SQLERRM;
apps.fnd_file.put_line
(apps.fnd_file.output,
'Error Occured while Deriving responsibility id'
);
END;

mo_global.set_policy_context ('S', l_org_id);
apps.fnd_global.apps_initialize (l_user_id, l_resp_id, l_appl_id);

FOR l_inv_rec IN invoice_cur ----(l_org_id)
LOOP
DBMS_OUTPUT.put_line
( 'Calling API ap_cancel_pkg.ap_cancel_single_invoice to Cancel Invoice: '
|| l_inv_rec.invoice_num
);
DBMS_OUTPUT.put_line
('**************************************************************');
l_boolean :=
ap_cancel_pkg.ap_cancel_single_invoice
(p_invoice_id => l_inv_rec.invoice_id,
p_last_updated_by => l_inv_rec.last_updated_by,
p_last_update_login => l_inv_rec.last_update_login,
p_accounting_date => l_inv_rec.gl_date,
p_message_name => l_message_name,
p_invoice_amount => l_invoice_amount,
p_base_amount => l_base_amount,
p_temp_cancelled_amount => l_temp_cancelled_amount,
p_cancelled_by => l_cancelled_by,
p_cancelled_amount => l_cancelled_amount,
p_cancelled_date => l_cancelled_date,
p_last_update_date => l_last_update_date,
p_original_prepayment_amount => l_original_prepayment_amount,
p_pay_curr_invoice_amount => l_pay_curr_invoice_amount,
p_token => l_token,
p_calling_sequence => NULL
);
DBMS_OUTPUT.put_line ('l_message_name => ' || l_message_name);
DBMS_OUTPUT.put_line ('l_invoice_amount => ' || l_invoice_amount);
DBMS_OUTPUT.put_line ('l_base_amount => ' || l_base_amount);
DBMS_OUTPUT.put_line ('l_tax_amount => ' || l_tax_amount);
DBMS_OUTPUT.put_line ( 'l_temp_cancelled_amount => '
|| l_temp_cancelled_amount
);
DBMS_OUTPUT.put_line ('l_cancelled_by => ' || l_cancelled_by);
DBMS_OUTPUT.put_line ('l_cancelled_amount => ' || l_cancelled_amount);
DBMS_OUTPUT.put_line ('l_cancelled_date => ' || l_cancelled_date);
DBMS_OUTPUT.put_line ('P_last_update_date => ' || l_last_update_date);
DBMS_OUTPUT.put_line ( 'P_original_prepayment_amount => '
|| l_original_prepayment_amount
);
DBMS_OUTPUT.put_line ( 'l_pay_curr_invoice_amount => '
|| l_pay_curr_invoice_amount
);

IF l_boolean
THEN
DBMS_OUTPUT.put_line ( 'Successfully Cancelled the Invoice => '
|| l_inv_rec.invoice_num
);
COMMIT;
ELSE
DBMS_OUTPUT.put_line ( 'Failed to Cancel the Invoice => '
|| l_inv_rec.invoice_num
);
ROLLBACK;
END IF;
END LOOP;
END cancel_invoices;