Query to Define Value Set for Customer
select RTRIM (RPAD (SUBSTRB (party_name, 1, 50), 36)) customer_name from hz_parties
where
party_type = 'ORGANIZATION'
and party_name <> '%'
and party_name not like 'cancel%'
Order by party_name
Friday, 27 December 2013
Query to Define Value Set for Customer
select RTRIM (RPAD (SUBSTRB (party_name, 1, 50), 36)) customer_name from hz_parties
where
party_type = 'ORGANIZATION'
and party_name <> '%'
and party_name not like 'cancel%'
Order by party_name
Wednesday, 23 October 2013
Sql Functions
Aggregate functions
All aggregate functions except COUNT(*)and GROUPING ignore nulls.
These are Aggregate functions
SUM()
SELECT SUM(salary) as Sum_Sal FROM emp;
AVG()
SELECT AVG(salary) as Avg_Sal FROM emp;
Max()
MAX returns maximum value of expression.
SELECT Max(salary) as Max_Sal FROM emp;
Min()
Min returns minimum value of expression.
SELECT Min(salary) as Min_Sal FROM emp;
Count()
Count returns the number of rows in the query.
SELECT COUNT(*) as total_rec FROM emp;
Example of Aggregate Function using GROUP BY:
To return the minimum and maximum salaries for each department in the emp table,we can use:group by clause
SELECT department_id,MIN(salary),MAX(salary)
FROM emp
GROUP BY dept_id;
To return the minimum and maximum salaries for each department in the emp table where minimum salary should be more than 5000
SELECT department_id, MIN(salary), MAX (salary)
FROM emp
WHERE MIN(salary) > 5000
GROUP BY dept_id;
Character Functions
ascii('A') would return 65
ascii('a') would return 97
ascii('a8') would return 97
CHR(<Character>)
Returns the character equivalent of the given integer.
Example
SELECT CHR(65), CHR(97) FROM dual;
Output : A a
CONCAT(<string1>,<string2>)
This function returns String2 appended to String1.
Example:
SELECT CONCAT('Fname', 'Lname') Emp_name FROM emp;
INITCAP(<String>)
This function returns String with the first character of each word in upper case and rest of all in lower case.
Example:
SELECT INITCAP('oracle tutorial') FROM Dual;
Output : Oracle Tutorial
instr( string1, string2 [, start_position [, nth_Appearance ] ] ):
string1 is the string to search.
string2 is the substring to search for in string1.
start_position is the position in string1 from where the search will start. This argument is optional. If not mentioned, it defaults to 1.
The first position in the string is 1. If the start_position is negative, the function counts backward direction
nth_appearance is the nth appearance of string2. This is optional. If not defined, it defaults to 1.
Example
SELECT INSTR('Character','r',1,1) POS1
, INSTR('Character','r',1,2) POS2
, INSTR('Character','a',-1,2) POS3
,INSTR('character','c',) POS4
FROM Dual;
pos1 pos2 pos3 pos4
4 9 3 6
LENGTH(<Str>)
Returns length of a string
secect length('Sql Tutorial') as len from dual;
O/p len
12
LOWER(<Str>)
This function returns a character string with all characters in lower case.
UPPER(<Str>)
This function returns a character string with all characters in upper case.
LPAD(<Str1>,<i>[,<Str2>])
This function returns the character string Str1 expanded in length to i characters, using Str2 to fill in space as needed on the left side of Str1.
Example
SELECT LPAD('Oracle',10,'.') lapd_doted from Dual, would return ....Oracle
SELECT LPAD('RAM', 7) lapd_exa from Dual would return ' RAM'
RPAD(<Str1>,<i>[,<Str2>])
RPAD is same as LPAD but Str2 is padded at the right side
LTRIM(<Str1>[,<Str2>])
The LTRIM function removes characters from the left side of the character Srting, with all the leftmost characters that appear in another text expression removed.
This function returns Str1 without any leading character that appears in Str2.If Str2 characters are leading character in Str1, then Str1 is returned unchanged.
Str2 defaults to a single space.
Example
Select LTRIM('datawarehousing','ing') trim1
, LTRIM('datawarehousing ') trim2
, LTRIM(' datawarehousing') trim3
, LTRIM('datawarehousing','data') trim4 from dual
O/P trim1 trim2 trim3 trim4
datawarehousing datawarehousing datawarehousing warehousing
RTRIM(<Str1>[,<Str2>])
Same as LTRIM but the characters are trimmed from the right side
TRIM([[<Str1>]<Str2> FROM]<Str3>)
Example
SELECT TRIM(' Oracle ') trim1, TRIM('Oracle ') trim2 FROM Dual;
Ans trim1 trim2
Oracle Oracle
It'll remove the space from both string.
REPLACE(<Str1>,<Str2>[,<Str3>]
This function returns Str1 with all occurrence of Str2 replaced with Str3
Example
SELECT REPLACE (‘Oracle’, ’Ora’, ’Arti’) replace_exa FROM Dual;
O/p replace_exa
Atricle
Essential Numeric Functions
ABS()
Select Absolute value
SELECT ABS(-25) "Abs" FROM DUAL;
Abs
-------
15
ACOS ()
Select cos value
SELECT ACOS(.28)"Arc_Cosine" FROM DUAL;
ASIN ()
Select sin value
SELECT ASIN(.6)"Arc_Cosine" FROM DUAL;
ATAN()
Select tan value
SELECT ATAN(.6)"Arc_Cosine" FROM DUAL;
CEIL()
Returns the smallest integer greater than or equal to the order total of a specified orde
SELECT CEIL(239.8) FROM Dual would return 240
FLOOR()
Returns the largest integer equal to or less than value.
SELECT FLOOR(15.65) "Floor" FROM DUAL;
Floor
------
15
MOD()
Return modulus value
SELECT MOD(11,3) "Mod" FROM DUAL;
Modulus
-------
2
POWER()
SELECT POWER(3,2) "Power" FROM DUAL;
power
-------
9
ROUND (number)
SELECT ROUND(43.698,1) "Round" FROM DUAL;
Round
----------
43.7
TRUNC (number)
The TRUNC (number) function returns n1 truncated to n2 decimal places. If n2 is omitted, then n1 is truncated to 0 places. n2 can be negative to truncate (make zero) n2 digits left of the decimal point.
SELECT TRUNC(12.75,1) "Trunc" FROM DUAL;
Trunc
----------
12.75
SELECT TRUNC(12.75,-1) "Trunc" FROM DUAL;
Trunc
------
10
Date And Time Function
ADD_MONTHS(date,number_of_month)
SELECT SYSDATE, ADD_MONTHS(SYSDATE,2), ADD_MONTHS(SYSDATE,-2) FROM DUAL;
Result:
SYSDATE ADD_MONTH ADD_MONTH
------------- ------------------------------------------
10-Feb-13 10-Apr-13 10-Dec-13
EXTRACT(<type> FROM <date>)
'Type' can be YEAR, MONTH, DAY, HOUR, MIN, SECOND, TIME_ZONE_HOUR, TIME_ZONE_MINUTE, TIME_ZONE_REGION
SELECT SYSDATE, EXTRACT(YEAR FROM SYSDATE)YEAR, EXTRACT(DAY FROM SYSDATE)DAY , EXTRACT(TIMEZONE_HOUR FROM SYSTIMESTAMP) TZH FROM DUAL;
LAST_DAY(<date>)
Extract last day of month
Example:
SELECT SYSDATE, LAST_DAY(SYSDATE) END_OF_MONTH FROM DUAL;
Result: SYSDATE END_OF_MO
---------- ---------------
10-Feb-13 28-Feb-13
NEXT_DAY(<date>,<day>)
SELECT NEXT_DAY('28-Feb-13','SUN') "FIRST MONDAY OF MARCH” FROM DUAL;
O/P FIRST MONDAY OF MARCH
03-Mar-13
ROUND (date[,<fmt>])
SELECT SYSDATE, ROUND(SYSDATE,'MM'), ROUND(SYSDATE,'YYYY') FROM DUAL;
Result:SYSDATE ROUND(SYS ROUND(SYS
----------- ---------------
10-FEB-13 01-MAR-13 01-JAN-13
TRUNC(date[,<fmt>])
SELECT SYSDATE, TRUNC(SYSDATE,'MM'), TRUNC(SYSDATE,'YYYY') FROM DUAL;
Result: SYSDATE TRUNC(SYS TRUNC(SYS
------- - ------------ --------
10-FEB-13 01-FEB-13 01-JAN-13
- Aggregate functions return a single result row based on groups of rows, rather than on single rows.
- Aggregate functions can appear in select lists and in ORDER BY and HAVING clauses.
All aggregate functions except COUNT(*)and GROUPING ignore nulls.
These are Aggregate functions
SUM()
SELECT SUM(salary) as Sum_Sal FROM emp;
AVG()
SELECT AVG(salary) as Avg_Sal FROM emp;
Max()
MAX returns maximum value of expression.
SELECT Max(salary) as Max_Sal FROM emp;
Min()
Min returns minimum value of expression.
SELECT Min(salary) as Min_Sal FROM emp;
Count()
Count returns the number of rows in the query.
SELECT COUNT(*) as total_rec FROM emp;
Example of Aggregate Function using GROUP BY:
To return the minimum and maximum salaries for each department in the emp table,we can use:group by clause
SELECT department_id,MIN(salary),MAX(salary)
FROM emp
GROUP BY dept_id;
To return the minimum and maximum salaries for each department in the emp table where minimum salary should be more than 5000
SELECT department_id, MIN(salary), MAX (salary)
FROM emp
WHERE MIN(salary) > 5000
GROUP BY dept_id;
Character Functions
- It calculates the ASCII equivalent of the first character of the given input string.
ascii('A') would return 65
ascii('a') would return 97
ascii('a8') would return 97
CHR(<Character>)
Returns the character equivalent of the given integer.
Example
SELECT CHR(65), CHR(97) FROM dual;
Output : A a
CONCAT(<string1>,<string2>)
This function returns String2 appended to String1.
Example:
SELECT CONCAT('Fname', 'Lname') Emp_name FROM emp;
INITCAP(<String>)
This function returns String with the first character of each word in upper case and rest of all in lower case.
Example:
SELECT INITCAP('oracle tutorial') FROM Dual;
Output : Oracle Tutorial
instr( string1, string2 [, start_position [, nth_Appearance ] ] ):
string1 is the string to search.
string2 is the substring to search for in string1.
start_position is the position in string1 from where the search will start. This argument is optional. If not mentioned, it defaults to 1.
The first position in the string is 1. If the start_position is negative, the function counts backward direction
nth_appearance is the nth appearance of string2. This is optional. If not defined, it defaults to 1.
Example
SELECT INSTR('Character','r',1,1) POS1
, INSTR('Character','r',1,2) POS2
, INSTR('Character','a',-1,2) POS3
,INSTR('character','c',) POS4
FROM Dual;
pos1 pos2 pos3 pos4
4 9 3 6
LENGTH(<Str>)
Returns length of a string
secect length('Sql Tutorial') as len from dual;
O/p len
12
LOWER(<Str>)
This function returns a character string with all characters in lower case.
UPPER(<Str>)
This function returns a character string with all characters in upper case.
LPAD(<Str1>,<i>[,<Str2>])
This function returns the character string Str1 expanded in length to i characters, using Str2 to fill in space as needed on the left side of Str1.
Example
SELECT LPAD('Oracle',10,'.') lapd_doted from Dual, would return ....Oracle
SELECT LPAD('RAM', 7) lapd_exa from Dual would return ' RAM'
RPAD(<Str1>,<i>[,<Str2>])
RPAD is same as LPAD but Str2 is padded at the right side
LTRIM(<Str1>[,<Str2>])
The LTRIM function removes characters from the left side of the character Srting, with all the leftmost characters that appear in another text expression removed.
This function returns Str1 without any leading character that appears in Str2.If Str2 characters are leading character in Str1, then Str1 is returned unchanged.
Str2 defaults to a single space.
Example
Select LTRIM('datawarehousing','ing') trim1
, LTRIM('datawarehousing ') trim2
, LTRIM(' datawarehousing') trim3
, LTRIM('datawarehousing','data') trim4 from dual
O/P trim1 trim2 trim3 trim4
datawarehousing datawarehousing datawarehousing warehousing
RTRIM(<Str1>[,<Str2>])
Same as LTRIM but the characters are trimmed from the right side
TRIM([[<Str1>]<Str2> FROM]<Str3>)
- If present Str1 can be one of the following literal: LEADING, TRAILING, BOTH.
- This function returns Str3 with all C1(leading trailing or both) occurrences of characters in Str2 removed.
- If any of Str1,Str2 or Str3 is Null, this function returns a Null.
Example
SELECT TRIM(' Oracle ') trim1, TRIM('Oracle ') trim2 FROM Dual;
Ans trim1 trim2
Oracle Oracle
It'll remove the space from both string.
REPLACE(<Str1>,<Str2>[,<Str3>]
This function returns Str1 with all occurrence of Str2 replaced with Str3
Example
SELECT REPLACE (‘Oracle’, ’Ora’, ’Arti’) replace_exa FROM Dual;
O/p replace_exa
Atricle
Essential Numeric Functions
ABS()
Select Absolute value
SELECT ABS(-25) "Abs" FROM DUAL;
Abs
-------
15
ACOS ()
Select cos value
SELECT ACOS(.28)"Arc_Cosine" FROM DUAL;
ASIN ()
Select sin value
SELECT ASIN(.6)"Arc_Cosine" FROM DUAL;
ATAN()
Select tan value
SELECT ATAN(.6)"Arc_Cosine" FROM DUAL;
CEIL()
Returns the smallest integer greater than or equal to the order total of a specified orde
SELECT CEIL(239.8) FROM Dual would return 240
FLOOR()
Returns the largest integer equal to or less than value.
SELECT FLOOR(15.65) "Floor" FROM DUAL;
Floor
------
15
MOD()
Return modulus value
SELECT MOD(11,3) "Mod" FROM DUAL;
Modulus
-------
2
POWER()
SELECT POWER(3,2) "Power" FROM DUAL;
power
-------
9
ROUND (number)
SELECT ROUND(43.698,1) "Round" FROM DUAL;
Round
----------
43.7
TRUNC (number)
The TRUNC (number) function returns n1 truncated to n2 decimal places. If n2 is omitted, then n1 is truncated to 0 places. n2 can be negative to truncate (make zero) n2 digits left of the decimal point.
SELECT TRUNC(12.75,1) "Trunc" FROM DUAL;
Trunc
----------
12.75
SELECT TRUNC(12.75,-1) "Trunc" FROM DUAL;
Trunc
------
10
Date And Time Function
ADD_MONTHS(date,number_of_month)
SELECT SYSDATE, ADD_MONTHS(SYSDATE,2), ADD_MONTHS(SYSDATE,-2) FROM DUAL;
Result:
SYSDATE ADD_MONTH ADD_MONTH
------------- ------------------------------------------
10-Feb-13 10-Apr-13 10-Dec-13
EXTRACT(<type> FROM <date>)
'Type' can be YEAR, MONTH, DAY, HOUR, MIN, SECOND, TIME_ZONE_HOUR, TIME_ZONE_MINUTE, TIME_ZONE_REGION
SELECT SYSDATE, EXTRACT(YEAR FROM SYSDATE)YEAR, EXTRACT(DAY FROM SYSDATE)DAY , EXTRACT(TIMEZONE_HOUR FROM SYSTIMESTAMP) TZH FROM DUAL;
LAST_DAY(<date>)
Extract last day of month
Example:
SELECT SYSDATE, LAST_DAY(SYSDATE) END_OF_MONTH FROM DUAL;
Result: SYSDATE END_OF_MO
---------- ---------------
10-Feb-13 28-Feb-13
NEXT_DAY(<date>,<day>)
SELECT NEXT_DAY('28-Feb-13','SUN') "FIRST MONDAY OF MARCH” FROM DUAL;
O/P FIRST MONDAY OF MARCH
03-Mar-13
ROUND (date[,<fmt>])
SELECT SYSDATE, ROUND(SYSDATE,'MM'), ROUND(SYSDATE,'YYYY') FROM DUAL;
Result:SYSDATE ROUND(SYS ROUND(SYS
----------- ---------------
10-FEB-13 01-MAR-13 01-JAN-13
TRUNC(date[,<fmt>])
SELECT SYSDATE, TRUNC(SYSDATE,'MM'), TRUNC(SYSDATE,'YYYY') FROM DUAL;
Result: SYSDATE TRUNC(SYS TRUNC(SYS
------- - ------------ --------
10-FEB-13 01-FEB-13 01-JAN-13
Most frequently asked in interviews.
These questions are the most frequently asked in interviews.
-
To fetch ALTERNATE records from a table. (EVEN NUMBERED)
select * from emp where rowid in (select decode(mod(rownum,2),0,rowid, null) from emp); -
To select ALTERNATE records from a table. (ODD NUMBERED)
select * from emp where rowid in (select decode(mod(rownum,2),0,null ,rowid) from emp); -
Find the 3rd MAX salary in the emp table.
select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2 where e1.sal <= e2.sal); -
Find the 3rd MIN salary in the emp table.
select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2where e1.sal >= e2.sal); -
Select FIRST n records from a table.
select * from emp where rownum <= &n; -
Select LAST n records from a table
select * from emp minus select * from emp where rownum <= (select count(*) - &n from emp); -
List dept no., Dept name for all the departments in which there are no employees in the department.
select * from dept where deptno not in (select deptno from emp);
alternate solution: select * from dept a where not exists (select * from emp b where a.deptno = b.deptno);
altertnate solution: select empno,ename,b.deptno,dname from emp a, dept b where a.deptno(+) = b.deptno and empno is null; -
How to get 3 Max salaries ?
select distinct sal from emp a where 3 >= (select count(distinct sal) from emp b where a.sal <= b.sal) order by a.sal desc; -
How to get 3 Min salaries ?
select distinct sal from emp a where 3 >= (select count(distinct sal) from emp b where a.sal >= b.sal); -
How to get nth max salaries ?
select distinct hiredate from emp a where &n = (select count(distinct sal) from emp b where a.sal >= b.sal); -
Select DISTINCT RECORDS from emp table.
select * from emp a where rowid = (select max(rowid) from emp b where a.empno=b.empno); -
How to delete duplicate rows in a table?
delete from emp a where rowid != (select max(rowid) from emp b where a.empno=b.empno); -
Count of number of employees in department wise.
select count(EMPNO), b.deptno, dname from emp a, dept b where a.deptno(+)=b.deptno group by b.deptno,dname; -
Suppose there is annual salary information provided by emp table. How to fetch monthly salary of each and every employee?
select ename,sal/12 as monthlysal from emp;
-
Select all record from emp table where deptno =10 or 40.
select * from emp where deptno=30 or deptno=10;
-
Select all record from emp table where deptno=30 and sal>1500.
select * from emp where deptno=30 and sal>1500;
-
Select all record from emp where job not in SALESMAN or CLERK.
select * from emp where job not in ('SALESMAN','CLERK');
-
Select all record from emp where ename in 'BLAKE','SCOTT','KING'and'FORD'.
select * from emp where ename in('JONES','BLAKE','SCOTT','KING','FORD');
-
Select all records where ename starts with ‘S’ and its lenth is 6 char.
select * from emp where ename like'S____';
-
Select all records where ename may be any no of character but it should end with ‘R’.
select * from emp where ename like'%R';
-
Count MGR and their salary in emp table.
select count(MGR),count(sal) from emp;
-
In emp table add comm+sal as total sal .
select ename,(sal+nvl(comm,0)) as totalsal from emp;
-
Select any salary <3000 from emp table.
select * from emp where sal> any(select sal from emp where sal<3000);
-
Select all salary <3000 from emp table.
select * from emp where sal> all(select sal from emp where sal<3000);
-
Select all the employee group by deptno and sal in descending order.
select ename,deptno,sal from emp order by deptno,sal desc;
-
How can I create an empty table emp1 with same structure as emp?
Create table emp1 as select * from emp where 1=2;
-
How to retrive record where sal between 1000 to 2000?
Select * from emp where sal>=1000 And sal<2000
-
Select all records where dept no of both emp and dept table matches.
select * from emp where exists(select * from dept where emp.deptno=dept.deptno)
-
If there are two tables emp1 and emp2, and both have common record. How can I fetch all the recods but common records only once?
(Select * from emp) Union (Select * from emp1)
-
How to fetch only common records from two tables emp and emp1?
(Select * from emp) Intersect (Select * from emp1)
-
How can I retrive all records of emp1 those should not present in emp2?
(Select * from emp) Minus (Select * from emp1)
-
Count the totalsa deptno wise where more than 2 employees exist.
SELECT deptno, sum(sal) As totalsal
FROM emp
GROUP BY deptno
HAVING COUNT(empno) > 2
Thursday, 3 October 2013
Order to Cash query
SELECT hp.party_name customer
,hca.account_number
,hp.party_number
,ooh.order_number
,ooh.flow_status_code Order_status
,rcta.trx_date Invoice_date
,rcta.trx_number Invoice_number
,rcta.status_trx Invoice_Status
,rcta.invoice_currency_code Invoice_currency
,rctla.line_number
,msi.segment1 Item_number
,rctla.description
,rctla.extended_amount line_amt
,arp.amount_due_original
,arp.amount_due_remaining
,rcta.org_id
,rcta.customer_trx_id
FROM RA_CUSTOMER_TRX_ALL RCTA,
RA_CUSTOMER_TRX_LINES_ALL RCTLA,
RA_CUST_TRX_TYPES_ALL rctt,
AR_PAYMENT_SCHEDULES_ALL arp,
HZ_CUST_ACCOUNTS_ALL HCA,
HZ_PARTIES HP,
MTL_SYSTEM_ITEMS_B msi,
OE_ORDER_LINES_ALL ool,
OE_ORDER_HEADERS_ALL ooh
WHERE rcta.customer_trx_id = rctla.customer_trx_id
AND msi.inventory_item_id = rctla.inventory_item_id
AND msi.organization_id = 116
AND rcta.cust_trx_type_id = rctt.cust_trx_type_id
AND arp.customer_trx_id = rcta.customer_trx_id
AND rctla.line_type = 'LINE'
AND rcta.org_id = rctla.org_id
AND rcta.bill_to_customer_id = hca.cust_account_id
AND hca.party_id = hp.party_id
AND rctla.interface_line_attribute6 = ool.line_id
AND ool.header_id = ooh.header_id
-- AND ooh.order_number = '13904756'
-- AND hp.party_name = 'BANQUE MAGNETIQUE'
ORDER BY hp.party_name,rcta.trx_number,rctla.line_number;
Invoice List for a Customer query
SELECT hp.party_name customer
,hca.account_number
,rcta.trx_date
,rcta.trx_number Invoice_number
,rcta.status_trx
,rcta.invoice_currency_code Invoice_currency
,rctla.line_number
,msi.segment1 Item_number
,rctla.description
,rctla.extended_amount line_amt
,rcta.org_id
,rctt.cust_trx_type_id
FROM RA_CUSTOMER_TRX_ALL RCTA,
RA_CUSTOMER_TRX_LINES_ALL RCTLA,
RA_CUST_TRX_TYPES_ALL rctt,
HZ_CUST_ACCOUNTS_ALL HCA,
HZ_PARTIES HP,
MTL_SYSTEM_ITEMS_B msi
WHERE rcta.customer_trx_id = rctla.customer_trx_id
AND msi.inventory_item_id = rctla.inventory_item_id
AND msi.organization_id = 116
AND rcta.cust_trx_type_id = rctt.cust_trx_type_id
AND rctt.TYPE = 'INV'
AND rctla.line_type = 'LINE'
AND rcta.org_id = rctla.org_id
AND rcta.bill_to_customer_id = hca.cust_account_id
AND hca.party_id = hp.party_id
AND hp.party_name = 'A. C. Networks'
-- AND hca.account_number = '1581'
-- AND rcta.status_trx = 'OP'
ORDER BY hp.party_name,rcta.trx_number,rctla.line_number;
Get List of responsibilities assigned to a user
select fu.user_name, frt.responsibility_name, furg.start_date, furg.end_date from fnd_user fu , fnd_user_resp_groups_direct furg , fnd_responsibility_vl frt where fu.user_id = furg.user_id and frt.responsibility_id = furg.responsibility_id and frt.application_id = furg.responsibility_application_id and nvl(furg.end_date,sysdate+1) > sysdate and nvl(frt.end_date,sysdate +1) > sysdate and fu.user_name = :p_user_name --and fu.user_name = :p_userid; ;
Trace details for a concurrent request along with the log and output file location details
SELECT 'Request id: ' || request_id, 'Trace id: ' || oracle_process_id, 'Trace Flag: ' || req.enable_trace, 'Trace Name: ' || dest.VALUE || '/' || LOWER (dbnm.VALUE) || '_ora_' || oracle_process_id || '.trc', 'Prog. Name: ' || prog.user_concurrent_program_name, 'File Name: ' || execname.execution_file_name || execname.subroutine_name, 'Status : ' || DECODE (phase_code, 'R', 'Running') || '-' || DECODE (status_code, 'R', 'Normal'), 'SID Serial: ' || ses.sid || ',' || ses.serial#, 'Module : ' || ses.module , TO_CHAR(req.actual_start_date,'DD-MON-YYYY HH24:MI:SS') Request_start_date , TO_CHAR(req.actual_completion_date,'DD-MON-YYYY HH24:MI:SS') Request_completion_date , req.logfile_name , req.outfile_name , req.argument_text parameters_passed FROM fnd_concurrent_requests req, v$session ses, v$process PROC, v$parameter dest, v$parameter dbnm, fnd_concurrent_programs_vl prog, fnd_executables execname WHERE req.request_id = :p_request_id AND req.oracle_process_id = proc.spid(+) AND proc.addr = ses.paddr(+) AND dest.NAME = 'user_dump_dest' AND dbnm.NAME = 'db_name' AND req.concurrent_program_id = prog.concurrent_program_id AND req.program_application_id = prog.application_id AND prog.application_id = execname.application_id AND prog.executable_id = execname.executable_id ;
List Concurrent Programs using a specific valueset in its parameter list
SELECT fcp.user_concurrent_program_name, fat.application_name, fdfc.column_seq_num Seq, fdfc.form_left_prompt "Parameter Name", fdfc.enabled_flag Active FROM FND_DESCR_FLEX_COL_USAGE_VL fdfc , fnd_flex_value_sets ffvs , fnd_concurrent_programs_vl fcp , fnd_application_tl fat WHERE 1 =1 AND ffvs.flex_value_set_id = fdfc.flex_value_set_id and fdfc.descriptive_flexfield_name = '$SRS$.'||fcp.concurrent_program_name and fcp.application_id = fat.application_id and ffvs.flex_value_set_name = :p_valuesetname -- Enter value set name ;
Friday, 30 August 2013
Form Personalization in Oracle Apps
Form Personalization in Oracle Apps
Form Personalization
Oracle has provided a simple and easy feature to implement the customer specific requirements without modifying the underlying forms code or CUSTOM library. Although CUSTOM library still can be used for forms customization to implement the complex business logic, the personalization feature provided in the latest release is easy, faster and requires minimum development effort.
If user needs to personalize any details in a form like changing field names, adding some validations, etc, then Form Personalization can provide the way out in few simple steps.
Please follow here to go through Steps with example
Steps:
Step – 1:
Go to the specific form where you need the changes
Then we need to note down the block and field name which is needed in next steps.
So keep the cursor on the required field and then Go to Help --> Diagnostics --> Examine
Note down the Block and Field value.
Step -2:
Now the step to do the personalization
Follow this navigation
Help à Diagnostics à Custom Code à Personalize
This will show the Form Personalization window
Here you need to do the customization as per the requirement.
As you can see in this form, there are mainly 4 sections
1. Rule
2. Condition
3. Actions
4. Context
Step – 3:
Rule:
This is the section where you need to create the rules how your requirement is going to be executed. This section contains 4 segments, Seq, description, Level and Enabled flag.
Seq : Need to give the Sequence how the rule will execute (a number)
Description: Write a sort description of the rule which you are going to create
Level: Select the level where you want the rule to execute
Enabled: This is the flag which will enable or disable the rule.
Example:
Step – 4:
Condition:
Now need to set the condition.
This is the section where you will define when the rule will be executed.
This section contains 4 segments, Trigger Event, Trigger Object, Condition and Processing mode.
Trigger Event: This specifies where the rule will be executed. There are mainly 5 types of event which decided the trigger of the rule.
Trigger Object: This is the Object in the form which decides at what level the rule will be executed.
The values can be “<Form Name>”, ‘<Block Name>” or “<Item Name>”
Condition: Here mention any SQL statement to control the execution of the rule when the mentioned criterion is met.
Processing Mode: This is the mode where you want the rules to be executed.
Example:
Step – 5:
Actions:
This decides what will happen when the rule will execute.
This contains 2 sections.
Left sections contains Seq, Type, Description, Language, Enabled
Seq: Mention the Action sequence which this rule will execute
Type: This is of 4 types, Property, Message, Builtin, Menu.
This decides which type of action, the rule will perform.
According to this the right section will come and need to fill the detailed actions.
Description: Write a description on the action which this will perform.
Language: Select the language
Enabled: You can enable or disable the actions here.
Right Section is a dynamic section; it appears depending on the Type field value in Left section.
Action Types:
Property
The action type “Property” is used to set the properties of the objects. The various objects include
“Item, Window, and Block etc.”. Actual object name defined in the form should be entered after selecting the object type.
Property Name is the property to be changed
Value is the new desired value for the property.
The current value of the property will be displayed when clicked on the “Get Value” button.
Example:
Object Type = “Item”
Target Object = “:ORDER.ORDER_NUMBER”
Property Name = “PROMPT_TEXT”
Value = “Claim Number”
The value is interpreted at runtime, so you can use SQL functions and operators. Any value started with “=” operator will be interpreted at runtime; otherwise the value is treated as is entered in the value field
Message:
The action type “Message” is used to display custom messages during runtime. Message Type and Description should be entered after selecting the action type as “Message”. The available message types are “Error, Warning, Hint, Question and Debug”. Depending on the response expected from the user, the appropriate message type should be selected.
Example:
Message Type = “Hint”
Message Text = “Please Follow the Date format DD-MON-YYYY”
Builtin:
The action type “Builtin” is used to execute the form and AOL API’s. Depending on the API type selected, the parameters should be entered.
Example:
Builtin Type = FND_UTILITIES.OPEN_URL
Argument = http://www.oracle.com
Menu:
The action type “Menu” is used to activate the available special menus on the Tools menu.
Oracle Applications provide 45 special menus under Tools menu which can be used by customers based on their requirements.
Select the SPECIAL menu which is not used by the form. Menu label is the prompt which appears to the users when Tools menu is invoked, block specifies the blocks for which the special menu should be activated and Icon name is the .ico file name.
A separator can be created above the activated special menu by selecting the “Render line before menu” checkbox.
Example:
Menu Entry = SPECIAL4
Menu Label = Additional Order Header Information
Enabled in Blocks(s) = ORDER
Icon Name = Flower.ico
Step – 6:
Context:
Context manages to whom the personalization should apply. This is similar to the concept of using profile options in Oracle Applications. The various levels are Site, Responsibility, Industry and User. During runtime, the values provided in the context are evaluated and personalization rules will be applied. Usage of context is very vital in implementing the personalization to prevent the inappropriate users accessing these customizations of the form.
Example:
Context = Responsibility
Value = Order Management Super User
Step by Step Analysis
Requirement: Don’t allow the user to use more than 1 qty in Order line Qty field during creation of a sales order.
Solution:
Step -1:
Go to the specific form where you need the changes
Here in this case this will be the Sales Orders form and then Line Items tab.
Then we need to note down the block and field name which is needed in next steps.
So keep the cursor on the required field and then Go to Help à Diagnostics à Examine
Note down the Block and Field value.
Step -2:
Help --> Diagnostics --> Custom Code --> Personalize
Step – 3:
Fill the Rule section
Step – 4:
Fill the Condition section
Step – 5:
Fill the Actions section
Step – 6:
Fill the context As you need.
Save this.
Log out of the application and login again, then in Sales Order line item tab enter Qty field value as 1 and try to save, you can see the message.
Wednesday, 14 August 2013
Tuesday, 30 July 2013
Oracle APPS Important Tables
ONT- Order Management
Table Name Description
OE_ORDER_HEADERS_ALL stores header information
for orders in Order Management.
OE_ORDER_LINES_ALL stores information for all order lines in Oracle Order Management.
OE_ORDER_SOURCES Feeder System Names that create orders in Order Management tables.
OE_ORDER_HOLDS_ALL This table stores information of all the orders and lines that are on hold and the link to hold sources and hold releases.
OE_SALES_CREDITS This table stores information about sales credits.
OE_TRANSACTION_TYPES_ALL This table stores information about the order and line transaction types
WSH_DELIVERY_ASSIGNMENTS Delivery Assignments
WSH_DELIVERY_DETAILS Delivery Details
WSH_NEW_DELIVERIES Deliveries
WSH_TRIPS Trips
WSH_TRIP_STOPS Trip Stops
PO - Purchasing
Table Name Description
PO_ACTION_HISTORY Document approval and control action history table
PO_AGENTS Buyers table
PO_DISTRIBUTIONS_ALL Purchase order distributions
PO_HEADERS_ALL Document headers (for purchase orders, purchase agreements, quotations, RFQs)
PO_LINES_ALL Purchase document lines (for purchase orders, purchase agreements, quotations, RFQs)
PO_LINE_LOCATIONS_ALL Document shipment schedules (for purchase orders, purchase agreements, quotations, RFQs)
PO_RELEASES_ALL Purchase order releases
PO_LINES_ARCHIVE_ALL Archived purchase order lines
PO_LINE_LOCATIONS_ARCHIVE_ALL Archived purchase order shipments
PO_HEADERS_ARCHIVE_ALL Archived purchase orders
PO_LINE_TY PES_B Line types
PO_RELEASES_ARCHIVE_ALL Archived releases
PO_REQUISITION_HEADERS_ALL Requisition headers
PO_REQUISITION_LINES_ALL Requisition lines
PO_REQ_DISTRIBUTIONS_ALL Requisition distributions
RCV_TRANSACTIONS Receiving transactions
RCV_SHIPMENT_HEADERS Shipment and receipt header information
RCV_SHIPMENT_LINES Receiving shipment line information
INV – Inventory
Table Name Description
MTL_CATEGORIES_B Code combinations table for Item Category
MTL_CATEGORY_SETS_B Category Sets
MTL_CUSTOMER_ITEMS Customer item Information
MTL_CUSTOMER_ITEM_XREFS Relationships between customer items and inventory items
MTL_DEMAND Sales order demand and reservations
MTL_DEMAND_HISTORIES Sales order demand and reservations
MTL_ITEM_LOCATIONS Definitions for stock locators
MTL_ITEM_REVISIONS_B Item revisions
MTL_ITEM_TEMPLATES_B Item template definitions
MTL_ITEM_TEMPL_ATTRIBUTES Item attributes and attribute values for a template
MTL_LOT_NUMBERS Lot number definitions
MTL_MATERIAL_TRANSACTIONS Material transaction table
MTL_MATERIAL_TRANSACTIONS_TEMP Temporary table for processing material transactions
MTL_ONHAND_QUANTITIES_DETAIL FIFO quantities by control level and receipt
MTL_PARAMETERS Inventory control options and defaults
MTL_RESERVATIONS Reservations
MTL_SECONDARY_INVENTORIES Subinventory definitions
MTL_SECONDARY_LOCATORS Item-subinventory-locator assignments
MTL_SERIAL_NUMBERS Serial number definitions
MTL_SYSTEM_ITEMS_B Inventory item definitions
MTL_TRANSACTION_ACCOUNTS Material transaction distributions
MTL_TRANSACTION_TYPES Inventory Transaction Types Table
MTL_TXN_REQUEST_HEADERS Move Order headers table
MTL_TXN_REQUEST_LINES Move order lines table
MTL_UNIT_TRANSACTIONS Serial number transactions
GL- General Ledger
Table Name Description
GL_CODE_COMBINATIONS Stores valid account combinations
GL_SETS_OF_BOOKS Stores information about the sets of books
GL_IMPORT_REFERENCES Stores individual transactions from subledgers
GL_DAILY_RATES Stores the daily conversion rates for foreign currency Transactions
GL_PERIODS Stores information about the accounting periods
GL_JE_HEADERS Stores journal entries
GL_JE_LINES Stores the journal entry lines that you enter in the Enter Journals form
GL_JE_BATCHES Stores journal entry batches
GL_BALANCES Stores actual, budget, and encumbrance balances for detail and summary accounts
GL_BUDGETS Stores Budget definitions
GL_INTERFACE Import journal entry batches
GL_BUDGET_INTERFACE Upload budget data from external sources
GL_DAILY_RATES_INTERFACE Import daily conversion rates
AR- Accounts Receivables
Table Name Description
RA_CUST_TRX_TYPES_ALL Transaction type for invoices, commitments and credit memos
RA_CUSTOMER_TRX_ALL Header-level information about invoices, debit memos, chargebacks, commitments and credit memos
RA_CUSTOMER_TRX_LINES_ALL Invoice, debit memo, chargeback, credit memo and commitment lines
RA_CUST_TRX_LINE_GL_DIST_ALL Accounting records for revenue, unearned revenue and unbilled receivables
RA_CUST_TRX_LINE_SALESREPS_ALL Sales credit assignments for transactions
AR_ADJUSTMENTS_ALL Pending and approved invoice adjustments
RA_BATCHES_ALL
AR_CASH_RECEIPTS_ALL Detailed receipt information
AR_CASH_RECEIPT_HISTORY_ALL History of actions and status changes in the life cycle of a receipt
AR_PAYMENT_SCHEDULES_ALL All transactions except adjustments and miscellaneous cash receipts
AR_RECEIVABLE_APPLICATIONS_ALL Accounting information for cash and credit memo applications
AR_TRANSACTION_HISTORY_ALL Life cycle of a transaction
HZ_CUST_ACCOUNTS Stores information about customer accounts.
HZ_CUSTOMER_PROFILES Credit information for customer accounts and customer account sites
HZ_CUST_ACCT_SITES_ALL Stores all customer account sites across all operating units
HZ_CUST_ACCT_RELATE_ALL Relationships between customer accounts
HZ_CUST_CONTACT_POINTS This table is no longer used
HZ_CUST_PROF_CLASS_AMTS Customer profile class amount limits for each currency
HZ_CUST_SITE_USES_ALL Stores business purposes assigned to customer account sites.
HZ_LOCATIONS Physical addresses
HZ_ORG_CONTACTS People as contacts for parties
HZ_ORG_CONTACT_ROLES Roles played by organization contacts
HZ_PARTIES Information about parties such as organizations, people, and groups
HZ_PARTY_SITES Links party to physical locations
HZ_PARTY_SITE_USES The way that a party uses a particular site or address
HZ_RELATIONSHIPS Relationships between entities
HZ_RELATIONSHIP_TYPES Relationship types
CE- Cash Management
Table Name Description
CE_BANK_ACCOUNTS This table contains bank account information. Each bank account must be affiliated with one bank branch.
CE_BANK_ACCT_BALANCES This table stores the internal bank account balances
CE_BANK_ACCT_USES_ALL This table stores information about your bank account uses.
CE_STATEMENT_HEADERS Bank statements
CE_STATEMENT_LINES Bank statement lines
CE_STATEMENT_HEADERS_INT Open interface for bank statements
CE_STATEMENT_LINES_INTERFACE Open interface for bank statement lines
CE_TRANSACTION_CODES Bank transaction codes
AP- Accounts Payables
Table Name Description
AP_ACCOUNTING_EVENTS_ALL Accounting events table
AP_AE_HEADERS_ALL Accounting entry headers table
AP_AE_LINES_ALL Accounting entry lines table
AP_BANK_ACCOUNTS_ALL Bank Account Details
AP_BANK_ACCOUNT_USES_ALL Bank Account Uses Information
AP_BANK_BRANCHES Bank Branches
AP_BATCHES_ALL Summary invoice batch information
AP_CHECKS_ALL Supplier payment data
AP_HOLDS_ALL Invoice hold information
AP_INVOICES_ALL Detailed invoice records
AP_INVOICE_LINES_ALL AP_INVOICE_LINES_ALL contains records for invoice lines entered manually, generated automatically or imported from the Open Interface.
AP_INVOICE_DISTRIBUTIONS_ALL Invoice distribution line information
AP.AP_INVOICE_PAYMENTS_ALL Invoice payment records
AP_PAYMENT_DISTRIBUTIONS_ALL Payment distribution information
AP_PAYMENT_HISTORY_ALL Maturity and reconciliation history for payments
AP_PAYMENT_SCHEDULES_ALL Scheduled payment information on invoices
AP_INTERFACE_REJECTIONS Information about data that could not be loaded by Payables Open Interface Import
AP_INVOICES_INTERFACE Information used to create an invoice using Payables Open Interface Import
AP_INVOICE_LINES_INTERFACE Information used to create one or more invoice distributions
AP_SUPPLIERS AP_SUPPLIERS stores information about your supplier level attributes.
AP_SUPPLIER_SITES_ALL AP_SUPPLIER_SITES_ALL stores information about your supplier site level attributes.
AP_SUPPLIER_CONTACTS Stores Supplier Contacts
FA – Fixed Assets
Table Name Description
FA_ADDITIONS_B Descriptive information about assets
FA_ADJUSTMENTS Information used by the posting program to generate journal entry lines in the general ledger
FA_ASSET_HISTORY Historical information about asset reclassifications and unit adjustments
FA_ASSET_INVOICES Accounts payable and purchasing information for each asset
FA_BOOKS Financial information of each asset
FA_BOOK_CONTROLS Control information that affects all assets in a depreciation book
FA_CALENDAR_PERIODS Detailed calendar information
FA_CALENDAR_TYPES General calendar information
FA_CATEGORIES_B Default financial information for asset categories
FA_CATEGORY_BOOKS Default financial information for an asset category and depreciation book combination
FA_DEPRN_DETAIL Depreciation amounts charged to the depreciation expense account in each distribution line
FA_DEPRN_PERIODS Information about each depreciation period
FA_DEPRN_EVENTS Information about depreciation accounting events.
FA_DEPRN_SUMMARY Depreciation information at the asset level
FA_DISTRIBUTION_ACCOUNTS Table to store account ccids for all distributions for a book
FA_DISTRIBUTION_DEFAULTS Distribution set information
FA_DISTRIBUTION_HISTORY Employee, location, and Accounting Flexfield values assigned to each asset
FA_DISTRIBUTION_SETS Header information for distribution sets
FA_FORMULAS Depreciation rates for formula-based methods
FA_LOCATIONS Location flexfield segment value combinations
FA_MASS_ADDITIONS Information about assets that you want to automatically add to Oracle Assets from another system
FA_METHODS Depreciation method information
FA_RETIREMENTS Information about asset retirements and reinstatements
HRMS- Human Resource Management System
Table Name Description
HR_ALL_ORGANIZATION_UNITS Organization unit definitions.
HR_ALL_POSITIONS_F Position definition information.
HR_LOCATIONS_ALL Work location definitions.
PER_ADDRESSES Address information for people
PER_ALL_PEOPLE_F DateTracked table holding personal information for employees, applicants and other people.
PER_ALL_ASSIGNMENTS_F Allocated Tasks
PER_ANALYSIS_CRITERIA Flexfield combination table for the personal analysis key flexfield.
PER_ASSIGNMENT_EXTRA_INFO Extra information for an assignment.
PER_ASSIGNMENT_STATUS_TYPES Predefined and user defined assignment status types.
PER_CONTRACTS_F The details of a persons contract of employment
PER_CONTACT_RELATIONSHIPS Contacts and relationship details for dependents, beneficiaries, emergency contacts, parents etc.
PER_GRADES Grade definitions for a business group.
PER_JOBS Jobs defined for a Business Group
PER_PAY_BASES Definitions of specific salary bases
PER_PAY_PROPOSALS Salary proposals and performance review information for employee assignments
PER_PEOPLE_EXTRA_INFO Extra information for a person
PER_PERIODS_OF_PLACEMENT Periods of placement details for a non-payrolled worker
PER_PERIODS_OF_SERVICE Period of service details for an employee.
PER_PERSON_ANALYSES Special information types for a person
PER_PERSON_TYPES Person types visible to specific Business Groups.
PER_PERSON_TYPE_USAGES_F Identifies the types a person may be.
PER_PHONES PER_PHONES holds phone numbers for current and ex-employees, current and ex-applicants and employee contacts.
PER_SECURITY_PROFILES Security profile definitions to restrict user access to specific HRMS records
PAY- Payroll
Table Name Description
PAY_ACTION_INFORMATION Archived data stored by legislation
PAY_ALL_PAYROLLS_F Payroll group definitions.
PAY_ASSIGNMENT_ACTIONS Action or process results, showing which assignments have been processed by a specific payroll action, or process.
PAY_ELEMENT_CLASSIFICATIONS Element classifications for legislation and information needs.
PAY_ELEMENT_ENTRIES_F Element entry list for each assignment.
PAY_ELEMENT_ENTRY_VALUES_F Actual input values for specific element entries.
PAY_ELEMENT_LINKS_F Eligibility rules for an element type.
PAY_ELEMENT_TYPES_F Element definitions.
PAY_ELEMENT_TYPE_USAGES_F Used to store elements included or excluded from a defined run type.
PAY_ORG_PAYMENT_METHODS_F Payment methods used by a Business Group.
PAY_PAYMENT_TYPES Types of payment that can be processed by the system.
PAY_PAYROLL_ACTIONS Holds information about a payroll process.
PAY_PEOPLE_GROUPS People group flexfield information.
PAY_PERSONAL_PAYMENT_METHODS_F Personal payment method details for an employee.
PAY_RUN_RESULTS Result of processing a single element entry.
PAY_RUN_RESULT_VALUES Result values from processing a single element entry.
PAY_SECURITY_PAYROLLS List of payrolls and security profile access rules.
PAY_INPUT_VALUES_F Input value definitions for specific elements.
BOM – Bills Of Material
Table Name Description
BOM_DEPARTMENTS Departments
BOM_DEPARTMENT_CLASSES Department classes
BOM_DEPARTMENT_RESOURCES Resources associated with departments
BOM_OPERATIONAL_ROUTINGS Routings
BOM_OPERATION_NETWORKS Routing operation networks
BOM_OPERATION_RESOURCES Resources on operations
BOM_OPERATION_SEQUENCES Routing operations
BOM_OPERATION_SKILLS
BOM_RESOURCES Resources, overheads, material cost codes, and material overheads
BOM_STANDARD_OPERATIONS Standard operations
BOM_ALTERNATE_DESIGNATORS Alternate designators
BOM_COMPONENTS_B Bill of material components
BOM_STRUCTURES_B Bills of material
BOM_STRUCTURE_TYPES_B Structure Type master table
WIP – Work in Process
Table Name Description
WIP_DISCRETE_JOBS Discrete jobs
WIP_ENTITIES Information common to jobs and schedules
WIP_LINES Production lines
WIP_MOVE_TRANSACTIONS Shop floor move transactions
WIP_MOVE_TXN_ALLOCATIONS Move transaction allocations for repetitive schedules
WIP_OPERATIONS Operations necessary for jobs and schedules
WIP_OPERATION_NETWORKS Operation dependency
WIP_OPERATION_OVERHEADS Overheads for operations in an average costing organization
WIP_OPERATION_RESOURCES Resources necessary for operations
WIP_OPERATION_YIELDS This table keeps all costing information for operation yield costing.
WIP_TRANSACTIONS WIP resource transactions
WIP_TRANSACTION_ACCOUNTS Debits and credits due to resource transactions
FND – Appication Object Library
Table Name Description
FND_APPLICATION Applications registered with Oracle Application Object Library
FND_CONCURRENT_PROGRAMS Concurrent programs
FND_CONCURRENT_REQUESTS Concurrent requests information
FND_CURRENCIES Currencies enabled for use at your site
FND_DATA_GROUPS Data groups registered with Oracle Application Object Library
FND_FLEX_VALUES Valid values for flexfield segments
FND_FLEX_VALUE_HIERARCHIES Child value ranges for key flexfield segment values
FND_FLEX_VALUE_SETS Value sets used by both key and descriptive flexfields
FND_FORM Application forms registered with Oracle Application Object Library
FND_FORM_FUNCTIONS Functionality groupings
FND_ID_FLEXS Registration information about key flexfields
FND_ID_FLEX_SEGMENTS Key flexfield segments setup information and correspondences between table columns and key flexfield segments
FND_ID_FLEX_STRUCTURES Key flexfield structure information
FND_LOOKUP_TYPES Oracle Application Object Library QuickCodes
FND_LOOKUP_VALUES QuickCode values
FND_MENUS New menu tabl for Release 10SC
FND_PROFILE_OPTIONS User profile options
FND_PROFILE_OPTION_VALUES Values of user profile options defined at different profile levels
FND_REQUEST_SETS Reports sets
FND_REQUEST_SET_PROGRAMS Reports within report sets
FND_REQUEST_SET_STAGES Stores request set stages
FND_RESPONSIBILITY Responsibilities
FND_RESP_FUNCTIONS Function Security
FND_USER Application users
JA - Asia/Pacific Localizations
Table Name Description
JAI_CMN_BOE_HDRS Stores BOE header info when a BOE Invoice is created through IL
JAI_CMN_BOE_DTLS Detail table for BOE Invoices
JAI_CMN_TAXES_ALL Master table for Localization Taxes
JAI_CMN_TAX_CTGS_ALL Stores tax categories and their link to excise ITEM classes.
JAI_CMN_TAX_CTG_LINES Stores the tax lines for defined tax categories
JAI_CMN_VENDOR_SITES Stores excise account related information about vendors.
JAI_RGM_DEFINITIONS Stores regime information.
JAI_RGM_TAXES This table stores tax details for transactions having TCS tax type.
JAI_CMN_RG_23AC_I_TRXS Stores Information of RG23A/C records and known as Quantity Register.
JAI_CMN_RG_23AC_II_TRXS Stores Information of RG23A/C Part II Details. Also known as Amount Register
JAI_CMN_RG_23D_TRXS Quantity register for Trading Organizations
JAI_CMN_RG_BALANCES Store the current balances of RG23A, RG23C and PLA Registers
JAI_CMN_RG_PLA_TRXS Stores the Transaction Information of PLA Register.
JAI_CMN_RG_PLA_HDRS Stores PLA header Infomation when a PLA invoice is created in AP module
JAI_CMN_RG_PLA_DTLS Stores PLA Detail Information when a PLA Invoice is created in AP Module
QP – Advanced Pricing
Table Name Description
QP_LIST_HEADERS_B QP_LIST_HEADERS_B stores the header information for all lists. List types can be, for example, Price Lists, Discount Lists or Promotions.
QP_LIST_LINES QP_LIST_LINES stores all list lines for lists in QP_LIST_HEADERS_B.
QP_PRICE_FORMULAS_B QP_PRICE_FORMULAS_B stores the pricing formula header information.
QP_PRICE_FORMULA_LINES QP_PRICE_FORMULA_LINES stores each component that makes up the formula.
QP_PRICING_ATTRIBUTES QP_PRICING_ATTRIBUTES stores product information and pricing attributes.
QP_QUALIFIERS QP_QUALIFIERS stores qualifier attribute information.
XLA - Subledger Accounting
Table Name Description
XLA_EVENTS The XLA_EVENTS table record all information related to a specific event. This table is created as a type XLA_ARRAY_EVENT_TYPE.
XLA_TRANSACTION_ENTITIES The table XLA_ENTITIES contains information about sub-ledger document or transactions.
XLA_AE_HEADERS The XLA_AE_HEADERS table stores subledger journal entries. There is a one-to-many relationship between accounting events and journal entry headers.
XLA_AE_LINES The XLA_AE_LINES table stores the subledger journal entry lines. There is a one-to-many relationship between subledger journal entry headers and subledger journal entry lines.
XLA_DISTRIBUTION_LINKS The XLA_DISTRIBUTION_LINKS table stores the link between transactions and subledger journal entry lines.
XLA_ACCOUNTING_ERRORS The XLA_ACCOUNTING_ERRORS table stores the errors encountered during execution of the Accounting Program.
XLA_ACCTG_METHODS_B The XLA_ACCTG_METHODS_B table stores Subledger Accounting Methods (SLAM) across products. SLAMs provided by development are not chart of accounts specific. Enabled SLAMs are assigned to ledgers.
XLA_EVENT_TYPES_B The XLA_EVENT_TYPES_B table stores all event types that belong to an event class.
XLA_GL_LEDGERS This table contains ledger information used by subledger accounting.
Subscribe to:
Posts (Atom)